public static function queryDepotSummary($includes_refill = false, $save_to_config = true) { //TODO: Please consider to also includes warehouse id as filter when we want to add new depot set_time_limit(0); self::loadDIOHsettings(); $depot_summary = load_config('depot-summary'); $totalstars = 3; $goodstock_tolerance = 0; use_class('logger'); $logger = new logger('classes', 'product'); $logger->write('QUERY DEPOT SUMMARY!'); $logger->write('Previous Summary Result:'); foreach ($depot_summary as $key => $value) { $logger->write("{$key} = {$value}"); } $refill_data = array(); if ($includes_refill) { use_class('depot_orders'); $q = "SELECT products_id, articles_id, SUM(quantity) AS refill_qty"; $q .= " FROM depot_orders"; $q .= " WHERE trans_type IN (" . depot_orders::FILTER_TRANS_TYPE_ALLREFILL . ")"; $q .= " AND status NOT IN ( " . depot_orders::FILTER_STATUS_CLOSE . ")"; $q .= " GROUP BY products_id, articles_id"; $r = tep_db_query($q); while ($row = tep_db_fetch_array($r)) { $refill_data["{$row['products_id']}-{$row['articles_id']}"] = $row['refill_qty']; } } $q = "SELECT ps.products_id, ps.articles_id, p.stars, (ps.stock-ps.booking_active) AS stock_available, p.material_expenses"; $q .= " FROM products p"; $q .= " LEFT JOIN products_stock ps ON ps.products_id=p.products_id"; $q .= " WHERE p.products_status='1' OR ps.stock>0"; $q .= " ORDER BY products_id, articles_id"; $r = tep_db_query($q); $product = null; $overstock = array(); $shortages = array(); $goodstock = array(); for ($s = 0; $s <= $totalstars; $s++) { $overstock[$s]['products'] = array(); $overstock[$s]['articles'] = 0; $overstock[$s]['quantity'] = 0; $overstock[$s]['value'] = 0; $shortages[$s]['products'] = array(); $shortages[$s]['articles'] = 0; $shortages[$s]['quantity'] = 0; $shortages[$s]['value'] = 0; $goodstock[$s]['products'] = array(); $goodstock[$s]['articles'] = 0; $goodstock[$s]['quantity'] = 0; $goodstock[$s]['value'] = 0; } $rowcounter = 0; while ($row = tep_db_fetch_array($r)) { $rowcounter++; $stock_refill = $includes_refill && isset($refill_data["{$row['products_id']}-{$row['articles_id']}"]) ? $refill_data["{$row['products_id']}-{$row['articles_id']}"] : 0; $stock_available = ($row['stock_available'] < 0 ? 0 : $row['stock_available']) + $stock_refill; $material_expenses = isset($row['material_expenses']) && $row['material_expenses'] > 0 ? self::getMaterialExpenseInDefaultCurrency($row['material_expenses']) : 0; if (product::$diohStopLevel[$row['stars']] == 0) { //NO TARGET, NO NEED TO COUNT if ($stock_available == 0) { $goodstock[$row['stars']]['products'][] = $row['products_id']; $goodstock[$row['stars']]['articles']++; } else { $overstock[$row['stars']]['products'][] = $row['products_id']; $overstock[$row['stars']]['articles']++; $overstock[$row['stars']]['quantity'] += $stock_available; $overstock[$row['stars']]['value'] += $stock_available * $material_expenses; } } else { if (is_null($product) || is_object($product) && $product->id != $row['products_id']) { $product = new product($row['products_id']); } $stock_target = $product->getDIOHstockTarget($row['articles_id']); $stock_diff = $stock_available - $stock_target; if ($stock_diff > $goodstock_tolerance) { //OVERSTOCK $overstock_quantity = $stock_diff - $goodstock_tolerance; $overstock[$product->stars]['products'][] = $product->id; $overstock[$product->stars]['articles']++; $overstock[$product->stars]['quantity'] += $overstock_quantity; $goodstock[$product->stars]['quantity'] += $stock_available - $overstock_quantity; $overstock[$product->stars]['value'] += $overstock_quantity * $material_expenses; $goodstock[$product->stars]['value'] += ($stock_available - $overstock_quantity) * $material_expenses; } elseif ($stock_diff < -1 * $goodstock_tolerance) { //SHORTAGE $shortages_quantity = abs($stock_diff) - $goodstock_tolerance; $shortages[$product->stars]['products'][] = $product->id; $shortages[$product->stars]['articles']++; $shortages[$product->stars]['quantity'] += $shortages_quantity; $shortages[$product->stars]['quantity'] += $stock_available; $shortages[$product->stars]['value'] += $shortages_quantity * $material_expenses; $shortages[$product->stars]['value'] += $stock_available * $material_expenses; } else { $goodstock[$product->stars]['products'][] = $product->id; $goodstock[$product->stars]['articles']++; $goodstock[$product->stars]['quantity'] += $stock_available; $goodstock[$product->stars]['value'] += $stock_available * $material_expenses; } } // echo '. '; } $logger->write('Total rows from query = ' . $rowcounter); for ($s = 0; $s <= $totalstars; $s++) { $overstock[$s]['products'] = count(array_unique($overstock[$s]['products'])); $shortages[$s]['products'] = count(array_unique($shortages[$s]['products'])); $goodstock[$s]['products'] = count(array_unique($goodstock[$s]['products'])); } // echo '<pre>'; // var_dump($goodstock); // var_dump($overstock); // var_dump($shortages); // echo '</pre>'; $depot_summary['lastrun'] = time(); $depot_summary['diohTarget-0'] = self::$diohStopLevel[0]; $depot_summary['diohTarget-1'] = self::$diohStopLevel[1]; $depot_summary['diohTarget-2'] = self::$diohStopLevel[2]; $depot_summary['diohTarget-3'] = self::$diohStopLevel[3]; $depot_summary['overstock-3-products'] = $overstock[3]['products']; $depot_summary['overstock-3-articles'] = $overstock[3]['articles']; $depot_summary['overstock-3-quantity'] = $overstock[3]['quantity']; $depot_summary['overstock-3-value'] = $overstock[3]['value']; $depot_summary['overstock-2-products'] = $overstock[2]['products']; $depot_summary['overstock-2-articles'] = $overstock[2]['articles']; $depot_summary['overstock-2-quantity'] = $overstock[2]['quantity']; $depot_summary['overstock-2-value'] = $overstock[2]['value']; $depot_summary['overstock-1-products'] = $overstock[1]['products']; $depot_summary['overstock-1-articles'] = $overstock[1]['articles']; $depot_summary['overstock-1-quantity'] = $overstock[1]['quantity']; $depot_summary['overstock-1-value'] = $overstock[1]['value']; $depot_summary['overstock-0-products'] = $overstock[0]['products']; $depot_summary['overstock-0-articles'] = $overstock[0]['articles']; $depot_summary['overstock-0-quantity'] = $overstock[0]['quantity']; $depot_summary['overstock-0-value'] = $overstock[0]['value']; $depot_summary['goodstock-3-products'] = $goodstock[3]['products']; $depot_summary['goodstock-3-articles'] = $goodstock[3]['articles']; $depot_summary['goodstock-3-quantity'] = $goodstock[3]['quantity']; $depot_summary['goodstock-3-value'] = $goodstock[3]['value']; $depot_summary['goodstock-2-products'] = $goodstock[2]['products']; $depot_summary['goodstock-2-articles'] = $goodstock[2]['articles']; $depot_summary['goodstock-2-quantity'] = $goodstock[2]['quantity']; $depot_summary['goodstock-2-value'] = $goodstock[2]['value']; $depot_summary['goodstock-1-products'] = $goodstock[1]['products']; $depot_summary['goodstock-1-articles'] = $goodstock[1]['articles']; $depot_summary['goodstock-1-quantity'] = $goodstock[1]['quantity']; $depot_summary['goodstock-1-value'] = $goodstock[1]['value']; $depot_summary['goodstock-0-products'] = $goodstock[0]['products']; $depot_summary['goodstock-0-articles'] = $goodstock[0]['articles']; $depot_summary['goodstock-0-quantity'] = $goodstock[0]['quantity']; $depot_summary['goodstock-0-value'] = $goodstock[0]['value']; $depot_summary['shortages-3-products'] = $shortages[3]['products']; $depot_summary['shortages-3-articles'] = $shortages[3]['articles']; $depot_summary['shortages-3-quantity'] = $shortages[3]['quantity']; $depot_summary['shortages-2-products'] = $shortages[2]['products']; $depot_summary['shortages-2-articles'] = $shortages[2]['articles']; $depot_summary['shortages-2-quantity'] = $shortages[2]['quantity']; $depot_summary['shortages-2-value'] = $shortages[2]['value']; $depot_summary['shortages-1-products'] = $shortages[1]['products']; $depot_summary['shortages-1-articles'] = $shortages[1]['articles']; $depot_summary['shortages-1-quantity'] = $shortages[1]['quantity']; $depot_summary['shortages-1-value'] = $shortages[1]['value']; $depot_summary['shortages-0-products'] = $shortages[0]['products']; $depot_summary['shortages-0-articles'] = $shortages[0]['articles']; $depot_summary['shortages-0-quantity'] = $shortages[0]['quantity']; $depot_summary['shortages-0-value'] = $shortages[0]['value']; if ($save_to_config) { save_config('depot-summary', $depot_summary); } $logger->write('New value successfully saved'); $logger->close(); // echo 'Done!'; return $depot_summary; }
public static function queryDepotSummary() { set_time_limit(0); self::loadDIOHsettings(); $depot_summary = load_config('depot-summary'); $totalstars = 3; $goodstock_tolerance = 0; use_class('logger'); $logger = new logger('classes', 'product'); $logger->write('QUERY DEPOT SUMMARY!'); $logger->write('Previous Summary Result:'); foreach ($depot_summary as $key => $value) { $logger->write("{$key} = {$value}"); } $q = "SELECT ps.products_id, ps.articles_id, p.stars, (ps.stock-ps.booking_active) AS stock_available"; $q .= " FROM products p"; $q .= " LEFT JOIN products_stock ps ON ps.products_id=p.products_id"; $q .= " WHERE p.products_status='1' OR ps.stock>0"; $q .= " ORDER BY products_id, articles_id"; $r = tep_db_query($q); $product = null; $overstock = array(); $shortages = array(); $goodstock = array(); for ($s = 0; $s <= $totalstars; $s++) { $overstock[$s]['products'] = array(); $overstock[$s]['articles'] = 0; $overstock[$s]['quantity'] = 0; $shortages[$s]['products'] = array(); $shortages[$s]['articles'] = 0; $shortages[$s]['quantity'] = 0; $goodstock[$s]['products'] = array(); $goodstock[$s]['articles'] = 0; $goodstock[$s]['quantity'] = 0; } $rowcounter = 0; while ($row = tep_db_fetch_array($r)) { $rowcounter++; $stockAvailable = $row['stock_available'] < 0 ? 0 : $row['stock_available']; if (product::$diohStopLevel[$row['stars']] == 0) { //NO TARGET, NO NEED TO COUNT if ($stockAvailable == 0) { $goodstock[$row['stars']]['products'][] = $row['products_id']; $goodstock[$row['stars']]['articles']++; } else { $overstock[$row['stars']]['products'][] = $row['products_id']; $overstock[$row['stars']]['articles']++; $overstock[$row['stars']]['quantity'] += $stockAvailable; } } else { if (is_null($product) || is_object($product) && $product->id != $row['products_id']) { $product = new product($row['products_id']); } $stockTarget = $product->getDIOHstockTarget($row['articles_id']); $stockDiff = $stockAvailable - $stockTarget; if ($stockDiff > $goodstock_tolerance) { //OVERSTOCK $overstock_quantity = $stockDiff - $goodstock_tolerance; $overstock[$product->stars]['products'][] = $product->id; $overstock[$product->stars]['articles']++; $overstock[$product->stars]['quantity'] += $overstock_quantity; $goodstock[$product->stars]['quantity'] += $stockAvailable - $overstock_quantity; } elseif ($stockDiff < -1 * $goodstock_tolerance) { //SHORTAGE $shortages_quantity = abs($stockDiff) - $goodstock_tolerance; $shortages[$product->stars]['products'][] = $product->id; $shortages[$product->stars]['articles']++; $shortages[$product->stars]['quantity'] += $shortages_quantity; $shortages[$product->stars]['quantity'] += $stockAvailable; } else { $goodstock[$product->stars]['products'][] = $product->id; $goodstock[$product->stars]['articles']++; $goodstock[$product->stars]['quantity'] += $stockAvailable; } } // echo '. '; } $logger->write('Total rows from query = ' . $rowcounter); for ($s = 0; $s <= $totalstars; $s++) { $overstock[$s]['products'] = count(array_unique($overstock[$s]['products'])); $shortages[$s]['products'] = count(array_unique($shortages[$s]['products'])); $goodstock[$s]['products'] = count(array_unique($goodstock[$s]['products'])); } // echo '<pre>'; // var_dump($goodstock); // var_dump($overstock); // var_dump($shortages); // echo '</pre>'; $depot_summary['lastrun'] = time(); $depot_summary['diohTarget-0'] = self::$diohStopLevel[0]; $depot_summary['diohTarget-1'] = self::$diohStopLevel[1]; $depot_summary['diohTarget-2'] = self::$diohStopLevel[2]; $depot_summary['diohTarget-3'] = self::$diohStopLevel[3]; $depot_summary['overstock-3-products'] = $overstock[3]['products']; $depot_summary['overstock-3-articles'] = $overstock[3]['articles']; $depot_summary['overstock-3-quantity'] = $overstock[3]['quantity']; $depot_summary['overstock-2-products'] = $overstock[2]['products']; $depot_summary['overstock-2-articles'] = $overstock[2]['articles']; $depot_summary['overstock-2-quantity'] = $overstock[2]['quantity']; $depot_summary['overstock-1-products'] = $overstock[1]['products']; $depot_summary['overstock-1-articles'] = $overstock[1]['articles']; $depot_summary['overstock-1-quantity'] = $overstock[1]['quantity']; $depot_summary['overstock-0-products'] = $overstock[0]['products']; $depot_summary['overstock-0-articles'] = $overstock[0]['articles']; $depot_summary['overstock-0-quantity'] = $overstock[0]['quantity']; $depot_summary['goodstock-3-products'] = $goodstock[3]['products']; $depot_summary['goodstock-3-articles'] = $goodstock[3]['articles']; $depot_summary['goodstock-3-quantity'] = $goodstock[3]['quantity']; $depot_summary['goodstock-2-products'] = $goodstock[2]['products']; $depot_summary['goodstock-2-articles'] = $goodstock[2]['articles']; $depot_summary['goodstock-2-quantity'] = $goodstock[2]['quantity']; $depot_summary['goodstock-1-products'] = $goodstock[1]['products']; $depot_summary['goodstock-1-articles'] = $goodstock[1]['articles']; $depot_summary['goodstock-1-quantity'] = $goodstock[1]['quantity']; $depot_summary['goodstock-0-products'] = $goodstock[0]['products']; $depot_summary['goodstock-0-articles'] = $goodstock[0]['articles']; $depot_summary['goodstock-0-quantity'] = $goodstock[0]['quantity']; $depot_summary['shortages-3-products'] = $shortages[3]['products']; $depot_summary['shortages-3-articles'] = $shortages[3]['articles']; $depot_summary['shortages-3-quantity'] = $shortages[3]['quantity']; $depot_summary['shortages-2-products'] = $shortages[2]['products']; $depot_summary['shortages-2-articles'] = $shortages[2]['articles']; $depot_summary['shortages-2-quantity'] = $shortages[2]['quantity']; $depot_summary['shortages-1-products'] = $shortages[1]['products']; $depot_summary['shortages-1-articles'] = $shortages[1]['articles']; $depot_summary['shortages-1-quantity'] = $shortages[1]['quantity']; $depot_summary['shortages-0-products'] = $shortages[0]['products']; $depot_summary['shortages-0-articles'] = $shortages[0]['articles']; $depot_summary['shortages-0-quantity'] = $shortages[0]['quantity']; save_config('depot-summary', $depot_summary); $logger->write('New value successfully saved'); $logger->close(); // echo 'Done!'; return $depot_summary; }