public static function getClosestCOGSOnSpecificDate($products_id, $date, $current_cogs = null)
{
$date = date('Y-m-d', strtotime($date));
$date_start = date('Y-m-d', strtotime($date . ' -10 days'));
$date_end = date('Y-m-d', strtotime($date . ' +10 days'));
$q2 = "SELECT cogs FROM jng_sp_orders_items" . " WHERE products_id = {$products_id}" . " AND shipping_window_open >= '{$date_start}'" . " AND shipping_window_open <= '{$date_end}'" . " AND cogs > 0" . " ORDER BY RAND() LIMIT 1";
$r2 = tep_db_query($q2);
if (tep_db_num_rows($r2) > 0) {
$row2 = tep_db_fetch_array($r2);
$cogs = $row2['cogs'];
} else {
//check latest order after
$q3 = "SELECT cogs FROM jng_sp_orders_items" . " WHERE products_id = {$products_id}" . " AND shipping_window_open >= '{$date_start}'" . " AND cogs > 0" . " ORDER BY shipping_window_open ASC LIMIT 1";
$r3 = tep_db_query($q3);
if (tep_db_num_rows($r3) > 0) {
$row3 = tep_db_fetch_array($r3);
$cogs = $row3['cogs'];
} else {
//check previous order before
$q4 = "SELECT cogs FROM jng_sp_orders_items" . " WHERE products_id = {$products_id}" . " AND shipping_window_open <= '{$date_start}'" . " AND cogs > 0" . " ORDER BY shipping_window_open DESC LIMIT 1";
$r4 = tep_db_query($q4);
if (tep_db_num_rows($r4) > 0) {
$row4 = tep_db_fetch_array($r4);
$cogs = $row4['cogs'];
} else {
if (is_null($current_cogs)) {
$p = new Product($products_id);
$cogs = $p->getCOGSValue();
} else {
$cogs = $current_cogs;
}
}
}
}
return $cogs;
}
$total_cogs_to_id = 0;
$total_qty_to_id = 0;
$total_matexp_to_id = 0;
$country_name = getCountryName(SERVER_LOCAL_CODE_INDONESIA);
$q = "SELECT do.depot_orders_id, do.products_id, MAX(dosh.update_time) AS update_time,";
$q .= " dosh.status, do.group_name, SUM(do.quantity) as quantity, dosh.update_by FROM depot_orders do";
$q .= " INNER JOIN depot_orders_status_history dosh ON dosh.depot_orders_id = do.depot_orders_id";
$q .= " WHERE do.group_name = 'Manobo Local Orders ({$country_name})'" . " AND dosh.status = '11' " . " AND dosh.update_time >= '{$prev_month_day_start}'" . " AND dosh.update_time <= '{$prev_month_day_end}'";
$q .= " GROUP BY do.products_id";
$r = tep_db_query($q);
if (tep_db_num_rows($r) > 0) {
while ($row = tep_db_fetch_array($r)) {
$products_id = $row['products_id'];
$product = new Product($products_id);
//COGS
$cogs = $product->getCOGSValue();
$total_cogs_to_id += $cogs * $row['quantity'];
$total_qty_to_id += $row['quantity'];
//MATEXP
$date = $row['update_time'];
$mat_exp = Product::getClosestMaterialExpensesOnSpecificDate($products_id, $date);
$total_matexp_to_id += $mat_exp * $row['quantity'];
}
}
//START PREPAIR EMAIL AND SEND
/* 1) TOTAL MATERIAL EXPENSES OF SENT PRODUCTS - LAST MONTH - TO:
- HAMBURG
- LOCAL INDONESIA
notes: mat.exp taken from latest date products sent to HH */
$report_config = load_config('report-settings');
$newsletter_group_id = $report_config['nlaccreportweeklygroupid'];
