public static function getAllDashboards($login)
{
$dashboards = Piwik_FetchAll('SELECT iddashboard, name
FROM ' . Piwik_Common::prefixTable('user_dashboard') . ' WHERE login = ? ORDER BY iddashboard', array($login));
$pos = 0;
$nameless = 1;
foreach ($dashboards as &$dashboard) {
if (!empty($dashboard['name'])) {
$dashboard['name'] = $dashboard['name'];
} else {
$dashboard['name'] = Piwik_Translate('Dashboard_DashboardOf', $login);
if ($nameless > 1) {
$dashboard['name'] .= " ({$nameless})";
}
if (empty($dashboard['layout'])) {
$layout = '[]';
} else {
$layout = html_entity_decode($dashboard['layout']);
$layout = str_replace("\\\"", "\"", $layout);
}
$dashboard['layout'] = Piwik_Common::json_decode($layout);
$nameless++;
}
$dashboard['name'] = Piwik_Common::unsanitizeInputValue($dashboard['name']);
$pos++;
}
return $dashboards;
}
/**
* Returns the layout in the DB for the given user, or false if the layout has not been set yet.
* Parameters must be checked BEFORE this function call
*
* @param string $login
* @param int $idDashboard
* @param string|false $layout
*/
protected function getLayoutForUser($login, $idDashboard)
{
$paramsBind = array($login, $idDashboard);
$return = Piwik_FetchAll('SELECT layout FROM ' . Piwik::prefixTable('user_dashboard') . ' WHERE login = ? AND iddashboard = ?', $paramsBind);
if (count($return) == 0) {
return false;
}
return $return[0]['layout'];
}
public static function getGoals($idSite)
{
$goals = Piwik_FetchAll("SELECT * \n\t\t\t\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('goal') . " \n\t\t\t\t\t\t\t\t\t\t\tWHERE idsite = ?\n\t\t\t\t\t\t\t\t\t\t\t\tAND deleted = 0", $idSite);
$cleanedGoals = array();
foreach ($goals as &$goal) {
unset($goal['idsite']);
$cleanedGoals[$goal['idgoal']] = $goal;
}
return $cleanedGoals;
}
/** Add SiteSearch config to tracker cache */
public function recordWebsiteDataInCache($notification)
{
$idsite = $notification->getNotificationInfo();
$cache =& $notification->getNotificationObject();
$sql = '
SELECT sitesearch_url, sitesearch_parameter
FROM ' . Piwik_Common::prefixTable('site') . ' AS site
WHERE idsite = ' . intval($idsite) . '
';
$result = Piwik_FetchAll($sql);
$site = $result[0];
$cache['sitesearch_url'] = $site['sitesearch_url'];
$cache['sitesearch_parameter'] = $site['sitesearch_parameter'];
}
public function getFunnels($idSite)
{
$funnel_table = Piwik_Common::prefixTable('funnel');
$goal_table = Piwik_Common::prefixTable('goal');
$funnel_step_table = Piwik_Common::prefixTable('funnel_step');
$funnels = Piwik_FetchAll("SELECT " . $funnel_table . ".*, " . $goal_table . ".name as goal_name, " . $goal_table . ".idgoal\n\t\t\t\t\t\t\t\t FROM " . $funnel_table . ", " . $goal_table . " \n\t\t\t\t\t\t\t\t WHERE " . $funnel_table . ".idsite = ?\n\t\t\t\t\t\t\t\t AND " . $funnel_table . ".idgoal = " . $goal_table . ".idgoal\n\t\t\t\t\t\t\t\t AND " . $funnel_table . ".deleted = 0", array($idSite));
$funnelsById = array();
foreach ($funnels as &$funnel) {
$funnel_steps = Piwik_FetchAll("SELECT *\n\t\t\t\t\t\t\t\t\t\t\tFROM " . $funnel_step_table . "\n\t\t\t\t\t\t\t\t\t\t\tWHERE idsite = ?\n\t\t\t\t\t\t\t\t\t\t\tAND idfunnel = ?\n\t\t\t\t\t\t\t\t\t\t\tAND deleted = 0", array($idSite, $funnel['idfunnel']));
$funnel['steps'] = $funnel_steps;
$funnelsById[$funnel['idfunnel']] = $funnel;
}
return $funnelsById;
}
private function autoload()
{
static $loaded = false;
if ($loaded) {
return;
}
$all = Piwik_FetchAll('SELECT option_value, option_name
FROM `' . Piwik::prefixTable('option') . '`
WHERE autoload = 1');
foreach ($all as $option) {
$this->all[$option['option_name']] = $option['option_value'];
}
$loaded = true;
}
public static function getGoals($idSite)
{
$goals = Piwik_FetchAll("SELECT * \n\t\t\t\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('goal') . " \n\t\t\t\t\t\t\t\t\t\t\tWHERE idsite = ?\n\t\t\t\t\t\t\t\t\t\t\t\tAND deleted = 0", $idSite);
$cleanedGoals = array();
foreach ($goals as &$goal) {
unset($goal['idsite']);
if ($goal['match_attribute'] == 'manually') {
unset($goal['pattern']);
unset($goal['pattern_type']);
unset($goal['case_sensitive']);
}
$cleanedGoals[$goal['idgoal']] = $goal;
}
return $cleanedGoals;
}
static function update()
{
try {
$dashboards = Piwik_FetchAll('SELECT * FROM `' . Piwik_Common::prefixTable('user_dashboard') . '`');
foreach ($dashboards as $dashboard) {
$idDashboard = $dashboard['iddashboard'];
$login = $dashboard['login'];
$layout = $dashboard['layout'];
$layout = html_entity_decode($layout);
$layout = str_replace("\\\"", "\"", $layout);
Piwik_Query('UPDATE `' . Piwik_Common::prefixTable('user_dashboard') . '` SET layout = ? WHERE iddashboard = ? AND login = ?', array($layout, $idDashboard, $login));
}
Piwik_Updater::updateDatabase(__FILE__, self::getSql());
} catch (Exception $e) {
}
}
/**
* Returns all Goals for a given website, or list of websites
*
* @param string|array $idSite Array or Comma separated list of website IDs to request the goals for
* @return array Array of Goal attributes
*/
public function getGoals($idSite)
{
if (!is_array($idSite)) {
$idSite = Piwik_Site::getIdSitesFromIdSitesString($idSite);
}
Piwik::checkUserHasViewAccess($idSite);
$goals = Piwik_FetchAll("SELECT * \n\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('goal') . " \n\t\t\t\t\t\t\t\tWHERE idsite IN (" . implode(", ", $idSite) . ")\n\t\t\t\t\t\t\t\t\tAND deleted = 0");
$cleanedGoals = array();
foreach ($goals as &$goal) {
if ($goal['match_attribute'] == 'manually') {
unset($goal['pattern']);
unset($goal['pattern_type']);
unset($goal['case_sensitive']);
}
$cleanedGoals[$goal['idgoal']] = $goal;
}
return $cleanedGoals;
}
static function update()
{
Piwik_Updater::updateDatabase(__FILE__, self::getSql());
if (!Piwik_PluginsManager::getInstance()->isPluginLoaded('PDFReports')) {
return;
}
try {
// Piwik_Common::prefixTable('pdf') has been heavily refactored to be more generic
// The following actions are taken in this update script :
// - create the new generic report table Piwik_Common::prefixTable('report')
// - migrate previous reports, if any, from Piwik_Common::prefixTable('pdf') to Piwik_Common::prefixTable('report')
// - delete Piwik_Common::prefixTable('pdf')
$reports = Piwik_FetchAll('SELECT * FROM `' . Piwik_Common::prefixTable('pdf') . '`');
foreach ($reports as $report) {
$idreport = $report['idreport'];
$idsite = $report['idsite'];
$login = $report['login'];
$description = $report['description'];
$period = $report['period'];
$format = $report['format'];
$display_format = $report['display_format'];
$email_me = $report['email_me'];
$additional_emails = $report['additional_emails'];
$reports = $report['reports'];
$ts_created = $report['ts_created'];
$ts_last_sent = $report['ts_last_sent'];
$deleted = $report['deleted'];
$parameters = array();
if (!is_null($additional_emails)) {
$parameters[Piwik_PDFReports::ADDITIONAL_EMAILS_PARAMETER] = preg_split('/,/', $additional_emails);
}
$parameters[Piwik_PDFReports::EMAIL_ME_PARAMETER] = is_null($email_me) ? Piwik_PDFReports::EMAIL_ME_PARAMETER_DEFAULT_VALUE : (bool) $email_me;
$parameters[Piwik_PDFReports::DISPLAY_FORMAT_PARAMETER] = $display_format;
Piwik_Query('INSERT INTO `' . Piwik_Common::prefixTable('report') . '` SET
idreport = ?, idsite = ?, login = ?, description = ?, period = ?,
type = ?, format = ?, reports = ?, parameters = ?, ts_created = ?,
ts_last_sent = ?, deleted = ?', array($idreport, $idsite, $login, $description, is_null($period) ? Piwik_PDFReports::DEFAULT_PERIOD : $period, Piwik_PDFReports::EMAIL_TYPE, is_null($format) ? Piwik_PDFReports::DEFAULT_REPORT_FORMAT : $format, Piwik_Common::json_encode(preg_split('/,/', $reports)), Piwik_Common::json_encode($parameters), $ts_created, $ts_last_sent, $deleted));
}
Piwik_Query('DROP TABLE `' . Piwik_Common::prefixTable('pdf') . '`');
} catch (Exception $e) {
}
}
/**
* Returns all Goals for a given website, or list of websites
*
* @param string|array $idSite Array or Comma separated list of website IDs to request the goals for
* @return array Array of Goal attributes
*/
public function getGoals($idSite)
{
//TODO calls to this function could be cached as static
// would help UI at least, since some UI requests would call this 2-3 times..
$idSite = Piwik_Site::getIdSitesFromIdSitesString($idSite);
if (empty($idSite)) {
return array();
}
Piwik::checkUserHasViewAccess($idSite);
$goals = Piwik_FetchAll("SELECT * \n\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('goal') . " \n\t\t\t\t\t\t\t\tWHERE idsite IN (" . implode(", ", $idSite) . ")\n\t\t\t\t\t\t\t\t\tAND deleted = 0");
$cleanedGoals = array();
foreach ($goals as &$goal) {
if ($goal['match_attribute'] == 'manually') {
unset($goal['pattern']);
unset($goal['pattern_type']);
unset($goal['case_sensitive']);
}
$cleanedGoals[$goal['idgoal']] = $goal;
}
return $cleanedGoals;
}
public function getExperiments($idSite)
{
Piwik::checkUserHasViewAccess($idSite);
$experiment_table = Piwik_Common::prefixTable('experiment');
$experiment_page_table = Piwik_Common::prefixTable('experiment_page');
$goal_table = Piwik_Common::prefixTable('goal');
$experiments = Piwik_FetchAll("SELECT " . $experiment_table . ".*, " . $goal_table . ".name as goal_name, " . $goal_table . ".idgoal\n\t\t\t\t\t\t\t\t FROM " . $experiment_table . ", " . $goal_table . " \n\t\t\t\t\t\t\t\t WHERE " . $experiment_table . ".idsite = ?\n\t\t\t\t\t\t\t\t AND " . $experiment_table . ".idgoal = " . $goal_table . ".idgoal\n\t\t\t\t\t\t\t\t AND " . $experiment_table . ".deleted = 0", array($idSite));
$experimentsById = array();
foreach ($experiments as &$experiment) {
$experiment_pages = Piwik_FetchAll("SELECT *\n \t\t\t\t\t\t\t\t \t FROM " . $experiment_page_table . "\n \t\t\t\t\t\t\t\t\t\t\t WHERE idsite = ?\n \t\t\t\t\t\t\t\t\t\t\t AND idexperiment = ?\n \t\t\t\t\t\t\t\t\t\t\t AND deleted = 0", array($idSite, $experiment['idexperiment']));
$experiment['variation_pages'] = array();
foreach ($experiment_pages as $experiment_page) {
if ($experiment_page['original'] == 1) {
$experiment['original_page'] = $experiment_page;
} else {
$experiment['variation_pages'][] = $experiment_page;
}
}
$experimentsById[$experiment['idexperiment']] = $experiment;
}
return $experimentsById;
}
/**
* Returns the idArchive if the archive is available in the database.
* Returns false if the archive needs to be computed.
*
* An archive is available if
* - for today, the archive was computed less than maxTimestampArchive seconds ago
* - for any other day, if the archive was computed once this day was finished
* - for other periods, if the archive was computed once the period was finished
*
* @return int|false
*/
protected function isArchived()
{
$bindSQL = array($this->idsite, $this->strDateStart, $this->strDateEnd, $this->periodId);
$timeStampWhere = " AND UNIX_TIMESTAMP(ts_archived) >= ? ";
$bindSQL[] = $this->maxTimestampArchive;
$sqlQuery = "\tSELECT idarchive, value, name, UNIX_TIMESTAMP(date1) as timestamp\n\t\t\t\t\t\tFROM " . $this->tableArchiveNumeric->getTableName() . "\n\t\t\t\t\t\tWHERE idsite = ?\n\t\t\t\t\t\t\tAND date1 = ?\n\t\t\t\t\t\t\tAND date2 = ?\n\t\t\t\t\t\t\tAND period = ?\n\t\t\t\t\t\t\tAND ( (name = 'done' AND value = " . Piwik_ArchiveProcessing::DONE_OK . ")\n\t\t\t\t\t\t\t\t\tOR name = 'nb_visits')\n\t\t\t\t\t\t\t{$timeStampWhere}\n\t\t\t\t\t\tORDER BY ts_archived DESC";
$results = Piwik_FetchAll($sqlQuery, $bindSQL);
if (empty($results)) {
return false;
}
$idarchive = false;
// we look for the more recent idarchive
foreach ($results as $result) {
if ($result['name'] == 'done') {
$idarchive = $result['idarchive'];
$this->timestampDateStart = $result['timestamp'];
break;
}
}
// case when we have a nb_visits entry in the archive, but the process is not finished yet or failed to finish
// therefore we don't have the done=OK
if ($idarchive === false) {
return false;
}
// we look for the nb_visits result for this more recent archive
foreach ($results as $result) {
if ($result['name'] == 'nb_visits' && $result['idarchive'] == $idarchive) {
$this->isThereSomeVisits = $result['value'] != 0;
break;
}
}
return $idarchive;
}
/**
* Called at the end of the archiving process.
* Does some cleaning job in the database.
*/
protected function postCompute()
{
parent::postCompute();
$blobTable = $this->tableArchiveBlob->getTableName();
$numericTable = $this->tableArchiveNumeric->getTableName();
$key = 'lastPurge_' . $blobTable;
$timestamp = Piwik_GetOption($key);
if (!$timestamp || $timestamp < time() - 86400) {
Piwik_SetOption($key, time());
// we delete out of date daily archives from table, maximum once per day
// we only delete archives processed that are older than 1 day, to not delete archives we just processed
$yesterday = Piwik_Date::factory('yesterday')->getDateTime();
$result = Piwik_FetchAll("\n\t\t\t\t\t\t\tSELECT idarchive\n\t\t\t\t\t\t\tFROM {$numericTable}\n\t\t\t\t\t\t\tWHERE name LIKE 'done%'\n\t\t\t\t\t\t\t\tAND value = " . Piwik_ArchiveProcessing::DONE_OK_TEMPORARY . "\n\t\t\t\t\t\t\t\tAND ts_archived < ?", array($yesterday));
$idArchivesToDelete = array();
if (!empty($result)) {
foreach ($result as $row) {
$idArchivesToDelete[] = $row['idarchive'];
}
$query = "DELETE \n \t\t\t\t\t\tFROM %s\n \t\t\t\t\t\tWHERE idarchive IN (" . implode(',', $idArchivesToDelete) . ")\n \t\t\t\t\t\t";
Piwik_Query(sprintf($query, $blobTable));
Piwik_Query(sprintf($query, $numericTable));
}
Piwik::log("Purging temporary archives: done [ purged archives older than {$yesterday} from {$blobTable} and {$numericTable} ] [Deleted IDs: " . implode(',', $idArchivesToDelete) . "]");
// Deleting "Custom Date Range" reports after 1 day, since they can be re-processed
// and would take up unecessary space
$query = "DELETE \n \t\t\t\t\tFROM %s\n \t\t\t\t\tWHERE period = ?\n \t\t\t\t\t\tAND ts_archived < ?";
$bind = array(Piwik::$idPeriods['range'], $yesterday);
Piwik_Query(sprintf($query, $blobTable), $bind);
Piwik_Query(sprintf($query, $numericTable), $bind);
} else {
Piwik::log("Purging temporary archives: skipped.");
}
if (!isset($this->archives)) {
return;
}
foreach ($this->archives as $archive) {
destroy($archive);
}
$this->archives = array();
}
/**
* Given a monthly archive table, will delete all reports that are now outdated,
* or reports that ended with an error
*/
public static function doPurgeOutdatedArchives($numericTable)
{
$blobTable = str_replace("numeric", "blob", $numericTable);
$key = self::FLAG_TABLE_PURGED . $blobTable;
$timestamp = Piwik_GetOption($key);
// we shall purge temporary archives after their timeout is finished, plus an extra 6 hours
// in case archiving is disabled or run once a day, we give it this extra time to run
// and re-process more recent records...
// TODO: Instead of hardcoding 6 we should put the actual number of hours between 2 archiving runs
$temporaryArchivingTimeout = self::getTodayArchiveTimeToLive();
$purgeEveryNSeconds = max($temporaryArchivingTimeout, 6 * 3600);
// we only delete archives if we are able to process them, otherwise, the browser might process reports
// when &segment= is specified (or custom date range) and would below, delete temporary archives that the
// browser is not able to process until next cron run (which could be more than 1 hour away)
if (self::isRequestAuthorizedToArchive() && (!$timestamp || $timestamp < time() - $purgeEveryNSeconds)) {
Piwik_SetOption($key, time());
// If Browser Archiving is enabled, it is likely there are many more temporary archives
// We delete more often which is safe, since reports are re-processed on demand
if (self::isBrowserTriggerArchivingEnabled()) {
$purgeArchivesOlderThan = Piwik_Date::factory(time() - 2 * $temporaryArchivingTimeout)->getDateTime();
} else {
$purgeArchivesOlderThan = Piwik_Date::factory('today')->getDateTime();
}
$result = Piwik_FetchAll("\n\t\t\t\tSELECT idarchive\n\t\t\t\tFROM {$numericTable}\n\t\t\t\tWHERE name LIKE 'done%'\n\t\t\t\t\tAND (( value = " . Piwik_ArchiveProcessing::DONE_OK_TEMPORARY . "\n\t\t\t\t\t\t AND ts_archived < ?)\n\t\t\t\t\t\t OR value = " . Piwik_ArchiveProcessing::DONE_ERROR . ")", array($purgeArchivesOlderThan));
$idArchivesToDelete = array();
if (!empty($result)) {
foreach ($result as $row) {
$idArchivesToDelete[] = $row['idarchive'];
}
$query = "DELETE \n \t\t\t\t\t\tFROM %s\n \t\t\t\t\t\tWHERE idarchive IN (" . implode(',', $idArchivesToDelete) . ")\n \t\t\t\t\t\t";
Piwik_Query(sprintf($query, $numericTable));
// Individual blob tables could be missing
try {
Piwik_Query(sprintf($query, $blobTable));
} catch (Exception $e) {
}
}
Piwik::log("Purging temporary archives: done [ purged archives older than {$purgeArchivesOlderThan} from {$blobTable} and {$numericTable} ] [Deleted IDs: " . implode(',', $idArchivesToDelete) . "]");
// Deleting "Custom Date Range" reports after 1 day, since they can be re-processed
// and would take up unecessary space
$yesterday = Piwik_Date::factory('yesterday')->getDateTime();
$query = "DELETE \n \t\t\t\t\tFROM %s\n \t\t\t\t\tWHERE period = ?\n \t\t\t\t\t\tAND ts_archived < ?";
$bind = array(Piwik::$idPeriods['range'], $yesterday);
Piwik::log("Purging Custom Range archives: done [ purged archives older than {$yesterday} from {$blobTable} and {$numericTable} ]");
Piwik_Query(sprintf($query, $numericTable), $bind);
// Individual blob tables could be missing
try {
Piwik_Query(sprintf($query, $blobTable), $bind);
} catch (Exception $e) {
}
// these tables will be OPTIMIZEd daily in a scheduled task, to claim lost space
} else {
Piwik::log("Purging temporary archives: skipped.");
}
}
private function loadLastVisitorDetailsFromDatabase($visitorId = null, $idSite = null, $limit = null, $minIdVisit = false)
{
$where = $whereBind = array();
if (!is_null($idSite)) {
$where[] = " idsite = ? ";
$whereBind[] = $idSite;
}
if (!is_null($visitorId)) {
$where[] = " visitor_idcookie = ? ";
$whereBind[] = $visitorId;
}
if (!is_null($minIdVisit)) {
$where[] = " idvisit > ? ";
$whereBind[] = $minIdVisit;
}
$sqlWhere = "";
if (count($where) > 0) {
$sqlWhere = " WHERE " . join(' AND ', $where);
}
$sql = "SELECT \t*\n\t\t\t\tFROM " . Piwik::prefixTable('log_visit') . "\n\t\t\t\t{$sqlWhere} \n\t\t\t\tORDER BY idvisit DESC\n\t\t\t\tLIMIT {$limit}";
return Piwik_FetchAll($sql, $whereBind);
}
/**
* Loads the access levels for the current user.
*
* Calls the authentication method to try to log the user in the system.
* If the user credentials are not correct we don't load anything.
* If the login/password is correct the user is either the SuperUser or a normal user.
* We load the access levels for this user for all the websites.
*
* @return true on success, false if reloading access failed (when auth object wasn't specified and user is not enforced to be Super User)
*/
public function reloadAccess(Piwik_Auth $auth = null)
{
if(!is_null($auth))
{
$this->auth = $auth;
}
// if the Piwik_Auth wasn't set, we may be in the special case of setSuperUser(), otherwise we fail
if(is_null($this->auth))
{
if($this->isSuperUser())
{
return $this->reloadAccessSuperUser();
}
return false;
}
// access = array ( idsite => accessIdSite, idsite2 => accessIdSite2)
$result = $this->auth->authenticate();
if(!$result->isValid())
{
return false;
}
$this->login = $result->getIdentity();
$this->token_auth = $result->getTokenAuth();
// case the superUser is logged in
if($result->getCode() == Piwik_Auth_Result::SUCCESS_SUPERUSER_AUTH_CODE)
{
return $this->reloadAccessSuperUser();
}
// case valid authentification (normal user logged in)
// we join with site in case there are rows in access for an idsite that doesn't exist anymore
// (backward compatibility ; before we deleted the site without deleting rows in _access table)
$accessRaw = Piwik_FetchAll("SELECT access, t2.idsite
FROM ".Piwik::prefixTable('access'). " as t1
JOIN ".Piwik::prefixTable('site')." as t2 USING (idsite) ".
" WHERE login = ?", $this->login);
foreach($accessRaw as $access)
{
$this->idsitesByAccess[$access['access']][] = $access['idsite'];
}
return true;
}
/**
* Execute the query.
* The object has to be configured first using the other methods.
*
* @param $innerQuery string The "payload" query. The result has be sorted as desired.
* @param $bind array Bindings for the inner query.
* @return array The format depends on which methods have been used
* to configure the ranking query
*/
public function execute($innerQuery, $bind = array())
{
$query = $this->generateQuery($innerQuery);
$data = Piwik_FetchAll($query, $bind);
if ($this->columnToMarkExcludedRows !== false) {
// split the result into the regular result and the rows with special treatment
$excludedFromLimit = array();
$result = array();
foreach ($data as &$row) {
if ($row[$this->columnToMarkExcludedRows] != 0) {
$excludedFromLimit[] = $row;
} else {
$result[] = $row;
}
}
$data = array('result' => &$result, 'excludedFromLimit' => &$excludedFromLimit);
}
if ($this->partitionColumn !== false) {
if ($this->columnToMarkExcludedRows !== false) {
$data['result'] = $this->splitPartitions($data['result']);
} else {
$data = $this->splitPartitions($data);
}
}
return $data;
}
protected function _checkTableIsExpectedBlob($table, $data)
{
$fetched = Piwik_FetchAll('SELECT * FROM ' . $table);
foreach ($data as $id => $row) {
$this->assertEquals($fetched[$id]['idarchive'], $data[$id][0], "record {$id} idarchive is not '{$data[$id][0]}'");
$this->assertEquals($fetched[$id]['name'], $data[$id][1], "record {$id} name is not '{$data[$id][1]}'");
$this->assertEquals($fetched[$id]['idsite'], $data[$id][2], "record {$id} idsite is not '{$data[$id][2]}'");
$this->assertEquals($fetched[$id]['date1'], $data[$id][3], "record {$id} date1 is not '{$data[$id][3]}'");
$this->assertEquals($fetched[$id]['date2'], $data[$id][4], "record {$id} date2 is not '{$data[$id][4]}'");
$this->assertEquals($fetched[$id]['period'], $data[$id][5], "record {$id} period is not '{$data[$id][5]}'");
$this->assertEquals($fetched[$id]['ts_archived'], $data[$id][6], "record {$id} ts_archived is not '{$data[$id][6]}'");
$this->assertEquals($fetched[$id]['value'], $data[$id][7], "record {$id} value is unexpected");
}
}
public static function getRawSitesWithSomeViewAccess($login)
{
return Piwik_FetchAll(self::getSqlAccessSite("access, t2.idsite"), $login);
}
private function loadValuesFromDB($fields)
{
$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 ( {$this->getArchiveIds()} )\n\t\t\t\t\t\t\t\t\tAND name IN ( {$this->getSqlStringFieldsArray($fields)} )";
return Piwik_FetchAll($sql);
}
/**
* Returns the list of all the websites ID registered
*
* @return array the list of websites ID
*/
public static function getAllSitesId()
{
Piwik::checkUserIsSuperUser();
$result = Piwik_FetchAll("SELECT idsite FROM " . Piwik::prefixTable('site'));
$idSites = array();
foreach ($result as $idSite) {
$idSites[] = $idSite['idsite'];
}
return $idSites;
}
/** Build DataTable from sql
* @return Piwik_DataTable */
private function buildDataTableFromSql($sql, $bind = array())
{
$data = Piwik_FetchAll($sql, $bind);
return $this->buildDataTable($data);
}
private function loadLastVisitorDetailsFromDatabase($idSite, $period = false, $date = false, $segment = false, $filter_limit = false, $maxIdVisit = false, $visitorId = false, $minTimestamp = false)
{
// var_dump($period); var_dump($date); var_dump($filter_limit); var_dump($maxIdVisit); var_dump($visitorId);
//var_dump($minTimestamp);
if(empty($filter_limit))
{
$filter_limit = 100;
}
$where = $whereBind = array();
$where[] = "log_visit.idsite = ? ";
$whereBind[] = $idSite;
$orderBy = "idsite, visit_last_action_time DESC";
$orderByParent = "sub.visit_last_action_time DESC";
if(!empty($visitorId))
{
$where[] = "log_visit.idvisitor = ? ";
$whereBind[] = Piwik_Common::hex2bin($visitorId);
}
if(!empty($maxIdVisit))
{
$where[] = "log_visit.idvisit < ? ";
$whereBind[] = $maxIdVisit;
$orderBy = "idvisit DESC";
$orderByParent = "sub.idvisit DESC";
}
if(!empty($minTimestamp))
{
$where[] = "log_visit.visit_last_action_time > ? ";
$whereBind[] = date("Y-m-d H:i:s", $minTimestamp);
}
// If no other filter, only look at the last 24 hours of stats
if(empty($visitorId)
&& empty($maxIdVisit)
&& empty($period)
&& empty($date))
{
$period = 'day';
$date = 'yesterdaySameTime';
}
// SQL Filter with provided period
if (!empty($period) && !empty($date))
{
$currentSite = new Piwik_Site($idSite);
$currentTimezone = $currentSite->getTimezone();
$dateString = $date;
if($period == 'range')
{
$processedPeriod = new Piwik_Period_Range('range', $date);
if($parsedDate = Piwik_Period_Range::parseDateRange($date))
{
$dateString = $parsedDate[2];
}
}
else
{
$processedDate = Piwik_Date::factory($date);
if($date == 'today'
|| $date == 'now'
|| $processedDate->toString() == Piwik_Date::factory('now', $currentTimezone)->toString())
{
$processedDate = $processedDate->subDay(1);
}
$processedPeriod = Piwik_Period::factory($period, $processedDate);
}
$dateStart = $processedPeriod->getDateStart()->setTimezone($currentTimezone);
$where[] = "log_visit.visit_last_action_time >= ?";
$whereBind[] = $dateStart->toString('Y-m-d H:i:s');
if(!in_array($date, array('now', 'today', 'yesterdaySameTime'))
&& strpos($date, 'last') === false
&& strpos($date, 'previous') === false
&& Piwik_Date::factory($dateString)->toString('Y-m-d') != Piwik_Date::factory('now', $currentTimezone)->toString())
{
$dateEnd = $processedPeriod->getDateEnd()->setTimezone($currentTimezone);
$where[] = " log_visit.visit_last_action_time <= ?";
$dateEndString = $dateEnd->addDay(1)->toString('Y-m-d H:i:s');
$whereBind[] = $dateEndString;
}
}
$sqlWhere = "";
if(count($where) > 0)
{
$sqlWhere = "
WHERE " . join("
AND ", $where);
}
$segment = new Piwik_Segment($segment, $idSite);
$segmentSql = $segment->getSql();
$sqlSegment = $segmentSql['sql'];
if(!empty($sqlSegment)) $sqlSegment = ' AND '.$sqlSegment;
$whereBind = array_merge ( $whereBind, $segmentSql['bind'] );
// Subquery to use the indexes for ORDER BY
// Group by idvisit so that a visitor converting 2 goals only appears twice
$sql = "
SELECT sub.*
FROM (
SELECT *
FROM " . Piwik_Common::prefixTable('log_visit') . " AS log_visit
$sqlWhere
$sqlSegment
ORDER BY $orderBy
LIMIT ".(int)$filter_limit."
) AS sub
GROUP BY sub.idvisit
ORDER BY $orderByParent
";
try {
$data = Piwik_FetchAll($sql, $whereBind);
} catch(Exception $e) {
echo $e->getMessage();exit;
}
//var_dump($whereBind); echo($sql);
//var_dump($data);
return $data;
}
/**
* Returns an array associating table names w/ lists of row data.
*
* @return array
*/
protected static function getDbTablesWithData()
{
$result = array();
foreach (Piwik::getTablesInstalled() as $tableName) {
$result[$tableName] = Piwik_FetchAll("SELECT * FROM {$tableName}");
}
return $result;
}
/**
* Loads the access levels for the current user.
*
* Calls the authentication method to try to log the user in the system.
* If the user credentials are not correct we don't load anything.
* If the login/password is correct the user is either the SuperUser or a normal user.
* We load the access levels for this user for all the websites.
*
* @return true on success, false if reloading access failed (when auth object wasn't specified and user is not enforced to be Super User)
*/
public function reloadAccess(Piwik_Auth $auth = null)
{
if (!is_null($auth)) {
$this->auth = $auth;
}
// if the Piwik_Auth wasn't set, we may be in the special case of setSuperUser(), otherwise we fail
if (is_null($this->auth)) {
if ($this->isSuperUser()) {
return $this->reloadAccessSuperUser();
}
return false;
}
// access = array ( idsite => accessIdSite, idsite2 => accessIdSite2)
$result = $this->auth->authenticate();
if (!$result->isValid()) {
return false;
}
$this->login = $result->getIdentity();
$this->token_auth = $result->getTokenAuth();
// case the superUser is logged in
if ($result->getCode() == Piwik_Auth_Result::SUCCESS_SUPERUSER_AUTH_CODE) {
return $this->reloadAccessSuperUser();
}
// in case multiple calls to API using different tokens, we ensure we reset it as not SU
$this->setSuperUser(false);
// we join with site in case there are rows in access for an idsite that doesn't exist anymore
// (backward compatibility ; before we deleted the site without deleting rows in _access table)
$accessRaw = Piwik_FetchAll(self::getSqlAccessSite("access, t2.idsite"), $this->login);
foreach ($accessRaw as $access) {
$this->idsitesByAccess[$access['access']][] = $access['idsite'];
}
return true;
}
/**
* Returns the list of reports matching the passed parameters
*
* @param int $idSite If specified, will filter reports that belong to a specific idsite
* @param string $period If specified, will filter reports that are scheduled for this period (day,week,month)
* @param int $idReport If specified, will filter the report that has the given idReport
* @return array
* @throws Exception if $idReport was specified but the report wasn't found
*/
public function getReports($idSite = false, $period = false, $idReport = false, $ifSuperUserReturnOnlySuperUserReports = false)
{
Piwik::checkUserIsNotAnonymous();
$cacheKey = (int) $idSite . '.' . (string) $period . '.' . (int) $idReport . '.' . (int) $ifSuperUserReturnOnlySuperUserReports;
if (isset(self::$cache[$cacheKey])) {
return self::$cache[$cacheKey];
}
$sqlWhere = '';
$bind = array();
// Super user gets all reports back, other users only their own
if (!Piwik::isUserIsSuperUser() || $ifSuperUserReturnOnlySuperUserReports) {
$sqlWhere .= "AND login = ?";
$bind[] = Piwik::getCurrentUserLogin();
}
if (!empty($period)) {
$this->validateReportPeriod($period);
$sqlWhere .= " AND period = ? ";
$bind[] = $period;
}
if (!empty($idSite)) {
Piwik::checkUserHasViewAccess($idSite);
$sqlWhere .= " AND " . Piwik_Common::prefixTable('site') . ".idsite = ?";
$bind[] = $idSite;
}
if (!empty($idReport)) {
$sqlWhere .= " AND idreport = ?";
$bind[] = $idReport;
}
// Joining with the site table to work around pre-1.3 where reports could still be linked to a deleted site
$reports = Piwik_FetchAll("SELECT * \n\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('report') . "\n\t\t\t\t\t\t\t\t\tJOIN " . Piwik_Common::prefixTable('site') . "\n\t\t\t\t\t\t\t\t\tUSING (idsite)\n\t\t\t\t\t\t\t\tWHERE deleted = 0\n\t\t\t\t\t\t\t\t\t{$sqlWhere}", $bind);
// When a specific report was requested and not found, throw an error
if ($idReport !== false && empty($reports)) {
throw new Exception("Requested report couldn't be found.");
}
foreach ($reports as &$report) {
// decode report parameters
$report['parameters'] = Piwik_Common::json_decode($report['parameters'], true);
// decode report list
$report['reports'] = Piwik_Common::json_decode($report['reports'], true);
}
// static cache
self::$cache[$cacheKey] = $reports;
return $reports;
}
/**
* Called at the end of the archiving process.
* Does some cleaning job in the database.
*/
protected function postCompute()
{
parent::postCompute();
$blobTable = $this->tableArchiveBlob->getTableName();
$numericTable = $this->tableArchiveNumeric->getTableName();
$key = 'lastPurge_' . $blobTable;
$timestamp = Piwik_GetOption($key);
// we shall purge temporary archives after their timeout is finished, plus an extra 2 hours
// in case archiving is disabled and is late to run, we give it this extra time to run and re-process more recent records
$temporaryArchivingTimeout = self::getTodayArchiveTimeToLive();
$purgeEveryNSeconds = $temporaryArchivingTimeout + 2 * 3600;
// we only delete archives if we are able to process them, otherwise, the browser might process reports
// when &segment= is specified (or custom date range) and would below, delete temporary archives that the
// browser is not able to process until next cron run (which could be more than 1 hour away)
if ($this->isRequestAuthorizedToArchive() && (!$timestamp || $timestamp < time() - $purgeEveryNSeconds)) {
Piwik_SetOption($key, time());
$purgeArchivesOlderThan = Piwik_Date::factory(time() - $purgeEveryNSeconds)->getDateTime();
$result = Piwik_FetchAll("\n\t\t\t\t\t\t\tSELECT idarchive\n\t\t\t\t\t\t\tFROM {$numericTable}\n\t\t\t\t\t\t\tWHERE name LIKE 'done%'\n\t\t\t\t\t\t\t\tAND value = " . Piwik_ArchiveProcessing::DONE_OK_TEMPORARY . "\n\t\t\t\t\t\t\t\tAND ts_archived < ?", array($purgeArchivesOlderThan));
$idArchivesToDelete = array();
if (!empty($result)) {
foreach ($result as $row) {
$idArchivesToDelete[] = $row['idarchive'];
}
$query = "DELETE \n \t\t\t\t\t\tFROM %s\n \t\t\t\t\t\tWHERE idarchive IN (" . implode(',', $idArchivesToDelete) . ")\n \t\t\t\t\t\t";
Piwik_Query(sprintf($query, $blobTable));
Piwik_Query(sprintf($query, $numericTable));
}
Piwik::log("Purging temporary archives: done [ purged archives older than {$purgeArchivesOlderThan} from {$blobTable} and {$numericTable} ] [Deleted IDs: " . implode(',', $idArchivesToDelete) . "]");
// Deleting "Custom Date Range" reports after 1 day, since they can be re-processed
// and would take up unecessary space
$yesterday = Piwik_Date::factory('yesterday')->getDateTime();
$query = "DELETE \n \t\t\t\t\tFROM %s\n \t\t\t\t\tWHERE period = ?\n \t\t\t\t\t\tAND ts_archived < ?";
$bind = array(Piwik::$idPeriods['range'], $yesterday);
Piwik::log("Purging Custom Range archives: done [ purged archives older than {$yesterday} from {$blobTable} and {$numericTable} ]");
Piwik_Query(sprintf($query, $blobTable), $bind);
Piwik_Query(sprintf($query, $numericTable), $bind);
// these tables will be OPTIMIZEd daily in a scheduled task, to claim lost space
} else {
Piwik::log("Purging temporary archives: skipped.");
}
if (!isset($this->archives)) {
return;
}
foreach ($this->archives as $archive) {
destroy($archive);
}
$this->archives = array();
}
/**
* Returns the idArchive if the archive is available in the database.
* Returns false if the archive needs to be computed.
*
* An archive is available if
* - for today, the archive was computed less than minDatetimeArchiveProcessedUTC seconds ago
* - for any other day, if the archive was computed once this day was finished
* - for other periods, if the archive was computed once the period was finished
*
* @return int|false
*/
protected function isArchived()
{
$bindSQL = array($this->idsite, $this->period->getDateStart()->toString('Y-m-d'), $this->period->getDateEnd()->toString('Y-m-d'), $this->periodId);
$timeStampWhere = '';
if ($this->minDatetimeArchiveProcessedUTC) {
$timeStampWhere = " AND ts_archived >= ? ";
$bindSQL[] = Piwik_Date::factory($this->minDatetimeArchiveProcessedUTC)->getDatetime();
}
// When a Segment is specified, we try and only process the requested report in the archive
// As a limitation, we don't know all the time which plugin should process which report
// There is a catch all flag 'all' appended to archives containing all reports already
// We look for this 'done.ABCDEFG.all', or for an archive that contains only our plugin data 'done.ABDCDEFG.Referers'
$done = $this->getDoneStringFlag();
$doneAllPluginsProcessed = $this->getDoneStringFlag($flagArchiveAsAllPlugins = true);
$sqlSegmentsFindArchiveAllPlugins = '';
if ($done != $doneAllPluginsProcessed) {
$sqlSegmentsFindArchiveAllPlugins = "OR (name = '" . $doneAllPluginsProcessed . "' AND value = " . Piwik_ArchiveProcessing::DONE_OK . ")\n\t\t\t\t\tOR (name = '" . $doneAllPluginsProcessed . "' AND value = " . Piwik_ArchiveProcessing::DONE_OK_TEMPORARY . ")";
}
$sqlQuery = "\tSELECT idarchive, value, name, date1 as startDate\n\t\t\t\t\t\tFROM " . $this->tableArchiveNumeric->getTableName() . "\n\t\t\t\t\t\tWHERE idsite = ?\n\t\t\t\t\t\t\tAND date1 = ?\n\t\t\t\t\t\t\tAND date2 = ?\n\t\t\t\t\t\t\tAND period = ?\n\t\t\t\t\t\t\tAND ( (name = '" . $done . "' AND value = " . Piwik_ArchiveProcessing::DONE_OK . ")\n\t\t\t\t\t\t\t\t\tOR (name = '" . $done . "' AND value = " . Piwik_ArchiveProcessing::DONE_OK_TEMPORARY . ")\n\t\t\t\t\t\t\t\t\t{$sqlSegmentsFindArchiveAllPlugins}\n\t\t\t\t\t\t\t\t\tOR name = 'nb_visits')\n\t\t\t\t\t\t\t{$timeStampWhere}\n\t\t\t\t\t\tORDER BY ts_archived DESC";
$results = Piwik_FetchAll($sqlQuery, $bindSQL);
if (empty($results)) {
return false;
}
$idarchive = false;
// we look for the more recent idarchive
foreach ($results as $result) {
if ($result['name'] == $done || $result['name'] == $doneAllPluginsProcessed) {
$idarchive = $result['idarchive'];
$this->timestampDateStart = Piwik_Date::factory($result['startDate'])->getTimestamp();
break;
}
}
// case when we have a nb_visits entry in the archive, but the process is not finished yet or failed to finish
// therefore we don't have the done=OK
if ($idarchive === false) {
return false;
}
if ($this->getPluginBeingProcessed($this->getRequestedReport()) == 'VisitsSummary') {
$this->isThereSomeVisits = false;
}
// we look for the nb_visits result for this most recent archive
foreach ($results as $result) {
if ($result['name'] == 'nb_visits' && $result['idarchive'] == $idarchive) {
$this->isThereSomeVisits = $result['value'] > 0;
$this->setNumberOfVisits($result['value']);
break;
}
}
return $idarchive;
}
/**
* Returns the list of unique timezones from all configured sites.
*
* @return array ( string )
*/
public function getUniqueSiteTimezones()
{
Piwik::checkUserIsSuperUser();
$results = Piwik_FetchAll("SELECT distinct timezone FROM ".Piwik_Common::prefixTable('site'));
$timezones = array();
foreach($results as $result)
{
$timezones[] = $result['timezone'];
}
return $timezones;
}
