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; }