· 4 years ago · Aug 31, 2021, 01:42 PM
1CREATE TABLE IF NOT EXISTS `sma_addresses` (
2 `id` int(11) NOT NULL AUTO_INCREMENT,
3 `company_id` int(11) NOT NULL,
4 `line1` varchar(50) NOT NULL,
5 `line2` varchar(50) NULL,
6 `city` varchar(25) NOT NULL,
7 `postal_code` varchar(20) NULL,
8 `state` varchar(25) NOT NULL,
9 `country` varchar(50) NOT NULL,
10 `phone` varchar(50) NULL,
11 `updated_at` timestamp NOT NULL,
12 PRIMARY KEY (`id`),
13 KEY `company_id` (`company_id`)
14) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
15
16CREATE TABLE IF NOT EXISTS `sma_adjustments` (
17 `id` int(11) NOT NULL AUTO_INCREMENT,
18 `date` timestamp NOT NULL,
19 `reference_no` varchar(55) NOT NULL,
20 `warehouse_id` int(11) NOT NULL,
21 `note` text NULL,
22 `attachment` VARCHAR(55) NULL,
23 `created_by` int(11) NOT NULL,
24 `updated_by` int(11) NULL,
25 `updated_at` datetime NULL,
26 `count_id` INT NULL,
27 PRIMARY KEY (`id`),
28 KEY `warehouse_id` (`warehouse_id`)
29) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
30
31CREATE TABLE IF NOT EXISTS `sma_adjustment_items` (
32 `id` int(11) NOT NULL AUTO_INCREMENT,
33 `adjustment_id` INT NOT NULL,
34 `product_id` int(11) NOT NULL,
35 `option_id` int(11) DEFAULT NULL,
36 `quantity` decimal(15,4) NOT NULL,
37 `warehouse_id` int(11) NOT NULL,
38 `serial_no` VARCHAR(255) NULL DEFAULT NULL,
39 `type` VARCHAR(20) NOT NULL,
40 PRIMARY KEY (`id`),
41 KEY `adjustment_id` (`adjustment_id`)
42) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
43
44CREATE TABLE IF NOT EXISTS `sma_attachments` (
45 `id` int(11) NOT NULL AUTO_INCREMENT,
46 `subject_id` int(11) NOT NULL,
47 `subject_type` varchar(55) NOT NULL,
48 `file_name` varchar(100) NOT NULL,
49 `orig_name` varchar(100) NULL,
50 PRIMARY KEY (`id`)
51) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
52
53CREATE TABLE IF NOT EXISTS `sma_brands` (
54 `id` int(11) NOT NULL AUTO_INCREMENT,
55 `code` varchar(20) NULL,
56 `name` varchar(50) NOT NULL,
57 `image` varchar(50) NULL,
58 `slug` VARCHAR(55) NULL,
59 `description` VARCHAR(255) NULL,
60 PRIMARY KEY (`id`),
61 KEY `name` (`name`)
62) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
63
64CREATE TABLE IF NOT EXISTS `sma_calendar` (
65 `id` int(11) NOT NULL AUTO_INCREMENT,
66 `title` varchar(55) NOT NULL,
67 `description` varchar(255) DEFAULT NULL,
68 `start` datetime NOT NULL,
69 `end` datetime DEFAULT NULL,
70 `color` varchar(7) NOT NULL,
71 `user_id` int(11) DEFAULT NULL,
72 PRIMARY KEY (`id`)
73) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
74
75CREATE TABLE IF NOT EXISTS `sma_captcha` (
76 `captcha_id` bigint(13) unsigned NOT NULL AUTO_INCREMENT,
77 `captcha_time` int(10) unsigned NOT NULL,
78 `ip_address` varchar(16) CHARACTER SET latin1 NOT NULL DEFAULT '0',
79 `word` varchar(20) CHARACTER SET latin1 NOT NULL,
80 PRIMARY KEY (`captcha_id`),
81 KEY `word` (`word`)
82) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
83
84CREATE TABLE IF NOT EXISTS `sma_categories` (
85 `id` int(11) NOT NULL AUTO_INCREMENT,
86 `code` varchar(55) NOT NULL,
87 `name` varchar(55) NOT NULL,
88 `image` varchar(55) DEFAULT NULL,
89 `parent_id` INT(11) NULL,
90 `slug` VARCHAR(55) NULL,
91 `description` VARCHAR(255) NULL,
92 PRIMARY KEY (`id`),
93 KEY `id` (`id`)
94) DEFAULT CHARSET=utf8 AUTO_INCREMENT=2;
95
96INSERT INTO `sma_categories` (`id`, `code`, `name`, `image`) VALUES
97(1, 'C1', 'Category 1', NULL);
98
99CREATE TABLE IF NOT EXISTS `sma_combo_items` (
100 `id` int(11) NOT NULL AUTO_INCREMENT,
101 `product_id` int(11) NOT NULL,
102 `item_code` varchar(20) NOT NULL,
103 `quantity` decimal(12,4) NOT NULL,
104 `unit_price` decimal(25,4) NULL,
105 PRIMARY KEY (`id`)
106) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
107
108CREATE TABLE IF NOT EXISTS `sma_companies` (
109 `id` int(11) NOT NULL AUTO_INCREMENT,
110 `group_id` int(10) unsigned DEFAULT NULL,
111 `group_name` varchar(20) NOT NULL,
112 `customer_group_id` int(11) DEFAULT NULL,
113 `customer_group_name` varchar(100) DEFAULT NULL,
114 `name` varchar(55) NOT NULL,
115 `company` varchar(255) NOT NULL,
116 `vat_no` varchar(100) DEFAULT NULL,
117 `address` varchar(255) DEFAULT NULL,
118 `city` varchar(55) DEFAULT NULL,
119 `state` varchar(55) DEFAULT NULL,
120 `postal_code` varchar(8) DEFAULT NULL,
121 `country` varchar(100) DEFAULT NULL,
122 `phone` varchar(20) DEFAULT NULL,
123 `email` varchar(100) NOT NULL,
124 `cf1` varchar(100) DEFAULT NULL,
125 `cf2` varchar(100) DEFAULT NULL,
126 `cf3` varchar(100) DEFAULT NULL,
127 `cf4` varchar(100) DEFAULT NULL,
128 `cf5` varchar(100) DEFAULT NULL,
129 `cf6` varchar(100) DEFAULT NULL,
130 `invoice_footer` text,
131 `payment_term` int(11) DEFAULT '0',
132 `logo` varchar(255) DEFAULT 'logo.png',
133 `award_points` int NULL DEFAULT '0',
134 `deposit_amount` DECIMAL(25,4) NULL,
135 `price_group_id` INT(11) NULL,
136 `price_group_name` VARCHAR(50) NULL,
137 `gst_no` VARCHAR(100) NULL,
138 PRIMARY KEY (`id`),
139 KEY `group_id` (`group_id`),
140 KEY `group_id_2` (`group_id`)
141) DEFAULT CHARSET=utf8 AUTO_INCREMENT=4;
142
143INSERT INTO `sma_companies` (`id`, `group_id`, `group_name`, `customer_group_id`, `customer_group_name`, `name`, `company`, `vat_no`, `address`, `city`, `state`, `postal_code`, `country`, `phone`, `email`, `cf1`, `cf2`, `cf3`, `cf4`, `cf5`, `cf6`, `invoice_footer`, `payment_term`, `logo`) VALUES
144(1, 3, 'customer', 1, 'General', 'Walk-in Customer', 'Walk-in Customer', '', 'Customer Address', 'Petaling Jaya', 'Selangor', '46000', 'Malaysia', '0123456789', 'customer@tecdiary.com', '', '', '', '', '', '', NULL, 0, 'logo.png'),
145(2, 4, 'supplier', NULL, NULL, 'Test Supplier', 'Supplier Company Name', NULL, 'Supplier Address', 'Petaling Jaya', 'Selangor', '46050', 'Malaysia', '0123456789', 'supplier@tecdiary.com', '-', '-', '-', '-', '-', '-', NULL, 0, 'logo.png'),
146(3, NULL, 'biller', NULL, NULL, 'Mian Saleem', 'Test Biller', '5555', 'Biller adddress', 'City', '', '', 'Country', '012345678', 'saleem@tecdiary.com', '', '', '', '', '', '', ' Thank you for shopping with us. Please come again', 0, 'logo1.png');
147
148CREATE TABLE IF NOT EXISTS `sma_costing` (
149 `id` int(11) NOT NULL AUTO_INCREMENT,
150 `date` date NOT NULL,
151 `product_id` int(11) DEFAULT NULL,
152 `sale_item_id` int(11) NOT NULL,
153 `sale_id` int(11) DEFAULT NULL,
154 `purchase_item_id` int(11) DEFAULT NULL,
155 `quantity` decimal(15,4) NOT NULL,
156 `purchase_net_unit_cost` decimal(25,4) DEFAULT NULL,
157 `purchase_unit_cost` decimal(25,4) DEFAULT NULL,
158 `sale_net_unit_price` decimal(25,4) NOT NULL,
159 `sale_unit_price` decimal(25,4) NOT NULL,
160 `quantity_balance` decimal(15,4) DEFAULT NULL,
161 `inventory` tinyint(1) DEFAULT '0',
162 `overselling` tinyint(1) DEFAULT '0',
163 `option_id` INT NULL,
164 `purchase_id` int NULL,
165 `transfer_id` INT NULL,
166 PRIMARY KEY (`id`)
167) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
168
169CREATE TABLE IF NOT EXISTS `sma_currencies` (
170 `id` int(11) NOT NULL AUTO_INCREMENT,
171 `code` varchar(5) NOT NULL,
172 `name` varchar(55) NOT NULL,
173 `rate` decimal(12,4) NOT NULL,
174 `auto_update` tinyint(1) NOT NULL DEFAULT '0',
175 `symbol` VARCHAR(50) NULL,
176 PRIMARY KEY (`id`)
177) DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;
178
179INSERT INTO `sma_currencies` (`id`, `code`, `name`, `rate`, `auto_update`) VALUES
180(1, 'USD', 'US Dollar', '1.000', 0),
181(2, 'EUR', 'EURO', '0.734', 0);
182
183CREATE TABLE IF NOT EXISTS `sma_customer_groups` (
184 `id` int(11) NOT NULL AUTO_INCREMENT,
185 `name` varchar(100) NOT NULL,
186 `percent` int(11) NOT NULL,
187 `discount` tinyint NULL,
188 PRIMARY KEY (`id`)
189) DEFAULT CHARSET=utf8 AUTO_INCREMENT=5;
190
191INSERT INTO `sma_customer_groups` (`id`, `name`, `percent`) VALUES
192(1, 'General', 0),
193(2, 'Reseller', -5),
194(3, 'Distributor', -15),
195(4, 'New Customer (+10)', 10);
196
197CREATE TABLE IF NOT EXISTS `sma_date_format` (
198 `id` int(11) NOT NULL AUTO_INCREMENT,
199 `js` varchar(20) NOT NULL,
200 `php` varchar(20) NOT NULL,
201 `sql` varchar(20) DEFAULT NULL,
202 PRIMARY KEY (`id`)
203) DEFAULT CHARSET=utf8 AUTO_INCREMENT=7;
204
205INSERT INTO `sma_date_format` (`id`, `js`, `php`, `sql`) VALUES
206(1, 'mm-dd-yyyy', 'm-d-Y', '%m-%d-%Y'),
207(2, 'mm/dd/yyyy', 'm/d/Y', '%m/%d/%Y'),
208(3, 'mm.dd.yyyy', 'm.d.Y', '%m.%d.%Y'),
209(4, 'dd-mm-yyyy', 'd-m-Y', '%d-%m-%Y'),
210(5, 'dd/mm/yyyy', 'd/m/Y', '%d/%m/%Y'),
211(6, 'dd.mm.yyyy', 'd.m.Y', '%d.%m.%Y');
212
213CREATE TABLE IF NOT EXISTS `sma_deliveries` (
214 `id` int(11) NOT NULL AUTO_INCREMENT,
215 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
216 `sale_id` int(11) NOT NULL,
217 `do_reference_no` varchar(50) NOT NULL,
218 `sale_reference_no` varchar(50) NOT NULL,
219 `customer` varchar(55) NOT NULL,
220 `address` varchar(1000) NOT NULL,
221 `note` varchar(1000) DEFAULT NULL,
222 `status` VARCHAR(15) NULL,
223 `attachment` VARCHAR(50) NULL,
224 `delivered_by` VARCHAR(50) NULL,
225 `received_by` VARCHAR(50) NULL,
226 `created_by` int(11) DEFAULT NULL,
227 `updated_by` int(11) DEFAULT NULL,
228 `updated_at` timestamp NULL DEFAULT NULL,
229 PRIMARY KEY (`id`)
230) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
231
232CREATE TABLE IF NOT EXISTS `sma_deposits` (
233 `id` int(11) NOT NULL AUTO_INCREMENT,
234 `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
235 `company_id` INT(11) NOT NULL,
236 `amount` DECIMAL(25,4) NOT NULL,
237 `paid_by` VARCHAR(50) NULL,
238 `note` VARCHAR(255) NULL,
239 `created_by` INT NOT NULL,
240 `updated_by` INT NOT NULL,
241 `updated_at` DATETIME NULL,
242 PRIMARY KEY (`id`)
243) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
244
245CREATE TABLE IF NOT EXISTS `sma_expenses` (
246 `id` int(11) NOT NULL AUTO_INCREMENT,
247 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
248 `reference` varchar(50) NOT NULL,
249 `amount` decimal(25,4) NOT NULL,
250 `note` varchar(1000) DEFAULT NULL,
251 `created_by` varchar(55) NOT NULL,
252 `attachment` VARCHAR(55) NULL,
253 `category_id` INT NULL,
254 `warehouse_id` INT NULL,
255 PRIMARY KEY (`id`)
256) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
257
258CREATE TABLE IF NOT EXISTS `sma_expense_categories` (
259 `id` int(11) NOT NULL AUTO_INCREMENT,
260 `code` varchar(55) NOT NULL,
261 `name` varchar(55) NOT NULL,
262 PRIMARY KEY (`id`),
263 KEY `id` (`id`)
264) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
265
266CREATE TABLE IF NOT EXISTS `sma_gift_cards` (
267 `id` int(11) NOT NULL AUTO_INCREMENT,
268 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
269 `card_no` varchar(20) NOT NULL,
270 `value` decimal(25,4) NOT NULL,
271 `customer_id` int(11) DEFAULT NULL,
272 `customer` varchar(255) DEFAULT NULL,
273 `balance` decimal(25,4) NOT NULL,
274 `expiry` date DEFAULT NULL,
275 `created_by` varchar(55) NOT NULL,
276 PRIMARY KEY (`id`),
277 UNIQUE KEY `card_no` (`card_no`)
278) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
279
280CREATE TABLE IF NOT EXISTS `sma_gift_card_topups` (
281 `id` int(11) NOT NULL AUTO_INCREMENT,
282 `date` timestamp NOT NULL,
283 `card_id` int(11) NOT NULL,
284 `amount` decimal(15,4) NOT NULL,
285 `created_by` int(11) NOT NULL,
286 PRIMARY KEY (`id`),
287 KEY `card_id` (`card_id`)
288) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
289
290CREATE TABLE IF NOT EXISTS `sma_groups` (
291 `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
292 `name` varchar(20) NOT NULL,
293 `description` varchar(100) NOT NULL,
294 PRIMARY KEY (`id`)
295) DEFAULT CHARSET=utf8 AUTO_INCREMENT=6;
296
297INSERT INTO `sma_groups` (`id`, `name`, `description`) VALUES
298(1, 'owner', 'Owner'),
299(2, 'admin', 'Administrator'),
300(3, 'customer', 'Customer'),
301(4, 'supplier', 'Supplier'),
302(5, 'sales', 'Sales Staff');
303
304CREATE TABLE IF NOT EXISTS `sma_login_attempts` (
305 `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
306 `ip_address` varbinary(16) NOT NULL,
307 `login` varchar(100) NOT NULL,
308 `time` int(11) unsigned DEFAULT NULL,
309 PRIMARY KEY (`id`)
310) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
311
312CREATE TABLE IF NOT EXISTS `sma_migrations` (
313 `version` bigint(20) NOT NULL
314) DEFAULT CHARSET=utf8;
315INSERT INTO `sma_migrations` (`version`) VALUES (315);
316
317CREATE TABLE IF NOT EXISTS `sma_notifications` (
318 `id` int(11) NOT NULL AUTO_INCREMENT,
319 `comment` text NOT NULL,
320 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
321 `from_date` datetime DEFAULT NULL,
322 `till_date` datetime DEFAULT NULL,
323 `scope` tinyint(1) NOT NULL DEFAULT '3',
324 PRIMARY KEY (`id`)
325) DEFAULT CHARSET=utf8 AUTO_INCREMENT=2;
326
327INSERT INTO `sma_notifications` (`id`, `comment`, `date`, `from_date`, `till_date`, `scope`) VALUES
328(1, '<p>Thank you for purchasing Stock Manager Advance. Please do not forget to check the documentation in help folder. If you find any error/bug, please email to support@tecdiary.com with details. You can send us your valued suggestions/feedback too.</p><p>Please rate Stock Manager Advance on your download page of codecanyon.net</p>', '2014-08-15 06:00:57', '2015-01-01 00:00:00', '2017-01-01 00:00:00', 3);
329
330CREATE TABLE IF NOT EXISTS `sma_order_ref` (
331 `ref_id` int(11) NOT NULL AUTO_INCREMENT,
332 `date` date NOT NULL,
333 `so` int(11) NOT NULL DEFAULT '1',
334 `qu` int(11) NOT NULL DEFAULT '1',
335 `po` int(11) NOT NULL DEFAULT '1',
336 `to` int(11) NOT NULL DEFAULT '1',
337 `pos` int(11) NOT NULL DEFAULT '1',
338 `do` int(11) NOT NULL DEFAULT '1',
339 `pay` int(11) NOT NULL DEFAULT '1',
340 `re` int(11) NOT NULL DEFAULT '1',
341 `rep` int(11) NOT NULL DEFAULT '1',
342 `ex` int(11) NOT NULL DEFAULT '1',
343 `ppay` int(11) NOT NULL DEFAULT '1',
344 `qa` int(11) NULL DEFAULT '1',
345 PRIMARY KEY (`ref_id`)
346) DEFAULT CHARSET=utf8 AUTO_INCREMENT=2;
347
348INSERT INTO `sma_order_ref` (`ref_id`, `date`, `so`, `qu`, `po`, `to`, `pos`, `do`, `pay`, `re`, `rep`, `ex`, `ppay`) VALUES
349(1, '2015-03-01', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1);
350
351CREATE TABLE IF NOT EXISTS `sma_payments` (
352 `id` int(11) NOT NULL AUTO_INCREMENT,
353 `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
354 `sale_id` int(11) DEFAULT NULL,
355 `return_id` int(11) DEFAULT NULL,
356 `purchase_id` int(11) DEFAULT NULL,
357 `reference_no` varchar(50) NOT NULL,
358 `transaction_id` varchar(50) DEFAULT NULL,
359 `paid_by` varchar(20) NOT NULL,
360 `cheque_no` varchar(20) DEFAULT NULL,
361 `cc_no` varchar(20) DEFAULT NULL,
362 `cc_holder` varchar(25) DEFAULT NULL,
363 `cc_month` varchar(2) DEFAULT NULL,
364 `cc_year` varchar(4) DEFAULT NULL,
365 `cc_type` varchar(20) DEFAULT NULL,
366 `amount` decimal(25,4) NOT NULL,
367 `currency` varchar(3) DEFAULT NULL,
368 `created_by` int(11) NOT NULL,
369 `attachment` varchar(55) DEFAULT NULL,
370 `type` varchar(20) NOT NULL,
371 `note` varchar(1000) DEFAULT NULL,
372 `pos_paid` decimal(25,4) DEFAULT '0.00',
373 `pos_balance` decimal(25,4) DEFAULT '0.00',
374 `approval_code` VARCHAR(50) NULL,
375 PRIMARY KEY (`id`)
376) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
377
378CREATE TABLE IF NOT EXISTS `sma_paypal` (
379 `id` int(11) NOT NULL,
380 `active` tinyint(4) NOT NULL,
381 `account_email` varchar(255) NOT NULL,
382 `paypal_currency` varchar(3) NOT NULL DEFAULT 'USD',
383 `fixed_charges` decimal(25,4) NOT NULL DEFAULT '2.00',
384 `extra_charges_my` decimal(25,4) NOT NULL DEFAULT '3.90',
385 `extra_charges_other` decimal(25,4) NOT NULL DEFAULT '4.40',
386 PRIMARY KEY (`id`)
387) DEFAULT CHARSET=utf8;
388
389INSERT INTO `sma_paypal` (`id`, `active`, `account_email`, `paypal_currency`, `fixed_charges`, `extra_charges_my`, `extra_charges_other`) VALUES
390(1, 1, 'mypaypal@paypal.com', 'USD', '0.00', '0.00', '0.00');
391
392CREATE TABLE IF NOT EXISTS `sma_permissions` (
393 `id` int(11) NOT NULL AUTO_INCREMENT,
394 `group_id` int(11) NOT NULL,
395 `products-index` tinyint(1) NULL DEFAULT '0',
396 `products-add` tinyint(1) NULL DEFAULT '0',
397 `products-edit` tinyint(1) NULL DEFAULT '0',
398 `products-delete` tinyint(1) NULL DEFAULT '0',
399 `products-cost` TINYINT(1) NULL DEFAULT '0',
400 `products-price` TINYINT(1) NULL DEFAULT '0',
401 `quotes-index` tinyint(1) NULL DEFAULT '0',
402 `quotes-add` tinyint(1) NULL DEFAULT '0',
403 `quotes-edit` tinyint(1) NULL DEFAULT '0',
404 `quotes-pdf` tinyint(1) NULL DEFAULT '0',
405 `quotes-email` tinyint(1) NULL DEFAULT '0',
406 `quotes-delete` tinyint(1) NULL DEFAULT '0',
407 `sales-index` tinyint(1) NULL DEFAULT '0',
408 `sales-add` tinyint(1) NULL DEFAULT '0',
409 `sales-edit` tinyint(1) NULL DEFAULT '0',
410 `sales-pdf` tinyint(1) NULL DEFAULT '0',
411 `sales-email` tinyint(1) NULL DEFAULT '0',
412 `sales-delete` tinyint(1) NULL DEFAULT '0',
413 `purchases-index` tinyint(1) NULL DEFAULT '0',
414 `purchases-add` tinyint(1) NULL DEFAULT '0',
415 `purchases-edit` tinyint(1) NULL DEFAULT '0',
416 `purchases-pdf` tinyint(1) NULL DEFAULT '0',
417 `purchases-email` tinyint(1) NULL DEFAULT '0',
418 `purchases-delete` tinyint(1) NULL DEFAULT '0',
419 `transfers-index` tinyint(1) NULL DEFAULT '0',
420 `transfers-add` tinyint(1) NULL DEFAULT '0',
421 `transfers-edit` tinyint(1) NULL DEFAULT '0',
422 `transfers-pdf` tinyint(1) NULL DEFAULT '0',
423 `transfers-email` tinyint(1) NULL DEFAULT '0',
424 `transfers-delete` tinyint(1) NULL DEFAULT '0',
425 `customers-index` tinyint(1) NULL DEFAULT '0',
426 `customers-add` tinyint(1) NULL DEFAULT '0',
427 `customers-edit` tinyint(1) NULL DEFAULT '0',
428 `customers-delete` tinyint(1) NULL DEFAULT '0',
429 `suppliers-index` tinyint(1) NULL DEFAULT '0',
430 `suppliers-add` tinyint(1) NULL DEFAULT '0',
431 `suppliers-edit` tinyint(1) NULL DEFAULT '0',
432 `suppliers-delete` tinyint(1) NULL DEFAULT '0',
433 `sales-deliveries` tinyint(1) NULL DEFAULT '0',
434 `sales-add_delivery` tinyint(1) NULL DEFAULT '0',
435 `sales-edit_delivery` tinyint(1) NULL DEFAULT '0',
436 `sales-delete_delivery` tinyint(1) NULL DEFAULT '0',
437 `sales-email_delivery` tinyint(1) NULL DEFAULT '0',
438 `sales-pdf_delivery` tinyint(1) NULL DEFAULT '0',
439 `sales-gift_cards` tinyint(1) NULL DEFAULT '0',
440 `sales-add_gift_card` tinyint(1) NULL DEFAULT '0',
441 `sales-edit_gift_card` tinyint(1) NULL DEFAULT '0',
442 `sales-delete_gift_card` tinyint(1) NULL DEFAULT '0',
443 `pos-index` tinyint(1) NULL DEFAULT '0',
444 `sales-return_sales` tinyint(1) NULL DEFAULT '0',
445 `reports-index` tinyint(1) NULL DEFAULT '0',
446 `reports-warehouse_stock` tinyint(1) NULL DEFAULT '0',
447 `reports-quantity_alerts` tinyint(1) NULL DEFAULT '0',
448 `reports-expiry_alerts` tinyint(1) NULL DEFAULT '0',
449 `reports-products` tinyint(1) NULL DEFAULT '0',
450 `reports-daily_sales` tinyint(1) NULL DEFAULT '0',
451 `reports-monthly_sales` tinyint(1) NULL DEFAULT '0',
452 `reports-sales` tinyint(1) NULL DEFAULT '0',
453 `reports-payments` tinyint(1) NULL DEFAULT '0',
454 `reports-purchases` tinyint(1) NULL DEFAULT '0',
455 `reports-profit_loss` tinyint(1) NULL DEFAULT '0',
456 `reports-customers` tinyint(1) NULL DEFAULT '0',
457 `reports-suppliers` tinyint(1) NULL DEFAULT '0',
458 `reports-staff` tinyint(1) NULL DEFAULT '0',
459 `reports-register` tinyint(1) NULL DEFAULT '0',
460 `sales-payments` tinyint(1) NULL DEFAULT '0',
461 `purchases-payments` tinyint(1) NULL DEFAULT '0',
462 `purchases-expenses` tinyint(1) NULL DEFAULT '0',
463 `products-adjustments` TINYINT(1) NOT NULL DEFAULT '0',
464 `bulk_actions` TINYINT(1) NOT NULL DEFAULT '0',
465 `customers-deposits` TINYINT(1) NOT NULL DEFAULT '0',
466 `customers-delete_deposit` TINYINT(1) NOT NULL DEFAULT '0',
467 `products-barcode` TINYINT(1) NOT NULL DEFAULT '0',
468 `purchases-return_purchases` TINYINT(1) NOT NULL DEFAULT '0',
469 `reports-expenses` TINYINT(1) NOT NULL DEFAULT '0',
470 `reports-daily_purchases` TINYINT(1) NULL DEFAULT '0',
471 `reports-monthly_purchases` TINYINT(1) NULL DEFAULT '0',
472 `products-stock_count` TINYINT(1) NULL DEFAULT '0',
473 `edit_price` TINYINT(1) NULL DEFAULT '0',
474 `returns-index` TINYINT(1) NULL DEFAULT '0',
475 `returns-add` TINYINT(1) NULL DEFAULT '0',
476 `returns-edit` TINYINT(1) NULL DEFAULT '0',
477 `returns-delete` TINYINT(1) NULL DEFAULT '0',
478 `returns-email` TINYINT(1) NULL DEFAULT '0',
479 `returns-pdf` TINYINT(1) NULL DEFAULT '0',
480 `reports-tax` TINYINT(1) NULL DEFAULT '0',
481 PRIMARY KEY (`id`)
482) DEFAULT CHARSET=utf8 AUTO_INCREMENT=2;
483
484INSERT INTO `sma_permissions` (`id`, `group_id`, `products-index`, `products-add`, `products-edit`, `products-delete`, `quotes-index`, `quotes-add`, `quotes-edit`, `quotes-pdf`, `quotes-email`, `quotes-delete`, `sales-index`, `sales-add`, `sales-edit`, `sales-pdf`, `sales-email`, `sales-delete`, `purchases-index`, `purchases-add`, `purchases-edit`, `purchases-pdf`, `purchases-email`, `purchases-delete`, `transfers-index`, `transfers-add`, `transfers-edit`, `transfers-pdf`, `transfers-email`, `transfers-delete`, `customers-index`, `customers-add`, `customers-edit`, `customers-delete`, `suppliers-index`, `suppliers-add`, `suppliers-edit`, `suppliers-delete`, `sales-deliveries`, `sales-add_delivery`, `sales-edit_delivery`, `sales-delete_delivery`, `sales-email_delivery`, `sales-pdf_delivery`, `sales-gift_cards`, `sales-add_gift_card`, `sales-edit_gift_card`, `sales-delete_gift_card`, `pos-index`) VALUES
485(1, 5, 1, 0, 0, 0, 1, 1, 1, 1, 1, 0, 1, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 0, 0, 0, 0, 0, 1, 1, 1, 0, 0, 1, 1, 1, 0, 0, 1);
486
487CREATE TABLE IF NOT EXISTS `sma_products` (
488 `id` int(11) NOT NULL AUTO_INCREMENT,
489 `code` varchar(50) NOT NULL,
490 `name` varchar(255) NOT NULL,
491 `unit` int(11) DEFAULT NULL,
492 `cost` decimal(25,4) DEFAULT NULL,
493 `price` decimal(25,4) NOT NULL,
494 `alert_quantity` decimal(15,4) DEFAULT '20.00',
495 `image` varchar(255) DEFAULT 'no_image.png',
496 `category_id` int(11) NOT NULL,
497 `subcategory_id` int(11) DEFAULT NULL,
498 `cf1` varchar(255) DEFAULT NULL,
499 `cf2` varchar(255) DEFAULT NULL,
500 `cf3` varchar(255) DEFAULT NULL,
501 `cf4` varchar(255) DEFAULT NULL,
502 `cf5` varchar(255) DEFAULT NULL,
503 `cf6` varchar(255) DEFAULT NULL,
504 `quantity` decimal(15,4) DEFAULT '0.00',
505 `tax_rate` int(11) DEFAULT NULL,
506 `track_quantity` tinyint(1) DEFAULT '1',
507 `details` varchar(1000) DEFAULT NULL,
508 `warehouse` int(11) DEFAULT NULL,
509 `barcode_symbology` varchar(55) NOT NULL DEFAULT 'code128',
510 `file` varchar(100) DEFAULT NULL,
511 `product_details` text,
512 `tax_method` tinyint(1) DEFAULT '0',
513 `type` varchar(55) NOT NULL DEFAULT 'standard',
514 `supplier1` int(11) DEFAULT NULL,
515 `supplier1price` decimal(25,4) DEFAULT NULL,
516 `supplier2` int(11) DEFAULT NULL,
517 `supplier2price` decimal(25,4) DEFAULT NULL,
518 `supplier3` int(11) DEFAULT NULL,
519 `supplier3price` decimal(25,4) DEFAULT NULL,
520 `supplier4` int(11) DEFAULT NULL,
521 `supplier4price` decimal(25,4) DEFAULT NULL,
522 `supplier5` int(11) DEFAULT NULL,
523 `supplier5price` decimal(25,4) DEFAULT NULL,
524 `promotion` TINYINT(1) NULL DEFAULT 0,
525 `promo_price` DECIMAL(25,4) NULL DEFAULT NULL,
526 `start_date` DATE NULL,
527 `end_date` DATE NULL,
528 `supplier1_part_no` VARCHAR(50) NULL,
529 `supplier2_part_no` VARCHAR(50) NULL,
530 `supplier3_part_no` VARCHAR(50) NULL,
531 `supplier4_part_no` VARCHAR(50) NULL,
532 `supplier5_part_no` VARCHAR(50) NULL,
533 `sale_unit` INT(11) NULL,
534 `purchase_unit` INT(11) NULL,
535 `brand` INT NULL,
536 `slug` VARCHAR(55) NULL,
537 `featured` TINYINT(1) NULL,
538 `weight` DECIMAL(10,4) NULL,
539 `hsn_code` INT NULL,
540 `views` INT(11) NOT NULL DEFAULT '0',
541 `hide` TINYINT(1) NOT NULL DEFAULT '0',
542 `second_name` VARCHAR(255) NULL,
543 `hide_pos` TINYINT(1) NOT NULL DEFAULT '0',
544 PRIMARY KEY (`id`),
545 UNIQUE KEY `code` (`code`),
546 KEY `category_id` (`category_id`),
547 KEY `id` (`id`),
548 KEY `id_2` (`id`),
549 KEY `category_id_2` (`category_id`),
550 INDEX(`unit`),
551 INDEX(`brand`)
552) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
553
554CREATE TABLE IF NOT EXISTS `sma_price_groups` (
555 `id` int(11) NOT NULL AUTO_INCREMENT,
556 `name` varchar(50) NOT NULL,
557 PRIMARY KEY (`id`),
558 KEY `name` (`name`)
559) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
560INSERT INTO `sma_price_groups` (`name`) VALUES ('Default');
561
562CREATE TABLE IF NOT EXISTS `sma_printers` (
563 `id` int(11) NOT NULL AUTO_INCREMENT,
564 `title` varchar(55) NOT NULL,
565 `type` varchar(25) NOT NULL,
566 `profile` varchar(25) NOT NULL,
567 `char_per_line` TINYINT UNSIGNED NULL,
568 `path` varchar(255) DEFAULT NULL,
569 `ip_address` varbinary(45) DEFAULT NULL,
570 `port` varchar(10) DEFAULT NULL,
571 PRIMARY KEY (`id`)
572) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
573
574CREATE TABLE IF NOT EXISTS `sma_product_prices` (
575 `id` int(11) NOT NULL AUTO_INCREMENT,
576 `product_id` int(11) NOT NULL,
577 `price_group_id` int(11) NOT NULL,
578 `price` DECIMAL(25,4) NOT NULL,
579 PRIMARY KEY (`id`),
580 KEY `product_id` (`product_id`),
581 KEY `price_group_id` (`price_group_id`)
582) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
583
584CREATE TABLE IF NOT EXISTS `sma_product_photos` (
585 `id` int(11) NOT NULL AUTO_INCREMENT,
586 `product_id` int(11) NOT NULL,
587 `photo` varchar(100) NOT NULL,
588 PRIMARY KEY (`id`)
589) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
590
591CREATE TABLE IF NOT EXISTS `sma_product_variants` (
592 `id` int(11) NOT NULL AUTO_INCREMENT,
593 `product_id` int(11) NOT NULL,
594 `name` varchar(55) NOT NULL,
595 `cost` decimal(25,4) DEFAULT NULL,
596 `price` decimal(25,4) DEFAULT NULL,
597 `quantity` decimal(15,4) DEFAULT NULL,
598 PRIMARY KEY (`id`),
599 UNIQUE `unique_product_id_name` (`product_id`, `name`)
600) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
601
602CREATE TABLE IF NOT EXISTS `sma_purchases` (
603 `id` int(11) NOT NULL AUTO_INCREMENT,
604 `reference_no` varchar(55) NOT NULL,
605 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
606 `supplier_id` int(11) NOT NULL,
607 `supplier` varchar(55) NOT NULL,
608 `warehouse_id` int(11) NOT NULL,
609 `note` varchar(1000) NOT NULL,
610 `total` decimal(25,4) DEFAULT NULL,
611 `product_discount` decimal(25,4) DEFAULT NULL,
612 `order_discount_id` varchar(20) DEFAULT NULL,
613 `order_discount` decimal(25,4) DEFAULT NULL,
614 `total_discount` decimal(25,4) DEFAULT NULL,
615 `product_tax` decimal(25,4) DEFAULT NULL,
616 `order_tax_id` int(11) DEFAULT NULL,
617 `order_tax` decimal(25,4) DEFAULT NULL,
618 `total_tax` decimal(25,4) DEFAULT '0.00',
619 `shipping` decimal(25,4) DEFAULT '0.00',
620 `grand_total` decimal(25,4) NOT NULL,
621 `paid` decimal(25,4) NOT NULL DEFAULT '0.00',
622 `status` varchar(55) DEFAULT '',
623 `payment_status` varchar(20) DEFAULT 'pending',
624 `created_by` int(11) DEFAULT NULL,
625 `updated_by` int(11) DEFAULT NULL,
626 `updated_at` timestamp NULL DEFAULT NULL,
627 `attachment` VARCHAR(55) NULL,
628 `payment_term` TINYINT NULL,
629 `due_date` DATE NULL,
630 `return_id` int(11) DEFAULT NULL,
631 `surcharge` decimal(25,4) NOT NULL DEFAULT '0.00',
632 `return_purchase_ref` VARCHAR(55) NULL,
633 `purchase_id` INT NULL,
634 `return_purchase_total` DECIMAL(25,4) NOT NULL DEFAULT '0',
635 `cgst` DECIMAL(25,4) NULL,
636 `sgst` DECIMAL(25,4) NULL,
637 `igst` DECIMAL(25,4) NULL,
638 PRIMARY KEY (`id`),
639 KEY `id` (`id`)
640) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
641
642CREATE TABLE IF NOT EXISTS `sma_purchase_items` (
643 `id` int(11) NOT NULL AUTO_INCREMENT,
644 `purchase_id` int(11) DEFAULT NULL,
645 `transfer_id` int(11) DEFAULT NULL,
646 `product_id` int(11) NOT NULL,
647 `product_code` varchar(50) NOT NULL,
648 `product_name` varchar(255) NOT NULL,
649 `option_id` int(11) DEFAULT NULL,
650 `net_unit_cost` decimal(25,4) NOT NULL,
651 `quantity` decimal(15,4) NOT NULL,
652 `warehouse_id` int(11) NOT NULL,
653 `item_tax` decimal(25,4) DEFAULT NULL,
654 `tax_rate_id` int(11) DEFAULT NULL,
655 `tax` varchar(20) DEFAULT NULL,
656 `discount` varchar(20) DEFAULT NULL,
657 `item_discount` decimal(25,4) DEFAULT NULL,
658 `expiry` date DEFAULT NULL,
659 `subtotal` decimal(25,4) NOT NULL,
660 `quantity_balance` decimal(15,4) DEFAULT '0.00',
661 `date` date NOT NULL,
662 `status` varchar(50) NOT NULL,
663 `unit_cost` DECIMAL(25,4) NULL,
664 `real_unit_cost` DECIMAL(25,4) NULL,
665 `quantity_received` DECIMAL(15,4) NULL,
666 `supplier_part_no` VARCHAR(50) NULL,
667 `purchase_item_id` INT NULL,
668 `product_unit_id` INT(11) NULL,
669 `product_unit_code` VARCHAR(10) NULL,
670 `unit_quantity` DECIMAL(15,4) NOT NULL,
671 `gst` VARCHAR(20) NULL,
672 `cgst` DECIMAL(25,4) NULL,
673 `sgst` DECIMAL(25,4) NULL,
674 `igst` DECIMAL(25,4) NULL,
675 `base_unit_cost` decimal(25,4) NULL,
676 PRIMARY KEY (`id`),
677 KEY `purchase_id` (`purchase_id`),
678 KEY `product_id` (`product_id`)
679) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
680
681CREATE TABLE IF NOT EXISTS `sma_quotes` (
682 `id` int(11) NOT NULL AUTO_INCREMENT,
683 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
684 `reference_no` varchar(55) NOT NULL,
685 `customer_id` int(11) NOT NULL,
686 `customer` varchar(55) NOT NULL,
687 `warehouse_id` int(11) DEFAULT NULL,
688 `biller_id` int(11) NOT NULL,
689 `biller` varchar(55) NOT NULL,
690 `note` varchar(1000) DEFAULT NULL,
691 `internal_note` varchar(1000) DEFAULT NULL,
692 `total` decimal(25,4) NOT NULL,
693 `product_discount` decimal(25,4) DEFAULT '0.00',
694 `order_discount` decimal(25,4) DEFAULT NULL,
695 `order_discount_id` varchar(20) DEFAULT NULL,
696 `total_discount` decimal(25,4) DEFAULT '0.00',
697 `product_tax` decimal(25,4) DEFAULT '0.00',
698 `order_tax_id` int(11) DEFAULT NULL,
699 `order_tax` decimal(25,4) DEFAULT NULL,
700 `total_tax` decimal(25,4) DEFAULT NULL,
701 `shipping` decimal(25,4) DEFAULT '0.00',
702 `grand_total` decimal(25,4) NOT NULL,
703 `status` varchar(20) DEFAULT NULL,
704 `created_by` int(11) DEFAULT NULL,
705 `updated_by` int(11) DEFAULT NULL,
706 `updated_at` timestamp NULL DEFAULT NULL,
707 `attachment` VARCHAR(55) NULL,
708 `supplier_id` INT NULL,
709 `supplier` VARCHAR(55) NULL,
710 `hash` VARCHAR(255) NULL,
711 `cgst` DECIMAL(25,4) NULL,
712 `sgst` DECIMAL(25,4) NULL,
713 `igst` DECIMAL(25,4) NULL,
714 PRIMARY KEY (`id`),
715 KEY `id` (`id`)
716) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
717
718CREATE TABLE IF NOT EXISTS `sma_quote_items` (
719 `id` int(11) NOT NULL AUTO_INCREMENT,
720 `quote_id` int(11) NOT NULL,
721 `product_id` int(11) NOT NULL,
722 `product_code` varchar(55) NOT NULL,
723 `product_name` varchar(255) NOT NULL,
724 `product_type` varchar(20) DEFAULT NULL,
725 `option_id` int(11) DEFAULT NULL,
726 `net_unit_price` decimal(25,4) NOT NULL,
727 `unit_price` decimal(25,4) DEFAULT NULL,
728 `quantity` decimal(15,4) NOT NULL,
729 `warehouse_id` int(11) DEFAULT NULL,
730 `item_tax` decimal(25,4) DEFAULT NULL,
731 `tax_rate_id` int(11) DEFAULT NULL,
732 `tax` varchar(55) DEFAULT NULL,
733 `discount` varchar(55) DEFAULT NULL,
734 `item_discount` decimal(25,4) DEFAULT NULL,
735 `subtotal` decimal(25,4) NOT NULL,
736 `serial_no` varchar(255) DEFAULT NULL,
737 `real_unit_price` DECIMAL(25,4) NULL,
738 `product_unit_id` INT(11) NULL,
739 `product_unit_code` VARCHAR(10) NULL,
740 `unit_quantity` DECIMAL(15,4) NOT NULL,
741 `gst` VARCHAR(20) NULL,
742 `cgst` DECIMAL(25,4) NULL,
743 `sgst` DECIMAL(25,4) NULL,
744 `igst` DECIMAL(25,4) NULL,
745 PRIMARY KEY (`id`),
746 KEY `quote_id` (`quote_id`),
747 KEY `product_id` (`product_id`)
748) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
749
750CREATE TABLE IF NOT EXISTS `sma_returns` (
751 `id` int(11) NOT NULL AUTO_INCREMENT,
752 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
753 `reference_no` varchar(55) NOT NULL,
754 `customer_id` int(11) NOT NULL,
755 `customer` varchar(55) NOT NULL,
756 `biller_id` int(11) NOT NULL,
757 `biller` varchar(55) NOT NULL,
758 `warehouse_id` int(11) DEFAULT NULL,
759 `note` varchar(1000) DEFAULT NULL,
760 `staff_note` varchar(1000) DEFAULT NULL,
761 `total` decimal(25,4) NOT NULL,
762 `product_discount` decimal(25,4) DEFAULT '0.00',
763 `order_discount_id` varchar(20) DEFAULT NULL,
764 `total_discount` decimal(25,4) DEFAULT '0.00',
765 `order_discount` decimal(25,4) DEFAULT '0.00',
766 `product_tax` decimal(25,4) DEFAULT '0.00',
767 `order_tax_id` int(11) DEFAULT NULL,
768 `order_tax` decimal(25,4) DEFAULT '0.00',
769 `total_tax` decimal(25,4) DEFAULT '0.00',
770 `grand_total` decimal(25,4) NOT NULL,
771 `created_by` int(11) DEFAULT NULL,
772 `updated_by` int(11) DEFAULT NULL,
773 `updated_at` timestamp NULL DEFAULT NULL,
774 `total_items` SMALLINT DEFAULT NULL,
775 `paid` decimal(25,4) DEFAULT '0.00',
776 `surcharge` decimal(25,4) NOT NULL DEFAULT '0.00',
777 `attachment` VARCHAR(55) NULL,
778 `hash` VARCHAR(255) NULL,
779 `cgst` DECIMAL(25,4) NULL,
780 `sgst` DECIMAL(25,4) NULL,
781 `igst` DECIMAL(25,4) NULL,
782 `shipping` DECIMAL(25,4) NULL DEFAULT '0',
783 PRIMARY KEY (`id`),
784 KEY `id` (`id`)
785) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
786
787CREATE TABLE IF NOT EXISTS `sma_return_items` (
788 `id` int(11) NOT NULL AUTO_INCREMENT,
789 `return_id` int(11) unsigned NOT NULL,
790 `product_id` int(11) unsigned NOT NULL,
791 `product_code` varchar(55) NOT NULL,
792 `product_name` varchar(255) NOT NULL,
793 `product_type` varchar(20) DEFAULT NULL,
794 `option_id` int(11) DEFAULT NULL,
795 `net_unit_price` decimal(25,4) NOT NULL,
796 `unit_price` decimal(25,4) DEFAULT NULL,
797 `quantity` decimal(15,4) NOT NULL,
798 `warehouse_id` int(11) DEFAULT NULL,
799 `item_tax` decimal(25,4) DEFAULT NULL,
800 `tax_rate_id` int(11) DEFAULT NULL,
801 `tax` varchar(55) DEFAULT NULL,
802 `discount` varchar(55) DEFAULT NULL,
803 `item_discount` decimal(25,4) DEFAULT NULL,
804 `subtotal` decimal(25,4) NOT NULL,
805 `serial_no` varchar(255) DEFAULT NULL,
806 `real_unit_price` DECIMAL(25,4) NULL,
807 `product_unit_id` INT(11) NULL,
808 `product_unit_code` VARCHAR(10) NULL,
809 `unit_quantity` DECIMAL(15,4) NOT NULL,
810 `comment` VARCHAR(255) NULL,
811 `gst` VARCHAR(20) NULL,
812 `cgst` DECIMAL(25,4) NULL,
813 `sgst` DECIMAL(25,4) NULL,
814 `igst` DECIMAL(25,4) NULL,
815 PRIMARY KEY (`id`),
816 KEY `return_id` (`return_id`),
817 KEY `product_id` (`product_id`),
818 KEY `product_id_2` (`product_id`,`return_id`),
819 KEY `return_id_2` (`return_id`,`product_id`)
820) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
821
822CREATE TABLE IF NOT EXISTS `sma_sales` (
823 `id` int(11) NOT NULL AUTO_INCREMENT,
824 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
825 `reference_no` varchar(55) NOT NULL,
826 `customer_id` int(11) NOT NULL,
827 `customer` varchar(55) NOT NULL,
828 `biller_id` int(11) NOT NULL,
829 `biller` varchar(55) NOT NULL,
830 `warehouse_id` int(11) DEFAULT NULL,
831 `note` varchar(1000) DEFAULT NULL,
832 `staff_note` varchar(1000) DEFAULT NULL,
833 `total` decimal(25,4) NOT NULL,
834 `product_discount` decimal(25,4) DEFAULT '0.00',
835 `order_discount_id` varchar(20) DEFAULT NULL,
836 `total_discount` decimal(25,4) DEFAULT '0.00',
837 `order_discount` decimal(25,4) DEFAULT '0.00',
838 `product_tax` decimal(25,4) DEFAULT '0.00',
839 `order_tax_id` int(11) DEFAULT NULL,
840 `order_tax` decimal(25,4) DEFAULT '0.00',
841 `total_tax` decimal(25,4) DEFAULT '0.00',
842 `shipping` decimal(25,4) DEFAULT '0.00',
843 `grand_total` decimal(25,4) NOT NULL,
844 `sale_status` varchar(20) DEFAULT NULL,
845 `payment_status` varchar(20) DEFAULT NULL,
846 `payment_term` tinyint(4) DEFAULT NULL,
847 `due_date` date DEFAULT NULL,
848 `created_by` int(11) DEFAULT NULL,
849 `updated_by` int(11) DEFAULT NULL,
850 `updated_at` timestamp NULL DEFAULT NULL,
851 `total_items` SMALLINT DEFAULT NULL,
852 `pos` tinyint(1) NOT NULL DEFAULT '0',
853 `paid` decimal(25,4) DEFAULT '0.00',
854 `return_id` int(11) DEFAULT NULL,
855 `surcharge` decimal(25,4) NOT NULL DEFAULT '0.00',
856 `attachment` VARCHAR(55) NULL,
857 `return_sale_ref` VARCHAR(55) NULL,
858 `sale_id` INT NULL,
859 `return_sale_total` DECIMAL(25,4) NOT NULL DEFAULT '0',
860 `rounding` DECIMAL(10,4) NULL,
861 `suspend_note` VARCHAR(255) NULL,
862 `api` TINYINT(1) NULL DEFAULT '0',
863 `shop` TINYINT(1) NULL DEFAULT '0',
864 `address_id` INT NULL,
865 `reserve_id` INT NULL,
866 `hash` VARCHAR(255) NULL,
867 `manual_payment` VARCHAR(55) NULL,
868 `cgst` DECIMAL(25,4) NULL,
869 `sgst` DECIMAL(25,4) NULL,
870 `igst` DECIMAL(25,4) NULL,
871 `payment_method` VARCHAR(55) NULL,
872 PRIMARY KEY (`id`),
873 KEY `id` (`id`)
874) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
875
876CREATE TABLE IF NOT EXISTS `sma_sale_items` (
877 `id` int(11) NOT NULL AUTO_INCREMENT,
878 `sale_id` int(11) unsigned NOT NULL,
879 `product_id` int(11) unsigned NOT NULL,
880 `product_code` varchar(55) NOT NULL,
881 `product_name` varchar(255) NOT NULL,
882 `product_type` varchar(20) DEFAULT NULL,
883 `option_id` int(11) DEFAULT NULL,
884 `net_unit_price` decimal(25,4) NOT NULL,
885 `unit_price` decimal(25,4) DEFAULT NULL,
886 `quantity` decimal(15,4) NOT NULL,
887 `warehouse_id` int(11) DEFAULT NULL,
888 `item_tax` decimal(25,4) DEFAULT NULL,
889 `tax_rate_id` int(11) DEFAULT NULL,
890 `tax` varchar(55) DEFAULT NULL,
891 `discount` varchar(55) DEFAULT NULL,
892 `item_discount` decimal(25,4) DEFAULT NULL,
893 `subtotal` decimal(25,4) NOT NULL,
894 `serial_no` varchar(255) DEFAULT NULL,
895 `real_unit_price` DECIMAL(25,4) NULL,
896 `sale_item_id` INT NULL,
897 `product_unit_id` INT(11) NULL,
898 `product_unit_code` VARCHAR(10) NULL,
899 `unit_quantity` DECIMAL(15,4) NOT NULL,
900 `comment` VARCHAR(255) NULL,
901 `gst` VARCHAR(20) NULL,
902 `cgst` DECIMAL(25,4) NULL,
903 `sgst` DECIMAL(25,4) NULL,
904 `igst` DECIMAL(25,4) NULL,
905 PRIMARY KEY (`id`),
906 KEY `sale_id` (`sale_id`),
907 KEY `product_id` (`product_id`),
908 KEY `product_id_2` (`product_id`,`sale_id`),
909 KEY `sale_id_2` (`sale_id`,`product_id`)
910) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
911
912CREATE TABLE IF NOT EXISTS `sma_sessions` (
913 `id` varchar(40) NOT NULL,
914 `ip_address` varchar(45) NOT NULL,
915 `timestamp` int(10) unsigned NOT NULL DEFAULT '0',
916 `data` blob NOT NULL,
917 PRIMARY KEY (`id`),
918 KEY `ci_sessions_timestamp` (`timestamp`)
919) DEFAULT CHARSET=utf8;
920
921CREATE TABLE IF NOT EXISTS `sma_settings` (
922 `setting_id` int(1) NOT NULL,
923 `logo` varchar(255) NOT NULL,
924 `logo2` varchar(255) NOT NULL,
925 `site_name` varchar(55) NOT NULL,
926 `language` varchar(20) NOT NULL,
927 `default_warehouse` int(2) NOT NULL,
928 `accounting_method` tinyint(4) NOT NULL DEFAULT '0',
929 `default_currency` varchar(3) NOT NULL,
930 `default_tax_rate` int(2) NOT NULL,
931 `rows_per_page` int(2) NOT NULL,
932 `version` varchar(10) NOT NULL DEFAULT '1.0',
933 `default_tax_rate2` int(11) NOT NULL DEFAULT '0',
934 `dateformat` int(11) NOT NULL,
935 `sales_prefix` varchar(20) DEFAULT NULL,
936 `quote_prefix` varchar(20) DEFAULT NULL,
937 `purchase_prefix` varchar(20) DEFAULT NULL,
938 `transfer_prefix` varchar(20) DEFAULT NULL,
939 `delivery_prefix` varchar(20) DEFAULT NULL,
940 `payment_prefix` varchar(20) DEFAULT NULL,
941 `return_prefix` varchar(20) DEFAULT NULL,
942 `returnp_prefix` varchar(20) DEFAULT NULL,
943 `expense_prefix` varchar(20) DEFAULT NULL,
944 `item_addition` tinyint(1) NOT NULL DEFAULT '0',
945 `theme` varchar(20) NOT NULL,
946 `product_serial` tinyint(4) NOT NULL,
947 `default_discount` int(11) NOT NULL,
948 `product_discount` tinyint(1) NOT NULL DEFAULT '0',
949 `discount_method` tinyint(4) NOT NULL,
950 `tax1` tinyint(4) NOT NULL,
951 `tax2` tinyint(4) NOT NULL,
952 `overselling` tinyint(1) NOT NULL DEFAULT '0',
953 `restrict_user` tinyint(4) NOT NULL DEFAULT '0',
954 `restrict_calendar` tinyint(4) NOT NULL DEFAULT '0',
955 `timezone` varchar(100) DEFAULT NULL,
956 `iwidth` int(11) NOT NULL DEFAULT '0',
957 `iheight` int(11) NOT NULL,
958 `twidth` int(11) NOT NULL,
959 `theight` int(11) NOT NULL,
960 `watermark` tinyint(1) DEFAULT NULL,
961 `reg_ver` tinyint(1) DEFAULT NULL,
962 `allow_reg` tinyint(1) DEFAULT NULL,
963 `reg_notification` tinyint(1) DEFAULT NULL,
964 `auto_reg` tinyint(1) DEFAULT NULL,
965 `protocol` varchar(20) NOT NULL DEFAULT 'mail',
966 `mailpath` varchar(55) NULL DEFAULT '/usr/sbin/sendmail',
967 `smtp_host` varchar(100) DEFAULT NULL,
968 `smtp_user` varchar(100) DEFAULT NULL,
969 `smtp_pass` varchar(255) DEFAULT NULL,
970 `smtp_port` varchar(10) DEFAULT '25',
971 `smtp_crypto` varchar(10) DEFAULT NULL,
972 `corn` datetime DEFAULT NULL,
973 `customer_group` int(11) NOT NULL,
974 `default_email` varchar(100) NOT NULL,
975 `mmode` tinyint(1) NOT NULL,
976 `bc_fix` tinyint(4) NOT NULL DEFAULT '0',
977 `auto_detect_barcode` tinyint(1) NOT NULL DEFAULT '0',
978 `captcha` tinyint(1) NOT NULL DEFAULT '1',
979 `reference_format` tinyint(1) NOT NULL DEFAULT '1',
980 `racks` tinyint(1) DEFAULT '0',
981 `attributes` tinyint(1) NOT NULL DEFAULT '0',
982 `product_expiry` tinyint(1) NOT NULL DEFAULT '0',
983 `decimals` tinyint(2) NOT NULL DEFAULT '2',
984 `qty_decimals` tinyint(2) NOT NULL DEFAULT '2',
985 `decimals_sep` varchar(2) NOT NULL DEFAULT '.',
986 `thousands_sep` varchar(2) NOT NULL DEFAULT ',',
987 `invoice_view` tinyint(1) DEFAULT '0',
988 `default_biller` INT NULL,
989 `envato_username` varchar(50) NULL,
990 `purchase_code` varchar(100) NULL,
991 `rtl` tinyint(1) NULL DEFAULT '0',
992 `each_spent` DECIMAL(15,4) NULL,
993 `ca_point` TINYINT NULL,
994 `each_sale` DECIMAL(15,4) NULL,
995 `sa_point` TINYINT NULL,
996 `update` tinyint(1) NULL DEFAULT '0',
997 `sac` tinyint(1) NULL DEFAULT '0',
998 `display_all_products` TINYINT(1) NULL DEFAULT '0',
999 `display_symbol` TINYINT(1) NULL,
1000 `symbol` VARCHAR(50) NULL,
1001 `remove_expired` TINYINT(1) NULL DEFAULT '0',
1002 `barcode_separator` VARCHAR(2) NOT NULL DEFAULT '-',
1003 `set_focus` TINYINT(1) NOT NULL DEFAULT '0',
1004 `price_group` INT(11) NULL,
1005 `barcode_img` TINYINT(1) NOT NULL DEFAULT '1',
1006 `ppayment_prefix` VARCHAR(20) NULL DEFAULT 'POP',
1007 `disable_editing` SMALLINT NULL DEFAULT '90',
1008 `qa_prefix` VARCHAR(55) NULL,
1009 `update_cost` TINYINT(1) NULL,
1010 `apis` TINYINT(1) NOT NULL DEFAULT '0',
1011 `state` VARCHAR(100) NULL,
1012 `pdf_lib` VARCHAR(20) NULL DEFAULT 'dompdf',
1013 `use_code_for_slug` TINYINT(1) NULL DEFAULT NULL,
1014 `ws_barcode_type` varchar(10) NULL DEFAULT 'weight',
1015 `ws_barcode_chars` tinyint NULL,
1016 `flag_chars` tinyint NULL,
1017 `item_code_start` tinyint NULL,
1018 `item_code_chars` tinyint NULL,
1019 `price_start` tinyint NULL,
1020 `price_chars` tinyint NULL,
1021 `price_divide_by` int NULL,
1022 `weight_start` tinyint NULL,
1023 `weight_chars` tinyint NULL,
1024 `weight_divide_by` int NULL,
1025 PRIMARY KEY (`setting_id`)
1026) DEFAULT CHARSET=utf8;
1027
1028INSERT INTO `sma_settings` (`setting_id`, `logo`, `logo2`, `site_name`, `language`, `default_warehouse`, `accounting_method`, `default_currency`, `default_tax_rate`, `rows_per_page`, `version`, `default_tax_rate2`, `dateformat`, `sales_prefix`, `quote_prefix`, `purchase_prefix`, `transfer_prefix`, `delivery_prefix`, `payment_prefix`, `return_prefix`, `returnp_prefix`, `item_addition`, `theme`, `product_serial`, `default_discount`, `product_discount`, `discount_method`, `tax1`, `tax2`, `overselling`, `restrict_user`, `restrict_calendar`, `timezone`, `iwidth`, `iheight`, `twidth`, `theight`, `watermark`, `reg_ver`, `allow_reg`, `reg_notification`, `auto_reg`, `protocol`, `smtp_host`, `smtp_user`, `smtp_pass`, `smtp_port`, `corn`, `customer_group`, `default_email`, `mmode`, `bc_fix`, `auto_detect_barcode`, `captcha`, `reference_format`, `racks`, `attributes`, `product_expiry`, `decimals`, `decimals_sep`, `thousands_sep`, `invoice_view`, `default_biller`) VALUES
1029(1, 'logo2.png', 'logo3.png', 'Stock Manager Advance', 'english', 1, 0, 'USD', 1, 10, '3.4.47', 1, 5, 'SALE', 'QUOTE', 'PO', 'TR', 'DO', 'IPAY', 'SR', 'PR', 0, 'default', 1, 1, 1, 1, 1, 1, 0, 1, 0, 'Asia/Kuala_Lumpur', 800, 800, 150, 150, 0, 0, 0, 0, NULL, 'mail', 'pop.gmail.com', 'contact@sma.tecdiary.org', 'jEFTM4T63AiQ9dsidxhPKt9CIg4HQjCN58n/RW9vmdC/UDXCzRLR469ziZ0jjpFlbOg43LyoSmpJLBkcAHh0Yw==', '25', NULL, 1, 'contact@tecdiary.com', 0, 4, 1, 0, 2, 1, 1, 0, 2, '.', ',', 0, 3);
1030
1031CREATE TABLE IF NOT EXISTS `sma_skrill` (
1032 `id` int(11) NOT NULL,
1033 `active` tinyint(4) NOT NULL,
1034 `account_email` varchar(255) NOT NULL DEFAULT 'testaccount2@moneybookers.com',
1035 `secret_word` varchar(20) NOT NULL DEFAULT 'mbtest',
1036 `skrill_currency` varchar(3) NOT NULL DEFAULT 'USD',
1037 `fixed_charges` decimal(25,4) NOT NULL DEFAULT '0.00',
1038 `extra_charges_my` decimal(25,4) NOT NULL DEFAULT '0.00',
1039 `extra_charges_other` decimal(25,4) NOT NULL DEFAULT '0.00',
1040 PRIMARY KEY (`id`)
1041) DEFAULT CHARSET=utf8;
1042
1043INSERT INTO `sma_skrill` (`id`, `active`, `account_email`, `secret_word`, `skrill_currency`, `fixed_charges`, `extra_charges_my`, `extra_charges_other`) VALUES
1044(1, 1, 'testaccount2@moneybookers.com', 'mbtest', 'USD', '0.00', '0.00', '0.00');
1045
1046CREATE TABLE IF NOT EXISTS `sma_stock_counts` (
1047 `id` int(11) NOT NULL AUTO_INCREMENT,
1048 `date` timestamp NOT NULL,
1049 `reference_no` varchar(55) NOT NULL,
1050 `warehouse_id` int(11) NOT NULL,
1051 `type` varchar(10) NOT NULL,
1052 `initial_file` varchar(50) NOT NULL,
1053 `final_file` VARCHAR(50) NULL,
1054 `brands` varchar(50) NULL,
1055 `brand_names` varchar(100) NULL,
1056 `categories` varchar(50) NULL,
1057 `category_names` varchar(100) NULL,
1058 `note` text NULL,
1059 `products` int(11) NULL,
1060 `rows` int(11) NULL,
1061 `differences` int(11) NULL,
1062 `matches` int(11) NULL,
1063 `missing` int(11) NULL,
1064 `created_by` int(11) NOT NULL,
1065 `updated_by` int(11) NULL,
1066 `updated_at` datetime NULL,
1067 `finalized` tinyint(1) NULL,
1068 PRIMARY KEY (`id`),
1069 KEY `warehouse_id` (`warehouse_id`)
1070) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1071
1072CREATE TABLE IF NOT EXISTS `sma_stock_count_items` (
1073 `id` int(11) NOT NULL AUTO_INCREMENT,
1074 `stock_count_id` int(11) NOT NULL,
1075 `product_id` int(11) NOT NULL,
1076 `product_code` varchar(50) NULL,
1077 `product_name` varchar(255) NULL,
1078 `product_variant` varchar(55) NULL,
1079 `product_variant_id` int(11) NULL,
1080 `expected` DECIMAL(15,4) NOT NULL,
1081 `counted` DECIMAL(15,4) NOT NULL,
1082 `cost` DECIMAL(25,4) NOT NULL,
1083 PRIMARY KEY (`id`),
1084 KEY `stock_count_id` (`stock_count_id`),
1085 KEY `product_id` (`product_id`)
1086) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1087
1088CREATE TABLE IF NOT EXISTS `sma_tax_rates` (
1089 `id` int(11) NOT NULL AUTO_INCREMENT,
1090 `name` varchar(55) NOT NULL,
1091 `code` varchar(10) DEFAULT NULL,
1092 `rate` decimal(12,4) NOT NULL,
1093 `type` varchar(50) NOT NULL,
1094 PRIMARY KEY (`id`)
1095) DEFAULT CHARSET=utf8 AUTO_INCREMENT=5;
1096
1097INSERT INTO `sma_tax_rates` (`id`, `name`, `code`, `rate`, `type`) VALUES
1098(1, 'No Tax', 'NT', '0.00', '2'),
1099(2, 'VAT @10%', 'VAT10', '10.00', '1'),
1100(3, 'GST @6%', 'GST', '6.00', '1'),
1101(4, 'VAT @20%', 'VT20', '20.00', '1');
1102
1103CREATE TABLE IF NOT EXISTS `sma_transfers` (
1104 `id` int(11) NOT NULL AUTO_INCREMENT,
1105 `transfer_no` varchar(55) NOT NULL,
1106 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
1107 `from_warehouse_id` int(11) NOT NULL,
1108 `from_warehouse_code` varchar(55) NOT NULL,
1109 `from_warehouse_name` varchar(55) NOT NULL,
1110 `to_warehouse_id` int(11) NOT NULL,
1111 `to_warehouse_code` varchar(55) NOT NULL,
1112 `to_warehouse_name` varchar(55) NOT NULL,
1113 `note` varchar(1000) DEFAULT NULL,
1114 `total` decimal(25,4) DEFAULT NULL,
1115 `total_tax` decimal(25,4) DEFAULT NULL,
1116 `grand_total` decimal(25,4) DEFAULT NULL,
1117 `created_by` varchar(255) DEFAULT NULL,
1118 `status` varchar(55) NOT NULL DEFAULT 'pending',
1119 `shipping` decimal(25,4) NOT NULL DEFAULT '0.00',
1120 `attachment` VARCHAR(55) NULL,
1121 `cgst` DECIMAL(25,4) NULL,
1122 `sgst` DECIMAL(25,4) NULL,
1123 `igst` DECIMAL(25,4) NULL,
1124 PRIMARY KEY (`id`),
1125 KEY `id` (`id`)
1126) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1127
1128CREATE TABLE IF NOT EXISTS `sma_transfer_items` (
1129 `id` int(11) NOT NULL AUTO_INCREMENT,
1130 `transfer_id` int(11) NOT NULL,
1131 `product_id` int(11) NOT NULL,
1132 `product_code` varchar(55) NOT NULL,
1133 `product_name` varchar(255) NOT NULL,
1134 `option_id` int(11) DEFAULT NULL,
1135 `expiry` date DEFAULT NULL,
1136 `quantity` decimal(15,4) NOT NULL,
1137 `tax_rate_id` int(11) DEFAULT NULL,
1138 `tax` varchar(55) DEFAULT NULL,
1139 `item_tax` decimal(25,4) DEFAULT NULL,
1140 `net_unit_cost` decimal(25,4) DEFAULT NULL,
1141 `subtotal` decimal(25,4) DEFAULT NULL,
1142 `quantity_balance` decimal(15,4) NOT NULL,
1143 `unit_cost` DECIMAL(25,4) NULL,
1144 `real_unit_cost` DECIMAL(25,4) NULL,
1145 `date` DATE NULL,
1146 `warehouse_id` INT NULL,
1147 `product_unit_id` INT(11) NULL,
1148 `product_unit_code` VARCHAR(10) NULL,
1149 `unit_quantity` DECIMAL(15,4) NOT NULL,
1150 `gst` VARCHAR(20) NULL,
1151 `cgst` DECIMAL(25,4) NULL,
1152 `sgst` DECIMAL(25,4) NULL,
1153 `igst` DECIMAL(25,4) NULL,
1154 PRIMARY KEY (`id`),
1155 KEY `transfer_id` (`transfer_id`),
1156 KEY `product_id` (`product_id`)
1157) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1158
1159CREATE TABLE IF NOT EXISTS `sma_units` (
1160 `id` int(11) NOT NULL AUTO_INCREMENT,
1161 `code` varchar(10) NOT NULL,
1162 `name` varchar(55) NOT NULL,
1163 `base_unit` int(11) DEFAULT NULL,
1164 `operator` varchar(1) DEFAULT NULL,
1165 `unit_value` varchar(55) DEFAULT NULL,
1166 `operation_value` varchar(55) DEFAULT NULL,
1167 PRIMARY KEY (`id`),
1168 KEY `base_unit` (`base_unit`)
1169) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1170
1171CREATE TABLE IF NOT EXISTS `sma_users` (
1172 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
1173 `last_ip_address` varbinary(45) DEFAULT NULL,
1174 `ip_address` varbinary(45) NOT NULL,
1175 `username` varchar(100) NOT NULL,
1176 `password` varchar(40) NOT NULL,
1177 `salt` varchar(40) DEFAULT NULL,
1178 `email` varchar(100) NOT NULL,
1179 `activation_code` varchar(40) DEFAULT NULL,
1180 `forgotten_password_code` varchar(40) DEFAULT NULL,
1181 `forgotten_password_time` int(11) unsigned DEFAULT NULL,
1182 `remember_code` varchar(40) DEFAULT NULL,
1183 `created_on` int(11) unsigned NOT NULL,
1184 `last_login` int(11) unsigned DEFAULT NULL,
1185 `active` tinyint(1) unsigned DEFAULT NULL,
1186 `first_name` varchar(50) DEFAULT NULL,
1187 `last_name` varchar(50) DEFAULT NULL,
1188 `company` varchar(100) DEFAULT NULL,
1189 `phone` varchar(20) DEFAULT NULL,
1190 `avatar` varchar(55) DEFAULT NULL,
1191 `gender` varchar(20) DEFAULT NULL,
1192 `group_id` int(10) unsigned NOT NULL,
1193 `warehouse_id` int(10) unsigned DEFAULT NULL,
1194 `biller_id` int(10) unsigned DEFAULT NULL,
1195 `company_id` int(11) DEFAULT NULL,
1196 `show_cost` TINYINT(1) NULL DEFAULT '0',
1197 `show_price` TINYINT(1) NULL DEFAULT '0',
1198 `award_points` int NULL DEFAULT '0',
1199 `view_right` TINYINT(1) NOT NULL DEFAULT '0',
1200 `edit_right` TINYINT(1) NOT NULL DEFAULT '0',
1201 `allow_discount` TINYINT(1) NULL DEFAULT '0',
1202 PRIMARY KEY (`id`),
1203 KEY `group_id` (`group_id`,`warehouse_id`,`biller_id`),
1204 KEY `group_id_2` (`group_id`,`company_id`)
1205) DEFAULT CHARSET=utf8 AUTO_INCREMENT=2;
1206
1207INSERT INTO `sma_users` (`id`, `last_ip_address`, `ip_address`, `username`, `password`, `salt`, `email`, `activation_code`, `forgotten_password_code`, `forgotten_password_time`, `remember_code`, `created_on`, `last_login`, `active`, `first_name`, `last_name`, `company`, `phone`, `avatar`, `gender`, `group_id`, `warehouse_id`, `biller_id`, `company_id`) VALUES
1208(1, '127.0.0.1', '\0\0', 'owner', '2c8ab736b2ccab4f50e72d5fd7d21020cbb77ae7', NULL, 'owner@tecdiary.com', NULL, NULL, NULL, NULL, 1351661704, 1426618492, 1, 'Owner', 'Owner', 'Stock Manager', '012345678', NULL, 'male', 1, NULL, NULL, NULL);
1209
1210CREATE TABLE IF NOT EXISTS `sma_user_logins` (
1211 `id` int(11) NOT NULL AUTO_INCREMENT,
1212 `user_id` int(11) NOT NULL,
1213 `company_id` int(11) DEFAULT NULL,
1214 `ip_address` varbinary(16) NOT NULL,
1215 `login` varchar(100) NOT NULL,
1216 `time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
1217 PRIMARY KEY (`id`)
1218) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1219
1220CREATE TABLE IF NOT EXISTS `sma_variants` (
1221 `id` int(11) NOT NULL AUTO_INCREMENT,
1222 `name` varchar(55) NOT NULL,
1223 PRIMARY KEY (`id`)
1224) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1225
1226CREATE TABLE IF NOT EXISTS `sma_warehouses` (
1227 `id` int(11) NOT NULL AUTO_INCREMENT,
1228 `code` varchar(50) NOT NULL,
1229 `name` varchar(255) NOT NULL,
1230 `address` varchar(255) NOT NULL,
1231 `map` varchar(255) DEFAULT NULL,
1232 `phone` varchar(55) DEFAULT NULL,
1233 `email` varchar(55) DEFAULT NULL,
1234 `price_group_id` INT NULL,
1235 PRIMARY KEY (`id`),
1236 KEY `id` (`id`)
1237) DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;
1238
1239INSERT INTO `sma_warehouses` (`id`, `code`, `name`, `address`, `map`, `phone`, `email`) VALUES
1240(1, 'WHI', 'Warehouse 1', '<p>Address, City</p>', NULL, '012345678', 'whi@tecdiary.com'),
1241(2, 'WHII', 'Warehouse 2', '<p>Warehouse 2, Jalan Sultan Ismail, 54000, Kuala Lumpur</p>', NULL, '0105292122', 'whii@tecdiary.com');
1242
1243CREATE TABLE IF NOT EXISTS `sma_warehouses_products` (
1244 `id` int(11) NOT NULL AUTO_INCREMENT,
1245 `product_id` int(11) NOT NULL,
1246 `warehouse_id` int(11) NOT NULL,
1247 `quantity` DECIMAL(15,4) NOT NULL,
1248 `rack` varchar(55) DEFAULT NULL,
1249 `avg_cost` DECIMAL(25,4) NOT NULL,
1250 PRIMARY KEY (`id`),
1251 KEY `product_id` (`product_id`),
1252 KEY `warehouse_id` (`warehouse_id`)
1253) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1254
1255CREATE TABLE IF NOT EXISTS `sma_warehouses_products_variants` (
1256 `id` int(11) NOT NULL AUTO_INCREMENT,
1257 `option_id` int(11) NOT NULL,
1258 `product_id` int(11) NOT NULL,
1259 `warehouse_id` int(11) NOT NULL,
1260 `quantity` DECIMAL(15,4) NOT NULL,
1261 `rack` varchar(55) DEFAULT NULL,
1262 PRIMARY KEY (`id`),
1263 KEY `option_id` (`option_id`),
1264 KEY `product_id` (`product_id`),
1265 KEY `warehouse_id` (`warehouse_id`)
1266) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1267
1268
1269-- POS Module
1270
1271CREATE TABLE IF NOT EXISTS `sma_pos_register` (
1272 `id` int(11) NOT NULL AUTO_INCREMENT,
1273 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
1274 `user_id` int(11) NOT NULL,
1275 `cash_in_hand` decimal(25,4) NOT NULL,
1276 `status` varchar(10) NOT NULL,
1277 `total_cash` decimal(25,4) DEFAULT NULL,
1278 `total_cheques` int(11) DEFAULT NULL,
1279 `total_cc_slips` int(11) DEFAULT NULL,
1280 `total_cash_submitted` decimal(25,4) DEFAULT NULL,
1281 `total_cheques_submitted` int(11) DEFAULT NULL,
1282 `total_cc_slips_submitted` int(11) DEFAULT NULL,
1283 `note` text,
1284 `closed_at` timestamp NULL DEFAULT NULL,
1285 `transfer_opened_bills` varchar(50) DEFAULT NULL,
1286 `closed_by` INT NULL,
1287 PRIMARY KEY (`id`)
1288) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1289
1290CREATE TABLE IF NOT EXISTS `sma_pos_settings` (
1291 `pos_id` int(1) NOT NULL,
1292 `cat_limit` int(11) NOT NULL,
1293 `pro_limit` int(11) NOT NULL,
1294 `default_category` int(11) NOT NULL,
1295 `default_customer` int(11) NOT NULL,
1296 `default_biller` int(11) NOT NULL,
1297 `display_time` varchar(3) NOT NULL DEFAULT 'yes',
1298 `cf_title1` varchar(255) DEFAULT NULL,
1299 `cf_title2` varchar(255) DEFAULT NULL,
1300 `cf_value1` varchar(255) DEFAULT NULL,
1301 `cf_value2` varchar(255) DEFAULT NULL,
1302 `receipt_printer` varchar(55) DEFAULT NULL,
1303 `cash_drawer_codes` varchar(55) DEFAULT NULL,
1304 `focus_add_item` varchar(55) DEFAULT NULL,
1305 `add_manual_product` varchar(55) DEFAULT NULL,
1306 `customer_selection` varchar(55) DEFAULT NULL,
1307 `add_customer` varchar(55) DEFAULT NULL,
1308 `toggle_category_slider` varchar(55) DEFAULT NULL,
1309 `toggle_subcategory_slider` varchar(55) DEFAULT NULL,
1310 `cancel_sale` varchar(55) DEFAULT NULL,
1311 `suspend_sale` varchar(55) DEFAULT NULL,
1312 `print_items_list` varchar(55) DEFAULT NULL,
1313 `finalize_sale` varchar(55) DEFAULT NULL,
1314 `today_sale` varchar(55) DEFAULT NULL,
1315 `open_hold_bills` varchar(55) DEFAULT NULL,
1316 `close_register` varchar(55) DEFAULT NULL,
1317 `keyboard` tinyint(1) NOT NULL,
1318 `pos_printers` varchar(255) DEFAULT NULL,
1319 `java_applet` tinyint(1) NOT NULL,
1320 `product_button_color` varchar(20) NOT NULL DEFAULT 'default',
1321 `tooltips` tinyint(1) DEFAULT '1',
1322 `paypal_pro` tinyint(1) DEFAULT '0',
1323 `stripe` tinyint(1) DEFAULT '0',
1324 `rounding` TINYINT(1) NULL DEFAULT '0',
1325 `char_per_line` TINYINT NULL DEFAULT '42',
1326 `pin_code` VARCHAR(20) NULL,
1327 `purchase_code` VARCHAR(100) NULL DEFAULT 'purchase_code',
1328 `envato_username` VARCHAR(50) NULL DEFAULT 'envato_username',
1329 `version` VARCHAR(10) NULL DEFAULT '3.4.47',
1330 `after_sale_page` TINYINT(1) NULL DEFAULT '0',
1331 `item_order` TINYINT(1) NULL DEFAULT '0',
1332 `authorize` TINYINT(1) NULL DEFAULT '0',
1333 `toggle_brands_slider` VARCHAR(55) NULL,
1334 `remote_printing` TINYINT(1) NULL DEFAULT '1',
1335 `printer` INT(11) NULL,
1336 `order_printers` VARCHAR(55) NULL,
1337 `auto_print` TINYINT(1) NULL DEFAULT '0',
1338 `customer_details` TINYINT(1) NULL,
1339 `local_printers` TINYINT(1) NULL,
1340 PRIMARY KEY (`pos_id`)
1341) DEFAULT CHARSET=utf8;
1342
1343INSERT INTO `sma_pos_settings` (`pos_id`, `cat_limit`, `pro_limit`, `default_category`, `default_customer`, `default_biller`, `display_time`, `cf_title1`, `cf_title2`, `cf_value1`, `cf_value2`, `receipt_printer`, `cash_drawer_codes`, `focus_add_item`, `add_manual_product`, `customer_selection`, `add_customer`, `toggle_category_slider`, `toggle_subcategory_slider`, `cancel_sale`, `suspend_sale`, `print_items_list`, `finalize_sale`, `today_sale`, `open_hold_bills`, `close_register`, `keyboard`, `pos_printers`, `java_applet`, `product_button_color`, `tooltips`, `paypal_pro`, `stripe`, `version`) VALUES
1344(1, 22, 20, 1, 1, 3, '1', 'GST Reg', 'VAT Reg', '123456789', '987654321', 'BIXOLON SRP-350II', 'x1C', 'Ctrl+F3', 'Ctrl+Shift+M', 'Ctrl+Shift+C', 'Ctrl+Shift+A', 'Ctrl+F11', 'Ctrl+F12', 'F4', 'F7', 'F9', 'F8', 'Ctrl+F1', 'Ctrl+F2', 'Ctrl+F10', 1, 'BIXOLON SRP-350II, BIXOLON SRP-350II', 0, 'default', 1, 0, 0, '3.4.47');
1345
1346CREATE TABLE IF NOT EXISTS `sma_suspended_bills` (
1347 `id` int(11) NOT NULL AUTO_INCREMENT,
1348 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
1349 `customer_id` int(11) NOT NULL,
1350 `customer` varchar(55) DEFAULT NULL,
1351 `count` int(11) NOT NULL,
1352 `order_discount_id` varchar(20) DEFAULT NULL,
1353 `order_tax_id` int(11) DEFAULT NULL,
1354 `total` decimal(25,4) NOT NULL,
1355 `biller_id` int(11) DEFAULT NULL,
1356 `warehouse_id` int(11) DEFAULT NULL,
1357 `created_by` int(11) NOT NULL,
1358 `suspend_note` varchar(255) DEFAULT NULL,
1359 `shipping` DECIMAL(15,4) NULL DEFAULT '0',
1360 `cgst` DECIMAL(25,4) NULL,
1361 `sgst` DECIMAL(25,4) NULL,
1362 `igst` DECIMAL(25,4) NULL,
1363 PRIMARY KEY (`id`)
1364) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1365
1366CREATE TABLE IF NOT EXISTS `sma_suspended_items` (
1367 `id` int(11) NOT NULL AUTO_INCREMENT,
1368 `suspend_id` int(11) unsigned NOT NULL,
1369 `product_id` int(11) unsigned NOT NULL,
1370 `product_code` varchar(55) NOT NULL,
1371 `product_name` varchar(255) NOT NULL,
1372 `net_unit_price` decimal(25,4) NOT NULL,
1373 `unit_price` decimal(25,4) NOT NULL,
1374 `quantity` decimal(15,4) DEFAULT '0.00',
1375 `warehouse_id` int(11) DEFAULT NULL,
1376 `item_tax` decimal(25,4) DEFAULT NULL,
1377 `tax_rate_id` int(11) DEFAULT NULL,
1378 `tax` varchar(55) DEFAULT NULL,
1379 `discount` varchar(55) DEFAULT NULL,
1380 `item_discount` decimal(25,4) DEFAULT NULL,
1381 `subtotal` decimal(25,4) NOT NULL,
1382 `serial_no` varchar(255) DEFAULT NULL,
1383 `option_id` int(11) DEFAULT NULL,
1384 `product_type` varchar(20) DEFAULT NULL,
1385 `real_unit_price` DECIMAL(25,4) NULL,
1386 `product_unit_id` INT(11) NULL,
1387 `product_unit_code` VARCHAR(10) NULL,
1388 `unit_quantity` DECIMAL(15,4) NOT NULL,
1389 `comment` VARCHAR(255) NULL,
1390 `gst` VARCHAR(20) NULL,
1391 `cgst` DECIMAL(25,4) NULL,
1392 `sgst` DECIMAL(25,4) NULL,
1393 `igst` DECIMAL(25,4) NULL,
1394 PRIMARY KEY (`id`)
1395) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1396
1397CREATE TABLE IF NOT EXISTS `sma_promos` (
1398 `id` int(11) NOT NULL AUTO_INCREMENT,
1399 `name` varchar(100) NOT NULL,
1400 `product2buy` int(11) NOT NULL,
1401 `product2get` int(11) NOT NULL,
1402 `start_date` date NULL DEFAULT NULL,
1403 `end_date` date NULL DEFAULT NULL,
1404 `description` text NULL DEFAULT NULL,
1405 PRIMARY KEY (`id`)
1406) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1407
1408
1409-- Shop Module
1410
1411CREATE TABLE IF NOT EXISTS `sma_cart` (
1412 `id` varchar(40) NOT NULL,
1413 `time` varchar(30) NOT NULL,
1414 `user_id` int(11) NULL,
1415 `data` text NULL,
1416 PRIMARY KEY (`id`)
1417) DEFAULT CHARSET=utf8;
1418
1419CREATE TABLE IF NOT EXISTS `sma_wishlist` (
1420 `id` int(11) NOT NULL AUTO_INCREMENT,
1421 `user_id` int(11) NULL,
1422 `product_id` int(11) NULL,
1423 PRIMARY KEY (`id`)
1424) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1425
1426CREATE TABLE IF NOT EXISTS `sma_pages` (
1427 `id` int(11) NOT NULL AUTO_INCREMENT,
1428 `name` varchar(15) NOT NULL,
1429 `title` varchar(60) NOT NULL,
1430 `description` varchar(180) NOT NULL,
1431 `slug` varchar(55) NULL,
1432 `body` text NOT NULL,
1433 `active` tinyint(1) NOT NULL,
1434 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1435 `order_no` tinyint DEFAULT NULL,
1436 PRIMARY KEY (`id`)
1437) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
1438
1439CREATE TABLE IF NOT EXISTS `sma_shop_settings` (
1440 `shop_id` int(11) NOT NULL,
1441 `shop_name` varchar(55) NOT NULL,
1442 `description` varchar(160) NOT NULL,
1443 `warehouse` int(11) NOT NULL,
1444 `biller` int(11) NOT NULL,
1445 `about_link` varchar(55) NOT NULL,
1446 `terms_link` varchar(55) NOT NULL,
1447 `privacy_link` varchar(55) NOT NULL,
1448 `contact_link` varchar(55) NOT NULL,
1449 `payment_text` varchar(100) NOT NULL,
1450 `follow_text` varchar(100) NOT NULL,
1451 `facebook` varchar(55) NOT NULL,
1452 `twitter` varchar(55) NULL,
1453 `google_plus` varchar(55) NULL,
1454 `instagram` varchar(55) NULL,
1455 `phone` VARCHAR(30) NULL,
1456 `email` VARCHAR(55) NULL,
1457 `cookie_message` VARCHAR(180) NULL,
1458 `cookie_link` VARCHAR(55) NULL,
1459 `slider` TEXT NULL,
1460 `shipping` INT NULL,
1461 `purchase_code` VARCHAR(100) NULL DEFAULT 'purchase_code',
1462 `envato_username` VARCHAR(50) NULL DEFAULT 'envato_username',
1463 `version` VARCHAR(10) NULL DEFAULT '3.4.47',
1464 `logo` VARCHAR(55) NULL,
1465 `bank_details` VARCHAR(255) NULL,
1466 `products_page` TINYINT(1) NULL,
1467 `hide0` TINYINT(1) NULL DEFAULT '0',
1468 `products_description` VARCHAR(255) NULL,
1469 `private` TINYINT(1) NULL DEFAULT '0',
1470 `hide_price` TINYINT(1) NULL DEFAULT '0',
1471 `stripe` tinyint(1) DEFAULT '0',
1472 PRIMARY KEY (`shop_id`)
1473) DEFAULT CHARSET=utf8;
1474
1475INSERT INTO `sma_shop_settings` (`shop_id`, `logo`, `shop_name`, `description`, `warehouse`, `biller`, `about_link`, `terms_link`, `privacy_link`, `contact_link`, `payment_text`, `follow_text`, `facebook`, `twitter`, `google_plus`, `instagram`, `phone`, `email`, `cookie_message`, `cookie_link`, `slider`, `shipping`, `version`, `purchase_code`) VALUES
1476(1, 'sma-shop.png', 'SMA Shop', 'Stock Manager Advance - SMA Shop - Demo Ecommerce Shop that would help you to sell your products from your site.', 1, 3, 'about', 'terms-conditions', 'privacy-policy', 'contact', 'We accept PayPal or you can pay with your credit/debit cards.', 'Please click the link below to follow us on social media.', 'http://facebook.com/tecdiary', 'http://twitter.com/tecdiary', '', '', '010 1234 567', 'info@tecdiary.com', 'We use cookies to improve your experience on our website. By browsing this website, you agree to our use of cookies.', '', '[{\"image\":\"s1.jpg\",\"link\":\"http:\\/\\/ci.dev\\/sma\\/shop\\/products\",\"caption\":\"\"},{\"image\":\"s2.jpg\",\"link\":\"\",\"caption\":\"\"},{\"image\":\"s3.jpg\",\"link\":\"\",\"caption\":\"\"},{\"link\":\"\",\"caption\":\"\"},{\"link\":\"\",\"caption\":\"\"}]', 0, '3.4.47', '');
1477
1478
1479CREATE TABLE IF NOT EXISTS `sma_api_keys` (
1480 `id` INT(11) NOT NULL AUTO_INCREMENT,
1481 `user_id` INT(11) NOT NULL,
1482 `reference` VARCHAR(40) NOT NULL,
1483 `key` VARCHAR(40) NOT NULL,
1484 `level` INT(2) NOT NULL,
1485 `ignore_limits` TINYINT(1) NOT NULL DEFAULT '0',
1486 `is_private_key` TINYINT(1) NOT NULL DEFAULT '0',
1487 `ip_addresses` TEXT NULL DEFAULT NULL,
1488 `date_created` INT(11) NOT NULL,
1489 PRIMARY KEY (`id`)
1490) DEFAULT CHARSET=utf8;
1491
1492CREATE TABLE IF NOT EXISTS `sma_api_logs` (
1493 `id` INT(11) NOT NULL AUTO_INCREMENT,
1494 `uri` VARCHAR(255) NOT NULL,
1495 `method` VARCHAR(6) NOT NULL,
1496 `params` TEXT DEFAULT NULL,
1497 `api_key` VARCHAR(40) NOT NULL,
1498 `ip_address` VARCHAR(45) NOT NULL,
1499 `time` INT(11) NOT NULL,
1500 `rtime` FLOAT DEFAULT NULL,
1501 `authorized` VARCHAR(1) NOT NULL,
1502 `response_code` smallint(3) DEFAULT '0',
1503 PRIMARY KEY (`id`)
1504) DEFAULT CHARSET=utf8;
1505
1506CREATE TABLE IF NOT EXISTS `sma_api_limits` (
1507 `id` INT(11) NOT NULL AUTO_INCREMENT,
1508 `uri` VARCHAR(255) NOT NULL,
1509 `count` INT(10) NOT NULL,
1510 `hour_started` INT(11) NOT NULL,
1511 `api_key` VARCHAR(40) NOT NULL,
1512 PRIMARY KEY (`id`)
1513) DEFAULT CHARSET=utf8;
1514
1515update `sma_categories` set slug = REPLACE(LOWER(name), ' ', '-');
1516update `sma_brands` set slug = REPLACE(LOWER(name), ' ', '-');
1517update `sma_products` set slug = REPLACE(LOWER(name), ' ', '-');
1518
1519CREATE TABLE `sma_sms_settings` (
1520 `id` int(11) NOT NULL AUTO_INCREMENT,
1521 `auto_send` TINYINT(1) NULL,
1522 `config` text NULL,
1523 PRIMARY KEY (`id`)
1524) DEFAULT CHARSET=utf8;
1525INSERT INTO `sma_sms_settings` (`id`, `config`) VALUES ('1', '{"gateway":"Log","Log":{}');
1526
1527CREATE TABLE `sma_logs` (
1528 `id` int(11) NOT NULL AUTO_INCREMENT,
1529 `detail` varchar(190) NOT NULL,
1530 `model` longtext NULL,
1531 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
1532 PRIMARY KEY (`id`)
1533) DEFAULT CHARSET=utf8;