diff options
| author | mattab <matthieu.aubry@gmail.com> | 2013-06-10 13:00:42 +0400 |
|---|---|---|
| committer | mattab <matthieu.aubry@gmail.com> | 2013-06-16 12:10:27 +0400 |
| commit | c17139710a43b55b5f48377e6281db6ae602957c (patch) | |
| tree | 42fda2ab71074d8d273fab1c444a1fec53957df9 | |
| parent | 7fdd382b2880f291752e33ec6f6f44a5dbb21dec (diff) | |
refactoring / improvements of Archiveprocessing (in progress)
such as removing duplicate code and a lot of refactoring, the code is now much more readable!
26 files changed, 956 insertions, 1446 deletions
diff --git a/core/Archive.php b/core/Archive.php index a242b456da..f5856d4a99 100644 --- a/core/Archive.php +++ b/core/Archive.php @@ -678,9 +678,10 @@ class Piwik_Archive Piwik::log("Archive $archiveDesc skipped, archive is after today."); continue; } - + // prepare the ArchiveProcessing instance - $processing = $this->getArchiveProcessingInstance($period); + $processing = Piwik_ArchiveProcessing::factory($period->getLabel()); + $processing->setSite($site); $processing->setPeriod($period); $processing->setSegment($this->params->getSegment()); @@ -754,23 +755,7 @@ class Piwik_Archive { return Piwik_ArchiveProcessing::getDoneStringFlagFor($this->params->getSegment(), $this->getPeriodLabel(), $plugin); } - - /** - * Returns an ArchiveProcessing instance that should be used for a specific - * period. - * - * @param Piwik_Period $period - * @return Piwik_ArchiveProcessing - */ - private function getArchiveProcessingInstance($period) - { - $label = $period->getLabel(); - if (!isset($this->processingCache[$label])) { - $this->processingCache[$label] = Piwik_ArchiveProcessing::factory($label); - } - return $this->processingCache[$label]; - } - + private function getPeriodLabel() { $periods = $this->params->getPeriods(); diff --git a/core/ArchiveProcessing.php b/core/ArchiveProcessing.php index 738bc13fa0..5785458e18 100644 --- a/core/ArchiveProcessing.php +++ b/core/ArchiveProcessing.php @@ -532,6 +532,13 @@ abstract class Piwik_ArchiveProcessing return $this->insertRecord($name, $value); } + public function insertNumericRecords($numericRecords) + { + foreach ($numericRecords as $name => $value) { + $this->insertNumericRecord($name, $value); + } + } + /** * @param string $name * @param string|array $values @@ -547,6 +554,7 @@ abstract class Piwik_ArchiveProcessing // but for the child table of 'Google' which has the ID = 9 the name would be 'referer_search_engine_9' $newName = $name; if ($id != 0) { + //FIXMEA: refactor $newName = $name . '_' . $id; } @@ -630,7 +638,7 @@ abstract class Piwik_ArchiveProcessing protected function insertRecord($name, $value) { // We choose not to record records with a value of 0 - if ($value === 0) { + if ($value == 0) { return; } $tableName = $this->getTableNameToInsert($value); @@ -831,7 +839,7 @@ abstract class Piwik_ArchiveProcessing protected function isProcessingEnabled() { return $this->shouldProcessReportsAllPlugins($this->getSegment(), $this->getPeriod()->getLabel()) - || ($this->isVisitsSummaryRequested()); + || $this->isVisitsSummaryRequested(); } /** diff --git a/core/ArchiveProcessing/Day.php b/core/ArchiveProcessing/Day.php index 41a3e3c556..b662ca54d0 100644 --- a/core/ArchiveProcessing/Day.php +++ b/core/ArchiveProcessing/Day.php @@ -21,269 +21,36 @@ */ class Piwik_ArchiveProcessing_Day extends Piwik_ArchiveProcessing { - public function getDb() - { - return Zend_Registry::get('db'); - } - - /** - * Main method to process logs for a day. The only logic done here is computing the number of visits, actions, etc. - * All the other reports are computed inside plugins listening to the event 'ArchiveProcessing_Day.compute'. - * See some of the plugins for an example eg. 'Provider' - */ - protected function compute() - { - if (!$this->isThereSomeVisits()) { - return; - } - Piwik_PostEvent('ArchiveProcessing_Day.compute', $this); - } - - /** - * Returns true if there are logs for the current archive. - * - * If the current archive is for a specific plugin (for example, Referers), - * (for example when a Segment is defined and the Keywords report is requested) - * Then the function will create the Archive for the Core metrics 'VisitsSummary' which will in turn process the number of visits - * - * If there is no specified segment, the SQL query will always run. - * - * @return bool|null - */ - public function isThereSomeVisits() - { - if (!is_null($this->isThereSomeVisits)) { - if ($this->isThereSomeVisits && is_null($this->nb_visits)) { - debug_print_backtrace(); - exit; - } - return $this->isThereSomeVisits; - } - - if (!$this->isProcessingEnabled()) { - return $this->redirectRequestToVisitsSummary(); - } - - $select = "count(distinct log_visit.idvisitor) as nb_uniq_visitors, - count(*) as nb_visits, - sum(log_visit.visit_total_actions) as nb_actions, - max(log_visit.visit_total_actions) as max_actions, - sum(log_visit.visit_total_time) as sum_visit_length, - sum(case log_visit.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) as bounce_count, - sum(case log_visit.visit_goal_converted when 1 then 1 else 0 end) as nb_visits_converted - "; - $from = "log_visit"; - $where = "log_visit.visit_last_action_time >= ? - AND log_visit.visit_last_action_time <= ? - AND log_visit.idsite = ? - "; - - $bind = array($this->getStartDatetimeUTC(), $this->getEndDatetimeUTC(), $this->getSite()->getId()); - $query = $this->getSegment()->getSelectQuery($select, $from, $where, $bind); - - $bind = $query['bind']; - $sql = $query['sql']; - - $data = $this->getDb()->fetchRow($sql, $bind); - - // no visits found - if (!is_array($data) || $data['nb_visits'] == 0) { - return $this->isThereSomeVisits = false; - } - - // visits found: set attribtues - foreach ($data as $name => $value) { - $this->insertNumericRecord($name, $value); - } - - $this->setNumberOfVisits($data['nb_visits']); - $this->setNumberOfVisitsConverted($data['nb_visits_converted']); - - return $this->isThereSomeVisits = true; - } - - /** - * If a segment is specified but a plugin other than 'VisitsSummary' is being requested, - * we create an archive for processing VisitsSummary Core Metrics, which will in turn - * execute the query above (in isThereSomeVisits) - * - * @return bool|null - */ - private function redirectRequestToVisitsSummary() - { - $archive = $this->makeNewArchive(); - $nbVisits = $archive->getNumeric('nb_visits'); - $this->isThereSomeVisits = $nbVisits > 0; - - if ($this->isThereSomeVisits) { - $nbVisitsConverted = $archive->getNumeric('nb_visits_converted'); - $this->setNumberOfVisits($nbVisits); - $this->setNumberOfVisitsConverted($nbVisitsConverted); - } - - return $this->isThereSomeVisits; - } - - /** - * Converts a database SELECT result into a whole DataTable with two columns and as many - * rows as elements in $row. - * - * The key of each element in $row is used as the value of the first column, and the - * value of each element is used as the second column. - * - * NOTE: $selectAsPrefix can be used to make sure only SOME of the data in $row is used. - * - * @param array $row The database row to convert. - * @param mixed $labelCount The label to use for the second column of the DataTable result. - * @param string $selectAsPrefix A string that identifies which elements of $row to use - * in the result. Every key of $row that starts with this - * value is used. - * @return Piwik_DataTable - */ - public function getSimpleDataTableFromRow($row, $labelCount, $selectAsPrefix = '') - { - // the labels in $row can have prefixes that need to be removed before creating a table - $cleanRow = array(); + const FIELDS_SEPARATOR = ", \n\t\t\t"; + const LOG_CONVERSION_TABLE = "log_conversion"; + const REVENUE_SUBTOTAL_FIELD = 'revenue_subtotal'; + const REVENUE_TAX_FIELD = 'revenue_tax'; + const REVENUE_SHIPPING_FIELD = 'revenue_shipping'; + const REVENUE_DISCOUNT_FIELD = 'revenue_discount'; + const TOTAL_REVENUE_FIELD = 'revenue'; + const ITEMS_COUNT_FIELD = "items"; - foreach ($row as $label => $count) { - if (empty($selectAsPrefix) || strpos($label, $selectAsPrefix) === 0) { - $cleanLabel = substr($label, strlen($selectAsPrefix)); + const IDGOAL_FIELD = 'idgoal'; - $cleanRow[$cleanLabel] = array($labelCount => $count); - } - } + const CONVERSION_DATETIME_FIELD = "server_time"; + const ACTION_DATETIME_FIELD = "server_time"; - $table = new Piwik_DataTable(); - $table->addRowsFromArrayWithIndexLabel($cleanRow); - return $table; - } + const VISIT_DATETIME_FIELD = 'visit_last_action_time'; + const LOG_ACTIONS_TABLE = 'log_link_visit_action'; - /** - * Helper function that returns a DataTable containing the $select fields / value pairs. - * IMPORTANT: The $select must return only one row!! - * - * Example $select = "count(distinct( config_os )) as countDistinctOs, - * sum( config_flash ) / count(distinct(idvisit)) as percentFlash " - * $labelCount = "test_column_name" - * will return a dataTable that looks like - * label test_column_name - * CountDistinctOs 9 - * PercentFlash 0.5676 - * - * - * @param string $select - * @param string $labelCount - * @return Piwik_DataTable - */ - public function getSimpleDataTableFromSelect($select, $labelCount) - { - $data = $this->queryVisitsSimple($select); - return $this->getSimpleDataTableFromRow($data, $labelCount); - } - - /** - * Performs a simple query on the log_visit table within the time range this archive - * represents. - * - * @param string $select The SELECT clause. - * @param string|bool $orderBy The ORDER BY clause (without the 'ORDER BY' part). Set to - * false to specify no ORDER BY. - * @param array|bool $groupByCols An array of column names to group by. Set to false to - * specify no GROUP BY. - * @param bool $oneResultRow Whether only one row is expected or not. If set to true, - * this function returns one row, if false, an array of rows. - * @return array - */ - public function queryVisitsSimple($select, $orderBy = false, $groupByCols = false, $oneResultRow = true) - { - $from = "log_visit"; - $where = "log_visit.visit_last_action_time >= ? - AND log_visit.visit_last_action_time <= ? - AND log_visit.idsite = ?"; - - $groupBy = false; - if ($groupByCols and !empty($groupByCols)) { - $groupBy = implode(',', $groupByCols); - } - - $bind = array($this->getStartDatetimeUTC(), $this->getEndDatetimeUTC(), $this->getSite()->getId()); - - $query = $this->getSegment()->getSelectQuery($select, $from, $where, $bind, $orderBy, $groupBy); + const LOG_VISIT_TABLE = 'log_visit'; - if ($oneResultRow) { - return $this->getDb()->fetchRow($query['sql'], $query['bind']); - } else { - return $this->getDb()->fetchAll($query['sql'], $query['bind']); - } - } - - /** - * Returns the actions by the given dimension - * - * - The basic use case is to use $label and optionally $where. - * - If you want to apply a limit and group the others, use $orderBy to sort the way you - * want the limit to be applied and pass a pre-configured instance of Piwik_RankingQuery. - * The ranking query instance has to have a limit and at least one label column. - * See Piwik_RankingQuery::setLimit() and Piwik_RankingQuery::addLabelColumn(). - * If $rankingQuery is set, the return value is the array returned by - * Piwik_RankingQuery::execute(). - * - By default, the method only queries log_link_visit_action. If you need data from - * log_action (e.g. to partition the result from the ranking query into the different - * action types), use $joinLogActionOnColumn and $addSelect to join log_action and select - * the column you need from log_action. - * - * - * @param array|string $label the dimensions(s) you're interested in - * @param string $where 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 - * @param Piwik_RankingQuery $rankingQuery pre-configured ranking query instance - * @param bool|string $joinLogActionOnColumn column from log_link_visit_action that - * log_action should be joined on. - * can be an array to join multiple times. - * @param bool|string $addSelect additional select clause - * @return mixed - */ - public function queryActionsByDimension($label, $where = '', $metrics = false, $orderBy = false, - $rankingQuery = null, $joinLogActionOnColumn = false, $addSelect = false) + public function queryActionsByDimension($dimensions, $where = '', $additionalSelects = array(), $metrics = false, $rankingQuery = null, $joinLogActionOnColumn = false) { - if (is_array($label)) { - $label2 = $label; - foreach ($label2 as &$field) { - $field = 'log_link_visit_action.' . $field; - } - $groupBy = implode(", ", $label2); - foreach ($label2 as $id => &$field) { - $field = "$field AS " . $label[$id]; - } - $select = implode(", ", $label2); + $tableName = self::LOG_ACTIONS_TABLE; + $availableMetrics = $this->getActionsMetricFields(); - // IF we query Custom Variables scope "page" either: Product SKU, Product Name, - // then we also query the "Product page view" price which was possibly recorded. - if (in_array(reset($label), array('custom_var_k3', 'custom_var_k4', 'custom_var_k5'))) { - $select .= ", " . self::getSqlRevenue("AVG(log_link_visit_action.custom_var_v2)") . " as `" . Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED . "`"; - } - } else { - $select = $label . " AS label "; - $groupBy = 'label'; - } - - if (!empty($where)) { - $where = sprintf($where, "log_link_visit_action", "log_link_visit_action"); - $where = ' AND ' . $where; - } - - $pre = ", \n\t\t\t"; - if (!$metrics || in_array(Piwik_Archive::INDEX_NB_VISITS, $metrics)) - $select .= $pre . "count(distinct log_link_visit_action.idvisit) as `" . Piwik_Archive::INDEX_NB_VISITS . "`"; - if (!$metrics || in_array(Piwik_Archive::INDEX_NB_UNIQ_VISITORS, $metrics)) - $select .= $pre . "count(distinct log_link_visit_action.idvisitor) as `" . Piwik_Archive::INDEX_NB_UNIQ_VISITORS . "`"; - if (!$metrics || in_array(Piwik_Archive::INDEX_NB_ACTIONS, $metrics)) - $select .= $pre . "count(*) as `" . Piwik_Archive::INDEX_NB_ACTIONS . "`"; - - $from = "log_link_visit_action"; + $select = $this->getSelectStatement($dimensions, $tableName, $additionalSelects, $availableMetrics, $metrics); + $from = array($tableName); + $where = $this->getWhereStatement($tableName, self::ACTION_DATETIME_FIELD, $where); + $groupBy = $this->getGroupByStatement($dimensions, $tableName); + $orderBy = false; if ($joinLogActionOnColumn !== false) { $multiJoin = is_array($joinLogActionOnColumn); @@ -291,12 +58,10 @@ class Piwik_ArchiveProcessing_Day extends Piwik_ArchiveProcessing $joinLogActionOnColumn = array($joinLogActionOnColumn); } - $from = array($from); - foreach ($joinLogActionOnColumn as $i => $joinColumn) { $tableAlias = 'log_action' . ($multiJoin ? $i + 1 : ''); if (strpos($joinColumn, ' ') === false) { - $joinOn = $tableAlias . '.idaction = log_link_visit_action.' . $joinColumn; + $joinOn = $tableAlias . '.idaction = ' . $tableName . '.' . $joinColumn; } else { // more complex join column like IF(...) $joinOn = $tableAlias . '.idaction = ' . $joinColumn; @@ -309,32 +74,16 @@ class Piwik_ArchiveProcessing_Day extends Piwik_ArchiveProcessing } } - if ($addSelect !== false) { - $select .= ', ' . $addSelect; + if ($rankingQuery) { + $orderBy = '`' . Piwik_Archive::INDEX_NB_ACTIONS . '` DESC'; } - $where = "log_link_visit_action.server_time >= ? - AND log_link_visit_action.server_time <= ? - AND log_link_visit_action.idsite = ? - $where"; - - $bind = array($this->getStartDatetimeUTC(), $this->getEndDatetimeUTC(), $this->getSite()->getId()); - - $query = $this->getSegment()->getSelectQuery($select, $from, $where, $bind, $orderBy, $groupBy); + $query = $this->query($select, $from, $where, $groupBy, $orderBy); if ($rankingQuery !== null) { - $sumColumns = array( - Piwik_Archive::INDEX_NB_UNIQ_VISITORS, - Piwik_Archive::INDEX_NB_VISITS, - Piwik_Archive::INDEX_NB_ACTIONS - ); + $sumColumns = array_keys($availableMetrics); if ($metrics) { - foreach ($sumColumns as $i => $column) { - if (!in_array($column, $metrics)) { - unset($sumColumns[$i]); - } - } - $sumColumns = array_values($sumColumns); + $sumColumns = array_intersect($sumColumns, $metrics); } $rankingQuery->addColumn($sumColumns, 'sum'); return $rankingQuery->execute($query['sql'], $query['bind']); @@ -343,163 +92,77 @@ class Piwik_ArchiveProcessing_Day extends Piwik_ArchiveProcessing return $this->getDb()->query($query['sql'], $query['bind']); } - /** - * 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 bool|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) + protected function getActionsMetricFields() { - 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 >= ? - AND log_visit.visit_last_action_time <= ? - AND log_visit.idsite = ? - $where"; + return $availableMetrics = array( + Piwik_Archive::INDEX_NB_VISITS => "count(distinct " . self::LOG_ACTIONS_TABLE . ".idvisit)", + Piwik_Archive::INDEX_NB_UNIQ_VISITORS => "count(distinct " . self::LOG_ACTIONS_TABLE . ".idvisitor)", + Piwik_Archive::INDEX_NB_ACTIONS => "count(*)", + ); + } + protected function query($select, $from, $where, $groupBy, $orderBy) + { $bind = array($this->getStartDatetimeUTC(), $this->getEndDatetimeUTC(), $this->getSite()->getId()); $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->getDb()->query($query['sql'], $query['bind']); + return $query; } /** * @see queryVisitsByDimension() Similar to this function, - * but queries metrics for the requested dimensions, + * but queries metrics for the requested dimensionRecord, * for each Goal conversion * - * @param string|array $label + * @param string|array $dimensions * @param string $where - * @param array $aggregateLabels + * @param array $additionalSelects * @return PDOStatement */ - public function queryConversionsByDimension($label, $where = '', $aggregateLabels = array()) + public function queryConversionsByDimension($dimensions = array(), $where = false, $additionalSelects = array()) { - if (empty($label)) { - $select = ""; - $groupBy = ""; - } elseif (is_array($label)) { - $groupBy = "log_conversion." . implode(", log_conversion.", $label); - foreach ($label as &$field) { - $field = 'log_conversion.' . $field . ' AS ' . $field; - } - $select = implode(", ", $label) . ", "; - } else { - $select = $label . " AS label, "; - $groupBy = 'label'; - } - if (!empty($aggregateLabels)) { - $select .= implode(", ", $aggregateLabels) . ", "; - } - if (!empty($where)) { - $where = sprintf($where, "log_conversion", "log_conversion"); - $where = ' AND ' . $where; - } - - $select .= self::getSqlRevenue('SUM(log_conversion.revenue_subtotal)') . " as `" . Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_SUBTOTAL . "`," . - self::getSqlRevenue('SUM(log_conversion.revenue_tax)') . " as `" . Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_TAX . "`," . - self::getSqlRevenue('SUM(log_conversion.revenue_shipping)') . " as `" . Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_SHIPPING . "`," . - self::getSqlRevenue('SUM(log_conversion.revenue_discount)') . " as `" . Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_DISCOUNT . "`," . - "SUM(log_conversion.items) as `" . Piwik_Archive::INDEX_GOAL_ECOMMERCE_ITEMS . "`, "; - - $groupBy = !empty($groupBy) ? ", $groupBy" : ''; - - $select = "$select - log_conversion.idgoal, - count(*) as `" . Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS . "`, - " . self::getSqlRevenue('SUM(log_conversion.revenue)') . " as `" . Piwik_Archive::INDEX_GOAL_REVENUE . "`, - count(distinct log_conversion.idvisit) as `" . Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED . "`"; + $dimensions = array_merge( array(self::IDGOAL_FIELD), $dimensions ); + $availableMetrics = $this->getConversionsMetricFields(); + $tableName = self::LOG_CONVERSION_TABLE; - $from = "log_conversion"; + $select = $this->getSelectStatement($dimensions, $tableName, $additionalSelects, $availableMetrics); - $where = "log_conversion.server_time >= ? - AND log_conversion.server_time <= ? - AND log_conversion.idsite = ? - $where"; - - $groupBy = "log_conversion.idgoal $groupBy"; + $from = array($tableName); + $where = $this->getWhereStatement($tableName, self::CONVERSION_DATETIME_FIELD, $where); + $groupBy = $this->getGroupByStatement($dimensions, $tableName); + $orderBy = false; + $query = $this->query($select, $from, $where, $groupBy, $orderBy); + return $this->getDb()->query($query['sql'], $query['bind']); + } - $bind = array($this->getStartDatetimeUTC(), $this->getEndDatetimeUTC(), $this->getSite()->getId()); + protected function getSelectStatement($dimensions, $tableName, $additionalSelects, $availableMetrics, $requestedMetrics = false) + { + $selects = array_merge( + $this->getSelectDimensions($dimensions, $tableName), + $this->getSelectsMetrics($availableMetrics, $requestedMetrics), + !empty($additionalSelects) ? $additionalSelects : array() + ); + $select = implode(self::FIELDS_SEPARATOR, $selects); + return $select; + } - $query = $this->getSegment()->getSelectQuery($select, $from, $where, $bind, $orderBy = false, $groupBy); + static public function getConversionsMetricFields() + { + return array( + Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS => "count(*)", + Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED => "count(distinct " . self::LOG_CONVERSION_TABLE . ".idvisit)", + Piwik_Archive::INDEX_GOAL_REVENUE => self::getSqlConversionRevenueSum(self::TOTAL_REVENUE_FIELD), + Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_SUBTOTAL => self::getSqlConversionRevenueSum(self::REVENUE_SUBTOTAL_FIELD), + Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_TAX => self::getSqlConversionRevenueSum(self::REVENUE_TAX_FIELD), + Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_SHIPPING => self::getSqlConversionRevenueSum(self::REVENUE_SHIPPING_FIELD), + Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_DISCOUNT => self::getSqlConversionRevenueSum(self::REVENUE_DISCOUNT_FIELD), + Piwik_Archive::INDEX_GOAL_ECOMMERCE_ITEMS => "SUM(" . self::LOG_CONVERSION_TABLE . "." . self::ITEMS_COUNT_FIELD . ")", + ); + } - return $this->getDb()->query($query['sql'], $query['bind']); + static public function getSqlConversionRevenueSum($field) + { + return self::getSqlRevenue('SUM(' . self::LOG_CONVERSION_TABLE . '.' . $field . ')'); } /** @@ -526,7 +189,9 @@ class Piwik_ArchiveProcessing_Day extends Piwik_ArchiveProcessing AND idsite = ? AND deleted = 0 GROUP BY ecommerceType, $field - ORDER BY NULL"; + ORDER BY null"; + // Segment not supported yet + // $query = $this->query($select, $from, $where, $groupBy, $orderBy); $bind = array($this->getStartDatetimeUTC(), $this->getEndDatetimeUTC(), @@ -550,11 +215,19 @@ class Piwik_ArchiveProcessing_Day extends Piwik_ArchiveProcessing * @param array $array * @return Piwik_DataTable */ - static public function getDataTableFromArray($array) + static public function getDataTableFromDataArray(Piwik_DataArray $array) { - $table = new Piwik_DataTable(); - $table->addRowsFromArrayWithIndexLabel($array); - return $table; + $dataArray = $array->getDataArray(); + $dataArrayTwoLevels = $array->getDataArrayWithTwoLevels(); + + $subtableByLabel = null; + if (!empty($dataArrayTwoLevels)) { + $subtableByLabel = array(); + foreach ($dataArrayTwoLevels as $label => $subTable) { + $subtableByLabel[$label] = Piwik_DataTable::makeFromIndexedArray($subTable); + } + } + return Piwik_DataTable::makeFromIndexedArray($dataArray, $subtableByLabel); } /** @@ -579,7 +252,7 @@ class Piwik_ArchiveProcessing_Day extends Piwik_ArchiveProcessing * - sum of the visits' length in sec * - count of bouncing visits (visits with one page view) * - * For example if $label = 'config_os' it will return the statistics for every distinct Operating systems + * For example if $dimension = 'config_os' it will return the statistics for every distinct Operating systems * The returned array will have a row per distinct operating systems, * and a column per stat (nb of visits, max actions, etc) * @@ -588,279 +261,250 @@ class Piwik_ArchiveProcessing_Day extends Piwik_ArchiveProcessing * Windows XP 12 ... * Mac OS 15 36 ... * - * @param string $label Table log_visit field name to be use to compute common stats - * @return array + * @param string $dimension Table log_visit field name to be use to compute common stats + * @return Piwik_DataArray */ - public function getMetricsForLabel($label) + public function getMetricsForDimension($dimension) { - $query = $this->queryVisitsByDimension($label); - $metrics = array(); + if(!is_array($dimension)) { + $dimension = array($dimension); + } + if(count($dimension) == 1) { + $dimension = array("label" => reset($dimension)); + } + $query = $this->queryVisitsByDimension($dimension); + $metrics = new Piwik_DataArray(); while ($row = $query->fetch()) { - if (!isset($metrics[$row['label']])) { - $metrics[$row['label']] = $this->makeEmptyRow(); - } - $this->sumMetrics($row, $metrics[$row['label']]); + $metrics->sumMetricsVisits($row["label"], $row); } return $metrics; } - /** - * Helper function that returns the multiple serialized DataTable of the given PHP array. - * The DataTable here associates a subtable to every row of the level 0 array. - * This is used for example for search engines. - * Every search engine (level 0) has a subtable containing the keywords. - * - * The $arrayLevel0 must have the format - * Example: array ( - * // Yahoo.com => array( kwd1 => stats, kwd2 => stats ) - * LABEL => array(col1 => X, col2 => Y), - * LABEL2 => array(col1 => X, col2 => Y), - * ) + * Returns true if there are logs for the current archive. * - * The $subArrayLevel1ByKey must have the format - * Example: array( - * // Yahoo.com => array( stats ) - * LABEL => #Piwik_DataTable_ForLABEL, - * LABEL2 => #Piwik_DataTable_ForLABEL2, - * ) + * If the current archive is for a specific plugin (for example, Referers), + * (for example when a Segment is defined and the Keywords report is requested) + * Then the function will create the Archive for the Core metrics 'VisitsSummary' which will in turn process the number of visits * + * If there is no specified segment, the SQL query will always run. * - * @param array $arrayLevel0 - * @param array $subArrayLevel1ByKey Array of Piwik_DataTable - * @return array Array with N elements: the strings of the datatable serialized + * @return bool|null */ - public function getDataTableWithSubtablesFromArraysIndexedByLabel($arrayLevel0, $subArrayLevel1ByKey) + public function isThereSomeVisits() { - $parentTableLevel0 = new Piwik_DataTable(); + if (!is_null($this->isThereSomeVisits)) { + return $this->isThereSomeVisits; + } - $tablesByLabel = array(); - foreach ($arrayLevel0 as $label => $aAllRowsForThisLabel) { - $table = new Piwik_DataTable(); - $table->addRowsFromArrayWithIndexLabel($aAllRowsForThisLabel); - $tablesByLabel[$label] = $table; + if (!$this->isProcessingEnabled()) { + return $this->makeArchiveToCheckForVisits(); + } + + $query = $this->queryVisitsByDimension(); + $data = $query->fetch(); + + // no visits found + if (!is_array($data) || $data[Piwik_Archive::INDEX_NB_VISITS] == 0) { + return $this->isThereSomeVisits = false; + } + $metrics = array(); + foreach($data as $metricId => $value) { + $readableMetric = Piwik_Archive::$mappingFromIdToName[$metricId]; + $metrics[$readableMetric] = $value; } - $parentTableLevel0->addRowsFromArrayWithIndexLabel($subArrayLevel1ByKey, $tablesByLabel); + $this->insertNumericRecords($metrics); - return $parentTableLevel0; + $this->setNumberOfVisits($data[Piwik_Archive::INDEX_NB_VISITS]); + $this->setNumberOfVisitsConverted($data[Piwik_Archive::INDEX_NB_VISITS_CONVERTED]); + return $this->isThereSomeVisits = true; } /** - * Returns an empty row containing default metrics + * Query visits by dimension * - * @return array + * @param array|string $dimensions 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 bool|string $where Additional condition for WHERE clause + * @param bool|string $additionalSelects Additional SELECT 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|\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(). + * + * @return mixed */ - public function makeEmptyRow() + public function queryVisitsByDimension(array $dimensions = array(), $where = false, array $additionalSelects = array(), $metrics = false, $rankingQuery = false) { - return array(Piwik_Archive::INDEX_NB_UNIQ_VISITORS => 0, - Piwik_Archive::INDEX_NB_VISITS => 0, - Piwik_Archive::INDEX_NB_ACTIONS => 0, - Piwik_Archive::INDEX_MAX_ACTIONS => 0, - Piwik_Archive::INDEX_SUM_VISIT_LENGTH => 0, - Piwik_Archive::INDEX_BOUNCE_COUNT => 0, - Piwik_Archive::INDEX_NB_VISITS_CONVERTED => 0, - ); - } + $tableName = self::LOG_VISIT_TABLE; + $availableMetrics = $this->getVisitsMetricFields(); + $select = $this->getSelectStatement($dimensions, $tableName, $additionalSelects, $availableMetrics, $metrics); + $from = array($tableName); + $where = $this->getWhereStatement($tableName, self::VISIT_DATETIME_FIELD, $where); + $groupBy = $this->getGroupByStatement($dimensions, $tableName); + $orderBy = false; - /** - * Returns an empty row tracking only Actions - * - * @return array - */ - public function makeEmptyActionRow() + if ($rankingQuery) { + $orderBy = '`' . Piwik_Archive::INDEX_NB_VISITS . '` DESC'; + } + $query = $this->query($select, $from, $where, $groupBy, $orderBy); + + if ($rankingQuery) { + unset($availableMetrics[Piwik_Archive::INDEX_MAX_ACTIONS]); + $sumColumns = array_keys($availableMetrics); + if ($metrics) { + $sumColumns = array_intersect($sumColumns, $metrics); + } + $rankingQuery->addColumn($sumColumns, 'sum'); + if ($this->isMetricRequested(Piwik_Archive::INDEX_MAX_ACTIONS, $metrics)) { + $rankingQuery->addColumn(Piwik_Archive::INDEX_MAX_ACTIONS, 'max'); + } + return $rankingQuery->execute($query['sql'], $query['bind']); + } + + return $this->getDb()->query($query['sql'], $query['bind']); + } + + protected function getVisitsMetricFields() { return array( - Piwik_Archive::INDEX_NB_UNIQ_VISITORS => 0, - Piwik_Archive::INDEX_NB_VISITS => 0, - Piwik_Archive::INDEX_NB_ACTIONS => 0, + Piwik_Archive::INDEX_NB_UNIQ_VISITORS => "count(distinct " . self::LOG_VISIT_TABLE . ".idvisitor)", + Piwik_Archive::INDEX_NB_VISITS => "count(*)", + Piwik_Archive::INDEX_NB_ACTIONS => "sum(" . self::LOG_VISIT_TABLE . ".visit_total_actions)", + Piwik_Archive::INDEX_MAX_ACTIONS => "max(" . self::LOG_VISIT_TABLE . ".visit_total_actions)", + Piwik_Archive::INDEX_SUM_VISIT_LENGTH => "sum(" . self::LOG_VISIT_TABLE . ".visit_total_time)", + Piwik_Archive::INDEX_BOUNCE_COUNT => "sum(case " . self::LOG_VISIT_TABLE . ".visit_total_actions when 1 then 1 when 0 then 1 else 0 end)", + Piwik_Archive::INDEX_NB_VISITS_CONVERTED => "sum(case " . self::LOG_VISIT_TABLE . ".visit_goal_converted when 1 then 1 else 0 end)", ); } - /** - * @param $idGoal - * @return array - */ - public function makeEmptyGoalRow($idGoal) + protected function getWhereStatement($tableName, $datetimeField, $extraWhere = false) { - if ($idGoal > Piwik_Tracker_GoalManager::IDGOAL_ORDER) { - return array(Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS => 0, - Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED => 0, - Piwik_Archive::INDEX_GOAL_REVENUE => 0, - ); - } - if ($idGoal == Piwik_Tracker_GoalManager::IDGOAL_ORDER) { - return array(Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS => 0, - Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED => 0, - Piwik_Archive::INDEX_GOAL_REVENUE => 0, - Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_SUBTOTAL => 0, - Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_TAX => 0, - Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_SHIPPING => 0, - Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_DISCOUNT => 0, - Piwik_Archive::INDEX_GOAL_ECOMMERCE_ITEMS => 0, - ); + $where = "$tableName.$datetimeField >= ? + AND $tableName.$datetimeField <= ? + AND $tableName.idsite = ?"; + if (!empty($extraWhere)) { + $extraWhere = sprintf($extraWhere, $tableName, $tableName); + $where .= ' AND ' . $extraWhere; } - // idGoal == Piwik_Tracker_GoalManager::IDGOAL_CART - return array(Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS => 0, - Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED => 0, - Piwik_Archive::INDEX_GOAL_REVENUE => 0, - Piwik_Archive::INDEX_GOAL_ECOMMERCE_ITEMS => 0, - ); + return $where; } - /** - * Returns a Piwik_DataTable_Row containing default values for common stat, - * plus a column 'label' with the value $label - * - * @param string $label - * @return Piwik_DataTable_Row - */ - public function makeEmptyRowLabeled($label) + + protected function getSelectsMetrics($metricsAvailable, $metricsRequested = false) { - return new Piwik_DataTable_Row( - array( - Piwik_DataTable_Row::COLUMNS => array('label' => $label) - + $this->makeEmptyRow() - ) - ); + $selects = array(); + foreach ($metricsAvailable as $metricId => $statement) { + if ($this->isMetricRequested($metricId, $metricsRequested)) { + $selects[] = $statement . " as `" . $metricId . "`"; + } + } + return $selects; } /** - * Adds the given row $newRowToAdd to the existing $oldRowToUpdate passed by reference - * The rows are php arrays Name => value - * - * @param array $newRowToAdd - * @param array $oldRowToUpdate - * @param bool $onlyMetricsAvailableInActionsTable - * @param bool $doNotSumVisits - * - * @return void + * @param $metricId + * @param $metricsRequested + * @return bool */ - public function sumMetrics($newRowToAdd, &$oldRowToUpdate, $onlyMetricsAvailableInActionsTable = false) + protected function isMetricRequested($metricId, $metricsRequested) { - // Pre 1.2 format: string indexed rows are returned from the DB - // Left here for Backward compatibility with plugins doing custom SQL queries using these metrics as string - if (!isset($newRowToAdd[Piwik_Archive::INDEX_NB_VISITS])) { - $oldRowToUpdate[Piwik_Archive::INDEX_NB_VISITS] += $newRowToAdd['nb_visits']; - $oldRowToUpdate[Piwik_Archive::INDEX_NB_ACTIONS] += $newRowToAdd['nb_actions']; - $oldRowToUpdate[Piwik_Archive::INDEX_NB_UNIQ_VISITORS] += $newRowToAdd['nb_uniq_visitors']; - if ($onlyMetricsAvailableInActionsTable) { - return; - } - $oldRowToUpdate[Piwik_Archive::INDEX_MAX_ACTIONS] = (float)max($newRowToAdd['max_actions'], $oldRowToUpdate[Piwik_Archive::INDEX_MAX_ACTIONS]); - $oldRowToUpdate[Piwik_Archive::INDEX_SUM_VISIT_LENGTH] += $newRowToAdd['sum_visit_length']; - $oldRowToUpdate[Piwik_Archive::INDEX_BOUNCE_COUNT] += $newRowToAdd['bounce_count']; - $oldRowToUpdate[Piwik_Archive::INDEX_NB_VISITS_CONVERTED] += $newRowToAdd['nb_visits_converted']; - return; - } - - $oldRowToUpdate[Piwik_Archive::INDEX_NB_VISITS] += $newRowToAdd[Piwik_Archive::INDEX_NB_VISITS]; - $oldRowToUpdate[Piwik_Archive::INDEX_NB_ACTIONS] += $newRowToAdd[Piwik_Archive::INDEX_NB_ACTIONS]; - $oldRowToUpdate[Piwik_Archive::INDEX_NB_UNIQ_VISITORS] += $newRowToAdd[Piwik_Archive::INDEX_NB_UNIQ_VISITORS]; - if ($onlyMetricsAvailableInActionsTable) { - return; - } - - $oldRowToUpdate[Piwik_Archive::INDEX_MAX_ACTIONS] = (float)max($newRowToAdd[Piwik_Archive::INDEX_MAX_ACTIONS], $oldRowToUpdate[Piwik_Archive::INDEX_MAX_ACTIONS]); - $oldRowToUpdate[Piwik_Archive::INDEX_SUM_VISIT_LENGTH] += $newRowToAdd[Piwik_Archive::INDEX_SUM_VISIT_LENGTH]; - $oldRowToUpdate[Piwik_Archive::INDEX_BOUNCE_COUNT] += $newRowToAdd[Piwik_Archive::INDEX_BOUNCE_COUNT]; - $oldRowToUpdate[Piwik_Archive::INDEX_NB_VISITS_CONVERTED] += $newRowToAdd[Piwik_Archive::INDEX_NB_VISITS_CONVERTED]; - + return $metricsRequested === false + || in_array($metricId, $metricsRequested); } /** - * Given an array of stats, it will process the sum of goal conversions - * and sum of revenue and add it in the stats array in two new fields. - * - * @param array $metricsByLabel Passed by reference, it will be modified as follows: - * Input: - * array( - * LABEL => array( Piwik_Archive::INDEX_NB_VISITS => X, - * Piwik_Archive::INDEX_GOALS => array( - * idgoal1 => array( [...] ), - * idgoal2 => array( [...] ), - * ), - * [...] ), - * LABEL2 => array( Piwik_Archive::INDEX_NB_VISITS => Y, [...] ) - * ); + * Returns the actions by the given dimension * + * - The basic use case is to use $dimensionRecord and optionally $where. + * - If you want to apply a limit and group the others, use $orderBy to sort the way you + * want the limit to be applied and pass a pre-configured instance of Piwik_RankingQuery. + * The ranking query instance has to have a limit and at least one label column. + * See Piwik_RankingQuery::setLimit() and Piwik_RankingQuery::addLabelColumn(). + * If $rankingQuery is set, the return value is the array returned by + * Piwik_RankingQuery::execute(). + * - By default, the method only queries log_link_visit_action. If you need data from + * log_action (e.g. to partition the result from the ranking query into the different + * action types), use $joinLogActionOnColumn and $additionalSelects to join log_action and select + * the column you need from log_action. * - * Output: - * array( - * LABEL => array( Piwik_Archive::INDEX_NB_VISITS => X, - * Piwik_Archive::INDEX_NB_CONVERSIONS => Y, // sum of all conversions - * Piwik_Archive::INDEX_REVENUE => Z, // sum of all revenue - * Piwik_Archive::INDEX_GOALS => array( - * idgoal1 => array( [...] ), - * idgoal2 => array( [...] ), - * ), - * [...] ), - * LABEL2 => array( Piwik_Archive::INDEX_NB_VISITS => Y, [...] ) - * ); - * ) * - * @param array $metricsByLabel Passed by reference, will be modified + * @param array|string $dimensions the dimensionRecord(s) you're interested in + * @param string $where where clause + * @param bool|string $additionalSelects additional select 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 Piwik_RankingQuery $rankingQuery pre-configured ranking query instance + * @param bool|string $joinLogActionOnColumn column from log_link_visit_action that + * log_action should be joined on. + * can be an array to join multiple times. + * @internal param bool|string $orderBy order by clause + * @return mixed */ - function enrichMetricsWithConversions(&$metricsByLabel) + protected function getGroupByStatement($dimensions, $tableName) { - foreach ($metricsByLabel as $label => &$values) { - if (isset($values[Piwik_Archive::INDEX_GOALS])) { - // When per goal metrics are processed, general 'visits converted' is not meaningful because - // it could differ from the sum of each goal conversions - unset($values[Piwik_Archive::INDEX_NB_VISITS_CONVERTED]); - $revenue = $conversions = 0; - foreach ($values[Piwik_Archive::INDEX_GOALS] as $idgoal => $goalValues) { - // Do not sum Cart revenue since it is a lost revenue - if ($idgoal >= Piwik_Tracker_GoalManager::IDGOAL_ORDER) { - $revenue += $goalValues[Piwik_Archive::INDEX_GOAL_REVENUE]; - $conversions += $goalValues[Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS]; - } - } - $values[Piwik_Archive::INDEX_NB_CONVERSIONS] = $conversions; + $dimensions = $this->getSelectDimensions($dimensions, $tableName, $appendSelectAs = false); + $groupBy = implode(", ", $dimensions); + return $groupBy; + } - // 25.00 recorded as 25 - if (round($revenue) == $revenue) { - $revenue = round($revenue); - } - $values[Piwik_Archive::INDEX_REVENUE] = $revenue; + protected function getSelectDimensions($dimensions, $tableName, $appendSelectAs = true) + { + foreach ($dimensions as $selectAs => &$field) { + $selectAsString = $field; + if(!is_numeric($selectAs)) { + $selectAsString = $selectAs; + } + if($selectAsString == $field) { + $field = "$tableName.$field"; + } + if($appendSelectAs) { + $field = "$field AS $selectAsString"; } } + return $dimensions; } /** - * - * @param array $metricsByLabelAndSubLabel Passed by reference, will be modified + * Main method to process logs for a day. The only logic done here is computing the number of visits, actions, etc. + * All the other reports are computed inside plugins listening to the event 'ArchiveProcessing_Day.compute'. + * See some of the plugins for an example eg. 'Provider' */ - function enrichPivotMetricsWithConversions(&$metricsByLabelAndSubLabel) + protected function compute() { - foreach ($metricsByLabelAndSubLabel as $mainLabel => &$metricsBySubLabel) { - $this->enrichMetricsWithConversions($metricsBySubLabel); + if (!$this->isThereSomeVisits()) { + return; } + Piwik_PostEvent('ArchiveProcessing_Day.compute', $this); } /** + * If a segment is specified but a plugin other than 'VisitsSummary' is being requested, + * we create an archive for processing VisitsSummary Core Metrics, which will in turn + * execute the query above (in isThereSomeVisits) * - * @param $newRowToAdd - * @param $oldRowToUpdate + * @return bool|null */ - function sumGoalMetrics($newRowToAdd, &$oldRowToUpdate) + private function makeArchiveToCheckForVisits() { - $oldRowToUpdate[Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS] += $newRowToAdd[Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS]; - $oldRowToUpdate[Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED] += $newRowToAdd[Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED]; - $oldRowToUpdate[Piwik_Archive::INDEX_GOAL_REVENUE] += $newRowToAdd[Piwik_Archive::INDEX_GOAL_REVENUE]; - - // Cart & Order - if (isset($oldRowToUpdate[Piwik_Archive::INDEX_GOAL_ECOMMERCE_ITEMS])) { - $oldRowToUpdate[Piwik_Archive::INDEX_GOAL_ECOMMERCE_ITEMS] += $newRowToAdd[Piwik_Archive::INDEX_GOAL_ECOMMERCE_ITEMS]; - - // Order only - if (isset($oldRowToUpdate[Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_SUBTOTAL])) { - $oldRowToUpdate[Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_SUBTOTAL] += $newRowToAdd[Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_SUBTOTAL]; - $oldRowToUpdate[Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_TAX] += $newRowToAdd[Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_TAX]; - $oldRowToUpdate[Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_SHIPPING] += $newRowToAdd[Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_SHIPPING]; - $oldRowToUpdate[Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_DISCOUNT] += $newRowToAdd[Piwik_Archive::INDEX_GOAL_ECOMMERCE_REVENUE_DISCOUNT]; - } + $archive = $this->makeNewArchive(); + $nbVisits = $archive->getNumeric('nb_visits'); + $this->isThereSomeVisits = $nbVisits > 0; + + if ($this->isThereSomeVisits) { + $nbVisitsConverted = $archive->getNumeric('nb_visits_converted'); + $this->setNumberOfVisits($nbVisits); + $this->setNumberOfVisitsConverted($nbVisitsConverted); } + + return $this->isThereSomeVisits; } + public function getDb() + { + return Zend_Registry::get('db'); + } } diff --git a/core/DataAccess/LogAggregator.php b/core/DataAccess/LogAggregator.php index 0e9efcc99c..aafc3e4c1c 100644 --- a/core/DataAccess/LogAggregator.php +++ b/core/DataAccess/LogAggregator.php @@ -21,10 +21,20 @@ class Piwik_DataAccess_LogAggregator * ie (AND, OR, etc.). * @return array An array of SQL SELECT expressions. */ - public static function buildReduceByRangeSelect( $column, $ranges, $table, $selectColumnPrefix = '', $extraCondition = false) + public static function getSelectsFromRangedColumn( $metadata ) { - $selects = array(); + @list($column, $ranges, $table, $selectColumnPrefix, $i_am_your_nightmare_DELETE_ME) = $metadata; + $selects = array(); + $extraCondition = ''; + if($i_am_your_nightmare_DELETE_ME) { + // extra condition for the SQL SELECT that makes sure only returning visits are counted + // when creating the 'days since last visit' report + $extraCondition = 'and log_visit.visitor_returning = 1'; + $extraSelect = "sum(case when log_visit.visitor_returning = 0 then 1 else 0 end) " + . " as `". $selectColumnPrefix . 'General_NewVisits' . "`"; + $selects[] = $extraSelect; + } foreach ($ranges as $gap) { if (count($gap) == 2) { $lowerBound = $gap[0]; @@ -46,4 +56,30 @@ class Piwik_DataAccess_LogAggregator return $selects; } + /** + * Clean up the row data and return values. + * $lookForThisPrefix can be used to make sure only SOME of the data in $row is used. + * + * The array will have one column $columnName + * + * @param $row + * @param $columnName + * @param $lookForThisPrefix A string that identifies which elements of $row to use + * in the result. Every key of $row that starts with this + * value is used. + * @return array + */ + static public function makeArrayOneColumn($row, $columnName, $lookForThisPrefix = false) + { + $cleanRow = array(); + foreach ($row as $label => $count) { + if (empty($lookForThisPrefix) + || strpos($label, $lookForThisPrefix) === 0) { + $cleanLabel = substr($label, strlen($lookForThisPrefix)); + $cleanRow[$cleanLabel] = array($columnName => $count); + } + } + return $cleanRow; + } + }
\ No newline at end of file diff --git a/core/DataTable.php b/core/DataTable.php index ca38047bd9..8d7f75245f 100644 --- a/core/DataTable.php +++ b/core/DataTable.php @@ -1029,6 +1029,7 @@ class Piwik_DataTable // we then serialize the rows and store them in the serialized dataTable $addToRows = array(self::ID_SUMMARY_ROW => $this->summaryRow); + //FIXMEA let's kill this soon * re-do if necessary if ($this->parents && Piwik_Config::getInstance()->General['enable_archive_parents_of_datatable']) { $addToRows[self::ID_PARENTS] = $this->parents; } @@ -1181,7 +1182,7 @@ class Piwik_DataTable * LABEL => array(col1 => X, col2 => Y), * LABEL2 => array(col1 => X, col2 => Y), * ) - * to the structure + * to a DataTable, ie. with the internal structure * array ( * array( Piwik_DataTable_Row::COLUMNS => array('label' => LABEL, col1 => X, col2 => Y)), * array( Piwik_DataTable_Row::COLUMNS => array('label' => LABEL2, col1 => X, col2 => Y)), @@ -1192,57 +1193,42 @@ class Piwik_DataTable * LABEL => X, * LABEL2 => Y, * ) - * would be converted to the structure + * would be converted to: * array ( * array( Piwik_DataTable_Row::COLUMNS => array('label' => LABEL, 'value' => X)), * array( Piwik_DataTable_Row::COLUMNS => array('label' => LABEL2, 'value' => Y)), * ) * - * The optional parameter $subtablePerLabel is an array of subTable associated to the rows of the $array - * For example if $subtablePerLabel is given - * array( - * LABEL => #Piwik_DataTable_ForLABEL, - * LABEL2 => #Piwik_DataTable_ForLABEL2, - * ) - * - * the $array would become - * array ( - * array( Piwik_DataTable_Row::COLUMNS => array('label' => LABEL, col1 => X, col2 => Y), - * Piwik_DataTable_Row::DATATABLE_ASSOCIATED => #ID DataTable For LABEL - * ), - * array( Piwik_DataTable_Row::COLUMNS => array('label' => LABEL2, col1 => X, col2 => Y) - * Piwik_DataTable_Row::DATATABLE_ASSOCIATED => #ID2 DataTable For LABEL2 - * ), - * ) * - * @param array $array See method description - * @param array|null $subtablePerLabel See method description + * @param array $array Indexed array, two formats are supported + * @param array|null $subtablePerLabel An indexed array of up to one DataTable to associate as a sub table */ - public function addRowsFromArrayWithIndexLabel($array, $subtablePerLabel = null) + public static function makeFromIndexedArray($array, $subtablePerLabel = null) { + $table = new Piwik_DataTable(); $cleanRow = array(); foreach ($array as $label => $row) { + // Support the case of an $array of single values if (!is_array($row)) { $row = array('value' => $row); } - $cleanRow[Piwik_DataTable_Row::DATATABLE_ASSOCIATED] = null; - // we put the 'label' column first as it looks prettier in API results + // Put the 'label' column first $cleanRow[Piwik_DataTable_Row::COLUMNS] = array('label' => $label) + $row; - if (!is_null($subtablePerLabel) - // some rows of this table don't have subtables - // (for example case of campaigns without keywords) - && isset($subtablePerLabel[$label]) - ) { + // Assign subtable if specified + if (isset($subtablePerLabel[$label])) { $cleanRow[Piwik_DataTable_Row::DATATABLE_ASSOCIATED] = $subtablePerLabel[$label]; } - $this->addRow(new Piwik_DataTable_Row($cleanRow)); + $table->addRow(new Piwik_DataTable_Row($cleanRow)); } + return $table; } /** * Set the array of parent ids * * @param array $parents + * + * FIXMEA */ public function setParents($parents) { diff --git a/core/DataTable/Row.php b/core/DataTable/Row.php index 5179618bfe..9038aed608 100644 --- a/core/DataTable/Row.php +++ b/core/DataTable/Row.php @@ -522,7 +522,7 @@ class Piwik_DataTable_Row ) { // We shall update metadata, and keep the metadata with the _most visits or pageviews_, rather than first or last seen $visits = max($rowToSum->getColumn(Piwik_Archive::INDEX_PAGE_NB_HITS) || $rowToSum->getColumn(Piwik_Archive::INDEX_NB_VISITS), - // Old format pre-1.2, @see also method sumMetrics() + // Old format pre-1.2, @see also method doSumVisitsMetrics() $rowToSum->getColumn('nb_actions') || $rowToSum->getColumn('nb_visits')); if (($visits && $visits > $this->maxVisitsSummed) || empty($this->c[self::METADATA]) diff --git a/core/PluginsArchiver.php b/core/PluginsArchiver.php index 7dcdf3e54f..dca85a7c77 100644 --- a/core/PluginsArchiver.php +++ b/core/PluginsArchiver.php @@ -20,7 +20,6 @@ abstract class Piwik_PluginsArchiver public function __construct(Piwik_ArchiveProcessing $processing) { $this->maximumRows = Piwik_Config::getInstance()->General['datatable_archiving_maximum_rows_standard']; - $this->maximumRows = Piwik_Config::getInstance()->General['datatable_archiving_maximum_rows_standard']; $this->processor = $processing; } diff --git a/core/Segment.php b/core/Segment.php index ce37845e88..b59e04d37f 100644 --- a/core/Segment.php +++ b/core/Segment.php @@ -162,8 +162,6 @@ class Piwik_Segment */ public function getSelectQuery($select, $from, $where = false, $bind = array(), $orderBy = false, $groupBy = false) { - $joinWithSubSelect = false; - if (!is_array($from)) { $from = array($from); } diff --git a/plugins/Actions/Archiver.php b/plugins/Actions/Archiver.php index 84dc0b7da3..1a8e902f16 100644 --- a/plugins/Actions/Archiver.php +++ b/plugins/Actions/Archiver.php @@ -439,10 +439,14 @@ class Piwik_Actions_Archiver extends Piwik_PluginsArchiver { $dataTable = $this->getDataTable(Piwik_Tracker_Action::TYPE_ACTION_URL); $this->recordDataTable($dataTable, self::PAGE_URLS_RECORD_NAME); - $this->getProcessor()->insertNumericRecord(self::METRIC_PAGEVIEWS_RECORD_NAME, array_sum($dataTable->getColumn(Piwik_Archive::INDEX_PAGE_NB_HITS))); - $this->getProcessor()->insertNumericRecord(self::METRIC_UNIQ_PAGEVIEWS_RECORD_NAME, array_sum($dataTable->getColumn(Piwik_Archive::INDEX_NB_VISITS))); - $this->getProcessor()->insertNumericRecord(self::METRIC_SUM_TIME_RECORD_NAME, array_sum($dataTable->getColumn(Piwik_Archive::INDEX_PAGE_SUM_TIME_GENERATION))); - $this->getProcessor()->insertNumericRecord(self::METRIC_HITS_TIMED_RECORD_NAME, array_sum($dataTable->getColumn(Piwik_Archive::INDEX_PAGE_NB_HITS_WITH_TIME_GENERATION))); + + $records = array( + self::METRIC_PAGEVIEWS_RECORD_NAME => array_sum($dataTable->getColumn(Piwik_Archive::INDEX_PAGE_NB_HITS)), + self::METRIC_UNIQ_PAGEVIEWS_RECORD_NAME => array_sum($dataTable->getColumn(Piwik_Archive::INDEX_NB_VISITS)), + self::METRIC_SUM_TIME_RECORD_NAME => array_sum($dataTable->getColumn(Piwik_Archive::INDEX_PAGE_SUM_TIME_GENERATION)), + self::METRIC_HITS_TIMED_RECORD_NAME => array_sum($dataTable->getColumn(Piwik_Archive::INDEX_PAGE_NB_HITS_WITH_TIME_GENERATION)) + ); + $this->getProcessor()->insertNumericRecords( $records ); } /** diff --git a/plugins/CustomVariables/Archiver.php b/plugins/CustomVariables/Archiver.php index f5df35ba38..15cc2fdf23 100644 --- a/plugins/CustomVariables/Archiver.php +++ b/plugins/CustomVariables/Archiver.php @@ -13,8 +13,11 @@ class Piwik_CustomVariables_Archiver extends Piwik_PluginsArchiver { const LABEL_CUSTOM_VALUE_NOT_DEFINED = "Value not defined"; const CUSTOM_VARIABLE_RECORD_NAME = 'CustomVariables_valueByName'; - protected $metricsByKey = array(); - protected $metricsByKeyAndValue = array(); + + /** + * @var Piwik_DataArray + */ + protected $dataArray; protected $maximumRowsInDataTableLevelZero; protected $maximumRowsInSubDataTable; protected $newEmptyRow; @@ -28,15 +31,15 @@ class Piwik_CustomVariables_Archiver extends Piwik_PluginsArchiver public function archiveDay() { + $this->dataArray = new Piwik_DataArray(); + for ($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $i++) { $this->aggregateCustomVariable($i); } $this->removeVisitsMetricsFromActionsAggregate(); - $this->getProcessor()->enrichMetricsWithConversions($this->metricsByKey); - $this->getProcessor()->enrichPivotMetricsWithConversions($this->metricsByKeyAndValue); - - $table = $this->getProcessor()->getDataTableWithSubtablesFromArraysIndexedByLabel($this->metricsByKeyAndValue, $this->metricsByKey); + $this->dataArray->enrichMetricsWithConversions(); + $table = $this->getProcessor()->getDataTableFromDataArray($this->dataArray); $blob = $table->getSerialized( $this->maximumRowsInDataTableLevelZero, $this->maximumRowsInSubDataTable, $columnToSort = Piwik_Archive::INDEX_NB_VISITS @@ -55,21 +58,34 @@ class Piwik_CustomVariables_Archiver extends Piwik_PluginsArchiver $query = $this->getProcessor()->queryVisitsByDimension($dimensions, $where); $this->aggregateFromVisits($query, $keyField, $valueField); - $query = $this->getProcessor()->queryActionsByDimension($dimensions, $where); + // IF we query Custom Variables scope "page" either: Product SKU, Product Name, + // then we also query the "Product page view" price which was possibly recorded. + $additionalSelects = false; + // FIXMEA + if (in_array($slot, array(3,4,5))) { + $additionalSelects = array( $this->getSelectAveragePrice() ); + } + $query = $this->getProcessor()->queryActionsByDimension($dimensions, $where, $additionalSelects); $this->aggregateFromActions($query, $keyField, $valueField); $query = $this->getProcessor()->queryConversionsByDimension($dimensions, $where); $this->aggregateFromConversions($query, $keyField, $valueField); } + protected function getSelectAveragePrice() + { + return Piwik_ArchiveProcessing_Day::getSqlRevenue("AVG(log_link_visit_action.custom_var_v2)") + . " as `" . Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED . "`"; + } + protected function aggregateFromVisits($query, $keyField, $valueField) { while ($row = $query->fetch()) { - $value = $row[$valueField]; - $value = $this->cleanCustomVarValue($value); - $key = $row[$keyField]; - $this->aggregateVisit($key, $value, $row); + $value = $this->cleanCustomVarValue($row[$valueField]); + + $this->dataArray->sumMetricsVisits($key, $row); + $this->dataArray->sumMetricsVisitsPivot($key, $value, $row); } } @@ -81,35 +97,18 @@ class Piwik_CustomVariables_Archiver extends Piwik_PluginsArchiver return self::LABEL_CUSTOM_VALUE_NOT_DEFINED; } - protected function aggregateVisit($key, $value, $row) - { - if (!isset($this->metricsByKey[$key])) { - $this->metricsByKey[$key] = $this->getProcessor()->makeEmptyRow(); - } - if (!isset($this->metricsByKeyAndValue[$key][$value])) { - $this->metricsByKeyAndValue[$key][$value] = $this->getProcessor()->makeEmptyRow(); - } - - $this->getProcessor()->sumMetrics($row, $this->metricsByKey[$key]); - $this->getProcessor()->sumMetrics($row, $this->metricsByKeyAndValue[$key][$value]); - } protected function aggregateFromActions($query, $keyField, $valueField) { while ($row = $query->fetch()) { $key = $row[$keyField]; - $value = $row[$valueField]; - $value = $this->cleanCustomVarValue($value); - $this->aggregateAction($key, $value, $row); - } - } + $value = $this->cleanCustomVarValue($row[$valueField]); - protected function aggregateAction($key, $value, $row) - { - $alreadyAggregated = $this->aggregateEcommerceCategories($key, $value, $row); - if (!$alreadyAggregated) { - $this->aggregateActionByKeyAndValue($key, $value, $row); - $this->aggregateActionByKey($key, $row); + $alreadyAggregated = $this->aggregateEcommerceCategories($key, $value, $row); + if (!$alreadyAggregated) { + $this->aggregateActionByKeyAndValue($key, $value, $row); + $this->dataArray->sumMetricsActions($key, $row); + } } } @@ -146,17 +145,14 @@ class Piwik_CustomVariables_Archiver extends Piwik_PluginsArchiver protected function aggregateActionByKeyAndValue($key, $value, $row) { - if (!isset($this->metricsByKeyAndValue[$key][$value])) { - $this->metricsByKeyAndValue[$key][$value] = $this->getProcessor()->makeEmptyActionRow(); - } - $this->getProcessor()->sumMetrics($row, $this->metricsByKeyAndValue[$key][$value], $onlyMetricsAvailableInActionsTable = true); + $this->dataArray->sumMetricsActionsPivot($key, $value, $row); if ($this->isReservedKey($key)) { // Price tracking on Ecommerce product/category pages: // the average is returned from the SQL query so the price is not "summed" like other metrics $index = Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED; if (!empty($row[$index])) { - $this->metricsByKeyAndValue[$key][$value][$index] = (float)$row[$index]; + $this->dataArray->setRowColumnPivot($key, $value, $index, (float)$row[$index]); } } } @@ -166,13 +162,6 @@ class Piwik_CustomVariables_Archiver extends Piwik_PluginsArchiver return in_array($key, Piwik_CustomVariables_API::getReservedCustomVariableKeys()); } - protected function aggregateActionByKey($key, $row) - { - if (!isset($this->metricsByKey[$key])) { - $this->metricsByKey[$key] = $this->getProcessor()->makeEmptyActionRow(); - } - $this->getProcessor()->sumMetrics($row, $this->metricsByKey[$key], $onlyMetricsAvailableInActionsTable = true); - } protected function aggregateFromConversions($query, $keyField, $valueField) { @@ -182,32 +171,17 @@ class Piwik_CustomVariables_Archiver extends Piwik_PluginsArchiver while ($row = $query->fetch()) { $key = $row[$keyField]; $value = $this->cleanCustomVarValue($row[$valueField]); - $idGoal = $row['idgoal']; - $this->aggregateConversion($key, $value, $idGoal, $row); - } - } - - protected function aggregateConversion($key, $value, $idGoal, $row) - { - if (!isset($this->metricsByKey[$key][Piwik_Archive::INDEX_GOALS][$idGoal])) { - $this->metricsByKey[$key][Piwik_Archive::INDEX_GOALS][$idGoal] = $this->getProcessor()->makeEmptyGoalRow($idGoal); - } - if (!isset($this->metricsByKeyAndValue[$key][$value][Piwik_Archive::INDEX_GOALS][$idGoal])) { - $this->metricsByKeyAndValue[$key][$value][Piwik_Archive::INDEX_GOALS][$idGoal] = $this->getProcessor()->makeEmptyGoalRow($idGoal); + $this->dataArray->sumMetricsGoals($key, $row); + $this->dataArray->sumMetricsGoalsPivot($key, $value, $row); } - - $this->getProcessor()->sumGoalMetrics($row, $this->metricsByKey[$key][Piwik_Archive::INDEX_GOALS][$idGoal]); - $this->getProcessor()->sumGoalMetrics($row, $this->metricsByKeyAndValue[$key][$value][Piwik_Archive::INDEX_GOALS][$idGoal]); } protected function removeVisitsMetricsFromActionsAggregate() { - $emptyActionRow = $this->getProcessor()->makeEmptyActionRow(); - - foreach ($this->metricsByKey as $key => &$row) { - $isActionRowAggregate = (count($row) == count($emptyActionRow)); + $dataArray = &$this->dataArray->getDataArray(); + foreach ($dataArray as $key => &$row) { if (!self::isReservedKey($key) - && $isActionRowAggregate + && Piwik_DataArray::isRowActions($row) ) { unset($row[Piwik_Archive::INDEX_NB_UNIQ_VISITORS]); unset($row[Piwik_Archive::INDEX_NB_VISITS]); diff --git a/plugins/DBStats/API.php b/plugins/DBStats/API.php index fec0e0fe71..693c7f2e1e 100644 --- a/plugins/DBStats/API.php +++ b/plugins/DBStats/API.php @@ -128,9 +128,7 @@ class Piwik_DBStats_API $rowToAddTo['row_count'] += $status['Rows']; } - $result = new Piwik_DataTable(); - $result->addRowsFromArrayWithIndexLabel($rows); - return $result; + return Piwik_DataTable::makeFromIndexedArray($rows); } /** diff --git a/plugins/DevicesDetection/Archiver.php b/plugins/DevicesDetection/Archiver.php index 5ff05d3f3a..8bedcc9d39 100644 --- a/plugins/DevicesDetection/Archiver.php +++ b/plugins/DevicesDetection/Archiver.php @@ -11,82 +11,46 @@ class Piwik_DevicesDetection_Archiver extends Piwik_PluginsArchiver { - const TYPE_RECORD_NAME = 'DevicesDetection_types'; - const BRAND_RECORD_NAME = 'DevicesDetection_brands'; - const MODEL_RECORD_NAME = 'DevicesDetection_models'; + const DEVICE_TYPE_RECORD_NAME = 'DevicesDetection_types'; + const DEVICE_BRAND_RECORD_NAME = 'DevicesDetection_brands'; + const DEVICE_MODEL_RECORD_NAME = 'DevicesDetection_models'; const OS_RECORD_NAME = 'DevicesDetection_os'; const OS_VERSION_RECORD_NAME = 'DevicesDetection_osVersions'; const BROWSER_RECORD_NAME = 'DevicesDetection_browsers'; const BROWSER_VERSION_RECORD_NAME = 'DevicesDetection_browserVersions'; - public function __construct($processor) - { - parent::__construct($processor); - $this->maximumRowsInDataTable = Piwik_Config::getInstance()->General['datatable_archiving_maximum_rows_standard']; - } + const DEVICE_TYPE_FIELD = "config_device_type"; + const DEVICE_BRAND_FIELD = "config_device_brand"; + const DEVICE_MODEL_FIELD = "config_device_model"; + const OS_FIELD = "config_os"; + const OS_VERSION_FIELD = "CONCAT(log_visit.config_os, ';', log_visit.config_os_version)"; + const BROWSER_FIELD = "config_browser_name"; + const BROWSER_VERSION_DIMENSION = "CONCAT(log_visit.config_browser_name, ';', log_visit.config_browser_version)"; public function archiveDay() { - $this->archiveDayDeviceTypes(); - $this->archiveDayDeviceBrands(); - $this->archiveDayDeviceModels(); - $this->archiveDayOs(); - $this->archiveDayOsVersions(); - $this->archiveDayBrowserFamilies(); - $this->archiveDayBrowsersVersions(); - } - - private function archiveDayDeviceTypes() - { - $labelSQL = "log_visit.config_device_type"; - $this->aggregateByLabel( $labelSQL, self::TYPE_RECORD_NAME); + $this->aggregateByLabel( self::DEVICE_TYPE_FIELD, self::DEVICE_TYPE_RECORD_NAME); + $this->aggregateByLabel( self::DEVICE_BRAND_FIELD, self::DEVICE_BRAND_RECORD_NAME); + $this->aggregateByLabel( self::DEVICE_MODEL_FIELD, self::DEVICE_MODEL_RECORD_NAME); + $this->aggregateByLabel( self::OS_FIELD, self::OS_RECORD_NAME); + $this->aggregateByLabel( self::OS_VERSION_FIELD, self::OS_VERSION_RECORD_NAME); + $this->aggregateByLabel( self::BROWSER_FIELD, self::BROWSER_RECORD_NAME); + $this->aggregateByLabel( self::BROWSER_VERSION_DIMENSION, self::BROWSER_VERSION_RECORD_NAME); } private function aggregateByLabel( $labelSQL, $recordName) { - $metricsByLabel = $this->getProcessor()->getMetricsForLabel($labelSQL); - $tableBrand = $this->getProcessor()->getDataTableFromArray($metricsByLabel); - - $this->getProcessor()->insertBlobRecord($recordName, $tableBrand->getSerialized($this->maximumRowsInDataTable, null, Piwik_Archive::INDEX_NB_VISITS)); - } - - private function archiveDayDeviceBrands() - { - $this->aggregateByLabel( "log_visit.config_device_brand", self::BRAND_RECORD_NAME); - } - - private function archiveDayDeviceModels() - { - $this->aggregateByLabel( "log_visit.config_device_model", self::MODEL_RECORD_NAME); - } - - private function archiveDayOs() - { - $this->aggregateByLabel( "log_visit.config_os", self::OS_RECORD_NAME); - } - - private function archiveDayOsVersions() - { - $this->aggregateByLabel( "CONCAT(log_visit.config_os, ';', log_visit.config_os_version)", self::OS_VERSION_RECORD_NAME); - } - - private function archiveDayBrowserFamilies() - { - $this->aggregateByLabel( "log_visit.config_browser_name", self::BROWSER_RECORD_NAME); - } - - private function archiveDayBrowsersVersions() - { - $this->aggregateByLabel( "CONCAT(log_visit.config_browser_name, ';', log_visit.config_browser_version)", self::BROWSER_VERSION_RECORD_NAME); + $metrics = $this->getProcessor()->getMetricsForDimension($labelSQL); + $table = $this->getProcessor()->getDataTableFromDataArray($metrics); + $this->getProcessor()->insertBlobRecord($recordName, $table->getSerialized($this->maximumRows, null, Piwik_Archive::INDEX_NB_VISITS)); } public function archivePeriod() { - $maximumRowsInSubDataTable = $this->maximumRowsInDataTable; $dataTablesToSum = array( - self::TYPE_RECORD_NAME, - self::BRAND_RECORD_NAME, - self::MODEL_RECORD_NAME, + self::DEVICE_TYPE_RECORD_NAME, + self::DEVICE_BRAND_RECORD_NAME, + self::DEVICE_MODEL_RECORD_NAME, self::OS_RECORD_NAME, self::OS_VERSION_RECORD_NAME, self::BROWSER_RECORD_NAME, @@ -94,7 +58,7 @@ class Piwik_DevicesDetection_Archiver extends Piwik_PluginsArchiver ); foreach ($dataTablesToSum as $dt) { $this->getProcessor()->archiveDataTable( - $dt, null, $this->maximumRowsInDataTable, $maximumRowsInSubDataTable, $columnToSort = "nb_visits"); + $dt, null, $this->maximumRows, $this->maximumRows, $columnToSort = "nb_visits"); } } }
\ No newline at end of file diff --git a/plugins/ExampleUI/API.php b/plugins/ExampleUI/API.php index 91e29806be..697844a589 100644 --- a/plugins/ExampleUI/API.php +++ b/plugins/ExampleUI/API.php @@ -50,11 +50,7 @@ class Piwik_ExampleUI_API $value = array('server1' => $server1, 'server2' => $server2); $temperatures[$subPeriod->getLocalizedShortString()] = $value; } - - // convert this array to a DataTable object - $dataTable = new Piwik_DataTable(); - $dataTable->addRowsFromArrayWithIndexLabel($temperatures); - return $dataTable; + return Piwik_DataTable::makeFromIndexedArray($temperatures); } // we generate an array of random server temperatures @@ -71,10 +67,7 @@ class Piwik_ExampleUI_API $temperatures[$xAxisLabel] = $temperatureValues[$i]; } - // convert this array to a DataTable object - $dataTable = new Piwik_DataTable(); - $dataTable->addRowsFromArrayWithIndexLabel($temperatures); - return $dataTable; + return Piwik_DataTable::makeFromIndexedArray($temperatures); } public function getPlanetRatios() @@ -90,9 +83,7 @@ class Piwik_ExampleUI_API 'Neptune' => 3.883, ); // convert this array to a DataTable object - $dataTable = new Piwik_DataTable(); - $dataTable->addRowsFromArrayWithIndexLabel($planetRatios); - return $dataTable; + return Piwik_DataTable::makeFromIndexedArray($planetRatios); } public function getPlanetRatiosWithLogos() diff --git a/plugins/Goals/Archiver.php b/plugins/Goals/Archiver.php index 45d915e1ab..0a5a154f3b 100644 --- a/plugins/Goals/Archiver.php +++ b/plugins/Goals/Archiver.php @@ -16,6 +16,17 @@ class Piwik_Goals_Archiver extends Piwik_PluginsArchiver const ITEMS_SKU_RECORD_NAME = 'Goals_ItemsSku'; const ITEMS_NAME_RECORD_NAME = 'Goals_ItemsName'; const ITEMS_CATEGORY_RECORD_NAME = 'Goals_ItemsCategory'; + const SKU_FIELD = 'idaction_sku'; + const NAME_FIELD = 'idaction_name'; + const CATEGORY_FIELD = 'idaction_category'; + const CATEGORY2_FIELD = 'idaction_category2'; + const CATEGORY3_FIELD = 'idaction_category3'; + const CATEGORY4_FIELD = 'idaction_category4'; + const CATEGORY5_FIELD = 'idaction_category5'; + const NO_LABEL = ':'; + const LOG_CONVERSION_TABLE = 'log_conversion'; + const VISITS_COUNT_FIELD = 'visitor_count_visits'; + const DAYS_SINCE_FIRST_VISIT_FIELD = 'visitor_days_since_first'; /** * This array stores the ranges to use when displaying the 'visits to conversion' report */ @@ -53,209 +64,307 @@ class Piwik_Goals_Archiver extends Piwik_PluginsArchiver array(121, 364), array(364) ); - - protected $dimensions = array( - 'idaction_sku' => self::ITEMS_SKU_RECORD_NAME, - 'idaction_name' => self::ITEMS_NAME_RECORD_NAME, - 'idaction_category' => self::ITEMS_CATEGORY_RECORD_NAME + protected $dimensionRecord = array( + self::SKU_FIELD => self::ITEMS_SKU_RECORD_NAME, + self::NAME_FIELD => self::ITEMS_NAME_RECORD_NAME, + self::CATEGORY_FIELD => self::ITEMS_CATEGORY_RECORD_NAME ); + /** + * Array containing one DataArray for each Ecommerce items dimension (name/sku/category abandoned carts and orders) + * @var array + */ + protected $itemReports = array(); + public function archiveDay() { $this->archiveGeneralGoalMetrics(); $this->archiveEcommerceItems(); } - function archiveGeneralGoalMetrics() + protected function archiveGeneralGoalMetrics() { - $selectAsVisitCount = 'vcv'; - $selectAsDaysSince = 'vdsf'; - // extra aggregate selects for the visits to conversion report - $visitToConvExtraCols = Piwik_DataAccess_LogAggregator::buildReduceByRangeSelect( - 'visitor_count_visits', self::$visitCountRanges, 'log_conversion', $selectAsVisitCount); - - // extra aggregate selects for the days to conversion report - $daysToConvExtraCols = Piwik_DataAccess_LogAggregator::buildReduceByRangeSelect( - 'visitor_days_since_first', self::$daysToConvRanges, 'log_conversion', $selectAsDaysSince); - - $query = $this->getProcessor()->queryConversionsByDimension(array(), '', array_merge($visitToConvExtraCols, $daysToConvExtraCols)); + $prefixes = array( + self::VISITS_UNTIL_RECORD_NAME => 'vcv', + self::DAYS_UNTIL_CONV_RECORD_NAME => 'vdsf', + ); + $aggregatesMetadata = array( + array(self::VISITS_COUNT_FIELD, self::$visitCountRanges, self::LOG_CONVERSION_TABLE, $prefixes[self::VISITS_UNTIL_RECORD_NAME]), + array(self::DAYS_SINCE_FIRST_VISIT_FIELD, self::$daysToConvRanges, self::LOG_CONVERSION_TABLE, $prefixes[self::DAYS_UNTIL_CONV_RECORD_NAME]), + ); + $selects = array(); + foreach ($aggregatesMetadata as $aggregateMetadata) { + $selects = array_merge($selects, Piwik_DataAccess_LogAggregator::getSelectsFromRangedColumn($aggregateMetadata)); + } + $query = $this->getProcessor()->queryConversionsByDimension(array(), false, $selects); if ($query === false) { return; } - $goals = array(); - $visitsToConvReport = array(); - $daysToConvReport = array(); + $totalConversions = $totalRevenue = 0; + $goals = new Piwik_DataArray(); + $visitsToConversions = $daysToConversions = array(); - // Get a standard empty goal row - $overall = $this->getProcessor()->makeEmptyGoalRow($idGoal = 1); + $conversionMetrics = $this->getProcessor()->getConversionsMetricFields(); while ($row = $query->fetch()) { - $idgoal = $row['idgoal']; + $idGoal = $row['idgoal']; + unset($row['idgoal']); + unset($row['label']); - if (!isset($goals[$idgoal])) { - $goals[$idgoal] = $this->getProcessor()->makeEmptyGoalRow($idgoal); + $values = array(); + foreach($conversionMetrics as $field => $statement) { + $values[$field] = $row[$field]; + } + $goals->sumMetrics($idGoal, $values); - $visitsToConvReport[$idgoal] = new Piwik_DataTable(); - $daysToConvReport[$idgoal] = new Piwik_DataTable(); + if (empty($visitsToConversions[$idGoal])) { + $visitsToConversions[$idGoal] = new Piwik_DataTable(); } - $this->getProcessor()->sumGoalMetrics($row, $goals[$idgoal]); + $array = Piwik_DataAccess_LogAggregator::makeArrayOneColumn($row, Piwik_Archive::INDEX_NB_CONVERSIONS, $prefixes[self::VISITS_UNTIL_RECORD_NAME]); + $visitsToConversions[$idGoal]->addDataTable(Piwik_DataTable::makeFromIndexedArray($array)); + + if (empty($daysToConversions[$idGoal])) { + $daysToConversions[$idGoal] = new Piwik_DataTable(); + } + $array = Piwik_DataAccess_LogAggregator::makeArrayOneColumn($row, Piwik_Archive::INDEX_NB_CONVERSIONS, $prefixes[self::DAYS_UNTIL_CONV_RECORD_NAME]); + $daysToConversions[$idGoal]->addDataTable(Piwik_DataTable::makeFromIndexedArray($array)); // We don't want to sum Abandoned cart metrics in the overall revenue/conversions/converted visits // since it is a "negative conversion" - if ($idgoal != Piwik_Tracker_GoalManager::IDGOAL_CART) { - $this->getProcessor()->sumGoalMetrics($row, $overall); + if ($idGoal != Piwik_Tracker_GoalManager::IDGOAL_CART) { + $totalConversions += $row[Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS]; + $totalRevenue += $row[Piwik_Archive::INDEX_GOAL_REVENUE]; } - - // map the goal + visit number of a visitor with the # of conversions that happened on that visit - $table = $this->getProcessor()->getSimpleDataTableFromRow($row, Piwik_Archive::INDEX_NB_CONVERSIONS, $selectAsVisitCount); - $visitsToConvReport[$idgoal]->addDataTable($table); - - // map the goal + day number of a visit with the # of conversion that happened on that day - $table = $this->getProcessor()->getSimpleDataTableFromRow($row, Piwik_Archive::INDEX_NB_CONVERSIONS, $selectAsDaysSince); - $daysToConvReport[$idgoal]->addDataTable($table); } - // these data tables hold reports for every goal of a site - $visitsToConvOverview = new Piwik_DataTable(); - $daysToConvOverview = new Piwik_DataTable(); - // Stats by goal, for all visitors - foreach ($goals as $idgoal => $values) { - foreach ($values as $metricId => $value) { - $metricName = Piwik_Archive::$mappingFromIdToNameGoal[$metricId]; - $recordName = self::getRecordName($metricName, $idgoal); - $this->getProcessor()->insertNumericRecord($recordName, $value); - } - $conversion_rate = $this->getConversionRate($values[Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED]); - $recordName = self::getRecordName('conversion_rate', $idgoal); - $this->getProcessor()->insertNumericRecord($recordName, $conversion_rate); - - // if the goal is not a special goal (like ecommerce) add it to the overview report - if ($idgoal !== Piwik_Tracker_GoalManager::IDGOAL_CART && - $idgoal !== Piwik_Tracker_GoalManager::IDGOAL_ORDER - ) { - $visitsToConvOverview->addDataTable($visitsToConvReport[$idgoal]); - $daysToConvOverview->addDataTable($daysToConvReport[$idgoal]); - } - - // visit count until conversion stats - $this->getProcessor()->insertBlobRecord( - self::getRecordName(self::VISITS_UNTIL_RECORD_NAME, $idgoal), - $visitsToConvReport[$idgoal]->getSerialized()); + $numericRecords = $this->getConversionsNumericMetrics($goals); + $this->getProcessor()->insertNumericRecords($numericRecords); - // day count until conversion stats - $this->getProcessor()->insertBlobRecord( - self::getRecordName(self::DAYS_UNTIL_CONV_RECORD_NAME, $idgoal), - $daysToConvReport[$idgoal]->getSerialized()); - } - - // archive overview reports - $this->getProcessor()->insertBlobRecord( - self::getRecordName(self::VISITS_UNTIL_RECORD_NAME), $visitsToConvOverview->getSerialized()); - $this->getProcessor()->insertBlobRecord( - self::getRecordName(self::DAYS_UNTIL_CONV_RECORD_NAME), $daysToConvOverview->getSerialized()); + $this->insertReports(self::VISITS_UNTIL_RECORD_NAME, $visitsToConversions); + $this->insertReports(self::DAYS_UNTIL_CONV_RECORD_NAME, $daysToConversions); // Stats for all goals - $totalAllGoals = array( + $metrics = array( self::getRecordName('conversion_rate') => $this->getConversionRate($this->getProcessor()->getNumberOfVisitsConverted()), - self::getRecordName('nb_conversions') => $overall[Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS], + self::getRecordName('nb_conversions') => $totalConversions, self::getRecordName('nb_visits_converted') => $this->getProcessor()->getNumberOfVisitsConverted(), - self::getRecordName('revenue') => $overall[Piwik_Archive::INDEX_GOAL_REVENUE], + self::getRecordName('revenue') => $totalRevenue, ); - foreach ($totalAllGoals as $recordName => $value) { - $this->getProcessor()->insertNumericRecord($recordName, $value); + $this->getProcessor()->insertNumericRecords($metrics); + } + + protected function getConversionsNumericMetrics(Piwik_DataArray $goals) + { + $numericRecords = array(); + $goals = $goals->getDataArray(); + foreach ($goals as $idGoal => $array) { + foreach ($array as $metricId => $value) { + $metricName = Piwik_Archive::$mappingFromIdToNameGoal[$metricId]; + $recordName = self::getRecordName($metricName, $idGoal); + $numericRecords[$recordName] = $value; + } + if(!empty($array[Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED])) { + $conversion_rate = $this->getConversionRate($array[Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED]); + $recordName = self::getRecordName('conversion_rate', $idGoal); + $numericRecords[$recordName] = $conversion_rate; + } } + return $numericRecords; } /** - * @param Piwik_ArchiveProcessing_Day $this->getProcessor() + * @param string $recordName 'nb_conversions' + * @param int|bool $idGoal idGoal to return the metrics for, or false to return overall + * @return string Archive record name */ - function archiveEcommerceItems() + static public function getRecordName($recordName, $idGoal = false) { - if (!$this->shouldArchiveEcommerceItems()) { - return false; + $idGoalStr = ''; + if ($idGoal !== false) { + $idGoalStr = $idGoal . "_"; } - $items = array(); + return 'Goal_' . $idGoalStr . $recordName; + } - $dimensionsToQuery = $this->dimensions; - $dimensionsToQuery['idaction_category2'] = 'AdditionalCategory'; - $dimensionsToQuery['idaction_category3'] = 'AdditionalCategory'; - $dimensionsToQuery['idaction_category4'] = 'AdditionalCategory'; - $dimensionsToQuery['idaction_category5'] = 'AdditionalCategory'; + protected function getConversionRate($count) + { + $visits = $this->getProcessor()->getNumberOfVisits(); + return round(100 * $count / $visits, Piwik_Tracker_GoalManager::REVENUE_PRECISION); + } + + protected function insertReports($recordName, $visitsToConversions) + { + foreach ($visitsToConversions as $idGoal => $table) { + $record = self::getRecordName($recordName, $idGoal); + $this->getProcessor()->insertBlobRecord($record, $table->getSerialized()); + } + $overviewTable = $this->getOverviewFromGoalTables($visitsToConversions); + $this->getProcessor()->insertBlobRecord(self::getRecordName($recordName), $overviewTable->getSerialized()); + } + + protected function getOverviewFromGoalTables($tableByGoal) + { + $overview = new Piwik_DataTable(); + foreach ($tableByGoal as $idGoal => $table) { + if ($this->isStandardGoal($idGoal)) { + $overview->addDataTable($table); + } + } + return $overview; + } - foreach ($dimensionsToQuery as $dimension => $recordName) { + protected function isStandardGoal($idGoal) + { + return !in_array($idGoal, $this->getEcommerceIdGoals()); + } + + protected function archiveEcommerceItems() + { + if (!$this->shouldArchiveEcommerceItems()) { + return false; + } + $this->initItemReports(); + foreach ($this->getItemsDimensions() as $dimension) { $query = $this->getProcessor()->queryEcommerceItems($dimension); if ($query == false) { continue; } + $this->aggregateFromEcommerceItems($query, $dimension); + } + $this->recordItemReports(); + } - while ($row = $query->fetch()) { - $label = $row['label']; - $ecommerceType = $row['ecommerceType']; - - if (empty($label)) { - // idaction==0 case: - // If we are querying any optional category, we do not include idaction=0 - // Otherwise we over-report in the Product Categories report - if ($recordName == 'AdditionalCategory') { - continue; - } - // Product Name/Category not defined" - if (class_exists('Piwik_CustomVariables')) { - $label = Piwik_CustomVariables_Archiver::LABEL_CUSTOM_VALUE_NOT_DEFINED; - } else { - $label = "Value not defined"; - } - } - // For carts, idorder = 0. To count abandoned carts, we must count visits with an abandoned cart - if ($ecommerceType == Piwik_Tracker_GoalManager::IDGOAL_CART) { - $row[Piwik_Archive::INDEX_ECOMMERCE_ORDERS] = $row[Piwik_Archive::INDEX_NB_VISITS]; - } - unset($row[Piwik_Archive::INDEX_NB_VISITS]); - unset($row['label']); - unset($row['ecommerceType']); - - $columnsToRound = array( - Piwik_Archive::INDEX_ECOMMERCE_ITEM_REVENUE, - Piwik_Archive::INDEX_ECOMMERCE_ITEM_QUANTITY, - Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE, - Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED, - ); - foreach ($columnsToRound as $column) { - if (isset($row[$column]) - && $row[$column] == round($row[$column]) - ) { - $row[$column] = round($row[$column]); - } - } - $items[$dimension][$ecommerceType][$label] = $row; + protected function initItemReports() + { + foreach ($this->getEcommerceIdGoals() as $ecommerceType) { + foreach ($this->dimensionRecord as $dimension => $record) { + $this->itemReports[$dimension][$ecommerceType] = new Piwik_DataArray(); } } + } - foreach ($this->dimensions as $dimension => $recordName) { - foreach (array(Piwik_Tracker_GoalManager::IDGOAL_CART, Piwik_Tracker_GoalManager::IDGOAL_ORDER) as $ecommerceType) { - if (!isset($items[$dimension][$ecommerceType])) { - continue; - } - $recordNameInsert = $recordName; + protected function recordItemReports() + { + /** @var Piwik_DataArray $array */ + foreach ($this->itemReports as $dimension => $itemAggregatesByType) { + foreach ($itemAggregatesByType as $ecommerceType => $itemAggregate) { + $recordName = $this->dimensionRecord[$dimension]; if ($ecommerceType == Piwik_Tracker_GoalManager::IDGOAL_CART) { - $recordNameInsert = self::getItemRecordNameAbandonedCart($recordName); - } - $table = $this->getProcessor()->getDataTableFromArray($items[$dimension][$ecommerceType]); - - // For "category" report, we aggregate all 5 category queries into one datatable - if ($dimension == 'idaction_category') { - foreach (array('idaction_category2', 'idaction_category3', 'idaction_category4', 'idaction_category5') as $categoryToSum) { - if (!empty($items[$categoryToSum][$ecommerceType])) { - $tableToSum = $this->getProcessor()->getDataTableFromArray($items[$categoryToSum][$ecommerceType]); - $table->addDataTable($tableToSum); - } - } + $recordName = self::getItemRecordNameAbandonedCart($recordName); } - $this->getProcessor()->insertBlobRecord($recordNameInsert, $table->getSerialized()); + $table = $this->getProcessor()->getDataTableFromDataArray($itemAggregate); + $this->getProcessor()->insertBlobRecord($recordName, $table->getSerialized()); + } + } + } + + protected function shouldArchiveEcommerceItems() + { + // Per item doesn't support segment + // Also, when querying Goal metrics for visitorType==returning, we wouldnt want to trigger an extra request + // event if it did support segment + // (when this is implemented, we should have shouldProcessReportsForPlugin() support partial archiving based on which metric is requested) + if (!$this->getProcessor()->getSegment()->isEmpty()) { + return false; + } + return true; + } + + protected function getItemsDimensions() + { + $dimensions = array_keys($this->dimensionRecord); + foreach ($this->getItemExtraCategories() as $category) { + $dimensions[] = $category; + } + return $dimensions; + } + + protected function getItemExtraCategories() + { + return array(self::CATEGORY2_FIELD, self::CATEGORY3_FIELD, self::CATEGORY4_FIELD, self::CATEGORY5_FIELD); + } + + protected function isItemExtraCategory($field) + { + return in_array($field, $this->getItemExtraCategories()); + } + + protected function aggregateFromEcommerceItems($query, $dimension) + { + while ($row = $query->fetch()) { + $ecommerceType = $row['ecommerceType']; + + $label = $this->cleanupRowGetLabel($row, $dimension); + if ($label === false) { + continue; + } + + // Aggregate extra categories in the Item categories array + if ($this->isItemExtraCategory($dimension)) { + $array = $this->itemReports[self::CATEGORY_FIELD][$ecommerceType]; + } else { + $array = $this->itemReports[$dimension][$ecommerceType]; + } + + $this->roundColumnValues($row); + $array->sumMetrics($label, $row); + } + } + + protected function cleanupRowGetLabel(&$row, $currentField) + { + $label = $row['label']; + if (empty($label)) { + // An empty additional category -> skip this iteration + if ($this->isItemExtraCategory($currentField)) { + return false; + } + $label = "Value not defined"; + // Product Name/Category not defined" + if (class_exists('Piwik_CustomVariables')) { + $label = Piwik_CustomVariables_Archiver::LABEL_CUSTOM_VALUE_NOT_DEFINED; } } + + if ($row['ecommerceType'] == Piwik_Tracker_GoalManager::IDGOAL_CART) { + // abandoned carts are the numner of visits with an abandoned cart + $row[Piwik_Archive::INDEX_ECOMMERCE_ORDERS] = $row[Piwik_Archive::INDEX_NB_VISITS]; + } + + unset($row[Piwik_Archive::INDEX_NB_VISITS]); + unset($row['label']); + unset($row['ecommerceType']); + + return $label; + } + + protected function roundColumnValues(&$row) + { + $columnsToRound = array( + Piwik_Archive::INDEX_ECOMMERCE_ITEM_REVENUE, + Piwik_Archive::INDEX_ECOMMERCE_ITEM_QUANTITY, + Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE, + Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED, + ); + foreach ($columnsToRound as $column) { + if (isset($row[$column]) + && $row[$column] == round($row[$column]) + ) { + $row[$column] = round($row[$column]); + } + } + } + + protected function getEcommerceIdGoals() + { + return array(Piwik_Tracker_GoalManager::IDGOAL_CART, Piwik_Tracker_GoalManager::IDGOAL_ORDER); + } + + static public function getItemRecordNameAbandonedCart($recordName) + { + return $recordName . '_Cart'; } /** @@ -267,8 +376,8 @@ class Piwik_Goals_Archiver extends Piwik_PluginsArchiver * Archive Ecommerce Items */ if ($this->shouldArchiveEcommerceItems()) { - $dataTableToSum = $this->dimensions; - foreach ($this->dimensions as $recordName) { + $dataTableToSum = $this->dimensionRecord; + foreach ($this->dimensionRecord as $recordName) { $dataTableToSum[] = self::getItemRecordNameAbandonedCart($recordName); } $this->getProcessor()->archiveDataTable($dataTableToSum); @@ -303,51 +412,13 @@ class Piwik_Goals_Archiver extends Piwik_PluginsArchiver // sum up the visits to conversion data table & the days to conversion data table $this->getProcessor()->archiveDataTable(array( - self::getRecordName(self::VISITS_UNTIL_RECORD_NAME, $goalId), - self::getRecordName(self::DAYS_UNTIL_CONV_RECORD_NAME, $goalId))); + self::getRecordName(self::VISITS_UNTIL_RECORD_NAME, $goalId), + self::getRecordName(self::DAYS_UNTIL_CONV_RECORD_NAME, $goalId))); } // sum up goal overview reports $this->getProcessor()->archiveDataTable(array( - self::getRecordName(self::VISITS_UNTIL_RECORD_NAME), - self::getRecordName(self::DAYS_UNTIL_CONV_RECORD_NAME))); - } - - protected function shouldArchiveEcommerceItems() - { - // Per item doesn't support segment - // Also, when querying Goal metrics for visitorType==returning, we wouldnt want to trigger an extra request - // event if it did support segment - // (when this is implemented, we should have shouldProcessReportsForPlugin() support partial archiving based on which metric is requested) - if (!$this->getProcessor()->getSegment()->isEmpty()) { - return false; - } - return true; - } - - static public function getItemRecordNameAbandonedCart($recordName) - { - return $recordName . '_Cart'; + self::getRecordName(self::VISITS_UNTIL_RECORD_NAME), + self::getRecordName(self::DAYS_UNTIL_CONV_RECORD_NAME))); } - - /** - * @param string $recordName 'nb_conversions' - * @param int|bool $idGoal idGoal to return the metrics for, or false to return overall - * @return string Archive record name - */ - static public function getRecordName($recordName, $idGoal = false) - { - $idGoalStr = ''; - if ($idGoal !== false) { - $idGoalStr = $idGoal . "_"; - } - return 'Goal_' . $idGoalStr . $recordName; - } - - private function getConversionRate($count) - { - $visits = $this->getProcessor()->getNumberOfVisits(); - return round(100 * $count / $visits, Piwik_Tracker_GoalManager::REVENUE_PRECISION); - } - }
\ No newline at end of file diff --git a/plugins/Provider/Archiver.php b/plugins/Provider/Archiver.php index 3cc703a0e2..692c3a72d2 100644 --- a/plugins/Provider/Archiver.php +++ b/plugins/Provider/Archiver.php @@ -11,19 +11,12 @@ class Piwik_Provider_Archiver extends Piwik_PluginsArchiver { const PROVIDER_RECORD_NAME = 'Provider_hostnameExt'; - protected $maximumRows; - - public function __construct($processor) - { - parent::__construct($processor); - $this->maximumRows = Piwik_Config::getInstance()->General['datatable_archiving_maximum_rows_standard']; - } + const PROVIDER_FIELD = "location_provider"; public function archiveDay() { - $labelSQL = "log_visit.location_provider"; - $metricsByProvider = $this->getProcessor()->getMetricsForLabel($labelSQL); - $tableProvider = $this->getProcessor()->getDataTableFromArray($metricsByProvider); + $metrics = $this->getProcessor()->getMetricsForDimension(self::PROVIDER_FIELD); + $tableProvider = $this->getProcessor()->getDataTableFromDataArray($metrics); $this->getProcessor()->insertBlobRecord(self::PROVIDER_RECORD_NAME, $tableProvider->getSerialized($this->maximumRows, null, Piwik_Archive::INDEX_NB_VISITS)); } diff --git a/plugins/Referers/API.php b/plugins/Referers/API.php index abec869e56..e194b67df2 100644 --- a/plugins/Referers/API.php +++ b/plugins/Referers/API.php @@ -129,7 +129,7 @@ class Piwik_Referers_API public function getKeywords($idSite, $period, $date, $segment = false, $expanded = false) { - $dataTable = $this->getDataTable(Piwik_Referers_Archiver::SEARCH_ENGINE_BY_KEYWORD_RECORD_NAME, $idSite, $period, $date, $segment, $expanded); + $dataTable = $this->getDataTable(Piwik_Referers_Archiver::KEYWORDS_RECORD_NAME, $idSite, $period, $date, $segment, $expanded); $dataTable = $this->handleKeywordNotDefined($dataTable); return $dataTable; } @@ -212,7 +212,7 @@ class Piwik_Referers_API public function getSearchEnginesFromKeywordId($idSite, $period, $date, $idSubtable, $segment = false) { - $dataTable = $this->getDataTable(Piwik_Referers_Archiver::SEARCH_ENGINE_BY_KEYWORD_RECORD_NAME, $idSite, $period, $date, $segment, $expanded = false, $idSubtable); + $dataTable = $this->getDataTable(Piwik_Referers_Archiver::KEYWORDS_RECORD_NAME, $idSite, $period, $date, $segment, $expanded = false, $idSubtable); $dataTable->queueFilter('ColumnCallbackAddMetadata', array('label', 'url', 'Piwik_getSearchEngineUrlFromName')); $dataTable->queueFilter('MetadataCallbackAddMetadata', array('url', 'logo', 'Piwik_getSearchEngineLogoFromUrl')); @@ -228,7 +228,7 @@ class Piwik_Referers_API public function getSearchEngines($idSite, $period, $date, $segment = false, $expanded = false) { - $dataTable = $this->getDataTable(Piwik_Referers_Archiver::KEYWORDS_BY_SEARCH_ENGINE_RECORD_NAME, $idSite, $period, $date, $segment, $expanded); + $dataTable = $this->getDataTable(Piwik_Referers_Archiver::SEARCH_ENGINES_RECORD_NAME, $idSite, $period, $date, $segment, $expanded); $dataTable->queueFilter('ColumnCallbackAddMetadata', array('label', 'url', 'Piwik_getSearchEngineUrlFromName')); $dataTable->queueFilter('MetadataCallbackAddMetadata', array('url', 'logo', 'Piwik_getSearchEngineLogoFromUrl')); return $dataTable; @@ -236,7 +236,7 @@ class Piwik_Referers_API public function getKeywordsFromSearchEngineId($idSite, $period, $date, $idSubtable, $segment = false) { - $dataTable = $this->getDataTable(Piwik_Referers_Archiver::KEYWORDS_BY_SEARCH_ENGINE_RECORD_NAME, $idSite, $period, $date, $segment, $expanded = false, $idSubtable); + $dataTable = $this->getDataTable(Piwik_Referers_Archiver::SEARCH_ENGINES_RECORD_NAME, $idSite, $period, $date, $segment, $expanded = false, $idSubtable); // get the search engine and create the URL to the search result page $searchEngines = $this->getSearchEngines($idSite, $period, $date, $segment); @@ -267,25 +267,25 @@ class Piwik_Referers_API public function getCampaigns($idSite, $period, $date, $segment = false, $expanded = false) { - $dataTable = $this->getDataTable(Piwik_Referers_Archiver::KEYWORD_BY_CAMPAIGN_RECORD_NAME, $idSite, $period, $date, $segment, $expanded); + $dataTable = $this->getDataTable(Piwik_Referers_Archiver::CAMPAIGNS_RECORD_NAME, $idSite, $period, $date, $segment, $expanded); return $dataTable; } public function getKeywordsFromCampaignId($idSite, $period, $date, $idSubtable, $segment = false) { - $dataTable = $this->getDataTable(Piwik_Referers_Archiver::KEYWORD_BY_CAMPAIGN_RECORD_NAME, $idSite, $period, $date, $segment, $expanded = false, $idSubtable); + $dataTable = $this->getDataTable(Piwik_Referers_Archiver::CAMPAIGNS_RECORD_NAME, $idSite, $period, $date, $segment, $expanded = false, $idSubtable); return $dataTable; } public function getWebsites($idSite, $period, $date, $segment = false, $expanded = false) { - $dataTable = $this->getDataTable(Piwik_Referers_Archiver::URL_BY_WEBSITE_RECORD_NAME, $idSite, $period, $date, $segment, $expanded); + $dataTable = $this->getDataTable(Piwik_Referers_Archiver::WEBSITES_RECORD_NAME, $idSite, $period, $date, $segment, $expanded); return $dataTable; } public function getUrlsFromWebsiteId($idSite, $period, $date, $idSubtable, $segment = false) { - $dataTable = $this->getDataTable(Piwik_Referers_Archiver::URL_BY_WEBSITE_RECORD_NAME, $idSite, $period, $date, $segment, $expanded = false, $idSubtable); + $dataTable = $this->getDataTable(Piwik_Referers_Archiver::WEBSITES_RECORD_NAME, $idSite, $period, $date, $segment, $expanded = false, $idSubtable); // the htmlspecialchars_decode call is for BC for before 1.1 // as the Referer URL was previously encoded in the log tables, but is now recorded raw $dataTable->queueFilter('ColumnCallbackAddMetadata', array('label', 'url', create_function('$label', 'return htmlspecialchars_decode($label);'))); @@ -308,7 +308,7 @@ class Piwik_Referers_API { require PIWIK_INCLUDE_PATH . '/core/DataFiles/Socials.php'; - $dataTable = $this->getDataTable( Piwik_Referers_Archiver::URL_BY_WEBSITE_RECORD_NAME, $idSite, $period, $date, $segment, $expanded); + $dataTable = $this->getDataTable( Piwik_Referers_Archiver::WEBSITES_RECORD_NAME, $idSite, $period, $date, $segment, $expanded); $dataTable->filter('ColumnCallbackDeleteRow', array('label', 'Piwik_Referrers_isSocialUrl')); @@ -341,7 +341,7 @@ class Piwik_Referers_API { require PIWIK_INCLUDE_PATH . '/core/DataFiles/Socials.php'; - $dataTable = $this->getDataTable( Piwik_Referers_Archiver::URL_BY_WEBSITE_RECORD_NAME, $idSite, $period, $date, $segment, $expanded = true); + $dataTable = $this->getDataTable( Piwik_Referers_Archiver::WEBSITES_RECORD_NAME, $idSite, $period, $date, $segment, $expanded = true); // get the social network domain referred to by $idSubtable $social = false; diff --git a/plugins/Referers/Archiver.php b/plugins/Referers/Archiver.php index 58907702ad..ddae742131 100644 --- a/plugins/Referers/Archiver.php +++ b/plugins/Referers/Archiver.php @@ -11,30 +11,21 @@ class Piwik_Referers_Archiver extends Piwik_PluginsArchiver { - const KEYWORDS_BY_SEARCH_ENGINE_RECORD_NAME = 'Referers_keywordBySearchEngine'; - const SEARCH_ENGINE_BY_KEYWORD_RECORD_NAME = 'Referers_searchEngineByKeyword'; - const KEYWORD_BY_CAMPAIGN_RECORD_NAME = 'Referers_keywordByCampaign'; - const URL_BY_WEBSITE_RECORD_NAME = 'Referers_urlByWebsite'; + const SEARCH_ENGINES_RECORD_NAME = 'Referers_keywordBySearchEngine'; + const KEYWORDS_RECORD_NAME = 'Referers_searchEngineByKeyword'; + const CAMPAIGNS_RECORD_NAME = 'Referers_keywordByCampaign'; + const WEBSITES_RECORD_NAME = 'Referers_urlByWebsite'; const REFERER_TYPE_RECORD_NAME = 'Referers_type'; - const METRIC_DISTINCT_SEARCH_ENGINE_RECORD_NAME = 'Referers_distinctSearchEngines'; const METRIC_DISTINCT_KEYWORD_RECORD_NAME = 'Referers_distinctKeywords'; const METRIC_DISTINCT_CAMPAIGN_RECORD_NAME = 'Referers_distinctCampaigns'; const METRIC_DISTINCT_WEBSITE_RECORD_NAME = 'Referers_distinctWebsites'; const METRIC_DISTINCT_URLS_RECORD_NAME = 'Referers_distinctWebsitesUrls'; - protected $columnToSortByBeforeTruncation; protected $maximumRowsInDataTableLevelZero; protected $maximumRowsInSubDataTable; - protected $metricsBySearchEngine = array(); - protected $metricsByKeyword = array(); - protected $metricsBySearchEngineAndKeyword = array(); - protected $metricsByKeywordAndSearchEngine = array(); - protected $metricsByWebsite = array(); - protected $metricsByWebsiteAndUrl = array(); - protected $metricsByCampaignAndKeyword = array(); - protected $metricsByCampaign = array(); - protected $metricsByType = array(); + /* @var array[Piwik_DataArray] $arrays */ + protected $arrays = array(); protected $distinctUrls = array(); function __construct($processor) @@ -47,6 +38,9 @@ class Piwik_Referers_Archiver extends Piwik_PluginsArchiver public function archiveDay() { + foreach ($this->getRecordNames() as $record) { + $this->arrays[$record] = new Piwik_DataArray(); + } $query = $this->getProcessor()->queryVisitsByDimension(array("referer_type", "referer_name", "referer_keyword", "referer_url")); $this->aggregateFromVisits($query); @@ -57,12 +51,22 @@ class Piwik_Referers_Archiver extends Piwik_PluginsArchiver $this->recordDayReports(); } + protected function getRecordNames() + { + return array( + self::REFERER_TYPE_RECORD_NAME, + self::KEYWORDS_RECORD_NAME, + self::SEARCH_ENGINES_RECORD_NAME, + self::WEBSITES_RECORD_NAME, + self::CAMPAIGNS_RECORD_NAME, + ); + } + protected function aggregateFromVisits($query) { while ($row = $query->fetch()) { $this->makeRefererTypeNonEmpty($row); $this->aggregateVisit($row); - $this->aggregateVisitByType($row); } } @@ -77,15 +81,32 @@ class Piwik_Referers_Archiver extends Piwik_PluginsArchiver { switch ($row['referer_type']) { case Piwik_Common::REFERER_TYPE_SEARCH_ENGINE: - $this->aggregateVisitBySearchEngine($row); + if (empty($row['referer_keyword'])) { + $row['referer_keyword'] = Piwik_Referers_API::LABEL_KEYWORD_NOT_DEFINED; + } + $searchEnginesArray = $this->getDataArray(self::SEARCH_ENGINES_RECORD_NAME); + $searchEnginesArray->sumMetricsVisits($row['referer_name'], $row); + $searchEnginesArray->sumMetricsVisitsPivot($row['referer_name'], $row['referer_keyword'], $row); + $keywordsDataArray = $this->getDataArray(self::KEYWORDS_RECORD_NAME); + $keywordsDataArray->sumMetricsVisits($row['referer_keyword'], $row); + $keywordsDataArray->sumMetricsVisitsPivot($row['referer_keyword'], $row['referer_name'], $row); break; case Piwik_Common::REFERER_TYPE_WEBSITE: - $this->aggregateVisitByWebsite($row); + $this->getDataArray(self::WEBSITES_RECORD_NAME)->sumMetricsVisits($row['referer_name'], $row); + $this->getDataArray(self::WEBSITES_RECORD_NAME)->sumMetricsVisitsPivot($row['referer_name'], $row['referer_url'], $row); + + $urlHash = substr(md5($row['referer_url']), 0, 10); + if (!isset($this->distinctUrls[$urlHash])) { + $this->distinctUrls[$urlHash] = true; + } break; case Piwik_Common::REFERER_TYPE_CAMPAIGN: - $this->aggregateVisitByCampaign($row); + if (!empty($row['referer_keyword'])) { + $this->getDataArray(self::CAMPAIGNS_RECORD_NAME)->sumMetricsVisitsPivot($row['referer_name'], $row['referer_keyword'], $row); + } + $this->getDataArray(self::CAMPAIGNS_RECORD_NAME)->sumMetricsVisits($row['referer_name'], $row); break; case Piwik_Common::REFERER_TYPE_DIRECT_ENTRY: @@ -96,70 +117,16 @@ class Piwik_Referers_Archiver extends Piwik_PluginsArchiver throw new Exception("Non expected referer_type = " . $row['referer_type']); break; } + $this->getDataArray(self::REFERER_TYPE_RECORD_NAME)->sumMetricsVisits($row['referer_type'], $row); } - protected function aggregateVisitBySearchEngine($row) - { - if (empty($row['referer_keyword'])) { - $row['referer_keyword'] = Piwik_Referers_API::LABEL_KEYWORD_NOT_DEFINED; - } - if (!isset($this->metricsBySearchEngine[$row['referer_name']])) { - $this->metricsBySearchEngine[$row['referer_name']] = $this->getProcessor()->makeEmptyRow(); - } - if (!isset($this->metricsByKeyword[$row['referer_keyword']])) { - $this->metricsByKeyword[$row['referer_keyword']] = $this->getProcessor()->makeEmptyRow(); - } - if (!isset($this->metricsBySearchEngineAndKeyword[$row['referer_name']][$row['referer_keyword']])) { - $this->metricsBySearchEngineAndKeyword[$row['referer_name']][$row['referer_keyword']] = $this->getProcessor()->makeEmptyRow(); - } - if (!isset($this->metricsByKeywordAndSearchEngine[$row['referer_keyword']][$row['referer_name']])) { - $this->metricsByKeywordAndSearchEngine[$row['referer_keyword']][$row['referer_name']] = $this->getProcessor()->makeEmptyRow(); - } - - $this->getProcessor()->sumMetrics($row, $this->metricsBySearchEngine[$row['referer_name']]); - $this->getProcessor()->sumMetrics($row, $this->metricsByKeyword[$row['referer_keyword']]); - $this->getProcessor()->sumMetrics($row, $this->metricsBySearchEngineAndKeyword[$row['referer_name']][$row['referer_keyword']]); - $this->getProcessor()->sumMetrics($row, $this->metricsByKeywordAndSearchEngine[$row['referer_keyword']][$row['referer_name']]); - } - - protected function aggregateVisitByWebsite($row) - { - if (!isset($this->metricsByWebsite[$row['referer_name']])) { - $this->metricsByWebsite[$row['referer_name']] = $this->getProcessor()->makeEmptyRow(); - } - $this->getProcessor()->sumMetrics($row, $this->metricsByWebsite[$row['referer_name']]); - - if (!isset($this->metricsByWebsiteAndUrl[$row['referer_name']][$row['referer_url']])) { - $this->metricsByWebsiteAndUrl[$row['referer_name']][$row['referer_url']] = $this->getProcessor()->makeEmptyRow(); - } - $this->getProcessor()->sumMetrics($row, $this->metricsByWebsiteAndUrl[$row['referer_name']][$row['referer_url']]); - - $urlHash = substr(md5($row['referer_url']), 0, 10); - if (!isset($this->distinctUrls[$urlHash])) { - $this->distinctUrls[$urlHash] = true; - } - } - - protected function aggregateVisitByCampaign($row) - { - if (!empty($row['referer_keyword'])) { - if (!isset($this->metricsByCampaignAndKeyword[$row['referer_name']][$row['referer_keyword']])) { - $this->metricsByCampaignAndKeyword[$row['referer_name']][$row['referer_keyword']] = $this->getProcessor()->makeEmptyRow(); - } - $this->getProcessor()->sumMetrics($row, $this->metricsByCampaignAndKeyword[$row['referer_name']][$row['referer_keyword']]); - } - if (!isset($this->metricsByCampaign[$row['referer_name']])) { - $this->metricsByCampaign[$row['referer_name']] = $this->getProcessor()->makeEmptyRow(); - } - $this->getProcessor()->sumMetrics($row, $this->metricsByCampaign[$row['referer_name']]); - } - - protected function aggregateVisitByType($row) + /** + * @param $name + * @return Piwik_DataArray + */ + protected function getDataArray($name) { - if (!isset($this->metricsByType[$row['referer_type']])) { - $this->metricsByType[$row['referer_type']] = $this->getProcessor()->makeEmptyRow(); - } - $this->getProcessor()->sumMetrics($row, $this->metricsByType[$row['referer_type']]); + return $this->arrays[$name]; } protected function aggregateFromConversions($query) @@ -172,16 +139,14 @@ class Piwik_Referers_Archiver extends Piwik_PluginsArchiver $skipAggregateByType = $this->aggregateConversion($row); if (!$skipAggregateByType) { - $this->aggregateConversionByType($row); + $this->getDataArray(self::REFERER_TYPE_RECORD_NAME)->sumMetricsGoals($row['referer_type'], $row); } } - $this->getProcessor()->enrichMetricsWithConversions($this->metricsByType); - $this->getProcessor()->enrichMetricsWithConversions($this->metricsBySearchEngine); - $this->getProcessor()->enrichMetricsWithConversions($this->metricsByKeyword); - $this->getProcessor()->enrichMetricsWithConversions($this->metricsByWebsite); - $this->getProcessor()->enrichMetricsWithConversions($this->metricsByCampaign); - $this->getProcessor()->enrichPivotMetricsWithConversions($this->metricsByCampaignAndKeyword); + foreach ($this->arrays as $dataArray) { + /* @var Piwik_DataArray $dataArray */ + $dataArray->enrichMetricsWithConversions(); + } } protected function aggregateConversion($row) @@ -189,15 +154,23 @@ class Piwik_Referers_Archiver extends Piwik_PluginsArchiver $skipAggregateByType = false; switch ($row['referer_type']) { case Piwik_Common::REFERER_TYPE_SEARCH_ENGINE: - $this->aggregateConversionBySearchEngine($row); + if (empty($row['referer_keyword'])) { + $row['referer_keyword'] = Piwik_Referers_API::LABEL_KEYWORD_NOT_DEFINED; + } + + $this->getDataArray(self::SEARCH_ENGINES_RECORD_NAME)->sumMetricsGoals($row['referer_name'], $row); + $this->getDataArray(self::KEYWORDS_RECORD_NAME)->sumMetricsGoals($row['referer_keyword'], $row); break; case Piwik_Common::REFERER_TYPE_WEBSITE: - $this->aggregateConversionByWebsite($row); + $this->getDataArray(self::WEBSITES_RECORD_NAME)->sumMetricsGoals($row['referer_name'], $row); break; case Piwik_Common::REFERER_TYPE_CAMPAIGN: - $this->aggregateConversionByCampaign($row); + if (!empty($row['referer_keyword'])) { + $this->getDataArray(self::CAMPAIGNS_RECORD_NAME)->sumMetricsGoalsPivot($row['referer_name'], $row['referer_keyword'], $row); + } + $this->getDataArray(self::CAMPAIGNS_RECORD_NAME)->sumMetricsGoals($row['referer_name'], $row); break; case Piwik_Common::REFERER_TYPE_DIRECT_ENTRY: @@ -213,52 +186,6 @@ class Piwik_Referers_Archiver extends Piwik_PluginsArchiver return $skipAggregateByType; } - protected function aggregateConversionBySearchEngine($row) - { - if (empty($row['referer_keyword'])) { - $row['referer_keyword'] = Piwik_Referers_API::LABEL_KEYWORD_NOT_DEFINED; - } - if (!isset($this->metricsBySearchEngine[$row['referer_name']][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) { - $this->metricsBySearchEngine[$row['referer_name']][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $this->getProcessor()->makeEmptyGoalRow($row['idgoal']); - } - if (!isset($this->metricsByKeyword[$row['referer_keyword']][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) { - $this->metricsByKeyword[$row['referer_keyword']][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $this->getProcessor()->makeEmptyGoalRow($row['idgoal']); - } - - $this->getProcessor()->sumGoalMetrics($row, $this->metricsBySearchEngine[$row['referer_name']][Piwik_Archive::INDEX_GOALS][$row['idgoal']]); - $this->getProcessor()->sumGoalMetrics($row, $this->metricsByKeyword[$row['referer_keyword']][Piwik_Archive::INDEX_GOALS][$row['idgoal']]); - } - - protected function aggregateConversionByWebsite($row) - { - if (!isset($this->metricsByWebsite[$row['referer_name']][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) { - $this->metricsByWebsite[$row['referer_name']][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $this->getProcessor()->makeEmptyGoalRow($row['idgoal']); - } - $this->getProcessor()->sumGoalMetrics($row, $this->metricsByWebsite[$row['referer_name']][Piwik_Archive::INDEX_GOALS][$row['idgoal']]); - } - - protected function aggregateConversionByCampaign($row) - { - if (!empty($row['referer_keyword'])) { - if (!isset($this->metricsByCampaignAndKeyword[$row['referer_name']][$row['referer_keyword']][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) { - $this->metricsByCampaignAndKeyword[$row['referer_name']][$row['referer_keyword']][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $this->getProcessor()->makeEmptyGoalRow($row['idgoal']); - } - $this->getProcessor()->sumGoalMetrics($row, $this->metricsByCampaignAndKeyword[$row['referer_name']][$row['referer_keyword']][Piwik_Archive::INDEX_GOALS][$row['idgoal']]); - } - if (!isset($this->metricsByCampaign[$row['referer_name']][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) { - $this->metricsByCampaign[$row['referer_name']][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $this->getProcessor()->makeEmptyGoalRow($row['idgoal']); - } - $this->getProcessor()->sumGoalMetrics($row, $this->metricsByCampaign[$row['referer_name']][Piwik_Archive::INDEX_GOALS][$row['idgoal']]); - } - - protected function aggregateConversionByType($row) - { - if (!isset($this->metricsByType[$row['referer_type']][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) { - $this->metricsByType[$row['referer_type']][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $this->getProcessor()->makeEmptyGoalRow($row['idgoal']); - } - $this->getProcessor()->sumGoalMetrics($row, $this->metricsByType[$row['referer_type']][Piwik_Archive::INDEX_GOALS][$row['idgoal']]); - } - /** * Records the daily stats (numeric or datatable blob) into the archive tables. * @@ -273,31 +200,21 @@ class Piwik_Referers_Archiver extends Piwik_PluginsArchiver protected function recordDayNumeric() { $numericRecords = array( - self::METRIC_DISTINCT_SEARCH_ENGINE_RECORD_NAME => count($this->metricsBySearchEngineAndKeyword), - self::METRIC_DISTINCT_KEYWORD_RECORD_NAME => count($this->metricsByKeywordAndSearchEngine), - self::METRIC_DISTINCT_CAMPAIGN_RECORD_NAME => count($this->metricsByCampaign), - self::METRIC_DISTINCT_WEBSITE_RECORD_NAME => count($this->metricsByWebsite), + self::METRIC_DISTINCT_SEARCH_ENGINE_RECORD_NAME => count($this->getDataArray(self::SEARCH_ENGINES_RECORD_NAME)), + self::METRIC_DISTINCT_KEYWORD_RECORD_NAME => count($this->getDataArray(self::KEYWORDS_RECORD_NAME)), + self::METRIC_DISTINCT_CAMPAIGN_RECORD_NAME => count($this->getDataArray(self::CAMPAIGNS_RECORD_NAME)), + self::METRIC_DISTINCT_WEBSITE_RECORD_NAME => count($this->getDataArray(self::WEBSITES_RECORD_NAME)), self::METRIC_DISTINCT_URLS_RECORD_NAME => count($this->distinctUrls), ); - foreach ($numericRecords as $name => $value) { - $this->getProcessor()->insertNumericRecord($name, $value); - } + $this->getProcessor()->insertNumericRecords($numericRecords); } protected function recordDayBlobs() { - $table = new Piwik_DataTable(); - $table->addRowsFromArrayWithIndexLabel($this->metricsByType); - $this->getProcessor()->insertBlobRecord(self::REFERER_TYPE_RECORD_NAME, $table->getSerialized()); - - $blobRecords = array( - self::KEYWORDS_BY_SEARCH_ENGINE_RECORD_NAME => $this->getProcessor()->getDataTableWithSubtablesFromArraysIndexedByLabel($this->metricsBySearchEngineAndKeyword, $this->metricsBySearchEngine), - self::SEARCH_ENGINE_BY_KEYWORD_RECORD_NAME => $this->getProcessor()->getDataTableWithSubtablesFromArraysIndexedByLabel($this->metricsByKeywordAndSearchEngine, $this->metricsByKeyword), - self::KEYWORD_BY_CAMPAIGN_RECORD_NAME => $this->getProcessor()->getDataTableWithSubtablesFromArraysIndexedByLabel($this->metricsByCampaignAndKeyword, $this->metricsByCampaign), - self::URL_BY_WEBSITE_RECORD_NAME => $this->getProcessor()->getDataTableWithSubtablesFromArraysIndexedByLabel($this->metricsByWebsiteAndUrl, $this->metricsByWebsite), - ); - foreach ($blobRecords as $recordName => $table) { + foreach ($this->getRecordNames() as $recordName) { + $dataArray = $this->getDataArray($recordName); + $table = $this->getProcessor()->getDataTableFromDataArray($dataArray); $blob = $table->getSerialized($this->maximumRowsInDataTableLevelZero, $this->maximumRowsInSubDataTable, $this->columnToSortByBeforeTruncation); $this->getProcessor()->insertBlobRecord($recordName, $blob); } @@ -305,35 +222,29 @@ class Piwik_Referers_Archiver extends Piwik_PluginsArchiver public function archivePeriod() { - $dataTableToSum = array( - self::REFERER_TYPE_RECORD_NAME, - self::KEYWORDS_BY_SEARCH_ENGINE_RECORD_NAME, - self::SEARCH_ENGINE_BY_KEYWORD_RECORD_NAME, - self::KEYWORD_BY_CAMPAIGN_RECORD_NAME, - self::URL_BY_WEBSITE_RECORD_NAME, - ); + $dataTableToSum = $this->getRecordNames(); $nameToCount = $this->getProcessor()->archiveDataTable($dataTableToSum, null, $this->maximumRowsInDataTableLevelZero, $this->maximumRowsInSubDataTable, $this->columnToSortByBeforeTruncation); $mappingFromArchiveName = array( self::METRIC_DISTINCT_SEARCH_ENGINE_RECORD_NAME => array('typeCountToUse' => 'level0', - 'nameTableToUse' => self::KEYWORDS_BY_SEARCH_ENGINE_RECORD_NAME, + 'nameTableToUse' => self::SEARCH_ENGINES_RECORD_NAME, ), self::METRIC_DISTINCT_KEYWORD_RECORD_NAME => array('typeCountToUse' => 'level0', - 'nameTableToUse' => self::SEARCH_ENGINE_BY_KEYWORD_RECORD_NAME, + 'nameTableToUse' => self::KEYWORDS_RECORD_NAME, ), self::METRIC_DISTINCT_CAMPAIGN_RECORD_NAME => array('typeCountToUse' => 'level0', - 'nameTableToUse' => self::KEYWORD_BY_CAMPAIGN_RECORD_NAME, + 'nameTableToUse' => self::CAMPAIGNS_RECORD_NAME, ), self::METRIC_DISTINCT_WEBSITE_RECORD_NAME => array('typeCountToUse' => 'level0', - 'nameTableToUse' => self::URL_BY_WEBSITE_RECORD_NAME, + 'nameTableToUse' => self::WEBSITES_RECORD_NAME, ), self::METRIC_DISTINCT_URLS_RECORD_NAME => array('typeCountToUse' => 'recursive', - 'nameTableToUse' => self::URL_BY_WEBSITE_RECORD_NAME, + 'nameTableToUse' => self::WEBSITES_RECORD_NAME, ), ); diff --git a/plugins/SEO/API.php b/plugins/SEO/API.php index 26ac00c5bd..bc94a2663d 100644 --- a/plugins/SEO/API.php +++ b/plugins/SEO/API.php @@ -100,8 +100,6 @@ class Piwik_SEO_API $data[Piwik_Translate('SEO_Dmoz')] = $dmozRank; } - $dataTable = new Piwik_DataTable(); - $dataTable->addRowsFromArrayWithIndexLabel($data); - return $dataTable; + return Piwik_DataTable::makeFromIndexedArray($data); } } diff --git a/plugins/Transitions/Transitions.php b/plugins/Transitions/Transitions.php index 765854bff7..b92ef87b8c 100644 --- a/plugins/Transitions/Transitions.php +++ b/plugins/Transitions/Transitions.php @@ -79,16 +79,15 @@ class Piwik_Transitions extends Piwik_Plugin // group by. when we group by both, we don't get a single column for the keyword but instead // one column per keyword + search engine url. this way, we could not get the top keywords using // the ranking query. - $dimension = 'referrer_data'; + $dimensions = array('referrer_data', 'referer_type'); $rankingQuery->addLabelColumn('referrer_data'); - $select = ' - CASE referer_type + $selects = array( + 'CASE referer_type WHEN ' . Piwik_Common::REFERER_TYPE_DIRECT_ENTRY . ' THEN \'\' WHEN ' . Piwik_Common::REFERER_TYPE_SEARCH_ENGINE . ' THEN referer_keyword WHEN ' . Piwik_Common::REFERER_TYPE_WEBSITE . ' THEN referer_url WHEN ' . Piwik_Common::REFERER_TYPE_CAMPAIGN . ' THEN CONCAT(referer_name, \' \', referer_keyword) - END AS referrer_data, - referer_type'; + END AS referrer_data'); // get one limited group per referrer type $rankingQuery->partitionResultIntoMultipleGroups('referer_type', array( @@ -98,14 +97,11 @@ class Piwik_Transitions extends Piwik_Plugin Piwik_Common::REFERER_TYPE_CAMPAIGN )); - $orderBy = '`' . Piwik_Archive::INDEX_NB_VISITS . '` DESC'; - $type = $this->getColumnTypeSuffix($actionType); $where = 'visit_entry_idaction_' . $type . ' = ' . intval($idaction); $metrics = array(Piwik_Archive::INDEX_NB_VISITS); - $data = $archiveProcessing->queryVisitsByDimension($dimension, $where, $metrics, $orderBy, - $rankingQuery, $select, $selectGeneratesLabelColumn = true); + $data = $archiveProcessing->queryVisitsByDimension($dimensions, $where, $selects, $metrics, $rankingQuery); $referrerData = array(); $referrerSubData = array(); @@ -132,7 +128,9 @@ class Piwik_Transitions extends Piwik_Plugin } } - return $archiveProcessing->getDataTableWithSubtablesFromArraysIndexedByLabel($referrerSubData, $referrerData); + //FIXMEA refactor after integration tests written + $array = new Piwik_DataArray($referrerData, $referrerSubData); + return Piwik_ArchiveProcessing_Day::getDataTableFromDataArray($array); } /** @@ -161,14 +159,16 @@ class Piwik_Transitions extends Piwik_Plugin $dimension = 'idaction_name_ref'; } - $addSelect = ' - 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 1 - WHEN log_action.type = ' . Piwik_Tracker_Action::TYPE_SITE_SEARCH . ' THEN 2 - ELSE 0 - END AS action_partition'; + $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 1 + WHEN log_action.type = ' . Piwik_Tracker_Action::TYPE_SITE_SEARCH . ' THEN 2 + ELSE 0 + END AS action_partition' + ); $where = ' log_link_visit_action.idaction_' . $type . ' = ' . intval($idaction); @@ -183,11 +183,8 @@ class Piwik_Transitions extends Piwik_Plugin $dimension = array($dimension); } - $orderBy = '`' . Piwik_Archive::INDEX_NB_ACTIONS . '` DESC'; - $metrics = array(Piwik_Archive::INDEX_NB_ACTIONS); - $data = $archiveProcessing->queryActionsByDimension($dimension, $where, $metrics, $orderBy, - $rankingQuery, $joinLogActionOn, $addSelect); + $data = $archiveProcessing->queryActionsByDimension($dimension, $where, $selects, $metrics, $rankingQuery, $joinLogActionOn); $loops = 0; $nbPageviews = 0; @@ -278,7 +275,7 @@ class Piwik_Transitions extends Piwik_Plugin // 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'; + $selects = array('log_action.name', 'log_action.url_prefix', 'log_action.type'); } else { // specific setup for page titles: $types[Piwik_Tracker_Action::TYPE_ACTION_NAME] = 'followingPages'; @@ -295,25 +292,25 @@ class Piwik_Transitions extends Piwik_Plugin ELSE log_action1.idaction END '; - $addSelect = ' - CASE + $selects = array( + 'CASE ' /* following site search */ . ' WHEN log_link_visit_action.idaction_url IS NULL THEN log_action2.name ' /* following page view: use page title */ . ' WHEN log_action1.type = ' . Piwik_Tracker_Action::TYPE_ACTION_URL . ' THEN log_action2.name ' /* following download or outlink: use url */ . ' ELSE log_action1.name - END AS name, - CASE + END AS name', + 'CASE ' /* following site search */ . ' WHEN log_link_visit_action.idaction_url IS NULL THEN log_action2.type ' /* following page view: use page title */ . ' WHEN log_action1.type = ' . Piwik_Tracker_Action::TYPE_ACTION_URL . ' THEN log_action2.type ' /* following download or outlink: use url */ . ' ELSE log_action1.type - END AS type, - NULL AS url_prefix - '; + END AS type', + 'NULL AS url_prefix' + ); } // these types are available for both titles and urls @@ -332,11 +329,8 @@ class Piwik_Transitions extends Piwik_Plugin . '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); + $data = $archiveProcessing->queryActionsByDimension($dimension, $where, $selects, $metrics, $rankingQuery, $joinLogActionColumn); $this->totalTransitionsToFollowingActions = 0; $dataTables = array(); diff --git a/plugins/UserCountry/API.php b/plugins/UserCountry/API.php index 15f5f69ecf..40432969e9 100644 --- a/plugins/UserCountry/API.php +++ b/plugins/UserCountry/API.php @@ -32,7 +32,7 @@ class Piwik_UserCountry_API public function getCountry($idSite, $period, $date, $segment = false) { - $dataTable = $this->getDataTable(Piwik_UserCountry_Archiver::VISITS_BY_COUNTRY_RECORD_NAME, $idSite, $period, $date, $segment); + $dataTable = $this->getDataTable(Piwik_UserCountry_Archiver::COUNTRY_RECORD_NAME, $idSite, $period, $date, $segment); // apply filter on the whole datatable in order the inline search to work (searches are done on "beautiful" label) $dataTable->filter('ColumnCallbackAddMetadata', array('label', 'code')); @@ -46,7 +46,7 @@ class Piwik_UserCountry_API public function getContinent($idSite, $period, $date, $segment = false) { - $dataTable = $this->getDataTable(Piwik_UserCountry_Archiver::VISITS_BY_COUNTRY_RECORD_NAME, $idSite, $period, $date, $segment); + $dataTable = $this->getDataTable(Piwik_UserCountry_Archiver::COUNTRY_RECORD_NAME, $idSite, $period, $date, $segment); $getContinent = array('Piwik_Common', 'getContinent'); $dataTable->filter('GroupBy', array('label', $getContinent)); @@ -68,7 +68,7 @@ class Piwik_UserCountry_API */ public function getRegion($idSite, $period, $date, $segment = false) { - $dataTable = $this->getDataTable(Piwik_UserCountry_Archiver::VISITS_BY_REGION_RECORD_NAME, $idSite, $period, $date, $segment); + $dataTable = $this->getDataTable(Piwik_UserCountry_Archiver::REGION_RECORD_NAME, $idSite, $period, $date, $segment); $separator = Piwik_UserCountry_Archiver::LOCATION_SEPARATOR; $unk = Piwik_Tracker_Visit::UNKNOWN_CODE; @@ -110,7 +110,7 @@ class Piwik_UserCountry_API */ public function getCity($idSite, $period, $date, $segment = false) { - $dataTable = $this->getDataTable(Piwik_UserCountry_Archiver::VISITS_BY_CITY_RECORD_NAME, $idSite, $period, $date, $segment); + $dataTable = $this->getDataTable(Piwik_UserCountry_Archiver::CITY_RECORD_NAME, $idSite, $period, $date, $segment); $separator = Piwik_UserCountry_Archiver::LOCATION_SEPARATOR; $unk = Piwik_Tracker_Visit::UNKNOWN_CODE; diff --git a/plugins/UserCountry/Archiver.php b/plugins/UserCountry/Archiver.php index ecd124d67f..fb69e89ff8 100644 --- a/plugins/UserCountry/Archiver.php +++ b/plugins/UserCountry/Archiver.php @@ -11,9 +11,9 @@ class Piwik_UserCountry_Archiver extends Piwik_PluginsArchiver { - const VISITS_BY_COUNTRY_RECORD_NAME = 'UserCountry_country'; - const VISITS_BY_REGION_RECORD_NAME = 'UserCountry_region'; - const VISITS_BY_CITY_RECORD_NAME = 'UserCountry_city'; + const COUNTRY_RECORD_NAME = 'UserCountry_country'; + const REGION_RECORD_NAME = 'UserCountry_region'; + const CITY_RECORD_NAME = 'UserCountry_city'; const DISTINCT_COUNTRIES_METRIC = 'UserCountry_distinctCountries'; // separate region, city & country info in stored report labels @@ -21,15 +21,28 @@ class Piwik_UserCountry_Archiver extends Piwik_PluginsArchiver private $latLongForCities = array(); - private $metricsByDimension = array(); + private $dataArrays = array(); protected $maximumRows; + const COUNTRY_FIELD = 'location_country'; + + const REGION_FIELD = 'location_region'; + + const CITY_FIELD = 'location_city'; + + protected $dimensions = array( self::COUNTRY_FIELD, self::REGION_FIELD, self::CITY_FIELD ); + + protected $arrays; + const LATITUDE_FIELD = 'location_latitude'; + const LONGITUDE_FIELD = 'location_longitude'; + + public function archiveDay() { - $this->metricsByDimension = array('location_country' => array(), - 'location_region' => array(), - 'location_city' => array()); + foreach($this->dimensions as $dimension) { + $this->arrays[$dimension] = new Piwik_DataArray(); + } $this->aggregateFromVisits(); $this->aggregateFromConversions(); $this->recordDayReports(); @@ -37,17 +50,9 @@ class Piwik_UserCountry_Archiver extends Piwik_PluginsArchiver protected function aggregateFromVisits() { - $dimensions = array_keys($this->metricsByDimension); - $query = $this->getProcessor()->queryVisitsByDimension( - $dimensions, - $where = '', - $metrics = false, - $orderBy = false, - $rankingQuery = null, - $addSelect = 'MAX(log_visit.location_latitude) as location_latitude, - MAX(log_visit.location_longitude) as location_longitude' - ); - + $additionalSelects = array('MAX(log_visit.location_latitude) as location_latitude', + 'MAX(log_visit.location_longitude) as location_longitude'); + $query = $this->getProcessor()->queryVisitsByDimension($this->dimensions, $where = false, $additionalSelects); if ($query === false) { return; } @@ -55,7 +60,11 @@ class Piwik_UserCountry_Archiver extends Piwik_PluginsArchiver while ($row = $query->fetch()) { $this->makeRegionCityLabelsUnique($row); $this->rememberCityLatLong($row); - $this->aggregateVisit($row); + + /* @var $dataArray Piwik_DataArray */ + foreach ($this->arrays as $dimension => $dataArray) { + $dataArray->sumMetricsVisits($row[$dimension], $row); + } } } @@ -66,61 +75,33 @@ class Piwik_UserCountry_Archiver extends Piwik_PluginsArchiver */ private function makeRegionCityLabelsUnique(&$row) { - static $locationColumns = array('location_region', 'location_country', 'location_city'); - - // to be on the safe side, remove the location separator from the region/city/country we - // get from the query - foreach ($locationColumns as $column) { + // remove the location separator from the region/city/country we get from the query + foreach ($this->dimensions as $column) { $row[$column] = str_replace(self::LOCATION_SEPARATOR, '', $row[$column]); } - if (!empty($row['location_region'])) // do not differentiate between unknown regions - { - $row['location_region'] = $row['location_region'] . self::LOCATION_SEPARATOR . $row['location_country']; + if (!empty($row[self::REGION_FIELD])) { + $row[self::REGION_FIELD] = $row[self::REGION_FIELD] . self::LOCATION_SEPARATOR . $row[self::COUNTRY_FIELD]; } - if (!empty($row['location_city'])) // do not differentiate between unknown cities - { - $row['location_city'] = $row['location_city'] . self::LOCATION_SEPARATOR . $row['location_region']; + if (!empty($row[self::CITY_FIELD])) { + $row[self::CITY_FIELD] = $row[self::CITY_FIELD] . self::LOCATION_SEPARATOR . $row[self::REGION_FIELD]; } } protected function rememberCityLatLong($row) { - $lat = $long = false; - if (!empty($row['location_city'])) { - if (!empty($row['location_latitude'])) { - $lat = $row['location_latitude']; - } - if (!empty($row['location_longitude'])) { - $long = $row['location_longitude']; - } - } - - // store latitude/longitude, if we should - if ($lat !== false && $long !== false - && empty($this->latLongForCities[$row['location_city']]) - ) { - $this->latLongForCities[$row['location_city']] = array($lat, $long); - } - } - - protected function aggregateVisit($row) - { - foreach ($this->metricsByDimension as $dimension => &$table) { - $label = (string)$row[$dimension]; - - if (!isset($table[$label])) { - $table[$label] = $this->getProcessor()->makeEmptyRow(); - } - $this->getProcessor()->sumMetrics($row, $table[$label]); + if ( !empty($row[self::CITY_FIELD]) + && !empty($row[self::LATITUDE_FIELD]) + && !empty($row[self::LONGITUDE_FIELD]) + && empty($this->latLongForCities[$row[self::CITY_FIELD]])) { + $this->latLongForCities[$row[self::CITY_FIELD]] = array($row[self::LATITUDE_FIELD], $row[self::LONGITUDE_FIELD]); } } protected function aggregateFromConversions() { - $dimensions = array_keys($this->metricsByDimension); - $query = $this->getProcessor()->queryConversionsByDimension($dimensions); + $query = $this->getProcessor()->queryConversionsByDimension($this->dimensions); if ($query === false) { return; @@ -129,36 +110,32 @@ class Piwik_UserCountry_Archiver extends Piwik_PluginsArchiver while ($row = $query->fetch()) { $this->makeRegionCityLabelsUnique($row); - $idGoal = $row['idgoal']; - foreach ($this->metricsByDimension as $dimension => &$table) { - $label = (string)$row[$dimension]; - - if (!isset($table[$label][Piwik_Archive::INDEX_GOALS][$idGoal])) { - $table[$label][Piwik_Archive::INDEX_GOALS][$idGoal] = $this->getProcessor()->makeEmptyGoalRow($idGoal); - } - $this->getProcessor()->sumGoalMetrics($row, $table[$label][Piwik_Archive::INDEX_GOALS][$idGoal]); + /* @var $dataArray Piwik_DataArray */ + foreach ($this->arrays as $dimension => $dataArray) { + $dataArray->sumMetricsGoals($row[$dimension], $row); } } - foreach ($this->metricsByDimension as &$table) { - $this->getProcessor()->enrichMetricsWithConversions($table); + /* @var $dataArray Piwik_DataArray */ + foreach ($this->arrays as $dataArray) { + $dataArray->enrichMetricsWithConversions(); } } protected function recordDayReports() { - $tableCountry = Piwik_ArchiveProcessing_Day::getDataTableFromArray($this->metricsByDimension['location_country']); - $this->getProcessor()->insertBlobRecord(self::VISITS_BY_COUNTRY_RECORD_NAME, $tableCountry->getSerialized()); + $tableCountry = Piwik_ArchiveProcessing_Day::getDataTableFromDataArray($this->arrays[self::COUNTRY_FIELD]); + $this->getProcessor()->insertBlobRecord(self::COUNTRY_RECORD_NAME, $tableCountry->getSerialized()); $this->getProcessor()->insertNumericRecord(self::DISTINCT_COUNTRIES_METRIC, $tableCountry->getRowsCount()); - $tableRegion = Piwik_ArchiveProcessing_Day::getDataTableFromArray($this->metricsByDimension['location_region']); + $tableRegion = Piwik_ArchiveProcessing_Day::getDataTableFromDataArray($this->arrays[self::REGION_FIELD]); $serialized = $tableRegion->getSerialized($this->maximumRows, $this->maximumRows, Piwik_Archive::INDEX_NB_VISITS); - $this->getProcessor()->insertBlobRecord(self::VISITS_BY_REGION_RECORD_NAME, $serialized); + $this->getProcessor()->insertBlobRecord(self::REGION_RECORD_NAME, $serialized); - $tableCity = Piwik_ArchiveProcessing_Day::getDataTableFromArray($this->metricsByDimension['location_city']); + $tableCity = Piwik_ArchiveProcessing_Day::getDataTableFromDataArray($this->arrays[self::CITY_FIELD]); $this->setLatitudeLongitude($tableCity); $serialized = $tableCity->getSerialized($this->maximumRows, $this->maximumRows, Piwik_Archive::INDEX_NB_VISITS); - $this->getProcessor()->insertBlobRecord(self::VISITS_BY_CITY_RECORD_NAME, $serialized); + $this->getProcessor()->insertBlobRecord(self::CITY_RECORD_NAME, $serialized); } /** @@ -185,14 +162,14 @@ class Piwik_UserCountry_Archiver extends Piwik_PluginsArchiver public function archivePeriod() { $dataTableToSum = array( - self::VISITS_BY_COUNTRY_RECORD_NAME, - self::VISITS_BY_REGION_RECORD_NAME, - self::VISITS_BY_CITY_RECORD_NAME, + self::COUNTRY_RECORD_NAME, + self::REGION_RECORD_NAME, + self::CITY_RECORD_NAME, ); $nameToCount = $this->getProcessor()->archiveDataTable($dataTableToSum); $this->getProcessor()->insertNumericRecord(self::DISTINCT_COUNTRIES_METRIC, - $nameToCount[self::VISITS_BY_COUNTRY_RECORD_NAME]['level0']); + $nameToCount[self::COUNTRY_RECORD_NAME]['level0']); } }
\ No newline at end of file diff --git a/plugins/UserSettings/API.php b/plugins/UserSettings/API.php index 4369d397b9..03ea9ac61d 100644 --- a/plugins/UserSettings/API.php +++ b/plugins/UserSettings/API.php @@ -189,7 +189,6 @@ class Piwik_UserSettings_API // walk through the results and calculate the percentage foreach ($tableArray as $key => $table) { - // get according browserType table foreach ($browserTypesArray AS $k => $browsers) { if ($k == $key) { diff --git a/plugins/UserSettings/Archiver.php b/plugins/UserSettings/Archiver.php index 7b43657eeb..a27db178ca 100644 --- a/plugins/UserSettings/Archiver.php +++ b/plugins/UserSettings/Archiver.php @@ -22,12 +22,11 @@ class Piwik_UserSettings_Archiver extends Piwik_PluginsArchiver const OS_RECORD_NAME = 'UserSettings_os'; const CONFIGURATION_RECORD_NAME = 'UserSettings_configuration'; - public function __construct($processor) - { - parent::__construct($processor); - $this->maximumRowsInDataTable = Piwik_Config::getInstance()->General['datatable_archiving_maximum_rows_standard']; - $this->columnToSortByBeforeTruncation = Piwik_Archive::INDEX_NB_VISITS; - } + const LANGUAGE_DIMENSION = "log_visit.location_browser_lang"; + const RESOLUTION_DIMENSION = "log_visit.config_resolution"; + const BROWSER_VERSION_DIMENSION = "CONCAT(log_visit.config_browser_name, ';', log_visit.config_browser_version)"; + const OS_DIMENSION = "log_visit.config_os"; + const CONFIGURATION_DIMENSION = "CONCAT(log_visit.config_os, ';', log_visit.config_browser_name, ';', log_visit.config_resolution)"; public function archiveDay() { @@ -41,17 +40,16 @@ class Piwik_UserSettings_Archiver extends Piwik_PluginsArchiver protected function aggregateByConfiguration() { - $labelSQL = "CONCAT(log_visit.config_os, ';', log_visit.config_browser_name, ';', log_visit.config_resolution)"; - $metrics = $this->getProcessor()->getMetricsForLabel($labelSQL); - $table = $this->getProcessor()->getDataTableFromArray($metrics); - $this->getProcessor()->insertBlobRecord(self::CONFIGURATION_RECORD_NAME, $table->getSerialized($this->maximumRowsInDataTable, null, $this->columnToSortByBeforeTruncation)); + $metrics = $this->getProcessor()->getMetricsForDimension(self::CONFIGURATION_DIMENSION); + $table = $this->getProcessor()->getDataTableFromDataArray($metrics); + $this->insertTable(self::CONFIGURATION_RECORD_NAME, $table); } protected function aggregateByOs() { - $metrics = $this->getProcessor()->getMetricsForLabel("log_visit.config_os"); - $table = $this->getProcessor()->getDataTableFromArray($metrics); - $this->getProcessor()->insertBlobRecord(self::OS_RECORD_NAME, $table->getSerialized($this->maximumRowsInDataTable, null, $this->columnToSortByBeforeTruncation)); + $metrics = $this->getProcessor()->getMetricsForDimension(self::OS_DIMENSION); + $table = $this->getProcessor()->getDataTableFromDataArray($metrics); + $this->insertTable(self::OS_RECORD_NAME, $table); } protected function aggregateByBrowser() @@ -62,18 +60,16 @@ class Piwik_UserSettings_Archiver extends Piwik_PluginsArchiver protected function aggregateByBrowserVersion() { - $labelSQL = "CONCAT(log_visit.config_browser_name, ';', log_visit.config_browser_version)"; - $metrics = $this->getProcessor()->getMetricsForLabel($labelSQL); - $tableBrowser = $this->getProcessor()->getDataTableFromArray($metrics); - - $this->getProcessor()->insertBlobRecord(self::BROWSER_RECORD_NAME, $tableBrowser->getSerialized($this->maximumRowsInDataTable, null, $this->columnToSortByBeforeTruncation)); + $metrics = $this->getProcessor()->getMetricsForDimension(self::BROWSER_VERSION_DIMENSION); + $tableBrowser = $this->getProcessor()->getDataTableFromDataArray($metrics); + $this->insertTable(self::BROWSER_RECORD_NAME, $tableBrowser); return $tableBrowser; } protected function aggregateByBrowserType(Piwik_DataTable $tableBrowser) { $tableBrowser->filter('GroupBy', array('label', 'Piwik_getBrowserFamily')); - $this->getProcessor()->insertBlobRecord(self::BROWSER_TYPE_RECORD_NAME, $tableBrowser->getSerialized()); + $this->insertTable(self::BROWSER_TYPE_RECORD_NAME, $tableBrowser); } protected function aggregateByResolutionAndScreenType() @@ -84,53 +80,58 @@ class Piwik_UserSettings_Archiver extends Piwik_PluginsArchiver protected function aggregateByResolution() { - $metrics = $this->getProcessor()->getMetricsForLabel("log_visit.config_resolution"); - $table = $this->getProcessor()->getDataTableFromArray($metrics); + $metrics = $this->getProcessor()->getMetricsForDimension(self::RESOLUTION_DIMENSION); + $table = $this->getProcessor()->getDataTableFromDataArray($metrics); $table->filter('ColumnCallbackDeleteRow', array('label', 'Piwik_UserSettings_keepStrlenGreater')); - $this->getProcessor()->insertBlobRecord(self::RESOLUTION_RECORD_NAME, $table->getSerialized($this->maximumRowsInDataTable, null, $this->columnToSortByBeforeTruncation)); + $this->insertTable(self::RESOLUTION_RECORD_NAME, $table); return $table; } protected function aggregateByScreenType(Piwik_DataTable $resolutions) { $resolutions->filter('GroupBy', array('label', 'Piwik_getScreenTypeFromResolution')); - $this->getProcessor()->insertBlobRecord(self::SCREEN_TYPE_RECORD_NAME, $resolutions->getSerialized()); + $this->insertTable(self::SCREEN_TYPE_RECORD_NAME, $resolutions); } protected function aggregateByPlugin() { - $toSelect = "sum(case log_visit.config_pdf when 1 then 1 else 0 end) as pdf, - sum(case log_visit.config_flash when 1 then 1 else 0 end) as flash, - sum(case log_visit.config_java when 1 then 1 else 0 end) as java, - sum(case log_visit.config_director when 1 then 1 else 0 end) as director, - sum(case log_visit.config_quicktime when 1 then 1 else 0 end) as quicktime, - sum(case log_visit.config_realplayer when 1 then 1 else 0 end) as realplayer, - sum(case log_visit.config_windowsmedia when 1 then 1 else 0 end) as windowsmedia, - sum(case log_visit.config_gears when 1 then 1 else 0 end) as gears, - sum(case log_visit.config_silverlight when 1 then 1 else 0 end) as silverlight, - sum(case log_visit.config_cookie when 1 then 1 else 0 end) as cookie "; - - $data = $this->getProcessor()->queryVisitsSimple($toSelect); - $table = $this->getProcessor()->getSimpleDataTableFromRow($data, Piwik_Archive::INDEX_NB_VISITS); - $this->getProcessor()->insertBlobRecord(self::PLUGIN_RECORD_NAME, $table->getSerialized()); + $selects = array( + "sum(case log_visit.config_pdf when 1 then 1 else 0 end) as pdf", + "sum(case log_visit.config_flash when 1 then 1 else 0 end) as flash", + "sum(case log_visit.config_java when 1 then 1 else 0 end) as java", + "sum(case log_visit.config_director when 1 then 1 else 0 end) as director", + "sum(case log_visit.config_quicktime when 1 then 1 else 0 end) as quicktime", + "sum(case log_visit.config_realplayer when 1 then 1 else 0 end) as realplayer", + "sum(case log_visit.config_windowsmedia when 1 then 1 else 0 end) as windowsmedia", + "sum(case log_visit.config_gears when 1 then 1 else 0 end) as gears", + "sum(case log_visit.config_silverlight when 1 then 1 else 0 end) as silverlight", + "sum(case log_visit.config_cookie when 1 then 1 else 0 end) as cookie" + ); + + $query = $this->getProcessor()->queryVisitsByDimension(array(), false, $selects, $metrics = array()); + $data = $query->fetch(); + $cleanRow = Piwik_DataAccess_LogAggregator::makeArrayOneColumn($data, Piwik_Archive::INDEX_NB_VISITS); + $table = Piwik_DataTable::makeFromIndexedArray($cleanRow); + $this->insertTable(self::PLUGIN_RECORD_NAME, $table); } protected function aggregateByLanguage() { - $query = $this->getProcessor()->queryVisitsByDimension("log_visit.location_browser_lang"); + $query = $this->getProcessor()->queryVisitsByDimension( array("label" => self::LANGUAGE_DIMENSION) ); $languageCodes = array_keys(Piwik_Common::getLanguagesList()); - $metricsByLanguage = array(); + $metricsByLanguage = new Piwik_DataArray(); while ($row = $query->fetch()) { $code = Piwik_Common::extractLanguageCodeFromBrowserLanguage($row['label'], $languageCodes); - - if (!isset($metricsByLanguage[$code])) { - $metricsByLanguage[$code] = $this->getProcessor()->makeEmptyRow(); - } - $this->getProcessor()->sumMetrics($row, $metricsByLanguage[$code]); + $metricsByLanguage->sumMetricsVisits($code, $row); } - $tableLanguage = $this->getProcessor()->getDataTableFromArray($metricsByLanguage); - $this->getProcessor()->insertBlobRecord(self::LANGUAGE_RECORD_NAME, $tableLanguage->getSerialized($this->maximumRowsInDataTable, null, $this->columnToSortByBeforeTruncation)); + $tableLanguage = $this->getProcessor()->getDataTableFromDataArray($metricsByLanguage); + $this->insertTable(self::LANGUAGE_RECORD_NAME, $tableLanguage); + } + + protected function insertTable($recordName, Piwik_DataTable $table) + { + return $this->getProcessor()->insertBlobRecord($recordName, $table->getSerialized($this->maximumRows, null, Piwik_Archive::INDEX_NB_VISITS)); } public function archivePeriod() @@ -145,7 +146,7 @@ class Piwik_UserSettings_Archiver extends Piwik_PluginsArchiver self::PLUGIN_RECORD_NAME, self::LANGUAGE_RECORD_NAME, ); - $this->getProcessor()->archiveDataTable($dataTableToSum, null, $this->maximumRowsInDataTable); + $this->getProcessor()->archiveDataTable($dataTableToSum, null, $this->maximumRows); } } diff --git a/plugins/VisitTime/Archiver.php b/plugins/VisitTime/Archiver.php index cd19d99c00..af811e0d2f 100644 --- a/plugins/VisitTime/Archiver.php +++ b/plugins/VisitTime/Archiver.php @@ -22,53 +22,49 @@ class Piwik_VisitTime_Archiver extends Piwik_PluginsArchiver protected function aggregateByServerTime() { - $metricsByServerTime = $this->getProcessor()->getMetricsForLabel("HOUR(log_visit.visit_last_action_time)"); - $query = $this->getProcessor()->queryConversionsByDimension("HOUR(log_conversion.server_time)"); - - if ($query === false) return; + $array = $this->getProcessor()->getMetricsForDimension( array("label" => "HOUR(log_visit.visit_last_action_time)" )) ; + $query = $this->getProcessor()->queryConversionsByDimension( array("label" => "HOUR(log_conversion.server_time)") ); + if ($query === false) { + return; + } while ($row = $query->fetch()) { - if (!isset($metricsByServerTime[$row['label']][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) { - $metricsByServerTime[$row['label']][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $this->getProcessor()->makeEmptyGoalRow($row['idgoal']); - } - $this->getProcessor()->sumGoalMetrics($row, $metricsByServerTime[$row['label']][Piwik_Archive::INDEX_GOALS][$row['idgoal']]); + $array->sumMetricsGoals($row['label'], $row); } - $this->getProcessor()->enrichMetricsWithConversions($metricsByServerTime); - - $metricsByServerTime = $this->convertServerTimeToLocalTimezone($metricsByServerTime); - $tableServerTime = $this->getProcessor()->getDataTableFromArray($metricsByServerTime); - $this->makeSureAllHoursAreSet($tableServerTime); - $this->getProcessor()->insertBlobRecord(self::SERVER_TIME_RECORD_NAME, $tableServerTime->getSerialized()); + $array->enrichMetricsWithConversions(); + $array = $this->convertTimeToLocalTimezone($array); + $this->ensureAllHoursAreSet($array); + $this->getProcessor()->insertBlobRecord(self::SERVER_TIME_RECORD_NAME, $this->getProcessor()->getDataTableFromDataArray($array)->getSerialized()); } protected function aggregateByLocalTime() { - $metricsByLocalTime = $this->getProcessor()->getMetricsForLabel("HOUR(log_visit.visitor_localtime)"); - $tableLocalTime = $this->getProcessor()->getDataTableFromArray($metricsByLocalTime); - $this->makeSureAllHoursAreSet($tableLocalTime); - $this->getProcessor()->insertBlobRecord(self::LOCAL_TIME_RECORD_NAME, $tableLocalTime->getSerialized()); + $array = $this->getProcessor()->getMetricsForDimension("HOUR(log_visit.visitor_localtime)"); + $this->ensureAllHoursAreSet($array); + $this->getProcessor()->insertBlobRecord(self::LOCAL_TIME_RECORD_NAME, $this->getProcessor()->getDataTableFromDataArray($array)->getSerialized()); } - protected function convertServerTimeToLocalTimezone($metricsByServerTime) + protected function convertTimeToLocalTimezone(Piwik_DataArray &$array) { $date = Piwik_Date::factory($this->getProcessor()->getStartDatetimeUTC())->toString(); $timezone = $this->getProcessor()->getSite()->getTimezone(); - $visitsByHourTz = array(); - foreach ($metricsByServerTime as $hour => $stats) { + + $converted = array(); + foreach ($array->getDataArray() as $hour => $stats) { $datetime = $date . ' ' . $hour . ':00:00'; $hourInTz = (int)Piwik_Date::factory($datetime, $timezone)->toString('H'); - $visitsByHourTz[$hourInTz] = $stats; + $converted[$hourInTz] = $stats; } - return $visitsByHourTz; + return new Piwik_DataArray($converted); } - private function makeSureAllHoursAreSet($table) + private function ensureAllHoursAreSet( Piwik_DataArray &$array) { + $data = $array->getDataArray(); for ($i = 0; $i <= 23; $i++) { - if ($table->getRowFromLabel($i) === false) { - $row = $this->getProcessor()->makeEmptyRowLabeled($i); - $table->addRow($row); + if (empty($data[$i])) { + $array->sumMetricsVisits( $i, Piwik_DataArray::makeEmptyRow()); } } } diff --git a/plugins/VisitorInterest/Archiver.php b/plugins/VisitorInterest/Archiver.php index f90ea3d2a0..5f0a15d3d0 100644 --- a/plugins/VisitorInterest/Archiver.php +++ b/plugins/VisitorInterest/Archiver.php @@ -90,49 +90,29 @@ class Piwik_VisitorInterest_Archiver extends Piwik_PluginsArchiver self::VISITS_COUNT_RECORD_NAME => 'vbvn', self::DAYS_SINCE_LAST_RECORD_NAME => 'dslv', ); - $row = $this->aggregateFromVisits($prefixes); + $aggregatesMetadata = array( + array('visit_total_time', self::getSecondsGap(), 'log_visit', $prefixes[self::TIME_SPENT_RECORD_NAME]), + array('visit_total_actions', self::$pageGap, 'log_visit', $prefixes[self::PAGES_VIEWED_RECORD_NAME]), + array('visitor_count_visits', self::$visitNumberGap, 'log_visit', $prefixes[self::VISITS_COUNT_RECORD_NAME]), + array('visitor_days_since_last', self::$daysSinceLastVisitGap, 'log_visit', $prefixes[self::DAYS_SINCE_LAST_RECORD_NAME], + $i_am_your_nightmare_DELETE_ME = true + ), + ); + $selects = array(); + foreach($aggregatesMetadata as $aggregateMetadata) { + $selectsFromRangedColumn = Piwik_DataAccess_LogAggregator::getSelectsFromRangedColumn($aggregateMetadata); + $selects = array_merge( $selects, $selectsFromRangedColumn); + } + $query = $this->getProcessor()->queryVisitsByDimension(array(), $where = false, $selects, array()); + $row = $query->fetch(); foreach($prefixes as $recordName => $selectAsPrefix) { - $processor = $this->getProcessor(); - $dataTable = $processor->getSimpleDataTableFromRow($row, Piwik_Archive::INDEX_NB_VISITS, $selectAsPrefix); - $processor->insertBlobRecord($recordName, $dataTable->getSerialized()); + $cleanRow = Piwik_DataAccess_LogAggregator::makeArrayOneColumn($row, Piwik_Archive::INDEX_NB_VISITS, $selectAsPrefix); + $dataTable = Piwik_DataTable::makeFromIndexedArray($cleanRow); + $this->getProcessor()->insertBlobRecord($recordName, $dataTable->getSerialized()); } } - protected function aggregateFromVisits($prefixes) - { - // extra condition for the SQL SELECT that makes sure only returning visits are counted - // when creating the 'days since last visit' report. the SELECT expression below it - // is used to count all new visits. - $daysSinceLastExtraCondition = 'and log_visit.visitor_returning = 1'; - $selectAs = $prefixes[self::DAYS_SINCE_LAST_RECORD_NAME] . 'General_NewVisits'; - $newVisitCountSelect = "sum(case when log_visit.visitor_returning = 0 then 1 else 0 end) as `$selectAs`"; - - $daysSinceLastVisitSelects = Piwik_DataAccess_LogAggregator::buildReduceByRangeSelect( - 'visitor_days_since_last', self::$daysSinceLastVisitGap, 'log_visit', $prefixes[self::DAYS_SINCE_LAST_RECORD_NAME], - $daysSinceLastExtraCondition); - - // create the select expressions to use - $timeGapSelects = Piwik_DataAccess_LogAggregator::buildReduceByRangeSelect( - 'visit_total_time', self::getSecondsGap(), 'log_visit', $prefixes[self::TIME_SPENT_RECORD_NAME]); - - $pageGapSelects = Piwik_DataAccess_LogAggregator::buildReduceByRangeSelect( - 'visit_total_actions', self::$pageGap, 'log_visit', $prefixes[self::PAGES_VIEWED_RECORD_NAME]); - - $visitsByVisitNumSelects = Piwik_DataAccess_LogAggregator::buildReduceByRangeSelect( - 'visitor_count_visits', self::$visitNumberGap, 'log_visit', $prefixes[self::VISITS_COUNT_RECORD_NAME]); - - - array_unshift($daysSinceLastVisitSelects, $newVisitCountSelect); - - $selects = array_merge( - $timeGapSelects, $pageGapSelects, $visitsByVisitNumSelects, $daysSinceLastVisitSelects); - - // select data for every report - $row = $this->getProcessor()->queryVisitsSimple(implode(',', $selects)); - return $row; - } - /** * Transforms and returns the set of ranges used to calculate the 'visits by total time' * report from ranges in minutes to equivalent ranges in seconds. diff --git a/tests/PHPUnit/Integration/TwoVisitsWithCustomVariables_SegmentMatchVisitorTypeTest.php b/tests/PHPUnit/Integration/TwoVisitsWithCustomVariables_SegmentMatchVisitorTypeTest.php index 17bc92db2d..14d9c78092 100755 --- a/tests/PHPUnit/Integration/TwoVisitsWithCustomVariables_SegmentMatchVisitorTypeTest.php +++ b/tests/PHPUnit/Integration/TwoVisitsWithCustomVariables_SegmentMatchVisitorTypeTest.php @@ -87,6 +87,9 @@ class Test_Piwik_Integration_TwoVisitsWithCustomVariables_SegmentMatchVisitorTyp foreach ($tests as $table => $expectedRows) { $sql = "SELECT count(*) FROM " . Piwik_Common::prefixTable($table); $countBlobs = Zend_Registry::get('db')->fetchOne($sql); + if($expectedRows != $countBlobs) { + var_export(Zend_Registry::get('db')->fetchAll("SELECT * FROM " . Piwik_Common::prefixTable($table))); + } $this->assertEquals($expectedRows, $countBlobs, "$table: %s"); } } |