se/SE/stuff/scripts/BI_audit_generate/script.clear_krs_msig.php

#!/usr/bin/env php
<?php
error_reporting(E_ALL & ~E_NOTICE);
$_SERVER['SERVER_NAME'] = gethostname();
//require("/Library/Server/Web/Data/Sites/Default/dev-bzyk/se-lib/bootstrap.php");
require("/Library/Server/Web/Data/Sites/Default/SE/se-lib/bootstrap.php");
date_default_timezone_set('Europe/Warsaw');


$mainTables = ['BI_audit_KRS', 'BI_audit_KRS_address', 'BI_audit_KRS_company', 'BI_audit_KRS_name', 'BI_audit_KRS_person',
	'BI_audit_MSIG', 'BI_audit_MSIG_address', 'BI_audit_MSIG_company', 'BI_audit_MSIG_name', 'BI_audit_MSIG_person'];

$query = "show tables";
$result = DB::getPDO()->fetchAll($query);
$tables = array_map("reset", $result);
$allTables = array_filter($tables, function ($table) {
	if (preg_match('/(__@|_HIST$|^BI_audit_ALL|^BI_audit_BENFORD|^BI_audit_VALIDATE)/', $table)) return false;
	if (preg_match('/^BI_audit/', $table)) return true;
	return false;
});
sort($allTables);

$refTables = [];
foreach ($mainTables as $mainTable) {
	foreach ($allTables as $allTable) {
		try {
			$refTables[] = ACL::getRefTable("default_db/{$mainTable}/{$mainTable}", "default_db__x3A__{$allTable}:{$allTable}");
		} catch (Exception $e) {
		}
		try {
			$refTables[] = ACL::getRefTable("default_db/{$allTable}/{$allTable}", "default_db__x3A__{$mainTable}:{$mainTable}");
		} catch (Exception $e) {
		}
	}
}

$refTables = array_unique(array_filter($refTables, function ($table) {
	if (preg_match('/_VIEW$/', $table)) return false;
	return true;
}));
usort($refTables, function ($a, $b) {
	list($ai, $bi) = array_map(function($x) {
		preg_match('/__([[:digit:]]+)$/', $x, $matches);
		return (int)$matches[1];
	}, [$a, $b]);
	if ($ai == $bi) return 0;
	return ($ai > $bi);
});

echo "Główne tabele do wyczyszczenia: (" . count($mainTables) . "):\n" . implode(", ", $mainTables) . "\n\n";
echo "Tabele relacyjne do wyczyszczenia (" . count($refTables) . "):\n" . implode(", ", $refTables) . "\n\n";

$prompt = null;
while (!in_array($prompt, ['t', 'n'])) {
	$prompt = strtolower(readline("Jesteś pewien, że chcesz wyczyścić te wszystkie tabele? [T/N]: "));
}
if ($prompt === 'n') die("Przerywam\n");

/*
try {
	Lib::loadClass('Token');
	$pass = Config::getConfFile('default_db')['pass'];
	$tokenObj = new Token($pass);
	$token = $tokenObj->genToken();
	echo "Token: {$token}\n";
	$hash = readline("Hash: ");
	if ($pass !== $tokenObj->verify($hash)) die("Błędny token, przerywam\n"); 
} catch (Exception $e) {
	die("Wystąpił nieznany błąd, przerywam\n");
}
*/

$prompt = null;
while (!in_array($prompt, ['t', 'n'])) {
	$prompt = strtolower(readline("Na pewno jesteś pewien, że chcesz wyczyścić te wszystkie tabele? [T/N]: "));
}
if ($prompt === 'n') die("Przerywam\n");

$queries = [];
foreach ($mainTables as $table) $queries[] = [
	"desc" => "Czyszczę tabelę główną `{$table}`",
	"sql" => "delete from `{$table}`",
];
foreach ($refTables as $table) $queries[] = [
	"desc" => "Czyszczę tabelę relacyjną `{$table}`",
	"sql" => "truncate table `{$table}`",
];
$queries[] = [
	"desc" => "Usuwam relacje z `BI_audit_ALL_ref` (relacje OD)",
	"sql" => "delete from `ref` using `BI_audit_ALL` `all` join `BI_audit_ALL_ref` `ref` on `all`.`ID` = `ref`.`ID1` where `all`.`REMOTE_TABLE` in ('" . implode("', '", $mainTables) . "')",
];
$queries[] = [
	"desc" => "Usuwam relacje z `BI_audit_ALL_ref` (relacje DO)",
	"sql" => "delete from `ref` using `BI_audit_ALL` `all` join `BI_audit_ALL_ref` `ref` on `all`.`ID` = `ref`.`ID2` where `all`.`REMOTE_TABLE` in ('" . implode("', '", $mainTables) . "')",
];
$queries[] = [
	"desc" => "Usuwam dane z `BI_audit_ALL`",
	"sql" => "delete from `BI_audit_ALL` where `REMOTE_TABLE` in ('" . implode("', '", $mainTables) . "')",
];
$queries[] = [
	"desc" => "Optymalizuję tabelę `BI_audit_ALL`",
	"sql" => "optimize table `BI_audit_ALL`",
];
$queries[] = [
	"desc" => "Optymalizuję tabelę `BI_audit_ALL_ref`",
	"sql" => "optimize table `BI_audit_ALL_ref`",
];
foreach ($queries as $query) {
	echo $query['desc'];
//	echo " ({$query['sql']})";
	try {
		DB::getPDO()->query($query['sql']);
		echo " - OK\n";
	} catch (Exception $e) {
		echo " - ERROR\n";
	}
}