1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
|
<?php
/**
* Matomo - free/libre analytics platform
*
* @link https://matomo.org
* @license https://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
*/
namespace Piwik\Plugins\CoreConsole\Commands;
use Piwik\ArchiveProcessor\Parameters;
use Piwik\DataAccess\LogAggregator;
use Piwik\Development;
use Piwik\Metrics;
use Piwik\Period\Factory;
use Piwik\Plugin\ConsoleCommand;
use Piwik\Plugins\SitesManager\Model;
use Piwik\Segment;
use Piwik\Site;
class GetSegmentSql extends ConsoleCommand
{
public function isEnabled(): bool
{
return Development::isEnabled();
}
protected function configure()
{
$this->setName('development:get-segment-sql');
$this->setDescription('Print out the SQL used to query for a segment. Used for debugging or diagnosing segment issues. The site ID and dates are hardcoded in the query.');
$this->addRequiredValueOption('segment', null, 'The segment, correctly encoded.');
$this->addRequiredValueOption('idSites', null, 'Comma separated list of site IDs for the segment. (optional)');
$this->addRequiredValueOption('queryType', null, 'The query type to generate: visit, action or conversion');
}
protected function doExecute(): int
{
$input = $this->getInput();
$output = $this->getOutput();
$idSites = $input->getOption('idSites') ?: '';
$idSites = explode(',', $idSites);
$idSites = array_map('intval', $idSites);
$idSites = array_filter($idSites);
$queryType = $input->getOption('queryType');
$segment = $input->getOption('segment');
$segment = new Segment($segment, $idSites);
$model = new Model();
$allIdSites = $model->getSitesId();
$idSiteInQuery = reset($allIdSites);
$params = new Parameters(new Site($idSiteInQuery), Factory::build('day', 'today'), $segment);
$logAggregator = new LogAggregator($params);
switch ($queryType) {
case 'visit':
$query = $logAggregator->generateQuery(
implode(LogAggregator::FIELDS_SEPARATOR, [
Metrics::INDEX_NB_UNIQ_VISITORS => "count(distinct " . LogAggregator::LOG_VISIT_TABLE . ".idvisitor)",
Metrics::INDEX_NB_VISITS => "count(*)",
]),
[LogAggregator::LOG_VISIT_TABLE],
$logAggregator->getWhereStatement(LogAggregator::LOG_VISIT_TABLE, LogAggregator::VISIT_DATETIME_FIELD),
'',
''
);
break;
case 'action':
$query = $logAggregator->generateQuery(
implode(LogAggregator::FIELDS_SEPARATOR, [
Metrics::INDEX_NB_VISITS => "count(distinct " . LogAggregator::LOG_ACTIONS_TABLE . ".idvisit)",
Metrics::INDEX_NB_UNIQ_VISITORS => "count(distinct " . LogAggregator::LOG_ACTIONS_TABLE . ".idvisitor)",
Metrics::INDEX_NB_ACTIONS => "count(*)",
]),
[LogAggregator::LOG_ACTIONS_TABLE],
$logAggregator->getWhereStatement(LogAggregator::LOG_ACTIONS_TABLE, LogAggregator::ACTION_DATETIME_FIELD),
'',
''
);
break;
case 'conversion':
$query = $logAggregator->generateQuery(
implode(LogAggregator::FIELDS_SEPARATOR, [
Metrics::INDEX_GOAL_NB_CONVERSIONS => "count(*)",
Metrics::INDEX_GOAL_NB_VISITS_CONVERTED => "count(distinct " . LogAggregator::LOG_CONVERSION_TABLE . ".idvisit)",
]),
[LogAggregator::LOG_CONVERSION_TABLE],
$logAggregator->getWhereStatement(LogAggregator::LOG_CONVERSION_TABLE, LogAggregator::CONVERSION_DATETIME_FIELD),
'',
''
);
break;
default:
throw new \InvalidArgumentException('Invalid value for --queryType, must be one of the following: visit, action, conversion');
}
$output->writeln("QUERY: " . $query['sql']);
foreach ($query['bind'] as $key => $value) {
$output->writeln(' BIND #' . $key . ': ' . $value);
}
return self::SUCCESS;
}
}
|
