database cleanup for piwik bug #6415 as a plugin-command

gistfile1.txt

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

run this command why can not remove 0 ations in UI