//BEFORE DOING ANYTHING, FIRST WE NEED TO SET MATEXP AND COGS OF NEW ORDERS
$timestamp_new_orders_checker = strtotime('-2 days');
$jg_counter = 0;
$sp_counter = 0;
$q = "SELECT * FROM ((" . " SELECT" . " 0 AS jng_sp_id," . " op.orders_products_id AS item_id," . " o.date_purchased AS order_date," . " op.products_id," . " op.products_model AS products_code," . " op.material_expenses AS item_matexp," . " op.cogs AS item_cogs," . " p.material_expenses" . " FROM orders o" . " INNER JOIN orders_products op ON op.orders_id = o.orders_id" . " LEFT JOIN products p ON p.products_id = op.products_id" . " WHERE (op.material_expenses = 0 OR op.cogs = 0)" . " AND op.orders_products_id > 0" . " AND p.material_expenses > 0" . ") UNION ALL (" . " SELECT" . " jo.jng_sp_id," . " joi.jng_sp_orders_items_id AS item_id," . " jo.order_date," . " joi.products_id," . " joi.article_number AS products_code," . " joi.material_expenses AS item_matexp," . " joi.cogs AS item_cogs," . " p.material_expenses" . " FROM jng_sp_orders jo" . " INNER JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_id = jo.jng_sp_orders_id" . " LEFT JOIN products p ON p.products_id = joi.products_id" . " WHERE (joi.material_expenses = 0 OR joi.cogs = 0)" . " AND joi.jng_sp_orders_items_id > 0" . " )) temp_table" . " ORDER BY item_id";
//" LIMIT 10000";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$order_date_timestamp = strtotime($row['order_date']);
$p = new Product($row['products_id']);
//By default we will always use the current material expense and cogs
//it needs to be converted to local currency
$matexp = convertCurrency($row['material_expenses'], CURRENCY_CODE_EURO, CURRENCY_DEFAULT);
$cogs = $p->getProductCOGSValue();
if ($order_date_timestamp < $timestamp_new_orders_checker) {
$matexp = Product::getClosestMaterialExpensesOnSpecificDate($row['products_id'], $row['order_date'], $matexp);
$cogs = Product::getClosestCOGSOnSpecificDate($row['products_id'], $row['order_date'], $cogs);
}
//echo $row['jng_sp_id'] . ' - ' . $row['item_id'] . ' - ' .
//$row['products_code'] . ' - ' . $matexp;
if ($matexp > 0 && $cogs > 0) {
$sda = array();
if ($row['item_matexp'] == 0) {
$sda['material_expenses'] = $matexp;
}
if ($row['item_cogs'] == 0) {
$sda['cogs'] = $cogs;
}
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();
$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'];
$class_mg = new minierp_groups($newsletter_group_id);
$report_members = $class_mg->getMembersGroup();
$subject = '[REPORT] Material Expenses Products Sent - ' . date('F Y', strtotime($prev_month_day_end));
$message = '<strong>' . date('F Y', strtotime($prev_month_day_end)) . '</strong>';
$message .= "<br/><br/>Total material expenses: ";
