/** * Создает коллекцию товарных предложений в акции * @param int $offer_id */ public static function makeProductsInPurchasesCollection($offer_id = 0) { $offer_id = (int) $offer_id; $product_in_purchase_table_name = \App\Models\ProductInPurchase::TABLE_NAME; /** * Очищаем все товарные предложения в акциях, лоя которых нет реальных активных товарных предложений */ \DB::table($product_in_purchase_table_name)->where('offers_count', '<', 1)->delete(); if ($offer_id > 0) { /** * Если указан ID торговой акции, то очищаем товарные предложения только для неё */ \DB::table($product_in_purchase_table_name)->where('offer_id', '=', $offer_id)->delete(); $insert_sql = "insert {$product_in_purchase_table_name} (product_offer_id, offer_id, min_price, offers_count) (\n select pp.product_offer_id, co.offer_id,\n min(pp.value)*(100+ofr.margin_percent)/100 as min_price,\n count(pp.product_offer_id) as offers_count\n from offers ofr\n join catalog_offer co on co.offer_id = ofr.id\n join catalogs ct on ct.id = co.catalog_id\n join products_offers po on po.catalog_id = ct.id\n join products_prices pp on pp.product_offer_id = po.id\n where ofr.status = 1 and ct.status = 1 and po.status = 1 and ofr.id = {$offer_id}\n group by pp.product_offer_id, co.offer_id\n )"; } else { /** * Если НЕ указан ID торговой акции, то очищаем товарные предложения для всех акций */ \DB::table($product_in_purchase_table_name)->delete(); $insert_sql = "insert {$product_in_purchase_table_name} (product_offer_id, offer_id, min_price, offers_count) (\n select pp.product_offer_id, co.offer_id,\n min(pp.value)*(100+ofr.margin_percent)/100 as min_price,\n count(pp.product_offer_id) as offers_count\n from offers ofr\n join catalog_offer co on co.offer_id = ofr.id\n join catalogs ct on ct.id = co.catalog_id\n join products_offers po on po.catalog_id = ct.id\n join products_prices pp on pp.product_offer_id = po.id\n where ofr.status = 1 and ct.status = 1 and po.status = 1\n group by pp.product_offer_id, co.offer_id\n )"; } \DB::insert($insert_sql); }