If Value is between Value and Value..

[Cheesecake87]

Hey,

Building a hotel booking system as a college project, And as I do not want to go through the building a calendar that shows dates that are already booked, aww can be bothered.

ok the max number of days of stay allowed are 7, now I have thought of a system that will just store 2 dates in the database - bookings/room table - DateIn and DateOut.

Now I was just gawna show all the rooms in a loop with an if statement matching Booking/Room ID in with the likes of:

IF DateNow is Between DateIn and DateOut

THEN Room is Booked - room is available from COUNT days untill DateOut - LINK book from this date>

ELSE Room is available from - COUNT days untill next booking - MAX COUNT 7 - LINK Book this room>

ENDIF

Counting the days will be counting days in a full date format output.

Now I have all the theory, I have no idea how to write it LOL ... So help please :D .. or if any1 has a better way of working this sytem, I'm open for sugestions.

Thanks

[arrgh]

(What you describe would be bad in a real application) But it sounds like you have already mapped out an algorithm. So how about showing some good faith effort first.

What code have you tried, what problems are you having, etc...?

aww can be bothered... just gawna show

Just a bit of advice, your .. erm... initiative, grammar, and lack of any code whatsoever - are not exactly motivating us to want to help here .. ;-)

[Cheesecake87]

*Sigh* OK...

I just need it to be simple, this application is not being used by the real world.
It is just a college project that has to have basic requirements.

These requirements being:
• Storing customer information
• Booking a room
• Storing the booking date
• Change room details
• Change Pricing

I have looked at the possibility to have a coldfusion calendar that would show the days that the room is booked for, browsing the calendar using months. I know this would be the normal way of doing this type or project, but I am looking for something simpler.

So I came up with a system that would just count days from a selected date.

The maximum amount of days allowed to stay at the make believe hotel is seven, and there is a set number of 8 rooms.

This system would then be useful for people with no prior telephone reservation.

Ok now the only thing that is complex is counting the days from the next booking.

Here is the none code theory:

Hotel worker clicks to page that checks room availability:-

Application then lists all the rooms. Room status will be shown beside room, either availiable or occupied.
The application will run a query for each room using the current date to find out what rooms are booked.
It will do this by finding out if the current date is in between a booked range.

an example being:

IF current date is more than or equal to #CheckInDate# AND less than or equal to #CheckOutDate#

THEN Room is occupied [Count the days until the room is available (Days untill #CheckOutDate#)]

ELSE

Room is available[Link to book room]

ENDIF

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

I recently have managed to create a simple if statement like above just using numbers being set in the code.

Example:

<cfset #myNumber# = 3>
<cfset #first# = 1>
<cfset #last# = 7>

<cfif #myNumber# GTE #first# AND #myNumber# LTE #last#>
Your number is between the range
<cfelse>
Your number is NOT between the range
</cfif>

Now this is a fairly easy bit of code, converting this to accept dates is what I am stuck at. Not only this, but I have problems trying to think of a good database structure to accompany this design, as well as showing the information in a list.

So advice on this would be good.

[arrgh]

Application then lists all the rooms. Room status will be shown beside room, either availiable or occupied.
The application will run a query for each room using the current date to find out what rooms are booked.

Instead of doing most of the logic in CF, you would be better off using SQL. Run a query against your "reservations" table to find out which rooms are available during the desired date periods. But that's all db logic:

http://forums.devshed.com/ms-sql-development-95/filtering-on-startdate-and-enddate-question-277324.html

You're going to have to know some SQL, in addition to CF. It's not hard. But if your course doesn't cover SQL, definitely start with a tutorial like this one first:
http://www.w3schools.com/sql/default.asp

The db structure you described so far is not bad. You'll have to build on it for pricing, etc. But the basics are right

TABLE: Rooms (Unique rooms)
TABLE: Reservations (RoomID, StartDate, EndDate)

Example:

<cfset #myNumber# = 3>
<cfset #first# = 1>
<cfset #last# = 7>

<cfif #myNumber# GTE #first# AND #myNumber# LTE #last#>
Your number is between the range
...
</cfif>

Get rid of all the extra pound signs in those lines. You don't need them, and the code looks more professional without them.

ie Use <cfset myNumber = 3>
Instead of #myNumber# = 3>

[Cheesecake87]

OK I'm completely suck,

I have my bookings table in my database, looks like this -

BOOKINGID
ROOMID
CUSTOMERID
DateIN
DateOUT

Now that's pretty easy to work out what's happening there, now, here is where I am stuck. Now I have had a look at the SQL statements for querying, but suppose I go on the system to check what rooms are available, between the two selected dates that I choose.

OK so lets think about this, so I need to find out what rooms are not booked between these two dates, so:

SELECT *
BOOKINGS
Where SELinDATE between DateIN and DateOUT ... this is not going to work at all.
Where SELinDATE > .... na still not seeing that working.

I don't want this, the funny thing is I cant find a statement for not between.

*EDIT*(OK there is a not between statement, but any ideas of how to get this working using two values instead of one ?)

Is there another way of doing this ? its driving me crazy.

I need to show all the rooms that are NOT booked between two dates entered.

*EDIT* ( Can you have :

SELECT *
BOOKINGS
WHERE DateIN and DateOUT NOT BETWEEN SELinDate and SELoutDate ??? yes / no would be fine, that would work I'm sure.)

[arrgh]

I need to show all the rooms that are NOT booked between two dates entered.

I would approach it another way- with a subquery.

The query in the link I posted earlier would give you the _reserved_ rooms. Obviously that's the opposite of what you want. But if you use it as subquery, and add in a NOT EXISTS clause, it should return the results you want: ie Rooms that are not reserved

--- get unique rooms
SELECT r.RoomName, etc...
FROM YourRoomTable r
--- that are NOT in the set of RESERVED rooms
WHERE NOT EXISTS (
... select query from the other thread ....
)

[Cheesecake87]

I have got it working using not between, but it has a bug when either values day is 5 or below.

Can you please explain your method in a little more detail.

Thank you for your help.

[Cheesecake87]

Here is my code:

<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif isDefined("FORM.SearchRooms")>

<cfset #CalDate# = #DateFormat( DateAdd( 'd', #FORM.SelDays#, #FORM.SelDate# ), 'dd/mm/yyyy' )#>

<cfquery name="Check_Rooms_RS" datasource="HotelBookingSystem" username="HBSuser" password="HBSpass">
    SELECT *
    FROM Bookings
    WHERE BookingDateIN AND 
    BookingDateOUT NOT BETWEEN 
    <cfqueryparam value="#FORM.SelDate#" cfsqltype="cf_sql_timestamp"> 
    AND 
    <cfqueryparam value="#CalDate#" cfsqltype="cf_sql_timestamp">
</cfquery>

<cfquery name="Rooms_RS" datasource="HotelBookingSystem" username="HBSuser" password="HBSpass">
SELECT *
FROM Rooms
WHERE RoomID = <cfloop query="Check_Rooms_RS">#RoomID# OR </cfloop>0
</cfquery>

</cfif>

    	<cfparam name="PreSelDate" default="#LSDateFormat(Now(), 'dd/mm/yyyy')#">
<cfif isDefined("FORM.SearchRooms")>
    <cfset #PreSelDate# = #FORM.SelDate#>
</cfif>
        <cfform name="form1" width="375" height="350" action="#currentpage#">
           <cfinput type="DateField" name="SelDate" label="Block out starts" width="100" value="#PreSelDate#" mask="DD/MM/YYYY">
           
<cfselect size="1" name="SelDays" required="Yes" message="Select days staying">
      <option value="1" selected>1 Day</option>
      <option value="2">2 Days</option>
      <option value="3">3 Days</option>
      <option value="4">4 Days</option>
      <option value="5">5 Days</option>
      <option value="6">6 Days</option>
      <option value="7">7 Days</option>
</cfselect>

           <cfinput type="Submit" name="SearchRooms" value="Check" width="100">
        </cfform>
    </div>
    
	<cfif isDefined("FORM.SearchRooms")>
        <div class="search_results">
        	<cfoutput query="Check_Rooms_RS">
            	#Check_Rooms_RS.RoomID#
            </cfoutput>

            <cfoutput query="Rooms_RS">
            	#Rooms_RS.RoomType#
            </cfoutput>   
        </div>
    </cfif>

[arrgh]

First, those dates aren't going to work the way you're thinking. The regular CF date functions use U.S. date formatting rules ie mm/dd/yyyy. It will be able to figure out some values like 27/05/2010. But when you say "07/05/2010", you're going to get July 5th, not May 7th.

If your CF instance is using a locale where dd/mm/yyyy is the standard (like in the UK, etc..) then use LSParseDateTime() on your date string first. LSParseDateTime will parse FORM.SelDate into a date _object_ using your locale's rules. (Obviously, nothing will change if your machine is using the English (US) locale.)

<cfset parsedDate = LSParseDateTime(FORM.SelDate)>

If you're not sure what locale you're running, check:

<cfdump var="#getLocale()#">

<cfset #CalDate# = #DateFormat( DateAdd( 'd', #FORM.SelDays#, #FORM.SelDate# ), 'dd/mm/yyyy' )#>

Don't use DateFormat(). It's for display purposes only because it returns a string, not a date object like LSParseDateTime(), dateAdd(), etc... When you use strings, unexpected things can happen (like getting July 5th instead of May 7th). So always use date objects unless you're displaying output.

As far as the query from the link, just convert the example to fit the columns in your form and table. So the subquery would find all rooms that _are_ reserved between your two dates: FORM.SelDate and CalDate.

Totally untested, but something like

<!--- Legend

       SD = startdate, ED = enddate    (BookingDateIN and BookingDateOut)
       FS = filter startdate, FE = filter enddate (FORM.SelDate and CalDate)

       where FE >= SD and FS <= ED
      ---> 

       ie .... types omitted for brevity

       SELECT BookingDateIN FROM Bookings
       WHERE <cfqueryparam value="#form.selDate#" cfsqltype="cf_sql_timestamp"> >= BookingDateIN 
       AND   BookingDateOut <= <cfqueryparam value="#CalDate#" cfsqltype="cf_sql_timestamp"> <= BookingDateOut

Then just make it a subquery and match on the room number column. By using NOT EXISTS, the result will be all rooms that are NOT reserved during the given dates.

SELECT r.RoomName, etc...
       FROM YourRoomTable r
       --- that are NOT in the set of RESERVED rooms
       WHERE NOT EXISTS (
       SELECT b.BookingDateIN FROM Bookings b
       WHERE <cfqueryparam value="#form.selDate#" cfsqltype="..."> >= b.BookingDateIN 
       AND   BookingDateOut <= <cfqueryparam value="#CalDate#" cfsqltype="cf_sql_timestamp"> <= b.BookingDateOut
       AND   ... match on room number column ...
      )

[arrgh]

<cfqueryparam value="#form.selDate#" cfsqltype="cf_sql_timestamp">

In case it wasn't obvious, that was just to demonstrate the relation between your columns/fields and the example. In the real code you'd need use LSParseDateTime() on the form fields first.

[Cheesecake87]

SELECT r.RoomName, etc...
FROM YourRoomTable r
--- that are NOT in the set of RESERVED rooms
WHERE NOT EXISTS (
SELECT b.BookingDateIN FROM Bookings b

What are the r.RoomName and b.BookingDateIN, is the r. , b. , "Bookings 'b'" and "YourRoomTable 'r'" the name of the table ? looks a bit confusing cause its just one letter.

Thank you for your response

[arrgh]

Good Luck

-----------
Cast not pearls before swine