<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
        "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
	<title>Downloads by department</title>
	<meta name="generator" content="BBEdit 8.2" />
</head>
<body>

<h1>Downloads by department</h1>

<?php
require("inc.vars.es.php"); 
require("inc.fns.es.php");
require("inc.class.mysql.es.php");

$sql_archive = new sqlconn();
/* $sql->setDebug($GLOBALS["config_vars"]["debug"][$GLOBALS["config_vars"]["thisrequest"]]); */

$connection_archive = $sql_archive->doConnectServer
	(	$GLOBALS["config_vars"]["connections"]["eprints_archives"]["otago_eprints"]["sqlserver_eprints"],
		$GLOBALS["config_vars"]["connections"]["eprints_archives"]["otago_eprints"]["sqluser_eprints"],
		$GLOBALS["config_vars"]["connections"]["eprints_archives"]["otago_eprints"]["sqlpass_eprints"]
	);
if (!$connection_archive) 
	error($GLOBALS["config_vars"]["messages"]["message_noservice"] ,ERROR_EXIT);

$db_archive = $sql_archive->doConnectDb($GLOBALS["config_vars"]["connections"]["eprints_archives"]["otago_eprints"]["sqluser_eprints"]);
if (!$db_archive)
{
	error($GLOBALS["config_vars"]["messages"]["message_noservice"],ERROR_EXIT);
	exitScript();
}

$sql_stats = new sqlconn();

$connection_stats = $sql_stats->doConnectServer
	(	$GLOBALS["config_vars"]["connections"]["sqlserver"],
		$GLOBALS["config_vars"]["connections"]["sqluser_public"],
		$GLOBALS["config_vars"]["connections"]["sqlpass_public"]
	);
if (!$connection_stats) 
	error($GLOBALS["config_vars"]["messages"]["message_noservice"] ,ERROR_EXIT);

$db_stats = $sql_stats->doConnectDb($GLOBALS["config_vars"]["connections"]["sqldatabase"]);
if (!$db_stats)
{
	error($GLOBALS["config_vars"]["messages"]["message_noservice"],ERROR_EXIT);
	exitScript();
}

$query = 
"SELECT TRIM(TRAILING ' ' FROM S.name) AS name, E.eprintid AS eprintid
 FROM archive E INNER JOIN archive_affiliations A USING (eprintid)
                INNER JOIN subject_name S ON (A.affiliations = S.subjectid)
 WHERE S.name LIKE '% '
 ORDER BY S.name"; /* Note use of non-breaking space (UTF8 00A0) in name column. */

$current_dept = "";
$idlist = "";
$dept_count = 0;
$result = mysql_query($query, $connection_archive);

if (!$result)
{
	error($GLOBALS["config_vars"]["messages"]["message_noservice"],ERROR_EXIT);
	print "<p>failed departments query</p>";
	exitScript();
}
else
{
	print "<table border='1'><tr>
			<th>Department</th>
			<th>eprints</th>
			<th>Abstract views</th>
			<th>Downloads</th>";
	while ($row = mysql_fetch_assoc($result))
	{
		if ($row["name"] != $current_dept)
		{
			print_new_row($connection_stats, $current_dept, $dept_count, $idlist);
			$current_dept = $row["name"];
			$dept_count = 0;
			$idlist = "";
		}
		$dept_count++;
		$idlist .= $row["eprintid"] . ',';
	}
	print_new_row($connection_stats, $current_dept, $dept_count, $idlist);
	print "</tr></table>";
}

function print_new_row ($conn, $dept, $count, $idlist)
{
	if ($dept != "")
	{
		print "</tr><tr>";
		print "<td>" . $dept . "</td>";
		print "<td align='center'>" . $count . "</td>";
		print_downloads($conn, rtrim($idlist, ','));
	}
}

function print_downloads ($conn, $idlist)
{
	$query = 
	"SELECT COUNT(*) AS num
	 FROM view
	 WHERE view.archiveid IN ($idlist)
	   AND view.view_type = ";
	
	$viewtypes = array('abstract', 'download');
	
	foreach ($viewtypes as $type)
	{
		$result = mysql_query($query . "'" . $type . "'", $conn);
	
		if (!$result)
		{
			print "<td align='center'>failed</td>";
		}
		else
		{
			$row = mysql_fetch_assoc($result);
			print "<td align='center'>" . $row["num"] . "</td>";
		}
	}
}

?>


</body>
</html>
