Link to home
Start Free TrialLog in
Avatar of erikTsomik
erikTsomikFlag for United States of America

asked on

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

Avatar of erikTsomik
erikTsomik
Flag of United States of America image

ASKER

Any suggestions
Any advise
Avatar of dgrafx
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)?
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
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.
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
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 ...
the travel time does not get paid it is done to utilizing the cars
ASKER CERTIFIED SOLUTION
Avatar of dgrafx
dgrafx
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm glad that concept worked for you