/* continue_if_error  */
CREATE TABLE RETROACTIVATION
(
id Integer NOT NULL,
isa Timestamp NOT NULL,
CONSTRAINT PK_RETROACTIVATION PRIMARY KEY (id)
);
/* continue_if_error  */
CREATE GENERATOR RETROACTIVATION;
SET GENERATOR RETROACTIVATION TO 1;

/* continue_if_error  */
CREATE TABLE RETROACTIVATION_PASSAGE
(
  ID Integer NOT NULL,
  MONTANT Numeric(10,2),
  INDIVIDU Integer,
  PORTE_MONNAIE Integer, 
  RESERVATION Integer,
  PASSAGE Integer,  
  QUOTIENT_FAMILIAL Integer,    
  R_MONTANT Numeric(10,2),
  R_MONTANT_TICKET Numeric(10,2),
  R_QUOTIENT_FAMILIAL Integer,    
  CONSTRAINT PK_RETROACTIVATION_PASSAGE PRIMARY KEY (ID)
);
/* continue_if_error  */
CREATE INDEX idx_RETROACTIVATION_INDIVIDU ON RETROACTIVATION_PASSAGE (INDIVIDU);

/* continue_if_error  */
CREATE TABLE RETROACTIVATION_PASSAGE_TEMP
(
  ID Integer NOT NULL,
  MONTANT Numeric(10,2),
  INDIVIDU Integer,
  PORTE_MONNAIE Integer, 
  RESERVATION Integer,
  PASSAGE Integer,  
  QUOTIENT_FAMILIAL Integer,    
  R_MONTANT Numeric(10,2),
  R_MONTANT_TICKET Numeric(10,2),
  R_QUOTIENT_FAMILIAL Integer,    
  CONSTRAINT PK_RETROACTIVATION_PASSAGE_TEMP PRIMARY KEY (ID)
);
/* continue_if_error  */
CREATE INDEX idx_RETROACTIVATION_TEMP_INDIV ON RETROACTIVATION_PASSAGE_TEMP (INDIVIDU);

/* continue_if_error  */
CREATE TABLE RETROACTIVATION_PASSAGE_LOG
(
  GEN_RETROACTIVATION_ID Integer NOT NULL,
  ID Integer NOT NULL,
  MONTANT Numeric(10,2),
  INDIVIDU Integer,
  PORTE_MONNAIE Integer, 
  RESERVATION Integer,
  PASSAGE Integer,  
  QUOTIENT_FAMILIAL Integer,    
  R_MONTANT Numeric(10,2),
  R_MONTANT_TICKET Numeric(10,2),
  R_QUOTIENT_FAMILIAL Integer
);
--------------------------------


  SET TERM ^ ;

/* continue_if_error  */
CREATE PROCEDURE sp_Qf_Retroactivity 
(
    P_START_DATE Date
)
AS 
DECLARE VARIABLE minPassageTrimestreId integer; 
DECLARE VARIABLE GEN_RETROACTIVATION_ID integer; 
DECLARE VARIABLE DebutTCourant date;
DECLARE VARIABLE DebutTraiement date;
declare variable maxIdPassage integer; 
BEGIN

	select max(id) from passage into :maxIdPassage;
    
    select case pg.trimestrecourant when 1 then pg.debutt1 when 2 then pg.debutt2 else pg.debutt3 end from PARAMETRES_GENERAUX pg into :DebutTCourant;
	
	select case when :P_START_DATE < :DebutTCourant then :DebutTCourant else :P_START_DATE end from RDB$DATABASE into DebutTraiement;
	 
	select coalesce(min(p.id), :maxIdPassage) from passage p where p.jour >= :DebutTraiement into :minPassageTrimestreId; 
	 
	-- Suppression des données de la table temporaire et table de travail 	
	DELETE FROM RETROACTIVATION_PASSAGE_TEMP;
	DELETE FROM RETROACTIVATION_PASSAGE;
  
	-- Ajout de tous les passages dans la table RETROACTIVATION_PASSAGE_TEMP avec un id > minId passage
	INSERT INTO RETROACTIVATION_PASSAGE_TEMP (
		ID ,
		MONTANT ,
		INDIVIDU ,
		PORTE_MONNAIE , 
		RESERVATION ,
		PASSAGE ,  
		QUOTIENT_FAMILIAL
	)

	SELECT 
		P.ID ,
		P.MONTANT ,
		P.INDIVIDU ,
		P.PORTE_MONNAIE , 
		P.RESERVATION ,
		P.PASSAGE ,  
		P.QUOTIENT_FAMILIAL
	FROM PASSAGE P
	WHERE
	P.ID > :minPassageTrimestreId;
  
  
	-- Insertion dans la table RETROACTIVATION_PASSAGE des passages qui n'ont pas le bon montant par rapport au qf donné
	-- Cet insert est plus rapide à partir de la table temporaire grace à l'ajout d'index
  
	INSERT INTO RETROACTIVATION_PASSAGE (
		ID ,
		MONTANT ,
		INDIVIDU ,
		PORTE_MONNAIE , 
		RESERVATION ,
		PASSAGE ,  
		QUOTIENT_FAMILIAL,
		R_MONTANT,
		R_MONTANT_TICKET,
		R_QUOTIENT_FAMILIAL
	)

	SELECT 
		P.ID ,
		P.MONTANT ,
		P.INDIVIDU ,
		P.PORTE_MONNAIE , 
		P.RESERVATION ,
		P.PASSAGE ,  
		P.QUOTIENT_FAMILIAL,
		qf.MONTANT,
		qf.MONTANT_TICKET,
		qf.ID
	FROM IMPORT_TRANCHE_DATA ITD
	INNER JOIN INDIVIDU I
		ON I.NUM_INTERNE = ITD.CODE_ELEVE
	INNER JOIN ETABLISSEMENT E 
		ON E.NUMERO = ITD.RNE
	INNER JOIN REGIME R
		ON R.ID = I.REGIME
	INNER JOIN REGIME_GFE_ORCADIA RGO 
		ON RGO.REGIME_ORCADIA_ID = R.ID
	INNER JOIN REGIME_GFE_QF_TYPE GFEQFT 
		ON GFEQFT.REGIME_ID = RGO.REGIME_GFE_ID
	INNER JOIN DIVISION D 
		on D.ID = I.DIVISION
	INNER JOIN QUOTIENT_FAMILIAL QF 
		on I.QUOTIENT_FAMILIAL = QF.ID
	INNER JOIN RETROACTIVATION_PASSAGE_TEMP P 
		ON P.INDIVIDU = I.ID

	WHERE
	QF.ANNEE = (select ANNEECOURANTE from PARAMETRES_GENERAUX)
	AND QF.QFTT_ID = GFEQFT.QFTT_ID
	AND QF.POSTBAC =  (SELECT CASE D.OGIL_STATUT WHEN 'POSTBAC' THEN 1 ELSE 0 END FROM RDB$DATABASE)
	AND I.QUOTIENT_FAMILIAL = QF.ID
	AND P.QUOTIENT_FAMILIAL <> QF.ID;

	-- A ce point nous avons connaissons les passages à modifier.

	-- On pousse les passages dans la table de log
	SELECT GEN_ID(RETROACTIVATION, 1) from RDB$DATABASE into :GEN_RETROACTIVATION_ID;
  
	INSERT INTO RETROACTIVATION(ID, ISA) 
	SELECT :GEN_RETROACTIVATION_ID, cast('NOW' as timestamp) from rdb$database; 
  
	INSERT INTO RETROACTIVATION_PASSAGE_LOG 
	(
		GEN_RETROACTIVATION_ID,
		ID,
		MONTANT,
		INDIVIDU,
		PORTE_MONNAIE, 
		RESERVATION,
		PASSAGE,  
		QUOTIENT_FAMILIAL,    
		R_MONTANT ,
		R_MONTANT_TICKET ,
		R_QUOTIENT_FAMILIAL
	)
	SELECT
		:GEN_RETROACTIVATION_ID,
		ID,
		MONTANT,
		INDIVIDU,
		PORTE_MONNAIE, 
		RESERVATION,
		PASSAGE,  
		QUOTIENT_FAMILIAL,    
		R_MONTANT ,
		R_MONTANT_TICKET ,
		R_QUOTIENT_FAMILIAL
	FROM
	RETROACTIVATION_PASSAGE;
  
	-- On met à jour les passages
	UPDATE PASSAGE
	SET 
		QUOTIENT_FAMILIAL = 
			(SELECT PR.R_QUOTIENT_FAMILIAL FROM RETROACTIVATION_PASSAGE PR WHERE PR.ID = PASSAGE.ID),
		MONTANT = 
			(select case PM.sous_service when 2 then PR.R_MONTANT_TICKET else PASSAGE.MONTANT end 
			FROM RETROACTIVATION_PASSAGE PR INNER JOIN PORTE_MONNAIE PM on PM.ID = PR.PORTE_MONNAIE WHERE PASSAGE.ID = PR.ID )
	WHERE EXISTS 
	(SELECT * FROM RETROACTIVATION_PASSAGE PR WHERE PR.ID = PASSAGE.ID);
  
  
END^

SET TERM ; ^
