--------------------------------------------------------------------------------
-- Script de mise à jour vers la version v3.6.0-a1
--
-- XXX Ce fichier doit être renommé en v3.6.0-a1.sql au moment de la release
--
-- @package openfoncier
-- @version SVN : $Id$
-------------------------------------------------------------------------------

--
-- XXX Commenter
--
UPDATE om_widget SET libelle = 'Dossiers événement incomplet ou majoration sans RAR' WHERE om_widget = 10;

--
-- Renommage de la clé primaire selon la convention openmairie
--
ALTER TABLE om_requete RENAME COLUMN id TO om_requete;

--
-- Ajout de consultation pour les tests
--
INSERT INTO consultation VALUES (nextval('consultation_seq'), 'PC0130551200002P0', '2013-01-01', NULL, '2013-02-01', 1, NULL, '2013-01-01', '', NULL, 't', '120000000001', 'OP_FAILURE', 't');
INSERT INTO consultation VALUES (nextval('consultation_seq'), 'PC0130551200001P0', '2013-03-01', NULL, '2013-04-01', 1, NULL, '2013-03-01', '', NULL, 't', '120000000001', 'OP_FAILURE', 't');

--
-- Modification dossier d'instruction
--
UPDATE dossier SET evenement_suivant_tacite = 64 WHERE dossier = 'AT0130551200001P0';

--
-- Insertion de données dans la table lien_demande_type_etat_dossier_autorisation
-- Permet d'ajouter une demande de modification après l'accord d'un DI
--
COPY lien_demande_type_etat_dossier_autorisation (lien_demande_type_etat_dossier_autorisation, demande_type, etat_dossier_autorisation) FROM stdin;
1	17	2
2	18	2
3	19	2
4	20	2
5	21	2
6	22	2
7	23	2
8	24	2
\.
SELECT pg_catalog.setval('lien_demande_type_etat_dossier_autorisation_seq', 9, false);

UPDATE dossier SET evenement_suivant_tacite = 64 WHERE dossier = 'AT0130551200001P0';

--
-- Ajout de champs dans les requêtes pour les éditions
--
-- Consultation
UPDATE om_requete SET requete =
'SELECT 


    service.libelle as service, 
    service.adresse, 
    service.adresse2, 
    service.cp, 
    service.ville, 
    dossier.dossier,
    replace(dossier.terrain_references_cadastrales, '';'', '' ''),
    dossier_autorisation_type_detaille.libelle as nature, 
    civilite.libelle as demandeur_civilite, 
    to_char(dossier.date_depot,''DD/MM/YYYY'') as date_depot, 
    to_char(dossier.date_rejet,''DD/MM/YYYY'') as date_rejet, 
    to_char(consultation.date_envoi,''DD/MM/YYYY'') as date_envoi, 
    consultation.code_barres as code_barres,
    donnees_techniques.co_projet_desc,
    donnees_techniques.am_projet_desc,
    donnees_techniques.dm_projet_desc,
    donnees_techniques.am_lot_max_nb,
    donnees_techniques.am_lot_max_shon,
    donnees_techniques.su_cstr_shon_tot,
    donnees_techniques.su_demo_shon_tot,
    CONCAT(''Habitation : '', donnees_techniques.su_trsf_shon1, 
    '' - Hébergement hôtelier : '', donnees_techniques.su_trsf_shon2, 
    '' - Bureaux : '', donnees_techniques.su_trsf_shon3, 
    '' - Commerce : '', donnees_techniques.su_trsf_shon4,
    '' - Artisanat : '', donnees_techniques.su_trsf_shon5,
    '' - Industrie : '', donnees_techniques.su_trsf_shon6,
    '' - Exploitation agricole ou forestière : '', donnees_techniques.su_trsf_shon7,
    '' - Entrepôt : '', donnees_techniques.su_trsf_shon8,
    '' - Service public ou d''''intérêt collectif : '', donnees_techniques.su_trsf_shon9),
    donnees_techniques.co_tot_log_nb,
    donnees_techniques.co_statio_place_nb
    
    
FROM 


    &DB_PREFIXEconsultation 
    LEFT JOIN &DB_PREFIXEservice 
        ON service.service=consultation.service 
    LEFT JOIN &DB_PREFIXEdossier 
        ON dossier.dossier=consultation.dossier 
    LEFT JOIN &DB_PREFIXEdonnees_techniques 
        ON dossier.dossier = donnees_techniques.dossier_instruction
    LEFT JOIN &DB_PREFIXEdossier_autorisation 
        ON dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation 
    LEFT JOIN &DB_PREFIXEinstructeur 
        ON instructeur.instructeur=dossier.instructeur 
    LEFT JOIN &DB_PREFIXElien_dossier_demandeur 
        ON lien_dossier_demandeur.dossier=dossier.dossier 
    LEFT JOIN &DB_PREFIXEdemandeur 
        ON demandeur.demandeur=lien_dossier_demandeur.demandeur 
    LEFT JOIN &DB_PREFIXEcivilite 
        ON demandeur.personne_morale_civilite=civilite.civilite OR demandeur.particulier_civilite=civilite.civilite 
    LEFT JOIN &DB_PREFIXEdossier_instruction_type 
        ON dossier.dossier_instruction_type=dossier_instruction_type.dossier_instruction_type 
    LEFT JOIN &DB_PREFIXEdossier_autorisation_type_detaille 
        ON dossier_instruction_type.dossier_autorisation_type_detaille=dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
        
         
WHERE 


    consultation.consultation = &idx AND lien_dossier_demandeur.petitionnaire_principal IS TRUE' 
WHERE code = 'consultation' ;

-- Dossier
UPDATE om_requete SET requete =
'SELECT
    --
    -- INFORMATIONS CONCERNANT LE DOSSIER
    --

    dossier.dossier as dossier,
    dossier.dossier_autorisation,

    dossier_autorisation_type_detaille.code as code_type_da_det,
    dossier_autorisation_type_detaille.libelle as libelle_type_da_det,
    dossier_autorisation_type.code as code_type_da,
    dossier_autorisation_type.libelle as libelle_type_da,
    dossier_instruction_type.code as code_type_di,
    dossier_instruction_type.libelle as libelle_type_di,
    dossier.delai as delai_di,
    replace(dossier.terrain_references_cadastrales, '';'', '' ''),

    avis_decision.libelle as decision,

    --
    -- INSTRUCTEUR
    --

    instructeur.nom as instructeur_nom,
    instructeur.telephone as instructeur_telephone,
    division.code as instructeur_division,
    om_utilisateur.email as instructeur_email,

    --
    -- SIGNATAIRES
    --
    division.chef as division_chef,
    direction.chef as direction_chef,

    --
    -- ADRESSE TERRAIN
    --

    dossier.terrain_adresse_voie_numero,
    dossier.complement as adresse_voie,
    dossier.terrain_adresse_lieu_dit,
    dossier.terrain_adresse_bp,
    dossier.terrain_adresse_code_postal,
    dossier.terrain_adresse_localite as terrain_adresse_ville,
    dossier.terrain_adresse_cedex,

    arrondissement.libelle as arrondissement,

    --
    -- PETITIONNAIRE PRINCIPAL
    --

    CASE WHEN petitionnaire_principal.qualite=''particulier''
        THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom))
        ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
    END as petitionnaire_principal_nom,
    petitionnaire_principal.numero as petitionnaire_principal_numero,
    petitionnaire_principal.voie as petitionnaire_principal_voie,
    petitionnaire_principal.complement as petitionnaire_principal_complement,
    petitionnaire_principal.lieu_dit as petitionnaire_principal_lieu_dit,
    petitionnaire_principal.bp as petitionnaire_principal_bp,
    petitionnaire_principal.code_postal as petitionnaire_principal_code_postal,
    petitionnaire_principal.localite as petitionnaire_principal_ville,
    petitionnaire_principal.cedex as petitionnaire_principal_cedex,
    petitionnaire_principal.pays as petitionnaire_principal_pays,

    --
    -- DELEGATAIRE
    --

    CASE
        WHEN delegataire.qualite=''particulier''
        THEN TRIM(CONCAT(delegataire.particulier_nom, '' '', delegataire.particulier_prenom))
        ELSE TRIM(CONCAT(delegataire.personne_morale_raison_sociale, '' '', delegataire.personne_morale_denomination))
    END as delegataire_nom,
    delegataire.numero as delegataire_numero,
    delegataire.voie as delegataire_voie,
    delegataire.complement as delegataire_complement,
    delegataire.lieu_dit as delegataire_lieu_dit,
    delegataire.bp as delegataire_bp,
    delegataire.code_postal as delegataire_code_postal,
    delegataire.localite as delegataire_ville,
    delegataire.cedex as delegataire_cedex,
    delegataire.pays as delegataire_pays,

    --
    -- CORRESPONDANT : destinataire du courrier. Il est le délégataire ou le pétitionnaire principal
    --

    CASE
        WHEN delegataire.qualite IS NULL
        THEN
            CASE WHEN petitionnaire_principal.qualite=''particulier''
                    THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom))
                    ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
            END
        ELSE
            CASE WHEN delegataire.qualite=''particulier''
                THEN TRIM(CONCAT(delegataire.particulier_nom, '' '', delegataire.particulier_prenom))
                ELSE TRIM(CONCAT(delegataire.personne_morale_raison_sociale, '' '', delegataire.personne_morale_denomination))
            END
    END as correspondant_nom,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.numero
        ELSE delegataire.numero
    END as correspondant_numero,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.voie
        ELSE delegataire.voie
    END as correspondant_voie,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.complement
        ELSE delegataire.complement
    END as correspondant_complement,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.lieu_dit
        ELSE delegataire.lieu_dit
    END as correspondant_lieu_dit,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.bp
        ELSE delegataire.bp
    END as correspondant_bp,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.code_postal
        ELSE delegataire.code_postal
    END as correspondant_code_postal,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.localite
        ELSE delegataire.localite
    END as correspondant_ville,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.cedex
        ELSE delegataire.cedex
    END as correspondant_cedex,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.pays
        ELSE delegataire.pays
    END as correspondant_pays,

    --
    -- DATES DI
    --

    to_char(dossier.date_depot, ''DD/MM/YYYY'') as date_depot,
    to_char(dossier.date_decision, ''DD/MM/YYYY'') as date_decision,
    to_char(dossier.date_limite,''DD/MM/YYYY'') as date_limite,
    to_char(dossier.date_achevement,''DD/MM/YYYY'') as date_achevement,
    to_char(dossier.date_conformite,''DD/MM/YYYY'') as date_conformite,
    
    --
    -- DONNÉES TECHNIQUES
    --
    donnees_techniques.co_projet_desc,
    donnees_techniques.am_projet_desc,
    donnees_techniques.dm_projet_desc,
    donnees_techniques.am_lot_max_nb,
    donnees_techniques.am_lot_max_shon,
    donnees_techniques.su_cstr_shon_tot,
    donnees_techniques.su_demo_shon_tot,
    CONCAT(''Habitation : '', donnees_techniques.su_trsf_shon1, 
    '' - Hébergement hôtelier : '', donnees_techniques.su_trsf_shon2, 
    '' - Bureaux : '', donnees_techniques.su_trsf_shon3, 
    '' - Commerce : '', donnees_techniques.su_trsf_shon4,
    '' - Artisanat : '', donnees_techniques.su_trsf_shon5,
    '' - Industrie : '', donnees_techniques.su_trsf_shon6,
    '' - Exploitation agricole ou forestière : '', donnees_techniques.su_trsf_shon7,
    '' - Entrepôt : '', donnees_techniques.su_trsf_shon8,
    '' - Service public ou d''''intérêt collectif : '', donnees_techniques.su_trsf_shon9),
    donnees_techniques.co_tot_log_nb,
    donnees_techniques.co_statio_place_nb

FROM
    &DB_PREFIXEdossier
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        dossier.dossier = lien_dossier_demandeur.dossier
LEFT JOIN
    &DB_PREFIXEdemandeur as petitionnaire_principal
    ON
        lien_dossier_demandeur.demandeur = petitionnaire_principal.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN
    &DB_PREFIXEdemandeur as delegataire
    ON
        lien_dossier_demandeur.demandeur = delegataire.demandeur AND delegataire.type_demandeur = ''delegataire''
LEFT JOIN
    &DB_PREFIXEdossier_instruction_type
    ON
        dossier.dossier_instruction_type = dossier_instruction_type.dossier_instruction_type
LEFT JOIN
    &DB_PREFIXEdossier_autorisation
    ON
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type_detaille
    ON
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type
    ON
        dossier_autorisation_type_detaille.dossier_autorisation_type = dossier_autorisation_type.dossier_autorisation_type
LEFT JOIN
    &DB_PREFIXEinstructeur
    ON
        dossier.instructeur = instructeur.instructeur
LEFT JOIN
    &DB_PREFIXEom_utilisateur
    ON
        om_utilisateur.om_utilisateur = instructeur.om_utilisateur
LEFT JOIN
    &DB_PREFIXEdivision
    ON
        instructeur.division = division.division
LEFT JOIN
    &DB_PREFIXEdirection
    ON
        division.direction = direction.direction
LEFT JOIN
    &DB_PREFIXEarrondissement
    ON
        dossier.terrain_adresse_code_postal = arrondissement.code_postal
LEFT JOIN
    &DB_PREFIXEavis_decision
    ON
        dossier.avis_decision = avis_decision.avis_decision
LEFT JOIN
    &DB_PREFIXEetat
    ON
        dossier.etat = etat.etat
LEFT JOIN
    &DB_PREFIXEdonnees_techniques
    ON
    dossier.dossier = donnees_techniques.dossier_instruction
WHERE dossier.dossier = ''&idx''' 
WHERE code = 'dossier' ;

-- Instruction
UPDATE om_requete SET requete =
'SELECT

    --
    -- CHAMPS DE L''EVENEMENT D''INSTRUCTION

    instruction.complement,
    instruction.complement2,
    instruction.code_barres as code_barres,
    om_lettretype.libelle as instruction_objet,

    --
    -- INFORMATIONS CONCERNANT LE DOSSIER
    --

    dossier.dossier as dossier,
    dossier.dossier_autorisation,

    dossier_autorisation_type_detaille.code as code_type_da_det,
    dossier_autorisation_type_detaille.libelle as libelle_type_da_det,
    dossier_autorisation_type.code as code_type_da,
    dossier_autorisation_type.libelle as libelle_type_da,
    dossier_instruction_type.code as code_type_di,
    dossier_instruction_type.libelle as libelle_type_di,
    dossier.delai as delai_di,
    replace(dossier.terrain_references_cadastrales, '';'', '' ''),

    avis_decision.libelle as decision,

    --
    -- INSTRUCTEUR
    --

    instructeur.nom as instructeur_nom,
    instructeur.telephone as instructeur_telephone,
    division.code as instructeur_division,
    om_utilisateur.email as instructeur_email,

    --
    -- ADRESSE TERRAIN
    --

    dossier.terrain_adresse_voie_numero,
    dossier.complement as adresse_voie,
    dossier.terrain_adresse_lieu_dit,
    dossier.terrain_adresse_bp,
    dossier.terrain_adresse_code_postal,
    dossier.terrain_adresse_localite as terrain_adresse_ville,
    dossier.terrain_adresse_cedex,

    arrondissement.libelle as arrondissement,

    --
    -- PETITIONNAIRE PRINCIPAL
    --

    CASE WHEN petitionnaire_principal.qualite=''particulier''
        THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom))
        ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
    END as petitionnaire_principal_nom,
    petitionnaire_principal.numero as petitionnaire_principal_numero,
    petitionnaire_principal.voie as petitionnaire_principal_voie,
    petitionnaire_principal.complement as petitionnaire_principal_complement,
    petitionnaire_principal.lieu_dit as petitionnaire_principal_lieu_dit,
    petitionnaire_principal.bp as petitionnaire_principal_bp,
    petitionnaire_principal.code_postal as petitionnaire_principal_code_postal,
    petitionnaire_principal.localite as petitionnaire_principal_ville,
    petitionnaire_principal.cedex as petitionnaire_principal_cedex,
    petitionnaire_principal.pays as petitionnaire_principal_pays,

    --
    -- DELEGATAIRE
    --

    CASE
        WHEN delegataire.qualite=''particulier''
        THEN TRIM(CONCAT(delegataire.particulier_nom, '' '', delegataire.particulier_prenom))
        ELSE TRIM(CONCAT(delegataire.personne_morale_raison_sociale, '' '', delegataire.personne_morale_denomination))
    END as delegataire_nom,
    delegataire.numero as delegataire_numero,
    delegataire.voie as delegataire_voie,
    delegataire.complement as delegataire_complement,
    delegataire.lieu_dit as delegataire_lieu_dit,
    delegataire.bp as delegataire_bp,
    delegataire.code_postal as delegataire_code_postal,
    delegataire.localite as delegataire_ville,
    delegataire.cedex as delegataire_cedex,
    delegataire.pays as delegataire_pays,

    --
    -- CORRESPONDANT : destinataire du courrier. Il est le délégataire ou le pétitionnaire principal
    --

    CASE
        WHEN delegataire.qualite IS NULL
        THEN
            CASE WHEN petitionnaire_principal.qualite=''particulier''
                    THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom))
                    ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
            END
        ELSE
            CASE WHEN delegataire.qualite=''particulier''
                THEN TRIM(CONCAT(delegataire.particulier_nom, '' '', delegataire.particulier_prenom))
                ELSE TRIM(CONCAT(delegataire.personne_morale_raison_sociale, '' '', delegataire.personne_morale_denomination))
            END
    END as correspondant_nom,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.numero
        ELSE delegataire.numero
    END as correspondant_numero,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.voie
        ELSE delegataire.voie
    END as correspondant_voie,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.complement
        ELSE delegataire.complement
    END as correspondant_complement,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.lieu_dit
        ELSE delegataire.lieu_dit
    END as correspondant_lieu_dit,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.bp
        ELSE delegataire.bp
    END as correspondant_bp,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.code_postal
        ELSE delegataire.code_postal
    END as correspondant_code_postal,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.localite
        ELSE delegataire.localite
    END as correspondant_ville,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.cedex
        ELSE delegataire.cedex
    END as correspondant_cedex,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.pays
        ELSE delegataire.pays
    END as correspondant_pays,

    --
    -- DATES DI
    --

    to_char(dossier.date_depot, ''DD/MM/YYYY'') as date_depot,
    to_char(dossier.date_decision, ''DD/MM/YYYY'') as date_decision,
    to_char(dossier.date_limite,''DD/MM/YYYY'') as date_limite,
    to_char(dossier.date_achevement,''DD/MM/YYYY'') as date_achevement,
    to_char(dossier.date_conformite,''DD/MM/YYYY'') as date_conformite,
    
    --
    -- SIGNATAIRES
    --
    CONCAT(signataire_arrete.prenom, '' '', signataire_arrete.nom) as signataire_arrete,
    division.chef as division_chef,
    direction.chef as direction_chef,
    
    --
    -- DONNÉES TECHNIQUES
    --
    donnees_techniques.co_projet_desc,
    donnees_techniques.am_projet_desc,
    donnees_techniques.dm_projet_desc,
    donnees_techniques.am_lot_max_nb,
    donnees_techniques.am_lot_max_shon,
    donnees_techniques.su_cstr_shon_tot,
    donnees_techniques.su_demo_shon_tot,
    CONCAT(''Habitation : '', donnees_techniques.su_trsf_shon1, 
    '' - Hébergement hôtelier : '', donnees_techniques.su_trsf_shon2, 
    '' - Bureaux : '', donnees_techniques.su_trsf_shon3, 
    '' - Commerce : '', donnees_techniques.su_trsf_shon4,
    '' - Artisanat : '', donnees_techniques.su_trsf_shon5,
    '' - Industrie : '', donnees_techniques.su_trsf_shon6,
    '' - Exploitation agricole ou forestière : '', donnees_techniques.su_trsf_shon7,
    '' - Entrepôt : '', donnees_techniques.su_trsf_shon8,
    '' - Service public ou d''''intérêt collectif : '', donnees_techniques.su_trsf_shon9),
    donnees_techniques.co_tot_log_nb,
    donnees_techniques.co_statio_place_nb
    
FROM
    &DB_PREFIXEinstruction
LEFT JOIN
    &DB_PREFIXEsignataire_arrete
    ON instruction.signataire_arrete = signataire_arrete.signataire_arrete
LEFT JOIN
    &DB_PREFIXEom_lettretype
    ON instruction.lettretype = om_lettretype.id and om_lettretype.actif IS TRUE
LEFT JOIN
    &DB_PREFIXEdossier
    ON
        instruction.dossier=dossier.dossier
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        instruction.dossier = lien_dossier_demandeur.dossier AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN
    &DB_PREFIXEdemandeur as petitionnaire_principal
    ON
        lien_dossier_demandeur.demandeur = petitionnaire_principal.demandeur
LEFT JOIN
    (
    &DB_PREFIXElien_dossier_demandeur AS lien_dossier_delegataire
        JOIN &DB_PREFIXEdemandeur as delegataire
        ON
            lien_dossier_delegataire.demandeur = delegataire.demandeur AND delegataire.type_demandeur = ''delegataire''
    )
    ON
        instruction.dossier = lien_dossier_delegataire.dossier AND lien_dossier_delegataire.petitionnaire_principal IS FALSE
LEFT JOIN
    &DB_PREFIXEdossier_instruction_type
    ON
        dossier.dossier_instruction_type = dossier_instruction_type.dossier_instruction_type
LEFT JOIN
    &DB_PREFIXEdossier_autorisation
    ON
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type_detaille
    ON
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type
    ON
        dossier_autorisation_type_detaille.dossier_autorisation_type = dossier_autorisation_type.dossier_autorisation_type
LEFT JOIN
    &DB_PREFIXEinstructeur
    ON
        dossier.instructeur = instructeur.instructeur
LEFT JOIN
    &DB_PREFIXEom_utilisateur
    ON
        om_utilisateur.om_utilisateur = instructeur.om_utilisateur
LEFT JOIN
    &DB_PREFIXEdivision
    ON
        instructeur.division = division.division
LEFT JOIN 
    &DB_PREFIXEdirection
    ON division.direction = direction.direction
LEFT JOIN
    &DB_PREFIXEarrondissement
    ON
        dossier.terrain_adresse_code_postal = arrondissement.code_postal
LEFT JOIN
    &DB_PREFIXEavis_decision
    ON
        dossier.avis_decision = avis_decision.avis_decision
LEFT JOIN
    &DB_PREFIXEetat
    ON
        dossier.etat = etat.etat
LEFT JOIN
    &DB_PREFIXEdonnees_techniques
    ON
    dossier.dossier = donnees_techniques.dossier_instruction
WHERE instruction.instruction = &idx' 
WHERE code = 'instruction' ;

-- Rapport d'instruction
UPDATE om_requete SET requete =
'SELECT

 
    rapport_instruction.dossier_instruction, analyse_reglementaire, description_projet, proposition_decision, 
    dossier.dossier as d, etat, pos, servitude, terrain_adresse_voie_numero, dossier.complement as dc, terrain_adresse_lieu_dit, terrain_adresse_localite, terrain_adresse_code_postal, terrain_adresse_bp, terrain_adresse_cedex, terrain_superficie, dossier.delai, replace(dossier.terrain_references_cadastrales, '';'', '' ''),
    particulier_nom, particulier_prenom, personne_morale_denomination, personne_morale_raison_sociale, personne_morale_siret, personne_morale_nom, personne_morale_prenom, numero, voie, demandeur.complement as deco, lieu_dit, localite, code_postal, bp, cedex, instructeur.nom, 
    dossier_autorisation_type_detaille.libelle as dl, 
    civilite.code as cc,
    avis_decision.libelle as avis,
    division.chef as division_chef,
    direction.chef as direction_chef,
    donnees_techniques.co_projet_desc,
    donnees_techniques.am_projet_desc,
    donnees_techniques.dm_projet_desc,
    donnees_techniques.am_lot_max_nb,
    donnees_techniques.am_lot_max_shon,
    donnees_techniques.su_cstr_shon_tot,
    donnees_techniques.su_demo_shon_tot,
    CONCAT(''Habitation : '', donnees_techniques.su_trsf_shon1, 
    '' - Hébergement hôtelier : '', donnees_techniques.su_trsf_shon2, 
    '' - Bureaux : '', donnees_techniques.su_trsf_shon3, 
    '' - Commerce : '', donnees_techniques.su_trsf_shon4,
    '' - Artisanat : '', donnees_techniques.su_trsf_shon5,
    '' - Industrie : '', donnees_techniques.su_trsf_shon6,
    '' - Exploitation agricole ou forestière : '', donnees_techniques.su_trsf_shon7,
    '' - Entrepôt : '', donnees_techniques.su_trsf_shon8,
    '' - Service public ou d''''intérêt collectif : '', donnees_techniques.su_trsf_shon9),
    donnees_techniques.co_tot_log_nb,
    donnees_techniques.co_statio_place_nb


FROM 

    &DB_PREFIXErapport_instruction 
    LEFT JOIN &DB_PREFIXEdossier 
        ON dossier.dossier=rapport_instruction.dossier_instruction 
    LEFT JOIN &DB_PREFIXEavis_decision 
        ON dossier.avis_decision = avis_decision.avis_decision
    LEFT JOIN &DB_PREFIXEdonnees_techniques
        ON dossier.dossier = donnees_techniques.dossier_instruction
    LEFT JOIN &DB_PREFIXEdivision
        ON dossier.division = division.division
    LEFT JOIN &DB_PREFIXEdirection
        ON division.direction = direction.direction
    LEFT JOIN &DB_PREFIXEinstructeur 
        ON instructeur.instructeur=dossier.instructeur 
    LEFT JOIN &DB_PREFIXElien_dossier_demandeur 
        ON lien_dossier_demandeur.dossier=dossier.dossier 
    LEFT JOIN &DB_PREFIXEdemandeur 
        ON demandeur.demandeur=lien_dossier_demandeur.demandeur 
    LEFT JOIN &DB_PREFIXEcivilite 
        ON demandeur.personne_morale_civilite=civilite.civilite OR demandeur.particulier_civilite=civilite.civilite 
    LEFT JOIN &DB_PREFIXEdossier_instruction_type 
        ON dossier.dossier_instruction_type=dossier_instruction_type.dossier_instruction_type 
    LEFT JOIN &DB_PREFIXEdossier_autorisation_type_detaille 
        ON dossier_instruction_type.dossier_autorisation_type_detaille=dossier_autorisation_type_detaille.dossier_autorisation_type_detaille 
    
WHERE 

    rapport_instruction = &idx AND lien_dossier_demandeur.petitionnaire_principal IS TRUE' 
WHERE code = 'rapport_instruction' ;

---
--- Ajoute le document arrêté dans la table document_numerise
---
INSERT INTO document_numerise VALUES
(nextval('document_numerise_seq'), '483cf5c504c9f81a7c7f470c5a209140', 'AZ0130551200001P0',    '20130614ARRT.pdf', '2013-06-14',   9);

---
--- Modifi la table instruction : modifi le champs document_arrete en document_numerise
---
ALTER TABLE instruction RENAME COLUMN document_arrete TO document_numerise;
ALTER TABLE instruction ALTER COLUMN document_numerise TYPE integer USING (trim(document_numerise)::integer);
ALTER TABLE ONLY instruction 
    ADD CONSTRAINT instruction_document_numerise_fkey FOREIGN KEY (document_numerise) REFERENCES document_numerise(document_numerise);

---
--- Met à jour une instruction en ajoutant le document arrêté
---
UPDATE instruction SET document_numerise = 1
WHERE instruction = 9;

---
--- Mise à jour des données d'un cerfa
---
UPDATE cerfa set om_validite_fin = '2013-12-31' WHERE cerfa = 1;

---
--- Met à jour l'instruction 9 en ajoutant les dates obligatoires pour la mise en GED
---
UPDATE instruction SET date_retour_rar = '2013-03-27', date_retour_signature = '2013-03-27', date_retour_controle_legalite = '2013-03-27' WHERE instruction = 9;
