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