Solved

coldfusion reservation system

Posted on 2015-01-04
17
174 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 500 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 500 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
site launch date and last modified date 3 99
cookies analysis tools 2 100
Update cached table in H2 database 6 68
MYSQL database problem within Coldfusion 2016 environment 12 50
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
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 the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

821 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