/********************* ROLES **********************/

/********************* UDFS ***********************/

DECLARE EXTERNAL FUNCTION ASCII_CHAR
integer
RETURNS char(1)
ENTRY_POINT 'IB_UDF_ascii_char'
MODULE_NAME 'ib_udf';

/****************** SEQUENCES ********************/

CREATE SEQUENCE GEN_PK;
/******************** DOMAINS *********************/

CREATE DOMAIN DOM_PK
 AS integer
 DEFAULT 1
 NOT NULL
 CHECK (VALUE > 0)
;
CREATE DOMAIN MON$SEC_DATABASE
 AS char(7) CHARACTER SET ASCII
 NOT NULL
 COLLATE ASCII;
CREATE DOMAIN SEC$KEY
 AS varchar(10) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;
CREATE DOMAIN SEC$NAME_PART
 AS varchar(10) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;
CREATE DOMAIN SEC$USER_NAME
 AS varchar(10) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;
CREATE DOMAIN SEC$VALUE
 AS varchar(85) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;
/******************* PROCEDURES ******************/

SET TERM ^ ;
CREATE PROCEDURE P_ABM_FACTURA (
    OP char(1),
    NRO integer,
    IMPORTE double precision )
RETURNS (
    RET integer,
    MSG varchar(200) )
AS
BEGIN SUSPEND; END^
SET TERM ; ^

SET TERM ^ ;
CREATE PROCEDURE P_ABM_FACTURA2 (
    OP char(1),
    NRO integer,
    IMPORTE double precision )
RETURNS (
    RET integer,
    MSG varchar(200) )
AS
BEGIN SUSPEND; END^
SET TERM ; ^

SET TERM ^ ;
CREATE PROCEDURE P_ABM_FACTURA3 (
    OP char(1),
    NRO integer,
    IMPORTE double precision )
RETURNS (
    RET integer,
    MSG varchar(200) )
AS
BEGIN SUSPEND; END^
SET TERM ; ^

SET TERM ^ ;
CREATE PROCEDURE P_ABM_FACTURA4 (
    OP char(1),
    NRO integer,
    IMPORTE double precision )
RETURNS (
    RET integer,
    MSG varchar(200) )
AS
BEGIN SUSPEND; END^
SET TERM ; ^

SET TERM ^ ;
CREATE PROCEDURE SP_FACTURA (
    INRO integer )
RETURNS (
    NRO integer,
    IMPORTE double precision,
    DIMPORTE double precision,
    SALDOD double precision )
AS
BEGIN SUSPEND; END^
SET TERM ; ^

SET TERM ^ ;
CREATE PROCEDURE SP_FACTURA2 (
    INRO integer )
RETURNS (
    FILA integer,
    NRO integer,
    IMPORTE double precision,
    DIMPORTE double precision,
    SALDOD double precision )
AS
BEGIN SUSPEND; END^
SET TERM ; ^

/******************** TABLES **********************/

CREATE TABLE DETALLE
(
  NRO integer NOT NULL,
  ID integer NOT NULL,
  CANTIDAD integer DEFAULT 1 NOT NULL,
  PRECIO double precision NOT NULL,
  CONSTRAINT PK_DETALLE PRIMARY KEY (NRO,ID)
);
CREATE TABLE FACTURA
(
  NRO integer NOT NULL,
  IMPORTE double precision DEFAULT 0 NOT NULL,
  CONSTRAINT PK_FACTURA PRIMARY KEY (NRO)
);
CREATE TABLE FACTURA_AUX
(
  NRO integer NOT NULL,
  NLINEAS integer DEFAULT 0 NOT NULL,
  UNRO integer,
  SALDOD double precision,
  CONSTRAINT PK_FACTURA_AUX PRIMARY KEY (NRO)
);
CREATE TABLE LOG
(
  ID integer DEFAULT 1 NOT NULL,
  USUARIO varchar(64) NOT NULL,
  PROCESO varchar(100),
  RET integer,
  MSG varchar(200),
  FECHA timestamp,
  ROL varchar(64),
  CONSTRAINT PK_LOG PRIMARY KEY (ID)
);
CREATE TABLE PABORRAR
(
  CODIGO DOM_PK NOT NULL,
  CONSTRAINT PK_PABORRAR PRIMARY KEY (CODIGO)
);
CREATE TABLE PAGO
(
  NRO integer NOT NULL,
  NROPAGO integer NOT NULL,
  IMPORTE double precision NOT NULL,
  CONSTRAINT PK_PAGO PRIMARY KEY (NRO,NROPAGO)
);
CREATE TABLE PRODUCTO
(
  ID integer NOT NULL,
  DESCR varchar(100),
  STOCK integer DEFAULT 0 NOT NULL,
  CONSTRAINT PK_PRODUCTO PRIMARY KEY (ID)
);
/********************* VIEWS **********************/

CREATE VIEW VPABORRAR (CODIGO, CODIGO10)
AS SELECT CODIGO,CODIGO+10 FROM PABORRAR;
/******************* EXCEPTIONS *******************/

CREATE EXCEPTION EX_LINEAS
'Supero el maximo de lineas permitidas por factura!';
CREATE EXCEPTION EX_PAGO
'Factura con pagos no puede ser modificada!';
CREATE EXCEPTION EX_PAGO2
'El pago no puede superar al saldo deudor de la factura!';
CREATE EXCEPTION EX_PAGO3
'No se pueden hacer cambios en los pagos!';
CREATE EXCEPTION EX_PAGO4
'No puedo borrar pagos intermedios!';
CREATE EXCEPTION EX_PK
'Error, no puede modificarse la clave primaria!';
/******************** TRIGGERS ********************/

SET TERM ^ ;
CREATE TRIGGER TRG_ADDETALLE FOR DETALLE ACTIVE
AFTER delete POSITION 0
AS
BEGIN
    /* DECREMENTO NLINEAS */ 
    UPDATE FACTURA_AUX SET NLINEAS = NLINEAS - 1 WHERE NRO = OLD.NRO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_ADFACTURA FOR FACTURA ACTIVE
AFTER delete POSITION 0
AS
BEGIN
    /* enter trigger code here */ 
    DELETE FROM FACTURA_AUX WHERE NRO = OLD.NRO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_ADPAGO FOR PAGO ACTIVE
AFTER delete POSITION 0
AS
BEGIN
    UPDATE FACTURA_AUX SET SALDOD = SALDOD + OLD.IMPORTE,UNRO = UNRO - 1 WHERE NRO = OLD.NRO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AIDETALLE FOR DETALLE ACTIVE
AFTER insert POSITION 0
AS
BEGIN
    /* enter trigger code here */ 
    UPDATE FACTURA_AUX SET NLINEAS = NLINEAS + 1 WHERE NRO = NEW.NRO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AIFACTURA FOR FACTURA ACTIVE
AFTER insert POSITION 0
AS
BEGIN
    /* enter trigger code here */
    INSERT INTO FACTURA_AUX(NRO,NLINEAS,UNRO,SALDOD) VALUES(NEW.NRO,0,0,NEW.IMPORTE); 
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AIPAGO FOR PAGO ACTIVE
AFTER insert POSITION 0
AS
BEGIN
    /* enter trigger code here */ 
    UPDATE FACTURA_AUX SET SALDOD = SALDOD - NEW.IMPORTE,UNRO = NEW.NROPAGO WHERE NRO = NEW.NRO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BDPAGO FOR PAGO ACTIVE
BEFORE delete POSITION 0
AS
BEGIN
    /* enter trigger code here */ 
    IF (OLD.NROPAGO <> (SELECT UNRO FROM FACTURA_AUX WHERE NRO = :OLD.NRO) ) THEN 
        EXCEPTION EX_PAGO4;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BIDETALLE FOR DETALLE ACTIVE
BEFORE insert POSITION 0
AS
BEGIN
    /* EL NUMERO MAXIMO DE LINEAS DE DETALLE POR FACTURA ES 3 */ 
    IF ( (SELECT NLINEAS FROM FACTURA_AUX WHERE NRO = NEW.NRO) >= 3 ) THEN EXCEPTION EX_LINEAS;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BILOG FOR LOG ACTIVE
BEFORE insert POSITION 0
AS
BEGIN
    /* enter trigger code here */ 
    NEW.ID = GEN_ID(GEN_PK,1);
    NEW.FECHA = 'NOW';
    NEW.ROL = CURRENT_ROLE;
    NEW.USUARIO = CURRENT_USER;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BIPABORRAR FOR PABORRAR ACTIVE
BEFORE insert POSITION 0
AS 
BEGIN 
    /* enter trigger code here */
    NEW.CODIGO = GEN_ID(GEN_PK,1); 
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BIPAGO FOR PAGO ACTIVE
BEFORE insert POSITION 0
AS 
    DECLARE VARIABLE SALDOD TYPE OF COLUMN FACTURA_AUX.SALDOD;
    DECLARE VARIABLE UNRO TYPE OF COLUMN FACTURA_AUX.UNRO;
BEGIN 
    /* enter trigger code here */
    SELECT SALDOD,UNRO FROM FACTURA_AUX WHERE NRO = NEW.NRO INTO :SALDOD,:UNRO;
    IF (NEW.IMPORTE > SALDOD ) THEN EXCEPTION EX_PAGO2;
    NEW.NROPAGO = UNRO+1;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BUDETALLE FOR DETALLE ACTIVE
BEFORE update POSITION 0
AS
BEGIN
    /* NO PERMITO CAMBIOS EN CLAVE PRIMARIA */ 
    IF ( OLD.NRO <> NEW.NRO ) THEN EXCEPTION EX_PK;
    IF ( OLD.ID  <> NEW.ID  ) THEN EXCEPTION EX_PK;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BUFACTURA FOR FACTURA ACTIVE
BEFORE update POSITION 0
AS
BEGIN
    /* enter trigger code here */ 
    IF ( OLD.NRO <> NEW.NRO ) THEN EXCEPTION EX_PK;
    IF ( EXISTS(SELECT * FROM PAGO WHERE NRO = :NEW.NRO) ) THEN EXCEPTION EX_PAGO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BUPAGO FOR PAGO ACTIVE
BEFORE update POSITION 0
AS
BEGIN
    /* No se permite hacer ningun tipo de cambios en pago */ 
    EXCEPTION EX_PAGO3; 
END^
SET TERM ; ^

SET TERM ^ ;
ALTER PROCEDURE P_ABM_FACTURA (
    OP char(1),
    NRO integer,
    IMPORTE double precision )
RETURNS (
    RET integer,
    MSG varchar(200) )
AS
BEGIN
   RET=0;MSG=NULL;
   IF ( OP = 'I' ) THEN BEGIN --ALTA 
      INSERT INTO FACTURA(NRO,IMPORTE) VALUES(:NRO,:IMPORTE);
   END 
   IF ( OP = 'U' ) THEN BEGIN --CAMBIO
      UPDATE FACTURA SET IMPORTE = :IMPORTE WHERE NRO = :NRO;
   END 
   IF ( OP = 'D' ) THEN BEGIN --BAJA
      DELETE FROM FACTURA WHERE NRO = :NRO;
   END 
END^
SET TERM ; ^


SET TERM ^ ;
ALTER PROCEDURE P_ABM_FACTURA2 (
    OP char(1),
    NRO integer,
    IMPORTE double precision )
RETURNS (
    RET integer,
    MSG varchar(200) )
AS
BEGIN
   -- EVITO QUE DE ERROR EL PROCEDIMIENTO Y ACTIVO VALORES DE RETORNO
   RET=0;MSG=NULL;
   IF ( OP = 'I' ) THEN BEGIN --ALTA 
      INSERT INTO FACTURA(NRO,IMPORTE) VALUES(:NRO,:IMPORTE);
   END 
   IF ( OP = 'U' ) THEN BEGIN --CAMBIO
      UPDATE FACTURA SET IMPORTE = :IMPORTE WHERE NRO = :NRO;
   END 
   IF ( OP = 'D' ) THEN BEGIN --BAJA
      DELETE FROM FACTURA WHERE NRO = :NRO;
   END 
   WHEN ANY DO BEGIN
      --CAPTURO TODO TIPO DE ERROR QUE SUCEDA EN BLOQUE BEGIN - END QUE CONTIENE A ESTE WHEN 
      RET=SQLCODE;
      MSG='ERROR SQL ' || SQLCODE || ' OP=' || :OP || ' NRO=' || :NRO || ', IMPORTE=' || :IMPORTE; 
   END 
END^
SET TERM ; ^


SET TERM ^ ;
ALTER PROCEDURE P_ABM_FACTURA3 (
    OP char(1),
    NRO integer,
    IMPORTE double precision )
RETURNS (
    RET integer,
    MSG varchar(200) )
AS
BEGIN
   -- EVITO QUE DE ERROR EL PROCEDIMIENTO Y ACTIVO VALORES DE RETORNO
   RET=0;MSG=NULL;
   IF ( OP = 'I' ) THEN BEGIN --ALTA 
      INSERT INTO FACTURA(NRO,IMPORTE) VALUES(:NRO,:IMPORTE);
      WHEN ANY DO BEGIN
         --CAPTURO TODO TIPO DE ERROR QUE SUCEDA EN BLOQUE BEGIN - END QUE CONTIENE A ESTE WHEN 
         RET=SQLCODE;
         MSG='ERROR SQL ' || SQLCODE || ' OP=I' || ' NRO=' || :NRO || ', IMPORTE=' || :IMPORTE; 
      END 
   END 
   IF ( OP = 'U' ) THEN BEGIN --CAMBIO
      UPDATE FACTURA SET IMPORTE = :IMPORTE WHERE NRO = :NRO;
      WHEN ANY DO BEGIN
         --CAPTURO TODO TIPO DE ERROR QUE SUCEDA EN BLOQUE BEGIN - END QUE CONTIENE A ESTE WHEN 
         RET=SQLCODE;
         MSG='ERROR SQL ' || SQLCODE || ' OP=U' || ' NRO=' || :NRO || ', IMPORTE=' || :IMPORTE; 
      END 
   END 
   IF ( OP = 'D' ) THEN BEGIN --BAJA
      DELETE FROM FACTURA WHERE NRO = :NRO;
      WHEN ANY DO BEGIN
         --CAPTURO TODO TIPO DE ERROR QUE SUCEDA EN BLOQUE BEGIN - END QUE CONTIENE A ESTE WHEN 
         RET=SQLCODE;
         MSG='ERROR SQL ' || SQLCODE || ' OP=D' || ' NRO=' || :NRO; 
      END 
   END 
END^
SET TERM ; ^


SET TERM ^ ;
ALTER PROCEDURE P_ABM_FACTURA4 (
    OP char(1),
    NRO integer,
    IMPORTE double precision )
RETURNS (
    RET integer,
    MSG varchar(200) )
AS
DECLARE VARIABLE PNAME VARCHAR(100);
BEGIN
   -- EVITO QUE DE ERROR EL PROCEDIMIENTO Y ACTIVO VALORES DE RETORNO
   -- GRABA ERROR EN TABLA LOG
   RET=0;MSG=NULL;PNAME='P_ABM_FACTURA4';
   IF ( OP = 'I' ) THEN BEGIN --ALTA 
      INSERT INTO FACTURA(NRO,IMPORTE) VALUES(:NRO,:IMPORTE);
      WHEN ANY DO BEGIN
         --CAPTURO TODO TIPO DE ERROR QUE SUCEDA EN BLOQUE BEGIN - END QUE CONTIENE A ESTE WHEN 
         RET=SQLCODE;
         MSG='ERROR SQL ' || SQLCODE || ' OP=I' || ' NRO=' || :NRO || ', IMPORTE=' || :IMPORTE; 
         INSERT INTO LOG(RET,MSG,PROCESO) VALUES(:RET,:MSG,:PNAME);
      END 
   END 
   IF ( OP = 'U' ) THEN BEGIN --CAMBIO
      UPDATE FACTURA SET IMPORTE = :IMPORTE WHERE NRO = :NRO;
      WHEN ANY DO BEGIN
         --CAPTURO TODO TIPO DE ERROR QUE SUCEDA EN BLOQUE BEGIN - END QUE CONTIENE A ESTE WHEN 
         RET=SQLCODE;
         MSG='ERROR SQL ' || SQLCODE || ' OP=U' || ' NRO=' || :NRO || ', IMPORTE=' || :IMPORTE; 
         INSERT INTO LOG(RET,MSG,PROCESO) VALUES(:RET,:MSG,:PNAME);
      END 
   END 
   IF ( OP = 'D' ) THEN BEGIN --BAJA
      DELETE FROM FACTURA WHERE NRO = :NRO;
      WHEN ANY DO BEGIN
         --CAPTURO TODO TIPO DE ERROR QUE SUCEDA EN BLOQUE BEGIN - END QUE CONTIENE A ESTE WHEN 
         RET=SQLCODE;
         MSG='ERROR SQL ' || SQLCODE || ' OP=D' || ' NRO=' || :NRO; 
         INSERT INTO LOG(RET,MSG,PROCESO) VALUES(:RET,:MSG,:PNAME);
      END 
   END 
END^
SET TERM ; ^


SET TERM ^ ;
ALTER PROCEDURE SP_FACTURA (
    INRO integer )
RETURNS (
    NRO integer,
    IMPORTE double precision,
    DIMPORTE double precision,
    SALDOD double precision )
AS
BEGIN
  /* write your code here */
  FOR SELECT F.NRO,F.IMPORTE,A.SALDOD
      FROM FACTURA F INNER JOIN FACTURA_AUX A ON F.NRO = A.NRO 
      WHERE F.NRO = :INRO
      INTO :NRO,:IMPORTE,:SALDOD
  DO BEGIN
      SELECT SUM(CANTIDAD*PRECIO) FROM DETALLE WHERE NRO = :NRO INTO :DIMPORTE;
      IF ( DIMPORTE IS NULL ) THEN DIMPORTE = 0.0;
      SUSPEND;
  END  
END^
SET TERM ; ^


SET TERM ^ ;
ALTER PROCEDURE SP_FACTURA2 (
    INRO integer )
RETURNS (
    FILA integer,
    NRO integer,
    IMPORTE double precision,
    DIMPORTE double precision,
    SALDOD double precision )
AS
BEGIN
  /* write your code here */
  FILA=0;
  FOR SELECT F.NRO,F.IMPORTE,A.SALDOD
      FROM FACTURA F INNER JOIN FACTURA_AUX A ON F.NRO = A.NRO 
      WHERE F.NRO = COALESCE(:INRO,F.NRO)
      INTO :NRO,:IMPORTE,:SALDOD
  DO BEGIN
      SELECT SUM(CANTIDAD*PRECIO) FROM DETALLE WHERE NRO = :NRO INTO :DIMPORTE;
      IF ( DIMPORTE IS NULL ) THEN DIMPORTE = 0.0;
      FILA=FILA+1;
      SUSPEND;
  END  
END^
SET TERM ; ^


ALTER TABLE DETALLE ADD CONSTRAINT FK_DETALLE_FACTURA
  FOREIGN KEY (NRO) REFERENCES FACTURA (NRO);
ALTER TABLE DETALLE ADD CONSTRAINT FK_DETALLE_PRODUCTO
  FOREIGN KEY (ID) REFERENCES PRODUCTO (ID);
ALTER TABLE PAGO ADD CONSTRAINT FK_PAGO_FACTURA
  FOREIGN KEY (NRO) REFERENCES FACTURA (NRO);
GRANT EXECUTE
 ON PROCEDURE P_ABM_FACTURA TO  SYSDBA WITH GRANT OPTION;

GRANT EXECUTE
 ON PROCEDURE P_ABM_FACTURA2 TO  SYSDBA WITH GRANT OPTION;

GRANT EXECUTE
 ON PROCEDURE P_ABM_FACTURA3 TO  SYSDBA WITH GRANT OPTION;

GRANT EXECUTE
 ON PROCEDURE P_ABM_FACTURA4 TO  SYSDBA WITH GRANT OPTION;

GRANT EXECUTE
 ON PROCEDURE SP_FACTURA TO  SYSDBA WITH GRANT OPTION;

GRANT EXECUTE
 ON PROCEDURE SP_FACTURA2 TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON DETALLE TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON FACTURA TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON FACTURA_AUX TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON LOG TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON PABORRAR TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON PAGO TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON PRODUCTO TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON VPABORRAR TO  SYSDBA WITH GRANT OPTION;

