[load_grdList]
SELECT
 chk.checkbox
, m.sir_cd
, m.sir_nm
, m.sir_rnm
, m.sir_kana
, m.zip_no
, COALESCE(addr1,'') || COALESCE(addr2,'') || COALESCE(addr3,'') || COALESCE(addr4,'') AS addr1
, m.tel_no
, m.fax_no
, m.mob_no
, m.tori_kbn
, h1.han_name as tori_kbn_nm
, m.biko1
, m.biko2
, h2.han_name as cat_nm
, case (m.shime_dd::varchar)
	when '99' then '末日'
	else m.shime_dd::varchar || '日'
	end as shime_dd
, COALESCE(h3.han_name,'')||(case when(m.pay_d = 99)then('末日')else(cast(m.pay_d as varchar)||'日')end) as pay_md
, h29a.han_name as kaisyu_cond
, h29b.han_name as kaisyu_cond2
, m.kaisyu_site
, m.kaisyu_site2
, m.tegata_ratio
--#20420 2021.08.24 MOD STT
, (case when(h99a.han_name = '1')then(m.sir_cd)else(coalesce(m.sir_kana, ''))end) as order_value
--#20420 2021.08.24 MOD END
, ta.tan_nm
, m.cash_limit_gaku
, m.zei_calc_kbn
, (case when(m.zei_calc_kbn = '1')then('一括')else('')end) as zei_calc_kbn_nm
, m.kazei_kind
, hbo.han_name as kazei_kind_nm
, m.reg_no
FROM m_sir m
left join m_tanto ta on (ta.tan_cd = m.tan_cd)
LEFT JOIN m_hanyo h1 on (h1.mst_kbn = '12' and h1.han_cd = m.tori_kbn)
LEFT JOIN m_hanyo h2 on (h2.mst_kbn = '45' and h2.han_cd = m.cat_cd)
LEFT JOIN m_hanyo h3 on (h3.mst_kbn = '10' and h3.han_cd = cast(m.pay_m as varchar))
LEFT JOIN m_hanyo h29a on (h29a.mst_kbn = '29' and h29a.han_cd = m.kaisyu_cond)
LEFT JOIN m_hanyo h29b on (h29b.mst_kbn = '29' and h29b.han_cd = m.kaisyu_cond2)
LEFT JOIN m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'orcd-v8030')
LEFT JOIN m_hanyo hbo on (hbo.mst_kbn = 'BO' and hbo.han_cd = m.kazei_kind)
LEFT JOIN (select max(s_id), sir_cd, 1 as checkbox from w_basv8030_chk where upd_id = ':login_id' group by sir_cd) chk on (chk.sir_cd = m.sir_cd)
WHERE 1 = 1
AND m.del_flg = 0
AND ((m.sir_cd = ':sir_cd') or (':sir_cd' = ''))
--AND ((m.sir_nm LIKE '%:sir_nm%') or (m.sir_kana LIKE '%:sir_nm%') or (':sir_nm' = ''))
--#14318 2020.10.09 MOD STT
--AND ((fnc_translate_case(m.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (fnc_translate_case(m.sir_kana) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (':sir_nm' = ''))
AND ((m.search_nm LIKE '%'||':search_sir_nm'||'%') or (m.search_kana LIKE '%'||':search_sir_nm'||'%') or (':sir_nm' = ''))
--#14318 2020.10.09 MOD END
AND ((COALESCE(addr1,'') || COALESCE(addr2,'') || COALESCE(addr3,'') || COALESCE(addr4,'')  LIKE '%:addr1%') or (':addr1' = ''))
--AND ((m.tel_no LIKE '%:tel_no%' or m.mob_no LIKE '%:tel_no%') or (':tel_no' = ''))
--#14318 2020.10.09 MOD STT
--AND ((fnc_translate_case(m.tel_no) LIKE '%'||fnc_translate_case(':tel_no')||'%' or fnc_translate_case(m.mob_no) LIKE '%'||fnc_translate_case(':tel_no')||'%') or (':tel_no' = ''))
AND ((fnc_translate_case(m.tel_no) LIKE '%'||':search_tel_no'||'%' or fnc_translate_case(m.mob_no) LIKE '%'||':search_tel_no'||'%') or (':tel_no' = ''))
--#14318 2020.10.09 MOD END
AND ((m.tori_kbn = ':tori_kbn') or (':tori_kbn' = ''))
AND ((m.cat_cd = ':cat_cd') or (':cat_cd' = ''))
--AND ((fnc_get_kana_group(m.sir_kana) = ':kana' or '' = ':kana')) --50音検索ないときはkana = ''
AND ((m.kana_group = ':kana' or '' = ':kana'))
AND ((m.search_nm like ':sir_e_initial%' OR '' = ':sir_e_initial'))
AND ((m.eig_cd = ':eig_cd') or (':eig_cd' = ''))
AND ((m.shime_dd::varchar = ':shime_dd') or (':shime_dd' = ''))
AND ((m.tan_cd = ':tan_cd') or (':tan_cd' = ''))
:rdo_rpt_only
:rdo_rpt_apart
:rdo_rpt_cond_last
:rdo_rpt_cond_hacyu
--#20420 2021.08.24 MOD STT
--order by coalesce(m.sir_kana, ''), m.sir_cd
order by order_value, sir_cd
--#20420 2021.08.24 MOD END

[getTranslateCaseCond]
select fnc_translate_case(':sir_nm') as search_sir_nm, fnc_translate_case(':tel_no') as search_tel_no

[deleteRecord]
delete from m_sir
where sir_cd = ':sir_cd'


[insertRecord]
insert into m_sir
( 
  sir_cd
, sir_nm
, zip_no
, addr1
, addr2
, tel_no
, cre_time
, upd_time
--#28602 2023.06.22 MOD STT
--, tan_cd
--, rank
--#28602 2023.06.22 MOD END
)
values
(
--#28602 2023.06.22 MOD STT
--  ':tok_cd'			--tok_cd
--, ':tok_nm'			--tok_nm
  ':sir_cd'			--tok_cd
, ':sir_nm'			--tok_nm
--#28602 2023.06.22 MOD END
, ':zip_no'			--zip_no
, ':addr1'			--addr1
, ':addr2'			--addr2
, ':tel_no'			--tel_no
, 'now()'			--cre_time
, 'now()'			--upd_time
--#28602 2023.06.22 MOD STT
--, ':tan_cd'			--tan_cd
--, ':rank'			--rank
--#28602 2023.06.22 MOD END
);

[deleteSirWork]
delete from w_basv8030_chk where upd_id = ':login_id' and sir_cd = ':sir_cd';

[insertWork]
insert into w_basv8030_chk (s_id, sir_cd, del_flg, cre_id, cre_time, upd_id, upd_time)
 values (:s_id, ':sir_cd', 0, ':login_id', now(), ':login_id', now());

[deleteTantoWork]
delete from w_basv8030_chk where upd_id = ':login_id'
;

[deleteReportWork]
delete from w_basv8030_rep where s_id = :s_id
;

[insertReportWork]
insert into w_basv8030_rep(
	s_id
	, sir_cd
	, opt_cd
	, opt_time
	, del_flg
	, cre_id
	, cre_time
	, upd_id, 
	upd_time
	)
select 
	:s_id
		, w.sir_cd 
		, ':w_opt_cd'
		, now()
		, 0
		, 'login_id'
		, now()
		, 'login_id'
		, now()
from w_basv8030_chk w
where w.upd_id = ':login_id' 
group by sir_cd
;

[updateMSirOptFlg]
update m_sir set
		upd_id = ':login_id'
		, upd_time = now()
		:opt_flg_s
from w_basv8030_rep
where 1=1
and m_sir.sir_cd = w_basv8030_rep.sir_cd
and w_basv8030_rep.s_id = :s_id
;