<?php 

namespace App\Services;

use Illuminate\Support\Facades\DB;

/**
 * connection : oracle
 * table : ACT_ALLOCATION_DETAILS
 */
Class OracleActAllocationDetailService
{
    public function paginate(array $params){
        $page = $params['page'] ?? 1;
        $pagiante = $params['pagiante'] ?? 50;
        if (!is_numeric($page) || !is_numeric($pagiante))return;
        $sql = "SELECT ACT_ALLOCATION_DETAILS.* FROM(SELECT ACT_ALLOCATION_DETAILS.*,ROWNUM rn FROM
                    (SELECT orderno FROM ACT_ALLOCATION_DETAILS WHERE 1=1 ";
        if ($params['checktime_start'] ?? false){
            $sql .= " AND checktime >= '".$params['checktime_start']." 00:00:00'";
        }
        if ($params['checktime_end'] ?? false){
            $sql .= " AND checktime <= '".$params['checktime_end']." 23:59:59'";
        }
        $sql .= "GROUP BY orderno)ACT_ALLOCATION_DETAILS)ACT_ALLOCATION_DETAILS
                WHERE ACT_ALLOCATION_DETAILS.rn <= ".$page*$pagiante." AND rn >".($page-1)*$pagiante;
        return DB::connection('oracle')->select(DB::raw($sql));
    }

    public function getOrderno(array $params){
        $allocations = $this->paginate($params);
        $count = count($allocations);
        $str = "(";
        foreach ($allocations as $index => $allocation){
            if ($index < $count-1){
                $str .= "'".$allocation->orderno."',";
            }else{
                $str .= "'".$allocation->orderno."')";
            }
        }
        return $str;
    }
}