Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
database cleanup for piwik bug #6415 as a plugin-command
<?php
/**
* Piwik - free/libre analytics platform
*
* @link http://piwik.org
* @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
*
*/
namespace Piwik\Plugins\CleanZeroActionsCommand\Commands;
use Piwik\Common;
use Piwik\Log;
use Piwik\Plugin\ConsoleCommand;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use Exception;
use Piwik\Db;
/**
* This class provides a piwik action to merge/delete 0-action visists
* according bug https://github.com/piwik/piwik/issues/6415
*
*/
class CleanZeroActions extends ConsoleCommand
{
protected $dateFormat = "Y-m-d H:i:s"; //"YY-mm-dd HH:mm:s";
protected $timeWihinMinutes = "60";
protected $deletedVisits = 0;
protected $mergedVisits = 0;
/**
* This methods allows you to configure your command. Here you can define the name and description of your command
* as well as all options and arguments you expect when executing it.
*/
protected function configure()
{
$this->setName('cleanzeroactionscommand:cleanzeroactions');
$this->setDescription('CleanZeroActions Command');
}
/**
* Main Method for command execution
*
* @param InputInterface $input
* @param OutputInterface $output
* @return void
*/
protected function execute(InputInterface $input, OutputInterface $output)
{
ini_set("max_execution_time", 0);
$output->writeln("SEARCH FOR ZERO ACTIONS");
$aVisits = $this->getZeroActionVisits();
if (count($aVisits) === 0) {
Log::error(
"NO ZERO VISITS FOUND. STOPPING HERE"
);
exit();
} else {
$output->writeln(sprintf(
"FOUND %s VISITS",
count($aVisits)
));
foreach ($aVisits as $i => $aVisit) {
$this->parseZeroActionVisit($aVisit);
}
}
$output->writeln("DONE");
$output->writeln(sprtinf(
"DELETED VISITS: %s",
$this->deletedVisits
));
$output->writeln(sprtinf(
"MERGED VISITS: %s",
$this->mergedVisits
));
return "";
}
/**
* parse a Zero Visit
*
* @param $aVisit
* @throws Exception
*/
protected function parseZeroActionVisit($aVisit)
{
Log::debug(
"SEARCH VISITS OF THE SAME VISITOR WITHIN %s MIN",
$this->timeWihinMinutes
);
$aOtherVisits = $this->getOtherVisits($aVisit);
if (count($aOtherVisits) === 0) {
Log::debug(
"FOUND NO OTHER VISITS - visit ID: %s - DELETE",
$aVisit['idvisit']
);
$this->deleteVisitAndActions($aVisit['idvisit']);
} else {
Log::debug(
"FOUND %s OTHER VISITS - MERGE WITH THE LAST ONE",
count($aVisits)
);
//Log::error("OTHER VISITS %s",var_export($aOtherVisits, true));
$newId = $aOtherVisits[0]['idvisit'];
$oldId = $aVisit['idvisit'];
$this->moveActionsToNewVisit($oldId, $newId);
$this->deleteVisit($oldId);
}
}
/**
* move actions to new visit
*
* @param $oldId
* @param $newId
* @throws Exception
*/
protected function moveActionsToNewVisit($oldId, $newId)
{
$this->mergedVisits++;
Log::warning("moveActionsToNewVisit %s => %s", $oldId, $newId);
DB::query("UPDATE " . Common::prefixTable('log_link_visit_action') . "
SET idvisit = ?
WHERE idvisit = ?",
array(
$newId,
$oldId
)
);
}
/**
* delete visit and action
*
* @param $idvisit
*/
protected function deleteVisitAndActions($idvisit)
{
Log::warning("deleteVisitAndActions %s", $idvisit);
$this->deleteVisitActions($idvisit);
$this->deleteVisit($idvisit);
}
/**
* delete a visit
*
* @param $idvisit
* @throws Exception
*/
protected function deleteVisit($idvisit)
{
$this->deletedVisits++;
Log::warning("deleteVisit %s", $idvisit);
DB::query("DELETE FROM " . Common::prefixTable('log_visit') . "
WHERE idvisit = ?",
array(
$idvisit
)
);
}
/**
* delete a action
*
* @param $idvisit
* @throws Exception
*/
protected function deleteVisitActions($idvisit)
{
Log::warning("deleteVisitActions %s", $idvisit);
DB::query("DELETE FROM " . Common::prefixTable('log_link_visit_action') . "
WHERE idvisit = ?",
array(
$idvisit
)
);
}
/**
* get the action count for idvisit
* not user anymore
*
* @deprecated
* @param $idvisit
* @return string
* @throws Exception
*/
protected function getActionCountForVisit($idvisit)
{
return DB::fetchOne("SELECT count(idlink_va)
FROM " . Common::prefixTable('log_link_visit_action') . "
WHERE idvisit = ? ",
array(
$idvisit
));
}
/**
* get other visit candidates to merge
*
* @param $aVisit
* @throws Exception
*/
protected function getOtherVisits($aVisit)
{
$oFirstActionTime = \DateTime::createFromFormat($this->dateFormat, $aVisit['visit_first_action_time']);
$minActionTime = \DateTime::createFromFormat($this->dateFormat, $aVisit['visit_first_action_time']);
$maxActionTime = \DateTime::createFromFormat($this->dateFormat, $aVisit['visit_first_action_time']);
$sIntervallString = "PT" . ($this->timeWihinMinutes) . "M";
// it could be some time bevore
$minActionTime = $minActionTime->sub(new \DateInterval($sIntervallString));
// but not after
//$maxActionTime = $maxActionTime->add(new \DateInterval($sIntervallString));
Log::debug(
"o: %s f: %s, min: %s, max: %s",
$aVisit['visit_first_action_time'],
$oFirstActionTime->format($this->dateFormat),
$minActionTime->format($this->dateFormat),
$maxActionTime->format($this->dateFormat)
);
return DB::fetchAll("SELECT *
FROM " . Common::prefixTable('log_visit') . "
WHERE idvisit != ? AND idvisitor = ? AND config_id = ? AND visit_first_action_time <= ? AND visit_total_actions > ?
ORDER BY visit_first_action_time DESC",
array(
$aVisit['idvisit'], // not the same visit
$aVisit['idvisitor'], // but the same visitor
$aVisit['config_id'], // and the same config (site?)
$maxActionTime->format($this->dateFormat),
0 // ignore zero action visists
));
}
/**
* get all the visits with zero actions
*
* @return array
* @throws Exception
*/
protected function getZeroActionVisits()
{
$aVisits = Db::fetchAll("SELECT idvisit, idvisitor, config_id, visit_first_action_time
FROM " . Common::prefixTable('log_visit') . "
WHERE visit_total_actions = ?
",
array(0));
return $aVisits;
}
}
@huang0808

This comment has been minimized.

Copy link

huang0808 commented Jul 26, 2018

run this command why can not remove 0 ations in UI

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.