<?php

namespace App\Services;

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


class OracleBasSkuService
{
    use ServiceAppAop;
    protected $modelClass=OracleBasSku::class;
    public function getOwnerCommodities(array $params){
        $query = OracleBasSKU::query();
        if ($params['code'] ?? false){
            if (!is_array($params['code'])){
                $params['code'] = [$params['code']];
            }
            $query->whereIn('customerid',$params['code']);
        }
        return $query->get();
    }
    public function get(array $params){
        $query = $this->query($params);
        return $query->get();
    }
    public function first(array $params){
        return $this->query($params)->first();
    }

    public function count(array $params){
        $query = $this->query($params);
        return $query->count();
    }

    private function query(array $params){
        $query = OracleBasSKU::query();
        foreach ($params as $column => $value){
            if ($column == "barcode"){
                $query->where(function ($builder)use($value){
                   $builder->where('alternate_sku1',$value)->orWhere('alternate_sku2',$value);
                });
                continue;
            }
            if (is_array($value))$query->whereIn($column,$value);
            else $query->where($column,$value);
        }
        return $query;
    }

    public function getPiece(array $params,int $start, int $amount){
        $sql = "SELECT * FROM (SELECT ROWNUM rn,d.* FROM BAS_SKU d WHERE 1=1";
        foreach ($params as $column => $value){
            if (is_array($value)){
                if (count($value) < 1)return null;
                $sql .= " AND ".$column." IN (";
                foreach ($value as $i => $v){
                    if ($i != 0)$sql .= ",";
                    $sql .= "'".$v."'";
                }
                $sql .= ")";
                continue;
            }
            $sql .= " AND ".$column." = '".$value."'";
        }
        $sql .= " ORDER BY addtime)WHERE rn>".$start." AND rn <= ".($start+$amount);
        return DB::connection('oracle')->select(DB::raw($sql));
    }

    public function 获取BasSKu_通过WMSOrderHeaders($orderHeaders){
        if(!$orderHeaders){return null;}
        $customer_Ids = array_unique(data_get($orderHeaders,'*.customerid'));
        $sku_s = array_unique(data_get($orderHeaders,'*.oracleDOCOrderDetails.*.sku'));
        if(!$customer_Ids || !$sku_s){return null;}
        return OracleBasSKU::query()->whereIn('customerid',$customer_Ids)->whereIn('sku',$sku_s)->get();
    }
    public function getWmsCreatedCommodities($startDate)
    {
        if (!$startDate) return null;
        return OracleBasSKU::query()
            ->select('customerid','sku','descr_c','alternate_sku1','alternate_sku2','alternate_sku3','skulength','skuwidth','skuhigh','cube','packid','addtime','edittime')
            ->where('addTime', '>=', $startDate)
            ->orderByDesc('addtime')
            ->get();
    }

    public function getWmsUpdatedCommodities($startDate)
    {
        if (!$startDate) return null;
        return OracleBasSKU::query()
            ->select('customerid','sku','descr_c','alternate_sku1','alternate_sku2','alternate_sku3','skulength','skuwidth','skuhigh','cube','packid','addtime','edittime')
            ->where('EditTime', '>=', $startDate)
            ->whereColumn('EditTime', '<>', 'addTime')
            ->orderByDesc('EditTime')
            ->get();
    }
}
