
SET SQL DIALECT 3; 

/* CREATE DATABASE 'D:\grchere\i189\pp\2019\PROYECTO.FDB' PAGE_SIZE 4096 DEFAULT CHARACTER SET NONE */


/*  External Function declarations */
DECLARE EXTERNAL FUNCTION STRLEN
CSTRING(32767) CHARACTER SET NONE
RETURNS INTEGER BY VALUE 
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';


/*  Generators or sequences */
CREATE GENERATOR GEN_IDPRO;

/* Domain definitions */
CREATE DOMAIN DOM_CHARPK AS CHAR(20)
         CHECK (UPPER(VALUE) = VALUE AND STRLEN(VALUE) > 2) NOT NULL;
CREATE DOMAIN DOM_DESCR AS VARCHAR(100)
         CHECK (VALUE = UPPER(VALUE)) NOT NULL;
CREATE DOMAIN DOM_NIVEL AS CHAR(1)
         DEFAULT 'M'
         CHECK (UPPER(VALUE) = VALUE AND VALUE IN('A','M','I')) NOT NULL;
CREATE DOMAIN DOM_PK AS INTEGER
         DEFAULT 1
         CHECK (VALUE >= 1) NOT NULL;
CREATE DOMAIN DOM_ROL AS CHAR(1)
         DEFAULT 'A'
         CHECK (VALUE IN('A','P') AND UPPER(VALUE) = VALUE) NOT NULL;

/* Table: TBL_ANALISTA, Owner: SYSDBA */
CREATE TABLE TBL_ANALISTA (LEGAJO DOM_PK NOT NULL,
CONSTRAINT PK_TBL_ANALISTA PRIMARY KEY (LEGAJO));

/* Table: TBL_EMPLEADO, Owner: SYSDBA */
CREATE TABLE TBL_EMPLEADO (LEGAJO DOM_PK NOT NULL,
        APE DOM_DESCR,
        NOM DOM_DESCR,
        DIR VARCHAR(100),
        CEL INTEGER,
        EMAIL VARCHAR(100),
        SALARIO DOUBLE PRECISION DEFAULT 0.0,
CONSTRAINT PK_TBL_EMPLEADO PRIMARY KEY (LEGAJO));

/* Table: TBL_EMPLEADO_AUX, Owner: SYSDBA */
CREATE TABLE TBL_EMPLEADO_AUX (LEGAJO DOM_PK NOT NULL,
        ROL DOM_ROL,
CONSTRAINT PK_TBL_EMPLEADO_AUX PRIMARY KEY (LEGAJO));

/* Table: TBL_GERENCIA, Owner: SYSDBA */
CREATE TABLE TBL_GERENCIA (NOMBRE DOM_CHARPK NOT NULL,
CONSTRAINT PK_TBL_GERENCIA PRIMARY KEY (NOMBRE));

/* Table: TBL_LENGUAJE, Owner: SYSDBA */
CREATE TABLE TBL_LENGUAJE (NOMBRE DOM_CHARPK NOT NULL,
CONSTRAINT PK_TBL_LENGUAJE PRIMARY KEY (NOMBRE));

/* Table: TBL_PE, Owner: SYSDBA */
CREATE TABLE TBL_PE (IDP DOM_PK NOT NULL,
        LEGAJO DOM_PK NOT NULL,
        FECHA DATE DEFAULT current_date NOT NULL,
        HS INTEGER DEFAULT 1 NOT NULL,
        VALOR DOUBLE PRECISION NOT NULL,
CONSTRAINT PK_TBL_PE PRIMARY KEY (IDP, LEGAJO));

/* Table: TBL_PE_AUX, Owner: SYSDBA */
CREATE TABLE TBL_PE_AUX (IDP DOM_PK NOT NULL,
        LEGAJO DOM_PK NOT NULL,
        ROL DOM_ROL,
CONSTRAINT PK_TBL_PE_AUX PRIMARY KEY (IDP, LEGAJO));

/* Table: TBL_PROGRAMADOR, Owner: SYSDBA */
CREATE TABLE TBL_PROGRAMADOR (LEGAJO DOM_PK NOT NULL,
CONSTRAINT PK_TBL_PROGRAMADOR PRIMARY KEY (LEGAJO));

/* Table: TBL_PROG_LENG, Owner: SYSDBA */
CREATE TABLE TBL_PROG_LENG (LEGAJO DOM_PK NOT NULL,
        NOMBRE DOM_CHARPK NOT NULL,
        NIVEL DOM_NIVEL,
CONSTRAINT PK_TBL_PROG_LENG PRIMARY KEY (LEGAJO, NOMBRE));

/* Table: TBL_PROYECTO, Owner: SYSDBA */
CREATE TABLE TBL_PROYECTO (IDP DOM_PK NOT NULL,
        DESCR DOM_DESCR,
        FECHAI DATE NOT NULL,
        FECHAF DATE,
        PRESUP DOUBLE PRECISION NOT NULL,
        PRESUP_HS INTEGER NOT NULL,
        GERENCIA DOM_CHARPK,
        LIDER DOM_PK,
CONSTRAINT PK_TBL_PROYECTO PRIMARY KEY (IDP));

/* Table: TBL_PROYECTO_AUX, Owner: SYSDBA */
CREATE TABLE TBL_PROYECTO_AUX (IDP DOM_PK NOT NULL,
        PRESUP DOUBLE PRECISION NOT NULL,
        PRESUP_HS INTEGER NOT NULL,
        CONSUMO DOUBLE PRECISION NOT NULL,
        CONSUMO_HS INTEGER NOT NULL,
        SALDO COMPUTED BY (PRESUP - CONSUMO),
        SALDO_HS COMPUTED BY (PRESUP_HS - CONSUMO_HS),
        PSALDO COMPUTED BY ((CONSUMO / PRESUP)*100.0),
        PSALDO_HS COMPUTED BY (((CONSUMO_HS*1.0) / (PRESUP_HS*1.0))*100.0),
CONSTRAINT PK_TBL_PROYECTO_AUX PRIMARY KEY (IDP));

ALTER TABLE TBL_ANALISTA ADD CONSTRAINT FK_TBL_ANALISTA_EMPLEADO FOREIGN KEY (LEGAJO) REFERENCES TBL_EMPLEADO (LEGAJO);

ALTER TABLE TBL_PE ADD CONSTRAINT FK_TBL_PE_EMPL FOREIGN KEY (LEGAJO) REFERENCES TBL_EMPLEADO (LEGAJO);

ALTER TABLE TBL_PE ADD CONSTRAINT FK_TBL_PE_PROY FOREIGN KEY (IDP) REFERENCES TBL_PROYECTO (IDP);

ALTER TABLE TBL_PROGRAMADOR ADD CONSTRAINT FK_TBL_PROGRAMADOR_EMPLEADO FOREIGN KEY (LEGAJO) REFERENCES TBL_EMPLEADO (LEGAJO);

ALTER TABLE TBL_PROG_LENG ADD CONSTRAINT FK_TBL_PROG_LENG_LENG FOREIGN KEY (NOMBRE) REFERENCES TBL_LENGUAJE (NOMBRE);

ALTER TABLE TBL_PROG_LENG ADD CONSTRAINT FK_TBL_PROG_LENG_PROG FOREIGN KEY (LEGAJO) REFERENCES TBL_PROGRAMADOR (LEGAJO);

ALTER TABLE TBL_PROYECTO ADD CONSTRAINT FK_TBL_PROYECTO_EMPLEADO FOREIGN KEY (LIDER) REFERENCES TBL_EMPLEADO (LEGAJO);

ALTER TABLE TBL_PROYECTO ADD CONSTRAINT FK_TBL_PROYECTO_GERENCIA FOREIGN KEY (GERENCIA) REFERENCES TBL_GERENCIA (NOMBRE);

/* View: V_PROYECTO, Owner: SYSDBA */
CREATE VIEW V_PROYECTO (IDP, DESCR, FECHAI, FECHAF, PRESUP, PRESUP_HS, GERENCIA, LIDER, CONSUMO, CONSUMO_HS, SALDO, SALDO_HS, PSALDO, PSALDO_HS) AS

/* Unir datos de tabla con tabla auxiliar */
SELECT P.*,PA.CONSUMO,PA.CONSUMO_HS,PA.SALDO,PA.SALDO_HS,PA.PSALDO,PA.PSALDO_HS
FROM TBL_PROYECTO P INNER JOIN TBL_PROYECTO_AUX PA ON P.IDP = PA.IDP
;

/* View: V_PE, Owner: SYSDBA */
CREATE VIEW V_PE (IDP, LEGAJO, FECHA, HS, VALOR, ROL) AS

/* Unir datos de tabla con tabla auxiliar */
SELECT P.*,PA.ROL
FROM TBL_PE P INNER JOIN TBL_PE_AUX PA ON P.IDP = PA.IDP AND P.LEGAJO = PA.LEGAJO
;

/* View: V_EMPLEADO, Owner: SYSDBA */
CREATE VIEW V_EMPLEADO (LEGAJO, APE, NOM, DIR, CEL, EMAIL, ROL) AS

/* Unir datos de tabla con tabla auxiliar */
SELECT P.*,PA.ROL
FROM TBL_EMPLEADO P INNER JOIN TBL_EMPLEADO_AUX PA ON P.LEGAJO = PA.LEGAJO
;

ALTER TABLE TBL_PE ADD 
        CONSTRAINT CHK_HS_VALOR CHECK (HS > 0 AND VALOR > 0.0);

/*  Exceptions */
CREATE EXCEPTION EX_ERROR 'Operacion no permitida';
CREATE EXCEPTION EX_PROYECTO_FECHAF 'Proyecto Finalizado, no puede agregar horas!';
SET TERM ^ ;

/* Triggers only will work for SQL triggers */
CREATE TRIGGER TRG_AITBL_EMPLEADO FOR TBL_EMPLEADO 
ACTIVE AFTER INSERT POSITION 0 
AS
BEGIN
    /* Mantengo la tabla paralela TBL_EMPLEADO_AUX 
       Asumo que un empleado es analista por defecto */ 
    INSERT INTO TBL_EMPLEADO_AUX(LEGAJO,ROL) VALUES(NEW.LEGAJO,'A');
END ^

CREATE TRIGGER TRG_BUTBL_EMPLEADO FOR TBL_EMPLEADO 
ACTIVE BEFORE UPDATE POSITION 0 
AS
BEGIN
    /* No permito cambios en legajo */
    IF ( NEW.LEGAJO <> OLD.LEGAJO ) THEN EXCEPTION EX_ERROR 'No se puede cambiar el Legajo';
END ^

CREATE TRIGGER TRG_ADTBL_EMPLEADO FOR TBL_EMPLEADO 
ACTIVE AFTER DELETE POSITION 0 
AS
BEGIN
    /* Mantengo tabla paralela TBL_EMPLEADO_AUX */ 
    DELETE FROM TBL_EMPLEADO_AUX WHERE LEGAJO = OLD.LEGAJO;
END ^

CREATE TRIGGER TRG_BITBL_PE FOR TBL_PE 
ACTIVE BEFORE INSERT POSITION 0 
AS
    DECLARE VARIABLE FECHA    TYPE OF COLUMN TBL_PROYECTO.FECHAF;
    DECLARE VARIABLE FECHAI   TYPE OF COLUMN TBL_PROYECTO.FECHAI; 
    DECLARE VARIABLE ESALARIO TYPE OF COLUMN TBL_EMPLEADO.SALARIO;
    
BEGIN
    /* Si el proyecto esta finalizado no se pueden cargar horas al mismo */ 
    SELECT FECHAI,FECHAF FROM TBL_PROYECTO WHERE IDP = NEW.IDP INTO :FECHAI,:FECHA;
    IF ( FECHA IS NOT NULL ) THEN EXCEPTION EX_PROYECTO_FECHAF; 
    /* La fecha de las horas debe ser >= a la fecha de inicio del proyecto.
       Si no se indica fecha, se asume la fecha de inicio del proyecto */
    IF ( NEW.FECHA IS NULL ) THEN NEW.FECHA = FECHAI;
    IF ( NEW.FECHA < FECHAI ) THEN EXCEPTION EX_ERROR 'La fecha ' || NEW.FECHA || ' debe ser >= a ' || :FECHAI;
    /* El valor de la hora debe ser >= (salario del empleado / 160) .
       Si no indica valor, se asume (salario del empleado / 160) */
    SELECT SALARIO FROM TBL_EMPLEADO WHERE LEGAJO = NEW.LEGAJO INTO :ESALARIO;
    IF ( NEW.VALOR IS NULL ) THEN NEW.VALOR = ( ESALARIO / 160.0);
    IF ( NEW.VALOR < ( ESALARIO / 160.0) ) THEN EXCEPTION EX_ERROR 'El valor horario debe ser >= a ' || ( ESALARIO / 160.0);
    
END ^

CREATE TRIGGER TRG_AITBL_PE FOR TBL_PE 
ACTIVE AFTER INSERT POSITION 0 
AS
    DECLARE VARIABLE ROL TYPE OF COLUMN TBL_EMPLEADO_AUX.ROL;
BEGIN
    /* Si inserto en TBL_PE tambien inserto en TBL_PE_AUX */ 
    SELECT ROL FROM TBL_EMPLEADO_AUX WHERE LEGAJO = NEW.LEGAJO INTO :ROL;
    INSERT INTO TBL_PE_AUX(IDP,LEGAJO,ROL) VALUES(NEW.IDP,NEW.LEGAJO,:ROL);
END ^

CREATE TRIGGER TRG_BUTBL_PE FOR TBL_PE 
ACTIVE BEFORE UPDATE POSITION 0 
AS
BEGIN
    /* No pertimos cambios en PE */
    EXCEPTION EX_ERROR; 
END ^

CREATE TRIGGER TRG_BITBL_PROYECTO FOR TBL_PROYECTO 
ACTIVE BEFORE INSERT POSITION 0 
AS
BEGIN
    /* Un proyecto no puede comenzar con fecha de finalizacion */
    NEW.FECHAF = NULL; 
END ^

COMMIT WORK ^
SET TERM ; ^
