The importance of a detailed specification

What the boss requested:

#!/bin/sh
# Hack to report on the phone queue 

mysql altigen --user=db_user --password=censored -e \
"SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL floor((Unix_Timestamp(Current_Timestamp()) - EndTime) / 86400) DAY),'%c-%d') as \"Date\",
DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL floor((Unix_Timestamp(Current_Timestamp()) - EndTime) / 86400) DAY), '%a') as \"Day\",
count(*) as \"Calls\",
concat(
  lpad(floor(avg(EndTime - StartTime) / 60),2,' '),':', 
  lpad(floor(avg(EndTime - StartTime) MOD 60),2,'0')) AS \" Avg. Time\"
FROM CDRMAIN
WHERE (Unix_Timestamp(Current_Timestamp()) - EndTime) < 86400 * 7
and (TargetWGNum in (2000, 2002, 2088, 2099, 2999)
 or (TargetNum > 8020 and targetNum < 8030)
 or TargetName like '%SupportGroup%'
)
and talkduration > 0
GROUP BY Date,Day
ORDER BY CURDATE() - floor((Unix_Timestamp(Current_Timestamp()) - EndTime) / 86400)" | mail -s "Automated Weekly Queue Report" users@company.xxx

What was finally delivered after being sent back multiple times for revision:

#!/usr/bin/perl
# Simple script to mail weekly reports about the Altigen phone queue

##### Stuff you can easily change #################################
### E-mail parameters
@mailList = ('users@company.xxx');
$mailSubject = 'Support Center Call summary'; # Subject of e-mail message
#
## Final command to process and mail output
$CMD = "/bin/mail -s '$mailSubject' @mailList"; # Simple, can't do text/html 
$CMD = "/usr/sbin/sendmail -t ";
$CMD = "cat"; # Uncomment this line to test changes to the program
# Note: for Sendmail, the $mailSubject and @mailList are set in the e-mail
# header which is then attached to the message before it is sent. 
# Since /bin/mail can't set headers, /bin/mail takes $mailSubject and
# @maillist on the command line.
#
## The report's MIME format -- can be either text/html or text/plain 
$format = "text/plain";	
#$format = "text/html";	
# Note: /bin/mail cannot change mail headers to display an HTML e-mail,
# so use text/plain if using /bin/mail
### Functional parameters
## Date range
$NUM_DAYS = 7; # Number of days to show data for 
$DAYS_BACK = 2; # Number of days ago data ends.
# When run from Cron on Sunday morning, set DAYS_BACK to 1 to get
# a report for the previous Sunday-Saturday week.
#
$show_errors = 1; # Display errors at end of message 
#$show_query = 0; # Display SQL query at end of message. TODO: Fix. 
####################################################################


# Compute a few values
$DAY_END = $DAYS_BACK;
$DAY_START = $NUM_DAYS + $DAYS_BACK;

if($DAY_START < $DAY_END){
	# swap
	my $tmp = $DAY_END;
	$DAY_END = $DAY_START;
	$DAY_START = $tmp;
}

# Set mail headers for Sendmail
# (or whatever $CMD is, but /bin/mail doesn't seem to do headers)
$mailHeaders = "";
if(@mailList){
	# Create a BCC list
	# Our server doesn't handle multiple BCC entries
	my $str .= "BCC:";
	foreach $i (@mailList){
		$str .= " $i,";
	}
	$str =~ s/,$//;
	$mailHeaders .= "$str\n"; 
}
$mailHeaders .=
"Subject: $mailSubject
Reply-To: source@company.xxx
Content-type: $format\n\n"; 


########## Begin program 

# Connect to the database
use DBI;
$dbh = DBI->connect('DBI:mysql:altigen','db_user','censored');




###### Strings for conditions to be included in WHERE clauses 
# These can be plugged in like "WHERE $str1 AND NOT $str2";

# Whether a call comes from the SupportGroup 
$WHERECALL_SupportGroup_IN =
"(TargetWGNum in (2000, 2002, 2088, 2099)
 or (TargetNum > 8020 and targetNum < 8030)
 or TargetName like '\%SupportGroup\%')";
$WHERECALL_SupportGroup_OUT = "(OutGoingWG = 8099 or OutGoingWG = 2099
 or (CallerNum > 8020 and CallerNum < 8030)
 or CallerName like '\%SupportGroup\%')";
$WHERECALL_SupportGroup = "($WHERECALL_SupportGroup_IN or $WHERECALL_SupportGroup_OUT)";

# Whether the call took up any time
# (many records have 0 talkduration and are irrelevant)
$WHERECALL_TIME_OK = "(talkduration > 0)";
$WHERECALL_TIME_HOLD = "(holdduration > 0)"; # If call was on hold.
$WHERECALL_TIME_INQUEUE = "(queueduration > 0)";

# Whether the call is in the date range set at the top of this file.
$WHERECALL_DATE_INRANGE = 
"(DATEDIFF(CURDATE(), DATE(FROM_UNIXTIME(EndTime))) < $DAY_START
AND DATEDIFF(CURDATE(), DATE(FROM_UNIXTIME(EndTime))) >= $DAY_END)";

# Whether the call is to the answering service
$WHERECALL_ASERVICE = "(TargetNum = 8081 or TargetNum = 2345678)";

###### Strings for fields in SELECT clauses 
# Like the WHEREs, these can be used like "SELECT $str1, $str2, $str3"
# They all pull from the CDRMAIN table for now. 
$SELECT_DATE = "DATE_FORMAT(FROM_UNIXTIME(EndTime), '%c-%d') as \"Date\"";
$SELECT_DAY = "DATE_FORMAT(FROM_UNIXTIME(EndTime), '%a') as \"Day\"";
$SELECT_HOUR = "DATE_FORMAT(FROM_UNIXTIME(EndTime), '%H') as \"Hour\"";
$SELECT_CALLAVGTIME = "concat(
  lpad(floor(avg(EndTime - StartTime) / 60),2,' '),':',
  lpad(floor(avg(EndTime - StartTime) MOD 60),2,'0')) AS AvgTime";
$SELECT_CALLTIME = "concat(
  lpad(floor((EndTime - StartTime) / 60),2,' '),':',
  lpad(floor((EndTime - StartTime) MOD 60),2,'0')) AS \" Time\"";
$SELECT_CALLSUMTIME = "concat(
   lpad(floor((sum(EndTime - StartTime) / 3600)),2,' '),':',
   lpad(floor(sum(EndTime - StartTime) / 60 MOD 60),2,'0')) AS \"Sum Time\"";



###### Functions to build full SQL queries from common clauses
###### and custom additions. 

# Main query 
# This is a SQL-code-returning subroutine because similar queries
# are of the same form but differ slightly in the WHERE clause.
# Takes a boolean segment of a WHERE clause. 
# Note: This query takes about 2 seconds to run.
sub sqlSupportGroupTable {
	my $MYWHERE_SupportGroup = $_[0];		# Takes a WHERECALL_SupportGroup
	my $MYWHERE_TIME = whereTime($_[1]);	# Takes a WHERECALL_TIME
	if(!$MYWHERE_SupportGroup){
		$MYWHERE_SupportGroup = $WHERECALL_SupportGroup;
		$MYWHERE_TIME = $WHERECALL_TIME;
		recordError("Warning: sqlSupportGroupTable called without arguments\n");
	} elsif(!$MYWHERE_TIME){
		$MYWHERE_TIME = $WHERECALL_TIME;
		recordError("Warning: sqlSupportGroupTable called with only one non-null argument:\n$MYWHERE_SupportGroup\n");
	}
	my($sql) =
"SELECT $SELECT_DATE, $SELECT_DAY, count(*) as \"Calls\",
$SELECT_CALLAVGTIME
FROM CDRMAIN
WHERE $WHERECALL_DATE_INRANGE and $MYWHERE_TIME 
and $MYWHERE_SupportGroup
GROUP BY Date,Day
ORDER BY DATEDIFF(CURDATE(), DATE(FROM_UNIXTIME(EndTime))) DESC; ";
	return $sql;
}



# Additional functions to create SQL code for producing summaries of
# the main table and similar queries.
sub sqlNumCalls {
	my($where) = $_[0]; # takes a $WHERECALL_foo
	return "SELECT COUNT(*) FROM CDRMAIN WHERE $where;"
}
sub sqlNumXFers {
	my($where) = $_[0]; 
	$where =~ s/$WHERECALL_TIME_OK//; # Talkduration on these is 0
	return sqlNumCalls("$where AND $WHERECALL_ASERVICE");
}
sub sqlSumMinutes  {
	my($where) = $_[0];
	return "SELECT floor(SUM(EndTime - StartTime)  / 60) as SumMin FROM CDRMAIN WHERE $where";
}
sub sqlAvgCallLen {
	my($where) = $_[0];
	return "SELECT $SELECT_CALLAVGTIME FROM CDRMAIN WHERE $where";
}
# Pick which type of TIME line to use. 
sub whereTime{
	my $timeStr = shift;
	if($timeStr){
		if($timeStr eq "HOLD"){
			return $WHERECALL_TIME_HOLD }
		elsif($timeStr eq "QUEUE"){
			return $WHERECALL_TIME_INQUEUE }
	}
	return $WHERECALL_TIME_OK;
}

# Comparison between hours 
sub whereHours {
	return "
(
  (TIMEDIFF($_[1], TIME(FROM_UNIXTIME(EndTime))) < TIMEDIFF($_[1],$_[0]))
  AND
  (TIMEDIFF($_[1], TIME(FROM_UNIXTIME(EndTime))) > TIME('0:00'))
)";
}


##### Other queries used for other reports 

# Account code query 
$sql_acctCode = "SELECT 
left(coalesce(ACCOUNTS.Name,'[null]'), 11) as Name,
CDRMAIN.AccountCode, 
sum(talkDuration) as \"Time\",
avg(talkDuration) as \"Avg.\",
count(*) as \"Calls\"
FROM CDRMAIN LEFT OUTER JOIN ACCOUNTS
ON CDRMAIN.AccountCode = ACCOUNTS.AccountCode
WHERE $WHERECALL_DATE_INRANGE AND $WHERECALL_TIME_OK
AND length(CDRMAIN.AccountCode) > 0
GROUP BY CDRMAIN.AccountCode
ORDER BY cast(CDRMAIN.AccountCode as SIGNED)";


$sql_acctCode_2 =
"SELECT Name, AccountCode, 
CONCAT(
   lpad(floor(\"Time\" / 3600),2,' '),':',
   lpad(floor(\"Time\" / 60 MOD 60),2,'0')
),
CONCAT(
   lpad(floor(\"Avg.\" / 3600),2,' '),':',
   lpad(floor(\"Avg.\" / 60 MOD 60),2,'0')
)
Calls
FROM ($sql_acctCode) as a"; 

#left(coalesce(ACCOUNTS.Name,'[null]'), 11),
#CDRMAIN.AccountCode, 
#CONCAT(
#   lpad(floor((sum(talkDuration) / 3600)),2,' '),':',
#   lpad(floor(sum(talkDuration) / 60 MOD 60),2,'0')
#) AS \"Time\",
#CONCAT(
#   lpad(floor((avg(talkDuration) / 3600)),2,' '),':',
#   lpad(floor(avg(talkDuration) / 60 MOD 60),2,'0')
#) AS \"Avg\",
#avg(talkDuration) as \"Avg.\",
#count(*) as \"Calls\"
#FROM CDRMAIN LEFT OUTER JOIN ACCOUNTS
#ON CDRMAIN.AccountCode = ACCOUNTS.AccountCode
#WHERE $WHERECALL_DATE_INRANGE AND $WHERECALL_TIME_OK
#AND length(CDRMAIN.AccountCode) > 0
#GROUP BY CDRMAIN.AccountCode
#ORDER BY cast(CDRMAIN.AccountCode as SIGNED);";


# Get list of long outbound calls 
# Note: This query groups by SessionID since "duplicates" are in the
# database for the same session ID. This could cause buggy reports if there
# are calls with wildly different start and end times with the same sessionID.
$sql_longOutCalls = "
SELECT $SELECT_DATE, $SELECT_DAY, CallerName, $SELECT_CALLTIME, TargetNum
FROM (select 
min(startTime) as StartTime, max(endTime) as EndTime,
CallerName, max(targetNum) as targetNum
FROM CDRMAIN
WHERE $WHERECALL_DATE_INRANGE AND $WHERECALL_TIME_OK
AND $WHERECALL_SupportGroup_OUT AND NOT $WHERECALL_SupportGroup_IN
GROUP BY SessionID, callerName) as A
ORDER BY (EndTime - StartTime) DESC
LIMIT 1,10;";


# Get list of inbound calls with no account code 
$sql_NoCodeCallsInbound = "
SELECT $SELECT_DATE, $SELECT_DAY, TargetName, $SELECT_CALLTIME, CallerNum
FROM CDRMAIN
WHERE $WHERECALL_DATE_INRANGE AND $WHERECALL_TIME_OK
AND $WHERECALL_SupportGroup_IN AND NOT $WHERECALL_SupportGroup_OUT 
AND (AccountCode is NULL
or length(AccountCode) = 0)
ORDER BY StartTime ;";
# Get list of outbound calls with no account code 
$sql_NoCodeCallsOutbound = "
SELECT $SELECT_DATE, $SELECT_DAY, CallerName, $SELECT_CALLTIME, TargetNum
FROM CDRMAIN
WHERE $WHERECALL_DATE_INRANGE AND $WHERECALL_TIME_OK 
AND $WHERECALL_SupportGroup_OUT AND NOT $WHERECALL_SupportGroup_IN 
AND (AccountCode is NULL
or length(AccountCode) = 0)
ORDER BY StartTime ;";

# changed: now using summaries instead of full lists 
$sql_NoCodeCallsInboundSummary = "
SELECT TargetName, count(*) as num
FROM CDRMAIN
WHERE $WHERECALL_DATE_INRANGE AND $WHERECALL_TIME_OK
AND $WHERECALL_SupportGroup_IN AND NOT $WHERECALL_SupportGroup_OUT 
AND (AccountCode is NULL
or length(AccountCode) = 0)
GROUP BY TargetName  
ORDER BY num DESC;";
$sql_NoCodeCallsOutboundSummary = "
SELECT CallerName, count(*) as num
FROM CDRMAIN
WHERE $WHERECALL_DATE_INRANGE AND $WHERECALL_TIME_OK
AND $WHERECALL_SupportGroup_OUT AND NOT $WHERECALL_SupportGroup_IN
AND (AccountCode is NULL
or length(AccountCode) = 0)
GROUP BY CallerName
ORDER BY num DESC;";


# Get list of all SupportGroup calls
$sql_allSupportGroupCalls = "
SELECT $SELECT_DATE, $SELECT_DAY, CallerName, $SELECT_CALLTIME,
TargetNum, AccountCode
FROM CDRMAIN
WHERE $WHERECALL_DATE_INRANGE AND $WHERECALL_TIME_OK AND $WHERECALL_SupportGroup
ORDER BY StartTime ;";


$sql_callsByHour = "
SELECT
$SELECT_HOUR, '',
$SELECT_CALLSUMTIME,
count(*) AS numCalls,
-- floor(sum(EndTime - StartTime) / count(*) / 60.0) AS MinPerCall
-- sum(EndTime - StartTime) / count(*) / 60.0 AS MinPerCall
concat(
  lpad(floor((sum(EndTime - StartTime) / count(*) / 60)),2,' '),':',
  lpad(floor(sum(EndTime - StartTime) / count(*) MOD 60),2,'0'))

FROM CDRMAIN
WHERE $WHERECALL_DATE_INRANGE AND $WHERECALL_SupportGroup
AND (targetWGNum <> 0 OR outgoingWG <> 0)
GROUP BY Hour
ORDER BY Hour
; ";


# qry([$dbh],$sql,[$description]): SQL query wrapper for DBI.
# Arguments are:
#    * $dbh: A database handle.
#    * $sql: SQL code. A string. 
#    * $description: Human-readable description of the process
#    for error messages for debugging. 
# Globals used (optional):
#    * $dbh: Database handle. If you set a global $dbh, then you can
#    call this function as q($sql) instead of q($dbh, $sql).
#    * $print_sql: If defined and nonzero, print the SQL query to STDERR. 
#    * $print_time: If defined and nonzero, print query running time to STDERR. 
sub qry {
	my $q_dbh; 
	my $sql;
	my $desc;
	{ # Handle arguments 
		my $arg1 = shift;
		my $arg1_type = ref($arg1);
		if(! $arg1_type){ # ref("scalar") is undefined
			if(defined($dbh)){ # Use global $dbh 
				my $dbh_type = ref($dbh);
				if($dbh_type eq "DBI::db"){
					$q_dbh = $dbh;
					$sql = $arg1;
				} else {
					die("qry: Global \$dbh (type $dbh_type) is not a database handle");
				}
			} else {
				die("qry: No database handle for scalar argument ($arg1)");
			}
		} elsif($arg1_type eq "DBI::db"){
			$q_dbh = $arg1;
			$sql = shift;
		} else {
			die("qry: unknown type $arg1_type passed to function qry()");
		}
		$desc = shift;
		if(!$sql){
			die("qry: No sql");
		}
		if(!$desc){ 
			$desc = $sql; 
			$sql =~ /([^\n]*)/m;
			$desc = $1;
		}
	}
	my $time_pre = time(); 
	if(defined($print_sql)){ # check for global print_sql 
		if($print_sql != 0){
			print STDERR "qry: $desc:\n$sql\n";
		}
	}
	# Run the two lines that this 50-line wrapper is meant to replace 
	my $query = $dbh->prepare($sql) or die "qry: Couldn't $desc: $!\ndbh prepare failed\n"; 
	$query->execute() or die "qry: Couldn't $desc: $!\nQuery that failed was:\n$sql";
	
	my $time_post = time(); 
	if(defined($print_time)){ # check for global print_time 
		if($print_time != 0){
			print STDERR "Running time: " . int($time_post - $time_pre) . " seconds\n";
		}
	}
	# Return query so it can be used by fetchrow functions 
	return $query;
}

##### Begin report producing logic 

@errs = ();   # List of errors
$report = ""; # final version of report -- set at end of program
$txtReport = "";   # txt version of report 
# html version of report 
$htmlReport = <<EOHTML;
<html><head><title>Support Center Phone Summary</title>
<style type="text/css">
table { border: 2px inset silver; }
td { border: 2px inset silver; }
th { border: 2px inset silver; }
tr.even { background-color: lightgrey; }
tr.odd { background-color: white; }
div.scalarReport {
	padding: 1em;
}
div.scalarReport p {
	padding-top: 0pt; padding-bottom: 0pt; 
	margin-top: 0pt; margin-bottom: 0pt;
}
</style>
</head>
<body>
EOHTML

$lspace = "    "; # Leading space for text reports (why not use a tab?)


# 20080218 -- wrapped push(@errs) in a function so we can extend it.
sub recordError{
	my $errData = shift;
	push(@errs, $errData); 
	return;
}


# Grotesque hack to show empty days in weekly report
$tmpDay =  $DAY_START - 1; 
$dayIndex = 0; 
@allDates = (); @allDays = ();
while($tmpDay >= $DAY_END){

	my $sqlTemp ="SELECT
	DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL $tmpDay DAY), '%c-%d') as \"Date\",
	DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL $tmpDay DAY), '%a') as \"Day\"
	FROM CDRMAIN LIMIT 0,1;";
	my $tmpQ = qry($sqlTemp, "Detecting empty dates");
	while(@arr = $tmpQ->fetchrow_array){
		$allDates[$dayIndex] = $arr[0];
		$allDays[$dayIndex] = $arr[1];
	}

	$tmpDay -= 1; 
	$dayIndex += 1; 
}



# 20080218 -- Revised output backend functions and associated variables. 
# Goals:
#   * Only writeTable() and scalarReport() will write to the reports
#   * Everything that needs to write to the reports will use these functions
# 
# Use a global "output context" variable so that consecutive scalarReports
# can be grouped in an html div. 
our $globOutputContext = ""; # Keep track of output contexts
# Every backend write function will call setOutputContext
sub setOutputContext {
	my $oldContext = $globOutputContext;
	$globOutputContext = shift;
	# Do nothing if the context has not changed
	if($oldContext eq $globOutputContext){ return; }
	# Handle special cases if the new and old contexts differ
	if($oldContext eq "scalarReport"){
		$htmlReport .= "</div>\n"; # End a scalarReport
	}
	if($globOutputContext eq "scalarReport"){
		$htmlReport .= "<div class=\"scalarReport\">\n";
	}
	# Add newline to text report on any context change
	$txtReport .= "\n";
	return;
}

# writeRecordTxt() -- Helper function for writeTable() to print a text
# record whether it has a matching format in %pFormats or not. An
# unformatted record is printed as tab-separated variable.
# Arguments:
#   $_[0] - reference to an array representing the record
#   $_[1] - a printf format OR undefined.
# TODO: Check for wrong number of strings in pFormat vs recordPtr
sub writeRecordTxt {
	my $recordPtr = shift;
	my $pFormat = shift;
	my @record = @{$recordPtr};
	if($pFormat){
		$txtReport .= sprintf($pFormat, @record);
	} else {
		$txtReport .= shift(@record); 
		foreach $i (@record){
			$txtReport .= "\t$i";
		}
	}
	$txtReport .= "\n";
	return;
}
# printf formats for plain text reports:
%pFormats = (
	# (all format keys are in lowercase)
	"daytable",	'%-6s %-6s %-5s %-6s',
	"longcalls",	'%6s %4s %15s %6s %-15s %s ',
	"listbyhour",	'%6s %5s %5s %6s %-15s %s',
	"byacctcode",   '%-11s %6s  %-6s  %2s',
	"list2",	'%3s -- %s'
); # (html reports will just use the key as a class name and CSS it)


# writeTable -- Function to write a table, any table, to both
# the plain-text and HTML reports. Extendable by creating a new
# printf format in the %pFormats hash. 
# Returns the number of records retrieved and printed. 
# Arguments are:
# [0] -- $queryPtr -- Pointer to the query used. Assumes it has been
# tested and works. #TODO: Do not make that assumption.
# [1] -- $formatClass -- a key to the global %pFormats hash
# of printf format strings. Also used to set the table's class.
# [2] -- $ptrHeaders -- Reference to an [array,of,headers] passed in
# using square brackets.  


# Helper for writeTable. Print one record.
sub writeTableRecord{
	my($recordPtr, $pFormat, $recordIndex, $isHeader) = @_;
	my @record = @{$recordPtr}; 
	if(!$recordIndex){ $recordIndex=0; }
	my $TD="td"; 
	if($isHeader){ $TD="th"; }
	# Text version
	writeRecordTxt(\@record, $pFormat);
	# HTML version
	my $trClass ="";
	if(! $isHeader){
		# Support alternate row coloring 
		$trClass="odd";
		if($recordIndex %2 ){
			$trClass="even";
		}
		$trClass =" class=\"$trClass\"";
	}
	$htmlReport .= "<tr$trClass>";
	foreach $i(@record){ $htmlReport .= "<$TD>$i</$TD>"; }
	$htmlReport .= "</tr>\n";
}

sub writeTable{
	setOutputContext("table");
	my $numRecords = 0; 
	my ($queryPtr, $formatClass, $ptrHeaders) = @_;

	my @headers = @{$ptrHeaders}; 

	# tolower and sanitize the $formatClass string. 
	$formatClass = lc($formatClass); 
	while($formatClass =~ /[">]/){ $formatClass =~ s/[">]/_/g;}
	my $pFormat = 0; # Default
	if (exists($pFormats{$formatClass})){
		$pFormat = $pFormats{$formatClass}; 
		# print "DEBUG: Got format $pFormat\n";
	}# else { print "DEBUG: No match for format $formatClass\n"; }

	# Support the ugly daytable hack for days with zero records
	# (if we figure out how to print records for empty days from SQL,
	# we could get rid of this) 
	my $doDayHack = 0;
	if ($formatClass eq "daytable"){
		$doDayHack = 1;
	}
	
	# Pre-header preparations...
	$htmlReport .= "<table class=\"$formatClass\">\n";

	# Print the headers.
	if(@headers > 0){
		writeTableRecord(\@headers, $pFormat, 0, 1);
	} else { 
		#print("DEBUG: Empty header list received by writeTable\n");
		#recordError("Warning: Empty header list received by writeTable");
		; # Not an error; intentional.
	}

	# Print contents
	while(my @record = $queryPtr->fetchrow_array){
		if($doDayHack){
			my $date=$record[0]; 
			while(($numRecords < @allDates) and ($allDates[$numRecords] ne $date)){
				writeTableRecord([$allDates[$numRecords],$allDays[$numRecords], "0", " -:--"], $pFormat, $numRecords);
				$numRecords += 1;
			}
		}  # end grotesque hack

		writeTableRecord(\@record, $pFormat, $numRecords);

		$numRecords += 1; 
	}
	# More of the daytable hack -- print empty records at end of period
	if($doDayHack && ($numRecords > 0)){
		while($numRecords < $NUM_DAYS){
			writeTableRecord([$allDates[$numRecords],$allDays[$numRecords], "0", " -:--"], $pFormat, $numRecords);
			$numRecords += 1;
		}
	}

	
	# Post-contents wrap-up
	$txtReport .= "\n";
	$htmlReport .= "</table>\n";

	return $numRecords; 
}


sub scalarReport{
	setOutputContext("scalarReport"); 
	my $sql = shift;
	my $desc = shift;
	my $q=qry($sql, $desc); 
	my ($result) = $q->fetchrow_array();
	my $str = "$desc: $result";
	$htmlReport .= "<p>$str</p>\n";
	$txtReport .= "$lspace$str\n";
}
sub printSeparator {
	setOutputContext(""); # Clear the output context
	$txtReport .= "\n" . "#" x 40 . "\n";
	$htmlReport .= "<hr/>\n";
}

# dayTable: Print a table by days.  
# functionized to avoid repeating 1/5 of this file for 3 similar tables
# re-written to use writeTable. 
sub dayTable{
	my($sqlWhere, $allHeader, $qtypeHACK) = ($_[0],$_[1], $_[2]);
	my $header  = ['Date', 'Day', 'Calls', ' Avg. Time'];

	# Here I go violating my own coding standards, but this is not
	# handled by writeTable and probably shouldn't be. 
	$txtReport .= "\n$allHeader:\n\n";
	$htmlReport .= "<p>$allHeader:</p>\n";

	my $numRecords = writeTable(qry(sqlSupportGroupTable($sqlWhere, $qtypeHACK)), "daytable", $header);

	my $whereRange = "$WHERECALL_DATE_INRANGE and ". whereTime($qtypeHACK);
	my $bigWhere = "$whereRange and $sqlWhere"; 

	scalarReport(sqlNumCalls($bigWhere), "Total number of calls");
	scalarReport(sqlAvgCallLen($bigWhere), "Average call time");
	scalarReport("SELECT concat(
	concat(FLOOR(a.SumMin/60),'h '), 
	concat(MOD(a.SumMin, 60), 'min')
	) FROM (" . sqlSumMinutes($bigWhere) . ") as a", "Total time");

	# Special query run for inbound calls: count transfers  
	if($sqlWhere eq $WHERECALL_SupportGroup_IN){
		scalarReport(sqlNumXFers($WHERECALL_DATE_INRANGE), "Transfers to Answering Service"); 
	}
}


# Another function to replace repeated code 
# takes $sql, $header, and boolean for whether to display Code field
sub listReport{
	my($sql) = shift();
	my($sqlDesc) = shift();

	# HACK: Boolean for if the SQL includes an account code column
	my($usingCode) = shift();

	my $header = ["Date", "Day", "Name", "Time", "Number Dialed"]; 

	# Set the format class. 
	my $formatClass = "longCalls"; 

	# HACK: Alternate header for different report. 
	if($sql eq $sql_callsByHour){
		$formatClass = "listByHour";
		$header = ['Hour', '', 'hr:mm', 'Calls', 'mm:ss per call'];
	}

	# HACK: Add extra account code column
	if($usingCode){push(@{$header}, "Code");}
	else { push(@{$header}, "");} # HACK: keep printf from choking on extra %s

	# Describe the report
	$txtReport .= "\n$sqlDesc:\n";
	$htmlReport .= "<p>$sqlDesc:</p>\n";
	writeTable(qry($sql, "list report $sqlDesc"), $formatClass, $header);
}

 


sub incallHoursReport {
	my $sql = "
SELECT DATE_FORMAT(FROM_UNIXTIME(EndTime),'\%a') as \"DAY\",
sum(".whereHours("'0:00'","'6:00'")."),
sum(".whereHours("'6:00'","'8:00'")."),
sum(".whereHours("'8:00'","'10:00'")."),
sum(".whereHours("'10:00'","'12:00'")."),
sum(".whereHours("'12:00'","'14:00'")."),
sum(".whereHours("'14:00'","'18:00'")."),
sum(".whereHours("'18:00'","'23:59:59'")."),
count(*)
FROM CDRMAIN as maincdr 
WHERE
$WHERECALL_DATE_INRANGE
AND $WHERECALL_TIME_OK"
# AND $WHERECALL_SupportGroup_IN 
."
AND TargetWGNum in (2088, 2099)
GROUP BY DAY
ORDER BY EndTime
;";
	my $desc = "Generate by-hours report";
	my @headers = ('','0-6h','6-8h','8-10h','10-12h','12-14h','14-18h','18-24h','Total');
	
	$txtReport .= "Inbound SupportGroup calls by hour:\n";
	$htmlReport .= "<p>Inbound SupportGroup calls by hour:</p>\n";
	writeTable (qry($sql, $desc), "", \@headers); 
}


dayTable $WHERECALL_SupportGroup, "Inbound and outbound calls by day";
dayTable $WHERECALL_SupportGroup_IN, "Inbound calls by day";
dayTable $WHERECALL_SupportGroup_OUT, "Outbound calls by day";
dayTable $WHERECALL_SupportGroup, "Calls in queue by day", "QUEUE";
dayTable $WHERECALL_SupportGroup, "Calls on hold by day", "HOLD";


listReport($sql_longOutCalls,"Longest 10 outbound calls", 0);

printSeparator();

listReport($sql_callsByHour, "All SupportGroup calls by hour");
printSeparator();

incallHoursReport(); # Run hours report

# Print a separator  
printSeparator();

sub acctCode {  

	my $topHeader = "Inbound and outbound calls by Account Code";
	$txtReport .= "\n$topHeader:\n";
	$htmlReport .= "<p>$topHeader:</p>\n";

	#my $str = "Account coded calls"; 
	#$txtReport .= "\n$lspace$str: $allCount\n";
	#$htmlReport .= "<p>$str: $allCount</p>\n";
	my $numRecords = writeTable(qry($sql_acctCode_2, "sql_acctCode2"),
	"byAcctCode", ['Contract', 'Code', 'Time', 'NumberOfCalls']);

	my $str = "Account coded calls"; 
	$txtReport .= "\n$lspace$str: $numRecords\n";
	$htmlReport .= "<p>$str: $numRecords</p>\n";

}
acctCode();

#$txtReport .= "\nList of calls with no code:\n";
#$htmlReport .= "<p>List of calls with no code:</p>\n";
#listReport($sql_NoCodeCallsInbound, "Inbound",0);
#listReport($sql_NoCodeCallsOutbound, "Outbound",0);

printSeparator();

sub List2 {
	my $sql = shift;
	my $header = shift;
	$txtReport .= "\n$header:\n";
	$htmlReport .= "<p>$header:</p>\n";
	writeTable(qry($sql, $header), "list2");
}

List2($sql_NoCodeCallsInboundSummary, "Number of inbound calls with no account code"); 
List2($sql_NoCodeCallsOutboundSummary, "Number of outbound calls with no account code"); 

#listReport($sql_allSupportGroupCalls, "List of all calls in/out",1);
#listReport($sql_allSupportGroupCalls, "List of all calls",1);

if($show_errors == 1){
	if(@errs > 0){
		$htmlReport .= "<p>Errors:</p><ul class=\"errors\">\n";
		$txtReport .= "Error list:\n";
		foreach $e (@errs){ 
			$htmlReport .= "<li>$e</li>\n"; 
			$txtReport .= "* $e\n"; 
		}
		$htmlReport .= "</ul>\n";
	}
}



$htmlReport .= "</body></html>\n";

#### Finally, deliver the document

if ($format =~ /html/i){
	# MIME-ify HTML mail so it can be read in a reader
	my $mime_boundary = "aybabtu-o-rly-ya-rly-no-wai";
$report =<<EOF_MIMEMAIL;
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary=\"$mime_boundary\"
Content-Transfer-Encoding: 7bit

$txtReport

--$mime_boundary
Content-Type: text/plain; charset=\"US-ASCII\"
Content-Transfer-Encoding: 7bit

$htmlReport
.
EOF_MIMEMAIL

} else {
	$report = $txtReport;
}

if($CMD =~ /sendmail/){
	$report = $mailHeaders . $report;
}

open(OUTPUT, "|$CMD") or die "Error, can't open output\n$!"; 

print OUTPUT $report;