Ubuntu

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; } }