se/SE/se-lib/Route/UrlAction/BiAuditGenerate.php

<?php

Lib::loadClass('RouteBase');
Lib::loadClass('FoldersConfig');
Lib::loadClass('FileUploader');
Lib::loadClass('ProcesHelper');
Lib::loadClass('SchemaFactory');
Lib::loadClass('Core_AclHelper');

class Route_UrlAction_BiAuditGenerate extends RouteBase {

	private $powiazanie = null;
	private $REFERER;

	public function handleAuth() {
		if (!User::logged()) {
			User::authByRequest();
		}
	}

	private function getDirectory($table, $id) {
		$folderConf = FoldersConfig::getAll($table.'_COLUMN');
		$record = new stdClass;
		$record->ID = $id;
		$uploader = new FileUploader($table.'_COLUMN', $record);
		$errMsg = "";
		if (!$uploader->setConfig($folderConf, $errMsg)) throw new HttpException("Błąd danych konfiguracyjnych<br>\n{$$errMsg}", 404);
		$uploader->findFolder();
		return $uploader->getDestLocalPath(true);
	}

	private function getMainDirectory() {
		$folderConf = FoldersConfig::getAll('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_COLUMN');
		if (!isset($folderConf['mount_point'])) throw new HttpException("Błąd danych konfiguracyjnych<br>\n{$errMsg}", 404);
		return $folderConf['mount_point'];
	}

	private function initializePowiazaniaForm() {
?>
<div class="container" style="margin-top:20px">
  <form class="form-horizontal" method="post">
    <legend>
      Generowanie powiązań :: <?=$this->powiazanie['L_APPOITMENT_INFO']?>
      <span class="pull-right"># <?=$this->powiazanie['ID']?></span>
    </legend>
    <div class="form-group">
      <div class="col-sm-12">
        <h4>Wybierz pracowników, dla których mają zostać wyszukane powiązania</h4>
        <table class="table table-bordered table-hover table-striped" height=5>
          <thead>
            <tr style="text-align:center; background-color:lightgray">
              <td width=1><input type="checkbox" name="checkAll" onClick="toggleAll(this)"/></td>
              <td width=1>ID</td>
              <td>Imiona</td>
              <td>Nazwisko</td>
              <td>Pesel</td>
              <td>NIP</td>
              <td>Regon</td>
            </tr>
          </thead>
          <tbody>
	
<?php
		$acl = Core_AclHelper::getAclByNamespace('default_db/BI_audit_ENERGA_PRACOWNICY/BI_audit_ENERGA_PRACOWNICY');
		$queryFeatures = $acl->buildQuery(['limit' => 0]);
		$pracownicy = $queryFeatures->getItems();
//echo "<pre>"; print_r($pracownicy); echo "</pre>";
		foreach ($pracownicy as $pracownik) {
?>
            <tr>
              <td><input type="checkbox" name="prID[]" value="<?=$pracownik['ID']?>" onClick="toggle(this)"/></td>
              <td align="right"><?=$pracownik['ID']?></td>
              <td><?=$pracownik['imiona']?></td>
              <td><?=$pracownik['nazwisko']?></td>
              <td><?=$pracownik['pesel']?></td>
              <td><?=$pracownik['nip']?></td>
              <td><?=$pracownik['regon']?></td>
            </tr>
<?php
		}
?>
            <tr>
              <td><input type="checkbox" name="reloadCache"/></td>
              <td colspan=6>Odśwież cache (wymagane w przypadku, gdy dokonano dowolnych zmian w tabelach pracowników/kontrahentów)</td>
            </tr>
          </tbody>
        </table>
      </div>
    </div>
    <div class="form-group">
      <div class="col-sm-12">
        <div class="containter" style="text-align:center">
          <button type="submit" class="btn btn-primary" name="action" value="initialize">Generuj</button>
          <a href="<?=$this->REFERER?>" class="btn btn-default">Anuluj</a>
        </div>
      </div>
    </div>
  <input type="hidden" name="REFERER" value="<?=$this->REFERER?>"/>
  </form>
</div>
<script language="JavaScript">
<!--
function toggleAll(source) {
  checkboxes = document.getElementsByName('prID[]');
  for(var i=0, n=checkboxes.length;i<n;i++) checkboxes[i].checked = source.checked;
}
function toggle(source) {
  checkboxes = document.getElementsByName('prID[]');
  all = true;
  for(var i=0, n=checkboxes.length;i<n;i++) if(checkboxes[i].checked == false) all = false;
  source.form['checkAll'].checked = all;
}
-->
</script>
<?php
	}

	private function initializePowiazaniaSave() {
		$prID = V::get('prID', array(), $_POST);
		if (!$prID) throw new Exception("Nie wybrano żadnego pracownika!");
		$reloadCache = V::get('reloadCache', 'off', $_POST);
		$sqlArr = [
			'ID' => $this->powiazanie['ID'],
			'FILE_STATUS' => 'TO_GENERATE',
			'BI_analiza_employees' => implode(',', $prID),	// TODO do wyjebania
		];
		if ($reloadCache == 'on') $sqlArr['BI_analiza_reloadCache'] = 'Y';

		$refPowiazaniaToPracownicy = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA',
			'default_db__x3A__BI_audit_ENERGA_PRACOWNICY:BI_audit_ENERGA_PRACOWNICY');
		foreach ($prID as $v) DB::getPDO()->insert($refPowiazaniaToPracownicy, ['PRIMARY_KEY' => $this->powiazanie['ID'], 'REMOTE_PRIMARY_KEY' => $v]);

		$acl = Core_AclHelper::getAclByNamespace('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI/BI_audit_ENERGA_RUM_KONTRAHENCI');
		$queryFeatures = $acl->buildQuery(['limit' => 0, 'cols' => ['ID']]);
		$kontrahenci = $queryFeatures->getItems();
		$refPowiazaniaToKontrahenci = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA',
			'default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI:BI_audit_ENERGA_RUM_KONTRAHENCI');
		foreach ($kontrahenci as $kontrahent) DB::getPDO()->insert($refPowiazaniaToKontrahenci, ['PRIMARY_KEY' => $this->powiazanie['ID'], 'REMOTE_PRIMARY_KEY' => $kontrahent['ID']]);

		$acl = Core_AclHelper::getAclByNamespace('default_db/BI_audit_KW_requested_person/BI_audit_KW_requested_person');
		$queryFeatures = $acl->buildQuery(['limit' => 0, 'cols' => ['ID']]);
		$requestedPersons = $queryFeatures->getItems();
		$refPowiazaniaToKwRequestedPreson = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA',
			'default_db__x3A__BI_audit_KW_requested_person:BI_audit_KW_requested_person');
		foreach ($requestedPersons as $requestedPerson) DB::getPDO()->insert($refPowiazaniaToKwRequestedPreson, ['PRIMARY_KEY' => $this->powiazanie['ID'], 'REMOTE_PRIMARY_KEY' => $requestedPerson['ID']]);

		$query = "select count(*) from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where BI_analiza_reloadCache = 'Y'";
		$result = DB::getPDO()->fetchValue($query);
		if ($result) $sqlArr['FILE_STATUS_info'] = 'Oczekuje na odświeżenie cache (wymagane dla innego rekordu)';
		elseif ($reloadCache == 'on') $sqlArr['FILE_STATUS_info'] = 'Oczekuje na odświeżenie cache';
		else $sqlArr['FILE_STATUS_info'] = "Oczekuje na wygenerowanie powiązań";

		$affected = DB::getDB()->UPDATE_OBJ('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', $sqlArr);
		if ($affected) {
			SE_Layout::alert('success','Oznaczono rekord do wygenerowania');
?>
<div class="container" style="text-align:center">
  <a href="<?=$this->REFERER?>" class="btn btn-primary">Powrót</a>
</div>
<?php
		} else {
			throw new Exception("Wystąpił nieznany błąd @initializePowiazaniaSave");
		}
	}

	private function initializePowiazania() {
		switch (V::get('action', '', $_POST)) {
			case "initialize":
				$this->initializePowiazaniaSave();
				break;
			default:
				$this->initializePowiazaniaForm();
		}
	}

	private function showPowiazania() {
//		$powiazanieDirLocation = $this->getDirectory('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', $this->powiazanie['ID']);
//		$xmlFile = "{$powiazanieDirLocation}/relations.xml";
//		echo "<pre>" . htmlentities(file_get_contents($xmlFile)) . "</pre>";
		echo "Statystyki znalezionych powiązań [TODO]";
	}

	private function powiazania($ID_BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA) {
		try {
			$powiazania = DB::getPDO()->fetchall("select * from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where ID = '{$ID_BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA}'");
			if (!$powiazania) throw new Exception("Błąd danych");
			$this->powiazanie = $powiazania[0];
			switch ($this->powiazanie['FILE_STATUS']) {
				case "NONE":
					$this->initializePowiazania();
					break;
				case "GENERATED":
					$this->showPowiazania();
					break;
				default:
					throw new Exception("Błędny status rekordu");
			}
		} catch (Exception $e) {
			SE_Layout::alert('danger',$e->getMessage());
			$_SESSION['REFERER'] = $this->REFERER;
?>
<div class="container" style="text-align:center">
  <a href="<?=$_SERVER['HTTP_REFERER']?>" class="btn btn-primary">Powrót</a>
</div>
<?php
		}
	}

	public function defaultAction() {
		SE_Layout::gora();
		SE_Layout::menu();
		if (isset($_SESSION['REFERER'])) {
			$this->REFERER = $_SESSION['REFERER'];
			unset($_SESSION['REFERER']);
		} elseif (V::get('REFERER', '', $_POST) != '') $this->REFERER = V::get('REFERER', '', $_POST);
		else $this->REFERER=$_SERVER['HTTP_REFERER'];
		if (V::get('ID_BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', 0, $_GET, 'int') > 0) $this->powiazania(V::get('ID_BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', 0, $_GET, 'int'));
		else SE_Layout::alert('danger','Błąd parametru');
		SE_Layout::dol();
	}

	public function doGenerate() {

		function getRefsTables($namespace) {
			try {
				$item = SchemaFactory::loadDefaultObject('SystemObject')->getItem($namespace, [ 'propertyName' => '*,field' ]);
				if (!($item['hasStruct'] && $item['isStructInstalled'] && $item['isObjectActive'])) throw new Exception("Object error");
				$fields = $item['field'];
				$refsTables = [];
				foreach ($fields as $field) {
					list($type, $child) = explode(":", $field['xsdType'], 2);
					if ($type == "ref" && $field['isActive'] && (!$field['isLocal'])) $refsTables[] = ACL::getRefTable($ns, $child);
				}
				return $refsTables;
			} catch (Exception $e) {
				echo "{$ns} - {$e->getMessage}";
				return [];
			}
		}

		function deleteResultsFromDB($ID) {
//DUPA
			$query = "delete from row, ref using BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row row join `CRM__#REF_TABLE__25` ref on row.ID=ref.REMOTE_PRIMARY_KEY where ref.PRIMARY_KEY='{$ID}'";
			DB::getPDO()->query($query);
		}

		function generatePhpScript($function) {
			return '<?php
ini_set("memory_limit", "4G");
define("DS", DIRECTORY_SEPARATOR);
define("APP_PATH_ROOT", "' . APP_PATH_ROOT . '");
define("APP_PATH_WWW", "' . APP_PATH_WWW . '");
define("APP_PATH_CONFIG", APP_PATH_ROOT . DS . "config");

ini_set("display_startup_errors", "0");
ini_set("log_errors", "1");
ini_set("error_log", "/var/log/apache2/error_log");

$_SERVER["SERVER_NAME"] = "' . $_SERVER['SERVER_NAME'] . '";
require_once(APP_PATH_ROOT . DS . "se-lib" . DS . "bootstrap.php");

Lib::loadClass("Router");
Router::getRoute("UrlAction_BiAuditGenerate")->' . $function . ';
';
		}

		try {
			### Ustawiamy zmienną sesji nazwy użytkownika, aby w rekordach historycznych było widać, że autorem systemu jest moduł BiAuditGenerate
			$_SESSION['ADM_ACCOUNT']="BiAuditGenerate";


			### Sprawdzenie czy modul BiAudit jest aktywny na danej instalacji Procesy5
			$moduleActive=DB::getPDO()->fetchValue("select count(*) from CRM_CONFIG where CONF_KEY='module_BiAudit' and CONF_VAL='on'");
			if (!$moduleActive) throw new Exception("Module BiAudit disabled/not installed.");


			### Ustawienie zmiennych
			$powiazaniaDirLocation = $this->getMainDirectory();
			$tasksDirLocation = $powiazaniaDirLocation . "/.tasks";
			$reloadCachePhpFile = $tasksDirLocation . "/reloadCache.php";
			$reloadCachePidFile = $tasksDirLocation . "/reloadCache.pid";
			$reloadCacheResultFile = $tasksDirLocation . "/reloadCache.result";
			$reloadCacheLogFile = $tasksDirLocation . "/reloadCache.log";

			### Utworzenie niezbędnych katalogów i plików
			if (!file_exists($tasksDirLocation)) mkdir($tasksDirLocation, 0755, true);
			if (!file_exists($tasksDirLocation)) throw new Exception('Error during creating temporary directory.');


			### Sprawdzenie czy któryś rekord wymaga odświeżenia cache
			$doGenerate = true;
			$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where BI_analiza_reloadCache = 'Y' and FILE_STATUS = 'TO_GENERATE'";
			$result = DB::getPDO()->fetchAll($query);
			if ($result) {
				$doGenerate = false;
				$sqlArr = [];

				if (file_exists($reloadCachePhpFile)) {
					if (file_exists($reloadCacheResultFile)) {
						$reloadCacheResult = file_get_contents($reloadCacheResultFile);
						if ($reloadCacheResult == "ok") {
							$sqlArr = [
								'FILE_STATUS_info' => 'Odświeżono cache, oczekiwanie na wygenerowanie powiązań',
								'BI_analiza_reloadCache' => 'N',
							];
							$doGenerate = true;
						} else {
							$sqlArr = [
								'FILE_STATUS' => 'ERROR',
								'FILE_STATUS_info' => "Wystąpił błąd podczas odświeżania cache ({$reloadCacheResult})",
							];
							$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where FILE_STATUS = 'TO_GENERATE'";
							$result = DB::getPDO()->fetchAll($query);
						}
						unlink($reloadCachePhpFile);
						unlink($reloadCachePidFile);
						unlink($reloadCacheResultFile);
					} elseif (file_exists($reloadCachePidFile)) {
						$pid = file_get_contents($reloadCachePidFile);
						$processRunning = trim(shell_exec("ps -p {$pid} -o command | grep -v '^COMMAND$'"));
						$processShouldBeRunning = "php {$reloadCachePhpFile}";
						if ($processRunning != $processShouldBeRunning) {
							if (!file_exists($reloadCacheResultFile)) {
								$sqlArr = [
									'FILE_STATUS' => 'ERROR',
									'FILE_STATUS_info' => "Wystąpił nieznany błąd podczas odświeżania cache - nie znaleziono procesu potomnego",
								];
								$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where FILE_STATUS = 'TO_GENERATE'";
								$result = DB::getPDO()->fetchAll($query);
								unlink($reloadCachePhpFile);
								unlink($reloadCachePidFile);
								unlink($reloadCacheResultFile);
							}
						}
					}
				} else {
					$query = "select count(*) from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where FILE_STATUS = 'IN_PROGRESS'";
					$result2 = DB::getPDO()->fetchValue($query);
					if ($result2) {
						$sqlArr = [
							'FILE_STATUS_info' => 'Wstrzymano odświeżanie cache - oczekiwanie na dokończenie szukania powiązań dla innych rekordów',
						];
					} else {
						$sqlArr = [
							'FILE_STATUS_info' => 'W trakcie odświeżania cache',
						];
						file_put_contents($reloadCachePhpFile, generatePhpScript('doReloadCache()'));
						shell_exec('su - root -c "php ' . $reloadCachePhpFile . ' > ' . $reloadCacheLogFile. ' 2>&1 &"');
					}
				}

				if ($sqlArr) {
					foreach ($result as $row) {
						DB::getDB()->UPDATE_OBJ('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', array_merge(['ID' => $row['ID']], $sqlArr));
					}
				}

			}


			### Generowanie powiązań
			if ($doGenerate) {
				$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where FILE_STATUS = 'TO_GENERATE' and ID = 1"; //TODO usunac and ID = 1
				$result = DB::getPDO()->fetchAll($query);
				foreach ($result as $row) {
					$sqlArr = [
						'ID' => $row['ID'],
						'FILE_STATUS' => 'IN_PROGRESS',
						'FILE_STATUS_info' => 'W trakcie generowania powiązań',
					];
					DB::getDB()->UPDATE_OBJ('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', $sqlArr);
					deleteResultsFromDB($row['ID']);

					$generatePowiazaniaResultFile = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}.result";
					$generatePowiazaniaPhpFile = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}.php";
					$generatePowiazaniaLogFile = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}.log";
					file_put_contents($generatePowiazaniaPhpFile, generatePhpScript("doGeneratePowiazania({$row['ID']})"));
					shell_exec('su - root -c "php ' . $generatePowiazaniaPhpFile . ' > ' . $generatePowiazaniaLogFile. ' 2>&1 &"');
				}
			}


			### Weryfikacja świeżo wygenerowanych powiązań
			$query = "select ID, BI_analiza_depth from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where FILE_STATUS = 'IN_PROGRESS'";
			$result = DB::getPDO()->fetchAll($query);
			foreach ($result as $row) {
				$generatePowiazaniaResultFile = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}.result";
				$generatePowiazaniaPidFile = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}.pid";
				$generatePowiazaniaPhpFile = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}.php";
				$error = false;
				$success = false;
				$sqlArr = [];
				if (file_exists($generatePowiazaniaResultFile)) {
					$xmlFile = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}.xml";
					if (file_exists($xmlFile)) {
						if (filesize($xmlFile)) {
							$generatePowiazaniaResult = file_get_contents($generatePowiazaniaResultFile);
							if ($generatePowiazaniaResult == "ok") {
								$powiazanieDirLocation = $this->getDirectory('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', $row['ID']);
								if (!file_exists($powiazanieDirLocation)) mkdir($powiazanieDirLocation, 0777, false);
								if (!file_exists($powiazanieDirLocation)) $error = "Nie udało się wgrać pliku xml - nie można utworzyć katalogu dla rekordu";
								else $success = true;
							} else $error = "Wystąpił błąd podczas generowania powiązań ({$generatePowiazaniaResult})";
						} else $error = "Wystąpił nieznany błąd - plk xml został wygenerowany niepoprawnie";
					} else $error = "Wystąpił nienzany błąd - nie znaleziono pliku xml";
				} elseif (file_exists($generatePowiazaniaPidFile)) {
					$pid = file_get_contents($generatePowiazaniaPidFile);
					$processRunning = trim(shell_exec("ps -p {$pid} -o command | grep -v '^COMMAND$'"));
					$processShouldBeRunning = "php {$generatePowiazaniaPhpFile}";
					if ($processRunning != $processShouldBeRunning) {
						if (!file_exists($generatePowiazaniaResultFile)) $error = "Wystąpił nieznany błąd w przetwarzaniu - nie znaleziono procesu potomnego";
					}
				}

				if ($error) {
					$sqlArr = [
						'FILE_STATUS' => 'ERROR',
						'FILE_STATUS_info' => $error,
					];
					deleteResultsFromDB($row['ID']);
					if (file_exists($xmlFile)) unlink($xmlFile);
				} elseif ($success) {
					$sqlArr = [
						'FILE_STATUS' => 'GENERATED',
						'FILE_STATUS_info' => 'Poprawnie wygenerowano powiązania',
					];
					$destXmlFile = "{$powiazanieDirLocation}/relations.xml";
					rename($xmlFile, $destXmlFile);
				}

				if ($sqlArr) {
					$sqlArr['ID'] = $row['ID'];
					DB::getDB()->UPDATE_OBJ('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', $sqlArr);
					if (file_exists($generatePowiazaniaPhpFile)) unlink($generatePowiazaniaPhpFile);
					if (file_exists($generatePowiazaniaPidFile)) unlink($generatePowiazaniaPidFile);
					if (file_exists($generatePowiazaniaResultFile)) unlink($generatePowiazaniaResultFile);
				}
			}

		} catch (Exception $e) {
			echo $e->getMessage()."\n";
		}
	}

	private function reloadCache_reTeryt() {

		Lib::loadClass('Teryt');

		function reTeryt($table) {
			$tableConf = [
				'BI_audit_ENERGA_RUM_KONTRAHENCI' => [
					'kodPocztowy' => 'Kod_pocztowy',
					'miejscowosc' => 'Miejscowosc',
					'ulica' => 'Ulica',
				],
				'BI_audit_ENERGA_PRACOWNICY_adresy' => [
					'kodPocztowy' => 'kodPocztowy',
					'miejscowosc' => 'miejscowosc',
					'ulica' => 'ulica',
				],
			];

			$columns = ['kodPocztowy', 'miejscowosc', 'ulica'];

			$query = "select * from `{$table}` where TERYT_SYM is null";
			try {
				$adresy = DB::getPDO()->fetchall($query);
				foreach ($adresy as $adres) {
					$search = [];
					foreach ($columns as $column) $search[$column] = $adres[$tableConf[$table][$column]];
					try {
						$teryt = Teryt::search($search);
						if ($teryt) {
							$sqlArr = ['TERYT_SYM' => $teryt['SYM']];
							$sqlArr['TERYT_SYM_UL'] = (isset($teryt['SYM_UL']) ? $teryt['SYM_UL'] : 0);
							DB::getPDO()->update($table, "ID", $adres['ID'], $sqlArr);
						}
					} catch (Exception $e) {
					}
				}
			} catch (Exception $e) {
			}
		}

		reTeryt('BI_audit_ENERGA_RUM_KONTRAHENCI');
		reTeryt('BI_audit_ENERGA_PRACOWNICY_adresy');
	}

	private function reloadCache_updateAll() {
		$tables = [
			'BI_audit_ENERGA_PRACOWNICY' => [
				'fields' => [
					'nip' => 'nip',
					'regon' => 'regon',
					'pesel' => 'pesel',
				],
				//'where' => '',
			],
			'BI_audit_ENERGA_PRACOWNICY_adresy' => [
				'fields' => [
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'nrBudynku',
					'nrLokalu' => 'nrLokalu',
				],
				//'where' => '',
			],
			'BI_audit_ENERGA_RUM_KONTRAHENCI' => [
				'fields' => [
					'nip' => 'NIP',
					'regon' => 'REGON',
					'pesel' => 'PESEL',
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'Numer_budynku',
					'nrLokalu' => 'Numer_mieszkania_lokalu',
				],
				'where' => "ownCompany != 'Y'",
			],
			'BI_audit_KW_requested_person' => [
				'fields' => [
					'pesel' => 'Seller_person_pesel',
					'krs' => 'Seller_person_KRS',
					'nip' => 'Seller_person_NIP',
					'regon' => 'Seller_person_REGON',
				],
				//'where' => '',
			],
		];
		$queries[] = "delete from BI_audit_ALL where BASE = 0";
		$queries[] = "delete from BI_audit_ALL_ref where BASE = 0";
		$queries[] = "delete from BI_audit_ALL_ref_RELATIONS where BASE = 0";
		foreach ($tables as $name => $table) {
			if (isset($table['where']) && $table['where']) $where = "where {$table['where']}";
			else $where = '';
			$queries[] = "insert into BI_audit_ALL (REMOTE_TABLE, REMOTE_ID, " . implode(", ", array_keys($table['fields'])) . ") select '{$name}', ID, " . implode(", ", $table['fields']) . " from {$name} {$where}";
		}
		$queries[] = "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 and BASE = 0";
		$queries[] = "set @var = (select round(coalesce(max(ID), '0.5')) from BI_audit_ALL_ref_RELATIONS)";
		$queries[] = "insert into BI_audit_ALL_ref_RELATIONS (ID, RELATION) select @var:=@var*2, a.RELATION from (select a.REMOTE_TABLE as RELATION from BI_audit_ALL a where a.REMOTE_TABLE not in ('BI_audit_ENERGA_RUM_KONTRAHENCI', 'BI_audit_KW_requested_person') group by a.REMOTE_TABLE) as a left join BI_audit_ALL_ref_RELATIONS r on a.RELATION = r.RELATION where r.RELATION is null";
		$queries[] = "insert into BI_audit_ALL_ref (ID1, ID2, RELATION_ID) select all1.ID, all2.ID, rel.ID from BI_audit_ALL as all1 join `CRM__#REF_TABLE__27` as ref on all1.REMOTE_TABLE = 'BI_audit_ENERGA_PRACOWNICY' and all1.REMOTE_ID = ref.PRIMARY_KEY join BI_audit_ALL as all2 on all2.REMOTE_TABLE = 'BI_audit_ENERGA_PRACOWNICY_adresy' and ref.REMOTE_PRIMARY_KEY = all2.REMOTE_ID join BI_audit_ALL_ref_RELATIONS rel on all1.REMOTE_TABLE = rel.RELATION";

		foreach ($queries as $query) DB::getPDO()->query($query);
		$BiAuditRelations = new BiAuditRelations();
		$query = "select ID from BI_audit_ALL where BASE = 0";
		$result = DB::query($query);
		while ($row = DB::fetch($result)) $BiAuditRelations->findRelations($row->ID);
	}

	public function doReloadCache() {
		$powiazaniaDirLocation = $this->getMainDirectory();
		$tasksDirLocation = $powiazaniaDirLocation . "/.tasks";
		$reloadCachePidFile = $tasksDirLocation . "/reloadCache.pid";
		$reloadCacheResultFile = $tasksDirLocation . "/reloadCache.result";
		file_put_contents($reloadCachePidFile, getmypid());
		try {
//			$this->reloadCache_reTeryt();	// TODO remove comment
			$this->reloadCache_updateAll();
			file_put_contents($reloadCacheResultFile, "ok");
		} catch (Exception $e) {
			file_put_contents($reloadCacheResultFile, $e->getMessage());
		}
	}

	public function doGeneratePowiazania($ID) {
		$powiazaniaDirLocation = $this->getMainDirectory();
		$tasksDirLocation = $powiazaniaDirLocation . "/.tasks";
		$resultFile = "{$tasksDirLocation}/generatePowiazania-{$ID}.result";
		$xmlFile = "{$tasksDirLocation}/generatePowiazania-{$ID}.xml";
		$pidFile = "{$tasksDirLocation}/generatePowiazania-{$ID}.pid";
		file_put_contents($pidFile, getmypid());

		$tablesConf = [
			'BI_audit_CEIDG' => ['ID', 'imie', 'nazwisko', 'nip', 'regon', 'firma', 'miejscowosc'],
			'BI_audit_CEIDG_pelnomocnicy' => ['ID', 'nazwa', 'nip', 'miejscowosc'],
			'BI_audit_CEIDG_powiazania' => ['ID', 'nip', 'regon'],
			'BI_audit_ENERGA_PRACOWNICY' => ['ID', 'imiona', 'nazwisko', 'pesel', 'nip', 'regon'],
			'BI_audit_ENERGA_PRACOWNICY_adresy' => ['ID', 'miejscowosc'],
			'BI_audit_ENERGA_RUM_KONTRAHENCI' => ['ID', 'Pelna_nazwa_kontrahenta', 'NIP', 'REGON', 'PESEL', 'KRS', 'Miejscowosc'],
			'BI_audit_KRS' => ['ID', 'krs', 'nip', 'regon', 'nazwa', 'A_miejscowosc'],
			'BI_audit_KRS_company' => ['ID', 'nazwa', 'regon', 'krs'],
			'BI_audit_KRS_person' => ['ID', 'nazwisko', 'imiona', 'pesel'],
			'BI_audit_KW_requested_person' => ['ID', 'Seller_person', 'Seller_person_pesel', 'Seller_person_KRS', 'Seller_person_NIP', 'Seller_person_REGON'],
		];

		try {
			$BiAuditPowiazania = new BiAuditPowiazania($ID);
			if (!$BiAuditPowiazania->powiazaniaFound()) throw new Exception("Nie znaleziono żadnych powiązań");
			file_put_contents($xmlFile, $BiAuditPowiazania->asXml());
			$BiAuditPowiazania->saveToDb();
/*			$powiazania = $BiAuditPowiazania->asArray('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA');
			if (!$powiazania) return false;
			DB::getPDO()->query($query);
			foreach ($powiazania['BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row'] as $row) {
				$powiazaniaRowSqlArr = [];
				$string_concat_path = [];
				foreach ($row['object'] as $i => $object) {
					$powiazaniaRowObjectSqlArr = [];
					$table = array_keys($object)[0];
					if (!isset($tablesConf[$table])) throw new Exception("Błąd danych konfiguracyjnych");
					$concat = "table='{$table}'";
					if (isset($object['relation_from'])) $concat .= ";relation_from='{$object['relation_from']}'";
					foreach ($tablesConf[$table] as $field) {
						if ($object[$table][$field]) $concat .= ";{$field}='{$object[$table][$field]}'";
					}
					if ($i == 0) $powiazaniaRowSqlArr['string_concat_from'] = $concat;
					elseif ($i == count($row['object']) - 1) $powiazaniaRowSqlArr['string_concat_to'] = $concat;
					else $string_concat_path[] = $concat;
				}
				$powiazaniaRowSqlArr['string_concat_path'] = implode(' / ', $string_concat_path);
				$id_row = DB::getPDO()->insert("BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row", $powiazaniaRowSqlArr);
				if (!$id_row) throw new Exception("Błąd bazy danych");
				DB::getPDO()->insert('CRM__#REF_TABLE__25', ["PRIMARY_KEY" => $ID, "REMOTE_PRIMARY_KEY" => $id_row]);
			}
*/
			file_put_contents($resultFile, "ok");
		} catch (Exception $e) {
			file_put_contents($resultFile, $e->getMessage());
		}
	}

	public function reinstallAction() {
		$this->reinstall();
		die('OK');
	}

	public function reinstall() {
		$sqlList = array();
		//$sqlList['RemoveTable'] = "DROP TABLE IF EXISTS `CRM_CONFIG`";
		$sqlList['ActivateBiAuditGenerate'] = <<<SQL
REPLACE INTO `CRM_CONFIG` (`CONF_KEY`,`CONF_VAL`) VALUES ('module_BiAudit','on');
SQL;

		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		foreach ($sqlList as $sqlName => $sql) {
			$res = $db->query($sql);
			if ($db->has_errors()) {
				throw new Exception("DB Errors at sql '{$sqlName}': " . implode("\n<br>", $db->get_errors()));
			}
		}
	}
}

                
class BiAuditRelations {
	private $RELATIONS_ID = [];

	private $relations = [
		'nip' => ['nip'],
		'regon' => ['regon'],
		'krs' => ['krs'],
		'pesel' => ['pesel'],
		'adres' => ['TERYT_SYM', 'TERYT_SYM_UL', 'nrDomu', 'nrLokalu'],
	];

	private $destTables = ['BI_audit_ENERGA_RUM_KONTRAHENCI', 'BI_audit_KW_requested_person'];
	private $sourceTable = ['BI_audit_ENERGA_PRACOWNICY', 'BI_audit_ENERGA_PRACOWNICY_adresy'];

	public function __construct() {
		$query = "select * from BI_audit_ALL_ref_RELATIONS";
		$result = DB::getPDO()->fetchAll($query);
		foreach ($result as $row) $this->RELATIONS_ID[$row['RELATION']] = $row['ID'];
	}

	private function getRelationID($name) {
		if (!isset($this->RELATIONS_ID[$name])) {
			$query = "select round(coalesce(max(ID), 0.5) * 2) as newID from BI_audit_ALL_ref_RELATIONS";
			$newID = DB::getPDO()->fetchValue($query);
			$this->RELATIONS_ID[$name] = $newID;
			$query = "insert into BI_audit_ALL_ref_RELATIONS (ID, RELATION) values ('{$newID}', '{$name}')";
			DB::getPDO()->query($query);
		}
		return $this->RELATIONS_ID[$name];
	}

	public function findRelations($ID) {
		$query = "select * from BI_audit_ALL where ID = '{$ID}'";
		if (!($result = DB::getPDO()->fetchAll($query))) return false;
		$row = $result[0];

		$joins = [];
		$items = '';

		foreach ($this->relations as $name => $columns) {
			$ok = 0;
			$join = [];
			foreach ($columns as $column) {
				$join[] = "t1.{$column} = t2.{$column}";
				if ($row[$column]) $ok++;
			}
			if ($ok / count($columns) > 0.5) {
				$joins[] = "(" . implode(" and ", $join) . ")";
				$items .= ", if(" . implode(" and ", $join) . ", 1, 0) as {$name}";
			}
		}
		if (!$joins) return false;

		$query = "select t2.ID as ID, t1.REMOTE_TABLE as REMOTE_TABLE_1, t2.REMOTE_TABLE as REMOTE_TABLE_2 {$items} " .
			"from BI_audit_ALL t1 join BI_audit_ALL t2 on " . implode(" or ", $joins) . " where t1.ID = {$ID} and (t2.BASE = 1 or t2.ID > {$ID})";
		$result = DB::getPDO()->fetchAll($query);
		foreach ($result as $row) {
			$relationID = 0;
			foreach ($this->relations as $name => $v) {
				if (isset($row[$name]) && $row[$name]) $relationID += $this->getRelationID($name);
			}
			$start1 = (in_array($row['REMOTE_TABLE_1'], $this->sourceTables) ? 1 : 0);
			$start2 = (in_array($row['REMOTE_TABLE_2'], $this->sourceTables) ? 1 : 0);
			$end1 = (in_array($row['REMOTE_TABLE_1'], $this->destTables) ? 1 : 0);
			$end2 = (in_array($row['REMOTE_TABLE_2'], $this->destTables) ? 1 : 0);
			if (!($start2 || $end1)) {
				$query = "insert into BI_audit_ALL_ref (ID1, ID2, RELATION_ID, END) values ({$ID}, {$row['ID']}, '{$relationID}', {$end2})";
				DB::getPDO()->query($query);
			}
			if (!($start1 || $end2)) {
				$query = "insert into BI_audit_ALL_ref (ID2, ID1, RELATION_ID, END) values ({$ID}, {$row['ID']}, '{$relationID}', {$end1})";
				DB::getPDO()->query($query);
			}
		}
	}
}


class BiAuditPowiazania {

	private $DEPTH;
	private $ID;
	private $endNodes;
	private $path = [];
	private $results = [];
	private $items_results = [];
	private $relations = [];
	private $destTables = ['BI_audit_ENERGA_RUM_KONTRAHENCI', 'BI_audit_KW_requested_person'];

	public function __construct($ID = 0) {
		if (!$ID) throw new Exception("Wrong ID parameter");
		$query = "select BI_analiza_depth, BI_analiza_employees from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where ID = '{$ID}' and FILE_STATUS = 'IN_PROGRESS' and BI_analiza_reloadCache != 'Y'";
		$result = DB::getPDO()->fetchAll($query);
		if (!$result) throw new Exception("Błąd danych");
		$powiazanie = $result[0];
		$this->DEPTH = (int) $powiazanie['BI_analiza_depth'];
		$this->ID = $ID;

		// TODO przerobic na refy
		$subQueries = [];
		foreach ($this->destTables as $destTable) {
			$refTable = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', "default_db__x3A__{$destTable}:{$destTable}");
			$subQueries[] = "select `all`.ID from BI_audit_ALL `all` join `{$refTable}` ref on `all`.REMOTE_TABLE = '{$destTable}' and `all`.REMOTE_ID = ref.REMOTE_PRIMARY_KEY and ref.PRIMARY_KEY = '{$ID}'";
		}
		$query = implode(" union ", $subQueries);
		$result = DB::getPDO()->fetchAll($query);
		if (!$result) throw new Exception("Błąd danych - nie zdefinowano żadnego końcowego obiektu");
		$this->endNodes = array_map('reset', $result);

		$refPowiazaniaToPracownicy = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA',
			'default_db__x3A__BI_audit_ENERGA_PRACOWNICY:BI_audit_ENERGA_PRACOWNICY');
		$query = "select `all`.ID as ID from BI_audit_ALL `all` join `{$refPowiazaniaToPracownicy}` ref 
			on `all`.REMOTE_TABLE = 'BI_audit_ENERGA_PRACOWNICY' and `all`.REMOTE_ID = ref.REMOTE_PRIMARY_KEY and ref.PRIMARY_KEY = '{$ID}'";
		$result = DB::getPDO()->fetchAll($query);
		foreach ($result as $row) $this->findPowiazania($row['ID']);
	}

	public function findPowiazania($ID, $relation = "", $end = 0) {
		if (isset($this->path[$ID])) return false;

		$this->path[$ID] = $relation;
		if (!$relation) $relation = 0;

		if ($end) {
			if (in_array($ID, $this->endNodes)) $this->results[] = $this->path;
			array_pop($this->path);
			return true;
		}

		if (count($this->path) > $this->DEPTH) {
			array_pop($this->path);
			return false;
		}

		$nodes = [];
		if ($relation) $where = "and ({$relation} & ref.RELATION_ID) != {$relation}";
		else $where = "";
		$query = "select ref.ID2, RELATION_ID, END from BI_audit_ALL_ref ref where ref.ID1 = '{$ID}' {$where}";
		$result = DB::query($query);
		while ($row = DB::fetch($result)) $nodes[$row->ID2] = ['relation' => (int) $row->RELATION_ID, 'end' => $row->END];
		foreach ($nodes as $node => $data) $this->findPowiazania($node, $data['relation'], $data['end']);
		array_pop($this->path);
	}

	private function relationName($ID) {
		if (!isset($this->relations[$ID])) {
			$query = "select RELATION from BI_audit_ALL_ref_RELATIONS where ID & '{$ID}' order by ID";
			$result = DB::getPDO()->fetchAll($query);
			$rels = array_map('reset', $result);
			$this->relations[$ID] = implode(", ", $rels);
		}
		return $this->relations[$ID];
	}

	private function generateItemsResults() {
		$xmlRoot2 = "BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA";
		$xmlElements = "BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row";

		$this->items_results = [];
		$items_kontrahenci = [];
		$items_kw_person = [];
		$kontrahenci = [];
		$kw_person = [];

		foreach ($this->results as $result) {
			$item_results = [];
			foreach ($result as $ID => $rel) {
				$query = "select REMOTE_TABLE, REMOTE_ID from BI_audit_ALL where ID = '{$ID}'";
				$row = DB::getPDO()->fetchFirst($query);
				$query = "select * from `{$row['REMOTE_TABLE']}` where ID = '{$row['REMOTE_ID']}'";
				$obj = DB::fetch(DB::query($query));
				$item_result = [$row['REMOTE_TABLE'] => (array) $obj];
				if ($rel) $item_result['relation_from'] = $this->relationName($rel);
				$item_results['object'][] = $item_result;
				if ($row['REMOTE_TABLE'] == "BI_audit_ENERGA_RUM_KONTRAHENCI" && (!in_array($row['REMOTE_ID'], $kontrahenci))) {
					$kontrahenci[] = $row['REMOTE_ID'];
					$query = "select umowy.* from BI_audit_ENERGA_RUM_UMOWY umowy join `CRM__#REF_TABLE__23` ref on umowy.ID = ref.PRIMARY_KEY where ref.REMOTE_PRIMARY_KEY = '{$row['REMOTE_ID']}'";
					$res = DB::query($query);
					if (mysql_num_rows($res)) {
						$umowy = [];
						while ($obj = DB::fetch($res)) $umowy[] = (array) $obj;
						$item_kontrahenci['ID'] = $row['REMOTE_ID'];
						$item_kontrahenci['BI_audit_ENERGA_RUM_UMOWY'] = $umowy;
						$items_kontrahenci[] = $item_kontrahenci;
					}
				} elseif ($row['REMOTE_TABLE'] == "BI_audit_KW_requested_person" && (!in_array($row['REMOTE_ID'], $kw_person))) {
					$kw_person[] = $row['REMOTE_ID'];
					$query = "select kw.* from BI_audit_KW_requested kw join `CRM__#REF_TABLE__24` ref on kw.ID = ref.PRIMARY_KEY where ref.REMOTE_PRIMARY_KEY = '{$row['REMOTE_ID']}'";
					$res = DB::query($query);
					if (mysql_num_rows($res)) {
						$kw = [];
						while ($obj = DB::fetch($res)) $kw[] = (array) $obj;
						$item_kw_person['ID'] = $row['REMOTE_ID'];
						$item_kw_person['BI_audit_KW_requested'] = $kw;
						$items_kw_person[] = $item_kw_person;
					}
				}
			}
			$this->items_results[$xmlRoot2][$xmlElements][] = $item_results;
		}
		$this->items_results[$xmlRoot2]['BI_audit_ENERGA_RUM_KONTRAHENCI'] = $items_kontrahenci;
		$this->items_results[$xmlRoot2]['BI_audit_KW_requested_person'] = $items_kw_person;
	}

	public function saveToDb() {
		if (!$this->results) return null;
		$refPowiazaniaToPowiazaniaRow = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA',
			'default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row:BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row');
		$refPowiazaniaRowToPowiazaniaRowObject = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row',
			'default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object:BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object');
		foreach ($this->results as $result) {
			$IdPowiazaniaRow = DB::getPDO()->insert('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row', []);
			DB::getPDO()->insert($refPowiazaniaToPowiazaniaRow, ['PRIMARY_KEY' => $this->ID, 'REMOTE_PRIMARY_KEY' => $IdPowiazaniaRow]);
			foreach ($result as $ID => $rel) {
				$query = "select REMOTE_TABLE, REMOTE_ID from BI_audit_ALL where ID = '{$ID}'";
				$object = DB::getPDO()->fetchFirst($query);
				$idPowiazaniaRowObject = DB::getPDO()->insert('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object', ['relation_from' => $rel]);
				DB::getPDO()->insert($refPowiazaniaRowToPowiazaniaRowObject, ['PRIMARY_KEY' => $IdPowiazaniaRow, 'REMOTE_PRIMARY_KEY' => $idPowiazaniaRowObject]);
				$ref = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object',
					"default_db__x3A__{$object['REMOTE_TABLE']}:{$object['REMOTE_TABLE']}");
				DB::getPDO()->insert($ref, ['PRIMARY_KEY' => $idPowiazaniaRowObject, 'REMOTE_PRIMARY_KEY' => $object['REMOTE_ID']]);
			}
		}
	}

	public function asXml() {
		if (!$this->results) return null;
		if (!$this->items_results) $this->generateItemsResults();
		$xmlRoot = "RelatedFeatureRoot";
		return V::arrayToXML($this->items_results, true, $xmlRoot);
	}

	public function asArray($subArray = null) {
		if (!$this->results) return null;
		if (!$this->items_results) $this->generateItemsResults();
		if ($subArray) {
			if (isset($this->items_results[$subArray])) return $this->items_results[$subArray];
			else return [];
		} else return $this->items_results;
	}

	public function powiazaniaFound() {
		if ($this->results) return true;
		else return false;
	}
}