sql/alter2.4.sql

   1 ALTER TABLE `0_suppliers` ADD COLUMN  `tax_algorithm` tinyint(1) NOT NULL default '1' AFTER `tax_included`;
   2 ALTER TABLE `0_supp_trans` ADD COLUMN `tax_algorithm` tinyint(1) NULL default '1' AFTER `tax_included`;
   3 INSERT INTO `0_sys_prefs` VALUES('tax_algorithm','glsetup.customer', 'tinyint', 1, '1');
   4 INSERT INTO `0_sys_prefs` VALUES('gl_closing_date','setup.closing_date', 'date', 8, '');
   5 ALTER TABLE `0_audit_trail` CHANGE `fiscal_year` `fiscal_year` int(11) NOT NULL default 0;
   6
   7 # Fix eventual invalid date/year in audit records
   8 UPDATE `0_audit_trail` audit
   9                 LEFT JOIN `0_gl_trans` gl ON  gl.`type`=audit.`type` AND gl.type_no=audit.trans_no
  10                 LEFT JOIN `0_fiscal_year` year ON year.begin<=gl.tran_date AND year.end>=gl.tran_date
  11                 SET audit.gl_date=gl.tran_date, audit.fiscal_year=year.id
  12                 WHERE NOT ISNULL(gl.`type`);
  13
  14 DROP TABLE IF EXISTS `0_wo_costing`;
  15
  16 CREATE TABLE `0_wo_costing` (
  17   `id` int(11) NOT NULL auto_increment,
  18   `workorder_id` int(11) NOT NULL default '0',
  19   `cost_type`   tinyint(1) NOT NULL default '0',
  20   `trans_type` int(11) NOT NULL default '0',
  21   `trans_no` int(11) NOT NULL default '0',
  22   `factor` double NOT NULL default '1',
  23   PRIMARY KEY  (`id`)
  24 ) ENGINE=InnoDB;
  25
  26 UPDATE `0_gl_trans` gl
  27                 LEFT JOIN `0_cust_branch` br ON br.receivables_account=gl.account AND br.debtor_no=gl.person_id AND gl.person_type_id=2
  28                 LEFT JOIN `0_suppliers` sup ON sup.payable_account=gl.account AND sup.supplier_id=gl.person_id AND gl.person_type_id=3
  29  SET `person_id` = IF(br.receivables_account, br.debtor_no, IF(sup.payable_account, sup.supplier_id, NULL)),
  30         `person_type_id` = IF(br.receivables_account, 2, IF(sup.payable_account, 3, NULL));
  31
  32 ALTER TABLE `0_tax_group_items` ADD COLUMN `tax_shipping` tinyint(1) NOT NULL default '0' AFTER `rate`;
  33 UPDATE `0_tax_group_items` tgi
  34         SET tgi.tax_shipping=1
  35         WHERE tgi.rate=(SELECT 0_tax_types.rate FROM 0_tax_types, 0_tax_groups
  36                 WHERE tax_shipping=1 AND tgi.tax_group_id=0_tax_groups.id AND tgi.tax_type_id=0_tax_types.id);
  37 ALTER TABLE `0_tax_groups` DROP COLUMN `tax_shipping`;
  38
  39 ALTER TABLE `0_sales_order_details` ADD KEY `stkcode` (`stk_code`);
  40 ALTER TABLE `0_purch_order_details` ADD KEY `itemcode` (`item_code`);
  41 ALTER TABLE `0_sys_prefs` CHANGE `value` `value` TEXT NOT NULL;
  42 ALTER TABLE `0_cust_branch` ADD COLUMN `bank_account` varchar(60) DEFAULT NULL AFTER `notes`;
  43
  44 ALTER TABLE `0_debtor_trans` ADD COLUMN `tax_included` tinyint(1) unsigned NOT NULL default '0' AFTER `payment_terms`;
  45 UPDATE `0_debtor_trans` tr, `0_trans_tax_details` td SET tr.tax_included=td.included_in_price
  46         WHERE tr.`type`=td.trans_type AND tr.trans_no=td.trans_no AND td.included_in_price;
  47
  48 ALTER TABLE `0_bank_accounts` ADD COLUMN `bank_charge_act` varchar(15) NOT NULL DEFAULT '' AFTER `id`;
  49 UPDATE `0_bank_accounts` SET `bank_charge_act`=(SELECT `value` FROM 0_sys_prefs WHERE name='bank_charge_act');
  50
  51 ALTER TABLE `0_users` ADD `transaction_days` INT( 6 ) NOT NULL default '30' COMMENT 'Transaction days' AFTER `startup_tab`;
  52
  53 ALTER TABLE `0_purch_orders` ADD COLUMN `prep_amount` double NOT NULL DEFAULT 0 AFTER `total`;
  54 ALTER TABLE `0_purch_orders` ADD COLUMN `alloc` double NOT NULL DEFAULT 0 AFTER `prep_amount`;
  55
  56 ALTER TABLE `0_sales_orders` ADD COLUMN `prep_amount` double NOT NULL DEFAULT 0 AFTER `total`;
  57 ALTER TABLE `0_sales_orders` ADD COLUMN `alloc` double NOT NULL DEFAULT 0 AFTER `prep_amount`;
  58
  59 ALTER TABLE `0_cust_allocations` ADD  UNIQUE KEY(`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`);
  60 ALTER TABLE `0_supp_allocations` ADD  UNIQUE KEY(`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`);
  61
  62 ALTER TABLE `0_sales_order_details` ADD COLUMN `invoiced` double NOT NULL DEFAULT 0 AFTER `quantity`;
  63
  64 # update sales_order_details.invoiced with sum of invoiced quantities on all related SI
  65 UPDATE `0_sales_order_details` so
  66         LEFT JOIN `0_debtor_trans_details` delivery ON delivery.`debtor_trans_type`=13 AND src_id=so.id
  67         LEFT JOIN (SELECT src_id, sum(quantity) as qty FROM `0_debtor_trans_details` WHERE `debtor_trans_type`=10 GROUP BY src_id) inv
  68                 ON inv.src_id=delivery.id
  69         SET `invoiced` = `invoiced`+inv.qty;
  70
  71 ALTER TABLE `0_debtor_trans` ADD COLUMN `prep_amount` double NOT NULL DEFAULT 0 AFTER `alloc`;
  72
  73 INSERT INTO `0_sys_prefs` VALUES ('deferred_income_act', 'glsetup.sales', 'varchar', '15', '');
  74
  75 # set others transactions edition for all roles for backward  compatibility
  76 UPDATE `0_security_roles` SET `sections`=CONCAT_WS(';', `sections`, '768'), `areas`='775'
  77         WHERE NOT `sections` REGEXP '[^0-9]?768[^0-9]?';
  78
  79 UPDATE `0_security_roles` SET `areas`=CONCAT_WS(';', `areas`, '775')
  80         WHERE NOT `areas` REGEXP '[^0-9]?775[^0-9]?';
  81
  82 ALTER TABLE `0_stock_master` ADD COLUMN `no_purchase` tinyint(1) NOT NULL default '0' AFTER `no_sale`;
  83 ALTER TABLE `0_stock_category` ADD COLUMN `dflt_no_purchase` tinyint(1) NOT NULL default '0' AFTER `dflt_no_sale`;
  84
  85 # added exchange rate field in grn_batch
  86 ALTER TABLE `0_grn_batch` ADD COLUMN `rate` double NULL default '1' AFTER `loc_code`;
  87 ALTER TABLE `0_users` CHANGE `query_size` `query_size` TINYINT(1) UNSIGNED NOT NULL DEFAULT 10;
  88
  89 ALTER TABLE `0_users` ADD `save_report_selections` SMALLINT( 6 ) NOT NULL default '0' COMMENT 'Save Report Selection Days' AFTER `transaction_days`;
  90 ALTER TABLE `0_users` ADD `use_date_picker` TINYINT(1) NOT NULL default '1' COMMENT 'Use Date Picker for all Date Values' AFTER `save_report_selections`;
  91 ALTER TABLE `0_users` ADD `def_print_destination` TINYINT(1) NOT NULL default '0' COMMENT 'Default Report Destination' AFTER `use_date_picker`;
  92 ALTER TABLE `0_users` ADD `def_print_orientation` TINYINT(1) NOT NULL default '0' COMMENT 'Default Report Orientation' AFTER `def_print_destination`;
  93
  94 INSERT INTO `0_sys_prefs` VALUES('no_zero_lines_amount', 'glsetup.sales', 'tinyint', 1, '1');
  95 INSERT INTO `0_sys_prefs` VALUES('show_po_item_codes', 'glsetup.purchase', 'tinyint', 1, '0');
  96 INSERT INTO `0_sys_prefs` VALUES('accounts_alpha', 'glsetup.general', 'tinyint', 1, '0');
  97 INSERT INTO `0_sys_prefs` VALUES('loc_notification', 'glsetup.inventory', 'tinyint', 1, '0');
  98 INSERT INTO `0_sys_prefs` VALUES('print_invoice_no', 'glsetup.sales', 'tinyint', 1, '0');
  99 INSERT INTO `0_sys_prefs` VALUES('allow_negative_prices', 'glsetup.inventory', 'tinyint', 1, '1');
 100 INSERT INTO `0_sys_prefs` VALUES('print_item_images_on_quote', 'glsetup.inventory', 'tinyint', 1, '0');
 101 INSERT INTO `0_sys_prefs` VALUES('default_receival_required', 'glsetup.purchase', 'smallint', 6, '10');
 102
 103 # switching all MyISAM tables to InnoDB
 104 ALTER TABLE `0_areas` ENGINE=InnoDB;
 105 ALTER TABLE `0_attachments` ENGINE=InnoDB;
 106 ALTER TABLE `0_bank_accounts` ENGINE=InnoDB;
 107 ALTER TABLE `0_bom` ENGINE=InnoDB;
 108 ALTER TABLE `0_chart_class` ENGINE=InnoDB;
 109 ALTER TABLE `0_chart_master` ENGINE=InnoDB;
 110 ALTER TABLE `0_chart_types` ENGINE=InnoDB;
 111 ALTER TABLE `0_credit_status` ENGINE=InnoDB;
 112 ALTER TABLE `0_currencies` ENGINE=InnoDB;
 113 ALTER TABLE `0_cust_branch` ENGINE=InnoDB;
 114 ALTER TABLE `0_debtors_master` ENGINE=InnoDB;
 115 ALTER TABLE `0_exchange_rates` ENGINE=InnoDB;
 116 ALTER TABLE `0_groups` ENGINE=InnoDB;
 117 ALTER TABLE `0_item_codes` ENGINE=InnoDB;
 118 ALTER TABLE `0_item_units` ENGINE=InnoDB;
 119 ALTER TABLE `0_locations` ENGINE=InnoDB;
 120 ALTER TABLE `0_movement_types` ENGINE=InnoDB;
 121 ALTER TABLE `0_payment_terms` ENGINE=InnoDB;
 122 ALTER TABLE `0_prices` ENGINE=InnoDB;
 123 ALTER TABLE `0_printers` ENGINE=InnoDB;
 124 ALTER TABLE `0_print_profiles` ENGINE=InnoDB;
 125 ALTER TABLE `0_purch_data` ENGINE=InnoDB;
 126 ALTER TABLE `0_quick_entries` ENGINE=InnoDB;
 127 ALTER TABLE `0_quick_entry_lines` ENGINE=InnoDB;
 128 ALTER TABLE `0_salesman` ENGINE=InnoDB;
 129 ALTER TABLE `0_sales_pos` ENGINE=InnoDB;
 130 ALTER TABLE `0_sales_types` ENGINE=InnoDB;
 131 ALTER TABLE `0_security_roles` ENGINE=InnoDB;
 132 ALTER TABLE `0_shippers` ENGINE=InnoDB;
 133 ALTER TABLE `0_sql_trail` ENGINE=InnoDB;
 134 ALTER TABLE `0_stock_category` ENGINE=InnoDB;
 135 ALTER TABLE `0_suppliers` ENGINE=InnoDB;
 136 ALTER TABLE `0_sys_prefs` ENGINE=InnoDB;
 137 ALTER TABLE `0_tags` ENGINE=InnoDB;
 138 ALTER TABLE `0_tag_associations` ENGINE=InnoDB;
 139 ALTER TABLE `0_useronline` ENGINE=InnoDB;
 140 ALTER TABLE `0_users` ENGINE=InnoDB;
 141 ALTER TABLE `0_workcentres` ENGINE=InnoDB;
 142
 143 ALTER TABLE `0_gl_trans` CHANGE `type_no` `type_no` int(11) NOT NULL default '0';
 144 ALTER TABLE `0_loc_stock` CHANGE `reorder_level` `reorder_level` double NOT NULL default '0';
 145
 146 # added dimensions in supplier documents
 147 ALTER TABLE `0_supp_invoice_items` ADD COLUMN `dimension_id` int(11) NOT NULL DEFAULT '0' AFTER `memo_`;
 148 ALTER TABLE `0_supp_invoice_items` ADD COLUMN `dimension2_id` int(11) NOT NULL DEFAULT '0' AFTER `dimension_id`;
 149
 150 UPDATE `0_supp_invoice_items` si
 151         LEFT JOIN `0_gl_trans` gl ON si.supp_trans_type=gl.`type` AND si.supp_trans_no=gl.type_no AND si.gl_code=gl.account
 152         SET si.dimension_id=gl.dimension_id, si.dimension2_id=gl.dimension2_id
 153 WHERE si.grn_item_id=-1 AND (gl.dimension_id OR gl.dimension2_id)
 154
 155 ALTER TABLE `0_quick_entries` ADD COLUMN `usage` varchar(120) NULL AFTER `description`;
 156 ALTER TABLE `0_quick_entry_lines` ADD COLUMN `memo` tinytext NOT NULL AFTER `amount`;
 157
 158 # multiply allocations to single jiurnal transaction
 159 ALTER TABLE `0_cust_allocations` ADD COLUMN `person_id` int(11) DEFAULT NULL AFTER `id`;
 160 UPDATE `0_cust_allocations` alloc LEFT JOIN `0_debtor_trans` trans ON alloc.trans_no_to=trans.trans_no AND alloc.trans_type_to=trans.type
 161         SET alloc.person_id = trans.debtor_no;
 162
 163 ALTER TABLE `0_supp_allocations` ADD COLUMN `person_id` int(11) DEFAULT NULL AFTER `id`;
 164 UPDATE `0_supp_allocations` alloc LEFT JOIN `0_supp_trans` trans ON alloc.trans_no_to=trans.trans_no AND alloc.trans_type_to=trans.type
 165         SET alloc.person_id = trans.supplier_id;
 166
 167 ALTER TABLE `0_cust_allocations` DROP KEY `trans_type_from`;
 168 ALTER TABLE `0_cust_allocations` ADD  UNIQUE KEY(`person_id`,`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`);
 169 ALTER TABLE `0_supp_allocations` DROP KEY `trans_type_from`;
 170 ALTER TABLE `0_supp_allocations` ADD  UNIQUE KEY(`person_id`,`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`);
 171
 172 # full support for any journal transaction
 173 DROP TABLE IF EXISTS `0_journal`;
 174 CREATE TABLE `0_journal` (
 175   `type` smallint(6) NOT NULL DEFAULT '0',
 176   `trans_no` int(11) NOT NULL DEFAULT '0',
 177   `tran_date` date DEFAULT '0000-00-00',
 178   `reference` varchar(60) NOT NULL DEFAULT '',
 179   `source_ref` varchar(60) NOT NULL DEFAULT '',
 180   `event_date` date DEFAULT '0000-00-00',
 181   `doc_date` date NOT NULL DEFAULT '0000-00-00',
 182   `currency` char(3) NOT NULL DEFAULT '',
 183   `amount` double NOT NULL DEFAULT '0',
 184   `rate` double NOT NULL DEFAULT '1',
 185   PRIMARY KEY `Type_and_Number` (`type`,`trans_no`),
 186   KEY `tran_date` (`tran_date`)
 187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
 188
 189 INSERT INTO `0_journal` (`type`, `trans_no`, `tran_date`, `reference`, `event_date`,`doc_date`,`currency`,`amount`)
 190  SELECT `gl`.`type`, `gl`.`type_no`, `gl`.`tran_date`, `ref`.`reference`, `gl`.`event_date`,
 191                 `gl`.`doc_date`, `sys_curr`.`value`, SUM(IF(`gl`.`amount`>0,`gl`.`amount`,0))
 192  FROM `0_gl_trans` gl LEFT JOIN `0_refs` ref ON gl.type = ref.type AND gl.type_no=ref.id
 193  LEFT JOIN `0_sys_prefs` sys_curr ON `sys_curr`.`name`='curr_default'
 194  WHERE `gl`.`type` IN(0, 35)
 195  GROUP BY `type`,`type_no`;
 196
 197 # allow multiply customers.suppliers in single journal transaction
 198 ALTER TABLE `0_debtor_trans` DROP PRIMARY KEY;
 199 ALTER TABLE `0_debtor_trans` ADD  PRIMARY KEY (`type`,`trans_no`, `debtor_no`);
 200 ALTER TABLE `0_supp_trans` DROP PRIMARY KEY;
 201 ALTER TABLE `0_supp_trans` ADD  PRIMARY KEY (`type`,`trans_no`, `supplier_id`);
 202