[deleteTotalWork]
delete from w_basr3040_total
where s_id = :s_id

[insertTotalWork]
insert into w_basr3040_total (
  s_id
, rec_type
, group_cd
, koutei_cd
, anken_su
, yplan_kouki
, yotei_kouki
, jisek_kouki
, rate1
, rate2
, yplan_kouki_kei
, yotei_kouki_kei
, jisek_kouki_kei
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  :s_id
, :w_rec_type
, ':w_group_cd'
, ':w_koutei_cd'
, :w_anken_su
, :w_yplan_kouki
, :w_yotei_kouki
, :w_jisek_kouki
, :w_rate1
, :w_rate2
, :w_yplan_kouki_kei
, :w_yotei_kouki_kei
, :w_jisek_kouki_kei
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
)

[getType1]
select v.han_name as col_name
, t.anken_cnt
, t.kouki0
, t.kouki1
, t.kouki2
, t.rate1
, t.rate2
, v.koumoku2 as col_cd
, w.yplan_kouki
, w.yotei_kouki
, w.jisek_kouki
, w.yplan_kouki_kei
, w.yotei_kouki_kei
, w.jisek_kouki_kei
from m_hanyo v
left join (
	select
	  w2.group_cd
	, w2.anken_cnt
	, w2.kouki0
	, w2.kouki1
	, w2.kouki2
	, 100 as rate1
	, (case when(w2.kouki1 = 0)then(100)else(round(cast(w2.kouki2 * 100 as int) / cast(w2.kouki1 as int), 0))end) as rate2
	from (
		select
		  w1.group_cd
--		, count(*) as anken_cnt
		, sum(w1.anken_cnt) as anken_cnt
--		, round(avg(w1.kouki0)) as kouki0
--		, round(avg(w1.kouki1)) as kouki1
--		, round(avg(w1.kouki2)) as kouki2
		, round(sum(w1.kouki0) / sum(w1.anken_cnt)) as kouki0
		, round(sum(w1.kouki1) / sum(w1.anken_cnt)) as kouki1
		, round(sum(w1.kouki2) / sum(w1.anken_cnt)) as kouki2
		from (
			select
			  m1.anken_cd
			, 1 as anken_cnt
			, h1.koumoku10 as group_cd
--			, sum(m2.next_kikan_std) as kouki0
			, sum(cast(h2.koumoku3 as int)) as kouki0
--			, sum(m2.next_kikan) as kouki1
			, sum(EXTRACT(DAY FROM m1.yotei_stt_time - m2.yotei_stt_time)) as kouki1
			, sum(EXTRACT(DAY FROM m1.stt_time - m2.stt_time)) as kouki2
			from t_anken_m m1
			inner join t_anken ah on (ah.anken_cd = m1.anken_cd and ah.sedai_cd = ':sedai_cd' and ah.status = 1)
			inner join m_hanyo h1 on (h1.mst_kbn = '71' and h1.han_cd = ':sedai_key'||'.'||m1.koutei_cd)
			inner join t_anken_m m2 on (m2.anken_cd = m1.anken_cd and m2.koutei_no = m1.koutei_no - 1)
			inner join m_hanyo h2 on (h2.mst_kbn = '71' and h2.han_cd = ':sedai_key'||'.'||m2.koutei_cd)
			left join m_hanyo h9 on (h9.mst_kbn = '58' and h9.han_cd = ah.anken_kbn)
			where 1 = 1
			and COALESCE(ah.non_analyze_flg,0) = 0
--TODO
--and ah.anken_cd='216053'
			and ah.jucyu_ymd != ''
			and COALESCE(ah.cancel_ymd,'') = ''
--2018.12.07 #6609 MOD STT
--			and COALESCE(h9.koumoku3,'') = ''
			and (('' = ':anken_kbn') or (ah.anken_kbn = ':anken_kbn'))
--2018.12.07 #6609 MOD END
			and (('' = ':tan_cd') or (ah.anken_cd in (select anken_cd from t_anken_tan where tan_cd = ':tan_cd')))
			and (('' = ':op_cd') or (ah.anken_cd in (select anken_cd from t_anken_op where op_cd = ':op_cd')))
			group by 
			  m1.anken_cd
			, h1.koumoku10
		) w1
		group by w1.group_cd
	) w2
) t on (t.group_cd = v.koumoku2)
left join w_basr3040_total w on (w.s_id = :s_id and w.rec_type = 1 and w.group_cd = v.koumoku2)
where v.mst_kbn = '63'
and v.koumoku1 = ':sedai_key'
order by han_cd

[getType1Detail]
select v.han_name as col_name
, t.anken_cnt
, t.kouki0
, t.kouki1
, t.kouki2
, t.rate1
, t.rate2
, v.han_cd as col_cd
, w.yplan_kouki
, w.yotei_kouki
, w.jisek_kouki
, w.yplan_kouki_kei
, w.yotei_kouki_kei
, w.jisek_kouki_kei
from m_hanyo v
left join (
	select
	  w2.group_cd
	, w2.anken_cnt
	, w2.kouki0
	, w2.kouki1
	, w2.kouki2
	, 100 as rate1
--	, (case when(w2.kouki1 = 0)then(100)else(round(cast(w2.kouki2 * 100 as int) / w2.kouki1, 0))end) as rate2
	, (case when(w2.kouki1 = 0)then(100)else(round(cast(w2.kouki2 * 100 as int) / cast(w2.kouki1 as int), 0))end) as rate2
	from (
		select
		  w1.group_cd
		, count(*) as anken_cnt
		, round(avg(w1.kouki0)) as kouki0
		, round(avg(w1.kouki1)) as kouki1
		, round(avg(w1.kouki2)) as kouki2
		from (
			select
			  m1.anken_cd
			, m1.koutei_cd as group_cd
--			, sum(m2.next_kikan_std) as kouki0
			, sum(cast(h2.koumoku3 as int)) as kouki0
--			, sum(m2.next_kikan) as kouki1
			, sum(EXTRACT(DAY FROM m1.yotei_stt_time - m2.yotei_stt_time)) as kouki1
			, sum(EXTRACT(DAY FROM m1.stt_time - m2.stt_time)) as kouki2
			from t_anken_m m1
			inner join t_anken ah on (ah.anken_cd = m1.anken_cd and ah.sedai_cd = ':sedai_cd' and ah.status = 1)
			inner join m_hanyo h1 on (h1.mst_kbn = '71' and h1.han_cd = ':sedai_key'||'.'||m1.koutei_cd)
			inner join t_anken_m m2 on (m2.anken_cd = m1.anken_cd and m2.koutei_no = m1.koutei_no - 1)
			inner join m_hanyo h2 on (h2.mst_kbn = '71' and h2.han_cd = ':sedai_key'||'.'||m2.koutei_cd)
			left join m_hanyo h9 on (h9.mst_kbn = '58' and h9.han_cd = ah.anken_kbn)
			where 1 = 1
			and COALESCE(ah.non_analyze_flg,0) = 0
--TODO
--and ah.anken_cd='216053'
			and ah.jucyu_ymd != ''
			and COALESCE(ah.cancel_ymd,'') = ''
--2018.12.07 #6609 MOD STT
--			and COALESCE(h9.koumoku3,'') = ''
			and (('' = ':anken_kbn') or (ah.anken_kbn = ':anken_kbn'))
--2018.12.07 #6609 MOD END
			and (('' = ':daiku_tan_cd') or (ah.anken_cd in (select anken_cd from t_anken_tan where tan_cd = ':daiku_tan_cd')))
			and (('' != ':daiku_tan_cd') or (h1.koumoku10 = ':col_cd'))
			--TODO
--			and (('' = ':daiku_tan_cd') or (h1.koumoku10 in ('08','09','10')))
			and (('' = ':daiku_tan_cd') or (h1.koumoku7 != ''))
			group by 
			  m1.anken_cd
			, m1.koutei_cd
		) w1
		group by w1.group_cd
	) w2
) t on (':sedai_key'||'.'||t.group_cd = v.han_cd)
left join w_basr3040_total w on (w.s_id = :s_id and w.rec_type = 2 and w.koutei_cd = v.han_cd)
where v.mst_kbn = '71'
and v.han_cd like ':sedai_key%'
and (('' != ':daiku_tan_cd') or (v.koumoku10 = ':col_cd'))
--TODO
--and (('' = ':daiku_tan_cd') or (v.koumoku10 in ('08','09','10')))
and (('' = ':daiku_tan_cd') or (v.koumoku7 != ''))
order by han_cd

[getType2TanList]
select 
  at.tan_cd
, mt.tan_nm
, count(*) as anken_cnt
from t_anken ah
inner join t_anken_tan at on (at.anken_cd = ah.anken_cd)
inner join m_hanyo h1 on (h1.mst_kbn = '24' and h1.han_cd = at.tan_kind and COALESCE(h1.koumoku2,'') != '1')
left join m_hanyo h9 on (h9.mst_kbn = '58' and h9.han_cd = ah.anken_kbn)
left join m_tanto mt on (mt.tan_cd = at.tan_cd)
where ah.sedai_cd = ':sedai_cd'
and ah.status = 1
and COALESCE(ah.non_analyze_flg,0) = 0
and ah.jucyu_ymd != ''
and COALESCE(ah.cancel_ymd,'') = ''
--2018.12.07 #6609 MOD STT
--and COALESCE(h9.koumoku3,'') = ''
and (('' = ':anken_kbn') or (ah.anken_kbn = ':anken_kbn'))
--2018.12.07 #6609 MOD END

--TODO
--and at.tan_cd = '210051'
group by
  at.tan_cd
, mt.tan_nm
order by tan_cd

[getType3TanList]
select 
  at.tan_cd
, mt.tan_nm
, count(*) as anken_cnt
from t_anken ah
inner join t_anken_tan at on (at.anken_cd = ah.anken_cd)
inner join m_hanyo h1 on (h1.mst_kbn = '24' and h1.han_cd = at.tan_kind and COALESCE(h1.koumoku2,'') = '1')
left join m_hanyo h9 on (h9.mst_kbn = '58' and h9.han_cd = ah.anken_kbn)
left join m_tanto mt on (mt.tan_cd = at.tan_cd)
where ah.sedai_cd = ':sedai_cd'
and ah.status = 1
and COALESCE(ah.non_analyze_flg,0) = 0
and ah.jucyu_ymd != ''
and COALESCE(ah.cancel_ymd,'') = ''
--2018.12.07 #6609 MOD STT
--and COALESCE(h9.koumoku3,'') = ''
and (('' = ':anken_kbn') or (ah.anken_kbn = ':anken_kbn'))
--2018.12.07 #6609 MOD END

--TODO
--and at.tan_cd = 'D01'
group by
  at.tan_cd
, mt.tan_nm
order by tan_cd

[getType4OptionList]
select 
  op.op_cd
, h1.han_name as op_nm
, count(*) as anken_cnt
from t_anken ah
inner join t_anken_op op on (op.anken_cd = ah.anken_cd)
inner join m_hanyo h1 on (h1.mst_kbn = '22' and h1.han_cd = op.op_cd)
left join m_hanyo h9 on (h9.mst_kbn = '58' and h9.han_cd = ah.anken_kbn)
where ah.sedai_cd = ':sedai_cd'
and ah.status = 1
and COALESCE(ah.non_analyze_flg,0) = 0
and ah.jucyu_ymd != ''
and COALESCE(ah.cancel_ymd,'') = ''
--2018.12.07 #6609 MOD STT
--and COALESCE(h9.koumoku3,'') = ''
and (('' = ':anken_kbn') or (ah.anken_kbn = ':anken_kbn'))
--2018.12.07 #6609 MOD END
group by
  op.op_cd
, h1.han_name
order by op_cd

[deleteWork]
delete from w_basr3040
where s_id = :s_id

[insertWork]
insert into w_basr3040 (
  s_id
, tan_cd
, anken_su
, yplan_kouki
, yotei_kouki
, jisek_kouki
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  :s_id
, ':daiku_tan_cd'
, :anken_su
, :yplan_kouki
, :yotei_kouki
, :jisek_kouki
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
)

[getTanRanking]
select 
  w.tan_cd
, mt.tan_nm
, w.anken_su
, w.yplan_kouki
, w.yotei_kouki
, w.jisek_kouki
from w_basr3040 w
left join m_tanto mt on (mt.tan_cd = w.tan_cd)
where s_id = :s_id
--and w.jisek_kouki > 0
order by jisek_kouki, anken_su desc, tan_cd
limit 20