CREATE OR REPLACE function insertshiharai(psrclist text, psakuseiuserid text)
returns numeric
AS $$
DECLARE
    vreccnt numeric;
    vrecret record;
    vcuridx numeric;
    vtmpnum numeric;
    vzeiritsu numeric[];
    vzeinukikingaku numeric[];
    vhikazeikingaku numeric;
    vshiharaizeinuki numeric;
    vshohizei numeric[];
    vparamshiharai cursor for
        select distinct 
            shiire.shiiresakicd
            , case 
                when trim(first_value(tmp3.shimebi) over (partition by tmp3.shiiresakicd order by tmp3.shiireno)) in ('', '0001/01/01') 
                    then clock_timestamp()::date 
                else to_date(first_value(tmp3.shimebi) over (partition by tmp3.shiiresakicd order by tmp3.shiireno), 'yyyy/mm/dd') 
                end as shimebi
            , first_value(tmp3.biko) over (partition by tmp3.shiiresakicd order by tmp3.shiireno) as biko
            , first_value(tmp3.shanaibiko) over (partition by tmp3.shiiresakicd order by tmp3.shiireno) as shanaibiko
            , sum(tmp3.maebaraiseikyugaku) over (partition by tmp3.shiiresakicd) as maebaraiseikyugakugokei
            , shiire.shohizeihasushorikb
        from (
            select
                cast(juchukey [1] as numeric) as shiireno
                , cast(juchukey [2] as numeric) as rirekino
                , cast(juchukey [3] as text) as biko
                , cast(juchukey [4] as text) as shanaibiko
                , cast(juchukey [5] as text) as shimebi
                , cast(juchukey [6] as text) as shiiresakicd
                , cast(juchukey [7] as numeric) as maebaraiseikyugaku 
            from
                ( 
                    select
                        regexp_split_to_array(a, '_,') as juchukey 
                    from
                        ( 
                            select
                                regexp_split_to_table(psrclist, '_:') as a
                        ) tmp
                ) tmp2
        ) tmp3
        inner join mst_shiiresaki shiire on shiire.shiiresakicd = tmp3.shiiresakicd
        order by shiire.shiiresakicd;
                
    vparamshiharaisum cursor (pshiiresakicd text) for
        select
            shiire.shiiresakicd
            , ms.shohizeiritsu
            , ms.kazeikb
            , sum(ms.kingaku) as kingaku
            , sum(coalesce(ms.choseishohizeigaku, 0)) as choseishohizeigaku
        from
            ( 
                select
                    cast(juchukey [1] as numeric) as shiireno
                    , cast(juchukey [2] as numeric) as rirekino
                    , cast(juchukey [3] as text) as biko
                    , cast(juchukey [4] as text) as shanaibiko
                    , cast(juchukey [5] as text) as shimebi
                    , cast(juchukey [6] as text) as shiiresakicd
                    , cast(juchukey [7] as numeric) as maebaraiseikyugaku
                from
                    ( 
                        select
                            regexp_split_to_array(a, '_,') as juchukey 
                        from
                            ( 
                                select
                                    regexp_split_to_table(psrclist, '_:') as a
                            ) tmp
                    ) tmp2
            ) tmp3
        inner join dat_d_shiire ds 
            on ds.shiireno = tmp3.shiireno 
        inner join dat_m_shiire ms 
            on ms.shiireno = ds.shiireno 
            and ms.meisaikb = '1' 
        inner join mst_shiiresaki shiire 
            on shiire.shiiresakicd = ds.shiiresakicd
        where
            shiire.shiiresakicd = pshiiresakicd
        group by
            shiire.shiiresakicd, ms.shohizeiritsu, ms.kazeikb
        order by
            shiire.shiiresakicd, ms.shohizeiritsu, ms.kazeikb;
    vparamshiharaiuw cursor (pshiiresakicd text) for
        select distinct
            tmp3.shiiresakicd
            , tmp3.shiireno
            , tmp3.rirekino
        from
            ( 
                select
                    cast(juchukey [1] as numeric) as shiireno
                    , cast(juchukey [2] as numeric) as rirekino
                    , cast(juchukey [3] as text) as biko
                    , cast(juchukey [4] as text) as shanaibiko
                    , cast(juchukey [5] as text) as shimebi
                    , cast(juchukey [6] as text) as shiiresakicd
                    , cast(juchukey [7] as numeric) as maebaraiseikyugaku
                from
                    ( 
                        select
                            regexp_split_to_array(a, '_,') as juchukey 
                        from
                            ( 
                                select
                                    regexp_split_to_table(psrclist, '_:') as a
                            ) tmp
                    ) tmp2
            ) tmp3
        where
            tmp3.shiiresakicd = pshiiresakicd
        order by
            tmp3.shiiresakicd, tmp3.shiireno;
 BEGIN
    vreccnt := 0;
    FOR recd IN vparamshiharai LOOP
        select nextval('sqns_shiharaino') into vrecret;
        vcuridx := -1;
        vzeiritsu := null::numeric[];
        vzeinukikingaku := null::numeric[];
        vshohizei := null::numeric[];
        vshiharaizeinuki := 0;
        vhikazeikingaku := 0;
        FOR recsum IN vparamshiharaisum(recd.shiiresakicd) LOOP
            if vcuridx < 0 or vzeiritsu[vcuridx] <> recsum.shohizeiritsu then
                if recsum.shohizeiritsu <> 0 then
                    vcuridx := vcuridx + 1;
                    vzeiritsu[vcuridx] := recsum.shohizeiritsu;
                    vzeinukikingaku[vcuridx] := 0;
                    vshohizei[vcuridx] := 0;
                end if;
            end if;
            if recsum.shohizeiritsu = 0 then
                vhikazeikingaku := vhikazeikingaku + recsum.kingaku;
                vshiharaizeinuki := vshiharaizeinuki + recsum.kingaku;
            elsif recsum.kazeikb = '1' then
                vzeinukikingaku[vcuridx] := vzeinukikingaku[vcuridx] + recsum.kingaku;
                vtmpnum = recsum.kingaku * recsum.shohizeiritsu / 100;
                if recd.shohizeihasushorikb = '1' then
                    vtmpnum := floor(vtmpnum);
                elsif recd.shohizeihasushorikb = '2' then
                    vtmpnum := ceil(vtmpnum);
                elsif recd.shohizeihasushorikb = '3' then
                    vtmpnum := round(vtmpnum);
                else
                    RAISE EXCEPTION '無効な消費税端数処理区分が指定されました --> 仕入先:% 消費税端数処理区分:%', recd.shiiresakicd, recd.shohizeihasushorikb USING HINT = '仕入先マスタの消費税端数処理区分を確認してください';
                end if;
                vshohizei[vcuridx] := vshohizei[vcuridx] + vtmpnum + recsum.choseishohizeigaku;
                vshiharaizeinuki := vshiharaizeinuki + recsum.kingaku;
            elsif recsum.kazeikb = '2' then
                vtmpnum := recsum.kingaku / (recsum.shohizeiritsu + 100) * recsum.shohizeiritsu;
                if recd.shohizeihasushorikb = '1' then
                    vtmpnum := floor(vtmpnum);
                elsif recd.shohizeihasushorikb = '2' then
                    vtmpnum := ceil(vtmpnum);
                elsif recd.shohizeihasushorikb = '3' then
                    vtmpnum := round(vtmpnum);
                else
                    RAISE EXCEPTION '無効な消費税端数処理区分が指定されました --> 仕入先:% 消費税端数処理区分:%', recd.shiiresakicd, recd.shohizeihasushorikb USING HINT = '仕入先マスタの消費税端数処理区分を確認してください';
                end if;
                vzeinukikingaku[vcuridx] := vzeinukikingaku[vcuridx] + recsum.kingaku - vtmpnum -  recsum.choseishohizeigaku;
                vshohizei[vcuridx] := vshohizei[vcuridx] + vtmpnum + recsum.choseishohizeigaku;
                vshiharaizeinuki := vshiharaizeinuki + recsum.kingaku - vtmpnum - recsum.choseishohizeigaku;
            elsif recsum.kazeikb = '3' then
                vhikazeikingaku := vhikazeikingaku + recsum.kingaku;
                vshiharaizeinuki := vshiharaizeinuki + recsum.kingaku;
            else
                RAISE EXCEPTION '無効な課税区分が指定されました --> 仕入先:% 課税区分:%', recd.shiiresakicd, recsum.kazeikb USING HINT = '仕入の課税区分を確認してください';
            end if;
        END LOOP;
        if vshohizei[2] is not null then
            RAISE EXCEPTION '税率は非課税を除いて2つまでしか混在できません --> 仕入先:%', recd.shiiresakicd USING HINT = '支払締を分けるか、仕入の税率を見直してください。';
        end if;
        FOR recuw IN vparamshiharaiuw(recd.shiiresakicd) LOOP
            -- 帳票内訳挿入
            INSERT 
            INTO dat_m_chohyouchiwake( 
                chohyokb
                , chohyono
                , denpyoshuruicd
                , denpyono
                , rirekino
                , sakuseiuserid
                , sakuseinichiji
                , koshinuserid
                , koshinnichiji
            ) values (
                '2'
                , currval('sqns_shiharaino')
                , 'SHIIRE'
                , recuw.shiireno
                , recuw.rirekino
                , psakuseiuserid
                , clock_timestamp()
                , psakuseiuserid
                , clock_timestamp() 
            );
        END LOOP;
        -- 支払挿入
        INSERT 
        INTO dat_d_shiharai( 
            shiharaino
            , shiiresakicd
            , shimebi
            , shiharaigaku
            , hikazeikingaku
            , shohizeiritsu1
            , kazeitaishogaku1
            , shohizeigaku1
            , shohizeiritsu2
            , kazeitaishogaku2
            , shohizeigaku2
            , maebaraiseikyugaku
            , biko
            , shanaibiko
            , shoninnichiji
            , shoninshaid
            , insatsukaisu
            , sakuseiuserid
            , sakuseinichiji
            , koshinuserid
            , koshinnichiji
        ) values (
            currval('sqns_shiharaino')
            , recd.shiiresakicd
            , recd.shimebi
            , vshiharaizeinuki
            , vhikazeikingaku
            , coalesce(vzeiritsu[0], 0)
            , coalesce(vzeinukikingaku[0], 0)
            , coalesce(vshohizei[0], 0)
            , coalesce(vzeiritsu[1], 0)
            , coalesce(vzeinukikingaku[1], 0)
            , coalesce(vshohizei[1], 0)
            , recd.maebaraiseikyugakugokei
            , recd.biko
            , recd.shanaibiko
            , null
            , null
            , 0
            , psakuseiuserid
            , clock_timestamp()
            , psakuseiuserid
            , clock_timestamp()
        );
        vreccnt := vreccnt + 1;
     END LOOP;
   return vreccnt;
END;
$$
LANGUAGE plpgsql;
