CREATE OR REPLACE function insertseikyu(psrclist text, psakuseiuserid text)
returns numeric
AS $$
DECLARE
    vreccnt numeric;
    vrecret record;
    vcuridx numeric;
    vtmpnum numeric;
    vzeiritsu numeric[];
    vzeinukikingaku numeric[];
    vhikazeikingaku numeric;
    vseikyuzeinuki numeric;
    vshohizei numeric[];
    vparamseikyu cursor for
        select distinct 
            tok.tokuisakicd
            , case 
                when trim(tmp3.seikyubi) in ('', '0001/01/01') 
                    then clock_timestamp()::date 
                else to_date(tmp3.seikyubi, 'yyyy/mm/dd') 
                end as seikyubi
            , case 
                when trim(tmp3.nyukinyoteibi) in ('', '0001/01/01') 
                    then getnyukinyoteibi(clock_timestamp()::date, tok.nyukinyoteikb, tok.nyukinyoteitsuki, tok.seikyushimekiribi, tok.nyukinyoteinissu) 
                else to_date(tmp3.nyukinyoteibi, 'yyyy/mm/dd') 
                end as nyukinyoteibi
            , tmp3.biko
            , tmp3.shanaibiko
            , sum(tmp3.maeukeseikyugaku) over (partition by tmp3.tokuisakicd, tmp3.seikyubi, tmp3.nyukinyoteibi, tmp3.biko, tmp3.shanaibiko) as maeukeseikyugakugokei
            , tok.shohizeihasushorikb
            , tmp3.seikyubi as seikyubirow
            , tmp3.nyukinyoteibi as nyukinyoteibirow
        from (
            select
                cast(juchukey [1] as numeric) as uriageno
                , 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 seikyubi
                , cast(juchukey [6] as text) as tokuisakicd
                , cast(juchukey [7] as numeric) as maeukeseikyugaku 
                , cast(juchukey [8] as numeric) as maeukeseikyuno
                , cast(juchukey [9] as text) as nyukinyoteibi
            from
                ( 
                    select
                        regexp_split_to_array(a, '_,') as juchukey 
                    from
                        ( 
                            select
                                regexp_split_to_table(psrclist, '_:') as a
                        ) tmp
                ) tmp2
        ) tmp3
        inner join mst_tokuisaki tok on tok.tokuisakicd = tmp3.tokuisakicd
        order by tok.tokuisakicd, tmp3.seikyubi, tmp3.nyukinyoteibi, tmp3.biko, tmp3.shanaibiko;
    vparammseikyutoksum cursor (ptokuisakicd text, pseikyubi text, pnyukinyoteibi text, pbiko text, pshanaibiko text) for
        select
            tok.tokuisakicd
            , tmp3.seikyubi
            , tmp3.nyukinyoteibi
            , tmp3.biko
            , tmp3.shanaibiko
            , mu.shohizeiritsu
            , mu.kazeikb
            , sum(mu.kingaku) as kingaku
        from
            ( 
                select
                    cast(juchukey [1] as numeric) as uriageno
                    , 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 seikyubi
                    , cast(juchukey [6] as text) as tokuisakicd
                    , cast(juchukey [7] as numeric) as maeukeseikyugaku
                    , cast(juchukey [8] as numeric) as maeukeseikyuno 
                    , cast(juchukey [9] as text) as nyukinyoteibi
                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_uriage du 
            on du.uriageno = tmp3.uriageno 
        inner join dat_m_uriage mu 
            on mu.uriageno = du.uriageno 
            and mu.meisaikb = '1' 
        inner join mst_tokuisaki tok 
            on tok.tokuisakicd = du.tokuisakicd
        where
            tok.tokuisakicd = ptokuisakicd
            and tmp3.seikyubi = pseikyubi
            and tmp3.nyukinyoteibi = pnyukinyoteibi
            and tmp3.biko = pbiko
            and tmp3.shanaibiko = pshanaibiko
        group by
           tok.tokuisakicd, tmp3.seikyubi, tmp3.nyukinyoteibi, tmp3.biko, tmp3.shanaibiko, mu.shohizeiritsu, mu.kazeikb
        order by
           tok.tokuisakicd, tmp3.seikyubi, tmp3.nyukinyoteibi, tmp3.biko, tmp3.shanaibiko, mu.shohizeiritsu, mu.kazeikb;
    vparammseikyutokuw cursor (ptokuisakicd text, pseikyubi text, pnyukinyoteibi text, pbiko text, pshanaibiko text) for
        select distinct
            tmp3.tokuisakicd
            , tmp3.seikyubi
            , tmp3.nyukinyoteibi
            , tmp3.biko
            , tmp3.shanaibiko
            , tmp3.uriageno
            , tmp3.rirekino
            , tmp3.maeukeseikyuno
        from
            ( 
                select
                    cast(juchukey [1] as numeric) as uriageno
                    , 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 seikyubi
                    , cast(juchukey [6] as text) as tokuisakicd
                    , cast(juchukey [7] as numeric) as maeukeseikyugaku
                    , cast(juchukey [8] as numeric) as maeukeseikyuno 
                    , cast(juchukey [9] as text) as nyukinyoteibi 
                from
                    ( 
                        select
                            regexp_split_to_array(a, '_,') as juchukey 
                        from
                            ( 
                                select
                                    regexp_split_to_table(psrclist, '_:') as a
                            ) tmp
                    ) tmp2
            ) tmp3
        where
            tmp3.tokuisakicd = ptokuisakicd
            and tmp3.seikyubi = pseikyubi
            and tmp3.nyukinyoteibi = pnyukinyoteibi
            and tmp3.biko = pbiko
            and tmp3.shanaibiko = pshanaibiko
        order by
            tmp3.uriageno;
 BEGIN
    vreccnt := 0;
    FOR recd IN vparamseikyu LOOP
        select nextval('sqns_seikyuno') into vrecret;
        vcuridx := -1;
        vzeiritsu := null::numeric[];
        vzeinukikingaku := null::numeric[];
        vshohizei := null::numeric[];
        vseikyuzeinuki := 0;
        vhikazeikingaku := 0;
        FOR recsum IN vparammseikyutoksum(recd.tokuisakicd, recd.seikyubirow, recd.nyukinyoteibirow, recd.biko, recd.shanaibiko) 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;
                vseikyuzeinuki := vseikyuzeinuki + 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.tokuisakicd, recd.shohizeihasushorikb USING HINT = '得意先マスタの消費税端数処理区分を確認してください';
                end if;
                vshohizei[vcuridx] := vshohizei[vcuridx] + vtmpnum;
                vseikyuzeinuki := vseikyuzeinuki + 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.tokuisakicd, recd.shohizeihasushorikb USING HINT = '得意先マスタの消費税端数処理区分を確認してください';
                end if;
                vzeinukikingaku[vcuridx] := vzeinukikingaku[vcuridx] + recsum.kingaku - vtmpnum;
                vshohizei[vcuridx] := vshohizei[vcuridx] + vtmpnum;
                vseikyuzeinuki := vseikyuzeinuki + recsum.kingaku - vtmpnum;
            elsif recsum.kazeikb = '3' then
                vhikazeikingaku := vhikazeikingaku + recsum.kingaku;
                vseikyuzeinuki := vseikyuzeinuki + recsum.kingaku;
            else
                RAISE EXCEPTION '無効な課税区分が指定されました --> 得意先:% 課税区分:%', recd.tokuisakicd, recsum.kazeikb USING HINT = '売上の課税区分を確認してください';
            end if;
        END LOOP;
        if vshohizei[2] is not null then
            RAISE EXCEPTION '税率は非課税を除いて2つまでしか混在できません --> 得意先:%', recd.tokuisakicd USING HINT = '請求を分けるか、売上の税率を見直してください。';
        end if;
        FOR recuw IN vparammseikyutokuw(recd.tokuisakicd, recd.seikyubirow, recd.nyukinyoteibirow, recd.biko, recd.shanaibiko) LOOP
            -- 帳票内訳挿入
            INSERT 
            INTO dat_m_chohyouchiwake( 
                chohyokb
                , chohyono
                , denpyoshuruicd
                , denpyono
                , rirekino
                , sakuseiuserid
                , sakuseinichiji
                , koshinuserid
                , koshinnichiji
            ) values (
                '1'
                , currval('sqns_seikyuno')
                , case when recuw.uriageno = 0 then 'MAEUKESEIKYU' else 'URIAGE' end
                , case when recuw.uriageno = 0 then recuw.maeukeseikyuno else recuw.uriageno end
                , case when recuw.uriageno = 0 then 0 else recuw.rirekino end
                , psakuseiuserid
                , clock_timestamp()
                , psakuseiuserid
                , clock_timestamp() 
            );
        END LOOP;
        -- 請求挿入
        INSERT 
        INTO dat_d_seikyu( 
            seikyuno
            , tokuisakicd
            , shimebi
            , seikyubi
            , nyukinyoteibi
            , seikyugaku
            , hikazeikingaku
            , shohizeiritsu1
            , kazeitaishogaku1
            , shohizeigaku1
            , shohizeiritsu2
            , kazeitaishogaku2
            , shohizeigaku2
            , maeukeseikyugaku
            , biko
            , shanaibiko
            , shoninnichiji
            , shoninshaid
            , insatsukaisu
            , sakuseiuserid
            , sakuseinichiji
            , koshinuserid
            , koshinnichiji
        ) values (
            currval('sqns_seikyuno')
            , recd.tokuisakicd
            , recd.seikyubi
            , recd.seikyubi
            , recd.nyukinyoteibi
            , vseikyuzeinuki
            , 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.maeukeseikyugakugokei
            , recd.biko
            , recd.shanaibiko
            , null
            , null
            , 0
            , psakuseiuserid
            , clock_timestamp()
            , psakuseiuserid
            , clock_timestamp()
        );
        vreccnt := vreccnt + 1;
     END LOOP;
   return vreccnt;
END;
$$
LANGUAGE plpgsql;
