function getStockOutTrendData()
{
global $sqlParameterList;
global $lan;
$StartMonthId = $sqlParameterList[0];
$StartYearId = $sqlParameterList[1];
$EndMonthId = $sqlParameterList[2];
$EndYearId = $sqlParameterList[3];
$CountryId = $sqlParameterList[4];
$ItemGroupId = $sqlParameterList[5];
$OwnerTypeId = $sqlParameterList[6];
$months = $sqlParameterList[7];
//echo $itemGroupId;
if ($lan == 'en-GB') {
$mosTypeName = 'MosTypeName';
$lblMOSTypeName = 'MOS Type Name';
} else {
$mosTypeName = 'MosTypeNameFrench';
$lblMOSTypeName = 'Type MSD Nom';
}
if ($months != 0) {
$monthIndex = date("n");
$yearIndex = date("Y");
if ($monthIndex == 1) {
$monthIndex = 12;
$yearIndex = $yearIndex - 1;
} else {
$monthIndex = $monthIndex - 1;
}
} else {
$startDate = $StartYearId . "-" . $StartMonthId . "-" . "01";
$endDate = $EndYearId . "-" . $EndMonthId . "-" . "01";
$months = getMonthsBtnTwoDate($startDate, $endDate) + 1;
$monthIndex = $EndMonthId;
$yearIndex = $EndYearId;
}
settype($yearIndex, "integer");
$month_name = array();
$Tdetails = array();
$sumRiskCount = array();
$sumTR = 0;
for ($i = 1; $i <= $months; $i++) {
/*$sql = " SELECT v.MosTypeId, $mosTypeName MosTypeName, ColorCode, IFNULL(RiskCount,0) RiskCount FROM
(SELECT p.MosTypeId, COUNT(*) RiskCount FROM (
SELECT a.ItemNo, a.MOS,(SELECT MosTypeId FROM t_mostype x WHERE a.MOS >= x.MinMos AND a.MOS < x.MaxMos) MosTypeId
FROM t_cnm_stockstatus a
WHERE a.MOS IS NOT NULL AND a.MonthId = ".$monthIndex. " AND Year = ".$yearIndex." AND (CountryId = ".$CountryId." OR ".$CountryId." = 0)) p
GROUP BY p.MosTypeId) u
RIGHT JOIN t_mostype v ON u.MosTypeId = v.MosTypeId
GROUP BY v.MosTypeId"; */
//$ItemGroupId = $_POST['ItemGroup'];
if ($ItemGroupId > 0) {
$sql = " SELECT v.MosTypeId, {$mosTypeName} MosTypeName, ColorCode, IFNULL(RiskCount,0) RiskCount FROM\n \t\t (SELECT p.MosTypeId, COUNT(*) RiskCount FROM (\n SELECT a.ItemNo, a.MOS,(SELECT MosTypeId FROM t_mostype x WHERE a.MOS >= x.MinMos AND a.MOS < x.MaxMos) MosTypeId\n\t\t\t\t FROM t_cnm_stockstatus a\n INNER JOIN t_itemlist b ON a.ItemNo = b.ItemNo \n\t\t\t\t\t AND b.ItemGroupId = " . $ItemGroupId . "\n\t\t\t\t WHERE a.MOS IS NOT NULL \n\t\t\t\t\t AND a.MonthId = " . $monthIndex . " \n\t\t\t\t\t AND Year = " . $yearIndex . "\n\t\t\t\t\t AND a.OwnerTypeId = " . $OwnerTypeId . " \n AND (CountryId = " . $CountryId . " OR " . $CountryId . " = 0)) p \n\t\t\t\t GROUP BY p.MosTypeId) u\n\t\t\t\t RIGHT JOIN t_mostype v ON u.MosTypeId = v.MosTypeId\n\t\t\t\t GROUP BY v.MosTypeId";
} else {
$sql = " SELECT v.MosTypeId, {$mosTypeName} MosTypeName, ColorCode, IFNULL(RiskCount,0) RiskCount FROM\n \t\t (SELECT p.MosTypeId, COUNT(*) RiskCount FROM (\n SELECT a.ItemNo, a.MOS,(SELECT MosTypeId FROM t_mostype x WHERE a.MOS >= x.MinMos AND a.MOS < x.MaxMos) MosTypeId\n\t\t\t\t FROM t_cnm_stockstatus a\n INNER JOIN t_itemlist b ON a.ItemNo = b.ItemNo \n\t\t\t\t\t AND b.bCommonBasket = 1\n\t\t\t\t WHERE a.MOS IS NOT NULL \n\t\t\t\t\t AND a.MonthId = " . $monthIndex . " \n\t\t\t\t\t AND Year = " . $yearIndex . "\n\t\t\t\t\t AND a.OwnerTypeId = " . $OwnerTypeId . " \t\t\t\t\t\n AND (CountryId = " . $CountryId . " OR " . $CountryId . " = 0)) p \n\t\t\t\t GROUP BY p.MosTypeId) u\n\t\t\t\t RIGHT JOIN t_mostype v ON u.MosTypeId = v.MosTypeId\n\t\t\t\t GROUP BY v.MosTypeId;";
}
//echo $sql.' imhere ok ';
$result = mysql_query($sql);
$total = mysql_num_rows($result);
$Pdetails = array();
if ($total > 0) {
while ($aRow = mysql_fetch_array($result)) {
$Pdetails['MosTypeId'] = $aRow['MosTypeId'];
$Pdetails['MonthIndex'] = $monthIndex;
$Pdetails['MosTypeName'] = $aRow['MosTypeName'];
$Pdetails['RiskCount'] = $aRow['RiskCount'];
array_push($Tdetails, $Pdetails);
}
$mn = date("M", mktime(0, 0, 0, $monthIndex, 1, 0));
$mn = $mn . " " . $yearIndex;
array_push($month_name, $mn);
}
$monthIndex--;
if ($monthIndex == 0) {
$monthIndex = 12;
$yearIndex = $yearIndex - 1;
}
}
$veryHighRisk = array();
$highRisk = array();
$mediumRisk = array();
$lowRisk = array();
$noRisk = array();
$areaName = array();
$rmonth_name = array_reverse($month_name);
$RTdetails = array_reverse($Tdetails);
foreach ($RTdetails as $key => $value) {
$MosTypeId = $value['MosTypeId'];
$MonthIndex = $value['MonthIndex'];
$MosTypeName = $value['MosTypeName'];
$RiskCount = $value['RiskCount'];
if ($MosTypeId == 1) {
array_push($veryHighRisk, $RiskCount);
array_push($areaName, $MosTypeName);
} else {
if ($MosTypeId == 2) {
array_push($highRisk, $RiskCount);
array_push($areaName, $MosTypeName);
} else {
if ($MosTypeId == 3) {
array_push($mediumRisk, $RiskCount);
array_push($areaName, $MosTypeName);
} else {
if ($MosTypeId == 4) {
array_push($lowRisk, $RiskCount);
array_push($areaName, $MosTypeName);
} else {
if ($MosTypeId == 5) {
array_push($noRisk, $RiskCount);
array_push($areaName, $MosTypeName);
}
}
}
}
}
}
$vhr = array();
$hr = array();
$mr = array();
$lr = array();
$nr = array();
for ($i = 0; $i < count($veryHighRisk); $i++) {
$sumOfRiskCount = $veryHighRisk[$i] + $highRisk[$i] + $mediumRisk[$i] + $lowRisk[$i] + $noRisk[$i];
if ($sumOfRiskCount == 0) {
$sumOfRiskCount = 1;
}
$newPercentVHR = number_format($veryHighRisk[$i] * 100 / $sumOfRiskCount, 1);
$newPercentHR = number_format($highRisk[$i] * 100 / $sumOfRiskCount, 1);
$newPercentMR = number_format($mediumRisk[$i] * 100 / $sumOfRiskCount, 1);
$newPercentLR = number_format($lowRisk[$i] * 100 / $sumOfRiskCount, 1);
$newPercentNR = number_format($noRisk[$i] * 100 / $sumOfRiskCount, 1);
array_push($vhr, $newPercentVHR . "%");
array_push($hr, $newPercentHR . "%");
array_push($mr, $newPercentMR . "%");
array_push($lr, $newPercentLR . "%");
array_push($nr, $newPercentNR . "%");
}
$unique = array_reverse(array_unique($areaName));
array_unshift($vhr, "1", $unique[0]);
array_unshift($hr, "2", $unique[1]);
array_unshift($mr, "3", $unique[2]);
array_unshift($lr, "4", $unique[3]);
array_unshift($nr, "5", $unique[4]);
global $datalist;
$datalist[] = $vhr;
$datalist[] = $hr;
$datalist[] = $mr;
$datalist[] = $lr;
$datalist[] = $nr;
}
if ($_GET['MonthNumber'] != 0) {
$months = $_GET['MonthNumber'];
$monthIndex = date("n");
$yearIndex = date("Y");
if ($monthIndex == 1) {
$monthIndex = 12;
$yearIndex = $yearIndex - 1;
} else {
$monthIndex = $monthIndex - 1;
$endDate = $yearIndex . "-" . $monthIndex . "-" . "01";
$startDate = date("Y-m-d", strtotime(date("Y-m-d", strtotime($endDate)) . "+" . -($months - 1) . " month"));
}
} else {
$startDate = $StartYearId . "-" . $StartMonthId . "-" . "01";
$endDate = $EndYearId . "-" . $EndMonthId . "-" . "01";
$months = getMonthsBtnTwoDate($startDate, $endDate) + 1;
$monthIndex = $EndMonthId;
$yearIndex = $EndYearId;
}
settype($yearIndex, "integer");
$month_name = array();
$Tdetails = array();
$sumRiskCount = array();
$sumTR = 0;
for ($i = 1; $i <= $months; $i++) {
$sql = " SELECT v.MosTypeId, {$mosTypeName} MosTypeName, ColorCode, IFNULL(RiskCount,0) RiskCount FROM\r\n \t\t (SELECT p.MosTypeId, COUNT(*) RiskCount FROM (\r\n SELECT a.ItemNo, a.MOS,(SELECT MosTypeId FROM t_mostype x WHERE a.MOS >= x.MinMos AND a.MOS < x.MaxMos) MosTypeId\r\n\t\t\t\t FROM t_cnm_stockstatus a\r\n\t\t\t\t WHERE a.MOS IS NOT NULL AND a.MonthId = " . $monthIndex . " AND Year = " . $yearIndex . " AND (CountryId = " . $CountryId . " OR " . $CountryId . " = 0)) p \r\n\t\t\t\t GROUP BY p.MosTypeId) u\r\n\t\t\t\t RIGHT JOIN t_mostype v ON u.MosTypeId = v.MosTypeId\r\n\t\t\t\t GROUP BY v.MosTypeId";
mysql_query("SET character_set_results=utf8");
$result = mysql_query($sql);
$total = mysql_num_rows($result);
$Pdetails = array();
if ($total > 0) {
function generateStockoutTrendReport($conn)
{
global $gTEXT;
$CountryId = $_POST['Country'];
$months = $_POST['MonthNumber'];
$StartMonthId = $_POST['StartMonthId'];
$EndMonthId = $_POST['EndMonthId'];
$StartYearId = $_POST['StartYearId'];
$EndYearId = $_POST['EndYearId'];
$CountryName = $_POST['CountryName'];
$MonthName = $_GET['MonthName'];
if ($_POST['MonthNumber'] != 0) {
$months = $_POST['MonthNumber'];
$monthIndex = date("m");
$yearIndex = date("Y");
if ($monthIndex == 1) {
$monthIndex = 12;
$yearIndex = $yearIndex - 1;
} else {
$monthIndex = $monthIndex - 1;
$endDate = $yearIndex . "-" . $monthIndex . "-" . "01";
$startDate = date("Y-m-d", strtotime(date("Y-m-d", strtotime($endDate)) . "+" . -($months - 1) . " month"));
}
} else {
$startDate = $StartYearId . "-" . $StartMonthId . "-" . "01";
$endDate = $EndYearId . "-" . $EndMonthId . "-" . "01";
$months = getMonthsBtnTwoDate($startDate, $endDate) + 1;
$monthIndex = $EndMonthId;
$yearIndex = $EndYearId;
}
settype($yearIndex, "integer");
$month_name = array();
$Tdetails = array();
$sumRiskCount = array();
$sumTR = 0;
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 = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
</style>
<body>
<h4 style="text-align:left;"><b>' . $gTEXT['Stockout trend Report of '] . ' ' . $CountryName . ' ' . $gTEXT['from'] . ' ' . date('M,Y', strtotime($startDate)) . ' ' . $gTEXT['to'] . ' ' . date('M,Y', strtotime($endDate)) . '</b></h4>
</body>';
$pdf->writeHTMLCell(0, 0, 12, '', $html_head, '', 1, 1, false, 'L', true, $spacing = 0);
$pdf->setSourceFile("pdfslice/StockoutTrendChart.pdf");
print_r();
$tplIdx = $pdf->importPage(1);
$pdf->useTemplate($tplIdx, -5, 0, 200);
//=====================================================Stockout Trend Table=======================================================
for ($i = 1; $i <= $months; $i++) {
$sql = " SELECT v.MosTypeId, MosTypeName, ColorCode, IFNULL(RiskCount,0) RiskCount FROM\n \t\t (SELECT p.MosTypeId, COUNT(*) RiskCount FROM (\n SELECT a.ItemNo, a.MOS,(SELECT MosTypeId FROM t_mostype x WHERE a.MOS >= x.MinMos AND a.MOS < x.MaxMos) MosTypeId\n\t\t\t\t FROM t_cnm_stockstatus a\n\t\t\t\t WHERE a.MOS IS NOT NULL AND a.MonthId = " . $monthIndex . " AND Year = " . $yearIndex . " AND (CountryId = " . $CountryId . " OR " . $CountryId . " = 0)) p \n\t\t\t\t GROUP BY p.MosTypeId) u\n\t\t\t\t RIGHT JOIN t_mostype v ON u.MosTypeId = v.MosTypeId\n\t\t\t\t GROUP BY v.MosTypeId";
mysql_query("SET character_set_results=utf8");
$result = mysql_query($sql);
$total = mysql_num_rows($result);
$Pdetails = array();
if ($total > 0) {
while ($aRow = mysql_fetch_array($result)) {
$Pdetails['MosTypeId'] = $aRow['MosTypeId'];
$Pdetails['MonthIndex'] = $monthIndex;
$Pdetails['MosTypeName'] = $aRow['MosTypeName'];
$Pdetails['RiskCount'] = $aRow['RiskCount'];
array_push($Tdetails, $Pdetails);
}
$mn = date("M", mktime(0, 0, 0, $monthIndex, 1, 0));
$mn = $mn . " " . $yearIndex;
array_push($month_name, $mn);
}
$monthIndex--;
if ($monthIndex == 0) {
$monthIndex = 12;
$yearIndex = $yearIndex - 1;
}
}
$veryHighRisk = array();
$highRisk = array();
$mediumRisk = array();
$lowRisk = array();
$noRisk = array();
$areaName = array();
$rmonth_name = array_reverse($month_name);
$RTdetails = array_reverse($Tdetails);
foreach ($RTdetails as $key => $value) {
$MosTypeId = $value['MosTypeId'];
$MonthIndex = $value['MonthIndex'];
$MosTypeName = $value['MosTypeName'];
$RiskCount = $value['RiskCount'];
if ($MosTypeId == 1) {
array_push($veryHighRisk, $RiskCount);
array_push($areaName, $MosTypeName);
} else {
if ($MosTypeId == 2) {
array_push($highRisk, $RiskCount);
array_push($areaName, $MosTypeName);
} else {
if ($MosTypeId == 3) {
array_push($mediumRisk, $RiskCount);
array_push($areaName, $MosTypeName);
} else {
if ($MosTypeId == 4) {
array_push($lowRisk, $RiskCount);
array_push($areaName, $MosTypeName);
} else {
if ($MosTypeId == 5) {
array_push($noRisk, $RiskCount);
array_push($areaName, $MosTypeName);
}
}
}
}
}
}
$vhr = array();
$hr = array();
$mr = array();
$lr = array();
$nr = array();
for ($i = 0; $i < count($veryHighRisk); $i++) {
$sumOfRiskCount = $veryHighRisk[$i] + $highRisk[$i] + $mediumRisk[$i] + $lowRisk[$i] + $noRisk[$i];
if ($sumOfRiskCount == 0) {
$sumOfRiskCount = 1;
}
$newPercentVHR = number_format($veryHighRisk[$i] * 100 / $sumOfRiskCount, 1);
$newPercentHR = number_format($highRisk[$i] * 100 / $sumOfRiskCount, 1);
$newPercentMR = number_format($mediumRisk[$i] * 100 / $sumOfRiskCount, 1);
$newPercentLR = number_format($lowRisk[$i] * 100 / $sumOfRiskCount, 1);
$newPercentNR = number_format($noRisk[$i] * 100 / $sumOfRiskCount, 1);
array_push($vhr, $newPercentVHR . "%");
array_push($hr, $newPercentHR . "%");
array_push($mr, $newPercentMR . "%");
array_push($lr, $newPercentLR . "%");
array_push($nr, $newPercentNR . "%");
}
$unique = array_reverse(array_unique($areaName));
array_unshift($vhr, "1", $unique[0]);
array_unshift($hr, "2", $unique[1]);
array_unshift($mr, "3", $unique[2]);
array_unshift($lr, "4", $unique[3]);
array_unshift($nr, "5", $unique[4]);
$col = '';
$col = '<tr><th width="38" align="left"><b>SL</b></th>';
$col .= '<th width="38" align="left"><b>' . $gTEXT['MOS Type Name'] . '</b></th>';
$f = 0;
for ($f = 0; $f < count($rmonth_name); $f++) {
$col .= '<th width="38" align="right"><b>' . $rmonth_name[$f] . '</b></th>';
}
$col .= '</tr><tr>';
$x = 0;
for ($x = 0; $x < count($vhr); $x++) {
$col .= '<td width="38" align="left"><b>' . $vhr[$x] . '</b></td>';
}
$col .= '</tr><tr>';
$x = 0;
for ($x = 0; $x < count($hr); $x++) {
$col .= '<td width="38" align="left"><b>' . $hr[$x] . '</b></td>';
}
$col .= '</tr><tr>';
$x = 0;
for ($x = 0; $x < count($mr); $x++) {
$col .= '<td width="38" align="left"><b>' . $mr[$x] . '</b></td>';
}
$col .= '</tr><tr>';
$x = 0;
for ($x = 0; $x < count($lr); $x++) {
$col .= '<td width="38" align="left"><b>' . $lr[$x] . '</b></td>';
}
$col .= '</tr><tr>';
$x = 0;
for ($x = 0; $x < count($nr); $x++) {
$col .= '<td width="38" align="left"><b>' . $nr[$x] . '</b></td>';
}
$col .= '</tr>';
$html_head = "<span><b>" . $gTEXT['Stockout Trend Data List'] . "</b></span>";
$pdf->SetFont('dejavusans', '', 9);
$pdf->writeHTMLCell(0, 0, 12, 110, $html_head, '', 0, 0, false, 'L', true);
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
td{
height: 6px;
line-height:3px;
}
</style>
<body>
<table width="550px" border="0.5" style="margin:0px auto;">' . $col . '</table></body>';
$pdf->SetFont('dejavusans', '', 7);
$pdf->writeHTMLCell(0, 0, 10, 120, $html, '', 1, 1, false, 'C', true);
$filePath = SITEDOCUMENT . 'administrator/components/com_jcode/source/report/pdfslice/StockoutTrendReport.pdf';
if (file_exists($filePath)) {
unlink($filePath);
}
$pdf->Output('pdfslice/StockoutTrendReport.pdf', 'F');
echo 'StockoutTrendReport.pdf';
}