mentecky
Posts: 321
|
| Posted: 06/02/2008, 6:54 PM |
|
This tip is a continuation of a solution I presented in another thread: http://forums.codecharge.com/posts.php?post_id=95561
I suggest reading that thread first because it defines the tables and strategy for making one single event record repeat. That method works fine if you are querying a single date, but how do you list events that occur between two dates? I recently added this function to my community web site at http://www.meetwny.com/chat_month_view.php?chat_roomsDate=2008-05
What you see there is the result of only 7 records. What we need to do is build a query that spans all dates displayed in the calendar.
OK, first create a page and add a Calendar using the builder. On the data source page open Query Builder. Select your table that holds the recurring events, in my case chat_rooms. In the top left change the type to SQL.
Now we have to build a SQL statement that will return a dataset in the format we want. What we are doing differently here is we won't be specifying any parameters. We will deal with those later in the code. My SQL statement looks like this:
SELECT chat_rooms.*, chat_types.chat_type AS chat_types_desc, e.chat_date as chat_date
FROM chat_rooms INNER JOIN chat_types ON
chat_rooms.chat_type = chat_types.chat_type_id,
(SELECT chat_date FROM
(
SELECT DATE('2008-01-01') as chat_date
UNION
SELECT DATE('2008-01-02') as chat_date
UNION
SELECT DATE('2008-01-03') as chat_date
UNION
SELECT DATE('2008-01-04') as chat_date
) d
) e
WHERE chat_rooms.chat_start_date <= e.chat_date
AND chat_rooms.chat_end_date >= e.chat_date
AND ((( chat_types.chat_type_id = 11
OR chat_types.chat_type_id = 10 ))
OR ( chat_types.chat_type_id = 9
AND ( 1 = DAYOFWEEK(e.chat_date)
OR 7 = DAYOFWEEK(e.chat_date) ))
OR ( chat_types.chat_type_id = 8
AND 1 <> DAYOFWEEK(e.chat_date)
AND 7 <> DAYOFWEEK(e.chat_date) )
OR chat_types.chat_type_id = DAYOFWEEK(e.chat_date) )
ORDER BY chat_date, chat_start
If you got my first example running you will recognize the query with a few differences. The SELECT statements with the union returns a table, aliased as "e", of dates. Also, in the where clause anywhere we tested for a date the parameter has been replaced with e.chat_date. Everything else is the same as the previous project. So, you can use the query that works for your implementation as a basis. When you get this right you should be able to click the "Show Data" button on the tool bar and get a resulting table and no errors.
Save your query and finish the builder as you normally would. Remember, chat_date will be your date value for the builder and start_time will be the time. Also, select Time stored in separate field.
Now the FUN part! Click your Calendar Control and in events add code to Before Execute Select. The following is the code I added:
// Retrieve the start and end dates for the calendar
$first_dt = $chat_rooms->StartDate;
$last_dt = $chat_rooms->EndDate;
$first_ts = $first_dt[ccsTimestamp];
$last_ts = $last_dt[ccsTimestamp];
// Convert to MySQL dates
$this_date = date("Y-m-d", $first_ts);
$last_date = date("Y-m-d", $last_ts);
// We're building a totally new SQL statement here
// NOTE: The extra spaces at the end of lines are important!
$SQL = "SELECT chat_rooms.*, chat_types.chat_type AS chat_types_desc, e.chat_date as chat_date ";
$SQL .= "FROM chat_rooms INNER JOIN chat_types ON ";
$SQL .= "chat_rooms.chat_type = chat_types.chat_type_id, ";
$SQL .= "(SELECT chat_date FROM ";
$SQL .= " (";
// Start dayoffset at 0
$dayoffset = 0;
// Here's the creative part. We're building a bunch of SELECT statements
// that will return a list of dates for each day on our calendar.
// The resulting SQL will look like this:
//
// SELECT DATE('2008-01-01') as chat_date
// UNION
// SELECT DATE('2008-01-02') as chat_date
// UNION
// .
// .
// .
// SELECT DATE('2008-01-31') as chat_date
//
// The resulting table is aliased as "e" for use in the WHERE clause
while (strtotime($this_date) <= strtotime($last_date))
{
$SQL .= " SELECT DATE('$this_date') as chat_date ";
if (strtotime($this_date) < strtotime($last_date))
{
$SQL .= " UNION ";
}
// Increment the dayoffset
$dayoffset++;
$this_date = date("Y-m-d", mktime ($first_dt[ccsHour], $first_dt[ccsMinute], $first_dt[ccsSecond], $first_dt[ccsMonth], $first_dt[ccsDay] + $dayoffset, $first_dt[ccsYear]));
}
$SQL .= " ) d) e ";
// Add the WHERE clause. This is similar to the previous Recurring Date code
// except we are using "e.chat_date" for all the date tests
$SQL .= "WHERE chat_rooms.chat_start_date <= e.chat_date ";
$SQL .= "AND chat_rooms.chat_end_date >= e.chat_date ";
$SQL .= "AND ((( chat_types.chat_type_id = 11 ";
$SQL .= "OR chat_types.chat_type_id = 10 )) ";
$SQL .= "OR ( chat_types.chat_type_id = 9 ";
$SQL .= "AND ( 1 = DAYOFWEEK(e.chat_date) ";
$SQL .= "OR 7 = DAYOFWEEK(e.chat_date) )) ";
$SQL .= "OR ( chat_types.chat_type_id = 8 ";
$SQL .= "AND 1 <> DAYOFWEEK(e.chat_date) ";
$SQL .= "AND 7 <> DAYOFWEEK(e.chat_date) ) ";
$SQL .= "OR chat_types.chat_type_id = DAYOFWEEK(e.chat_date) ) ";
$SQL .= "ORDER BY chat_date, chat_start";
// Set the new SQL statement
$chat_rooms->DataSource->SQL = $SQL;
// This probably isn't necessary but it makes sense. Clear our old ORDER BY
$chat_rooms->DataSource->Order = "";
That code builds the exact same SQL statement that we put in the Calendar Builder except it builds the "e" table based on the begin and end dates of the Calendar Control.
That's about it, format your Calendar as needed. I did have to add formats to the date and time labels, but after that it works great.
I hope this helps!
Rick
_________________
http://www.ccselite.com |
