se/SE/stuff/scripts/patch.2017-11-22.BI_audit_ALL_ref_bugfix.php

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

$refTablesConf = [
	'BI_audit_CEIDG' => [
		'refTablesTo' => [
			'BI_audit_CEIDG_pelnomocnicy',
			'BI_audit_CEIDG_powiazania',
		],
		'base' => 1,
	],
	'BI_audit_ENERGA_PRACOWNICY' => [
		'refTablesTo' => [
			'BI_audit_ENERGA_PRACOWNICY_adresy',
		],
		'base' => 0,
	],
	'BI_audit_KRS' => [
		'refTablesTo' => [
			'BI_audit_KRS_address',
			'BI_audit_KRS_company',
			'BI_audit_KRS_person',
		],
		'base' => 1,
	],
	'BI_audit_MSIG' => [
		'refTablesTo' => [
			'BI_audit_MSIG_address',
			'BI_audit_MSIG_company',
			'BI_audit_MSIG_person',
		],
		'base' => 1,
	],
];

$sourceTables = ['BI_audit_ENERGA_PRACOWNICY'];

$queries = [];
$tables = [];

foreach ($refTablesConf as $refTableFrom => $refTableConf) {
	$tables[] = $refTableFrom;
	foreach ($refTableConf['refTablesTo'] as $refTableTo) {
		$tables[] = $refTableTo;
		$refTable = ACL::getRefTable("default_db/{$refTableFrom}/{$refTableFrom}", "default_db__x3A__{$refTableTo}:{$refTableTo}");
		$queries["Inserting refs for {$refTableFrom} => {$refTableTo}"] = "insert into `BI_audit_ALL_ref` (`ID1`, `ID2`, `RELATION_ID`, `BASE`) select `all1`.`ID`, `all2`.`ID`, `rel`.`ID`, {$refTableConf['base']} from `BI_audit_ALL` as `all1` join `{$refTable}` as `ref` on `all1`.`REMOTE_TABLE` = '{$refTableFrom}' and `all1`.`REMOTE_ID` = `ref`.`PRIMARY_KEY` and `ref`.`A_STATUS` = 'NORMAL' join `BI_audit_ALL` as `all2` on `all2`.`REMOTE_TABLE` = '{$refTableTo}' and `ref`.`REMOTE_PRIMARY_KEY` = `all2`.`REMOTE_ID` join `BI_audit_ALL_ref_RELATIONS` `rel` on `all1`.`REMOTE_TABLE` = `rel`.`RELATION`";
		if (!in_array($refTableFrom, $sourceTables)) $queries["Inserting refs for {$refTableTo} => {$refTableFrom}"] = "insert into `BI_audit_ALL_ref` (`ID1`, `ID2`, `RELATION_ID`, `BASE`) select `all2`.`ID`, `all1`.`ID`, `rel`.`ID`, {$refTableConf['base']} from `BI_audit_ALL` as `all1` join `{$refTable}` as `ref` on `all1`.`REMOTE_TABLE` = '{$refTableFrom}' and `all1`.`REMOTE_ID` = `ref`.`PRIMARY_KEY` and `ref`.`A_STATUS` = 'NORMAL' join `BI_audit_ALL` as `all2` on `all2`.`REMOTE_TABLE` = '{$refTableTo}' and `ref`.`REMOTE_PRIMARY_KEY` = `all2`.`REMOTE_ID` join `BI_audit_ALL_ref_RELATIONS` `rel` on `all2`.`REMOTE_TABLE` = `rel`.`RELATION`";
	}
}

$queries = array_merge([
	"Disabling keys for refs table" => "alter table `BI_audit_ALL_ref` disable keys",
	"Deleting refs" => "delete from `ref` using `BI_audit_ALL_ref` as `ref` join `BI_audit_ALL_ref_RELATIONS` as `rel` on `ref`.`RELATION_ID` = `rel`.`ID` where `rel`.`RELATION` in ('" . implode("', '", $tables) . "')",
	"Optimizing refs table" => "optimize table `BI_audit_ALL_ref`",
], $queries, [
	"Enabling keys for refs table" => "alter table `BI_audit_ALL_ref` enable keys",
]);

foreach ($queries as $desc => $query) {
	echo "{$desc}\nSQL: {$query}\n";
	DB::getPDO()->query($query);
	echo "Done.\n\n";
}
echo "Finished.\n";