$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();
} else {
//echo "Product not found: $ean<br />";
} else {
if (!isset($ean_daily[$date])) {
$ean_daily[$date] = array();
}
if (in_array($ean, $ean_daily[$date])) {
$correction_mode = true;
} else {
$correction_mode = false;
$ean_daily[$date][] = $ean;
}
$products_id = $ean_pid[$ean];
$articles_id = $ean_aid[$ean];
$complexity = $ean_complexity[$ean];
$matexp = Product::getClosestMaterialExpensesOnSpecificDate($products_id, $date);
$cogs = Product::getClosestCOGSOnSpecificDate($products_id, $date);
$matexp_total = $qty * $matexp;
$cogs_total = $qty * $cogs;
if ($correction_mode) {
ReportCOGS::addToExistingData($jng_sp_id, $date, $ean, $complexity, $qty, $price_total, $matexp_total, $cogs_total);
} else {
if ($qty >= 0) {
$qty_sold = $qty;
$qty_return = 0;
$price_total_before_return = $price_total;
} else {
$qty_sold = 0;
$qty_return = abs($qty);
$price_total_before_return = 0;
}
ReportCOGS::add($jng_sp_id, $date, $ean, $products_id, $articles_id, $complexity, $qty_sold, $qty_return, $price_total, $matexp_total, $cogs_total, 0, jng_sp::CASH_DISCOUNT_B2B, $price_total_before_return);
