Solved

coldfusion reservation system

Posted on 2015-01-04
17
162 Views
Last Modified: 2015-01-23
I'm working on a reservation system. THe idea is the instructor put their availability date and the system goes through the locations that that instructor is assigned to it will automatically pick the car  for that instructor and checks if there are any instructors who already reserved that location. if location is already picked and the car is the same pick another car. The final result should be the time when the instructor car serve a session. The times are from 6AM to 8PM.

Please advice
0
Comment
Question by:erikTsomik
  • 7
  • 7
  • 2
  • +1
17 Comments
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
I would think it would be not only easier to program but more user friendly to provide a calendar like interface that shows availability dates/times (and possibly unavailable dates/times grayed out).
At each step of the way show the user what's available and let them click to select and go on to the next step.

post back with your thoughts ...
0
 
LVL 18

Expert Comment

by:Jerry Miller
Comment Utility
This is a very vague question and it sounds as if you have a lot of design requirements to iron out. But here are some ideas to get you started.

You have to decide what type of interface you want for the instructors to choose times; calendar, drop down list, etc. If a list, populate it with a query that displays locations for the current instructor.Then it will display a car / location based on availability and ask the instructor to confirm.  

Your database should keep track of available cars for each location and the query to assign cars would look at only the cars that are unassigned during the time frame entered.

At the very least, your database would contain tables to hold Instructors, Locations, and Cars. You would most likely need bridge tables between Instructors / Locations, and Locations / Cars to make the assignments. the columns you need in each table depend upon the needs of the web site, but I generally always add some auditing fields. These usually include create date, create ID, modified date, modified ID, delete date, delete ID, and a Boolean active field.

You would also need some sort of admin interface so the cars and instructor can be assigned to locations. Plus someone will have to add / delete users and determine their access privileges.
0
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
I have already design all of that. I need to display available times in the drop dropDown from where the instructor will pick the time
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
ok - so forgive me but could you clarify what you are asking please ...
0
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
I need to create a time availability drop down. Based on the instructor and specified location.
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
well in general you could loop through your times and display based on if reserved or not
first you can query
<cfquery name="myQ">
select starttime from table
</cfquery>
so now you have a list of times that are already taken
loop from 6am to 8pm
<cfloop from="0600" to="2000" index="ii"> easier to use 24 hour time i would imagine
<option value="#ii#" <cfif listfind(valuelist(myQ.startttime),ii)> then some code that grays out and makes unselectable</cfif>>#ii#
</cfloop>

would need to do some time conversions coming out of DB probably so that query out is a list of 0700,0900,1400 etc AND need to tie in day and year I'd imagine

that of course is real quick and basic but should get you started ...
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
Comment Utility
If the reservation blocks are a standard time ie intervals of N minutes (15, 30, 45...) you could also tweak that approach. Create a db table that stores all of the standard times between 6:00 and 8PM

RecordID | IntervalTime
1    | 6:00 AM
2    | 6:30 AM
3    | 7:00 AM
4    | 7:30 AM
.....
N-1 | 7:30 PM
N    |  8:00 PM

Open in new window


Once you've created the table you can use an OUTER join to your reservation table to find the available times.  Not tested, but something along these lines.  

SELECT  it.IntervalTime AS AvailableTime
FROM     IntervalTable it LEFT JOIN ReservationTable rt 
                       ON rt.StartTime <  it.IntervalTime
                       AND rt.EndTime > it.IntervalTime
                       <!--- possibly other criteria like date and location here ....--->
WHERE  rt.StartTime IS NULL        

Open in new window

     

Since it's an OUTER join, you know the time is available if the reservation record is NULL. So the above should give you the available times in one query.  Again, the above isn't tested. It's just to show the concept.
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
that is the approach I'd probably take _agx_ ...
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
Would I check for the session conflict and a car conflict. Each session may or may not allow the buffer. Because the instructor may need to travel from location to a location
0
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
so far that what I came up with

[indent]<cfloop index="ii" from="#startHour#" to="#endHour#">
							<cfloop index="jj" from="0" to="45" step="15">
								<cfset showtime="y">
								<cfset time = createtime(ii, jj, 0)>

								<cfloop query="qAssignedSessions">
									<cfset preSessionStart = dateadd("n",-120-buffertime,sessionstart)>
									<cfset postSessionend = dateadd("n",bufferTime,sessionend)>
									<cfoutput>
										#buffertime#--#preSessionStart# -- #postSessionend#<br>
									</cfoutput>
							  		<cfif (hour(time) gt hour(preSessionStart) or (hour(time) eq hour(preSessionStart) and minute(time) gt minute(preSessionStart)))
							  				and (hour(time) lt hour(postSessionend) or (hour(time) eq hour(postSessionend) and minute(time) lt minute(postSessionend)))>
								  		<cfset showtime="n">
								  		<cfbreak>
								  	</cfif>
								</cfloop>
								<cfset ArrayAppend(timeslots, timeformat(time, 'HH:mm tt'))>
								<cfif ((<!---datecompare(arguments.startDt,latestSessionDate) eq 1 and---> ((hour(time) eq 20 and minute(time) eq 00) or hour(time) lt 20))
										or (datecompare(arguments.startDt,latestSessionDate) eq 0 and ((hour(time) eq 20 and minute(time) eq 00) or (hour(time) lt 20 and hour(time) gt hour(now()))))
											)
										and showtime eq "y">
									<option value="<cfoutput>#timeformat(time, 'HH:mm')#</cfoutput>"><cfoutput>#timeformat(time, "hh:mm tt")#</cfoutput></option>
								</cfif>
							</cfloop>
						</cfloop>[/indent]

Open in new window

0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
"the buffer" ???

when a reservation is made you update a db table - not stored in session ...
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
Comment Utility
Edit:  There's no reason you can't add some extra checks to the query, if that's what you're asking.  But I don't understand the "buffer" concept either.  At least not how you're applying it here.
0
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
well the instructor can service multiple locations. So he/she in location A and the session is finished at 10:00 AM his next session is in location B and they are 1 mile away from each other the instructor need some time to get there. That why I am checking the buffer time so the sessions are not scheduled back to back to avoid down time
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
if your "sessions" take 45 minutes - then have your app create 60 minute sessions - for example ...
0
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
and that what I am doing here . The session takes 120 minus and the buffer is 15 minutes

<cfset preSessionStart = dateadd("n",-120-buffertime,sessionstart)>
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
so in your db table for your time blocks - like agx suggested - use 135 minute time blocks instead of the 15 minute blocks he suggested  ...
6 am
8:15 am
10:30 am
etc ...
0
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
Provided this example how would I also remove some unwanted slots.

For example The instructor A had reservation from 6 to 8. so what I want to show next is 8 and 8:15 and block out the time for 105 min, so the next available time slot will be 10:00AM.

and then contunie 10:15,1030.

And then another reservation at 1:00Pm then show 3:00PM and 3:15 and block out untill 5:00pm
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will learn how to count occurrences of each item in an array.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now