#!/usr/bin/perl -w

# $Id: sched.pl,v 1.120 2013/08/21 15:16:14 swick Exp $

# Author: R. Swick
# Date: 2002-06-14

# Copyright 2002 World Wide Web Consortium, (Massachusetts Institute
# of Technology, Institut National de Recherche en Informatique et en
# Automatique, Keio University).

# All Rights Reserved. http://www.w3.org/Consortium/Legal/

# This W3C work (including software, documents, or other related
# items) is being provided by the copyright holders under the
# following license. By obtaining, using and/or copying this work, you
# (the licensee) agree that you have read, understood, and will comply
# with the following terms and conditions:

# Permission to use, copy, modify, and distribute this software and its
# documentation, with or without modification, for any purpose and
# without fee or royalty is hereby granted, provided that you include
# the following on ALL copies of the software and documentation or
# portions thereof, including modifications, that you make:

# 1. The full text of this NOTICE in a location viewable to users of
#    the redistributed or derivative work.

# 2. Any pre-existing intellectual property disclaimers, notices, or
#    terms and conditions.

# 3. Notice of any changes or modifications to the W3C files,
#    including the date changes were made. (We recommend you provide
#    URIs to the location from which the code is derived.)

# THIS SOFTWARE AND DOCUMENTATION IS PROVIDED "AS IS," AND COPYRIGHT
# HOLDERS MAKE NO REPRESENTATIONS OR WARRANTIES, EXPRESS OR IMPLIED,
# INCLUDING BUT NOT LIMITED TO, WARRANTIES OF MERCHANTABILITY OR
# FITNESS FOR ANY PARTICULAR PURPOSE OR THAT THE USE OF THE SOFTWARE
# OR DOCUMENTATION WILL NOT INFRINGE ANY THIRD PARTY PATENTS,
# COPYRIGHTS, TRADEMARKS OR OTHER RIGHTS.

# COPYRIGHT HOLDERS WILL NOT BE LIABLE FOR ANY DIRECT, INDIRECT,
# SPECIAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF ANY USE OF THE
# SOFTWARE OR DOCUMENTATION.

# The name and trademarks of copyright holders may NOT be used in
# advertising or publicity pertaining to the software without
# specific, written prior permission. Title to copyright in this
# software and any associated documentation will at all times remain
# with copyright holders.

# To Do:
#
# 1. Report timezone of reservation

use DBI;
use File::Basename;
use Getopt::Long;
use POSIX qw(difftime strftime);
use Time::Local;

my $debug;
my $Data_path;			# path to raw database files
my $DisplayInstructions = 0;	# (boolean) include instructions in HTML
my $ForDuration = 2;		# compute calendars for this many months
my $ForMonth = undef;		# start calendar at this month
my $FrontFile = undef;		# file to be inserted in front of the calendar
my $hidden_codes = '';		# regex matched to conference name
my $cal_output = undef;		# path for XHTML output or undef for none
my $PasscodeReuseWindow = 30 * 60; # seconds between telecons w/same passcode
my $RDF_output = '/dev/null';	# path for RDF output
my $text_output = undef;	# path for text output or undef for none

my %Bridge = ( '00000001' => 'http://www.w3.org/2002/06/bridge#Zakim',
	       '00000002' => 'http://www.w3.org/2002/06/bridge#Longfellow',
	       '00000003' => 'http://www.w3.org/2002/06/bridge#Tobin',
	       '00000004' => 'http://www.w3.org/2002/06/bridge#Mystic',
	       '00000005' => 'http://www.w3.org/2002/06/bridge#MIT7910',
	       '00000006' => 'http://www.w3.org/2002/06/bridge#RemoteLink3003',
	       '00000007' => 'http://www.w3.org/2002/06/bridge#RemoteLink4004',
	       '00000008' => 'http://www.w3.org/2002/06/bridge#MIT5208',
	     );

my %BridgeTel = ( 'Zakim' => '+1.617.761.6200',
		  'Longfellow' => '+1.617.252.1038',
		  'Tobin' => '+1.617.252.7000',
		  'Mystic' => '+1.617.252.1859',
		  'MIT7910' => '+1.617.258.7910',
		  'RemoteLink3003' => '+1.630.536.3003',
		  'RemoteLink4004' => '+1.630.536.3003',
		  'MIT5208' => '+1.617.452.5208',
		);

my @MonthName = (January, February, March, April, May, June, July, August,
		 September, October, November, December);

my @Weekday = (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday);

my $CVSRevision = ' $Revision: 1.120 $'; # capture CVS info here
$CVSRevision =~ s/\$//g;	# drop '$' for W3C site repository checkin

my $Contact = {};

my $Conference = {};

my $Timezone = {};

my %PasscodeLastUsedBy;		# {confCode} -> conf_ctl_id

# Returns a copy of the input string with any characters special to XML escaped
sub xml_escape ($) {
    my $string=shift;
    $string =~ s/\&/\&amp;/g;
    $string =~ s/</\&lt;/g;
    $string =~ s/>/\&gt;/g;
    $string =~ s/[\x00-\x1F]//g;  # remove ASCII 0-31
    $string =~ s/([\x80-\xFF])/"\&#".ord($1).";"/ge; # escape ASCII 128-255
    $string;
}

{
    my $usage="usage: ".basename($0,".pl")." [-cal path] [-db path] [-debug] [-duration nmonths] [-for month] [-front path] [-hide regex] [-instructions] [-rdf path] [-text path]\n";

    print '$Id: sched.pl,v 1.120 2013/08/21 15:16:14 swick Exp $',"\n"
	if $debug;

    die $usage unless GetOptions (
	'cal=s'		=> \$cal_output,
	'db=s'		=> \$Data_path,
	'debug!'	=> \$debug,
	'duration=s'	=> \$ForDuration,
	'for=s'		=> \$ForMonth,
	'front=s'	=> \$FrontFile,
	'hide=s'	=> \$hidden_codes,
	'instructions!'	=> \$DisplayInstructions,
	'rdf=s'		=> \$RDF_output,
	'text=s'	=> \$text_output,
	) && @ARGV<3;

    open( RDF, ">$RDF_output") or die "cannot open $RDF_output for writing";

    my $today = strftime( "%Y-%m-%dT%H:%M:%SZ", gmtime );
    my $schedLastModified = undef;

    if ($Data_path) {
	my ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,$atime,$mtime,
	    $ctime,$blksize,$blocks) = stat($Data_path.'/SCHED.DB');

	if ($mtime) {
#	    $mtime -= 3600;	# hack to workaround unpatched DST 2007-03-15
#	    $mtime += 3600;	# hack to workaround unpatched DST 2007-11-04
#	    $mtime -= 3600;	# hack to workaround unpatched DST 2008-03-10
#	    $mtime += 3600;     # hack to workaround unpatched DST 2008-11-02
#	    $mtime -= 3600;     # hack to workaround unpatched DST 2009-03-09
#	    $mtime += 3600;     # hack to workaround unpatched DST 2009-11-01
#	    $mtime -= 3600;     # hack to workaround unpatched DST 2010-03-14
	    $schedLastModified = strftime("%Y-%m-%dT%H:%M:%SZ",
					  gmtime($mtime));

	    printf "Schedule last modified on $schedLastModified\n";
	}
    }


    DBI->trace(2, 'nul:sched.trace');
    $dbh = DBI->connect('dbi:ODBC:RSB') || die $DBI::errstr;

    # Compute the first day of the current month
    my @periodStart = gmtime;
    $periodStart[0] = 0;		# sec
    $periodStart[1] = 0;		# min
    $periodStart[2] = 0;		# hour
    $periodStart[3] = 1;		# day of month
    $periodStart[4] = $ForMonth-1 if $ForMonth;

    my $thisMonth = strftime( "%Y-%m-%d", @periodStart );
    my $calYear = $periodStart[5] + 1900;
    my $calMonth = $periodStart[4];

    # Compute the day after the last day of the schedule we intend to extract
    my @periodEnd = @periodStart;
    $periodEnd[0] = 0;		# sec
    $periodEnd[1] = 0;		# min
    $periodEnd[2] = 0;		# hour
    $periodEnd[3] = 1;		# day of month
    $periodEnd[4] += $ForDuration; # how many months of data we want
    while ($periodEnd[4] > 12) { # modulo 11; period rolls into the next year
	$periodEnd[4] -= 12;
	$periodEnd[5]++;	# year
    }

    # "monthAfter" might have been a better name for this
    my $nextMonth = strftime( "%Y-%m-%d", @periodEnd );

    LoadContactInfo();

#    LoadTimezoneInfo();

    print "Reporting $thisMonth through $nextMonth\n";

    # Note that we can -- and do -- use SQL to do the date filtering for us
    $sth = $dbh->prepare(
     "SELECT sched.sched_id, sched.bridge_id, sched.conf_name, sched.start,
	     sched.end_time, sched.tz_id,
	     conf.conf_ctl_id, conf.standing, conf.addl_ports,
	     conf.guest_passcode, conf.contact_id,
	     conf.notes, conf.spec_instr,
	     conf.dnis_1_id, conf.dnis_1_instr,
	     conf.dnis_2_id, conf.dnis_2_instr,
	     conf.bill_type, conf.charge_code,
	     acct.billing_id
      FROM Sched sched, Conf conf, Account acct
      WHERE conf.conf_ctl_id = sched.conf_ctl_id AND
	    conf.acct_id = acct.acct_id  AND
	    sched.start > #$thisMonth# AND
	    sched.end_time < #$nextMonth# ")
    || die $dbh->errstr;

    # bill_type field is being overloaded to hold bridge linking info
    # bill_type = 0 (default) => no linking
    #             1 => linked, to the bridge identified by charge_code.
    #                  and this record is the one that will be displayed
    #                  in the XHTML calendar output.
    #             2 => linked, to the bridge identified by charge_code
    #                  and this record is skipped in the XHTML calendar
    #                  This code can also be used to suppress any event
    #                  in the XHTML calendar output.
    # charge_code holds the (short) name of the other bridge(s) that are
    #                  linked to make a single conference.  This is
    #                  primarily for display purposes; this code interposes
    #                  a '+' between the two bridge names.

    $sth->execute() || die $sth->errstr;

    if ($text_output) {
	unless (open( TEXT, ">$text_output"))  {
	    warn "cannot open $text_output for writing";
	    $text_output = undef;
	}
    }

    if ($cal_output) {
	unless (open( CAL, ">$cal_output"))  {
	    warn "cannot open $cal_output for writing";
	    $cal_output = undef;
	}
    }

    my $lastDayOfMonth;		# number of days in this month (0-based)
    my $dayOfWeek;		# weekday cursor

    my $startingUTCoffset;	# remember this to note changes during period
    my $now = time;
    $startingUTCoffset = difftime($now, timegm(localtime($now)));

    # Calendar XHTML header
    if ($cal_output) {
	print CAL <<EOH;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head> 
 <title>Teleconference Calendar</title>
 <link rel="stylesheet" type="text/css" href="/2002/06/calendar.css" />
</head>
<body>

<div class="nearby">
<a href="/"><img alt="W3C" src="/Icons/w3c_home" width="72" height="48" /></a>
<a href="/Member/"><img src="/Icons/member-site" alt="Member Site" width="128" height="48" /></a>
&nbsp;
<a href="#HowTo">How-To</a> |
<a href="#$MonthName[$calMonth]">$MonthName[$calMonth]</a> |
<a href="#$MonthName[$calMonth<11 ? $calMonth+1:0]">$MonthName[$calMonth<11 ? $calMonth+1:0]</a> |
<a href="#descript">details</a> |
<a href="#one-time">one-time conferences</a>
</div>

<h1>W3C Teleconference Calendar</h1>

EOH
	($dayOfWeek, $lastDayOfMonth) = IndexCal( 0, $calMonth, $calYear );
	IndexCal( 1,
		  $calMonth < 11 ? $calMonth + 1 : 0,
		  $calMonth < 11 ? $calYear      : $calYear + 1 );

	print CAL '
All times shown without an adjacent UTC time are ',
	    abs($startingUTCoffset) / 3600, ' hours ',
	    $startingUTCoffset > 0 ? 'behind' : 'ahead of',
	    ' UTC (Z).
' if $startingUTCoffset;

	if ($FrontFile) {
	    if (open(FRONT, "<$FrontFile")) {
		while (<FRONT>) {
		    print CAL;
		}
		close FRONT;
	    }
	    else {
		print "failed to open front matter \"$FrontFile\"\n";
	    }
	}

	StartMonth( $dayOfWeek, $calMonth, $calYear );
    }

    my $calDay = 1;		# cursor for tracking the calendar cell

    # RDF header
    print RDF "<?xml-stylesheet type=\"text/css\" href=\"/2002/06/conf-style.css\" ?>
<rdf:RDF 
    xmlns:rdf=\"http://www.w3.org/1999/02/22-rdf-syntax-ns#\"
    xmlns:dc=\"http://purl.org/dc/elements/1.1/\" 
    xmlns:conf=\"http://www.w3.org/2002/06/conf#\"
    xmlns:contact=\"http://www.w3.org/2000/10/swap/pim/contact#\" >\n\n";

    print RDF '<rdf:Description rdf:about="">
  <dc:date>', $today, '</dc:date>
  <dc:title>W3C Teleconference Calendar</dc:title>
  <dc:creator>', basename(__FILE__), $CVSRevision, '</dc:creator>
  <dc:description>W3C Scheduled Teleconferences, for the current month and the following month.</dc:description>
</rdf:Description>

<rdf:Description rdf:about="#dataSource">
  <dc:date>', $schedLastModified, '</dc:date>
  <dc:title>RSB Database</dc:title>
  <dc:description>The database maintained by Compunetix\' Reservation, Scheduling, and Billing System</dc:description>
</rdf:Description>

';

    my $prevStart = 0;		# record the previous starting time

    # Enumerate all the scheduled conferences within the date window
    while ( @row = $sth->fetchrow_array ) {
	my ($schedID, $bridgeID, $confName, $start, $end, $timezone,
	    $conf_ctl_id, $standing, $ports, $gcode, $contactID,
	    $notes, $instructions, $dnis1_id, $dnis1_instructions,
	    $dnis2_id, $dnis2_instructions, $linked, $otherBridge, $groupID)
		= @row;

	next if ($dnis1_instructions && $dnis1_instructions =~ m/hidden/i);

	$schedID =~ s/^0*//;
	$conf_ctl_id =~ s/^0*//;
	$contactID =~ s/^0*// if $contactID;
	$groupID =~ s/^0*// if $groupID;

	# reformat the dates to our preferred ISO format and extract time_t
	# these are stored in UTC in the database
	$start =~ s/(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)/$1-$2-$3T$4:$5Z/;
	my $start_tm = timegm($6, $5, $4, $3, $2-1, $1);

#	my $UTCoffset = $Timezone{$timezone}->{utcOffset};
#
#	{
#	    my ($month, $day) = ($2-1, $3);
#
#	    if ($Timezone{$timezone}->{usesDST} &&
#		($month >= $Timezone{$timezone}->{dstStartMonth}) &&
#		($month <= $Timezone{$timezone}->{dstEndMonth}))
#	    {
#		if (($month > $Timezone{$timezone}->{dstStartMonth}) &&
#		    ($month < $Timezone{$timezone}->{dstEndMonth}))
#		{
#		    $UTCoffset += $Timezone{$timezone}->{dstOffset};
#		}
#	    }
#	}

	$end =~ s/(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)/$1-$2-$3T$4:$5Z/;	
	my $end_tm = timegm($6, $5, $4, $3, $2-1, $1);

	my $duration = difftime($end_tm, $start_tm) / 60;

	# check for passcode reuse in consecutive telecons
	# if a conflict is discovered, flag both telecons
	if ($cal_output) {
	    my $usedBy = $PasscodeLastUsedBy{$gcode};
	    if ($usedBy) {
		if ($Conference{$usedBy}->{end}) {
		    if (($Conference{$usedBy}->{end} + $PasscodeReuseWindow)
			> $start_tm) {
			$Conference{$conf_ctl_id}->{codeConflict} = $usedBy;
			$Conference{$usedBy}->{codeConflict} = $conf_ctl_id;
		    }
		} else {
		    print( "Code $gcode reused from ",
			   ($Conference{$usedBy}->{name}
			     ? 'conference '.$Conference{$usedBy}->{name}
			     : 'unknown conference'),
			   ($Conference{$usedBy}->{start}
			     ? ' on '
			       . strftime('%Y-%m-%dT%H:%M',
					  gmtime($Conference{$usedBy}->{start}))
			     : ''),
			   ' by ',
			   $confName,
			   ' on ',
			   strftime('%Y-%m-%dT%H:%M', gmtime($start_tm)),
			   "\n");

		}
	    }
	    $PasscodeLastUsedBy{$gcode} = $conf_ctl_id;
	}

	my $givenName	= $Contact{$contactID}->{givenName};
	my $familyName	= $Contact{$contactID}->{familyName};
	my $email	= $Contact{$contactID}->{email};

	my $groupName = undef;
	my $groupMailbox = undef;
	my $groupURI = undef;

	if ($groupID) {
	    $groupName	  = $Contact{$groupID}->{familyName};
	    $groupMailbox = $Contact{$groupID}->{email};
	    $groupURI	  = $Contact{$groupID}->{URI};
	}

	# billing contact only represents a W3C group if address_1 is a URI
	unless ($groupName && $groupURI && $groupURI =~ m/http:/) {
	    $groupID = $groupName = $groupURI = $groupMailbox = undef;
	}

	# extract the right-most component of the bridge URI
	# This strips off trailing digits unless they begin with 5 or 7
	# to distinguish the two MIT bridges
	my $shortBridge = ($Bridge{$bridgeID} =~ m/#([a-zA-Z]+([57]\d+)?)\d*$/ && $1);

	# delete the time, empty '()'s, and trailing '_' from the conf name
	# remove the magic '*' first.
	my $shortName = $confName;
	$shortName =~ s/\((.*)\*(.*)\)/\($1$2\)/;
	$shortName =~ s/_?(\(\))?\d+:\d+[AP]M$//;

	if ($cal_output) {
	    my (undef, undef, undef, $confDay, $confMonth) = gmtime($start_tm);

	    my $rule = $start_tm == $prevStart ? '<hr />' : '<hr class="h"/>';
	    $rule = '' unless $prevStart; # first time
	    $prevStart = $start_tm;

	    # when we've run out of scheduled conferences for the current
	    # month, we need to fill out empty calendar cells for the remainder
	    # of the current month, and start the new month
	    if ($confMonth != $calMonth) {
		# special case: a conference can have a UTC start time
 		# that actually puts it on the local day before the first day
		# of the calendar (e.g. a Japan call).
		next if (($calMonth >  0 && $confMonth == ($calMonth - 1)) ||
			 ($calMonth == 0 && $confMonth == 11));

		while ($calDay++ < $lastDayOfMonth) {
		    print CAL "</td>\n";
		    if ($dayOfWeek++ == 6) {
			print CAL "</tr>\n\n<tr valign=\"top\">\n";
			$dayOfWeek = 0;
		    }
		    DateBox( $calDay, $calMonth, $calYear, $dayOfWeek );
		}
		$rule = '';
		print CAL "</td>\n</tr>\n</table>\n\n";
		$dayOfWeek = 0 if $dayOfWeek++ == 6;
		$calYear++ if $confMonth < $calMonth;
		$calMonth = $confMonth;
		StartMonth( $dayOfWeek, $calMonth, $calYear );
		$calDay = 1;
	    }

	    # when the current scheduled conference is for a new day, we
	    # need to close the current day cell, fill out empty day
	    # cells for any intervening days, and open the new day cell
	    while ($calDay < $confDay) {
		print CAL "</td>\n";
		if ($dayOfWeek++ == 6) {
		    print CAL "</tr>\n<tr valign=\"top\">\n";
		    $dayOfWeek = 0;
		}
		$calDay++;
		DateBox( $calDay, $calMonth, $calYear, $dayOfWeek );
		$rule = '';
	    }

	    unless ($linked == 2) {
		print CAL $rule, "\n ",
			'<a href="http://www.timeanddate.com/worldclock/fixedtime.html',
			strftime('?month=%m&amp;day=%d&amp;year=%Y&amp;hour=%H&amp;min=%M', gmtime($start_tm)),
			'&amp;sec=0&amp;p1=0">', # timezone = UTC
			strftime('%H:%M', gmtime($start_tm)), '-',
			strftime('%H:%M', gmtime($end_tm)), 'Z</a>',
			'<br />',
			    '<a href="#s_', $conf_ctl_id, '">',
			    xml_escape($shortName), '</a>',
			    ($bridgeID != 1 || $linked == 1)
				? '<br />'
				  .$shortBridge
			          .($linked == 1 ? "+$otherBridge" : '')
				: '';

		# Remember reservation details so they can be included
		# at the end of the calendar page.  A scheduled conference
		# that is part of a standing reservation will be hyperlinked
		# to this corresponding detail entry at the end of the page.

		# Remember this end time for detecting code conflicts
		$Conference{$conf_ctl_id}->{end} = $end_tm;

		# If the conference occurs more than once (i.e. is a standing
		# conference) then we overwrite old information with the
		# subsequent information up to the first occurrence on
		# or after the current time.

		# If the conference meets on consecutive days then we
		# identify each of the days.

		if ((not defined $Conference{$conf_ctl_id}->{start}) ||
		    ($Conference{$conf_ctl_id}->{start} < $now &&
		     $start_tm > $Conference{$conf_ctl_id}->{start}) ||
		    (($Conference{$conf_ctl_id}->{start} + 25*60*60)
		      >= $start_tm))
		{
		    $Conference{$conf_ctl_id}->{standing} = $standing;
		    $Conference{$conf_ctl_id}->{name} = $shortName;
		    $Conference{$conf_ctl_id}->{contact} =
			($givenName ? $givenName.' ' : '')
			.($familyName ? $familyName.' ' : '')
			.($email ? '<'.$email.'>' : '');
		    $Conference{$conf_ctl_id}->{bridge} = $shortBridge;
		    $Conference{$conf_ctl_id}->{linkedBridge} =
		    	($linked == 1 ? $otherBridge : undef);
		    if ($bridgeID =~ m/^0000000[23458]/ ||
			($hidden_codes && $confName =~ m/$hidden_codes/)) {
			$Conference{$conf_ctl_id}->{code} = undef;
			$Conference{$conf_ctl_id}->{dnis1_id} = undef;
		    }
		    else {
			$Conference{$conf_ctl_id}->{code} = $gcode;
			$Conference{$conf_ctl_id}->{dnis1_id} = $dnis1_id;
		    }
		    $Conference{$conf_ctl_id}->{ports} = $ports;

		    # Remember the weekday date
		    # Meetings that are scheduled over consecutive days
		    # get special handling.  Detect these by noting that
		    # the next starting time is within 25 hours of the
		    # previous one.
		    if ($Conference{$conf_ctl_id}->{start} &&
			(($Conference{$conf_ctl_id}->{start} + 25*60*60)
			 >= $start_tm))
		    {
			$Conference{$conf_ctl_id}->{daysAreConsecutive} = 1;
			my $dates = $Conference{$conf_ctl_id}->{weekday};
			if (length($dates) == 1) {
			    # first weekday now needs to be expanded
			    $dates =
			       $Weekday[$Conference{$conf_ctl_id}->{weekday}]
			       .', '
			       .$Conference{$conf_ctl_id}->{day}
			       .' '
			       .substr($MonthName[$Conference{$conf_ctl_id}->{month}],0,3);
			}
			else {
			    # we have a new ending date; trim the old one
			    $dates =~ s/ - .*$//;
			}
			$dates .=
			    ' - '
			    .$Weekday[$dayOfWeek]
			    .', '
			    .$confDay
			    .' '
			    .substr($MonthName[$confMonth],0,3);
			$Conference{$conf_ctl_id}->{weekday} = $dates;
		    }
		    else {
			$Conference{$conf_ctl_id}->{weekday} = $dayOfWeek;
			# Use the starting date for sorting
			# Only keep the next starting date after $now
			$Conference{$conf_ctl_id}->{start} = $start_tm;
			$Conference{$conf_ctl_id}->{end}   = $end_tm;
		    }

		    $Conference{$conf_ctl_id}->{month} = $confMonth;
		    $Conference{$conf_ctl_id}->{day}   = $confDay;

		    $Conference{$conf_ctl_id}->{timezone} = $timezone;
		    $Conference{$conf_ctl_id}->{notes} = $notes;
		    $Conference{$conf_ctl_id}->{instructions} = $instructions;
		    $Conference{$conf_ctl_id}->{dnis1_instructions} = $dnis1_instructions;
		    $Conference{$conf_ctl_id}->{dnis2_id} = $dnis2_id;
		    $Conference{$conf_ctl_id}->{dnis2_instructions} = $dnis2_instructions;
		    $Conference{$conf_ctl_id}->{groupName} = $groupName;
		    $Conference{$conf_ctl_id}->{groupURI} = $groupURI;
		    $Conference{$conf_ctl_id}->{groupMailbox} = $groupMailbox;
		}
	    }
	}

	printf TEXT "%-10s %5s %17s %-27s %5s %2s %3s %s %s %s\n",
		$shortBridge, $schedID, $start, $confName, $gcode,
		$ports || '',
		$duration,
		$givenName || '',
		$familyName || '',
		$email || ''
	    if $text_output;

	print RDF '<conf:ScheduledConference rdf:about="#m', $schedID, "\">\n",
	    '  <conf:id>', $schedID, "</conf:id>\n";

	print RDF '  <conf:code>', $gcode, "</conf:code>\n"
	  unless $bridgeID =~ m/^0000000[2345]/ ||
		 $hidden_codes && $confName =~ m/$hidden_codes/;

	print RDF '  <conf:name>', xml_escape($confName), "</conf:name>\n",
	    '  <conf:start>', $start, "</conf:start>\n",
	    "  <conf:duration>", $duration, "</conf:duration>\n",
	    '  <conf:bridge rdf:resource="', $Bridge{$bridgeID}, "\"/>\n";
	print RDF '  <conf:ports>', $ports, "</conf:ports>\n" if $ports;
	print RDF "  <conf:subEventOf rdf:resource=\"#s_$conf_ctl_id\"/>\n"
	    if $standing;

	if ($groupURI || $groupMailbox) {
	    if ($Contact{$groupID}->{ALREADYOUTPUT}) {
		print RDF "  <conf:group rdf:resource=\"#c_$groupID\"/>\n";
	    }
	    else {
		print RDF "  <conf:group>\n",
			  "    <rdf:Description rdf:about=\"#c_$groupID\">\n";
		print RDF '      <contact:mailbox rdf:resource="mailto:', xml_escape($groupMailbox), "\"/>\n"
		    if $groupMailbox;
		print RDF '      <contact:homePage rdf:resource="', xml_escape($groupURI), "\"/>\n"
		    if $groupURI;
		print RDF "    </rdf:Description>\n",
			  "  </conf:group>\n";
		$Contact{$groupID}->{ALREADYOUTPUT} = 1;
	    }
	}

	if ($givenName || $familyName || $email) {
	    if ($Contact{$contactID}->{ALREADYOUTPUT}) {
		print RDF "  <conf:contact rdf:resource=\"#c_$contactID\"/>\n";
	    }
	    else {
		print RDF "  <conf:contact>\n",
			  "    <contact:Person rdf:about=\"#c_$contactID\">\n";
		print RDF '      <contact:givenName>', xml_escape($givenName), "</contact:givenName>\n"
		    if $givenName;
		print RDF '      <contact:familyName>', xml_escape($familyName), "</contact:familyName>\n"
		    if $familyName;
		print RDF '      <contact:mailbox rdf:resource="mailto:', xml_escape($email), "\"/>\n"
		    if $email;
		print RDF "    </contact:Person>\n",
			  "  </conf:contact>\n";
		$Contact{$contactID}->{ALREADYOUTPUT} = 1;
	    }
	}

        print RDF "</conf:ScheduledConference>\n\n";
    }

    $sth->finish;
    $dbh->disconnect || warn $dbh->errstr;

    # Create empty calendar cells for any remaining days of the (next) month
    if ($cal_output) {
	$periodEnd[3] = 0;	# zeroth day of following month, mormalized
	$lastDayOfMonth = strftime( '%d', @periodEnd );

	while ($calDay++ < $lastDayOfMonth) {
	    print CAL "</td>\n";
	    if ($dayOfWeek++ == 6) {
		print CAL "</tr>\n<tr valign=\"top\">\n";
		$dayOfWeek = 0;
	    }
	    DateBox( $calDay, $calMonth, $calYear, $dayOfWeek );
	}

	# complete the calendar table
	print CAL "</td>\n</tr>\n</table>\n";

	# Format any standing reservation detail information
	print CAL '
<a name="descript" id="descript"></a>
<h2>Standing Conference Descriptions</h2>

<p>The weekday and time shown in each of the following descriptions
correspond to the next time the respective conference meets on or after ',
	    strftime('%d %B', gmtime), '.</p>
';

	my @list = sort {$Conference{$a}->{name}.$Conference{$a}->{start}
			     cmp
			  $Conference{$b}->{name}.$Conference{$b}->{start}
			}
		   keys %Conference;

	my @OneTimeConfList = ();

	foreach my $ctlID (@list) {
	    # Save one-time details for later
	    if ($Conference{$ctlID}->{standing}) {
		FormatConfDetails( $ctlID );
	    }
	    else {
		push @OneTimeConfList, $ctlID;
	    }
	}

	# Format any one-time reservation detail information
	print CAL '<a name="one-time" id="one-time"></a>',"\n",
		  "<h2>One-Time Conference Details</h2>\n";

	if ($#OneTimeConfList >= 0) {
	    @list = sort {$Conference{$a}->{start}
			      cmp
			  $Conference{$b}->{start}
			    }
		    @OneTimeConfList;

	    foreach my $ctlID (@list) {
		FormatConfDetails( $ctlID );
	    }
	}

	print CAL '

<h2>Acknowledgements</h2>
Thanks to:
<ul>
 <li>Libby Miller, ILRT, for much of the calendar style; taken from
     <a href="http://swordfish.rdfweb.org/discovery/2002/05/rsscal/">RSS events data display</a> prototype.</li>
 <li>Amy van der Hiel, Carol Nicolora, and Susan Westhaver, W3C</li>
 <li>Development of RDF tools supported in part by funding from US Defense
    Advanced Research Projects Agency (DARPA) and Air Force Research
    Laboratory, Air Force Materiel Command, USAF, under agreement
    number F30602-00-2-0593, "Semantic Web Development".</li>
 <li><a href="http://www.compunetix.com">Compunetix, Inc.</a>, providers
    of the voice teleconferencing system supporting Zakim.</li>
</ul>
<address>
 <a href="mailto:swick@w3.org">Ralph R. Swick</a>, W3C MIT/CSAIL
</address>
<hr />
<em>Created by ', basename(__FILE__), $CVSRevision, 'on ', $today;

	print CAL "<br />\nfrom data as of $schedLastModified"
	    if $schedLastModified;
	print CAL '</em>
</body>
</html>
';
	close CAL;
    }

    print RDF "</rdf:RDF>\n";
    close RDF;

    close TEXT if $text_output;
}

# Writes the markup for the start of a calendar table
# including creating empty calendar cells for the beginning
# of the week in which the month starts.
#
# Inputs: day of week on which the month begins,
#	  month number (0-based)
#	  year
sub StartMonth
{
    my ($dayToStart, $month, $year) = @_;
    print CAL <<EOH
<h2><a name="$MonthName[$month]" id="$MonthName[$month]"></a>$MonthName[$month] $year</h2>
<table class="conf">
<tr valign="top">
EOH
;
    for (my $day = 0; $day < $dayToStart; $day++) {
	print CAL " <td class=\"empty\">&nbsp;</td>\n";
    }
    DateBox( 1, $month, $year, $dayToStart );
}

# Writes the markup to start a single day's calendar cell
#
# Inputs: day of month,
#	  number of month (0-based)
#	  day of week (0=Sunday)
sub DateBox
{
    my ($day, $month, $year, $wday) = @_;

    my $id = DayID($year, $month, $day);
    $id = '<a name="'.$id.'" id="'.$id.'"></a>';

    print CAL ' <td>'.$id.'<b>', $day, '&nbsp;', $MonthName[$month],
	      '</b><br /><em>', $Weekday[$wday], '</em><br /><br />';
}

sub DayID
{
    my ($year, $month, $day) = @_;
    return sprintf "D%4d%02d%02d", $year, $month+1, $day;
}


# Writes the markup for a small 'index' calendar
#
# Inputs: day of week on which the month begins,
#	  month number (0-based)
#	  year
#
# Writes CAL file handle directly
#
# Returns: day of week on which the first of the month falls
#	   number of days in the month
sub IndexCal
{
    my ($col, $month, $year) = @_;

    $col = ('colA', 'colB')[$col];
    my @date = (0, 0, 0, 1, $month, $year-1900);
    my $firstWeekday = strftime( "%w", @date ); # weekday of day 1 @@hack

    # @@hack; zeroth day of next month == last day of this one
    @date = (0, 0, 0, 0,			# sec, min, hour, day
	     $month < 11 ? $month + 1 : 0, 	# month
	     $month < 11 ? $year - 1900 : $year - 1900 + 1 ); # year
    my $lastDayOfMonth = strftime( '%d', @date );

    print CAL <<EOH
<div class="$col">
<b>$MonthName[$month] $year</b>
<table class="index">
<tr>
EOH
    ;

    for (my $day = 0; $day < 7; $day++) {
	print CAL ' <td class="empty"><em>',
		  (Su, Mo, Tu, We, Th, Fr, Sa)[$day], "</em></td>\n";
    }
    print CAL "</tr>\n<tr>\n";

    for (my $day = 0; $day < $firstWeekday; $day++) {
	print CAL " <td class=\"empty\">&nbsp;</td>\n";
    }

    my $dayOfWeek = $firstWeekday; # weekday cursor
    for (my $day = 1; $day <= $lastDayOfMonth; $day++) {
	my $id = DayID($year, $month, $day);
	print CAL ' <td><a href="#'.$id.'">', $day, "</a></td>\n";
	if ($dayOfWeek++ == 6 && $day != $lastDayOfMonth) {
	    print CAL "</tr>\n<tr>\n";
	    $dayOfWeek = 0;
	}
    }

    print CAL <<EOH
</tr>
</table>
</div>

EOH
    ;
    return ($firstWeekday, $lastDayOfMonth);
}

# Reads the address book so we don't have to use SQL joins
sub LoadContactInfo
{
    my $cqh = $dbh->prepare(
     "SELECT person_id, first_name, last_name, email_addr, addr_line_1
      FROM Person")
	|| die $dbh->errstr;

    $cqh->execute() || die $cqh->errstr;

    while ( my @person = $cqh->fetchrow_array ) {
	my ($ID, $givenName, $familyName, $email, $URI) = @person;
	
	$ID =~ s/^0*//;

	$Contact{$ID}->{givenName} = $givenName;
	$Contact{$ID}->{familyName} = $familyName;
	$Contact{$ID}->{email} = $email;
	$Contact{$ID}->{URI} = $URI;
    }
    $cqh->finish;
}

# Reads the timezone configuration
sub LoadTimezoneInfo
{

    my (undef, undef, undef, undef, undef, $year) = localtime;

    my $cqh = $dbh->prepare(
     "SELECT tz_id, std_abbr, gmt_offset, dst_used, dst_abbr, dst_offset,
	     dst_start_def, dst_end_def
      FROM Timezone")
	|| die $dbh->errstr;

    $cqh->execute() || die $cqh->errstr;

    while ( my @timezone = $cqh->fetchrow_array ) {
	my ($ID, $tzname, $utcOffset, $usesDST, $dstname, $dstOffset,
	    $dstStart, $dstEnd) = @timezone;
	
	$Timezone{$ID}->{name} = $tzname;
	$Timezone{$ID}->{utcOffset} = $utcOffset * 60; # in seconds
	$Timezone{$ID}->{usesDST} = $usesDST;

	if ($usesDST) {
	    $Timezone{$ID}->{dstname} = $dstname;
	    $Timezone{$ID}->{dstOffset} = $dstOffset * 60; # in seconds

	    my ($month, $week, $day, $minute) = split('~', $dstStart);
	    $Timezone{$ID}->{dstStartMonth} = $month;
	    $Timezone{$ID}->{dstStartWeek} = $week;
	    $Timezone{$ID}->{dstStartDay} = $day;
	    $Timezone{$ID}->{dstStartMinute} = $minute;

	    ($month, $week, $day, $minute) = split('~', $dstEnd);
	    $Timezone{$ID}->{dstEndMonth} = $month;
	    $Timezone{$ID}->{dstEndWeek} = $week;
	    $Timezone{$ID}->{dstEndDay} = $day;
	    $Timezone{$ID}->{dstEndMinute} = $minute;
	}
    }
    $cqh->finish;
}

# Output reservation details
sub FormatConfDetails
{
    my ($ctlID) = @_;
    print CAL "\n<p>\n",
	'<a name="s_', $ctlID, '"></a>',
	'<b>', xml_escape($Conference{$ctlID}->{name}), "</b><br />\n",
	$Conference{$ctlID}->{groupURI} ?
	    ('<a href="', xml_escape($Conference{$ctlID}->{groupURI}), '">',
	     xml_escape($Conference{$ctlID}->{groupName}),
	     "</a><br />\n")
	   :   '';

    if (length($Conference{$ctlID}->{weekday}) > 1) {
	print CAL $Conference{$ctlID}->{weekday};
    }
    else {
	print CAL $Weekday[$Conference{$ctlID}->{weekday}];
	if ($Conference{$ctlID}->{standing}) {
	    print CAL 's';
	}
	else {
	    print CAL ', ', $Conference{$ctlID}->{day},
		      ' ', $MonthName[$Conference{$ctlID}->{month}];
	}
    }

    {
	my $start_tm = $Conference{$ctlID}->{start};
	my $end_tm   = $Conference{$ctlID}->{end};
	print CAL ' <a href="http://www.timeanddate.com/worldclock/fixedtime.html',
		  strftime('?month=%m&amp;day=%d&amp;year=%Y&amp;hour=%H&amp;min=%M',
			   gmtime($start_tm)),
		  '&amp;sec=0&amp;p1=0">', # timezone = UTC
		  strftime('%H:%M',gmtime($start_tm)),
		  '-',
		  strftime('%H:%M',gmtime($end_tm)),
		  ' UTC</a>';

	# format the start and end times in 12-hour format for Boston local.
	# strftime patterns %l and %P should have done this but didn't
	my ($s_time, $e_time, $gm_wkday, $local_wkday);
	{
	    my (undef, $s_min, $s_hour) = localtime($start_tm);
	    $s_time = ($s_hour < 12)
		? sprintf( "%d:%02dam", ($s_hour || 12), $s_min )
		: sprintf( "%d:%02dpm", ($s_hour - 12 || $s_hour), $s_min );

	    my (undef, $e_min, $e_hour) = localtime($end_tm);
	    $e_time = ($e_hour < 12)
		? sprintf( "%d:%02dam", ($e_hour || 12), $e_min )
		: sprintf( "%d:%02dpm", ($e_hour - 12 || $e_hour), $e_min );
	}
	$gm_wkday = strftime('%w',gmtime($start_tm));
	$local_wkday = strftime('%w',localtime($start_tm));

	if ($gm_wkday == $local_wkday) {
	    $local_wkday = '';
	}
	else {
	    $local_wkday = $Weekday[$local_wkday];
	    if ($Conference{$ctlID}->{standing}
		&& !$Conference{$ctlID}->{daysAreConsecutive}) {
		$local_wkday .= 's ';
	    }
	    else {
		$local_wkday .= ' ';
	    }
	}
	    
	print CAL " ($local_wkday$s_time-$e_time Boston local)<br />\n";
    }

    print CAL '<b>',
	xml_escape($Conference{$ctlID}->{dnis1_instructions}), "</b><br />\n"
	    if $Conference{$ctlID}->{dnis1_instructions};

    my $linkedBridge = $Conference{$ctlID}->{linkedBridge};
    print CAL
	$Conference{$ctlID}->{bridge},
	($linkedBridge ? '+'.$linkedBridge : ''), ' Bridge',
	($linkedBridge ? 's' : ''),
	' <a href="tel:', $BridgeTel{$Conference{$ctlID}->{bridge}}, '">',
        $BridgeTel{$Conference{$ctlID}->{bridge}}, '</a>';

    print CAL
	' or <a href="tel:', $BridgeTel{$linkedBridge}, '">',
        $BridgeTel{$linkedBridge}, '</a>'
	    if $linkedBridge;

    if ($Conference{$ctlID}->{code}) {
	print CAL ', conference ', $Conference{$ctlID}->{code};
	print CAL ' ("', $Conference{$ctlID}->{dnis1_id}, '")'
	    if $Conference{$ctlID}->{dnis1_id};
    }

    if (my $conflicts = $Conference{$ctlID}->{codeConflict}) {
	print CAL ' &nbsp;&nbsp;<strong>NOTE WELL: this code conflicts with ',
		  '<a href="#s_', $conflicts, '">',
		  xml_escape($Conference{$conflicts}->{name}),
		  '</a></strong>';
    }

    if ($Conference{$ctlID}->{dnis1_id}) {
	my $trCode = $Conference{$ctlID}->{dnis1_id};
	$trCode =~ tr/abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ/2223334445556667777888999922233344455566677778889999/;
	if ($Conference{$ctlID}->{code} != $trCode) {
	    print CAL ' &nbsp;&nbsp;<strong>NOTE WELL: this code does not match "',
		      xml_escape($Conference{$ctlID}->{dnis1_id}),
		      '"</strong>';
	}
    }

    print CAL "<br />\n";
    print CAL $Conference{$ctlID}->{ports}, " participants<br />\n"
	if $Conference{$ctlID}->{ports};
    print CAL xml_escape($Conference{$ctlID}->{contact}), "<br />\n";
    if ($DisplayInstructions){
	print CAL xml_escape($Conference{$ctlID}->{instructions}), "<br />\n"
	    if $Conference{$ctlID}->{instructions};
	print CAL xml_escape($Conference{$ctlID}->{dnis2_instructions}), "<br />\n"
	    if $Conference{$ctlID}->{dnis2_instructions};
	print CAL '<em>', xml_escape($Conference{$ctlID}->{notes}), "</em><br />\n"
	    if $Conference{$ctlID}->{notes};
    }
    print CAL "</p>\n";
}
