You can use the methods in this class within {@link Piwik\Plugin\Archiver Archiver} descendants
to aggregate log data without having to write SQL queries.
### Aggregation Dimension
All aggregation methods accept a **dimension** parameter. These parameters are important as
they control how rows in a table are aggregated together.
A **_dimension_** is just a table column. Rows that have the same values for these columns are
aggregated together. The result of these aggregations is a set of metrics for every recorded value
of a **dimension**.
_Note: A dimension is essentially the same as a **GROUP BY** field._
### Examples
**Aggregating visit data**
$archiveProcessor = // ...
$logAggregator = $archiveProcessor->getLogAggregator();
get metrics for every used browser language of all visits by returning visitors
$query = $logAggregator->queryVisitsByDimension(
$dimensions = array('log_visit.location_browser_lang'),
$where = 'log_visit.visitor_returning = 1',
also count visits for each browser language that are not located in the US
$additionalSelects = array('sum(case when log_visit.location_country <> 'us' then 1 else 0 end) as nonus'),
we're only interested in visits, unique visitors & actions, so don't waste time calculating anything else
$metrics = array(Metrics::INDEX_NB_UNIQ_VISITORS, Metrics::INDEX_NB_VISITS, Metrics::INDEX_NB_ACTIONS),
);
if ($query === false) {
return;
}
while ($row = $query->fetch()) {
$uniqueVisitors = $row[Metrics::INDEX_NB_UNIQ_VISITORS];
$visits = $row[Metrics::INDEX_NB_VISITS];
$actions = $row[Metrics::INDEX_NB_ACTIONS];
... do something w/ calculated metrics ...
}
**Aggregating conversion data**
$archiveProcessor = // ...
$logAggregator = $archiveProcessor->getLogAggregator();
get metrics for ecommerce conversions for each country
$query = $logAggregator->queryConversionsByDimension(
$dimensions = array('log_conversion.location_country'),
$where = 'log_conversion.idgoal = 0', // 0 is the special ecommerceOrder idGoal value in the table
also calculate average tax and max shipping per country
$additionalSelects = array(
'AVG(log_conversion.revenue_tax) as avg_tax',
'MAX(log_conversion.revenue_shipping) as max_shipping'
)
);
if ($query === false) {
return;
}
while ($row = $query->fetch()) {
$country = $row['location_country'];
$numEcommerceSales = $row[Metrics::INDEX_GOAL_NB_CONVERSIONS];
$numVisitsWithEcommerceSales = $row[Metrics::INDEX_GOAL_NB_VISITS_CONVERTED];
$avgTaxForCountry = $row['avg_tax'];
$maxShippingForCountry = $row['max_shipping'];
... do something with aggregated data ...
}
public function test_generateQuery_WithQueryHint_ShouldAddQueryHintAsComment()
{
$this->logAggregator->setQueryOriginHint('MyPluginName');
$query = $this->logAggregator->generateQuery('test, test2', 'log_visit', '1=1', false, '5');
$expected = array('sql' => 'SELECT /* MyPluginName */
test, test2
FROM
log_visit AS log_visit
WHERE
1=1
ORDER BY
5', 'bind' => array(0 => '2012-01-01 00:00:00', 1 => '2012-01-31 23:59:59', 2 => 1));
$this->assertSame($expected, $query);
}
/**
* @param $idVisit
* @param $idOrder
* @param $actionsLimit
* @return array
* @throws \Exception
*/
public function queryEcommerceItemsForOrder($idVisit, $idOrder, $actionsLimit)
{
$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, $idOrder);
$itemsDetails = Db::fetchAll($sql, $bind);
return $itemsDetails;
}
/**
* Instantiates the Archiver class in each plugin that defines it,
* and triggers Aggregation processing on these plugins.
*/
public function callAggregateAllPlugins($visits, $visitsConverted)
{
Log::debug("PluginsArchiver::%s: Initializing archiving process for all plugins [visits = %s, visits converted = %s]", __FUNCTION__, $visits, $visitsConverted);
$this->archiveProcessor->setNumberOfVisits($visits, $visitsConverted);
$archivers = $this->getPluginArchivers();
foreach ($archivers as $pluginName => $archiverClass) {
// We clean up below all tables created during this function call (and recursive calls)
$latestUsedTableId = Manager::getInstance()->getMostRecentTableId();
/** @var Archiver $archiver */
$archiver = new $archiverClass($this->archiveProcessor);
if (!$archiver->isEnabled()) {
Log::debug("PluginsArchiver::%s: Skipping archiving for plugin '%s'.", __FUNCTION__, $pluginName);
continue;
}
if ($this->shouldProcessReportsForPlugin($pluginName)) {
$this->logAggregator->setQueryOriginHint($pluginName);
$timer = new Timer();
if ($this->isSingleSiteDayArchive) {
Log::debug("PluginsArchiver::%s: Archiving day reports for plugin '%s'.", __FUNCTION__, $pluginName);
$archiver->aggregateDayReport();
} else {
Log::debug("PluginsArchiver::%s: Archiving period reports for plugin '%s'.", __FUNCTION__, $pluginName);
$archiver->aggregateMultipleReports();
}
$this->logAggregator->setQueryOriginHint('');
Log::debug("PluginsArchiver::%s: %s while archiving %s reports for plugin '%s'.", __FUNCTION__, $timer->getMemoryLeak(), $this->params->getPeriod()->getLabel(), $pluginName);
} else {
Log::debug("PluginsArchiver::%s: Not archiving reports for plugin '%s'.", __FUNCTION__, $pluginName);
}
Manager::getInstance()->deleteAll($latestUsedTableId);
unset($archiver);
}
}
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);
}
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());
}
}
/**
* @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;
}
/**
* Get information about internal referrers (previous pages & loops, i.e. page refreshes)
*
* @param $idaction
* @param $actionType
* @param LogAggregator $logAggregator
* @param $limitBeforeGrouping
* @return array(previousPages:DataTable, loops:integer)
*/
protected function queryInternalReferrers($idaction, $actionType, $logAggregator, $limitBeforeGrouping = false)
{
$keyIsOther = 0;
$keyIsPageUrlAction = 1;
$keyIsSiteSearchAction = 2;
$rankingQuery = new RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping);
$rankingQuery->addLabelColumn(array('name', 'url_prefix'));
$rankingQuery->setColumnToMarkExcludedRows('is_self');
$rankingQuery->partitionResultIntoMultipleGroups('action_partition', array($keyIsOther, $keyIsPageUrlAction, $keyIsSiteSearchAction));
$type = $this->getColumnTypeSuffix($actionType);
$mainActionType = Action::TYPE_PAGE_URL;
$dimension = 'idaction_url_ref';
if ($actionType == 'title') {
$mainActionType = Action::TYPE_PAGE_TITLE;
$dimension = 'idaction_name_ref';
}
$selects = array('log_action.name', 'log_action.url_prefix', 'CASE WHEN log_link_visit_action.idaction_' . $type . '_ref = ' . intval($idaction) . ' THEN 1 ELSE 0 END AS `is_self`', 'CASE
WHEN log_action.type = ' . $mainActionType . ' THEN ' . $keyIsPageUrlAction . '
WHEN log_action.type = ' . Action::TYPE_SITE_SEARCH . ' THEN ' . $keyIsSiteSearchAction . '
ELSE ' . $keyIsOther . '
END AS `action_partition`');
$where = ' log_link_visit_action.idaction_' . $type . ' = ' . intval($idaction);
if ($dimension == 'idaction_url_ref') {
// site search referrers are logged with url_ref=NULL
// when we find one, we have to join on name_ref
$dimension = 'IF( idaction_url_ref IS NULL, idaction_name_ref, idaction_url_ref )';
$joinLogActionOn = $dimension;
} else {
$joinLogActionOn = $dimension;
}
$metrics = array(Metrics::INDEX_NB_ACTIONS);
$data = $logAggregator->queryActionsByDimension(array($dimension), $where, $selects, $metrics, $rankingQuery, $joinLogActionOn);
$loops = 0;
$nbPageviews = 0;
$previousPagesDataTable = new DataTable();
if (isset($data['result'][$keyIsPageUrlAction])) {
foreach ($data['result'][$keyIsPageUrlAction] as &$page) {
$nbActions = intval($page[Metrics::INDEX_NB_ACTIONS]);
$previousPagesDataTable->addRow(new Row(array(Row::COLUMNS => array('label' => $this->getPageLabel($page, Action::TYPE_PAGE_URL), Metrics::INDEX_NB_ACTIONS => $nbActions))));
$nbPageviews += $nbActions;
}
}
$previousSearchesDataTable = new DataTable();
if (isset($data['result'][$keyIsSiteSearchAction])) {
foreach ($data['result'][$keyIsSiteSearchAction] as &$search) {
$nbActions = intval($search[Metrics::INDEX_NB_ACTIONS]);
$previousSearchesDataTable->addRow(new Row(array(Row::COLUMNS => array('label' => $search['name'], Metrics::INDEX_NB_ACTIONS => $nbActions))));
$nbPageviews += $nbActions;
}
}
if (isset($data['result'][0])) {
foreach ($data['result'][0] as &$referrer) {
$nbPageviews += intval($referrer[Metrics::INDEX_NB_ACTIONS]);
}
}
if (count($data['excludedFromLimit'])) {
$loops += intval($data['excludedFromLimit'][0][Metrics::INDEX_NB_ACTIONS]);
$nbPageviews += $loops;
}
return array('pageviews' => $nbPageviews, 'previousPages' => $previousPagesDataTable, 'previousSiteSearches' => $previousSearchesDataTable, 'loops' => $loops);
}
protected function getSelectAveragePrice()
{
$field = "custom_var_v" . \PiwikTracker::CVAR_INDEX_ECOMMERCE_ITEM_PRICE;
return LogAggregator::getSqlRevenue("AVG(log_link_visit_action." . $field . ")") . " as `" . Metrics::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED . "`";
}