[saraaa]

Hi,
How can I make an advanced search according to the variables? I defined a variable (Project_Name), and managers want to search cases according to the variable(Project_Name) in advanced search.
I'm using community edition.
I have some ideas for doing that. For example, I can make report table with the mentioned variable(Project_Name) , and after that I can emerge the fields of app_cache_view table and report table inside a grid to export them to a CVS file for managers. Can you suggest better solution?

[amosbatto]

The easiest solution is to use the Custom Case List Builder feature in the Enterprise Edition, which allows you to search in the variables used for the extra columns in the case list.

However, if you want to stay with the Community Edition, my recommendation is to create a plugin that adds an extra option in the cases sidebar under the HOME menu. You have the right idea of searching in the Report Tables and left join those results with the APPLICATION or APP_CACHE_VIEW table to get details about the case.

To execute a database query in a plugin, you can import the ProcessMaker functions and then use executeQuery():

Code: Select all

G::LoadClass("pmFunctions");
$results = executeQuery("SELECT...");

[saraaa]

Thanks,
I made a process with the trigger below:

Code: Select all

$query = 'SELECT APP_NUMBER AS app_number, APP_STATUS AS app_status, DEL_DELEGATE_DATE AS delegate_date, 
DEL_INIT_DATE AS init_date, DEL_FINISH_DATE AS finish_date, DEL_TASK_DUE_DATE AS due_date, DEL_THREAD_STATUS AS task_status,
 APP_PRO_TITLE AS pro_title, APP_TAS_TITLE AS task_title, APP_CURRENT_USER AS user FROM APP_CACHE_VIEW WHERE DEL_THREAD_STATUS="CLOSED"';
$result = executeQuery($query);
if (is_array($result) and count($result) > 0)
   @=get_KPI = $result;

That was OK.
I created a report table, too. I want to combine PMT_PROJECT and APP_CACHE_VIEW tables in the aforementioned grid.
I need to have project_name column from PMT_PROJECT WHERE APP_NUMBER of PMT_PROJECT must be equal with APP_NUMBER of APP_CACHE_VIEW.
In other words, those fields of project_name must be added the grid which have the same APP_NUMBER.
I wrote a trigger like this:

Code: Select all

$query1 = 'SELECT PROJECT_NAME AS project_name, APP_NUMBER AS app_number, APP_STATUS AS app_status,
 DEL_DELEGATE_DATE AS delegate_date, DEL_INIT_DATE AS init_date, DEL_FINISH_DATE AS finish_date, 
DEL_TASK_DUE_DATE AS due_date, DEL_THREAD_STATUS AS task_status, APP_PRO_TITLE AS pro_title,
 APP_TAS_TITLE AS task_title, APP_CURRENT_USER AS user FROM APP_CACHE_VIEW INNER JOIN PMT_PROJECT ON 
APP_CACHE_VIEW.APP_NUMBER = PMT_PROJECT.APP_NUMBER';
$result1 = executeQuery($query1);
if (is_array($result1) and count($result1) > 0)
   @=get_KPI = $result1;

I see this error:
Could not execute query [Native Error: Column 'APP_NUMBER' in field list is ambiguous]
My trigger has some problems. Can you help me?

[saraaa]

does anyone know?

[amosbatto]

You need to specify which tables the fields come from in your SQL query and use a left join:

Code: Select all

$query1 = 'SELECT P.PROJECT_NAME AS project_name, P.APP_NUMBER AS app_number, 
   ACV.APP_STATUS AS app_status, ACV.DEL_DELEGATE_DATE AS delegate_date, ACV.DEL_INIT_DATE AS init_date,
   ACV.DEL_FINISH_DATE AS finish_date, ACV.DEL_TASK_DUE_DATE AS due_date, 
   ACV.DEL_THREAD_STATUS AS task_status, ACV.APP_PRO_TITLE AS pro_title,
   ACV.APP_TAS_TITLE AS task_title, ACV.APP_CURRENT_USER AS user 
   FROM APP_CACHE_VIEW AS ACV
   LEFT JOIN PMT_PROJECT AS P ON 
   ACV.APP_NUMBER = P.APP_NUMBER';
$result1 = executeQuery($query1);

if (is_array($result1) and count($result1) > 0) {
   @=get_KPI = $result1;
}

You can execute your SQL queries in MySQL from the command line or in PhpMyAdmin to debug them.

[saraaa]

Thank you Amos,
I wrote a query like this:

Code: Select all

 SELECT SUBSTRING(A.APP_DATA, 450, 10) AS app_data, ACV.APP_NUMBER AS app_number, 
    ACV.APP_STATUS AS app_status 
    FROM APP_CACHE_VIEW AS ACV 
    LEFT JOIN APPLICATION AS A ON 
   ACV.APP_NUMBER = A.APP_NUMBER

The problem is that the characters length of the variables and names are not fixed. So, I can not see my intended output.
As I told you, I made a variable( project_name), and I want to see just the name of it. ( For example, project_name_label is TEST PROJECT.
If I use report table and left join those results with APP_CACHE_VIEW table, it does not meet my intention. Because, I have project_name in more than 5 processes.
What can I do for solving that?

[amosbatto]

You have to unserialize the APP_DATA and then check if the variable "project_name" has been set in the case. Then add it to the array of query results like this:

Code: Select all

$sql = "SELECT A.APP_DATA AS app_data, ACV.APP_NUMBER AS app_number, 
    ACV.APP_STATUS AS app_status 
    FROM APP_CACHE_VIEW AS ACV 
    LEFT JOIN APPLICATION AS A ON 
   ACV.APP_NUMBER = A.APP_NUMBER";
$aCases = executeQuery($sql);

for ($i = 1; $i <= count($aCases); $i++) {
    $aVars = unserialize($aCases[$i]['app_data']);
    $aCases[$i]['project_name'] = isset($aVars['project_name']) ? $aVars['project_name'] : '';
}

See: https://wiki.processmaker.com/3.2/Trigg ... le_Storage

[saraaa]

Thanks Amos,
Now, I'm trying to write a program which has more options. After advanced search, I want to calculate the differences between to dates. (for example DEL_INIT_DATE and DEL_FINISH_DATE)
The problem is that it does not consider the off time, off days and vacations. So, I can not have the real time.
For example, I have this code:

Code: Select all

$fdate = strtotime('2019/09/10');
$sdate = strtotime('2019/09/01');
$number_of_days = ($fdate - $sdate)/3600/24;
$real_hours = $number_of_days * 8;

It represents the employee worked 72 hours. But, we know this would be less than 72 hours. (because of weekends)
It is obvious hat, there are some off days between two mentioned days. How can I find that from PM?
Do you have idea?

[amosbatto]

I want to calculate the differences between to dates. (for example DEL_INIT_DATE and DEL_FINISH_DATE)
The problem is that it does not consider the off time, off days and vacations. So, I can not have the real time.

If you import my extraFunctions plugin, then you can use the PMFTaskDuration() function to calculate the time based on the user's calendar.