Project Specification
Page 1
HOTEL MANAGEMENT SYSTEM Introduction The Hotel Management System (HMS) project will automate the manual and legacy operations of M/S CHEZ-LAURENT HOTELS & RESORTS INTERNATIONAL. The project will keep track of guest database, room reservations, cancellations, check-ins, check-outs, room service, weekly shift scheduling of employees, finance and reports. Data Flow Diagram
User
Logon
Rooms
Book / Cancel
Bookings / Availability
Page 1
Guests
Query / Occupy
Bookings
Employees
Attend
Assignments
SRM Systems & Software P Ltd.
Project Specification
Page 2
Database Schema
Table: GUESTTYPES Column Name Id Name Remarks
Data Type VARCHAR(8) VARCHAR(50) VARCHAR(255)
Description Unique Identification for every category of guests Whether Corporate Client or Friend or Regular Guest or Walk-In Guest or Staff Member Extra information you may want to store about this category of guests Table: GUESTS
Column Name Id Title FirstName Page 2
Data Type VARCHAR(8) VARCHAR(5) VARCHAR(50)
Description Unique Identification for every guest Whether Dr. or Mr. or Miss or Mr. and Mrs. Or Mrs. Or Ms. or Rev. or Sir or Sister First name of the guest SRM Systems & Software P Ltd.
Project Specification LastName Address1 Address2 Address3 Pin Phone Email Mobile CarRegNo CompName Nationality NumChildren TotalChildrenAtHom e GuestType Remarks CreateDate CreatedBy
Page 3 VARCHAR(50) VARCHAR(50) VARCHAR(50) VARCHAR(50) VARCHAR(20) VARCHAR(20) VARCHAR(50) VARCHAR(20) VARCHAR(20) VARCHAR(50) VARCHAR(20) INTEGER INTEGER
Last name of the guest Street/House Number of the guest’s residence Sector/Colony of the guest’s residence Area/Locality of the guest’s residence PIN Code of the guest’s residence Phone number of the guest’s residence Email id Mobile phone number Registration Number of the guest’s car Company Name where the Guest is working Nationality of the guest Number of Children who are going to stay in this hotel now Total number of children the guest has
VARCHAR(8) VARCHAR(255 ) DATETIME VARCHAR(50)
Refers ID column of GUESTTYPES table Any extra information you want to store about this guest Date on which this information was fed into the database. Default is today’s date Login Id of the clerk who entered this information about the guest Table: CONSUMABLES
Column Name Id Name Remarks UOM PhotoPath ApplyTax PricePerUnit
Data Type VARCHAR(8) VARCHAR(50) VARCHAR(255 ) VARCHAR(25) VARCHAR(255 ) BIT NUMERIC(9,2)
Description Unique Identification for every consumable (Food Or Beverage) Name of the dish/drink viz. Idly-Sambar, Bread-Omelets, Chicken Tikka, Malt Whisky, etc. Any extra information you want to store about this dish/drink Unit of Measurement of this dish/drink viz. ml, slices, numbers, etc. Full path of the picture file name, for example C:\HMS\Images\MOQVQM01.GIF Whether to apply Sales Tax for this dish or not Price Per Dish Table: ROOMTYPES
Column Name Id Name Remarks
Data Type VARCHAR(8) VARCHAR(50) VARCHAR(255 )
Description Unique Identification for Room-Type Name of the Room-Type viz. Single or Double or A/C or Non-A/C Comments on the Room-Type
Column Name Id Name
Data Type VARCHAR(8) VARCHAR(50)
Description Unique Identification for Tariffs Bed-n-Breakfast, Family-Winter-Special, Room-Only-per-Night, Winter-Special, Zero-Rate,
TariffAmount
NUMERIC(9,2)
Amount of Tariff
Column Name RoomNo Description
Data Type VARCHAR(8) VARCHAR(255 ) VARCHAR(5) VARCHAR(10) INTEGER VARCHAR(8) CHAR(1)
Table: TARIFFS
etc.
Table: ROOMS
FloorNo RoomTypeId Extension TariffCode Status
Description Unique identification for every Room Description for the Room Floor Number viz. I, II, III, etc Refers ID column of ROOMTYPES table Intercom Extension number for the room Refers ID column of TARIFFS table Whether Provisional (P), Confirmed (C), Check-In (I), Checked-Out (O) or Cancelled (L) Table: DEPARTMENTS
Column Name Id Name
Data Type VARCHAR(8) VARCHAR(50)
Remarks
VARCHAR(255 )
Column Name Id FirstName LastName
Data Type VARCHAR(8) VARCHAR(50) VARCHAR(50)
Description Unique identification for every Department Name of the Department viz. House-Keeping, Banquets, Room Service, Restaurant, Bar, etc. Comments on the Department Table: EMPLOYEES
Page 3
Description Unique identification for every Employee First name of the Employee Last name of the Employee SRM Systems & Software P Ltd.
Project Specification
Page 4
Sex DeptNo Address1 Address2 Address3 Phone
VARCHAR(10) VARCHAR(8) VARCHAR(50) VARCHAR(50) VARCHAR(50) VARCHAR(25)
Column Name Id Name TariffId
Data Type VARCHAR(8) VARCHAR(50) VARCHAR(8)
Column Name Id Name Remarks
Data Type VARCHAR(8) VARCHAR(50) VARCHAR(255 )
Column Name BookingCode GuestNo RoomNo BookingType BookingSource ArrivalDate Deposit
Data Type VARCHAR(8) VARCHAR(8) VARCHAR(8) VARCHAR(8) VARCHAR(8) DATETIME NUMERIC(9,2)
Column Name BookingCode CheckOutDate Outstanding
Data Type VARCHAR(8) DATETIME NUMERIC(9,2)
Column Name BillNo ConsumableNo RoomNo BillDate
Data Type VARCHAR(8) VARCHAR(8) VARCHAR(8) DATETIME
Column Name BookingCode RoomNo BookieName BookDate GuestName NumberOfAdults NumberOfChildren CheckInDate
Data Type VARCHAR(8) VARCHAR(8) VARCHAR(50) DATETIME VARCHAR(50) INTEGER INTEGER DATETIME
Gender of employee whether M or F Department id to which the employee belongs. Refers ID column of DEPARTMENTS table Employee Address Line 1 Employee Address Line 2 Employee Address Line 3 Residence Phone Table: BOOKINGTYPES Description Unique identification for every Booking-Type Name of the Booking type Refers ID column of TARIFFS table Table: BOOKINGSOURCES Description Unique identification for every Booking-Source Name of the Booking Source viz. Phone, Internet, Booking-Agent, etc. Comments about the Booking Source Table: CHECK-IN Description Unique identification for every Booking Guest number Refers ID column of ROOMS table Refers ID column of BOOKINGTYPES table Refers ID column of BOOKINGSOURCES table Date of the arrival of guests at this hotel Amount deposited by the guest (also called CAUTION DEPOSIT) Table: CHECK-OUT Description Refers BOOKINGCODE column of CHECK-IN table Date on which the guest vacated the room Amount pending from the guest (if he is a regular customer or staff member) Table: GUESTSERVICES Description Unique identification for every bill produced by the restaurant and bar Refers ID column of CONSUMABLES table Refers ID column of ROOMS table Date on which the bill was created Table: RESERVATIONS Description Auto-generated unique identification for every booking made by customers Refers ID column of ROOMS table Name of the person who is booking the room(s) Date of booking Name of the guest (if different from BookieName) Number of adults who are going to stay Number of children who are going to stay Expected date of check-in
Table: SHIFTSCHEDULES (stores info about the shift for every employee, whether first, second, night or general) Column Name Data Type Description StartDate DATETIME Beginning date of the week EndDate DATETIME Ending date of the week EmpNo VARCHAR(8) Refers ID column of EMPLOYEES table Location VARCHAR(8) Duty place of employee for the week viz. Room No 45 to 60 or Restaurant or Banquets ShiftNo CHAR(1) Whether first shift, second shift, night shift or general shift (store only 1, 2, N or G here)
Column Name BookingCode PayDate DueDate Page 4
Table: PAYDATA1 (stores info about all payments made by guests) Data Type Description VARCHAR(8) Booking Code for the guest. Refers BOOKINGCODE column of CHECK-IN table DATETIME Date of the payment. Default is today’s date DATETIME Due date for the payment (in case the customer is staying today and paying after few days) SRM Systems & Software P Ltd.
Project Specification DueAmount PayAmount PayMode PayRef Remarks Table: Column Name BookingCode RoomNo CancelDate Remarks FineLevied FineWaived FineCollected
Page 5 NUMERIC(9,2) NUMERIC(9,2) VARCHAR(10) VARCHAR(50) VARCHAR(255 )
Amount to be paid by customer/guest Amount paid by the guest/customer Mode of the Payment, whether Cheque or Cash or Credit Card Payment Reference i.e. Cheque/Credit Card Number along with bank & branch name Comments about Payment
CANCELLATIONS (stores info about reservations which are not promoted to check-in i.e. cancellations) Data Type Description VARCHAR(8) The BC which you want to cancel. Refers BOOKINGCODE column of RESERVATIONS table VARCHAR(8) Refers ID column of ROOMS table DATETIME Date of the cancellation VARCHAR(255 Any comments about the cancellation ) NUMERIC(9,2) Fine charged for the cancellation NUMERIC(9,2) Amount of fine waived by the hotel (if the guest is known to F & B manager or such cases) NUMERIC(9,2) Total fine amount collected from guest
Menu Hierarchy HMS starts with a CONTROL CENTRE screen, which is nothing but a MDI form containing these menu items:
The CHECK-IN Menu Item (under BOOKING menu) This option should present a screen which displays a grid in the centre and a couple of options on top of it. The options are: •
Page 5
An Availability Button which will open a popup. The popup will ask for Start Date and Number of Days to Display and will display the availability status of all rooms within those particular days.
SRM Systems & Software P Ltd.
Project Specification
•
Page 6
A New Booking Button which opens a popup and accepts Arrival Date, Departure Date, Room Type, Number of Adults and Number of Children. Based on the user’s choice of room type, all available rooms in that category must be displayed and user must choose one room from this list. After selecting a room, a big window like this should be displayed:
When the operator clicks on “DEPOSITS” button, a small popup window containing Mode of Payment, Amount Paid and Date of Payment (today’s date) should be displayed. Always, the status of a room should be “PROVISIONAL” when a guest makes reservation. Status should be changed to “CONFIRMED” when the guest makes a deposit (usually 2 days rent). •
A Check-Out Button which requires at least one booking on the grid to be highlighted (selected).
•
A View Booking button which requires at least one booking on the grid to be highlighted (selected). This button will open a popup window which contains information about Room number, arrival date, departure date, number of nights stayed, number of adults, number of children, tariff charged, guest name, deposits received from guest, any payments received so far from guest, booking type, booking source, remarks and total accommodation cost. You should also provide options to print, make group booking and add to group.
•
A Sort By Frame containing a drop-down list box with options Booking Code, Group Code, Arrival Date, Departure Date, Room Number, Guest Name, Balance, etc.
•
A Filter by Room Type Frame containing 2 radio buttons – SINGLE and DOUBLE.
The grid should contain the following information:
Page 6
SRM Systems & Software P Ltd.
Project Specification
Page 7
You can also include a frame titled “Search For” with a set of radio buttons - “All Bookings”, “Future Bookings”, “Arrivals”, “StayOvers”, “Departures”, “Past Bookings”, “Created Date”, “Booking Code” and “Group Code”. Whenever a user clicks on one radio button, the grid must immediately display the information based on the radio button selected. You can assign different colors for confirmed rooms, checked-in rooms, checked-out rooms and cancelled rooms. Make your grid colorful. For example, the above picture shows a sky-blue color for bookings no. 685, 686, 676 & 684, which means the rooms have been checked-out. The CHECK-OUT Menu Item (under BOOKING menu) If the “CHECK-IN” window is already open, then this option must bring CHECK-IN window to the top. If not, the CHECK-IN window must be opened with all guest info displayed in a grid (only those guests who are currently staying in the hotel). The operator should select one row at a time and click the “CHECK-OUT” button. This should open a new window showing the expenses incurred by the guest so far, during his stay. That is, all restaurant bills, permit room bills, room service bills, banquet bills, telephone charges and room rent must be displayed VERY NEATLY on this screen. Include a “CHANGE” button next to Restaurant Charges and Banquets Charges textboxes (it is possible to give discounts on these charges if the guest is known to the F & B Manager or some top person in the hotel). In the bottom of the charges screen, you should display a “PAY NOW” button which will open a modal popup window and accept MODE OF PAYMENT, AMOUNT and PAYMENT REFERENCE (usually the Credit Card Number or Cheque Number with bank & branch name). The RESERVATION Menu Item (under BOOKING menu) If the “CHECK-IN” window is already open, then this option must bring CHECK-IN window to the top. If not, the CHECK-IN window must be opened. Repeat all the procedures for NEW BOOKING button in CHECK-IN screen. A reservation is usually done when a customer phones and requests for a room for the night. So the status of the room he requests should be set to “PROVISIONAL” in the database. The CANCELLATION Menu Item (under BOOKING menu) Only a room which is already reserved can be cancelled. So this menu option should open a window showing all currently reserved rooms, in a grid. You must also display some buttons like “VIEW GUEST DETAILS”, “POSTPONE BOOKING”, “ROOM AVAILABILITY”, etc. in this screen. The operator should click on a particular row and click “CANCEL NOW” button. Immediately that room status should be set to “CANCELLED” in the db. The PAYMENTS Menu Item (under BOOKING menu) This option should open a big window and display the same window as shown above, in page 5. The screen should calculate all payments due from the guest including restaurant expenses, bar expenses, room service expenses, etc. When operator clicks “OK” in the end, all this info must be recorded in the PAYDATA1 table. The GUEST DATABASE Menu Item (under MARKETING menu) This option should open a big screen with a grid. Display the Full Name, Company Name, PIN Code and Email Id in the grid, and have some extras like CREATE GUEST button, REMOVE GUEST button, SEARCH BY radio-button, SORT BY drop-down-list, etc. Also, please include a “ALL GUESTS” radio button somewhere ! This does not fall under the purview of this project and may please be omitted. For details contact arvindkmurthy. The REPORTS Menu Reports are the most vital pieces of information for a company’s self-assessment and further development. Always remember, all reports must have the hotel logo on the top right corner and name of report on top Page 7
SRM Systems & Software P Ltd.
Project Specification
Page 8
left corner. At the bottom, you should display DATE RUN: 23 October 2001, 14:30 hours on left bottom corner, PAGE n OF n on right bottom corner. All reports should accept a FROM DATE and TO DATE in a popup window first, and only then open the Crystal Report or Data Report (whatever it may be).
Page 8
SRM Systems & Software P Ltd.
Project Specification
Page 9
The ARRIVALS REPORT should display Booking Code, Room Name, Number of Nights, Guest Name, No. of Adults, No. of Children and Current Room Status between those dates. The DEPARTURES REPORT should display Booking Code, Room Name, Number of Nights, Guest Name, No. of Adults, No. of Children and Balance between those dates. The BOOKING TOTALS REPORT should display Total Number of Bookings, Total Accommodation Cost, Total Extras Cost, Total Bookings Cost and Total Balance between those dates. The CONSUMABLES DISBURSEMENT REPORT should display Consumable Name, Quantity Disbursed and Total Cost FOR EVERY ROOM between those dates given. So you must produce a grouped report. The HOUSE-KEEPING ACTIVITIES REPORT doesn’t fall under the purview of this project. So please omit this. The CANCELLED BOOKINGS REPORT should display Booking Code, Arrival Date, Departure Date, Guest Name, Cancellation Reason, Cancelled By, Total Cost, Deposits, Payments and Current Balance. Print totals for all numeric values. See the screen shot below for guidance.
The DEPOSITS RECEIVED REPORT should display Booking Code, Arrival Date, Departure Date, Guest Name, Payment Method and Deposit Amount between those dates. Print totals for all numeric values. The OCCUPANCY REPORT should be grouped by ROOMTYPE i.e. Occupation Date, Vacated Date, Number of Adults and Number of Children should be displayed for every room, categorized by room-type (Single Room, Double Room, Deluxe Room, etc) between those dates. The PAYMENTS RECEIVED REPORT should display Booking Code, Arrival Date, Departure Date, Guest Name, Payment Method and Amount of Payment between those dates. Print totals for TOTAL NUMBER OF ENTRIES and TOTAL PAYMENT RECEIVED.
Page 9
SRM Systems & Software P Ltd.
Project Specification
Page 10
The FINANCIAL BY ARRIVAL and FINANCIAL BY DEPARTURES REPORTS should display the following info:
The MAINTENANCE Menu Only the WEEKLY SHIFT SCHEDULE option must be enabled. All other options must be disabled. The WEEKLY SHIFT SCHEDULE screen should look like this:
The LICENSING Menu Item (under SETTINGS menu) This option should open a window and display the License Number, Hotel Name, Hotel Address, Telephone Numbers, Email Addresses, etc. The ACCESS CONTROL Menu Item (under SETTINGS menu) This option should display a window with one list box and three buttons (ADD, EDIT and REMOVE). The list box should contain all user names currently available. When the operator selects one username and clicks EDIT, a small window should popup. It should contain four text boxes: USERNAME (disabled), OLD PASSWORD, NEW PASSWORD and CONFIRM NEW PASSWORD along with Ok and Cancel button. Page 10
SRM Systems & Software P Ltd.
Project Specification
Page 11
The TARIFF SETTINGS Menu Item (under SETTINGS menu) See screen shot below for guidance.
The ROOM SETTINGS Menu Item (under SETTINGS menu) This option should display a window with one list box and three buttons (ADD, EDIT and REMOVE). The list box should contain all user names currently available. When the operator selects one username and clicks EDIT, The GUEST DATABASE SETTINGS Menu Item (under SETTINGS menu) This option should display an ADD-MOD-DEL screen for guests. Just display the guest names and their company names in a grid and include some option to ADD, MODIFY, DELETE and SEARCH FOR information. Put some radio buttons to search by First Name, Company Name, Phone Number, Guest Type, Nationality, etc. Also include options to print customer standings, send email to a particular customer or a group of customers together. The PAYMENT MODES Menu Item (under SETTINGS menu) This is an ADD-MOD-DEL screen for the different paymodes like Cash, Bank Transfer, Cheque, Credit Card, Debit Card, etc. The SYSTEM SETTINGS Menu Item (under SETTINGS menu) This option should open-up a screen containing three tab pages – Module Access, Database Store and Color Schemes. The first tab page should display a frame with caption “Login Required For” containing five checkboxes - “Booking”, “Marketing”, “Reports”, “Maintenance” and “Settings”. If the operator ticks an option, then HMS should demand a password (in a popup window) every time the operator chooses an item from that particular menu. The second tab page “Database Store” should allow operator to change the database store. This screen should contain two radio buttons reading “Microsoft Access” and “ODBC”. If operator chooses “MS Access” then a textbox should appear and allow him to browse for the MDB file. If he chooses the “ODBC” option, then a drop-down list box containing the options “Oracle ODBC”, “Oracle – OLE-DB” and “SQL Server – OLE-DB” should appear. Next to this drop-down, there must be a text box where user can type the connection string. This option is used in case the hotel wants to change to Oracle or SQL Server or Sybase or Ingres or Informix database (or vice versa) midway between. Page 11
SRM Systems & Software P Ltd.