/**
* Performs an SQL query.
*
* @param string $query
* @param mixed $limit
* @param boolean $warnOnFailure
* @param boolean $tablesAlreadyCreated
* @access public
*/
function query($query, $limit = false, $warnOnFailure = true, $tablesAlreadyCreated = false)
{
if ($this->isManip($query)) {
if (!$tablesAlreadyCreated) {
$query = $this->_replaceTableNames($query);
}
if (isset($this->result) && is_resource($this->result)) {
@mysql_free_result($this->result);
}
if ($this->config['debug_level'] > 1) {
$this->debugQuery($query);
}
$this->result = @mysql_query($query, $this->connection);
if (!$this->result) {
$throwError = $warnOnFailure ? true : false;
if (!$tablesAlreadyCreated && mysql_errno($this->connection) == 1146) {
$this->_createNewTables();
$this->query($query, $limit, $warnOnFailure, true);
$throwError = false;
}
if ($throwError) {
phpOpenTracker::handleError(@mysql_error($this->connection), E_USER_ERROR);
}
}
} else {
$query = $this->_rewriteSelectQuery($query);
if ($limit != false) {
$query .= ' LIMIT ' . $limit;
}
parent::query($query);
}
}
/**
* @return array
* @access public
*/
function post()
{
if ($this->container['first_request']) {
if (!@file(POT_CONFIG_PATH . 'countries.php')) {
return phpOpenTracker::handleError(sprintf('Cannot open "%s".', POT_CONFIG_PATH . 'countries.php'), E_USER_ERROR);
}
if (!@file(POT_CONFIG_PATH . 'ips.db')) {
return phpOpenTracker::handleError(sprintf('Cannot open "%s".', POT_CONFIG_PATH . 'ips.db'), E_USER_ERROR);
}
if (!@file(POT_CONFIG_PATH . 'ips.idx')) {
return phpOpenTracker::handleError(sprintf('Cannot open "%s".', POT_CONFIG_PATH . 'ips.idx'), E_USER_ERROR);
}
if (!@file(POT_INCLUDE_PATH . '/LoggingEngine/plugins/i2m.class.php')) {
return phpOpenTracker::handleError(sprintf('Cannot open "%s".', POT_INCLUDE_PATH . '/LoggingEngine/plugins/i2m.class.php'), E_USER_ERROR);
}
require_once 'i2m.class.php';
$i2m = new ip2more(null, true);
$ip = $i2m->ip;
$country = $i2m->country['name'];
$iso2 = $i2m->country['iso2'];
$iso3 = $i2m->country['iso3'];
$fips104 = $i2m->country['fips104'];
$iso_number = $i2m->country['isono'];
$flag = substr($i2m->country['flag_small'], -6);
$region = $i2m->country['region'];
$capital = $i2m->country['capital'];
$currency = $i2m->country['currency'];
$currency_code = $i2m->country['currency_code'];
if (isset($ip) && isset($country) && isset($iso2) && isset($iso3) && isset($fips104) && isset($iso_number) && isset($flag) && isset($region) && isset($capital) && isset($currency) && isset($currency_code)) {
$this->db->query(sprintf("INSERT INTO %s\n (accesslog_id, client_id, ip, country, iso2, iso3, fips104, iso_number, flag, region, capital, currency, currency_code)\n VALUES ('%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')\n\t\t\t", $this->config['plugins']['localizer']['table'], $this->container['accesslog_id'], $this->container['client_id'], $this->db->prepareString($ip), $this->db->prepareString($country), $this->db->prepareString($iso2), $this->db->prepareString($iso3), $this->db->prepareString($fips104), $this->db->prepareString($iso_number), $this->db->prepareString($flag), $this->db->prepareString($region), $this->db->prepareString($capital), $this->db->prepareString($currency), $this->db->prepareString($currency_code)));
}
}
return array();
}
/**
* Runs the phpOpenTracker API call.
*
* @param array $parameters
* @return mixed
* @access public
*/
function run($parameters)
{
if (!isset($parameters['what'])) {
return phpOpenTracker::handleError('Required parameter "what" missing.');
}
list($constraint, $selfJoin) = $this->_constraint($parameters['constraints'], true);
if ($selfJoin) {
$selfJoinConstraint = 'AND accesslog.accesslog_id = accesslog2.accesslog_id';
$selfJoinTable = sprintf('%s accesslog2,', $this->config['accesslog_table']);
} else {
$selfJoinConstraint = '';
$selfJoinTable = '';
}
$timerange = $this->_whereTimerange($parameters['start'], $parameters['end']);
if ($parameters["start"] > 0) {
$this->whereString = "AND starttime > " . $parameters["start"] . " AND endtime < " . $parameters["end"] . " ";
}
if ($parameters['what'] == 'pi') {
return $this->_run_pi($parameters);
}
if ($parameters['what'] == 'visits') {
return $this->_run_visits($parameters);
}
if ($parameters['what'] == 'avg_clickstream') {
return $this->_run_avg_clickstream($parameters);
}
if ($parameters['what'] == 'avg_time') {
return $this->_run_avg_time($parameters);
}
}
/**
* @return array
* @access public
*/
function post()
{
$referer = $this->container['referer_orig'];
if ($this->container['first_request'] && !empty($referer)) {
if (!($ignoreRules = @file(POT_CONFIG_PATH . 'search_engines.ignore.ini'))) {
return phpOpenTracker::handleError(sprintf('Cannot open "%s".', POT_CONFIG_PATH . 'search_engines.ignore.ini'), E_USER_ERROR);
}
if (!($matchRules = @file(POT_CONFIG_PATH . 'search_engines.match.ini'))) {
return phpOpenTracker::handleError(sprintf('Cannot open "%s".', POT_CONFIG_PATH . 'search_engines.match.ini'), E_USER_ERROR);
}
$ignore = false;
foreach ($ignoreRules as $ignoreRule) {
if (preg_match(trim($ignoreRule), $referer)) {
$ignore = true;
break;
}
}
if (!$ignore) {
foreach ($matchRules as $matchRule) {
if (preg_match(trim($matchRule), $referer, $tmp)) {
$keywords = $tmp[1];
}
}
$searchEngineName = phpOpenTracker_Parser::match($referer, phpOpenTracker_Parser::readRules(POT_CONFIG_PATH . 'search_engines.group.ini'));
}
if (isset($keywords) && isset($searchEngineName) && $searchEngineName != $referer) {
$this->db->query(sprintf("INSERT INTO %s\n (accesslog_id,\n search_engine, keywords)\n VALUES ('%d',\n '%s', '%s')", $this->config['plugins']['search_engines']['table'], $this->container['accesslog_id'], $this->db->prepareString($searchEngineName), $this->db->prepareString($keywords)));
$this->container['referer'] = '';
$this->container['referer_orig'] = '';
$this->container['referer_id'] = 0;
}
}
return array();
}
/**
* Returns an instance of phpOpenTracker_DB.
*
* @access public
* @return phpOpenTracker_DB
* @static
*/
function &getInstance()
{
static $db;
if (!isset($db)) {
$config =& phpOpenTracker_Config::getConfig();
$dbClass = 'phpOpenTracker_DB_' . $config['db_type'];
if (!@(include POT_INCLUDE_PATH . 'DB/' . $config['db_type'] . '.php')) {
phpOpenTracker::handleError(sprintf('Unknown database handler "%s".', $config['db_type']), E_USER_ERROR);
}
$db = new $dbClass();
}
return $db;
}
/**
* Performs an SQL query.
*
* @param string $query
* @param mixed $limit
* @param boolean $warnOnFailure
* @access public
*/
function query($query, $limit = false, $warnOnFailure = true)
{
if ($limit != false) {
$query .= ' LIMIT ' . $limit;
}
if ($this->config['debug_level'] > 1) {
$this->debugQuery($query);
}
@pg_freeresult($this->result);
$this->result = @pg_exec($this->connection, $query);
if (!$this->result && $warnOnFailure) {
phpOpenTracker::handleError(@pg_errormessage($this->connection), E_USER_ERROR);
}
}
/**
* Performs an SQL query.
*
* @param string $query
* @param mixed $limit
* @param boolean $warnOnFailure
* @access public
*/
function query($query, $limit = false, $warnOnFailure = true)
{
if ($limit != false) {
$query = str_replace('SELECT', 'SELECT TOP ' . $limit, $query);
}
if ($this->config['debug_level'] > 1) {
$this->debugQuery($query);
}
@mssql_free_result($this->result);
$this->result = @mssql_query($query, $this->connection);
if (!$this->result && $warnOnFailure) {
phpOpenTracker::handleError('Database query failed.', E_USER_ERROR);
}
}
/**
* Runs the phpOpenTracker API call.
*
* @param array $parameters
* @return mixed
* @access public
*/
function run($parameters)
{
if (!isset($parameters['accesslog_id'])) {
return phpOpenTracker::handleError('Required parameter "accesslog_id" missing.');
}
$parameters['resolve_ids'] = isset($parameters['resolve_ids']) ? $parameters['resolve_ids'] : true;
if ($parameters['resolve_ids']) {
$this->db->query(sprintf("SELECT data_values.string AS document,\r\n data_values.document_url AS document_url,\r\n accesslog.timestamp AS timestamp\r\n FROM %s accesslog,\r\n %s data_values\r\n WHERE accesslog.accesslog_id = '%d'\r\n AND accesslog.document_id = data_values.data_id\r\n ORDER BY timestamp", $this->config['accesslog_table'], $this->config['documents_table'], $parameters['accesslog_id']));
} else {
$this->db->query(sprintf("SELECT accesslog.document_id AS document,\r\n accesslog.timestamp\r\n FROM %s accesslog\r\n WHERE accesslog.accesslog_id = '%d'\r\n ORDER BY timestamp", $this->config['accesslog_table'], $parameters['accesslog_id']));
}
$i = 0;
while ($row = $this->db->fetchRow()) {
$documents[$i] = $row['document'];
$documentURLs[$i] = $row['document_url'];
if (isset($previousTimestamp)) {
$timeSpent[$i - 1] = $row['timestamp'] - $previousTimestamp;
}
$previousTimestamp = $row['timestamp'];
$i++;
}
if (!isset($documents)) {
return new phpOpenTracker_Clickpath(array());
}
$timeSpent[sizeof($documents) - 1] = 1;
$clickpath = new phpOpenTracker_Clickpath($documents, $documentURLs, $timeSpent);
switch ($parameters['result_format']) {
case 'graphviz':
return $clickpath->toGraph();
break;
case 'graphviz_object':
return $clickpath->toGraph(true);
break;
case 'xml':
return $clickpath->toXML();
break;
case 'xml_object':
return $clickpath->toXML(true);
break;
default:
return $clickpath;
}
}
/**
* Performs an SQL query.
*
* @param string $query
* @param mixed $limit
* @param boolean $warnOnFailure
* @access public
*/
function query($query, $limit = false, $warnOnFailure = true)
{
if ($limit != false) {
$query = sprintf('SELECT * FROM (%s) WHERE ROWNUM <= %d', $query, $limit);
}
if ($this->config['debug_level'] > 1) {
$this->debugQuery($query);
}
@OCIFreeStatement($this->result);
$this->result = @OCIParse($this->connection, $query);
if (!$this->result) {
$error = OCIError($this->result);
phpOpenTracker::handleError($error['code'] . $error['message'], E_USER_ERROR);
}
@OCIExecute($this->result);
if (!$this->result && $warnOnFailure) {
$error = OCIError($this->result);
phpOpenTracker::handleError($error['code'] . $error['message'], E_USER_ERROR);
}
}
/**
* Runs the phpOpenTracker API call.
*
* @param array $parameters
* @return mixed
* @access public
*/
function run($parameters)
{
if (!isset($parameters['what'])) {
return phpOpenTracker::handleError('Required parameter "what" missing.');
}
list($constraint, $selfJoin) = $this->_constraint($parameters['constraints'], true);
if ($selfJoin) {
$selfJoinConstraint = 'AND accesslog.accesslog_id = accesslog2.accesslog_id';
$selfJoinTable = sprintf('%s accesslog2,', $this->config['accesslog_table']);
} else {
$selfJoinConstraint = '';
$selfJoinTable = '';
}
$timerange = $this->_whereTimerange($parameters['start'], $parameters['end']);
switch ($parameters['result_format']) {
case 'csv':
$csv = "Rank;Item;Count;Percent\n";
break;
case 'xml':
case 'xml_object':
$tree = new XML_Tree();
$root =& $tree->addRoot('top');
break;
case 'separate_result_arrays':
$names = array();
$values = array();
$percent = array();
break;
default:
$topItems = array();
}
switch ($parameters['what']) {
case 'document':
case 'exit_target':
case 'host':
case 'operating_system':
case 'referer':
case 'user_agent':
case 'entry_document':
case 'exit_document':
$dataConstraint = '';
switch ($parameters['what']) {
case 'document':
$column = 'accesslog.document_id';
$dataTable = $this->config['documents_table'];
break;
case 'entry_document':
$column = 'accesslog.document_id';
$dataConstraint = "AND accesslog.entry_document = '1'";
$dataTable = $this->config['documents_table'];
break;
case 'exit_document':
$column = 'accesslog.document_id';
$dataConstraint = "AND accesslog.exit_target_id <> '0'";
$dataTable = $this->config['documents_table'];
break;
case 'exit_target':
$column = 'accesslog.exit_target_id';
$dataConstraint = "AND accesslog.exit_target_id <> '0'";
$dataTable = $this->config['exit_targets_table'];
break;
case 'host':
$column = 'visitors.host_id';
$dataTable = $this->config['hostnames_table'];
break;
case 'operating_system':
$column = 'visitors.operating_system_id';
$dataTable = $this->config['operating_systems_table'];
break;
case 'referer':
$column = 'visitors.referer_id';
$dataTable = $this->config['referers_table'];
break;
case 'user_agent':
$column = 'visitors.user_agent_id';
$dataTable = $this->config['user_agents_table'];
break;
}
$nestedQuery = sprintf("SELECT data_table.string AS item\n FROM %s accesslog,\n %s\n %s visitors,\n %s data_table\n WHERE visitors.client_id = '%d'\n AND visitors.accesslog_id = accesslog.accesslog_id\n AND data_table.data_id = %s\n %s\n %s\n %s\n %s\n GROUP BY visitors.accesslog_id,\n data_table.string", $this->config['accesslog_table'], $selfJoinTable, $this->config['visitors_table'], $dataTable, $parameters['client_id'], $column, $selfJoinConstraint, $dataConstraint, $constraint, $timerange);
break;
default:
$nestedQuery = sprintf("SELECT add_data.data_value AS item\n FROM %s accesslog,\n %s\n %s visitors,\n %s add_data\n WHERE visitors.client_id = '%d'\n AND visitors.accesslog_id = accesslog.accesslog_id\n AND accesslog.accesslog_id = add_data.accesslog_id\n AND add_data.data_field = '%s'\n %s\n %s\n %s\n GROUP BY visitors.accesslog_id,\n add_data.data_value", $this->config['accesslog_table'], $selfJoinTable, $this->config['visitors_table'], $this->config['additional_data_table'], $parameters['client_id'], $parameters['what'], $selfJoinConstraint, $constraint, $timerange);
}
if ($this->db->supportsNestedQueries()) {
$queryTotalUnique = sprintf('SELECT COUNT(item) AS total_items,
COUNT(DISTINCT(item)) AS unique_items
FROM (%s) items', $nestedQuery);
$queryItems = sprintf('SELECT COUNT(item) AS item_count,
item
FROM (%s) items
GROUP BY item
ORDER BY item_count %s,
item', $nestedQuery, $parameters['order']);
if ($parameters['result_format'] == 'sql') {
return $queryItems;
}
} else {
if ($this->config['db_type'] == 'mysql' || $this->config['db_type'] == 'mysql_merge') {
$queryCreateTemporaryTable = sprintf('CREATE TEMPORARY TABLE pot_temporary_table %s', $nestedQuery);
$queryTotalUnique = sprintf('SELECT COUNT(item) AS total_items,
COUNT(DISTINCT(item)) AS unique_items
FROM pot_temporary_table', $nestedQuery);
$queryItems = sprintf('SELECT COUNT(item) AS item_count,
item
FROM pot_temporary_table
GROUP BY item
ORDER BY item_count %s,
item', $parameters['order']);
if ($parameters['result_format'] != 'sql') {
$dropTemporaryTable = true;
$this->db->query($queryCreateTemporaryTable);
} else {
return $queryCreateTemporaryTable . ';' . $queryItems;
}
} else {
return phpOpenTracker::handleError('You need a database system capable of nested queries.', E_USER_ERROR);
}
}
$this->db->query($queryTotalUnique);
if ($row = $this->db->fetchRow()) {
$totalItems = intval($row['total_items']);
$uniqueItems = intval($row['unique_items']);
} else {
return phpOpenTracker::handleError('Database query failed.');
}
if ($totalItems > 0) {
$this->db->query($queryItems, $parameters['limit']);
$i = 0;
while ($row = $this->db->fetchRow()) {
$percentValue = doubleval(number_format(100 * $row['item_count'] / $totalItems, 2));
switch ($parameters['result_format']) {
case 'csv':
$csv .= sprintf("%d;%s;%d;%d\n", $i + 1, $row['item'], intval($row['item_count']), $percentValue);
break;
case 'xml':
case 'xml_object':
$itemChild =& $root->addChild('item');
$itemChild->addChild('rank', $i + 1);
$itemChild->addChild('string', $row['item']);
$itemChild->addChild('count', intval($row['item_count']));
$itemChild->addChild('percent', $percentValue);
if (isset($row['document_url'])) {
$itemChild->addChild('url', $row['document_url']);
}
break;
case 'separate_result_arrays':
$names[$i] = $row['item'];
$values[$i] = intval($row['item_count']);
$percent[$i] = $percentValue;
break;
default:
$topItems[$i]['count'] = intval($row['item_count']);
$topItems[$i]['string'] = $row['item'];
$topItems[$i]['percent'] = $percentValue;
if (isset($row['document_url'])) {
$topItems[$i]['url'] = $row['document_url'];
}
}
$i++;
}
}
if (isset($dropTemporaryTable)) {
$this->db->query('DROP TABLE pot_temporary_table');
}
switch ($parameters['result_format']) {
case 'csv':
return $csv;
break;
case 'xml':
case 'xml_object':
$root->addChild('total', $totalItems);
$root->addChild('unique', $uniqueItems);
switch ($parameters['result_format']) {
case 'xml':
return $root->get();
break;
case 'xml_object':
return $root;
break;
}
break;
case 'separate_result_arrays':
return array($names, $values, $percent, $uniqueItems);
break;
default:
return array('top_items' => $topItems, 'unique_items' => $uniqueItems);
}
}
/**
* Runs the phpOpenTracker API call.
*
* @param array $parameters
* @return mixed
* @access public
*/
function run($parameters)
{
$parameters['session_lifetime'] = isset($parameters['session_lifetime']) ? $parameters['session_lifetime'] : 3;
switch ($parameters['result_format']) {
case 'xml':
case 'xml_object':
$tree = new XML_Tree();
$root =& $tree->addRoot('visitorsonline');
$children = array();
break;
default:
$result = array();
}
$accesslogIDs = array();
$this->db->query(sprintf("SELECT DISTINCT(accesslog.accesslog_id) AS accesslog_id\r\n FROM %s accesslog,\r\n %s visitors\r\n WHERE visitors.client_id = '%d'\r\n AND visitors.accesslog_id = accesslog.accesslog_id\r\n AND accesslog.timestamp >= '%d'", $this->config['accesslog_table'], $this->config['visitors_table'], $parameters['client_id'], time() - $parameters['session_lifetime'] * 60));
while ($row = $this->db->fetchRow()) {
$accesslogIDs[] = $row['accesslog_id'];
}
for ($i = 0, $max = sizeof($accesslogIDs); $i < $max; $i++) {
switch ($parameters['result_format']) {
case 'xml':
case 'xml_object':
$visitorNode =& $root->addChild('visitor');
$visitorNode->addChild(phpOpenTracker::get(array('client_id' => $parameters['client_id'], 'api_call' => 'individual_clickpath', 'accesslog_id' => $accesslogIDs[$i], 'result_format' => 'xml_object')));
break;
default:
$result[$i]['clickpath'] = phpOpenTracker::get(array('client_id' => $parameters['client_id'], 'api_call' => 'individual_clickpath', 'accesslog_id' => $accesslogIDs[$i]));
}
$this->db->query(sprintf("SELECT MAX(timestamp) as last_access\r\n FROM %s\r\n WHERE accesslog_id = '%s'", $this->config['accesslog_table'], $accesslogIDs[$i]));
if ($row = $this->db->fetchRow()) {
switch ($parameters['result_format']) {
case 'xml':
case 'xml_object':
$visitorNode->addChild('last_access', $row['last_access']);
break;
default:
$result[$i]['last_access'] = $row['last_access'];
}
} else {
return phpOpenTracker::handleError('Database query failed.');
}
$this->db->query(sprintf("SELECT hosts.string AS host,\r\n user_agents.string AS user_agent\r\n FROM %s visitors,\r\n %s hosts,\r\n %s user_agents\r\n WHERE visitors.accesslog_id = '%d'\r\n AND visitors.host_id = hosts.data_id\r\n AND visitors.user_agent_id = user_agents.data_id", $this->config['visitors_table'], $this->config['hostnames_table'], $this->config['user_agents_table'], $accesslogIDs[$i]));
if ($row = $this->db->fetchRow()) {
switch ($parameters['result_format']) {
case 'xml':
case 'xml_object':
$visitorNode->addChild('host', $row['host']);
$visitorNode->addChild('user_agent', $row['user_agent']);
break;
default:
$result[$i]['host'] = $row['host'];
$result[$i]['user_agent'] = $row['user_agent'];
}
} else {
return phpOpenTracker::handleError('Database query failed.');
}
$this->db->query(sprintf("SELECT referers.string AS referer\r\n FROM %s visitors,\r\n %s referers\r\n WHERE visitors.accesslog_id = '%d'\r\n AND visitors.referer_id = referers.data_id", $this->config['visitors_table'], $this->config['referers_table'], $accesslogIDs[$i]));
if ($row = $this->db->fetchRow()) {
$referer = $row['referer'];
} else {
$referer = '';
}
switch ($parameters['result_format']) {
case 'xml':
case 'xml_object':
$visitorNode->addChild('referer', $referer);
break;
default:
$result[$i]['referer'] = $referer;
}
}
switch ($parameters['result_format']) {
case 'xml':
return $root->get();
break;
case 'xml_object':
return $root;
break;
default:
return $result;
}
}
/**
* Reads a set of regular expressions from a given file.
*
* @param string $rulesFile
* @return array
* @access public
* @static
*/
function readRules($rulesFile)
{
$rules = array();
if ($file = @file($rulesFile)) {
$index = 0;
$numLines = sizeof($file);
for ($i = 0; $i < $numLines; $i += 3) {
$rules[$index]['pattern'] = $file[$i];
$rules[$index]['string'] = $file[$i + 1];
$index++;
}
} else {
return phpOpenTracker::handleError(sprintf('Cannot open "%s".', $rulesFile), E_USER_ERROR);
}
return $rules;
}
/**
* Loads the phpOpenTracker Logging Engine plugins.
*
* @param array $parameters
* @access private
*/
function _loadPlugins($parameters)
{
if ($this->config['logging_engine_plugins'] == '') {
return;
}
$plugins = explode(',', str_replace(' ', '', $this->config['logging_engine_plugins']));
foreach ($plugins as $pluginName) {
if (@(include POT_INCLUDE_PATH . 'LoggingEngine/plugins/' . $pluginName . '.php')) {
$pluginClass = 'phpOpenTracker_LoggingEngine_Plugin_' . $pluginName;
$this->plugins[] = new $pluginClass($parameters);
} else {
phpOpenTracker::handleError(sprintf('Could not load plugin "%s".', $pluginName));
}
}
}
/**
* Invokes the phpOpenTracker Logging Engine.
*
* @param optional array $parameters
* @return boolean
* @access public
* @static
*/
function log($parameters = array())
{
static $called;
if (!isset($called)) {
$called = true;
} else {
return phpOpenTracker::handleError('phpOpenTracker::log() may only be called once per request.');
}
include POT_INCLUDE_PATH . 'LoggingEngine.php';
$le = new phpOpenTracker_LoggingEngine($parameters);
return $le->log(isset($parameters['add_data']) ? $parameters['add_data'] : array());
}
/**
* Performs an SQL query.
*
* @param string $query
* @param optional mixed $limit
* @param optional boolean $warnOnFailure
* @param optional boolean $tablesAlreadyCreated
* @param optional boolean $rewrite
* @access public
*/
function query($query, $limit = false, $warnOnFailure = true, $tablesAlreadyCreated = false, $rewrite = true)
{
if (!$rewrite) {
return parent::query($query, $limit, $warnOnFailure);
}
$insert = false;
if (!$tablesAlreadyCreated && strstr($query, 'INSERT')) {
$insert = true;
$query = $this->_replaceTableNames($query, date('Y') . date('m'));
} else {
$query = $this->_rewriteSelectQuery($query);
if ($limit != false) {
$query .= ' LIMIT ' . $limit;
}
}
if ($this->config['debug_level'] > 1) {
$this->debugQuery($query);
}
if (isset($this->result) && is_resource($this->result)) {
@mysql_free_result($this->result);
}
$this->result = @mysql_unbuffered_query($query, $this->connection);
if (!$this->result) {
$throwError = $warnOnFailure ? true : false;
if ($insert && !$tablesAlreadyCreated && mysql_errno($this->connection) == 1146) {
$this->_newTables();
$this->query($query, $limit, $warnOnFailure, true);
$throwError = false;
}
if ($throwError) {
phpOpenTracker::handleError(@mysql_error($this->connection), E_USER_ERROR);
}
}
}
/**
* Returns XML markup for the graph.
*
* @param boolean $returnObject
* @return mixed
* @access public
*/
function toXML($returnObject = false)
{
if (!@(include_once 'XML/Tree.php')) {
phpOpenTracker::handleError('Could not find PEAR XML_Tree package, exiting.', E_USER_ERROR);
}
$tree = new XML_Tree();
$root =& $tree->addRoot('clickpath');
for ($i = 0; $i < $this->length; $i++) {
$root->addChild('length', $this->length);
$node =& $root->addChild('node');
$node->addChild('document', $this->documents[$i]);
if (!isset($this->statistics[$i]['count'])) {
$node->addChild('timespent', $this->statistics[$i]);
}
}
if (!$returnObject) {
return $root->get();
} else {
return $root;
}
}
/**
* Performs an SQL query.
*
* @param string $query
* @param mixed $limit
* @param boolean $warnOnFailure
* @access public
*/
function query($query, $limit = false, $warnOnFailure = true)
{
if ($limit != false) {
$query .= ' LIMIT ' . $limit;
}
if ($this->config['debug_level'] > 1) {
$this->debugQuery($query);
}
@mysql_free_result($this->result);
$this->result = @mysql_unbuffered_query($query, $this->connection);
if (!$this->result && $warnOnFailure) {
phpOpenTracker::handleError(@mysql_error($this->connection), E_USER_ERROR);
}
}
/**
* Parses plain-text timeranges into timestamp intervals.
*
* @param string $range
* @param integer $start
* @param integer $length
* @return array
* @access private
*/
function _timerange($range, $start = 0, $length = 0)
{
switch ($range) {
case 'current_minute':
return $this->_timerange('minute');
break;
case 'previous_minute':
return $this->_timerange('minute', 1, 1);
break;
case 'current_hour':
return $this->_timerange('hour');
break;
case 'previous_hour':
return $this->_timerange('hour', 1, 1);
break;
case 'today':
return $this->_timerange('day');
break;
case 'yesterday':
return $this->_timerange('day', 1, 1);
break;
case 'current_month':
return $this->_timerange('month');
break;
case 'previous_month':
return $this->_timerange('month', 1, 1);
break;
case 'current_week':
return $this->_timerange('week');
break;
case 'previous_week':
return $this->_timerange('week', 1, 1);
break;
case 'current_year':
return $this->_timerange('year');
break;
case 'previous_year':
return $this->_timerange('year', 1, 1);
break;
case 'minute':
$start = mktime(date('H'), date('i') - $start, 0);
$end = mktime(date('H'), date('i') - $length, 59);
break;
case 'hour':
$start = mktime(date('H') - $start, 0, 0);
$end = mktime(date('H') - $length, 59, 59);
break;
case 'day':
$start = mktime(0, 0, 0, date('m'), date('d') - $start);
$end = mktime(23, 59, 59, date('m'), date('d') - $length);
break;
case 'month':
$start = mktime(0, 0, 0, date('m') - $start, 1);
$end = mktime(23, 59, 59, date('m') - $length, date('t', mktime(0, 0, 0, date('m') - $length)));
break;
case 'week':
$start = $this->_mondayOfWeek(date('W') - $start, date('Y'));
$end = $this->_sundayOfWeek(date('W') - $length, date('Y'));
break;
case 'year':
$start = mktime(0, 0, 0, 1, 1, date('Y') - $start);
$end = mktime(23, 59, 59, 12, 31, date('Y') - $length);
break;
default:
return phpOpenTracker::handleError('Syntax error in timerange specification.');
}
return array($start, $end);
}
/**
* @param array $parameters
* @return mixed
* @access public
*/
function run($parameters)
{
if (!isset($parameters['what'])) {
return phpOpenTracker::handleError('Required parameter "what" missing.');
}
$constraint = $this->_constraint($parameters['constraints']);
$timerange = $this->_whereTimerange($parameters['start'], $parameters['end']);
switch ($parameters['what']) {
case 'combined_statistics':
$_parameters = $parameters;
$_parameters['what'] = 'top_search_engines';
$searchEngines = phpOpenTracker::get($_parameters);
$_parameters['what'] = 'top_search_keywords';
for ($i = 0; $i < sizeof($searchEngines['top_items']); $i++) {
$_parameters['search_engine'] = $searchEngines['top_items'][$i]['string'];
$searchKeywords = phpOpenTracker::get($_parameters);
$searchEngines['top_items'][$i]['search_keywords'] = $searchKeywords['top_items'];
}
return $searchEngines['top_items'];
break;
case 'top_search_engines':
case 'top_search_keywords':
switch ($parameters['result_format']) {
case 'csv':
$csv = "Rank;Item;Count;Percent\n";
break;
case 'xml':
case 'xml_object':
$tree = new XML_Tree();
$root = $tree->addRoot('top');
break;
case 'separate_result_arrays':
$names = array();
$values = array();
$percent = array();
break;
default:
$topItems = array();
}
if (isset($parameters['search_engine'])) {
$searchEngineConstraint = sprintf("AND search_engines.search_engine = '%s'", $parameters['search_engine']);
} else {
$searchEngineConstraint = '';
}
$field = $parameters['what'] == 'top_search_engines' ? 'search_engine' : 'keywords';
$nestedQuery = sprintf("SELECT search_engines.%s AS item\r\n FROM %s accesslog,\r\n %s visitors,\r\n %s search_engines\r\n WHERE visitors.client_id = '%d'\r\n AND visitors.accesslog_id = accesslog.accesslog_id\r\n AND visitors.accesslog_id = search_engines.accesslog_id\r\n %s\r\n %s\r\n %s\r\n GROUP BY visitors.accesslog_id,\r\n search_engines.%s", $field, $this->config['accesslog_table'], $this->config['visitors_table'], $this->config['plugins']['search_engines']['table'], $parameters['client_id'], $searchEngineConstraint, $constraint, $timerange, $field);
if ($this->db->supportsNestedQueries()) {
$queryTotalUnique = sprintf('SELECT COUNT(item) AS total_items,
COUNT(DISTINCT(item)) AS unique_items
FROM (%s) items', $nestedQuery);
$queryItems = sprintf('SELECT COUNT(item) AS item_count,
item
FROM (%s) items
GROUP BY item
ORDER BY item_count %s,
item', $nestedQuery, $parameters['order']);
} else {
if ($this->config['db_type'] == 'mysql' || $this->config['db_type'] == 'mysql_merge') {
$dropTemporaryTable = true;
$this->db->query(sprintf('CREATE TEMPORARY TABLE pot_temporary_table %s', $nestedQuery));
$queryTotalUnique = sprintf('SELECT COUNT(item) AS total_items,
COUNT(DISTINCT(item)) AS unique_items
FROM pot_temporary_table', $nestedQuery);
$queryItems = sprintf('SELECT COUNT(item) AS item_count,
item
FROM pot_temporary_table
GROUP BY item
ORDER BY item_count %s,
item', $parameters['order']);
} else {
return phpOpenTracker::handleError('You need a database system capable of nested ' . 'queries to use the "search_engines" API calls.', E_USER_ERROR);
}
}
$this->db->query($queryTotalUnique);
if ($row = $this->db->fetchRow()) {
$totalItems = intval($row['total_items']);
$uniqueItems = intval($row['unique_items']);
} else {
return phpOpenTracker::handleError('Database query failed.');
}
if ($totalItems > 0) {
$this->db->query($queryItems, $parameters['limit']);
$i = 0;
while ($row = $this->db->fetchRow()) {
$percentValue = doubleval(number_format(100 * $row['item_count'] / $totalItems, 2));
switch ($parameters['result_format']) {
case 'csv':
$csv = sprintf("%d;%s;%d;%d\n", $i + 1, $row['item'], intval($row['item_count']), $percentValue);
break;
case 'xml':
case 'xml_object':
$itemChild =& $root->addChild('item');
$itemChild->addChild('rank', $i + 1);
$itemChild->addChild('string', $row['item']);
$itemChild->addChild('count', intval($row['item_count']));
$itemChild->addChild('percent', $percentValue);
break;
case 'separate_result_arrays':
$names[$i] = $row['item'];
$values[$i] = intval($row['item_count']);
$percent[$i] = $percentValue;
break;
default:
$topItems[$i]['count'] = intval($row['item_count']);
$topItems[$i]['string'] = $row['item'];
$topItems[$i]['percent'] = $percentValue;
}
$i++;
}
}
if (isset($dropTemporaryTable)) {
$this->db->query('DROP TABLE pot_temporary_table');
}
switch ($parameters['result_format']) {
case 'csv':
return $csv;
break;
case 'xml':
case 'xml_object':
$root->addChild('total', $totalItems);
$root->addChild('unique', $uniqueItems);
switch ($parameters['result_format']) {
case 'xml':
return $root->get();
break;
case 'xml_object':
return $root;
break;
}
break;
case 'separate_result_arrays':
return array($names, $values, $percent, $uniqueItems);
break;
default:
return array('top_items' => $topItems, 'unique_items' => $uniqueItems);
}
break;
}
}
/**
* Returns detailed information about the visitors who have made a conversion
* (placed an order or inquiry or signed up for something).
*
* @param array $parameters
* @return mixed
* @access private
* @since phpOpenTracker 1.3.0
*/
function _conversions($parameters)
{
switch ($parameters['result_format']) {
case 'xml':
case 'xml_object':
$tree = new XML_Tree();
$root =& $tree->addRoot('visitorsonline');
$children = array();
break;
default:
$result = array();
}
$accesslogIDs = array();
// get a list of all accesslogIDs where there was a conversion
$this->db->query(sprintf("SELECT DISTINCT(accesslog.accesslog_id) AS accesslog_id\n FROM %s accesslog,\n %s conversions\n WHERE conversions.client_id = '%d'\n AND conversions.accesslog_id = accesslog.accesslog_id\n AND accesslog.timestamp >= '%d'", $this->config['accesslog_table'], $this->config['plugins']['conversions']['table'], $parameters['client_id'], time() - $parameters['session_lifetime'] * 60));
while ($row = $this->db->fetchRow()) {
$accesslogIDs[] = $row['accesslog_id'];
}
// for each conversion, get details
for ($i = 0, $max = sizeof($accesslogIDs); $i < $max; $i++) {
// get the clickpaths for each conversion
switch ($parameters['result_format']) {
case 'xml':
case 'xml_object':
$visitorNode =& $root->addChild('visitor');
$visitorNode->addChild(phpOpenTracker::get(array('client_id' => $parameters['client_id'], 'api_call' => 'individual_clickpath', 'accesslog_id' => $accesslogIDs[$i], 'result_format' => 'xml_object')));
break;
default:
$result[$i]['clickpath'] = phpOpenTracker::get(array('client_id' => $parameters['client_id'], 'api_call' => 'individual_clickpath', 'accesslog_id' => $accesslogIDs[$i]));
}
// get time of last access
$this->db->query(sprintf("SELECT MAX(timestamp) as last_access\n FROM %s\n WHERE accesslog_id = '%s'", $this->config['accesslog_table'], $accesslogIDs[$i]));
if ($row = $this->db->fetchRow()) {
switch ($parameters['result_format']) {
case 'xml':
case 'xml_object':
$visitorNode->addChild('last_access', $row['last_access']);
break;
default:
$result[$i]['last_access'] = $row['last_access'];
}
} else {
return phpOpenTracker::handleError('Database query failed. A');
}
// get host and user agent
$this->db->query(sprintf("SELECT hosts.string AS host,\n user_agents.string AS user_agent\n FROM %s visitors,\n %s hosts,\n %s user_agents\n WHERE visitors.accesslog_id = '%d'\n AND visitors.host_id = hosts.data_id\n AND visitors.user_agent_id = user_agents.data_id", $this->config['visitors_table'], $this->config['hostnames_table'], $this->config['user_agents_table'], $accesslogIDs[$i]));
if ($row = $this->db->fetchRow()) {
switch ($parameters['result_format']) {
case 'xml':
case 'xml_object':
$visitorNode->addChild('host', $row['host']);
$visitorNode->addChild('user_agent', $row['user_agent']);
break;
default:
$result[$i]['host'] = $row['host'];
$result[$i]['user_agent'] = $row['user_agent'];
}
} else {
return phpOpenTracker::handleError('Database query failed. B');
}
// to determine source, first look for a record of
// the use of a search engine on the first visit,
// then a referrer on the first visit, then
// the use of a search engine on the most recent visit,
// then a referrer on the most recent visit
// get visitor_id and number of visits
$this->db->query(sprintf("SELECT visitors.visitor_id AS visitor_id\n FROM %s visitors\n WHERE visitors.accesslog_id = '%d'", $this->config['visitors_table'], $accesslogIDs[$i]));
if ($row = $this->db->fetchRow()) {
$visitor_id = intval($row['visitor_id']);
} else {
$visitor_id = 0;
}
$this->db->query(sprintf("SELECT COUNT(visitors.visitor_id) AS num_visits\n FROM %s visitors\n WHERE visitors.visitor_id = '%d'", $this->config['visitors_table'], $visitor_id));
if ($row = $this->db->fetchRow()) {
$num_visits = intval($row['num_visits']);
} else {
$num_visits = 0;
}
switch ($parameters['result_format']) {
case 'xml':
case 'xml_object':
$visitorNode->addChild('num_visits', $num_visits);
break;
default:
$result[$i]['num_visits'] = $num_visits;
}
// end query for visitor_id and number of visits
// get accesslog_id of first visit
$this->db->query(sprintf("SELECT visitors.accesslog_id AS first_access\n FROM %s visitors\n WHERE visitors.visitor_id = '%d'\n ORDER BY visitors.timestamp", $this->config['visitors_table'], $visitor_id));
if ($row = $this->db->fetchRow()) {
$first_access = $row['first_access'];
}
// end query for accesslog_id of first visit
// get first search engine and search engine keywords
if ($num_visits > 1) {
$this->db->query(sprintf("SELECT search_engines.search_engine AS search_engine,\n search_engines.keywords AS keywords\n FROM %s search_engines\n WHERE search_engines.accesslog_id = '%d'", $this->config['plugins']['search_engines']['table'], $first_access));
if ($row = $this->db->fetchRow()) {
$source = $row['search_engine'] . ': "' . $row['keywords'] . '"';
}
}
// end query for first search engine and search engine keywords
// get first referrer
if (!isset($source)) {
if ($num_visits > 1) {
$this->db->query(sprintf("SELECT referers.string AS first_referer\n FROM %s visitors,\n %s referers\n WHERE visitors.accesslog_id = '%d'\n AND visitors.referer_id = referers.data_id\n ORDER BY visitors.timestamp", $this->config['visitors_table'], $this->config['referers_table'], $first_access));
if ($row = $this->db->fetchRow()) {
$source = $row['first_referer'];
}
}
}
// end query for first referrer
// get most recent search engine and search engine keywords
if (!isset($source)) {
$this->db->query(sprintf("SELECT search_engines.search_engine AS search_engine,\n search_engines.keywords AS keywords\n FROM %s search_engines,\n %s visitors\n WHERE visitors.visitor_id = '%d'\n AND visitors.accesslog_id = search_engines.accesslog_id\n ORDER BY visitors.timestamp", $this->config['plugins']['search_engines']['table'], $this->config['visitors_table'], $visitor_id));
if ($row = $this->db->fetchRow()) {
$source = $row['search_engine'] . ': "' . $row['keywords'] . '"';
}
}
// end query for most recent search engines and search engine keywords
// get most recent referrer
if (!isset($source)) {
$this->db->query(sprintf("SELECT referers.string AS referer\n FROM %s visitors,\n %s referers\n WHERE visitors.accesslog_id = '%d'\n AND visitors.referer_id = referers.data_id", $this->config['visitors_table'], $this->config['referers_table'], $accesslogIDs[$i]));
if ($row = $this->db->fetchRow()) {
$source = $row['referer'];
} else {
$source = '';
}
}
// end query for most recent referrer
// store source as a result
switch ($parameters['result_format']) {
case 'xml':
case 'xml_object':
$visitorNode->addChild('source', $source);
break;
default:
$result[$i]['source'] = $source;
}
// done storing source
unset($source);
// query for conversions type, amount, and details
$this->db->query(sprintf("SELECT conversions.sale AS sale,\n conversions.inquiry AS inquiry,\n conversions.signup AS signup,\n conversions.email AS email,\n conversions.zip AS zip,\n conversions.country AS country,\n conversions.name AS name\n FROM %s conversions,\n %s visitors\n WHERE visitors.accesslog_id = '%d'\n AND visitors.accesslog_id = conversions.accesslog_id", $this->config['plugins']['conversions']['table'], $this->config['visitors_table'], $accesslogIDs[$i]));
if ($row = $this->db->fetchRow()) {
$sale = $row['sale'];
$inquiry = $row['inquiry'];
$signup = $row['signup'];
$email = $row['email'];
$zip = $row['zip'];
$country = $row['country'];
$name = $row['name'];
} else {
$sale = '';
$inquiry = '';
$signup = '';
$email = '';
$zip = '';
$country = '';
$name = '';
}
switch ($parameters['result_format']) {
case 'xml':
case 'xml_object':
$visitorNode->addChild('sale', $sale);
$visitorNode->addChild('inquiry', $inquiry);
$visitorNode->addChild('signup', $signup);
$visitorNode->addChild('email', $email);
$visitorNode->addChild('zip', $zip);
$visitorNode->addChild('country', $country);
$visitorNode->addChild('name', $name);
break;
default:
$result[$i]['sale'] = $sale;
$result[$i]['inquiry'] = $inquiry;
$result[$i]['signup'] = $signup;
$result[$i]['email'] = $email;
$result[$i]['zip'] = $zip;
$result[$i]['country'] = $country;
$result[$i]['name'] = $name;
}
// end query for conversions type, amount, etc.
}
// end loop
// return results
switch ($parameters['result_format']) {
case 'xml':
return $root->get();
break;
case 'xml_object':
return $root;
break;
default:
return $result;
}
}
