<!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"]["sqlserver_eprints"],
$GLOBALS["config_vars"]["connections"]["sqluser_eprints"],
$GLOBALS["config_vars"]["connections"]["sqlpass_eprints"]
);
if (!$connection_archive)
error($GLOBALS["config_vars"]["messages"]["message_noservice"] ,ERROR_EXIT);
$db_archive = $sql_archive->doConnectDb($GLOBALS["config_vars"]["connections"]["sqldatabase_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>
