--------------------------------------------------------------------------------
-- Script de mise à jour vers la version v3.10.0-a1
--
-- XXX Ce fichier doit être renommé en v3.10.0-a1.sql au moment de la release
--
-- @package openfoncier
-- @version SVN : $Id: v3.10.0-a1.sql 2432 2013-10-22 13:22:12Z vpihour $
--------------------------------------------------------------------------------

-- Création d'un index sur le champ version de dossier
CREATE INDEX version_key ON dossier(version);

--- Mise à jour du widget pour les dossiers en cours
UPDATE om_widget 
    SET libelle = 'Dossier en cours', lien = 'nouvelle_demande_dossier_encours'
    WHERE lien = 'nouvelle_demande_dossier_existant';

--- Renomme les droits
UPDATE om_droit 
    SET libelle = 'demande_dossier_encours_tab'
    WHERE libelle = 'demande_dossier_existant_tab';

UPDATE om_droit 
    SET libelle = 'demande_dossier_encours_ajouter'
    WHERE libelle = 'demande_dossier_existant_ajouter';

--- Ajout des nouveaux droits

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'demande_autre_dossier_tab', 
(SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'demande_autre_dossier_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR')
);
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'demande_autre_dossier_ajouter', 
(SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'demande_autre_dossier_ajouter' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'demande_autre_dossier_tab', 
(SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET UNIQUE')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'demande_autre_dossier_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET UNIQUE')
);
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'demande_autre_dossier_ajouter', 
(SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET UNIQUE')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'demande_autre_dossier_ajouter' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET UNIQUE')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'demande_autre_dossier_tab', 
(SELECT om_profil FROM om_profil WHERE libelle = 'DIVISIONNAIRE')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'demande_autre_dossier_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'DIVISIONNAIRE')
);
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'demande_autre_dossier_ajouter', 
(SELECT om_profil FROM om_profil WHERE libelle = 'DIVISIONNAIRE')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'demande_autre_dossier_ajouter' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'DIVISIONNAIRE')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'demande_autre_dossier_tab', 
(SELECT om_profil FROM om_profil WHERE libelle = 'CHEF DE SERVICE')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'demande_autre_dossier_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'CHEF DE SERVICE')
);
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'demande_autre_dossier_ajouter', 
(SELECT om_profil FROM om_profil WHERE libelle = 'CHEF DE SERVICE')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'demande_autre_dossier_ajouter' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'CHEF DE SERVICE')
);


UPDATE om_sousetat SET 
om_sql = 'SELECT
    dossier.dossier_libelle as dossier,
    evenement.libelle as evenement,
    CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(civilite.code, '' '', demandeur.particulier_nom, '' '', demandeur.particulier_prenom, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.localite, '' '', demandeur.code_postal, '' '', demandeur.cedex, '' '', demandeur.bp)
         WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(civilite.code, '' '', demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, ''\n'', 
                                                              demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale, '' '', demandeur.personne_morale_siret, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite, '' '', demandeur.cedex, '' '', demandeur.bp)
    END as coordonnees_demandeur,
    CONCAT ( dossier.terrain_adresse_voie_numero, '' '', dossier.terrain_adresse_voie, '' '', dossier.terrain_adresse_lieu_dit, '' '', dossier.terrain_adresse_code_postal, '' '', dossier.terrain_adresse_localite, '' '', dossier.terrain_adresse_cedex, '' '', dossier.terrain_adresse_bp) as coordonnees_terrain
FROM
    &DB_PREFIXEdossier
LEFT JOIN
    &DB_PREFIXEinstruction
    ON
        instruction.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEevenement
    ON
        instruction.evenement = evenement.evenement
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        lien_dossier_demandeur.dossier = dossier.dossier
LEFT JOIn
    &DB_PREFIXEdemandeur
    ON
        demandeur.demandeur = lien_dossier_demandeur
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        civilite.civilite = demandeur.particulier_civilite OR civilite.civilite = demandeur.personne_morale_civilite
LEFT JOIN
    &DB_PREFIXEautorite_competente
    ON
        autorite_competente.autorite_competente = dossier.autorite_competente
WHERE
    (LOWER(evenement.type) != ''arrete'' OR LOWER(evenement.type) IS NULL ) AND
    instruction.date_envoi_signature = ''&date_bordereau''
ORDER BY
    dossier.dossier'
WHERE om_sousetat = 15;

UPDATE om_sousetat SET 
om_sql = 'SELECT
    dossier.dossier_libelle as dossier,
    evenement.libelle as evenement,
    CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(civilite.code, '' '', demandeur.particulier_nom, '' '', demandeur.particulier_prenom, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.localite, '' '', demandeur.code_postal, '' '', demandeur.cedex, '' '', demandeur.bp)
         WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(civilite.code, '' '', demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, ''\n'', 
                                                              demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale, '' '', demandeur.personne_morale_siret, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite, '' '', demandeur.cedex, '' '', demandeur.bp)
    END as coordonnees_demandeur,
    CONCAT ( dossier.terrain_adresse_voie_numero, '' '', dossier.terrain_adresse_voie, '' '', dossier.terrain_adresse_lieu_dit, '' '', dossier.terrain_adresse_code_postal, '' '', dossier.terrain_adresse_localite, '' '', dossier.terrain_adresse_cedex, '' '', dossier.terrain_adresse_bp) as coordonnees_terrain
FROM
    &DB_PREFIXEdossier
LEFT JOIN
    &DB_PREFIXEinstruction
    ON
        instruction.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEevenement
    ON
        instruction.evenement = evenement.evenement
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        lien_dossier_demandeur.dossier = dossier.dossier
LEFT JOIn
    &DB_PREFIXEdemandeur
    ON
        demandeur.demandeur = lien_dossier_demandeur
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        civilite.civilite = demandeur.particulier_civilite OR civilite.civilite = demandeur.personne_morale_civilite
LEFT JOIN
    &DB_PREFIXEautorite_competente
    ON
        autorite_competente.autorite_competente = dossier.autorite_competente
WHERE
    LOWER(autorite_competente.code) = ''etat'' AND
    instruction.date_envoi_controle_legalite = ''&date_bordereau''
ORDER BY
    dossier.dossier'
WHERE om_sousetat = 13;

UPDATE om_sousetat SET 
om_sql = 'SELECT
    dossier.dossier_libelle as dossier,
    evenement.libelle as evenement,
    CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(civilite.code, '' '', demandeur.particulier_nom, '' '', demandeur.particulier_prenom, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.localite, '' '', demandeur.code_postal, '' '', demandeur.cedex, '' '', demandeur.bp)
         WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(civilite.code, '' '', demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, ''\n'', 
                                                              demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale, '' '', demandeur.personne_morale_siret, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite, '' '', demandeur.cedex, '' '', demandeur.bp)
    END as coordonnees_demandeur,
    CONCAT ( dossier.terrain_adresse_voie_numero, '' '', dossier.terrain_adresse_voie, '' '', dossier.terrain_adresse_lieu_dit, '' '', dossier.terrain_adresse_code_postal, '' '', dossier.terrain_adresse_localite, '' '', dossier.terrain_adresse_cedex, '' '', dossier.terrain_adresse_bp) as coordonnees_terrain
FROM
    &DB_PREFIXEdossier
LEFT JOIN
    &DB_PREFIXEinstruction
    ON
        instruction.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEevenement
    ON
        instruction.evenement = evenement.evenement
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        lien_dossier_demandeur.dossier = dossier.dossier
LEFT JOIn
    &DB_PREFIXEdemandeur
    ON
        demandeur.demandeur = lien_dossier_demandeur
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        civilite.civilite = demandeur.particulier_civilite OR civilite.civilite = demandeur.personne_morale_civilite
LEFT JOIN
    &DB_PREFIXEautorite_competente
    ON
        autorite_competente.autorite_competente = dossier.autorite_competente
WHERE
    LOWER(autorite_competente.code) = ''etat'' AND
    instruction.date_envoi_controle_legalite = ''&date_bordereau''
ORDER BY
    dossier.dossier'
WHERE om_sousetat = 14;

UPDATE om_sousetat SET 
om_sql = 'SELECT
    dossier.dossier_libelle as dossier,
    evenement.libelle as evenement,
    CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(civilite.code, '' '', demandeur.particulier_nom, '' '', demandeur.particulier_prenom, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.localite, '' '', demandeur.code_postal, '' '', demandeur.cedex, '' '', demandeur.bp)
         WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(civilite.code, '' '', demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, ''\n'', 
                                                              demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale, '' '', demandeur.personne_morale_siret, '' '', demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite, '' '', demandeur.cedex, '' '', demandeur.bp)
    END as coordonnees_demandeur,
    CONCAT ( dossier.terrain_adresse_voie_numero, '' '', dossier.terrain_adresse_voie, '' '', dossier.terrain_adresse_lieu_dit, '' '', dossier.terrain_adresse_code_postal, '' '', dossier.terrain_adresse_localite, '' '', dossier.terrain_adresse_cedex, '' '', dossier.terrain_adresse_bp) as coordonnees_terrain
FROM
    &DB_PREFIXEdossier
LEFT JOIN
    &DB_PREFIXEinstruction
    ON
        instruction.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEevenement
    ON
        instruction.evenement = evenement.evenement
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        lien_dossier_demandeur.dossier = dossier.dossier
LEFT JOIn
    &DB_PREFIXEdemandeur
    ON
        demandeur.demandeur = lien_dossier_demandeur
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        civilite.civilite = demandeur.particulier_civilite OR civilite.civilite = demandeur.personne_morale_civilite
WHERE
    LOWER(evenement.type) = ''arrete'' AND
    instruction.date_envoi_signature = ''&date_bordereau'' AND
    lien_dossier_demandeur.petitionnaire_principal IS TRUE
ORDER BY
    dossier.dossier'
WHERE om_sousetat = 12;

DROP FUNCTION fn_fixsequencesdatd();
