baoshi-was/app/Filters/OrderPackageFilters.php

<?php


namespace App\Filters;

use App\OracleDOCOrderHeader;
use App\Order;
use App\OrderIssue;
use App\Services\UserService;
use App\Traits\ModelSearchWay;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use phpDocumentor\Reflection\Types\Boolean;

class OrderPackageFilters
{
    use ModelSearchWay;
    protected $request;
    /** @var $queryBuilder Builder */
    protected $queryBuilder;
    protected $filters = [
        'logistic_number',//快递单号
        'status',//状态
        'received_at_start',//签收时间开始
        'default_logistics',//是否默认承运商
        'is_issue',//是否为问题件
        'sync_routes_flag',//是否同步过路由
        'remark',//备注
        'received_at_end',//签收时间截止
        'is_weighed',//是否称重
        'logistic',//承运商
        'owner',//货主
        'warehouse',//货主
        'sent_at_start',//复核时间开始
        'created_at_start',//创建时间开始
        'created_at_end',//创建时间开始
        'sent_at_end',//复核时间截止
        'default_date',//默认日期
        'has_transfer_status',//是否有路由信息
        'is_new_rejecting',//退件状态
        'result_explain',//情况说明
        'issue_type_name',//问题件类型
        'order_notes',//订单备注
        'exception_status',//订单备注
        'is_customer_service_remark',//是否有客服备注
        'customer_service_remark_created_at_start',//客服备注起始时间
        'customer_service_remark_created_at_end',//客服备注截止时间
        'virtual_receive',//虚拟揽收
    ];

    protected $orderIssueQuery;
    protected $orderQuery;

    public function __construct(Request $request)
    {
        $this->request = $request;
    }

    public function apply($builder)
    {
        $this->queryBuilder = $builder;
        $filters = array_filter($this->request->only($this->filters), function ($item) {
            return $item !== null;
        });
        $this->queryBuilder
            ->whereIn('status', [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, null])
            ->whereIn('exception_status', [0, 1, 2, 3, 4, 5, 6, 7, null]);
        if(array_key_exists("sent_at_end",$filters) || array_key_exists("sent_at_start",$filters)){
            $this->queryBuilder->from(DB::raw("`order_packages` force index (order_packages_sent_at_index)"));
        }
        $this->getOrderQuery();

        foreach ($filters as $filter => $value) {
            if (method_exists($this, $filter)) {
                $this->$filter($value, $this->queryBuilder);
            }
        }
        $this->after();
        return $this->queryBuilder;
    }

    public function after(){
        if($this->orderQuery){
            $this->queryBuilder->whereIn('order_id',$this->orderQuery);
        }
    }

    public function getOrderQuery(): Builder
    {
        if (!$this->orderQuery) {
            $this->orderQuery = Order::query()->select('id')->where('wms_status', '!=','订单取消');
        }
        return $this->orderQuery;
    }

    public function getOrderIssueQuery(): Builder
    {
        if (!$this->orderIssueQuery) {
            $this->orderIssueQuery = OrderIssue::query()->select('order_id');
        }
        return $this->orderIssueQuery;
    }


    private function logistic_number($logistic_number)
    {
        if (strpos($logistic_number, ',') || strpos($logistic_number, '，') || strpos($logistic_number, ' ')) {
            $arr = array_filter(preg_split('/[,， ]+/is', $logistic_number));
            $this->queryBuilder->whereIn('logistic_number', $arr);
        } else {
            $this->queryBuilder->where('logistic_number', 'like', $logistic_number . "%");
        }
    }


    private function has_transfer_status($has_transfer_status)
    {
        if ($has_transfer_status == '是') {
            $this->queryBuilder->whereNotNull('order_package_express_route_id');
        } elseif ($has_transfer_status == '否') {
            $this->queryBuilder->where(function ($query) {
                $query->whereNull('order_package_express_route_id');
            });
        }
    }

    private function status($status)
    {
        $statuses = array_filter(preg_split('/[,， ]+/is', $status), function ($item) {
            return $item !== null;
        });
        $this->queryBuilder->whereIn('status', $statuses);
    }

    private function received_at_start($received_at_start)
    {
        $this->queryBuilder->where('received_at', '>=', $received_at_start);
    }

    private function received_at_end($received_at_end)
    {
        $this->queryBuilder->where('received_at', '<=', $received_at_end);
    }

    private function is_weighed($is_weighed)
    {
        if ($is_weighed == 'true') {
            $this->queryBuilder->whereNotNull('weighed_at');
        } else {
            $this->queryBuilder->whereNull('weighed_at');
        }
    }

    private function logistic($logistic_id)
    {
        $logistic_ids = array_filter(preg_split('/[,， ]+/is', $logistic_id));
        $this->getOrderQuery()->whereIn('logistic_id',$logistic_ids);
    }

    private function owner($owner_id)
    {
        $owner_ids = array_filter(preg_split('/[,， ]+/is', $owner_id));
        $this->queryBuilder->whereIn('owner_id', $owner_ids);
    }

    private function sent_at_start($sent_at_start)
    {
        $this->queryBuilder->where('sent_at', '>=', Carbon::parse($sent_at_start)->startOfDay());
    }

    private function sent_at_end($sent_at_end)
    {
        $this->queryBuilder->where('sent_at', '<=', Carbon::parse($sent_at_end)->endOfDay());
    }

    private function created_at_start($created_at_start)
    {
        $this->queryBuilder->where('created_at', '>=', Carbon::parse($created_at_start)->startOfDay());
    }

    private function created_at_end($created_at_end)
    {
        $this->queryBuilder->where('created_at', '<=', Carbon::parse($created_at_end)->endOfDay());
    }

    public function default_date()
    {
        $this->queryBuilder->where('created_at', '>=', now()->startOfDay()->subDays(15))
            ->where('created_at', '<', now()->startOfDay()->addDay());
    }

    public function default_logistics()
    {
        $this->getOrderQuery()->whereNotIn('logistic_id', [13, 24, 25, 26, 27, 30, 31, 32, 33, 34, 60, 63, 65, 66, 68, 70, 102, 105, 107, 116, 10]);
    }

    public function is_issue($is_issue)
    {
        if ($is_issue === '是') {
            $this->queryBuilder->whereIn('order_id', function ($query) {
                $query->from('order_issues')->select('order_id')->whereNotNull('order_id');
            });
        } else {
            $this->queryBuilder->whereNotIn('order_id', function ($query) {
                $query->from('order_issues')->select('order_id')->whereNotNull('order_id');
            });
        }
    }

    public function sync_routes_flag($sync_routes_flag)
    {
        $this->queryBuilder->where('sync_routes_flag', $sync_routes_flag === 'true');
    }

    public function remark($remark)
    {
        $this->queryBuilder->whereIn('id', function ($query) use ($remark) {
            $query->from('order_package_remarks')->select('order_package_id')->where('content', 'like', $remark);
        });
    }

    public function is_new_rejecting($is_new_rejecting)
    {
        if ($is_new_rejecting === '有') {
            $this->queryBuilder->whereIn('logistic_number', function ($query) {
                $query->from('rejected_bills')->select('logistic_number_return')->where('logistic_number', '原单退回')->whereNotNull('logistic_number_return');
            });
        }
        if ($is_new_rejecting === '无') {
            $this->queryBuilder->whereNotIn('logistic_number', function ($query) {
                $query->from('rejected_bills')->select('logistic_number_return')->where('logistic_number', '原单退回')->whereNotNull('logistic_number_return');
            });
        }
    }

    public function issue_type_name($issue_type_name)
    {
        $this->queryBuilder->whereIn('order_id', function ($query) use ($issue_type_name) {
            $query->from('order_issues')->select('order_id')->where('order_issue_type_id', function ($query) use ($issue_type_name) {
                $query->from('order_issue_types')->select('id')->where('name', $issue_type_name);
            });
        });
    }

    public function result_explain($result_explain)
    {
        $this->queryBuilder->whereIn('order_id', function ($query) use ($result_explain) {
            $query->from('order_issues')->select('order_id')->where('result_explain', 'like', $result_explain);
        });
    }

    public function order_notes($order_notes)
    {
        $orderNos = OracleDOCOrderHeader::query()->select('orderno')->where('notes', 'like', $order_notes)->pluck('orderno');
        $this->getOrderQuery()->whereIn('code',$orderNos);
    }

    public function exception_status($exception_status)
    {
        $arr = array_filter(preg_split('/[,， ]+/is', $exception_status));
        $this->queryBuilder->whereIn('exception_status', $arr);
    }

    public function is_customer_service_remark($is_customer_service_remark)
    {
        if ($is_customer_service_remark === '是') {
            $this->queryBuilder->has('orderPackageRemarks');
        } else if ($is_customer_service_remark === '否') {
            $this->queryBuilder->doesntHave('orderPackageRemarks');
        }
    }

    public function customer_service_remark_created_at_start($customer_service_remark_created_at_start)
    {
        $this->queryBuilder->whereIn('id', function ($query) use ($customer_service_remark_created_at_start) {
            return $query->from('order_package_remarks')->select('order_package_id')->where('created_at', '>=', $customer_service_remark_created_at_start);
        });
    }


    public function customer_service_remark_created_at_end($customer_service_remark_created_at_end)
    {
        $this->queryBuilder->whereIn('id', function ($query) use ($customer_service_remark_created_at_end) {
            return $query->from('order_package_remarks')->select('order_package_id')->where('created_at', '<=', $customer_service_remark_created_at_end);
        });
    }

    public function warehouse($warehouseId){
        $this->searchWay($this->getOrderQuery(),$warehouseId,'warehouse_id');
    }

    public function virtual_receive()
    {
        $this->queryBuilder->where('route_length', '>', 1)->where('route_length', '<', 3);
    }
}