1-- 2-- Be carefull to requests order. 3-- This file must be loaded by calling /install/index.php page 4-- when current version is 10.0.0 or higher. 5-- 6-- To restrict request to Mysql version x.y minimum use -- VMYSQLx.y 7-- To restrict request to Pgsql version x.y minimum use -- VPGSQLx.y 8-- To rename a table: ALTER TABLE llx_table RENAME TO llx_table_new; 9-- To add a column: ALTER TABLE llx_table ADD COLUMN newcol varchar(60) NOT NULL DEFAULT '0' AFTER existingcol; 10-- To rename a column: ALTER TABLE llx_table CHANGE COLUMN oldname newname varchar(60); 11-- To drop a column: ALTER TABLE llx_table DROP COLUMN oldname; 12-- To change type of field: ALTER TABLE llx_table MODIFY COLUMN name varchar(60); 13-- To drop a foreign key: ALTER TABLE llx_table DROP FOREIGN KEY fk_name; 14-- To create a unique index ALTER TABLE llx_table ADD UNIQUE INDEX uk_table_field (field); 15-- To drop an index: -- VMYSQL4.1 DROP INDEX nomindex on llx_table 16-- To drop an index: -- VPGSQL8.2 DROP INDEX nomindex 17-- To make pk to be auto increment (mysql): -- VMYSQL4.3 ALTER TABLE llx_table CHANGE COLUMN rowid rowid INTEGER NOT NULL AUTO_INCREMENT; 18-- To make pk to be auto increment (postgres): 19-- -- VPGSQL8.2 CREATE SEQUENCE llx_table_rowid_seq OWNED BY llx_table.rowid; 20-- -- VPGSQL8.2 ALTER TABLE llx_table ADD PRIMARY KEY (rowid); 21-- -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN rowid SET DEFAULT nextval('llx_table_rowid_seq'); 22-- -- VPGSQL8.2 SELECT setval('llx_table_rowid_seq', MAX(rowid)) FROM llx_table; 23-- To set a field as NULL: -- VMYSQL4.3 ALTER TABLE llx_table MODIFY COLUMN name varchar(60) NULL; 24-- To set a field as NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name DROP NOT NULL; 25-- To set a field as NOT NULL: -- VMYSQL4.3 ALTER TABLE llx_table MODIFY COLUMN name varchar(60) NOT NULL; 26-- To set a field as NOT NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name SET NOT NULL; 27-- To set a field as default NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name SET DEFAULT NULL; 28-- Note: fields with type BLOB/TEXT can't have default value. 29 30-- Missing in 9.0 31 32ALTER TABLE llx_actioncomm MODIFY COLUMN code varchar(50); 33 34DROP TABLE llx_ticket_logs; 35 36CREATE TABLE llx_pos_cash_fence( 37 rowid INTEGER AUTO_INCREMENT PRIMARY KEY, 38 entity INTEGER DEFAULT 1 NOT NULL, 39 ref VARCHAR(64), 40 label VARCHAR(255), 41 opening double(24,8) default 0, 42 cash double(24,8) default 0, 43 card double(24,8) default 0, 44 cheque double(24,8) default 0, 45 status INTEGER, 46 date_creation DATETIME NOT NULL, 47 date_valid DATETIME, 48 day_close INTEGER, 49 month_close INTEGER, 50 year_close INTEGER, 51 posmodule VARCHAR(30), 52 posnumber VARCHAR(30), 53 fk_user_creat integer, 54 fk_user_valid integer, 55 tms TIMESTAMP NOT NULL, 56 import_key VARCHAR(14) 57) ENGINE=innodb; 58 59 60 61-- For 10.0 62 63UPDATE llx_chargesociales SET date_creation = tms WHERE date_creation IS NULL; 64 65DROP TABLE llx_cotisation; 66ALTER TABLE llx_accounting_bookkeeping DROP COLUMN validated; 67ALTER TABLE llx_accounting_bookkeeping_tmp DROP COLUMN validated; 68 69ALTER TABLE llx_loan ADD COLUMN insurance_amount double(24,8) DEFAULT 0; 70 71ALTER TABLE llx_facture DROP INDEX idx_facture_uk_facnumber; 72ALTER TABLE llx_facture CHANGE facnumber ref VARCHAR(30) NOT NULL; 73ALTER TABLE llx_facture ADD UNIQUE INDEX uk_facture_ref (ref, entity); 74 75insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('TICKET_CREATE','Ticket created','Executed when a ticket is created','ticket',161); 76insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('TICKET_MODIFY','Ticket modified','Executed when a ticket is modified','ticket',163); 77insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('TICKET_ASSIGNED','Ticket assigned','Executed when a ticket is assigned to another user','ticket',164); 78insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('TICKET_CLOSE','Ticket closed','Executed when a ticket is closed','ticket',165); 79insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('TICKET_SENTBYMAIL','Ticket message sent by email','Executed when a message is sent from the ticket record','ticket',166); 80insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('TICKET_DELETE','Ticket deleted','Executed when a ticket is deleted','ticket',167); 81 82create table llx_mailing_unsubscribe 83( 84 rowid integer AUTO_INCREMENT PRIMARY KEY, 85 entity integer DEFAULT 1 NOT NULL, -- multi company id 86 email varchar(255), 87 unsubscribegroup varchar(128) DEFAULT '', 88 ip varchar(128), 89 date_creat datetime, -- creation date 90 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 91)ENGINE=innodb; 92 93ALTER TABLE llx_mailing_unsubscribe ADD UNIQUE uk_mailing_unsubscribe(email, entity, unsubscribegroup); 94 95ALTER TABLE llx_adherent ADD gender VARCHAR(10); 96ALTER TABLE llx_adherent_type ADD morphy VARCHAR(3); 97ALTER TABLE llx_subscription ADD fk_type integer; 98 99UPDATE llx_subscription as s SET fk_type = (SELECT fk_adherent_type FROM llx_adherent as a where a.rowid = s.fk_adherent) where fk_type IS NULL; 100 101-- Add url_id into unique index of bank_url 102ALTER TABLE llx_bank_url DROP INDEX uk_bank_url; 103ALTER TABLE llx_bank_url ADD UNIQUE INDEX uk_bank_url (fk_bank, url_id, type); 104 105ALTER TABLE llx_actioncomm ADD COLUMN calling_duration integer; 106ALTER TABLE llx_actioncomm ADD COLUMN visibility varchar(12) DEFAULT 'default'; 107 108DROP TABLE llx_ticket_msg; 109 110ALTER TABLE llx_don ADD COLUMN fk_soc integer NULL; 111 112ALTER TABLE llx_payment_various ADD COLUMN subledger_account varchar(32); 113 114ALTER TABLE llx_prelevement_facture_demande ADD COLUMN entity integer; 115ALTER TABLE llx_prelevement_facture_demande ADD COLUMN sourcetype varchar(32); 116ALTER TABLE llx_prelevement_facture_demande ADD COLUMN ext_payment_id varchar(128) NULL; 117ALTER TABLE llx_prelevement_facture_demande ADD COLUMN ext_payment_site varchar(128) NULL; 118 119-- Fix if table exists 120ALTER TABLE llx_c_units DROP INDEX uk_c_units_code; 121ALTER TABLE llx_c_units ADD COLUMN scale integer; 122ALTER TABLE llx_c_units ADD COLUMN unit_type varchar(10); 123 124-- Create if table dos not exists 125CREATE TABLE llx_c_units( 126 rowid integer AUTO_INCREMENT PRIMARY KEY, 127 code varchar(3), 128 scale integer, 129 label varchar(50), 130 short_label varchar(5), 131 unit_type varchar(10), 132 active tinyint DEFAULT 1 NOT NULL 133) ENGINE=innodb; 134 135ALTER TABLE llx_c_units ADD UNIQUE uk_c_units_code(code); 136 137INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('T','3','WeightUnitton','T', 'weight', 1); 138INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('KG','0','WeightUnitkg','kg', 'weight', 1); 139INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('G','-3','WeightUnitg','g', 'weight', 1); 140INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('MG','-6','WeightUnitmg','mg', 'weight', 1); 141INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('OZ','98','WeightUnitounce','Oz', 'weight', 1); 142INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('LB','99','WeightUnitpound','lb', 'weight', 1); 143 144INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('M','0','SizeUnitm','m', 'size', 1); 145INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('DM','-1','SizeUnitdm','dm', 'size', 1); 146INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('CM','-2','SizeUnitcm','cm', 'size', 1); 147INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('MM','-3','SizeUnitmm','mm', 'size', 1); 148INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('FT','98','SizeUnitfoot','ft', 'size', 1); 149INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('IN','99','SizeUnitinch','in', 'size', 1); 150 151INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('M2','0','SurfaceUnitm2','m2', 'surface', 1); 152INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('DM2','-2','SurfaceUnitdm2','dm2', 'surface', 1); 153INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('CM2','-4','SurfaceUnitcm2','cm2', 'surface', 1); 154INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('MM2','-6','SurfaceUnitmm2','mm2', 'surface', 1); 155INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('FT2','98','SurfaceUnitfoot2','ft2', 'surface', 1); 156INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('IN2','99','SurfaceUnitinch2','in2', 'surface', 1); 157 158 159INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('M3','0','VolumeUnitm3','m3', 'volume', 1); 160INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('DM3','-3','VolumeUnitdm3','dm3', 'volume', 1); 161INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('CM3','-6','VolumeUnitcm3','cm3', 'volume', 1); 162INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('MM3','-9','VolumeUnitmm3','mm3', 'volume', 1); 163INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('FT3','88','VolumeUnitfoot3','ft3', 'volume', 1); 164INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('IN3','89','VolumeUnitinch3','in3', 'volume', 1); 165INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('OZ3','97','VolumeUnitounce','Oz', 'volume', 1); 166INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('L', '98','VolumeUnitlitre','L', 'volume', 1); 167INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('GAL','99','VolumeUnitgallon','gal', 'volume', 1); 168 169INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('P','0','Piece','p', 'qty', 1); 170INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('SET', '0','Set','set', 'qty', 1); 171 172INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('S','0','second','s', 'time', 1); 173INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('MI','60','minute','i', 'time', 1); 174INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('H','3600','hour','h', 'time', 1); 175INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('D','86400','day','d', 'time', 1); 176INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('W','604800','week','w', 'time', 1); 177INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('MO','2629800','month','m', 'time', 1); 178INSERT INTO llx_c_units (code, scale, label, short_label, unit_type, active) VALUES ('Y','31557600','year','y', 'time', 1); 179 180UPDATE llx_c_units SET short_label = 'i' WHERE code = 'MI'; 181UPDATE llx_c_units SET unit_type = 'weight', short_label = 'kg', scale = 0 WHERE code = 'KG'; 182UPDATE llx_c_units SET unit_type = 'weight', short_label = 'g', scale = -3 WHERE code = 'G'; 183UPDATE llx_c_units SET unit_type = 'time' WHERE code IN ('S','H','D'); 184UPDATE llx_c_units SET unit_type = 'size' WHERE code IN ('M','LM'); 185UPDATE llx_c_units SET label = 'SizeUnitm', scale = 0 WHERE code IN ('M'); 186UPDATE llx_c_units SET active = 0, scale = 0 WHERE code IN ('LM'); 187UPDATE llx_c_units SET unit_type = 'surface', scale = 0 WHERE code IN ('M2'); 188UPDATE llx_c_units SET unit_type = 'volume', scale = 0 WHERE code IN ('M3','L'); 189UPDATE llx_c_units SET scale = -3, active = 0 WHERE code IN ('L'); 190UPDATE llx_c_units SET label = 'VolumeUnitm3' WHERE code IN ('M3'); 191UPDATE llx_c_units SET label = 'SurfaceUnitm2' WHERE code IN ('M2'); 192 193 194-- Default Warehouse id for a user 195ALTER TABLE llx_user ADD COLUMN fk_warehouse INTEGER NULL; 196 197-- Save informations for online / API shopping and push to invoice 198ALTER TABLE llx_commande ADD COLUMN module_source varchar(32); 199ALTER TABLE llx_commande ADD COLUMN pos_source varchar(32); 200 201 202ALTER TABLE llx_societe ADD COLUMN linkedin varchar(255) after whatsapp; 203ALTER TABLE llx_socpeople ADD COLUMN linkedin varchar(255) after whatsapp; 204ALTER TABLE llx_adherent ADD COLUMN linkedin varchar(255) after whatsapp; 205ALTER TABLE llx_user ADD COLUMN linkedin varchar(255) after whatsapp; 206 207ALTER TABLE llx_expensereport_det ADD COLUMN fk_ecm_files integer DEFAULT NULL; 208 209ALTER TABLE llx_expensereport ADD COLUMN paid smallint default 0 NOT NULL; 210UPDATE llx_expensereport set paid = 1 WHERE fk_statut = 6 and paid = 0; 211 212 213CREATE TABLE llx_bom_bom( 214 -- BEGIN MODULEBUILDER FIELDS 215 rowid integer AUTO_INCREMENT PRIMARY KEY NOT NULL, 216 entity integer DEFAULT 1 NOT NULL, 217 ref varchar(128) NOT NULL, 218 label varchar(255), 219 description text, 220 note_public text, 221 note_private text, 222 fk_product integer, 223 qty double(24,8), 224 efficiency double(8,4), 225 date_creation datetime NOT NULL, 226 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 227 date_valid datetime, 228 fk_user_creat integer NOT NULL, 229 fk_user_modif integer, 230 fk_user_valid integer, 231 import_key varchar(14), 232 status integer NOT NULL 233 -- END MODULEBUILDER FIELDS 234) ENGINE=innodb; 235 236ALTER TABLE llx_bom_bom ADD COLUMN efficiency double(8,4) DEFAULT 1; 237ALTER TABLE llx_bom_bom ADD COLUMN entity integer DEFAULT 1 NOT NULL; 238ALTER TABLE llx_bom_bom ADD COLUMN date_valid datetime; 239 240create table llx_bom_bom_extrafields 241( 242 rowid integer AUTO_INCREMENT PRIMARY KEY, 243 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 244 fk_object integer NOT NULL, 245 import_key varchar(14) -- import key 246) ENGINE=innodb; 247 248CREATE TABLE llx_bom_bomline( 249 -- BEGIN MODULEBUILDER FIELDS 250 rowid integer AUTO_INCREMENT PRIMARY KEY NOT NULL, 251 fk_bom integer NOT NULL, 252 fk_product integer NOT NULL, 253 fk_bom_child integer NULL, 254 description text, 255 import_key varchar(14), 256 qty double(24,8) NOT NULL, 257 efficiency double(8,4) NOT NULL DEFAULT 1, 258 position integer NOT NULL 259 -- END MODULEBUILDER FIELDS 260) ENGINE=innodb; 261 262ALTER TABLE llx_bom_bomline ADD COLUMN efficiency double(8,4) DEFAULT 1; 263ALTER TABLE llx_bom_bomline ADD COLUMN fk_bom_child integer NULL; 264ALTER TABLE llx_bom_bomline ADD COLUMN position integer NOT NULL; 265 266create table llx_bom_bomline_extrafields 267( 268 rowid integer AUTO_INCREMENT PRIMARY KEY, 269 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 270 fk_object integer NOT NULL, 271 import_key varchar(14) -- import key 272) ENGINE=innodb; 273 274ALTER TABLE llx_bom_bom ADD INDEX idx_bom_bom_rowid (rowid); 275ALTER TABLE llx_bom_bom ADD INDEX idx_bom_bom_ref (ref); 276ALTER TABLE llx_bom_bom ADD CONSTRAINT llx_bom_bom_fk_user_creat FOREIGN KEY (fk_user_creat) REFERENCES llx_user(rowid); 277ALTER TABLE llx_bom_bom ADD INDEX idx_bom_bom_status (status); 278ALTER TABLE llx_bom_bom ADD INDEX idx_bom_bom_fk_product (fk_product); 279 280ALTER TABLE llx_bom_bomline ADD INDEX idx_bom_bomline_rowid (rowid); 281ALTER TABLE llx_bom_bomline ADD INDEX idx_bom_bomline_fk_product (fk_product); 282ALTER TABLE llx_bom_bomline ADD INDEX idx_bom_bomline_fk_bom (fk_bom); 283 284ALTER TABLE llx_bom_bom ADD UNIQUE INDEX uk_bom_bom_ref(ref, entity); 285ALTER TABLE llx_bom_bomline ADD CONSTRAINT llx_bom_bomline_fk_bom FOREIGN KEY (fk_bom) REFERENCES llx_bom_bom(rowid); 286 287 288ALTER TABLE llx_product_fournisseur_price ADD COLUMN barcode varchar(180) DEFAULT NULL; 289ALTER TABLE llx_product_fournisseur_price ADD COLUMN fk_barcode_type integer DEFAULT NULL; 290ALTER TABLE llx_product_fournisseur_price ADD INDEX idx_product_barcode (barcode); 291ALTER TABLE llx_product_fournisseur_price ADD INDEX idx_product_fk_barcode_type (fk_barcode_type); 292ALTER TABLE llx_product_fournisseur_price ADD UNIQUE INDEX uk_product_barcode (barcode, fk_barcode_type, entity); 293ALTER TABLE llx_product_fournisseur_price ADD CONSTRAINT fk_product_fournisseur_price_barcode_type FOREIGN KEY (fk_barcode_type) REFERENCES llx_c_barcode_type (rowid); 294 295ALTER TABLE llx_facturedet_rec ADD COLUMN buy_price_ht double(24,8) DEFAULT 0; 296ALTER TABLE llx_facturedet_rec ADD COLUMN fk_product_fournisseur_price integer DEFAULT NULL; 297 298ALTER TABLE llx_facturedet_rec ADD COLUMN fk_user_author integer; 299ALTER TABLE llx_facturedet_rec ADD COLUMN fk_user_modif integer; 300 301ALTER TABLE llx_expensereport_det MODIFY COLUMN value_unit double(24,8) NOT NULL; 302ALTER TABLE llx_expensereport_det ADD COLUMN subprice double(24,8) DEFAULT 0 NOT NULL after qty; 303 304ALTER TABLE llx_product_attribute_combination ADD INDEX idx_product_att_com_product_parent (fk_product_parent); 305ALTER TABLE llx_product_attribute_combination ADD INDEX idx_product_att_com_product_child (fk_product_child); 306 307ALTER TABLE llx_user ADD COLUMN fk_user_expense_validator integer after fk_user; 308ALTER TABLE llx_user ADD COLUMN fk_user_holiday_validator integer after fk_user_expense_validator; 309ALTER TABLE llx_user ADD COLUMN personal_email varchar(255) after email; 310ALTER TABLE llx_user ADD COLUMN personal_mobile varchar(20) after user_mobile; 311 312ALTER TABLE llx_product ADD COLUMN fk_project integer DEFAULT NULL; 313ALTER TABLE llx_product ADD INDEX idx_product_fk_project (fk_project); 314 315ALTER TABLE llx_actioncomm ADD COLUMN calling_duration integer; 316 317ALTER TABLE llx_emailcollector_emailcollector ADD COLUMN datelastok datetime; 318ALTER TABLE llx_emailcollector_emailcollector ADD COLUMN maxemailpercollect integer DEFAULT 100; 319 320DELETE FROM llx_const WHERE name = __ENCRYPT('THEME_ELDY_USE_HOVER')__ AND value = __ENCRYPT('0')__; 321DELETE FROM llx_const WHERE name = __ENCRYPT('THEME_ELDY_USE_CHECKED')__ AND value = __ENCRYPT('0')__; 322 323ALTER TABLE llx_inventorydet DROP COLUMN pmp; 324ALTER TABLE llx_inventorydet DROP COLUMN pa; 325ALTER TABLE llx_inventorydet DROP COLUMN new_pmp; 326 327UPDATE llx_c_shipment_mode SET label = 'https://www.laposte.fr/outils/suivre-vos-envois?code={TRACKID}' WHERE code IN ('COLSUI'); 328UPDATE llx_c_shipment_mode SET label = 'https://www.laposte.fr/outils/suivre-vos-envois?code={TRACKID}' WHERE code IN ('LETTREMAX'); 329 330 331-- VMYSQL4.3 ALTER TABLE llx_holiday MODIFY COLUMN ref varchar(30) NULL; 332-- VPGSQL8.2 ALTER TABLE llx_holiday ALTER COLUMN ref DROP NOT NULL; 333 334 335create table llx_reception 336( 337 rowid integer AUTO_INCREMENT PRIMARY KEY, 338 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 339 ref varchar(30) NOT NULL, 340 entity integer DEFAULT 1 NOT NULL, -- multi company id 341 fk_soc integer NOT NULL, 342 fk_projet integer DEFAULT NULL, 343 344 ref_ext varchar(30), -- reference into an external system (not used by dolibarr) 345 ref_int varchar(30), -- reference into an internal system (used by dolibarr to store extern id like paypal info) 346 ref_supplier varchar(30), -- customer number 347 348 date_creation datetime, -- date de creation 349 fk_user_author integer, -- author of creation 350 fk_user_modif integer, -- author of last change 351 date_valid datetime, -- date de validation 352 fk_user_valid integer, -- valideur 353 date_delivery datetime DEFAULT NULL, -- date planned of delivery 354 date_reception datetime, 355 fk_shipping_method integer, 356 tracking_number varchar(50), 357 fk_statut smallint DEFAULT 0, -- 0 = draft, 1 = validated, 2 = billed or closed depending on WORKFLOW_BILL_ON_SHIPMENT option 358 billed smallint DEFAULT 0, 359 360 height float, -- height 361 width float, -- with 362 size_units integer, -- unit of all sizes (height, width, depth) 363 size float, -- depth 364 weight_units integer, -- unit of weight 365 weight float, -- weight 366 note_private text, 367 note_public text, 368 model_pdf varchar(255), 369 fk_incoterms integer, -- for incoterms 370 location_incoterms varchar(255), -- for incoterms 371 372 import_key varchar(14), 373 extraparams varchar(255) -- for other parameters with json format 374)ENGINE=innodb; 375 376ALTER TABLE llx_reception ADD UNIQUE INDEX idx_reception_uk_ref (ref, entity); 377 378ALTER TABLE llx_reception ADD INDEX idx_reception_fk_soc (fk_soc); 379ALTER TABLE llx_reception ADD INDEX idx_reception_fk_user_author (fk_user_author); 380ALTER TABLE llx_reception ADD INDEX idx_reception_fk_user_valid (fk_user_valid); 381ALTER TABLE llx_reception ADD INDEX idx_reception_fk_shipping_method (fk_shipping_method); 382 383create table llx_reception_extrafields 384( 385 rowid integer AUTO_INCREMENT PRIMARY KEY, 386 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 387 fk_object integer NOT NULL, 388 import_key varchar(14) -- import key 389) ENGINE=innodb; 390 391ALTER TABLE llx_reception_extrafields ADD INDEX idx_reception_extrafields (fk_object); 392 393ALTER TABLE llx_commande_fournisseur_dispatch ADD COLUMN fk_projet integer DEFAULT NULL; 394ALTER TABLE llx_commande_fournisseur_dispatch ADD COLUMN fk_reception integer DEFAULT NULL; 395 396ALTER TABLE llx_accounting_bookkeeping ADD COLUMN date_export datetime DEFAULT NULL after date_validated; 397 398insert into llx_c_type_contact(rowid, element, source, code, libelle, active ) values (110, 'supplier_proposal', 'internal', 'SALESREPFOLL', 'Responsable suivi de la demande', 1); 399insert into llx_c_type_contact(rowid, element, source, code, libelle, active ) values (111, 'supplier_proposal', 'external', 'BILLING', 'Contact fournisseur facturation', 1); 400insert into llx_c_type_contact(rowid, element, source, code, libelle, active ) values (112, 'supplier_proposal', 'external', 'SHIPPING', 'Contact fournisseur livraison', 1); 401insert into llx_c_type_contact(rowid, element, source, code, libelle, active ) values (113, 'supplier_proposal', 'external', 'SERVICE', 'Contact fournisseur prestation', 1); 402 403ALTER TABLE llx_ticket_extrafields ADD INDEX idx_ticket_extrafields (fk_object); 404 405-- Use special_code=3 in Takepos 406-- VMYSQL4.1 UPDATE llx_facturedet AS fd LEFT JOIN llx_facture AS f ON f.rowid = fd.fk_facture SET fd.special_code = 4 WHERE f.module_source = 'takepos' AND fd.special_code = 3; 407 408UPDATE llx_website_page set fk_user_creat = fk_user_modif WHERE fk_user_creat IS NULL and fk_user_modif IS NOT NULL; 409 410