baoshi-was/app/Services/ReplenishmentService.php

<?php

namespace App\Services;

use App\Traits\ServiceAppAop;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;


class ReplenishmentService
{
    use ServiceAppAop;

    /**
     * @param $customer
     * @return array
     * 根据货主，订单状态为拣货完成之前的
     * 订单信息
     */
    public function getOrderInfoRecentMonthByCustomer($customer): array
    {
        $db = DB::connection("oracle");
        $sql = <<<sql
select orders.customerid, orders.sku,orders.amount,BAS_SKU.ALTERNATE_SKU1,BAS_SKU.ALTERNATE_SKU2,BAS_SKU.ALTERNATE_SKU3
from (
         select customerid, sku, sum(QTYORDERED_EACH) amount
         from DOC_ORDER_DETAILS
         where ORDERNO in (select ORDERNO
                           from DOC_ORDER_HEADER
                           where SOSTATUS IN ('00', '10', '20', '30', '40', '50')
                             AND customerid = ?
                             AND carrierid !='ZT'
                             AND ADDTIME >= TO_DATE(?, 'yyyy-mm-dd hh24:mi:ss')
                             AND ADDTIME <= TO_DATE(?, 'yyyy-mm-dd hh24:mi:ss'))
         group by customerid, sku
     ) orders
left join BAS_SKU on orders.customerid=BAS_SKU.customerid and orders.sku = BAS_SKU.SKU
order by orders.amount desc
sql;
        $res= $db->select(DB::raw($sql),[$customer,Carbon::now()->subDays(30)->toDateTimeString(),Carbon::now()->toDateTimeString()]);
        $orders=array();
        foreach ($res as $item){
            $orders[]=json_decode( json_encode( $item),true);
        }
        return $orders;

    }

    /**
     * @param $customer
     * @param array $skuArr
     * @return array
     * 根据货主和发货sku 获取库存拣货位及拣货位商品数量
     * 并整合为sku 为key的数组
     */
    public function getEaInventoryByCustomerAndSku($customer,array $skuArr): array
    {
        $db = DB::connection("oracle");
        $info=array();
        foreach (array_chunk($skuArr,1000) as $item){
            $sql = <<<sql
select INV_LOT_LOC_ID.CUSTOMERID,INV_LOT_LOC_ID.SKU,INV_LOT_LOC_ID.LOCATIONID,sum(INV_LOT_LOC_ID.QTY-INV_LOT_LOC_ID.QTYALLOCATED) qty
from INV_LOT_LOC_ID
         left join BAS_LOCATION on BAS_LOCATION.LOCATIONID = INV_LOT_LOC_ID.LOCATIONID
where BAS_LOCATION.LOCATIONUSAGE = 'EA'
  AND customerid = ?

sql;
        if (count($item) > 0) {
            $sql .= " and sku in (";
            foreach ($item as $index => $str) {
                if ($index == 0) {
                    $sql .= "'" . $str . "'";
                    continue;
                }
                $sql .= ",'" . $str . "'";
            }
            $sql .= ")";
        }
            $sql.= ' group by INV_LOT_LOC_ID.CUSTOMERID,INV_LOT_LOC_ID.SKU,INV_LOT_LOC_ID.LOCATIONID';
            $info[]= $db->select(DB::raw($sql),[$customer]);
        }
        $info = array_merge(...$info);
        //整合同sku下的拣货库位
        $tmpArray = array();
        foreach ($info as $row) {
            $key = $row->sku;
            if (array_key_exists($key, $tmpArray)) {
                if (is_array($tmpArray[$key]['locationid'])) {
                    $tmpArray[$key]['locationid'][] = $row->locationid;
                } else {
                    $tmpArray[$key]['locationid'] = array($tmpArray[$key]['locationid'], $row->locationid);
                }
                if ($tmpArray[$key]['qty']) {
                    $tmpArray[$key]['qty'] += $row->qty;
                } else {
                    $tmpArray[$key]['qty'] =  $row->qty;
                }
            } else {
                $tmpArray[$key] = array('sku'=>$row->sku,'locationid'=>array($row->locationid),'qty'=>$row->qty);
            }
        }
        return $tmpArray;
    }

    /**
     * @param $customer
     * @param array $skuArr
     * @return array
     * 根据货主和发货sku 获取库存存储位及存储位商品数量
     * 并整合为sku 为key的数组
     */
    public function getRsInventoryByCustomerAndSku($customer,array $skuArr): array
    {
        $db = DB::connection("oracle");
        $info=array();
        foreach (array_chunk($skuArr,1000) as $item){
            $sql = <<<sql
select INV_LOT_LOC_ID.CUSTOMERID,INV_LOT_LOC_ID.SKU,INV_LOT_LOC_ID.LOCATIONID,INV_LOT_LOC_ID.LOTNUM,sum(INV_LOT_LOC_ID.QTY-INV_LOT_LOC_ID.QTYALLOCATED) qty
from INV_LOT_LOC_ID
         left join BAS_LOCATION on BAS_LOCATION.LOCATIONID = INV_LOT_LOC_ID.LOCATIONID
where BAS_LOCATION.LOCATIONUSAGE = 'RS'
  AND customerid = ?
sql;
            if (count($item) > 0) {
                $sql .= " and sku in (";
                foreach ($item as $index => $str) {
                    if ($index == 0) {
                        $sql .= "'" . $str . "'";
                        continue;
                    }
                    $sql .= ",'" . $str . "'";
                }
                $sql .= ")";
            }
            $sql.= ' group by INV_LOT_LOC_ID.CUSTOMERID,INV_LOT_LOC_ID.SKU,INV_LOT_LOC_ID.LOCATIONID,INV_LOT_LOC_ID.LOTNUM order by qty desc';
            $info[]= $db->select(DB::raw($sql),[$customer]);
        }
        $info=array_merge(...$info);
        //整合同sku下的存储库位
        $tmpArray = array();
        foreach ($info as $row) {
            $key = $row->sku;
            if (array_key_exists($key, $tmpArray)) {
                if (is_array($tmpArray[$key]['locationid'])) {
                    if (count($tmpArray[$key]['locationid'])<5){
                        $tmpArray[$key]['locationid'][] = ['location'=>$row->locationid,'qty'=>$row->qty,'lot'=>$row->lotnum];
                    }
                } else {
                    $tmpArray[$key]['locationid'] = array($tmpArray[$key]['locationid'], ['location'=>$row->locationid,'qty'=>$row->qty,'lot'=>$row->lotnum]);
                }
            } else {
                $tmpArray[$key] = array('sku'=>$row->sku,'locationid'=>array(['location'=>$row->locationid,'qty'=>$row->qty,'lot'=>$row->lotnum]));
            }
        }
        return $tmpArray;
    }

    public function combinationSql(array $skuArr,$sql)
    {
        foreach (array_chunk($skuArr,1000) as $index=>$item){
            if ($index==0){
                $sql .= " and sku in (";
                foreach ($item as $i => $str) {
                    if ($i == 0) {
                        $sql .= "'" . $str . "'";
                        continue;
                    }
                    $sql .= ",'" . $str . "'";
                }
            }else{
                $sql .= " or sku in (";
                foreach ($item as $a => $str) {
                    if ($a == 0) {
                        $sql .= "'" . $str . "'";
                        continue;
                    }
                    $sql .= ",'" . $str . "'";
                }
            }
            $sql .= ")";
        }
        return $sql;
    }
}