baoshi-was/app/Services/ProcessService.php

<?php

namespace App\Services;

use App\Process;
use App\Services\common\QueryService;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

Class ProcessService
{
    private function conditionQuery(array $params){
        $processes=Process::filterAuthorities()->with(['tutorials','signs',
            'signUnitPrice','processesContents'=>function($query){
            return $query->with(['signCommodityName','signCommoditySku','signCommodityBarcode','commodity'=>function($query){
                return $query->with('barcodes');
            }]);
        }])->orderBy('processes.id','DESC');
        if ($params['commodity_barcode'] ?? false){
            $barcode=$params['commodity_barcode'];
            $processes=$processes->whereHas('processesContents',function ($query)use($barcode){
                $query->whereHas('commodity',function ($builder)use($barcode){
                    $builder->whereHas('barcodes',function ($builder)use($barcode){
                        $builder->where('code','like',$barcode);
                    });
                });
            });
            unset($params['commodity_barcode']);
        }
        if ($params['wms_code'] ?? false){
            $wms_code=$params['wms_code'];
            $processes=$processes->whereHas('processesContents',function ($builder)use ($wms_code){
                $builder->where('wms_code','like',$wms_code);
            });
            unset($params['wms_code']);
        }
        if (!($params['is_accomplish'] ?? false)){
            $processes=$processes->where('status','!=','交接完成');
        }else unset($params['is_accomplish']);
        $columnQueryRules=[
            'date_start' => ['alias' => 'created_at' , 'startDate' => ' 00:00:00'],
            'date_end' => ['alias' => 'created_at' , 'endDate' => ' 23:59:59'],
            'code' => ['like' => ''],
            'owner_id' => ['multi' => ','],
            'id' => ['multi' => ','],
        ];
        $processes = app(QueryService::class)->query($params,$processes,$columnQueryRules,"processes");
        return $processes;
    }

    public function paginate(array $params){
        return $this->conditionQuery($params)->paginate($request->paginate ?? 50);
    }

    public function get(array $params){
        return $this->conditionQuery($params)->get();
    }

    public function getSql(array $params){
        return $this->conditionQuery($params)->selectRaw('processes.code,processes.amount,processes.completed_amount,processes.status,processes.remark,processes.unit_price,processes.created_at')
            ->leftJoin('owners','processes.owner_id','owners.id')
                ->selectRaw('owners.name owner_name')
            ->leftJoin('process_methods','processes.process_method_id','process_methods.id')
                ->selectRaw('process_methods.name process_method_name')
            ->leftJoin('processes_contents','processes.id','processes_contents.process_id')
                ->selectRaw('processes_contents.bill_type content_bill_type,processes_contents.wms_code content_wms_code,processes_contents.amount content_amount')
            ->leftJoin('commodities','processes_contents.commodity_id','commodities.id')
                ->selectRaw('commodities.name commodity_name,commodities.sku commodity_sku')
            ->leftJoin('commodity_barcodes','commodities.id','commodity_barcodes.commodity_id')
                ->selectRaw(DB::raw("GROUP_CONCAT(commodity_barcodes.code) commodity_barcode_code"))
                ->groupBy("processes_contents.id","processes_contents.commodity_id")
            ->leftJoin('signs as sign_commodity_sku',function ($join){
                $join->on('sign_commodity_sku.signable_type',DB::raw("'processes_contents'"))
                    ->on('sign_commodity_sku.signable_id','processes_contents.id')
                    ->on('sign_commodity_sku.field',DB::raw("'commodity_sku'"));
            })
                ->selectRaw('sign_commodity_sku.mark sign_commodity_sku_mark')
            ->leftJoin('signs as sign_commodity_name',function ($join){
                $join->on('sign_commodity_name.signable_type',DB::raw("'processes_contents'"))
                    ->on('sign_commodity_name.signable_id','processes_contents.id')
                    ->on('sign_commodity_name.field',DB::raw("'commodity_name'"));
            })
                ->selectRaw('sign_commodity_name.mark sign_commodity_name_mark')
            ->leftJoin('signs as sign_commodity_barcode',function ($join){
                $join->on('sign_commodity_barcode.signable_type',DB::raw("'processes_contents'"))
                    ->on('sign_commodity_barcode.signable_id','processes_contents.id')
                    ->on('sign_commodity_barcode.field',DB::raw("'commodity_barcode'"));
            })
                ->selectRaw('sign_commodity_barcode.mark sign_commodity_barcode_mark')
            ->sql();
    }
}