Last active
July 1, 2024 07:24
-
-
Save videni/c19c6ffdfc90fad16412ce5aa89f6bb6 to your computer and use it in GitHub Desktop.
A aswsomes 1200 line method
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| public function QueryBuilder(array $criteria, array $depths = []): QueryBuilder | |
| { | |
| $builder = QueryBuilder::for(Order::class); | |
| $condition = $depths['condition'] ?? ''; | |
| $with = [ | |
| 'urgentType', | |
| // 'orderItems', | |
| // 'orderLogisticsFeatures', | |
| 'orderItemsField', | |
| 'orderItemLogisticsFeatures', | |
| 'customTagsEnable', | |
| 'abolishReason', | |
| 'systemTagsEnable', | |
| // 'orderDeclares', | |
| 'deliveryType', | |
| 'orderAmount', | |
| ]; | |
| if (isset($criteria['loadCustomTagHandlers'])){ | |
| $with[] = 'customTagsEnable.handlers'; | |
| } | |
| // 自定义加载关联关系 | |
| if (isset($depths['with'])) { | |
| $with = $depths['with']; | |
| } | |
| // 支持传入select 检索字段 | |
| if (isset($depths['select']) && !empty($depths['select'])) { | |
| $select = array_unique(array_merge(['orders.id'], $depths['select'])); | |
| // 防止有排序字段时，select中没有该字段而报错 订单排序目前都是orders表字段 | |
| if (isset($criteria['sort'])) { | |
| $sort_filed = ltrim($criteria['sort'], '-'); | |
| if ($sort_filed == 'payment_at_gmt') { | |
| $sort_filed = 'payment_at_timezone'; | |
| } | |
| $select[] = 'orders.' . $sort_filed; | |
| } | |
| $builder->select($select); | |
| } else { | |
| $builder->select('orders.*'); | |
| } | |
| $is_warehouse = $this->checkWarehousePermission($criteria); | |
| $leftJoinItem = false; | |
| // 数据权限过滤 | |
| if (isset($criteria['permission'])) { | |
| if (isset($criteria['permission']['pattern'])) { | |
| if (empty($criteria['permission']['pattern'])) { | |
| return $builder->where('orders.id', '=', -1); | |
| } | |
| $pattern = $criteria['permission']['pattern']; | |
| $options = $criteria['permission']['options'] ?? []; | |
| if (empty($options)) { | |
| return $builder->where('orders.id', '=', -1); | |
| } | |
| // 按照店铺查看 | |
| if ($pattern == 'order_show_shop') { | |
| if (is_array($options) && !empty(array_filter($options))) { | |
| $builder->whereIn('orders.shop_id', $options); | |
| } | |
| } | |
| // 按照仓库查看 | |
| if ($pattern == 'order_show_warehouse') { | |
| if (is_array($options) && !empty(array_filter($options))) { | |
| $builder->leftJoin('order_items', 'orders.id', '=', 'order_items.order_id') | |
| ->whereIn('order_items.sku_warehouse_id', array_filter($options))->distinct('orders.id'); | |
| $leftJoinItem = true; | |
| } | |
| } | |
| // 按照物流渠道查看 | |
| if ($pattern == 'order_show_logistic') { | |
| if (is_array($options)) { | |
| // 只有空物流渠道 | |
| if (in_array(0, $options) && count($options) == 1) { | |
| $builder->whereNull('orders.logistic_channel_id'); | |
| } | |
| // 只有正常物流渠道 | |
| if (!in_array(0, $options) && count($options) > 0) { | |
| $builder->whereIn('orders.logistic_channel_id', $options); | |
| } | |
| // 既有正常渠道 又有空渠道 | |
| if (in_array(0, $options) && count($options) > 1) { | |
| $builder->where( | |
| function ($query1) use ($options) { | |
| $query1->whereIn('orders.logistic_channel_id', $options) | |
| ->orWhereNull('orders.logistic_channel_id'); | |
| } | |
| ); | |
| } | |
| } | |
| } | |
| } | |
| } | |
| if (isset($criteria['ids'])) { | |
| $builder->useIndex('PRIMARY'); | |
| $builder->whereIn('orders.id', array_unique(array_filter($criteria['ids']))); | |
| } | |
| // 订单页签列表检索 | |
| if (isset($depths['tab']) && !empty($depths['tab'])) { | |
| // 待处理订单列表 | |
| if ($depths['tab'] == OrderConstants::TAB_PENDING) { | |
| $builder->where('orders.order_status', '=', Order::PENDING) | |
| // ->where('orders.transport_status', '=', Order::TRANSPORT_STATUS_PENDING) | |
| ->where('orders.is_abnormal', '=', Order::NORMAL_ORDER); | |
| // ->whereNotIn( | |
| // 'orders.id', | |
| // function ($query) { | |
| // $query->select('orders.id')->from('orders') | |
| // ->leftJoin('order_multiple_tags', 'orders.id', '=', 'order_multiple_tags.order_id') | |
| // ->where('order_multiple_tags.is_handle', '=', 0) | |
| // ->where('order_multiple_tags.type', '=', 2); | |
| // } | |
| // ); | |
| } | |
| // 交运订单列表 | |
| if ($depths['tab'] == OrderConstants::TAB_SHIP) { | |
| $builder->where('orders.order_status','=',Order::DISTRIBUTION) | |
| ->where('orders.is_abnormal', '=', Order::NORMAL_ORDER); | |
| // ->whereNotIn( | |
| // 'orders.id', | |
| // function ($query) { | |
| // $query->select('orders.id')->from('orders') | |
| // ->leftJoin('order_multiple_tags', 'orders.id', '=', 'order_multiple_tags.order_id') | |
| // ->where('order_multiple_tags.is_handle', '=', 0) | |
| // ->where('order_multiple_tags.type', '=', 2); | |
| // } | |
| // ); | |
| } | |
| // 异常订单列表 | |
| if ($depths['tab'] == OrderConstants::TAB_ABNORMAL) { | |
| if (isset($criteria['abnormal_tag_id']) && $criteria['abnormal_tag_id'] != '') | |
| { | |
| $this->filterAbnormalTagById($builder, $criteria['abnormal_tag_id']); | |
| $joinMultipleTags = true; | |
| } else { | |
| // 查询全部异常订单 | |
| $builder->where('orders.is_abnormal', '=', Order::ABNORMAL_ORDER); | |
| $builder->whereIn( | |
| 'orders.order_status', | |
| [Order::PENDING, Order::DISTRIBUTION, Order::DELIVERY] | |
| ); | |
| } | |
| } | |
| // 交运异常订单列表 | |
| if ($depths['tab'] == OrderConstants::SHIP_ABNORMAL) { | |
| $builder->where('orders.transport_status', '=', Order::TRANSPORT_STATUS_FAIL) | |
| ->where('orders.transport_error_remove', '=', 0) | |
| ->where('orders.is_abnormal','=',Order::NORMAL_ORDER) | |
| ->whereIn('orders.order_status', [Order::PENDING, Order::DISTRIBUTION]); | |
| } | |
| // 已发货订单列表 发货中订单列表 | |
| if ($depths['tab'] == OrderConstants::TAB_SHIPPED || $depths['tab'] == OrderConstants::TAB_SHIPMENT) { | |
| // $builder->whereNotIn( | |
| // 'orders.id', | |
| // function ($query) { | |
| // $query->select('orders.id')->from('orders') | |
| // ->leftJoin('order_multiple_tags', 'orders.id', '=', 'order_multiple_tags.order_id') | |
| // ->where('order_multiple_tags.is_handle', '=', 0) | |
| // ->where('order_multiple_tags.type', '=', 2); | |
| // } | |
| // ); | |
| $builder->where('orders.is_abnormal', '=', Order::NORMAL_ORDER); | |
| } | |
| // } else { | |
| // // 全部订单tab：默认排序：优先订单状态排序（异常订单>待处理>可配货>发货中>已发货>已作废,按以上顺序排序），订单状态一致时按付款时间排序 | |
| // $builder->orderBy('is_abnormal', 'desc')->orderBy('order_status'); | |
| } | |
| //全部订单tab下，多选tab检索 | |
| if (!empty($criteria['status_tab'])){ | |
| $tabs = is_array($criteria['status_tab']) ? $criteria['status_tab'] : explode(',',$criteria['status_tab']); | |
| $tabConditions = [ | |
| //异常订单 | |
| OrderConstants::TAB_ABNORMAL => function (Builder $query) { | |
| $query->whereIn('orders.order_status', [Order::PENDING, Order::DISTRIBUTION, Order::DELIVERY])->where('orders.is_abnormal', Order::ABNORMAL_ORDER); | |
| }, | |
| //待处理 | |
| OrderConstants::TAB_PENDING => function (Builder $query) { | |
| $query->whereIn('orders.order_status', [Order::PENDING])->where('orders.is_abnormal', Order::NORMAL_ORDER); | |
| }, | |
| //交运订单 | |
| OrderConstants::TAB_SHIP => function (Builder $query) { | |
| $query->whereIn('orders.order_status', [Order::DISTRIBUTION])->where('orders.is_abnormal', Order::NORMAL_ORDER); | |
| }, | |
| //发货中 | |
| OrderConstants::TAB_SHIPMENT => function (Builder $query) { | |
| $query->whereIn('orders.order_status', [Order::DELIVERY])->where('orders.is_abnormal', Order::NORMAL_ORDER); | |
| }, | |
| //已发货 | |
| OrderConstants::TAB_SHIPPED => function (Builder $query) { | |
| $query->whereIn('orders.order_status', [Order::DELIVERED])->where('orders.is_abnormal', Order::NORMAL_ORDER); | |
| }, | |
| //已作废 | |
| OrderConstants::TAB_REPEAL => function (Builder $query) { | |
| $query->whereIn('orders.order_status', [Order::REPEAL])->where('orders.is_abnormal', Order::NORMAL_ORDER); | |
| }, | |
| ]; | |
| $builder->where(function (Builder $query) use ($tabs, $tabConditions) { | |
| foreach ($tabs as $tab) { | |
| if (isset($tabConditions[$tab])) { | |
| $query->orWhere(function (Builder $query) use ($tabConditions, $tab) { | |
| $tabConditions[$tab]($query); | |
| }); | |
| } | |
| } | |
| }); | |
| } | |
| if (!empty($criteria['order_status'])) { | |
| $orderStatus = $criteria['order_status']; | |
| if (!is_array($criteria['order_status'])) { | |
| $orderStatus = (array)$orderStatus; | |
| } | |
| $builder->whereIn('orders.order_status', $orderStatus); | |
| } | |
| $normalFilterOptions = [ | |
| 'shop_id', | |
| 'logistic_channel_id', | |
| 'logistic_id', | |
| 'urgent_type_id', | |
| ]; | |
| $timeIntervalOptions = [ | |
| 'payment_at_gmt', | |
| 'delivered_at_gmt', | |
| 'handled_at_gmt', | |
| 'transport_at_gmt', | |
| 'repeal_at_gmt', | |
| 'created_at_gmt', | |
| 'transfer_no_updated_at_gmt', | |
| ]; | |
| $seniorFilterOptions = [ | |
| 'plat_order_no', | |
| 'transaction_no', | |
| 'paypal_id', | |
| 'logistic_freight_no', | |
| 'logistic_freight_transfer_no', | |
| 'logistic_virtual_number', | |
| 'logistic_freight_inner_no', | |
| // 'order_sku_detail',// 特殊查询，为提高代码可读性，从改数组中移除，单独编写检索逻辑，下同 | |
| 'customer_name', | |
| 'customer_email', | |
| 'customer_country', | |
| 'customer_city', | |
| 'customer_state', | |
| 'customer_tel', | |
| 'customer_address1', | |
| 'customer_address2', | |
| 'customer_zip', | |
| ]; | |
| $intervalOptions = [ | |
| // 'zip_long', | |
| 'sku_quantity', | |
| 'sku_quantity_total', | |
| 'estimated_weight', | |
| 'logistic_weight_by_hub', | |
| 'logistic_weight', | |
| 'total_amount_cny', | |
| 'total_amount', | |
| ]; | |
| $keywordOptions = [ | |
| 'reshipping_remarks', | |
| 'remarks', | |
| 'remarks_delivery', | |
| 'transport_error_message', | |
| // 'abolish_reason', | |
| 'abolish_remark',//作废说明 | |
| ]; | |
| foreach ($criteria as $key => $value) { | |
| if (empty($value)) { | |
| continue; | |
| } | |
| if (in_array($key,$normalFilterOptions)){ | |
| // 物流公司、物流渠道支持查空 前端传0过来 | |
| if (in_array($key,['logistic_id','logistic_channel_id']) && in_array(0, $value)) { | |
| $builder->where(function ($query) use ($key,$value) { | |
| $query->whereIn('orders.'.$key, array_filter($value)) | |
| ->orWhereNull('orders.'.$key); | |
| }); | |
| }else { | |
| $builder->whereIn('orders.'.$key,array_filter($value)); | |
| } | |
| continue; | |
| } | |
| if (in_array($key,$timeIntervalOptions)){ | |
| if($key == 'payment_at_gmt'){ | |
| $key = 'payment_at_timezone'; | |
| } | |
| if($key == 'transport_at_gmt'){ | |
| $builder->where('orders.transport_status','!=',4); | |
| } | |
| if (isset($value['start']) && !empty($value['start'])) { | |
| $builder->where('orders.' . $key, '>=', $value['start']); | |
| } | |
| if (isset($value['end']) && !empty($value['end'])) { | |
| $builder->where('orders.' . $key, '<=', $value['end']); | |
| } | |
| continue; | |
| } | |
| if (in_array($key,$seniorFilterOptions) && is_array($value)){ | |
| $this->transformAndLoadBuilderByMatch($builder, $value['condition'], 'orders.'.$key, $value['value'] ?? []);//condition=3时，前端不传value | |
| continue; | |
| } | |
| if (in_array($key,$intervalOptions)){ | |
| $start = $value['start'] ?? null; | |
| $end = $value['end'] ?? null; | |
| if (!empty($start)){ | |
| $builder->where('orders.'.$key,'>=',$start); | |
| } | |
| if (!empty($end)){ | |
| $builder->where('orders.'.$key,'<=',$end); | |
| } | |
| continue; | |
| } | |
| if (in_array($key,$keywordOptions)){ | |
| $builder->where('orders.'.$key, 'LIKE', '%' . $value . '%'); | |
| continue; | |
| } | |
| if (in_array($key, ['plat_sku','order_sku_detail'])) { | |
| if (!$leftJoinItem){ | |
| $builder->join('order_items','orders.id','=','order_items.order_id'); | |
| } | |
| $this->transformAndLoadBuilderByMatch($builder, $value['condition'], 'order_items.plat_sku', $value['value']); | |
| continue; | |
| } | |
| if ($key == 'zip_long'){ | |
| $start = $value['start'] ?? null; | |
| $end = $value['end'] ?? null; | |
| if (!empty($start)){ | |
| $builder->whereRaw("IFNULL(LENGTH(orders.customer_zip),0) >= $start"); | |
| } | |
| if (!empty($end)){ | |
| $builder->whereRaw("IFNULL(LENGTH(orders.customer_zip),0) <= $end"); | |
| } | |
| continue; | |
| } | |
| if ($key == 'abolish_reason'){ | |
| $builder->leftJoin('abolish_reasons','orders.abolish_reason_id','=','abolish_reasons.id') | |
| ->where('abolish_reasons.title','LIKE','%' . $value . '%'); | |
| continue; | |
| } | |
| } | |
| //加急分类反选 | |
| if (!empty($criteria['no_urgent_type_id']) && is_array($criteria['no_urgent_type_id'])){ | |
| if (empty($criteria['urgent_type_id'])){ | |
| $builder->where(function ($query) use ($criteria){ | |
| $query->whereNotIn('urgent_type_id',$criteria['no_urgent_type_id']) | |
| ->orWhereNull('urgent_type_id'); | |
| }); | |
| }else{ | |
| $builder->whereNotIn('urgent_type_id',$criteria['no_urgent_type_id']); | |
| } | |
| } | |
| // 是否有货运转单号查询 | |
| if (isset($criteria['is_transfer_no']) && in_array($criteria['is_transfer_no'], [0, 1])) { | |
| if($criteria['is_transfer_no'] == 0) { | |
| $builder->where('is_real_transfer_no', '=', 1) | |
| ->whereNotNull('logistic_freight_transfer_no'); | |
| } else { | |
| $builder->whereNull('logistic_freight_transfer_no'); | |
| } | |
| } | |
| if (isset($criteria['is_real_transfer_no']) && in_array($criteria['is_real_transfer_no'], [0, 1])) { | |
| $builder->where('is_real_transfer_no', '=', $criteria['is_real_transfer_no']); | |
| } | |
| if (isset($criteria['ship_warehouse_ids']) && !empty($criteria['ship_warehouse_ids'])) { | |
| if (!$leftJoinItem){ | |
| $builder->leftJoin('order_items','orders.id','=','order_items.order_id'); | |
| $leftJoinItem = true; | |
| } | |
| $warehouseIds = is_array($criteria['ship_warehouse_ids']) ? $criteria['ship_warehouse_ids'] : explode(',', $criteria['ship_warehouse_ids']); | |
| $builder->whereIn('order_items.sku_warehouse_id', array_filter($warehouseIds))->distinct('orders.id'); | |
| } | |
| // 平台、店铺域名检索 需要联shops表 | |
| if (!empty($criteria['plat_ids']) || !empty($criteria['shop_domain'])) { | |
| $builder->leftJoin('shops','orders.shop_id','=','shops.id'); | |
| if (!empty($criteria['plat_ids'])){ | |
| $platIds = is_array($criteria['plat_ids']) ? $criteria['plat_ids'] : explode(',', $criteria['plat_ids']); | |
| $builder->whereIn('shops.shop_plat_id', array_filter($platIds)); | |
| } | |
| if (!empty($criteria['shop_domain'])){ | |
| $shop_domain = $criteria['shop_domain']; | |
| $this->transformAndLoadBuilderByMatch($builder, $shop_domain['condition'], 'shops.code_url', $shop_domain['value'] ?? []); | |
| } | |
| } | |
| // SKU、SPU、SKU名称检索 需要联order_items表 | |
| if (isset($criteria['skus']) || isset($criteria['spus']) || isset($criteria['sku_title'])){//sku、spu、title | |
| if (!$leftJoinItem) { | |
| $builder->leftJoin('order_items', 'orders.id', '=', 'order_items.order_id'); | |
| $leftJoinItem = true; | |
| } | |
| // SKU检索 | |
| if (isset($criteria['skus'])) { | |
| $this->transformAndLoadBuilderByMatch($builder, $criteria['sku_condition'], 'order_items.sku_code', $criteria['skus']); | |
| } | |
| // SPU检索 需要联skus、spus表 | |
| if (isset($criteria['spus'])) { | |
| $builder->leftJoin('skus', 'order_items.sku_id', '=', 'skus.id') | |
| ->leftJoin('spus', 'skus.spu_id', '=', 'spus.id'); | |
| if ($criteria['spu_condition'] == 3){ | |
| $builder->whereNull('skus.spu_id'); | |
| }else{ | |
| $this->transformAndLoadBuilderByMatch($builder, $criteria['spu_condition'], 'spus.spu_code', $criteria['spus']); | |
| } | |
| } | |
| // SKU名称检索 | |
| if (isset($criteria['sku_title'])) { | |
| $this->transformAndLoadBuilderByMatch($builder, $criteria['title_condition'], 'order_items.title', $criteria['titles'] ?? []); | |
| } | |
| $builder->distinct('orders.id'); | |
| } | |
| //交运状态检索 | |
| if (!empty($criteria['transport_status']) && is_array($criteria['transport_status'])){ | |
| $transportStatus = array_filter($criteria['transport_status'],function ($value){ | |
| return $value >= 0 && $value <= 5; | |
| }); | |
| //待交运实际有0和1，前端只会传0，因此需要将1也添加进数组 | |
| if (in_array(Order::TRANSPORT_STATUS_PENDING,$transportStatus)){ | |
| $transportStatus[] = Order::TRANSPORT_STATUS_START; | |
| } | |
| $builder->where(function ($query) use ($transportStatus){ | |
| //没有'交运成功'和'交运超期'的，直接wherein | |
| if ( | |
| (!in_array(Order::TRANSPORT_STATUS_SUCCESS,$transportStatus) && !in_array(Order::TRANSPORT_STATUS_OVERDUE,$transportStatus)) | |
| || (in_array(Order::TRANSPORT_STATUS_SUCCESS,$transportStatus) && in_array(Order::TRANSPORT_STATUS_OVERDUE,$transportStatus)) | |
| ) | |
| { | |
| $query->whereIn('orders.transport_status',$transportStatus); | |
| } | |
| //有'交运成功'，无'交运超期' | |
| elseif (in_array(Order::TRANSPORT_STATUS_SUCCESS,$transportStatus) && !in_array(Order::TRANSPORT_STATUS_OVERDUE,$transportStatus)){ | |
| $transportStatus = array_filter($transportStatus,function ($value){ | |
| return $value != Order::TRANSPORT_STATUS_SUCCESS; | |
| }); | |
| //不包含交运成功的 | |
| if (!empty($transportStatus)){ | |
| $query->whereIn('orders.transport_status',$transportStatus); | |
| } | |
| // 1、手填单号，状态=成功，交运超期时间为空 | |
| $query->orWhere(function ($query1){ | |
| $query1->where('orders.transport_status','=',Order::TRANSPORT_STATUS_SUCCESS) | |
| ->whereNull('orders.transport_expired_gmt'); | |
| }); | |
| // 2、状态=成功、订单已发货 | |
| $query->orWhere(function ($query2){ | |
| $query2->where('orders.transport_status','=',Order::TRANSPORT_STATUS_SUCCESS) | |
| ->where('orders.order_status','=',Order::DELIVERED); | |
| }); | |
| // 3、状态=成功，未超期的 | |
| $query->orWhere(function ($query3){ | |
| $query3->where('orders.transport_status','=',Order::TRANSPORT_STATUS_SUCCESS) | |
| ->where('orders.transport_expired_gmt','>=',Carbon::now()); | |
| }); | |
| } | |
| //有'交运超期'，无交运成功 | |
| elseif (!in_array(Order::TRANSPORT_STATUS_SUCCESS,$transportStatus) && in_array(Order::TRANSPORT_STATUS_OVERDUE,$transportStatus)){ | |
| $query->whereIn('orders.transport_status',$transportStatus); | |
| $query->orWhere(function ($query4){ | |
| $query4->where('orders.transport_status','=',Order::TRANSPORT_STATUS_SUCCESS) | |
| ->where('orders.transport_expired_gmt','<',Carbon::now()) | |
| ->whereIn('orders.order_status',[Order::PENDING,Order::DISTRIBUTION,Order::DELIVERY]); | |
| }); | |
| } | |
| }); | |
| } | |
| // 异常订单tab下选择某个异常分类时，返回查询失败列表专用 | |
| if (!empty($criteria['filter_abnormal_tag_id']) && !($joinMultipleTags ?? false)){//防止重复left join | |
| $this->filterAbnormalTagById($builder, $criteria['filter_abnormal_tag_id']); | |
| } | |
| //旧版本检索器👇 | |
| // 此处针对交运异常列表兼容开头是，结尾是的查询条件 | |
| $oldOrderFilterFields = [ | |
| 'plat_order_no' => 'plat_order_no', | |
| 'transaction_no' => 'transaction_no', | |
| 'customer_name' => 'customer_name', | |
| 'delivery_error' => 'transport_error_message', | |
| 'customer_country_code' => 'customer_country', | |
| ]; | |
| foreach ($oldOrderFilterFields as $alias => $orderField) { | |
| if (!isset($criteria[$alias])){ | |
| continue; | |
| } | |
| if (in_array($condition,[1,2,4,5]) && !empty($criteria[$alias])){ | |
| $value = explode(PHP_EOL, $criteria[$alias]); | |
| $this->transformAndLoadBuilderByMatch($builder, $condition, 'orders.' . $orderField, $value); | |
| } | |
| } | |
| if (isset($criteria['plat_sku'])) { | |
| if ($condition == 1 && $criteria['plat_sku'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['plat_sku']); | |
| $builder->whereIn('orders.order_sku_detail', $arr); | |
| } | |
| if ($condition == 2 && $criteria['plat_sku'] != '') { | |
| $builder->where('orders.order_sku_detail', 'like', '%' . $criteria['plat_sku'] . '%'); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereNull('orders.order_sku_detail'); | |
| } | |
| } | |
| if (isset($criteria['logistic_freight_no'])) { | |
| if ($condition == 1 && $criteria['logistic_freight_no'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['logistic_freight_no']); | |
| $builder->whereIn('orders.logistic_freight_no', $arr); | |
| } | |
| if ($condition == 2 && $criteria['logistic_freight_no'] != '') { | |
| $builder->where('orders.logistic_freight_no', 'like', '%' . $criteria['logistic_freight_no'] . '%'); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereNull('orders.logistic_freight_no'); | |
| } | |
| } | |
| if (isset($criteria['logistic_freight_inner_no'])) { | |
| if ($condition == 1 && $criteria['logistic_freight_inner_no'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['logistic_freight_inner_no']); | |
| $builder->whereIn('orders.logistic_freight_inner_no', $arr); | |
| } | |
| if ($condition == 2 && $criteria['logistic_freight_inner_no'] != '') { | |
| $builder->where( | |
| 'orders.logistic_freight_inner_no', | |
| 'like', | |
| '%' . $criteria['logistic_freight_inner_no'] . '%' | |
| ); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereNull('orders.logistic_freight_inner_no'); | |
| } | |
| } | |
| if (isset($criteria['customer_email'])) { | |
| if ($condition == 1 && $criteria['customer_email'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['customer_email']); | |
| $builder->whereIn('orders.customer_email', $arr); | |
| } | |
| if ($condition == 2 && $criteria['customer_email'] != '') { | |
| $builder->where('orders.customer_email', 'like', '%' . $criteria['customer_email'] . '%'); | |
| } | |
| if ($condition == 3) { | |
| $builder->where('orders.customer_email', '=', ''); | |
| } | |
| } | |
| if (isset($criteria['paypal_id'])) { | |
| if ($condition == 1 && $criteria['paypal_id'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['paypal_id']); | |
| $builder->whereIn('orders.paypal_id', $arr); | |
| } | |
| if ($condition == 2 && $criteria['paypal_id'] != '') { | |
| $builder->where('orders.paypal_id', 'like', '%' . $criteria['paypal_id'] . '%'); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereNull('orders.paypal_id'); | |
| } | |
| } | |
| if (isset($criteria['customer_state'])) { | |
| if ($condition == 1 && $criteria['customer_state'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['customer_state']); | |
| $builder->whereIn('orders.customer_state', $arr); | |
| } | |
| if ($condition == 2 && $criteria['customer_state'] != '') { | |
| $builder->where('orders.customer_state', 'like', '%' . $criteria['customer_state'] . '%'); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereNull('orders.customer_state'); | |
| } | |
| } | |
| if (isset($criteria['customer_city'])) { | |
| if ($condition == 1 && $criteria['customer_city'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['customer_city']); | |
| $builder->whereIn('orders.customer_city', $arr); | |
| } | |
| if ($condition == 2 && $criteria['customer_city'] != '') { | |
| $builder->where('orders.customer_city', 'like', '%' . $criteria['customer_city'] . '%'); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereNull('orders.customer_city'); | |
| } | |
| } | |
| if (isset($criteria['customer_tel'])) { | |
| if ($condition == 1 && $criteria['customer_tel'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['customer_tel']); | |
| $builder->whereIn('orders.customer_tel', $arr); | |
| } | |
| if ($condition == 2 && $criteria['customer_tel'] != '') { | |
| $builder->where('orders.customer_tel', 'like', '%' . $criteria['customer_tel'] . '%'); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereNull('orders.customer_tel'); | |
| } | |
| } | |
| if (isset($criteria['sku_quantity'])) { | |
| if ($condition == 1 && $criteria['sku_quantity'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['sku_quantity']); | |
| $builder->whereIn('orders.sku_quantity_total', $arr); | |
| } | |
| if ($condition == 2 && $criteria['sku_quantity'] != '') { | |
| $builder->where('orders.sku_quantity_total', 'like', '%' . $criteria['sku_quantity'] . '%'); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereNull('orders.sku_quantity_total'); | |
| } | |
| } | |
| if (isset($criteria['customer_address1'])) { | |
| if ($condition == 1 && $criteria['customer_address1'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['customer_address1']); | |
| $builder->whereIn('orders.customer_address1', $arr); | |
| } | |
| if ($condition == 2 && $criteria['customer_address1'] != '') { | |
| $builder->where('orders.customer_address1', 'like', '%' . $criteria['customer_address1'] . '%'); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereNull('orders.customer_address1'); | |
| } | |
| } | |
| if (isset($criteria['customer_address2'])) { | |
| if ($condition == 1 && $criteria['customer_address2'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['customer_address2']); | |
| $builder->whereIn('orders.customer_address2', $arr); | |
| } | |
| if ($condition == 2 && $criteria['customer_address2'] != '') { | |
| $builder->where('orders.customer_address2', 'like', '%' . $criteria['customer_address2'] . '%'); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereNull('orders.customer_address2'); | |
| } | |
| } | |
| if (isset($criteria['remarks'])) { | |
| if ($condition == 1 && $criteria['remarks'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['remarks']); | |
| $builder->whereIn('orders.remarks', $arr); | |
| } | |
| if ($condition == 2 && $criteria['remarks'] != '') { | |
| $builder->where('orders.remarks', 'like', '%' . $criteria['remarks'] . '%'); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereNull('orders.remarks'); | |
| } | |
| } | |
| // 邮编 | |
| if (isset($criteria['customer_zip'])) { | |
| if ($condition == 1 && $criteria['customer_zip'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['customer_zip']); | |
| $builder->whereIn('orders.customer_zip', $arr); | |
| } | |
| if ($condition == 2 && $criteria['customer_zip'] != '') { | |
| $builder->where('orders.customer_zip', 'like', '%' . $criteria['customer_zip'] . '%'); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereNull('orders.customer_zip'); | |
| } | |
| } | |
| // 邮编长度 | |
| if (isset($criteria['customer_zip_long'])) { | |
| if (in_array($condition, [1, 2]) && $criteria['customer_zip_long'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['customer_zip_long']); | |
| // 过滤非number类型的 | |
| $arr = array_filter( | |
| $arr, | |
| function ($val) { | |
| return $val && is_numeric($val); | |
| } | |
| ); | |
| $builder->whereRaw('length(orders.customer_zip) in (?)', empty($arr) ? '-1' : implode(',', $arr)); | |
| } | |
| if ($condition == 3) { | |
| $builder->whereRaw('length(orders.customer_zip) = 0'); | |
| } | |
| } | |
| if (isset($criteria['title'])) { | |
| if (!$leftJoinItem) { | |
| $builder->leftJoin('order_items', 'orders.id', '=', 'order_items.order_id'); | |
| $leftJoinItem = true; | |
| } | |
| if ($condition == 1 && $criteria['title'] != '') { | |
| $arr = explode(PHP_EOL, $criteria['title']); | |
| $builder->whereIn('order_items.title', $arr)->distinct('orders.id'); | |
| } | |
| if ($condition == 2 && $criteria['title'] != '') { | |
| $builder->where('order_items.title', 'like', '%' . $criteria['title'] . '%')->distinct('orders.id'); | |
| } | |
| if ($condition == 3) { | |
| $builder->where('order_items.title', '=', '')->distinct('orders.id'); | |
| } | |
| } | |
| // 店铺id检索 | |
| if (isset($criteria['shop_ids']) && !empty($criteria['shop_ids'])) { | |
| $shopIds = is_array($criteria['shop_ids']) ? $criteria['shop_ids'] : explode(',', $criteria['shop_ids']); | |
| $builder->whereIn('orders.shop_id', array_filter($shopIds)); | |
| } | |
| // 2023/12/23 增加自定义 分类设置 | |
| if (isset($criteria['setting_tags']) && !empty($criteria['setting_tags']) && is_array($criteria['setting_tags'])) { | |
| // 异常已处理 handled | |
| // 异常未处理 unhandled | |
| // 正常分类 normal | |
| $typeArr = []; | |
| $handleArr = []; | |
| if (in_array('handled', $criteria['setting_tags'])) { | |
| $handleArr[] = 1; | |
| $typeArr[] = 2; | |
| } | |
| if(in_array('unhandled', $criteria['setting_tags'])) { | |
| $handleArr[] = 0; | |
| $typeArr[] = 2; | |
| } | |
| if (in_array('normal', $criteria['setting_tags'])) { | |
| $typeArr[] = 1; | |
| } | |
| $type = count($typeArr) == 1 ? reset($typeArr) : null; | |
| $is_handle = count($handleArr) == 1 ? reset($handleArr) : null; | |
| //固定分类检索 | |
| $systemTagsFlag = isset($criteria['system_tags']) && !empty($criteria['system_tags']); | |
| $systemTagsReverseFlag = isset($criteria['no_system_tags']) && !empty($criteria['no_system_tags']); | |
| $system_tags_union = ($criteria['system_tags_set'] ?? 'union') == 'union'; | |
| $systemTagIds = $excludeSystemTagIds = []; | |
| if ($systemTagsFlag) { | |
| $systemTagIds = is_array($criteria['system_tags']) ? $criteria['system_tags'] : explode(',', $criteria['system_tags']); | |
| } | |
| if ($systemTagsReverseFlag) { | |
| $excludeSystemTagIds = is_array($criteria['no_system_tags']) ? $criteria['no_system_tags'] : explode(',', $criteria['no_system_tags']); | |
| } | |
| if (!$systemTagsFlag && $systemTagsReverseFlag && count($excludeSystemTagIds) == 1){ | |
| $system_tags_union = false;//只有一个反选时，取交集intersection | |
| } | |
| // 固定分类 后面优化为横表检索 默认取交集 | |
| if ($systemTagIds && $excludeSystemTagIds) { | |
| //并集 | |
| if ($system_tags_union){ | |
| $builder->where( | |
| function ($query) use ($systemTagIds, $excludeSystemTagIds, $type, $is_handle) { | |
| $query->whereIn( | |
| 'orders.id', | |
| function ($query) use ($systemTagIds, $type, $is_handle) { | |
| $query->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags omt1 where omt1.multiple_tag_id in (%s) and is_system = 1 %s %s', | |
| implode(',', $systemTagIds), | |
| !is_null($type) ? 'and type = ' . $type : '', | |
| !is_null($is_handle) ? 'and is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| ); | |
| } | |
| ); | |
| $query->orWhereNotIn( | |
| 'orders.id', | |
| function ($query) use ($excludeSystemTagIds, $type, $is_handle) { | |
| $query->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags omt2 where omt2.multiple_tag_id in (%s) and is_system = 1 %s %s', | |
| implode(',', $excludeSystemTagIds), | |
| !is_null($type) ? 'and type = ' . $type : '', | |
| !is_null($is_handle) ? 'and is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| ); | |
| } | |
| ); | |
| } | |
| ); | |
| }else{ | |
| //交集 | |
| $builder->where( | |
| function ($query) use ($systemTagIds, $excludeSystemTagIds, $type, $is_handle) { | |
| $query->whereIn( | |
| 'orders.id', | |
| function ($query) use ($systemTagIds, $type, $is_handle) { | |
| $query->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags omt1 where omt1.multiple_tag_id in (%s) and is_system = 1 %s %s', | |
| implode(',', $systemTagIds), | |
| !is_null($type) ? 'and type = ' . $type : '', | |
| !is_null($is_handle) ? 'and is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| )->groupBy('order_id')->havingRaw(sprintf('COUNT(*) = %s', count($systemTagIds))); | |
| } | |
| ); | |
| $query->whereNotIn( | |
| 'orders.id', | |
| function ($query) use ($excludeSystemTagIds, $type, $is_handle) { | |
| $query->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags omt2 where omt2.multiple_tag_id in (%s) and is_system = 1 %s %s', | |
| implode(',', $excludeSystemTagIds), | |
| !is_null($type) ? 'and type = ' . $type : '', | |
| !is_null($is_handle) ? 'and is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| ); | |
| } | |
| ); | |
| } | |
| ); | |
| } | |
| } elseif ($systemTagIds) { | |
| //并集 | |
| if ($system_tags_union){ | |
| $builder->whereIn( | |
| 'orders.id', | |
| function ($query) use ($systemTagIds, $type, $is_handle) { | |
| $query->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags omt1 where omt1.multiple_tag_id in (%s) and is_system = 1 %s %s', | |
| implode(',', $systemTagIds), | |
| !is_null($type) ? 'and type = ' . $type : '', | |
| !is_null($is_handle) ? 'and is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| ); | |
| } | |
| ); | |
| }else{ | |
| //交集 | |
| $builder->whereIn( | |
| 'orders.id', | |
| function ($query1) use ($systemTagIds, $type, $is_handle) { | |
| $query1->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags omt1 where omt1.multiple_tag_id in (%s) and is_system = 1 %s %s', | |
| implode(',', $systemTagIds), | |
| !is_null($type) ? 'and type = ' . $type : '', | |
| !is_null($is_handle) ? 'and is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| )->groupBy('order_id')->havingRaw(sprintf('COUNT(*) = %s', count($systemTagIds))); | |
| } | |
| ); | |
| } | |
| } elseif ($excludeSystemTagIds) { | |
| if($system_tags_union){ | |
| //并集 | |
| $builder->leftJoin('order_multiple_tags', 'orders.id', '=', 'order_multiple_tags.order_id') | |
| ->whereNotIn('order_multiple_tags.multiple_tag_id', $excludeSystemTagIds)->distinct('orders.id'); | |
| if ($type) { | |
| $builder->where('order_multiple_tags.type', '=', $type); | |
| } | |
| if ($is_handle) { | |
| $builder->where('order_multiple_tags.is_handle', '=', $is_handle); | |
| } | |
| $builder->where('order_multiple_tags.is_system', '=', 1)->distinct('orders.id'); | |
| $leftJoinTag = true; | |
| }else{ | |
| //交集 | |
| $builder->whereNotIn( | |
| 'orders.id', | |
| function ($query) use ($excludeSystemTagIds, $type, $is_handle) { | |
| $query->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags where multiple_tag_id in (%s) and is_system = 1 %s %s', | |
| implode(',', $excludeSystemTagIds), | |
| !is_null($type) ? 'and type = ' . $type : '', | |
| !is_null($is_handle) ? 'and is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| ); | |
| } | |
| ); | |
| } | |
| } | |
| // 自定义分类交集并集检索 | |
| $customTagsFlag = isset($criteria['custom_tags']) && !empty($criteria['custom_tags']); | |
| $customTagsReverseFlag = isset($criteria['no_custom_tags']) && !empty($criteria['no_custom_tags']); | |
| $customTagIds = $excludeCustomTagIds = []; | |
| $custom_tags_set = ($criteria['custom_tags_set'] ?? 'union') == 'union'; | |
| if ($customTagsFlag) { | |
| $customTagIds = is_array($criteria['custom_tags']) ? $criteria['custom_tags'] : explode(',', $criteria['custom_tags']); | |
| } | |
| if ($customTagsReverseFlag) { | |
| $excludeCustomTagIds = is_array($criteria['no_custom_tags']) ? $criteria['no_custom_tags'] : explode(',', $criteria['no_custom_tags']); | |
| } | |
| if (!$customTagsFlag && $customTagsReverseFlag && count($excludeCustomTagIds) == 1){ | |
| $custom_tags_set = false;//只有一个反选时，取交集intersection | |
| } | |
| if ($customTagIds && $excludeCustomTagIds) { | |
| // 并集 | |
| if ($custom_tags_set) { | |
| $builder->where( | |
| function ($query) use ($customTagIds, $excludeCustomTagIds, $type, $is_handle) { | |
| $query->whereIn( | |
| 'orders.id', | |
| function ($query) use ($customTagIds, $type, $is_handle) { | |
| $query->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags left join multiple_tags on | |
| order_multiple_tags.multiple_tag_id= multiple_tags.id | |
| where order_multiple_tags.multiple_tag_id in (%s) | |
| and order_multiple_tags.is_system = 0 %s', | |
| implode(',', $customTagIds), | |
| 'and (multiple_tags.type = 1 or (multiple_tags.type = 2 and order_multiple_tags.is_handle=0) or (multiple_tags.type = 2 and order_multiple_tags.is_handle=1 and multiple_tags.hide_after_handled=0)) ', | |
| // !is_null($type) ? 'and order_multiple_tags.type = ' . $type : '', | |
| // ($is_handle == 0) ? 'and order_multiple_tags.is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| ); | |
| } | |
| ); | |
| $query->orWhereNotIn( | |
| 'orders.id', | |
| function ($query) use ($excludeCustomTagIds, $type, $is_handle) { | |
| $query->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags left join multiple_tags on | |
| order_multiple_tags.multiple_tag_id= multiple_tags.id | |
| where order_multiple_tags.multiple_tag_id in (%s) and order_multiple_tags.is_system = 0 %s', | |
| implode(',', $excludeCustomTagIds), | |
| 'and (multiple_tags.type = 1 or (multiple_tags.type = 2 and order_multiple_tags.is_handle=0) or (multiple_tags.type = 2 and order_multiple_tags.is_handle=1 and multiple_tags.hide_after_handled=0)) ', | |
| // !is_null($type) ? 'and order_multiple_tags.type = ' . $type : '', | |
| // ($is_handle==0) ? 'and order_multiple_tags.is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| ); | |
| } | |
| ); | |
| } | |
| ); | |
| } else { | |
| // 交集 | |
| $builder->where( | |
| function ($query) use ($customTagIds, $excludeCustomTagIds, $type, $is_handle) { | |
| $query->whereIn( | |
| 'orders.id', | |
| function ($query) use ($customTagIds, $type, $is_handle) { | |
| $query->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags left join multiple_tags on | |
| order_multiple_tags.multiple_tag_id= multiple_tags.id | |
| where order_multiple_tags.multiple_tag_id in (%s) and order_multiple_tags.is_system = 0 %s', | |
| implode(',', $customTagIds), | |
| 'and (multiple_tags.type = 1 or (multiple_tags.type = 2 and order_multiple_tags.is_handle=0) or (multiple_tags.type = 2 and order_multiple_tags.is_handle=1 and multiple_tags.hide_after_handled=0)) ', | |
| // !is_null($type) ? 'and order_multiple_tags.type = ' . $type : '', | |
| // ($is_handle == 0) ? 'and order_multiple_tags.is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| )->groupBy('order_multiple_tags.order_id')->havingRaw(sprintf('COUNT(*) = %s', count($customTagIds))); | |
| } | |
| ); | |
| $query->whereNotIn( | |
| 'orders.id', | |
| function ($query) use ($excludeCustomTagIds, $type, $is_handle) { | |
| $query->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags left join multiple_tags on | |
| order_multiple_tags.multiple_tag_id= multiple_tags.id | |
| where order_multiple_tags.multiple_tag_id in (%s) and order_multiple_tags.is_system = 0 %s', | |
| implode(',', $excludeCustomTagIds), | |
| 'and (multiple_tags.type = 1 or (multiple_tags.type = 2 and order_multiple_tags.is_handle=0) or (multiple_tags.type = 2 and order_multiple_tags.is_handle=1 and multiple_tags.hide_after_handled=0)) ', | |
| // !is_null($type) ? 'and order_multiple_tags.type = ' . $type : '', | |
| // !is_null($is_handle) ? 'and order_multiple_tags.is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| ); | |
| } | |
| ); | |
| } | |
| ); | |
| } | |
| } elseif ($customTagIds) { | |
| // 并集 | |
| if ($custom_tags_set) { | |
| $builder->whereIn( | |
| 'orders.id', | |
| function ($query) use ($customTagIds, $type, $is_handle) { | |
| $query->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags left join multiple_tags on | |
| order_multiple_tags.multiple_tag_id= multiple_tags.id | |
| where order_multiple_tags.multiple_tag_id in (%s) and order_multiple_tags.is_system = 0 %s', | |
| implode(',', $customTagIds), | |
| 'and (multiple_tags.type = 1 or (multiple_tags.type = 2 and order_multiple_tags.is_handle=0) or (multiple_tags.type = 2 and order_multiple_tags.is_handle=1 and multiple_tags.hide_after_handled=0)) ', | |
| // !is_null($type) ? 'and order_multiple_tags.type = ' . $type : '', | |
| // !is_null($is_handle) ? 'and order_multiple_tags.is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| ); | |
| } | |
| ); | |
| } else { | |
| // 交集 | |
| $builder->whereIn( | |
| 'orders.id', | |
| function ($query1) use ($customTagIds, $type, $is_handle) { | |
| $query1->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags left join multiple_tags on | |
| order_multiple_tags.multiple_tag_id= multiple_tags.id | |
| where order_multiple_tags.multiple_tag_id in (%s) and order_multiple_tags.is_system = 0 %s', | |
| implode(',', $customTagIds), | |
| 'and (multiple_tags.type = 1 or (multiple_tags.type = 2 and order_multiple_tags.is_handle=0) or (multiple_tags.type = 2 and order_multiple_tags.is_handle=1 and multiple_tags.hide_after_handled=0)) ', | |
| // !is_null($type) ? 'and order_multiple_tags.type = ' . $type : '', | |
| // !is_null($is_handle) ? 'and order_multiple_tags.is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| )->groupBy('order_multiple_tags.order_id')->havingRaw(sprintf('COUNT(*) = %s', count($customTagIds))); | |
| } | |
| ); | |
| } | |
| } elseif ($excludeCustomTagIds) { | |
| // 并集 | |
| if ($custom_tags_set) { | |
| $leftJoinTag = $leftJoinTag ?? false; | |
| if (!$leftJoinTag){ | |
| $builder->leftJoin('order_multiple_tags', 'orders.id', '=', 'order_multiple_tags.order_id'); | |
| } | |
| $builder->leftJoin('multiple_tags', 'order_multiple_tags.multiple_tag_id', '=', 'multiple_tags.id'); | |
| $builder->whereNotIn('order_multiple_tags.multiple_tag_id', $excludeCustomTagIds); | |
| // if ($type) { | |
| // $builder->where('order_multiple_tags.type', '=', $type); | |
| // } | |
| // if ($is_handle == 0) { | |
| // $builder->where('order_multiple_tags.is_handle', '=', $is_handle); | |
| // } | |
| $builder->where(function ($query) { | |
| $query->where('multiple_tags.type', '=', 1) | |
| ->orWhere(function ($query1) { | |
| $query1->where('multiple_tags.type', '=', 2) | |
| ->where('order_multiple_tags.is_handle', '=', 0); | |
| }) | |
| ->orWhere(function ($query2) { | |
| $query2->where('multiple_tags.type', '=', 2) | |
| ->where('order_multiple_tags.is_handle', '=', 1) | |
| ->where('multiple_tags.hide_after_handled', '=', 0); | |
| }); | |
| }); | |
| $builder->where('order_multiple_tags.is_system', '=', 0); | |
| } else { | |
| // 交集 | |
| $builder->whereNotIn( | |
| 'orders.id', | |
| function ($query) use ($excludeCustomTagIds, $type, $is_handle) { | |
| $query->selectRaw( | |
| sprintf( | |
| 'order_id from order_multiple_tags left join multiple_tags on order_multiple_tags.multiple_tag_id= multiple_tags.id | |
| where order_multiple_tags.multiple_tag_id in (%s) and order_multiple_tags.is_system = 0 %s ', | |
| implode(',', $excludeCustomTagIds), | |
| 'and (multiple_tags.type = 1 or (multiple_tags.type = 2 and order_multiple_tags.is_handle=0) or (multiple_tags.type = 2 and order_multiple_tags.is_handle=1 and multiple_tags.hide_after_handled=0)) ', | |
| // !is_null($type) ? 'and order_multiple_tags.type = ' . $type : '', | |
| // ($is_handle == 0) ? 'and order_multiple_tags.is_handle = ' . $is_handle : '', | |
| ), | |
| [] | |
| ); | |
| } | |
| ); | |
| } | |
| } | |
| $builder->distinct('orders.id'); | |
| } | |
| // 物流渠道检索 | |
| if (isset($criteria['logistic_channels']) && $criteria['logistic_channels'] != '') { | |
| $logisticChannelIds = explode(',', $criteria['logistic_channels']); | |
| $builder->whereIn('orders.logistic_channel_id', $logisticChannelIds); | |
| } | |
| // 时间类型条件检索 | |
| if (isset($criteria['time_field']) && !empty($criteria['start_time']) && !empty($criteria['end_time'])) { | |
| $builder->whereBetween( | |
| 'orders.' . $criteria['time_field'], | |
| [$criteria['start_time'], $criteria['end_time']] | |
| ); | |
| } | |
| // sku id检索 | |
| if (isset($criteria['sku_ids']) && $criteria['sku_ids'] != '') { | |
| $skuIds = explode(',', $criteria['sku_ids']); | |
| $skuIds = array_filter(array_unique($skuIds)); | |
| if (!$leftJoinItem) { | |
| $builder->leftJoin('order_items', 'orders.id', '=', 'order_items.order_id'); | |
| $leftJoinItem = true; | |
| } | |
| $builder->whereIn('order_items.sku_id', $skuIds)->distinct('orders.id'); | |
| } | |
| // sku code批量检索 | |
| if (isset($criteria['sku_codes'])) { | |
| if (!$leftJoinItem) { | |
| $builder->leftJoin('order_items', 'orders.id', '=', 'order_items.order_id'); | |
| $leftJoinItem = true; | |
| } | |
| $skuCodes = explode(PHP_EOL, $criteria['sku_codes']); | |
| $this->transformAndLoadBuilderByMatch($builder, $condition, 'order_items.sku_code', $skuCodes); | |
| } | |
| // spu code批量检索 | |
| if (isset($criteria['spu_codes'])) { | |
| if (!$leftJoinItem) { | |
| $builder->leftJoin('order_items', 'orders.id', '=', 'order_items.order_id'); | |
| $leftJoinItem = true; | |
| } | |
| if ($condition == 1 && $criteria['spu_codes'] != '') { | |
| $spuCodes = explode(PHP_EOL, $criteria['spu_codes']); | |
| $builder->leftJoin('skus', 'order_items.sku_id', '=', 'skus.id'); | |
| $builder->leftJoin('spus', 'skus.spu_id', '=', 'spus.id') | |
| ->whereIn('spus.spu_code', array_filter(array_unique($spuCodes)))->distinct('orders.id'); | |
| } | |
| if ($condition == 2 && $criteria['spu_codes'] != '') { | |
| $builder->leftJoin('skus', 'order_items.sku_id', '=', 'skus.id'); | |
| $builder->leftJoin('spus', 'skus.spu_id', '=', 'spus.id') | |
| ->where('spus.spu_code', 'like', '%' . $criteria['spu_codes'] . '%')->distinct('orders.id'); | |
| } | |
| if ($condition == 3) { | |
| $builder->leftJoin('skus', 'order_items.sku_id', '=', 'skus.id') | |
| ->whereNull('skus.spu_id')->distinct('orders.id'); | |
| } | |
| } | |
| // 店铺域名 | |
| if (isset($criteria['shop_domain'])) { | |
| if ($condition == 1 && $criteria['shop_domain'] != '') { | |
| $domains = explode(PHP_EOL, $criteria['shop_domain']); | |
| $builder->leftJoin('shops', 'orders.shop_id', '=', 'shops.id') | |
| ->whereIn('shops.code_url', array_unique($domains))->distinct('orders.id'); | |
| } | |
| if ($condition == 2) { | |
| $builder->leftJoin('shops', 'orders.shop_id', '=', 'shops.id') | |
| ->where('shops.code_url', 'like', '%' . $criteria['shop_domain'] . '%')->distinct('orders.id'); | |
| } | |
| if ($condition == 3) { | |
| $builder->leftJoin('shops', 'orders.shop_id', '=', 'shops.id') | |
| ->whereNull('shops.code_url')->distinct('orders.id'); | |
| } | |
| } | |
| // 平台检索 | |
| if (isset($criteria['shop_plat_id']) && $criteria['shop_plat_id'] != '') { | |
| $builder->leftJoin('shops', 'orders.shop_id', '=', 'shops.id') | |
| ->where('shops.shop_plat_id', '=', $criteria['shop_plat_id'])->distinct('orders.id'); | |
| } | |
| // 订单物流属性检索 | |
| if (isset($criteria['order_logistics']) && $criteria['order_logistics'] != '') { | |
| $logisticIds = explode(',', $criteria['order_logistics']); | |
| $builder->leftJoin('order_logistics_features', 'orders.id', '=', 'order_logistics_features.order_id') | |
| ->whereIn('order_logistics_features.logistics_feature_id', $logisticIds)->distinct('orders.id'); | |
| } | |
| // 订单商品物流属性检索 | |
| if (isset($criteria['order_item_logistics']) && !empty($criteria['order_item_logistics'])) { | |
| $itemLogisticIds = is_array($criteria['order_item_logistics']) ? $criteria['order_item_logistics'] : explode(',',$criteria['order_item_logistics']); | |
| $builder->leftJoin( | |
| 'order_item_logistics_features', | |
| 'orders.id', | |
| '=', | |
| 'order_item_logistics_features.order_id' | |
| ) | |
| ->whereIn('order_item_logistics_features.logistics_feature_id', $itemLogisticIds) | |
| ->distinct('orders.id'); | |
| // // 优化sql写法 直接连表改为连接子查询 | |
| // $subQuery = OrderItemLogisticsFeature::query()->select('order_id')->whereIn('logistics_feature_id', $itemLogisticIds) | |
| // ->groupBy('order_id'); | |
| // $builder->whereIn('orders.id', $subQuery); | |
| } | |
| // if (isset($depths['list']) && $depths['list'] == 1) { | |
| // $with[] = 'systemTagsEnable'; | |
| // } | |
| // 过滤作废商品 | |
| if ($leftJoinItem) { | |
| // $builder->where('order_items.is_discard', '=', 0); | |
| } | |
| if (!empty($with)) { | |
| $builder->with($with); | |
| } | |
| if (!isset($depths['no_group'])) { | |
| $builder->groupBy('orders.id'); | |
| } | |
| if (($criteria['sortByOrderNo'] ?? false) && empty($criteria['sort'])){ | |
| $builder->orderByRaw(DB::raw("FIELD(orders.plat_order_no, '" . implode("','", array_unique($criteria['plat_order_no']['value'])) . "')")); | |
| $depths['no_page_sort'] = true; | |
| } | |
| // 增加可选参数 是否要使用页面排序 因为全部转入发货中操作 需要使用chunkById 要使用id asc排序 | |
| if (!isset($depths['no_page_sort'])) { | |
| $builder->allowedSorts( | |
| [ | |
| AllowedSort::field('payment_at_gmt', 'payment_at_timezone'), | |
| 'created_at_gmt', | |
| 'delivery_at_gmt', | |
| 'delivered_at_gmt', | |
| 'transport_at_gmt', | |
| 'total_amount_cny', | |
| 'abnormal_at_gmt', | |
| 'repeal_at_gmt' | |
| ] | |
| )->defaultSort('-id'); | |
| } | |
| return $builder; | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment