baoshi-was/app/Services/InventoryService.php

<?php


namespace App\Services;

use App\inventoryDailyLog;
use App\InventoryDailyLoggingOwner;
use App\Owner;
use App\Services\common\QueryService;
use Illuminate\Support\Facades\DB;
use App\Traits\ServiceAppAop;


class InventoryService
{
    use ServiceAppAop;
    protected $modelClass=Inventory::class;
    public function getSql(array $params,$page=null,$paginate=null){
        $ownerCodes=Owner::filterAuthorities()->select('code')->get();
        $date_start=$params['date_start'] ?? null;
        $range = $params['range'] ?? null;
        if ($range)$date_start=date('Y-m-d',strtotime('-'.$range." day"));
        $date_end=$params['date_end'] ?? null;
        $TOLocation=$params['TOLocation'] ?? null;
        $LotAtt05=$params['LotAtt05'] ?? null;
        $LotAtt02_start=$params['LotAtt02_start'] ?? null;
        $customerid=$params['customerid'] ?? null;
        $SKU=$params['SKU'] ?? null;
        $ALTERNATE_SKU1=$params['ALTERNATE_SKU1'] ?? null;
        $LotAtt02_end=$params['LotAtt02_end'] ?? null;
        $sql='select * from (select result.*,rownum rn from (';
        $sql.=' select customer.Descr_C as 货主,stockLog.客户 客户, 库位, sku.SKU 产品编码, sku.ALTERNATE_SKU1 产品条码, ';
        $sql.=' sku.Descr_C 商品名称, lot.LotAtt05 属性仓, lot.LotAtt08 质量状态, lot.LotAtt02 失效日期, ';
        $sql.=' lot.LotAtt04 批号 ';
        $sql.=' , sum(移出数量)移出数量, sum(移入数量)移入数量 ';
        $sql.=' , storeStatus.QTY 在库数量, storeStatus.QtyAllocated 占用数量,count(1) over () as sum from ';
        $sql.=' (select FMLotNum,FMSKU,TOCustomerID 客户,0 as 移出数量, sum(TOQty_Each) as 移入数量, TOLocation as 库位 ';
        $sql.=" from ACT_Transaction_Log where TransactionType='PA' ";
        if ($date_start) $sql.=" and addtime > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end) $sql.=" and addtime < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
//        if ($TOLocation)$sql .= " and TOLocation like '".$TOLocation."' ";
//        if ($SKU)$sql.=" and FMSKU like '".$SKU."' ";
        if ($TOLocation)$sql=$this->searchWay($sql,$TOLocation,"TOLocation");
        if ($SKU)$sql=$this->searchWay($sql,$SKU,"FMSKU");
        if ($customerid){
            $sql .= ' and TOCustomerID in (';
            $arr = explode(',',$customerid);
            foreach ($arr as $index => $data){
                if ($index != 0)$sql .= ',';
                $sql .= "'".$data."'";
            }
            $sql .= ') ';
        }
        if (!$customerid&&$ownerCodes){
            $sql .= ' and TOCustomerID in (';
            foreach ($ownerCodes as $index => $data){
                if ($index != 0)$sql .= ',';
                $sql .= "'".$data['code']."'";
            }
            $sql .= ') ';
        }
        $sql.=' group by TOCustomerID, TOLocation,FMSKU,FMLotNum union all ';
        $sql.=' select FMLotNum,FMSKU,FMCUSTOMERID 客户,sum(FMQty_Each) as 移出数量, 0 as 移入数量, FMLOCATION as 库位 ';
        $sql.=" from ACT_Transaction_Log where TransactionType='SO' ";
        if ($date_start) $sql.=" and addtime > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end) $sql.=" and addtime < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
//        if ($TOLocation)$sql .= " and FMLOCATION like '".$TOLocation."' ";
//        if ($SKU)$sql.=" and FMSKU like '".$SKU."' ";
        if ($TOLocation)$sql=$this->searchWay($sql,$TOLocation,"FMLOCATION");
        if ($SKU)$sql=$this->searchWay($sql,$SKU,"FMSKU");
        if ($customerid){
            $sql .= ' and FMCUSTOMERID in (';
            $arr = explode(',',$customerid);
            foreach ($arr as $index => $data){
                if ($index != 0)$sql .= ',';
                $sql .= "'".$data."'";
            }
            $sql .= ') ';
        }
        if (!$customerid&&$ownerCodes){
            $sql .= ' and FMCUSTOMERID in (';
            foreach ($ownerCodes as $index => $data){
                if ($index != 0)$sql .= ',';
                $sql .= "'".$data['code']."'";
            }
            $sql .= ') ';
        }
        $sql.=' group by FMCustomerID, FMLocation,FMSKU,FMLotNum union all ';
        $sql.=' select FMLotNum,FMSKU,FMCUSTOMERID 客户,sum(FMQty_Each) as 移出数量,0 as 移入数量, FMLocation as 库位 ';
        $sql.=" from ACT_Transaction_Log  where TransactionType='MV' ";
        if ($date_start) $sql.=" and addtime > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end) $sql.=" and addtime < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
//        if ($TOLocation)$sql .= " and FMLocation like '".$TOLocation."' ";
//        if ($SKU)$sql.=" and FMSKU like '".$SKU."' ";
        if ($TOLocation)$sql=$this->searchWay($sql,$TOLocation,"FMLocation");
        if ($SKU)$sql=$this->searchWay($sql,$SKU,"FMSKU");

        if ($customerid){
            $sql .= ' and FMCUSTOMERID in (';
            $arr = explode(',',$customerid);
            foreach ($arr as $index => $data){
                if ($index != 0)$sql .= ',';
                $sql .= "'".$data."'";
            }
            $sql .= ') ';
        }
        if (!$customerid&&$ownerCodes){
            $sql .= ' and FMCUSTOMERID in (';
            foreach ($ownerCodes as $index => $data){
                if ($index != 0)$sql .= ',';
                $sql .= "'".$data['code']."'";
            }
            $sql .= ') ';
        }
        $sql.=' group by FMLocation,FMCUSTOMERID,FMSKU,FMLotNum union all ';
        $sql.=' select FMLotNum,FMSKU,TOCustomerID 客户,0 as 移出数量,sum(TOQty_Each)as 移入数量, TOLocation as 库位 ';
        $sql.=" from ACT_Transaction_Log where TransactionType='MV' ";
        if ($date_start) $sql.=" and addtime > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end) $sql.=" and addtime < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
//        if ($TOLocation)$sql .= " and TOLocation like '".$TOLocation."' ";
//        if ($SKU)$sql.=" and FMSKU like '".$SKU."' ";
        if ($TOLocation)$sql=$this->searchWay($sql,$TOLocation,"TOLocation");
        if ($SKU)$sql=$this->searchWay($sql,$SKU,"FMSKU");

        if ($customerid){
            $sql .= ' and TOCustomerID in (';
            $arr = explode(',',$customerid);
            foreach ($arr as $index => $data){
                if ($index != 0)$sql .= ',';
                $sql .= "'".$data."'";
            }
            $sql .= ') ';
        }
        if (!$customerid&&$ownerCodes){
            $sql .= ' and TOCustomerID in (';
            foreach ($ownerCodes as $index => $data){
                if ($index != 0)$sql .= ',';
                $sql .= "'".$data['code']."'";
            }
            $sql .= ') ';
        }
        $sql.=' group by TOLocation,TOCustomerID,FMSKU,FMLotNum)stockLog ';
        $sql.=' left join BAS_Customer customer on customer.CustomerID=stockLog.客户 ';
        $sql.=' left join BAS_SKU sku on sku.SKU=stockLog.FMSKU and sku.CUSTOMERID=stockLog.客户 ';
        $sql.=' left join INV_LOT_ATT lot on lot.LOTNUM=stockLog.FMLOTNUM ';
        $sql.=' left join INV_LOT_LOC_ID storeStatus on storeStatus.LOTNUM=stockLog.FMLOTNUM ';;
        $sql.=' and storeStatus.LocationID=stockLog.库位 ';
        $sql.=' group by 库位,customer.Descr_C,sku.SKU,sku.ALTERNATE_SKU1 ';
        $sql.=' ,sku.Descr_C,FMLotNum,lot.LotAtt05,lot.LotAtt08,lot.LotAtt02,lot.LotAtt04 ';
        $sql.=' , storeStatus.QTY, storeStatus.QtyAllocated,stockLog.客户  ';
        $sql.=' )result where 1=1 ';
//        if ($LotAtt05)$sql .=" and 属性仓 like '".$LotAtt05."' ";
        if ($LotAtt05)$sql=$this->searchWay($sql,$LotAtt05,"属性仓");
        if ($LotAtt02_start)$sql.=" and 失效日期 >='".$LotAtt02_start." 00:00:00' ";
        if ($LotAtt02_end)$sql.=" and 失效日期 <='".$LotAtt02_end." 23:59:59' ";
//        if ($ALTERNATE_SKU1)$sql.=" and 产品条码 like '".$ALTERNATE_SKU1."' ";
        if ($ALTERNATE_SKU1)$sql=$this->searchWay($sql,$ALTERNATE_SKU1,"产品条码");
        if ($page&&$paginate)$sql.="  and ROWNUM<='".$page*$paginate."'";
        $sql.=' )  ';
        if ($page&&$paginate)$sql.="  where rn>'".($page-1)*$paginate."'";
        return $sql;
    }
    private function searchWay($sql,$condition,$column)
    {
        if ($condition){
            if (strpos($condition, ',') || strpos($condition, '，') || strpos($condition, ' ')) {
                $arr = array_filter(preg_split('/[,， ]+/is', $condition));
                $sql .= ' and '.$column.' in (';
                foreach ($arr as $index => $arr){
                    if ($index != 0)$sql .= ',';
                    $sql .= "'".$arr."'";
                }
                $sql .= ') ';
                unset($condition);
            } else {
                $sql .= " and $column like '".$condition."' ";
            }
        }
        return $sql;
    }

    public function paginate(array $params)
    {
        return DB::connection('oracle')->select(DB::raw($this->getSql($params, $params['page'] ?? 1, $params['paginate'] ?? 50)));
    }

    //库存体积条件
    private function conditionQueryDailyLog(array $param){
        $inventoryDailyLogs = InventoryDailyLog::query()->with(['owner','commodity'=>function($query){
            $query->with('barcodes');
        }])->orderByDesc('id');
        $columnQueryRules=[
            'owner_id' => ['multi' => ','],
            'created_at_start' => ['alias' => 'created_at' , 'startDate' => ' 00:00:00'],
            'created_at_end' => ['alias' => 'created_at' , 'endDate' => ' 23:59:59'],
        ];
        $inventoryDailyLogs = app(QueryService::class)->query($param,$inventoryDailyLogs,$columnQueryRules);
        return $inventoryDailyLogs;
    }

    //库存体积
    public function getInventoryDailyLog(array $param){
        return $this->conditionQueryDailyLog($param)->paginate($param['paginate'] ?? 50);
    }

    //获取开启监听记录货主
    public function getInventoryDailyLoggingOwner($column = ['id','owner_id'], $status = "启用"){
        if (!is_array($column)) {
            $column = [$column];
        }
        return InventoryDailyLoggingOwner::query()->select($column)->where('status',$status)->get();
    }

    //录入监听记录货主
    public function firstOrCreate($param,$column = null){
        if ($column)return InventoryDailyLoggingOwner::query()->firstOrCreate($param,$column);
        return InventoryDailyLoggingOwner::query()->firstOrCreate($param);
    }
}