' . MENU_PRODUCT_INVENTORY_ACTIVITY . '
';
$excelContent .= MENU_PRODUCT_INVENTORY_ACTIVITY."\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['location_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['location_id']!='') {
$query=mysql_query("SELECT name FROM locations WHERE id=".$_POST['location_id']);
$data=mysql_fetch_array($query);
$msg .= '
'.TABLE_LOCATION.': '.$data[0];
$excelContent .= TABLE_LOCATION.': '.$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));
$excelContent .= TABLE_NO."\t".TABLE_SKU."\t".TABLE_PRODUCT_NAME."\tBeginning\tIn/(Out)"."\t".'Ending'."\t".TABLE_UOM;
?>
|
|
|
Beginning |
In/Out |
Ending |
|
= DATE(order_date)';
}
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'status = 2';
// Get Location Group
if ($col[3] != '') {
$conditionGetLocation .= " = ".$col[3];
}else{
$conditionGetLocation .= " IN (SELECT user_location_groups.location_group_id FROM user_location_groups INNER JOIN location_groups ON location_groups.id = user_location_groups.location_group_id AND location_groups.location_group_type_id != 1 WHERE user_location_groups.user_id = ".$user['User']['id']." GROUP BY user_location_groups.location_group_id)";
}
// Get Location
if ($col[4] != '') {
$condition != '' ? $condition .= ' AND ' : '';
$condition .= 'location_id=' . $col[4];
$conditionGetLocation = " AND id = ".$col[4];
}
// Get Product Group
if($col[5] != ""){
// Get Product
if($col[7] != ""){
$conProduct = " AND product_id = ".$col[7];
}
$conPGroup = " AND products.id IN (SELECT product_id FROM product_pgroups WHERE pgroup_id = ".$col[5].$conProduct.")";
}
// Get Product Parent
if($col[6] != ""){
$conParent = " AND products.parent_id =".$col[6];
}
// Get Product
if($col[7] != ""){
if($conPGroup == ""){
$conPGroup = " AND products.id IN (".$col[7].")";
}
}
$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, product_id";
$filedDaily = "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, uoms.name AS u_name, products.price_uom_id AS qty_uom_id, products.parent_id AS parent_id, products.code AS product_code, products.name AS product_name, products.id AS product_id, (SELECT CONCAT(' ',code,name) FROM products WHERE id = products.parent_id LIMIT 1) AS parent_name";
$conditionDaily = " products.is_active = 1 AND date ".$conPGroup.$conVendor.$conParent.$conCompany;
$conditionDailyBbi = " products.is_active = 1 AND date AND changeId".$conParent.$conCompany;
$groupByDaily = "GROUP BY product_id";
$tableInventory = "";
$fieldInventory = "";
$conditionInventory = "";
$groupByInventory = "";
// List Location
$queryLocationList = mysql_query('SELECT id AS location_id FROM locations WHERE location_group_id' . $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." ".$groupByDaily;
// 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." ".$groupByDaily;
// Inventory Total
$conditionInventory = " products.is_active = 1 ".$conPGroup.$conParent.$conCompany;
$groupByInventory = " GROUP BY ".$dataLocationList['location_id']."_inventory_totals.product_id";
$tableInventory .= "SELECT total_qty AS qty, uoms.name AS u_name, products.price_uom_id AS qty_uom_id, products.parent_id AS parent_id, products.code AS product_code, products.name AS product_name, products.id AS product_id, (SELECT CONCAT(' ',code,name) FROM products WHERE id = products.parent_id LIMIT 1) AS parent_name FROM ".$dataLocationList['location_id']."_inventory_totals".$joinProducts." (products.id = ".$dataLocationList['location_id']."_inventory_totals.product_id)".$joinUom." WHERE ".$conditionInventory.$groupByInventory;
}
}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." ".$groupByDaily;
// 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." ".$groupByDaily;
// Inventory Total
$conditionInventory = " products.is_active = 1 ".$conPGroup.$conParent.$conCompany;
$groupByInventory = " GROUP BY ".$dataLocationList['location_id']."_inventory_totals.product_id";
$tableInventory .= "SELECT total_qty AS qty, uoms.name AS u_name, products.price_uom_id AS qty_uom_id, products.parent_id AS parent_id, products.code AS product_code, products.name AS product_name, products.id AS product_id, (SELECT CONCAT(' ',code,name) FROM products WHERE id = products.parent_id LIMIT 1) AS parent_name FROM ".$dataLocationList['location_id']."_inventory_totals".$joinProducts." (products.id = ".$dataLocationList['location_id']."_inventory_totals.product_id)".$joinUom." WHERE ".$conditionInventory.$groupByInventory;
}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." ".$groupByDaily;
// 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." ".$groupByDaily;
// Inventory Total
$conditionInventory = " products.is_active = 1 ".$conPGroup.$conParent.$conCompany;
$groupByInventory = " GROUP BY ".$dataLocationList['location_id']."_inventory_totals.product_id";
$tableInventory .= " UNION ALL SELECT total_qty AS qty, uoms.name AS u_name, products.price_uom_id AS qty_uom_id, products.parent_id AS parent_id, products.code AS product_code, products.name AS product_name, products.id AS product_id, (SELECT CONCAT(' ',code,name) FROM products WHERE id = products.parent_id LIMIT 1) AS parent_name FROM ".$dataLocationList['location_id']."_inventory_totals".$joinProducts." (products.id = ".$dataLocationList['location_id']."_inventory_totals.product_id)".$joinUom." WHERE ".$conditionInventory.$groupByInventory;
}
$tmpLocationId = $dataLocationList['location_id'];
$locId++;
}
}
$dateFrom = "date<'".dateConvert($col[1])."'";
$dateTo = "DATE(date)<='".dateConvert($col[2])."'";
$tableDailyBeginning = str_replace("date", $dateFrom, $tableDailyBbi);
$tableDailyEnding = str_replace("date", $dateTo, $tableDaily);
$sqlCmtInventory = "SELECT SUM(qty) AS qty, u_name, qty_uom_id, IFNULL(parent_id,0) AS parent_id, product_id, product_name, IFNULL(parent_name,'No Parent') AS parent_name FROM (".$tableInventory.") AS inventory GROUP BY product_id";
$sqlCmtDailyBiginning = "SELECT SUM(total_qty) AS qty FROM (".$tableDailyBeginning.") AS stockDaily GROUP BY product_id";
$sqlCmtDailyEnding = "SELECT SUM(total_qty) AS qty, u_name, qty_uom_id, IFNULL(parent_id,0) AS parent_id, product_id, product_code, product_name, IFNULL(parent_name,'No Parent') AS parent_name FROM (".$tableDailyEnding.") AS stockDaily GROUP BY product_id";
} else {
$sqlCmtInventory = "";
$sqlCmtDailyBiginning = "";
$sqlCmtDailyEnding = "";
}
$excelContent .= "\n".'Product'; ?>
| Product |
0){
$sqlParent = mysql_query("SELECT CONCAT_WS(' ',code,name) AS name FROM products WHERE id = ".$data['parent_id']);
$rowParent = mysql_fetch_array($sqlParent);
$parentName = $rowParent['name'];
}else{
$parentName = 'No Parent';
}
if($data['product_id']!=$oldProductId){
if($oldProductName!=''){
$excelContent .= "\n".$index."\t".$oldProductCode."\t".$oldProductName."\t".number_format($subTotalBBI, 0)."\t".number_format($subTotalQty, 0)."\t".number_format($subTotalEBI, 0)."\t".$small_label;
$subTotalParentBBI+=$subTotalBBI;
$subTotalParentEBI+=$subTotalEBI;
$totalBBI+=$subTotalBBI;
$totalEBI+=$subTotalEBI;
?>
|
|
|
|
|
|
|
| Total |
|
|
|
|
|
|
|
|
|
|
|
|
| Total |
|
|
|
|
| Total Product |
|
|
|
|
| |
| Service |
|
|
|
|
|
|
|
|
| Total Service |
|
|
| |
| Miscellaneous |
|
|
|
|
|
|
|
|
| Total Miscellaneous |
|
|