' . MENU_INVENTORY_CONSIGNMENT . '
';
$excelContent .= MENU_INVENTORY_CONSIGNMENT."\n\n";
if($_POST['date_from']!='') {
$msg .= REPORT_FROM.': '.$_POST['date_from'];
$excelContent .= REPORT_FROM.': '.$_POST['date_from'];
}
if($_POST['date_to']!='') {
$msg .= ' '.REPORT_TO.': '.$_POST['date_to'];
$excelContent .= ' '.REPORT_TO.': '.$_POST['date_to'];
}
if($_POST['location_group_id']!='' || $_POST['pgroup_id']!='') {
$msg .= '
';
$excelContent .= "\n\n";
}
if($_POST['location_group_id']!='') {
$query=mysql_query("SELECT name FROM location_groups WHERE id=".$_POST['location_group_id']);
$data=mysql_fetch_array($query);
$msg .= '
'.TABLE_COMPANY.': '.$data[0];
$excelContent .= TABLE_COMPANY.': '.$data[0];
}
if($_POST['pgroup_id']!='') {
$query=mysql_query("SELECT name FROM pgroups WHERE id=".$_POST['pgroup_id']);
$data=mysql_fetch_array($query);
$msg .= '
'.GENERAL_TYPE.': '.$data[0];
$excelContent .= GENERAL_TYPE.': '.$data[0];
}
$msg .= '
';
$excelContent .= "\n\n";
echo $this->element('/print/header-report',array('msg'=>$msg));
if($rowSettingUomDetail[0] == 0){
$excelLots = '';
} else {
$excelLots = '\t'.TABLE_LOTS_NO;
}
$excelContent .= TABLE_NO."\t".TABLE_SKU."\t".TABLE_LOCATION.$excelLots."\t".TABLE_EXPIRED_DATE."\t".'Beginning'."\t".'Consignment In'."\t".'Consignment Out'."\t".'Sales'."\t".'Ending'."\t".TABLE_TOTAL_ORDER."\t".TABLE_UOM;
?>
|
|
|
|
|
Beginning |
Consignment In |
Consignment Out |
Sales |
Ending |
|
|
0 GROUP BY location_groups.id)";
}
// Get Product Group
if($col[4] != ""){
// Get Product
if($col[6] != ""){
$conProduct = " AND product_id = ".$col[6];
}
$conPGroup = " AND products.id IN (SELECT product_id FROM product_pgroups WHERE pgroup_id = ".$col[4].$conProduct.")";
}
// Get Product Parent
if($col[5] != ""){
$conParent = " AND products.parent_id =".$col[5];
}
// Get Product
if($col[6] != ""){
if($conPGroup == ""){
$conPGroup = " AND products.id IN (".$col[6].")";
}
}
$joinProducts = " INNER JOIN products ON";
$joinUom = " INNER JOIN uoms ON uoms.id = products.price_uom_id";
$tableDaily = "";
$tableDailyBbi = "";
$filedDailyBbi = "SUM((total_pb + total_cm + total_to_in + total_cycle + total_cus_consign_in + total_ven_consign_in) - (total_so + total_pbc + total_pos + total_to_out + total_cus_consign_out + total_ven_consign_out)) AS total_qty, SUM(total_pb) AS total_pb, SUM(total_cm) AS total_cm, SUM(total_to_in) AS total_to_in, SUM(total_cycle) AS total_cycle, SUM(total_so) AS total_so, SUM(total_pbc) AS total_pbc, SUM(total_pos) AS total_pos, SUM(total_to_out) AS total_to_out, SUM(total_cus_consign_in) AS total_cus_consign_in, SUM(total_cus_consign_out) AS total_cus_consign_out, SUM(total_ven_consign_in) AS total_ven_consign_in, SUM(total_ven_consign_out) AS total_ven_consign_out, SUM(total_order) AS total_order, product_id, location_id, lots_number, expired_date";
$filedDaily = "SUM(total_pb) AS total_pb, SUM(total_cm) AS total_cm, SUM(total_to_in) AS total_to_in, SUM(total_cycle) AS total_cycle, SUM(total_so) AS total_so, SUM(total_pbc) AS total_pbc, SUM(total_pos) AS total_pos, SUM(total_to_out) AS total_to_out, SUM(total_cus_consign_in) AS total_cus_consign_in, SUM(total_cus_consign_out) AS total_cus_consign_out, SUM(total_ven_consign_in) AS total_ven_consign_in, SUM(total_ven_consign_out) AS total_ven_consign_out, SUM(total_order) AS total_order, uoms.name AS u_name, products.price_uom_id AS qty_uom_id, products.code AS product_code, products.name AS product_name, products.id AS product_id, location_id, (SELECT name FROM locations WHERE id = location_id) AS location_name, lots_number, expired_date";
$conditionDaily = " products.is_active = 1 AND DayFilter ".$conPGroup.$conVendor.$conParent.$conCompany;
$conditionDailyBbi = " products.is_active = 1 AND DayFilter AND changeId AND locationId AND lotsNumber AND expDate ".$conParent.$conCompany;
// List Location
$queryLocationList = mysql_query('SELECT id AS location_id FROM locations WHERE ' . $conditionGetLocation . ' GROUP BY id');
if(@mysql_num_rows($queryLocationList)){
if(mysql_num_rows($queryLocationList) == 1){
while($dataLocationList=mysql_fetch_array($queryLocationList)){
// Stock Daily Bigging
$tableDailyBbi .= "SELECT ".$filedDailyBbi." FROM ".$dataLocationList['location_id']."_inventory_total_details".$joinProducts." products.id = ".$dataLocationList['location_id']."_inventory_total_details.product_id WHERE".$conditionDailyBbi." GROUP BY ".$dataLocationList['location_id']."_inventory_total_details.location_id, ".$dataLocationList['location_id']."_inventory_total_details.lots_number, ".$dataLocationList['location_id']."_inventory_total_details.expired_date, ".$dataLocationList['location_id']."_inventory_total_details.product_id";
// Stock Daily Ending
$tableDaily .= "SELECT ".$filedDaily." FROM ".$dataLocationList['location_id']."_inventory_total_details".$joinProducts." products.id = ".$dataLocationList['location_id']."_inventory_total_details.product_id".$joinUom." WHERE".$conditionDaily." GROUP BY ".$dataLocationList['location_id']."_inventory_total_details.location_id, ".$dataLocationList['location_id']."_inventory_total_details.lots_number, ".$dataLocationList['location_id']."_inventory_total_details.expired_date, ".$dataLocationList['location_id']."_inventory_total_details.product_id";
}
}else{
$locId = 1;
$tmpLocationId = 0;
while($dataLocationList=mysql_fetch_array($queryLocationList)){
if($locId == 1){
// Stock Daily Bigging
$tableDailyBbi .= "SELECT ".$filedDailyBbi." FROM ".$dataLocationList['location_id']."_inventory_total_details".$joinProducts." products.id = ".$dataLocationList['location_id']."_inventory_total_details.product_id WHERE".$conditionDailyBbi." GROUP BY ".$dataLocationList['location_id']."_inventory_total_details.location_id, ".$dataLocationList['location_id']."_inventory_total_details.lots_number, ".$dataLocationList['location_id']."_inventory_total_details.expired_date, ".$dataLocationList['location_id']."_inventory_total_details.product_id";
// Stock Daily Ending
$tableDaily .= "SELECT ".$filedDaily." FROM ".$dataLocationList['location_id']."_inventory_total_details".$joinProducts." products.id = ".$dataLocationList['location_id']."_inventory_total_details.product_id".$joinUom." WHERE ".$conditionDaily." GROUP BY ".$dataLocationList['location_id']."_inventory_total_details.location_id, ".$dataLocationList['location_id']."_inventory_total_details.lots_number, ".$dataLocationList['location_id']."_inventory_total_details.expired_date, ".$dataLocationList['location_id']."_inventory_total_details.product_id";
}else{
// Stock Daily Ending
$tableDailyBbi .= " UNION ALL SELECT ".$filedDailyBbi." FROM ".$dataLocationList['location_id']."_inventory_total_details".$joinProducts." products.id = ".$dataLocationList['location_id']."_inventory_total_details.product_id WHERE ".$conditionDailyBbi." GROUP BY ".$dataLocationList['location_id']."_inventory_total_details.location_id, ".$dataLocationList['location_id']."_inventory_total_details.lots_number, ".$dataLocationList['location_id']."_inventory_total_details.expired_date, ".$dataLocationList['location_id']."_inventory_total_details.product_id";
// Stock Daily Ending
$tableDaily .= " UNION ALL SELECT ".$filedDaily." FROM ".$dataLocationList['location_id']."_inventory_total_details".$joinProducts." products.id = ".$dataLocationList['location_id']."_inventory_total_details.product_id".$joinUom." WHERE ".$conditionDaily." GROUP BY ".$dataLocationList['location_id']."_inventory_total_details.location_id, ".$dataLocationList['location_id']."_inventory_total_details.lots_number, ".$dataLocationList['location_id']."_inventory_total_details.expired_date, ".$dataLocationList['location_id']."_inventory_total_details.product_id";
}
$tmpLocationId = $dataLocationList['location_id'];
$locId++;
}
}
$dateFrom = "date<'".dateConvert($col[1])."'";
$dateTo = "DATE(date)<='".dateConvert($col[2])."'";
$tableDailyBeginning = str_replace("DayFilter", $dateFrom, $tableDailyBbi);
$tableDailyEnding = str_replace("DayFilter", $dateTo, $tableDaily);
$sqlCmtDailyBiginning = "SELECT SUM(total_qty) AS qty, SUM(total_pb) AS total_pb, SUM(total_cm) AS total_cm, SUM(total_to_in) AS total_to_in, SUM(total_cycle) AS total_cycle, SUM(total_so) AS total_so, SUM(total_pbc) AS total_pbc, SUM(total_pos) AS total_pos, SUM(total_to_out) AS total_to_out, SUM(total_cus_consign_in) AS total_cus_consign_in, SUM(total_cus_consign_out) AS total_cus_consign_out, SUM(total_ven_consign_in) AS total_ven_consign_in, SUM(total_ven_consign_out) AS total_ven_consign_out, SUM(total_order) AS total_order FROM (".$tableDailyBeginning.") AS stockDaily GROUP BY location_id, lots_number, expired_date, product_id ORDER BY product_id";
$sqlCmtDailyEnding = "SELECT SUM(total_pb) AS total_pb, SUM(total_cm) AS total_cm, SUM(total_to_in) AS total_to_in, SUM(total_cycle) AS total_cycle, SUM(total_so) AS total_so, SUM(total_pbc) AS total_pbc, SUM(total_pos) AS total_pos, SUM(total_to_out) AS total_to_out, SUM(total_cus_consign_in) AS total_cus_consign_in, SUM(total_cus_consign_out) AS total_cus_consign_out, SUM(total_ven_consign_in) AS total_ven_consign_in, SUM(total_ven_consign_out) AS total_ven_consign_out, SUM(total_order) AS total_order, u_name, qty_uom_id, product_id, product_code, product_name, location_id, (SELECT name FROM locations WHERE id = location_id) AS location_name, lots_number, expired_date FROM (".$tableDailyEnding.") AS stockDaily GROUP BY location_id, lots_number, expired_date, product_id ORDER BY product_id";
} else {
$sqlCmtDailyBiginning = '';
$sqlCmtDailyEnding = '';
}
?>
| Product |
| Total |
|
|
|
|
|
|
|
|
0?$dataGlobal['qty']:0;
$subTotalEBI = ($data['total_pb'] + $data['total_cm'] + $data['total_to_in'] + $data['total_cycle'] + $data['total_cus_consign_in'] + $data['total_ven_consign_in']) - ($data['total_so'] + $data['total_pbc'] + $data['total_pos'] + $data['total_to_out'] + $data['total_cus_consign_out'] + $data['total_ven_consign_out']);
// Total Consignment In
$subTotalConsignmentIn = ($data['total_cus_consign_in'] - $dataGlobal['total_cus_consign_in']);
$subTotalParentConsignmentIn += ($data['total_cus_consign_in'] - $dataGlobal['total_cus_consign_in']);
$totalConsignmentIn += ($data['total_cus_consign_in'] - $dataGlobal['total_cus_consign_in']);
// Total Consignment Out
$subTotalConsignmentOut = ($data['total_cus_consign_out'] - $dataGlobal['total_cus_consign_out']);
$subTotalParentConsignmentOut += ($data['total_cus_consign_out'] - $dataGlobal['total_cus_consign_out']);
$totalConsignmentOut += ($data['total_cus_consign_out'] - $dataGlobal['total_cus_consign_out']);
// Total Sales + Total POS
$subTotalInvoiceQty = (($data['total_pos'] + $data['total_so']) - ($dataGlobal['total_pos'] + $dataGlobal['total_so']));
$subTotalParentInvoiceQty += (($data['total_pos'] + $data['total_so']) - ($dataGlobal['total_pos'] + $dataGlobal['total_so']));
$totalInvoiceQty += (($data['total_pos'] + $data['total_so']) - ($dataGlobal['total_pos'] + $dataGlobal['total_so']));
// Total EBI & Total
$totalBBI += $subTotalBBI;
$totalEBI += $subTotalEBI;
$subTotalParentBBI += $subTotalBBI;
$subTotalParentEBI += $subTotalEBI;
// Total Order
$subTotalOrderQty = ($data['total_order'] - $dataGlobal['total_order']);
$subTotalParentOrderQty += ($data['total_order'] - $dataGlobal['total_order']);
$totalOrderQty += ($data['total_order'] - $dataGlobal['total_order']);
$oldProductId=$data['product_id'];
$oldProductCode=$data['product_code'];
$oldProductName=$data['product_name'];
$expDate = "";
if($data['expired_date'] != '' && $data['expired_date'] != '0000-00-00'){
$expDate = dateShort($data['expired_date']);
}
$excelLotsData = "";
if($rowSettingUomDetail[0] == 1){
$excelLotsData = "\t".$data['lots_number'];
}
$excelContent .= "\n".$index++."\t".$oldProductCode."\t".$data['location_name'].$excelLotsData."\t".$expDate."\t".$subTotalBBI."\t".$subTotalConsignmentIn."\t".($subTotalConsignmentOut*-1)."\t".($subTotalInvoiceQty*-1)."\t".$subTotalEBI."\t".number_format($subTotalOrderQty, 2)."\t".$small_label; ?>
|
|
|
|
|
|
|
|
|
|
|
|
| Total |
|
|
|
|
|
|
|
| Total |
|
|
|
|
|
|
|