#
# Mise à jour 8.4.0 - 27/09/17
#
# Sprint 4 -- ajout des rules pour afficher les categories prescripteurs (PR-42)

CALL proc_AddRuleByGroupAllLocal('MENU_GESTION_PRESCRIPTEUR_CATEGORIE','Acces a la partie Prescripteur Categorie', 'Direction', 1);


CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CATEGORIE_CONSULTATION','Acces a la partie consultation Prescripteur Categorie', 'Direction', 1);

CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CATEGORIE_AJOUTER','Acces a la partie ajout Prescripteur Categorie', 'Direction', 1);

CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CATEGORIE_SUPPRESSION','Acces a la partie suppression Prescripteur Categorie', 'Direction', 1);

CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CATEGORIE_MODIFICATION','Acces a la partie modification Prescripteur Categorie', 'Direction', 1);

#
# Mise à jour 8.3.0 - 12/09/17
#
# Token dauthentification pour la passerelle
INSERT INTO financial_parametre (id_local, variable, valeur, type)
VALUES (null, 'API_PREMISTA_AUTH_TOKEN', 'tJ4wOc7N4LXsDVaaFprHa5LVNw7bKf40', 'String');

#
# Mise à jour 6.18.0 - 21/09/17
#
INSERT INTO financial_mail (libelle, description, actif, type_mail) VALUES
( 'DEPOT_VALIDE', 'DEPOT_VALIDE', 'Y', 'depot_banque');

#
# Mise à jour 6.17.0 - 14/09/17
#

ALTER TABLE financial_mail_critere CHANGE type_critere type_critere ENUM('mail_critere_utilisateur','mail_critere_dossier_statut','mail_critere_depotbanque_suivi','mail_critere_local','mail_critere_prescripteur','mail_critere_contactdossier_type_personne','mail_critere_dossier_scoring','mail_critere_rendezvousnotaire_etat','mail_critere_assurance_suivi','mail_critere_rc_prescripteur','mail_critere_support','mail_critere_banque','mail_critere_dossier_commercial_orientation','mail_critere_dossier_type')  NOT NULL;


#
# Mise à jour 6.16.0 - 31/08/17
#

UPDATE financial_banque SET libelle = 'BNP PARIBAS PERSONAL FINANCE' WHERE id_banque = 6;
ALTER TABLE `financial_affiliation` ADD `mutualisation` VARCHAR(255) NULL DEFAULT NULL AFTER `formulaire`;


INSERT INTO financial_parametre (id_local, variable, valeur, type) VALUES(NULL, 'PARAM_COMPAGNIE_GARANTIE_BUDGET', '66', 'Int');
/*
   Issue #4550
   RENAME TABLE changes internally generated foreign key constraint names
 */
RENAME TABLE financial_filleuil TO financial_filleul;
RENAME TABLE financial_parrain_filleuil TO financial_parrain_filleul;

#
# Mise à jour 6.8.0 - 15/06/17
#

UPDATE financial_produit_banque
SET reste_a_vivre1 = 710,
reste_a_vivre2 = 710,
reste_a_vivre3 = 1065,
reste_a_vivre4 = 1420,
reste_a_vivre5 = 1775,
reste_a_vivre_supp = 355
WHERE id_produit_banque = 00011;

UPDATE financial_produit_banque
SET reste_a_vivre1 = 800,
reste_a_vivre2 = 800,
reste_a_vivre3 = 1200,
reste_a_vivre4 = 1600,
reste_a_vivre5 = 2000,
reste_a_vivre_supp = 400
WHERE id_produit_banque = 00093;

UPDATE financial_produit_banque
SET reste_a_vivre1 = 710,
reste_a_vivre2 = 710,
reste_a_vivre3 = 1065,
reste_a_vivre4 = 1420,
reste_a_vivre5 = 1775,
reste_a_vivre_supp = 355
WHERE id_produit_banque = 00164;

UPDATE financial_produit_banque
SET reste_a_vivre1 = 710,
reste_a_vivre2 = 710,
reste_a_vivre3 = 1065,
reste_a_vivre4 = 1420,
reste_a_vivre5 = 1775,
reste_a_vivre_supp = 355
WHERE id_produit_banque = 00248;

UPDATE financial_produit_banque
SET reste_a_vivre1 = 710,
reste_a_vivre2 = 710,
reste_a_vivre3 = 1065,
reste_a_vivre4 = 1420,
reste_a_vivre5 = 1775,
reste_a_vivre_supp = 355,
fcc = 'N',
endettement_apres_ac ='45'
WHERE id_produit_banque = 00238;

UPDATE financial_produit_banque
SET reste_a_vivre1 = 710,
reste_a_vivre2 = 710,
reste_a_vivre3 = 1065,
reste_a_vivre4 = 1420,
reste_a_vivre5 = 1775,
reste_a_vivre_supp = 355,
fcc = 'N',
endettement_apres_ac ='45'
WHERE id_produit_banque = 00220;

UPDATE financial_produit_banque
SET reste_a_vivre1 = 710,
reste_a_vivre2 = 710,
reste_a_vivre3 = 1065,
reste_a_vivre4 = 1420,
reste_a_vivre5 = 1775,
reste_a_vivre_supp = 355,
fcc = 'N',
endettement_apres_ac ='45'
WHERE id_produit_banque = 00241;

UPDATE financial_produit_banque
SET reste_a_vivre1 = 710,
reste_a_vivre2 = 710,
reste_a_vivre3 = 1065,
reste_a_vivre4 = 1420,
reste_a_vivre5 = 1775,
reste_a_vivre_supp = 355,
fcc = 'N',
endettement_apres_ac ='45'
WHERE id_produit_banque = 00242;

UPDATE financial_produit_banque
SET reste_a_vivre1 = 710,
reste_a_vivre2 = 710,
reste_a_vivre3 = 1065,
reste_a_vivre4 = 1420,
reste_a_vivre5 = 1775,
reste_a_vivre_supp = 355,
fcc = 'N',
endettement_apres_ac ='45'
WHERE id_produit_banque = 00239;

UPDATE financial_produit_banque
SET reste_a_vivre1 = 710,
reste_a_vivre2 = 710,
reste_a_vivre3 = 1065,
reste_a_vivre4 = 1420,
reste_a_vivre5 = 1775,
reste_a_vivre_supp = 355,
fcc = 'N',
endettement_apres_ac ='45'
WHERE id_produit_banque = 00221;

UPDATE financial_produit_banque
SET reste_a_vivre1 = 710,
reste_a_vivre2 = 710,
reste_a_vivre3 = 1065,
reste_a_vivre4 = 1420,
reste_a_vivre5 = 1775,
reste_a_vivre_supp = 355,
fcc = 'N',
endettement_apres_ac ='45'
WHERE id_produit_banque = 00243;

UPDATE financial_produit_banque
SET reste_a_vivre1 = 710,
reste_a_vivre2 = 710,
reste_a_vivre3 = 1065,
reste_a_vivre4 = 1420,
reste_a_vivre5 = 1775,
reste_a_vivre_supp = 355,
fcc = 'N',
endettement_apres_ac ='45'
WHERE id_produit_banque = 00240;

UPDATE financial_produit_banque
SET reste_a_vivre1 = 710,
reste_a_vivre2 = 710,
reste_a_vivre3 = 1065,
reste_a_vivre4 = 1420,
reste_a_vivre5 = 1775,
reste_a_vivre_supp = 355,
fcc = 'N',
endettement_apres_ac ='45'
WHERE id_produit_banque = 00222;



/****************************************************/
/*   ETUDE_CAN_REFUSER_DOSSIER_ENCOURS_COMMERCIAL   */
/****************************************************/
INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENCOURS_COMMERCIAL') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_3';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENCOURS_COMMERCIAL') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_4';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENCOURS_COMMERCIAL') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'direction';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENCOURS_COMMERCIAL') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_technique_ph';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENCOURS_COMMERCIAL') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_tutel';

/******************************************/
/*   ETUDE_CAN_REFUSER_DOSSIER_FIN_AZUR   */
/******************************************/
INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_FIN_AZUR') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_3';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_FIN_AZUR') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_4';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_FIN_AZUR') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'direction';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_FIN_AZUR') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_technique_ph';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_FIN_AZUR') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_tutel';
/*******************************************/
/*   ETUDE_CAN_REFUSER_DOSSIER_ENVOI_BFC   */
/*******************************************/

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENVOI_BFC') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_3';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENVOI_BFC') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_4';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENVOI_BFC') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'direction';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENVOI_BFC') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_technique_ph';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENVOI_BFC') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_tutel';
/*******************************************/
/*   ETUDE_CAN_REFUSER_DOSSIER_POTENTIEL   */
/*******************************************/

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_POTENTIEL') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_3';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_POTENTIEL') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_4';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_POTENTIEL') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'direction';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_POTENTIEL') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_technique_ph';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_POTENTIEL') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_tutel';
/******************************************************/
/*   ETUDE_CAN_REFUSER_DOSSIER_ENCOURS_DEPOT_BANQUE   */
/******************************************************/

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENCOURS_DEPOT_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_3';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENCOURS_DEPOT_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_4';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENCOURS_DEPOT_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'direction';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENCOURS_DEPOT_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_technique_ph';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ENCOURS_DEPOT_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_tutel';
/***********************************************/
/*   ETUDE_CAN_REFUSER_DOSSIER_ACCORD_BANQUE   */
/***********************************************/

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ACCORD_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_3';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ACCORD_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_4';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ACCORD_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'direction';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ACCORD_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_technique_ph';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ACCORD_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_tutel';
/********************************************************/
/*   ETUDE_CAN_REFUSER_DOSSIER_EN_CONTESTATION_BANQUE   */
/********************************************************/

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_EN_CONTESTATION_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_3';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_EN_CONTESTATION_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_4';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_EN_CONTESTATION_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'direction';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_EN_CONTESTATION_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_technique_ph';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_EN_CONTESTATION_BANQUE') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_tutel';
/**********************************************************/
/*   ETUDE_CAN_REFUSER_DOSSIER_ATTENTE_SIGNATURE_CLIENT   */
/**********************************************************/

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ATTENTE_SIGNATURE_CLIENT') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_3';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ATTENTE_SIGNATURE_CLIENT') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_4';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ATTENTE_SIGNATURE_CLIENT') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'direction';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ATTENTE_SIGNATURE_CLIENT') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_technique_ph';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_ATTENTE_SIGNATURE_CLIENT') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_tutel';
/**********************************************************/
/*   ETUDE_CAN_REFUSER_DOSSIER_RE_EDITION_OFFRE_DE_PRET   */
/**********************************************************/

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_RE_EDITION_OFFRE_DE_PRET') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_3';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_RE_EDITION_OFFRE_DE_PRET') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_4';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_RE_EDITION_OFFRE_DE_PRET') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'direction';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_RE_EDITION_OFFRE_DE_PRET') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_technique_ph';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_RE_EDITION_OFFRE_DE_PRET') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_tutel';
/***************************************/
/*   ETUDE_CAN_REFUSER_DOSSIER_FRIGO   */
/***************************************/

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_FRIGO') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_3';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_FRIGO') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'commercial_4';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_FRIGO') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'direction';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_FRIGO') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_technique_ph';

INSERT INTO financial_rule_utilisateur(id_rule, id_utilisateur, more_less) 
SELECT rule.id_rule,u.id_utilisateur,'Y' from
(SELECT id_rule,id_local FROM financial_rule where rule='ETUDE_CAN_REFUSER_DOSSIER_FRIGO') AS rule 
inner join financial_utilisateur as u on rule.id_local=u.id_local_direction
WHERE type_utilisateur = 'adjoint_tutel';

SET @GROUP = 'Direction';
SET @FORCED = 1;
CALL proc_AddRuleByGroupAllLocal('VISIBILITE_ALL','/',@GROUP,@FORCED);

SET @GROUP = 'Directeur Commercial';
SET @FORCED = 1;
CALL proc_AddRuleByGroupAllLocal('VISIBILITE_ALL','/',@GROUP,@FORCED);

SET @GROUP = 'Responsable Clientèle';
SET @FORCED = 1;
CALL proc_AddRuleByGroupAllLocal('VISIBILITE_ALL','/',@GROUP,@FORCED);

SET @GROUP = 'Conseiller Clientèle privé';
SET @FORCED = 1;
CALL proc_AddRuleByGroupAllLocal('VISIBILITE_ALL','/',@GROUP,@FORCED);


SET @GROUP = 'Adjoint tutel';
SET @FORCED = 1;
CALL proc_AddRuleByGroupAllLocal('VISIBILITE_ALL','/',@GROUP,@FORCED);

SET @GROUP = 'Adjoint Technique';
SET @FORCED = 1;
CALL proc_AddRuleByGroupAllLocal('VISIBILITE_ALL','/',@GROUP,@FORCED);




SET @GROUP = 'Direction';
SET @FORCED = 1;

CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_AJOUTER','Ajout prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_MODIFIER','Modification prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_MODIFIER_ADMIN','Modification prescripteur (admin)',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_SUPPRIMER','Suppression prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_AJOUTER','Ajout contact prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_MODIFIER','Modification contact prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_SUPPRIMER','Suppression contact prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_MODIFIER_ADMIN','Modification contact prescripteur (admin)',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_VISITE_AJOUTER','Modification visite prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_VISITE_MODIFIER','Modification visite prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_VISITE_SUPPRIMER','Suppression visite prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_VISITE_SUPPRIMER_ADMIN','Suppression visite prescripteur (admin)',@GROUP,@FORCED);


SET @GROUP = 'Direction Commercial';
SET @FORCED = 1;

CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_AJOUTER','Ajout prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_MODIFIER','Modification prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_SUPPRIMER','Suppression prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_AJOUTER','Ajout contact prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_MODIFIER','Modification contact prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_SUPPRIMER','Suppression contact prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_VISITE_AJOUTER','Modification visite prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_VISITE_MODIFIER','Modification visite prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_VISITE_SUPPRIMER','Suppression visite prescripteur',@GROUP,@FORCED);


CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_VISITE_CONSULTER','Consultation visite prescripteur',@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal('GESTION_PRESCRIPTEUR_CONTACT_CONSULTER','Consultation des contacts prescripteur',@GROUP,@FORCED);
DELETE FROM `financial_parametre` WHERE `variable` ="SOCIETE_MIOB";
DELETE FROM `financial_parametre_agence` WHERE `variable` ="SOCIETE_MIOB";
DELETE FROM `financial_parametre_utilisateur` WHERE `variable` ="SOCIETE_MIOB";

DELETE FROM `financial_parametre` WHERE `variable` ="SOCIETE_MNE";
DELETE FROM `financial_parametre_agence` WHERE `variable` ="SOCIETE_MNE";
DELETE FROM `financial_parametre_utilisateur` WHERE `variable` ="SOCIETE_MNE";





CALL proc_AddRuleByGroupAllLocal('DIRECTEUR_COMMERCIALE','/','Direction Commercial',1);


CREATE TABLE `financial_rule_local` (
 `id_rule` smallint(5) unsigned zerofill DEFAULT NULL,
 `id_local` SMALLINT UNSIGNED ZEROFILL NULL,
 KEY `id_rule` (`id_rule`),
 KEY `id_local` (`id_local`),
 FOREIGN KEY (id_rule) REFERENCES financial_rule(id_rule),
 FOREIGN KEY (id_local) REFERENCES financial_local(id_local)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


UPDATE `financial_local_info_societe` SET `id_iob` = NULL;


SET @GROUP = 'Adjoint Technique';
SET @FORCED = 1;
  
CALL proc_AddRuleByGroupAllLocal("ACCES_DOCUMENT_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DOCUMENT_MODIFIER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DOCUMENT_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_ASSURANCE_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_ASSURANCE_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_ASSURANCE_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_RENONCIATION_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_RENONCIATION_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_RENONCIATION_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_PRECONISATION_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_PRECONISATION_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_PRECONISATION_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_RAPPEL_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_RAPPEL_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_RAPPEL_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOUILLEMENT_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOUILLEMENT_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOUILLEMENT_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTNOTAIRE_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTNOTAIRE_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTNOTAIRE_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTBANQUE_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTBANQUE_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTBANQUE_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_DEMANDEANNULATION_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DEMANDEANNULATION_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_DEMANDEANNULATION_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_CONTACTDOSSIER_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_CONTACTDOSSIER_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_CONTACTDOSSIER_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_CF100_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_CF100_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_CF100_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_CF101_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_CF101_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_CF101_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_CF102_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_CF102_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_CF102_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_RENDEZVOUS_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_RENDEZVOUS_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_RENDEZVOUS_SUPPRIMER","/",@GROUP,@FORCED);
  
  
SET @GROUP = 'Direction';
  
CALL proc_AddRuleByGroupAllLocal("ACCES_DOCUMENT_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DOCUMENT_MODIFIER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DOCUMENT_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_ASSURANCE_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_ASSURANCE_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_ASSURANCE_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_RENONCIATION_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_RENONCIATION_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_RENONCIATION_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_PRECONISATION_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_PRECONISATION_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_PRECONISATION_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_RAPPEL_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_RAPPEL_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_RAPPEL_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOUILLEMENT_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOUILLEMENT_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOUILLEMENT_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTNOTAIRE_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTNOTAIRE_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTNOTAIRE_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTBANQUE_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTBANQUE_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTBANQUE_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_DEMANDEANNULATION_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DEMANDEANNULATION_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_DEMANDEANNULATION_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_CONTACTDOSSIER_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_CONTACTDOSSIER_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_CONTACTDOSSIER_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_CF100_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_CF100_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_CF100_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_CF101_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_CF101_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_CF101_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_CF102_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_CF102_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_CF102_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_RENDEZVOUS_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_RENDEZVOUS_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_RENDEZVOUS_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_VISA_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_VISA_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_VISA_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_PRODUIT_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_PRODUIT_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_PRODUIT_SUPPRIMER","/",@GROUP,@FORCED);
  
  
SET @GROUP = 'Direction Commercial';
  
CALL proc_AddRuleByGroupAllLocal("ACCES_DOCUMENT_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DOCUMENT_MODIFIER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DOCUMENT_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_ASSURANCE_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_ASSURANCE_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_ASSURANCE_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_RENONCIATION_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_RENONCIATION_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_RENONCIATION_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_PRECONISATION_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_PRECONISATION_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_PRECONISATION_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_RAPPEL_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_RAPPEL_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_RAPPEL_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOUILLEMENT_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOUILLEMENT_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOUILLEMENT_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTNOTAIRE_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTNOTAIRE_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTNOTAIRE_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTBANQUE_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTBANQUE_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_DEPOTBANQUE_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_DEMANDEANNULATION_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_DEMANDEANNULATION_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_DEMANDEANNULATION_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_CONTACTDOSSIER_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_CONTACTDOSSIER_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_CONTACTDOSSIER_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_CF100_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_CF100_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_CF100_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_CF101_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_CF101_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_CF101_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_CF102_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_CF102_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_CF102_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_RENDEZVOUS_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_RENDEZVOUS_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_RENDEZVOUS_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_VISA_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_VISA_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_VISA_SUPPRIMER","/",@GROUP,@FORCED);

CALL proc_AddRuleByGroupAllLocal("ACCES_PRODUIT_AJOUTER","/",@GROUP,@FORCED);
CALL proc_AddRuleByGroupAllLocal("ACCES_PRODUIT_MODIFIER","/",@GROUP,@FORCED);
#CALL proc_AddRuleByGroupAllLocal("ACCES_PRODUIT_SUPPRIMER","/",@GROUP,@FORCED);
  
INSERT INTO `financial_parametre` (`id_local`, `variable`, `valeur`, `type`) VALUES (NULL, 'TEMPLATE_POSITION_BASSE_MANDAT', '230', 'Int');

UPDATE `financial_parametre` SET `valeur`= 10 WHERE `variable`='TEMPLATE_POSITION_HAUTE_MANDAT';

DROP FUNCTION IF EXISTS function_insert_update_rule;

DELIMITER $$

##
#
# Procedure : insert une rule
#
##
CREATE FUNCTION function_insert_update_rule(var_nom_rule VARCHAR(255), var_description_rule VARCHAR(255),var_id_local INT,var_libelle_group VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN

SET @ID_RULE = (SELECT id_rule FROM financial_rule WHERE rule = var_nom_rule AND id_local = var_id_local);
IF @ID_RULE IS NULL THEN
	INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_rule_parent`, `id_local`) VALUES ( var_nom_rule, var_description_rule, 'Y', NULL, var_id_local);
	SET @ID_RULE = ( SELECT LAST_INSERT_ID());
END IF;

SET @ID_GROUP = (SELECT id_group FROM financial_group WHERE libelle = var_libelle_group AND id_local = var_id_local);
IF @ID_GROUP IS NULL THEN
	INSERT INTO financial_group(libelle,id_local) VALUES (var_libelle_group,var_id_local);
	SET @ID_GROUP = ( SELECT LAST_INSERT_ID());
END IF;

IF @ID_GROUP > 0 AND @ID_RULE > 0  THEN
	SET @ID_RULE_GROUP = (SELECT count(*) FROM financial_rule_group WHERE id_group = @ID_GROUP AND id_rule = @ID_RULE );

	IF  @ID_RULE_GROUP = 0 THEN
		INSERT INTO financial_rule_group(id_group,id_rule) VALUES (@ID_GROUP,@ID_RULE);
	END IF;
END IF;

return CONCAT("id rule : ",@ID_RULE," id_group :",@ID_GROUP);

END$$

DELIMITER ;

DROP PROCEDURE IF EXISTS procedure_rc_local_direction;

DELIMITER $$

CREATE PROCEDURE procedure_rc_local_direction(var_nom_rule VARCHAR(255), var_description_rule VARCHAR(255),var_libelle_group VARCHAR(255))
BEGIN
  DECLARE bDone INT;
  DECLARE id_local_temp INT;  
  DECLARE curs CURSOR FOR  SELECT id_local FROM financial_local WHERE id_local = id_local_direction;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
  OPEN curs;

  SET bDone = 0;
 
 fct_insert_group: LOOP
	FETCH curs INTO id_local_temp;
	IF bDone = 1 THEN
	LEAVE fct_insert_group;
	END IF;

SET @ID_RULE = function_insert_update_rule(var_nom_rule,var_description_rule,id_local_temp,var_libelle_group);
	
	
END LOOP fct_insert_group;
CLOSE curs;
END$$

DELIMITER ;

CALL procedure_rc_local_direction('AZUR_CAN_ADD_BANQUE_CLIENT','/','Responsable de Client�le');


DELETE FROM `financial_parametre` WHERE variable='TELEPHONE_COUT_NUMERO_INDIGO';
DELETE FROM `financial_parametre` WHERE variable='TELEPHONE_COUT_NUMERO_VERT';

UPDATE `financial_parametre` SET `valeur`= 30 WHERE `variable`='TEMPLATE_POSITION_HAUTE' ;


CALL procedure_update_rule_group('ACCES_LOCAL_ZONE_CHALANDISE','Admin');
CALL procedure_update_rule_group('ACCES_LOCAL_SUPPORT_BFI','Admin');

SET @libelle_group = 'RC';
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_ENVOI_CONTRAT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_DEMANDE_COMPLEMENT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_COMPLETUDE",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_RETOUR_CONTRAT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_RENONCIATION",@libelle_group);
SET @libelle_group = 'Adjoint Technique';
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_ENVOI_CONTRAT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_DEMANDE_COMPLEMENT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_COMPLETUDE",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_RETOUR_CONTRAT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_RENONCIATION",@libelle_group);
SET @libelle_group = 'Direction';
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_ENVOI_CONTRAT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_DEMANDE_COMPLEMENT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_COMPLETUDE",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_RETOUR_CONTRAT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_RENONCIATION",@libelle_group);
SET @libelle_group = 'Adjoint Tutel';
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_ENVOI_CONTRAT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_DEMANDE_COMPLEMENT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_COMPLETUDE",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_RETOUR_CONTRAT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_RENONCIATION",@libelle_group);
SET @libelle_group = 'Directeur Commercial';
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_ENVOI_CONTRAT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_DEMANDE_COMPLEMENT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_COMPLETUDE",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_RETOUR_CONTRAT",@libelle_group);
CALL procedure_update_rule_group("DOCUMENT_ASSURANCE_LETTRE_RENONCIATION",@libelle_group);

REPLACE INTO financial_parametre(id_local,variable,valeur,type)
SELECT id_local,'DOCUMENT_COM_LETTRE_ASSURANCE_RENONCIATION','true','Boolean' FROM financial_local WHERE id_local = id_local_direction;
REPLACE INTO financial_parametre(id_local,variable,valeur,type)
SELECT id_local,'DOCUMENT_COM_LETTRE_ENVOI_CONTRAT','true','Boolean' FROM financial_local WHERE id_local = id_local_direction;
REPLACE INTO financial_parametre(id_local,variable,valeur,type)
SELECT id_local,'DOCUMENT_COM_LETTRE_DEMANDE_COMPLEMENT','true','Boolean' FROM financial_local WHERE id_local = id_local_direction;
REPLACE INTO financial_parametre(id_local,variable,valeur,type)
SELECT id_local,'DOCUMENT_COM_LETTRE_COMPLETUDE','true','Boolean' FROM financial_local WHERE id_local = id_local_direction;
REPLACE INTO financial_parametre(id_local,variable,valeur,type)
SELECT id_local,'DOCUMENT_COM_LETTRE_RETOUR_CONTRAT','true','Boolean' FROM financial_local WHERE id_local = id_local_direction;


INSERT INTO `financial_mail` (`id_mail`, `libelle`, `description`, `actif`) VALUES (NULL, 'AJOUT_RAPPEL', '/', 'Y');


CREATE TABLE financial_commissionnement(
id_commissionnement MEDIUMINT UNSIGNED ZEROFILL NOT NULL,
date_reception_hib DATE NULL,
date_facturation_surcom DATE NULL,
date_facturation_com_assurance_emprunteur DATE NULL,
date_facturation_com_assurance_coemprunteur DATE NULL,
date_saisie_bordereau DATE NULL,
montant_hib_percu DOUBLE UNSIGNED NULL,
commission_banque DOUBLE UNSIGNED NULL,
surcommission_banque DOUBLE UNSIGNED NULL,
commission_assurance_emprunteur DOUBLE UNSIGNED NULL,
commission_assurance_coemprunteur DOUBLE UNSIGNED NULL,
montant_retrocession_hib DOUBLE UNSIGNED NULL,
montant_retrocession_banque DOUBLE UNSIGNED NULL,
montant_retrocession_assurance_emprunteur DOUBLE UNSIGNED NULL,
montant_retrocession_assurance_coemprunteur DOUBLE UNSIGNED NULL,
id_remarque MEDIUMINT UNSIGNED ZEROFILL NULL,
PRIMARY KEY (id_commissionnement),
FOREIGN KEY (id_commissionnement) REFERENCES financial_dossier(id_dossier) ON DELETE CASCADE,
FOREIGN KEY (id_remarque) REFERENCES financial_remarque(id_remarque) ON DELETE SET NULL
)ENGINE=InnoDB AUTO_INCREMENT=000;

CREATE TABLE financial_commissionnement_facture (
  id_facture MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  id_commissionnement MEDIUMINT(8) unsigned zerofill NOT NULL,
  date_facture DATE DEFAULT NULL,
  montant double UNSIGNED NULL,
  montant_assurance double UNSIGNED NULL,
  numero_facture MEDIUMINT(8) UNSIGNED ZEROFILL DEFAULT NULL,
  id_remarque MEDIUMINT UNSIGNED ZEROFILL NULL,
  PRIMARY KEY (id_facture),
  FOREIGN KEY (id_commissionnement) REFERENCES financial_dossier(id_dossier) ON DELETE CASCADE,
  FOREIGN KEY (id_remarque) REFERENCES financial_remarque(id_remarque) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=000 ;


SET @libelle_group = 'RC';
CALL procedure_update_rule_group("ACCES_SUPPORT_PRESCRIPTION",@libelle_group);
SET @libelle_group = 'Adjoint Technique';
CALL procedure_update_rule_group("ACCES_SUPPORT_PRESCRIPTION",@libelle_group);
SET @libelle_group = 'Direction';
CALL procedure_update_rule_group("ACCES_SUPPORT_PRESCRIPTION",@libelle_group);
SET @libelle_group = 'Adjoint Tutel';
CALL procedure_update_rule_group("ACCES_SUPPORT_PRESCRIPTION",@libelle_group);
SET @libelle_group = 'Directeur Commercial';
CALL procedure_update_rule_group("ACCES_SUPPORT_PRESCRIPTION",@libelle_group);



UPDATE financial_local_info_societe SET id_iob = 1 WHERE id_iob IS NULL AND id_local<>1 ;


UPDATE financial_rule SET description='type utilisateur' WHERE rule = 'ADJOINT_TECHNIQUE'; 
UPDATE financial_rule SET description='type utilisateur' WHERE rule = 'ADMIN'; 
UPDATE financial_rule SET description='type utilisateur' WHERE rule = 'CHARGE_CLIENTELE'; 
UPDATE financial_rule SET description='type utilisateur' WHERE rule = 'DIRECTION'; 
UPDATE financial_rule SET description='type utilisateur' WHERE rule = 'INTERNET_USER'; 
UPDATE financial_rule SET description='type utilisateur' WHERE rule = 'DIRECTEUR_COMMERCIAL'; 
UPDATE financial_rule SET description='type utilisateur' WHERE rule = 'RC'; 
UPDATE financial_rule SET description='type utilisateur' WHERE rule = 'RC_PARTENAIRE'; 
UPDATE financial_rule SET description='type utilisateur' WHERE rule = 'RC_LOCAL'; 

UPDATE financial_parametre SET variable ='COMMISSIONNEMENT_TAUX_COMMISSION' WHERE variable = 'COMMISSIONNEMENT_TAUX_RETROCESSION';

INSERT into financial_parametre SET variable = "PREFIXE_COMMISSIONNEMENT_FACTURE", valeur = "CL-F-", type ="String";

CREATE TABLE IF NOT EXISTS `financial_commissionnement_facture` (
id_facture MEDIUMINT UNSIGNED ZEROFILL NOT NULL auto_increment,
id_commissionnement MEDIUMINT UNSIGNED ZEROFILL NOT NULL,
date_facture DATE NULL,
montant DOUBLE UNSIGNED NOT NULL,
numero_facture MEDIUMINT UNSIGNED ZEROFILL NULL,
PRIMARY KEY (`id_facture`),
FOREIGN KEY (id_commissionnement) REFERENCES financial_commissionnement(id_commissionnement) ON DELETE CASCADE)
ENGINE = InnoDB;

UPDATE financial_banque
SET criteres_commissionnement = "P1;P2;P3;L1;L2;Prêt sans garantie"
WHERE id_banque = 4;

UPDATE financial_banque
SET criteres_commissionnement = "Choix A;Choix B;Choix C"
WHERE id_banque = 3;


UPDATE financial_parametre SET variable ='COMMISSIONNEMENT_TAUX_COMMISSION' WHERE variable = 'COMMISSIONNEMENT_TAUX_RETROCESSION';


DROP PROCEDURE IF EXISTS procedure_rule_lier_parent;

DELIMITER $$

##
#
# Procedure : lie une rule à son superieur
#
##
CREATE PROCEDURE procedure_rule_lier_parent(rule_parent VARCHAR(255),rule_enfant VARCHAR(255))
BEGIN
  DECLARE bDone INT;
  DECLARE id_local_temp INT;  
  DECLARE curs CURSOR FOR  SELECT id_local FROM financial_local WHERE id_local = id_local_direction;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
  OPEN curs;

  SET bDone = 0;
 
 fct_insert_group: LOOP
	FETCH curs INTO id_local_temp;
	IF bDone = 1 THEN
	LEAVE fct_insert_group;
	END IF;

SET @id_rule_enfant =(SELECT id_rule FROM financial_rule WHERE rule=rule_enfant AND id_local = id_local_temp);
SET @id_rule_parent =(SELECT id_rule FROM financial_rule WHERE rule=rule_parent AND id_local = id_local_temp);

UPDATE financial_rule SET id_rule_parent=@id_rule_parent WHERE id_rule = @id_rule_enfant AND id_local = id_local_temp;

END LOOP fct_insert_group;
CLOSE curs;
END$$

DELIMITER ;

DROP PROCEDURE IF EXISTS procedure_update_rule_group;

DELIMITER $$

##
#
# Procedure : ajoute une rule à un certain group sur tous locaux
#
##
CREATE PROCEDURE procedure_update_rule_group(var_nom_rule VARCHAR(255),libelle_group VARCHAR(255))
BEGIN
DECLARE bDone INT;
DECLARE id_local_temp INT;
DECLARE curs CURSOR FOR SELECT id_local FROM financial_local WHERE id_local = id_local_direction;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
OPEN curs;

SET bDone = 0;

fct_insert_group: LOOP
FETCH curs INTO id_local_temp;
IF bDone = 1 THEN
LEAVE fct_insert_group;
END IF;

SET @ID_RULE = ( SELECT id_rule FROM financial_rule WHERE rule = var_nom_rule AND id_local = id_local_temp);
SET @ID_GROUP = ( SELECT id_group FROM financial_group WHERE libelle = libelle_group AND id_local = id_local_temp);

SET @ALLREADY_IN_DB = NULL;

IF @ID_RULE IS NULL THEN
INSERT INTO financial_rule(rule,description,actif,id_local) VALUES (var_nom_rule,'/','Y',id_local_temp);
SET @ID_RULE = ( SELECT id_rule FROM financial_rule WHERE rule = var_nom_rule AND id_local = id_local_temp);
END IF;

IF @ID_RULE >0 AND @ID_GROUP>0 THEN
SET @ALLREADY_IN_DB = ( SELECT id_rule FROM financial_rule_group WHERE id_rule = @ID_RULE AND id_group = @ID_GROUP);
END IF;

IF @ALLREADY_IN_DB IS NULL AND @ID_RULE >0 AND @ID_GROUP>0 THEN
INSERT INTO financial_rule_group(id_group,id_rule) VALUES (@ID_GROUP,@ID_RULE);
END IF;

END LOOP fct_insert_group;
CLOSE curs;
END$$

DELIMITER ;


DROP FUNCTION IF EXISTS procedure_insert_group;

DELIMITER $$

CREATE FUNCTION procedure_insert_group(var_nom_group VARCHAR(255),type_group_temp VARCHAR(255),id_local_temp INT) RETURNS INT
BEGIN

	  SET @ID_GROUP = ( SELECT id_group FROM financial_group WHERE libelle = var_nom_group AND id_local = id_local_temp);
	  IF @ID_GROUP IS NULL THEN
	    INSERT INTO `financial_group` ( `libelle`,`type_group`, `id_local`) VALUES ( var_nom_group, type_group_temp,id_local_temp);
	    SET @ID_GROUP = ( SELECT LAST_INSERT_ID());
	  END IF;
	  
	  RETURN @ID_GROUP;

END$$

DELIMITER ;

DROP PROCEDURE IF EXISTS procedure_temp_group;

DELIMITER $$

CREATE PROCEDURE procedure_temp_group(var_nom_group VARCHAR(255),type_group_temp VARCHAR(255))
BEGIN
  DECLARE bDone INT;

  DECLARE id_local_temp INT;  

  DECLARE curs CURSOR FOR  SELECT id_local FROM financial_local WHERE id_local = id_local_direction;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  OPEN curs;

  SET bDone = 0;
 

 fct_insert_group: LOOP

	FETCH curs INTO id_local_temp;
 
	IF bDone = 1 THEN
	LEAVE fct_insert_group;
	END IF;
	
	SET @ID_GROUP = (SELECT procedure_insert_group(var_nom_group,type_group_temp,id_local_temp));
	
	INSERT INTO financial_parametre(id_local, variable,valeur,type) VALUES(id_local_temp,"GROUPE_RULES_LOCAL",@ID_GROUP,"Int");
	
	END LOOP fct_insert_group;
 
CLOSE curs;
END$$

DELIMITER ;

DROP PROCEDURE IF EXISTS procedure_insert_rule;

DELIMITER $$

##
#
# Procedure : insert une rule tous les locaux
#
##
CREATE PROCEDURE procedure_insert_rule(var_nom_rule VARCHAR(255), var_description_rule VARCHAR(255))
BEGIN
  DECLARE bDone INT;
  DECLARE id_local_temp INT;  
  DECLARE curs CURSOR FOR  SELECT id_local FROM financial_local WHERE id_local = id_local_direction;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
  OPEN curs;

  SET bDone = 0;
 
 fct_insert_group: LOOP
	FETCH curs INTO id_local_temp;
	IF bDone = 1 THEN
	LEAVE fct_insert_group;
	END IF;

INSERT INTO financial_rule(rule,description,actif,id_local) VALUES (var_nom_rule,'/','Y',id_local_temp);
#SET @ID_RULE = ( SELECT LAST_INSERT_ID());

#INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_RULE FROM financial_group);
END LOOP fct_insert_group;
CLOSE curs;
END$$

DELIMITER ;


SET @ID_LOCAL_ADMIN = (SELECT id_local FROM financial_rule WHERE rule='ADMIN'); 
INSERT INTO financial_rule(rule,description,actif,id_local) VALUES ('GESTION_TYPE_SOCIETE','/','Y',@ID_LOCAL_ADMIN);
INSERT INTO financial_rule(rule,description,actif,id_local) VALUES ('GESTION_TYPE_INTERMEDIAIRE','/','Y',@ID_LOCAL_ADMIN);

CALL procedure_rule_lier_parent('MENU','GESTION_TYPE_SOCIETE');
CALL procedure_rule_lier_parent('MENU','GESTION_TYPE_INTERMEDIAIRE');


DROP PROCEDURE IF EXISTS procedure_update_rule_group;

DELIMITER $$

##
#
# Procedure : ajoute une rule à un certain group sur tous locaux
#
##
CREATE PROCEDURE procedure_update_rule_group(var_nom_rule VARCHAR(255),libelle_group VARCHAR(255))
BEGIN
DECLARE bDone INT;
DECLARE id_local_temp INT;
DECLARE curs CURSOR FOR SELECT id_local FROM financial_local WHERE id_local = id_local_direction;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
OPEN curs;

SET bDone = 0;

fct_insert_group: LOOP
FETCH curs INTO id_local_temp;
IF bDone = 1 THEN
LEAVE fct_insert_group;
END IF;

SET @ID_RULE = ( SELECT id_rule FROM financial_rule WHERE rule = var_nom_rule AND id_local = id_local_temp);
SET @ID_GROUP = ( SELECT id_group FROM financial_group WHERE libelle = libelle_group AND id_local = id_local_temp);

SET @ALLREADY_IN_DB = NULL;

IF @ID_RULE IS NULL THEN
	INSERT INTO financial_rule(rule,description,actif,id_local) VALUES (var_nom_rule,'/','Y',id_local_temp);
	SET @ID_RULE = ( SELECT id_rule FROM financial_rule WHERE rule = var_nom_rule AND id_local = id_local_temp);
END IF;

IF @ID_RULE >0 AND @ID_GROUP>0 THEN
	SET @ALLREADY_IN_DB = ( SELECT id_rule FROM financial_rule_group WHERE id_rule = @ID_RULE AND id_group = @ID_GROUP);
END IF;

IF @ALLREADY_IN_DB IS NULL AND @ID_RULE >0 AND @ID_GROUP>0 THEN
	INSERT INTO financial_rule_group(id_group,id_rule) VALUES (@ID_GROUP,@ID_RULE);
END IF;

END LOOP fct_insert_group;
CLOSE curs;
END$$

DELIMITER ;



CALL procedure_update_rule_group('DIRECTION_PUB','Admin');

DROP PROCEDURE IF EXISTS fct_init_group_utilisateur;
DELIMITER $$
CREATE PROCEDURE fct_init_group_utilisateur(libelle_group_temp VARCHAR(255),type_group_temp VARCHAR(255),temp_type_utilisateur VARCHAR(255))
BEGIN
  DECLARE bDone INT;
  DECLARE id_local_temp INT;  
  DECLARE curs CURSOR FOR  SELECT id_local FROM financial_local WHERE id_local = id_local_direction;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
  OPEN curs;
  SET bDone = 0;
 
 fct_temp: LOOP
  FETCH curs INTO id_local_temp;
 
  IF bDone = 1 THEN
  LEAVE fct_temp;
  END IF;

  SET @ID_GROUP = ( SELECT id_group FROM financial_group WHERE libelle = libelle_group_temp AND id_local = id_local_temp);
  IF @ID_GROUP IS NULL THEN
    INSERT INTO `financial_group` ( `libelle`,`type_group`, `id_local`) VALUES ( libelle_group_temp, type_group_temp,id_local_temp);
    SET @ID_GROUP = ( SELECT LAST_INSERT_ID());
  END IF;
  IF @ID_GROUP > 0 THEN
      INSERT INTO financial_group_utilisateur(id_utilisateur,id_group)(SELECT fu.id_utilisateur,@ID_GROUP FROM financial_utilisateur fu LEFT JOIN financial_group_utilisateur fgu ON fu.id_utilisateur = fgu.id_utilisateur WHERE fgu.id_group IS NULL AND fu.type_utilisateur = temp_type_utilisateur AND fu.id_local_direction = id_local_temp);
  END IF;
  END LOOP fct_temp;
 
  CLOSE curs;
END$$
DELIMITER ;


CALL fct_init_group_utilisateur('Assistant Commercial',null,'assistantcommercialinternet');
CALL fct_init_group_utilisateur('Adjoint Tutel',null,'adjoint_tutel');
CALL fct_init_group_utilisateur('Direction',NULL,'direction');
CALL fct_init_group_utilisateur('Adjoint Technique',NULL,'adjoint_technique_ph');
CALL fct_init_group_utilisateur('RC','responsable','commercial_stagiaire');
CALL fct_init_group_utilisateur('RC','responsable','commercial_1');
CALL fct_init_group_utilisateur('RC','responsable','commercial_1_5');
CALL fct_init_group_utilisateur('RC','responsable','commercial_2');
CALL fct_init_group_utilisateur('RC','responsable','commercial_3');
CALL fct_init_group_utilisateur('RC','responsable','commercial_4');


CALL procedure_update_rule_group('DEPOT_BANQUE_COMMISSION_BANQUE','Direction');
CALL procedure_update_rule_group('DEPOT_BANQUE_COMMISSION_BANQUE','Directeur Commercial');




DROP PROCEDURE IF EXISTS procedure_insert_suivi_rc;

DELIMITER $$

CREATE PROCEDURE procedure_insert_suivi_rc()
BEGIN
  DECLARE bDone INT;
  DECLARE id_cc_temp INT;
  DECLARE id_utilisateur_temp INT;
  DECLARE id_dossier_temp INT;
  DECLARE allready_exist INT;

  DECLARE curs_dossier_error CURSOR FOR SELECT fgs.id_utilisateur,fd.id_dossier FROM financial_dossier fd,financial_gestion_suivi fgs WHERE fd.id_suivi_rc = fgs.id_gestionsuivi;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  OPEN curs_dossier_error;

  SET bDone = 0;

 fct_boucle: LOOP

  FETCH curs_dossier_error INTO id_utilisateur_temp,id_dossier_temp;
 
  IF bDone = 1 THEN
  LEAVE fct_boucle;
  END IF;

  SET allready_exist = (SELECT count(*) FROM financial_dossier_suivi WHERE id_utilisateur = id_utilisateur_temp AND id_dossier = id_dossier_temp);

    IF allready_exist = 0 AND id_utilisateur_temp>0 AND id_dossier_temp>0 THEN
      INSERT INTO financial_dossier_suivi SET id_utilisateur = id_utilisateur_temp, id_dossier = id_dossier_temp;
    END IF;

END LOOP fct_boucle;


END$$

DELIMITER ;


CALL procedure_insert_suivi_rc();

UPDATE financial_parametre SET type='String' WHERE variable = 'BANQUE_DEPOT_DOSSIER_IMMOBILIER';

ALTER TABLE  `financial_rendezvous` ADD INDEX (  `date_rdv` );
ALTER TABLE  `financial_gestion_suivi` ADD INDEX (  `date` );
ALTER TABLE  `financial_modification` ADD INDEX (  `date` );

ALTER TABLE  `financial_service_produit` ADD INDEX (  `reponse_client` );
ALTER TABLE  `financial_servicecontrat` ADD INDEX (  `type` );
ALTER TABLE  `financial_suivi_assurance` ADD INDEX (  `etat` );
ALTER TABLE  `financial_utilisateur` ADD INDEX (  `type_utilisateur` );
ALTER TABLE  `financial_local` ADD INDEX (  `type_local` );
ALTER TABLE  `financial_suivi_depot` ADD INDEX (  `etat` );
ALTER TABLE  `financial_validation` ADD INDEX (  `zone_valider` );
ALTER TABLE  `financial_pret_consommation` ADD INDEX (  `conserve` );
ALTER TABLE  `financial_pret_immobilier` ADD INDEX (  `conserve` );

ALTER TABLE financial_gestion_suivi ENGINE=InnoDB;
ALTER TABLE financial_rendezvous ENGINE=InnoDB;
ALTER TABLE financial_suivi_depot ENGINE=InnoDB;
ALTER TABLE financial_remarque ENGINE=InnoDB;
ALTER TABLE financial_pret_immobilier ENGINE=InnoDB;
ALTER TABLE financial_pret_consommation ENGINE=InnoDB;
ALTER TABLE financial_utilisateur ENGINE=InnoDB;
ALTER TABLE financial_modification ENGINE=InnoDB;


DELETE FROM financial_rule WHERE rule = 'MENU_MES_STATISTIQUES';
DELETE FROM financial_rule WHERE rule = 'MENU_RAPPORT';

DELETE FROM financial_rule_group WHERE id_rule NOT IN (SELECT id_rule FROM financial_rule);



DROP PROCEDURE IF EXISTS procedure_insert_group;

DELIMITER $$

CREATE PROCEDURE procedure_insert_group()
BEGIN
  DECLARE bDone INT;

  DECLARE id_local_temp INT;  

  DECLARE curs CURSOR FOR  SELECT id_local FROM financial_local WHERE id_local = id_local_direction;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  OPEN curs;

  SET bDone = 0;
 

 fct_insert_group: LOOP

	FETCH curs INTO id_local_temp;
 
	IF bDone = 1 THEN
	LEAVE fct_insert_group;
	END IF;

  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_MES_STATISTIQUES', '/', 'Y', id_local_temp);
  SET @ID_MENU_MES_STATISTIQUES = ( SELECT LAST_INSERT_ID());

  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_RAPPORT', '/', 'Y', id_local_temp);
  SET @ID_MENU_RAPPORT = ( SELECT LAST_INSERT_ID());


  INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_MENU_MES_STATISTIQUES FROM financial_group WHERE type_group IN ('direction' ) AND id_local = id_local_temp);
  INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_MENU_MES_STATISTIQUES FROM financial_group WHERE type_group IN ('responsable' ) AND id_local = id_local_temp);
  INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_MENU_MES_STATISTIQUES FROM financial_group WHERE type_group IN ('assistant' ) AND id_local = id_local_temp);

  INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_MENU_RAPPORT FROM financial_group WHERE type_group IN ('direction' ) AND id_local = id_local_temp);
  INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_MENU_RAPPORT FROM financial_group WHERE type_group IN ('responsable' ) AND id_local = id_local_temp);
  INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_MENU_RAPPORT FROM financial_group WHERE type_group IN ('assistant' ) AND id_local = id_local_temp);

  SET @ID_group_direction = ( SELECT id_group FROM financial_group WHERE libelle = 'Direction'  AND id_local = id_local_temp);
  SET @ID_rule_creation_dossier = ( SELECT id_rule FROM financial_rule WHERE rule = 'PRE_ETUDE_CREATION'  AND id_local = id_local_temp);
  SET @ID_rule_mes_clients = ( SELECT id_rule FROM financial_rule WHERE rule = 'MES_CLIENTS'  AND id_local = id_local_temp);

	IF @ID_rule_creation_dossier >0 THEN
	  INSERT INTO financial_rule_group(id_group,id_rule) VALUES (@ID_group_direction,@ID_rule_creation_dossier);
	END IF;
	
	IF @ID_rule_mes_clients >0 THEN
	  INSERT INTO financial_rule_group(id_group,id_rule) VALUES (@ID_group_direction,@ID_rule_mes_clients);
	END IF;
	
	  SET @ID_group_responsable = ( SELECT id_group FROM financial_group WHERE libelle = 'RC'  AND id_local = id_local_temp);
	  
	IF @ID_rule_mes_clients >0 THEN
	  INSERT INTO financial_rule_group(id_group,id_rule) VALUES (@ID_group_responsable,@ID_rule_mes_clients);
	END IF;

END LOOP fct_insert_group;
 
CLOSE curs;
END$$

DELIMITER ;

CALL procedure_insert_group();
DROP PROCEDURE IF EXISTS procedure_insert_group;

##permet de lister les utilisateurs lié aux dossiers
CREATE TABLE financial_dossier_suivi(
id_dossier MEDIUMINT UNSIGNED ZEROFILL NOT NULL,
id_utilisateur SMALLINT UNSIGNED ZEROFILL NOT NULL,
PRIMARY KEY (id_dossier,id_utilisateur),
FOREIGN KEY (id_dossier) REFERENCES financial_dossier(id_dossier) ON DELETE CASCADE,
FOREIGN KEY (id_utilisateur) REFERENCES financial_utilisateur(id_utilisateur) ON DELETE CASCADE
)ENGINE=InnoDB AUTO_INCREMENT=000;


TRUNCATE financial_dossier_suivi;

DROP PROCEDURE IF EXISTS procedure_insert_suivi_rc;

DELIMITER $$

CREATE PROCEDURE procedure_insert_suivi_rc()
BEGIN
  DECLARE bDone INT;
  DECLARE id_cc_temp INT;
  DECLARE id_utilisateur_temp INT;
  DECLARE id_dossier_temp INT;
  DECLARE allready_exist INT;

  DECLARE curs_dossier_error CURSOR FOR SELECT fgs.id_utilisateur,fd.id_dossier FROM financial_dossier fd,financial_gestion_suivi fgs WHERE fd.id_suivi_rc = fgs.id_gestionsuivi;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  OPEN curs_dossier_error;

  SET bDone = 0;

 fct_boucle: LOOP

  FETCH curs_dossier_error INTO id_utilisateur_temp,id_dossier_temp;
 
  IF bDone = 1 THEN
  LEAVE fct_boucle;
  END IF;

  SET allready_exist = (SELECT count(*) FROM financial_dossier_suivi WHERE id_utilisateur = id_utilisateur_temp AND id_dossier = id_dossier_temp);

    IF allready_exist = 0 AND id_utilisateur_temp>0 AND id_dossier_temp>0 THEN
      INSERT INTO financial_dossier_suivi SET id_utilisateur = id_utilisateur_temp, id_dossier = id_dossier_temp;
    END IF;

END LOOP fct_boucle;


END$$

DELIMITER ;


CALL procedure_insert_suivi_rc();


DROP PROCEDURE IF EXISTS procedure_colonne_dossierlist;

DELIMITER $$

CREATE PROCEDURE procedure_colonne_dossierlist()
BEGIN
  DECLARE bDone INT;

  DECLARE id_local_temp INT;  

  DECLARE curs CURSOR FOR  SELECT id_local FROM financial_local WHERE id_local = id_local_direction;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  OPEN curs;

  SET bDone = 0;
 

 fct_insert_group: LOOP

	FETCH curs INTO id_local_temp;
 
	IF bDone = 1 THEN
	LEAVE fct_insert_group;
	END IF;



INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
( 'DOSSIER_LIST_COLONNE_RESPONSABLE_VISIBLE', '/', 'Y', id_local_temp);
SET @ID_DOSSIER_LIST_COLONNE_RESPONSABLE_VISIBLE = ( SELECT LAST_INSERT_ID());

INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
( 'DOSSIER_LIST_COLONNE_ASSISTANT_VISIBLE', '/', 'Y', id_local_temp);
SET @ID_DOSSIER_LIST_COLONNE_ASSISTANT_VISIBLE = ( SELECT LAST_INSERT_ID());

INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
( 'DOSSIER_LIST_COLONNE_EDITEUR_VISIBLE', '/', 'Y', id_local_temp);
SET @ID_DOSSIER_LIST_COLONNE_EDITEUR_VISIBLE = ( SELECT LAST_INSERT_ID());

INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
( 'DOSSIER_LIST_COLONNE_PRIORITE_VISIBLE', '/', 'Y', id_local_temp);
SET @ID_DOSSIER_LIST_COLONNE_PRIORITE_VISIBLE = ( SELECT LAST_INSERT_ID());

INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
( 'DOSSIER_LIST_COLONNE_PRESCRIPTEUR_VISIBLE', '/', 'Y', id_local_temp);
SET @ID_DOSSIER_LIST_COLONNE_PRESCRIPTEUR_VISIBLE = ( SELECT LAST_INSERT_ID());

INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
( 'DOSSIER_LIST_COLONNE_CONTACT_VISIBLE', '/', 'Y', id_local_temp);
SET @ID_DOSSIER_LIST_COLONNE_CONTACT_VISIBLE = ( SELECT LAST_INSERT_ID());


INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_DOSSIER_LIST_COLONNE_RESPONSABLE_VISIBLE FROM financial_group WHERE type_group IN ('responsable' ) AND id_local = id_local_temp);
INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_DOSSIER_LIST_COLONNE_ASSISTANT_VISIBLE FROM financial_group WHERE type_group IN ('responsable' ) AND id_local = id_local_temp);
INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_DOSSIER_LIST_COLONNE_EDITEUR_VISIBLE FROM financial_group WHERE type_group IN ('responsable' ) AND id_local = id_local_temp);
INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_DOSSIER_LIST_COLONNE_PRIORITE_VISIBLE FROM financial_group WHERE type_group IN ('responsable' ) AND id_local = id_local_temp);
INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_DOSSIER_LIST_COLONNE_RESPONSABLE_VISIBLE FROM financial_group WHERE type_group IN ('responsable' ) AND id_local = id_local_temp);

INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_DOSSIER_LIST_COLONNE_RESPONSABLE_VISIBLE FROM financial_group WHERE type_group IN ('direction' ) AND id_local = id_local_temp);
INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_DOSSIER_LIST_COLONNE_ASSISTANT_VISIBLE FROM financial_group WHERE type_group IN ('direction' ) AND id_local = id_local_temp);
INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_DOSSIER_LIST_COLONNE_EDITEUR_VISIBLE FROM financial_group WHERE type_group IN ('direction' ) AND id_local = id_local_temp);
INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_DOSSIER_LIST_COLONNE_PRIORITE_VISIBLE FROM financial_group WHERE type_group IN ('direction' ) AND id_local = id_local_temp);
INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_DOSSIER_LIST_COLONNE_RESPONSABLE_VISIBLE FROM financial_group WHERE type_group IN ('direction' ) AND id_local = id_local_temp);

INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_DOSSIER_LIST_COLONNE_PRESCRIPTEUR_VISIBLE FROM financial_group WHERE type_group IN ('direction' ) AND id_local = id_local_temp);
INSERT INTO financial_rule_group(id_group,id_rule) (SELECT id_group,@ID_DOSSIER_LIST_COLONNE_CONTACT_VISIBLE FROM financial_group WHERE type_group IN ('direction' ) AND id_local = id_local_temp);

 
END LOOP fct_insert_group;
 
  CLOSE curs;
END$$

DELIMITER ;

CALL procedure_colonne_dossierlist();


DROP PROCEDURE IF EXISTS procedure_colonne_dossierlist;





################# UPDATE SQL REQUESTS - 3.1.0 -> 3.1.x #################

ALTER TABLE financial_group change type_group type_group enum('responsable','assistant','assistant indirect','azur') DEFAULT NULL;


TRUNCATE financial_rule;
TRUNCATE financial_rule_group;
TRUNCATE financial_group_utilisateur;
TRUNCATE financial_group;
TRUNCATE financial_group_superieur;

DROP PROCEDURE IF EXISTS procedure_insert_group;

DELIMITER $$

CREATE PROCEDURE procedure_insert_group()
BEGIN
  DECLARE bDone INT;

  DECLARE id_local_temp INT;  

  DECLARE curs CURSOR FOR  SELECT id_local FROM financial_local WHERE id_local = id_local_direction;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  OPEN curs;

  SET bDone = 0;
 

 fct_insert_group: LOOP

	FETCH curs INTO id_local_temp;
 
	IF bDone = 1 THEN
	LEAVE fct_insert_group;
	END IF;

  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_ACCUEIL', '/', 'Y', id_local_temp);
  SET @ID_MENU_CALENDRIER = ( SELECT LAST_INSERT_ID());
   
  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_ACCUEIL', '/', 'Y', id_local_temp);
  SET @ID_MENU_ACCUEIL = ( SELECT LAST_INSERT_ID());

  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_PRE_ETUDE_CREATION', '/', 'Y', id_local_temp);
  SET @ID_MENU_PRE_ETUDE_CREATION = ( SELECT LAST_INSERT_ID());


  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_GESTION_PRESCRIPTEUR', '/', 'Y', id_local_temp);
  SET @ID_MENU_GESTION_PRESCRIPTEUR = ( SELECT LAST_INSERT_ID());

  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_GESTION_NOTAIRE', '/', 'Y', id_local_temp);
  SET @ID_MENU_GESTION_NOTAIRE = ( SELECT LAST_INSERT_ID());

  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_ACCUEIL_SECONDAIRE_RC', '/', 'Y', id_local_temp);
  SET @ID_MENU_ACCUEIL_SECONDAIRE_RC = ( SELECT LAST_INSERT_ID());

  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_ACCUEIL_BFI', '/', 'Y', id_local_temp);
  SET @ID_MENU_ACCUEIL_BFI = ( SELECT LAST_INSERT_ID());

  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_RETOUR_PUB_COMMERCIAL', '/', 'Y', id_local_temp);
  SET @ID_MENU_RETOUR_PUB_COMMERCIAL = ( SELECT LAST_INSERT_ID());

  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_LISTE_DOSSIER', '/', 'Y', id_local_temp);
  SET @ID_MENU_LISTE_DOSSIER = ( SELECT LAST_INSERT_ID());

  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_LISTE_DOSSIER_LOCAUX', '/', 'Y', id_local_temp);
  SET @ID_MENU_LISTE_DOSSIER_LOCAUX = ( SELECT LAST_INSERT_ID());

  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_MES_STATISTIQUES', '/', 'Y', @ID_LOCAL);
  SET @ID_MENU_MES_STATISTIQUES = ( SELECT LAST_INSERT_ID());

  INSERT INTO `financial_rule` ( `rule`, `description`, `actif`, `id_local`) VALUES
  ( 'MENU_RAPPORT', '/', 'Y', @ID_LOCAL);
  SET @ID_MENU_RAPPORT = ( SELECT LAST_INSERT_ID());


  INSERT INTO `financial_group` ( `libelle`,`type_group`, `id_local`) VALUES
  ( 'RC', 'responsable',id_local_temp);
  SET @ID_GROUP = ( SELECT LAST_INSERT_ID());
  INSERT INTO financial_group_utilisateur(id_utilisateur,id_group)(
  SELECT id_utilisateur,@ID_GROUP FROM financial_utilisateur WHERE type_utilisateur IN ('commercial_stagiaire','commercial_1','commercial_1_5','commercial_2','commercial_3','commercial_4')  AND id_local_direction = id_local_temp);

  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_CALENDRIER);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_PRE_ETUDE_CREATION);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_GESTION_PRESCRIPTEUR);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_ACCUEIL);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_ACCUEIL_SECONDAIRE_RC);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_LISTE_DOSSIER);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_LISTE_DOSSIER_LOCAUX);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_ACCUEIL_BFI);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_MES_STATISTIQUES);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_RAPPORT);


  INSERT INTO `financial_group` ( `libelle`,`type_group`, `id_local`) VALUES
  ( 'Direction', 'responsable',id_local_temp);
  SET @ID_GROUP = ( SELECT LAST_INSERT_ID());
  INSERT INTO financial_group_utilisateur(id_utilisateur,id_group)(
  SELECT id_utilisateur,@ID_GROUP FROM financial_utilisateur WHERE type_utilisateur IN('direction','directiongeneral') AND id_local_direction = id_local_temp);

  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_CALENDRIER);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_ACCUEIL);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_GESTION_PRESCRIPTEUR);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_RETOUR_PUB_COMMERCIAL);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_LISTE_DOSSIER);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_LISTE_DOSSIER_LOCAUX);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_ACCUEIL_BFI);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_MES_STATISTIQUES);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_RAPPORT);

  INSERT INTO `financial_group` ( `libelle`,`type_group`, `id_local`) VALUES
  ( 'Adjoint Technique', 'assistant',id_local_temp);
  SET @ID_GROUP = ( SELECT LAST_INSERT_ID());
  INSERT INTO financial_group_utilisateur(id_utilisateur,id_group)(
  SELECT id_utilisateur,@ID_GROUP FROM financial_utilisateur WHERE type_utilisateur='adjoint_technique_ph' AND id_local_direction = id_local_temp);

  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_CALENDRIER);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_GESTION_PRESCRIPTEUR);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_PRE_ETUDE_CREATION);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_LISTE_DOSSIER);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_LISTE_DOSSIER_LOCAUX);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_MES_STATISTIQUES);
  INSERT INTO financial_rule_group(id_group,id_rule) VALUE(@ID_GROUP,@ID_MENU_RAPPORT);


END LOOP fct_insert_group;
 
CLOSE curs;
END$$

DELIMITER ;

CALL procedure_insert_group();

DROP PROCEDURE IF EXISTS procedure_insert_group;


INSERT INTO `financial_ville` ( `id_departement`, `cp`, `libelle`, `libelle_clean`, `lat`, `lng`) VALUES
(00068, '57155', 'Marly', 'MARLY', '49.06103', '6.15234');


ALTER TABLE financial_utilisateur 
MODIFY type_utilisateur ENUM('admin','superviseur','assistantcommercial','assistantcommercialinternet','direction','directiongeneral','directiontechnique','manager_azur','manager_commercial','manager_sg','manager_servicerealisation','DGA_azur','DGA_commercial','DGA_servicerealisation','DGA_prescription','charge_clientele','analyste_sg','responsableconcretisation','assistantprescription','assistanteservice','partenaire','commercial_stagiaire','commercial_1','commercial_1_5','commercial_2','commercial_3','commercial_4','adjoint_technique_ph','animateur_reseau','operateur_offre_de_pret','responsable back office','directeur_agence','responsable administratif','lambda','adjoint_tutel','comptabilite') NOT NULL;

CREATE TABLE financial_commissionnement(
id_commissionnement MEDIUMINT UNSIGNED ZEROFILL NOT NULL,
date_facturation_com DATE NULL,
date_facturation_surcom DATE NULL,
date_facturation_com_assurance DATE,
commission_banque DOUBLE UNSIGNED NULL,
surcommission_banque DOUBLE UNSIGNED NULL,
commission_assurance DOUBLE UNSIGNED NULL,
id_remarque MEDIUMINT UNSIGNED ZEROFILL NULL,
PRIMARY KEY (id_commissionnement),
FOREIGN KEY (id_commissionnement) REFERENCES financial_dossier(id_dossier) ON DELETE CASCADE,
FOREIGN KEY (id_remarque) REFERENCES financial_remarque(id_remarque) ON DELETE SET NULL
)ENGINE=InnoDB AUTO_INCREMENT=000;

INSERT INTO financial_parametre(variable,valeur,type) VALUES('COMMISSIONNEMENT_TAUX_RETROCESSION','75','int');


INSERT INTO  `financial_parametre` (
`id_local` ,
`variable` ,
`valeur` ,
`type`
)
VALUES (
NULL ,  'LIBELLE_SERVICE_AZUR',  'Fiche Client',  'String'
);

INSERT INTO financial_refus_detail(libelle,actif,type,id_categorie_refus,type_refus) 
VALUES("Refus avant transfert BFI","Y","global",1,"refus_cf");

SET @ID_PREFUS = ( SELECT LAST_INSERT_ID());

INSERT financial_parametre_agence(id_local,variable,valeur,type)
VALUES(22,"PARAM_ID_DETAILS_REFUS_BFI",@ID_PREFUS,"Int");

INSERT financial_parametre_agence(id_local,variable,valeur,type)
VALUES(22,"PARAM_ENABLE_REFUS_BFI_ATTENTE","true","Boolean");

ALTER TABLE financial_projet_immo
ADD COLUMN type_operation ENUM('renegociation','credit_immobilier','autre');

ALTER TABLE financial_projet_immo
ADD COLUMN connaissance ENUM('bon','moyen','insuffisant');

INSERT INTO financial_parametre_utilisateur(`id_utilisateur` ,`variable` ,`valeur` ,`type`) VALUES (1526,  'ENABLE_CONSULTATION_BFI',  'true',  'Boolean');


ALTER TABLE financial_projet_immo
ADD COLUMN remarque MEDIUMINT UNSIGNED ZEROFILL NULL;


ALTER TABLE financial_projet_immo
ADD FOREIGN KEY (remarque) REFERENCES financial_remarque(id_remarque) ON DELETE SET NULL;

ALTER TABLE financial_projet_immo
MODIFY objet_financement ENUM('residence_principale','residence_secondaire','investissement_locartif','murs_commerciaux','rachat_pret','autre') NULL;


INSERT INTO financial_parametre_utilisateur(`id_utilisateur` ,`variable` ,`valeur` ,`type`) VALUES (1526,  'ENABLE_CONSULTATION_BFI',  'true',  'Boolean');

CREATE TABLE financial_projet_immo (
  id_projet_immo MEDIUMINT UNSIGNED ZEROFILL NOT NULL,
  objet_financement ENUM('residence_principale','residence_secondaire','investissement_locartif','murs_commerciaux','rachat_pret') NULL,
  etat_residence ENUM('etat_ancien','etat_recent') NULL, 
  type_residence ENUM('maison','appartement') NULL,
  surface SMALLINT UNSIGNED NULL,
  nb_pieces TINYINT UNSIGNED NULL,
  performance_energetique ENUM('A','B','C','D','E','F','G') NULL,
  adresse1 VARCHAR(255) NULL,
  adresse2 VARCHAR(255) NULL,
  ville varchar(255) NULL,
  code_postal varchar(255) NULL,
  pays SMALLINT(5) UNSIGNED NULL,
  type_signature ENUM('signature_acte_vente','signature_livraison') NULL,
  date_signature date NULL,
  PRIMARY KEY (id_projet_immo),
  FOREIGN KEY (id_projet_immo) REFERENCES financial_dossier(id_dossier) ON DELETE CASCADE,
  FOREIGN KEY (pays) REFERENCES financial_pays(id_pays) ON DELETE SET NULL
) ENGINE=InnoDB;

UPDATE financial_produit_banque 
SET `libelle` = 'Pro +2ans TEAP 45% Primo RAC' 
WHERE  `id_produit_banque` = 12;

UPDATE financial_produit_banque 
SET `libelle` = 'LOC OU PRO -2ANS TEAP40%' 
WHERE  `id_produit_banque` = 99;

UPDATE financial_produit_banque 
SET `libelle` = 'LOC OU POR -2ANS TEAP 45%' 
WHERE  `id_produit_banque` = 143;

UPDATE financial_produit_banque 
SET `libelle` = 'Pro +2ans TEAP45% RAC de RAC' 
WHERE  `id_produit_banque` = 199;

ALTER TABLE financial_charge ADD charge_courante DOUBLE NULL;

ALTER TABLE financial_suivi_assurance 
MODIFY etat ENUM(
  'reception_adp',
  'renvoi_client',
  'envoi_compagnie',
  'refus_cf',
  'refus_client',
  'accord_compagnie',
  'refus_compagnie',
  'demande_doc',
  'envoi_doc',
  'tarif_normal',
  'majoration',
  'reception_bpa',
  'edition_ca',
  'envoi_adp'
  ) NOT NULL;

ALTER TABLE `financial_cf100` CHANGE `type_client` `type_client` ENUM( 'locataire', 'proprietaire', 'accedant_propriete', 'loge_titre_gratuit' ) DEFAULT NULL;

ALTER TABLE financial_gestion_bfi
ADD COLUMN (
`bfi_semaine` smallint UNSIGNED zerofill NOT NULL,
`bfi_max_semaine` smallint UNSIGNED zerofill NOT NULL,
`jour_semaine` varchar(255)
);

INSERT INTO  `financial_banque` (
`libelle` ,
`id_remarque` ,
`actif` ,
`list_autorise_depot` ,
`adresse1` ,
`adresse2` ,
`cp` ,
`ville` ,
`pays`
)
VALUES (
  
'Banque', 
NULL ,  
'Y',  
'commercial_4-commercial_stagiaire-commercial_1-commercial_1_5-commercial_2-commercial_3-partenaire',  
NULL,  
NULL,  
NULL,  
NULL,  
'FRANCE'
);

SET @ID_BANQUE_IMMO = ( SELECT LAST_INSERT_ID()) ;

INSERT INTO financial_parametre(variable,valeur,type)
VALUES('BANQUE_DEPOT_DOSSIER_IMMOBILIER',@ID_BANQUE_IMMO,'Int');





ALTER TABLE financial_gestion_bfi 
ADD COLUMN `bfi_max_jour` SMALLINT UNSIGNED NOT NULL DEFAULT 0;

INSERT INTO `financial_compagnie` (
`adresse1` ,
`adresse2` ,
`cp` ,
`ville` ,
`pays` ,
`tel1` ,
`tel2` ,
`fax` ,
`mail` ,
`id_remarque` ,
`actif` ,
`raison_sociale`
)
VALUES (
'Assurance Groupe', 'Assurance Groupe', '.', '.', '.', '.', '.', '.', NULL, NULL , 'Y', 'Assurance Groupe'
);

SET @id_compagnie = ( SELECT LAST_INSERT_ID( ) ) ;


INSERT INTO `financial_servicecontrat` (
`libelle` ,
`type` ,
`actif` ,
`id_compagnie`
)
VALUES (
'Divers', 'assurance', 'Y', @id_compagnie
);



ALTER TABLE financial_gestion_bfi 
ADD COLUMN 
(bfi_jour SMALLINT UNSIGNED NOT NULL,
id_remarque MEDIUMINT UNSIGNED ZEROFILL NULL);

ALTER TABLE financial_gestion_bfi 
ADD FOREIGN KEY (id_remarque) REFERENCES financial_remarque(id_remarque);

ALTER TABLE financial_affiliation
ADD COLUMN id_local SMALLINT UNSIGNED ZEROFILL NULL;

UPDATE financial_affiliation a, financial_dossier d 
SET a.id_local = d.id_local
WHERE a.id_dossier = d.id_dossier
AND a.id_local IS NULL;

################# UPDATE SQL REQUESTS - 3.0.0 -> 3.0.x #################

INSERT INTO financial_parametre(id_local, variable,valeur,type) VALUES(NULL,"MAIL_ADMINISTRATEUR","marc.richard@credit-libra.fr","String");

################# UPDATE SQL REQUESTS - 2.1.13 -> 2.1.14 #################

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"DOCUMENT_COM_LETTRE_DEMANDE_COMPLEMENT",'false',"Boolean" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"DOCUMENT_COM_LETTRE_RETOUR_CONTRAT",'false',"Boolean" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"DOCUMENT_COM_LETTRE_COMPLETUDE",'false',"Boolean" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"TEMPLATE_VIERGE","false","Boolean" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"TEMPLATE_ESPACEMENT",4.5,"float" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"TEMPLATE_DATE_JOUR_MARGE_GAUCHE",110,"Float" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"TEMPLATE_DATE_JOUR_MARGE_TOP",70,"Float" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"TEMPLATE_INFO_CLIENT_MARGE_GAUCHE",110,"Float" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"TEMPLATE_INFO_CLIENT_MARGE_TOP",45,"Float" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"TEMPLATE_REFERENCE_MARGE_TOP",75,"Float" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"TEMPLATE_PARAGRAPHE_INFO_MARGE_TOP",50,"Float" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"TEMPLATE_CONTENU_MARGE_TOP",100,"Float" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"TEMPLATE_ADRESSE_ENTREPRISE_MARGE_TOP",65,"Float" FROM financial_local WHERE id_local = id_local_direction);

INSERT INTO financial_parametre(id_local,variable,valeur,type)(
SELECT id_local,"TEMPLATE_ADRESSE_ENTREPRISE_MARGE_GAUCHE",24,"Float" FROM financial_local WHERE id_local = id_local_direction);

################# UPDATE SQL REQUESTS - 2.1.9 -> 2.2.0 #################
--
--SET FOREIGN_KEY_CHECKS = 0;
--
--TRUNCATE TABLE financial_produit_banque;
--TRUNCATE TABLE financial_type_calcul_loyer_1;
--TRUNCATE TABLE financial_type_calcul_loyer_2;
--TRUNCATE TABLE financial_type_calcul_loyer_3;
--TRUNCATE TABLE financial_type_calcul_loyer_4;
--TRUNCATE TABLE financial_link_type_calcul_loyer;
--
--INSERT INTO `financial_produit_banque` (`id_produit_banque`, `libelle`, `age_max_debut_pret`, `age_min_debut_pret`, `endettement_avant_max`, `endettement_apres_ac`, `reste_a_vivre1`, `reste_a_vivre2`, `reste_a_vivre3`, `reste_a_vivre4`, `reste_a_vivre_supp`, `montant_max_pro`, `montant_min`, `ratio_hypo`, `ficp`, `fcc`, `dossier_refinance`, `type_produit`, `actif`, `id_banque`, `id_remarque`, `age_max_fin_pret`, `revenu_mini`, `duree_max`, `charge`, `apl`, `taux_nominal`, `frais_dossier`, `honoraire_cf`, `pourcentage_apl`, `pourcentage_revenus_fonciers`, `frais_dossier_plafond`, `nombre_pp_min`, `pourcentage_immo_min`, `pourcentage_immo_max`, `endettement_avant_min`, `ordre`, `montant_max_loc`, `type_dossier`, `pourcentage_immo_conso_with_treso`, `montant_rachat_with_honoraire`, `revenu_without_pension_versee`, `nombre_pp_max`, `reste_a_vivre5`) VALUES
--(00010, 'Ev 50 % rev 3000 LOCATAIRE', 0, 18, 50, 40, 750, 1000, 1200, 1600, 400, 80000, 10000, 0, 'N', 'N', 'N', 'consommation', 'N', 003, 01247508, 80, 3000, 120, 'N', 'N', 8.9, 0, 0, 70, 100, 0, 0, 0, 0, 0, 1, 80000, 'locataire', 'Y', 'N', 'N', 0, 2000),
--(00011, 'CREATIS P1 : PROPRIETAIRE', 0, 18, 0, 45, 710, 710, 1065, 1420, 355, 150000, 7650, 0, 'N', 'Y', 'N', 'consommation', 'Y', 004, NULL, 75, 0, 144, 'N', 'Y', 8.4, 2.4, 5, 0, 100, 0, 0, 0, 60, 0, 1, 150000, 'proprietaire', 'Y', 'Y', 'Y', 0, 1775),
--(00012, 'PROPRIETAIRE OU ACCEDANT +2ans TEAP 45%', 0, 18, 0, 45, 750, 900, 1100, 1300, 250, 100000, 20000, 0, 'N', 'N', 'N', 'consommation', 'Y', 005, NULL, 83, 0, 144, 'Y', 'N', 7.73, 1.5, 5, 100, 100, 0, 0, 0, 0, 0, 0, 100000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1550),
--(00014, 'Ev 55% rev 3500', 75, 25, 55, 50, 650, 900, 1100, 1252, 150, 80000, 10000, 0, 'N', 'N', 'N', 'consommation', 'Y', 006, NULL, 84, 3500, 144, 'Y', 'N', 7.3, 1.5, 5, 0, 80, 0, 0, 0, 0, 0, 0, 80000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1402),
--(00023, 'MEDIATIS P', 0, 25, 0, 48, 650, 900, 1100, 1250, 150, 80000, 3000, 0, 'N', 'N', 'N', 'consommation', 'N', 011, NULL, 80, 1300, 144, 'Y', 'N', 7.73, 5, 1.5, 0, 80, 0, 0, 0, 0, 0, 0, 80000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1400),
--(00024, 'GE P', 0, 18, 70, 40, 900, 1100, 1350, 1600, 250, 70000, 10000, 0, 'N', 'N', 'N', 'consommation', 'Y', 001, NULL, 80, 0, 144, 'N', 'N', 8.5, 1.5, 5, 0, 80, 0, 0, 0, 0, 0, 0, 70000, 'proprietaire', 'Y', 'Y', 'Y', 0, 1850),
--(00026, 'COFIDIS P', 0, 0, 90, 65, 490, 590, 690, 790, 100, 60000, 3000, 0, 'N', 'N', 'N', 'consommation', 'Y', 014, NULL, 80, 0, 120, 'Y', 'N', 8.94, 1.5, 5, 100, 80, 0, 0, 0, 0, 0, 0, 60000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 890),
--(00027, 'EP+1 EV 50% 300M EN LCC', 0, 0, 50, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 001, 01247515, 85, 2500, 300, 'Y', 'N', 4.35, 0, 0, 0, 80, 700, 6, 0, 60, 0, 1, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00028, 'EP+ 2 EV 50% 360M EN LCC', 0, 0, 50, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 001, 01247516, 85, 2500, 360, 'Y', 'N', 5.35, 0, 0, 0, 80, 700, 6, 0, 60, 0, 2, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00029, 'EP 1 : EV 40% 300M EN LCC', 0, 0, 40, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 001, 01247517, 85, 0, 300, 'Y', 'N', 4.35, 1.5, 0, 0, 80, 1490, 4, 0, 60, 0, 5, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00030, 'EP 2 : EV 40% 360M EN LCC', 0, 0, 40, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 001, 01247518, 85, 0, 360, 'Y', 'N', 5.5, 1.5, 0, 0, 80, 1490, 4, 0, 60, 0, 6, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00031, 'E1 1 : EV 50% 300M EN LCC', 0, 0, 50, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 001, 01247519, 85, 0, 300, 'Y', 'N', 4.45, 1.5, 0, 0, 80, 1490, 6, 0, 60, 0, 9, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00032, 'E1 2 : EV 50% 360M EN LCC', 0, 0, 50, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 001, 01247520, 85, 0, 360, 'Y', 'N', 4.65, 1.5, 0, 0, 80, 1490, 6, 0, 60, 0, 10, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00033, 'E2 1 : EV 60% 300M EN LCC', 0, 0, 60, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 001, 01247521, 85, 0, 300, 'Y', 'N', 4.95, 1.5, 0, 0, 80, 1490, 0, 0, 60, 0, 14, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00034, 'E2 2 : EV 60% 300M EN LS2', 0, 0, 60, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 001, NULL, 85, 0, 300, 'Y', 'N', 4.45, 1.5, 0, 0, 80, 1490, 0, 60, 100, 0, 15, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00035, 'E2R 1 : EV 60% 300 MOIS LCC', 0, 0, 60, 35, 750, 900, 1150, 1400, 250, 1000000, 30000, 70, 'N', 'N', 'N', 'hypothecaire', 'Y', 001, 01247522, 85, 0, 300, 'Y', 'N', 5.45, 1.5, 0, 0, 80, 1490, 0, 0, 60, 0, 16, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00036, 'E2R 2 : EV60% 300 MOIS LS2', 0, 0, 60, 35, 750, 900, 1150, 1400, 250, 1000000, 30000, 70, 'N', 'N', 'N', 'hypothecaire', 'Y', 001, NULL, 85, 0, 300, 'Y', 'N', 4.95, 1.5, 0, 0, 80, 1490, 0, 60, 100, 0, 17, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00037, 'E4 1 : EV 80-100 300 MOIS LCC', 0, 0, 100, 38, 850, 1000, 1250, 1500, 250, 400000, 35000, 70, 'N', 'Y', 'N', 'hypothecaire', 'Y', 001, 01247523, 85, 0, 300, 'Y', 'N', 5.55, 1.5, 0, 0, 80, 1490, 0, 0, 60, 0, 22, 400000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1750),
--(00038, 'E4 2 : EV 80-100 300 MOIS LS2', 0, 0, 100, 38, 850, 1000, 1250, 1500, 250, 400000, 35000, 70, 'N', 'Y', 'N', 'hypothecaire', 'Y', 001, NULL, 85, 0, 300, 'Y', 'N', 5.1, 1.5, 0, 0, 80, 1490, 0, 60, 100, 0, 23, 400000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1750),
--(00039, 'Privilege 1', 0, 0, 50, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 002, NULL, 85, 0, 300, 'Y', 'N', 4.81, 1.5, 0, 0, 80, 1490, 20, 50, 100, 0, 1, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1650),
--(00040, 'Privilege 2', 0, 0, 50, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 002, NULL, 80, 0, 360, 'Y', 'N', 5.16, 1.5, 0, 0, 80, 1490, 20, 50, 100, 0, 2, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1650),
--(00041, 'Privilege 3', 0, 0, 70, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 002, NULL, 85, 0, 300, 'Y', 'N', 5.64, 1.5, 0, 0, 80, 1490, 20, 50, 100, 0, 3, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1650),
--(00042, 'Privilege 4', 0, 0, 70, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 002, NULL, 85, 0, 360, 'Y', 'N', 5.95, 1.5, 0, 0, 80, 1490, 20, 50, 100, 0, 4, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1650),
--(00043, 'Libre Finance 1', 0, 0, 50, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 50, 'N', 'N', 'N', 'hypothecaire', 'N', 002, NULL, 85, 0, 300, 'Y', 'N', 5.3, 1.5, 0, 0, 80, 1490, 20, 50, 100, 0, 5, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1650),
--(00044, 'Libre Finance 2', 0, 0, 50, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 50, 'N', 'N', 'N', 'hypothecaire', 'N', 002, NULL, 80, 0, 360, 'Y', 'N', 5.66, 1.5, 0, 0, 80, 1490, 20, 50, 100, 0, 6, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1650),
--(00045, 'Libre Finance 3', 0, 0, 80, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 70, 'N', 'N', 'N', 'hypothecaire', 'N', 002, NULL, 85, 0, 300, 'Y', 'N', 5.89, 1.5, 0, 0, 80, 1490, 20, 50, 100, 0, 7, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1650),
--(00046, 'Libre Finance 4', 0, 0, 80, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 70, 'N', 'N', 'N', 'hypothecaire', 'N', 002, NULL, 80, 0, 360, 'Y', 'N', 6.26, 1.5, 0, 0, 80, 1490, 20, 50, 100, 0, 8, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1650),
--(00047, 'Libre Finance 5', 0, 0, 80, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 002, NULL, 85, 0, 300, 'Y', 'N', 6.3, 1.5, 0, 0, 80, 1490, 20, 50, 100, 0, 9, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1650),
--(00048, 'Libre Finance 6', 0, 0, 80, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 002, NULL, 80, 0, 360, 'Y', 'N', 6.66, 1.5, 0, 0, 80, 1490, 20, 50, 100, 0, 10, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1650),
--(00049, 'Rebond 1', 0, 0, 80, 35, 850, 1000, 1250, 1500, 250, 1000000, 30000, 65, 'Y', 'Y', 'N', 'hypothecaire', 'N', 002, NULL, 80, 0, 300, 'Y', 'N', 7.45, 1.5, 0, 0, 80, 1490, 20, 50, 100, 0, 11, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1750),
--(00050, 'Rebond 2', 0, 0, 80, 35, 850, 1000, 1250, 1500, 250, 1000000, 30000, 65, 'Y', 'Y', 'N', 'hypothecaire', 'N', 002, NULL, 80, 0, 360, 'Y', 'N', 7.71, 1.5, 0, 0, 80, 1490, 20, 50, 100, 0, 12, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1750),
--(00051, 'Rebond plus', 0, 0, 65, 35, 850, 1000, 1250, 1500, 250, 400000, 30000, 65, 'Y', 'Y', 'N', 'hypothecaire', 'N', 002, NULL, 75, 0, 300, 'Y', 'N', 7.45, 1.5, 0, 0, 80, 1490, 10, 0, 100, 0, 13, 400000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1750),
--(00052, 'RC1 1 : REVENU  1500 LCC', 0, 0, 40, 30, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, 01247546, 82, 1500, 300, 'Y', 'N', 5.15, 0, 0, 0, 80, 600, 100, 0, 60, 0, 1, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00053, 'RC1 2 : REVENU 3000 LCC', 0, 0, 40, 36, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, 01247547, 82, 3000, 300, 'Y', 'N', 5.15, 0, 0, 0, 80, 600, 100, 0, 60, 0, 2, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00054, 'RC1 3 : REVENU 4500 LCC', 0, 0, 40, 42, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, 01247548, 82, 4500, 300, 'Y', 'N', 5.15, 0, 0, 0, 80, 600, 100, 0, 60, 0, 3, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00055, 'RC2 1 REVENU  1500 LCC', 0, 0, 50, 30, 550, 830, 1101, 1252, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, 01247549, 82, 1500, 300, 'Y', 'N', 5.15, 0, 0, 0, 80, 850, 100, 0, 60, 0, 7, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1434),
--(00056, 'RC2 2  REVENU 3000 LCC', 0, 0, 50, 36, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, 01247550, 82, 3000, 300, 'Y', 'N', 5.15, 0, 0, 0, 80, 850, 100, 0, 60, 0, 8, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00057, 'RC2 3 REVENU 4500 LCC', 0, 0, 50, 42, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, 01247551, 82, 4500, 300, 'Y', 'N', 5.15, 0, 0, 0, 80, 850, 100, 0, 60, 0, 9, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00058, 'RC3 1 REVENU  1500 LCC', 0, 0, 60, 30, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, 01247552, 82, 1500, 300, 'Y', 'N', 5.15, 0, 0, 0, 80, 1000, 100, 0, 60, 0, 13, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00059, 'RC3 2 REVENU 3000 LCC', 0, 0, 60, 36, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, 01247553, 82, 3000, 300, 'Y', 'N', 5.15, 0, 0, 0, 80, 1000, 100, 0, 60, 0, 14, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00060, 'RC3 3 REVENU 4500 LCC', 0, 0, 60, 42, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, 01247554, 82, 4500, 300, 'Y', 'N', 5.15, 0, 0, 0, 80, 1000, 100, 0, 60, 0, 15, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00061, 'RC4 1 EV>80%.REV 1500 LCC', 0, 0, 80, 30, 850, 1100, 1325, 1528, 182, 0, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 009, 01247555, 82, 1500, 300, 'Y', 'N', 5.15, 0, 0, 0, 80, 1250, 100, 0, 60, 0, 19, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1710),
--(00062, 'RC4 2 EV>80%.REV 3000 LCC', 0, 0, 80, 31.5, 850, 1100, 1325, 1528, 182, 0, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 009, 01247556, 82, 3000, 300, 'Y', 'N', 5.15, 0, 0, 0, 80, 1250, 100, 0, 60, 0, 20, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1710),
--(00063, 'RC4 3 EV>80%.REV 4500 LCC', 0, 0, 80, 33, 850, 1100, 1325, 1528, 182, 0, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 009, 01247557, 82, 4500, 300, 'Y', 'N', 5.15, 0, 0, 0, 80, 1250, 100, 0, 60, 0, 21, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1710),
--(00064, 'HYPOCLASSIQUE TEAP 35%', 0, 0, 500, 35, 780, 1000, 1250, 1500, 250, 1500000, 22000, 90, 'N', 'N', 'N', 'hypothecaire', 'Y', 012, 01247558, 95, 0, 420, 'Y', 'N', 5.7, 1, 0, 0, 80, 0, 0, 0, 60, 0, 1, 1500000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1750),
--(00065, 'HYPO IMMO TEAP 35%', 0, 0, 100, 35, 780, 1000, 1250, 1500, 250, 1500000, 22000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 012, 01247561, 95, 0, 420, 'Y', 'N', 4.95, 1, 1.9, 0, 80, 0, 0, 60, 100, 0, 4, 1500000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1750),
--(00066, 'Hypowash :  fichÃ©', 0, 0, 500, 35, 780, 1000, 1250, 1500, 250, 300000, 22000, 80, 'Y', 'N', 'N', 'hypothecaire', 'Y', 012, 01247562, 95, 0, 420, 'Y', 'N', 6.8, 1, 0, 0, 80, 0, 0, 0, 60, 50, 8, 300000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1750),
--(00067, 'Optimum', 0, 0, 55, 45, 700, 900, 1150, 1500, 350, 0, 40000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 003, 01247511, 84, 2000, 300, 'Y', 'N', 5, 3, 0, 0, 70, 0, 0, 0, 60, 0, 2, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1850),
--(00068, 'OPTIMUM + : EV MAX 50%', 0, 0, 50, 40, 700, 900, 1050, 1400, 350, 0, 40000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 003, NULL, 84, 3000, 240, 'Y', 'N', 6.5, 3, 0, 0, 70, 0, 0, 0, 60, 0, 1, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1750),
--(00069, 'RAC I : PART IMMO > 60%', 0, 0, 45, 40, 700, 900, 1150, 1500, 350, 0, 40000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 003, 01247513, 84, 3000, 300, 'Y', 'N', 6.21, 3, 0, 0, 70, 0, 0, 60, 100, 0, 4, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1850),
--(00070, 'SERENIUM : EV > 70%, REV 3000', 0, 0, 80, 40, 700, 900, 1050, 1400, 350, 0, 40000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 003, NULL, 84, 1500, 240, 'Y', 'N', 8.95, 3, 0, 0, 70, 0, 0, 0, 60, 0, 3, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1750),
--(00071, 'ALLEGIO 1 : EV45%, REV.3000', 75, 0, 45, 50, 650, 900, 1100, 1250, 150, 800000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 006, 01247525, 85, 3000, 300, 'Y', 'N', 5.65, 1.5, 0, 0, 80, 1500, 0, 0, 60, 0, 1, 800000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1400),
--(00072, 'ALLEGIO 2 : EV45% REV 1300', 75, 0, 45, 50, 650, 900, 1100, 1250, 150, 800000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 006, NULL, 85, 1300, 300, 'Y', 'N', 5.9, 1.5, 0, 0, 80, 1500, 0, 0, 60, 0, 2, 800000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1400),
--(00073, 'ALLEGIO 3 : EV < 55%', 75, 0, 55, 50, 650, 900, 1100, 1250, 150, 800000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 006, 01247527, 85, 1300, 300, 'Y', 'N', 6.1, 1.5, 0, 0, 80, 1500, 0, 0, 60, 0, 3, 800000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1400),
--(00074, 'SUMMUM 2 : EV60%, 360M LCC', 70, 0, 60, 33, 900, 900, 1100, 1300, 200, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 008, 01247534, 86, 0, 240, 'Y', 'N', 5.5, 1.5, 0, 0, 80, 1000000000000, 3, 0, 60, 0, 2, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1500),
--(00075, 'SUMMUM 3 : EV60%, 420M LCC', 70, 0, 60, 33, 900, 900, 1100, 1300, 200, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 008, 01247535, 86, 0, 240, 'Y', 'N', 6.5, 1.5, 0, 0, 80, 1500000000000, 3, 0, 60, 0, 3, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1500),
--(00076, 'PREMIUM 2 : EV>60 360M LCC', 70, 0, 100, 33, 900, 900, 1100, 1300, 200, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 008, 01247538, 86, 0, 240, 'Y', 'N', 6, 1.5, 0, 0, 80, 15000000000, 10, 0, 60, 0, 8, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1500),
--(00077, '>60% IMMO EV 100% MAX 10PP MAX', 70, 0, 100, 33, 900, 900, 1100, 1300, 200, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 008, 01247539, 86, 0, 240, 'Y', 'N', 3.1, 1.5, 0, 100, 85, 15000000000, 10, 60, 100, 0, 9, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1500),
--(00078, '<60% IMMO EV 100% MAX 10PP', 70, 0, 100, 33, 1000, 1250, 1500, 1750, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 008, 01247542, 86, 0, 240, 'Y', 'N', 6.1, 1.5, 0, 100, 85, 15000000000000, 10, 0, 60, 0, 11, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 2000),
--(00079, 'OPTIMUM 2 : +10PP 360M LCC', 70, 0, 100, 33, 1000, 1250, 1500, 1750, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 008, 01247543, 86, 0, 240, 'Y', 'N', 7.8, 1.5, 0, 0, 80, 15000000000000, 100, 0, 60, 0, 12, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 2000),
--(00080, 'MEDIATIS H', 74, 0, 100, 50, 650, 900, 1100, 1250, 150, 0, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 011, NULL, 85, 1300, 300, 'Y', 'N', 7.5, 1.5, 0, 0, 80, 1500, 0, 0, 90, 0, 1, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1400),
--(00081, 'Ev 70 rev 3000 LOCATAIRE', 0, 18, 70, 40, 750, 1000, 1200, 1600, 400, 80000, 10000, 0, 'N', 'N', 'N', 'consommation', 'Y', 003, 01247509, 80, 3000, 120, 'N', 'N', 10.5, 0, 0, 70, 100, 0, 0, 0, 0, 0, 3, 80000, 'locataire', 'Y', 'N', 'N', 0, 2000),
--(00082, 'Ev 70 rev 1500 LOCATAIRE', 0, 18, 80, 40, 750, 1000, 1200, 1600, 400, 80000, 10000, 0, 'N', 'N', 'N', 'consommation', 'Y', 003, 01247510, 80, 1500, 120, 'N', 'N', 11.06, 0, 0, 70, 100, 0, 0, 0, 0, 0, 5, 80000, 'locataire', 'Y', 'N', 'N', 0, 2000),
--(00083, 'Summum 5 : EV60% 360M LS2', 70, 0, 0, 33, 900, 900, 1100, 1300, 200, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 008, 01247536, 86, 0, 240, 'Y', 'N', 4.7, 1.5, 0, 0, 80, 150000000000, 3, 60, 100, 0, 5, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1500),
--(00084, 'Summum 6 : EV60% 420M LS2', 70, 0, 0, 33, 900, 900, 1100, 1300, 200, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 008, 01247537, 86, 0, 240, 'Y', 'N', 5.7, 1.5, 0, 0, 80, 150000000000, 3, 60, 100, 0, 6, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1500),
--(00085, 'Premium5', 70, 0, 0, 40, 700, 1000, 1300, 1600, 300, 1000000, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 008, 01247540, 86, 0, 420, 'Y', 'N', 4.2, 1.5, 0, 0, 80, 15000000000000, 10, 60, 100, 0, 11, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1900),
--(00086, 'Premium6', 70, 0, 0, 40, 700, 1000, 1300, 1600, 300, 1000000, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 008, 01247541, 86, 0, 420, 'Y', 'N', 5.85, 1.5, 0, 0, 80, 150000000000000, 10, 60, 100, 0, 12, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1900),
--(00087, '>60% IMMO EV 100% MAX 10PP', 70, 0, 100, 33, 1000, 1250, 1500, 1750, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 008, 01247544, 86, 0, 240, 'Y', 'N', 3.4, 1.5, 0, 100, 85, 15000000000000, 10, 60, 100, 0, 13, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 2000),
--(00088, 'OPTIMUM 4 : +10PP 360M LS2', 70, 0, 100, 33, 1000, 1250, 1500, 1750, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 008, 01247545, 86, 0, 240, 'Y', 'N', 5.1, 1.5, 0, 0, 80, 1500000000000, 100, 60, 100, 0, 14, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 2000),
--(00089, 'HYPOCLASSIQUE TEAP 38%', 0, 0, 500, 38, 930, 1200, 1450, 1700, 250, 1500000, 22000, 90, 'N', 'N', 'N', 'hypothecaire', 'Y', 012, 01247559, 95, 0, 420, 'Y', 'N', 5.7, 1, 0, 0, 80, 0, 0, 0, 60, 0, 2, 1500000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1950),
--(00090, 'HYPOCLASSIQUE TEAP 40%', 0, 0, 500, 40, 1130, 1400, 1650, 1900, 250, 1500000, 22000, 90, 'N', 'N', 'N', 'hypothecaire', 'Y', 012, 01247560, 95, 0, 420, 'Y', 'N', 5.7, 1, 0, 0, 80, 0, 0, 0, 60, 0, 3, 1500000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 2150),
--(00091, 'Classique 5', 0, 0, 100, 35, 780, 1000, 1250, 1500, 250, 1500000, 22000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 012, NULL, 95, 0, 420, 'Y', 'N', 5.9, 1, 0, 0, 80, 0, 0, 0, 60, 0, 5, 1500000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1750),
--(00092, 'Classique 6', 0, 0, 100, 35, 780, 1000, 1250, 1500, 250, 1500000, 22000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 012, NULL, 95, 0, 420, 'Y', 'N', 5.9, 1, 0, 0, 80, 0, 0, 0, 60, 0, 6, 1500000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1750),
--(00093, 'CREATIS P2 : LOCATAIRE', 0, 18, 0, 45, 800, 800, 1200, 1600, 200, 100000, 7650, 0, 'N', 'N', 'N', 'consommation', 'Y', 004, NULL, 75, 0, 144, 'N', 'Y', 9.06, 2.4, 5, 0, 100, 0, 0, 0, 60, 0, 2, 100000, 'locataire', 'Y', 'Y', 'Y', 0, 1800),
--(00094, 'Brookeo', 0, 0, 0, 45, 450, 700, 900, 1200, 300, 70000, 5000, 0, 'N', 'N', 'N', 'consommation', 'Y', 015, NULL, 85, 0, 144, 'Y', 'N', 6.65, 1, 5, 100, 100, 0, 0, 0, 60, 0, 3, 70000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1500),
--(00095, 'ALLEGIO 4 : EV < 70%', 75, 0, 70, 50, 650, 900, 1100, 1250, 150, 800000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 006, 01247528, 85, 1300, 300, 'Y', 'N', 6.45, 1.5, 0, 0, 80, 1500, 0, 0, 60, 0, 4, 800000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1400),
--(00096, 'ALLEGIO 5 : EV > 70%', 75, 0, 5000, 50, 650, 900, 1100, 1250, 150, 800000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 006, NULL, 85, 1300, 300, 'Y', 'N', 6.95, 1.5, 0, 0, 80, 1500, 0, 0, 60, 70, 5, 800000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1400),
--(00097, 'IMMOLEGIO :  LS2', 75, 0, 70, 45, 1000, 1300, 1500, 1700, 200, 800000, 10000, 85, 'N', 'N', 'N', 'hypothecaire', 'N', 006, 01247529, 85, 1300, 300, 'Y', 'N', 5.35, 1.5, 0, 0, 80, 1500, 0, 0, 90, 0, 6, 600000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1900),
--(00098, 'E5 1 : Fiché, 240M EN LCC', 0, 0, 100, 35, 850, 1000, 1250, 1500, 250, 400000, 35000, 65, 'Y', 'Y', 'N', 'hypothecaire', 'Y', 001, 01247524, 80, 0, 240, 'Y', 'N', 5.55, 1.5, 0, 0, 80, 1490, 20, 0, 60, 40, 26, 400000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1750),
--(00099, 'LOCATAIRE OU ACCEDANT -2ans TEAP 40%', 0, 18, 0, 40, 750, 900, 1100, 1300, 250, 100000, 20000, 0, 'N', 'N', 'N', 'consommation', 'Y', 005, NULL, 83, 0, 120, 'Y', 'N', 7.73, 1.5, 5, 100, 100, 0, 0, 0, 0, 0, 1, 100000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1550),
--(00100, '<60% IMMO EV 60% MAX', 70, 0, 60, 33, 900, 900, 1100, 1300, 200, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 008, 01247530, 86, 0, 240, 'Y', 'N', 3.8, 1.5, 0, 100, 85, 150000000000000, 3, 0, 60, 0, 1, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1500),
--(00101, '>60% IMMO EV 60% MAX', 70, 0, 100, 33, 900, 900, 1100, 1300, 200, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 008, 01247531, 86, 0, 240, 'Y', 'N', 3, 1.5, 0, 100, 85, 150000000000000, 3, 60, 100, 0, 4, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1500),
--(00102, '<60% IMMO EV 100% MAX 10PP MAX', 70, 0, 100, 33, 900, 900, 1100, 1300, 200, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 008, 01247532, 86, 0, 240, 'Y', 'N', 4.3, 1.5, 0, 100, 85, 150000000000000, 10, 0, 60, 0, 7, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1500),
--(00103, 'PREMIUM 4 : EV>60 360M LS2', 70, 0, 100, 33, 900, 900, 1100, 1300, 200, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 008, 01247533, 86, 0, 240, 'Y', 'N', 4.8, 1.5, 0, 0, 80, 150000000000000, 10, 60, 100, 0, 10, 1000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1500),
--(00104, 'EP+3 : EV 50% 300M EN LS2', 0, 0, 50, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 001, NULL, 85, 2500, 300, 'Y', 'N', 3.85, 1.5, 0, 0, 80, 700, 6, 60, 100, 0, 3, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00105, 'EP+4 : EV 50% 360M EN LS2', 0, 0, 50, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 001, NULL, 85, 2500, 360, 'Y', 'N', 4.85, 1.5, 0, 0, 80, 700, 6, 60, 100, 0, 4, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00106, 'EP 3 : EV 40%  300M EN LS2', 0, 0, 40, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 001, NULL, 85, 0, 300, 'Y', 'N', 3.85, 1.5, 0, 0, 80, 1490, 4, 60, 100, 0, 7, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00107, 'EP 4 : EV 40% 360M EN LCC', 0, 0, 40, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 001, NULL, 85, 0, 360, 'Y', 'N', 5, 1.5, 0, 0, 80, 1490, 4, 60, 100, 0, 8, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00108, 'E1 3 : EV 50% 300M  EN LS2', 0, 0, 50, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 001, NULL, 85, 0, 300, 'Y', 'N', 3.95, 1.5, 0, 0, 80, 1490, 6, 60, 100, 0, 11, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00109, 'E1 4 : EV 50% 360M  EN LS2', 0, 0, 50, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 001, NULL, 85, 0, 360, 'Y', 'N', 4.25, 1.5, 0, 0, 80, 1490, 6, 60, 100, 0, 12, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00110, 'E3 1 : EV 60-80% 300M LCC', 0, 0, 80, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 001, NULL, 85, 0, 300, 'Y', 'N', 5.45, 1.5, 0, 0, 80, 1490, 0, 0, 60, 0, 18, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00111, 'E3 2 :EV 60-80% 300M LS2', 0, 0, 80, 40, 750, 900, 1150, 1400, 250, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 001, NULL, 85, 0, 300, 'Y', 'N', 4.95, 1.5, 0, 0, 80, 1490, 0, 60, 100, 0, 19, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1650),
--(00112, 'E3.5 EV 60- 80%, 240M en LS2', 0, 0, 80, 40, 850, 1000, 1250, 1500, 210, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 001, NULL, 85, 0, 240, 'Y', 'N', 5.05, 1.5, 0, 0, 80, 1490, 0, 60, 100, 0, 20, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1710),
--(00113, 'E3.6 EV 60- 80%, 300M en LS2', 0, 0, 80, 40, 850, 1000, 1250, 1500, 210, 1000000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 001, NULL, 85, 0, 300, 'Y', 'N', 5.1, 1.5, 0, 0, 80, 1490, 0, 60, 100, 0, 21, 1000000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1710),
--(00114, 'E4 3 EV 80-100% 240M EN LS2', 0, 0, 100, 38, 850, 1000, 1250, 1500, 250, 400000, 35000, 70, 'N', 'N', 'N', 'hypothecaire', 'N', 001, NULL, 85, 0, 240, 'Y', 'N', 5.55, 1.5, 0, 0, 80, 1490, 20, 60, 100, 0, 24, 400000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1750),
--(00115, 'E4 4 EV 80-100% 300M EN LS2', 0, 0, 100, 38, 850, 1000, 1250, 1500, 250, 400000, 35000, 70, 'N', 'N', 'N', 'hypothecaire', 'N', 001, NULL, 85, 0, 300, 'Y', 'N', 5.1, 1.5, 0, 0, 80, 1490, 6, 60, 100, 0, 25, 400000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1750),
--(00116, 'E5 2 : Fiché, 240M LS2', 0, 0, 100, 35, 850, 1000, 1250, 1500, 250, 400000, 35000, 65, 'N', 'N', 'N', 'hypothecaire', 'Y', 001, NULL, 80, 0, 240, 'Y', 'N', 5.1, 1.5, 0, 0, 80, 1490, 20, 60, 100, 40, 27, 400000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1750),
--(00117, 'RC1 4 REVENU  1500 LS2', 0, 0, 40, 30, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, 1500, 300, 'Y', 'N', 4.3, 0, 0, 0, 80, 600, 100, 60, 100, 0, 4, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00118, 'RC1 5 REVENU 3000 LS2', 0, 0, 40, 36, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, 3000, 300, 'Y', 'N', 4.3, 0, 0, 0, 80, 600, 100, 60, 100, 0, 5, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00119, 'RC1 6 REVENU 4500 LS2', 0, 0, 40, 42, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, 4500, 300, 'Y', 'N', 4.3, 0, 0, 0, 80, 600, 100, 60, 100, 0, 6, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00120, 'RC2 4 REVENU  1500 LS2', 0, 0, 50, 30, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, 1500, 300, 'Y', 'N', 4.3, 0, 0, 0, 80, 850, 100, 60, 100, 0, 10, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00121, 'RC2 5 REVENU 3000 LS2', 0, 0, 50, 36, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, 3000, 300, 'Y', 'N', 4.3, 0, 0, 0, 80, 850, 100, 60, 100, 0, 11, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00122, 'RC2 6 REVENU 4500 LS2', 0, 0, 50, 42, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, 4500, 300, 'Y', 'N', 4.3, 0, 0, 0, 80, 850, 100, 60, 100, 0, 12, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00123, 'RC3 4 REVENU  1500 LS2', 0, 0, 60, 30, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, 1500, 300, 'Y', 'N', 4.3, 0, 0, 0, 80, 1000, 100, 60, 100, 0, 16, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00124, 'RC3 5 REVENU 3000 LS2', 0, 0, 60, 36, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, 3000, 300, 'Y', 'N', 4.3, 0, 0, 0, 80, 1000, 100, 60, 100, 0, 17, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00125, 'RC3 6 REVENU 4500 LS2', 0, 0, 60, 42, 550, 830, 1055, 1258, 182, 0, 30000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, 4500, 300, 'Y', 'N', 4.3, 0, 0, 0, 80, 1000, 100, 60, 100, 0, 18, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1440),
--(00126, 'RC4 4 EV>80%.REV 1500 LS2', 0, 0, 60, 30, 850, 1100, 1325, 1528, 182, 0, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, 1500, 300, 'Y', 'N', 4.3, 0, 0, 0, 80, 1250, 100, 60, 100, 0, 22, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1710),
--(00127, 'RC4 5 EV>80%.REV 3000 LS2', 0, 0, 60, 31.5, 850, 1100, 1325, 1528, 182, 0, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, 3000, 300, 'Y', 'N', 4.3, 0, 0, 0, 80, 1250, 100, 60, 100, 0, 23, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1710),
--(00128, 'RC4 6 EV>80%.REV 4500 LS2', 0, 0, 60, 33, 850, 1100, 1325, 1528, 182, 0, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, 4500, 300, 'Y', 'N', 4.3, 0, 0, 0, 80, 1250, 100, 60, 100, 0, 24, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1710),
--(00129, 'mini rac', 0, 0, 55, 40, 750, 1000, 1200, 1600, 400, 25000, 10000, 100, 'N', 'N', 'N', 'consommation', 'Y', 003, NULL, 80, 2500, 120, 'Y', 'N', 7.5, 0, 0, 100, 70, 0, 0, 0, 0, 0, 0, 25000, 'proprietaire', 'Y', 'N', 'N', 0, 2000),
--(00130, 'Classique 7', 0, 0, 0, 35, 1250, 1900, 2300, 2700, 250, 1500000, 22000, 90, 'N', 'N', 'N', 'hypothecaire', 'N', 012, NULL, 95, 0, 420, 'Y', 'N', 4.3, 1, 0, 0, 80, 0, 0, 60, 100, 0, 7, 1500000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 2950),
--(00131, 'PERFORMANCE EV 75%, REV 3000', 0, 0, 70, 40, 700, 900, 1050, 1400, 350, 0, 40000, 80, 'N', 'N', 'N', 'hypothecaire', 'N', 003, NULL, 84, 3000, 240, 'Y', 'N', 7.95, 3, 0, 0, 70, 0, 0, 0, 60, 0, 2, 0, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1750),
--(00132, 'CGI P4 Loc', 0, 18, 75, 40, 750, 1000, 1200, 1600, 400, 100000, 10000, 0, 'N', 'N', 'N', 'consommation', 'N', 003, NULL, 80, 1500, 120, 'N', 'N', 10.5, 0, 0, 70, 100, 0, 0, 0, 0, 0, 7, 70000, 'locataire', 'Y', 'N', 'N', 0, 2000),
--(00133, 'Ev 50 % rev 3000 PROPRIETAIRE', 0, 18, 50, 40, 700, 900, 1050, 1400, 350, 150000, 10000, 0, 'N', 'N', 'N', 'consommation', 'Y', 003, NULL, 80, 3000, 144, 'N', 'N', 8.3, 0, 0, 70, 100, 0, 0, 0, 0, 0, 2, 150000, 'proprietaire', 'Y', 'N', 'N', 0, 1750),
--(00134, 'Ev 70 % rev 3000 PROPRIETAIRE', 0, 18, 70, 40, 700, 900, 1050, 1400, 350, 150000, 10000, 0, 'N', 'N', 'N', 'consommation', 'Y', 003, NULL, 80, 3000, 144, 'N', 'N', 9.4, 0, 0, 70, 100, 0, 0, 0, 0, 0, 4, 150000, 'proprietaire', 'Y', 'N', 'N', 0, 1750),
--(00135, 'Ev 70 % rev 1200 PROPRIETAIRE', 0, 18, 85, 40, 700, 900, 1050, 1400, 350, 150000, 10000, 0, 'N', 'N', 'N', 'consommation', 'Y', 003, NULL, 80, 1300, 144, 'N', 'N', 10.5, 0, 0, 70, 100, 0, 0, 0, 0, 0, 6, 150000, 'proprietaire', 'Y', 'N', 'N', 0, 1750),
--(00136, 'CGI P4 Pro', 0, 18, 85, 40, 750, 1000, 1200, 1600, 400, 100000, 10000, 0, 'N', 'N', 'N', 'consommation', 'N', 003, NULL, 80, 1500, 120, 'N', 'N', 9.59, 0, 0, 100, 70, 0, 0, 0, 0, 0, 8, 70000, 'proprietaire', 'Y', 'N', 'N', 0, 2000),
--(00137, 'CGI P5 PRO 144 MOIS', 0, 18, 85, 40, 750, 1000, 1200, 1600, 400, 100000, 25000, 0, 'N', 'N', 'N', 'consommation', 'N', 003, NULL, 80, 1000, 144, 'N', 'N', 9.59, 0, 0, 100, 70, 0, 0, 0, 0, 0, 9, 70000, 'proprietaire', 'Y', 'N', 'N', 0, 2000),
--(00138, 'Ev 70 % rev 2000 PROPRIETAIRE', 75, 25, 70, 50, 650, 900, 1101, 1252, 150, 80000, 10000, 0, 'N', 'N', 'N', 'consommation', 'Y', 006, NULL, 84, 2000, 144, 'Y', 'N', 7.9, 1.5, 5, 0, 80, 0, 0, 0, 0, 0, 2, 80000, 'proprietaire', 'Y', 'Y', 'N', 0, 1402),
--(00139, 'EV sup 70% Locataire', 75, 25, 100, 50, 850, 1000, 1200, 1350, 150, 80000, 10000, 0, 'N', 'N', 'N', 'consommation', 'Y', 006, NULL, 84, 1300, 144, 'Y', 'N', 9, 1.5, 5, 0, 80, 0, 0, 0, 0, 0, 3, 80000, 'locataire', 'Y', 'Y', 'N', 0, 1500),
--(00140, 'EV sup 70% Proprietaire', 75, 25, 1000, 50, 650, 900, 1101, 1252, 150, 80000, 10000, 0, 'N', 'N', 'N', 'consommation', 'Y', 006, NULL, 84, 1300, 144, 'Y', 'N', 8.5, 1.5, 5, 0, 80, 0, 0, 0, 0, 0, 4, 80000, 'proprietaire', 'Y', 'Y', 'N', 0, 1402),
--(00141, 'CREATIS P3 LOC. REV 4000', 0, 18, 0, 45, 800, 800, 1200, 1600, 200, 100000, 7650, 0, 'N', 'N', 'N', 'consommation', 'Y', 004, NULL, 75, 4000, 144, 'N', 'Y', 8.4, 2.4, 5, 0, 100, 0, 0, 0, 60, 0, 3, 100000, 'locataire', 'Y', 'Y', 'Y', 0, 1800),
--(00142, 'PROPRIETAIRE 150 000€', 75, 25, 70, 45, 1000, 1300, 1500, 1700, 200, 150000, 80001, NULL, 'N', 'N', 'N', 'consommation', 'Y', 006, NULL, 84, 1300, 144, 'Y', 'N', 7, 1.5, 6, 100, 80, 1500, 0, 0, 0, 0, 5, 150000, 'proprietaire', 'Y', 'Y', 'N', 0, 1900),
--(00143, 'LOCATAIRE OU ACCEDANT -2ans TEAP 45%', 0, 18, 0, 45, 935, 1125, 1375, 1625, 312, 100000, 20000, 0, 'N', 'N', 'N', 'consommation', 'Y', 005, NULL, 83, 0, 144, 'Y', 'N', 7.73, 1.5, 5, 100, 100, 0, 0, 0, 0, 0, 3, 100000, 'proprietaire', 'Y', 'Y', 'N', 0, 1937),
--(00144, 'E6 1 : FICP IMMO LCC', 0, 0, 65, 35, 850, 1000, 1250, 1500, 250, 400000, 35000, 65, 'Y', 'Y', 'N', 'hypothecaire', 'Y', 001, NULL, 75, NULL, 240, 'Y', 'N', 5.55, 0, 0, 100, 100, 0, 10, 60, 100, 0, 28, 400000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1750),
--(00145, 'E6 2 : FICP IMMO LS2', 0, 0, 65, 35, 850, 1000, 1250, 1500, 250, 400000, 35000, 65, 'Y', 'Y', 'N', 'hypothecaire', 'Y', 001, NULL, 75, NULL, 240, 'Y', 'N', 5.1, 0, 0, 100, 100, 0, 10, 0, 60, 0, 29, 400000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1750),
--(00146, 'BNP Conso', 0, 18, 70, 45, 500, 600, 700, 800, 100, 80000, 0, 0, 'N', 'N', 'N', 'consommation', 'Y', 009, NULL, 81, 0, 120, 'Y', 'N', 8.1, 0, 0, 75, 100, 0, 0, 0, 60, 0, 0, 80000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 900),
--(00147, 'connu 240 mois - de 60 immo', 0, 0, 0, 45, 927, 1159, 1391, 1623, 232, 10000000000000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 009, NULL, 82, NULL, 240, 'Y', 'N', 4.7, 0, 0, 75, 80, 1000, 0, 0, 60, 0, 1, 1000000000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1855),
--(00148, 'connu 300 Immo>45%', 0, 0, 0, 42, 910, 910, 1120, 1330, 210, 10000000000000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, NULL, 300, 'Y', 'N', 5, 0, 0, 100, 80, 1000, 0, 45, 100, 0, 2, 1000000000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1540),
--(00149, 'connu 240 + de 60 immo', 0, 0, 0, 45, 927, 1159, 1391, 1623, 232, 10000000000000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 009, NULL, 82, NULL, 240, 'Y', 'N', 3.9, 0, 0, 75, 80, 1000, 0, 60, 100, 0, 3, 1000000000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1855),
--(00150, 'connu 300 LS2', 0, 0, 0, 42, 927, 1159, 1391, 1623, 232, 10000000000000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 009, NULL, 82, NULL, 300, 'Y', 'N', 4, 0, 0, 75, 80, 1000, 0, 60, 100, 0, 4, 1000000000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1855),
--(00151, 'Inconnu LCC 240M', 0, 0, 80, 45, 927, 1159, 1391, 1623, 232, 10000000000000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 009, NULL, 82, NULL, 240, 'Y', 'N', 4.7, 0, 0, 75, 80, 1000, 0, 0, 60, 0, 5, 1000000000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1855),
--(00152, 'Inconnu LS2 240M', 0, 0, 0, 42, 910, 910, 1120, 1330, 210, 10000000000000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'N', 009, NULL, 82, NULL, 300, 'Y', 'N', 4.2, 0, 0, 100, 80, 1000, 0, 45, 100, 0, 6, 1000000000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1540),
--(00153, 'inconnu 240 45', 0, 0, 80, 45, 927, 1159, 1391, 1623, 232, 10000000000000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 009, NULL, 82, NULL, 240, 'Y', 'N', 3.9, 0, 0, 75, 80, 1000, 0, 60, 100, 0, 7, 1000000000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1855),
--(00154, 'Inconnu LS2 300 MOIS', 0, 0, 80, 42, 927, 1159, 1391, 1623, 232, 10000000000000, 30000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 009, NULL, 82, NULL, 300, 'Y', 'N', 4, 0, 0, 75, 80, 1000, 0, 60, 100, 0, 8, 1000000000000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1855),
--(00155, 'Ev 70% rev 2000 LOCATAIRE', 75, 25, 70, 50, 850, 1000, 1200, 1350, 150, 80000, 10000, 0, 'N', 'N', 'N', 'consommation', 'Y', 006, NULL, 84, 2000, 144, 'Y', 'N', 8.6, 1.5, 5, 0, 80, 0, 0, 0, 0, 0, 1, 80000, 'locataire', 'Y', 'Y', 'N', 0, 1500),
--(00156, 'HYPOLIFT 240M TEAP40%', 0, 0, 90, 40, 750, 900, 1100, 1300, 250, 300000, 45000, 75, 'N', 'N', 'N', 'hypothecaire', 'Y', 005, NULL, 83, NULL, 240, 'Y', 'N', 6.96, 0, 0, 100, 20, 0, 0, 0, 60, 0, 1, 300000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1550),
--(00157, 'HYPOLIFT 300 M TEAP35%', 0, 0, 80, 35, 750, 900, 1100, 1300, 250, 300000, 45000, 75, 'N', 'N', 'N', 'hypothecaire', 'Y', 005, NULL, 83, NULL, 300, 'Y', 'N', 6.96, 0, 0, 100, 20, 0, 0, 0, 60, 0, 2, 300000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1550),
--(00158, 'HYPOLIFT 300 M TEAP40%', 0, 0, 80, 40, 1500, 1800, 2200, 2600, 500, 300000, 45000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 005, NULL, 83, NULL, 300, 'Y', 'N', 5.92, 0, 0, 100, 20, 0, 0, 0, 60, 0, 3, 300000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 3100),
--(00159, 'HYPOLIFT 300 M TEAP45%', 0, 0, 80, 45, 2250, 2700, 3300, 3900, 750, 300000, 45000, 85, 'N', 'N', 'N', 'hypothecaire', 'Y', 005, NULL, 83, NULL, 300, 'Y', 'N', 5.92, 0, 0, 100, 20, 0, 0, 0, 60, 0, 4, 300000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 4650),
--(00160, 'IMMOFLEX', 75, 0, 500, 45, 950, 1150, 1350, 1500, 200, 800000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 006, NULL, 79, 2000, 300, 'Y', 'N', 4.6, 0, 0, 100, 100, 0, 0, 60, 90, 0, 0, 800000, 'locataireproprietaire', 'Y', 'Y', 'N', 0, 1800),
--(00161, 'HYPO IMMO TEAP 38%', 0, 0, 100, 35, 930, 1200, 1450, 1700, 250, 1500000, 22000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 012, NULL, 95, 0, 420, 'Y', 'N', 4.95, 1, 1.9, 100, 80, 0, 0, 60, 100, 0, 5, 1500000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1950),
--(00162, 'HYPO IMMO TEAP 40%', 0, 0, 100, 40, 1130, 1400, 1650, 1900, 250, 1500000, 22000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 012, NULL, 95, 0, 420, 'Y', 'N', 4.95, 1, 1.9, 100, 80, 0, 0, 60, 100, 0, 6, 1500000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 2150),
--(00163, 'HYPO IMMO First Révisable', 0, 0, 100, 40, 1250, 1900, 2300, 2700, 400, 1500000, 22000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 012, NULL, 95, 0, 420, 'Y', 'N', 4.95, 1, 1.9, 100, 80, 0, 0, 60, 100, 0, 7, 1500000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 3100),
--(00164, 'PROFIL PRIME', 0, 18, 500, 45, 710, 710, 1065, 1420, 355, 150000, 7650, 80, 'N', 'N', 'N', 'consommation', 'Y', 004, NULL, 75, 3000, 144, 'Y', 'N', 7.3, 0, 0, 100, 100, 0, 0, 0, 100, 0, 4, 150000, 'proprietaire', 'Y', 'Y', 'Y', 0, 1775),
--(00165, 'ALLEGIOFLEX REVISABLE', 75, 0, 500, 35, 950, 1150, 1150, 1500, 200, 800000, 30000, 80, 'N', 'N', 'N', 'hypothecaire', 'Y', 006, NULL, 80, 2000, 300, 'Y', 'N', 4.85, 1.5, 0, 100, 80, 1500, 0, 0, 60, 0, 6, 800000, 'locataireproprietaire', 'Y', 'Y', 'Y', 0, 1800);
--
--UPDATE `financial_produit_banque` SET `id_remarque` = NULL;
--
--
--
--INSERT INTO `financial_type_calcul_loyer_1` (`id_calcul_loyer`, `loyer`) VALUES
--(00006, 300),
--(00008, 305),
--(00009, 300),
--(00010, 300),
--(00011, 350),
--(00012, 305),
--(00013, 305),
--(00014, 305),
--(00015, 300),
--(00016, 400),
--(00017, 305);
--
--INSERT INTO `financial_type_calcul_loyer_2` (`id_calcul_loyer`, `loyer`, `loyer_sup_3_pers`) VALUES
--(00004, 450, 550),
--(00008, 450, 550),
--(00009, 450, 550);
--
--INSERT INTO `financial_type_calcul_loyer_3` (`id_calcul_loyer`, `loyer_une_personne`, `loyer_couple`, `loyer_personne_sup`) VALUES
--(00003, 400, 500, 100);
--
--INSERT INTO `financial_type_calcul_loyer_4` (`id_calcul_loyer`, `pourcentage`, `loyer_mini`) VALUES
--(00003, 20, 0),
--(00006, 25, 0),
--(00007, 20, 0),
--(00008, 20, 400),
--(00009, 20, 400),
--(00010, 20, 400),
--(00011, 20, 400),
--(00012, 20, 400),
--(00013, 20, 400),
--(00014, 20, 0);
--
--
--INSERT INTO `financial_link_type_calcul_loyer` (`id_produit_banque`, `type_calcul`, `id_calcul_loyer`) VALUES
--(00010, 4, 8),
--(00011, 1, 6),
--(00012, 4, 3),
--(00014, 1, 8),
--(00023, 1, 9),
--(00024, 3, 3),
--(00026, 4, 6),
--(00081, 4, 10),
--(00082, 4, 12),
--(00093, 1, 10),
--(00094, 1, 11),
--(00099, 4, 7),
--(00132, 2, 4),
--(00133, 4, 9),
--(00134, 4, 11),
--(00135, 4, 13),
--(00136, 2, 8),
--(00137, 2, 9),
--(00138, 1, 12),
--(00139, 1, 13),
--(00140, 1, 14),
--(00141, 1, 15),
--(00143, 4, 14),
--(00146, 1, 16),
--(00155, 1, 17);
--
--SET FOREIGN_KEY_CHECKS = 1;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 4
--WHERE `id_produit_banque` = 31;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 4.5
--WHERE `id_produit_banque` = 32;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 3.75
--WHERE `id_produit_banque` = 108;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 4.15
--WHERE `id_produit_banque` = 109;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 4.55
--WHERE `id_produit_banque` = 33;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 4.3
--WHERE `id_produit_banque` = 34;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 5
--WHERE `id_produit_banque` = 35;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 4.75
--WHERE `id_produit_banque` = 36;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 5
--WHERE `id_produit_banque` = 110;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 4.75
--WHERE `id_produit_banque` = 111;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 5.3
--WHERE `id_produit_banque` = 37;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 4.95
--WHERE `id_produit_banque` = 38;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 5.3
--WHERE `id_produit_banque` = 98;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 4.95
--WHERE `id_produit_banque` = 116;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 5.3
--WHERE `id_produit_banque` = 144;
--
--UPDATE `financial_produit_banque` 
--SET taux_nominal = 4.95
--WHERE `id_produit_banque` = 145;

################# UPDATE SQL REQUESTS - 2 -> 2.0.1 #################

--INSERT INTO financial_parametre(`id_local` ,`variable` ,`valeur` ,`type`)VALUES(18,'ALERT_NB_JOUR_DATE_PREVISIBLE_FRIGO',7,'Int');

################# UPDATE SQL REQUESTS - 1.16.5 -> 1.16.6 #################

--
--INSERT INTO financial_parametre(`id_local` ,`variable` ,`valeur` ,`type`)VALUES(null,'HIB_LOC','6','Float');
--INSERT INTO financial_parametre(`id_local` ,`variable` ,`valeur` ,`type`)VALUES(null,'HIB_PRO','8','Float');


--INSERT INTO financial_parametre(`id_local` ,`variable` ,`valeur` ,`type`)VALUES(18,'AUTORISE_ENVOI_MAIL_AUTO_RELATION_CLIENT','false','Boolean');

################# UPDATE SQL REQUESTS - 1.16.4 -> 1.16.5 #################

--INSERT INTO financial_parametre SET variable ='APP_LOGO', valeur ='', type = 'String';
--INSERT INTO financial_parametre SET variable ='APP_LOGO_TITLE', valeur ='', type = 'String';
--
--##METZ
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(4,'PARAM_SERSON_ENTREPRISE','SERSON CREDIT','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(4,'PARAM_SERSON_ADRESSE','12 rue des tanneurs','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(4,'PARAM_SERSON_CP_VILLE','57000 Metz','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(4,'PARAM_SERSON_INFO_SARL','SARL au capital de 5000','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(4,'PARAM_SERSON_RCS','R.C.S Metz 522 129 931','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(4,'PARAM_SERSON_APE','APE 6619B','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(4,'PARAM_SERSON_ORIAS','Inscription ORIAS N¬∞07 027 892','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(4,'PARAM_SERSON_MAIL_CONTACT','service-clients@credit-libra.fr','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(4,'TEMPLATE_LETTRE_INFORMATION',null,'File');
--
--##NANCY
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(5,'PARAM_SERSON_ENTREPRISE','SERSON CREDIT','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(5,'PARAM_SERSON_ADRESSE','12 rue des tanneurs','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(5,'PARAM_SERSON_CP_VILLE','57000 Metz','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(5,'PARAM_SERSON_INFO_SARL','SARL au capital de 5000','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(5,'PARAM_SERSON_RCS','R.C.S Metz 522 129 931','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(5,'PARAM_SERSON_APE','APE 6619B','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(5,'PARAM_SERSON_ORIAS','Inscription ORIAS N¬∞07 027 892','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(5,'PARAM_SERSON_MAIL_CONTACT','service-clients@credit-libra.fr','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(5,'TEMPLATE_LETTRE_INFORMATION',null,'File');
--
--##BESANCON
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(6,'PARAM_SERSON_ENTREPRISE','SERSON CREDIT','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(6,'PARAM_SERSON_ADRESSE','12 rue des tanneurs','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(6,'PARAM_SERSON_CP_VILLE','57000 Metz','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(6,'PARAM_SERSON_INFO_SARL','SARL au capital de 5000','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(6,'PARAM_SERSON_RCS','R.C.S Metz 522 129 931','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(6,'PARAM_SERSON_APE','APE 6619B','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(6,'PARAM_SERSON_ORIAS','Inscription ORIAS N¬∞07 027 892','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(6,'PARAM_SERSON_MAIL_CONTACT','service-clients@credit-libra.fr','String');
--INSERT INTO financial_parametre_agence (`id_local` ,`variable` ,`valeur` ,`type`)VALUES(6,'TEMPLATE_LETTRE_INFORMATION',null,'File');
--
--##LILLE
--INSERT INTO financial_parametre_agence 

################# UPDATE SQL REQUESTS - 1.15.2 -> 1.16.0 #################

--INSERT INTO financial_parametre (id_local,variable,valeur,type)
--(SELECT id_local,'ENABLE_TELETRANSFERT','false','Boolean' FROM financial_local WHERE id_local = id_local_direction);

################# UPDATE SQL REQUESTS - 1.15.0 -> 1.15.1 #################

--ALTER TABLE `financial_dossier_refinancement_commercial` ADD id_mise_disposition_att MEDIUMINT UNSIGNED ZEROFILL NULL;
--ALTER TABLE `financial_dossier_refinancement_commercial` ADD FOREIGN KEY ( `id_mise_disposition_att` ) REFERENCES  `financial_modification` (`id_modification`) ON DELETE CASCADE ;
--
--INSERT INTO financial_parametre (variable,valeur,type) VALUES('MISE_DISPOSITION_ATT','true','boolean');

################# UPDATE SQL REQUESTS - 1.13.4 -> 1.13.5 #################

--INSERT INTO financial_parametre SET variable ='PACKBFI_MAX_ZONE_2', valeur ='100', type = 'int';
--
--UPDATE financial_parametre SET valeur = '50' WHERE variable = 'PACKBFI_MAX_ZONE';

################# UPDATE SQL REQUESTS - 1.10.1 -> 1.11.0 #################
--
--INSERT INTO financial_parametre SET variable ='PARAM_CNIL_NUMERO_DECLARANT', valeur ='1313764', type = 'String';

################# UPDATE SQL REQUESTS - 1.10.1 -> 1.11.0 #################

--INSERT INTO `financial_parametre` VALUES (NULL, 'TEMPLATE_POSITION_HAUTE_MANDAT', '30', 'Int');
--UPDATE `financial_parametre` SET `valeur` = '55' WHERE `financial_parametre`.`id_local` IS NULL AND `variable`  = 'TEMPLATE_POSITION_HAUTE'  LIMIT 1 ;

################# UPDATE SQL REQUESTS - 1.8.0 -> 1.9.0 #################

--ALTER TABLE financial_dossier_refinancement_commercial DROP FOREIGN KEY `financial_dossier_refinancement_commercial_ibfk_19`;
--ALTER TABLE financial_dossier_refinancement_commercial ADD FOREIGN KEY (id_mandat_non_signe) REFERENCES financial_refus_mandat(id_refus_mandat);
--
--INSERT INTO financial_parametre(id_local,variable,valeur,type)
--(SELECT id_local,'PASSERELLE','','String'
--FROM financial_local
--WHERE id_local=id_local_direction);

################# UPDATE SQL REQUESTS - 1.4.0 -> 1.8.0 #################
--
--INSERT INTO financial_parametre(id_local,variable,valeur,type)(
--SELECT id_local,"PARAM_ADRESSE_EXTRANET","https:--prescription.credit-libra.fr","String" FROM financial_local WHERE id_local = id_local_direction);

################# UPDATE SQL REQUESTS - 1.3.0 -> 1.4.0 #################

--UPDATE `financial_parametre` SET `valeur` = '55' WHERE variable = 'TEMPLATE_POSITION_HAUTE'  LIMIT 1 ;
--UPDATE `financial_parametre` SET `valeur` = '30' WHERE variable = 'TEMPLATE_MARGE_GAUCHE'  LIMIT 1 ;
--UPDATE `financial_parametre` SET `valeur` = '150' WHERE variable = 'TEMPLATE_LARGEUR_PARAGRAPHE'  LIMIT 1 ;
--UPDATE `financial_parametre` SET `valeur` = '240' WHERE variable = 'TEMPLATE_POSITION_BASSE'  LIMIT 1 ;
--UPDATE `financial_parametre` SET `valeur` = '259' WHERE variable = 'TEMPLATE_POSITION_LIBELLE_PAGE'  LIMIT 1 ;
--UPDATE `financial_parametre` SET `valeur` = '10' WHERE variable = 'TEMPLATE_MARGE_GAUCHE_PARAPHE'  LIMIT 1 ;
--UPDATE `financial_parametre` SET `valeur` = '200' WHERE variable = 'TEMPLATE_MARGE_GAUCHE_PAGE_NUMBER'  LIMIT 1 ;



################# UPDATE SQL REQUESTS - 1.0.0 -> 1.1.0 #################

--INSERT INTO financial_parametre(id_local,variable,valeur,type)
--(SELECT id_local,'TELETRANSFERT_CFCAL_CODE_REMETTANT',NULL,'Int'
--FROM financial_local
--WHERE id_local=id_local_direction);

################# UPDATE SQL REQUESTS - 0.12.0 -> 1.0.0 #################

--INSERT INTO financial_parametre(id_local,variable,valeur,type)VALUES(null,'PREFIXE_DOCUMENT','CL','String');

################# UPDATE SQL REQUESTS - 0.11.0 -> 0.12.0 #################

--DELETE FROM financial_parametre WHERE variable = 'PACKBFI_ANTICIPATION_ZONE';

################# UPDATE SQL REQUESTS - 0.10.0 -> 0.11.0 #################
--DELETE FROM financial_parametre WHERE variable = 'PACKBFI_PARTENAIRE_TRANSFERT_SUPPORT';
--
--INSERT INTO `financial_parametre` (
--`id_local` ,
--`variable` ,
--`valeur` ,
--`type`
--)
--VALUES (
--NULL , 'PACKBFI_TRANSFERT_PARTENAIRE', 'false', 'Boolean'
--),(
--NULL , 'PACKBFI_LOCAL_DEFAULT', '4', 'Int'
--),(
--NULL , 'PARTENAIRE_TRANSFERT_SUPPORT', '24', 'Int'
--);
--
--UPDATE  financial_parametre SET  valeur =  'http:--www.credit-libra.fr/img/structure/logo.png' WHERE variable =  'PARAM_LOGO';
--
--INSERT INTO `financial_support` (`id_support`, `id_local`, `libelle`, `actif`, `type_support`) VALUES (29, NULL, 'INTERNET MR C', 'N', 'Internet');
--UPDATE  `financial_support` SET  actif =  'N' WHERE id_support =00003;
################# UPDATE SQL REQUESTS - 0.9.0 -> 0.10.0 #################

--
--INSERT INTO `financial_parametre` (
--`id_local` ,
--`variable` ,
--`valeur` ,
--`type`
--)
--VALUES (
--NULL , 'ALERT_NB_JOUR_PRECONISATION_ATT', '7', 'Int'
--);



################# UPDATE SQL REQUESTS - 0.8.0 -> 0.9.0 #################

--
--DELETE FROM `financial_parametre` 
--WHERE  id_local<>4 AND variable IN 
--('ALARME_ADP_PRO',
--'ALARME_LOC_ENCAISSEMENT',
--'ALARME_PRO_ENCAISSEMENT',
--'ALERT_NB_JOUR_DATE_PREVISIBLE_FRIGO_AT',
--'ALERT_NB_JOUR_PRECONISATION',
--'AZUR_SERVEUR_DISCOFONE_NB_RAPPEL_P1_P3',
--'DUREE_LOC_DEFAULT',
--'DUREE_MENSUALITE_LOCATAIRE',
--'DUREE_PRO_DEFAULT',
--'EV_LOC_MAX',
--'EV_LOC_MIN',
--'LETTRE_REFUS_DELAI_MAX',
--'LOYER_FICTIF',
--'MONITORING_MANAGER_RDV_JOUR_MAX',
--'PRESCRIPTEUR_LASTVISITE_ALERT_DAY',
--'PRIORITE_P1_CRD',
--'PRIORITE_P1_EV',
--'PRIORITE_P2_CRD',
--'PRIORITE_P2_EV',
--'PRIORITE_P4_LOC_EV',
--'PRIORITE_P4_PRO_CRD',
--'PRIORITE_P4_PRO_EV',
--'TAUX_HONORAIRE_PRODUIT_LOCATAIRE',
--'TAUX_HONORAIRE_PRODUIT_PROPRIETAIRE',
--'TAUX_OUTIL_SIMULATION',
--'TELETRANSFERT_CGI',
--'TELETRANSFERT_GE',
--'TELETRANSFERT_SYGMA',
--'TEMPS_COTISATION_RETRAITE',
--'WARNING_BFI',
--'WARNING_BFSPROPOSITION',
--'WARNING_PRESCRIPTEUR_VISITE');
--
--
--DELETE FROM `financial_parametre` 
--WHERE id_local<>4 AND  variable IN 
--('ALLOC_2_ENFANTS',
--'ALLOC_3_ENFANTS',
--'ALLOC_SUP_ENFANTS',
--'COMMISSION_COURTAGE_POURCENTAGE_HYPO_1',
--'COMMISSION_COURTAGE_POURCENTAGE_HYPO_1_MAX',
--'COMMISSION_COURTAGE_POURCENTAGE_HYPO_1_MIN',
--'COMMISSION_COURTAGE_POURCENTAGE_HYPO_2',
--'COMMISSION_COURTAGE_POURCENTAGE_HYPO_2_MIN',
--'COMMISSION_COURTAGE_POURCENTAGE_PERSO',
--'DUREE_LOC_MAX',
--'DUREE_LOC_MIN',
--'DUREE_PRO_MAX',
--'DUREE_PRO_MIN',
--'EV_PRO_MAX',
--'EV_PRO_MIN',
--'MONTANT_LOC_MIN',
--'MONTANT_PRO_MAX',
--'MONTANT_PRO_MIN',
--'TAUX_CALCULETTEFINANCIERE',
--'TAUX_DE_BANQUE',
--'TAUX_NOMINAL_LOCATAIRE',
--'TAUX_NOMINAL_PRODUIT',
--'TAUX_TEG_PROPOSITION_COMMERCIALE',
--'UPLOAD_MAX_SIZE');
--
--DELETE FROM `financial_parametre` 
--WHERE id_local<>4 AND  variable IN 
--('AZUR_SERVEUR_DISCOFONE_DB_LOGIN',
--'AZUR_SERVEUR_DISCOFONE_DB_NAME',
--'AZUR_SERVEUR_DISCOFONE_DB_PASS',
--'AZUR_SERVEUR_DISCOFONE_DB_PATH',
--'CF100_TITRE',
--'LIST_DEPT_RDV_DOM',
--'LIST_DEPT_RDV_TEL',
--'PARAM_LOGO',
--'PARAM_SITE',
--'TELETRANSFERT_CGI_MDP_APPORTEUR',
--'TELETRANSFERT_CGI_NUM_APPORTEUR',
--'TELETRANSFERT_SYGMA_NUM_CONTREMARQUE');
--
--DELETE FROM `financial_parametre` 
--WHERE id_local<>4 AND  variable IN 
--('AUTORISE_ENVOI_SYSTEME_TELETRANSMIS',
--'TEMPLATE_PRE_ETUDE');
--
--
--UPDATE`financial_parametre` SET id_local = NULL 
--WHERE variable IN 
--('ALARME_ADP_PRO',
--'ALARME_LOC_ENCAISSEMENT',
--'ALARME_PRO_ENCAISSEMENT',
--'ALERT_NB_JOUR_DATE_PREVISIBLE_FRIGO_AT',
--'ALERT_NB_JOUR_PRECONISATION',
--'AZUR_SERVEUR_DISCOFONE_NB_RAPPEL_P1_P3',
--'DUREE_LOC_DEFAULT',
--'DUREE_MENSUALITE_LOCATAIRE',
--'DUREE_PRO_DEFAULT',
--'EV_LOC_MAX',
--'EV_LOC_MIN',
--'LETTRE_REFUS_DELAI_MAX',
--'LOYER_FICTIF',
--'MONITORING_MANAGER_RDV_JOUR_MAX',
--'PRESCRIPTEUR_LASTVISITE_ALERT_DAY',
--'PRIORITE_P1_CRD',
--'PRIORITE_P1_EV',
--'PRIORITE_P2_CRD',
--'PRIORITE_P2_EV',
--'PRIORITE_P4_LOC_EV',
--'PRIORITE_P4_PRO_CRD',
--'PRIORITE_P4_PRO_EV',
--'TAUX_HONORAIRE_PRODUIT_LOCATAIRE',
--'TAUX_HONORAIRE_PRODUIT_PROPRIETAIRE',
--'TAUX_OUTIL_SIMULATION',
--'TELETRANSFERT_CGI',
--'TELETRANSFERT_GE',
--'TELETRANSFERT_SYGMA',
--'TEMPS_COTISATION_RETRAITE',
--'WARNING_BFI',
--'WARNING_BFSPROPOSITION',
--'WARNING_PRESCRIPTEUR_VISITE');
--
--UPDATE`financial_parametre` SET id_local = NULL 
--WHERE variable IN 
--('ALLOC_2_ENFANTS',
--'ALLOC_3_ENFANTS',
--'ALLOC_SUP_ENFANTS',
--'COMMISSION_COURTAGE_POURCENTAGE_HYPO_1',
--'COMMISSION_COURTAGE_POURCENTAGE_HYPO_1_MAX',
--'COMMISSION_COURTAGE_POURCENTAGE_HYPO_1_MIN',
--'COMMISSION_COURTAGE_POURCENTAGE_HYPO_2',
--'COMMISSION_COURTAGE_POURCENTAGE_HYPO_2_MIN',
--'COMMISSION_COURTAGE_POURCENTAGE_PERSO',
--'DUREE_LOC_MAX',
--'DUREE_LOC_MIN',
--'DUREE_PRO_MAX',
--'DUREE_PRO_MIN',
--'EV_PRO_MAX',
--'EV_PRO_MIN',
--'MONTANT_LOC_MIN',
--'MONTANT_PRO_MAX',
--'MONTANT_PRO_MIN',
--'TAUX_CALCULETTEFINANCIERE',
--'TAUX_DE_BANQUE',
--'TAUX_NOMINAL_LOCATAIRE',
--'TAUX_NOMINAL_PRODUIT',
--'TAUX_TEG_PROPOSITION_COMMERCIALE',
--'UPLOAD_MAX_SIZE');
--
--UPDATE`financial_parametre` SET id_local = NULL 
--WHERE variable IN 
--('AZUR_SERVEUR_DISCOFONE_DB_LOGIN',
--'AZUR_SERVEUR_DISCOFONE_DB_NAME',
--'AZUR_SERVEUR_DISCOFONE_DB_PASS',
--'AZUR_SERVEUR_DISCOFONE_DB_PATH',
--'CF100_TITRE',
--'LIST_DEPT_RDV_DOM',
--'LIST_DEPT_RDV_TEL',
--'PARAM_LOGO',
--'PARAM_SITE',
--'TELETRANSFERT_CGI_MDP_APPORTEUR',
--'TELETRANSFERT_CGI_NUM_APPORTEUR',
--'TELETRANSFERT_SYGMA_NUM_CONTREMARQUE');
--
--
--UPDATE`financial_parametre` SET id_local = NULL 
--WHERE variable IN 
--('AUTORISE_ENVOI_SYSTEME_TELETRANSMIS',
--'TEMPLATE_PRE_ETUDE');
--
--INSERT INTO `financial_parametre` (
--`id_local` ,
--`variable` ,
--`valeur` ,
--`type`
--)
--VALUES (
--NULL , 'PREFIX_NUM_DOSSIER', 'DCL', 'String'
--), (
--NULL , 'PREFIX_NUM_CLIENT', 'CCL', 'String'
--), (
--NULL , 'PARAM_ENTREPRISE', 'Credit Libra', 'String'
--);
--
--DELETE FROM financial_parametre WHERE variable ="PACKBFI_MAX_ZONE";
--DELETE FROM financial_parametre WHERE variable ="PACKBFI_QUOTA_ANTICIPATION";
--DELETE FROM financial_parametre WHERE variable ="PACKBFI_PRIX_UNITAIRE_HT";
--DELETE FROM financial_parametre WHERE variable ="PACKBFI_URL_VALIDATION";
--DELETE FROM financial_parametre WHERE variable ="PACKBFI_VALIDATION_TIMEOUT";
--DELETE FROM financial_parametre WHERE variable ="PACKBFI_LOCAL_TEMP_VALIDATION";
--DELETE FROM financial_parametre WHERE variable ="PACKBFI_URL_PARTENAIRE_TRANSFERT";
--DELETE FROM financial_parametre WHERE variable ="PACKBFI_PARTENAIRE_TRANSFERT_SUPPORT";
--DELETE FROM financial_parametre WHERE variable ="PACKBFI_CNF_PARTENAIRE_TRANSFERT";
--DELETE FROM financial_parametre WHERE variable ="PACKBFI_ANTICIPATION";
--DELETE FROM financial_parametre WHERE variable ="PACKBFI_ANTICIPATION_ZONE";
--DELETE FROM financial_parametre WHERE valeur= "PACKBFI_LOCAL_TEMP_VALIDATION";
--
--INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_MAX_ZONE','200','INT');
--INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_PAS_ZONE','20','INT');
--INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_PRIX_UNITAIRE_HT','25','FLOAT');
--INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_URL_VALIDATION','v_simu','String');
--INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_VALIDATION_TIMEOUT','60','INT');
--INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_LOCAL_TEMP_VALIDATION','7','INT');
--INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_URL_PARTENAIRE_TRANSFERT','http:--financial.centralfinances.fr/Services/Service_Financial.php','String');
--INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_CNF_PARTENAIRE_TRANSFERT','centralfinances.fr','String');
--INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_PARTENAIRE_TRANSFERT_SUPPORT','24','INT');
--
--INSERT INTO financial_parametre(id_local,variable,valeur,type) (SELECT id_local,'PACKBFI_QUOTA_ANTICIPATION','30','INT' from financial_local WHERE id_local = id_local_direction);
--INSERT INTO financial_parametre(id_local,variable,valeur,type) (SELECT id_local,'PACKBFI_ANTICIPATION','false','BOOLEAN' from financial_local WHERE id_local = id_local_direction);
--INSERT INTO financial_parametre(id_local,variable,valeur,type) (SELECT id_local,'PACKBFI_ANTICIPATION_ZONE','false','BOOLEAN' from financial_local WHERE id_local = id_local_direction);
--
--INSERT INTO `financial_local` (
--`id_local` ,
--`id_local_direction` ,
--`type_local` ,
--`tel1` ,
--`tel2` ,
--`fax` ,
--`mail` ,
--`adresse1_local` ,
--`adresse2_local` ,
--`cp_local` ,
--`ville_local` ,
--`pays_local` ,
--`commentaire` ,
--`actif` ,
--`adresse1_postal` ,
--`adresse2_postal` ,
--`cp_postal` ,
--`ville_postal` ,
--`pays_postal`
--)
--VALUES (
--'7',  '00001',  'local_administratif',  '0000000000', NULL , NULL , NULL ,  '1 Rue de Lorraine',  'ZAC Croix St Nicolas',  '54840',  'GONDREVILLE',  'FRANCE', 'Local fictif pour le stockage des dossiers en cours d''acheminement des BFI vers le r√î√∏Œ©seau d''agence',  'N', NULL , NULL , NULL , NULL , NULL
--);
--
--UPDATE  `financial_parametre` SET  `valeur` =  'http:--www.credit-libra.fr/img/structure/logo.png' WHERE  `financial_parametre`.`id_local` IS NULL AND  `financial_parametre`.`variable` = 'PARAM_LOGO' AND  `financial_parametre`.`valeur` IS NULL AND  `financial_parametre`.`type` =  'String' LIMIT 1 ;
--
--INSERT INTO  `financial_support` (
--`id_support` ,
--`id_local` ,
--`libelle` ,
--`actif` ,
--`type_support`
--)
--VALUES (
--'59', NULL ,  'INTERNET MR C',  'N',  'Internet'
--);

################# UPDATE SQL REQUESTS - 0.7.0 -> 0.8.0 #################

--SET FOREIGN_KEY_CHECKS = 0;
--
--ALTER TABLE financial_ville DROP FOREIGN KEY financial_ville_ibfk_1;
--ALTER TABLE financial_ville ADD FOREIGN KEY (`id_departement`) REFERENCES `financial_departement` (`id_departement`) ON DELETE CASCADE;
--
--ALTER TABLE financial_departement DROP FOREIGN KEY financial_departement_ibfk_1√î√∏Œ©;
--ALTER TABLE financial_departement ADD FOREIGN KEY (`id_region`) REFERENCES `financial_region` (`id_region`) ON DELETE CASCADE;
--
--ALTER TABLE financial_region DROP FOREIGN KEY financial_region_ibfk_1√î√∏Œ©;
--ALTER TABLE financial_region ADD FOREIGN KEY (`id_pays`) REFERENCES `financial_pays` (`id_pays`) ON DELETE CASCADE;
--
--INSERT INTO financial_parametre VALUES(1,'PACKBFI_PRIX_UNITAIRE_HT','25','FLOAT');
--INSERT INTO financial_parametre VALUES(1,'PACKBFI_URL_VALIDATION','v_simu','String');
--INSERT INTO financial_parametre VALUES(1,'PACKBFI_VALIDATION_TIMEOUT','60','INT');
--INSERT INTO financial_parametre VALUES(1,'PACKBFI_LOCAL_TEMP_VALIDATION','7','INT');
--
--
--INSERT INTO financial_parametre(id_local,variable,valeur,type)
--(SELECT id_local,'PACKBFI_QUOTA_ANTICIPATION','30','INT'
--FROM financial_local
--WHERE id_local=id_local_direction);
--
--INSERT INTO financial_parametre(id_local,variable,valeur,type)
--(SELECT id_local,'PACKBFI_URL_VALIDATION','v_simu','String'
--FROM financial_local
--WHERE id_local=id_local_direction);
--
--INSERT INTO financial_parametre(id_local,variable,valeur,type)
--(SELECT id_local,'PACKBFI_PRIX_UNITAIRE_HT','25','INT'
--FROM financial_local
--WHERE id_local=id_local_direction);
--
--INSERT INTO financial_parametre(id_local,variable,valeur,type)
--(SELECT id_local,'PACKBFI_VALIDATION_TIMEOUT','60','INT'
--FROM financial_local
--WHERE id_local=id_local_direction);
--
--INSERT INTO financial_parametre(id_local,variable,valeur,type)
--(SELECT id_local,'PACKBFI_LOCAL_TEMP_VALIDATION','7','INT'
--FROM financial_local
--WHERE id_local=id_local_direction);
--
--INSERT INTO financial_parametre(id_local,variable,valeur,type)
--(SELECT id_local,'PACKBFI_URL_PARTENAIRE_TRANSFERT','http:--financial.centralfinances.fr/Services/Service_Financial.php','String'
--FROM financial_local
--WHERE id_local=id_local_direction);
--
--INSERT INTO financial_parametre(id_local,variable,valeur,type)
--(SELECT id_local,'PACKBFI_CNF_PARTENAIRE_TRANSFERT','centralfinances.fr','String'
--FROM financial_local
--WHERE id_local=id_local_direction);
--
--INSERT INTO financial_parametre(id_local,variable,valeur,type)
--(SELECT id_local,'PACKBFI_PARTENAIRE_TRANSFERT_SUPPORT','24','INT'
--FROM financial_local
--WHERE id_local=id_local_direction);
----
---- Structure de la table `financial_gestion_bfi`
----
--DROP TABLE IF EXISTS financial_gestion_bfi;
--CREATE TABLE IF NOT EXISTS `financial_gestion_bfi` (
--`id_local` smallint UNSIGNED zerofill NOT NULL,
--`date_commande`date NOT NULL,
--`bfi_commandes` smallint UNSIGNED zerofill NOT NULL,
--`bfi_consommes` smallint UNSIGNED zerofill NOT NULL,
--`bfi_anticipation` SMALLINT UNSIGNED NOT NULL,
--`bfi_wait` SMALLINT UNSIGNED NOT NULL,
--PRIMARY KEY (`id_local`, `date_commande`),
--FOREIGN KEY (`id_local`) references financial_local(id_local) ON DELETE CASCADE)
--ENGINE = InnoDB AUTO_INCREMENt=000;
--
--
--SET FOREIGN_KEY_CHECKS = 1;

################# UPDATE SQL REQUESTS - 0.4.0 -> 0.5.0 #################

--UPDATE financial_region SET id_pays = 2 WHERE id_pays=1;
--
--DELETE FROM financial_pays WHERE id_pays = 1 LIMIT 1;

################# UPDATE SQL REQUESTS - 0.3.0 -> 0.4.0 #################

################# UPDATE SQL REQUESTS - 0.2.0 -> 0.3.0 #################

--INSERT INTO financial_parametre VALUES(1,'PACKBFI_ANTICIPATION','false','BOOLEAN');
--INSERT INTO financial_parametre VALUES(1,'PACKBFI_ANTICIPATION_ZONE','false','BOOLEAN');
--INSERT INTO financial_parametre VALUES(1,'PACKBFI_MAX_ZONE','200','INT');
--INSERT INTO financial_parametre VALUES(1,'PACKBFI_PAS_ZONE','20','INT');

################# UPDATE SQL REQUESTS - 0.1.0 -> 0.2.0 #################
--
--CREATE TABLE IF NOT EXISTS `financial_gestion_bfi` (
--`id_local` smallint unsigned zerofill NOT NULL,
--`date_commande`date NOT NULL,
--`bfi_commandes` smallint unsigned zerofill NOT NULL,
--`bfi_consommes` smallint unsigned zerofill NOT NULL,
--PRIMARY KEY (`id_local`, `date_commande`),
--FOREIGN KEY (`id_local`) references financial_local(id_local))
--ENGINE = InnoDB AUTO_INCREMENt=000;
--
--REPLACE INTO financial_parametre(id_local,variable,valeur,type) VALUES
--(1,'PACKBFI_ANTICIPATION','false','Boolean'),
--(1,'PACKBFI_ANTICIPATION_ZONE','false','Boolean'),
--(1,'PACKBFI_MAX_ZONE','200','String'),
--(1,'PACKBFI_PAS_ZONE','10','String');
--
--
--INSERT INTO `financial_parametre` (`id_local`,`variable`, `valeur`, `type`) VALUES (1,'MODE_LOGICIEL', 'SINGLE', 'String');
--#INSERT INTO `financial_parametre` (`id_local`,`variable`, `valeur`, `type`) VALUES (1,'MODE_LOGICIEL', 'BACKOFFICE', 'String');
--
--
--
--
--INSERT INTO `financial_parametre` (
--`id_local` ,
--`variable` ,
--`valeur` ,
--`type`
--)
--VALUES (
--'00001', 'WARNING_BFSPROPOSITION', NULL , 'Int'
--);
--
--INSERT INTO `financial_parametre` (
--`id_local` ,
--`variable` ,
--`valeur` ,
--`type`
--)
--VALUES (
--'00001', 'WARNING_BFI', NULL , 'Int'
--);
--
--
--INSERT INTO `financial_parametre` (
--`id_local` ,
--`variable` ,
--`valeur` ,
--`type`
--)
--VALUES (
--'00001', 'CACHE_MANAGER_HIB', NULL , 'String'
--);
