' . MENU_REPORT_FSN_ANALYSIS . '
';
$excelContent .= MENU_REPORT_ABC_ANALYSIS."\n\n";
$condition = '';
if($_POST['date_from']!='') {
$msg .= REPORT_FROM.': '.$_POST['date_from'];
$excelContent .= REPORT_FROM.': '.$_POST['date_from'];
$condition .= "inv_date.date >= '".dateConvert($_POST['date_from'])."'";
}
if($_POST['date_to']!='') {
$msg .= ' '.REPORT_TO.': '.$_POST['date_to'];
$excelContent .= ' '.REPORT_TO.': '.$_POST['date_to'];
$condition .= " AND inv_date.date <= '".dateConvert($_POST['date_to'])."'";
}
$msg .= '
';
$excelContent .= "\n\n";
echo $this->element('/print/header-report',array('msg'=>$msg));
// Total Begin Balance
$fsnAvgs = array();
$fsnCons = array();
$fsnFinal = array();
$totalAvg = 0;
$totalCon = 0;
$totalItems = 0;
$dateFrom = dateConvert($_POST['date_from']);
$dateTo = dateConvert($_POST['date_to']);
$dayDiff = getDays($dateFrom, $dateTo);
$sqlFsn = mysql_query("SELECT products.id AS product_id, products.code AS code, products.name AS name, SUM(inv_date.total_pb) AS totalRec, SUM(((inv_date.total_so + inv_date.total_pos) - inv_date.total_cm)) AS totalSales, SUM(inv_date.total_ending) AS totalEnd FROM inventory_total_by_dates AS inv_date INNER JOIN products ON products.id = inv_date.product_id WHERE ".$condition." GROUP BY inv_date.product_id;");
$i = 0;
if(mysql_num_rows($sqlFsn)){
$totalItems = mysql_num_rows($sqlFsn);
while($rowFsn = mysql_fetch_array($sqlFsn)){
$totalBegin = 0;
$avgStay = 0;
$consRate = 0;
$sqlBg = mysql_query("SELECT total_ending FROM inventory_total_by_dates WHERE date <= '".dateConvert($_POST['date_from'])."' AND product_id = ".$rowFsn['product_id']." ORDER BY date DESC LIMIT 1;");
if(mysql_num_rows($sqlBg)){
$rowBg = mysql_fetch_array($sqlBg);
$totalBegin = $rowBg[0];
}
if($rowFsn['totalEnd'] > 0){
$avgStay = $rowFsn['totalEnd'] / ($totalBegin + $rowFsn['totalRec']);
}
if($rowFsn['totalSales'] > 0){
$consRate = $rowFsn['totalSales'] / $dayDiff;
}
// AVG Stay
$fsnAvgs[$i]['pid'] = $rowFsn['product_id'];
$fsnAvgs[$i]['code'] = $rowFsn['code'];
$fsnAvgs[$i]['name'] = $rowFsn['name'];
$fsnAvgs[$i]['avg_stay'] = $avgStay;
$totalAvg += $avgStay>0?$avgStay:1;
// Cons Rate
$fsnCons[$i]['pid'] = $rowFsn['product_id'];
$fsnCons[$i]['code'] = $rowFsn['code'];
$fsnCons[$i]['name'] = $rowFsn['name'];
$fsnCons[$i]['con_rate'] = $consRate;
$totalCon += $consRate;
$i++;
}
}
// (%) Avg Stay
$j=0;
foreach($fsnAvgs AS $fsnAvg){
if($fsnAvg['avg_stay'] == 0){
$fsnAvgs[$j]['avg_percent'] = 0;
} else {
$fsnAvgs[$j]['avg_percent'] = $fsnAvg['avg_stay'] * 100 / $totalAvg;
}
$j++;
}
// (%) Cons Rate
$k=0;
foreach($fsnCons AS $fsnCon){
if($fsnCon['con_rate'] == 0){
$fsnCons[$k]['con_percent'] = 0;
} else {
$fsnCons[$k]['con_percent'] = $fsnCon['con_rate'] * 100 / $totalCon;
}
$k++;
}
// Sort
arraySortBy('avg_percent', $fsnAvgs, 'asc');
arraySortBy('con_percent', $fsnCons, 'asc');
?>