[loadHeader]
select start_ymd, anken_su
from t_yplan
where nendo = :nendo

[saveHeader]
insert into t_yplan (
  nendo
, del_flg
, cre_id
, cre_time
)
select
  :nendo
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
where not exists (
select 1 from t_yplan
where nendo = :nendo
);
update t_yplan set
  start_ymd = ':start_ymd'
, anken_su = :anken_su
, upd_id = ':login_id'
, upd_time = now()
where nendo = :nendo
;

[checkPlanMonth]
select syori_ym
from t_yplan_m
where nendo = :nendo

[resetNendo]
select fnc_update_reset_yplan(:nendo, ':login_id')

[updateOriginal]
select fnc_update_yplan(:nendo, ':login_id')


[load_grdList]
select
  w.syori_ym
, substr(w.syori_ym,1,4)||'/'||substr(w.syori_ym,5,2) as syori_ym_s
, w.anken_su
, w.jisseki_su
, COALESCE(w.jisseki_su,0) - COALESCE(w.anken_su,0) as sai_su
, w.mi_su
, w.biko
, 1 as rec_flg
from (
select
  t.syori_ym
, t.anken_su
, (
	select count(*)
	from t_yplan_anken a
	inner join t_anken b on (b.anken_cd = a.anken_cd)
	where a.nendo = t.nendo
	and a.syori_ym = t.syori_ym
	and b.jucyu_ymd != ''
	)
+ (
	select count(*)
	from t_anken b
	left join t_yplan_anken a on (b.anken_cd = a.anken_cd)
	left join m_hanyo h1 on (h1.mst_kbn = '58' and h1.han_cd = b.anken_kbn)
	where 1 = 1
	and b.end_ymd between t.syori_ym||'01' and t.syori_ym||'99'
	and COALESCE(b.cancel_ymd,'') = ''
	and b.jucyu_ymd != ''
	and a.yplan_anken_cd is null
	--リフォームは除く
	and COALESCE(h1.koumoku1,'') != '1'
	)
 as jisseki_su
, (
	select count(*)
	from t_yplan_anken a
	inner join t_anken b on (b.anken_cd = a.anken_cd)
	where a.nendo = t.nendo
	and a.syori_ym = t.syori_ym
	and COALESCE(b.jucyu_ymd,'') = ''
	)
 as mi_su_linked
, (
	select count(*)
	from t_anken b
	left join m_hanyo h1 on (h1.mst_kbn = '58' and h1.han_cd = b.anken_kbn)
	where 1 = 1
	and b.end_ymd between t.syori_ym||'01' and t.syori_ym||'99'
	and COALESCE(b.jucyu_ymd,'') = ''
	and COALESCE(b.cancel_ymd,'') = ''
	--リフォームは除く
	and COALESCE(h1.koumoku1,'') != '1'
	)
 as mi_su
, t.biko
from t_yplan_m t
where t.nendo = :nendo
) w
order by syori_ym