SATE-EasyReceipt/app/Util/SelectQueryUtil.php

<?php

namespace App\Util;

use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;

class SelectQueryUtil
{
    public static function select(array|string $columns, string $as = null): static
    {
        return  new static($columns, $as);
    }

    public static function value(string|int $val, string $as)
    {
        if (is_string($val)) {
            return DB::raw(sprintf("'%s' as %s", $val, $as));
        } else if (is_int($val)) {
            return DB::raw(sprintf("CAST('%s' as INTEGER) as %s", $val, $as));
        }
        return "";
    }

    private string $columnName;

    private string|null $as = null;

    private string|int|null $nullValue = null;



    public function __construct(array|string $column, string $as = null)
    {
        if (is_string($column)) {
            $this->columnName = $column;
            $this->as = $column;
        } else {
            $ret = "";
            foreach ($column as $c) {
                if ($ret !== "") {
                    $ret .= ".";
                }
                $ret .= sprintf('"%s"', $c);
            }
            $this->columnName = $ret;

            $this->as = Arr::last($column);
        }

        if ($as !== null) {
            $this->as = $as;
        }
    }

    public function as(string $as): static
    {
        $this->as = $as;
        return $this;
    }

    public function nullValue(string|int|null $nullValue): static
    {
        $this->nullValue = $nullValue;
        return $this;
    }


    public function build()
    {
        $query = $this->columnName;
        $needRaw = true;

        if ($this->nullValue !== null) {
            if (is_string($this->nullValue)) {
                $query = sprintf("COALESCE(%s, '%s')", $query, $this->nullValue);
                $needRaw = true;
            } else if (is_int($this->nullValue)) {
                $query = sprintf("COALESCE(%s, %d)", $query, $this->nullValue);
                $needRaw = true;
            }
        }
        if ($this->as !== null) {
            $query = sprintf('%s as %s', $query, $this->as);
        }


        return $needRaw ? DB::raw($query) : $query;
    }
}