1-- 2-- Be carefull to requests order. 3-- This file must be loaded by calling /install/index.php page 4-- when current version is 11.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 31-- Missing in v10 32ALTER TABLE llx_account_bookkeeping ADD COLUMN date_export datetime DEFAULT NULL; 33ALTER TABLE llx_expensereport ADD COLUMN paid smallint default 0 NOT NULL; 34UPDATE llx_expensereport set paid = 1 WHERE fk_statut = 6 and paid = 0; 35 36UPDATE llx_c_units SET short_label = 'i' WHERE code = 'MI'; 37UPDATE llx_c_units SET unit_type = 'weight', short_label = 'kg', scale = 0 WHERE code = 'KG'; 38UPDATE llx_c_units SET unit_type = 'weight', short_label = 'g', scale = -3 WHERE code = 'G'; 39UPDATE llx_c_units SET unit_type = 'time' WHERE code IN ('S','H','D'); 40UPDATE llx_c_units SET unit_type = 'size' WHERE code IN ('M','LM'); 41UPDATE llx_c_units SET label = 'SizeUnitm', scale = 0 WHERE code IN ('M'); 42UPDATE llx_c_units SET active = 0, scale = 0 WHERE code IN ('LM'); 43UPDATE llx_c_units SET unit_type = 'surface', scale = 0 WHERE code IN ('M2'); 44UPDATE llx_c_units SET unit_type = 'volume', scale = 0 WHERE code IN ('M3','L'); 45UPDATE llx_c_units SET scale = -3, active = 0 WHERE code IN ('L'); 46UPDATE llx_c_units SET label = 'VolumeUnitm3' WHERE code IN ('M3'); 47UPDATE llx_c_units SET label = 'SurfaceUnitm2' WHERE code IN ('M2'); 48 49ALTER TABLE llx_adherent_type ADD UNIQUE INDEX uk_adherent_type_libelle (libelle, entity); 50 51ALTER TABLE llx_mailing_cibles MODIFY COLUMN lastname varchar(160); 52ALTER TABLE llx_mailing_cibles MODIFY COLUMN firstname varchar(160); 53 54ALTER TABLE llx_emailcollector_emailcollector ADD COLUMN login varchar(128); 55ALTER TABLE llx_emailcollector_emailcollector ADD COLUMN codelastresult varchar(16); 56ALTER TABLE llx_emailcollector_emailcollectoraction ADD COLUMN position integer DEFAULT 0; 57 58 59 60-- For v11 61 62 63ALTER TABLE llx_product_price MODIFY COLUMN tva_tx double(6,3) DEFAULT 0 NOT NULL; 64 65ALTER TABLE llx_facturedet MODIFY COLUMN situation_percent real DEFAULT 100; 66UPDATE llx_facturedet SET situation_percent = 100 WHERE situation_percent IS NULL AND fk_prev_id IS NULL; 67 68-- Set country to null for deprecated accounting system (there is now one per country) 69UPDATE llx_accounting_system SET fk_country = NULL, active = 0 WHERE pcg_version = 'SYSCOHADA'; 70INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 20, 'BAS-K1-MINI', 'The Swedish mini chart of accounts', 1); 71 72ALTER TABLE llx_c_action_trigger MODIFY COLUMN elementtype varchar(64) NOT NULL; 73 74ALTER TABLE llx_societe_account ADD COLUMN site_account varchar(128); 75 76UPDATE llx_holiday SET ref = rowid WHERE ref IS NULL; 77-- VMYSQL4.3 ALTER TABLE llx_holiday MODIFY COLUMN ref varchar(30) NOT NULL; 78-- VPGSQL8.2 ALTER TABLE llx_holiday ALTER COLUMN ref SET NOT NULL; 79 80ALTER TABLE llx_c_email_senderprofile MODIFY COLUMN active tinyint DEFAULT 1 NOT NULL; 81 82insert into llx_c_type_container (code,label,module,active) values ('menu', 'Menu', 'system', 1); 83 84INSERT INTO llx_c_ticket_type (code, pos, label, active, use_default, description) VALUES('HELP', '15', 'Request for functionnal help', 1, 0, NULL); 85 86ALTER TABLE llx_don ADD COLUMN fk_user_modif integer; 87 88ALTER TABLE llx_expeditiondet ADD INDEX idx_expeditiondet_fk_origin_line (fk_origin_line); 89 90ALTER TABLE llx_rights_def ADD COLUMN module_position INTEGER NOT NULL DEFAULT 0; 91ALTER TABLE llx_rights_def ADD COLUMN family_position INTEGER NOT NULL DEFAULT 0; 92 93UPDATE llx_rights_def SET subperms = 'write' WHERE perms = 'fiscalyear' AND module = 'accounting' AND subperms IS NULL; 94 95ALTER TABLE llx_bom_bom ADD COLUMN duration double(24,8) DEFAULT NULL; 96ALTER TABLE llx_bom_bom ADD COLUMN fk_warehouse integer; 97ALTER TABLE llx_bom_bomline ADD COLUMN position integer NOT NULL DEFAULT 0; 98ALTER TABLE llx_bom_bomline ADD COLUMN qty_frozen smallint DEFAULT 0; 99ALTER TABLE llx_bom_bomline ADD COLUMN disable_stock_change smallint DEFAULT 0; 100 101-- VMYSQL4.1 ALTER TABLE llx_bom_bomline DROP COLUMN `rank`; 102-- VPGSQL8.2 ALTER TABLE llx_bom_bomline DROP COLUMN rank; 103 104create table llx_categorie_warehouse 105( 106 fk_categorie integer NOT NULL, 107 fk_warehouse integer NOT NULL, 108 import_key varchar(14) 109) ENGINE=innodb; 110 111ALTER TABLE llx_categorie_warehouse ADD PRIMARY KEY pk_categorie_warehouse (fk_categorie, fk_warehouse); 112ALTER TABLE llx_categorie_warehouse ADD INDEX idx_categorie_warehouse_fk_categorie (fk_categorie); 113ALTER TABLE llx_categorie_warehouse ADD INDEX idx_categorie_warehouse_fk_warehouse (fk_warehouse); 114 115ALTER TABLE llx_categorie_warehouse ADD CONSTRAINT fk_categorie_warehouse_categorie_rowid FOREIGN KEY (fk_categorie) REFERENCES llx_categorie (rowid); 116ALTER TABLE llx_categorie_warehouse ADD CONSTRAINT fk_categorie_warehouse_fk_warehouse_rowid FOREIGN KEY (fk_warehouse) REFERENCES llx_entrepot (rowid); 117 118 119create table llx_holiday_extrafields 120( 121 rowid integer AUTO_INCREMENT PRIMARY KEY, 122 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 123 fk_object integer NOT NULL, 124 import_key varchar(14) -- import key 125) ENGINE=innodb; 126 127ALTER TABLE llx_holiday_extrafields ADD INDEX idx_holiday_extrafields (fk_object); 128 129ALTER TABLE llx_societe_rib MODIFY label varchar(200); 130 131ALTER TABLE llx_societe ADD COLUMN logo_squarred varchar(255); 132 133insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('USER_SENTBYMAIL','Email sent','Executed when an email is sent from user card','user',300); 134 135create table llx_entrepot_extrafields 136( 137 rowid integer AUTO_INCREMENT PRIMARY KEY, 138 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 139 fk_object integer NOT NULL, 140 import_key varchar(14) -- import key 141) ENGINE=innodb; 142 143ALTER TABLE llx_entrepot_extrafields ADD INDEX idx_entrepot_extrafields (fk_object); 144 145ALTER TABLE llx_extrafields ADD COLUMN printable boolean DEFAULT FALSE; 146 147ALTER TABLE llx_facture ADD COLUMN retained_warranty real DEFAULT NULL after situation_final; 148ALTER TABLE llx_facture ADD COLUMN retained_warranty_date_limit date DEFAULT NULL after retained_warranty; 149ALTER TABLE llx_facture ADD COLUMN retained_warranty_fk_cond_reglement integer DEFAULT NULL after retained_warranty_date_limit; 150ALTER TABLE llx_facture ADD COLUMN date_closing datetime DEFAULT NULL after date_valid; 151ALTER TABLE llx_facture ADD COLUMN fk_user_closing integer DEFAULT NULL after fk_user_valid; 152 153ALTER TABLE llx_c_shipment_mode ADD COLUMN entity integer DEFAULT 1 NOT NULL; 154 155ALTER TABLE llx_c_shipment_mode DROP INDEX uk_c_shipment_mode; 156ALTER TABLE llx_c_shipment_mode ADD UNIQUE INDEX uk_c_shipment_mode (code, entity); 157 158ALTER TABLE llx_facture_fourn DROP COLUMN total; 159 160ALTER TABLE llx_user ADD COLUMN iplastlogin varchar(250); 161ALTER TABLE llx_user ADD COLUMN ippreviouslogin varchar(250); 162 163ALTER TABLE llx_events ADD COLUMN prefix_session varchar(255) NULL; 164 165create table llx_payment_salary_extrafields 166( 167 rowid integer AUTO_INCREMENT PRIMARY KEY, 168 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 169 fk_object integer NOT NULL, -- salary payment id 170 import_key varchar(14) -- import key 171)ENGINE=innodb; 172 173ALTER TABLE llx_payment_salary_extrafields ADD INDEX idx_payment_salary_extrafields (fk_object); 174 175ALTER TABLE llx_c_price_expression MODIFY COLUMN expression varchar(255) NOT NULL; 176 177UPDATE llx_bank_url set url = REPLACE( url, 'compta/salaries/', 'salaries/'); 178 179ALTER TABLE llx_stock_mouvement ADD COLUMN fk_projet INTEGER NOT NULL DEFAULT 0 AFTER model_pdf; 180 181ALTER TABLE llx_oauth_token ADD COLUMN fk_soc integer DEFAULT NULL after token; 182 183 184ALTER TABLE llx_adherent_type ADD COLUMN duration varchar(6) DEFAULT NULL after morphy; 185 186ALTER TABLE llx_mailing ADD COLUMN tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 187ALTER TABLE llx_mailing_cibles ADD COLUMN tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 188 189ALTER TABLE llx_projet ADD COLUMN usage_opportunity integer DEFAULT 0; 190ALTER TABLE llx_projet ADD COLUMN usage_task integer DEFAULT 1; 191ALTER TABLE llx_projet CHANGE COLUMN bill_time usage_bill_time integer DEFAULT 0; -- rename existing field 192ALTER TABLE llx_projet ADD COLUMN usage_organize_event integer DEFAULT 0; 193 194UPDATE llx_projet set usage_opportunity = 1 WHERE fk_opp_status > 0; 195 196create table llx_societe_contacts 197( 198 rowid integer AUTO_INCREMENT PRIMARY KEY, 199 entity integer DEFAULT 1 NOT NULL, 200 date_creation datetime NOT NULL, 201 fk_soc integer NOT NULL, 202 fk_c_type_contact int NOT NULL, 203 fk_socpeople integer NOT NULL, 204 tms TIMESTAMP, 205 import_key VARCHAR(14) 206)ENGINE=innodb; 207 208ALTER TABLE llx_societe_contacts ADD UNIQUE INDEX idx_societe_contacts_idx1 (entity, fk_soc, fk_c_type_contact, fk_socpeople); 209ALTER TABLE llx_societe_contacts ADD CONSTRAINT fk_societe_contacts_fk_c_type_contact FOREIGN KEY (fk_c_type_contact) REFERENCES llx_c_type_contact(rowid); 210ALTER TABLE llx_societe_contacts ADD CONSTRAINT fk_societe_contacts_fk_soc FOREIGN KEY (fk_soc) REFERENCES llx_societe(rowid); 211ALTER TABLE llx_societe_contacts ADD CONSTRAINT fk_societe_contacts_fk_socpeople FOREIGN KEY (fk_socpeople) REFERENCES llx_socpeople(rowid); 212 213-- VMYSQL4.3 ALTER TABLE llx_accounting_account MODIFY COLUMN rowid bigint AUTO_INCREMENT; 214-- VPGSQL8.2 ALTER TABLE llx_accounting_account MODIFY COLUMN rowid bigint; 215 216 217 218ALTER TABLE llx_supplier_proposaldet ADD COLUMN date_start datetime DEFAULT NULL; 219ALTER TABLE llx_supplier_proposaldet ADD COLUMN date_end datetime DEFAULT NULL; 220 221 222create table llx_c_hrm_public_holiday 223( 224 id integer AUTO_INCREMENT PRIMARY KEY, 225 entity integer DEFAULT 0 NOT NULL, -- multi company id, 0 = all 226 fk_country integer, 227 code varchar(62), 228 dayrule varchar(64) DEFAULT '', -- 'easter', 'eastermonday', ... 229 day integer, 230 month integer, 231 year integer, -- 0 for all years 232 active integer DEFAULT 1, 233 import_key varchar(14) 234)ENGINE=innodb; 235 236ALTER TABLE llx_c_hrm_public_holiday ADD UNIQUE INDEX uk_c_hrm_public_holiday(entity, code); 237ALTER TABLE llx_c_hrm_public_holiday ADD UNIQUE INDEX uk_c_hrm_public_holiday2(entity, fk_country, dayrule, day, month, year); 238 239 240-- A lot of countries 241INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, year, month, day, active) VALUES('NEWYEARDAY1', 0, 0, 0, 1, 1, 1); 242INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, year, month, day, active) VALUES('LABORDAY1', 0, 0, 0, 5, 1, 1); 243INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, year, month, day, active) VALUES('ASSOMPTIONDAY1', 0, 0, 0, 8, 15, 1); 244INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, year, month, day, active) VALUES('CHRISTMASDAY1', 0, 0, 0, 12, 25, 1); 245 246-- France only (1) 247INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('FR-VICTORYDAY', 0, 1, '', 0, 5, 8, 1); 248INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('FR-NATIONALDAY', 0, 1, '', 0, 7, 14, 1); 249INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('FR-ASSOMPTION', 0, 1, '', 0, 8, 15, 1); 250INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('FR-TOUSSAINT', 0, 1, '', 0, 11, 1, 1); 251INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('FR-ARMISTICE', 0, 1, '', 0, 11, 11, 1); 252INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('FR-EASTER', 0, 1, 'eastermonday', 0, 0, 0, 1); 253INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('FR-ASCENSION', 0, 1, 'ascension', 0, 0, 0, 1); 254INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('FR-PENTECOST', 0, 1, 'pentecost', 0, 0, 0, 1); 255 256-- Italy (3) 257INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, year, month, day, active) VALUES('IT-LIBEAZIONE', 0, 3, 0, 4, 25, 1); 258INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, year, month, day, active) VALUES('IT-EPIPHANY', 0, 3, 0, 6, 1, 1); 259INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, year, month, day, active) VALUES('IT-REPUBBLICA', 0, 3, 0, 6, 2, 1); 260INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, year, month, day, active) VALUES('IT-TUTTISANTIT', 0, 3, 0, 11, 1, 1); 261INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, year, month, day, active) VALUES('IT-IMMACULE', 0, 3, 0, 12, 8, 1); 262INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, year, month, day, active) VALUES('IT-SAINTSTEFAN', 0, 3, 0, 12, 26, 1); 263 264-- Spain (4) 265INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('ES-EASTER', 0, 4, 'easter', 0, 0, 0, 1); 266INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('ES-REYE', 0, 4, '', 0, 6, 1, 1); 267INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('ES-HISPANIDAD', 0, 4, '', 0, 10, 12, 1); 268INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('ES-TOUSSAINT', 0, 4, '', 0, 11, 1, 1); 269INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('ES-CONSTITUIZION', 0, 4, '', 0, 12, 6, 1); 270INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('ES-IMMACULE', 0, 4, '', 0, 12, 8, 1); 271 272-- Austria (41) 273INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('AT-EASTER', 0, 41, 'eastermonday', 0, 0, 0, 1); 274INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('AT-ASCENSION', 0, 41, 'ascension', 0, 0, 0, 1); 275INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('AT-PENTECOST', 0, 41, 'pentecost', 0, 0, 0, 1); 276INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('AT-FRONLEICHNAM', 0, 41, 'fronleichnam', 0, 0, 0, 1); 277INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('AT-KONEGIE', 0, 41, '', 0, 6, 1, 1); 278INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('AT-26OKT', 0, 41, '', 0, 10, 26, 1); 279INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('AT-TOUSSAINT', 0, 41, '', 0, 11, 1, 1); 280INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('AT-IMMACULE', 0, 41, '', 0, 12, 8, 1); 281INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('AT-24DEC', 0, 41, '', 0, 12, 24, 1); 282INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('AT-SAINTSTEFAN', 0, 41, '', 0, 12, 26, 1); 283INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('AT-Silvester', 0, 41, '', 0, 12, 31, 1); 284 285-- India (117) 286INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('IN-REPUBLICDAY', 0, 117, '', 0, 1, 26, 1); 287INSERT INTO llx_c_hrm_public_holiday (code, entity, fk_country, dayrule, year, month, day, active) VALUES('IN-GANDI', 0, 117, '', 0, 10, 2, 1); 288 289 290create table llx_c_socialnetworks 291( 292 rowid integer AUTO_INCREMENT PRIMARY KEY, 293 entity integer DEFAULT 1 NOT NULL, 294 code varchar(100), 295 label varchar(150), 296 url text, 297 icon varchar(20), 298 active tinyint DEFAULT 1 NOT NULL 299)ENGINE=innodb; 300 301ALTER TABLE llx_c_socialnetworks ADD UNIQUE INDEX idx_c_socialnetworks_code (code); 302 303INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'facebook', 'Facebook', 'https://www.facebook.com/{socialid}', 'fa-facebook', 1); 304INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'skype', 'Skype', 'https://www.skype.com/{socialid}', 'fa-skype', 1); 305INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'twitter', 'Twitter', 'https://www.twitter.com/{socialid}', 'fa-twitter', 1); 306INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'linkedin', 'LinkedIn', 'https://www.linkedin.com/{socialid}', 'fa-linkedin', 1); 307INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'instagram', 'Instagram', 'https://www.instagram.com/{socialid}', 'fa-instagram', 1); 308INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'snapchat', 'Snapchat', '{socialid}', 'fa-snapchat', 1); 309INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'googleplus', 'GooglePlus', 'https://www.googleplus.com/{socialid}', 'fa-google-plus-g', 1); 310INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'youtube', 'Youtube', 'https://www.youtube.com/{socialid}', 'fa-youtube', 1); 311INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'whatsapp', 'Whatsapp', '{socialid}', 'fa-whatsapp', 1); 312INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'tumblr', 'Tumblr', 'https://www.tumblr.com/{socialid}', 'fa-tumblr', 0); 313INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'vero', 'Vero', 'https://vero.co/{socialid}', '', 0); 314INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'viadeo', 'Viadeo', 'https://fr.viadeo.com/fr/{socialid}', 'fa-viadeo', 0); 315INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'slack', 'Slack', '{socialid}', 'fa-slack', 0); 316INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'xing', 'Xing', '{socialid}', 'fa-xing', 0); 317INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'meetup', 'Meetup', '{socialid}', 'fa-meetup', 0); 318INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'pinterest', 'Pinterest', '{socialid}', 'fa-pinterest', 0); 319INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'flickr', 'Flickr', '{socialid}', 'fa-flickr', 0); 320INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, '500px', '500px', '{socialid}', 'fa-500px', 0); 321INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'giphy', 'Giphy', '{socialid}', '', 0); 322INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'gifycat', 'Gificat', '{socialid}', '', 0); 323INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'dailymotion', 'Dailymotion', '{socialid}', '', 0); 324INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'vimeo', 'Vimeo', '{socialid}', '', 0); 325INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'periscope', 'Periscope', '{socialid}', '', 0); 326INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'twitch', 'Twitch', '{socialid}', '', 0); 327INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'discord', 'Discord', '{socialid}', 'fa-discord', 0); 328INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'wikipedia', 'Wikipedia', '{socialid}', '', 0); 329INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'reddit', 'Reddit', '{socialid}', 'fa-reddit', 0); 330INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'quora', 'Quora', '{socialid}', '', 0); 331INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'tripadvisor', 'Tripadvisor', '{socialid}', '', 0); 332INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'mastodon', 'Mastodon', '{socialid}', '', 0); 333INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'diaspora', 'Diaspora', '{socialid}', '', 0); 334INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'viber', 'Viber', '{socialid}', '', 0); 335 336ALTER TABLE llx_adherent ADD COLUMN socialnetworks text DEFAULT NULL AFTER email; 337ALTER TABLE llx_societe ADD COLUMN socialnetworks text DEFAULT NULL AFTER email; 338ALTER TABLE llx_socpeople ADD COLUMN socialnetworks text DEFAULT NULL AFTER email; 339ALTER TABLE llx_user ADD COLUMN socialnetworks text DEFAULT NULL AFTER personal_email; 340 341ALTER TABLE llx_product ADD COLUMN net_measure float; 342ALTER TABLE llx_product ADD COLUMN net_measure_units tinyint; 343 344create table llx_adherent_type_lang 345( 346 rowid integer AUTO_INCREMENT PRIMARY KEY, 347 fk_type integer DEFAULT 0 NOT NULL, 348 lang varchar(5) DEFAULT 0 NOT NULL, 349 label varchar(255) NOT NULL, 350 description text, 351 email text, 352 import_key varchar(14) DEFAULT NULL 353)ENGINE=innodb; 354 355create table llx_fichinter_rec 356( 357 rowid integer AUTO_INCREMENT PRIMARY KEY, 358 titre varchar(50) NOT NULL, 359 entity integer DEFAULT 1 NOT NULL, -- multi company id 360 fk_soc integer DEFAULT NULL, 361 datec datetime, -- date de creation 362 fk_contrat integer DEFAULT 0, -- contrat auquel est rattache la fiche 363 fk_user_author integer, -- createur 364 fk_projet integer, -- projet auquel est associe la facture 365 duree real, -- duree totale de l'intervention 366 description text, 367 modelpdf varchar(50), 368 note_private text, 369 note_public text, 370 frequency integer, -- frequency (for example: 3 for every 3 month) 371 unit_frequency varchar(2) DEFAULT 'm', -- 'm' for month (date_when must be a day <= 28), 'y' for year, ... 372 date_when datetime DEFAULT NULL, -- date for next gen (when an invoice is generated, this field must be updated with next date) 373 date_last_gen datetime DEFAULT NULL, -- date for last gen (date with last successfull generation of invoice) 374 nb_gen_done integer DEFAULT NULL, -- nb of generation done (when an invoice is generated, this field must incremented) 375 nb_gen_max integer DEFAULT NULL, -- maximum number of generation 376 auto_validate integer NULL DEFAULT NULL -- statut of the generated intervention 377 378)ENGINE=innodb; 379 380ALTER TABLE llx_fichinter_rec ADD UNIQUE INDEX idx_fichinter_rec_uk_titre (titre, entity); 381ALTER TABLE llx_fichinter_rec ADD INDEX idx_fichinter_rec_fk_soc (fk_soc); 382ALTER TABLE llx_fichinter_rec ADD INDEX idx_fichinter_rec_fk_user_author (fk_user_author); 383ALTER TABLE llx_fichinter_rec ADD INDEX idx_fichinter_rec_fk_projet (fk_projet); 384ALTER TABLE llx_fichinter_rec ADD CONSTRAINT fk_fichinter_rec_fk_user_author FOREIGN KEY (fk_user_author) REFERENCES llx_user (rowid); 385ALTER TABLE llx_fichinter_rec ADD CONSTRAINT fk_fichinter_rec_fk_projet FOREIGN KEY (fk_projet) REFERENCES llx_projet (rowid); 386 387create table llx_fichinterdet_rec 388( 389 rowid integer AUTO_INCREMENT PRIMARY KEY, 390 fk_fichinter integer NOT NULL, 391 date datetime, -- date de la ligne d'intervention 392 description text, -- description de la ligne d'intervention 393 duree integer, -- duree de la ligne d'intervention 394 rang integer DEFAULT 0, -- ordre affichage sur la fiche 395 total_ht DOUBLE(24, 8) NULL DEFAULT NULL, 396 subprice DOUBLE(24, 8) NULL DEFAULT NULL, 397 fk_parent_line integer NULL DEFAULT NULL, 398 fk_product integer NULL DEFAULT NULL, 399 label varchar(255) NULL DEFAULT NULL, 400 tva_tx DOUBLE(6, 3) NULL DEFAULT NULL, 401 localtax1_tx DOUBLE(6, 3) NULL DEFAULT 0, 402 localtax1_type VARCHAR(1) NULL DEFAULT NULL, 403 localtax2_tx DOUBLE(6, 3) NULL DEFAULT 0, 404 localtax2_type VARCHAR(1) NULL DEFAULT NULL, 405 qty double NULL DEFAULT NULL, 406 remise_percent double NULL DEFAULT 0, 407 remise double NULL DEFAULT 0, 408 fk_remise_except integer NULL DEFAULT NULL, 409 price DOUBLE(24, 8) NULL DEFAULT NULL, 410 total_tva DOUBLE(24, 8) NULL DEFAULT NULL, 411 total_localtax1 DOUBLE(24, 8) NULL DEFAULT 0, 412 total_localtax2 DOUBLE(24, 8) NULL DEFAULT 0, 413 total_ttc DOUBLE(24, 8) NULL DEFAULT NULL, 414 product_type INTEGER NULL DEFAULT 0, 415 date_start datetime NULL DEFAULT NULL, 416 date_end datetime NULL DEFAULT NULL, 417 info_bits INTEGER NULL DEFAULT 0, 418 buy_price_ht DOUBLE(24, 8) NULL DEFAULT 0, 419 fk_product_fournisseur_price integer NULL DEFAULT NULL, 420 fk_code_ventilation integer NOT NULL DEFAULT 0, 421 fk_export_commpta integer NOT NULL DEFAULT 0, 422 special_code integer UNSIGNED NULL DEFAULT 0, 423 fk_unit integer NULL DEFAULT NULL, 424 import_key varchar(14) NULL DEFAULT NULL 425)ENGINE=innodb; 426 427ALTER TABLE llx_supplier_proposaldet ADD COLUMN date_start datetime DEFAULT NULL AFTER product_type; 428ALTER TABLE llx_supplier_proposaldet ADD COLUMN date_end datetime DEFAULT NULL AFTER date_start; 429 430--List of parcels details related to an expedition 431create table llx_expedition_package 432( 433 rowid integer AUTO_INCREMENT PRIMARY KEY, 434 fk_expedition integer NOT NULL, 435 description varchar(255), --Description of goods in the package (required by the custom) 436 value double(24,8) DEFAULT 0,--Value (Price of the content, for insurance & custom) 437 fk_parcel_type integer, -- Type or package, linked to llx_c_shipment_parcel_type (eg: 1=enveloppe, 2=package, 3=palette, 4=other) 438 height float, -- height 439 width float, -- width 440 size float, -- depth 441 size_units integer, -- unit of all sizes (height, width, depth) 442 weight float, -- weight 443 weight_units integer, -- unit of weight 444 dangerous_goods smallint DEFAULT 0, -- 0 = no dangerous goods or 1 = Explosives, 2 = Flammable Gases, 3 = Flammable Liquids, 4 = Flammable solids, 5 = Oxidizing, 6 = Toxic & Infectious, 7 = Radioactive, 8 = Corrosives, 9 = Miscellaneous (see https://en.wikipedia.org/wiki/Dangerous_goods). I'm not sure if just register 0 (no) or 1 (yes) is enough. 445 tail_lift smallint DEFAULT 0, -- 0 = no tail lift required to load/unload package(s), 1 = a tail lift is required to load/unload package(s). Sometime tail lift load can be different than tail lift delivery so maybe adding a new table line. 446 rang integer DEFAULT 0 447)ENGINE=innodb; 448 449--Dictionary of package type 450create table llx_c_shipment_package_type 451( 452 rowid integer AUTO_INCREMENT PRIMARY KEY, 453 label varchar(50) NOT NULL, -- Short name 454 description varchar(255), -- Description 455 active integer DEFAULT 1 NOT NULL, -- Active or not 456 entity integer DEFAULT 1 NOT NULL -- Multi company id 457)ENGINE=innodb; 458 459CREATE TABLE llx_product_fournisseur_price_extrafields ( 460 rowid integer AUTO_INCREMENT PRIMARY KEY, 461 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 462 fk_object integer NOT NULL, 463 import_key varchar(14) -- import key 464) ENGINE=innodb; 465 466ALTER TABLE llx_product_fournisseur_price_extrafields ADD INDEX idx_product_fournisseur_price_extrafields (fk_object); 467 468CREATE TABLE llx_mrp_mo( 469 -- BEGIN MODULEBUILDER FIELDS 470 rowid integer AUTO_INCREMENT PRIMARY KEY NOT NULL, 471 ref varchar(128) DEFAULT '(PROV)' NOT NULL, 472 entity integer DEFAULT 1 NOT NULL, 473 label varchar(255), 474 qty real NOT NULL, 475 fk_warehouse integer, 476 fk_soc integer, 477 note_public text, 478 note_private text, 479 date_creation datetime NOT NULL, 480 date_valid datetime NULL, 481 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 482 fk_user_creat integer NOT NULL, 483 fk_user_modif integer, 484 fk_user_valid integer, 485 model_pdf varchar(255), 486 import_key varchar(14), 487 status integer NOT NULL, 488 fk_product integer NOT NULL, 489 date_start_planned datetime, 490 date_end_planned datetime, 491 fk_bom integer, 492 fk_project integer 493 -- END MODULEBUILDER FIELDS 494) ENGINE=innodb; 495 496ALTER TABLE llx_mrp_mo ADD COLUMN date_valid datetime NULL; 497ALTER TABLE llx_mrp_mo ADD COLUMN fk_user_valid integer; 498 499ALTER TABLE llx_bom_bom ADD COLUMN model_pdf varchar(255); 500ALTER TABLE llx_mrp_mo ADD COLUMN model_pdf varchar(255); 501 502ALTER TABLE llx_mrp_mo ADD INDEX idx_mrp_mo_ref (ref); 503ALTER TABLE llx_mrp_mo ADD INDEX idx_mrp_mo_entity (entity); 504ALTER TABLE llx_mrp_mo ADD INDEX idx_mrp_mo_fk_soc (fk_soc); 505ALTER TABLE llx_mrp_mo ADD CONSTRAINT fk_mrp_mo_fk_user_creat FOREIGN KEY (fk_user_creat) REFERENCES llx_user(rowid); 506ALTER TABLE llx_mrp_mo ADD INDEX idx_mrp_mo_status (status); 507ALTER TABLE llx_mrp_mo ADD INDEX idx_mrp_mo_fk_product (fk_product); 508ALTER TABLE llx_mrp_mo ADD INDEX idx_mrp_mo_date_start_planned (date_start_planned); 509ALTER TABLE llx_mrp_mo ADD INDEX idx_mrp_mo_date_end_planned (date_end_planned); 510ALTER TABLE llx_mrp_mo ADD INDEX idx_mrp_mo_fk_bom (fk_bom); 511ALTER TABLE llx_mrp_mo ADD INDEX idx_mrp_mo_fk_project (fk_project); 512 513 514create table llx_mrp_mo_extrafields 515( 516 rowid integer AUTO_INCREMENT PRIMARY KEY, 517 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 518 fk_object integer NOT NULL, 519 import_key varchar(14) -- import key 520) ENGINE=innodb; 521 522ALTER TABLE llx_mrp_mo_extrafields ADD INDEX idx_fk_object(fk_object); 523 524 525insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BOM_VALIDATE','BOM validated','Executed when a BOM is validated','bom',650); 526insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BOM_UNVALIDATE','BOM unvalidated','Executed when a BOM is unvalidated','bom',651); 527insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BOM_CLOSE','BOM disabled','Executed when a BOM is disabled','bom',652); 528insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BOM_REOPEN','BOM reopen','Executed when a BOM is re-open','bom',653); 529insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BOM_DELETE','BOM deleted','Executed when a BOM deleted','bom',654); 530 531DELETE FROM llx_c_action_trigger where code LIKE 'MO_%'; 532insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MRP_MO_VALIDATE','MO validated','Executed when a MO is validated','bom',660); 533insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MRP_MO_PRODUCED','MO produced','Executed when a MO is produced','bom',661); 534insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MRP_MO_DELETE','MO deleted','Executed when a MO is deleted','bom',662); 535insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MRP_MO_CANCEL','MO canceled','Executed when a MO is canceled','bom',663); 536 537ALTER TABLE llx_comment ADD COLUMN fk_user_modif integer DEFAULT NULL; 538 539 540CREATE TABLE llx_mrp_production( 541 rowid integer AUTO_INCREMENT PRIMARY KEY NOT NULL, 542 fk_mo integer NOT NULL, 543 position integer NOT NULL DEFAULT 0, 544 fk_product integer NOT NULL, 545 fk_warehouse integer, 546 qty real NOT NULL DEFAULT 1, 547 qty_frozen smallint DEFAULT 0, 548 disable_stock_change smallint DEFAULT 0, 549 batch varchar(30), 550 role varchar(10), -- 'toconsume' or 'toproduce' (initialized at MO creation), 'consumed' or 'produced' (added after MO validation) 551 fk_mrp_production integer, -- if role = 'consumed', id of line with role 'toconsume', if role = 'produced' id of line with role 'toproduce' 552 fk_stock_movement integer, -- id of stock movement when movements are validated 553 date_creation datetime NOT NULL, 554 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 555 fk_user_creat integer NOT NULL, 556 fk_user_modif integer, 557 import_key varchar(14) 558) ENGINE=innodb; 559ALTER TABLE llx_mrp_production MODIFY COLUMN qty real NOT NULL DEFAULT 1; 560 561ALTER TABLE llx_mrp_production ADD COLUMN qty_frozen smallint DEFAULT 0; 562ALTER TABLE llx_mrp_production ADD COLUMN disable_stock_change smallint DEFAULT 0; 563 564ALTER TABLE llx_mrp_production ADD CONSTRAINT fk_mrp_production_mo FOREIGN KEY (fk_mo) REFERENCES llx_mrp_mo (rowid); 565ALTER TABLE llx_mrp_production ADD CONSTRAINT fk_mrp_production_product FOREIGN KEY (fk_product) REFERENCES llx_product (rowid); 566ALTER TABLE llx_mrp_production ADD CONSTRAINT fk_mrp_production_stock_movement FOREIGN KEY (fk_stock_movement) REFERENCES llx_stock_mouvement (rowid); 567 568ALTER TABLE llx_mrp_production ADD INDEX idx_mrp_production_fk_mo (fk_mo); 569 570ALTER TABLE llx_emailcollector_emailcollector ADD UNIQUE INDEX uk_emailcollector_emailcollector_ref(ref, entity); 571 572ALTER TABLE llx_website ADD COLUMN use_manifest integer; 573 574ALTER TABLE llx_facture_rec MODIFY COLUMN fk_cond_reglement integer NOT NULL DEFAULT 1; 575 576create table llx_commande_fournisseur_dispatch_extrafields 577( 578 rowid integer AUTO_INCREMENT PRIMARY KEY, 579 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 580 fk_object integer NOT NULL, -- object id 581 import_key varchar(14) -- import key 582)ENGINE=innodb; 583 584ALTER TABLE llx_commande_fournisseur_dispatch_extrafields ADD INDEX idx_commande_fournisseur_dispatch_extrafields (fk_object); 585 586 587create table llx_facturedet_rec_extrafields 588( 589 rowid integer AUTO_INCREMENT PRIMARY KEY, 590 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 591 fk_object integer NOT NULL, -- object id 592 import_key varchar(14) -- import key 593)ENGINE=innodb; 594 595ALTER TABLE llx_facturedet_rec_extrafields ADD INDEX idx_facturedet_rec_extrafields (fk_object); 596 597