baoshi-was/app/Services/OracleActAllocationDetailService.php

<?php 

namespace App\Services;

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

/**
 * connection : oracle
 * table : ACT_ALLOCATION_DETAILS
 */
Class OracleActAllocationDetailService
{
    private function query($sql, array $params){
        if ($params['checktime_start'] ?? false){
            $sql .= " AND checktime >= '".$params['checktime_start'].":00'";
        }
        if ($params['checktime_end'] ?? false){
            $sql .= " AND checktime <= '".$params['checktime_end'].":59'";
        }
        return $sql;
    }

    public function paginate(array $params){
        $sql = "SELECT ACT_ALLOCATION_DETAILS.* FROM(SELECT ACT_ALLOCATION_DETAILS.*,ROWNUM rn FROM
                    (SELECT orderno FROM ACT_ALLOCATION_DETAILS WHERE 1=1 ";
        $sql = $this->query($sql, $params);
        $sql .= "GROUP BY orderno)ACT_ALLOCATION_DETAILS)ACT_ALLOCATION_DETAILS
                WHERE ACT_ALLOCATION_DETAILS.rn <= ".$params['page']*$params['paginate']." AND rn >".($params['page']-1)*$params['paginate'];
        return DB::connection('oracle')->select(DB::raw($sql));
    }
    public function get(array $params){
        $sql = "SELECT orderno FROM ACT_ALLOCATION_DETAILS WHERE 1=1 ";
        $sql = $this->query($sql, $params);
        $sql .= "GROUP BY orderno";
        return DB::connection('oracle')->select(DB::raw($sql));
    }

    public function getOrderno(array $params){
        if (($params['page'] ?? false) && ($params['paginate'] ?? false))$allocations = $this->paginate($params);
        else $allocations = $this->get($params);
        $count = count($allocations);
        if (!$count)return null;
        $str = "(";
        foreach ($allocations as $index => $allocation){
            if ($index < $count-1){
                $str .= "'".$allocation->orderno."',";
            }else{
                $str .= "'".$allocation->orderno."')";
            }
        }
        return $str;
    }
    /**
     * @param array $params
     * @return OracleActAllocationDetails
     */
    public function first(array $params){
        $actAllocationDetail = OracleActAllocationDetails::query();
        foreach ($params as $column => $value){
            $actAllocationDetail->where($column, $value);
        }
        /** @var OracleActAllocationDetails $actAllocationDetail */
        return $actAllocationDetail->first();
    }
}