/******************************************************************************************/
/*				Mettre à jour les libellés du PDF préventif		  */
/******************************************************************************************/

DELETE from sous_items si
WHERE si.id_items=40493 and cd_sous_items like 'prev%';

INSERT into sous_items (cd_sous_items,lib_sous_item,id_items,date_creation) values ('prev1','LOCALISATION',40493,now());
INSERT into sous_items (cd_sous_items,lib_sous_item,id_items,date_creation) values ('prev2','CARACTERISTIQUES DU CONTROLEUR',40493,now());
INSERT into sous_items (cd_sous_items,lib_sous_item,id_items,date_creation) values ('prev3','MAINTENANCE PREVENTIVE SUR EQUIPEMENTS ELECTRONIQUES (cf $1.2.a CCTP)',40493,now());
INSERT into sous_items (cd_sous_items,lib_sous_item,id_items,date_creation) values ('prev4','MAINTENANCE PREVENTIVE HORS EQUIPEMENTS ELECTRONIQUES (cf $1.2.b CCTP)',40493,now());
INSERT into sous_items (cd_sous_items,lib_sous_item,id_items,date_creation) values ('prev5','EQUIPEMENTS STATIQUES',40493,now());
INSERT into sous_items (cd_sous_items,lib_sous_item,id_items,date_creation) values ('prev6','EXPLOITATION',40493,now());
              





/******************************************************************************************/
/*				Mettre à jour l'export préventif			  */
/******************************************************************************************/

CREATE OR REPLACE FUNCTION export_rapport_preventif (id_intervention integer) RETURNS VARCHAR AS $$
DECLARE
    inter RECORD;
    text varchar(500);
    id_inter varchar(500);
    ordre_sql varchar;
    nom_fich varchar;
    destination varchar;
    contenu varchar;
    requete varchar;
    ancien varchar;
    chemin varchar;
    
BEGIN

CREATE TABLE fichier(
societe_AAMM varchar(100),
num_installation varchar(100),
date_deb_interv_preventive varchar(100),
commentaire varchar(3000),
obs_arrivee_prev varchar(3000),
observation_depart_prev varchar(3000),
type_preventif varchar(50),
num_install varchar(100)
); 

SELECT value_ccfg 
FROM copixconfig where id_ccfg='default|url_export'
INTO chemin;

chemin := replace (chemin,'\\','/');
  
IF chemin is null OR chemin='' THEN
	drop table fichier;
	return -1;
END IF;

FOR inter IN
SELECT DISTINCT
		--ex:AXI0708
		substring(s.nom_societe, 1, 3)||'_'||substring(i.cd_intervention, 3, 4) AS societe_AAMM_interv,
		--numéro installation
		CASE when um.autre_reference1 is null 
		THEN ',00'
		ELSE um.autre_reference1||',00'
		END
		AS num_installation,
		--date/heure intervention
		i.date_debut_intervention AS date_deb_interv,
		--compte rendu intervention
		it.commentaires AS commentaire,
		--observations
		i.observations_arrivee AS obs_arrivee,
		i.observations_depart AS obs_depart,
		c.id_contrats AS idContrat
		FROM interventions i
		INNER JOIN unite_maintenance um ON um.id_unite_maintenance = i.id_unite_maintenance
		INNER JOIN intervention_taches it ON it.id_interventions = i.id_interventions
		INNER JOIN prestations_unite_maintenance pum ON pum.id_prestations_unite_maintenance = i.id_prestations_unite_maintenance
		INNER JOIN prestations p ON p.id_prestations = pum.id_prestations
		INNER JOIN contrats c ON c.id_contrats = p.id_contrats
		INNER JOIN societes s ON s.id_societes = c.id_societes
		WHERE i.type_intervention = 105 AND i.id_interventions = id_intervention
	
LOOP
		
		DELETE FROM fichier;
		INSERT INTO fichier VALUES(inter.societe_AAMM_interv, inter.num_installation ,to_char(inter.date_deb_interv,'DD/MM/YYYY HH:MI:SS'), inter.commentaire,
					   inter.obs_arrivee, inter.obs_depart);

		nom_fich := 'RP_' || inter.societe_AAMM_interv || '_' || substring(inter.num_installation, 1, 3) || '.gmao';
		destination := chemin || inter.idContrat || '/' || nom_fich;

		INSERT INTO export (nom_fichier, interv_prev, adresse, date_creation, id_contrats) VALUES (nom_fich, 'true', chemin, current_timestamp, inter.idContrat);

		requete :=' COPY fichier TO '''|| destination ||''' DELIMITER '';'' ';

		execute requete;
		
END LOOP;

drop table fichier;

return 1;
END;
$$ language plpgsql;




/******************************************************************************************/
/*				Mettre à jour l'export curatif				  */
/******************************************************************************************/


CREATE OR REPLACE FUNCTION export_rapport_curatif (id_intervention integer) RETURNS VARCHAR AS $$
DECLARE
    inter RECORD;
    text varchar(500);
    id_inter varchar(500);
    ordre_sql varchar;
    nom_fich varchar;
    destination varchar;
    contenu varchar;
    requete varchar;
    ancien varchar;
    chemin varchar;
    
BEGIN

CREATE TEMP TABLE fichier(
societePrestataire_numInterv varchar(1000),
autre_ref1 varchar(1000),
model varchar(1000),
nom_demandeur varchar(1000),
societe_demandeur varchar(1000),
id_dem_nature varchar(1000),
type_curatif varchar(1000),
journal_bord varchar(1000),
date_dem varchar(1000),
date_deb_interv varchar(1000),
date_fin_interv varchar(1000),
--com varchar(1000),
nom_technicien varchar(1000),
obs_arrivee varchar(1000),
obs_departcont varchar(1000),
demande_exceptionnelle varchar(1000)
); 

SELECT value_ccfg 
FROM copixconfig where id_ccfg='default|url_export'
INTO chemin;

chemin := replace (chemin,'\\','/');

IF chemin is null OR chemin='' THEN
	drop table fichier;
	return -1;
END IF;

FOR inter IN
		SELECT DISTINCT
		i.id_interventions,
		substring(s.nom_societe, 1, 3)||'_'|| i.cd_intervention AS societePrestataire_numInterv,
		substring(um.autre_reference1, 1, 5) AS autre_ref1,
		con.modele AS model,
		p2.nom_personne AS nom_demandeur,
		s.nom_societe AS societe_demandeur,
		dn.id_demandes_natures AS id_dem_nature,
		i.cd_intervention as cd_intervention,
		CASE i.type_intervention when 104 THEN 'C' 
		END
		AS type_curatif,
		d.date_demande AS date_dem,
		i.date_debut_intervention AS date_deb_interv,
		i.date_fin_intervention AS date_fin_interv,
		--iea.commentaires AS com,
		p.nom_personne AS nom_technicien,
		i.observations_arrivee AS obs_arrivee,
		i.observations_depart AS obs_depart,
		--'0' AS demande_exceptionnelle
		c.id_contrats as idContrat
		FROM interventions i
		INNER JOIN demandes d ON d.id_demandes = i.id_demandes
		INNER JOIN personnes p2 ON p2.id_personnes = d.demandeur
		INNER JOIN demandes_natures dn ON dn.id_demandes = d.id_demandes
		--INNER JOIN intervention_etat_arrivee iea ON iea.id_interventions = i.id_interventions
		INNER JOIN intervenants inte ON inte.id_interventions = i.id_interventions
		INNER JOIN personnes p ON p.id_personnes = inte.id_personnes
		INNER JOIN contrats c ON c.id_contrats = d.id_contrats
		INNER JOIN societes s ON s.id_societes = c.id_societes
		INNER JOIN unite_maintenance um ON d.id_unite_maintenance = um.id_unite_maintenance
		INNER JOIN controleur con ON con.id_unite_maintenance = um.id_unite_maintenance
		WHERE i.type_intervention = 104 and i.id_interventions = id_intervention
LOOP
		
		DELETE FROM fichier;
		INSERT INTO fichier VALUES(inter.societePrestataire_numInterv, inter.autre_ref1, inter.model, inter.nom_demandeur, 
					   inter.societe_demandeur, inter.id_dem_nature, inter.type_curatif, '', to_char(inter.date_dem,'DD/MM/YYYY HH:MI:SS'), to_char(inter.date_deb_interv,'DD/MM/YYYY HH:MI:SS'), 
				           to_char(inter.date_fin_interv,'DD/MM/YYYY HH:MI:SS'), inter.nom_technicien, inter.obs_arrivee,inter.obs_depart, 0);

		
		nom_fich := 'RI_AXI_0_'||inter.cd_intervention||'.gmao';
		destination := chemin || inter.idContrat || '/' || nom_fich;
		

		INSERT INTO export (nom_fichier, interv_prev, adresse, date_creation, id_contrats) VALUES (nom_fich, 'false', chemin, current_timestamp, inter.idContrat);
		requete :='COPY fichier TO ''' || destination || ''' DELIMITER '';'' ';
		
		execute requete;


END LOOP;

drop table fichier;

return 1;
END; 
$$ language plpgsql;


/******************************************************************************************/
/*				Mettre à jour la version	 			  */
/******************************************************************************************/

 update   copixconfig
set value_ccfg='GMAO V 1.4.5'
where id_ccfg='default|titlePage';