?
Solved

Coldfusion calendar scheduler complex table output

Posted on 2006-03-24
18
Medium Priority
?
696 Views
Last Modified: 2013-12-24
I am developing a scheduling application and am having issues with outputting of a sparse table. On the Y axis (column) are locations (rather rooms to be scheduled) and on the X axis (row) are times (whole times, eg 8:30am, 9:00am, 9:30am etc). Meetings can run for a half hour or 2, 3 hours etc. Simply put, I need to output a row for each location and in certain columns 'block out' that time. Open times should have a cell populated with a link to our scheduling form. A static example of how the table should like (minus the fact that every cell is filled in) can be seen here: http://cfdev.ucsd.edu:81/beta/loop.cfm

I am trying to avoid running logic on each cell to determine if its open or not. That and running multiple nested loops. Response time is a large priority.

What I have seen thus far: structs being used to speed up the data interaction as well as stored procedures (I am using MS SQL Server).

Any ideas guys? Its got me and a few others in the office stumped!

-Tyler
0
Comment
Question by:twanlass
  • 7
  • 6
  • 5
18 Comments
 
LVL 5

Assisted Solution

by:8riaN
8riaN earned 600 total points
ID: 16285653
Let's assume: that you store reservations in a table of the same name, with fields for and ID, description, day, room, schedule slot the reservation starts on, and the number of slots it requires; that you want a three hour reservation to actually span 6 columns; that some fast logic is acceptable on each cell; that you know how to select only the reservations from the day you want or whatever the criteria are.

This could be done with the database storing real time objects, but it would involve more calculation when the reservations page is drawn

<cfquery name="getReservations" datasource="DSN">
      SELECT resID,room,startingSlot,slotsReserved,description FROM reservations WHERE day=today (etc.)
</cfquery>

<cfset totalSlots=19><!--- number of possible start times i.e. data columns --->
<cfset resGrid=StructNew()>
<cfloop query="getReservations">
      <cfif NOT StructKeyExists(resGrid,"#room#"><!--- initialize with link to open slot --->
            <cfset resGrid["#room#"]=ArrayNew(1)>
                  <cfloop from="1" to="#totalSlots#" index="thisSlot">
                        <cfset resGrid["#room#"][#thisSlot#]=StructNew()>
                        <cfset resGrid["#room#"][#thisSlot#]["colspan"]=1>
                        <cfset resGrid["#room#"][#thisSlot#]["contents"]='<a href=""scheduler.cfm?day=#today#&room=#room#&slot=#slot#"">reserve</a>'>
                  </cfloop>
      </cfif>
      <!--- now set this reservation --->
      <cfset resGrid["#room#"][#startingSlot#]["colspan"]=slotsReserved>
      <cfset resGrid["#room#"][#startingSlot#]["contents"]='<a href="displayEvent.cfm?id=#resID#">#description#</a>'>
</cfloop>


<!--- Now the actual display table --->
                  <table width="100%" border="1">
                    <tr>
                      <td width="84">&nbsp;</td>
                        <td class="mainBodyTextSmall">8:00      <strong><span class="am">am</span></strong></td>
                        ...
                        <td class="mainBodyTextSmall">5:00      <strong><span class="pm">pm</span></strong></td>
                    </tr>
<cfoutput>
<cfloop collection="#resGrid#" item="thisRoom">
      <cfset slotArray=evaluate('resGrid["#thisRoom#"])>
                        <tr>
                              <td>#thisRoom#</td>
            <cfset nextSlot=1>
            <cfset loop condition="#nextSlot# LT #totalSlots#">
                  <cfset colspan=slotArray[nextSlot]['colspan']>
                              <td colspan="#colspan#">#slotArray[nextSlot]['contents']#</td>
                  <cfset nextSlot=nextSlot+colspan>
                        </tr>
            </cfloop>
</cfloop>
</cfoutput>
                  </table>


So now you just need a scheduling form that can translate start time and duration into starting slot and number of slots.  Do you need help on that too?
0
 
LVL 16

Accepted Solution

by:
RCorfman earned 600 total points
ID: 16287009
I would drive this all on the query from the database, then just do a grouped output.
I don't know sqlserver (I'm an Oracle guy), but the concept should work the same on both, I just don't know the syntax.

You need to generate a full outer join between the rooms and the timeslots and return that from the database all in one query. the data would be sorted by room,timeslot.  Each record would show if it is an occupied time or not.

At that point output is a straight <cfoutput query = "fullouterjoin" group="room">
                                                   <tr><td>#roomname#</td>
                                              <cfoutput><td><cfif booked>output booked display<cfelse>output free link</cfif></td></cfoutput></tr>

Let me know if this doesn't make sense.
0
 

Author Comment

by:twanlass
ID: 16301597
Thanks for the fast replies. Well, its Monday morning so I will begin cracking on it now. I think the only issue I was having was how do I output this table without running logic on each table cell or without producing quick queries for each room / table row. I will start looking into each proposed method. I'll be back!

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:twanlass
ID: 16302212
RCorfman,

How can I associate the meetings with the times? The code currently only generates one column of meetings for all rooms with no regard to time (obviously we have'nt linked that yet). I have a cfquery doing a full outer join on the tables and data I need. That part is working thus far, but until I can associate time there is no way to show what rooms are free as it only display meetings, not empty blocks.

-Tyler
0
 
LVL 5

Expert Comment

by:8riaN
ID: 16302284
could you post the data structure for the tables?  How are you storing appointments in the database?  What other tables are involved?  RCorfman seems to have assumed that you are storing timeslots in the database, which never occurred to me, I assumed you were storing appointments with a room, day, start time and duration.  What are you actually doing?
0
 

Author Comment

by:twanlass
ID: 16302374
One issue is that I can't really modify the DB as I am creating a rather large band aid for a 3rd party product running off of thier DB. The two main tables I am concerned with are Reservations and Rooms. We have a Room_ID, Location_ID (what building that room is located in), Meeting_Title, Meeting_Start, and Meeting_End (both of which are just date times). Thus far, when selecting meetings I have been using 'BETWEEN' to select meetings for a certain day based on a selected location ID. As for the time markers, or when meetings can happen, I simply have a table called TIME that has values from 8am -5pm.

Let me know if more info is needed. Thanks guys!
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16302634
It sounded like you were going to show free slots and booked slots. I'm not sure how you can do that without storing the data in the database. I was assuming that this was the case.  If not, then how/where would you know a slot is used vs. free?
0
 
LVL 5

Expert Comment

by:8riaN
ID: 16302660
To use my code with that data, you'd need to create a date/time object for the first 30 minute slot of the day called FirstSlot, then add the following right under this comment:

     <!--- now set this reservation --->
     <cfset startingSlot=1+INT(DateDiff('n',FirstSlot,Meeting_Start)/30)>
     <cfset slotsReserved=INT(DateDiff('n',Meeting_Start,Meeting_End)/30>
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16302685
I don't know how to write the queries in sql server. I have nothing against sql server, just isn't used in my shop (my company has it, but not the group I support).  In Oracle, we can generate rows starting with a time for any time increment and number of slots desired using some sql 'tricks'. I would full outer join that with the rooms, then use the reservations table to indicate if a slot is taken or not. I prefer database solutions when possible, but sounds like it isn't in this case.
0
 

Author Comment

by:twanlass
ID: 16302759
RCorfman,

that is the goal, to show open and booked slots. In the 'Reservations' table I have times and locations when meetings will occur. But as the code stands, it only ouputs when meetings are booked (very simple), however it does not show 'open blocks' that can be clicked on and reserved.
0
 
LVL 5

Expert Comment

by:8riaN
ID: 16302770
Whereas I like code solutions so the application is portable to different dbases, 'cause I'm a contractor, not an in-house coder
0
 
LVL 5

Expert Comment

by:8riaN
ID: 16302861
Well, get back to me if you decide to try my solution.

8riaN
0
 

Author Comment

by:twanlass
ID: 16302884
I appreciate your input. I had put off really working with it if there was a solution w/ a little less code. As far as being portable, I would agree, however this is just a band aid and will sit on the same server archictecture for years to come :)
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16303140
SQL> desc udttimes
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------
 SLOT                                                           DATE

SQL> desc udtrooms
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------
 ID                                                             NUMBER
 DESCRIPTION                                                    VARCHAR2(30)

SQL> desc udtreserve
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------
 ID                                                             NUMBER
 ROOM                                                           NUMBER
 START_TIME                                                     DATE
 END_TIME                                                       DATE

SQL> l
  1  select t.slot,t.description,t.id,r.start_time,r.end_time,r.id
  2  from (select slot,id,description from udttimes,udtrooms) t
  3   left outer join udtreserve r on
  4*  r.room=t.id and slot between start_time and end_time
SQL>

The above works, but you need a table with the time slots.  It gives you a list of all the rooms,times available, and start_time/end_time is filled in if there is already a reservation on that slot.

Note that as I mentioned, in Oracle I know how to generate a  'time table' on the fly.  in this case, I populated udttimes with date/times in 15 minute increments.  I build a physical table to hold these.
0
 
LVL 5

Expert Comment

by:8riaN
ID: 16303243
Either my browser, or this site has now dumped 2 of my posts into the bit-bucket, giong to have to quit forgetting to save them to the clipboard before I submit...

Anyway, the point of the last trashed post was that the logic of what you want to do is inherently complex, so either you do it in the database, or the application, it's not going to happen automatically.  I'd say if it's a band-aid, let the db alone and just apply the fix externally.  More to the point, this is the CF forum, you should move this to a DB forum if you want a SQL Server solution.

In support of my solution, it will look really good if you want to display any details about the meetings, for example their names, because the meetings span the appropriate number of columns and can easily be made into links for the detail - if that's appropriate.
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16303701
Wow, I guess i figured that some problems need to be looked at multiple ways. Usually there are mutiple answers. One way to solve this problem is too look at the query, doesn't mean it is the right answer, and it doesn't mean it isn't a coldfusion question.  I was just asserting that one way to solve it is with the query.

8riaN I'd be surprised if you never found that sometimes a correct coldfusion answer revolves around a good <cfquery> statement.  Anyway, I do agree that if a database solution is correct, you could look to help in writing the query from the sqlserver folks.  I too would not have touched the database if the backend was Oracle because I know how to write and Oracle-specific query to solve this problem. There may well be an sqlserver method that the folks in that TA could help you with to do the same without a database change, just a different <cfquery>.

I also agree, that using code is also a good solution.  Usually there is more than one reasonable answer, especially for a complex problem.  I also agree that if you want to group blocks of cells together spanning a time period, the code method could solve this easier than the database method.

The only reason I showed the query I did is because it is pretty generic and would work with almost any database... but it would required a table that was populated with all the theoretical time slots.
0
 
LVL 5

Expert Comment

by:8riaN
ID: 16304237
All excellent general points which I agree with completely.  Far from claiming that there is any strict right or wrong answer either to this question or in general, I simply think that in this particular case, template code is the best route to the best result that I can think of to offer.  There is most likely an intermediate solution that does some more of the work in the SQL, but I don't know what it is, so I can't advocate it :)

The point about moving to a different forum is that the query logic is complex enough to merit calling in the big-gun specialists - I'm just not good enough at it in any db system, and you're an expert in the wrong system, so...

I do hope I didn't come off as brusque or dismissive in my previous post, I worded the one the browser discarded much more carefully and I was a little ticked at having to type it again.
0
 

Author Comment

by:twanlass
ID: 16306055
Guys, you both did great. Really, thank you. I ended up going with something somewhere in between. I used a beefier SQL / CFQUERY statement to get data from both of my tables and then implemented some more application side logic to use that colspan trick to ensure times were blocked off from start to finish.

That being said I will be splitting the points between the both of you. Thank you again!

-Tyler
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
Suggested Courses

850 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