+++ /dev/null
-SET CLIENT_ENCODING TO 'LATIN9';
-
-CREATE TABLE authority_types (
- id integer PRIMARY KEY,
- parent_type_id integer,
- description VARCHAR(512)
-);
-
-CREATE SEQUENCE authorities_id_seq;
-CREATE TABLE authorities (
- id integer PRIMARY KEY DEFAULT nextval('authorities_id_seq'),
- authority_type_id integer,
- status integer,
- name VARCHAR(255),
- email VARCHAR(255),
- siren VARCHAR(10),
- agreement VARCHAR(64),
- address VARCHAR(255),
- postal_code integer,
- city VARCHAR(255),
- telephone VARCHAR(25),
- fax VARCHAR(25),
- department char(3),
- district char(1)
-);
-
-CREATE SEQUENCE users_id_seq;
-CREATE TABLE users (
- id integer PRIMARY KEY DEFAULT nextval('users_id_seq'),
- email VARCHAR(255),
- subject_dn VARCHAR(512) NOT NULL,
- issuer_dn VARCHAR(512) NOT NULL,
- name VARCHAR(100),
- givenname VARCHAR(100),
- telephone VARCHAR(25),
- role varchar(5) NOT NULL,
- authority_id integer NOT NULL,
- status integer,
- certificate text,
- cert_not_before timestamp with time zone,
- cert_not_after timestamp with time zone,
- cert_serial VARCHAR(32)
-);
-
-CREATE SEQUENCE users_perms_id_seq;
-CREATE TABLE users_perms (
- id integer PRIMARY KEY DEFAULT nextval('users_perms_id_seq'),
- module_id integer,
- user_id integer,
- perm VARCHAR(10)
-);
-
-CREATE SEQUENCE modules_id_seq;
-CREATE TABLE modules (
- id integer PRIMARY KEY DEFAULT nextval('modules_id_seq'),
- name VARCHAR(50),
- description VARCHAR(128),
- menu_entry VARCHAR(128),
- status integer not null DEFAULT 1
-);
-
-
-CREATE SEQUENCE authority_departments_id_seq;
-CREATE TABLE authority_departments (
- id integer PRIMARY KEY DEFAULT nextval('authority_departments_id_seq'),
- code VARCHAR(3),
- name VARCHAR(128)
-);
-
-CREATE SEQUENCE authority_districts_id_seq;
-CREATE TABLE authority_districts (
- id integer PRIMARY KEY DEFAULT nextval('authority_districts_id_seq'),
- authority_department_id integer,
- code VARCHAR(1),
- name VARCHAR(128)
-);
-
-CREATE SEQUENCE modules_authorities_id_seq;
-CREATE TABLE modules_authorities (
- id integer PRIMARY KEY DEFAULT nextval('modules_authorities_id_seq'),
- module_id integer NOT NULL,
- authority_id integer NOT NULL
-);
-
-CREATE SEQUENCE modules_params_id_seq;
-CREATE TABLE modules_params (
- id integer PRIMARY KEY DEFAULT nextval('modules_params_id_seq'),
- module_id integer NOT NULL,
- name VARCHAR(64),
- value VARCHAR(256),
- description VARCHAR(512)
-);
-
-CREATE SEQUENCE logs_id_seq;
-CREATE TABLE logs (
- id integer PRIMARY KEY DEFAULT nextval('logs_id_seq'),
- date timestamp with time zone NOT NULL,
- severity integer,
- module varchar(50),
- issuer varchar(30),
- user_id integer,
- visibility varchar(5),
- message text,
- timestamp text
-);
-
-
--- Constraints
-ALTER TABLE authority_types ADD CONSTRAINT authority_types_parent_type_id_fk FOREIGN KEY (parent_type_id) REFERENCES authority_types(id);
-
-ALTER TABLE authorities ADD CONSTRAINT authorities_authority_type_id_fk FOREIGN KEY (authority_type_id) REFERENCES authority_types(id);
-
-ALTER TABLE users ADD CONSTRAINT users_entity_id_fk FOREIGN KEY (authority_id) REFERENCES authorities(id);
-
-ALTER TABLE users_perms ADD CONSTRAINT users_perms_module_id_fk FOREIGN KEY (module_id) REFERENCES modules(id);
-ALTER TABLE users_perms ADD CONSTRAINT users_perms_user_id_fk FOREIGN KEY (user_id) REFERENCES users(id);
-
-ALTER TABLE modules_authorities ADD CONSTRAINT modules_authorities_module_id_fk FOREIGN KEY (module_id) REFERENCES modules(id);
-ALTER TABLE modules_authorities ADD CONSTRAINT modules_authorities_authority_id_fk FOREIGN KEY (authority_id) REFERENCES authorities(id);
-
-ALTER TABLE modules_params ADD CONSTRAINT modules_params_id_fk FOREIGN KEY (module_id) REFERENCES modules(id);
-
-CREATE UNIQUE INDEX modules_name_idx ON modules (name);
-
--- Initial data
-
--- Authorities type
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (1, null, 'Région');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (11, 1, 'Conseil régional');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (12, 1, 'Établissements publics locaux d\'enseignement');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (13, 1, 'Autres établissements publics');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (14, 1, 'Sociétés d\'économie mixte locales');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (2, null, 'Département');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (21, 2, 'Conseil général');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (22, 2, 'Établissements publics de santé');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (23, 2, 'Établissements publics locaux d\'enseignement');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (24, 2, 'Autres établissements publics');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (25, 2, 'Sociétés d\'économie mixte locales');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (3, null, 'Commune');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (31, 3, 'Commune');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (32, 3, 'Établissements publics de santé');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (33, 3, 'Autres établissements publics');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (34, 3, 'Sociétés d\'économie mixte locales');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (4, null, 'Établissements publics de coopération intercommunale et syndicats');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (41, 4, 'Syndicats de communes et syndicats mixtes « fermés » associant exclusivement des communes, et des EPCI');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (42, 4, 'Syndicats mixtes « ouverts » associant des collectivités territoriales, des groupements de collectivités territoriales et d\'autres personnes morales de droit public');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (43, 4, 'Syndicats d\'agglomération nouvelle');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (44, 4, 'Communautés de communes');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (45, 4, 'Communautés urbaines');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (46, 4, 'Communautés d\'agglomération');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (47, 4, 'Sociétés d\'économie mixte locales');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (5, null, 'Autres');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (51, 5, 'Service départemental d\'incendie et de secours');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (52, 5, 'Entente interdépartementale');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (53, 5, 'Entente interrégionale');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (54, 5, 'Autres sociétés d\'économie mixte locales');
-INSERT INTO authority_types (id, parent_type_id, description) VALUES (55, 5, 'Autres');
-
--- Admin authority
-INSERT INTO authorities (id, status, name) VALUES(nextval('authorities_id_seq'), 1, 'Administrateurs');
-
--- ACTES
-CREATE SEQUENCE actes_envelopes_id_seq;
-CREATE TABLE actes_envelopes (
- id integer PRIMARY KEY DEFAULT nextval('actes_envelopes_id_seq'),
- user_id integer,
- submission_date timestamp with time zone,
- siren VARCHAR(128),
- department char(3),
- district char(1),
- authority_type_code integer,
- name VARCHAR(100),
- telephone VARCHAR(25),
- email VARCHAR(255),
- file_path varchar(1024),
- file_size integer,
- return_mail varchar(1024),
- warning_sent char(1) DEFAULT NULL
-);
-
-CREATE SEQUENCE actes_transactions_id_seq;
-CREATE TABLE actes_transactions (
- id integer PRIMARY KEY DEFAULT nextval('actes_transactions_id_seq'),
- envelope_id integer NOT NULL,
- type varchar(5),
- related_transaction_id integer,
- nature_code varchar(10),
- nature_descr varchar(128),
- title varchar(128),
- subject varchar(512),
- number varchar(20),
- classification varchar(128),
- classification_date timestamp with time zone,
- decision_date timestamp with time zone,
- unique_id varchar(128)
-);
-
-CREATE TABLE actes_natures (
- id integer PRIMARY KEY,
- short_descr varchar(5),
- descr varchar(128)
-);
-
-CREATE SEQUENCE actes_transactions_workflow_id_seq;
-CREATE TABLE actes_transactions_workflow (
- id integer PRIMARY KEY DEFAULT nextval('actes_transactions_workflow_id_seq'),
- transaction_id integer,
- status_id integer,
- date timestamp with time zone,
- message varchar(512)
-);
-
-CREATE INDEX atw_tid_idx ON actes_transactions_workflow (transaction_id);
-
-CREATE TABLE actes_status (
- id integer PRIMARY KEY,
- name varchar(64)
-);
-
-CREATE SEQUENCE actes_included_files_id_seq;
-CREATE TABLE actes_included_files (
- id integer PRIMARY KEY DEFAULT nextval('actes_included_files_id_seq'),
- envelope_id integer,
- transaction_id integer,
- filename varchar(512),
- filetype varchar(64),
- filesize integer,
- signature text
-);
-
-CREATE SEQUENCE actes_classification_codes_id_seq;
-CREATE TABLE actes_classification_codes (
- id integer PRIMARY KEY DEFAULT nextval('actes_classification_codes_id_seq'),
- authority_id integer NOT NULL,
- level integer,
- code integer,
- parent_id integer,
- description varchar(128)
-);
-
-CREATE SEQUENCE actes_classification_requests_id_seq;
-CREATE TABLE actes_classification_requests (
- id integer PRIMARY KEY DEFAULT nextval('actes_classification_requests_id_seq'),
- request_date timestamp with time zone,
- requested_by integer,
- version_date timestamp with time zone
-);
-
-
-CREATE SEQUENCE actes_envelope_serials_id_seq;
-CREATE TABLE actes_envelope_serials (
- id integer PRIMARY KEY DEFAULT nextval('actes_envelope_serials_id_seq'),
- authority_id integer,
- reset_date timestamp with time zone,
- serial integer
-);
-
-CREATE SEQUENCE actes_transmission_windows_id_seq;
-CREATE TABLE actes_transmission_windows (
- id integer PRIMARY KEY DEFAULT nextval('actes_transmission_windows_id_seq'),
- rate_limit integer
-);
-
-CREATE SEQUENCE actes_transmission_window_hours_id_seq;
-CREATE TABLE actes_transmission_window_hours (
- id integer PRIMARY KEY DEFAULT nextval('actes_transmission_window_hours_id_seq'),
- transmission_window_id integer,
- window_begin timestamp with time zone,
- window_end timestamp with time zone,
- consumed integer
-);
-
-
--- Constraints
-ALTER TABLE actes_envelopes ADD CONSTRAINT actes_envelopes_user_id_fk FOREIGN KEY (user_id) REFERENCES users(id);
-ALTER TABLE actes_transactions ADD CONSTRAINT actes_transactions_envelope_id_fk FOREIGN KEY (envelope_id) REFERENCES actes_envelopes(id);
-ALTER TABLE actes_transactions ADD CONSTRAINT actes_transactions_related_transaction_id_fk FOREIGN KEY (related_transaction_id) REFERENCES actes_transactions(id);
-ALTER TABLE actes_transactions_workflow ADD CONSTRAINT actes_transactions_workflow_transaction_id_fk FOREIGN KEY (transaction_id) REFERENCES actes_transactions(id);
-ALTER TABLE actes_transactions_workflow ADD CONSTRAINT actes_transactions_workflow_status_id_fk FOREIGN KEY (status_id) REFERENCES actes_status(id);
-ALTER TABLE actes_included_files ADD CONSTRAINT actes_included_files_envelope_id_fk FOREIGN KEY (envelope_id) REFERENCES actes_envelopes(id);
-ALTER TABLE actes_included_files ADD CONSTRAINT actes_included_files_transaction_id_fk FOREIGN KEY (transaction_id) REFERENCES actes_transactions(id);
-ALTER TABLE actes_classification_codes ADD CONSTRAINT actes_classification_codes_authority_id_fk FOREIGN KEY (authority_id) REFERENCES authorities(id);
-ALTER TABLE actes_classification_codes ADD CONSTRAINT actes_classification_codes_parent_id_fk FOREIGN KEY (parent_id) REFERENCES actes_classification_codes(id);
-ALTER TABLE actes_classification_requests ADD CONSTRAINT actes_classification_requests_requested_by_fk FOREIGN KEY (requested_by) REFERENCES users(id);
-ALTER TABLE actes_envelope_serials ADD CONSTRAINT actes_envelope_serials_authority_id_fk FOREIGN KEY (authority_id) REFERENCES authorities(id);
-ALTER TABLE actes_transmission_window_hours ADD CONSTRAINT actes_transmission_window_hours_transmission_window_id_fk FOREIGN KEY (transmission_window_id) REFERENCES actes_transmission_windows(id);
-
--- Module data
-INSERT INTO modules (name, description, menu_entry, status) VALUES ('actes', 'Module Actes', 'Transactions Actes', 1);
--- Initial data
-INSERT INTO actes_natures (id, short_descr, descr) VALUES (1, 'DE', 'Délibérations');
-INSERT INTO actes_natures (id, short_descr, descr) VALUES (2, 'AR', 'Arrêtés réglementaires');
-INSERT INTO actes_natures (id, short_descr, descr) VALUES (3, 'AI', 'Arrêtés individuels');
-INSERT INTO actes_natures (id, short_descr, descr) VALUES (4, 'CC', 'Contrats et conventions');
-INSERT INTO actes_natures (id, short_descr, descr) VALUES (5, 'BF', 'Documents budgétaires et financiers');
-INSERT INTO actes_natures (id, short_descr, descr) VALUES (6, 'AU', 'Autres');
-
-INSERT INTO actes_status (id, name) VALUES (-1, 'Erreur');
-INSERT INTO actes_status (id, name) VALUES (0, 'Annulé');
-INSERT INTO actes_status (id, name) VALUES (1, 'Posté');
-INSERT INTO actes_status (id, name) VALUES (2, 'En attente de transmission');
-INSERT INTO actes_status (id, name) VALUES (3, 'Transmis');
-INSERT INTO actes_status (id, name) VALUES (4, 'Acquittement reçu');
-INSERT INTO actes_status (id, name) VALUES (5, 'Validé');
-INSERT INTO actes_status (id, name) VALUES (6, 'Refusé');
-
-
--- Data for Name: authority_departments; Type: TABLE DATA; Schema: public; Owner: tedetis
---
-
-COPY authority_departments (id, code, name) FROM stdin;
-101 001 Ain
-102 002 Aisne
-103 003 Allier
-104 004 Alpes-de-Haute-Provence
-105 005 Hautes-Alpes
-106 006 Alpes-Maritimes
-107 007 Ardèche
-108 008 Ardennes
-109 009 Ariège
-110 010 Aube
-111 011 Aude
-112 012 Aveyron
-113 013 Bouches-du-Rhône
-114 014 Calvados
-115 015 Cantal
-116 016 Charente
-117 017 Charente-Maritime
-118 018 Cher
-119 019 Corrèze
-120 02A Corse-du-Sud
-121 02B Haute-Corse
-122 021 Côte-d'Or
-123 022 Côtes-d'Armor
-124 023 Creuse
-125 024 Dordogne
-126 025 Doubs
-127 026 Drôme
-128 027 Eure
-129 028 Eure-et-Loir
-130 029 Finistère
-131 030 Gard
-132 031 Haute-Garonne
-133 032 Gers
-134 033 Gironde
-135 034 Hérault
-136 035 Ille-et-Vilaine
-137 036 Indre
-138 037 Indre-et-Loire
-139 038 Isère
-140 039 Jura
-141 040 Landes
-142 041 Loir-et-Cher
-143 042 Loire
-144 043 Haute-Loire
-145 044 Loire-Atlantique
-146 045 Loiret
-147 046 Lot
-148 047 Lot-et-Garonne
-149 048 Lozère
-150 049 Maine-et-Loire
-151 050 Manche
-152 051 Marne
-153 052 Haute-Marne
-154 053 Mayenne
-155 054 Meurthe-et-Moselle
-156 055 Meuse
-157 056 Morbihan
-158 057 Moselle
-159 058 Nièvre
-160 059 Nord
-161 060 Oise
-162 061 Orne
-163 062 Pas-de-Calais
-164 063 Puy-de-Dôme
-165 064 Pyrénées-Atlantiques
-166 065 Hautes-Pyrénées
-167 066 Pyrénées-Orientales
-168 067 Bas-Rhin
-169 068 Haut-Rhin
-170 069 Rhône
-171 070 Haute-Saône
-172 071 Saône-et-Loire
-173 072 Sarthe
-174 073 Savoie
-175 074 Haute-Savoie
-176 075 Paris
-177 076 Seine-Maritime
-178 077 Seine-et-Marne
-179 078 Yvelines
-180 079 Deux-Sèvres
-181 080 Somme
-182 081 Tarn
-183 082 Tarn-et-Garonne
-184 083 Var
-185 084 Vaucluse
-186 085 Vendée
-187 086 Vienne
-188 087 Haute-Vienne
-189 088 Vosges
-190 089 Yonne
-191 090 Territoire de Belfort
-192 091 Essonne
-193 092 Hauts-de-Seine
-194 093 Seine-Saint-Denis
-195 094 Val-de-Marne
-196 095 Val-d'Oise
-197 971 Guadeloupe
-198 972 Martinique
-199 973 Guyane
-200 974 La Réunion
-\.
-
---
--- Data for Name: authority_districts; Type: TABLE DATA; Schema: public; Owner: tedetis
---
-
-COPY authority_districts (id, authority_department_id, code, name) FROM stdin;
-685 101 1 Belley
-686 101 2 Bourg-en-Bresse
-687 101 3 Gex
-688 101 4 Nantua
-689 102 1 Château-Thierry
-690 102 2 Laon
-691 102 3 Saint-Quentin
-692 102 4 Soissons
-693 102 5 Vervins
-694 103 1 Montluçon
-695 103 2 Moulins
-696 103 3 Vichy
-697 104 1 Barcelonnette
-698 104 2 Castellane
-699 104 3 Digne-les-Bains
-700 104 4 Forcalquier
-701 105 1 Briançon
-702 105 2 Gap
-703 106 1 Grasse
-704 106 2 Nice
-705 107 1 Largentière
-706 107 2 Privas
-707 107 3 Tournon-sur-Rhône
-708 108 1 Charleville-Mézières
-709 108 2 Rethel
-710 108 3 Sedan
-711 108 4 Vouziers
-712 109 1 Foix
-713 109 2 Pamiers
-714 109 3 Saint-Girons
-715 110 1 Bar-sur-Aube
-716 110 2 Nogent-sur-Seine
-717 110 3 Troyes
-718 111 1 Carcassonne
-719 111 2 Limoux
-720 111 3 Narbonne
-721 112 1 Millau
-722 112 2 Rodez
-723 112 3 Villefranche-de-Rouergue
-724 113 1 Aix-en-Provence
-725 113 2 Arles
-726 113 3 Marseille
-727 113 4 Istres
-728 114 1 Bayeux
-729 114 2 Caen
-730 114 3 Lisieux
-731 114 4 Vire
-732 115 1 Aurillac
-733 115 2 Mauriac
-734 115 3 Saint-Flour
-735 116 1 Angoulême
-736 116 2 Cognac
-737 116 3 Confolens
-738 117 1 Jonzac
-739 117 2 Rochefort
-740 117 3 (La)\tRochelle
-741 117 4 Saintes
-742 117 5 Saint-Jean-d'Angély
-743 118 1 Bourges
-744 118 2 Saint-Amand-Montrond
-745 118 3 Vierzon
-746 119 1 Brive-la-Gaillarde
-747 119 2 Tulle
-748 119 3 Ussel
-749 120 1 Ajaccio
-750 120 4 Sartène
-751 121 2 Bastia
-752 121 3 Corte
-753 121 5 Calvi
-754 122 1 Beaune
-755 122 2 Dijon
-756 122 3 Montbard
-757 123 1 Dinan
-758 123 2 Guingamp
-759 123 3 Lannion
-760 123 4 Saint-Brieuc
-761 124 1 Aubusson
-762 124 2 Guéret
-763 125 1 Bergerac
-764 125 2 Nontron
-765 125 3 Périgueux
-766 125 4 Sarlat-la-Canéda
-767 126 1 Besançon
-768 126 2 Montbéliard
-769 126 3 Pontarlier
-770 127 1 Die
-771 127 2 Nyons
-772 127 3 Valence
-773 128 1 (Les)\tAndelys
-774 128 2 Bernay
-775 128 3 Évreux
-776 129 1 Chartres
-777 129 2 Châteaudun
-778 129 3 Dreux
-779 129 4 Nogent-le-Rotrou
-780 130 1 Brest
-781 130 2 Châteaulin
-782 130 3 Morlaix
-783 130 4 Quimper
-784 131 1 Alès
-785 131 2 Nîmes
-786 131 3 (Le)\tVigan
-787 132 1 Muret
-788 132 2 Saint-Gaudens
-789 132 3 Toulouse
-790 133 1 Auch
-791 133 2 Condom
-792 133 3 Mirande
-793 134 1 Blaye
-794 134 2 Bordeaux
-795 134 3 Langon
-796 134 4 Lesparre-Médoc
-797 134 5 Libourne
-798 135 1 Béziers
-799 135 2 Lodève
-800 135 3 Montpellier
-801 136 1 Fougères
-802 136 2 Redon
-803 136 3 Rennes
-804 136 4 Saint-Malo
-805 137 1 (Le)\tBlanc
-806 137 2 Châteauroux
-807 137 3 (La)\tChâtre
-808 137 4 Issoudun
-809 138 1 Chinon
-810 138 2 Tours
-811 138 3 Loches
-812 139 1 Grenoble
-813 139 2 (La)\tTour-du-Pin
-814 139 3 Vienne
-815 140 1 Dole
-816 140 2 Lons-le-Saunier
-817 140 3 Saint-Claude
-818 141 1 Dax
-819 141 2 Mont-de-Marsan
-820 142 1 Blois
-821 142 2 Vendôme
-822 142 3 Romorantin-Lanthenay
-823 143 1 Montbrison
-824 143 2 Roanne
-825 143 3 Saint-Étienne
-826 144 1 Brioude
-827 144 2 (Le)\tPuy-en-Velay
-828 144 3 Yssingeaux
-829 145 1 Châteaubriant
-830 145 2 Nantes
-831 145 3 Saint-Nazaire
-832 145 4 Ancenis
-833 146 1 Montargis
-834 146 2 Orléans
-835 146 3 Pithiviers
-836 147 1 Cahors
-837 147 2 Figeac
-838 147 3 Gourdon
-839 148 1 Agen
-840 148 2 Marmande
-841 148 3 Villeneuve-sur-Lot
-842 148 4 Nérac
-843 149 1 Florac
-844 149 2 Mende
-845 150 1 Angers
-846 150 2 Cholet
-847 150 3 Saumur
-848 150 4 Segré
-849 151 1 Avranches
-850 151 2 Cherbourg-Octeville
-851 151 3 Coutances
-852 151 4 Saint-Lô
-853 152 1 Châlons-en-Champagne
-854 152 2 Épernay
-855 152 3 Reims
-856 152 4 Vitry-le-François
-857 152 5 Sainte-Menehould
-858 153 1 Chaumont
-859 153 2 Langres
-860 153 3 Saint-Dizier
-861 154 1 Château-Gontier
-862 154 2 Laval
-863 154 3 Mayenne
-864 155 1 Briey
-865 155 2 Lunéville
-866 155 3 Nancy
-867 155 4 Toul
-868 156 1 Bar-le-Duc
-869 156 2 Commercy
-870 156 3 Verdun
-871 157 1 Lorient
-872 157 2 Pontivy
-873 157 3 Vannes
-874 158 1 Boulay-Moselle
-875 158 2 Château-Salins
-876 158 3 Forbach
-877 158 4 Metz-Campagne
-878 158 5 Sarrebourg
-879 158 6 Sarreguemines
-880 158 7 Thionville-Est
-881 158 8 Thionville-Ouest
-882 158 9 Metz-Ville
-883 159 1 Château-Chinon(Ville)
-884 159 2 Clamecy
-885 159 3 Nevers
-886 159 4 Cosne-Cours-sur-Loire
-887 160 1 Avesnes-sur-Helpe
-888 160 2 Cambrai
-889 160 3 Douai
-890 160 4 Dunkerque
-891 160 5 Lille
-892 160 6 Valenciennes
-893 161 1 Beauvais
-894 161 2 Clermont
-895 161 3 Compiègne
-896 161 4 Senlis
-897 162 1 Alençon
-898 162 2 Argentan
-899 162 3 Mortagne-au-Perche
-900 163 1 Arras
-901 163 2 Béthune
-902 163 3 Boulogne-sur-Mer
-903 163 4 Montreuil
-904 163 5 Saint-Omer
-905 163 6 Calais
-906 163 7 Lens
-907 164 1 Ambert
-908 164 2 Clermont-Ferrand
-909 164 3 Issoire
-910 164 4 Riom
-911 164 5 Thiers
-912 165 1 Bayonne
-913 165 2 Oloron-Sainte-Marie
-914 165 3 Pau
-915 166 1 Argelès-Gazost
-916 166 2 Bagnères-de-Bigorre
-917 166 3 Tarbes
-918 167 1 Céret
-919 167 2 Perpignan
-920 167 3 Prades
-921 168 2 Haguenau
-922 168 3 Molsheim
-923 168 4 Saverne
-924 168 5 Sélestat-Erstein
-925 168 6 Strasbourg-Campagne
-926 168 7 Wissembourg
-927 168 8 Strasbourg-Ville
-928 169 1 Altkirch
-929 169 2 Colmar
-930 169 3 Guebwiller
-931 169 4 Mulhouse
-932 169 5 Ribeauvillé
-933 169 6 Thann
-934 170 1 Lyon
-935 170 2 Villefranche-sur-Saône
-936 171 1 Lure
-937 171 2 Vesoul
-938 172 1 Autun
-939 172 2 Chalon-sur-Saône
-940 172 3 Charolles
-941 172 4 Louhans
-942 172 5 Mâcon
-943 173 1 (La)\tFlèche
-944 173 2 Mamers
-945 173 3 (Le)\tMans
-946 174 1 Albertville
-947 174 2 Chambéry
-948 174 3 Saint-Jean-de-Maurienne
-949 175 1 Annecy
-950 175 2 Bonneville
-951 175 3 Saint-Julien-en-Genevois
-952 175 4 Thonon-les-Bains
-953 176 1 Paris
-954 177 1 Dieppe
-955 177 2 (Le)\tHavre
-956 177 3 Rouen
-957 178 1 Meaux
-958 178 2 Melun
-959 178 3 Provins
-960 178 4 Fontainebleau
-961 178 5 Torcy
-962 179 1 Mantes-la-Jolie
-963 179 2 Rambouillet
-964 179 3 Saint-Germain-en-Laye
-965 179 4 Versailles
-966 180 1 Bressuire
-967 180 2 Niort
-968 180 3 Parthenay
-969 181 1 Abbeville
-970 181 2 Amiens
-971 181 3 Montdidier
-972 181 4 Péronne
-973 182 1 Albi
-974 182 2 Castres
-975 183 1 Castelsarrasin
-976 183 2 Montauban
-977 184 1 Draguignan
-978 184 2 Toulon
-979 184 3 Brignoles
-980 185 1 Apt
-981 185 2 Avignon
-982 185 3 Carpentras
-983 186 1 Fontenay-le-Comte
-984 186 2 (La)\tRoche-sur-Yon
-985 186 3 (Les)\tSables-d'Olonne
-986 187 1 Châtellerault
-987 187 2 Montmorillon
-988 187 3 Poitiers
-989 188 1 Bellac
-990 188 2 Limoges
-991 188 3 Rochechouart
-992 189 1 Épinal
-993 189 2 Neufchâteau
-994 189 3 Saint-Dié-des-Vosges
-995 190 1 Auxerre
-996 190 2 Avallon
-997 190 3 Sens
-998 191 1 Belfort
-999 192 1 Étampes
-1000 192 2 Évry
-1001 192 3 Palaiseau
-1002 193 1 Antony
-1003 193 2 Nanterre
-1004 193 3 Boulogne-Billancourt
-1005 194 1 Bobigny
-1006 194 2 (Le)\tRaincy
-1007 194 3 Saint-Denis
-1008 195 1 Créteil
-1009 195 2 Nogent-sur-Marne
-1010 195 3 Haó-les-Roses
-1011 196 1 Argenteuil
-1012 196 2 Sarcelles
-1013 196 3 Pontoise
-1014 197 1 Basse-Terre
-1015 197 2 Pointe-à-Pitre
-1016 197 3 Saint-Martin-Saint-Barthélemy
-1017 198 1 Fort-de-France
-1018 198 2 (La)\tTrinité
-1019 198 3 (Le)\tMarin
-1020 198 4 Saint-Pierre
-1021 199 1 Cayenne
-1022 199 2 Saint-Laurent-du-Maroni
-1023 200 1 Saint-Denis
-1024 200 2 Saint-Pierre
-1025 200 3 Saint-Benoît
-1026 200 4 Saint-Paul
-\.
-
-
--- 1.0.1 vers 1.0.2
-CREATE SEQUENCE authority_groups_id_seq;
-CREATE TABLE authority_groups (
- id integer PRIMARY KEY DEFAULT nextval('authority_groups_id_seq'),
- name VARCHAR(128),
- status integer
-);
-
-CREATE SEQUENCE authority_group_siren_id_seq;
-CREATE TABLE authority_group_siren (
- id integer PRIMARY KEY DEFAULT nextval('authority_group_siren_id_seq'),
- authority_group_id integer,
- siren VARCHAR(10)
-);
-
-ALTER TABLE authorities ADD authority_group_id integer DEFAULT NULL;
-ALTER TABLE authorities ADD CONSTRAINT authorities_authority_group_id_fk FOREIGN KEY (authority_group_id) REFERENCES authority_groups(id);
-
-ALTER TABLE authority_group_siren ADD CONSTRAINT authority_group_siren_authority_group_id_fk FOREIGN KEY (authority_group_id) REFERENCES authority_groups(id);
-
-ALTER TABLE users ADD authority_group_id integer DEFAULT NULL;
-ALTER TABLE users ADD CONSTRAINT users_authority_group_id_fk FOREIGN KEY (authority_group_id) REFERENCES authority_groups(id);
-
-ALTER TABLE authorities ADD broadcast_email VARCHAR(255);
-
--- ACTES 1.0.0 vers 1.0.1
-ALTER TABLE actes_classification_requests ADD xml_data text DEFAULT NULL;
-
-
--- ACTES 1.0.1 vers 1.0.2
-ALTER TABLE actes_included_files ADD posted_filename VARCHAR(512);
-
-ALTER TABLE actes_transactions ADD archive_url VARCHAR(1024);
-
-CREATE SEQUENCE actes_batches_id_seq;
-CREATE TABLE actes_batches (
- id integer PRIMARY KEY DEFAULT nextval('actes_batches_id_seq'),
- user_id integer,
- submission_date timestamp with time zone,
- storage_dir varchar(1024),
- description varchar(1024),
- num_prefix varchar(16),
- next_suffix integer
-);
-
-CREATE SEQUENCE actes_batch_files_id_seq;
-CREATE TABLE actes_batch_files (
- id integer PRIMARY KEY DEFAULT nextval('actes_batch_files_id_seq'),
- batch_id integer,
- transaction_id integer,
- filename varchar(1024),
- filesize integer,
- status varchar(10),
- signature text
-);
-
-ALTER TABLE actes_batches ADD CONSTRAINT actes_batches_user_id_fk FOREIGN KEY (user_id) REFERENCES users(id);
-ALTER TABLE actes_batch_files ADD CONSTRAINT actes_batch_files_batch_id_fk FOREIGN KEY (batch_id) REFERENCES actes_batches(id);
-ALTER TABLE actes_batch_files ADD CONSTRAINT actes_batch_files_transaction_id_fk FOREIGN KEY (transaction_id) REFERENCES actes_transactions(id);
-
-
-
--- ACTES 1.0.2 vers 1.0.4
-ALTER TABLE actes_classification_codes ALTER COLUMN description TYPE varchar(256);
-
-ALTER TABLE authorities ALTER COLUMN broadcast_email TYPE text;
-ALTER TABLE authorities ADD default_broadcast_email text;
-
-ALTER TABLE actes_transactions ADD broadcast_emails text;
-ALTER TABLE actes_transactions ADD broadcast_send_sources integer;
-ALTER TABLE actes_transactions ADD broadcasted boolean DEFAULT false;
-
-
--- ACTES 1.0.4.1
--- La colonne broadcast_send_sources doit contenir une valeur
-alter table actes_transactions alter column broadcast_send_sources set default 0;
-alter table actes_transactions alter column broadcast_send_sources drop not null;
-
--- ACTES 1.0.5
-ALTER TABLE actes_transactions_workflow ADD flux_retour text;
-
-
--- ACTES Version 1.4 du protocole, implémentation NTSYS
-CREATE TABLE actes_messages_status (
- id integer PRIMARY KEY not null,
- nom character(64)
-);
-
-INSERT INTO actes_messages_status (id, nom) VALUES (1, 'Posté la réponse echoué');
-INSERT INTO actes_messages_status (id, nom) VALUES (2, 'Echèc de la transmission');
-INSERT INTO actes_messages_status (id, nom) VALUES (3, 'En attente de la transmission');
-INSERT INTO actes_messages_status (id, nom) VALUES (4, 'Transmis');
-
-INSERT INTO actes_messages_status (id, nom) VALUES (20, 'un courrier simple reçu');
-INSERT INTO actes_messages_status (id, nom) VALUES (21, 'Posté la réponse de courrier simple');
---
-INSERT INTO actes_messages_status (id, nom) VALUES (30, 'Réception de la demande des pièces complémentaire ');
-INSERT INTO actes_messages_status (id, nom) VALUES (31, 'AR demande pièces complémentaires');
-INSERT INTO actes_messages_status (id, nom) VALUES (32, 'Refus de pièce complémentaire posté');
-INSERT INTO actes_messages_status (id, nom) VALUES (33, 'Pièce complémentaire postés');
-INSERT INTO actes_messages_status (id, nom) VALUES (36, 'AR pièce complémentaire ou refus');
---
-INSERT INTO actes_messages_status (id, nom) VALUES (40, 'Réception du lettre d''observation');
-INSERT INTO actes_messages_status (id, nom) VALUES (41, 'Ar de lettre d''observation');
-INSERT INTO actes_messages_status (id, nom) VALUES (42, 'La réponse de lettre d''observations');
-INSERT INTO actes_messages_status (id, nom) VALUES (43, 'Réfus de lettre d''observation');
-INSERT INTO actes_messages_status (id, nom) VALUES (46, 'AR lettre d''observation');
---
-INSERT INTO actes_messages_status (id, nom) VALUES (50, 'Réception d''info déféré au TA');
-
-
-CREATE SEQUENCE actes_messages_id_seq
- INCREMENT BY 1
- MAXVALUE 999999999999999
- NO MINVALUE
- CACHE 1;
-CREATE TABLE actes_messages (
- id integer PRIMARY KEY DEFAULT nextval('actes_messages_id_seq'::regclass) NOT NULL,
- actes_transactions_id integer,
- messages_type integer,
- description varchar(512),
- actes_messages_status_id integer,
- received_date timestamp with time zone,
- FOREIGN KEY (actes_transactions_id) references actes_transactions(id),
- FOREIGN KEY (actes_messages_status_id) references actes_messages_status(id)
-);
-
-CREATE SEQUENCE actes_messages_reponses_seq
- INCREMENT BY 1
- MAXVALUE 999999999999999
- NO MINVALUE
- CACHE 1;
-CREATE TABLE actes_messages_reponses (
- id integer PRIMARY KEY DEFAULT nextval('actes_messages_reponses_seq'::regclass) NOT NULL,
- actes_messages_id integer,
- dir_path varchar(4096),
- enveloppe varchar(512),
- filename varchar(512),
- agreement boolean,
- file_size integer,
- warning_sent boolean DEFAULT false,
- first_sent_time timestamp with time zone,
- actual_sent_time timestamp with time zone,
- FOREIGN KEY (actes_messages_id) references actes_messages(id)
-);
-
-CREATE SEQUENCE actes_messages_workflow_id_seq
- INCREMENT BY 1
- MAXVALUE 999999999999999
- NO MINVALUE
- CACHE 1;
-
-CREATE TABLE actes_messages_workflow (
- id integer PRIMARY KEY DEFAULT nextval('actes_messages_workflow_id_seq'::regclass) NOT NULL,
- actes_messages_id integer,
- actes_messages_status_id integer,
- action_time timestamp with time zone,
- message varchar (1024),
- FOREIGN KEY (actes_messages_id) references actes_messages(id),
- FOREIGN KEY (actes_messages_status_id) references actes_messages_status(id)
-);
-
--- fichier 1.0.8-1.0.8.1.sql
-alter table authorities add column helios_ftp_password varchar (128) ;
-alter table authorities add column helios_ftp_login varchar (128);
-alter table authorities add column helios_ftp_dest varchar (128);
-alter table authorities add column ext_siret varchar (5);
-
--- Module HELIOS de base
-CREATE SEQUENCE helios_transactions_id_seq;
-
-CREATE TABLE helios_transactions (
- id integer DEFAULT nextval('helios_transactions_id_seq'::regclass) NOT NULL,
- user_id integer NOT NULL,
- filename varchar(1024) NOT NULL,
- file_size integer,
- siren varchar(128),
- sha1 character(40),
- warning_sent integer,
- url_archivage character(1024),
- submission_date timestamp with time zone,
- xml_nomfic varchar(255),
- acquit_filename varchar(255),
- complete_name varchar(150)
-);
-
-CREATE UNIQUE INDEX xml_nomfic_index_unique ON helios_transactions USING btree (xml_nomfic);
-
-
-CREATE SEQUENCE helios_transactions_workflow_id_seq;
-CREATE TABLE helios_transactions_workflow (
- id integer PRIMARY KEY DEFAULT nextval('helios_transactions_workflow_id_seq'),
- transaction_id integer NOT NULL,
- status_id integer NOT NULL,
- date timestamp with time zone NOT NULL,
- message varchar(512) NOT NULL
-);
-
-CREATE TABLE helios_status (
- id integer PRIMARY KEY,
- name varchar(64) NOT NULL
-);
-
--- Constraints
-ALTER TABLE helios_transactions ADD CONSTRAINT helios_transactions_user_id_fk FOREIGN KEY (user_id) REFERENCES users(id);
-
--- NOTE EP 24/10/2009 : cette requete échoue car helios_transactions(id) n'est pas une clé primaire
-ALTER TABLE helios_transactions_workflow ADD CONSTRAINT helios_transactions_workflow_transaction_id_fk FOREIGN KEY (transaction_id) REFERENCES helios_transactions(id);
-
-
--- Module data
-INSERT INTO modules (name, description, menu_entry, status) VALUES ('helios', 'Module Helios', 'Transactions Helios', 1);
-
--- Initial Data
-
-INSERT INTO helios_status (id, name) VALUES (-1, 'Erreur');
-INSERT INTO helios_status (id, name) VALUES (0, 'Annulé');
-INSERT INTO helios_status (id, name) VALUES (1, 'Posté');
-INSERT INTO helios_status (id, name) VALUES (2, 'En attente de transmission. Fichier valide.');
-INSERT INTO helios_status (id, name) VALUES (3, 'Transmis');
-INSERT INTO helios_status (id, name) VALUES (4, 'Acquittement reçu');
-INSERT INTO helios_status (id, name) VALUES (5, 'Validé. Non utilisé en Helios.');
-INSERT INTO helios_status (id, name) VALUES (6, 'Refusé');
-INSERT INTO helios_status (id, name) VALUES (7, 'En traitement');
-
-
---------------------------------------
-
-CREATE SEQUENCE helios_transmission_windows_id_seq
- INCREMENT BY 1
- MAXVALUE 999999999999999
- NO MINVALUE
- CACHE 1;
-CREATE TABLE helios_transmission_windows (
- id integer PRIMARY KEY DEFAULT nextval('helios_transmission_windows_id_seq'),
- rate_limit integer
-);
-
-
-CREATE SEQUENCE helios_transmission_window_hours_id_seq
- INCREMENT BY 1
- MAXVALUE 999999999999999
- NO MINVALUE
- CACHE 1;
-
-CREATE TABLE helios_transmission_window_hours (
- id integer PRIMARY KEY DEFAULT nextval('helios_transmission_window_hours_id_seq'),
- transmission_window_id integer,
- window_begin timestamp with time zone,
- window_end timestamp with time zone,
- consumed integer
-);
-
-
-ALTER TABLE helios_transmission_window_hours ADD CONSTRAINT helios_transmission_window_hours_transmission_window_id_fk FOREIGN KEY (transmission_window_id) REFERENCES helios_transmission_windows(id);
-
-
-CREATE SEQUENCE helios_retour_id_seq
- INCREMENT BY 1
- MAXVALUE 999999999999999
- NO MINVALUE
- CACHE 1;
-
-CREATE TABLE helios_retour (
- id integer PRIMARY KEY DEFAULT nextval('helios_retour_id_seq'),
- siren varchar(255),
- filename varchar(255),
- date timestamp with time zone,
- status integer
-);
-