[load_grdList]
select
  t.anken_cd
, t.anken_nm
, t.start_ymd
, t.jucyu_ymd
, t.end_ymd
, t.biko
, (case when(t.status = 1 and COALESCE(t.cancel_ymd,'') = '')then('完了')
        when(t.status = 1 and t.cancel_ymd != '')then('取消')
        when(t.status = 3)then('中断')else('')end) as status_nm
, fnc_get_anken_ops(t.anken_cd) as all_op_nm
, mt1.tan_nm as tan1_nm
, mt2.tan_nm as tan2_nm
, mt3.tan_nm as tan3_nm
, mt4.tan_nm as tan4_nm
, mt5.tan_nm as tan5_nm
, mt6.tan_nm as tan6_nm
, mt7.tan_nm as tan7_nm
, mt8.tan_nm as tan8_nm
, r.val_id
, r.val_nm
, (case when(aa.image_cnt > 0)then('有')else('')end) as image_umu
from t_anken t
left join (
select 1 as val_id, '事業計画' as val_nm where :row1 = 1
union all select 2 as val_id, '実施計画' as val_nm where :row2 = 1
union all select 3 as val_id, '対事業計画差異' as val_nm where :row3 = 1
union all select 4 as val_id, '実施日' as val_nm where :row4 = 1
union all select 5 as val_id, '対実施計画差異' as val_nm where :row5 = 1
union all select 6 as val_id, '工程毎差異' as val_nm where :row6 = 1
) r on (1 = 1)
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t1 on (t1.anken_cd = t.anken_cd and t1.tan_kind = '01')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t2 on (t2.anken_cd = t.anken_cd and t2.tan_kind = '02')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t3 on (t3.anken_cd = t.anken_cd and t3.tan_kind = '03')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t4 on (t4.anken_cd = t.anken_cd and t4.tan_kind = '04')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t5 on (t5.anken_cd = t.anken_cd and t5.tan_kind = '05')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t6 on (t6.anken_cd = t.anken_cd and t6.tan_kind = '06')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t7 on (t7.anken_cd = t.anken_cd and t7.tan_kind = '07')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t8 on (t8.anken_cd = t.anken_cd and t8.tan_kind = '08')
left join m_tanto mt1 on (mt1.tan_cd = t1.tan_cd)
left join m_tanto mt2 on (mt2.tan_cd = t2.tan_cd)
left join m_tanto mt3 on (mt3.tan_cd = t3.tan_cd)
left join m_tanto mt4 on (mt4.tan_cd = t4.tan_cd)
left join m_tanto mt5 on (mt5.tan_cd = t5.tan_cd)
left join m_tanto mt6 on (mt6.tan_cd = t6.tan_cd)
left join m_tanto mt7 on (mt7.tan_cd = t7.tan_cd)
left join m_tanto mt8 on (mt8.tan_cd = t8.tan_cd)
left join (select anken_cd, count(*) as image_cnt from t_anken_atch group by anken_cd) aa on (aa.anken_cd = t.anken_cd)
where 1 = 1
and ((t.anken_cd = ':anken_cd') or ('' = ':anken_cd'))
and ((t.anken_kbn = ':anken_kbn') or ('' = ':anken_kbn'))
and (('' = ':syori_ymd_fr') or (t.start_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (t.start_ymd <= ':syori_ymd_to'))
--and (('' = ':tan_cd') or (t.anken_cd in (select anken_cd from v_tan_anken_koutei where tan_cd = ':tan_cd')))
and (('' = ':tan_cd') or (t.anken_cd in (select anken_cd from t_anken_tan where tan_cd = ':tan_cd')))
and (('1' = ':status_jucyu' and COALESCE(t.status,0) in (0,2) and t.jucyu_ymd != '')
  or ('1' = ':status_mi' and COALESCE(t.status,0) in (0,2) and COALESCE(t.jucyu_ymd,'') = '')
  or ('1' = ':status_end' and t.status = 1 and COALESCE(t.cancel_ymd,'') = '')
  or ('1' = ':status_stop' and t.status = 3)
  or ('1' = ':status_cancel' and t.status = 1 and t.cancel_ymd != '')
)
and ((t.sedai_cd = ':sedai_cd') or ('' = ':sedai_cd'))
order by (case when(t.jucyu_ymd != '')then(0)else(1)end), t.end_ymd, t.anken_cd, r.val_id

[getTanKindList]
select
  (case when(koumoku4 != '')then(koumoku4)else(han_cd)end) as tan_kind
, han_name as tan_kind_nm
from m_hanyo
where mst_kbn = '24'
and del_flg = 0
order by tan_kind
limit 8

[getKouteiFields]
select v.koutei_cd, v.koutei_nm, v.koutei_filter as kousyu_cd
from v_m_koutei v, m_hanyo h1
where h1.mst_kbn = '90'
and h1.han_cd = 'sedai_cd'
and h1.koumoku1 = ':sedai_cd'
union all
select v.koutei_cd, v.koutei_nm, v.koutei_filter as kousyu_cd
from t_anken_m v
where v.anken_cd = (
select min(anken_cd) from t_anken where sedai_cd = ':sedai_cd'
)
and ':sedai_cd' != (select h1.koumoku1 from m_hanyo h1 where h1.mst_kbn = '90' and h1.han_cd = 'sedai_cd')
order by koutei_cd

[getKouteiValue1]
select koutei_cd, to_char(yotei_stt_time,'yyyy/mm/dd') as val
from t_yplan_anken y
inner join t_yplan_anken_m m on (m.yplan_anken_cd = y.yplan_anken_cd)
where y.anken_cd = ':w_anken_cd'
order by koutei_no

[getKouteiValue2]
select koutei_cd, to_char(yotei_stt_time,'yyyy/mm/dd') as val
from t_anken_m
where anken_cd = ':w_anken_cd'
order by koutei_no

[getKouteiValue3]
select am.koutei_cd
, EXTRACT(DAY FROM ym.yotei_stt_time - am.yotei_stt_time) as val
from t_anken_m am
left join t_yplan_anken y on (y.anken_cd = ':w_anken_cd')
left join t_yplan_anken_m ym on (ym.yplan_anken_cd = y.yplan_anken_cd and ym.koutei_no = am.koutei_no)
where am.anken_cd = ':w_anken_cd'
order by am.koutei_no

[getKouteiValue4]
select koutei_cd, to_char(stt_time,'yyyy/mm/dd') as val, status
from t_anken_m
where anken_cd = ':w_anken_cd'
order by koutei_no

[getKouteiValue5]
select koutei_cd
, EXTRACT(DAY FROM yotei_stt_time - stt_time) as val
from t_anken_m
where anken_cd = ':w_anken_cd'
order by koutei_no

[getKouteiValue6]
select m1.koutei_cd
, (case when(m2.next_kikan is not null)then(m2.next_kikan - (EXTRACT(DAY FROM m1.stt_time - m2.stt_time)))else(0)end) as val
from t_anken_m m1
left join t_anken_m m2 on (m2.anken_cd = m1.anken_cd and m2.koutei_no = m1.koutei_no - 1)
where m1.anken_cd = ':w_anken_cd'
order by m1.koutei_no

[OLD_getKouteiValue6]
select m1.koutei_cd
--, kouki - (EXTRACT(DAY FROM end_time - stt_time)) as val
, (case when(m2.stt_time is not null)then(m1.next_kikan - (EXTRACT(DAY FROM m2.stt_time - m1.stt_time)))else(0)end) as val
from t_anken_m m1
left join t_anken_m m2 on (m2.anken_cd = m1.anken_cd and m2.koutei_no = m1.koutei_no + 1)
where m1.anken_cd = ':w_anken_cd'
order by m1.koutei_no


[refreshSedaiCodeList]
delete from m_hanyo where mst_kbn = '57' and 1 = 0;
insert into m_hanyo (
  mst_kbn
, han_cd
, han_name
, sort_key
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select distinct 
  '57'
, w.sedai_cd
, w.sedai_cd
, w.sedai_cd
, 0
, ':login_id'
, now()
, ':login_id'
, now()
from (
select distinct sedai_cd from t_anken
union all
select koumoku1 as sedai_cd from m_hanyo where mst_kbn = '90' and han_cd = 'sedai_cd'
) w
where 1 = 0
;




[deleteRowWork]
delete from w_basv3020_row where s_id = :s_id

[insertRowWork]
insert into w_basv3020_row (s_id, row_kind, del_flg, cre_id, cre_time, upd_id, upd_time)
values (:s_id, :row_kind, 0, ':login_id', now(), ':login_id', now())