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