$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);