baoshi-was/app/Services/OracleDOCOrderHeaderService.php

<?php

namespace App\Services;

use App\OracleActAllocationDetails;
use App\OracleBasCode;
use App\OracleBasCustomer;
use App\OracleDOCOrderDetail;
use App\OracleDOCOrderHeader;
use App\Owner;
use App\Traits\ServiceAppAop;


class OracleDOCOrderHeaderService
{
    use ServiceAppAop;
    protected $modelClass=OracleDOCOrderHeader::class;

    static $columns =[
        'DOC_Order_Header.OrderNo','DOC_Order_Header.CustomerID','DOC_Order_Header.OrderTime',
        'DOC_Order_Header.SOReference1', 'DOC_Order_Header.ConsigneeID','DOC_Order_Header.C_Contact',
        'DOC_Order_Header.ConsigneeName','DOC_Order_Header.C_Address1', 'DOC_Order_Header.C_City',
        'DOC_Order_Header.C_Province','DOC_Order_Header.UserDefine1','DOC_Order_Header.AddTime',
        'DOC_Order_Header.EditTime','DOC_Order_Header.Notes','DOC_Order_Header.CarrierID',
        'DOC_Order_Header.CarrierName', 'DOC_Order_Header.LastShipmentTime','DOC_Order_Header.EDISendFlag',
        'DOC_Order_Header.SOReference5', 'DOC_Order_Header.C_Tel2','DOC_Order_Header.Transportation',
        'DOC_Order_Header.WareHouseId','DOC_Order_Header.SOStatus','DOC_Order_Header.C_Tel1','DOC_Order_Header.C_District',
        'DOC_Order_Header.IssuePartyName','DOC_Order_Header.OrderType','DOC_Order_Header.WaveNo',
        'DOC_Order_Header.ReleaseStatus','DOC_Order_Header.h_edi_20','DOC_Order_Header.h_edi_03',
    ];

    public function getQuery(){
        return OracleDOCOrderHeader::query()->selectRaw(implode(',',self::$columns))
            ->with(['oracleBASCustomer'=>function($query){
                $query->selectRaw('BAS_CUSTOMER.CustomerID,BAS_CUSTOMER.Customer_Type,BAS_CUSTOMER.Descr_C,BAS_CUSTOMER.Active_Flag');
            },'oracleDOCOrderDetails'=>function($query){
                $query->selectRaw('doc_order_details.orderNo,doc_order_details.customerid,doc_order_details.sku,doc_order_details.QtyOrdered,doc_order_details.Location,doc_order_details.OrderLineNo,doc_order_details.d_edi_03');
            }, 'actAllocationDetails'=>function($query){
                $query->selectRaw('ACT_Allocation_Details.AllocationDetailsID,ACT_Allocation_Details.OrderNo,ACT_Allocation_Details.Qty_Each,ACT_Allocation_Details.PickToTraceID,ACT_Allocation_Details.CustomerID,ACT_Allocation_Details.Sku,ACT_Allocation_Details.Location,ACT_Allocation_Details.SkuLineNo,ACT_Allocation_Details.OrderLineno,ACT_Allocation_Details.CheckTime');
            },'oracleBASCode'=>function($query){
                $query->selectRaw('BAS_Codes.CodeID,BAS_Codes.CodeName_C,BAS_Codes.Code');
            },'orderType'=>function($query){
                $query->selectRaw('BAS_Codes.CodeID,BAS_Codes.CodeName_C,BAS_Codes.Code');
            }]);
    }

    public function getOrderTracking()
    {
        return OracleDOCOrderHeader::query()->selectRaw(implode(',',self::$columns))
            ->with(['oracleBASCustomer'=>function($query){
                $query->selectRaw('BAS_CUSTOMER.CustomerID,BAS_CUSTOMER.Customer_Type,BAS_CUSTOMER.Descr_C,BAS_CUSTOMER.Active_Flag');
            },'oracleDOCOrderDetails'=>function($query){
                $query->selectRaw('doc_order_details.orderNo,doc_order_details.customerid,doc_order_details.sku,doc_order_details.QtyOrdered,doc_order_details.Location,doc_order_details.OrderLineNo,doc_order_details.d_edi_03');
            }, 'actAllocationDetails'=>function($query){
                $query->with(['oracleDocOrderSerialNos'=>function($query){
                    $query->selectRaw('SerialNo,OrderNo,Sku,AllocationDetailsId');
                }]);
                $query->selectRaw('ACT_Allocation_Details.AllocationDetailsID,ACT_Allocation_Details.OrderNo,ACT_Allocation_Details.Qty_Each,ACT_Allocation_Details.PickToTraceID,ACT_Allocation_Details.CustomerID,ACT_Allocation_Details.Sku,ACT_Allocation_Details.Location,ACT_Allocation_Details.SkuLineNo,ACT_Allocation_Details.OrderLineno,ACT_Allocation_Details.CheckTime');
            },'oracleBASCode'=>function($query){
                $query->selectRaw('BAS_Codes.CodeID,BAS_Codes.CodeName_C,BAS_Codes.Code');
            },'orderType'=>function($query){
                $query->selectRaw('BAS_Codes.CodeID,BAS_Codes.CodeName_C,BAS_Codes.Code');
            }]);
    }

    function first(array $params){
        $order = OracleDOCOrderHeader::query();
        foreach ($params as $column => $value){
            $order->where($column, $value);
        }
        return $order->first();
    }


    public function getWmsOrderOnStartDateCreate($startDate){
        /** @var Owner $owners */
        $owners = app(OrderTrackingOwnerService::class)->getTrackingOrderOwner();
        return OracleDOCOrderHeader::query()
            ->with(['oracleDOCOrderDetails', 'actAllocationDetails.oracleDocOrderSubSerialNos','oracleBASCode','oracleBASCustomer'])
            ->where('DOC_Order_Header.addTime','>=',$startDate)
            ->whereIn('DOC_Order_Header.customerID',data_get($owners,'*.code'))
            ->get();
    }

    public function getWmsOrderOnStartDateEdit($startDate){
        /** @var Owner $owners */
        $owners = app(OrderTrackingOwnerService::class)->getTrackingOrderOwner();
        return OracleDOCOrderHeader::query()
            ->with(['oracleDOCOrderDetails', 'actAllocationDetails.oracleDocOrderSubSerialNos','oracleBASCode','oracleBASCustomer'])
            ->where('DOC_Order_Header.EditTime','>=',$startDate)
            ->whereColumn('DOC_Order_Header.EditTime','<>','DOC_Order_Header.addTime')
            ->whereIn('DOC_Order_Header.customerID',data_get($owners,'*.code'))
            ->get();
    }

    /**
     * @param OracleDOCOrderHeader $orderHeader
     * @return array|mixed
     */
    public function getLogisticNumbers($orderHeader)
    {
        if(!$orderHeader ?? false){return [];}
        $actAllocationDetails = $orderHeader->actAllocationDetails ?? [];
        return array_unique(array_diff(data_get($actAllocationDetails,'*.picktotraceid'),['','*',null]));
    }


    public function getWMSOrderOnStartDate($startDate)
    {
        return  $this->getQuery()->where('DOC_Order_Header.addTime','>=',$startDate)
            ->whereColumn('DOC_Order_Header.editTime','=','DOC_Order_Header.addTime')
            ->orderByDesc('DOC_Order_Header.addTime')
            ->get();
    }

    public function getWMSOrderOnEditDate($startDate){
        return  $this->getQuery()->where('DOC_Order_Header.editTime','>=',$startDate)
            ->whereColumn('DOC_Order_Header.editTime','!=','DOC_Order_Header.addTime')
            ->orderByDesc('DOC_Order_Header.editTime')
            ->get();
    }

    public function getOrderInfoByOrderNos($orderNos)
    {
        if(empty($orderNos))return collect();
        if(count($orderNos) == 0)return collect();
        return $this->getQuery()->whereIn('DOC_Order_Header.OrderNo',$orderNos)->get();
    }

    public function loadMissing($orderHeaders)
    {
        return $orderHeaders->loadMissing(['oracleBASCustomer'=>function($query){
            $query->selectRaw('BAS_CUSTOMER.CustomerID,BAS_CUSTOMER.Customer_Type,BAS_CUSTOMER.Descr_C,BAS_CUSTOMER.Active_Flag');
        },'oracleDOCOrderDetails'=>function($query){
            $query->selectRaw('doc_order_details.orderNo,doc_order_details.customerid,doc_order_details.sku,doc_order_details.QtyOrdered,doc_order_details.Location,doc_order_details.OrderLineNo,doc_order_details.d_edi_03');
        }, 'actAllocationDetails'=>function($query){
            $query->selectRaw('ACT_Allocation_Details.AllocationDetailsID,ACT_Allocation_Details.OrderNo,ACT_Allocation_Details.Qty_Each,ACT_Allocation_Details.PickToTraceID,ACT_Allocation_Details.CustomerID,ACT_Allocation_Details.Sku,ACT_Allocation_Details.Location,ACT_Allocation_Details.SkuLineNo,ACT_Allocation_Details.OrderLineno,ACT_Allocation_Details.CheckTime');
        },'oracleBASCode'=>function($query){
            $query->selectRaw('BAS_Codes.CodeID,BAS_Codes.CodeName_C,BAS_Codes.Code');
        },'orderType'=>function($query){
            $query->selectRaw('BAS_Codes.CodeID,BAS_Codes.CodeName_C,BAS_Codes.Code');
        }]);
    }

}