先に作成した試算表算出SQLには、[年度 <=2024][年度 = 2024]等、実行時にそこを書き換えないとダメな部分が決め打ちで入っているのですから、モデルで調整しないといけません。
また、Laravel DB::select で渡すパラメーター配列は、サーバー側prepare に渡されるはず（sql error 時のメッセージからはそう見えています）なので、どのように渡す事が可能なのかを調べないといけません。
where 節におけるパラメーター側だけ変更可能なのかとか、union all でのテーブル名をパラメータで渡せるのかなどなどの事です。
そこで、モデルとテストに修正を加えます
class KaikeiWorkModel
{
//試算表算出SQLを元に
private static $sqlstring ="
with
cte_zenki
as(
--中略
) as 前残
from kaikei.work_仕訳_明細_増減
:WPARAM1 --元は where 年度 < 2024
group by 科目コード),
cte_touki
as(
--中略
) as 当期残
from kaikei.work_仕訳_明細_増減
:WPARAM2 --元は where 年度 <= 2024
group by 科目コード),
cte_zenki_touki
as(
--こういうパラメータが可能かどうかは大事です
select distinct :WPARAM3::integer as 年度,
科目コード,
--中略
),
cte_kari
as(
select 年度,
科目コード, sum(金額-内消費税) as 借方
from kaikei.work_仕訳_明細_税集計
:WPARAM4 --元は where 年度 = 2024
and 仕訳貸借 in('借方')
group by 年度, 科目コード),
cte_kasi
as(
select 年度,
科目コード, sum(金額-内消費税) as 貸方
from kaikei.work_仕訳_明細_税集計
:WPARAM5 --元は where 年度 = 2024
and 仕訳貸借 in('貸方')
group by 年度, 科目コード
)
select
COALESCE(cte_zenki_touki.年度, COALESCE(cte_zenki_touki.年度, COALESCE(cte_kasi.年度, cte_kari.年度))) as 年度,
COALESCE(cte_zenki_touki.科目コード, COALESCE(cte_kasi.科目コード, cte_kari.科目コード)) as 科目コード,
勘定科目名,
COALESCE(cte_zenki_touki.前残, 0) as 前残,
COALESCE(借方, 0) as 借方,
COALESCE(貸方, 0) as 貸方,
COALESCE(cte_zenki_touki.残高, 0) as 残高,
case m.要素コード
when '1' then COALESCE(cte_zenki_touki.残高, 0)
when '2' then (COALESCE(貸方, 0) - COALESCE(借方, 0))
when '3' then COALESCE(cte_zenki_touki.残高, 0)
when '4' then COALESCE(cte_zenki_touki.残高, 0)
when '5' then COALESCE(貸方, 0) - COALESCE(借方, 0)
when 'X' then 0
end 評価
from kaikei.ma_勘定科目 m
left outer join cte_kari on m.勘定科目コード = cte_kari.科目コード
left outer join cte_kasi on m.勘定科目コード = cte_kasi.科目コード
left outer join cte_zenki_touki on m.勘定科目コード = cte_zenki_touki.科目コード
where not (cte_kasi.年度 is null and cte_kari.年度 is null and cte_zenki_touki.年度 is null)
and not ( 前残 = 0 and 借方 = 0 and 貸方 = 0 and 残高 = 0)
order by 科目コード
";
public static function dryRun():array|bool
{
Log::debug(__FUNCTION__);
try{
$sqlstring = str_replace(":WPARAM1","where 年度 < ? ",self::$sqlstring);
$sqlstring = str_replace(":WPARAM2","where 年度 <= ? ",$sqlstring);
$sqlstring = str_replace(":WPARAM3","?",$sqlstring);
$sqlstring = str_replace(":WPARAM4","where 年度 = ? ",$sqlstring);
$sqlstring = str_replace(":WPARAM5","where 年度 = ? ",$sqlstring);
#以下ををいろいろ書き換えて試す。テストの本来の使い方からは逸脱でしょうけども
# return DB::select( $sqlstring,[2024,2024,2024,2024,2024]);
#return DB::select( $sqlstring,[2024,2024,'hoge',2024,2024]);
#こんなのはどうでしょう
return DB::select( $sqlstring,[2024,2024,'; select 1; --',2024,2024]);
}catch(\Exception $e){
Log::error($e->getMessage());
return false;
}
}
//以下変更無し
:WPARAM1,:WPARAM2等々を順次文字列置き換えで、？ パラメータの入った文字列とし、その上で
DB::select で配列としてパラメータを渡す構成になっているのは、
DB::select("select * from hoge ?",["where fuga = 1"]);
と言うような書き方は許されていないから（たぶん）です。これが可能なら、単純な文字列置き換えに過ぎず、bindして渡す事が、sqlインジェクション対策に繋がりません。
また、
select distinct :WPARAM3::integer as 年度
の部分は、phpでの文字列置き換えを経て
select distinct ?::integer as 年度
となるのですが、これはサーバー側prepareで通ることを確認出来ました。
テスト側は、
class KaikeiWorkModelTest extends TestCase
{
public function test_example(): void
{
$response = KaikeiWorkModel::dryRun();
$this->assertTrue( is_array($response) or $response == false );
if(is_array($response)){ dump($response[0]);}else{
dump($response);
}
}
//以下変更なし
続いて、この :WPARAM1 〜 :WPARAM5を書き換えることで、年度ではなく、締日、特定の日付の範囲で選択などの当初からの試算表算出の目的に沿うSQLが走るのかを確認します。