<?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
$query = "
select count(*) as count,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
$query = 'select count(*) as count,country_name,country_code from view ' .
$limit2 .
'group by country_name order by count desc';
$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 --------------------------------------------------
$query = "
select count(*) as count,archiveid,eprint_name from view
$date_range
group by archiveid order by count desc
";
$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'";
$query = "
select count(*) as count, archiveid, eprint_name, country_name from view
$restriction
group by archiveid order by count desc
";
$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)";
}
$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';
$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 = '';
if($year>=2002 and $year<2005) {
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)";
}
$query = "
select count(*) as count,view_type from view
WHERE archiveid = $id
$date_range
group by view_type order by count desc
";
$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 timeinsert 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"];
}
}
}
?>
