1-- 2-- Be carefull to requests order. 3-- This file must be loaded by calling /install/index.php page 4-- when current version is 3.8.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 28UPDATE llx_facture_fourn set ref=rowid where ref IS NULL; 29ALTER TABLE llx_facture_fourn MODIFY COLUMN ref varchar(255) NOT NULL; 30 31ALTER TABLE llx_bank_url MODIFY COLUMN type varchar(24) NOT NULL; 32 33-- IVORY COST (id country=21) 34insert into llx_c_tva(rowid,fk_pays,taux,recuperableonly,localtax1,localtax1_type,localtax2,localtax2_type,note,active) values (211, 21, '0','0',0,0,0,0,'IVA Rate 0',1); 35insert into llx_c_tva(rowid,fk_pays,taux,recuperableonly,localtax1,localtax1_type,localtax2,localtax2_type,note,active) values (212, 21, '18','0',7.5,2,0,0,'IVA standard rate',1); 36-- Taiwan VAT Rates 37insert into llx_c_tva(rowid,fk_pays,taux,recuperableonly,note,active) values ( 2131, 213, '5', '0', 'VAT 5%', 1); 38 39ALTER TABLE llx_societe_rib ADD COLUMN rum varchar(32) AFTER default_rib; 40ALTER TABLE llx_societe_rib ADD COLUMN frstrecur varchar(16) DEFAULT 'FRST' AFTER rum; 41 42ALTER TABLE llx_cronjob ADD COLUMN entity integer DEFAULT 0; 43ALTER TABLE llx_cronjob MODIFY COLUMN params text NULL; 44-- VPGSQL8.2 ALTER TABLE llx_cronjob ALTER COLUMN params DROP NOT NULL; 45 46-- Loan 47create table llx_loan 48( 49 rowid integer AUTO_INCREMENT PRIMARY KEY, 50 entity integer DEFAULT 1 NOT NULL, 51 datec datetime, 52 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 53 label varchar(80) NOT NULL, 54 fk_bank integer, 55 capital real DEFAULT 0 NOT NULL, 56 datestart date, 57 dateend date, 58 nbterm real, 59 rate double NOT NULL, 60 note_private text, 61 note_public text, 62 capital_position real DEFAULT 0, 63 date_position date, 64 paid smallint DEFAULT 0 NOT NULL, 65 accountancy_account_capital varchar(32), 66 accountancy_account_insurance varchar(32), 67 accountancy_account_interest varchar(32), 68 fk_user_author integer DEFAULT NULL, 69 fk_user_modif integer DEFAULT NULL, 70 active tinyint DEFAULT 1 NOT NULL 71)ENGINE=innodb; 72 73create table llx_payment_loan 74( 75 rowid integer AUTO_INCREMENT PRIMARY KEY, 76 fk_loan integer, 77 datec datetime, 78 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 79 datep datetime, 80 amount_capital real DEFAULT 0, 81 amount_insurance real DEFAULT 0, 82 amount_interest real DEFAULT 0, 83 fk_typepayment integer NOT NULL, 84 num_payment varchar(50), 85 note_private text, 86 note_public text, 87 fk_bank integer NOT NULL, 88 fk_user_creat integer, 89 fk_user_modif integer 90)ENGINE=innodb; 91 92ALTER TABLE llx_extrafields ADD COLUMN fieldrequired integer DEFAULT 0; 93ALTER TABLE llx_extrafields ADD COLUMN perms varchar(255) AFTER fieldrequired; 94ALTER TABLE llx_extrafields ADD COLUMN list integer DEFAULT 0 AFTER perms; 95 96ALTER TABLE llx_payment_salary ADD COLUMN salary real AFTER datev; 97 98ALTER TABLE llx_payment_salary ADD INDEX idx_payment_salary_ref (num_payment); 99ALTER TABLE llx_payment_salary ADD INDEX idx_payment_salary_user (fk_user, entity); 100ALTER TABLE llx_payment_salary ADD INDEX idx_payment_salary_datep (datep); 101ALTER TABLE llx_payment_salary ADD INDEX idx_payment_salary_datesp (datesp); 102ALTER TABLE llx_payment_salary ADD INDEX idx_payment_salary_dateep (dateep); 103 104ALTER TABLE llx_payment_salary ADD CONSTRAINT fk_payment_salary_user FOREIGN KEY (fk_user) REFERENCES llx_user (rowid); 105 106 107UPDATE llx_projet_task_time SET task_datehour = task_date where task_datehour IS NULL; 108ALTER TABLE llx_projet_task_time ADD COLUMN task_date_withhour integer DEFAULT 0 AFTER task_datehour; 109 110ALTER TABLE llx_projet_task MODIFY COLUMN duration_effective real DEFAULT 0 NULL; 111ALTER TABLE llx_projet_task MODIFY COLUMN planned_workload real DEFAULT 0 NULL; 112 113-- VPGSQL8.2 ALTER TABLE llx_projet_task ALTER COLUMN planned_workload DROP NOT NULL; 114 115ALTER TABLE llx_commande_fournisseur MODIFY COLUMN date_livraison datetime; 116 117-- Add id commandefourndet in llx_commande_fournisseur_dispatch to correct /fourn/commande/dispatch.php display when several times same product in supplier order 118ALTER TABLE llx_commande_fournisseur_dispatch ADD COLUMN fk_commandefourndet INTEGER NOT NULL DEFAULT 0 AFTER fk_product; 119 120 121-- Remove menu entries of removed or renamed modules 122DELETE FROM llx_menu where module = 'printipp'; 123 124 125ALTER TABLE llx_bank ADD INDEX idx_bank_num_releve(num_releve); 126 127 128--create table for price expressions and add column in product supplier 129create table llx_c_price_expression 130( 131 rowid integer AUTO_INCREMENT PRIMARY KEY, 132 title varchar(20) NOT NULL, 133 expression varchar(80) NOT NULL 134)ENGINE=innodb; 135 136ALTER TABLE llx_product_fournisseur_price ADD COLUMN fk_supplier_price_expression integer DEFAULT NULL; 137ALTER TABLE llx_product ADD COLUMN fk_price_expression integer DEFAULT NULL; 138ALTER TABLE llx_product_price ADD COLUMN fk_price_expression integer DEFAULT NULL; 139 140ALTER TABLE llx_product ADD COLUMN fifo double(24,8) AFTER pmp; 141ALTER TABLE llx_product ADD COLUMN lifo double(24,8) AFTER fifo; 142 143 144--create table for user conf of printing driver 145CREATE TABLE llx_printing 146( 147 rowid integer AUTO_INCREMENT PRIMARY KEY, 148 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 149 datec datetime, 150 printer_name text NOT NULL, 151 printer_location text NOT NULL, 152 printer_id varchar(255) NOT NULL, 153 copy integer NOT NULL DEFAULT '1', 154 module varchar(16) NOT NULL, 155 driver varchar(16) NOT NULL, 156 userid integer 157)ENGINE=innodb; 158 159-- Add situation invoices 160ALTER TABLE llx_facture ADD COLUMN situation_cycle_ref smallint; 161ALTER TABLE llx_facture ADD COLUMN situation_counter smallint; 162ALTER TABLE llx_facture ADD COLUMN situation_final smallint; 163ALTER TABLE llx_facturedet ADD COLUMN situation_percent real; 164ALTER TABLE llx_facturedet ADD COLUMN fk_prev_id integer; 165 166-- Convert SMTP config to main entity, so new entities don't get the old values 167UPDATE llx_const SET entity = __ENCRYPT('1')__ WHERE __DECRYPT('entity')__ = 0 AND __DECRYPT('name')__ = 'MAIN_MAIL_SENDMODE'; 168UPDATE llx_const SET entity = __ENCRYPT('1')__ WHERE __DECRYPT('entity')__ = 0 AND __DECRYPT('name')__ = 'MAIN_MAIL_SMTP_PORT'; 169UPDATE llx_const SET entity = __ENCRYPT('1')__ WHERE __DECRYPT('entity')__ = 0 AND __DECRYPT('name')__ = 'MAIN_MAIL_SMTP_SERVER'; 170UPDATE llx_const SET entity = __ENCRYPT('1')__ WHERE __DECRYPT('entity')__ = 0 AND __DECRYPT('name')__ = 'MAIN_MAIL_SMTPS_ID'; 171UPDATE llx_const SET entity = __ENCRYPT('1')__ WHERE __DECRYPT('entity')__ = 0 AND __DECRYPT('name')__ = 'MAIN_MAIL_SMTPS_PW'; 172UPDATE llx_const SET entity = __ENCRYPT('1')__ WHERE __DECRYPT('entity')__ = 0 AND __DECRYPT('name')__ = 'MAIN_MAIL_EMAIL_TLS'; 173 174-- This option with this value is not compatible with 3.8. Value must be set to 'mutiselect', 'select2'... 175DELETE from llx_const where name = 'MAIN_USE_JQUERY_MULTISELECT' and value = '1'; 176 177create table llx_bank_account_extrafields 178( 179 rowid integer AUTO_INCREMENT PRIMARY KEY, 180 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 181 fk_object integer NOT NULL, 182 import_key varchar(14) -- import key 183) ENGINE=innodb; 184 185 186ALTER TABLE llx_stock_mouvement MODIFY COLUMN label varchar(255); 187ALTER TABLE llx_stock_mouvement MODIFY COLUMN price double(24,8) DEFAULT 0; 188ALTER TABLE llx_stock_mouvement ADD COLUMN inventorycode varchar(128); 189 190 191ALTER TABLE llx_product_association ADD COLUMN incdec integer DEFAULT 1; 192 193 194 195ALTER TABLE llx_bank_account_extrafields ADD INDEX idx_bank_account_extrafields (fk_object); 196 197 198create table llx_contratdet_extrafields 199( 200 rowid integer AUTO_INCREMENT PRIMARY KEY, 201 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 202 fk_object integer NOT NULL, -- object id 203 import_key varchar(14) -- import key 204)ENGINE=innodb; 205 206ALTER TABLE llx_contratdet_extrafields ADD INDEX idx_contratdet_extrafields (fk_object); 207 208ALTER TABLE llx_product_fournisseur_price ADD COLUMN delivery_time_days integer; 209 210 211ALTER TABLE llx_commande_fournisseur_dispatch ADD COLUMN comment varchar(255); 212ALTER TABLE llx_commande_fournisseur_dispatch ADD COLUMN status integer; 213ALTER TABLE llx_commande_fournisseur_dispatch ADD COLUMN tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 214ALTER TABLE llx_commande_fournisseur_dispatch ADD COLUMN batch varchar(30) DEFAULT NULL; 215ALTER TABLE llx_commande_fournisseur_dispatch ADD COLUMN eatby date DEFAULT NULL; 216ALTER TABLE llx_commande_fournisseur_dispatch ADD COLUMN sellby date DEFAULT NULL; 217ALTER TABLE llx_stock_mouvement ADD COLUMN batch varchar(30) DEFAULT NULL; 218ALTER TABLE llx_stock_mouvement ADD COLUMN eatby date DEFAULT NULL; 219ALTER TABLE llx_stock_mouvement ADD COLUMN sellby date DEFAULT NULL; 220 221UPDATE llx_product_batch SET batch = 'unknown' WHERE batch IS NULL; 222ALTER TABLE llx_product_batch MODIFY COLUMN batch varchar(30) NOT NULL; 223 224 225CREATE TABLE llx_expensereport ( 226 rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY, 227 ref varchar(50) NOT NULL, 228 entity integer DEFAULT 1 NOT NULL, -- multi company id 229 ref_number_int integer DEFAULT NULL, 230 ref_ext integer, 231 total_ht double(24,8) DEFAULT 0, 232 total_tva double(24,8) DEFAULT 0, 233 localtax1 double(24,8) DEFAULT 0, -- amount total localtax1 234 localtax2 double(24,8) DEFAULT 0, -- amount total localtax2 235 total_ttc double(24,8) DEFAULT 0, 236 date_debut date NOT NULL, 237 date_fin date NOT NULL, 238 date_create datetime NOT NULL, 239 date_valid datetime, 240 date_approve datetime, 241 date_refuse datetime, 242 date_cancel datetime, 243 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 244 fk_user_author integer NOT NULL, 245 fk_user_modif integer DEFAULT NULL, 246 fk_user_valid integer DEFAULT NULL, 247 fk_user_validator integer DEFAULT NULL, 248 fk_user_approve integer DEFAULT NULL, 249 fk_user_refuse integer DEFAULT NULL, 250 fk_user_cancel integer DEFAULT NULL, 251 fk_statut integer NOT NULL, -- 1=draft, 2=validate (waiting approbation), 4=cancel, 5=approved, 6=paid, 99=refused 252 fk_c_paiement integer DEFAULT NULL, 253 paid smallint DEFAULT 0 NOT NULL, 254 note_public text, 255 note_private text, 256 detail_refuse varchar(255) DEFAULT NULL, 257 detail_cancel varchar(255) DEFAULT NULL, 258 integration_compta integer DEFAULT NULL, -- not used 259 fk_bank_account integer DEFAULT NULL, 260 model_pdf varchar(50) DEFAULT NULL 261) ENGINE=innodb; 262 263 264CREATE TABLE llx_expensereport_det 265( 266 rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY, 267 fk_expensereport integer NOT NULL, 268 fk_c_type_fees integer NOT NULL, 269 fk_projet integer, 270 comments text NOT NULL, 271 product_type integer DEFAULT -1, 272 qty real NOT NULL, 273 value_unit real NOT NULL, 274 remise_percent real, 275 tva_tx double(6,3), -- Vat rat 276 localtax1_tx double(6,3) DEFAULT 0, -- localtax1 rate 277 localtax1_type varchar(10) NULL, -- localtax1 type 278 localtax2_tx double(6,3) DEFAULT 0, -- localtax2 rate 279 localtax2_type varchar(10) NULL, -- localtax2 type 280 total_ht double(24,8) DEFAULT 0 NOT NULL, 281 total_tva double(24,8) DEFAULT 0 NOT NULL, 282 total_localtax1 double(24,8) DEFAULT 0, -- Total LocalTax1 for total quantity of line 283 total_localtax2 double(24,8) DEFAULT 0, -- total LocalTax2 for total quantity of line 284 total_ttc double(24,8) DEFAULT 0 NOT NULL, 285 date date NOT NULL, 286 info_bits integer DEFAULT 0, -- TVA NPR ou non 287 special_code integer DEFAULT 0, -- code for special lines 288 rang integer DEFAULT 0, -- position of line 289 import_key varchar(14) 290) ENGINE=innodb; 291 292ALTER TABLE llx_expensereport_det MODIFY COLUMN fk_projet integer NULL; 293ALTER TABLE llx_expensereport_det MODIFY COLUMN fk_c_tva integer NULL; 294 295create table llx_payment_expensereport 296( 297 rowid integer AUTO_INCREMENT PRIMARY KEY, 298 fk_expensereport integer, 299 datec datetime, -- date de creation 300 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 301 datep datetime, -- payment date 302 amount real DEFAULT 0, 303 fk_typepayment integer NOT NULL, 304 num_payment varchar(50), 305 note text, 306 fk_bank integer NOT NULL, 307 fk_user_creat integer, -- creation user 308 fk_user_modif integer -- last modification user 309)ENGINE=innodb; 310 311 312ALTER TABLE llx_projet ADD COLUMN budget_amount double(24,8); 313-- Alias names (commercial, trademark or alias names) 314ALTER TABLE llx_societe ADD COLUMN name_alias varchar(128) NULL; 315 316create table llx_commande_fournisseurdet_extrafields 317( 318 rowid integer AUTO_INCREMENT PRIMARY KEY, 319 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 320 fk_object integer NOT NULL, 321 import_key varchar(14) 322) ENGINE=innodb; 323 324ALTER TABLE llx_commande_fournisseurdet_extrafields ADD INDEX idx_commande_fournisseurdet_extrafields (fk_object); 325 326 327create table llx_facture_fourn_det_extrafields 328( 329 rowid integer AUTO_INCREMENT PRIMARY KEY, 330 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 331 fk_object integer NOT NULL, 332 import_key varchar(14) -- import key 333) ENGINE=innodb; 334 335ALTER TABLE llx_facture_fourn_det_extrafields ADD INDEX idx_facture_fourn_det_extrafields (fk_object); 336 337ALTER TABLE llx_facture_fourn_det ADD COLUMN special_code integer DEFAULT 0; 338ALTER TABLE llx_facture_fourn_det ADD COLUMN rang integer DEFAULT 0; 339ALTER TABLE llx_facture_fourn_det ADD COLUMN fk_parent_line integer NULL AFTER fk_facture_fourn; 340 341ALTER TABLE llx_commande_fournisseurdet ADD COLUMN special_code integer DEFAULT 0; 342ALTER TABLE llx_commande_fournisseurdet ADD COLUMN rang integer DEFAULT 0; 343ALTER TABLE llx_commande_fournisseurdet ADD COLUMN fk_parent_line integer NULL AFTER fk_commande; 344 345ALTER TABLE llx_projet ADD COLUMN date_close datetime DEFAULT NULL; 346ALTER TABLE llx_projet ADD COLUMN fk_user_close integer DEFAULT NULL; 347ALTER TABLE llx_projet ADD COLUMN fk_opp_status integer DEFAULT NULL AFTER fk_statut; 348ALTER TABLE llx_projet ADD COLUMN opp_amount double(24,8) DEFAULT NULL; 349 350 351-- Module AskPriceSupplier -- 352CREATE TABLE llx_askpricesupplier ( 353 rowid integer AUTO_INCREMENT PRIMARY KEY, 354 ref varchar(30) NOT NULL, 355 entity integer NOT NULL DEFAULT '1', 356 ref_ext varchar(255) DEFAULT NULL, 357 ref_int varchar(255) DEFAULT NULL, 358 fk_soc integer DEFAULT NULL, 359 fk_projet integer DEFAULT NULL, 360 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 361 datec datetime DEFAULT NULL, 362 date_valid datetime DEFAULT NULL, 363 date_cloture datetime DEFAULT NULL, 364 fk_user_author integer DEFAULT NULL, 365 fk_user_modif integer DEFAULT NULL, 366 fk_user_valid integer DEFAULT NULL, 367 fk_user_cloture integer DEFAULT NULL, 368 fk_statut smallint NOT NULL DEFAULT '0', 369 price double DEFAULT '0', 370 remise_percent double DEFAULT '0', 371 remise_absolue double DEFAULT '0', 372 remise double DEFAULT '0', 373 total_ht double(24,8) DEFAULT 0, 374 tva double(24,8) DEFAULT 0, 375 localtax1 double(24,8) DEFAULT 0, 376 localtax2 double(24,8) DEFAULT 0, 377 total double(24,8) DEFAULT 0, 378 fk_account integer DEFAULT NULL, 379 fk_currency varchar(3) DEFAULT NULL, 380 fk_cond_reglement integer DEFAULT NULL, 381 fk_mode_reglement integer DEFAULT NULL, 382 note_private text, 383 note_public text, 384 model_pdf varchar(255) DEFAULT NULL, 385 date_livraison date DEFAULT NULL, 386 fk_shipping_method integer DEFAULT NULL, 387 import_key varchar(14) DEFAULT NULL, 388 extraparams varchar(255) DEFAULT NULL 389) ENGINE=innodb; 390 391CREATE TABLE llx_askpricesupplierdet ( 392 rowid integer AUTO_INCREMENT PRIMARY KEY, 393 fk_askpricesupplier integer NOT NULL, 394 fk_parent_line integer DEFAULT NULL, 395 fk_product integer DEFAULT NULL, 396 label varchar(255) DEFAULT NULL, 397 description text, 398 fk_remise_except integer DEFAULT NULL, 399 tva_tx double(6,3) DEFAULT 0, 400 localtax1_tx double(6,3) DEFAULT 0, 401 localtax1_type varchar(10) DEFAULT NULL, 402 localtax2_tx double(6,3) DEFAULT 0, 403 localtax2_type varchar(10) DEFAULT NULL, 404 qty double DEFAULT NULL, 405 remise_percent double DEFAULT '0', 406 remise double DEFAULT '0', 407 price double DEFAULT NULL, 408 subprice double(24,8) DEFAULT 0, 409 total_ht double(24,8) DEFAULT 0, 410 total_tva double(24,8) DEFAULT 0, 411 total_localtax1 double(24,8) DEFAULT 0, 412 total_localtax2 double(24,8) DEFAULT 0, 413 total_ttc double(24,8) DEFAULT 0, 414 product_type integer DEFAULT 0, 415 info_bits integer DEFAULT 0, 416 buy_price_ht double(24,8) DEFAULT 0, 417 fk_product_fournisseur_price integer DEFAULT NULL, 418 special_code integer DEFAULT 0, 419 rang integer DEFAULT 0, 420 ref_fourn varchar(30) DEFAULT NULL 421) ENGINE=innodb; 422 423CREATE TABLE llx_askpricesupplier_extrafields ( 424 rowid integer AUTO_INCREMENT PRIMARY KEY, 425 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 426 fk_object integer NOT NULL, 427 import_key varchar(14) DEFAULT NULL 428) ENGINE=innodb; 429 430CREATE TABLE llx_askpricesupplierdet_extrafields ( 431 rowid integer AUTO_INCREMENT PRIMARY KEY, 432 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 433 fk_object integer NOT NULL, 434 import_key varchar(14) DEFAULT NULL 435) ENGINE=innodb; 436-- End Module AskPriceSupplier -- 437 438 439ALTER TABLE llx_commande_fournisseur ADD COLUMN date_approve2 datetime AFTER date_approve; 440ALTER TABLE llx_commande_fournisseur ADD COLUMN fk_user_approve2 integer AFTER fk_user_approve; 441 442ALTER TABLE llx_societe ADD COLUMN fk_incoterms integer; 443ALTER TABLE llx_societe ADD COLUMN location_incoterms varchar(255); 444ALTER TABLE llx_propal ADD COLUMN fk_incoterms integer; 445ALTER TABLE llx_propal ADD COLUMN location_incoterms varchar(255); 446ALTER TABLE llx_commande ADD COLUMN fk_incoterms integer; 447ALTER TABLE llx_commande ADD COLUMN location_incoterms varchar(255); 448ALTER TABLE llx_commande_fournisseur ADD COLUMN fk_incoterms integer; 449ALTER TABLE llx_commande_fournisseur ADD COLUMN location_incoterms varchar(255); 450ALTER TABLE llx_facture ADD COLUMN fk_incoterms integer; 451ALTER TABLE llx_facture ADD COLUMN location_incoterms varchar(255); 452ALTER TABLE llx_facture_fourn ADD COLUMN fk_incoterms integer; 453ALTER TABLE llx_facture_fourn ADD COLUMN location_incoterms varchar(255); 454ALTER TABLE llx_expedition ADD COLUMN fk_incoterms integer; 455ALTER TABLE llx_expedition ADD COLUMN location_incoterms varchar(255); 456ALTER TABLE llx_livraison ADD COLUMN fk_incoterms integer; 457ALTER TABLE llx_livraison ADD COLUMN location_incoterms varchar(255); 458 459CREATE TABLE llx_c_incoterms ( 460 rowid integer AUTO_INCREMENT PRIMARY KEY, 461 code varchar(3) NOT NULL, 462 libelle varchar(255) NOT NULL, 463 active tinyint DEFAULT 1 NOT NULL 464) ENGINE=innodb; 465 466ALTER TABLE llx_c_incoterms ADD UNIQUE INDEX uk_c_incoterms (code); 467 468INSERT INTO llx_c_incoterms (code, libelle, active) VALUES ('EXW', 'Ex Works, au départ non chargé, non dédouané sortie d''usine (uniquement adapté aux flux domestiques, nationaux)', 1); 469INSERT INTO llx_c_incoterms (code, libelle, active) VALUES ('FCA', 'Free Carrier, marchandises dédouanées et chargées dans le pays de départ, chez le vendeur ou chez le commissionnaire de transport de l''acheteur', 1); 470INSERT INTO llx_c_incoterms (code, libelle, active) VALUES ('FAS', 'Free Alongside Ship, sur le quai du port de départ', 1); 471INSERT INTO llx_c_incoterms (code, libelle, active) VALUES ('FOB', 'Free On Board, chargé sur le bateau, les frais de chargement dans celui-ci étant fonction du liner term indiqué par la compagnie maritime (à la charge du vendeur)', 1); 472INSERT INTO llx_c_incoterms (code, libelle, active) VALUES ('CFR', 'Cost and Freight, chargé dans le bateau, livraison au port de départ, frais payés jusqu''au port d''arrivée, sans assurance pour le transport, non déchargé du navire à destination (les frais de déchargement sont inclus ou non au port d''arrivée)', 1); 473INSERT INTO llx_c_incoterms (code, libelle, active) VALUES ('CIF', 'Cost, Insurance and Freight, chargé sur le bateau, frais jusqu''au port d''arrivée, avec l''assurance marchandise transportée souscrite par le vendeur pour le compte de l''acheteur', 1); 474INSERT INTO llx_c_incoterms (code, libelle, active) VALUES ('CPT', 'Carriage Paid To, livraison au premier transporteur, frais jusqu''au déchargement du mode de transport, sans assurance pour le transport', 1); 475INSERT INTO llx_c_incoterms (code, libelle, active) VALUES ('CIP', 'Carriage and Insurance Paid to, idem CPT, avec assurance marchandise transportée souscrite par le vendeur pour le compte de l''acheteur', 1); 476INSERT INTO llx_c_incoterms (code, libelle, active) VALUES ('DAT', 'Delivered At Terminal, marchandises (déchargées) livrées sur quai, dans un terminal maritime, fluvial, aérien, routier ou ferroviaire désigné (dédouanement import, et post-acheminement payés par l''acheteur)', 1); 477INSERT INTO llx_c_incoterms (code, libelle, active) VALUES ('DAP', 'Delivered At Place, marchandises (non déchargées) mises à disposition de l''acheteur dans le pays d''importation au lieu précisé dans le contrat (déchargement, dédouanement import payé par l''acheteur)', 1); 478INSERT INTO llx_c_incoterms (code, libelle, active) VALUES ('DDP', 'Delivered Duty Paid, marchandises (non déchargées) livrées à destination finale, dédouanement import et taxes à la charge du vendeur ; l''acheteur prend en charge uniquement le déchargement (si exclusion des taxes type TVA, le préciser clairement)', 1); 479 480-- Extrafields fk_object must be unique (1-1 relation) 481ALTER TABLE llx_societe_extrafields DROP INDEX idx_societe_extrafields; 482ALTER TABLE llx_societe_extrafields ADD UNIQUE INDEX uk_societe_extrafields (fk_object); 483 484-- Module Donation 485ALTER TABLE llx_don ADD COLUMN fk_country integer NOT NULL DEFAULT 0 AFTER country; 486ALTER TABLE llx_don CHANGE COLUMN fk_paiement fk_payment integer; 487ALTER TABLE llx_don ADD COLUMN paid smallint DEFAULT 0 NOT NULL AFTER fk_payment; 488ALTER TABLE llx_don CHANGE COLUMN fk_don_projet fk_projet integer NULL; 489ALTER TABLE llx_don CHANGE COLUMN fk_project fk_projet integer NULL; 490 491create table llx_don_extrafields 492( 493 rowid integer AUTO_INCREMENT PRIMARY KEY, 494 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 495 fk_object integer NOT NULL, 496 import_key varchar(14) -- import key 497) ENGINE=innodb; 498 499ALTER TABLE llx_don_extrafields ADD INDEX idx_don_extrafields (fk_object); 500 501create table llx_payment_donation 502( 503 rowid integer AUTO_INCREMENT PRIMARY KEY, 504 fk_donation integer, 505 datec datetime, -- date de creation 506 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 507 datep datetime, -- payment date 508 amount real DEFAULT 0, 509 fk_typepayment integer NOT NULL, 510 num_payment varchar(50), 511 note text, 512 fk_bank integer NOT NULL, 513 fk_user_creat integer, -- creation user 514 fk_user_modif integer -- last modification user 515)ENGINE=innodb; 516 517insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BILL_VALIDATE','Customer invoice validated','Executed when a customer invoice is approved','facture',6); 518insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SUPPLIER_APPROVE','Supplier order request approved','Executed when a supplier order is approved','order_supplier',12); 519insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SUPPLIER_REFUSE','Supplier order request refused','Executed when a supplier order is refused','order_supplier',13); 520insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_VALIDATE','Customer order validate','Executed when a customer order is validated','commande',4); 521insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROPAL_VALIDATE','Customer proposal validated','Executed when a commercial proposal is validated','propal',2); 522insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('COMPANY_SENTBYMAIL','Mails sent from third party card','Executed when you send email from third party card','societe',1); 523insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('COMPANY_CREATE','Third party created','Executed when a third party is created','societe',1); 524insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('CONTRACT_VALIDATE','Contract validated','Executed when a contract is validated','contrat',18); 525insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROPAL_SENTBYMAIL','Commercial proposal sent by mail','Executed when a commercial proposal is sent by mail','propal',3); 526insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SENTBYMAIL','Customer order sent by mail','Executed when a customer order is sent by mail ','commande',5); 527insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BILL_PAYED','Customer invoice paid','Executed when a customer invoice is paid','facture',7); 528insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BILL_CANCEL','Customer invoice canceled','Executed when a customer invoice is conceled','facture',8); 529insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BILL_SENTBYMAIL','Customer invoice sent by mail','Executed when a customer invoice is sent by mail','facture',9); 530insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SUPPLIER_VALIDATE','Supplier order validated','Executed when a supplier order is validated','order_supplier',11); 531insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SUPPLIER_SENTBYMAIL','Supplier order sent by mail','Executed when a supplier order is sent by mail','order_supplier',14); 532insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BILL_SUPPLIER_VALIDATE','Supplier invoice validated','Executed when a supplier invoice is validated','invoice_supplier',15); 533insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BILL_SUPPLIER_PAYED','Supplier invoice paid','Executed when a supplier invoice is paid','invoice_supplier',16); 534insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BILL_SUPPLIER_SENTBYMAIL','Supplier invoice sent by mail','Executed when a supplier invoice is sent by mail','invoice_supplier',17); 535insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BILL_SUPPLIER_CANCELED','Supplier invoice cancelled','Executed when a supplier invoice is cancelled','invoice_supplier',17); 536insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('SHIPPING_VALIDATE','Shipping validated','Executed when a shipping is validated','shipping',20); 537insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('SHIPPING_SENTBYMAIL','Shipping sent by mail','Executed when a shipping is sent by mail','shipping',21); 538insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MEMBER_VALIDATE','Member validated','Executed when a member is validated','member',22); 539insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MEMBER_SUBSCRIPTION','Member subscribed','Executed when a member is subscribed','member',23); 540insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MEMBER_RESILIATE','Member resiliated','Executed when a member is resiliated','member',24); 541insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MEMBER_MODIFY','Member modified','Executed when a member is modified','member',24); 542insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MEMBER_DELETE','Member deleted','Executed when a member is deleted','member',25); 543insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('BILL_UNVALIDATE','Customer invoice unvalidated','Executed when a customer invoice status set back to draft','facture',10); 544insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('FICHINTER_VALIDATE','Intervention validated','Executed when a intervention is validated','ficheinter',19); 545insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('FICHINTER_CLASSIFY_BILLED','Intervention set billed','Executed when a intervention is set to billed (when option FICHINTER_CLASSIFY_BILLED is set)','ficheinter',19); 546insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('FICHINTER_CLASSIFY_UNBILLED','Intervention set unbilled','Executed when a intervention is set to unbilled (when option FICHINTER_CLASSIFY_BILLED is set)','ficheinter',19); 547insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('FICHINTER_REOPEN','Intervention opened','Executed when a intervention is re-opened','ficheinter',19); 548insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('FICHINTER_SENTBYMAIL','Intervention sent by mail','Executed when a intervention is sent by mail','ficheinter',19); 549insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROJECT_CREATE','Project creation','Executed when a project is created','project',30); 550insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROPAL_CLOSE_SIGNED','Customer proposal closed signed','Executed when a customer proposal is closed signed','propal',2); 551insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROPAL_CLOSE_REFUSED','Customer proposal closed refused','Executed when a customer proposal is closed refused','propal',2); 552insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROPAL_CLASSIFY_BILLED','Customer proposal set billed','Executed when a customer proposal is set to billed','propal',2); 553insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('TASK_CREATE','Task created','Executed when a project task is created','project',35); 554insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('TASK_MODIFY','Task modified','Executed when a project task is modified','project',36); 555insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('TASK_DELETE','Task deleted','Executed when a project task is deleted','project',37); 556 557create table llx_c_price_global_variable 558( 559 rowid integer AUTO_INCREMENT PRIMARY KEY, 560 code varchar(20) NOT NULL, 561 description text DEFAULT NULL, 562 value double(24,8) DEFAULT 0 563)ENGINE=innodb; 564 565create table llx_c_price_global_variable_updater 566( 567 rowid integer AUTO_INCREMENT PRIMARY KEY, 568 type integer NOT NULL, 569 description text DEFAULT NULL, 570 parameters text DEFAULT NULL, 571 fk_variable integer NOT NULL, 572 update_interval integer DEFAULT 0, 573 next_update integer DEFAULT 0, 574 last_status text DEFAULT NULL 575)ENGINE=innodb; 576 577ALTER TABLE llx_adherent CHANGE COLUMN note note_private text DEFAULT NULL; 578ALTER TABLE llx_adherent ADD COLUMN note_public text DEFAULT NULL AFTER note_private; 579 580CREATE TABLE IF NOT EXISTS llx_propal_merge_pdf_product ( 581 rowid integer NOT NULL auto_increment PRIMARY KEY, 582 fk_product integer NOT NULL, 583 file_name varchar(200) NOT NULL, 584 lang varchar(5) DEFAULT NULL, 585 fk_user_author integer DEFAULT NULL, 586 fk_user_mod integer NOT NULL, 587 datec datetime NOT NULL, 588 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 589 import_key varchar(14) DEFAULT NULL 590) ENGINE=InnoDB; 591 592 593-- Units 594create table llx_c_units( 595 rowid integer AUTO_INCREMENT PRIMARY KEY, 596 code varchar(3), 597 label varchar(50), 598 short_label varchar(5), 599 active tinyint DEFAULT 1 NOT NULL 600)ENGINE=innodb; 601ALTER TABLE llx_c_units ADD UNIQUE uk_c_units_code(code); 602 603INSERT INTO llx_c_units ( code, label, short_label, active) VALUES ('P','piece','p', 1); 604INSERT INTO llx_c_units ( code, label, short_label, active) VALUES ('SET','set','se', 1); 605INSERT INTO llx_c_units ( code, label, short_label, active) VALUES ('S','second','s', 1); 606INSERT INTO llx_c_units ( code, label, short_label, active) VALUES ('H','hour','h', 1); 607INSERT INTO llx_c_units ( code, label, short_label, active) VALUES ('D','day','d', 1); 608INSERT INTO llx_c_units ( code, label, short_label, active) VALUES ('KG','kilogram','kg', 1); 609INSERT INTO llx_c_units ( code, label, short_label, active) VALUES ('G','gram','g', 1); 610INSERT INTO llx_c_units ( code, label, short_label, active) VALUES ('M','meter','m', 1); 611INSERT INTO llx_c_units ( code, label, short_label, active) VALUES ('LM','linear meter','lm', 1); 612INSERT INTO llx_c_units ( code, label, short_label, active) VALUES ('M2','square meter','m2', 1); 613INSERT INTO llx_c_units ( code, label, short_label, active) VALUES ('M3','cubic meter','m3', 1); 614INSERT INTO llx_c_units ( code, label, short_label, active) VALUES ('L','liter','l', 1); 615 616alter table llx_product add fk_unit integer DEFAULT NULL; 617ALTER TABLE llx_product ADD CONSTRAINT fk_product_fk_unit FOREIGN KEY (fk_unit) REFERENCES llx_c_units (rowid); 618 619alter table llx_facturedet_rec add fk_unit integer DEFAULT NULL; 620ALTER TABLE llx_facturedet_rec ADD CONSTRAINT fk_facturedet_rec_fk_unit FOREIGN KEY (fk_unit) REFERENCES llx_c_units (rowid); 621 622alter table llx_facturedet add fk_unit integer DEFAULT NULL; 623ALTER TABLE llx_facturedet ADD CONSTRAINT fk_facturedet_fk_unit FOREIGN KEY (fk_unit) REFERENCES llx_c_units (rowid); 624 625alter table llx_propaldet add fk_unit integer DEFAULT NULL; 626ALTER TABLE llx_propaldet ADD CONSTRAINT fk_propaldet_fk_unit FOREIGN KEY (fk_unit) REFERENCES llx_c_units (rowid); 627 628alter table llx_commandedet add fk_unit integer DEFAULT NULL; 629ALTER TABLE llx_commandedet ADD CONSTRAINT fk_commandedet_fk_unit FOREIGN KEY (fk_unit) REFERENCES llx_c_units (rowid); 630 631alter table llx_contratdet add fk_unit integer DEFAULT NULL; 632ALTER TABLE llx_contratdet ADD CONSTRAINT fk_contratdet_fk_unit FOREIGN KEY (fk_unit) REFERENCES llx_c_units (rowid); 633 634alter table llx_commande_fournisseurdet add fk_unit integer DEFAULT NULL; 635ALTER TABLE llx_commande_fournisseurdet ADD CONSTRAINT fk_commande_fournisseurdet_fk_unit FOREIGN KEY (fk_unit) REFERENCES llx_c_units (rowid); 636 637alter table llx_facture_fourn_det add fk_unit integer DEFAULT NULL; 638ALTER TABLE llx_facture_fourn_det ADD CONSTRAINT fk_facture_fourn_det_fk_unit FOREIGN KEY (fk_unit) REFERENCES llx_c_units (rowid); 639 640 641 642 643-- Feature request: A page to merge two thirdparties into one #2613 644ALTER TABLE llx_categorie_societe DROP FOREIGN KEY fk_categorie_societe_fk_soc; 645ALTER TABLE llx_categorie_societe CHANGE COLUMN fk_societe fk_soc INTEGER NOT NULL; 646ALTER TABLE llx_categorie_societe ADD CONSTRAINT fk_categorie_societe_fk_soc FOREIGN KEY (fk_soc) REFERENCES llx_societe (rowid); 647 648ALTER TABLE llx_categorie_fournisseur DROP FOREIGN KEY fk_categorie_fournisseur_fk_soc; 649ALTER TABLE llx_categorie_fournisseur CHANGE COLUMN fk_societe fk_soc INTEGER NOT NULL; 650ALTER TABLE llx_categorie_fournisseur ADD CONSTRAINT fk_categorie_fournisseur_fk_soc FOREIGN KEY (fk_soc) REFERENCES llx_societe (rowid); 651 652ALTER TABLE llx_user DROP INDEX uk_user_fk_societe; 653ALTER TABLE llx_user DROP INDEX idx_user_fk_societe; 654ALTER TABLE llx_user CHANGE COLUMN fk_societe fk_soc INTEGER; 655ALTER TABLE llx_user ADD INDEX idx_user_fk_societe (fk_soc); 656 657ALTER TABLE llx_user ADD gender VARCHAR(10); 658 659-- API module 660ALTER TABLE llx_user ADD api_key VARCHAR(128) DEFAULT NULL AFTER pass_temp; 661ALTER TABLE llx_user ADD INDEX idx_user_api_key (api_key); 662 663-- Deprecated fields 664ALTER TABLE llx_actioncomm DROP COLUMN datea; 665ALTER TABLE llx_actioncomm DROP INDEX idx_actioncomm_datea; 666ALTER TABLE llx_actioncomm DROP COLUMN datea2; 667 668-- Email tracking 669ALTER TABLE llx_actioncomm ADD COLUMN email_msgid varchar(255); 670ALTER TABLE llx_actioncomm ADD COLUMN email_from varchar(255); 671ALTER TABLE llx_actioncomm ADD COLUMN email_sender varchar(255); 672ALTER TABLE llx_actioncomm ADD COLUMN email_to varchar(255); 673ALTER TABLE llx_actioncomm ADD COLUMN errors_to varchar(255); 674 675-- Recurring events 676ALTER TABLE llx_actioncomm ADD COLUMN recurid varchar(128); 677ALTER TABLE llx_actioncomm ADD COLUMN recurrule varchar(128); 678ALTER TABLE llx_actioncomm ADD COLUMN recurdateend datetime; 679 680ALTER TABLE llx_c_stcomm ADD COLUMN picto varchar(128); 681 682-- New trigger for Supplier invoice unvalidation 683INSERT INTO llx_c_action_trigger (code, label, description, elementtype, rang) VALUES ('BILL_SUPPLIER_UNVALIDATE','Supplier invoice unvalidated','Executed when a supplier invoice status is set back to draft','invoice_supplier',15); 684 685 686--VMYSQL4.1 ALTER TABLE llx_holiday_users DROP PRIMARY KEY; 687--VPGSQL8.2 ALTER TABLE llx_holiday_users DROP CONSTRAINT llx_holiday_users_pkey; 688 689DROP TABLE llx_holiday_types; 690 691CREATE TABLE llx_c_holiday_types ( 692 rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY, 693 code varchar(16) NOT NULL, 694 label varchar(255) NOT NULL, 695 affect integer NOT NULL, 696 delay integer NOT NULL, 697 newByMonth double(8,5) DEFAULT 0 NOT NULL, 698 fk_country integer DEFAULT NULL, 699 active integer DEFAULT 1 700) ENGINE=innodb; 701 702ALTER TABLE llx_c_holiday_types ADD UNIQUE INDEX uk_c_holiday_types(code); 703 704insert into llx_c_holiday_types(code, label, affect, delay, newByMonth, fk_country) values ('LEAVE_PAID', 'Paid vacation', 1, 7, 0, NULL); 705insert into llx_c_holiday_types(code, label, affect, delay, newByMonth, fk_country) values ('LEAVE_SICK', 'Sick leave', 0, 0, 0, NULL); 706insert into llx_c_holiday_types(code, label, affect, delay, newByMonth, fk_country) values ('LEAVE_OTHER','Other leave', 0, 0, 0, NULL); 707-- Leaves specific to France 708insert into llx_c_holiday_types(code, label, affect, delay, newByMonth, fk_country) values ('LEAVE_RTT', 'RTT' , 1, 7, 0.83, 1); 709 710ALTER TABLE llx_holiday ADD COLUMN fk_type integer NOT NULL DEFAULT 1; 711ALTER TABLE llx_holiday_users ADD COLUMN fk_type integer NOT NULL DEFAULT 1; 712ALTER TABLE llx_holiday_logs ADD COLUMN fk_type integer NOT NULL DEFAULT 1; 713 714UPDATE llx_holiday_users SET fk_type = 1 WHERE fk_type IS NULL; 715UPDATE llx_holiday_logs SET fk_type = 1 WHERE fk_type IS NULL; 716 717UPDATE llx_const SET name = __ENCRYPT('ACCOUNTING_VAT_SOLD_ACCOUNT')__ WHERE __DECRYPT('name')__ = 'ACCOUNTING_VAT_ACCOUNT'; 718 719create table llx_c_lead_status 720( 721 rowid integer AUTO_INCREMENT PRIMARY KEY, 722 code varchar(10), 723 label varchar(50), 724 position integer, 725 percent double(5,2), 726 active tinyint DEFAULT 1 NOT NULL 727)ENGINE=innodb; 728 729-- Opportunities status 730INSERT INTO llx_c_lead_status(rowid,code,label,position,percent,active) VALUES (1,'PROSP' ,'Prospection', 10, 0,1); 731INSERT INTO llx_c_lead_status(rowid,code,label,position,percent,active) VALUES (2,'QUAL' ,'Qualification',20, 20,1); 732INSERT INTO llx_c_lead_status(rowid,code,label,position,percent,active) VALUES (3,'PROPO' ,'Proposal', 30, 40,1); 733INSERT INTO llx_c_lead_status(rowid,code,label,position,percent,active) VALUES (4,'NEGO' ,'Negotiation', 40, 60,1); 734INSERT INTO llx_c_lead_status(rowid,code,label,position,percent,active) VALUES (5,'PENDING','Pending', 50, 50,0); 735INSERT INTO llx_c_lead_status(rowid,code,label,position,percent,active) VALUES (6,'WON' ,'Won', 60, 100,1); 736INSERT INTO llx_c_lead_status(rowid,code,label,position,percent,active) VALUES (7,'LOST' ,'Lost', 70, 0,1); 737 738 739DELETE FROM llx_c_action_trigger where code = 'PROPAL_CLASSIFYBILLED'; 740DELETE FROM llx_c_action_trigger where code = 'FICHINTER_CLASSIFYBILLED'; 741 742-- Spain provinces to ISO codes 743UPDATE llx_c_departements SET code_departement='VI' WHERE ncc='ALAVA' AND fk_region=419; 744UPDATE llx_c_departements SET code_departement='AB' WHERE ncc='ALBACETE' AND fk_region=404; 745UPDATE llx_c_departements SET code_departement='A' WHERE ncc='ALICANTE' AND fk_region=411; 746UPDATE llx_c_departements SET code_departement='AL' WHERE ncc='ALMERIA' AND fk_region=401; 747UPDATE llx_c_departements SET code_departement='AV' WHERE ncc='AVILA' AND fk_region=403; 748UPDATE llx_c_departements SET code_departement='BA' WHERE ncc='BADAJOZ' AND fk_region=412; 749UPDATE llx_c_departements SET code_departement='PM' WHERE ncc='ISLAS BALEARES' AND fk_region=414; 750UPDATE llx_c_departements SET code_departement='B' WHERE ncc='BARCELONA' AND fk_region=406; 751UPDATE llx_c_departements SET code_departement='BU' WHERE ncc='BURGOS' AND fk_region=403; 752UPDATE llx_c_departements SET code_departement='CC' WHERE ncc='CACERES' AND fk_region=412; 753UPDATE llx_c_departements SET code_departement='CA' WHERE ncc='CADIZ' AND fk_region=401; 754UPDATE llx_c_departements SET code_departement='CS' WHERE ncc='CASTELLON' AND fk_region=411; 755UPDATE llx_c_departements SET code_departement='CR' WHERE ncc='CIUDAD REAL' AND fk_region=404; 756UPDATE llx_c_departements SET code_departement='CO' WHERE ncc='CORDOBA' AND fk_region=401; 757UPDATE llx_c_departements SET code_departement='C' WHERE ncc='LA CORUÑA' AND fk_region=413; 758UPDATE llx_c_departements SET code_departement='CU' WHERE ncc='CUENCA' AND fk_region=404; 759UPDATE llx_c_departements SET code_departement='GI' WHERE ncc='GERONA' AND fk_region=406; 760UPDATE llx_c_departements SET code_departement='GR' WHERE ncc='GRANADA' AND fk_region=401; 761UPDATE llx_c_departements SET code_departement='GU' WHERE ncc='GUADALAJARA' AND fk_region=404; 762UPDATE llx_c_departements SET code_departement='SS' WHERE ncc='GUIPUZCOA' AND fk_region=419; 763UPDATE llx_c_departements SET code_departement='H' WHERE ncc='HUELVA' AND fk_region=401; 764UPDATE llx_c_departements SET code_departement='HU' WHERE ncc='HUESCA' AND fk_region=402; 765UPDATE llx_c_departements SET code_departement='J' WHERE ncc='JAEN' AND fk_region=401; 766UPDATE llx_c_departements SET code_departement='LE' WHERE ncc='LEON' AND fk_region=403; 767UPDATE llx_c_departements SET code_departement='L' WHERE ncc='LERIDA' AND fk_region=406; 768UPDATE llx_c_departements SET code_departement='LO' WHERE ncc='LA RIOJA' AND fk_region=415; 769UPDATE llx_c_departements SET code_departement='LU' WHERE ncc='LUGO' AND fk_region=413; 770UPDATE llx_c_departements SET code_departement='M' WHERE ncc='MADRID' AND fk_region=416; 771UPDATE llx_c_departements SET code_departement='MA' WHERE ncc='MALAGA' AND fk_region=401; 772UPDATE llx_c_departements SET code_departement='MU' WHERE ncc='MURCIA' AND fk_region=417; 773UPDATE llx_c_departements SET code_departement='NA' WHERE ncc='NAVARRA' AND fk_region=408; 774UPDATE llx_c_departements SET code_departement='OR' WHERE ncc='ORENSE' AND fk_region=413; 775UPDATE llx_c_departements SET code_departement='VI' WHERE ncc='ALAVA' AND fk_region=419; 776UPDATE llx_c_departements SET code_departement='O' WHERE ncc='ASTURIAS' AND fk_region=418; 777UPDATE llx_c_departements SET code_departement='P' WHERE ncc='PALENCIA' AND fk_region=403; 778UPDATE llx_c_departements SET code_departement='GC' WHERE ncc='LAS PALMAS' AND fk_region=405; 779UPDATE llx_c_departements SET code_departement='PO' WHERE ncc='PONTEVEDRA' AND fk_region=413; 780UPDATE llx_c_departements SET code_departement='SA' WHERE ncc='SALAMANCA' AND fk_region=403; 781UPDATE llx_c_departements SET code_departement='TF' WHERE ncc='STA. CRUZ DE TENERIFE' AND fk_region=405; 782UPDATE llx_c_departements SET code_departement='S' WHERE ncc='CANTABRIA' AND fk_region=410; 783UPDATE llx_c_departements SET code_departement='SG' WHERE ncc='SEGOVIA' AND fk_region=403; 784UPDATE llx_c_departements SET code_departement='SE' WHERE ncc='SEVILLA' AND fk_region=401; 785UPDATE llx_c_departements SET code_departement='SO' WHERE ncc='SORIA' AND fk_region=403; 786UPDATE llx_c_departements SET code_departement='T' WHERE ncc='TARRAGONA' AND fk_region=406; 787UPDATE llx_c_departements SET code_departement='TE' WHERE ncc='TERUEL' AND fk_region=402; 788UPDATE llx_c_departements SET code_departement='TO' WHERE ncc='TOLEDO' AND fk_region=404; 789UPDATE llx_c_departements SET code_departement='V' WHERE ncc='VALENCIA' AND fk_region=411; 790UPDATE llx_c_departements SET code_departement='VA' WHERE ncc='VALLADOLID' AND fk_region=403; 791UPDATE llx_c_departements SET code_departement='BI' WHERE ncc='VIZCAYA' AND fk_region=419; 792UPDATE llx_c_departements SET code_departement='ZA' WHERE ncc='ZAMORA' AND fk_region=403; 793UPDATE llx_c_departements SET code_departement='Z' WHERE ncc='ZARAGOZA' AND fk_region=402; 794UPDATE llx_c_departements SET code_departement='VI' WHERE ncc='ALAVA' AND fk_region=419; 795UPDATE llx_c_departements SET code_departement='CE' WHERE ncc='CEUTA' AND fk_region=407; 796UPDATE llx_c_departements SET code_departement='ML' WHERE ncc='MELILLA' AND fk_region=409; 797DELETE FROM llx_c_departements WHERE ncc='OTROS' AND fk_region=420; 798DELETE FROM llx_c_regions WHERE code_region=420 and fk_pays=4; 799 800ALTER TABLE llx_c_paiement MODIFY COLUMN libelle varchar(62); 801 802ALTER TABLE llx_societe_remise_except MODIFY COLUMN description text NOT NULL; 803 804-- Fix bad data 805update llx_opensurvey_sondage set format = 'D' where format = 'D+'; 806update llx_opensurvey_sondage set format = 'A' where format = 'A+'; 807 808 809--Deal with holidays_user that do not have rowid 810-- Disabled: too dangerous patch. rowid is a primary key. How is it possible to have no rowid ? 811--CREATE TABLE llx_holiday_users_tmp 812--( 813-- rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY, 814-- fk_user integer NOT NULL, 815-- fk_type integer NOT NULL, 816-- nb_holiday real NOT NULL DEFAULT '0' 817--) ENGINE=innodb; 818--INSERT INTO llx_holiday_users_tmp(fk_user,fk_type,nb_holiday) SELECT fk_user,fk_type,nb_holiday FROM llx_holiday_users; 819--DROP TABLE llx_holiday_users; 820--ALTER TABLE llx_holiday_users_tmp RENAME TO llx_holiday_users; 821 822