dolibarr/htdocs/install/mysql/migration/3.9.0-4.0.0.sql

--
-- Be carefull to requests order.
-- This file must be loaded by calling /install/index.php page
-- when current version is 4.0.0 or higher.
--
-- To rename a table:       ALTER TABLE llx_table RENAME TO llx_table_new;
-- To add a column:         ALTER TABLE llx_table ADD COLUMN newcol varchar(60) NOT NULL DEFAULT '0' AFTER existingcol;
-- To rename a column:      ALTER TABLE llx_table CHANGE COLUMN oldname newname varchar(60);
-- To drop a column:        ALTER TABLE llx_table DROP COLUMN oldname;
-- To change type of field: ALTER TABLE llx_table MODIFY COLUMN name varchar(60);
-- To drop a foreign key:   ALTER TABLE llx_table DROP FOREIGN KEY fk_name;
-- To drop an index:        -- VMYSQL4.0 DROP INDEX nomindex on llx_table
-- To drop an index:        -- VPGSQL8.0 DROP INDEX nomindex
-- To restrict request to Mysql version x.y minimum use -- VMYSQLx.y
-- To restrict request to Pgsql version x.y minimum use -- VPGSQLx.y
-- 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;
-- To make pk to be auto increment (postgres): VPGSQL8.2 NOT POSSIBLE. MUST DELETE/CREATE TABLE
-- To set a field as NULL:                     VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name DROP NOT NULL;
-- To set a field as default NULL:             VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name SET DEFAULT NULL;
-- Note: fields with type BLOB/TEXT can't have default value.
-- -- VPGSQL8.2 DELETE FROM llx_usergroup_user      WHERE fk_user      NOT IN (SELECT rowid from llx_user);
-- -- VMYSQL4.1 DELETE FROM llx_usergroup_user      WHERE fk_usergroup NOT IN (SELECT rowid from llx_usergroup);

-- Drop old table not used (Informations are already presents in llx_accounting_bookkeeping)
DROP TABLE llx_accountingtransaction;
DROP TABLE llx_accountingdebcred;

-- Already into 3.9 but we do it again to be sure
ALTER TABLE llx_product ADD COLUMN localtax1_type varchar(10)  NOT NULL DEFAULT '0' after localtax1_tx; 
ALTER TABLE llx_product ADD COLUMN localtax2_type varchar(10)  NOT NULL DEFAULT '0' after localtax2_tx; 
ALTER TABLE llx_product_price ADD COLUMN localtax1_type varchar(10)  NOT NULL DEFAULT '0' after localtax1_tx; 
ALTER TABLE llx_product_price ADD COLUMN localtax2_type varchar(10)  NOT NULL DEFAULT '0' after localtax2_tx; 
ALTER TABLE llx_product_customer_price ADD COLUMN localtax1_type varchar(10)  NOT NULL DEFAULT '0' after localtax1_tx; 
ALTER TABLE llx_product_customer_price ADD COLUMN localtax2_type varchar(10)  NOT NULL DEFAULT '0' after localtax2_tx; 
ALTER TABLE llx_product_customer_price_log ADD COLUMN localtax1_type varchar(10)  NOT NULL DEFAULT '0' after localtax1_tx; 
ALTER TABLE llx_product_customer_price_log ADD COLUMN localtax2_type varchar(10)  NOT NULL DEFAULT '0' after localtax2_tx; 

ALTER TABLE llx_opensurvey_sondage ADD COLUMN status integer DEFAULT 1 after date_fin;

ALTER TABLE llx_expedition ADD COLUMN billed smallint DEFAULT 0;

insert into llx_c_type_contact(rowid, element, source, code, libelle, active ) values (150, 'dolresource','internal', 'USERINCHARGE',     'In charge of resource', 1);
insert into llx_c_type_contact(rowid, element, source, code, libelle, active ) values (151, 'dolresource','external', 'THIRDINCHARGE',    'In charge of resource', 1);

-- DROP TABLE llx_product_lot;
CREATE TABLE llx_product_lot (
  rowid           integer AUTO_INCREMENT PRIMARY KEY,
  entity          integer,
  fk_product      integer NOT NULL,				-- Id of product
  batch           varchar(30) DEFAULT NULL,		-- Lot or serial number
  eatby           date DEFAULT NULL,			-- Eatby date
  sellby          date DEFAULT NULL, 			-- Sellby date
  datec         datetime,
  tms           timestamp,
  fk_user_creat integer,
  fk_user_modif integer,
  import_key    integer
) ENGINE=InnoDB;

ALTER TABLE llx_product_lot ADD UNIQUE INDEX uk_product_lot(fk_product, batch);

DROP TABLE llx_stock_serial; 

ALTER TABLE llx_product ADD COLUMN note_public text;
ALTER TABLE llx_user ADD COLUMN note_public text;

ALTER TABLE llx_c_type_contact ADD COLUMN position integer NOT NULL DEFAULT 0;


ALTER TABLE llx_product ADD COLUMN  model_pdf	varchar(255) default '';

ALTER TABLE llx_product ADD COLUMN width		float        DEFAULT NULL;
ALTER TABLE llx_product ADD COLUMN width_units	tinyint      DEFAULT NULL;
ALTER TABLE llx_product ADD COLUMN height		float        DEFAULT NULL;
ALTER TABLE llx_product ADD COLUMN height_units tinyint      DEFAULT NULL;

ALTER TABLE llx_product ADD COLUMN default_vat_code	varchar(10) after cost_price;

ALTER TABLE llx_product MODIFY COLUMN stock	real;

CREATE TABLE llx_categorie_user 
(
  fk_categorie 	integer NOT NULL,
  fk_user 		integer NOT NULL,
  import_key 	varchar(14)
) ENGINE=innodb;

ALTER TABLE llx_categorie_user ADD PRIMARY KEY pk_categorie_user (fk_categorie, fk_user);
ALTER TABLE llx_categorie_user ADD INDEX idx_categorie_user_fk_categorie (fk_categorie);
ALTER TABLE llx_categorie_user ADD INDEX idx_categorie_user_fk_user (fk_user);

ALTER TABLE llx_categorie_user ADD CONSTRAINT fk_categorie_user_categorie_rowid FOREIGN KEY (fk_categorie) REFERENCES llx_categorie (rowid);
ALTER TABLE llx_categorie_user ADD CONSTRAINT fk_categorie_user_fk_user FOREIGN KEY (fk_user) REFERENCES llx_user (rowid);



ALTER TABLE llx_accounting_bookkeeping ADD COLUMN validated tinyint DEFAULT 0 NOT NULL;
ALTER TABLE llx_bank_account MODIFY COLUMN accountancy_journal varchar(16) DEFAULT NULL;

ALTER TABLE llx_fichinter ADD COLUMN datet date  after duree;
ALTER TABLE llx_fichinter ADD COLUMN datee date  after duree;
ALTER TABLE llx_fichinter ADD COLUMN dateo date  after duree;

ALTER TABLE llx_projet ADD COLUMN opp_percent double(5,2) after fk_opp_status;
UPDATE llx_projet as p set opp_percent = (SELECT percent from llx_c_lead_status as cls where cls.rowid = p.fk_opp_status) where opp_percent IS NULL;

ALTER TABLE llx_overwrite_trans ADD UNIQUE INDEX uk_overwrite_trans(lang, transkey);

ALTER TABLE llx_cronjob MODIFY COLUMN unitfrequency	varchar(255) NOT NULL DEFAULT '3600';
ALTER TABLE llx_cronjob ADD COLUMN test varchar(255) DEFAULT '1';

ALTER TABLE llx_facture ADD INDEX idx_facture_fk_statut (fk_statut);

ALTER TABLE llx_facture ADD COLUMN date_pointoftax date DEFAULT NULL;

UPDATE llx_projet as p set p.opp_percent = (SELECT percent FROM llx_c_lead_status as cls WHERE cls.rowid = p.fk_opp_status)  WHERE p.opp_percent IS NULL AND p.fk_opp_status IS NOT NULL;
 
ALTER TABLE llx_facturedet ADD COLUMN fk_contract_line  integer NULL AFTER rang;
ALTER TABLE llx_facturedet_rec ADD COLUMN import_key varchar(14);

ALTER TABLE llx_chargesociales ADD COLUMN import_key varchar(14);
ALTER TABLE llx_tva ADD COLUMN import_key varchar(14);

--DROP TABLE llx_website_page;
--DROP TABLE llx_website;
CREATE TABLE llx_website
(
	rowid         integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
	entity        integer DEFAULT 1,
	ref		      varchar(24) NOT NULL,
	description   varchar(255),
	status		  integer,
	fk_default_home integer,
    date_creation     datetime,
    date_modification datetime,
	tms           timestamp
) ENGINE=innodb;
ALTER TABLE llx_website ADD COLUMN fk_default_home integer;
ALTER TABLE llx_website ADD UNIQUE INDEX uk_website_ref (ref, entity);

CREATE TABLE llx_website_page
(
	rowid         integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
	fk_website    integer,
	pageurl       varchar(16) NOT NULL,
	title         varchar(255),						
	description   varchar(255),						
	keywords      varchar(255),
	content		  text,
    status        integer,
    date_creation     datetime,
    date_modification datetime,
	tms           timestamp
) ENGINE=innodb;

ALTER TABLE llx_website_page ADD UNIQUE INDEX uk_website_page_url (fk_website,pageurl);

ALTER TABLE llx_website_page ADD CONSTRAINT fk_website_page_website FOREIGN KEY (fk_website) REFERENCES llx_website (rowid);

CREATE TABLE llx_c_format_cards
(
  rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code varchar(50) NOT NULL,
  name varchar(50) NOT NULL,
  paper_size varchar(20) NOT NULL,
  orientation varchar(1) NOT NULL,
  metric varchar(5) NOT NULL,
  leftmargin double(24,8) NOT NULL,
  topmargin double(24,8) NOT NULL,
  nx integer NOT NULL,
  ny integer NOT NULL,
  spacex double(24,8) NOT NULL,
  spacey double(24,8) NOT NULL,
  width double(24,8) NOT NULL,
  height double(24,8) NOT NULL,
  font_size integer NOT NULL,
  custom_x double(24,8) NOT NULL,
  custom_y double(24,8) NOT NULL,
  active integer NOT NULL
) ENGINE=InnoDB;

INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (1, '5160', 'Avery-5160, WL-875WX', 'letter', 'P', 'mm', 5.58165000, 12.70000000, 3, 10, 3.55600000, 0.00000000, 65.87490000, 25.40000000, 7, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (2, '5161', 'Avery-5161, WL-75WX', 'letter', 'P', 'mm', 4.44500000, 12.70000000, 2, 10, 3.96800000, 0.00000000, 101.60000000, 25.40000000, 7, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (3, '5162', 'Avery-5162, WL-100WX', 'letter', 'P', 'mm', 3.87350000, 22.35200000, 2, 7, 4.95400000, 0.00000000, 101.60000000, 33.78100000, 8, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (4, '5163', 'Avery-5163, WL-125WX', 'letter', 'P', 'mm', 4.57200000, 12.70000000, 2, 5, 3.55600000, 0.00000000, 101.60000000, 50.80000000, 10, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (5, '5164', '5164 (Letter)', 'letter', 'P', 'in', 0.14800000, 0.50000000, 2, 3, 0.20310000, 0.00000000, 4.00000000, 3.33000000, 12, 0.00000000, 0.00000000, 0);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (6, '8600', 'Avery-8600', 'letter', 'P', 'mm', 7.10000000, 19.00000000, 3, 10, 9.50000000, 3.10000000, 66.60000000, 25.40000000, 7, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (7, '99012', 'DYMO 99012 89*36mm', 'custom', 'L', 'mm', 1.00000000, 1.00000000, 1, 1, 0.00000000, 0.00000000, 36.00000000, 89.00000000, 10, 36.00000000, 89.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (8, '99014', 'DYMO 99014 101*54mm', 'custom', 'L', 'mm', 1.00000000, 1.00000000, 1, 1, 0.00000000, 0.00000000, 54.00000000, 101.00000000, 10, 54.00000000, 101.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (9, 'AVERYC32010', 'Avery-C32010', 'A4', 'P', 'mm', 15.00000000, 13.00000000, 2, 5, 10.00000000, 0.00000000, 85.00000000, 54.00000000, 10, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (10, 'CARD', 'Dolibarr Business cards', 'A4', 'P', 'mm', 15.00000000, 15.00000000, 2, 5, 0.00000000, 0.00000000, 85.00000000, 54.00000000, 10, 0.00000000, 0.00000000, 1);
INSERT INTO llx_c_format_cards (rowid, code, name, paper_size, orientation, metric, leftmargin, topmargin, nx, ny, spacex, spacey, width, height, font_size, custom_x, custom_y, active) VALUES (11, 'L7163', 'Avery-L7163', 'A4', 'P', 'mm', 5.00000000, 15.00000000, 2, 7, 2.50000000, 0.00000000, 99.10000000, 38.10000000, 8, 0.00000000, 0.00000000, 1);

ALTER TABLE llx_extrafields ADD COLUMN ishidden integer DEFAULT 0;


ALTER TABLE llx_paiementfourn ADD COLUMN ref varchar(30) AFTER rowid;
ALTER TABLE llx_paiementfourn ADD COLUMN entity integer AFTER ref;


CREATE TABLE llx_multicurrency 
( 
	rowid integer AUTO_INCREMENT PRIMARY KEY, 
	date_create datetime DEFAULT NULL, 
	code varchar(255) DEFAULT NULL, 
	name varchar(255) DEFAULT NULL, 
	entity integer DEFAULT 1,
	fk_user integer DEFAULT NULL
) ENGINE=innodb;

CREATE TABLE llx_multicurrency_rate 
( 
	rowid integer AUTO_INCREMENT PRIMARY KEY, 
	date_sync datetime DEFAULT NULL,  
	rate double NOT NULL DEFAULT 0, 
	fk_multicurrency integer NOT NULL 
) ENGINE=innodb;

ALTER TABLE llx_societe ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_societe ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_societe ADD COLUMN fk_shipping_method integer AFTER cond_reglement_supplier;

ALTER TABLE llx_product_price ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_product_price ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_product_price ADD COLUMN multicurrency_price double(24,8) DEFAULT 0;

ALTER TABLE llx_commande ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_commande ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_commande ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_commande ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_commande ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_commande ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_commandedet ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_commandedet ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_commandedet ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_commandedet ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_commandedet ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_commandedet ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_commande_fournisseur ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_commande_fournisseur ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_commande_fournisseur ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_commande_fournisseur ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_commande_fournisseur ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_commande_fournisseur ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_commande_fournisseurdet ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_commande_fournisseurdet ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_commande_fournisseurdet ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_commande_fournisseurdet ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_commande_fournisseurdet ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_commande_fournisseurdet ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_facture_fourn ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_facture_fourn ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_facture_fourn ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_facture_fourn ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_fourn ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_fourn ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_facture_fourn_det ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_facture_fourn_det ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_facture_fourn_det ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_fourn_det ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_fourn_det ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_fourn_det ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_facture ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_facture ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_facture ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_facture ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_facture ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_facture ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_facturedet ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_facturedet ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_facturedet ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_propal ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_propal ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_propal ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_propal ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_propal ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_propal ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_propaldet ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_propaldet ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_propaldet ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_propaldet ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_propaldet ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_propaldet ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

 
-- Add for recurring template invoices

ALTER TABLE llx_facture_rec ADD COLUMN auto_validate integer DEFAULT 0;
ALTER TABLE llx_facture_rec ADD COLUMN fk_account integer DEFAULT 0;

ALTER TABLE llx_facture_rec ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_facture_rec ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_facture_rec ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_facture_rec ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_rec ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_facture_rec ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_facturedet_rec ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_facturedet_rec ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_facturedet_rec ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet_rec ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet_rec ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_facturedet_rec ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_contratdet ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_contratdet ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_contratdet ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_contratdet ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_contratdet ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_contratdet ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_paiement ADD COLUMN multicurrency_amount double(24,8) DEFAULT 0;
ALTER TABLE llx_paiement_facture ADD COLUMN multicurrency_amount double(24,8) DEFAULT 0;
ALTER TABLE llx_paiementfourn ADD COLUMN multicurrency_amount double(24,8) DEFAULT 0;
ALTER TABLE llx_paiementfourn_facturefourn ADD COLUMN multicurrency_amount double(24,8) DEFAULT 0;

ALTER TABLE llx_societe_remise_except ADD COLUMN multicurrency_amount_ht double(24,8) DEFAULT 0 NOT NULL;
ALTER TABLE llx_societe_remise_except ADD COLUMN multicurrency_amount_tva double(24,8) DEFAULT 0 NOT NULL;
ALTER TABLE llx_societe_remise_except ADD COLUMN multicurrency_amount_ttc double(24,8) DEFAULT 0 NOT NULL;

ALTER TABLE llx_supplier_proposal ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_supplier_proposal ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_supplier_proposal ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_supplier_proposal ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_supplier_proposal ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_supplier_proposal ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_supplier_proposaldet ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_supplier_proposaldet ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_supplier_proposaldet ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_supplier_proposaldet ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_supplier_proposaldet ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_supplier_proposaldet ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_expensereport ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_expensereport ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_expensereport ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_expensereport ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_expensereport_det ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_expensereport_det ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_expensereport_det ADD COLUMN multicurrency_subprice double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport_det ADD COLUMN multicurrency_total_ht double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport_det ADD COLUMN multicurrency_total_tva double(24,8) DEFAULT 0;
ALTER TABLE llx_expensereport_det ADD COLUMN multicurrency_total_ttc double(24,8) DEFAULT 0;

ALTER TABLE llx_expensereport_det ADD COLUMN fk_facture	integer DEFAULT 0;

ALTER TABLE llx_product_lang ADD COLUMN import_key varchar(14) DEFAULT NULL;

ALTER TABLE llx_actioncomm MODIFY COLUMN elementtype varchar(255) DEFAULT NULL;

DELETE FROM llx_menu where module='expensereport';

ALTER TABLE llx_accounting_system DROP COLUMN fk_pays;
ALTER TABLE llx_accounting_account ADD COLUMN fk_accounting_category integer DEFAULT 0 after label;

CREATE TABLE llx_c_accounting_category (
  rowid 			integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code 				varchar(16) NOT NULL,
  label 			varchar(255) NOT NULL,
  range_account		varchar(255) NOT NULL,
  sens 				tinyint NOT NULL DEFAULT '0', -- For international accounting  0 : credit - debit / 1 : debit - credit
  category_type		tinyint NOT NULL DEFAULT '0', -- Field calculated or not
  formula			varchar(255) NOT NULL,			 -- Example : 1 + 2 (rowid of the category)
  position    		integer DEFAULT 0,
  fk_country 		integer DEFAULT NULL,			 -- This category is dedicated to a country
  active 			integer DEFAULT 1
) ENGINE=innodb;

ALTER TABLE llx_c_accounting_category ADD UNIQUE INDEX uk_c_accounting_category(code);

INSERT INTO llx_c_accounting_category (rowid, code, label, range_account, sens, category_type, formula, position, fk_country, active) VALUES (  1,'VTE',"Ventes de marchandises", '707xxx', 0, 0, '', '10', 1, 1);
INSERT INTO llx_c_accounting_category (rowid, code, label, range_account, sens, category_type, formula, position, fk_country, active) VALUES (  2,'MAR',"Coût d'achats marchandises vendues", '603xxx | 607xxx | 609xxx', 0, 0, '', '20', 1, 1);
INSERT INTO llx_c_accounting_category (rowid, code, label, range_account, sens, category_type, formula, position, fk_country, active) VALUES (  3,'MARGE',"Marge commerciale", '', 0, 1, '1 + 2', '30', 1, 1);

UPDATE llx_accounting_account SET account_parent = '0' WHERE account_parent = '';
-- VMYSQL4.1 ALTER TABLE llx_accounting_account MODIFY COLUMN account_parent integer DEFAULT 0;
-- VPGSQL8.2 ALTER TABLE llx_accounting_account ALTER COLUMN account_parent TYPE integer USING account_parent::integer;

CREATE TABLE llx_accounting_journal
(
  rowid             integer AUTO_INCREMENT PRIMARY KEY,
  code       		varchar(32) NOT NULL,
  label             varchar(128) NOT NULL,
  nature			smallint DEFAULT 0 NOT NULL,			-- type of journals (Sale / purchase / bank / various operations)
  active            smallint DEFAULT 0
)ENGINE=innodb;

ALTER TABLE llx_accounting_journal ADD UNIQUE INDEX uk_accounting_journal_code (code);

-- VMYSQL4.1 DROP INDEX uk_bordereau_cheque ON llx_bordereau_cheque;
-- VPGSQL8.2 DROP INDEX uk_bordereau_cheque;
ALTER TABLE llx_bordereau_cheque CHANGE COLUMN number ref VARCHAR(30) NOT NULL;
CREATE UNIQUE INDEX uk_bordereau_cheque ON llx_bordereau_cheque (ref, entity);


ALTER TABLE llx_societe_rib ADD COLUMN date_rum	date after rum;

-- Add more action to log
update llx_c_action_trigger set rang = 140 where code = 'PROJECT_CREATE';
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROJECT_MODIFY','Project modified','Executed when a project is modified','project',141);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROJECT_DELETE','Project deleted','Executed when a project is deleted','project',142);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SUPPLIER_CREATE','Supplier order validated','Executed when a supplier order is validated','order_supplier',11);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SUPPLIER_SUBMIT','Supplier order request submited','Executed when a supplier order is approved','order_supplier',12);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SUPPLIER_RECEIVE','Supplier order request received','Executed when a supplier order is received','order_supplier',12);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('ORDER_SUPPLIER_CLASSIFY_BILLED','Supplier order set billed','Executed when a supplier order is set as billed','order_supplier',14);

ALTER TABLE llx_product_fournisseur_price ADD supplier_reputation varchar(10) NULL;

ALTER TABLE llx_product ADD COLUMN default_vat_code varchar(10) after cost_price;

CREATE TABLE llx_categorie_account
(
  fk_categorie  integer NOT NULL,
  fk_account    integer NOT NULL,
  import_key    varchar(14)
) ENGINE=innodb;

ALTER TABLE llx_categorie_account ADD PRIMARY KEY pk_categorie_account (fk_categorie, fk_account);
ALTER TABLE llx_categorie_account ADD INDEX idx_categorie_account_fk_categorie (fk_categorie);
ALTER TABLE llx_categorie_account ADD INDEX idx_categorie_account_fk_account (fk_account);

ALTER TABLE llx_categorie_account ADD CONSTRAINT fk_categorie_account_categorie_rowid FOREIGN KEY (fk_categorie) REFERENCES llx_categorie (rowid);
ALTER TABLE llx_categorie_account ADD CONSTRAINT fk_categorie_account_fk_account FOREIGN KEY (fk_account) REFERENCES llx_bank_account (rowid);

-- Delete old deprecated field
ALTER TABLE llx_product_stock DROP COLUMN pmp;

ALTER TABLE llx_resource ADD COLUMN asset_number    varchar(255) after ref;
ALTER TABLE llx_resource ADD COLUMN datec           datetime DEFAULT NULL;
ALTER TABLE llx_resource ADD COLUMN date_valid      datetime DEFAULT NULL;
ALTER TABLE llx_resource ADD COLUMN fk_user_author  integer DEFAULT NULL;
ALTER TABLE llx_resource ADD COLUMN fk_user_modif   integer DEFAULT NULL;
ALTER TABLE llx_resource ADD COLUMN fk_user_valid   integer DEFAULT NULL;
ALTER TABLE llx_resource ADD COLUMN fk_statut       smallint NOT NULL DEFAULT '0';
ALTER TABLE llx_resource ADD COLUMN import_key			varchar(14);
ALTER TABLE llx_resource ADD COLUMN extraparams			varchar(255);	
 
ALTER TABLE llx_element_resources ADD COLUMN duree real;          -- total duration of using ressource

UPDATE llx_element_resources SET resource_type = 'dolresource' WHERE resource_type = 'resource';

CREATE TABLE llx_advtargetemailing
(
  rowid integer NOT NULL auto_increment PRIMARY KEY,
  name varchar(200) NOT NULL,
  entity integer NOT NULL DEFAULT 1,
  fk_mailing	integer NOT NULL,
  filtervalue	text,
  fk_user_author integer NOT NULL,
  datec datetime NOT NULL,
  fk_user_mod integer NOT NULL,
  tms timestamp NOT NULL
)ENGINE=InnoDB;

ALTER TABLE llx_advtargetemailing ADD UNIQUE INDEX uk_advtargetemailing_name (name);


update llx_product_batch set batch = '000000' where batch = 'Non défini';
update llx_product_batch set batch = '000000' where batch = 'Non défini';
update llx_product_batch set batch = '000000' where batch = 'Undefined';

update llx_product_batch set batch = '000000' where batch = '';
update llx_product_batch set batch = '000000' where batch = '';
update llx_product_batch set batch = '000000' where batch = '';

update llx_product_lot set batch = '000000' where batch = 'Undefined';
update llx_stock_mouvement set batch = '000000' where batch = 'Undefined';


-- At end (higher risk of error)

-- VMYSQL4.1 ALTER TABLE llx_c_type_resource CHANGE COLUMN rowid rowid integer NOT NULL AUTO_INCREMENT;

ALTER TABLE llx_product_batch ADD UNIQUE INDEX uk_product_batch (fk_product_stock, batch);

CREATE TABLE llx_oauth_token (
    rowid integer AUTO_INCREMENT PRIMARY KEY,
    service varchar(36),
    token text,
    fk_user integer,
    fk_adherent integer,
    entity integer
)ENGINE=InnoDB;

CREATE TABLE llx_oauth_state (
    rowid integer AUTO_INCREMENT PRIMARY KEY,
    service varchar(36),
    state varchar(128),
    fk_user integer,
    fk_adherent integer,
    entity integer
)ENGINE=InnoDB;

ALTER TABLE llx_import_model MODIFY COLUMN type varchar(50);