1-- 2-- Be carefull to requests order. 3-- This file must be loaded by calling /install/index.php page 4-- when current version is 8.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-- -- VPGSQL8.2 DELETE FROM llx_usergroup_user WHERE fk_user NOT IN (SELECT rowid from llx_user); 32-- -- VMYSQL4.1 DELETE FROM llx_usergroup_user WHERE fk_usergroup NOT IN (SELECT rowid from llx_usergroup); 33 34 35-- Forgot in < 4.0 36 37ALTER TABLE llx_c_ziptown DROP FOREIGN KEY fk_c_ziptown_fk_pays; 38ALTER TABLE llx_c_ziptown ADD CONSTRAINT fk_c_ziptown_fk_pays FOREIGN KEY (fk_pays) REFERENCES llx_c_country(rowid); 39 40-- Forgot in 7.0 41 42-- VMYSQL4.1 DROP INDEX nom on llx_societe; 43-- VMYSQL4.1 ALTER TABLE llx_c_regions drop FOREIGN KEY fk_c_regions_fk_pays; 44-- VMYSQL4.1 ALTER TABLE llx_product_association ADD COLUMN rowid integer AUTO_INCREMENT PRIMARY KEY; 45 46ALTER TABLE llx_website_page ADD COLUMN fk_user_create integer; 47ALTER TABLE llx_website_page ADD COLUMN fk_user_modif integer; 48ALTER TABLE llx_website_page ADD COLUMN type_container varchar(16) NOT NULL DEFAULT 'page'; 49 50ALTER TABLE llx_ecm_files DROP INDEX uk_ecm_files; 51ALTER TABLE llx_ecm_files ADD UNIQUE INDEX uk_ecm_files (filepath, filename, entity); 52 53UPDATE llx_const set name = __ENCRYPT('INVOICE_FREE_TEXT')__ where name = __ENCRYPT('FACTURE_FREE_TEXT')__; 54 55ALTER TABLE llx_chargesociales MODIFY COLUMN amount double(24,8); 56 57 58-- drop very old table (bad name) 59DROP TABLE llx_c_accountancy_category; 60DROP TABLE llx_c_accountingaccount; 61 62-- drop old postgresql unique key 63-- VPGSQL8.2 ALTER TABLE llx_usergroup_rights DROP CONSTRAINT llx_usergroup_rights_fk_usergroup_fk_id_key; 64-- VPGSQL8.2 DROP INDEX llx_usergroup_rights_fk_usergroup_fk_id_key; 65 66update llx_propal set fk_statut = 1 where fk_statut = -1; 67 68ALTER TABLE llx_inventory ADD COLUMN fk_user_creat integer; 69ALTER TABLE llx_inventory ADD COLUMN fk_user_modif integer; 70ALTER TABLE llx_inventory ADD COLUMN fk_user_valid integer; 71ALTER TABLE llx_inventory ADD COLUMN import_key varchar(14); 72 73-- Missing Chart of accounts in migration 7.0.0 74INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 1, 'PCG14-DEV', 'The developed accountancy french plan 2014', 1); 75INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 6, 'PCG_SUISSE', 'Switzerland plan', 1); 76INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES (140, 'PCN-LUXEMBURG', 'Plan comptable normalisé Luxembourgeois', 1); 77INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 80, 'DK-STD', 'Standardkontoplan fra SKAT', 1); 78INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 10, 'PCT', 'The Tunisia plan', 1); 79INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 12, 'PCG', 'The Moroccan chart of accounts', 1); 80 81INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 67,'PC-MIPYME', 'The PYME accountancy Chile plan', 1); 82INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 7,'ENG-BASE', 'England plan', 1); 83INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 49,'SYSCOHADA-BJ', 'Plan comptable Ouest-Africain', 1); 84INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 60,'SYSCOHADA-BF', 'Plan comptable Ouest-Africain', 1); 85INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 24,'SYSCOHADA-CM', 'Plan comptable Ouest-Africain', 1); 86INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 65,'SYSCOHADA-CF', 'Plan comptable Ouest-Africain', 1); 87INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 71,'SYSCOHADA-KM', 'Plan comptable Ouest-Africain', 1); 88INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 72,'SYSCOHADA-CG', 'Plan comptable Ouest-Africain', 1); 89INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 21,'SYSCOHADA-CI', 'Plan comptable Ouest-Africain', 1); 90INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 16,'SYSCOHADA-GA', 'Plan comptable Ouest-Africain', 1); 91INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 87,'SYSCOHADA-GQ', 'Plan comptable Ouest-Africain', 1); 92INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES (147,'SYSCOHADA-ML', 'Plan comptable Ouest-Africain', 1); 93INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES (168,'SYSCOHADA-NE', 'Plan comptable Ouest-Africain', 1); 94INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 73,'SYSCOHADA-CD', 'Plan comptable Ouest-Africain', 1); 95INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 22,'SYSCOHADA-SN', 'Plan comptable Ouest-Africain', 1); 96INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 66,'SYSCOHADA-TD', 'Plan comptable Ouest-Africain', 1); 97INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 15,'SYSCOHADA-TG', 'Plan comptable Ouest-Africain', 1); 98 99-- For 8.0 100 101DROP TABLE llx_website_account; 102DROP TABLE llx_website_account_extrafields; 103 104ALTER TABLE llx_paiementfourn ADD COLUMN fk_user_modif integer AFTER fk_user_author; 105 106-- delete old permission no more used 107DELETE FROM llx_rights_def WHERE perms = 'main' and module = 'commercial'; 108 109delete from llx_rights_def where perms IS NULL; 110delete from llx_user_rights where fk_user not IN (select rowid from llx_user); 111delete from llx_usergroup_rights where fk_usergroup not in (select rowid from llx_usergroup); 112delete from llx_usergroup_rights where fk_id not in (select id from llx_rights_def); 113 114ALTER TABLE llx_inventory ADD COLUMN fk_product integer DEFAULT NULL; 115ALTER TABLE llx_inventory MODIFY COLUMN fk_warehouse integer DEFAULT NULL; 116 117ALTER TABLE llx_c_type_fees DROP COLUMN llx_c_type_fees; 118ALTER TABLE llx_c_type_fees ADD COLUMN type integer DEFAULT 0; 119 120ALTER TABLE llx_c_ecotaxe CHANGE COLUMN libelle label varchar(255); 121 122ALTER TABLE llx_product_fournisseur_price DROP COLUMN unitcharges; 123 124ALTER TABLE llx_societe ADD COLUMN fk_entrepot integer DEFAULT 0; 125ALTER TABLE llx_projet ADD COLUMN bill_time integer DEFAULT 0; 126 127ALTER TABLE llx_societe ADD COLUMN order_min_amount double(24,8) DEFAULT NULL AFTER outstanding_limit; 128ALTER TABLE llx_societe ADD COLUMN supplier_order_min_amount double(24,8) DEFAULT NULL AFTER order_min_amount; 129 130 131create table llx_c_type_container 132( 133 rowid integer AUTO_INCREMENT PRIMARY KEY, 134 code varchar(32) NOT NULL, 135 entity integer DEFAULT 1 NOT NULL, -- multi company id 136 label varchar(64) NOT NULL, 137 module varchar(32) NULL, 138 active tinyint DEFAULT 1 NOT NULL 139)ENGINE=innodb; 140 141ALTER TABLE llx_c_type_container ADD UNIQUE INDEX uk_c_type_container_id (code, entity); 142 143 144ALTER TABLE llx_societe_remise_except ADD COLUMN discount_type integer DEFAULT 0 NOT NULL AFTER fk_soc; 145ALTER TABLE llx_societe_remise_except ADD INDEX idx_societe_remise_except_discount_type (discount_type); 146ALTER TABLE llx_societe ADD COLUMN remise_supplier real DEFAULT 0 AFTER remise_client; 147CREATE TABLE llx_societe_remise_supplier 148( 149 rowid integer AUTO_INCREMENT PRIMARY KEY, 150 entity integer DEFAULT 1 NOT NULL, -- multi company id 151 fk_soc integer NOT NULL, 152 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 153 datec datetime, -- creation date 154 fk_user_author integer, -- creation user 155 remise_supplier double(6,3) DEFAULT 0 NOT NULL, -- discount 156 note text 157)ENGINE=innodb; 158insert into llx_c_type_container (code,label,module,active) values ('page', 'Page', 'system', 1); 159insert into llx_c_type_container (code,label,module,active) values ('banner', 'Banner', 'system', 1); 160insert into llx_c_type_container (code,label,module,active) values ('blogpost', 'BlogPost', 'system', 1); 161insert into llx_c_type_container (code,label,module,active) values ('other', 'Other', 'system', 1); 162 163-- For supplier product buy price in multicurency 164ALTER TABLE llx_product_fournisseur_price CHANGE COLUMN multicurrency_price_ttc multicurrency_unitprice DOUBLE(24,8) NULL DEFAULT NULL; 165ALTER TABLE llx_product_fournisseur_price_log CHANGE COLUMN multicurrency_price_ttc multicurrency_unitprice DOUBLE(24,8) NULL DEFAULT NULL; 166 167ALTER TABLE llx_expensereport_det ADD COLUMN docnumber varchar(128) after fk_expensereport; 168 169ALTER TABLE llx_website_page ADD COLUMN aliasalt varchar(255) after pageurl; 170 171DELETE FROM llx_c_paiement WHERE code = '' or code = '-' or id = 0; 172 173-- Remove duplicate record with same primary key in llx_c_paiement 174DROP TABLE llx_c_paiement_temp; 175CREATE TABLE llx_c_paiement_temp AS SELECT * FROM llx_c_paiement; 176DELETE FROM llx_c_paiement WHERE entity > 1 AND id IN (SELECT cp2.id FROM llx_c_paiement_temp as cp2 WHERE cp2.entity = 1); 177 178-- Add missing keys and primary key 179ALTER TABLE llx_c_paiement DROP INDEX uk_c_paiement; 180ALTER TABLE llx_c_paiement ADD UNIQUE INDEX uk_c_paiement_code(entity, code); 181 182-- VMYSQL4.3 ALTER TABLE llx_c_paiement CHANGE COLUMN id id INTEGER AUTO_INCREMENT PRIMARY KEY; 183-- VPGSQL8.2 CREATE SEQUENCE llx_c_paiement_id_seq OWNED BY llx_c_paiement.id; 184-- VPGSQL8.2 ALTER TABLE llx_c_paiement ADD PRIMARY KEY (id); 185-- VPGSQL8.2 ALTER TABLE llx_c_paiement ALTER COLUMN id SET DEFAULT nextval('llx_c_paiement_id_seq'); 186-- VPGSQL8.2 SELECT setval('llx_c_paiement_id_seq', MAX(id)) FROM llx_c_paiement; 187 188-- Add missing keys and primary key 189ALTER TABLE llx_c_payment_term DROP INDEX uk_c_payment_term; 190ALTER TABLE llx_c_payment_term ADD UNIQUE INDEX uk_c_payment_term_code(entity, code); 191-- VMYSQL4.3 ALTER TABLE llx_c_payment_term CHANGE COLUMN rowid rowid INTEGER AUTO_INCREMENT PRIMARY KEY; 192-- VPGSQL8.2 CREATE SEQUENCE llx_c_payment_term_rowid_seq OWNED BY llx_c_payment_term.rowid; 193-- VPGSQL8.2 ALTER TABLE llx_c_payment_term ADD PRIMARY KEY (rowid); 194-- VPGSQL8.2 ALTER TABLE llx_c_payment_term ALTER COLUMN rowid SET DEFAULT nextval('llx_c_payment_term_rowid_seq'); 195-- VPGSQL8.2 SELECT setval('llx_c_payment_term_rowid_seq', MAX(rowid)) FROM llx_c_payment_term; 196 197ALTER TABLE llx_oauth_token ADD COLUMN tokenstring text; 198 199-- Add field for payment modes 200ALTER TABLE llx_societe_rib ADD COLUMN type varchar(32) DEFAULT 'ban' after rowid; 201ALTER TABLE llx_societe_rib ADD COLUMN last_four varchar(4); 202ALTER TABLE llx_societe_rib ADD COLUMN card_type varchar(255); 203ALTER TABLE llx_societe_rib ADD COLUMN cvn varchar(255); 204ALTER TABLE llx_societe_rib ADD COLUMN exp_date_month INTEGER; 205ALTER TABLE llx_societe_rib ADD COLUMN exp_date_year INTEGER; 206ALTER TABLE llx_societe_rib ADD COLUMN country_code varchar(10); 207ALTER TABLE llx_societe_rib ADD COLUMN approved integer DEFAULT 0; 208ALTER TABLE llx_societe_rib ADD COLUMN email varchar(255); 209ALTER TABLE llx_societe_rib ADD COLUMN ending_date date; 210ALTER TABLE llx_societe_rib ADD COLUMN max_total_amount_of_all_payments double(24,8); 211ALTER TABLE llx_societe_rib ADD COLUMN preapproval_key varchar(255); 212ALTER TABLE llx_societe_rib ADD COLUMN starting_date date; 213ALTER TABLE llx_societe_rib ADD COLUMN total_amount_of_all_payments double(24,8); 214ALTER TABLE llx_societe_rib ADD COLUMN stripe_card_ref varchar(128); 215ALTER TABLE llx_societe_rib ADD COLUMN status integer NOT NULL DEFAULT 1; 216 217UPDATE llx_societe_rib set type = 'ban' where type = '' OR type IS NULL; 218-- VMYSQL4.3 ALTER TABLE llx_societe_rib MODIFY COLUMN type varchar(32) NOT NULL; 219-- VPGSQL8.2 ALTER TABLE llx_societe_rib ALTER COLUMN type SET NOT NULL; 220 221 222ALTER TABLE llx_ticket_msg DROP FOREIGN KEY fk_ticket_msg_fk_track_id; 223 224-- Module ticket 225CREATE TABLE llx_ticket 226( 227 rowid integer AUTO_INCREMENT PRIMARY KEY, 228 entity integer DEFAULT 1, 229 ref varchar(128) NOT NULL, 230 track_id varchar(128) NOT NULL, 231 fk_soc integer DEFAULT 0, 232 fk_project integer DEFAULT 0, 233 origin_email varchar(128), 234 fk_user_create integer, 235 fk_user_assign integer, 236 subject varchar(255), 237 message text, 238 fk_statut integer, 239 resolution integer, 240 progress varchar(100), 241 timing varchar(20), 242 type_code varchar(32), 243 category_code varchar(32), 244 severity_code varchar(32), 245 datec datetime, 246 date_read datetime, 247 date_close datetime, 248 notify_tiers_at_create tinyint, 249 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 250)ENGINE=innodb; 251 252ALTER TABLE llx_ticket ADD COLUMN notify_tiers_at_create integer; 253ALTER TABLE llx_ticket DROP INDEX uk_ticket_rowid_track_id; 254ALTER TABLE llx_ticket ADD UNIQUE uk_ticket_track_id (track_id); 255 256CREATE TABLE llx_ticket_msg 257( 258 rowid integer AUTO_INCREMENT PRIMARY KEY, 259 entity integer DEFAULT 1, 260 fk_track_id varchar(128), 261 fk_user_action integer, 262 datec datetime, 263 message text, 264 private integer DEFAULT 0 265)ENGINE=innodb; 266 267 268ALTER TABLE llx_ticket_msg ADD CONSTRAINT fk_ticket_msg_fk_track_id FOREIGN KEY (fk_track_id) REFERENCES llx_ticket (track_id); 269 270 271CREATE TABLE llx_ticket_extrafields 272( 273 rowid integer AUTO_INCREMENT PRIMARY KEY, 274 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 275 fk_object integer NOT NULL, 276 import_key varchar(14) 277)ENGINE=innodb; 278 279create table llx_facture_rec_extrafields 280( 281 rowid integer AUTO_INCREMENT PRIMARY KEY, 282 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 283 fk_object integer NOT NULL, 284 import_key varchar(14) 285) ENGINE=innodb; 286 287 288-- Create dictionaries tables for ticket 289create table llx_c_ticket_severity 290( 291 rowid integer AUTO_INCREMENT PRIMARY KEY, 292 entity integer DEFAULT 1, 293 code varchar(32) NOT NULL, 294 pos varchar(32) NOT NULL, 295 label varchar(128) NOT NULL, 296 color varchar(10) NOT NULL, 297 active integer DEFAULT 1, 298 use_default integer DEFAULT 1, 299 description varchar(255) 300)ENGINE=innodb; 301 302create table llx_c_ticket_type 303( 304 rowid integer AUTO_INCREMENT PRIMARY KEY, 305 entity integer DEFAULT 1, 306 code varchar(32) NOT NULL, 307 pos varchar(32) NOT NULL, 308 label varchar(128) NOT NULL, 309 active integer DEFAULT 1, 310 use_default integer DEFAULT 1, 311 description varchar(255) 312)ENGINE=innodb; 313 314create table llx_c_ticket_category 315( 316 rowid integer AUTO_INCREMENT PRIMARY KEY, 317 entity integer DEFAULT 1, 318 code varchar(32) NOT NULL, 319 pos varchar(32) NOT NULL, 320 label varchar(128) NOT NULL, 321 active integer DEFAULT 1, 322 use_default integer DEFAULT 1, 323 description varchar(255) 324)ENGINE=innodb; 325 326ALTER TABLE llx_c_ticket_category ADD UNIQUE INDEX uk_code (code, entity); 327ALTER TABLE llx_c_ticket_severity ADD UNIQUE INDEX uk_code (code, entity); 328ALTER TABLE llx_c_ticket_type ADD UNIQUE INDEX uk_code (code, entity); 329 330 331 332-- Load data 333INSERT INTO llx_c_ticket_severity (code, pos, label, color, active, use_default, description) VALUES('LOW', '10', 'Low', '', 1, 0, NULL); 334INSERT INTO llx_c_ticket_severity (code, pos, label, color, active, use_default, description) VALUES('NORMAL', '20', 'Normal', '', 1, 1, NULL); 335INSERT INTO llx_c_ticket_severity (code, pos, label, color, active, use_default, description) VALUES('HIGH', '30', 'High', '', 1, 0, NULL); 336INSERT INTO llx_c_ticket_severity (code, pos, label, color, active, use_default, description) VALUES('BLOCKING', '40', 'Critical / blocking', '', 1, 0, NULL); 337 338INSERT INTO llx_c_ticket_type (code, pos, label, active, use_default, description) VALUES('COM', '10', 'Commercial question', 1, 1, NULL); 339INSERT INTO llx_c_ticket_type (code, pos, label, active, use_default, description) VALUES('ISSUE', '20', 'Issue or problem' , 1, 0, NULL); 340INSERT INTO llx_c_ticket_type (code, pos, label, active, use_default, description) VALUES('REQUEST', '25', 'Change or enhancement request', 1, 0, NULL); 341INSERT INTO llx_c_ticket_type (code, pos, label, active, use_default, description) VALUES('PROJECT', '30', 'Project', 0, 0, NULL); 342INSERT INTO llx_c_ticket_type (code, pos, label, active, use_default, description) VALUES('OTHER', '40', 'Other', 1, 0, NULL); 343 344INSERT INTO llx_c_ticket_category (code, pos, label, active, use_default, description) VALUES('OTHER', '10', 'Other', 1, 1, NULL); 345 346INSERT INTO llx_c_type_contact (rowid, element, source, code, libelle, active, module) VALUES(155, 'ticket', 'internal', 'SUPPORTTEC', 'Utilisateur contact support', 1, NULL); 347INSERT INTO llx_c_type_contact (rowid, element, source, code, libelle, active, module) VALUES(156, 'ticket', 'internal', 'CONTRIBUTOR', 'Intervenant', 1, NULL); 348INSERT INTO llx_c_type_contact (rowid, element, source, code, libelle, active, module) VALUES(157, 'ticket', 'external', 'SUPPORTCLI', 'Contact client suivi incident', 1, NULL); 349INSERT INTO llx_c_type_contact (rowid, element, source, code, libelle, active, module) VALUES(158, 'ticket', 'external', 'CONTRIBUTOR', 'Intervenant', 1, NULL); 350 351 352 353ALTER TABLE llx_facturedet_rec ADD COLUMN date_start_fill integer DEFAULT 0; 354ALTER TABLE llx_facturedet_rec ADD COLUMN date_end_fill integer DEFAULT 0; 355 356 357 358CREATE TABLE llx_societe_account( 359 -- BEGIN MODULEBUILDER FIELDS 360 rowid integer AUTO_INCREMENT PRIMARY KEY NOT NULL, 361 entity integer DEFAULT 1, 362 key_account varchar(128), 363 login varchar(128) NOT NULL, 364 pass_encoding varchar(24), 365 pass_crypted varchar(128), 366 pass_temp varchar(128), -- temporary password when asked for forget password 367 fk_soc integer, 368 site varchar(128), 369 fk_website integer, 370 note_private text, 371 date_last_login datetime, 372 date_previous_login datetime, 373 date_creation datetime NOT NULL, 374 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 375 fk_user_creat integer NOT NULL, 376 fk_user_modif integer, 377 import_key varchar(14), 378 status integer 379 -- END MODULEBUILDER FIELDS 380) ENGINE=innodb; 381 382-- VMYSQL4.3 ALTER TABLE llx_societe_account MODIFY COLUMN pass_encoding varchar(24) NULL; 383 384ALTER TABLE llx_const MODIFY type varchar(64) DEFAULT 'string'; 385 386UPDATE llx_const set type = 'text' where type = 'texte'; 387UPDATE llx_const set type = 'html' where name in (__ENCRYPT('ADHERENT_AUTOREGISTER_NOTIF_MAIL')__,__ENCRYPT('ADHERENT_AUTOREGISTER_MAIL')__,__ENCRYPT('ADHERENT_MAIL_VALID')__,__ENCRYPT('ADHERENT_MAIL_COTIS')__,__ENCRYPT('ADHERENT_MAIL_RESIL')__); 388 389--UPDATE llx_const SET value = '', type = 'emailtemplate:member' WHERE name = __ENCRYPT('ADHERENT_AUTOREGISTER_MAIL')__ AND type != 'emailtemplate:member'; 390--UPDATE llx_const SET value = '', type = 'emailtemplate:member' WHERE name = __ENCRYPT('ADHERENT_MAIL_VALID')__ AND type != 'emailtemplate:member'; 391--UPDATE llx_const SET value = '', type = 'emailtemplate:member' WHERE name = __ENCRYPT('ADHERENT_MAIL_COTIS')__ AND type != 'emailtemplate:member'; 392--UPDATE llx_const SET value = '', type = 'emailtemplate:member' WHERE name = __ENCRYPT('ADHERENT_MAIL_RESIL')__ AND type != 'emailtemplate:member'; 393 394ALTER TABLE llx_societe_account ADD COLUMN key_account varchar(128); 395 396ALTER TABLE llx_societe_account ADD INDEX idx_societe_account_rowid (rowid); 397ALTER TABLE llx_societe_account ADD INDEX idx_societe_account_login (login); 398ALTER TABLE llx_societe_account ADD INDEX idx_societe_account_status (status); 399ALTER TABLE llx_societe_account ADD INDEX idx_societe_account_fk_website (fk_website); 400ALTER TABLE llx_societe_account ADD INDEX idx_societe_account_fk_soc (fk_soc); 401 402ALTER TABLE llx_societe_account ADD UNIQUE INDEX uk_societe_account_login_website_soc(entity, fk_soc, login, site, fk_website); 403ALTER TABLE llx_societe_account ADD UNIQUE INDEX uk_societe_account_key_account_soc(entity, fk_soc, key_account, site, fk_website); 404 405ALTER TABLE llx_societe_account ADD CONSTRAINT llx_societe_account_fk_website FOREIGN KEY (fk_website) REFERENCES llx_website(rowid); 406ALTER TABLE llx_societe_account ADD CONSTRAINT llx_societe_account_fk_societe FOREIGN KEY (fk_soc) REFERENCES llx_societe(rowid); 407 408 409ALTER TABLE llx_societe_rib MODIFY COLUMN max_total_amount_of_all_payments double(24,8); 410ALTER TABLE llx_societe_rib MODIFY COLUMN total_amount_of_all_payments double(24,8); 411 412 413INSERT INTO llx_c_email_templates (entity,module,type_template,lang,private,fk_user,datec,label,position,enabled,active,topic,content,content_lines,joinfiles) VALUES (0,'adherent','member','',0,null,null,'(SendingEmailOnAutoSubscription)' ,10,1,1,'[__[MAIN_INFO_SOCIETE_NOM]__] __(YourMembershipRequestWasReceived)__','__(Hello)__ __MEMBER_FULLNAME__,<br><br>\n\n__(ThisIsContentOfYourMembershipRequestWasReceived)__<br>\n<br>__ONLINE_PAYMENT_TEXT_AND_URL__<br>\n<br><br>\n__(Sincerely)__<br>__USER_SIGNATURE__',null, 0); 414INSERT INTO llx_c_email_templates (entity,module,type_template,lang,private,fk_user,datec,label,position,enabled,active,topic,content,content_lines,joinfiles) VALUES (0,'adherent','member','',0,null,null,'(SendingEmailOnMemberValidation)' ,20,1,1,'[__[MAIN_INFO_SOCIETE_NOM]__] __(YourMembershipWasValidated)__', '__(Hello)__ __MEMBER_FULLNAME__,<br><br>\n\n__(ThisIsContentOfYourMembershipWasValidated)__<br>\n<br>__ONLINE_PAYMENT_TEXT_AND_URL__<br>\n<br><br>\n__(Sincerely)__<br>__USER_SIGNATURE__',null, 0); 415INSERT INTO llx_c_email_templates (entity,module,type_template,lang,private,fk_user,datec,label,position,enabled,active,topic,content,content_lines,joinfiles) VALUES (0,'adherent','member','',0,null,null,'(SendingEmailOnNewSubscription)' ,30,1,1,'[__[MAIN_INFO_SOCIETE_NOM]__] __(YourSubscriptionWasRecorded)__', '__(Hello)__ __MEMBER_FULLNAME__,<br><br>\n\n__(ThisIsContentOfYourSubscriptionWasRecorded)__<br>\n\n<br><br>\n__(Sincerely)__<br>__USER_SIGNATURE__',null, 1); 416INSERT INTO llx_c_email_templates (entity,module,type_template,lang,private,fk_user,datec,label,position,enabled,active,topic,content,content_lines,joinfiles) VALUES (0,'adherent','member','',0,null,null,'(SendingReminderForExpiredSubscription)',40,1,1,'[__[MAIN_INFO_SOCIETE_NOM]__] __(SubscriptionReminderEmail)__', '__(Hello)__ __MEMBER_FULLNAME__,<br><br>\n\n__(ThisIsContentOfSubscriptionReminderEmail)__<br>\n<br>__ONLINE_PAYMENT_TEXT_AND_URL__<br>\n<br><br>\n__(Sincerely)__<br>__USER_SIGNATURE__',null, 0); 417INSERT INTO llx_c_email_templates (entity,module,type_template,lang,private,fk_user,datec,label,position,enabled,active,topic,content,content_lines,joinfiles) VALUES (0,'adherent','member','',0,null,null,'(SendingEmailOnCancelation)' ,50,1,1,'[__[MAIN_INFO_SOCIETE_NOM]__] __(YourMembershipWasCanceled)__', '__(Hello)__ __MEMBER_FULLNAME__,<br><br>\n\n__(YourMembershipWasCanceled)__<br>\n<br><br>\n__(Sincerely)__<br>__USER_SIGNATURE__',null, 0); 418INSERT INTO llx_c_email_templates (entity,module,type_template,lang,private,fk_user,datec,label,position,enabled,active,topic,content,content_lines,joinfiles) VALUES (0,'adherent','member','',0,null,null,'(SendingAnEMailToMember)' ,60,1,1,'[__[MAIN_INFO_SOCIETE_NOM]__] __(CardContent)__', '__(Hello)__,<br><br>\n\n__(ThisIsContentOfYourCard)__<br>\n__(ID)__ : __ID__<br>\n__(Civiliyty)__ : __MEMBER_CIVILITY__<br>\n__(Firstname)__ : __MEMBER_FIRSTNAME__<br>\n__(Lastname)__ : __MEMBER_LASTNAME__<br>\n__(Fullname)__ : __MEMBER_FULLNAME__<br>\n__(Company)__ : __MEMBER_COMPANY__<br>\n__(Address)__ : __MEMBER_ADDRESS__<br>\n__(Zip)__ : __MEMBER_ZIP__<br>\n__(Town)__ : __MEMBER_TOWN__<br>\n__(Country)__ : __MEMBER_COUNTRY__<br>\n__(Email)__ : __MEMBER_EMAIL__<br>\n__(Birthday)__ : __MEMBER_BIRTH__<br>\n__(Photo)__ : __MEMBER_PHOTO__<br>\n__(Login)__ : __MEMBER_LOGIN__<br>\n__(Password)__ : __MEMBER_PASSWORD__<br>\n__(Phone)__ : __MEMBER_PHONE__<br>\n__(PhonePerso)__ : __MEMBER_PHONEPRO__<br>\n__(PhoneMobile)__ : __MEMBER_PHONEMOBILE__<br><br>\n__(Sincerely)__<br>__USER_SIGNATURE__',null, 0); 419 420ALTER TABLE llx_product ADD COLUMN fk_default_warehouse integer DEFAULT NULL; 421ALTER TABLE llx_product ADD CONSTRAINT fk_product_default_warehouse FOREIGN KEY (fk_default_warehouse) REFERENCES llx_entrepot (rowid); 422 423-- Assets 424CREATE TABLE llx_asset( 425 rowid integer AUTO_INCREMENT PRIMARY KEY NOT NULL, 426 ref varchar(128) NOT NULL, 427 entity integer DEFAULT 1 NOT NULL, 428 label varchar(255), 429 amount_ht double(24,8) DEFAULT NULL, 430 amount_vat double(24,8) DEFAULT NULL, 431 fk_asset_type integer NOT NULL, 432 description text, 433 note_public text, 434 note_private text, 435 date_creation datetime NOT NULL, 436 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 437 fk_user_creat integer NOT NULL, 438 fk_user_modif integer, 439 import_key varchar(14), 440 status integer NOT NULL 441) ENGINE=innodb; 442 443ALTER TABLE llx_asset ADD INDEX idx_asset_rowid (rowid); 444ALTER TABLE llx_asset ADD INDEX idx_asset_ref (ref); 445ALTER TABLE llx_asset ADD INDEX idx_asset_entity (entity); 446 447ALTER TABLE llx_asset ADD INDEX idx_asset_fk_asset_type (fk_asset_type); 448 449create table llx_asset_extrafields 450( 451 rowid integer AUTO_INCREMENT PRIMARY KEY, 452 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 453 fk_object integer NOT NULL, 454 import_key varchar(14) 455) ENGINE=innodb; 456 457create table llx_asset_type 458( 459 rowid integer AUTO_INCREMENT PRIMARY KEY, 460 entity integer DEFAULT 1 NOT NULL, -- multi company id 461 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 462 label varchar(50) NOT NULL, 463 accountancy_code_asset varchar(32), 464 accountancy_code_depreciation_asset varchar(32), 465 accountancy_code_depreciation_expense varchar(32), 466 note text 467)ENGINE=innodb; 468 469ALTER TABLE llx_asset_type ADD UNIQUE INDEX uk_asset_type_label (label, entity); 470 471ALTER TABLE llx_asset ADD CONSTRAINT fk_asset_asset_type FOREIGN KEY (fk_asset_type) REFERENCES llx_asset_type (rowid); 472 473create table llx_asset_type_extrafields 474( 475 rowid integer AUTO_INCREMENT PRIMARY KEY, 476 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 477 fk_object integer NOT NULL, 478 import_key varchar(14) -- import key 479) ENGINE=innodb; 480 481ALTER TABLE llx_asset_type_extrafields ADD INDEX idx_asset_type_extrafields (fk_object); 482 483INSERT INTO llx_accounting_journal (rowid, code, label, nature, active) VALUES (7,'INV', 'Inventory journal', 8, 1); 484 485UPDATE llx_accounting_account set account_parent = 0 WHERE account_parent = '' OR account_parent IS NULL; 486-- VMYSQL4.1 ALTER TABLE llx_accounting_account MODIFY COLUMN account_parent integer DEFAULT 0; 487-- VPGSQL8.2 ALTER TABLE llx_accounting_account ALTER COLUMN account_parent DROP DEFAULT; 488-- VPGSQL8.2 ALTER TABLE llx_accounting_account MODIFY COLUMN account_parent integer USING account_parent::integer; 489-- VPGSQL8.2 ALTER TABLE llx_accounting_account ALTER COLUMN account_parent SET DEFAULT 0; 490ALTER TABLE llx_accounting_account ADD INDEX idx_accounting_account_account_parent (account_parent); 491 492UPDATE llx_accounting_bookkeeping set date_creation = tms where date_creation IS NULL; 493 494ALTER TABLE llx_extrafields MODIFY COLUMN list VARCHAR(128); 495 496UPDATE llx_rights_def set module = 'asset' where module = 'assets'; 497 498ALTER TABLE llx_c_accounting_category ADD COLUMN entity integer NOT NULL DEFAULT 1 AFTER rowid; 499-- VMYSQL4.1 DROP INDEX uk_c_accounting_category on llx_c_accounting_category; 500-- VPGSQL8.2 DROP INDEX uk_c_accounting_category; 501ALTER TABLE llx_c_accounting_category ADD UNIQUE INDEX uk_c_accounting_category(code,entity); 502-- VMYSQL4.1 DROP INDEX uk_accounting_journal_code on llx_accounting_journal; 503-- VPGSQL8.2 DROP INDEX uk_accounting_journal_code; 504ALTER TABLE llx_accounting_journal ADD UNIQUE INDEX uk_accounting_journal_code (code,entity); 505 506UPDATE llx_c_email_templates SET lang = '' WHERE lang IS NULL; 507 508-- Warehouse 509ALTER TABLE llx_entrepot ADD COLUMN model_pdf VARCHAR(255) AFTER fk_user_author; 510ALTER TABLE llx_stock_mouvement ADD COLUMN model_pdf VARCHAR(255) AFTER origintype; 511 512 513insert into llx_c_regions (fk_pays, code_region, cheflieu, tncc, nom, active) values ( 118, 11801, '', 0, 'Indonesia', 1); 514 515INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('BA', 11801, NULL, 0, 'BA', 'Bali', 1); 516INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('BB', 11801, NULL, 0, 'BB', 'Bangka Belitung', 1); 517INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('BT', 11801, NULL, 0, 'BT', 'Banten', 1); 518INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('BE', 11801, NULL, 0, 'BA', 'Bengkulu', 1); 519INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('YO', 11801, NULL, 0, 'YO', 'DI Yogyakarta', 1); 520INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('JK', 11801, NULL, 0, 'JK', 'DKI Jakarta', 1); 521INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('GO', 11801, NULL, 0, 'GO', 'Gorontalo', 1); 522INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('JA', 11801, NULL, 0, 'JA', 'Jambi', 1); 523INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('JB', 11801, NULL, 0, 'JB', 'Jawa Barat', 1); 524INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('JT', 11801, NULL, 0, 'JT', 'Jawa Tengah', 1); 525INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('JI', 11801, NULL, 0, 'JI', 'Jawa Timur', 1); 526INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('KB', 11801, NULL, 0, 'KB', 'Kalimantan Barat', 1); 527INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('KS', 11801, NULL, 0, 'KS', 'Kalimantan Selatan', 1); 528INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('KT', 11801, NULL, 0, 'KT', 'Kalimantan Tengah', 1); 529INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('KI', 11801, NULL, 0, 'KI', 'Kalimantan Timur', 1); 530INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('KU', 11801, NULL, 0, 'KU', 'Kalimantan Utara', 1); 531INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('KR', 11801, NULL, 0, 'KR', 'Kepulauan Riau', 1); 532INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('LA', 11801, NULL, 0, 'LA', 'Lampung', 1); 533INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('MA', 11801, NULL, 0, 'MA', 'Maluku', 1); 534INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('MU', 11801, NULL, 0, 'MU', 'Maluku Utara', 1); 535INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('AC', 11801, NULL, 0, 'AC', 'Nanggroe Aceh Darussalam', 1); 536INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('NB', 11801, NULL, 0, 'NB', 'Nusa Tenggara Barat', 1); 537INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('NT', 11801, NULL, 0, 'NT', 'Nusa Tenggara Timur', 1); 538INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('PA', 11801, NULL, 0, 'PA', 'Papua', 1); 539INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('PB', 11801, NULL, 0, 'PB', 'Papua Barat', 1); 540INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('RI', 11801, NULL, 0, 'RI', 'Riau', 1); 541INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('SR', 11801, NULL, 0, 'SR', 'Sulawesi Barat', 1); 542INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('SN', 11801, NULL, 0, 'SN', 'Sulawesi Selatan', 1); 543INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('ST', 11801, NULL, 0, 'ST', 'Sulawesi Tengah', 1); 544INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('SG', 11801, NULL, 0, 'SG', 'Sulawesi Tenggara', 1); 545INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('SA', 11801, NULL, 0, 'SA', 'Sulawesi Utara', 1); 546INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('SB', 11801, NULL, 0, 'SB', 'Sumatera Barat', 1); 547INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('SS', 11801, NULL, 0, 'SS', 'Sumatera Selatan', 1); 548INSERT INTO llx_c_departements ( code_departement, fk_region, cheflieu, tncc, ncc, nom, active) VALUES ('SU', 11801, NULL, 0, 'SU', 'Sumatera Utara ', 1); 549 550-- New available chart of accounts 551INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES (188, 'RO-BASE', 'Plan de conturi romanesc', 1); 552INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 5, 'SKR03', 'Standardkontenrahmen SKR 03', 1); 553INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 5, 'SKR04', 'Standardkontenrahmen SKR 04', 1); 554 555 556-- advtargetmailing 557ALTER TABLE llx_advtargetemailing ADD COLUMN fk_element integer NOT NULL; 558ALTER TABLE llx_advtargetemailing ADD COLUMN type_element varchar(180) NOT NULL; 559UPDATE llx_advtargetemailing SET fk_element = fk_mailing, type_element='mailing'; 560ALTER TABLE llx_advtargetemailing DROP COLUMN fk_mailing; 561 562DROP TABLE llx_ticket_logs; 563 564ALTER TABLE llx_actioncomm MODIFY COLUMN code varchar(50); 565