drop view public.v_eau17sig_step;
drop view eau17_sig;
drop view public.v_eau17sig_stations_traitements;
drop view public.v_eau17sig_stations_pompages;
drop view public.v_eau17sig_reservoirs;
drop view public.v_eau17sig_poste_relevage;
drop view public.v_eau17sig_captages;
drop view public.v_eau17sig_stations_traitements;
drop view public.eau17sig_reservoirs;







CREATE OR REPLACE VIEW public.v_eau17sig_step
WITH(security_barrier=false)
AS SELECT aep_installations.identifiant,
    inst_step.capacitetraitement,
    lex_filieretraitementboues.libelle AS filieretraitementboues,
    lex_filieretraitementeauxusees.libelle AS filieretraitementeauxusees,
    lex_filiereeliminationboues.libelle AS filiereeliminationboues,
    inst_step.lagunefinition,
    lex_typerejet.libelle AS typerejet,
    inst_step.reutilisationeauusee,
    inst_step.unitecurage,
    inst_step.unitegraisse,
    inst_step.unitevidange,
    inst_step.datearrete,
    aep_installations.datemiseenservice AS datemiseenroute,
    lex_etat.libelle AS fonctionnement,
    inst_step_gc.presencebassintampon,
    inst_step_gc.volumebassintampon,
        CASE
            WHEN installations_batiments.nbbatiments = 0 THEN false
            WHEN installations_batiments.nbbatiments IS NULL THEN false
            ELSE true
        END AS batiment,
    installations_batiments.surfaces,
    lex_mesureentree.libelle AS dispositifmesureentree,
    lex_mesuresortie.libelle AS dispositifmesuresortie,
        CASE
            WHEN lex_bypass.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS bypassentree,
        CASE
            WHEN lex_piezo.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS piezometre,
    inst_electricite_com.hastelegestion,
    inst_electricite_com.hassupervision,
    inst_electricite_com.transformateur = ANY (ARRAY['2'::bigint, '3'::bigint, '4'::bigint]) AS transformateur,
        CASE
            WHEN equip_groupe_electrogene.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS groupelectrogene,
    inst_electricite_com.installationarmoire AS inverseursource,
    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.filieretraitementboues = lex_filieretraitementboues.code AND lex_filieretraitementboues.champ::text = 'INSTALLATIONS.FILIERETRAITEMENTEBOUES'::text
     LEFT JOIN codes_sandre lex_filieretraitementeauxusees ON inst_step.filieretraitementeauxusees = lex_filieretraitementeauxusees.code AND lex_filieretraitementeauxusees.champ::text = 'INSTALLATIONS.FILIERETRAITEMENTEAUXUSEES'::text
     LEFT JOIN codes_sandre lex_typerejet ON inst_step.typerejet = lex_typerejet.code AND lex_typerejet.champ::text = 'INSTALLATIONS.TYPEREJET'::text
     LEFT JOIN codes_sandre lex_filiereeliminationboues ON inst_step.filiereeliminationboues = lex_filiereeliminationboues.code AND lex_filiereeliminationboues.champ::text = 'INSTALLATIONS.FILIEREELIMINATIONBOUES'::text
     LEFT JOIN codes_sandre lex_etat ON aep_installations.codeetat = lex_etat.code AND lex_etat.champ::text = 'CODE_ETAT'::text
     LEFT JOIN ( SELECT inst_batiments.codeinstallation,
            count(*) AS nbbatiments,
            sum(inst_batiments.surfacebatiment) AS surfaces
           FROM inst_batiments
          WHERE inst_batiments.typebatiment = 4
          GROUP BY inst_batiments.codeinstallation) installations_batiments ON aep_installations.codeinstallation = installations_batiments.codeinstallation
     LEFT JOIN inst_step_equipements ON aep_installations.codeinstallation = inst_step_equipements.codeinstallation
     LEFT JOIN inst_electricite_com ON aep_installations.codeinstallation = inst_electricite_com.codeinstallation
     LEFT JOIN codes_sandre lex_mesureentree ON inst_step_equipements.mesurevolumeentree = lex_mesureentree.code AND lex_mesureentree.champ::text = 'INSTALLATIONS.MESUREDEBIT'::text
     LEFT JOIN codes_sandre lex_mesuresortie ON inst_step_equipements.mesurevolumesortie = lex_mesuresortie.code AND lex_mesuresortie.champ::text = 'INSTALLATIONS.MESUREDEBIT'::text
     LEFT JOIN inst_equipements_detail lex_bypass ON aep_installations.codeinstallation = lex_bypass.codeinstallation AND lex_bypass.codeequipement = 0
     LEFT JOIN inst_equipements_detail lex_piezo ON aep_installations.codeinstallation = lex_piezo.codeinstallation AND lex_piezo.codeequipement = 1
     LEFT JOIN inst_equipements_detail equip_groupe_electrogene ON aep_installations.codeinstallation = lex_bypass.codeinstallation AND lex_bypass.codeequipement = 0
  WHERE aep_installations.typeinstallation = 2;
 
 CREATE OR REPLACE VIEW public.v_eau17sig_stations_traitements
WITH(security_barrier=false)
AS SELECT aep_installations.identifiant,
    inst_st_traitement_gestion.batiment,
    inst_st_traitement_gestion.capacitenominale,
    inst_st_traitement_gestion.capacitehoraire,
    inst_st_traitement_gestion.parametrestraites,
    lex_filieretraitement.libelle AS filieretraitement,
    inst_st_traitement_gestion.numarrete AS arreteautorisationouvrage,
    inst_st_traitement_gestion.datearrete AS datearreteautorisationouvrage,
    inst_st_traitement_gestion.autorisationrejet,
    inst_st_traitement_gestion.dateautorisationrejet,
    aep_installations.datemiseenservice AS datemiseenroute,
    lex_etat.libelle AS fonctionnement,
        CASE
            WHEN equip_antiintrusion.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presenceantiintrusion,
    inst_electricite_com.hastelegestion,
        CASE
            WHEN equip_debimetre.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presencedebimetre,
    inst_electricite_com.installationarmoire AS inverseursource,
    installations_batiments.surfaces,
    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.codeetat = lex_etat.code AND lex_etat.champ::text = 'CODE_ETAT'::text
     LEFT JOIN codes_sandre lex_filieretraitement ON inst_st_traitement_gestion.filieretraitement = lex_filieretraitement.code AND lex_filieretraitement.champ::text = 'NSTALLATIONS.FILIERETRAITEMENT'::text
     LEFT JOIN inst_equipements_detail equip_antiintrusion ON aep_installations.codeinstallation = equip_antiintrusion.codeinstallation AND equip_antiintrusion.codeequipement = 39
     LEFT JOIN ( SELECT inst_batiments.codeinstallation,
            count(*) AS nbbatiments,
            sum(inst_batiments.surfacebatiment) AS surfaces
           FROM inst_batiments
          WHERE inst_batiments.typebatiment = 4
          GROUP BY inst_batiments.codeinstallation) installations_batiments ON aep_installations.codeinstallation = installations_batiments.codeinstallation
  WHERE aep_installations.typeinstallation = 11;
 -- public.v_eau17sig_stations_pompages source

CREATE OR REPLACE VIEW public.v_eau17sig_stations_pompages
AS SELECT aep_installations.identifiant,
    inst_st_pompage_aep.capacitenominale,
    inst_st_pompage_aep.capacitehoraire,
    lex_etat.libelle AS fonctionnement,
    inst_st_pompage_aep.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 AS nbgroupes,
    groupe1.nomgroupe AS nomgroupe1,
    groupe1.debitnominal AS debitnominal1,
    groupe1.hmt AS hmt1,
    groupe1.nbpompes AS nbpompes1,
    groupe2.nomgroupe AS nomgroupe2,
    groupe2.debitnominal AS debitnominal2,
    groupe2.hmt AS hmt2,
    groupe2.nbpompes AS nbpompes2,
    groupe3.nomgroupe AS nomgroupe3,
    groupe3.debitnominal AS debitnominal3,
    groupe3.hmt AS hmt3,
    groupe3.nbpompes AS nbpompes3,
        CASE
            WHEN equip_turbidimetre.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presenceturbidimetre,
        CASE
            WHEN equip_chlore.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presencanalyseurchlore,
        CASE
            WHEN equip_groupe_electrogene.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presencegroupeelectrogene,
        CASE
            WHEN equip_antiintrusion.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presenceantiintrusion,
    inst_electricite_com.hastelegestion,
    inst_electricite_com.transformateur,
    inst_electricite_com.demarreur,
    inst_electricite_com.variateur,
    lex_mesure_debit.libelle AS 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::text = 'CODE_ETAT'::text
     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::text = 'INSTALLATIONS.MESUREDEBIT'::text
  WHERE aep_installations.typeinstallation = 10;
 
 -- public.v_eau17sig_reservoirs source

CREATE OR REPLACE VIEW public.v_eau17sig_reservoirs
AS SELECT aep_installations.identifiant,
    lex_etat.libelle AS fonctionnement,
    inst_reservoir_gc.nbcuves,
    lex_natures_cuves.libelle AS naturecuves,
    lex_revetements_cuves.libelle AS revetementcuve,
    lex_mode_rempl_cuves.libelle AS moderemplissage,
        CASE
            WHEN equip_turbidimetre.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presenceturbidimetre,
        CASE
            WHEN equip_chlore.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presencanalyseurchlore,
        CASE
            WHEN equip_colonne_seche.codeprotection IS NOT NULL THEN true
            ELSE false
        END AS colonneseche,
        CASE
            WHEN equip_fontaine.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS fontaine,
    lex_detection_niveau.libelle AS typedetectionniveau,
    lex_mesure_debit.libelle AS typemesuredebitmetrique,
        CASE
            WHEN equip_bypass.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presencebypass,
        CASE
            WHEN equip_robinetflotteur.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presencerobinetflotteur,
        CASE
            WHEN equip_antiintrusion.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presenceantiintrusion,
    inst_electricite_com.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::text = 'CODE_ETAT'::text
     LEFT JOIN codes_sandre lex_natures_cuves ON inst_reservoir_gc.naturecuve = lex_natures_cuves.code AND lex_natures_cuves.champ::text = 'CUVES.MATERIAU'::text
     LEFT JOIN codes_sandre lex_revetements_cuves ON inst_reservoir_gc.revetementcuve = lex_revetements_cuves.code AND lex_revetements_cuves.champ::text = 'INSTALLATIONS.REVETEMENTCUVE'::text
     LEFT JOIN codes_sandre lex_mode_rempl_cuves ON inst_reservoir_gc.moderemplissage = lex_mode_rempl_cuves.code AND lex_mode_rempl_cuves.champ::text = 'INSTALLATIONS.MODEREMPLISSAGE'::text
     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::text = 'INSTALLATIONS.TYPEDETECTION'::text
     LEFT JOIN codes_sandre lex_mesure_debit ON inst_reservoir_equipements.mesuredebitmetrique = lex_mesure_debit.code AND lex_mesure_debit.champ::text = 'INSTALLATIONS.MESUREDEBIT'::text
     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 aep_installations.typeinstallation = 12;
 
-- public.v_eau17sig_poste_relevage source

CREATE OR REPLACE VIEW public.v_eau17sig_poste_relevage
AS SELECT aep_installations.identifiant,
    lex_etat.libelle AS fonctionnement,
    lex_natures_cuves.libelle AS naturecuves,
    inst_poste_relevage_gc.revetementantiacide,
    inst_poste_relevage_gc.cotealtidessusposte,
    inst_poste_relevage_gc.coteentreefileau,
    inst_poste_relevage_gc.cotefondcuve,
    inst_poste_relevage_gc.profondeur,
    inst_poste_relevage_gc.tropplein,
    inst_poste_relevage_gc.cotetropplein,
    inst_poste_relevage_gc.bassintampon,
    inst_poste_relevage_equipements.nbpompes,
    inst_poste_relevage_equipements.debitnominal,
    inst_poste_relevage_equipements.hmt,
        CASE
            WHEN equip_brassage.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS dispositifbrassage,
        CASE
            WHEN equip_antibelier.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS antibelier,
        CASE
            WHEN equip_traitementh2s.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS traitementh2s,
        CASE
            WHEN equip_mesuredebimetrique.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS mesuredebimetrique,
        CASE
            WHEN equip_desodorisation.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS desodorisation,
        CASE
            WHEN equip_ventousechambrevannes.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS ventousechambrevannes,
        CASE
            WHEN equip_vanneisolement.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS vanneisolement,
        CASE
            WHEN equip_branchementaep.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS branchementaep,
        CASE
            WHEN equip_groupe_electrogene.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS groupeelectrogene,
        CASE
            WHEN equip_barreantichute.codeprotection IS NOT NULL THEN true
            ELSE false
        END AS barreantichute,
    inst_electricite_com.hastelegestion,
        CASE
            WHEN inst_electricite_com.dispositifsecours IS NOT NULL THEN true
            ELSE false
        END AS 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::text = 'CUVES.MATERIAU'::text
     LEFT JOIN codes_sandre lex_etat ON aep_installations.statut = lex_etat.code AND lex_etat.champ::text = 'CODE_ETAT'::text
     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 aep_installations.typeinstallation = 9;
  -- public.v_eau17sig_captages source

CREATE OR REPLACE VIEW public.v_eau17sig_captages
AS SELECT aep_installations.identifiant,
    lex_type.libelle,
    aquiferes.libelle AS nappe,
    lex_gisement.libelle AS modegisement,
    aep_installations.datefin AS datedesaffectation,
    lex_etat.libelle AS fonctionnement,
    forages_gc.profondeurforage,
    forages_gc.diametreinternetubage,
    forages_gc.epaisseurtubage,
    lex_tubage.libelle AS naturetubage,
    forages_gc.profondeurtubage,
    forages_gc.ouvragecrepine,
    inst_forages.profpompe,
    forages_gc.cotebride,
    forages_gc.coterepere,
    forages_gc.margelleprotection,
    forages_gc.capotage,
    forages_gc.teteetanche,
    forages_gc.derniereinspection,
    forages_equipements.debitnominal,
    forages_equipements.hmt,
    lex_typepompe.libelle AS typepompe,
    forages_equipements.diametreexterieurcolonneexhaure,
    lex_typecolonneexhaure.libelle AS typecolonneexhaure,
    lex_naturecolonneexhaure.libelle AS naturecolonneexhaure,
    lex_typedetectionniveau.libelle AS typedetectionniveau,
    forages_gc.profondeursonde,
        CASE
            WHEN equip_ventouse.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS ventouse,
        CASE
            WHEN equip_mesuredebit.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS mesuredebimetrique,
        CASE
            WHEN equip_clapet.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS clapetantiretour,
        CASE
            WHEN equip_antiintrusion.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presenceantiintrusion,
    systemesecurite.nbsystemes,
    inst_electricite_com.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::text = 'PIEZOMETRES.MODEGISEMENT'::text
     LEFT JOIN codes_sandre lex_etat ON aep_installations.statut = lex_etat.code AND lex_etat.champ::text = 'CODE_ETAT'::text
     LEFT JOIN codes_sandre lex_tubage ON forages_gc.naturetubage = lex_tubage.code AND lex_tubage.champ::text = 'INSTALLATIONS.NATUREEQUIPEMENT'::text
     LEFT JOIN lien_forages_aquifere ON aep_installations.codeinstallation = lien_forages_aquifere.codeinstallation AND (lien_forages_aquifere.numeroordre = 0 OR lien_forages_aquifere.numeroordre IS NULL)
     LEFT JOIN aquiferes ON lien_forages_aquifere.codeaquifere::double precision = aquiferes.codeaquifere
     LEFT JOIN codes_sandre lex_type ON inst_forages.codenature = lex_type.code AND lex_type.champ::text = 'PIEZOMETRES.CODENATURE'::text
     LEFT JOIN codes_sandre lex_typepompe ON forages_equipements.typepompe = lex_typepompe.code AND lex_typepompe.champ::text = 'INSTALLATIONS.TYPEPOMPE'::text
     LEFT JOIN codes_sandre lex_typecolonneexhaure ON forages_equipements.typecolonneexhaure = lex_typecolonneexhaure.code AND lex_typecolonneexhaure.champ::text = 'INSTALLATIONS.TYPECOLONNEEXHAURE'::text
     LEFT JOIN codes_sandre lex_naturecolonneexhaure ON forages_equipements.naturecolonneexhaure = lex_typecolonneexhaure.code AND lex_typecolonneexhaure.champ::text = 'INSTALLATIONS.NATURECOLONNEEXHAURE'::text
     LEFT JOIN codes_sandre lex_typedetectionniveau ON forages_equipements.typedetectionniveau = lex_typedetectionniveau.code AND lex_typedetectionniveau.champ::text = 'INSTALLATIONS.TYPEDETECTION'::text
     LEFT JOIN ( SELECT inst_securite.codeinstallation,
            count(*) AS nbsystemes
           FROM inst_securite
          GROUP BY inst_securite.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 aep_installations.typeinstallation = 0 AND aep_installations.jobexecutionid IS NULL;
  
 CREATE OR REPLACE VIEW public.v_eau17sig_stations_traitements
WITH(security_barrier=false)
AS SELECT aep_installations.identifiant,
    inst_st_traitement_gestion.batiment,
    inst_st_traitement_gestion.capacitenominale,
    inst_st_traitement_gestion.capacitehoraire,
    inst_st_traitement_gestion.parametrestraites,
    lex_filieretraitement.libelle AS filieretraitement,
    inst_st_traitement_gestion.numarrete AS arreteautorisationouvrage,
    inst_st_traitement_gestion.datearrete AS datearreteautorisationouvrage,
    inst_st_traitement_gestion.autorisationrejet,
    inst_st_traitement_gestion.dateautorisationrejet,
    aep_installations.datemiseenservice AS datemiseenroute,
    lex_etat.libelle AS fonctionnement,
        CASE
            WHEN equip_antiintrusion.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presenceantiintrusion,
    inst_electricite_com.hastelegestion,
        CASE
            WHEN equip_debimetre.codeequipement IS NOT NULL THEN true
            ELSE false
        END AS presencedebimetre,
    inst_electricite_com.installationarmoire AS inverseursource,
    installations_batiments.surfaces,
    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.codeetat = lex_etat.code AND lex_etat.champ::text = 'CODE_ETAT'::text
     LEFT JOIN codes_sandre lex_filieretraitement ON inst_st_traitement_gestion.filieretraitement = lex_filieretraitement.code AND lex_filieretraitement.champ::text = 'NSTALLATIONS.FILIERETRAITEMENT'::text
     LEFT JOIN inst_equipements_detail equip_antiintrusion ON aep_installations.codeinstallation = equip_antiintrusion.codeinstallation AND equip_antiintrusion.codeequipement = 39
     LEFT JOIN ( SELECT inst_batiments.codeinstallation,
            count(*) AS nbbatiments,
            sum(inst_batiments.surfacebatiment) AS surfaces
           FROM inst_batiments
          WHERE inst_batiments.typebatiment = 4
          GROUP BY inst_batiments.codeinstallation) installations_batiments ON aep_installations.codeinstallation = installations_batiments.codeinstallation
  WHERE aep_installations.typeinstallation = 11;
  
 -- public.ouv_forages source

CREATE OR REPLACE VIEW public.ouv_forages
AS SELECT aep_installations.codeinstallation,
    aep_installations.identifiant,
    aep_installations.nom AS nom_installation,
    aep_installations.descriptif,
    aep_installations.datecreation,
    aep_installations.codecommune,
    communes.nom AS nom_commune,
    inst_forages.profondeur
   FROM aep_installations
     LEFT JOIN communes ON aep_installations.codecommune::text = communes.codecommune::text
     LEFT JOIN inst_forages ON aep_installations.codeinstallation = inst_forages.codeinstallation
  WHERE aep_installations.typeinstallation = 0;
  
 
 -- public.eau17sig_reservoirs source

CREATE OR REPLACE VIEW public.eau17sig_reservoirs
AS SELECT aep_installations.identifiant,
    inst_reservoir_gc.nbcuves,
    lex_natures_cuves.libelle AS naturecuves,
    lex_revetements_cuves.libelle AS revetementcuve,
    lex_mode_rempl_cuves.libelle AS moderemplissage,
        CASE equip_turbidimetre.codeequipement
            WHEN 0 THEN true
            ELSE false
        END AS presenceturbidimetre,
        CASE equip_chlore.codeequipement
            WHEN 0 THEN true
            ELSE false
        END AS presencanalyseurchlore,
        CASE equip_colonne_seche.codeprotection
            WHEN 0 THEN true
            ELSE false
        END AS colonneseche,
        CASE equip_fontaine.codeequipement
            WHEN 0 THEN true
            ELSE false
        END AS fontaine,
    lex_detection_niveau.libelle AS typedetectionniveau,
    lex_mesure_debit.libelle AS typemesuredebitmetrique,
        CASE equip_bypass.codeequipement
            WHEN 0 THEN true
            ELSE false
        END AS presencebypass,
        CASE equip_robinetflotteur.codeequipement
            WHEN 0 THEN true
            ELSE false
        END AS presencerobinetflotteur,
    aep_installations.loginmaj,
    aep_installations.datemaj
   FROM aep_installations
     LEFT JOIN inst_reservoir_gc ON aep_installations.codeinstallation = inst_reservoir_gc.codeinstallation
     LEFT JOIN codes_sandre lex_natures_cuves ON inst_reservoir_gc.naturecuve = lex_natures_cuves.code AND lex_natures_cuves.champ::text = 'CUVES.MATERIAU'::text
     LEFT JOIN codes_sandre lex_revetements_cuves ON inst_reservoir_gc.revetementcuve = lex_revetements_cuves.code AND lex_revetements_cuves.champ::text = 'INSTALLATIONS.REVETEMENTCUVE'::text
     LEFT JOIN codes_sandre lex_mode_rempl_cuves ON inst_reservoir_gc.moderemplissage = lex_mode_rempl_cuves.code AND lex_mode_rempl_cuves.champ::text = 'INSTALLATIONS.MODEREMPLISSAGE'::text
     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_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::text = 'INSTALLATIONS.TYPEDETECTION'::text
     LEFT JOIN codes_sandre lex_mesure_debit ON inst_reservoir_equipements.mesuredebitmetrique = lex_mesure_debit.code AND lex_mesure_debit.champ::text = 'INSTALLATIONS.MESUREDEBIT'::text
     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 aep_installations.typeinstallation = 12;
  
 -- public.eau17_sig source

CREATE OR REPLACE VIEW public.eau17_sig
AS SELECT aep_installations_sba.identifiant,
    aep_installations_sba.nom,
    aep_installations_sba.x,
    aep_installations_sba.y,
    aep_installations_sba.typeprojection
   FROM aep_installations_sba;
