[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
--#6424 2018.11.24 MOD STT
--and h.nouki_dt >= ':nouki_dt'
and ((':nouki_dt' = '') or (h.nouki_dt >= ':nouki_dt'))
--#6424 2018.11.24 MOD END
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 ':day_size 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'))
and (('0' != ':cond_status') or (COALESCE(h.koutei_end_flg,0) != 1))
and (('1' != ':cond_status') or (COALESCE(h.koutei_end_flg,0) = 1))
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
, cast(EXTRACT(HOUR FROM m.yotei_stt_time) / 3 as int) as hour_i
from v_koutei m
where m.shijisyo_no = :w_shijisyo_no
order by koutei_no

[loadTempProcs]
select
  m.shijisyo_no
, m.koutei_no
, m.koutei_cd
, m.koutei_cd as koutei_rnm
, ceil(m.kousu) as kousu
, m.status
, (case(m.status)when(0)then(0)when(1)then(9)else(1)end) as state_i
, cast(m.yotei_stt_time as date) - to_date(':start_dt','yyyymmdd') as day_i
, cast(EXTRACT(HOUR FROM m.yotei_stt_time) / 3 as int) as hour_i
from (
	select
	  t.shiji_no as shijisyo_no
	, t.koutei_no
	, t.koutei_cd
	, t.kousu
	, t.seizou_tan_cd as seizo_tanto
	, t.seizou_biko as sagyou_biko
	, t.status
	, t.yotei_stt_time
	, t.yotei_end_time
	, t.stt_time
	, t.end_time
	, t.stop_time
	, t.restart_time
	, t.stop_kikan
	, m1.koutei_nm
	, h.jucyu_no as jyucyu_no
	, h.jucyu_row_no as jyucyu_row_no
	, jh.tok_cd as tokui_cd
	, (case when(jh.tok_cd is null)then(jh.tok_nm)else(m2.tok_nm)end) as tokui_nm
	, m1.koutei_nm as koutei_rnm
	, jm.hin_nm as syohin_nm
	, h.suryo as shiji_suryo
	from w_basv3130_seizou_m t
	inner join w_basv3130_seizou h on (h.s_id = :s_id and h.shiji_no = t.shiji_no)
	left join t_uri jh on (jh.den_no = h.jucyu_no)
	left join t_uri_m jm on (jm.den_no = h.jucyu_no and jm.row_no = h.jucyu_row_no)
	left join v_m_koutei m1 on (m1.koutei_cd = t.koutei_cd)
	left join m_tokui m2 on (m2.tok_cd = jh.tok_cd)
	where 1 = 1
	and t.s_id = :s_id
	and jh.den_no is not null
) m
where m.shijisyo_no = :w_shijisyo_no
order by koutei_no

[saveProc]
update t_seizou_m set
  yotei_stt_time = to_timestamp(':start_dt','yyyymmdd') + cast(':day_i days' as interval) + cast(cast((:hour_i * 3) as varchar)||' hours' as interval)
, upd_id = ':login_id'
, upd_time = now()
where shiji_no = :shiji_no
and koutei_no = :koutei_no
;
update t_seizou_m set
  yotei_end_time = yotei_stt_time + cast(cast(kousu * 3 as varchar)||' hours' as interval)
, upd_id = ':login_id'
, upd_time = now()
where shiji_no = :shiji_no
and koutei_no = :koutei_no
;
update t_seizou set
  manual_flg = 1
, upd_id = ':login_id'
, upd_time = now()
where shiji_no = :shiji_no
and manual_flg = 0
;

[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
, v.kousu_iki
, v.sort_key
, 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
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '7 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu7
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '8 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu8
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '9 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu9
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '10 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu10
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '11 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu11
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '12 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu12
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '13 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu13
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '14 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu14
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '15 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu15
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '16 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu16
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '17 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu17
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '18 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu18
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '19 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu19
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '20 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu20
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '21 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu21
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '22 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu22
, sum(case when(w.syori_ymd = to_char(to_date(':start_dt','yyyymmdd') + interval '23 days','yyyymmdd'))then(w.kousu)else(null)end) as kousu23
from (
select
  sm.koutei_cd
--, to_char(sm.yotei_stt_time,'yyyymmdd') as syori_ymd
, sm.syori_ymd
, sum(kousu) as kousu
--from t_seizou_m sm
from t_seizou_day 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
--2018.04.05 MOD STT
--and jm.nonyu_ymd >= ':nouki_dt'
--#6424 2018.11.24 MOD STT
--and jm.syotei_ymd >= ':nouki_dt'
and ((':nouki_dt' = '') or (jm.syotei_ymd >= ':nouki_dt'))
--#6424 2018.11.24 MOD END
--2018.04.05 MOD END
--2018.04.05 MOD STT
--and ((':nonyu_ymd_to' = '') or (jm.nonyu_ymd <= ':nonyu_ymd_to'))
--and ((':nonyu_ymd_to' != '') or (jm.nonyu_ymd < to_char(to_date(':start_dt','yyyymmdd') + interval ':day_size days','yyyymmdd')))
and ((':nonyu_ymd_to' = '') or (jm.syotei_ymd <= ':nonyu_ymd_to'))
and ((':nonyu_ymd_to' != '') or (jm.syotei_ymd < to_char(to_date(':start_dt','yyyymmdd') + interval ':day_size days','yyyymmdd')))
--2018.04.05 MOD END
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')
group by sm.koutei_cd, sm.syori_ymd
) w
left join v_m_koutei v on (v.koutei_cd = w.koutei_cd)
group by w.koutei_cd, v.koutei_nm, v.kousu_iki, v.sort_key
order by sort_key, koutei_cd

[doOptimize]
select fnc_update_optimize_seizou(:s_id, ':nouki_dt', to_char(to_date(':nouki_dt','yyyymmdd') + interval '4 days','yyyymmdd'), ':login_id')

[saveOptimize]
select fnc_update_optimize_seizou_commit(:s_id, ':login_id')