· 9 years ago · Dec 12, 2016, 08:36 PM
1Database Schema
2
3=======================================================================================
4
5CREATE DATABASE IF NOT EXISTS `roombooking` /*!40100 DEFAULT CHARACTER SET utf8 */;
6USE `roombooking`;
7-- MySQL dump 10.13 Distrib 5.7.12, for Win64 (x86_64)
8--
9-- Host: localhost Database: roombooking
10-- ------------------------------------------------------
11-- Server version 5.7.16-log
12
13/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
14/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
15/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
16/*!40101 SET NAMES utf8 */;
17/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
18/*!40103 SET TIME_ZONE='+00:00' */;
19/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
20/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
21/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
22/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
23
24--
25-- Table structure for table `bookings`
26--
27
28DROP TABLE IF EXISTS `bookings`;
29/*!40101 SET @saved_cs_client = @@character_set_client */;
30/*!40101 SET character_set_client = utf8 */;
31CREATE TABLE `bookings` (
32 `BOOKINGID` int(11) NOT NULL AUTO_INCREMENT,
33 `ROOMID` int(11) NOT NULL,
34 `USERID` int(11) NOT NULL,
35 `RESERVEDDATE` date NOT NULL,
36 `TIMEFROM` time NOT NULL,
37 `TIMETO` time NOT NULL,
38 PRIMARY KEY (`BOOKINGID`),
39 UNIQUE KEY `INDEX_BOOKINGS_BOOKINGID` (`BOOKINGID`)
40) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
41/*!40101 SET character_set_client = @saved_cs_client */;
42
43--
44-- Dumping data for table `bookings`
45--
46
47LOCK TABLES `bookings` WRITE;
48/*!40000 ALTER TABLE `bookings` DISABLE KEYS */;
49INSERT INTO `bookings` VALUES (1,1,1,'2016-12-12','09:00:00','09:30:00'),(2,1,1,'2016-12-12','09:30:00','10:00:00');
50/*!40000 ALTER TABLE `bookings` ENABLE KEYS */;
51UNLOCK TABLES;
52
53--
54-- Table structure for table `rooms`
55--
56
57DROP TABLE IF EXISTS `rooms`;
58/*!40101 SET @saved_cs_client = @@character_set_client */;
59/*!40101 SET character_set_client = utf8 */;
60CREATE TABLE `rooms` (
61 `ROOMID` int(11) NOT NULL AUTO_INCREMENT,
62 `ROOMNUMBER` int(11) DEFAULT NULL,
63 PRIMARY KEY (`ROOMID`)
64) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
65/*!40101 SET character_set_client = @saved_cs_client */;
66
67--
68-- Dumping data for table `rooms`
69--
70
71LOCK TABLES `rooms` WRITE;
72/*!40000 ALTER TABLE `rooms` DISABLE KEYS */;
73INSERT INTO `rooms` VALUES (1,1),(2,2),(3,3);
74/*!40000 ALTER TABLE `rooms` ENABLE KEYS */;
75UNLOCK TABLES;
76
77--
78-- Table structure for table `users`
79--
80
81DROP TABLE IF EXISTS `users`;
82/*!40101 SET @saved_cs_client = @@character_set_client */;
83/*!40101 SET character_set_client = utf8 */;
84CREATE TABLE `users` (
85 `USERID` int(11) unsigned NOT NULL AUTO_INCREMENT,
86 `FIRSTNAME` varchar(50) DEFAULT NULL,
87 `LASTNAME` varchar(50) DEFAULT NULL,
88 `PASSWORD` varchar(250) DEFAULT NULL,
89 `EMAILADDRESS` varchar(250) DEFAULT NULL,
90 PRIMARY KEY (`USERID`),
91 UNIQUE KEY `USERID_UNIQUE` (`USERID`)
92) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
93/*!40101 SET character_set_client = @saved_cs_client */;
94
95--
96-- Dumping data for table `users`
97--
98
99LOCK TABLES `users` WRITE;
100/*!40000 ALTER TABLE `users` DISABLE KEYS */;
101INSERT INTO `users` VALUES (1,'M','W','password','mw@email.com');
102/*!40000 ALTER TABLE `users` ENABLE KEYS */;
103UNLOCK TABLES;
104/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
105
106/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
107/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
108/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
109/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
110/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
111/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
112/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
113
114-- Dump completed on 2016-12-12 20:31:49
115
116
117---------------------------------------------------------------------------------------
118
119Start of HTML / PHP
120
121========================================================================================
122
123<style>
124table, tr, th, td {
125 border:1px solid black;
126}
127</style>
128<?php
129//roombooking
130include 'Configs/db.config';
131include 'Classes/class.db.php';
132
133$db = new DB($config_db_host, $config_db_port, $config_db_db, $config_db_charset, $config_db_username, $config_db_password);
134$reservedDate = '2016-12-12';
135
136$sql = "SELECT B.RESERVEDDATE, B.TIMEFROM, B.TIMETO, ";
137$sql.= "R.ROOMNUMBER, ";
138$sql.= "U.FIRSTNAME, U.LASTNAME ";
139$sql.= "FROM BOOKINGS B ";
140$sql.= "INNER JOIN ROOMS R ON B.ROOMID = R.ROOMID ";
141$sql.= "INNER JOIN USERS U ON B.USERID = U.USERID ";
142$sql.= "WHERE B.RESERVEDDATE = :RESERVEDDATE ";
143$sql.= "ORDER BY B.RESERVEDDATE ASC, B.TIMEFROM ASC ";
144echo $sql.'<hr>';
145$statement = $db->conn->prepare($sql);
146$bindarray = array(":RESERVEDDATE" => $reservedDate);
147$statement->execute($bindarray);
148while($data = $statement->fetch(PDO::FETCH_ASSOC)) {
149 $bookings[$data["RESERVEDDATE"]][$data["TIMEFROM"]] = $data;
150}
151
152echo '<pre>';
153print_r($bookings);
154echo '</pre>';
155
156$gridTimeFrom = "$reservedDate 08:00";
157$gridTimeTo = "$reservedDate 20:00";
158$dtFrom = new DateTime($gridTimeFrom);
159$dtTo = new DateTime($gridTimeTo);
160$resolution = "15 minutes";
161$currentBookingStartTime = '';
162$currentBookingEndTime = '';
163
164?>
165<table>
166 <tr>
167 <th>Time</th>
168 <th colspan="3"><?php echo $reservedDate ?></th>
169 </tr>
170 <?php
171 while($dtFrom <= $dtTo) {
172 $time = $dtFrom->format('H:i:s');
173 ?>
174 <tr>
175 <td><?php echo $time;?></td>
176 <?php
177 if (isset($bookings[$reservedDate][$time])) {
178 $currentBookingEndTime = $bookings[$reservedDate][$time]["TIMETO"];
179 $currentBookingStartTime = $bookings[$reservedDate][$time]["TIMEFROM"];
180 ?>
181 <td><?php echo $bookings[$reservedDate][$time]["ROOMNUMBER"] ?></td>
182 <td><?php echo $bookings[$reservedDate][$time]["FIRSTNAME"] ?></td>
183 <td><?php echo $bookings[$reservedDate][$time]["LASTNAME"] ?></td>
184 <?php
185 }
186 else if ($currentBookingStartTime <= $time && $currentBookingEndTime > $time) {
187 ?>
188 <td colspan="3">-----</td>
189 <?php
190 }
191 else {
192 ?>
193 <td colspan=3"> </td>
194 <?php
195 }
196 $dtFrom->add(date_interval_create_from_date_string($resolution));
197 ?>
198 </tr>
199 <?php
200 }
201 ?>
202</table>