<!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 organisational unit</title> <meta name="generator" content="BBEdit 8.2" /> </head> <body> <h1>Downloads by organisational unit</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) ORDER BY S.name"; $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>Organisational unit</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>