/*ALIAS>prop_db*/

/*MANYStart>1660*/
/*Mahame kakvoto trqbva za da smenim tipyt na razni poleta*/
drop trigger set_per_id;
drop trigger persons_bu;
drop procedure personadd;
drop procedure firmadd;
drop procedure personupdate;
drop procedure firmupdate;
drop procedure invoiceaddheader;
/*MANYStop>1660*/

/*SQLStart>1661*/
drop procedure persongetinfo;
/*SQLStop>1661*/

/*MANYStart>1662*/
/* Smenqme im tipyt */
alter table persons alter per_clearname type varchar(140);
alter table persons alter per_name type varchar(140);
alter table persons alter per_telefon type varchar(40);
alter table invoices alter in_pername type varchar(140);
alter table invoices alter in_permolname type varchar(140);
alter table documents alter doc_addtext type varchar(600);
alter table imot alter im_address type varchar(250);
/*MANYStop>1662*/


/* Veche vryshtame trigerite i procedurite*/
/*SQLStart>1663*/
CREATE OR ALTER TRIGGER SET_PER_ID FOR PERSONS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW.Per_ID=GEN_ID(Per_ID_GEN,1);
  New.Per_Name = ATrimEx(New.Per_Name);
  New.Per_CLEARNAME = ACLearStr(New.Per_Name);
  New.Per_CLEAREGN = ACLearStr(New.Per_EGN);
END
/*SQLStop>1663*/


/*SQLStart>1664*/
CREATE OR ALTER TRIGGER PERSONS_BU FOR PERSONS
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
  New.Per_Name = ATrimEx(New.Per_Name);
  New.Per_CLEARNAME = ACLearStr(New.Per_Name);
  New.Per_CLEAREGN = ACLearStr(New.Per_EGN);
END
/*SQLStop>1664*/

/*SQLStart>1665*/
CREATE OR ALTER procedure PERSONADD (
    PERNAME varchar(140),
    PERTYPE smallint,
    PEREGN varchar(15),
    PERPDATA varchar(15),
    PEREMAIL varchar(50),
    PERCOMENT varchar(150),
    PERNM varchar(30),
    PERADDR varchar(250),
    PERTELEFON varchar(25),
    PERFALGPASS char(1),
    PEROBSHTINA varchar(25),
    PEROBLAST varchar(25),
    PERFAX varchar(25),
    PERPK varchar(5)) 
returns (
    PERID integer,
    RESULT char(1)) 
as
BEGIN
/* proc-a wrashta PerID na wece waweden person ili na insertnatiq, 
   Result = F -> znachi wece go e imalo
   Result = N -> znachi ei sega e bil insertnat
*/
  if ((PerEGN is null) or (PerEGN = ""))then
  begin
    select Per_ID
      from persons
      where 
        Per_Flag = 'T' and
        AClearStr(Per_Name) = AClearStr(:PerName) and
        AClearStr(Per_NM) = AClearStr(:PerNM)
      into PerID;
  end
  else
  begin
    select Per_ID
      from persons
      where 
        Per_Flag = 'T' and
        Per_EGN = :PerEGN
      into PerID;
  end
  RESULT = 'F';
  if (:PerID is null) then
  begin
    insert into Persons(PER_NAME, PER_TYPE, PER_EGN, PER_PDATA, PER_EMAIL,
                        PER_COMENT, PER_NM, PER_ADDR, PER_TELEFON,PER_FlagPass,
                        PER_OBSHTINA, PER_OBLAST, PER_FAX, PER_FLAG, PER_PK)
    values (:PERNAME, :PERTYPE, :PEREGN, :PERPDATA, :PEREMAIL,
                        :PERCOMENT, :PERNM, :PERADDR, :PERTELEFON, :PERFALGPASS,
                        :PEROBSHTINA, :PEROBLAST, :PERFAX, 'T', :PERPK);
    PerID = GEN_ID(PER_ID_GEN, 0);
    RESULT = 'T';
  end
  suspend;
END
/*SQLStop>1665*/


/*SQLStart>1666*/
CREATE OR ALTER procedure FIRMADD (
    PERNAME varchar(140),
    PERTYPE smallint,
    PEREGN varchar(15),
    PEREMAIL varchar(50),
    PERCOMENT varchar(150),
    PERNM varchar(30),
    PERADDR varchar(250),
    PERTELEFON varchar(25),
    PERDANN varchar(10),
    PERSAD varchar(40),
    PERFIRMDELO varchar(40),
    PERDATEREG date,
    PEROBSHTINA varchar(25),
    PEROBLAST varchar(25),
    PERFAX varchar(25),
    PERMOL integer,
    PERPK varchar(5),
    PERINDDS varchar(15),
    PERADDRCORESP varchar(250),
    PERTDNAP varchar(20),
    PERTDNAPADDR varchar(50)) 
returns (
    PERID integer,
    RESULT char(1)) 
as
begin
/* proc-a wrashta PerID na wece waweden person ili na insertnatiq, 
   Result = F -> znachi wece go e imalo
   Result = N -> znachi ei sega e bil insertnat
*/
  select per_id 
    from persons
    where 
      Per_Flag = 'F' and 
      Per_EGN = :PerEGN
    into PerID;
  RESULT = 'F'; 
  if (PerID is null) then
  begin
    insert into persons(PER_NAME, PER_TYPE, PER_EGN, PER_EMAIL,
                        PER_COMENT, PER_NM, PER_ADDR, PER_TELEFON,
                        PER_DANN, PER_SAD, PER_FIRMDELO, PER_DATEREG,
                        PER_OBSHTINA, PER_OBLAST, PER_FAX, PER_MOL, PER_FLAG, PER_PK, PER_INDDS, 
                        PER_ADDR_CORESP, PER_TDNAP, PER_TDNAP_ADDR)
    values (:PERNAME, :PERTYPE, :PEREGN, :PEREMAIL,
                        :PERCOMENT, :PERNM, :PERADDR, :PERTELEFON,
                        :PERDANN, :PERSAD, :PERFIRMDELO, :PERDATEREG,
                        :PEROBSHTINA, :PEROBLAST, :PERFAX, :PERMOL, 'F', :PERPK, :PERINDDS,
                        :PERADDRCORESP, :PERTDNAP, :PERTDNAPADDR);
    PerID = GEN_ID(PER_ID_GEN, 0);
    RESULT = 'N';
  end
  suspend; 
end
/*SQLStop>1666*/


/*SQLStart>1667*/
CREATE OR ALTER procedure PERSONUPDATE (
    PERID integer,
    PERNAME varchar(140),
    PERTYPE smallint,
    PEREGN varchar(15),
    PERPDATA varchar(15),
    PEREMAIL varchar(50),
    PERCOMENT varchar(150),
    PERNM varchar(30),
    PERADDR varchar(250),
    PERTELEFON varchar(25),
    PERFLAGPASS char(1),
    PEROBSHTINA varchar(16),
    PEROBLAST varchar(15),
    PERFAX varchar(25)) 
returns (
    RETVAL integer) 
as
BEGIN
  if ((PerEGN is null) or (PerEGN = ""))then
  begin
    select count(Per_ID)
      from persons
      where 
        Per_Flag = 'T' and
        AClearStr(Per_Name) = AClearStr(:PerName) and
        Per_ID <> :PERID
      into RETVAL;
  end
  else
  begin
    select count(Per_ID)
      from persons
      where 
        Per_Flag = 'T' and
        Per_EGN = :PerEGN and
        Per_ID <> :PERID
      into RETVAL;
  end
  if (:RETVAL = 0) then
  begin
    update persons
      set PER_NAME = :PERNAME,
          PER_TYPE = :PERTYPE, 
          PER_EGN =  :PEREGN,
          PER_PDATA = :PERPDATA,
          PER_EMAIL = :PEREMAIL,
          PER_COMENT = :PERCOMENT,
          PER_NM = :PERNM,
          PER_ADDR = :PERADDR,
          PER_TELEFON = :PERTELEFON,
          PER_FLAGPASS = :PERFLAGPASS,
          PER_OBSHTINA = :PEROBSHTINA, 
          PER_OBLAST = :PEROBLAST,
          PER_FAX = :PERFAX
       where
         PER_ID = :PERID;

    RETVAL = 0;
  end
  suspend;
END
/*SQLStop>1667*/

/*SQLStart>1668*/
CREATE OR ALTER procedure FIRMUPDATE (
    PERID integer,
    PERNAME varchar(140),
    PERTYPE smallint,
    PEREGN varchar(15),
    PEREMAIL varchar(50),
    PERCOMENT varchar(150),
    PERNM varchar(30),
    PERADDR varchar(250),
    PERTELEFON varchar(25),
    PERDANN varchar(10),
    PERSAD varchar(40),
    PERFIRMDELO varchar(40),
    PERDATEREG date,
    PEROBSHTINA varchar(16),
    PEROBLAST varchar(15),
    PERFAX varchar(25),
    PERMOL integer) 
returns (
    RETVAL integer) 
as
begin
  select count(per_id)
    from persons
    where 
      Per_Flag = 'F' and 
      Per_EGN = :PerEGN and
      Per_ID <> :PERID
    into RETVAL;
  if (RETVAL = 0) then
  begin
    update persons
      set PER_NAME = :PERNAME,
          PER_TYPE = :PERTYPE, 
          PER_EGN =  :PEREGN,
          PER_EMAIL = :PEREMAIL,
          PER_COMENT = :PERCOMENT,
          PER_NM = :PERNM,
          PER_ADDR = :PERADDR,
          PER_TELEFON = :PERTELEFON,
          PER_DANN = :PERDANN,
          PER_SAD = :PERSAD,
          PER_FIRMDELO = :PERFIRMDELO,
          PER_DATEREG = :PERDATEREG,
          PER_OBSHTINA = :PEROBSHTINA, 
          PER_OBLAST = :PEROBLAST,
          PER_FAX = :PERFAX,
          PER_MOL = :PERMOL
      where
          PER_ID = :PERID;
  end
  suspend; 
end
/*SQLStop>1668*/

/*SQLStart>1669*/
CREATE OR ALTER procedure PERSONGETINFO (
    PERID integer) 
returns (
    PERNAME varchar(140),
    PEREGN varchar(15),
    PERDANN varchar(10),
    PERADDR varchar(250),
    PERNM varchar(30),
    PERMOLNAME varchar(140),
    PERFLAG char(1),
    PERINDDS varchar(15),
    PERADDR_CORESP varchar(250),
    PERTDNAP varchar(20),
    PERTDNAP_ADDR varchar(50)) 
as
BEGIN
  SELECT  p31.per_name,
          p31.per_egn,
          p31.per_nm ,
          p31.per_addr,
          p31.per_dann,
          p31.per_flag,
          p31.per_indds,
          p311.per_name as Per_MolName,
          p31.per_addr_coresp,
          p31.per_tdnap,
          p31.per_tdnap_addr
  FROM  persons p31 LEFT OUTER JOIN persons p311 ON p311.per_id=p31.per_mol
  Where p31.per_id = :PerID
  into :PerName, :PerEGN, :PerNM, :PerAddr, :PerDanN, :PerFlag, :PerIndds, :PerMolName, :PERADDR_CORESP, :PERTDNAP, :PERTDNAP_ADDR;
  suspend;
END
/*SQLStop>1669*/

/*SQLStart>1670*/
create or alter procedure PERSONGETINFO (
    PERID integer) 
returns (
    PERNAME varchar(140),
    PEREGN varchar(15),
    PERDANN varchar(10),
    PERADDR varchar(250),
    PERNM varchar(30),
    PERMOLNAME varchar(140),
    PERFLAG char(1),
    PERINDDS varchar(15),
    PERADDR_CORESP varchar(250),
    PERTDNAP varchar(20),
    PERTDNAP_ADDR varchar(50)) 
as
BEGIN
  SELECT  p31.per_name,
          p31.per_egn,
          p31.per_nm ,
          p31.per_addr,
          p31.per_dann,
          p31.per_flag,
          p31.per_indds,
          p311.per_name as Per_MolName,
          p31.per_addr_coresp,
          p31.per_tdnap,
          p31.per_tdnap_addr
  FROM  persons p31 LEFT OUTER JOIN persons p311 ON p311.per_id=p31.per_mol
  Where p31.per_id = :PerID
  into :PerName, :PerEGN, :PerNM, :PerAddr, :PerDanN, :PerFlag, :PerIndds, :PerMolName, :PERADDR_CORESP, :PERTDNAP, :PERTDNAP_ADDR;
  suspend;
END
/*SQLStop>1670*/

/*SQLStart>1671*/
CREATE OR ALTER procedure INVOICEADDHEADER (
    IN_PERID integer,
    IN_PAYID integer,
    IN_PAYTYPE char(1),
    IN_PAYSUM numeric(15,2),
    IN_PAYCLEARSUM numeric(15,2),
    IN_PAYDDS numeric(15,2),
    IN_PAYLIHVI numeric(15,2),
    IN_PAYWAY char(1),
    IN_COMMENT varchar(250),
    IN_PAYTEXT varchar(250),
    IN_OPERATORID integer,
    IN_OBMOL varchar(50),
    IN_PERPAYFROM varchar(50),
    IN_PAYNODDSSUM numeric(15,2),
    IN_PAYDDSSUM numeric(15,2),
    IN_FACTOR numeric(15,2),
    IN_PAYDATE date,
    IN_REALPAYDATE date,
    IN_NODDSDESCR varchar(255),
    IN_DDSEVENTDATE date) 
returns (
    IN_ID integer) 
as
declare variable IN_OPERATORNAME varchar(140);
declare variable IN_PERNAME varchar(140);
declare variable IN_PERADDR varchar(200);
declare variable IN_PERNM varchar(30);
declare variable IN_PEREGN varchar(15);
declare variable IN_PERDANN varchar(10);
declare variable IN_PERMOLNAME varchar(140);
declare variable IN_PERINDDS varchar(15);
declare variable IN_PER_ADDR_CORESP varchar(250);
declare variable IN_PER_TDNAP varchar(20);
declare variable IN_PER_TDNAP_ADDR varchar(50);
begin
  /* wzima se imeto na kasiera */
  select USER_STR from users
  where USER_ID = :IN_OperatorID
  into :IN_OperatorName;

  /* wzimat se dannite na personata */
  select PerName, PerEgn, PerDanN, PerAddr, PerNM, PerMolName, PerINDDS, PerAddr_Coresp, PerTDNap, PerTDNap_Addr
  from PersonGetInfo(:IN_PerID)
  into :IN_PerName, :IN_PerEgn, :IN_PerDanN, :IN_PerAddr, :IN_PerNM, :IN_PERMOLNAME, :IN_PERINDDS,
  :IN_PER_ADDR_CORESP, :IN_PER_TDNAP, :IN_PER_TDNAP_ADDR;

  if (IN_PAYDATE < "01.01.1990" ) then
    IN_PayDate = "NOW";

  if (IN_DDSEVENTDATE < "01.01.1990" ) then
    IN_DDSEVENTDATE = IN_PayDate;
  
  IN_ID = GEN_ID(IN_ID_GEN, 1);
  insert into Invoices (IN_ID, IN_PERID, IN_PERNAME, IN_PERADDR, IN_PERNM, IN_PEREGN,
                        IN_PERDANN, IN_PERMOLNAME, IN_PAYTYPE, IN_PAYTEXT, IN_PAYID,
                        IN_PAYSUM, IN_PAYDDS, IN_OPERATORID, IN_OPERATORNAME, IN_PAYLIHVI,
                        IN_PAYWAY, IN_ANUL, IN_COMMENT, IN_PAYCLEARSUM,
                        IN_OBMOL, IN_REAL, IN_PerPayFrom,
                        IN_PayDDSSum, IN_PayNoDDSSum, IN_FACTOR, IN_PAYDATE, IN_REALPAYDATE, 
                        IN_PERINDDS, IN_FLAG2007, IN_NODDSDESCR, IN_DDSEVENTDATE,
                        IN_PER_ADDR_CORESP, IN_PER_TDNAP, IN_PER_TDNAP_ADDR)
  values               (:IN_ID, :IN_PERID, :IN_PERNAME, :IN_PERADDR, :IN_PERNM, :IN_PEREGN,
                        :IN_PERDANN, :IN_PERMOLNAME, :IN_PAYTYPE, :IN_PAYTEXT, :IN_PAYID,
                        :IN_PAYSUM, :IN_PAYDDS, :IN_OPERATORID, :IN_OPERATORNAME, :IN_PAYLIHVI,
                        :IN_PAYWAY, "A", :IN_COMMENT, :IN_PAYCLEARSUM,
                        :IN_OBMOL, "F", :IN_PerPayFrom,
                        :IN_PayDDSSum, :IN_PayNoDDSSum, :IN_FACTOR, :IN_PAYDATE, :IN_REALPAYDATE,
                        :IN_PERINDDS, "T", :IN_NODDSDESCR, :IN_DDSEVENTDATE,
                        :IN_PER_ADDR_CORESP, :IN_PER_TDNAP, :IN_PER_TDNAP_ADDR);
  
  suspend;
end
/*SQLStop>1671*/

/*MANYStart>1672*/
/*grantvame procedurite*/
grant execute on procedure personadd to public;
grant execute on procedure firmadd to public;
grant execute on procedure personupdate to public;
grant execute on procedure firmupdate to public;
grant execute on procedure invoiceaddheader to public;
grant execute on procedure persongetinfo to public;
/*MANYStop>1672*/
