function generateStockStatusReport($conn) { global $gTEXT; $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->SetAutoPageBreak(true, 1); //$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM); $pdf->AddPage(); $pdf->SetFillColor(255, 255, 255); $html_head = "<span style='text-align:center;font-size:10px;'><b>" . $gTEXT['Stock Status at Different Level Report of'] . " " . $CountryName . " on " . $MonthName . ", " . $Year . "</b></span>"; $html = <<<EOF <!-- EXAMPLE OF CSS STYLE --> <style> </style> <body> </body> EOF; $pdf->writeHTMLCell(0, 0, 40, '', $html_head, '', 1, 1, false, 'L', true); $pdf->setSourceFile("pdfslice/StockStatusChart.pdf"); $tplIdx = $pdf->importPage(1); $pdf->useTemplate($tplIdx, 6, 20, 200, 300); $pdf->endPage(); //=====================================================Stock List Table======================================================= $Year = $_POST['Year']; $ItemGroupId = $_POST['ItemGroup']; $Month = $_POST['Month']; $CountryId = $_POST['Country']; $ownnerTypeId = $_POST['OwnnerTypeId']; $lan = $_REQUEST['lan']; if ($lan == 'en-GB') { $fLevelName = 'FLevelName'; } else { $fLevelName = 'FLevelNameFrench'; } if ($CountryId) { $CountryId = " AND a.CountryId = " . $CountryId . " "; } $columnList = array(); $productName = 'Product Name'; $aData = array(); if ($ownnerTypeId == 1 || $ownnerTypeId == 2) { $sQuery = "SELECT f.FLevelId, {$fLevelName} FLevelName, a.ItemNo, b.ItemName, f.ColorCode, IFNULL(SUM(ClStock),0) FacilitySOH, IFNULL(SUM(AMC),0) FacilityAMC\n\t\t\t, IFNULL(((SUM(ClStock))/(SUM(AMC))),0) MOS \n FROM t_cfm_stockstatus a \n INNER JOIN t_itemlist b ON a.ItemNo = b.ItemNo AND b.bKeyItem = 1 AND b.ItemGroupId = " . $ItemGroupId . "\n INNER JOIN t_cfm_masterstockstatus c ON a.CFMStockId = c.CFMStockId and c.StatusId = 5 AND c.ItemGroupId = " . $ItemGroupId . "\n INNER JOIN t_facility d ON a.FacilityId = d.FacilityId\n INNER JOIN t_facility_group_map e ON d.FacilityId = e.FacilityId AND e.ItemGroupId = " . $ItemGroupId . "\n INNER JOIN t_facility_level f ON d.FLevelId = f.FLevelId\n WHERE a.MonthId = " . $Month . " AND a.Year = '" . $Year . "' " . $CountryId . "\n\t\t\tAND d.OwnerTypeId = " . $ownnerTypeId . "\n GROUP BY f.FLevelId, {$fLevelName}, ItemNo, ItemName, f.ColorCode\n HAVING IFNULL(((SUM(ClStock))/(SUM(AMC))),0)>0\n\t\t\torder by ItemName,f.FLevelId;"; } else { $sQuery = "SELECT f.FLevelId, {$fLevelName} FLevelName, a.ItemNo, b.ItemName, f.ColorCode, IFNULL(SUM(ClStock),0) FacilitySOH, IFNULL(SUM(AMC),0) FacilityAMC\n\t\t\t\t, IFNULL(((SUM(ClStock))/(SUM(AMC))),0) MOS \n\t\t\t\tFROM t_cfm_stockstatus a \n\t\t\t\tINNER JOIN t_itemlist b ON a.ItemNo = b.ItemNo AND b.bKeyItem = 1 AND b.ItemGroupId = " . $ItemGroupId . "\n\t\t\t\tINNER JOIN t_cfm_masterstockstatus c ON a.CFMStockId = c.CFMStockId and c.StatusId = 5 AND c.ItemGroupId = " . $ItemGroupId . "\n\t\t\t\tINNER JOIN t_facility d ON a.FacilityId = d.FacilityId\n\t\t\t\tINNER JOIN t_facility_group_map e ON d.FacilityId = e.FacilityId AND e.ItemGroupId = " . $ItemGroupId . "\n\t\t\t\tINNER JOIN t_facility_level f ON d.FLevelId = f.FLevelId\n\t\t\t\tWHERE a.MonthId = " . $Month . " AND a.Year = '" . $Year . "' " . $CountryId . "\n\t\t\t\tAND d.AgentType = " . $ownnerTypeId . "\n\t\t\t\tGROUP BY f.FLevelId, {$fLevelName}, ItemNo, ItemName, f.ColorCode\n\t\t\t\tHAVING IFNULL(((SUM(ClStock))/(SUM(AMC))),0)>0\n\t\t\t\torder by ItemName,f.FLevelId;"; } //echo $sQuery; $rResult = safe_query($sQuery); $total = mysql_num_rows($rResult); $tmpItemName = ''; $sl = 1; $count = 0; $preItemName = ''; //echo 'Rubel'; if ($total > 0) { $data = array(); $headerList = array(); while ($row = mysql_fetch_assoc($rResult)) { $data[] = $row; } foreach ($data as $row) { ////Duplicate value not push in array //if (!in_array($row['FLevelName'], $headerList)) { // $headerList[] = $row['FLevelName']; //} $headerList[$row['FLevelId']] = $row['FLevelName']; } //array_push($headerList,'National'); $headerList[999] = 'National'; foreach ($headerList as $key => $value) { $columnList[] = $value; //.' Level AMC'; $columnList[] = $value; //.' Level SOH'; $columnList[] = $value; //.' Level MOS'; } $fetchDataList = array(); foreach ($data as $row) { if ($tmpItemName != $row['ItemName']) { if ($count > 0) { $fetchDataList['999' . '2'] = number_format($fetchDataList['999' . '2']); $fetchDataList['999' . '3'] = number_format($fetchDataList['999' . '3'], 1); array_unshift($fetchDataList, $sl, $preItemName); $aData[] = $fetchDataList; $sl++; } $count++; $preItemName = $row['ItemName']; unset($fetchDataList); foreach ($headerList as $key => $value) { $fetchDataList[$key . '1'] = NULL; $fetchDataList[$key . '2'] = NULL; $fetchDataList[$key . '3'] = NULL; } $tmpItemName = $row['ItemName']; } $fLevelId = $row['FLevelId']; $fetchDataList[$fLevelId . '1'] = number_format($row['FacilityAMC']); $fetchDataList[$fLevelId . '2'] = number_format($row['FacilitySOH']); $fetchDataList[$fLevelId . '3'] = number_format($row['MOS'], 1); if ($fetchDataList['999' . '1'] < $row['FacilityAMC']) { $fetchDataList['999' . '1'] = number_format($row['FacilityAMC']); } $fetchDataList['999' . '2'] += $row['FacilitySOH']; $fetchDataList['999' . '3'] += $row['MOS']; } $fetchDataList['999' . '2'] = number_format($fetchDataList['999' . '2']); $fetchDataList['999' . '3'] = number_format($fetchDataList['999' . '3'], 1); array_unshift($fetchDataList, $sl, $preItemName); $aData[] = $fetchDataList; $col = ''; $col .= ' <tr><th rowspan="2" style="text-align:center; width:5%;"><b>SL</b></th> <th rowspan="2" style="text-align:center; width:10%;"><b>' . $gTEXT['Product Name'] . '</b></th>'; $Header = '-1'; for ($i = 0; $i < count($columnList); $i++) { if ($Header != $columnList[$i]) { $col .= '<th colspan="3" style="text-align:center;width:90px;"><b>' . $columnList[$i] . '</b></th>'; $Header = $columnList[$i]; } } $index = 0; $col .= '</tr><tr>'; for ($i = 0; $i < count($columnList); $i++) { $index++; if ($index == 1) { $col .= '<th style="text-align:left; ">AMC</th>'; } else { if ($index == 2) { $col .= '<th style="text-align:left; ">SOH</th>'; } else { if ($index == 3) { $col .= '<th style="text-align:left; ">' . $gTEXT['MOS'] . '</th>'; } } } if ($index == 3) { $index = 0; } } $col .= '</tr>'; $data = ''; for ($p = 0; $p < count($aData); $p++) { $data .= '<tr>'; for ($i = 0; $i < count($aData[$p]); $i++) { $data .= '<td>' . $aData[$p][$i] . '</td>'; } $data .= '</tr>'; } $pdf->startPage(); $html_head = "<span><b>" . $gTEXT['Stock Status at Different Level Data List'] . "</b></span>"; $pdf->SetFont('dejavusans', '', 9); $pdf->writeHTMLCell(0, 0, 3, 10, $html_head, '', 0, 0, false, 'C', true); $html = ' <!-- EXAMPLE OF CSS STYLE --> <style> td{ height: 6px; line-height:3px; } th{ height:20; font-size:6px; } </style> <body> <table width="510px" border="0.5" style="margin:0 auto;"> ' . $col . '' . $data . '</table> </body>'; $pdf->SetFont('dejavusans', '', 6); $pdf->writeHTMLCell(0, 0, '', 20, $html, '', 1, 1, false, 'L', true); $pdf->endPage(); $filePath = SITEDOCUMENT . 'administrator/components/com_jcode/source/report/pdfslice/StockStatusatDifferentLevelReport.pdf'; if (file_exists($filePath)) { unlink($filePath); } $pdf->Output('pdfslice/StockStatusatDifferentLevelReport.pdf', 'F'); echo 'StockStatusatDifferentLevelReport.pdf'; } else { echo 'Processing Error'; } }
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'; } }