--------------------------------------------------------------------------------
-- Script de mise à jour vers la version v3.14.0-b2
--
-- XXX Ce fichier doit être renommé en v3.14.0-b2.sql au moment de la release
--
-- @package openfoncier
-- @version SVN : $Id$
--------------------------------------------------------------------------------
-- Mise à jour des bordereaux
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, '' \ '',
                                                              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_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_PREFIXEgroupe
    ON
        dossier_autorisation_type.groupe = groupe.groupe
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.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
    evenement.retour IS FALSE
    AND instruction.date_envoi_signature >= ''&date_bordereau_debut''
    AND instruction.date_envoi_signature <= ''&date_bordereau_fin''
    AND LOWER(groupe.code) = ''ads''
    AND (evenement.type <> ''arrete'' OR evenement.type IS NULL)
    AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
ORDER BY
    dossier.dossier'
WHERE id = 'bordereau_courriers_signature_maire';

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, '' \ '',
                                                              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_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_PREFIXEgroupe
    ON
        dossier_autorisation_type.groupe = groupe.groupe
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.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
    evenement.retour IS FALSE
    AND (LOWER(autorite_competente.code) = ''etatmaire''
      OR LOWER(autorite_competente.code) = ''etat'')
    AND instruction.date_envoi_rar >= ''&date_bordereau_debut''
    AND instruction.date_envoi_rar <= ''&date_bordereau_fin''
    AND LOWER(groupe.code) = ''ads''
    AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
ORDER BY
    dossier.dossier'
WHERE id = 'bordereau_avis_maire_prefet';

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, '' \ '',
                                                              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_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_PREFIXEgroupe
    ON
        dossier_autorisation_type.groupe = groupe.groupe
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.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
    evenement.retour IS FALSE
    AND LOWER(autorite_competente.code) = ''com''
    AND instruction.date_envoi_controle_legalite >= ''&date_bordereau_debut''
    AND instruction.date_envoi_controle_legalite <= ''&date_bordereau_fin''
    AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
    AND LOWER(groupe.code) = ''ads''
ORDER BY
    dossier.dossier'
WHERE id = 'bordereau_controle_legalite';

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, '' \ '',
                                                              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_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_PREFIXEgroupe
    ON
        dossier_autorisation_type.groupe = groupe.groupe
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.demandeur
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        civilite.civilite = demandeur.particulier_civilite OR civilite.civilite = demandeur.personne_morale_civilite
WHERE
    evenement.retour IS FALSE
    AND LOWER(evenement.type) = ''arrete''
    AND instruction.date_envoi_signature >= ''&date_bordereau_debut''
    AND instruction.date_envoi_signature <= ''&date_bordereau_fin''
    AND LOWER(groupe.code) = ''ads''
    AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
ORDER BY
    dossier.dossier'
WHERE id = 'bordereau_decisions';