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\DB;
use Illuminate\Support\Facades\Gate;
use Maatwebsite\Excel\Facades\Excel;

class OrderController extends Controller
{
    private function preciseQuery(Request $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."%'";
        }else unset($request->input()['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 = '".$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_temp.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.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
                      LEFT JOIN (select XMLAGG(XMLPARSE(CONTENT picktotraceid || ',' WELLFORMED) ORDER BY ORDERNO).GETCLOBVAL() as picktotraceid,
                                    ORDERNO from ACT_ALLOCATION_DETAILS group by ORDERNO)ACT_ALLOCATION_DETAILS_temp
                                ON DOC_ORDER_HEADER.orderno = ACT_ALLOCATION_DETAILS_temp.orderno";
        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 ($order->picktotraceid){
                $order->picktotraceid=rtrim($order->picktotraceid, ',');
                $picktotraceid=array_unique(explode(',',$order->picktotraceid));
                $order->picktotraceid=$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]);
                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]);
            $order->notes=($order->notes).",".$content;
            array_push($data,$order);
        }
        foreach ($ordersNullArr as $order){
            array_push($data,["orderno"=>$order,'notes'=>$content]);
        }
        return $data;
    }
    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'=>'*']);
        if ($waveno && $waveno!='*'){
            OracleDOCWaveDetails::where('waveno',$waveno)->where('orderno',$orderno)->delete();
        }
        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']);
        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');
    }

}