1 -- MySQL dump of database 'en_US-demo' on host 'localhost'
2 -- Backup Date and Time: 2015-05-07 14:03
3 -- Built by FrontAccounting 2.4.Beta
4 -- http://frontaccounting.com
5 -- Company: Training Co
8 -- Compatibility: 2.4.1
11 -- Database: `fatraining`
14 -- --------------------------------------------------------
19 -- Table structure for table `0_areas`
22 DROP TABLE IF EXISTS `0_areas`;
23 CREATE TABLE IF NOT EXISTS `0_areas` (
24 `area_code` int(11) NOT NULL AUTO_INCREMENT,
25 `description` varchar(60) NOT NULL DEFAULT '',
26 `inactive` tinyint(1) NOT NULL DEFAULT '0',
27 PRIMARY KEY (`area_code`),
28 UNIQUE KEY `description` (`description`)
29 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
32 -- Dumping data for table `0_areas`
35 INSERT INTO `0_areas` VALUES (1, 'Global', 0);
37 -- --------------------------------------------------------
40 -- Table structure for table `0_attachments`
43 DROP TABLE IF EXISTS `0_attachments`;
44 CREATE TABLE IF NOT EXISTS `0_attachments` (
45 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
46 `description` varchar(60) NOT NULL DEFAULT '',
47 `type_no` int(11) NOT NULL DEFAULT '0',
48 `trans_no` int(11) NOT NULL DEFAULT '0',
49 `unique_name` varchar(60) NOT NULL DEFAULT '',
50 `tran_date` date NOT NULL DEFAULT '0000-00-00',
51 `filename` varchar(60) NOT NULL DEFAULT '',
52 `filesize` int(11) NOT NULL DEFAULT '0',
53 `filetype` varchar(60) NOT NULL DEFAULT '',
55 KEY `type_no` (`type_no`,`trans_no`)
59 -- Dumping data for table `0_attachments`
62 -- --------------------------------------------------------
65 -- Table structure for table `0_audit_trail`
68 DROP TABLE IF EXISTS `0_audit_trail`;
69 CREATE TABLE IF NOT EXISTS `0_audit_trail` (
70 `id` int(11) NOT NULL AUTO_INCREMENT,
71 `type` smallint(6) unsigned NOT NULL DEFAULT '0',
72 `trans_no` int(11) unsigned NOT NULL DEFAULT '0',
73 `user` smallint(6) unsigned NOT NULL DEFAULT '0',
74 `stamp` timestamp NOT NULL,
75 `description` varchar(60) DEFAULT NULL,
76 `fiscal_year` int(11) NOT NULL default 0,
77 `gl_date` date NOT NULL DEFAULT '0000-00-00',
78 `gl_seq` int(11) unsigned DEFAULT NULL,
80 KEY `Seq` (`fiscal_year`,`gl_date`,`gl_seq`),
81 KEY `Type_and_Number` (`type`,`trans_no`)
82 ) ENGINE=InnoDB AUTO_INCREMENT=29 ;
85 -- Dumping data for table `0_audit_trail`
88 INSERT INTO `0_audit_trail` VALUES
89 ('1', '18', '1', '1', '2017-05-05 14:08:02', NULL, '1', '2017-05-05', '0'),
90 ('2', '25', '1', '1', '2017-05-05 14:08:14', NULL, '1', '2017-05-05', '0'),
91 ('3', '30', '1', '1', '2017-05-05 14:09:54', NULL, '1', '2017-05-10', '0'),
92 ('4', '13', '1', '1', '2017-05-05 14:09:55', NULL, '1', '2017-05-10', '0'),
93 ('5', '10', '1', '1', '2017-05-05 14:09:55', NULL, '1', '2017-05-10', '0'),
94 ('6', '12', '1', '1', '2017-05-05 14:09:55', NULL, '1', '2017-05-10', '0'),
95 ('7', '29', '1', '1', '2017-05-05 14:18:49', 'Quick production.', '1', '2017-05-05', '0'),
96 ('8', '18', '2', '1', '2017-05-05 14:22:32', NULL, '1', '2017-05-05', '0'),
97 ('9', '25', '2', '1', '2017-05-05 14:22:32', NULL, '1', '2017-05-05', '0'),
98 ('10', '20', '1', '1', '2017-05-05 14:22:32', NULL, '1', '2017-05-05', '0'),
99 ('11', '30', '2', '1', '2017-05-07 07:55:15', NULL, '1', '2017-05-07', '0'),
100 ('12', '13', '2', '1', '2017-05-07 07:55:16', NULL, '1', '2017-05-07', '0'),
101 ('13', '10', '2', '1', '2017-05-07 07:55:16', NULL, '1', '2017-05-07', '0'),
102 ('14', '12', '2', '1', '2017-05-07 07:55:16', NULL, '1', '2017-05-07', '0'),
103 ('15', '30', '3', '1', '2017-05-07 08:08:24', NULL, '1', '2017-05-07', '0'),
104 ('16', '30', '4', '1', '2017-05-07 09:18:44', NULL, '1', '2017-05-07', '0'),
105 ('17', '30', '5', '1', '2017-05-07 11:42:41', NULL, '1', '2017-05-07', '0'),
106 ('18', '13', '3', '1', '2017-05-07 11:42:41', NULL, '1', '2017-05-07', '0'),
107 ('19', '10', '3', '1', '2017-05-07 11:42:41', NULL, '1', '2017-05-07', '0'),
108 ('20', '30', '6', '1', '2017-05-07 14:02:35', NULL, '1', '2017-05-07', '0'),
109 ('21', '30', '7', '1', '2017-05-07 14:05:38', NULL, '1', '2017-05-07', '0'),
110 ('22', '13', '4', '1', '2017-05-07 14:05:38', NULL, '1', '2017-05-07', '0'),
111 ('23', '10', '4', '1', '2017-05-07 14:05:38', NULL, '1', '2017-05-07', '0'),
112 ('24', '12', '3', '1', '2017-05-07 14:05:38', NULL, '1', '2017-05-07', '0'),
113 ('25', '26', '1', '1', '2017-05-07 15:59:34', NULL, '1', '2017-05-07', NULL),
114 ('26', '29', '1', '1', '2017-05-07 15:59:01', 'Production.', '1', '2017-05-07', '0'),
115 ('27', '26', '1', '1', '2017-05-07 15:59:34', 'Released.', '1', '2017-05-07', '0'),
116 ('28', '1', '1', '1', '2017-05-07 16:01:00', NULL, '1', '2017-05-07', '0');
118 -- --------------------------------------------------------
121 -- Table structure for table `0_bank_accounts`
124 DROP TABLE IF EXISTS `0_bank_accounts`;
125 CREATE TABLE IF NOT EXISTS `0_bank_accounts` (
126 `account_code` varchar(15) NOT NULL DEFAULT '',
127 `account_type` smallint(6) NOT NULL DEFAULT '0',
128 `bank_account_name` varchar(60) NOT NULL DEFAULT '',
129 `bank_account_number` varchar(100) NOT NULL DEFAULT '',
130 `bank_name` varchar(60) NOT NULL DEFAULT '',
131 `bank_address` tinytext,
132 `bank_curr_code` char(3) NOT NULL DEFAULT '',
133 `dflt_curr_act` tinyint(1) NOT NULL DEFAULT '0',
134 `id` smallint(6) NOT NULL AUTO_INCREMENT,
135 `bank_charge_act` varchar(15) NOT NULL DEFAULT '',
136 `last_reconciled_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
137 `ending_reconcile_balance` double NOT NULL DEFAULT '0',
138 `inactive` tinyint(1) NOT NULL DEFAULT '0',
140 KEY `bank_account_name` (`bank_account_name`),
141 KEY `bank_account_number` (`bank_account_number`),
142 KEY `account_code` (`account_code`)
143 ) ENGINE=InnoDB AUTO_INCREMENT=3 ;
146 -- Dumping data for table `0_bank_accounts`
149 INSERT INTO `0_bank_accounts` VALUES ('1060', 0, 'Current account', 'N/A', 'N/A', '', 'USD', 1, 1, '5690', '0000-00-00 00:00:00', 0, 0);
150 INSERT INTO `0_bank_accounts` VALUES ('1065', 3, 'Petty Cash account', 'N/A', 'N/A', '', 'USD', 0, 2, '5690', '0000-00-00 00:00:00', 0, 0);
152 -- --------------------------------------------------------
155 -- Table structure for table `0_bank_trans`
158 DROP TABLE IF EXISTS `0_bank_trans`;
159 CREATE TABLE IF NOT EXISTS `0_bank_trans` (
160 `id` int(11) NOT NULL AUTO_INCREMENT,
161 `type` smallint(6) DEFAULT NULL,
162 `trans_no` int(11) DEFAULT NULL,
163 `bank_act` varchar(15) NOT NULL DEFAULT '',
164 `ref` varchar(40) DEFAULT NULL,
165 `trans_date` date NOT NULL DEFAULT '0000-00-00',
166 `amount` double DEFAULT NULL,
167 `dimension_id` int(11) NOT NULL DEFAULT '0',
168 `dimension2_id` int(11) NOT NULL DEFAULT '0',
169 `person_type_id` int(11) NOT NULL DEFAULT '0',
170 `person_id` tinyblob,
171 `reconciled` date DEFAULT NULL,
173 KEY `bank_act` (`bank_act`,`ref`),
174 KEY `type` (`type`,`trans_no`),
175 KEY `bank_act_2` (`bank_act`,`reconciled`),
176 KEY `bank_act_3` (`bank_act`,`trans_date`)
177 ) ENGINE=InnoDB AUTO_INCREMENT=5 ;
180 -- Dumping data for table `0_bank_trans`
183 INSERT INTO `0_bank_trans` VALUES
184 ('1', '12', '1', '2', '001/2017', '2017-05-10', '6240', '0', '0', '2', '1', NULL),
185 ('2', '12', '2', '2', '002/2017', '2017-05-07', '300', '0', '0', '2', '1', NULL),
186 ('3', '12', '3', '2', '003/2017', '2017-05-07', '0', '0', '0', '2', '1', NULL),
187 ('4', '1', '1', '1', '001/2017', '2017-05-07', '-5', '0', '0', '0', 'Goods received', NULL);
189 -- --------------------------------------------------------
192 -- Table structure for table `0_bom`
195 DROP TABLE IF EXISTS `0_bom`;
196 CREATE TABLE IF NOT EXISTS `0_bom` (
197 `id` int(11) NOT NULL AUTO_INCREMENT,
198 `parent` char(20) NOT NULL DEFAULT '',
199 `component` char(20) NOT NULL DEFAULT '',
200 `workcentre_added` int(11) NOT NULL DEFAULT '0',
201 `loc_code` char(5) NOT NULL DEFAULT '',
202 `quantity` double NOT NULL DEFAULT '1',
203 PRIMARY KEY (`parent`,`component`,`workcentre_added`,`loc_code`),
204 KEY `component` (`component`),
206 KEY `loc_code` (`loc_code`),
207 KEY `parent` (`parent`,`loc_code`),
208 KEY `workcentre_added` (`workcentre_added`)
209 ) ENGINE=InnoDB AUTO_INCREMENT=5 ;
212 -- Dumping data for table `0_bom`
215 INSERT INTO `0_bom` VALUES
216 ('1', '201', '101', '1', 'DEF', '1'),
217 ('2', '201', '102', '1', 'DEF', '1'),
218 ('3', '201', '103', '1', 'DEF', '1'),
219 ('4', '201', '301', '1', 'DEF', '1');
221 -- --------------------------------------------------------
224 -- Table structure for table `0_budget_trans`
227 DROP TABLE IF EXISTS `0_budget_trans`;
228 CREATE TABLE IF NOT EXISTS `0_budget_trans` (
229 `id` int(11) NOT NULL AUTO_INCREMENT,
230 `tran_date` date NOT NULL DEFAULT '0000-00-00',
231 `account` varchar(15) NOT NULL DEFAULT '',
232 `memo_` tinytext NOT NULL,
233 `amount` double NOT NULL DEFAULT '0',
234 `dimension_id` int(11) DEFAULT '0',
235 `dimension2_id` int(11) DEFAULT '0',
237 KEY `Account` (`account`,`tran_date`,`dimension_id`,`dimension2_id`)
241 -- Dumping data for table `0_budget_trans`
244 -- --------------------------------------------------------
247 -- Table structure for table `0_chart_class`
250 DROP TABLE IF EXISTS `0_chart_class`;
251 CREATE TABLE IF NOT EXISTS `0_chart_class` (
252 `cid` varchar(3) NOT NULL,
253 `class_name` varchar(60) NOT NULL DEFAULT '',
254 `ctype` tinyint(1) NOT NULL DEFAULT '0',
255 `inactive` tinyint(1) NOT NULL DEFAULT '0',
260 -- Dumping data for table `0_chart_class`
263 INSERT INTO `0_chart_class` VALUES ('1', 'Assets', 1, 0);
264 INSERT INTO `0_chart_class` VALUES ('2', 'Liabilities', 2, 0);
265 INSERT INTO `0_chart_class` VALUES ('3', 'Income', 4, 0);
266 INSERT INTO `0_chart_class` VALUES ('4', 'Costs', 6, 0);
268 -- --------------------------------------------------------
271 -- Table structure for table `0_chart_master`
274 DROP TABLE IF EXISTS `0_chart_master`;
275 CREATE TABLE IF NOT EXISTS `0_chart_master` (
276 `account_code` varchar(15) NOT NULL DEFAULT '',
277 `account_code2` varchar(15) NOT NULL DEFAULT '',
278 `account_name` varchar(60) NOT NULL DEFAULT '',
279 `account_type` varchar(10) NOT NULL DEFAULT '0',
280 `inactive` tinyint(1) NOT NULL DEFAULT '0',
281 PRIMARY KEY (`account_code`),
282 KEY `account_name` (`account_name`),
283 KEY `accounts_by_type` (`account_type`,`account_code`)
287 -- Dumping data for table `0_chart_master`
290 INSERT INTO `0_chart_master` VALUES ('1060', '', 'Checking Account', '1', 0);
291 INSERT INTO `0_chart_master` VALUES ('1065', '', 'Petty Cash', '1', 0);
292 INSERT INTO `0_chart_master` VALUES ('1200', '', 'Accounts Receivables', '1', 0);
293 INSERT INTO `0_chart_master` VALUES ('1205', '', 'Allowance for doubtful accounts', '1', 0);
294 INSERT INTO `0_chart_master` VALUES ('1510', '', 'Inventory', '2', 0);
295 INSERT INTO `0_chart_master` VALUES ('1520', '', 'Stocks of Raw Materials', '2', 0);
296 INSERT INTO `0_chart_master` VALUES ('1530', '', 'Stocks of Work In Progress', '2', 0);
297 INSERT INTO `0_chart_master` VALUES ('1540', '', 'Stocks of Finished Goods', '2', 0);
298 INSERT INTO `0_chart_master` VALUES ('1550', '', 'Goods Received Clearing account', '2', 0);
299 INSERT INTO `0_chart_master` VALUES ('1820', '', 'Office Furniture & Equipment', '3', 0);
300 INSERT INTO `0_chart_master` VALUES ('1825', '', 'Accum. Amort. -Furn. & Equip.', '3', 0);
301 INSERT INTO `0_chart_master` VALUES ('1840', '', 'Vehicle', '3', 0);
302 INSERT INTO `0_chart_master` VALUES ('1845', '', 'Accum. Amort. -Vehicle', '3', 0);
303 INSERT INTO `0_chart_master` VALUES ('2100', '', 'Accounts Payable', '4', 0);
304 INSERT INTO `0_chart_master` VALUES ('2105', '', 'Deferred Income', '4', 0);
305 INSERT INTO `0_chart_master` VALUES ('2110', '', 'Accrued Income Tax - Federal', '4', 0);
306 INSERT INTO `0_chart_master` VALUES ('2120', '', 'Accrued Income Tax - State', '4', 0);
307 INSERT INTO `0_chart_master` VALUES ('2130', '', 'Accrued Franchise Tax', '4', 0);
308 INSERT INTO `0_chart_master` VALUES ('2140', '', 'Accrued Real & Personal Prop Tax', '4', 0);
309 INSERT INTO `0_chart_master` VALUES ('2150', '', 'Sales Tax', '4', 0);
310 INSERT INTO `0_chart_master` VALUES ('2160', '', 'Accrued Use Tax Payable', '4', 0);
311 INSERT INTO `0_chart_master` VALUES ('2210', '', 'Accrued Wages', '4', 0);
312 INSERT INTO `0_chart_master` VALUES ('2220', '', 'Accrued Comp Time', '4', 0);
313 INSERT INTO `0_chart_master` VALUES ('2230', '', 'Accrued Holiday Pay', '4', 0);
314 INSERT INTO `0_chart_master` VALUES ('2240', '', 'Accrued Vacation Pay', '4', 0);
315 INSERT INTO `0_chart_master` VALUES ('2310', '', 'Accr. Benefits - 401K', '4', 0);
316 INSERT INTO `0_chart_master` VALUES ('2320', '', 'Accr. Benefits - Stock Purchase', '4', 0);
317 INSERT INTO `0_chart_master` VALUES ('2330', '', 'Accr. Benefits - Med, Den', '4', 0);
318 INSERT INTO `0_chart_master` VALUES ('2340', '', 'Accr. Benefits - Payroll Taxes', '4', 0);
319 INSERT INTO `0_chart_master` VALUES ('2350', '', 'Accr. Benefits - Credit Union', '4', 0);
320 INSERT INTO `0_chart_master` VALUES ('2360', '', 'Accr. Benefits - Savings Bond', '4', 0);
321 INSERT INTO `0_chart_master` VALUES ('2370', '', 'Accr. Benefits - Garnish', '4', 0);
322 INSERT INTO `0_chart_master` VALUES ('2380', '', 'Accr. Benefits - Charity Cont.', '4', 0);
323 INSERT INTO `0_chart_master` VALUES ('2620', '', 'Bank Loans', '5', 0);
324 INSERT INTO `0_chart_master` VALUES ('2680', '', 'Loans from Shareholders', '5', 0);
325 INSERT INTO `0_chart_master` VALUES ('3350', '', 'Common Shares', '6', 0);
326 INSERT INTO `0_chart_master` VALUES ('3590', '', 'Retained Earnings - prior years', '7', 0);
327 INSERT INTO `0_chart_master` VALUES ('4010', '', 'Sales', '8', 0);
328 INSERT INTO `0_chart_master` VALUES ('4430', '', 'Shipping & Handling', '9', 0);
329 INSERT INTO `0_chart_master` VALUES ('4440', '', 'Interest', '9', 0);
330 INSERT INTO `0_chart_master` VALUES ('4450', '', 'Foreign Exchange Gain', '9', 0);
331 INSERT INTO `0_chart_master` VALUES ('4500', '', 'Prompt Payment Discounts', '9', 0);
332 INSERT INTO `0_chart_master` VALUES ('4510', '', 'Discounts Given', '9', 0);
333 INSERT INTO `0_chart_master` VALUES ('5010', '', 'Cost of Goods Sold - Retail', '10', 0);
334 INSERT INTO `0_chart_master` VALUES ('5020', '', 'Material Usage Varaiance', '10', 0);
335 INSERT INTO `0_chart_master` VALUES ('5030', '', 'Consumable Materials', '10', 0);
336 INSERT INTO `0_chart_master` VALUES ('5040', '', 'Purchase price Variance', '10', 0);
337 INSERT INTO `0_chart_master` VALUES ('5050', '', 'Purchases of materials', '10', 0);
338 INSERT INTO `0_chart_master` VALUES ('5060', '', 'Discounts Received', '10', 0);
339 INSERT INTO `0_chart_master` VALUES ('5100', '', 'Freight', '10', 0);
340 INSERT INTO `0_chart_master` VALUES ('5410', '', 'Wages & Salaries', '11', 0);
341 INSERT INTO `0_chart_master` VALUES ('5420', '', 'Wages - Overtime', '11', 0);
342 INSERT INTO `0_chart_master` VALUES ('5430', '', 'Benefits - Comp Time', '11', 0);
343 INSERT INTO `0_chart_master` VALUES ('5440', '', 'Benefits - Payroll Taxes', '11', 0);
344 INSERT INTO `0_chart_master` VALUES ('5450', '', 'Benefits - Workers Comp', '11', 0);
345 INSERT INTO `0_chart_master` VALUES ('5460', '', 'Benefits - Pension', '11', 0);
346 INSERT INTO `0_chart_master` VALUES ('5470', '', 'Benefits - General Benefits', '11', 0);
347 INSERT INTO `0_chart_master` VALUES ('5510', '', 'Inc Tax Exp - Federal', '11', 0);
348 INSERT INTO `0_chart_master` VALUES ('5520', '', 'Inc Tax Exp - State', '11', 0);
349 INSERT INTO `0_chart_master` VALUES ('5530', '', 'Taxes - Real Estate', '11', 0);
350 INSERT INTO `0_chart_master` VALUES ('5540', '', 'Taxes - Personal Property', '11', 0);
351 INSERT INTO `0_chart_master` VALUES ('5550', '', 'Taxes - Franchise', '11', 0);
352 INSERT INTO `0_chart_master` VALUES ('5560', '', 'Taxes - Foreign Withholding', '11', 0);
353 INSERT INTO `0_chart_master` VALUES ('5610', '', 'Accounting & Legal', '12', 0);
354 INSERT INTO `0_chart_master` VALUES ('5615', '', 'Advertising & Promotions', '12', 0);
355 INSERT INTO `0_chart_master` VALUES ('5620', '', 'Bad Debts', '12', 0);
356 INSERT INTO `0_chart_master` VALUES ('5660', '', 'Amortization Expense', '12', 0);
357 INSERT INTO `0_chart_master` VALUES ('5685', '', 'Insurance', '12', 0);
358 INSERT INTO `0_chart_master` VALUES ('5690', '', 'Interest & Bank Charges', '12', 0);
359 INSERT INTO `0_chart_master` VALUES ('5700', '', 'Office Supplies', '12', 0);
360 INSERT INTO `0_chart_master` VALUES ('5760', '', 'Rent', '12', 0);
361 INSERT INTO `0_chart_master` VALUES ('5765', '', 'Repair & Maintenance', '12', 0);
362 INSERT INTO `0_chart_master` VALUES ('5780', '', 'Telephone', '12', 0);
363 INSERT INTO `0_chart_master` VALUES ('5785', '', 'Travel & Entertainment', '12', 0);
364 INSERT INTO `0_chart_master` VALUES ('5790', '', 'Utilities', '12', 0);
365 INSERT INTO `0_chart_master` VALUES ('5795', '', 'Registrations', '12', 0);
366 INSERT INTO `0_chart_master` VALUES ('5800', '', 'Licenses', '12', 0);
367 INSERT INTO `0_chart_master` VALUES ('5810', '', 'Foreign Exchange Loss', '12', 0);
368 INSERT INTO `0_chart_master` VALUES ('9990', '', 'Year Profit/Loss', '12', 0);
370 -- --------------------------------------------------------
373 -- Table structure for table `0_chart_types`
376 DROP TABLE IF EXISTS `0_chart_types`;
377 CREATE TABLE IF NOT EXISTS `0_chart_types` (
378 `id` varchar(10) NOT NULL,
379 `name` varchar(60) NOT NULL DEFAULT '',
380 `class_id` varchar(3) NOT NULL DEFAULT '',
381 `parent` varchar(10) NOT NULL DEFAULT '-1',
382 `inactive` tinyint(1) NOT NULL DEFAULT '0',
385 KEY `class_id` (`class_id`)
389 -- Dumping data for table `0_chart_types`
392 INSERT INTO `0_chart_types` VALUES ('1', 'Current Assets', '1', '', 0);
393 INSERT INTO `0_chart_types` VALUES ('2', 'Inventory Assets', '1', '', 0);
394 INSERT INTO `0_chart_types` VALUES ('3', 'Capital Assets', '1', '', 0);
395 INSERT INTO `0_chart_types` VALUES ('4', 'Current Liabilities', '2', '', 0);
396 INSERT INTO `0_chart_types` VALUES ('5', 'Long Term Liabilities', '2', '', 0);
397 INSERT INTO `0_chart_types` VALUES ('6', 'Share Capital', '2', '', 0);
398 INSERT INTO `0_chart_types` VALUES ('7', 'Retained Earnings', '2', '', 0);
399 INSERT INTO `0_chart_types` VALUES ('8', 'Sales Revenue', '3', '', 0);
400 INSERT INTO `0_chart_types` VALUES ('9', 'Other Revenue', '3', '', 0);
401 INSERT INTO `0_chart_types` VALUES ('10', 'Cost of Goods Sold', '4', '', 0);
402 INSERT INTO `0_chart_types` VALUES ('11', 'Payroll Expenses', '4', '', 0);
403 INSERT INTO `0_chart_types` VALUES ('12', 'General & Administrative expenses', '4', '', 0);
405 -- --------------------------------------------------------
408 -- Table structure for table `0_comments`
411 DROP TABLE IF EXISTS `0_comments`;
412 CREATE TABLE IF NOT EXISTS `0_comments` (
413 `type` int(11) NOT NULL DEFAULT '0',
414 `id` int(11) NOT NULL DEFAULT '0',
415 `date_` date DEFAULT '0000-00-00',
417 KEY `type_and_id` (`type`,`id`)
421 -- Dumping data for table `0_comments`
424 INSERT INTO `0_comments` VALUES
425 ('12', '1', '2017-05-10', 'Cash invoice 1'),
426 ('12', '2', '2017-05-07', 'Cash invoice 2'),
427 ('13', '4', '2017-05-07', 'Recurrent Invoice covers period 04/01/2017 - 04/07/2017.'),
428 ('10', '4', '2017-05-07', 'Recurrent Invoice covers period 04/01/2017 - 04/07/2017.'),
429 ('12', '3', '2017-05-07', 'Cash invoice 4');
431 -- --------------------------------------------------------
434 -- Table structure for table `0_credit_status`
437 DROP TABLE IF EXISTS `0_credit_status`;
438 CREATE TABLE IF NOT EXISTS `0_credit_status` (
439 `id` int(11) NOT NULL AUTO_INCREMENT,
440 `reason_description` char(100) NOT NULL DEFAULT '',
441 `dissallow_invoices` tinyint(1) NOT NULL DEFAULT '0',
442 `inactive` tinyint(1) NOT NULL DEFAULT '0',
444 UNIQUE KEY `reason_description` (`reason_description`)
445 ) ENGINE=InnoDB AUTO_INCREMENT=5 ;
448 -- Dumping data for table `0_credit_status`
451 INSERT INTO `0_credit_status` VALUES (1, 'Good History', 0, 0);
452 INSERT INTO `0_credit_status` VALUES (3, 'No more work until payment received', 1, 0);
453 INSERT INTO `0_credit_status` VALUES (4, 'In liquidation', 1, 0);
455 -- --------------------------------------------------------
458 -- Table structure for table `0_crm_categories`
461 DROP TABLE IF EXISTS `0_crm_categories`;
462 CREATE TABLE IF NOT EXISTS `0_crm_categories` (
463 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'pure technical key',
464 `type` varchar(20) NOT NULL COMMENT 'contact type e.g. customer',
465 `action` varchar(20) NOT NULL COMMENT 'detailed usage e.g. department',
466 `name` varchar(30) NOT NULL COMMENT 'for category selector',
467 `description` tinytext NOT NULL COMMENT 'usage description',
468 `system` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'nonzero for core system usage',
469 `inactive` tinyint(1) NOT NULL DEFAULT '0',
471 UNIQUE KEY `type` (`type`,`action`),
472 UNIQUE KEY `type_2` (`type`,`name`)
473 ) ENGINE=InnoDB AUTO_INCREMENT=13 ;
476 -- Dumping data for table `0_crm_categories`
479 INSERT INTO `0_crm_categories` VALUES (1, 'cust_branch', 'general', 'General', 'General contact data for customer branch (overrides company setting)', 1, 0);
480 INSERT INTO `0_crm_categories` VALUES (2, 'cust_branch', 'invoice', 'Invoices', 'Invoice posting (overrides company setting)', 1, 0);
481 INSERT INTO `0_crm_categories` VALUES (3, 'cust_branch', 'order', 'Orders', 'Order confirmation (overrides company setting)', 1, 0);
482 INSERT INTO `0_crm_categories` VALUES (4, 'cust_branch', 'delivery', 'Deliveries', 'Delivery coordination (overrides company setting)', 1, 0);
483 INSERT INTO `0_crm_categories` VALUES (5, 'customer', 'general', 'General', 'General contact data for customer', 1, 0);
484 INSERT INTO `0_crm_categories` VALUES (6, 'customer', 'order', 'Orders', 'Order confirmation', 1, 0);
485 INSERT INTO `0_crm_categories` VALUES (7, 'customer', 'delivery', 'Deliveries', 'Delivery coordination', 1, 0);
486 INSERT INTO `0_crm_categories` VALUES (8, 'customer', 'invoice', 'Invoices', 'Invoice posting', 1, 0);
487 INSERT INTO `0_crm_categories` VALUES (9, 'supplier', 'general', 'General', 'General contact data for supplier', 1, 0);
488 INSERT INTO `0_crm_categories` VALUES (10, 'supplier', 'order', 'Orders', 'Order confirmation', 1, 0);
489 INSERT INTO `0_crm_categories` VALUES (11, 'supplier', 'delivery', 'Deliveries', 'Delivery coordination', 1, 0);
490 INSERT INTO `0_crm_categories` VALUES (12, 'supplier', 'invoice', 'Invoices', 'Invoice posting', 1, 0);
492 -- --------------------------------------------------------
495 -- Table structure for table `0_crm_contacts`
498 DROP TABLE IF EXISTS `0_crm_contacts`;
499 CREATE TABLE IF NOT EXISTS `0_crm_contacts` (
500 `id` int(11) NOT NULL AUTO_INCREMENT,
501 `person_id` int(11) NOT NULL DEFAULT '0' COMMENT 'foreign key to crm_contacts',
502 `type` varchar(20) NOT NULL COMMENT 'foreign key to crm_categories',
503 `action` varchar(20) NOT NULL COMMENT 'foreign key to crm_categories',
504 `entity_id` varchar(11) DEFAULT NULL COMMENT 'entity id in related class table',
506 KEY `type` (`type`,`action`)
507 ) ENGINE=InnoDB AUTO_INCREMENT=11 ;
510 -- Dumping data for table `0_crm_contacts`
513 INSERT INTO `0_crm_contacts` VALUES
514 ('4', '2', 'supplier', 'general', '2'),
515 ('5', '3', 'cust_branch', 'general', '1'),
516 ('7', '3', 'customer', 'general', '1'),
517 ('8', '1', 'supplier', 'general', '1'),
518 ('9', '4', 'cust_branch', 'general', '2'),
519 ('10', '4', 'customer', 'general', '2');
521 -- --------------------------------------------------------
524 -- Table structure for table `0_crm_persons`
527 DROP TABLE IF EXISTS `0_crm_persons`;
528 CREATE TABLE IF NOT EXISTS `0_crm_persons` (
529 `id` int(11) NOT NULL AUTO_INCREMENT,
530 `ref` varchar(30) NOT NULL,
531 `name` varchar(60) NOT NULL,
532 `name2` varchar(60) DEFAULT NULL,
534 `phone` varchar(30) DEFAULT NULL,
535 `phone2` varchar(30) DEFAULT NULL,
536 `fax` varchar(30) DEFAULT NULL,
537 `email` varchar(100) DEFAULT NULL,
538 `lang` char(5) DEFAULT NULL,
539 `notes` tinytext NOT NULL,
540 `inactive` tinyint(1) NOT NULL DEFAULT '0',
543 ) ENGINE=InnoDB AUTO_INCREMENT=5 ;
546 -- Dumping data for table `0_crm_persons`
549 INSERT INTO `0_crm_persons` VALUES
550 ('1', 'Dino Saurius', 'John Doe', NULL, 'N/A', NULL, NULL, NULL, NULL, NULL, '', '0'),
551 ('2', 'Beefeater', 'Joe Oversea', NULL, 'N/A', NULL, NULL, NULL, NULL, NULL, '', '0'),
552 ('3', 'Donald Easter', 'Donald Easter LLC', NULL, 'N/A', NULL, NULL, NULL, NULL, NULL, '', '0'),
553 ('4', 'MoneyMaker', 'MoneyMaker Ltd.', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', '0');
555 -- --------------------------------------------------------
558 -- Table structure for table `0_currencies`
561 DROP TABLE IF EXISTS `0_currencies`;
562 CREATE TABLE IF NOT EXISTS `0_currencies` (
563 `currency` varchar(60) NOT NULL DEFAULT '',
564 `curr_abrev` char(3) NOT NULL DEFAULT '',
565 `curr_symbol` varchar(10) NOT NULL DEFAULT '',
566 `country` varchar(100) NOT NULL DEFAULT '',
567 `hundreds_name` varchar(15) NOT NULL DEFAULT '',
568 `auto_update` tinyint(1) NOT NULL DEFAULT '1',
569 `inactive` tinyint(1) NOT NULL DEFAULT '0',
570 PRIMARY KEY (`curr_abrev`)
574 -- Dumping data for table `0_currencies`
577 INSERT INTO `0_currencies` VALUES ('US Dollars', 'USD', '$', 'United States', 'Cents', 1, 0);
578 INSERT INTO `0_currencies` VALUES ('CA Dollars', 'CAD', '$', 'Canada', 'Cents', 1, 0);
579 INSERT INTO `0_currencies` VALUES ('Euro', 'EUR', '€', 'Europe', 'Cents', 1, 0);
580 INSERT INTO `0_currencies` VALUES ('Pounds', 'GBP', '£', 'England', 'Pence', 1, 0);
582 -- --------------------------------------------------------
585 -- Table structure for table `0_cust_allocations`
588 DROP TABLE IF EXISTS `0_cust_allocations`;
589 CREATE TABLE IF NOT EXISTS `0_cust_allocations` (
590 `id` int(11) NOT NULL AUTO_INCREMENT,
591 `person_id` int(11) DEFAULT NULL,
592 `amt` double unsigned DEFAULT NULL,
593 `date_alloc` date NOT NULL DEFAULT '0000-00-00',
594 `trans_no_from` int(11) DEFAULT NULL,
595 `trans_type_from` int(11) DEFAULT NULL,
596 `trans_no_to` int(11) DEFAULT NULL,
597 `trans_type_to` int(11) DEFAULT NULL,
599 UNIQUE KEY `trans_type_from` (`person_id`,`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`),
600 KEY `From` (`trans_type_from`,`trans_no_from`),
601 KEY `To` (`trans_type_to`,`trans_no_to`)
602 ) ENGINE=InnoDB AUTO_INCREMENT=4 ;
605 -- Dumping data for table `0_cust_allocations`
608 INSERT INTO `0_cust_allocations` VALUES
609 ('1', '1', '6240', '2017-05-10', '1', '12', '1', '10'),
610 ('2', '1', '300', '2017-05-07', '2', '12', '2', '10'),
611 ('3', '1', '0', '2017-05-07', '3', '12', '4', '10');
613 -- --------------------------------------------------------
616 -- Table structure for table `0_cust_branch`
619 DROP TABLE IF EXISTS `0_cust_branch`;
620 CREATE TABLE IF NOT EXISTS `0_cust_branch` (
621 `branch_code` int(11) NOT NULL AUTO_INCREMENT,
622 `debtor_no` int(11) NOT NULL DEFAULT '0',
623 `br_name` varchar(60) NOT NULL DEFAULT '',
624 `branch_ref` varchar(30) NOT NULL DEFAULT '',
625 `br_address` tinytext NOT NULL,
626 `area` int(11) DEFAULT NULL,
627 `salesman` int(11) NOT NULL DEFAULT '0',
628 `default_location` varchar(5) NOT NULL DEFAULT '',
629 `tax_group_id` int(11) DEFAULT NULL,
630 `sales_account` varchar(15) NOT NULL DEFAULT '',
631 `sales_discount_account` varchar(15) NOT NULL DEFAULT '',
632 `receivables_account` varchar(15) NOT NULL DEFAULT '',
633 `payment_discount_account` varchar(15) NOT NULL DEFAULT '',
634 `default_ship_via` int(11) NOT NULL DEFAULT '1',
635 `br_post_address` tinytext NOT NULL,
636 `group_no` int(11) NOT NULL DEFAULT '0',
637 `notes` tinytext NOT NULL,
638 `bank_account` varchar(60) DEFAULT NULL,
639 `inactive` tinyint(1) NOT NULL DEFAULT '0',
640 PRIMARY KEY (`branch_code`,`debtor_no`),
641 KEY `branch_ref` (`branch_ref`),
642 KEY `group_no` (`group_no`)
643 ) ENGINE=InnoDB AUTO_INCREMENT=3 ;
646 -- Dumping data for table `0_cust_branch`
649 INSERT INTO `0_cust_branch` VALUES
650 ('1', '1', 'Donald Easter LLC', 'Donald Easter', 'N/A', '1', '1', 'DEF', '1', '', '4510', '1200', '4500', '1', 'N/A', '0', '', NULL, '0'),
651 ('2', '2', 'MoneyMaker Ltd.', 'MoneyMaker', '', '1', '1', 'DEF', '2', '', '4510', '1200', '4500', '1', '', '0', '', NULL, '0');
653 -- --------------------------------------------------------
656 -- Table structure for table `0_debtors_master`
659 DROP TABLE IF EXISTS `0_debtors_master`;
660 CREATE TABLE IF NOT EXISTS `0_debtors_master` (
661 `debtor_no` int(11) NOT NULL auto_increment,
662 `name` varchar(100) NOT NULL default '',
663 `debtor_ref` varchar(30) NOT NULL,
665 `tax_id` varchar(55) NOT NULL default '',
666 `curr_code` char(3) NOT NULL default '',
667 `sales_type` int(11) NOT NULL default '1',
668 `dimension_id` int(11) NOT NULL default '0',
669 `dimension2_id` int(11) NOT NULL default '0',
670 `credit_status` int(11) NOT NULL default '0',
671 `payment_terms` int(11) default NULL,
672 `discount` double NOT NULL default '0',
673 `pymt_discount` double NOT NULL default '0',
674 `credit_limit` float NOT NULL default '1000',
675 `notes` tinytext NOT NULL,
676 `inactive` tinyint(1) NOT NULL default '0',
677 PRIMARY KEY (`debtor_no`),
679 UNIQUE KEY `debtor_ref` (`debtor_ref`)
683 -- Dumping data for table `0_debtors_master`
687 INSERT INTO `0_debtors_master` VALUES
688 ('1', 'Donald Easter LLC', 'Donald Easter', 'N/A', '123456789', 'USD', '1', '0', '0', '1', '4', '0', '0', '1000', '', '0'),
689 ('2', 'MoneyMaker Ltd.', 'MoneyMaker', 'N/A', '54354333', 'EUR', '1', '1', '0', '1', '1', '0', '0', '1000', '', '0');
691 -- --------------------------------------------------------
694 -- Table structure for table `0_debtor_trans`
697 DROP TABLE IF EXISTS `0_debtor_trans`;
698 CREATE TABLE IF NOT EXISTS `0_debtor_trans` (
699 `trans_no` int(11) unsigned NOT NULL DEFAULT '0',
700 `type` smallint(6) unsigned NOT NULL DEFAULT '0',
701 `version` tinyint(1) unsigned NOT NULL DEFAULT '0',
702 `debtor_no` int(11) unsigned NOT NULL,
703 `branch_code` int(11) NOT NULL DEFAULT '-1',
704 `tran_date` date NOT NULL DEFAULT '0000-00-00',
705 `due_date` date NOT NULL DEFAULT '0000-00-00',
706 `reference` varchar(60) NOT NULL DEFAULT '',
707 `tpe` int(11) NOT NULL DEFAULT '0',
708 `order_` int(11) NOT NULL DEFAULT '0',
709 `ov_amount` double NOT NULL DEFAULT '0',
710 `ov_gst` double NOT NULL DEFAULT '0',
711 `ov_freight` double NOT NULL DEFAULT '0',
712 `ov_freight_tax` double NOT NULL DEFAULT '0',
713 `ov_discount` double NOT NULL DEFAULT '0',
714 `alloc` double NOT NULL DEFAULT '0',
715 `prep_amount` double NOT NULL DEFAULT '0',
716 `rate` double NOT NULL DEFAULT '1',
717 `ship_via` int(11) DEFAULT NULL,
718 `dimension_id` int(11) NOT NULL DEFAULT '0',
719 `dimension2_id` int(11) NOT NULL DEFAULT '0',
720 `payment_terms` int(11) DEFAULT NULL,
721 `tax_included` tinyint(1) unsigned NOT NULL DEFAULT '0',
722 PRIMARY KEY (`type`,`trans_no`,`debtor_no`),
723 KEY `debtor_no` (`debtor_no`,`branch_code`),
724 KEY `tran_date` (`tran_date`),
725 KEY `order_` (`order_`)
729 -- Dumping data for table `0_debtor_trans`
733 INSERT INTO `0_debtor_trans` VALUES
734 ('1', '10', '0', '1', '1', '2017-05-10', '2017-05-05', '001/2017', '1', '1', '6240', '0', '0', '0', '0', '6240', '0', '1', '1', '0', '0', '4', '1'),
735 ('2', '10', '0', '1', '1', '2017-05-07', '2017-05-07', '002/2017', '1', '2', '300', '0', '0', '0', '0', '300', '0', '1', '1', '0', '0', '4', '1'),
736 ('3', '10', '0', '2', '2', '2017-05-07', '2017-06-17', '003/2017', '1', '5', '267.14', '0', '0', '0', '0', '0', '0', '1.123', '1', '1', '0', '1', '1'),
737 ('4', '10', '0', '1', '1', '2017-05-07', '2017-05-07', '004/2017', '1', '7', '0', '0', '0', '0', '0', '0', '0', '1', '1', '0', '0', '4', '1'),
738 ('1', '12', '0', '1', '1', '2017-05-10', '0000-00-00', '001/2017', '0', '0', '6240', '0', '0', '0', '0', '6240', '0', '1', '0', '0', '0', NULL, '0'),
739 ('2', '12', '0', '1', '1', '2017-05-07', '0000-00-00', '002/2017', '0', '0', '300', '0', '0', '0', '0', '300', '0', '1', '0', '0', '0', NULL, '0'),
740 ('3', '12', '0', '1', '1', '2017-05-07', '0000-00-00', '003/2017', '0', '0', '0', '0', '0', '0', '0', '0', '0', '1', '0', '0', '0', NULL, '0'),
741 ('1', '13', '1', '1', '1', '2017-05-10', '2017-05-05', 'auto', '1', '1', '6240', '0', '0', '0', '0', '0', '0', '1', '1', '0', '0', '4', '1'),
742 ('2', '13', '1', '1', '1', '2017-05-07', '2017-05-07', 'auto', '1', '2', '300', '0', '0', '0', '0', '0', '0', '1', '1', '0', '0', '4', '1'),
743 ('3', '13', '1', '2', '2', '2017-05-07', '2017-06-17', 'auto', '1', '5', '267.14', '0', '0', '0', '0', '0', '0', '1.123', '1', '1', '0', '1', '1'),
744 ('4', '13', '1', '1', '1', '2017-05-07', '2017-05-07', 'auto', '1', '7', '0', '0', '0', '0', '0', '0', '0', '1', '1', '0', '0', '4', '1');
746 -- --------------------------------------------------------
749 -- Table structure for table `0_debtor_trans_details`
752 DROP TABLE IF EXISTS `0_debtor_trans_details`;
753 CREATE TABLE IF NOT EXISTS `0_debtor_trans_details` (
754 `id` int(11) NOT NULL AUTO_INCREMENT,
755 `debtor_trans_no` int(11) DEFAULT NULL,
756 `debtor_trans_type` int(11) DEFAULT NULL,
757 `stock_id` varchar(20) NOT NULL DEFAULT '',
758 `description` tinytext,
759 `unit_price` double NOT NULL DEFAULT '0',
760 `unit_tax` double NOT NULL DEFAULT '0',
761 `quantity` double NOT NULL DEFAULT '0',
762 `discount_percent` double NOT NULL DEFAULT '0',
763 `standard_cost` double NOT NULL DEFAULT '0',
764 `qty_done` double NOT NULL DEFAULT '0',
765 `src_id` int(11) NOT NULL,
767 KEY `Transaction` (`debtor_trans_type`,`debtor_trans_no`),
769 ) ENGINE=InnoDB AUTO_INCREMENT=13 ;
772 -- Dumping data for table `0_debtor_trans_details`
775 INSERT INTO `0_debtor_trans_details` VALUES
776 ('1', '1', '13', '101', 'iPad Air 2 16GB', '300', '14.29', '20', '0', '200', '20', '1'),
777 ('2', '1', '13', '301', 'Support', '80', '3.81', '3', '0', '0', '3', '2'),
778 ('3', '1', '10', '101', 'iPad Air 2 16GB', '300', '14.2855', '20', '0', '200', '0', '1'),
779 ('4', '1', '10', '301', 'Support', '80', '3.81', '3', '0', '0', '0', '2'),
780 ('5', '2', '13', '101', 'iPad Air 2 16GB', '300', '14.29', '1', '0', '200', '1', '3'),
781 ('6', '2', '10', '101', 'iPad Air 2 16GB', '300', '14.29', '1', '0', '200', '0', '5'),
782 ('7', '3', '13', '102', 'iPhone 6 64GB', '222.62', '0', '1', '0', '150', '1', '7'),
783 ('8', '3', '13', '103', 'iPhone Cover Case', '44.52', '0', '1', '0', '10', '1', '8'),
784 ('9', '3', '10', '102', 'iPhone 6 64GB', '222.62', '0', '1', '0', '150', '0', '7'),
785 ('10', '3', '10', '103', 'iPhone Cover Case', '44.52', '0', '1', '0', '10', '0', '8'),
786 ('11', '4', '13', '202', 'Maintenance', '0', '0', '5', '0', '0', '5', '10'),
787 ('12', '4', '10', '202', 'Maintenance', '0', '0', '5', '0', '0', '0', '11');
789 -- --------------------------------------------------------
792 -- Table structure for table `0_dimensions`
795 DROP TABLE IF EXISTS `0_dimensions`;
796 CREATE TABLE IF NOT EXISTS `0_dimensions` (
797 `id` int(11) NOT NULL AUTO_INCREMENT,
798 `reference` varchar(60) NOT NULL DEFAULT '',
799 `name` varchar(60) NOT NULL DEFAULT '',
800 `type_` tinyint(1) NOT NULL DEFAULT '1',
801 `closed` tinyint(1) NOT NULL DEFAULT '0',
802 `date_` date NOT NULL DEFAULT '0000-00-00',
803 `due_date` date NOT NULL DEFAULT '0000-00-00',
805 UNIQUE KEY `reference` (`reference`),
806 KEY `date_` (`date_`),
807 KEY `due_date` (`due_date`),
808 KEY `type_` (`type_`)
809 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
812 -- Dumping data for table `0_dimensions`
815 INSERT INTO `0_dimensions` VALUES ('1', '001/2017', 'Cost Centre', '1', '0', '2017-05-05', '2017-05-25');
817 -- --------------------------------------------------------
820 -- Table structure for table `0_exchange_rates`
823 DROP TABLE IF EXISTS `0_exchange_rates`;
824 CREATE TABLE IF NOT EXISTS `0_exchange_rates` (
825 `id` int(11) NOT NULL AUTO_INCREMENT,
826 `curr_code` char(3) NOT NULL DEFAULT '',
827 `rate_buy` double NOT NULL DEFAULT '0',
828 `rate_sell` double NOT NULL DEFAULT '0',
829 `date_` date NOT NULL DEFAULT '0000-00-00',
831 UNIQUE KEY `curr_code` (`curr_code`,`date_`)
832 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
835 -- Dumping data for table `0_exchange_rates`
838 INSERT INTO `0_exchange_rates` VALUES ('1', 'EUR', '1.123', '1.123', '2017-05-07');
840 -- --------------------------------------------------------
843 -- Table structure for table `0_fiscal_year`
846 DROP TABLE IF EXISTS `0_fiscal_year`;
847 CREATE TABLE IF NOT EXISTS `0_fiscal_year` (
848 `id` int(11) NOT NULL AUTO_INCREMENT,
849 `begin` date DEFAULT '0000-00-00',
850 `end` date DEFAULT '0000-00-00',
851 `closed` tinyint(1) NOT NULL DEFAULT '0',
853 UNIQUE KEY `begin` (`begin`),
854 UNIQUE KEY `end` (`end`)
855 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
858 -- Dumping data for table `0_fiscal_year`
861 INSERT INTO `0_fiscal_year` VALUES (1, '2017-01-01', '2017-12-31', 0);
862 INSERT INTO `0_fiscal_year` VALUES (2, '2018-01-01', '2018-12-31', 0);
864 -- --------------------------------------------------------
867 -- Table structure for table `0_gl_trans`
870 DROP TABLE IF EXISTS `0_gl_trans`;
871 CREATE TABLE IF NOT EXISTS `0_gl_trans` (
872 `counter` int(11) NOT NULL AUTO_INCREMENT,
873 `type` smallint(6) NOT NULL DEFAULT '0',
874 `type_no` int(11) NOT NULL DEFAULT '0',
875 `tran_date` date NOT NULL DEFAULT '0000-00-00',
876 `account` varchar(15) NOT NULL DEFAULT '',
877 `memo_` tinytext NOT NULL,
878 `amount` double NOT NULL DEFAULT '0',
879 `dimension_id` int(11) NOT NULL DEFAULT '0',
880 `dimension2_id` int(11) NOT NULL DEFAULT '0',
881 `person_type_id` int(11) DEFAULT NULL,
882 `person_id` tinyblob,
883 PRIMARY KEY (`counter`),
884 KEY `Type_and_Number` (`type`,`type_no`),
885 KEY `dimension_id` (`dimension_id`),
886 KEY `dimension2_id` (`dimension2_id`),
887 KEY `tran_date` (`tran_date`),
888 KEY `account_and_tran_date` (`account`,`tran_date`)
889 ) ENGINE=InnoDB AUTO_INCREMENT=41 ;
892 -- Dumping data for table `0_gl_trans`
895 INSERT INTO `0_gl_trans` VALUES
896 ('1', '25', '1', '2017-05-05', '1510', '101', '20000', '0', '0', NULL, NULL),
897 ('2', '25', '1', '2017-05-05', '1510', '102', '15000', '0', '0', NULL, NULL),
898 ('3', '25', '1', '2017-05-05', '1510', '103', '1000', '0', '0', NULL, NULL),
899 ('4', '25', '1', '2017-05-05', '1550', '', '-36000', '0', '0', NULL, NULL),
900 ('5', '13', '1', '2017-05-10', '5010', '', '4000', '0', '0', NULL, NULL),
901 ('6', '13', '1', '2017-05-10', '1510', '', '-4000', '0', '0', NULL, NULL),
902 ('7', '10', '1', '2017-05-10', '4010', '', '-5714.29', '0', '0', NULL, NULL),
903 ('8', '10', '1', '2017-05-10', '4010', '', '-228.57', '0', '0', NULL, NULL),
904 ('9', '10', '1', '2017-05-10', '1200', '', '6240', '0', '0', '2', '1'),
905 ('10', '10', '1', '2017-05-10', '2150', '', '-297.14', '0', '0', NULL, NULL),
906 ('11', '12', '1', '2017-05-10', '1065', '', '6240', '0', '0', NULL, NULL),
907 ('12', '12', '1', '2017-05-10', '1200', '', '-6240', '0', '0', '2', '1'),
908 ('13', '29', '1', '2017-05-05', '1510', '1 * iPad Air 2 16GB', '-400', '0', '0', NULL, NULL),
909 ('14', '29', '1', '2017-05-05', '1510', '1 * iPhone 6 64GB', '-300', '0', '0', NULL, NULL),
910 ('15', '29', '1', '2017-05-05', '1510', '1 * iPhone Cover Case', '-20', '0', '0', NULL, NULL),
911 ('16', '29', '1', '2017-05-05', '1530', '1 * Support', '720', '0', '0', NULL, NULL),
912 ('17', '26', '1', '2017-05-05', '1530', '', '-720', '0', '0', NULL, NULL),
913 ('18', '26', '1', '2017-05-05', '1510', '', '720', '0', '0', NULL, NULL),
914 ('19', '25', '2', '2017-05-05', '1510', '101', '3000', '0', '0', NULL, NULL),
915 ('20', '25', '2', '2017-05-05', '1550', '', '-3000', '0', '0', NULL, NULL),
916 ('21', '20', '1', '2017-05-05', '2150', '', '150', '0', '0', NULL, NULL),
917 ('22', '20', '1', '2017-05-05', '2100', '', '-3150', '0', '0', '3', '1'),
918 ('23', '20', '1', '2017-05-05', '1550', '', '3000', '0', '0', NULL, NULL),
919 ('24', '13', '2', '2017-05-07', '5010', '', '200', '0', '0', NULL, NULL),
920 ('25', '13', '2', '2017-05-07', '1510', '', '-200', '0', '0', NULL, NULL),
921 ('26', '10', '2', '2017-05-07', '4010', '', '-285.71', '0', '0', NULL, NULL),
922 ('27', '10', '2', '2017-05-07', '1200', '', '300', '0', '0', '2', '1'),
923 ('28', '10', '2', '2017-05-07', '2150', '', '-14.29', '0', '0', NULL, NULL),
924 ('29', '12', '2', '2017-05-07', '1065', '', '300', '0', '0', NULL, NULL),
925 ('30', '12', '2', '2017-05-07', '1200', '', '-300', '0', '0', '2', '1'),
926 ('31', '13', '3', '2017-05-07', '5010', '', '150', '1', '0', NULL, NULL),
927 ('32', '13', '3', '2017-05-07', '1510', '', '-150', '0', '0', NULL, NULL),
928 ('33', '13', '3', '2017-05-07', '5010', '', '10', '1', '0', NULL, NULL),
929 ('34', '13', '3', '2017-05-07', '1510', '', '-10', '0', '0', NULL, NULL),
930 ('35', '10', '3', '2017-05-07', '4010', '', '-250', '1', '0', NULL, NULL),
931 ('36', '10', '3', '2017-05-07', '4010', '', '-50', '1', '0', NULL, NULL),
932 ('37', '10', '3', '2017-05-07', '1200', '', '300', '0', '0', '2', '2'),
933 ('38', '12', '3', '2017-05-07', '1065', '', '0', '0', '0', NULL, NULL),
934 ('39', '1', '1', '2017-05-07', '5010', '', '5', '1', '0', NULL, NULL),
935 ('40', '1', '1', '2017-05-07', '1060', '', '-5', '0', '0', NULL, NULL);
937 -- --------------------------------------------------------
940 -- Table structure for table `0_grn_batch`
943 DROP TABLE IF EXISTS `0_grn_batch`;
944 CREATE TABLE IF NOT EXISTS `0_grn_batch` (
945 `id` int(11) NOT NULL AUTO_INCREMENT,
946 `supplier_id` int(11) NOT NULL DEFAULT '0',
947 `purch_order_no` int(11) DEFAULT NULL,
948 `reference` varchar(60) NOT NULL DEFAULT '',
949 `delivery_date` date NOT NULL DEFAULT '0000-00-00',
950 `loc_code` varchar(5) DEFAULT NULL,
951 `rate` double DEFAULT '1',
953 KEY `delivery_date` (`delivery_date`),
954 KEY `purch_order_no` (`purch_order_no`)
955 ) ENGINE=InnoDB AUTO_INCREMENT=3 ;
958 -- Dumping data for table `0_grn_batch`
961 INSERT INTO `0_grn_batch` VALUES
962 ('1', '1', '1', '001/2017', '2017-05-05', 'DEF', '1'),
963 ('2', '1', '2', 'auto', '2017-05-05', 'DEF', '1');
965 -- --------------------------------------------------------
968 -- Table structure for table `0_grn_items`
971 DROP TABLE IF EXISTS `0_grn_items`;
972 CREATE TABLE IF NOT EXISTS `0_grn_items` (
973 `id` int(11) NOT NULL AUTO_INCREMENT,
974 `grn_batch_id` int(11) DEFAULT NULL,
975 `po_detail_item` int(11) NOT NULL DEFAULT '0',
976 `item_code` varchar(20) NOT NULL DEFAULT '',
977 `description` tinytext,
978 `qty_recd` double NOT NULL DEFAULT '0',
979 `quantity_inv` double NOT NULL DEFAULT '0',
981 KEY `grn_batch_id` (`grn_batch_id`)
982 ) ENGINE=InnoDB AUTO_INCREMENT=5 ;
985 -- Dumping data for table `0_grn_items`
988 INSERT INTO `0_grn_items` VALUES
989 ('1', '1', '1', '101', 'iPad Air 2 16GB', '100', '0'),
990 ('2', '1', '2', '102', 'iPhone 6 64GB', '100', '0'),
991 ('3', '1', '3', '103', 'iPhone Cover Case', '100', '0'),
992 ('4', '2', '4', '101', 'iPad Air 2 16GB', '15', '15');
994 -- --------------------------------------------------------
997 -- Table structure for table `0_groups`
1000 DROP TABLE IF EXISTS `0_groups`;
1001 CREATE TABLE IF NOT EXISTS `0_groups` (
1002 `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
1003 `description` varchar(60) NOT NULL DEFAULT '',
1004 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1006 UNIQUE KEY `description` (`description`)
1007 ) ENGINE=InnoDB AUTO_INCREMENT=4 ;
1010 -- Dumping data for table `0_groups`
1013 INSERT INTO `0_groups` VALUES (1, 'Small', 0);
1014 INSERT INTO `0_groups` VALUES (2, 'Medium', 0);
1015 INSERT INTO `0_groups` VALUES (3, 'Large', 0);
1017 -- --------------------------------------------------------
1020 -- Table structure for table `0_item_codes`
1023 DROP TABLE IF EXISTS `0_item_codes`;
1024 CREATE TABLE IF NOT EXISTS `0_item_codes` (
1025 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
1026 `item_code` varchar(20) NOT NULL,
1027 `stock_id` varchar(20) NOT NULL,
1028 `description` varchar(200) NOT NULL DEFAULT '',
1029 `category_id` smallint(6) unsigned NOT NULL,
1030 `quantity` double NOT NULL DEFAULT '1',
1031 `is_foreign` tinyint(1) NOT NULL DEFAULT '0',
1032 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1034 UNIQUE KEY `stock_id` (`stock_id`,`item_code`),
1035 KEY `item_code` (`item_code`)
1036 ) ENGINE=InnoDB AUTO_INCREMENT=9 ;
1039 -- Dumping data for table `0_item_codes`
1042 INSERT INTO `0_item_codes` VALUES
1043 ('1', '101', '101', 'iPad Air 2 16GB', '1', '1', '0', '0'),
1044 ('2', '102', '102', 'iPhone 6 64GB', '1', '1', '0', '0'),
1045 ('3', '103', '103', 'iPhone Cover Case', '1', '1', '0', '0'),
1046 ('4', '201', '201', 'AP Surf Set', '3', '1', '0', '0'),
1047 ('5', '301', '301', 'Support', '4', '1', '0', '0'),
1048 ('6', '501', '102', 'iPhone Pack', '1', '1', '0', '0'),
1049 ('7', '501', '103', 'iPhone Pack', '1', '1', '0', '0'),
1050 ('8', '202', '202', 'Maintenance', '4', '1', '0', '0');
1052 -- --------------------------------------------------------
1055 -- Table structure for table `0_item_tax_types`
1058 DROP TABLE IF EXISTS `0_item_tax_types`;
1059 CREATE TABLE IF NOT EXISTS `0_item_tax_types` (
1060 `id` int(11) NOT NULL auto_increment,
1061 `name` varchar(60) NOT NULL default '',
1062 `exempt` tinyint(1) NOT NULL default '0',
1063 `inactive` tinyint(1) NOT NULL default '0',
1065 UNIQUE KEY `name` (`name`)
1066 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
1069 -- Dumping data for table `0_item_tax_types`
1072 INSERT INTO `0_item_tax_types` VALUES (1, 'Regular', 0, 0);
1074 -- --------------------------------------------------------
1077 -- Table structure for table `0_item_tax_type_exemptions`
1080 DROP TABLE IF EXISTS `0_item_tax_type_exemptions`;
1081 CREATE TABLE IF NOT EXISTS `0_item_tax_type_exemptions` (
1082 `item_tax_type_id` int(11) NOT NULL DEFAULT '0',
1083 `tax_type_id` int(11) NOT NULL DEFAULT '0',
1084 PRIMARY KEY (`item_tax_type_id`,`tax_type_id`)
1088 -- Dumping data for table `0_item_tax_type_exemptions`
1091 -- --------------------------------------------------------
1094 -- Table structure for table `0_item_units`
1097 DROP TABLE IF EXISTS `0_item_units`;
1098 CREATE TABLE IF NOT EXISTS `0_item_units` (
1099 `abbr` varchar(20) NOT NULL,
1100 `name` varchar(40) NOT NULL,
1101 `decimals` tinyint(2) NOT NULL,
1102 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1103 PRIMARY KEY (`abbr`),
1104 UNIQUE KEY `name` (`name`)
1108 -- Dumping data for table `0_item_units`
1111 INSERT INTO `0_item_units` VALUES ('each', 'Each', 0, 0);
1112 INSERT INTO `0_item_units` VALUES ('hr', 'Hours', 0, 0);
1114 -- --------------------------------------------------------
1117 -- Table structure for table `0_journal`
1120 DROP TABLE IF EXISTS `0_journal`;
1121 CREATE TABLE `0_journal` (
1122 `type` smallint(6) NOT NULL DEFAULT '0',
1123 `trans_no` int(11) NOT NULL DEFAULT '0',
1124 `tran_date` date DEFAULT '0000-00-00',
1125 `reference` varchar(60) NOT NULL DEFAULT '',
1126 `source_ref` varchar(60) NOT NULL DEFAULT '',
1127 `event_date` date DEFAULT '0000-00-00',
1128 `doc_date` date NOT NULL DEFAULT '0000-00-00',
1129 `currency` char(3) NOT NULL DEFAULT '',
1130 `amount` double NOT NULL DEFAULT '0',
1131 `rate` double NOT NULL DEFAULT '1',
1132 PRIMARY KEY (`type`,`trans_no`),
1133 KEY `tran_date` (`tran_date`)
1137 -- Dumping data for table `0_journal`
1140 -- --------------------------------------------------------
1143 -- Table structure for table `0_locations`
1146 DROP TABLE IF EXISTS `0_locations`;
1147 CREATE TABLE IF NOT EXISTS `0_locations` (
1148 `loc_code` varchar(5) NOT NULL DEFAULT '',
1149 `location_name` varchar(60) NOT NULL DEFAULT '',
1150 `delivery_address` tinytext NOT NULL,
1151 `phone` varchar(30) NOT NULL DEFAULT '',
1152 `phone2` varchar(30) NOT NULL DEFAULT '',
1153 `fax` varchar(30) NOT NULL DEFAULT '',
1154 `email` varchar(100) NOT NULL DEFAULT '',
1155 `contact` varchar(30) NOT NULL DEFAULT '',
1156 `fixed_asset` tinyint(1) NOT NULL DEFAULT '0',
1157 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1158 PRIMARY KEY (`loc_code`)
1162 -- Dumping data for table `0_locations`
1165 INSERT INTO `0_locations` VALUES ('DEF', 'Default', 'N/A', '', '', '', '', '', 0, 0);
1167 -- --------------------------------------------------------
1170 -- Table structure for table `0_loc_stock`
1173 DROP TABLE IF EXISTS `0_loc_stock`;
1174 CREATE TABLE IF NOT EXISTS `0_loc_stock` (
1175 `loc_code` char(5) NOT NULL DEFAULT '',
1176 `stock_id` char(20) NOT NULL DEFAULT '',
1177 `reorder_level` double NOT NULL DEFAULT '0',
1178 PRIMARY KEY (`loc_code`,`stock_id`),
1179 KEY `stock_id` (`stock_id`)
1183 -- Dumping data for table `0_loc_stock`
1186 INSERT INTO `0_loc_stock` VALUES
1187 ('DEF', '101', '0'),
1188 ('DEF', '102', '0'),
1189 ('DEF', '103', '0'),
1190 ('DEF', '201', '0'),
1191 ('DEF', '202', '0'),
1192 ('DEF', '301', '0');
1194 -- --------------------------------------------------------
1197 -- Table structure for table `0_payment_terms`
1200 DROP TABLE IF EXISTS `0_payment_terms`;
1201 CREATE TABLE IF NOT EXISTS `0_payment_terms` (
1202 `terms_indicator` int(11) NOT NULL AUTO_INCREMENT,
1203 `terms` char(80) NOT NULL DEFAULT '',
1204 `days_before_due` smallint(6) NOT NULL DEFAULT '0',
1205 `day_in_following_month` smallint(6) NOT NULL DEFAULT '0',
1206 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1207 PRIMARY KEY (`terms_indicator`),
1208 UNIQUE KEY `terms` (`terms`)
1209 ) ENGINE=InnoDB AUTO_INCREMENT=5 ;
1212 -- Dumping data for table `0_payment_terms`
1215 INSERT INTO `0_payment_terms` VALUES (1, 'Due 15th Of the Following Month', 0, 17, 0);
1216 INSERT INTO `0_payment_terms` VALUES (2, 'Due By End Of The Following Month', 0, 30, 0);
1217 INSERT INTO `0_payment_terms` VALUES (3, 'Payment due within 10 days', 10, 0, 0);
1218 INSERT INTO `0_payment_terms` VALUES (4, 'Cash Only', 0, 0, 0);
1219 INSERT INTO `0_payment_terms` VALUES (5, 'Prepaid', -1, 0, 0);
1221 -- --------------------------------------------------------
1224 -- Table structure for table `0_prices`
1227 DROP TABLE IF EXISTS `0_prices`;
1228 CREATE TABLE IF NOT EXISTS `0_prices` (
1229 `id` int(11) NOT NULL AUTO_INCREMENT,
1230 `stock_id` varchar(20) NOT NULL DEFAULT '',
1231 `sales_type_id` int(11) NOT NULL DEFAULT '0',
1232 `curr_abrev` char(3) NOT NULL DEFAULT '',
1233 `price` double NOT NULL DEFAULT '0',
1235 UNIQUE KEY `price` (`stock_id`,`sales_type_id`,`curr_abrev`)
1236 ) ENGINE=InnoDB AUTO_INCREMENT=4 ;
1239 -- Dumping data for table `0_prices`
1242 INSERT INTO `0_prices` VALUES
1243 ('1', '101', '1', 'USD', '300'),
1244 ('2', '102', '1', 'USD', '250'),
1245 ('3', '103', '1', 'USD', '50');
1247 -- --------------------------------------------------------
1250 -- Table structure for table `0_printers`
1253 DROP TABLE IF EXISTS `0_printers`;
1254 CREATE TABLE IF NOT EXISTS `0_printers` (
1255 `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
1256 `name` varchar(20) NOT NULL,
1257 `description` varchar(60) NOT NULL,
1258 `queue` varchar(20) NOT NULL,
1259 `host` varchar(40) NOT NULL,
1260 `port` smallint(11) unsigned NOT NULL,
1261 `timeout` tinyint(3) unsigned NOT NULL,
1263 UNIQUE KEY `name` (`name`)
1264 ) ENGINE=InnoDB AUTO_INCREMENT=4 ;
1267 -- Dumping data for table `0_printers`
1270 INSERT INTO `0_printers` VALUES (1, 'QL500', 'Label printer', 'QL500', 'server', 127, 20);
1271 INSERT INTO `0_printers` VALUES (2, 'Samsung', 'Main network printer', 'scx4521F', 'server', 515, 5);
1272 INSERT INTO `0_printers` VALUES (3, 'Local', 'Local print server at user IP', 'lp', '', 515, 10);
1274 -- --------------------------------------------------------
1277 -- Table structure for table `0_print_profiles`
1280 DROP TABLE IF EXISTS `0_print_profiles`;
1281 CREATE TABLE IF NOT EXISTS `0_print_profiles` (
1282 `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
1283 `profile` varchar(30) NOT NULL,
1284 `report` varchar(5) DEFAULT NULL,
1285 `printer` tinyint(3) unsigned DEFAULT NULL,
1287 UNIQUE KEY `profile` (`profile`,`report`)
1288 ) ENGINE=InnoDB AUTO_INCREMENT=10 ;
1291 -- Dumping data for table `0_print_profiles`
1294 INSERT INTO `0_print_profiles` VALUES (1, 'Out of office', '', 0);
1295 INSERT INTO `0_print_profiles` VALUES (2, 'Sales Department', '', 0);
1296 INSERT INTO `0_print_profiles` VALUES (3, 'Central', '', 2);
1297 INSERT INTO `0_print_profiles` VALUES (4, 'Sales Department', '104', 2);
1298 INSERT INTO `0_print_profiles` VALUES (5, 'Sales Department', '105', 2);
1299 INSERT INTO `0_print_profiles` VALUES (6, 'Sales Department', '107', 2);
1300 INSERT INTO `0_print_profiles` VALUES (7, 'Sales Department', '109', 2);
1301 INSERT INTO `0_print_profiles` VALUES (8, 'Sales Department', '110', 2);
1302 INSERT INTO `0_print_profiles` VALUES (9, 'Sales Department', '201', 2);
1304 -- --------------------------------------------------------
1307 -- Table structure for table `0_purch_data`
1310 DROP TABLE IF EXISTS `0_purch_data`;
1311 CREATE TABLE IF NOT EXISTS `0_purch_data` (
1312 `supplier_id` int(11) NOT NULL DEFAULT '0',
1313 `stock_id` char(20) NOT NULL DEFAULT '',
1314 `price` double NOT NULL DEFAULT '0',
1315 `suppliers_uom` char(50) NOT NULL DEFAULT '',
1316 `conversion_factor` double NOT NULL DEFAULT '1',
1317 `supplier_description` char(50) NOT NULL DEFAULT '',
1318 PRIMARY KEY (`supplier_id`,`stock_id`)
1322 -- Dumping data for table `0_purch_data`
1325 INSERT INTO `0_purch_data` VALUES
1326 ('1', '101', '200', '', '1', 'iPad Air 2 16GB'),
1327 ('1', '102', '150', '', '1', 'iPhone 6 64GB'),
1328 ('1', '103', '10', '', '1', 'iPhone Cover Case');
1330 -- --------------------------------------------------------
1333 -- Table structure for table `0_purch_orders`
1336 DROP TABLE IF EXISTS `0_purch_orders`;
1337 CREATE TABLE IF NOT EXISTS `0_purch_orders` (
1338 `order_no` int(11) NOT NULL AUTO_INCREMENT,
1339 `supplier_id` int(11) NOT NULL DEFAULT '0',
1340 `comments` tinytext,
1341 `ord_date` date NOT NULL DEFAULT '0000-00-00',
1342 `reference` tinytext NOT NULL,
1343 `requisition_no` tinytext,
1344 `into_stock_location` varchar(5) NOT NULL DEFAULT '',
1345 `delivery_address` tinytext NOT NULL,
1346 `total` double NOT NULL DEFAULT '0',
1347 `prep_amount` double NOT NULL DEFAULT '0',
1348 `alloc` double NOT NULL DEFAULT '0',
1349 `tax_included` tinyint(1) NOT NULL DEFAULT '0',
1350 PRIMARY KEY (`order_no`),
1351 KEY `ord_date` (`ord_date`)
1352 ) ENGINE=InnoDB AUTO_INCREMENT=3 ;
1355 -- Dumping data for table `0_purch_orders`
1358 INSERT INTO `0_purch_orders` VALUES
1359 ('1', '1', NULL, '2017-05-05', '001/2017', NULL, 'DEF', 'N/A', '37800', '0', '0', '0'),
1360 ('2', '1', NULL, '2017-05-05', 'auto', 'rr4', 'DEF', 'N/A', '3150', '0', '0', '0');
1362 -- --------------------------------------------------------
1365 -- Table structure for table `0_purch_order_details`
1368 DROP TABLE IF EXISTS `0_purch_order_details`;
1369 CREATE TABLE IF NOT EXISTS `0_purch_order_details` (
1370 `po_detail_item` int(11) NOT NULL AUTO_INCREMENT,
1371 `order_no` int(11) NOT NULL DEFAULT '0',
1372 `item_code` varchar(20) NOT NULL DEFAULT '',
1373 `description` tinytext,
1374 `delivery_date` date NOT NULL DEFAULT '0000-00-00',
1375 `qty_invoiced` double NOT NULL DEFAULT '0',
1376 `unit_price` double NOT NULL DEFAULT '0',
1377 `act_price` double NOT NULL DEFAULT '0',
1378 `std_cost_unit` double NOT NULL DEFAULT '0',
1379 `quantity_ordered` double NOT NULL DEFAULT '0',
1380 `quantity_received` double NOT NULL DEFAULT '0',
1381 PRIMARY KEY (`po_detail_item`),
1382 KEY `order` (`order_no`,`po_detail_item`),
1383 KEY `itemcode` (`item_code`)
1384 ) ENGINE=InnoDB AUTO_INCREMENT=5 ;
1387 -- Dumping data for table `0_purch_order_details`
1390 INSERT INTO `0_purch_order_details` VALUES
1391 ('1', '1', '101', 'iPad Air 2 16GB', '2017-05-15', '0', '200', '200', '200', '100', '100'),
1392 ('2', '1', '102', 'iPhone 6 64GB', '2017-05-15', '0', '150', '150', '150', '100', '100'),
1393 ('3', '1', '103', 'iPhone Cover Case', '2017-05-15', '0', '10', '10', '10', '100', '100'),
1394 ('4', '2', '101', 'iPad Air 2 16GB', '2017-05-05', '15', '200', '200', '200', '15', '15');
1396 -- --------------------------------------------------------
1399 -- Table structure for table `0_quick_entries`
1402 DROP TABLE IF EXISTS `0_quick_entries`;
1403 CREATE TABLE IF NOT EXISTS `0_quick_entries` (
1404 `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
1405 `type` tinyint(1) NOT NULL DEFAULT '0',
1406 `description` varchar(60) NOT NULL,
1407 `usage` varchar(120) NULL,
1408 `base_amount` double NOT NULL DEFAULT '0',
1409 `base_desc` varchar(60) DEFAULT NULL,
1410 `bal_type` tinyint(1) NOT NULL DEFAULT '0',
1412 KEY `description` (`description`)
1413 ) ENGINE=InnoDB AUTO_INCREMENT=4 ;
1416 -- Dumping data for table `0_quick_entries`
1419 INSERT INTO `0_quick_entries` VALUES (1, 1, 'Maintenance', NULL, 0, 'Amount', 0);
1420 INSERT INTO `0_quick_entries` VALUES (2, 4, 'Phone', NULL, 0, 'Amount', 0);
1421 INSERT INTO `0_quick_entries` VALUES (3, 2, 'Cash Sales', 'Retail sales without invoice', 0, 'Amount', 0);
1423 -- --------------------------------------------------------
1426 -- Table structure for table `0_quick_entry_lines`
1429 DROP TABLE IF EXISTS `0_quick_entry_lines`;
1430 CREATE TABLE IF NOT EXISTS `0_quick_entry_lines` (
1431 `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
1432 `qid` smallint(6) unsigned NOT NULL,
1433 `amount` double DEFAULT '0',
1434 `memo` tinytext NOT NULL,
1435 `action` varchar(2) NOT NULL,
1436 `dest_id` varchar(15) NOT NULL DEFAULT '',
1437 `dimension_id` smallint(6) unsigned DEFAULT NULL,
1438 `dimension2_id` smallint(6) unsigned DEFAULT NULL,
1441 ) ENGINE=InnoDB AUTO_INCREMENT=7 ;
1444 -- Dumping data for table `0_quick_entry_lines`
1447 INSERT INTO `0_quick_entry_lines` VALUES (1, 1, 0, '', 't-', '1', 0, 0);
1448 INSERT INTO `0_quick_entry_lines` VALUES (2, 2, 0, '', 't-', '1', 0, 0);
1449 INSERT INTO `0_quick_entry_lines` VALUES (3, 3, 0, '', 't-', '1', 0, 0);
1450 INSERT INTO `0_quick_entry_lines` VALUES (4, 3, 0, '', '=', '4010', 0, 0);
1451 INSERT INTO `0_quick_entry_lines` VALUES (5, 1, 0, '', '=', '5765', 0, 0);
1452 INSERT INTO `0_quick_entry_lines` VALUES (6, 2, 0, '', '=', '5780', 0, 0);
1454 -- --------------------------------------------------------
1457 -- Table structure for table `0_recurrent_invoices`
1460 DROP TABLE IF EXISTS `0_recurrent_invoices`;
1461 CREATE TABLE IF NOT EXISTS `0_recurrent_invoices` (
1462 `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
1463 `description` varchar(60) NOT NULL DEFAULT '',
1464 `order_no` int(11) unsigned NOT NULL,
1465 `debtor_no` int(11) unsigned DEFAULT NULL,
1466 `group_no` smallint(6) unsigned DEFAULT NULL,
1467 `days` int(11) NOT NULL DEFAULT '0',
1468 `monthly` int(11) NOT NULL DEFAULT '0',
1469 `begin` date NOT NULL DEFAULT '0000-00-00',
1470 `end` date NOT NULL DEFAULT '0000-00-00',
1471 `last_sent` date NOT NULL DEFAULT '0000-00-00',
1473 UNIQUE KEY `description` (`description`)
1474 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
1477 -- Dumping data for table `0_recurrent_invoices`
1480 INSERT INTO `0_recurrent_invoices` VALUES ('1', 'Weekly Maintenance', '6', '1', '1', '7', '0', '2017-04-01', '2020-05-07', '2017-04-08');
1482 -- --------------------------------------------------------
1485 -- Table structure for table `0_reflines`
1488 DROP TABLE IF EXISTS `0_reflines`;
1490 CREATE TABLE `0_reflines` (
1491 `id` int(11) NOT NULL AUTO_INCREMENT,
1492 `trans_type` int(11) NOT NULL,
1493 `prefix` char(5) NOT NULL DEFAULT '',
1494 `pattern` varchar(35) NOT NULL DEFAULT '1',
1495 `description` varchar(60) NOT NULL DEFAULT '',
1496 `default` tinyint(1) NOT NULL DEFAULT '0',
1497 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1499 UNIQUE KEY `prefix` (`trans_type`, `prefix`)
1500 ) ENGINE=InnoDB AUTO_INCREMENT=23;
1503 -- Dumping data for table `0_reflines`
1506 INSERT INTO `0_reflines` VALUES
1507 ('1', '0', '', '{001}/{YYYY}', '', '1', '0'),
1508 ('2', '1', '', '{001}/{YYYY}', '', '1', '0'),
1509 ('3', '2', '', '{001}/{YYYY}', '', '1', '0'),
1510 ('4', '4', '', '{001}/{YYYY}', '', '1', '0'),
1511 ('5', '10', '', '{001}/{YYYY}', '', '1', '0'),
1512 ('6', '11', '', '{001}/{YYYY}', '', '1', '0'),
1513 ('7', '12', '', '{001}/{YYYY}', '', '1', '0'),
1514 ('8', '13', '', '{001}/{YYYY}', '', '1', '0'),
1515 ('9', '16', '', '{001}/{YYYY}', '', '1', '0'),
1516 ('10', '17', '', '{001}/{YYYY}', '', '1', '0'),
1517 ('11', '18', '', '{001}/{YYYY}', '', '1', '0'),
1518 ('12', '20', '', '{001}/{YYYY}', '', '1', '0'),
1519 ('13', '21', '', '{001}/{YYYY}', '', '1', '0'),
1520 ('14', '22', '', '{001}/{YYYY}', '', '1', '0'),
1521 ('15', '25', '', '{001}/{YYYY}', '', '1', '0'),
1522 ('16', '26', '', '{001}/{YYYY}', '', '1', '0'),
1523 ('17', '28', '', '{001}/{YYYY}', '', '1', '0'),
1524 ('18', '29', '', '{001}/{YYYY}', '', '1', '0'),
1525 ('19', '30', '', '{001}/{YYYY}', '', '1', '0'),
1526 ('20', '32', '', '{001}/{YYYY}', '', '1', '0'),
1527 ('21', '35', '', '{001}/{YYYY}', '', '1', '0'),
1528 ('22', '40', '', '{001}/{YYYY}', '', '1', '0');
1530 -- --------------------------------------------------------
1533 -- Table structure for table `0_refs`
1536 DROP TABLE IF EXISTS `0_refs`;
1537 CREATE TABLE IF NOT EXISTS `0_refs` (
1538 `id` int(11) NOT NULL DEFAULT '0',
1539 `type` int(11) NOT NULL DEFAULT '0',
1540 `reference` varchar(100) NOT NULL DEFAULT '',
1541 PRIMARY KEY (`id`,`type`),
1542 KEY `Type_and_Reference` (`type`,`reference`)
1546 -- Dumping data for table `0_refs`
1549 INSERT INTO `0_refs` VALUES
1550 ('1', '1', '001/2017'),
1551 ('1', '10', '001/2017'),
1552 ('2', '10', '002/2017'),
1553 ('3', '10', '003/2017'),
1554 ('4', '10', '004/2017'),
1555 ('1', '12', '001/2017'),
1556 ('2', '12', '002/2017'),
1557 ('3', '12', '003/2017'),
1558 ('1', '18', '001/2017'),
1559 ('1', '20', '001/2017'),
1560 ('1', '25', '001/2017'),
1561 ('1', '26', '001/2017'),
1562 ('2', '26', '002/2017'),
1563 ('3', '26', '003/2017'),
1564 ('3', '30', '001/2017'),
1565 ('4', '30', '002/2017'),
1566 ('6', '30', '003/2017'),
1567 ('1', '40', '001/2017');
1569 -- --------------------------------------------------------
1572 -- Table structure for table `0_salesman`
1575 DROP TABLE IF EXISTS `0_salesman`;
1576 CREATE TABLE IF NOT EXISTS `0_salesman` (
1577 `salesman_code` int(11) NOT NULL AUTO_INCREMENT,
1578 `salesman_name` char(60) NOT NULL DEFAULT '',
1579 `salesman_phone` char(30) NOT NULL DEFAULT '',
1580 `salesman_fax` char(30) NOT NULL DEFAULT '',
1581 `salesman_email` varchar(100) NOT NULL DEFAULT '',
1582 `provision` double NOT NULL DEFAULT '0',
1583 `break_pt` double NOT NULL DEFAULT '0',
1584 `provision2` double NOT NULL DEFAULT '0',
1585 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1586 PRIMARY KEY (`salesman_code`),
1587 UNIQUE KEY `salesman_name` (`salesman_name`)
1588 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
1591 -- Dumping data for table `0_salesman`
1594 INSERT INTO `0_salesman` VALUES (1, 'Sales Person', '', '', '', 5, 1000, 4, 0);
1596 -- --------------------------------------------------------
1599 -- Table structure for table `0_sales_orders`
1602 DROP TABLE IF EXISTS `0_sales_orders`;
1603 CREATE TABLE IF NOT EXISTS `0_sales_orders` (
1604 `order_no` int(11) NOT NULL,
1605 `trans_type` smallint(6) NOT NULL DEFAULT '30',
1606 `version` tinyint(1) unsigned NOT NULL DEFAULT '0',
1607 `type` tinyint(1) NOT NULL DEFAULT '0',
1608 `debtor_no` int(11) NOT NULL DEFAULT '0',
1609 `branch_code` int(11) NOT NULL DEFAULT '0',
1610 `reference` varchar(100) NOT NULL DEFAULT '',
1611 `customer_ref` tinytext NOT NULL,
1612 `comments` tinytext,
1613 `ord_date` date NOT NULL DEFAULT '0000-00-00',
1614 `order_type` int(11) NOT NULL DEFAULT '0',
1615 `ship_via` int(11) NOT NULL DEFAULT '0',
1616 `delivery_address` tinytext NOT NULL,
1617 `contact_phone` varchar(30) DEFAULT NULL,
1618 `contact_email` varchar(100) DEFAULT NULL,
1619 `deliver_to` tinytext NOT NULL,
1620 `freight_cost` double NOT NULL DEFAULT '0',
1621 `from_stk_loc` varchar(5) NOT NULL DEFAULT '',
1622 `delivery_date` date NOT NULL DEFAULT '0000-00-00',
1623 `payment_terms` int(11) DEFAULT NULL,
1624 `total` double NOT NULL DEFAULT '0',
1625 `prep_amount` double NOT NULL DEFAULT '0',
1626 `alloc` double NOT NULL DEFAULT '0',
1627 PRIMARY KEY (`trans_type`,`order_no`)
1631 -- Dumping data for table `0_sales_orders`
1634 INSERT INTO `0_sales_orders` VALUES
1635 ('1', '30', '1', '0', '1', '1', 'auto', '', NULL, '2017-05-10', '1', '1', 'N/A', NULL, NULL, 'Donald Easter LLC', '0', 'DEF', '2017-05-05', '4', '6240', '0', '0'),
1636 ('2', '30', '1', '0', '1', '1', 'auto', '', NULL, '2017-05-07', '1', '1', 'N/A', NULL, NULL, 'Donald Easter LLC', '0', 'DEF', '2017-05-07', '4', '300', '0', '0'),
1637 ('3', '30', '0', '0', '1', '1', '001/2017', '', NULL, '2017-05-07', '1', '1', 'N/A', NULL, NULL, 'Donald Easter LLC', '0', 'DEF', '2017-05-08', '4', '300', '0', '0'),
1638 ('4', '30', '0', '0', '2', '2', '002/2017', '', NULL, '2017-05-07', '1', '1', 'N/A', NULL, NULL, 'MoneyMaker Ltd.', '0', 'DEF', '2017-05-08', '1', '267.14', '0', '0'),
1639 ('5', '30', '1', '0', '2', '2', 'auto', '', NULL, '2017-05-07', '1', '1', 'N/A', NULL, NULL, 'MoneyMaker Ltd.', '0', 'DEF', '2017-06-17', '1', '267.14', '0', '0'),
1640 ('6', '30', '0', '1', '1', '1', '003/2017', '', NULL, '2017-05-07', '1', '1', 'N/A', NULL, NULL, 'Donald Easter LLC', '0', 'DEF', '2017-05-08', '4', '450', '0', '0'),
1641 ('7', '30', '1', '0', '1', '1', 'auto', '', 'Recurrent Invoice covers period 04/01/2017 - 04/07/2017.', '2017-05-07', '1', '1', 'N/A', NULL, NULL, 'Donald Easter LLC', '0', 'DEF', '2017-05-07', '4', '0', '0', '0');
1643 -- --------------------------------------------------------
1646 -- Table structure for table `0_sales_order_details`
1649 DROP TABLE IF EXISTS `0_sales_order_details`;
1650 CREATE TABLE IF NOT EXISTS `0_sales_order_details` (
1651 `id` int(11) NOT NULL AUTO_INCREMENT,
1652 `order_no` int(11) NOT NULL DEFAULT '0',
1653 `trans_type` smallint(6) NOT NULL DEFAULT '30',
1654 `stk_code` varchar(20) NOT NULL DEFAULT '',
1655 `description` tinytext,
1656 `qty_sent` double NOT NULL DEFAULT '0',
1657 `unit_price` double NOT NULL DEFAULT '0',
1658 `quantity` double NOT NULL DEFAULT '0',
1659 `invoiced` double NOT NULL DEFAULT '0',
1660 `discount_percent` double NOT NULL DEFAULT '0',
1662 KEY `sorder` (`trans_type`,`order_no`),
1663 KEY `stkcode` (`stk_code`)
1664 ) ENGINE=InnoDB AUTO_INCREMENT=11 ;
1667 -- Dumping data for table `0_sales_order_details`
1670 INSERT INTO `0_sales_order_details` VALUES
1671 ('1', '1', '30', '101', 'iPad Air 2 16GB', '20', '300', '20', '0', '0'),
1672 ('2', '1', '30', '301', 'Support', '3', '80', '3', '0', '0'),
1673 ('3', '2', '30', '101', 'iPad Air 2 16GB', '1', '300', '1', '0', '0'),
1674 ('4', '3', '30', '102', 'iPhone 6 64GB', '0', '250', '1', '0', '0'),
1675 ('5', '3', '30', '103', 'iPhone Cover Case', '0', '50', '1', '0', '0'),
1676 ('6', '4', '30', '101', 'iPad Air 2 16GB', '0', '267.14', '1', '0', '0'),
1677 ('7', '5', '30', '102', 'iPhone 6 64GB', '1', '222.62', '1', '0', '0'),
1678 ('8', '5', '30', '103', 'iPhone Cover Case', '1', '44.52', '1', '0', '0'),
1679 ('9', '6', '30', '202', 'Maintenance', '0', '90', '5', '0', '0'),
1680 ('10', '7', '30', '202', 'Maintenance', '5', '0', '5', '0', '0');
1682 -- --------------------------------------------------------
1685 -- Table structure for table `0_sales_pos`
1688 DROP TABLE IF EXISTS `0_sales_pos`;
1689 CREATE TABLE IF NOT EXISTS `0_sales_pos` (
1690 `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
1691 `pos_name` varchar(30) NOT NULL,
1692 `cash_sale` tinyint(1) NOT NULL,
1693 `credit_sale` tinyint(1) NOT NULL,
1694 `pos_location` varchar(5) NOT NULL,
1695 `pos_account` smallint(6) unsigned NOT NULL,
1696 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1698 UNIQUE KEY `pos_name` (`pos_name`)
1699 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
1702 -- Dumping data for table `0_sales_pos`
1705 INSERT INTO `0_sales_pos` VALUES (1, 'Default', 1, 1, 'DEF', 2, 0);
1707 -- --------------------------------------------------------
1710 -- Table structure for table `0_sales_types`
1713 DROP TABLE IF EXISTS `0_sales_types`;
1714 CREATE TABLE IF NOT EXISTS `0_sales_types` (
1715 `id` int(11) NOT NULL AUTO_INCREMENT,
1716 `sales_type` char(50) NOT NULL DEFAULT '',
1717 `tax_included` int(1) NOT NULL DEFAULT '0',
1718 `factor` double NOT NULL DEFAULT '1',
1719 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1721 UNIQUE KEY `sales_type` (`sales_type`)
1722 ) ENGINE=InnoDB AUTO_INCREMENT=3 ;
1725 -- Dumping data for table `0_sales_types`
1728 INSERT INTO `0_sales_types` VALUES (1, 'Retail', 1, 1, 0);
1729 INSERT INTO `0_sales_types` VALUES (2, 'Wholesale', 0, 0.7, 0);
1731 -- --------------------------------------------------------
1734 -- Table structure for table `0_security_roles`
1737 DROP TABLE IF EXISTS `0_security_roles`;
1738 CREATE TABLE IF NOT EXISTS `0_security_roles` (
1739 `id` int(11) NOT NULL AUTO_INCREMENT,
1740 `role` varchar(30) NOT NULL,
1741 `description` varchar(50) DEFAULT NULL,
1744 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1746 UNIQUE KEY `role` (`role`)
1747 ) ENGINE=InnoDB AUTO_INCREMENT=11 ;
1750 -- Dumping data for table `0_security_roles`
1753 INSERT INTO `0_security_roles` VALUES (1, 'Inquiries', 'Inquiries', '768;2816;3072;3328;5632;5888;8192;8448;10752;11008;13312;15872;16128', '257;258;259;260;513;514;515;516;517;518;519;520;521;522;523;524;525;773;774;2822;3073;3075;3076;3077;3329;3330;3331;3332;3333;3334;3335;5377;5633;5640;5889;5890;5891;7937;7938;7939;7940;8193;8194;8450;8451;10497;10753;11009;11010;11012;13313;13315;15617;15618;15619;15620;15621;15622;15623;15624;15625;15626;15873;15882;16129;16130;16131;16132;775', 0);
1754 INSERT INTO `0_security_roles` VALUES (2, 'System Administrator', 'System Administrator', '256;512;768;2816;3072;3328;5376;5632;5888;7936;8192;8448;9472;9728;10496;10752;11008;13056;13312;15616;15872;16128', '257;258;259;260;513;514;515;516;517;518;519;520;521;522;523;524;525;526;769;770;771;772;773;774;2817;2818;2819;2820;2821;2822;2823;3073;3074;3082;3075;3076;3077;3078;3079;3080;3081;3329;3330;3331;3332;3333;3334;3335;5377;5633;5634;5635;5636;5637;5641;5638;5639;5640;5889;5890;5891;7937;7938;7939;7940;8193;8194;8195;8196;8197;8449;8450;8451;9217;9218;9220;9473;9474;9475;9476;9729;10497;10753;10754;10755;10756;10757;11009;11010;11011;11012;13057;13313;13314;13315;15617;15618;15619;15620;15621;15622;15623;15624;15628;15625;15626;15627;15873;15874;15875;15876;15877;15878;15879;15880;15883;15881;15882;16129;16130;16131;16132;775', 0);
1755 INSERT INTO `0_security_roles` VALUES (3, 'Salesman', 'Salesman', '768;3072;5632;8192;15872', '773;774;3073;3075;3081;5633;8194;15873;775', 0);
1756 INSERT INTO `0_security_roles` VALUES (4, 'Stock Manager', 'Stock Manager', '768;2816;3072;3328;5632;5888;8192;8448;10752;11008;13312;15872;16128', '2818;2822;3073;3076;3077;3329;3330;3330;3330;3331;3331;3332;3333;3334;3335;5633;5640;5889;5890;5891;8193;8194;8450;8451;10753;11009;11010;11012;13313;13315;15882;16129;16130;16131;16132;775', 0);
1757 INSERT INTO `0_security_roles` VALUES (5, 'Production Manager', 'Production Manager', '512;768;2816;3072;3328;5632;5888;8192;8448;10752;11008;13312;15616;15872;16128', '521;523;524;2818;2819;2820;2821;2822;2823;3073;3074;3076;3077;3078;3079;3080;3081;3329;3330;3330;3330;3331;3331;3332;3333;3334;3335;5633;5640;5640;5889;5890;5891;8193;8194;8196;8197;8450;8451;10753;10755;11009;11010;11012;13313;13315;15617;15619;15620;15621;15624;15624;15876;15877;15880;15882;16129;16130;16131;16132;775', 0);
1758 INSERT INTO `0_security_roles` VALUES (6, 'Purchase Officer', 'Purchase Officer', '512;768;2816;3072;3328;5376;5632;5888;8192;8448;10752;11008;13312;15616;15872;16128', '521;523;524;2818;2819;2820;2821;2822;2823;3073;3074;3076;3077;3078;3079;3080;3081;3329;3330;3330;3330;3331;3331;3332;3333;3334;3335;5377;5633;5635;5640;5640;5889;5890;5891;8193;8194;8196;8197;8449;8450;8451;10753;10755;11009;11010;11012;13313;13315;15617;15619;15620;15621;15624;15624;15876;15877;15880;15882;16129;16130;16131;16132;775', 0);
1759 INSERT INTO `0_security_roles` VALUES (7, 'AR Officer', 'AR Officer', '512;768;2816;3072;3328;5632;5888;8192;8448;10752;11008;13312;15616;15872;16128', '521;523;524;771;773;774;2818;2819;2820;2821;2822;2823;3073;3073;3074;3075;3076;3077;3078;3079;3080;3081;3081;3329;3330;3330;3330;3331;3331;3332;3333;3334;3335;5633;5633;5634;5637;5638;5639;5640;5640;5889;5890;5891;8193;8194;8194;8196;8197;8450;8451;10753;10755;11009;11010;11012;13313;13315;15617;15619;15620;15621;15624;15624;15873;15876;15877;15878;15880;15882;16129;16130;16131;16132;775', 0);
1760 INSERT INTO `0_security_roles` VALUES (8, 'AP Officer', 'AP Officer', '512;768;2816;3072;3328;5376;5632;5888;8192;8448;10752;11008;13312;15616;15872;16128', '257;258;259;260;521;523;524;769;770;771;772;773;774;2818;2819;2820;2821;2822;2823;3073;3074;3082;3076;3077;3078;3079;3080;3081;3329;3330;3331;3332;3333;3334;3335;5377;5633;5635;5640;5889;5890;5891;7937;7938;7939;7940;8193;8194;8196;8197;8449;8450;8451;10497;10753;10755;11009;11010;11012;13057;13313;13315;15617;15619;15620;15621;15624;15876;15877;15880;15882;16129;16130;16131;16132;775', 0);
1761 INSERT INTO `0_security_roles` VALUES (9, 'Accountant', 'New Accountant', '512;768;2816;3072;3328;5376;5632;5888;8192;8448;10752;11008;13312;15616;15872;16128', '257;258;259;260;521;523;524;771;772;773;774;2818;2819;2820;2821;2822;2823;3073;3074;3075;3076;3077;3078;3079;3080;3081;3329;3330;3331;3332;3333;3334;3335;5377;5633;5634;5635;5637;5638;5639;5640;5889;5890;5891;7937;7938;7939;7940;8193;8194;8196;8197;8449;8450;8451;10497;10753;10755;11009;11010;11012;13313;13315;15617;15618;15619;15620;15621;15624;15873;15876;15877;15878;15880;15882;16129;16130;16131;16132;775', 0);
1762 INSERT INTO `0_security_roles` VALUES (10, 'Sub Admin', 'Sub Admin', '512;768;2816;3072;3328;5376;5632;5888;8192;8448;10752;11008;13312;15616;15872;16128', '257;258;259;260;521;523;524;771;772;773;774;2818;2819;2820;2821;2822;2823;3073;3074;3082;3075;3076;3077;3078;3079;3080;3081;3329;3330;3331;3332;3333;3334;3335;5377;5633;5634;5635;5637;5638;5639;5640;5889;5890;5891;7937;7938;7939;7940;8193;8194;8196;8197;8449;8450;8451;10497;10753;10755;11009;11010;11012;13057;13313;13315;15617;15619;15620;15621;15624;15873;15874;15876;15877;15878;15879;15880;15882;16129;16130;16131;16132;775', 0);
1764 -- --------------------------------------------------------
1767 -- Table structure for table `0_shippers`
1770 DROP TABLE IF EXISTS `0_shippers`;
1771 CREATE TABLE IF NOT EXISTS `0_shippers` (
1772 `shipper_id` int(11) NOT NULL AUTO_INCREMENT,
1773 `shipper_name` varchar(60) NOT NULL DEFAULT '',
1774 `phone` varchar(30) NOT NULL DEFAULT '',
1775 `phone2` varchar(30) NOT NULL DEFAULT '',
1776 `contact` tinytext NOT NULL,
1777 `address` tinytext NOT NULL,
1778 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1779 PRIMARY KEY (`shipper_id`),
1780 UNIQUE KEY `name` (`shipper_name`)
1781 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
1784 -- Dumping data for table `0_shippers`
1787 INSERT INTO `0_shippers` VALUES (1, 'Default', '', '', '', '', 0);
1789 -- --------------------------------------------------------
1792 -- Table structure for table `0_sql_trail`
1795 DROP TABLE IF EXISTS `0_sql_trail`;
1796 CREATE TABLE IF NOT EXISTS `0_sql_trail` (
1797 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
1798 `sql` text NOT NULL,
1799 `result` tinyint(1) NOT NULL,
1800 `msg` varchar(255) NOT NULL,
1805 -- Dumping data for table `0_sql_trail`
1808 -- --------------------------------------------------------
1811 -- Table structure for table `0_stock_category`
1814 DROP TABLE IF EXISTS `0_stock_category`;
1815 CREATE TABLE IF NOT EXISTS `0_stock_category` (
1816 `category_id` int(11) NOT NULL AUTO_INCREMENT,
1817 `description` varchar(60) NOT NULL DEFAULT '',
1818 `dflt_tax_type` int(11) NOT NULL DEFAULT '1',
1819 `dflt_units` varchar(20) NOT NULL DEFAULT 'each',
1820 `dflt_mb_flag` char(1) NOT NULL DEFAULT 'B',
1821 `dflt_sales_act` varchar(15) NOT NULL DEFAULT '',
1822 `dflt_cogs_act` varchar(15) NOT NULL DEFAULT '',
1823 `dflt_inventory_act` varchar(15) NOT NULL DEFAULT '',
1824 `dflt_adjustment_act` varchar(15) NOT NULL DEFAULT '',
1825 `dflt_wip_act` varchar(15) NOT NULL DEFAULT '',
1826 `dflt_dim1` int(11) DEFAULT NULL,
1827 `dflt_dim2` int(11) DEFAULT NULL,
1828 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1829 `dflt_no_sale` tinyint(1) NOT NULL DEFAULT '0',
1830 `dflt_no_purchase` tinyint(1) NOT NULL DEFAULT '0',
1831 PRIMARY KEY (`category_id`),
1832 UNIQUE KEY `description` (`description`)
1833 ) ENGINE=InnoDB AUTO_INCREMENT=5 ;
1836 -- Dumping data for table `0_stock_category`
1839 INSERT INTO `0_stock_category` VALUES (1, 'Components', 1, 'each', 'B', '4010', '5010', '1510', '5040', '1530', 0, 0, 0, 0, 0);
1840 INSERT INTO `0_stock_category` VALUES (2, 'Charges', 1, 'each', 'D', '4010', '5010', '1510', '5040', '1530', 0, 0, 0, 0, 0);
1841 INSERT INTO `0_stock_category` VALUES (3, 'Systems', 1, 'each', 'M', '4010', '5010', '1510', '5040', '1530', 0, 0, 0, 0, 0);
1842 INSERT INTO `0_stock_category` VALUES (4, 'Services', 1, 'hr', 'D', '4010', '5010', '1510', '5040', '1530', 0, 0, 0, 0, 0);
1844 -- --------------------------------------------------------
1847 -- Structure of table `0_stock_fa_class` ###
1850 DROP TABLE IF EXISTS `0_stock_fa_class`;
1851 CREATE TABLE `0_stock_fa_class` (
1852 `fa_class_id` varchar(20) NOT NULL DEFAULT '',
1853 `parent_id` varchar(20) NOT NULL DEFAULT '',
1854 `description` varchar(200) NOT NULL DEFAULT '',
1855 `long_description` tinytext NOT NULL,
1856 `depreciation_rate` double NOT NULL DEFAULT '0',
1857 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1858 PRIMARY KEY (`fa_class_id`)
1861 -- --------------------------------------------------------
1864 -- Table structure for table `0_stock_master`
1867 DROP TABLE IF EXISTS `0_stock_master`;
1868 CREATE TABLE IF NOT EXISTS `0_stock_master` (
1869 `stock_id` varchar(20) NOT NULL DEFAULT '',
1870 `category_id` int(11) NOT NULL DEFAULT '0',
1871 `tax_type_id` int(11) NOT NULL DEFAULT '0',
1872 `description` varchar(200) NOT NULL DEFAULT '',
1873 `long_description` tinytext NOT NULL,
1874 `units` varchar(20) NOT NULL DEFAULT 'each',
1875 `mb_flag` char(1) NOT NULL DEFAULT 'B',
1876 `sales_account` varchar(15) NOT NULL DEFAULT '',
1877 `cogs_account` varchar(15) NOT NULL DEFAULT '',
1878 `inventory_account` varchar(15) NOT NULL DEFAULT '',
1879 `adjustment_account` varchar(15) NOT NULL DEFAULT '',
1880 `wip_account` varchar(15) NOT NULL DEFAULT '',
1881 `dimension_id` int(11) DEFAULT NULL,
1882 `dimension2_id` int(11) DEFAULT NULL,
1883 `purchase_cost` double NOT NULL DEFAULT '0',
1884 `material_cost` double NOT NULL DEFAULT '0',
1885 `labour_cost` double NOT NULL DEFAULT '0',
1886 `overhead_cost` double NOT NULL DEFAULT '0',
1887 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1888 `no_sale` tinyint(1) NOT NULL DEFAULT '0',
1889 `no_purchase` tinyint(1) NOT NULL DEFAULT '0',
1890 `editable` tinyint(1) NOT NULL DEFAULT '0',
1891 `depreciation_method` char(1) NOT NULL DEFAULT 'S',
1892 `depreciation_rate` double NOT NULL DEFAULT '0',
1893 `depreciation_factor` double NOT NULL DEFAULT '1',
1894 `depreciation_start` date NOT NULL DEFAULT '0000-00-00',
1895 `depreciation_date` date NOT NULL DEFAULT '0000-00-00',
1896 `fa_class_id` varchar(20) NOT NULL DEFAULT '',
1897 PRIMARY KEY (`stock_id`)
1901 -- Dumping data for table `0_stock_master`
1904 INSERT INTO `0_stock_master` VALUES
1905 ('101', '1', '1', 'iPad Air 2 16GB', '', 'each', 'B', '4010', '5010', '1510', '5040', '1530', '0', '0', '0', '200', '0', '0', '0', '0', '0', '0', 'S', '0', '1', '0000-00-00', '0000-00-00', ''),
1906 ('102', '1', '1', 'iPhone 6 64GB', '', 'each', 'B', '4010', '5010', '1510', '5040', '1530', '0', '0', '0', '150', '0', '0', '0', '0', '0', '0', 'S', '0', '1', '0000-00-00', '0000-00-00', ''),
1907 ('103', '1', '1', 'iPhone Cover Case', '', 'each', 'B', '4010', '5010', '1510', '5040', '1530', '0', '0', '0', '10', '0', '0', '0', '0', '0', '0', 'S', '0', '1', '0000-00-00', '0000-00-00', ''),
1908 ('201', '3', '1', 'AP Surf Set', '', 'each', 'M', '4010', '5010', '1510', '5040', '1530', '0', '0', '0', '360', '0', '0', '0', '0', '0', '0', 'S', '0', '1', '0000-00-00', '0000-00-00', ''),
1909 ('202', '4', '1', 'Maintenance', '', 'hr', 'D', '4010', '5010', '1510', '5040', '1530', '0', '0', '0', '0', '0', '0', '0', '0', '0', '1', 'S', '0', '1', '0000-00-00', '0000-00-00', ''),
1910 ('301', '4', '1', 'Support', '', 'hr', 'D', '4010', '5010', '1510', '5040', '1530', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 'S', '0', '1', '0000-00-00', '0000-00-00', '');
1912 -- --------------------------------------------------------
1915 -- Table structure for table `0_stock_moves`
1918 DROP TABLE IF EXISTS `0_stock_moves`;
1919 CREATE TABLE `0_stock_moves` (
1920 `trans_id` int(11) NOT NULL AUTO_INCREMENT,
1921 `trans_no` int(11) NOT NULL DEFAULT '0',
1922 `stock_id` char(20) NOT NULL DEFAULT '',
1923 `type` smallint(6) NOT NULL DEFAULT '0',
1924 `loc_code` char(5) NOT NULL DEFAULT '',
1925 `tran_date` date NOT NULL DEFAULT '0000-00-00',
1926 `price` double NOT NULL DEFAULT '0',
1927 `reference` char(40) NOT NULL DEFAULT '',
1928 `qty` double NOT NULL DEFAULT '1',
1929 `standard_cost` double NOT NULL DEFAULT '0',
1930 PRIMARY KEY (`trans_id`),
1931 KEY `type` (`type`,`trans_no`),
1932 KEY `Move` (`stock_id`,`loc_code`,`tran_date`)
1933 ) ENGINE=InnoDB AUTO_INCREMENT=16 ;
1936 -- Dumping data for table `0_stock_moves`
1939 INSERT INTO `0_stock_moves` VALUES
1940 ('1', '1', '101', '25', 'DEF', '2017-05-05', '200', '', '100', '200'),
1941 ('2', '1', '102', '25', 'DEF', '2017-05-05', '150', '', '100', '150'),
1942 ('3', '1', '103', '25', 'DEF', '2017-05-05', '10', '', '100', '10'),
1943 ('4', '1', '101', '13', 'DEF', '2017-05-10', '300', 'auto', '-20', '200'),
1944 ('5', '1', '301', '13', 'DEF', '2017-05-10', '80', 'auto', '-3', '0'),
1945 ('6', '1', '101', '29', 'DEF', '2017-05-05', '200', '001/2017', '-2', '200'),
1946 ('7', '1', '102', '29', 'DEF', '2017-05-05', '150', '001/2017', '-2', '150'),
1947 ('8', '1', '103', '29', 'DEF', '2017-05-05', '10', '001/2017', '-2', '10'),
1948 ('9', '1', '301', '29', 'DEF', '2017-05-05', '0', '001/2017', '-2', '0'),
1949 ('10', '1', '201', '26', 'DEF', '2017-05-05', '0', '001/2017', '2', '360'),
1950 ('11', '2', '101', '25', 'DEF', '2017-05-05', '200', '', '15', '200'),
1951 ('12', '2', '101', '13', 'DEF', '2017-05-07', '300', 'auto', '-1', '200'),
1952 ('13', '3', '102', '13', 'DEF', '2017-05-07', '222.62', 'auto', '-1', '150'),
1953 ('14', '3', '103', '13', 'DEF', '2017-05-07', '44.52', 'auto', '-1', '10'),
1954 ('15', '4', '202', '13', 'DEF', '2017-05-07', '0', 'auto', '-5', '0');
1956 -- --------------------------------------------------------
1959 -- Table structure for table `0_suppliers`
1962 DROP TABLE IF EXISTS `0_suppliers`;
1963 CREATE TABLE IF NOT EXISTS `0_suppliers` (
1964 `supplier_id` int(11) NOT NULL AUTO_INCREMENT,
1965 `supp_name` varchar(60) NOT NULL DEFAULT '',
1966 `supp_ref` varchar(30) NOT NULL DEFAULT '',
1967 `address` tinytext NOT NULL,
1968 `supp_address` tinytext NOT NULL,
1969 `gst_no` varchar(25) NOT NULL DEFAULT '',
1970 `contact` varchar(60) NOT NULL DEFAULT '',
1971 `supp_account_no` varchar(40) NOT NULL DEFAULT '',
1972 `website` varchar(100) NOT NULL DEFAULT '',
1973 `bank_account` varchar(60) NOT NULL DEFAULT '',
1974 `curr_code` char(3) DEFAULT NULL,
1975 `payment_terms` int(11) DEFAULT NULL,
1976 `tax_included` tinyint(1) NOT NULL DEFAULT '0',
1977 `dimension_id` int(11) DEFAULT '0',
1978 `dimension2_id` int(11) DEFAULT '0',
1979 `tax_group_id` int(11) DEFAULT NULL,
1980 `credit_limit` double NOT NULL DEFAULT '0',
1981 `purchase_account` varchar(15) NOT NULL DEFAULT '',
1982 `payable_account` varchar(15) NOT NULL DEFAULT '',
1983 `payment_discount_account` varchar(15) NOT NULL DEFAULT '',
1984 `notes` tinytext NOT NULL,
1985 `inactive` tinyint(1) NOT NULL DEFAULT '0',
1986 PRIMARY KEY (`supplier_id`),
1987 KEY `supp_ref` (`supp_ref`)
1988 ) ENGINE=InnoDB AUTO_INCREMENT=3 ;
1991 -- Dumping data for table `0_suppliers`
1994 INSERT INTO `0_suppliers` VALUES
1995 ('1', 'Dino Saurius Inc.', 'Dino Saurius', 'N/A', '', '987654321', '', '', '', '', 'USD', '3', '0', '0', '0', '1', '0', '', '2100', '5060', '', '0'),
1996 ('2', 'Beefeater Ltd.', 'Beefeater', 'N/A', '', '67565590', '', '', '', '', 'GBP', '4', '0', '0', '0', '1', '0', '', '2100', '5060', '', '0');
1998 -- --------------------------------------------------------
2001 -- Table structure for table `0_supp_allocations`
2004 DROP TABLE IF EXISTS `0_supp_allocations`;
2005 CREATE TABLE IF NOT EXISTS `0_supp_allocations` (
2006 `id` int(11) NOT NULL AUTO_INCREMENT,
2007 `person_id` int(11) DEFAULT NULL,
2008 `amt` double unsigned DEFAULT NULL,
2009 `date_alloc` date NOT NULL DEFAULT '0000-00-00',
2010 `trans_no_from` int(11) DEFAULT NULL,
2011 `trans_type_from` int(11) DEFAULT NULL,
2012 `trans_no_to` int(11) DEFAULT NULL,
2013 `trans_type_to` int(11) DEFAULT NULL,
2015 UNIQUE KEY `trans_type_from` (`person_id`,`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`),
2016 KEY `From` (`trans_type_from`,`trans_no_from`),
2017 KEY `To` (`trans_type_to`,`trans_no_to`)
2021 -- Dumping data for table `0_supp_allocations`
2024 -- --------------------------------------------------------
2027 -- Table structure for table `0_supp_invoice_items`
2030 DROP TABLE IF EXISTS `0_supp_invoice_items`;
2031 CREATE TABLE IF NOT EXISTS `0_supp_invoice_items` (
2032 `id` int(11) NOT NULL AUTO_INCREMENT,
2033 `supp_trans_no` int(11) DEFAULT NULL,
2034 `supp_trans_type` int(11) DEFAULT NULL,
2035 `gl_code` varchar(15) NOT NULL DEFAULT '',
2036 `grn_item_id` int(11) DEFAULT NULL,
2037 `po_detail_item_id` int(11) DEFAULT NULL,
2038 `stock_id` varchar(20) NOT NULL DEFAULT '',
2039 `description` tinytext,
2040 `quantity` double NOT NULL DEFAULT '0',
2041 `unit_price` double NOT NULL DEFAULT '0',
2042 `unit_tax` double NOT NULL DEFAULT '0',
2044 `dimension_id` int(11) NOT NULL DEFAULT '0',
2045 `dimension2_id` int(11) NOT NULL DEFAULT '0',
2047 KEY `Transaction` (`supp_trans_type`,`supp_trans_no`,`stock_id`)
2048 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
2051 -- Dumping data for table `0_supp_invoice_items`
2054 INSERT INTO `0_supp_invoice_items` VALUES ('1', '1', '20', '0', '4', '4', '101', 'iPad Air 2 16GB', '15', '200', '10', NULL, '0', '0');
2056 -- --------------------------------------------------------
2059 -- Table structure for table `0_supp_trans`
2062 DROP TABLE IF EXISTS `0_supp_trans`;
2063 CREATE TABLE IF NOT EXISTS `0_supp_trans` (
2064 `trans_no` int(11) unsigned NOT NULL DEFAULT '0',
2065 `type` smallint(6) unsigned NOT NULL DEFAULT '0',
2066 `supplier_id` int(11) unsigned NOT NULL,
2067 `reference` tinytext NOT NULL,
2068 `supp_reference` varchar(60) NOT NULL DEFAULT '',
2069 `tran_date` date NOT NULL DEFAULT '0000-00-00',
2070 `due_date` date NOT NULL DEFAULT '0000-00-00',
2071 `ov_amount` double NOT NULL DEFAULT '0',
2072 `ov_discount` double NOT NULL DEFAULT '0',
2073 `ov_gst` double NOT NULL DEFAULT '0',
2074 `rate` double NOT NULL DEFAULT '1',
2075 `alloc` double NOT NULL DEFAULT '0',
2076 `tax_included` tinyint(1) NOT NULL DEFAULT '0',
2077 PRIMARY KEY (`type`,`trans_no`,`supplier_id`),
2078 KEY `supplier_id` (`supplier_id`),
2079 KEY `tran_date` (`tran_date`)
2083 -- Dumping data for table `0_supp_trans`
2087 INSERT INTO `0_supp_trans` VALUES ('1', '20', '1', '001/2017', 'rr4', '2017-05-05', '2017-05-15', '3000', '0', '150', '1', '0', '0');
2089 -- --------------------------------------------------------
2092 -- Table structure for table `0_sys_prefs`
2095 DROP TABLE IF EXISTS `0_sys_prefs`;
2096 CREATE TABLE IF NOT EXISTS `0_sys_prefs` (
2097 `name` varchar(35) NOT NULL DEFAULT '',
2098 `category` varchar(30) DEFAULT NULL,
2099 `type` varchar(20) NOT NULL DEFAULT '',
2100 `length` smallint(6) DEFAULT NULL,
2101 `value` text NOT NULL DEFAULT '',
2102 PRIMARY KEY (`name`),
2103 KEY `category` (`category`)
2107 -- Dumping data for table `0_sys_prefs`
2110 INSERT INTO `0_sys_prefs` VALUES ('coy_name', 'setup.company', 'varchar', 60, 'Training Co');
2111 INSERT INTO `0_sys_prefs` VALUES ('gst_no', 'setup.company', 'varchar', 25, '33445566');
2112 INSERT INTO `0_sys_prefs` VALUES ('coy_no', 'setup.company', 'varchar', 25, '');
2113 INSERT INTO `0_sys_prefs` VALUES ('tax_prd', 'setup.company', 'int', 11, '1');
2114 INSERT INTO `0_sys_prefs` VALUES ('tax_last', 'setup.company', 'int', 11, '1');
2115 INSERT INTO `0_sys_prefs` VALUES ('postal_address', 'setup.company', 'tinytext', 0, 'N/A');
2116 INSERT INTO `0_sys_prefs` VALUES ('phone', 'setup.company', 'varchar', 30, '');
2117 INSERT INTO `0_sys_prefs` VALUES ('fax', 'setup.company', 'varchar', 30, '');
2118 INSERT INTO `0_sys_prefs` VALUES ('email', 'setup.company', 'varchar', 100, 'delta@delta.com');
2119 INSERT INTO `0_sys_prefs` VALUES ('coy_logo', 'setup.company', 'varchar', 100, 'logo_frontaccounting.jpg');
2120 INSERT INTO `0_sys_prefs` VALUES ('domicile', 'setup.company', 'varchar', 55, '');
2121 INSERT INTO `0_sys_prefs` VALUES ('curr_default', 'setup.company', 'char', 3, 'USD');
2122 INSERT INTO `0_sys_prefs` VALUES ('use_dimension', 'setup.company', 'tinyint', 1, '1');
2123 INSERT INTO `0_sys_prefs` VALUES ('f_year', 'setup.company', 'int', 11, '2');
2124 INSERT INTO `0_sys_prefs` VALUES ('shortname_name_in_list','setup.company', 'tinyint', 1, '0');
2125 INSERT INTO `0_sys_prefs` VALUES ('no_item_list', 'setup.company', 'tinyint', 1, '0');
2126 INSERT INTO `0_sys_prefs` VALUES ('no_customer_list', 'setup.company', 'tinyint', 1, '0');
2127 INSERT INTO `0_sys_prefs` VALUES ('no_supplier_list', 'setup.company', 'tinyint', 1, '0');
2128 INSERT INTO `0_sys_prefs` VALUES ('base_sales', 'setup.company', 'int', 11, '1');
2129 INSERT INTO `0_sys_prefs` VALUES ('time_zone', 'setup.company', 'tinyint', 1, '0');
2130 INSERT INTO `0_sys_prefs` VALUES ('add_pct', 'setup.company', 'int', 5, '-1');
2131 INSERT INTO `0_sys_prefs` VALUES ('round_to', 'setup.company', 'int', 5, '1');
2132 INSERT INTO `0_sys_prefs` VALUES ('login_tout', 'setup.company', 'smallint', 6, '600');
2133 INSERT INTO `0_sys_prefs` VALUES ('past_due_days', 'glsetup.general', 'int', 11, '30');
2134 INSERT INTO `0_sys_prefs` VALUES ('profit_loss_year_act', 'glsetup.general', 'varchar', 15, '9990');
2135 INSERT INTO `0_sys_prefs` VALUES ('retained_earnings_act', 'glsetup.general', 'varchar', 15, '3590');
2136 INSERT INTO `0_sys_prefs` VALUES ('bank_charge_act', 'glsetup.general', 'varchar', 15, '5690');
2137 INSERT INTO `0_sys_prefs` VALUES ('exchange_diff_act', 'glsetup.general', 'varchar', 15, '4450');
2138 INSERT INTO `0_sys_prefs` VALUES ('tax_algorithm', 'glsetup.customer', 'tinyint', 1, '1');
2139 INSERT INTO `0_sys_prefs` VALUES ('default_credit_limit', 'glsetup.customer', 'int', 11, '1000');
2140 INSERT INTO `0_sys_prefs` VALUES ('accumulate_shipping', 'glsetup.customer', 'tinyint', 1, '0');
2141 INSERT INTO `0_sys_prefs` VALUES ('legal_text', 'glsetup.customer', 'tinytext', 0, '');
2142 INSERT INTO `0_sys_prefs` VALUES ('freight_act', 'glsetup.customer', 'varchar', 15, '4430');
2143 INSERT INTO `0_sys_prefs` VALUES ('debtors_act', 'glsetup.sales', 'varchar', 15, '1200');
2144 INSERT INTO `0_sys_prefs` VALUES ('default_sales_act', 'glsetup.sales', 'varchar', 15, '4010');
2145 INSERT INTO `0_sys_prefs` VALUES ('default_sales_discount_act', 'glsetup.sales', 'varchar', 15, '4510');
2146 INSERT INTO `0_sys_prefs` VALUES ('default_prompt_payment_act', 'glsetup.sales', 'varchar', 15, '4500');
2147 INSERT INTO `0_sys_prefs` VALUES ('default_delivery_required', 'glsetup.sales', 'smallint', 6, '1');
2148 INSERT INTO `0_sys_prefs` VALUES ('default_receival_required', 'glsetup.purchase', 'smallint', 6, '10');
2149 INSERT INTO `0_sys_prefs` VALUES ('default_quote_valid_days', 'glsetup.sales', 'smallint', 6, '30');
2150 INSERT INTO `0_sys_prefs` VALUES ('default_dim_required', 'glsetup.dims', 'int', 11, '20');
2151 INSERT INTO `0_sys_prefs` VALUES ('pyt_discount_act', 'glsetup.purchase', 'varchar', 15, '5060');
2152 INSERT INTO `0_sys_prefs` VALUES ('creditors_act', 'glsetup.purchase', 'varchar', 15, '2100');
2153 INSERT INTO `0_sys_prefs` VALUES ('po_over_receive', 'glsetup.purchase', 'int', 11, '10');
2154 INSERT INTO `0_sys_prefs` VALUES ('po_over_charge', 'glsetup.purchase', 'int', 11, '10');
2155 INSERT INTO `0_sys_prefs` VALUES ('allow_negative_stock', 'glsetup.inventory', 'tinyint', 1, '0');
2156 INSERT INTO `0_sys_prefs` VALUES ('default_inventory_act', 'glsetup.items', 'varchar', 15, '1510');
2157 INSERT INTO `0_sys_prefs` VALUES ('default_cogs_act', 'glsetup.items', 'varchar', 15, '5010');
2158 INSERT INTO `0_sys_prefs` VALUES ('default_adj_act', 'glsetup.items', 'varchar', 15, '5040');
2159 INSERT INTO `0_sys_prefs` VALUES ('default_inv_sales_act', 'glsetup.items', 'varchar', 15, '4010');
2160 INSERT INTO `0_sys_prefs` VALUES ('default_wip_act', 'glsetup.items', 'varchar', 15, '1530');
2161 INSERT INTO `0_sys_prefs` VALUES ('default_workorder_required', 'glsetup.manuf', 'int', 11, '20');
2162 INSERT INTO `0_sys_prefs` VALUES ('version_id', 'system', 'varchar', 11, '2.4.1');
2163 INSERT INTO `0_sys_prefs` VALUES ('auto_curr_reval', 'setup.company', 'smallint', 6, '1');
2164 INSERT INTO `0_sys_prefs` VALUES ('grn_clearing_act', 'glsetup.purchase', 'varchar', 15, '1550');
2165 INSERT INTO `0_sys_prefs` VALUES ('bcc_email', 'setup.company', 'varchar', 100, '');
2166 INSERT INTO `0_sys_prefs` VALUES ('deferred_income_act', 'glsetup.sales', 'varchar', '15', '2105');
2167 INSERT INTO `0_sys_prefs` VALUES ('gl_closing_date','setup.closing_date', 'date', 8, '');
2168 INSERT INTO `0_sys_prefs` VALUES ('alternative_tax_include_on_docs','setup.company', 'tinyint', 1, '0');
2169 INSERT INTO `0_sys_prefs` VALUES ('no_zero_lines_amount','glsetup.sales', 'tinyint', 1, '1');
2170 INSERT INTO `0_sys_prefs` VALUES ('show_po_item_codes','glsetup.purchase', 'tinyint', 1, '0');
2171 INSERT INTO `0_sys_prefs` VALUES ('accounts_alpha','glsetup.general', 'tinyint', 1, '0');
2172 INSERT INTO `0_sys_prefs` VALUES ('loc_notification','glsetup.inventory', 'tinyint', 1, '0');
2173 INSERT INTO `0_sys_prefs` VALUES ('print_invoice_no','glsetup.sales', 'tinyint', 1, '0');
2174 INSERT INTO `0_sys_prefs` VALUES ('allow_negative_prices','glsetup.inventory', 'tinyint', 1, '1');
2175 INSERT INTO `0_sys_prefs` VALUES ('print_item_images_on_quote','glsetup.inventory', 'tinyint', 1, '0');
2176 INSERT INTO `0_sys_prefs` VALUES ('suppress_tax_rates','setup.company', 'tinyint', 1, '0');
2177 INSERT INTO `0_sys_prefs` VALUES ('company_logo_report','setup.company', 'tinyint', 1, '0');
2178 INSERT INTO `0_sys_prefs` VALUES ('barcodes_on_stock','setup.company', 'tinyint', 1, '0');
2179 INSERT INTO `0_sys_prefs` VALUES ('ref_no_auto_increase','setup.company', 'tinyint', 1, '0');
2180 INSERT INTO `0_sys_prefs` VALUES ('default_loss_on_asset_disposal_act', 'glsetup.items', 'varchar', '15', '5660');
2181 INSERT INTO `0_sys_prefs` VALUES ('depreciation_period', 'glsetup.company', 'tinyint', '1', '1');
2182 INSERT INTO `0_sys_prefs` VALUES ('use_manufacturing','setup.company', 'tinyint', 1, '1');
2183 INSERT INTO `0_sys_prefs` VALUES ('use_fixed_assets','setup.company', 'tinyint', 1, '1');
2186 -- --------------------------------------------------------
2189 -- Table structure for table `0_tags`
2192 DROP TABLE IF EXISTS `0_tags`;
2193 CREATE TABLE IF NOT EXISTS `0_tags` (
2194 `id` int(11) NOT NULL AUTO_INCREMENT,
2195 `type` smallint(6) NOT NULL,
2196 `name` varchar(30) NOT NULL,
2197 `description` varchar(60) DEFAULT NULL,
2198 `inactive` tinyint(1) NOT NULL DEFAULT '0',
2200 UNIQUE KEY `type` (`type`,`name`)
2204 -- Dumping data for table `0_tags`
2207 -- --------------------------------------------------------
2210 -- Table structure for table `0_tag_associations`
2213 DROP TABLE IF EXISTS `0_tag_associations`;
2214 CREATE TABLE IF NOT EXISTS `0_tag_associations` (
2215 `record_id` varchar(15) NOT NULL,
2216 `tag_id` int(11) NOT NULL,
2217 UNIQUE KEY `record_id` (`record_id`,`tag_id`)
2221 -- Dumping data for table `0_tag_associations`
2224 -- --------------------------------------------------------
2227 -- Table structure for table `0_tax_groups`
2230 DROP TABLE IF EXISTS `0_tax_groups`;
2231 CREATE TABLE IF NOT EXISTS `0_tax_groups` (
2232 `id` int(11) NOT NULL AUTO_INCREMENT,
2233 `name` varchar(60) NOT NULL DEFAULT '',
2234 `inactive` tinyint(1) NOT NULL DEFAULT '0',
2236 UNIQUE KEY `name` (`name`)
2237 ) ENGINE=InnoDB AUTO_INCREMENT=3 ;
2240 -- Dumping data for table `0_tax_groups`
2243 INSERT INTO `0_tax_groups` VALUES (1, 'Tax', 0);
2244 INSERT INTO `0_tax_groups` VALUES (2, 'Tax Exempt', 0);
2246 -- --------------------------------------------------------
2249 -- Table structure for table `0_tax_group_items`
2252 DROP TABLE IF EXISTS `0_tax_group_items`;
2253 CREATE TABLE IF NOT EXISTS `0_tax_group_items` (
2254 `tax_group_id` int(11) NOT NULL DEFAULT '0',
2255 `tax_type_id` int(11) NOT NULL DEFAULT '0',
2256 `tax_shipping` tinyint(1) NOT NULL DEFAULT '0',
2257 PRIMARY KEY (`tax_group_id`,`tax_type_id`)
2261 -- Dumping data for table `0_tax_group_items`
2264 INSERT INTO `0_tax_group_items` VALUES (1, 1, 1);
2266 -- --------------------------------------------------------
2269 -- Table structure for table `0_tax_types`
2272 DROP TABLE IF EXISTS `0_tax_types`;
2273 CREATE TABLE IF NOT EXISTS `0_tax_types` (
2274 `id` int(11) NOT NULL AUTO_INCREMENT,
2275 `rate` double NOT NULL DEFAULT '0',
2276 `sales_gl_code` varchar(15) NOT NULL DEFAULT '',
2277 `purchasing_gl_code` varchar(15) NOT NULL DEFAULT '',
2278 `name` varchar(60) NOT NULL DEFAULT '',
2279 `inactive` tinyint(1) NOT NULL DEFAULT '0',
2281 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
2284 -- Dumping data for table `0_tax_types`
2287 INSERT INTO `0_tax_types` VALUES (1, 5, '2150', '2150', 'Tax', 0);
2289 -- --------------------------------------------------------
2292 -- Table structure for table `0_trans_tax_details`
2295 DROP TABLE IF EXISTS `0_trans_tax_details`;
2296 CREATE TABLE IF NOT EXISTS `0_trans_tax_details` (
2297 `id` int(11) NOT NULL AUTO_INCREMENT,
2298 `trans_type` smallint(6) DEFAULT NULL,
2299 `trans_no` int(11) DEFAULT NULL,
2300 `tran_date` date NOT NULL,
2301 `tax_type_id` int(11) NOT NULL DEFAULT '0',
2302 `rate` double NOT NULL DEFAULT '0',
2303 `ex_rate` double NOT NULL DEFAULT '1',
2304 `included_in_price` tinyint(1) NOT NULL DEFAULT '0',
2305 `net_amount` double NOT NULL DEFAULT '0',
2306 `amount` double NOT NULL DEFAULT '0',
2308 `reg_type` tinyint(1) DEFAULT NULL,
2310 KEY `Type_and_Number` (`trans_type`,`trans_no`),
2311 KEY `tran_date` (`tran_date`)
2312 ) ENGINE=InnoDB AUTO_INCREMENT=8 ;
2315 -- Dumping data for table `0_trans_tax_details`
2318 INSERT INTO `0_trans_tax_details` VALUES
2319 ('1', '13', '1', '2017-05-10', '1', '5', '1', '1', '5942.86', '297.14', 'auto', NULL),
2320 ('2', '10', '1', '2017-05-10', '1', '5', '1', '1', '5942.86', '297.14', '001/2017', '0'),
2321 ('3', '20', '1', '2017-05-05', '1', '5', '1', '0', '3000', '150', 'rr4', '1'),
2322 ('4', '13', '2', '2017-05-07', '1', '5', '1', '1', '285.71', '14.29', 'auto', NULL),
2323 ('5', '10', '2', '2017-05-07', '1', '5', '1', '1', '285.71', '14.29', '002/2017', '0'),
2324 ('6', '13', '3', '2017-05-07', '0', '0', '1.123', '1', '267.14', '0', 'auto', NULL),
2325 ('7', '10', '3', '2017-05-07', '0', '0', '1.123', '1', '267.14', '0', '003/2017', '0');
2327 -- --------------------------------------------------------
2330 -- Table structure for table `0_useronline`
2333 DROP TABLE IF EXISTS `0_useronline`;
2334 CREATE TABLE IF NOT EXISTS `0_useronline` (
2335 `id` int(11) NOT NULL AUTO_INCREMENT,
2336 `timestamp` int(15) NOT NULL DEFAULT '0',
2337 `ip` varchar(40) NOT NULL DEFAULT '',
2338 `file` varchar(100) NOT NULL DEFAULT '',
2340 KEY `timestamp` (`timestamp`),
2345 -- Dumping data for table `0_useronline`
2348 -- --------------------------------------------------------
2351 -- Table structure for table `0_users`
2354 DROP TABLE IF EXISTS `0_users`;
2355 CREATE TABLE IF NOT EXISTS `0_users` (
2356 `id` smallint(6) NOT NULL AUTO_INCREMENT,
2357 `user_id` varchar(60) NOT NULL DEFAULT '',
2358 `password` varchar(100) NOT NULL DEFAULT '',
2359 `real_name` varchar(100) NOT NULL DEFAULT '',
2360 `role_id` int(11) NOT NULL DEFAULT '1',
2361 `phone` varchar(30) NOT NULL DEFAULT '',
2362 `email` varchar(100) DEFAULT NULL,
2363 `language` varchar(20) DEFAULT NULL,
2364 `date_format` tinyint(1) NOT NULL DEFAULT '0',
2365 `date_sep` tinyint(1) NOT NULL DEFAULT '0',
2366 `tho_sep` tinyint(1) NOT NULL DEFAULT '0',
2367 `dec_sep` tinyint(1) NOT NULL DEFAULT '0',
2368 `theme` varchar(20) NOT NULL DEFAULT 'default',
2369 `page_size` varchar(20) NOT NULL DEFAULT 'A4',
2370 `prices_dec` smallint(6) NOT NULL DEFAULT '2',
2371 `qty_dec` smallint(6) NOT NULL DEFAULT '2',
2372 `rates_dec` smallint(6) NOT NULL DEFAULT '4',
2373 `percent_dec` smallint(6) NOT NULL DEFAULT '1',
2374 `show_gl` tinyint(1) NOT NULL DEFAULT '1',
2375 `show_codes` tinyint(1) NOT NULL DEFAULT '0',
2376 `show_hints` tinyint(1) NOT NULL DEFAULT '0',
2377 `last_visit_date` datetime DEFAULT NULL,
2378 `query_size` tinyint(1) unsigned NOT NULL DEFAULT '10',
2379 `graphic_links` tinyint(1) DEFAULT '1',
2380 `pos` smallint(6) DEFAULT '1',
2381 `print_profile` varchar(30) NOT NULL DEFAULT '',
2382 `rep_popup` tinyint(1) DEFAULT '1',
2383 `sticky_doc_date` tinyint(1) DEFAULT '0',
2384 `startup_tab` varchar(20) NOT NULL DEFAULT '',
2385 `transaction_days` smallint(6) NOT NULL DEFAULT '30',
2386 `save_report_selections` smallint(6) NOT NULL DEFAULT '0',
2387 `use_date_picker` tinyint(1) NOT NULL DEFAULT '1',
2388 `def_print_destination` tinyint(1) NOT NULL DEFAULT '0',
2389 `def_print_orientation` tinyint(1) NOT NULL DEFAULT '0',
2390 `inactive` tinyint(1) NOT NULL DEFAULT '0',
2392 UNIQUE KEY `user_id` (`user_id`)
2393 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
2396 -- Dumping data for table `0_users`
2399 INSERT INTO `0_users` VALUES (1, 'admin', '5f4dcc3b5aa765d61d8327deb882cf99', 'Administrator', 2, '', 'adm@example.com', 'C', 0, 0, 0, 0, 'default', 'Letter', 2, 2, 4, 1, 1, 0, 0, '2017-05-07 13:58:33', 10, 1, 1, '1', 1, 0, 'orders', 30, 0, 1, 0, 0, 0);
2401 -- --------------------------------------------------------
2404 -- Table structure for table `0_voided`
2407 DROP TABLE IF EXISTS `0_voided`;
2408 CREATE TABLE IF NOT EXISTS `0_voided` (
2409 `type` int(11) NOT NULL DEFAULT '0',
2410 `id` int(11) NOT NULL DEFAULT '0',
2411 `date_` date NOT NULL DEFAULT '0000-00-00',
2412 `memo_` tinytext NOT NULL,
2413 UNIQUE KEY `id` (`type`,`id`)
2417 -- Dumping data for table `0_voided`
2420 -- --------------------------------------------------------
2423 -- Table structure for table `0_workcentres`
2426 DROP TABLE IF EXISTS `0_workcentres`;
2427 CREATE TABLE IF NOT EXISTS `0_workcentres` (
2428 `id` int(11) NOT NULL AUTO_INCREMENT,
2429 `name` char(40) NOT NULL DEFAULT '',
2430 `description` char(50) NOT NULL DEFAULT '',
2431 `inactive` tinyint(1) NOT NULL DEFAULT '0',
2433 UNIQUE KEY `name` (`name`)
2434 ) ENGINE=InnoDB AUTO_INCREMENT=2 ;
2437 -- Dumping data for table `0_workcentres`
2440 INSERT INTO `0_workcentres` VALUES ('1', 'Work Centre', '', '0');
2442 -- --------------------------------------------------------
2445 -- Table structure for table `0_workorders`
2448 DROP TABLE IF EXISTS `0_workorders`;
2449 CREATE TABLE IF NOT EXISTS `0_workorders` (
2450 `id` int(11) NOT NULL AUTO_INCREMENT,
2451 `wo_ref` varchar(60) NOT NULL DEFAULT '',
2452 `loc_code` varchar(5) NOT NULL DEFAULT '',
2453 `units_reqd` double NOT NULL DEFAULT '1',
2454 `stock_id` varchar(20) NOT NULL DEFAULT '',
2455 `date_` date NOT NULL DEFAULT '0000-00-00',
2456 `type` tinyint(4) NOT NULL DEFAULT '0',
2457 `required_by` date NOT NULL DEFAULT '0000-00-00',
2458 `released_date` date NOT NULL DEFAULT '0000-00-00',
2459 `units_issued` double NOT NULL DEFAULT '0',
2460 `closed` tinyint(1) NOT NULL DEFAULT '0',
2461 `released` tinyint(1) NOT NULL DEFAULT '0',
2462 `additional_costs` double NOT NULL DEFAULT '0',
2464 UNIQUE KEY `wo_ref` (`wo_ref`)
2465 ) ENGINE=InnoDB AUTO_INCREMENT=4 ;
2468 -- Dumping data for table `0_workorders`
2471 INSERT INTO `0_workorders` VALUES
2472 ('1', '001/2017', 'DEF', '2', '201', '2017-05-05', '0', '2017-05-05', '2017-05-05', '2', '1', '1', '0'),
2473 ('2', '002/2017', 'DEF', '5', '201', '2017-05-07', '2', '2017-05-27', '2017-05-07', '0', '0', '1', '0'),
2474 ('3', '003/2017', 'DEF', '5', '201', '2017-05-07', '2', '2017-05-27', '0000-00-00', '0', '0', '0', '0');
2476 -- --------------------------------------------------------
2479 -- Table structure for table `0_wo_costing`
2482 DROP TABLE IF EXISTS `0_wo_costing`;
2483 CREATE TABLE `0_wo_costing` (
2484 `id` int(11) NOT NULL AUTO_INCREMENT,
2485 `workorder_id` int(11) NOT NULL DEFAULT '0',
2486 `cost_type` tinyint(1) NOT NULL DEFAULT '0',
2487 `trans_type` int(11) NOT NULL DEFAULT '0',
2488 `trans_no` int(11) NOT NULL DEFAULT '0',
2489 `factor` double NOT NULL DEFAULT '1',
2494 -- Dumping data for table `0_wo_costing`
2497 -- --------------------------------------------------------
2500 -- Table structure for table `0_wo_issues`
2503 DROP TABLE IF EXISTS `0_wo_issues`;
2504 CREATE TABLE IF NOT EXISTS `0_wo_issues` (
2505 `issue_no` int(11) NOT NULL AUTO_INCREMENT,
2506 `workorder_id` int(11) NOT NULL DEFAULT '0',
2507 `reference` varchar(100) DEFAULT NULL,
2508 `issue_date` date DEFAULT NULL,
2509 `loc_code` varchar(5) DEFAULT NULL,
2510 `workcentre_id` int(11) DEFAULT NULL,
2511 PRIMARY KEY (`issue_no`),
2512 KEY `workorder_id` (`workorder_id`)
2516 -- Dumping data for table `0_wo_issues`
2519 -- --------------------------------------------------------
2522 -- Table structure for table `0_wo_issue_items`
2525 DROP TABLE IF EXISTS `0_wo_issue_items`;
2526 CREATE TABLE IF NOT EXISTS `0_wo_issue_items` (
2527 `id` int(11) NOT NULL AUTO_INCREMENT,
2528 `stock_id` varchar(40) DEFAULT NULL,
2529 `issue_id` int(11) DEFAULT NULL,
2530 `qty_issued` double DEFAULT NULL,
2531 `unit_cost` double NOT NULL default '0',
2536 -- Dumping data for table `0_wo_issue_items`
2539 -- --------------------------------------------------------
2542 -- Table structure for table `0_wo_manufacture`
2545 DROP TABLE IF EXISTS `0_wo_manufacture`;
2546 CREATE TABLE IF NOT EXISTS `0_wo_manufacture` (
2547 `id` int(11) NOT NULL AUTO_INCREMENT,
2548 `reference` varchar(100) DEFAULT NULL,
2549 `workorder_id` int(11) NOT NULL DEFAULT '0',
2550 `quantity` double NOT NULL DEFAULT '0',
2551 `date_` date NOT NULL DEFAULT '0000-00-00',
2553 KEY `workorder_id` (`workorder_id`)
2557 -- Dumping data for table `0_wo_manufacture`
2560 INSERT INTO `0_wo_manufacture` VALUES (1, '001/2017', 1, 2, '2017-05-05');
2562 -- --------------------------------------------------------
2565 -- Table structure for table `0_wo_requirements`
2568 DROP TABLE IF EXISTS `0_wo_requirements`;
2569 CREATE TABLE IF NOT EXISTS `0_wo_requirements` (
2570 `id` int(11) NOT NULL AUTO_INCREMENT,
2571 `workorder_id` int(11) NOT NULL DEFAULT '0',
2572 `stock_id` char(20) NOT NULL DEFAULT '',
2573 `workcentre` int(11) NOT NULL DEFAULT '0',
2574 `units_req` double NOT NULL DEFAULT '1',
2575 `unit_cost` double NOT NULL DEFAULT '0',
2576 `loc_code` char(5) NOT NULL DEFAULT '',
2577 `units_issued` double NOT NULL DEFAULT '0',
2579 KEY `workorder_id` (`workorder_id`)
2580 ) ENGINE=InnoDB AUTO_INCREMENT=9 ;
2583 -- Dumping data for table `0_wo_requirements`
2586 INSERT INTO `0_wo_requirements` VALUES
2587 ('1', '1', '101', '1', '1', '200', 'DEF', '2'),
2588 ('2', '1', '102', '1', '1', '150', 'DEF', '2'),
2589 ('3', '1', '103', '1', '1', '10', 'DEF', '2'),
2590 ('4', '1', '301', '1', '1', '0', 'DEF', '2'),
2591 ('5', '2', '101', '1', '1', '200', 'DEF', '0'),
2592 ('6', '2', '102', '1', '1', '150', 'DEF', '0'),
2593 ('7', '2', '103', '1', '1', '10', 'DEF', '0'),
2594 ('8', '2', '301', '1', '1', '0', 'DEF', '0');