-
-
Notifications
You must be signed in to change notification settings - Fork 2.6k
/
3.9.0-4.0.0.sql
549 lines (437 loc) · 33 KB
/
3.9.0-4.0.0.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
--
-- Be carefull to requests order.
-- This file must be loaded by calling /install/index.php page
-- when current version is 4.0.0 or higher.
--
-- To rename a table: ALTER TABLE llx_table RENAME TO llx_table_new;
-- To add a column: ALTER TABLE llx_table ADD COLUMN newcol varchar(60) NOT NULL DEFAULT '0' AFTER existingcol;
-- To rename a column: ALTER TABLE llx_table CHANGE COLUMN oldname newname varchar(60);
-- To drop a column: ALTER TABLE llx_table DROP COLUMN oldname;
-- To change type of field: ALTER TABLE llx_table MODIFY COLUMN name varchar(60);
-- To drop a foreign key: ALTER TABLE llx_table DROP FOREIGN KEY fk_name;
-- To drop an index: -- VMYSQL4.0 DROP INDEX nomindex on llx_table
-- To drop an index: -- VPGSQL8.0 DROP INDEX nomindex
-- To restrict request to Mysql version x.y minimum use -- VMYSQLx.y
-- To restrict request to Pgsql version x.y minimum use -- VPGSQLx.y
-- To make pk to be auto increment (mysql): VMYSQL4.3 ALTER TABLE llx_c_shipment_mode CHANGE COLUMN rowid rowid INTEGER NOT NULL AUTO_INCREMENT;
-- To make pk to be auto increment (postgres): VPGSQL8.2 NOT POSSIBLE. MUST DELETE/CREATE TABLE
-- To set a field as NULL: VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name DROP NOT NULL;
-- To set a field as default NULL: VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name SET DEFAULT NULL;
-- Note: fields with type BLOB/TEXT can't have default value.
-- -- VPGSQL8.2 DELETE FROM llx_usergroup_user WHERE fk_user NOT IN (SELECT rowid from llx_user);
-- -- VMYSQL4.1 DELETE FROM llx_usergroup_user WHERE fk_usergroup NOT IN (SELECT rowid from llx_usergroup);
-- Drop old table not used (Informations are already presents in llx_accounting_bookkeeping)
DROP TABLE llx_accountingtransaction;
DROP TABLE llx_accountingdebcred;
-- Already into 3.9 but we do it again to be sure
ALTER TABLE llx_product ADD COLUMN localtax1_type varchar(10) NOT NULL DEFAULT '0' after localtax1_tx;
ALTER TABLE llx_product ADD COLUMN localtax2_type varchar(10) NOT NULL DEFAULT '0' after localtax2_tx;
ALTER TABLE llx_product_price ADD COLUMN localtax1_type varchar(10) NOT NULL DEFAULT '0' after localtax1_tx;
ALTER TABLE llx_product_price ADD COLUMN localtax2_type varchar(10) NOT NULL DEFAULT '0' after localtax2_tx;
ALTER TABLE llx_product_customer_price ADD COLUMN localtax1_type varchar(10) NOT NULL DEFAULT '0' after localtax1_tx;
ALTER TABLE llx_product_customer_price ADD COLUMN localtax2_type varchar(10) NOT NULL DEFAULT '0' after localtax2_tx;
ALTER TABLE llx_product_customer_price_log ADD COLUMN localtax1_type varchar(10) NOT NULL DEFAULT '0' after localtax1_tx;
ALTER TABLE llx_product_customer_price_log ADD COLUMN localtax2_type varchar(10) NOT NULL DEFAULT '0' after localtax2_tx;
ALTER TABLE llx_opensurvey_sondage ADD COLUMN status integer DEFAULT 1 after date_fin;
ALTER TABLE llx_expedition ADD COLUMN billed smallint DEFAULT 0;
insert into llx_c_type_contact(rowid, element, source, code, libelle, active ) values (150, 'dolresource','internal', 'USERINCHARGE', 'In charge of resource', 1);
insert into llx_c_type_contact(rowid, element, source, code, libelle, active ) values (151, 'dolresource','external', 'THIRDINCHARGE', 'In charge of resource', 1);
DELETE FROM llx_user_param where param = 'MAIN_THEME' and value in ('auguria', 'amarok', 'cameleo');
-- DROP TABLE llx_product_lot;
CREATE TABLE llx_product_lot (
rowid integer AUTO_INCREMENT PRIMARY KEY,
entity integer DEFAULT 1,
fk_product integer NOT NULL, -- Id of product
batch varchar(30) DEFAULT NULL, -- Lot or serial number
eatby date DEFAULT NULL, -- Eatby date
sellby date DEFAULT NULL, -- Sellby date
datec datetime,
tms timestamp,
fk_user_creat integer,
fk_user_modif integer,
import_key integer
) ENGINE=InnoDB;
ALTER TABLE llx_product_lot ADD UNIQUE INDEX uk_product_lot(fk_product, batch);
-- VPGSQL8.2 ALTER TABLE llx_product_lot ALTER COLUMN entity SET DEFAULT 1;
ALTER TABLE llx_product_lot MODIFY COLUMN entity integer DEFAULT 1;
UPDATE llx_product_lot SET entity = 1 WHERE entity IS NULL;
DROP TABLE llx_stock_serial;
ALTER TABLE llx_product ADD COLUMN note_public text;
ALTER TABLE llx_user ADD COLUMN note_public text;
ALTER TABLE llx_c_type_contact ADD COLUMN position integer NOT NULL DEFAULT 0;
ALTER TABLE llx_product ADD COLUMN model_pdf varchar(255) default '';
ALTER TABLE llx_product ADD COLUMN width float DEFAULT NULL;
ALTER TABLE llx_product ADD COLUMN width_units tinyint DEFAULT NULL;
ALTER TABLE llx_product ADD COLUMN height float DEFAULT NULL;
ALTER TABLE llx_product ADD COLUMN height_units tinyint DEFAULT NULL;
ALTER TABLE llx_product ADD COLUMN default_vat_code varchar(10) after cost_price;
ALTER TABLE llx_product MODIFY COLUMN stock real;
CREATE TABLE llx_categorie_user
(
fk_categorie integer NOT NULL,
fk_user integer NOT NULL,
import_key varchar(14)
) ENGINE=innodb;
ALTER TABLE llx_categorie_user ADD PRIMARY KEY pk_categorie_user (fk_categorie, fk_user);
ALTER TABLE llx_categorie_user ADD INDEX idx_categorie_user_fk_categorie (fk_categorie);
ALTER TABLE llx_categorie_user ADD INDEX idx_categorie_user_fk_user (fk_user);
ALTER TABLE llx_categorie_user ADD CONSTRAINT fk_categorie_user_categorie_rowid FOREIGN KEY (fk_categorie) REFERENCES llx_categorie (rowid);
ALTER TABLE llx_categorie_user ADD CONSTRAINT fk_categorie_user_fk_user FOREIGN KEY (fk_user) REFERENCES llx_user (rowid);
ALTER TABLE llx_accounting_bookkeeping ADD COLUMN validated tinyint DEFAULT 0 NOT NULL;
ALTER TABLE llx_bank_account MODIFY COLUMN accountancy_journal varchar(16) DEFAULT NULL;
ALTER TABLE llx_fichinter ADD COLUMN datet date after duree;
ALTER TABLE llx_fichinter ADD COLUMN datee date after duree;
ALTER TABLE llx_fichinter ADD COLUMN dateo date after duree;
ALTER TABLE llx_projet ADD COLUMN opp_percent double(5,2) after fk_opp_status;
UPDATE llx_projet as p set opp_percent = (SELECT percent from llx_c_lead_status as cls where cls.rowid = p.fk_opp_status) where opp_percent IS NULL;
ALTER TABLE llx_overwrite_trans ADD UNIQUE INDEX uk_overwrite_trans(lang, transkey);
ALTER TABLE llx_cronjob MODIFY COLUMN unitfrequency varchar(255) NOT NULL DEFAULT '3600';
ALTER TABLE llx_cronjob ADD COLUMN test varchar(255) DEFAULT '1';
ALTER TABLE llx_facture ADD INDEX idx_facture_fk_statut (fk_statut);
ALTER TABLE llx_facture ADD COLUMN date_pointoftax date DEFAULT NULL;
UPDATE llx_projet as p set p.opp_percent = (SELECT percent FROM llx_c_lead_status as cls WHERE cls.rowid = p.fk_opp_status) WHERE p.opp_percent IS NULL AND p.fk_opp_status IS NOT NULL;
ALTER TABLE llx_facturedet ADD COLUMN fk_contract_line integer NULL AFTER rang;
ALTER TABLE llx_facturedet_rec ADD COLUMN import_key varchar(14);
ALTER TABLE llx_chargesociales ADD COLUMN import_key varchar(14);
ALTER TABLE llx_tva ADD COLUMN import_key varchar(14);
--DROP TABLE llx_website_page;
--DROP TABLE llx_website;
CREATE TABLE llx_website
(
rowid integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
entity integer DEFAULT 1,
ref varchar(24) NOT NULL,
description varchar(255),
status integer,
fk_default_home integer,
date_creation datetime,
date_modification datetime,
tms timestamp
) ENGINE=innodb;
ALTER TABLE llx_website ADD COLUMN fk_default_home integer;
ALTER TABLE llx_website CHANGE COLUMN shortname ref varchar(24) NOT NULL;
ALTER TABLE llx_website ADD UNIQUE INDEX uk_website_ref (ref, entity);
CREATE TABLE llx_website_page
(
rowid integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
fk_website integer,
pageurl varchar(16) NOT NULL,
title varchar(255),
description varchar(255),
keywords varchar(255),
content text,
status integer,
date_creation datetime,
date_modification datetime,
tms timestamp
) ENGINE=innodb;
ALTER TABLE llx_website_page ADD UNIQUE INDEX uk_website_page_url (fk_website,pageurl);
ALTER TABLE llx_website_page ADD CONSTRAINT fk_website_page_website FOREIGN KEY (fk_website) REFERENCES llx_website (rowid);
CREATE TABLE llx_c_format_cards
(
rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
code varchar(50) NOT NULL,
name varchar(50) NOT NULL,
paper_size varchar(20) NOT NULL,
orientation varchar(1) NOT NULL,
metric varchar(5) NOT NULL,
leftmargin double(24,8) NOT NULL,
topmargin double(24,8) NOT NULL,
nx integer NOT NULL,
ny integer NOT NULL,
spacex double(24,8) NOT NULL,
spacey double(24,8) NOT NULL,
width double(24,8) NOT NULL,
height double(24,8) NOT NULL,
font_size integer NOT NULL,
custom_x double(24,8) NOT NULL,
custom_y double(24,8) NOT NULL,
active integer NOT NULL
) ENGINE=InnoDB;
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (1, '5160', 'Avery-5160, WL-875WX', 'letter', 'P', 'mm', 5.58165000, 12.70000000, 3, 10, 3.55600000, 0.00000000, 65.87490000, 25.40000000, 7, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (2, '5161', 'Avery-5161, WL-75WX', 'letter', 'P', 'mm', 4.44500000, 12.70000000, 2, 10, 3.96800000, 0.00000000, 101.60000000, 25.40000000, 7, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (3, '5162', 'Avery-5162, WL-100WX', 'letter', 'P', 'mm', 3.87350000, 22.35200000, 2, 7, 4.95400000, 0.00000000, 101.60000000, 33.78100000, 8, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (4, '5163', 'Avery-5163, WL-125WX', 'letter', 'P', 'mm', 4.57200000, 12.70000000, 2, 5, 3.55600000, 0.00000000, 101.60000000, 50.80000000, 10, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (5, '5164', '5164 (Letter)', 'letter', 'P', 'in', 0.14800000, 0.50000000, 2, 3, 0.20310000, 0.00000000, 4.00000000, 3.33000000, 12, 0.00000000, 0.00000000, 0);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (6, '8600', 'Avery-8600', 'letter', 'P', 'mm', 7.10000000, 19.00000000, 3, 10, 9.50000000, 3.10000000, 66.60000000, 25.40000000, 7, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (7, '99012', 'DYMO 99012 89*36mm', 'custom', 'L', 'mm', 1.00000000, 1.00000000, 1, 1, 0.00000000, 0.00000000, 36.00000000, 89.00000000, 10, 36.00000000, 89.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (8, '99014', 'DYMO 99014 101*54mm', 'custom', 'L', 'mm', 1.00000000, 1.00000000, 1, 1, 0.00000000, 0.00000000, 54.00000000, 101.00000000, 10, 54.00000000, 101.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (9, 'AVERYC32010', 'Avery-C32010', 'A4', 'P', 'mm', 15.00000000, 13.00000000, 2, 5, 10.00000000, 0.00000000, 85.00000000, 54.00000000, 10, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (10, 'CARD', 'Dolibarr Business cards', 'A4', 'P', 'mm', 15.00000000, 15.00000000, 2, 5, 0.00000000, 0.00000000, 85.00000000, 54.00000000, 10, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (11, 'L7163', 'Avery-L7163', 'A4', 'P', 'mm', 5.00000000, 15.00000000, 2, 7, 2.50000000, 0.00000000, 99.10000000, 38.10000000, 8, 0.00000000, 0.00000000, 1);
ALTER TABLE llx_extrafields ADD COLUMN ishidden integer DEFAULT 0;
ALTER TABLE llx_paiementfourn ADD COLUMN ref varchar(30) AFTER rowid;
ALTER TABLE llx_paiementfourn ADD COLUMN entity integer AFTER ref;
CREATE TABLE llx_multicurrency
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
date_create datetime DEFAULT NULL,
code varchar(255) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
entity integer DEFAULT 1,
fk_user integer DEFAULT NULL
) ENGINE=innodb;
CREATE TABLE llx_multicurrency_rate
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
date_sync datetime DEFAULT NULL,
rate double NOT NULL DEFAULT 0,
fk_multicurrency integer NOT NULL
) ENGINE=innodb;
ALTER TABLE llx_societe ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_societe ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_societe ADD COLUMN fk_shipping_method integer AFTER cond_reglement_supplier;
ALTER TABLE llx_product_price ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_product_price ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_product_price ADD COLUMN multicurrency_price double(24,8) DEFAULT 0;
ALTER TABLE llx_commande ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_commande ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_commande ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_commande ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_commande ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_commande ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_commandedet ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_commandedet ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_commandedet ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_commandedet ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_commandedet ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_commandedet ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_commande_fournisseur ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_commande_fournisseur ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_commande_fournisseur ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_commande_fournisseur ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_commande_fournisseur ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_commande_fournisseur ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_commande_fournisseurdet ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_commande_fournisseurdet ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_commande_fournisseurdet ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_commande_fournisseurdet ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_commande_fournisseurdet ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_commande_fournisseurdet ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_fourn ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_facture_fourn ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_facture_fourn ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_facture_fourn ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_fourn ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_fourn ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_fourn_det ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_facture_fourn_det ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_facture_fourn_det ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_fourn_det ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_fourn_det ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_fourn_det ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_facture ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_facture ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_facture ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_facture ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_facture ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_facture ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_facturedet ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_facturedet ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_propal ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_propal ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_propal ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_propal ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_propal ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_propal ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_propaldet ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_propaldet ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_propaldet ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_propaldet ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_propaldet ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_propaldet ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
-- Add for recurring template invoices
ALTER TABLE llx_facture_rec ADD COLUMN auto_validate integer DEFAULT 0;
ALTER TABLE llx_facture_rec ADD COLUMN fk_account integer DEFAULT 0;
ALTER TABLE llx_facture_rec ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_facture_rec ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_facture_rec ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_facture_rec ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_rec ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_rec ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet_rec ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_facturedet_rec ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_facturedet_rec ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet_rec ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet_rec ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet_rec ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_contratdet ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_contratdet ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_contratdet ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_contratdet ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_contratdet ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_contratdet ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_paiement ADD COLUMN multicurrency_amount double(24,8) DEFAULT 0;
ALTER TABLE llx_paiement_facture ADD COLUMN multicurrency_amount double(24,8) DEFAULT 0;
ALTER TABLE llx_paiementfourn ADD COLUMN multicurrency_amount double(24,8) DEFAULT 0;
ALTER TABLE llx_paiementfourn_facturefourn ADD COLUMN multicurrency_amount double(24,8) DEFAULT 0;
ALTER TABLE llx_societe_remise_except ADD COLUMN multicurrency_amount_ht double(24,8) DEFAULT 0 NOT NULL;
ALTER TABLE llx_societe_remise_except ADD COLUMN multicurrency_amount_tva double(24,8) DEFAULT 0 NOT NULL;
ALTER TABLE llx_societe_remise_except ADD COLUMN multicurrency_amount_ttc double(24,8) DEFAULT 0 NOT NULL;
ALTER TABLE llx_supplier_proposal ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_supplier_proposal ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_supplier_proposal ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_supplier_proposal ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_supplier_proposal ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_supplier_proposal ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_supplier_proposaldet ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_supplier_proposaldet ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_supplier_proposaldet ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_supplier_proposaldet ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_supplier_proposaldet ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_supplier_proposaldet ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_expensereport ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_expensereport ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_expensereport ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport_det ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_expensereport_det ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_expensereport_det ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport_det ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport_det ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport_det ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport_det ADD COLUMN fk_facture integer DEFAULT 0;
ALTER TABLE llx_product_lang ADD COLUMN import_key varchar(14) DEFAULT NULL;
ALTER TABLE llx_actioncomm MODIFY COLUMN elementtype varchar(255) DEFAULT NULL;
ALTER TABLE llx_accounting_system DROP COLUMN fk_pays;
ALTER TABLE llx_accounting_account ADD COLUMN fk_accounting_category integer DEFAULT 0 after label;
CREATE TABLE llx_c_accounting_category (
rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
code varchar(16) NOT NULL,
label varchar(255) NOT NULL,
range_account varchar(255) NOT NULL,
sens tinyint NOT NULL DEFAULT '0', -- For international accounting 0 : credit - debit / 1 : debit - credit
category_type tinyint NOT NULL DEFAULT '0', -- Field calculated or not
formula varchar(255) NOT NULL, -- Example : 1 + 2 (rowid of the category)
position integer DEFAULT 0,
fk_country integer DEFAULT NULL, -- This category is dedicated to a country
active integer DEFAULT 1
) ENGINE=innodb;
ALTER TABLE llx_c_accounting_category ADD UNIQUE INDEX uk_c_accounting_category(code);
INSERT INTO llx_c_accounting_category (rowid, code, label, range_account, sens, category_type, formula, position, fk_country, active) VALUES ( 1,'VTE',"Ventes de marchandises", '707xxx', 0, 0, '', '10', 1, 1);
INSERT INTO llx_c_accounting_category (rowid, code, label, range_account, sens, category_type, formula, position, fk_country, active) VALUES ( 2,'MAR',"Coût d'achats marchandises vendues", '603xxx | 607xxx | 609xxx', 0, 0, '', '20', 1, 1);
INSERT INTO llx_c_accounting_category (rowid, code, label, range_account, sens, category_type, formula, position, fk_country, active) VALUES ( 3,'MARGE',"Marge commerciale", '', 0, 1, '1 + 2', '30', 1, 1);
UPDATE llx_accounting_account SET account_parent = '0' WHERE account_parent = '';
-- VMYSQL4.1 ALTER TABLE llx_accounting_account MODIFY COLUMN account_parent varchar(32) DEFAULT '0';
-- VPGSQL8.2 ALTER TABLE llx_accounting_account ALTER COLUMN account_parent SET DEFAULT '0';
CREATE TABLE llx_accounting_journal
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
code varchar(32) NOT NULL,
label varchar(128) NOT NULL,
nature smallint DEFAULT 0 NOT NULL, -- type of journals (Sale / purchase / bank / various operations)
active smallint DEFAULT 0
)ENGINE=innodb;
ALTER TABLE llx_accounting_journal ADD UNIQUE INDEX uk_accounting_journal_code (code);
-- VMYSQL4.1 DROP INDEX uk_bordereau_cheque ON llx_bordereau_cheque;
-- VPGSQL8.2 DROP INDEX uk_bordereau_cheque;
ALTER TABLE llx_bordereau_cheque CHANGE COLUMN number ref VARCHAR(30) NOT NULL;
CREATE UNIQUE INDEX uk_bordereau_cheque ON llx_bordereau_cheque (ref, entity);
ALTER TABLE llx_societe_rib ADD COLUMN date_rum date after rum;
-- Add more action to log
update llx_c_action_trigger set rang = 140 where code = 'PROJECT_CREATE';
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROJECT_MODIFY','Project modified','Executed when a project is modified','project',141);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROJECT_DELETE','Project deleted','Executed when a project is deleted','project',142);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SUPPLIER_CREATE','Supplier order validated','Executed when a supplier order is validated','order_supplier',11);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SUPPLIER_SUBMIT','Supplier order request submited','Executed when a supplier order is approved','order_supplier',12);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SUPPLIER_RECEIVE','Supplier order request received','Executed when a supplier order is received','order_supplier',12);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SUPPLIER_CLASSIFY_BILLED','Supplier order set billed','Executed when a supplier order is set as billed','order_supplier',14);
ALTER TABLE llx_product_fournisseur_price ADD supplier_reputation varchar(10) NULL;
ALTER TABLE llx_product ADD COLUMN default_vat_code varchar(10) after cost_price;
CREATE TABLE llx_categorie_account
(
fk_categorie integer NOT NULL,
fk_account integer NOT NULL,
import_key varchar(14)
) ENGINE=innodb;
ALTER TABLE llx_categorie_account ADD PRIMARY KEY pk_categorie_account (fk_categorie, fk_account);
ALTER TABLE llx_categorie_account ADD INDEX idx_categorie_account_fk_categorie (fk_categorie);
ALTER TABLE llx_categorie_account ADD INDEX idx_categorie_account_fk_account (fk_account);
ALTER TABLE llx_categorie_account ADD CONSTRAINT fk_categorie_account_categorie_rowid FOREIGN KEY (fk_categorie) REFERENCES llx_categorie (rowid);
ALTER TABLE llx_categorie_account ADD CONSTRAINT fk_categorie_account_fk_account FOREIGN KEY (fk_account) REFERENCES llx_bank_account (rowid);
-- Delete old deprecated field
ALTER TABLE llx_product_stock DROP COLUMN pmp;
ALTER TABLE llx_resource ADD COLUMN asset_number varchar(255) after ref;
ALTER TABLE llx_resource ADD COLUMN datec datetime DEFAULT NULL;
ALTER TABLE llx_resource ADD COLUMN date_valid datetime DEFAULT NULL;
ALTER TABLE llx_resource ADD COLUMN fk_user_author integer DEFAULT NULL;
ALTER TABLE llx_resource ADD COLUMN fk_user_modif integer DEFAULT NULL;
ALTER TABLE llx_resource ADD COLUMN fk_user_valid integer DEFAULT NULL;
ALTER TABLE llx_resource ADD COLUMN fk_statut smallint NOT NULL DEFAULT '0';
ALTER TABLE llx_resource ADD COLUMN import_key varchar(14);
ALTER TABLE llx_resource ADD COLUMN extraparams varchar(255);
ALTER TABLE llx_element_resources ADD COLUMN duree real; -- total duration of using ressource
UPDATE llx_element_resources SET resource_type = 'dolresource' WHERE resource_type = 'resource';
CREATE TABLE llx_advtargetemailing
(
rowid integer NOT NULL auto_increment PRIMARY KEY,
name varchar(200) NOT NULL,
entity integer NOT NULL DEFAULT 1,
fk_mailing integer NOT NULL,
filtervalue text,
fk_user_author integer NOT NULL,
datec datetime NOT NULL,
fk_user_mod integer NOT NULL,
tms timestamp NOT NULL
)ENGINE=InnoDB;
ALTER TABLE llx_advtargetemailing ADD UNIQUE INDEX uk_advtargetemailing_name (name);
update llx_product_batch set batch = '000000' where batch = 'Non défini';
update llx_product_batch set batch = '000000' where batch = 'Non défini';
update llx_product_batch set batch = '000000' where batch = 'Undefined';
update llx_product_batch set batch = '000000' where batch = '';
update llx_product_batch set batch = '000000' where batch = '';
update llx_product_batch set batch = '000000' where batch = '';
update llx_product_lot set batch = '000000' where batch = 'Undefined';
update llx_stock_mouvement set batch = '000000' where batch = 'Undefined';
ALTER TABLE llx_import_model MODIFY COLUMN type varchar(50);
UPDATE llx_projet set fk_opp_status = NULL where fk_opp_status = -1;
UPDATE llx_c_lead_status set code = 'WON' where code = 'WIN';
UPDATE llx_c_lead_status set percent = 100 where code = 'WON';
CREATE TABLE llx_oauth_token (
rowid integer AUTO_INCREMENT PRIMARY KEY,
service varchar(36),
token text,
fk_user integer,
fk_adherent integer,
entity integer
)ENGINE=InnoDB;
CREATE TABLE llx_oauth_state (
rowid integer AUTO_INCREMENT PRIMARY KEY,
service varchar(36),
state varchar(128),
fk_user integer,
fk_adherent integer,
entity integer
)ENGINE=InnoDB;
-- At end (higher risk of error)
-- VMYSQL4.1 ALTER TABLE llx_c_type_resource CHANGE COLUMN rowid rowid integer NOT NULL AUTO_INCREMENT;
ALTER TABLE llx_product_batch ADD UNIQUE INDEX uk_product_batch (fk_product_stock, batch);
-- Panama datas
insert into llx_c_tva(rowid,fk_pays,taux,recuperableonly,note,active) values (1781, 178, '7','0','ITBMS standard rate',1);
insert into llx_c_tva(rowid,fk_pays,taux,recuperableonly,note,active) values (1782, 178, '0','0','ITBMS Rate 0',1);
INSERT INTO llx_c_regions (fk_pays, code_region, cheflieu, tncc, nom, active) values ( 178, 17801, '', 0, 'Panama', 1);
INSERT INTO llx_c_departements (code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('PA-1', 17801, '', 0, '', 'Bocas del Toro', 1);
INSERT INTO llx_c_departements (code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('PA-2', 17801, '', 0, '', 'Coclé', 1);
INSERT INTO llx_c_departements (code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('PA-3', 17801, '', 0, '', 'Colón', 1);
INSERT INTO llx_c_departements (code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('PA-4', 17801, '', 0, '', 'Chiriquí', 1);
INSERT INTO llx_c_departements (code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('PA-5', 17801, '', 0, '', 'Darién', 1);
INSERT INTO llx_c_departements (code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('PA-6', 17801, '', 0, '', 'Herrera', 1);
INSERT INTO llx_c_departements (code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('PA-7', 17801, '', 0, '', 'Los Santos', 1);
INSERT INTO llx_c_departements (code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('PA-8', 17801, '', 0, '', 'Panamá', 1);
INSERT INTO llx_c_departements (code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('PA-9', 17801, '', 0, '', 'Veraguas', 1);
INSERT INTO llx_c_departements (code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('PA-13', 17801, '', 0, '', 'Panamá Oeste', 1);
INSERT INTO llx_c_forme_juridique (fk_pays, code, libelle, active) VALUES (178, '17801', 'Empresa individual', 1);
INSERT INTO llx_c_forme_juridique (fk_pays, code, libelle, active) VALUES (178, '17802', 'Asociación General', 1);
INSERT INTO llx_c_forme_juridique (fk_pays, code, libelle, active) VALUES (178, '17803', 'Sociedad de Responsabilidad Limitada', 1);
INSERT INTO llx_c_forme_juridique (fk_pays, code, libelle, active) VALUES (178, '17804', 'Sociedad Civil', 1);
INSERT INTO llx_c_forme_juridique (fk_pays, code, libelle, active) VALUES (178, '17805', 'Sociedad Anónima', 1);
-- VMYSQL4.1 ALTER TABLE llx_establishment CHANGE COLUMN fk_user_mod fk_user_mod integer NULL;
-- VPGSQL8.2 ALTER TABLE llx_establishment ALTER COLUMN fk_user_mod DROP NOT NULL;
ALTER TABLE llx_multicurrency_rate ADD COLUMN entity integer DEFAULT 1;
ALTER TABLE llx_user MODIFY COLUMN login varchar(50);