1<?php 2/** 3 * Matomo - free/libre analytics platform 4 * 5 * @link https://matomo.org 6 * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later 7 * 8 */ 9namespace Piwik\Plugins\CustomDimensions; 10 11use Piwik\Common; 12use Piwik\Config; 13use Piwik\Metrics; 14use Piwik\Plugins\Actions\Metrics as ActionsMetrics; 15use Piwik\Plugins\CustomDimensions\Dao\Configuration; 16use Piwik\Plugins\CustomDimensions\Dao\LogTable; 17use Piwik\RankingQuery; 18use Piwik\Tracker; 19use Piwik\ArchiveProcessor; 20 21/** 22 * Archives reports for each active Custom Dimension of a website. 23 */ 24class Archiver extends \Piwik\Plugin\Archiver 25{ 26 const LABEL_CUSTOM_VALUE_NOT_DEFINED = "Value not defined"; 27 private $recordNames = array(); 28 29 /** 30 * @var DataArray 31 */ 32 protected $dataArray; 33 protected $maximumRowsInDataTableLevelZero; 34 protected $maximumRowsInSubDataTable; 35 36 /** 37 * @var ArchiveProcessor 38 */ 39 private $processor; 40 41 /** 42 * @var int 43 */ 44 private $rankingQueryLimit; 45 46 function __construct($processor) 47 { 48 parent::__construct($processor); 49 50 $this->processor = $processor; 51 52 $this->maximumRowsInDataTableLevelZero = Config::getInstance()->General['datatable_archiving_maximum_rows_custom_dimensions']; 53 $this->maximumRowsInSubDataTable = Config::getInstance()->General['datatable_archiving_maximum_rows_subtable_custom_dimensions']; 54 $this->rankingQueryLimit = $this->getRankingQueryLimit(); 55 } 56 57 public static function buildRecordNameForCustomDimensionId($id) 58 { 59 return 'CustomDimensions_Dimension' . (int) $id; 60 } 61 62 private function getRecordNames() 63 { 64 if (!empty($this->recordNames)) { 65 return $this->recordNames; 66 } 67 68 $dimensions = $this->getActiveCustomDimensions(); 69 70 foreach ($dimensions as $dimension) { 71 $this->recordNames[] = self::buildRecordNameForCustomDimensionId($dimension['idcustomdimension']); 72 } 73 74 return $this->recordNames; 75 } 76 77 private function getActiveCustomDimensions() 78 { 79 $idSite = $this->processor->getParams()->getSite()->getId(); 80 81 $config = new Configuration(); 82 $dimensions = $config->getCustomDimensionsForSite($idSite); 83 84 $active = array(); 85 foreach ($dimensions as $index => $dimension) { 86 if ($dimension['active']) { 87 $active[] = $dimension; 88 } 89 } 90 91 return $active; 92 } 93 94 public function aggregateMultipleReports() 95 { 96 $columnsAggregationOperation = null; 97 98 $this->getProcessor()->aggregateDataTableRecords( 99 $this->getRecordNames(), 100 $this->maximumRowsInDataTableLevelZero, 101 $this->maximumRowsInSubDataTable, 102 $columnToSort = Metrics::INDEX_NB_VISITS, 103 $columnsAggregationOperation, 104 $columnsToRenameAfterAggregation = null, 105 $countRowsRecursive = array()); 106 } 107 108 public function aggregateDayReport() 109 { 110 $dimensions = $this->getActiveCustomDimensions(); 111 foreach ($dimensions as $dimension) { 112 $this->dataArray = new DataArray(); 113 114 $valueField = LogTable::buildCustomDimensionColumnName($dimension); 115 $dimensions = array($valueField); 116 117 if ($dimension['scope'] === CustomDimensions::SCOPE_VISIT) { 118 $this->aggregateFromVisits($valueField, $dimensions, " log_visit.$valueField is not null"); 119 $this->aggregateFromConversions($valueField, $dimensions, " log_conversion.$valueField is not null"); 120 } elseif ($dimension['scope'] === CustomDimensions::SCOPE_ACTION) { 121 $this->aggregateFromActions($valueField); 122 } 123 124 $this->dataArray->enrichMetricsWithConversions(); 125 $table = $this->dataArray->asDataTable(); 126 127 $blob = $table->getSerialized( 128 $this->maximumRowsInDataTableLevelZero, $this->maximumRowsInSubDataTable, 129 $columnToSort = Metrics::INDEX_NB_VISITS 130 ); 131 132 $recordName = self::buildRecordNameForCustomDimensionId($dimension['idcustomdimension']); 133 $this->getProcessor()->insertBlobRecord($recordName, $blob); 134 135 Common::destroy($table); 136 unset($this->dataArray); 137 } 138 } 139 140 protected function aggregateFromVisits($valueField, $dimensions, $where) 141 { 142 if ($this->rankingQueryLimit > 0) { 143 $rankingQuery = new RankingQuery($this->rankingQueryLimit); 144 $rankingQuery->addLabelColumn($dimensions[0]); 145 146 $query = $this->getLogAggregator()->queryVisitsByDimension($dimensions, $where, [], false, $rankingQuery, false, -1, 147 $rankingQueryGenerate = true); 148 } else { 149 $query = $this->getLogAggregator()->queryVisitsByDimension($dimensions, $where); 150 } 151 152 while ($row = $query->fetch()) { 153 $value = $this->cleanCustomDimensionValue($row[$valueField]); 154 155 $this->dataArray->sumMetricsVisits($value, $row); 156 } 157 } 158 159 protected function aggregateFromConversions($valueField, $dimensions, $where) 160 { 161 if ($this->rankingQueryLimit > 0) { 162 $rankingQuery = new RankingQuery($this->rankingQueryLimit); 163 $rankingQuery->addLabelColumn([$dimensions[0], 'idgoal']); 164 165 $query = $this->getLogAggregator()->queryConversionsByDimension($dimensions, $where, false, [], $rankingQuery, $rankingQueryGenerate = true); 166 } else { 167 $query = $this->getLogAggregator()->queryConversionsByDimension($dimensions, $where); 168 } 169 170 while ($row = $query->fetch()) { 171 $value = $this->cleanCustomDimensionValue($row[$valueField]); 172 173 $this->dataArray->sumMetricsGoals($value, $row); 174 } 175 } 176 177 public function queryCustomDimensionActions(DataArray $dataArray, $valueField, $additionalWhere = '') 178 { 179 $metricsConfig = ActionsMetrics::getActionMetrics(); 180 181 $metricIds = array_keys($metricsConfig); 182 $metricIds[] = Metrics::INDEX_PAGE_SUM_TIME_SPENT; 183 $metricIds[] = Metrics::INDEX_BOUNCE_COUNT; 184 $metricIds[] = Metrics::INDEX_PAGE_EXIT_NB_VISITS; 185 $dataArray->setActionMetricsIds($metricIds); 186 187 $select = "log_link_visit_action.$valueField, 188 log_action.name as url, 189 sum(log_link_visit_action.time_spent) as `" . Metrics::INDEX_PAGE_SUM_TIME_SPENT . "`, 190 sum(case log_visit.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) as `" . Metrics::INDEX_BOUNCE_COUNT . "`, 191 sum(IF(log_visit.last_idlink_va = log_link_visit_action.idlink_va, 1, 0)) as `" . Metrics::INDEX_PAGE_EXIT_NB_VISITS . "`"; 192 193 $select = $this->addMetricsToSelect($select, $metricsConfig); 194 195 $from = array( 196 "log_link_visit_action", 197 array( 198 "table" => "log_visit", 199 "joinOn" => "log_visit.idvisit = log_link_visit_action.idvisit" 200 ), 201 array( 202 "table" => "log_action", 203 "joinOn" => "log_link_visit_action.idaction_url = log_action.idaction" 204 ) 205 ); 206 207 $where = $this->getLogAggregator()->getWhereStatement('log_link_visit_action', 'server_time'); 208 $where .= " AND log_link_visit_action.$valueField is not null"; 209 210 if (!empty($additionalWhere)) { 211 $where .= ' AND ' . $additionalWhere; 212 } 213 214 $groupBy = "log_link_visit_action.$valueField, url"; 215 $orderBy = "`" . Metrics::INDEX_PAGE_NB_HITS . "` DESC"; 216 217 // get query with segmentation 218 $logAggregator = $this->getLogAggregator(); 219 $query = $logAggregator->generateQuery($select, $from, $where, $groupBy, $orderBy); 220 221 if ($this->rankingQueryLimit > 0) { 222 $rankingQuery = new RankingQuery($this->rankingQueryLimit); 223 $rankingQuery->addLabelColumn(array($valueField, 'url')); 224 225 $sumMetrics = [ 226 Metrics::INDEX_PAGE_SUM_TIME_SPENT, 227 Metrics::INDEX_BOUNCE_COUNT, 228 Metrics::INDEX_PAGE_EXIT_NB_VISITS, 229 // NOTE: INDEX_NB_UNIQ_VISITORS is summed in LogAggregator's queryActionsByDimension, so we do it here as well 230 Metrics::INDEX_NB_UNIQ_VISITORS, 231 ]; 232 $rankingQuery->addColumn($sumMetrics, 'sum'); 233 234 foreach ($metricsConfig as $column => $config) { 235 if (empty($config['aggregation'])) { 236 continue; 237 } 238 $rankingQuery->addColumn($column, $config['aggregation']); 239 } 240 241 $query['sql'] = $rankingQuery->generateRankingQuery($query['sql']); 242 } 243 244 $db = $logAggregator->getDb(); 245 $resultSet = $db->query($query['sql'], $query['bind']); 246 247 return $resultSet; 248 } 249 250 protected function aggregateFromActions($valueField) 251 { 252 $resultSet = $this->queryCustomDimensionActions($this->dataArray, $valueField); 253 254 while ($row = $resultSet->fetch()) { 255 $label = $row[$valueField]; 256 $label = $this->cleanCustomDimensionValue($label); 257 258 $this->dataArray->sumMetricsActions($label, $row); 259 260 // make sure we always work with normalized URL no matter how the individual action stores it 261 $normalized = Tracker\PageUrl::normalizeUrl($row['url']); 262 $row['url'] = $normalized['url']; 263 264 $subLabel = $row['url']; 265 266 if (empty($subLabel)) { 267 continue; 268 } 269 270 $this->dataArray->sumMetricsActionCustomDimensionsPivot($label, $subLabel, $row); 271 } 272 } 273 274 private function addMetricsToSelect($select, $metricsConfig) 275 { 276 if (!empty($metricsConfig)) { 277 foreach ($metricsConfig as $metric => $config) { 278 $select .= ', ' . $config['query'] . " as `" . $metric . "`"; 279 } 280 } 281 282 return $select; 283 } 284 285 protected function cleanCustomDimensionValue($value) 286 { 287 if (isset($value) && strlen($value)) { 288 return $value; 289 } 290 291 return self::LABEL_CUSTOM_VALUE_NOT_DEFINED; 292 } 293 294 private function getRankingQueryLimit() 295 { 296 $configGeneral = Config::getInstance()->General; 297 $configLimit = max($configGeneral['archiving_ranking_query_row_limit'], 10 * $this->maximumRowsInDataTableLevelZero); 298 $limit = $configLimit == 0 ? 0 : max( 299 $configLimit, 300 $this->maximumRowsInDataTableLevelZero 301 ); 302 return $limit; 303 } 304 305} 306