Features

This tutorial shows how you can implement your own hotel room booking system using DayPilot Pro JavaScript Scheduler component. It has a modern HTML5 frontend which communicates with the server using a REST API implemented in PHP.

  • Use drag and drop to create, move and update reservations
  • Delete reservations using a built-in icon
  • Edit reservation details using a modal dialog
  • Overlapping reservations are blocked to prevent overbooking
  • Each hotel room has a status (Ready, Cleanup, Dirty) which is highlighted using a color code
  • The receptionists can filter the hotel room by capacity
  • Each reservation has a status (New, Confirmed, Arrived, CheckedOut, Expired), highlighted using a duration bar color
  • The server-side REST API is implemented in PHP (source code included) that uses MySQL to store the dta
  • The front desk user interface is built using HTML5 Scheduler that provides a visual overview of all reservations
  • The project includes a trial version of DayPilot Pro for JavaScript

License

Licensed for testing and evaluation purposes. Please see the license agreement included in the sample project. You can use the source code of the tutorial if you are a licensed user of DayPilot Pro for JavaScript. Buy a license.

Scheduler UI Builder

javascript-scheduler-configurator-ui-builder.png

NEW: You can use Scheduler UI Builder online app to create a quick prototype of your new scheduling project. Configure the Scheduler UI in minutes and generate a downloadable HTML5/JavaScript project. See also a tutorial on using the scheduler configurator.

JavaScript Scheduler Initialization

html5-hotel-room-booking-javascript-php-mysql-initialization.png

We will start the front-end implementation by create a new JavaScript Scheduler instance. This is the web component that will display the reservation data for all rooms. At first, we will create an empty Scheduler without any data:

HTML5/JavaScript

<div id="dp"></div>

<script>
  var dp = new DayPilot.Scheduler("dp");
  dp.init();
</script>

The default configuration displays an empty timeline with the default scale (one cell per hour) and no resources (rooms) defined.

Adjusting Scheduler Scale and Time Headers

html5-hotel-room-booking-javascript-php-mysql-scale-time-headers.png

Hotel reservations work with days as the basic time units. We need to switch the Scheduler scale to "Days" and display months and days in the time header rows.

<div id="dp"></div>
      
<script type="text/javascript">

  var dp = new DayPilot.Scheduler("dp");
  dp.startDate = DayPilot.Date.today().firstDayOfMonth();
  dp.days = DayPilot.Date.today().daysInMonth();
  dp.scale = "Day";
  dp.timeHeaders = [
      { groupBy: "Month", format: "MMMM yyyy" },
      { groupBy: "Day", format: "d" }
  ];
  dp.init();

</script>

Loading Hotel Rooms

html5-hotel-room-booking-javascript-php-mysql-loading-rooms.png

We want to display the hotel rooms as Scheduler resources on the vertical (Y) axis. The room data is stored in the MySQL database and we will need to load them from the server using a REST API. We can use jQuery $.post() to load the room data using an HTTP call.

JavaScript

function loadResources() {
  $.post("backend_rooms.php", 
  function(data) {
      dp.resources = data;
      dp.update();
  });
}

The Scheduler also provides a shortcut rows.load() method that lets us simplify the room loading function:

function loadResources() {
  dp.rows.load("backend_rooms.php");
}

The server-side backend_rooms.php script is a simple JSON endpoint that returns the hotel room data in the following format:

[
  {"id":"1","name":"Room 1","capacity":"2","status":"Dirty"},
  {"id":"2","name":"Room 2","capacity":"2","status":"Cleanup"},
  {"id":"3","name":"Room 3","capacity":"2","status":"Ready"},
  {"id":"4","name":"Room 4","capacity":"4","status":"Ready"},
  {"id":"5","name":"Room 5","capacity":"1","status":"Ready"}
]

The PHP script loads the rooms from MySQL database and returns a JSON array. In addition to the required properties (id and name), the response also includes additional room data (status and capacity) which we will use later to customize the front desk view.

backend_rooms.php

<?php
require_once '_db.php';

$stmt = $db->prepare("SELECT * FROM rooms ORDER BY name");
$stmt->execute();
$rooms = $stmt->fetchAll();

class Room {}

$result = array();

foreach($rooms as $room) {
  $r = new Room();
  $r->id = $room['id'];
  $r->name = $room['name'];
  $r->capacity = $room['capacity'];
  $r->status = $room['status'];
  $result[] = $r;
  
}

header('Content-Type: application/json');
echo json_encode($result);

?>

Hotel Room Capacity and Status

html5-hotel-room-booking-javascript-php-mysql-columns-capacity.png

Our application stores additional room information in the database (capacity, status) and we would like to display them in the reservation overview in additional row header columns. The columns can be defined using rowHeaderColumns property.

JavaScript

dp.rowHeaderColumns = [
  {title: "Room", width: 80},
  {title: "Capacity", width: 80},
  {title: "Status", width: 80}
];

There is a special rendering event (onBeforeResHeaderRender) that will let us customize the row headers. We will create a new event handler to map the room data to columns.

JavaScript

dp.onBeforeResHeaderRender = function(args) {
  var beds = function(count) {
      return count + " bed" + (count > 1 ? "s" : "");
  };
  
  args.resource.columns[0].html = beds(args.resource.capacity);
  args.resource.columns[1].html = args.resource.status;
  switch (args.resource.status) {
      case "Dirty":
          args.resource.cssClass = "status_dirty";
          break;
      case "Cleanup":
          args.resource.cssClass = "status_cleanup";
          break;
  }
};

We have added custom CSS classes to the room headers depending on the room status ("status_dirty", "status_cleanup").

Our Scheduler component uses the built-in CSS theme ("scheduler_default"). We can extend the theme by overriding specified classes. This will let us modify the row appearance depending on the room status.

<style type="text/css">
  .scheduler_default_rowheader_inner
  {
      border-right: 1px solid #ccc;
  }
  .scheduler_default_rowheader.scheduler_default_rowheadercol2
  {
      background: #fff;
  }
  .scheduler_default_rowheadercol2 .scheduler_default_rowheader_inner
  {
      top: 2px;
      bottom: 2px;
      left: 2px;
      background-color: transparent;
      border-left: 5px solid #1a9d13; /* green */
      border-right: 0px none;
  }
  .status_dirty.scheduler_default_rowheadercol2 .scheduler_default_rowheader_inner
  {
      border-left: 5px solid #ea3624; /* red */
  }
  .status_cleanup.scheduler_default_rowheadercol2 .scheduler_default_rowheader_inner
  {
      border-left: 5px solid #f9ba25; /* orange */
  }
</style>

Loading Reservations from MySQL Database

html5-hotel-room-booking-javascript-php-mysql-loading-reservations.png

Now we want to load the reservations (events) from MySQL database. Again, we will use a call to the REST API and update the view as soon as the response arrives.

JavaScript

function loadEvents() {
  var start = dp.visibleStart();
  var end = dp.visibleEnd();

  $.post("backend_events.php", 
      {
          start: start.toString(),
          end: end.toString()
      },
      function(data) {
          dp.events.list = data;
          dp.update();
      }
  );
}

We can also replace the jQuery ajax call with the built-in events.load() method that loads the events from the specified URL (the start and end query string parameters with the visible start and end dates will be added automatically):

dp.events.load("backend_events.php");

The server-side backend_event.php script returns a JSON array with reservation details. Sample JSON output:

[
  {
    "id": "2",
    "text": "Mr. García",
    "start": "2019-09-06T12:00:00",
    "end": "2019-09-11T12:00:00",
    "resource": "2",
    "bubbleHtml": "Reservation details: <br/>Mr. García",
    "status": "Confirmed",
    "paid": "0"
  },
  {
    "id": "3",
    "text": "Mrs. Jones",
    "start": "2019-09-05T12:00:00",
    "end": "2019-09-09T12:00:00",
    "resource": "4",
    "bubbleHtml": "Reservation details: <br/>Mrs. Jones",
    "status": "New",
    "paid": "0"
  },
  {
    "id": "4",
    "text": "Mr. Gray",
    "start": "2019-09-02T12:00:00",
    "end": "2019-09-06T12:00:00",
    "resource": "3",
    "bubbleHtml": "Reservation details: <br/>Mr. Gray",
    "status": "CheckedOut",
    "paid": "100"
  },
  {
    "id": "5",
    "text": "Mrs. Schwartz",
    "start": "2019-09-07T12:00:00",
    "end": "2019-09-12T12:00:00",
    "resource": "3",
    "bubbleHtml": "Reservation details: <br/>Mrs. Schwartz",
    "status": "New",
    "paid": "0"
  },
  {
    "id": "6",
    "text": "Mr. Laurent",
    "start": "2019-09-04T12:00:00",
    "end": "2019-09-09T12:00:00",
    "resource": "1",
    "bubbleHtml": "Reservation details: <br/>Mr. Laurent",
    "status": "Arrived",
    "paid": "50"
  }
]

The PHP backend endpoint uses the start and end query string parameters to filter the reservation records.

backend_events.php

<?php
require_once '_db.php';

$start = isset($_POST['start']) ? $_POST['start'] : $_GET['start'];
$end = isset($_POST['end']) ? $_POST['end'] : $_GET['end'];

$stmt = $db->prepare("SELECT * FROM reservations WHERE NOT ((end <= :start) OR (start >= :end))");
$stmt->bindParam(':start', $start);
$stmt->bindParam(':end', $end);
$stmt->execute();
$result = $stmt->fetchAll();

class Event {}
$events = array();

date_default_timezone_set("UTC");
$now = new DateTime("now");
$today = $now->setTime(0, 0, 0);

foreach($result as $row) {
    $e = new Event();
    $e->id = $row['id'];
    $e->text = $row['name'];
    $e->start = $row['start'];
    $e->end = $row['end'];
    $e->resource = $row['room_id'];
    $e->bubbleHtml = "Reservation details: <br/>".$e->text;
    
    // additional properties
    $e->status = $row['status'];
    $e->paid = $row['paid'];
    $events[] = $e;

}

header('Content-Type: application/json');
echo json_encode($events);

?>

You can see that we are loading a couple of additional reservation parameters (status, paid) so we can display them in the front desk view.

Displaying Room Reservation Status

html5-hotel-room-booking-javascript-php-mysql-reservation-status.png

The reservation box appearance can be customized using onBeforeEventRender event handler. We will set custom duration bar color and text, depending on the reservation status.

dp.onBeforeEventRender = function(args) {
  var start = new DayPilot.Date(args.e.start);
  var end = new DayPilot.Date(args.e.end);

  var today = DayPilot.Date.today();
  var now = new DayPilot.Date();

  args.e.html = args.e.text + " (" + start.toString("M/d/yyyy") + " - " + end.toString("M/d/yyyy") + ")";

  switch (args.e.status) {
      case "New":
          var in2days = today.addDays(1);

          if (start < in2days) {
              args.e.barColor = 'red';
              args.e.toolTip = 'Expired (not confirmed in time)';
          }
          else {
              args.e.barColor = 'orange';
              args.e.toolTip = 'New';
          }
          break;
      case "Confirmed":
          var arrivalDeadline = today.addHours(18);

          if (start < today || (start.getDatePart() === today.getDatePart() && now > arrivalDeadline)) { // must arrive before 6 pm
              args.e.barColor = "#f41616";  // red
              args.e.toolTip = 'Late arrival';
          }
          else {
              args.e.barColor = "green";
              args.e.toolTip = "Confirmed";
          }
          break;
      case 'Arrived': // arrived
          var checkoutDeadline = today.addHours(10);

          if (end < today || (end.getDatePart() === today.getDatePart() && now > checkoutDeadline)) { // must checkout before 10 am
              args.e.barColor = "#f41616";  // red
              args.e.toolTip = "Late checkout";
          }
          else
          {
              args.e.barColor = "#1691f4";  // blue
              args.e.toolTip = "Arrived";
          }
          break;
      case 'CheckedOut': // checked out
          args.e.barColor = "gray";
          args.e.toolTip = "Checked out";
          break;
      default:
          args.e.toolTip = "Unexpected state";
          break;
  }

  args.e.html = "<div>" + args.e.html + "<br /><span style='color:gray'>" + args.e.toolTip + "</span></div>";

  var paid = args.e.paid;
  var paidColor = "#aaaaaa";

  args.e.areas = [
      { bottom: 10, right: 4, html: "<div style='color:" + paidColor + "; font-size: 8pt;'>Paid: " + paid + "%</div>", v: "Visible"},
      { left: 4, bottom: 8, right: 4, height: 2, html: "<div style='background-color:" + paidColor + "; height: 100%; width:" + paid + "%'></div>", v: "Visible" }
  ];

};

Filtering Hotel Rooms by Capacity

html5-hotel-room-booking-javascript-php-mysql-room-filter.png

Our room booking application will support filtering the hotel rooms by capacity. We can implement the room filter using a simple drop-down list.

For a detailed tutorial on more advanced room filtering, please see JavaScript/HTML5 Scheduler: Filtering Rooms by Availability. It explains how to implement multi-parameter filters and find rooms of a specified size that are available for a given date

HTML5/JavaScript

Room filter:
<select id="filter">
    <option value="0">All</option>
    <option value="1">Single</option>
    <option value="2">Double</option>
    <option value="4">Family</option>
</select>

<script>
  $(document).ready(function() {
    $("#filter").change(function() {
        loadResources();
    });
  });
</script>

html5-hotel-room-booking-javascript-php-mysql-room-filter-single.png

The filtering logic is implemented on the server side, in the backend_rooms.php script. We need to extend loadResources() method and backend_rooms.php to support the room capacity filter.

The selected filter value is added to the request URL as capacity query string parameter.

JavaScript

function loadResources() {
  $.post("backend_rooms.php", 
  { capacity: $("#filter").val() },
  function(data) {
      dp.resources = data;
      dp.update();
  });
}

backend_rooms.php

<?php
require_once '_db.php';

$capacity = isset($_POST['capacity']) ? $_POST['capacity'] : '0';

$stmt = $db->prepare("SELECT * FROM rooms WHERE capacity = :capacity OR :capacity = '0' ORDER BY name");
$stmt->bindParam(':capacity', $capacity); 
$stmt->execute();
$rooms = $stmt->fetchAll();

class Room {}

$result = array();

foreach($rooms as $room) {
  $r = new Room();
  $r->id = $room['id'];
  $r->name = $room['name'];
  $r->capacity = $room['capacity'];
  $r->status = $room['status'];
  $result[] = $r;
  
}

header('Content-Type: application/json');
echo json_encode($result);

?>

Creating Room Reservations using Drag and Drop

html5-hotel-room-booking-javascript-php-mysql-create-reservation.png

The front desk workers should be able to create a new room reservation using drag and drop. Drag and drop time range selecting is already enabled, we just need to add the event handler.

JavaScript

dp.onTimeRangeSelected = function (args) {
  //var name = prompt("New event name:", "Event");
  //if (!name) return;

  var modal = new DayPilot.Modal();
  modal.closed = function() {
      dp.clearSelection();
      
      // reload all events
      var data = this.result;
      if (data && data.result === "OK") {
          loadEvents();
      }
  };
  modal.showUrl("reservation_new.php?start=" + args.start + "&end=" + args.end + "&resource=" + args.resource);
  
};

The event handler opens a new page (reservation_new.php) in a modal dialog created using DayPilot.Modal tool.

html5-hotel-room-booking-javascript-php-mysql-new-reservation-modal.png

new.php

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>New Reservation</title>
    	<link type="text/css" rel="stylesheet" href="media/layout.css" />    
        <script src="js/jquery/jquery-1.9.1.min.js" type="text/javascript"></script>
    </head>
    <body>
        <?php
            // check the input
            //is_numeric($_GET['id']) or die("invalid URL");
            
            require_once '_db.php';
            
            $rooms = $db->query('SELECT * FROM rooms');
            
            $start = $_GET['start']; // TODO parse and format
            $end = $_GET['end']; // TODO parse and format
        ?>
        <form id="f" action="backend_create.php" style="padding:20px;">
            <h1>New Reservation</h1>
            <div>Name: </div>
            <div><input type="text" id="name" name="name" value="" /></div>
            <div>Start:</div>
            <div><input type="text" id="start" name="start" value="<?php echo $start ?>" /></div>
            <div>End:</div>
            <div><input type="text" id="end" name="end" value="<?php echo $end ?>" /></div>
            <div>Room:</div>
            <div>
                <select id="room" name="room">
                    <?php 
                        foreach ($rooms as $room) {
                            $selected = $_GET['resource'] == $room['id'] ? ' selected="selected"' : '';
                            $id = $room['id'];
                            $name = $room['name'];
                            print "<option value='$id' $selected>$name</option>";
                        }
                    ?>
                </select>
                
            </div>
            <div class="space"><input type="submit" value="Save" /> <a href="javascript:close();">Cancel</a></div>
        </form>
        
        <script type="text/javascript">
        function close(result) {
            if (parent && parent.DayPilot && parent.DayPilot.ModalStatic) {
                parent.DayPilot.ModalStatic.close(result);
            }
        }

        $("#f").submit(function () {
            var f = $("#f");
            $.post(f.attr("action"), f.serialize(), function (result) {
                close(eval(result));
            });
            return false;
        });

        $(document).ready(function () {
            $("#name").focus();
        });
    
        </script>
    </body>
</html>

This page contains a simple HTML form. We need to close the modal dialog on the submission so we intercept it using jQuery. We will submit it using a special AJAX call ($.post()) and close the modal dialog using parent.DayPilot.ModalStatic.close().

The backend_create.php PHP script is the REST API endpoint for creating a new reservation in the database.

backend_create.php

<?php
require_once '_db.php';

$stmt = $db->prepare("INSERT INTO reservations (name, start, end, room_id, status, paid) VALUES (:name, :start, :end, :room, 'New', 0)");
$stmt->bindParam(':start', $_POST['start']);
$stmt->bindParam(':end', $_POST['end']);
$stmt->bindParam(':name', $_POST['name']);
$stmt->bindParam(':room', $_POST['room']);
$stmt->execute();

class Result {}

$response = new Result();
$response->result = 'OK';
$response->message = 'Created with id: '.$db->lastInsertId();
$response->id = $db->lastInsertId();

header('Content-Type: application/json');
echo json_encode($response);

?>

Editing Room Reservation Details

html5-hotel-room-booking-javascript-php-mysql-edit-reservation.png

In order to let the receptionists edit the reservation details we add a new event click handler that opens a modal dialog with reservation information.

dp.onEventClick = function(args) {
  var modal = new DayPilot.Modal();
  modal.closed = function() {
      // reload all events
      var data = this.result;
      if (data && data.result === "OK") {
          loadEvents();
      }
  };
  modal.showUrl("reservation_edit.php?id=" + args.e.id());
};

reservation_edit.php

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Edit Event</title>
    	<link type="text/css" rel="stylesheet" href="media/layout.css" />    
        <script src="js/jquery/jquery-1.9.1.min.js" type="text/javascript"></script>
    </head>
    <body>
        <?php
            // check the input
            is_numeric($_GET['id']) or die("invalid URL");
            
            require_once '_db.php';
            
            $stmt = $db->prepare('SELECT * FROM reservations WHERE id = :id');
            $stmt->bindParam(':id', $_GET['id']);
            $stmt->execute();
            $reservation = $stmt->fetch();
            
            $rooms = $db->query('SELECT * FROM rooms');
        ?>
        <form id="f" action="backend_update.php" style="padding:20px;">
            <input type="hidden" name="id" value="<?php print $_GET['id'] ?>" />
            <h1>Edit Reservation</h1>
            
            <div class="space">
                <div>Start:</div>
                <div><input type="text" id="start" name="start" value="<?php print $reservation['start'] ?>" /></div>
            </div>
            
            <div class="space">
                <div>End:</div>
                <div><input type="text" id="end" name="end" value="<?php print $reservation['end'] ?>" /></div>
            </div>
            
            <div class="space">
                <div>Room:</div>
                <div>
                    <select id="room" name="room">
                        <?php 
                            foreach ($rooms as $room) {
                                $selected = $reservation['room_id'] == $room['id'] ? ' selected="selected"' : '';
                                $id = $room['id'];
                                $name = $room['name'];
                                print "<option value='$id' $selected>$name</option>";
                            }
                        ?>
                    </select>
                </div>
            </div>
            
            <div class="space">
                <div>Name: </div>
                <div><input type="text" id="name" name="name" value="<?php print $reservation['name'] ?>" /></div>
            </div>
            
            <div class="space">
                <div>Status:</div>
                <div>
                    <select id="status" name="status">
                        <?php 
                            $options = array("New", "Confirmed", "Arrived", "CheckedOut");
                            foreach ($options as $option) {
                                $selected = $option == $reservation['status'] ? ' selected="selected"' : '';
                                $id = $option;
                                $name = $option;
                                print "<option value='$id' $selected>$name</option>";
                            }
                        ?>
                    </select>                
                </div>
            </div>
            
            <div class="space">
                <div>Paid:</div>
                <div>
                    <select id="paid" name="paid">
                        <?php 
                            $options = array(0, 50, 100);
                            foreach ($options as $option) {
                                $selected = $option == $reservation['paid'] ? ' selected="selected"' : '';
                                $id = $option;
                                $name = $option."%";
                                print "<option value='$id' $selected>$name</option>";
                            }
                        ?>
                    </select>
                </div>
            </div>
            
            <div class="space">
                <input type="submit" value="Save" /> <a href="javascript:close();">Cancel</a>
            </div>
        </form>
        
        <script type="text/javascript">
        function close(result) {
            if (parent && parent.DayPilot && parent.DayPilot.ModalStatic) {
                parent.DayPilot.ModalStatic.close(result);
            }
        }

        $("#f").submit(function () {
            var f = $("#f");
            $.post(f.attr("action"), f.serialize(), function (result) {
                close(eval(result));
            });
            return false;
        });

        $(document).ready(function () {
            $("#name").focus();
        });
    
        </script>
    </body>
</html>

The modal dialog logic is the same as in case of creating new reservations. We intercept the form submission and save the data using a special AJAX call to backend_update.php.

backend_update.php

<?php
require_once '_db.php';

$stmt = $db->prepare("UPDATE reservations SET name = :name, start = :start, end = :end, room_id = :room, status = :status, paid = :paid WHERE id = :id");
$stmt->bindParam(':id', $_POST['id']);
$stmt->bindParam(':name', $_POST['name']);
$stmt->bindParam(':start', $_POST['start']);
$stmt->bindParam(':end', $_POST['end']);
$stmt->bindParam(':room', $_POST['room']);
$stmt->bindParam(':status', $_POST['status']);
$stmt->bindParam(':paid', $_POST['paid']);
$stmt->execute();

class Result {}

$response = new Result();
$response->result = 'OK';
$response->message = 'Update successful';

header('Content-Type: application/json');
echo json_encode($response);

?>

Moving Reservations using Drag and Drop

html5-hotel-room-booking-javascript-php-mysql-moving-reservation-drag-drop.png

The reservations can be moved using drag and drop. Whenever the reservation is dropped at a new location, onEventMoved handler is fired.

JavaScript

dp.onEventMoved = function (args) {
  $.post("backend_move.php", 
  {
      id: args.e.id(),
      newStart: args.newStart.toString(),
      newEnd: args.newEnd.toString(),
      newResource: args.newResource
  }, 
  function(data) {
      dp.message(data.message);
  });
};

We will call backend_move.php REST API endpoint to save the new reservation date in the MySQL database.

backend_move.php

<?php
require_once '_db.php';

class Result {}

$stmt = $db->prepare("SELECT * FROM reservations WHERE NOT ((end <= :start) OR (start >= :end)) AND id <> :id AND room_id = :resource");
$stmt->bindParam(':start', $_POST['newStart']);
$stmt->bindParam(':end', $_POST['newEnd']);
$stmt->bindParam(':id', $_POST['id']);
$stmt->bindParam(':resource', $_POST['newResource']);
$stmt->execute();
$overlaps = $stmt->rowCount() > 0;

if ($overlaps) {
    $response = new Result();
    $response->result = 'Error';
    $response->message = 'This reservation overlaps with an existing reservation.';

    header('Content-Type: application/json');
    echo json_encode($response);
    exit;
}

$stmt = $db->prepare("UPDATE reservations SET start = :start, end = :end, room_id = :resource WHERE id = :id");
$stmt->bindParam(':id', $_POST['id']);
$stmt->bindParam(':start', $_POST['newStart']);
$stmt->bindParam(':end', $_POST['newEnd']);
$stmt->bindParam(':resource', $_POST['newResource']);
$stmt->execute();


$response = new Result();
$response->result = 'OK';
$response->message = 'Update successful';

header('Content-Type: application/json');
echo json_encode($response);

?>

Preventing Reservation Overlaps

html5-hotel-room-booking-javascript-php-mysql-reservation-overlaps.png

As we don't want to allow overlapping reservations (double booking of a room) we will disable event overlapping.

dp.allowEventOverlap = false;

This will prevent moving or resizing a reservation over existing reservations and selecting a date that has been already booked.

Deleting Room Reservations

html5-hotel-room-booking-javascript-php-mysql-deleting-reservations.png

The reservations can be deleted using an icon in the upper-right event box. 

We need to enabled this feature using eventDeleteHandling property and add an onEventDeleted handler.

JavaScript

dp.eventDeleteHandling = "Update";

dp.onEventDeleted = function(args) {
  $.post("backend_delete.php", 
  {
      id: args.e.id()
  }, 
  function() {
      dp.message("Deleted.");
  });
};

The event handler will call backend_delete.php to save the changes in the database.

backend_delete.php

<?php
require_once '_db.php';

$stmt = $db->prepare("DELETE FROM reservations WHERE id = :id");
$stmt->bindParam(':id', $_POST['id']);
$stmt->execute();

class Result {}

$response = new Result();
$response->result = 'OK';
$response->message = 'Delete successful';

header('Content-Type: application/json');
echo json_encode($response);

?>

Changing the Date

html5-hotel-room-booking-javascript-php-mysql-changing-date.png

We will let the front end clerks change the visible date by selecting it using a Navigator control.

HTML5

<div id="nav"></div>

JavaScript

<script type="text/javascript">
  var nav = new DayPilot.Navigator("nav");
  nav.selectMode = "Month";
  nav.showMonths = 3;
  nav.skipMonths = 3;
  nav.onTimeRangeSelected = function(args) {
      loadTimeline(args.start);
      loadEvents();
  };
  nav.init();
</script>

html5-hotel-room-booking-javascript-php-mysql-date-range.png

The receptionists will also be able to change the visible date range using a drop-down list.

HTML5

Time range: 
<select id="timerange">
    <option value="week">Week</option>
    <option value="month" selected>Month</option>
</select>

JavaScript

$("#timerange").change(function() {
  switch (this.value) {
      case "week":
          dp.days = 7;
          nav.selectMode = "Week";
          nav.select(nav.selectionDay);
          break;
      case "month":
          dp.days = dp.startDate.daysInMonth();
          nav.selectMode = "Month";
          nav.select(nav.selectionDay);
          break;
  }
  loadTimeline(DayPilot.Date.today());
  loadEvents();
});

Hotel Room Check-In and Check-Out Time

html5-hotel-room-booking-javascript-php-mysql-standard-check-in.png

Normally, the Scheduler generates the timeline automatically from startDate and days properties. The day cells in the standard timeline always start at 00:00 and end and 24:00.

html5-hotel-room-booking-javascript-php-mysql-custom-check-in.png

Hotel reservation systems need to work custom check-in and check-out times which are usually around noon. In order to display the check-in/check-out deadlines we will create the Scheduler timeline manually and create the timeline cells one-by-one. The check-in and the check-out times are set to 12:00 (noon).

function loadTimeline(date) {
  dp.scale = "Manual";
  dp.timeline = [];
  var start = date.getDatePart().addHours(12);
  
  for (var i = 0; i < dp.days; i++) {
      dp.timeline.push({start: start.addDays(i), end: start.addDays(i+1)});
  }
  dp.update();
}

It is necessary to use a time header with groupBy: "Day" modifier. The default value (groupBy: "Cell") would align the day header with the grid cells.

dp.timeHeaders = [
  { groupBy: "Month", format: "MMMM yyyy" },
  { groupBy: "Day", format: "d" }
];

Database (MySQL, SQLite)

The sample project includes a PDO implementation of the database access for two databases:

  • MySQL (_db_mysql.php)
  • SQLite (_db_sqlite.php)

The default database engine (_db.php) is SQLite:

<?php

// use sqlite
require_once '_db_sqlite.php';

// use MySQL
//require_once '_db_mysql.php';

You can switch to MySQL by changing _db.php as follows:

 <?php

// use sqlite
// require_once '_db_sqlite.php';

// use MySQL
require_once '_db_mysql.php';

Don't forget to adjust the database connection properties (server, database name, username, password, port) in _db_mysql.php.

The MySQL script will create rooms and reservations tables in the target database (if they don't exist already).

MySQL Database Schema

CREATE TABLE IF NOT EXISTS rooms (
  id INTEGER PRIMARY KEY AUTO_INCREMENT, 
  name TEXT, 
  capacity INTEGER,
  status VARCHAR(30));

CREATE TABLE IF NOT EXISTS reservations (
  id INTEGER PRIMARY KEY AUTO_INCREMENT, 
  name TEXT, 
  start DATETIME, 
  end DATETIME,
  room_id INTEGER,
  status VARCHAR(30),
  paid INTEGER);

History

  • October 21, 2019: Minor fixes. Inline code snippets highlighted.
  • September 6, 2019: DayPilot Pro version updated (2019.3.3997). Updated look. New screenshots.
  • May 22, 2019: DayPilot Pro version updated (2019.2.3851). Event HTML updated.
  • February 17, 2018: DayPilot Pro version updated (2018.1.3169). _db.php using requireonce to select DB backend (SQLite/MySQL)
  • January 26, 2017:  DayPilot Pro version updated (8.2.2661).
  • June 8, 2016: onBeforeEventRender fixed (missing "now" declaration). Updated with the latest DayPilot Pro version (8.2.2200).
  • December 8, 2015: MySQL DB schema fixed (auto incrementing room id).
  • November 16, 2015: DayPilot bug fixed (moving existing events over today).
  • November 12, 2015: Navigator added, room creating and editing.
  • October 1, 2015: Incorrect start and end dates in loadEvents() fixed.
  • September 30, 2014: Initial release, based on Hotel Room Booking Tutorial (ASP.NET, C#, VB.NET, SQL Server)