Coldfusion calendar scheduler complex table output

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
twanlassAsked:
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.

8riaNCommented:
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
RCorfmanCommented:
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

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
twanlassAuthor Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

twanlassAuthor Commented:
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
8riaNCommented:
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
twanlassAuthor Commented:
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
RCorfmanCommented:
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
8riaNCommented:
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
RCorfmanCommented:
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
twanlassAuthor Commented:
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
8riaNCommented:
Whereas I like code solutions so the application is portable to different dbases, 'cause I'm a contractor, not an in-house coder
0
8riaNCommented:
Well, get back to me if you decide to try my solution.

8riaN
0
twanlassAuthor Commented:
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
RCorfmanCommented:
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
8riaNCommented:
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
RCorfmanCommented:
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
8riaNCommented:
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
twanlassAuthor Commented:
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
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
Web Servers

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.