Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

coldfusion reservation system

Posted on 2015-01-04
17
Medium Priority
?
187 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 2
  • +1
17 Comments
 
LVL 25

Expert Comment

by:dgrafx
ID: 40531510
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
ID: 40531538
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
ID: 40531634
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
Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

 
LVL 25

Expert Comment

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

Author Comment

by:erikTsomik
ID: 40531740
I need to create a time availability drop down. Based on the instructor and specified location.
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 40531766
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 2000 total points
ID: 40532054
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 25

Expert Comment

by:dgrafx
ID: 40532070
that is the approach I'd probably take _agx_ ...
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40532078
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
ID: 40532081
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 25

Expert Comment

by:dgrafx
ID: 40532085
"the buffer" ???

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

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 40532086
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
ID: 40532141
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 25

Expert Comment

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

Author Comment

by:erikTsomik
ID: 40532225
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 25

Expert Comment

by:dgrafx
ID: 40532253
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
ID: 40566497
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

636 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