/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @return void
*/
protected function archiveDayAggregate(Piwik_ArchiveProcessing_Day $archiveProcessing)
{
for ($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $i++) {
$keyField = "custom_var_k" . $i;
$valueField = "custom_var_v" . $i;
$dimensions = array($keyField, $valueField);
$where = "%s.{$keyField} != ''";
// Custom Vars names and values metrics for visits
$query = $archiveProcessing->queryVisitsByDimension($dimensions, $where);
while ($row = $query->fetch()) {
// Handle case custom var value is empty
$row[$valueField] = $this->cleanCustomVarValue($row[$valueField]);
// Aggregate
if (!isset($this->interestByCustomVariables[$row[$keyField]])) {
$this->interestByCustomVariables[$row[$keyField]] = $archiveProcessing->getNewInterestRow();
}
if (!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]])) {
$this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]] = $archiveProcessing->getNewInterestRow();
}
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariables[$row[$keyField]]);
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]]);
}
// Custom Vars names and values metrics for page views
$query = $archiveProcessing->queryActionsByDimension($dimensions, $where);
$onlyMetricsAvailableInActionsTable = true;
while ($row = $query->fetch()) {
// Handle case custom var value is empty
$row[$valueField] = $this->cleanCustomVarValue($row[$valueField]);
$label = $row[$valueField];
// Remove price tracked if it's zero or we if we are not currently tracking an ecommerce var
if (!in_array($row[$keyField], array('_pks', '_pkn', '_pkc'))) {
unset($row[Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED]);
}
// when custom variable value is a JSON array of categories
// possibly JSON value
$mustInsertCustomVariableValue = true;
if ($row[$keyField] == '_pkc' && $label[0] == '[' && $label[1] == '"') {
// In case categories were truncated, try closing the array
if (substr($label, -2) != '"]') {
$label .= '"]';
}
$decoded = @Piwik_Common::json_decode($label);
if (is_array($decoded)) {
$count = 0;
foreach ($decoded as $category) {
if (empty($category) || $count >= Piwik_Tracker_GoalManager::MAXIMUM_PRODUCT_CATEGORIES) {
continue;
}
if (!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$category])) {
$this->interestByCustomVariablesAndValue[$row[$keyField]][$category] = $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable);
}
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$category], $onlyMetricsAvailableInActionsTable);
$mustInsertCustomVariableValue = false;
$count++;
}
}
}
// end multi categories hack
if ($mustInsertCustomVariableValue) {
if (!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]])) {
$this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]] = $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable);
}
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]], $onlyMetricsAvailableInActionsTable);
}
// Do not report on Price viewed for the Custom Variable names
unset($row[Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED]);
// When tracking Custom Variables with scope=page we do not add up visits numbers
// as it is incorrect to sum visits this way
// for scope=visit this is allowed, since there is supposed to be one custom var value per custom variable name for a given visit
$doNotSumVisits = true;
if (!isset($this->interestByCustomVariables[$row[$keyField]])) {
$this->interestByCustomVariables[$row[$keyField]] = $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable, $doNotSumVisits);
}
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariables[$row[$keyField]], $onlyMetricsAvailableInActionsTable, $doNotSumVisits);
}
// Custom Vars names and values metrics for Goals
$query = $archiveProcessing->queryConversionsByDimension($dimensions, $where);
if ($query !== false) {
while ($row = $query->fetch()) {
// Handle case custom var value is empty
$row[$valueField] = $this->cleanCustomVarValue($row[$valueField]);
if (!isset($this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) {
$this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
}
if (!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) {
$this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
}
$archiveProcessing->updateGoalStats($row, $this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
$archiveProcessing->updateGoalStats($row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
}
}
}
$archiveProcessing->enrichConversionsByLabelArray($this->interestByCustomVariables);
$archiveProcessing->enrichConversionsByLabelArrayHasTwoLevels($this->interestByCustomVariablesAndValue);
// var_dump($this->interestByCustomVariables);
//var_dump($this->interestByCustomVariablesAndValue);
}
/**
* 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;
}
/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @return void
*/
protected function archiveDayAggregate(Piwik_ArchiveProcessing_Day $archiveProcessing)
{
for($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $i++ )
{
$keyField = "custom_var_k".$i;
$valueField = "custom_var_v".$i;
$dimensions = array($keyField, $valueField);
$where = "%s.$keyField != '' AND %s.$valueField != ''";
// Custom Vars names and values metrics for visits
$query = $archiveProcessing->queryVisitsByDimension($dimensions, $where);
while($row = $query->fetch() )
{
if(!isset($this->interestByCustomVariables[$row[$keyField]])) $this->interestByCustomVariables[$row[$keyField]]= $archiveProcessing->getNewInterestRow();
if(!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]])) $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]] = $archiveProcessing->getNewInterestRow();
$archiveProcessing->updateInterestStats( $row, $this->interestByCustomVariables[$row[$keyField]]);
$archiveProcessing->updateInterestStats( $row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]]);
}
// Custom Vars names and values metrics for page views
$query = $archiveProcessing->queryActionsByDimension($dimensions, $where);
$onlyMetricsAvailableInActionsTable = true;
while($row = $query->fetch() )
{
if(!isset($this->interestByCustomVariables[$row[$keyField]])) $this->interestByCustomVariables[$row[$keyField]]= $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable);
if(!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]])) $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]] = $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable);
$archiveProcessing->updateInterestStats( $row, $this->interestByCustomVariables[$row[$keyField]], $onlyMetricsAvailableInActionsTable);
$archiveProcessing->updateInterestStats( $row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]], $onlyMetricsAvailableInActionsTable);
}
// Custom Vars names and values metrics for Goals
$query = $archiveProcessing->queryConversionsByDimension($dimensions, $where);
if($query !== false)
{
while($row = $query->fetch() )
{
if(!isset($this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) $this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
if(!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
$archiveProcessing->updateGoalStats( $row, $this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
$archiveProcessing->updateGoalStats( $row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
}
}
}
$archiveProcessing->enrichConversionsByLabelArray($this->interestByCustomVariables);
$archiveProcessing->enrichConversionsByLabelArrayHasTwoLevels($this->interestByCustomVariablesAndValue);
// var_dump($this->interestByCustomVariables);
// var_dump($this->interestByCustomVariablesAndValue);
}
/**
* Ordnet f�r den gegebenen Archivzeitraum allen Digitalisat-URLs Kollektionen zu,
* und archiviert diese.
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @return Piwik_DataTable
*/
private function getCollectionData($archiveProcessing)
{
Piwik_cdebug::clog('getCollectionData');
$collections = array();
$query = $archiveProcessing->queryActionsByDimension('idaction_url');
while ($row = $query->fetch()) {
$url = Piwik_FetchOne("SELECT name FROM " . Piwik_Common::prefixTable("log_action") . " WHERE idaction = ?", $row["label"]);
$idcollections = $this->getCollectionsForURL($url);
if (!is_array($idcollections) || count($idcollections) < 1) {
continue;
}
foreach ($idcollections as $c) {
if (!array_key_exists($c, $collections)) {
$collections[$c] = array(self::$valuefield => 1);
} else {
$collections[$c][self::$valuefield]++;
}
}
}
return $collections;
}
