se/SE/schema/WPS_Functions/BI_audit/3_create_all.php

#!/usr/bin/env php
<?php
$_SERVER['SERVER_NAME'] = "biuro.biall-net.pl";
require("/Library/Server/Web/Data/Sites/Default/dev-bzyk/se-lib/bootstrap.php");
#require("/Library/Server/Web/Data/Sites/SE-producition-git/SE/se-lib/bootstrap.php");
date_default_timezone_set('Europe/Warsaw');// PHP 5 >= 5.1.0 required by date functions

$tables = [
	'BI_audit_CEIDG' => [
		'nip' => 'nip',
		'regon' => 'regon',
		'TERYT_SYM' => 'TERYT_SYM',
		'TERYT_SYM_UL' => 'TERYT_SYM_UL',
		'nrDomu' => 'budynek',
		'nrLokalu' => 'lokal',
	],
	'BI_audit_CEIDG_pelnomocnicy' => [
		'nip' => 'nip',
		'TERYT_SYM' => 'TERYT_SYM',
		'TERYT_SYM_UL' => 'TERYT_SYM_UL',
		'nrDomu' => 'budynek',
		'nrLokalu' => 'lokal',
	],
	'BI_audit_CEIDG_powiazania' => [
		'nip' => 'nip',
		'regon' => 'regon',
	],
	'BI_audit_ENERGA_PRACOWNICY' => [
		'nip' => 'nip',
		'regon' => 'regon',
		'pesel' => 'pesel',
	],
	'BI_audit_ENERGA_PRACOWNICY_adresy' => [
		'TERYT_SYM' => 'TERYT_SYM',
		'TERYT_SYM_UL' => 'TERYT_SYM_UL',
		'nrDomu' => 'nrBudynku',
		'nrLokalu' => 'nrLokalu',
	],
	'BI_audit_ENERGA_RUM_KONTRAHENCI' => [
		'nip' => 'NIP',
		'regon' => 'REGON',
		'pesel' => 'PESEL',
		'TERYT_SYM' => 'TERYT_SYM',
		'TERYT_SYM_UL' => 'TERYT_SYM_UL',
		'nrDomu' => 'Numer_budynku',
		'nrLokalu' => 'Numer_mieszkania_lokalu',
	],
	'BI_audit_KRS' => [
		'krs' => 'krs',
		'nip' => 'nip',
		'regon' => 'regon',
		'TERYT_SYM' => 'TERYT_SYM',
		'TERYT_SYM_UL' => 'TERYT_SYM_UL',
		'nrDomu' => 'A_nrDomu',
		'nrLokalu' => 'A_nrLokalu',
	],
	'BI_audit_KRS_company' => [
		'regon' => 'regon',
		'krs' => 'krs',
	],
	'BI_audit_KRS_person' => [
		'pesel' => 'pesel',
	],
//	'BI_audit_KW_requested' => [
//	],
	'BI_audit_KW_requested_person' => [
		'pesel' => 'Seller_person_pesel',
		'krs' => 'Seller_person_KRS',
		'nip' => 'Seller_person_NIP',
		'regon' => 'Seller_person_REGON',
	],
];

$query[] = "truncate table BI_audit_ALL";
foreach ($tables as $name => $table) {
	if ($table) $query[] = "insert into BI_audit_ALL (REMOTE_TABLE, REMOTE_ID, " . implode(", ", array_keys($table)) . ") select '{$name}', ID, " . implode(", ", $table) . " from {$name}";
	else $query[] = "insert into BI_audit_ALL (REMOTE_TABLE, REMOTE_ID) select '{$name}', ID from {$name}";
}
$query[] = "update BI_audit_ALL set nrDomu = substring(nrDomu, 1, position('/' in nrDomu) - 1), nrLokalu = substring(nrDomu, position('/' in nrDomu ) + 1) where nrDomu rlike '^[[:digit:]]+.*/[[:digit:]]+.*$' and nrLokalu is null";

$query = [];
$query[] = "truncate table BI_audit_ALL_ref";

$query[] = "insert ignore into BI_audit_ALL_ref (ID1, ID2, RELATION) select all1.ID, all2.ID, all1.REMOTE_TABLE from BI_audit_ALL as all1 join `CRM__#REF_TABLE__19` as ref on all1.REMOTE_TABLE = 'BI_audit_CEIDG' and all1.REMOTE_ID = ref.PRIMARY_KEY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_CEIDG_pelnomocnicy' and ref.REMOTE_PRIMARY_KEY = all2.REMOTE_ID";
$query[] = "insert ignore into BI_audit_ALL_ref (ID1, ID2, RELATION) select all2.ID, all1.ID, all2.REMOTE_TABLE from BI_audit_ALL as all1 join `CRM__#REF_TABLE__19` as ref on all1.REMOTE_TABLE = 'BI_audit_CEIDG' and all1.REMOTE_ID = ref.PRIMARY_KEY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_CEIDG_pelnomocnicy' and ref.REMOTE_PRIMARY_KEY = all2.REMOTE_ID";

$query[] = "insert ignore into BI_audit_ALL_ref (ID1, ID2, RELATION) select all1.ID, all2.ID, all1.REMOTE_TABLE from BI_audit_ALL as all1 join `CRM__#REF_TABLE__18` as ref on all1.REMOTE_TABLE = 'BI_audit_CEIDG' and all1.REMOTE_ID = ref.PRIMARY_KEY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_CEIDG_powiazania' and ref.REMOTE_PRIMARY_KEY = all2.REMOTE_ID";
$query[] = "insert ignore into BI_audit_ALL_ref (ID1, ID2, RELATION) select all2.ID, all1.ID, all2.REMOTE_TABLE from BI_audit_ALL as all1 join `CRM__#REF_TABLE__18` as ref on all1.REMOTE_TABLE = 'BI_audit_CEIDG' and all1.REMOTE_ID = ref.PRIMARY_KEY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_CEIDG_powiazania' and ref.REMOTE_PRIMARY_KEY = all2.REMOTE_ID";

$query[] = "insert ignore into BI_audit_ALL_ref (ID1, ID2, RELATION) select all1.ID, all2.ID, all1.REMOTE_TABLE from BI_audit_ALL as all1 join `CRM__#REF_TABLE__11` as ref on all1.REMOTE_TABLE = 'BI_audit_KRS' and all1.REMOTE_ID = ref.PRIMARY_KEY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_KRS_company' and ref.REMOTE_PRIMARY_KEY = all2.REMOTE_ID";
$query[] = "insert ignore into BI_audit_ALL_ref (ID1, ID2, RELATION) select all2.ID, all1.ID, all2.REMOTE_TABLE from BI_audit_ALL as all1 join `CRM__#REF_TABLE__11` as ref on all1.REMOTE_TABLE = 'BI_audit_KRS' and all1.REMOTE_ID = ref.PRIMARY_KEY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_KRS_company' and ref.REMOTE_PRIMARY_KEY = all2.REMOTE_ID";

$query[] = "insert ignore into BI_audit_ALL_ref (ID1, ID2, RELATION) select all1.ID, all2.ID, all1.REMOTE_TABLE from BI_audit_ALL as all1 join `CRM__#REF_TABLE__12` as ref on all1.REMOTE_TABLE = 'BI_audit_KRS' and all1.REMOTE_ID = ref.PRIMARY_KEY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_KRS_person' and ref.REMOTE_PRIMARY_KEY = all2.REMOTE_ID";
$query[] = "insert ignore into BI_audit_ALL_ref (ID1, ID2, RELATION) select all2.ID, all1.ID, all2.REMOTE_TABLE from BI_audit_ALL as all1 join `CRM__#REF_TABLE__12` as ref on all1.REMOTE_TABLE = 'BI_audit_KRS' and all1.REMOTE_ID = ref.PRIMARY_KEY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_KRS_person' and ref.REMOTE_PRIMARY_KEY = all2.REMOTE_ID";

$query[] = "insert ignore into BI_audit_ALL_ref (ID1, ID2, RELATION) select all1.ID, all2.ID, all1.REMOTE_TABLE from BI_audit_ALL as all1 join `BI_audit_ENERGA_PRACOWNICY` as pracownicy on all1.REMOTE_TABLE = 'BI_audit_ENERGA_PRACOWNICY' and all1.REMOTE_ID = pracownicy.ID join BI_audit_ENERGA_PRACOWNICY_adresy as adresy on pracownicy.ID = adresy.ID_BI_audit_ENERGA_PRACOWNICY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_ENERGA_PRACOWNICY_adresy' and adresy.ID = all2.REMOTE_ID";
$query[] = "insert ignore into BI_audit_ALL_ref (ID1, ID2, RELATION) select all2.ID, all1.ID, all2.REMOTE_TABLE from BI_audit_ALL as all1 join `BI_audit_ENERGA_PRACOWNICY` as pracownicy on all1.REMOTE_TABLE = 'BI_audit_ENERGA_PRACOWNICY' and all1.REMOTE_ID = pracownicy.ID join BI_audit_ENERGA_PRACOWNICY_adresy as adresy on pracownicy.ID = adresy.ID_BI_audit_ENERGA_PRACOWNICY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_ENERGA_PRACOWNICY_adresy' and adresy.ID = all2.REMOTE_ID";

//$query[] = "replace into BI_audit_ALL_ref (ID1, ID2, RELATION) select all1.ID, all2.ID, all1.REMOTE_TABLE from BI_audit_ALL as all1 join `CRM__#REF_TABLE__24` as ref on all1.REMOTE_TABLE = 'BI_audit_KW_requested' and all1.REMOTE_ID = ref.PRIMARY_KEY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_KW_requested_person' and ref.REMOTE_PRIMARY_KEY = all2.REMOTE_ID";
//$query[] = "replace into BI_audit_ALL_ref (ID1, ID2, RELATION) select all2.ID, all1.ID, all2.REMOTE_TABLE from BI_audit_ALL as all1 join `CRM__#REF_TABLE__24` as ref on all1.REMOTE_TABLE = 'BI_audit_KW_requested' and all1.REMOTE_ID = ref.PRIMARY_KEY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_KW_requested_person' and ref.REMOTE_PRIMARY_KEY = all2.REMOTE_ID";


//$query[] = "replace into BI_audit_ALL_ref (ID1, ID2) select ID2, ID1 from BI_audit_ALL_ref";

foreach ($query as $sql) {
	echo $sql . "\n";
	DB::getPDO()->query($sql);
}