<?php
/**
* Project: ePrintstats
* File: inc.class.mysql.es.php
* Description: All SQL interactions via this object.
* This includes connections, adding, updating, inserting.
*/
class sqlconn {
var $_mysql_error = array();
var $_database;
var $_host_info;
var $_debug = FALSE;
var $_debugAll = array();
function doConnectServer($sqlserver,$sqluser,$sqlpass)
{
$connect = mysql_pconnect ($sqlserver,$sqluser,$sqlpass)
or sqlconn::_setError(mysql_errno(),mysql_error());
$db_list = mysql_list_dbs($connect);
while ($row = mysql_fetch_object($db_list)) {
$this->_db_list[] = $row->Database;
}
$this->_host_info = sprintf ("MySQL host info: %s\n", mysql_get_host_info());
return $connect;
}
function doConnectDb($sqldatabase)
{
$db = mysql_select_db($sqldatabase)
or sqlconn::_setError(mysql_errno(),mysql_error());
sqlconn::_setError(mysql_errno(),mysql_error());
$this->_database = $sqldatabase;
return $db;
}
function closeConnection()
{
mysql_close();
}
function getHostInfo()
{
return $this->_host_info;
}
function setDebug($debuglevel)
{
$this->_debug = $debuglevel;
}
function getDebug()
{
$_outstring = '';
foreach ($this->_debugAll as $k=>$v) {
$_outstring .= $v;
}
return $_outstring;
}
function getError()
{
return $this->_mysql_error;
}
/*
* @return void
* @param $input unknown
* @desc HTML output debug info
* @usage sqlconn::_debug($this->_db_schema);
*/
function _debug($output,$line,$file,$function,$class)
{
ob_start();
print "<div class=\"debug\">\n";
print "<pre>\n";
if (!empty($line)) { print "LINE: $line\n"; };
if (!empty($file)) { print "FILE: $file\n"; };
if (!empty($function)) { print "FUNCTION: $function\n"; };
if (!empty($class)) { print "CLASS: $class\n"; };
//if (!empty($method)) { print "METHOD: $method\n"; }; // Only > PHP5
print_r($output);
print "</ pre></div>";
$ret_str = ob_get_contents();
ob_end_clean();
$this->_debugAll[] = $ret_str;
}
function _setError($mysql_errno,$mysql_error)
{
/*
* May be of limited use where a method makes multiple calls
* and one error message is replaced by another.
*/
$this->_mysql_error["code"] = $mysql_errno;
$this->_mysql_error["text"] = $mysql_error;
}
/********************************************
*
*
* Program specific
*
**********************************************/
/* ------------------------------------------------------------------------------
This function is a rewritten version of getCumulativeUsage, to optionally
select the view_type.
(c) Copyright 2004 Arthur Sale, University of Tasmania
Parameters:
id # of eprint
strings 'abstract', 'download' or anything else acts as view=abstract+download
Returns:
Result array from query or failure
------------------------------------------------------------------------------ */
function getCumulativeUsageType($id,$type)
{
// Formulate the id part of the select
$id = (int) $id;
$part1 = ($id > 1);
if ($part1) {
$limit1 = "archiveid = $id";
} else {
$limit1 = '';
}
// Formulate the view part of the select
if ($type == 'download') {
$limit2 = 'view_type = "download"';
$part2 = true;
} elseif ($type == 'abstract') {
$limit2 = 'view_type = "abstract"';
$part2 = true;
} else {
$limit2 = '';
$part2 = false;
}
// Combine the parts as needed
if ($part1 and $part2) {
$limit = $limit1 . ' AND ' . $limit2;
} else {
$limit = $limit1 . $limit2;
}
if ($part1 or $part2) {
$limit = 'WHERE ' . $limit;
}
// Create a result array
$records = array();
//Build up the query
// NJS 2006-01-18: Added count of country_code.
$query = "
SELECT COUNT(*) AS count, COUNT(DISTINCT country_code) AS countries, MONTH(request_date) AS monthnum, DATE_FORMAT(request_date, '%b') AS month, YEAR(request_date) AS year FROM view
$limit
GROUP BY YEAR(request_date), MONTH(request_date) ORDER BY request_date DESC
";
// Make the query
$result = mysql_query($query);
if (!$result) {
sqlconn::_setError(mysql_errno(),mysql_error());
return mysql_error();
} else {
while ($row = mysql_fetch_assoc($result)) {
$records[] = $row;
}
return $records;
}
}
/* ------------------------------------------------------------------------------
This function is a rewritten version of getCumulativeUsageCountry, to optionally
select the view_type.
(c) Copyright 2004 Arthur Sale, University of Tasmania
Parameters:
id # of eprint
strings 'abstract', 'download' or anything else acts as view=abstract+download
Returns:
Result array from query or failure
------------------------------------------------------------------------------ */
function getCumulativeUsageCountryType($type)
{
// Formulate the view part of the select
if ($type == 'download') {
$limit2 = 'WHERE view_type = "download" ';
} elseif ($type == 'abstract') {
$limit2 = 'WHERE view_type = "abstract" ';
} else {
$limit2 = '';
}
// Create a result array
$records = array();
//Build up the query
// NJS 2006-05-02: Fixed typo in ORDER BY and added ascending order of country
// name for countries with the same number of accesses.
$query = 'SELECT COUNT(*) AS count, country_name, country_code FROM view ' .
$limit2 .
'GROUP BY country_name ORDER BY count DESC, country_name ASC';
$result = mysql_query($query);
if (!$result) {
sqlconn::_setError(mysql_errno(),mysql_error());
return mysql_error();
} else {
while ($row = mysql_fetch_assoc($result)) {
$records[] = $row;
}
return $records;
}
}
/* ------------------------------------------------------------------------------ */
function getArchiveCountDate($year,$month,$range,$type='download')
{
$year = (int) $year;
$month = (int) $month;
$records = array();
// create a date based limit
$date_range = '';
if($year>=2002 and $year<2100) {
if($month>0 and $month<=12) {
$date_range = "WHERE YEAR(request_date) = $year AND MONTH(request_date) = $month";
} else {
$date_range = "WHERE YEAR(request_date) = $year";
}
}
if($range=='4w') {
$date_range = "WHERE request_date >= date_sub(curdate(),INTERVAL 1 MONTH)";
}
// Constrain the view to downloads - Arthur Sale addition -----------
if ($date_range != '') {
$date_range = $date_range . ' AND view_type = "download"';
} else { // empty
$date_range = 'WHERE view_type = "download"';
}
// End of addition --------------------------------------------------
// NJS 2006-01-18: Added count of country_code.
// NJS 2003-05-02: Added descending order of number of countries and
// ascending order of title for eprints with the same number of
// downloads.
$query = "
SELECT COUNT(*) AS count, COUNT(DISTINCT country_code) AS countries, archiveid, eprint_name FROM view
$date_range
GROUP BY archiveid ORDER BY count DESC, countries DESC, eprint_name ASC
";
$result = mysql_query($query);
if (!$result) {
sqlconn::_setError(mysql_errno(),mysql_error());
return mysql_error();
} else {
while ($row = mysql_fetch_assoc($result)) {
$records[] = $row;
}
return $records;
}
}
/*
NJS 2005-12-15
Similar to getArchiveCountDate but does it by country instead.
*/
function getArchiveCountCountry($ccode,$type='download')
{
$records = array();
// Create a country based limit. Also constrain to downloads only.
$restriction = "WHERE country_code = '$ccode' AND view_type = 'download'";
// NJS 2003-05-02: Added ascending order of title for eprints with
// the same number of downloads.
$query = "
SELECT COUNT(*) AS count, archiveid, eprint_name, country_name FROM view
$restriction
GROUP BY archiveid ORDER BY count DESC, eprint_name ASC
";
$result = mysql_query($query);
if (!$result) {
sqlconn::_setError(mysql_errno(),mysql_error());
return mysql_error();
} else {
while ($row = mysql_fetch_assoc($result)) {
$records[] = $row;
}
return $records;
}
}
/* New */
function getCountryEprintType($year,$month,$rnge,$type,$id)
{
$year = (int) $year;
$month = (int) $month;
$records = array();
// Generate type enquiry
if ($type == 'download') {
$limit2 = ' AND view_type = "download" ';
} elseif ($type == 'abstract') {
$limit2 = ' AND view_type = "abstract" ';
} else {
$limit2 = '';
}
// generate date enquiry
$date_range = '';
if($year>=2002 and $year<2100) {
if($month>0 and $month<=12) {
$date_range = " AND year(request_date) = $year AND month(request_date) = $month";
} else {
$date_range = " AND year(request_date) = $year";
}
}
if($rnge=='4w') {
$date_range = " AND request_date >= date_sub(curdate(),INTERVAL 1 MONTH)";
}
// NJS 2006-05-02: Added ascending order of country
// name for countries with the same number of accesses.
$query =
'SELECT COUNT(*) AS count, country_name, country_code FROM view WHERE archiveid = ' .
$id .
$limit2 .
$date_range .
' GROUP BY country_name ORDER BY count DESC, country_name ASC';
$result = mysql_query($query);
if (!$result) {
sqlconn::_setError(mysql_errno(),mysql_error());
return mysql_error();
} else {
while ($row = mysql_fetch_assoc($result)) {
$records[] = $row;
}
return $records;
}
}
function getTitle($id)
{
// Get the title of an eprint given its id
$query = "SELECT eprint_name FROM view WHERE archiveid = '$id'";
$result = mysql_query($query);
if (!$result) {
sqlconn::_setError(mysql_errno(),mysql_error());
return;
} else {
$row = mysql_fetch_assoc($result);
return $row["eprint_name"];
}
}
function getAbstractDownload($year,$month,$range,$type='download',$id)
{
$year = (int) $year;
$month = (int) $month;
$records = array();
$date_range = '';
// NJS 2006-01-18: Fixed upper year bound (was 2005).
if($year>=2002 and $year<2100) {
if($month>0 and $month<=12) {
$date_range = "AND YEAR(request_date) = $year AND MONTH(request_date) = $month";
} else {
$date_range = "AND YEAR(request_date) = $year";
}
}
if($range=='4w') {
$date_range = "AND request_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)";
}
// NJS 2006-01-18: Added count of country_code.
// NJS 2006-05-02: Added ascending order of country code.
$query = "
SELECT COUNT(*) AS count, COUNT(DISTINCT country_code) AS countries, view_type from view
WHERE archiveid = $id
$date_range
GROUP BY view_type ORDER BY count DESC, country_code ASC
";
$result = mysql_query($query);
if (!$result) {
sqlconn::_setError(mysql_errno(),mysql_error());
return mysql_error();
} else {
while ($row = mysql_fetch_assoc($result)) {
$records[] = $row;
}
return $records;
}
}
function getLastProc()
{
// Get the time that the stats were last processed.
$query = "SELECT timeinsert FROM lastproc ORDER BY id DESC LIMIT 1;";
$result = mysql_query($query);
if (!$result) {
sqlconn::_setError(mysql_errno(),mysql_error());
return;
} else {
$row = mysql_fetch_assoc($result);
return $row["timeinsert"];
}
}
/*
NJS 2006-02-09 (moved here from inc.fns.show_detail_eprint.es.php)
Grab the title of an eprint from the repository. Only needed if
the title isn't already in the stats database, which should only
occur when an eprint hasn't been downloaded yet.
*/
function getEPrintTitle($eprint_id)
{
$eprint_title = '';
$conn_eprints = mysql_connect(
$GLOBALS["config_vars"]["connections"]["sqlserver_eprints"],
$GLOBALS["config_vars"]["connections"]["sqluser_eprints"],
$GLOBALS["config_vars"]["connections"]["sqlpass_eprints"]
);
$db_eprints = mysql_select_db(
$GLOBALS["config_vars"]["connections"]["sqldatabase_eprints"],
$conn_eprints
);
if (!$db_eprints) return $eprint_title;
$query_eprints = "select title from archive where eprintid = $eprint_id";
$result_eprints = mysql_query($query_eprints,$conn_eprints);
$row_eprints = mysql_fetch_assoc($result_eprints);
$eprint_title = trim($row_eprints["title"]);
$eprint_title = preg_replace("/\s+/"," ",$eprint_title);
mysql_close($conn_eprints);
return $eprint_title;
}
}
?>
