-- VUE RESERVOIRS : typeInstallation=12
drop view v_eau17sig_reservoirs;
create view v_eau17sig_reservoirs as
select identifiant, 
	LEX_Etat.libelle fonctionnement,
	nbCuves, 
	LEX_Natures_Cuves.libelle natureCuves,
	LEX_Revetements_Cuves.libelle RevetementCuve,
	LEX_Mode_Rempl_Cuves.libelle ModeRemplissage, 
	case when EQUIP_Turbidimetre.CodeEquipement is not null then true else false end presenceTurbidimetre, 
	case when EQUIP_Chlore.CodeEquipement is not null then true else false end presencAnalyseurChlore,
	case when EQUIP_Colonne_Seche.CodeProtection is not null then true else false end colonneSeche, 
	case when EQUIP_Fontaine.CodeEquipement is not null then true else false end Fontaine,
	LEX_Detection_Niveau.libelle typeDetectionNiveau,
	LEX_Mesure_Debit.libelle typeMesureDebitmetrique,
	case when EQUIP_Bypass.CodeEquipement is not null then true else false end presenceBypass,
	case when EQUIP_RobinetFlotteur.CodeEquipement is not null then true else false end presenceRobinetFlotteur,
	case when EQUIP_AntiIntrusion.CodeEquipement is not null then true else false end presenceAntiIntrusion,
	hastelegestion,	
	aep_installations.loginMaj, 
	aep_installations.dateMaj
from aep_installations left join inst_reservoir_gc on (aep_installations.codeinstallation=inst_reservoir_gc.codeinstallation)
left join inst_electricite_com on (aep_installations.codeinstallation=inst_electricite_com.codeinstallation)
left join codes_sandre LEX_Etat on (aep_installations.statut=LEX_Etat.code and LEX_Etat.champ='CODE_ETAT')
left join codes_sandre LEX_Natures_Cuves on (inst_reservoir_gc.natureCuve=LEX_Natures_Cuves.code and LEX_Natures_Cuves.champ='CUVES.MATERIAU')
left join codes_sandre LEX_Revetements_Cuves on (inst_reservoir_gc.revetementCuve=LEX_Revetements_Cuves.code and LEX_Revetements_Cuves.champ='INSTALLATIONS.REVETEMENTCUVE')
left join codes_sandre LEX_Mode_Rempl_Cuves on (inst_reservoir_gc.modeRemplissage=LEX_Mode_Rempl_Cuves.code and LEX_Mode_Rempl_Cuves.champ='INSTALLATIONS.MODEREMPLISSAGE')
left join inst_equipements_detail EQUIP_Turbidimetre on (aep_installations.codeinstallation=EQUIP_Turbidimetre.codeInstallation and EQUIP_Turbidimetre.codeEquipement=0) 
left join inst_equipements_detail EQUIP_Chlore on (aep_installations.codeinstallation=EQUIP_Chlore.codeInstallation and EQUIP_Chlore.codeEquipement=1) 
left join inst_equipements_detail EQUIP_Fontaine on (aep_installations.codeinstallation=EQUIP_Fontaine.codeInstallation and EQUIP_Fontaine.codeEquipement=3) 
left join inst_equipements_detail EQUIP_AntiIntrusion on (aep_installations.codeinstallation=EQUIP_AntiIntrusion.codeInstallation and EQUIP_AntiIntrusion.codeEquipement=39) 
left join inst_securite_personnel EQUIP_Colonne_Seche on (aep_installations.codeinstallation=EQUIP_Colonne_Seche.codeInstallation and EQUIP_Colonne_Seche.codeProtection=4) 
left join INST_reservoir_equipements on (aep_installations.codeinstallation=INST_reservoir_equipements.codeInstallation)
left join codes_sandre LEX_Detection_Niveau on (INST_reservoir_equipements.typeDetectionNiveau=LEX_Detection_Niveau.code and LEX_Detection_Niveau.champ='INSTALLATIONS.TYPEDETECTION')
left join codes_sandre LEX_Mesure_Debit on (INST_reservoir_equipements.mesureDebitmetrique=LEX_Mesure_Debit.code and LEX_Mesure_Debit.champ='INSTALLATIONS.MESUREDEBIT')
left join inst_equipements_detail EQUIP_Bypass on (aep_installations.codeinstallation=EQUIP_Bypass.codeInstallation and EQUIP_Bypass.codeEquipement=4) 
left join inst_equipements_detail EQUIP_RobinetFlotteur on (aep_installations.codeinstallation=EQUIP_RobinetFlotteur.codeInstallation and EQUIP_RobinetFlotteur.codeEquipement=5) 
where typeinstallation=12;

-- VUE STATIONS POMPAGES : typeInstallation=10
drop view v_eau17sig_stations_pompages;
create view v_eau17sig_stations_pompages as
select identifiant, 
	capaciteNominale, 
	capaciteHoraire,
	LEX_Etat.libelle fonctionnement,
	batiment,
	case when GROUPE1.nomgroupe  is null then 0 else 1 end
	+ case when GROUPE2.nomgroupe is null then 0 else 1 end
	+ case when GROUPE3.nomgroupe is null then 0 else 1 end
	nbGroupes,
	GROUPE1.nomgroupe nomGroupe1,
	GROUPE1.debitnominal debitnominal1,
	GROUPE1.hmt hmt1,
	GROUPE1.nbpompes nbpompes1,
	GROUPE2.nomgroupe nomGroupe2,
	GROUPE2.debitnominal debitnominal2,
	GROUPE2.hmt hmt2,
	GROUPE2.nbpompes nbpompes2,
	GROUPE3.nomgroupe nomGroupe3,
	GROUPE3.debitnominal debitnominal3,
	GROUPE3.hmt hmt3,
	GROUPE3.nbpompes nbpompes3,
	case when EQUIP_Turbidimetre.CodeEquipement is not null then true else false end presenceTurbidimetre, 
	case when EQUIP_Chlore.CodeEquipement is not null then true else false end presencAnalyseurChlore,
	case when EQUIP_Groupe_electrogene.CodeEquipement is not null then true else false end presenceGroupeElectrogene,
	case when EQUIP_AntiIntrusion.CodeEquipement is not null then true else false end presenceAntiIntrusion,
	hastelegestion,
	transformateur,
	demarreur,
	variateur,
	LEX_Mesure_Debit.libelle typeMesureDebitmetrique,
	aep_installations.loginMaj, 
	aep_installations.dateMaj
from aep_installations left join inst_st_pompage_aep on (aep_installations.codeinstallation=inst_st_pompage_aep.codeinstallation)
left join inst_electricite_com on (aep_installations.codeinstallation=inst_electricite_com.codeinstallation)
left join inst_st_pompage_aep_equipements GROUPE1 on (aep_installations.codeinstallation=GROUPE1.codeinstallation and (GROUPE1.numgroupe=1 or GROUPE1.numgroupe is null))
left join inst_st_pompage_aep_equipements GROUPE2 on (aep_installations.codeinstallation=GROUPE2.codeinstallation and (GROUPE2.numgroupe=1 or GROUPE2.numgroupe is null))
left join inst_st_pompage_aep_equipements GROUPE3 on (aep_installations.codeinstallation=GROUPE3.codeinstallation and (GROUPE3.numgroupe=1 or GROUPE3.numgroupe is null))
left join codes_sandre LEX_Etat on (aep_installations.statut=LEX_Etat.code and LEX_Etat.champ='CODE_ETAT')
left join inst_equipements_detail EQUIP_Turbidimetre on (aep_installations.codeinstallation=EQUIP_Turbidimetre.codeInstallation and EQUIP_Turbidimetre.codeEquipement=0) 
left join inst_equipements_detail EQUIP_Chlore on (aep_installations.codeinstallation=EQUIP_Chlore.codeInstallation and EQUIP_Chlore.codeEquipement=1) 
left join inst_equipements_detail EQUIP_Groupe_electrogene on (aep_installations.codeinstallation=EQUIP_Groupe_electrogene.codeInstallation and EQUIP_Groupe_electrogene.codeEquipement=76) 
left join inst_equipements_detail EQUIP_AntiIntrusion on (aep_installations.codeinstallation=EQUIP_AntiIntrusion.codeInstallation and EQUIP_AntiIntrusion.codeEquipement=39) 
left join inst_st_pompage_aep_equipements_divers on (aep_installations.codeinstallation=inst_st_pompage_aep_equipements_divers.codeInstallation)
left join codes_sandre LEX_Mesure_Debit on (inst_st_pompage_aep_equipements_divers.mesureDebitmetrique=LEX_Mesure_Debit.code and LEX_Mesure_Debit.champ='INSTALLATIONS.MESUREDEBIT')
where typeinstallation=10;


-- VUE STATIONS TRAITEMENTS : typeInstallation=11
drop view v_eau17sig_stations_traitements;
create view v_eau17sig_stations_traitements as
select identifiant, 
	batiment,
	capaciteNominale,
	capacitehoraire,
	parametrestraites,
	LEX_FiliereTraitement.libelle filiereTraitement,
	numarrete arreteAutorisationOuvrage,
	dateArrete dateArreteAutorisationOuvrage,
	autorisationRejet,
	dateAutorisationRejet,
	aep_installations.dateMiseEnService dateMiseEnRoute,
	LEX_Etat.libelle fonctionnement,
	case when EQUIP_AntiIntrusion.CodeEquipement is not null then true else false end presenceAntiIntrusion,
	hastelegestion,
	case when EQUIP_Debimetre.CodeEquipement is not null then true else false end presenceDebimetre,
	case when dispositifSecours is not null then true else false end inverseurSource,
	aep_installations.loginMaj, 
	aep_installations.dateMaj
from aep_installations left join inst_electricite_com on (aep_installations.codeinstallation=inst_electricite_com.codeinstallation)
left join inst_equipements_detail EQUIP_Debimetre on (aep_installations.codeinstallation=EQUIP_Debimetre.codeInstallation and EQUIP_Debimetre.codeEquipement=33) 
left join inst_st_traitement_gestion on (aep_installations.codeinstallation=inst_st_traitement_gestion.codeinstallation)
left join codes_sandre LEX_Etat on (aep_installations.statut=LEX_Etat.code and LEX_Etat.champ='CODE_ETAT')
left join codes_sandre LEX_FiliereTraitement on (inst_st_traitement_gestion.filiereTraitement=LEX_FiliereTraitement.code and LEX_FiliereTraitement.champ='NSTALLATIONS.FILIERETRAITEMENT')
left join inst_equipements_detail EQUIP_AntiIntrusion on (aep_installations.codeinstallation=EQUIP_AntiIntrusion.codeInstallation and EQUIP_AntiIntrusion.codeEquipement=39) 
where typeinstallation=11;


-- VUE CAPTAGES : typeInstallation=0 (Forages)
drop view v_eau17sig_captages;
create view v_eau17sig_captages as
select identifiant, 
	LEX_Type.libelle,
	aquiferes.libelle nappe,
	LEX_Gisement.libelle modeGisement,
	aep_installations.dateFin dateDesaffectation,
	LEX_Etat.libelle fonctionnement,
	profondeurforage,
	diametreinternetubage,
	epaisseurTubage,
	LEX_Tubage.libelle natureTubage,
	profondeurTubage,
	ouvrageCrepine,
	profPompe,
	coteBride,
	coteRepere,
	margelleProtection,
	capotage,
	teteEtanche,
	derniereInspection,
	debitNominal,
	HMT,
	LEX_TypePompe.libelle typePompe,
	diametreExterieurColonneExhaure,
	LEX_TypeColonneExhaure.libelle typeColonneExhaure,
	LEX_NatureColonneExhaure.libelle natureColonneExhaure,
	LEX_TypeDetectionNiveau.libelle typeDetectionNiveau,
	forages_gc.profondeursonde,
	case when EQUIP_Ventouse.codeEquipement is not null then true else false end ventouse,
	case when EQUIP_MesureDebit.codeEquipement is not null then true else false end mesureDebimetrique,
	case when EQUIP_Clapet.codeEquipement is not null then true else false end clapetAntiRetour,
	case when EQUIP_AntiIntrusion.CodeEquipement is not null then true else false end presenceAntiIntrusion,
	nbSystemes,
	hasTelegestion,
	aep_installations.loginMaj, 
	aep_installations.dateMaj
from aep_installations left join inst_forages on (aep_installations.codeInstallation=inst_forages.codeInstallation)
left join forages_gc on (aep_installations.codeInstallation=forages_gc.codeInstallation)
left join forages_equipements on (aep_installations.codeInstallation=forages_equipements.codeInstallation)
left join inst_equipements_detail EQUIP_Ventouse on (aep_installations.codeInstallation=EQUIP_Ventouse.codeInstallation and (EQUIP_Ventouse.codeEquipement=0 or EQUIP_Ventouse.codeEquipement is null))
left join inst_equipements_detail EQUIP_MesureDebit on (aep_installations.codeInstallation=EQUIP_MesureDebit.codeInstallation and (EQUIP_MesureDebit.codeEquipement=1 or EQUIP_MesureDebit.codeEquipement is null))
left join inst_equipements_detail EQUIP_Clapet on (aep_installations.codeInstallation=EQUIP_Clapet.codeInstallation and (EQUIP_Clapet.codeEquipement=2 or EQUIP_Clapet.codeEquipement is null))
left join codes_sandre LEX_Gisement on (inst_forages.modeGisement=LEX_Gisement.code and LEX_Gisement.champ='PIEZOMETRES.MODEGISEMENT')
left join codes_sandre LEX_Etat on (aep_installations.statut=LEX_Etat.code and LEX_Etat.champ='CODE_ETAT')
left join codes_sandre LEX_Tubage on (forages_gc.natureTubage=LEX_Tubage.code and LEX_Tubage.champ='INSTALLATIONS.NATUREEQUIPEMENT')
left join lien_forages_aquifere on (aep_installations.codeInstallation=lien_forages_aquifere.codeinstallation and (numeroOrdre=0 or numeroOrdre is null))
left join aquiferes on (lien_forages_aquifere.codeAquifere=aquiferes.codeAquifere)
left join codes_sandre LEX_Type on (inst_forages.codeNature=LEX_Type.code and LEX_Type.champ='PIEZOMETRES.CODENATURE')
left join codes_sandre LEX_TypePompe on (forages_equipements.typePompe=LEX_TypePompe.code and LEX_TypePompe.champ='INSTALLATIONS.TYPEPOMPE')
left join codes_sandre LEX_TypeColonneExhaure on (forages_equipements.typeColonneExhaure=LEX_TypeColonneExhaure.code and LEX_TypeColonneExhaure.champ='INSTALLATIONS.TYPECOLONNEEXHAURE')
left join codes_sandre LEX_NatureColonneExhaure on (forages_equipements.natureColonneExhaure=LEX_TypeColonneExhaure.code and LEX_TypeColonneExhaure.champ='INSTALLATIONS.NATURECOLONNEEXHAURE')
left join codes_sandre LEX_TypeDetectionNiveau on (forages_equipements.typeDetectionNiveau=LEX_TypeDetectionNiveau.code and LEX_TypeDetectionNiveau.champ='INSTALLATIONS.TYPEDETECTION')
left join (select codeinstallation, count(*) nbSystemes from inst_securite group by codeinstallation) systemeSecurite on (aep_installations.codeinstallation=systemeSecurite.codeInstallation)
left join inst_electricite_com on (aep_installations.codeInstallation=inst_electricite_com.codeinstallation)
left join inst_equipements_detail EQUIP_AntiIntrusion on (aep_installations.codeinstallation=EQUIP_AntiIntrusion.codeInstallation and EQUIP_AntiIntrusion.codeEquipement=39) 
where typeinstallation=0 and aep_installations.jobexecutionid is null;

-- VUE STEP : typeInstallation=8
drop view v_eau17sig_step;
create view v_eau17sig_step as
select identifiant, 
	capaciteTraitement,
	LEX_FiliereTraitementBoues.libelle filiereTraitementBoues,
	LEX_FiliereTraitementEauxUsees.libelle filiereTraitementEauxUsees,
	LEX_FiliereEliminationBoues.libelle
	laguneFinition,
	LEX_TypeRejet.libelle typeRejet,
	reutilisationEauUsee,
	uniteCurage,
	uniteGraisse,
	uniteVidange,
	dateArrete,
	aep_installations.dateMiseEnService dateMiseEnRoute,
	LEX_Etat.libelle fonctionnement,
	presencebassinTampon,
	volumebassinTampon,
	case when nbBatiments is not null then false when null then false else true end batiment,
	aep_installations.loginMaj, 
	aep_installations.dateMaj
from aep_installations left join inst_step on (aep_installations.codeInstallation=inst_step.codeInstallation)
left join inst_step_gc on (aep_installations.codeInstallation=inst_step_gc.codeInstallation)
left join codes_sandre LEX_FiliereTraitementBoues on (inst_step.filiereTraitementEauxUsees=LEX_FiliereTraitementBoues.code and LEX_FiliereTraitementBoues.champ='INSTALLATIONS.FILIERETRAITEMENTEBOUES')
left join codes_sandre LEX_FiliereTraitementEauxUsees on (inst_step.filiereTraitementEauxUsees=LEX_FiliereTraitementEauxUsees.code and LEX_FiliereTraitementEauxUsees.champ='INSTALLATIONS.FILIERETRAITEMENTEAUXUSEES')
left join codes_sandre LEX_TypeRejet on (inst_step.typeRejet=LEX_TypeRejet.code and LEX_TypeRejet.champ='INSTALLATIONS.TYPEREJET')
left join codes_sandre LEX_FiliereEliminationBoues on (inst_step.filiereEliminationBoues=LEX_FiliereEliminationBoues.code and LEX_FiliereEliminationBoues.champ='INSTALLATIONS.FILIEREELIMINATIONBOUES')
left join codes_sandre LEX_Etat on (aep_installations.statut=LEX_Etat.code and LEX_Etat.champ='CODE_ETAT')
left join (select codeinstallation, count(*) nbBatiments from inst_step_gc_batiments group by codeinstallation) installations_batiments on (aep_installations.codeInstallation=installations_batiments.codeInstallation)
where typeinstallation=8;


-- VUE POSTES DE RELEVAGES : typeInstallation=9
drop view v_eau17sig_poste_relevage;
create view v_eau17sig_poste_relevage as
select identifiant, 
	LEX_Etat.libelle fonctionnement,
	LEX_Natures_Cuves.libelle natureCuves,
	revetementAntiAcide,
	coteAltiDessusPoste,
	coteEntreeFilEau,
	coteFondCuve,
	profondeur,
	tropPlein,
	coteTropPlein,
	bassinTampon,
	nbPompes,
	debitNominal,
	HMT,
	case when EQUIP_Brassage.CodeEquipement is not null then true else false end dispositifBrassage,
	case when EQUIP_AntiBelier.CodeEquipement is not null  then true else false end antiBelier,
	case when EQUIP_TraitementH2S.CodeEquipement is not null then true else false end traitementH2S,
	case when EQUIP_MesureDebimetrique.CodeEquipement is not null  then true else false end mesureDebimetrique,
	case when EQUIP_Desodorisation.CodeEquipement is not null  then true else false end desodorisation,
	case when EQUIP_VentouseChambreVannes.CodeEquipement is not null  then true else false end ventouseChambreVannes,
	case when EQUIP_VanneIsolement.CodeEquipement is not null  then true else false end vanneIsolement,
	case when EQUIP_BranchementAEP.CodeEquipement is not null  then true else false end branchementAEP,
	case when EQUIP_Groupe_electrogene.CodeEquipement is not null  then true else false end groupeElectrogene,
	case when EQUIP_BarreAntiChute.CodeProtection is not null  then true else false end barreAntiChute,
	hasTelegestion,
	case when dispositifSecours is not null then true else false end inverseurSource,
	aep_installations.loginMaj, 
	aep_installations.dateMaj
from aep_installations left join inst_poste_relevage_gc on (aep_installations.codeInstallation=inst_poste_relevage_gc.codeinstallation)
left join inst_poste_relevage_equipements on (aep_installations.codeInstallation=inst_poste_relevage_equipements.codeinstallation)
left join inst_electricite_com on (aep_installations.codeinstallation=inst_electricite_com.codeinstallation)
left join inst_securite_personnel EQUIP_BarreAntiChute on (aep_installations.codeInstallation=EQUIP_BarreAntiChute.codeinstallation and EQUIP_BarreAntiChute.codeProtection=2) 
left join codes_sandre LEX_Natures_Cuves on (inst_poste_relevage_gc.natureCuve=LEX_Natures_Cuves.code and LEX_Natures_Cuves.champ='CUVES.MATERIAU')
left join codes_sandre LEX_Etat on (aep_installations.statut=LEX_Etat.code and LEX_Etat.champ='CODE_ETAT')
left join inst_equipements_detail EQUIP_Brassage on (aep_installations.codeinstallation=EQUIP_Brassage.codeInstallation and EQUIP_Brassage.codeEquipement=0) 
left join inst_equipements_detail EQUIP_AntiBelier on (aep_installations.codeinstallation=EQUIP_AntiBelier.codeInstallation and EQUIP_AntiBelier.codeEquipement=1) 
left join inst_equipements_detail EQUIP_TraitementH2S on (aep_installations.codeinstallation=EQUIP_TraitementH2S.codeInstallation and EQUIP_TraitementH2S.codeEquipement=2) 
left join inst_equipements_detail EQUIP_MesureDebimetrique on (aep_installations.codeinstallation=EQUIP_MesureDebimetrique.codeInstallation and EQUIP_MesureDebimetrique.codeEquipement=3) 
left join inst_equipements_detail EQUIP_Desodorisation on (aep_installations.codeinstallation=EQUIP_Desodorisation.codeInstallation and EQUIP_Desodorisation.codeEquipement=4) 
left join inst_equipements_detail EQUIP_VentouseChambreVannes on (aep_installations.codeinstallation=EQUIP_VentouseChambreVannes.codeInstallation and EQUIP_VentouseChambreVannes.codeEquipement=5) 
left join inst_equipements_detail EQUIP_VanneIsolement on (aep_installations.codeinstallation=EQUIP_VanneIsolement.codeInstallation and EQUIP_VanneIsolement.codeEquipement=6) 
left join inst_equipements_detail EQUIP_BranchementAEP on (aep_installations.codeinstallation=EQUIP_BranchementAEP.codeInstallation and EQUIP_BranchementAEP.codeEquipement=7) 
left join inst_equipements_detail EQUIP_Groupe_electrogene on (aep_installations.codeinstallation=EQUIP_Groupe_electrogene.codeInstallation and EQUIP_Groupe_electrogene.codeEquipement=76) 
where typeinstallation=9;
