1-- 2-- Be carefull to requests order. 3-- This file must be loaded by calling /install/index.php page 4-- when current version is 6.0.0 or higher. 5-- 6-- To rename a table: ALTER TABLE llx_table RENAME TO llx_table_new; 7-- To add a column: ALTER TABLE llx_table ADD COLUMN newcol varchar(60) NOT NULL DEFAULT '0' AFTER existingcol; 8-- To rename a column: ALTER TABLE llx_table CHANGE COLUMN oldname newname varchar(60); 9-- To drop a column: ALTER TABLE llx_table DROP COLUMN oldname; 10-- To change type of field: ALTER TABLE llx_table MODIFY COLUMN name varchar(60); 11-- To drop a foreign key: ALTER TABLE llx_table DROP FOREIGN KEY fk_name; 12-- To drop an index: -- VMYSQL4.0 DROP INDEX nomindex on llx_table 13-- To drop an index: -- VPGSQL8.0 DROP INDEX nomindex 14-- To restrict request to Mysql version x.y minimum use -- VMYSQLx.y 15-- To restrict request to Pgsql version x.y minimum use -- VPGSQLx.y 16-- 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; 17-- To make pk to be auto increment (postgres): -- VPGSQL8.2 NOT POSSIBLE. MUST DELETE/CREATE TABLE 18-- To set a field as NULL: -- VMYSQL4.3 ALTER TABLE llx_table MODIFY COLUMN name varchar(60) NULL; 19-- To set a field as NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name DROP NOT NULL; 20-- To set a field as NOT NULL: -- VMYSQL4.3 ALTER TABLE llx_table MODIFY COLUMN name varchar(60) NOT NULL; 21-- To set a field as NOT NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name SET NOT NULL; 22-- To set a field as default NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name SET DEFAULT NULL; 23-- Note: fields with type BLOB/TEXT can't have default value. 24-- -- VPGSQL8.2 DELETE FROM llx_usergroup_user WHERE fk_user NOT IN (SELECT rowid from llx_user); 25-- -- VMYSQL4.1 DELETE FROM llx_usergroup_user WHERE fk_usergroup NOT IN (SELECT rowid from llx_usergroup); 26 27 28 29-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_journal MODIFY code VARCHAR(20) CHARACTER SET utf8; 30-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_journal MODIFY code VARCHAR(20) COLLATE utf8_unicode_ci; 31-- VMYSQLUTF8UNICODECI ALTER TABLE llx_bank_account MODIFY accountancy_journal VARCHAR(20) CHARACTER SET utf8; 32-- VMYSQLUTF8UNICODECI ALTER TABLE llx_bank_account MODIFY accountancy_journal VARCHAR(20) COLLATE utf8_unicode_ci; 33 34 35ALTER TABLE llx_holiday_config MODIFY COLUMN name varchar(128); 36 37ALTER TABLE llx_supplier_proposaldet CHANGE COLUMN fk_askpricesupplier fk_supplier_proposal integer NOT NULL; 38 39-- VMYSQL4.1 SET sql_mode = 'ALLOW_INVALID_DATES'; 40-- -- VMYSQL4.1 ALTER TABLE llx_adherent MODIFY COLUMN datefin datetime DEFAULT '2001-01-01 00:00:00'; 41-- VMYSQL4.1 update llx_adherent set datefin = NULL where DATE(STR_TO_DATE(datefin, '%Y-%m-%d')) IS NULL; 42-- VMYSQL4.1 SET sql_mode = 'NO_ZERO_DATE'; 43-- VMYSQL4.1 update llx_adherent set datefin = NULL where DATE(STR_TO_DATE(datefin, '%Y-%m-%d')) IS NULL; 44 45-- VMYSQL4.1 ALTER TABLE llx_opensurvey_sondage MODIFY COLUMN tms timestamp DEFAULT '2001-01-01 00:00:00'; 46-- VMYSQL4.1 ALTER TABLE llx_adherent MODIFY COLUMN datefin datetime NULL; 47 48-- To remove a default value for date that is not valid when field is not null 49-- VMYSQL4.1 ALTER TABLE llx_chargesociales MODIFY COLUMN date_ech datetime DEFAULT NULL; 50-- VMYSQL4.1 ALTER TABLE llx_chargesociales MODIFY COLUMN date_ech datetime NOT NULL; 51 52 53 54 55-- Clean corrupted values for tms 56-- VMYSQL4.1 SET sql_mode = 'ALLOW_INVALID_DATES'; 57-- VMYSQL4.1 update llx_opensurvey_sondage set tms = date_fin where DATE(STR_TO_DATE(tms, '%Y-%m-%d')) IS NULL; 58-- VMYSQL4.1 SET sql_mode = 'NO_ZERO_DATE'; 59-- VMYSQL4.1 update llx_opensurvey_sondage set tms = date_fin where DATE(STR_TO_DATE(tms, '%Y-%m-%d')) IS NULL; 60-- Remove default not null on date_fin 61-- VMYSQL4.3 ALTER TABLE llx_opensurvey_sondage MODIFY COLUMN date_fin DATETIME NULL DEFAULT NULL; 62-- VPGSQL8.2 ALTER TABLE llx_opensurvey_sondage ALTER COLUMN date_fin DROP NOT NULL; 63 64-- VMYSQL4.1 ALTER TABLE llx_opensurvey_sondage MODIFY COLUMN tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 65 66ALTER TABLE llx_opensurvey_sondage ADD COLUMN fk_user_creat integer NOT NULL DEFAULT 0; 67ALTER TABLE llx_opensurvey_sondage ADD COLUMN status integer DEFAULT 1 after date_fin; 68ALTER TABLE llx_opensurvey_sondage ADD COLUMN entity integer DEFAULT 1 NOT NULL; 69ALTER TABLE llx_opensurvey_sondage ADD COLUMN allow_comments tinyint NOT NULL DEFAULT 1; 70ALTER TABLE llx_opensurvey_sondage ADD COLUMN allow_spy tinyint NOT NULL DEFAULT 1 AFTER allow_comments; 71ALTER TABLE llx_opensurvey_sondage ADD COLUMN sujet TEXT; 72 73 74ALTER TABLE llx_socpeople MODIFY COLUMN zip varchar(25); 75 76 77ALTER TABLE llx_extrafields ADD COLUMN fieldcomputed text; 78ALTER TABLE llx_extrafields ADD COLUMN fielddefault varchar(255); 79 80ALTER TABLE llx_c_typent MODIFY COLUMN libelle varchar(64); 81 82 83ALTER TABLE llx_holiday ADD COLUMN ref varchar(30) NULL; 84ALTER TABLE llx_holiday ADD COLUMN ref_ext varchar(255); 85 86 87CREATE TABLE llx_notify_def_object 88( 89 id integer AUTO_INCREMENT PRIMARY KEY, 90 entity integer DEFAULT 1 NOT NULL, -- multi company id 91 objet_type varchar(16), -- 'actioncomm' 92 objet_id integer NOT NULL, -- id of parent key 93 type_notif varchar(16) DEFAULT 'browser', -- 'browser', 'email', 'sms', 'webservice', ... 94 date_notif datetime, -- date notification 95 user_id integer, -- notification is for this user 96 moreparam varchar(255) 97)ENGINE=innodb; 98 99ALTER TABLE llx_facturedet_rec ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx; 100 101ALTER TABLE llx_extrafields ADD COLUMN langs varchar(24); 102 103ALTER TABLE llx_supplier_proposaldet ADD COLUMN fk_unit integer DEFAULT NULL; 104 105ALTER TABLE llx_ecm_files ADD COLUMN ref varchar(128) AFTER rowid; 106ALTER TABLE llx_ecm_files CHANGE COLUMN fullpath filepath varchar(255); 107ALTER TABLE llx_ecm_files CHANGE COLUMN filepath filepath varchar(255); 108ALTER TABLE llx_ecm_files ADD COLUMN position integer; 109ALTER TABLE llx_ecm_files ADD COLUMN keyword varchar(750); 110ALTER TABLE llx_ecm_files CHANGE COLUMN keyword keyword varchar(750); 111ALTER TABLE llx_ecm_files ADD COLUMN gen_or_uploaded varchar(12); 112 113ALTER TABLE llx_ecm_files DROP INDEX uk_ecm_files; 114ALTER TABLE llx_ecm_files ADD UNIQUE INDEX uk_ecm_files (filepath, filename, entity); 115 116ALTER TABLE llx_ecm_files ADD INDEX idx_ecm_files_label (label); 117 118 119ALTER TABLE llx_expedition ADD COLUMN fk_projet integer DEFAULT NULL after fk_soc; 120 121 122ALTER TABLE llx_holiday ADD COLUMN import_key varchar(14); 123ALTER TABLE llx_holiday ADD COLUMN extraparams varchar(255); 124 125ALTER TABLE llx_expensereport ADD COLUMN import_key varchar(14); 126ALTER TABLE llx_expensereport ADD COLUMN extraparams varchar(255); 127 128ALTER TABLE llx_actioncomm ADD COLUMN import_key varchar(14); 129ALTER TABLE llx_actioncomm ADD COLUMN extraparams varchar(255); 130 131 132ALTER TABLE llx_bank_account ADD COLUMN extraparams varchar(255); 133 134ALTER TABLE llx_bank ADD COLUMN numero_compte varchar(32) NULL; 135 136-- VMYSQL4.1 ALTER TABLE llx_bank_account MODIFY COLUMN state_id integer DEFAULT NULL; 137-- VPGSQL8.2 ALTER TABLE llx_bank_account MODIFY COLUMN state_id integer USING state_id::integer; 138 139-- VMYSQL4.1 ALTER TABLE llx_adherent MODIFY COLUMN state_id integer DEFAULT NULL; 140-- VPGSQL8.2 ALTER TABLE llx_adherent MODIFY COLUMN state_id integer USING state_id::integer; 141-- VMYSQL4.1 ALTER TABLE llx_adherent MODIFY COLUMN country integer DEFAULT NULL; 142-- VPGSQL8.2 ALTER TABLE llx_adherent MODIFY COLUMN country integer USING country::integer; 143 144INSERT INTO llx_c_action_trigger (code,label,description,elementtype,rang) VALUES ('PRODUCT_CREATE','Product or service created','Executed when a product or sevice is created','product',30); 145INSERT INTO llx_c_action_trigger (code,label,description,elementtype,rang) VALUES ('PRODUCT_MODIFY','Product or service modified','Executed when a product or sevice is modified','product',30); 146INSERT INTO llx_c_action_trigger (code,label,description,elementtype,rang) VALUES ('PRODUCT_DELETE','Product or service deleted','Executed when a product or sevice is deleted','product',30); 147 148INSERT INTO llx_c_action_trigger (code,label,description,elementtype,rang) VALUES ('EXPENSE_REPORT_CREATE','Expense report created','Executed when an expense report is created','expense_report',201); 149INSERT INTO llx_c_action_trigger (code,label,description,elementtype,rang) VALUES ('EXPENSE_REPORT_CREATE','Expense report created','Executed when an expense report is created','expense_report',201); 150INSERT INTO llx_c_action_trigger (code,label,description,elementtype,rang) VALUES ('EXPENSE_REPORT_VALIDATE','Expense report validated','Executed when an expense report is validated','expense_report',202); 151INSERT INTO llx_c_action_trigger (code,label,description,elementtype,rang) VALUES ('EXPENSE_REPORT_APPROVE','Expense report approved','Executed when an expense report is approved','expense_report',203); 152INSERT INTO llx_c_action_trigger (code,label,description,elementtype,rang) VALUES ('EXPENSE_REPORT_PAYED','Expense report billed','Executed when an expense report is set as billed','expense_report',204); 153 154ALTER TABLE llx_c_email_templates ADD COLUMN content_lines text; 155 156ALTER TABLE llx_loan ADD COLUMN fk_projet integer DEFAULT NULL; 157 158ALTER TABLE llx_holiday ADD COLUMN fk_user_modif integer; 159ALTER TABLE llx_projet ADD COLUMN fk_user_modif integer; 160ALTER TABLE llx_projet_task ADD COLUMN fk_user_modif integer; 161 162ALTER TABLE llx_projet_task_time ADD COLUMN datec date; 163ALTER TABLE llx_projet_task_time ADD COLUMN tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 164 165ALTER TABLE llx_product_price ADD COLUMN fk_multicurrency integer; 166ALTER TABLE llx_product_price ADD COLUMN multicurrency_code varchar(255); 167ALTER TABLE llx_product_price ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1; 168ALTER TABLE llx_product_price ADD COLUMN multicurrency_price double(24,8) DEFAULT NULL; 169ALTER TABLE llx_product_price ADD COLUMN multicurrency_price_ttc double(24,8) DEFAULT NULL; 170 171ALTER TABLE llx_product_price_by_qty ADD COLUMN fk_user_creat integer; 172ALTER TABLE llx_product_price_by_qty ADD COLUMN fk_user_modif integer; 173ALTER TABLE llx_product_price_by_qty DROP COLUMN date_price; 174ALTER TABLE llx_product_price_by_qty ADD COLUMN tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 175ALTER TABLE llx_product_price_by_qty ADD COLUMN import_key varchar(14); 176 177ALTER TABLE llx_user ADD COLUMN import_key varchar(14); 178 179ALTER TABLE llx_facture_rec ADD COLUMN tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 180UPDATE llx_facture_rec SET tms = datec where tms < '2000-01-01'; 181 182CREATE TABLE llx_product_attribute 183( 184 rowid INT PRIMARY KEY NOT NULL AUTO_INCREMENT, 185 ref VARCHAR(255) NOT NULL, 186 label VARCHAR(255) NOT NULL, 187 rang INT DEFAULT 0 NOT NULL, 188 entity INT DEFAULT 1 NOT NULL 189); 190ALTER TABLE llx_product_attribute ADD CONSTRAINT unique_ref UNIQUE (ref); 191 192CREATE TABLE llx_product_attribute_value 193( 194 rowid INT PRIMARY KEY NOT NULL AUTO_INCREMENT, 195 fk_product_attribute INT NOT NULL, 196 ref VARCHAR(255) DEFAULT NULL, 197 value VARCHAR(255) DEFAULT NULL, 198 entity INT DEFAULT 1 NOT NULL 199); 200ALTER TABLE llx_product_attribute_value ADD CONSTRAINT unique_ref UNIQUE (fk_product_attribute,ref); 201 202CREATE TABLE llx_product_attribute_combination2val 203( 204 rowid INT PRIMARY KEY NOT NULL AUTO_INCREMENT, 205 fk_prod_combination INT NOT NULL, 206 fk_prod_attr INT NOT NULL, 207 fk_prod_attr_val INT NOT NULL 208); 209CREATE TABLE llx_product_attribute_combination 210( 211 rowid INT PRIMARY KEY NOT NULL AUTO_INCREMENT, 212 fk_product_parent INT NOT NULL, 213 fk_product_child INT NOT NULL, 214 variation_price FLOAT NOT NULL, 215 variation_price_percentage INT NULL, 216 variation_weight FLOAT NOT NULL, 217 entity INT DEFAULT 1 NOT NULL 218); 219 220 221ALTER TABLE llx_bank_account DROP FOREIGN KEY bank_fk_accountancy_journal; 222 223-- Fix missing entity column after init demo 224ALTER TABLE llx_accounting_journal ADD COLUMN entity integer DEFAULT 1; 225 226-- Add journal entries 227INSERT INTO llx_accounting_journal (rowid, code, label, nature, active) VALUES (1,'VT', 'Sale journal', 2, 1); 228INSERT INTO llx_accounting_journal (rowid, code, label, nature, active) VALUES (2,'AC', 'Purchase journal', 3, 1); 229INSERT INTO llx_accounting_journal (rowid, code, label, nature, active) VALUES (3,'BQ', 'Bank journal', 4, 1); 230INSERT INTO llx_accounting_journal (rowid, code, label, nature, active) VALUES (4,'OD', 'Other journal', 1, 1); 231INSERT INTO llx_accounting_journal (rowid, code, label, nature, active) VALUES (5,'AN', 'Has new journal', 9, 1); 232INSERT INTO llx_accounting_journal (rowid, code, label, nature, active) VALUES (6,'ER', 'Expense report journal', 5, 1); 233-- Fix old entries 234UPDATE llx_accounting_journal SET nature = 1 WHERE code = 'OD' AND nature = 0; 235UPDATE llx_accounting_journal SET nature = 2 WHERE code = 'VT' AND nature = 1; 236UPDATE llx_accounting_journal SET nature = 3 WHERE code = 'AC' AND nature = 2; 237UPDATE llx_accounting_journal SET nature = 4 WHERE (code = 'BK' OR code = 'BQ') AND nature = 3; 238 239UPDATE llx_bank_account SET accountancy_journal = 'BQ' WHERE accountancy_journal = 'BK'; 240UPDATE llx_bank_account SET accountancy_journal = 'OD' WHERE accountancy_journal IS NULL; 241 242ALTER TABLE llx_bank_account ADD COLUMN fk_accountancy_journal integer; 243ALTER TABLE llx_bank_account ADD INDEX idx_fk_accountancy_journal (fk_accountancy_journal); 244 245UPDATE llx_bank_account AS ba SET fk_accountancy_journal = (SELECT rowid FROM llx_accounting_journal AS aj WHERE ba.accountancy_journal = aj.code AND aj.entity = ba.entity) WHERE accountancy_journal NOT IN ('1', '2', '3', '4', '5', '6', '5', '8', '9', '10', '11', '12', '13', '14', '15'); 246ALTER TABLE llx_bank_account ADD CONSTRAINT fk_bank_account_accountancy_journal FOREIGN KEY (fk_accountancy_journal) REFERENCES llx_accounting_journal (rowid); 247 248--Update general ledger for FEC format & harmonization 249 250ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN code_tiers varchar(32); 251ALTER TABLE llx_accounting_bookkeeping CHANGE COLUMN code_tiers thirdparty_code varchar(32); 252 253--Subledger account 254ALTER TABLE llx_accounting_bookkeeping ADD COLUMN subledger_account varchar(32); 255ALTER TABLE llx_accounting_bookkeeping CHANGE COLUMN thirdparty_label subledger_label varchar(255); -- If field was already created, rename it 256ALTER TABLE llx_accounting_bookkeeping ADD COLUMN subledger_label varchar(255) AFTER subledger_account; -- If field dod not exists yet 257 258UPDATE llx_accounting_bookkeeping SET subledger_account = numero_compte WHERE subledger_account IS NULL; 259 260ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN label_compte varchar(255); 261ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN code_journal varchar(32); 262 263ALTER TABLE llx_accounting_bookkeeping ADD COLUMN label_operation varchar(255) AFTER label_compte; 264ALTER TABLE llx_accounting_bookkeeping ADD COLUMN multicurrency_amount double AFTER sens; 265ALTER TABLE llx_accounting_bookkeeping ADD COLUMN multicurrency_code varchar(255) AFTER multicurrency_amount; 266ALTER TABLE llx_accounting_bookkeeping ADD COLUMN lettering_code varchar(255) AFTER multicurrency_code; 267ALTER TABLE llx_accounting_bookkeeping ADD COLUMN date_lettering datetime AFTER lettering_code; 268ALTER TABLE llx_accounting_bookkeeping ADD COLUMN journal_label varchar(255) AFTER code_journal; 269ALTER TABLE llx_accounting_bookkeeping ADD COLUMN date_validated datetime AFTER validated; 270 271DROP TABLE llx_accounting_bookkeeping_tmp; 272CREATE TABLE llx_accounting_bookkeeping_tmp 273( 274 rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY, 275 entity integer DEFAULT 1 NOT NULL, -- | multi company id 276 doc_date date NOT NULL, -- FEC:PieceDate 277 doc_type varchar(30) NOT NULL, -- FEC:PieceRef | facture_client/reglement_client/facture_fournisseur/reglement_fournisseur 278 doc_ref varchar(300) NOT NULL, -- | facture_client/reglement_client/... reference number 279 fk_doc integer NOT NULL, -- | facture_client/reglement_client/... rowid 280 fk_docdet integer NOT NULL, -- | facture_client/reglement_client/... line rowid 281 thirdparty_code varchar(32), -- Third party code (customer or supplier) when record is saved (may help debug) 282 subledger_account varchar(32), -- FEC:CompAuxNum | account number of subledger account 283 subledger_label varchar(255), -- FEC:CompAuxLib | label of subledger account 284 numero_compte varchar(32), -- FEC:CompteNum | account number 285 label_compte varchar(255) NOT NULL, -- FEC:CompteLib | label of account 286 label_operation varchar(255), -- FEC:EcritureLib | label of the operation 287 debit double(24,8) NOT NULL, -- FEC:Debit 288 credit double(24,8) NOT NULL, -- FEC:Credit 289 montant double(24,8) NOT NULL, -- FEC:Montant (Not necessary) 290 sens varchar(1) DEFAULT NULL, -- FEC:Sens (Not necessary) 291 multicurrency_amount double(24,8), -- FEC:Montantdevise 292 multicurrency_code varchar(255), -- FEC:Idevise 293 lettering_code varchar(255), -- FEC:EcritureLet 294 date_lettering datetime, -- FEC:DateLet 295 fk_user_author integer NOT NULL, -- | user creating 296 fk_user_modif integer, -- | user making last change 297 date_creation datetime, -- FEC:EcritureDate | creation date 298 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- | date last modification 299 import_key varchar(14), 300 code_journal varchar(32) NOT NULL, -- FEC:JournalCode 301 journal_label varchar(255), -- FEC:JournalLib 302 piece_num integer NOT NULL, -- FEC:EcritureNum 303 validated tinyint DEFAULT 0 NOT NULL, -- | 0 line not validated / 1 line validated (No deleting / No modification) 304 date_validated datetime -- FEC:ValidDate 305) ENGINE=innodb; 306 307ALTER TABLE llx_accounting_bookkeeping_tmp ADD INDEX idx_accounting_bookkeeping_tmp_doc_date (doc_date); 308ALTER TABLE llx_accounting_bookkeeping_tmp ADD INDEX idx_accounting_bookkeeping_tmp_fk_docdet (fk_docdet); 309ALTER TABLE llx_accounting_bookkeeping_tmp ADD INDEX idx_accounting_bookkeeping_tmp_numero_compte (numero_compte); 310ALTER TABLE llx_accounting_bookkeeping_tmp ADD INDEX idx_accounting_bookkeeping_tmp_code_journal (code_journal); 311 312 313ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN debit double(24,8); 314ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN credit double(24,8); 315ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN montant double(24,8); 316ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN multicurrency_amount double(24,8); 317 318 319ALTER TABLE llx_paiementfourn ADD COLUMN model_pdf varchar(255); 320ALTER TABLE llx_paiementfourn ADD COLUMN fk_user_modif integer AFTER fk_user_author; 321 322insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('EXPENSE_REPORT_CREATE','Expense report created','Executed when an expense report is created','expensereport',201); 323insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('EXPENSE_REPORT_VALIDATE','Expense report validated','Executed when an expense report is validated','expensereport',202); 324insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('EXPENSE_REPORT_APPROVE','Expense report approved','Executed when an expense report is approved','expensereport',203); 325insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('EXPENSE_REPORT_PAYED','Expense report billed','Executed when an expense report is set as billed','expensereport',204); 326 327insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('HOLIDAY_CREATE' ,'Leave request created','Executed when a leave request is created','holiday',221); 328insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('HOLIDAY_VALIDATE','Leave request validated','Executed when a leave request is validated','holiday',222); 329insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('HOLIDAY_APPROVE' ,'Leave request approved','Executed when a leave request is approved','holiday',223); 330 331 332ALTER TABLE llx_societe_remise_except ADD COLUMN fk_invoice_supplier_line integer; 333ALTER TABLE llx_societe_remise_except ADD COLUMN fk_invoice_supplier integer; 334ALTER TABLE llx_societe_remise_except ADD COLUMN fk_invoice_supplier_source integer; 335 336ALTER TABLE llx_societe_remise_except ADD CONSTRAINT fk_soc_remise_fk_invoice_supplier_line FOREIGN KEY (fk_invoice_supplier_line) REFERENCES llx_facture_fourn_det (rowid); 337ALTER TABLE llx_societe_remise_except ADD CONSTRAINT fk_societe_remise_fk_invoice_supplier FOREIGN KEY (fk_invoice_supplier) REFERENCES llx_facture_fourn (rowid); 338ALTER TABLE llx_societe_remise_except ADD CONSTRAINT fk_societe_remise_fk_invoice_supplier_source FOREIGN KEY (fk_invoice_supplier) REFERENCES llx_facture_fourn (rowid); 339 340ALTER TABLE llx_facture_rec ADD COLUMN vat_src_code varchar(10) DEFAULT ''; 341ALTER TABLE llx_expensereport_det ADD COLUMN vat_src_code varchar(10) DEFAULT ''; 342 343DELETE FROM llx_const WHERE name = __ENCRYPT('ADHERENT_BANK_USE_AUTO')__; 344 345UPDATE llx_const SET value = __ENCRYPT('moono-lisa')__ WHERE value = __ENCRYPT('moono')__ AND name = __ENCRYPT('FCKEDITOR_SKIN')__; 346DELETE FROM llx_document_model where nom = 'fsfe.fr.php' and type='donation'; 347 348ALTER TABLE llx_product_price ADD COLUMN default_vat_code varchar(10) AFTER tva_tx; 349ALTER TABLE llx_product_customer_price ADD COLUMN default_vat_code varchar(10) AFTER tva_tx; 350ALTER TABLE llx_product_fournisseur_price ADD COLUMN default_vat_code varchar(10) AFTER tva_tx; 351 352ALTER TABLE llx_user ADD COLUMN model_pdf varchar(255); 353ALTER TABLE llx_usergroup ADD COLUMN model_pdf varchar(255); 354 355INSERT INTO llx_const (name, entity, value, type, visible, note) VALUES (__ENCRYPT('PRODUCT_ADDON_PDF_ODT_PATH')__, 1, __ENCRYPT('DOL_DATA_ROOT/doctemplates/products')__, 'chaine', 0, ''); 356INSERT INTO llx_const (name, entity, value, type, visible, note) VALUES (__ENCRYPT('CONTRACT_ADDON_PDF_ODT_PATH')__, 1, __ENCRYPT('DOL_DATA_ROOT/doctemplates/contracts')__, 'chaine', 0, ''); 357INSERT INTO llx_const (name, entity, value, type, visible, note) VALUES (__ENCRYPT('USERGROUP_ADDON_PDF_ODT_PATH')__, 1, __ENCRYPT('DOL_DATA_ROOT/doctemplates/usergroups')__, 'chaine', 0, ''); 358INSERT INTO llx_const (name, entity, value, type, visible, note) VALUES (__ENCRYPT('USER_ADDON_PDF_ODT_PATH')__, 1, __ENCRYPT('DOL_DATA_ROOT/doctemplates/users')__, 'chaine', 0, ''); 359 360INSERT INTO llx_const (name, entity, value, type, visible, note) VALUES (__ENCRYPT('MAIN_ENABLE_OVERWRITE_TRANSLATION')__, 1, __ENCRYPT('1')__, 'chaine', 0, 'Enable overwrote of translation'); 361 362ALTER TABLE llx_chargesociales ADD COLUMN ref varchar(16); 363ALTER TABLE llx_chargesociales ADD COLUMN fk_projet integer DEFAULT NULL; 364 365ALTER TABLE llx_cronjob ADD COLUMN processing integer NOT NULL DEFAULT 0; 366 367ALTER TABLE llx_website ADD COLUMN fk_user_create integer; 368ALTER TABLE llx_website ADD COLUMN fk_user_modif integer; 369 370-- Add missing fields making not possible to enter reference price of products into another currency 371ALTER TABLE llx_product_fournisseur_price ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1; 372ALTER TABLE llx_product_fournisseur_price ADD COLUMN multicurrency_price_ttc double(24,8) DEFAULT NULL; 373 374ALTER TABLE llx_product_fournisseur_price ADD COLUMN fk_multicurrency integer; 375ALTER TABLE llx_product_fournisseur_price ADD COLUMN multicurrency_code varchar(255); 376ALTER TABLE llx_product_fournisseur_price ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1; 377ALTER TABLE llx_product_fournisseur_price ADD COLUMN multicurrency_price double(24,8) DEFAULT NULL; 378ALTER TABLE llx_product_fournisseur_price ADD COLUMN multicurrency_price_ttc double(24,8) DEFAULT NULL; 379 380ALTER TABLE llx_product_fournisseur_price_log ADD COLUMN fk_multicurrency integer; 381ALTER TABLE llx_product_fournisseur_price_log ADD COLUMN multicurrency_code varchar(255); 382ALTER TABLE llx_product_fournisseur_price_log ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1; 383ALTER TABLE llx_product_fournisseur_price_log ADD COLUMN multicurrency_price double(24,8) DEFAULT NULL; 384ALTER TABLE llx_product_fournisseur_price_log ADD COLUMN multicurrency_price_ttc double(24,8) DEFAULT NULL; 385 386ALTER TABLE llx_product_customer_price_log ADD COLUMN default_vat_code varchar(10); 387 388UPDATE llx_contrat SET ref = rowid WHERE ref IS NULL OR ref = ''; 389ALTER TABLE llx_contratdet ADD COLUMN vat_src_code varchar(10) DEFAULT ''; 390 391CREATE TABLE llx_payment_various 392( 393 rowid integer AUTO_INCREMENT PRIMARY KEY, 394 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 395 datec datetime, 396 datep date, 397 datev date, 398 sens smallint DEFAULT 0 NOT NULL, 399 amount double(24,8) DEFAULT 0 NOT NULL, 400 fk_typepayment integer NOT NULL, 401 num_payment varchar(50), 402 label varchar(255), 403 accountancy_code varchar(32), 404 entity integer DEFAULT 1 NOT NULL, 405 note text, 406 fk_bank integer, 407 fk_user_author integer, 408 fk_user_modif integer 409)ENGINE=innodb; 410 411 412CREATE TABLE llx_default_values 413( 414 rowid integer AUTO_INCREMENT PRIMARY KEY, 415 entity integer DEFAULT 1 NOT NULL, -- multi company id 416 type varchar(10), -- 'createform', 'filters', 'sortorder' 417 user_id integer DEFAULT 0 NOT NULL, -- 0 or user id 418 page varchar(255), -- relative url of page 419 param varchar(255), -- parameter 420 value varchar(128) -- value 421)ENGINE=innodb; 422 423ALTER TABLE llx_default_values ADD UNIQUE INDEX uk_default_values(type, entity, user_id, page, param); 424 425 426ALTER TABLE llx_supplier_proposaldet ADD INDEX idx_supplier_proposaldet_fk_supplier_proposal (fk_supplier_proposal); 427ALTER TABLE llx_supplier_proposaldet ADD INDEX idx_supplier_proposaldet_fk_product (fk_product); 428 429UPDATE llx_supplier_proposaldet SET fk_unit = NULL where fk_unit not in (SELECT rowid from llx_c_units); 430ALTER TABLE llx_supplier_proposaldet ADD CONSTRAINT fk_supplier_proposaldet_fk_unit FOREIGN KEY (fk_unit) REFERENCES llx_c_units (rowid); 431 432ALTER TABLE llx_supplier_proposaldet ADD CONSTRAINT fk_supplier_proposaldet_fk_supplier_proposal FOREIGN KEY (fk_supplier_proposal) REFERENCES llx_supplier_proposal (rowid); 433 434-- NEW inventory module 435CREATE TABLE llx_inventory 436( 437rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY, 438entity integer DEFAULT 0, 439ref varchar(48), 440datec datetime DEFAULT NULL, 441tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 442fk_user_author integer, 443fk_user_modif integer, 444fk_user_valid integer, 445fk_warehouse integer DEFAULT 0, 446status integer DEFAULT 0, 447title varchar(255) NOT NULL, 448date_inventory datetime DEFAULT NULL, 449import_key varchar(14) 450)ENGINE=InnoDB; 451 452CREATE TABLE llx_inventorydet 453( 454rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY, 455datec datetime DEFAULT NULL, 456tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 457fk_inventory integer DEFAULT 0, 458fk_warehouse integer DEFAULT 0, 459fk_product integer DEFAULT 0, 460batch varchar(30) DEFAULT NULL, 461qty_view double DEFAULT NULL, 462qty_stock double DEFAULT NULL, 463qty_regulated double DEFAULT NULL, 464pmp double DEFAULT 0, 465pa double DEFAULT 0, 466new_pmp double DEFAULT 0 467)ENGINE=InnoDB; 468 469ALTER TABLE llx_inventory ADD COLUMN datec datetime DEFAULT NULL; 470ALTER TABLE llx_inventory ADD COLUMN tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 471 472ALTER TABLE llx_inventory ADD INDEX idx_inventory_tms (tms); 473ALTER TABLE llx_inventory ADD INDEX idx_inventory_datec (datec); 474ALTER TABLE llx_inventorydet ADD INDEX idx_inventorydet_tms (tms); 475ALTER TABLE llx_inventorydet ADD INDEX idx_inventorydet_datec (datec); 476ALTER TABLE llx_inventorydet ADD INDEX idx_inventorydet_fk_inventory (fk_inventory); 477 478INSERT INTO llx_c_tva(fk_pays,taux,code,recuperableonly,note,active) VALUES (1, '8.5', '85', '0','VAT standard rate (DOM sauf Guyane et Saint-Martin)',0); 479INSERT INTO llx_c_tva(fk_pays,taux,code,recuperableonly,note,active) VALUES (1, '8.5', '85NPR', '1','VAT standard rate (DOM sauf Guyane et Saint-Martin), non perçu par le vendeur mais récupérable par acheteur',0); 480INSERT INTO llx_c_tva(fk_pays,taux,code,recuperableonly,localtax1,localtax1_type,note,active) VALUES (1, '8.5', '85NPROM', '1', 2, 3, 'VAT standard rate (DOM sauf Guyane et Saint-Martin), NPR, Octroi de Mer',0); 481INSERT INTO llx_c_tva(fk_pays,taux,code,recuperableonly,localtax1,localtax1_type,localtax2,localtax2_type,note,active) VALUES (1, '8.5', '85NPROMOMR', '1', 2, 3, 2.5, 3, 'VAT standard rate (DOM sauf Guyane et Saint-Martin), NPR, Octroi de Mer et Octroi de Mer Regional',0); 482 483ALTER TABLE llx_events MODIFY COLUMN ip varchar(250); 484 485ALTER TABLE llx_facture ADD COLUMN fk_fac_rec_source integer; 486 487DELETE FROM llx_c_actioncomm WHERE code IN ('AC_PROP','AC_COM','AC_FAC','AC_SHIP','AC_SUP_ORD','AC_SUP_INV') AND id NOT IN (SELECT DISTINCT fk_action FROM llx_actioncomm); 488 489-- Fix: delete orphelin category. 490DELETE FROM llx_categorie_product WHERE fk_categorie NOT IN (SELECT rowid FROM llx_categorie WHERE type = 0); 491DELETE FROM llx_categorie_societe WHERE fk_categorie NOT IN (SELECT rowid FROM llx_categorie WHERE type IN (1, 2)); 492DELETE FROM llx_categorie_member WHERE fk_categorie NOT IN (SELECT rowid FROM llx_categorie WHERE type = 3); 493DELETE FROM llx_categorie_contact WHERE fk_categorie NOT IN (SELECT rowid FROM llx_categorie WHERE type = 4); 494DELETE FROM llx_categorie_project WHERE fk_categorie NOT IN (SELECT rowid FROM llx_categorie WHERE type = 6); 495 496ALTER TABLE llx_inventory ADD COLUMN ref varchar(48); 497 498-- VPGSQL8.2 ALTER TABLE llx_projet_task ALTER COLUMN planned_workload DROP NOT NULL; 499 500CREATE TABLE llx_loan_schedule 501( 502 rowid integer AUTO_INCREMENT PRIMARY KEY, 503 fk_loan integer, 504 datec datetime, 505 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 506 datep datetime, 507 amount_capital real DEFAULT 0, 508 amount_insurance real DEFAULT 0, 509 amount_interest real DEFAULT 0, 510 fk_typepayment integer NOT NULL, 511 num_payment varchar(50), 512 note_private text, 513 note_public text, 514 fk_bank integer NOT NULL, 515 fk_user_creat integer, 516 fk_user_modif integer 517)ENGINE=innodb; 518 519ALTER TABLE llx_tva ADD COLUMN datec date AFTER tms; 520 521ALTER TABLE llx_user_rights ADD COLUMN entity integer DEFAULT 1 NOT NULL AFTER rowid; 522ALTER TABLE llx_user_rights DROP FOREIGN KEY fk_user_rights_fk_user_user; 523ALTER TABLE llx_user_rights DROP INDEX uk_user_rights; 524ALTER TABLE llx_user_rights DROP INDEX fk_user; 525ALTER TABLE llx_user_rights ADD UNIQUE INDEX uk_user_rights (entity, fk_user, fk_id); 526DELETE FROM llx_user_rights WHERE fk_user NOT IN (select rowid from llx_user); 527ALTER TABLE llx_user_rights ADD CONSTRAINT fk_user_rights_fk_user_user FOREIGN KEY (fk_user) REFERENCES llx_user (rowid); 528 529ALTER TABLE llx_usergroup_rights ADD COLUMN entity integer DEFAULT 1 NOT NULL AFTER rowid; 530ALTER TABLE llx_usergroup_rights DROP FOREIGN KEY fk_usergroup_rights_fk_usergroup; 531ALTER TABLE llx_usergroup_rights DROP INDEX fk_usergroup; 532ALTER TABLE llx_usergroup_rights ADD UNIQUE INDEX uk_usergroup_rights (entity, fk_usergroup, fk_id); 533ALTER TABLE llx_usergroup_rights ADD CONSTRAINT fk_usergroup_rights_fk_usergroup FOREIGN KEY (fk_usergroup) REFERENCES llx_usergroup (rowid); 534 535-- For new module website 536 537CREATE TABLE llx_website_page 538( 539 rowid integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 540 fk_website integer NOT NULL, 541 pageurl varchar(16) NOT NULL, 542 title varchar(255), 543 description varchar(255), 544 keywords varchar(255), 545 content mediumtext, -- text is not enough in size 546 status integer, 547 fk_user_create integer, 548 fk_user_modif integer, 549 date_creation datetime, 550 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 551) ENGINE=innodb; 552 553ALTER TABLE llx_website_page ADD UNIQUE INDEX uk_website_page_url (fk_website,pageurl); 554 555ALTER TABLE llx_website_page ADD CONSTRAINT fk_website_page_website FOREIGN KEY (fk_website) REFERENCES llx_website (rowid); 556 557ALTER TABLE llx_website_page ADD COLUMN fk_user_create integer; 558ALTER TABLE llx_website_page ADD COLUMN fk_user_modif integer; 559 560 561UPDATE llx_extrafields set elementtype='categorie' where elementtype='categories'; 562 563 564-- For new module blockedlog 565 566CREATE TABLE llx_blockedlog 567( 568 rowid integer AUTO_INCREMENT PRIMARY KEY, 569 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 570 action varchar(50), 571 amounts real NOT NULL, 572 signature varchar(100) NOT NULL, 573 signature_line varchar(100) NOT NULL, 574 element varchar(50), 575 fk_object integer, 576 ref_object varchar(100), 577 date_object datetime, 578 object_data text, 579 fk_user integer, 580 entity integer DEFAULT 1 NOT NULL, 581 certified integer 582) ENGINE=innodb; 583 584ALTER TABLE llx_blockedlog ADD INDEX signature (signature); 585ALTER TABLE llx_blockedlog ADD INDEX fk_object_element (fk_object,element); 586ALTER TABLE llx_blockedlog ADD INDEX entity (entity); 587ALTER TABLE llx_blockedlog ADD INDEX fk_user (fk_user); 588ALTER TABLE llx_blockedlog ADD INDEX entity_action (entity,action); 589ALTER TABLE llx_blockedlog ADD INDEX entity_action_certified (entity,action,certified); 590 591CREATE TABLE llx_blockedlog_authority 592( 593 rowid integer AUTO_INCREMENT PRIMARY KEY, 594 blockchain longtext NOT NULL, 595 signature varchar(100) NOT NULL, 596 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 597) ENGINE=innodb; 598 599ALTER TABLE llx_blockedlog_authority ADD INDEX signature (signature); 600 601-- VMYSQL4.1 INSERT IGNORE INTO llx_product_lot (entity, fk_product, batch, eatby, sellby, datec, fk_user_creat, fk_user_modif) SELECT DISTINCT e.entity, ps.fk_product, pb.batch, pb.eatby, pb.sellby, pb.tms, e.fk_user_author, e.fk_user_author from llx_product_batch as pb, llx_product_stock as ps, llx_entrepot as e WHERE pb.fk_product_stock = ps.rowid AND ps.fk_entrepot = e.rowid; 602 603UPDATE llx_bank SET label= '(SupplierInvoicePayment)' WHERE label= 'Règlement fournisseur'; 604UPDATE llx_bank SET label= '(CustomerInvoicePayment)' WHERE label= 'Règlement client'; 605UPDATE llx_bank SET label= '(payment_salary)' WHERE label LIKE 'Règlement salaire'; 606 607ALTER TABLE llx_mailing_cibles MODIFY COLUMN source_url varchar(255); 608 609-- VPGSQL8.2 CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON llx_website FOR EACH ROW EXECUTE PROCEDURE update_modified_column_tms(); 610-- VPGSQL8.2 CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON llx_website_page FOR EACH ROW EXECUTE PROCEDURE update_modified_column_tms(); 611 612 613CREATE TABLE llx_facture_rec_extrafields 614( 615 rowid integer AUTO_INCREMENT PRIMARY KEY, 616 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 617 fk_object integer NOT NULL, 618 import_key varchar(14) 619) ENGINE=innodb; 620 621ALTER TABLE llx_facture_rec_extrafields ADD INDEX idx_facture_rec_extrafields (fk_object); 622 623CREATE TABLE llx_facturedet_rec_extrafields 624( 625 rowid integer AUTO_INCREMENT PRIMARY KEY, 626 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 627 fk_object integer NOT NULL, 628 import_key varchar(14) 629)ENGINE=innodb; 630 631ALTER TABLE llx_facturedet_rec_extrafields ADD INDEX idx_facturedet_rec_extrafields (fk_object); 632 633insert into llx_c_tva(rowid,fk_pays,code,taux,localtax1,localtax1_type,localtax2,localtax2_type,recuperableonly,note,active) values (1178, 117, 'C+S-5', 0, 2.5, '1', 2.5, '1', 0, 'CGST+SGST - Same state sales', 1); 634insert into llx_c_tva(rowid,fk_pays,code,taux,localtax1,localtax1_type,localtax2,localtax2_type,recuperableonly,note,active) values (1179, 117, 'I-5' , 5, 0, '0', 0, '0', 0, 'IGST', 1); 635insert into llx_c_tva(rowid,fk_pays,code,taux,localtax1,localtax1_type,localtax2,localtax2_type,recuperableonly,note,active) values (1180, 117, 'C+S-12', 0, 6, '1', 6, '1', 0, 'CGST+SGST - Same state sales', 1); 636insert into llx_c_tva(rowid,fk_pays,code,taux,localtax1,localtax1_type,localtax2,localtax2_type,recuperableonly,note,active) values (1181, 117, 'I-12' , 12, 0, '0', 0, '0', 0, 'IGST', 1); 637insert into llx_c_tva(rowid,fk_pays,code,taux,localtax1,localtax1_type,localtax2,localtax2_type,recuperableonly,note,active) values (1176, 117, 'C+S-18', 0, 9, '1', 9, '1', 0, 'CGST+SGST - Same state sales', 1); 638insert into llx_c_tva(rowid,fk_pays,code,taux,localtax1,localtax1_type,localtax2,localtax2_type,recuperableonly,note,active) values (1177, 117, 'I-18' , 18, 0, '0', 0, '0', 0, 'IGST', 1); 639insert into llx_c_tva(rowid,fk_pays,code,taux,localtax1,localtax1_type,localtax2,localtax2_type,recuperableonly,note,active) values (1182, 117, 'C+S-28', 0, 14, '1', 14, '1', 0, 'CGST+SGST - Same state sales', 1); 640insert into llx_c_tva(rowid,fk_pays,code,taux,localtax1,localtax1_type,localtax2,localtax2_type,recuperableonly,note,active) values (1183, 117, 'I-28' , 28, 0, '0', 0, '0', 0, 'IGST', 1); 641