public function queryCustomDimensionActions(DataArray $dataArray, $valueField, $additionalWhere = '')
{
$metricsConfig = ActionsMetrics::getActionMetrics();
$metricIds = array_keys($metricsConfig);
$metricIds[] = Metrics::INDEX_PAGE_SUM_TIME_SPENT;
$metricIds[] = Metrics::INDEX_BOUNCE_COUNT;
$metricIds[] = Metrics::INDEX_PAGE_EXIT_NB_VISITS;
$dataArray->setActionMetricsIds($metricIds);
$select = "log_link_visit_action.{$valueField},\n log_action.name as url,\n sum(log_link_visit_action.time_spent) as `" . Metrics::INDEX_PAGE_SUM_TIME_SPENT . "`,\n sum(case log_visit.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) as `" . Metrics::INDEX_BOUNCE_COUNT . "`,\n sum(IF(log_visit.last_idlink_va = log_link_visit_action.idlink_va, 1, 0)) as `" . Metrics::INDEX_PAGE_EXIT_NB_VISITS . "`";
$select = $this->addMetricsToSelect($select, $metricsConfig);
$from = array("log_link_visit_action", array("table" => "log_visit", "joinOn" => "log_visit.idvisit = log_link_visit_action.idvisit"), array("table" => "log_action", "joinOn" => "log_link_visit_action.idaction_url = log_action.idaction"));
$where = "log_link_visit_action.server_time >= ?\n AND log_link_visit_action.server_time <= ?\n AND log_link_visit_action.idsite = ?";
if (!empty($additionalWhere)) {
$where .= ' AND ' . $additionalWhere;
}
$groupBy = "log_link_visit_action.{$valueField}, url";
$orderBy = "`" . Metrics::INDEX_PAGE_NB_HITS . "` DESC";
// get query with segmentation
$logAggregator = $this->getLogAggregator();
$query = $logAggregator->generateQuery($select, $from, $where, $groupBy, $orderBy);
$db = $logAggregator->getDb();
$resultSet = $db->query($query['sql'], $query['bind']);
return $resultSet;
}
protected function archiveDayActions($rankingQueryLimit)
{
$select = "log_action.name,\n\t\t\t\tlog_action.type,\n\t\t\t\tlog_action.idaction,\n\t\t\t\tlog_action.url_prefix,\n\t\t\t\tcount(distinct log_link_visit_action.idvisit) as " . $this->db->quoteIdentifier(PiwikMetrics::INDEX_NB_VISITS) . ",\n\t\t\t\tcount(distinct log_link_visit_action.idvisitor) as " . $this->db->quoteIdentifier(PiwikMetrics::INDEX_NB_UNIQ_VISITORS) . ",\n\t\t\t\tcount(*) as " . $this->db->quoteIdentifier(PiwikMetrics::INDEX_PAGE_NB_HITS) . "";
$metricsConfig = Metrics::getActionMetrics();
$select = $this->addMetricsToSelect($select, $metricsConfig);
$from = array("log_link_visit_action", array("table" => "log_action", "joinOn" => "log_link_visit_action.%s = log_action.idaction"));
$where = "log_link_visit_action.server_time >= ?\n AND log_link_visit_action.server_time <= ?\n AND log_link_visit_action.idsite = ?\n AND log_link_visit_action.%s IS NOT NULL" . $this->getWhereClauseActionIsNotEvent();
$groupBy = "log_action.idaction";
$orderBy = $this->db->quoteIdentifier(PiwikMetrics::INDEX_PAGE_NB_HITS) . " DESC, name ASC";
$rankingQuery = false;
if ($rankingQueryLimit > 0) {
$rankingQuery = Factory::getHelper('RankingQuery');
$rankingQuery->setLimit($rankingQueryLimit);
$rankingQuery->setOthersLabel(DataTable::LABEL_SUMMARY_ROW);
$rankingQuery->addLabelColumn(array('idaction', 'name'));
$rankingQuery->addColumn(array('url_prefix', PiwikMetrics::INDEX_NB_UNIQ_VISITORS));
$rankingQuery->addColumn(array(PiwikMetrics::INDEX_PAGE_NB_HITS, PiwikMetrics::INDEX_NB_VISITS), 'sum');
if ($this->isSiteSearchEnabled()) {
$rankingQuery->addColumn(PiwikMetrics::INDEX_SITE_SEARCH_HAS_NO_RESULT, 'min');
$rankingQuery->addColumn(PiwikMetrics::INDEX_PAGE_IS_FOLLOWING_SITE_SEARCH_NB_HITS, 'sum');
}
$this->addMetricsToRankingQuery($rankingQuery, $metricsConfig);
$rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($this->actionsTablesByType));
}
// Special Magic to get
// 1) No result Keywords
// 2) For each page view, count number of times the referrer page was a Site Search
if ($this->isSiteSearchEnabled()) {
$this->updateQuerySelectFromForSiteSearch($select, $from);
}
$this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, "idaction_name", $rankingQuery, $metricsConfig);
$this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, "idaction_url", $rankingQuery, $metricsConfig);
}
