X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sql%2Falter2.3.sql;h=c5518404e25e9b185a8569c3abd12b58ea41619a;hb=40e5dda3847073d8f86c558eafbbc5372133bedd;hp=b52bfa475e01349debab4bbb3b97376607f12de3;hpb=4a4cafb0a0184fdcc96fcde391f01ce02dbf31b1;p=fa-stable.git diff --git a/sql/alter2.3.sql b/sql/alter2.3.sql index b52bfa47..c5518404 100644 --- a/sql/alter2.3.sql +++ b/sql/alter2.3.sql @@ -56,7 +56,7 @@ INSERT INTO `0_sys_prefs` SELECT 'gst_no','setup.company', 'varchar','25', c.gst INSERT INTO `0_sys_prefs` SELECT 'coy_no','setup.company', 'varchar','25', c.coy_no FROM `0_company` c; INSERT INTO `0_sys_prefs` SELECT 'tax_prd','setup.company', 'int','11', c.tax_prd FROM `0_company` c; INSERT INTO `0_sys_prefs` SELECT 'tax_last','setup.company', 'int','11', c.tax_last FROM `0_company` c; -INSERT INTO `0_sys_prefs` SELECT 'postal_address','setup.company', 'tinytext','', c.postal_address FROM `0_company` c; +INSERT INTO `0_sys_prefs` SELECT 'postal_address','setup.company', 'tinytext','0', c.postal_address FROM `0_company` c; INSERT INTO `0_sys_prefs` SELECT 'phone','setup.company', 'varchar','30', c.phone FROM `0_company` c; INSERT INTO `0_sys_prefs` SELECT 'fax','setup.company', 'varchar','30',c.fax FROM `0_company` c; INSERT INTO `0_sys_prefs` SELECT 'email','setup.company', 'varchar','100', c.email FROM `0_company` c; @@ -83,7 +83,7 @@ INSERT INTO `0_sys_prefs` SELECT 'exchange_diff_act','glsetup.general', 'varchar INSERT INTO `0_sys_prefs` SELECT 'default_credit_limit','glsetup.customer', 'int','11', c.default_credit_limit FROM `0_company` c; INSERT INTO `0_sys_prefs` SELECT 'accumulate_shipping','glsetup.customer', 'tinyint','1', c.accumulate_shipping FROM `0_company` c; -INSERT INTO `0_sys_prefs` SELECT 'legal_text','glsetup.customer', 'tinytext','', c.legal_text FROM `0_company` c; +INSERT INTO `0_sys_prefs` SELECT 'legal_text','glsetup.customer', 'tinytext','0', c.legal_text FROM `0_company` c; INSERT INTO `0_sys_prefs` SELECT 'freight_act','glsetup.customer', 'varchar','15', c.freight_act FROM `0_company` c; INSERT INTO `0_sys_prefs` SELECT 'debtors_act','glsetup.sales', 'varchar','15', c.debtors_act FROM `0_company` c; @@ -115,6 +115,8 @@ INSERT INTO `0_sys_prefs` SELECT 'version_id', 'system', 'varchar', '11', c.vers ALTER TABLE `0_stock_master` ADD COLUMN `editable` TINYINT(1) NOT NULL default '0'; ALTER TABLE `0_debtor_trans` ADD COLUMN `payment_terms` int(11) default NULL; ALTER TABLE `0_sales_orders` ADD COLUMN `payment_terms` int(11) default NULL; +ALTER TABLE `0_sales_orders` ADD COLUMN `total` double NOT NULL default '0'; +ALTER TABLE `0_purch_orders` ADD COLUMN `total` double NOT NULL default '0'; # change account, groups and classes id's ALTER TABLE `0_bank_accounts` CHANGE `account_code` `account_code` VARCHAR(15) NOT NULL DEFAULT ''; @@ -150,15 +152,100 @@ ALTER TABLE `0_chart_master` CHANGE `account_type` `account_type` VARCHAR(10) NO ALTER TABLE `0_chart_types` CHANGE `id` `id` VARCHAR(10) NOT NULL; ALTER TABLE `0_chart_types` CHANGE `parent` `parent` VARCHAR(10) NOT NULL DEFAULT '-1'; ALTER TABLE `0_chart_types` CHANGE `class_id` `class_id` VARCHAR(3) NOT NULL DEFAULT ''; -. -UPDATE `0_chart_types` SET parent='' WHERE parent='0' OR parent='-1'; - -ALTER TABLE `0_debtors_master` ADD COLUMN `rep_lang` char(5) default NULL; -UPDATE `0_debtors_master` set `rep_lang`= 'en_GB' WHERE `curr_code`<>(SELECT value FROM `0_sys_prefs` WHERE name='curr_default'); - -ALTER TABLE `0_cust_branch` ADD COLUMN `rep_lang` char(5) default NULL; -ALTER TABLE `0_suppliers` ADD COLUMN `rep_lang` char(5) default NULL; -UPDATE `0_suppliers` set `rep_lang`= 'en_GB' WHERE `curr_code`<>(SELECT value FROM `0_sys_prefs` WHERE name='curr_default'); +UPDATE `0_chart_types` SET parent='' WHERE parent='0' OR parent='-1'; INSERT INTO `0_sys_prefs` (name, category, type, length, value) VALUES ('auto_curr_reval','setup.company', 'smallint','6', '1'); + +DROP TABLE IF EXISTS `0_crm_categories`; +CREATE TABLE `0_crm_categories` ( + `id` int(11) NOT NULL auto_increment COMMENT 'pure technical key', + `type` varchar(20) NOT NULL COMMENT 'contact type e.g. customer' , + `action` varchar(20) NOT NULL COMMENT 'detailed usage e.g. department', + `name` varchar(30) NOT NULL COMMENT 'for category selector', + `description` tinytext NOT NULL COMMENT 'usage description', + `system` tinyint(1) NOT NULL default '0' COMMENT 'nonzero for core system usage', + `inactive` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`id`), + UNIQUE KEY(`type`, `action`), + UNIQUE KEY(`type`, `name`) +) TYPE=InnoDB ; + + +INSERT INTO `0_crm_categories` VALUES (1, 'cust_branch', 'general', 'General', 'General contact data for customer branch (overrides company setting)', 1, 0); +INSERT INTO `0_crm_categories` VALUES (2, 'cust_branch', 'invoice', 'Invoices', 'Invoice posting (overrides company setting)', 1, 0); +INSERT INTO `0_crm_categories` VALUES (3, 'cust_branch', 'order', 'Orders', 'Order confirmation (overrides company setting)', 1, 0); +INSERT INTO `0_crm_categories` VALUES (4, 'cust_branch', 'delivery', 'Deliveries', 'Delivery coordination (overrides company setting)', 1, 0); +INSERT INTO `0_crm_categories` VALUES (5, 'customer', 'general', 'General', 'General contact data for customer', 1, 0); +INSERT INTO `0_crm_categories` VALUES (6, 'customer', 'order', 'Orders', 'Order confirmation', 1, 0); +INSERT INTO `0_crm_categories` VALUES (7, 'customer', 'delivery', 'Deliveries', 'Delivery coordination', 1, 0); +INSERT INTO `0_crm_categories` VALUES (8, 'customer', 'invoice', 'Invoices', 'Invoice posting', 1, 0); +INSERT INTO `0_crm_categories` VALUES (9, 'supplier', 'general', 'General', 'General contact data for supplier', 1, 0); +INSERT INTO `0_crm_categories` VALUES (10,'supplier', 'order', 'Orders', 'Order confirmation', 1, 0); +INSERT INTO `0_crm_categories` VALUES (11,'supplier', 'delivery', 'Deliveries', 'Delivery coordination', 1, 0); +INSERT INTO `0_crm_categories` VALUES (12,'supplier', 'invoice', 'Invoices', 'Invoice posting', 1, 0); + +DROP TABLE IF EXISTS `0_crm_persons`; + +CREATE TABLE `0_crm_persons` ( + `id` int(11) NOT NULL auto_increment, + `ref` varchar(30) NOT NULL, + `name` varchar(60) NOT NULL, + `name2` varchar(60) default NULL, + `address` tinytext default NULL, + `phone` varchar(30) default NULL, + `phone2` varchar(30) default NULL, + `fax` varchar(30) default NULL, + `email` varchar(100) default NULL, + `lang` char(5) default NULL, + `notes` tinytext NOT NULL, + `tmp_id` varchar(11), + `tmp_class` varchar(20), + `inactive` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`id`), + KEY (`ref`) +) TYPE=InnoDB AUTO_INCREMENT=1 ; + +DROP TABLE IF EXISTS `0_crm_contacts`; + +CREATE TABLE `0_crm_contacts` ( + `id` int(11) NOT NULL auto_increment, + `person_id` int(11) NOT NULL default '0' COMMENT 'foreign key to crm_contacts', + `type` varchar(20) NOT NULL COMMENT 'foreign key to crm_categories', + `action` varchar(20) NOT NULL COMMENT 'foreign key to crm_categories', + `entity_id` varchar(11) NULL COMMENT 'entity id in related class table', + PRIMARY KEY (`id`), + KEY(`type`, `action`) +) TYPE=InnoDB ; + + +# +# tmp_id, tmp_class fields are used temporarily during upgrade to makethe process easier +# +INSERT INTO `0_crm_persons` (`ref`, `email`, `lang`, `tmp_id`, `tmp_class`) + SELECT `debtor_ref`, `email`, if(`curr_code`=d.`lang`, NULL, 'en_GB'), `debtor_no`, 'customer' + FROM `0_debtors_master`, + (SELECT `value` as lang FROM `0_sys_prefs` WHERE name='curr_default') d; + +INSERT INTO `0_crm_persons` (`ref`, `name`, `address`, `phone`, `phone2`, + `fax`,`email`, `tmp_id`,`tmp_class`) + SELECT `branch_ref`, `contact_name`, `br_address`, `phone`, `phone2`, + `fax`,`email`,`branch_code`, 'cust_branch' FROM `0_cust_branch`; + +INSERT INTO `0_crm_persons` (`ref`, `name`, `address`, `phone`, `phone2`, + `fax`,`email`,`lang`,`tmp_id`,`tmp_class`) + SELECT `supp_ref`, `contact`, `supp_address`, `phone`, `phone2`, + `fax`,`email`,if(`curr_code`=d.`lang`, NULL, 'en_GB'),`supplier_id`,'supplier' + FROM `0_suppliers`, + (SELECT `value` as lang FROM `0_sys_prefs` WHERE name='curr_default') d; + + +INSERT INTO `0_crm_contacts` (`person_id`, `type`, `action`, `entity_id`) + SELECT `id`, `tmp_class`, 'general', `tmp_id` + FROM `0_crm_persons`; + +ALTER TABLE `0_debtor_trans_details` ADD COLUMN `src_id` int(11) default NULL; +ALTER TABLE `0_debtor_trans_details` ADD KEY (`src_id`); +ALTER TABLE `0_suppliers` ADD COLUMN `tax_included` tinyint(1) NOT NULL default '0' AFTER `payment_terms`; +ALTER TABLE `0_supp_trans` ADD COLUMN `tax_included` tinyint(1) NOT NULL default '0'; +ALTER TABLE `0_purch_orders` ADD COLUMN `tax_included` tinyint(1) NOT NULL default '0';