Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Résumé parcelle vide. #460

Open
REIMSMetropole opened this issue Sep 16, 2024 · 1 comment
Open

Résumé parcelle vide. #460

REIMSMetropole opened this issue Sep 16, 2024 · 1 comment

Comments

@REIMSMetropole
Copy link

REIMSMetropole commented Sep 16, 2024 •
edited by Gustry
Loading

Bonjour,

Intégration d'une version 2024 de cadastre, dans un qgis 3.34. Version du plugin 1.20.0 mise à jour le 19/08/2024 14:48 .

Nous rencontrons le problème identique à celui signalé en 2021 : résumé de la parcelle vide.
image

Lorsque nous interrogeons la même parcelle mais en version 2023 du cadastre, nous avons bien un résumé. Ex.
image

Est-ce connu ? Un correctif en cours ?

D'avance, merci

L'erreur remontée dans Qgis est la suivante :

2024-09-16T15:41:09     CRITICAL    Error while fetching data from database : Erreur SQL : SET search_path = "cadastre", public, pg_catalog;WITH infos AS (
              SELECT
              p.parcelle,
              -- identification
              l.dnubat AS l_batiment, l.descr AS l_numero_entree,
              l.dniv AS l_niveau_etage, l.dpor AS l_numero_local,
              l.invar AS l_invariant,
              (l.dnubat || l.descr || l.dniv || l.dpor) AS l_identifiant,
             
              -- adresse
              ltrim(l.dnvoiri, '0') || l.dindic AS l_numero_voirie,
              CASE WHEN v.libvoi IS NOT NULL THEN v.natvoi || v.libvoi ELSE p.cconvo || p.dvoilib END AS l_adresse,
             
              -- proprio et acte
              string_agg((l10.ccodep || l10.ccocom || '-' ||l10.dnupro), '|') AS l10_compte_proprietaire,
             
              string_agg(
              '
              '
' || pr.dnulp || '
' ||
              '
' || pr.dnuper || '
' ||
              '
' ||
              trim(coalesce(pr.dqualp, '')) || ' ' ||
              CASE WHEN trim(pr.dnomus) != trim(pr.dnomlp) THEN Coalesce( trim(pr.dnomus) || '/' || trim(pr.dprnus) || ', née ', '' ) ELSE '' END ||
              trim(coalesce(pr.ddenom, '')) ||
              '
' ||
              '
' || ltrim(trim(coalesce(pr.dlign4, '')), '0') || trim(coalesce(pr.dlign5, '')) || ' ' || trim(coalesce(pr.dlign6, '')) || '
' ||
              CASE WHEN True THEN '
' || Coalesce( trim(cast(pr.jdatnss AS text) ), '-') || '
' ELSE '' END ||
              CASE WHEN True THEN '
' || coalesce(trim(pr.dldnss), '-') || '
' ELSE '' END ||
              '
' || Coalesce(ccodro_lib, '') || '
' ||
              '
' || Coalesce(ccodem_lib, '') || '
' ||
              '
'
              , ''
              ) AS l10_proprietaires,
             
              l10.jdatat AS l10_date_acte,
             
              -- autres infos
              dteloc_lib AS l10_type_local,
              cconlc_lib AS l10_nature_local,
              ccoplc_lib AS l10_nature_construction_particuliere,
              l10.jannat AS l10_annee_construction,
              l10.dnbniv AS l10_nombre_niveaux,
              dnatlc_lib AS l10_nature_occupation,
             
              -- pev : informations générales
              pev.pev,
              pev.dnupev AS pev_dnupev,
              ccoaff_lib AS pev_affectation,
              pev.ccostb AS pev_lettre_serie,
              pev.dcapec AS pev_categorie,
              pev.dcetlc AS pev_entretien,
              pev.dvlper AS pev_valeur_locative_ref,
              pev.dvlpera AS pev_valeur_locative_an,
              pev.gnexpl AS pev_nature_exoneration_permanente,
              pev.dnuref AS pev_numero_local_type,
              pev.dcsplca AS pev_coefficient_situation_particuliere,
              pev.dcsglca AS pev_coefficient_situation_generale,
             
              -- pev : taxation (1 seule par PEV)
              Coalesce(Cast(pt.co_vlbaia * px.pexb / 100 AS numeric(10,2)) , 0) as co_vlbaia, pt.co_bipevla as co_bipevla,
              Coalesce(Cast(pt.gp_vlbaia * px.pexb / 100 AS numeric(10,2)) , 0) as gp_vlbaia, pt.gp_bipevla as gp_bipevla,
              Coalesce(Cast(pt.de_vlbaia * px.pexb / 100 AS numeric(10,2)) , 0) as de_vlbaia, pt.de_bipevla as de_bipevla,
              Coalesce(Cast(pt.re_vlbaia * px.pexb / 100 AS numeric(10,2)) , 0) as re_vlbaia, Coalesce(pt.re_bipevla, 0) as re_bipevla
             
              FROM parcelle p
              INNER JOIN local00 l ON l.parcelle = p.parcelle
              INNER JOIN local10 l10 ON l10.local00 = l.local00
              INNER JOIN pev ON pev.local10 = l10.local10
              LEFT JOIN voie v ON v.voie = l.voie
              LEFT JOIN pevtaxation pt ON pt.pev = pev.pev
              LEFT JOIN pevexoneration px ON px.pev = pev.pev
              LEFT JOIN "dteloc" ON l10.dteloc = dteloc.dteloc
              LEFT JOIN "cconlc" ON l10.cconlc = cconlc.cconlc
              LEFT JOIN "ccoplc" ON l10.ccoplc = ccoplc.ccoplc
              LEFT JOIN "dnatlc" ON l10.dnatlc = dnatlc.dnatlc
              LEFT JOIN "ccoaff" ON pev.ccoaff = ccoaff.ccoaff
              LEFT JOIN proprietaire AS pr ON pr.comptecommunal = l10.comptecommunal
              LEFT JOIN "ccodro" c2 ON pr.ccodro = c2.ccodro
              LEFT JOIN "ccodem" c3 ON pr.ccodem = c3.ccodem
             
              WHERE 2>1
              AND p.parcelle = '510454000ZO0004'
             
              GROUP BY
              p.parcelle,
              l.invar,
              l.dnubat, l.dniv, l.descr, l.dpor,
              l.dnvoiri, l.dindic,
              v.natvoi, v.libvoi, p.cconvo, p.dvoilib,
              l10.ccodep, l10.ccocom, l10.dnupro, l10.jdatat,
              dteloc_lib, cconlc_lib, ccoplc_lib, l10.jannat, l10.dnbniv, dnatlc_lib,
              pev.pev, ccoaff_lib, pev.ccostb, pev.dcapec, pev.dcetlc, pev.dvlpera, pev.gnexpl, pev.dnuref, pev.dcsplca, pev.dcsglca,
              pt.co_vlbaia, pt.gp_vlbaia, pt.de_vlbaia, pt.re_vlbaia, px.pexb, pt.co_bipevla, pt.gp_bipevla, pt.de_bipevla, pt.re_bipevla
             
              ORDER BY l_identifiant
             
             ),
             
             pevs AS (
              SELECT pp.pev,
              'Habitation' AS type_pev, 'Habitation' AS sous_type_pev,
              (
              'Nombre de pièces: ' || pp.dnbpdc || '
Pièces principales: ' || pp.dnbppr ||
              '
Surface des pièces: ' || pp.dsupdc || ' m2' || '
Salles à manger: ' || pp.dnbsam || '
Chambres: ' || pp.dnbcha ||
              '
Cuisines - 9m2: ' || pp.dnbcu8 || '
Cuisines > 9m2: ' || pp.dnbcu9 ||
              '
Salles d''eau: ' || pp.dnbsea || '
Pièces annexes: ' || pp.dnbann ||
              '
Matériaux des gros murs: ' ||
              CASE
              WHEN pp.dmatgm IN ('00', '00') THEN 'indéterminé'
              WHEN pp.dmatgm IN ('10', '01') THEN 'pierre'
              WHEN pp.dmatgm IN ('20', '02') THEN 'meulière'
              WHEN pp.dmatgm IN ('30', '03') THEN 'béton'
              WHEN pp.dmatgm IN ('40', '04') THEN 'brique'
              WHEN pp.dmatgm IN ('50', '05') THEN 'aggloméré'
              WHEN pp.dmatgm IN ('80', '08') THEN 'bois'
              WHEN pp.dmatgm IN ('90', '09') THEN 'autres'
              ELSE 'inconnu'
              END ||
              '
Matériaux des toitures: ' ||
              CASE
              WHEN pp.dmatto IN ('00', '00') THEN 'indéterminé'
              WHEN pp.dmatto IN ('10', '01') THEN 'tuiles'
              WHEN pp.dmatto IN ('20', '02') THEN 'ardoises'
              WHEN pp.dmatto IN ('30', '03') THEN 'zinc aluminium'
              WHEN pp.dmatto IN ('40', '04') THEN 'béton'
              ELSE 'inconnu'
              END
             
              ) AS descriptif,
              (
              'Eau: ' || pp.geaulc || '
Électricité: ' || pp.gelelc ||
              '
Gaz: ' || pp.ggazlc || '
Chauffage central: ' || pp.gchclc ||
              '
Baignoire(s): ' || pp.dnbbai || '
Douche(s): ' || pp.dnbdou ||
              '
Lavabo(s): ' || pp.dnblav || '
WC: ' || pp.dnbwc
              ) AS confort
              FROM pevprincipale pp
              JOIN infos ON infos.pev = pp.pev
              UNION ALL
              SELECT pd.pev,
              'Dépendance' AS type_pev, cconad_lib AS sous_type_pev,
              (
              'Situation particulière: ' || pd.dcspdea || '
Surface réelle: ' || pd.dsudep || ' m2' ||
              '
Pondération: ' || pd.dcimlc || '
État d''entretien: ' || pd.detent ||
              '
Matériaux des gros murs: ' ||
              CASE
              WHEN pd.dmatgm IN ('00', '00') THEN 'indéterminé'
              WHEN pd.dmatgm IN ('10', '01') THEN 'pierre'
              WHEN pd.dmatgm IN ('20', '02') THEN 'meulière'
              WHEN pd.dmatgm IN ('30', '03') THEN 'béton'
              WHEN pd.dmatgm IN ('40', '04') THEN 'brique'
              WHEN pd.dmatgm IN ('50', '05') THEN 'aggloméré'
              WHEN pd.dmatgm IN ('60', '06') THEN 'bois'
              WHEN pd.dmatgm IN ('90', '09') THEN 'autres'
              ELSE 'inconnu'
              END ||
              '
Matériaux des toitures: ' ||
              CASE
              WHEN pd.dmatto IN ('00', '00') THEN 'indéterminé'
              WHEN pd.dmatto IN ('10', '01') THEN 'tuiles'
              WHEN pd.dmatto IN ('20', '02') THEN 'ardoises'
              WHEN pd.dmatto IN ('30', '03') THEN 'zinc aluminium'
              WHEN pd.dmatto IN ('40', '04') THEN 'béton'
              ELSE 'inconnu'
              END
              ) AS descriptif,
              (
              'Eau: ' || pd.geaulc || '
Électricité: ' || pd.gelelc ||
              '
Chauffage central: ' || pd.gchclc || '
Baignoire(s): ' || pd.dnbbai ||
              '
Douche(s): ' || pd.dnbdou || '
Lavabo(s): ' || pd.dnblav || '
WC: ' || pd.dnbwc
              ) AS confort
              FROM pevdependances pd
              JOIN infos ON infos.pev = pd.pev
              LEFT JOIN cconad ON cconad.cconad = pd.cconad
              UNION ALL
              SELECT po.pev,
              'Professionnel' AS type, 'Local professionnel' AS sous_type_pev,
              Coalesce('Surface réelle: ' || po.vsurzt || ' m2', '') AS descriptif,
              '' AS confort
              FROM pevprofessionnelle po
              JOIN infos ON infos.pev = po.pev
             ),
             source AS (
              SELECT
              parcelle,
              l_batiment, l_numero_entree, l_niveau_etage, l_numero_local, l_invariant, l_identifiant, l_numero_voirie, l_adresse,
              l10_compte_proprietaire, l10_proprietaires, l10_date_acte, l10_type_local, l10_nature_local, l10_nature_occupation, l10_nature_construction_particuliere, l10_annee_construction, l10_nombre_niveaux,
              pev_dnupev, pev_affectation, pev_lettre_serie, pev_categorie, pev_entretien, pev_valeur_locative_ref, pev_valeur_locative_an, pev_nature_exoneration_permanente,
              pev_numero_local_type, pev_coefficient_situation_particuliere, pev_coefficient_situation_generale,
              co_vlbaia, gp_vlbaia, de_vlbaia, re_vlbaia, co_bipevla, gp_bipevla, de_bipevla, re_bipevla,
              count(p.pev) AS nb_pev,
              string_agg(
              '
              '
' || pev_dnupev || '
' ||
              '
' || type_pev || '
' ||
              '
' || Coalesce(sous_type_pev, '') || '
' ||
              '
' || descriptif || '
' ||
              '
' || confort || '
' ||
              '
'
              , ''
              )
              AS infos_pev
              FROM infos i
              JOIN pevs p ON i.pev = p.pev
              GROUP BY
              parcelle,
              l_batiment, l_numero_entree, l_niveau_etage, l_numero_local, l_invariant, l_identifiant, l_numero_voirie, l_adresse,
              l10_compte_proprietaire, l10_proprietaires, l10_date_acte, l10_type_local, l10_nature_local, l10_nature_occupation, l10_nature_construction_particuliere, l10_annee_construction, l10_nombre_niveaux,
              pev_dnupev, pev_affectation, pev_lettre_serie, pev_categorie, pev_entretien, pev_valeur_locative_ref, pev_valeur_locative_an, pev_nature_exoneration_permanente,
              pev_numero_local_type, pev_coefficient_situation_particuliere, pev_coefficient_situation_generale,
              co_vlbaia, gp_vlbaia, de_vlbaia, re_vlbaia, co_bipevla, gp_bipevla, de_bipevla, re_bipevla
              ORDER BY l_identifiant
             )
             
             SELECT
             'La parcelle contient ' || count(l_identifiant) || CASE WHEN count(l_identifiant) > 1 THEN ' locaux' ELSE ' local' END
             ||
             '
' || string_agg(
              (
              '
Local ' || l_invariant || '
' ||
              '
Description générale
' ||
             
              '
Identification
' ||
              '
' ||
              '
              '
              '
Invariant
' ||
              '
Bat.
' ||
              '
Entrée
' ||
              '
Etage
' ||
              '
Local
' ||
              '
Identifiant
' ||
              '
Adresse
' ||
              '
' ||
              '
              '
' || l_invariant || '
' ||
              '
' || l_batiment || '
' ||
              '
' || l_numero_entree || '
' ||
              '
' || l_niveau_etage || '
' ||
              '
' || l_numero_local || '
' ||
              '
' || l_identifiant || '
' ||
              '
'|| l_adresse || '
' ||
              '
' ||
              '' ||
              '
' ||
             
              '
Propriété
' ||
              '
' ||
              'Compte propriétaire: ' || l10_compte_proprietaire ||
              '
Date de l''acte: ' || Coalesce(cast(l10_date_acte AS text), '-') ||
              '
' ||
             
              '
Caractéristiques
' ||
              '
' ||
              'Type: ' || l10_type_local ||
              '
Nature: ' || l10_nature_local ||
              '
Occupation: ' || l10_nature_occupation ||
              '
Construction: ' || l10_nature_construction_particuliere ||
              '
Année de construction: ' || l10_annee_construction ||
              '
Niveaux: ' || l10_nombre_niveaux ||
              '
' ||
             
              '
Description foncière
' ||
             
              '
Évaluation
' ||
              '
' ||
              'Numéro de PEV: ' || pev_dnupev ||
              '
Affectation: ' || pev_affectation ||
              '
Lettre de série: ' || pev_lettre_serie ||
              '
Catégorie: ' || pev_categorie ||
              '
Entretien: ' || Coalesce(pev_entretien, -1) ||
              '
Valeur locative (en valeur de référence): ' || Coalesce(pev_valeur_locative_ref, -1) ||
              '
Valeur locative (en valeur de l''année): ' || Coalesce(pev_valeur_locative_an, -1) ||
              '
Exonération permanente: ' || Coalesce(pev_nature_exoneration_permanente, '') ||
              '
Numéro du local type: ' || Coalesce(pev_numero_local_type, '') ||
              '
Situation générale: ' || Coalesce(pev_coefficient_situation_generale, '') ||
              '
Situation particulière: ' || Coalesce(pev_coefficient_situation_particuliere, '') ||
              '
' ||
             
              '
Taxation
' ||
              '
' ||
              'Commune: ' || Coalesce(co_bipevla, 0) ||
              '
Intercommunalité: ' || Coalesce(gp_bipevla, 0) ||
              '
Département: ' || Coalesce(de_bipevla, 0) ||
              '
Région: ' || Coalesce(re_bipevla, 0) ||
              '
' ||
             
              '
Parties d''évaluation
' ||
              'Le local contient ' || nb_pev || ' parties.' ||
              '
' ||
              '
              '
              '
PEV
' ||
              '
Type
' ||
              '
Sous-type
' ||
              '
Descriptif
' ||
              '
Confort
' ||
              '
' ||
              Coalesce(infos_pev, '') ||
              '' ||
              '
' ||
             
              '
Propriétaires
' ||
              '
              '
              '
Numéro
' ||
              '
Code
' ||
              '
Nom
' ||
              '
Adresse
' ||
              CASE WHEN True THEN '
Date de naissance
' ELSE '' END ||
              CASE WHEN True THEN '
Lieux de naissance
' ELSE '' END ||
              '
Code droit
' ||
              '
Code démembrement
' ||
              '
' ||
              Coalesce(l10_proprietaires, '') ||
              '' ||
              ''
             
              )
              , '


'
             ) || '
' AS locaux
             FROM source
             GROUP BY parcelle
             ;
              renvoyé 0 [ERREUR: la colonne « pev.dnupev » doit apparaître dans la clause GROUP BY ou doit être utilisé dans une fonction d'agrégat
             LINE 47: pev.dnupev AS pev_dnupev,
              ^
             ]







2024-09-16T15:41:09     INFO    SET search_path = "cadastre", public, pg_catalog;WITH infos AS (
              SELECT
              p.parcelle,
              -- identification
              l.dnubat AS l_batiment, l.descr AS l_numero_entree,
              l.dniv AS l_niveau_etage, l.dpor AS l_numero_local,
              l.invar AS l_invariant,
              (l.dnubat || l.descr || l.dniv || l.dpor) AS l_identifiant,
             
              -- adresse
              ltrim(l.dnvoiri, '0') || l.dindic AS l_numero_voirie,
              CASE WHEN v.libvoi IS NOT NULL THEN v.natvoi || v.libvoi ELSE p.cconvo || p.dvoilib END AS l_adresse,
             
              -- proprio et acte
              string_agg((l10.ccodep || l10.ccocom || '-' ||l10.dnupro), '|') AS l10_compte_proprietaire,
             
              string_agg(
              '
              '
' || pr.dnulp || '
' ||
              '
' || pr.dnuper || '
' ||
              '
' ||
              trim(coalesce(pr.dqualp, '')) || ' ' ||
              CASE WHEN trim(pr.dnomus) != trim(pr.dnomlp) THEN Coalesce( trim(pr.dnomus) || '/' || trim(pr.dprnus) || ', née ', '' ) ELSE '' END ||
              trim(coalesce(pr.ddenom, '')) ||
              '
' ||
              '
' || ltrim(trim(coalesce(pr.dlign4, '')), '0') || trim(coalesce(pr.dlign5, '')) || ' ' || trim(coalesce(pr.dlign6, '')) || '
' ||
              CASE WHEN True THEN '
' || Coalesce( trim(cast(pr.jdatnss AS text) ), '-') || '
' ELSE '' END ||
              CASE WHEN True THEN '
' || coalesce(trim(pr.dldnss), '-') || '
' ELSE '' END ||
              '
' || Coalesce(ccodro_lib, '') || '
' ||
              '
' || Coalesce(ccodem_lib, '') || '
' ||
              '
'
              , ''
              ) AS l10_proprietaires,
             
              l10.jdatat AS l10_date_acte,
             
              -- autres infos
              dteloc_lib AS l10_type_local,
              cconlc_lib AS l10_nature_local,
              ccoplc_lib AS l10_nature_construction_particuliere,
              l10.jannat AS l10_annee_construction,
              l10.dnbniv AS l10_nombre_niveaux,
              dnatlc_lib AS l10_nature_occupation,
             
              -- pev : informations générales
              pev.pev,
              pev.dnupev AS pev_dnupev,
              ccoaff_lib AS pev_affectation,
              pev.ccostb AS pev_lettre_serie,
              pev.dcapec AS pev_categorie,
              pev.dcetlc AS pev_entretien,
              pev.dvlper AS pev_valeur_locative_ref,
              pev.dvlpera AS pev_valeur_locative_an,
              pev.gnexpl AS pev_nature_exoneration_permanente,
              pev.dnuref AS pev_numero_local_type,
              pev.dcsplca AS pev_coefficient_situation_particuliere,
              pev.dcsglca AS pev_coefficient_situation_generale,
             
              -- pev : taxation (1 seule par PEV)
              Coalesce(Cast(pt.co_vlbaia * px.pexb / 100 AS numeric(10,2)) , 0) as co_vlbaia, pt.co_bipevla as co_bipevla,
              Coalesce(Cast(pt.gp_vlbaia * px.pexb / 100 AS numeric(10,2)) , 0) as gp_vlbaia, pt.gp_bipevla as gp_bipevla,
              Coalesce(Cast(pt.de_vlbaia * px.pexb / 100 AS numeric(10,2)) , 0) as de_vlbaia, pt.de_bipevla as de_bipevla,
              Coalesce(Cast(pt.re_vlbaia * px.pexb / 100 AS numeric(10,2)) , 0) as re_vlbaia, Coalesce(pt.re_bipevla, 0) as re_bipevla
             
              FROM parcelle p
              INNER JOIN local00 l ON l.parcelle = p.parcelle
              INNER JOIN local10 l10 ON l10.local00 = l.local00
              INNER JOIN pev ON pev.local10 = l10.local10
              LEFT JOIN voie v ON v.voie = l.voie
              LEFT JOIN pevtaxation pt ON pt.pev = pev.pev
              LEFT JOIN pevexoneration px ON px.pev = pev.pev
              LEFT JOIN "dteloc" ON l10.dteloc = dteloc.dteloc
              LEFT JOIN "cconlc" ON l10.cconlc = cconlc.cconlc
              LEFT JOIN "ccoplc" ON l10.ccoplc = ccoplc.ccoplc
              LEFT JOIN "dnatlc" ON l10.dnatlc = dnatlc.dnatlc
              LEFT JOIN "ccoaff" ON pev.ccoaff = ccoaff.ccoaff
              LEFT JOIN proprietaire AS pr ON pr.comptecommunal = l10.comptecommunal
              LEFT JOIN "ccodro" c2 ON pr.ccodro = c2.ccodro
              LEFT JOIN "ccodem" c3 ON pr.ccodem = c3.ccodem
             
              WHERE 2>1
              AND p.parcelle = '510454000ZO0004'
             
              GROUP BY
              p.parcelle,
              l.invar,
              l.dnubat, l.dniv, l.descr, l.dpor,
              l.dnvoiri, l.dindic,
              v.natvoi, v.libvoi, p.cconvo, p.dvoilib,
              l10.ccodep, l10.ccocom, l10.dnupro, l10.jdatat,
              dteloc_lib, cconlc_lib, ccoplc_lib, l10.jannat, l10.dnbniv, dnatlc_lib,
              pev.pev, ccoaff_lib, pev.ccostb, pev.dcapec, pev.dcetlc, pev.dvlpera, pev.gnexpl, pev.dnuref, pev.dcsplca, pev.dcsglca,
              pt.co_vlbaia, pt.gp_vlbaia, pt.de_vlbaia, pt.re_vlbaia, px.pexb, pt.co_bipevla, pt.gp_bipevla, pt.de_bipevla, pt.re_bipevla
             
              ORDER BY l_identifiant
             
             ),
             
             pevs AS (
              SELECT pp.pev,
              'Habitation' AS type_pev, 'Habitation' AS sous_type_pev,
              (
              'Nombre de pièces: ' || pp.dnbpdc || '
Pièces principales: ' || pp.dnbppr ||
              '
Surface des pièces: ' || pp.dsupdc || ' m2' || '
Salles à manger: ' || pp.dnbsam || '
Chambres: ' || pp.dnbcha ||
              '
Cuisines - 9m2: ' || pp.dnbcu8 || '
Cuisines > 9m2: ' || pp.dnbcu9 ||
              '
Salles d''eau: ' || pp.dnbsea || '
Pièces annexes: ' || pp.dnbann ||
              '
Matériaux des gros murs: ' ||
              CASE
              WHEN pp.dmatgm IN ('00', '00') THEN 'indéterminé'
              WHEN pp.dmatgm IN ('10', '01') THEN 'pierre'
              WHEN pp.dmatgm IN ('20', '02') THEN 'meulière'
              WHEN pp.dmatgm IN ('30', '03') THEN 'béton'
              WHEN pp.dmatgm IN ('40', '04') THEN 'brique'
              WHEN pp.dmatgm IN ('50', '05') THEN 'aggloméré'
              WHEN pp.dmatgm IN ('80', '08') THEN 'bois'
              WHEN pp.dmatgm IN ('90', '09') THEN 'autres'
              ELSE 'inconnu'
              END ||
              '
Matériaux des toitures: ' ||
              CASE
              WHEN pp.dmatto IN ('00', '00') THEN 'indéterminé'
              WHEN pp.dmatto IN ('10', '01') THEN 'tuiles'
              WHEN pp.dmatto IN ('20', '02') THEN 'ardoises'
              WHEN pp.dmatto IN ('30', '03') THEN 'zinc aluminium'
              WHEN pp.dmatto IN ('40', '04') THEN 'béton'
              ELSE 'inconnu'
              END
             
              ) AS descriptif,
              (
              'Eau: ' || pp.geaulc || '
Électricité: ' || pp.gelelc ||
              '
Gaz: ' || pp.ggazlc || '
Chauffage central: ' || pp.gchclc ||
              '
Baignoire(s): ' || pp.dnbbai || '
Douche(s): ' || pp.dnbdou ||
              '
Lavabo(s): ' || pp.dnblav || '
WC: ' || pp.dnbwc
              ) AS confort
              FROM pevprincipale pp
              JOIN infos ON infos.pev = pp.pev
              UNION ALL
              SELECT pd.pev,
              'Dépendance' AS type_pev, cconad_lib AS sous_type_pev,
              (
              'Situation particulière: ' || pd.dcspdea || '
Surface réelle: ' || pd.dsudep || ' m2' ||
              '
Pondération: ' || pd.dcimlc || '
État d''entretien: ' || pd.detent ||
              '
Matériaux des gros murs: ' ||
              CASE
              WHEN pd.dmatgm IN ('00', '00') THEN 'indéterminé'
              WHEN pd.dmatgm IN ('10', '01') THEN 'pierre'
              WHEN pd.dmatgm IN ('20', '02') THEN 'meulière'
              WHEN pd.dmatgm IN ('30', '03') THEN 'béton'
              WHEN pd.dmatgm IN ('40', '04') THEN 'brique'
              WHEN pd.dmatgm IN ('50', '05') THEN 'aggloméré'
              WHEN pd.dmatgm IN ('60', '06') THEN 'bois'
              WHEN pd.dmatgm IN ('90', '09') THEN 'autres'
              ELSE 'inconnu'
              END ||
              '
Matériaux des toitures: ' ||
              CASE
              WHEN pd.dmatto IN ('00', '00') THEN 'indéterminé'
              WHEN pd.dmatto IN ('10', '01') THEN 'tuiles'
              WHEN pd.dmatto IN ('20', '02') THEN 'ardoises'
              WHEN pd.dmatto IN ('30', '03') THEN 'zinc aluminium'
              WHEN pd.dmatto IN ('40', '04') THEN 'béton'
              ELSE 'inconnu'
              END
              ) AS descriptif,
              (
              'Eau: ' || pd.geaulc || '
Électricité: ' || pd.gelelc ||
              '
Chauffage central: ' || pd.gchclc || '
Baignoire(s): ' || pd.dnbbai ||
              '
Douche(s): ' || pd.dnbdou || '
Lavabo(s): ' || pd.dnblav || '
WC: ' || pd.dnbwc
              ) AS confort
              FROM pevdependances pd
              JOIN infos ON infos.pev = pd.pev
              LEFT JOIN cconad ON cconad.cconad = pd.cconad
              UNION ALL
              SELECT po.pev,
              'Professionnel' AS type, 'Local professionnel' AS sous_type_pev,
              Coalesce('Surface réelle: ' || po.vsurzt || ' m2', '') AS descriptif,
              '' AS confort
              FROM pevprofessionnelle po
              JOIN infos ON infos.pev = po.pev
             ),
             source AS (
              SELECT
              parcelle,
              l_batiment, l_numero_entree, l_niveau_etage, l_numero_local, l_invariant, l_identifiant, l_numero_voirie, l_adresse,
              l10_compte_proprietaire, l10_proprietaires, l10_date_acte, l10_type_local, l10_nature_local, l10_nature_occupation, l10_nature_construction_particuliere, l10_annee_construction, l10_nombre_niveaux,
              pev_dnupev, pev_affectation, pev_lettre_serie, pev_categorie, pev_entretien, pev_valeur_locative_ref, pev_valeur_locative_an, pev_nature_exoneration_permanente,
              pev_numero_local_type, pev_coefficient_situation_particuliere, pev_coefficient_situation_generale,
              co_vlbaia, gp_vlbaia, de_vlbaia, re_vlbaia, co_bipevla, gp_bipevla, de_bipevla, re_bipevla,
              count(p.pev) AS nb_pev,
              string_agg(
              '
              '
' || pev_dnupev || '
' ||
              '
' || type_pev || '
' ||
              '
' || Coalesce(sous_type_pev, '') || '
' ||
              '
' || descriptif || '
' ||
              '
' || confort || '
' ||
              '
'
              , ''
              )
              AS infos_pev
              FROM infos i
              JOIN pevs p ON i.pev = p.pev
              GROUP BY
              parcelle,
              l_batiment, l_numero_entree, l_niveau_etage, l_numero_local, l_invariant, l_identifiant, l_numero_voirie, l_adresse,
              l10_compte_proprietaire, l10_proprietaires, l10_date_acte, l10_type_local, l10_nature_local, l10_nature_occupation, l10_nature_construction_particuliere, l10_annee_construction, l10_nombre_niveaux,
              pev_dnupev, pev_affectation, pev_lettre_serie, pev_categorie, pev_entretien, pev_valeur_locative_ref, pev_valeur_locative_an, pev_nature_exoneration_permanente,
              pev_numero_local_type, pev_coefficient_situation_particuliere, pev_coefficient_situation_generale,
              co_vlbaia, gp_vlbaia, de_vlbaia, re_vlbaia, co_bipevla, gp_bipevla, de_bipevla, re_bipevla
              ORDER BY l_identifiant
             )
             
             SELECT
             'La parcelle contient ' || count(l_identifiant) || CASE WHEN count(l_identifiant) > 1 THEN ' locaux' ELSE ' local' END
             ||
             '
' || string_agg(
              (
              '
Local ' || l_invariant || '
' ||
              '
Description générale
' ||
             
              '
Identification
' ||
              '
' ||
              '
              '
              '
Invariant
' ||
              '
Bat.
' ||
              '
Entrée
' ||
              '
Etage
' ||
              '
Local
' ||
              '
Identifiant
' ||
              '
Adresse
' ||
              '
' ||
              '
              '
' || l_invariant || '
' ||
              '
' || l_batiment || '
' ||
              '
' || l_numero_entree || '
' ||
              '
' || l_niveau_etage || '
' ||
              '
' || l_numero_local || '
' ||
              '
' || l_identifiant || '
' ||
              '
'|| l_adresse || '
' ||
              '
' ||
              '' ||
              '
' ||
             
              '
Propriété
' ||
              '
' ||
              'Compte propriétaire: ' || l10_compte_proprietaire ||
              '
Date de l''acte: ' || Coalesce(cast(l10_date_acte AS text), '-') ||
              '
' ||
             
              '
Caractéristiques
' ||
              '
' ||
              'Type: ' || l10_type_local ||
              '
Nature: ' || l10_nature_local ||
              '
Occupation: ' || l10_nature_occupation ||
              '
Construction: ' || l10_nature_construction_particuliere ||
              '
Année de construction: ' || l10_annee_construction ||
              '
Niveaux: ' || l10_nombre_niveaux ||
              '
' ||
             
              '
Description foncière
' ||
             
              '
Évaluation
' ||
              '
' ||
              'Numéro de PEV: ' || pev_dnupev ||
              '
Affectation: ' || pev_affectation ||
              '
Lettre de série: ' || pev_lettre_serie ||
              '
Catégorie: ' || pev_categorie ||
              '
Entretien: ' || Coalesce(pev_entretien, -1) ||
              '
Valeur locative (en valeur de référence): ' || Coalesce(pev_valeur_locative_ref, -1) ||
              '
Valeur locative (en valeur de l''année): ' || Coalesce(pev_valeur_locative_an, -1) ||
              '
Exonération permanente: ' || Coalesce(pev_nature_exoneration_permanente, '') ||
              '
Numéro du local type: ' || Coalesce(pev_numero_local_type, '') ||
              '
Situation générale: ' || Coalesce(pev_coefficient_situation_generale, '') ||
              '
Situation particulière: ' || Coalesce(pev_coefficient_situation_particuliere, '') ||
              '
' ||
             
              '
Taxation
' ||
              '
' ||
              'Commune: ' || Coalesce(co_bipevla, 0) ||
              '
Intercommunalité: ' || Coalesce(gp_bipevla, 0) ||
              '
Département: ' || Coalesce(de_bipevla, 0) ||
              '
Région: ' || Coalesce(re_bipevla, 0) ||
              '
' ||
             
              '
Parties d''évaluation
' ||
              'Le local contient ' || nb_pev || ' parties.' ||
              '
' ||
              '
              '
              '
PEV
' ||
              '
Type
' ||
              '
Sous-type
' ||
              '
Descriptif
' ||
              '
Confort
' ||
              '
' ||
              Coalesce(infos_pev, '') ||
              '' ||
              '
' ||
             
              '
Propriétaires
' ||
              '
              '
              '
Numéro
' ||
              '
Code
' ||
              '
Nom
' ||
              '
Adresse
' ||
              CASE WHEN True THEN '
Date de naissance
' ELSE '' END ||
              CASE WHEN True THEN '
Lieux de naissance
' ELSE '' END ||
              '
Code droit
' ||
              '
Code démembrement
' ||
              '
' ||
              Coalesce(l10_proprietaires, '') ||
              '' ||
              ''
             
              )
              , '


'
             ) || '
' AS locaux
             FROM source
             GROUP BY parcelle
             ;

Originally posted by @REIMSMetropole in #287 (comment)
@REIMSMetropole REIMSMetropole changed the title Bonjour, Résumé parcelle vide. Sep 16, 2024
@sigeal
Copy link

sigeal commented Oct 1, 2024

Je rencontre ce problème également.
Il provient du fait que la table commune reste vide suite à l'import des données.
Le remplacement de :
LEFT OUTER JOIN commune c
par :
LEFT OUTER JOIN commune_majic c
dans templates/parcelle_info_parcelle_majic.sql permet de contourner le problème.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@sigeal @REIMSMetropole