/*ALIAS>prop_db*/

/*SQLStart>2856*/
CREATE GENERATOR PersonIdentities_ID_GEN;
/*SQLStop*/

/*SQLStart>2857*/
SET GENERATOR PersonIdentities_ID_GEN to 0;
/*SQLStop*/

/*SQLStart>2858*/
CREATE TABLE PersonIdentities(
  PI_ID INTEGER NOT NULL,
  PI_Identifier VARCHAR(15) NOT NULL,
  PI_CODE VARCHAR(10) NOT NULL
);
/*SQLStop*/

/*SQLStart>2859*/
ALTER TABLE PersonIdentities
ADD CONSTRAINT PI_PRIMARYKEY PRIMARY KEY (PI_ID);
/*SQLStop*/

/*SQLStart>2860*/
CREATE UNIQUE INDEX PI_I_Unique ON PersonIdentities (PI_Identifier);
/*SQLStop*/

/*SQLStart>2861*/
CREATE TRIGGER Set_PersonIdentities_ID_GEN for PersonIdentities
BEFORE INSERT
AS
BEGIN
 new.PI_ID = gen_id(PersonIdentities_ID_GEN, 1);
END;
/*SQLStop*/

/*SQLStart>2862*/
GRANT all ON PersonIdentities to public;
/*SQLStop*/

/*SQLStart>2863*/
ALTER TABLE Invoices
ADD IN_PER_IDENTITYCODE VARCHAR(10);
/*SQLStop*/

/*SQLStart>2864*/
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 blob sub_type 1 segment size 4096,
    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,
    IN_PAYMENTCHANNELID integer,
    IN_PER_IDENTITYCODE varchar(10))
returns (
    IN_ID integer)
as
declare variable IN_OPERATORNAME varchar(140);
declare variable IN_PERNAME varchar(140);
declare variable IN_PERADDR varchar(250);
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, IN_PAYMENTCHANNELID,
                        IN_PER_IDENTITYCODE)
  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, :IN_PAYMENTCHANNELID,
                        :IN_PER_IDENTITYCODE);
  
  suspend;
end;
/*SQLStop*/

/*SQLStart>2865*/
UPDATE system_strings SET sys_str = '6.7.240.1' WHERE sys_id = 1401;
/*SQLStop*/