/**
*
* @param Int $actual_stock Stock qty we're actually having on related depot warehouse
* @param Int $weight Product weight (0 for virtual product like voucher)
* @param Int $stars Current stars of product
* @param String $complexity Complexity flag of product
* @param Int $age Current age of product
* @param Int $total_sold_lastyear Last year total sold of product
* @param Int $total_sold_thisyear This year total sold of product
* @param Int $jng_sp_id Specified SP, this parameter can accept 2 values,
* 1) string or integer of jng_sp_id and
* 2) array result of class jng_sp function retrieveDetail
* @param Int $reported_stock Virtual stock qty of a product to report to SP
* @param Int $delivery_time Standard delivery time of a product to report to SP
* @param Boolean $is_wholesale_product
* @return Array
* <strong>stock</strong> = total stock to be reported to specified SP
* <br /><strong>stock_isreal</strong> = flag if stock is real (1) or virtual (0)
* <br /><strong>stock_ishide</strong> = flag to show (1) or hide (0) stock depending on special cases
* <br /><strong>delivery_time</strong> = number of days the product can be delivered
*/
function actualStockReport($actual_stock, $weight, $stars, $complexity, $age, $total_sold_lastyear, $total_sold_thisyear, $total_sold_last30d, $jng_sp_id, $reported_stock, $delivery_time, $is_wholesale_product = false)
{
//use below global variable so we only need to set the settings once even if the function called multiple times
if ($weight > 0) {
global $actualStockReport_settings;
if (!isset($actualStockReport_settings) || !is_array($actualStockReport_settings)) {
use_class('products_minierp');
$class_pm = new products_minierp();
$bgst = $class_pm->retrieveBestGoodSettings();
$actualStockReport_settings = array();
$actualStockReport_settings['jng_sp_id_affected_by_nostock_flag'] = array('JNG', '2');
//group of sp that will be affected by all hide flag
$actualStockReport_settings['hide_hard_flag'] = $bgst['hidenostock_hard'] == '1';
$actualStockReport_settings['hide_extreme_flag'] = $bgst['hidenostock_xtreme'] == '1';
$actualStockReport_settings['hide_nostock_flag'] = $bgst['hidenostock_somesp'] == '1';
$actualStockReport_settings['hide_oldproducts_flag'] = $bgst['hidenostock_oldproducts'] == '1';
$actualStockReport_settings['oldproducts_age_limit'] = $bgst['hidenostock_oldproducts_age'];
$actualStockReport_settings['oldproducts_sold_limit'] = $bgst['hidenostock_oldproducts_sold'];
$actualStockReport_settings['oldproducts_sold_limit_l30d'] = $bgst['hidenostock_oldproducts_sold_l30d'];
$actualStockReport_settings['oldproducts_sold_limit_yearcurrent'] = $bgst['hidenostock_oldproducts_sold_yearcurrent'];
$actualStockReport_settings['hide_newproducts_flag'] = $bgst['hidenostock_newproducts'] == '1';
$actualStockReport_settings['newproducts_age_limit'] = $bgst['hidenostock_newproducts_age'];
$actualStockReport_settings['newproducts_sold_limit_l30d'] = $bgst['hidenostock_newproducts_sold_l30d'];
$actualStockReport_settings['hidenostock_somesp_stars'] = $bgst['hidenostock_somesp_stars'];
}
if (is_array($jng_sp_id)) {
$sp = $jng_sp_id;
$jng_sp_id = $sp['jng_sp_id'];
} elseif ($jng_sp_id == 'JNG') {
//JULE & GRACE
$sp = array();
$sp['delivery_time'] = 3;
} else {
use_class('jng_sp');
$class_sp = new jng_sp();
$sp = $class_sp->retrieveDetail($jng_sp_id);
}
//======================================================================
// Rules are used to decide when to use Virtual Stock
// and when to use Real Stock, especially when we don't
// have real stock available
//
// Main Rule = We will only hide stock when we have no real stock
//======================================================================
//Rule 1: Hide stock if reported stock set for SP is 0
$hidestock_rule1 = $reported_stock == 0;
//Rule 2: Hide stock if:
// - hide no stock flag is on
// - product star is <= ____ star (configured by user)
// - SP is listed in the special list
$hidestock_rule2 = $actualStockReport_settings['hide_nostock_flag'] && $stars <= $actualStockReport_settings['hidenostock_somesp_stars'] && in_array($jng_sp_id, $actualStockReport_settings['jng_sp_id_affected_by_nostock_flag']);
//Rule 3: Hide stock if:
// - hide no stock extreme products flag is on
// - product complexity is extreme
$hidestock_rule3 = $actualStockReport_settings['hide_extreme_flag'] && $complexity == 'X';
//Rule 4: Hide stock if:
// - hide no stock hard products flag is on
// - product complexity is hard
$hidestock_rule4 = $actualStockReport_settings['hide_hard_flag'] && $complexity == 'H';
//Rule 5: Hide stock if:
// - hide no stock long tail products is on
// - product age > ____ days (configured by user)
// - total sold last year <= ____ (configured by user)
// - total sold last 30 days <= ____ (configured by user)
// - total sold this year <= ____ (configured by user)
$hidestock_rule5 = $actualStockReport_settings['hide_oldproducts_flag'] && $age >= $actualStockReport_settings['oldproducts_age_limit'] && $total_sold_lastyear <= $actualStockReport_settings['oldproducts_sold_limit'] && $total_sold_last30d <= $actualStockReport_settings['oldproducts_sold_limit_l30d'] && $total_sold_thisyear <= $actualStockReport_settings['oldproducts_sold_limit_yearcurrent'];
//Rule 6: Hide stock if:
// - hide no stock new products is on
// - product age <= ____ days (configured by user)
// - total sold last 30 days <= ____ (configured by user)
$hidestock_rule6 = $actualStockReport_settings['hide_newproducts_flag'] && $age <= $actualStockReport_settings['newproducts_age_limit'] && $total_sold_last30d <= $actualStockReport_settings['newproducts_sold_limit_l30d'];
//Rule 7: Hide all wholesale products
$hidestock_rule7 = $is_wholesale_product;
//===================== END OF HIDE STOCK RULES ========================
//Prepare the default result of this function to be returned
$result = array();
$result['stock'] = $reported_stock;
//0 = VIRTUAL, 1 = REAL
$result['stock_isreal'] = '0';
//The array key stock_ishide will keep the result from all rules
$result['stock_ishide'] = $hidestock_rule1 || $hidestock_rule2 || $hidestock_rule3 || $hidestock_rule4 || $hidestock_rule5 || $hidestock_rule6 || $hidestock_rule7 ? '1' : '0';
$result['delivery_time'] = $sp['delivery_time'];
//Check if we have real stock
if ($actual_stock > 0) {
//If yes, report real stock and fast delivery time
$result['stock'] = $actual_stock;
$result['stock_isreal'] = '1';
$result['delivery_time'] = '1';
} else {
//If no, check hide stock status
if ($result['stock_ishide'] == '1') {
$result['stock'] = '0';
$result['stock_isreal'] = '1';
}
//Check if a delivery time for virtual stock exist
if ($delivery_time > 0) {
$result['delivery_time'] = $delivery_time;
}
if ($hidestock_rule3 || $hidestock_rule4) {
//Just to be more safe, if products should be hidden because
//of Rule 3 or 4 (extreme/hard products) and we have no real
//stock, delivery time should be set to 3 times the normal one
$result['delivery_time'] = 3 * $result['delivery_time'];
}
}
} else {
//VIRTUAL PRODUCT WITH WEIGHT=0 (VOUCHERS)
$result = array();
$result['stock'] = $reported_stock;
$result['stock_isreal'] = '0';
//0=VIRTUAL, 1=REAL
$result['stock_ishide'] = '0';
$result['delivery_time'] = '1';
}
return $result;
}
#########################################
global $db;
require_once '../confy.php';
require_once '../functions.php';
require_once '../functions-2.php';
tep_db_connect();
list($input_id, $bctype) = split(";", tep_db_prepare_input(strtolower(trim($_SERVER['QUERY_STRING']))));
$qpid = tep_db_query("SELECT products_id, products_model FROM products WHERE products_id = {$input_id} OR products_model = {$input_id}");
$rpid = tep_db_fetch_array($qpid);
$product_id = $rpid['products_id'];
$product_model = $rpid['products_model'];
$products = array();
$is_best_product = false;
use_class('products_minierp');
$class_pm = new products_minierp();
$bg_settings = $class_pm->retrieveBestGoodSettings();
$q_min_sold = tep_db_query("SELECT {$bg_settings['log_column']} AS actual_sold FROM products_log WHERE products_id = {$product_id}");
$r_min_sold = tep_db_fetch_array($q_min_sold);
$bg_status = 'bad';
if ($r_min_sold['actual_sold'] >= $bg_settings['min_sold_good']) {
$bg_status = 'good';
}
if ($r_min_sold['actual_sold'] >= $bg_settings['min_sold_best']) {
$bg_status = 'best';
}
$qa = "SELECT p.products_id, 0 AS products_articles_id, pnc.products_length AS length FROM products p LEFT JOIN products_non_configurator pnc ON pnc.products_id = p.products_id";
$qa .= " WHERE p.products_id = {$product_id}";
$qa .= " UNION ALL";
$qa .= " SELECT products_id, products_articles_id, length FROM products_articles WHERE products_id = {$product_id}";
$pa = tep_db_query($qa);
if (tep_db_num_rows($pa) > 0) {
$ldate = explode('.', tep_db_prepare_input($_POST['ldate']));
$logistic_date = date('Y-m-d', strtotime($ldate[2] . '-' . $ldate[1] . '-' . $ldate[0]));
$logistic_code = strtolower($logpart) == 'dhl' ? 'DHL' . date('ymd', strtotime($logistic_date)) : tep_db_prepare_input($_POST['lcode']);
$logistic_weight = tep_db_prepare_input($_POST['lweight']);
$logistic_price = tep_db_prepare_input($_POST['lprice']);
$currency = tep_db_prepare_input($_POST['currency']);
$currency_value = calculateCurrency(1, $currency);
$awb_no = tep_db_prepare_input($_POST['awb_no']);
$cogs_purchase_price_multiplier = $bgst['cogs_purchase_price_multiplier'];
$use_cogs = $logistic_id > $last_logistic_id_without_cogs ? 1 : 0;
$logistic_id = $class_ml->createUpdate($logistic_id, $logistic_date, $logistic_code, $logistic_weight, $logistic_price, $currency, $currency_value, $awb_no, $cogs_purchase_price_multiplier, $use_cogs);
echo utf8_encode($logistic_id);
} elseif ($_POST['me_action'] == 'ADDPACKAGE') {
use_class('products_minierp');
$class_pm = new products_minierp();
$bgst = $class_pm->retrieveBestGoodSettings();
$logistic_id = tep_db_prepare_input($_POST['logistic_id']);
$logistic_partner = tep_db_prepare_input($_POST['logistic_partner']);
$package_type = tep_db_prepare_input($_POST['package_type']);
$package_id = tep_db_prepare_input($_POST['package_id']);
$class_ml->addPackage($logistic_id, $package_type, $package_id, $logistic_partner);
} elseif ($_POST['me_action'] == 'REMOVEPACKAGE') {
$logistic_id = tep_db_prepare_input($_POST['logistic_id']);
$logistic_partner = tep_db_prepare_input($_POST['logistic_partner']);
$package_type = tep_db_prepare_input($_POST['package_type']);
$package_id = tep_db_prepare_input($_POST['package_id']);
$class_ml->removePackage($logistic_id, $package_type, $package_id, $logistic_partner);
} elseif ($_POST['me_action'] == 'SAVEDATA') {
$logistic_id = tep_db_prepare_input($_POST['logistic_id']);
$data = array();
$data['logistic_id'] = $logistic_id;
function drawKPItable($kpi_title, $kpi_date = '', $hidetable = false)
{
global $design;
use_class('products_minierp');
$class_pm = new products_minierp();
$bgst = $class_pm->retrieveBestGoodSettings();
$kpi_data = array();
$kpi_target = array();
$descats = array();
$descat_count = array();
$descat_percent = array();
$kpi_type1 = in_array($kpi_title, array('WIP', 'FIN'));
//WIP: Work In Progress, FIN: Designs Finalized
$kpi_type2 = in_array($kpi_title, array('PCATIP', 'PCATF'));
//PCAT:Products Category >> IP:in Progress, F:Finalized
$kpi_type3 = in_array($kpi_title, array('PPRIP', 'PPRF'));
//PPR:Products Price >> IP:in Progress, F:Finalized
$kpi_type4 = in_array($kpi_title, array('WIPT', 'FINT'));
//WIPT: Work In Progress Type, FIN: Designs Finalized Type
$ksd = generateKPISupportData($kpi_title, $kpi_date);
$q = $ksd['q'];
$kpi_id = $ksd['kpi_id'];
$kpi_header = $ksd['kpi_header'];
$r = tep_db_query($q);
if ($kpi_type1) {
$kpi_target['W'] = $bgst['kpides_catnw'];
$kpi_target['R'] = $bgst['kpides_catr'];
while ($row = tep_db_fetch_array($r)) {
$kpi_data[$row['designs_category']] = $row['total_designs'];
}
$descats = design::getDesignCategory();
if (array_key_exists('', $kpi_data)) {
$descats[''] = '<span class="red">Unset</span>';
}
} elseif ($kpi_type2) {
$kpi_target[2] = $bgst['kpides_prodnl'];
$kpi_target[3] = $bgst['kpides_prodbl'];
$kpi_target[4] = $bgst['kpides_proder'];
$kpi_target[9] = $bgst['kpides_prodch'];
$kpi_target[28] = $bgst['kpides_prodpn'];
$kpi_target[29] = $bgst['kpides_prodrg'];
$kpi_target[30] = $bgst['kpides_prodset'];
$kpi_target[33] = $bgst['kpides_prodan'];
while ($row = tep_db_fetch_array($r)) {
$kpi_data[$row['products_category_id']] = $row['total_designs'];
}
$pcs = getProductsCategorySupportData();
$q = "SELECT {$pcs['field_id']} AS id, {$pcs['field_name']} AS name FROM {$pcs['tables']} WHERE {$pcs['filter']}";
$dbq = tep_db_query($q);
while ($r = tep_db_fetch_array($dbq)) {
$descats_temp[$r['id']] = $r['name'];
}
//SORT Product Category as Requested by user
$descats_sorts = array(4 => 1, 2 => 2, 3 => 3, 29 => 4, 30 => 5, 9 => 6, 28 => 7, 33 => 8, 35 => 9);
foreach ($descats_temp as $key => $val) {
$descats_temp2[$descats_sorts[$key]] = $key;
}
ksort($descats_temp2);
foreach ($descats_temp2 as $val) {
$descats[$val] = $descats_temp[$val];
}
} elseif ($kpi_type3) {
$kpi_target[1] = $bgst['kpides_pr1target'];
$kpi_target[2] = $bgst['kpides_pr2target'];
$kpi_target[3] = $bgst['kpides_pr3target'];
$kpi_target[4] = $bgst['kpides_pr4target'];
$kpi_target[5] = $bgst['kpides_pr5target'];
$kpi_target[6] = $bgst['kpides_pr6target'];
$kpi_target[7] = $bgst['kpides_pr7target'];
$kpi_target[8] = $bgst['kpides_pr8target'];
while ($row = tep_db_fetch_array($r)) {
if ($row['target_price'] >= $bgst['kpides_pr1bgn'] && $row['target_price'] <= $bgst['kpides_pr1end']) {
$kpi_data[1] += $row['total_designs'];
} elseif ($row['target_price'] >= $bgst['kpides_pr2bgn'] && $row['target_price'] <= $bgst['kpides_pr2end']) {
$kpi_data[2] += $row['total_designs'];
} elseif ($row['target_price'] >= $bgst['kpides_pr3bgn'] && $row['target_price'] <= $bgst['kpides_pr3end']) {
$kpi_data[3] += $row['total_designs'];
} elseif ($row['target_price'] >= $bgst['kpides_pr4bgn'] && $row['target_price'] <= $bgst['kpides_pr4end']) {
$kpi_data[4] += $row['total_designs'];
} elseif ($row['target_price'] >= $bgst['kpides_pr5bgn'] && $row['target_price'] <= $bgst['kpides_pr5end']) {
$kpi_data[5] += $row['total_designs'];
} elseif ($row['target_price'] >= $bgst['kpides_pr6bgn'] && $row['target_price'] <= $bgst['kpides_pr6end']) {
$kpi_data[6] += $row['total_designs'];
} elseif ($row['target_price'] >= $bgst['kpides_pr7bgn'] && $row['target_price'] <= $bgst['kpides_pr7end']) {
$kpi_data[7] += $row['total_designs'];
} elseif ($row['target_price'] > $bgst['kpides_pr8bgn']) {
$kpi_data[8] += $row['total_designs'];
}
}
$descats[1] = "{$bgst['kpides_pr1bgn']} - {$bgst['kpides_pr1end']}";
$descats[2] = "{$bgst['kpides_pr2bgn']} - {$bgst['kpides_pr2end']}";
$descats[3] = "{$bgst['kpides_pr3bgn']} - {$bgst['kpides_pr3end']}";
$descats[4] = "{$bgst['kpides_pr4bgn']} - {$bgst['kpides_pr4end']}";
$descats[5] = "{$bgst['kpides_pr5bgn']} - {$bgst['kpides_pr5end']}";
$descats[6] = "{$bgst['kpides_pr6bgn']} - {$bgst['kpides_pr6end']}";
$descats[7] = "{$bgst['kpides_pr7bgn']} - {$bgst['kpides_pr7end']}";
$descats[8] = "> {$bgst['kpides_pr8ge']}";
} elseif ($kpi_type4) {
$kpi_target['I'] = $bgst['kpides_typib'];
$kpi_target['E'] = $bgst['kpides_typeb'];
$kpi_target['T'] = $bgst['kpides_typth'];
while ($row = tep_db_fetch_array($r)) {
$kpi_data[$row['designs_type']] += $row['total_designs'];
}
$descats = design::getDesignType();
if (array_key_exists('', $kpi_data)) {
$descats[''] = '<span class="red">Unset</span>';
}
}
$descat_count_total = array_sum($kpi_data);
if (count($descats) > 0) {
foreach ($descats as $key => $dc) {
if ($descat_count_total == 0) {
$descat_count[$key] = 0;
$descat_percent[$key] = 0;
} else {
$descat_count[$key] = !isset($kpi_data[$key]) ? 0 : $kpi_data[$key];
$descat_percent[$key] = number_format($descat_count[$key] / $descat_count_total * 100, 1);
}
}
}
$kpi_table = '';
$kpi_table .= '<div id="' . $kpi_id . '" class="ibox ui-corner-all kpi-box" style="margin:0 0 10px 10px;float:left;' . ($hidetable ? 'display:none;' : '') . '">';
$kpi_table .= '<input type="hidden" value="' . $kpi_title . '"/>';
$kpi_table .= '<h3>KPI : ' . $kpi_header . '</h3>';
$kpi_table .= '<table class="spo" border="0" cellpadding="0" cellspacing="0">';
$kpi_table .= '<tr><td class="bold">' . (in_array($kpi_title, array('WIPT', 'FINT')) ? 'Type' : 'Category') . '</td><td class="bold" colspan="2" style="white-space:nowrap;">Total Designs</td><td class="bold" style="text-align:right;">Target</td></tr>';
foreach ($descats as $key => $dc) {
$kpi_table .= '<tr><td>' . $dc . '</td><td>' . $descat_count[$key] . '</td><td style="text-align:right;">' . $descat_percent[$key] . '%</td><td style="text-align:right;">' . $kpi_target[$key] . '%</td></tr>';
}
$kpi_table .= '<tr><td width="100" class="bold">Total Designs</td><td width="20" class="bold">' . $descat_count_total . '</td><td width="50"> </td><td width="50"> </td></tr>';
$kpi_table .= '</table></div>';
return $kpi_table;
}
public static function generatePMLog($date)
{
//THE CALCULATION WHICH USE STARS WILL NOT GIVE RELEVANT DATA WHEN CALCULATING BACKDATE
$sda = array();
$q = tep_db_query("SELECT COUNT(products_id) AS total_products_new FROM products WHERE active_status='1' AND products_date_added LIKE '{$date}%'");
$row = tep_db_fetch_array($q);
$sda['products_new'] = $row['total_products_new'];
$q = tep_db_query("SELECT COUNT(products_id) AS total_products_killed FROM products_removed WHERE removed_date LIKE '{$date}%'");
$row = tep_db_fetch_array($q);
$sda['products_killed'] = $row['total_products_killed'];
$q = tep_db_query("SELECT 1 FROM scorecard_pm WHERE date='{$date}'");
if (tep_db_num_rows($q) == 0) {
$sda['date'] = $date;
tep_db_perform('scorecard_pm', $sda);
} else {
tep_db_perform('scorecard_pm', $sda, 'update', "date='{$date}'");
}
//FOR BELOW DATA WE SAVED MONTHLY SO WE MUST CHANGE THE DATE TO ALWAYS IS THE END OF THE MONTH
$date_monthly = date('Y-m-t', strtotime($date));
$date_l90d = date('Y-m-d', strtotime("{$date_monthly} -90 days"));
$q = "SELECT p.stars, COUNT(p.products_id) AS total_products_new_l90d";
$q .= " FROM products p";
$q .= " WHERE p.active_status='1' AND p.price_margin>0 AND p.products_date_added>='{$date_l90d}' AND p.products_date_added<='{$date_monthly}'";
$q .= " GROUP BY stars";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$sda = array();
$sda['products_new_l90d'] = $row['total_products_new_l90d'];
$q = tep_db_query("SELECT 1 FROM scorecard_pm_stars WHERE period='{$date_monthly}' AND stars={$row['stars']}");
if (tep_db_num_rows($q) == 0) {
$sda['period'] = $date_monthly;
$sda['stars'] = $row['stars'];
tep_db_perform('scorecard_pm_stars', $sda);
} else {
tep_db_perform('scorecard_pm_stars', $sda, 'update', "period='{$date_monthly}' AND stars={$row['stars']}");
}
}
//STARS
$q = "SELECT stars, COUNT(products_id) AS total_products FROM `products` WHERE active_status='1' AND price_margin>0 GROUP BY stars";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$sda = array();
$sda['products_total'] = $row['total_products'];
$q = tep_db_query("SELECT 1 FROM scorecard_pm_stars WHERE period='{$date_monthly}' AND stars={$row['stars']}");
if (tep_db_num_rows($q) == 0) {
$sda['period'] = $date_monthly;
$sda['stars'] = $row['stars'];
tep_db_perform('scorecard_pm_stars', $sda);
} else {
tep_db_perform('scorecard_pm_stars', $sda, 'update', "period='{$date_monthly}' AND stars={$row['stars']}");
}
}
//FOR BELOW DATA WE SAVED MONTHLY AND NEVER UPDATE BACKDATE BECAUSE THE DATA WILL BE IRRELEVANT
//Only run filter if date is the actual month or actual date is maximum 1st day of a new month
if (strpos($date_monthly, date('Y-m')) !== false || $date_monthly == date('Y-m-d', strtotime('-1 day'))) {
use_class('products_minierp');
$class_pm = new products_minierp();
$bgst = $class_pm->retrieveBestGoodSettings();
//BRAND
$q = "SELECT p.products_brand_id, AVG(p.price_margin) AS average_margin, COUNT(p.products_id) AS products_total FROM products p WHERE p.active_status='1' AND p.price_margin>0 GROUP BY products_brand_id";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$sda = array();
$sda['average_margin'] = $row['average_margin'];
$sda['products_total'] = $row['products_total'];
$q = tep_db_query("SELECT 1 FROM scorecard_pm_brand WHERE period='{$date_monthly}' AND products_brand_id={$row['products_brand_id']}");
if (tep_db_num_rows($q) == 0) {
$sda['period'] = $date_monthly;
$sda['products_brand_id'] = $row['products_brand_id'];
tep_db_perform('scorecard_pm_brand', $sda);
} else {
tep_db_perform('scorecard_pm_brand', $sda, 'update', "period='{$date_monthly}' AND products_brand_id={$row['products_brand_id']}");
}
}
//PRICE GROUP
$q = "SELECT CASE";
$q .= " WHEN p.products_price BETWEEN {$bgst['pg1_bgn']} AND {$bgst['pg1_end']} THEN 'pg1'";
$q .= " WHEN p.products_price BETWEEN {$bgst['pg2_bgn']} AND {$bgst['pg2_end']} THEN 'pg2'";
$q .= " WHEN p.products_price BETWEEN {$bgst['pg3_bgn']} AND {$bgst['pg3_end']} THEN 'pg3'";
$q .= " WHEN p.products_price BETWEEN {$bgst['pg4_bgn']} AND {$bgst['pg4_end']} THEN 'pg4'";
$q .= " WHEN p.products_price BETWEEN {$bgst['pg5_bgn']} AND {$bgst['pg5_end']} THEN 'pg5'";
$q .= " WHEN p.products_price BETWEEN {$bgst['pg6_bgn']} AND {$bgst['pg6_end']} THEN 'pg6'";
$q .= " WHEN p.products_price BETWEEN {$bgst['pg7_bgn']} AND {$bgst['pg7_end']} THEN 'pg7'";
$q .= " WHEN p.products_price BETWEEN {$bgst['pg8_bgn']} AND {$bgst['pg8_end']} THEN 'pg8'";
//set prices not in range as price group pgX
$q .= " ELSE 'pgX' END AS price_group";
$q .= ", MAX(p.products_price) AS products_price_max";
$q .= ", AVG(p.price_margin) AS average_margin";
$q .= ", COUNT(p.products_id) AS products_total";
$q .= " FROM products p WHERE p.active_status='1' AND p.price_margin>0 GROUP BY price_group";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$sda = array();
if ($row['price_group'] == 'pgX') {
$sda['pricegroup_settings'] = 'Max Price = ' . $row['products_price_max'];
} else {
$sda['pricegroup_settings'] = $bgst[$row['price_group'] . '_bgn'] . ' - ' . $bgst[$row['price_group'] . '_end'];
}
$sda['average_margin'] = $row['average_margin'];
$sda['products_total'] = $row['products_total'];
$q = tep_db_query("SELECT 1 FROM scorecard_pm_pricegroup WHERE period='{$date_monthly}' AND pricegroup_alias='{$row['price_group']}'");
if (tep_db_num_rows($q) == 0) {
$sda['period'] = $date_monthly;
$sda['pricegroup_alias'] = $row['price_group'];
tep_db_perform('scorecard_pm_pricegroup', $sda);
} else {
tep_db_perform('scorecard_pm_pricegroup', $sda, 'update', "period='{$date_monthly}' AND pricegroup_alias='{$row['price_group']}'");
}
}
//CATEGORIES
$q = "SELECT ptc.categories_id, AVG(p.price_margin) AS average_margin, COUNT(p.products_id) AS products_total FROM products p LEFT JOIN products_to_categories ptc ON ptc.products_id=p.products_id WHERE p.active_status='1' AND p.price_margin>0 GROUP BY categories_id";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$sda = array();
$sda['average_margin'] = $row['average_margin'];
$sda['products_total'] = $row['products_total'];
$q = tep_db_query("SELECT 1 FROM scorecard_pm_categories WHERE period='{$date_monthly}' AND categories_id={$row['categories_id']}");
if (tep_db_num_rows($q) == 0) {
$sda['period'] = $date_monthly;
$sda['categories_id'] = $row['categories_id'];
tep_db_perform('scorecard_pm_categories', $sda);
} else {
tep_db_perform('scorecard_pm_categories', $sda, 'update', "period='{$date_monthly}' AND categories_id={$row['categories_id']}");
}
}
}
}
function calculateStars($sold)
{
//currently $sold value is Last 30 Days sold (L30D)
use_class('products_minierp');
$class_pm = new products_minierp();
$bgst = $class_pm->retrieveBestGoodSettings();
$stars = 0;
if ($sold >= $bgst['e_star0_bgn'] && $sold <= $bgst['e_star0_end']) {
$stars = 0;
} elseif ($sold >= $bgst['e_star1_bgn'] && $sold <= $bgst['e_star1_end']) {
$stars = 1;
} elseif ($sold >= $bgst['e_star2_bgn'] && $sold <= $bgst['e_star2_end']) {
$stars = 2;
} elseif ($sold >= $bgst['e_star3_ge']) {
$stars = 3;
}
return $stars;
}
public static function calculatePurchasePrice($cogs_or_matexp)
{
global $class_pm;
if (!is_object($class_pm)) {
$class_pm = new products_minierp();
}
$bgst = $class_pm->retrieveBestGoodSettings();
return round($bgst['cogs_purchase_price_multiplier'] * $cogs_or_matexp, 2);
}
public static function loadDIOHsettings()
{
use_class('products_minierp');
$class_pm = new products_minierp();
$bgst = $class_pm->retrieveBestGoodSettings();
self::$diohStopLevel = array();
self::$diohStopLevel[0] = $bgst['e_star0_dioh'];
self::$diohStopLevel[1] = $bgst['e_star1_dioh'];
self::$diohStopLevel[2] = $bgst['e_star2_dioh'];
self::$diohStopLevel[3] = $bgst['e_star3_dioh'];
self::$adjustmentOrder = $bgst['eao'];
self::$dtMultiplier = $bgst['dt_multiplier'];
}
