erikTsomik
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
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> </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>
ASKER
Any advise
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)?
is that what you are saying?
and also are the schedules created by a person (start and end times)?
ASKER
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
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.
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.
ASKER
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 ...
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 ...
ASKER
the travel time does not get paid it is done to utilizing the cars
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm glad that concept worked for you
ASKER