Project

General

Profile

sgami1904.sql

Florian AZIZIAN, 07/17/2019 04:31 PM

 
1
-- *************************************************************************--
2
--                                                                          --
3
--                                                                          --
4
-- Model migration script - 18.10 to 19.04                               --
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
/* Redirected Baskets */
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
/* CONFIGURATIONS */
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
/* EMAILS */
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
/* SHIPPING TEMPLATES */
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
/* SERVICES */
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
/* Replace occurence in basket_clause */
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
/* PARAM LIST DISPLAY */
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
/* ACTIONS */
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
/*SHIPPING*/
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
/* Acknowledgement Receipts */
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
/* Foldertypes */
237
UPDATE foldertypes SET coll_id = 'letterbox_coll' WHERE coll_id <> 'letterbox_coll';
238

    
239
/* REFACTORING */
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
/* RE-CREATE VIEW*/
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;