function insertUpdateProcessTracking($conn)
{
date_default_timezone_set("Asia/Dhaka");
$jUserId = $_REQUEST['jUserId'];
$language = $_REQUEST['language'];
$TrackingNo = strtoupper(trim($_POST['TrackingNo']));
$RegNo = strtoupper(trim($_POST['RegNo']));
$ProcessId = $_POST['ProcessId'];
$ParentProcessId = $_POST['ParentProcessId'];
$ProcUnitId = 2;
$aTextilePrefix = array('GBGDT', 'BGDT');
$aAnalyticalPrefix = array('GBGDA', 'BGDA', 'BGDF', 'GBGDF');
$aTextileSubConPrefix = array('GBGDA', 'BGDA', 'BGCT', 'GBGCT', 'BGDF', 'GBGDF', 'BGCA', 'GBGCA');
$aAnalyticalSubConPrefix = array('GBGDT', 'BGDT', 'BGCT', 'GBGCT', 'BGDF', 'GBGDF', 'BGCA', 'GBGCA');
switch ($ProcessId) {
case 23:
case 24:
if (!$TrackingNo) {
echo json_encode(array('msgType' => 'error', 'msg' => 'Job no can not be empty.'));
return;
}
if ($TrackingNo) {
$aRecExistData2 = getRecExistInProcByInwardNo($TrackingNo, $ProcessId);
// print_r($aRecExistData2);
// exit;
$OwnProTrackId = $aRecExistData2['ProTrackId'];
//$OwnInTime = $aRecExistData2['InTime'];
//$OwnOutTime = $aRecExistData2['OutTime'];
if ($OwnProTrackId) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This Job is scanned already.'));
return;
}
}
/* Update out time of parent */
if ($ParentProcessId) {
$aParentData = getParentProcessByInwardNo($TrackingNo, $ParentProcessId);
if (!$aParentData) {
echo json_encode(array('msgType' => 'error', 'msg' => 'Job is not scanned by the previous process.'));
return;
}
$ProTrackId = $aParentData['ProTrackId'];
$ParentInTime = $aParentData['InTime'];
$ParentBHold = $aParentData['bHold'];
if ($ParentBHold) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job is holded by previous process.'));
exit;
}
if (!$ProTrackId) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job is not scanned by Inward.'));
exit;
}
$aParentTimeDuration = getTimeDuration($ParentInTime);
$duration = $aParentTimeDuration['Duration'];
$txtDuration = $aParentTimeDuration['txtDuration'];
if ($ProTrackId) {
$sql2 = "UPDATE t_process_tracking SET OutTime = NOW(), Duration = {$duration}, TxtDuration = '{$txtDuration}', OutUserId = '{$jUserId}' WHERE ProTrackId = {$ProTrackId};";
$aQuery2 = array('command' => 'UPDATE', 'query' => $sql2, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo'), 'pk_values' => array("'" . $TrackingNo . "'"), 'bUseInsetId' => FALSE);
$aQuerys[] = $aQuery2;
}
}
/* Insert the current process */
$sql = "INSERT INTO t_process_tracking\n\t\t\t\t(TrackingNo, RegNo, ProcessId, InTime, EntryDate, YearId, MonthId, InUserId, ProcUnitId)\n\t\t\t\tVALUES ('{$TrackingNo}', '{$TrackingNo}', {$ProcessId}, NOW(), Now(), YEAR(NOW()), MONTH(NOW()), '{$jUserId}', 2);";
$aQuery1 = array('command' => 'INSERT', 'query' => $sql, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo', 'ProcessId'), 'pk_values' => array("'" . $TrackingNo . "'", $ProcessId), 'bUseInsetId' => TRUE);
$aQuerys[] = $aQuery1;
echo json_encode(exec_query($aQuerys, $jUserId, $language, FALSE, FALSE));
break;
case 25:
/* Start receiving from Photo Taking */
$TrackingNoPt = strtoupper($_POST['TrackingNoPt']);
if ($TrackingNoPt) {
$aRecExistData2 = getRecExistInProcByInwardNo($TrackingNoPt, $ParentProcessId);
if (!$aRecExistData2) {
echo json_encode(array('msgType' => 'error', 'msg' => 'Job is not scanned by the previous process.'));
return;
}
$ParentProTrackId = $aRecExistData2['ProTrackId'];
$ParentInTime = $aRecExistData2['InTime'];
$ParentOutTime = $aRecExistData2['OutTime'];
$ParentBHold = $aRecExistData2['bHold'];
if ($ParentBHold) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job is holded by previous process.'));
exit;
}
if ($ParentOutTime) {
echo json_encode(array('msgType' => 'error', 'msg' => 'Photo taking job is received already.'));
return;
}
$aParentTimeDuration = getTimeDuration($ParentInTime);
$duration = $aParentTimeDuration['Duration'];
$txtDuration = $aParentTimeDuration['txtDuration'];
if ($ParentProTrackId) {
$sql2 = "UPDATE t_process_tracking SET OutTime = NOW(), Duration = {$duration}, TxtDuration = '{$txtDuration}', OutUserId = '{$jUserId}' WHERE ProTrackId = {$ParentProTrackId};";
$aQuery2 = array('command' => 'UPDATE', 'query' => $sql2, 'sTable' => 't_process_tracking', 'pks' => array('ProTrackId'), 'pk_values' => array($ParentProTrackId), 'bUseInsetId' => FALSE);
$aQuerys[] = $aQuery2;
}
//Insert sample registration job
$aQuery1 = getProTrackInsertCmd($TrackingNoPt, $ProcessId, $jUserId, $ProcUnitId);
$aQuerys[] = $aQuery1;
echo json_encode(exec_query($aQuerys, $jUserId, $language, FALSE, FALSE));
return;
}
/* End receiving from Photo Taking */
/* Starting sample registration */
if (!$TrackingNo) {
echo json_encode(array('msgType' => 'error', 'msg' => 'Inward no can not be empty.'));
return;
}
if (!$RegNo) {
echo json_encode(array('msgType' => 'error', 'msg' => 'Registration no can not be empty.'));
return;
}
$aRecExistData3 = getRecExistInProcByInwardNo($TrackingNo, $ProcessId);
if (!$aRecExistData3) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This inward no is not availabe.'));
return;
}
if (getRecExistInProcByInwardNoAndRegNo($TrackingNo, $RegNo, $ProcessId)) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This Job is scanned already.'));
return;
}
if ($aRecExistData3['TrackingNo'] != $aRecExistData3['RegNo']) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This inward no (' . $aRecExistData3['TrackingNo'] . ') is already linked with Registration No (' . $aRecExistData3['RegNo'] . ').'));
return;
}
/* Update RegNo of ancestors */
$sql3 = "UPDATE t_process_tracking\n\t\t\t\tSET RegNo = '{$RegNo}'\n\t\t\t\tWHERE TrackingNo = '{$TrackingNo}' AND RegNo = '{$TrackingNo}';";
$aQuery3 = array('command' => 'UPDATE', 'query' => $sql3, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo', 'ProcessId'), 'pk_values' => array("'" . $TrackingNo . "'", $ProcessId), 'bUseInsetId' => TRUE);
$aQuerys[] = $aQuery3;
echo json_encode(exec_query($aQuerys, $jUserId, $language, FALSE, FALSE));
break;
case 31:
if ($_POST['RegNoWet']) {
$ParentProcessId = 27;
$RegNo = $_POST['RegNoWet'];
} else {
if ($_POST['RegNoMec']) {
$ParentProcessId = 30;
$RegNo = $_POST['RegNoMec'];
}
}
if (!$RegNo) {
echo json_encode(array('msgType' => 'error', 'msg' => 'Registration no can not be empty.'));
return;
}
/* Update out time of parent */
if ($ParentProcessId) {
$aParentData = getParentProcessByRegNo($RegNo, $ParentProcessId);
if (!$aParentData) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job has no previous record.'));
return;
}
$ProTrackId = $aParentData['ProTrackId'];
$ParentInTime = $aParentData['InTime'];
$ParentOutTime = $aParentData['OutTime'];
$ParentBHold = $aParentData['bHold'];
if ($ParentBHold) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job is holded by previous process.'));
exit;
}
if ($ParentOutTime) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job is received already.'));
return;
}
$aParentTimeDuration = getTimeDuration($ParentInTime);
$duration = $aParentTimeDuration['Duration'];
$txtDuration = $aParentTimeDuration['txtDuration'];
if ($ProTrackId) {
$sql2 = "UPDATE t_process_tracking SET OutTime = NOW(), Duration = {$duration}, TxtDuration = '{$txtDuration}', OutUserId = '{$jUserId}' WHERE ProTrackId = {$ProTrackId};";
$aQuery2 = array('command' => 'UPDATE', 'query' => $sql2, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo'), 'pk_values' => array("'" . $TrackingNo . "'"), 'bUseInsetId' => FALSE);
$aQuerys[] = $aQuery2;
}
}
$aRecExistData2 = getRecExistInProcByRegNo($RegNo, $ProcessId);
if (!$aRecExistData2) {
/* Insert the current process */
$sql = "INSERT INTO t_process_tracking\n\t\t\t\t\t(TrackingNo, RegNo, ProcessId, InTime, EntryDate, YearId, MonthId, InUserId, ProcUnitId)\n\t\t\t\t\tVALUES ('{$RegNo}', '{$RegNo}', {$ProcessId}, NOW(), Now(), YEAR(NOW()), MONTH(NOW()), '{$jUserId}', 2);";
$aQuery1 = array('command' => 'INSERT', 'query' => $sql, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo', 'ProcessId'), 'pk_values' => array("'" . $TrackingNo . "'", $ProcessId), 'bUseInsetId' => TRUE);
$aQuerys[] = $aQuery1;
}
if ($ParentProcessId) {
$aParentData2 = getInwardNoByRegNo($RegNo, $ParentProcessId);
$TrackingNo = $aParentData2['TrackingNo'];
if ($TrackingNo) {
/* Update RegNo of ancestors */
$sql3 = "UPDATE t_process_tracking\n\t\t\t\t\t\tSET TrackingNo = '{$TrackingNo}'\n\t\t\t\t\t\tWHERE TrackingNo = '{$RegNo}' AND RegNo = '{$RegNo}';";
$aQuery3 = array('command' => 'UPDATE', 'query' => $sql3, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo', 'ProcessId'), 'pk_values' => array("'" . $TrackingNo . "'", $ProcessId), 'bUseInsetId' => TRUE);
$aQuerys[] = $aQuery3;
}
}
echo json_encode(exec_query($aQuerys, $jUserId, $language, FALSE, FALSE));
break;
case 33:
if ($_POST['RegNoPhy']) {
$ParentProcessId = 31;
$RegNo = $_POST['RegNoPhy'];
} else {
if ($_POST['RegNoCol']) {
$ParentProcessId = 28;
$RegNo = $_POST['RegNoCol'];
} else {
if ($_POST['RegNoFib']) {
$ParentProcessId = 32;
$RegNo = $_POST['RegNoFib'];
}
}
}
if (!$RegNo) {
echo json_encode(array('msgType' => 'error', 'msg' => 'Registration no can not be empty.'));
return;
}
/* Update out time of parent */
if ($ParentProcessId) {
$aParentData = getParentProcessByRegNo($RegNo, $ParentProcessId);
if (!$aParentData) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job has no previous record.'));
return;
}
$ProTrackId = $aParentData['ProTrackId'];
$ParentInTime = $aParentData['InTime'];
$ParentOutTime = $aParentData['OutTime'];
$ParentBHold = $aParentData['bHold'];
if ($ParentBHold) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job is holded by previous process.'));
exit;
}
if ($ParentOutTime) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job is received already.'));
return;
}
$aParentTimeDuration = getTimeDuration($ParentInTime);
$duration = $aParentTimeDuration['Duration'];
$txtDuration = $aParentTimeDuration['txtDuration'];
if ($ProTrackId) {
$sql2 = "UPDATE t_process_tracking SET OutTime = NOW(), Duration = {$duration}, TxtDuration = '{$txtDuration}', OutUserId = '{$jUserId}' WHERE ProTrackId = {$ProTrackId};";
$aQuery2 = array('command' => 'UPDATE', 'query' => $sql2, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo'), 'pk_values' => array("'" . $TrackingNo . "'"), 'bUseInsetId' => FALSE);
$aQuerys[] = $aQuery2;
}
}
$aRecExistData2 = getRecExistInProcByRegNo($RegNo, $ProcessId);
if (!$aRecExistData2) {
/* Insert the current process */
$sql = "INSERT INTO t_process_tracking\n\t\t\t\t\t(TrackingNo, RegNo, ProcessId, InTime, EntryDate, YearId, MonthId, InUserId, ProcUnitId)\n\t\t\t\t\tVALUES ('{$RegNo}', '{$RegNo}', {$ProcessId}, NOW(), Now(), YEAR(NOW()), MONTH(NOW()), '{$jUserId}', 2);";
$aQuery1 = array('command' => 'INSERT', 'query' => $sql, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo', 'ProcessId'), 'pk_values' => array("'" . $TrackingNo . "'", $ProcessId), 'bUseInsetId' => TRUE);
$aQuerys[] = $aQuery1;
}
if ($ParentProcessId) {
$aParentData2 = getInwardNoByRegNo($RegNo, $ParentProcessId);
$TrackingNo = $aParentData2['TrackingNo'];
if ($TrackingNo) {
/* Update RegNo of ancestors */
$sql3 = "UPDATE t_process_tracking\n\t\t\t\t\t\tSET TrackingNo = '{$TrackingNo}'\n\t\t\t\t\t\tWHERE TrackingNo = '{$RegNo}' AND RegNo = '{$RegNo}';";
$aQuery3 = array('command' => 'UPDATE', 'query' => $sql3, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo', 'ProcessId'), 'pk_values' => array("'" . $TrackingNo . "'", $ProcessId), 'bUseInsetId' => TRUE);
$aQuerys[] = $aQuery3;
}
}
echo json_encode(exec_query($aQuerys, $jUserId, $language, FALSE, FALSE));
break;
case 36:
if ($_POST['RegNoRec']) {
$RegNo = $_POST['RegNoRec'];
$aRecExistData2 = getRecExistInProcByRegNo($RegNo, $ProcessId);
$CurProTrackId = $aRecExistData2['ProTrackId'];
if ($CurProTrackId) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job is scanned already.'));
return;
}
/* Update out time of parent */
if ($ParentProcessId) {
$aParentData = getParentProcessByRegNo($RegNo, $ParentProcessId);
$ProTrackId = $aParentData['ProTrackId'];
$ParentInTime = $aParentData['InTime'];
$ParentBHold = $aParentData['bHold'];
if ($ParentBHold) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job is holded by previous process.'));
exit;
}
if (!$ProTrackId) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job has no previous record.'));
return;
}
$aParentTimeDuration = getTimeDuration($ParentInTime);
$duration = $aParentTimeDuration['Duration'];
$txtDuration = $aParentTimeDuration['txtDuration'];
if ($ProTrackId) {
$sql2 = "UPDATE t_process_tracking SET OutTime = NOW(), Duration = {$duration}, TxtDuration = '{$txtDuration}', OutUserId = '{$jUserId}' WHERE ProTrackId = {$ProTrackId};";
$aQuery2 = array('command' => 'UPDATE', 'query' => $sql2, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo'), 'pk_values' => array("'" . $TrackingNo . "'"), 'bUseInsetId' => FALSE);
$aQuerys[] = $aQuery2;
}
}
/* Insert the current process */
$sql = "INSERT INTO t_process_tracking\n\t\t\t\t\t(TrackingNo, RegNo, ProcessId, InTime, EntryDate, YearId, MonthId, InUserId, ProcUnitId)\n\t\t\t\t\tVALUES ('{$RegNo}', '{$RegNo}', {$ProcessId}, NOW(), Now(), YEAR(NOW()), MONTH(NOW()), '{$jUserId}', 2);";
$aQuery1 = array('command' => 'INSERT', 'query' => $sql, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo', 'ProcessId'), 'pk_values' => array("'" . $TrackingNo . "'", $ProcessId), 'bUseInsetId' => TRUE);
$aQuerys[] = $aQuery1;
if ($ParentProcessId) {
$aParentData2 = getInwardNoByRegNo($RegNo, $ParentProcessId);
$TrackingNo = $aParentData2['TrackingNo'];
if ($TrackingNo) {
/* Update RegNo of ancestors */
$sql3 = "UPDATE t_process_tracking\n\t\t\t\t\t\t\tSET TrackingNo = '{$TrackingNo}'\n\t\t\t\t\t\t\tWHERE TrackingNo = '{$RegNo}' AND RegNo = '{$RegNo}';";
$aQuery3 = array('command' => 'UPDATE', 'query' => $sql3, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo', 'ProcessId'), 'pk_values' => array("'" . $TrackingNo . "'", $ProcessId), 'bUseInsetId' => TRUE);
$aQuerys[] = $aQuery3;
}
}
echo json_encode(exec_query($aQuerys, $jUserId, $language, FALSE, FALSE));
} else {
if ($_POST['RegNoDel']) {
$RegNo = $_POST['RegNoDel'];
$aParentData = getParentProcessByRegNo($RegNo, $ParentProcessId);
if (!$aParentData) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job has no previous record.'));
return;
}
$aRecExistData2 = getRecExistInProcByRegNo($RegNo, $ProcessId);
// print_r($aRecExistData2);
// exit;
$OwnProTrackId = $aRecExistData2['ProTrackId'];
$OwnInTime = $aRecExistData2['InTime'];
$OwnOutTime = $aRecExistData2['OutTime'];
$OwnBHold = $aRecExistData2['bHold'];
if ($OwnBHold) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job is holded by previous process.'));
exit;
}
if ($OwnOutTime) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job is delivered already.'));
return;
}
$aOwnTimeDuration = getTimeDuration($OwnInTime);
$duration = $aOwnTimeDuration['Duration'];
$txtDuration = $aOwnTimeDuration['txtDuration'];
if ($OwnProTrackId) {
$sql2 = "UPDATE t_process_tracking SET OutTime = NOW(), Duration = {$duration}, TxtDuration = '{$txtDuration}', OutUserId = '{$jUserId}' WHERE ProTrackId = {$OwnProTrackId};";
$aQuery2 = array('command' => 'UPDATE', 'query' => $sql2, 'sTable' => 't_process_tracking', 'pks' => array('ProTrackId'), 'pk_values' => array($OwnProTrackId), 'bUseInsetId' => FALSE);
$aQuerys[] = $aQuery2;
}
echo json_encode(exec_query($aQuerys, $jUserId, $language, FALSE, FALSE));
}
}
break;
default:
/* $bSubContract = isset($_POST['bSubContract']) ? $_POST['bSubContract'] : '';
if ($bSubContract == 'on') {
$bSubContract = 1;
} else {
$bSubContract = 0;
} */
if (patn_array_match($aAnalyticalSubConPrefix, $RegNo)) {
$bSubContract = 1;
} else {
$bSubContract = 0;
}
if (!$RegNo) {
echo json_encode(array('msgType' => 'error', 'msg' => 'Registration no can not be empty.'));
return;
}
if (!$RegNo) {
echo json_encode(array('msgType' => 'error', 'msg' => 'Registration no can not be empty.'));
return;
}
if (getRecExistInProcByRegNo($RegNo, $ProcessId)) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This Job is scanned already.'));
return;
}
/* Update out time of parent */
if ($ParentProcessId && !$bSubContract) {
$aParentData = getParentProcessByRegNo($RegNo, $ParentProcessId);
if (!$aParentData) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job has no previous record.'));
return;
}
$ProTrackId = $aParentData['ProTrackId'];
$ParentInTime = $aParentData['InTime'];
$ParentBHold = $aParentData['bHold'];
if ($ParentBHold) {
echo json_encode(array('msgType' => 'error', 'msg' => 'This job is holded by previous process.'));
exit;
}
$aParentTimeDuration = getTimeDuration($ParentInTime);
$duration = $aParentTimeDuration['Duration'];
$txtDuration = $aParentTimeDuration['txtDuration'];
if ($ProTrackId) {
$sql2 = "UPDATE t_process_tracking SET OutTime = NOW(), Duration = {$duration}, TxtDuration = '{$txtDuration}', OutUserId = '{$jUserId}' WHERE ProTrackId = {$ProTrackId};";
$aQuery2 = array('command' => 'UPDATE', 'query' => $sql2, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo'), 'pk_values' => array("'" . $TrackingNo . "'"), 'bUseInsetId' => FALSE);
$aQuerys[] = $aQuery2;
}
}
/* Insert the current process */
$sql = "INSERT INTO t_process_tracking\n\t\t\t\t(TrackingNo, RegNo, ProcessId, InTime, EntryDate, YearId, MonthId, InUserId, ProcUnitId, bSubContract)\n\t\t\t\tVALUES ('{$RegNo}', '{$RegNo}', {$ProcessId}, NOW(), Now(), YEAR(NOW()), MONTH(NOW()), '{$jUserId}', {$ProcUnitId}, {$bSubContract});";
$aQuery1 = array('command' => 'INSERT', 'query' => $sql, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo', 'ProcessId'), 'pk_values' => array("'" . $TrackingNo . "'", $ProcessId), 'bUseInsetId' => TRUE);
$aQuerys[] = $aQuery1;
if ($ParentProcessId && !$bSubContract) {
$aParentData2 = getInwardNoByRegNo($RegNo, $ParentProcessId);
$TrackingNo = $aParentData2['TrackingNo'];
if ($TrackingNo) {
/* Update RegNo of ancestors */
$sql3 = "UPDATE t_process_tracking\n\t\t\t\t\t\tSET TrackingNo = '{$TrackingNo}'\n\t\t\t\t\t\tWHERE TrackingNo = '{$RegNo}' AND RegNo = '{$RegNo}';";
$aQuery3 = array('command' => 'UPDATE', 'query' => $sql3, 'sTable' => 't_process_tracking', 'pks' => array('TrackingNo', 'ProcessId'), 'pk_values' => array("'" . $TrackingNo . "'", $ProcessId), 'bUseInsetId' => TRUE);
$aQuerys[] = $aQuery3;
}
}
echo json_encode(exec_query($aQuerys, $jUserId, $language, FALSE, FALSE));
break;
}
}
function validateJobByRegNo($RegNo, $ProcessId)
{
if (!$RegNo) {
return array('msgType' => 'error', 'msg' => 'Registration no can not be empty.');
}
if (getRecExistInProcByRegNo($RegNo, $ProcessId)) {
return array('msgType' => 'error', 'msg' => 'This Job is scanned already.');
}
}
