feladas_ki

create or alter procedure FELADAS_KI (
    P_FELADSEMA integer,
    P_DATUM1 date,
    P_DATUM2 date,
    P_TELEPHELY integer = 0,
    P_LASTSYNC integer = -1)
returns (
    R_BIZONYLAT integer,
    R_BIZSZAM varchar(20),
    R_TELJESITES date,
    R_DATUM date,
    R_FIZHATAR date,
    R_UGYFELID integer,
    R_UGYFELNEV varchar(81),
    R_UGYFELIRSZAM integer,
    R_UGYFELHELYSEG varchar(40),
    R_UGYFELCIM varchar(40),
    R_UGYFELADO varchar(15),
    R_UGYFELBANK varchar(34),
    R_DEVIZA integer,
    R_DEVIZANEV varchar(3),
    R_ARFOLYAM double precision,
    R_BIZ_NETTO double precision,
    R_BIZ_BRUTTO double precision,
    R_BIZ_BESZNETTO double precision,
    R_BIZ_BESZBRUTTO double precision,
    R_AFA double precision,
    R_TETEL_NETTOERTEK double precision,
    R_TETEL_AFAERTEK double precision,
    R_TETEL_BRUTTOERTEK double precision,
    R_TETEL_BESZERTEK double precision,
    R_AZONOSITO varchar(20),
    R_UGYFELEUADO varchar(20),
    R_TETEL_GYUJTO integer,
    R_TETEL_GYUJTO_NEV varchar(30),
    R_FOKSZAM1 varchar(10),
    R_FOKSZAM2 varchar(10),
    R_GYUJTO_ID integer,
    R_GYUJTO_NEV varchar(20),
    R_GYUJTOTETEL_ID integer,
    R_GYUJTOTETEL_KOD varchar(4),
    R_GYUJTOTETEL_NEV varchar(30),
    R_AFAGYUJTO smallint,
    R_VTSZ varchar(10),
    R_TOMEG double precision,
    R_UGYFELBES_TIPUS varchar(20),
    R_UGYFELBES_ERTEK varchar(40),
    R_TELEPHELY integer,
    R_SYNC integer)
as
declare variable V_FIZMOD_KP smallint;
declare variable V_FIZMOD_UT smallint;
declare variable V_FIZMOD_CSEKK smallint;
declare variable V_SORSZAM integer;
declare variable V_BIZSZAMUTOTAG integer;
declare variable V_AKT integer;
declare variable V_COUNT integer;
declare variable V_OSSZ_TETEL_NETTO integer;
declare variable V_OSSZ_TETEL_BRUTTO integer;
declare variable V_VEVO_FORDITOTT smallint;
declare variable V_FORDITOTT smallint;
declare variable V_BIZ_BRUTTO double precision;
declare variable V_DEFAULT_AFA double precision;
declare variable V_ID_TERMEK integer;
declare variable V_ID_SZAMLATETEL integer;
begin
  V_FIZMOD_KP = 0;
  V_FIZMOD_UT = 0;
  R_DEVIZA = 1;
  R_DEVIZANEV = 'HUF';
  R_ARFOLYAM = 1;
  R_TETEL_BESZERTEK = 0;
  R_BIZ_BESZNETTO = 0;
  R_BIZ_BESZBRUTTO = 0;
  R_UGYFELBES_TIPUS = 'Üzletkötő';
  select FIZMOD_KP, FIZMOD_UT, FIZMOD_CSEKK
  from FELADSEMA
  where ID_FELADSEMA = :P_FELADSEMA
  into :V_FIZMOD_KP, :V_FIZMOD_UT, :V_FIZMOD_CSEKK;
  select first 1 AFA
  from AFAERVENY
  where ID_AFA = 1 and
        BEALLITVA = 1
  into :V_DEFAULT_AFA;
  for select SZ.ID_SZAMLA, SZ.SORSZAM, SZ.TELJESITES, SZ.DATUM, SZ.FIZHATAR, SZ.ID_VEVO, V.NEV, V.IRSZAM, V.VAROS,
             V.CIM, SUBSTR(V.ADOSZAM, 1, 15), V.SZAMLASZAM, SZ.NETTO, SZ.BRUTTO, V.KONYVELESI_AZONOSITO,
             SZ.EREDETI_BIZSZAM, coalesce(V.FORDITOTT, 0),
             (select NEV
              from UZLETKOTO
              where ID_UZLETKOTO = coalesce(SZ.ID_UZLETKOTO, V.ID_UZLETKOTO)), SZ.ID_ORIG_TELEPHELY, SZ.ID_SYNC,
             coalesce(SZ.FORDITOTT, 0)
      from SZAMLA SZ, VEVO V
      where V.ID_VEVO = SZ.ID_VEVO and
            ((:P_LASTSYNC = -1 and
            SZ.TELJESITES between :P_DATUM1 and :P_DATUM2 and
            SZ.ID_ORIG_TELEPHELY in (select ID_ERTEK
                                     from FELADSEMATETEL
                                     where ID_FELADSEMA = :P_FELADSEMA and
                                           ID_TULAJDONSAG = 1)) or (:P_LASTSYNC > -1 and
            SZ.ID_TELEPHELYSYNC = :P_TELEPHELY and
            SZ.ID_ORIG_TELEPHELY = :P_TELEPHELY and
            SZ.ID_SYNC > :P_LASTSYNC)) and
            coalesce(SZ.EREDETI_BIZSZAM, '') <> '' and
            ((:V_FIZMOD_KP = 1 and
            SZ.FIZMOD = 0) or (:V_FIZMOD_UT = 1 and
            SZ.FIZMOD = 1) or (:V_FIZMOD_CSEKK = 1 and
            SZ.FIZMOD = 2))
      into :R_BIZONYLAT, :V_SORSZAM, :R_TELJESITES, :R_DATUM, :R_FIZHATAR, :R_UGYFELID, :R_UGYFELNEV, :R_UGYFELIRSZAM,
           :R_UGYFELHELYSEG, :R_UGYFELCIM, :R_UGYFELADO, :R_UGYFELBANK, :R_BIZ_NETTO, :R_BIZ_BRUTTO, :R_AZONOSITO,
           :R_BIZSZAM, :V_VEVO_FORDITOTT, :R_UGYFELBES_ERTEK, :R_TELEPHELY, :R_SYNC, :V_FORDITOTT
  do
  begin
    V_AKT = 1;
    V_OSSZ_TETEL_NETTO = 0;
    V_OSSZ_TETEL_BRUTTO = 0;
    if (V_FORDITOTT = 1) then
    begin
      R_AFAGYUJTO = 1;
      V_BIZ_BRUTTO = R_BIZ_NETTO;
    end
    else
    begin
      R_AFAGYUJTO = 0;
      V_BIZ_BRUTTO = R_BIZ_BRUTTO;
    end
    V_COUNT = 0;

    select count(*)
    from szamlatetel szt
    where szt.id_szamla = :r_bizonylat and szt.id_termek is null
    into :V_count;

    for select iif(:R_AFAGYUJTO = 1, V.NEV, '')
        from SZAMLATETEL SZT
        join TERMEK T on SZT.ID_TERMEK = T.ID_TERMEK and SZT.ID_SZAMLA = :R_BIZONYLAT
        left join AFAERVENY E on E.ID_AFA = T.ID_AFA and E.DATUM1 <= :R_TELJESITES and (E.DATUM2 is null or E.DATUM2 >= :R_TELJESITES)
        left join CSOPORT CS on CS.ID_CSOPORT = T.ID_CSOPORT
        left outer join VTSZ V on V.ID_VTSZ = T.ID_VTSZ
        group by 1, coalesce(E.AFA, :V_DEFAULT_AFA), CS.ID_GYUJTO
        into :R_VTSZ
    do
      V_COUNT = V_COUNT + 1;

    R_VTSZ = null;

    for select szt.id_termek, szt.id_szamlatetel, cast(sum(SZT.ELADAR * SZT.MENNYISEG) as integer),
        cast(sum(SZT.ELADAR * SZT.MENNYISEG * (1.0 + iif(:R_AFAGYUJTO = 1, 0, :V_DEFAULT_AFA) / 100.0)) as integer), 0
        from szamlatetel szt
        where szt.id_szamla = :R_bizonylat
        group by szt.id_termek, szt.id_szamlatetel
        into :V_ID_termek, :v_id_szamlatetel, :r_tetel_nettoertek, :r_tetel_bruttoertek, :r_tomeg
    do
    begin
        if(v_id_termek is not null) then
        begin
            select distinct iif(:R_AFAGYUJTO = 1, V.NEV, ''), iif(:R_AFAGYUJTO = 1, 0, coalesce(E.AFA, :V_DEFAULT_AFA)),
               cast(sum(SZT.ELADAR * SZT.MENNYISEG) as integer),
               cast(sum(SZT.ELADAR * SZT.MENNYISEG * (1.0 + iif(:R_AFAGYUJTO = 1, 0, coalesce(E.AFA, :V_DEFAULT_AFA)) / 100.0)) as integer),
               CS.ID_GYUJTO, iif(:R_AFAGYUJTO = 1, sum(SZT.MENNYISEG *
               case
                 when T.EGYSEG = 'KG' then 1.0
                 when T.EGYSEG = 'Q' then 100.0
                 when T.EGYSEG = 'T' then 1000.0
                 when T.EGYSEG = 'GR' then 0.001
                 else coalesce(T.TOMEG, 0)
               end), 0)
            from SZAMLATETEL SZT
            join TERMEK T on SZT.ID_TERMEK = T.ID_TERMEK and SZT.ID_SZAMLA = :R_BIZONYLAT
            left join AFAERVENY E on E.ID_AFA = T.ID_AFA and E.DATUM1 <= :R_TELJESITES and (E.DATUM2 is null or E.DATUM2 >= :R_TELJESITES)
            left outer join CSOPORT CS on CS.ID_CSOPORT = T.ID_CSOPORT
            left outer join VTSZ V on V.ID_VTSZ = T.ID_VTSZ
            where szt.id_szamlatetel = :v_id_szamlatetel and szt.id_termek = :v_id_termek
            group by 1, coalesce(E.AFA, :V_DEFAULT_AFA), CS.ID_GYUJTO
            into :R_VTSZ, :R_AFA, :R_TETEL_NETTOERTEK, :R_TETEL_BRUTTOERTEK, :R_TETEL_GYUJTO, :R_TOMEG;
        end

        if (V_AKT = V_COUNT) then
        begin
           R_TETEL_NETTOERTEK = R_BIZ_NETTO - V_OSSZ_TETEL_NETTO;
           R_TETEL_BRUTTOERTEK = V_BIZ_BRUTTO - V_OSSZ_TETEL_BRUTTO;
        end

        R_TETEL_AFAERTEK = R_TETEL_BRUTTOERTEK - R_TETEL_NETTOERTEK;
        R_TETEL_GYUJTO_NEV = null;

      select NEV
      from GYUJTO
      where ID_GYUJTO = :R_TETEL_GYUJTO
      into :R_TETEL_GYUJTO_NEV;

      suspend;

      V_AKT = V_AKT + 1;
      V_OSSZ_TETEL_NETTO = V_OSSZ_TETEL_NETTO + R_TETEL_NETTOERTEK;
      V_OSSZ_TETEL_BRUTTO = V_OSSZ_TETEL_BRUTTO + R_TETEL_BRUTTOERTEK;
    end
  end
end