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 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');
        $customerid=$request->input('customerid');
        $orderno=$request->input('orderno');
        $soreference5=$request->input('soreference5');
        $codename_c=$request->input('codename_c');
        $codename_c_end=$request->input('codename_c_end');
        $carriername=$request->input('carriername');
        $soreference1=$request->input('soreference1');
        $issuepartyname=$request->input('issuepartyname');
        $notes=$request->input('notes');
        $addtime=$request->input('addtime');
        $waveno=$request->input('waveno');
        $alternate_sku1=$request->input('alternate_sku1');
        $edisendflag2=$request->edisendflag2;
        $edisendflag=$request->edisendflag;
        if ($alternate_sku1){
            if ($request->checkAllSign) $detailsOrderno = $this->getOrdersNo($alternate_sku1,false);
            else $detailsOrderno = $this->getOrdersNo($alternate_sku1,true, $request->page ?? 1, $request->paginate ?? 50);
            if (count($detailsOrderno)>0){
                $sql.=' AND orderno IN (';
                foreach ($detailsOrderno as $index => $no){
                    if ($index==0){
                        $sql.="'".$no."'";
                        continue;
                    }
                    $sql.=",'".$no."'";
                }
                $sql.=')';
            }else{
                $sql .= 'AND orderno IS NULL ';
            }
        }
        if ($orderdate_start && $orderdate_end && $addtime){
            $request->offsetUnset('orderdate_start');$request->offsetUnset('orderdate_end');
            $orderdate_start=null;
            $orderdate_end=null;
        }
        if ($orderdate_start){
            if (strlen($orderdate_start)<11)$orderdate_start .= ' 00:00';
            $sql.=" and addtime>='".$orderdate_start.":00'";
        }
        if ($orderdate_end){
            if (strlen($orderdate_end)<11)$orderdate_end .= ' 23:59 ';
            $sql.=" and addtime<='".$orderdate_end.":59' ";
        }
        if ($customerid){
            $arr = explode(',',$customerid);
            if (count($arr)>0) {
                $sql.=" and customerid in (";
                foreach ($arr as $index=>$str){
                    if ($index==0){
                        $sql.="'".$str."'";
                        continue;
                    }
                    $sql.=",'".$str."'";
                }
                $sql.=")";
            }
        }
        if ($orderno)$sql.=" and orderno like '".$orderno."%'";
        if ($codename_c&&$codename_c_end)$sql.=" and sostatus between '".$codename_c."' and '".$codename_c_end."'";
        if ($codename_c&&!$codename_c_end)$sql.=" and sostatus = '".$codename_c."'";
        if (!$codename_c&&$codename_c_end)$sql.=" and sostatus = '".$codename_c_end."'";
        if ($carriername)$sql.=" and carriername like '".$carriername."%'";
        if ($soreference1){
            $arr=array_filter(preg_split('/[,， ]+/is', $soreference1));
            if (count($arr)==1){
                $sql.=" and soreference1 like '".$soreference1."%'";
            }
            if (count($arr)>1){
                $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."%'";
        }else $request->offsetUnset('addtime');
        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.=") ";
                /*分箱号*/
                $oracleActAllocationDetails=DB::connection('oracle')->table('ACT_ALLOCATION_DETAILS')->whereIn('picktotraceid',$arr)->get();
                $picktotraceids=array_unique(array_column($oracleActAllocationDetails->toArray(),'orderno'));
                if (count($picktotraceids)>0){
                    $sql.='OR orderno IN (';
                    foreach ($picktotraceids as $index=>$str){
                        if ($index==0){
                            $sql.="'".$str."'";
                            continue;
                        }
                        $sql.=",'".$str."'";
                    }
                    $sql.=") ";
                }
                $sql.=")";
            }
        }
        if ($edisendflag2){
            $sql.=" and edisendflag2 like '".$edisendflag2."%'";
        }
        if ($edisendflag){
            $sql.=" and edisendflag like '".$edisendflag."%'";
        }
        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('data');
        $export=$request->input('checkAllSign');
        $sql="select ACT_ALLOCATION_DETAILS.picktotraceid,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.EDIREMARKS2,
       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.LineStatus,DOC_Order_Details.OrderLineNo,order_code.codename_c as orderCodeName,
       order_detail_code.codename_c as orderDetailCodeName,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 (!$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,DOC_ORDER_HEADER.EDIREMARKS2 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 (!$export)$sql.=" and ROWNUM<='".$page*$paginate."')";
        if (!$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  order_code on order_code.CODE=DOC_ORDER_HEADER.sostatus and order_code.codeid='SO_STS'
                      left join BAS_CODES  order_detail_code on order_detail_code.CODE=DOC_ORDER_DETAILS.linestatus and order_detail_code.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";
        $orders=DB::connection('oracle')->select(DB::raw($sql));
        $commodities=[];
        $picktotraceids=[];
        /*
         * 第二种扁平化方式(节省空间)： 使用快慢指针，因拿到数据正常为顺序数据,自下标1开始与上一条对比orderno唯一标识，不同则更新慢指针下标，
         *  相同则为指定下标map类型数组追加一个键值对属性来记录同类数量,如N条，每次追加属性时先看属性是否存在，存在+1
         * */
        foreach ($orders as $index=>$order){
            if ($order->picktotraceid){
                if (array_key_exists($order->orderno,$picktotraceids)){
                    if (!in_array($order->picktotraceid,$picktotraceids[$order->orderno],true)){
                        array_push($picktotraceids[$order->orderno],$order->picktotraceid);
                    };
                }else $picktotraceids[$order->orderno] = [$order->picktotraceid];
            }
            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,"orderdetailcodename"=>$order->orderdetailcodename]);
                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,"orderdetailcodename"=>$order->orderdetailcodename]];
        }
        $picktotraceids=json_encode($picktotraceids);
        $orders=array_values($orders);
        $orders = new Collection($orders);
        $commodities=new Collection($commodities);
        if ($checkData || $export)return $this->export($orders,$commodities);
        $customers=OracleBasCustomer::query()->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')->orderBy('code','asc')->get();
        return view('order/index/delivering',compact('orders','customers','request','codes','commodities','page','picktotraceids'));
    }
    //通过商品条码获取订单编号
    public function getOrdersNo($alternate_sku1, $isPaging = true, $page = 1, $paginate = 50){
        if ($isPaging){
            $sql = "SELECT RESULT.ORDERNO FROM 
                        (SELECT ROWNUM rn,DETAIL.ORDERNO as ORDERNO FROM
                            (SELECT ORDERNO FROM DOC_ORDER_DETAILS WHERE SKU LIKE '".$alternate_sku1."' GROUP BY ORDERNO ORDER BY ORDERNO DESC)DETAIL
                        WHERE ROWNUM<='".$page*$paginate."')RESULT 
                    WHERE RESULT.rn>'".($page-1)*$paginate."'";
        }else {
            $sql = "SELECT ORDERNO FROM DOC_ORDER_DETAILS WHERE SKU LIKE '".$alternate_sku1."' GROUP BY ORDERNO ORDER BY ORDERNO DESC";
        }
        $orderDetails = DB::connection('oracle')->select(DB::raw($sql));
        return array_column($orderDetails,'orderno');
    }

    //批量备注追加
    public function batchComments(Request $request){
        if(!Gate::allows('订单管理-批量备注')){ return redirect(url('/'));  }
        $checkData=$request->input('checkData');
        $content=$request->input('content');
        $ordersNotNull=OracleDOCOrderHeader::query()->select('OrderNo','Notes')->whereIn('orderno',$checkData)
            ->whereNotNull('notes')->get();
        OracleDOCOrderHeader::query()->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::query()->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){
            $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']);
            }
            $is_freeze=OracleDOCOrderHeader::where('orderno',$order->orderno)->update(['releasestatus'=>'H','waveno'=>'*']);
            $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);
            $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']);
            }
            $deAllocation=OracleDOCOrderHeader::where('orderno',$order->orderno)->update(['sostatus'=>'00','waveno'=>'*']);
            $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];
//        }
        if ($waveno && $waveno!='*'){
            OracleDOCWaveDetails::where('waveno',$waveno)->where('orderno',$orderno)->delete();
            $this->log(__METHOD__,'冻结删除波次号'.__FUNCTION__,json_encode($request->toArray()),Auth::user()['id']);
        }
        OracleDOCOrderHeader::where('orderno',$orderno)->update(['releasestatus'=>'H','waveno'=>'*']);
        $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'=>'产品条码',
            'orderdetailcodename'=>'明细状态',
            'descr_c'=>'产品名称',
            'qtyordered'=>'订单数量',
            'checktime'=>'复核时间',
            'erpcancelflag'=>'接口取消标记',
            'picking_print_flag'=>'拣货单打印标记',
            'edisendflag'=>'接口回传标记',
            'ediremarks2'=>'接口回传异常备注',
            'releasestatus'=>'订单冻结',

        ]];
        $list=[];
        $sign=[];
        $mergeCell=[];
        foreach ($orders as $order){
            $data=[
                'orderno'=>$order->orderno,
                'addtime'=>$order->addtime,
                'sostatus'=>$order->ordercodename,
                '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,
                'ediremarks2'=>$order->ediremarks2,
                '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']='';
                $data['checktime']='';
                $data['orderdetailcodename']='';
                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'];
                $data['checktime']=$oracleDOCOrderDetail['checktime'];
                $data['orderdetailcodename']=$oracleDOCOrderDetail['orderdetailcodename'];
                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');
        if ($waveno && $waveno!='*'){
            OracleDOCWaveDetails::where('waveno',$waveno)->where('orderno',$orderno)->delete();
            $this->log(__METHOD__,'取消分配删除波次号'.__FUNCTION__,json_encode($request->toArray()),Auth::user()['id']);
        }
        OracleDOCOrderHeader::where('orderno',$orderno)->update(['sostatus'=>'00','waveno'=>'*']);
        $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);
    }

}