[existKey]
select 1 from m_hin
where hin_cd = ':hin_cd'

[load_grdList_rireki]
select
  syori_ymd
, tor_cd
, tor_nm
, tanka
, tanka_f2
, den_no
, row_no
, tor_kbn
, nyuko_su
, syuko_su
, tor_kbn_key
from v_hin_rireki
where hin_cd = ':hin_cd'
and ((':ns_cond' != 'n') or (tor_kbn_key in ('01', '02', '03')))
and ((':ns_cond' != 's') or (tor_kbn_key in ('04', '05', '06')))
and (('' = ':syori_ymd_fr') or (syori_ymd >= ':syori_ymd_fr'))
order by syori_ymd desc, tor_kbn desc, den_no desc, row_no
:rireki_limit

[changeHin]
select fnc_update_change_hin(':hin_cd', ':nhin_cd', ':login_id');

[existNewKey]
select 1 from m_hin
where hin_cd = ':nhin_cd'


[mhin_auto]
select coalesce(oya.han_cd, '') as group_cd, oya.koumoku1 as mhin_auto
from m_hanyo ko --on hin.cat_cd = ko.han_cd
inner join m_hanyo oya on ko.koumoku1 = oya.han_cd and oya.mst_kbn = '14'
where ko.mst_kbn = '05' 
and ko.han_cd = ':ocat_cd'
--and ko.koumoku2 = ':ocat_cd'
;

[get_new_cd]
select max(hin.hin_cd) as max_cd
from m_hin hin
inner join m_hanyo ko on ko.koumoku2 = hin.cat_cd and ko.mst_kbn = '05'
--where ko.koumoku2 = ':group_cd'
where ko.koumoku1 = ':group_cd'
and hin_cd ~ '^[0-9]+$'
;

[load_grdList_sir]
select
	hs.hin_cd
	, hs.sir_cd
	, ms.sir_nm
	, hs.row_no
	, hs.sir_hin_cd
	, hs.sir_hin_nm
	, hs.tanka
	, hs.tanka as tanka_f2
	, hs.biko
	, hs.del_flg
	, hs.cre_id
	, hs.cre_time
	, hs.upd_id
	, hs.upd_time
from m_sirhin hs
left join m_sir ms on (ms.sir_cd = hs.sir_cd) 
where hin_cd = ':hin_cd'
order by hs.sir_cd,hs.row_no;

[load_grdList_stock]
select st.hin_cd,
	st.loc_no,
	st.now_stock,
	case when st.loc_no = '0' then '自社' else ms.sir_nm end as sir_nm
from m_stock_loc st
left join m_sir ms on (ms.sir_cd = st.loc_no)
where hin_cd = ':hin_cd'
order by st.hin_cd;

[load_grdList_size]
select w.dhin_cd,w.link_size,siz.hin_nm,siz.hin_cd , case when hin.size_l is null then '0' else '1' end as select_flg --'1' as select_flg
from (select
  ':hin_cd'::text as dhin_cd
, cast(h52.han_name as numeric(6,2)) as link_size
, '' as hin_nm
from m_hanyo h52
where h52.mst_kbn = '52'
and h52.del_flg = 0
) as w 
left join m_hin oya on oya.hin_cd = ':hin_cd' and oya.del_flg = 0
left join (
select ko.dhin_cd,ko.hin_cd,ko.nm_ext as hin_nm,mhs.link_size
from m_hin oya 
left join m_hin ko on oya.hin_cd = ko.dhin_cd --
left join m_hin_size mhs on mhs.hin_cd = ko.hin_cd
where oya.hin_cd = ':hin_cd'
) siz on siz.dhin_cd = w.dhin_cd and w.link_size = siz.link_size
left join (
select mm.hin_cd,mm.dhin_cd,mm.size_l
from m_hin m
left join m_hin mm on (mm.dhin_cd = m.hin_cd)
where m.hin_cd = ':hin_cd'
) hin on hin.dhin_cd = w.dhin_cd and w.link_size = hin.size_l
order by w.link_size
;

[load_grdList_size_old_20200915]
select w.dhin_cd,w.link_size,siz.hin_nm,siz.hin_cd , case when hin.size_l is null then '0' else '1' end as select_flg --'1' as select_flg
from (select
  ':hin_cd'::text as dhin_cd
, cast(h52.han_name as numeric(6,2)) as link_size
, '' as hin_nm
from m_hanyo h52
where h52.mst_kbn = '52'
and h52.del_flg = 0
) as w 
left join m_hin oya on oya.hin_cd = ':hin_cd' and oya.del_flg = 0
left join (
select ko.dhin_cd,ko.hin_cd,ko.hin_nm,mhs.link_size
from m_hin oya 
left join m_hin ko on oya.hin_cd = ko.dhin_cd --
left join m_hin_size mhs on mhs.hin_cd = ko.hin_cd
where oya.hin_cd = ':hin_cd'
) siz on siz.dhin_cd = w.dhin_cd and w.link_size = siz.link_size
left join (
select mm.hin_cd,mm.dhin_cd,mm.size_l
from m_hin m
left join m_hin mm on (mm.dhin_cd = m.hin_cd)
where m.hin_cd = ':hin_cd'
) hin on hin.dhin_cd = w.dhin_cd and w.link_size = hin.size_l
order by w.link_size
;


[load_grdList_size_old]
select w.dhin_cd,w.link_size,siz.hin_nm,siz.hin_cd
from (select
  ':hin_cd'::text as dhin_cd
, cast(h52.han_name as numeric(6,2)) as link_size
, '' as hin_nm
from m_hanyo h52
where h52.mst_kbn = '52'
and h52.del_flg = 0
) as w 
left join m_hin oya on oya.hin_cd = ':hin_cd' and oya.del_flg = 0
left join (
select ko.dhin_cd,ko.hin_cd,ko.hin_nm,mhs.link_size
from m_hin oya 
left join m_hin ko on oya.hin_cd = ko.dhin_cd --
left join m_hin_size mhs on mhs.hin_cd = ko.hin_cd
where oya.hin_cd = ':hin_cd'
) siz on siz.dhin_cd = w.dhin_cd and w.link_size = siz.link_size
order by w.link_size
;

[load_grdList_tanka]
select 
	  thtc.hin_cd
	, thtc.rireki_seq
	, thtc.syori_ymd
	, thtc.tan_cd
	, tan.tan_nm
	, thtc.tanka
	, thtc.stanka
	, thtc.biko1
from t_hin_tanka_chg thtc
left join m_tanto tan on (thtc.tan_cd = tan.tan_cd)
where thtc.hin_cd = ':hin_cd'
order by thtc.rireki_seq desc
;

[set_now_stock]
select
	coalesce(MAX(now_stock),0) as now_stock
from m_stock
where hin_cd = ':hin_cd'
;
[get_hin_atch]
select file_image, file_name
from t_hin_atch
where hin_cd = ':hin_cd'
and row_no = :row_no
;

[getImage]
select file_image from t_hin_atch
where hin_cd = ':hin_cd'
and row_no = :_row_no
;

[getStockInfo]
select --w.hin_cd, 
	max(st.now_stock) as now_stock,max(st.now_ztanka) as now_ztanka,
	coalesce(sum(w.jucyu_su),0) as jucyu_su, 
	coalesce(sum(w.hacyu_su),0) as hacyu_su,
	coalesce(sum(w.jucyu_su),0) - coalesce(sum(w.hacyu_su),0) as suryo
	
from m_hin hin 
left join (
select sum(b.jucyu_su) as jucyu_su, 0 as hacyu_su,b.hin_cd
from t_uri a
inner join t_uri_m b on a.den_no  = b.den_no 
where a.del_flg = 0 and a.jucyu_flg = 1 --受注
and b.hin_cd  = ':hin_cd'
group by b.hin_cd
union all
select 
null as jucyu_su, sum(b.hacyu_su) as hacyu_su,b.hin_cd
from t_sir a
inner join t_sir_m b on a.den_no  = b.den_no 
where a.del_flg = 0 and a.hacyu_flg = 1 --発注
and b.hin_cd  = ':hin_cd'
group by b.hin_cd
) w on hin.hin_cd = w.hin_cd
left join m_stock st on st.hin_cd = hin.hin_cd
where st.hin_cd = ':hin_cd'
group by w.hin_cd
;

[deleteDocRow]
delete from t_hin_atch where hin_cd = ':hin_cd' and row_no = :d_row_no;

[chk_hin_atch]
select 1 from t_hin_atch where hin_cd = ':hin_cd' and row_no = :d_row_no;

[chk_atch_visible]
--select 1 as i_chk_flg from w_hin_atch where dhin_cd = ':hin_cd' limit 1; --#13855 Tbl違う
select 1 as i_chk_flg from t_hin_atch where hin_cd = ':hin_cd' limit 1; 


[deleteSizeHin]
DELETE
FROM m_hin_size a USING m_hin b
WHERE a.hin_cd = b.hin_cd AND b.dhin_cd = ':hin_cd' and b.dhin_cd != b.hin_cd
;
DELETE FROM m_hin WHERE dhin_cd = ':hin_cd' and dhin_cd != hin_cd
;

[insertSizeHin]
insert into m_hin
(hin_cd, hin_nm, hin_rnm, htanka, tanka, stanka, dhin_cd, hin_sts, 
	cat_cd, brand_cd, price_kbn, jan_cd, new_tanka, stop_flg, iri_su, 
	other_id, sir_cd, sir_hin_cd, sir_hin_nm, bante, biko1, biko2, tani, 
	inner_flg, sir_bante, non_stock_flg, color_cd, sethin_id, hacyu_biko, 
	hacyu_irai_su, hacyu_irai_ymd, hacyu_irai_tan_cd, nm_ext, pattern3, 
	pattern4, set_iri_su, zei_kbn, szei_kbn, hacyu_lot, uri_hin_flg, 
	del_flg, cre_id, cre_time, upd_id, upd_time, size_l, size_w, size_h, 
	weight, elong1, elong2, hosoku1, hosoku2, hosoku3, hosoku4, sort_key)
select ':chin_cd', ':chin_nm', ':chin_nm', htanka, tanka, stanka, ':hin_cd', 
	hin_sts, cat_cd, brand_cd, null, null, new_tanka, 0, 
	null, null, sir_cd, null, null, null, null, null, 
	tani, 0, null, non_stock_flg, null, null, 
	null, null, null, null,  ':chin_nm_ex', 
	null, null, null, zei_kbn, szei_kbn, null, 
	uri_hin_flg, del_flg, ':login_id', now(), ':login_id', now(), null, 
	null, null, null, null, null, null, null, null, 
	null, ':chin_cd'
from m_hin a
WHERE hin_cd = ':hin_cd'
AND NOT EXISTS(SELECT 1 FROM m_hin b WHERE b.hin_cd = ':chin_cd');
;

[insertHinSize]
insert into m_hin_size
(hin_cd, link_size, del_flg, cre_id, cre_time, upd_id, upd_time)
select ':chin_cd', :link_size, 0,':login_id', now(), ':login_id', now()
WHERE NOT EXISTS(SELECT 1 FROM m_hin_size b WHERE b.hin_cd = ':chin_cd' AND link_size = :link_size)
;

[insertSize]
insert into m_hin
(hin_cd, hin_nm, hin_rnm, htanka, tanka, stanka, dhin_cd, hin_sts, 
	cat_cd, brand_cd, price_kbn, jan_cd, new_tanka, stop_flg, iri_su, 
	other_id, sir_cd, sir_hin_cd, sir_hin_nm, bante, biko1, biko2, tani, 
	inner_flg, sir_bante, non_stock_flg, color_cd, sethin_id, hacyu_biko, 
	hacyu_irai_su, hacyu_irai_ymd, hacyu_irai_tan_cd, nm_ext, pattern3, 
	pattern4, set_iri_su, zei_kbn, szei_kbn, hacyu_lot, uri_hin_flg, 
	del_flg, cre_id, cre_time, upd_id, upd_time, size_l, size_w, size_h, 
	weight, elong1, elong2, hosoku1, hosoku2, hosoku3, hosoku4, sort_key)
select ':chin_cd', ':chin_nm', ':chin_nm', htanka, tanka, stanka, ':hin_cd', 
	hin_sts, cat_cd, brand_cd, null, null, new_tanka, 0, 
	null, null, sir_cd, null, null, null, null, null, 
	tani, 0, null, non_stock_flg, null, null, 
	null, null, null, null, null, 
	null, null, null, zei_kbn, szei_kbn, null, 
	uri_hin_flg, del_flg, ':login_id', now(), ':login_id', now(), ':size_l', 
	null, null, null, null, null, null, null, null, 
	null, ':chin_cd'
from m_hin a
WHERE hin_cd = ':hin_cd'
AND NOT EXISTS(SELECT 1 FROM m_hin b WHERE b.hin_cd = ':chin_cd');
;

[checkDelHinMst]
select 1 from m_hin where hin_cd = ':chk_hin_cd' and coalesce(dhin_cd,'') != ':hin_cd'
;

[set_dhinCd]
update m_hin set dhin_cd = hin_cd where hin_cd = ':hin_cd';

[reset_dhinCd]
update m_hin set dhin_cd = null where hin_cd =':hin_cd';

[newTankaRireki]
insert into t_hin_tanka_chg (
	hin_cd
	, rireki_seq
	, syori_ymd
	, tan_cd
	, tanka
	, stanka
	, biko1
	, del_flg
	, cre_id
	, cre_time
	, upd_id
	, upd_time
)
values(
	':hin_cd'
	--, (select max(rireki_seq)+1 from t_hin_tanka_chg where hin_cd = ':hin_cd' group by hin_cd)
	, '1'
	, to_char(now(),'yyyymmdd')
	, ':login_id'
	, :tanka
	, :stanka
	, '新規登録'
	, '0'
	, ':login_id'
	, now()
	, ':login_id'
	, now()
)
;

[insertTankaRireki]
insert into t_hin_tanka_chg (
	hin_cd
	, rireki_seq
	, syori_ymd
	, tan_cd
	, tanka
	, stanka
	, biko1
	, del_flg
	, cre_id
	, cre_time
	, upd_id
	, upd_time
)
values(
	':hin_cd'
	, coalesce((select max(rireki_seq)+1 from t_hin_tanka_chg where hin_cd = ':hin_cd' group by hin_cd),1)
	, ':t_syori_ymd'
	, ':login_id'
--#22575 2022.01.18 MOD STT
--	, ':tanka'
--	, ':stanka'
	, :tanka
	, :stanka
--#22575 2022.01.18 MOD END
	, ':t_biko1'
	, '0'
	, ':login_id'
	, now()
	, ':login_id'
	, now()
)
;

[getMustHinCd]
select
	hin_cd as m_hin_cd
from m_hin
where hin_cd = ':hin_cd';

[getNotSizehin]
select m.hin_cd
from m_hin m
where m.hin_cd =':hin_cd'
and dhin_cd is null;

[getSizehin]
select m.hin_cd
from m_hin m
left join m_hin m2 on (m2.dhin_cd = m.hin_cd)
where m.hin_cd = ':hin_cd'
and m.hin_cd = m.dhin_cd
and exists (select 1 from m_hin_size ms where m2.hin_cd = ms.hin_cd);