' . MENU_REPORT_XYZ_ANALYSIS . '
';
$excelContent .= MENU_REPORT_XYZ_ANALYSIS."\n\n";
$condition = '';
if($_POST['year']!='') {
$msg .= TABLE_YEAR.': '.$_POST['year']."
";
$excelContent .= TABLE_YEAR.': '.$_POST['year'];
$condition .= "YEAR(inv_date.date) = '".$_POST['year']."'";
}
if($_POST['date_from']!='') {
$msg .= REPORT_FROM.': '.convertMonthToEnglish($_POST['date_from']);
$excelContent .= REPORT_FROM.': '.convertMonthToEnglish($_POST['date_from']);
$condition .= " AND MONTH(inv_date.date) >= '".$_POST['date_from']."'";
}
if($_POST['date_to']!='') {
$msg .= ' '.REPORT_TO.': '.convertMonthToEnglish($_POST['date_to']);
$excelContent .= ' '.REPORT_TO.': '.convertMonthToEnglish($_POST['date_to']);
$condition .= " AND MONTH(inv_date.date) <= '".$_POST['date_to']."'";
}
$msg .= '
';
$excelContent .= "\n\n";
echo $this->element('/print/header-report',array('msg'=>$msg));
// Total Cost
$totalMonth = ($_POST['date_to'] - $_POST['date_from']) + 1;
$itemInfo = array();
$itemFinish = array();
$currentId = 0;
$sqlSales = mysql_query("SELECT products.id AS pid, products.code AS code, products.name AS name, SUM((inv_date.total_pos + inv_date.total_so)) AS totalSales FROM inventory_total_by_dates AS inv_date INNER JOIN products ON products.id = inv_date.product_id WHERE ".$condition." GROUP BY MONTH(inv_date.date), inv_date.product_id ORDER BY inv_date.product_id ASC;");
if(mysql_num_rows($sqlSales)){
while($rowSales = mysql_fetch_array($sqlSales)){
if($currentId != $rowSales['pid']){
$i = 0;
}
if(array_key_exists($rowSales['pid'], $itemInfo)) {
$itemInfo[$rowSales['pid']]['totalSales'] += replaceThousand(number_format($rowSales['totalSales'], 0));
} else {
$itemInfo[$rowSales['pid']]['totalSales'] = replaceThousand(number_format($rowSales['totalSales'], 0));
}
$itemInfo[$rowSales['pid']]['sales'][$i] = $rowSales['totalSales'];
$itemInfo[$rowSales['pid']]['code'] = $rowSales['code'];
$itemInfo[$rowSales['pid']]['name'] = $rowSales['name'];
$currentId = $rowSales['pid'];
$i++;
}
}
if(!empty($itemInfo)){
foreach($itemInfo AS $key => $info) {
$avgComs = replaceThousand(number_format(($info['totalSales'] / $totalMonth), 0));
$standardDiv = 0;
$sumItem = 0;
foreach($info['sales'] AS $item){
$value = abs($item - $avgComs) * abs($item - $avgComs);
$sumItem += $value;
}
if($sumItem > 0){
$standardDiv = replaceThousand(number_format(sqrt(($sumItem / $totalMonth)), 0));
}
$itemFinish[$key]['code'] = $info['code'];
$itemFinish[$key]['name'] = $info['name'];
$itemFinish[$key]['totalSales'] = $info['totalSales'];
$itemFinish[$key]['avgCons'] = $avgComs;
$itemFinish[$key]['stanDev'] = $standardDiv;
$itemFinish[$key]['stanDevP'] = number_format(($standardDiv / $avgComs) * 100, 2);
}
}
arraySortBy('stanDevP', $itemFinish);
?>