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

<?php

namespace App\Http\Controllers;

use App\Exports\Export;
use App\OracleBasCustomer;
use App\OracleDOCOrderHeader;
use App\OracleDOCWaveDetails;
use Doctrine\DBAL\Schema\AbstractAsset;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Gate;
use Maatwebsite\Excel\Facades\Excel;

class OrderController extends Controller
{
    private function preciseQuery($request,$sql){
        $orderdate_start=$request->input('orderdate_start');
        $orderdate_end=$request->input('orderdate_end');
        $ordertime_start=$request->input('ordertime_start');
        $ordertime_end=$request->input('ordertime_end');
        $customerid=$request->input('customerid');
        $orderno=$request->input('orderno');
        $soreference5=$request->input('soreference5');
        $codename_c=$request->input('codename_c');
        $carriername=$request->input('carriername');
        $soreference1=$request->input('soreference1');
        $issuepartyname=$request->input('issuepartyname');
        $notes=$request->input('notes');
        $addtime=$request->input('addtime');
        $waveno=$request->input('waveno');
        $edisendflag2=$request->edisendflag2;
        if ($orderdate_start){
            $sql.=" and ordertime>='".$orderdate_start;
            if ($ordertime_start)$sql.=" ".$ordertime_start.":00'";
            else $sql.=" 00:00:00'";
        }
        if ($orderdate_end){
            $sql.=" and ordertime<='".$orderdate_end;
            if ($ordertime_end)$sql.=" ".$ordertime_end.":59' ";
            else $sql.=" 23:59:59' ";
        }
        if ($customerid)$sql.=" and customerid='".$customerid."'";
        if ($orderno)$sql.=" and orderno like '".$orderno."%'";
        if ($codename_c)$sql.=" and sostatus = '".$codename_c."'";
        if ($carriername)$sql.=" and carriername like '".$carriername."%'";
        if ($soreference1){
            $arr=array_filter(preg_split('/[,， ]+/is', $soreference1));
            if (count($arr)>0){
                $sql.=" and soreference1 in (";
                foreach ($arr as $index=>$str){
                    if ($index==0){
                        $sql.="'".$str."'";
                        continue;
                    }
                    $sql.=",'".$str."'";
                }
                $sql.=")";
            }
        }
        if ($waveno)$sql.=" and WaveNo like '".$waveno."%'";
        if ($issuepartyname)$sql.=" and issuepartyname like '".$issuepartyname."%'";
        if ($notes){
            if ($addtime&&$addtime!=0)$sql.=" and addtime >= '".date('Y-m-d H:i:s',strtotime("-".$addtime." day"))."'";
            $sql.=" and notes like '%".$notes."%'";
        }
        if ($soreference5){
            $arr=array_filter(preg_split('/[,， ]+/is', $soreference5));
            if (count($arr)>0){
                $sql.=" and soreference5 in (";
                foreach ($arr as $index=>$str){
                    if ($index==0){
                        $sql.="'".$str."'";
                        continue;
                    }
                    $sql.=",'".$str."'";
                }
                $sql.=")";
            }
        }
        if ($edisendflag2){
            $sql.=" and edisendflag2 = '".$edisendflag2."'";
        }
        return $sql;
    }

    public function delivering(Request $request){
        if(!Gate::allows('订单管理-查询')){ return redirect(url('/'));  }
        $paginate=$request->input('paginate')??50;
        $page=$request->input('page')??1;
        $checkData=$request->input('checkData');
        $alternate_sku1=$request->input('alternate_sku1');
        $export=$request->input('export');
        $sql="select ACT_ALLOCATION_DETAILS.CHECKTIME,DOC_ORDER_HEADER.addtime,DOC_ORDER_HEADER.C_PROVINCE,DOC_ORDER_HEADER.C_CITY,DOC_ORDER_HEADER.C_DISTRICT,DOC_ORDER_HEADER.C_CONTACT,DOC_ORDER_HEADER.OrderNo,DOC_ORDER_HEADER.SOStatus,DOC_ORDER_HEADER.WAREHOUSEID,DOC_ORDER_HEADER.CustomerID
        ,DOC_ORDER_HEADER.C_Tel2,DOC_ORDER_HEADER.CarrierName,DOC_ORDER_HEADER.IssuePartyName,
       DOC_ORDER_HEADER.WaveNo,DOC_ORDER_HEADER.SOReference1
        ,DOC_ORDER_HEADER.soreference5,DOC_ORDER_HEADER.EDISENDFLAG2,DOC_ORDER_HEADER.EDISendTime2,DOC_ORDER_HEADER.Notes,DOC_ORDER_HEADER.ERPCANCELFLAG,
       DOC_ORDER_HEADER.Picking_Print_Flag,DOC_ORDER_HEADER.EDISENDFLAG
        ,DOC_ORDER_HEADER.ReleaseStatus,DOC_ORDER_HEADER.C_Address1,DOC_ORDER_HEADER.OrderTime,DOC_Order_Details.CustomerID,
       DOC_Order_Details.SKU,DOC_Order_Details.QtyOrdered,DOC_Order_Details.OrderLineNo,BAS_Codes.codename_c,BAS_Customer.descr_c as customer_descr_c,
       BAS_SKU.Alternate_SKU1,BAS_SKU.Descr_C,(select count(*) from DOC_ORDER_HEADER WHERE 1=1";
        $sql=$this->preciseQuery($request,$sql);
        ;$sql.=" ) as counted from (";
        if (!$request->input('export')&&!$request->input('export'))$sql.=" select * from (";
        $sql.=" select ROWNUM as rn,DOC_ORDER_HEADER.addtime,DOC_ORDER_HEADER.C_PROVINCE,DOC_ORDER_HEADER.C_CITY,DOC_ORDER_HEADER.C_DISTRICT,DOC_ORDER_HEADER.C_CONTACT,DOC_ORDER_HEADER.OrderNo,DOC_ORDER_HEADER.SOStatus,DOC_ORDER_HEADER.WAREHOUSEID,DOC_ORDER_HEADER.CustomerID
                          ,DOC_ORDER_HEADER.C_Tel2,DOC_ORDER_HEADER.CarrierName,DOC_ORDER_HEADER.IssuePartyName,
                         DOC_ORDER_HEADER.WaveNo,DOC_ORDER_HEADER.SOReference1
                          ,DOC_ORDER_HEADER.soreference5,DOC_ORDER_HEADER.EDISENDFLAG2,DOC_ORDER_HEADER.EDISendTime2,DOC_ORDER_HEADER.Notes,DOC_ORDER_HEADER.ERPCANCELFLAG,
                         DOC_ORDER_HEADER.Picking_Print_Flag,DOC_ORDER_HEADER.EDISENDFLAG
                          ,DOC_ORDER_HEADER.ReleaseStatus,DOC_ORDER_HEADER.C_Address1,DOC_ORDER_HEADER.OrderTime from (select * from DOC_ORDER_HEADER  order by ADDTIME desc )DOC_ORDER_HEADER where 1=1 ";
        $sql=$this->preciseQuery($request,$sql);
        if ($checkData){
            $checkData=explode(',',$checkData);
            if (count($checkData)>0){
                $sql.=" and orderno in (";
                foreach ($checkData as $index=>$str){
                    if ($index==0){
                        $sql.="'".$str."'";
                        continue;
                    }
                    $sql.=",'".$str."'";
                }
                $sql.=")";
            }
        }
        if (!$request->input('export')&&!$request->input('export'))$sql.=" and ROWNUM<='".$page*$paginate."')";
        if (!$request->input('export')&&!$request->input('export'))$sql.=" header where header.rn>'".($page-1)*$paginate."' ";
        $sql.=")DOC_ORDER_HEADER left join DOC_ORDER_DETAILS on DOC_ORDER_DETAILS.ORDERNO=DOC_ORDER_HEADER.ORDERNO
                      left join BAS_CODES on BAS_CODES.CODE=DOC_ORDER_HEADER.sostatus and BAS_CODES.codeid='SO_STS'
                      left join BAS_Customer on BAS_Customer.customerid=DOC_ORDER_HEADER.customerid
                      left join  BAS_SKU on DOC_Order_Details.CustomerID=BAS_SKU.CustomerID and DOC_Order_Details.SKU=BAS_SKU.SKU
                      left join ACT_ALLOCATION_DETAILS on DOC_Order_Details.orderno=ACT_ALLOCATION_DETAILS.orderno and DOC_Order_Details.orderlineno=ACT_ALLOCATION_DETAILS.orderlineno ";
        if ($alternate_sku1)$sql.=" where BAS_SKU.Alternate_SKU1 like '".$alternate_sku1."%'";
        $orders=DB::connection('oracle')->select(DB::raw($sql));
        $commodities=[];
        foreach ($orders as $index=>$order){
            if (isset($commodities[$order->orderno])){
                array_push($commodities[$order->orderno],
                    ["sku"=>$order->sku,"alternate_sku1"=>$order->alternate_sku1,"descr_c"=>$order->descr_c,"qtyordered"=>$order->qtyordered
                    ,"checktime"=>$order->checktime]);
                unset($orders[$index]);
                continue;
            }
            $commodities[$order->orderno]=[["sku"=>$order->sku,"alternate_sku1"=>$order->alternate_sku1,
                "descr_c"=>$order->descr_c,"qtyordered"=>$order->qtyordered,"checktime"=>$order->checktime]];
        }
        $orders=array_values($orders);
        $orders = new Collection($orders);
        $commodities=new Collection($commodities);
        if ($checkData || $export)return $this->export($orders,$commodities);
        $customers=OracleBasCustomer::select('customerid','descr_c')->where('customer_type','OW')->where('active_flag','Y')->get();
        $request=$request->input();
        $codes=DB::connection('oracle')->table('BAS_CODES')->select('code','codename_c')->where('codeid','SO_STS')->get();
        return view('order/index/delivering',compact('orders','customers','request','codes','commodities','page'));
    }
    //批量备注追加
    public function batchComments(Request $request){
        if(!Gate::allows('订单管理-批量备注')){ return redirect(url('/'));  }
        $checkData=$request->input('checkData');
        $content=$request->input('content');
        $ordersNotNull=OracleDOCOrderHeader::select('OrderNo','Notes')->whereIn('orderno',$checkData)
            ->whereNotNull('notes')->get();
        OracleDOCOrderHeader::select('OrderNo','Notes')->whereIn('orderno',$checkData)
            ->whereNull('notes')->update(['notes'=>$content]);
        $ordersNotNullArr=array_column($ordersNotNull->toArray(),'orderno');
        $ordersNullArr=array_diff($checkData,$ordersNotNullArr);
        $data=[];
        foreach ($ordersNotNull as $order){
            OracleDOCOrderHeader::where('orderno',$order->orderno)->update(["notes"=>($order->notes).",".$content]);
            $this->log(__METHOD__,'批量备注追加修改'.__FUNCTION__,json_encode($request->toArray()),Auth::user()['id']);
            $order->notes=($order->notes).",".$content;
            array_push($data,$order);
        }
        foreach ($ordersNullArr as $order){
            array_push($data,["orderno"=>$order,'notes'=>$content]);
        }
        return $data;
    }
    //批量冻结
    public function freezeAll(Request $request){
        if(!Gate::allows('订单管理-编辑')){ return redirect(url('/'));  }
        $checkData=$request->input('checkData')??'';
        //冻结条件只局限于 分配完成sostatus=40  创建订单sostatus=00  拣货完成sostatus=60  播种完成sostatus=61
        $sostatus=['40','00','60','61'];
        $orders=OracleDOCOrderHeader::select('OrderNo','SoStatus')->whereIn('orderno',$checkData)->whereIn('SoStatus',$sostatus)->get();
        if(count($checkData)!=count($orders))return ['success'=>false,'fail_info'=>"勾选订单有不可冻结订单"];
        foreach ($orders as $order){
            $is_freeze=OracleDOCOrderHeader::where('orderno',$order->orderno)->update(['releasestatus'=>'H','waveno'=>'*']);
            $this->log(__METHOD__,'批量冻结修改'.__FUNCTION__,json_encode($request->toArray()),Auth::user()['id']);
            $waveno=OracleDOCOrderHeader::where('orderno',$order->orderno)->value('waveno');
            if ($waveno && $waveno!='*'){
                OracleDOCWaveDetails::where('waveno',$waveno)->where('orderno',$order->orderno)->delete();
                $this->log(__METHOD__,'批量冻结删除波次号'.__FUNCTION__,json_encode($request->toArray()),Auth::user()['id']);
            }
            if (!$is_freeze)return ['success'=>false,'fail_info'=>"数据 {$order['orderno']} 更新失败"];
        }
        return ['success'=>true];
    }
    //批量取消分配
    public function deAllocationAll(Request $request){
        if(!Gate::allows('订单管理-编辑')){ return redirect(url('/'));  }
        $checkData=$request->input('checkData')??'';
        //取消分配条件只局限于 分配完成sostatus=40 和 部分分配sostatus=30
        $sostatus=['30','40'];
//        $orders=OracleDOCOrderHeader::select('OrderNo','SoStatus')->whereIn('orderno',$checkData)->where(function($query){
//            $query->orWhere('SoStatus','30')->orWhere('SoStatus','40');
//        })->toSql();
        $orders=OracleDOCOrderHeader::select('OrderNo','SoStatus')->whereIn('orderno',$checkData)->whereIn('SoStatus',$sostatus)->get();
        if(count($checkData)!=count($orders))return ['success'=>false,'fail_info'=>"勾选订单有不可取消分配订单"];
        foreach ($orders as $order){
            $is_deAllocation=$this->isDeAllocation($order->orderno);
            $deAllocation=OracleDOCOrderHeader::where('orderno',$order->orderno)->update(['sostatus'=>'00','waveno'=>'*']);
            $this->log(__METHOD__,'批量取消分配'.__FUNCTION__,json_encode($request->toArray()),Auth::user()['id']);
            $waveno=OracleDOCOrderHeader::where('orderno',$order->orderno)->value('waveno');
            if ($waveno && $waveno!='*'){
                OracleDOCWaveDetails::where('waveno',$waveno)->where('orderno',$order->orderno)->delete();
                $this->log(__METHOD__,'批量取消分配删除波次号'.__FUNCTION__,json_encode($request->toArray()),Auth::user()['id']);
            }
            if (!$is_deAllocation||!$deAllocation)return ['success'=>false,'fail_info'=>"数据 {$order['orderno']} 更新失败"];
        }
        return ['success'=>true];
    }
    //冻结
    public function freeze(Request $request){
        if(!Gate::allows('订单管理-编辑')){ return redirect(url('/'));  }
        $orderno=$request->input('orderno');
        $waveno=$request->input('waveno');
//        $order=OracleDOCOrderHeader::where('orderno',$orderno)->first();
//        if(!($order['codename_c']==='创建订单'||$order['codename_c']==='分配完成'||$order['codename_c']==='拣货完成'||$order['codename_c']==='播种完成')){
//            return ['success'=>false];
//        }
        OracleDOCOrderHeader::where('orderno',$orderno)->update(['releasestatus'=>'H','waveno'=>'*']);
        $this->log(__METHOD__,'冻结'.__FUNCTION__,json_encode($request->toArray()),Auth::user()['id']);
        if ($waveno && $waveno!='*'){
            OracleDOCWaveDetails::where('waveno',$waveno)->where('orderno',$orderno)->delete();
            $this->log(__METHOD__,'冻结删除波次号'.__FUNCTION__,json_encode($request->toArray()),Auth::user()['id']);
        }
        return ['success'=>true];
    }
    public function thaw(Request $request){
        if(!Gate::allows('订单管理-编辑')){ return redirect(url('/'));  }
        $orderno=$request->input('orderno');
        OracleDOCOrderHeader::where('orderno',$orderno)->update(['releasestatus'=>'N']);
        $this->log(__METHOD__,'解冻'.__FUNCTION__,json_encode($request->toArray()),Auth::user()['id']);
        return ['success'=>true];
    }

    function export($orders,$commodities){
        if(!Gate::allows('订单管理')){ return redirect(url('/'));  }
        ini_set('max_execution_time',3500);
        ini_set('memory_limit','3526M');
        $row=[[
            'orderno'=>'编号',
            'sostatus'=>'订单状态',
            'notes'=>'备注',
            'addtime'=>'接口下发时间',
            'issuepartyname'=>'店铺名称',
            'oracleBASCustomer_descr_c'=>'客户',
            'soreference1'=>'客户订单号',
            'carriername'=>'承运人',
            'soreference5'=>'快递单号',
            'c_contact'=>'收货人名称',
            'c_tel2'=>'收货人电话',
            'c_province'=>'省',
            'c_city'=>'市',
            'c_district'=>'区',
            'c_address1'=>'收货人地址',
            'waveno'=>'波次编号',
            'warehouseid'=>'仓库',
            'edisendflag2'=>'快递获取标记',
            'edisendtime2'=>'快递获取时间',
            'sku'=>'产品代码',
            'alternate_sku1'=>'产品条码',
            'descr_c'=>'产品名称',
            'qtyordered'=>'订单数量',
            'erpcancelflag'=>'接口取消标记',
            'picking_print_flag'=>'拣货单打印标记',
            'edisendflag'=>'接口回传标记',
            'releasestatus'=>'订单冻结',

        ]];
        $list=[];
        $sign=[];
        $mergeCell=[];
        foreach ($orders as $order){
            $data=[
                'orderno'=>$order->orderno,
                'addtime'=>$order->addtime,
                'sostatus'=>$order->codename_c,
                'warehouseid'=>$order->warehouseid,
                'oracleBASCustomer_descr_c'=>$order->customer_descr_c,
                'c_contact'=>$order->c_contact,
                'c_tel2'=>$order->c_tel2,
                'c_province'=>$order->c_province,
                'c_city'=>$order->c_city,
                'c_district'=>$order->c_district,
                'carriername'=>$order->carriername,
                'issuepartyname'=>$order->issuepartyname,
                'waveno'=>$order->waveno,
                'soreference1'=>$order->soreference1,
                'soreference5'=>$order->soreference5,
                'edisendflag2'=>$order->edisendflag2,
                'edisendtime2'=>$order->edisendtime2,
                'notes'=>$order->notes,
                'erpcancelflag'=>$order->erpcancelflag,
                'picking_print_flag'=>$order->picking_print_flag,
                'edisendflag'=>$order->edisendflag,
                'releasestatus'=>$order->releasestatus,
                'c_address1'=>$order->c_address1,
            ];
            if (count($commodities[$order->orderno])<1){
                $data['sku']='';
                $data['alternate_sku1']='';
                $data['descr_c']='';
                $data['qtyordered']='';
                array_push($list,$data);
                continue;
            }
            foreach ($commodities[$order->orderno] as $oracleDOCOrderDetail){
                $data['sku']=$oracleDOCOrderDetail['sku'];
                $data['alternate_sku1']=$oracleDOCOrderDetail['alternate_sku1'];
                $data['descr_c']=$oracleDOCOrderDetail['descr_c'];
                $data['qtyordered']=$oracleDOCOrderDetail['qtyordered'];
                if (isset($sign[$order->orderno])){
                    $data['orderno']='';$data['sostatus']='';$data['warehouseid']='';$data['oracleBASCustomer_descr_c']='';$data['c_contact']='';$data['addtime']='';
                    $data['c_tel2']='';$data['carriername']='';$data['issuepartyname']='';$data['waveno']='';$data['soreference1']='';$data['soreference5']='';
                    $data['edisendflag2']='';$data['edisendtime2']='';$data['notes']='';$data['erpcancelflag']='';$data['picking_print_flag']='';$data['edisendflag']='';
                    $data['releasestatus']='';$data['c_address1']='';$data['c_province']='';$data['c_city']='';$data['c_district']='';
                    array_push($list,$data);
                    continue;
                }
                array_push($list,$data);
                $sign[$order->orderno]=count($list)+1;
            }
            if (count($commodities[$order->orderno])>1){
                $mergeCell[$sign[$order->orderno]]=$sign[$order->orderno]+(count($commodities[$order->orderno])-1);
            }
        }
        $columnName=["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","X","Y","Z","AA"];
        return Excel::download(new Export($row,$list,$mergeCell,$columnName),date('YmdHis', time()).'-出库发运单.xlsx');
    }
    //取消分配
    public function deAllocation(Request $request){
        if(!Gate::allows('订单管理-编辑')){ return redirect(url('/'));  }
        $orderno=$request->input('orderno');
        $waveno=$request->input('waveno');
        OracleDOCOrderHeader::where('orderno',$orderno)->update(['sostatus'=>'00','waveno'=>'*']);
        $this->log(__METHOD__,'取消分配'.__FUNCTION__,json_encode($request->toArray()),Auth::user()['id']);
        if ($waveno && $waveno!='*'){
            OracleDOCWaveDetails::where('waveno',$waveno)->where('orderno',$orderno)->delete();
            $this->log(__METHOD__,'取消分配删除波次号'.__FUNCTION__,json_encode($request->toArray()),Auth::user()['id']);
        }
        $is_deAllocation=$this->isDeAllocation($orderno);
        if($is_deAllocation) return ['success'=>true];

    }
    //调用sp取消选中订单的分配
    public function isDeAllocation($orderno){
        $username=config('database.connections.oracle.username');
        $password=config('database.connections.oracle.password');
        $host=config('database.connections.oracle.host');
        $service_name=config('database.connections.oracle.service_name');
        $conn=oci_connect($username,$password,$host.'/'.$service_name); //连接oracle数据库
        $IN_Warehouse='';
        $IN_Process_Action_C='' ;
        $IN_Process_By_C='By OrderNO';
        $IN_WaveNO_C='';
        $IN_OrderNO_C=$orderno;
        $IN_OrderLineNO_C='';
        $IN_AllocationDetailsID='';
        $IN_Language='';
        $IN_UserID='';
        $OUT_Return_Code='';
        $sql_sp="begin SPSO_DEALLOCATION_PROCESS(:IN_Warehouse,:IN_Process_Action_C,:IN_Process_By_C,
        :IN_WaveNO_C,:IN_OrderNO_C,:IN_OrderLineNO_C,:IN_AllocationDetailsID,:IN_Language,:IN_UserID,:OUT_Return_Code); end;";
        $stmt = oci_parse($conn, $sql_sp);
        oci_bind_by_name($stmt,':IN_Warehouse',$IN_Process_Action_C);
        oci_bind_by_name($stmt,':IN_Process_Action_C',$IN_Warehouse);
        oci_bind_by_name($stmt,':IN_Process_By_C',$IN_Process_By_C);
        oci_bind_by_name($stmt,':IN_WaveNO_C',$IN_WaveNO_C);
        oci_bind_by_name($stmt,':IN_OrderNO_C',$IN_OrderNO_C);
        oci_bind_by_name($stmt,':IN_OrderLineNO_C',$IN_OrderLineNO_C);
        oci_bind_by_name($stmt,':IN_AllocationDetailsID',$IN_AllocationDetailsID);
        oci_bind_by_name($stmt,':IN_Language',$IN_Language);
        oci_bind_by_name($stmt,':IN_UserID',$IN_UserID);
        oci_bind_by_name($stmt,':OUT_Return_Code',$OUT_Return_Code);
        $this->log(__METHOD__,'调用sp根据订单取消分配'.__FUNCTION__,json_encode($orderno),Auth::user()['id']);
        return oci_execute($stmt);

    }

}