baoshi-was/app/Services/OrderService.php

<?php

namespace App\Services;

use App\Commodity;
use App\Components\Database;
use App\Feature;
use App\Jobs\OrderCreateInstantBill;
use App\Jobs\OrderCreateWaybill;
use App\Jobs\OrderFreeze;
use App\Logistic;
use App\OracleActAllocationDetails;
use App\OracleDOCASNHeader;
use App\OracleDOCOrderHeader;
use App\Order;
use App\OrderCommodity;
use App\OrderIssue;
use App\OrderPackage;
use App\Owner;
use App\OwnerFeeDetail;
use App\OwnerFeeDetailLogistic;
use App\OwnerFeeExpress;
use App\OwnerReport;
use App\Province;
use App\RejectedBill;
use App\Services\common\BatchUpdateService;
use App\Services\common\DataHandlerService;
use App\Shop;
use App\Traits\ModelSearchWay;
use App\ValueStore;
use App\Warehouse;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use App\Traits\ServiceAppAop;


class OrderService
{
    use ServiceAppAop;
    use ModelSearchWay, Database;

    protected $modelClass = Order::class;
    /**
     * @var OracleDOCOrderHeaderService $oracleDOCOrderHeaderService
     * @var OrderPackageService $packageService
     * @var OrderCommodityService $orderCommodityService
     * @var BatchService $batchService
     */
    private $oracleDOCOrderHeaderService;
    private $packageService;
    private $orderCommodityService;
    private $batchService;

    public function __construct()
    {
        $this->oracleDOCOrderHeaderService = app('OracleDocOrderHeaderService');
        $this->packageService = app('OrderPackageService');
        $this->orderCommodityService = app('OrderCommodityService');
        $this->batchService = app('BatchService');
    }

    public function batchUpdate($params)
    {
        return app(BatchUpdateService::class)->batchUpdate('orders', $params);
    }


    private function delayedChangeParam(array &$params, $page, $paginate)
    {
        $startRow = $page ? ($page-1)*$paginate : 0;
        $endRow = $page ? $page*$paginate : null;
        $sql = <<<SQL
    SELECT ORDERNO FROM(
    SELECT ACT_ALLOCATION_DETAILS.ORDERNO,ROWNUM rn FROM ACT_ALLOCATION_DETAILS
    LEFT JOIN DOC_ORDER_HEADER ON DOC_ORDER_HEADER.ORDERNO = ACT_ALLOCATION_DETAILS.ORDERNO
    WHERE CHECKTIME > TO_DATE(NVL(H_EDI_20,H_EDI_03),'yyyy-mm-dd,hh24:mi:ss')+({$params["delayedDeliver"]}/24)
    AND NVL(H_EDI_20,H_EDI_03) IS NOT NULL AND NVL(DOC_ORDER_HEADER.H_EDI_20,DOC_ORDER_HEADER.H_EDI_03) <> 'ERP订单取消'
    AND CHECKTIME IS NOT NULL
SQL;
        if (isset($paginate["customerid"]) && is_array($paginate["customerid"]) && count($paginate["customerid"])>0){
            $sql .= " AND CUSTOMERID IN (";
            foreach ($paginate["customerid"] as $index => $customer){
                $sql .= "'".$customer."'";
                if ($index!==count($paginate["customerid"])-1)$sql.=",";
            }
            $sql.=")";
        }
        $ki = (isset($params["orderdate_start"]) ? 1 : 0) + (isset($params["orderdate_end"]) ? 2 : 0);
        switch ($ki){
            case 0:
                $params["orderdate_start"] = date('Y-m-d H:i', strtotime("-15 day"));
                $params["orderdate_end"] = date('Y-m-d H:i');
                $sql.=" AND DOC_ORDER_HEADER.ADDTIME BETWEEN TO_DATE('{$params["orderdate_start"]}:00','yyyy-mm-dd,hh24:mi:ss') AND TO_DATE('{$params["orderdate_end"]}:59','yyyy-mm-dd,hh24:mi:ss')";
                break;
            case 1:
                if (strlen($params["orderdate_start"]) < 11) $params["orderdate_start"] .= ' 00:00';
                $sql.=" AND DOC_ORDER_HEADER.ADDTIME >= TO_DATE('{$params["orderdate_start"]}:00','yyyy-mm-dd,hh24:mi:ss')";
                break;
            case 2:
                if (strlen($params["orderdate_end"]) < 11) $params["orderdate_end"] .= ' 23:59';
                $sql.=" AND DOC_ORDER_HEADER.ADDTIME <= TO_DATE('{$params["orderdate_end"]}:59','yyyy-mm-dd,hh24:mi:ss')";
                break;
            default:
                if (strlen($params["orderdate_start"]) < 11) $params["orderdate_start"] .= ' 00:00';
                if (strlen($params["orderdate_end"]) < 11) $params["orderdate_end"] .= ' 23:59';
                $sql.=" AND DOC_ORDER_HEADER.ADDTIME BETWEEN TO_DATE('{$params["orderdate_start"]}:00','yyyy-mm-dd,hh24:mi:ss') AND TO_DATE('{$params["orderdate_end"]}:59','yyyy-mm-dd,hh24:mi:ss')";
        }
        if ($endRow)$sql .= " AND ROWNUM<=50";
        $sql .= " ORDER BY ORDERNO) WHERE rn>{$startRow}";
        $params = ["query" => " AND ORDERNO IN ({$sql})"];
    }

    private function preciseQuery(array $params, $sql)
    {
        $orderdate_start = $params["orderdate_start"] ?? null;
        $orderdate_end = $params["orderdate_end"] ?? null;
        $customerid = $params["customerid"] ?? null;
        $orderno = $params["orderno"] ?? null;
        $soreference5 = $params["soreference5"] ?? null;
        $codename_c = $params["codename_c"] ?? null;
        $codename_c_end = $params["codename_c_end"] ?? null;
        $carriername = $params["carriername"] ?? null;
        $soreference1 = $params["soreference1"] ?? null;
        $issuepartyname = $params["issuepartyname"] ?? null;
        $notes = $params["notes"] ?? null;
        $addtime = $params["addtime"] ?? null;
        $waveno = $params["waveno"] ?? null;
        $alternate_sku1 = $params["alternate_sku1"] ?? null;
        $c_contact = $params["c_contact"] ?? null;
        $c_tel2 = $params["c_tel2"] ?? null;
        $c_province = $params["c_province"] ?? null;
        $c_city = $params["c_city"] ?? null;
        $c_district = $params["c_district"] ?? null;
        $releasestatus = $params["releasestatus"] ?? null;
        $ordernos = $params["ordernos"] ?? null;
        $edisendflag2 = $params["edisendflag2"] ?? null;
        $edisendflag = $params["edisendflag"] ?? null;
        $manualflag = $params["manualflag"] ?? null;
        $checkAllSign = $params['checkAllSign'] ?? null;
        $presentDelayed = $params['presentDelayed'] ?? null;
        if ($params["query"] ?? false){
            $sql .= $params["query"];
        }
        if ($ordernos) {
            $sql .= " AND orderno IN " . $ordernos;
        }
        if ($c_contact) $sql = $this->sqlSearchWay($sql, $c_contact, 'c_contact');
        if ($c_tel2) {
            $c_tel2 = str_replace("'", '"', $c_tel2);
            $sql .= " AND (c_tel2 like '" . $c_tel2 . "' OR c_tel1 like '" . $c_tel2 . "')";
        }
        if ($presentDelayed){
            $dateTime = date("Y-m-d H:i:s");
            $sql .= <<<SQL
 AND TO_DATE('{$dateTime}','yyyy-mm-dd,hh24:mi:ss') > TO_DATE(NVL(H_EDI_20,H_EDI_03),'yyyy-mm-dd,hh24:mi:ss')+({$presentDelayed}/24)
  AND NVL(H_EDI_20,H_EDI_03) IS NOT NULL AND NVL(DOC_ORDER_HEADER.H_EDI_20,DOC_ORDER_HEADER.H_EDI_03) <> 'ERP订单取消' AND SOSTATUS < 63
  AND ADDTIME > TO_DATE('{$dateTime}','yyyy-mm-dd,hh24:mi:ss')-(({$presentDelayed}/24)+7)
SQL;
            $orderdate_start = null;$addtime = null;
        }
        if ($c_province) {
            $c_province = str_replace("'", '"', $c_province);
            if ($c_province == '空') $sql .= " AND c_province is null";
            else$sql .= " AND c_province like '" . $c_province . "'";
        }
        if ($c_city) {
            $c_city = str_replace("'", '"', $c_city);
            if ($c_city == '空') $sql .= " AND c_city is null";
            else$sql .= " AND c_city like '" . $c_city . "'";
        }
        if ($c_district) {
            $c_district = str_replace("'", '"', $c_district);
            if ($c_district == '空') $sql .= " AND c_district is null";
            else$sql .= " AND c_district like '" . $c_district . "'";
        }
        if ($releasestatus) {
            $releasestatus = str_replace("'", '"', $releasestatus);
            $sql .= " AND releasestatus = '" . $releasestatus . "'";
        }
        if ($alternate_sku1) {
            $alternate_sku1 = str_replace("'", '"', $alternate_sku1);
            if ($checkAllSign) $detailsOrderno = $this->getOrdersNo($alternate_sku1, false);
            else $detailsOrderno = $this->getOrdersNo($alternate_sku1, true, $params['page'] ?? 1, $params['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) {
            $orderdate_start = null;
            $orderdate_end = null;
        }
        if ($orderdate_start) {
            if (strlen($orderdate_start) < 11) $orderdate_start .= ' 00:00';
            $sql .= " and addtime>=to_date('" . $orderdate_start . ":00','yyyy-mm-dd hh24:mi:ss')";
        }
        if ($orderdate_end) {
            if (strlen($orderdate_end) < 11) $orderdate_end .= ' 23:59 ';
            $sql .= " and addtime<=to_date('" . $orderdate_end . ":59','yyyy-mm-dd hh24:mi:ss')";
        }
        if ($customerid) {
            if (!is_array($customerid)) $arr = explode(',', $customerid);
            else $arr = $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 = $this->sqlSearchWay($sql, $orderno, '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) {
            if (!is_array($carriername)) $arr = explode(',', $carriername);
            else $arr = $carriername;
            if (count($arr) > 0) {
                $sql .= " and carriername in (";
                foreach ($arr as $index => $str) {
                    if ($index == 0) {
                        $sql .= "'" . $str . "'";
                        continue;
                    }
                    $sql .= ",'" . $str . "'";
                }
                $sql .= ")";
            }
        }
        if ($waveno) $sql = $this->sqlSearchWay($sql, $waveno, 'WaveNo');
        if ($issuepartyname) $sql .= " and issuepartyname like '" . $issuepartyname . "%'";
        if ($notes) {
            if ($addtime && $addtime != 0) $sql .= " and addtime >= to_date('" . date('Y-m-d H:i:s', strtotime("-" . $addtime . " day")) . "','yyyy-mm-dd hh24:mi:ss')";
            $sql .= " and notes like '%" . $notes . "%'";
        }
        if ($soreference5) {
            $soreference5 = str_replace("'", '"', $soreference5);
            mb_regex_encoding('utf-8');
            $arr = array_values(array_filter(preg_split('/[,， ]+/u', $soreference5, -1, PREG_SPLIT_NO_EMPTY)));
            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 . "' OR edisendflag3 = '".$edisendflag2."')";
        if ($edisendflag) $sql .= " and edisendflag like '" . $edisendflag . "%'";
        if ($manualflag) $sql .= " and manualflag = '" . $manualflag . "'";
        if ($soreference1) {
            mb_regex_encoding('utf-8');
            $arr = array_values(array_filter(preg_split('/[,， ]+/u', $soreference1, -1, PREG_SPLIT_NO_EMPTY)));
            if (count($arr) == 1) {
                $sql .= " and soreference1 like '" . $soreference1 . "%'";
            }
            if (count($arr) > 1 && !strpos($soreference1, '%')) {
                $sql .= " and soreference1 in (";
                foreach ($arr as $index => $str) {
                    if ($index == 0) {
                        $sql .= "'" . $str . "'";
                        continue;
                    }
                    $sql .= ",'" . $str . "'";
                }
                $sql .= ")";
            }
            if ($params['soreference'] ?? false) $sql .= " and soreference1 like '" . $params['soreference'] . "'";
        }
        return $sql;
    }

    public function getSql($params, $page = null, $paginate = null)
    {
        if (isset($params["delayedDeliver"]) && is_numeric($params["delayedDeliver"]) && is_int((int)$params["delayedDeliver"])){
            if ($page)$page = 1;
            $this->delayedChangeParam($params, $page, $paginate);
        }

        $checktime_start = $params['checktime_start'] ?? null;
        $checktime_end = $params['checktime_end'] ?? null;
        $checkData = $params['data'] ?? null;
        if ($checktime_start || $checktime_end) {
            /** @var  OracleActAllocationDetailService */
            $ordernos = app('OracleActAllocationDetailService')
                ->getOrderno(['checktime_start' => $checktime_start, 'checktime_end' => $checktime_end,
                    'paginate' => $paginate, 'page' => $page]);
            if ($ordernos) $params['ordernos'] = $ordernos;
            else return null;
        }
        $sql = "SELECT * FROM (select ACT_ALLOCATION_DETAILS.CHECKTIME,DOC_ORDER_HEADER.addtime,DOC_ORDER_HEADER.C_PROVINCE,DOC_ORDER_HEADER.C_CITY,DOC_ORDER_HEADER.MANUALFLAG,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 as header_customer_id
        ,DOC_ORDER_HEADER.C_Tel2,DOC_ORDER_HEADER.C_Tel1,logistic.descr_c as CarrierName,DOC_ORDER_HEADER.IssuePartyName,DOC_ORDER_HEADER.EDIREMARKS2,
       DOC_ORDER_HEADER.WaveNo,DOC_ORDER_HEADER.SOReference1, DOC_ORDER_HEADER.SOREFERENCE5 as code5
        ,NVL(ACT_ALLOCATION_DETAILS.PICKTOTRACEID,DOC_ORDER_HEADER.soreference5) as 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.LOTNUM,DOC_Order_Details.LineStatus,DOC_Order_Details.OrderLineNo,order_code.codename_c as orderCodeName,
       order_detail_code.codename_c as orderDetailCodeName,owner.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($params, $sql);;
        $sql .= " ) as counted from (";
        if ($paginate && $page) $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.MANUALFLAG,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.C_Tel1,DOC_ORDER_HEADER.CarrierName,DOC_ORDER_HEADER.USERDEFINE1,DOC_ORDER_HEADER.IssuePartyName,
                         DOC_ORDER_HEADER.WaveNo,DOC_ORDER_HEADER.SOReference1
                          ,DOC_ORDER_HEADER.soreference5,nvl( DOC_ORDER_HEADER.EDISENDFLAG2, DOC_ORDER_HEADER.edisendflag3 ) as EDISENDFLAG2,nvl( DOC_ORDER_HEADER.EDISendTime2, DOC_ORDER_HEADER.EDISENDTIME3 ) 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($params, $sql);
        if ($checkData) {
            if (!is_array($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 (isset($params["alternate_sku1"])) $page = 1;
        if ($paginate && $page) $sql .= " and ROWNUM<='" . $page * $paginate . "')";
        if ($paginate && $page) $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 owner on owner.customerid=DOC_ORDER_HEADER.customerid
                      left join BAS_Customer logistic on logistic.customerid=DOC_ORDER_HEADER.USERDEFINE1
                      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
                      GROUP BY ACT_ALLOCATION_DETAILS.CHECKTIME,DOC_ORDER_HEADER.addtime,DOC_ORDER_HEADER.C_PROVINCE,DOC_ORDER_HEADER.C_CITY,DOC_ORDER_HEADER.MANUALFLAG,
                      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.C_Tel1,DOC_ORDER_HEADER.CarrierName,DOC_ORDER_HEADER.IssuePartyName,DOC_ORDER_HEADER.SOREFERENCE5,
                      DOC_ORDER_HEADER.EDIREMARKS2,DOC_ORDER_HEADER.WaveNo,DOC_ORDER_HEADER.SOReference1, NVL(ACT_ALLOCATION_DETAILS.PICKTOTRACEID,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.LOTNUM,
                      DOC_Order_Details.LineStatus,DOC_Order_Details.OrderLineNo,order_code.codename_c,order_detail_code.codename_c,owner.descr_c,logistic.descr_c,
                      BAS_SKU.Alternate_SKU1,BAS_SKU.Descr_C ORDER BY DOC_ORDER_HEADER.ADDTIME desc)";
        return $sql;

    }

    //通过商品条码获取订单编号
    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 paginate(array $params)
    {
        if (($params['is_display_all'] ?? false) && !($params['codename_c'] ?? false) && !($params['codename_c_end'] ?? false)) {
            $params['codename_c'] = "00";
            $params['codename_c_end'] = "62";
        }
        $sql = null;
        if ($params['soreference1'] ?? false) {
            $arr = array_values(array_filter(preg_split('/[,， ]+/is', $params['soreference1'])));
            if (count($arr) == 1) {
                $params['soreference'] = '';
                $sql = $this->getSql($params, $params['page'] ?? 1, $params['paginate'] ?? 50);
            }
            if (count($arr) > 1 && !strpos($params['soreference1'], '%')) {
                $params['soreference'] = '';
                $sql = $this->getSql($params, $params['page'] ?? 1, $params['paginate'] ?? 50);
            }
            if (count($arr) > 1 && strpos($params['soreference1'], '%')) {
                foreach ($arr as $index => $str) {
                    if ($index == 0) {
                        $params['soreference'] = $str;
                        $sql = $this->getSql($params, $params['page'] ?? 1, $params['paginate'] ?? 50);
                        continue;
                    }
                    $params['soreference'] = $str;
                    $sql .= " union " . $this->getSql($params, $params['page'] ?? 1, $params['paginate'] ?? 50);
                }
            }
        } else {
            $params['soreference'] = '';
            $sql = $this->getSql($params, $params['page'] ?? 1, $params['paginate'] ?? 50);
        }
        if ($sql) {
            $orders = DB::connection('oracle')->select(DB::raw($sql));
        } else $orders = [];
        return $this->orderFormat($orders);
    }

    public function get(array $params)
    {
        $sql = $this->getSql($params);
        if ($sql) $orders = DB::connection('oracle')->select(DB::raw($sql));
        else $orders = [];
        return $this->orderFormat($orders);
    }

    private function orderFormat($orders)
    {
        $commodities = [];
        $orderNos = "(''";
        $picktotraceids = [];
        $count = 1;
        /*
         * 第二种扁平化方式(节省空间)： 使用快慢指针，因拿到数据正常为顺序数据,自下标1开始与上一条对比orderno唯一标识，不同则更新慢指针下标，
         *  相同则为指定下标map类型数组追加一个键值对属性来记录同类数量,如N条，每次追加属性时先看属性是否存在，存在+1
         * */
        foreach ($orders as $index => $order) {
            $key = $order->orderno;
            if (isset($commodities[$key])) {
                if (!isset($signs[$order->orderlineno])) {
                    $signs[$order->orderlineno] = true;
                    array_push($commodities[$key],
                        ["sku" => $order->sku, "alternate_sku1" => $order->alternate_sku1, "descr_c" => $order->descr_c, "qtyordered" => $order->qtyordered
                            , "lotnum" => $order->lotnum, "checktime" => $order->checktime, "orderdetailcodename" => $order->orderdetailcodename]);
                }
                unset($orders[$index]);
                continue;
            }
            $signs = [];
            $signs[$order->orderlineno] = true;
            $commodities[$key] = [["sku" => $order->sku, "alternate_sku1" => $order->alternate_sku1,
                "descr_c" => $order->descr_c, "qtyordered" => $order->qtyordered, "lotnum" => $order->lotnum, "checktime" => $order->checktime, "orderdetailcodename" => $order->orderdetailcodename]];
            $orderNos .= ",'" . $order->orderno . "'";
            if ($count % 500 == 0) {
                $orderNos .= ")";
                $allocations = DB::connection("oracle")->select(DB::raw("SELECT DROPID,ORDERNO FROM ACT_ALLOCATION_DETAILS WHERE orderno IN " . $orderNos . " GROUP BY (DROPID,ORDERNO)"));
                foreach ($allocations as $allocation) {
                    if (isset($picktotraceids[$allocation->orderno])) $picktotraceids[$allocation->orderno][] = $allocation->dropid;
                    else $picktotraceids[$allocation->orderno] = [$allocation->dropid];
                }
                $orderNos = "(''";
            }
            $count++;
        }
        if ($orderNos !== "(''") {
            $orderNos .= ")";
            $allocations = DB::connection("oracle")->select(DB::raw("SELECT DROPID,ORDERNO FROM ACT_ALLOCATION_DETAILS WHERE orderno IN " . $orderNos . " GROUP BY (DROPID,ORDERNO)"));
            foreach ($allocations as $allocation) {
                if (isset($picktotraceids[$allocation->orderno])) $picktotraceids[$allocation->orderno][] = $allocation->dropid;
                else $picktotraceids[$allocation->orderno] = [$allocation->dropid];
            }
        }
        $picktotraceids = json_encode($picktotraceids);
        $orders = array_values($orders);
        $orders = new Collection($orders);
        $commodities = new Collection($commodities);
        return ['picktotraceids' => $picktotraceids, 'orders' => $orders, 'commodities' => $commodities];
    }

    public function findOrCreateByClientCode($clientCode)
    {
        /** @var OracleDOCOrderHeaderService $orderHeaderService */
        $orderHeaderService = app('OracleDocOrderHeaderService');
        $orderHeaders = $orderHeaderService->getQuery()->where('SOReference1', $clientCode)->get();
        $this->syncOrderInfo($orderHeaders, false);
        return Order::with(['packages.commodities.commodity', 'logistic'])->where('client_code', $clientCode)->first();
    }

    public function getRejectedBillOfClientCode($clientCode)
    {
        $orderHeader = OracleDOCOrderHeader::query()->where('SOReference1', $clientCode)->first();
        if (!$orderHeader) {
            return null;
        }
        $ASNHeader = OracleDOCASNHeader::query()->where('ASNReference2', $orderHeader->soreference2)->first();
        if (!$ASNHeader) {
            return null;
        }
        return RejectedBill::query()->with(['owner', 'logistic', 'items'])->where('logistic_number_return', $ASNHeader->asnreference2)->first();
    }

    public function findOrCreteByLogisticNumberReturn($logisticNumberReturn)
    {
        $orderHeader = OracleDOCOrderHeader::query()->where('SOReference1', function ($query) use ($logisticNumberReturn) {
            $query->from('Doc_ASN_Header')->select('ASNReference2')->where('ASNReference3', $logisticNumberReturn);
        })->first();
        if (!$orderHeader) return null;
        return $this->findOrCreateByClientCode($orderHeader['soreference1']);
    }

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

    public function logisticNumberFirstOrCreateOrder($logistic_number)
    {
        /**
         * @var OracleActAllocationDetailService
         * @var OracleActAllocationDetails $actAllocationDetail
         */
        $actAllocationDetail = app('OracleActAllocationDetailService')->first(['picktotraceid' => $logistic_number]);
        if ($actAllocationDetail) {
            $order = $this->first(['code' => $actAllocationDetail->orderno]);
            if ($order) return $order;

            $actAllocationDetail->load(['oracleDocOrderHeader' => function ($query) {
                $query->with('oracleBASCode');
            }]);
            $orderHeader = $actAllocationDetail->oracleDocOrderHeader ?? null;
        } else {
            $orderHeader = app('OracleDocOrderHeaderService')->first(['soreference5' => $logistic_number]);
        }
        if ($orderHeader == null) return null;
        $order = $this->first(['code' => $orderHeader->orderno]);
        if ($order) return $order;
        /** @var OwnerService */
        $owner = app('OwnerService')->first(['code' => $orderHeader->customerid]);
        if (!$owner) {
            $orderHeader->load('oracleBASCustomer');
            $customer_name = $orderHeader->oracleBASCustomer ? $orderHeader->oracleBASCustomer->descr_c : null;
            /** @var OwnerService */
            if ($customer_name) $owner = app('OwnerService')->create(['code' => $orderHeader->customerid, 'name' => $customer_name]);
        }

        $shop = null;
        $shop_name = $orderHeader->issuepartyname;
        if ($shop_name && $owner) {
            /** @var ShopService */
            $shop = app('ShopService')->firstOrCreate(['name' => $shop_name, 'owner_id' => $owner->id]);
        }
        $logistics = app('LogisticService')->firstOrCreate(['code' => $orderHeader->userdefine1],
            ['code' => $orderHeader->userdefine1, 'name' => $orderHeader->carriername]);
        $wareHouse = Warehouse::query()->firstOrCreate(['code' => $orderHeader->warehouseid],
            ['name' => $orderHeader->warehouseid, 'code' => $orderHeader->warehouseid]);

        try {
            $order = Order::query()->create([
                'warehouse_id' => $wareHouse->id ?? '',
                'code' => $orderHeader->orderno,
                'owner_id' => $owner ? $owner->id : null,
                'wms_status' => $orderHeader->oracleBASCode ? $orderHeader->oracleBASCode->codename_c : null,
                'created_at' => $orderHeader->addtime,
                'logistic_id' => $logistics ? $logistics->id : null,
                'shop_id' => $shop ? $shop->id : null,
                'consignee_name' => $orderHeader->c_contact,
                'consignee_phone' => empty($orderHeader->c_tel2) ? $orderHeader->c_tel1 : $orderHeader->c_tel2,
                'province' => $orderHeader->c_province,
                'city' => $orderHeader->c_city,
                'district' => $orderHeader->c_district,
                'address' => $orderHeader->c_address1,
                'client_code' => $orderHeader->soreference1,
            ]);
        } catch (\Exception $e) {
            return $this->first(['code' => $orderHeader->orderno]);
        }
        app('LogService')->log(__METHOD__, __FUNCTION__, '创建 Order ' . json_encode($order));
        return $order;
    }

    public function create(array $params)
    {
        if (count($params) == 0) return [];
        try {
            $this->insert($params);
        } catch (\Exception $e) {
            app('LogService')->log(__METHOD__, __FUNCTION__, '批量创建 order ' . json_encode($params) . '||' . $e->getMessage() . '||' . $e->getTraceAsString());
        } finally {
            return Order::query()->whereIn('code', data_get($params, '*.code'))->get();
        }
    }

    public function getByWmsOrders($orderHeaders)
    {
        if (!$orderHeaders) {
            return null;
        }
        $order_nos = data_get($orderHeaders, '*.orderno');
        return Order::query()->with('packages.commodities')->whereIn('code', $order_nos)->get();
    }

    public function 返回有问题件的订单号($orders)
    {
        if (!isset($orders)) {
            return [];
        }
        $orderNo = data_get($orders, '*.orderno');
        $orderIssues = OrderIssue::query()->with('order')
            ->whereIn('order_id', function ($query) use ($orderNo) {
                $query->from('orders')->select('id')->whereIn('code', $orderNo);
            })->get();
        return $orderIssues->map(function ($orderIssue) {
            return $orderIssue->order->code;
        });
    }

    public function getParamByOrderHeader($orderHeader)
    {
        $logistic = Logistic::query()->where('code', $orderHeader->userdefine1)->first();
        $owner = Owner::query()->where('code', $orderHeader->customerid ?? '')->first();
        $shop = Shop::query()->where('name', $orderHeader->issuepartyname)->where('owner_id', $owner->id)->first();
        $warehouse = app('WarehouseService')->firstOrCreate(["code" => $orderHeader->warehouseid], ["code" => $orderHeader->warehouseid, "name" => $orderHeader->warehouseid]);
        return [
            'code' => $orderHeader->orderno,
            'owner_id' => $owner->id ?? '',
            'shop_id' => $shop->id ?? '',
            'logistic_id' => $logistic->id ?? '',
            'consignee_name' => $orderHeader->c_contact,
            'consignee_phone' => empty($orderHeader->c_tel2) ? $orderHeader->c_tel1 : $orderHeader->c_tel2,
            'province' => $orderHeader->c_province,
            'city' => $orderHeader->c_city,
            'district' => $orderHeader->c_district,
            'address' => $orderHeader->c_address1,
            'client_code' => $orderHeader->soreference1,
            'wms_edittime' => $orderHeader->edittime,
            'wms_status' => $orderHeader->oracleBASCode_codename_c ?? '',
            'updated_at' => (string)Carbon::now()->format('Y-m-d H:i:s'),
            'created_at' => $orderHeader->addtime,
            'warehouse_id' => $warehouse->id ?? ''
        ];
    }


    public function getOrderByLogisticNumber($logisticNumber)
    {
        /** @var OracleDOCOrderHeaderService $orderHeaderService */
        $orderHeaderService = app('OracleDocOrderHeaderService');
        $orderHeaders = $orderHeaderService->getQuery()->whereIn('OrderNo', function ($query) use ($logisticNumber) {
            $query->from('Act_Allocation_Details')->select('OrderNo')->where('picktotraceid', $logisticNumber);
        })->first();
        if (!$orderHeaders) return null;
        $this->syncOrderInfo($orderHeaders);
        $code = $orderHeaders->first()->code;
        return Order::query()->with('packages.commodities.commodity')->where('code', $code)->first();
    }

    /**
     * 同步 Order信息
     *
     * @param array $orderHeaders
     * @param bool $syncOrderCommodity
     * @param bool $syncOrderPackage
     * @param bool $syncOrderPackageCommodity
     */
    public function syncOrderInfo($orderHeaders, $syncOrderCommodity = true, $syncOrderPackage = true, $syncOrderPackageCommodity = true)
    {
        $this->syncOrderByWMSOrderHeaders($orderHeaders);
        if ($syncOrderCommodity) app("OrderCommodityService")->syncOrderCommodity($orderHeaders);
        if ($syncOrderPackage) app('OrderPackageService')->syncOrderPackage($orderHeaders);
        if ($syncOrderPackageCommodity) app("OrderPackageCommoditiesService")->syncOrderPackageCommodities($orderHeaders);
    }

    public function syncOrder(&$orderHeaders)
    {
        $this->syncOrderByWMSOrderHeaders($orderHeaders);
        app("OrderCommodityService")->syncOrderCommodity($orderHeaders);
        app('OrderPackageService')->syncOrderPackage($orderHeaders);
        app("OrderPackageCommoditiesService")->syncOrderPackageCommodities($orderHeaders);
        $this->pushQueue($orderHeaders);
        unset($orderHeaders);
    }

    public function pushQueue($orderHeaders)
    {
        $orders = Order::query()->with(["logistic", "packages.commodities.commodity"])->where('wms_status', '订单完成')->whereIn('code', data_get($orderHeaders, '*.orderno'))->get();
        foreach ($orders->chunk(50) as $or) {
            dispatch(new OrderCreateInstantBill($or));
        }
    }

    public function syncOrderByWMSOrderHeaders(&$orderHeaders)
    {
        if ($orderHeaders->isEmpty()) return;
        /**
         * @var OwnerService $ownerService
         * @var LogisticService $logisticService
         * @var ShopService $shopService
         * @var WarehouseService $warehouseService
         */
        $ownerService = app("OwnerService");
        $logisticService = app("LogisticService");
        $shopService = app('ShopService');
        $warehouseService = app('WarehouseService');

        $owner_codes = [];
        $logistic_codes = [];
        $warehouse_codes = [];
        $shop_names = [];
        $batch_codes = [];
        foreach ($orderHeaders as $orderHeader) {
            if (!empty($orderHeader['waveno']))
                $batch_codes[$orderHeader['waveno']] = $orderHeader['waveno'];
            if (!empty($orderHeader['customerid']))
                $owner_codes[$orderHeader['customerid']] = $orderHeader['customerid'];
            if (!empty($orderHeader['warehouseid']))
                $warehouse_codes[$orderHeader['warehouseid']] = $orderHeader['warehouseid'];
            if (!empty($orderHeader['userdefine1']))
                $logistic_codes[$orderHeader['userdefine1']] = $orderHeader['userdefine1'];
            if (!empty($orderHeader['issuepartyname']) && !empty($orderHeader['customerid'])) {
                // 商品换行符处理
                $shop_name = str_replace("\n", ' ', $orderHeader['issuepartyname']);
                $value = ['owner_code' => $orderHeader['customerid'], 'issuepartyname' => $shop_name];
                $key = "owner_code_{$orderHeader['customerid']}_issuepartyname_{$shop_name}";
                $shop_names[$key] = $value;
            }
        }

        $owners = $ownerService->getOwnerByCodes($owner_codes);
        $logistics = $logisticService->getLogisticByCodes($logistic_codes);
        $shops = $shopService->getShopByCodeMap($shop_names);
        $warehouses = $warehouseService->getWareHouseByCodes($warehouse_codes);
        $batches = $this->batchService->getBatchByCodes($batch_codes);

        unset($owner_codes, $logistic_codes, $shop_names, $warehouse_codes);

        $orders = $this->getByWmsOrders($orderHeaders);

        $created_params = $this->getCreateOrderModelsByWMSOrderHeaders($orderHeaders, $orders, $warehouses, $owners, $logistics, $shops, $batches); //3s

        $update_order = $this->getUpdateOrderModelByWMSOrderHeaders($orderHeaders, $orders, $warehouses, $owners, $logistics, $shops, $batches);

        unset($owners, $logistics, $shops, $warehouses, $batches);

        foreach ($update_order as $item) {
            if ($item['batch_id'] == null) {
                LogService::log(__METHOD__, 'batch_id is null', json_encode($batch_codes) . $item['code']);
                break;
            }
        }

        unset($batch_codes);
        // 转换插入 3s
        $this->pushOrderCreatedCountCache($created_params);
        if (count($created_params) > 0) {
            collect($created_params)->chunk(1000)->each(function ($inner_params) {
                $arr = $inner_params->toArray();
                $this->insert($arr, false, false, false);
                dispatch(new OrderFreeze($arr));
                //dispatch(new OrderCreateWaybill($arr));
            });
        }
        unset($created_params);
        $update_params = [
            ['id', 'code', 'warehouse_id', 'owner_id', 'batch_id', 'shop_id', 'logistic_id', 'consignee_name', 'consignee_phone', 'province', 'city', 'district', 'address', 'client_code', 'wms_status', 'created_at', 'updated_at', 'wms_edittime', 'order_type', 'frozen']
        ];
        $update_order->each(function ($item) use (&$update_params) {
            $update_params[] = $item->toArray();
        });
        unset($update_order);
        if (count($update_params) == 0) return;
        $this->pushOrderUpdatedCountCache($orders, $update_params);
        $this->batchUpdate($update_params);
        dispatch(new OrderFreeze($update_params));
        unset($update_params);
    }

    public function getCreateOrderModelsByWMSOrderHeaders(&$orderHeaders, &$orders, &$warehouses, &$owners, &$logistics, &$shops, &$batches)
    {
        /** @var DataHandlerService $dataHandlerService */
        $dataHandlerService = app('DataHandlerService');
        $warehouse_map = $dataHandlerService->dataHeader(['code'], $warehouses);
        $owner_map = $dataHandlerService->dataHeader(['code'], $owners);
        $logistic_map = $dataHandlerService->dataHeader(['code'], $logistics);
        $shop_map = $dataHandlerService->dataHeader(['name', 'owner_id'], $shops);
        $order_map = $dataHandlerService->dataHeader(['code'], $orders);
        $batch_map = $dataHandlerService->dataHeader(['code'], $batches);
        $inner_params = [];
        $date = (string)Carbon::now();
        foreach ($orderHeaders as $orderHeader) {
            $order_no = $orderHeader->orderno;
            $order = $dataHandlerService->getKeyValue(['code' => $order_no], $order_map);
            if (isset($order)) continue;
            $order_model = $this->getCreateOrderModels($orderHeader, $warehouse_map, $owner_map, $logistic_map, $shop_map, $batch_map, $date);
            $inner_params[] = $order_model;
        }
        return $inner_params;
    }

    public function getUpdateOrderModelByWMSOrderHeaders(&$orderHeaders, &$orders, &$warehouses, &$owners, &$logistics, &$shops, &$batches)
    {
        /**
         * @var DataHandlerService $dataHandlerService
         * @var Order $order
         */
        $dataHandlerService = app('DataHandlerService');
        $orderHeader_map = $dataHandlerService->dataHeader(['orderno'], $orderHeaders);
        $warehouse_map = $dataHandlerService->dataHeader(['code'], $warehouses);
        $owner_map = $dataHandlerService->dataHeader(['code'], $owners);
        $logistic_map = $dataHandlerService->dataHeader(['code'], $logistics);
        $shop_map = $dataHandlerService->dataHeader(['name', 'owner_id'], $shops);
        $batch_map = $dataHandlerService->dataHeader(['code'], $batches);
        $collect = collect();
        $date = (string)Carbon::now();
        foreach ($orders as $order) {
            $orderHeader = $dataHandlerService->getKeyValue(['orderno' => $order->code], $orderHeader_map);
            if (!$orderHeader) continue;
            $order_model = $this->getCreateOrderModels($orderHeader, $warehouse_map, $owner_map, $logistic_map, $shop_map, $batch_map, $date);
            if (!$order->isEquals($order_model)) {
                $order->assignValueByOrder($order_model);
                $collect->push($order);
            }
        }
        return $collect;
    }

    public function getCreateOrderModels(&$orderHeader, &$warehouse_map, &$owner_map, &$logistic_map, &$shop_map, &$batch_map, $date)
    {
        /** @var DataHandlerService $dataHandlerService */
        $dataHandlerService = app('DataHandlerService');
        $warehouse = $dataHandlerService->getKeyValue(['code' => $orderHeader->warehouseid], $warehouse_map);
        $owner = $dataHandlerService->getKeyValue(['code' => $orderHeader->customerid], $owner_map);
        $logistic = $dataHandlerService->getKeyValue(['code' => $orderHeader->userdefine1], $logistic_map);
        $batch = $dataHandlerService->getKeyValue(['code' => $orderHeader->waveno], $batch_map);
        $shop['id'] = null;
        if ($orderHeader->issuepartyname != null && $orderHeader->issuepartyname != '') {
            // 商品换行符处理
            $shop_name = str_replace("\n", ' ', $orderHeader['issuepartyname']);
            $shop = $dataHandlerService->getKeyValue(['name' => $shop_name ?? '', 'owner_id' => $owner->id ?? ''], $shop_map);
        }
        return [
            'code' => $orderHeader['orderno'],
            'batch_id' => $batch['id'] ?? null,
            'warehouse_id' => $warehouse['id'] ?? null,
            'owner_id' => $owner['id'] ?? null,
            'shop_id' => $shop['id'] ?? null,
            'logistic_id' => $logistic['id'] ?? null,
            'consignee_name' => $orderHeader['c_contact'],
            'consignee_phone' => $orderHeader['c_tel2'] ?? $orderHeader['c_tel1'],
            'province' => $orderHeader['c_province'],
            'city' => $orderHeader['c_city'],
            'district' => $orderHeader['c_district'],
            'address' => $orderHeader['c_address1'],
            'client_code' => $orderHeader['soreference1'],
            'wms_edittime' => $orderHeader['edittime'],
            'wms_status' => $orderHeader->oracleBASCode->codename_c ?? '',
            'order_type' => $orderHeader->orderType->codename_c ?? '',
            'frozen' => Order::getFrozen($orderHeader->releasestatus),
            'updated_at' => $date,
            'created_at' => $orderHeader['addtime'],
            'pay_at' => $orderHeader['h_edi_20'] ?? $orderHeader['h_edi_03'],
        ];
    }

    public function createOrFindOrder($orderHeader)
    {
        $order = Order::query()->where('code', $orderHeader->orderno)->first();
        if ($order) return $order;
        $create_params = $this->getParamByOrderHeader($orderHeader);
        try {
            $order = Order::query()->create($create_params);
            app('LogService')->log(__METHOD__, __FUNCTION__, '创建 Order' . json_encode($order));
            return $order;
        } catch (\Exception $e) {
            app('LogService')->log(__METHOD__, 'Error' . __FUNCTION__, '创建 Order Error' . ' || ' . json_encode($order) . ' || ' . json_encode($e->getMessage()) . ' || ' . json_encode($e->getTraceAsString()));
            return null;
        }
    }

    public function createOrFindOrderInfo($orderHeader)
    {
        /**
         * @var OrderPackageService $orderPackageService
         * @var OrderPackageCommoditiesService $orderPackageCommoditiesService
         */
        $orderPackageService = app('OrderPackageService');
        $orderPackageCommoditiesService = app('OrderPackageCommoditiesService');
        $orderHeaders = collect();
        $orderHeaders->push($orderHeader);
        try {
            $this->syncOrderByWMSOrderHeaders($orderHeaders);
            $orderPackageService->syncPackageByOrderHeaders($orderHeaders);
            $orderPackageCommoditiesService->syncOrderPackageCommodities($orderHeaders);
            return Order::query()->with('packages.commodities')->where('code', $orderHeader->orderno)->first();
        } catch (\Exception $e) {
            app('LogService')->log(__METHOD__, 'Error' . __FUNCTION__, '创建 Order Error' . ' || ' . json_encode($orderHeader) . ' || ' . json_encode($e->getMessage()) . ' || ' . json_encode($e->getTraceAsString()));
            return null;
        }
    }

    public function setOrderSyncAt($key, $date, $bool = true)
    {
        if ($bool) {
            ValueStore::query()->where('name', $key)->update(['value' => Carbon::parse($date)->subSeconds(1)]);
        } else {
            ValueStore::query()->where('name', $key)->update(['value' => $date]);
        }
    }

    public function getOrderSyncAt($key, $type = 'newest')
    {
        $keyValue = ValueStore::query()->where('name', $key)->first();
        if ($keyValue->value) return $keyValue->value;
        if ($type == 'newest') $order = Order::query()->orderByDesc('Created_At')->first();
        else $order = Order::query()->orderByDesc('Wms_EditTime')->first();
        if ($order) return $order->created_at;
        return Carbon::now()->subSeconds(65);
    }

    public function pushOrderCache($orderHeaders, $prefixKey, $hasKey, $list_key)
    {
        $list = [];
        try {
            Cache::get($hasKey);
            collect($orderHeaders)->each(function ($item) use (&$list, $prefixKey) {
                $list[] = $item->orderno;
                Cache::put($prefixKey . $item->orderno, true);
            });
            if (count($orderHeaders) > 0) {
                Cache::put($list_key, $list);
                Cache::put($hasKey, true);
            }
        } catch (\Exception $e) {

        }
    }

    public function filterOrderByCache($orderHeaders, $key)
    {
        /**
         * @var OracleDOCOrderHeader $item
         */
        if (!$orderHeaders) return $orderHeaders;
        if (!$key) return $orderHeaders;
        try {
            Cache::get($key);
            return array_filter($orderHeaders, function ($item) use ($key) {
                $bool = Cache::get($key . $item->orderno);
                return !$bool;
            });
        } catch (\Exception $e) {
            return $orderHeaders;
        }
    }

    public function cancelOrderCache($key, string $prefix)
    {
        try {
            Cache::get($prefix);
            $list = Cache::get($key);
            collect($list)->each(function ($item) use ($prefix) {
                Cache::forget($prefix . $item);
            });
            Cache::put($key, []);
        } catch (\Exception $e) {
        }
    }

    public function syncCreatedOrder()
    {
        /**
         * @var OracleDOCOrderHeaderService $oracleDOCOrderHeaderService
         * @var OrderService $orderService
         */
        $newest = config('sync.order_sync.cache_prefix.created_at');
        $have_task_is_running_key = config('sync.order_sync.cache_prefix.newest_is_running');
        $newest_list = config('sync.order_sync.cache_prefix.newest_list');
        $hasKey = config('sync.order_sync.cache_prefix.newest_has');
        $prefixKey = config('sync.order_sync.cache_prefix.newest');

        $have_task_is_running = Cache::get($have_task_is_running_key);
        if ($have_task_is_running) return;
        Cache::remember($have_task_is_running_key, config('cache.expirations.forever'), function () {
            return true;
        });

        ini_set('memory_limit', '512M');
        $orderService = app('OrderService');
        $oracleDOCOrderHeaderService = app('OracleDocOrderHeaderService');

        $last_date = $orderService->getOrderSyncAt($newest, 'newest');                             // 获取时间点

        $orderHeaders = $oracleDOCOrderHeaderService->getWMSOrderOnStartDate($last_date);               // WMS订单
        if ($orderHeaders->count() == 0) return;
        $last_order = $orderHeaders->first();                                                           // 时间点靠后的
        $newest_orders = $orderHeaders->where('addtime', $last_order->addtime);

        $orderHeaders = $orderService->filterOrderByCache($orderHeaders, $newest_list);                  // 对比缓存

        if (count($newest_orders) > 0 && count($orderHeaders) > 0) {

            $orderService->syncOrder($orderHeaders);                                                //  同步订单
            $orderService->cancelOrderCache($newest_list, $prefixKey);                                   //  清除缓存
            $orderService->pushOrderCache($newest_orders, $prefixKey, $hasKey, $newest_list);              //  添加缓存
            $orderService->setOrderSyncAt($newest, $last_order->addtime, count($orderHeaders) > 0);   //  更新时间
        }

        Cache::remember($have_task_is_running_key, config('cache.expirations.forever'), function () {
            return false;
        });
    }

    public function syncUpdatedOrder()
    {
        /**
         * @var OracleDOCOrderHeaderService $oracleDOCOrderHeaderService
         * @var OrderService $orderService
         */
        $renewal = config('sync.order_sync.cache_prefix.updated_at');
        $have_task_is_running_key = config('sync.order_sync.cache_prefix.renewal_is_running');
        $renewal_list = config('sync.order_sync.cache_prefix.renewal_list');
        $hasKey = config('sync.order_sync.cache_prefix.renewal_has');
        $prefixKey = config('sync.order_sync.cache_prefix.renewal');

        $have_task_is_running = Cache::get($have_task_is_running_key);
        if ($have_task_is_running) return;
        Cache::remember($have_task_is_running_key, config('cache.expirations.forever'), function () {
            return true;
        });

        ini_set('memory_limit', '512M');

        $orderService = app('OrderService');
        $oracleDOCOrderHeaderService = app('OracleDocOrderHeaderService');

        $last_date = $orderService->getOrderSyncAt($renewal, 'renewal');                               // 获取时间点


        $orderHeaders = $oracleDOCOrderHeaderService->getWMSOrderOnEditDate($last_date);                   // WMS订单

        if ($orderHeaders->count() == 0) return;
        $renewal_order = $orderHeaders->first();                                                            // 时间点靠后的
        $renewal_orders = $orderHeaders->where('edittime', $renewal_order->edittime);

        $orderHeaders = $orderService->filterOrderByCache($orderHeaders, $renewal_list);                     // 对比缓存

        if (count($renewal_orders) > 0 && count($orderHeaders) > 0) {

            $orderService->syncOrder($orderHeaders);                                                    // 同步订单
            $orderService->cancelOrderCache($renewal_list, $prefixKey);                                      // 清除缓存
            $orderService->pushOrderCache($renewal_orders, $prefixKey, $hasKey, $renewal_list);                // 添加缓存
            $orderService->setOrderSyncAt($renewal, $renewal_order->edittime, count($orderHeaders) > 0);   // 更新时间
        }

        Cache::remember($have_task_is_running_key, config('cache.expirations.forever'), function () {
            return false;
        });
    }

    /**
     * 根据 订单号集合 OrderNo 同步订单
     * @param array $orderNos
     */
    public function syncOrderInfoByWmsOrderNos($orderNos)
    {
        if (empty($orderNos)) return;
        if (count($orderNos) == 0) return;
        $orderHeaders = app('OracleDocOrderHeaderService')->getOrderInfoByOrderNos($orderNos);
        $this->syncOrderInfoByWMSOrderHeaders($orderHeaders);
    }

    /**
     * 根据表头信息 OrderHeader 集合 同步订单
     * @param array $orderHeaders
     */
    public function syncOrderInfoByWMSOrderHeaders($orderHeaders)
    {
        if (empty($orderHeaders)) return;
        if (count($orderHeaders) == 0) return;
        $orderHeaders = app('OracleDocOrderHeaderService')->loadMissing($orderHeaders);
        $this->syncOrderByWMSOrderHeaders($orderHeaders);
        app('OrderPackageService')->syncOrderPackage($orderHeaders);
        app("OrderPackageCommoditiesService")->syncOrderPackageCommodities($orderHeaders);
    }

    public function update(array $params, array $values)
    {
        $query = Order::query();
        foreach ($params as $column => $param) {
            if (is_array($param)) $query->whereIn($column, $param);
            else $query->where($column, $param);
        }
        return $query->update($values);
    }

    /**
     * 直接生成order
     *
     * @param array $values
     * @return Model
     */
    public function createOrder(array $values)
    {
        return Order::query()->create($values);
    }

    /**
     * 订单量丢失补偿逻辑
     *
     * @param int $owner
     * @param array|null $logistics
     * @param string|null $month
     */
    private function orderQuantityCompensationLogic(int $owner, ?array $logistics = null, ?string $month = null)
    {
        if (!$logistics) $logistics = app("OwnerPriceExpressService")->getBuildLogistic($owner);
        if (!$month)$month = date("Y-m");
        $query = DB::raw(<<<sql
SELECT COUNT(1) count,logistic_id FROM orders WHERE wms_status = '订单完成'
AND owner_id >= ?
AND wms_edittime >= ? and wms_edittime <= ?   GROUP BY logistic_id
sql
        );
        $startDate = $month.'-01 00:00:00';
        $endDate = Carbon::parse($startDate)->addMonth()->subMicrosecond()->toDateTimeString();
        $statistics = DB::select($query, [$owner, $startDate,$endDate]);
        $toB = 0;
        $toC = 0;
        foreach ($statistics as $statistic) {
            if (array_search($statistic->logistic_id, $logistics) === false) $toB += $statistic->count;
            else $toC += $statistic->count;
        }
        Cache::put($month . "|B|" . $owner, $toB, 2764800);
        Cache::put($month . "|C|" . $owner, $toC, 2764800);
    }

    /**
     * 设置订单量
     *
     * @param int|null $owner
     * @param int|null $logistic
     * @param string|null $month
     */
    public function setOrderQuantity(?int $owner, ?int $logistic, ?string $month = null)
    {
        if (!$owner || !$logistic) return;
        $logistics = app("OwnerPriceExpressService")->getBuildLogistic($owner);
        $date = $month ?: date("Y-m");
        if (array_search($logistic, $logistics) === false) $type = "|B|";
        else $type = "|C|";
        if (!Cache::has($date . $type . $owner)) {
            //补偿逻辑
            $this->orderQuantityCompensationLogic($owner, $logistics, $month);
        }
        Cache::increment($date . $type . $owner);
        $key = $date . "|D|" . $owner;
        if (!Cache::has($key) || time() - Cache::get($key) > 60) {
            //向数据库持久化
            OwnerReport::query()->where("owner_id", $owner)
                ->where("counting_month", "like", $date . "%")
                ->update(["to_business_quantity" => Cache::get($date . "|B|" . $owner), "to_customer_quantity" => Cache::get($date . "|C|" . $owner)]);
        }
        Cache::put($key, time());
    }

    /**
     * 获取货主下有效订单量 对C端
     *
     * @param int $owner
     * @param bool $isToB
     * @param string|null $month
     *
     * @return int
     */
    public function getOrderQuantity(int $owner, bool $isToB = false, ?string $month = null): int
    {
        $date = $month ?: date("Y-m");
        if ($isToB) $type = "|B|";
        else $type = "|C|";
        if (!Cache::has($date . $type . $owner)) $this->orderQuantityCompensationLogic($owner);
        return Cache::get($date . $type . $owner);
    }

    /**
     * 获取物流费用信息
     *
     * @param Order|\stdClass $order
     * @param string $month
     *
     * @return array
     */
    public function getLogisticFeeInfo(Order $order,string $month): array
    {
        //预定义返回参数
        $default = function () {
            return [
                "amount" => 0, "logisticBill" => null, "volume" => 0,
                "weight" => 0, "logisticFee" => null, "logisticTaxFee" => null,
                "items" => [], "fee_info" => [],
            ];
        };
        $result = $default();
        if (!$order->logistic) return $result;
        //21-8-12 询问业务方(周旭)确定物流单都会在运输管理推单 所以此处直接排除物流单
        if ($order->logistic->type == '物流') return $result;
        //获取城市
        $province = app("ProvinceService")->getProvince($order->province);
        $unit = app("UnitService")->getUnit();
        $city = app("CityService")->getCity($order->city);
        $isBunched = $order->logistic->is_bunched == 'Y';
        $exe = function ($type, $ownerId, $logisticId, $amount, $weight) use ($province, $unit, $city, $month) {
            switch ($type) {
                case "快递":
                    /** @var OwnerPriceExpressService $service */
                    $service = app("OwnerPriceExpressService");
                    return $service->matching($weight, $ownerId, $logisticId, $province->id, $month);
                default:
                    /** @var OwnerPriceLogisticService $service */
                    $service = app("OwnerPriceLogisticService");
                    return $service->matching($amount, $ownerId, $logisticId, $unit->id, $province->id, $city->id);
            }
        };
        //默认的提取信息模板
        $defaultInfo = function ($number, $weight) use ($province, $order) {
            return [
                "province_id" => $province->id,
                "owner_id" => $order->owner_id,
                "logistic_id" => $order->logistic->id,
                "logistic_number" => $number,
                "weight" => $weight,
                "initial_weight" => 0,
                "initial_weight_price" => 0,
                "additional_weight" => 0,
                "additional_weight_amount" => 0,
                "additional_weight_price" => 0,
                "total_fee" => 0,
                "tax_rate" => 0,
                "created_at" => $order->wms_edittime ?: $order->updated_at,
            ];
        };
        foreach ($order->packages as $package) {
            if (!$package->weight || !$province) return $default(); //任一包裹不存在重量 放弃所有计算 直接跳出
            $tax = $fee = null;
            $result["logisticBill"] .= $package->logistic_number . ",";
            $result["volume"] += $package->bulk;
            $result["weight"] += $package->weight;
            $partAmount = 0;
            foreach ($package->commodities as $commodity) $partAmount += $commodity->amount;
            $result["amount"] += $partAmount;

            if (!$isBunched) {
                $GLOBALS["FEE_INFO"] = $defaultInfo($package->logistic_number, $package->weight);
                list($fee, $tax) = $exe($order->logistic->type, $order->owner_id, $order->logistic_id, $partAmount, $package->weight);
                $result["logisticFee"] += $fee;
                $result["logisticTaxFee"] += $tax;
                $GLOBALS["FEE_INFO"]["total_fee"] = $fee ?: 0;
                if ($order->logistic->type == '快递') $result["fee_info"][] = $GLOBALS["FEE_INFO"];
            }
            $result["items"][] = [
                "amount" => $partAmount,
                "logistic_bill" => $package->logistic_number,
                "volume" => $package->bulk,
                "weight" => $package->weight,
                "logistic_fee" => $fee,
                "tax_fee" => $tax,
            ];
        }
        $result["logisticBill"] = rtrim($result["logisticBill"], ",");
        /* 子母单计算 */
        if ($isBunched && $province) {
            $GLOBALS["FEE_INFO"] = $defaultInfo($result["logisticBill"], $result["weight"]);

            list($result["logisticFee"], $result["logisticTaxFee"]) =
                $exe($order->logistic->type, $order->owner_id, $order->logistic_id, $result["amount"], $result["weight"]);
            $GLOBALS["FEE_INFO"]["total_fee"] = $result["logisticFee"] ?: 0;
            if ($order->logistic->type == '快递') $result["fee_info"][] = $GLOBALS["FEE_INFO"];
        }
        if ($result["logisticFee"] < 0) $result["logisticFee"] = null;
        if ($result["logisticTaxFee"] < 0) $result["logisticTaxFee"] = null;
        return $result;
    }

    /**
     * 生成即时账单
     *
     * @param Order $order
     * @return bool
     */
    public function createInstantBill(Order $order): bool
    {
        /** @var \stdClass $order */
        //检查订单对象
        if (!$order || $order->wms_status != "订单完成") return true;
        //防重推标记
        if (Cache::has("owner_fee_details:orders_" . $order->id)) return true;
        //加载所需数据
        $order->loadMissing(["logistic", "shop", "packages.commodities.commodity", "batch"]);
        $workedAt = $order->wms_edittime ?: $order->updated_at;
        //获取运输费
        $month = substr($workedAt,0,7);
        $logisticInfo = $this->getLogisticFeeInfo($order,$month);

        //获取作业费
        /** @var OwnerPriceOperationService $service */
        $service = app("OwnerPriceOperationService");
        $GLOBALS["FEE_INFO"] = [];
        list($id, $money, $workTaxFee) = $service->matching($month, $order, Feature::MAPPING["order"], $order->owner_id);

        //数据组装
        $detail = OwnerFeeDetail::query()->create([
            "owner_id" => $order->owner_id,
            "worked_at" => $workedAt,
            "type" => "发货",
            "shop_id" => $order->shop_id,
            "operation_bill" => $order->code,
            "province" => $order->province,
            "consignee_name" => $order->consignee_name,
            "consignee_phone" => $order->consignee_phone,
            "commodity_amount" => $logisticInfo["amount"],
            "logistic_bill" => $logisticInfo["logisticBill"],
            "volume" => $logisticInfo["volume"],
            "weight" => $logisticInfo["weight"],
            "logistic_id" => $order->logistic_id,
            "work_fee" => $money,
            "owner_price_operation_id" => $id,
            "logistic_fee" => $logisticInfo["logisticFee"],
            "created_at" => date('Y-m-d H:i:s'),
            "outer_id" => $order->id,
            "outer_table_name" => "orders",
            "work_tax_fee" => $workTaxFee,
            "logistic_tax_fee" => $logisticInfo["logisticTaxFee"],
        ]);
        //向指定表插入标记
        if ($logisticInfo["logisticFee"] && $logisticInfo["fee_info"]) OwnerFeeExpress::query()->insert($logisticInfo["fee_info"]);
        if ($money > 0) app("StoreService")->constructFeeInfo([
            "worked_at" => $order->wms_edittime ?: $order->updated_at,
            "owner_id" => $order->owner_id,
            "model_id" => $id,
            "source_number" => $order->client_code,
            "doc_number" => $order->code,
            "commodity_id" => 0,
            "total_fee" => 0,
            "tax_rate" => 0,
            "fee_description" => '',
        ]);
        //后续处理
        foreach ($logisticInfo["items"] as &$item) $item["owner_fee_detail_id"] = $detail->id;
        if (count($logisticInfo["items"]) > 1) OwnerFeeDetailLogistic::query()->insert($logisticInfo["items"]);
        $this->setOrderQuantity($order->owner_id, $order->logistic_id, $month);
        Cache::put("owner_fee_details:orders_" . $order->id, 1, 86400);
        return true;
    }

    public function getMonthTotal($ownerId = null, $month = null)
    {
        if (!$month) $month = date("Y-m");
        $day = date("t", strtotime($month));
        $whereSql = $ownerId ? "and owner_id = ? " : " ";
        $count = DB::selectOne(DB::raw("SELECT count(*) c from orders where wms_status ='订单完成' {$whereSql} and wms_edittime BETWEEN '{$month}-01 00:00:00' and '{$month}-{$day} 23:59:59';"));
        return $count->c;
    }

    public function pushOrderUpdatedCountCache($orders, $update_params)
    {
        /** @var DataHandlerService $dataHandlerService */
        $dataHandlerService = app(DataHandlerService::class);
        $order_map = $dataHandlerService->dataHeader(['code'], $orders);
        $owner_map = [];
        $month_begin = date('Y-m-01 00:00:00');
        foreach ($update_params as $update_param) {
            if (!($update_param['wms_status'] ?? false) || $update_param['wms_status'] != '订单完成') continue;
            $wms_EditTime = \Illuminate\Support\Carbon::parse($update_param['wms_edittime']);
            if ($wms_EditTime->isBefore($month_begin)) continue;
            $owner_id = $update_param['owner_id'] ?? false;
            if (!$owner_id) continue;
            $order = $dataHandlerService->getKeyValue(['code' => $update_param['code']], $order_map);
            if (!$order) continue;
            if ($order['wms_status'] === '订单完成') continue;
            if (!array_key_exists($owner_id, $owner_map)) $owner_map[$owner_id] = 1;
            else $owner_map[$owner_id] += 1;
        }
        foreach ($owner_map as $owner_id => $count) {
            $this->setCurrentFinishedOrders_byOwnerId($owner_id, $count);
        }
    }

    public function pushOrderCreatedCountCache($created_params)
    {
        $owner_map = [];
        $month_begin = date('Y-m-01 00:00:00');
        foreach ($created_params as $created_param) {
            $owner_id = $created_param['owner_id'] ?? false;
            $wms_EditTime = \Illuminate\Support\Carbon::parse($created_param['wms_edittime']);
            if ($wms_EditTime->isBefore($month_begin)) continue;
            if (!$owner_id) continue;
            if ($created_param['wms_status'] !== '订单完成') continue;
            if (!array_key_exists($owner_id, $owner_map)) $owner_map[$owner_id] = 1;
            else  $owner_map[$owner_id] += 1;
        }
        foreach ($owner_map as $owner_id => $count) {
            $this->setCurrentFinishedOrders_byOwnerId($owner_id, $count);
        }
    }

    private function setCurrentFinishedOrders_byOwnerId($id, $count, $cancelCount = 0)
    {
        $sum = Cache::get("order_count_ownerId_{$id}", function () use ($id) {
            return Order::query()
                ->where('owner_id', $id)
                ->where('wms_status', '订单完成')
                ->where('wms_edittime', '>=', date('Y-m-01 00:00:00'))->count();
        });
        $sum += $count;
        $sum -= $cancelCount;
        Cache::put("order_count_ownerId_{$id}", $sum, date('Y-m-t 23:59:59'));
    }

    public function getCurrentFinishedOrders_byOwnerId($id): int
    {
        return Cache::get("order_count_ownerId_{$id}", function () use ($id) {
            return Order::query()->where('owner_id', $id)
                ->where('wms_status', '订单完成')
                ->where('wms_edittime', '>=', date('Y-m-01 00:00:00'))
                ->count();
        });
    }

    /**
     * 校正运输费
     *
     * @param Order $order
     *
     * @return bool
     */
    public function reviseLogisticFee(Order $order): bool
    {
        /** @var \stdClass $order */
        if (!$order || $order->wms_status != '订单完成') return false; //订单不存在或状态不为完成
        $feeBill = OwnerFeeDetail::query()->where("outer_table_name", "orders")
            ->where("outer_id", $order->id)->whereNull("logistic_fee")->first();
        if (!$feeBill) return false; //是否已有即时账单，没有跳出
        //加载所需数据
        $order->loadMissing(["logistic", "shop", "packages.commodities.commodity", "batch"]);
        if (!$order->packages->count() || !$order->owner) return false;
        //获取运输费
        $logisticInfo = $this->getLogisticFeeInfo($order,substr($feeBill->worked_at,0,7));
        if (!$logisticInfo["logisticFee"] || !$logisticInfo["fee_info"]) return false;

        $workedAt = $order->wms_edittime ?: $order->updated_at;
        $month = substr($workedAt,0,7);

        //获取作业费
        $GLOBALS["FEE_INFO"] = [];
        list($id, $money, $workTaxFee) = app("OwnerPriceOperationService")->matching($month,$order, Feature::MAPPING["order"], $order->owner_id);
        //数据组装
        $feeBill->update([
            "commodity_amount" => $logisticInfo["amount"],
            "logistic_bill" => $logisticInfo["logisticBill"],
            "volume" => $logisticInfo["volume"],
            "weight" => $logisticInfo["weight"],
            "logistic_fee" => $logisticInfo["logisticFee"],
            "logistic_tax_fee" => $logisticInfo["logisticTaxFee"],
            "work_fee" => $money,
            "owner_price_operation_id" => $id,
            "work_tax_fee" => $workTaxFee,
        ]);
        foreach ($logisticInfo["items"] as &$item) $item["owner_fee_detail_id"] = $feeBill->id;
        if (count($logisticInfo["items"]) > 1) OwnerFeeDetailLogistic::query()->insert($logisticInfo["items"]);
        if ($money > 0) {
            app("StoreService")->clearFeeInfo($order->code);
            app("StoreService")->constructFeeInfo([
                "worked_at" => $order->wms_edittime ?: $order->updated_at,
                "owner_id" => $order->owner_id,
                "model_id" => $id,
                "source_number" => $order->client_code,
                "doc_number" => $order->code,
                "commodity_id" => 0,
                "total_fee" => 0,
                "tax_rate" => 0,
                "fee_description" => '',
            ]);
        }
        //向指定表插入标记
        OwnerFeeExpress::query()->insert($logisticInfo["fee_info"]);
        return true;
    }

    public function freezeRemoveWave($orders, $isSql = false)
    {
        if (!$isSql && !is_array($orders)) $orders = [$orders];
        if (!$isSql) {
            $where = "''";
            foreach ($orders as $f) $where .= ",'{$f}'";
            $orders = $where;
        }
        $sql = <<<sql
    DELETE FROM DOC_WAVE_DETAILS WHERE (WAVENO,ORDERNO) IN (SELECT DOC_ORDER_HEADER.WAVENO,DOC_ORDER_HEADER.ORDERNO FROM DOC_ORDER_HEADER LEFT JOIN DOC_WAVE_DETAILS ON DOC_ORDER_HEADER.WAVENO = DOC_WAVE_DETAILS.WAVENO
    AND DOC_ORDER_HEADER.ORDERNO = DOC_WAVE_DETAILS.ORDERNO WHERE DOC_ORDER_HEADER.orderno IN ({$orders}) AND DOC_ORDER_HEADER.WAVENO != '*' AND DOC_ORDER_HEADER.WAVENO IS NOT NULL)
sql;
        $result = DB::connection("oracle")->delete(DB::raw("$sql"));
        LogService::log(__METHOD__, "删除波次详情", $sql);
        return $result;
    }

    /**
     * 恢复不存在商品订单
     *
     * @param Order|\stdClass $order
     */
    public function notExistToRecover(Order $order)
    {
        $sql = <<<sql
SELECT customerid,orderno,sku,orderlineno,qty_each FROM ACT_ALLOCATION_DETAILS WHERE orderno = ?
sql;

        $allocations = DB::connection("oracle")->select(DB::raw($sql), [$order->code]);
        if (!$allocations) return;
        $insert[] = [];
        $sku = array_column($allocations, "sku");
        app("CommodityService")->notExistToCreate($order->owner_id, $sku);
        $commodities = Commodity::query()->where("owner_id", $order->owner_id)->whereIn("sku", $sku)->get();
        $commodityMap = [];
        foreach ($commodities as $commodity) $commodityMap[$commodity->sku] = $commodity->id;
        foreach ($allocations as $allocation) {
            if (!($commodityMap[$allocation->sku] ?? false)) LogService::log(__CLASS__, "ERROR-SKU对应商品在FLUX不存在", json_encode($allocation, JSON_UNESCAPED_UNICODE));
            $insert[] = [
                'order_id' => $order->id,
                'commodity_id' => $commodityMap[$allocation->sku] ?? null,
                'amount' => $allocation->qty_each,
                'location' => $allocation->location,
            ];
        }
        OrderCommodity::query()->insert($insert);
    }

    /**
     * 根据 订单的 code 同步订单
     * @param $codes
     */
    public function syncOrderByCodes($codes)
    {
        $orderHeaders = app(OracleDOCOrderHeaderService::class)->getQuery()->whereIn('DOC_Order_Header.OrderNo', $codes)->get();
        $this->syncOrderByWMSOrderHeaders($orderHeaders);
        app('OrderCommodityService')->syncOrderCommodity($orderHeaders);
        app('OrderPackageService')->syncOrderPackage($orderHeaders);
        app("OrderPackageCommoditiesService")->syncOrderPackageCommodities($orderHeaders);
    }

    /**
     * 标记 问题件 存在的 order
     * @param $orders
     */
    public function tagOrderByOrderIssue(&$orders)
    {
        $orderNos = data_get($orders, '*.orderno');
        $orderIssues = OrderIssue::query()->with('order')->whereIn('order_id', function ($query) use ($orderNos) {
            $query->from('orders')->select('id')->whereIn('code', $orderNos);
        })->get();
        $tags = [];
        $orderIssues->each(function ($item) use (&$tags) {
            $tags[$item['order']['code']] = true;
        });
        foreach ($orders as &$order) {
            if (array_key_exists($order->orderno, $tags)) $order->is_order_issue = true;
            else $order->is_order_issue = false;
        }
    }

    /**
     * @param $orderno
     * @return bool
     * 一键回传 调用flux的sp
     */
    public function manualBack($orderno): bool
    {
        $who = 'WAS' . (Auth::user() ? '-' . Auth::user()["name"] : '');
        $host = config('database.connections.oracle.host');
        $serviceName = config('database.connections.oracle.service_name');
        $user = config('database.connections.oracle.username');
        $password = config('database.connections.oracle.password');
        $conn = oci_connect($user, $password, $host . '/' . $serviceName, 'utf8');
        $IN_WarehouseID = '';
        $IN_OrderNo = $orderno;
        $IN_Language = '';
        $IN_UserID = $who;
        $OUT_Return_Code = '';
        $sql_sp = "begin SPSOUDF_BS_ManualCF(:IN_WarehouseID,:IN_OrderNo,:IN_Language,:IN_UserID,:OUT_Return_Code); end;";
        $stmt = oci_parse($conn, $sql_sp);
        oci_bind_by_name($stmt, ':IN_WarehouseID', $IN_WarehouseID);
        oci_bind_by_name($stmt, ':IN_OrderNo', $IN_OrderNo);
        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, 300);
        oci_execute($stmt);

        if (substr($OUT_Return_Code, 0, 3) != '000') {
            app('LogService')->log(__METHOD__, '调用sp一键回传订单失败' . __FUNCTION__, "ORDERNO:" . $orderno . "ERROR：" . $OUT_Return_Code);
            return false;
        }
        oci_close($conn);
        return true;
    }

    /**
     * @param string $orderNo
     * @param string|null $orderLineNo
     * @param string|null $warehouseCode
     * @param resource|null|mixed $conn
     * @return string
     */
    public function allocation(string $orderNo, ?string $orderLineNo = null, ?string $warehouseCode = null, $conn = null): string
    {
        if (!$warehouseCode) {
            $order = DB::connection("oracle")->selectOne(DB::raw("SELECT warehouseid FROM DOC_ORDER_HEADER WHERE ORDERNO = ?"), [$orderNo]);
            if (!$order) return "500#订单丢失";
            $warehouseCode = $order->warehouseid;
        }
        $user = Auth::user() ? "W" . Auth::user()["name"] : "WAS";
        $mode = $orderLineNo ? 'By OrderNO + OrderLineNO' : 'By OrderNO';
        $lineNo = $orderLineNo ?: '0';
        $sql = <<<sql
BEGIN
    SPSO_HardAllocation_Process('{$warehouseCode}','Allocation','{$mode}',
                                 null,'{$orderNo}','{$lineNo}',null,
    '{$user}',:CODE);
END;
sql;
        $releaseSign = true;
        if (!$conn || !is_resource($conn)) $conn = $this->getFluxConnection();
        else $releaseSign = false; //连接是外部传递则不释放资源
        $stmt = oci_parse($conn, $sql);
        $code = "";
        oci_bind_by_name($stmt, ':CODE', $code, 256);
        oci_execute($stmt);
        if ($releaseSign) $this->releaseFluxConnection($conn);
        return $code;
    }

    /**
     * 根据orderno 获取 快递单号
     * 严格模式根据筛选条件
     *      1. 状态 61 81
     *      2. 承运商是中通
     *      3. 有单号
     *      4. 有复核，
     *      5. 没揽收
     * 非严格模式不校验任何参数
     * @param $strict boolean 是否为严格模式
     * @param $orderno array
     * @return Collection
     */
    public function getCollectUploadLogisticNumbers(bool $strict, array $orderno): Collection
    {
        if ($strict) {
            $logisticNums = OracleActAllocationDetails::query()
                ->select('picktotraceid')
                ->whereIn('orderno', $orderno)
                ->whereNotNull('picktotraceid')//有单号
                ->whereNotNull('checktime')//有复核
                ->whereIn('orderno', function (Builder $query) use ($orderno) {
                    $query->from('doc_order_header')
                        ->select('orderno')
                        ->whereIn('orderno', $orderno)
                        ->whereIn('carriername', ['中通（轻小包裹）', '中通', '中通速递']) // 承运商是中通
                        ->where('sostatus', '!=', '90')
                        ->where("edisendflag","Y");
                })
                ->pluck('picktotraceid');
            if ($logisticNums->count()==0)return collect();
            $logisticNums = OrderPackage::query()
                ->select('logistic_number')
                ->whereIn('logistic_number', $logisticNums)
                ->whereNull('transfer_status')
                ->pluck('logistic_number');
        } else {
            //非严格模式 不进行任何校验
            $logisticNums = OracleActAllocationDetails::query()
                ->select('picktotraceid')
                ->whereIn('orderno', $orderno)
                ->pluck('picktotraceid');
        }
        return $logisticNums;
    }
}