alter table NAPLO add  "NA_FE_ID"  INTEGER default 0 NOT NULL^

alter table FEJLEC add "FE_NYOMSZAM"  VARCHAR(16) CHARACTER SET WIN1250 default '' COLLATE PXW_HUNDC^

drop index "UG_1"^

CREATE UNIQUE INDEX "UG_1" ON "UGYFEL"("UG_ROVID","UG_EV","UG_KARTON")^

drop table EVZAR^

CREATE TABLE "EVZAR"
( "EV_NEV"     VARCHAR(30) CHARACTER SET WIN1250 default '' NOT NULL COLLATE PXW_HUNDC,
  "EV_SOR"     NUMERIC(14,5) default 0 NOT NULL,
  "EV_A"       VARCHAR(250) CHARACTER SET WIN1250 default '' NOT NULL COLLATE PXW_HUNDC,
  "EV_B"       VARCHAR(30) CHARACTER SET WIN1250 default '' NOT NULL COLLATE PXW_HUNDC,
  "EV_SEGED"   NUMERIC(14,4) default 0,
  "EV_FLAG"    INTEGER default 0,
  "EV_US_NEV"	 "D_USER" COLLATE PXW_HUNDC,
  "EV_TIME"	   TIMESTAMP default 'now',
  PRIMARY KEY ("EV_NEV","EV_SOR")
)^

drop table BEALL^

CREATE TABLE "BEALL"
( "BE_NEV"      VARCHAR(40) CHARACTER SET WIN1250 default '' NOT NULL COLLATE PXW_HUNDC,
  "BE_TAG"      INTEGER default 0 NOT NULL,
  "BE_OK"       "D_NY",
  "BE_STR"      VARCHAR(254) CHARACTER SET WIN1250 default '' COLLATE PXW_HUNDC,
  "BE_NUM"      NUMERIC(18,5) default 0 NOT NULL,
  "BE_DAT"	    TIMESTAMP default 'now',
  "BE_EV"       "D_EV",
  "BE_US_NEV"	"D_USER" COLLATE PXW_HUNDC,
  "BE_TIME"	TIMESTAMP default 'now',
  PRIMARY KEY ("BE_NEV","BE_TAG","BE_EV")
)^


/**DB0301****************************/


CREATE EXCEPTION "EAR"   'DbErr8: Az árfolyamkülömbözeti tétel védett az összevezetés miatt'^

alter table NAPLO add  "NA_STATUS"   "D_STATUS"^

CREATE TABLE "IDOZAR"
( "ID_TELJESIT"	DATE default 'now' NOT NULL,
  "ID_US_NEV"	"D_USER" COLLATE PXW_HUNDC,
  "ID_TIME"	   TIMESTAMP default 'now',
  "ID_EV"      "D_EV",
 PRIMARY KEY ("ID_TELJESIT","ID_EV")
)^

CREATE TRIGGER IDOZAR_BIU FOR IDOZAR BEFORE INSERT OR UPDATE POSITION 0 AS
BEGIN New.ID_TIME='now'; END^

CREATE TABLE "VERSION"
( "VE_NUM"    VARCHAR(4) default '0100' NOT NULL,
  "VE_TIME"	  TIMESTAMP default 'now',
 PRIMARY KEY ("VE_NUM")
)^

CREATE TRIGGER VERSION_BIU FOR VERSION BEFORE INSERT OR UPDATE POSITION 0 AS
BEGIN New.VE_TIME='now'; END^

drop trigger EVZAR_BIU^
CREATE TRIGGER EVZAR_BIU FOR EVZAR BEFORE INSERT OR UPDATE POSITION 0 AS
BEGIN New.EV_TIME='now'; END^

drop trigger OSSZEPONT_BI^

CREATE TRIGGER OSSZEPONT_BI FOR OSSZEPONT BEFORE INSERT POSITION 0 AS
DECLARE VARIABLE A INTEGER;
DECLARE VARIABLE FEID INTEGER;
DECLARE VARIABLE C INTEGER;
DECLARE VARIABLE B1 NUMERIC(7,1);
DECLARE VARIABLE B2 NUMERIC(7,1);
DECLARE VARIABLE A1 NUMERIC(16,2);
DECLARE VARIABLE A2 NUMERIC(16,2);
DECLARE VARIABLE D1 NUMERIC(16,2);
DECLARE VARIABLE D2 NUMERIC(16,2);
DECLARE VARIABLE K1 NUMERIC(16,6);
DECLARE VARIABLE K2 NUMERIC(16,6);
DECLARE VARIABLE C1 CHAR(1);
DECLARE VARIABLE C2 CHAR(1);
DECLARE VARIABLE T1 TIMESTAMP;
DECLARE VARIABLE T2 TIMESTAMP;
DECLARE VARIABLE H1 TIMESTAMP;
DECLARE VARIABLE H2 TIMESTAMP;
DECLARE VARIABLE V1 varchar(3);
DECLARE VARIABLE V2 varchar(3);
DECLARE VARIABLE I1 CHAR(1);
DECLARE VARIABLE I2 CHAR(1);
DECLARE VARIABLE DELTA NUMERIC(16,2);
DECLARE VARIABLE SUMA NUMERIC(16,2);

BEGIN
  New.OS_TIME='now';
  New.OS_KESES=0;
  if (New.OS_MOD is NULL) then New.OS_MOD=0;
  select FE_IRANY,FE_DEVKURZ,FE_TOTAL,FE_DEVTOTAL,FE_EV,FE_MO_CHAR,FE_TELJESIT,FE_HATARIDO, FE_DEV from FEJLEC where FE_ID=NEW.OS_FE_ID_A INTO :I1,:K1,:A1,:D1, :B1, :C1, :T1, :H1,:V1 ;
  select FE_IRANY,FE_DEVKURZ,FE_TOTAL,FE_DEVTOTAL,FE_EV,FE_MO_CHAR,FE_TELJESIT,FE_HATARIDO, FE_DEV from FEJLEC where FE_ID=NEW.OS_FE_ID_B INTO :I2,:K2,:A2,:D2, :B2, :C2, :T2, :H2,:V2 ;
  if (New.OS_DEV<>'+') then begin
    if (Upper(V1)<>Upper(V2)) then begin
      EXCEPTION "EOSDEV";
    end
    if (New.OS_SUMA>0) then begin
      IF ((C1=C2) and (I1<>I2) and (A1=A2) and (D1=D2)) then New.OS_MOD=1;
      DELTA=0;
      if (V1>' ') then begin
        if (D1>D2) then begin
          SUMA=D2;
        end else begin
          SUMA=D1;
        end
        if (NEW.OS_SUMA>SUMA) then begin
          New.OS_SUMA=SUMA;
        end
        if ((C1='V') and (C2='B')) then begin
          /* csak banki kiegy eseten van arfolyam aut. konyvelese */
          if (I1='T') then begin
            DELTA=NEW.OS_SUMA*K2 - NEW.OS_SUMA*K1;
          end else begin
            DELTA=NEW.OS_SUMA*K1 - NEW.OS_SUMA*K2;
          end
        end
        if ((C1='S') and (C2='B')) then begin
          if (I1='K') then begin
            DELTA=NEW.OS_SUMA*K1 - NEW.OS_SUMA*K2;
          end else begin
            DELTA=NEW.OS_SUMA*K2 - NEW.OS_SUMA*K1;
          end
        end
        if ((C1='B') and (C2='V')) then begin
          if (I2='T') then begin
            DELTA=NEW.OS_SUMA*K1 - NEW.OS_SUMA*K2;
          end else begin
            DELTA=NEW.OS_SUMA*K2 - NEW.OS_SUMA*K1;
          end
        end
        if ((C1='B') and (C2='S')) then begin
          if (I2='K') then begin
            DELTA=NEW.OS_SUMA*K2 - NEW.OS_SUMA*K1;
          end else begin
            DELTA=NEW.OS_SUMA*K1 - NEW.OS_SUMA*K2;
          end
        end
      end else begin
        if (A2<A1) then begin
          SUMA=A2;
        end else begin
          SUMA=A1;
        end
        if (NEW.OS_SUMA>SUMA) then begin
          New.OS_SUMA=SUMA;
        end
      end
      NEW.OS_12=DELTA;
      FEID=0;
      if (DELTA<>0) then begin
        A=0;
        select count(*) from BEALL where BE_NEV='1' and BE_OK='Y' and BE_EV=New.OS_EV into :A ;
        if (A>0) then begin
          FEID=GEN_ID(FE,1);
          /* uj fejlec a arfolyam kulombozet konyvelesehez */
        end
      end
      NEW.OS_FEID=FEID;
      if (B2>B1) then B1=B2;
      NEW.OS_EV=B1;
      if (C1<>C2) then begin
        if ((C1='S') or (C1='V'))  then begin
          C= cast((cast(T2 as DATE) - cast(H1 as DATE)) as INTEGER);
          New.OS_KESES=C;
        end else begin
          C= cast((cast(T1 as DATE) - cast(H2 as DATE)) as INTEGER);
          New.OS_KESES=C;
        end
      end
      if (C1<>C2) then begin
        if ((C2='V') or (C2='S')) then begin
          C=NEW.OS_FE_ID_B;
          NEW.OS_FE_ID_B=NEW.OS_FE_ID_A;
          NEW.OS_FE_ID_A=C;
        end
      end
    end
  end else begin
    C= cast((cast(T2 as DATE) - cast(H1 as DATE)) as INTEGER);
    New.OS_KESES=C;
  end
END^

drop trigger FOKONYV_BU^

CREATE TRIGGER FOKONYV_BU FOR FOKONYV BEFORE UPDATE POSITION 0 AS
BEGIN
  if (New.FO_US_NEV='TRSYS') then  begin
    New.FO_TIME=Old.FO_TIME;
    New.FO_US_NEV=Old.FO_US_NEV;
  end else begin
    New.FO_TIME='now';
  end
END^

drop trigger UGYFEL_BU^

CREATE TRIGGER UGYFEL_BU FOR UGYFEL BEFORE UPDATE POSITION 0 AS
BEGIN
  if ((OLD.UG_ROVID='') and (OLD.UG_ID=0))  then begin
    if ((NEW.UG_ID<>0) or (NEW.UG_ROVID<>'') or (NEW.UG_CEG<>'')) then EXCEPTION ENONE;
  end
  if (NEW.UG_US_NEV='TRSYS') then begin
    New.UG_TIME=Old.UG_TIME;
    NEW.UG_US_NEV=Old.UG_US_NEV;
  end else begin
    New.UG_TIME='now';
  end
END^

drop TRIGGER FEJLEC_BD^
CREATE TRIGGER FEJLEC_BD FOR FEJLEC BEFORE DELETE POSITION 0 AS
DECLARE VARIABLE T date;
BEGIN
  if ((Old.FE_AZ_ID>0) and (Old.FE_AFATOTAL<>0)) then EXCEPTION EAFA;
  if (Old.FE_OSMOD2=1) then EXCEPTION EAR;
END^

drop TRIGGER FEJLEC_BI^
CREATE TRIGGER FEJLEC_BI FOR FEJLEC BEFORE INSERT POSITION 0 AS
DECLARE VARIABLE A integer;
DECLARE VARIABLE T date;
BEGIN
  New.FE_TIME='now';
  New.FE_AZ_ID=0;
  if (New.FE_KESES is NULL) then New.FE_KESES=0;
  if (New.FE_OSMOD is NULL) then New.FE_OSMOD=0;
  if (New.FE_OSMOD2 is NULL) then New.FE_OSMOD2=0;
  if (New.FE_DEVTOTAL is NULL) then New.FE_DEVTOTAL=0;
  if ((New.FE_DEV is NULL) or (New.FE_DEV<' ')) then New.FE_DEV='';
  if (New.FE_DEV>' ')  then begin
    New.FE_SUMAR=New.FE_DEVTOTAL;
  end else begin
    New.FE_SUMAR=New.FE_TOTAL;
  end
  if (New.FE_AFATOTAL is NULL) then New.FE_AFATOTAL=0;
  if ((NEW.FE_MO_CHAR='B') or
      (NEW.FE_MO_CHAR='P') or
      (NEW.FE_MO_CHAR='N') or
      (NEW.FE_MO_CHAR='X'))
  then begin
     NEW.FE_TELJESIT=NEW.FE_KELTE;
     NEW.FE_HATARIDO=NEW.FE_KELTE;
  end
  if ((NEW.FE_MO_CHAR='N') and (New.FE_STATUS='N')) then New.FE_STATUS='R';
  if (New.FE_KIEGY is NULL) then New.FE_KIEGY=0;
  if (New.FE_KIEGY2 is NULL) then New.FE_KIEGY2=0;
  if (New.FE_GY_NEV is NULL) then New.FE_GY_NEV='';
END^

drop trigger FEJLEC_AI^

CREATE TRIGGER FEJLEC_AI FOR FEJLEC
  AFTER INSERT POSITION 0 AS
 DECLARE VARIABLE A integer;
begin
  A=0;
  update NAPLO set NA_RECNO=NA_RECNO+1 where NA_NO=New.FE_NAPLO and NA_EV=New.FE_EV and NA_TYP=New.FE_MO_CHAR;
  if (ROW_COUNT<=0) then begin
    insert into NAPLO (NA_TYP,NA_EV,NA_NO,NA_T,NA_K,NA_E,NA_OWNER,NA_FE_ID,NA_TIME,NA_RECNO) VALUES (New.FE_MO_CHAR,New.FE_EV,New.FE_NAPLO,0,0,0,New.FE_US_NEV,NEW.FE_ID,'now',1);
  end
  A=GEN_ID(CO,1);
  insert into CONTIR (CO_EV,CO_ID,CO_FE_ID,CO_MO_CHAR,CO_FE_NAPLO,CO_TE_ID,CO_FO_KARTON,CO_IRANY,CO_OSSZEG,CO_STATUS,CO_US_NEV,CO_TIME,CO_GY_NEV,CO_TELJESIT,CO_HATARIDO,CO_KELTE,CO_UG_ID,CO_AFAALAP,CO_AF_NEV,CO_ERR)
   VALUES (New.FE_EV, :A, New.FE_ID, NEW.FE_MO_CHAR,NEW.FE_NAPLO,0,NEW.FE_KARTON,NEW.FE_IRANY, NEW.FE_TOTAL,NEW.FE_STATUS,NEW.FE_US_NEV,'now',NEW.FE_GY_NEV, New.FE_TELJESIT, NEW.FE_HATARIDO,NEW.FE_KELTE,NEW.FE_UG_ID,0,'',New.FE_ERR);
END^


drop procedure ACT_NAPLO^

CREATE PROCEDURE ACT_NAPLO ( NP INTEGER, A CHAR(1), EV NUMERIC(7,1), OWN varchar(16) )
RETURNS ( N INTEGER,R INTEGER, T NUMERIC(16, 2), K NUMERIC(16, 2), E NUMERIC(16, 2), OW varchar(16), FEID integer)
AS
BEGIN
 /** NP<-1  insert new record NA_NO=NA_NO+1 ; NP=-1 - get max NA_NO ; NP>0 get NA_NO=NP **/
  if (NP>=0) then begin
    select NA_NO,NA_T,NA_K,NA_E,NA_OWNER,NA_RECNO,NA_FE_ID from NAPLO where NA_TYP=:A and NA_EV=:EV and NA_NO=:NP into :N,:T,:K,:E,:OW,:R,:FEID ;
    if (N is NULL) then begin
      N=-1; T=0; K=0; E=0; R=0; OW=OWN; FEID=0;
    end
  end else begin
    if (NP=-1) then begin
      select First 1 NA_NO,NA_T,NA_K,NA_E,NA_OWNER,NA_RECNO,NA_FE_ID from NAPLO where NA_TYP=:A and NA_EV=:EV order by NA_NO DESC into :N,:T,:K,:E,:OW,:R,:FEID;
      if (N is null) then begin
        N=1;
        FEID=1+GEN_ID(FE,0);
        insert into NAPLO (NA_TYP,NA_EV,NA_NO,NA_T,NA_K,NA_E,NA_OWNER,NA_FE_ID,NA_TIME,NA_RECNO) VALUES (:A,:EV,1,0,0,0,:OWN,:FEID,'now',0);
        N=1; T=0; K=0; E=0; R=0; OW=OWN;
      end
    end else begin
      select MAX(NA_NO) from NAPLO where NA_TYP=:A and NA_EV=:EV into :N ;
      if (N is NULL) then N=0;
      N=N+1;
      FEID=1+GEN_ID(FE,0);
      insert into NAPLO (NA_TYP,NA_EV,NA_NO,NA_T,NA_K,NA_E,NA_OWNER,NA_FE_ID,NA_TIME,NA_RECNO) VALUES (:A,:EV,:N,0,0,0,:OWN,:FEID,'now',0);
      T=0; K=0; E=0; R=0; OW=OWN;
    end
  end
  if (T is NULL) then T=0;
  if (K is NULL) then K=0;
  if (R is NULL) then R=0;
  if (OW is NULL) then OW='';
  if (N is NULL) then N=-1;
  if (FEID is NULL) then FEID=0;
END^


alter TABLE TETEL drop  "TE_ARANYOS"^

alter TABLE TETEL add  "TE_ARANYOS"  "D_NY"^

alter TABLE CONTIR drop  "CO_ARANYOS"^

alter TABLE CONTIR add  "CO_ARANYOS"  "D_NY"^


/**DB0403***********************/

alter table FEJLEC add "FE_REBUILD" integer default 0^

/**DB0503***********************/

drop PROCEDURE FEID_NAPLO^

CREATE PROCEDURE FEID_NAPLO
AS
DECLARE VARIABLE N INTEGER;
DECLARE VARIABLE E double precision;
DECLARE VARIABLE F INTEGER;
DECLARE VARIABLE M VARCHAR(1);

DECLARE VARIABLE A INTEGER;
DECLARE VARIABLE B INTEGER;
DECLARE VARIABLE C INTEGER;
DECLARE VARIABLE C1 CHAR(1);
DECLARE VARIABLE C2 CHAR(1);
DECLARE VARIABLE T1 TIMESTAMP;
DECLARE VARIABLE T2 TIMESTAMP;
DECLARE VARIABLE H1 TIMESTAMP;
DECLARE VARIABLE H2 TIMESTAMP;
DECLARE VARIABLE ID INTEGER;
DECLARE VARIABLE M1 INTEGER;
DECLARE VARIABLE M2 INTEGER;
BEGIN
  FOR select NA_NO,NA_EV, NA_TYP from NAPLO order by NA_EV,NA_NO INTO :N, :E, :M do
  begin
    select MIN(FE_ID) FROM FEJLEC where FE_MO_CHAR=:M and FE_NAPLO=:N and FE_EV=:E INTO :F;
    if (F is null) then F=0;
    update NAPLO set NA_FE_ID=:F where NA_NO=:N and NA_TYP=:M and NA_EV=:E;
  end
  update FEJLEC set FE_US_NEV='REBUILD', FE_SUMAR=FE_DEVTOTAL, FE_KIEGY2=F_ROUNDBY(FE_KIEGY*FE_DEVKURZ,2,2) where FE_DEV>' ';
  update FEJLEC set FE_US_NEV='REBUILD', FE_SUMAR=FE_TOTAL, FE_KIEGY2=FE_KIEGY where FE_DEV<=' ';
  FOR select OS_FE_ID_A, OS_FE_ID_B, OS_ID from OSSZEPONT order by OS_ID INTO :A, :B, :ID do
  begin
    select FE_MO_CHAR, FE_TELJESIT, FE_HATARIDO,FE_OSMOD from FEJLEC where FE_ID=:A INTO :C1,:T1,:H1,:M1 ;
    select FE_MO_CHAR, FE_TELJESIT, FE_HATARIDO,FE_OSMOD from FEJLEC where FE_ID=:B INTO :C2,:T2,:H2,:M2 ;
    C=0;
    if ((M1=M2) and (M1=1)) then begin
      update OSSZEPONT set OS_KESES=0 where OS_ID=:ID ;
      update FEJLEC set FE_KESES=0, FE_US_NEV='REBUILD' where FE_ID=:A or FE_ID=:B;
    end else begin
      if (C1<>C2) then begin
         /* not storno */
        if (((C1='S') or (C1='V')) and (C2<>'S') and (C2<>'V')) then begin
          C= cast((cast(T2 as DATE) - cast(H1 as DATE)) as INTEGER);
          update OSSZEPONT set OS_KESES=:C where OS_ID=:ID ;
          update FEJLEC set FE_KESES=:C, FE_US_NEV='REBUILD' where FE_ID=:A or FE_ID=:B;
        end
        if (((C2='S') or (C2='V')) and (C1<>'S') and (C1<>'V')) then begin
          C= cast((cast(T1 as DATE) - cast(H2 as DATE)) as INTEGER);
          update OSSZEPONT set OS_KESES=:C where OS_ID=:ID ;
          update FEJLEC set FE_KESES=:C, FE_US_NEV='REBUILD' where FE_ID=:A or FE_ID=:B;
        end
      end
    end
  end
END^

drop trigger OSSZEPONT_AD^

CREATE TRIGGER OSSZEPONT_AD FOR OSSZEPONT
  AFTER DELETE POSITION 0 AS
DECLARE VARIABLE SUMA NUMERIC(16,2);
BEGIN
  if (Old.OS_DEV<>'+') then begin
    if (Old.OS_DEV>' ') then begin
      if (Old.OS_MOD=1) then begin
        update FEJLEC set FE_KIEGY2=FE_KIEGY2-F_ROUNDBY(Old.OS_SUMA*FE_DEVKURZ,2,2), FE_KIEGY=FE_KIEGY-Old.OS_SUMA,FE_KESES=0, FE_OSMOD=0, FE_US_NEV='REBUILD' where FE_EV=Old.OS_EV and (FE_ID=Old.OS_FE_ID_A or FE_ID=Old.OS_FE_ID_B) ;
      end else begin
        if (OLD.OS_FEID>0) then begin
          update FEJLEC set FE_US_NEV='REBUILD', FE_OSMOD2=0 where FE_ID=Old.OS_FEID;
          delete from FEJLEC where FE_ID=Old.OS_FEID;
          update FEJLEC set FE_KIEGY2=FE_KIEGY2-Old.OS_12, FE_KESES=0,FE_OSMOD=0, FE_US_NEV='REBUILD' where FE_DEV>' ' and FE_EV=OLD.OS_EV and FE_ID=OLD.OS_FE_ID_A;
        end
        update FEJLEC set FE_KIEGY2=FE_KIEGY2-F_ROUNDBY(Old.OS_SUMA*FE_DEVKURZ,2,2)+Old.OS_12, FE_KIEGY=FE_KIEGY-OLD.OS_SUMA, FE_KESES=0,FE_OSMOD=0, FE_US_NEV='REBUILD' where FE_DEV>' ' and FE_EV=OLD.OS_EV and FE_ID=OLD.OS_FE_ID_A;
        update FEJLEC set FE_KIEGY2=FE_KIEGY2-F_ROUNDBY(Old.OS_SUMA*FE_DEVKURZ,2,2),FE_KIEGY=FE_KIEGY-OLD.OS_SUMA, FE_KESES=0,FE_OSMOD=0, FE_US_NEV='REBUILD' where FE_DEV>' ' and FE_EV=OLD.OS_EV and FE_ID=OLD.OS_FE_ID_B;
      end
    end else begin
      update FEJLEC set FE_KIEGY2=FE_KIEGY2-Old.OS_SUMA, FE_KIEGY=FE_KIEGY-OLD.OS_SUMA, FE_KESES=0,FE_OSMOD=0, FE_US_NEV='REBUILD' where FE_EV=OLD.OS_EV and (FE_ID=OLD.OS_FE_ID_A or FE_ID=OLD.OS_FE_ID_B);
    end
  end else begin
    SUMA=Old.OS_SUMA;
    if (Old.OS_SUMA<0) then SUMA=-Old.OS_SUMA;
    update FEJLEC set FE_KIEGY2=FE_KIEGY2-Old.OS_SUMA, FE_KESES=0,FE_OSMOD=0, FE_US_NEV='REBUILD' where FE_EV=OLD.OS_EV and FE_ID=OLD.OS_FE_ID_A ;
    update FEJLEC set FE_KIEGY2=FE_KIEGY2-:SUMA, FE_KIEGY=FE_KIEGY-:SUMA, FE_KESES=0, FE_OSMOD=0, FE_US_NEV='REBUILD' where FE_EV=OLD.OS_EV and FE_ID=Old.OS_FE_ID_B;
  end
END^

drop TRIGGER FEJLEC_BU^
CREATE TRIGGER FEJLEC_BU FOR FEJLEC BEFORE UPDATE POSITION 0 AS
 DECLARE VARIABLE A integer;
 DECLARE VARIABLE T date;
BEGIN
  if (NEW.FE_US_NEV='REBUILD') then begin
     NEW.FE_REBUILD=9;
     NEW.FE_US_NEV=OLD.FE_US_NEV;
     New.FE_TIME=OLD.FE_TIME;
  end else begin
     NEW.FE_REBUILD=0;
  end
  if (NEW.FE_REBUILD=0) then begin
    if ((New.FE_AFATOTAL<>OLD.FE_AFATOTAL) and (Old.FE_AZ_ID>0)) then begin
      EXCEPTION EAFA;
    end
    if ((New.FE_TOTAL<>OLD.FE_TOTAL) or (New.FE_DEV<>Old.FE_DEV) or (New.FE_DEVTOTAL<>OLD.FE_DEVTOTAL) or (New.FE_DEVKURZ<>Old.FE_DEVKURZ) ) then begin
      A=0;
      select COUNT(*) from OSSZEPONT where OS_FE_ID_A=New.FE_ID or OS_FE_ID_B=New.FE_ID or OS_FEID=New.FE_ID into :A ;
      if (A>0) then begin
        EXCEPTION ETOTAL;
      end
    end
    if ((NEW.FE_MO_CHAR='B') or
        (NEW.FE_MO_CHAR='P') or
        (NEW.FE_MO_CHAR='N') or
        (NEW.FE_MO_CHAR='X'))
    then begin
       NEW.FE_TELJESIT=NEW.FE_KELTE;
       NEW.FE_HATARIDO=NEW.FE_KELTE;
    end
    if ((NEW.FE_MO_CHAR='N') and (New.FE_STATUS='N')) then New.FE_STATUS='R';
    if (NEW.FE_US_NEV='TRSYS') then begin
      New.FE_TIME=OLD.FE_TIME;
      NEW.FE_US_NEV=Old.FE_US_NEV;
    end else begin
      New.FE_TIME='now';
    end
    if (New.FE_KIEGY is NULL) then New.FE_KIEGY=0;
    if (New.FE_KESES is NULL) then New.FE_KESES=0;
    if (New.FE_GY_NEV is NULL) then New.FE_GY_NEV='';
    New.FE_GY_NEV=F_TRIM(New.FE_GY_NEV);
    if (New.FE_DEV>' ') then begin
      New.FE_SUMAR=New.FE_DEVTOTAL;
    end else begin
      New.FE_SUMAR=New.FE_TOTAL;
    end
  end
END^

drop trigger FEJLEC_AU^

CREATE TRIGGER FEJLEC_AU FOR FEJLEC
  AFTER UPDATE POSITION 0 AS
 DECLARE VARIABLE A integer;
begin
  if (New.FE_REBUILD=0) then begin
    delete from CONTIR where CO_FE_ID=Old.FE_ID and CO_TE_ID=0;
    A=GEN_ID(CO,1);
    insert into CONTIR (CO_EV,CO_ID,CO_FE_ID,CO_MO_CHAR,CO_FE_NAPLO,CO_TE_ID,CO_FO_KARTON,CO_IRANY,CO_OSSZEG,CO_STATUS,CO_US_NEV,CO_TIME,CO_GY_NEV, CO_TELJESIT,CO_HATARIDO,CO_KELTE,CO_UG_ID,CO_AFAALAP,CO_AF_NEV,CO_AZ_ID,CO_ERR)
     VALUES (NEW.FE_EV,:A, New.FE_ID, NEW.FE_MO_CHAR,NEW.FE_NAPLO,0,NEW.FE_KARTON,NEW.FE_IRANY, NEW.FE_TOTAL,NEW.FE_STATUS,NEW.FE_US_NEV,'now',NEW.FE_GY_NEV,NEW.FE_TELJESIT,NEW.FE_HATARIDO,NEW.FE_KELTE,NEW.FE_UG_ID,0,'',New.FE_AZ_ID,New.FE_ERR);
    if ((New.FE_TELJESIT<>Old.FE_TELJESIT) or (New.FE_HATARIDO<>Old.FE_HATARIDO)) then begin
      update OSSZEPONT set OS_KESES=0 where OS_FE_ID_A=New.FE_ID or OS_FE_ID_B=New.FE_ID;
    end
    if (NEW.FE_STATUS<>OLD.FE_STATUS)  then begin
      update TETEL set TE_FE_STATUS=NEW.FE_STATUS, TE_US_NEV='TRSYS' where TE_FE_ID=New.FE_ID ;
    end
    if ((NEW.FE_STATUS<>OLD.FE_STATUS) or (NEW.FE_TELJESIT<>OLD.FE_TELJESIT) or (New.FE_KELTE<>Old.FE_KELTE) or (NEW.FE_HATARIDO<>OLD.FE_HATARIDO))   then begin
      update CONTIR set CO_STATUS=NEW.FE_STATUS,CO_TELJESIT=NEW.FE_TELJESIT,CO_HATARIDO=NEW.FE_HATARIDO, CO_KELTE=NEW.FE_KELTE, CO_US_NEV='TRSYS' where CO_FE_ID=New.FE_ID and CO_ID<>:A ;
    end
    if ((OLD.FE_NAPLO<>New.FE_NAPLO) or (Old.FE_MO_CHAR<>New.FE_MO_CHAR)) then begin
      update NAPLO set NA_RECNO=NA_RECNO-1 where NA_NO=Old.FE_NAPLO and NA_EV=OLD.FE_EV and NA_TYP=Old.FE_MO_CHAR;
      update NAPLO set NA_RECNO=NA_RECNO+1 where NA_NO=New.FE_NAPLO and NA_EV=New.FE_EV and NA_TYP=New.FE_MO_CHAR;
      if (ROW_COUNT<=0) then begin
        insert into NAPLO (NA_TYP,NA_EV,NA_NO,NA_T,NA_K,NA_E,NA_OWNER,NA_FE_ID,NA_TIME,NA_RECNO) VALUES (New.FE_MO_CHAR,New.FE_EV,New.FE_NAPLO,0,0,0,New.FE_US_NEV,New.FE_ID,'now',1);
      end
    end
  end
END^

/**DB0604*************************/

drop trigger OSSZEPONT_AI^

CREATE TRIGGER OSSZEPONT_AI FOR OSSZEPONT
  AFTER INSERT POSITION 0 AS
DECLARE VARIABLE A INTEGER;
DECLARE VARIABLE UGID INTEGER;
DECLARE VARIABLE UGKAR VARCHAR(16);
DECLARE VARIABLE UGTYP CHAR(1);
DECLARE VARIABLE UGTYP2 CHAR(1);
DECLARE VARIABLE IR CHAR(1);
DECLARE VARIABLE S CHAR(1);
DECLARE VARIABLE J CHAR(1);
DECLARE VARIABLE B VARCHAR(16);
DECLARE VARIABLE BB VARCHAR(16);
DECLARE VARIABLE CC1 VARCHAR(180);
DECLARE VARIABLE CC2 VARCHAR(180);
DECLARE VARIABLE H VARCHAR(16);
DECLARE VARIABLE KELT  TIMESTAMP;
DECLARE VARIABLE ELLEN VARCHAR(16);
DECLARE VARIABLE N INTEGER;
DECLARE VARIABLE NN INTEGER;
DECLARE VARIABLE SUMA NUMERIC(16,2);
BEGIN
  if (NEW.OS_SUMA<>0) then begin
    if (New.OS_DEV<>'+') then begin
      if (New.OS_MOD=1) then begin
        update FEJLEC set FE_KIEGY2=FE_KIEGY2+F_ROUNDBY(New.OS_SUMA*FE_DEVKURZ,2,2), FE_KIEGY=FE_KIEGY+NEW.OS_SUMA,FE_KESES=0, FE_OSMOD=New.OS_MOD, FE_US_NEV='REBUILD' where FE_EV=NEW.OS_EV and (FE_ID=NEW.OS_FE_ID_A or FE_ID=NEW.OS_FE_ID_B) ;
      end else begin
        if (New.OS_DEV>' ') then begin
          update FEJLEC set FE_KIEGY2=FE_KIEGY2+F_ROUNDBY(New.OS_SUMA*FE_DEVKURZ,2,2)-New.OS_12,FE_KIEGY=FE_KIEGY+NEW.OS_SUMA,FE_KESES=New.OS_KESES, FE_OSMOD=New.OS_MOD, FE_US_NEV='REBUILD' where FE_EV=NEW.OS_EV and FE_ID=NEW.OS_FE_ID_A;
          update FEJLEC set FE_KIEGY2=FE_KIEGY2+F_ROUNDBY(New.OS_SUMA*FE_DEVKURZ,2,2),FE_KIEGY=FE_KIEGY+NEW.OS_SUMA,FE_KESES=New.OS_KESES, FE_OSMOD=New.OS_MOD, FE_US_NEV='REBUILD' where FE_EV=NEW.OS_EV and FE_ID=NEW.OS_FE_ID_B;
        end else begin
          update FEJLEC set FE_KIEGY2=FE_KIEGY2+NEW.OS_SUMA,FE_KIEGY=FE_KIEGY+NEW.OS_SUMA,FE_KESES=New.OS_KESES, FE_OSMOD=New.OS_MOD, FE_US_NEV='REBUILD' where FE_EV=NEW.OS_EV and (FE_ID=NEW.OS_FE_ID_A or FE_ID=NEW.OS_FE_ID_B) ;
        end
      end
      if ((New.OS_FEID>0) and (New.OS_SUMA>0)) then begin
        select FE_KARTON, FE_MO_CHAR,FE_BIZONYLAT,FE_NAPLO from FEJLEC where FE_ID=New.OS_FE_ID_A and FE_EV=New.OS_EV into :UGKAR,:UGTYP,:BB,:N ;
        select FE_MO_CHAR, FE_IRANY,FE_STATUS,FE_BIZONYLAT,FE_HIVATKOZ,FE_UG_ID,FE_KELTE FROM FEJLEC where FE_ID=New.OS_FE_ID_B and FE_EV=New.OS_EV into :UGTYP2,:IR,:S,:B,:H,:UGID,:KELT ;
        SUMA=New.OS_12;
        ELLEN='';
        If (SUMA>0) then begin
          select FO_KARTON from FOKONYV where FO_TYP='1' and FO_COMPLET='Y' into :ELLEN ;
        end else begin
          select FO_KARTON from FOKONYV where FO_TYP='2' and FO_COMPLET='Y' into :ELLEN ;
        end
        if ((ELLEN is NULL) or (ELLEN='')) then begin
          EXCEPTION EOSK;
        end else begin
          J='S';
          if (SUMA>0) then begin
            IR='T';
            if (UGTYP='S') then J='J';
            /* nyereseg 1  mindig kovetel oldalra K-976 */
          end else begin
            IR='K';
            if (UGTYP='V') then J='J';
            /* veszteseg 2  mindig tartozik oldalra T-876 */
            SUMA=-SUMA;
          end
          NN=N;
          /*
          select NA_NO from NAPLO where NA_TYP=:UGTYP and NA_OWNER=New.OS_US_NEV and NA_EV=New.OS_EV  order by NA_NO DESC into :N ;
          if ((N is NULL) or (N<1)) then begin
            N=NN;
            select MAX(NA_NO)+1 from NAPLO where NA_TYP=:UGTYP and NA_EV=New.OS_EV into :N ;
            insert into NAPLO (NA_TYP,NA_EV,NA_NO,NA_T,NA_K,NA_E,NA_OWNER,NA_FE_ID,NA_TIME,NA_RECNO) VALUES (:UGTYP, New.OS_EV,:N,0,0,0,New.OS_US_NEV,New.OS_FEID,'now',0);
            select NA_NO from NAPLO where NA_TYP=:UGTYP and NA_OWNER=New.OS_US_NEV and NA_EV=New.OS_EV  order by NA_NO DESC into :N ;
            if ((N is NULL) or (N<1)) then N=NN;
          end
          */
          CC1= UGTYP || BB || ':' || UGTYP2 || B || ' árfolyam különbözet';
          CC2= UGTYP || BB || ':' || UGTYP2 || B || ' dif.course';
          insert INTO FEJLEC (FE_NAPLO, FE_MO_CHAR, FE_ID, FE_EV, FE_US_NEV, FE_IRANY, FE_KARTON, FE_BIZONYLAT,FE_HIVATKOZ,FE_UG_ID,FE_KELTE,FE_TOTAL,FE_MEGJEGY,FE_JELLEG,FE_OSMOD2,FE_DEVKURZ,FE_DEV,FE_KIEGY,FE_KIEGY2,FE_TELJESIT,FE_HATARIDO) VALUES
                             (:N, :UGTYP, New.OS_FEID ,New.OS_EV, New.OS_US_NEV, :IR,:UGKAR,:BB,:H,:UGID,:KELT,:SUMA,:CC1,:J,1,0,New.OS_DEV,0,:SUMA,:KELT,:KELT);
          insert INTO TETEL  (TE_ID,TE_FE_ID,TE_FE_IRANY, TE_ELLEN, TE_SUM,TE_US_NEV,TE_EV,TE_MEGJEGY,TE_MEGJEGY_E,TE_MEGJEGY_D) VALUES
                             (GEN_ID(TE,1),New.OS_FEID,:IR, :ELLEN, :SUMA, New.OS_US_NEV, New.OS_EV,:CC1,:CC2,:CC2);
          update FEJLEC set FE_KIEGY2=FE_KIEGY2+New.OS_12, FE_US_NEV='REBUILD' where FE_EV=NEW.OS_EV and FE_ID=NEW.OS_FE_ID_A;
        end
      end
    end else begin
      SUMA=New.OS_SUMA;
      if (New.OS_SUMA<0) then SUMA=-New.OS_SUMA;
      update FEJLEC set FE_KIEGY2=FE_KIEGY2+NEW.OS_SUMA, FE_KESES=New.OS_KESES, FE_OSMOD=New.OS_MOD, FE_US_NEV='REBUILD' where FE_EV=NEW.OS_EV and FE_ID=NEW.OS_FE_ID_A;
      update FEJLEC set FE_KIEGY2=FE_KIEGY2+:SUMA, FE_KIEGY=FE_KIEGY+:SUMA, FE_KESES=New.OS_KESES, FE_OSMOD=New.OS_MOD, FE_US_NEV='REBUILD' where FE_EV=NEW.OS_EV and FE_ID=NEW.OS_FE_ID_B;
    end
  end
END^


/**DB0706***********************************/

drop procedure FE_OS^

CREATE PROCEDURE FE_OS (ID integer)
RETURNS ( FE INTEGER, OS INTEGER, S NUMERIC(16,2) ,KES INTEGER, DEV VARCHAR(3))
AS
DECLARE VARIABLE A INTEGER;
DECLARE VARIABLE B INTEGER;
DECLARE VARIABLE D varchar(3);
DECLARE VARIABLE FEID INTEGER;
DECLARE VARIABLE OS12 NUMERIC(16,2);
BEGIN
  For select OS_SUMA,OS_FE_ID_A,OS_FE_ID_B,OS_ID, OS_KESES, OS_DEV, OS_FEID,OS_12 from OSSZEPONT where OS_FE_ID_A=:ID or OS_FE_ID_B=:ID or OS_FEID=:ID into :S, :A, :B, :OS, :KES, :DEV, :FEID, :OS12
  DO
  begin
    if (A=ID) then begin
      FE=B;
      suspend;
      if ((FEID>0) and (OS12>0)) then begin
        FE=-FEID; DEV='+'; S=OS12; KES=0;
        suspend;
      end
    end
    if (B=ID) then begin
      FE=A;
      suspend;
      if ((FEID>0) and (OS12>0)) then begin
        FE=-FEID; DEV='+'; S=OS12; KES=0;
        suspend;
      end
    end
    if ((FEID=ID) and (OS12>0)) then begin
      FE=-B; DEV='+'; S=OS12; KES=0;
      suspend;
    end
  end
END^

/**DB1001************************/

drop trigger TETEL_BI^

CREATE TRIGGER TETEL_BI FOR TETEL BEFORE INSERT POSITION 0 AS
BEGIN
  New.TE_TIME='now';
  if (New.TE_ELLEN is null) then begin
    NEW.TE_ELLEN='';
  end
  if (New.TE_AFASZAMLA is null) then begin
    NEW.TE_AFASZAMLA='';
  end
  if (New.TE_ATVEZ1 is null) then begin
    NEW.TE_ATVEZ1='';
  end
  if (New.TE_ATVEZ2 is null) then begin
    NEW.TE_ATVEZ2='';
  end
END^

drop PROCEDURE FO_REBUILD^

CREATE PROCEDURE FO_REBUILD(EV double precision)
AS
DECLARE VARIABLE KA VARCHAR(16);
DECLARE VARIABLE S double precision;
DECLARE VARIABLE A integer;
DECLARE VARIABLE T double precision;
DECLARE VARIABLE K double precision;
DECLARE VARIABLE E double precision;
DECLARE VARIABLE C integer;
DECLARE VARIABLE UG integer;
DECLARE VARIABLE NA integer;
DECLARE VARIABLE NAT CHAR(1);
DECLARE VARIABLE M1 CHAR(1);
DECLARE VARIABLE M2 CHAR(1);
BEGIN

  delete FROM FEJLEC where FE_EV=:EV and FE_UG_ID not in (select UG_ID from UGYFEL where UG_EV=:EV);
  For select FE_ID from FEJLEC where FE_EV=:EV and FE_DEVTOTAL>=0.01 and FE_TOTAL>=0.01 and F_ABS(F_ROUNDBY(FE_DEVTOTAL*FE_DEVKURZ,2,2)-FE_TOTAL)>=0.01 into :C do
  begin
    update FEJLEC set FE_DEVKURZ= cast (FE_TOTAL as numeric(16,6)) / cast (FE_DEVTOTAL as numeric(16,6)) , FE_US_NEV='REBUILD' where FE_EV=:EV and FE_ID=:C;
  end
  For select FE_ID from FEJLEC where FE_OSMOD2=0 and FE_EV=:EV and (FE_KIEGY<>0 or FE_KIEGY2<>0) and
     (FE_ID not in (select OS_FE_ID_A from OSSZEPONT where OS_EV=:EV)) and
     (FE_ID not in (select OS_FE_ID_B from OSSZEPONT where OS_EV=:EV)) into :C do
  begin
    update FEJLEC set FE_KIEGY=0, FE_KIEGY2=0, FE_US_NEV='REBUILD', FE_OSMOD=0, FE_OSMOD2=0 where FE_EV=:EV and FE_ID=:C ;
  end

  update FEJLEC set FE_KESES=0, FE_KIEGY=0, FE_SUMAR=0, FE_KIEGY2=FE_TOTAL, FE_US_NEV='REBUILD'
    where FE_OSMOD2=1 and (FE_KIEGY2<>FE_TOTAL) and FE_EV=:EV;

  update FEJLEC set FE_KESES=0, FE_KIEGY=0, FE_SUMAR=0, FE_KIEGY2=FE_TOTAL, FE_OSMOD2=1, FE_DEVTOTAL=0, FE_OSMOD=0, FE_US_NEV='REBUILD'
    where FE_KIEGY2<>FE_TOTAL and (FE_ID in (select OS_FEID from OSSZEPONT where OS_FEID>0 and OS_12<>0 and OS_EV=:EV));

  update TETEL set TE_US_NEV='TRSYS',TE_AFASZAMLA='', TE_ATVEZ1='', TE_ATVEZ2=''
    where TE_FE_ID in (select FE_ID from FEJLEC where  FE_OSMOD2=1 and FE_EV=:EV);

  FOR select FO_KARTON from FOKONYV where FO_COMPLET='Y' and FO_EV=:EV order by FO_KARTON INTO :KA do
  begin
    select SUM(CO_K) K ,SUM(CO_T) T From CONTIR where CO_FO_KARTON=:KA and CO_EV=:EV and CO_STATUS='N' and CO_MO_CHAR<>'N' into :K, :T ;
    if (T is NULL) then T=0;
    if (K is NULL) then K=0;
    update FOKONYV set FO_NT=:T, FO_NK=:K, FO_US_NEV='TRSYS' where FO_KARTON=:KA and FO_EV=:EV;

    select SUM(CO_K) K ,SUM(CO_T) T From CONTIR where CO_FO_KARTON=:KA and CO_EV=:EV and CO_STATUS<>'N' and CO_STATUS<>'E' into :K, :T ;
    if (T is NULL) then T=0;
    if (K is NULL) then K=0;
    update FOKONYV set FO_RT=:T, FO_RK=:K, FO_US_NEV='TRSYS' where FO_KARTON=:KA and FO_EV=:EV;

    select SUM(CO_K) K ,SUM(CO_T) T From CONTIR where CO_FO_KARTON=:KA and CO_EV=:EV and CO_MO_CHAR='N' and CO_STATUS<>'E' into :K, :T ;
    if (T is NULL) then T=0;
    if (K is NULL) then K=0;
    update FOKONYV set FO_T=:T, FO_K=:K, FO_US_NEV='TRSYS' where FO_KARTON=:KA and FO_EV=:EV;
  end

  FOR select UG_ID,UG_KARTON,UG_MOD from UGYFEL where UG_ID>0 and UG_EV=:EV order by UG_ID INTO :UG, :KA, :M1 do
  begin
    select SUM(CO_K) K ,SUM(CO_T) T From CONTIR,FEJLEC  where CO_FE_ID=FE_ID and FE_UG_ID=:UG and CO_FO_KARTON=:KA and CO_EV=:EV and FE_EV=:EV and CO_STATUS='N' into :K, :T ;
    if (T is NULL) then T=0;
    if (K is NULL) then K=0;
    update UGYFEL set UG_NT=:T, UG_NK=:K, UG_US_NEV='TRSYS' where UG_ID=:UG and UG_EV=:EV and UG_KARTON=:KA;

    select SUM(CO_K) K ,SUM(CO_T) T From CONTIR,FEJLEC  where CO_FE_ID=FE_ID and FE_UG_ID=:UG and CO_FO_KARTON=:KA and CO_EV=:EV and FE_EV=:EV and CO_STATUS<>'N' into :K, :T ;
    if (T is NULL) then T=0;
    if (K is NULL) then K=0;
    update UGYFEL set UG_RT=:T, UG_RK=:K, UG_US_NEV='TRSYS' where UG_ID=:UG and UG_EV=:EV and UG_KARTON=:KA;

    select SUM(CO_K) K ,SUM(CO_T) T From CONTIR,FEJLEC  where CO_FE_ID=FE_ID and FE_UG_ID=:UG and CO_FO_KARTON=:KA and CO_EV=:EV and FE_EV=:EV and CO_STATUS='E' into :K, :T ;
    if (T is NULL) then T=0;
    if (K is NULL) then K=0;
    update UGYFEL set UG_T=:T, UG_K=:K, UG_US_NEV='TRSYS' where UG_ID=:UG and UG_EV=:EV and UG_KARTON=:KA;

    select FO_TYP from FOKONYV where FO_EV=:EV and FO_KARTON=:KA into :M2 ;
    if ((M2 is not null) and (M2>' ') and (M1<>M2)) then begin
      if ((M2='S') or (M2='s') or (M2='v') or (M2='V') or (M2='P') or (M2='p')) then begin
        update UGYFEL set UG_MOD=:M2, UG_US_NEV='TRSYS' where UG_ID=:UG and UG_EV=:EV;
      end
    end
  end

  FOR select NA_NO,NA_TYP from NAPLO where NA_NO>0 and NA_EV=:EV order by NA_TYP,NA_NO INTO :NA, :NAT do
  begin
    select COUNT(*) C  From FEJLEC  where FE_NAPLO=:NA and FE_EV=:EV and FE_MO_CHAR=:NAT into :C ;
    select SUM(CO_K) K ,SUM(CO_T) T From CONTIR  where CO_FE_NAPLO=:NA and CO_EV=:EV and CO_MO_CHAR=:NAT into :K, :T ;
    if (C is NULL) then C=0;
    if (T is NULL) then T=0;
    if (K is NULL) then K=0;
    E=T-K;
    update NAPLO set NA_T=:T, NA_K=:K, NA_E=:E, NA_RECNO=:C where NA_TYP=:NAT and NA_NO=:NA and NA_EV=:EV;
  end

  EXECUTE PROCEDURE START;

END^


DELETE from VERSION^
INSERT INTO VERSION (VE_NUM) VALUES ('1001')^

EXECUTE PROCEDURE FEID_NAPLO^
EXECUTE PROCEDURE FO_REBUILD(2005)^





