· 5 years ago · Jun 30, 2020, 11:02 AM
1-- --------------------------------------------------------
2-- Host: localhost
3-- Server version: 5.7.24 - MySQL Community Server (GPL)
4-- Server OS: Win64
5-- HeidiSQL Version: 10.2.0.5599
6-- --------------------------------------------------------
7
8/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
9/*!40101 SET NAMES utf8 */;
10/*!50503 SET NAMES utf8mb4 */;
11/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
12/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
13
14
15-- Dumping database structure for uas_market
16CREATE DATABASE IF NOT EXISTS `uas_market` /*!40100 DEFAULT CHARACTER SET latin1 */;
17USE `uas_market`;
18
19-- Dumping structure for table uas_market.brand
20CREATE TABLE IF NOT EXISTS `brand` (
21 `id` int(11) NOT NULL AUTO_INCREMENT,
22 `name` varchar(255) NOT NULL,
23 PRIMARY KEY (`id`)
24) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
25
26-- Dumping data for table uas_market.brand: ~5 rows (approximately)
27/*!40000 ALTER TABLE `brand` DISABLE KEYS */;
28INSERT INTO `brand` (`id`, `name`) VALUES
29 (1, 'Indofood'),
30 (2, 'ABC'),
31 (3, 'Wings Food'),
32 (4, 'Sosro'),
33 (5, 'Sinde');
34/*!40000 ALTER TABLE `brand` ENABLE KEYS */;
35
36-- Dumping structure for table uas_market.buy_transaction
37CREATE TABLE IF NOT EXISTS `buy_transaction` (
38 `id` int(11) NOT NULL AUTO_INCREMENT,
39 `customer_id` int(11) NOT NULL,
40 `user_id` int(11) DEFAULT NULL,
41 `status` enum('created','submitted','cancelled') NOT NULL DEFAULT 'created',
42 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
43 PRIMARY KEY (`id`),
44 KEY `customer_id` (`customer_id`),
45 KEY `user_id` (`user_id`),
46 CONSTRAINT `buy_transaction_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
47 CONSTRAINT `buy_transaction_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
48) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
49
50-- Dumping data for table uas_market.buy_transaction: ~1 rows (approximately)
51/*!40000 ALTER TABLE `buy_transaction` DISABLE KEYS */;
52INSERT INTO `buy_transaction` (`id`, `customer_id`, `user_id`, `status`, `time`) VALUES
53 (3, 1, 1, 'submitted', '2020-06-23 19:14:35');
54/*!40000 ALTER TABLE `buy_transaction` ENABLE KEYS */;
55
56-- Dumping structure for table uas_market.buy_transaction_detail
57CREATE TABLE IF NOT EXISTS `buy_transaction_detail` (
58 `id` int(11) NOT NULL AUTO_INCREMENT,
59 `buy_transaction_id` int(11) NOT NULL,
60 `item_id` int(11) NOT NULL,
61 `amount` int(11) NOT NULL,
62 `status` enum('created','submitted','cancelled') NOT NULL DEFAULT 'created',
63 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
64 PRIMARY KEY (`id`),
65 KEY `buy_transaction_id` (`buy_transaction_id`),
66 KEY `item_id` (`item_id`),
67 CONSTRAINT `buy_transaction_detail_ibfk_1` FOREIGN KEY (`buy_transaction_id`) REFERENCES `buy_transaction` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
68 CONSTRAINT `buy_transaction_detail_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`) ON UPDATE CASCADE
69) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
70
71-- Dumping data for table uas_market.buy_transaction_detail: ~2 rows (approximately)
72/*!40000 ALTER TABLE `buy_transaction_detail` DISABLE KEYS */;
73INSERT INTO `buy_transaction_detail` (`id`, `buy_transaction_id`, `item_id`, `amount`, `status`, `time`) VALUES
74 (8, 3, 1, 2, 'submitted', '2020-06-23 16:04:37'),
75 (9, 3, 2, 3, 'submitted', '2020-06-23 16:04:37');
76/*!40000 ALTER TABLE `buy_transaction_detail` ENABLE KEYS */;
77
78-- Dumping structure for table uas_market.customer
79CREATE TABLE IF NOT EXISTS `customer` (
80 `id` int(11) NOT NULL AUTO_INCREMENT,
81 `name` varchar(255) NOT NULL,
82 `address` text NOT NULL,
83 PRIMARY KEY (`id`)
84) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
85
86-- Dumping data for table uas_market.customer: ~5 rows (approximately)
87/*!40000 ALTER TABLE `customer` DISABLE KEYS */;
88INSERT INTO `customer` (`id`, `name`, `address`) VALUES
89 (1, 'Adnyana', 'Badung'),
90 (2, 'Nonik', 'Denpasar'),
91 (3, 'Yoga', 'Bangli'),
92 (4, 'Yosep', 'Denpasar'),
93 (5, 'Imran', 'Tabanan');
94/*!40000 ALTER TABLE `customer` ENABLE KEYS */;
95
96-- Dumping structure for table uas_market.item
97CREATE TABLE IF NOT EXISTS `item` (
98 `id` int(11) NOT NULL AUTO_INCREMENT,
99 `name` varchar(255) NOT NULL,
100 `brand_id` int(11) DEFAULT NULL,
101 `stock` int(11) NOT NULL,
102 `price_buy` int(11) NOT NULL,
103 `price_sell` int(11) NOT NULL,
104 `unit_id` int(11) DEFAULT NULL,
105 PRIMARY KEY (`id`),
106 KEY `item_ibfk_1` (`brand_id`),
107 KEY `item_ibfk_2` (`unit_id`),
108 CONSTRAINT `item_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `brand` (`id`) ON UPDATE CASCADE,
109 CONSTRAINT `item_ibfk_2` FOREIGN KEY (`unit_id`) REFERENCES `unit` (`id`) ON UPDATE CASCADE
110) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
111
112-- Dumping data for table uas_market.item: ~6 rows (approximately)
113/*!40000 ALTER TABLE `item` DISABLE KEYS */;
114INSERT INTO `item` (`id`, `name`, `brand_id`, `stock`, `price_buy`, `price_sell`, `unit_id`) VALUES
115 (1, 'Sambal Extra Pedas', 1, 20, 10000, 14000, 6),
116 (2, 'Indomie Sambal Matah', 1, 39, 2000, 2500, 2),
117 (3, 'Mi Sedap Kari Special', 3, 4, 45000, 50000, 5),
118 (4, 'Sambal Asli', 2, 15, 8000, 9500, 6),
119 (5, 'Kopi Susu', 2, 50, 1000, 1500, 2),
120 (6, 'Kopi Mocca', 2, 40, 1000, 1500, 2);
121/*!40000 ALTER TABLE `item` ENABLE KEYS */;
122
123-- Dumping structure for table uas_market.supplier
124CREATE TABLE IF NOT EXISTS `supplier` (
125 `id` int(11) NOT NULL AUTO_INCREMENT,
126 `name` varchar(255) NOT NULL,
127 `address` text NOT NULL,
128 PRIMARY KEY (`id`)
129) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
130
131-- Dumping data for table uas_market.supplier: ~4 rows (approximately)
132/*!40000 ALTER TABLE `supplier` DISABLE KEYS */;
133INSERT INTO `supplier` (`id`, `name`, `address`) VALUES
134 (1, 'Grosir Untung', 'Denpasar'),
135 (2, 'Grosir Apem', 'Badung'),
136 (3, 'Grosir Yoga', 'Tabanan'),
137 (4, 'Grosir Bu Eka', 'Denpasar');
138/*!40000 ALTER TABLE `supplier` ENABLE KEYS */;
139
140-- Dumping structure for table uas_market.supply_transaction
141CREATE TABLE IF NOT EXISTS `supply_transaction` (
142 `id` int(11) NOT NULL AUTO_INCREMENT,
143 `user_id` int(11) NOT NULL,
144 `supplier_id` int(11) NOT NULL,
145 `status` enum('created','submitted') NOT NULL DEFAULT 'created',
146 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
147 PRIMARY KEY (`id`),
148 KEY `user_id` (`user_id`),
149 KEY `supplier_id` (`supplier_id`),
150 CONSTRAINT `supply_transaction_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE,
151 CONSTRAINT `supply_transaction_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `supplier` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
152) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
153
154-- Dumping data for table uas_market.supply_transaction: ~2 rows (approximately)
155/*!40000 ALTER TABLE `supply_transaction` DISABLE KEYS */;
156INSERT INTO `supply_transaction` (`id`, `user_id`, `supplier_id`, `status`, `time`) VALUES
157 (1, 1, 1, 'submitted', '2020-06-23 18:31:05'),
158 (2, 1, 1, 'created', '2020-06-23 18:34:34');
159/*!40000 ALTER TABLE `supply_transaction` ENABLE KEYS */;
160
161-- Dumping structure for table uas_market.supply_transaction_detail
162CREATE TABLE IF NOT EXISTS `supply_transaction_detail` (
163 `id` int(11) NOT NULL AUTO_INCREMENT,
164 `supply_transaction_id` int(11) NOT NULL,
165 `item_id` int(11) NOT NULL,
166 `amount` int(11) NOT NULL,
167 `status` enum('created','submitted') NOT NULL DEFAULT 'created',
168 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
169 PRIMARY KEY (`id`),
170 KEY `buy_transaction_id` (`supply_transaction_id`),
171 KEY `item_id` (`item_id`),
172 CONSTRAINT `supply_transaction_detail_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`) ON UPDATE CASCADE,
173 CONSTRAINT `supply_transaction_detail_ibfk_2` FOREIGN KEY (`supply_transaction_id`) REFERENCES `supply_transaction` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
174) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
175
176-- Dumping data for table uas_market.supply_transaction_detail: ~2 rows (approximately)
177/*!40000 ALTER TABLE `supply_transaction_detail` DISABLE KEYS */;
178INSERT INTO `supply_transaction_detail` (`id`, `supply_transaction_id`, `item_id`, `amount`, `status`, `time`) VALUES
179 (1, 1, 1, 2, 'submitted', '2020-06-23 18:46:31'),
180 (2, 1, 2, 2, 'submitted', '2020-06-23 18:46:31');
181/*!40000 ALTER TABLE `supply_transaction_detail` ENABLE KEYS */;
182
183-- Dumping structure for table uas_market.unit
184CREATE TABLE IF NOT EXISTS `unit` (
185 `id` int(11) NOT NULL AUTO_INCREMENT,
186 `name` varchar(50) DEFAULT NULL,
187 PRIMARY KEY (`id`)
188) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
189
190-- Dumping data for table uas_market.unit: ~6 rows (approximately)
191/*!40000 ALTER TABLE `unit` DISABLE KEYS */;
192INSERT INTO `unit` (`id`, `name`) VALUES
193 (1, 'unit'),
194 (2, 'pcs'),
195 (3, 'kg'),
196 (4, 'gram'),
197 (5, 'box'),
198 (6, 'botol');
199/*!40000 ALTER TABLE `unit` ENABLE KEYS */;
200
201-- Dumping structure for table uas_market.user
202CREATE TABLE IF NOT EXISTS `user` (
203 `id` int(11) NOT NULL AUTO_INCREMENT,
204 `username` varchar(255) NOT NULL,
205 `password` varchar(255) NOT NULL,
206 `name` varchar(255) NOT NULL,
207 `phone` varchar(255) NOT NULL,
208 `address` text NOT NULL,
209 `email` varchar(255) NOT NULL,
210 `status` enum('active','inactive') NOT NULL,
211 PRIMARY KEY (`id`)
212) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
213
214-- Dumping data for table uas_market.user: ~3 rows (approximately)
215/*!40000 ALTER TABLE `user` DISABLE KEYS */;
216INSERT INTO `user` (`id`, `username`, `password`, `name`, `phone`, `address`, `email`, `status`) VALUES
217 (1, 'laba', '12345', 'Laba Subagia', '089321234412', 'Badung', 'laba@mail.com', 'active'),
218 (2, 'ufik', '12345', 'Ufik Alngatiq', '089232123431', 'Denpasar', 'ufik@mail.com', 'active'),
219 (3, 'bayu', '12345', 'Bayu', '082123412312', 'Denpasar', 'bayu@mail.com', 'active'),
220 (4, 'mokil', '12345', 'Mokil', '089132123412', 'Denpasar', 'mokil@mail.com', 'active');
221/*!40000 ALTER TABLE `user` ENABLE KEYS */;
222
223-- Dumping structure for view uas_market.buy_transaction_detail_v_all
224-- Creating temporary table to overcome VIEW dependency errors
225CREATE TABLE `buy_transaction_detail_v_all` (
226 `id` INT(11) NOT NULL,
227 `buy_transaction_id` INT(11) NOT NULL,
228 `item_id` INT(11) NOT NULL,
229 `amount` INT(11) NOT NULL,
230 `status` ENUM('created','submitted','cancelled') NOT NULL COLLATE 'latin1_swedish_ci',
231 `time` TIMESTAMP NOT NULL,
232 `customer_id` INT(11) NOT NULL,
233 `customer_name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
234 `customer_address` TEXT NULL COLLATE 'latin1_swedish_ci',
235 `user_id` INT(11) NULL,
236 `cashier` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
237 `item_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
238 `item_price` INT(11) NOT NULL,
239 `item_brand_name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
240 `subtotal_price` BIGINT(21) NOT NULL,
241 `item_unit` VARCHAR(50) NULL COLLATE 'latin1_swedish_ci'
242) ENGINE=MyISAM;
243
244-- Dumping structure for view uas_market.buy_transaction_v_all
245-- Creating temporary table to overcome VIEW dependency errors
246CREATE TABLE `buy_transaction_v_all` (
247 `id` INT(11) NOT NULL,
248 `customer_id` INT(11) NOT NULL,
249 `user_id` INT(11) NULL,
250 `status` ENUM('created','submitted','cancelled') NOT NULL COLLATE 'latin1_swedish_ci',
251 `time` TIMESTAMP NOT NULL,
252 `customer_name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
253 `customer_address` TEXT NULL COLLATE 'latin1_swedish_ci',
254 `cashier` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
255 `total_price` DECIMAL(42,0) NULL
256) ENGINE=MyISAM;
257
258-- Dumping structure for view uas_market.item_v_all
259-- Creating temporary table to overcome VIEW dependency errors
260CREATE TABLE `item_v_all` (
261 `id` INT(11) NOT NULL,
262 `name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
263 `brand_id` INT(11) NULL,
264 `stock` INT(11) NOT NULL,
265 `price_buy` INT(11) NOT NULL,
266 `price_sell` INT(11) NOT NULL,
267 `unit_id` INT(11) NULL,
268 `brand_name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
269 `unit_name` VARCHAR(50) NULL COLLATE 'latin1_swedish_ci'
270) ENGINE=MyISAM;
271
272-- Dumping structure for view uas_market.partner_transaction_v_all
273-- Creating temporary table to overcome VIEW dependency errors
274CREATE TABLE `partner_transaction_v_all` (
275 `id` INT(11) NOT NULL,
276 `admin` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
277 `partner_name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
278 `partner_address` TEXT NULL COLLATE 'latin1_swedish_ci',
279 `transaction_type` VARCHAR(7) NOT NULL COLLATE 'utf8mb4_general_ci',
280 `total_price` DECIMAL(42,0) NULL,
281 `time` TIMESTAMP NOT NULL
282) ENGINE=MyISAM;
283
284-- Dumping structure for view uas_market.supply_transaction_detail_v_all
285-- Creating temporary table to overcome VIEW dependency errors
286CREATE TABLE `supply_transaction_detail_v_all` (
287 `id` INT(11) NOT NULL,
288 `supply_transaction_id` INT(11) NOT NULL,
289 `item_id` INT(11) NOT NULL,
290 `amount` INT(11) NOT NULL,
291 `status` ENUM('created','submitted') NOT NULL COLLATE 'latin1_swedish_ci',
292 `time` TIMESTAMP NOT NULL,
293 `supplier_id` INT(11) NOT NULL,
294 `supplier_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
295 `supplier_address` TEXT NOT NULL COLLATE 'latin1_swedish_ci',
296 `user_id` INT(11) NOT NULL,
297 `admin_username` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
298 `admin_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
299 `item_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
300 `item_price` INT(11) NOT NULL,
301 `item_unit` VARCHAR(50) NULL COLLATE 'latin1_swedish_ci',
302 `item_brand_name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
303 `subtotal_price` BIGINT(21) NOT NULL
304) ENGINE=MyISAM;
305
306-- Dumping structure for view uas_market.supply_transaction_v_all
307-- Creating temporary table to overcome VIEW dependency errors
308CREATE TABLE `supply_transaction_v_all` (
309 `id` INT(11) NOT NULL,
310 `user_id` INT(11) NOT NULL,
311 `supplier_id` INT(11) NOT NULL,
312 `status` ENUM('created','submitted') NOT NULL COLLATE 'latin1_swedish_ci',
313 `time` TIMESTAMP NOT NULL,
314 `supplier_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
315 `supplier_address` TEXT NOT NULL COLLATE 'latin1_swedish_ci',
316 `admin_username` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
317 `admin_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
318 `total_price` DECIMAL(42,0) NULL
319) ENGINE=MyISAM;
320
321-- Dumping structure for procedure uas_market.BuyTransactionCancel
322DELIMITER //
323CREATE DEFINER=`root`@`localhost` PROCEDURE `BuyTransactionCancel`(
324 IN param_id int
325)
326BEGIN
327 DECLARE confirm_status varchar(20) DEFAULT 'cancelled';
328 UPDATE buy_transaction
329 SET status=confirm_status
330 WHERE id=param_id;
331 UPDATE buy_transaction_detail
332 SET status=confirm_status
333 WHERE buy_transaction_id=param_id;
334
335 SELECT
336 id,
337 cashier,
338 customer_name,
339 customer_address,
340 total_price,
341 `status`,
342 time
343 FROM buy_transaction_v_all
344 WHERE id = param_id;
345END//
346DELIMITER ;
347
348-- Dumping structure for procedure uas_market.BuyTransactionConfirm
349DELIMITER //
350CREATE DEFINER=`root`@`localhost` PROCEDURE `BuyTransactionConfirm`(
351 IN `param_id` int
352)
353BEGIN
354 DECLARE confirm_status varchar(20) DEFAULT 'submitted';
355 UPDATE buy_transaction
356 SET status=confirm_status
357 WHERE id=param_id;
358 UPDATE buy_transaction_detail
359 SET status=confirm_status
360 WHERE buy_transaction_id=param_id;
361
362 SELECT
363 id,
364 cashier,
365 customer_name,
366 customer_address,
367 total_price,
368 `status`,
369 time
370 FROM buy_transaction_v_all
371 WHERE id = param_id;
372END//
373DELIMITER ;
374
375-- Dumping structure for procedure uas_market.BuyTransactionCreate
376DELIMITER //
377CREATE DEFINER=`root`@`localhost` PROCEDURE `BuyTransactionCreate`(
378 IN `param_customer_id` int,
379 IN `param_user_id` int
380
381)
382BEGIN
383 INSERT INTO buy_transaction
384 (customer_id, user_id)
385 VALUES
386 (param_customer_id, param_user_id);
387 SELECT
388 id,
389 cashier,
390 customer_name,
391 customer_address,
392 total_price,
393 `status`,
394 time
395 FROM buy_transaction_v_all
396 WHERE id = LAST_INSERT_ID();
397END//
398DELIMITER ;
399
400-- Dumping structure for procedure uas_market.BuyTransactionDetailCreate
401DELIMITER //
402CREATE DEFINER=`root`@`localhost` PROCEDURE `BuyTransactionDetailCreate`(
403 IN `param_buy_transaction_id` int,
404 IN `param_item_id` int,
405 IN `param_amount` int
406
407
408
409)
410BEGIN
411 INSERT INTO buy_transaction_detail
412 (buy_transaction_id, item_id, amount)
413 VALUES
414 (param_buy_transaction_id, param_item_id, param_amount);
415
416 SELECT
417 buy_transaction_id,
418 cashier,
419 customer_name,
420 item_name,
421 amount,
422 item_unit,
423 item_price,
424 subtotal_price
425 FROM buy_transaction_detail_v_all
426 WHERE id = LAST_INSERT_ID();
427END//
428DELIMITER ;
429
430-- Dumping structure for procedure uas_market.SupplyTransactionConfirm
431DELIMITER //
432CREATE DEFINER=`root`@`localhost` PROCEDURE `SupplyTransactionConfirm`(
433 IN `param_id` int
434)
435BEGIN
436 DECLARE confirm_status varchar(20) DEFAULT 'submitted';
437 UPDATE supply_transaction
438 SET status=confirm_status
439 WHERE id=param_id;
440 UPDATE supply_transaction_detail
441 SET status=confirm_status
442 WHERE supply_transaction_id=param_id;
443
444 SELECT
445 id,
446 supplier_name,
447 supplier_address,
448 admin_username,
449 total_price,
450 `status`,
451 time
452 FROM supply_transaction_v_all
453 WHERE id = param_id;
454END//
455DELIMITER ;
456
457-- Dumping structure for procedure uas_market.SupplyTransactionCreate
458DELIMITER //
459CREATE DEFINER=`root`@`localhost` PROCEDURE `SupplyTransactionCreate`(
460 IN `param_supplier_id` int,
461 IN `param_user_id` int
462
463)
464BEGIN
465 INSERT INTO supply_transaction
466 (supplier_id, user_id)
467 VALUES
468 (param_supplier_id, param_user_id);
469
470 SELECT
471 id,
472 supplier_name,
473 supplier_address,
474 admin_username,
475 total_price,
476 `status`,
477 time
478 FROM supply_transaction_v_all
479 WHERE id = LAST_INSERT_ID();
480END//
481DELIMITER ;
482
483-- Dumping structure for procedure uas_market.SupplyTransactionDetailCreate
484DELIMITER //
485CREATE DEFINER=`root`@`localhost` PROCEDURE `SupplyTransactionDetailCreate`(
486 IN param_supply_transaction_id int,
487 IN param_item_id int, IN param_amount int
488)
489BEGIN
490 INSERT INTO supply_transaction_detail
491 (supply_transaction_id, item_id, amount)
492 VALUES
493 (param_supply_transaction_id, param_item_id, param_amount);
494
495 SELECT
496 supply_transaction_id,
497 admin_username,
498 supplier_name,
499 item_name,
500 amount,
501 item_unit,
502 item_price,
503 subtotal_price
504 FROM supply_transaction_detail_v_all
505 WHERE id = LAST_INSERT_ID();
506END//
507DELIMITER ;
508
509-- Dumping structure for function uas_market.BalanceByMonth
510DELIMITER //
511CREATE DEFINER=`root`@`localhost` FUNCTION `BalanceByMonth`(
512 `month` int,
513 `year` int
514) RETURNS int(11)
515BEGIN
516 DECLARE balance int;
517 SELECT SellByMonth(month, year)-SpendByMonth(month, year)
518 INTO balance;
519 RETURN balance;
520END//
521DELIMITER ;
522
523-- Dumping structure for function uas_market.BalanceByYear
524DELIMITER //
525CREATE DEFINER=`root`@`localhost` FUNCTION `BalanceByYear`(
526 `year` int
527) RETURNS int(11)
528BEGIN
529 DECLARE balance int;
530 SELECT SellByYear(year)-SpendByYear(year) INTO balance;
531 RETURN balance;
532END//
533DELIMITER ;
534
535-- Dumping structure for function uas_market.ProfitByMonth
536DELIMITER //
537CREATE DEFINER=`root`@`localhost` FUNCTION `ProfitByMonth`(
538 `month` INT,
539 `year` INT
540) RETURNS int(11)
541BEGIN
542 DECLARE profit int;
543 SELECT
544 SUM(btd.amount*(i.price_sell-i.price_buy)) INTO profit
545 FROM buy_transaction_detail btd
546 INNER JOIN item i ON btd.item_id = i.id
547 WHERE MONTH(btd.time) = month
548 AND YEAR(btd.time) = year
549 AND status='submitted';
550 RETURN profit;
551END//
552DELIMITER ;
553
554-- Dumping structure for function uas_market.ProfitByYear
555DELIMITER //
556CREATE DEFINER=`root`@`localhost` FUNCTION `ProfitByYear`(
557 `year` int
558) RETURNS int(11)
559BEGIN
560 DECLARE profit int;
561 SELECT
562 SUM(btd.amount*(i.price_sell-i.price_buy)) INTO profit
563 FROM buy_transaction_detail btd
564 INNER JOIN item i ON btd.item_id = i.id
565 WHERE YEAR(btd.time) = year
566 AND status='submitted';
567 RETURN profit;
568END//
569DELIMITER ;
570
571-- Dumping structure for function uas_market.SellByMonth
572DELIMITER //
573CREATE DEFINER=`root`@`localhost` FUNCTION `SellByMonth`(
574 `month` INT,
575 `year` INT
576) RETURNS int(11)
577BEGIN
578 DECLARE total int;
579 SELECT SUM(amount*item_price) INTO total
580 FROM buy_transaction_detail_v_all
581 WHERE status='submitted'
582 AND MONTH(time) = month
583 AND YEAR(time) = year;
584 RETURN total;
585END//
586DELIMITER ;
587
588-- Dumping structure for function uas_market.SellByYear
589DELIMITER //
590CREATE DEFINER=`root`@`localhost` FUNCTION `SellByYear`(
591 `year` INT
592) RETURNS int(11)
593BEGIN
594 DECLARE total int;
595 SELECT SUM(amount*item_price) INTO total
596 FROM buy_transaction_detail_v_all
597 WHERE status='submitted'
598 AND YEAR(time) = year;
599 RETURN total;
600END//
601DELIMITER ;
602
603-- Dumping structure for function uas_market.SpendByMonth
604DELIMITER //
605CREATE DEFINER=`root`@`localhost` FUNCTION `SpendByMonth`(
606 `month` INT,
607 `year` INT
608) RETURNS int(11)
609BEGIN
610 DECLARE spend int;
611 SELECT
612 SUM(amount*item_price) INTO spend
613 FROM supply_transaction_detail_v_all
614 WHERE status='submitted'
615 AND MONTH(time) = month
616 AND YEAR(time) = year;
617 RETURN spend;
618END//
619DELIMITER ;
620
621-- Dumping structure for function uas_market.SpendByYear
622DELIMITER //
623CREATE DEFINER=`root`@`localhost` FUNCTION `SpendByYear`(
624 `year` INT
625) RETURNS int(11)
626BEGIN
627 DECLARE spend int;
628 SELECT
629 SUM(amount*item_price) INTO spend
630 FROM supply_transaction_detail_v_all
631 WHERE status='submitted'
632 AND YEAR(time) = year;
633 RETURN spend;
634END//
635DELIMITER ;
636
637-- Dumping structure for trigger uas_market.BuyTransactionUpdate
638SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
639DELIMITER //
640CREATE TRIGGER `BuyTransactionUpdate` AFTER UPDATE ON `buy_transaction_detail` FOR EACH ROW BEGIN
641 DECLARE current_stock int;
642 SELECT stock INTO current_stock FROM item WHERE id=NEW.item_id;
643 IF NEW.status = 'submitted' AND current_stock > 0 THEN
644 UPDATE item SET stock=stock-NEW.amount WHERE id=NEW.item_id;
645 ELSEIF OLD.status = 'submitted' AND NEW.status = 'cancelled' THEN
646 UPDATE item SET stock=stock+NEW.amount WHERE id=NEW.item_id;
647 END IF;
648END//
649DELIMITER ;
650SET SQL_MODE=@OLDTMP_SQL_MODE;
651
652-- Dumping structure for trigger uas_market.SupplyTransactionUpdate
653SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
654DELIMITER //
655CREATE TRIGGER `SupplyTransactionUpdate` AFTER UPDATE ON `supply_transaction_detail` FOR EACH ROW BEGIN
656 IF NEW.status = 'submitted' THEN
657 UPDATE item
658 SET stock=stock+NEW.amount
659 WHERE id=NEW.item_id;
660 END IF;
661END//
662DELIMITER ;
663SET SQL_MODE=@OLDTMP_SQL_MODE;
664
665-- Dumping structure for view uas_market.buy_transaction_detail_v_all
666-- Removing temporary table and create final VIEW structure
667DROP TABLE IF EXISTS `buy_transaction_detail_v_all`;
668CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `buy_transaction_detail_v_all` AS SELECT
669 btd.*,
670 bt.customer_id,
671 bt.customer_name,
672 bt.customer_address,
673 bt.user_id,
674 bt.cashier,
675 i.name AS item_name,
676 i.price_sell AS item_price,
677 i.brand_name AS item_brand_name,
678 i.price_sell*btd.amount as subtotal_price,
679 i.unit_name as item_unit
680FROM buy_transaction_detail btd
681 INNER JOIN buy_transaction_v_all bt
682 ON btd.buy_transaction_id = bt.id
683 INNER JOIN item_v_all i
684 ON btd.item_id = i.id ;
685
686-- Dumping structure for view uas_market.buy_transaction_v_all
687-- Removing temporary table and create final VIEW structure
688DROP TABLE IF EXISTS `buy_transaction_v_all`;
689CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `buy_transaction_v_all` AS SELECT
690 bt.*,
691 c.name as customer_name,
692 c.address as customer_address,
693 u.username as cashier,
694 (
695 SELECT SUM(amount*price_sell)
696 FROM buy_transaction_detail btd
697 INNER JOIN item i ON btd.item_id = i.id
698 WHERE buy_transaction_id=bt.id
699 ) AS total_price
700FROM buy_transaction bt
701 -- left join because customer_id can be null
702 LEFT JOIN customer c ON bt.customer_id = c.id
703
704 LEFT JOIN user u ON bt.user_id = u.id ;
705
706-- Dumping structure for view uas_market.item_v_all
707-- Removing temporary table and create final VIEW structure
708DROP TABLE IF EXISTS `item_v_all`;
709CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `item_v_all` AS SELECT i.*, b.name as brand_name, u.name as unit_name
710FROM item i
711 LEFT JOIN brand b ON i.brand_id = b.id
712 LEFT JOIN unit u ON i.unit_id = u.id ;
713
714-- Dumping structure for view uas_market.partner_transaction_v_all
715-- Removing temporary table and create final VIEW structure
716DROP TABLE IF EXISTS `partner_transaction_v_all`;
717CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `partner_transaction_v_all` AS SELECT
718 id,
719 admin_username AS admin,
720 supplier_name AS partner_name,
721 supplier_address AS partner_address,
722 'Supply' AS transaction_type,
723 (
724 SELECT sum(amount*item_price)
725 FROM supply_transaction_detail_v_all
726 WHERE supply_transaction_id = st.id
727 ) AS total_price,
728 time
729FROM supply_transaction_v_all AS st
730WHERE STATUS='submitted'
731UNION
732 SELECT
733 id,
734 cashier AS admin,
735 customer_name AS partner_name,
736 customer_address AS partner_address,
737 'Selling' AS transaction_type,
738 (
739 SELECT sum(amount*item_price)
740 FROM buy_transaction_detail_v_all
741 WHERE buy_transaction_id = bt.id
742 ) AS total_price,
743 TIME
744 FROM buy_transaction_v_all bt
745 WHERE STATUS='submitted' ;
746
747-- Dumping structure for view uas_market.supply_transaction_detail_v_all
748-- Removing temporary table and create final VIEW structure
749DROP TABLE IF EXISTS `supply_transaction_detail_v_all`;
750CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `supply_transaction_detail_v_all` AS SELECT
751 sd.*,
752 st.supplier_id,
753 st.supplier_name,
754 st.supplier_address,
755 st.user_id,
756 st.admin_username,
757 st.admin_name,
758 i.name AS item_name,
759 i.price_buy AS item_price,
760 i.unit_name AS item_unit,
761 i.brand_name AS item_brand_name,
762 i.price_buy*sd.amount as subtotal_price
763FROM supply_transaction_detail sd
764 INNER JOIN supply_transaction_v_all st
765 ON st.id = sd.supply_transaction_id
766 INNER JOIN item_v_all i
767 ON i.id = sd.item_id ;
768
769-- Dumping structure for view uas_market.supply_transaction_v_all
770-- Removing temporary table and create final VIEW structure
771DROP TABLE IF EXISTS `supply_transaction_v_all`;
772CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `supply_transaction_v_all` AS SELECT
773 st.`*`,
774 s.name AS supplier_name,
775 s.address AS supplier_address,
776 u.username AS admin_username,
777 u.name AS admin_name,
778 (
779 SELECT SUM(amount*price_buy)
780 FROM supply_transaction_detail td
781 INNER JOIN item i ON td.item_id = i.id
782 WHERE supply_transaction_id=st.id
783 ) AS total_price
784FROM supply_transaction st
785 INNER JOIN supplier s ON st.supplier_id = s.id
786 INNER JOIN user u ON st.user_id = u.id ;
787
788/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
789/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
790/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;