Spaces:

Leon4gr45
/

matomo

Paused

matomo / plugins /GeoIp2 /Commands /ConvertRegionCodesToIso.php

Upload source files chunk 12

a82a2f0 verified 3 months ago

4.28 kB

<?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\GeoIp2\Commands;

use Piwik\Common;
use Piwik\Db;
use Piwik\DbHelper;
use Piwik\Option;
use Piwik\Plugin\ConsoleCommand;
use Piwik\Plugins\UserCountry\LocationProvider;
use Piwik\Plugins\GeoIp2\LocationProvider\GeoIp2;

class ConvertRegionCodesToIso extends ConsoleCommand
{
    public const OPTION_NAME = 'regioncodes_converted';
    public const MAPPING_TABLE_NAME = 'fips2iso';

    protected function configure()
    {
        $this->setName('usercountry:convert-region-codes');
        $this->setDescription("Convert FIPS region codes saved by GeoIP legacy provider to ISO.");
    }

    public function isEnabled()
    {
        return (LocationProvider::getCurrentProvider() instanceof GeoIp2);
    }

    protected function doExecute(): int
    {
        $output = $this->getOutput();

        // chick if option is set to disable second run
        if (Option::get(self::OPTION_NAME)) {
            $output->writeln('Converting region codes already done.');
            return self::SUCCESS;
        }

        $output->setDecorated(true);

        $output->write('Creating mapping table in database');

        Db::query('DROP table if exists ' . self::MAPPING_TABLE_NAME);

        DbHelper::createTable(
            self::MAPPING_TABLE_NAME,
            "`country_code` VARCHAR(2) NOT NULL,
                           `fips_code` VARCHAR(2) NOT NULL,
                           `iso_code` VARCHAR(4) NULL DEFAULT NULL,
                           PRIMARY KEY (`country_code`, `fips_code`)"
        );

        $output->writeln(' <fg=green>✓</>');

        $mappings = include __DIR__ . '/../data/regionMapping.php';

        $output->write('Inserting mapping data ');

        $counter = 0;
        foreach ($mappings as $country => $regionMapping) {
            foreach ($regionMapping as $fips => $iso) {
                if ($fips == $iso) {
                    continue; // nothing needs to be changed, so ignore the mapping
                }

                Db::query('INSERT INTO `' . Common::prefixTable(self::MAPPING_TABLE_NAME) . '` VALUES (?, ?, ?)', [$country, $fips, $iso]);
                $counter++;
                if ($counter % 50 == 0) {
                    $output->write('.');
                }
            }
        }

        $output->writeln(' <fg=green>✓</>');

        $output->writeln('Updating Matomo log tables:');

        $activationTime = Option::get(GeoIp2::SWITCH_TO_ISO_REGIONS_OPTION_NAME);
        $activationDateTime = date('Y-m-d H:i:s', $activationTime);

        // fix country and region of tibet so it will be updated correctly afterwards
        $tibetFixQuery = 'UPDATE `%s` SET location_country = "cn", location_region = "14" WHERE location_country = "ti"';

        // replace invalid country codes used by GeoIP Legacy
        $fixInvalidCountriesQuery = 'UPDATE `%s` SET location_country = "" WHERE location_country IN("AP", "EU", "A1", "A2")';

        $query = "UPDATE `%s` INNER JOIN %s ON location_country = country_code AND location_region = fips_code SET location_region = iso_code
                  WHERE `%s` < ?";

        $logTables = ['log_visit' => 'visit_first_action_time', 'log_conversion' => 'server_time'];

        foreach ($logTables as $logTable => $dateField) {
            $output->write('- Updating ' . $logTable);

            Db::query(sprintf($tibetFixQuery, Common::prefixTable($logTable)));
            Db::query(sprintf($fixInvalidCountriesQuery, Common::prefixTable($logTable)));

            $sql = sprintf($query, Common::prefixTable($logTable), Common::prefixTable(self::MAPPING_TABLE_NAME), $dateField);
            Db::query($sql, $activationDateTime);

            $output->writeln(' <fg=green>✓</>');
        }

        $output->write('Removing mapping table from database ');
        Db::dropTables(Common::prefixTable(self::MAPPING_TABLE_NAME));
        $output->writeln(' <fg=green>✓</>');

        // save option to prevent a second run
        Option::set(self::OPTION_NAME, true);

        $output->writeln('All region codes converted.');

        return self::SUCCESS;
    }
}

	<?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\GeoIp2\Commands;

	use Piwik\Common;
	use Piwik\Db;
	use Piwik\DbHelper;
	use Piwik\Option;
	use Piwik\Plugin\ConsoleCommand;
	use Piwik\Plugins\UserCountry\LocationProvider;
	use Piwik\Plugins\GeoIp2\LocationProvider\GeoIp2;

	class ConvertRegionCodesToIso extends ConsoleCommand
	{
	public const OPTION_NAME = 'regioncodes_converted';
	public const MAPPING_TABLE_NAME = 'fips2iso';

	protected function configure()
	{
	$this->setName('usercountry:convert-region-codes');
	$this->setDescription("Convert FIPS region codes saved by GeoIP legacy provider to ISO.");
	}

	public function isEnabled()
	{
	return (LocationProvider::getCurrentProvider() instanceof GeoIp2);
	}

	protected function doExecute(): int
	{
	$output = $this->getOutput();

	// chick if option is set to disable second run
	if (Option::get(self::OPTION_NAME)) {
	$output->writeln('Converting region codes already done.');
	return self::SUCCESS;
	}

	$output->setDecorated(true);

	$output->write('Creating mapping table in database');

	Db::query('DROP table if exists ' . self::MAPPING_TABLE_NAME);

	DbHelper::createTable(
	self::MAPPING_TABLE_NAME,
	"`country_code` VARCHAR(2) NOT NULL,
	`fips_code` VARCHAR(2) NOT NULL,
	`iso_code` VARCHAR(4) NULL DEFAULT NULL,
	PRIMARY KEY (`country_code`, `fips_code`)"
	);

	$output->writeln(' <fg=green>✓</>');

	$mappings = include __DIR__ . '/../data/regionMapping.php';

	$output->write('Inserting mapping data ');

	$counter = 0;
	foreach ($mappings as $country => $regionMapping) {
	foreach ($regionMapping as $fips => $iso) {
	if ($fips == $iso) {
	continue; // nothing needs to be changed, so ignore the mapping
	}

	Db::query('INSERT INTO `' . Common::prefixTable(self::MAPPING_TABLE_NAME) . '` VALUES (?, ?, ?)', [$country, $fips, $iso]);
	$counter++;
	if ($counter % 50 == 0) {
	$output->write('.');
	}
	}
	}

	$output->writeln(' <fg=green>✓</>');

	$output->writeln('Updating Matomo log tables:');

	$activationTime = Option::get(GeoIp2::SWITCH_TO_ISO_REGIONS_OPTION_NAME);
	$activationDateTime = date('Y-m-d H:i:s', $activationTime);

	// fix country and region of tibet so it will be updated correctly afterwards
	$tibetFixQuery = 'UPDATE `%s` SET location_country = "cn", location_region = "14" WHERE location_country = "ti"';

	// replace invalid country codes used by GeoIP Legacy
	$fixInvalidCountriesQuery = 'UPDATE `%s` SET location_country = "" WHERE location_country IN("AP", "EU", "A1", "A2")';

	$query = "UPDATE `%s` INNER JOIN %s ON location_country = country_code AND location_region = fips_code SET location_region = iso_code
	WHERE `%s` < ?";

	$logTables = ['log_visit' => 'visit_first_action_time', 'log_conversion' => 'server_time'];

	foreach ($logTables as $logTable => $dateField) {
	$output->write('- Updating ' . $logTable);

	Db::query(sprintf($tibetFixQuery, Common::prefixTable($logTable)));
	Db::query(sprintf($fixInvalidCountriesQuery, Common::prefixTable($logTable)));

	$sql = sprintf($query, Common::prefixTable($logTable), Common::prefixTable(self::MAPPING_TABLE_NAME), $dateField);
	Db::query($sql, $activationDateTime);

	$output->writeln(' <fg=green>✓</>');
	}

	$output->write('Removing mapping table from database ');
	Db::dropTables(Common::prefixTable(self::MAPPING_TABLE_NAME));
	$output->writeln(' <fg=green>✓</>');

	// save option to prevent a second run
	Option::set(self::OPTION_NAME, true);

	$output->writeln('All region codes converted.');

	return self::SUCCESS;
	}
	}