/**
* Given a list of fields defining numeric values, it will return a Piwik_DataTable_Array
* which is an array of Piwik_DataTable_Simple, ordered by chronological order
*
* @param array|string $fields array( fieldName1, fieldName2, ...) Names of the mysql table fields to load
* @return Piwik_DataTable_Array
*/
public function getDataTableFromNumeric($fields)
{
$inNames = Piwik_Common::getSqlStringFieldsArray($fields);
// we select in different shots
// one per distinct table (case we select last 300 days, maybe we will select from 10 different tables)
$queries = array();
foreach ($this->archives as $archive) {
$archive->setRequestedReport(is_string($fields) ? $fields : current($fields));
$archive->prepareArchive();
if (!$archive->isThereSomeVisits) {
continue;
}
$table = $archive->archiveProcessing->getTableArchiveNumericName();
// for every query store IDs
$queries[$table][] = $archive->getIdArchive();
}
// we select the requested value
$db = Zend_Registry::get('db');
// date => array( 'field1' =>X, 'field2'=>Y)
// date2 => array( 'field1' =>X2, 'field2'=>Y2)
$arrayValues = array();
foreach ($queries as $table => $aIds) {
$inIds = implode(', ', array_filter($aIds));
if (empty($inIds)) {
// Probable timezone configuration error, i.e., mismatch between PHP and MySQL server.
continue;
}
$sql = "SELECT value, name, date1 as startDate\n\t\t\t\t\tFROM {$table}\n\t\t\t\t\tWHERE idarchive IN ( {$inIds} )\n\t\t\t\t\tAND name IN ( {$inNames} )\n\t\t\t\t\tORDER BY date1, name";
$values = $db->fetchAll($sql, $fields);
foreach ($values as $value) {
$timestamp = Piwik_Date::factory($value['startDate'])->getTimestamp();
$arrayValues[$timestamp][$value['name']] = $this->formatNumericValue($value['value']);
}
}
$contentArray = array();
// we add empty tables so that every requested date has an entry, even if there is nothing
// example: <result date="2007-01-01" />
$archiveByTimestamp = array();
foreach ($this->archives as $archive) {
$timestamp = $archive->getTimestampStartDate();
$archiveByTimestamp[$timestamp] = $archive;
$contentArray[$timestamp]['table'] = new Piwik_DataTable_Simple();
$contentArray[$timestamp]['prettyDate'] = $archive->getPrettyDate();
}
foreach ($arrayValues as $timestamp => $aNameValues) {
// undefined in some edge/unknown cases see http://dev.piwik.org/trac/ticket/2578
if (isset($contentArray[$timestamp]['table'])) {
$contentArray[$timestamp]['table']->addRowsFromArray($aNameValues);
}
}
$contentArray;
$tableArray = $this->getNewDataTableArray();
foreach ($contentArray as $timestamp => $aData) {
$tableArray->addTable($aData['table'], $aData['prettyDate']);
$this->loadMetadata($tableArray, $archiveByTimestamp[$timestamp]);
}
return $tableArray;
}
/**
* Helper function used by other record* methods which will INSERT or UPDATE the conversion in the DB
*
* @param array $newGoal
* @param bool $mustUpdateNotInsert If set to true, the previous conversion will be UPDATEd. This is used for the Cart Update conversion (only one cart per visit)
* @param array $updateWhere
* @return bool
*/
protected function recordGoal($newGoal, $mustUpdateNotInsert = false, $updateWhere = array())
{
$newGoalDebug = $newGoal;
$newGoalDebug['idvisitor'] = bin2hex($newGoalDebug['idvisitor']);
printDebug($newGoalDebug);
$fields = implode(", ", array_keys($newGoal));
$bindFields = Piwik_Common::getSqlStringFieldsArray($newGoal);
if ($mustUpdateNotInsert) {
$updateParts = $sqlBind = $updateWhereParts = array();
foreach ($newGoal as $name => $value) {
$updateParts[] = $name . " = ?";
$sqlBind[] = $value;
}
foreach ($updateWhere as $name => $value) {
$updateWhereParts[] = $name . " = ?";
$sqlBind[] = $value;
}
$sql = 'UPDATE ' . Piwik_Common::prefixTable('log_conversion') . "\t\n\t\t\t\t\tSET " . implode($updateParts, ', ') . "\n\t\t\t\t\t\tWHERE " . implode($updateWhereParts, ' AND ');
Piwik_Tracker::getDatabase()->query($sql, $sqlBind);
return true;
} else {
$sql = 'INSERT IGNORE INTO ' . Piwik_Common::prefixTable('log_conversion') . "\t\n\t\t\t\t\t({$fields}) VALUES ({$bindFields}) ";
$bind = array_values($newGoal);
$result = Piwik_Tracker::getDatabase()->query($sql, $bind);
// If a record was inserted, we return true
return Piwik_Tracker::getDatabase()->rowCount($result) > 0;
}
}
/**
* Returns the list of all the users
*
* @param string $userLogins Comma separated list of users to select. If not specified, will return all users
* @return array the list of all the users
*/
public function getUsers($userLogins = '')
{
Piwik::checkUserHasSomeAdminAccess();
$where = '';
$bind = array();
if (!empty($userLogins)) {
$userLogins = explode(',', $userLogins);
$where = 'WHERE login IN (' . Piwik_Common::getSqlStringFieldsArray($userLogins) . ')';
$bind = $userLogins;
}
$db = Zend_Registry::get('db');
$users = $db->fetchAll("SELECT * \n\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable("user") . "\n\t\t\t\t\t\t\t\t{$where} \n\t\t\t\t\t\t\t\tORDER BY login ASC", $bind);
// Non Super user can only access login & alias
if (!Piwik::isUserIsSuperUser()) {
foreach ($users as &$user) {
$user = array('login' => $user['login'], 'alias' => $user['alias']);
}
}
return $users;
}
/**
* Records in the DB the association between the visit and this action.
*
* @param int idVisit is the ID of the current visit in the DB table log_visit
* @param int idRefererActionUrl is the ID of the last action done by the current visit.
* @param int timeSpentRefererAction is the number of seconds since the last action was done.
* It is directly related to idRefererActionUrl.
*/
public function record( $idVisit, $visitorIdCookie, $idRefererActionUrl, $idRefererActionName, $timeSpentRefererAction)
{
$this->loadIdActionNameAndUrl();
$idActionName = in_array($this->getActionType(), array(Piwik_Tracker_Action::TYPE_ACTION_NAME, Piwik_Tracker_Action::TYPE_ACTION_URL))
? (int)$this->getIdActionName()
: null;
$insert = array(
'idvisit' => $idVisit,
'idsite' => $this->idSite,
'idvisitor' => $visitorIdCookie,
'server_time' => Piwik_Tracker::getDatetimeFromTimestamp($this->timestamp),
'idaction_url' => (int)$this->getIdActionUrl(),
'idaction_name' => $idActionName,
'idaction_url_ref' => $idRefererActionUrl,
'idaction_name_ref' => $idRefererActionName,
'time_spent_ref_action' => $timeSpentRefererAction
);
$customVariables = Piwik_Tracker_Visit::getCustomVariables($scope = 'page', $this->request);
$insert = array_merge($insert, $customVariables);
// Mysqli apparently does not like NULL inserts?
$insertWithoutNulls = array();
foreach($insert as $column => $value)
{
if(!is_null($value))
{
$insertWithoutNulls[$column] = $value;
}
}
$fields = implode(", ", array_keys($insertWithoutNulls));
$bind = array_values($insertWithoutNulls);
$values = Piwik_Common::getSqlStringFieldsArray($insertWithoutNulls);
$sql = "INSERT INTO ".Piwik_Common::prefixTable('log_link_visit_action'). " ($fields) VALUES ($values)";
Piwik_Tracker::getDatabase()->query( $sql, $bind );
$this->idLinkVisitAction = Piwik_Tracker::getDatabase()->lastInsertId();
$info = array(
'idSite' => $this->idSite,
'idLinkVisitAction' => $this->idLinkVisitAction,
'idVisit' => $idVisit,
'idRefererActionUrl' => $idRefererActionUrl,
'idRefererActionName' => $idRefererActionName,
'timeSpentRefererAction' => $timeSpentRefererAction,
);
printDebug($insertWithoutNulls);
/*
* send the Action object ($this) and the list of ids ($info) as arguments to the event
*/
Piwik_PostEvent('Tracker.Action.record', $this, $info);
}
/**
* Performs a batch insert into a specific table by iterating through the data
*
* NOTE: you should use tableInsertBatch() which will fallback to this function if LOAD DATA INFILE not available
*
* @param string $tableName PREFIXED table name! you must call Piwik_Common::prefixTable() before passing the table name
* @param array $fields array of unquoted field names
* @param array $values array of data to be inserted
* @param bool $ignoreWhenDuplicate Ignore new rows that contain unique key values that duplicate old rows
*/
public static function tableInsertBatchIterate($tableName, $fields, $values, $ignoreWhenDuplicate = true)
{
$fieldList = '(' . join(',', $fields) . ')';
$ignore = $ignoreWhenDuplicate ? 'IGNORE' : '';
foreach ($values as $row) {
$query = "INSERT {$ignore}\n\t\t\t\t\tINTO " . $tableName . "\n\t\t\t\t\t{$fieldList}\n\t\t\t\t\tVALUES (" . Piwik_Common::getSqlStringFieldsArray($row) . ")";
Piwik_Query($query, $row);
}
}
/**
* @param $fields
* @return array|array (one row in the array per row fetched in the DB)
*/
private function loadValuesFromDB($fields)
{
$requestedMetrics = is_string($fields) ? array($fields) : $fields;
$inNames = Piwik_Common::getSqlStringFieldsArray($fields);
// get the archive ids
if (!$this->getFirstArchive()->isArchivingDisabled()) {
$archiveIds = $this->getArchiveIdsAfterLaunching($requestedMetrics);
} else {
$archiveIds = $this->getArchiveIdsWithoutLaunching($requestedMetrics);
}
$archiveIds = implode(', ', array_filter($archiveIds));
// if no archive ids are found, avoid executing any SQL queries
if (empty($archiveIds)) {
return array();
}
// select archive data
$sql = "SELECT value, name, idarchive, idsite\n\t\t\t\t\t\t\t\tFROM {$this->getNumericTableName()}\n\t\t\t\t\t\t\t\tWHERE idarchive IN ( {$archiveIds} )\n\t\t\t\t\t\t\t\t\tAND name IN ( {$inNames} )";
return Piwik_FetchAll($sql, $fields);
}
/**
* Save new visitor information to log_visit table.
* Provides pre- and post- event hooks (Tracker.saveVisitorInformation and Tracker.saveVisitorInformation.end) for plugins
*/
protected function saveVisitorInformation()
{
Piwik_PostEvent('Tracker.saveVisitorInformation', $this->visitorInfo);
if (empty($this->visitorInfo['location_country'])) {
$this->visitorInfo['location_country'] = 'xx';
}
$this->visitorInfo['location_continent'] = Piwik_Common::getContinent($this->visitorInfo['location_country']);
$this->visitorInfo['location_browser_lang'] = substr($this->visitorInfo['location_browser_lang'], 0, 20);
$this->visitorInfo['referer_name'] = substr($this->visitorInfo['referer_name'], 0, 70);
$this->visitorInfo['referer_keyword'] = substr($this->visitorInfo['referer_keyword'], 0, 255);
$this->visitorInfo['config_resolution'] = substr($this->visitorInfo['config_resolution'], 0, 9);
$fields = implode(", ", array_keys($this->visitorInfo));
$values = Piwik_Common::getSqlStringFieldsArray($this->visitorInfo);
$sql = "INSERT INTO " . Piwik_Common::prefixTable('log_visit') . " ({$fields}) VALUES ({$values})";
$bind = array_values($this->visitorInfo);
Piwik_Tracker::getDatabase()->query($sql, $bind);
$idVisit = Piwik_Tracker::getDatabase()->lastInsertId();
$this->visitorInfo['idvisit'] = $idVisit;
$this->visitorInfo['visit_first_action_time'] = $this->getCurrentTimestamp();
$this->visitorInfo['visit_last_action_time'] = $this->getCurrentTimestamp();
Piwik_PostEvent('Tracker.saveVisitorInformation.end', $this->visitorInfo);
}
/**
* Returns all websites with a timezone matching one the specified timezones
*
* @param array $timezones
* @ignore
*/
public function getSitesIdFromTimezones($timezones)
{
Piwik::checkUserIsSuperUser();
$timezones = Piwik::getArrayFromApiParameter($timezones);
$timezones = array_unique($timezones);
$ids = Zend_Registry::get('db')->fetchAll('SELECT idsite
FROM ' . Piwik_Common::prefixTable('site') . '
WHERE timezone IN (' . Piwik_Common::getSqlStringFieldsArray($timezones) . ')
ORDER BY idsite ASC', $timezones);
$return = array();
foreach ($ids as $id) {
$return[] = $id['idsite'];
}
return $return;
}
private function loadValuesFromDB($fields)
{
$inNames = Piwik_Common::getSqlStringFieldsArray($fields);
$archiveIds = $this->getArchiveIds();
if(empty($archiveIds))
{
return array();
}
$sql = "SELECT value, name, idarchive, idsite
FROM {$this->getNumericTableName()}
WHERE idarchive IN ( $archiveIds )
AND name IN ( $inNames )";
return Piwik_FetchAll($sql, $fields);
}
