COldFusion html Display

I am trying to display a car reservation and it works fine for me. The only thing I can not get my head around is how to handle a a buffer time if the garage have remote locations.

For example, the session end and 8:00 AM and the garage have remote locations so the next sessions may be scheduled not at 8:00 AM but at 8:15AM. Basically I need to reset AvailShowflg to 0 and show unavailable

 <cfloop query="qCars">
							       	<td align = "center" ><font style="color:black;font-weight:bold;">Car-#qCars.carnumber# (#qCars.carKey#) #trim(qCars.locationName)#</font><br>&nbsp;</td>
						     	</cfloop>
							<cfset altcnt = 0>
							<cfloop index="ii" from="#startHour#" to="#endHour#">
							<cfloop index="jj" from="0" to="#60-qryGetStateInfo.btwMinute#" step="#qryGetStateInfo.btwMinute#">
								<tr>
								<cfif altcnt eq 0>
									<cfset altcnt = 1>
									<td align = "center" bgcolor="gray">
								<cfelse>
									<cfset altcnt = 0>
									<td align = "center" bgcolor="white">
								</cfif>
									<cfset time = createtime(ii, jj, 0)>
									<cfset slottime = #timeformat(time, 'HH:mm')#>
									#slottime#
								</td>
								<cfloop query="qCars">
									
									<cfset timecarkey = qCars.carkey>
								    <cfset AvailShowflg = "1">
                                    <cfquery name="qAssignedSessions1" datasource="#request.datasource#">
                                         select c.carkey, c.carnumber, su.sessionunitkey, su.sessionend, su.sessionstart,   i.firstname+','+i.lastname instname,left(lastname,7)+''+left(firstName,1) as lName,left(l.name,6) as locName,
                                        
                                       <!--- <cfif isRemote eq 1>
                                            DateAdd(n,-15, su.sessionstart) as sessionstart,
                                            
                                        <cfelse>
                                            su.sessionstart, 
                                        </cfif>	--->
                                         (SELECT count(1) from SessionMap SM
                                             where SM.sessionKey=S.sessionKey
                                             and SM.userKey<>0
                                           )as cnt
                                        FROM Car c
                                        inner join Session s on s.carKey=c.carKey
                                        inner join sessionUnit SU  on s.sessionKey=su.sessionKey
                                        inner join instructors i on i.instructorkey = su.instructorkey
                                        left outer join Location L  on L.locationKey = S.locationKey
                                        WHERE
                                         CONVERT(date, su.sessionStart, 120) = <cfqueryparam value="#dateformat(arguments.activeDt, "yyyy-mm-dd")#" cfsqltype="cf_sql_date">
                                        <cfif isdefined("timecarkey") and timecarkey neq "">
                                            and c.carkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#timecarkey#"/>
                                        </cfif>
                                        ORDER BY su.sessionstart
                                    </cfquery>
                        <!---<cfdump var="#qAssignedSessions1#">--->
									<cfloop query="qAssignedSessions1">
										<cfif (hour(slottime) gt hour(sessionstart) or (hour(slottime) eq hour(sessionstart) and minute(slottime) gte minute(sessionstart)))
											  				and (hour(slottime) lt hour(sessionend) or (hour(slottime) eq hour(sessionend) and minute(slottime) lt minute(sessionend)))>
											<cfif cnt gt 0>
												<td bgcolor="red" alt="" title=""><font style="color:black;font-weight:bold;">#lName# <cfif len(locName)>-#trim(locName)#</cfif> </font></td>
											<cfelse>
												<td bgcolor="yellow" alt="" title=""><font style="color:black;font-weight:bold;">#lName# <cfif len(locName)>-#trim(locName)#</cfif></font></td>
											</cfif>

											<cfset AvailShowflg = "2">
											<cfbreak>
										<cfelse>
									      
											<cfset availchecktime = dateadd('n',qryGetStateInfo.bwtSpan,slottime )>
				
                                                            			
											<cfif (hour(slottime) eq 22 and minute(slottime) gt 0) or ((hour(availchecktime) gt hour(sessionstart) or (hour(availchecktime) eq hour(sessionstart) and minute(availchecktime) gt minute(sessionstart)))
											  				and (hour(availchecktime) lt hour(sessionend) or (hour(availchecktime) eq hour(sessionend) and minute(availchecktime) lt minute(sessionend))))>
												<cfset AvailShowflg = "0">
												<cfbreak>
											</cfif>
										</cfif>
										
										<!---Check if it is buffer needed . *******Here is where the problem is --->
										<cfif AvailShowflg eq 1>
											<cfif isRemote eq 1>
												<cfif (hour(slottime) eq 22 and minute(slottime) gt 0) or ((hour(availchecktime) gt hour(sessionstart) or (hour(availchecktime) eq hour(sessionstart) and minute(availchecktime) gte minute(sessionstart)))
                                                            and (hour(availchecktime) lt hour(sessionend) or (hour(availchecktime) eq hour(sessionend) and minute(availchecktime) lt minute(sessionend))))>
                                                <cfset AvailShowflg = "0">
                                                <cfbreak>
                                            </cfif>
											</cfif>	
										</cfif>	
									</cfloop>
									
									<cfif  qAssignedSessions1.recordcount eq 0 and (hour(slottime) eq 22 and minute(slottime) gt 0) and isRemote eq 1>
										<cfset AvailShowflg = "0">
									</cfif>
									<cfif AvailShowflg eq 1>
										<td bgcolor="lightgreen"><font style="color:black;font-weight:bold;">Available</font></td>
									<cfelseif AvailShowflg eq 0>
										<td bgcolor="gray"><font style="color:black;font-weight:bold;">Unavailable</font></td>
									</cfif>
									
								</cfloop>
								</tr>
							</cfloop>
							</cfloop>

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

erikTsomikSystem Architect, CF programmer Author Commented:
Any suggestions
0
erikTsomikSystem Architect, CF programmer Author Commented:
Any advise
0
dgrafxCommented:
so you are saying that you may have 15 minutes (for ex) that are unavailable due to travel time?
is that what you are saying?
and also are the schedules created by a person (start and end times)?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

erikTsomikSystem Architect, CF programmer Author Commented:
Yes, If I have a remote location I would have a 15 minute buffer.

And Yes the schedule is created by a person for both start and end time
0
dgrafxCommented:
I would say the easiest is to manually set the start and end time to be 15 minutes or whatever is appropriate whenever there is this travel time delay.
Otherwise I imagine you could tie your functionality into a latitude / longitude database to calculate distance between points then estimate a number of minutes per mile.
0
erikTsomikSystem Architect, CF programmer Author Commented:
Calculation will not be possible. All I need is to say If I have a remote location then add 15 minute buffer or skip the slot or set a different status
0
dgrafxCommented:
ok so why not do just that?
i'm not trying to be sarcastic but just add time to the start time and or end time.
you can have a time block that is taken up from 8am to 9:30am but also the appointment time is actually 8:15 to 9:15.
i guess i don't understand WHY ...
does it have to do with billing?
and if so it is pretty common to bill for travel time - just sayin ...
0
erikTsomikSystem Architect, CF programmer Author Commented:
the travel time does not get paid it is done to utilizing the cars
0
dgrafxCommented:
ok so it looks like you have appointment start and end times figured out.
so just do a starttime = starttime - 15 minutes IF its a remote location and endtime = endtime + 15minutes also.
and also for billing the totaltime = totaltime - 30 minutes IF it's a remote location.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dgrafxCommented:
I'm glad that concept worked for you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.