<?php 

namespace App\Services; 

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

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

    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 (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));
    }
}