You can use **Archive** instances to get data that was archived for one or more sites,
for one or more periods and one optional segment.
If archive data is not found, this class will initiate the archiving process. 1
**Archive** instances must be created using the {@link build()} factory method;
they cannot be constructed.
You can search for metrics (such as nb_visits) using the {@link getNumeric()} and
{@link getDataTableFromNumeric()} methods. You can search for
reports using the {@link getBlob()}, {@link getDataTable()} and {@link getDataTableExpanded()} methods.
If you're creating an API that returns report data, you may want to use the
{@link createDataTableFromArchive()} helper function.
### Learn more
Learn more about _archiving_ here.
### Limitations
- You cannot get data for multiple range periods in a single query.
- You cannot get data for periods of different types in a single query.
### Examples
**_Querying metrics for an API method_**
one site and one period
$archive = Archive::build($idSite = 1, $period = 'week', $date = '2013-03-08');
return $archive->getDataTableFromNumeric(array('nb_visits', 'nb_actions'));
all sites and multiple dates
$archive = Archive::build($idSite = 'all', $period = 'month', $date = '2013-01-02,2013-03-08');
return $archive->getDataTableFromNumeric(array('nb_visits', 'nb_actions'));
**_Querying and using metrics immediately_**
one site and one period
$archive = Archive::build($idSite = 1, $period = 'week', $date = '2013-03-08');
$data = $archive->getNumeric(array('nb_visits', 'nb_actions'));
$visits = $data['nb_visits'];
$actions = $data['nb_actions'];
... do something w/ metric data ...
multiple sites and multiple dates
$archive = Archive::build($idSite = '1,2,3', $period = 'month', $date = '2013-01-02,2013-03-08');
$data = $archive->getNumeric('nb_visits');
$janSite1Visits = $data['1']['2013-01-01,2013-01-31']['nb_visits'];
$febSite1Visits = $data['1']['2013-02-01,2013-02-28']['nb_visits'];
... etc.
**_Querying for reports_**
$archive = Archive::build($idSite = 1, $period = 'week', $date = '2013-03-08');
$dataTable = $archive->getDataTable('MyPlugin_MyReport');
... manipulate $dataTable ...
return $dataTable;
**_Querying a report for an API method_**
public function getMyReport($idSite, $period, $date, $segment = false, $expanded = false)
{
$dataTable = Archive::createDataTableFromArchive('MyPlugin_MyReport', $idSite, $period, $date, $segment, $expanded);
return $dataTable;
}
**_Querying data for multiple range periods_**
get data for first range
$archive = Archive::build($idSite = 1, $period = 'range', $date = '2013-03-08,2013-03-12');
$dataTable = $archive->getDataTableFromNumeric(array('nb_visits', 'nb_actions'));
get data for second range
$archive = Archive::build($idSite = 1, $period = 'range', $date = '2013-03-15,2013-03-20');
$dataTable = $archive->getDataTableFromNumeric(array('nb_visits', 'nb_actions'));
[1]: The archiving process will not be launched if browser archiving is disabled
and the current request came from a browser.
protected function getNumeric($idSite, $period, $date, $segment, $toFetch)
{
Piwik::checkUserHasViewAccess($idSite);
$archive = Archive::build($idSite, $period, $date, $segment);
$dataTable = $archive->getDataTableFromNumeric($toFetch);
return $dataTable;
}
private function getDataTable($name, $idSite, $period, $date, $segment, $expanded, $idSubtable)
{
Piwik::checkUserHasViewAccess($idSite);
$recordName = Dimensions::getRecordNameForAction($name);
$dataTable = Archive::getDataTableFromArchive($recordName, $idSite, $period, $date, $segment, $expanded, $idSubtable);
$this->filterDataTable($dataTable);
return $dataTable;
}
protected function getDataTable($name, $idSite, $period, $date, $segment, $column = Metrics::INDEX_NB_VISITS)
{
Piwik::checkUserHasViewAccess($idSite);
$archive = Archive::build($idSite, $period, $date, $segment);
$dataTable = $archive->getDataTable($name);
$dataTable->queueFilter('ReplaceColumnNames');
return $dataTable;
}
/**
* @param int $idSite
* @param string $period
* @param Date $date
* @param string $segment
* @param bool $expanded
* @param int $idSubtable
*
* @return DataTable|DataTable\Map
*/
protected function getDataTable($idSite, $period, $date, $segment, $expanded, $idSubtable)
{
$dataTable = Archive::getDataTableFromArchive(Archiver::CUSTOM_VARIABLE_RECORD_NAME, $idSite, $period, $date, $segment, $expanded, $idSubtable);
$dataTable->filter('Sort', array(Metrics::INDEX_NB_ACTIONS, 'desc', $naturalSort = false, $expanded));
$dataTable->queueFilter('ReplaceColumnNames');
$dataTable->queueFilter('ColumnDelete', 'nb_uniq_visitors');
return $dataTable;
}
/**
* Test that restricting the number of sites to those viewable to another login
* works when building an archive query object.
*
* @group Integration
*/
public function testArchiveSitesWhenRestrictingToLogin()
{
try {
Archive::build('all', 'day', self::$fixture->dateTime, $segment = false, $_restrictToLogin = '******');
$this->fail("Restricting sites to invalid login did not return 0 sites.");
} catch (Exception $ex) {
// pass
}
}
/**
* @param string $name
* @param int $idSite
* @param string $period
* @param string $date
* @param string $segment
* @return DataTable
*/
protected function getDataTable($name, $idSite, $period, $date, $segment)
{
Piwik::checkUserHasViewAccess($idSite);
$archive = Archive::build($idSite, $period, $date, $segment);
$dataTable = $archive->getDataTable($name);
$dataTable->queueFilter('ReplaceColumnNames');
$dataTable->queueFilter('ReplaceSummaryRowLabel');
return $dataTable;
}
/**
* Get a report of all User Ids.
*
* @param int $idSite
*
* @param string $period
* @param int $date
* @param string|bool $segment
*
* @return DataTable
*/
public function getUsers($idSite, $period, $date, $segment = false)
{
Piwik::checkUserHasViewAccess($idSite);
$archive = Archive::build($idSite, $period, $date, $segment);
$dataTable = $archive->getDataTable(Archiver::USERID_ARCHIVE_RECORD);
$dataTable->queueFilter('ReplaceColumnNames');
$dataTable->queueFilter('ReplaceSummaryRowLabel');
return $dataTable;
}
/**
* API method that returns number of visits based on hour parity.
* @param int $idSite
* @param string $period
* @param string $date
* @param bool|string $segment
* @return DataTable
*/
public function getEvenUnevenTimes($idSite, $period, $date, $segment = false)
{
$archive = Archive::build($idSite, $period, $date);
$oddHoursCount = $archive->getNumeric(Archiver::ODD_HOURS_COUNT_RECORD_NAME);
$evenHoursCount = $archive->getNumeric(Archiver::EVEN_HOURS_COUNT_RECORD_NAME);
$table = new DataTable();
$table->setTableSortedBy('times');
$table->addRowFromArray(array(Row::COLUMNS => array('times' => Piwik::translate('HourParity_Oddhours'), 'nb_visits' => $oddHoursCount)));
$table->addRowFromArray(array(Row::COLUMNS => array('times' => Piwik::translate('HourParity_Evenhours'), 'nb_visits' => $evenHoursCount)));
return $table;
}
public function getProvider($idSite, $period, $date, $segment = false)
{
Piwik::checkUserHasViewAccess($idSite);
$archive = Archive::build($idSite, $period, $date, $segment);
$dataTable = $archive->getDataTable(Archiver::PROVIDER_RECORD_NAME);
$dataTable->filter('ColumnCallbackAddMetadata', array('label', 'url', __NAMESPACE__ . '\\getHostnameUrl'));
$dataTable->filter('GroupBy', array('label', __NAMESPACE__ . '\\getPrettyProviderName'));
$dataTable->queueFilter('ReplaceColumnNames');
$dataTable->queueFilter('ReplaceSummaryRowLabel');
return $dataTable;
}
private function getDataTable($name, $idSite, $period, $date, $segment, $expanded, $idSubtable)
{
Piwik::checkUserHasViewAccess($idSite);
$recordName = Dimensions::getRecordNameForAction($name);
$dataTable = Archive::getDataTableFromArchive($recordName, $idSite, $period, $date, $segment, $expanded, $idSubtable);
if (empty($idSubtable)) {
$dataTable->filter('AddSegmentValue', array(function ($label) {
if ($label === Archiver::CONTENT_PIECE_NOT_SET) {
return false;
}
return $label;
}));
}
$this->filterDataTable($dataTable);
return $dataTable;
}
public function getPlugin($idSite, $period, $date, $segment = false)
{
// fetch all archive data required
$dataTable = $this->getDataTable(Archiver::PLUGIN_RECORD_NAME, $idSite, $period, $date, $segment);
$browserVersions = $this->getDataTable(DDArchiver::BROWSER_VERSION_RECORD_NAME, $idSite, $period, $date, $segment);
$archive = Archive::build($idSite, $period, $date, $segment);
$visitsSums = $archive->getDataTableFromNumeric('nb_visits');
// check whether given tables are arrays
if ($dataTable instanceof DataTable\Map) {
$dataTableMap = $dataTable->getDataTables();
$browserVersionsArray = $browserVersions->getDataTables();
$visitSumsArray = $visitsSums->getDataTables();
} else {
$dataTableMap = array($dataTable);
$browserVersionsArray = array($browserVersions);
$visitSumsArray = array($visitsSums);
}
// walk through the results and calculate the percentage
foreach ($dataTableMap as $key => $table) {
// Calculate percentage, but ignore IE users because plugin detection doesn't work on IE
$ieVisits = 0;
$browserVersionsToExclude = array('IE;10.0', 'IE;9.0', 'IE;8.0', 'IE;7.0', 'IE;6.0');
foreach ($browserVersionsToExclude as $browserVersionToExclude) {
$ieStats = $browserVersionsArray[$key]->getRowFromLabel($browserVersionToExclude);
if ($ieStats !== false) {
$ieVisits += $ieStats->getColumn(Metrics::INDEX_NB_VISITS);
}
}
// get according visitsSum
$visits = $visitSumsArray[$key];
if ($visits->getRowsCount() == 0) {
$visitsSumTotal = 0;
} else {
$visitsSumTotal = (double) $visits->getFirstRow()->getColumn('nb_visits');
}
$visitsSum = $visitsSumTotal - $ieVisits;
$extraProcessedMetrics = $table->getMetadata(DataTable::EXTRA_PROCESSED_METRICS_METADATA_NAME);
$extraProcessedMetrics[] = new VisitsPercent($visitsSum);
$table->setMetadata(DataTable::EXTRA_PROCESSED_METRICS_METADATA_NAME, $extraProcessedMetrics);
}
$dataTable->queueFilter('ColumnCallbackAddMetadata', array('label', 'logo', __NAMESPACE__ . '\\getPluginsLogo'));
$dataTable->queueFilter('ColumnCallbackReplace', array('label', 'ucfirst'));
$dataTable->queueFilter('RangeCheck', array('nb_visits_percentage', 0, 1));
return $dataTable;
}
/**
* Returns datatable describing the number of visits for each day of the week.
*
* @param string $idSite The site ID. Cannot refer to multiple sites.
* @param string $period The period type: day, week, year, range...
* @param string $date The start date of the period. Cannot refer to multiple dates.
* @param bool|string $segment The segment.
* @throws Exception
* @return DataTable
*/
public function getByDayOfWeek($idSite, $period, $date, $segment = false)
{
Piwik::checkUserHasViewAccess($idSite);
// metrics to query
$metrics = Metrics::getVisitsMetricNames();
unset($metrics[Metrics::INDEX_MAX_ACTIONS]);
// disabled for multiple dates
if (Period::isMultiplePeriod($date, $period)) {
throw new Exception("VisitTime.getByDayOfWeek does not support multiple dates.");
}
// get metric data for every day within the supplied period
$oPeriod = Period\Factory::makePeriodFromQueryParams(Site::getTimezoneFor($idSite), $period, $date);
$dateRange = $oPeriod->getDateStart()->toString() . ',' . $oPeriod->getDateEnd()->toString();
$archive = Archive::build($idSite, 'day', $dateRange, $segment);
// disabled for multiple sites
if (count($archive->getParams()->getIdSites()) > 1) {
throw new Exception("VisitTime.getByDayOfWeek does not support multiple sites.");
}
$dataTable = $archive->getDataTableFromNumeric($metrics)->mergeChildren();
// if there's no data for this report, don't bother w/ anything else
if ($dataTable->getRowsCount() == 0) {
return $dataTable;
}
// group by the day of the week (see below for dayOfWeekFromDate function)
$dataTable->filter('GroupBy', array('label', __NAMESPACE__ . '\\dayOfWeekFromDate'));
// create new datatable w/ empty rows, then add calculated datatable
$rows = array();
foreach (array(1, 2, 3, 4, 5, 6, 7) as $day) {
$rows[] = array('label' => $day, 'nb_visits' => 0);
}
$result = new DataTable();
$result->addRowsFromSimpleArray($rows);
$result->addDataTable($dataTable);
// set day of week integer as metadata
$result->filter('ColumnCallbackAddMetadata', array('label', 'day_of_week'));
// translate labels
$result->filter('ColumnCallbackReplace', array('label', __NAMESPACE__ . '\\translateDayOfWeek'));
// set datatable metadata for period start & finish
$result->setMetadata('date_start', $oPeriod->getDateStart());
$result->setMetadata('date_end', $oPeriod->getDateEnd());
return $result;
}
public function test_subtables_willBeSplitIntoChunks()
{
$numSubtablesToGenerate = 1053;
$blobs = $this->generateDataTableWithManySubtables($numSubtablesToGenerate);
$this->assertCount($numSubtablesToGenerate + 1, $blobs);
// +1 for the root table
$recordName = 'Actions_MyRecord';
$archiver = $this->createPluginsArchiver();
$archiver->archiveProcessor->insertBlobRecord($recordName, $blobs);
$archiver->finalizeArchive();
// verify they were split into chunks
$archiveRows = $this->getAllRowsFromArchiveBlobTable('name');
$expectedArchiveNames = array($recordName, $recordName . '_chunk_0_99', $recordName . '_chunk_1000_1099', $recordName . '_chunk_100_199', $recordName . '_chunk_200_299', $recordName . '_chunk_300_399', $recordName . '_chunk_400_499', $recordName . '_chunk_500_599', $recordName . '_chunk_600_699', $recordName . '_chunk_700_799', $recordName . '_chunk_800_899', $recordName . '_chunk_900_999');
$this->assertEquals($expectedArchiveNames, array_column($archiveRows, 'name'));
// verify all have same archiveIds
$expectedArchiveIds = array_fill(0, count($expectedArchiveNames), $archiveId = '1');
$this->assertEquals($expectedArchiveIds, array_column($archiveRows, 'idarchive'));
// verify the subtables were actually splitted into chunks
foreach ($archiveRows as $row) {
$value = unserialize(gzuncompress($row['value']));
$this->assertTrue(is_array($value));
if ($row['name'] == $recordName) {
$this->assertCount($numSubtablesToGenerate, $value);
// 1053 rows
} elseif ($row['name'] == $recordName . '_chunk_1000_1099') {
$this->assertCount($numSubtablesToGenerate % Archive\Chunk::NUM_TABLES_IN_CHUNK + 1, $value);
// 53 subtables
} elseif ($row['name'] == $recordName . '_chunk_0_99') {
$this->assertCount(Archive\Chunk::NUM_TABLES_IN_CHUNK - 1, $value);
// one less as we do not store the root table here
} else {
$this->assertCount(Archive\Chunk::NUM_TABLES_IN_CHUNK, $value);
}
}
// should be able to rebuild the datatable
$archive = Archive::build(1, 'day', $this->date);
$table = $archive->getDataTable($recordName);
$this->assertSame(1053, $table->getRowsCount());
$this->assertSame('Label Test 1', $table->getFirstRow()->getColumn('label'));
$this->assertSame(1, $table->getFirstRow()->getColumn('nb_visits'));
}
/**
* Fetch a report for the given idDimension. Only reports for active dimensions can be fetched. Requires at least
* view access.
*
* @param int $idDimension
* @param int $idSite
* @param string $period
* @param string $date
* @param bool|false $segment
* @param bool|false $expanded
* @param bool|false $flat
* @param int|null $idSubtable
* @return DataTable|DataTable\Map
* @throws \Exception
*/
public function getCustomDimension($idDimension, $idSite, $period, $date, $segment = false, $expanded = false, $flat = false, $idSubtable = null)
{
Piwik::checkUserHasViewAccess($idSite);
$dimension = new Dimension($idDimension, $idSite);
$dimension->checkActive();
$record = Archiver::buildRecordNameForCustomDimensionId($idDimension);
$dataTable = Archive::createDataTableFromArchive($record, $idSite, $period, $date, $segment, $expanded, $flat, $idSubtable);
if (isset($idSubtable) && $dataTable->getRowsCount()) {
$parentTable = Archive::createDataTableFromArchive($record, $idSite, $period, $date, $segment);
foreach ($parentTable->getRows() as $row) {
if ($row->getIdSubDataTable() == $idSubtable) {
$parentValue = $row->getColumn('label');
$dataTable->queueFilter('Piwik\\Plugins\\CustomDimensions\\DataTable\\Filter\\AddSubtableSegmentMetadata', array($idDimension, $parentValue));
break;
}
}
} else {
$dataTable->queueFilter('Piwik\\Plugins\\CustomDimensions\\DataTable\\Filter\\AddSegmentMetadata', array($idDimension));
}
$dataTable->filter('Piwik\\Plugins\\CustomDimensions\\DataTable\\Filter\\RemoveUserIfNeeded', array($idSite, $period, $date));
return $dataTable;
}
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;
}
protected function getDataTable($name, $idSite, $period, $date, $segment, $expanded = false, $idSubtable = null, $secondaryDimension = false, $flat = false)
{
Piwik::checkUserHasViewAccess($idSite);
$this->checkSecondaryDimension($name, $secondaryDimension);
$recordName = $this->getRecordNameForAction($name, $secondaryDimension);
$dataTable = Archive::createDataTableFromArchive($recordName, $idSite, $period, $date, $segment, $expanded, $flat, $idSubtable);
if ($flat) {
$dataTable->filterSubtables('Piwik\\Plugins\\Events\\DataTable\\Filter\\ReplaceEventNameNotSet');
} else {
$dataTable->filter('AddSegmentValue', array(function ($label) {
if ($label === Archiver::EVENT_NAME_NOT_SET) {
return false;
}
return $label;
}));
}
$dataTable->filter('Piwik\\Plugins\\Events\\DataTable\\Filter\\ReplaceEventNameNotSet');
return $dataTable;
}
public function getNumberOfDistinctCountries($idSite, $period, $date, $segment = false)
{
Piwik::checkUserHasViewAccess($idSite);
$archive = Archive::build($idSite, $period, $date, $segment);
return $archive->getDataTableFromNumeric(Archiver::DISTINCT_COUNTRIES_METRIC);
}
protected function getDataTable($name, $idSite, $period, $date, $segment, $expanded = false, $idSubtable = null, $secondaryDimension = false)
{
Piwik::checkUserHasViewAccess($idSite);
$this->checkSecondaryDimension($name, $secondaryDimension);
$recordName = $this->getRecordNameForAction($name, $secondaryDimension);
$dataTable = Archive::getDataTableFromArchive($recordName, $idSite, $period, $date, $segment, $expanded, $idSubtable);
$this->filterDataTable($dataTable);
return $dataTable;
}
/**
* @param int $idSite
* @param string $period
* @param string $date
* @param string|false $segment
* @param bool $expanded
* @param DataTable $dataTable
*/
private function buildExpandedTableForFlattenGetSocials($idSite, $period, $date, $segment, $expanded, $dataTable)
{
$urlsTable = Archive::createDataTableFromArchive(Archiver::WEBSITES_RECORD_NAME, $idSite, $period, $date, $segment, $expanded, $flat = true);
$urlsTable->filter('ColumnCallbackDeleteRow', array('label', function ($url) {
return !Social::getInstance()->isSocialUrl($url);
}));
$urlsTable = $urlsTable->mergeSubtables();
foreach ($dataTable->getRows() as $row) {
$row->removeSubtable();
$social = $row->getColumn('label');
$newTable = $urlsTable->getEmptyClone();
$rows = $urlsTable->getRows();
foreach ($rows as $id => $urlsTableRow) {
$url = $urlsTableRow->getColumn('label');
if (Social::getInstance()->isSocialUrl($url, $social)) {
$newTable->addRow($urlsTableRow);
$urlsTable->deleteRow($id);
}
}
if ($newTable->getRowsCount()) {
$newTable->filter('Piwik\\Plugins\\Referrers\\DataTable\\Filter\\UrlsForSocial', array($expanded));
$row->setSubtable($newTable);
}
}
Common::destroy($urlsTable);
$urlsTable = null;
}
public function getPlugin($idSite, $period, $date, $segment = false)
{
// fetch all archive data required
$dataTable = $this->getDataTable(Archiver::PLUGIN_RECORD_NAME, $idSite, $period, $date, $segment);
$browserTypes = $this->getDataTable(Archiver::BROWSER_TYPE_RECORD_NAME, $idSite, $period, $date, $segment);
$archive = Archive::build($idSite, $period, $date, $segment);
$visitsSums = $archive->getDataTableFromNumeric('nb_visits');
// check whether given tables are arrays
if ($dataTable instanceof DataTable\Map) {
$dataTableMap = $dataTable->getDataTables();
$browserTypesArray = $browserTypes->getDataTables();
$visitSumsArray = $visitsSums->getDataTables();
} else {
$dataTableMap = array($dataTable);
$browserTypesArray = array($browserTypes);
$visitSumsArray = array($visitsSums);
}
// walk through the results and calculate the percentage
foreach ($dataTableMap as $key => $table) {
// get according browserType table
foreach ($browserTypesArray as $k => $browsers) {
if ($k == $key) {
$browserType = $browsers;
}
}
// get according visitsSum
foreach ($visitSumsArray as $k => $visits) {
if ($k == $key) {
if (is_object($visits)) {
if ($visits->getRowsCount() == 0) {
$visitsSumTotal = 0;
} else {
$visitsSumTotal = (double) $visits->getFirstRow()->getColumn('nb_visits');
}
} else {
$visitsSumTotal = (double) $visits;
}
}
}
// Calculate percentage, but ignore IE users because plugin detection doesn't work on IE
$ieVisits = 0;
$ieStats = $browserType->getRowFromLabel('ie');
if ($ieStats !== false) {
$ieVisits = $ieStats->getColumn(Metrics::INDEX_NB_VISITS);
}
$visitsSum = $visitsSumTotal - $ieVisits;
// When Truncate filter is applied, it will call AddSummaryRow which tries to sum all rows.
// We tell the object to skip the column nb_visits_percentage when aggregating (since it's not correct to sum % values)
$columnAggregationOps = $table->getMetadata(DataTable::COLUMN_AGGREGATION_OPS_METADATA_NAME);
$columnAggregationOps['nb_visits_percentage'] = 'skip';
$table->setMetadata(DataTable::COLUMN_AGGREGATION_OPS_METADATA_NAME, $columnAggregationOps);
// The filter must be applied now so that the new column can
// be sorted by the generic filters (applied right after this loop exits)
$table->filter('ColumnCallbackAddColumnPercentage', array('nb_visits_percentage', Metrics::INDEX_NB_VISITS, $visitsSum, 1));
$table->filter('RangeCheck', array('nb_visits_percentage'));
}
$dataTable->queueFilter('ColumnCallbackAddMetadata', array('label', 'logo', __NAMESPACE__ . '\\getPluginsLogo'));
$dataTable->queueFilter('ColumnCallbackReplace', array('label', 'ucfirst'));
return $dataTable;
}
/**
* Helper function that creates an Archive instance and queries for report data using
* query parameter data. API methods can use this method to reduce code redundancy.
*
* @param string $name The name of the report to return.
* @param int|string|array $idSite @see {@link build()}
* @param string $period @see {@link build()}
* @param string $date @see {@link build()}
* @param string $segment @see {@link build()}
* @param bool $expanded If true, loads all subtables. See {@link getDataTableExpanded()}
* @param int|null $idSubtable See {@link getDataTableExpanded()}
* @param bool $skipAggregationOfSubTables Whether or not we should skip the aggregation of all sub-tables and only aggregate parent DataTable.
* @param int|null $depth See {@link getDataTableExpanded()}
* @return DataTable|DataTable\Map See {@link getDataTable()} and
* {@link getDataTableExpanded()} for more
* information
*/
public static function getDataTableFromArchive($name, $idSite, $period, $date, $segment, $expanded, $idSubtable = null, $skipAggregationOfSubTables = false, $depth = null)
{
Piwik::checkUserHasViewAccess($idSite);
if ($skipAggregationOfSubTables && ($expanded || $idSubtable)) {
throw new \Exception("Not expected to skipAggregationOfSubTables when expanded=1 or idSubtable is set.");
}
$archive = Archive::build($idSite, $period, $date, $segment, $_restrictSitesToLogin = false, $skipAggregationOfSubTables);
if ($idSubtable === false) {
$idSubtable = null;
}
if ($expanded) {
$dataTable = $archive->getDataTableExpanded($name, $idSubtable, $depth);
} else {
$dataTable = $archive->getDataTable($name, $idSubtable);
}
$dataTable->queueFilter('ReplaceSummaryRowLabel');
return $dataTable;
}
protected function getArchive()
{
if (empty($this->archive)) {
$subPeriods = $this->params->getSubPeriods();
$idSites = $this->params->getIdSites();
$this->archive = Archive::factory($this->params->getSegment(), $subPeriods, $idSites);
}
return $this->archive;
}
public function clearInMemoryCaches()
{
Archive::clearStaticCache();
DataTableManager::getInstance()->deleteAll();
Option::clearCache();
Site::clearCache();
Cache::deleteTrackerCache();
PiwikCache::getTransientCache()->flushAll();
PiwikCache::getEagerCache()->flushAll();
PiwikCache::getLazyCache()->flushAll();
ArchiveTableCreator::clear();
\Piwik\Plugins\ScheduledReports\API::$cache = array();
Singleton::clearAll();
PluginsArchiver::$archivers = array();
$_GET = $_REQUEST = array();
Translate::reset();
self::getConfig()->Plugins;
// make sure Plugins exists in a config object for next tests that use Plugin\Manager
// since Plugin\Manager uses getFromGlobalConfig which doesn't init the config object
}
public function get($archiveNames, $archiveDataType, $idSubtable = null)
{
return parent::get($archiveNames, $archiveDataType, $idSubtable);
}
private function buildDataTable($idSitesOrIdSite, $period, $date, $segment, $_restrictSitesToLogin, $enhanced, $multipleWebsitesRequested)
{
$allWebsitesRequested = $idSitesOrIdSite == 'all';
if ($allWebsitesRequested) {
// First clear cache
Site::clearCache();
// Then, warm the cache with only the data we should have access to
if (Piwik::isUserIsSuperUser() && !TaskScheduler::isTaskBeingExecuted()) {
$sites = APISitesManager::getInstance()->getAllSites();
} else {
$sites = APISitesManager::getInstance()->getSitesWithAtLeastViewAccess($limit = false, $_restrictSitesToLogin);
}
// Both calls above have called Site::setSitesFromArray. We now get these sites:
$sitesToProblablyAdd = Site::getSites();
} else {
$sitesToProblablyAdd = array(APISitesManager::getInstance()->getSiteFromId($idSitesOrIdSite));
}
// build the archive type used to query archive data
$archive = Archive::build($idSitesOrIdSite, $period, $date, $segment, $_restrictSitesToLogin);
// determine what data will be displayed
$fieldsToGet = array();
$columnNameRewrites = array();
$apiECommerceMetrics = array();
$apiMetrics = API::getApiMetrics($enhanced);
foreach ($apiMetrics as $metricName => $metricSettings) {
$fieldsToGet[] = $metricSettings[self::METRIC_RECORD_NAME_KEY];
$columnNameRewrites[$metricSettings[self::METRIC_RECORD_NAME_KEY]] = $metricName;
if ($metricSettings[self::METRIC_IS_ECOMMERCE_KEY]) {
$apiECommerceMetrics[$metricName] = $metricSettings;
}
}
// get the data
// $dataTable instanceOf Set
$dataTable = $archive->getDataTableFromNumeric($fieldsToGet);
$dataTable = $this->mergeDataTableMapAndPopulateLabel($idSitesOrIdSite, $multipleWebsitesRequested, $dataTable);
if ($dataTable instanceof DataTable\Map) {
foreach ($dataTable->getDataTables() as $table) {
$this->addMissingWebsites($table, $fieldsToGet, $sitesToProblablyAdd);
}
} else {
$this->addMissingWebsites($dataTable, $fieldsToGet, $sitesToProblablyAdd);
}
// calculate total visits/actions/revenue
$this->setMetricsTotalsMetadata($dataTable, $apiMetrics);
// if the period isn't a range & a lastN/previousN date isn't used, we get the same
// data for the last period to show the evolution of visits/actions/revenue
list($strLastDate, $lastPeriod) = Range::getLastDate($date, $period);
if ($strLastDate !== false) {
if ($lastPeriod !== false) {
// NOTE: no easy way to set last period date metadata when range of dates is requested.
// will be easier if DataTable\Map::metadata is removed, and metadata that is
// put there is put directly in DataTable::metadata.
$dataTable->setMetadata(self::getLastPeriodMetadataName('date'), $lastPeriod);
}
$pastArchive = Archive::build($idSitesOrIdSite, $period, $strLastDate, $segment, $_restrictSitesToLogin);
$pastData = $pastArchive->getDataTableFromNumeric($fieldsToGet);
$pastData = $this->mergeDataTableMapAndPopulateLabel($idSitesOrIdSite, $multipleWebsitesRequested, $pastData);
// use past data to calculate evolution percentages
$this->calculateEvolutionPercentages($dataTable, $pastData, $apiMetrics);
$this->setPastDataMetadata($dataTable, $pastData, $apiMetrics);
}
// remove eCommerce related metrics on non eCommerce Piwik sites
// note: this is not optimal in terms of performance: those metrics should not be retrieved in the first place
if ($enhanced) {
if ($dataTable instanceof DataTable\Map) {
foreach ($dataTable->getDataTables() as $table) {
$this->removeEcommerceRelatedMetricsOnNonEcommercePiwikSites($table, $apiECommerceMetrics);
}
} else {
$this->removeEcommerceRelatedMetricsOnNonEcommercePiwikSites($dataTable, $apiECommerceMetrics);
}
}
// move the site id to a metadata column
$dataTable->filter('ColumnCallbackAddMetadata', array('label', 'idsite'));
// set the label of each row to the site name
if ($multipleWebsitesRequested) {
$dataTable->filter('ColumnCallbackReplace', array('label', '\\Piwik\\Site::getNameFor'));
} else {
$dataTable->filter('ColumnDelete', array('label'));
}
// replace record names with user friendly metric names
$dataTable->filter('ReplaceColumnNames', array($columnNameRewrites));
// Ensures data set sorted, for Metadata output
$dataTable->filter('Sort', array(self::NB_VISITS_METRIC, 'desc', $naturalSort = false));
// filter rows without visits
// note: if only one website is queried and there are no visits, we can not remove the row otherwise
// ResponseBuilder throws 'Call to a member function getColumns() on a non-object'
if ($multipleWebsitesRequested && !$enhanced) {
$dataTable->filter('ColumnCallbackDeleteRow', array(self::NB_VISITS_METRIC, function ($value) {
return $value == 0;
}));
}
return $dataTable;
}
/**
* Utility method that retrieve an archived DataTable for a specific site, date range,
* segment and goal. If not goal is specified, this method will retrieve and sum the
* data for every goal.
*
* @param string $recordName The archive entry name.
* @param int|string $idSite The site(s) to select data for.
* @param string $period The period type.
* @param string $date The date type.
* @param string $segment The segment.
* @param int|bool $idGoal The id of the goal to get data for. If this is set to false,
* data for every goal that belongs to $idSite is returned.
* @return false|DataTable
*/
protected function getGoalSpecificDataTable($recordName, $idSite, $period, $date, $segment, $idGoal)
{
Piwik::checkUserHasViewAccess($idSite);
$archive = Archive::build($idSite, $period, $date, $segment);
// check for the special goal ids
$realGoalId = $idGoal != true ? false : self::convertSpecialGoalIds($idGoal);
// get the data table
$dataTable = $archive->getDataTable(Archiver::getRecordName($recordName, $realGoalId), $idSubtable = null);
$dataTable->queueFilter('ReplaceColumnNames');
return $dataTable;
}
protected function getDataTableFromArchive($name, $idSite, $period, $date, $segment, $expanded = false, $idSubtable = null, $depth = null)
{
$skipAggregationOfSubTables = false;
if ($period == 'range' && empty($idSubtable) && empty($expanded) && !Request::shouldLoadFlatten()) {
$skipAggregationOfSubTables = false;
}
return Archive::getDataTableFromArchive($name, $idSite, $period, $date, $segment, $expanded, $idSubtable, $skipAggregationOfSubTables, $depth);
}
protected static function _addReportData()
{
$date = Date::factory(self::$dateTime);
$archive = Archive::build(self::$idSite, 'year', $date);
APIVisitorInterest::getInstance()->getNumberOfVisitsPerVisitDuration(self::$idSite, 'year', $date);
// APIVisitorInterest::getInstance()->get(self::$idSite, 'month', $date, $segment = false, self::$idSite);
// months are added via the 'year' period, but weeks must be done manually
for ($daysAgo = self::$daysAgoStart; $daysAgo > 0; $daysAgo -= 7) {
$dateTime = $date->subDay($daysAgo);
$archive = Archive::build(self::$idSite, 'week', $dateTime);
$archive->getNumeric('nb_visits');
APIVisitorInterest::getInstance()->getNumberOfVisitsPerVisitDuration(self::$idSite, 'week', $dateTime);
}
// add segment for one day
$archive = Archive::build(self::$idSite, 'day', '2012-01-14', 'browserCode==FF');
$archive->getNumeric('nb_visits', 'nb_hits');
APIVisitorInterest::getInstance()->getNumberOfVisitsPerVisitDuration(self::$idSite, 'day', '2012-01-14', 'browserCode==FF');
// add range within January
$rangeEnd = Date::factory('2012-01-29');
$rangeStart = $rangeEnd->subDay(1);
$range = $rangeStart->toString('Y-m-d') . "," . $rangeEnd->toString('Y-m-d');
$rangeArchive = Archive::build(self::$idSite, 'range', $range);
$rangeArchive->getNumeric('nb_visits', 'nb_hits');
APIVisitorInterest::getInstance()->getNumberOfVisitsPerVisitDuration(self::$idSite, 'range', $range);
// add range between January & February
$rangeStart = $rangeEnd;
$rangeEnd = $rangeStart->addDay(3);
$range = $rangeStart->toString('Y-m-d') . "," . $rangeEnd->toString('Y-m-d');
$rangeArchive = Archive::build(self::$idSite, 'range', $range);
$rangeArchive->getNumeric('nb_visits', 'nb_hits');
APIVisitorInterest::getInstance()->getNumberOfVisitsPerVisitDuration(self::$idSite, 'range', $range);
// when archiving is initiated, the archive metrics & reports for EVERY loaded plugin
// are archived. don't want this test to depend on every possible metric, so get rid of
// the unwanted archive data now.
$metricsToSave = array('nb_visits', 'nb_actions', Archiver::getRecordName('revenue'), Archiver::getRecordName('nb_conversions', 1), Archiver::getRecordName('revenue', GoalManager::IDGOAL_ORDER));
$archiveTables = self::_getArchiveTableNames();
foreach ($archiveTables['numeric'] as $table) {
$realTable = Common::prefixTable($table);
$sql = "DELETE FROM {$realTable} WHERE name NOT IN ('" . implode("','", $metricsToSave) . "') AND name NOT LIKE 'done%'";
Db::query($sql);
}
foreach ($archiveTables['blob'] as $table) {
$realTable = Common::prefixTable($table);
Db::query("DELETE FROM {$realTable} WHERE name NOT IN ('VisitorInterest_timeGap')");
}
// add garbage metrics
$janDate1 = '2012-01-05';
$febDate1 = '2012-02-04';
$sql = "INSERT INTO %s (idarchive,name,idsite,date1,date2,period,ts_archived,value)\n VALUES (10000,?,1,?,?,?,?,?)";
// one metric for jan & one for feb
Db::query(sprintf($sql, Common::prefixTable($archiveTables['numeric'][0])), array(self::GARBAGE_FIELD, $janDate1, $janDate1, $janDate1, 1, 100));
Db::query(sprintf($sql, Common::prefixTable($archiveTables['numeric'][1])), array(self::GARBAGE_FIELD, $febDate1, $febDate1, $febDate1, 1, 200));
// add garbage reports
Db::query(sprintf($sql, Common::prefixTable($archiveTables['blob'][0])), array(self::GARBAGE_FIELD, $janDate1, $janDate1, $janDate1, 10, 'blobval'));
Db::query(sprintf($sql, Common::prefixTable($archiveTables['blob'][1])), array(self::GARBAGE_FIELD, $febDate1, $febDate1, $febDate1, 20, 'blobval'));
}
private function getNumeric($name, $idSite, $period, $date, $segment)
{
Piwik::checkUserHasViewAccess($idSite);
$archive = Archive::build($idSite, $period, $date, $segment);
return $archive->getDataTableFromNumeric($name);
}
private function buildDataTable($sitesToProblablyAdd, $period, $date, $segment, $_restrictSitesToLogin, $enhanced, $multipleWebsitesRequested, $showColumns)
{
$idSites = array();
if (!empty($sitesToProblablyAdd)) {
foreach ($sitesToProblablyAdd as $site) {
$idSites[] = $site['idsite'];
}
}
// build the archive type used to query archive data
$archive = Archive::build($idSites, $period, $date, $segment, $_restrictSitesToLogin);
// determine what data will be displayed
$fieldsToGet = array();
$columnNameRewrites = array();
$apiECommerceMetrics = array();
$apiMetrics = API::getApiMetrics($enhanced);
foreach ($apiMetrics as $metricName => $metricSettings) {
if (!empty($showColumns) && !in_array($metricName, $showColumns)) {
unset($apiMetrics[$metricName]);
continue;
}
$fieldsToGet[] = $metricSettings[self::METRIC_RECORD_NAME_KEY];
$columnNameRewrites[$metricSettings[self::METRIC_RECORD_NAME_KEY]] = $metricName;
if ($metricSettings[self::METRIC_IS_ECOMMERCE_KEY]) {
$apiECommerceMetrics[$metricName] = $metricSettings;
}
}
$dataTable = $archive->getDataTableFromNumericAndMergeChildren($fieldsToGet);
$this->populateLabel($dataTable);
$totalMetrics = $this->preformatApiMetricsForTotalsCalculation($apiMetrics);
$this->setMetricsTotalsMetadata($dataTable, $totalMetrics);
// if the period isn't a range & a lastN/previousN date isn't used, we get the same
// data for the last period to show the evolution of visits/actions/revenue
list($strLastDate, $lastPeriod) = Range::getLastDate($date, $period);
if ($strLastDate !== false) {
if ($lastPeriod !== false) {
// NOTE: no easy way to set last period date metadata when range of dates is requested.
// will be easier if DataTable\Map::metadata is removed, and metadata that is
// put there is put directly in DataTable::metadata.
$dataTable->setMetadata(self::getLastPeriodMetadataName('date'), $lastPeriod);
}
$pastArchive = Archive::build($idSites, $period, $strLastDate, $segment, $_restrictSitesToLogin);
$pastData = $pastArchive->getDataTableFromNumericAndMergeChildren($fieldsToGet);
$this->populateLabel($pastData);
// labels are needed to calculate evolution
$this->calculateEvolutionPercentages($dataTable, $pastData, $apiMetrics);
$this->setPastTotalVisitsMetadata($dataTable, $pastData);
if ($dataTable instanceof DataTable) {
// needed for MultiSites\Dashboard
$dataTable->setMetadata('pastData', $pastData);
}
}
// move the site id to a metadata column
$dataTable->queueFilter('MetadataCallbackAddMetadata', array('idsite', 'group', array('\\Piwik\\Site', 'getGroupFor'), array()));
$dataTable->queueFilter('MetadataCallbackAddMetadata', array('idsite', 'main_url', array('\\Piwik\\Site', 'getMainUrlFor'), array()));
// set the label of each row to the site name
if ($multipleWebsitesRequested) {
$dataTable->queueFilter('ColumnCallbackReplace', array('label', '\\Piwik\\Site::getNameFor'));
} else {
$dataTable->queueFilter('ColumnDelete', array('label'));
}
// replace record names with user friendly metric names
$dataTable->queueFilter('ReplaceColumnNames', array($columnNameRewrites));
// filter rows without visits
// note: if only one website is queried and there are no visits, we can not remove the row otherwise
// ResponseBuilder throws 'Call to a member function getColumns() on a non-object'
if ($multipleWebsitesRequested && !$enhanced) {
$dataTable->filter('ColumnCallbackDeleteRow', array(self::NB_VISITS_METRIC, function ($value) {
return $value == 0;
}));
}
if ($multipleWebsitesRequested && $dataTable->getRowsCount() === 1 && $dataTable instanceof DataTable\Simple) {
$simpleTable = $dataTable;
$dataTable = $simpleTable->getEmptyClone();
$dataTable->addRow($simpleTable->getFirstRow());
unset($simpleTable);
}
return $dataTable;
}