sql/pgsql/demande_avis.form.inc.php

<?php
//$Id: demande_avis.form.inc.php 5429 2015-11-13 17:08:37Z nmeucci $ 
//gen openMairie le 10/02/2011 20:32 
include('../gen/sql/pgsql/consultation.form.inc.php');

$tableSelect=DB_PREFIXE."consultation
    LEFT JOIN ".DB_PREFIXE."dossier 
        ON consultation.dossier=dossier.dossier
    LEFT JOIN ".DB_PREFIXE."service
        ON service.service=consultation.service
    LEFT OUTER JOIN ".DB_PREFIXE."instructeur
        ON instructeur.instructeur=dossier.instructeur
    LEFT OUTER JOIN ".DB_PREFIXE."division
        ON division.division=instructeur.division
    LEFT OUTER JOIN ".DB_PREFIXE."avis_consultation 
        ON consultation.avis_consultation=avis_consultation.avis_consultation 
    LEFT JOIN ".DB_PREFIXE."donnees_techniques
        ON donnees_techniques.dossier_instruction = dossier.dossier
    LEFT JOIN ".DB_PREFIXE."lien_dossier_demandeur
        ON dossier.dossier=lien_dossier_demandeur.dossier AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
    LEFT JOIN ".DB_PREFIXE."demandeur
        ON demandeur.demandeur=lien_dossier_demandeur.demandeur
    LEFT JOIN ".DB_PREFIXE."etat
        ON dossier.etat = etat.etat
        ";

$champs=array("consultation.consultation as \"consultation\"",
// Fieldset "Infos générales"
    // Fielsdet "Dossier"
        // 1ere ligne
            // 1ere colonne
                "consultation.dossier as \"dossier\"",
                "dossier.dossier_libelle as \"dossier_libelle\"",
                "division.chef as \"responsable\"",
                "etat.libelle as \"etat\"",
            // 2eme colonne
                "division.libelle as \"division\"",
                "concat(instructeur.nom,' tel. : '||instructeur.telephone) as \"instructeur\"",
        // 2eme ligne
                "to_char(dossier.date_depot ,'DD/MM/YYYY') as \"date_depot\"",
                "to_char(dossier.date_dernier_depot ,'DD/MM/YYYY') as \"date_dernier_depot\"",
                
                "CASE WHEN dossier.incomplet_notifie IS TRUE AND dossier.incompletude IS TRUE 
                    THEN to_char(dossier.date_limite_incompletude ,'DD/MM/YYYY') 
                    ELSE to_char(dossier.date_limite ,'DD/MM/YYYY')
                END as \"dossier_date_limite\"",
    
                //"to_char(dossier.date_limite ,'DD/MM/YYYY') as \"dossier_date_limite\"",
        // 3eme ligne
                "dossier.autorite_competente as \"autorite_competente\"",
        // 4eme ligne
                'TRIM(CONCAT(personne_morale_denomination,\' \',personne_morale_nom,\' \',demandeur.particulier_nom)) as "petitionnaire"',
                "TRIM(CONCAT(demandeur.numero,' ',demandeur.voie,' ',demandeur.complement,
                      ' ',demandeur.lieu_dit,' ',demandeur.code_postal,' ',demandeur.localite,
                      ' ',demandeur.bp,' ',demandeur.cedex,' ',demandeur.pays)) as \"adresse_petitionnaire\"",
                "replace(dossier.terrain_references_cadastrales, ';', ' ') as \"parcelle\"",
                'TRIM(CONCAT(dossier.terrain_adresse_voie_numero,\' \',dossier.terrain_adresse_voie,
    \' \', dossier.terrain_adresse_lieu_dit, \' \', dossier.terrain_adresse_code_postal,\' \', dossier.terrain_adresse_localite)) as "terrain"',
                "public.ST_AsText(dossier.geom::geometry) as \"geom\"",
    // Fieldset "Demande d'avis"
                "to_char(consultation.date_envoi ,'DD/MM/YYYY') as \"date_envoi\"",
                "concat(service.delai,' ','"._("mois")."') as \"delai\"",
                "to_char(consultation.date_limite ,'DD/MM/YYYY') as \"date_limite\"",

                // Fieldset "Avis rendu"
                "consultation.avis_consultation as \"avis_consultation\"",
                "consultation.motivation as \"motivation\"",
                "consultation.fichier as \"fichier\"",
// Fermeture fieldset "Infos générales"

// Fieldset "Principales caractéristiques du projet"
                "CONCAT_WS(
                    '<br/>',
                    CASE WHEN co_projet_desc = '' THEN
                        NULL
                    ELSE
                        TRIM(co_projet_desc)
                    END,
                    CASE WHEN ope_proj_desc = '' THEN
                        NULL
                    ELSE
                        TRIM(ope_proj_desc)
                    END,
                    CASE WHEN am_projet_desc = '' THEN
                        NULL
                    ELSE
                        TRIM(am_projet_desc)
                    END,
                    CASE WHEN dm_projet_desc = '' THEN
                        NULL
                    ELSE
                        TRIM(dm_projet_desc)
                    END
                ) as \"description_projet\"",
                "donnees_techniques.su_tot_shon_tot||' m²' as \"surface_total_projet\"",
                "REGEXP_REPLACE(CONCAT(
                    CASE
                        WHEN donnees_techniques.su_cstr_shon1 IS NULL
                        THEN ''
                        ELSE CONCAT('Habitation - ', donnees_techniques.su_cstr_shon1, ' m² <br/>')
                    END,
                    CASE
                        WHEN donnees_techniques.su_cstr_shon2 IS NULL
                        THEN ''
                        ELSE CONCAT('Hébergement hôtelier - ', donnees_techniques.su_cstr_shon2, ' m² <br/>')
                    END,
                    CASE
                        WHEN donnees_techniques.su_cstr_shon3 IS NULL
                        THEN ''
                        ELSE CONCAT('Bureaux - ', donnees_techniques.su_cstr_shon3, ' m² <br/>')
                    END,
                    CASE
                        WHEN donnees_techniques.su_cstr_shon4 IS NULL
                        THEN ''
                        ELSE CONCAT('Commerce - ', donnees_techniques.su_cstr_shon4, ' m² <br/>')
                    END,
                    CASE
                        WHEN donnees_techniques.su_cstr_shon5 IS NULL
                        THEN ''
                        ELSE CONCAT('Artisanat - ', donnees_techniques.su_cstr_shon5, ' m² <br/>')
                    END,
                    CASE
                        WHEN donnees_techniques.su_cstr_shon6 IS NULL
                        THEN ''
                        ELSE CONCAT('Industrie - ', donnees_techniques.su_cstr_shon6, ' m² <br/>')
                    END,
                    CASE
                        WHEN donnees_techniques.su_cstr_shon7 IS NULL
                        THEN ''
                        ELSE CONCAT('Exploitation agricole ou forestière - ', donnees_techniques.su_cstr_shon7, ' m² <br/>')
                    END,
                    CASE
                        WHEN donnees_techniques.su_cstr_shon8 IS NULL
                        THEN ''
                        ELSE CONCAT('Entrepôt - ', donnees_techniques.su_cstr_shon8, ' m² <br/>')
                    END, 
                    CASE
                        WHEN donnees_techniques.su_cstr_shon9 IS NULL
                        THEN ''
                        ELSE CONCAT('Service public ou d''intérêt collectif - ', donnees_techniques.su_cstr_shon9, ' m²')
                    END
                ), ' <br/>$', '') as \"surface\"",
                "donnees_techniques.co_tot_ind_nb as \"nombre_logement_crees_individuel\"",
                "donnees_techniques.co_tot_coll_nb as \"nombre_logement_crees_collectif\"",
                "donnees_techniques.co_statio_apr_nb as \"nombre_places_parking\"",
// Fermeture fieldset "Principales caractéristiques du projet"

                "consultation.service",
);

$sql_service="SELECT service.service, service.abrege||' '||service.libelle 
    FROM ".DB_PREFIXE."service 
    WHERE ((service.om_validite_debut IS NULL AND (service.om_validite_fin IS NULL OR service.om_validite_fin > CURRENT_DATE)) 
        OR (service.om_validite_debut <= CURRENT_DATE AND (service.om_validite_fin IS NULL OR service.om_validite_fin > CURRENT_DATE)))
    ORDER BY service.abrege, service.libelle";
$sql_service_by_id = "SELECT service.service, service.abrege||' '||service.libelle FROM ".DB_PREFIXE."service WHERE service = <idx>";
    
$sql_autorite_competente="SELECT autorite_competente.autorite_competente,
                                autorite_competente.libelle
                         FROM ".DB_PREFIXE."autorite_competente
                         ORDER BY autorite_competente.libelle";
$sql_autorite_competente_by_id = "SELECT autorite_competente.autorite_competente,
                                autorite_competente.libelle
                         FROM ".DB_PREFIXE."autorite_competente
                         WHERE autorite_competente.autorite_competente = <idx>";
             
?>
1	mbroquet	3730	<?php
2			//$Id: demande_avis.form.inc.php 5429 2015-11-13 17:08:37Z nmeucci $
3			//gen openMairie le 10/02/2011 20:32
4			include('../gen/sql/pgsql/consultation.form.inc.php');
5
6			$tableSelect=DB_PREFIXE."consultation
7			LEFT JOIN ".DB_PREFIXE."dossier
8			ON consultation.dossier=dossier.dossier
9			LEFT JOIN ".DB_PREFIXE."service
10			ON service.service=consultation.service
11			LEFT OUTER JOIN ".DB_PREFIXE."instructeur
12			ON instructeur.instructeur=dossier.instructeur
13			LEFT OUTER JOIN ".DB_PREFIXE."division
14			ON division.division=instructeur.division
15			LEFT OUTER JOIN ".DB_PREFIXE."avis_consultation
16			ON consultation.avis_consultation=avis_consultation.avis_consultation
17			LEFT JOIN ".DB_PREFIXE."donnees_techniques
18			ON donnees_techniques.dossier_instruction = dossier.dossier
19			LEFT JOIN ".DB_PREFIXE."lien_dossier_demandeur
20			ON dossier.dossier=lien_dossier_demandeur.dossier AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
21			LEFT JOIN ".DB_PREFIXE."demandeur
22			ON demandeur.demandeur=lien_dossier_demandeur.demandeur
23			LEFT JOIN ".DB_PREFIXE."etat
24			ON dossier.etat = etat.etat
25			";
26
27			$champs=array("consultation.consultation as \"consultation\"",
28			// Fieldset "Infos générales"
29			// Fielsdet "Dossier"
30			// 1ere ligne
31			// 1ere colonne
32			"consultation.dossier as \"dossier\"",
33			"dossier.dossier_libelle as \"dossier_libelle\"",
34			"division.chef as \"responsable\"",
35			"etat.libelle as \"etat\"",
36			// 2eme colonne
37			"division.libelle as \"division\"",
38			"concat(instructeur.nom,' tel. : '\|\|instructeur.telephone) as \"instructeur\"",
39			// 2eme ligne
40			"to_char(dossier.date_depot ,'DD/MM/YYYY') as \"date_depot\"",
41			"to_char(dossier.date_dernier_depot ,'DD/MM/YYYY') as \"date_dernier_depot\"",
42
43			"CASE WHEN dossier.incomplet_notifie IS TRUE AND dossier.incompletude IS TRUE
44			THEN to_char(dossier.date_limite_incompletude ,'DD/MM/YYYY')
45			ELSE to_char(dossier.date_limite ,'DD/MM/YYYY')
46			END as \"dossier_date_limite\"",
47
48			//"to_char(dossier.date_limite ,'DD/MM/YYYY') as \"dossier_date_limite\"",
49			// 3eme ligne
50			"dossier.autorite_competente as \"autorite_competente\"",
51			// 4eme ligne
52			'TRIM(CONCAT(personne_morale_denomination,\' \',personne_morale_nom,\' \',demandeur.particulier_nom)) as "petitionnaire"',
53			"TRIM(CONCAT(demandeur.numero,' ',demandeur.voie,' ',demandeur.complement,
54			' ',demandeur.lieu_dit,' ',demandeur.code_postal,' ',demandeur.localite,
55			' ',demandeur.bp,' ',demandeur.cedex,' ',demandeur.pays)) as \"adresse_petitionnaire\"",
56			"replace(dossier.terrain_references_cadastrales, ';', ' ') as \"parcelle\"",
57			'TRIM(CONCAT(dossier.terrain_adresse_voie_numero,\' \',dossier.terrain_adresse_voie,
58			\' \', dossier.terrain_adresse_lieu_dit, \' \', dossier.terrain_adresse_code_postal,\' \', dossier.terrain_adresse_localite)) as "terrain"',
59			"public.ST_AsText(dossier.geom::geometry) as \"geom\"",
60			// Fieldset "Demande d'avis"
61			"to_char(consultation.date_envoi ,'DD/MM/YYYY') as \"date_envoi\"",
62			"concat(service.delai,' ','"._("mois")."') as \"delai\"",
63			"to_char(consultation.date_limite ,'DD/MM/YYYY') as \"date_limite\"",
64
65	nhaye	3814	// Fieldset "Avis rendu"
66			"consultation.avis_consultation as \"avis_consultation\"",
67			"consultation.motivation as \"motivation\"",
68			"consultation.fichier as \"fichier\"",
69			// Fermeture fieldset "Infos générales"
70
71	mbroquet	3730	// Fieldset "Principales caractéristiques du projet"
72			"CONCAT_WS(
73			'<br/>',
74			CASE WHEN co_projet_desc = '' THEN
75			NULL
76			ELSE
77			TRIM(co_projet_desc)
78			END,
79			CASE WHEN ope_proj_desc = '' THEN
80			NULL
81			ELSE
82			TRIM(ope_proj_desc)
83			END,
84			CASE WHEN am_projet_desc = '' THEN
85			NULL
86			ELSE
87			TRIM(am_projet_desc)
88			END,
89			CASE WHEN dm_projet_desc = '' THEN
90			NULL
91			ELSE
92			TRIM(dm_projet_desc)
93			END
94			) as \"description_projet\"",
95			"donnees_techniques.su_tot_shon_tot\|\|' m²' as \"surface_total_projet\"",
96			"REGEXP_REPLACE(CONCAT(
97			CASE
98			WHEN donnees_techniques.su_cstr_shon1 IS NULL
99			THEN ''
100			ELSE CONCAT('Habitation - ', donnees_techniques.su_cstr_shon1, ' m² <br/>')
101			END,
102			CASE
103			WHEN donnees_techniques.su_cstr_shon2 IS NULL
104			THEN ''
105			ELSE CONCAT('Hébergement hôtelier - ', donnees_techniques.su_cstr_shon2, ' m² <br/>')
106			END,
107			CASE
108			WHEN donnees_techniques.su_cstr_shon3 IS NULL
109			THEN ''
110			ELSE CONCAT('Bureaux - ', donnees_techniques.su_cstr_shon3, ' m² <br/>')
111			END,
112			CASE
113			WHEN donnees_techniques.su_cstr_shon4 IS NULL
114			THEN ''
115			ELSE CONCAT('Commerce - ', donnees_techniques.su_cstr_shon4, ' m² <br/>')
116			END,
117			CASE
118			WHEN donnees_techniques.su_cstr_shon5 IS NULL
119			THEN ''
120			ELSE CONCAT('Artisanat - ', donnees_techniques.su_cstr_shon5, ' m² <br/>')
121			END,
122			CASE
123			WHEN donnees_techniques.su_cstr_shon6 IS NULL
124			THEN ''
125			ELSE CONCAT('Industrie - ', donnees_techniques.su_cstr_shon6, ' m² <br/>')
126			END,
127			CASE
128			WHEN donnees_techniques.su_cstr_shon7 IS NULL
129			THEN ''
130			ELSE CONCAT('Exploitation agricole ou forestière - ', donnees_techniques.su_cstr_shon7, ' m² <br/>')
131			END,
132			CASE
133			WHEN donnees_techniques.su_cstr_shon8 IS NULL
134			THEN ''
135			ELSE CONCAT('Entrepôt - ', donnees_techniques.su_cstr_shon8, ' m² <br/>')
136			END,
137			CASE
138			WHEN donnees_techniques.su_cstr_shon9 IS NULL
139			THEN ''
140			ELSE CONCAT('Service public ou d''intérêt collectif - ', donnees_techniques.su_cstr_shon9, ' m²')
141			END
142			), ' <br/>$', '') as \"surface\"",
143			"donnees_techniques.co_tot_ind_nb as \"nombre_logement_crees_individuel\"",
144			"donnees_techniques.co_tot_coll_nb as \"nombre_logement_crees_collectif\"",
145			"donnees_techniques.co_statio_apr_nb as \"nombre_places_parking\"",
146			// Fermeture fieldset "Principales caractéristiques du projet"
147
148			"consultation.service",
149			);
150
151			$sql_service="SELECT service.service, service.abrege\|\|' '\|\|service.libelle
152			FROM ".DB_PREFIXE."service
153			WHERE ((service.om_validite_debut IS NULL AND (service.om_validite_fin IS NULL OR service.om_validite_fin > CURRENT_DATE))
154			OR (service.om_validite_debut <= CURRENT_DATE AND (service.om_validite_fin IS NULL OR service.om_validite_fin > CURRENT_DATE)))
155			ORDER BY service.abrege, service.libelle";
156			$sql_service_by_id = "SELECT service.service, service.abrege\|\|' '\|\|service.libelle FROM ".DB_PREFIXE."service WHERE service = <idx>";
157
158			$sql_autorite_competente="SELECT autorite_competente.autorite_competente,
159			autorite_competente.libelle
160			FROM ".DB_PREFIXE."autorite_competente
161			ORDER BY autorite_competente.libelle";
162			$sql_autorite_competente_by_id = "SELECT autorite_competente.autorite_competente,
163			autorite_competente.libelle
164			FROM ".DB_PREFIXE."autorite_competente
165			WHERE autorite_competente.autorite_competente = <idx>";
166
167			?>