baoshi-was/app/Http/Controllers/PackageController.php

<?php

namespace App\Http\Controllers;

use App\Events\WeighedEvent;
use App\Exports\Export;
use App\Logistic;
use App\OracleDOCOrderHeader;
use App\OracleDOCWaveDetails;
use App\Owner;
use App\Package;
use App\PaperBox;
use Box\Spout\Common\Helper\Escaper\XLSX;
use Box\Spout\Common\Type;
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Writer\Common\Creator\WriterFactory;
use Carbon\Carbon;
use Illuminate\Contracts\Foundation\Application;
use Illuminate\Http\RedirectResponse;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Routing\Redirector;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Gate;
use Illuminate\Support\Facades\Validator;
use Maatwebsite\Excel\Facades\Excel;
use Box\Spout\Common\Entity\Row;


class PackageController extends Controller
{
    //超15天精确查询抽离 cloumn前提：数据库字段名必须与request内字段名一致
    public function preciseQuery(string $column,Request $request,$packages){
        $today=Carbon::now()->subDays(15);
        $packagesTem=clone $packages;
        $packagesTem=$packagesTem->where($column,'like','%'.$request->input($column).'%')->where('created_at','>',$today->format('Y-m-d'));
        if($packagesTem->count()==0   //TODO: 注意：该get()[0]的查询方式在大数据量的情况下不适用，应clone一份做first判断 或干脆去除该条件
            ||$packagesTem->get()[0][$column]==$request->input($column)){
            $packages=$packages->where($column,$request->input($column));
        }else{
            $packages=$packagesTem;
        }
        return $packages;
    }

    public function conditionQuery(Request $request,$packages){
        if ($request->input('logistic_number')){
            $packages=$this->preciseQuery('logistic_number',$request,$packages);
        }
        /*        if ($request->input('delivery_number')){
                    $packages=$this->preciseQuery('delivery_number',$request,$packages);
                }*/
        if ($request->input('created_at_start')){
            $created_at_start=$request->input('created_at_start')." 00:00:00";
            $packages=$packages->where('created_at','>=',$created_at_start);
        }
        if ($request->input('created_at_end')){
            $created_at_end=$request->input('created_at_end')." 23:59:59";
            $packages=$packages->where('created_at','<=',$created_at_end);
        }
        if ($request->input('owner_id')){
            $packages=$packages->where('owner_id',$request->input('owner_id'));
        }
        if ($request->input('batch_number')){
            $packages=$this->preciseQuery('batch_number',$request,$packages);
        }
        return $packages;
    }

    /**
     * Display a listing of the resource.
     *
     * @return Response
     */
    public function index(Request $request)
    {
        if(!Gate::allows('称重管理-查询')){ return redirect(url('/'));  }
        if ($request->input()){
            $packages=Package::orderBy('id','DESC');
            $packages=$this->conditionQuery($request,$packages);
            $packages=$packages->paginate($request->input('paginate')?$request->input('paginate'):50);
            $owners=Owner::select('id','name')->get();
            return view('weight.package.index',['packages'=>$packages,'owners'=>$owners,'request'=>$request->input()]);
        }
        $packages=Package::orderBy('id','DESC')->paginate(50);
        $owners=Owner::select('id','name')->get();
        return view('weight.package.index',['packages'=>$packages,'owners'=>$owners]);
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return Response
     */
    public function create()
    {
        if(!Gate::allows('称重管理-录入')){ return redirect(url('/'));  }
        $paperBoxes=PaperBox::select('id','model')->get();
        return view('weight.package.create',['paperBoxes'=>$paperBoxes]);
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param Request $request
     * @return Application|RedirectResponse|Redirector
     */
    public function store(Request $request)
    {
        if(!Gate::allows('称重管理-录入')){ return redirect(url('/'));  }
        $this->validator($request)->validate();
        $logistic_number=$request->input('logistic_number');
        $weight=$request->input('weight');
        $batch_number=strtoupper($request->input('batch_number'));
        $order_code=$request->input('order_code');
        $paper_box_id=$request->input('paper_box_id');
//        $isSamePackBatch=$request->input('is_same_pack_batch');
        if($logistic_number&&$batch_number)return redirect('package/create')->with('successError','录入失败！波次号和快递单号只能填一项！');
        $package=null;
        $successTip = '操作成功';

        $accomplishToWMS=new Api\thirdPart\flux\PackageController();
        if($batch_number){
            $this->log(__METHOD__,'活动波次开始同步_'.__FUNCTION__,json_encode($package),Auth::user()['name']);
//            $this->syncBatch($package->batch_number,$weight,null,null,null,Carbon::now(),null);
            $result=$accomplishToWMS->markWMSOnBatch($batch_number,$weight);
            Package::createPackagesFromBatchCode($batch_number,$weight);
            if($package&&$package->isActivityBatch())
                $package->unifyThisMeasureUnderSameBatch();

            if ($result['result']){
                Controller::logS(__METHOD__,'SUCCESS_'.__FUNCTION__,'批量更改波次上传成功'.$batch_number);
                $successTip = "批量录入波次成功！波次号：$batch_number";
                return redirect('package/create')->with('successTip', $successTip);
            }
            Controller::logS(__METHOD__,'error_'.__FUNCTION__,'批量更改波次上传异常:'.$result['message'].$batch_number);
            return redirect('package/create')->with('successError','录入失败！'.$result['message']);
        }

        if ($order_code){
            $package=Package::where('order_code',$order_code)->first();
        }else{
            if($batch_number){
                $package=Package::where('batch_number',$batch_number)->first();
            }elseif($logistic_number){
                $package=Package::where('logistic_number',$logistic_number)->first();
            }
        }
        if (!$package && !$logistic_number)return redirect('package/create')->with('successError','录入失败！系统内没有对应波次的包裹！');
        if ($package){
            if(!$package->batch_number)$package->batch_number=$batch_number;
            if(!$package->order_code)$package->order_code=$order_code;
            $package->fetchAllFromOracle();
            $package->weight=$weight;
            if(!$package->paper_box_id)$package->paper_box_id=$paper_box_id;
            $result=$accomplishToWMS->accomplishToWMS($package);
            if ($result['result']){
                $package->status="已上传";
            }else{
                $package->status="上传异常";
            }
            $package->save();
            $this->log(__METHOD__,'create_'.__FUNCTION__,json_encode($package),Auth::user()['name']);
//            event(new WeighedEvent($package));
        }else{
            $package=new Package([
                'logistic_number'=>$logistic_number??'',
                'delivery_number'=>$logistic_number??'',
                'weight'=>$weight,
                'weighed_at'=>Carbon::now(),
                'status'=>"已上传",
            ]);
            $package->fetchAllFromOracle();
            $result=$accomplishToWMS->accomplishToWMS($package);
            $str = "录入包裹成功！";
            $str .= $package['logistic_number']?"快递单号：{$package['logistic_number']}":'';
            $str .= $package['order_code']?", 订单号：{$package['order_code']}":'';
            if(!$result||$result['result']=='false'){
                $package['status'] = '上传失败';
                $str = "录入包裹失败！";
                $str .= $package['logistic_number']?"快递单号：{$package['logistic_number']}":'';
                $str .= $package['order_code']?", 订单号：{$package['order_code']}":'';
                $this->log(__METHOD__,__FUNCTION__,json_encode($package).json_encode($result).$str,Auth::user()['name']);
            }
            $package->save();
            $successTip = $str;
        }
        return redirect('package/create')->with('successTip', $successTip);
    }


    public function statistics(Request $request){
        $packages=Package::select(DB::raw('owner_id,logistic_id,COUNT(logistic_id) AS count'))->whereNotNull('owner_id');
        if($request->input('owner_id')){
            $owners_id=$id = explode( ',',$request->input('owner_id'));
            $packages=$packages->whereIn('owner_id',$owners_id);
        }
        if($request->input('logistic_id')){
            $logistics_id=$id = explode( ',',$request->input('logistic_id'));
            $packages=$packages->whereIn('logistic_id',$logistics_id);
        }
        if($request->input('date_start')){
            $packages=$packages->where('created_at','>=',$request->input('date_start'));
        }
        if($request->input('date_end')){
            $packages=$packages->where('created_at','<=',$request->input('date_end'));
        }
        $packages=$packages->groupBy(['owner_id','logistic_id'])->get();
        $owners=Owner::get();
        $logistics=Logistic::get();
        if ($request->input('checkSign')){
            $logistics=$this->checkLogistic($packages);
            if ($request->input('checkSign')=="-1"){
                $excel=$this->statisticExport($packages,$owners,$logistics);
                return $excel;
            }
            $id=$id = explode( ',',$request->input('checkSign'));
            $packages=Package::select(DB::raw('owner_id,logistic_id,COUNT(logistic_id) AS count'))
                ->whereIn('owner_id',$id)->groupBy(['owner_id','logistic_id'])->get();
            $logistics=$this->checkLogistic($packages);
            $excel=$this->statisticExport($packages,$owners,$logistics);
            return $excel;

        }
        return view('weight.package.statistics',["packages"=>$packages,'owners'=>$owners,'logistics'=>$logistics]);
    }

    /*
     * 根据packages统计记录删选物流公司
     */
    public function checkLogistic($packages){
        $logisticIds=[];
        foreach ($packages as $package){
            $isLogistic=true;
            for ($i=0;$i<count($logisticIds);$i++){
                if ($package->logistic_id==$logisticIds[$i]){
                    $isLogistic=false;
                    break;
                }
            }
            if ($isLogistic){
                array_push($logisticIds,$package->logistic_id);
            }
        }
        $logistics=Logistic::whereIn('id',$logisticIds)->get();
        return $logistics;
    }

    /**
     * Display the specified resource.
     *
     * @param Package $packages
     * @return Response
     */
    public function show(Package $packages)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param Package $packages
     * @return Response
     */
    public function edit(Package $packages)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param Request $request
     * @param Package $packages
     * @return Response
     */
    public function update(Request $request, Package $packages)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param Package $packages
     * @return Response
     */
    public function destroy(Package $packages)
    {
        //
    }

    public function export($id,Request $request){
        if(!Gate::allows('称重管理-查询')){ return '没有权限';  }
        ini_set('max_execution_time',5500);
        ini_set('memory_limit','5526M');
        if ($id==-1){
            $id=[];
            $packages=Package::select('id');
            $packages=$this->conditionQuery($request,$packages);
            $packages=$packages->get();
            foreach ($packages as $package){
                array_push($id,$package->id);
            }
        }else $id = explode( ',',$id);
        if (!$id)return ;
        $row=[[
            'id'=>'ID',
            'owner_name'=>'货主',
            'logistic_number'=>'快递单号',
            'delivery_number'=>'发货单号',
            'batch_number'=>'波次号',
            'batch_rule'=>'波次规则',
            'created_at'=>'操作时间',
            'recipient'=>'收件人',
            'recipient_mobile'=>'收件人电话',
            'logistic_name'=>'承运商',
            'measuringMachine_name'=>'设备',
            'weight'=>'重量(KG)',
            'length'=>'长(CM)',
            'width'=>'宽(CM)',
            'height'=>'高(CM)',
            'bulk'=>'体积(DM³)',
            'paperBox_name'=>'纸箱',
            'status'=>'状态',
        ]];
        $list=[];
        for ($i=0; $i<count($id);$i++){
            $package=Package::find($id[$i]);
            $w=[
                'id'=>isset($package->id)?$package->id:'',
                'owner_name'=>isset($package->owner_name)?$package->owner_name:'',
                'logistic_number'=>isset($package->logistic_number)?$package->logistic_number:'',
                'delivery_number'=>isset($package->delivery_number)?$package->delivery_number:'',
                'batch_number'=>isset($package->batch_number)?$package->batch_number:'',
                'batch_rule'=>isset($package->batch_rule)?$package->batch_rule:'',
                'created_at'=>isset($package->created_at)?$package->created_at:'',
                'recipient'=>isset($package->recipient)?$package->recipient:'',
                'recipient_mobile'=>isset($package->recipient_mobile)?$package->recipient_mobile:'',
                'logistic_name'=>isset($package->logistic_name)?$package->logistic_name:'',
                'measuringMachine_name'=>isset($package->measuringMachine_name)?$package->measuringMachine_name:'',
                'weight'=>isset($package->weight)?$package->weight:'',
                'length'=>isset($package->length)?$package->length/10:'',
                'width'=>isset($package->width)?$package->width/10:'',
                'height'=>isset($package->height)?$package->height/10:'',
                'bulk'=>isset($package->bulk)?round($package->bulk/10000)/100:'',//立方毫米到立方分米带双小数
                'paperBox_name'=>isset($package->paperBox_name)?$package->paperBox_name:'',
                'status'=>isset($package->status)?$package->status:''
            ];
            $list[$i]=$w;
        }
        return Excel::download(new Export($row,$list),date('YmdHis', time()).'-称重记录单.xlsx');
    }
    protected function validator(Request $request){
        $validator=Validator::make($request->input(),[
            'logistic_number'=>['required_without_all:batch_number,order_code'],
            'order_code'=>['required_without_all:logistic_number,batch_number'],
            'weight'=>'required|min:0|max:999999|numeric',
            'paper_box_id'=>'nullable|integer',
            'batch_number'=>['required_without_all:logistic_number,order_code']
        ],[
            'required'=>':attribute 为必填项',
            'max'=>':attribute 字符过多或输入值过大',
            'integer'=>':attribute 选择有误',
            'min'=>':attribute 不得为负',
            'numeric'=>':attribute 应为数字',
            'unique'=>':attribute 已存在',
            'required_without_all'=>':attribute 与快递单号或订单号至少选一项'
        ],[
            'logistic_number'=>'快递单号',
            'weight'=>'重量',
            'paper_box_id'=>'纸箱',
            'batch_number'=>'波次号',
            'order_code'=>'订单号',
        ]);
        return $validator;
    }

//    public function syncBatch($batch_number,$weight,$max,$centre,$min,$date,$paperBox_id){
////        $accomplishToWMS=new Api\thirdPart\flux\PackageController();
////        $packageBatch=Package::where('batch_number',$batch_number)->first();
//        $newValues = ['weight' => $weight];
//        $newValues['batch_number']=$batch_number;
//        if($max)$newValues['length']=$max;
//        if($centre)$newValues['width']=$centre;
//        if($min)$newValues['height']=$min;
//        if($date)$newValues['weighed_at']=$date;
//        if($paperBox_id)$newValues['paper_box_id']=$paperBox_id;
//        if($max&&$centre&&$min){
//            $newValues['bulk']=$max*$centre*$min;
//        }
////        $weightChanged=$packageBatch['weight']!=$weight;
////        Package::where('batch_number',$batch_number)->update($newValues);
////        $packageBatch['forceUpload']=$weightChanged;
//        Controller::logS(__METHOD__,__FUNCTION__,"批量更新时批次号传入：{$batch_number}");
////        $result=$accomplishToWMS->markWMSOnBatch($packageBatch['batch_number']);
////        if ($result['result']){
////            $newValues['status']='已上传';
////            Controller::logS(__METHOD__,'SUCCESS_'.__FUNCTION__,'批量更改波次上传成功'.json_encode($packageBatch));
////        }else{
////            $newValues['status']='上传异常';
////            Controller::logS(__METHOD__,'error_'.__FUNCTION__,'批量更改波次上传异常:'.$result['message'].json_encode($packageBatch));
////        }picktotraceid
//        $packagesInOracle=OracleDOCWaveDetails::where('doc_wave_details.waveno',$batch_number)
//            ->leftJoin('act_allocation_details','act_allocation_details.orderno','doc_wave_details.orderno')
//            ->get();
//        foreach ($packagesInOracle as $packageInOracle) {
//            $newValues['logistic_number']=$packageInOracle['picktotraceid'];
//            $package = Package::where('logistic_number', $packageInOracle['picktotraceid'])->first();
//            if(!$package){
//                Package::create($newValues);
//            }else{
//                $package->fill($newValues);
//                $package->update();
//            }
//        }
////        Package::where('batch_number',$batch_number)->update($newValues);
//
//    }

    public function statisticExport($packages,$owners,$logistics){
        if (!$packages||!$owners||!$logistics) return;
        ini_set('max_execution_time',3500);
        ini_set('memory_limit','2726M');
        $row=[[]];
        $row[0]['owner']='货主';
        $row[0]['sum']='总计';
        foreach ($logistics as $logistic){
            $row[0][$logistic->id]=$logistic->name;
        }
        $ownerArr=[];
        foreach ($owners as $owner){
            foreach ($packages as $package){
                if ($owner->id==$package->owner_id){
                    $ownerArr[$owner->name][$package->logistic_id]=$package->count;
                }
            }
        }
        $list=[];
        $i=0;
        foreach ($owners as $owner){
            if (isset($ownerArr[$owner->name])){
                $w['owner']=$owner->name;
                $sum=0;
                foreach ($logistics as $logistic){
                    if (isset($ownerArr[$owner->name][$logistic->id])){
                        $w[$logistic->id]=$ownerArr[$owner->name][$logistic->id];
                        $sum=$sum+$ownerArr[$owner->name][$logistic->id];
                    }
                    if (!isset($ownerArr[$owner->name][$logistic->id])){
                        $w[$logistic->id]=0;
                    }
                }
                $w['sum']=$sum;
                $list[$i]=$w;
                $i++;
            }
        }
        return Excel::download(new Export($row,$list),date('YmdHis', time()).'-称重统计记录单.xlsx');
    }


}