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;
}
function getFundingReqData($conn)
{
global $gTEXT;
//$condition='';
// $sWhere = "";
$itemGroupId = $_POST['itemGroupId'];
$serviceTypeId = $_POST['ServiceTypeId'];
//echo $serviceTypeId;
/* if($itemGroupId!=0){
$sWhere=" WHERE a.ItemGroupId = '".$itemGroupId."'";
// $condition.=" "; //AND a.ServiceTypeId = '".$serviceTypeId."'
} */
$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_fundingreq(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 .= " WHERE (FundingReqSourceName LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n OR FundingReqSourceNameFrench LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%') ";
}
/* $sWhere = "";
if ($_POST['sSearch'] != "") {
$sWhere .= " AND (FundingReqSourceName LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'
OR FundingReqSourceNameFrench LIKE '%".mysql_real_escape_string( $_POST['sSearch'] )."%') ";
} */
$sql = "SELECT SQL_CALC_FOUND_ROWS FundingReqSourceId, FundingReqSourceName,FundingReqSourceNameFrench, a.ItemGroupId, a.ServiceTypeId\n\t\t\t\tFROM t_fundingreqsources a\n INNER JOIN t_itemgroup b ON a.ItemGroupId = b.ItemGroupId\n INNER JOIN t_servicetype c ON a.ServiceTypeId = c.ServiceTypeId\n AND a.ItemGroupId = " . $itemGroupId . " OR " . $itemGroupId . " = 0\n AND (a.ServiceTypeId = " . $serviceTypeId . " OR " . $serviceTypeId . " = 0)\n {$sWhere} {$sOrder} {$sLimit} ";
//echo $sql;
$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;
$y = "<a class='task-del itmEdit' href='javascript:void(0);'><span class='label label-info'>" . $gTEXT['Edit'] . "</span></a>";
$z = "<a class='task-del itmDrop' style='margin-left:4px' href='javascript:void(0);'><span class='label label-danger'>" . $gTEXT['Delete'] . "</span></a>";
$f = 0;
while ($aRow = mysql_fetch_array($result)) {
$FundingName = crnl2br($aRow['FundingReqSourceName']);
// $GroupName = crnl2br($aRow['GroupName']) ;
// $ServiceTypeName = crnl2br($aRow['ServiceTypeName']) ;
// $ColorCode = mysql_real_escape_string( '<span style="width:30px;height:15px;display:block;align:center;background:'.$aRow['ColorCode'].';"></span>');
if ($f++) {
$sOutput .= ',';
}
$sOutput .= "[";
$sOutput .= '"' . $aRow['FundingReqSourceId'] . '",';
//addslashes
$sOutput .= '"' . $serial++ . '",';
$sOutput .= '"' . $FundingName . '",';
//******* $FormulationName. '",';
$sOutput .= '"' . $aRow['FundingReqSourceNameFrench'] . '",';
//$sOutput .= '"' . $aRow['GroupName'] . '",';
//$sOutput .= '"' . $aRow['ServiceTypeName'] . '",';
//$sOutput .= '"' . $ColorCode . '",';
$sOutput .= '"' . $y . $z . '",';
$sOutput .= '"' . $aRow['ItemGroupId'] . '",';
$sOutput .= '"' . $aRow['ServiceTypeId'] . '"';
// $sOutput .= '"' . $aRow['ColorCode'] . '"';
$sOutput .= "]";
}
$sOutput .= '] }';
echo $sOutput;
}
function generateProductStockReport($conn)
{
$jBaseUrl = $_POST['jBaseUrl'];
$tbltitle = $_REQUEST['tbltitle'];
$filePath = 'pdfslice/product_stock.svg';
$Year = $_REQUEST['Year'];
$MonthId = $_REQUEST['Month'];
$ItemGroupId = $_REQUEST['ItemGroup'];
$bKeyItem = $_REQUEST['bKeyItem'];
if ($bKeyItem == 1) {
$tp = "Tracer Products";
} else {
$tp = "All Products";
}
$ItemGroupName = $_REQUEST['ItemGroupName'];
$itemNo = $_REQUEST['ItemNo'];
$bKeyItemName = $_REQUEST['bKeyItemName'];
$MonthName = $_REQUEST['MonthName'];
$ItemName = $_REQUEST['ItemName'];
$CountryId = 1;
$alavel = htmlentities($_POST['alavel'], ENT_QUOTES, "UTF-8");
$alavel = html_entity_decode($alavel, ENT_QUOTES, "UTF-8");
require_once 'tcpdf/tcpdf.php';
require_once 'fpdf/fpdi.php';
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__) . '/lang/eng.php')) {
require_once dirname(__FILE__) . '/lang/eng.php';
$pdf->setLanguageArray($l);
}
$pdf->SetFont('dejavusans', '', 7);
$pdf->AddPage();
$html0 = '
<table >
<tr >
<td width="60" align="center" height="100" ><img src="../images/logo.png"/></td>
</tr>
</table>';
$pdf->writeHTMLCell(0, 0, 35, 7, $html0, '', 0, 0, false, 'C', true);
$html1 = '
<table >
<tr >
<td width="400" align="center" style="font-size: 16px;" > Swaziland Health Product Tracking System </td>
</tr>
</table>';
$pdf->writeHTMLCell(0, 0, 50, 10, $html1, '', 0, 0, false, 'C', true);
$html = '
<div class="padding-md clearfix">
<h3 style="text-align:center;">Product Stock Status - ' . $ItemGroupName . ' (' . $tp . ')</h3>
</div>
';
//<div style="margin:10px ; padding:20px;border: 1px solid gray "></div>
$html .= ' <img src="' . $filePath . '" width=auto height=auto />
<table style="text-aling:center"> <tr><td></td><td></td>';
$sQuery = "SELECT MosTypeId, MosTypeName, MinMos, MaxMos, ColorCode, MosLabel \r\n FROM t_mostype\r\n WHERE ItemGroupId = " . $ItemGroupId . "\r\n ORDER BY MosTypeId ;";
$rResult = mysql_query($sQuery);
$output = array();
$x = "";
//<table><tr>
$y .= "</tr><tr><td></td><td></td>";
while ($row = mysql_fetch_array($rResult)) {
$x .= '<td style="background-color:' . $row['ColorCode'] . '; width:65px;"> </td>';
$y .= "<td>MOS: " . $row['MosLabel'] . "</td>";
}
$html .= $x . $y . '</tr>';
$html .= ' <tr><td></td></tr><tr><td></td></tr></table>
<style>
td{
height: 6px;
line-height:3px;
}
th{
height: 20;
}
</style>
<table width="635px" border="0.5" style="margin:0 auto; font-size: 8px; font-family: sans-serif">
<tr><th style="text-align:center;"><b> ' . $tbltitle . '</b></th></tr>
<tr >
<td width="30" align="center" ><b>SL</b></td>
<td width="30" align="left"> </td>
<td width="200" align="left"><b>Products</b></td>
<td width="105" align="right"><b>Reported Closing Balance</b></td>
<td width="105" align="right"><b>Reported Consumption</b></td>
<td width="105" align="right"><b>Average Monthly Consumption</b></td>
<td width="60" align="right"><b>MOS</b></td>
</tr>
';
mysql_query("SET @rank=0;");
$serial = "@rank:=@rank+1 AS SL";
$sql = "SELECT SQL_CALC_FOUND_ROWS " . $serial . "\r\n\t\t\t\t, ItemName\t\t\t \r\n\t\t\t , DispenseQty\r\n\t\t\t , ClStock\t\t\t \r\n\t\t\t , AMC\r\n\t\t\t , MOS\r\n\t\t\t , ColorCode\r\n\t\t\t FROM \r\n\t\t\t\t/* b-START */(SELECT ItemName\t\t\t \r\n\t\t\t , SUM(DispenseQty) DispenseQty\r\n\t\t\t , SUM(ClStock) ClStock\t\t\t \r\n\t\t\t , SUM(AMC) AMC\r\n\t\t\t , IFNULL(SUM(ClStock)/SUM(AMC), 0) MOS\r\n\t\t\t , /* Co-START */(SELECT ColorCode FROM t_mostype\r\n\t\t\tWHERE (ItemGroupId = {$ItemGroupId} AND IFNULL(SUM(a.ClStock)/SUM(a.AMC), 0) >= MinMos AND IFNULL(SUM(a.ClStock)/SUM(a.AMC), 0) < MaxMos)) ColorCode /* Co-END */ FROM\r\n\t\t\t/* a-START */(SELECT\r\n\t\t\t t_cfm_masterstockstatus.ItemGroupId\r\n\t\t\t , t_cfm_stockstatus.ItemNo\r\n\t\t\t , t_itemlist.ItemName\r\n\t\t\t , t_itemlist.ShortName\r\n\t\t\t , SUM(t_cfm_stockstatus.ClStock) ClStock\r\n\t\t\t , 0 DispenseQty\r\n\t\t\t , 0 AMC \r\n\t\t\tFROM\r\n\t\t\t t_cfm_stockstatus\r\n\t\t\t INNER JOIN t_cfm_masterstockstatus \r\n\t\t\t ON (t_cfm_stockstatus.CFMStockId = t_cfm_masterstockstatus.CFMStockId)\r\n\t\t\t INNER JOIN t_itemlist \r\n\t\t\t ON (t_cfm_stockstatus.ItemNo = t_itemlist.ItemNo)\r\n\t\t\t INNER JOIN t_facility \r\n\t\t\t ON (t_cfm_stockstatus.FacilityId = t_facility.FacilityId)\r\n\t\t\t INNER JOIN t_facility_group_map \r\n\t\t\t ON (t_facility_group_map.FacilityId = t_facility.FacilityId AND t_facility_group_map.ItemGroupId = {$ItemGroupId}) \r\n\t\t\tWHERE (t_cfm_masterstockstatus.Year = '{$Year}'\r\n\t\t\t AND t_cfm_masterstockstatus.MonthId = {$MonthId}\r\n\t\t\t AND t_cfm_masterstockstatus.CountryId = {$CountryId}\r\n\t\t\t AND t_cfm_masterstockstatus.StatusId = 5\r\n\t\t\t AND t_cfm_masterstockstatus.ItemGroupId = {$ItemGroupId}\r\n\t\t\t AND t_itemlist.bKeyItem = {$bKeyItem})\r\n\t\t\tGROUP BY t_cfm_masterstockstatus.ItemGroupId, t_cfm_stockstatus.ItemNo\r\n\t\t\tUNION ALL\r\n\t\t\tSELECT\r\n\t\t\t t_cfm_masterstockstatus.ItemGroupId\r\n\t\t\t , t_cfm_stockstatus.ItemNo\r\n\t\t\t , t_itemlist.ItemName\r\n\t\t\t , t_itemlist.ShortName \r\n\t\t\t , 0 ClStock\r\n\t\t\t , SUM(t_cfm_stockstatus.DispenseQty) DispenseQty\r\n\t\t\t , SUM(t_cfm_stockstatus.AMC) AMC \r\n\t\t\tFROM\r\n\t\t\t t_cfm_stockstatus\r\n\t\t\t INNER JOIN t_cfm_masterstockstatus \r\n\t\t\t ON (t_cfm_stockstatus.CFMStockId = t_cfm_masterstockstatus.CFMStockId)\r\n\t\t\t INNER JOIN t_itemlist \r\n\t\t\t ON (t_cfm_stockstatus.ItemNo = t_itemlist.ItemNo)\r\n\t\t\t INNER JOIN t_facility \r\n\t\t\t ON (t_cfm_stockstatus.FacilityId = t_facility.FacilityId)\r\n\t\t\t INNER JOIN t_facility_group_map \r\n\t\t\t ON (t_facility_group_map.FacilityId = t_facility.FacilityId AND t_facility_group_map.ItemGroupId = 2) \r\n\t\t\tWHERE (t_cfm_masterstockstatus.Year = '{$Year}'\r\n\t\t\t AND t_cfm_masterstockstatus.MonthId = {$MonthId}\r\n\t\t\t AND t_cfm_masterstockstatus.CountryId = {$CountryId}\r\n\t\t\t AND t_cfm_masterstockstatus.StatusId = 5\r\n\t\t\t AND t_cfm_masterstockstatus.ItemGroupId = {$ItemGroupId}\r\n\t\t\t AND t_itemlist.bKeyItem = {$bKeyItem}\r\n\t\t\t AND t_facility.FLevelId = 99)\r\n\t\t\tGROUP BY t_cfm_masterstockstatus.ItemGroupId, t_cfm_stockstatus.ItemNo) a /* a-END */\r\n\t\t\tGROUP BY ItemGroupId, ItemNo, ItemName, ShortName) b /* b-END */\r\n\t\t\tWHERE 1=1\r\n\t\t\t\t ORDER BY \tItemName ";
$result = mysql_query($sql, $conn);
$total = mysql_num_rows($result);
// $data=array();
// $f=0;
// $tblHTML='';
// while ($rec = mysql_fetch_array($result)) {
// $data['SL'][$f]=$f;
// $data['ItemName'][$f] = $rec['ItemName'];
// $data['ReportedConsumption'][$f]=number_format($rec['ReportedConsumption']);
// $data['ReportedClosingBalance'][$f]=number_format($rec['ReportedClosingBalance']);
// $data['AMC'][$f]=number_format($rec['AMC']);
// $data['MOS'][$f] = number_format(($rec['MOS']),1);
//
// $html.='<tr style="page-break-inside:avoid;font-size: 7px; font-family: sans-serif">
// <td align="center" width="30" valign="middle" height="20" padding="15" >'.($data['SL'][$f]+1).'</td>
// <td align="left" width="200" valign="middle" >'.$data['ItemName'][$f].'</td>
// <td align="right" width="105" valign="middle">'.$data['ReportedConsumption'][$f].'</td>
// <td align="right" width="105" valign="middle">'.$data['ReportedClosingBalance'][$f].'</td>
// <td align="right" width="105" valign="middle">'.$data['AMC'][$f].'</td>
// <td align="right" width="60" valign="middle">'.$data['MOS'][$f].'</td>
// </tr>';
// $f++;
// }
//
//
$aColumns = array('SL', 'ColorCode', 'ItemName', 'DispenseQty', 'ClStock', 'AMC', 'MOS');
while ($aRow = mysql_fetch_array($result)) {
$aRow['ItemName'] = crnl2br($aRow['ItemName']);
$row = array();
for ($i = 0; $i < count($aColumns); $i++) {
if (is_null($aRow[$aColumns[$i]])) {
$row[] = '';
} else {
if ($aColumns[$i] == 'ClStock' || $aColumns[$i] == 'AMC') {
$row[] = number_format($aRow[$aColumns[$i]]);
} else {
if ($aColumns[$i] == 'MOS') {
$row[] = number_format($aRow[$aColumns[$i]], 1);
} else {
if ($aColumns[$i] == 'ColorCode') {
$row[] = '<span style="background-color:' . $aRow[$aColumns[$i]] . '; width:30px;"> </span>';
} else {
$row[] = $aRow[$aColumns[$i]];
}
}
}
}
}
$output['aaData'][] = $row;
// $i++;
}
for ($i = 0; $i < count($output['aaData']); $i++) {
$html .= '<tr>';
for ($j = 0; $j < count($output['aaData'][$i]); $j++) {
if ($j == 0 or $j == 1) {
$w = '30px';
$al = 'center';
} else {
if ($j == 2) {
$w = '200px';
$al = 'left';
} else {
if ($j == 6) {
$w = '60px';
$al = 'right';
} else {
$w = '105px';
$al = 'right';
}
}
}
$html .= '<td style="text-align: ' . $al . '; width:' . $w . ';">' . $output['aaData'][$i][$j] . '</td>';
}
$html .= '</tr>';
}
$html .= ' </table> ';
$pdf->writeHTMLCell(0, 0, 10, 10, $html, '', 0, 0, false, 'C', true);
$filePath = SITEDOCUMENT . 'administrator/components/com_jcode/source/report/pdfslice/Product_Stock_Status_Chart.pdf';
if (file_exists($filePath)) {
unlink($filePath);
}
$filename = 'Product_Stock_Report_' . $ItemGroupName . '_' . $MonthName . '_' . $Year . '.pdf';
$pdf->Output('pdfslice/' . $filename, 'F');
echo $filename;
}
function getErrorLog($conn)
{
global $gTEXT;
mysql_query("SET character_set_results=utf8");
$data = array();
$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_ErrorLog(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 = " WHERE (userName LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n\t\t\t\t\t\t\tOR " . " RemoteIP LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' \n\t\t\t\t\t\t\tOR " . " queryType LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' \n\t\t\t\t\t\t\tOR " . " errorNo LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' \n\t\t\t\t\t\t\tOR " . " errorMsg LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' ) ";
}
$sql = "SELECT `logId`,`logDate`,`RemoteIP`,`userName`,`queryType`,`query`, `errorNo`,`errorMsg` FROM t_errorlog {$sWhere} {$sOrder} {$sLimit}";
// echo $sql;
$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;
// $y = "<a class='task-del itmEdit' href='javascript:void(0);'><span class='label label-info'>".$gTEXT['Edit']."</span></a>";
// $z = "<a class='task-del itmDrop' style='margin-left:4px' href='javascript:void(0);'><span class='label label-danger'>".$gTEXT['Delete']."</span></a>";
$f = 0;
while ($aRow = mysql_fetch_array($result)) {
$logDate = strtotime($aRow['logDate']);
$logDate = date('d/m/Y H:i:s', $logDate);
$query = $aRow['query'];
$errorMsg = $aRow['errorMsg'];
if ($f++) {
$sOutput .= ',';
}
$sOutput .= "[";
$sOutput .= '"' . $aRow['logId'] . '",';
$sOutput .= '"' . $serial++ . '",';
$sOutput .= '"' . $aRow['RemoteIP'] . '",';
$sOutput .= '"' . $aRow['userName'] . '",';
$sOutput .= '"' . $logDate . '",';
$sOutput .= '"' . $aRow['queryType'] . '",';
$sOutput .= '"' . crnl2br($query) . '",';
$sOutput .= '"' . $aRow['errorNo'] . '",';
$sOutput .= '"' . crnl2br($errorMsg) . '"';
$sOutput .= "]";
}
$sOutput .= '] }';
echo $sOutput;
}
}
$objPHPExcel->getActiveSheet()->getStyle('A7:A7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A' . $j . ':A' . $j)->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->getStyle('B' . $j . ':B' . $j)->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->getStyle('A7:A7')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B7:B7')->getFont()->setBold(true);
$j = 8;
for ($x = 0; $x < count($aData); $x++) {
$k = 1;
for ($y = 0; $y < count($aData[$x]); $y++) {
$l = getLatter($k);
if ($y == 0) {
$objPHPExcel->getActiveSheet()->SetCellValue($l . $j, $aData[$x][$y]);
$objPHPExcel->getActiveSheet()->getStyle($l . $j . ':' . $l . $j)->applyFromArray($styleThinBlackBorderOutline);
} else {
$objPHPExcel->getActiveSheet()->SetCellValue($l . $j, crnl2br($aData[$x][$y]));
$objPHPExcel->getActiveSheet()->getStyle($l . $j . ':' . $l . $j)->applyFromArray($styleThinBlackBorderOutline);
}
if ($l == 'A') {
$objPHPExcel->getActiveSheet()->getStyle($l . $j . ':' . $l . $j)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
} else {
if ($l == 'B') {
$objPHPExcel->getActiveSheet()->getStyle($l . $j . ':' . $l . $j)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
} else {
$objPHPExcel->getActiveSheet()->getStyle($l . $j . ':' . $l . $j)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
}
}
$k++;
}
$j++;
}
