| 1 |
<?php |
| 2 |
//$Id: demande_avis.inc.php 5422 2015-11-10 17:50:51Z jymadier $ |
| 3 |
include('../sql/pgsql/consultation.inc.php'); |
| 4 |
include "../sql/pgsql/app_om_tab_common_select.inc.php"; |
| 5 |
|
| 6 |
$tab_title = __("Demandes d'avis"); |
| 7 |
// Titre de la page |
| 8 |
$ent = __("Demandes d'avis"); |
| 9 |
// FROM |
| 10 |
$table = DB_PREFIXE."consultation |
| 11 |
LEFT JOIN ".DB_PREFIXE."dossier |
| 12 |
ON consultation.dossier=dossier.dossier |
| 13 |
INNER JOIN ".DB_PREFIXE."dossier_instruction_type |
| 14 |
ON dossier.dossier_instruction_type = dossier_instruction_type.dossier_instruction_type |
| 15 |
INNER JOIN ".DB_PREFIXE."dossier_autorisation_type_detaille |
| 16 |
ON dossier_instruction_type.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille |
| 17 |
-- Récupère le motif de la consultation (concerne uniquement les consultations des tiers) |
| 18 |
LEFT JOIN ".DB_PREFIXE."motif_consultation |
| 19 |
ON motif_consultation.motif_consultation=consultation.motif_consultation |
| 20 |
-- Récupère l utilisateur en charge de la consultation |
| 21 |
LEFT JOIN ".DB_PREFIXE."service |
| 22 |
ON service.service=consultation.service |
| 23 |
LEFT JOIN ".DB_PREFIXE."lien_service_om_utilisateur |
| 24 |
ON lien_service_om_utilisateur.service=service.service |
| 25 |
LEFT JOIN ".DB_PREFIXE."tiers_consulte |
| 26 |
ON tiers_consulte.tiers_consulte=consultation.tiers_consulte |
| 27 |
LEFT JOIN ".DB_PREFIXE."lien_om_utilisateur_tiers_consulte |
| 28 |
ON lien_om_utilisateur_tiers_consulte.tiers_consulte=tiers_consulte.tiers_consulte |
| 29 |
-- Jointure avec la table utilisateur en utilisant le lien entre l'utilisateur et le service |
| 30 |
-- pour les consultations faites à des services ou le lien entre l'utilisateur et le tiers |
| 31 |
-- pour les consultations faites à des tiers |
| 32 |
LEFT JOIN ".DB_PREFIXE."om_utilisateur |
| 33 |
ON om_utilisateur.om_utilisateur=lien_service_om_utilisateur.om_utilisateur |
| 34 |
OR om_utilisateur.om_utilisateur=lien_om_utilisateur_tiers_consulte.om_utilisateur |
| 35 |
LEFT OUTER JOIN ".DB_PREFIXE."avis_consultation |
| 36 |
ON consultation.avis_consultation=avis_consultation.avis_consultation |
| 37 |
LEFT JOIN ".DB_PREFIXE."donnees_techniques |
| 38 |
ON donnees_techniques.dossier_instruction = dossier.dossier |
| 39 |
LEFT JOIN ( |
| 40 |
SELECT * |
| 41 |
FROM ".DB_PREFIXE."lien_dossier_demandeur |
| 42 |
INNER JOIN ".DB_PREFIXE."demandeur |
| 43 |
ON demandeur.demandeur = lien_dossier_demandeur.demandeur |
| 44 |
WHERE lien_dossier_demandeur.petitionnaire_principal IS TRUE |
| 45 |
AND LOWER(demandeur.type_demandeur) = LOWER('petitionnaire') |
| 46 |
) as demandeur |
| 47 |
ON demandeur.dossier=consultation.dossier |
| 48 |
"; |
| 49 |
// XXX LEFT OUTER JOIN pieces_complementaire |
| 50 |
|
| 51 |
// SELECT pour récupérer l'avis rendu de la consultation, à mettre dans une |
| 52 |
// varibale pour gérer son unset dans les demandes d'avis en cours |
| 53 |
$avis_rendu = 'avis_consultation.libelle as "'.__("avis rendu").'"'; |
| 54 |
|
| 55 |
// SELECT |
| 56 |
$champAffiche = array( |
| 57 |
'consultation.consultation as "'.__("consultation").'"', |
| 58 |
'to_char(consultation.date_limite ,\'DD/MM/YYYY\') as "'.__("date_limite").'"', |
| 59 |
$select__dossier_libelle__column_as, |
| 60 |
// date de depots de piece complementaire |
| 61 |
'CONCAT_WS( |
| 62 |
\'<br/>\', |
| 63 |
CASE WHEN donnees_techniques.co_projet_desc = \'\' THEN |
| 64 |
NULL |
| 65 |
ELSE |
| 66 |
TRIM(donnees_techniques.co_projet_desc) |
| 67 |
END, |
| 68 |
CASE WHEN donnees_techniques.ope_proj_desc = \'\' THEN |
| 69 |
NULL |
| 70 |
ELSE |
| 71 |
TRIM(donnees_techniques.ope_proj_desc) |
| 72 |
END, |
| 73 |
CASE WHEN donnees_techniques.am_projet_desc = \'\' THEN |
| 74 |
NULL |
| 75 |
ELSE |
| 76 |
TRIM(donnees_techniques.am_projet_desc) |
| 77 |
END, |
| 78 |
CASE WHEN donnees_techniques.dm_projet_desc = \'\' THEN |
| 79 |
NULL |
| 80 |
ELSE |
| 81 |
TRIM(donnees_techniques.dm_projet_desc) |
| 82 |
END |
| 83 |
) as "'.__("Description du projet").'"', |
| 84 |
'TRIM( |
| 85 |
CASE |
| 86 |
WHEN dossier.adresse_normalisee IS NULL |
| 87 |
OR TRIM(dossier.adresse_normalisee) = \'\' |
| 88 |
THEN |
| 89 |
CONCAT_WS( |
| 90 |
\' \', |
| 91 |
dossier.terrain_adresse_voie_numero, |
| 92 |
dossier.terrain_adresse_voie, |
| 93 |
dossier.terrain_adresse_code_postal |
| 94 |
) |
| 95 |
ELSE |
| 96 |
dossier.adresse_normalisee |
| 97 |
END |
| 98 |
) as "'.__("Localisation").'"', |
| 99 |
'TRIM(CONCAT(personne_morale_denomination,\' \',personne_morale_nom,\' \',demandeur.particulier_nom)) as "'.__("petitionaire").'"', |
| 100 |
'-- Si une valeur est saisie dans la deuxième version du tableau des surfaces |
| 101 |
-- alors on récupère seulement ses valeurs |
| 102 |
CASE WHEN su2_avt_shon1 IS NOT NULL |
| 103 |
OR su2_avt_shon2 IS NOT NULL |
| 104 |
OR su2_avt_shon3 IS NOT NULL |
| 105 |
OR su2_avt_shon4 IS NOT NULL |
| 106 |
OR su2_avt_shon5 IS NOT NULL |
| 107 |
OR su2_avt_shon6 IS NOT NULL |
| 108 |
OR su2_avt_shon7 IS NOT NULL |
| 109 |
OR su2_avt_shon8 IS NOT NULL |
| 110 |
OR su2_avt_shon9 IS NOT NULL |
| 111 |
OR su2_avt_shon10 IS NOT NULL |
| 112 |
OR su2_avt_shon11 IS NOT NULL |
| 113 |
OR su2_avt_shon12 IS NOT NULL |
| 114 |
OR su2_avt_shon13 IS NOT NULL |
| 115 |
OR su2_avt_shon14 IS NOT NULL |
| 116 |
OR su2_avt_shon15 IS NOT NULL |
| 117 |
OR su2_avt_shon16 IS NOT NULL |
| 118 |
OR su2_avt_shon17 IS NOT NULL |
| 119 |
OR su2_avt_shon18 IS NOT NULL |
| 120 |
OR su2_avt_shon19 IS NOT NULL |
| 121 |
OR su2_avt_shon20 IS NOT NULL |
| 122 |
OR su2_avt_shon21 IS NOT NULL |
| 123 |
OR su2_avt_shon22 IS NOT NULL |
| 124 |
OR su2_avt_shon23 IS NOT NULL |
| 125 |
OR su2_avt_shon24 IS NOT NULL |
| 126 |
OR su2_avt_shon_tot IS NOT NULL |
| 127 |
OR su2_cstr_shon1 IS NOT NULL |
| 128 |
OR su2_cstr_shon2 IS NOT NULL |
| 129 |
OR su2_cstr_shon3 IS NOT NULL |
| 130 |
OR su2_cstr_shon4 IS NOT NULL |
| 131 |
OR su2_cstr_shon5 IS NOT NULL |
| 132 |
OR su2_cstr_shon6 IS NOT NULL |
| 133 |
OR su2_cstr_shon7 IS NOT NULL |
| 134 |
OR su2_cstr_shon8 IS NOT NULL |
| 135 |
OR su2_cstr_shon9 IS NOT NULL |
| 136 |
OR su2_cstr_shon10 IS NOT NULL |
| 137 |
OR su2_cstr_shon11 IS NOT NULL |
| 138 |
OR su2_cstr_shon12 IS NOT NULL |
| 139 |
OR su2_cstr_shon13 IS NOT NULL |
| 140 |
OR su2_cstr_shon14 IS NOT NULL |
| 141 |
OR su2_cstr_shon15 IS NOT NULL |
| 142 |
OR su2_cstr_shon16 IS NOT NULL |
| 143 |
OR su2_cstr_shon17 IS NOT NULL |
| 144 |
OR su2_cstr_shon18 IS NOT NULL |
| 145 |
OR su2_cstr_shon19 IS NOT NULL |
| 146 |
OR su2_cstr_shon20 IS NOT NULL |
| 147 |
OR su2_cstr_shon21 IS NOT NULL |
| 148 |
OR su2_cstr_shon22 IS NOT NULL |
| 149 |
OR su2_cstr_shon23 IS NOT NULL |
| 150 |
OR su2_cstr_shon24 IS NOT NULL |
| 151 |
OR su2_cstr_shon_tot IS NOT NULL |
| 152 |
OR su2_chge_shon1 IS NOT NULL |
| 153 |
OR su2_chge_shon2 IS NOT NULL |
| 154 |
OR su2_chge_shon3 IS NOT NULL |
| 155 |
OR su2_chge_shon4 IS NOT NULL |
| 156 |
OR su2_chge_shon5 IS NOT NULL |
| 157 |
OR su2_chge_shon6 IS NOT NULL |
| 158 |
OR su2_chge_shon7 IS NOT NULL |
| 159 |
OR su2_chge_shon8 IS NOT NULL |
| 160 |
OR su2_chge_shon9 IS NOT NULL |
| 161 |
OR su2_chge_shon10 IS NOT NULL |
| 162 |
OR su2_chge_shon11 IS NOT NULL |
| 163 |
OR su2_chge_shon12 IS NOT NULL |
| 164 |
OR su2_chge_shon13 IS NOT NULL |
| 165 |
OR su2_chge_shon14 IS NOT NULL |
| 166 |
OR su2_chge_shon15 IS NOT NULL |
| 167 |
OR su2_chge_shon16 IS NOT NULL |
| 168 |
OR su2_chge_shon17 IS NOT NULL |
| 169 |
OR su2_chge_shon18 IS NOT NULL |
| 170 |
OR su2_chge_shon19 IS NOT NULL |
| 171 |
OR su2_chge_shon20 IS NOT NULL |
| 172 |
OR su2_chge_shon21 IS NOT NULL |
| 173 |
OR su2_chge_shon22 IS NOT NULL |
| 174 |
OR su2_chge_shon23 IS NOT NULL |
| 175 |
OR su2_chge_shon24 IS NOT NULL |
| 176 |
OR su2_chge_shon_tot IS NOT NULL |
| 177 |
OR su2_demo_shon1 IS NOT NULL |
| 178 |
OR su2_demo_shon2 IS NOT NULL |
| 179 |
OR su2_demo_shon3 IS NOT NULL |
| 180 |
OR su2_demo_shon4 IS NOT NULL |
| 181 |
OR su2_demo_shon5 IS NOT NULL |
| 182 |
OR su2_demo_shon6 IS NOT NULL |
| 183 |
OR su2_demo_shon7 IS NOT NULL |
| 184 |
OR su2_demo_shon8 IS NOT NULL |
| 185 |
OR su2_demo_shon9 IS NOT NULL |
| 186 |
OR su2_demo_shon10 IS NOT NULL |
| 187 |
OR su2_demo_shon11 IS NOT NULL |
| 188 |
OR su2_demo_shon12 IS NOT NULL |
| 189 |
OR su2_demo_shon13 IS NOT NULL |
| 190 |
OR su2_demo_shon14 IS NOT NULL |
| 191 |
OR su2_demo_shon15 IS NOT NULL |
| 192 |
OR su2_demo_shon16 IS NOT NULL |
| 193 |
OR su2_demo_shon17 IS NOT NULL |
| 194 |
OR su2_demo_shon18 IS NOT NULL |
| 195 |
OR su2_demo_shon19 IS NOT NULL |
| 196 |
OR su2_demo_shon20 IS NOT NULL |
| 197 |
OR su2_demo_shon21 IS NOT NULL |
| 198 |
OR su2_demo_shon22 IS NOT NULL |
| 199 |
OR su2_demo_shon23 IS NOT NULL |
| 200 |
OR su2_demo_shon24 IS NOT NULL |
| 201 |
OR su2_demo_shon_tot IS NOT NULL |
| 202 |
OR su2_sup_shon1 IS NOT NULL |
| 203 |
OR su2_sup_shon2 IS NOT NULL |
| 204 |
OR su2_sup_shon3 IS NOT NULL |
| 205 |
OR su2_sup_shon4 IS NOT NULL |
| 206 |
OR su2_sup_shon5 IS NOT NULL |
| 207 |
OR su2_sup_shon6 IS NOT NULL |
| 208 |
OR su2_sup_shon7 IS NOT NULL |
| 209 |
OR su2_sup_shon8 IS NOT NULL |
| 210 |
OR su2_sup_shon9 IS NOT NULL |
| 211 |
OR su2_sup_shon10 IS NOT NULL |
| 212 |
OR su2_sup_shon11 IS NOT NULL |
| 213 |
OR su2_sup_shon12 IS NOT NULL |
| 214 |
OR su2_sup_shon13 IS NOT NULL |
| 215 |
OR su2_sup_shon14 IS NOT NULL |
| 216 |
OR su2_sup_shon15 IS NOT NULL |
| 217 |
OR su2_sup_shon16 IS NOT NULL |
| 218 |
OR su2_sup_shon17 IS NOT NULL |
| 219 |
OR su2_sup_shon18 IS NOT NULL |
| 220 |
OR su2_sup_shon19 IS NOT NULL |
| 221 |
OR su2_sup_shon20 IS NOT NULL |
| 222 |
OR su2_sup_shon21 IS NOT NULL |
| 223 |
OR su2_sup_shon22 IS NOT NULL |
| 224 |
OR su2_sup_shon23 IS NOT NULL |
| 225 |
OR su2_sup_shon24 IS NOT NULL |
| 226 |
OR su2_sup_shon_tot IS NOT NULL |
| 227 |
OR su2_tot_shon_tot IS NOT NULL |
| 228 |
THEN donnees_techniques.su2_tot_shon_tot |
| 229 |
ELSE donnees_techniques.su_tot_shon_tot |
| 230 |
END as "'.__("surface").'"', |
| 231 |
'COALESCE(service.abrege, tiers_consulte.abrege) as "'.__("service / tiers").'"', |
| 232 |
$case_type_consultation.' as "'.__("type_consultation").'"', |
| 233 |
$avis_rendu, |
| 234 |
); |
| 235 |
// |
| 236 |
$champRecherche = array( |
| 237 |
'consultation.consultation as "'.__("consultation").'"', |
| 238 |
'to_char(consultation.date_limite ,\'DD/MM/YYYY\') as "'.__("date_limite").'"', |
| 239 |
'consultation.dossier as "'.__("dossier").'"', |
| 240 |
// date de depots de piece complementaire |
| 241 |
'dossier.terrain_adresse_voie as "'.__("Localisation (voie)").'"', |
| 242 |
'dossier.terrain_adresse_code_postal as "'.__("Localisation (code postal)").'"', |
| 243 |
'dossier.terrain_adresse_localite as "'.__("Localisation (ville)").'"', |
| 244 |
'dossier.adresse_normalisee as "'.__("Adresse normalisée").'"', |
| 245 |
'demandeur.personne_morale_denomination as "'.__("petitionnaire personne morale").'"', |
| 246 |
'demandeur.particulier_nom as "'.__("petitionnaire particulier").'"', |
| 247 |
'-- Si une valeur est saisie dans la deuxième version du tableau des surfaces |
| 248 |
-- alors on récupère seulement ses valeurs |
| 249 |
CASE WHEN su2_avt_shon1 IS NOT NULL |
| 250 |
OR su2_avt_shon2 IS NOT NULL |
| 251 |
OR su2_avt_shon3 IS NOT NULL |
| 252 |
OR su2_avt_shon4 IS NOT NULL |
| 253 |
OR su2_avt_shon5 IS NOT NULL |
| 254 |
OR su2_avt_shon6 IS NOT NULL |
| 255 |
OR su2_avt_shon7 IS NOT NULL |
| 256 |
OR su2_avt_shon8 IS NOT NULL |
| 257 |
OR su2_avt_shon9 IS NOT NULL |
| 258 |
OR su2_avt_shon10 IS NOT NULL |
| 259 |
OR su2_avt_shon11 IS NOT NULL |
| 260 |
OR su2_avt_shon12 IS NOT NULL |
| 261 |
OR su2_avt_shon13 IS NOT NULL |
| 262 |
OR su2_avt_shon14 IS NOT NULL |
| 263 |
OR su2_avt_shon15 IS NOT NULL |
| 264 |
OR su2_avt_shon16 IS NOT NULL |
| 265 |
OR su2_avt_shon17 IS NOT NULL |
| 266 |
OR su2_avt_shon18 IS NOT NULL |
| 267 |
OR su2_avt_shon19 IS NOT NULL |
| 268 |
OR su2_avt_shon20 IS NOT NULL |
| 269 |
OR su2_avt_shon21 IS NOT NULL |
| 270 |
OR su2_avt_shon22 IS NOT NULL |
| 271 |
OR su2_avt_shon23 IS NOT NULL |
| 272 |
OR su2_avt_shon24 IS NOT NULL |
| 273 |
OR su2_avt_shon_tot IS NOT NULL |
| 274 |
OR su2_cstr_shon1 IS NOT NULL |
| 275 |
OR su2_cstr_shon2 IS NOT NULL |
| 276 |
OR su2_cstr_shon3 IS NOT NULL |
| 277 |
OR su2_cstr_shon4 IS NOT NULL |
| 278 |
OR su2_cstr_shon5 IS NOT NULL |
| 279 |
OR su2_cstr_shon6 IS NOT NULL |
| 280 |
OR su2_cstr_shon7 IS NOT NULL |
| 281 |
OR su2_cstr_shon8 IS NOT NULL |
| 282 |
OR su2_cstr_shon9 IS NOT NULL |
| 283 |
OR su2_cstr_shon10 IS NOT NULL |
| 284 |
OR su2_cstr_shon11 IS NOT NULL |
| 285 |
OR su2_cstr_shon12 IS NOT NULL |
| 286 |
OR su2_cstr_shon13 IS NOT NULL |
| 287 |
OR su2_cstr_shon14 IS NOT NULL |
| 288 |
OR su2_cstr_shon15 IS NOT NULL |
| 289 |
OR su2_cstr_shon16 IS NOT NULL |
| 290 |
OR su2_cstr_shon17 IS NOT NULL |
| 291 |
OR su2_cstr_shon18 IS NOT NULL |
| 292 |
OR su2_cstr_shon19 IS NOT NULL |
| 293 |
OR su2_cstr_shon20 IS NOT NULL |
| 294 |
OR su2_cstr_shon21 IS NOT NULL |
| 295 |
OR su2_cstr_shon22 IS NOT NULL |
| 296 |
OR su2_cstr_shon23 IS NOT NULL |
| 297 |
OR su2_cstr_shon24 IS NOT NULL |
| 298 |
OR su2_cstr_shon_tot IS NOT NULL |
| 299 |
OR su2_chge_shon1 IS NOT NULL |
| 300 |
OR su2_chge_shon2 IS NOT NULL |
| 301 |
OR su2_chge_shon3 IS NOT NULL |
| 302 |
OR su2_chge_shon4 IS NOT NULL |
| 303 |
OR su2_chge_shon5 IS NOT NULL |
| 304 |
OR su2_chge_shon6 IS NOT NULL |
| 305 |
OR su2_chge_shon7 IS NOT NULL |
| 306 |
OR su2_chge_shon8 IS NOT NULL |
| 307 |
OR su2_chge_shon9 IS NOT NULL |
| 308 |
OR su2_chge_shon10 IS NOT NULL |
| 309 |
OR su2_chge_shon11 IS NOT NULL |
| 310 |
OR su2_chge_shon12 IS NOT NULL |
| 311 |
OR su2_chge_shon13 IS NOT NULL |
| 312 |
OR su2_chge_shon14 IS NOT NULL |
| 313 |
OR su2_chge_shon15 IS NOT NULL |
| 314 |
OR su2_chge_shon16 IS NOT NULL |
| 315 |
OR su2_chge_shon17 IS NOT NULL |
| 316 |
OR su2_chge_shon18 IS NOT NULL |
| 317 |
OR su2_chge_shon19 IS NOT NULL |
| 318 |
OR su2_chge_shon20 IS NOT NULL |
| 319 |
OR su2_chge_shon21 IS NOT NULL |
| 320 |
OR su2_chge_shon22 IS NOT NULL |
| 321 |
OR su2_chge_shon23 IS NOT NULL |
| 322 |
OR su2_chge_shon24 IS NOT NULL |
| 323 |
OR su2_chge_shon_tot IS NOT NULL |
| 324 |
OR su2_demo_shon1 IS NOT NULL |
| 325 |
OR su2_demo_shon2 IS NOT NULL |
| 326 |
OR su2_demo_shon3 IS NOT NULL |
| 327 |
OR su2_demo_shon4 IS NOT NULL |
| 328 |
OR su2_demo_shon5 IS NOT NULL |
| 329 |
OR su2_demo_shon6 IS NOT NULL |
| 330 |
OR su2_demo_shon7 IS NOT NULL |
| 331 |
OR su2_demo_shon8 IS NOT NULL |
| 332 |
OR su2_demo_shon9 IS NOT NULL |
| 333 |
OR su2_demo_shon10 IS NOT NULL |
| 334 |
OR su2_demo_shon11 IS NOT NULL |
| 335 |
OR su2_demo_shon12 IS NOT NULL |
| 336 |
OR su2_demo_shon13 IS NOT NULL |
| 337 |
OR su2_demo_shon14 IS NOT NULL |
| 338 |
OR su2_demo_shon15 IS NOT NULL |
| 339 |
OR su2_demo_shon16 IS NOT NULL |
| 340 |
OR su2_demo_shon17 IS NOT NULL |
| 341 |
OR su2_demo_shon18 IS NOT NULL |
| 342 |
OR su2_demo_shon19 IS NOT NULL |
| 343 |
OR su2_demo_shon20 IS NOT NULL |
| 344 |
OR su2_demo_shon21 IS NOT NULL |
| 345 |
OR su2_demo_shon22 IS NOT NULL |
| 346 |
OR su2_demo_shon23 IS NOT NULL |
| 347 |
OR su2_demo_shon24 IS NOT NULL |
| 348 |
OR su2_demo_shon_tot IS NOT NULL |
| 349 |
OR su2_sup_shon1 IS NOT NULL |
| 350 |
OR su2_sup_shon2 IS NOT NULL |
| 351 |
OR su2_sup_shon3 IS NOT NULL |
| 352 |
OR su2_sup_shon4 IS NOT NULL |
| 353 |
OR su2_sup_shon5 IS NOT NULL |
| 354 |
OR su2_sup_shon6 IS NOT NULL |
| 355 |
OR su2_sup_shon7 IS NOT NULL |
| 356 |
OR su2_sup_shon8 IS NOT NULL |
| 357 |
OR su2_sup_shon9 IS NOT NULL |
| 358 |
OR su2_sup_shon10 IS NOT NULL |
| 359 |
OR su2_sup_shon11 IS NOT NULL |
| 360 |
OR su2_sup_shon12 IS NOT NULL |
| 361 |
OR su2_sup_shon13 IS NOT NULL |
| 362 |
OR su2_sup_shon14 IS NOT NULL |
| 363 |
OR su2_sup_shon15 IS NOT NULL |
| 364 |
OR su2_sup_shon16 IS NOT NULL |
| 365 |
OR su2_sup_shon17 IS NOT NULL |
| 366 |
OR su2_sup_shon18 IS NOT NULL |
| 367 |
OR su2_sup_shon19 IS NOT NULL |
| 368 |
OR su2_sup_shon20 IS NOT NULL |
| 369 |
OR su2_sup_shon21 IS NOT NULL |
| 370 |
OR su2_sup_shon22 IS NOT NULL |
| 371 |
OR su2_sup_shon23 IS NOT NULL |
| 372 |
OR su2_sup_shon24 IS NOT NULL |
| 373 |
OR su2_sup_shon_tot IS NOT NULL |
| 374 |
OR su2_tot_shon_tot IS NOT NULL |
| 375 |
THEN donnees_techniques.su2_tot_shon_tot |
| 376 |
ELSE donnees_techniques.su_tot_shon_tot |
| 377 |
END as "'.__("surface").'"', |
| 378 |
'COALESCE(service.abrege, tiers_consulte.abrege) as "'.__("service / tiers").'"', |
| 379 |
$avis_rendu, |
| 380 |
); |
| 381 |
$tri="ORDER BY consultation.date_limite::date DESC NULLS LAST, consultation.consultation DESC"; |
| 382 |
$edition=""; |
| 383 |
$selection=' WHERE om_utilisateur.login=\''.$_SESSION['login'].'\''; |
| 384 |
|
| 385 |
//Suppression des liens |
| 386 |
$tab_actions['corner']['ajouter']=NULL; |
| 387 |
$tab_actions['left']['consulter']=NULL; |
| 388 |
|
| 389 |
/** |
| 390 |
* OPTIONS - ADVSEARCH |
| 391 |
*/ |
| 392 |
// |
| 393 |
$champs = array(); |
| 394 |
// |
| 395 |
$champs['date_limite'] = array( |
| 396 |
'colonne' => 'date_limite', |
| 397 |
'table' => 'consultation', |
| 398 |
'libelle' => __('Date limite'), |
| 399 |
'type' => 'date', |
| 400 |
'where' => 'intervaldate', |
| 401 |
'taille' => '', |
| 402 |
); |
| 403 |
// |
| 404 |
$champs['dossier'] = array( |
| 405 |
'libelle' => __('dossier'), |
| 406 |
'type' => 'text', |
| 407 |
'table' => 'dossier', |
| 408 |
'colonne' => array( |
| 409 |
'dossier', |
| 410 |
'dossier_libelle', |
| 411 |
), |
| 412 |
'taille' => '', |
| 413 |
'max' => '', |
| 414 |
); |
| 415 |
// |
| 416 |
$champs['nature'] = array( |
| 417 |
'libelle' => __('nature des travaux'), |
| 418 |
'type' => 'text', |
| 419 |
'table' => 'donnees_techniques', |
| 420 |
'colonne' => array( |
| 421 |
'am_projet_desc', |
| 422 |
'co_projet_desc', |
| 423 |
), |
| 424 |
'taille' => '', |
| 425 |
'max' => '', |
| 426 |
); |
| 427 |
// |
| 428 |
$champs['adresse'] = array( |
| 429 |
'libelle' => __('localisation'), |
| 430 |
'help' => __("Recherche dans les champs numéro, voie, lieu-dit, code postal, localité, boite postale, cedex et dans l'adresse normalisée. |
| 431 |
|
| 432 |
La chaîne recherchée doit figurer dans l'un de ces champs. |
| 433 |
|
| 434 |
Par exemple, dans le cas d'une adresse avec la voie 'RUE DU ROUET' et la localité 'MARSEILLE' : |
| 435 |
- la recherche de 'RUE DU ROUET' donne des résultats car le champ voie contient 'RUE DU ROUET', |
| 436 |
- la recherche de 'MARSEILLE' donne des résultats car le champ localité contient 'MARSEILLE', |
| 437 |
- la recherche de 'RUE DU ROUET MARSEILLE' ne donne aucun résultat car ni le numéro ni la voie ni le lieu-dit ni le code postal ni la localité ni la boite postale ni le cedex ne contient 'RUE DU ROUET MARSEILLE'. |
| 438 |
|
| 439 |
Dans le cas de l'adresse normalisée, la recherche se fait sur la chaîne complète telle que retournée par la BAN. Il est donc conseillé d'utiliser le signe de remplacement * en début de votre recherche."), |
| 440 |
'type' => 'text', |
| 441 |
'table' => 'dossier', |
| 442 |
'colonne' => array( |
| 443 |
'terrain_adresse_voie_numero', |
| 444 |
'terrain_adresse_voie', |
| 445 |
'terrain_adresse_lieu_dit', |
| 446 |
'terrain_adresse_code_postal', |
| 447 |
'terrain_adresse_localite', |
| 448 |
'terrain_adresse_bp', |
| 449 |
'terrain_adresse_cedex', |
| 450 |
'adresse_normalisee', |
| 451 |
), |
| 452 |
'taille' => '', |
| 453 |
'max' => '', |
| 454 |
); |
| 455 |
// |
| 456 |
$champs['petitionnaire'] = array( |
| 457 |
'libelle' => __('petitionnaire'), |
| 458 |
'help' => __("Recherche dans les champs : nom, prénom, raison sociale, dénomination. |
| 459 |
|
| 460 |
La chaîne recherchée doit figurer dans l'un de ces champs. |
| 461 |
|
| 462 |
Par exemple, dans le cas d'un demandeur avec le nom 'DUPONT' et le prénom 'JEAN' : |
| 463 |
- la recherche de 'JEAN' donne des résultats car le champ prénom contient 'JEAN', |
| 464 |
- la recherche de 'DUPONT' donne des résultats car le champ nom contient 'DUPONT', |
| 465 |
- la recherche de 'DUPONT JEAN' ne donne aucun résultat car ni le nom ni le prénom ni la raison sociale ni la dénomination ne contient 'DUPONT JEAN'."), |
| 466 |
'type' => 'text', |
| 467 |
'table' => 'demandeur', |
| 468 |
'colonne' => array( |
| 469 |
'particulier_nom', |
| 470 |
'particulier_prenom', |
| 471 |
'personne_morale_raison_sociale', |
| 472 |
'personne_morale_denomination', |
| 473 |
), |
| 474 |
'taille' => '', |
| 475 |
'max' => '', |
| 476 |
); |
| 477 |
// |
| 478 |
$champs['service_abrege'] = array( |
| 479 |
'table' => 'service', |
| 480 |
'colonne' => 'abrege', |
| 481 |
'type' => 'text', |
| 482 |
'libelle' => __('Service (abrege)'), |
| 483 |
'taille' => '', |
| 484 |
'max' => '', |
| 485 |
); |
| 486 |
|
| 487 |
// Le champ de recherche de l'avis rendu est affiché seulement sur le menu des |
| 488 |
// demandes d'avis passées |
| 489 |
if ($obj === "demande_avis_passee" || $obj === "demande_avis") { |
| 490 |
// |
| 491 |
$champs['avis_consultation'] = array( |
| 492 |
'libelle' => __('avis rendu'), |
| 493 |
'type' => 'select', |
| 494 |
'table' => 'avis_consultation', |
| 495 |
'colonne' => 'avis_consultation', |
| 496 |
); |
| 497 |
} |
| 498 |
|
| 499 |
// advsearch -> options |
| 500 |
$options[] = array( |
| 501 |
'type' => 'search', |
| 502 |
'display' => true, |
| 503 |
'advanced' => $champs, |
| 504 |
'absolute_object' => 'consultation', |
| 505 |
'export' => array("csv"), |
| 506 |
); |
| 507 |
|
| 508 |
/* Gestion des onglets */ |
| 509 |
|
| 510 |
$sousformulaire = array(); |
| 511 |
$sousformulaire_parameters = array(); |
| 512 |
|
| 513 |
// Vérification du droit de lister les documents numérisés pour l'utilisateur connecté |
| 514 |
if ($f->isAccredited("document_numerise") || $f->isAccredited(array("demande_avis", "demande_avis_document_numerise"), "OR")) { |
| 515 |
$sousformulaire[] = "document_numerise"; |
| 516 |
// On modifie le lien du paramètre |
| 517 |
$sousformulaire_parameters["document_numerise"] = array( |
| 518 |
"title" => __("Pièces & documents"), |
| 519 |
"href" => "".OM_ROUTE_FORM."&obj=demande_avis&action=4&idx=".((isset($idx))?$idx:"")."&retourformulaire=".((isset($_GET['obj']))?$_GET['obj']:"")."&", |
| 520 |
); |
| 521 |
} |
| 522 |
|
| 523 |
// Onglet listant les consultations du DI lié à la demande d'avis courante |
| 524 |
$sousformulaire[] = "consultation"; |
| 525 |
$sousformulaire_parameters["consultation"] = array( |
| 526 |
"title" => __("consultation(s)"), |
| 527 |
"href" => "".OM_ROUTE_FORM."&obj=".(isset($obj) ? $obj : "demande_avis")."&action=10&idx=".((isset($idx))?$idx:"")."&", |
| 528 |
); |
| 529 |
|
| 530 |
/** |
| 531 |
* Options |
| 532 |
*/ |
| 533 |
// On met la ligne en couleur selon le type de condition |
| 534 |
$options[] = array( |
| 535 |
"type" => "condition", |
| 536 |
"field" => $case_type_consultation, |
| 537 |
"case" => array( |
| 538 |
array( |
| 539 |
"values" => array(__("avec avis attendu"), ), |
| 540 |
"style" => "consultation-avec-avis-attendu", |
| 541 |
), |
| 542 |
array( |
| 543 |
"values" => array(__("pour conformite"), ), |
| 544 |
"style" => "consultation-pour-conformite", |
| 545 |
), |
| 546 |
array( |
| 547 |
"values" => array(__("pour information"), ), |
| 548 |
"style" => "consultation-pour-information", |
| 549 |
), |
| 550 |
), |
| 551 |
); |
| 552 |
|
| 553 |
|
| 554 |
// Ajout du filtrage des sous dossier à la requête d'affichage du listing |
| 555 |
$sqlFiltreSD = $this->get_sql_filtre_sous_dossier($table.$selection); |
| 556 |
$selection .= $sqlFiltreSD['WHERE']; |
| 557 |
$table .= $sqlFiltreSD['FROM']; |
| 558 |
?> |
Parent Directory
| 
Revision Log