Newer
Older
Digital_Repository / Repositories / statistics / includes / inc.class.mysql.es.php
nstanger on 26 Nov 2005 9 KB - Added makefile.
<?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;
		}
	
	}

/* 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"];
		}
	}

}

?>