protected function loadSettings() { $sql = "SELECT `setting_name`, `setting_value` FROM " . $this->getTableName() . " WHERE idsite = ?"; $bind = array($this->idSite); $settings = $this->db->fetchAll($sql, $bind); $flat = array(); foreach ($settings as $setting) { $flat[$setting['setting_name']] = unserialize($setting['setting_value']); } return $flat; }
public static function getUsersBySite($idsite) { $getRegularUsers = Db::fetchAll("SELECT login,\n (SELECT email FROM " . Common::prefixTable('user') . " WHERE login = acc.login) AS email\n FROM " . Common::prefixTable('access') . " AS acc WHERE idsite = ?", array($idsite)); $getSuperUsers = Db::fetchAll("SELECT login,email FROM " . Common::prefixTable('user') . " WHERE superuser_access = 1"); $getUsers = array_merge($getRegularUsers, $getSuperUsers); return $getUsers; }
private function assertDuplicatesFixedInLogConversionItemTable() { $columns = array('idaction_sku', 'idaction_name', 'idaction_category', 'idaction_category2', 'idaction_category3', 'idaction_category4', 'idaction_category5'); $rows = Db::fetchAll("SELECT " . implode(',', $columns) . " FROM " . Common::prefixTable('log_conversion_item')); $expectedRows = array(array('idaction_sku' => '1', 'idaction_name' => '1', 'idaction_category' => '1', 'idaction_category2' => '4', 'idaction_category3' => '5', 'idaction_category4' => '6', 'idaction_category5' => '5'), array('idaction_sku' => '1', 'idaction_name' => '1', 'idaction_category' => '5', 'idaction_category2' => '5', 'idaction_category3' => '8', 'idaction_category4' => '4', 'idaction_category5' => '6')); $this->assertEquals($expectedRows, $rows); }
public function test_delete_ConvertsIdActionsToInt() { $this->actionsAccess->delete(array("2", "0, 1")); $expectedActions = array(array('name' => 'action1'), array('name' => 'action3')); $actualActions = Db::fetchAll("SELECT name FROM " . Common::prefixTable('log_action')); $this->assertEquals($expectedActions, $actualActions); }
/** * Analyzes numeric & blob tables for a single table date (ie, `'2015_01'`) and returns * statistics including: * * - number of archives present * - number of invalidated archives * - number of temporary archives * - number of error archives * - number of segment archives * - number of numeric rows * - number of blob rows * * @param string $tableDate ie `'2015_01'` * @return array */ public function getArchiveTableAnalysis($tableDate) { $numericQueryEmptyRow = array('count_archives' => '-', 'count_invalidated_archives' => '-', 'count_temporary_archives' => '-', 'count_error_archives' => '-', 'count_segment_archives' => '-', 'count_numeric_rows' => '-'); $tableDate = str_replace("`", "", $tableDate); // for sanity $numericTable = Common::prefixTable("archive_numeric_{$tableDate}"); $blobTable = Common::prefixTable("archive_blob_{$tableDate}"); // query numeric table $sql = "SELECT CONCAT_WS('.', idsite, date1, date2, period) AS label,\n SUM(CASE WHEN name LIKE 'done%' THEN 1 ELSE 0 END) AS count_archives,\n SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_invalidated_archives,\n SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_temporary_archives,\n SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_error_archives,\n SUM(CASE WHEN name LIKE 'done%' AND CHAR_LENGTH(name) > 32 THEN 1 ELSE 0 END) AS count_segment_archives,\n SUM(CASE WHEN name NOT LIKE 'done%' THEN 1 ELSE 0 END) AS count_numeric_rows,\n 0 AS count_blob_rows\n FROM `{$numericTable}`\n GROUP BY idsite, date1, date2, period"; $rows = Db::fetchAll($sql, array(ArchiveWriter::DONE_INVALIDATED, ArchiveWriter::DONE_OK_TEMPORARY, ArchiveWriter::DONE_ERROR)); // index result $result = array(); foreach ($rows as $row) { $result[$row['label']] = $row; } // query blob table & manually merge results (no FULL OUTER JOIN in mysql) $sql = "SELECT CONCAT_WS('.', idsite, date1, date2, period) AS label,\n COUNT(*) AS count_blob_rows\n FROM `{$blobTable}`\n GROUP BY idsite, date1, date1, period"; foreach (Db::fetchAll($sql) as $blobStatsRow) { $label = $blobStatsRow['label']; if (isset($result[$label])) { $result[$label] = array_merge($result[$label], $blobStatsRow); } else { $result[$label] = $blobStatsRow + $numericQueryEmptyRow; } } return $result; }
public function test_fixDuplicateActionsInTable_CorrectlyUpdatesIdActionColumns_InSpecifiedTable() { $this->duplicateActionRemover->fixDuplicateActionsInTable('log_conversion_item', 5, array(3, 6, 7, 10)); $columns = array('idaction_sku', 'idaction_name', 'idaction_category', 'idaction_category2', 'idaction_category3', 'idaction_category4', 'idaction_category5'); $expectedResult = array(array('idaction_sku' => '1', 'idaction_name' => '2', 'idaction_category' => '5', 'idaction_category2' => '4', 'idaction_category3' => '5', 'idaction_category4' => '5', 'idaction_category5' => '5'), array('idaction_sku' => '2', 'idaction_name' => '5', 'idaction_category' => '5', 'idaction_category2' => '5', 'idaction_category3' => '8', 'idaction_category4' => '9', 'idaction_category5' => '5')); $actualResult = Db::fetchAll("SELECT " . implode(", ", $columns) . " FROM " . Common::prefixTable('log_conversion_item')); $this->assertEquals($expectedResult, $actualResult); }
private function getLdapUserLogins() { $rows = Db::fetchAll("SELECT login from " . Common::prefixTable('user') . " WHERE password LIKE '{LDAP}%'"); $result = array(); foreach ($rows as $row) { $result[] = $row['login']; } return $result; }
/** * Returns the list of all the website IDs registered. * Caller must check access. * * @return array The list of website IDs */ public function getSitesId() { $result = Db::fetchAll("SELECT idsite FROM " . Common::prefixTable('site')); $idSites = array(); foreach ($result as $idSite) { $idSites[] = $idSite['idsite']; } return $idSites; }
public static function getUnreadConversations($login) { $conversations = array(); $rows = Db::fetchAll("SELECT idvisitor, lastviewed, lastsent FROM " . Common::prefixTable('chat_history_admin') . " WHERE login = ? AND lastsent > lastviewed", array($login)); $rows = ChatCommon::formatRows($rows); foreach ($rows as $row) { $conversations[$row['idvisitor']] = $row; } return $conversations; }
public static function setUpBeforeClass() { parent::setUpBeforeClass(); // add duplicates for every action $table = Common::prefixTable('log_action'); foreach (Db::fetchAll("SELECT * FROM {$table}") as $row) { $insertSql = "INSERT INTO {$table} (name, type, hash, url_prefix)\n VALUES (?, ?, CRC32(?), ?)"; Db::query($insertSql, array($row['name'], $row['type'], $row['name'], $row['url_prefix'])); } }
/** * Returns the list of column names for a table. * * @param string $table Prefixed table name. * @return string[] List of column names.. */ public function getColumns($table) { $table = str_replace("`", "", $table); $columns = Db::fetchAll("SHOW COLUMNS FROM `" . $table . "`"); $columnNames = array(); foreach ($columns as $column) { $columnNames[] = $column['Field']; } return $columnNames; }
protected static function getTemporaryArchiveIdsOlderThan(Date $date, $purgeArchivesOlderThan) { $query = "SELECT idarchive\n FROM " . ArchiveTableCreator::getNumericTable($date) . "\n WHERE name LIKE 'done%'\n AND (( value = " . ArchiveWriter::DONE_OK_TEMPORARY . "\n AND ts_archived < ?)\n OR value = " . ArchiveWriter::DONE_ERROR . ")"; $result = Db::fetchAll($query, array($purgeArchivesOlderThan)); $idArchivesToDelete = array(); if (!empty($result)) { foreach ($result as $row) { $idArchivesToDelete[] = $row['idarchive']; } } return $idArchivesToDelete; }
public function getArchiveIdAndVisits($numericTable, $idSite, $period, $dateStartIso, $dateEndIso, $minDatetimeIsoArchiveProcessedUTC, $doneFlags, $possibleValues) { $bindSQL = array($idSite, $dateStartIso, $dateEndIso, $period); $timeStampWhere = ''; if ($minDatetimeIsoArchiveProcessedUTC) { $timeStampWhere = " AND ts_archived >= ? "; $bindSQL[] = $minDatetimeIsoArchiveProcessedUTC; } $sqlWhereArchiveName = self::getNameCondition($doneFlags, $possibleValues); $sqlQuery = "SELECT idarchive, value, name, date1 as startDate FROM {$numericTable}\n WHERE idsite = ?\n AND date1 = ?\n AND date2 = ?\n AND period = ?\n AND ( ({$sqlWhereArchiveName})\n OR name = '" . ArchiveSelector::NB_VISITS_RECORD_LOOKED_UP . "'\n OR name = '" . ArchiveSelector::NB_VISITS_CONVERTED_RECORD_LOOKED_UP . "')\n {$timeStampWhere}\n ORDER BY idarchive DESC"; $results = Db::fetchAll($sqlQuery, $bindSQL); return $results; }
static function update() { try { $dashboards = Db::fetchAll('SELECT * FROM `' . 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); Db::query('UPDATE `' . Common::prefixTable('user_dashboard') . '` SET layout = ? WHERE iddashboard = ? AND login = ?', array($layout, $idDashboard, $login)); } Updater::updateDatabase(__FILE__, self::getSql()); } catch (\Exception $e) { } }
public function doUpdate(Updater $updater) { try { $dashboards = Db::fetchAll('SELECT * FROM `' . 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); Db::query('UPDATE `' . Common::prefixTable('user_dashboard') . '` SET layout = ? WHERE iddashboard = ? AND login = ?', array($layout, $idDashboard, $login)); } $updater->executeMigrationQueries(__FILE__, $this->getMigrationQueries($updater)); } catch (\Exception $e) { } }
public function doUpdate(Updater $updater) { $updater->executeMigrations(__FILE__, $this->getMigrations($updater)); if (!\Piwik\Plugin\Manager::getInstance()->isPluginLoaded('ScheduledReports')) { return; } try { // 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 Common::prefixTable('report') // - migrate previous reports, if any, from Common::prefixTable('pdf') to Common::prefixTable('report') // - delete Common::prefixTable('pdf') $reports = Db::fetchAll('SELECT * FROM `' . 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[ScheduledReports::ADDITIONAL_EMAILS_PARAMETER] = preg_split('/,/', $additional_emails); } $parameters[ScheduledReports::EMAIL_ME_PARAMETER] = is_null($email_me) ? ScheduledReports::EMAIL_ME_PARAMETER_DEFAULT_VALUE : (bool) $email_me; $parameters[ScheduledReports::DISPLAY_FORMAT_PARAMETER] = $display_format; Db::query('INSERT INTO `' . 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) ? ScheduledReports::DEFAULT_PERIOD : $period, ScheduledReports::EMAIL_TYPE, is_null($format) ? ScheduledReports::DEFAULT_REPORT_FORMAT : $format, json_encode(preg_split('/,/', $reports)), json_encode($parameters), $ts_created, $ts_last_sent, $deleted)); } $updater->executeMigration(__FILE__, $this->migration->db->dropTable('pdf')); } catch (\Exception $e) { } }
public function doUpdate(Updater $updater) { try { $migrations = array(); $table = Common::prefixTable('user_dashboard'); $dashboards = Db::fetchAll('SELECT iddashboard, login, layout FROM `' . $table . '`'); $updateQuery = 'UPDATE `' . $table . '` SET layout = ? WHERE iddashboard = ? AND login = ?'; foreach ($dashboards as $dashboard) { $idDashboard = $dashboard['iddashboard']; $login = $dashboard['login']; $layout = $dashboard['layout']; $layout = html_entity_decode($layout); $layout = str_replace("\\\"", "\"", $layout); $migrations[] = $this->migration->db->boundSql($updateQuery, array($layout, $idDashboard, $login)); } $updater->executeMigrations(__FILE__, $migrations); $updater->executeMigrations(__FILE__, $this->getMigrations($updater)); } 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 = Site::getIdSitesFromIdSitesString($idSite); if (empty($idSite)) { return array(); } Piwik::checkUserHasViewAccess($idSite); $goals = Db::fetchAll("SELECT *\n\t\t\t\t\t\t\t\tFROM " . 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; }
private function bannerStats($bannerName, $params) { $contentPiece = false; if (strpos($bannerName, '_') !== false) { list($bannerName, $contentPiece) = explode('_', $bannerName); } $segment = 'contentName==' . $bannerName; $recordName = Dimensions::getRecordNameForAction('getContentPieces'); $subTable = Archive::getDataTableFromArchive($recordName, $params['idSite'], $params['period'], $params['date'], $segment, true); //echo '<pre>'; $bannerTable = new DataTable(); if (!$contentPiece) { foreach ($subTable->getRows() as $row) { $ContentPieceId = Db::fetchOne("SELECT idaction FROM piwik_log_action WHERE TYPE = 14 and name = ?", array($row->getColumn('label'))); $bannerRow = new Row(array(Row::COLUMNS => array('Label' => $row->getColumn('label'), 'Impressions' => $row->getColumn(41), 'Interactions' => $row->getColumn(42), 'Conversion rate' => $this->interactionRate($row->getColumn(41), $row->getColumn(42))), Row::DATATABLE_ASSOCIATED => implode('_', array($bannerName, $ContentPieceId)))); $bannerTable->addRow($bannerRow); } } else { $orderColumn = str_replace(' ', '_', strtolower($params['filter_sort_column'])); $orderOrder = in_array($params['filter_sort_order'], array('asc', 'desc')) ? $params['filter_sort_order'] : 'asc'; $orderLimit = intval($params['filter_limit']); $where = ''; /* TODO: filter_pattern is processed by piwik in some way. The results are good with this query, but piwik does some post-processing? if (isset($params['filter_pattern'])) { $where = 'and piwik_log_action.name like "%' . $params['filter_pattern'] . '%"'; } */ $result = Db::fetchAll("\n SELECT \n trim(substring_index(piwik_log_action.name, '|', 1)) as referrer,\n trim(substring_index(piwik_log_action.name, '|', -1)) as target,\n sum(IF(idaction_content_interaction is null, 1, 0)) as impressions, \n sum(IF(idaction_content_interaction is null, 0, 1)) as interactions,\n ((100 / sum(IF(idaction_content_interaction is null, 1, 0))) * sum(IF(idaction_content_interaction is null, 0, 1))) as conversion_rate\n FROM piwik_log_link_visit_action \n left join piwik_log_action on piwik_log_action.idaction = idaction_content_target\n WHERE \n idaction_content_name in (SELECT idaction FROM piwik_log_action WHERE name = ?)\n and\n idaction_content_piece = ?\n \n {$where}\n\n group by piwik_log_action.name\n order by {$orderColumn} {$orderOrder}\n limit {$orderLimit}\n ", array($bannerName, $contentPiece)); foreach ($result as $row) { $bannerRow = new Row(array(Row::COLUMNS => array('Referrer' => $row['referrer'], 'Target' => $row['target'], 'Impressions' => $row['impressions'], 'Interactions' => $row['interactions'], 'Conversion rate' => round($row['conversion_rate']) . '%'))); $bannerTable->addRow($bannerRow); } } return $bannerTable; }
/** * Iterates over logs in a log table in chunks. Parameters to this function are as backend agnostic * as possible w/o dramatically increasing code complexity. * * @param string $logTable The log table name. Unprefixed, eg, `log_visit`. * @param array[] $conditions An array describing the conditions logs must match in the query. Translates to * the WHERE part of a SELECT statement. Each element must contain three elements: * * * the column name * * the operator (ie, '=', '<>', '<', etc.) * * the operand (ie, a value) * * The elements are AND-ed together. * * Example: * * ``` * array( * array('visit_first_action_time', '>=', ...), * array('visit_first_action_time', '<', ...) * ) * ``` * @param int $iterationStep The number of rows to query at a time. * @param callable $callback The callback that processes each chunk of rows. */ public function forAllLogs($logTable, $fields, $conditions, $iterationStep, $callback) { $idField = $this->getIdFieldForLogTable($logTable); list($query, $bind) = $this->createLogIterationQuery($logTable, $idField, $fields, $conditions, $iterationStep); $lastId = 0; do { $rows = Db::fetchAll($query, array_merge(array($lastId), $bind)); if (!empty($rows)) { $lastId = $rows[count($rows) - 1][$idField]; $callback($rows); } } while (count($rows) == $iterationStep); }
/** * Returns an array associating table names w/ lists of row data. * * @return array */ protected static function getDbTablesWithData() { $result = array(); foreach (DbHelper::getTablesInstalled() as $tableName) { $result[$tableName] = Db::fetchAll("SELECT * FROM `{$tableName}`"); } return $result; }
/** * Queries and returns archive data using a set of archive IDs. * * @param array $archiveIds The IDs of the archives to get data from. * @param array $recordNames The names of the data to retrieve (ie, nb_visits, nb_actions, etc.) * @param string $archiveDataType The archive data type (either, 'blob' or 'numeric'). * @param bool $loadAllSubtables Whether to pre-load all subtables * @throws Exception * @return array */ public static function getArchiveData($archiveIds, $recordNames, $archiveDataType, $loadAllSubtables) { // create the SQL to select archive data $inNames = Common::getSqlStringFieldsArray($recordNames); if ($loadAllSubtables) { $name = reset($recordNames); // select blobs w/ name like "$name_[0-9]+" w/o using RLIKE $nameEnd = strlen($name) + 2; $whereNameIs = "(name = ?\n OR (name LIKE ?\n AND SUBSTRING(name, {$nameEnd}, 1) >= '0'\n AND SUBSTRING(name, {$nameEnd}, 1) <= '9') )"; $bind = array($name, $name . '%'); } else { $whereNameIs = "name IN ({$inNames})"; $bind = array_values($recordNames); } $getValuesSql = "SELECT value, name, idsite, date1, date2, ts_archived\n FROM %s\n WHERE idarchive IN (%s)\n AND " . $whereNameIs; // get data from every table we're querying $rows = array(); foreach ($archiveIds as $period => $ids) { if (empty($ids)) { throw new Exception("Unexpected: id archive not found for period '{$period}' '"); } // $period = "2009-01-04,2009-01-04", $date = Date::factory(substr($period, 0, 10)); if ($archiveDataType == 'numeric') { $table = ArchiveTableCreator::getNumericTable($date); } else { $table = ArchiveTableCreator::getBlobTable($date); } $sql = sprintf($getValuesSql, $table, implode(',', $ids)); $dataRows = Db::fetchAll($sql, $bind); foreach ($dataRows as $row) { $rows[] = $row; } } return $rows; }
private function getAllRowsFromArchiveBlobTable() { $table = ArchiveTableCreator::getBlobTable(Date::factory($this->date)); $rows = Db::fetchAll("SELECT * FROM " . $table); return $rows; }
/** * Returns the list of unique timezones from all configured sites. * * @return array ( string ) */ public function getUniqueSiteTimezones() { Piwik::checkUserHasSuperUserAccess(); $results = Db::fetchAll("SELECT distinct timezone FROM " . Common::prefixTable('site')); $timezones = array(); foreach ($results as $result) { $timezones[] = $result['timezone']; } return $timezones; }
public function getListConversations() { $rows = Db::fetchAll("SELECT\n\t\t\tidvisitor, \n\t\t\tMAX(idmessage) AS maxid, \n\t\t\t(SELECT t2.content FROM " . Common::prefixTable('chat') . " AS t2 WHERE t2.idvisitor = t1.idvisitor ORDER BY t2.idmessage DESC LIMIT 1) AS content,\n\t\t\t(SELECT t2.microtime FROM " . Common::prefixTable('chat') . " AS t2 WHERE t2.idvisitor = t1.idvisitor ORDER BY t2.idmessage DESC LIMIT 1) AS microtime,\n\t\t\t(SELECT t3.name FROM " . Common::prefixTable('chat_personnal_informations') . " AS t3 WHERE t3.idvisitor = t1.idvisitor) AS name,\n\t\t\t(SELECT t3.email FROM " . Common::prefixTable('chat_personnal_informations') . " AS t3 WHERE t3.idvisitor = t1.idvisitor) AS email\n\t\tFROM " . Common::prefixTable('chat') . " AS t1\n\t\tWHERE idsite = ?\n\t\tGROUP BY idvisitor\n\t\tORDER BY microtime DESC", array($this->idsite)); $rows = ChatCommon::formatRows($rows); return $rows; }
/** * Must be called before dispatch() * - checks that directories are writable, * - loads the configuration file, * - loads the plugin, * - inits the DB connection, * - etc. * * @throws Exception * @return void */ public function init() { static $initialized = false; if ($initialized) { return; } $initialized = true; $tmpPath = StaticContainer::get('path.tmp'); $directoriesToCheck = array($tmpPath, $tmpPath . '/assets/', $tmpPath . '/cache/', $tmpPath . '/logs/', $tmpPath . '/tcpdf/', $tmpPath . '/templates_c/'); Filechecks::dieIfDirectoriesNotWritable($directoriesToCheck); $this->handleMaintenanceMode(); $this->handleProfiler(); $this->handleSSLRedirection(); Plugin\Manager::getInstance()->loadPluginTranslations(); Plugin\Manager::getInstance()->loadActivatedPlugins(); // try to connect to the database try { Db::createDatabaseObject(); Db::fetchAll("SELECT DATABASE()"); } catch (Exception $exception) { if (self::shouldRethrowException()) { throw $exception; } Log::debug($exception); /** * Triggered when Piwik cannot connect to the database. * * This event can be used to start the installation process or to display a custom error * message. * * @param Exception $exception The exception thrown from creating and testing the database * connection. */ Piwik::postEvent('Db.cannotConnectToDb', array($exception), $pending = true); throw $exception; } // try to get an option (to check if data can be queried) try { Option::get('TestingIfDatabaseConnectionWorked'); } catch (Exception $exception) { if (self::shouldRethrowException()) { throw $exception; } Log::debug($exception); /** * Triggered when Piwik cannot access database data. * * This event can be used to start the installation process or to display a custom error * message. * * @param Exception $exception The exception thrown from trying to get an option value. */ Piwik::postEvent('Config.badConfigurationFile', array($exception), $pending = true); throw $exception; } // Init the Access object, so that eg. core/Updates/* can enforce Super User and use some APIs Access::getInstance(); /** * Triggered just after the platform is initialized and plugins are loaded. * * This event can be used to do early initialization. * * _Note: At this point the user is not authenticated yet._ */ Piwik::postEvent('Request.dispatchCoreAndPluginUpdatesScreen'); $this->throwIfPiwikVersionIsOlderThanDBSchema(); \Piwik\Plugin\Manager::getInstance()->installLoadedPlugins(); // ensure the current Piwik URL is known for later use if (method_exists('Piwik\\SettingsPiwik', 'getPiwikUrl')) { SettingsPiwik::getPiwikUrl(); } /** * Triggered before the user is authenticated, when the global authentication object * should be created. * * Plugins that provide their own authentication implementation should use this event * to set the global authentication object (which must derive from {@link Piwik\Auth}). * * **Example** * * Piwik::addAction('Request.initAuthenticationObject', function() { * StaticContainer::getContainer()->set('Piwik\Auth', new MyAuthImplementation()); * }); */ Piwik::postEvent('Request.initAuthenticationObject'); try { $authAdapter = StaticContainer::get('Piwik\\Auth'); } catch (Exception $e) { $message = "Authentication object cannot be found in the container. Maybe the Login plugin is not activated?\n <br />You can activate the plugin by adding:<br />\n <code>Plugins[] = Login</code><br />\n under the <code>[Plugins]</code> section in your config/config.ini.php"; $ex = new AuthenticationFailedException($message); $ex->setIsHtmlMessage(); throw $ex; } Access::getInstance()->reloadAccess($authAdapter); // Force the auth to use the token_auth if specified, so that embed dashboard // and all other non widgetized controller methods works fine if (Common::getRequestVar('token_auth', false, 'string') !== false) { Request::reloadAuthUsingTokenAuth(); } SettingsServer::raiseMemoryLimitIfNecessary(); \Piwik\Plugin\Manager::getInstance()->postLoadPlugins(); /** * Triggered after the platform is initialized and after the user has been authenticated, but * before the platform has handled the request. * * Piwik uses this event to check for updates to Piwik. */ Piwik::postEvent('Platform.initialized'); }
/** * Returns the site IDs for invalidated archives in an archive table. * * @param string $numericTable The numeric table to search through. * @return int[] */ public function getSitesWithInvalidatedArchive($numericTable) { $rows = Db::fetchAll("SELECT DISTINCT idsite FROM `{$numericTable}` WHERE name LIKE 'done%' AND value = " . ArchiveWriter::DONE_INVALIDATED); $result = array(); foreach ($rows as $row) { $result[] = $row['idsite']; } return $result; }
/** * @param $visitorDetailsArray * @param $actionsLimit * @param $timezone * @return array */ public static function enrichVisitorArrayWithActions($visitorDetailsArray, $actionsLimit, $timezone) { $idVisit = $visitorDetailsArray['idVisit']; $maxCustomVariables = CustomVariables::getMaxCustomVariables(); $sqlCustomVariables = ''; for ($i = 1; $i <= $maxCustomVariables; $i++) { $sqlCustomVariables .= ', custom_var_k' . $i . ', custom_var_v' . $i; } // The second join is a LEFT join to allow returning records that don't have a matching page title // eg. Downloads, Outlinks. For these, idaction_name is set to 0 $sql = "\n\t\t\t\tSELECT\n\t\t\t\t\tCOALESCE(log_action_event_category.type, log_action.type, log_action_title.type) AS type,\n\t\t\t\t\tlog_action.name AS url,\n\t\t\t\t\tlog_action.url_prefix,\n\t\t\t\t\tlog_action_title.name AS pageTitle,\n\t\t\t\t\tlog_action.idaction AS pageIdAction,\n\t\t\t\t\tlog_link_visit_action.server_time as serverTimePretty,\n\t\t\t\t\tlog_link_visit_action.time_spent_ref_action as timeSpentRef,\n\t\t\t\t\tlog_link_visit_action.idlink_va AS pageId,\n\t\t\t\t\tlog_link_visit_action.custom_float\n\t\t\t\t\t" . $sqlCustomVariables . ",\n\t\t\t\t\tlog_action_event_category.name AS eventCategory,\n\t\t\t\t\tlog_action_event_action.name as eventAction\n\t\t\t\tFROM " . Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action\n\t\t\t\t\tON log_link_visit_action.idaction_url = log_action.idaction\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_title\n\t\t\t\t\tON log_link_visit_action.idaction_name = log_action_title.idaction\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_category\n\t\t\t\t\tON log_link_visit_action.idaction_event_category = log_action_event_category.idaction\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_action\n\t\t\t\t\tON log_link_visit_action.idaction_event_action = log_action_event_action.idaction\n\t\t\t\tWHERE log_link_visit_action.idvisit = ?\n\t\t\t\tORDER BY server_time ASC\n\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t\t "; $actionDetails = Db::fetchAll($sql, array($idVisit)); foreach ($actionDetails as $actionIdx => &$actionDetail) { $actionDetail =& $actionDetails[$actionIdx]; $customVariablesPage = array(); for ($i = 1; $i <= $maxCustomVariables; $i++) { if (!empty($actionDetail['custom_var_k' . $i])) { $cvarKey = $actionDetail['custom_var_k' . $i]; $cvarKey = static::getCustomVariablePrettyKey($cvarKey); $customVariablesPage[$i] = array('customVariablePageName' . $i => $cvarKey, 'customVariablePageValue' . $i => $actionDetail['custom_var_v' . $i]); } unset($actionDetail['custom_var_k' . $i]); unset($actionDetail['custom_var_v' . $i]); } if (!empty($customVariablesPage)) { $actionDetail['customVariables'] = $customVariablesPage; } if ($actionDetail['type'] == Action::TYPE_CONTENT) { unset($actionDetails[$actionIdx]); continue; } elseif ($actionDetail['type'] == Action::TYPE_EVENT_CATEGORY) { // Handle Event if (strlen($actionDetail['pageTitle']) > 0) { $actionDetail['eventName'] = $actionDetail['pageTitle']; } unset($actionDetail['pageTitle']); } else { if ($actionDetail['type'] == Action::TYPE_SITE_SEARCH) { // Handle Site Search $actionDetail['siteSearchKeyword'] = $actionDetail['pageTitle']; unset($actionDetail['pageTitle']); } } // Event value / Generation time if ($actionDetail['type'] == Action::TYPE_EVENT_CATEGORY) { if (strlen($actionDetail['custom_float']) > 0) { $actionDetail['eventValue'] = round($actionDetail['custom_float'], self::EVENT_VALUE_PRECISION); } } elseif ($actionDetail['custom_float'] > 0) { $actionDetail['generationTime'] = \Piwik\MetricsFormatter::getPrettyTimeFromSeconds($actionDetail['custom_float'] / 1000); } unset($actionDetail['custom_float']); if ($actionDetail['type'] != Action::TYPE_EVENT_CATEGORY) { unset($actionDetail['eventCategory']); unset($actionDetail['eventAction']); } // Reconstruct url from prefix $actionDetail['url'] = Tracker\PageUrl::reconstructNormalizedUrl($actionDetail['url'], $actionDetail['url_prefix']); unset($actionDetail['url_prefix']); // Set the time spent for this action (which is the timeSpentRef of the next action) if (isset($actionDetails[$actionIdx + 1])) { $actionDetail['timeSpent'] = $actionDetails[$actionIdx + 1]['timeSpentRef']; $actionDetail['timeSpentPretty'] = \Piwik\MetricsFormatter::getPrettyTimeFromSeconds($actionDetail['timeSpent']); } unset($actionDetails[$actionIdx]['timeSpentRef']); // not needed after timeSpent is added } // If the visitor converted a goal, we shall select all Goals $sql = "\n\t\t\t\tSELECT\n\t\t\t\t\t\t'goal' as type,\n\t\t\t\t\t\tgoal.name as goalName,\n\t\t\t\t\t\tgoal.idgoal as goalId,\n\t\t\t\t\t\tgoal.revenue as revenue,\n\t\t\t\t\t\tlog_conversion.idlink_va as goalPageId,\n\t\t\t\t\t\tlog_conversion.server_time as serverTimePretty,\n\t\t\t\t\t\tlog_conversion.url as url\n\t\t\t\tFROM " . Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\tLEFT JOIN " . Common::prefixTable('goal') . " AS goal\n\t\t\t\t\tON (goal.idsite = log_conversion.idsite\n\t\t\t\t\t\tAND\n\t\t\t\t\t\tgoal.idgoal = log_conversion.idgoal)\n\t\t\t\t\tAND goal.deleted = 0\n\t\t\t\tWHERE log_conversion.idvisit = ?\n\t\t\t\t\tAND log_conversion.idgoal > 0\n ORDER BY server_time ASC\n\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t"; $goalDetails = Db::fetchAll($sql, array($idVisit)); $sql = "SELECT\n\t\t\t\t\t\tcase idgoal when " . GoalManager::IDGOAL_CART . " then '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART . "' else '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER . "' end as type,\n\t\t\t\t\t\tidorder as orderId,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue') . " as revenue,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_subtotal') . " as revenueSubTotal,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_tax') . " as revenueTax,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_shipping') . " as revenueShipping,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_discount') . " as revenueDiscount,\n\t\t\t\t\t\titems as items,\n\n\t\t\t\t\t\tlog_conversion.server_time as serverTimePretty\n\t\t\t\t\tFROM " . Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\t\tWHERE idvisit = ?\n\t\t\t\t\t\tAND idgoal <= " . GoalManager::IDGOAL_ORDER . "\n\t\t\t\t\tORDER BY server_time ASC\n\t\t\t\t\tLIMIT 0, {$actionsLimit}"; $ecommerceDetails = Db::fetchAll($sql, array($idVisit)); foreach ($ecommerceDetails as &$ecommerceDetail) { if ($ecommerceDetail['type'] == Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART) { unset($ecommerceDetail['orderId']); unset($ecommerceDetail['revenueSubTotal']); unset($ecommerceDetail['revenueTax']); unset($ecommerceDetail['revenueShipping']); unset($ecommerceDetail['revenueDiscount']); } // 25.00 => 25 foreach ($ecommerceDetail as $column => $value) { if (strpos($column, 'revenue') !== false) { if ($value == round($value)) { $ecommerceDetail[$column] = round($value); } } } } // Enrich ecommerce carts/orders with the list of products usort($ecommerceDetails, array('static', 'sortByServerTime')); foreach ($ecommerceDetails as &$ecommerceConversion) { $sql = "SELECT\n\t\t\t\t\t\t\tlog_action_sku.name as itemSKU,\n\t\t\t\t\t\t\tlog_action_name.name as itemName,\n\t\t\t\t\t\t\tlog_action_category.name as itemCategory,\n\t\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('price') . " as price,\n\t\t\t\t\t\t\tquantity as quantity\n\t\t\t\t\t\tFROM " . Common::prefixTable('log_conversion_item') . "\n\t\t\t\t\t\t\tINNER JOIN " . Common::prefixTable('log_action') . " AS log_action_sku\n\t\t\t\t\t\t\tON idaction_sku = log_action_sku.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_name\n\t\t\t\t\t\t\tON idaction_name = log_action_name.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_category\n\t\t\t\t\t\t\tON idaction_category = log_action_category.idaction\n\t\t\t\t\t\tWHERE idvisit = ?\n\t\t\t\t\t\t\tAND idorder = ?\n\t\t\t\t\t\t\tAND deleted = 0\n\t\t\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t\t"; $bind = array($idVisit, isset($ecommerceConversion['orderId']) ? $ecommerceConversion['orderId'] : GoalManager::ITEM_IDORDER_ABANDONED_CART); $itemsDetails = Db::fetchAll($sql, $bind); foreach ($itemsDetails as &$detail) { if ($detail['price'] == round($detail['price'])) { $detail['price'] = round($detail['price']); } } $ecommerceConversion['itemDetails'] = $itemsDetails; } $actions = array_merge($actionDetails, $goalDetails, $ecommerceDetails); usort($actions, array('static', 'sortByServerTime')); $visitorDetailsArray['actionDetails'] = $actions; foreach ($visitorDetailsArray['actionDetails'] as &$details) { switch ($details['type']) { case 'goal': $details['icon'] = 'plugins/Morpheus/images/goal.png'; break; case Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER: case Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART: $details['icon'] = 'plugins/Morpheus/images/' . $details['type'] . '.gif'; break; case Action::TYPE_DOWNLOAD: $details['type'] = 'download'; $details['icon'] = 'plugins/Morpheus/images/download.png'; break; case Action::TYPE_OUTLINK: $details['type'] = 'outlink'; $details['icon'] = 'plugins/Morpheus/images/link.gif'; break; case Action::TYPE_SITE_SEARCH: $details['type'] = 'search'; $details['icon'] = 'plugins/Morpheus/images/search_ico.png'; break; case Action::TYPE_EVENT_CATEGORY: $details['type'] = 'event'; $details['icon'] = 'plugins/Morpheus/images/event.png'; break; default: $details['type'] = 'action'; $details['icon'] = null; break; } // Convert datetimes to the site timezone $dateTimeVisit = Date::factory($details['serverTimePretty'], $timezone); $details['serverTimePretty'] = $dateTimeVisit->getLocalized(Piwik::translate('CoreHome_ShortDateFormat') . ' %time%'); } $visitorDetailsArray['goalConversions'] = count($goalDetails); return $visitorDetailsArray; }
protected function _checkTableIsExpectedBlob($table, $data) { $fetched = Db::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"); } }
/** * Returns the list of unique timezones from all configured sites. * * @return array ( string ) */ public function getUniqueSiteTimezones() { $results = Db::fetchAll("SELECT distinct timezone FROM " . $this->table); $timezones = array(); foreach ($results as $result) { $timezones[] = $result['timezone']; } return $timezones; }