0 GROUP BY branch, date
UNION ALL
SELECT
SUM(((sales.total_amount-IFNULL(sales.discount,0)+IFNULL(sales.total_vat,0)) * -1)) AS total_amount,
MONTH(sales.order_date) AS date,
sales.branch_id AS branch
FROM credit_memos AS sales
WHERE ".$condtion."YEAR(sales.order_date) = '{$dateInput}' AND sales.status > 0 GROUP BY branch, date
ORDER BY branch, date");
if(mysql_num_rows($sqlTrans)){
while($rowTrans = mysql_fetch_array($sqlTrans)){
$dateTrans = str_pad($rowTrans['date'], 2, '0', STR_PAD_LEFT);
if (array_key_exists($rowTrans['branch'], $datas)){
if (array_key_exists($dateTrans, $datas[$rowTrans['branch']])){
$datas[$rowTrans['branch']][$dateTrans] += $rowTrans['total_amount'];
} else {
$datas[$rowTrans['branch']][$dateTrans] = $rowTrans['total_amount'];
}
} else {
$datas[$rowTrans['branch']][$dateTrans] = $rowTrans['total_amount'];
}
}
}
} else if ($group == 2){
$sqlYearStart = mysql_query("SELECT YEAR(date) FROM inventories GROUP BY YEAR(date) ORDER BY YEAR(date) ASC LIMIT 1;");
if(mysql_num_rows($sqlYearStart)){
$rowYearStart = mysql_fetch_array($sqlYearStart);
$start = $rowYearStart[0];
} else {
$start = date("Y");
}
$yearNow = date("Y");
for($i = $start; $i <= $yearNow; $i++){
$dateHeader[] = $i;
}
$sqlTrans = mysql_query("SELECT
SUM(sales.total_amount-IFNULL(sales.discount,0)+IFNULL(sales.total_vat,0)) AS total_amount,
YEAR(sales.order_date) AS date,
sales.branch_id AS branch
FROM sales_orders AS sales
WHERE ".$condtion."YEAR(sales.order_date) >= '{$start}' AND YEAR(sales.order_date) <= '{$yearNow}' AND sales.status > 0 GROUP BY branch, date
UNION ALL
SELECT
SUM(((sales.total_amount-IFNULL(sales.discount,0)+IFNULL(sales.total_vat,0)) * -1)) AS total_amount,
YEAR(sales.order_date) AS date,
sales.branch_id AS branch
FROM credit_memos AS sales
WHERE ".$condtion."YEAR(sales.order_date) >= '{$start}' AND YEAR(sales.order_date) <= '{$yearNow}' AND sales.status > 0 GROUP BY branch, date
ORDER BY branch, date");
if(mysql_num_rows($sqlTrans)){
while($rowTrans = mysql_fetch_array($sqlTrans)){
$dateTrans = $rowTrans['date'];
if (array_key_exists($rowTrans['branch'], $datas)){
if (array_key_exists($dateTrans, $datas[$rowTrans['branch']])){
$datas[$rowTrans['branch']][$dateTrans] += $rowTrans['total_amount'];
} else {
$datas[$rowTrans['branch']][$dateTrans] = $rowTrans['total_amount'];
}
} else {
$datas[$rowTrans['branch']][$dateTrans] = $rowTrans['total_amount'];
}
}
}
} else {
$dateLists = listDays($_POST['date_from'], $_POST['date_to']);
$i = 1;
$count = count($dateLists);
foreach($dateLists AS $dateList){
$name = date("d/M", strtotime($dateList));
$dateHeader[] = $dateList;
}
$sqlTrans = mysql_query("SELECT
SUM(sales.total_amount-IFNULL(sales.discount,0)+IFNULL(sales.total_vat,0)) AS total_amount,
sales.order_date AS date,
sales.branch_id AS branch
FROM sales_orders AS sales
WHERE ".$condtion."sales.order_date >= '{$_POST['date_from']}' AND sales.order_date <= '{$_POST['date_to']}' AND sales.status > 0 GROUP BY branch, date
UNION ALL
SELECT
SUM(((sales.total_amount-IFNULL(sales.discount,0)+IFNULL(sales.total_vat,0)) * -1)) AS total_amount,
sales.order_date AS date,
sales.branch_id AS branch
FROM credit_memos AS sales
WHERE ".$condtion."sales.order_date >= '{$_POST['date_from']}' AND sales.order_date <= '{$_POST['date_to']}' AND sales.status > 0 GROUP BY branch, date
ORDER BY branch, date");
if(mysql_num_rows($sqlTrans)){
while($rowTrans = mysql_fetch_array($sqlTrans)){
$dateTrans = $rowTrans['date'];
if (array_key_exists($rowTrans['branch'], $datas)){
if (array_key_exists($dateTrans, $datas[$rowTrans['branch']])){
$datas[$rowTrans['branch']][$dateTrans] += $rowTrans['total_amount'];
} else {
$datas[$rowTrans['branch']][$dateTrans] = $rowTrans['total_amount'];
}
} else {
$datas[$rowTrans['branch']][$dateTrans] = $rowTrans['total_amount'];
}
}
}
}
$title = MENU_REPORT_INVOICE." (Summary)";
if(!empty($_POST['year']) && $group == 1){
$title .= " Year: ".$_POST['year'];
}
echo $this->element('/print/header-report',array('msg' => $title));
$filename = "public/report/total_sales_summary".$group. $user['User']['id'] . ".csv";
$fp = fopen($filename, "wb");
$excelContent = $title;
$excelContent .= "\n\n".TABLE_NO."\t" . TABLE_BRANCH;
$totalTblWidth = (COUNT($dateHeader) * 120) + 500;
?>