John is the CIO of DrMag.com. He can be reached at [email protected].
At DrMag.com (an Internet-based resource center for magazines that lets you search for and subscribe to nearly 2000 publications), we have reduced printing costs and increased communication by developing our intranet as a replacement for distributing information by paper. More information gets distributed this way, and it becomes available to every employee instantly, which is especially helpful in an entrepreneurial firm like ours. Our intranet primarily uses VBScripts in Active Server Pages (ASP), with data stored in Microsoft SQL 7 databases -- the same technology we use with the public DrMag.com site.
We put off creating intranet calendar-based reports for day-to-day analysis because creating such a web page was a little less than intuitive. Once you have the structure for one such report, however, practically anything can be output with only simple modifications to the query. There are numerous issues that I'll discuss here for putting SQL results in a web calendar, but it boils down to three main tasks:
- Structuring a SQL query so that the results can easily be assigned to a calendar's cells.
- Writing scripts to dynamically create a calendar.
- Inserting the SQL results into the calendar.
In addition to presenting the exact VBScripts I use, I'll discuss enough of the logic to make it straightforward for you to port it to Java, Perl, Cold Fusion, or whatever language you prefer.
The Query
First, the query will need to report the month, the day of the month, and the day of the week, for each piece of information to be displayed in the calendar. If you want multiple years on one web page, you have to return the year as well. At least one table in each query must have a date field, and in most cases, it should not contain nulls. SQL calculates these columns by the DATEPART function for T-SQL (Microsoft and Sybase), and by TO_CHAR and Format for Oracle. DB2 uses three separate commands. Listing One covers some of the syntax for these functions, but for full syntax or other flavors of SQL, check your documentation. (By the way, IBM provides free access to over 400 DB2 books online, including its SQL reference, at http://www.software.ibm.com/ data/ db2/udb/library.html. Thanks, IBM.)
Listing Two is an example of a useful query and Example 1 shows a few rows of its results. By the way, if you're working with both SQL and web-page scripting, get used to seeing overlapping terms for database objects: Columns and fields mean the same thing, and so do rows and records.
The UnitsSold table has a date column (RecDate) and information such as keys to other tables to identify which item was purchased, who the sales person was, who purchased it, and so on, and it has one record for each unit sold. This product group has many units sold each day, so there will be many records with the same day in the date column. In the select list, three of the four columns returned deal with the date, and only one is the data we actually want to present in our calendar. This is provided by the aggregate COUNT function on the OrderID column. Since there is one record for each sale, a count of the records for each day returns the total number of units sold for each day (if any).
The @Year is a T-SQL variable in my stored procedure, named sp_UnitsSold. If you're using a SQL database, as opposed to something like Microsoft Access, be sure to put your query in a stored procedure and have the web-page script pass the year as an input variable. (See Listing Three for a VBScript call to a stored procedure with a passed variable.) The performance boost is well worth it. Otherwise, you will need to replace the SQL variable with a VBScript variable; see Example 2 for an instance of that type.
If you have records with more than one year in the date column, you must include the WHERE clause to restrict the results set to show records from only one year, or add the year to the select list, group by, and order by statements to show records from multiple years.
In the Example 1 results, the first record shown is for Friday, since that is the sixth day of the week. This may vary, depending on your SQL server's configuration.
The Calendar
STDCAL.ASP (available electronically; see "Resource Center," page 5) is code for a web page that displays a standard calendar. You can use this as a reference for the HTML/VBScript that creates the calendar without being cluttered with presenting query results. This HTML table creates eight columns, a column on the left to hold the name of the month, and one for each day of the week. A live example of this page is available at http://www .drmag.com/calendar.asp.
The HTML form posts back to itself so that the person viewing it can change the year displayed. Scripting languages have their own valid ranges of years, and your SQL may have a different range from your scripting language. The reason this code checks both request.querystring and request.form is that request.querystring checks for the parameters in the URL, and request.form looks for them in the HTTP header, where they will be if the POST method of an HTML form has been used. Incidentally, if you'd like an easy way to see the entire HTTP header, you can print it to a web page using <%=request.servervariables(ALL_RAW)%>.
The basic logic is to determine the year and month the code will work with next, determine the day of the week that month's first day falls on, determine the number of weeks in that month, and use variables to keep up with the values as they change. You also have to calculate the length of the month by setting a date variable to the first day of the next month and subtracting one day. (An IF/ELSE statement handles December differently. And yes, you use the DateAdd function to subtract a day. Present the name of the month if it's the first line for the month, and if the first day of the month is not on Sunday, fill in blank cells up to the first day, with a SELECT CASE. Then fill in the days of the month (1-31) for the first week ending on Saturday. Start the next row and fill in the days of the month until your day counter equals the last day of the month. If the last day is not Saturday, fill in blank cells for the rest of the week. Repeat for the next month while any months remain.
The main HMTL table has one fixed row of column headers, and then we have our first VBScript loop, in this case a FOR/NEXT, since it's going to present exactly 12 months. Use a DO LOOP if you want a conditional number of months presented. A month counter variable (mc) identifies the month about to be processed.
To track the number of weeks in a particular month, set the variable NumWeeks to 5 for the default, and use an IF statement to change it to 6 if the beginning weekday of the month (bwdom) is 6 and the last day of the month (ldom) is 31, or if the beginning weekday of the month is 7 and the last day of the month is equal to or greater than 30. An additional IF checks to see if the month is February, not a leap year, and the first day of the month is Sunday, the only case in which NumWeeks is set to 4.
The Final Form
Listing Three takes the HTML/VBScript of STDCAL.ASP, and with a few modifications, inserts an ODBC connection, query, and presentation of the query results. Once you have the query returning results correctly, and the HTML table presenting the calendar correctly, it's fairly easy to look at Listing Four and see where to plug in the query and its results.
Unlike STDCAL.ASP, this code uses a DO UNTIL to make the presentation of months conditional. If no records are returned for some months, those months won't be displayed.
The biggest difference is the addition of the DIM D(31) statement, which creates a variable array. Unless declared otherwise, VBScript variables are the variant datatype. This array gets its values assigned with an initialization loop inside the month loop. Then, as you loop through the HTML table to create a cell and put the day of the month in it, the day of the month variable corresponds to the array variable. For example, the third day of the month will have its SQL results stored in D(3). When the code creates a day's cell, you just print the value inside it with <%=D(DayCounter)%>.
If you need to troubleshoot, you can break long scripts and output variable contents as HTML, as in Example 2. This technique is especially useful when you're combining text strings with variables, to make sure your concatenation and other syntax is coming out correctly. Of course, run your query with your normal query tool first, to make sure it outputs the records correctly.
Single-Month View Variation
A seemingly infinite number of variations can be made for the HMTL table layout and the time period covered by it, like two months side-by-side, or your company's current fiscal quarter. Most useful if you have a large amount of data to display for each day, such as text as opposed to amounts, is a single-month or single-week web page that will have much larger cells for each day (for an example, see http://www.drmag.com/ month.asp).
Listing Four is a stored procedure you could use with a single month, so you can compare it with Listing Two's query for an entire calendar year. Example 3 shows sample results. MONTH.ASP (available electronically) is HTML code for a single month view, with a query and its results inserted. The query in this example should be a stored procedure if your database provides that functionality, but I've done it this way to show users of Access and other small DBs an example of making a direct query, without a stored procedure. (By the way, if you are using Access in particular, don't expect it to handle more than a few simultaneous connections without slowing to a crawl. Consider upgrading to Microsoft SQL7, which can handle multiple simultaneous connections much faster than Access can handle one.)
The main differences from Listing Three are tracking the month variable the same way the year variable is tracked, and modification of the query to return data for only a single month. Also, in the calendar's cell where the day of the month and query results are displayed, I prefer to use a nested HTML table to maximize layout control, while retaining the broadest possible browser compatibility.
To improve the usability, at the bottom of the month display, I use hyperlinks to page to the previous and next months. The hyperlink passes the month and year variables in the URL instead of the HTTP header. If the month displayed is February through November, just increment or decrement the month. For January, the Previous link has to decrement the year and set the month to December, and for December's Next link, the year has to be incremented, and the month set to January. An IF/ELSEIF/ELSE statement handles this. Below that is an HTML form with a SELECT for the month and an input text box for the year, so users can jump to any month and year.
Last, you'll notice that the HTML SELECT for the month is over 60 lines of code because of the IF/ELSE statements used for each month. This is only necessary if you want the month displayed to be the default selection. You can shorten your code if you skip choosing the default month in this select, but it won't speed it up much. Other variations include:
- You can make a clock web page for reports segmented into hours, minutes, or even seconds. Just check your SQL documentation for the syntax on the DATEPART functions.
- You can vary the query, and therefore the results, by login ID for pages that are password protected. VBScript provides access to the HTTP header information through Server Variables, and the headers include user IDs when a password scheme is used. For example, <%IF INSTR(Request.servervariables(''LOGON_USE''), ''drdobb'') THEN ... %> is one way to start the logic. Use an INSTR function if the web server is on Windows NT, because the LOGON _USER variable may include the domain name, depending on whether the user logs into the web page from inside or outside the domain LAN. With VB and VBScripting, INSTR doesn't require an operator such as "> 1" because if there is no hit, it returns zero, which equates to False, and any hit at all returns an integer of one or more, and equates to True.
- You can add a column on the right to display totals for the week by several methods. Constructing a single query to return daily and weekly totals required a fairly complex query (and I thank the friends who helped), and gave the slowest overall performance. I haven't found a SQL language yet with a week-of-month function to make such a query easier. A second method is to run a separate query for the weekly totals, but the best performance in my tests was simply using a VBScript variable to count each day's value, display it after Saturday was counted, and reset it to zero before counting the next week.
Presenting Anniversaries and Holidays
Think twice before adding holidays to web-based calendars if you intend for it to have international appeal. Political holidays vary by nation, and religious holidays are never appreciated by everyone. If you want to present holidays on a limited-culture intranet, however, here are some examples that will help you figure out how to present any holiday.
The easiest feature to present is an anniversary or holiday that always occurs on the same day of the same month, such as New Year's Day (for the Julian calendar), which always falls on January 1. In the loop where the number of the day is presented, simply use an IF statement to check to see if the month being presented is equal to 1, and if the day counter is equal to 1. If so, then add your holiday text label or graphic before the END IF, like <%IF wMonth = 1 AND DayCounter = 1 THEN%> <TD><FONT size = 2>New Year's Day</FONT><%END IF%>.
Slightly more complex is a holiday such as Thanksgiving Day, which is always the fourth Thursday in November (in America). Because the month can begin on any one of seven days of the week, it is possible for the fourth Thursday to be in the fourth or fifth week (when the month begins on Friday or Saturday). In the IF statement, add AND DayOfWeekCounter = 5 to include only Thursdays, and change the DayCounter to check a range, with DayCounter > 21 AND DayCounter < 29. If you're looking for the first fourth- particular day of the week (?day), such as the fourth Thursday, follow these guidelines:
- First ?day of the month, change IF statement to DayCounter < 8.
- Second ?day of the month, change to DayCounter > 7 AND DayCounter <15.
- Third ?day of the month, change to DayCounter > 14 AND DayCounter <22.
- Fourth ?day of the month, change to DayCounter > 21 AND DayCounter <29.
The rule is a little different if you want the last ?day of the month, such as Memorial Day being the last Monday in May (in America). It isn't the same as the fourth ?day of the month, because months vary in length. Here's the rule for this condition for every month except February, depending on how many days the month has:
- 31 days, use DayCounter > 24.
- 30 days, use DayCounter > 23.
If February, because of periodic leap years, use the last-day-of-month variable (ldom) in Example 4.
Holidays based on lunar schedules are the most complex, and you'll have to use an algorithm to relate the lunar schedule to the Julian calendar. For example, here is how I handle Easter (Christian), which is the first Sunday following the "Paschal Full Moon" and can occur in March or April (based on Carter's algorithm, see http://www.ast.cam.ac.uk/pubinfo/leaflets/easter/easter.html). I have checked its accuracy against posted dates for Easter for a number of years past, and it always agreed. Right after initializing DayCounter and WeekCounter, before the loop that creates the cells for each week, I initialize two new variables, eMonth and eDay, to identify the month and day of the month for Easter in a given year; see Listing Five.
Then, inside the loop where all the other anniversary and holiday IF statements appear, I add <%IF wYear > 1699 AND wYear < 2200 AND abs(wMonth) = eMonth AND DayCounter = eDay THEN %>Easter <%END IF%>. I used the absolute function (abs) with wMonth because the variant datatype wouldn't match with eMonth otherwise, even though DayCounter will match eDay without it. A bug, or a feature? At any rate, you can see lunar-based special days can be difficult to implement in the Julian calendar. If you have to do much of this, show this sample to your boss and ask for a raise! Otherwise, have fun making web calendars, and remember that next year is always better.
DDJ
Listing One
Return an integer representing a specific part of a date: (a) Transact SQL (Microsoft and Sybase) DATEPART (datepart, date); (b) Oracle. TO_CHAR(date, 'datepart') FORMAT; (c) DB2. (a) <pre>Parameter Result Type Digits Returned yyyy year 1753-9999 mm month 1-12 dd day of month 1-31 dw day of week 1-7 (Sun.-Sat.) (b) <pre>Parameter Result Type Digits Returned YYYY Year 1-9999 MM month 01-12 DD day of month 01-31 D day of week 01-07 (c) <pre>YEAR(date) for year as 1-9999 MONTH(date) for month as 1-12 DAY(date) for day of month as 1-31 DAYOFWEEK(date) for day of week as 1-7
Listing Two
CREATE PROCEDURE sp_UnitsSold @Year int AS
SELECT DATEPART(mm, RecDate) AS Month, DATEPART(dd, RecDate) AS DayOfMonth,
DATEPART(dw, RecDate) AS WkDay, COUNT(OrderID) AS UnitsSold
FROM UnitsSold
WHERE DATEPART(yyyy, RecDate) = @Year
GROUP BY DATEPART(mm, RecDate), DATEPART(dd, RecDate), DATEPART(dw, RecDate)
ORDER BY DATEPART(mm, RecDate), DATEPART(dd, RecDate)
Listing Three
<%@ LANGUAGE=VBScript %>
<%wYear=Request.QueryString("wYear")
IF wYear = "" THEN wYear=Request.Form("wYear")
IF wYear = "" OR wYear < 1753 OR wYear > 9999 THEN wYear =
DATEPART("yyyy",now()) %>
<html><HEAD><TITLE>Calendar Report of Units Sold</TITLE></head>
<BODY><CENTER>
<form action="calendarunitsales.asp" method="post">
Enter a year for report:<br>
<input size=6 maxlength=4 name=wYear><br>
<input type="submit" name="Change" VALUE="Change">
</form><BR>
<H2><%=wYear%> Unit Sales</H2><P>
<%
dim D(31)
Set conn=server.createobject("ADODB.connection")
conn.Open "DATABASE=[database name];DSN=[DSN Name];
UID=[login];Password=[password];"
%>
<TABLE ALIGN=center WIDTH=30% BORDER=1 CELLSPACING=1 CELLPADDING=2>
<TR>
<TD ALIGN=middle></TD>
<TD ALIGN=middle><STRONG>Sun.</STRONG></TD>
<TD ALIGN=middle><STRONG>Mon.</STRONG></TD>
<TD ALIGN=middle><STRONG>Tue.</STRONG></TD>
<TD ALIGN=middle><STRONG>Wed.</STRONG></TD>
<TD ALIGN=middle><STRONG>Thu.</STRONG></TD>
<TD ALIGN=middle><STRONG>Fri.</STRONG></TD>
<TD ALIGN=middle><STRONG>Sat.</STRONG></TD>
</TR>
<%
SET rs1=conn.Execute("EXECUTE sp_UnitsSold " & wYear)
DO UNTIL rs1.EOF 'CONDITIONAL MONTH LOOP
mc = rs1("Month")%>
<TR>
<TD ALIGN=middle><STRONG><%=MonthName(lc)%></STRONG></TD>
<% 'Determine day of the week the month begins on
tempdate = mc & "/1/" & wYear
bwdom = datepart("w", tempdate)
DayOfWeekCounter = 0 'This "week" has 8 "days" to
' include the Name of the Month column.
'PRINT LEADING BLANK DAYS
SELECT CASE bwdom
CASE 1
DayOfWeekCounter = 1
CASE 2 %>
<TD></TD><%DayOfWeekCounter = 2
CASE 3 %>
<TD></TD><TD></TD><%DayOfWeekCounter = 3
CASE 4 %>
<TD></TD><TD></TD><TD></TD><%DayOfWeekCounter = 4
CASE 5 %>
<TD></TD><TD></TD><TD></TD><TD></TD><%DayOfWeekCounter = 5
CASE 6 %>
<TD></TD><TD></TD><TD></TD><TD></TD>
<TD></TD><%DayOfWeekCounter = 6
CASE 7 %>
<TD></TD><TD></TD><TD></TD><TD></TD><TD></TD>
<TD></TD><%DayOfWeekCounter = 7
CASE ELSE %>
<TD>Beginning Day of Week Error</TD><%
END SELECT
'Determine last day of month & number of weeks
ldom = Day(DateAdd("d", -1, mc + 1 & "/1/" & tempyear))
NumWeeks = 5
IF (bwdom = 6 AND ldom = 31) OR
(bwdom = 7 AND ldom > 29) THEN NumWeeks = 6
'INITIALIZE DAY ARRAY
lc = 0
FOR lc = 1 to 31
D(lc) = " "
NEXT
'loop through records for the month & assign to D array.
DO WHILE rs1("Month") = mc 'DAY ASSIGNMENT LOOP
TempD = rs1("DayOfMonth")
D(TempD) = rs1("UnitsSold")
IF NOT rs1.eof THEN rs1.movenext
IF rs1.eof then exit do
LOOP
DayCounter = 1
WeekCounter = 1
DO WHILE WeekCounter < NumWeeks + 1
DO WHILE DayOfWeekCounter < 8
IF DayCounter < ldom + 1 THEN %>
<TD ALIGN=middle><SUP><FONT size=-2><%=DayCounter%>
</FONT></SUP> <FONT color="ff0000">
<STRONG><%=D(DayCounter)%></STRONG></FONT></TD>
<%ELSE%>
<TD ALIGN=middle></TD>
<%END IF
DayOfWeekCounter = DayOfWeekCounter + 1
DayCounter = DayCounter + 1
LOOP
DayOfWeekCounter = 1
WeekCounter = WeekCounter + 1 %>
</TR><TR><TD></TD>
<%LOOP
LOOP
rs1.Close
conn.Close%>
</TABLE><P>
</CENTER></BODY></HTML>
Listing Four
CREATE PROCEDURE sp_SalesRecords @Year int, @Month int AS
SELECT DATEPART(dd, RecDate) AS DayOfMonth, DATEPART(dw, RecDate) AS WkDay,
DaysTopCustomer
FROM SalesRecords
WHERE DATEPART(yyyy, RecDate) = @Year
AND DATEPART(mm, RecDate) = @Month
GROUP BY DATEPART(dd, RecDate), DATEPART(dw, RecDate)
ORDER BY DATEPART(dd, RecDate)
Listing Five
<% IF wMonth = 3 OR wMonth = 4 THEN 'Begin Easter calculation
eMonth = 0: eDay = 0: v1 = 0: v2 = 0: v3 = 0: v4 = 0:
v5 = 0: v6 = 0: v7 = 0
IF wYear > 1699 AND wYear < 1800 THEN v6 = 23: v7=3
IF wYear > 1799 AND wYear < 1900 THEN v6 = 23: v7=4
IF wYear > 1899 AND wYear < 2100 THEN v6 = 24: v7=5
IF wYear > 2099 AND wYear < 2200 THEN v6 = 24: v7=6
v1 = wYear MOD 19: v2 = wYear MOD 4: v3 = wYear MOD 7
v4 = ((19*v1)+v6) MOD 30
v5 = ((2*v2)+(4*v3)+(6*v4)+v7) MOD 7
eDay = (22+v4+v5): eMonth = 3
IF eDay > 31 THEN
eDay = (v4+v5-9): eMonth = 4
IF eDay > 24 THEN
IF eDay = 26 THEN eDay = 19
IF eDay = 25 AND v4 = 28 AND v1 > 10 THEN eDay = 18
END IF
END IF
END IF 'End Easter calculation %>
Copyright © 1999, Dr. Dobb's Journal
