X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sql%2Fen_US-new.sql;h=31947a52b602dc8590f82c5c3a7871001516334d;hb=33dfc21a26f08e9ac19048bbcf80cce2351a6cfe;hp=3b3d3f2e0e9095365a220748b715ba9619d614c0;hpb=edc5aa7ee57d0f02cd3f860113b1d535ffff4718;p=fa-stable.git diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index 3b3d3f2e..31947a52 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -72,7 +72,7 @@ CREATE TABLE IF NOT EXISTS `0_audit_trail` ( `user` smallint(6) unsigned NOT NULL default '0', `stamp` timestamp NOT NULL, `description` varchar(60) default NULL, - `fiscal_year` int(11) NOT NULL, + `fiscal_year` int(11) NOT NULL default 0, `gl_date` date NOT NULL default '0000-00-00', `gl_seq` int(11) unsigned default NULL, PRIMARY KEY (`id`), @@ -529,6 +529,7 @@ INSERT INTO `0_currencies` VALUES('Pounds', 'GBP', '?', 'England', 'Pence', 1, 0 DROP TABLE IF EXISTS `0_cust_allocations`; CREATE TABLE IF NOT EXISTS `0_cust_allocations` ( `id` int(11) NOT NULL auto_increment, + `person_id` int(11) DEFAULT NULL, `amt` double unsigned default NULL, `date_alloc` date NOT NULL default '0000-00-00', `trans_no_from` int(11) default NULL, @@ -536,7 +537,7 @@ CREATE TABLE IF NOT EXISTS `0_cust_allocations` ( `trans_no_to` int(11) default NULL, `trans_type_to` int(11) default NULL, PRIMARY KEY (`id`), - UNIQUE KEY (`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`), + UNIQUE KEY `trans_type_from` (`person_id`,`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`), KEY `From` (`trans_type_from`,`trans_no_from`), KEY `To` (`trans_type_to`,`trans_no_to`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; @@ -760,8 +761,7 @@ CREATE TABLE IF NOT EXISTS `0_fiscal_year` ( -- Dumping data for table `0_fiscal_year` -- -INSERT INTO `0_fiscal_year` VALUES(1, '2013-01-01', '2013-12-31', 0); -INSERT INTO `0_fiscal_year` VALUES(2, '2014-01-01', '2014-12-31', 0); +INSERT INTO `0_fiscal_year` VALUES(1, '2015-01-01', '2015-12-31', 0); -- -- Table structure for table `0_gl_trans` @@ -771,7 +771,7 @@ DROP TABLE IF EXISTS `0_gl_trans`; CREATE TABLE IF NOT EXISTS `0_gl_trans` ( `counter` int(11) NOT NULL auto_increment, `type` smallint(6) NOT NULL default '0', - `type_no` bigint(16) NOT NULL default '1', + `type_no` int(11) NOT NULL default '0', `tran_date` date NOT NULL default '0000-00-00', `account` varchar(15) NOT NULL default '', `memo_` tinytext NOT NULL, @@ -954,6 +954,27 @@ CREATE TABLE IF NOT EXISTS `0_item_units` ( INSERT INTO `0_item_units` VALUES('each', 'Each', 0, 0); INSERT INTO `0_item_units` VALUES('hr', 'Hours', 0, 0); +-- Table structure for table `0_journal` + +DROP TABLE IF EXISTS `0_journal`; +CREATE TABLE `0_journal` ( + `type` smallint(6) NOT NULL DEFAULT '0', + `trans_no` int(11) NOT NULL DEFAULT '0', + `tran_date` date DEFAULT '0000-00-00', + `reference` varchar(60) NOT NULL DEFAULT '', + `source_ref` varchar(60) NOT NULL DEFAULT '', + `event_date` date DEFAULT '0000-00-00', + `doc_date` date NOT NULL DEFAULT '0000-00-00', + `currency` char(3) NOT NULL DEFAULT '', + `amount` double NOT NULL DEFAULT '0', + `rate` double NOT NULL DEFAULT '1', + PRIMARY KEY (`type`,`trans_no`), + KEY `tran_date` (`tran_date`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; + +-- Data of table `0_journal` + + -- -------------------------------------------------------- -- @@ -990,7 +1011,7 @@ DROP TABLE IF EXISTS `0_loc_stock`; CREATE TABLE IF NOT EXISTS `0_loc_stock` ( `loc_code` char(5) NOT NULL default '', `stock_id` char(20) NOT NULL default '', - `reorder_level` bigint(20) NOT NULL default '0', + `reorder_level` double NOT NULL default '0', PRIMARY KEY (`loc_code`,`stock_id`), KEY `stock_id` (`stock_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -999,28 +1020,6 @@ CREATE TABLE IF NOT EXISTS `0_loc_stock` ( -- Dumping data for table `0_loc_stock` -- - --- -------------------------------------------------------- - --- --- Table structure for table `0_movement_types` --- - -DROP TABLE IF EXISTS `0_movement_types`; -CREATE TABLE IF NOT EXISTS `0_movement_types` ( - `id` int(11) NOT NULL auto_increment, - `name` varchar(60) NOT NULL default '', - `inactive` tinyint(1) NOT NULL default '0', - PRIMARY KEY (`id`), - UNIQUE KEY `name` (`name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; - --- --- Dumping data for table `0_movement_types` --- - -INSERT INTO `0_movement_types` VALUES(1, 'Adjustment', 0); - -- -------------------------------------------------------- -- @@ -1217,6 +1216,7 @@ CREATE TABLE IF NOT EXISTS `0_quick_entries` ( `id` smallint(6) unsigned NOT NULL auto_increment, `type` tinyint(1) NOT NULL default '0', `description` varchar(60) NOT NULL, + `usage` varchar(120) NULL, `base_amount` double NOT NULL default '0', `base_desc` varchar(60) default NULL, `bal_type` tinyint(1) NOT NULL default '0', @@ -1228,9 +1228,9 @@ CREATE TABLE IF NOT EXISTS `0_quick_entries` ( -- Dumping data for table `0_quick_entries` -- -INSERT INTO `0_quick_entries` VALUES(1, 1, 'Maintenance', 0, 'Amount', 0); -INSERT INTO `0_quick_entries` VALUES(2, 4, 'Phone', 0, 'Amount', 0); -INSERT INTO `0_quick_entries` VALUES(3, 2, 'Cash Sales', 0, 'Amount', 0); +INSERT INTO `0_quick_entries` VALUES(1, 1, 'Maintenance', NULL, 0, 'Amount', 0); +INSERT INTO `0_quick_entries` VALUES(2, 4, 'Phone', NULL, 0, 'Amount', 0); +INSERT INTO `0_quick_entries` VALUES(3, 2, 'Cash Sales', 'Retail sales without invoice', 0, 'Amount', 0); -- -------------------------------------------------------- @@ -1243,6 +1243,7 @@ CREATE TABLE IF NOT EXISTS `0_quick_entry_lines` ( `id` smallint(6) unsigned NOT NULL auto_increment, `qid` smallint(6) unsigned NOT NULL, `amount` double default '0', + `memo` tinytext NOT NULL, `action` varchar(2) NOT NULL, `dest_id` varchar(15) NOT NULL default '', `dimension_id` smallint(6) unsigned default NULL, @@ -1255,12 +1256,12 @@ CREATE TABLE IF NOT EXISTS `0_quick_entry_lines` ( -- Dumping data for table `0_quick_entry_lines` -- -INSERT INTO `0_quick_entry_lines` VALUES(1, 1, 0, 't-', '1', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(2, 2, 0, 't-', '1', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(3, 3, 0, 't-', '1', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(4, 3, 0, '=', '4010', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(5, 1, 0, '=', '5765', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(6, 2, 0, '=', '5780', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(1, 1, 0, '', 't-', '1', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(2, 2, 0, '', 't-', '1', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(3, 3, 0, '', 't-', '1', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(4, 3, 0, '', '=', '4010', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(5, 1, 0, '', '=', '5765', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(6, 2, 0, '', '=', '5780', 0, 0); -- -------------------------------------------------------- @@ -1610,26 +1611,21 @@ CREATE TABLE IF NOT EXISTS `0_stock_master` ( -- Table structure for table `0_stock_moves` -- -DROP TABLE IF EXISTS `0_stock_moves`; -CREATE TABLE IF NOT EXISTS `0_stock_moves` ( - `trans_id` int(11) NOT NULL auto_increment, - `trans_no` int(11) NOT NULL default '0', - `stock_id` char(20) NOT NULL default '', - `type` smallint(6) NOT NULL default '0', - `loc_code` char(5) NOT NULL default '', - `tran_date` date NOT NULL default '0000-00-00', - `person_id` int(11) default NULL, - `price` double NOT NULL default '0', - `reference` char(40) NOT NULL default '', - `qty` double NOT NULL default '1', - `discount_percent` double NOT NULL default '0', - `standard_cost` double NOT NULL default '0', - `visible` tinyint(1) NOT NULL default '1', - PRIMARY KEY (`trans_id`), +CREATE TABLE `0_stock_moves` ( + `trans_id` int(11) NOT NULL AUTO_INCREMENT, + `trans_no` int(11) NOT NULL DEFAULT '0', + `stock_id` char(20) NOT NULL DEFAULT '', + `type` smallint(6) NOT NULL DEFAULT '0', + `loc_code` char(5) NOT NULL DEFAULT '', + `tran_date` date NOT NULL DEFAULT '0000-00-00', + `price` double NOT NULL DEFAULT '0', + `reference` char(40) NOT NULL DEFAULT '', + `qty` double NOT NULL DEFAULT '1', + `standard_cost` double NOT NULL DEFAULT '0', + PRIMARY KEY (`trans_id`), KEY `type` (`type`,`trans_no`), KEY `Move` (`stock_id`,`loc_code`,`tran_date`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; - +) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; -- -- Dumping data for table `0_stock_moves` -- @@ -1656,7 +1652,6 @@ CREATE TABLE IF NOT EXISTS `0_suppliers` ( `curr_code` char(3) default NULL, `payment_terms` int(11) default NULL, `tax_included` tinyint(1) NOT NULL default '0', - `tax_algorithm` tinyint(1) NOT NULL default '1', `dimension_id` int(11) default '0', `dimension2_id` int(11) default '0', `tax_group_id` int(11) default NULL, @@ -1684,6 +1679,7 @@ CREATE TABLE IF NOT EXISTS `0_suppliers` ( DROP TABLE IF EXISTS `0_supp_allocations`; CREATE TABLE IF NOT EXISTS `0_supp_allocations` ( `id` int(11) NOT NULL auto_increment, + `person_id` int(11) DEFAULT NULL, `amt` double unsigned default NULL, `date_alloc` date NOT NULL default '0000-00-00', `trans_no_from` int(11) default NULL, @@ -1691,7 +1687,7 @@ CREATE TABLE IF NOT EXISTS `0_supp_allocations` ( `trans_no_to` int(11) default NULL, `trans_type_to` int(11) default NULL, PRIMARY KEY (`id`), - UNIQUE KEY (`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`), + UNIQUE KEY `trans_type_from` (`person_id`,`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`), KEY `From` (`trans_type_from`,`trans_no_from`), KEY `To` (`trans_type_to`,`trans_no_to`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; @@ -1721,6 +1717,8 @@ CREATE TABLE IF NOT EXISTS `0_supp_invoice_items` ( `unit_price` double NOT NULL default '0', `unit_tax` double NOT NULL default '0', `memo_` tinytext, + `dimension_id` int(11) NOT NULL DEFAULT '0', + `dimension2_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `Transaction` (`supp_trans_type`,`supp_trans_no`,`stock_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; @@ -1751,7 +1749,6 @@ CREATE TABLE IF NOT EXISTS `0_supp_trans` ( `rate` double NOT NULL default '1', `alloc` double NOT NULL default '0', `tax_included` tinyint(1) NOT NULL default '0', - `tax_algorithm` tinyint(1) NOT NULL default '1', PRIMARY KEY (`type`,`trans_no`), KEY `supplier_id` (`supplier_id`), KEY `SupplierID_2` (`supplier_id`,`supp_reference`), @@ -1776,7 +1773,7 @@ CREATE TABLE IF NOT EXISTS `0_sys_prefs` ( `category` varchar(30) default NULL, `type` varchar(20) NOT NULL default '', `length` smallint(6) default NULL, - `value` text NOT NULL, + `value` TEXT NOT NULL DEFAULT '', PRIMARY KEY (`name`), KEY `category` (`category`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1798,7 +1795,7 @@ INSERT INTO `0_sys_prefs` VALUES('coy_logo', 'setup.company', 'varchar', 100, '' INSERT INTO `0_sys_prefs` VALUES('domicile', 'setup.company', 'varchar', 55, ''); INSERT INTO `0_sys_prefs` VALUES('curr_default', 'setup.company', 'char', 3, 'USD'); INSERT INTO `0_sys_prefs` VALUES('use_dimension', 'setup.company', 'tinyint', 1, '1'); -INSERT INTO `0_sys_prefs` VALUES('f_year', 'setup.company', 'int', 11, '2'); +INSERT INTO `0_sys_prefs` VALUES('f_year', 'setup.company', 'int', 11, '1'); INSERT INTO `0_sys_prefs` VALUES('no_item_list', 'setup.company', 'tinyint', 1, '0'); INSERT INTO `0_sys_prefs` VALUES('no_customer_list', 'setup.company', 'tinyint', 1, '0'); INSERT INTO `0_sys_prefs` VALUES('no_supplier_list', 'setup.company', 'tinyint', 1, '0'); @@ -2017,6 +2014,7 @@ CREATE TABLE IF NOT EXISTS `0_trans_tax_details` ( `net_amount` double NOT NULL default '0', `amount` double NOT NULL default '0', `memo` tinytext, + `reg_type` tinyint(1) default NULL, PRIMARY KEY (`id`), KEY `Type_and_Number` (`trans_type`,`trans_no`), KEY `tran_date` (`tran_date`)