array (
LABEL => array(col1 => X, col2 => Y),
LABEL2 => array(col1 => X, col2 => Y),
)
to a DataTable with rows that look like:
array (
array( Row::COLUMNS => array('label' => LABEL, col1 => X, col2 => Y)),
array( Row::COLUMNS => array('label' => LABEL2, col1 => X, col2 => Y)),
)
Will also convert arrays like:
array (
LABEL => X,
LABEL2 => Y,
)
to:
array (
array( Row::COLUMNS => array('label' => LABEL, 'value' => X)),
array( Row::COLUMNS => array('label' => LABEL2, 'value' => Y)),
)
protected function aggregateByPlugin()
{
$selects = array("sum(case log_visit.config_pdf when 1 then 1 else 0 end) as pdf", "sum(case log_visit.config_flash when 1 then 1 else 0 end) as flash", "sum(case log_visit.config_java when 1 then 1 else 0 end) as java", "sum(case log_visit.config_director when 1 then 1 else 0 end) as director", "sum(case log_visit.config_quicktime when 1 then 1 else 0 end) as quicktime", "sum(case log_visit.config_realplayer when 1 then 1 else 0 end) as realplayer", "sum(case log_visit.config_windowsmedia when 1 then 1 else 0 end) as windowsmedia", "sum(case log_visit.config_gears when 1 then 1 else 0 end) as gears", "sum(case log_visit.config_silverlight when 1 then 1 else 0 end) as silverlight", "sum(case log_visit.config_cookie when 1 then 1 else 0 end) as cookie");
$query = $this->getLogAggregator()->queryVisitsByDimension(array(), false, $selects, $metrics = array());
$data = $query->fetch();
$cleanRow = LogAggregator::makeArrayOneColumn($data, Metrics::INDEX_NB_VISITS);
$table = DataTable::makeFromIndexedArray($cleanRow);
$this->insertTable(self::PLUGIN_RECORD_NAME, $table);
}
/**
* @param Metric[] $metrics
* @return DataTable
*/
private function toDataTable(array $metrics)
{
$translated = array();
foreach ($metrics as $metric) {
if (!$metric instanceof Metric) {
continue;
}
$label = Piwik::translate($metric->getName());
$translated[$label] = array('id' => $metric->getId(), 'rank' => $metric->getValue(), 'logo' => $metric->getLogo(), 'logo_link' => $metric->getLogoLink(), 'logo_tooltip' => Piwik::translate($metric->getLogoTooltip()), 'rank_suffix' => Piwik::translate($metric->getValueSuffix()));
}
return DataTable::makeFromIndexedArray($translated);
}
/**
* Returns SEO statistics for a URL.
*
* @param string $url URL to request SEO stats for
* @return DataTable
*/
public function getRank($url)
{
Piwik::checkUserHasSomeViewAccess();
$rank = new RankChecker($url);
$linkToMajestic = MajesticClient::getLinkForUrl($url);
$data = array('Google PageRank' => array('rank' => $rank->getPageRank(), 'logo' => \Piwik\Plugins\Referrers\getSearchEngineLogoFromUrl('http://google.com'), 'id' => 'pagerank'), Piwik::translate('SEO_Google_IndexedPages') => array('rank' => $rank->getIndexedPagesGoogle(), 'logo' => \Piwik\Plugins\Referrers\getSearchEngineLogoFromUrl('http://google.com'), 'id' => 'google-index'), Piwik::translate('SEO_Bing_IndexedPages') => array('rank' => $rank->getIndexedPagesBing(), 'logo' => \Piwik\Plugins\Referrers\getSearchEngineLogoFromUrl('http://bing.com'), 'id' => 'bing-index'), Piwik::translate('SEO_AlexaRank') => array('rank' => $rank->getAlexaRank(), 'logo' => \Piwik\Plugins\Referrers\getSearchEngineLogoFromUrl('http://alexa.com'), 'id' => 'alexa'), Piwik::translate('SEO_DomainAge') => array('rank' => $rank->getAge(), 'logo' => 'plugins/SEO/images/whois.png', 'id' => 'domain-age'), Piwik::translate('SEO_ExternalBacklinks') => array('rank' => $rank->getExternalBacklinkCount(), 'logo' => 'plugins/SEO/images/majesticseo.png', 'logo_link' => $linkToMajestic, 'logo_tooltip' => Piwik::translate('SEO_ViewBacklinksOnMajesticSEO'), 'id' => 'external-backlinks'), Piwik::translate('SEO_ReferrerDomains') => array('rank' => $rank->getReferrerDomainCount(), 'logo' => 'plugins/SEO/images/majesticseo.png', 'logo_link' => $linkToMajestic, 'logo_tooltip' => Piwik::translate('SEO_ViewBacklinksOnMajesticSEO'), 'id' => 'referrer-domains'));
// Add DMOZ only if > 0 entries found
$dmozRank = array('rank' => $rank->getDmoz(), 'logo' => \Piwik\Plugins\Referrers\getSearchEngineLogoFromUrl('http://dmoz.org'), 'id' => 'dmoz');
if ($dmozRank['rank'] > 0) {
$data[Piwik::translate('SEO_Dmoz')] = $dmozRank;
}
return DataTable::makeFromIndexedArray($data);
}
protected function aggregateGeneralGoalMetrics()
{
$prefixes = array(self::VISITS_UNTIL_RECORD_NAME => 'vcv', self::DAYS_UNTIL_CONV_RECORD_NAME => 'vdsf');
$selects = array();
$selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn(self::VISITS_COUNT_FIELD, self::$visitCountRanges, self::LOG_CONVERSION_TABLE, $prefixes[self::VISITS_UNTIL_RECORD_NAME]));
$selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn(self::DAYS_SINCE_FIRST_VISIT_FIELD, self::$daysToConvRanges, self::LOG_CONVERSION_TABLE, $prefixes[self::DAYS_UNTIL_CONV_RECORD_NAME]));
$query = $this->getLogAggregator()->queryConversionsByDimension(array(), false, $selects);
if ($query === false) {
return;
}
$totalConversions = $totalRevenue = 0;
$goals = new DataArray();
$visitsToConversions = $daysToConversions = array();
$conversionMetrics = $this->getLogAggregator()->getConversionsMetricFields();
while ($row = $query->fetch()) {
$idGoal = $row['idgoal'];
unset($row['idgoal']);
unset($row['label']);
$values = array();
foreach ($conversionMetrics as $field => $statement) {
$values[$field] = $row[$field];
}
$goals->sumMetrics($idGoal, $values);
if (empty($visitsToConversions[$idGoal])) {
$visitsToConversions[$idGoal] = new DataTable();
}
$array = LogAggregator::makeArrayOneColumn($row, Metrics::INDEX_NB_CONVERSIONS, $prefixes[self::VISITS_UNTIL_RECORD_NAME]);
$visitsToConversions[$idGoal]->addDataTable(DataTable::makeFromIndexedArray($array));
if (empty($daysToConversions[$idGoal])) {
$daysToConversions[$idGoal] = new DataTable();
}
$array = LogAggregator::makeArrayOneColumn($row, Metrics::INDEX_NB_CONVERSIONS, $prefixes[self::DAYS_UNTIL_CONV_RECORD_NAME]);
$daysToConversions[$idGoal]->addDataTable(DataTable::makeFromIndexedArray($array));
// We don't want to sum Abandoned cart metrics in the overall revenue/conversions/converted visits
// since it is a "negative conversion"
if ($idGoal != GoalManager::IDGOAL_CART) {
$totalConversions += $row[Metrics::INDEX_GOAL_NB_CONVERSIONS];
$totalRevenue += $row[Metrics::INDEX_GOAL_REVENUE];
}
}
// Stats by goal, for all visitors
$numericRecords = $this->getConversionsNumericMetrics($goals);
$this->getProcessor()->insertNumericRecords($numericRecords);
$this->insertReports(self::VISITS_UNTIL_RECORD_NAME, $visitsToConversions);
$this->insertReports(self::DAYS_UNTIL_CONV_RECORD_NAME, $daysToConversions);
// Stats for all goals
$nbConvertedVisits = $this->getProcessor()->getNumberOfVisitsConverted();
$metrics = array(self::getRecordName('conversion_rate') => $this->getConversionRate($nbConvertedVisits), self::getRecordName('nb_conversions') => $totalConversions, self::getRecordName('nb_visits_converted') => $nbConvertedVisits, self::getRecordName('revenue') => $totalRevenue);
$this->getProcessor()->insertNumericRecords($metrics);
}
public function aggregateDayReport()
{
// these prefixes are prepended to the 'SELECT as' parts of each SELECT expression. detecting
// these prefixes allows us to get all the data in one query.
$prefixes = array(self::TIME_SPENT_RECORD_NAME => 'tg', self::PAGES_VIEWED_RECORD_NAME => 'pg', self::VISITS_COUNT_RECORD_NAME => 'vbvn', self::DAYS_SINCE_LAST_RECORD_NAME => 'dslv');
// collect our extra aggregate select fields
$selects = array();
$selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn('visit_total_time', self::getSecondsGap(), 'log_visit', $prefixes[self::TIME_SPENT_RECORD_NAME]));
$selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn('visit_total_actions', self::$pageGap, 'log_visit', $prefixes[self::PAGES_VIEWED_RECORD_NAME]));
$selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn('visitor_count_visits', self::$visitNumberGap, 'log_visit', $prefixes[self::VISITS_COUNT_RECORD_NAME]));
$selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn('visitor_days_since_last', self::$daysSinceLastVisitGap, 'log_visit', $prefixes[self::DAYS_SINCE_LAST_RECORD_NAME], $restrictToReturningVisitors = true));
$query = $this->getLogAggregator()->queryVisitsByDimension(array(), $where = false, $selects, array());
$row = $query->fetch();
foreach ($prefixes as $recordName => $selectAsPrefix) {
$cleanRow = LogAggregator::makeArrayOneColumn($row, Metrics::INDEX_NB_VISITS, $selectAsPrefix);
$dataTable = DataTable::makeFromIndexedArray($cleanRow);
$this->getProcessor()->insertBlobRecord($recordName, $dataTable->getSerialized());
}
}
/**
* Converts array to a datatable
*
* @return \Piwik\DataTable
*/
public function asDataTable()
{
$dataArray = $this->getDataArray();
$dataArrayTwoLevels = $this->getDataArrayWithTwoLevels();
$subtableByLabel = null;
if (!empty($dataArrayTwoLevels)) {
$subtableByLabel = array();
foreach ($dataArrayTwoLevels as $label => $subTable) {
$subtableByLabel[$label] = DataTable::makeFromIndexedArray($subTable);
}
}
return DataTable::makeFromIndexedArray($dataArray, $subtableByLabel);
}
public function getPlanetRatios()
{
$planetRatios = array('Mercury' => 0.382, 'Venus' => 0.949, 'Earth' => 1.0, 'Mars' => 0.532, 'Jupiter' => 11.209, 'Saturn' => 9.449, 'Uranus' => 4.007, 'Neptune' => 3.883);
return DataTable::makeFromIndexedArray($planetRatios);
}
/**
* Returns a datatable summarizing how data is distributed among Piwik tables.
*
* This function will group tracker tables, numeric archive tables, blob archive tables
* and other tables together so only four rows are shown.
*
* @return DataTable A datatable with three columns: 'data_size', 'index_size', 'row_count'.
*/
public function getDatabaseUsageSummary()
{
Piwik::checkUserHasSuperUserAccess();
$emptyRow = array('data_size' => 0, 'index_size' => 0, 'row_count' => 0);
$rows = array('tracker_data' => $emptyRow, 'metric_data' => $emptyRow, 'report_data' => $emptyRow, 'other_data' => $emptyRow);
foreach ($this->metadataProvider->getAllTablesStatus() as $status) {
if ($this->isNumericArchiveTable($status['Name'])) {
$rowToAddTo =& $rows['metric_data'];
} else {
if ($this->isBlobArchiveTable($status['Name'])) {
$rowToAddTo =& $rows['report_data'];
} else {
if ($this->isTrackerTable($status['Name'])) {
$rowToAddTo =& $rows['tracker_data'];
} else {
$rowToAddTo =& $rows['other_data'];
}
}
}
$rowToAddTo['data_size'] += $status['Data_length'];
$rowToAddTo['index_size'] += $status['Index_length'];
$rowToAddTo['row_count'] += $status['Rows'];
}
return DataTable::makeFromIndexedArray($rows);
}
