[loadGantt]
select
  h.shijisyo_no
, h.jyucyu_no
, h.jyucyu_row_no
, h.kousu_kei
, h.tokui_cd
, h.tokui_nm
, h.syohin_nm
, h.shiji_suryo
, h.nouki_dt
, to_date(h.nouki_dt,'yyyymmdd') - to_date(':start_dt','yyyymmdd') as day_i
, to_char(to_date(h.nouki_dt,'yyyymmdd'),'mm/dd') as nouki_dt_s
, h.sekkei_stt_ymd
, h.sekkei_end_ymd
--, h.sekkei_kousu
, ceil(h.sekkei_kousu) as sekkei_kousu
, to_date(h.sekkei_stt_ymd,'yyyymmdd') - to_date(':start_dt','yyyymmdd') as sekkei_stt_day_i
, to_date(h.sekkei_end_ymd,'yyyymmdd') - to_date(':start_dt','yyyymmdd') as sekkei_end_day_i
, (case when(h.sekkei_flg = 1)then(9)else(0)end) as sekkei_state_i
, hi.irai_cnt
, hi.haccyu_irai_dt as haccyu_stt_ymd
, COALESCE(hi.nyuka_yotei_ymd,hi.irai_nyuka_yotei_ymd) as haccyu_end_ymd
, to_date(hi.haccyu_irai_dt,'yyyymmdd') - to_date(':start_dt','yyyymmdd') as haccyu_stt_day_i
, to_date(COALESCE(hi.nyuka_yotei_ymd,hi.irai_nyuka_yotei_ymd),'yyyymmdd') - to_date(':start_dt','yyyymmdd') as haccyu_end_day_i
--1:着手中,9:完了,0:未着手
, (case when(hi.nyuka_end_cnt >= hi.irai_cnt)then(9)when(hi.nyuka_stt_cnt > 0)then(1)else(0)end) as haccyu_state_i
, h.eigyoukensa_flg
, h.sekkeikensa_flg
, h.kikakukensa_flg
, h.syukka_flg
from v_syukka_yotei h
left join (
	select
	  w.shijisyo_no
	, count(*) as irai_cnt
	, min(w.haccyu_irai_dt) as haccyu_irai_dt
	, max(w.irai_nyuka_yotei_ymd) as irai_nyuka_yotei_ymd
	, min(w.haccyu_ymd) as haccyu_ymd
	, max(w.nyuka_yotei_ymd) as nyuka_yotei_ymd
	, sum(w.nyuka_stt_flg) as nyuka_stt_cnt
	, sum(w.nyuka_end_flg) as nyuka_end_cnt
	from (
		select
		  sm.shiji_no as shijisyo_no
		, sm.row_no as siyou_row_no
		, sm.hacyu_irai_ymd as haccyu_irai_dt
		, to_char(to_date(sm.hacyu_irai_ymd,'yyyymmdd') + interval '3 days','yyyymmdd') as irai_nyuka_yotei_ymd
		, hs.haccyu_ymd
		, hs.nyuka_yotei_ymd
		, (case when(COALESCE(hs.nyuka_suryo,0) > 0)then(1)else(0)end) as nyuka_stt_flg
		, (case when(COALESCE(hs.nyuka_suryo,0) >= sm.hacyu_irai_su)then(1)else(0)end) as nyuka_end_flg
		from t_shiyou_zairyo sm
		left join v_haccyu_sum hs on (hs.shijisyo_no = sm.shiji_no and hs.siyou_row_no = sm.row_no)
	) w
	group by w.shijisyo_no
) hi on (hi.shijisyo_no = h.shijisyo_no)

where 1 = 1
and kousu_kei > 0
and h.nouki_dt >= ':nouki_dt'
and ((':nonyu_ymd_to' = '') or (h.nouki_dt <= ':nonyu_ymd_to'))
--TODO
and ((':nonyu_ymd_to' != '') or (h.nouki_dt < to_char(to_date(':start_dt','yyyymmdd') + interval '7 days','yyyymmdd')))
and ((h.jyucyu_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
and ((h.shijisyo_no = fnc_to_number(':shiji_no')) or ('' = ':shiji_no'))
and (('' = ':tok_cd') or (h.tokui_cd = ':tok_cd'))
and ((h.tokui_nm like '%:tok_nm%') or ('' = ':tok_nm'))
--and (('' = ':hin_cd') or (t.hin_cd = ':hin_cd'))
and ((h.syohin_nm like '%:hin_nm%') or ('' = ':hin_nm'))
order by nouki_dt, shijisyo_no
--TODO
--limit 30

[loadProcs]
select
  m.shijisyo_no
, m.koutei_no
, m.koutei_cd
--, COALESCE(m.koutei_rnm,m.koutei_nm) as koutei_rnm
, m.koutei_cd as koutei_rnm
--, m.kousu
, ceil(m.kousu) as kousu
, m.status
--1:着手中,9:完了,0:未着手
, (case(m.status)when(0)then(0)when(1)then(9)else(1)end) as state_i
--, (select ceil(sum(kousu)) from v_koutei m2 where m2.shijisyo_no = m.shijisyo_no and m2.koutei_no < m.koutei_no) as pre_kousu
, cast(m.yotei_stt_time as date) - to_date(':start_dt','yyyymmdd') as day_i
, EXTRACT(HOUR FROM m.yotei_stt_time) / 3 as hour_i
from v_koutei m
where m.shijisyo_no = :w_shijisyo_no
order by koutei_no

[doImport]
select fnc_load_smile_trn(':login_id')

[deleteImportWork]
truncate table w_smile1;
truncate table w_smile2;
truncate table w_smile3;

[checkImportWork]
select count(*) as cnt_i from :w_table_id

[load_grdList]
select
  w.koutei_cd
, v.koutei_nm
, sum(case when(w.syori_ymd = ':start_dt')then(w.kousu)else(null)end) as kousu0
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '1 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu1
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '2 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu2
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '3 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu3
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '4 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu4
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '5 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu5
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '6 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu6
from (
select
  sm.koutei_cd
, to_char(sm.yotei_stt_time,'yyyymmdd') as syori_ymd
, sum(kousu) as kousu
from t_seizou_m sm
inner join t_seizou sh on (sh.shiji_no = sm.shiji_no)
inner join t_uri_m jm on (jm.den_no = sh.jucyu_no and jm.row_no = sh.jucyu_row_no)
inner join t_uri jh on (jh.den_no = jm.den_no)
where 1 = 1
and jm.nonyu_ymd >= ':nouki_dt'
and ((':nonyu_ymd_to' = '') or (jm.nonyu_ymd <= ':nonyu_ymd_to'))
--TODO
and ((':nonyu_ymd_to' != '') or (jm.nonyu_ymd < to_char(to_date(':start_dt','yyyymmdd') + interval '7 days','yyyymmdd')))
and ((sh.jucyu_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
and ((sm.shiji_no = fnc_to_number(':shiji_no')) or ('' = ':shiji_no'))
and (('' = ':tok_cd') or (jh.tok_cd = ':tok_cd'))
and ((jh.tok_nm like '%:tok_nm%') or ('' = ':tok_nm'))
and (('' = ':hin_cd') or (jm.hin_cd = ':hin_cd'))
and ((jm.hin_nm like '%:hin_nm%') or ('' = ':hin_nm'))
group by sm.koutei_cd, to_char(sm.yotei_stt_time,'yyyymmdd')
) w
left join v_m_koutei v on (v.koutei_cd = w.koutei_cd)
group by w.koutei_cd, v.koutei_nm
order by koutei_cd