Ubuntu
setName('core:calculate-conversion-pages');
$this->setDescription('Calculate the pages before metric for historic conversions');
$this->addOptionalValueOption('dates', null, 'Calculate for conversions in this date range. Eg, 2012-01-01,2013-01-01', null);
$this->addOptionalValueOption('last-n', null, 'Calculate just the last n conversions', 0);
$this->addOptionalValueOption('idsite', null, 'Calculate for conversions belonging to the site with this ID. Comma separated list of website id. Eg, 1, 2, 3, etc. By default conversions from all sites are calculated.', null);
$this->addOptionalValueOption('idgoal', null, 'Calculate conversions for this goal. A comma separated list of goal ids can be used only if a single site is specified. Eg, 1, 2, 3, etc. By default conversions for all goals are calculated.', null);
$this->addOptionalValueOption('force-recalc', null, 'Recalculate for conversions which already have a pages before value', 0);
}
protected function doExecute() : int
{
$dates = $this->getInput()->getOption('dates');
$lastN = $this->getInput()->getOption('last-n');
$forceRecalc = $this->getInput()->getOption('force-recalc');
$idSite = $this->getSitesToCalculate();
$idGoal = $this->getGoalsToCalculate();
if (!$lastN && !$dates) {
throw new \InvalidArgumentException("No date range or last N option supplied. Calculating pages before for all conversions by default is not allowed, you must specify a date range using the --dates option or a last N count using the --last-n option");
}
if ($lastN && $dates) {
throw new \InvalidArgumentException("The last N option cannot be used with a date range, please choose just one of these options");
}
if (!is_numeric($lastN)) {
throw new \InvalidArgumentException("The last N option must be a number");
}
$from = null;
$to = null;
if (!empty($dates)) {
[$from, $to] = $this->getDateRangeToCalculate($dates);
}
$output = $this->getOutput();
$output->writeln(sprintf("Preparing to calculate the pages before metric for %s conversions belonging to %s %sfor %s.", $lastN ? "the last " . $lastN : 'all', $idSite ? "website {$idSite}" : "ALL websites", !empty($dates) ? "between " . $from . " and " . $to . " " : '', $idGoal ? "goal id {$idGoal}" : "ALL goals"));
$timer = new Timer();
$queries = $this->getQueries($from, $to, $lastN, $idSite, $idGoal, $forceRecalc);
$totalCalculated = 0;
foreach ($queries as $query) {
try {
$result = Db::query($query['sql'], $query['bind']);
} catch (\Exception $ex) {
$output->writeln("Exception executing query " . $query['sql'] . " with parameters " . json_encode($query['bind']));
throw $ex;
}
$calcCount = $result->rowCount();
$totalCalculated += $calcCount;
$output->write(".");
}
$this->writeSuccessMessage("Successfully calculated the pages before metric for {$totalCalculated} conversions. {$timer}");
return self::SUCCESS;
}
/**
* Static method to calculate conversion for today and yesterday, for all sites and goals.
* Called by the migration updater
*
*/
public static function calculateYesterdayAndToday() : void
{
$migration = StaticContainer::get(MigrationFactory::class);
$queries = self::getQueries(Date::factory('yesterday')->getDatetime(), Date::factory('today')->getEndOfDay()->getDatetime());
$migrations = [];
foreach ($queries as $query) {
$migrations[] = $migration->db->boundSql($query['sql'], $query['bind']);
}
$updater = StaticContainer::get(Updater::class);
$updater->executeMigrations(__FILE__, $migrations);
}
/**
* Validate dates parameter
*
* @return Date[]
*/
private function getDateRangeToCalculate(string $dates) : ?array
{
$parts = explode(',', $dates);
$parts = array_map('trim', $parts);
if (count($parts) !== 2) {
throw new \InvalidArgumentException("Invalid date range supplied: {$dates}");
}
[$start, $end] = $parts;
try {
/** @var Date[] $dateObjects */
$dateObjects = [Date::factory($start), Date::factory($end)->getEndOfDay()];
} catch (\Exception $ex) {
throw new \InvalidArgumentException("Invalid date range supplied: {$dates} (" . $ex->getMessage() . ")", $code = 0, $ex);
}
if ($dateObjects[0]->getTimestamp() > $dateObjects[1]->getTimestamp()) {
throw new \InvalidArgumentException("Invalid date range supplied: {$dates} (first date is older than the last date)");
}
$dateObjects = [$dateObjects[0]->getDatetime(), $dateObjects[1]->getDatetime()];
return $dateObjects;
}
/**
* Validate the sites parameter
*
*/
private function getSitesToCalculate() : ?string
{
$idSite = $this->getInput()->getOption('idsite');
if (is_null($idSite)) {
return null;
}
$sites = explode(',', $idSite);
foreach ($sites as $id) {
// validate the site ID
try {
new Site($id);
} catch (\Exception $ex) {
throw new \InvalidArgumentException("Invalid site ID: {$id}", $code = 0, $ex);
}
}
return $idSite;
}
/**
* Validate the goals parameter
*
*/
private function getGoalsToCalculate() : ?string
{
$idGoal = $this->getInput()->getOption('idgoal');
if (is_null($idGoal)) {
return null;
}
// Only allow the goals parameter to be used if a single site is specified
$idSite = $this->getInput()->getOption('idsite');
if (!is_numeric($idSite) || strpos($idSite, ',') !== \false) {
throw new \InvalidArgumentException("The goals parameter can only be used when a single website is specified using the idsite parameter", $code = 0);
}
$goals = explode(',', $idGoal);
$goalsModel = new GoalsModel();
foreach ($goals as $id) {
// validate the goal id
if (!$goalsModel->doesGoalExist($id, $idSite) && $id != GoalManager::IDGOAL_ORDER) {
throw new \InvalidArgumentException("Invalid goal ID: {$id}", $code = 0);
}
}
return $idGoal;
}
/**
* Generates the queries to calculate the 'pages before' metric for conversions within the specified date range,
* belonging to the specified site (if any) and specific goals (only if a single site is specified).
*
* @param string|null $startDatetime A datetime string. Visits that occur at this time or after are deleted. If not supplied,
* visits from the beginning of time are deleted.
* @param string|null $endDatetime A datetime string. Visits that occur before this time are deleted. If not supplied,
* visits from the end of time are deleted.
* @param int|null $lastN Calculate the last N conversions, should not be used with a date range
* @param string|null $idSite The site for which to calculate, or list of comma separated sites
* @param string|null $idGoal The goal for which to calculate, or list of comma separated idgoals (only if single site)
* @param bool $forceRecalc If enabled then values will be recalculated for conversions that already have a
* 'pages before' value. By default only conversions with a null value will be calculated.
*
* @return array An array of queries and bind arrays [['sql' => QUERY1, 'bind' => [PARAM1 => VALUE], ...]
*/
private static function getQueries(?string $startDatetime, ?string $endDatetime, ?int $lastN = null, ?string $idSite = null, ?string $idGoal = null, ?bool $forceRecalc = \false) : array
{
// Sites
if ($idSite === null) {
// All sites
$sitesModel = new SitesModel();
$sites = $sitesModel->getSitesId();
} else {
// Specific sites
$sites = explode(',', $idSite);
}
if ($lastN) {
// Since MySQL doesn't support multi-table updates with a LIMIT clause we will find the exact date time of
// the lastN record and use that as a date range start with the current date time as the date range end
/** @noinspection SqlResolve SqlUnused */
$sql = "\n SELECT MIN(s.t) FROM (\n SELECT c.server_time AS t\n FROM `" . Common::prefixTable('log_conversion') . "` c \n ";
$where = '';
if (!$forceRecalc) {
$where .= " AND c.pageviews_before IS NULL";
}
$bind = [];
if ($idGoal !== null) {
$where .= ' AND c.idgoal = ? ';
$bind[] = $idGoal;
}
if ($idSite !== null) {
$where .= ' AND c.idsite = ? ';
$bind[] = $idSite;
}
if ($where !== '') {
$sql .= ' WHERE ' . ltrim($where, 'AND ');
}
$sql .= " ORDER BY c.server_time DESC LIMIT " . $lastN . ") AS s";
$result = Db::fetchOne($sql, $bind);
if (!$result) {
return [];
}
$startDatetime = $result;
$endDatetime = Date::factory('now')->getDatetime();
}
// When querying for visit actions that contributed to the conversion we can use a cut off 24hrs before the
// start of the conversion date range as visits cannot last more than 24hrs, this limits the number of rows
// addressed by the subquery
$startDateTimeForActions = Date::factory($startDatetime)->subDay(1)->getDatetime();
$queries = [];
foreach ($sites as $site) {
$timezone = Site::getTimezoneFor($site);
if ($idGoal === null) {
// All goals
$gids = Db::fetchAll("SELECT idgoal FROM `" . Common::prefixTable('goal') . "`\n WHERE idsite = ? AND deleted = 0", [$site]);
$goals = array_column($gids, 'idgoal');
// Include ecommerce orders if enabled for the site
if (Site::isEcommerceEnabledFor($site)) {
$goals[] = GoalManager::IDGOAL_ORDER;
}
} else {
// Specific goals
$goals = explode(',', $idGoal);
}
foreach ($goals as $goal) {
$where = '';
if (!$forceRecalc) {
$where .= " AND c.pageviews_before IS NULL";
}
$conversionsStartDate = Date::factory($startDatetime, $timezone)->getDateTime();
$conversionsEndDate = Date::factory($endDatetime, $timezone)->getDateTime();
$bind = [$site, Date::factory($startDateTimeForActions, $timezone)->getDateTime(), $conversionsEndDate, $site, $goal, $conversionsStartDate, $conversionsEndDate, $site, $goal, $conversionsStartDate, $conversionsEndDate];
$sql = " \n UPDATE " . Common::prefixTable('log_conversion') . " c\n LEFT JOIN ( \n SELECT c.idvisit, c.idgoal, COUNT(a.idvisit) AS pagesbefore, c.idlink_va, c.server_time\n FROM `" . Common::prefixTable('log_conversion') . "` c\n LEFT JOIN (\n SELECT va.idvisit, va.server_time\n FROM `" . Common::prefixTable('log_link_visit_action') . "` va\n INNER JOIN `" . Common::prefixTable('log_action') . "` a ON a.idaction = va.idaction_url\n WHERE a.type = 1\n AND va.idsite = ?\n AND va.server_time >= ?\n AND va.server_time <= ?\n ORDER BY NULL\n ) AS a ON a.idvisit = c.idvisit AND a.server_time <= c.server_time\n WHERE c.idsite = ?\n AND c.idgoal = ?\n AND c.server_time >= ?\n AND c.server_time <= ?\n " . $where . " \n GROUP BY a.idvisit\n ORDER BY NULL\n ) AS s ON s.idvisit = c.idvisit AND s.server_time <= c.server_time \n SET c.pageviews_before = s.pagesbefore \n WHERE c.idsite = ? \n AND c.idgoal = ? \n AND c.server_time >= ?\n AND c.server_time <= ? \n " . $where;
$queries[] = ['sql' => $sql, 'bind' => $bind];
}
}
return $queries;
}
}