 mais au format PCAET et non SECTEN 1
 
 Suppression du secret statistique du calcul
+Ce script doit-être lancé après espace2cigale_vX.sql
 
 * Utilisation:
 Si utilisé avec PgAdmin, lancer tout le code avec PgScript (pour que les commandes soient passées une par une)




/** 
Récupération du nombre d'établissements (Uniquement si nécessaire, long)
- Secteur industriel
- Secteur tertiaire
- Avec affectation d'un SESCTEN1 et catégorie d'énergie
- Les arrondissements de marseille sont transformés en 13055

NOTE: Pour certains etablissement on a un id_etablissement = -999. L'ensemble de ces 
	  établissements ne représentera qu'un seul étab.
FIXME: On ne ferme aucun établissement dans le film. Du coup, on en a de plus en plus?

-- si besoin de faire une backup de la version précédente: 
-- create table public.cigale_nb_etab_backup as select * from public.cigale_nb_etab;
drop table if exists public.cigale_nb_etab;
create table public.cigale_nb_etab as 
select an, case when id_comm between 13201 and 13216 then 13055 else id_comm end as id_comm, id_secten1, code_cat_energie, count(*) as nb_etab
from (

	-- Sélection des établissements industriels dont on connait l'id (!= -999)
	select distinct an, a.id_comm, case when a.id_etablissement = -999 then code_gerep else a.id_etablissement::text end as id_etablissement, id_snap3, id_energie from total_ind.bilan_comm_v7_2016 as a left join src_ind.def_corresp_sources as b using (id_corresp) where (a.id_etablissement <> -999 or a.id_corresp is not null) and an = 2016 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 6 or b.id_version_corresp is null)
	union all	
	select distinct an, a.id_comm, case when a.id_etablissement = -999 then code_gerep else a.id_etablissement::text end as id_etablissement, id_snap3, id_energie from total_ind.bilan_comm_v7_2015 as a left join src_ind.def_corresp_sources as b using (id_corresp) where (a.id_etablissement <> -999 or a.id_corresp is not null) and an = 2015 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 6 or b.id_version_corresp is null)
	union all
	select distinct an, a.id_comm, case when a.id_etablissement = -999 then code_gerep else a.id_etablissement::text end as id_etablissement, id_snap3, id_energie from total_ind.bilan_comm_v7_2014 as a left join src_ind.def_corresp_sources as b using (id_corresp) where (a.id_etablissement <> -999 or a.id_corresp is not null) and an = 2014 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 6 or b.id_version_corresp is null)
	union all
	select distinct an, a.id_comm, case when a.id_etablissement = -999 then code_gerep else a.id_etablissement::text end as id_etablissement, id_snap3, id_energie from total_ind.bilan_comm_v7_2013 as a left join src_ind.def_corresp_sources as b using (id_corresp) where (a.id_etablissement <> -999 or a.id_corresp is not null) and an = 2013 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 6 or b.id_version_corresp is null)
	union all
	select distinct an, a.id_comm, case when a.id_etablissement = -999 then code_gerep else a.id_etablissement::text end as id_etablissement, id_snap3, id_energie from total_ind.bilan_comm_v7_2012 as a left join src_ind.def_corresp_sources as b using (id_corresp) where (a.id_etablissement <> -999 or a.id_corresp is not null) and an = 2012 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 6 or b.id_version_corresp is null)
	union all
	select distinct an, a.id_comm, case when a.id_etablissement = -999 then code_gerep else a.id_etablissement::text end as id_etablissement, id_snap3, id_energie from total_ind.bilan_comm_v7_2010 as a left join src_ind.def_corresp_sources as b using (id_corresp) where (a.id_etablissement <> -999 or a.id_corresp is not null) and an = 2010 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 6 or b.id_version_corresp is null)
	union all
	select distinct an, a.id_comm, case when a.id_etablissement = -999 then code_gerep else a.id_etablissement::text end as id_etablissement, id_snap3, id_energie from total_ind.bilan_comm_v7_2007 as a left join src_ind.def_corresp_sources as b using (id_corresp) where (a.id_etablissement <> -999 or a.id_corresp is not null) and an = 2007 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 6 or b.id_version_corresp is null)

-- 	union all

-- 	-- Sélection des établissements tertiaire dont on connait l'id (!= -999)
-- 	select distinct an, a.id_comm, b.code_gerep, id_snap3, id_energie from total_ter.bilan_comm_v5_2015 as a left join src_ind.def_corresp_sources as b using (id_corresp) where id_corresp <> -999 and an = 2015 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 5 or b.id_version_corresp is null)
-- 	union all
-- 	select distinct an, a.id_comm, b.code_gerep, id_snap3, id_energie from total_ter.bilan_comm_v5_2014 as a left join src_ind.def_corresp_sources as b using (id_corresp) where id_corresp <> -999 and an = 2014 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 5 or b.id_version_corresp is null)
-- 	union all
-- 	select distinct an, a.id_comm, b.code_gerep, id_snap3, id_energie from total_ter.bilan_comm_v5_2013 as a left join src_ind.def_corresp_sources as b using (id_corresp) where id_corresp <> -999 and an = 2013 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 5 or b.id_version_corresp is null)
-- 	union all
-- 	select distinct an, a.id_comm, b.code_gerep, id_snap3, id_energie from total_ter.bilan_comm_v5_2012 as a left join src_ind.def_corresp_sources as b using (id_corresp) where id_corresp <> -999 and an = 2012 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 5 or b.id_version_corresp is null)
-- 	union all
-- 	select distinct an, a.id_comm, b.code_gerep, id_snap3, id_energie from total_ter.bilan_comm_v5_2010 as a left join src_ind.def_corresp_sources as b using (id_corresp) where id_corresp <> -999 and an = 2010 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 5 or b.id_version_corresp is null)
-- 	union all
-- 	select distinct an, a.id_comm, b.code_gerep, id_snap3, id_energie from total_ter.bilan_comm_v5_2007 as a left join src_ind.def_corresp_sources as b using (id_corresp) where id_corresp <> -999 and an = 2007 and (b.actif is true or b.actif is null) and (b.id_version_corresp = 5 or b.id_version_corresp is null)

) as a
-- Lien avec les catégories d'énergie
left join total.corresp_energie_synapse as b on a.id_energie = b.espace_id_energie
left join transversal.tpk_energie as c on b.synapse_id_energie = c.id_energie
left join total.corresp_snap_synapse as d on a.id_snap3 = d.espace_id_snap3
left join transversal.tpk_snap3 as e on d.synapse_id_snap3 = e.id_snap3
where 
	-- Corresp multiples uniquement pour 60405 qu'on ne retrouve que dans ind et pas ter. 
	-- On conserve uniquement la correspondance ind.
	d.specificite is null or d.synapse_id_snap3 = '06040502'
group by an, case when id_comm between 13201 and 13216 then 13055 else id_comm end, id_secten1, code_cat_energie;
*/


/* Validations
select an, sum(nb_etab) 
from public.cigale_nb_etab
group by an
order by an;

select an, id_secten1, code_cat_energie, sum(nb_etab) 
from public.cigale_nb_etab
group by an, id_secten1, code_cat_energie
order by an, id_secten1, code_cat_energie;
*/

/**
Création de la table des émissions par secten 1 et catégorie d'énergie
- Récupération des données emi et élec de la table bilan
- Récupération des données GES séparées dans vue ges bilan
- On a des valeurs nulles dans les tables bilans de chaque schéma de calcul

from total.bilan_comm_v5 as a
left join total.corresp_energie_synapse as b on a.id_energie = b.espace_id_energie
left join transversal.tpk_energie as c on b.synapse_id_energie = c.id_energie
left join (select * from src_ind.def_corresp_sources where id_version_corresp = 6 and actif is true) as d using (id_corresp)
where 
	id_polluant in (131,38,65,108,16,48,36,11)
	and val is not null -- NOTE: Certaines valeurs nulles dans les tables bilan de chaque secteur
	and a.id_comm <> 99999 -- Sans les émissions associées à l'objet mer
	and a.id_comm <> 99138 -- Sans MC
group by
	id_secteur,
	id_polluant, an, a.id_comm, 
