- <?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($archive,$id,$type)
- {
- // NJS 2006-06-14: Added archive name as argument.
-
- // Build up the components of the WHERE clause (refactored).
- $where_parts = array();
- $limit = '';
-
- // Formulate the archive name part of the select.
- $this->addArchiveNameCondition( $archive, $where_parts );
-
- // Formulate the id part of the select.
- $this->addArchiveIDCondition( $id, $where_parts );
-
- // Formulate the view part of the select.
- $this->addViewTypeCondition( $type, $where_parts );
-
- $limit = $this->buildWhereClause( $where_parts, 'AND' );
-
- // 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, monthnum
- ORDER BY year DESC, monthnum 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($archive,$type)
- {
- // NJS 2006-06-14: Added archive name as argument.
-
- // Build up the components of the WHERE clause (refactored).
- $where_parts = array();
- $limit = '';
-
- // Formulate the archive name part of the select.
- $this->addArchiveNameCondition( $archive, $where_parts );
-
- // Formulate the view part of the select.
- $this->addViewTypeCondition( $type, $where_parts );
-
- $limit = $this->buildWhereClause( $where_parts, 'AND' );
-
- /*
- A possible optimisation? One query to get all abstracts & downloads, which
- eliminates the need for the merge_countries function. Below takes about the
- same time as the two original queries.
-
- select country_code, country_name, max(country_downloads), max(country_abstracts)
- from (
- select case view_type when view_type = 'abstract' then count(*) else 0 end as country_abstracts,
- case view_type when view_type = 'download' then count(*) else 0 end as country_downloads,
- view_type,
- country_name,
- country_code
- from view
- where archive_name = 'otago_eprints'
- group by country_name, view_type
- ) as inside
- group by country_code, country_name
- order by country_downloads desc, country_name;
- */
-
- // 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
- $limit
- 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($archive,$year,$month,$range,$type='download')
- {
- // NJS 2006-06-14: Added archive name as argument.
-
- $year = (int) $year;
- $month = (int) $month;
- $records = array();
-
- // Build up the components of the WHERE clause (refactored).
- $where_parts = array();
- $limit = '';
-
- // Formulate the archive name part of the select.
- $this->addArchiveNameCondition( $archive, $where_parts );
-
- // create a date based limit
- $this->addYearCondition( $year, $where_parts );
- $this->addMonthCondition( $month, $where_parts );
- // Note that year(+month) is mutually exclusive of "4w" (last four
- // weeks), but we don't check for this. (The original code didn't
- // check either.)
- $this->addFourWeeksCondition( $range, $where_parts );
-
- // Constrain the view to downloads - Arthur Sale addition -----------
- $this->addViewTypeCondition( 'download', $where_parts );
- // End of addition --------------------------------------------------
-
- // Filter out unknown and deleted eprints.
- $this->addDeletedFilter( $where_parts );
-
- $limit = $this->buildWhereClause( $where_parts, 'AND' );
-
- // NJS 2006-01-18: Added count of country_code.
- // NJS 2006-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,
- archive_name,
- eprint_name
- FROM view
- $limit
- GROUP BY archiveid, archive_name
- 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
-
- getArchiveCountCountry()
- arguments:
- $archive archive name
- $ccode country code
- $type eprint view type (e.g., download)
- returns: array
-
- Similar to getArchiveCountDate but does it by country code instead
- of by date.
- */
- function getArchiveCountCountry($archive,$ccode,$type='download')
- {
- // NJS 2006-06-14: Added archive name as argument.
-
- $records = array();
-
- // Build up the components of the WHERE clause (refactored).
- $where_parts = array();
- $limit = '';
-
- // Create a country based limit. Also constrain to downloads only
- // in the appropriate archive.
- $this->addArchiveNameCondition( $archive, $where_parts );
- $this->addStringCondition( 'country_code', '=', $ccode, $where_parts );
- $this->addViewTypeCondition( 'download', $where_parts );
-
- // Filter out unknown and deleted eprints.
- $this->addDeletedFilter( $where_parts );
-
- $limit = $this->buildWhereClause( $where_parts, 'AND' );
-
- // NJS 2003-05-02: Added ascending order of title for eprints with
- // the same number of downloads.
- $query = "
- SELECT COUNT(*) AS count,
- archiveid,
- archive_name,
- eprint_name,
- country_name
- FROM view
- $limit
- GROUP BY archiveid, archive_name
- 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($archive,$year,$month,$rnge,$type,$id)
- {
- // NJS 2006-06-14: Added archive name as argument.
-
- $year = (int) $year;
- $month = (int) $month;
- $records = array();
-
- // Build up the components of the WHERE clause (refactored).
- $where_parts = array();
- $limit = '';
-
- $this->addArchiveNameCondition( $archive, $where_parts );
- $this->addArchiveIDCondition( $id, $where_parts );
-
- // Generate type enquiry
- $this->addViewTypeCondition( $type, $where_parts );
-
- // generate date enquiry
- $this->addYearCondition( $year, $where_parts );
- $this->addMonthCondition( $month, $where_parts );
- // Note that year(+month) is mutually exclusive of "4w" (last four
- // weeks), but we don't check for this. (The original code didn't
- // check either.)
- $this->addFourWeeksCondition( $rnge, $where_parts );
-
- $limit = $this->buildWhereClause( $where_parts, 'AND' );
-
- // 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
- $limit
- 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($archive,$id)
- {
- // Get the title of an eprint given its id
- // NJS 2006-06-14: Added archive name as argument.
-
- // Build up the components of the WHERE clause (refactored).
- $where_parts = array();
- $limit = '';
-
- $this->addArchiveNameCondition( $archive, $where_parts );
- $this->addArchiveIDCondition( $id, $where_parts );
-
- $limit = $this->buildWhereClause( $where_parts, 'AND' );
-
- $query = "
- SELECT eprint_name
- FROM view
- $limit
- ";
- $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($archive,$year,$month,$range,$type='download',$id)
- {
- // NJS 2006-06-14: Added archive name as argument.
-
- $year = (int) $year;
- $month = (int) $month;
- $records = array();
-
- // Build up the components of the WHERE clause (refactored).
- $where_parts = array();
- $limit = '';
-
- $this->addArchiveNameCondition( $archive, $where_parts );
- $this->addArchiveIDCondition( $id, $where_parts );
-
- $this->addYearCondition( $year, $where_parts );
- $this->addMonthCondition( $month, $where_parts );
- // Note that year(+month) is mutually exclusive of "4w" (last four
- // weeks), but we don't check for this. (The original code didn't
- // check either.)
- $this->addFourWeeksCondition( $range, $where_parts );
-
- $limit = $this->buildWhereClause( $where_parts, 'AND' );
-
- // 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
- $limit
- 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.
- // Note that the archive name is irrelevant for this,
- // as the stats are updated for ALL archives at the
- // same time.
- //
- // NJS 2007-02-27: Changed from timeinsert to lastproc.
- // Missed this when fixing earlier bugs! Fortunately it
- // only affects display, not internal processing.
- $query = "
- SELECT lastproc
- 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["lastproc"];
- }
- }
-
-
- /*
- 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.
-
- Do nothing if the archive name is empty or "default".
-
- We could be more clever about this and raise some kind of error
- if the eprint ID doesn't exist in the archive, but I don't see
- that it would add that much to the overall user experience :)
- */
- function getEPrintTitle($archive,$eprint_id)
- {
- // NJS 2006-06-14: Added archive name as argument to support
- // multi-archive statistics.
-
- $eprint_title = '';
- if ( ( $archive != '' ) and ( $archive != 'default' ) )
- {
- $conn_eprints = mysql_connect(
- $GLOBALS["config_vars"]["connections"]["eprints_archives"][$archive]["sqlserver_eprints"],
- $GLOBALS["config_vars"]["connections"]["eprints_archives"][$archive]["sqluser_eprints"],
- $GLOBALS["config_vars"]["connections"]["eprints_archives"][$archive]["sqlpass_eprints"]
- );
- $db_eprints = mysql_select_db( $GLOBALS["config_vars"]["connections"]["eprints_archives"][$archive]["sqldb_eprints"], $conn_eprints );
- if (!$db_eprints) return $eprint_title;
- // NJS 2007-07-24: Added check for EPrints version, as the
- // database structure changed between versions 2 and 3.
- if ($GLOBALS["config_vars"]["general"]["eprints_version"] > 2)
- {
- $query_eprints = "
- SELECT title
- FROM eprint
- WHERE eprintid = $eprint_id
- AND eprint_status = 'archive'
- ";
- }
- else
- {
- $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;
- }
-
- /*
- NJS 2006-06-14
-
- buildWhereClause()
- arguments:
- $where_parts array of WHERE clause conditions
- $op boolean operator to separate conditions with
- (e.g., 'AND')
- returns: string
-
- Given an array of condition strings, build a WHERE clause,
- combining the conditions using the specified operator. (If you
- need to build something more complex, you'll have to do it
- manually.)
- */
- function buildWhereClause( $where_parts, $op )
- {
- $limit = '';
- $num_parts = count( $where_parts );
- if ( $num_parts > 0 )
- {
- $limit = 'WHERE ';
- // Append all but the last one, with the specified operator.
- for ( $i = 0; $i < ( $num_parts - 1 ); $i++ )
- {
- $limit .= $where_parts[$i] . " $op ";
- }
- // Append the last one with no operator.
- $limit .= $where_parts[$num_parts - 1];
- }
- return $limit;
- }
-
- /*
- NJS 2006-06-14
-
- addArchiveNameCondition()
- arguments:
- $archive the name of an archive
- $where_parts reference to an array to which to add the new
- condition string
- returns: void
-
- Add a condition for the archive name to the list of WHERE
- conditions. Do nothing if the archive name is empty. We also
- ignore the value "default" in order to be backwards-compatible
- with earlier versions of the stats package. Note that in either
- of the latter two cases this means that ALL stats for ALL
- archives will be queried!
- */
- function addArchiveNameCondition ( $archive, &$where_parts )
- {
- if ( ( $archive != '' ) and ( $archive != 'default' ) )
- $this->addStringCondition( 'archive_name', '=', $archive, $where_parts );
- }
-
- /*
- NJS 2006-06-14
-
- addArchiveIDCondition()
- arguments:
- $id the id of an eprint
- $where_parts reference to an array to which to add the new
- condition string
- returns: void
-
- Add a condition for the eprint ID to the list of WHERE
- conditions. Do nothing if the eprint ID is zero or less.
- */
- function addArchiveIDCondition ( $id, &$where_parts )
- {
- $id = (int) $id;
- // BUG FIX: ID should be > 0, not > 1!
- if ( $id > 0 ) $this->addNumericCondition( 'archiveid', '=', $id, $where_parts );
- }
-
- /*
- NJS 2006-06-14
-
- addViewTypeCondition()
- arguments:
- $type the type of archive view (download or abstract)
- $where_parts reference to an array to which to add the new
- condition string
- returns: void
-
- Add a condition for the eprint view type to the list of WHERE
- conditions. Do nothing if the view type is not "download" or
- "abstract".
- */
- function addViewTypeCondition ( $type, &$where_parts )
- {
- if ( ( $type == 'download' ) or ( $type == 'abstract' ) )
- $this->addStringCondition( 'view_type', '=', $type, $where_parts );
- }
-
- /*
- NJS 2006-06-14
-
- addYearCondition()
- arguments:
- $year the year to filter on
- $where_parts reference to an array to which to add the new
- condition string
- returns: void
-
- Add a condition for the year to the list of WHERE conditions. Do
- nothing if the year is outside the range 1990--2100. (Note:
- lower bound changed from 2002 as it seems more appropriate.)
-
- NJS 2006-01-18: Fixed upper year bound (was 2005).
- */
- function addYearCondition ( $year, &$where_parts )
- {
- if ( ( $year >= 1990 ) and ( $year < 2100 ) )
- $this->addStringCondition( 'YEAR(request_date)', '=', $year, $where_parts );
- }
-
- /*
- NJS 2006-06-14
-
- addMonthCondition()
- arguments:
- $month the month to filter on
- $where_parts reference to an array to which to add the new
- condition string
- returns: void
-
- Add a condition for the month to the list of WHERE conditions.
- Do nothing if the month is outside the range 1--12.
- */
- function addMonthCondition ( $month, &$where_parts )
- {
- if ( ( $month > 0 ) and ( $month <= 12 ) )
- $this->addStringCondition( 'MONTH(request_date)', '=', $month, $where_parts );
- }
-
- /*
- NJS 2006-06-14
-
- addFourWeeksCondition()
- arguments:
- $range the range to filter on
- $where_parts reference to an array to which to add the new
- condition string
- returns: void
-
- Add a condition for the last four weeks to the list of WHERE
- conditions. Do nothing if the range is not "4w".
- */
- function addFourWeeksCondition ( $range, &$where_parts )
- {
- if ( $range == '4w' )
- $this->addExprCondition( 'request_date', '>=', 'DATE_SUB(CURDATE(),INTERVAL 1 MONTH)', $where_parts );
- }
-
- /*
- NJS 2008-01-23
-
- addDeletedFilter()
- arguments:
- $where_parts reference to an array to which to add the new
- condition string
- returns: void
-
- Add conditions to filter out eprints whose titles are either
- "Unknown item [xxx]" or "Yyy [deleted]".
- */
- function addDeletedFilter ( &$where_parts )
- {
- $where_parts[] = "eprint_name NOT LIKE 'Unknown item [%]'";
- $where_parts[] = "eprint_name NOT LIKE '%[deleted]'";
- }
-
- /*
- NJS 2006-06-14
-
- addStringCondition()
- arguments:
- $column the name of the SQL column
- $op the comparison operator
- $value the value to be tested
- $where_parts reference to an array to which to add the new
- condition string
- returns: void
-
- Add a generic condition for a string column to the list of WHERE
- conditions. Any validation of the value should be done prior to
- calling this function.
- */
- function addStringCondition ( $column, $op, $value, &$where_parts )
- {
- $where_parts[] = "$column $op '$value'";
- }
-
- /*
- NJS 2006-06-14
-
- addNumericCondition()
- arguments:
- $column the name of the SQL column
- $op the comparison operator
- $value the value to be tested
- $where_parts reference to an array to which to add the new
- condition string
- returns: void
-
- Add a generic condition for a numeric column to the list of WHERE
- conditions. Any validation of the value should be done prior to
- calling this function.
- */
- function addNumericCondition ( $column, $op, $value, &$where_parts )
- {
- $where_parts[] = "$column $op $value";
- }
-
- /*
- NJS 2006-06-16
-
- addExprCondition()
- arguments:
- $column the name of the SQL column
- $op the comparison operator
- $expr the expression to be tested
- $where_parts reference to an array to which to add the new
- condition string
- returns: void
-
- Add to the list of WHERE conditions a generic condition for a
- column to be tested against some arbitrary expression. Any
- validation of the value should be done prior to calling this
- function.
- */
- function addExprCondition ( $column, $op, $expr, &$where_parts )
- {
- $where_parts[] = "$column $op $expr";
- }
-
-
- }
-
- ?>
-