$MonthName = $_GET['MonthName'];
$ItemGroupName = $_GET['ItemGroupName'];
$sWhere = "";
if ($_GET['sSearch'] != "") {
$sSearch = str_replace("|", "+", $_GET['sSearch']);
$sWhere = " WHERE (a.ItemName LIKE '%" . mysql_real_escape_string($sSearch) . "%'\n OR " . " a.AMC LIKE '%" . mysql_real_escape_string($sSearch) . "%'\n OR " . " a.ClStock LIKE '%" . mysql_real_escape_string($sSearch) . "%' \n OR " . " a.MOS LIKE '%" . mysql_real_escape_string($sSearch) . "%'\n OR " . " b.Qty LIKE '%" . mysql_real_escape_string($sSearch) . "%' \n )";
}
$sLimit = "";
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$sLimit = "LIMIT " . intval($_GET['iDisplayStart']) . ", " . intval($_GET['iDisplayLength']);
}
$sOrder = "";
if (isset($_GET['iSortCol_0'])) {
$sOrder = " ORDER BY ";
for ($i = 0; $i < mysql_real_escape_string($_GET['iSortingCols']); $i++) {
$sOrder .= fnColumnToField_Item(mysql_real_escape_string($_GET['iSortCol_' . $i])) . "\n\t\t\t\t\t\t\t\t" . mysql_real_escape_string($_GET['sSortDir_' . $i]) . ", ";
}
$sOrder = substr_replace($sOrder, "", -2);
}
$currentYearMonth = $_GET['YearId'] . "-" . $_GET['MonthId'] . "-" . "01";
$monthList = array('1' => 'January', '2' => 'February', '3' => 'March', '4' => 'April', '5' => 'May', '6' => 'June', '7' => 'July', '8' => 'August', '9' => 'September', '10' => 'October', '11' => 'November', '12' => 'December');
$sQuery = "SELECT ItemName, IFNULL(AMC,0) AMC, IFNULL(ClStock,0) ClStock, IFNULL(MOS,0) MOS, IFNULL(Qty,0) StockOnOrder FROM \n\t\t\t\t(SELECT\n\t\t\t\t t_cnm_masterstockstatus.CountryId,\n\t\t\t\t t_itemlist.ItemNo,\n\t\t\t\t t_itemlist.ItemName,\n\t\t\t\t SUM(t_cnm_stockstatus.AMC) AMC,\n\t\t\t\t SUM(t_cnm_stockstatus.ClStock) ClStock,\n\t\t\t\t SUM(t_cnm_stockstatus.MOS) MOS\n\t\t\t\tFROM t_cnm_stockstatus\n\t\t\t\t INNER JOIN t_cnm_masterstockstatus\n\t\t\t\t ON (t_cnm_stockstatus.CNMStockId = t_cnm_masterstockstatus.CNMStockId)\n\t\t\t\t INNER JOIN t_itemlist\n\t\t\t\t ON (t_cnm_stockstatus.ItemNo = t_itemlist.ItemNo)\n\t\t\t\tWHERE (t_cnm_masterstockstatus.Year = '{$year}'\n\t\t\t\t AND t_cnm_masterstockstatus.MonthId = {$monthId}\n\t\t\t\t AND t_cnm_masterstockstatus.CountryId = {$countryId}\n\t\t\t\t AND t_cnm_masterstockstatus.ItemGroupId = {$itemGroupId}\n\t\t\t\t AND t_cnm_masterstockstatus.StatusId = 5)\n\t\t\t\tGROUP BY t_cnm_masterstockstatus.CountryId, t_itemlist.ItemNo, t_itemlist.ItemName) a \n\t\t\t\tLEFT JOIN (SELECT\n\t\t\t\t CountryId\n\t\t\t\t , ItemNo\n\t\t\t\t , SUM(Qty) Qty\n\t\t\t\tFROM\n\t\t\t\t t_agencyshipment\n\t\t\t\tWHERE (ShipmentDate > CAST('{$currentYearMonth}' AS DATETIME) AND ShipmentStatusId = 2)\n\t\t\t\tGROUP BY CountryId, ItemNo) b\n\t\t\t\tON a.CountryId = b.CountryId AND a.ItemNo = b.ItemNo\n " . $sWhere . "\n\t\t\t\tHAVING AMC>0 OR MOS>0 OR ClStock>0 OR StockOnOrder>0\n order by ItemName\n {$sLimit}";
// echo $sQuery;
mysql_query("SET character_set_results=utf8");
$rResult = mysql_query($sQuery);
$total = mysql_num_rows($rResult);
$i = 1;
if ($total > 0) {
echo '<!DOCTYPE html>
<html>
<head>
function generatePipelineReport($conn)
{
global $gTEXT;
$year = $_POST['YearId'];
$MonthName = $_POST['MonthName'];
$CountryName = $_POST['CountryName'];
require_once 'tcpdf/tcpdf.php';
require_once 'fpdf/fpdi.php';
$pdf = new FPDI();
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->AddPage();
$pdf->SetFillColor(255, 255, 255);
$html_head = "<span style='text-align:center;'><b>" . $gTEXT['National Stock Pipeline Information Report of'] . " " . $CountryName . " On " . $MonthName . ", " . $year . "</b></span>";
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
</style>
<body>
<h4 style="text-align:center;"><b>' . $gTEXT['National Stock Pipeline Information Report of'] . ' ' . $CountryName . ' ' . $gTEXT['on'] . ' ' . $MonthName . ',' . $year . '</b></h4>
</body>';
$pdf->writeHTMLCell(0, 0, 20, '', $html_head, '', 1, 1, false, 'C', true, $spacing = 0);
$pdf->setSourceFile("pdfslice/PipelineInfoChart.pdf");
$tplIdx = $pdf->importPage(1);
$pdf->useTemplate($tplIdx, 3, 0, 200, 400);
$pdf->endPage();
//=====================================================Pipeline Info List Table=======================================================
$monthId = $_POST['MonthId'];
$year = $_POST['YearId'];
$countryId = $_POST['CountryId'];
$itemGroupId = $_POST['ItemGroupId'];
$currentYearMonth = $_POST['YearId'] . "-" . $_POST['MonthId'] . "-" . "01";
$monthList = array('1' => 'January', '2' => 'February', '3' => 'March', '4' => 'April', '5' => 'May', '6' => 'June', '7' => 'July', '8' => 'August', '9' => 'September', '10' => 'October', '11' => 'November', '12' => 'December');
$sWhere = "";
if ($_POST['sSearch'] != "") {
$sWhere = " WHERE (a.ItemName LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n OR " . " a.AMC LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n OR " . " a.ClStock LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' \n OR " . " a.MOS LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n OR " . " b.Qty LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' \n )";
}
$sLimit = "";
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$sLimit = "LIMIT " . intval($_GET['iDisplayStart']) . ", " . intval($_GET['iDisplayLength']);
}
$sOrder = "";
if (isset($_POST['iSortCol_0'])) {
$sOrder = " ORDER BY ";
for ($i = 0; $i < mysql_real_escape_string($_POST['iSortingCols']); $i++) {
$sOrder .= fnColumnToField_Item(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);
}
$sql = " SELECT ItemName, IFNULL(AMC,0) AMC, IFNULL(ClStock,0) ClStock, IFNULL(MOS,0) MOS, IFNULL(Qty,0) StockOnOrder \n FROM (SELECT\n t_cnm_masterstockstatus.CountryId,\n t_itemlist.ItemNo,\n t_itemlist.ItemName,\n SUM(t_cnm_stockstatus.AMC) AMC,\n SUM(t_cnm_stockstatus.ClStock) ClStock,\n SUM(t_cnm_stockstatus.MOS) MOS\n FROM t_cnm_stockstatus\n INNER JOIN t_cnm_masterstockstatus\n ON (t_cnm_stockstatus.CNMStockId = t_cnm_masterstockstatus.CNMStockId)\n INNER JOIN t_itemlist\n ON (t_cnm_stockstatus.ItemNo = t_itemlist.ItemNo)\n WHERE (t_cnm_masterstockstatus.Year = '{$year}'\n AND t_cnm_masterstockstatus.MonthId = {$monthId}\n AND t_cnm_masterstockstatus.CountryId = {$countryId}\n AND t_cnm_masterstockstatus.ItemGroupId = {$itemGroupId}\n AND t_cnm_masterstockstatus.StatusId = 5)\n GROUP BY t_cnm_masterstockstatus.CountryId, t_itemlist.ItemNo, t_itemlist.ItemName) a \n LEFT JOIN (SELECT\n CountryId, ItemNo, SUM(Qty) Qty\n FROM t_agencyshipment\n WHERE (ShipmentDate > CAST('{$currentYearMonth}' AS DATETIME) AND ShipmentStatusId = 2)\n GROUP BY CountryId, ItemNo) b\n ON a.CountryId = b.CountryId AND a.ItemNo = b.ItemNo\n " . $sWhere . "\n HAVING AMC>0 OR MOS>0 OR ClStock>0 OR StockOnOrder>0\n ORDER BY ItemName\n {$sLimit}";
//
$result = mysql_query($sql, $conn);
$total = mysql_num_rows($result);
if ($total > 0) {
$data = array();
$f = 0;
$tblHTML = '';
while ($rec = mysql_fetch_array($result)) {
$data['SL'][$f] = $f;
$data['ItemName'][$f] = $rec['ItemName'];
$data['AMC'][$f] = number_format($rec['AMC']);
$data['ClStock'][$f] = number_format($rec['ClStock']);
$data['MOS'][$f] = number_format($rec['MOS'], 1);
$data['StockOnOrder'][$f] = $rec['StockOnOrder'] == 0 ? '' : $rec['StockOnOrder'];
$amc = $rec['AMC'] == 0 ? 1 : $rec['AMC'];
$stockOnOrderMOS = $rec['StockOnOrder'] / $amc;
$stockOnOrderMOS = $stockOnOrderMOS == 0 ? '' : number_format($stockOnOrderMOS, 1);
$totalMOS = number_format(number_format($rec['MOS'], 1) + $stockOnOrderMOS, 1);
$totalMOS = $totalMOS == 0 ? '' : $totalMOS;
//$data['StockOnOrderMOS'][$f] = $rec['StockOnOrderMOS']== 0? '' : $rec['StockOnOrderMOS'];
//$data['TotalMOS'][$f] = $rec['TotalMOS']== 0? '' : $rec['TotalMOS'];
$tblHTML .= '<tr style="page-break-inside:avoid;">
<td align="center" width="20" valign="middle">' . ($data['SL'][$f] + 1) . '</td>
<td align="left" width="150" valign="middle">' . $data['ItemName'][$f] . '</td>
<td align="right" width="50" valign="middle">' . $data['AMC'][$f] . '</td>
<td align="right" width="70" valign="middle">' . $data['ClStock'][$f] . '</td>
<td align="right" width="74" valign="middle">' . $data['MOS'][$f] . '</td>
<td align="right" width="50" valign="middle">' . $data['StockOnOrder'][$f] . '</td>
<td align="right" width="70" valign="middle">' . $stockOnOrderMOS . '</td>
<td align="right" width="50" valign="middle">' . $totalMOS . '</td>
</tr>';
$f++;
}
$pdf->startPage();
$html_head = "<span><b>" . $gTEXT['National Stock Pipeline Information List'] . "</b></span>";
$pdf->SetFont('dejavusans', '', 10);
$pdf->writeHTMLCell(0, 0, 10, 15, $html_head, '', 0, 0, false, 'C', true);
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
td{
height: 6px;
line-height:3px;
}
</style>
<body>
<table width="600px" border="0.5" style="margin:0 auto;">
<tr>
<th width="20" align="center"><b>SL</b></th>
<th width="150" align="left"><b>' . $gTEXT['Products'] . '</b></th>
<th width="50" align="right"><b>' . $gTEXT['AMC'] . '</b></th>
<th width="70" align="right"><b>' . $gTEXT['Available Stock'] . '</b></th>
<th width="74" align="right"><b>' . $gTEXT['MOS(Available)'] . '</b></th>
<th width="50" align="right"><b>' . $gTEXT['Stock on Order'] . '</b></th>
<th width="70" align="right"><b>' . $gTEXT['MOS(pipeline)'] . '</b></th>
<th width="50" align="right"><b>' . $gTEXT['Total MOS'] . '</b></th>
</tr>' . $tblHTML . '</table></body>';
$pdf->SetFont('dejavusans', '', 7);
$pdf->writeHTMLCell(0, 0, '', 25, $html, '', 1, 1, false, 'L', true);
$pdf->endPage();
$filePath = SITEDOCUMENT . 'administrator/components/com_jcode/source/report/pdfslice/PipelineInfoReport.pdf';
if (file_exists($filePath)) {
unlink($filePath);
}
$pdf->Output('pdfslice/PipelineInfoReport.pdf', 'F');
echo 'PipelineInfoReport.pdf';
} else {
echo 'Processing Error';
}
}