/**
* @group Core
*/
public function testPartitionResult()
{
$query = new RankingQuery(1000);
$query->addLabelColumn('label');
$query->partitionResultIntoMultipleGroups('partition', array(1, 2, 3));
$innerQuery = "SELECT label, partition FROM myTable";
$expected = "\n\t\t\tSELECT\n\t\t\t\tCASE\n\t\t\t\t\tWHEN counter = 1001 THEN 'Others'\n\t\t\t\t\tELSE `label`\n\t\t\t\tEND AS `label`,\n\t\t\t\t`partition`\n\t\t\tFROM ( \n\t\t\t\tSELECT\n\t\t\t\t\t`label`,\n\t\t\t\t\tCASE\n\t\t\t\t\t\tWHEN `partition` = 1 AND @counter1 = 1001 THEN 1001\n\t\t\t\t\t\tWHEN `partition` = 1 THEN @counter1:=@counter1+1\n\t\t\t\t\t\tWHEN `partition` = 2 AND @counter2 = 1001 THEN 1001\n\t\t\t\t\t\tWHEN `partition` = 2 THEN @counter2:=@counter2+1\n\t\t\t\t\t\tWHEN `partition` = 3 AND @counter3 = 1001 THEN 1001\n\t\t\t\t\t\tWHEN `partition` = 3 THEN @counter3:=@counter3+1\n\t\t\t\t\t\tELSE 0\n\t\t\t\t\tEND AS counter,\n\t\t\t\t\t`partition`\n\t\t\t\tFROM\n\t\t\t\t\t( SELECT @counter1:=0 ) initCounter1,\n\t\t\t\t\t( SELECT @counter2:=0 ) initCounter2,\n\t\t\t\t\t( SELECT @counter3:=0 ) initCounter3, \n\t\t\t\t\t( SELECT label, partition FROM myTable ) actualQuery\n\t\t\t) AS withCounter\n\t\t\tGROUP BY counter, `partition`\n\t\t";
$this->checkQuery($query, $innerQuery, $expected);
}
/**
* Time per action
*/
protected function archiveDayActionsTime($rankingQueryLimit)
{
$rankingQuery = false;
if ($rankingQueryLimit > 0) {
$rankingQuery = new RankingQuery($rankingQueryLimit);
$rankingQuery->setOthersLabel(DataTable::LABEL_SUMMARY_ROW);
$rankingQuery->addLabelColumn('idaction');
$rankingQuery->addColumn(Metrics::INDEX_PAGE_SUM_TIME_SPENT, 'sum');
$rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($this->actionsTablesByType));
$extraSelects = "log_action.type, log_action.name, count(*) as `" . Metrics::INDEX_PAGE_NB_HITS . "`,";
$from = array("log_link_visit_action", array("table" => "log_action", "joinOn" => "log_link_visit_action.%s = log_action.idaction"));
$orderBy = "`" . Metrics::INDEX_PAGE_NB_HITS . "` DESC, log_action.name ASC";
} else {
$extraSelects = false;
$from = "log_link_visit_action";
$orderBy = false;
}
$select = "log_link_visit_action.%s as idaction, {$extraSelects}\n\t\t\t\tsum(log_link_visit_action.time_spent_ref_action) as `" . Metrics::INDEX_PAGE_SUM_TIME_SPENT . "`";
$where = "log_link_visit_action.server_time >= ?\n\t\t\t\tAND log_link_visit_action.server_time <= ?\n\t\t \t\tAND log_link_visit_action.idsite = ?\n\t\t \t\tAND log_link_visit_action.time_spent_ref_action > 0\n\t\t \t\tAND log_link_visit_action.%s > 0" . $this->getWhereClauseActionIsNotEvent();
$groupBy = "log_link_visit_action.%s, idaction";
$this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "idaction_url_ref", $rankingQuery);
$this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "idaction_name_ref", $rankingQuery);
}
/**
* 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);
}
