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;
}
