会計ソフトの作成 20 日付範囲指定の試算表

年次、月次の試算表が可能となったので、次は、特定の日付範囲指定での試算表を試みます。
当初の構想では、期をまたいでのそうした試算表的なものが算出できないかという事がありましたが、P/L科目の前残や当残は期首を0とした場合のそこからの累計と言うことになっていて、これまでの作成もこの基準を生かす形に作っているので、期をまたいでのそれは後回しにします。（それはもう本来の試算表の範疇ではありませんし）

関数名は、getTrialBalanceByPeriod　内容は、ByMonthのそれの微調整で可能でした。

public static function getTrialBalanceByPeriod(string $startDateStr, string $endDateStr): array|bool
{
 try {
  $sqlstring = 'with '.
  
  self::makeZanCte('cte_zen', '前残', ':WPARAM1').
  self::makeZanCte('cte_tou', '当期残', ':WPARAM2').

  self::$cte_common.
  //ここが差し替え
  self::makeKariKasiPeriodCte('cte_kari', '借方', ':WPARAM4', '借方').
  self::makeKariKasiPeriodCte('cte_kasi', '貸方', ':WPARAM5', '貸方', '科目コード', true).
  
  self::makeZanCte('cte_pl_zen', '前締日残', ':WPARAM6').
  self::$cte_pl.
  //ここも差し替え
  self::$sqlstring_period;

  $sqlstring = str_replace(':Q_TYPE', '締日', $sqlstring);
  $sqlstring = str_replace(':WPARAM1', 'where 伝票日付 < ? ', $sqlstring);
  $sqlstring = str_replace(':WPARAM2', 'where 伝票日付 <= ? ', $sqlstring);
  $sqlstring = str_replace(':WPARAM3', '?::date as 締日,', $sqlstring);
  $sqlstring = str_replace(':WPARAM4', 'where 伝票日付 between ? and ? ', $sqlstring);
  $sqlstring = str_replace(':WPARAM5', 'where 伝票日付 between ? and ? ', $sqlstring);
  $sqlstring = str_replace(':WPARAM6', " where 年度 < business_year( ? ) and 要素名 in('収益','費用')", $sqlstring);

  return DB::select($sqlstring, [$startDateStr, $endDateStr,
    $startDateStr, $startDateStr, $endDateStr, $startDateStr, $endDateStr, $startDateStr, $startDateStr]);
 } catch (\Exception $e) {
   Log::error($e->getMessage());
   return false;
 }

}

差し替えの部分は

public static function makeKariKasiPeriodCte($cteName, $colName, $whereParam, $kariKasi, $groupParam = '科目コード', $comma = true)
{
 $cte_karikasi_g = "
  :CTE_NAME
  as(
   select :GROUPPARAM,
    sum(金額-内消費税) as :COL_NAME
   from kaikei.work_仕訳_明細_税集計
    :WPARAM
    and 仕訳貸借 in(':KARIKASI')
    group by :GROUPPARAM)
 ";

 $cte_karikasi_g = str_replace(':CTE_NAME', $cteName, $cte_karikasi_g);
 $cte_karikasi_g = str_replace(':COL_NAME', $colName, $cte_karikasi_g);
 $cte_karikasi_g = str_replace(':WPARAM', $whereParam, $cte_karikasi_g);
 $cte_karikasi_g = str_replace(':KARIKASI', $kariKasi, $cte_karikasi_g);
 $cte_karikasi_g = str_replace(':GROUPPARAM', $groupParam, $cte_karikasi_g);

 if ($comma) {
  $cte_karikasi_g .= ',';
 }
 return $cte_karikasi_g;
}

private static $sqlstring_period = "

select distinct
COALESCE(cte_zen_tou.:Q_TYPE, cte_zen_tou.:Q_TYPE ) as :Q_TYPE,
COALESCE(cte_zen_tou.科目コード, COALESCE(cte_kasi.科目コード, cte_kari.科目コード)) as 科目コード,
勘定科目名,
case m.要素コード
when '5' then to_int(前損益累計)
when '2' then to_int(前損益累計)
else to_int(前残)
end as 前残,
to_int(借方) as 借方,
to_int(貸方) as 貸方,
case m.要素コード
when '5' then
 case m.科目貸借
  when '貸方' then to_int(前損益累計) - to_int(借方) + to_int(貸方)
  else to_int(前損益累計) + to_int(借方) - to_int(貸方)
 end
when '2' then
 case  m.科目貸借
  when '借方' then to_int(前損益累計) + to_int(借方) - to_int(貸方)
  else  -( to_int(前損益累計) + to_int(借方) - to_int(貸方))
 end
else to_int(残高) end as 残高,
m.要素コード,
case m.要素コード
when '1' then to_int(残高)
when '3' then to_int(残高)
when '4' then to_int(残高)
when '2' then
 case m.科目貸借
  when '貸方' then ( to_int(前損益累計) - to_int(借方) + to_int(貸方))
  else - ( to_int(前損益累計) + to_int(借方) - to_int(貸方) )
 end
when '5' then
 case m.科目貸借
  when '貸方' then to_int(前損益累計) - to_int(借方) + to_int(貸方)
  else -( to_int(前損益累計) + to_int(借方) - to_int(貸方) )
 end
when 'X' then 0
end 評価
from kaikei.view_勘定科目補助科目 m
left outer join cte_kari using(科目コード)
left outer join cte_kasi using(科目コード)
left outer join cte_zen_tou using(科目コード)
left outer join cte_pl_zen_tou using(科目コード)
where not ( cte_zen_tou.:Q_TYPE is null)
and not ( 前残 = 0 and 借方 = 0 and 貸方 = 0 and 残高 = 0 and 前損益累計 = 0 )
order by 科目コード
";

テスト側は、とりあえず

 public function test_get_trial_balance_by_period(): void
 {

 $sql="

SELECT distinct
date_trunc('month', ts) ::date as start_date,
(date_trunc('month', ts) + interval '1 month - 1 day')::date as end_date
FROM generate_series('2020-03-01'::timestamp, '2025-02-28'::timestamp, '1 day') AS ts
order by 1
";

 $arr = DB::select($sql);
 foreach ($arr as $key => $val) {

  $年月 = substr(str_replace('-', '', substr($val->start_date, 0, 7)), 2, 4);

  $response = KaikeiWorkModel::getTrialBalanceByPeriod($val->start_date,$val->end_date);
  $this->assertTrue(is_array($response));
  if (is_array($response)) {

  foreach ($response as $key => $val) {

   $sql = 'select * from wkkaikei.pca月次試算 where 年月= ? and 科目コード = ? ';
   $chkar = DB::select($sql, [$年月, $val->科目コード]);

 if ($chkar[0]->前残 != $val->前残) { dump($chkar[0], $val); }
 if ($chkar[0]->借方金額 != $val->借方) { dump($chkar[0], $val); }
 if ($chkar[0]->貸方金額 != $val->貸方) { dump($chkar[0], $val); }
 if ($chkar[0]->残高 != $val->残高) { dump($chkar[0], $val); }

 }

 }
 }

}

テストしているのは、毎月月初から月末を日付指定して、その月の月次試算表を算出する関数との比較です。
これが一緒にならないようではお話にならないので。

問題になるのは、この後で、期首1日目なら、年365日として355日がとり得る値、期首2日目なら、364日がとり得る値という全く不規則（startDate <= endDate で、かつ両方の日付は同一期内という条件はあり）な計算結果をが正しい事をどうやってテストするのかということでした。