Guest House Coding

**Guesthouse bookings ??" data entry and data display**

Currently booking entry uses a **database**, and everyday booking display uses a **spreadsheet**.

Booking entry is both manual and semi-automated; the display spreadsheet is now generated manually.

Semi-automated booking entry:

The semi-automated booking entry is based on data submitted in an e-mail via a web form.

For semi-automated booking entry the e-mail gives rise to a text file the contents of which are fed into the structure of the database. One booking comprises one or more room-bookings.

The specific room number and the agreed pricing are entered manually into the **RoomBookings** table, even in the “semi-automated?? case. (Guests request a type of occupancy, e.g. “double?? or “family??.)

Manual data entry:

Arrival and Departure dates are entered, the database structure above is created truly automatically for the new booking, and data entry proceeds manually via a multiple-page form in the database.

What we would like to happen is integration of Outlook, Access and Excel activity

What we would like to happen is integration of Outlook, Access and Excel activity.

## Deliverables

1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.

2) Deliverables must be in ready-to-run condition.

3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement).

--------------------------------------------------------

Guesthouse bookings ??" data entry and data display

Currently booking entry uses a database, and everyday booking display uses a spreadsheet.

Booking entry is both manual and semi-automated; the display spreadsheet is now generated

manually.

Semi-automated booking entry:

The semi-automated booking entry is based on data submitted in an e-mail via a web form.

For semi-automated booking entry the e-mail gives rise to a text file the contents of which are fed into

the structure of the database. One booking comprises one or more room-bookings.

The specific room number and the agreed pricing are entered manually into the RoomBookings table,

even in the “semi-automated?? case. (Guests request a type of occupancy, e.g. “double?? or “family??.)

Manual data entry:

Arrival and Departure dates are entered, the database structure above is created truly automatically for

the new booking, and data entry proceeds manually via a multiple-page form in the database.

What we would like to happen is integration of Outlook, Access and Excel activity

What we would like to happen is integration of Outlook, Access and Excel activity.:

We would like:

a) the database to generate the spreadsheet display, an example of which is provided; and

b) to provide an improved semi-automated booking entry system; and

c) miscellaneous extras.

1. TO GENERATE THE SPREADSHEET DISPLAY:

The display needs to show:

* Room number (R1-R15) and date (month, day of week, date in month) ??" e.g., by row &

column labelling: these can be assumed present and not drawn from the database;

* Booking start and finish ??" e.g., by marking the end of each booking with a thick vertical line;

* Occupancy status and type ??" e.g., by colour (“occupied?? (red) vs. “available for occupation??

(none) vs. “down for maintenance?? (grey), etc.) and hatching (single, double, family, or twin);

* Other booking details ??" e.g., booking agency (if any), guest names, guest country, bed

arrangements (s / 2s / 3s / 4s / d / ds / d2s / d3s), number of adults, number of children, arrival

method/time, pricing (daily room-rate / total / balance due), any special requirements.

“Other booking details??, above, are currently conveyed via a spreadsheet cell comment, in the start

date cell for the room-booking(s).

We find this arrangement best for visual overview in context, but other suggestions are welcome.

One noticeable problem with this approach is that years have 365/366 days and spreadsheets have 255

columns. Spreadsheets incorporate left- and rightward scrolling, also used on the web “Availability??

page (see <[url removed, login to view]>).

2. TO PROVIDE AN IMPROVED SEMI-AUTOMATED BOOKING

ENTRY SYSTEM:

BOOKING E-MAIL COMES IN

E-MAIL OPENED ??" KEYSTROKE TO START PROCESSING (AFTER USER APROVAL)

PROCESS :

write e-mail entries to new booking record

if ‘stayed_before’ = yes then check main_guest for first name/last

name/country ??" to connect new booking with older guest id. if

stayed before but not as main guest, new guest record to be created.

put up Room_Booking form for manual entry by user and enter

default values:

‘d’ for ‘beds’

‘2’ for ‘adults’

‘0’ for ‘children’

‘date of arrival’ from e-mail

‘departure date’ from e-mail

No of nights (calculated)

Room_Total (calculated)

User manually enters Room_No and Room_Rate and can edit

all except calculated fields.

ON EXIT FROM ROOM_BOOKING, DATABASE TO CHECK ANY CONFLICT FOR

ROOM_NOS/DATES

if conflict ??" show conflict Room_Nos and dates of conflict, and write

the new room_booking to room 0 in database, and then write it in

bright yellow on to special line ??" line 23 - in spreadsheet

MANUAL ENTRY (FROM TEL/LETTER BOOKING)

Set up blank records for a booking, in the standard linked structure

get entry from user for value of stayed_before

after entering main_guest’s lname/fname/country check for existing

main_guest record ??" show near misses (use phonetic/soundex

matching) and allow user to choose or reject rejection means a new

guest record needs to be created

let user enter values through forms

follow procedures above for on-exit from Room_Bookings

CHANGE OF ROOM_BOOKING

On exit from any change in Room_Booking:

if change in beds/no_adults/no_child re-write comment in

spreadsheet

if change in Room_No/either date/ re-write that room-booking

in spreadsheet to show change (Does not require re-writing of

whole spreadsheet.)

ON DELETION OF ROOM_BOOKING OR MARKING OF BOOKING AS ‘CANCELLED’

remove that room-booking from the spreadsheet and set the cells it

occupied to ‘available for occupation’

AFTER ENTRY OF NEW BOOKING

INSERT VALUES OF SELECTED FIELDS INTO STANDARD CONFIRMATION LETTER

a ‘confirmation e-mail’ requires no of nights, arrival day, end day,

departure day, length of stay (‘number of nights’) / room type / cost

of the room per night / balance to pay. For rooms 12-15 ‘, with

shared bathroom’ is also needed.

MISCELLANEOUS

To generate from the main database a new database centred round the notion of a DailyRoomBooking, with the

following fields:

Room number, date, room type, bed occupancy, number of adults, number of children, booking ID, main guest’s

last name, main guest’s country, room rate, cancelled? (Yes/No), agency (if applicable), repeat visitor (Yes/No),

staying weekly (Yes/No), No2 (True / False / Blank).

Note: This is a periodic generation: it is not required every time a new booking comes in, for instance.

## Platform

VB or VBA

Windows XP

Outlook 2003

Access 2003

Excel 2003

PHP

Nº du projet : #3619361

À propos du projet