bukan_asrama.sql

DELIMITER $$
DROP PROCEDURE IF EXISTS `CekKamar`$$
CREATE DEFINER=`admin`@`localhost` PROCEDURE `CekKamar`( dMulai date, dSampai date)
BEGIN
  DECLARE counter INT DEFAULT 0;
  DECLARE done INT DEFAULT 0;
  DECLARE tTanggal DATE;
  DECLARE cID INT;
  DECLARE rClass CHAR(16);
  DECLARE cur_class CURSOR FOR SELECT id,class FROM roomclass ORDER BY id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  SET @union_sql = '';

  OPEN cur_class;
  read_loop: LOOP
    FETCH cur_class INTO cID, rClass;
    IF done = 1 THEN
      LEAVE read_loop;
    ELSE
      IF counter>0 THEN
        SET @union_sql := CONCAT(@union_sql, ' UNION ALL ');
      ELSE
        SET counter = 1;
      END IF;
    END IF;
    SET tTanggal := dMulai;
    SET @sql := CONCAT('SELECT r.nomor,\'',cID,'-',rClass,'\' as class');
    WHILE tTanggal <= dSampai DO
      SET @sql := CONCAT(@sql, ',
        (SELECT IF(COUNT(1)<1,\'--\',\'NOT Avail\') FROM booking
        WHERE room_id=r.id AND \'', tTanggal, '\' BETWEEN checkin AND checkout) AS \'', tTanggal, '\'');
      SET tTanggal := tTanggal + INTERVAL 1 DAY;
    END WHILE;
    SET @sql := CONCAT(@sql, '\n FROM room AS r WHERE r.class_id=',cID);
    SET @union_sql := CONCAT(@union_sql, @sql);
  END LOOP;

  CLOSE cur_class;
  SET @union_sql := CONCAT(@union_sql, ' ORDER BY class,nomor');

  PREPARE stmt FROM @union_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

DROP TABLE IF EXISTS `booking`;
CREATE TABLE IF NOT EXISTS `booking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `booking_date` date NOT NULL,
  `room_id` int(11) NOT NULL,
  `checkin` date NOT NULL,
  `checkout` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `room_id` (`room_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `booking` (`id`, `booking_date`, `room_id`, `checkin`, `checkout`) VALUES
(1, '2017-01-30', 3, '2017-02-01', '2017-01-03'),
(2, '2017-01-30', 9, '2017-02-02', '2017-02-05'),
(3, '2017-01-30', 4, '2017-02-02', '2017-02-02'),
(4, '2017-01-29', 5, '2017-02-03', '2017-02-04'),
(5, '2017-01-30', 3, '2017-02-05', '2017-02-06'),
(6, '2017-01-29', 8, '2017-02-01', '2017-02-02');

DROP TABLE IF EXISTS `room`;
CREATE TABLE IF NOT EXISTS `room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_id` int(11) NOT NULL,
  `nomor` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `class_id` (`class_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

INSERT INTO `room` (`id`, `class_id`, `nomor`) VALUES
(1, 1, 201),
(2, 2, 207),
(3, 1, 301),
(4, 2, 211),
(5, 2, 303),
(6, 2, 218),
(7, 2, 305),
(8, 3, 216),
(9, 4, 202),
(10, 3, 323);

DROP TABLE IF EXISTS `roomclass`;
CREATE TABLE IF NOT EXISTS `roomclass` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `roomclass` (`id`, `class`) VALUES
(1, 'Standar'),
(2, 'Deluxe'),
(3, 'Superior'),
(4, 'VVIP');