' . 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_LOCATION_GROUP.': '.$data[0];
$excelContent .= TABLE_LOCATION_GROUP.': '.$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));
$excelColLot = "";
if($rowSettingUomDetail[0] == 1){
$excelColLot = "\t".TABLE_LOTS_NO;
}
$excelContent .= TABLE_NO."\t".TABLE_SKU."\t".TABLE_LOCATION.$excelColLot."\t".TABLE_EXPIRED_DATE."\t".TABLE_TOTAL_QTY."\t".TABLE_UOM;
?>
|
|
|
|
|
|
|
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, 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, 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 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, 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 EBI & Total
$totalBBI += $subTotalBBI;
$totalEBI += $subTotalEBI;
$subTotalParentBBI += $subTotalBBI;
$subTotalParentEBI += $subTotalEBI;
// Product
$oldProductId=$data['product_id'];
$oldProductCode=$data['product_code'];
$oldProductName=$data['product_name'];
$excelListLot = "";
if($rowSettingUomDetail[0] == 1){
$excelListLot = "\t".$data['lots_number'];
}
$expiredDate = "";
if($data['expired_date'] != '' && $data['expired_date'] != '0000-00-00'){
$expiredDate = dateShort($data['expired_date']);
}
$excelContent .= "\n".$index++."\t".$oldProductCode."\t".$data['location_name'].$excelListLot."\t".$expiredDate."\t".$subTotalEBI."\t".$small_label; ?>
|
|
|
|
|
|
|
| Total |
|
|
| Total |
|
|