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

<?php

namespace App\Http\Controllers;

use App\Exports\Export;
use App\OracleBasCustomer;
use App\OracleDOCOrderHeader;
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
{
    public function delivering(Request $request){
        if(!Gate::allows('订单管理-发运')){ return redirect(url('/'));  }
        $paginate=$request->input('paginate')??50;
        $page=$request->input('page')??1;
        $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');
        $checkData=$request->input('checkData');
        $export=$request->input('export');
        $sql="select 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_Tel1,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) 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_Tel1,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";
        if ($ordertime_start)$sql.=" and ordertime>='".$ordertime_start." 00:00:00'";
        if ($ordertime_end)$sql.=" and ordertime<='".$ordertime_end." 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)$sql.=" and soreference1 like '".$soreference1."%'";
        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 ($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."' order by ordertime)";
        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
        group by 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_Tel1,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_SKU.Alternate_SKU1,BAS_SKU.Descr_C,BAS_Codes.codename_c,BAS_Customer.descr_c order by ordertime" ;
        $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]);
                unset($orders[$index]);
                continue;
            }
            $commodities[$order->orderno]=[["sku"=>$order->sku,"alternate_sku1"=>$order->alternate_sku1,
                "descr_c"=>$order->descr_c,"qtyordered"=>$order->qtyordered]];
        }
        $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;
    }

    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_tel1'=>'收货人电话',
            '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_tel1'=>$order->c_tel1,
                '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_tel1']='';$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"];
        return Excel::download(new Export($row,$list,$mergeCell,$columnName),date('YmdHis', time()).'-出库发运单.xlsx');
    }

}