1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208
| /*liste améliorée dès le 16.02.2016*/
SELECT CONCAT(imm.NUMERO_DOSSIER, '-', imm.LETTRE_DOSSIER) AS NO_DOSSIER
, imm.DATE_DECISION_CANTON AS DECISION_CANTON
, (SELECT GROUP_CONCAT(regfoncier.NUMERO_PARCELLE ORDER BY regfoncier.NUMERO_PARCELLE ASC SEPARATOR '-') FROM REGISTRE_FONCIER AS regfoncier WHERE regfoncier.IMMEUBLE_ID_FK = imm.ID AND regfoncier.TYPE_REGISTRE_ID_FK = 1) AS PARCELLE
, (SELECT GROUP_CONCAT(regfoncier.NUMERO_PARCELLE ORDER BY regfoncier.NUMERO_PARCELLE ASC SEPARATOR '-') FROM REGISTRE_FONCIER AS regfoncier WHERE regfoncier.IMMEUBLE_ID_FK = imm.ID AND regfoncier.TYPE_REGISTRE_ID_FK = 2) AS DDP
, CASE
WHEN imm.TYPE_AIDE_ID_FK = 1 THEN 'Aide dégressive'
WHEN imm.TYPE_AIDE_ID_FK = 2 THEN 'Aide linéaire'
WHEN imm.TYPE_AIDE_ID_FK = 3 THEN 'Autres aides'
END AS TYPE_AIDE
, CASE IFNULL((SELECT caut.MONTANT_ACTIVE FROM CAUTION AS caut WHERE caut.IMMEUBLE_ID_FK = imm.ID ORDER BY caut.DEBUT_PERIODE DESC LIMIT 1), 0)
WHEN 0 then 'Non'
ELSE 'Oui'
END AS CAUTION
, imm.DATE_DEBUT AS LOCATION
, CASE
WHEN aide1.DATE_ECHEANCE_AIDE_CANTONAL IS NOT NULL THEN aide1.DATE_ECHEANCE_AIDE_CANTONAL
ELSE DATE_SUB(DATE_ADD(aide1.DEBUT_AIDE_CANTONAL, INTERVAL aide1.DUREE_AIDE_CANTONAL YEAR),INTERVAL 1 DAY)
END AS FIN_AIDES
, DATE_SUB(DATE_ADD(aide1.DATE_DEBUT_FEDERAL, INTERVAL aide1.DUREE_ASI YEAR),INTERVAL 1 DAY) AS ECHEANCE_ASI
, DATE_SUB(DATE_ADD(aide1.DATE_DEBUT_FEDERAL, INTERVAL aide1.DUREE_ASII YEAR),INTERVAL 1 DAY) AS ECHEANCE_ASII
, DATE_SUB(DATE_ADD(aide1.DATE_DEBUT_FEDERAL, INTERVAL aide1.DUREE_ASIII YEAR),INTERVAL 1 DAY) AS ECHEANCE_ASIII
, DATE_SUB(DATE_ADD(aide1.DATE_DEBUT_FEDERAL, INTERVAL aide1.DUREE_ASIV YEAR),INTERVAL 1 DAY) AS ECHEANCE_ASIV
, imm.DATE_CONVENTION_LIBERATION AS LIBERATION
, imm.DATE_SORTIE AS SORTIE
, dist.NOM_DISTRICT AS DISTRICT
, comm.NOM_COMMUNE AS COMMUNE
, imm.ADRESSE_PRINCIPALE AS ADRESSE
, CASE
WHEN imm.DELEGATION_COMPETENTE = 0 THEN 'Non'
ELSE 'Oui'
END AS DELEGATION
, QUERY_PARTENAIRES.PROPRIETAIRE AS PROPRIETAIRE
, QUERY_PARTENAIRES.TYPE_STATUT_JURIDIQUE AS STATUT_JURIDIQUE
, QUERY_PARTENAIRES.GERANCE AS GERANCE
, statut.TYPE AS TYPE_DECOMPTE
, travaux.TYPE AS TYPE_TRAVAUX
, COUNT(CASE log.NOMBRE_PIECE WHEN 1 THEN log.NOMBRE_PIECE END) AS 1_P
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN log.SURFACE_NETTE END), 2), 0) AS SURFACE_1_P
, COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN coutlog1.COUT END), 0) AS COUT_1_P
, COUNT(CASE log.NOMBRE_PIECE WHEN 2 THEN log.NOMBRE_PIECE END) AS 2_P
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 2 THEN log.SURFACE_NETTE END), 2), 0) AS SURFACE_2_P
, COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 2 THEN coutlog1.COUT END), 0) AS COUT_2_P
, COUNT(CASE log.NOMBRE_PIECE WHEN 3 THEN log.NOMBRE_PIECE END) AS 3_P
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 3 THEN log.SURFACE_NETTE END), 2), 0) AS SURFACE_3_P
, COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 3 THEN coutlog1.COUT END), 0) AS COUT_3_P
, COUNT(CASE log.NOMBRE_PIECE WHEN 4 THEN log.NOMBRE_PIECE END) AS 4_P
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 4 THEN log.SURFACE_NETTE END), 2), 0) AS SURFACE_4_P
, COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 4 THEN coutlog1.COUT END), 0) AS COUT_4_P
, COUNT(CASE log.NOMBRE_PIECE WHEN 5 THEN log.NOMBRE_PIECE END) AS 5_P
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 5 THEN log.SURFACE_NETTE END), 2), 0) AS SURFACE_5_P
, COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 5 THEN coutlog1.COUT END), 0) AS COUT_5_P
, COUNT(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.NOMBRE_PIECE END) AS NB_LOGEMENT
, ROUND(SUM(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.SURFACE_NETTE END), 2) AS SURFACE_TOTALE
, SUM(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN coutlog1.COUT END) AS COUT_TOTAL
, autre1.CAPITAL AS TERRAIN_DDP
, (SELECT COUNT(DISTINCT adresse.NUMERO_ECA) FROM ADRESSE_IMMEUBLE AS adresse WHERE adresse.IMMEUBLE_ID_FK = imm.ID ORDER BY adresse.IMMEUBLE_ID_FK) AS NB_BATIMENT
, CURDATE() AS DATE_EXPORT
FROM IMMEUBLE AS imm
LEFT JOIN REGISTRE_FONCIER AS regfonc
ON regfonc.IMMEUBLE_ID_FK = imm.ID
INNER JOIN TYPE_REGISTRE_FONCIER AS typeregfonc
ON typeregfonc.ID = regfonc.TYPE_REGISTRE_ID_FK
INNER JOIN COMMUNE AS comm
ON comm.ID = imm.COMMUNE_ID_FK
INNER JOIN DISTRICT AS dist
ON dist.ID = comm.DISTRICT_ID_FK
INNER JOIN TYPE_STATUT_IMMEUBLE AS statut
ON statut.ID = TYPE_STATUT_IMMEUBLE_ID_FK
INNER JOIN TYPE_NATURE_TRAVAUX AS travaux
ON travaux.ID = TYPE_NATURE_TRAVAUX_ID_FK
INNER JOIN AIDE AS aide1
ON aide1.IMMEUBLE_ID_FK = imm.ID
LEFT JOIN AIDE AS aide2
ON aide2.IMMEUBLE_ID_FK = imm.ID
AND aide2.DATE_DEBUT > aide1.DATE_DEBUT
INNER JOIN LOGEMENT AS log
ON log.IMMEUBLE_ID_FK = imm.ID
INNER JOIN COUT_LOGEMENT AS coutlog1
ON coutlog1.LOGEMENT_ID_FK = log.ID
LEFT JOIN COUT_LOGEMENT AS coutlog2
ON coutlog2.LOGEMENT_ID_FK = log.ID
AND coutlog2.DEBUT_PERIODE > coutlog1.DEBUT_PERIODE
INNER JOIN AUTRE_DECOMPTE AS autre1
ON autre1.IMMEUBLE_ID_FK = imm.ID
AND autre1.TYPE_AUTRE_DECOMTE_ID_FK = 1
LEFT JOIN AUTRE_DECOMPTE AS autre2
ON autre2.IMMEUBLE_ID_FK = imm.ID
AND autre2.DATE_DEBUT > autre1.DATE_DEBUT
INNER JOIN
(SELECT srrevloc.SR_ID AS ID_DOSSIER
, MAX(FONDS_DEBUT_PERIODE)
, MAX(ATTRIBUTION)
, MAX(PRELEVEMENT)
FROM (
SELECT immrl.ID AS SR_ID
, MONTANT AS FONDS_DEBUT_PERIODE
, 0 AS ATTRIBUTION
, 0 AS PRELEVEMENT
FROM IMMEUBLE AS immrl
INNER JOIN REVENU_LOCATIF AS revloc
ON revloc.IMMEUBLE_ID_FK = immrl.ID
WHERE revloc.ENUM_TYPE = 'fondRegulationExistant'
AND NOT EXISTS
(SELECT 1
FROM REVENU_LOCATIF AS revloc2
WHERE revloc2.IMMEUBLE_ID_FK = immrl.ID
AND revloc2.DATE_DEBUT > revloc.DATE_DEBUT)
UNION ALL
SELECT immrl.ID AS SR_ID
, 0 AS FONDS_DEBUT_PERIODE
, MONTANT AS ATTRIBUTION
, 0 AS PRELEVEMENT
FROM IMMEUBLE AS immrl
INNER JOIN REVENU_LOCATIF AS revloc
ON revloc.IMMEUBLE_ID_FK = immrl.ID
WHERE revloc.ENUM_TYPE = 'attribution'
AND NOT EXISTS
(SELECT 1
FROM REVENU_LOCATIF AS revloc2
WHERE revloc2.IMMEUBLE_ID_FK = immrl.ID
AND revloc2.DATE_DEBUT > revloc.DATE_DEBUT)
UNION ALL
SELECT immrl.ID AS SR_ID
, 0 AS FONDS_DEBUT_PERIODE
, 0 AS ATTRIBUTION
, MONTANT AS PRELEVEMENT
FROM IMMEUBLE AS immrl
INNER JOIN REVENU_LOCATIF AS revloc
ON revloc.IMMEUBLE_ID_FK = immrl.ID
WHERE revloc.ENUM_TYPE = 'prelevement'
AND NOT EXISTS
(SELECT 1
FROM REVENU_LOCATIF AS revloc2
WHERE revloc2.IMMEUBLE_ID_FK = immrl.ID
AND revloc2.DATE_DEBUT > revloc.DATE_DEBUT)
) AS srrevloc
GROUP BY SR_ID)
ON srrevolc.SR_ID = immrl.ID
LEFT OUTER JOIN
(SELECT q3.id,
q3.numero_dossier,
q3.lettre_dossier,
q3.proprietaire,
q3.gerance,
q3.TYPE_STATUT_JURIDIQUE_ID_FK,
TYPE AS TYPE_STATUT_JURIDIQUE
FROM
(SELECT q1.id,
q1.numero_dossier,
q1.Lettre_dossier,
q1.partenaire AS PROPRIETAIRE,
q2.partenaire AS GERANCE,
q1.TYPE_STATUT_JURIDIQUE_ID_FK
FROM
(SELECT im.id,
im.NUMERO_DOSSIER,
im.LETTRE_DOSSIER,
pa.PARTENAIRE,
pa.TYPE_STATUT_JURIDIQUE_ID_FK
FROM IMMEUBLE im
LEFT OUTER JOIN PARTENAIRE_IMMEUBLE pi ON im.id = pi.IMMEUBLE_ID_FK
INNER JOIN PARTENAIRE pa ON pi.PARTENAIRE_ID_FK = pa.ID
WHERE pa.TYPE_PARTENAIRE_ID_FK = 1) q1
LEFT OUTER JOIN
(SELECT im.id,
im.NUMERO_DOSSIER,
im.LETTRE_DOSSIER,
pa.PARTENAIRE
FROM IMMEUBLE im
LEFT OUTER JOIN PARTENAIRE_IMMEUBLE pi ON im.id = pi.IMMEUBLE_ID_FK
INNER JOIN PARTENAIRE pa ON pi.PARTENAIRE_ID_FK = pa.ID
WHERE pa.TYPE_PARTENAIRE_ID_FK = 2) q2 ON q1.ID = q2.ID
UNION SELECT q1.id,
q1.numero_dossier,
q1.Lettre_dossier,
q2.partenaire AS PROPRIETAIRE,
q1.partenaire AS GERANCE,
q1.TYPE_STATUT_JURIDIQUE_ID_FK
FROM
(SELECT im.id,
im.NUMERO_DOSSIER,
im.LETTRE_DOSSIER,
pa.PARTENAIRE,
pa.TYPE_STATUT_JURIDIQUE_ID_FK
FROM IMMEUBLE im
LEFT OUTER JOIN PARTENAIRE_IMMEUBLE pi ON im.id = pi.IMMEUBLE_ID_FK
INNER JOIN PARTENAIRE pa ON pi.PARTENAIRE_ID_FK = pa.ID
WHERE pa.TYPE_PARTENAIRE_ID_FK = 2) q1
LEFT OUTER JOIN
(SELECT im.id,
im.NUMERO_DOSSIER,
im.LETTRE_DOSSIER,
pa.PARTENAIRE
FROM IMMEUBLE im
LEFT OUTER JOIN PARTENAIRE_IMMEUBLE pi ON im.id = pi.IMMEUBLE_ID_FK
INNER JOIN PARTENAIRE pa ON pi.PARTENAIRE_ID_FK = pa.ID
WHERE pa.TYPE_PARTENAIRE_ID_FK = 1) q2 ON q1.ID = q2.ID
WHERE q2.partenaire IS NULL) q3
LEFT OUTER JOIN TYPE_STATUT_JURIDIQUE tsj ON tsj.id = q3.TYPE_STATUT_JURIDIQUE_ID_FK ) QUERY_PARTENAIRES ON QUERY_PARTENAIRES.ID = imm.id
WHERE log.NOMBRE_PIECE BETWEEN 1 AND 5
AND aide2.DATE_DEBUT IS NULL
AND coutlog2.DEBUT_PERIODE IS NULL
AND autre2.DATE_DEBUT IS NULL
GROUP BY imm.NUMERO_DOSSIER
, imm.LETTRE_DOSSIER
ORDER BY imm.NUMERO_DOSSIER ASC
, imm.LETTRE_DOSSIER ASC; |
Partager