$config = array();
$config['kpi-production-daily-capacity'] = $dc;
$config['kpi-backlog-ignore-refill'] = $blsr;
save_config('kpi-settings', $config);
tep_db_perform('products_best_good_settings', array('value' => $atu), 'update', "id=97");
tep_db_perform('products_best_good_settings', array('value' => $atul), 'update', "id=98");
$result = array('dc' => $dc, 'blsr' => $blsr, 'atu' => $atu, 'atul' => $atul);
ajaxReturn($result);
exit;
} elseif ($_POST['me_action'] == 'RESETATMACHINE') {
$machine_id = tep_db_prepare_input($_POST['machine_id']);
tep_db_perform("production_antitarnish", array('current_usage' => 0, 'last_reset_date' => date('Y-m-d H:i:s')), 'update', "machine_id = '{$machine_id}'");
$result = $machine_id;
} elseif ($_POST['me_action'] == 'KPI-DEPOT-QUERYDEPOTSUMMARY' || $_POST['me_action'] == 'KPI-DEPOT-LOADDEPOTSUMMARY') {
if ($_POST['me_action'] == 'KPI-DEPOT-QUERYDEPOTSUMMARY') {
$depot_summary = Product::queryDepotSummary();
} else {
$depot_summary = load_config('depot-summary');
}
$ajaxResult = array();
foreach ($depot_summary as $ds_key => $ds_value) {
if ($ds_key == 'lastrun') {
$ajaxValue = date('d.m.y H:i', $ds_value);
} elseif (strpos($ds_key, 'diohTarget') !== false) {
$ajaxValue = $ds_value . ' days';
} elseif (strpos($ds_key, '-products') !== false) {
$ajaxValue = number_format($ds_value, 0, '', '.') . ' products';
} elseif (strpos($ds_key, '-articles') !== false) {
$ajaxValue = number_format($ds_value, 0, '', '.') . ' articles';
} elseif (strpos($ds_key, '-quantity') !== false) {
$ajaxValue = number_format($ds_value, 0, '', '.') . ' qty';
public static function generateWarehouseLog($date)
{
$yesterday_date = date('Y-m-d', strtotime("-1 day"));
$result = false;
$query = tep_db_query("SELECT jng_warehouses_id, stars FROM scorecard_warehouses WHERE date='{$date}'");
$wh_exist = array();
while ($row = tep_db_fetch_array($query)) {
$wh_exist[] = $row['jng_warehouses_id'] . '-' . $row['stars'];
}
//values should only be grabbed for previous day
if ($date == $yesterday_date) {
//ELEMENTS VALUE
$q = "SELECT es.jng_warehouses_id, IFNULL(e.stars, 0) AS stars";
$q .= ", SUM(es.stock) AS stock_total";
$q .= ", SUM(es.stock*e.elements_price) AS stock_value";
//TODO: need to replaced p.material_expenses with COGS
$q .= " FROM elements_stock es";
$q .= " LEFT JOIN element e ON e.elements_id=es.elements_id";
$q .= " WHERE es.stock>0";
$q .= " GROUP BY jng_warehouses_id, stars";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$ukey = $row['jng_warehouses_id'] . '-' . $row['stars'];
$sda = array();
$sda['quantity'] = $row['stock_total'];
$sda['value'] = $row['stock_value'];
if (!in_array($ukey, $wh_exist)) {
$sda['date'] = $date;
$sda['jng_warehouses_id'] = $row['jng_warehouses_id'];
$sda['stars'] = $row['stars'];
tep_db_perform('scorecard_warehouses', $sda);
$wh_exist[] = $ukey;
} else {
tep_db_perform('scorecard_warehouses', $sda, 'update', "date={$date} AND jng_warehouses_id={$row['jng_warehouses_id']} AND stars={$row['stars']}");
}
}
//PRODUCTS VALUE
$q = "SELECT ps.jng_warehouses_id, IFNULL(p.stars, 0) AS stars";
$q .= ", SUM(ps.stock) AS stock_total";
$q .= ", SUM(ps.stock*p.material_expenses) AS stock_value";
//TODO: need to replaced p.material_expenses with COGS
$q .= " FROM products_stock ps";
$q .= " LEFT JOIN products p ON p.products_id=ps.products_id";
$q .= " WHERE ps.stock>0";
$q .= " GROUP BY jng_warehouses_id, stars";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$ukey = $row['jng_warehouses_id'] . '-' . $row['stars'];
$sda = array();
$sda['quantity'] = $row['stock_total'];
$sda['value'] = $row['stock_value'];
if (!in_array($ukey, $wh_exist)) {
$sda['date'] = $date;
$sda['jng_warehouses_id'] = $row['jng_warehouses_id'];
$sda['stars'] = $row['stars'];
tep_db_perform('scorecard_warehouses', $sda);
$wh_exist[] = $ukey;
} else {
tep_db_perform('scorecard_warehouses', $sda, 'update', "date={$date} AND jng_warehouses_id={$row['jng_warehouses_id']} AND stars={$row['stars']}");
}
}
}
//ELEMENTS INCOMING AND OUTGOING
$q = "SELECT mset.jng_warehouses_id, IFNULL(e.stars, 0) AS stars";
$q .= ", SUM(mset.stock_add) AS stock_add, SUM(mset.stock_reduce) AS stock_red";
$q .= ", SUM(mset.stock_add*mset.mat_exp) AS stock_add_value";
$q .= ", SUM(mset.stock_reduce*mset.mat_exp) AS stock_red_value";
$q .= " FROM minierp_stock_elements_transaction mset";
$q .= " LEFT JOIN element e ON e.elements_id=mset.elements_id";
$q .= " WHERE mset.transaction_time LIKE '{$date}%'";
$q .= " GROUP BY jng_warehouses_id, stars";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$ukey = $row['jng_warehouses_id'] . '-' . $row['stars'];
$sda = array();
$sda['incoming'] = $row['stock_add'];
$sda['incoming_value'] = $row['stock_add_value'];
$sda['outgoing'] = $row['stock_red'];
$sda['outgoing_value'] = $row['stock_red_value'];
if (!in_array($ukey, $wh_exist)) {
$sda['date'] = $date;
$sda['jng_warehouses_id'] = $row['jng_warehouses_id'];
$sda['stars'] = $row['stars'];
tep_db_perform('scorecard_warehouses', $sda);
$wh_exist[] = $ukey;
} else {
tep_db_perform('scorecard_warehouses', $sda, 'update', "date='{$date}' AND jng_warehouses_id={$row['jng_warehouses_id']} AND stars={$row['stars']}");
}
}
//PRODUCTS INCOMING AND OUTGOING
$q = "SELECT mspt.jng_warehouses_id, IFNULL(p.stars, 0) AS stars";
$q .= ", SUM(mspt.stock_add) AS stock_add, SUM(mspt.stock_reduce) AS stock_red";
$q .= ", SUM(mspt.stock_add*mspt.mat_exp) AS stock_add_value";
//TODO: material_expenses need to be replaced with COGS
$q .= ", SUM(mspt.stock_reduce*mspt.mat_exp) AS stock_red_value";
//TODO: material_expenses need to be replaced with COGS
$q .= " FROM minierp_stock_products_transaction mspt";
$q .= " LEFT JOIN products p ON p.products_id=mspt.products_id";
$q .= " WHERE mspt.transaction_time LIKE '{$date}%'";
$q .= " GROUP BY jng_warehouses_id, stars";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$ukey = $row['jng_warehouses_id'] . '-' . $row['stars'];
$sda = array();
$sda['incoming'] = $row['stock_add'];
$sda['incoming_value'] = $row['stock_add_value'];
$sda['outgoing'] = $row['stock_red'];
$sda['outgoing_value'] = $row['stock_red_value'];
if (!in_array($ukey, $wh_exist)) {
$sda['date'] = $date;
$sda['jng_warehouses_id'] = $row['jng_warehouses_id'];
$sda['stars'] = $row['stars'];
tep_db_perform('scorecard_warehouses', $sda);
$wh_exist[] = $ukey;
} else {
tep_db_perform('scorecard_warehouses', $sda, 'update', "date='{$date}' AND jng_warehouses_id={$row['jng_warehouses_id']} AND stars={$row['stars']}");
}
}
//BEGIN KPI DEPOT DATA SUPPORT ================
//SETTINGS - PRODUCTS DIOH TARGET, PRODUCTS STARS LEVEL ENTRY (whid = 5)
use_class('products_minierp');
use_class('depot_orders');
use_class('Product');
$class_pm = new products_minierp();
$bgst = $class_pm->retrieveBestGoodSettings();
$ukeys[] = WAREHOUSE_ID_PRODUCTS_HAMBURG . '-0';
$ukeys[] = WAREHOUSE_ID_PRODUCTS_HAMBURG . '-1';
$ukeys[] = WAREHOUSE_ID_PRODUCTS_HAMBURG . '-2';
$ukeys[] = WAREHOUSE_ID_PRODUCTS_HAMBURG . '-3';
foreach ($ukeys as $ukey) {
list($whid, $stars) = explode('-', $ukey);
$sda = array();
$sda['level_sold_ref'] = 'L30D';
$sda['level_start'] = $stars == 3 ? $bgst['star' . $stars . '_ge'] : $bgst['star' . $stars . '_bgn'];
$sda['level_end'] = $stars == 3 ? null : $bgst['star' . $stars . '_end'];
$sda['dioh_target'] = $bgst['star' . $stars . '_dioh'];
if (!in_array($ukey, $wh_exist)) {
$sda['date'] = $date;
$sda['jng_warehouses_id'] = $whid;
$sda['stars'] = $stars;
tep_db_perform('scorecard_warehouses', $sda);
$wh_exist[] = $ukey;
} else {
tep_db_perform('scorecard_warehouses', $sda, 'update', "date='{$date}' AND jng_warehouses_id={$whid} AND stars={$stars}");
}
}
//SETTINGS - ELEMENTS DIOH TARGET, ELEMENTS STARS LEVEL ENTRY (whid = 1)
$ukeys_e[] = WAREHOUSE_ID_ELEMENTS . '-0';
$ukeys_e[] = WAREHOUSE_ID_ELEMENTS . '-1';
$ukeys_e[] = WAREHOUSE_ID_ELEMENTS . '-2';
$ukeys_e[] = WAREHOUSE_ID_ELEMENTS . '-3';
foreach ($ukeys_e as $ukey) {
list($whid, $stars) = explode('-', $ukey);
$sda = array();
$sda['level_sold_ref'] = 'L30D';
$sda['level_start'] = $stars == 3 ? $bgst['star' . $stars . '_ge'] : $bgst['star' . $stars . '_bgn'];
$sda['level_end'] = $stars == 3 ? null : $bgst['e_star' . $stars . '_end'];
$sda['dioh_target'] = $bgst['e_star' . $stars . '_dioh'];
if (!in_array($ukey, $wh_exist)) {
$sda['date'] = $date;
$sda['jng_warehouses_id'] = $whid;
$sda['stars'] = $stars;
tep_db_perform('scorecard_warehouses', $sda);
$wh_exist[] = $ukey;
} else {
tep_db_perform('scorecard_warehouses', $sda, 'update', "date='{$date}' AND jng_warehouses_id={$whid} AND stars={$stars}");
}
}
//OVERSTOCK PRODUCTS
//get depot summary including refill and not saved to config
$depot_summary = Product::queryDepotSummary(true, false);
foreach ($ukeys as $ukey) {
list($whid, $stars) = explode('-', $ukey);
$sda = array();
$sda['overstock'] = $depot_summary["overstock-{$stars}-quantity"];
$sda['overstock_value'] = $depot_summary["overstock-{$stars}-value"];
if (!in_array($ukey, $wh_exist)) {
$sda['date'] = $date;
$sda['jng_warehouses_id'] = $whid;
$sda['stars'] = $stars;
tep_db_perform('scorecard_warehouses', $sda);
$wh_exist[] = $ukey;
} else {
tep_db_perform('scorecard_warehouses', $sda, 'update', "date='{$date}' AND jng_warehouses_id={$whid} AND stars={$stars}");
}
}
//L30D NO PROMO
$q = "SELECT jng_warehouses_id, stars, SUM(fc_base_monthly) AS l30d_nopromo FROM";
$q .= " (";
$q .= " SELECT ps.products_id, ps.jng_warehouses_id, p.stars, pl.fc_base_monthly FROM products_log pl";
$q .= " LEFT JOIN products_stock ps ON ps.products_id = pl.products_id";
$q .= " LEFT JOIN products p ON p.products_id = pl.products_id";
$q .= " WHERE jng_warehouses_id IS NOT NULL";
$q .= " GROUP BY ps.jng_warehouses_id, ps.products_id";
$q .= " ) dt";
$q .= " GROUP BY jng_warehouses_id, dt.stars";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$ukey = $row['jng_warehouses_id'] . '-' . $row['stars'];
$sda = array();
$sda['l30d_nopromo'] = $row['l30d_nopromo'];
if (!in_array($ukey, $wh_exist)) {
$sda['date'] = $date;
$sda['jng_warehouses_id'] = $row['jng_warehouses_id'];
$sda['stars'] = $row['stars'];
tep_db_perform('scorecard_warehouses', $sda);
$wh_exist[] = $ukey;
} else {
tep_db_perform('scorecard_warehouses', $sda, 'update', "date='{$date}' AND jng_warehouses_id={$row['jng_warehouses_id']} AND stars={$row['stars']}");
}
}
//TOTAL ASSORTMENT
$q = "SELECT jng_warehouses_id, stars, COUNT(1) total_assortment FROM (";
$q .= " SELECT DISTINCT jng_warehouses_id, p.products_id, p.stars FROM products p";
$q .= " INNER JOIN products_stock ps ON ps.products_id = p.products_id";
$q .= " ) dt";
$q .= " GROUP BY jng_warehouses_id, stars";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$ukey = $row['jng_warehouses_id'] . '-' . $row['stars'];
$sda = array();
$sda['total_assortment'] = $row['total_assortment'];
if (!in_array($ukey, $wh_exist)) {
$sda['date'] = $date;
$sda['jng_warehouses_id'] = $row['jng_warehouses_id'];
$sda['stars'] = $row['stars'];
tep_db_perform('scorecard_warehouses', $sda);
$wh_exist[] = $ukey;
} else {
tep_db_perform('scorecard_warehouses', $sda, 'update', "date='{$date}' AND jng_warehouses_id={$row['jng_warehouses_id']} AND stars={$row['stars']}");
}
}
//TOTAL ASSORTMENT NO STOCK (ONLY GET ACTIVE ARTICLES)
$q = "SELECT jng_warehouses_id, stars, COUNT(1) total_assortment_nostock" . " FROM (" . " SELECT DISTINCT jng_warehouses_id, p.products_id, p.stars" . " FROM products p" . " INNER JOIN products_stock ps ON ps.products_id = p.products_id" . " LEFT JOIN products_articles pa" . " ON pa.products_id = ps.products_id " . " AND pa.products_articles_id = ps.articles_id" . " WHERE ps.stock = 0" . " AND p.active_status = 1" . " AND IFNULL(pa.active_status, 1) = 1" . " ) dt" . " GROUP BY jng_warehouses_id, stars";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$ukey = $row['jng_warehouses_id'] . '-' . $row['stars'];
$sda = array();
$sda['total_assortment_nostock'] = $row['total_assortment_nostock'];
if (!in_array($ukey, $wh_exist)) {
$sda['date'] = $date;
$sda['jng_warehouses_id'] = $row['jng_warehouses_id'];
$sda['stars'] = $row['stars'];
tep_db_perform('scorecard_warehouses', $sda);
$wh_exist[] = $ukey;
} else {
tep_db_perform('scorecard_warehouses', $sda, 'update', "date='{$date}' AND jng_warehouses_id={$row['jng_warehouses_id']} AND stars={$row['stars']}");
}
}
//================ KPI DEPOT DATA SUPPORT END
}
