[akshaydange]

hello,
i want to generate new case depending upon the given date.let me explain what i want to do.

I have dynaform where user fills the form for document compliance and select the validity date of document. in my dynaform there are six document and their validity date. I used trigger for generate a new case but i want to generate a six new case for six validity date. if two dates are same then only one case will be generated for that date. Following is my code, what should i do??

Code: Select all

$process = @@PROCESS;
$task = '97755241357b30f9c6fe366028595037';
$case_id = @@APPLICATION;

$validityDate1 = date('Y-m-d',strtotime(@#validityDate)) . ' 18:00:00';
$validityDate2 = date('Y-m-d',strtotime(@#validityDate2)) . ' 18:00:00';
$validityDate3 = date('Y-m-d',strtotime(@#validityDate3)) . ' 18:00:00';
$validityDate4 = date('Y-m-d',strtotime(@#validityDate4)) . ' 18:00:00';
$validityDate5 = date('Y-m-d',strtotime(@#validityDate5)) . ' 18:00:00';
$validityDate6 = date('Y-m-d',strtotime(@#validityDate6)) . ' 18:00:00';
																
$caseUID = PMFNewCase($process, @@USER_LOGGED, $task, $case_id, $validityDate1);

//$query = "SELECT vehicle_no FROM vehicle_master WHERE vehicle_no='$vehicle_no'";
//$array_id = executeQuery($query);
//$vehicle_number = $array_id[1]["vehicle_no"]; 
						
	if ($caseUID) {
		
		executeQuery("UPDATE APPLICATION SET APP_STATUS='TO_DO' WHERE APP_UID='$caseUID'");
		executeQuery("UPDATE APP_DELEGATION SET DEL_TASK_DUE_DATE='$validityDate1' WHERE APP_UID='$caseUID' AND PRO_UID='$process' AND TAS_UID='$task' ORDER BY DEL_INDEX DESC LIMIT 1");
		
	}

[amosbatto]

If you are using PM2, then you can use the Case Scheduler. If you are using PM3, this might work with the new BPMN processes, but I haven't tested it. Otherwise, you need to import the attached classic process and design your process with the old classic style.
Then in your process, go to "Case Scheduler" and click on "New" and create a case which is executed "One time only".

Then, go to the PM database and copy the record which was created in the CASE_SCHEDULER table. You need to insert new records in that same table with the same values, but change the date.

Your trigger code will look something like this:

Code: Select all

$processId = @@PROCESS;
$taskId    = '97755241357b30f9c6fe366028595037';
$caseId    = @@APPLICATION;
$username  = 'admin'; //set to username assigned to first task in process

//lookup user's ID and MD5 hash of password:
$aResult = executeQuery("SELECT USR_UID, USR_PASSWORD FROM RBAC_USERS WHERE USR_USERNAME='$username');
if (empty($aResult)) {
   die("Unable to find user in RBAC_USERS");
}
$userId   = $aResult[1]['USR_UID'];
$password = $aResult[1]['USR_PASSWORD'];

$aValidityDates = array();
$aValidityDates[] = date('Y-m-d',strtotime(@@validityDate)) . ' 18:00:00';
$aValidityDates[] = date('Y-m-d',strtotime(@@validityDate2)) . ' 18:00:00';
$aValidityDates[] = date('Y-m-d',strtotime(@@validityDate3)) . ' 18:00:00';
$aValidityDates[] = date('Y-m-d',strtotime(@@validityDate4)) . ' 18:00:00';
$aValidityDates[] = date('Y-m-d',strtotime(@@validityDate5)) . ' 18:00:00';
$aValidityDates[] = date('Y-m-d',strtotime(@@validityDate6)) . ' 18:00:00';
//eliminate duplicate dates:
$aValidityDates = array_unique($aValidityDates);                          

foreach ($aValidityDates as $date) {
    $aSch = array(
        'SCH_UID'              => G::generateUniqueID(),
        'SCH_DEL_USER_NAME'    => $username, 
        'SCH_DEL_USER_PASS'    => $password,                
        'SCH_DEL_USER_UID'     => $userId,
        'SCH_NAME'             => 'Scheduled case ' . $date,
        'PRO_UID'              => $processId,
        'TAS_UID'              => $taskId,
        'SCH_TIME_NEXT_RUN'    => $date,
        'SCH_LAST_RUN_TIME'    => null,
        'SCH_STATE'            => 'ACTIVE',
        'SCH_LAST_STATE'       => 'CREATED',
        'USR_UID'              => $userId,
        'SCH_OPTION'           => 4,
        'SCH_START_TIME'       => date('Y-m-d H:i:s'),
        'SCH_START_DATE'       => date('Y-m-d H:i:s'),
        'SCH_DAYS_PERFORM_TASK'=> '',
        'SCH_EVERY_DAYS'       => 0,
        'SCH_WEEK_DAYS'        => '0|0|0|0|0|0|0',
        'SCH_START_DAY'        => '',
        'SCH_MONTHS'           => '0|0|0|0|0|0|0|0|0|0|0|0',
        'SCH_END_DATE'         => null,
        'SCH_REPEAT_EVERY'     => '',
        'SCH_REPEAT_UNTIL'     => '',
        'SCH_REPEAT_STOP_IF_RUNNING' => '',
        'SCH_EXECUTION_DATE'   => 0,
        'CASE_SH_PLUGIN_UID'   => null
   );
   $sql = 'INSERT INTO CASE_SCHEDULER (' . implode(', ', array_keys($aSch)) . 
      ') VALUES (' . implode(', ', $aSch) . ')';                                            
	executeQuery($sql);
}

Then set up a cron job or Windows scheduled task to periodically execute cron.php.

[akshaydange]

Thank u..

I executed above trigger but problem is that i have error in sql. The error is

[Native Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'case 2016-09-05 18:00:00, 44599844257b30f217c6329055449252, 97755241357b30f9c6fe' at line 1]

[amosbatto]

Oh I see the problem. SQL needs to know if it is a string, number, etc.
Try it this way:

Code: Select all

$processId = @@PROCESS;
$taskId    = '97755241357b30f9c6fe366028595037';
$caseId    = @@APPLICATION;
$username  = 'admin'; //set to username assigned to first task in process

//lookup user's ID and MD5 hash of password:
$aResult = executeQuery("SELECT USR_UID, USR_PASSWORD FROM RBAC_USERS WHERE USR_USERNAME='$username');
if (empty($aResult)) {
   die("Unable to find user in RBAC_USERS");
}
$userId   = $aResult[1]['USR_UID'];
$password = $aResult[1]['USR_PASSWORD'];

$aValidityDates = array();
$aValidityDates[] = date('Y-m-d',strtotime(@@validityDate)) . ' 18:00:00';
$aValidityDates[] = date('Y-m-d',strtotime(@@validityDate2)) . ' 18:00:00';
$aValidityDates[] = date('Y-m-d',strtotime(@@validityDate3)) . ' 18:00:00';
$aValidityDates[] = date('Y-m-d',strtotime(@@validityDate4)) . ' 18:00:00';
$aValidityDates[] = date('Y-m-d',strtotime(@@validityDate5)) . ' 18:00:00';
$aValidityDates[] = date('Y-m-d',strtotime(@@validityDate6)) . ' 18:00:00';
//eliminate duplicate dates:
$aValidityDates = array_unique($aValidityDates);                         

foreach ($aValidityDates as $date) {
    $aSch = array(
        'SCH_UID'              => "'".G::generateUniqueID() ."'",
        'SCH_DEL_USER_NAME'    => "'".$username ."'",
        'SCH_DEL_USER_PASS'    => "'".$password ."'",               
        'SCH_DEL_USER_UID'     => "'".$userId ."'",
        'SCH_NAME'             => "'Scheduled case " . $date ."'",
        'PRO_UID'              => "'".$processId ."'",
        'TAS_UID'              => "'".$taskId ."'",
        'SCH_TIME_NEXT_RUN'    => "'".$date."'",
        'SCH_LAST_RUN_TIME'    => 'null',
        'SCH_STATE'            => "'ACTIVE'",
        'SCH_LAST_STATE'       => "'CREATED'",
        'USR_UID'              => "'".$userId ."'",
        'SCH_OPTION'           => 4,
        'SCH_START_TIME'       => "'".date('Y-m-d H:i:s') ."'",
        'SCH_START_DATE'       => "'".date('Y-m-d H:i:s') ."'",
        'SCH_DAYS_PERFORM_TASK'=> "''",
        'SCH_EVERY_DAYS'       => 0,
        'SCH_WEEK_DAYS'        => "'0|0|0|0|0|0|0'",
        'SCH_START_DAY'        => "''",
        'SCH_MONTHS'           => "'0|0|0|0|0|0|0|0|0|0|0|0'",
        'SCH_END_DATE'         => 'null',
        'SCH_REPEAT_EVERY'     => "''",
        'SCH_REPEAT_UNTIL'     => "''",
        'SCH_REPEAT_STOP_IF_RUNNING' => "''",
        'SCH_EXECUTION_DATE'   => 0,
        'CASE_SH_PLUGIN_UID'   => 'null'
   );
   $sql = 'INSERT INTO CASE_SCHEDULER (' . implode(', ', array_keys($aSch)) .
      ') VALUES (' . implode(', ', $aSch) . ')';                                           
   executeQuery($sql);
}

Anyway, it should be something like that. The reason why I told you to create a one time scheduled task is so that you could compare the records inserted by the trigger with what ProcessMaker created in the CASE_SCHEDULER in the database. They should be the same except for the SCH_TIME_NEXT_RUN field.