[erdalayan]

Hi,

I have created a dynaform including available days the user have, days requested and days rest. I want the form keep uptodate the the number of days that the user has. Can anyone suggest a solution for this?

Best...

Erdal Ayan

[amosbatto]

You can manually create a PM Table where you keep track of this information for each user.

Then, you can use executeQuery() in a trigger to retrieve the information to display in the form.
For example, if your PM Table is named "USERS_DAYS_OFF" and it contains the fields:
- USERNAME
- DAYS_AVAILABLE
- DAYS_REQUESTED
- DAYS_RESTED
And you have the following associated variables for the fields in your DynaForm:
- daysAvailable
- daysRequested
- daysRested

Then, create the following trigger which is set to execute before the DynaForm:

Code: Select all

$username = @@USR_USERNAME; //username of the current logged-in user
$sql = "SELECT DAYS_AVAILABLE, DAYS_REQUESTED, DAYS_RESTED 
   FROM PMT_USERS_DAYS_OFF WHERE USERNAME='$username'";
$result = executeQuery($sql);
if (empty($result)) {
    throw new Exception("There is no record in the PMT_USERS_DAYS_OFF table for '$username'.");
}
@@daysAvailable = $result[1]['DAYS_AVAILABLE'];
@@daysRequested = $result[1]['DAYS_REQUESTED'];
@@daysRested = $result[1]['DAYS_RESTED'];

Then, you can create another trigger which is executed after the DynaForm which updates the PM Table based on what is entered in the DynaForm:

Code: Select all

$available = (int) @@daysAvailable;
$requested = (int) @@daysRequested;
$rested = (int) @@daysRested;
$username = @@USR_USERNAME;

$sql = "UPDATE PMT_USERS_DAYS_OFF SET DAYS_AVAILABLE='$available', 
   DAYS_REQUESTED='$requested', DAYS_RESTED='$rested'  
   WHERE USERNAME='$username'";
@@ret = $executeQuery($sql);

PS: If you want to calculate days and hours, based on a configured calendar in ProcessMaker, see the ExtraFunctions plugin.

[erdalayan]

Dear Amosbatto,

I have followed the instructions you provided but I got an error. What is the mistake I did?

[amosbatto]

The definition of your PM Table is wrong. The DAYS_AVAILABLE field should not be a primary key field, because the same value can be used in multiple rows in the table. The USERNAME field should be the primary key, which means that you only have one row per user.

Also, you need to manually create a row for each user in your PM Table (or you need to add code to your trigger to create a row for the user, if a row doesn't already exist.

Also, if you want your form to automatically calculate the "Number of days rested", then you can add the following formula to that field:
daysAvailable - daysRequested

If you can't figure it out, then export your process and your PM Table and post them here so I can look at them.

[erdalayan]

Dear Amos,

thank you for your responses. I have adjusted the mistakes but still getting the error when I activate the pm tables and triggers. Actually I did not get how to create pm tables for each user. I have exported the process and pm table and you can find them in the attached files. I would appreciate if you have a look when you are available.

Best...
Erdal Ayan

[amosbatto]

I have adjusted the mistakes but still getting the error when I activate the pm tables and triggers. Actually I did not get how to create pm tables for each user. I have exported the process and pm table and you can find them in the attached files. I would appreciate if you have a look when you are available.

You forgot to change the primary key field in your PM Table. I added an auto-incrementing ID field to your table to be your primary key. I also changed the other fields to be integers.

I changed the first trigger to auto-insert rows in the table because you didn't have any data in your table:

Code: Select all

$username = @@USR_USERNAME; //username of the current logged-in user
$sql = "SELECT DAYS_AVAILABLE, DAYS_REQUESTED, DAYS_RESTED 
   FROM PMT_USERS_DAYS_OFF WHERE USERNAME='$username'";
$result = executeQuery($sql);

if (empty($result)) {
	//if user doesn't have record in the table, then insert one with default values:
	@@daysAvailable = 10;
	@@daysRequested = 0;
	@@daysRested    = 10;
	$insert = "INSERT INTO PMT_USERS_DAYS_OFF 
		(USERNAME, DAYS_AVAILABLE, DAYS_REQUESTED, DAYS_RESTED) 
		VALUES ('$username', 10, 0, 10)";
	@@retInsert = executeQuery($insert);
	$g = new \G();
	$g->SendMessageText("Added record for user '$username' in the PMT_USERS_DAYS_OFF table",
		'INFO');
}
else {
	@@daysAvailable = $result[1]['DAYS_AVAILABLE'];
	@@daysRequested = $result[1]['DAYS_REQUESTED'];
	@@daysRested    = $result[1]['DAYS_RESTED'];
}

I found two errors in my second trigger. I changed $executeQuery to executeQuery and I added single quotes around '$username'. You will catch these sorts of errors if you turn on the ProcessMaker debugger in the process properties and if you set debug=1 in your env.ini file.

Code: Select all

//use @% to convert to integer and prevent SQL injection attacks
$available = @%daysAvailable; 
$requested = @%daysRequested;
$rested    = @%daysRested;
$username  = @@USR_USERNAME;

$sql = "UPDATE PMT_USERS_DAYS_OFF 
	SET DAYS_AVAILABLE=$available, DAYS_REQUESTED=$requested, DAYS_RESTED=$rested  
	WHERE USERNAME='$username'";
@@ret = executeQuery($sql);

[erdalayan]

Dear Amosbatto,

thank you for your checking the code and recommendations. Actually, the codes that you created work fine but the value in "the number of you have" is not updated after the first case and still keeps the value "20". but it should provide new value for the user. For example, lets say the user user 5 days out of 20, the next time the "the number of you have" should provide "15" but still keeps "20". How can I solve that problem?




Best...
Erdal Ayan

[erdalayan]

Hi,

I have updated the PM table and added one more row called daysCurrent in order to keep the updated date after the case. I have also updated the triggers accordingly but still there is not a solution...

Code: Select all

$username = @@USR_USERNAME; //username of the current logged-in user
$sql = "SELECT DAYS_AVAILABLE, DAYS_CURRENT, DAYS_REQUESTED, DAYS_RESTED 
   FROM PMT_USERS_DAYS_OFF WHERE USERNAME='$username'";
$result = executeQuery($sql);

if (empty($result)) {
	//if user doesn't have record in the table, then insert one with default values:
	@@daysAvailable = 20;
	@@daysCurrent 	= 0;
	@@daysRequested = 0;
	@@daysRested    = 0;
	$insert = "INSERT INTO PMT_USERS_DAYS_OFF 
		(USERNAME, DAYS_AVAILABLE, DAYS_CURRENT, DAYS_REQUESTED, DAYS_RESTED) 
		VALUES ('$username', 20, 0, 0, 0)";
	@@retInsert = executeQuery($insert);
	$g = new \G();
	$g->SendMessageText("Added record for user '$username' in the PMT_USERS_DAYS_OFF table",
		'INFO');
}
else {
	@@daysAvailable = $result[1]['DAYS_AVAILABLE'];
	@@daysCurrent   = $result[1]['DAYS_CURRENT'];
	@@daysRequested = $result[1]['DAYS_REQUESTED'];
	@@daysRested    = $result[1]['DAYS_RESTED'];
}

Code: Select all

//use @% to convert to integer and prevent SQL injection attacks
$available = @%daysAvailable;
$current   = @%daysCurrent;
$requested = @%daysRequested;
$rested    = @%daysRested;
$username  = @@USR_USERNAME;

$sql = "UPDATE PMT_USERS_DAYS_OFF 
	SET DAYS_AVAILABLE=$available, DAYS_CURRENT=$current, DAYS_REQUESTED=$requested, DAYS_RESTED=$rested  
	WHERE USERNAME='$username'";
@@ret = executeQuery($sql);

[amosbatto]

Actually, the codes that you created work fine but the value in "the number of you have" is not updated after the first case and still keeps the value "20". but it should provide new value for the user. For example, lets say the user user 5 days out of 20, the next time the "the number of you have" should provide "15" but still keeps "20". How can I solve that problem?

If that is what you want, then you should set daysAvailable to the value of daysRested like this:

Code: Select all

//use @% to convert to integer and prevent SQL injection attacks
$available = @%daysRested; 
$requested = 0;
$rested    = @%daysRested;
$username  = @@USR_USERNAME;

$sql = "UPDATE PMT_USERS_DAYS_OFF 
	SET DAYS_AVAILABLE=$available, DAYS_REQUESTED=$requested, DAYS_RESTED=$rested  
	WHERE USERNAME='$username'";
@@ret = executeQuery($sql);

If you have a supervisor who approves the requested days, then fire this trigger after the decision from the supervisor. For example, if you have a "supervisorDecision" dropdown with the option "approved", then your trigger would be:

Code: Select all

if (@@supervisorDecision == 'approved') {
   $available = @%daysRested; 
   $requested = 0;
   $rested    = @%daysRested;
   $username  = @@USR_USERNAME;
}
else { //supervisor denied the request, so undo the request:
    $available = @%daysAvailable; 
    $requested = 0;
    $rested    = $available;
}

$username  = @@USR_USERNAME;
$sql = "UPDATE PMT_USERS_DAYS_OFF 
	SET DAYS_AVAILABLE=$available, DAYS_REQUESTED=$requested, DAYS_RESTED=$rested  
	WHERE USERNAME='$username'";
@@ret = executeQuery($sql);

[erdalayan]

Dear Amosbatto,

thank you for your contribution. The codes that you revised worked well.

Actually, I did not define an "approval" button on my dynaform. I thought the supervisor may edit the form at any time when the case sent to him. But creating an approval would be fine idea. I was just wondering if should define a separate trigger including your second code block after defining an approval button on my dynaform.

Thanks in advance.
Best...
Erdal Ayan

[amosbatto]

Actually, I did not define an "approval" button on my dynaform. I thought the supervisor may edit the form at any time when the case sent to him. But creating an approval would be fine idea. I was just wondering if should define a separate trigger including your second code block after defining an approval button on my dynaform.

If you want a supervisor to approve the requested days, you will have to add JavaScript to your Dynaform. See this example: https://wiki.processmaker.com/3.0/Submi ... ancel_Case

It is much easier to use a dropdown with the options "Approved" and "Rejected".