SET TERM !! ;
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from rdb$relations where rdb$relation_name = 'WGHDELPREN')) then 
execute statement '
CREATE TABLE WGHDELPREN
(
  CODCLI Integer NOT NULL,
  ARRIVO Timestamp,
  PARTENZA Timestamp,
  TRATTAM Char(2),
  OPZIONE Timestamp,
  TIMELIM Smallint DEFAULT 0,
  ADULTI Integer DEFAULT 0,
  BAMBINI Integer DEFAULT 0,
  PRESTIN Char(1),
  LISTINO Integer DEFAULT 0,
  CODDITTA Integer DEFAULT 0,
  TIPOPAG Char(1),
  TIPOCAM1 Smallint DEFAULT 0,
  TIPOCAM2 Smallint DEFAULT 0,
  TIPOCAM3 Smallint DEFAULT 0,
  TIPOCAM4 Smallint DEFAULT 0,
  NCAM1 Integer DEFAULT 0,
  NCAM2 Integer DEFAULT 0,
  NCAM3 Integer DEFAULT 0,
  NCAM4 Integer DEFAULT 0,
  SUPPL1 Smallint DEFAULT 0,
  SUPPL2 Smallint DEFAULT 0,
  NRSUPPL1 Integer DEFAULT 0,
  NRSUPPL2 Integer DEFAULT 0,
  RIDUZ1 Smallint DEFAULT 0,
  RIDUZ2 Smallint DEFAULT 0,
  NRRIDUZ1 Integer DEFAULT 0,
  NRRIDUZ2 Integer DEFAULT 0,
  NOTE_PREN Varchar(2000),
  CAPARRA Numeric(15,4) DEFAULT 0,
  ACCONTO Numeric(15,4) DEFAULT 0,
  CODICE Integer NOT NULL,
  TAR1 Numeric(15,4) DEFAULT 0,
  TAR2 Numeric(18,4) DEFAULT 0,
  TAR3 Numeric(18,4) DEFAULT 0,
  DAL1 Timestamp,
  DAL2 Timestamp,
  NRPAC Integer DEFAULT 0,
  CAMRICH Varchar(255),
  NRFACC Integer DEFAULT 0,
  DATAPREN Timestamp,
  CARTA Smallint DEFAULT 0,
  PACCHETTO Integer DEFAULT 0,
  CANALE_V Smallint,
  SEGMENTO_M Smallint,
  DATA_LIMITE Timestamp,
  CODPRIMO Integer,
  CODSEC Integer,
  VALPRIMO Numeric(18,4),
  VALSEC Numeric(18,4),
  PRIMARY KEY (CODICE)
);';
END!!
SET TERM ; !!

SET TERM !! ;
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from rdb$relations where rdb$relation_name = 'WGHDELPRENCAM')) then 
execute statement '
CREATE TABLE WGHDELPRENCAM
(
  ID_RIGA Integer,
  CODPREN Integer,
  NUMCAM Varchar(6),
  TIPO Smallint,
  QTA Smallint
);';
END!!
SET TERM ; !!
COMMIT;

SET TERM ^ ;
EXECUTE BLOCK AS BEGIN
if (exists(select 1 from RDB$TRIGGERS where rdb$TRIGGER_NAME = 'COPIADELPREN')) then 
    BEGIN
    execute statement 'drop trigger COPIADELPREN;';
    END
END^
SET TERM ; ^

COMMIT;
SET TERM ^ ;
CREATE TRIGGER COPIADELPREN FOR WGHPREN
ACTIVE BEFORE DELETE POSITION 0
AS
declare variable codconto integer;
 
BEGIN 
	select first 1 codice from wghconto where wghconto.PRENOTAZIONE=old.CODICE into :codconto;
	if(codconto is null) then
        	begin
	        insert into wghdelpren select * from wghpren where codice=old.CODICE;
        	insert into wghdelprencam select * from WGHPRENCAM where WGHPRENCAM.CODPREN=old.CODICE;
        	update wghdelpren set wghdelpren.DATA_LIMITE=old.DATAPREN where WGHDELPREN.CODICE=old.CODICE;
			update wghdelpren set wghdelpren.DATAPREN=current_date where WGHDELPREN.CODICE=old.CODICE;
        	end
	
END^

SET TERM ; ^

SET TERM ^ ;
EXECUTE BLOCK AS BEGIN
if (exists(select 1 from RDB$PROCEDURES where rdb$Procedure_name = 'CALCOLA_PRESUNTOTARDEL')) then 
    BEGIN
    execute statement 'drop procedure CALCOLA_PRESUNTOTARDEL;';
    END
END^
SET TERM ; ^
COMMIT;
SET TERM ^ ;
CREATE PROCEDURE CALCOLA_PRESUNTOTARDEL (
    CODPREN Integer )
RETURNS (
    TOTALE Numeric(10,2) )
AS
DECLARE VARIABLE GIORNI  INTEGER;
DECLARE VARIABLE giorni_temp INTEGER;
DECLARE VARIABLE DATA_DAL_OLD TIMESTAMP;
DECLARE VARIABLE TARIFFA NUMERIC(10,2);
DECLARE VARIABLE DATA_DAL TIMESTAMP;
DECLARE VARIABLE ARRIVO TIMESTAMP;
BEGIN
    TOTALE=0;
    select cast(partenza-arrivo as INTEGER), partenza,arrivo from WGHDELPREN where codice=:CODPREN
        into :GIORNI, :DATA_DAL_OLD,:ARRIVO;
    for select tariffa,dal from wghprentar where codpren=:CODPREN and tipo is NULL
        order by dal DESC
        into :tariffa,:DATA_DAL
        do
        BEGIN
        giorni_temp=CAST((data_dal-arrivo) as INTEGER);
        TOTALE=TOTALE+(tariffa*(giorni-giorni_temp));
        giorni_temp=CAST((data_dal_old-data_dal) as INTEGER);
        giorni=giorni-giorni_temp;
        data_dal_old=data_dal;
        
        END
    SUSPEND;
END^
SET TERM ; ^

SET TERM ^ ;
ALTER TRIGGER COPIADELPREN
ACTIVE BEFORE DELETE POSITION 0
AS
declare variable codconto integer;
 
BEGIN 
	select first 1 codice from wghconto where wghconto.PRENOTAZIONE=old.CODICE into :codconto;
	if(codconto is null) then
        	begin
	        insert into wghdelpren select * from wghpren where codice=old.CODICE;
        	insert into wghdelprencam select * from WGHPRENCAM where WGHPRENCAM.CODPREN=old.CODICE;
        	update wghdelpren set wghdelpren.DATAPREN=current_date where WGHDELPREN.CODICE=old.CODICE;
        	end
	
END^

SET TERM ; ^