LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@qiita_boolean(boolean)

会計ソフトの作成 18 CTEの部品化でP/L科目の月次試算表への対応

Last updated at Posted at 2025-07-07

単一SQLの微調整で、年次、月次の試算表への対応が無理とわかり、方針を転換しました。
そこで、改めて、起きている問題を考えてみると

  1. 年次では動作する当初のSQLには、足りないCTEが一つあること
  2. 当初のSQLには、ほとんど同じ形式のCTEが多用されている事（元々それを狙っていたのですが）
    がわかりました。

ほぼ同じ内容が続く、長々しいSQLを、年度用、月次用に２つ残しておくのは得策では無いので、SQL自身を上から見ると、

  1. ほぼ同じ形式のCTEが繰り返し使われ
  2. そのCTEをまとめ上げた形でQUERYを組み立てて、DB::selectに渡している形であること
    になっています。
    そこで、まず、CTEを組み上げる関数を作りました。
public static function makeZanCte($cteName, $colName, $whereParam, $groupParam = '科目コード', $comma = true)
{

$cte_zan_g = "

:CTE_NAME
as(
select :GROUPPARAM,sum(
case
when 要素名 ='収益' and 科目貸借 = '借方' then - 税抜増減
when 要素名 ='費用' and 科目貸借 = '貸方' then - 税抜増減
else 税抜増減 end
)as :COL_NAME
from kaikei.work_仕訳_明細_増減
:WPARAM
group by :GROUPPARAM)
";

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

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

return $cte_zan_g;
}

累計での残高を算出する部分はこのパターンに収斂するはずです。
groupParamを渡しているのは、この後、勘定科目＋補助科目での集計を同じ関数の使い回して実現できそうな事への布石です。
commnaパラメータは、CTE数珠つなぎにしていく事になるのですが、終端となる場合は不要な事への対応です。

もう一つ、CTE生成関数

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

}

こちらは、貸借を集計する部分です。二つの関数の差異も微妙だから、単一化もできそうですけど、汎用的すぎて後のコードの意味が掴みにくそうなこともあり、ここまで。
単一SQLは消えて、幾つかの文字列が必要となります。


private static $cte_common = "

cte_zen_tou
as(
select distinct :WPARAM3
科目コード,
case when 要素名 in('収益', '費用') then 0 else 前残 end as 前残,
case when 要素名 in('収益', '費用') then 当期残 -前残 else 当期残 end as 残高
from cte_zen
join kaikei.view_勘定科目補助科目 using(科目コード)
full outer join cte_tou using( 科目コード)
),

";

private static $cte_pl = '
cte_pl_zen_tou
as(
select distinct ?::date as 締日,
科目コード,
前残 - 前締日残 as 前損益累計
from cte_pl_zen
  --ここでcte_pl_zen を使っているのでそのCTEを先に組み上げて置く必要あり
join kaikei.view_勘定科目補助科目 using(科目コード)
full outer join cte_zen using(科目コード)
)

';

なお、全てジョインする勘定科目側テーブルは kaikei.view_勘定科目補助科目に変えています。
勘定科目＋補助科目での集計の場合 using(科目コード,補助コード)で対応できるはずだからです。
組み上げたCTE群からQueryするselect部分が二つ

private static $sqlstring = "

select distinct
COALESCE(cte_zen_tou.:Q_TYPE, COALESCE(cte_zen_tou.:Q_TYPE, COALESCE(cte_kasi.:Q_TYPE, cte_kari.:Q_TYPE))) as :Q_TYPE,
COALESCE(cte_zen_tou.科目コード, COALESCE(cte_kasi.科目コード, cte_kari.科目コード)) as 科目コード,
勘定科目名,
to_int(前残) as 前残,
to_int(借方) as 借方,
to_int(貸方) as 貸方,
to_int(残高) as 残高,
case m.要素コード
when '2' then to_int(貸方) - to_int(借方)
when '5' then to_int(貸方) - to_int(借方)
when '3' then to_int(残高)
when '1' then to_int(残高)
when '4' then to_int(残高)
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(科目コード)
where not (cte_kasi.:Q_TYPE is null and cte_kari.:Q_TYPE is null and cte_zen_tou.:Q_TYPE is null)
and not ( 前残 = 0 and 借方 = 0 and 貸方 = 0 and 残高 = 0 )
order by 科目コード

";

private static $sqlstring_month = "

select distinct
COALESCE(cte_zen_tou.:Q_TYPE, COALESCE(cte_zen_tou.:Q_TYPE, COALESCE(cte_kasi.:Q_TYPE, cte_kari.: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 to_int(前損益累計) + to_int(借方) - to_int(貸方)
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_kasi.:Q_TYPE is null and cte_kari.:Q_TYPE is null and cte_zen_tou.:Q_TYPE is null)
and not ( 前残 = 0 and 借方 = 0 and 貸方 = 0 and 残高 = 0 and 前損益累計 = 0 )
order by 科目コード

";

勘定科目＋補助科目のパターンになった場合、この部分を調整することになりそうです。
to_int()というのは、integer numeric 型でnullを渡すと０を返すという簡単な自作関数です。
COALESCEの繰り返しよりは可読性がありそうなので入れています。

使う方の、年度、月次試算表取得関数は次の通り変更します。

public static function getTrialBalanceByYear(int $year): array|bool
{

  try {
    if (! self::isExistYear($year)) {
      throw new \Exception($year.' is not balance year');
    }

  //まずsqlstringを組み上げて
  $sqlstring = 'with '.
  self::makeZanCte('cte_zen', '前残', ':WPARAM1').
  self::makeZanCte('cte_tou', '当期残', ':WPARAM2').
  self::$cte_common.
  self::makeKariKasiCte('cte_kari', '借方', ':WPARAM4', '借方').
  self::makeKariKasiCte('cte_kasi', '貸方', ':WPARAM5', '貸方', '科目コード', false).
  self::$sqlstring;

  //後は同じ
  $sqlstring = str_replace(':Q_TYPE', '年度', $sqlstring);
  $sqlstring = str_replace(':WPARAM1', 'where 年度 < ? ', $sqlstring);
  $sqlstring = str_replace(':WPARAM2', 'where 年度 <= ? ', $sqlstring);
  $sqlstring = str_replace(':WPARAM3', '?::integer as 年度,', $sqlstring);
  $sqlstring = str_replace(':WPARAM4', 'where 年度 = ? ', $sqlstring);
  $sqlstring = str_replace(':WPARAM5', 'where 年度 = ? ', $sqlstring);

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

}

public static function getTrialBalanceByMonth(string $dateStr): array|bool
{

  try {
    if (! self::isExistCutoffDate($dateStr)) {
      throw new \Exception($dateStr.' is not cutoff date ');
    }

  //SQLの組み上げ方がちょっと違うだけ
  $sqlstring = 'with '.
  self::makeZanCte('cte_zen', '前残', ':WPARAM1').
  self::makeZanCte('cte_tou', '当期残', ':WPARAM2').
  self::$cte_common.
  self::makeKariKasiCte('cte_kari', '借方', ':WPARAM4', '借方').
  self::makeKariKasiCte('cte_kasi', '貸方', ':WPARAM5', '貸方', '科目コード', true).
  self::makeZanCte('cte_pl_zen', '前締日残', ':WPARAM6').
  self::$cte_pl.
  self::$sqlstring_month;

  $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 締日 = ? ', $sqlstring);
  $sqlstring = str_replace(':WPARAM5', 'where 締日 = ? ', $sqlstring);
  $sqlstring = str_replace(':WPARAM6', " where 年度 < business_year( ? ) and 要素名 in('収益','費用')", $sqlstring);

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

}

出来上がった試算表の確認をするための、簡単なdump関数を追加します


public static function dump($resource, $filename)
{

echo "\n";
　$output = '';
  foreach ($resource as $key => $val) {
  $output .=
　　($val->年度 ?? $val->締日).','.$val->科目コード.','.
  　$val->勘定科目名.','.$val->前残.','.$val->借方.','.
  　$val->貸方.','.$val->残高.','.$val->評価.',';

　　　$output .= "\n";

　　}
　　
  　echo $output."\n";
    Storage::disk('XXX')->put('/kaikeiapp/'.$filename, $output);
    //Windows側で比較するなら生かす
    //system('nkf -s -Lw --overwrite  /XXX/kaikeiapp/'.$filename, $res);
}

テストの書き換えと、実行

public function test_get_trial_balance_by_year(): void
{
 $response = KaikeiWorkModel::getTrialBalanceByYear(1000);
 $this->assertFalse($response);

 $response = KaikeiWorkModel::getTrialBalanceByYear(2024);
 $this->assertTrue(is_array($response));
 if (is_array($response)) {
  echo $response[0]->年度;
   KaikeiWorkModel::dump($response, 'byyear.csv');
 }

}

public function test_get_trial_balance_by_month(): void
{
 $response = KaikeiWorkModel::getTrialBalanceByMonth('2100/12/31');
 $this->assertFalse($response);

 $response = KaikeiWorkModel::getTrialBalanceByMonth('年度内締日のいずれか');
 $this->assertTrue(is_array($response));
 if (is_array($response)) {
  echo $response[0]->締日;
  KaikeiWorkModel::dump($response, 'bymonth.csv');
 }
}

これで、テスト年、テスト締日については、年度試算表、月次試算表双方が算出されているように見えています。
テストでなく、実際の過去データとの比較を考えると、例えば過去１０年遡ると、年度なら１０組のCSVの比較で良いのですが、月次だと、１２０組のCSVの比較となってしまいます。
月次での実データとの比較は、それ自体なんか考えないといけない量になります。

[追記]
元アプリ側が算出している月次試算表との対比は、結局そのデータもDBに入れてしまって年月と科目コードで比較する事にしました。これでは、元データがあるじゃないというお話にもなってしまいますが、今後増えていく月次データも投入時にテストできるし、自作側が変な数値を算出した場合もいろいろな切り方で元アプリの数値と比較点検できるので、結局それが楽ではという結論です。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?