This trigger code doesn't work because you didn't create an ENDDATE field in your PMT_LEAVE table:
Code: Select allif (isset(@@action) and @@action == 'QUERY') {
@@action = ''; //reset action
$username = @@empUser;
$sql = "SELECT FROMDATE, ENDDATE, NUMLVEDAYS
FROM PMT_LEAVE WHERE EMPUSER = '$username'";
@@leaveDetailsGrid = executeQuery($sql);
$formId = '4216425755a4c39fdf13ca5096948423'; //set to DynaForm's ID
PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $formId);
}
You need to add a trigger to your Leave process to calculate the end date. For example:
Code: Select allif (!empty(@@startDate) and !empty(@@numberDays)) {
$start = strtotime(@@startDate);
$end = strtotime('+ '. @@numberDays .' days', $start);
@@endDate = date('Y-m-d', $end);
}
Where startDate is a datetime field and numberDays is a text field limited to integers with a validation rule.
You can also add week days like this:
Code: Select all $end = strtotime('+ '. @@numberDays .' weekdays', $start);
Then add endDate to your Report Table.
As for editing the contents in your table, you are going to need a separate PM Table, because you should NOT write directly to your PMT_LEAVE table because its records are going to be overwritten when cases are updated in your Leave processes.
Basically, you are going to need a separate PM Table to hold data that can be updated by two different processes and you are going to need an ID field to identify each record, so you can keep track of what record you are changing with each process. I recommend that you create an auto-increment primary key field in your PM Table so the ID is automatically created for you when a new record is inserted in the table. The grid can store this ID in a hidden field. When the grid is submitted, then you look up the specific record and update it with executeQuery() in a trigger. If the hidden field is empty then you know to add a new record to the table.
For example, lets say that you create a PM Table named "PMT_LEAVE_DATA" which contains the following fields:
ID_LEAVE (auto-increment primary field, integer)
EMPUSER (var_char),
FROMDATE (date),
ENDDATE (date),
NUMLVEDAYS (integer)
Then, your trigger code would be something like this:
Code: Select all//if looking up the records:
if (isset(@@action) and @@action == 'QUERY') {
@@action = ''; //reset action
$username = @@empUser;
$sql = "SELECT ID_LEAVE, FROMDATE, ENDDATE, NUMLVEDAYS
FROM PMT_LEAVE_DATA WHERE EMPUSER = '$username'";
@=leaveDetailsGrid = executeQuery($sql);
$formId = '4216425755a4c39fdf13ca5096948423'; //set to DynaForm's ID
PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $formId);
}
//if submitting data
elseif (isset(@@action) and @@action == '' and !empty(@@empUser)) {
$empUser = @@empUser;
$aLeaveIDs = array();
foreach (@=leaveDetailsGrid as $aRow) {
//if adding a new row:
if (empty($aRow['ID_LEAVE'])) {
//don't include the ID_LEAVE field because it will automatically be inserted by MySQL:
$sql = "INSERT INTO PMT_LEAVE_DATA ( EMPUSER, FROMDATE, ENDDATE, NUMLVEDAYS )
VALUES ( '$empuser', '{$aRow['FROMDATE']}', '{$aRow['ENDDATE']}', {$aRow['NUMLVEDAYS']} )";
@@insertResult = executeQuery($sql);
}
else { //if an existing row, then update it in the database:
$aLeaveIDs[] = $aRow['ID_LEAVE'];
$sql = "UPDATE PMT_LEAVE_DATA SET FROMDATE='{$aRow['FROMDATE']}',
ENDDATE='{$aRow['ENDDATE']}', NUMLVEDAYS={$aRow['NUMLVEDAYS']}
WHERE ID_LEAVE={$aRow['ID_LEAVE']} ";
@@updateResult = executeQuery($sql);
}
}
//delete any records for the user which don't appear in the grid:
//(these are the records that were deleted in the grid)
$validIDs = implode(', ', $aRow['ID_LEAVE']);
$sql = "DELETE FROM PMT_LEAVE_DATA WHERE EMPUSER='$empUser' AND
ID_LEAVE NOT IN ($validIDs)";
@@deleteResult = executeQuery($sql);
}
You probably will have to debug this code, but it gives you an idea how to proceed.
- By Peterparker87