[insertWork]
insert into w_basv6010 (
	  s_id
	, recv_no
	, biko1
	, tan_cd
	, cre_time_s
	, tan_nm
	, log_kind_s
	, fax_no
	, tor_kbn
	, tor_kbn_nm
	, tor_cd
	, tor_nm
	, tor_tan_cd
	, tor_tan_nm
	, rs_kbn
	, rs_kbn_nm
	, rs_hoho
	, rs_sts
	, rs_sts_nm
	, file_name
	, jucyu_no
	, uri_no
	, ext_cat1_cd
	, ext_cat1_nm
	, edit_tan_cd
	, edit_tan_nm
)
select
	  :s_id
	, w.recv_no
	, w.biko1
	, w.tan_cd
	, w.cre_time_s
	, w.tan_nm
	, w.log_kind_s
	, w.fax_no
	, w.tor_kbn
	, w.tor_kbn_nm
	, w.tor_cd
	, w.tor_nm
	, w.tor_tan_cd
	, w.tor_tan_nm
	, w.rs_kbn
	, w.rs_kbn_nm
	, w.rs_hoho
	, w.rs_sts
	, w.rs_sts_nm
	, w.file_name
	, w.jucyu_no
	, w.uri_no
	, w.ext_cat1_cd
	, w.ext_cat1_nm
	, w.edit_tan_cd
	, w.edit_tan_nm
from fnc_query_rs_list(json_build_object(
  'trash_mode', ':trash_mode'
, 'recv_mode', ':recv_mode'
, 'send_mode', ':send_mode'
, 'recv_no', ':recv_no'
, 'syori_ymd_fr', ':syori_ymd_fr'
, 'syori_ymd_to', ':syori_ymd_to'
, 'recv_stsex', ':recv_stsex'
, 'sts_mi', ':sts_mi'
, 'sts_sumi', ':sts_sumi'
, 'recv_sts_cond', ':recv_sts_cond'
, 'recv_sts', ':recv_sts'
, 'ext_cat1_cd', ':ext_cat1_cd'
, 'tok_cd', ':tok_cd'
, 'tok_nm', ':tok_nm'
, 'sir_cd', ':sir_cd'
, 'sir_nm', ':sir_nm'
, 'kind_s', ':kind_s'
, 'tor_tan_cd', ':tor_tan_cd'
)) w
where 1 = 1
order by w.recv_no desc
limit 100
[insertWork_old]
insert into w_basv6010 (
	  s_id
	, recv_no
	, biko1
	, tan_cd
	, cre_time_s
	, tan_nm
	, log_kind_s
	, fax_no
	, tor_kbn
	, tor_kbn_nm
	, tor_cd
	, tor_nm
	, tor_tan_cd
	, tor_tan_nm
	, rs_kbn
	, rs_kbn_nm
	, rs_hoho
	, rs_sts
	, rs_sts_nm
	, file_name
	, jucyu_no
	, uri_no
	, ext_cat1_cd
	, ext_cat1_nm
	, edit_tan_cd
)
select
	  :s_id
	, w.recv_no
	, w.biko1
	, w.tan_cd
	, w.cre_time_s
	, w.tan_nm
	, w.log_kind_s
	, w.fax_no
	, w.tor_kbn
	, w.tor_kbn_nm
	, w.tor_cd
	, w.tor_nm
	, w.tor_tan_cd
	, w.tor_tan_nm
	, w.rs_kbn
	, w.rs_kbn_nm
	, w.rs_hoho
	, w.rs_sts
	, w.rs_sts_nm
	, w.file_name
	, w.jucyu_no
	, w.uri_no
	, w.ext_cat1_cd
	, w.ext_cat1_nm
	, w.edit_tan_cd
from (
--受信
select 
	  sys.recv_no
	, (case when(h99a.han_name = '1')then('')else(sys.recv_content)end) as biko1
	, sys.upd_id as tan_cd
	, to_char(sys.cre_time,(case when(h99b.han_name = '1')then('yyyy/mm/dd hh24:mi')else('yyyy/mm/dd hh24:mi:ss')end)) as cre_time_s
	, mt.tan_nm
	, sys.recv_title as log_kind_s
--	, null as fax_no
	, sys.email as fax_no
	, (case when(sys.tok_cd != '')then('mtok')when(sys.sir_cd != '')then('msir')else('')end) as tor_kbn
	, (case when(sys.tok_cd != '')then('得意先')when(sys.sir_cd != '')then('仕入先')else('')end) as tor_kbn_nm
	, (case when(sys.tok_cd != '')then(sys.tok_cd)when(sys.sir_cd != '')then(sys.sir_cd)else('')end) as tor_cd
	, (case when(sys.tok_cd != '')then(mtok.tok_nm)when(sys.sir_cd != '')then(msir.sir_nm)else('')end) as tor_nm
	, (case when(sys.tok_cd != '')then(mtok.tan_cd)when(sys.sir_cd != '')then(msir.tan_cd)else('')end) as tor_tan_cd
	, (case when(sys.tok_cd != '')then(mtt.tan_nm)when(sys.sir_cd != '')then(mts.tan_nm)else('')end) as tor_tan_nm
	, 'R' as rs_kbn
	, '受信' as rs_kbn_nm
--#32511 2024.05.31 MOD STT
--	, (case(sys.recv_hoho)when(1)then('Fax')when(2)then('eMail')else(sys.recv_hoho::varchar)end) as rs_hoho
	, (case when(hbw.han_name != '')then(hbw.han_name)when(sys.recv_hoho = 1)then('Fax')when(sys.recv_hoho = 2)then('eMail')else(sys.recv_hoho::varchar)end) as rs_hoho
--#32511 2024.05.31 MOD END
--	, null as rs_sts
--	, null as rs_sts_nm
	, sys.recv_sts as rs_sts
--#32512 2024.06.02 MOD STT
--	, (case(sys.recv_sts)when(0)then('待ち')when(1)then('完')else(sys.recv_sts::varchar)end) as rs_sts_nm
	, (case when(hbx.han_name != '')then(hbx.han_name)when(sys.recv_sts = 0)then('待ち')when(sys.recv_sts = 1)then('完')else(sys.recv_sts::varchar)end) as rs_sts_nm
--#32512 2024.06.02 MOD END
	, (case when(a.file_name != '')then(a.file_name)else(sys.file_name)end) as file_name
	, (case when(w.jucyu_count > 1)then(fnc_get_jucyu_no_list_recv(w.recv_no))else(w.jucyu_no::varchar)end) as jucyu_no
	, (case when(w.uri_count > 1)then(fnc_get_uri_no_list_recv(w.recv_no))else(w.uri_no::varchar)end) as uri_no
	, sys.ext_cat1_cd
	, hby.han_name as ext_cat1_nm
	, sys.edit_tan_cd
from fnc_query_recv_log_key('{
  "trash_mode":":trash_mode"
, "recv_no":":recv_no"
, "syori_ymd_fr":":syori_ymd_fr"
, "syori_ymd_to":":syori_ymd_to"
, "recv_stsex":":recv_stsex"
, "sts_mi":":sts_mi"
, "sts_sumi":":sts_sumi"
, "recv_sts_cond":":recv_sts_cond"
, "recv_sts":":recv_sts"
, "ext_cat1_cd":":ext_cat1_cd"
, "tok_cd":":tok_cd"
, "tok_nm":":tok_nm"
, "sir_cd":":sir_cd"
, "sir_nm":":sir_nm"
, "kind_s":":kind_s"
}') w
inner join t_recv_log sys on (sys.recv_no = w.recv_no)
left join m_tanto mt on (mt.tan_cd = sys.upd_id)
left join m_tokui mtok on (mtok.tok_cd = sys.tok_cd)
left join m_sir msir on (msir.sir_cd = sys.sir_cd)
left join m_tanto mtt on (mtt.tan_cd = mtok.tan_cd)
left join m_tanto mts on (mts.tan_cd = msir.tan_cd)
left join t_recv_atch a on (a.recv_no = sys.recv_no and a.row_no = 0)
left join m_hanyo hbw on (hbw.mst_kbn = 'BW' and hbw.han_cd = sys.recv_hoho::varchar)
left join m_hanyo hbx on (hbx.mst_kbn = 'BX' and hbx.han_cd = sys.recv_sts::varchar)
left join m_hanyo hby on (hby.mst_kbn = 'BY' and hby.han_cd = sys.ext_cat1_cd)
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'nrct-v6010')
left join m_hanyo h99b on (h99b.mst_kbn = '99' and h99b.han_cd = 'sp-v6010-1')
where 1 = 1
--and ((':trash_mode' != '0') or (sys.del_flg = 0))
--and ((':trash_mode' != '1') or (sys.del_flg = 1))
--and (('' = ':syori_ymd_fr') or (to_char(sys.cre_time,'yyyymmdd') >= ':syori_ymd_fr'))
--and (('' = ':syori_ymd_to') or (to_char(sys.cre_time,'yyyymmdd') <= ':syori_ymd_to'))
and ':recv_mode' = '1'
--and (
--       ((':sts_mi' = '1') and (sys.recv_sts in (0)))
--    or ((':sts_sumi' = '1') and (sys.recv_sts in (1)))
--    or (sys.del_flg = 1)
--)
--and :recv_sts_cond_str
--送信
union all
select 
	  sys.send_no as recv_no
	, (case when(sys.send_sts in (8))then(sys.biko1)else(sys.send_content)end) as biko1 --異常のときは備考を出す
	, sys.upd_id as tan_cd
	, to_char(sys.cre_time,'yyyy/mm/dd hh24:mi:ss') as cre_time_s
	, mt.tan_nm
	, sys.send_title as log_kind_s
	, sys.fax_no
	, (case when(sys.tok_cd != '')then('mtok')when(sys.sir_cd != '')then('msir')else('')end) as tor_kbn
	, (case when(sys.tok_cd != '')then('得意先')when(sys.sir_cd != '')then('仕入先')else('')end) as tor_kbn_nm
	, (case when(sys.tok_cd != '')then(sys.tok_cd)when(sys.sir_cd != '')then(sys.sir_cd)else('')end) as tor_cd
	, (case when(sys.tok_cd != '')then(mtok.tok_nm)when(sys.sir_cd != '')then(msir.sir_nm)else('')end) as tor_nm
	, (case when(sys.tok_cd != '')then(mtok.tan_cd)when(sys.sir_cd != '')then(msir.tan_cd)else('')end) as tor_tan_cd
	, (case when(sys.tok_cd != '')then(mtt.tan_nm)when(sys.sir_cd != '')then(mts.tan_nm)else('')end) as tor_tan_nm
	, 'S' as rs_kbn
	, '送信' as rs_kbn_nm
	, (case(sys.send_hoho)when(1)then('Fax')when(2)then('eMail')else(sys.send_hoho::varchar)end) as rs_hoho
	, sys.send_sts as rs_sts
	, (case(sys.send_sts)when(0)then('待ち')when(1)then('完')when(2)then('実行')when(8)then('異常')when(9)then('中止')else(sys.send_sts::varchar)end) as rs_sts_nm
	, sys.file_name
	, null::varchar as jucyu_no
	, null::varchar as uri_no
	, null::varchar as ext_cat1_cd
	, null::varchar as ext_cat1_nm
	, null::varchar as edit_tan_cd
from t_send_log sys
left join m_tanto mt on (mt.tan_cd = sys.upd_id)
left join m_tokui mtok on (mtok.tok_cd = sys.tok_cd)
left join m_sir msir on (msir.sir_cd = sys.sir_cd)
left join m_tanto mtt on (mtt.tan_cd = mtok.tan_cd)
left join m_tanto mts on (mts.tan_cd = msir.tan_cd)
where 1 = 1
and ((':trash_mode' != '0') or (sys.del_flg = 0))
and ((':trash_mode' != '1') or (sys.del_flg = 1))
and (('' = ':syori_ymd_fr') or (to_char(sys.cre_time,'yyyymmdd') >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (to_char(sys.cre_time,'yyyymmdd') <= ':syori_ymd_to'))
and ':send_mode' = '1'
and (
       ((':sts_mi' = '1') and (sys.send_sts in (0,2,8)))
    or ((':sts_sumi' = '1') and (sys.send_sts in (1,9)))
    or (sys.del_flg = 1)
)

) w
where 1 = 1
and (('' = ':tor_tan_cd') or (w.tor_tan_cd = ':tor_tan_cd'))

[load_grdList]
select * from w_basv6010 wk
where wk.s_id = :s_id
--order by cre_time_s desc, rs_kbn, recv_no desc
order by recv_no desc




[commitRecv]
update t_recv_log set
  recv_sts = 1
, upd_id = ':login_id'
, upd_time = now()
where recv_no = :recv_no
and recv_sts in (0)

[rollbackRecv]
update t_recv_log set
  recv_sts = 0
, upd_id = ':login_id'
, upd_time = now()
where recv_no = :recv_no
and recv_sts in (1)

[deleteRecv]
select fnc_delete_recv_log(:recv_no, ':login_id')

[deleteSend]
update t_send_log set
  del_flg = 1
, upd_id = ':login_id'
, upd_time = now()
where send_no = :send_no
and send_sts in (8, 9) --異常、中止のみ

[restoreRecv]
update t_recv_log set
  del_flg = 0
, upd_id = ':login_id'
, upd_time = now()
where recv_no = :recv_no
and del_flg = 1

[restoreSend]
update t_send_log set
  del_flg = 0
, upd_id = ':login_id'
, upd_time = now()
where send_no = :send_no
and del_flg = 1

[retrySend]
update t_send_log set
  send_sts = 0
, upd_id = ':login_id'
, upd_time = now()
where send_no = :send_no
and send_sts in (2, 8, 9) --異常、中止のみ

[cancelSend]
update t_send_log set
  send_sts = 9
, upd_id = ':login_id'
, upd_time = now()
where send_no = :send_no
and send_sts in (0, 8) --待ち、異常のみ

[cleanTrash]
delete from t_recv_log
where del_flg = 1
and cre_time < (now() - cast(':del_day'||'days' as interval))
;
delete from t_send_log
where del_flg = 1
and cre_time < (now() - cast(':del_day'||'days' as interval))
;
--#34007 2025.03.28 MOD STT
delete from t_recv_log_edit t
where not exists (select 1 from t_recv_log m where m.recv_no = recv_no)
;
delete from t_recv_atch t
where not exists (select 1 from t_recv_log m where m.recv_no = recv_no)
;
delete from t_recv_atch_org t
where not exists (select 1 from t_recv_log m where m.recv_no = recv_no)
;
analyze t_recv_log;
analyze t_send_log;
analyze t_recv_log_edit;
analyze t_recv_atch;
analyze t_recv_atch_org;
--#34007 2025.03.28 MOD END

[saveTorRecv]
update t_recv_log set
  tok_cd = ':w_tok_cd'
, sir_cd = ':w_sir_cd'
, upd_id = ':login_id'
, upd_time = now()
where recv_no = :recv_no

[saveTorSend]
update t_send_log set
  tok_cd = ':w_tok_cd'
, sir_cd = ':w_sir_cd'
, upd_id = ':login_id'
, upd_time = now()
where send_no = :send_no

[getRecvAtch]
select 0 as row_no, t.file_name, t.file_image
from t_recv_log t
where t.recv_no = :recv_no
and t.file_image is not null
and t.file_name != ''
union all
select t.row_no, t.file_name, t.file_image
from t_recv_atch t
where t.recv_no = :recv_no
and t.file_image is not null
and t.file_name != ''
order by row_no


[getSendAtch]
select t.file_name, t.file_image
from t_send_log t
where t.send_no = :send_no
and t.file_image is not null

[getJucyu]
select jh.den_no
from t_uri jh
where jh.recv_no = :recv_no
and jh.del_flg = 0
and jh.jucyu_flg = 1
order by jh.den_no limit 1

[load_grdList_sts]
select '' as recv_sts, '全て' as recv_sts_nm, count(*) as cnt_i, 0 as sort_i, '' as sort_key
from t_recv_log t
where t.del_flg = 0
union all
select hbx.han_cd as recv_sts, hbx.han_name as recv_sts_nm, s.cnt_i, 1 as sort_i, hbx.sort_key
from m_hanyo hbx
left join (
select t.recv_sts, count(*) as cnt_i
from t_recv_log t
where t.del_flg = 0
and t.recv_sts is not null
group by t.recv_sts
) s on (s.recv_sts::varchar = hbx.han_cd)
where hbx.mst_kbn = 'BX'
and hbx.del_flg = 0
order by sort_i, sort_key, recv_sts

[load_grdList_ext_cat1]
select '' as ext_cat1_cd, '全て' as ext_cat1_nm, count(*) as cnt_i, 0 as sort_i, '' as sort_key
from t_recv_log t
where t.del_flg = 0
union all
select hby.han_cd as ext_cat1_cd, hby.han_name as ext_cat1_nm, s.cnt_i, 1 as sort_i, hby.sort_key
from m_hanyo hby
left join (
select t.ext_cat1_cd, count(*) as cnt_i
from t_recv_log t
where t.del_flg = 0
and t.ext_cat1_cd != ''
group by t.ext_cat1_cd
) s on (s.ext_cat1_cd = hby.han_cd)
where hby.mst_kbn = 'BY'
and hby.del_flg = 0
order by sort_i, sort_key, ext_cat1_cd

[deleteWork]
delete from w_basv6010 where s_id = :pre_s_id