rdfx

Untitled

Jul 28th, 2021
739
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.  
  3.     public function getAssemblyMarketplace()
  4.     {
  5.         $sql = Yii::app()->dbRead->createCommand('
  6.               SELECT tmp.*, COUNT(eob.id) AS boxes FROM (
  7.                 SELECT t.*, omc.id AS carry, tp.last_name, COUNT(omq.order_id) AS quantity, d.id AS delivery_id,
  8.                    IF(DAYOFYEAR(d.delivery_date) - DAYOFYEAR(NOW()) = 0, 0, IF(DAYOFYEAR(d.delivery_date) - DAYOFYEAR(NOW()) = 1, 1, 2)) AS days_before_delivery,
  9.                    IF(DAYOFYEAR(o.pickup_date) - DAYOFYEAR(NOW()) = 0, 0, IF(DAYOFYEAR(o.pickup_date) - DAYOFYEAR(NOW()) = 1, 1, 2)) AS days_before_pickup_date,
  10.                    case
  11.                         when t.marketplace_name="express" then 55
  12.                        when t.express=1 and o.status_id not in(6,8) then 50
  13.                        when ome.id is not null  then 49
  14.                        when o.for_showcase = 1 then 47
  15.                        when t.pre_assembly = 1 then 48
  16.                        when max(t.`return`) = 1 then 46
  17.                        when max(t.`return`) = 0 and o.status_id = 8 then 45
  18.                        when (op.id is not null or o.invoice_number is not null) and d.id is null and omc.id is null then 44
  19.                        when omc.id is not null then -2
  20.                        else 0
  21.                    end as sort_order,
  22.                    case
  23.                        when op.id is not null or o.invoice_number is not null then 1 else 0
  24.                    end as payment_priority,
  25.                    max(t.return) as om_return FROM
  26.                        (
  27.                           SELECT om.*, eo.id AS marketplace_id, sm.marketplace AS marketplace_name
  28.                FROM `order_movement` om
  29.                LEFT JOIN `service_order` `so` ON so.order_id = om.order_id
  30.                LEFT JOIN `order` AS o ON o.id = om.`order_id`
  31.                 LEFT JOIN external_order eo ON eo.erp_order_id = om.order_id AND eo.marketplace_id != 0
  32.                LEFT JOIN `storage_address` AS sa ON o.`storage_address_id` = sa.`id`
  33.                LEFT JOIN sites_marketplace sm ON sm.id = eo.marketplace_id
  34.                WHERE om.movement = 0
  35.                 AND (om.date_considered IS NULL OR sa.`storage_address_group_id` IN (42, 43))
  36.                 AND so.id IS NULL
  37.               AND eo.id IS NOT NULL                
  38.                 AND eo.`marketplace_status` != 1          
  39.                GROUP BY `om`.`order_id`
  40.                ) t
  41.        LEFT JOIN `order_movement` `omc` ON t.order_id = omc.order_id AND omc.movement = 1 AND omc.date_considered IS NULL
  42.        LEFT JOIN `order_movement` `omq` ON t.order_id = omq.order_id
  43.        LEFT JOIN `order_movement` `ome` ON t.order_id = ome.order_id and ome.id <> t.id and ome.express=1 and t.express=1
  44.        LEFT JOIN `delivery` `d` ON d.order_id = t.order_id
  45.        LEFT JOIN `order` `o` ON o.id = t.order_id  
  46.        LEFT JOIN `order_payment` `op` ON op.order_id = t.order_id
  47.        LEFT JOIN `tbl_profiles` `tp` ON t.who_express = tp.user_id
  48.        WHERE t.marketplace_id IS NOT NULL and t.marketplace_name != "dbs"
  49.        GROUP BY `t`.`order_id`) AS tmp
  50.        LEFT JOIN external_order_box eob ON eob.external_order_id = tmp.marketplace_id
  51.        GROUP BY tmp.order_id
  52.        ORDER BY tmp.`sort_order` DESC, tmp.`carry`, case when tmp.delivery_id is not null then `days_before_delivery` else `days_before_pickup_date` end, `tmp`.`date` ASC
  53.        ');
  54.  
  55.  
  56.         $assembly = $sql->queryAll();
  57.  
  58.         $orders = [];
  59.         foreach ($assembly as $i => $m) {
  60.             $orders['order_'.$m['marketplace_id']] = $m;
  61.         }
  62.  
  63.         $storage = Order::model()->getStorageAddressByItems(array_column($orders, 'order_id'));
  64.         $orderModels = Order::model()->with(['deliveries_count'])->findAllByAttributes(['id' => array_column($orders, 'order_id')]);
  65.         foreach($orderModels as $order) {
  66.             $orders['order_'.$order->externalOrder->id]['possiblyUsed'] = [];
  67.             if($order->externalOrder) {
  68.                 $orders['order_'.$order->externalOrder->id]['order'] = $order;
  69.                 $possiblyUsed = $this->possiblyUsed($order->id);
  70.                 $orders['order_'.$order->externalOrder->id]['possiblyUsed'] = $possiblyUsed;
  71.                 if(isset($storage[$order->externalOrder->id])) {
  72.                     $orders['order_'.$order->externalOrder->id]['storage'] = $storage[$order->id];
  73.                 }
  74.             }
  75.         }
  76.  
  77.    //     Yii::app()->cache->set('assembly', $orders, 10);
  78.  
  79.         return $orders;
  80.     }
  81.  
  82.     public function possiblyUsed($order_id)
  83.     {
  84.         $out = [];
  85.  
  86.         $query = Yii::app()->db->createCommand('SELECT p.`title` FROM `order_item` AS oi LEFT JOIN product AS p ON p.`id` = oi.`product_id` LEFT JOIN `product_item_movement` AS pim ON pim.`product_item_id` = oi.`product_item_id` LEFT JOIN `storage_address` AS sa ON sa.`id` = pim.`storage_address_id` LEFT JOIN `storage_address_group` AS sag ON sag.`id` = sa.`storage_address_group_id` WHERE oi.`order_id` = :order_id AND (sag.`showcase` = 1 OR pim.`movement_type_id` = 3)');
  87.         $query->bindValues([':order_id' => $order_id]);
  88.         $res = $query->queryAll();
  89.         if($res) {
  90.             $out = array_column($res, 'title');
  91.         }
  92.         return $out;
  93.     }
RAW Paste Data