Need help on DateCompare

[bataraguru]

Hi guys,
I'm currently on a project for my company developing a resort booking system.
So far this is what i've done, i know its really messy.
2 input from form 'ChkInDate' & 'ChkOutDate' and 2 input from database 'ChkInDate' & 'ChkOutDate'. The system should compare there date that user can know weather he/she can book the room or not.

<cfloop query="SearchRoom">
<cfif DateCompare(#ChkInDate#,#form.ChkInDate#) EQ 0>
	Cannot book 1...
<cfelseif DateCompare(#ChkInDate#,#form.ChkOutDate#) EQ 0>
	Can book
<cfelseif DateCompare(#ChkInDate#,#form.ChkInDate#) EQ -1 AND DateCompare(#ChkOutDate#,#form.ChkOutDate#) EQ 1>
Cannot book 2...
<cfelseif DateCompare(#ChkInDate#,#form.ChkInDate#) EQ 1 AND DateCompare(#ChkOutDate#,#form.ChkOutDate#) EQ -1>
Cannot book 3...
<cfelseif DateCompare(#ChkInDate#,#form.ChkInDate#) EQ 0 AND DateCompare(#ChkOutDate#,#form.ChkOutDate#) EQ 0>
Cannot book 4
<cfelseif DateCompare(#ChkInDate#,#form.ChkInDate#) EQ -1 AND DateCompare(#ChkOutDate#,#form.ChkOutDate#) EQ -1>
Cannot book 5
<cfelseif DateCompare(#ChkInDate#,#form.ChkInDate#) EQ 1 AND DateCompare(#ChkOutDate#,#form.ChkOutDate#) EQ 1 AND DateCompare(#ChkOutDate#,#form.ChkInDate#) EQ 1>
Cannot book 5
<cfelse>

</cfif>
</cfloop>

Is there any way that i can simplify my code or anyone else have a very short way represent my code.

thank you so much..

 

[GubaGuba]

It is unclear what you want to do.

Why there are 2 couple of dates ?

Guba

 

[GubaGuba]

However, just to say...
1. Don't do repetitive use of the same functions with the same parameters. Is useless and it overloads the server for nothing.
2. Don't overuse complex comparative functions for the same reason.
3. KISS! Keep It Simple and Stupid! THE CODE, I MEAN...

I rewrite here, while I haven't understood what you want to do, the same concept of your code:

<CFSET DummyDate=CreateDateTime(2000,1,1,0,0,0)>
<CFSET F_CheckIN=DateDiff("d",DummyDate, Form.ChkInDate)>
<CFSET F_CheckOUT=DateDiff("d",DummyDate, Form.ChkOutDate)>
<CFIF F_CheckIN LTE F_CheckOUT>
<cfloop query="SearchRoom">
<CFSET Q_CheckIN=DateDiff("d",DummyDate, SearchRoom.ChkInDate)>
<CFSET Q_CheckOUT=DateDiff("d",DummyDate, SearchRoom.ChkOutDate)>

<cfif Q_CheckIN EQ F_CheckIN>
Cannot book 1...
<cfelseif Q_CheckIN EQ F_CheckOUT>
Can book
<cfelseif (Q_CheckIN LT F_CheckIN) AND (Q_CheckOUT GT F_CheckOUT) EQ 1>
Cannot book 2...
<cfelseif (Q_CheckIN GT F_CheckIN) AND (Q_CheckOUT LT F_CheckOUT) EQ -1>
Cannot book 3...
<cfelseif (Q_CheckIN EQ F_CheckIN) AND (Q_CheckOUT EQ F_CheckOUT) EQ 0>
Cannot book 4
<cfelseif (Q_CheckIN LT F_CheckIN) AND (Q_CheckOUT LT F_CheckOUT) EQ -1>
Cannot book 5
<cfelseif (Q_CheckIN GT F_CheckIN) AND (Q_CheckOUT GT F_CheckOUT) EQ 1 AND DateCompare(Q_CheckOUT GT F_CheckIN)>
Cannot book 5
<cfelse>

</cfloop>
</CFIF>

Let's comment it together.
<CFSET DummyDate=CreateDateTime(2000,1,1,0,0,0)>
This should have been done somewhere in the application declarations, once for ever.
This creates a socket date, in days number. The CREATION date.

<CFSET F_CheckIN=DateDiff("d",DummyDate, Form.ChkInDate)>
<CFSET F_CheckOUT=DateDiff("d",DummyDate, Form.ChkOutDate)>
This transforms the Form dates in numbers, simple alike, as distances from the CREATION date.

<CFIF F_CheckIN LTE F_CheckOUT>
... obviously the dates of the input must be ok....

the shoot the loop!
BUT...
1. convert the database dates in simple days from CREATION date...
2. use a very simple and readable comparison among simple integers, while unreadable (and slow) DATECOMPARE functions.
3. keep your code clean.

PS
The creation date may be any date. Because you are writing some booking application, the creation date may be the January 1st of the last year. Small numbers.

Guba

 

[bataraguru]

thanks, actually this is the checking module for room booking system.
It is base on a date user entered in the "search room available" and that is room number, check-in & check-out date.
onced user clicked the "check" button the system will check the whole booking table in the databse to find all available rooms that suit the check-in & check-out date.
The module will display the available room for them.
There will be several coparison in the module.
1. if form.check-in date equal to query.check-out date in the database, user can book the room and display AVAILABLE.
2. if the form.check-in & form.check-out are in the middle of query.check-in & query.check-out, user cannot book the room & display NOT AVAILABLE.
3. if the form.check-in are in the middle of query.check-in & query.check-out, user cannot book the room and diaplay NOT AVAILABLE.
4. if form.check-in is before query.check-in but form.check-out in the middle of query.check-in & query.check-out, user cannot book the room & display NOT AVAILABLE.
5. if form.check-in is in the middle of query.check-in & query.check-out AND form.check-out are after query.check-out, user cannot book the room & display NOT AVAILABLE.
6. if both form.check-in & form.check-out are not overlaping with query.check-in & query.check-out, user can book the room & display AVAILABLE.

This page is actually how i want my checking module to be done, but i still cant figured out how. Stupid me..
https://secure.yha.com/yha/hostels/bookings/index.cfm?&yhatoken=-1&hostelid=35

Thanks for the help guys, anyone have simpler solution.

 

[bataraguru]

anyone care to help??

thanks

 

[TruthInSatire]

Some times its hard to tell what a programmer is trying to do and why. This isn't any fault of your own.

What I would suggest is, "if it aint broke don't fix it." eventually your CF skills will improve and with that you'll find the best way to write that code. It's ok for new programmers to write sloppy code. You can't expect to read a book or take a class and write the prettiest, most efficient code ever. It's part of the learning process. Just like any other skill, the more you do it the better you get (providing your open to learning from other people's code, and have access to it. I still learn a lot from the other experts here at tek tips.

in the mean time i'd use Guba's suggestion with cfsets to prevent over-using functions to get the same result.

also you're crazy with extra pound signs. Check out this faq, it has some good info even for non-dreamweaver users. if you're a dreamweaver user it'll help a lot.

faq232-5806





Beware of programmers who carry screwdrivers.

 

[r937]

i don't understand why you have the date checking logic in coldfusion

it makes me suspect that you're pulling all the booking data out of the database, which seems somewhat excessive, to say the least

if it was me, i would simply pull only the available rooms out of the database

<cfquery name="getavailablerooms" datasource="foo">
  select [i]columns[/i]
    from [i]tables[/i]
   where [i]check for available dates[/i]
</cfquery>

<cfif getavailablerooms.RecordCount>
List of available rooms:
  <cfoutput query="getavailablerooms">
    #getavailablerooms.[i]columns[/i]#
  </cfoutput>
<cfelse>
No rooms available
</cfif>

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[bataraguru]

i so sorry if i have confused all of you or dont understand what is my problem.
the system i want to develope is a room booking system.
i have a form with check-in & check-out date and room type. i also have a table name 'booking' where there are also check-in & check-out date for the booked room.
Potential customer will entered the date they want to check-in & check-out with the room type. After they clicked submit the system should check all the available room suit the date entered in the form.
The only part that i'm so confused is the one with the checking the availability based on the check-in & check-out entered in the form.
It is similar to the Hotel Booking System. Is there any one have experience in developing a hotel booking system. I just want to know how to do the availability checking only. Thanks a lot guys.. I do really appreciate your comments & help.

 

[bataraguru]

Similar example of what i want to do.
The one in the "Make a Reservation" part.

http://www.northstarhotel.ie/home/index.php

 

[r937]

RS = Request Start date
RE = Request End date
Request = booking that the user/customer wants to have
BS = Booked Start date
BE = Booked End date
Booked = bookings that have already been made by someone

there is one pair RS,RE but possibly many pairs BS,BE

assume RS <= RE and BS <= BE

what you look for is that none of the dates between RS and RE overlap with any date in any Booked range BS,BE

if RE <= BS then RS,RE does not overlap BS,BE
if BE <= RS then RS,RE does not overlap BS,BE
all other cases (there are 4 of them) overlap

run a query with WHERE NOT ( RE<=BS OR BE<=RS )

if this query returns any rows, there is an overlap, so the request RS,RE cannot be filled

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[diablo03]

Hi, what u r trying to do is fair simple. I've done some apps like this. Actually, the most complex one I've done involved looking for free conference rooms, which implies both date and time.

You should be all set with a solution like the one r937 posted; no need to do fancy stuff.
I recommend you write a SQL query with these conditions -which covers all the date range possibilities:

-RequestedChkI<BookedChkIn AND RequestChkO <=BookedChkIn
-RequestedChkI<=BookedChkIn AND (RequestChkO>BookedChkIn and RequestChkO<=BookedChkOut )
-(RequestedChkI>=BookedChkIn and RequestedChkI<=BookedChkOut) AND RequestChkO<=BookedChkOut
-RequestedChkIn>=BookedChkOut and RequestChkO>BookedChkOut

I might have some errors on the conditions, but I guess you get the idea -you should!

If this query returns 0 rows, then you know there are not available rooms.

Obviously, you should check that the requested dates are congruent -check out later than check in, you should check this with a JS b4 submitting the form.

This kind of problem is easy to solve, just remember KISS!