sql/pgsql/demande_avis.form.inc.php

<?php
//$Id$ 
//gen openMairie le 10/02/2011 20:32 
include('../gen/sql/pgsql/consultation.form.inc.php');

$tableSelect=DB_PREFIXE."consultation
    INNER JOIN ".DB_PREFIXE."dossier 
        ON consultation.dossier=dossier.dossier
    INNER 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 OUTER JOIN ".DB_PREFIXE."travaux
        ON travaux.travaux=dossier.travaux
    LEFT OUTER JOIN ".DB_PREFIXE."architecte
        ON architecte.architecte=dossier.architecte
        ";

$champs=array("consultation.consultation as \"consultation\"",
// Fieldset "Infos générales"
    // 1ere ligne
        // 1ere colonne
                "consultation.dossier as \"dossier\"",
                "division.chef as \"chef\"",
                "dossier.autorite_competente as \"autorite_competente\"",
        // 2eme colonne
                "division.libelle as \"division\"",
                "concat(instructeur.nom,' tel. : '||instructeur.telephone) as \"instructeur\"",
                // XXX comunne/quaartier (manque les criteres d'affectation auto ?)
                "'XXX' as \"commune_quartier\"",
    // 2eme ligne
        // 1er groupe
                "to_char(dossier.date_depot ,'DD/MM/YYYY') as \"date_depot\"",
                // XXX date derniere modif
                "'XXX' as \"date_derniere_modif\"",
        // 2eme groupe
                "to_char(consultation.date_envoi ,'DD/MM/YYYY') as \"date_envoi\"",
                // XXX delai de reponse en jours
                "'XXX' as \"delai_jour\"",
                "to_char(consultation.date_limite ,'DD/MM/YYYY') as \"date_limite\"",
    // 3eme ligne
                "dossier.demandeur_nom as \"petitionnaire\"",
                "trim(concat(dossier.demandeur_adresse,' ',dossier.demandeur_cp,' ',demandeur_ville)) as \"adresse_petitionnaire\"",
                "trim(concat(architecte.nom,' ',architecte.prenom,' tel. '||architecte.telephone)) as \"architecte\"",
                //XXX parcelle
                "'XXX' as \"parcelle\"",
                "trim(concat(dossier.terrain_numero,' ',dossier.terrain_numero_complement,' ',
                dossier.terrain_adresse,' ',dossier.terrain_adresse_complement,
                ' ',terrain_cp,' ',terrain_ville)) as \"terrain\"",
// Fieldset "Caractéristiques principales du Règlement en vigueur"
                //XXX zonages
                "'XXX' as \"zonages\"",
                //XXX risques
                "'XXX' as \"risques\"",
                //XXX servitudes et reservations
                "'XXX' as \"servitudes_reservations\"",
                //XXX ALIGNEMENT, VOIES NOUVELLES ET ZONES NON AEDIFICANDI
                "'XXX' as \"alignement_voie\"",
                //XXX monuments_historique
                "'XXX' as \"monuments_historique\"",
                //XXX autres_dispo
                "'XXX' as \"autres_dispo\"",
// Fieldset "Principales caractéristiques du dossier"
    // 1ere ligne
        // 1er group
                "travaux.libelle as \"travaux\"",
        // 2eme group
                "dossier.shon as \"shon_total\"",
                "dossier.shon_calcul as \"shon_total_calcul\"",
                "dossier.terrain_surface as \"terrain_surface\"",
        // 3eme group
                //XXX repartition_shon_destination
                "'XXX' as \"repartition_shon_destination\"",
        // 4eme group
                "dossier.logement_nombre as \"logement_nombre\"",
        // 5eme group
                //XXX places_parking
                "'XXX' as \"places_parking\"",
    // 2eme ligne
        // 1er group
                "dossier.description as \"description_operation\"",
        // 2eme group
                //XXX Si CU, opération projetée
                "'XXX' as \"cu_operation\"",
                "consultation.service"
);

// Action : pdf
    $portlet_actions['pdfetat'] =
        array('lien' => "../app/pdf_consultation.php?idx=",
              'id' => "&amp;obj=consultation",
              'lib' => "<span class=\"om-prev-icon om-icon-16 om-icon-fix pdf-16\" title=\"".
                    _("Edition")."\">"._("Edition")."</span>",
              'ordre' => 30,
              'ajax' => false);
        
// Action : supprimer
    $portlet_actions['supprimer'] = NULL;
    
$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)))";
$sql_service_by_id = "SELECT service.service, service.abrege||' '||service.libelle FROM ".DB_PREFIXE."service WHERE service = <idx>";
             
?>
1	nhaye	652	<?php
2			//$Id$
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			INNER JOIN ".DB_PREFIXE."dossier
8			ON consultation.dossier=dossier.dossier
9			INNER 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 OUTER JOIN ".DB_PREFIXE."travaux
18			ON travaux.travaux=dossier.travaux
19			LEFT OUTER JOIN ".DB_PREFIXE."architecte
20			ON architecte.architecte=dossier.architecte
21			";
22
23	nhaye	700	$champs=array("consultation.consultation as \"consultation\"",
24			// Fieldset "Infos générales"
25			// 1ere ligne
26			// 1ere colonne
27			"consultation.dossier as \"dossier\"",
28			"division.chef as \"chef\"",
29			"dossier.autorite_competente as \"autorite_competente\"",
30			// 2eme colonne
31			"division.libelle as \"division\"",
32			"concat(instructeur.nom,' tel. : '\|\|instructeur.telephone) as \"instructeur\"",
33			// XXX comunne/quaartier (manque les criteres d'affectation auto ?)
34			"'XXX' as \"commune_quartier\"",
35			// 2eme ligne
36			// 1er groupe
37			"to_char(dossier.date_depot ,'DD/MM/YYYY') as \"date_depot\"",
38			// XXX date derniere modif
39			"'XXX' as \"date_derniere_modif\"",
40			// 2eme groupe
41			"to_char(consultation.date_envoi ,'DD/MM/YYYY') as \"date_envoi\"",
42			// XXX delai de reponse en jours
43			"'XXX' as \"delai_jour\"",
44			"to_char(consultation.date_limite ,'DD/MM/YYYY') as \"date_limite\"",
45			// 3eme ligne
46			"dossier.demandeur_nom as \"petitionnaire\"",
47			"trim(concat(dossier.demandeur_adresse,' ',dossier.demandeur_cp,' ',demandeur_ville)) as \"adresse_petitionnaire\"",
48			"trim(concat(architecte.nom,' ',architecte.prenom,' tel. '\|\|architecte.telephone)) as \"architecte\"",
49			//XXX parcelle
50			"'XXX' as \"parcelle\"",
51			"trim(concat(dossier.terrain_numero,' ',dossier.terrain_numero_complement,' ',
52			dossier.terrain_adresse,' ',dossier.terrain_adresse_complement,
53			' ',terrain_cp,' ',terrain_ville)) as \"terrain\"",
54			// Fieldset "Caractéristiques principales du Règlement en vigueur"
55			//XXX zonages
56			"'XXX' as \"zonages\"",
57			//XXX risques
58			"'XXX' as \"risques\"",
59			//XXX servitudes et reservations
60			"'XXX' as \"servitudes_reservations\"",
61			//XXX ALIGNEMENT, VOIES NOUVELLES ET ZONES NON AEDIFICANDI
62			"'XXX' as \"alignement_voie\"",
63			//XXX monuments_historique
64			"'XXX' as \"monuments_historique\"",
65			//XXX autres_dispo
66			"'XXX' as \"autres_dispo\"",
67			// Fieldset "Principales caractéristiques du dossier"
68			// 1ere ligne
69			// 1er group
70			"travaux.libelle as \"travaux\"",
71			// 2eme group
72			"dossier.shon as \"shon_total\"",
73			"dossier.shon_calcul as \"shon_total_calcul\"",
74			"dossier.terrain_surface as \"terrain_surface\"",
75			// 3eme group
76			//XXX repartition_shon_destination
77			"'XXX' as \"repartition_shon_destination\"",
78			// 4eme group
79			"dossier.logement_nombre as \"logement_nombre\"",
80			// 5eme group
81			//XXX places_parking
82			"'XXX' as \"places_parking\"",
83			// 2eme ligne
84			// 1er group
85			"dossier.description as \"description_operation\"",
86			// 2eme group
87			//XXX Si CU, opération projetée
88			"'XXX' as \"cu_operation\"",
89			"consultation.service"
90	nhaye	652	);
91
92	nhaye	679	// Action : pdf
93	nhaye	652	$portlet_actions['pdfetat'] =
94			array('lien' => "../app/pdf_consultation.php?idx=",
95			'id' => "&obj=consultation",
96			'lib' => "<span class=\"om-prev-icon om-icon-16 om-icon-fix pdf-16\" title=\"".
97			_("Edition")."\">"._("Edition")."</span>",
98			'ordre' => 30,
99			'ajax' => false);
100
101			// Action : supprimer
102			$portlet_actions['supprimer'] = NULL;
103
104			$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)))";
105			$sql_service_by_id = "SELECT service.service, service.abrege\|\|' '\|\|service.libelle FROM ".DB_PREFIXE."service WHERE service = <idx>";
106
107			?>