/**
* @param Piwik_RankingQuery $rankingQuery
* @param string $innerQuerySql
* @param string $expected
*/
private function checkQuery($rankingQuery, $innerQuerySql, $expected)
{
$query = $rankingQuery->generateQuery($innerQuerySql);
$queryNoWhitespace = preg_replace("/\\s+/", "", $query);
$expectedNoWhitespace = preg_replace("/\\s+/", "", $expected);
$message = 'Unexpected query: ' . $query;
$this->assertEquals($queryNoWhitespace, $expectedNoWhitespace, $message);
}
/**
* Query visits by dimension
*
* @param array|string $label Can be a string, eg. "referer_name", will be aliased as 'label' in the returned rows
* Can also be an array of strings, when the dimension spans multiple fields,
* eg. array("referer_name", "referer_keyword")
* @param string $where Additional condition for WHERE clause
* @param bool|array $metrics Set this if you want to limit the columns that are returned.
* The possible values in the array are Piwik_Archive::INDEX_*.
* @param bool|string $orderBy ORDER BY clause. This is needed in combination with $rankingQuery.
* @param Piwik_RankingQuery $rankingQuery
* A pre-configured ranking query instance that is used to limit the result.
* If set, the return value is the array returned by Piwik_RankingQuery::execute().
* @param string $addSelect Additional SELECT clause
* @param bool $addSelectGeneratesLabelColumn
* Set to true if the $label column is generated in $addSelect.
* @return mixed
*/
public function queryVisitsByDimension($label, $where = '', $metrics = false, $orderBy = false, $rankingQuery = null, $addSelect = false, $addSelectGeneratesLabelColumn = false)
{
if (is_array($label)) {
$groupBy = "log_visit." . implode(", log_visit.", $label);
foreach ($label as &$field) {
$field = 'log_visit.' . $field . ' AS ' . $field;
}
$select = implode(", ", $label);
} else {
if ($addSelectGeneratesLabelColumn) {
$select = $addSelect;
$groupBy = $label;
} else {
$select = $label . " AS label ";
$groupBy = 'label';
}
}
if (!empty($where)) {
$where = sprintf($where, "log_visit", "log_visit");
$where = ' AND ' . $where;
}
$pre = ", \n\t\t\t";
if (!$metrics || in_array(Piwik_Archive::INDEX_NB_UNIQ_VISITORS, $metrics)) {
$select .= $pre . "count(distinct log_visit.idvisitor) as `" . Piwik_Archive::INDEX_NB_UNIQ_VISITORS . "`";
}
if (!$metrics || in_array(Piwik_Archive::INDEX_NB_VISITS, $metrics)) {
$select .= $pre . "count(*) as `" . Piwik_Archive::INDEX_NB_VISITS . "`";
}
if (!$metrics || in_array(Piwik_Archive::INDEX_NB_ACTIONS, $metrics)) {
$select .= $pre . "sum(log_visit.visit_total_actions) as `" . Piwik_Archive::INDEX_NB_ACTIONS . "`";
}
if (!$metrics || in_array(Piwik_Archive::INDEX_MAX_ACTIONS, $metrics)) {
$select .= $pre . "max(log_visit.visit_total_actions) as `" . Piwik_Archive::INDEX_MAX_ACTIONS . "`";
}
if (!$metrics || in_array(Piwik_Archive::INDEX_SUM_VISIT_LENGTH, $metrics)) {
$select .= $pre . "sum(log_visit.visit_total_time) as `" . Piwik_Archive::INDEX_SUM_VISIT_LENGTH . "`";
}
if (!$metrics || in_array(Piwik_Archive::INDEX_BOUNCE_COUNT, $metrics)) {
$select .= $pre . "sum(case log_visit.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) as `" . Piwik_Archive::INDEX_BOUNCE_COUNT . "`";
}
if (!$metrics || in_array(Piwik_Archive::INDEX_NB_VISITS_CONVERTED, $metrics)) {
$select .= $pre . "sum(case log_visit.visit_goal_converted when 1 then 1 else 0 end) as `" . Piwik_Archive::INDEX_NB_VISITS_CONVERTED . "`";
}
if ($addSelect && !$addSelectGeneratesLabelColumn) {
$select .= ', ' . $addSelect;
}
$from = "log_visit";
$where = "log_visit.visit_last_action_time >= ?\n\t\t\t\tAND log_visit.visit_last_action_time <= ?\n\t\t\t\tAND log_visit.idsite = ?\n\t\t\t\t{$where}";
$bind = array($this->getStartDatetimeUTC(), $this->getEndDatetimeUTC(), $this->idsite);
$query = $this->getSegment()->getSelectQuery($select, $from, $where, $bind, $orderBy, $groupBy);
if ($rankingQuery !== null) {
$sumColumns = array(Piwik_Archive::INDEX_NB_UNIQ_VISITORS, Piwik_Archive::INDEX_NB_VISITS, Piwik_Archive::INDEX_NB_ACTIONS, Piwik_Archive::INDEX_SUM_VISIT_LENGTH, Piwik_Archive::INDEX_BOUNCE_COUNT, Piwik_Archive::INDEX_NB_VISITS_CONVERTED);
if ($metrics) {
foreach ($sumColumns as $i => $column) {
if (!in_array($column, $metrics)) {
unset($sumColumns[$i]);
}
}
$sumColumns = array_values($sumColumns);
}
$rankingQuery->addColumn($sumColumns, 'sum');
if (!$metrics || in_array(Piwik_Archive::INDEX_MAX_ACTIONS, $metrics)) {
$rankingQuery->addColumn(Piwik_Archive::INDEX_MAX_ACTIONS, 'max');
}
return $rankingQuery->execute($query['sql'], $query['bind']);
}
return $this->db->query($query['sql'], $query['bind']);
}
/**
* @param $select
* @param $from
* @param $where
* @param $orderBy
* @param $groupBy
* @param $sprintfField
* @param Piwik_ArchiveProcessing $archiveProcessing
* @param Piwik_RankingQuery|false $rankingQuery
* @return int
*/
protected function archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, $sprintfField, $archiveProcessing, $rankingQuery = false)
{
// idaction field needs to be set in select clause before calling getSelectQuery().
// if a complex segmentation join is needed, the field needs to be propagated
// to the outer select. therefore, $segment needs to know about it.
$select = sprintf($select, $sprintfField);
$bind = array();
// get query with segmentation
$query = $archiveProcessing->getSegment()->getSelectQuery($select, $from, $where, $bind, $orderBy, $groupBy);
// extend bindings
$bind = array_merge(array($archiveProcessing->getStartDatetimeUTC(), $archiveProcessing->getEndDatetimeUTC(), $archiveProcessing->idsite), $query['bind']);
// replace the rest of the %s
$querySql = str_replace("%s", $sprintfField, $query['sql']);
// apply ranking query
if ($rankingQuery) {
$querySql = $rankingQuery->generateQuery($querySql);
}
// echo '<pre>';var_dump($querySql);
// var_dump($bind);
// get result
$resultSet = $archiveProcessing->db->query($querySql, $bind);
$modified = Piwik_Actions_ArchivingHelper::updateActionsTableWithRowQuery($resultSet, $sprintfField, $this->actionsTablesByType);
return $modified;
}
/**
* Get information about the following actions (following pages, site searches, outlinks, downloads)
*
* @param $idaction
* @param $actionType
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @param $limitBeforeGrouping
* @param $includeLoops
* @return array(followingPages:Piwik_DataTable, outlinks:Piwik_DataTable, downloads:Piwik_DataTable)
*/
public function queryFollowingActions($idaction, $actionType, Piwik_ArchiveProcessing_Day $archiveProcessing, $limitBeforeGrouping = false, $includeLoops = false)
{
$types = array();
$isTitle = $actionType == 'title';
if (!$isTitle) {
// specific setup for page urls
$types[Piwik_Tracker_Action::TYPE_ACTION_URL] = 'followingPages';
$dimension = 'IF( idaction_url IS NULL, idaction_name, idaction_url )';
// site search referrers are logged with url=NULL
// when we find one, we have to join on name
$joinLogActionColumn = $dimension;
$addSelect = 'log_action.name, log_action.url_prefix, log_action.type';
} else {
// specific setup for page titles:
$types[Piwik_Tracker_Action::TYPE_ACTION_NAME] = 'followingPages';
// join log_action on name and url and pick depending on url type
// the table joined on url is log_action1
$joinLogActionColumn = array('idaction_url', 'idaction_name');
$dimension = '
CASE
' . '
WHEN log_link_visit_action.idaction_url IS NULL THEN log_action2.idaction
' . '
WHEN log_action1.type = ' . Piwik_Tracker_Action::TYPE_ACTION_URL . ' THEN log_action2.idaction
' . '
ELSE log_action1.idaction
END
';
$addSelect = '
CASE
' . '
WHEN log_link_visit_action.idaction_url IS NULL THEN log_action2.name
' . '
WHEN log_action1.type = ' . Piwik_Tracker_Action::TYPE_ACTION_URL . ' THEN log_action2.name
' . '
ELSE log_action1.name
END AS name,
CASE
' . '
WHEN log_link_visit_action.idaction_url IS NULL THEN log_action2.type
' . '
WHEN log_action1.type = ' . Piwik_Tracker_Action::TYPE_ACTION_URL . ' THEN log_action2.type
' . '
ELSE log_action1.type
END AS type,
NULL AS url_prefix
';
}
// these types are available for both titles and urls
$types[Piwik_Tracker_Action::TYPE_SITE_SEARCH] = 'followingSiteSearches';
$types[Piwik_Tracker_Action::TYPE_OUTLINK] = 'outlinks';
$types[Piwik_Tracker_Action::TYPE_DOWNLOAD] = 'downloads';
$rankingQuery = new Piwik_RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping);
$rankingQuery->addLabelColumn(array('name', 'url_prefix'));
$rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($types));
$type = $this->getColumnTypeSuffix($actionType);
$where = 'log_link_visit_action.idaction_' . $type . '_ref = ' . intval($idaction);
if (!$includeLoops) {
$where .= ' AND (log_link_visit_action.idaction_' . $type . ' IS NULL OR ' . 'log_link_visit_action.idaction_' . $type . ' != ' . intval($idaction) . ')';
}
$orderBy = '`' . Piwik_Archive::INDEX_NB_ACTIONS . '` DESC';
$metrics = array(Piwik_Archive::INDEX_NB_ACTIONS);
$data = $archiveProcessing->queryActionsByDimension($dimension, $where, $metrics, $orderBy, $rankingQuery, $joinLogActionColumn, $addSelect);
$this->totalTransitionsToFollowingActions = 0;
$dataTables = array();
foreach ($types as $type => $recordName) {
$dataTable = new Piwik_DataTable();
if (isset($data[$type])) {
foreach ($data[$type] as &$record) {
$actions = intval($record[Piwik_Archive::INDEX_NB_ACTIONS]);
$dataTable->addRow(new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => $this->getPageLabel($record, $isTitle), Piwik_Archive::INDEX_NB_ACTIONS => $actions))));
$this->totalTransitionsToFollowingActions += $actions;
}
}
$dataTables[$recordName] = $dataTable;
}
return $dataTables;
}
/**
* Get information about the following actions (following pages, outlinks, downloads)
*
* @param $idaction
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @return array(followingPages:Piwik_DataTable, outlinks:Piwik_DataTable, downloads:Piwik_DataTable)
*/
public function queryFollowingActions($idaction, Piwik_ArchiveProcessing_Day $archiveProcessing, $limitBeforeGrouping = false)
{
static $types = array(Piwik_Tracker_Action::TYPE_ACTION_URL => 'followingPages', Piwik_Tracker_Action::TYPE_OUTLINK => 'outlinks', Piwik_Tracker_Action::TYPE_DOWNLOAD => 'downloads');
$dimension = 'idaction_url';
$rankingQuery = new Piwik_RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping);
$rankingQuery->addLabelColumn(array('name', 'url_prefix'));
$rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($types));
$addSelect = 'log_action.name, log_action.url_prefix, log_action.type';
$where = '
log_link_visit_action.idaction_url_ref = ' . intval($idaction) . ' AND
log_link_visit_action.idaction_url != ' . intval($idaction);
$orderBy = '`' . Piwik_Archive::INDEX_NB_ACTIONS . '` DESC';
$metrics = array(Piwik_Archive::INDEX_NB_ACTIONS);
$data = $archiveProcessing->queryActionsByDimension(array($dimension), $where, $metrics, $orderBy, $rankingQuery, $dimension, $addSelect);
$dataTables = array();
foreach ($types as $type => $recordName) {
$dataTable = new Piwik_DataTable();
if (isset($data[$type])) {
foreach ($data[$type] as &$record) {
$dataTable->addRow(new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => $type == Piwik_Tracker_Action::TYPE_ACTION_URL ? Piwik_Tracker_Action::reconstructNormalizedUrl($record['name'], $record['url_prefix']) : $record['name'], Piwik_Archive::INDEX_NB_ACTIONS => intval($record[Piwik_Archive::INDEX_NB_ACTIONS])))));
}
}
$dataTables[$recordName] = $dataTable;
}
return $dataTables;
}
