/** * @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; }