function getWaitingProcessList($conn)
{
global $gTEXT;
date_default_timezone_set("Asia/Dhaka");
$lan = $_POST['lan'];
$ProcessId = $_POST['ProcessId'];
$ProcessOrder = $_POST['ProcessOrder'];
$sLimit = "";
if (isset($_POST['iDisplayStart'])) {
$sLimit = " LIMIT " . mysql_real_escape_string($_POST['iDisplayStart']) . ", " . mysql_real_escape_string($_POST['iDisplayLength']);
}
$sOrder = "";
if (isset($_POST['iSortCol_0'])) {
$sOrder = " ORDER BY ";
for ($i = 0; $i < mysql_real_escape_string($_POST['iSortingCols']); $i++) {
$sOrder .= fnColumnToField_ProcessTrackingList(mysql_real_escape_string($_POST['iSortCol_' . $i])) . "\n\t\t\t\t\t\t\t\t" . mysql_real_escape_string($_POST['sSortDir_' . $i]) . ", ";
}
$sOrder = substr_replace($sOrder, "", -2);
}
$sWhere = "";
if ($_POST['sSearch'] != "") {
$sWhere = " AND (t_process_tracking.TrackingNo LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . " t_process_list.ProcessName LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . " t_process_tracking.InTime LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . " t_process_tracking.OutTime LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%') ";
}
$sql = "SELECT \n\t\t\t\t SQL_CALC_FOUND_ROWS DISTINCT a.RegNo, p.RegNo RegNoWet, p.InTime InTimeWet, p.OutTime OutTimeWet, q.RegNo RegNoMec, q.InTime InTimeMec, q.OutTime OutTimeMec, r.RegNo RegNoPil, r.InTime InTimePil, r.OutTime OutTimePil \n\t\t\t\tFROM\n\t\t\t\t t_process_tracking a \n\t\t\t\t INNER JOIN t_process_list b \n\t\t\t\t\tON (a.ProcessId = b.ProcessId) \n\t\t\t\t LEFT JOIN \n\t\t\t\t\t(SELECT \n\t\t\t\t\t RegNo, InTime, OutTime \n\t\t\t\t\tFROM\n\t\t\t\t\t t_process_tracking \n\t\t\t\t\tWHERE ProcessId = 5) p \n\t\t\t\t\tON (a.RegNo = p.RegNo) \n\t\t\t\t LEFT JOIN \n\t\t\t\t\t(SELECT \n\t\t\t\t\t RegNo, InTime, OutTime \n\t\t\t\t\tFROM\n\t\t\t\t\t t_process_tracking \n\t\t\t\t\tWHERE ProcessId = 6) q \n\t\t\t\t\tON (a.RegNo = q.RegNo) \n\t\t\t\t LEFT JOIN \n\t\t\t\t\t(SELECT \n\t\t\t\t\t RegNo, InTime, OutTime \n\t\t\t\t\tFROM\n\t\t\t\t\t t_process_tracking \n\t\t\t\t\tWHERE ProcessId = 7) r \n\t\t\t\t\tON (a.RegNo = r.RegNo) \n\t\t\t\tWHERE a.ProcessId IN (5,6,7)\n {$sWhere} \n {$sOrder} \n {$sLimit} ";
$result = mysql_query($sql, $conn);
$total = mysql_num_rows($result);
$sQuery = "SELECT FOUND_ROWS()";
$rResultFilterTotal = mysql_query($sQuery);
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
$sOutput = '{';
$sOutput .= '"sEcho": ' . intval($_POST['sEcho']) . ', ';
$sOutput .= '"iTotalRecords": ' . $iFilteredTotal . ', ';
$sOutput .= '"iTotalDisplayRecords": ' . $iFilteredTotal . ', ';
$sOutput .= '"aaData": [ ';
$serial = $_POST['iDisplayStart'] + 1;
$f = 0;
while ($aRow = mysql_fetch_array($result)) {
if ($f++) {
$sOutput .= ',';
}
$sOutput .= "[";
$sOutput .= '"' . $aRow['ProTrackId'] . '",';
$sOutput .= '"' . $serial++ . '",';
$sOutput .= '"' . $aRow['TrackingNo'] . '",';
$sOutput .= '"' . $aRow['ProcessName'] . '",';
$sOutput .= '"' . date('d/m/Y g:i A', strtotime($aRow['InTime'])) . '",';
$sOutput .= '"' . $aRow['OutTime'] . '",';
$sOutput .= '"' . convertToHoursMins($aRow['Duration'], '%02d hours %02d minutes') . '",';
$sOutput .= '"' . ($aRow['Status'] < 0 ? abs($aRow['Status']) . ' minutes ahead' : abs($aRow['Status']) . ' minutes delay') . '",';
$sOutput .= '"' . $aRow['ProcessId'] . '",';
$sOutput .= '"' . $aRow['ProcessOrder'] . '"';
$sOutput .= "]";
}
$sOutput .= '] }';
echo $sOutput;
}
function getProcessTrackingData($conn)
{
global $gTEXT;
date_default_timezone_set("Asia/Dhaka");
$lan = $_POST['lan'];
$ProcessId = $_POST['ProcessId'];
$sLimit = "";
if (isset($_POST['iDisplayStart'])) {
$sLimit = " LIMIT " . mysql_real_escape_string($_POST['iDisplayStart']) . ", " . mysql_real_escape_string($_POST['iDisplayLength']);
}
$sOrder = "";
if (isset($_POST['iSortCol_0'])) {
$sOrder = " ORDER BY ";
for ($i = 0; $i < mysql_real_escape_string($_POST['iSortingCols']); $i++) {
$sOrder .= fnColumnToField_ProcessTrackingList(mysql_real_escape_string($_POST['iSortCol_' . $i])) . "\n\t\t\t\t\t\t\t\t" . mysql_real_escape_string($_POST['sSortDir_' . $i]) . ", ";
}
$sOrder = substr_replace($sOrder, "", -2);
}
$sWhere = "";
if ($_POST['sSearch'] != "") {
$sWhere = " AND (a.TrackingNo LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . " a.RegNo LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . " b.ProcessName LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . " a.InTime LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . " a.OutTime LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%') ";
}
if ($ProcessId == 1) {
$sql = "SELECT \n\t\t\tSQL_CALC_FOUND_ROWS a.ProTrackId, a.TrackingNo, a.RegNo, b.ProcessId, b.ProcessName, b.ProcessOrder, a.InTime, a.OutTime \n\t\t\t, TIMESTAMPDIFF(SECOND, InTime, NOW()) AS Duration\n\t\t\t, UsualDuration\n\t\t\t, (TIMESTAMPDIFF(SECOND, InTime, NOW()) - UsualDuration) Status\n\t\t\t, bHold\n , HoldComments\n\t\t\tFROM\n\t\t\t t_process_tracking a \n\t\t\t INNER JOIN t_process_list b \n\t\t\t\tON (a.ProcessId = b.ProcessId)\n\t\t\tWHERE a.ProcessId IN (1, 23) \n\t\t\t AND a.OutTime IS NULL \n {$sWhere} \n {$sOrder} \n {$sLimit} ";
} else {
$sql = "SELECT \n\t\t\tSQL_CALC_FOUND_ROWS a.ProTrackId, a.TrackingNo, a.RegNo, b.ProcessId, b.ProcessName, b.ProcessOrder, a.InTime, a.OutTime \n\t\t\t, TIMESTAMPDIFF(SECOND, InTime, NOW()) AS Duration\n\t\t\t, UsualDuration\n\t\t\t, (TIMESTAMPDIFF(SECOND, InTime, NOW()) - UsualDuration) Status\n\t\t\t, bHold\n , HoldComments\n\t\t\tFROM\n\t\t\t t_process_tracking a \n\t\t\t INNER JOIN t_process_list b \n\t\t\t\tON (a.ProcessId = b.ProcessId)\n\t\t\tWHERE a.ProcessId = {$ProcessId} \n\t\t\t AND a.OutTime IS NULL \n {$sWhere} \n {$sOrder} \n {$sLimit} ";
}
// echo $sql;
// exit;
$result = mysql_query($sql, $conn);
$total = mysql_num_rows($result);
$sQuery = "SELECT FOUND_ROWS()";
$rResultFilterTotal = mysql_query($sQuery);
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
$sOutput = '{';
$sOutput .= '"sEcho": ' . intval($_POST['sEcho']) . ', ';
$sOutput .= '"iTotalRecords": ' . $iFilteredTotal . ', ';
$sOutput .= '"iTotalDisplayRecords": ' . $iFilteredTotal . ', ';
$sOutput .= '"aaData": [ ';
$serial = $_POST['iDisplayStart'] + 1;
$f = 0;
while ($aRow = mysql_fetch_array($result)) {
$y = "<a class='task-del itmEdit' href='javascript:void(0);'><span class='label label-info'>" . 'Edit' . "</span></a>";
//$z = "<a class='task-del itmDrop' style='margin-left:4px' href='javascript:void(0);'><span class='label label-danger'>" . 'Delete' . "</span></a>";
if ($f++) {
$sOutput .= ',';
}
$sOutput .= "[";
$sOutput .= '"' . $aRow['ProTrackId'] . '",';
$sOutput .= '"' . $serial++ . '",';
$sOutput .= '"' . ($aRow['RegNo'] ? $aRow['RegNo'] : $aRow['TrackingNo']) . '",';
$sOutput .= '"' . $aRow['ProcessName'] . '",';
$sOutput .= '"' . date('d/m/Y g:i A', strtotime($aRow['InTime'])) . '",';
$sOutput .= '"' . $aRow['OutTime'] . '",';
$sOutput .= '"' . convertToHoursMins($aRow['Duration'], '%02d hours %02d minutes') . '",';
//$statusTime = $aRow['Status'] < 0 ? abs($aRow['Status']) : abs($aRow['Status']);
$statusTime = $aRow['Status'] < 0 ? "<span style='color:#078C09;'>" . convertToHoursMins(abs($aRow['Status']), '%02d hours %02d minutes remaining') . "</span>" : "<span style='color:#ff0000;'>" . convertToHoursMins(abs($aRow['Status']), '%02d hours %02d minutes delay' . "</span>");
$sOutput .= '"' . $statusTime . '",';
$sOutput .= '' . ($aRow['bHold'] ? 1 : 0) . ',';
$sOutput .= '' . ($aRow['bHold'] ? '"Yes"' : '""') . ',';
$sOutput .= '"' . crnl2br($aRow['HoldComments']) . '",';
$sOutput .= '"' . $y . '",';
$sOutput .= '' . $aRow['ProcessId'] . '';
$sOutput .= "]";
}
$sOutput .= '] }';
echo $sOutput;
}