if ($orders_type == 'SP') {
$status = $class_jo->setOrderPriority($orders_id, $priority);
} elseif ($orders_type == 'JG') {
//currently not supported
}
$ajaxResult = array();
$ajaxResult['orders_type'] = $orders_type;
$ajaxResult['orders_id'] = $orders_id;
$ajaxResult['priority'] = $priority ? '1' : '0';
$ajaxResult['status'] = $status;
ajaxReturn($ajaxResult);
exit;
}
}
$spdata = array();
$spdata_raw = $class_sp->retrieveList();
foreach ($spdata_raw as $spd) {
$spdata[$spd['jng_sp_id']] = $spd['name'];
}
$order_source = '<select name="{NAME}" class="input">';
$order_source .= '<option value=""></option>';
$order_source .= '<option value="0">JULIE & GRACE</option>';
$order_source .= loadComboListFromArray($spdata);
$order_source .= '</select>';
$order_status = '<select name="{NAME}" class="input">';
$order_status .= '<option value=""></option>';
$order_status .= loadComboListFromArray($statusName);
$order_status .= '</select>';
$stock_status = '<select name="{NAME}" class="input">';
$stock_status .= loadComboListFromArray($stock_status_options);
$stock_status .= '</select>';
set_time_limit(0);
$mt_start = microtime();
global $db;
require_once '../confy.php';
require_once '../functions.php';
require_once '../functions-2.php';
tep_db_connect();
use_class('logger');
$logger = new logger('cron', 'daily-counter');
use_class('jng_sp');
use_class('jng_sp_orders');
use_class('jng_sp_catalog');
$class_sp = new jng_sp();
$class_jo = new jng_sp_orders();
$class_jc = new jng_sp_catalog();
$sp_data = $class_sp->retrieveList();
//////////////////////////////
// SP PRODUCTS SOLD COUNTER //
//////////////////////////////
$logger->write('1 - Product Sold Counter');
//TODO: The calculation is still based on jng_sp_catalog, which means SP with no
//catalog (like buyvip/retails) will not be included in this calculation!
foreach ($sp_data as $sp_list) {
$class_jo->productsSoldCounter($sp_list['jng_sp_id']);
}
unset($sp);
/* WE STOP CALCULATING VC USING OLD METHOD
* NEW VC CALCULATION IS NOT IN ORDER BASED, BUT IN SP BASED
///////////////////////
// SP VC CALCULATION //
///////////////////////
$format_date = 'd M';
$days = isset($_GET['d']) ? tep_db_prepare_input($_GET['d']) : 60;
$y_points = 10;
for ($p = $days; $p >= 0; $p--) {
$format = date($format_date, strtotime("-{$p} days"));
$period[] = $format;
$bar_values[$format] = 0;
}
$q = " SELECT * FROM";
$q .= " (SELECT o.date_purchased AS order_date, op.products_quantity AS products";
$q .= " FROM orders o";
$q .= " LEFT JOIN orders_products op ON op.orders_id=o.orders_id";
$q .= " WHERE o.date_purchased>=DATE_SUB(CURRENT_DATE(), INTERVAL {$days} DAY)";
$q .= " AND o.orders_status<=10";
$q .= " UNION ALL";
$sp_active = $class_sp->retrieveList();
//Products sold on sp's
$q .= " SELECT jo.order_date, joi.order_quantity AS products";
$q .= " FROM jng_sp_orders jo";
$q .= " LEFT JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_id=jo.jng_sp_orders_id";
$q .= " WHERE jo.jng_sp_id IN (" . implode(',', array_keys($sp_active)) . ") AND jo.order_date>=DATE_SUB(CURRENT_DATE(), INTERVAL {$days} DAY) AND joi.status NOT IN (10,12)";
$q .= ")dt ORDER BY order_date";
$res = tep_db_query($q);
while ($r = tep_db_fetch_array($res)) {
$p = date($format_date, strtotime($r['order_date']));
$bar_values[$p] += $r['products'];
}
//Start constructing charts
$title = new title('Last ' . $days . ' Days Sales Chart');
$title->set_style("{font-size:14px; font-weight:bold; padding:10px;}");
$chart = new open_flash_chart();
$content .= ' <h2>Antitarnish Liquid Usage</h2>';
$content .= ' <table border="0" cellpadding="0" cellspacing="0">';
$content .= ' <tr><th style="width:150px;">Configuration</th><th colspan="2" style="width:150px;">Value</th></tr>';
$content .= ' <tr class="o"><td>Silver Usage</td><td>' . $input72 . '</td><td>gram</td></tr>';
$content .= ' <tr class="e"><td>Antitarnish Usage</td><td>' . $input73 . '</td><td>liter</td></tr>';
$content .= ' </table>';
$content .= ' </div>';
$content .= '</div>';
//#production .draw-table
//
//Production Instruction Settings Form
use_class('jng_sp');
use_class('products_brand');
$class_sp = new jng_sp();
$class_pb = new products_brand();
$sps = $class_sp->retrieveList();
$brands = $class_pb->retrieveList('active_status=1');
$content .= '<div id="production-instruction" class="draw-table" style="clear:both;padding-top:20px;">';
$content .= ' <div style="float:left;">';
$content .= ' <h2>Production Instruction (PI) Using Red Paper</h2>';
$content .= ' <table border="0" cellpadding="0" cellspacing="0">';
$content .= ' <tr><th style="width:150px;">Configuration</th><th style="width:550px;">Value</th></tr>';
$content .= ' <tr class="o"><td>Sales Partner</td><td>';
$checked = in_array('0', $sp_using_red_paper) ? 'checked="checked"' : '';
$cbid = 'set-pi-red-sp-0';
$content .= '<div class="float-left w080 tal" style="margin:2px 0;"><input type="checkbox" id="' . $cbid . '" name="' . $cbid . '" value="0" ' . $checked . '/><label for="' . $cbid . '"> JG.DE</label></div>';
foreach ($sps as $sp_id => $dt) {
$cbid = 'set-pi-red-sp-' . $sp_id;
$checked = in_array($sp_id, $sp_using_red_paper) ? 'checked="checked"' : '';
$content .= '<div class="float-left w080 tal" style="margin:2px 0;"><input type="checkbox" id="' . $cbid . '" name="' . $cbid . '" value="' . $sp_id . '" ' . $checked . '/><label for="' . $cbid . '"> ' . $dt['package_prefix'] . '</label></div>';
}
// STEP 2: GENERATE ORDERS //
/////////////////////////////
logThis('STEP 2: GENERATE ORDERS');
$files_imported = 0;
$file_not_exist = array();
//$order_files[] = SP_TRADEBYTE_DOWNLOAD_ORDERS_PATH.'TBORDER_123.xml';
$order_files = $class_jd->getFilesForProcess(0, 'R');
$order_files_total = count($order_files);
logThis($order_files_total . ' file(s) found:');
$orders_created = array();
$items_created = 0;
$orders_prepayment_paid = array();
$orders_canceled = array();
if ($order_files_total > 0) {
//Get all known SP with connection to Tradebyte
$sp_list = $class_sp->retrieveList("tb_used = 1");
$sp_tradebyte_id = array();
foreach ($sp_list as $sp) {
$sp_tradebyte_id[$sp['tb_channel']] = $sp['jng_sp_id'];
}
$file_counter = 0;
$daily_counter = array();
foreach ($order_files as $o_file) {
$file_counter++;
logThis('- Processing file ' . $file_counter . ': ' . $o_file['filename']);
$xml_filename = TRADEBYTE_DOWNLOAD_ORDERS_PATH . $o_file['filename'];
if (is_file($xml_filename)) {
$import_date = date('Y-m-d H:i:s');
$leadtime_start_date = $import_date;
$xml = $xmlt->loadFile($xml_filename);
$orders = $xml->ORDER;
if ($row['jng_sp_id'] > 0) {
tep_db_perform('jng_sp_orders_items', $sda, 'update', "jng_sp_orders_items_id = {$row['item_id']}");
$sp_counter += tep_db_affected_rows();
} else {
tep_db_perform('orders_products', $sda, 'update', "orders_products_id = {$row['item_id']}");
$jg_counter += tep_db_affected_rows();
}
//echo ' - updated';
}
//echo '<br />';
}
$logger->write("Filling mat-exp & cogs for JG orders: {$jg_counter} updated");
$logger->write("Filling mat-exp & cogs for SP orders: {$sp_counter} updated");
//Initial configuration for COGS calcultion
$bgst = $class_pm->retrieveBestGoodSettings();
$sp_included = $class_sp->retrieveList('sp_type = ' . SalesPartner::TYPE_B2C);
$sp_ids_included = array_keys($sp_included);
$logger->write('SP ID Included: ' . implode(',', $sp_ids_included));
//Set the date range to calculate
if (isset($_GET['start']) && isset($_GET['end'])) {
//MANUAL SET DATE RANGE
$date_start = date('Y-m-d', strtotime($_GET['start']));
$date_end = date('Y-m-d', strtotime($_GET['end']));
} else {
$lookback = '60 days';
$date_end = date('Y-m-d', strtotime('yesterday'));
$date_start = date('Y-m-d', strtotime("{$date_end} -{$lookback}"));
}
$logger->write('Date range is ' . $date_start . ' until ' . $date_end);
//PROCESS JNG, use $jng_sp_id = 0
$jng_sp_id = '0';
$xmlt->drawTag($xml_message, 'QUANTITY', intval($msg_data['qty']));
//EXCLUDE CANCELATIONS FROM ADDING CARRIER/IDCODE TAG
if ($type == 'S' || $type == 'R') {
$xmlt->drawTag($xml_message, 'CARRIER_PARCEL_TYPE', 'DHL_STD_NATIONAL');
if ($type == 'S') {
$xmlt->drawTag($xml_message, 'IDCODE', $msg_data['idcode_ship']);
$xmlt->drawTag($xml_message, 'IDCODE_RETURN_PROPOSAL', $msg_data['idcode_ret']);
} elseif ($type == 'R') {
$xmlt->drawTag($xml_message, 'IDCODE', $msg_data['idcode_ret']);
}
}
//DATE_CREATED
$xmlt->drawTag($xml_message, 'DATE_CREATED', $date_created);
}
//COLLECT ALL POSSIBLE MESSAGES
$jng_sp_active = $class_sp->retrieveList("tb_used=1", "name");
$sp_detail = array();
$data = array();
foreach ($jng_sp_active as $temp_sp) {
$jng_sp_id = $temp_sp['jng_sp_id'];
$data[$jng_sp_id] = array();
$sp_detail[$jng_sp_id] = $temp_sp;
//DELIVERY & CANCELS
$delivery_n_cancels_data = $class_jo->getUnconfirmedDelivery($jng_sp_id);
if (count($delivery_n_cancels_data['orders']) > 0) {
$data[$jng_sp_id]['S'] = $delivery_n_cancels_data;
}
//RETURNS
$returns_data = $class_jo->getUnconfirmedReturns($jng_sp_id);
if (count($returns_data['orders']) > 0) {
$data[$jng_sp_id]['R'] = $returns_data;
<?php
use_class('jng_sp');
$class_sp = new jng_sp();
use_class('products_brand');
$class_pb = new products_brand();
$brands = $class_pb->retrieveList();
$jng_sp_active = $class_sp->retrieveList("", "jng_sp_id");
//GET PAGE
$rowperpage = ROWPERPAGE;
$session_page = 'sp-products-activation';
$page = 1;
if (isset($_GET['page'])) {
$page = $_GET['page'];
}
//SUBMIT ACTION
if (isset($_POST['me_action'])) {
if ($_POST['me_action'] == 'SETACTIVATION') {
$jng_sp_id = tep_db_prepare_input($_POST['jng_sp_id']);
$products_id = tep_db_prepare_input($_POST['products_id']);
$status = tep_db_prepare_input($_POST['status']);
if ($jng_sp_id == '0') {
//JNG ACTIVATION
$sda = array('products_status' => $status);
if ($status == '1') {
$active_date = date('Y-m-d H:i:s');
$sda['products_last_modified'] = $active_date;
}
tep_db_perform('products', $sda, 'update', "products_id={$products_id}");
} else {
//SP ACTIVATION
function updateStatusPrice($products_id, $status, $jng_sp_id, $filter = '')
{
$type = strtolower($type);
if ($filter != '') {
$filter = " AND {$filter}";
}
if (is_array($products_id)) {
$products_id = implode(',', $products_id);
}
$sda = array();
$sda['status_price'] = $status;
if ($jng_sp_id == '0') {
tep_db_perform('products', $sda, 'update', "(products_id IN ({$products_id}) {$filter})");
} elseif ($jng_sp_id > 0) {
use_class('jng_sp');
$class_sp = new jng_sp();
$sptb = $class_sp->retrieveList('tb_used=1');
$sp_on_tb = in_array($jng_sp_id, array_keys($sptb));
if (!$sp_on_tb) {
$sda['image_uploaded'] = '0';
}
tep_db_perform('jng_sp_catalog', $sda, 'update', "(products_id IN ({$products_id}) AND jng_sp_id = {$jng_sp_id} {$filter})");
if ($sp_on_tb) {
use_class('products_minierp');
$class_pm = new products_minierp();
$class_pm->stockUpdateTBNeedUpdateStatus(WAREHOUSE_ID_PRODUCTS_HAMBURG, $products_id, '1');
}
}
}
if ($product['pnc']['products_length'] > 0) {
$length .= '<strong>' . textLength($product['pnc']['products_length']) . '</strong>';
$articles = $class_pa->retrieveList($products_id);
if (count($articles) > 0) {
foreach ($articles as $a) {
$length .= ' ⋅ ';
$length .= textLength($a['length']);
}
}
}
$added_to_amvd = false;
$amvd_provision = 0;
$amvd_catalog = array('jng_sp_catalog_id' => '0', 'price_new' => '');
$spq = tep_db_query("SELECT jc.jng_sp_id FROM jng_sp_catalog jc INNER JOIN jng_sp ON jng_sp.jng_sp_id=jc.jng_sp_id AND jng_sp.active_status=1 WHERE products_id={$products_id}");
if (tep_db_num_rows($spq) > 0) {
$spall = $class_sp->retrieveList();
$spname = array();
foreach ($spall as $sp) {
$spname[$sp['jng_sp_id']] = $sp['package_prefix'];
if ($sp['jng_sp_id'] == '5') {
$amvd_provision = $sp['provision_rate'];
}
}
$spadded_arr = array();
while ($row = tep_db_fetch_array($spq)) {
$spadded_arr[] = $spname[$row['jng_sp_id']];
if ($row['jng_sp_id'] == '5') {
$added_to_amvd = true;
$amvd_catalog = $class_jc->retrieveDetail(null, '5', $products_id);
}
}
$username = (isset($_GET['user'])) ? tep_db_prepare_input($_GET['user']) : 'MC';
*
*/
use_class('orders');
use_class('jng_sp');
use_class('jng_sp_orders');
use_class('payone_invoice');
$class_o = new orders();
$class_sp = new jng_sp();
$class_jo = new jng_sp_orders();
$order_type = strtoupper(tep_db_prepare_input($_POST['type']));
$order_type_total = strlen($order_type);
$item_id = tep_db_prepare_input($_POST['id']);
$req_status = tep_db_prepare_input($_POST['status']);
$username = isset($_POST['user']) ? tep_db_prepare_input($_POST['user']) : 'MC';
$SP_DOESNT_NEED_EASYLOG = array_keys($class_sp->retrieveList('use_easylog=0'));
$SP_INVDELDOC_USING_OTHERMODULE = array_keys($class_sp->retrieveList('print_invoice=0'));
//USE THIS FUNCTION TO RETURN WHATS NEEDED BY MANOBO CLIENT WHEN ORDER IS FOUND, THIS FUNCTION WILL HELP TO MAKE SURE WE SEND ALL REQUIRED DATA
function mc_orderDetail($sp_id, $sp_code, $pcl_file, $reference, $display_counter, $company, $name, $address, $city, $postcode, $payment_method, $cod, $country = '', $need_repackage = '0')
{
global $SP_DOESNT_NEED_EASYLOG, $SP_INVDELDOC_USING_OTHERMODULE;
$o = array();
$o['status'] = 'FOUND';
//TEMPORARY SOLUTION FOR AM.FR
//TODO: FIND a fixed solution
$o['jng_sp_id'] = $sp_id;
$o['sp_code'] = $sp_code;
$o['pcl'] = $pcl_file;
$o['print_invdd'] = in_array($sp_id, $SP_INVDELDOC_USING_OTHERMODULE) ? '0' : '1';
$o['print_label'] = $country == 'Austria' || in_array($sp_id, $SP_DOESNT_NEED_EASYLOG) ? '0' : '1';
$o['reference'] = $reference;
/**
* This module created to manage pricing display on website for all SP (include JG)
* @author IT TEAM BONOFACTUM
* @created Oct 11, 2013 2:56:38 PM
*/
//TODO:
//1) double check if we can use it directly for the bulk items when we want to start with amazon also in the future
//2) apply "SP Catalog Prices features" here also then we could start replace it with this module
use_class('jng_sp');
use_class('products_minierp');
use_class('product');
$class_sp = new jng_sp();
$class_pm = new products_minierp();
//exclude AM.VD, AM.FR, and BuyVip for now since it still need manual process to update price
$jng_sp_active = $class_sp->retrieveList("jng_sp_id NOT IN (5,7,9)", "jng_sp_id");
function getIconEyePricesBasedOnRules($status_price, $type = 'sp')
{
$eye = array();
$eye['price_old'] = '';
$eye['price'] = '';
$icon_eye_act_p = '<span class="icon_eye_act price" style="margin-left:5px;cursor:pointer" title="Click to hide this price on ' . strtoupper($type) . ' website"></span>';
$icon_eye_act_po = '<span class="icon_eye_act price_old" style="margin-left:5px;cursor:pointer" title="Click to hide this price on ' . strtoupper($type) . ' website"></span>';
$icon_eye_dis_p = '<span class="icon_eye_dis price" style="margin-left:5px;cursor:pointer" title="Click to display this price on ' . strtoupper($type) . ' website"></span>';
$icon_eye_dis_po = '<span class="icon_eye_dis price_old" style="margin-left:5px;cursor:pointer" title="Click to display this price on ' . strtoupper($type) . ' website"></span>';
switch ($status_price) {
case 1:
$eye['price'] = $icon_eye_act_p;
$eye['price_old'] = $icon_eye_act_po;
break;
case 2:
function getUnconfirmedOrders($jng_sp_id)
{
//TODO: move below configuration to a flag for each SP (confirmation wait for stock checking)
//Updated: currently we try using "use_amvd_logistic" flag
//$spid_confirm_need_stock_checking = '5,7,9'; //old code to set spid manually
use_class('jng_sp');
$class_sp = new jng_sp();
$sp_list = $class_sp->retrieveList('use_amvd_logistic=1');
if (count($sp_list) == 0) {
$spid_confirm_need_stock_checking = '0';
} else {
$spid_confirm_need_stock_checking = implode(',', array_keys($sp_list));
}
$q = "SELECT jo.* FROM jng_sp_orders jo";
$q .= " LEFT JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_id=jo.jng_sp_orders_id";
$q .= " WHERE";
if ($jng_sp_id != '0') {
$q .= " jo.jng_sp_id={$jng_sp_id} AND ";
}
$q .= " jo.confirm_status='0' AND ";
$q .= " (";
//for other SP ID
$q .= " jo.jng_sp_id NOT IN ({$spid_confirm_need_stock_checking})";
$q .= " OR";
//check if SP ID need stock checking
$q .= " (jo.jng_sp_id IN ({$spid_confirm_need_stock_checking}) AND ";
//if yes check also if stock status is already changed
$q .= " (joi.stock_status!='0'";
//or shipping window is not yet released (because we don't
//need to wait until released for confirmation)
$q .= " OR " . $this->filterQueryForShippingWindow(null, true) . ")";
$q .= " ))";
$q .= " GROUP BY jng_sp_orders_id";
$orders_query = tep_db_query($q);
$orders = array();
while ($row = tep_db_fetch_array($orders_query)) {
$orders[] = $row;
}
return $orders;
}
<?php
/**
* This module used to generate analysis product sold per week
* @author IT TEAM BONOFACTUM
* @created Jun 24, 2014 4:50:57 PM
*/
use_class('jng_sp');
use_class('jng_sp_catalog');
use_class('Product');
use_class('products_minierp');
use_class('ReportCOGS');
$class_sp = new jng_sp();
$class_pm = new products_minierp();
$class_jc = new jng_sp_catalog();
$sp_b2b = $class_sp->retrieveList('active_status = 1 AND use_amvd_logistic = 1');
foreach ($sp_b2b as $sp_b2b_dt) {
$sp_b2b_ids[] = $sp_b2b_dt['jng_sp_id'];
}
function getStartAndEndDate($week, $year)
{
$dto = new DateTime();
$dto->setISODate($year, $week);
$return['start_date'] = $dto->format('Y-m-d');
$dto->modify('+6 days');
$return['end_date'] = $dto->format('Y-m-d');
return $return;
}
if (isset($_POST['me_action']) && $_POST['me_action'] == 'PROCESS') {
$spid = strtoupper(tep_db_prepare_input($_POST['spid']));
$products_id = tep_db_prepare_input($_POST['pid']);
$logger = new logger('cron', 'sp-2-orders-confirmer');
$timestamp = date('Y-m-d H:i:s');
$RUNTIME = array('10', '40');
//Cron will be processed only on minute $RUNTIME
//10 and 40 because we download new orders every 00 and 30
//so in minutes 10 and 40 we will confirm the orders
if (in_array(date('i'), $RUNTIME)) {
use_class('jng_sp');
use_class('jng_sp_upload');
use_class('jng_sp_orders');
use_class('products_ean');
$class_sp = new jng_sp();
$class_ju = new jng_sp_upload();
$class_jo = new jng_sp_orders();
$class_ean = new products_ean();
$jng_sp_active = $class_sp->retrieveList("active_status=1");
$logger->write("Task 1 (CHECK UNCONFIRMED ORDERS) - Start");
foreach ($jng_sp_active as $temp_sp) {
$jng_sp_id = $temp_sp['jng_sp_id'];
$jng_sp_name = $temp_sp['name'];
$orders = $class_jo->getUnconfirmedOrders($jng_sp_id);
if (count($orders) == 0) {
$logger->write("There is no new order to confirm");
} else {
switch ($jng_sp_id) {
case '2':
$need_jms_upload = false;
$orders_confirmed = 0;
//1 XML FILE PER ORDER
$ts_counter_key = '';
foreach ($orders as $counter => $o) {
}
$content .= '<form name="pbrand" action="?open=products-brand" method="post" enctype="multipart/form-data">';
$total_products = $brand_id > 0 ? $class_pb->getTotalProducts($brand_id) : 0;
if (!$is_deleted) {
$content .= '<input type="hidden" name="me_action" value="SAVEPRODUCTSBRAND" />';
$content .= '<input type="hidden" name="brand_id" value="' . $brand_id . '" />';
$content .= '<table border="0" cellpadding="0" cellspacing="0">';
$content .= '<tr><td width="130">Active Status</td><td><input type="checkbox" id="brandstatus" name="active_status" value="1" ' . $active_status . ' /> <label for="brandstatus" class="red">Check this to activate brand in J&G Website</label></td></tr>';
$content .= '<tr><td>Active Products</td><td>' . strval($total_products) . '</td></tr>';
$content .= '<tr><td>Brand Name</td><td><input type="text" name="brand_name" value="' . $brand_name . '" class="input2" /></td></tr>';
$content .= '<tr><td>Brand Description</td><td><textarea id="brand-desc" name="brand_description" class="input2" style="height:100px;">' . $brand_desc . '</textarea></td></tr>';
$content .= '<tr><td>SEO Brand Text</td><td><textarea id="seo_text" name="seo_text" class="input2" style="height:100px;">' . $seo_text . '</textarea></td></tr>';
$content .= '<tr><td>Upload Brand Logo</td><td><input type="file" name="brand_image" /></td></tr>';
$content .= '<tr><td>Need Repackaging</td><td><input type="checkbox" name="need_repackaging" ' . $need_repackaging . ' value="1" /></td></tr>';
$content .= '<tr><td colspan="2" style="padding-top:20px;"><h3>Brand ID in Sales Partner</h3></td></tr>';
$partners = $class_sp->retrieveList();
$sp_brands = $brand_id != '' ? $class_pb->getSPbrands($brand_id) : array();
foreach ($partners as $p) {
$sp_brand_id = isset($sp_brands[$p['jng_sp_id']]) ? $sp_brands[$p['jng_sp_id']] : '';
$content .= '<tr><td>' . $p['name'] . '</td><td><input type="text" name="sp_brand_id[' . $p['jng_sp_id'] . ']" value="' . $sp_brand_id . '" class="input" /></td></tr>';
}
}
$content .= '</table>';
$content .= '<div class="buttons w500">';
$content .= '<div>';
if (!$is_deleted) {
$content .= '<input type="submit" name="submit" value="' . $brand_action . '" class="blue" />';
// if($id>0) $content .= '<div style="float:right;">'.buttonWithIcon("ui-icon-trash", "btn-delete", '', 'Delete this brand').'</div>';
}
$content .= '<input type="button" id="kill-brand" class="red" value="Kill Brand" title="Click this button to delete brand, all products under this brand will be automatically killed" />';
$content .= '</div>';
public static function retrieveDailyAverageSoldExcludingPromotionOrdersQuery($date_start, $total_days_to_lookback, $pid_aid_filter = '')
{
use_class('jng_sp');
$class_sp = new jng_sp();
$sp_list = $class_sp->retrieveList('use_in_forecast=1');
$sp_filter = implode(',', array_keys($sp_list));
//WE MUST REDUCE 1 DAY BECAUSE "date - X days" when both days are
//considered will give (X+1) total days instead.
$total_days_to_lookback_calc = $total_days_to_lookback - 1;
//start and end is reversed because we're looking backward
$date_start_query = date('Y-m-d', strtotime($date_start));
$date_end_query = date('Y-m-d', strtotime("{$date_start_query} -{$total_days_to_lookback_calc} days"));
$q = "SELECT products_id, articles_id, SUM(total_sold) AS total_sold FROM (";
//SP Query
$q .= " SELECT joi.products_id, joi.products_articles_id AS articles_id, SUM(joi.order_quantity) AS total_sold";
$q .= " FROM jng_sp_orders jo";
$q .= " INNER JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_id=jo.jng_sp_orders_id AND joi.status NOT IN (10,12)";
$q .= " WHERE jo.jng_sp_id IN ({$sp_filter})";
$q .= " AND joi.shipping_window_open<='{$date_start_query}' AND joi.shipping_window_open>='{$date_end_query}'";
if ($pid_aid_filter != '') {
$q .= " AND {$pid_aid_filter}";
}
$q .= " AND NOT " . isPromotionalOrderQuery('SP');
$q .= " GROUP BY products_id, articles_id";
$q .= " UNION ALL ";
//JG Query
$date_start_query .= ' 23:59:59';
$date_end_query .= ' 00:00:00';
$q .= " SELECT op.products_id, op.products_articles_id AS articles_id, SUM(op.products_quantity) AS total_sold";
$q .= " FROM orders o";
$q .= " INNER JOIN orders_products op ON op.orders_id=o.orders_id AND op.status NOT IN (10,12)";
$q .= " WHERE o.date_purchased<='{$date_start_query}' AND o.date_purchased>='{$date_end_query}'";
if ($pid_aid_filter != '') {
$q .= " AND {$pid_aid_filter}";
}
$q .= " AND NOT " . isPromotionalOrderQuery('JG');
$q .= " GROUP BY products_id, articles_id";
$q .= ") temp_table";
$q .= " GROUP BY products_id, articles_id";
$q .= " ORDER BY products_id, articles_id";
$r = tep_db_query($q);
$result = array();
while ($row = tep_db_fetch_array($r)) {
$row['daily_average'] = round($row['total_sold'] / $total_days_to_lookback, 2);
$result[] = $row;
}
return $result;
}
private function renderFilter()
{
use_class('jng_sp');
use_class('products_brand');
use_class('CategoryTop');
$class_sp = new jng_sp();
$class_pb = new products_brand();
$sp_datas = $class_sp->retrieveList();
$brands = $class_pb->retrieveList("active_status='1'");
//FILTER Order Type
$order_type_selected = self::getSession(self::FILTER_ORDER_TYPE);
$order_types = array(self::ORDER_TYPE_ALL => 'All Orders', self::ORDER_TYPE_JG => 'JG Orders');
$order_type_groups['SP Orders'][self::ORDER_TYPE_SP] = 'All SP Orders';
foreach ($sp_datas as $sp_id => $sp_data) {
$order_type_groups['SP Orders'][$sp_id] = $sp_data['name'];
}
$order_type_groups['Depot Orders'] = $this->DP_ORDER_TYPES;
$filter_order_type = '<select name="' . self::FILTER_ORDER_TYPE . '" class="cbo_filters">' . loadComboListFromArray($order_types, null, $order_type_selected, true, $order_type_groups) . '</select>';
/* Filter SP now merged into Filter Order Type
array_unshift($sp_datas, array(self::FILTER_SP_ID => '0', 'name' => 'All Sales Partners'));
$filter_sp = $class_sp->drawCombo($sp_datas, $this->filters_sp_id, '', self::FILTER_SP_ID, 'cbo_filters');
*/
//FILTER Brand
$brand_options = array();
$brand_options[0] = 'Show All Brands';
foreach ($brands as $b) {
$brand_options[$b['products_brand_id']] = $b['brand_name'];
}
$filter_brand = '<select name="' . self::FILTER_BRAND_ID . '" class="cbo_filters">' . loadComboListFromArray($brand_options, null, $this->filters_brand_id, false) . '</select>';
//FILTER Category
$category_selected = self::getSession(self::FILTER_CAT_ID);
$categories = array('' => 'Show All Categories');
$category_groups = array();
while ($ct = CategoryTop::getOneByOneAsObject()) {
if ($ct->getCategoriesTotalCount() > 0) {
while ($cat = $ct->getCategoriesOneByOneAsObject()) {
if ($cat->getProductCodePrefix() != "") {
$category_groups[$ct->name][$cat->id] = $cat->getName(1);
}
}
}
}
$filter_category = '<select name="' . self::FILTER_CAT_ID . '" class="cbo_filters">' . loadComboListFromArray($categories, null, $category_selected, true, $category_groups) . '</select>';
/*
$filter_category =
comboProductsCategory(
self::FILTER_CAT_ID
, self::FILTER_CAT_ID
, $this->filters_cat_id
, 'cbo_filters'
, ''
, 'Show All Category');
*/
//FILTER Product ID/EAN
$filter_prod_search = '<input type="text" name="' . self::FILTER_LIST_EAN . '" class="txt_filters input" value="' . $this->filters_list_ean . '" />';
//FILTER Order No.
$filter_orders_no = '<input type="text" name="' . self::FILTER_LIST_ORDERS_NO . '" class="txt_filters" value="' . $this->filters_list_orders_no . '" />';
//FILTER Lead Time Start Date
$filter_leadtime = '<input name="' . self::FILTER_LEADTIME_START_DATE . '" class="date_picker" value="' . $this->filters_leadtime_date_start . '"/>' . ' to <input name="' . self::FILTER_LEADTIME_END_DATE . '" class="date_picker" value="' . $this->filters_leadtime_date_end . '"/>' . ' <input type="button" value="Go" class="btn_filters_leadtime_go" ' . 'title="Apply lead time start date filter" />' . ' <input type="button" value="Clear" class="date_range_clear" ' . 'title="Clear lead time start date filter" />';
$enter_info = '<span class="notice smallText" style="font-weight:normal;">' . '(press <em>Enter</em> to filter)</span>';
$result .= '<div style="margin-bottom:10px;">';
$result .= '<table border="0" cellpadding="0" cellspacing="0">';
$result .= '<tr>';
$result .= '<td class="bold">Order Type</td>';
//$result .= '<td class="bold">Sales Partner</td>';
$result .= '<td class="bold">Brand</td>';
$result .= '<td class="bold">Category</td>';
$result .= '<td class="bold">Product ID/EAN ' . $enter_info . '</td>';
$result .= '<td class="bold">Order No. ' . $enter_info . '</td>';
$result .= '<td class="bold">Lead Time Start Date</td>';
$result .= '</tr>';
$result .= '<tr>' . '<td>' . $filter_order_type . '</td>' . '<td>' . $filter_brand . '</td>' . '<td>' . $filter_category . '</td>' . '<td>' . $filter_prod_search . '</td>' . '<td>' . $filter_orders_no . '</td>' . '<td>' . $filter_leadtime . '</td>' . '</tr>';
$result .= '</table></div>';
return $result;
}
<?php
use_class('jng_sp');
$class_sp = new jng_sp();
$all_sp = $class_sp->retrieveList();
use_class('jng_sp_download');
$class_jd = new jng_sp_download();
use_class('jng_sp_orders');
$class_jo = new jng_sp_orders();
use_class('jng_sp_customers');
$class_jcust = new jng_sp_customers();
use_class('jng_sp_orders_xml');
$total_orders_created = 0;
$daily_counter = array();
foreach ($all_sp as $sp_detail) {
//initial setting
$files_imported = 0;
$orders_created = 0;
$products_created = 0;
$jng_sp_id = $sp_detail["jng_sp_id"];
$jng_sp_name = $sp_detail["name"];
$process_status = array('1' => 'D', '2' => 'R', '3' => 'R', '4' => 'R', '5' => 'R');
$xmlwithgpg = array('1');
if (array_key_exists($jng_sp_id, $process_status)) {
$sp_detail = $class_sp->retrieveDetail($jng_sp_id);
$jng_sp_id_with_no_external_xml_class = array('2', '4', '5');
if (!in_array($jng_sp_id, $jng_sp_id_with_no_external_xml_class)) {
use_class('jng_sp_orders_xml_' . $jng_sp_id);
}
switch ($jng_sp_id) {
case '1':
$class_jo = new jng_sp_orders();
$runinlocal = DB_SERVER == 'localhost';
$content = '';
$sp_detail = array();
$data = array();
if (isset($_GET['reconfirm']) && $_GET['reconfirm'] != '') {
$is_reconfirm = true;
$order_id = tep_db_prepare_input($_GET['reconfirm']);
$data_rc = $class_jo->getReconfirmDelivery($order_id);
$jng_sp_id = $data_rc['orders'][0]['jng_sp_id'];
$sp_detail[$jng_sp_id] = $class_sp->retrieveDetail($jng_sp_id);
$data[$jng_sp_id] = $data_rc;
$jng_sp_name_array = array($jng_sp_id => $sp_detail[$jng_sp_id]['name']);
} else {
$is_reconfirm = false;
$jng_sp_active = $class_sp->retrieveList("", "name");
$jng_sp_name_array = array();
foreach ($jng_sp_active as $temp_sp) {
$jng_sp_id = $temp_sp['jng_sp_id'];
$jng_sp_name_array[$jng_sp_id] = $temp_sp['name'];
$sp_detail[$jng_sp_id] = $temp_sp;
$data[$jng_sp_id] = $class_jo->getUnconfirmedDelivery($jng_sp_id);
}
}
foreach ($data as $jng_sp_id => $data_sp) {
$jng_sp_name = $jng_sp_name_array[$jng_sp_id];
$orders = $data_sp['orders'];
if (count($orders) == 0) {
$content .= '<h3 class="notice">' . $jng_sp_name . ': There is no new delivery to confirm</h3>';
} else {
switch ($jng_sp_id) {
//A_VK_OLD
$xmlt->drawTag($xml_apricedata_aprice, 'A_VK_OLD', $sd['price_old']);
}
}
}
//A_STOCK
$xmlt->drawTag($xml_article, 'A_STOCK', $pri['stock'], array('identifier' => 'key', 'key' => 'storage-zade'));
//A_DELIVERY_TIME (NEED TO DISCUSS HOW WE SET THIS,
//IS IT USED AS WE NOW USED FOR SP/JG OR DIFFERENT???)
//note from sahat, for now we dont need this, we will need to rediscuss
//when implementing another channel that support "virtual stock"
//$delivery_time = $pri['stock'] > 0 ? 1 : 10;
//$xmlt->drawTag($xml_article, 'A_DELIVERY_TIME', $delivery_time);
}
}
$sp_use_tb = $class_sp->retrieveList("tb_used='1' AND tb_channel!=''");
$sp_use_tb_ids = array_keys($sp_use_tb);
$sp_use_tb_ids_filter = implode(',', $sp_use_tb_ids);
//ONCE A WEEK WE WILL UPLOAD FULL CATALOG UPDATE TO TRADEBYTE.
//THIS IS A WORKAROUND TO SOLVE ISSUE WHERE PRICE IS NOT AUTO UPDATED
//AND ALSO IF AN UPLOAD ERROR EXIST WHICH PREVENTED TRADEBYTE TO RECEIVE
//THE LATEST STOCK CONDITION
//The current settings is every thursday at 3am and reupload again from 6am
$config_upload_day = 4;
$config_upload_hour = 3;
$config_upload_wait_until = 6;
$day = intval(date('N'));
$hour = intval(date('H'));
$mins = intval(date('i'));
if ($day == $config_upload_day) {
if ($hour == $config_upload_hour && $mins == 0) {
}
}
$brands = $class_pb->retrieveList('active_status=1');
$brandnames = array();
$brandnames[0] = '... Select a Brand';
foreach ($brands as $b) {
$brandnames[$b['products_brand_id']] = $b['brand_name'];
}
$products_id = isset($_GET['id']) && $_GET['id'] != '' ? tep_db_prepare_input($_GET['id']) : null;
if (is_null($products_id)) {
exit;
}
$draft = new design_draft(null, $products_id);
$product = $class_pm->retrieveDetail($products_id, 'p,pd,pnc,pei,pci,cat');
$spid_filter = implode(',', $default_SP);
$sp_active_default = $class_sp->retrieveList("jng_sp_id IN ({$spid_filter}) AND active_status=1");
$pcat_collection = array();
foreach ($sp_active_default as $jng_sp_id => $spdetail) {
$pcat = $class_jc->retrieveDetail(null, $jng_sp_id, $products_id);
if ($pcat === false) {
$catid = $class_jc->addProduct($jng_sp_id, $products_id);
$pcat = $class_jc->retrieveDetail($catid[0]);
}
$pcat_collection[$jng_sp_id] = $pcat;
}
$sops = array();
$sops[] = 'Set <strong>Name</strong> and <strong>Color</strong>|Check|checkname';
$sops[] = 'Set <strong>Sub Category</strong>|Set|setsubcat';
$sops[] = 'Set <strong>Brand</strong>|Set|setbrand';
$sops[] = 'Set <strong>Product Lengths/Size</strong>|Set|setlength';
$sops[] = 'Check <strong>Elements Used</strong> & <strong>Qty</strong>|Update|addelements';
$year = $year_period;
$month = str_pad($m, 2, '0', STR_PAD_LEFT);
$p = $year . $month;
$periods[] = $p;
$periods_plan[] = $p;
$periods_name[$p] = strtoupper(date('M y', strtotime($year . '-' . $month . '-01')));
}
//Prepare the based data used to save the scorecard values
$based_list = array();
$based_key = array();
if ($based == 'sp') {
//For values saved based on Sales Partner
$based_name = 'Sales Partner';
$table_name = 'scorecard_sp';
$table_key_column = 'jng_sp_id';
$sp_list = $class_sp->retrieveList("active_status='1'", "name");
//Manually set J&G as SP with ID 0 (zero)
$sp_juliegrace = array('jng_sp_id' => '0', 'name' => 'Julie & Grace', 'package_prefix' => 'JG.DE');
array_unshift($sp_list, $sp_juliegrace);
//Save based data values in prepared variables
$sp_counter = 0;
foreach ($sp_list as $sp) {
$sp_counter++;
$b = array();
$b['based_id'] = $sp['jng_sp_id'];
$b['name'] = $sp['name'];
$based_list[] = $b;
$based_key[$sp['jng_sp_id']] = $sp_counter;
}
$total_key = $sp_counter + 1;
} elseif ($based == 'stars') {
use_class('products_articles');
use_class('minierp_customers');
use_class('products_ean');
use_class('jng_sp_orders');
use_class('jng_sp_customers');
use_class('jng_sp');
use_class('orders');
$class_do = new depot_orders();
$class_pm = new products_minierp();
$class_pa = new products_articles();
$class_mc = new minierp_customers();
$class_jo = new jng_sp_orders();
$class_jc = new jng_sp_customers();
$class_sp = new jng_sp();
$class_o = new orders();
$sp_list_raw = $class_sp->retrieveList();
$sp_list = array();
foreach ($sp_list_raw as $sp) {
$sp_list[$sp['jng_sp_id']] = $sp;
}
function getTotalOrderAmount($type, $order_id, $currency = 'EUR')
{
$type = strtoupper($type);
if ($type == 'J') {
global $class_o;
$orders_total = $class_o->retrieveTotals($order_id);
$total_price = $orders_total['ot_total']['value'];
} elseif ($type == 'S') {
$q_total_price = "SELECT jo.shipping_costs+jo.cod_costs+SUM(joi.order_quantity * joi.price) total_price FROM jng_sp_orders jo";
$q_total_price .= " INNER JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_id = jo.jng_sp_orders_id";
$q_total_price .= " WHERE jo.jng_sp_orders_id = {$order_id}";
/**
* Construct KPI Leadtime Table
* @param string $status
* @param string $kpi_closing_date_start
* @param string $kpi_closing_date_end
* @return string
*/
function kpiLeadtimeLoad($kpi_status, $kpi_closing_date_start = '', $kpi_closing_date_end = '')
{
use_class('jng_sp');
use_class('design');
$class_sp = new jng_sp();
$sp_b2b = array_keys($class_sp->retrieveList('active_status = 1 AND use_amvd_logistic = 1'));
$target_leadtime = load_config('kpi-leadtime');
$design_categories = design::getDesignCategory();
$initial = array('sourcing' => 0, 'depot-refill-orders' => 0, 'internal-levelling-orders' => 0, 'cust-order-mto-mmo' => 0, 'external-levelling-order' => 0, 'full-dso' => 0, 'mto' => 0);
foreach ($design_categories as $des_cat_id => $des_cat_name) {
$initial["design-{$des_cat_id}"] = 0;
}
$below_target = $initial;
$on_target = $initial;
$above_target_1to3d = $initial;
$above_target_gt3d = $initial;
$total = $initial;
$kpi_leadtime_data_o = array();
//open
$kpi_leadtime_data_c = array();
//closed
//PREPARE KPI LEADTIME DATA
if ($kpi_status == 'o') {
//OPEN ORDERS
//Sourcing
$q = " SELECT sp_type, item_id, quantity, MIN(status_date) AS date_start FROM";
$q .= " (";
$q .= " SELECT 'sp' AS sp_type, joi.jng_sp_orders_items_id AS item_id" . ", joish.status_date, joi.order_quantity AS quantity";
$q .= " FROM jng_sp_orders_items joi" . " INNER JOIN jng_sp_orders_items_status_history joish ON joish.jng_sp_orders_items_id = joi.jng_sp_orders_items_id" . " AND joish.status = 2";
$q .= " WHERE joi.status = 2";
$q .= " UNION";
$q .= " SELECT 'jg' AS sp_type, op.orders_products_id AS item_id" . ", opsh.status_date, op.products_quantity AS quantity";
$q .= " FROM orders_products op" . " INNER JOIN orders_products_status_history opsh ON opsh.orders_products_id = op.orders_products_id" . " AND opsh.status = 2";
$q .= " WHERE op.status = 2";
$q .= " UNION";
$q .= " SELECT 'dp' AS sp_type, do.depot_orders_id AS item_id" . ", dosh.update_time AS status_date, do.quantity";
$q .= " FROM depot_orders do" . " INNER JOIN depot_orders_status_history dosh ON dosh.depot_orders_id = do.depot_orders_id" . " AND dosh.status = 2";
$q .= " WHERE do.status = 2";
$q .= ") dt";
$q .= " GROUP BY sp_type, item_id";
$dbq = tep_db_query($q);
while ($r = tep_db_fetch_array($dbq)) {
//Sourcing Order Type
$kpi_leadtime_data_o['sourcing'][] = $r;
}
//Production
$q = " SELECT sp_type, item_id, quantity, MIN(status_date) AS date_start, mode, order_type, order_type_id, order_type_name";
$q .= " FROM (";
$q .= " SELECT 'sp' AS sp_type, joi.jng_sp_orders_items_id AS item_id" . ", joish.status_date, joi.order_quantity AS quantity, joi.mode" . ", '' AS order_type, '' AS order_type_id, '' AS order_type_name";
$q .= " FROM jng_sp_orders_items joi" . " INNER JOIN jng_sp_orders_items_status_history joish ON joish.jng_sp_orders_items_id = joi.jng_sp_orders_items_id" . " AND joish.status = 4";
$q .= " WHERE joi.status = 4";
$q .= " UNION";
$q .= " SELECT 'jg' AS sp_type, op.orders_products_id AS item_id" . ", opsh.status_date, op.products_quantity AS quantity, op.mode" . ", '' AS order_type, '' AS order_type_id, '' AS order_type_name";
$q .= " FROM orders_products op" . " INNER JOIN orders_products_status_history opsh ON opsh.orders_products_id = op.orders_products_id" . " AND opsh.status = 4";
$q .= " WHERE op.status = 4";
$q .= " UNION";
$q .= " SELECT 'dp' AS sp_type, do.depot_orders_id AS item_id" . ", dosh.update_time AS status_date, do.quantity, '' AS mode" . ", trans_type AS order_type, trans_id AS order_type_id, group_name AS order_type_name";
$q .= " FROM depot_orders do" . " INNER JOIN depot_orders_status_history dosh ON dosh.depot_orders_id = do.depot_orders_id" . " AND dosh.status = 4";
$q .= " WHERE do.status = 4";
$q .= ") dt";
$q .= " GROUP BY sp_type, item_id";
$dbq = tep_db_query($q);
while ($r = tep_db_fetch_array($dbq)) {
if ($r['order_type'] == 'AR' || $r['order_type'] == '' && $r['order_type_id'] == depot_orders::MANUAL_REFILL_ID) {
//Depot Refill Order Type
$kpi_leadtime_data_o['depot-refill-orders'][] = $r;
} elseif ($r['order_type_name'] == depot_orders::GROUP_NAME_LTF_INTERNAL) {
//Internal Levelling Order Type
$kpi_leadtime_data_o['internal-levelling-orders'][] = $r;
} elseif ($r['mode'] == 2 || $r['mode'] == 4) {
//Customer Orders (MTO/MMO) Order Type
$kpi_leadtime_data_o['cust-order-mto-mmo'][] = $r;
}
}
//Outsourcing
$q = " SELECT do.depot_orders_id AS item_id, MIN(dosh.update_time) AS status_date, do.quantity";
$q .= " FROM depot_orders do" . " INNER JOIN depot_orders_status_history dosh ON dosh.depot_orders_id = do.depot_orders_id" . " AND dosh.status = 22";
$q .= " WHERE do.status = 22";
$q .= " AND do.trans_type = '' && do.trans_id = " . depot_orders::MANUAL_REFILL_OUTSOURCED;
$q .= " GROUP BY item_id";
$dbq = tep_db_query($q);
while ($r = tep_db_fetch_array($dbq)) {
//External Levelling Order Type
$kpi_leadtime_data_o['external-levelling-order'][] = $r;
}
//Customer Order
$q = " SELECT sp_type, item_id, quantity, MIN(status_date) AS date_start, mode";
$q .= " FROM (";
$q .= " SELECT 'sp' AS sp_type, joi.jng_sp_orders_items_id AS item_id" . ", joish.status_date, joi.order_quantity AS quantity, joi.mode";
$q .= " FROM jng_sp_orders jo " . " INNER JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_id = jo.jng_sp_orders_id" . " INNER JOIN jng_sp_orders_items_status_history joish ON joish.jng_sp_orders_items_id = joi.jng_sp_orders_items_id" . " AND joish.status = 1";
$q .= " WHERE joi.status < 9 AND jo.jng_sp_id NOT IN (" . implode(',', $sp_b2b) . ")";
$q .= " UNION";
$q .= " SELECT 'jg' AS sp_type, op.orders_products_id AS item_id" . ", opsh.status_date, op.products_quantity AS quantity, op.mode";
$q .= " FROM orders_products op" . " INNER JOIN orders_products_status_history opsh ON opsh.orders_products_id = op.orders_products_id" . " AND opsh.status = 1";
$q .= " WHERE op.status < 9";
$q .= ") dt";
$q .= " GROUP BY sp_type, item_id";
$dbq = tep_db_query($q);
while ($r = tep_db_fetch_array($dbq)) {
if ($r['mode'] == 1) {
//Full DSO Order Type
$kpi_leadtime_data_o['full-dso'][] = $r;
} elseif ($r['mode'] == 2 || $r['mode'] == 3 || $r['mode'] == 4) {
$kpi_leadtime_data_o['mto'][] = $r;
}
}
//Design
$q = " SELECT d.designs_category, 1 AS quantity, d.designs_id AS item_id" . ", FROM_UNIXTIME(MIN(dsh.status_time)) AS date_start";
$q .= " FROM designs d " . " INNER JOIN designs_status_history dsh ON dsh.designs_id = d.designs_id AND dsh.status = 2";
$q .= " WHERE d.status NOT IN (1, 9)";
$q .= " GROUP BY d.designs_id";
$dbq = tep_db_query($q);
while ($r = tep_db_fetch_array($dbq)) {
$kpi_leadtime_data_o["design-{$r['designs_category']}"][] = $r;
}
} elseif ($kpi_status == 'c') {
$kpi_filter_closing_date_start = date('Y-m-d', strtotime($kpi_closing_date_start));
$kpi_filter_closing_date_end = date('Y-m-d', strtotime($kpi_closing_date_end));
//Sourcing
$q = " SELECT * FROM";
$q .= " (";
$q .= " SELECT 'sp' AS sp_type, joi.jng_sp_orders_items_id AS item_id, joi.order_quantity AS quantity" . ", MIN(joish_2.status_date) AS date_start, MAX(joish_3.status_date) AS date_end";
$q .= " FROM jng_sp_orders_items_status_history joish_3" . " LEFT JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_items_id = joish_3.jng_sp_orders_items_id" . " LEFT JOIN jng_sp_orders_items_status_history joish_2 ON joish_2.jng_sp_orders_items_id = joi.jng_sp_orders_items_id" . " AND joish_2.status = 2";
$q .= " WHERE joish_3.status_date >= '{$kpi_filter_closing_date_start} 00:00:00'" . " AND joish_3.status_date <= '{$kpi_filter_closing_date_end} 23:59:59'" . " AND joish_3.status = 3 AND joi.status NOT IN (1, 2)";
$q .= " GROUP BY sp_type, item_id";
$q .= " UNION";
$q .= " SELECT 'jg' AS sp_type, op.orders_products_id AS item_id, op.products_quantity AS quantity" . ", MIN(opsh_2.status_date) AS date_start, MAX(opsh_3.status_date) AS date_end";
$q .= " FROM orders_products_status_history opsh_3" . " LEFT JOIN orders_products op ON op.orders_products_id = opsh_3.orders_products_id" . " LEFT JOIN orders_products_status_history opsh_2 ON opsh_2.orders_products_id = op.orders_products_id" . " AND opsh_2.status = 2";
$q .= " WHERE opsh_3.status_date >= '{$kpi_filter_closing_date_start} 00:00:00'" . " AND opsh_3.status_date <= '{$kpi_filter_closing_date_end} 23:59:59'" . " AND opsh_3.status = 3 AND op.status NOT IN (1, 2)";
$q .= " GROUP BY sp_type, item_id";
$q .= " UNION";
$q .= " SELECT 'dp' AS sp_type, do.depot_orders_id AS item_id, do.quantity" . ", MIN(dosh_2.update_time) AS date_start, MAX(dosh_3.update_time) AS date_end";
$q .= " FROM depot_orders_status_history dosh_3" . " LEFT JOIN depot_orders do ON do.depot_orders_id = dosh_3.depot_orders_id" . " LEFT JOIN depot_orders_status_history dosh_2 ON dosh_2.depot_orders_id = do.depot_orders_id" . " AND dosh_2.status = 2";
$q .= " WHERE dosh_3.update_time >= '{$kpi_filter_closing_date_start} 00:00:00'" . " AND dosh_3.update_time <= '{$kpi_filter_closing_date_end} 23:59:59'" . " AND dosh_3.status = 3 AND do.status NOT IN (1, 2)";
$q .= " GROUP BY sp_type, item_id";
$q .= ") dt";
$q .= " WHERE date_start IS NOT NULL AND date_end IS NOT NULL";
$dbq = tep_db_query($q);
while ($r = tep_db_fetch_array($dbq)) {
//Sourcing Order Type
$kpi_leadtime_data_c['sourcing'][] = $r;
}
//Production
$q = " SELECT * FROM";
$q .= " (";
$q .= " SELECT 'sp' AS sp_type, joi.jng_sp_orders_items_id AS item_id, joi.order_quantity AS quantity" . ", MIN(joish_4.status_date) AS date_start, MAX(joish_5.status_date) AS date_end, joi.mode" . ", '' AS order_type, '' AS order_type_id, '' AS order_type_name";
$q .= " FROM jng_sp_orders_items_status_history joish_5" . " LEFT JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_items_id = joish_5.jng_sp_orders_items_id" . " LEFT JOIN jng_sp_orders_items_status_history joish_4 ON joish_4.jng_sp_orders_items_id = joi.jng_sp_orders_items_id" . " AND joish_4.status = 4";
$q .= " WHERE joish_5.status_date >= '{$kpi_filter_closing_date_start} 00:00:00'" . " AND joish_5.status_date <= '{$kpi_filter_closing_date_end} 23:59:59'" . " AND joish_5.status = 5 AND joi.status NOT IN (1, 2, 3, 4)";
$q .= " GROUP BY sp_type, item_id";
$q .= " UNION";
$q .= " SELECT 'jg' AS sp_type, op.orders_products_id AS item_id, op.products_quantity AS quantity" . ", MIN(opsh_4.status_date) AS date_start, MAX(opsh_5.status_date) AS date_end, op.mode" . ", '' AS order_type, '' AS order_type_id, '' AS order_type_name";
$q .= " FROM orders_products_status_history opsh_5" . " LEFT JOIN orders_products op ON op.orders_products_id = opsh_5.orders_products_id" . " LEFT JOIN orders_products_status_history opsh_4 ON opsh_4.orders_products_id = op.orders_products_id" . " AND opsh_4.status = 4";
$q .= " WHERE opsh_5.status_date >= '{$kpi_filter_closing_date_start} 00:00:00'" . " AND opsh_5.status_date <= '{$kpi_filter_closing_date_end} 23:59:59'" . " AND opsh_5.status = 5 AND op.status NOT IN (1, 2, 3, 4)";
$q .= " GROUP BY sp_type, item_id";
$q .= " UNION";
$q .= " SELECT 'dp' AS sp_type, do.depot_orders_id AS item_id, do.quantity" . ", MIN(dosh_4.update_time) AS date_start, MAX(dosh_5.update_time) AS date_end, '' AS mode" . ", trans_type AS order_type, trans_id AS order_type_id, group_name AS order_type_name";
$q .= " FROM depot_orders_status_history dosh_5" . " LEFT JOIN depot_orders do ON do.depot_orders_id = dosh_5.depot_orders_id" . " LEFT JOIN depot_orders_status_history dosh_4 ON dosh_4.depot_orders_id = do.depot_orders_id" . " AND dosh_4.status = 4";
$q .= " WHERE dosh_5.update_time >= '{$kpi_filter_closing_date_start} 00:00:00'" . " AND dosh_5.update_time <= '{$kpi_filter_closing_date_end} 23:59:59'" . " AND dosh_5.status = 5 AND do.status NOT IN (1, 2, 3, 4)";
$q .= " GROUP BY sp_type, item_id";
$q .= ") dt";
$q .= " WHERE date_start IS NOT NULL AND date_end IS NOT NULL";
$dbq = tep_db_query($q);
while ($r = tep_db_fetch_array($dbq)) {
if ($r['order_type'] == 'AR' || $r['order_type'] == '' && $r['order_type_id'] == depot_orders::MANUAL_REFILL_ID) {
//Depot Refill Order Type
$kpi_leadtime_data_c['depot-refill-orders'][] = $r;
} elseif ($r['order_type_name'] == depot_orders::GROUP_NAME_LTF_INTERNAL) {
//Internal Levelling Order Type
$kpi_leadtime_data_c['internal-levelling-orders'][] = $r;
} elseif ($r['mode'] == 2 || $r['mode'] == 4) {
//Customer Orders (MTO/MMO) Order Type
$kpi_leadtime_data_c['cust-order-mto-mmo'][] = $r;
}
}
//Outsourcing
$q = " SELECT * FROM";
$q .= " (";
$q .= " SELECT do.depot_orders_id AS item_id, do.quantity" . ", MIN(dosh_8.update_time) AS date_start, MAX(dosh_22.update_time) AS date_end";
$q .= " FROM depot_orders_status_history dosh_22" . " LEFT JOIN depot_orders do ON do.depot_orders_id = dosh_22.depot_orders_id" . " LEFT JOIN depot_orders_status_history dosh_8 ON dosh_8.depot_orders_id = do.depot_orders_id" . " AND dosh_8.status = 8";
$q .= " WHERE dosh_22.update_time >= '{$kpi_filter_closing_date_start} 00:00:00'" . " AND dosh_22.update_time <= '{$kpi_filter_closing_date_end} 23:59:59'" . " AND do.trans_type = '' AND do.trans_id = " . depot_orders::MANUAL_REFILL_OUTSOURCED . " AND dosh_22.status = 22 AND do.status NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9)";
$q .= " GROUP BY item_id";
$q .= ") dt";
$q .= " WHERE date_start IS NOT NULL AND date_end IS NOT NULL";
$dbq = tep_db_query($q);
while ($r = tep_db_fetch_array($dbq)) {
//External Levelling Order Type
$kpi_leadtime_data_c['external-levelling-order'][] = $r;
}
//Customer Order (only B2C Sales Partner)
$q = " SELECT * FROM";
$q .= " (";
$q .= " SELECT 'sp' AS sp_type, joi.jng_sp_orders_items_id AS item_id, joi.order_quantity AS quantity" . ", MIN(joish_1.status_date) AS date_start, MAX(joish_9.status_date) AS date_end, joi.mode";
$q .= " FROM jng_sp_orders_items_status_history joish_9" . " LEFT JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_items_id = joish_9.jng_sp_orders_items_id" . " INNER JOIN jng_sp_orders jo ON jo.jng_sp_orders_id = joi.jng_sp_orders_id" . " LEFT JOIN jng_sp_orders_items_status_history joish_1 ON joish_1.jng_sp_orders_items_id = joi.jng_sp_orders_items_id" . " AND joish_1.status = 1";
$q .= " WHERE joish_9.status_date >= '{$kpi_filter_closing_date_start} 00:00:00'" . " AND joish_9.status_date <= '{$kpi_filter_closing_date_end} 23:59:59'" . " AND joish_9.status = 9 AND joi.status NOT IN (1, 2, 3, 4, 5, 6, 7, 8)" . " AND jo.jng_sp_id NOT IN (" . implode(',', $sp_b2b) . ")";
$q .= " GROUP BY sp_type, item_id";
$q .= " UNION";
$q .= " SELECT 'jg' AS sp_type, op.orders_products_id AS item_id, op.products_quantity AS quantity" . ", MIN(opsh_1.status_date) AS date_start, MAX(opsh_9.status_date) AS date_end, op.mode";
$q .= " FROM orders_products_status_history opsh_9" . " LEFT JOIN orders_products op ON op.orders_products_id = opsh_9.orders_products_id" . " LEFT JOIN orders_products_status_history opsh_1 ON opsh_1.orders_products_id = op.orders_products_id" . " AND opsh_1.status = 1";
$q .= " WHERE opsh_9.status_date >= '{$kpi_filter_closing_date_start} 00:00:00'" . " AND opsh_9.status_date <= '{$kpi_filter_closing_date_end} 23:59:59'" . " AND opsh_9.status = 9 AND op.status NOT IN (1, 2, 3, 4, 5, 6, 7, 8)";
$q .= " GROUP BY sp_type, item_id";
$q .= " UNION";
$q .= " SELECT 'dp' AS sp_type, do.depot_orders_id AS item_id, do.quantity" . ", MIN(dosh_1.update_time) AS date_start, MAX(dosh_9.update_time) AS date_end, '' AS mode";
$q .= " FROM depot_orders_status_history dosh_9" . " LEFT JOIN depot_orders do ON do.depot_orders_id = dosh_9.depot_orders_id" . " LEFT JOIN depot_orders_status_history dosh_1 ON dosh_1.depot_orders_id = do.depot_orders_id" . " AND dosh_1.status = 1";
$q .= " WHERE dosh_9.update_time >= '{$kpi_filter_closing_date_start} 00:00:00'" . " AND dosh_9.update_time <= '{$kpi_filter_closing_date_end} 23:59:59'" . " AND dosh_9.status = 9 AND do.status NOT IN (1, 2, 3, 4, 5, 6, 7, 8)";
$q .= " GROUP BY sp_type, item_id";
$q .= ") dt";
$q .= " WHERE date_start IS NOT NULL AND date_end IS NOT NULL";
$dbq = tep_db_query($q);
while ($r = tep_db_fetch_array($dbq)) {
if ($r['mode'] == 1) {
//Full DSO Order Type
$kpi_leadtime_data_c['full-dso'][] = $r;
} elseif ($r['mode'] == 2 || $r['mode'] == 3 || $r['mode'] == 4) {
$kpi_leadtime_data_c['mto'][] = $r;
}
}
//Design
$q = " SELECT d.designs_category, 1 AS quantity, d.designs_id AS item_id" . ", FROM_UNIXTIME(MIN(dsh_2.status_time)) AS date_start" . ", FROM_UNIXTIME(MAX(dsh_9.status_time)) AS date_end";
$q .= " FROM designs_status_history dsh_9 " . " LEFT JOIN designs d ON d.designs_id = dsh_9.designs_id" . " LEFT JOIN designs_status_history dsh_2 ON dsh_2.designs_id = d.designs_id AND dsh_2.status = 2";
$q .= " WHERE dsh_9.status_time >= " . strtotime($kpi_filter_closing_date_start . " 00:00:00") . " AND dsh_9.status_time <= " . strtotime($kpi_filter_closing_date_end . " 23:59:59") . " AND dsh_9.status = 9 AND d.status = 9";
$q .= " GROUP BY d.designs_id";
$dbq = tep_db_query($q);
while ($r = tep_db_fetch_array($dbq)) {
$kpi_leadtime_data_c["design-{$r['designs_category']}"][] = $r;
}
}
$today = date('d-m-Y');
if ($kpi_status == 'c') {
$kpi_leadtime_data = $kpi_leadtime_data_c;
} else {
$kpi_leadtime_data = $kpi_leadtime_data_o;
}
$leadtime_arr = array();
foreach ($kpi_leadtime_data as $order_type => $klds) {
//klds: kpi leadtime datas
foreach ($klds as $kld) {
$lsd = $kld['date_start'];
$led = $kpi_status == 'o' ? $today : $kld['date_end'];
$qty = $kld['quantity'];
$datediff_sec = strtotime($led) - strtotime($lsd);
$leadtime = ceil($datediff_sec / 3600 / 24);
//in days format (round up)
$leadtime_arr[$order_type][] = $leadtime;
$target = $target_leadtime[$order_type];
$total[$order_type] += $qty;
if ($leadtime < $target) {
$below_target[$order_type] += $qty;
} elseif ($leadtime == $target) {
$on_target[$order_type] += $qty;
} elseif ($leadtime > $target + 3) {
$above_target_gt3d[$order_type] += $qty;
} else {
$above_target_1to3d[$order_type] += $qty;
}
}
$leadtime_avg[$order_type] = count($leadtime_arr[$order_type]) > 0 ? number_format(array_sum($leadtime_arr[$order_type]) / count($leadtime_arr[$order_type]), 2) : '-';
$leadtime_median[$order_type] = median($leadtime_arr[$order_type], 2);
}
//DRAW KPI LEADTIME TABLE
$tooltip_info_saved = ' title="press enter to save changes" ';
$border_dotted_bottom = 'border-bottom:1px dotted #ccc; ';
$border_dotted_right = 'border-right:1px dotted #ccc; ';
$kpi_lead = '<h3>' . ($kpi_status == 'o' ? 'Lead time of currently open orders/process (' . date('d.m.Y') . ')' : 'Lead time of orders with closing date from ' . date('d.m.Y', strtotime($kpi_closing_date_start)) . ' to ' . date('d.m.Y', strtotime($kpi_closing_date_end))) . '</h3>';
$kpi_lead .= '<table class="spo" cellspacing="0" cellpadding="0">';
$kpi_lead .= '<thead>';
$kpi_lead .= '<tr class="o">';
$kpi_lead .= '<th class="i" colspan="2" style="' . $border_dotted_bottom . $border_dotted_right . '">Running Lead Time / Lead Time</th>';
$kpi_lead .= '<th class="r" rowspan="2" style="' . $border_dotted_right . '">Target Lead Time<br/>in Days</th>';
$kpi_lead .= '<th class="d" rowspan="2" style="' . $border_dotted_right . '">Below Target</th>';
$kpi_lead .= '<th class="d" rowspan="2" style="' . $border_dotted_right . '">On Target</th>';
$kpi_lead .= '<th class="r" rowspan="2" style="' . $border_dotted_right . '">Above Target<br/>1 to 3 days</th>';
$kpi_lead .= '<th class="r" rowspan="2" style="' . $border_dotted_right . '">Above Target<br/>> 3 days</th>';
$kpi_lead .= '<th class="r" rowspan="2" style="' . $border_dotted_right . '">Total<br/>(based on Qty)</th>';
$kpi_lead .= '<th class="r" rowspan="2" style="' . $border_dotted_right . '">Lead Time<br/>Average in Days</th>';
$kpi_lead .= '<th class="r" rowspan="2">Lead Time<br/>Median in Days</th>';
$kpi_lead .= '</tr>';
$kpi_lead .= '<tr>';
$kpi_lead .= '<th style="' . $border_dotted_right . '">Category</th>';
$kpi_lead .= '<th style="' . $border_dotted_right . '">Order Type</th>';
$kpi_lead .= '</tr>';
$kpi_lead .= '</thead>';
$kpi_lead .= '<tbody>';
//Sourcing
$kpi_lead .= '<tr class="e">';
$kpi_lead .= '<td><strong>Sourcing</strong></td>';
$kpi_lead .= '<td>Sourcing</td>';
$kpi_lead .= '<td class="tac"><input type="text" class="target_leadtime w040 tac" name="lt-sourcing" value="' . $target_leadtime['sourcing'] . '"' . $tooltip_info_saved . '/></td>';
$kpi_lead .= '<td class="tac">' . $below_target['sourcing'] . '<br/>' . kpiFormatPercentage($total['sourcing'], $below_target['sourcing']) . '</td>';
$kpi_lead .= '<td class="tac">' . $on_target['sourcing'] . '<br/>' . kpiFormatPercentage($total['sourcing'], $on_target['sourcing']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_1to3d['sourcing'] . '<br/>' . kpiFormatPercentage($total['sourcing'], $above_target_1to3d['sourcing']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_gt3d['sourcing'] . '<br/>' . kpiFormatPercentage($total['sourcing'], $above_target_gt3d['sourcing']) . '</td>';
$kpi_lead .= '<td class="tac">' . $total['sourcing'] . '<br/>' . kpiFormatPercentage($total['sourcing'], $total['sourcing']) . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_avg['sourcing']) ? $leadtime_avg['sourcing'] : 'N/A') . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_median['sourcing']) ? $leadtime_median['sourcing'] : 'N/A') . '</td>';
$kpi_lead .= '</tr>';
//Production
$kpi_lead .= '<tr class="o">';
$kpi_lead .= '<td rowspan="3"><strong>Production</strong></td>';
$kpi_lead .= '<td>Depot Refill Orders</td>';
$kpi_lead .= '<td class="tac"><input type="text" class="target_leadtime w040 tac" name="lt-depot-refill-orders" value="' . $target_leadtime['depot-refill-orders'] . '"' . $tooltip_info_saved . '/></td>';
$kpi_lead .= '<td class="tac">' . $below_target['depot-refill-orders'] . '<br/>' . kpiFormatPercentage($total['depot-refill-orders'], $below_target['depot-refill-orders']) . '</td>';
$kpi_lead .= '<td class="tac">' . $on_target['depot-refill-orders'] . '<br/>' . kpiFormatPercentage($total['depot-refill-orders'], $on_target['depot-refill-orders']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_1to3d['depot-refill-orders'] . '<br/>' . kpiFormatPercentage($total['depot-refill-orders'], $above_target_1to3d['depot-refill-orders']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_gt3d['depot-refill-orders'] . '<br/>' . kpiFormatPercentage($total['depot-refill-orders'], $above_target_gt3d['depot-refill-orders']) . '</td>';
$kpi_lead .= '<td class="tac">' . $total['depot-refill-orders'] . '<br/>' . kpiFormatPercentage($total['depot-refill-orders'], $total['depot-refill-orders']) . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_avg['depot-refill-orders']) ? $leadtime_avg['depot-refill-orders'] : 'N/A') . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_median['depot-refill-orders']) ? $leadtime_median['depot-refill-orders'] : 'N/A') . '</td>';
$kpi_lead .= '</tr>';
$kpi_lead .= '<tr class="o">';
$kpi_lead .= '<td>Internal Levelling Orders</td>';
$kpi_lead .= '<td class="tac"><input type="text" class="target_leadtime w040 tac" name="lt-internal-levelling-orders" value="' . $target_leadtime['internal-levelling-orders'] . '"' . $tooltip_info_saved . '/></td>';
$kpi_lead .= '<td class="tac">' . $below_target['internal-levelling-orders'] . '<br/>' . kpiFormatPercentage($total['internal-levelling-orders'], $below_target['internal-levelling-orders']) . '</td>';
$kpi_lead .= '<td class="tac">' . $on_target['internal-levelling-orders'] . '<br/>' . kpiFormatPercentage($total['internal-levelling-orders'], $on_target['internal-levelling-orders']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_1to3d['internal-levelling-orders'] . '<br/>' . kpiFormatPercentage($total['internal-levelling-orders'], $above_target_1to3d['internal-levelling-orders']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_gt3d['internal-levelling-orders'] . '<br/>' . kpiFormatPercentage($total['internal-levelling-orders'], $above_target_gt3d['internal-levelling-orders']) . '</td>';
$kpi_lead .= '<td class="tac">' . $total['internal-levelling-orders'] . '<br/>' . kpiFormatPercentage($total['internal-levelling-orders'], $total['internal-levelling-orders']) . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_avg['internal-levelling-orders']) ? $leadtime_avg['internal-levelling-orders'] : 'N/A') . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_median['internal-levelling-orders']) ? $leadtime_median['internal-levelling-orders'] : 'N/A') . '</td>';
$kpi_lead .= '</tr>';
$kpi_lead .= '<tr class="o">';
$kpi_lead .= '<td>Customer Orders (MTO/MMO)</td>';
$kpi_lead .= '<td class="tac"><input type="text" class="target_leadtime w040 tac" name="lt-cust-order-mto-mmo" value="' . $target_leadtime['cust-order-mto-mmo'] . '"' . $tooltip_info_saved . '/></td>';
$kpi_lead .= '<td class="tac">' . $below_target['cust-order-mto-mmo'] . '<br/>' . kpiFormatPercentage($total['cust-order-mto-mmo'], $below_target['cust-order-mto-mmo']) . '</td>';
$kpi_lead .= '<td class="tac">' . $on_target['cust-order-mto-mmo'] . '<br/>' . kpiFormatPercentage($total['cust-order-mto-mmo'], $on_target['cust-order-mto-mmo']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_1to3d['cust-order-mto-mmo'] . '<br/>' . kpiFormatPercentage($total['cust-order-mto-mmo'], $above_target_1to3d['cust-order-mto-mmo']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_gt3d['cust-order-mto-mmo'] . '<br/>' . kpiFormatPercentage($total['cust-order-mto-mmo'], $above_target_gt3d['cust-order-mto-mmo']) . '</td>';
$kpi_lead .= '<td class="tac">' . $total['cust-order-mto-mmo'] . '<br/>' . kpiFormatPercentage($total['cust-order-mto-mmo'], $total['cust-order-mto-mmo']) . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_avg['cust-order-mto-mmo']) ? $leadtime_avg['cust-order-mto-mmo'] : 'N/A') . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_median['cust-order-mto-mmo']) ? $leadtime_median['cust-order-mto-mmo'] : 'N/A') . '</td>';
$kpi_lead .= '</tr>';
//Outsourcing
$kpi_lead .= '<tr class="e">';
$kpi_lead .= '<td><strong>Outsourcing</strong></td>';
$kpi_lead .= '<td>External Levelling Orders</td>';
$kpi_lead .= '<td class="tac"><input type="text" class="target_leadtime w040 tac" name="lt-external-levelling-order" value="' . $target_leadtime['external-levelling-order'] . '"' . $tooltip_info_saved . '/></td>';
$kpi_lead .= '<td class="tac">' . $below_target['external-levelling-order'] . '<br/>' . kpiFormatPercentage($total['external-levelling-order'], $below_target['external-levelling-order']) . '</td>';
$kpi_lead .= '<td class="tac">' . $on_target['external-levelling-order'] . '<br/>' . kpiFormatPercentage($total['external-levelling-order'], $on_target['external-levelling-order']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_1to3d['external-levelling-order'] . '<br/>' . kpiFormatPercentage($total['external-levelling-order'], $above_target_1to3d['external-levelling-order']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_gt3d['external-levelling-order'] . '<br/>' . kpiFormatPercentage($total['external-levelling-order'], $above_target_gt3d['external-levelling-order']) . '</td>';
$kpi_lead .= '<td class="tac">' . $total['external-levelling-order'] . '<br/>' . kpiFormatPercentage($total['external-levelling-order'], $total['external-levelling-order']) . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_avg['external-levelling-order']) ? $leadtime_avg['external-levelling-order'] : 'N/A') . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_median['external-levelling-order']) ? $leadtime_median['external-levelling-order'] : 'N/A') . '</td>';
$kpi_lead .= '</tr>';
//Customer Order
$kpi_lead .= '<tr class="o">';
$kpi_lead .= '<td rowspan="2"><strong>Customer Order</strong></td>';
$kpi_lead .= '<td>Full DSO</td>';
$kpi_lead .= '<td class="tac"><input type="text" class="target_leadtime w040 tac" name="lt-full-dso" value="' . $target_leadtime['full-dso'] . '"' . $tooltip_info_saved . '/></td>';
$kpi_lead .= '<td class="tac">' . $below_target['full-dso'] . '<br/>' . kpiFormatPercentage($total['full-dso'], $below_target['full-dso']) . '</td>';
$kpi_lead .= '<td class="tac">' . $on_target['full-dso'] . '<br/>' . kpiFormatPercentage($total['full-dso'], $on_target['full-dso']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_1to3d['full-dso'] . '<br/>' . kpiFormatPercentage($total['full-dso'], $above_target_1to3d['full-dso']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_gt3d['full-dso'] . '<br/>' . kpiFormatPercentage($total['full-dso'], $above_target_gt3d['full-dso']) . '</td>';
$kpi_lead .= '<td class="tac">' . $total['full-dso'] . '<br/>' . kpiFormatPercentage($total['full-dso'], $total['full-dso']) . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_avg['full-dso']) ? $leadtime_avg['full-dso'] : 'N/A') . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_median['full-dso']) ? $leadtime_median['full-dso'] : 'N/A') . '</td>';
$kpi_lead .= '</tr>';
$kpi_lead .= '<tr class="o">';
$kpi_lead .= '<td>MTO (Full MTO and Mixed Orders)</td>';
$kpi_lead .= '<td class="tac"><input type="text" class="target_leadtime w040 tac" name="lt-mto" value="' . $target_leadtime['mto'] . '"' . $tooltip_info_saved . '/></td>';
$kpi_lead .= '<td class="tac">' . $below_target['mto'] . '<br/>' . kpiFormatPercentage($total['mto'], $below_target['mto']) . '</td>';
$kpi_lead .= '<td class="tac">' . $on_target['mto'] . '<br/>' . kpiFormatPercentage($total['mto'], $on_target['mto']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_1to3d['mto'] . '<br/>' . kpiFormatPercentage($total['mto'], $above_target_1to3d['mto']) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_gt3d['mto'] . '<br/>' . kpiFormatPercentage($total['mto'], $above_target_gt3d['mto']) . '</td>';
$kpi_lead .= '<td class="tac">' . $total['mto'] . '<br/>' . kpiFormatPercentage($total['mto'], $total['mto']) . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_avg['mto']) ? $leadtime_avg['mto'] : 'N/A') . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_median['mto']) ? $leadtime_median['mto'] : 'N/A') . '</td>';
$kpi_lead .= '</tr>';
//Design
$kpi_lead .= '<tr class="e">';
$kpi_lead .= '<td rowspan="' . count($design_categories) . '"><strong>Design</strong></td>';
$des_cat_id_counter = 0;
foreach ($design_categories as $des_cat_id => $des_cat_name) {
$des_cat_id_counter++;
if ($des_cat_id_counter > 1) {
$kpi_lead .= '<tr class="e">';
}
$design_order_type = "design-{$des_cat_id}";
$kpi_lead .= "<td>{$des_cat_name}</td>";
$kpi_lead .= '<td class="tac"><input type="text" class="target_leadtime w040 tac" name="lt-design-' . $des_cat_id . '" value="' . $target_leadtime[$design_order_type] . '"' . $tooltip_info_saved . '/></td>';
$kpi_lead .= '<td class="tac">' . $below_target[$design_order_type] . '<br/>' . kpiFormatPercentage($total[$design_order_type], $below_target[$design_order_type]) . '</td>';
$kpi_lead .= '<td class="tac">' . $on_target[$design_order_type] . '<br/>' . kpiFormatPercentage($total[$design_order_type], $on_target[$design_order_type]) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_1to3d[$design_order_type] . '<br/>' . kpiFormatPercentage($total[$design_order_type], $above_target_1to3d[$design_order_type]) . '</td>';
$kpi_lead .= '<td class="tac">' . $above_target_gt3d[$design_order_type] . '<br/>' . kpiFormatPercentage($total[$design_order_type], $above_target_gt3d[$design_order_type]) . '</td>';
$kpi_lead .= '<td class="tac">' . $total[$design_order_type] . '<br/>' . kpiFormatPercentage($total[$design_order_type], $total[$design_order_type]) . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_avg[$design_order_type]) ? $leadtime_avg[$design_order_type] : 'N/A') . '</td>';
$kpi_lead .= '<td class="tac">' . (isset($leadtime_median[$design_order_type]) ? $leadtime_median[$design_order_type] : 'N/A') . '</td>';
$kpi_lead .= '</tr>';
}
$kpi_lead .= '</tbody>';
$kpi_lead .= '</table>';
return $kpi_lead;
}
echo 'UPDATED';
exit;
} elseif ($_POST['me_action'] == 'CHANGESTOCKDELAY') {
$new_delay = intval($_POST['new_delay']);
if ($new_delay >= 60) {
$config_open_orders = load_config('open-orders');
$config_open_orders['stock-delay-non-zalando'] = $new_delay;
save_config('open-orders', $config_open_orders);
} else {
$new_delay = 0;
}
echo utf8_encode(strval($new_delay));
exit;
} elseif ($_POST['me_action'] == 'CLOSEORDERSINSENTTAB') {
if (isset($_POST['tobeclosed']) && is_array($_POST['tobeclosed'])) {
$sp_list = $class_sp->retrieveList();
foreach ($_POST['tobeclosed'] as $tbc) {
list($jng_sp_id, $sent_date) = explode('|', $tbc);
$q = "SELECT DISTINCT joi.jng_sp_orders_items_id, jo.jng_sp_orders_id";
$q .= " FROM jng_sp_orders jo";
$q .= " INNER JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_id = jo.jng_sp_orders_id AND joi.status=9 AND joi.confirm_delivery='0'";
$q .= " INNER JOIN jng_sp_orders_items_status_history joish ON joish.jng_sp_orders_items_id=joi.jng_sp_orders_items_id AND joish.status=joi.status";
$q .= " WHERE jo.jng_sp_id={$jng_sp_id} AND joish.status_date LIKE '{$sent_date}%'";
$r = tep_db_query($q);
$orders_list = array();
$items_list = array();
while ($row = tep_db_fetch_array($r)) {
if (!in_array($row['jng_sp_orders_id'], $orders_list)) {
$orders_list[] = $row['jng_sp_orders_id'];
}
$items_list[] = $row['jng_sp_orders_items_id'];
*/
set_time_limit(0);
global $db;
require_once '../confy.php';
require_once '../functions.php';
require_once '../functions-2.php';
require_once DIR_WS_FUNCTIONS . 'html_output.php';
require_once DIR_WS_CLASSES . 'fpdf.php';
tep_db_connect();
define('FPDF_IMGPATH', DIR_WS_IMAGES . 'pdf/');
define('FPDF_FONTPATH', FPDF_IMGPATH . 'fonts/');
use_class('logger');
$logger = new logger('print', 'sales-report-weekly-products');
use_class('jng_sp');
$class_sp = new jng_sp();
$sp_list = $class_sp->retrieveList();
$pid = !empty($_POST['pid']) ? $_POST['pid'] : '';
$filter_date = !empty($_POST['filter_date']) ? $_POST['filter_date'] : date('Y-m-d h:i:s');
$filename = !empty($_POST['filename']) ? $_POST['filename'] : 'sales-report-weekly-products-';
$filename = $filename . date('YmdHi', strtotime($filter_date)) . '.pdf';
$logger->write('Filter Date: ' . $filter_date);
class PDF extends FPDF
{
var $margin = 10;
//Create image stars
function drawStar($stars_amount, $xpos, $ypos, $stars_max = 3, $width = 5, $height = 5)
{
$uri = @$_SERVER["HTTPS"] == "on" ? "https://" : "http://";
$uri .= "manobo.de/images/";
$stars = '';
if ($stars_amount > 0) {
use_class('Product');
use_class('ReportCOGS');
use_class('depot_orders');
use_class('jng_sp');
use_class('jng_sp_orders');
use_class('orders');
$class_sp = new jng_sp();
$class_jo = new jng_sp_orders();
$class_o = new orders();
$class_do = new depot_orders();
if (isset($_GET['week-sum-b2c']) && $_GET['week-sum-b2c'] != '') {
if ($_GET['week-sum-b2c'] == 'PREPAYMENT') {
//date setup
$sp_with_prepayment = array('0', '2', '8');
$sp_filter = implode(',', $sp_with_prepayment);
$sp_list = $class_sp->retrieveList("jng_sp_id IN ({$sp_filter})");
$lookback_start = '-14 days';
$weekly_days = 7;
$date_start = date('Y-m-d', strtotime($lookback_start));
$dates = array();
$datas = array();
for ($d = 0; $d < $weekly_days; $d++) {
$date_ts = strtotime($date_start . " -{$d} days");
$date_display = date('d-M-y', $date_ts);
$date_filter = date('Y-m-d', $date_ts);
$dates[$date_filter] = $date_display;
$datas[$date_filter] = array();
foreach ($sp_with_prepayment as $spid) {
$datas[$date_filter][$spid] = array();
$datas[$date_filter][$spid]['total'] = 0;
$datas[$date_filter][$spid]['canceled'] = 0;
/**
* Get SP data which using amazon logistic
* @return Array Array of SP data
*/
function getSalesPartnerUseAmazonLogistic()
{
use_class('jng_sp');
$class_sp = new jng_sp();
$sps = $class_sp->retrieveList("use_amvd_logistic=1");
return $sps;
}
* @author IT TEAM BONOFACTUM
* @created Apr 14, 2014 4:02:39 PM
*/
global $db;
require_once '../confy.php';
require_once '../functions.php';
require_once '../functions-2.php';
require_once DIR_WS_FUNCTIONS . 'html_output.php';
tep_db_connect();
use_class('logger');
$logger = new logger('cron', 'promised-delivery-date');
use_class('Order');
use_class('SalesPartner');
use_class('jng_sp');
$class_sp = new jng_sp();
$sp_list = $class_sp->retrieveList('sp_type = ' . SalesPartner::TYPE_B2C);
$sp_filter = implode(',', array_keys($sp_list));
//SEND NEW PDD FOR LATE ORDER
//since the cron is running every hour, we should give bali team a chance
//to finish their day and send the products. Therefore we send the delay
//notification only on mon-fri between 14-15 (2-3pm) server time
$day_now = date('N');
$hour_now = date('G');
if (!in_array($day_now, array('6', '7')) && $hour_now >= 14 && $hour_now <= 15) {
$logger->write('Preparing delay notification');
$status_to_check = '2,3,4,5,6';
//$pdd_to_use = 'pdd_start';
$pdd_to_use = 'pdd_end';
$pdd_to_check = date('Y-m-d', strtotime('+2 days'));
//Query J&G and SP orders which status is still in $status_to_check
//and has latest pdd (start range) <= $pdd_to_check
