`gl_date` date NOT NULL default '0000-00-00',
`gl_seq` int(11) unsigned default NULL,
PRIMARY KEY (`id`),
- KEY (`fiscal_year`, `gl_seq`)
+ KEY `Seq` (`fiscal_year`, `gl_date`, `gl_seq`),
+ KEY `Type_and_Number` (`type`,`trans_no`)
) TYPE=InnoDB ;
### Data of table `0_audit_trail` ###
`inactive` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `bank_account_name` (`bank_account_name`),
- KEY `bank_account_number` (`bank_account_number`)
+ KEY `bank_account_number` (`bank_account_number`),
+ KEY (`account_code`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
`reconciled` date default NULL,
PRIMARY KEY (`id`),
KEY `bank_act` (`bank_act`,`ref`),
- KEY `type` (`type`,`trans_no`)
+ KEY `type` (`type`,`trans_no`),
+ KEY (`bank_act`,`reconciled`),
+ KEY (`bank_act`,`trans_date`)
) TYPE=InnoDB AUTO_INCREMENT=12 ;
KEY `id` (`id`),
KEY `loc_code` (`loc_code`),
KEY `parent` (`parent`,`loc_code`),
- KEY `Parent_2` (`parent`),
KEY `workcentre_added` (`workcentre_added`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
`person_type_id` int(11) default NULL,
`person_id` tinyblob,
PRIMARY KEY (`counter`),
- KEY `Type_and_Number` (`type`,`type_no`)
+ KEY `Type_and_Number` (`type`,`type_no`),
+ KEY `Account` (`account`, `tran_date`, `dimension_id`, `dimension2_id`)
) TYPE=InnoDB AUTO_INCREMENT=1 ;
`account_type` int(11) NOT NULL default '0',
`inactive` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`account_code`),
- KEY `account_code` (`account_code`),
- KEY `account_name` (`account_name`)
+ KEY `account_name` (`account_name`),
+ KEY `accounts_by_type` (`account_type`, `account_code`)
) TYPE=MyISAM ;
`parent` int(11) NOT NULL default '-1',
`inactive` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
+ KEY (`class_id`),
KEY `name` (`name`)
) TYPE=MyISAM AUTO_INCREMENT=13 ;
`trans_type_from` int(11) default NULL,
`trans_no_to` int(11) default NULL,
`trans_type_to` int(11) default NULL,
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY `From` (`trans_type_from`, `trans_no_from`),
+ KEY `To` (`trans_type_to`, `trans_no_to`)
) TYPE=InnoDB AUTO_INCREMENT=2 ;
`inactive` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`branch_code`,`debtor_no`),
KEY `branch_code` (`branch_code`),
- KEY `br_name` (`br_name`)
+ KEY (`branch_ref`),
+ KEY (`group_no`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
`trans_link` int(11) NOT NULL default '0',
`dimension_id` int(11) NOT NULL default '0',
`dimension2_id` int(11) NOT NULL default '0',
- PRIMARY KEY (`trans_no`,`type`),
- KEY `debtor_no` (`debtor_no`,`branch_code`)
+ PRIMARY KEY (`type`, `trans_no`),
+ KEY `debtor_no` (`debtor_no`,`branch_code`),
+ KEY (`tran_date`)
) TYPE=InnoDB ;
`discount_percent` double NOT NULL default '0',
`standard_cost` double NOT NULL default '0',
`qty_done` double NOT NULL default '0',
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY `Transaction` (`debtor_trans_type`, `debtor_trans_no`)
) TYPE=InnoDB AUTO_INCREMENT=9 ;
`notes` tinytext NOT NULL,
`inactive` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`debtor_no`),
- UNIQUE KEY `name` (`name`)
+ UNIQUE KEY `name` (`name`),
+ KEY (`debtor_ref`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
`date_` date NOT NULL default '0000-00-00',
`due_date` date NOT NULL default '0000-00-00',
PRIMARY KEY (`id`),
- UNIQUE KEY `reference` (`reference`)
+ UNIQUE KEY `reference` (`reference`),
+ KEY (`date_`),
+ KEY (`due_date`),
+ KEY (`type_`)
) TYPE=InnoDB AUTO_INCREMENT=3 ;
`begin` date default '0000-00-00',
`end` date default '0000-00-00',
`closed` tinyint(1) NOT NULL default '0',
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ UNIQUE KEY(`begin`),
+ UNIQUE KEY(`end`)
) TYPE=InnoDB AUTO_INCREMENT=3 ;
`person_type_id` int(11) default NULL,
`person_id` tinyblob,
PRIMARY KEY (`counter`),
- KEY `Type_and_Number` (`type`,`type_no`)
+ KEY `Type_and_Number` (`type`,`type_no`),
+ KEY (`dimension_id`),
+ KEY (`dimension2_id`),
+ KEY (`tran_date`),
+ KEY `account_and_tran_date` (`account`, `tran_date`)
) TYPE=InnoDB AUTO_INCREMENT=84 ;
`reference` varchar(60) NOT NULL default '',
`delivery_date` date NOT NULL default '0000-00-00',
`loc_code` varchar(5) default NULL,
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY(`delivery_date`),
+ KEY(`purch_order_no`)
) TYPE=InnoDB AUTO_INCREMENT=2 ;
`description` tinytext,
`qty_recd` double NOT NULL default '0',
`quantity_inv` double NOT NULL default '0',
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY (`grn_batch_id`)
) TYPE=InnoDB AUTO_INCREMENT=4 ;
`is_foreign` tinyint(1) NOT NULL default '0',
`inactive` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
- UNIQUE KEY `stock_id` (`stock_id`,`item_code`)
+ UNIQUE KEY `stock_id` (`stock_id`,`item_code`),
+ KEY (`item_code`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;
`std_cost_unit` double NOT NULL default '0',
`quantity_ordered` double NOT NULL default '0',
`quantity_received` double NOT NULL default '0',
- PRIMARY KEY (`po_detail_item`)
+ PRIMARY KEY (`po_detail_item`),
+ KEY `order` (`order_no`, `po_detail_item`)
) TYPE=InnoDB AUTO_INCREMENT=6 ;
`requisition_no` tinytext,
`into_stock_location` varchar(5) NOT NULL default '',
`delivery_address` tinytext NOT NULL,
- PRIMARY KEY (`order_no`)
+ PRIMARY KEY (`order_no`),
+ KEY (`ord_date`)
) TYPE=InnoDB AUTO_INCREMENT=3 ;
`id` int(11) NOT NULL default '0',
`type` int(11) NOT NULL default '0',
`reference` varchar(100) NOT NULL default '',
- PRIMARY KEY (`id`,`type`)
+ PRIMARY KEY (`id`,`type`),
+ KEY `Type_and_Reference` (`type`,`reference`)
) TYPE=InnoDB ;
`unit_price` double NOT NULL default '0',
`quantity` double NOT NULL default '0',
`discount_percent` double NOT NULL default '0',
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY `sorder` (`trans_type`, `order_no`)
) TYPE=InnoDB AUTO_INCREMENT=6 ;
`standard_cost` double NOT NULL default '0',
`visible` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`trans_id`),
- KEY `type` (`type`,`trans_no`)
+ KEY `type` (`type`,`trans_no`),
+ KEY `Move` (`stock_id`,`loc_code`, `tran_date`)
) TYPE=InnoDB AUTO_INCREMENT=37 ;
`trans_type_from` int(11) default NULL,
`trans_no_to` int(11) default NULL,
`trans_type_to` int(11) default NULL,
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY `From` (`trans_type_from`, `trans_no_from`),
+ KEY `To` (`trans_type_to`, `trans_no_to`)
) TYPE=InnoDB AUTO_INCREMENT=2 ;
`unit_price` double NOT NULL default '0',
`unit_tax` double NOT NULL default '0',
`memo_` tinytext,
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY `Transaction` (`supp_trans_type`, `supp_trans_no`, `stock_id`)
) TYPE=InnoDB AUTO_INCREMENT=6 ;
`ov_gst` double NOT NULL default '0',
`rate` double NOT NULL default '1',
`alloc` double NOT NULL default '0',
- PRIMARY KEY (`trans_no`,`type`),
+ PRIMARY KEY (`type`, `trans_no`),
KEY `supplier_id` (`supplier_id`),
KEY `SupplierID_2` (`supplier_id`,`supp_reference`),
- KEY `type` (`type`)
+ KEY `type` (`type`),
+ KEY (`tran_date`)
) TYPE=InnoDB ;
`payment_discount_account` varchar(11) default NULL,
`notes` tinytext NOT NULL,
`inactive` tinyint(1) NOT NULL default '0',
- PRIMARY KEY (`supplier_id`)
+ PRIMARY KEY (`supplier_id`),
+ KEY (`supp_ref`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
INSERT INTO `0_suppliers` VALUES ('2', 'Lucky Luke Inc.', 'Lucky Luke', 'Mailing 1\r\nMailing 2\r\nMailing 3', 'Address 1\r\nAddress 2\r\nAddress 3', '(111) 222.333.444', '', '', '654321', 'Luke', '333', '', '', '', 'USD', '3', '0', '0', '1', '1', '5010', '2100', '5060', '', '0');
INSERT INTO `0_suppliers` VALUES ('3', 'Money Makers Ltd.', 'Money Makers', 'Mailing 1\r\nMailing 2\r\nMailing 3', 'Address 1\r\nAddress 2\r\nAddress 3', '+44 444 555 666', '', '', '987654', 'Makers', '222', '', '', '', 'GBP', '3', '0', '0', '2', '1', '5010', '2100', '5060', '', '0');
+### Structure of table `0_sys_prefs` ###
+
+DROP TABLE IF EXISTS `0_sys_prefs`;
+
+CREATE TABLE `0_sys_prefs` (
+ `name` varchar(35) NOT NULL default '',
+ `category` varchar(30) default NULL,
+ `type` varchar(20) NOT NULL default 'varchar',
+ `length` smallint(6) default NULL,
+ `value` tinytext,
+ PRIMARY KEY (`name`),
+ KEY `category` (`category`)
+) TYPE=MyISAM;
+
+
+### Data of table `0_sys_prefs` ###
+
+INSERT INTO `0_sys_prefs` VALUES ('coy_name', 'setup.company', 'varchar', '60', 'Training Co.');
+INSERT INTO `0_sys_prefs` VALUES ('gst_no', 'setup.company', 'varchar', '25', '9876543');
+INSERT INTO `0_sys_prefs` VALUES ('coy_no', 'setup.company', 'varchar', '25', '123456789');
+INSERT INTO `0_sys_prefs` VALUES ('tax_prd', 'setup.company', 'int', '11', '1');
+INSERT INTO `0_sys_prefs` VALUES ('tax_last', 'setup.company', 'int', '11', '1');
+INSERT INTO `0_sys_prefs` VALUES ('postal_address', 'setup.company', 'tinytext', '0', 'Address 1\r\nAddress 2\r\nAddress 3');
+INSERT INTO `0_sys_prefs` VALUES ('phone', 'setup.company', 'varchar', '30', '(222) 111.222.333');
+INSERT INTO `0_sys_prefs` VALUES ('fax', 'setup.company', 'varchar', '30', NULL);
+INSERT INTO `0_sys_prefs` VALUES ('email', 'setup.company', 'varchar', '100', 'delta@delta.com');
+INSERT INTO `0_sys_prefs` VALUES ('coy_logo', 'setup.company', 'varchar', '100', 'logo_frontaccounting.jpg');
+INSERT INTO `0_sys_prefs` VALUES ('domicile', 'setup.company', 'varchar', '55', NULL);
+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 ('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');
+INSERT INTO `0_sys_prefs` VALUES ('base_sales', 'setup.company', 'int', '11', '1');
+INSERT INTO `0_sys_prefs` VALUES ('time_zone', 'setup.company', 'tinyint', '1', '0');
+INSERT INTO `0_sys_prefs` VALUES ('add_pct', 'setup.company', 'int', '5', '-1');
+INSERT INTO `0_sys_prefs` VALUES ('round_to', 'setup.company', 'int', '5', '1');
+INSERT INTO `0_sys_prefs` VALUES ('login_tout', 'setup.company', 'smallint', '6', '600');
+INSERT INTO `0_sys_prefs` VALUES ('past_due_days', 'glsetup.general', 'int', '11', '30');
+INSERT INTO `0_sys_prefs` VALUES ('profit_loss_year_act', 'glsetup.general', 'varchar', '11', '9990');
+INSERT INTO `0_sys_prefs` VALUES ('retained_earnings_act', 'glsetup.general', 'varchar', '11', '3590');
+INSERT INTO `0_sys_prefs` VALUES ('bank_charge_act', 'glsetup.general', 'varchar', '11', '5690');
+INSERT INTO `0_sys_prefs` VALUES ('exchange_diff_act', 'glsetup.general', 'varchar', '11', '4450');
+INSERT INTO `0_sys_prefs` VALUES ('default_credit_limit', 'glsetup.customer', 'int', '11', '1000');
+INSERT INTO `0_sys_prefs` VALUES ('accumulate_shipping', 'glsetup.customer', 'tinyint', '1', '0');
+INSERT INTO `0_sys_prefs` VALUES ('legal_text', 'glsetup.customer', 'tinytext', '0', NULL);
+INSERT INTO `0_sys_prefs` VALUES ('freight_act', 'glsetup.customer', 'varchar', '11', '4430');
+INSERT INTO `0_sys_prefs` VALUES ('debtors_act', 'glsetup.sales', 'varchar', '11', '1200');
+INSERT INTO `0_sys_prefs` VALUES ('default_sales_act', 'glsetup.sales', 'varchar', '11', '4010');
+INSERT INTO `0_sys_prefs` VALUES ('default_sales_discount_act', 'glsetup.sales', 'varchar', '11', '4510');
+INSERT INTO `0_sys_prefs` VALUES ('default_prompt_payment_act', 'glsetup.sales', 'varchar', '11', '4500');
+INSERT INTO `0_sys_prefs` VALUES ('default_delivery_required', 'glsetup.sales', 'smallint', '6', '1');
+INSERT INTO `0_sys_prefs` VALUES ('default_dim_required', 'glsetup.dims', 'int', '11', '20');
+INSERT INTO `0_sys_prefs` VALUES ('pyt_discount_act', 'glsetup.purchase', 'varchar', '11', '5060');
+INSERT INTO `0_sys_prefs` VALUES ('creditors_act', 'glsetup.purchase', 'varchar', '11', '2100');
+INSERT INTO `0_sys_prefs` VALUES ('po_over_receive', 'glsetup.purchase', 'int', '11', '10');
+INSERT INTO `0_sys_prefs` VALUES ('po_over_charge', 'glsetup.purchase', 'int', '11', '10');
+INSERT INTO `0_sys_prefs` VALUES ('allow_negative_stock', 'glsetup.inventory', 'tinyint', '1', '0');
+INSERT INTO `0_sys_prefs` VALUES ('default_inventory_act', 'glsetup.items', 'varchar', '11', '1510');
+INSERT INTO `0_sys_prefs` VALUES ('default_cogs_act', 'glsetup.items', 'varchar', '11', '5010');
+INSERT INTO `0_sys_prefs` VALUES ('default_adj_act', 'glsetup.items', 'varchar', '11', '5040');
+INSERT INTO `0_sys_prefs` VALUES ('default_inv_sales_act', 'glsetup.items', 'varchar', '11', '4010');
+INSERT INTO `0_sys_prefs` VALUES ('default_assembly_act', 'glsetup.items', 'varchar', '11', '1530');
+INSERT INTO `0_sys_prefs` VALUES ('default_workorder_required', 'glsetup.manuf', 'int', '11', '20');
+INSERT INTO `0_sys_prefs` VALUES ('version_id', 'system', 'varchar', '11', '2.3');
+
+
### Structure of table `0_sys_types` ###
`net_amount` double NOT NULL default '0',
`amount` double NOT NULL default '0',
`memo` tinytext,
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY `Type_and_Number` (`trans_type`,`trans_no`),
+ KEY (`tran_date`)
) TYPE=InnoDB AUTO_INCREMENT=12 ;
`issue_date` date default NULL,
`loc_code` varchar(5) default NULL,
`workcentre_id` int(11) default NULL,
- PRIMARY KEY (`issue_no`)
+ PRIMARY KEY (`issue_no`),
+ KEY (`workorder_id`)
) TYPE=InnoDB AUTO_INCREMENT=1 ;
`workorder_id` int(11) NOT NULL default '0',
`quantity` double NOT NULL default '0',
`date_` date NOT NULL default '0000-00-00',
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY (`workorder_id`)
) TYPE=InnoDB AUTO_INCREMENT=2 ;
`std_cost` double NOT NULL default '0',
`loc_code` char(5) NOT NULL default '',
`units_issued` double NOT NULL default '0',
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY (`workorder_id`)
) TYPE=InnoDB AUTO_INCREMENT=22 ;
`ip` varchar(40) NOT NULL default '',
`file` varchar(100) NOT NULL default '',
PRIMARY KEY `id` (`id`) ,
- KEY (`timestamp`)
+ KEY (`timestamp`),
+ KEY(`ip`)
) TYPE=MYISAM AUTO_INCREMENT=1;