" selected=\"selceted\"",
FALSE => ""
); //end array "selected"
$dropdown = " \n";
$dropdown .= " \n";
mysql_free_result($result);
return $dropdown;
} //end function DropDownElement
function dbTable($show, $year) {
//select the proper database table for the selected report
if ($show == "download") {
$tbl = "`JR1_".$year."_COUNTER`";
}else {
$tbl = "`DB1_".$year."_COUNTER`";
} //end if
return $tbl;
} //end function dbTable
function validComparison ($yearArray) {
//check to see if we're comparing a base year with any other year(s)
$year = $yearArray[0];
$start = $yearArray[1];
$end = $yearArray[2];
//it's not a valid comparison if we're only comparing against the selected year.
return $valid = !(($end == $year) && ($start == $year));
} //end function validComparison
function organize($year, $start, $end) {
//organize the years in ascending order regardless of how they were entered
//while we're at it, we'll also set "none" to equal the comparison base year
if ($start == "none") $start = $year;
if ($end == "none") $end = $year;
if (($start == $end) && ($start != $year)) $start = $year;
$diff = $start - $end;
$years[0] = $year;
if ($diff > 0) {
$years[1] = $end;
$years[2] = $start;
}else{
$years[1] = $start;
$years[2] = $end;
} //end if
return $years;
} //end function organize
function ytd($show, $year, $total, $table, $compare, $sq) {
//create the monthly total columns for single-year reports and also
//the year-to-date total columns
$ytd = "";
if (!$compare) {
date_default_timezone_set("America/Chicago");
$start = $month = strtotime("$year-01-01");
$end = strtotime("$year-12-31");
while($month < $end) {
$strMonth = date('M', $month);
//echo "$strMonth, ";
$ytd .= ($sq) ? " SUM($table.`$strMonth`) AS `$strMonth $year`," : " $table.`$strMonth $year`,";
//$ytd .= " SUM($table.$strMonth) AS `$strMonth $year`,";
$month = strtotime("+1 month", $month);
} //end while
} // end if
if ($show == "download") {
$ytd .= ($sq) ? " SUM($table.`Total HTML`) AS `$year Total HTML`," : " $table.`$year Total HTML`,";
$ytd .= ($sq) ? " SUM($table.`Total PDF`) AS `$year Total PDF`," : " $table.`$year Total PDF`,";
} //end if
$ytd .= ($sq) ? " SUM($table.`Total`) AS `$year Total`" : " $table.`$year Total`";
return $ytd;
} //end function ytd
function sqlWhere($show, $group, $year, $total) {
//create the WHERE part of the SQL Query
$where = "";
$table = dbTable($show, $year);
$blank = "$table.`$group` IS NOT NULL AND $table.`$group` <> '' ";
$task = "$table.`task` LIKE '%$show%'";
if ($total) {
if ($show != "download") $where = " WHERE $task";
}else {
if ($show != "download") $where = " WHERE $task";
if ($where =="") {
$where = " WHERE $blank";
}else{
$where .= " AND $blank";
}//end if
} //end if
return $where;
} //end function sqlWhere
function makeQuery($show, $by, $yearArray, $total, $compare) {
//create the SQL query
//in the single case of showing a download (journal) report sorted by title, we need to group results by ISSN
//otherwise we group them on the same key we sort them by
$group = ($by=="title" && $show=="download") ? "Print ISSN" : $by;
$sel = "SELECT `sq".$yearArray[0]."`.`$by`, ";
//we need to loop through each year & create a sub query so we can run a comparison if requested
//if only a single year was selected, only one subquery is created & is used by itself
$year = $yearArray[1];
for ($year=$yearArray[1]; $year<=$yearArray[2]; $year++) {
$tbl = dbTable($show, $year);
$subQuery[$year] = ($total) ? "SELECT 'TOTAL' AS `$by`," : "SELECT $tbl.`$by`,";
$subQuery[$year] .= ($compare && (($show=="download") && ($by!=$group))) ? " $tbl.`$group`, " : "";
$subQuery[$year] .= ytd($show, $year, $total, $tbl, $compare, TRUE);
$subQuery[$year] .= " FROM $tbl";
$subQuery[$year] .= sqlWhere($show, $group, $year, $total);
$subQuery[$year] .= ($total) ? "" : " GROUP BY $tbl.`$group` ORDER BY $tbl.`$by` ASC";
//$subQuery[$year] .= ") AS `sq$year`";
$sel .= ytd($show, $year, $total, "`sq$year`", $compare, FALSE).",";
$leftJoin[$year] = " ON `sq$yearArray[0]`.`$by` = `sq$year`.`$by`";
} //next $year
$sel = substr($sel, 0, strlen($sel)-1)." FROM (";
//join the subqueries if we're comparing multiple years
if ($compare) {
//need to build left joins
$sql = $sel.$subQuery[$yearArray[0]].") AS `sq".$yearArray[0]."`";
for ($year=$yearArray[1]; $year<=$yearArray[2]; $year++) {
//we can't compare with base year, so skip this one
if ($year != $yearArray[0]) {
$sql .= " LEFT JOIN (".$subQuery[$year].") AS `sq$year` ".$leftJoin[$year];
} //end if
} //next $year
}else{
//it's a single year query--all we need is the subquery
$sql = $subQuery[$yearArray[0]];
} //end if
return $sql;
} //end function makeQuery
function showStats($show, $by, $year, $start, $end) {
//normalize the years
$yearArray = organize($year, $start, $end); //this returns an array ($year, minimum, maximum)
$compare = validComparison($yearArray);
//construct & submit the total query
$total = TRUE;
$sql = makeQuery($show, $by, $yearArray, $total, $compare);
$result = mysql_query($sql);
if (!$result) {
die("Query to show Total row failed Here's the query: $sql ");
} //end if
//start printing table
$fields_num = mysql_num_fields($result);
echo "