· 5 years ago · Jun 30, 2020, 07:56 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: ~0 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, 'cikal', '12345', 'Cikal Taruna', '081231234312', 'Denpasar', 'cikal@mail.com', 'active'),
219 (3, 'tosan', '12345', 'Tosan Krisna', '082123431234', 'Denpasar', 'tosan@mail.com', 'active');
220/*!40000 ALTER TABLE `user` ENABLE KEYS */;
221
222-- Dumping structure for view uas_market.buy_transaction_detail_v_all
223-- Creating temporary table to overcome VIEW dependency errors
224CREATE TABLE `buy_transaction_detail_v_all` (
225 `id` INT(11) NOT NULL,
226 `buy_transaction_id` INT(11) NOT NULL,
227 `item_id` INT(11) NOT NULL,
228 `amount` INT(11) NOT NULL,
229 `status` ENUM('created','submitted','cancelled') NOT NULL COLLATE 'latin1_swedish_ci',
230 `time` TIMESTAMP NOT NULL,
231 `customer_id` INT(11) NOT NULL,
232 `customer_name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
233 `customer_address` TEXT NULL COLLATE 'latin1_swedish_ci',
234 `user_id` INT(11) NULL,
235 `cashier` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
236 `item_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
237 `item_price` INT(11) NOT NULL,
238 `item_brand_name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
239 `subtotal_price` BIGINT(21) NOT NULL,
240 `item_unit` VARCHAR(50) NULL COLLATE 'latin1_swedish_ci'
241) ENGINE=MyISAM;
242
243-- Dumping structure for view uas_market.buy_transaction_v_all
244-- Creating temporary table to overcome VIEW dependency errors
245CREATE TABLE `buy_transaction_v_all` (
246 `id` INT(11) NOT NULL,
247 `customer_id` INT(11) NOT NULL,
248 `user_id` INT(11) NULL,
249 `status` ENUM('created','submitted','cancelled') NOT NULL COLLATE 'latin1_swedish_ci',
250 `time` TIMESTAMP NOT NULL,
251 `customer_name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
252 `customer_address` TEXT NULL COLLATE 'latin1_swedish_ci',
253 `cashier` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
254 `total_price` DECIMAL(42,0) NULL
255) ENGINE=MyISAM;
256
257-- Dumping structure for view uas_market.item_v_all
258-- Creating temporary table to overcome VIEW dependency errors
259CREATE TABLE `item_v_all` (
260 `id` INT(11) NOT NULL,
261 `name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
262 `brand_id` INT(11) NULL,
263 `stock` INT(11) NOT NULL,
264 `price_buy` INT(11) NOT NULL,
265 `price_sell` INT(11) NOT NULL,
266 `unit_id` INT(11) NULL,
267 `brand_name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
268 `unit_name` VARCHAR(50) NULL COLLATE 'latin1_swedish_ci'
269) ENGINE=MyISAM;
270
271-- Dumping structure for view uas_market.partner_transaction_v_all
272-- Creating temporary table to overcome VIEW dependency errors
273CREATE TABLE `partner_transaction_v_all` (
274 `id` INT(11) NOT NULL,
275 `admin` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
276 `partner_name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
277 `partner_address` TEXT NULL COLLATE 'latin1_swedish_ci',
278 `transaction_type` VARCHAR(7) NOT NULL COLLATE 'utf8mb4_general_ci',
279 `total_price` DECIMAL(42,0) NULL,
280 `time` TIMESTAMP NOT NULL
281) ENGINE=MyISAM;
282
283-- Dumping structure for view uas_market.supply_transaction_detail_v_all
284-- Creating temporary table to overcome VIEW dependency errors
285CREATE TABLE `supply_transaction_detail_v_all` (
286 `id` INT(11) NOT NULL,
287 `supply_transaction_id` INT(11) NOT NULL,
288 `item_id` INT(11) NOT NULL,
289 `amount` INT(11) NOT NULL,
290 `status` ENUM('created','submitted') NOT NULL COLLATE 'latin1_swedish_ci',
291 `time` TIMESTAMP NOT NULL,
292 `supplier_id` INT(11) NOT NULL,
293 `supplier_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
294 `supplier_address` TEXT NOT NULL COLLATE 'latin1_swedish_ci',
295 `user_id` INT(11) NOT NULL,
296 `admin_username` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
297 `admin_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
298 `item_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
299 `item_price` INT(11) NOT NULL,
300 `item_unit` VARCHAR(50) NULL COLLATE 'latin1_swedish_ci',
301 `item_brand_name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
302 `subtotal_price` BIGINT(21) NOT NULL
303) ENGINE=MyISAM;
304
305-- Dumping structure for view uas_market.supply_transaction_v_all
306-- Creating temporary table to overcome VIEW dependency errors
307CREATE TABLE `supply_transaction_v_all` (
308 `id` INT(11) NOT NULL,
309 `user_id` INT(11) NOT NULL,
310 `supplier_id` INT(11) NOT NULL,
311 `status` ENUM('created','submitted') NOT NULL COLLATE 'latin1_swedish_ci',
312 `time` TIMESTAMP NOT NULL,
313 `supplier_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
314 `supplier_address` TEXT NOT NULL COLLATE 'latin1_swedish_ci',
315 `admin_username` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
316 `admin_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
317 `total_price` DECIMAL(42,0) NULL
318) ENGINE=MyISAM;
319
320-- Dumping structure for procedure uas_market.BuyTransactionCancel
321DELIMITER //
322CREATE DEFINER=`root`@`localhost` PROCEDURE `BuyTransactionCancel`(
323 IN param_id int
324)
325BEGIN
326 DECLARE confirm_status varchar(20) DEFAULT 'cancelled';
327 UPDATE buy_transaction
328 SET status=confirm_status
329 WHERE id=param_id;
330 UPDATE buy_transaction_detail
331 SET status=confirm_status
332 WHERE buy_transaction_id=param_id;
333
334 SELECT
335 id,
336 cashier,
337 customer_name,
338 customer_address,
339 total_price,
340 `status`,
341 time
342 FROM buy_transaction_v_all
343 WHERE id = param_id;
344END//
345DELIMITER ;
346
347-- Dumping structure for procedure uas_market.BuyTransactionConfirm
348DELIMITER //
349CREATE DEFINER=`root`@`localhost` PROCEDURE `BuyTransactionConfirm`(
350 IN `param_id` int
351)
352BEGIN
353 DECLARE confirm_status varchar(20) DEFAULT 'submitted';
354 UPDATE buy_transaction
355 SET status=confirm_status
356 WHERE id=param_id;
357 UPDATE buy_transaction_detail
358 SET status=confirm_status
359 WHERE buy_transaction_id=param_id;
360
361 SELECT
362 id,
363 cashier,
364 customer_name,
365 customer_address,
366 total_price,
367 `status`,
368 time
369 FROM buy_transaction_v_all
370 WHERE id = param_id;
371END//
372DELIMITER ;
373
374-- Dumping structure for procedure uas_market.BuyTransactionCreate
375DELIMITER //
376CREATE DEFINER=`root`@`localhost` PROCEDURE `BuyTransactionCreate`(
377 IN `param_customer_id` int,
378 IN `param_user_id` int
379
380)
381BEGIN
382 INSERT INTO buy_transaction
383 (customer_id, user_id)
384 VALUES
385 (param_customer_id, param_user_id);
386 SELECT
387 id,
388 cashier,
389 customer_name,
390 customer_address,
391 total_price,
392 `status`,
393 time
394 FROM buy_transaction_v_all
395 WHERE id = LAST_INSERT_ID();
396END//
397DELIMITER ;
398
399-- Dumping structure for procedure uas_market.BuyTransactionDetailCreate
400DELIMITER //
401CREATE DEFINER=`root`@`localhost` PROCEDURE `BuyTransactionDetailCreate`(
402 IN `param_buy_transaction_id` int,
403 IN `param_item_id` int,
404 IN `param_amount` int
405
406
407
408)
409BEGIN
410 INSERT INTO buy_transaction_detail
411 (buy_transaction_id, item_id, amount)
412 VALUES
413 (param_buy_transaction_id, param_item_id, param_amount);
414
415 SELECT
416 buy_transaction_id,
417 cashier,
418 customer_name,
419 item_name,
420 amount,
421 item_unit,
422 item_price,
423 subtotal_price
424 FROM buy_transaction_detail_v_all
425 WHERE id = LAST_INSERT_ID();
426END//
427DELIMITER ;
428
429-- Dumping structure for procedure uas_market.SupplyTransactionConfirm
430DELIMITER //
431CREATE DEFINER=`root`@`localhost` PROCEDURE `SupplyTransactionConfirm`(
432 IN `param_id` int
433)
434BEGIN
435 DECLARE confirm_status varchar(20) DEFAULT 'submitted';
436 UPDATE supply_transaction
437 SET status=confirm_status
438 WHERE id=param_id;
439 UPDATE supply_transaction_detail
440 SET status=confirm_status
441 WHERE supply_transaction_id=param_id;
442
443 SELECT
444 id,
445 supplier_name,
446 supplier_address,
447 admin_username,
448 total_price,
449 `status`,
450 time
451 FROM supply_transaction_v_all
452 WHERE id = param_id;
453END//
454DELIMITER ;
455
456-- Dumping structure for procedure uas_market.SupplyTransactionCreate
457DELIMITER //
458CREATE DEFINER=`root`@`localhost` PROCEDURE `SupplyTransactionCreate`(
459 IN `param_supplier_id` int,
460 IN `param_user_id` int
461
462)
463BEGIN
464 INSERT INTO supply_transaction
465 (supplier_id, user_id)
466 VALUES
467 (param_supplier_id, param_user_id);
468
469 SELECT
470 id,
471 supplier_name,
472 supplier_address,
473 admin_username,
474 total_price,
475 `status`,
476 time
477 FROM supply_transaction_v_all
478 WHERE id = LAST_INSERT_ID();
479END//
480DELIMITER ;
481
482-- Dumping structure for procedure uas_market.SupplyTransactionDetailCreate
483DELIMITER //
484CREATE DEFINER=`root`@`localhost` PROCEDURE `SupplyTransactionDetailCreate`(
485 IN param_supply_transaction_id int,
486 IN param_item_id int, IN param_amount int
487)
488BEGIN
489 INSERT INTO supply_transaction_detail
490 (supply_transaction_id, item_id, amount)
491 VALUES
492 (param_supply_transaction_id, param_item_id, param_amount);
493
494 SELECT
495 supply_transaction_id,
496 admin_username,
497 supplier_name,
498 item_name,
499 amount,
500 item_unit,
501 item_price,
502 subtotal_price
503 FROM supply_transaction_detail_v_all
504 WHERE id = LAST_INSERT_ID();
505END//
506DELIMITER ;
507
508-- Dumping structure for function uas_market.BalanceByMonth
509DELIMITER //
510CREATE DEFINER=`root`@`localhost` FUNCTION `BalanceByMonth`(
511 `month` int,
512 `year` int
513) RETURNS int(11)
514BEGIN
515 DECLARE balance int;
516 SELECT SellByMonth(month, year)-SpendByMonth(month, year)
517 INTO balance;
518 RETURN balance;
519END//
520DELIMITER ;
521
522-- Dumping structure for function uas_market.BalanceByYear
523DELIMITER //
524CREATE DEFINER=`root`@`localhost` FUNCTION `BalanceByYear`(
525 `year` int
526) RETURNS int(11)
527BEGIN
528 DECLARE balance int;
529 SELECT SellByYear(year)-SpendByYear(year) INTO balance;
530 RETURN balance;
531END//
532DELIMITER ;
533
534-- Dumping structure for function uas_market.ProfitByMonth
535DELIMITER //
536CREATE DEFINER=`root`@`localhost` FUNCTION `ProfitByMonth`(
537 `month` INT,
538 `year` INT
539) RETURNS int(11)
540BEGIN
541 DECLARE profit int;
542 SELECT
543 SUM(btd.amount*(i.price_sell-i.price_buy)) INTO profit
544 FROM buy_transaction_detail btd
545 INNER JOIN item i ON btd.item_id = i.id
546 WHERE MONTH(btd.time) = month
547 AND YEAR(btd.time) = year
548 AND status='submitted';
549 RETURN profit;
550END//
551DELIMITER ;
552
553-- Dumping structure for function uas_market.ProfitByYear
554DELIMITER //
555CREATE DEFINER=`root`@`localhost` FUNCTION `ProfitByYear`(
556 `year` int
557) RETURNS int(11)
558BEGIN
559 DECLARE profit int;
560 SELECT
561 SUM(btd.amount*(i.price_sell-i.price_buy)) INTO profit
562 FROM buy_transaction_detail btd
563 INNER JOIN item i ON btd.item_id = i.id
564 WHERE YEAR(btd.time) = year
565 AND status='submitted';
566 RETURN profit;
567END//
568DELIMITER ;
569
570-- Dumping structure for function uas_market.SellByMonth
571DELIMITER //
572CREATE DEFINER=`root`@`localhost` FUNCTION `SellByMonth`(
573 `month` INT,
574 `year` INT
575) RETURNS int(11)
576BEGIN
577 DECLARE total int;
578 SELECT SUM(amount*item_price) INTO total
579 FROM buy_transaction_detail_v_all
580 WHERE status='submitted'
581 AND MONTH(time) = month
582 AND YEAR(time) = year;
583 RETURN total;
584END//
585DELIMITER ;
586
587-- Dumping structure for function uas_market.SellByYear
588DELIMITER //
589CREATE DEFINER=`root`@`localhost` FUNCTION `SellByYear`(
590 `year` INT
591) RETURNS int(11)
592BEGIN
593 DECLARE total int;
594 SELECT SUM(amount*item_price) INTO total
595 FROM buy_transaction_detail_v_all
596 WHERE status='submitted'
597 AND YEAR(time) = year;
598 RETURN total;
599END//
600DELIMITER ;
601
602-- Dumping structure for function uas_market.SpendByMonth
603DELIMITER //
604CREATE DEFINER=`root`@`localhost` FUNCTION `SpendByMonth`(
605 `month` INT,
606 `year` INT
607) RETURNS int(11)
608BEGIN
609 DECLARE spend int;
610 SELECT
611 SUM(amount*item_price) INTO spend
612 FROM supply_transaction_detail_v_all
613 WHERE status='submitted'
614 AND MONTH(time) = month
615 AND YEAR(time) = year;
616 RETURN spend;
617END//
618DELIMITER ;
619
620-- Dumping structure for function uas_market.SpendByYear
621DELIMITER //
622CREATE DEFINER=`root`@`localhost` FUNCTION `SpendByYear`(
623 `year` INT
624) RETURNS int(11)
625BEGIN
626 DECLARE spend int;
627 SELECT
628 SUM(amount*item_price) INTO spend
629 FROM supply_transaction_detail_v_all
630 WHERE status='submitted'
631 AND YEAR(time) = year;
632 RETURN spend;
633END//
634DELIMITER ;
635
636-- Dumping structure for trigger uas_market.BuyTransactionUpdate
637SET @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';
638DELIMITER //
639CREATE TRIGGER `BuyTransactionUpdate` AFTER UPDATE ON `buy_transaction_detail` FOR EACH ROW BEGIN
640 DECLARE current_stock int;
641 SELECT stock INTO current_stock FROM item WHERE id=NEW.item_id;
642 IF NEW.status = 'submitted' AND current_stock > 0 THEN
643 UPDATE item SET stock=stock-NEW.amount WHERE id=NEW.item_id;
644 ELSEIF OLD.status = 'submitted' AND NEW.status = 'cancelled' THEN
645 UPDATE item SET stock=stock+NEW.amount WHERE id=NEW.item_id;
646 END IF;
647END//
648DELIMITER ;
649SET SQL_MODE=@OLDTMP_SQL_MODE;
650
651-- Dumping structure for trigger uas_market.SupplyTransactionUpdate
652SET @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';
653DELIMITER //
654CREATE TRIGGER `SupplyTransactionUpdate` AFTER UPDATE ON `supply_transaction_detail` FOR EACH ROW BEGIN
655 IF NEW.status = 'submitted' THEN
656 UPDATE item
657 SET stock=stock+NEW.amount
658 WHERE id=NEW.item_id;
659 END IF;
660END//
661DELIMITER ;
662SET SQL_MODE=@OLDTMP_SQL_MODE;
663
664-- Dumping structure for view uas_market.buy_transaction_detail_v_all
665-- Removing temporary table and create final VIEW structure
666DROP TABLE IF EXISTS `buy_transaction_detail_v_all`;
667CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `buy_transaction_detail_v_all` AS SELECT
668 btd.*,
669 bt.customer_id,
670 bt.customer_name,
671 bt.customer_address,
672 bt.user_id,
673 bt.cashier,
674 i.name AS item_name,
675 i.price_sell AS item_price,
676 i.brand_name AS item_brand_name,
677 i.price_sell*btd.amount as subtotal_price,
678 i.unit_name as item_unit
679FROM buy_transaction_detail btd
680 INNER JOIN buy_transaction_v_all bt
681 ON btd.buy_transaction_id = bt.id
682 INNER JOIN item_v_all i
683 ON btd.item_id = i.id ;
684
685-- Dumping structure for view uas_market.buy_transaction_v_all
686-- Removing temporary table and create final VIEW structure
687DROP TABLE IF EXISTS `buy_transaction_v_all`;
688CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `buy_transaction_v_all` AS SELECT
689 bt.*,
690 c.name as customer_name,
691 c.address as customer_address,
692 u.username as cashier,
693 (
694 SELECT SUM(amount*price_sell)
695 FROM buy_transaction_detail btd
696 INNER JOIN item i ON btd.item_id = i.id
697 WHERE buy_transaction_id=bt.id
698 ) AS total_price
699FROM buy_transaction bt
700 -- left join because customer_id can be null
701 LEFT JOIN customer c ON bt.customer_id = c.id
702
703 LEFT JOIN user u ON bt.user_id = u.id ;
704
705-- Dumping structure for view uas_market.item_v_all
706-- Removing temporary table and create final VIEW structure
707DROP TABLE IF EXISTS `item_v_all`;
708CREATE 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
709FROM item i
710 LEFT JOIN brand b ON i.brand_id = b.id
711 LEFT JOIN unit u ON i.unit_id = u.id ;
712
713-- Dumping structure for view uas_market.partner_transaction_v_all
714-- Removing temporary table and create final VIEW structure
715DROP TABLE IF EXISTS `partner_transaction_v_all`;
716CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `partner_transaction_v_all` AS SELECT
717 id,
718 admin_username AS admin,
719 supplier_name AS partner_name,
720 supplier_address AS partner_address,
721 'Supply' AS transaction_type,
722 (
723 SELECT sum(amount*item_price)
724 FROM supply_transaction_detail_v_all
725 WHERE supply_transaction_id = st.id
726 ) AS total_price,
727 time
728FROM supply_transaction_v_all AS st
729WHERE STATUS='submitted'
730UNION
731 SELECT
732 id,
733 cashier AS admin,
734 customer_name AS partner_name,
735 customer_address AS partner_address,
736 'Selling' AS transaction_type,
737 (
738 SELECT sum(amount*item_price)
739 FROM buy_transaction_detail_v_all
740 WHERE buy_transaction_id = bt.id
741 ) AS total_price,
742 TIME
743 FROM buy_transaction_v_all bt
744 WHERE STATUS='submitted' ;
745
746-- Dumping structure for view uas_market.supply_transaction_detail_v_all
747-- Removing temporary table and create final VIEW structure
748DROP TABLE IF EXISTS `supply_transaction_detail_v_all`;
749CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `supply_transaction_detail_v_all` AS SELECT
750 sd.*,
751 st.supplier_id,
752 st.supplier_name,
753 st.supplier_address,
754 st.user_id,
755 st.admin_username,
756 st.admin_name,
757 i.name AS item_name,
758 i.price_buy AS item_price,
759 i.unit_name AS item_unit,
760 i.brand_name AS item_brand_name,
761 i.price_buy*sd.amount as subtotal_price
762FROM supply_transaction_detail sd
763 INNER JOIN supply_transaction_v_all st
764 ON st.id = sd.supply_transaction_id
765 INNER JOIN item_v_all i
766 ON i.id = sd.item_id ;
767
768-- Dumping structure for view uas_market.supply_transaction_v_all
769-- Removing temporary table and create final VIEW structure
770DROP TABLE IF EXISTS `supply_transaction_v_all`;
771CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `supply_transaction_v_all` AS SELECT
772 st.`*`,
773 s.name AS supplier_name,
774 s.address AS supplier_address,
775 u.username AS admin_username,
776 u.name AS admin_name,
777 (
778 SELECT SUM(amount*price_buy)
779 FROM supply_transaction_detail td
780 INNER JOIN item i ON td.item_id = i.id
781 WHERE supply_transaction_id=st.id
782 ) AS total_price
783FROM supply_transaction st
784 INNER JOIN supplier s ON st.supplier_id = s.id
785 INNER JOIN user u ON st.user_id = u.id ;
786
787/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
788/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
789/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;