· 9 years ago · Jan 30, 2017, 12:34 PM
1DELIMITER $$
2DROP PROCEDURE IF EXISTS `CekKamar`$$
3CREATE DEFINER=`admin`@`localhost` PROCEDURE `CekKamar`( dMulai date, dSampai date)
4BEGIN
5 DECLARE counter INT DEFAULT 0;
6 DECLARE done INT DEFAULT 0;
7 DECLARE tTanggal DATE;
8 DECLARE cID INT;
9 DECLARE rClass CHAR(16);
10 DECLARE cur_class CURSOR FOR SELECT id,class FROM roomclass ORDER BY id;
11 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
12
13 SET @union_sql = '';
14
15 OPEN cur_class;
16 read_loop: LOOP
17 FETCH cur_class INTO cID, rClass;
18 IF done = 1 THEN
19 LEAVE read_loop;
20 ELSE
21 IF counter>0 THEN
22 SET @union_sql := CONCAT(@union_sql, ' UNION ALL ');
23 ELSE
24 SET counter = 1;
25 END IF;
26 END IF;
27 SET tTanggal := dMulai;
28 SET @sql := CONCAT('SELECT r.nomor,\'',cID,'-',rClass,'\' as class');
29 WHILE tTanggal <= dSampai DO
30 SET @sql := CONCAT(@sql, ',
31 (SELECT IF(COUNT(1)<1,\'--\',\'NOT Avail\') FROM booking
32 WHERE room_id=r.id AND \'', tTanggal, '\' BETWEEN checkin AND checkout) AS \'', tTanggal, '\'');
33 SET tTanggal := tTanggal + INTERVAL 1 DAY;
34 END WHILE;
35 SET @sql := CONCAT(@sql, '\n FROM room AS r WHERE r.class_id=',cID);
36 SET @union_sql := CONCAT(@union_sql, @sql);
37 END LOOP;
38
39 CLOSE cur_class;
40 SET @union_sql := CONCAT(@union_sql, ' ORDER BY class,nomor');
41
42 PREPARE stmt FROM @union_sql;
43 EXECUTE stmt;
44 DEALLOCATE PREPARE stmt;
45END$$
46
47DELIMITER ;
48
49DROP TABLE IF EXISTS `booking`;
50CREATE TABLE IF NOT EXISTS `booking` (
51 `id` int(11) NOT NULL AUTO_INCREMENT,
52 `booking_date` date NOT NULL,
53 `room_id` int(11) NOT NULL,
54 `checkin` date NOT NULL,
55 `checkout` date NOT NULL,
56 PRIMARY KEY (`id`),
57 KEY `room_id` (`room_id`)
58) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
59
60INSERT INTO `booking` (`id`, `booking_date`, `room_id`, `checkin`, `checkout`) VALUES
61(1, '2017-01-30', 3, '2017-02-01', '2017-01-03'),
62(2, '2017-01-30', 9, '2017-02-02', '2017-02-05'),
63(3, '2017-01-30', 4, '2017-02-02', '2017-02-02'),
64(4, '2017-01-29', 5, '2017-02-03', '2017-02-04'),
65(5, '2017-01-30', 3, '2017-02-05', '2017-02-06'),
66(6, '2017-01-29', 8, '2017-02-01', '2017-02-02');
67
68DROP TABLE IF EXISTS `room`;
69CREATE TABLE IF NOT EXISTS `room` (
70 `id` int(11) NOT NULL AUTO_INCREMENT,
71 `class_id` int(11) NOT NULL,
72 `nomor` int(11) NOT NULL,
73 PRIMARY KEY (`id`),
74 KEY `class_id` (`class_id`)
75) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
76
77INSERT INTO `room` (`id`, `class_id`, `nomor`) VALUES
78(1, 1, 201),
79(2, 2, 207),
80(3, 1, 301),
81(4, 2, 211),
82(5, 2, 303),
83(6, 2, 218),
84(7, 2, 305),
85(8, 3, 216),
86(9, 4, 202),
87(10, 3, 323);
88
89DROP TABLE IF EXISTS `roomclass`;
90CREATE TABLE IF NOT EXISTS `roomclass` (
91 `id` int(11) NOT NULL AUTO_INCREMENT,
92 `class` varchar(16) NOT NULL,
93 PRIMARY KEY (`id`)
94) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
95
96INSERT INTO `roomclass` (`id`, `class`) VALUES
97(1, 'Standar'),
98(2, 'Deluxe'),
99(3, 'Superior'),
100(4, 'VVIP');