test sql file

--
-- importe les données du compte n°1 exportées par recreate_demo_account_export.sql dans la base marica_demo
--


--
-- client dependent tables
--

\c marica_demo

BEGIN;

TRUNCATE TABLE
tblcontentieux, tblcontentieux_intervenant, tblcontentieux_agenda, tblcontentieux_nature, tblcontentieux_centre_cout,
tblcontentieux_instance, tblcontentieux_log, tblcontentieux_honoraire, tblcontentieux_courrier, tblcontentieux_finance,
tblcontentieux_versement_tiers, tblcontentieux_tiers, tblcontentieux_categorie, tblcontentieux_etat, tblcontentieux_expected, tblcontentieux_keyword, tblcontentieux_budget,
tblcourrier_categorie,
tblagent,
tblobjet, tblobjet_categorie, tblobjet_parc, tblobjet_champ,
tblcontrat, tblcontrat_prime, tblgarantie, tblcontrat_garantie, tblcontrat_document, tblcontrat_autre, tblcontrat_autre_document, tblcontrat_type, tblcontrat_nature, tblcontrat_categorie,
tblagent_assur, tblintervenant,
tbldirection, tblservice, tblcivilite,
marica_dossier_group, marica_group, marica_user, marica_user_group,
tblmodele_document, tblreport_custom,
marica_client_site, marica_client_contact, marica_client, tbldatestyle,
tblmodele_categorie, tbloui_non, tblfield_label, tblcontentieux_position, tblmessage;



-- il faut détruire les règles avant copy, sinon les sites sont créés deux fois
DROP RULE create_client_site_0 ON marica_client;

INSERT INTO marica_client (id_client, nom_client) VALUES(1, 'xxxx');
INSERT INTO marica_client (id_client, nom_client) VALUES(0, '');
INSERT INTO marica_client_site(id_site, site_name, id_client) VALUES(0, '', 0);

CREATE RULE create_client_site_0 AS ON INSERT TO marica_client DO (INSERT INTO marica_client_site (id_client, id_client_site) VALUES (currval('marica_client_id_client_seq'::regclass), 0); INSERT INTO tbldirection (id_client, libelle) VALUES (currval('marica_client_id_client_seq'::regclass), 'Direction1'::text); INSERT INTO tblservice (id_direction, nom_service) VALUES (currval('tbldirection_id_direction_seq'::regclass), 'Service1'::text); INSERT INTO tblfield_label (table_name, field_name, libelle, id_client) SELECT tblfield_label.table_name, tblfield_label.field_name, tblfield_label.libelle, currval('marica_client_id_client_seq'::regclass) AS currval FROM tblfield_label WHERE (tblfield_label.id_client = 1); );

INSERT INTO tblcontentieux_budget (id_client, id_budget, libelle) VALUES(0, 0, '');

INSERT INTO tblcontentieux_centre_cout (id_client, id_centre_cout, libelle) VALUES(0, 0, '');

INSERT INTO tbldirection (id_client, id_direction, libelle) VALUES (0, 0, NULL);

INSERT INTO tblservice (id_service, id_direction, nom_service) VALUES (0, 0, '');

INSERT INTO tblcontentieux_keyword (id_client, id_keyword, libelle) VALUES(0, 0, '');

--
-- import client independent tables
--

\copy tblmodele_categorie from /home/www_marica/site/demo_tables/tblmodele_categorie.txt

\copy tblmodele_document from /home/www_marica/site/demo_tables/tblmodele_document.txt

\copy tbloui_non from /home/www_marica/site/demo_tables/tbloui_non.txt

\copy tblfield_label from /home/www_marica/site/demo_tables/tblfield_label.txt

\copy tblcontentieux_position from /home/www_marica/site/demo_tables/tblcontentieux_position.txt

--
-- import client dependent tables
--

\copy tbldatestyle from /home/www_marica/site/demo_tables/tbldatestyle.txt

\copy tblmessage from /home/www_marica/site/demo_tables/tblmessage.txt

\copy tblreport_custom from /home/www_marica/site/demo_tables/tblreport_custom.txt

INSERT INTO marica_user (username, userpass, id_client, active, administrateur, preferred_datestyle, list_layout) VALUES ('demo_', 'demo_', 1, 1, 1, 'SQL, dmy', '{ ''column_1'' => [ ''ref_dossier'', 10 ], ''column_2'' => [ ''id_tiers'', 15 ], ''column_3'' => [ ''ref_assureur'', 15 ], ''column_4'' => [ ''id_service'', 20 ], ''column_5'' => [ ''libelle'', 40 ] }');

INSERT INTO marica_user (username, userpass, id_client, active, administrateur, preferred_datestyle, list_layout) VALUES ('demo_standard', 'demo_', 1, 1, 0, 'SQL, dmy', '{ ''column_1'' => [ ''ref_dossier'', 10 ], ''column_2'' => [ ''id_tiers'', 15 ], ''column_3'' => [ ''ref_assureur'', 15 ], ''column_4'' => [ ''id_service'', 20 ], ''column_5'' => [ ''libelle'', 40 ] }');

\copy marica_group from /home/www_marica/site/demo_tables/marica_group.txt

INSERT INTO marica_user_group(id_group, id_user) VALUES (1, 'demo_standard');

\copy tblobjet from /home/www_marica/site/demo_tables/tblobjet.txt

DROP RULE create_default_parc_headers ON tblobjet;

INSERT INTO tblobjet(id_objet) VALUES (0);

CREATE RULE create_default_parc_headers AS ON INSERT TO tblobjet
DO ( INSERT INTO tblobjet_champ VALUES(lastval(),'champ_1', 'champ_2', 'champ_3', 'champ_4', 'champ_5', 'num_1', 'num_2', 'date_1', 'date_2'); );

\copy tblobjet_categorie from /home/www_marica/site/demo_tables/tblobjet_categorie.txt

INSERT INTO tblobjet_categorie (id_objet_categorie, libelle) VALUES (0, '');

\copy tblobjet_champ from /home/www_marica/site/demo_tables/tblobjet_champ.txt

\copy tblobjet_parc from /home/www_marica/site/demo_tables/tblobjet_parc.txt

INSERT INTO tblobjet_parc(id_objet_parc, id_objet_categorie) VALUES (0, 0);

\copy tblcivilite from /home/www_marica/site/demo_tables/tblcivilite.txt

INSERT INTO tblcivilite(id_civilite, libelle) VALUES (0, '');

\copy tblintervenant from /home/www_marica/site/demo_tables/tblintervenant.txt

\copy tblagent_assur from /home/www_marica/site/demo_tables/tblagent_assur.txt

INSERT INTO tblagent_assur(id_agent_assur,nom_agent) VALUES (0,'');

\copy tblcontrat from /home/www_marica/site/demo_tables/tblcontrat.txt

INSERT INTO tblcontrat(id_contrat, date_debut, date_fin, date_resiliation, id_objet) VALUES (0, '01/01/1', '01/01/1', '01/01/1', 0);

\copy tblcontrat_prime from /home/www_marica/site/demo_tables/tblcontrat_prime.txt

\copy tblgarantie from /home/www_marica/site/demo_tables/tblgarantie.txt

INSERT INTO tblgarantie(id_garantie, libelle) VALUES (0, '');

\copy tblcontrat_garantie from /home/www_marica/site/demo_tables/tblcontrat_garantie.txt

\copy tblcontentieux_categorie from /home/www_marica/site/demo_tables/tblcontentieux_categorie.txt

INSERT INTO tblcontentieux_categorie (id_categorie, libelle) VALUES(0, '');

\copy tblcontentieux_etat from /home/www_marica/site/demo_tables/tblcontentieux_etat.txt

INSERT INTO tblcontentieux_etat (id_etat, libelle) VALUES(0, '');

\copy tblcontentieux_expected from /home/www_marica/site/demo_tables/tblcontentieux_expected.txt

\copy tblcontentieux_keyword from /home/www_marica/site/demo_tables/tblcontentieux_keyword.txt

\copy tblcontentieux_budget from /home/www_marica/site/demo_tables/tblcontentieux_budget.txt

\copy tblcontentieux_centre_cout from /home/www_marica/site/demo_tables/tblcontentieux_centre_cout.txt

\copy tblcontentieux_nature from /home/www_marica/site/demo_tables/tblcontentieux_nature.txt

INSERT INTO tblcontentieux_nature(id_nature, libelle) VALUES(0, '');

\copy marica_client_site from /home/www_marica/site/demo_tables/marica_client_site.txt

\copy tblagent from /home/www_marica/site/demo_tables/tblagent.txt

INSERT INTO tblagent(id_agent, responsable_dossier, id_client, id_site, id_civilite) VALUES (0, 0, 0, 0, 0);

\copy tbldirection from /home/www_marica/site/demo_tables/tbldirection.txt

\copy tblservice from /home/www_marica/site/demo_tables/tblservice.txt

drop trigger create_default_instance on tblcontentieux;

\copy tblcontentieux from /home/www_marica/site/demo_tables/tblcontentieux.txt

create trigger create_default_instance after insert on tblcontentieux for each row execute procedure default_instance();

\copy tblcontentieux_instance from /home/www_marica/site/demo_tables/tblcontentieux_instance.txt

\copy tblcontentieux_log from /home/www_marica/site/demo_tables/tblcontentieux_log.txt

\copy tblcontentieux_intervenant from /home/www_marica/site/demo_tables/tblcontentieux_intervenant.txt

\copy tblcontentieux_tiers from /home/www_marica/site/demo_tables/tblcontentieux_tiers.txt

\copy tblcourrier_categorie from /home/www_marica/site/demo_tables/tblcourrier_categorie.txt

INSERT INTO tblcourrier_categorie (id_categorie	, libelle) VALUES(0, 'Non précisé');

\copy tblcontentieux_courrier from /home/www_marica/site/demo_tables/tblcontentieux_courrier.txt

\copy tblcontentieux_honoraire from /home/www_marica/site/demo_tables/tblcontentieux_honoraire.txt

\copy tblcontentieux_finance from /home/www_marica/site/demo_tables/tblcontentieux_finance.txt

\copy marica_dossier_group from /home/www_marica/site/demo_tables/marica_dossier_group.txt

-- contrats autres

INSERT INTO tblcontrat_type (id_contrat_type, id_client, libelle) VALUES (0, 0, '');

INSERT INTO tblcontrat_nature (id_contrat_nature, id_contrat_type, libelle) VALUES (0, 0, '');

INSERT INTO tblcontrat_categorie (id_contrat_categorie, id_client, libelle) VALUES (0, 0, '');

\copy tblcontrat_type from /home/www_marica/site/demo_tables/tblcontrat_type.txt

 select setval('tblcontrat_type_id_contrat_type_seq'::regclass,(select max(id_contrat_type) from tblcontrat_type));

\copy tblcontrat_categorie from /home/www_marica/site/demo_tables/tblcontrat_categorie.txt

 select setval('tblcontrat_categorie_id_contrat_categorie_seq'::regclass,(select max(id_contrat_categorie) from tblcontrat_categorie));

\copy tblcontrat_nature from /home/www_marica/site/demo_tables/tblcontrat_nature.txt

 select setval('tblcontrat_nature_id_contrat_nature_seq'::regclass,(select max(id_contrat_nature) from tblcontrat_nature));

\copy tblcontrat_autre from /home/www_marica/site/demo_tables/tblcontrat_autre.txt

 select setval('tblcontrat_autre_id_contrat_autre_seq'::regclass,(select max(id_contrat_autre) from tblcontrat_autre));

--
-- reset counters
 --
 select setval('marica_client_id_client_seq'::regclass,1);
 select setval('tblmodele_document_id_modele_seq'::regclass,(select max(id_modele) from tblmodele_document));
 select setval('tblobjet_categorie_id_objet_categorie_seq'::regclass,(select max(id_objet_categorie) from tblobjet_categorie));
 select setval('tblobjet_parc_id_objet_parc_seq'::regclass,(select max(id_objet_parc) from tblobjet_parc));
 select setval('tblobjet_id_objet_seq'::regclass,(select max(id_objet) from tblobjet));
 select setval('tblgarantie_id_garantie_seq'::regclass,(select max(id_garantie) from tblgarantie));
 select setval('tblcontrat_id_contrat_seq'::regclass,(select max(id_contrat) from tblcontrat));
 select setval('tblcontrat_prime_id_item_seq'::regclass,(select max(id_item) from tblcontrat_prime));
 select setval('tblcivilite_id_civilite_seq'::regclass,(select max(id_civilite) from tblcivilite));
 select setval('tblcontentieux_categorie_id_categorie_seq'::regclass,(select max(id_categorie) from tblcontentieux_categorie));
 select setval('tblcontentieux_etat_id_etat_seq'::regclass,(select max(id_etat) from tblcontentieux_etat));
 select setval('tblcontentieux_nature_id_nature_seq'::regclass,(select max(id_nature) from tblcontentieux_nature));
 select setval('tblintervenant_id_intervenant_seq'::regclass,(select max(id_intervenant) from tblintervenant));
 select setval('tblagent_assur_id_agent_assur_seq'::regclass,(select max(id_agent_assur) from tblagent_assur));
 select setval('tblagent_id_agent_seq'::regclass,(select max(id_agent) from tblagent));
 select setval('tbldirection_id_direction_seq'::regclass,(select max(id_direction) from tbldirection));
 select setval('tblservice_id_service_seq'::regclass,(select max(id_service) from tblservice));
 select setval('tblcontentieux_id_contentieux_seq'::regclass,(select max(id_contentieux) from tblcontentieux));
 select setval('tblcontentieux_instance_id_instance_seq'::regclass,(select max(id_instance) from tblcontentieux_instance));
 select setval('tblcontentieux_log_id_log_seq'::regclass,(select max(id_log) from tblcontentieux_log));
 select setval('tblcontentieux_tiers_id_tiers_seq'::regclass,(select max(id_tiers) from tblcontentieux_tiers));
 select setval('tblcontentieux_expected_id_item_seq'::regclass,(select max(id_item) from tblcontentieux_expected));
 select setval('tblcontentieux_keyword_id_keyword_seq'::regclass,(select max(id_keyword) from tblcontentieux_keyword));
 select setval('tblcontentieux_budget_id_budget_seq'::regclass,(select max(id_budget) from tblcontentieux_budget));
 select setval('tblcontentieux_centre_cout_id_centre_cout_seq'::regclass,(select max(id_centre_cout) from tblcontentieux_centre_cout));
 select setval('tblcourrier_categorie_id_categorie_seq'::regclass,(select max(id_categorie) from tblcourrier_categorie));
 select setval('tblcontentieux_courrier_id_courrier_seq'::regclass,(select max(id_courrier) from tblcontentieux_courrier));
 select setval('tblcontentieux_honoraire_id_honoraire_seq'::regclass,(select max(id_honoraire) from tblcontentieux_honoraire));
 select setval('marica_group_id_group_seq'::regclass,(select max(id_group) from marica_group));
 select setval('marica_client_site_id_site_seq'::regclass,(select max(id_site) from marica_client_site));
 select setval('tblreport_custom_id_report_seq'::regclass,(select max(id_report) from tblreport_custom));
-- pas d'élément dans tblcontentieux_finance
 select setval('tblcontentieux_finance_id_item_seq'::regclass,(select coalesce(max(id_item), 1) from tblcontentieux_finance));
-- pas d'élément dans tblcontrat_document, tblcontrat_autre_document
 select setval('tblcontrat_document_id_contrat_document_seq'::regclass, (select coalesce(max(id_contrat_document), 1) from tblcontrat_document));
 select setval('tblcontrat_autre_document_id_contrat_autre_document_seq'::regclass, (select coalesce(max(id_contrat_autre_document), 1) from tblcontrat_autre_document));

-- éléments de l'agenda : prendre cinq éléments de tblcontentieux_expected, et les insérer avec des dates calculées à partir de la date du jour

 insert into tblcontentieux_agenda (id_agenda, id_contentieux, id_item, date_expected, description)
 VALUES ( 1, (select id_contentieux from tblcontentieux where id_client = 1 and ref_dossier = 'RC 08-082'), (select id_item from tblcontentieux_expected where id_client = 1 and libelle = E'Rapport d''expert'), current_date + 1 * interval '1 day', 'Reguor - rapport POLYEXPERT');

 insert into tblcontentieux_agenda (id_agenda, id_contentieux, id_item, date_expected, description)
 VALUES ( 2, (select id_contentieux from tblcontentieux where id_client = 1 and ref_dossier = '1125-087-PJ 08-03'), (select id_item from tblcontentieux_expected where id_client = 1 and libelle = 'Audience du TGI'), current_date + 5 * interval '1 day', 'Roullier & Letram - Audience à 9h00 - Palais de Justice à Angers (vol métaux)');

 insert into tblcontentieux_agenda (id_agenda, id_contentieux, id_item, date_expected, description)
 VALUES ( 3, (select max(id_contentieux) from tblcontentieux where id_client = 1 and ref_dossier = 'RC 10-001'), (select id_item from tblcontentieux_expected where id_client = 1 and libelle = 'RDV Expertise'), current_date + 8 * interval '1 day' , 'Xuorel - Expertise à 11h00 - La Fraiserie à Bouchemaine (dégâts des eaux)');

 insert into tblcontentieux_agenda (id_agenda, id_contentieux, id_item, date_expected, description)
 VALUES ( 4, (select id_contentieux from tblcontentieux where id_client = 1 and ref_dossier = 'Tramway 08-012'), (select id_item from tblcontentieux_expected where id_client = 1 and libelle = 'Rapport du service concerné'), current_date + 11 * interval '1 day', 'Fissurations rue des dames - vérifier si infos suite au recours');

 insert into tblcontentieux_agenda (id_agenda, id_contentieux, id_item, date_expected, description)
 VALUES ( 5, (select id_contentieux from tblcontentieux where id_client = 1 and ref_dossier = '1125-157-CAA (SP)'), (select id_item from tblcontentieux_expected where id_client = 1 and libelle = 'RDV Expertise'), current_date + 20 * interval '1 day', 'Norev - Expertise à 14h00 - 17 rue Lamartine aux Ponts-de-Cé (eaux usées)');

 select setval('tblcontentieux_agenda_id_agenda_seq'::regclass,(select max(id_agenda) from tblcontentieux_agenda));

-- ajouter une année de plus en début d'année puis faire un export du compte démo

-- \set years_to_add 1

-- DROP TRIGGER tblcontentieux_nouvelle_date_et_fiche_sinistre ON tblcontentieux;

-- UPDATE tblcontentieux SET date_debut_contentieux = date_debut_contentieux + cast( :years_to_add || ' years' as interval), date_fin_contentieux = date_fin_contentieux + cast( :years_to_add || ' years' as interval);

-- CREATE TRIGGER tblcontentieux_nouvelle_date_et_fiche_sinistre BEFORE UPDATE ON tblcontentieux FOR EACH ROW EXECUTE PROCEDURE nouvelle_date_et_fiche_sinistre();

-- UPDATE tblcontrat SET date_debut = date_debut + cast( :years_to_add || ' years' as interval), date_fin = date_fin + cast( :years_to_add || ' years' as interval), date_resiliation = date_resiliation + cast( :years_to_add || ' years' as interval);

-- UPDATE tblcontentieux_log SET date_log = date_log + cast( :years_to_add || ' years' as interval);

-- UPDATE tblcontentieux_courrier SET date_courrier = date_courrier + cast( :years_to_add || ' years' as interval);

-- UPDATE tblcontrat_autre SET date_debut = date_debut + cast( :years_to_add || ' years' as interval), date_fin = date_fin + cast( :years_to_add || ' years' as interval), date_resiliation = date_resiliation + cast( :years_to_add || ' years' as interval);

COMMIT;