1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
8
|
UPDATE parameters SET param_value_string = '19.04.5' WHERE id = 'database_version';
|
9
|
|
10
|
DELETE FROM parameters WHERE id = 'QrCodePrefix';
|
11
|
INSERT INTO parameters (id, description, param_value_int) VALUES ('QrCodePrefix', 'Si activé (1), ajoute "Maarch_" dans le contenu des QrCode générés. (Utilisable avec MaarchCapture >= 1.4)', 0);
|
12
|
|
13
|
DROP VIEW IF EXISTS res_view_letterbox;
|
14
|
DROP VIEW IF EXISTS view_contacts;
|
15
|
DROP VIEW IF EXISTS res_view_attachments;
|
16
|
|
17
|
ALTER TABLE res_letterbox DROP COLUMN IF EXISTS external_signatory_book_id;
|
18
|
ALTER TABLE res_letterbox ADD COLUMN external_signatory_book_id integer;
|
19
|
|
20
|
ALTER TABLE users DROP COLUMN IF EXISTS external_id;
|
21
|
ALTER TABLE users ADD COLUMN external_id jsonb DEFAULT '{}';
|
22
|
|
23
|
|
24
|
DO $$ BEGIN
|
25
|
IF (SELECT count(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'user_abs') = 1 THEN
|
26
|
DROP TABLE IF EXISTS redirected_baskets;
|
27
|
CREATE TABLE redirected_baskets
|
28
|
(
|
29
|
id serial NOT NULL,
|
30
|
actual_user_id INTEGER NOT NULL,
|
31
|
owner_user_id INTEGER NOT NULL,
|
32
|
basket_id character varying(255) NOT NULL,
|
33
|
group_id INTEGER NOT NULL,
|
34
|
CONSTRAINT redirected_baskets_pkey PRIMARY KEY (id),
|
35
|
CONSTRAINT redirected_baskets_unique_key UNIQUE (owner_user_id, basket_id, group_id)
|
36
|
)
|
37
|
WITH (OIDS=FALSE);
|
38
|
|
39
|
INSERT INTO redirected_baskets (owner_user_id, actual_user_id, basket_id, group_id) SELECT users.id, us.id, user_abs.basket_id, usergroups.id FROM usergroups, usergroup_content, user_abs, groupbasket, users, users us
|
40
|
where usergroup_content.group_id = usergroups.group_id
|
41
|
and usergroup_content.user_id = user_abs.user_abs
|
42
|
and users.user_id = user_abs.user_abs
|
43
|
and us.user_id = user_abs.new_user
|
44
|
and groupbasket.group_id = usergroup_content.group_id
|
45
|
and groupbasket.basket_id = user_abs.basket_id;
|
46
|
|
47
|
DROP TABLE IF EXISTS user_abs;
|
48
|
END IF;
|
49
|
END$$;
|
50
|
UPDATE history SET table_name = 'redirected_baskets' WHERE table_name = 'user_abs';
|
51
|
|
52
|
|
53
|
DROP TABLE IF EXISTS configurations;
|
54
|
CREATE TABLE configurations
|
55
|
(
|
56
|
id serial NOT NULL,
|
57
|
service character varying(64) NOT NULL,
|
58
|
value json DEFAULT '{}' NOT NULL,
|
59
|
CONSTRAINT configuration_pkey PRIMARY KEY (id),
|
60
|
CONSTRAINT configuration_unique_key UNIQUE (service)
|
61
|
)
|
62
|
WITH (OIDS=FALSE);
|
63
|
INSERT INTO configurations (service, value) VALUES ('admin_email_server', '{"type" : "smtp", "host" : "smtp.gmail.com", "port" : 465, "user" : "", "password" : "", "auth" : true, "secure" : "ssl", "from" : "notifications@maarch.org", "charset" : "utf-8"}');
|
64
|
|
65
|
|
66
|
DROP TABLE IF EXISTS emails;
|
67
|
CREATE TABLE emails
|
68
|
(
|
69
|
id serial NOT NULL,
|
70
|
user_id INTEGER NOT NULL,
|
71
|
sender json DEFAULT '{}' NOT NULL,
|
72
|
recipients json DEFAULT '[]' NOT NULL,
|
73
|
cc json DEFAULT '[]' NOT NULL,
|
74
|
cci json DEFAULT '[]' NOT NULL,
|
75
|
object character varying(256),
|
76
|
body text,
|
77
|
document json,
|
78
|
is_html boolean NOT NULL DEFAULT TRUE,
|
79
|
status character varying(16) NOT NULL,
|
80
|
message_exchange_id text,
|
81
|
creation_date timestamp without time zone NOT NULL,
|
82
|
send_date timestamp without time zone,
|
83
|
CONSTRAINT emails_pkey PRIMARY KEY (id)
|
84
|
)
|
85
|
WITH (OIDS=FALSE);
|
86
|
|
87
|
|
88
|
DROP TABLE IF EXISTS shipping_templates;
|
89
|
CREATE TABLE shipping_templates
|
90
|
(
|
91
|
id serial NOT NULL,
|
92
|
label character varying(64) NOT NULL,
|
93
|
description character varying(255) NOT NULL,
|
94
|
options json DEFAULT '{}',
|
95
|
fee json DEFAULT '{}',
|
96
|
entities jsonb DEFAULT '{}',
|
97
|
account json DEFAULT '{}',
|
98
|
CONSTRAINT shipping_templates_pkey PRIMARY KEY (id)
|
99
|
)
|
100
|
WITH (OIDS=FALSE);
|
101
|
DROP TABLE IF EXISTS shippings;
|
102
|
CREATE TABLE shippings
|
103
|
(
|
104
|
id serial NOT NULL,
|
105
|
user_id INTEGER NOT NULL,
|
106
|
attachment_id INTEGER NOT NULL,
|
107
|
is_version boolean NOT NULL,
|
108
|
options json DEFAULT '{}',
|
109
|
fee FLOAT NOT NULL,
|
110
|
recipient_entity_id INTEGER NOT NULL,
|
111
|
account_id character varying(64) NOT NULL,
|
112
|
creation_date timestamp without time zone NOT NULL,
|
113
|
CONSTRAINT shippings_pkey PRIMARY KEY (id)
|
114
|
)
|
115
|
WITH (OIDS=FALSE);
|
116
|
|
117
|
|
118
|
DO $$ BEGIN
|
119
|
IF (SELECT count(group_id) FROM usergroups_services WHERE service_id IN ('edit_recipient_in_process', 'edit_recipient_outside_process')) = 0 THEN
|
120
|
INSERT INTO usergroups_services (group_id, service_id)
|
121
|
SELECT usergroups.group_id, 'edit_recipient_in_process' FROM usergroups
|
122
|
LEFT JOIN usergroups_services ON usergroups.group_id = usergroups_services.group_id AND usergroups_services.service_id = 'add_copy_in_process'
|
123
|
WHERE service_id is null;
|
124
|
|
125
|
INSERT INTO usergroups_services (group_id, service_id)
|
126
|
SELECT usergroups.group_id, 'edit_recipient_outside_process' FROM usergroups
|
127
|
LEFT JOIN usergroups_services ON usergroups.group_id = usergroups_services.group_id AND usergroups_services.service_id = 'add_copy_in_indexing_validation'
|
128
|
WHERE service_id is null;
|
129
|
|
130
|
DELETE FROM usergroups_services WHERE service_id in ('add_copy_in_process', 'add_copy_in_indexing_validation');
|
131
|
END IF;
|
132
|
END$$;
|
133
|
|
134
|
DROP TABLE IF EXISTS exports_templates;
|
135
|
CREATE TABLE exports_templates
|
136
|
(
|
137
|
id serial NOT NULL,
|
138
|
user_id INTEGER NOT NULL,
|
139
|
format character varying(3) NOT NULL,
|
140
|
delimiter character varying(3),
|
141
|
data json DEFAULT '[]' NOT NULL,
|
142
|
CONSTRAINT exports_templates_pkey PRIMARY KEY (id),
|
143
|
CONSTRAINT exports_templates_unique_key UNIQUE (user_id, format)
|
144
|
)
|
145
|
WITH (OIDS=FALSE);
|
146
|
|
147
|
ALTER TABLE baskets DROP COLUMN IF EXISTS id;
|
148
|
ALTER TABLE baskets ADD COLUMN id serial;
|
149
|
ALTER TABLE baskets ADD UNIQUE (id);
|
150
|
|
151
|
ALTER TABLE groupbasket DROP COLUMN IF EXISTS id;
|
152
|
ALTER TABLE groupbasket ADD COLUMN id serial;
|
153
|
ALTER TABLE groupbasket ADD UNIQUE (id);
|
154
|
ALTER TABLE groupbasket DROP COLUMN IF EXISTS list_display;
|
155
|
ALTER TABLE groupbasket ADD COLUMN list_display json DEFAULT '[]';
|
156
|
|
157
|
DO $$ BEGIN
|
158
|
IF (SELECT count(attname) FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mlb_coll_ext') AND attname = 'recommendation_limit_date') = 1 THEN
|
159
|
ALTER TABLE res_letterbox ADD COLUMN opinion_limit_date TIMESTAMP without TIME ZONE DEFAULT NULL;
|
160
|
UPDATE res_letterbox SET opinion_limit_date = mlb_coll_ext.recommendation_limit_date
|
161
|
FROM mlb_coll_ext
|
162
|
WHERE res_letterbox.res_id = mlb_coll_ext.res_id AND recommendation_limit_date IS NOT NULL;
|
163
|
ALTER TABLE mlb_coll_ext DROP COLUMN IF EXISTS recommendation_limit_date;
|
164
|
END IF;
|
165
|
END$$;
|
166
|
|
167
|
|
168
|
UPDATE baskets SET basket_clause = regexp_replace(basket_clause,'recommendation_limit_date','opinion_limit_date','g');
|
169
|
UPDATE baskets SET basket_res_order = regexp_replace(basket_res_order,'recommendation_limit_date','opinion_limit_date','g');
|
170
|
|
171
|
|
172
|
DO $$ BEGIN
|
173
|
IF (SELECT count(attname) FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'groupbasket') AND attname = 'result_page') THEN
|
174
|
UPDATE groupbasket SET list_display = '[{"value":"getPriority","cssClasses":[],"icon":"fa-traffic-light"},{"value":"getCategory","cssClasses":[],"icon":"fa-exchange-alt"},{"value":"getDoctype","cssClasses":[],"icon":"fa-suitcase"},{"value":"getAssignee","cssClasses":[],"icon":"fa-sitemap"},{"value":"getRecipients","cssClasses":[],"icon":"fa-user"},{"value":"getSenders","cssClasses":[],"icon":"fa-book"},{"value":"getCreationAndProcessLimitDates","cssClasses":["align_rightData"],"icon":"fa-calendar"}]' WHERE result_page = 'list_with_attachments' OR result_page = 'list_copies';
|
175
|
UPDATE groupbasket SET list_display = '[{"value":"getPriority","cssClasses":[],"icon":"fa-traffic-light"},{"value":"getCategory","cssClasses":[],"icon":"fa-exchange-alt"},{"value":"getDoctype","cssClasses":[],"icon":"fa-suitcase"},{"value":"getParallelOpinionsNumber","cssClasses":["align_rightData"],"icon":"fa-comment-alt"},{"value":"getOpinionLimitDate","cssClasses":["align_rightData"],"icon":"fa-stopwatch"}]' WHERE result_page = 'list_with_avis';
|
176
|
UPDATE groupbasket SET list_display = '[{"value":"getPriority","cssClasses":[],"icon":"fa-traffic-light"},{"value":"getDoctype","cssClasses":[],"icon":"fa-suitcase"},{"value":"getVisaWorkflow","cssClasses":[],"icon":"fa-list-ol"},{"value":"getCreationAndProcessLimitDates","cssClasses":["align_rightData"],"icon":"fa-calendar"}]' WHERE result_page = 'list_with_signatory';
|
177
|
|
178
|
ALTER TABLE groupbasket DROP COLUMN IF EXISTS result_page;
|
179
|
END IF;
|
180
|
END $$;
|
181
|
|
182
|
|
183
|
ALTER TABLE actions DROP COLUMN IF EXISTS component;
|
184
|
ALTER TABLE actions ADD COLUMN component CHARACTER VARYING (128);
|
185
|
UPDATE actions SET component = 'v1Action' WHERE action_page IN ('put_in_copy', 'process', 'index_mlb', 'validate_mail', 'sendFileWS', 'sendDataWS', 'close_mail_and_index', 'close_mail_with_attachment', 'send_attachments_to_contact', 'send_to_contact_with_mandatory_attachment', 'visa_workflow', 'interrupt_visa', 'rejection_visa_redactor', 'rejection_visa_previous', 'redirect_visa_entity', 'send_to_visa', 'send_signed_docs', 'send_docs_to_recommendation', 'validate_recommendation', 'send_to_avis', 'avis_workflow', 'avis_workflow_simple', 'export_seda', 'check_acknowledgement', 'check_reply', 'purge_letter', 'reset_letter');
|
186
|
UPDATE actions SET component = 'confirmAction' WHERE action_page = 'confirm_status' OR action_page is null OR action_page = '';
|
187
|
UPDATE actions SET component = 'updateDepartureDateAction' WHERE action_page = 'confirm_status_with_update_date';
|
188
|
UPDATE actions SET component = 'viewDoc' WHERE action_page = 'view';
|
189
|
UPDATE actions SET component = 'closeMailAction' WHERE action_page = 'close_mail';
|
190
|
UPDATE actions SET component = 'enabledBasketPersistenceAction' WHERE action_page = 'set_persistent_mode_on';
|
191
|
UPDATE actions SET component = 'disabledBasketPersistenceAction' WHERE action_page = 'set_persistent_mode_off';
|
192
|
UPDATE actions SET component = 'resMarkAsReadAction' WHERE action_page = 'mark_as_read';
|
193
|
UPDATE actions SET component = 'signatureBookAction' WHERE action_page = 'visa_mail';
|
194
|
UPDATE actions SET component = 'redirectAction' WHERE action_page = 'redirect';
|
195
|
UPDATE actions SET component = 'sendExternalSignatoryBookAction' WHERE action_page = 'sendToExternalSignatureBook';
|
196
|
|
197
|
|
198
|
ALTER TABLE res_attachments DROP COLUMN IF EXISTS in_send_attach;
|
199
|
ALTER TABLE res_attachments ADD COLUMN in_send_attach boolean NOT NULL DEFAULT false;
|
200
|
ALTER TABLE res_version_attachments DROP COLUMN IF EXISTS in_send_attach;
|
201
|
ALTER TABLE res_version_attachments ADD COLUMN in_send_attach boolean NOT NULL DEFAULT false;
|
202
|
|
203
|
|
204
|
DROP TABLE IF EXISTS acknowledgement_receipts;
|
205
|
CREATE TABLE acknowledgement_receipts
|
206
|
(
|
207
|
id serial NOT NULL,
|
208
|
res_id INTEGER NOT NULL,
|
209
|
type CHARACTER VARYING(16) NOT NULL,
|
210
|
format CHARACTER VARYING(8) NOT NULL,
|
211
|
user_id INTEGER NOT NULL,
|
212
|
contact_address_id INTEGER NOT NULL,
|
213
|
creation_date timestamp without time zone NOT NULL,
|
214
|
send_date timestamp without time zone,
|
215
|
docserver_id CHARACTER VARYING(128) NOT NULL,
|
216
|
path CHARACTER VARYING(256) NOT NULL,
|
217
|
filename CHARACTER VARYING(256) NOT NULL,
|
218
|
fingerprint CHARACTER VARYING(256) NOT NULL,
|
219
|
CONSTRAINT acknowledgement_receipts_pkey PRIMARY KEY (id)
|
220
|
)
|
221
|
WITH (OIDS=FALSE);
|
222
|
DELETE FROM docserver_types WHERE docserver_type_id = 'ACKNOWLEDGEMENT_RECEIPTS';
|
223
|
INSERT INTO docserver_types (docserver_type_id, docserver_type_label, enabled) VALUES ('ACKNOWLEDGEMENT_RECEIPTS', 'Accusés de réception', 'Y');
|
224
|
DELETE FROM docservers WHERE docserver_id = 'ACKNOWLEDGEMENT_RECEIPTS';
|
225
|
INSERT INTO docservers (docserver_id, docserver_type_id, device_label, is_readonly, size_limit_number, actual_size_number, path_template, creation_date, coll_id)
|
226
|
VALUES ('ACKNOWLEDGEMENT_RECEIPTS', 'ACKNOWLEDGEMENT_RECEIPTS', 'Dépôt des AR', 'N', 50000000000, 0, '/opt/maarch/docservers/acknowledgment_receipts/', '2019-04-19 22:22:22.201904', 'letterbox_coll');
|
227
|
|
228
|
DO $$ BEGIN
|
229
|
IF (SELECT count(attname) FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'res_letterbox') AND attname = 'sve_start_date') = 1 THEN
|
230
|
INSERT INTO acknowledgement_receipts (res_id, type, format, user_id, contact_address_id, creation_date, send_date, docserver_id, path, filename, fingerprint)
|
231
|
SELECT res_id, 'simple', 'html', 0, 0, sve_start_date, sve_start_date, 0, 0, 0, 0 FROM res_letterbox WHERE sve_start_date is not null;
|
232
|
ALTER TABLE res_letterbox DROP COLUMN IF EXISTS sve_start_date;
|
233
|
END IF;
|
234
|
END$$;
|
235
|
|
236
|
|
237
|
UPDATE foldertypes SET coll_id = 'letterbox_coll' WHERE coll_id <> 'letterbox_coll';
|
238
|
|
239
|
|
240
|
ALTER TABLE mlb_coll_ext DROP COLUMN IF EXISTS flag_notif;
|
241
|
UPDATE res_letterbox SET locker_user_id = NULL;
|
242
|
ALTER TABLE res_letterbox ALTER COLUMN locker_user_id DROP DEFAULT;
|
243
|
ALTER TABLE res_letterbox ALTER COLUMN locker_user_id TYPE INTEGER USING locker_user_id::integer;
|
244
|
ALTER TABLE res_letterbox ALTER COLUMN locker_user_id SET DEFAULT NULL;
|
245
|
ALTER TABLE notes DROP COLUMN IF EXISTS tablename;
|
246
|
ALTER TABLE notes DROP COLUMN IF EXISTS coll_id;
|
247
|
ALTER TABLE notes DROP COLUMN IF EXISTS type;
|
248
|
ALTER TABLE notes ADD COLUMN type CHARACTER VARYING (32) DEFAULT 'resource' NOT NULL;
|
249
|
ALTER TABLE users DROP COLUMN IF EXISTS thumbprint;
|
250
|
ALTER TABLE entities DROP COLUMN IF EXISTS entity_path;
|
251
|
|
252
|
DO $$ BEGIN
|
253
|
IF (SELECT count(attname) FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'notes') AND attname = 'date_note') = 1 THEN
|
254
|
ALTER TABLE notes RENAME COLUMN date_note TO creation_date;
|
255
|
ALTER sequence notes_seq RENAME TO notes_id_seq;
|
256
|
END IF;
|
257
|
END$$;
|
258
|
ALTER TABLE res_mark_as_read DROP COLUMN IF EXISTS coll_id;
|
259
|
|
260
|
UPDATE listinstance SET added_by_entity = 'superadmin' WHERE added_by_user = 'superadmin';
|
261
|
UPDATE listinstance SET added_by_entity = 'superadmin' WHERE listinstance_id IN
|
262
|
(SELECT listinstance_id FROM listinstance LEFT JOIN entities ON listinstance.added_by_entity = entities.entity_id WHERE entities.entity_id IS null);
|
263
|
|
264
|
UPDATE listinstance SET added_by_entity = 'superadmin' WHERE listinstance_id IN
|
265
|
(SELECT listinstance_id
|
266
|
FROM listinstance
|
267
|
LEFT JOIN users_entities ON listinstance.added_by_user = users_entities.user_id
|
268
|
LEFT JOIN entities ON users_entities.entity_id = entities.entity_id
|
269
|
WHERE primary_entity = 'Y' AND entities.entity_id IS NULL);
|
270
|
|
271
|
UPDATE listinstance SET added_by_entity =
|
272
|
(SELECT entity_id FROM users_entities WHERE users_entities.user_id = listinstance.added_by_user AND primary_entity = 'Y')
|
273
|
WHERE added_by_entity IS NULL OR added_by_entity = '';
|
274
|
|
275
|
DO $$ BEGIN
|
276
|
IF (SELECT count(attname) FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'listinstance_history') AND attname = 'updated_by_user') THEN
|
277
|
ALTER TABLE listinstance_history DROP COLUMN IF EXISTS user_id;
|
278
|
ALTER TABLE listinstance_history ADD COLUMN user_id integer;
|
279
|
UPDATE listinstance_history set user_id = (select id FROM users where users.user_id = listinstance_history.updated_by_user);
|
280
|
UPDATE listinstance_history set user_id = 0 WHERE user_id IS NULL;
|
281
|
ALTER TABLE listinstance_history ALTER COLUMN user_id set not null;
|
282
|
ALTER TABLE listinstance_history DROP COLUMN IF EXISTS updated_by_user;
|
283
|
END IF;
|
284
|
END$$;
|
285
|
DO $$ BEGIN
|
286
|
IF (SELECT count(attname) FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'contact_addresses') AND attname = 'external_contact_id') THEN
|
287
|
ALTER TABLE contact_addresses DROP COLUMN IF EXISTS external_id;
|
288
|
ALTER TABLE contact_addresses ADD COLUMN external_id jsonb DEFAULT '{}';
|
289
|
UPDATE contact_addresses SET external_id = json_build_object('m2m', external_contact_id);
|
290
|
ALTER TABLE contact_addresses DROP COLUMN IF EXISTS external_contact_id;
|
291
|
END IF;
|
292
|
END$$;
|
293
|
DO $$ BEGIN
|
294
|
IF (SELECT count(column_name) from information_schema.columns where table_name = 'res_attachments' and column_name = 'external_id' and data_type != 'jsonb') THEN
|
295
|
ALTER TABLE res_attachments DROP COLUMN IF EXISTS external_id_tmp;
|
296
|
ALTER TABLE res_attachments ADD COLUMN external_id_tmp jsonb DEFAULT '{}';
|
297
|
UPDATE res_attachments SET external_id_tmp = json_build_object('signatureBookId', external_id);
|
298
|
ALTER TABLE res_attachments DROP COLUMN IF EXISTS external_id;
|
299
|
ALTER TABLE res_attachments RENAME COLUMN external_id_tmp TO external_id;
|
300
|
END IF;
|
301
|
END$$;
|
302
|
DO $$ BEGIN
|
303
|
IF (SELECT count(column_name) from information_schema.columns where table_name = 'res_version_attachments' and column_name = 'external_id' and data_type != 'jsonb') THEN
|
304
|
ALTER TABLE res_version_attachments DROP COLUMN IF EXISTS external_id_tmp;
|
305
|
ALTER TABLE res_version_attachments ADD COLUMN external_id_tmp jsonb DEFAULT '{}';
|
306
|
UPDATE res_version_attachments SET external_id_tmp = json_build_object('signatureBookId', external_id);
|
307
|
ALTER TABLE res_version_attachments DROP COLUMN IF EXISTS external_id;
|
308
|
ALTER TABLE res_version_attachments RENAME COLUMN external_id_tmp TO external_id;
|
309
|
END IF;
|
310
|
END$$;
|
311
|
DO $$ BEGIN
|
312
|
IF (SELECT count(column_name) from information_schema.columns where table_name = 'res_letterbox' and column_name = 'external_id' and data_type != 'jsonb') THEN
|
313
|
ALTER TABLE res_letterbox RENAME COLUMN external_id TO external_reference;
|
314
|
ALTER TABLE res_letterbox ADD COLUMN external_id jsonb DEFAULT '{}';
|
315
|
UPDATE res_letterbox SET external_id = json_build_object('publikId', external_reference) WHERE external_link is not NULL;
|
316
|
UPDATE res_letterbox SET external_reference = NULL WHERE external_link is not NULL;
|
317
|
END IF;
|
318
|
END$$;
|
319
|
|
320
|
|
321
|
|
322
|
CREATE OR REPLACE VIEW res_view_letterbox AS
|
323
|
SELECT r.tablename,
|
324
|
r.is_multi_docservers,
|
325
|
r.res_id,
|
326
|
r.type_id,
|
327
|
r.policy_id,
|
328
|
r.cycle_id,
|
329
|
d.description AS type_label,
|
330
|
d.doctypes_first_level_id,
|
331
|
dfl.doctypes_first_level_label,
|
332
|
dfl.css_style AS doctype_first_level_style,
|
333
|
d.doctypes_second_level_id,
|
334
|
dsl.doctypes_second_level_label,
|
335
|
dsl.css_style AS doctype_second_level_style,
|
336
|
r.format,
|
337
|
r.typist,
|
338
|
r.creation_date,
|
339
|
r.modification_date,
|
340
|
r.relation,
|
341
|
r.docserver_id,
|
342
|
r.folders_system_id,
|
343
|
f.folder_id,
|
344
|
f.destination AS folder_destination,
|
345
|
f.is_frozen AS folder_is_frozen,
|
346
|
r.path,
|
347
|
r.filename,
|
348
|
r.fingerprint,
|
349
|
r.offset_doc,
|
350
|
r.filesize,
|
351
|
r.status,
|
352
|
r.work_batch,
|
353
|
r.doc_date,
|
354
|
r.description,
|
355
|
r.source,
|
356
|
r.author,
|
357
|
r.reference_number,
|
358
|
r.external_reference,
|
359
|
r.external_id,
|
360
|
r.external_link,
|
361
|
r.departure_date,
|
362
|
r.opinion_limit_date,
|
363
|
r.department_number_id,
|
364
|
r.barcode,
|
365
|
r.external_signatory_book_id,
|
366
|
r.custom_t1 AS doc_custom_t1,
|
367
|
r.custom_t2 AS doc_custom_t2,
|
368
|
r.custom_t3 AS doc_custom_t3,
|
369
|
r.custom_t4 AS doc_custom_t4,
|
370
|
r.custom_t5 AS doc_custom_t5,
|
371
|
r.custom_t6 AS doc_custom_t6,
|
372
|
r.custom_t7 AS doc_custom_t7,
|
373
|
r.custom_t8 AS doc_custom_t8,
|
374
|
r.custom_t9 AS doc_custom_t9,
|
375
|
r.custom_t10 AS doc_custom_t10,
|
376
|
r.custom_t11 AS doc_custom_t11,
|
377
|
r.custom_t12 AS doc_custom_t12,
|
378
|
r.custom_t13 AS doc_custom_t13,
|
379
|
r.custom_t14 AS doc_custom_t14,
|
380
|
r.custom_t15 AS doc_custom_t15,
|
381
|
r.custom_d1 AS doc_custom_d1,
|
382
|
r.custom_d2 AS doc_custom_d2,
|
383
|
r.custom_d3 AS doc_custom_d3,
|
384
|
r.custom_d4 AS doc_custom_d4,
|
385
|
r.custom_d5 AS doc_custom_d5,
|
386
|
r.custom_d6 AS doc_custom_d6,
|
387
|
r.custom_d7 AS doc_custom_d7,
|
388
|
r.custom_d8 AS doc_custom_d8,
|
389
|
r.custom_d9 AS doc_custom_d9,
|
390
|
r.custom_d10 AS doc_custom_d10,
|
391
|
r.custom_n1 AS doc_custom_n1,
|
392
|
r.custom_n2 AS doc_custom_n2,
|
393
|
r.custom_n3 AS doc_custom_n3,
|
394
|
r.custom_n4 AS doc_custom_n4,
|
395
|
r.custom_n5 AS doc_custom_n5,
|
396
|
r.custom_f1 AS doc_custom_f1,
|
397
|
r.custom_f2 AS doc_custom_f2,
|
398
|
r.custom_f3 AS doc_custom_f3,
|
399
|
r.custom_f4 AS doc_custom_f4,
|
400
|
r.custom_f5 AS doc_custom_f5,
|
401
|
r.scan_date,
|
402
|
r.scan_user,
|
403
|
r.scan_location,
|
404
|
r.scan_wkstation,
|
405
|
r.scan_batch,
|
406
|
r.scan_postmark,
|
407
|
f.foldertype_id,
|
408
|
ft.foldertype_label,
|
409
|
f.custom_t1 AS fold_custom_t1,
|
410
|
f.custom_t2 AS fold_custom_t2,
|
411
|
f.custom_t3 AS fold_custom_t3,
|
412
|
f.custom_t4 AS fold_custom_t4,
|
413
|
f.custom_t5 AS fold_custom_t5,
|
414
|
f.custom_t6 AS fold_custom_t6,
|
415
|
f.custom_t7 AS fold_custom_t7,
|
416
|
f.custom_t8 AS fold_custom_t8,
|
417
|
f.custom_t9 AS fold_custom_t9,
|
418
|
f.custom_t10 AS fold_custom_t10,
|
419
|
f.custom_t11 AS fold_custom_t11,
|
420
|
f.custom_t12 AS fold_custom_t12,
|
421
|
f.custom_t13 AS fold_custom_t13,
|
422
|
f.custom_t14 AS fold_custom_t14,
|
423
|
f.custom_t15 AS fold_custom_t15,
|
424
|
f.custom_d1 AS fold_custom_d1,
|
425
|
f.custom_d2 AS fold_custom_d2,
|
426
|
f.custom_d3 AS fold_custom_d3,
|
427
|
f.custom_d4 AS fold_custom_d4,
|
428
|
f.custom_d5 AS fold_custom_d5,
|
429
|
f.custom_d6 AS fold_custom_d6,
|
430
|
f.custom_d7 AS fold_custom_d7,
|
431
|
f.custom_d8 AS fold_custom_d8,
|
432
|
f.custom_d9 AS fold_custom_d9,
|
433
|
f.custom_d10 AS fold_custom_d10,
|
434
|
f.custom_n1 AS fold_custom_n1,
|
435
|
f.custom_n2 AS fold_custom_n2,
|
436
|
f.custom_n3 AS fold_custom_n3,
|
437
|
f.custom_n4 AS fold_custom_n4,
|
438
|
f.custom_n5 AS fold_custom_n5,
|
439
|
f.custom_f1 AS fold_custom_f1,
|
440
|
f.custom_f2 AS fold_custom_f2,
|
441
|
f.custom_f3 AS fold_custom_f3,
|
442
|
f.custom_f4 AS fold_custom_f4,
|
443
|
f.custom_f5 AS fold_custom_f5,
|
444
|
f.is_complete AS fold_complete,
|
445
|
f.status AS fold_status,
|
446
|
f.subject AS fold_subject,
|
447
|
f.parent_id AS fold_parent_id,
|
448
|
f.folder_level,
|
449
|
f.folder_name,
|
450
|
f.creation_date AS fold_creation_date,
|
451
|
r.initiator,
|
452
|
r.destination,
|
453
|
r.dest_user,
|
454
|
r.confidentiality,
|
455
|
mlb.category_id,
|
456
|
mlb.exp_contact_id,
|
457
|
mlb.exp_user_id,
|
458
|
mlb.dest_user_id,
|
459
|
mlb.dest_contact_id,
|
460
|
mlb.address_id,
|
461
|
mlb.nature_id,
|
462
|
mlb.alt_identifier,
|
463
|
mlb.admission_date,
|
464
|
mlb.process_limit_date,
|
465
|
mlb.closing_date,
|
466
|
mlb.alarm1_date,
|
467
|
mlb.alarm2_date,
|
468
|
mlb.flag_alarm1,
|
469
|
mlb.flag_alarm2,
|
470
|
mlb.is_multicontacts,
|
471
|
r.subject,
|
472
|
r.identifier,
|
473
|
r.title,
|
474
|
r.priority,
|
475
|
r.locker_user_id,
|
476
|
r.locker_time,
|
477
|
ca.case_id,
|
478
|
ca.case_label,
|
479
|
ca.case_description,
|
480
|
en.entity_label,
|
481
|
en.entity_type AS entitytype,
|
482
|
cont.contact_id,
|
483
|
cont.firstname AS contact_firstname,
|
484
|
cont.lastname AS contact_lastname,
|
485
|
cont.society AS contact_society,
|
486
|
u.lastname AS user_lastname,
|
487
|
u.firstname AS user_firstname
|
488
|
FROM doctypes d,
|
489
|
doctypes_first_level dfl,
|
490
|
doctypes_second_level dsl,
|
491
|
res_letterbox r
|
492
|
LEFT JOIN entities en ON r.destination::text = en.entity_id::text
|
493
|
LEFT JOIN folders f ON r.folders_system_id = f.folders_system_id
|
494
|
LEFT JOIN cases_res cr ON r.res_id = cr.res_id
|
495
|
LEFT JOIN mlb_coll_ext mlb ON mlb.res_id = r.res_id
|
496
|
LEFT JOIN foldertypes ft ON f.foldertype_id = ft.foldertype_id AND f.status::text <> 'DEL'::text
|
497
|
LEFT JOIN cases ca ON cr.case_id = ca.case_id
|
498
|
LEFT JOIN contacts_v2 cont ON mlb.exp_contact_id = cont.contact_id OR mlb.dest_contact_id = cont.contact_id
|
499
|
LEFT JOIN users u ON mlb.exp_user_id::text = u.user_id::text OR mlb.dest_user_id::text = u.user_id::text
|
500
|
WHERE r.type_id = d.type_id AND d.doctypes_first_level_id = dfl.doctypes_first_level_id AND d.doctypes_second_level_id = dsl.doctypes_second_level_id;
|
501
|
|
502
|
DROP VIEW IF EXISTS view_contacts;
|
503
|
CREATE OR REPLACE VIEW view_contacts AS
|
504
|
SELECT c.contact_id, c.contact_type, c.is_corporate_person, c.society, c.society_short, c.firstname AS contact_firstname
|
505
|
, c.lastname AS contact_lastname, c.title AS contact_title, c.function AS contact_function, c.other_data AS contact_other_data
|
506
|
, c.user_id AS contact_user_id, c.entity_id AS contact_entity_id, c.creation_date, c.update_date, c.enabled AS contact_enabled, ca.id AS ca_id
|
507
|
, ca.contact_purpose_id, ca.departement, ca.firstname, ca.lastname, ca.title, ca.function, ca.occupancy
|
508
|
, ca.address_num, ca.address_street, ca.address_complement, ca.address_town, ca.address_postal_code, ca.address_country
|
509
|
, ca.phone, ca.email, ca.website, ca.salutation_header, ca.salutation_footer, ca.other_data, ca.user_id, ca.entity_id, ca.is_private, ca.enabled, ca.external_id
|
510
|
, cp.label as contact_purpose_label, ct.label as contact_type_label
|
511
|
FROM contacts_v2 c
|
512
|
RIGHT JOIN contact_addresses ca ON c.contact_id = ca.contact_id
|
513
|
LEFT JOIN contact_purposes cp ON ca.contact_purpose_id = cp.id
|
514
|
LEFT JOIN contact_types ct ON c.contact_type = ct.id;
|
515
|
|
516
|
DROP VIEW IF EXISTS res_view_attachments;
|
517
|
CREATE OR REPLACE VIEW res_view_attachments AS
|
518
|
SELECT '0' as res_id, res_id as res_id_version, title, subject, description, type_id, format, typist,
|
519
|
creation_date, fulltext_result, author, identifier, source, relation, doc_date, docserver_id, folders_system_id, path,
|
520
|
filename, offset_doc, fingerprint, filesize, status, destination, validation_date, effective_date, origin, priority, initiator, dest_user, external_id,
|
521
|
coll_id, dest_contact_id, dest_address_id, updated_by, is_multicontacts, is_multi_docservers, res_id_master, attachment_type, attachment_id_master, in_signature_book, in_send_attach, signatory_user_serial_id
|
522
|
FROM res_version_attachments
|
523
|
UNION ALL
|
524
|
SELECT res_id, '0' as res_id_version, title, subject, description, type_id, format, typist,
|
525
|
creation_date, fulltext_result, author, identifier, source, relation, doc_date, docserver_id, folders_system_id, path,
|
526
|
filename, offset_doc, fingerprint, filesize, status, destination, validation_date, effective_date, origin, priority, initiator, dest_user, external_id,
|
527
|
coll_id, dest_contact_id, dest_address_id, updated_by, is_multicontacts, is_multi_docservers, res_id_master, attachment_type, '0', in_signature_book, in_send_attach, signatory_user_serial_id
|
528
|
FROM res_attachments;
|