private static function queryStockIncoming() { $archive_table = self::$archive_query_in_excel; $total_incoming_qty = 0; $total_incoming_value = 0; if ($archive_table) { //Create new excel object $excel_row = 1; $obj_excel = new PHPExcel(); $obj_sheet = $obj_excel->getActiveSheet(); $obj_sheet->getColumnDimension('A')->setWidth(15); $obj_sheet->getColumnDimension('B')->setWidth(15); $obj_sheet->getColumnDimension('C')->setWidth(15); $obj_sheet->getColumnDimension('D')->setWidth(15); $obj_sheet->getColumnDimension('E')->setWidth(15); $obj_sheet->getColumnDimension('F')->setWidth(15); //Set Header Row $obj_sheet->setCellValue('A1', 'Product ID'); $obj_sheet->setCellValue('B1', 'EAN'); $obj_sheet->setCellValue('C1', 'Purchase Price'); $obj_sheet->setCellValue('D1', 'Stock Incoming Qty'); $obj_sheet->setCellValue('E1', 'Stock Incoming Value'); } $q = "SELECT products_id, products_ean" . ", SUM(total_qty) AS total_qty FROM ((" . " SELECT products_id, products_ean" . ", SUM(quantity) AS total_qty" . " FROM depot_orders" . " WHERE status = 7" . " GROUP BY products_ean" . " ) UNION ALL (" . " SELECT products_id, products_ean" . ", SUM(order_quantity) AS total_qty" . " FROM jng_sp_orders_items" . " WHERE status = 7" . " GROUP BY products_ean" . " ) UNION ALL (" . " SELECT products_id, products_ean" . ", SUM(products_quantity) AS total_qty" . " FROM orders_products op" . " WHERE op.status = 7" . " GROUP BY products_id" . " )) temp_table" . " GROUP BY products_ean"; $r = tep_db_query($q); while ($row = tep_db_fetch_array($r)) { $pid = $row['products_id']; $ean = $row['products_ean']; $stock_qty = $row['total_qty']; $obj_product = new Product($pid); $purchase_price = $obj_product->getCOGSObject()->purchase_price; $stock_value = $stock_qty * $purchase_price; $total_incoming_qty += $stock_qty; $total_incoming_value += $stock_value; if ($archive_table) { $excel_row++; //Set Data Row $obj_sheet->setCellValue('A' . $excel_row, $pid); $obj_sheet->setCellValue('B' . $excel_row, $ean); $obj_sheet->setCellValue('C' . $excel_row, $purchase_price); $obj_sheet->setCellValue('D' . $excel_row, $stock_qty); $obj_sheet->setCellValue('E' . $excel_row, $stock_value); } } if ($archive_table) { $period = date(self::PERIOD_DATE_FORMAT, strtotime('-1 day')); self::saveArchiveFile($obj_excel, $period, self::ARCHIVE_FILENAME_STOCK_INCOMING); } self::$current_stock_incoming_qty = $total_incoming_qty; self::$current_stock_incoming_value = $total_incoming_value; }