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'];