public static function retrieveDailyAverageSoldExcludingPromotionOrdersQuery($date_start, $total_days_to_lookback, $pid_aid_filter = '')
{
$sp_list = SalesPartner::getOldSalesPartnerFunction()->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;
}
$q .= " AND do.trans_type IN (" . depot_orders::FILTER_TRANS_TYPE_ALLREFILL . ")";
$q .= " AND do.products_id={$pid} AND do.articles_id={$aid}";
$q .= " GROUP BY order_date, trans_type";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$refill_type = $row['trans_type'] == 'AR' ? 'auto' : 'manual';
$data[$row['order_date']]['refill_' . $refill_type] = intval($row['refill_qty']);
}
$q = "SELECT order_date, is_promotional, SUM(total_sold) AS total_sold FROM (";
$q .= "(SELECT DATE(joi.shipping_window_open) AS order_date, " . isPromotionalOrderQuery('SP') . " AS is_promotional, 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 joi.shipping_window_open>='{$filter_start_date}' AND joi.shipping_window_open<='{$filter_end_date}'";
$q .= " AND joi.products_id={$pid} AND joi.products_articles_id={$aid}";
$q .= ") UNION ALL (";
$q .= "SELECT DATE(o.date_purchased) AS order_date, " . isPromotionalOrderQuery('JG') . " AS is_promotional, 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>='{$filter_start_date}' AND o.date_purchased<='{$filter_end_date}'";
$q .= " AND op.products_id={$pid} AND op.products_articles_id={$aid}";
$q .= ")) temp_orders_table";
$q .= " GROUP BY order_date, is_promotional";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$promo_type = $row['is_promotional'] ? 'promo' : 'nonpromo';
$data[$row['order_date']]['total_sold_' . $promo_type] = intval($row['total_sold']);
}
$table = array();
$header = array();
$header['w080 tac a'] = 'Date';
$header['w080 tac y'] = 'Total Sold<br />Promotional';
function retrieveOpenOrdersItems()
{
$query = "SELECT 'DP' AS type, do.*, p.stars";
$query .= ", " . isPromotionalOrderQuery('DP') . " AS is_promotion_order";
$query .= " FROM depot_orders do";
$query .= " LEFT JOIN products p ON p.products_id=do.products_id";
$query .= " WHERE do.status IN (1,2) AND do.stock_status IN ('0', 'S')";
//Exclude Outsourcing Orders:
$query .= " AND NOT (do.trans_type='' AND do.trans_id=" . self::MANUAL_REFILL_OUTSOURCED . ")";
$query .= " ORDER BY do.order_date ASC, do.products_id ASC, do.articles_id ASC";
$result = tep_db_query($query);
$order_items = array();
while ($row = tep_db_fetch_array($result)) {
$order_items[$row['depot_orders_id']] = $row;
}
return $order_items;
}
/**
* Retrieve all items considered as "OPEN" for stock checking. The OPEN criteria is configurable and should be check for details inside the function code.
* @return Array of Array containing required Item details
*/
function retrieveOpenOrdersProducts()
{
//Current Open Criteria, configurable in config/open-orders.php
//Bali Status = 1-5 (New to Finish)
//Min Value = 4 EUR (Below this value order will be halted in New Tab)
//Shipping Window Release Buffer = Not used for J&G Orders
$opord_configs = load_config('open-orders');
$query = "SELECT 'JG' AS type, op.orders_id, op.orders_products_id, op.products_id, op.products_articles_id, IFNULL(pa.products_ean, p.products_ean) AS products_ean, op.products_stars";
$query .= ", op.products_quantity, op.order_item_total, op.status, op.stock_status, op.mode, op.print_count, op.prod_target_in, op.prod_target_out, op.ds_prioritized";
$query .= ", p.stars, p.is_outsourced";
$query .= ", o.date_purchased, o.customers_order_count, o.payment_method, o.paid_status";
$query .= ", og.greeting_card_id, csl.list_type AS customer_type";
//$query .= ", opt.customers_text_id, opi.customers_images_id";
$query .= ", TRUE AS shipping_window_is_opened";
$query .= ", " . isPromotionalOrderQuery('JG') . " AS is_promotion_order";
$query .= " FROM orders o";
$query .= " LEFT JOIN customers_special_list csl ON csl.customers_id=o.customers_id";
$query .= " LEFT JOIN orders_gift og ON og.orders_id=o.orders_id";
$query .= " LEFT JOIN orders_products op ON op.orders_id=o.orders_id";
//$query .= " LEFT JOIN orders_products_text opt ON opt.orders_products_id=op.orders_products_id";
//$query .= " LEFT JOIN orders_products_image opi ON opi.orders_products_id=op.orders_products_id";
$query .= " LEFT JOIN products p ON p.products_id=op.products_id";
$query .= " LEFT JOIN products_articles pa ON op.products_articles_id>0 AND pa.products_articles_id=op.products_articles_id";
$query .= " WHERE";
//filter shipping window is not needed for jng
//filter orders status MTO (depending on status settings) or WMO
$query .= " (op.status IN (" . $opord_configs['orders-inbali'] . ") OR (op.status=8 AND op.stock_status!='D'))";
//filter products_id must be > 0
$query .= " AND op.products_id>0";
//filter price must be above our minimum settings
$query .= " AND op.final_price>" . $opord_configs['min-order-value'];
$query .= " GROUP BY op.orders_products_id";
$query .= " ORDER BY o.date_purchased ASC, o.orders_id ASC, op.order_item_count ASC";
$result = tep_db_query($query);
$order_items = array();
while ($row = tep_db_fetch_array($result)) {
$take = !($row['status'] == '1' && $row['payment_method'] == PAYMENT_METHOD_BANK_TRANSFER && $row['paid_status'] == '0');
if ($take) {
$order_items[$row['orders_products_id']] = $row;
}
}
return $order_items;
}
function retrieveItemDetail($item_id)
{
if (!isset($this->items[$item_id])) {
$item_query = "SELECT joi.*, p.products_image, p.stars, ptc.categories_id";
$item_query .= ", " . isPromotionalOrderQuery('SP') . " AS is_promotion_order";
$item_query .= " FROM jng_sp_orders_items joi";
$item_query .= " LEFT JOIN jng_sp_orders jo ON jo.jng_sp_orders_id=joi.jng_sp_orders_id";
$item_query .= " LEFT JOIN products p ON p.products_id=joi.products_id";
$item_query .= " LEFT JOIN products_to_categories ptc ON ptc.products_id=joi.products_id";
$item_query .= " WHERE joi.jng_sp_orders_items_id='{$item_id}'";
$item_result = tep_db_query($item_query);
$item = tep_db_num_rows($item_result) == 1 ? tep_db_fetch_array($item_result) : array();
$this->items[$item_id] = $item;
} else {
$item = $this->items[$item_id];
}
return $item;
}
