ColdFusion - Time Display - Twin Set of Results - Matching Time Look Each Side

Question

This one has got me stuck...

I need to display 2 facility's side by side - with their opening times in the middle. And get each side on left and right to match up with their times.

Problem is - times are in 15 min increments. But sometimes left or right will be book for an hour or different than the 15mins increments.

So just gonna put this out there...

<CFSET pdate = #CREATEODBCDATE(todaydate)#>
<CFSET startdate = #CREATEODBCDATE(todaydate)#>
<CFSET enddate = #CREATEODBCDATE(todaydate)#>
<table>
  <tr>
    <td valign=top>
      <cfinclude template="_left.cfm">
    </td>
    <td valign=top>
      <cfinclude template="_middle.cfm">
    </td>
    <td valign=top>
      <cfinclude template="_right.cfm">
    </td>
  </tr>
</table>

_LEFT and _RIGHT Basically the same with SAID being SAIDLT and SAIDRT

<cfquery name="schlt" datasource="#ds#">
  select * from masterschedule
  where said = '#saidlt#'       or    where said = '#saidrt#'
  and schdate = #createODBCDate(pdate)#
  order by schstart
</cfquery>
<table cellpadding=20 cellspacing=0 align=center>
  <cfoutput query="schlt">
    or  query="schrt">
    <cfquery name="ug" datasource="#ds#">
      select * from users
      where uid = '#uid#'
    </cfquery>
    <tr bgcolor=<cfif (sch.currentRow MOD 2 EQ 0)>e8e8e8
    <cfelse>
    f7f7f7</cfif>>
    <td class=verd10 valign=middle>
      #Timeformat(schstart, 'H:mm TT')#
      <br>#Timeformat(schend, 'H:mm TT')#
      &nbsp;&nbsp;
    </td>
    <td class=verd11 valign=middle align=center>#ug.username#</td>
    </tr>
  </cfoutput>
</table>

_MIDDLE

<cfquery name="sadd" datasource="#ds#">
  select * from subarena
  where said = '#said#'
</cfquery>
<cfoutput>
  <cfset hrdiv = 60 / #sadd.increment#>
  <cfset hrsdiff1 = (DateFormat( todaydate, "yyyy-mm-dd" ) & " " & "#TimeFormat(sadd.topen)#") />
  <cfif sadd.tclose lte sadd.topen>
    <cfset hrsdiff2 = (DateFormat( todaydate, "yyyy-mm-dd" ) & " " & "#TimeFormat(sadd.tclose)#") />
    <cfset hrsdiff2 = #DateAdd('d', 1, '#hrsdiff2#')#>
    <cfelse>
    <cfset hrsdiff2 = (DateFormat( todaydate, "yyyy-mm-dd" ) & " " & "#TimeFormat(sadd.tclose)#") />    
  </cfif>
  <cfset loopreps = (#Abs(DateDiff("h", hrsdiff1, hrsdiff2))# * hrdiv) - 1>
</cfoutput>
<cfset schdate = '#startdate#'>
<cfloop index="dtToday" from="#StartDate#" to="#EndDate#">
  <cfoutput>
    <cfset usedate="#CreateODBCDate(dttoday)#">
    <cfset stime = (DateFormat( usedate, "yyyy-mm-dd" ) & " " & "#TimeFormat(sadd.topen)#") />
    <cfoutput>
      <table cellpadding=20 cellspacing=0 align=center>
        <cfset etime = #DateAdd('n', sadd.increment, '#stime#')#>
        <tr bgcolor=e8e8e8>
          <td class=verd10 valign=middle>
            #Timeformat(stime, 'H:mm TT')#
            <br>#Timeformat(etime, 'H:mm TT')#
            &nbsp;&nbsp;
          </td>
        </tr>
      </table>
    </cfoutput>
    <cfloop index="incr" from="1" to="#loopreps#">
      <cfoutput>
        <cfset stime = #DateAdd('n', sadd.increment, '#stime#')#>
        <cfset etime = #DateAdd('n', sadd.increment, '#stime#')#>
        <table cellpadding=20 cellspacing=0 align=center>
          <tr bgcolor=e8e8e8>
            <td class=verd10 valign=middle>
              #Timeformat(stime, 'H:mm TT')#
              <br>#Timeformat(etime, 'H:mm TT')#
              &nbsp;&nbsp;
            </td>
          </tr>
        </table>
      </cfoutput>
    </cfloop>
    <cfset schdate = #DateAdd('d', 1, '#schdate#')#>
  </cfoutput>
</cfloop>

The start times need to match up. So basically need the 1hr time - to span the 15min increments that are happening. — Merle_the_Pearl, Dec 03 '14 at 00:56
Two points: 1. use `` in your queries. 2. you don't need `# #` around variables in ``, e.g. `` could be written as `` — duncan, Dec 03 '14 at 09:04
So what is wrong with the result of the code above? The above is not a self contained example, so no one can run it without doing a lot of set up work. Most folks are busy and don't have that kind of time, so they'll just skip it instead. When it is not feasible to post a *completely* self-contained example, include other things to help clarify the issue, like: screen shots, data samples and/or a mock-up of the desired result. — Leigh, Dec 03 '14 at 14:52

score 0 · Accepted Answer · edited Dec 05 '14 at 17:35

You didn't provide a whole lot of details, so this is really really out of scope for the purpose of the site.

Further, I don't think this code even works. You have one spot in a query where you say

where said = '#saidlt#'       or    where said = '#saidrt#'
  and schdate = #createODBCDate(pdate)#

The proper way to set this up is most likely...

where (said = <cfqueryparam value="#said#" type="XXX"> or said = <cfqueryparam value="#said#" type="XXX">)
  and schdate = <cfqueryparam value="#createODBCDate(pdate)#" type="cf_sql_timestamp">

For the XXX in each, look up the tag and cordinate the value of that to that of the field type which there's no indication of in your code.

So your problem is display? The way I'd do this is stack divs one over the other, and program it so that the height was based on date occupied multiplied by a height.

I assume you're tracking the start and end time.

When I book an appoitment, I might log it like this..

You can do some math, determine the length of the appointment and use the length of the appointment as the height of the div which I demonstrate here, and below

<div style="color: <cfif some_schedule_flag is true>red<cfelse>green</cfif>; height: #DateDiff(...)#px">#Time#</div>

So it would be something like if a facility was scheduled for 9:30-9:45, 10-10:30 and 11-12 and the other facility it would look something like this

<div style="color: green; height: 15px">8:00</div>
<div style="color: green; height: 15px">8:15</div>
<div style="color: green; height: 15px">8:30</div>
<div style="color: green; height: 15px">8:45</div>
<div style="color: green; height: 15px">9:00</div>
<div style="color: green; height: 15px">9:15</div>
<div style="color: red; height: 15px">9:30</div>
<div style="color: green; height: 15px">9:45</div>
<div style="color: red; height: 30px">10:00</div> --- note the height of 30px, because it covers the gap
<div style="color: green; height: 15px">10:30</div> --- note that 10:15 is missing
<div style="color: green; height: 15px">10:45</div>
<div style="color: green; height: 60px">11:00</div> --- note the 60px height
<div style="color: green; height: 15px">12:00</div>
....

While, if the other side was free the entire day, they'd list out all the times, and things would balance as you desire, if they weren't free and you followed the same logic as here, you'd achieve your desired result.

Note, tables are out of style, but you can use tables and rowspans (and some math) to achieve the same thing.

Also, you should avoid using inline styles as I've done in my example, but it was merely for simple example.

Correct Answer Got things to work with your ideas thanx. Some Code below.

Middle Results All 20px #Timeformat(stime, 'H:mm TT')#

Left and Right Results Adjusted Accordingly

    <cfset nb = #DateDiff("n",schstart,actend)#>
    <cfset ht = nb / 15 >
    <cfset newht = ht * 20>
    <cfif (sch.currentRow MOD 2 EQ 0)><cfset bg = "e1e1e1"><cfelse><cfset bg = "d7d7d7"></cfif>
    <cfif ug.username is "available"><cfset cl = "green"><cfelse><cfset cl = "000000"></cfif>

    <div style="position: relative; background-color: #bg#; font-family: Verdana; font-size: 10pt; color: #cl#; height: #newht#px">
    <cfif ug.username is "available">
    <a href="_twin_book.cfm?mid=#masterid#">#ug.username#</a>
    <cfelse>#ug.username#</cfif>
    <cfif len(admnote)><span class=code8><br>#admnote#</span></cfif>
    <cfif actend gt schend>
    <div style="position: absolute; bottom: 0; width: 100%; height: 12px; background-color: pink;">
    </cfif>
    </div>

Sorry - thought if I added the (or) outside of the coding... and had the *lt and *rt - it would alert those looking it is the same code - just one is left and one is right,,, — Merle_the_Pearl, Dec 03 '14 at 09:09

ColdFusion - Time Display - Twin Set of Results - Matching Time Look Each Side

1 Answers1