You can create a separate process to clone cases. Add a Dynaform with a "selectCase" field to enter the unique ID of the case to clone. Then, the following trigger can be used to clone the case:
Code: Select allif (empty(@@selectedCase)) {
goto triggerEnd;
}
function esc($s) {
return mysql_real_escape_string($s);
}
$caseId = @@selectedCase;
$g = new G();
$cloneCaseId = $g->generateUniqueID();
//get next case number:
$sql = "SELECT ID FROM APP_SEQUENCE";
$lastCaseNo = executeQuery($sql)[1]['ID']
$nextCaseNo = $lastCaseNo + 1;
//clone the APPLICATION record:
$sql = "SELECT * FROM APPLICATION WHERE APP_UID='$caseId'";
$aRows = executeQuery($sql);
$aRow = $aRows[1];
$cloneTitle = esc('#'.$nextCaseNo.' (cloned #'.$aRow['APP_TITLE'].')');
$appData = esc($aRow['APP_DATA']);
$desc = esc($aRow['APP_DESCRIPTION']);
$sql = "INSERT INTO APPLICATION (
APP_UID,
APP_TITLE,
APP_DESCRIPTION,
APP_NUMBER,
APP_PARENT,
APP_STATUS,
APP_STATUS_ID,
PRO_UID,
APP_PROC_STATUS,
APP_PROC_CODE,
APP_PARALLEL,
APP_INIT_USER,
APP_CUR_USER,
APP_CREATE_DATE,
APP_INIT_DATE,
APP_FINISH_DATE,
APP_UPDATE_DATE,
APP_DATA,
APP_PIN,
APP_DURATION,
APP_DELAY_DURATION,
APP_DRIVE_FOLDER_UID,
APP_ROUTING_DATA
)
VALUES (
'$cloneCaseId',
'$cloneTitle',
'$desc',
$nextCaseNo,
'{$aRow['APP_PARENT']}',
'{$aRow['APP_STATUS']}',
{$aRow['APP_STATUS_ID']},
'{$aRow['PRO_UID']}',
'{$aRow['APP_PROC_STATUS']}',
'{$aRow['APP_PROC_CODE']}',
'{$aRow['APP_PARALLEL']}',
'{$aRow['APP_INIT_USER']}',
'{$aRow['APP_CUR_USER']}',
'{$aRow['APP_CREATE_DATE']}',
'{$aRow['APP_INIT_DATE']}',
'{$aRow['APP_CUR_USER']}',
'{$aRow['APP_FINISH_DATE']}',
'{$aRow['APP_UPDATE_DATE']}',
'$appData',
'{$aRow['APP_PIN']}',
{$aRow['APP_DURATION']},
{$aRow['APP_DELAY_DURATION']},
'{$aRow['APP_DRIVE_FOLDER_UID']}',
'{$aRow['APP_ROUTING_DATA']}'
)";
$result = executeQuery($sql);
//set next case No:
$sql = "UPDATE APP_SEQUENCE SET ID=$nextCaseNo";
executeQuery($sql);
//clone APP_DELEGATION records:
$sql = "SELECT * FROM APP_DELEGATION WHERE APP_UID='$caseId' ORDER BY DEL_INDEX";
$aRows = executeQuery($sql);
foreach ($aRows as $aRow) {
$delData = esc($aRow['DEL_DATA']);
$sql = "INSERT INTO APP_DELEGATION (
APP_UID,
DEL_INDEX,
DELEGATION_ID,
APP_NUMBER,
DEL_PREVIOUS,
DEL_LAST_INDEX,
PRO_UID,
TAS_UID,
USR_UID,
DEL_TYPE,
DEL_THREAD,
DEL_THREAD_STATUS,
DEL_PRIORITY,
DEL_DELEGATE_DATE,
DEL_INIT_DATE,
DEL_FINISH_DATE,
DEL_TASK_DUE_DATE,
DEL_RISK_DATE,
DEL_DURATION,
DEL_QUEUE_DURATION,
DEL_DELAY_DURATION,
DEL_STARTED,
DEL_FINISHED,
DEL_DELAYED,
DEL_DATA,
APP_OVERDUE_PERCENTAGE,
USR_ID,
PRO_ID,
TAS_ID
)
VALUES (
'$cloneCaseId',
{$aRow['DEL_INDEX']},
{$aRow['DELEGATION_ID']},
{$aRow['APP_NUMBER']},
{$aRow['DEL_PREVIOUS']},
{$aRow['DEL_LAST_INDEX']},
'{$aRow['PRO_UID']}',
'{$aRow['TAS_UID']}',
'{$aRow['USR_UID']}',
'{$aRow['DEL_TYPE']}',
{$aRow['DEL_THREAD']},
'{$aRow['DEL_THREAD_STATUS']}',
'{$aRow['DEL_PRIORITY']}',
'{$aRow['DEL_DELEGATE_DATE']}',
'{$aRow['DEL_INIT_DATE']}',
'{$aRow['DEL_FINISH_DATE']}',
'{$aRow['DEL_TASK_DUE_DATE']}',
'{$aRow['DEL_RISK_DATE']}',
{$aRow['DEL_DURATION']},
{$aRow['DEL_QUEUE_DURATION']},
{$aRow['DEL_DELAY_DURATION']},
{$aRow['DEL_STARTED']},
{$aRow['DEL_FINISHED']},
{$aRow['DEL_DELAYED']},
'$delData',
{$aRow['APP_OVERDUE_PERCENTAGE']},
{$aRow['USR_ID']},
{$aRow['PRO_ID']},
{$aRow['TAS_ID']}
)";
executeQuery($sql);
}
//clone APP_THREAD records:
$sql = "SELECT * FROM APP_THREAD WHERE APP_UID='$caseId'";
$aRows = executeQuery($sql);
foreach ($aRows as $aRow) {
$sql = "INSERT INTO APP_THREAD (
APP_UID,
APP_THREAD_INDEX,
APP_THREAD_PARENT,
APP_THREAD_STATUS,
DEL_INDEX
)
VALUES (
'$cloneCaseId',
{$aRow['APP_THREAD_INDEX']},
{$aRow['APP_THREAD_PARENT']},
'{$aRow['APP_THREAD_STATUS']}',
{$aRow['DEL_INDEX']}
)";
executeQuery($sql);
}
//clone APP_DOCUMENT records:
$sql = "SELECT * FROM APP_DOCUMENT WHERE APP_UID='$caseId'";
$aRows = executeQuery($sql);
foreach ($aRows as $aRow) {
$cloneAppDocUid = $g->generateUniqueID();
$filename = esc($aRow['APP_DOC_FILENAME']);
$fileComment = esc($aRow['APP_DOC_COMMENT']);
$sql = "INSERT INTO APP_THREAD (
APP_DOC_UID,
APP_DOC_FILENAME,
APP_DOC_TITLE,
APP_DOC_COMMENT,
DOC_VERSION,
APP_UID,
DEL_INDEX,
DOC_UID,
USR_UID,
APP_DOC_TYPE,
APP_DOC_CREATE_DATE,
APP_DOC_INDEX,
FOLDER_UID,
APP_DOC_PLUGIN,
APP_DOC_TAGS,
APP_DOC_STATUS,
APP_DOC_STATUS_DATE,
APP_DOC_FIELDNAME,
APP_DOC_DRIVE_DOWNLOAD,
SYNC_WITH_DRIVE,
SYNC_PERMISSIONS
)
VALUES (
'$cloneAppDocUid',
'$filename',
'{$aRow['APP_DOC_TITLE']}',
'$fileComment',
{$aRow['DOC_VERSION']},
'$cloneCaseId',
'{$aRow['DEL_INDEX']}',
'{$aRow['DOC_UID']}',
'{$aRow['USR_UID']}',
'{$aRow['APP_DOC_TYPE']}',
'{$aRow['APP_DOC_CREATE_DATE']}',
{$aRow['APP_DOC_INDEX']},
'{$aRow['FOLDER_UID']}',
'{$aRow['APP_DOC_PLUGIN']}',
'{$aRow['APP_DOC_TAGS']}',
'{$aRow['APP_DOC_STATUS']}',
'{$aRow['APP_DOC_STATUS_DATE']}',
'{$aRow['APP_DOC_FIELDNAME']}',
'{$aRow['APP_DOC_DRIVE_DOWNLOAD']}',
'{$aRow['SYNC_WITH_DRIVE']}',
'{$aRow['SYNC_PERMISSIONS']}'
)";
executeQuery($sql);
$basePath = PATH_DOCUMENT. $g->getPathFromUID($caseId);
$cloneBasePath = PATH_DOCUMENT. $g->getPathFromUID($cloneCaseId);
if (!file_exists($cloneBasePath)) {
if (!mkdir($cloneBasePath .PATH_SEP. 'outdocs', 0755, true)) {
throw new Exception("Error creating directories $cloneBasePath");
}
}
if ($aRow['APP_DOC_TYPE'] != 'OUTPUT') {
$basePath .= PATH_SEP . 'outdocs' . PATH_SEP;
$cloneBasePath .= PATH_SEP . 'outdocs' . PATH_SEP;
$oldPath = $basePath . $aRow['APP_DOC_UID'] .'_'. $aRow['DOC_VERSION'];
$clonePath = $cloneBasePath . $cloneAppDocUid .'_'. $aRow['DOC_VERSION'];
foreach (glob($oldPath.'.*') as $filePath) {
$ext = pathinfo($filePath, PATHINFO_EXTENSION);
if (!copy($filePath, $clonePath.'.'.$ext)) {
throw new Exception("Error copying from $filePath to $clonePath.$ext");
}
}
}
else { //if an Input Document or attached file
$ext = pathinfo($aRow['APP_DOC_FILENAME'], PATHINFO_EXTENSION);
$oldPath = $basePath .PATH_SEP. $aRow['APP_DOC_UID'] .'_'. $aRow['DOC_VERSION'] .'.'.$ext;
$clonePath = $cloneBasePath .PATH_SEP. $cloneAppDocUid .'_'. $aRow['DOC_VERSION'] .'.'.$ext;
if (!copy($oldPath, $clonePath)) {
throw new Exception("Error copying from $oldPath to $newPath");
}
}
//copy CONTENT records for document
$contentQuery = "SELECT * FROM CONTENT WHERE CON_ID='$aRow['APP_DOC_UID']}'
AND CON_PARENT='{$aRow['DOC_VERSION']}'";
$aContentRows = executeQuery($contentQuery);
foreach ($aContentRows as $aContentRow) {
$conValue = esc($aContentRow['CON_VALUE'])
$contentInsert = "INSERT INTO CONTENT (
CON_CATEGORY,
CON_PARENT,
CON_ID,
CON_LANG,
CON_VALUE
)
VALUES (
'{$aContentRow['CON_CATEGORY']}',
'{$aContentRow['CON_PARENT']}',
'$cloneAppDocUid',
'{$aContentRow['CON_LANG']}',
'$conValue'
)";
executeQuery($contentInsert);
}
}
//clone APP_CACHE_VIEW records:
$sql = "SELECT * FROM APP_CACHE_VIEW WHERE APP_UID='$caseId' ORDER BY DEL_INDEX";
$aRows = executeQuery($sql);
foreach ($aRows as $aRow) {
$cloneTitle = esc('#'.$nextCaseNo.' (cloned #'.$aRow['APP_TITLE'].')');
$processTitle = esc($aRow['APP_PRO_TITLE']);
$taskTitle = $aRow['APP_TAS_TITLE']);
$currentUser = esc(['APP_CURRENT_USER']);
$previousUser = esc('APP_DEL_PREVIOUS_USER']);
$sql = "INSERT INTO APP_CACHE_VIEW (
APP_UID,
DEL_INDEX,
DEL_LAST_INDEX,
APP_NUMBER,
APP_STATUS,
USR_UID,
PREVIOUS_USR_UID,
TAS_UID,
PRO_UID,
DEL_DELEGATE_DATE,
DEL_INIT_DATE,
DEL_FINISH_DATE,
DEL_TASK_DUE_DATE,
DEL_RISK_DATE,
DEL_THREAD_STATUS,
APP_THREAD_STATUS,
APP_TITLE,
APP_PRO_TITLE,
APP_TAS_TITLE,
APP_CURRENT_USER,
APP_DEL_PREVIOUS_USER,
DEL_PRIORITY,
DEL_DURATION,
DEL_QUEUE_DURATION,
DEL_DELAY_DURATION,
DEL_STARTED,
DEL_FINISHED,
DEL_DELAYED,
APP_CREATE_DATE,
APP_FINISH_DATE,
APP_UPDATE_DATE,
APP_OVERDUE_PERCENTAGE
)
VALUES (
'$cloneCaseId',
{$aRow['DEL_INDEX']},
{$aRow['DEL_LAST_INDEX']},
{$aRow['APP_NUMBER']},
'{$aRow['APP_STATUS']}',
'{$aRow['USR_UID']}',
'{$aRow['PREVIOUS_USR_UID']}',
'{$aRow['TAS_UID']}',
'{$aRow['PRO_UID']}',
'{$aRow['DEL_DELEGATE_DATE']}',
'{$aRow['DEL_INIT_DATE']}',
'{$aRow['DEL_FINISH_DATE']}',
'{$aRow['DEL_TASK_DUE_DATE']}',
'{$aRow['DEL_RISK_DATE']}',
'{$aRow['DEL_THREAD_STATUS']}',
'{$aRow['APP_THREAD_STATUS']}',
'$cloneTitle',
'$processTitle',
'$taskTitle',
'$currentUser',
'previousUser',
'{$aRow['DEL_PRIORITY']}',
{$aRow['DEL_DURATION']},
{$aRow['DEL_QUEUE_DURATION']},
{$aRow['DEL_DELAY_DURATION']},
{$aRow['DEL_STARTED']},
{$aRow['DEL_FINISHED']},
{$aRow['DEL_DELAYED']},
'{$aRow['APP_CREATE_DATE']}',
'{$aRow['APP_FINISH_DATE']}',
'{$aRow['APP_UPDATE_DATE']}',
{$aRow['APP_OVERDUE_PERCENTAGE']}
)";
executeQuery($sql);
}
//clone APP_ASSIGN_SELF_SERVICE_VALUE records:
$sql = "SELECT * FROM APP_ASSIGN_SELF_SERVICE_VALUE WHERE APP_UID='$caseId'";
$aRows = executeQuery($sql);
foreach ($aRows as $aRow) {
$sql = "INSERT INTO APP_ASSIGN_SELF_SERVICE_VALUE
(APP_UID, DEL_INDEX, PRO_UID, TAS_UID, GRP_UID)
VALUES ('$cloneCaseId', {$aRow['DEL_INDEX']}, '{$aRow['PRO_UID']}',
'{$aRow['TAS_UID']}', '{$aRow['GRP_UID']}')";
}
//copy all the other APP_* tables
triggerEnd:
I haven't finished this trigger and haven't debugged it, but it covers the most important tables holding information about cases. If I have time in the future, I'll finish it, but you can use it to get started. For example, if you want to clone paused cases, you need to copy the records for the case found in the APP_DELAY table. This code is for version 3.2.1. If using another version, you may need to delete some of the new database fields which were added in recent versions of ProcessMaker.
- By Peterparker87