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

<?php

//TODO optimal - akcja aby doruzcic do pracownikow do analizy jakas spolke z kontekstu przegladania KRS - zapytanie przyklad:
// insert ignore into BI_audit_ENERGA_PRACOWNICY (imiona, nazwisko, pesel, source )
// select p.imiona, p.nazwisko, p.pesel, 'ENSO_STORA/zadanie' from `BI_audit_KRS_person` as p
// right join `CRM__#REF_TABLE__12` as r on r.`REMOTE_PRIMARY_KEY`=p.ID
//  right join BI_audit_KRS as k on k.ID=r.`PRIMARY_KEY`
//  where k.`nazwa` like '%Stora%enso%';



//serwisowy skrypt jest tutaj pod nazwa xyz.

//TODO mozliwosc przerwania przetwarzania

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

class Route_UrlAction_BiAuditGenerate extends RouteBase {

	private $powiazanie = null;
	private $SOURCE = null;
	private $REFERER;
	private $POWIAZANIA_ID = null;

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

	private static 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 static function getUrl($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);
		return $uploader->getFolderWeb() . "/" . $uploader->getDestFolder(true);
	}

	private static 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 static function parseNipRegon($string) {
		$nip = null; $regon = null;

		preg_match('/^(.*[^[:digit:]])?([[:digit:]]{10})([^[:digit:]].*)?$/', str_replace('-', '', $string), $matches);
		if (!empty($matches[2])) $nip = trim($matches[2]);
		if ($nip && !V::isNip($nip)) $nip = null;

		preg_match('/^(.*[^[:digit:]])?([[:digit:]]{9})([^[:digit:]].*)?$/', str_replace('-', '', $string), $matches);
		if (!empty($matches[2])) $regon = trim($matches[2]);
		if ($regon && !V::isRegon($regon)) $regon = null;

		return [$nip, $regon];
	}

	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>
              <td>source</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();
		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>
              <td><?=$pracownik['source']?></td>
            </tr>
<?php
		}
?>
          </tbody>
        </table>
      </div>
    </div>
<!--    <div class="form-group">
      <label class="col-sm-2 control-label">
         Odśwież cache
      </label>
      <div class="col-sm-2">
        <select name="reloadCache" class="form-control">
          <option value="No" selected>Nie</option>
<!-#-          <option value="Part">Częściowe</option>-#->
          <option value="Full">Pełne</option>
        </select>
      </div>
    </div>
    <div class="form-group">
      <div class="col-sm-offset-2 col-sm-10">
<!-#-        Częściowe odświeżenie wymagane w przypadku, gdy jedynie dodano nowe rekordy w tabelach pracowników/kontrahentów.<br/>-#->
        Pełne odświeżenie wymagane w przypadku edycji/usunięcia istniejących rekordów w tabelach pracowników/kontrahentów.<br/>
        Uwaga! Jeżeli aktywne są inne zadania, te zadanie zostanie wstrzymane do momentu ich ukończenia.
      </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 btn-sm" 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!");

	$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();

	$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();

	if (!($kontrahenci || $requestedPersons)) throw new Exception("Nie znaleziono żadnego obiektu końcowego");

	$sqlUpdate = ['FILE_STATUS' => 'TO_GENERATE'];

	$reloadCache = V::get('reloadCache', 'No', $_POST);
	$reloadCacheAvailable = ['Full', 'Part', 'No'];
	if (!in_array($reloadCache, $reloadCacheAvailable)) throw new Exception("Błąd formularza!");
	$sqlUpdate['BI_analiza_reloadCache'] = $reloadCache;

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

	if ($this->powiazanie) { // wyzwolone z poziomu tabeli BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA - aktualizujemy istniejący rekord
		$powiazanieID = $this->powiazanie['ID'];
	} else { // wyzwolone z poziomu tabeli BI_audit_ENERGA_PRACOWNICY - dodajemy nowy rekord
		if (count($prID) != 1) throw new Exception("Błąd formularza!");
		if (!($lAppoitmentInfo = V::get('info', false, $_POST))) throw new Exception("Błąd formularza!");
		if (!($BiAnalizaMinDepth = V::get('minDepth', false, $_POST))) throw new Exception("Błąd formularza!");
		if (!($BiAnalizaMaxDepth = V::get('maxDepth', false, $_POST))) throw new Exception("Błąd formularza!");
		if (!($BiAnalizaOnlyTargets = V::get('onlyTargets', false, $_POST))) throw new Exception("Błąd formularza!");
		$query = "select * from BI_audit_ENERGA_PRACOWNICY where ID='{$prID[0]}'";
		$result = DB::getPDO()->fetchFirst($query);
		if (!$result) throw new Exception("Błąd formularza!");
		$sqlInsert = [
			'L_APPOITMENT_INFO' => $lAppoitmentInfo,
			'A_STATUS' => 'NORMAL',
			'A_STATUS_INFO' => 'Dodane przez BiAuditGenerate z poziomu tabeli BI_audit_ENERGA_PRACOWNICY',
			'A_ADM_COMPANY' => $result['A_ADM_COMPANY'],
			'A_CLASSIFIED' => $result['A_CLASSIFIED'],
			'FILE_STATUS' => 'NONE',
			'FILE_STATUS_info' => 'Oczekuję na zdefiniowanie danych wejściowych',
			'BI_analiza_reloadCache' => $sqlUpdate['BI_analiza_reloadCache'],
			'BI_analiza_minDepth' => $BiAnalizaMinDepth,
			'BI_analiza_maxDepth' => $BiAnalizaMaxDepth,
			'BI_analiza_onlyTargets' => $BiAnalizaOnlyTargets,
		];
		$powiazanieID = DB::getDB()->ADD_NEW_OBJ('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', (object)$sqlInsert);
		if ($powiazanieID) SE_Layout::alert('success','Dodano rekord do wygenerowania powiązań');
		else throw new Exception("Wystąpił nieznany błąd @initializePowiazaniaSave");
	}

	$sqlUpdate['ID'] = $powiazanieID;

	self::truncatePowiazaniaFromDB($powiazanieID);

	$refPowiazaniaToPracownicy = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', 'BI_audit_ENERGA_PRACOWNICY', true);
	foreach ($prID as $v) DB::getPDO()->insert($refPowiazaniaToPracownicy, ['PRIMARY_KEY' => $powiazanieID, 'REMOTE_PRIMARY_KEY' => $v]);

	$refPowiazaniaToKontrahenci = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', 'BI_audit_ENERGA_RUM_KONTRAHENCI', true);
	foreach ($kontrahenci as $kontrahent) DB::getPDO()->insert($refPowiazaniaToKontrahenci, ['PRIMARY_KEY' => $powiazanieID, 'REMOTE_PRIMARY_KEY' => $kontrahent['ID']]);

	$refPowiazaniaToKwRequestedPreson = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', 'BI_audit_KW_requested_person', true);
	foreach ($requestedPersons as $requestedPerson) DB::getPDO()->insert($refPowiazaniaToKwRequestedPreson, ['PRIMARY_KEY' => $powiazanieID, 'REMOTE_PRIMARY_KEY' => $requestedPerson['ID']]);

	$affected = DB::getDB()->UPDATE_OBJ('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', $sqlUpdate);
	if ($affected) {
		if ($this->powiazanie) {
			SE_Layout::alert('success','Oznaczono rekord do wygenerowania powiązań');
?>
<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() {
//		echo "Statystyki znalezionych powiązań [TODO]";
	$dir = self::getDirectory('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', $this->powiazanie['ID']);
	$url = self::getUrl('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', $this->powiazanie['ID']);

	$reports = [
		'pdf' => [
			'files' => glob("{$dir}/relations*.pdf"),
			'label' => 'Raporty PDF:<br/>&nbsp;',
			'button' => 'Pokaż raport PDF',
			'regex' => "/^relations(-{$this->powiazanie['ID']})?_([[:digit:]]+)\.pdf$/",
			'parentDir' => false,
		],
		'html' => [
			'files' => glob("{$dir}/html*/relations-{$this->powiazanie['ID']}*.html"),
			'label' => 'Raporty HTML<br/>do przeglądania:',
			'button' => 'Pokaż raport HTML',
			'regex' => "/^relations-({$this->powiazanie['ID']})_([[:digit:]]+)\.html$/",
			'parentDir' => true,
		],
		'htmlZip' => [
			'files' => glob("{$dir}/html*.zip"),
			'label' => 'Raporty HTML<br/>do pobrania:',
			'button' => 'Pobież raport HTML',
			'regex' => "/^html(-{$this->powiazanie['ID']})?_([[:digit:]]+)\.zip$/",
			'parentDir' => false,
		],
	];
?>
<div class="container" style="margin-top:20px">
<legend>
Wygenerowany raport nr <?=$this->powiazanie['ID']?>
</legend>
<div class="form-group" style="text-align: center;">
<?php
	array_walk($reports, function ($report) use ($url) {
		echo "<div class=\"col-sm-2\"><h4>{$report['label']}</h4>";
		$hrefs = array_filter(array_map(function ($path) use ($url, $report) {
			if (!is_file($path)) return false;
			$file = basename($path);
			if (preg_match($report['regex'], $file, $matches)) $i = " (" . ltrim($matches[2], '0') . ")";
			else $i = '';
			if ($report['parentDir']) $file = basename(dirname($path)) . "/" . $file;
			return "<a href=\"{$url}/{$file}\" target=\"_blank\" class=\"btn btn-primary\">{$report['button']}{$i}</a>";
		}, $report['files']));
		if ($hrefs) echo implode('<br/><br/>', $hrefs);
		else echo "<h5>Nie znaleziono</h5>";
		echo "</div>\n";
	});
?>
</div>
<div class="form-group">
<div class="col-sm-12">
<br/><br/><a href="<?=$this->REFERER?>" class="btn btn-default">Powrót</a>
</div>
</div>
</div>
<?php
}

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

private function showPowiazaniaListForm() {
	if (!$this->SOURCE['ID']) throw new Exception("Błąd danych");
	elseif ($this->SOURCE['TABLE'] != 'BI_audit_ENERGA_PRACOWNICY') throw new Exception("Błąd danych");

	$query = "select * from `{$this->SOURCE['TABLE']}` where ID = '{$this->SOURCE['ID']}'";
	$pracownik = DB::getPDO()->fetchFirst($query);
	if (!$pracownik) throw new Exception("Błąd danych");
?>
<div class="container" style="margin-top:20px">
<legend>
Lista wygenerowanych powiązań :: <?=htmlspecialchars($pracownik['imiona'] . " " . $pracownik['nazwisko'])?>
<span class="pull-right"># <?=$pracownik['ID']?></span>
</legend>
<div class="form-group">
<div class="col-sm-12">
<h4>Lista wygenerowanych powiązań, w których znajduje się pracownik</h4>
<table class="table table-bordered table-hover table-striped" height=5>
<thead>
  <tr style="text-align:center; background-color:lightgray">
    <td width=1>ID</td>
    <td>Adnotacje</td>
    <td>Minimalna głębokość analizy</td>
    <td>Maksymalna głębokość analizy</td>
    <td>Powiązania tylko do celów</td>
    <td>Status raportu</td>
    <td>Status raportu - informacje</td>
    <td>Indywidualny raport</td>
    <td width=1></td>
  </tr>
</thead>
<tbody>

<?php
	$refPowiazaniaToPracownicy = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', 'BI_audit_ENERGA_PRACOWNICY', true);
	$query = "select `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA`.*
from `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA`
join `{$refPowiazaniaToPracownicy}` on `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA`.`ID` = `{$refPowiazaniaToPracownicy}`.`PRIMARY_KEY`
where `REMOTE_PRIMARY_KEY` = '{$this->SOURCE['ID']}'";
	$result = DB::getPDO()->fetchAll($query);

	if (!$result) echo '<tr><td align="center" colspan="9">Brak znalezionych powiązań</td></tr>';
	else {
		foreach ($result as $row) {
			$query = "select count(*) from `{$refPowiazaniaToPracownicy}` where `PRIMARY_KEY` = '{$row['ID']}'";
			$count = DB::getPDO()->fetchValue($query);
?>
  <tr>
    <td align="right"><?=$row['ID']?></td>
    <td><?=$row['L_APPOITMENT_INFO']?></td>
    <td><?=$row['BI_analiza_minDepth']?></td>
    <td><?=$row['BI_analiza_maxDepth']?></td>
    <td><?=($row['BI_analiza_onlyTargets'] == 'N' ? "Nie" : "Tak")?></td>
    <td><?=$row['FILE_STATUS']?></td>
    <td><?=$row['FILE_STATUS_info']?></td>
    <td><?=($count > 1 ? 'Nie' : 'Tak')?></td>
    <td><?=UI::hButtonPost("Pokaż w tabeli", ['class' => 'btn-info btn-xs', 'action' => "index.php?_route=ViewTableAjax&namespace=default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA&ff_ID={$row['ID']}", 'data' > ['f_ID' => $row['ID']]])?></td>
  </tr>
<?php
		}
	}
?>
</tbody>
</table>
</div>
</div>
<div class="form-group">
<div class="col-sm-12">
<h4>Dodaj nowe zadanie generowania powiazań dla tego pracownika</h4>
</div>
</div>
<form class="form-horizontal" method="post">
    <div class="form-group">
      <label class="col-sm-4 control-label">Minimalna głębokość poszukiwań (liczba rekurencji)</label>
      <div class="col-sm-1">
        <input type="number" class="form-control" name="minDepth" id="minDepth"  data-bind="value:replyNumber" min="1" max="9" value="1" required/>
      </div>
    </div>
    <div class="form-group">
      <label class="col-sm-4 control-label">Maksymalna głębokość poszukiwań (liczba rekurencji)</label>
      <div class="col-sm-1">
        <input type="number" class="form-control" name="maxDepth" id="maxDepth" data-bind="value:replyNumber" min="1" max="9" value="6" required/>
      </div>
    </div>
    <div class="form-group">
      <label class="col-sm-4 control-label">Czy zwracać tylko wyniki ze znalezionymi obiektami docelowymi (z tabeli kontrahentów)?</label>
      <div class="col-sm-1">
        <select name="onlyTargets" class="form-control">
          <option value="Y" selected>Tak</option>
          <option value="N">Nie</option>
        </select>
      </div>
    </div>
    <div class="form-group">
      <label class="col-sm-4 control-label">Adnotacje</label>
      <div class="col-sm-4">
        <input type="text" class="form-control" name="info" value="Indywidualnie dla <?=htmlspecialchars($pracownik['imiona'] . " " . $pracownik['nazwisko'])?>" required/>
      </div>
    </div>
<!--    <div class="form-group">
      <label class="col-sm-4 control-label">
         Odśwież cache
      </label>
      <div class="col-sm-2">
        <select name="reloadCache" class="form-control">
          <option value="No" selected>Nie</option>
<!-#-          <option value="Part">Częściowe</option>-#->
          <option value="Full">Pełne</option>
        </select>
      </div>
    </div>
    <div class="form-group">
      <div class="col-sm-offset-4 col-sm-8">
<!-#-        Częściowe odświeżenie wymagane w przypadku, gdy jedynie dodano nowe rekordy w tabelach pracowników/kontrahentów.<br/>-#->
        Pełne odświeżenie wymagane w przypadku edycji/usunięcia istniejących rekordów w tabelach pracowników/kontrahentów.<br/>
        Uwaga! Jeżeli aktywne są inne zadania, te zadanie zostanie wstrzymane do momentu ich ukończenia.
      </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" onClick="return validate()">Generuj</button>
          <a href="<?=$this->REFERER?>" class="btn btn-default">Powrót</a>
        </div>
      </div>
    </div>
    <input type="hidden" name="REFERER" value="<?=$this->REFERER?>"/>
    <input type="hidden" name="prID[]" value="<?=$pracownik['ID']?>">
  </form>
</div>
<script language="JavaScript">
<!--
function validate() {
  result = (document.getElementById('minDepth').value <= document.getElementById('maxDepth').value);
  if (!result) alert('Wartość minimalnej głębokości analizy nie może być większa od wartości maksymalnej głębokości analizy!');
  return result;
}
-->
</script>
<?php
	}

	private function powiazania() {
		try {
			if (!$this->SOURCE) throw new Exception("Błąd danych");
			elseif (!(isset($this->SOURCE['TABLE']) && isset($this->SOURCE['ID']))) throw new Exception("Błąd danych");
			switch ($this->SOURCE['TABLE']) {
				case "BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA":
					$powiazania = DB::getPDO()->fetchall("select * from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where ID = '{$this->SOURCE['ID']}'");
					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");
					}
					break;
				case "BI_audit_ENERGA_PRACOWNICY":
					$this->showPowiazaniaList();
					break;
				case "BI_audit_KRS":
				case "BI_audit_MSIG":
					$this->importToPracownicySearch($this->SOURCE['ID']);
					break;
				case "BI_audit_KRS_person":
					$this->showKrsForKrsPerson();
					break;
				default: throw new Exception("Błąd wyzwolenia funkcji BiAuditGenerate");
			}
		} 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
		}
	}

	private function importToPracownicy() {
		$action = V::get('action', '', $_POST);
		switch ($action) {
			case "search":
				$this->importToPracownicySearch();
				break;
			default: $this->importToPracownicyForm();
		}
	}

	private $tableDesc = ['BI_audit_KRS' => 'KRS', 'BI_audit_MSIG' => 'MSiG'];

	private function importToPracownicyForm() {
		$TABLE = $this->SOURCE['TABLE'];
		$DESC = $this->tableDesc[$TABLE];
		
?>
<div class="container" style="margin-top:20px">
  <legend>
    Importowanie podmiotów z <?=$DESC?> do tabel kontrahentów i pracowników
  </legend>
  <div class="form-group">
    <div class="col-sm-12">
      <h4>Znajdź podmiot</h4>
    </div>
  </div>
  <form class="form-horizontal" method="post">
    <div class="form-group">
      <label class="col-sm-1 control-label">Nazwa</label>
      <div class="col-sm-4">
        <input type="text" class="form-control" name="nazwa" title="Podaj nazwę podmiotu">
      </div>
    </div>
    <div class="form-group">
      <label class="col-sm-1 control-label">KRS</label>
      <div class="col-sm-2">
	<input type="text" class="form-control" name="krs" pattern="^[0-9]{0,10}$" title="Podaj KRS"/>
      </div>
    </div>
    <div class="form-group">
      <label class="col-sm-1 control-label">NIP</label>
      <div class="col-sm-2">
        <input type="text" class="form-control" name="nip" pattern="^[0-9]{0,10}$" title="Podaj NIP (bez kresek i bez oznaczenia kraju)"/>
      </div>
    </div>
    <div class="form-group">
      <label class="col-sm-1 control-label">Regon</label>
      <div class="col-sm-2">
        <input type="text" class="form-control" name="regon" pattern="^[0-9]{0,9}$" title="Podaj regon"/>
      </div>
    </div>
    <div class="form-group">
      <div class="col-sm-offset-1 col-sm-11">
        <button type="submit" class="btn btn-primary" name="action" value="search">Szukaj</button>
        <a href="<?=$this->REFERER?>" class="btn btn-default">Powrót</a>
      </div>
    </div>
  </form>
</div>
<?php
	}

	private function importToPracownicySearch($krsId = null) {
		$TABLE = $this->SOURCE['TABLE'];
		$DESC = $this->tableDesc[$TABLE];

		$formItems = [
			"nazwa" => 'like',
			"krs" => '=',
			"nip" => '=',
			"regon" => '=',
		];

		try {
			$subaction = V::get('subaction', '', $_POST);
			switch ($subaction) {
				case "listPerson":
					$krsId = V::get('krsId', 0, $_POST, int);
					break;
				case "addPersonToPracownicy":
					$krsId = V::get('krsId', 0, $_POST, int);
					$personId = V::get('personId', [], $_POST);
					if (!$personId) throw new Exception("Błąd formularza");
					$query = "insert into BI_audit_ENERGA_PRACOWNICY (source, imiona, nazwisko, pesel) select 'KRS', imiona, nazwisko, pesel from {$TABLE}_person where ID in (" . implode(", ", $personId) . ")";
					DB::getPDO()->query($query);
					SE_Layout::alert('success', "Pomyślnie zaimportowano " . count($personId) . " pracownik" . ((count($personId) == 1) ? "a" : "ów"));
					break;
				case "addToKontrahenci":
					$krsId = V::get('krsId', 0, $_POST, int);
					if (!$krsId) throw new Exception("Błąd formularza");
					$query = "select count(*) from BI_audit_ENERGA_RUM_KONTRAHENCI kh join {$TABLE} krs on (kh.NIP = krs.nip or kh.REGON = krs.regon or kh.KRS = krs.krs) where krs.ID = '{$krsId}'";
					$kontrahentExists = DB::getPDO()->fetchValue($query);
					if ($kontrahentExists) throw new Exception("Podmiot znajduje się już w tabeli kontrahentów");
					$query = "insert into BI_audit_ENERGA_RUM_KONTRAHENCI (Tytul_dokumentu, Pelna_nazwa_kontrahenta, REGON, NIP, KRS) select 'ZaImportowano z {$DESC}', nazwa, regon, nip, krs from {$TABLE} where ID = ".$krsId." ";
					DB::getPDO()->query($query);
					SE_Layout::alert('success', "Pomyślnie zaimportowano kontrahenta");
					break;
			}

			if ($krsId) {
				$where = ["ID = '{$krsId}'"];
			} else {
				$form = [];
				foreach ($formItems as $item => $type) {
					if ($param = V::get($item, '', $_POST)) {
						if ($type == 'like') $param = "%{$param}%";
						$form[$item] = DB::getPDO()->quote($param);
					}
				}
				if (!$form) throw new Exception("Nie wypełniono żadnego pola wyszukiwania!");

				$where = [];
				foreach ($form as $name => $value) $where[] = "`{$name}` {$formItems[$name]} {$value}";
			}
		} catch (Exception $e) {
			SE_Layout::alert('danger', $e->getMessage());
			$this->importToPracownicyForm();
			return;
		}

		$query = "select * from `{$TABLE}` where " . implode(" and ", $where) . "order by ID limit 1001";
		$result = DB::getPDO()->fetchAll($query);
		if (count($result) == 1001) {
			SE_Layout::alert('danger', 'Znaleziono zbyt wiele wyników. Doprecyzuj parametry wyszukiwania.');
			$this->importToPracownicyForm();
			return;
		} elseif (count($result) == 1) {
			$krsId = $result[0]['ID'];
		}
?>
<div class="container" style="margin-top:20px">
  <form method="post">
    <legend>
      Importowanie podmiotów z <?=$DESC?> do tabel kontrahentów i pracowników
    </legend>
    <div class="form-group">
      <div class="col-sm-12">
        <h4>Znalezione podmioty:</h4>
      </div>
    </div>
    <div class="form-group">
      <div class="col-sm-12">
        <table class="table table-bordered table-hover table-striped" height=5>
          <thead>
            <tr style="text-align:center; background-color:lightgray">
              <td width=1>Lp.</td>
              <td>Nazwa</td>
              <td>Adres</td>
              <td>KRS</td>
              <td>NIP</td>
              <td>Regon</td>
<?php
		if (count($result) > 1) {
?>
              <td width=1>Wybierz</td>
<?php
		}
?>
            </tr>
          </thead>
          <tbody>

<?php
		if (!$result) echo '<tr><td align="center" colspan="6">Nie znaleziono podmiotu</td></tr>';
		else {
			$lp = 1;
			foreach ($result as $row) {
				$adres = $row['A_miejscowosc'];
				if ($row['A_ulica']) $adres .= ", {$row['A_ulica']}";
				if ($row['A_nrDomu']) {
					$adres .= " {$row['A_nrDomu']}";
					if ($row['A_nrLokalu']) $adres .= "/{$row['A_nrLokalu']}";
				}
?>
            <tr>
              <td align="right"><?=$lp++?></td>
              <td><?=htmlspecialchars($row['nazwa'])?></td>
              <td><?=htmlspecialchars($adres)?></td>
              <td><?=$row['krs']?></td>
              <td><?=$row['nip']?></td>
              <td><?=$row['regon']?></td>
<?php
		if (count($result) > 1) {
?>
              <td align="center">
               <input type="radio" name="krsId" value="<?=$row['ID']?>"/>
             </td>
<?php
		}
?>
            </tr>
<?php
			}
		}
?>
          </tbody>
        </table>
      </div>
    </div>
<?php
		if (count($result) > 1) {
?>
    <div class="form-group">
      <div class="col-sm-12">
<?php
			foreach (array_keys($formItems) as $item) {
?>
        <input type="hidden" name="<?=$item?>" value="<?=V::get($item, '', $_POST)?>"/>
<?php
			}
?>
	<input type="hidden" name="back" value="search"/>
        <div class="containter" style="text-align:center">
          <button type="submit" class="btn btn-primary" name="subaction" value="listPerson" onClick="return validateCompany(this)">Wybierz zaznaczony podmiot</button>
          <a href="" class="btn btn-default">Powrót</a>
        </div>
      </div>
    </div>
<?php
		} elseif ($krsId) {
?>
    <div class="form-group">
      <div class="col-sm-12">
        <h4>Znalezione osoby:</h4>
      </div>
    </div>
    <div class="form-group">
      <div class="col-sm-12">
        <table class="table table-bordered table-hover table-striped" height=5>
          <thead>
            <tr style="text-align:center; background-color:lightgray">
              <td width=1>Lp.</td>
              <td>Nazwisko</td>
              <td>Imiona</td>
              <td>Pesel</td>
              <td width=1><input type="checkbox" name="checkAll" onClick="toggleAll(this)"/></td>
            </tr>
          </thead>
          <tbody>

<?php
			$query = "select count(*) from BI_audit_ENERGA_RUM_KONTRAHENCI kh join {$TABLE} krs on (kh.NIP = krs.nip or kh.REGON = krs.regon or kh.KRS = krs.krs) where krs.ID = '{$krsId}'";
			$kontrahentExists = DB::getPDO()->fetchValue($query);

			$refToPerson = BiAuditRefTables::getRefTable($TABLE, "{$TABLE}_person", true);
			$query = "select person.*, pracownicy.ID as pracownicyId from `{$refToPerson}` ref join `{$TABLE}_person` person on ref.REMOTE_PRIMARY_KEY = person.ID left join BI_audit_ENERGA_PRACOWNICY pracownicy on person.pesel = pracownicy.pesel where ref.PRIMARY_KEY = '{$krsId}'";
			$result = DB::getPDO()->fetchAll($query);
			if (!$result) echo '<tr><td align="center" colspan="5">Nie znaleziono osób powiązanych z podmiotem</td></tr>';
			else {
				$lp = 1;
				foreach ($result as $row) {
?>
            <tr<?=($row['pracownicyId'] ? ' style="color:darkgrey"' : '')?>>
              <td align="right"><?=$lp++?></td>
              <td><?=$row['nazwisko']?></td>
              <td><?=$row['imiona']?></td>
              <td><?=$row['pesel']?></td>
              <td><input type="checkbox" name="personId[]" value="<?=$row['ID']?>" onClick="toggle(this)" <?=($row['pracownicyId'] ? "disabled" : "")?>/></td>
            </tr>
<?php
				}
			}
?>
          </tbody>
        </table>
      </div>
    </div>
<!--    <div class="form-group">
      <div class="col-sm-12">
          <label class="col-sm-4 control-label">Opis/źródło</label>
          <div class="col-sm-4">
            <input type="text" class="form-control" name="source" placeholder="KRS"/>
          </div>
      </div>
    </div>-->
    <div class="form-group">
      <div class="col-sm-12" style="text-align: center;">
        <button type="submit" class="btn btn-primary" name="subaction" value="addToKontrahenci"<?=($kontrahentExists ? " disabled" : "")?>>Dodaj firmę do tabeli kontrahentów</button>
        <button type="submit" class="btn btn-primary" name="subaction" value="addPersonToPracownicy" onClick="return validatePerson(this)">Dodaj zaznaczone osoby do tabeli pracowników</button>
        <input type="hidden" name="krsId" value="<?=$krsId?>"/>
        <input type="hidden" name="REFERER" value="<?=$this->REFERER?>"/>
<?php
			if (V::get('back', '', $_POST) == 'search') {
				foreach (array_keys($formItems) as $item) {
?>
        <input type="hidden" name="<?=$item?>" value="<?=V::get($item, '', $_POST)?>"/>
<?php
				}
?>
        <button type="submit" class="btn btn-default" name="action" value="search">Powrót</button>
<?php
			} else {
?>
        <a href="<?=$this->REFERER?>" class="btn btn-default">Powrót</a>
<?php
			}
?>
      </div>
    </div>
<?php
		}
?>
    <input type="hidden" name="action" value="search">
  </form>
</div>
<script language="JavaScript">
<!--
function toggleAll(source) {
  checkboxes = document.getElementsByName('personId[]');
  checked = false;
  for(var i=0, n=checkboxes.length;i<n;i++) {
    if (!checkboxes[i].disabled) {
      checkboxes[i].checked = source.checked;
      checked = true;
    }
  }
  if (source.checked && (!checked)) source.checked = false;
}
function toggle(source) {
  checkboxes = document.getElementsByName('personId[]');
  all = true;
  for(var i=0, n=checkboxes.length;i<n;i++) if(!checkboxes[i].disabled && checkboxes[i].checked == false) all = false;
  source.form['checkAll'].checked = all;
}
function validatePerson(source) {
  checkboxes = document.getElementsByName('personId[]');
  checked = false;
  for(var i=0, n=checkboxes.length;i<n;i++) if(checkboxes[i].checked) checked = true;
  if (!checked) alert('Nie wybrano żadnej osoby!');
  return checked;
}
function validateCompany(source) {
  radios = document.getElementsByName('krsId');
  checked = false;
  for(var i=0, n=radios.length;i<n;i++)  if(radios[i].checked) checked = true;
  if (!checked) alert('Nie wybrano żadnej firmy!');
  return checked;
}
-->
</script>
<?php
	}

	private function showKrsForKrsPerson() {
		$query = "select * from BI_audit_KRS_person where ID = '{$this->SOURCE['ID']}'";
		$result = DB::getPDO()->fetchFirst($query);
?>
<div class="container" style="margin-top:20px">
  <legend>
    Lista podmiotów, w których występuje <?="{$result['nazwisko']} {$result['imiona']}" . ($result['pesel'] ? " (PESEL: {$result['pesel']})" : "")?>
  </legend>
  <div class="form-group">
    <div class="col-sm-12">
      <table class="table table-bordered table-hover table-striped" height=5>
        <thead>
          <tr style="text-align:center; background-color:lightgray">
            <td width=1>Lp.</td>
            <td>Nazwa</td>
            <td>Adres</td>
            <td>KRS</td>
            <td>NIP</td>
            <td>Regon</td>
            <td width=1></td>
          </tr>
        </thead>
        <tbody>
<?php
		$refKrsToKrsPerson = BiAuditRefTables::getRefTable('BI_audit_KRS', 'BI_audit_KRS_person', true);
		$query = "select krs.* from `{$refKrsToKrsPerson}` ref join BI_audit_KRS krs on ref.PRIMARY_KEY = krs.ID where ref.REMOTE_PRIMARY_KEY = '{$this->SOURCE['ID']}'";
		$result = DB::getPDO()->fetchAll($query);

                if (!$result) echo '<tr><td align="center" colspan="5">Nie znaleziono powiązanych podmiotów</td></tr>';
                else {
			$lp = 1;
                        foreach ($result as $row) {
				$adres = $row['A_miejscowosc'];
				if ($row['A_ulica']) $adres .= ", {$row['A_ulica']}";
				if ($row['A_nrDomu']) {
					$adres .= " {$row['A_nrDomu']}";
					if ($row['A_nrLokalu']) $adres .= "/{$row['A_nrLokalu']}";
				}
?>
          <tr>
            <td align="right"><?=$lp++?></td>
            <td><?=htmlspecialchars($row['nazwa'])?></td>
            <td><?=htmlspecialchars($adres)?></td>
            <td><?=$row['krs']?></td>
            <td><?=$row['nip']?></td>
            <td><?=$row['regon']?></td>
            <td><?=UI::hButtonPost("Pokaż w tabeli", ['class' => 'btn-info btn-xs', 'action' => "index.php?_route=ViewTableAjax&namespace=default_db/BI_audit_KRS/BI_audit_KRS&ff_ID={$row['ID']}", 'data' > ['f_ID' => $row['ID']]])?></td>
          </tr>
<?php
                        }
                }
?>
        </tbody>
      </table>
    </div>
  </div>
  <div class="form-group">
    <div class="col-sm-12">
      <div class="containter" style="text-align:center">
        <a href="<?=$_SERVER['HTTP_REFERER']?>" class="btn btn-default">Powrót</a>
      </div>
    </div>
  </div>
</div>
<?php
	}

	private function clearTable_do() {
		try {
			$wynik = V::get('wynik', 0, $_POST, 'int');
			$ctrl = @gzuncompress(@base64_decode(V::get('_BiAuditGenerate_ctrl', '', $_SESSION)));
			unset($_SESSION['_BiAuditGenerate_ctrl']);
			$deleteFiles = (V::get('deleteFiles', '', $_POST) === 'on');
			$deletedDirs = 0; $deletedFiles = 0;
			if (!($ctrl)) throw new Exception('Błąd parametru');
			if (!($wynik == $ctrl)) {
				SE_Layout::alert('warning', 'Podano błędny wynik działania');
				$this->clearTable_form();
				return;
			}

			$refs = []; $backRefs = []; $tables = []; $queries = [];
			switch ($this->SOURCE['TABLE']) {
				case "BI_audit_ENERGA_PRACOWNICY":
					$refs[] = 'BI_audit_ENERGA_PRACOWNICY_adresy';
					$tables[] = 'BI_audit_ENERGA_PRACOWNICY_adresy';
					$queries[] = "update `BI_audit_ENERGA_PRACOWNICY_import` set `A_STATUS` = 'WAITING'";
					break;
				case "BI_audit_ENERGA_PRACOWNICY_import":
					break;
				case "BI_audit_ENERGA_RUM_KONTRAHENCI":
					$backRefs[] = 'BI_audit_ENERGA_RUM_UMOWY';
					$backRefs[] = 'BI_audit_ENERGA_FAKTURY';
					$queries[] = "update `BI_audit_ENERGA_RUM_UMOWY` set `A_STATUS` = 'WAITING'";
					break;
				case "BI_audit_ENERGA_RUM_UMOWY":
					$refs[] = 'BI_audit_ENERGA_RUM_KONTRAHENCI';
					break;
				case "BI_audit_ENERGA_FAKTURY":
					$refs[] = 'BI_audit_ENERGA_RUM_KONTRAHENCI';
					break;
				case "BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA":
					$query = "select ID from `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA`";
					$result = DB::getPDO()->fetchAll($query);
					foreach ($result as $row) {
						if ($deleteFiles) {
							$dir = self::getDirectory('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', $row['ID']);
							if (!file_exists($dir)) continue;
							$deleted = V::deleteWholeDirectory($dir, true, false);
							$deletedDirs += count($deleted['dirs']);
							$deletedFiles += count($deleted['files']);
						}
						self::deleteResultsFromDB($row['ID']);
						self::truncatePowiazaniaFromDB($row['ID']);
					}
					break;
				default: throw new Exception('Błąd parametru');
			}

			$tables[] = $this->SOURCE['TABLE'];
			foreach ($refs as $ref) $tables[] = BiAuditRefTables::getRefTable($this->SOURCE['TABLE'], $ref, true);
			foreach ($backRefs as $backRef) $tables[] = BiAuditRefTables::getRefTable($backRef, $this->SOURCE['TABLE'], true);
			foreach ($tables as $table) $queries[] = "delete from `{$table}`";
			foreach ($queries as $query) DB::getPDO()->query($query);
?>
<div style="text-align: center;">
  <h2>Pomyślnie wyczyszczono tabelę <?=$this->clearTable_tables[$this->SOURCE['TABLE']]?>.</h2>
<?php
			if ($deleteFiles && ($deletedDirs || $deletedFiles)):
?>
  <h4>Ponadto usunięto <?=$deletedDirs?> katalogów oraz <?=$deletedFiles?> plików.</h4>
<?php
			endif;
?>
  <br/><a href="<?=$this->REFERER?>" class="btn btn-primary" style="width: 80px;">Powrót</a>
</div>
<?php
		} catch (Exception $e) {
			SE_Layout::alert('danger', $e->getMessage());
		}
	}

	private function clearTable_form() {
		if (!isset($this->clearTable_tables[$this->SOURCE['TABLE']])) SE_Layout::alert('danger', 'Błąd parametru');

		$a = rand(10, 50);
		$b = rand(10, 50);
		if ($a == $b) $op = 1;
		else $op = rand(0, 1);

		if ($op) {
			$suma = $a + $b;
			$string = "{$a} + {$b}";
		} else {
			if ($a < $b) {
				$suma = $b - $a;
				$string = "{$b} - {$a}";
			} else {
				$suma = $a - $b;
				$string = "{$a} - {$b}";
			}
		}
		$string .= " = ";
		$_SESSION['_BiAuditGenerate_ctrl'] = base64_encode(gzcompress($suma));

		$im = imagecreate((strlen($string) - 1) * 10, 24);
		$bg = imagecolorallocate($im, 255, 255, 255);
		$textcolor = imagecolorallocate($im, 0, 0, 0);
		imagestring($im, 5, 0, 0, $string, $textcolor);
		ob_start();
		imagepng($im);
		$image = ob_get_contents();
		ob_end_clean();

/*
		$image = new Imagick();
		$draw = new ImagickDraw();
		$pixel = new ImagickPixel('white');
		$image->newImage(200, 24, $pixel);
//		$image->setFont("schema/WPS_Functions/mapserver_gml_to_png/map/fonts/Arial_Bold.ttf");
//		$image->newPseudoImage(100, 100, "label:\"{$a} {$op} {$b}\"");
//		$image->newPseudoImage(100, 100, "caption:Hello");
		$image->setImageFormat('png');
		$draw->setFillColor('black');
//		$draw->setFont('Arial');
		$draw->setFontSize(24);
		$image->annotateImage($draw, 0, 24, 0, $s);
		$image->trimImage(0);
*/

		$imageBase64 = base64_encode($image);

//echo $imageBase64;

		
?>
<div style="text-align: center;">
  <h2>Czy jesteś pewien, że chcesz usunąć<br/><b>wszystkie dane</b> z tabeli <?=$this->clearTable_tables[$this->SOURCE['TABLE']]?>?</h2><br/>
  <h3>Podaj wynik działania:</h3>
  <form method="post">
    <img src="data:image/png;base64,<?=$imageBase64?>" height="36px"/><input type="text" name="wynik" style="font-size: 24px; width: 50px;" pattern="^[0-9]{1,3}$" oninvalid="setCustomValidity('Nieprawidłowy format')" maxlength="3" autofocus required autocomplete="off"/><br/><br/>
<?php
		if ($this->SOURCE['TABLE'] == 'BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA'):
?>
    <h4><input type="checkbox" name="deleteFiles"/>&nbsp;&nbsp;Usuń również wszystkie pliki</h4><br/>
<?php
		endif;
?>
    <button type="submit" class="btn btn-primary" name="action" value="doClearTable" style="width: 80px;">Tak</button>
    <input type="hidden" name="REFERER" value="<?=$this->REFERER?>">
    <a href="<?=$this->REFERER?>" class="btn btn-default" style="width: 80px;">Anuluj</a>
  </form>
</div>
<?php
	}

	private function clearTable() {
		$this->clearTable_tables = [
			'BI_audit_ENERGA_PRACOWNICY' => 'pracowników',
			'BI_audit_ENERGA_PRACOWNICY_import' => 'pracowników (import)',
			'BI_audit_ENERGA_RUM_KONTRAHENCI' => 'kontrahentów',
			'BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA' => 'raportów',
			'BI_audit_ENERGA_RUM_UMOWY' => 'umów',
			'BI_audit_ENERGA_FAKTURY' => 'faktur',
		];
		$query = "select count(*) from `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA` where `FILE_STATUS` = 'IN_PROGRESS'";
		if (DB::getPDO()->fetchValue($query)) {
?>
<div style="text-align: center;">
  <h2>Nie można usunąć żadnych danych<br/>(wykryto uruchomione zadania)</h2><br/>
  <a href="<?=$this->REFERER?>" class="btn btn-primary" style="width: 80px;">Powrót</a>
</div>
<?php
			return;
		}

		$action = V::get('action', '', $_POST);
		switch ($action) {
			case "doClearTable":
				$this->clearTable_do();
				break;
			default: $this->clearTable_form();
		}
	}

	private function benford_getDetails($a = null, $b = null) {
                try {
			if (!($benford = V::get('benford', '', $_SESSION))) throw new Exception("Nieznany błąd wewnętrzny #1");
			if (isset($benford[$this->SOURCE['TABLE']]['details'][$a][$b])) throw new Exception(@gzuncompress($benford[$this->SOURCE['TABLE']]['details'][$a][$b]));
			if (!($benford = @unserialize(@gzuncompress($benford[$this->SOURCE['TABLE']]['data'])))) throw new Exception("Nieznany błąd wewnętrzny #2");
			if (!($groupField = V::get('_benfordGroupField', '', $_GET))) throw new Exception("Brak konfiguracji kolumny grupującej");
			if ($a === null) throw new Exception("Nieznany błąd wewnętrzny #3");
			if ($b === null) {
				if (!isset($benford['firstStep'][$a]['keys'])) throw new Exception("Nieznany błąd wewnętrzny #4");
				$keys = $benford['firstStep'][$a]['keys'];
			} else {
				if (!isset($benford['secondStep'][$a][$b]['keys'])) throw new Exception("Nieznany błąd wewnętrzny #5");
				$keys = $benford['secondStep'][$a][$b]['keys'];
			}

			$tempTable = uniqid('_temp_benford_ids_');
			DB::getPDO()->query("create temporary table `{$tempTable}` (`ID` int(11) NOT NULL, UNIQUE KEY `ID` (`ID`))");
			foreach (array_chunk($keys, 100) as $ids) {
                       	        DB::getPDO()->query("insert into `{$tempTable}` values (" . implode('),(', $ids) . ")");
			}
			$query = "select `table`.`{$groupField}` as `field`, count(*) as `count` from `{$this->SOURCE['TABLE']}` `table` join `{$tempTable}` `temp` on `table`.`ID` = `temp`.`ID` where coalesce(`table`.`{$groupField}`,'') != '' group by `table`.`{$groupField}` having `count` > 1 order by `count` desc limit 10";
			try {
				$result = DB::getPDO()->fetchAll($query);
			} catch (Exception $e) {
				throw new Exception("Nieznany błąd, prawdopodobnie błędna konfiguracja kolumny grupującej");
			}
			$showDetails = "<h4>Najczęściej występujące wartości kolumny {$groupField}</h4>";
			if ($result) {
				$showDetails .= "<table class=\"table table-bordered table-hover table-striped table-condensed\"><thead><tr style=\"font-weight:bold;\"><td>Wartość</td><td>Liczba wystąpień</td><td>Procent występowania</td></tr></thead><tbody>";
				$showDetails .= implode('', array_map(function($v) use ($keys) {
					return "<tr><td>{$v['field']}</td><td>{$v['count']}</td><td>" . number_format(round($v['count'] / count($keys) * 100, 3), 3, ',', '') . "</td></tr>";
				}, $result));
				$showDetails .= "</tbody></table>";
			} else {
				$showDetails .= "<h5>Żadna wartość nie występuje wiele razy</h5>";
			}
		} catch (Exception $e) {
			$showDetails = $e->getMessage();
		}
		$_SESSION['benford'][$this->SOURCE['TABLE']]['details'][$a][$b] = gzcompress($showDetails);
		$showDetails .= '<button type="submit" class="btn btn-primary btn-sm" onClick="hideDetails()">Zamknij</button>';
		return $showDetails;
	}

	private function benford_form($edit = false) {
		if (!($valueField = V::get('_benfordValueField', '', $_GET))) throw new Exception ("Błąd konfiguracji UrlAction - brak parametru _benfordField (DANE )");
		if (!($namespace = V::get('_fromNamespace', '', $_GET))) throw new Exception("Błąd formularza");

		Lib::loadClass('FeatureAttrSelected');
		$count = FeatureAttrSelected::getTotalSelected($namespace);
		$selectedTable = FeatureAttrSelected::getAttributeTableName($namespace, User::getID());
		$query = "select `t`.`ID`, `{$valueField}` from `{$this->SOURCE['TABLE']}` `t` join `{$selectedTable}` `s` on `t`.`ID` = `s`.`primaryKey` where `t`.`{$valueField}` != 0 order by `s`.`primaryKey`";
		$values = [];
		try {
			$result = DB::getPDO()->fetchAll($query);
			foreach ($result as $row) $values[$row['ID']] = $row[$valueField];
		} catch (Exception $e) {
			throw new Exception("Błąd zapytania SQL ({$e->getMessage()})");
		}
		if (!$values) throw new Exception("Nie wybrano żadnych obiektów lub wszystkie wybrane obiekty mają zerową wartość");

		Lib::loadClass('Benford');
		try {
			$benfordObj = new Benford($values);
			$benford = $benfordObj->getAllResult();
			$_SESSION['benford'][$this->SOURCE['TABLE']] = ['data' => gzcompress(serialize($benford))];
		} catch (Exception $e) {
			die($e->getMessage());
		}
?>
<div class="container" style="margin-top:20px">
  <form method="post">
    <input type="hidden" name="count" value="<?=count($values)?>">
    <legend>
      Analiza rozkładu Benford'a
      <span class="pull-right">Tabela: <?=$this->SOURCE['TABLE']?></span>
    </legend>
    <div class="form-group">
      <div class="col-sm-12">
        <h5>Analiza na podstawie kolumny <?=$valueField?>. Liczba znalezionych obiektów z niezerową wartością: <?=count($values)?> (wybrano obiektów: <?=$count?>)</h5>
      </div>
    </div>
<?php if($edit):?>
    <div class="form-group">
      <div class="col-sm-12">
        <center>
          <h3>Generowanie raportu analizy rozkładu Benford'a</h3>
          <table>
            <tr>
              <td>
                <select name="step" class="form-control" onChange="toggleStep(this)">
                  <option value="first" selected>Pierwszy stopień analizy</option>
                  <option value="second">Drugi stopień analizy</option>
                </select>
              </td>
              <td>&nbsp;&nbsp;</td>
              <td><input type="text" class="form-control" name="L_APPOITMENT_INFO" title="Tytuł raportu" placeholder="Tytuł raportu" required/ size="50"></td>
              <td>&nbsp;&nbsp;</td>
              <td><button type="submit" class="btn btn-primary" name="action" value="generate" style="width: 80px;">Generuj</button></td>
            </tr>
          </table>
        </center>
        <br/><br/>
      </div>
    </div>
<?php endif;?>
    <div class="form-group" id="benford">
      <div class="col-sm-offset-2 col-sm-8">
        <table class="table table-bordered table-hover table-striped table-condensed" id="benford">
          <thead style="text-align:center; font-weight:bold; background-color:lightgray">
            <tr>
              <td style="vertical-align:middle;" rowspan="2">Pierwsze cyfry</td>
              <td style="vertical-align:middle;" rowspan="2">Stopień analizy</td>
              <td style="vertical-align:middle;" rowspan="2">Liczba wystąpień</td>
              <td style="vertical-align:middle;" rowspan="2">Procent występowania</td>
              <td colspan="3">Rozkład Benford'a</td>
              <td style="vertical-align:middle;" rowspan="2">Szczegóły</td>
<?php if($edit):?>
              <td style="vertical-align:middle;" rowspan="2">Załącz obiekty<br/>do&nbsp;raportu</td>
<?php endif;?>
            </tr>
            <tr>
              <td nowrap>Norma (%)</td>
              <td nowrap>Odchylenie od&nbsp;normy (%)</td>
              <td>Zgodność</td>
            </tr>
          </thead>
          <tbody>
<?php
	$number_format = function($n) {
		return number_format($n, 3, ',', '');
	};

	for ($ka = 1; $ka < 10; $ka++) {
		if (!isset($benford['firstStep'][$ka])) $benford['firstStep'][$ka] = ['count' => 0, 'value' => 0, 'correct' => true];
		$va = $benford['firstStep'][$ka];
		$odchylenie = round(($va['value'] / Benford::benford($ka) - 1) * 100, 3);
		if ($odchylenie > 0) $odchylenie = "+" . $number_format($odchylenie);
		else $odchylenie = $number_format($odchylenie);
?>
            <tr style="text-align:center; font-weight:bold;<?=$va['correct'] ? '' : ' cursor:pointer;'?>"<?=$va['correct'] ? '' : ' onClick="toggleSecondStep(' . $ka . ');"'?>>
              <td style="text-align:left" class="step1-<?=$va['correct'] ? 'green' : 'red'?>"><?=$ka?></td>
              <td class="step1-<?=$va['correct'] ? 'green' : 'red'?>">I</td>
              <td style="text-align:right" class="step1-<?=$va['correct'] ? 'green' : 'red'?>"><?=$va['count']?></td>
              <td style="text-align:right" class="step1-<?=$va['correct'] ? 'green' : 'red'?>"><?=$number_format(round($va['value'] * 100, 3))?></td>
              <td style="text-align:right" class="step1-<?=$va['correct'] ? 'green' : 'red'?>"><?=$number_format(round(Benford::benford($ka) * 100, 3))?></td>
              <td style="text-align:right" class="step1-<?=$va['correct'] ? 'green' : 'red'?>"><?=$odchylenie?></td>
              <td <?=$va['correct'] ? 'class="step1-green">Tak' : 'class="step1-red" name="firstStepDesc" data-ka="' . $ka . '" nowrap>Nie (rozwiń)'?></td>
              <td<?=$va['correct'] ? '>' : ' onClick="showDetails(' . $ka . ', 0)"><span class="glyphicon glyphicon-search"></span>'?></td>
<?php if($edit):?>
              <td<?=$va['correct'] ? '' : ' onClick="_click(\'checkFirstStep_' . $ka . '\')"'?>><input type="checkbox" <?=$va['correct'] ? 'disabled' : 'checked id="checkFirstStep_' . $ka . '" name="checkFirstStep[]" value="' . $ka . '" onClick="toggleCheckFirst(this)"'?>></td>
<?php endif;?>
            </tr>
<?php
		if (!$va['correct']) {
			for ($kb = 0; $kb < 10; $kb++) {
				if (!isset($benford['secondStep'][$ka][$kb])) $benford['secondStep'][$ka][$kb] = ['count' => 0, 'value' => 0, 'correct' => true];
				$vb = $benford['secondStep'][$ka][$kb];
				$odchylenie = round(($vb['value'] / Benford::benford($ka * 10 + $kb) - 1) * 100, 3);
				if ($odchylenie > 0) $odchylenie = "+" . $number_format($odchylenie);
				else $odchylenie = $number_format($odchylenie);
?>
            <tr style="text-align:center;" name="secondStep" data-ka="<?=$ka?>" hidden>
              <td style="text-align:left" class="step2-<?=$vb['correct'] ? 'green' : 'red'?>"><?=$ka . $kb?></td>
              <td class="step2-<?=$vb['correct'] ? 'green' : 'red'?>">II</td>
              <td style="text-align:right" class="step2-<?=$vb['correct'] ? 'green' : 'red'?>"><?=$vb['count']?></td>
              <td style="text-align:right" class="step2-<?=$vb['correct'] ? 'green' : 'red'?>"><?=$number_format(round($vb['value'] * 100, 3))?></td>
              <td style="text-align:right" class="step2-<?=$vb['correct'] ? 'green' : 'red'?>"><?=$number_format(round(Benford::benford($ka * 10 + $kb) * 100, 3))?></td>
              <td style="text-align:right" class="step2-<?=$vb['correct'] ? 'green' : 'red'?>"><?=$odchylenie?></td>
              <td class="step2-<?=$vb['correct'] ? 'green' : 'red'?>"><?=$vb['correct'] ? 'Tak' : 'Nie'?></td>
              <td<?=$vb['correct'] ? '>' : ' style="cursor:pointer;" onClick="showDetails(' . "{$ka}, {$kb}" . ')"><span class="glyphicon glyphicon-search"></span>'?></td>
<?php if($edit):?>
              <td<?=$vb['correct'] ? '' : ' style="cursor:pointer;" onClick="_click(\'checkSecondStep_' . $ka . $kb . '\')"'?>><input type="checkbox"<?=$vb['correct'] ?: ' checked id="checkSecondStep_' . $ka . $kb . '" name="checkSecondStep[]" value="' . $ka . $kb . '" onClick="toggleCheckSecond(this)"'?> disabled></td>
<?php endif;?>
            </tr>
<?php
			}
		}
	}
?>
          </tbody>
        </table>
      </div>
    </div>
  </form>
</div>
<div id="details" onClick="hideDetails();">
  <div id="detailsMsg" onClick="event.stopPropagation();"></div>
</div>
<style type="text/css">
<!--
#benford .step1-green,#benford .step1-red {font-weight:bold;}
#benford .step1-green,#benford .step2-green {color:#008800;}
#benford .step1-red,#benford .step2-red {color:#ff0000;}
#benford table { -webkit-touch-callout: none; -webkit-user-select: none; -khtml-user-select: none; -moz-user-select: none; -ms-user-select: none; user-select: none;}
td {font-size: 12px;}
#benford ::selection {background: transparent;}
#details {display:none; position: fixed; left: 0; top: 0; width: 100%; height: 100%; text-align: center; z-index: 1000; background-color: rgba(0,0,0,0.5);}
#details div {width: 500px; height: auto; margin: 100px auto; background: #fff; padding: 10px; text-align: center; overflow: hidden;}
-->
</style>
<script language="JavaScript">
<!--
function showDetails(ka, kb) {
  event.stopPropagation();
  document.getElementById('detailsMsg').innerHTML = "<h4>Proszę czekać...</h4>";
  document.getElementById('details').style.display = 'block';
  var post = "_noMenu=1&action=getDetails&_ka=" + ka;
  if (kb) post += "&_kb=" + kb;
  var xhttp = new XMLHttpRequest();
  xhttp.open("POST", '', true);
  xhttp.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
  xhttp.onreadystatechange = function() {
    if (this.readyState == 4 && this.status == 200) {
      document.getElementById('detailsMsg').innerHTML = this.responseText;
    }
  }
  xhttp.send(post);
}
function hideDetails() {
  document.getElementById('details').style.display = 'none';
}
function _click(id) {
  event.stopPropagation();
  document.getElementById(id).click();
}
function toggleSecondStep(ka) {
  var rows = document.getElementsByName('secondStep');
  var hidden = false;
  for(var i = 0, n = rows.length; i < n; i++) {
    if (rows[i].dataset.ka == ka) {
      rows[i].hidden = !rows[i].hidden;
      hidden = rows[i].hidden;
    }
  }
  var descs = document.getElementsByName('firstStepDesc');
  for(var i = 0, n = descs.length; i < n; i++) {
    if (descs[i].dataset.ka == ka) {
      descs[i].innerHTML = (hidden ? 'Nie (rozwiń)' : 'Nie (zwiń)');
    }
  }
}
function toggleStep(option) {
  if (option.value == 'first') first = true;
  else if (option.value == 'second') first = false;
  else {
    alert('Wystąpił nieznany błąd formularza');
    return;
  }
  var checks = document.getElementsByName('checkSecondStep[]');
  var all = [];
  for(var i = 0, n = checks.length; i < n; i++) {
    checks[i].disabled = first;
    ka = checks[i].value / 10 | 0;
    if (all[ka] === 'undefined') all[ka] = checks[i].checked;
    else if (checks[i].checked) all[ka] = true;
  }
  var checks = document.getElementsByName('checkFirstStep[]');
  for(var i = 0, n = checks.length; i < n; i++) {
    checks[i].disabled = !(first);
    if (checks[i].checked != all[checks[i].value]) {
      checks[i].checked = all[checks[i].value];
      toggleCheckFirst(checks[i]);
    }
  }
  var descs = document.getElementsByName('firstStepDesc');
  for(var i = 0, n = descs.length; i < n; i++) {
    descs[i].innerHTML = (first ? 'Nie (rozwiń)' : 'Nie (zwiń)');
  }
  var rows = document.getElementsByName('secondStep');
  for(var i = 0, n = rows.length; i < n; i++) {
    rows[i].hidden = first;
  }
}
function toggleCheckFirst(check) {
  event.stopPropagation();
  var checks = document.getElementsByName('checkSecondStep[]');
  for(var i = 0, n = checks.length; i < n; i++) {
    if ((checks[i].value / 10 | 0) == check.value) checks[i].checked = check.checked;
  }
}
function toggleCheckSecond(check) {
  event.stopPropagation();
  var checks = document.getElementsByName('checkSecondStep[]');
  var all = true;
  var ka = check.value / 10 | 0;
  for(var i = 0, n = checks.length; i < n; i++) {
    if ((checks[i].value / 10 | 0) == ka) {
      if (!checks[i].checked) {
        all = false;
        break;
      }
    }
  }
  var checks = document.getElementsByName('checkFirstStep[]');
  for(var i = 0, n = checks.length; i < n; i++) {
    if (checks[i].value == ka) {
      checks[i].checked = all;
      break;
    }
  }
}
-->
</script>
<?php
	}

	private function benford_generate() {
		$benford = @unserialize(@gzuncompress(V::get('benford', '', $_SESSION)[$this->SOURCE['TABLE']]['data']));
		unset($_SESSION['benford'][$this->SOURCE['TABLE']]);
		if (!$benford) {
			$this->benford_form(true);
			return;
		}

		$root = 'RelatedFeatureRoot';
		$namespace = 'BI_audit_BENFORD';
		$array = [];
		$array['@attributes'] = ['xmlns:system_cache__dita' => 'http://biuro.biall-net.pl/xmlschema_procesy5/default_db_xml_cache/dita.xsd'];
		$array[$namespace] = [
			'@attributes' => ['fid' => "{$namespace}.1"],
			'ID' => 1,
			'L_APPOITMENT_INFO' => V::get('L_APPOITMENT_INFO', '', $_POST),
			'step' => V::get('step', '', $_POST),
			'table' => $this->SOURCE['TABLE'],
			'count' => V::get('count', '', $_POST),
		];

		$firstStep = [];
		for ($ka = 1; $ka <= 9; $ka++) {
			if (!isset($benford['firstStep'][$ka])) $va = ['count' => 0, 'value' => 0, 'correct' => true];
			else $va = $benford['firstStep'][$ka];
			$_firstStep = [
				'@attributes' => ['leadingNumber' => $ka],
				'count' => $va['count'],
				'value' => $va['value'],
				'correct' => $va['correct'] ? 'true' : 'false'
			];
			if ($array[$namespace]['step'] == 'second') {
				$secondStep = [];
				for ($kb = 0; $kb <= 9; $kb++) {
					if (!isset($benford['secondStep'][$ka][$kb])) $vb = ['count' => 0, 'value' => 0, 'correct' => true];
					else $vb = $benford['secondStep'][$ka][$kb];
					$_secondStep = [
						'@attributes' => ['leadingNumber' => $ka.$kb],
						'count' => $vb['count'],
						'value' => $vb['value'],
						'correct' => $vb['correct'] ? 'true' : 'false'
					];
					if (!$vb['correct']) {
						$objects = [];
						foreach ($benford['secondStep'][$ka][$kb]['keys'] as $id) {
							$object = DB::getPDO()->fetchFirst("select * from `{$this->SOURCE['TABLE']}` where `ID` = " . DB::getPDO()->quote($id));
							if (!$object) continue;
							$objects[] = array_merge(['@attributes' => ['fid' => "{$this->SOURCE['TABLE']}.{$id}"]], $object);
						}
						$_secondStep[$this->SOURCE['TABLE']][] = $objects;
					}
					$secondStep[] = $_secondStep;
				}
				$_firstStep['secondStep'] = $secondStep;
			} elseif (!$va['correct']) {
				$objects = [];
				foreach ($benford['firstStep'][$ka]['keys'] as $id) {
					$object = DB::getPDO()->fetchFirst("select * from `{$this->SOURCE['TABLE']}` where `ID` = " . DB::getPDO()->quote($id));
					if (!$object) continue;
					$objects[] = array_merge(['@attributes' => ['fid' => "{$this->SOURCE['TABLE']}.{$id}"]], $object);
				}
				$_firstStep[$this->SOURCE['TABLE']][] = $objects;
			}
			$firstStep[] = $_firstStep;
		}
		$array[$namespace]['firstStep'] = $firstStep;

		$xml = V::arrayToXML($array, true, $root);
		file_put_contents("/tmp/benford_{$array[$namespace]['step']}Step.xml", $xml);
?>
<pre><?//=htmlspecialchars($xml)?></pre>
<br><br>
<div style="text-align:center">
  <a href="<?=$this->REFERER?>" class="btn btn-default" style="width: 80px;">Powrót</a>
</div>
<?php
	}

	private function benford() {
		try {
			$action = V::get('action', '', $_POST);
			switch ($action) {
				case "generate":
					$this->benford_generate();
					break;
				case "getDetails":
					$ka = V::get('_ka', null, $_POST, 'int');
					$kb = V::get('_kb', null, $_POST, 'int');
					echo $this->benford_getDetails($ka, $kb);
					break;
				default:
					$this->benford_form(true);
			}
		} catch (Exception $e) {
			SE_Layout::alert('danger', $e->getMessage());
		}
	}

	public function defaultAction() {
		$showMenu = !(V::get('_noMenu', false, $_POST));
		if ($showMenu) {
			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 (($ID = V::get('ID_BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', 0, $_GET, 'int')) > 0) $this->SOURCE['TABLE'] = 'BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA';
		elseif (($ID = V::get('ID_BI_audit_ENERGA_PRACOWNICY', 0, $_GET, 'int')) > 0) $this->SOURCE['TABLE'] = 'BI_audit_ENERGA_PRACOWNICY';
		elseif (($ID = V::get('ID_BI_audit_KRS', 0, $_GET, 'int')) > 0) $this->SOURCE['TABLE'] = 'BI_audit_KRS';
		elseif (($ID = V::get('ID_BI_audit_MSIG', 0, $_GET, 'int')) > 0) $this->SOURCE['TABLE'] = 'BI_audit_MSIG';
		elseif (($ID = V::get('ID_BI_audit_KRS_person', 0, $_GET, 'int')) > 0) $this->SOURCE['TABLE'] = 'BI_audit_KRS_person';
		if ($this->SOURCE) {
			$this->SOURCE['ID'] = $ID;
			$this->powiazania();
		}
		elseif ($TABLE = V::get('_fromNamespace', '', $_GET)) {
			$this->SOURCE['TABLE'] = ACL::getAclByTypeName($TABLE)->getRootTableName();
			$_subUrlAction = V::get('_subUrlAction', '', $_GET);
			switch ($this->SOURCE['TABLE']) {
				case "BI_audit_ENERGA_PRACOWNICY":
					switch ($_subUrlAction) {
						case "clearTable":
							$this->clearTable();
							break;
						case "importFromKRS":
							$this->SOURCE['TABLE'] = 'BI_audit_KRS';
							$this->importToPracownicy();
							break;
						default: SE_Layout::alert('danger', 'Błąd parametru #21');
					}
					break;
				case "BI_audit_ENERGA_PRACOWNICY_import":
					switch ($_subUrlAction) {
						case "clearTable":
							$this->clearTable();
							break;
						default: SE_Layout::alert('danger', 'Błąd parametru #22');
					}
					break;
				case "BI_audit_ENERGA_RUM_KONTRAHENCI":
					switch ($_subUrlAction) {
						case "clearTable":
							$this->clearTable();
							break;
						default: SE_Layout::alert('danger', 'Błąd parametru #23');
					}
					break;
				case "BI_audit_ENERGA_RUM_UMOWY":
					switch ($_subUrlAction) {
						case "clearTable":
							$this->clearTable();
							break;
						case "benford":
							$this->benford();
							break;
						default: SE_Layout::alert('danger', 'Błąd parametru #24');
					}
					break;
				case "BI_audit_ENERGA_FAKTURY":
					switch ($_subUrlAction) {
						case "clearTable":
							$this->clearTable();
							break;
						case "benford":
							$this->benford();
							break;
						default: SE_Layout::alert('danger', 'Błąd parametru #25');
					}
					break;
				case "BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA":
					switch ($_subUrlAction) {
						case "clearTable":
							$this->clearTable();
							break;
						default: SE_Layout::alert('danger', 'Błąd parametru #25');
					}
					break;
				default:
					switch ($_subUrlAction) {
						case "benford":
							$this->benford();
							break;
						default: SE_Layout::alert('danger', 'Błąd parametru #1');
					}
			}
		}
		else SE_Layout::alert('danger', 'Błąd parametru #0');
		if ($showMenu) {
			echo "<br/><br/>";
			SE_Layout::dol();
		}
	}

	private static function truncatePowiazaniaFromDB($ID) {
		$refTables = BiAuditRefTables::getRefTables('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA');
		if (!$refTables) return null;
		$refPowiazaniaToPowiazaniaRow = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', 'BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row', true);
		$refTables = array_diff($refTables, [$refPowiazaniaToPowiazaniaRow]);

		$query = "delete from `" . implode("`, `", $refTables) . "` using `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA`";
		foreach ($refTables as $refTable) $query .= " left join `{$refTable}` on `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA`.`ID` = `{$refTable}`.`PRIMARY_KEY`";

		$query .= " where `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA`.`ID` = '{$ID}'";
		DB::getPDO()->query($query);
	}

	private static function deleteResultsFromDB($ID) {
		$refPowiazaniaToPowiazaniaRow = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', 'BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row', true);
		$refPowiazaniaRowToPowiazaniaRowObject = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row', 'BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object', true);
		$refTables = BiAuditRefTables::getRefTables('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object');

		$sqlDeleteFrom = "delete from
`{$refPowiazaniaToPowiazaniaRow}`,
`BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row`,
`{$refPowiazaniaRowToPowiazaniaRowObject}`,
`BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object`";
		$sqlUsing = "
using `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA`
join `{$refPowiazaniaToPowiazaniaRow}` on `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA`.`ID` = `{$refPowiazaniaToPowiazaniaRow}`.`PRIMARY_KEY`
join `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row` on `{$refPowiazaniaToPowiazaniaRow}`.`REMOTE_PRIMARY_KEY` = `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row`.`ID`
join `{$refPowiazaniaRowToPowiazaniaRowObject}` on `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row`.ID = `{$refPowiazaniaRowToPowiazaniaRowObject}`.`PRIMARY_KEY`
join `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object` on `{$refPowiazaniaRowToPowiazaniaRowObject}`.`REMOTE_PRIMARY_KEY` = `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object`.ID";
		$sqlWhere = "
where `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA`.`ID` = '{$ID}'";

		foreach ($refTables as $refTable) {
			$sqlDeleteFrom .= ",\n`{$refTable}`";
			$sqlUsing .= "\nleft join `{$refTable}` on `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object`.`ID` = `{$refTable}`.`PRIMARY_KEY`";
		}

		$query = $sqlDeleteFrom . $sqlUsing . $sqlWhere;
		DB::getPDO()->query($query);
	}

	public function doGenerate() {

		$generatePhpScript = function($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("display_errors", "1");
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");
date_default_timezone_set("Europe/Warsaw");

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 = self::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, 0770, true);
			if (!file_exists($tasksDirLocation)) throw new Exception('Error during creating temporary directory.');


			### Wyłączenie wcześniej używanego mechanizmu wymuszania częściowego (oraz pełnego od 2018-04-19) odświeżania cache - teraz działa to z automatu
			$sqlArr = [
				'BI_analiza_reloadCache' => 'No',
			];
			$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where BI_analiza_reloadCache in ('Part', 'Full') and FILE_STATUS = 'TO_GENERATE'";
			$result = DB::getPDO()->fetchAll($query);
			foreach ($result as $row) {
				DB::getDB()->UPDATE_OBJ('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', array_merge(['ID' => $row['ID']], $sqlArr));
			}

/*			2018-04-19 - zmieniono działanie silnika tak, aby sam wykrywał czy należy wykonać pełne odświeżenie cache
			### 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 in ('Full') 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' => 'Ukończono pełne Odświeżenie cache, oczekiwanie na wygenerowanie powiązań',
								'BI_analiza_reloadCache' => 'No',
							];
							$doGenerate = true;
						} else {
							$sqlArr = [
								'FILE_STATUS' => 'ERROR',
								'FILE_STATUS_info' => "Wystąpił błąd podczas pełnego 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 pełnego 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 pełne odświeżanie cache - oczekiwanie na dokończenie szukania powiązań dla innych rekordów',
						];
					} else {
						$sqlArr = [
							'FILE_STATUS_info' => 'W trakcie pełnego odświeżania cache',
						];

						$query = "select count(*) from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where BI_analiza_reloadCache = 'Full' and FILE_STATUS = 'TO_GENERATE'";
						$fullReloadCache = (DB::getPDO()->fetchValue($query) > 0);
						file_put_contents($reloadCachePhpFile, $generatePhpScript("doReloadCache({$fullReloadCache})"));
						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));
					}
				}

			}
*/			$doGenerate = true;

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

					$generatePowiazaniaResultFile = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}.result";
					$generatePowiazaniaPhpFile = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}.php";
					$generatePowiazaniaLogFile = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}.log";
					$generatePowiazaniaProgressFile = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}.progress";
					if (file_exists($generatePowiazaniaResultFile)) unlink ($generatePowiazaniaResultFile);
					if (file_exists($generatePowiazaniaProgressFile)) unlink ($generatePowiazaniaProgressFile);
					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 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)) {
					$generatePowiazaniaResult = json_decode(file_get_contents($generatePowiazaniaResultFile), true);
					if ($generatePowiazaniaResult['result'] == "ok") {
						$powiazanieDirLocation = self::getDirectory('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', $row['ID']);
						if (!file_exists($powiazanieDirLocation)) mkdir($powiazanieDirLocation, 0770, 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['message']})";
				} 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,
					];
					//self::deleteResultsFromDB($row['ID']);
					//if (file_exists($xmlFile)) unlink($xmlFile);
				} elseif ($success) {
					$sqlArr = [
						'FILE_STATUS' => 'GENERATED',
						'FILE_STATUS_info' => 'Poprawnie wygenerowano powiązania',
					];
					if ($generatePowiazaniaResult['parts']) {
						for ($i = 1; $i <= $generatePowiazaniaResult['parts']; $i++) {
							$partNo = str_pad($i, 6, '0', STR_PAD_LEFT);
							$generateFilesName = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}_{$partNo}";
							$generatePowiazaniaXmlFile = "{$generateFilesName}.xml";
							$generatePowiazaniaPdfFile = "{$generateFilesName}.pdf";
							$generatePowiazaniaHtmlDir = "{$generateFilesName}.html";
							$generatePowiazaniaHtmlZipFile = "{$generateFilesName}.zip";
							$destXmlFile = "{$powiazanieDirLocation}/relations-{$row['ID']}_{$partNo}.xml";
							$destPdfFile = "{$powiazanieDirLocation}/relations-{$row['ID']}_{$partNo}.pdf";
							$destHtmlDir = "{$powiazanieDirLocation}/html-{$row['ID']}_{$partNo}";
							$destHtmlZipFile = "{$powiazanieDirLocation}/html-{$row['ID']}_{$partNo}.zip";
							if (file_exists($generatePowiazaniaXmlFile)) rename($generatePowiazaniaXmlFile, $destXmlFile);
							if (file_exists($generatePowiazaniaPdfFile)) rename($generatePowiazaniaPdfFile, $destPdfFile);
							if (file_exists($generatePowiazaniaHtmlDir)) {
								if (file_exists($destHtmlDir)) shell_exec("rm -rf \"{$destHtmlDir}\"");
								rename($generatePowiazaniaHtmlDir, $destHtmlDir);
							}
							if (file_exists($generatePowiazaniaHtmlZipFile)) rename($generatePowiazaniaHtmlZipFile, $destHtmlZipFile);
						}
					} else {
						$generateFilesName = "{$tasksDirLocation}/generatePowiazania-{$row['ID']}";
						$generatePowiazaniaPdfFile = "{$generateFilesName}.pdf";
						$generatePowiazaniaHtmlDir = "{$generateFilesName}.html";
						$generatePowiazaniaHtmlZipFile = "{$generateFilesName}.zip";
						$destXmlFile = "{$powiazanieDirLocation}/relations-{$row['ID']}.xml";
						$destPdfFile = "{$powiazanieDirLocation}/relations-{$row['ID']}.pdf";
						$destHtmlDir = "{$powiazanieDirLocation}/html-{$row['ID']}";
						$destHtmlZipFile = "{$powiazanieDirLocation}/html-{$row['ID']}.zip";
						if (file_exists($generatePowiazaniaXmlFile)) rename($generatePowiazaniaXmlFile, $destXmlFile);
						if (file_exists($generatePowiazaniaPdfFile)) rename($generatePowiazaniaPdfFile, $destPdfFile);
						if (file_exists($generatePowiazaniaHtmlDir)) {
							if (file_exists($destHtmlDir)) shell_exec("rm -rf \"{$destHtmlDir}\"");
							rename($generatePowiazaniaHtmlDir, $destHtmlDir);
						}
						if (file_exists($generatePowiazaniaHtmlZipFile)) rename($generatePowiazaniaHtmlZipFile, $destHtmlZipFile);
					}
				}

				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 static function importPracownicy($full = false) {
		$fields = [
			'BI_audit_ENERGA_PRACOWNICY' => [
				'imiona' => 'imiona',
				'nazwisko' => 'nazwisko',
				'pesel' => 'pesel',
				'jednostka_organizacyjna' => 'jednostka_organizacyjna',
				'GUID' => 'GUID',
			],
			'BI_audit_ENERGA_PRACOWNICY_adresy' => [
				'kodPocztowy' => 'kodPocztowy',
				'miejscowosc' => 'miejscowosc',
				'ulica' => 'ulica',
				'nrBudynku' => 'nr',
				'nrLokalu' => 'nrLokalu',
			],
		];

		$refTable = BiAuditRefTables::getRefTable('BI_audit_ENERGA_PRACOWNICY', 'BI_audit_ENERGA_PRACOWNICY_adresy', true);

		if ($full) DB::getPDO()->query("update `BI_audit_ENERGA_PRACOWNICY_import` set `A_STATUS` = 'WAITING'");
		DB::getPDO()->query("update `BI_audit_ENERGA_PRACOWNICY_import` set `pesel` = null where `A_STATUS` = 'WAITING' and isPesel(`pesel`) = 0");

		$query = "select * from `BI_audit_ENERGA_PRACOWNICY_import` where `A_STATUS` = 'WAITING'";
		$pracownicy = DB::getPDO()->query($query);
		while ($pracownik = $pracownicy->fetch()) {
			if ($pracownik['pesel']) {
				$ID_pracownik = DB::getPDO()->fetchValue("select `ID` from `BI_audit_ENERGA_PRACOWNICY` where `pesel` = '{$pracownik['pesel']}' or (coalesce(`pesel`, '') = '' and `GUID` = '{$pracownik['GUID']}')");
			} elseif ($pracownik['GUID']) {
				$ID_pracownik = DB::getPDO()->fetchValue("select `ID` from `BI_audit_ENERGA_PRACOWNICY` where `GUID` = '{$pracownik['GUID']}'");
			} else {
				DB::getPDO()->update('BI_audit_ENERGA_PRACOWNICY_import', 'ID', $pracownik['ID'], ['A_STATUS' => 'OFF_SOFT']);
				continue;
			}

			$sqlArr = [];
			$sqlArr['source'] = 'import';
			$sqlArr['cached'] = '0';
			foreach ($fields['BI_audit_ENERGA_PRACOWNICY'] as $tableField => $importField) {
				$sqlArr[$tableField] = trim($pracownik[$importField]);
				if (!$sqlArr[$tableField]) unset($sqlArr[$tableField]);
			}

			if ($ID_pracownik) {
				DB::getPDO()->update('BI_audit_ENERGA_PRACOWNICY', 'ID', $ID_pracownik, $sqlArr);
				$full = true;
			}

			else $ID_pracownik = DB::getPDO()->insert('BI_audit_ENERGA_PRACOWNICY', $sqlArr);

			$sqlArr = [];
			foreach ($fields['BI_audit_ENERGA_PRACOWNICY_adresy'] as $tableField => $importField) {
				$sqlArr[$tableField] = trim($pracownik[$importField]);
				if (!$sqlArr[$tableField]) unset($sqlArr[$tableField]);
			}

			if (!$sqlArr) {
				DB::getPDO()->update('BI_audit_ENERGA_PRACOWNICY_import', 'ID', $pracownik['ID'], ['A_STATUS' => 'OFF_SOFT']);
				continue;
			}

			$sqlWhereArr = [];
			$sqlWhereArr[] = "`{$refTable}`.`PRIMARY_KEY` = '{$ID_pracownik}'";
			foreach ($fields['BI_audit_ENERGA_PRACOWNICY_adresy'] as $tableField => $importField) {
				if (isset($sqlArr[$tableField])) $sqlWhereArr[] = "`BI_audit_ENERGA_PRACOWNICY_adresy`.`{$tableField}` = " . DB::getPDO()->quote($sqlArr[$tableField]);
				else $sqlWhereArr[] = "`{$tableField}` is null";
			}
			$query = "select count(*) from `{$refTable}` join `BI_audit_ENERGA_PRACOWNICY_adresy` on `{$refTable}`.`REMOTE_PRIMARY_KEY` = `BI_audit_ENERGA_PRACOWNICY_adresy`.`ID` where " . implode(' and ', $sqlWhereArr);
			if (!(DB::getPDO()->fetchValue($query))) {
				$ID_adres = DB::getPDO()->insert('BI_audit_ENERGA_PRACOWNICY_adresy', $sqlArr);
				DB::getPDO()->insert($refTable, ['PRIMARY_KEY' => $ID_pracownik, 'REMOTE_PRIMARY_KEY' => $ID_adres]);
			}

			DB::getPDO()->update('BI_audit_ENERGA_PRACOWNICY_import', 'ID', $pracownik['ID'], ['A_STATUS' => 'NORMAL']);
		}

		return $full;
	}

	private static function reloadCache_parseFakturyUmowy($full = false) {
		$fields = [
			'BI_audit_ENERGA_RUM_KONTRAHENCI' => [
				'Tytul_dokumentu',
				'Pelna_nazwa_kontrahenta',
				'NIP',
				'REGON',
				'KRS',
				'Kraj',
				'Kod_pocztowy',
				'Miejscowosc',
				'Ulica',
				'Numer_budynku',
				'Numer_mieszkania_lokalu',
				'TERYT_SYM',
				'TERYT_SYM_UL',
			],
			'BI_audit_KRS' => [
				'"Zaimportowano z KRS na podstawie faktury/umowy"',
				'nazwa',
				'nip',
				'regon',
				'krs',
				'A_kraj',
				'A_kod',
				'A_miejscowosc',
				'A_ulica',
				'A_nrDomu',
				'A_nrLokalu',
				'TERYT_SYM',
				'TERYT_SYM_UL',
			],
			'BI_audit_CEIDG' => [
				'"Zaimportowano z CEiDG na podstawie faktury/umowy"',
				'firma',
				'nip',
				'regon',
				'null',
				'kraj',
				'kodPocztowy',
				'miejscowosc',
				'ulica',
				'budynek',
				'lokal',
				'TERYT_SYM',
				'TERYT_SYM_UL',
			],
		];

		$refTableFakturyToKontrahenci = BiAuditRefTables::getRefTable('BI_audit_ENERGA_FAKTURY', 'BI_audit_ENERGA_RUM_KONTRAHENCI', true);
		$refTableUmowyToKontrahenci = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_UMOWY', 'BI_audit_ENERGA_RUM_KONTRAHENCI', true);
		if ($full) {
			DB::getPDO()->query("truncate table `{$refTableFakturyToKontrahenci}`");
			DB::getPDO()->query("truncate table `{$refTableUmowyToKontrahenci}`");
			DB::getPDO()->query("update `BI_audit_ENERGA_FAKTURY` set `A_STATUS` = 'WAITING' where `A_STATUS` = 'NORMAL'");
			DB::getPDO()->query("update `BI_audit_ENERGA_RUM_UMOWY` set `A_STATUS` = 'WAITING' where `A_STATUS` = 'NORMAL'");
		}

		$query = "select `ID`, `NIP` from `BI_audit_ENERGA_FAKTURY` where `A_STATUS` = 'WAITING'";
		$result = DB::getPDO()->fetchAll($query);
		foreach ($result as $row) {
			$ID = $row['ID'];
			$nip = trim($row['NIP']);
			if (!$full) {
				$query = "delete from `{$refTableFakturyToKontrahenci}` where PRIMARY_KEY = '{$row['ID']}'";
				DB::getPDO()->query($query);
			}
			if (!$nip) {
				DB::getPDO()->update('BI_audit_ENERGA_FAKTURY', "ID", $ID, ['A_STATUS' => 'OFF_SOFT']);
				continue;
			}

			$K_IDs = [];
			$query = "select `ID` from `BI_audit_ENERGA_RUM_KONTRAHENCI` where `NIP` = '{$nip}'";
			if ($result2 = DB::getPDO()->fetchall($query)) {
				foreach ($result2 as $item) $K_IDs[] = $item['ID'];
			} else {
				$query = "select ID from BI_audit_KRS where nip = '{$nip}' order by `ID` desc limit 1";
				if ($result2 = DB::getPDO()->fetchall($query)) {
					foreach ($result2 as $item) {
						$query = "insert into `BI_audit_ENERGA_RUM_KONTRAHENCI` (" . implode(', ', $fields['BI_audit_ENERGA_RUM_KONTRAHENCI']) . ")" .
							" select " . implode(', ', $fields['BI_audit_KRS']) . " from `BI_audit_KRS` where `ID` = '{$item['ID']}'";
						if (DB::getPDO()->query($query)) $K_IDs[] = DB::getPDO()->lastInsertId();
					}
				} else {
					$query = "select `ID` from `BI_audit_CEIDG` where `nip` = '{$nip}' order by `ID` desc limit 1";
					if ($result2 = DB::getPDO()->fetchall($query)) {
						foreach ($result2 as $item) {
							$query = "insert into `BI_audit_ENERGA_RUM_KONTRAHENCI` (" . implode(', ', $fields['BI_audit_ENERGA_RUM_KONTRAHENCI']) . ")" .
								" select " . implode(', ', $fields['BI_audit_CEIDG']) . " from `BI_audit_CEIDG` where `ID` = '{$item['ID']}'";
							if (DB::getPDO()->query($query)) $K_IDs[] = DB::getPDO()->lastInsertId();
						}
					}
				}
			}

			foreach ($K_IDs as $K_ID) {
				if (!(DB::getPDO()->fetchValue("select count(*) from `{$refTableFakturyToKontrahenci}` where `PRIMARY_KEY` = {$ID} and `REMOTE_PRIMARY_KEY` = {$K_ID}"))) {
					DB::getPDO()->query("insert into `{$refTableFakturyToKontrahenci}` (`PRIMARY_KEY`, `REMOTE_PRIMARY_KEY`) values ({$ID}, {$K_ID})");
				}
			}
			DB::getPDO()->update('BI_audit_ENERGA_FAKTURY', "ID", $ID, ['A_STATUS' => 'NORMAL']);

		}

		$query = <<<SQL
update `BI_audit_ENERGA_RUM_UMOWY` set `Wartosc` = replace(
if(`Wartosc_przedmiotu_umowy_netto_w_PLN__aktualna_` not in ('0', '0,1', ''), `Wartosc_przedmiotu_umowy_netto_w_PLN__aktualna_`,
if(`Wartosc_przedmiotu_umowy_netto_w_PLN__pierwotna_` not in ('0', '0,1', ''), `Wartosc_przedmiotu_umowy_netto_w_PLN__pierwotna_`,
if(`Wartosc_kosztowa_przedmiotu_umowy_netto_w_walucie__aktualna_` not in ('0', '0,1', ''), `Wartosc_kosztowa_przedmiotu_umowy_netto_w_walucie__aktualna_`,
if(`Wartosc_kosztowa_przedmiotu_umowy_netto_w_walucie__pierwotna_` not in ('0', '0,1', ''), `Wartosc_kosztowa_przedmiotu_umowy_netto_w_walucie__pierwotna_`,
if(`Wartosc_przedmiotu_umowy_netto_w_walucie__pierwotna_` not in ('0', '0,1', ''), `Wartosc_przedmiotu_umowy_netto_w_walucie__pierwotna_`, 0))))),
',', '.') where `A_STATUS` = 'WAITING';
SQL;
		DB::getPDO()->query($query);

		$query = "select `ID`, `Kontrahenci` from `BI_audit_ENERGA_RUM_UMOWY` where `A_STATUS` = 'WAITING'";
		$result = DB::getPDO()->fetchAll($query);
		foreach ($result as $row) {
			$ID = $row['ID'];
			if (!$full) {
				$query = "delete from `{$refTableUmowyToKontrahenci}` where PRIMARY_KEY = '{$ID}'";
				DB::getPDO()->query($query);
			}
			$kontrahenci = array_map('trim', explode("|", $row['Kontrahenci']));
			$K_IDs = [];

			foreach ($kontrahenci as $item) {
				$kontrahent = array_map('trim', explode(";", $item));
				$nr = null;
				if (count($kontrahent) == 3) $i = 1;//$nr = $kontrahent[1];
				elseif (count($kontrahent) == 4) $i = 2;//$nr = $kontrahent[2];
				elseif (count($kontrahent) >= 5 && count($kontrahent) <= 6) $i = 3;//$nr = $kontrahent[3];
				else $i = null;
				if ($i) $nr = $kontrahent[$i];
				$notFound = false;
				if (is_numeric($nr)) {
					$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI where Numer_kontrahenta = '{$nr}' and not (NIP is null and REGON is null and KRS is null)";
					if ($K_ID = DB::getPDO()->fetchValue($query)) {
						$K_IDs[] = $K_ID;
					} else {
						$nazwa = $kontrahent[$i+1];
						$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI where Pelna_nazwa_kontrahenta = '" . addslashes($nazwa) . "' and not (NIP is null and REGON is null and KRS is null)";
						if ($result2 = DB::getPDO()->fetchAll($query)) {
							foreach ($result2 as $item) $K_IDs[] = $item['ID'];
						} else $notFound = true;
					}
				} else $notFound = true;
				if ($notFound) {
					list($nip, $regon) = self::parseNipRegon($item);
					if ($nip || $regon) {
						$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI where NIP = '{$nip}' or REGON = '{$regon}'";
						if ($result2 = DB::getPDO()->fetchall($query)) {
							foreach ($result2 as $item) $K_IDs[] = $item['ID'];
						} else {
							$query = "select ID from BI_audit_KRS where nip = '{$nip}' or regon = '{$regon}' order by `ID` desc limit 1";
							if ($result2 = DB::getPDO()->fetchall($query)) {
								foreach ($result2 as $item) {
									$query = "insert into BI_audit_ENERGA_RUM_KONTRAHENCI (" . implode(', ', $fields['BI_audit_ENERGA_RUM_KONTRAHENCI']) . ")" .
										" select " . implode(', ', $fields['BI_audit_KRS']) . " from BI_audit_KRS where ID = '{$item['ID']}'";
									if (DB::getPDO()->query($query)) $K_IDs[] = DB::getPDO()->lastInsertId();
								}
							} else {
								$query = "select ID from BI_audit_CEIDG where nip = '{$nip}' or regon = '{$regon}' order by `ID` desc limit 1";
								if ($result2 = DB::getPDO()->fetchall($query)) {
									foreach ($result2 as $item) {
										$query = "insert into BI_audit_ENERGA_RUM_KONTRAHENCI (" . implode(', ', $fields['BI_audit_ENERGA_RUM_KONTRAHENCI']) . ")" .
											" select " . implode(', ', $fields['BI_audit_CEIDG']) .
											" from BI_audit_CEIDG where ID = '{$item['ID']}'";
										if (DB::getPDO()->query($query)) $K_IDs[] = DB::getPDO()->lastInsertId();
									}
								}
							}
						}
					}
				}
			}

			foreach ($K_IDs as $K_ID) {
				if (!(DB::getPDO()->fetchValue("select count(*) from `{$refTableUmowyToKontrahenci}` where `PRIMARY_KEY` = {$ID} and `REMOTE_PRIMARY_KEY` = {$K_ID}"))) {
					DB::getPDO()->query("insert into `{$refTableUmowyToKontrahenci}` (PRIMARY_KEY, REMOTE_PRIMARY_KEY) values ({$ID}, {$K_ID})");
				}
			}
			DB::getPDO()->update('BI_audit_ENERGA_RUM_UMOWY', "ID", $ID, ['A_STATUS' => 'NORMAL']);
		}

		$query = "select ID, NIP, REGON, KRS from BI_audit_ENERGA_RUM_KONTRAHENCI where (NIP is null or REGON is null or KRS is null) and not (NIP is null and REGON is null and KRS is null) and `cached` = 0";
		$result = DB::getPDO()->fetchAll($query);
		foreach ($result as $row) {
			$query = "select nip, regon, krs from BI_audit_KRS where nip = '{$row['NIP']}' or regon = '{$row['REGON']}' or krs = '{$row['KRS']}'";
			if ($items = DB::getPDO()->fetchAll($query)) {
				list($nip, $regon, $krs) = [$items[0]["nip"], $items[0]["regon"], $items[0]["krs"]];
				if (count($items) > 1) {
					for ($i = 1; $i < count($items); $i++) {
						if ($nip != $items[$i]["nip"]) $nip = null;
						if ($regon != $items[$i]["regon"]) $regon = null;
						if ($krs != $items[$i]["krs"]) $krs = null;
					}
				}

				$set = [];
				if ($nip && $nip != $row['NIP']) $set[] = "NIP = '{$nip}'";
				if ($regon && $regon != $row['REGON']) $set[] = "REGON = '{$regon}'";
				if ($krs && $krs != $row['KRS']) $set[] = "KRS = '{$krs}'";
				if ($set) DB::getPDO()->query("update BI_audit_ENERGA_RUM_KONTRAHENCI set " . implode(', ', $set) . " where ID = '{$row['ID']}'");
			}
		}

		$query = "select ID, NIP, REGON from BI_audit_ENERGA_RUM_KONTRAHENCI where (NIP is null or REGON is null) and not (NIP is null and REGON is null) and `cached` = 0";
		$result = DB::getPDO()->fetchAll($query);
		foreach ($result as $row) {
			$query = "select nip, regon from BI_audit_CEIDG where nip = '{$row['NIP']}' or regon = '{$row['REGON']}'";
			if ($items = DB::getPDO()->fetchAll($query)) {
				list($nip, $regon) = [$items[0]["nip"], $items[0]["regon"]];
				if (count($items) > 1) {
					for ($i = 1; $i < count($items); $i++) {
						if ($nip != $items[$i]["nip"]) $nip = null;
						if ($regon != $items[$i]["regon"]) $regon = null;
					}
				}
				$set = [];
				if ($nip && $nip != $row['NIP']) $set[] = "NIP = '{$nip}'";
				if ($regon && $regon != $row['REGON']) $set[] = "REGON = '{$regon}'";
				if ($set) DB::getPDO()->query("update BI_audit_ENERGA_RUM_KONTRAHENCI set " . implode(', ', $set) . " where ID = '{$row->ID}'");
			}
		}
	}

	public static function reloadCache_reTeryt($full = false, $forceAll = false) {

		Lib::loadClass('Teryt');

		$tables = [
			'BI_audit_CEIDG' => [
				'fields' => [
					'wojewodztwo' => 'wojewodztwo',
					'powiat' => 'powiat',
					'gmina' => 'gmina',
					'miejscowosc' => 'miejscowosc',
					'ulica' => 'ulica',
					'kodPocztowy' => 'kodPocztowy',
				],
				'fieldsTeryt' => [
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'budynek',
					'nrLokalu' => 'lokal',
				],
				'base' => '1',
			],
			'BI_audit_CEIDG_pelnomocnicy' => [
				'fields' => [
					'wojewodztwo' => 'wojewodztwo',
					'powiat' => 'powiat',
					'gmina' => 'gmina',
					'miejscowosc' => 'miejscowosc',
					'ulica' => 'ulica',
					'kodPocztowy' => 'kodPocztowy',
				],
				'fieldsTeryt' => [
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'budynek',
					'nrLokalu' => 'lokal',
				],
				'base' => '1',
			],
			'BI_audit_ENERGA_PRACOWNICY' => [
				'fields' => [
					'miejscowosc' => 'miejscowosc',
					'kodPocztowy' => 'kodPocztowy',
					'ulica' => 'ulica',
				],
				'fieldsTeryt' => [
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'nr',
					'nrLokalu' => 'nrLokalu',
				],
				'base' => '0',
			],
			'BI_audit_ENERGA_PRACOWNICY_adresy' => [
				'fields' => [
					'kodPocztowy' => 'kodPocztowy',
					'miejscowosc' => 'miejscowosc',
					'ulica' => 'ulica',
				],
				'fieldsTeryt' => [
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'nrBudynku',
					'nrLokalu' => 'nrLokalu',
				],
				'base' => '0',
			],
			'BI_audit_ENERGA_RUM_KONTRAHENCI' => [
				'fields' => [
					'kodPocztowy' => 'Kod_pocztowy',
					'miejscowosc' => 'Miejscowosc',
					'ulica' => 'Ulica',
				],
				'fieldsTeryt' => [
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'Numer_budynku',
					'nrLokalu' => 'Numer_mieszkania_lokalu',
				],
				'base' => '0',
			],
			'BI_audit_KRS' => [ 
				'fields' => [
					'wojewodztwo' => 'S_wojewodztwo',
					'powiat' => 'S_powiat',
					'gmina' => 'S_gmina',
					'miejscowosc' => 'A_miejscowosc',
					'ulica' => 'A_ulica',
					'kodPocztowy' => 'A_kod',
				],
				'fieldsTeryt' => [
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'A_nrDomu',
					'nrLokalu' => 'A_nrLokalu',
				],
				'base' => '1',
			],
			'BI_audit_KRS_address' => [
				'fields' => [
					'wojewodztwo' => 'S_wojewodztwo',
					'powiat' => 'S_powiat',
					'gmina' => 'S_gmina',
					'miejscowosc' => 'A_miejscowosc',
					'ulica' => 'A_ulica',
					'kodPocztowy' => 'A_kod',
				],
				'fieldsTeryt' => [
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'A_nrDomu',
					'nrLokalu' => 'A_nrLokalu',
				],
				'base' => '1',
			],
			'BI_audit_MSIG_address' => [
				'fields' => [
					'wojewodztwo' => 'S_wojewodztwo',
					'powiat' => 'S_powiat',
					'gmina' => 'S_gmina',
					'miejscowosc' => 'A_miejscowosc',
					'ulica' => 'A_ulica',
					'kodPocztowy' => 'A_kod',
				],
				'fieldsTeryt' => [
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'A_nrDomu',
					'nrLokalu' => 'A_nrLokalu',
				],
				'base' => '1',
			],
		];

		foreach ($tables as $table => $tableConf) {
			DB::getPDO()->query("update `{$table}` set `{$tableConf['fieldsTeryt']['nrDomu']}` = null where trim(`{$tableConf['fieldsTeryt']['nrDomu']}`) = ''");
			DB::getPDO()->query("update `{$table}` set `{$tableConf['fieldsTeryt']['nrLokalu']}` = null where trim(`{$tableConf['fieldsTeryt']['nrLokalu']}`) = ''");
			$where = ($full && ($forceAll || (!$tableConf['base']))) ? "" : "where `TERYT_SYM` is null and `cached` = 0";
			$query = "select * from `{$table}` {$where}";
			$refTable = BiAuditRefTables::getRefTable($table, 'TERYT_adresy', true);

			$terytJoinArr = [];
			foreach ($tableConf['fieldsTeryt'] as $terytField => $tableField) {
				$terytJoinArr[] = (
					$terytField == 'nrLokalu'
						? "coalesce(`teryt`.`{$terytField}`, '') = coalesce(`table`.`{$tableField}`, '')"
						: "`teryt`.`{$terytField}` = `table`.`{$tableField}`"
				);
			}
			$terytJoin = implode(" and ", $terytJoinArr);
			$insertFields = "`" . implode("`, `", array_keys($tableConf['fieldsTeryt'])) . "`";
			$selectFields = "`" . implode("`, `", $tableConf['fieldsTeryt']) . "`";

			//$adresy = DB::getPDO()->queryNotBuffered($query);
			$adresy = DB::getPDO()->query($query);
			while ($adres = $adresy->fetch()) {
				DB::getPDO()->query("delete from `{$refTable}` where `PRIMARY_KEY` = '{$adres['ID']}'");

				if (!($forceAll and $adres['TERYT_SYM'])) {
					$search = [];
					foreach ($tableConf['fields'] as $field => $param) if (!empty($adres[$param])) $search[$field] = $adres[$param];
					try {
						$teryt = Teryt::search($search);
					} catch (Exception $e) {
						continue;
					}
					if (empty($teryt['SYM'])) continue;
					$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);
				}
				if (!trim($adres[$tableConf['fieldsTeryt']['nrDomu']])) continue;

				$terytID = DB::getPDO()->fetchValue("select `teryt`.`ID` from `TERYT_adresy` as `teryt` join `{$table}` as `table` on {$terytJoin} where `table`.`ID` = '{$adres['ID']}'");
				if (!$terytID) {
					if (DB::getPDO()->query("insert into `TERYT_adresy` ({$insertFields}) select {$selectFields} from `{$table}` where `ID` = '{$adres['ID']}'")) {
						if (!($terytID = DB::getPDO()->lastInsertId())) {
							throw new Exception("Błąd dodania rekordu do TERYT_adresy #1");
						}
					} else {
						throw new Exception("Błąd dodania rekordu do TERYT_adresy #2");
					}
				}

				DB::getPDO()->insert($refTable, ['PRIMARY_KEY' => $adres['ID'], 'REMOTE_PRIMARY_KEY' => $terytID]);
			}
		}
	}

	private static function reloadCache_updateAll($full, $onlyBase) {
		$tablesConf = [
			'BI_audit_CEIDG' => [
				'fields' => [
					'nip' => 'nip',
					'regon' => 'regon',
				],
				'base' => '1',
			],
			'BI_audit_CEIDG_pelnomocnicy' => [
				'fields' => [
					'nip' => 'nip',
				],
				'base' => '1',
			],
			'BI_audit_CEIDG_powiazania' => [
				'fields' => [
					'nip' => 'nip',
					'regon' => 'regon',
				],
				'base' => '1',
			],
			'BI_audit_ENERGA_PRACOWNICY' => [
				'fields' => [
					'nip' => 'nip',
					'regon' => 'regon',
					'pesel' => 'pesel',
					'krs' => 'krs',
				],
				'base' => '0',
				//'where' => '',
			],
			'BI_audit_ENERGA_PRACOWNICY_adresy' => [
				'fields' => [],
				'base' => '0',
				//'where' => '',
			],
                        
			'BI_audit_ENERGA_RUM_KONTRAHENCI' => [
				'fields' => [
					'krs' => 'KRS',
					'nip' => 'NIP',
					'regon' => 'REGON',
					'pesel' => 'PESEL',
				],
				'base' => '0',
				'where' => "and ownCompany != 'Y'",
			],
			'BI_audit_KRS' => [
				'fields' => [
					'krs' => 'krs',
					'nip' => 'nip',
					'regon' => 'regon',
				],
				'base' => '1',
			],
			'BI_audit_KRS_address' => [
				'fields' => [],
				'base' => '1',
			],
			'BI_audit_KRS_company' => [
				'fields' => [
					'regon' => 'regon',
					'krs' => 'krs',
				],
				'base' => '1',
			],
			'BI_audit_KRS_person' => [
				'fields' => [
					'pesel' => 'pesel',
				],
				'base' => '1',
			],
			'BI_audit_MSIG' => [
				'fields' => [
					'krs' => 'krs',
					'nip' => 'nip',
					'regon' => 'regon',
				],
				'base' => '1',
			],
			'BI_audit_MSIG_address' => [
				'fields' => [],
				'base' => '1',
			],
			'BI_audit_MSIG_company' => [
				'fields' => [
					'regon' => 'regon',
					'krs' => 'krs',
				],
				'base' => '1',
			],
			'BI_audit_MSIG_person' => [
				'fields' => [
					'pesel' => 'pesel',
				],
				'base' => '1',
			],
//			'BI_audit_KW_requested_person' => [
//				'fields' => [
//					'pesel' => 'Seller_person_pesel',
//					'krs' => 'Seller_person_KRS',
//					'nip' => 'Seller_person_NIP',
//					'regon' => 'Seller_person_REGON',
//				],
//				'base' => '0',
//				//'where' => '',
//			],
			'TERYT_adresy' => [
				'fields' => [],
				'base' => '1',
			],
		];

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

		if ($full) {
			foreach ($tablesConf as $table => $tableConf) if (!$tableConf['base']) $queries[] = "update `{$table}` set `cached` = 0";
			$queries[] = "delete from `BI_audit_ALL` where `BASE` = 0";
//			$queries[] = "optimize table `BI_audit_ALL`";
			$queries[] = "delete from `BI_audit_ALL_ref` where `BASE` = 0";
//			$queries[] = "optimize table `BI_audit_ALL_ref`";

			$allTables = [];
			foreach ($refTablesConf as $refTableFrom => $refTableConf) {
				$allTables[] = $refTableFrom;
//				if ($refTableConf['base']) continue;
				foreach ($refTableConf['refTablesTo'] as $refTableTo) {
					$allTables[] = $refTableTo;
					if ($refTableConf['base']) continue;
					$refTable = BiAuditRefTables::getRefTable($refTableFrom, $refTableTo, true);
					$queries[] = "update `{$refTable}` set `A_STATUS` = 'WAITNG' where `A_STATUS` = 'NORMAL'";
//					$queries[] = "insert into `{$refTable}` (`PRIMARY_KEY`, `REMOTE_PRIMARY_KEY`, `TRANSACTION_ID`, `A_STATUS`) select `all1`.`ID`, `all2`.`ID`, '-1', 'NORMAL' from `BI_audit_ALL` as `all1` join `BI_audit_ALL_ref` as `ref` on `all1`.`ID` = `ref`.`ID1` join `BI_audit_ALL` as `all2` on `ref`.`ID2` = `all2`.`ID` where `all1`.`REMOTE_TABLE` = '{$refTableFrom}' and `all2`.`REMOTE_TABLE` = '{$refTableTo}'";
				}
			}
			$allTables = array_unique($allTables);

			foreach ($allTables as $refTableFrom) {
				foreach ($allTables as $refTableTo) {
					if ($refTable = BiAuditRefTables::getRefTable($refTableFrom, $refTableTo, false)) {
						$queries[] = "delete from `{$refTable}` where `TRANSACTION_ID` = '-1'";
						$queries[] = "optimize table `{$refTable}`";
						$queries[] = "insert into `{$refTable}` (`PRIMARY_KEY`, `REMOTE_PRIMARY_KEY`, `TRANSACTION_ID`, `A_STATUS`) select `all1`.`REMOTE_ID`, `all2`.`REMOTE_ID`, '-1', 'NORMAL' from `BI_audit_ALL` as `all1` join `BI_audit_ALL_ref` as `ref` on `all1`.`ID` = `ref`.`ID1` join `BI_audit_ALL` as `all2` on `ref`.`ID2` = `all2`.`ID` left join `${refTable}` as `ref2` on `all1`.`REMOTE_ID` = `ref2`.`PRIMARY_KEY` and `all2`.`REMOTE_ID` = `ref2`.`REMOTE_PRIMARY_KEY` where `all1`.`REMOTE_TABLE` = '{$refTableFrom}' and `all2`.`REMOTE_TABLE` = '{$refTableTo}' and `ref2`.`PRIMARY_KEY` is null";
					}
				}
			}
		}

		foreach ($tablesConf as $table => $tableConf) {
			if ($onlyBase && (!$tableConf['base'])) continue;
			$where = "where `{$table}`.`cached` = 0";
			if (!empty($tableConf['where'])) $where .= " {$tableConf['where']}";
//			$queries[] = "insert into `BI_audit_ALL` (`REMOTE_TABLE`, `REMOTE_ID`, `BASE`, " . implode(", ", array_keys($tableConf['fields'])) .
//				") select '{$table}', ID, '{$tableConf['base']}', " . implode(", ", $tableConf['fields']) . " from `{$table}` {$where}";
			$fields = ["`REMOTE_TABLE`", "`REMOTE_ID`", "`BASE`"];
			$values = ["'{$table}'", "`ID`", "'{$tableConf['base']}'"];
			$updates = ["`BI_audit_ALL`.`reffed` = 0", "`{$table}`.`cached` = 1"];
			foreach ($tableConf['fields'] as $field => $value) {
				$fields[] = "`{$field}`";
				$values[] = "`{$value}`";
				$updates[] = "`BI_audit_ALL`.`{$field}` = `{$table}`.`{$value}`";
			}
			$queries[] = "update `BI_audit_ALL` join `{$table}` on `BI_audit_ALL`.`REMOTE_TABLE` = '{$table}' and `BI_audit_ALL`.`REMOTE_ID` = `{$table}`.`ID` set " . implode(", ", $updates) . " {$where}";
			$queries[] = "insert into `BI_audit_ALL` (" . implode(", ", $fields) . ") select " . implode(", ", $values) . " from `{$table}` {$where}";
			$queries[] = "update `{$table}` set `cached` = 1 {$where}";
		}

		$queries[] = "update `BI_audit_ALL` set `nip` = null where `nip` = 0";
		$queries[] = "update `BI_audit_ALL` set `regon` = null where `regon` = 0";
		$queries[] = "update `BI_audit_ALL` set `krs` = null where `krs` = 0";
		$queries[] = "update `BI_audit_ALL` set `pesel` = null where `pesel` = 0";

		$queries[] = "set @var = (select round(coalesce(max(`ID`), '0.5'), 1) from `BI_audit_ALL_ref_RELATIONS`)";
		$queries[] = "insert into `BI_audit_ALL_ref_RELATIONS` (`ID`, `RELATION`, `BASE`) select @var:=@var*2, `a`.`RELATION`, `a`.`BASE` from (select `a`.`REMOTE_TABLE` as `RELATION`, `a`.`BASE` as `BASE` 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` order by `a`.`BASE` desc) as `a` left join `BI_audit_ALL_ref_RELATIONS` `r` on `a`.`RELATION` = `r`.`RELATION` where `r`.`RELATION` is null";

		foreach ($refTablesConf as $refTableFrom => $refTableConf) {
			if ($onlyBase && (!$tableConf['base'])) continue;
			foreach ($refTableConf['refTablesTo'] as $refTableTo) {
				$refTable = BiAuditRefTables::getRefTable($refTableFrom, $refTableTo, true);
				$queries[] = "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` = 'WAITING' 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[] = "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` = 'WAITING' 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`";
				}
				if ($backRefTable = BiAuditRefTables::getRefTable($refTableTo, $refTableFrom, false)) {
					$queries[] = "insert into `{$backRefTable}` (`PRIMARY_KEY`, `REMOTE_PRIMARY_KEY`, `TRANSACTION_ID`, `A_STATUS`) select `REMOTE_PRIMARY_KEY`, `PRIMARY_KEY`, '-1', 'NORMAL' from `{$refTable}` where `A_STATUS` = 'WAITING'";
				}
				$queries[] = "update `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` = 'WAITING' join `BI_audit_ALL` as `all2` on `all2`.`REMOTE_TABLE` = '{$refTableTo}' and `ref`.`REMOTE_PRIMARY_KEY` = `all2`.`REMOTE_ID` set `ref`.`A_STATUS` = 'NORMAL'";
			}
		}

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

		$BiAuditRelations = new BiAuditRelations();
		//$result = DB::getPDO()->queryNotBuffered("select `ID` from `BI_audit_ALL` where `reffed` = 0 order by `ID`");
		$result = DB::getPDO()->query("select `ID` from `BI_audit_ALL` where `reffed` = 0 order by `ID`");
		while ($row = $result->fetch()) $BiAuditRelations->findRelations($row['ID']);
		DB::getPDO()->query("update `BI_audit_ALL` set `reffed` = 1");
	}

	public function doReloadCache($full = false) {
		if ($full === 'base') {
			$onlyBase = true;
			$full = false;
		} else {
			$onlyBase = false;
		}

		$setFull = function() {
			BiAuditPowiazania::saveToLog("Wykryto niespójność tabel - uruchamiam pełne odświeżenie cache");
			$sqlArr = [
				'BI_analiza_reloadCache' => 'Full',
				'FILE_STATUS_info' => 'Odświeżam w pełni cache',
			];
			DB::getPDO()->update('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', "ID", $this->POWIAZANIA_ID, $sqlArr);
		};

		if ($this->POWIAZANIA_ID && (!$full)) {
			$checkTables = ['BI_audit_ENERGA_PRACOWNICY', 'BI_audit_ENERGA_PRACOWNICY_adresy', 'BI_audit_ENERGA_RUM_KONTRAHENCI'];
			foreach ($checkTables as $table) {
				$query = "select count(*) from `BI_audit_ALL` `all` left join `{$table}` `t` on `all`.`REMOTE_ID`=`t`.`ID` and `t`.`cached` = '1' where `all`.`REMOTE_TABLE`='{$table}' and `t`.`ID` is null";
				if (DB::getPDO()->fetchValue($query)) {
					$full = true;
					$setFull();
					break;
				}
			}
		}

		$powiazaniaDirLocation = self::getMainDirectory();
		$tasksDirLocation = $powiazaniaDirLocation . "/.tasks";
		$reloadCachePidFile = $tasksDirLocation . "/reloadCache.pid";
		$reloadCacheResultFile = $tasksDirLocation . "/reloadCache.result";
		if (!$this->POWIAZANIA_ID) file_put_contents($reloadCachePidFile, getmypid());
		try {
			error_log('now: self::importPracownicy');
			$_full = self::importPracownicy($full);
			if ((!$full) && $_full) {
				$full = true;
				if ($this->POWIAZANIA_ID) $setFull();
			}
			error_log('now: self::reloadCache_parseFakturyUmowy');
			self::reloadCache_parseFakturyUmowy($full);
			error_log('now: self::reloadCache_reTeryt');
			self::reloadCache_reTeryt($full);
			error_log('now: self::reloadCache_updateAll');
			self::reloadCache_updateAll($full, $onlyBase);
			if (!$this->POWIAZANIA_ID) file_put_contents($reloadCacheResultFile, "ok");
		} catch (Exception $e) {
			var_dump($e);
			if (!$this->POWIAZANIA_ID) file_put_contents($reloadCacheResultFile, $e->getMessage());
			else throw new Exception($e->getMessage());
		}
	}

	public function doGeneratePowiazania($ID) {
		$this->POWIAZANIA_ID = $ID;
		$powiazaniaDirLocation = self::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, $tasksDirLocation);

			$query = "select count(*) from `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA` where `FILE_STATUS` = 'IN_PROGRESS' and `BI_analiza_reloadCache` in ('Part', 'Full') and ID != ${ID}";
			$sqlArr = [
				'BI_analiza_reloadCache' => 'Part',
				'FILE_STATUS_info' => 'Odświeżam częściowo cache',
			];
			do {
				DB::getPDO()->query("lock tables `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA` write");
				$activeReloadingCache = DB::getPDO()->fetchValue($query);
				if (!$activeReloadingCache) DB::getPDO()->update('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', "ID", $ID, $sqlArr);
				DB::getPDO()->query("unlock tables");
				if ($activeReloadingCache) {
					BiAuditPowiazania::saveToLog("Czekam na zakończenie odświeżania cache przez inne zadanie");
					sleep(30);
				}
			} while ($activeReloadingCache);
			
			BiAuditPowiazania::saveToLog("Uruchamiam częściowe odświeżenie cache");
			$this->doReloadCache(); //DUPA testowe wylaczanie
			BiAuditPowiazania::saveToLog("Odświeżono cache");

			$sqlArr = [
				'BI_analiza_reloadCache' => 'No',
				'FILE_STATUS_info' => 'Szukam powiązań',
			];
			DB::getPDO()->update('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', "ID", $ID, $sqlArr);

			$BiAuditPowiazania->run();
			//if (!$BiAuditPowiazania->powiazaniaFound()) throw new Exception("Nie znaleziono żadnych powiązań");
			//file_put_contents($xmlFile, $BiAuditPowiazania->asXml());

			$sqlArr = ['FILE_STATUS_info' => 'Generuję raporty PDF i HTML'];
			DB::getPDO()->update('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', "ID", $ID, $sqlArr);
			
			//$BiAuditPowiazania->saveXml();
			$BiAuditPowiazania->generatePdfAndHtml();
			file_put_contents($resultFile, json_encode(['result' => 'ok', 'parts' => $BiAuditPowiazania->getPartsCount()]));
		} catch (Exception $e) {
			file_put_contents($resultFile, json_encode(['result' => 'error', 'message' => $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;
		$sqlList['ChangeStructure1'] = <<<SQL
ALTER TABLE `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA` CHANGE `BI_analiza_reloadCache` `BI_analiza_reloadCache` ENUM('Y','N','Full','Part','No') NOT NULL DEFAULT 'No';
SQL;
		$sqlList['ChangeStructure2'] = <<<SQL
UPDATE `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA` SET `BI_analiza_reloadCache` = IF (`BI_analiza_reloadCache` = 'Y', 'Part', 'No');
SQL;
		$sqlList['ChangeStructure3'] = <<<SQL
ALTER TABLE `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA` CHANGE `BI_analiza_reloadCache` `BI_analiza_reloadCache` ENUM('Full','Part','No') NOT NULL DEFAULT 'No';
SQL;
		$sqlList['ChangeStructure4'] = <<<SQL
ALTER TABLE `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA` CHANGE `BI_analiza_depth` `BI_analiza_maxDepth` INT(2) NULL DEFAULT '9';
SQL;
		$sqlList['ChangeStructure5'] = <<<SQL
ALTER TABLE `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA` ADD `BI_analiza_minDepth` INT(2) NOT NULL DEFAULT '0' AFTER `FILE_STATUS_info`;
SQL;
		$sqlList['ChangeStructure6'] = <<<SQL
ALTER TABLE `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA` ADD `BI_analiza_onlyTargets` ENUM('Y','N') NOT NULL DEFAULT 'Y' AFTER `BI_analiza_maxDepth`;
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'],
	];

	private $destTables = ['BI_audit_ENERGA_RUM_KONTRAHENCI', 'BI_audit_KW_requested_person'];
	private $sourceTables = ['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];
	}

	private function addToRefTable($fromTable, $toTable, $fromID, $toID) {
		if ($refTable = BiAuditRefTables::getRefTable($fromTable, $toTable, false)) {
			if (!DB::getPDO()->fetchValue("select count(*) from `{$refTable}` where `PRIMARY_KEY` = '{$fromID}' and `REMOTE_PRIMARY_KEY` = '{$toID}' and `A_STATUS` != 'DELETED'")) {
				DB::getPDO()->insert($refTable, ['PRIMARY_KEY' => $fromID, 'REMOTE_PRIMARY_KEY' => $toID, 'A_STATUS' => 'NORMAL', 'TRANSACTION_ID' => '-1']);
			}
		}
	}

	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] !== null) $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, t1.REMOTE_ID as REMOTE_ID_1, t2.REMOTE_TABLE as REMOTE_TABLE_2, t2.REMOTE_ID as REMOTE_ID_2 {$items}, t1.BASE * t2.BASE as BASE " .
			"from BI_audit_ALL t1 join BI_audit_ALL t2 on " . implode(" or ", $joins) . " where t1.ID = {$ID} and (t2.reffed = 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, BASE) values ({$ID}, {$row['ID']}, '{$relationID}', {$end2}, {$row['BASE']})";
				$this->addToRefTable($row['REMOTE_TABLE_1'], $row['REMOTE_TABLE_2'], $row['REMOTE_ID_1'], $row['REMOTE_ID_2']);
				$this->addToRefTable($row['REMOTE_TABLE_2'], $row['REMOTE_TABLE_1'], $row['REMOTE_ID_2'], $row['REMOTE_ID_1']);
				DB::getPDO()->query($query);
			}
			if (!($start1 || $end2)) {
				$query = "insert into BI_audit_ALL_ref (ID2, ID1, RELATION_ID, END, BASE) values ({$ID}, {$row['ID']}, '{$relationID}', {$end1}, {$row['BASE']})";
				$this->addToRefTable($row['REMOTE_TABLE_1'], $row['REMOTE_TABLE_2'], $row['REMOTE_ID_1'], $row['REMOTE_ID_2']);
				$this->addToRefTable($row['REMOTE_TABLE_2'], $row['REMOTE_TABLE_1'], $row['REMOTE_ID_2'], $row['REMOTE_ID_1']);
				DB::getPDO()->query($query);
			}
		}
	}
}

class BiAuditRefTables {
	public static function getRefTable($tableFrom, $tableTo, $returnException = false) {
		static $refTables = [];
		static $tempRefTables = [];

		if (is_array($tableTo)) {
			if (!$tableTo) throw new Exception('BiAuditRefTables::getRefTable - bad parameter $tableTo');
			if (count($tableTo) == 1) return self::getRefTable($tableFrom, reset($tableTo), $returnException);

			$key = implode("__", $tableTo);
			if (!isset($tempRefTables[$tableFrom][$key])) {
				try {
					$_tableFrom = $tableFrom;
					foreach ($tableTo as $_tableTo) {
						$_refTables[] = self::getRefTable($_tableFrom, $_tableTo, true);
						$_tableFrom = $_tableTo;
					}

					$name = "TEMP__#REF_TABLE__" . (count($tempRefTables) + 1);
					$first = reset($_refTables);
					$last = end($_refTables);
					$prev = array_shift($_refTables);

					$query = "create temporary table `{$name}` select `{$first}`.`PRIMARY_KEY`, `{$last}`.`REMOTE_PRIMARY_KEY` from `{$first}`";
					while ($next = array_shift($_refTables)) {
						$query .= " join `{$next}` on `{$prev}`.`REMOTE_PRIMARY_KEY` = `{$next}`.`PRIMARY_KEY`";
						$prev = $next;
					}
					$query .= " group by `{$first}`.`PRIMARY_KEY`, `{$last}`.`REMOTE_PRIMARY_KEY`";
					DB::getPDO()->query($query);
					$tempRefTables[$tableFrom][$key] = $name;
				} catch (Exception $e) {
					if ($returnException) throw new Exception($e->getMessage());
					$tempRefTables[$tableFrom][$key] = false;
				}
			}
			return $tempRefTables[$tableFrom][$key];
		} else {
			if (!isset($refTables[$tableFrom][$tableTo])) {
				try {
					$refTables[$tableFrom][$tableTo] = ACL::getRefTable("default_db/{$tableFrom}/{$tableFrom}", "default_db__x3A__{$tableTo}:{$tableTo}");
				} catch (Exception $e) {
					if ($returnException) throw new Exception($e->getMessage());
					$refTables[$tableFrom][$tableTo] = false;
				}
			}
			return $refTables[$tableFrom][$tableTo];
		}
	}

	public static function getRefTables($tableFrom) {
		static $refTables = [];
		if (!isset($refTables[$tableFrom])) {
			$namespace = "default_db/{$tableFrom}/{$tableFrom}";
			try {
				$item = SchemaFactory::loadDefaultObject('SystemObject')->getItem($namespace, [ 'propertyName' => '*,field' ]);
				if (!($item['hasStruct'] && $item['isStructInstalled'] && $item['isObjectActive'])) throw new Exception("Object error");
				$fields = $item['field'];

				$_refTables = [];
				foreach ($fields as $field) {
					list($type, $child) = explode(":", $field['xsdType'], 2);
					if ($type == "ref" && $field['idZasob'] && $field['isActive'] && (!$field['isLocal'])) {
						try {
							$_refTable = ACL::getRefTable($namespace, $child);
							$_refTables[] = $_refTable;
						} catch (Exception $e) {
						}
					}
				}
				$refTables[$tableFrom] = $_refTables;
			} catch (Exception $e) {
				$refTables[$tableFrom] = [];
			}
		}
		return $refTables[$tableFrom];
	}
}

class BiAuditPowiazania {

	private $minDepth;
	private $maxDepth, $origMaxDepth;
	private $onlyTargets;
	private $ID;
	private $nodes = [];
	private $endNodes;
	private $path = [];
	private $results = [];
	private $items_results = [];
	private $relations = [];
	private $srcTables = ['BI_audit_ENERGA_PRACOWNICY']; //, ['BI_audit_POWIAZANIA_OD', 'BI_audit_POWIAZANIA_OBIEKTY']];
	private $destTables = ['BI_audit_ENERGA_RUM_KONTRAHENCI', 'BI_audit_KW_requested_person']; //, ['BI_audit_POWIAZANIA_DO', 'BI_audit_POWIAZANIA_OBIEKTY']];
	private $step = 0;
	private $tasksDirLocation;
	private $progressFile;
	private $progress = [
		'summary' => [
			'count' => 0,
			'message' => 'Inicjacja',
			'ts' => null,
			'step' => 'initialize',
			'reportsCount' => 0,
			'reportsCreated' => 0,
		],
		'details' => [],
	];
	private $lastProgress = 0;
	private $lastProgress_slowCheck = 0;
	private $lastResults = 0;
	private $startTimestamp = null;
	private $lastTimestamp = null;
	private $lowProgressCount = 0;
	private $count, $i;
	private $splittedResults = [];
	private $maxResults = 1000000;
	private $fidRow = [];
	private $itemID = 0;
	private $reverseSearch = false;
	private $query = "";

	private static function generateJson($type, $data) {
		return json_encode(['ts' => microtime(true), 'type' => $type, 'data' => $data]);
	}

	public static function saveToLog($message) {
		$messageJson = self::generateJson('message', ['message' => $message]);
		echo $messageJson . "\n";
	}

	private static function exec($cmd) {
		$path = 'export PATH=$PATH:/usr/sbin;';
		$cmd = "{$path} {$cmd}";
		echo "CMD :: {$cmd}\n";
		shell_exec($cmd);
	}

	private function saveProgress($args = []) {
		$timestamp = microtime(true);
		if (isset($args['progress']) && $this->i) {
			$elapsed = $timestamp - $this->startTimestamp;
			if ($args['progress']) {
				$estimated = ($elapsed * (1 - $args['progress'])) / $args['progress'];
			} else $estimated = "N/A";
			$this->lastProgress = $args['progress'];
			$this->lastTimestamp = $timestamp;
			$progressJson = self::generateJson('progress', ['progress' => $args['progress'], 'item' => $this->i . "/" . $this->count, 'results' => count($this->results), 'step' => $this->step, 'elapsed' => $elapsed, 'estimated' => $estimated]);
			echo $progressJson . "\n";
			$this->progress['details'][$this->itemID] = ['progress' => $args['progress'], 'results' => (count($this->results) - $this->lastResults), 'elapsed' => $elapsed, 'estimated' => $estimated];
		}
		if (isset($args['message'])) $this->progress['summary']['message'] = $args['message'];
		if (isset($args['step'])) $this->progress['summary']['step'] = $args['step'];
		if (isset($args['reportsCreated'])) $this->progress['summary']['reportsCreated'] = round($this->progress['summary']['reportsCreated'] + $args['reportsCreated'], 2);
		$this->progress['summary']['ts'] = $timestamp;
		file_put_contents($this->progressFile, json_encode($this->progress));
	}

	private static function throwException($message) {
		self::saveToLog($message);
		throw new Exception($message);
	}

	public function __construct($ID = 0, $tasksDirLocation = null) {
		if (!$ID) self::throwException("Wrong ID parameter");
		if (!$tasksDirLocation) self::throwException("Wrong directory of tasks location");
		$this->ID = $ID;
		$this->tasksDirLocation = $tasksDirLocation;
		$this->progressFile = "{$this->tasksDirLocation}/generatePowiazania-{$this->ID}.progress";
		$this->saveProgress();
	}

	public function run() {
		$query = "select BI_analiza_minDepth, BI_analiza_maxDepth, BI_analiza_onlyTargets from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where ID = '{$this->ID}' and FILE_STATUS = 'IN_PROGRESS' and BI_analiza_reloadCache not in ('Full')";
		$result = DB::getPDO()->fetchAll($query);
		if (!$result) self::throwException("Błąd danych");

		if ($this->loadResults()) {
			$this->splitResults();
			return;
		}

		self::saveToLog("Wczytuję parametry wyszukiwania powiązań");
		$this->minDepth = (int) $result[0]['BI_analiza_minDepth'];
		$this->maxDepth = (int) $result[0]['BI_analiza_maxDepth'];
		$this->onlyTargets = ($result[0]['BI_analiza_onlyTargets'] != 'N');
		if (!$this->minDepth) $this->minDepth = 1;
		if (!$this->maxDepth) self::throwException("Błąd danych - nieokreślono maksymalnej głębokości analizy");
		if ($this->minDepth > $this->maxDepth) self::throwException("Wartość minimalnej głębokości analizy jest większa od wartości maksymalnej głębokości analizy");

		$subQueries = [];
		$resultDest = [];
		foreach ($this->destTables as $destTable) {
			if ($refTable = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', $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` = '{$this->ID}'";
			}
		}
		if ($subQueries) {
			$query = implode(" union ", $subQueries) . " order by `ID` asc";
			$resultDest = DB::getPDO()->fetchAll($query);
		}
		if ((!$resultDest) && $this->onlyTargets) self::throwException("Błąd danych - nie zdefinowano żadnego końcowego obiektu");

		$subQueries = [];
		$resultSrc = [];
		foreach ($this->srcTables as $srcTable) {
			if ($refTable = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', $srcTable)) {
				$subQueries[] = "select `all`.`ID` from `BI_audit_ALL` `all` join `{$refTable}` `ref` on `all`.`REMOTE_TABLE` = '{$srcTable}' and `all`.`REMOTE_ID` = `ref`.`REMOTE_PRIMARY_KEY` and `ref`.`PRIMARY_KEY` = '{$this->ID}'";
			}
		}
		if ($subQueries) {
			$query = implode(" union ", $subQueries) . " order by `ID` asc";
			$resultSrc = DB::getPDO()->fetchAll($query);
		}
		if (!$resultSrc) self::throwException("Błąd danych - nie zdefinowano żadnego początkowego obiektu");

		if (count($resultSrc) <= count($resultDest) || (!$this->onlyTargets)) {
			$result = $resultSrc;
			$this->endNodes = array_map('reset', $resultDest);
			$this->reverseSearch = false;
			$this->query = "select ref.ID2 as ID, RELATION_ID from BI_audit_ALL_ref ref where ref.ID1";
		} else {
			$result = $resultDest;
			$this->endNodes = array_map('reset', $resultSrc);
			$this->reverseSearch = true;
			$this->query = "select ref.ID1 as ID, RELATION_ID from BI_audit_ALL_ref ref where ref.ID2";
			self::saveToLog("Docelowych obiektów jest mniej, niż początkowych - włączono mechanizm odwrotnego przeszukiwania");
		}

		self::saveToLog("Rozpoczynam wyszukiwanie powiązań");

		$this->count = count($result);
		$this->progress['summary']['count'] = $this->count;
		$this->origMaxDepth = $this->maxDepth;
		$this->saveProgress(['step' => 'relations']);

		try {
			foreach ($result as $i => $row) {
				$this->i = $i + 1;
				$this->itemID = DB::getPDO()->fetchValue("select `REMOTE_ID` from `BI_audit_ALL` where `ID` = :ID", [":ID" => $row['ID']]);
				$this->startTimestamp = microtime(true);
				$this->lastProgress = 0;
				$this->lastProgress_slowCheck = 0;
				$this->saveProgress(['progress' => 0, 'message' => "Wyszukiwanie powiązań dla obiektu {$this->i}/{$this->count}"]);
				if ($this->maxDepth != $this->origMaxDepth) {
					$this->maxDepth = $this->origMaxDepth;
					self::saveToLog("Przywrócono oryginalną maksymalną głębokość przeszukiwania ({$this->origMaxDepth})");
				}
				$this->findPowiazania($row['ID']);
				$this->saveProgress(['progress' => 1, 'message' => "Zakończono wyszukiwanie powiązań dla obiektu {$this->i}/{$this->count}"]);
				$this->lastResults = count($this->results);
			}
		} catch (Exception $e) {
			if ($this->i < $this->count) {
				$this->startTimestamp = microtime(true);
				$this->lastProgress = 0;
				$this->lastResults = count($this->results);
				while (++$this->i <= $this->count) $this->saveProgress(['progress' => 0]);
			}
			if ($message = $e->getMessage()) self::saveToLog("Wystąpił błąd podczas wyszukiwania powiązań - {$message}");
		}

		$this->nodes = [];
		self::saveToLog("Zakończono wyszukiwanie powiązań");
		$this->sortResults();
		$this->saveResults();
		//$this->saveToDb(); 2017-12-14 - przerobiono na zapisywanie danych do bazy w locie
		$this->splitResults();
	}

	private static function reversePath($path) {
		$arr = array_reverse($path, true);
		$keys = array_keys($arr);
		array_unshift($arr, array_pop($arr));
		return array_combine($keys, $arr);
	}

	private function addResult($progress) {
		if (count($this->results) == $this->maxResults) {
			$message = "Przerwano wyszukiwanie powiązań - osiągnięto maksymalną liczbę wyników {$this->maxResults}";
			self::saveToLog($message);
			$this->saveProgress(['progress' => $progress, 'message' => $message]);
			throw new Exception('');
		}
		if ($this->reverseSearch) $path = self::reversePath($this->path);
		else $path = $this->path;
		$this->results[] = $path;
		$this->saveToDb($path);           
	}

	private static function weightCalc($n) {
		if ($n < 21) return 1;
		if ($n < 41) return 2;
		if ($n < 81) return 3;
		if ($n < 161) return 4;
		if ($n < 321) return 5;
		if ($n < 641) return 6;
		if ($n < 1281) return 7;
		if ($n < 2561) return 8;
		if ($n < 5121) return 9;
		return 10;
	}

	public function findPowiazania($ID, $progress = 0, $steps = 1, $relation = "", $weight = 0) {
		$this->step++;

		if ($this->maxDepth > $this->minDepth) {
			if (($this->step % 1000000) == 0) {
				$progressDiff = $progress - $this->lastProgress_slowCheck;
				if ($progressDiff < 0.00005) {
					$this->lowProgressCount++;
					if ($this->lowProgressCount == 10) {
						$this->maxDepth--;
						self::saveToLog("Zbyt dużo rekurencji, zmniejszono maksymalną głębokość poszukiwań do {$this->maxDepth} dla bieżącego obiektu");
						$this->lowProgressCount = 0;
					}
				} else $this->lowProgressCount = 0;
				$this->lastProgress_slowCheck = $progress;
			}
		}

		if ((($this->step) % 100000) == 0) {
			$this->saveProgress(['progress' => $progress]);
		}

		if ((($this->step) % 10000) == 0) {
			if (memory_get_usage(true) > 3000000000) {
				$this->nodes = [];
				self::saveToLog("Oczyszczam podręczny cache - zajmuje zbyt dużo pamięci");
			}
		}

		if (isset($this->path[$ID])) return false;

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

		if (in_array($ID, $this->endNodes)) {
			if (count($this->path) > $this->minDepth) $this->addResult($progress);
			array_pop($this->path);
			return;
		}

		if (count($this->path) > $this->maxDepth) {
			if (!$this->onlyTargets) $this->addResult($progress);
			array_pop($this->path);
			return;
		}

		if (!isset($this->nodes[$ID][$relation])) {
			$where = $relation ? "and ({$relation} & ref.RELATION_ID) = 0" : "";
			$query = "{$this->query} = {$ID} {$where}";
			$this->nodes[$ID][$relation] = DB::getPDO()->fetchAll($query);
		}
		$nodes = array_values(array_filter($this->nodes[$ID][$relation], function ($node) {
			if (isset($this->path[$node['ID']])) return false;
			return true;
		}));

		$count = count($nodes);
		$weight += self::weightCalc($count);
		if ($weight <= $this->maxDepth) {
			foreach ($nodes as $i => $node) $this->findPowiazania($node['ID'], ($progress + ($i / ($count * $steps))), ($count * $steps), $node['RELATION_ID'], $weight);
		}

		array_pop($this->path);
	}

	private function sortResults() {
		if (!$this->results) return;
		uasort($this->results, function ($a, $b) {
			$ca = count($a);
			$cb = count($b);
			$c = min($ca, $cb);
			$ka = array_keys($a);
			$kb = array_keys($b);
			for ($i = 0; $i < $c; $i++) {
				if ($ka[$i] < $kb[$i]) return -1;
				if ($ka[$i] > $kb[$i]) return 1;
			}
			if ($ca < $cb) return -1;
			if ($ca > $cb) return 1;
			return 0;
		});
	}

	private function saveResults() {
		self::saveToLog("Zapisuję wyliczone dane do pliku");
//		if (!$this->results) {
//			self::saveToLog("Brak wyliczonych danych - niczego nie zapisaono");
//			return false;
//		}
		$dataFile = "{$this->tasksDirLocation}/generatePowiazania-{$this->ID}.data";
		$data = base64_encode(gzcompress(json_encode(['results' => $this->results, 'fidRow' => $this->fidRow])));
		file_put_contents($dataFile, $data);
		self::saveToLog("Zapisano wyliczone dane do pliku");
		return true;
	}

	private function loadResults() {
		self::saveToLog("Próbuję wczytać wcześniej wyliczone dane");
		$dataFile = "{$this->tasksDirLocation}/generatePowiazania-{$this->ID}.data";
		if (!file_exists($dataFile)) {
			self::saveToLog("Nie znaleziono pliku z wyliczonymi danymi");
			return false;
		}
		$data = file_get_contents($dataFile);
		$results = @json_decode(gzuncompress(base64_decode($data)), true);
		if (!$results) {
			self::saveToLog("Wystąpił błąd wczytywania wcześniej wyliczonych danych");
			return false;
		}
		if (isset($results['results'])) {
			$this->results = $results['results'];
			if (isset($results['fidRow'])) $this->fidRow = $results['fidRow'];
		} else $this->results = $results;
		self::saveToLog("Wczytano wcześniej wyliczone dane");

		return true;
	}

	private function relationName($ID) {
		if (!$ID) return '';
		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 splitResults() {
		$MAX = 1000;

		$countBySource = [];
		$resultsKeys = [];
		foreach ($this->results as $resultKey => $result) {
			$_keys = array_keys($result);
			$countBySource[reset($_keys)][] = $resultKey;
		}
		foreach ($countBySource as $resultKeys) {
			if (count($resultKeys) > $MAX) {
				$countByDest = [];
				foreach ($resultKeys as $resultKey) {
					$_keys = array_keys($this->results[$resultKey]);
					$countByDest[end($_keys)][] = $resultKey;
				}
				foreach ($countByDest as $resultKeys2) $resultsKeys = array_merge($resultsKeys, array_chunk($resultKeys2, $MAX));
			} else $resultsKeys[] = $resultKeys;
		}

		$results = [];
		while ($resultsKeys) {
			$resultKeys = array_shift($resultsKeys);
			while ($next = current($resultsKeys)) {
				if (count($resultKeys) + count($next) <= $MAX) {
					$resultKeys = array_merge($resultKeys, $next);
					unset($resultsKeys[key($resultsKeys)]);
				} else break; //next($resultsKeys);
			}
			$_results = [];
			foreach ($resultKeys as $key) $_results[$key] = $this->results[$key];
			$results[] = $_results;
//			$results[] = array_map(function ($resultKey) {return $this->results[$resultKey];}, $resultKeys);
		}

		if ($results) $this->splittedResults = $results;
		else $this->splittedResults[] = $this->results;
		$this->progress['summary']['reportsCount'] = count($results);
	}

	public function getPartsCount() {
		return count($this->splittedResults);
	}

	private function generateItemsResults($resultsPart = null) {
		if ($resultsPart === null) self::saveToLog("Generuję dane na potrzeby utworzenia pojedynczego pliku XML");
		else {
			self::saveToLog("Generuję dane na potrzeby utworzenia pliku XML ({$resultsPart})");
			if  ($resultsPart >= $this->getPartsCount()) throw new Exception("generateItemsResults() error - bad part number");
		}

		$xmlTask = "BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA";
		$xmlRow = "BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row";
		$xmlObject = "BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object";

		$items_kontrahenci = [];
		$items_kw_person = [];
		$kontrahenci = [];
		$kw_person = [];
		$_items_results = [];

		$refUmowyToKontrahenci = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_UMOWY', 'BI_audit_ENERGA_RUM_KONTRAHENCI', true);
		$refFakturyToKontrahenci = BiAuditRefTables::getRefTable('BI_audit_ENERGA_FAKTURY', 'BI_audit_ENERGA_RUM_KONTRAHENCI', true);
		$refKwRequestedToKwRequestedPerson = BiAuditRefTables::getRefTable('BI_audit_KW_requested', 'BI_audit_KW_requested_person', true);

		$this->items_results[$resultsPart] = [];
		$this->items_results[$resultsPart]['@attributes'] = ['xmlns:system_cache__dita' => 'http://biuro.biall-net.pl/xmlschema_procesy5/default_db_xml_cache/dita.xsd'];
		$this->items_results[$resultsPart][$xmlTask]['@attributes'] = ['fid' => "{$xmlTask}.{$this->ID}"];

		if ($resultsPart === null) {
			$results = $this->results;
		} else {
			$results = (!empty($this->splittedResults[$resultsPart])) ? $this->splittedResults[$resultsPart] : [];
			$this->items_results[$resultsPart][$xmlTask]['@attributes']['system_cache__dita:part'] = ($resultsPart + 1);
		}

		foreach ($results as $key => $result) {
			if ($this->fidRow) $item_results = ['@attributes' => ['fid' => "{$xmlRow}.{$this->fidRow[$key]['fid']}"]];
			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 = [];
				if ($this->fidRow) {
					$item_result['@attributes'] = ['fid' => "{$xmlObject}.{$this->fidRow[$key]['obj'][$ID]}"];
				}
				$item_result[$row['REMOTE_TABLE']] = array_merge(['@attributes' => ['fid' => "{$row['REMOTE_TABLE']}.{$row['REMOTE_ID']}"]], (array) $obj);

				if ($refTable = BiAuditRefTables::getRefTable('BI_audit_operational_raport_note', $row['REMOTE_TABLE'], false)) {
					$query = "select `note`.* from `BI_audit_operational_raport_note` `note` join `{$refTable}` `ref` on `note`.`ID` = `ref`.`PRIMARY_KEY` where `ref`.`REMOTE_PRIMARY_KEY` = '{$row['REMOTE_ID']}'";
					if ($notes = DB::getPDO()->fetchAll($query)) $item_result[$row['REMOTE_TABLE']]['BI_audit_operational_raport_note'] = $notes;
				}

				if ($refTable = BiAuditRefTables::getRefTable($row['REMOTE_TABLE'], 'BI_audit_taxpayer', false)) {
					$query = "select `taxpayer`.* from `{$refTable}` `ref` join `BI_audit_taxpayer` `taxpayer` on `ref`.`REMOTE_PRIMARY_KEY` = `taxpayer`.`ID` where `ref`.`PRIMARY_KEY` = '{$row['REMOTE_ID']}'";
					if ($taxpayer = DB::getPDO()->fetchFirst($query)) {
						$item_result[$row['REMOTE_TABLE']]['BI_audit_taxpayer'] = array_merge(['@attributes' => ['fid' => "BI_audit_taxpayer.{$taxpayer['ID']}"]], $taxpayer);
					}
				}

				if ($rel) $item_result['relation_from'] = $this->relationName($rel);
				$item_results[$xmlObject][] = $item_result;

				if ($row['REMOTE_TABLE'] == "BI_audit_ENERGA_RUM_KONTRAHENCI" && (!in_array($row['REMOTE_ID'], $kontrahenci))) {
					$kontrahenci[] = $row['REMOTE_ID'];

					$query = "select faktury.* from BI_audit_ENERGA_FAKTURY faktury join `{$refFakturyToKontrahenci}` ref on faktury.ID = ref.PRIMARY_KEY where ref.REMOTE_PRIMARY_KEY = '{$row['REMOTE_ID']}'";
					$res = DB::query($query);
					$faktury = [];
					if (mysql_num_rows($res)) {
						while ($obj = DB::fetch($res)) {
							$faktura = (array) $obj;
							$faktura = array_merge(['@attributes' => ['fid' => "BI_audit_ENERGA_FAKTURY.{$faktura['ID']}"]], $faktura);
							$faktury[] = $faktura;
						}
					}

					$query = "select umowy.* from BI_audit_ENERGA_RUM_UMOWY umowy join `{$refUmowyToKontrahenci}` ref on umowy.ID = ref.PRIMARY_KEY where ref.REMOTE_PRIMARY_KEY = '{$row['REMOTE_ID']}'";
					$res = DB::query($query);
					$umowy = [];
					if (mysql_num_rows($res)) {
						while ($obj = DB::fetch($res)) {
							$umowa = (array) $obj;
							$umowa = array_merge(['@attributes' => ['fid' => "BI_audit_ENERGA_RUM_UMOWY.{$umowa['ID']}"]], $umowa);
							$umowy[] = $umowa;
						}
					}

					if ($faktury || $umowy) {
						$item_kontrahenci['@attributes'] = ['fid' => "{$row['REMOTE_TABLE']}.{$row['REMOTE_ID']}"];
						$item_kontrahenci['ID'] = $row['REMOTE_ID'];
						if ($faktury) $item_kontrahenci['BI_audit_ENERGA_FAKTURY'] = $faktury;
						if ($umowy) $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 `{$refKwRequestedToKwRequestedPerson}` ref on kw.ID = ref.PRIMARY_KEY where ref.REMOTE_PRIMARY_KEY = '{$row['REMOTE_ID']}'";
					$res = DB::query($query);
					if (mysql_num_rows($res)) {
						$kws = [];
						while ($obj = DB::fetch($res)) {
							$kw = (array) $obj;
							$kw = array_merge(['@attributes' => ['fid' => "BI_audit_KW_requested.{$kw['ID']}"]], $kw);
							$kws[] = $kw;
						}
						$item_kw_person['@attributes'] = ['fid' => "{$row['REMOTE_TABLE']}.{$row['REMOTE_ID']}"];
						$item_kw_person['ID'] = $row['REMOTE_ID'];
						$item_kw_person['BI_audit_KW_requested'] = $kws;
						$items_kw_person[] = $item_kw_person;
					}
				}
			}
			$this->items_results[$resultsPart][$xmlTask][$xmlRow][] = $item_results;
		}
		$this->items_results[$resultsPart][$xmlTask]['BI_audit_ENERGA_RUM_KONTRAHENCI'] = $items_kontrahenci;
		$this->items_results[$resultsPart][$xmlTask]['BI_audit_KW_requested_person'] = $items_kw_person;
		self::saveToLog("Wygenerowano dane na potrzeby utworzenia pliku XML ({$resultsPart})");
	}

	private function saveToDb($result = null, $key = null) {
		if ($result === null) {
			self::saveToLog("Zapisuję wyliczone dane do bazy");
			if (!$this->results) {
				self::saveToLog("Brak wyliczonych danych - nie zapisaono niczego do bazy");
				return null;
			}
			foreach ($this->results as $key => $result) $this->saveToDb($result, $key);
			self::saveToLog("Zapisano wyliczone dane do bazy");
		} else {
			if ($key === null) {
				end($this->results);
				$key = key($this->results);
			}
			$refPowiazaniaToPowiazaniaRow = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', 'BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row', true);
			$refPowiazaniaRowToPowiazaniaRowObject = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row', 'BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object', true);
			$IdPowiazaniaRow = DB::getPDO()->insert('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row', []);
			$fidObj = [];
			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' => $this->relationName($rel)]);
				DB::getPDO()->insert($refPowiazaniaRowToPowiazaniaRowObject, ['PRIMARY_KEY' => $IdPowiazaniaRow, 'REMOTE_PRIMARY_KEY' => $idPowiazaniaRowObject]);
				$ref = BiAuditRefTables::getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object', $object['REMOTE_TABLE'], true);
				DB::getPDO()->insert($ref, ['PRIMARY_KEY' => $idPowiazaniaRowObject, 'REMOTE_PRIMARY_KEY' => $object['REMOTE_ID']]);
				$fidObj[$ID] = $idPowiazaniaRowObject;
			}
			$this->fidRow[$key] = [
				'fid' => $IdPowiazaniaRow,
				'obj' => $fidObj,
			];
		}
	}

	public function saveXml($resultsPart = null) {
		if ($this->getPartsCount()) {
			if ($resultsPart === null) {
				self::saveToLog("Generuję pliki XML (liczba plików do wygenerowania: {$this->getPartsCount()})");
				for ($i =0; $i < $this->getPartsCount(); $i++) {
					$partNo = str_pad($i + 1, 6, '0', STR_PAD_LEFT);
					$xmlFile = "{$this->tasksDirLocation}/generatePowiazania-{$this->ID}_{$partNo}.xml";
					file_put_contents($xmlFile, $this->asXml($i));
				}
				self::saveToLog("Zakończono generowanie plików XML");
			} else {
				if ($resultsPart >= $this->getPartsCount()) {
					self::saveToLog("saveXml() error - bad result number");
					return;
				}
				$partNo = str_pad($resultsPart + 1, 6, '0', STR_PAD_LEFT);
				$xmlFile = "{$this->tasksDirLocation}/generatePowiazania-{$this->ID}_{$partNo}.xml";
				file_put_contents($xmlFile, $this->asXml($resultsPart));
			}
		} else {
			$xmlFile = "{$this->tasksDirLocation}/generatePowiazania-{$this->ID}.xml";
			file_put_contents($xmlFile, $this->asXml());
		}
	}

	public function asXml($resultsPart = null) {
		self::saveToLog("Generuję plik XML ({$resultsPart})");
		if (!$this->results) {
			self::saveToLog("Brak wyników (#1), wygenerowano pusty plik XML ({$resultsPart})");
//			self::saveToLog("Brak wyników (#1), nie wygenerowano pliku XML ({$resultsPart})");
//			return null;
		}
		if (!isset($this->items_results[$resultsPart])) $this->generateItemsResults($resultsPart);
		if (!isset($this->items_results[$resultsPart])) {
			self::saveToLog("Brak wyników (#2), nie wygenerowano pliku XML ({$resultsPart})");
			return null;
		}
		$xmlRoot = "RelatedFeatureRoot";
		$return = V::arrayToXML($this->items_results[$resultsPart], true, $xmlRoot);
		self::saveToLog("Wygenerowano plik XML ({$resultsPart})");
		return $return;
	}

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

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

	public function generatePdfAndHtml() {
		$antDir = "/Library/Server/Web/Data/Sites/Default/SE/schema/WPS_Functions/default_db/recurse_ant/out";
		if (!file_exists($antDir)) mkdir($antDir, 0770, true);
		if (!is_dir($antDir)) {
			$sqlArr['FILE_STATUS_info'] .= ", ale nie udało się utworzyć plików PDF i HTML";
			self::saveToLog("Nie wygenerowano plików PDF oraz HTML - problem z utworzeniem katalogu dla ant ({$resultsPart})");
			$this->saveProgress(['reportsCreated' => 1]);
			return;
		}

		$this->saveProgress(['step' => 'reports']);
		if ($this->getPartsCount()) {
			self::saveToLog("Generuję raporty w plikach PDF oraz HTML (liczba raportów do wygenerowania: {$this->getPartsCount()})");
			for ($i =0; $i < $this->getPartsCount(); $i++) $this->_generatePdfAndHtml($i);
			self::saveToLog("Zakończono generowanie raportów w plikach PDF oraz HTML");
		} else $this->_generatePdfAndHtml();
	}

	private function _generatePdfAndHtml($resultsPart = null) {
		$id_part = $resultsPart === null ? $this->ID : $this->ID . "_" . str_pad($resultsPart + 1, 6, '0', STR_PAD_LEFT);
		self::saveToLog("Generuję raport w pliku PDF oraz HTML ({$resultsPart})");
		$xmlFile = "{$this->tasksDirLocation}/generatePowiazania-{$id_part}.xml";
		$pdfDestFile = "{$this->tasksDirLocation}/generatePowiazania-{$id_part}.pdf";
		$htmlDestDir = "{$this->tasksDirLocation}/generatePowiazania-{$id_part}.html";
		$htmlZipDestFile = "{$this->tasksDirLocation}/generatePowiazania-{$id_part}.zip";
		$antDir = "/Library/Server/Web/Data/Sites/Default/SE/schema/WPS_Functions/default_db/recurse_ant/out";
		$antXmlFilename = "relations-{$id_part}.out_task.dita.final_with_RelatedFeatureLoop.xml";
		$antXmlFile = $antDir . "/" . $antXmlFilename;
		//$baseDir = "/Library/Server/Web/Data/Sites/Default/SE/schema/WPS_Functions/default_db/CRM_PROCES_tree/temp/relations-{$id_part}";
		$baseDir = "/opt/local/pl.procesy5/p5build_SE/temp/WPS_Functions/temp/default_db/CRM_PROCES_tree/relations-{$id_part}";
		$ditamapFile = "{$baseDir}/relations-{$id_part}.ditamap";
		$pdfFile = "{$baseDir}/pdf/relations-{$id_part}.pdf";
		$htmlDir = "{$baseDir}/html";
		$htmlFile = "{$htmlDir}/relations-{$id_part}.html";

		//if (!file_exists($xmlFile))
			$this->saveXml($resultsPart);
		if (!file_exists($xmlFile)) {
			$sqlArr['FILE_STATUS_info'] .= ", ale nie udało się utworzyć plików XML, PDF i HTML";
			self::saveToLog("Nie wygenerowano plików XML, PDF oraz HTML - problem z wygenerowaniem pliku XML ({$resultsPart})");
			$this->saveProgress(['reportsCreated' => 1]);
			return;
		}
		 $this->saveProgress(['reportsCreated' => 0.05]);

		copy($xmlFile, $antXmlFile);
		self::saveToLog("Generuję plik przejściowy do wygenerowania plików PDF i HTML ({$resultsPart})");
		self::exec("cd /Library/Server/Web/Data/Sites/Default/SE/schema/WPS_Functions/default_db/CRM_PROCES_tree && /opt/local/bin/ant -f build_CRM_PROCES_tree.xml default_db:PROCES_INIT:tree:dita -Duuid=relations-{$id_part}");
		if (file_exists($ditamapFile))
		{
			self::saveToLog("Wygenerowano plik przejściowy");
			$this->saveProgress(['reportsCreated' => 0.15]);
		} else {
			self::saveToLog("Nie udało się wygenerować pliku przejściowego, przerywm");
			$this->saveProgress(['reportsCreated' => 1]);
			return;
		}

		self::saveToLog("Generuję plik PDF ({$resultsPart})");
		self::exec("cd \"{$baseDir}\" && /Library/Server/Web/Data/Sites/Default/SE/stuff/dita-ot-2.3.3/bin/dita -o pdf -i relations-{$id_part}.ditamap -f pdf");
		if (file_exists($pdfFile)) {
			rename($pdfFile, $pdfDestFile);
			self::saveToLog("Wygenerowano plik PDF ({$resultsPart})");
		} else self::saveToLog("Nie udało się wygenerować pliku PDF ({$resultsPart})");
		$this->saveProgress(['reportsCreated' => 0.45]);

		self::saveToLog("Generuję pliki HTML ({$resultsPart})");
		self::exec("cd \"{$baseDir}\" && /Library/Server/Web/Data/Sites/Default/SE/stuff/dita-ot-2.3.3/bin/dita -o html -i relations-{$id_part}.ditamap -f tocjs");
		if (file_exists($htmlDir) && is_dir($htmlDir) && file_exists($htmlFile)) {
			self::exec("cd \"{$baseDir}\" && zip -r \"{$htmlZipDestFile}\" html");
			if (file_exists($htmlDestDir)) self::exec("rm -rf \"{$htmlDestDir}\"");
			self::exec("mv \"{$htmlDir}\" \"{$htmlDestDir}\"");
			self::saveToLog("Wygenerowano pliki HTML ({$resultsPart})");
		} else self::saveToLog("Nie udało się wygenerować plików HTML {$resultsPart})");
		$this->saveProgress(['reportsCreated' => 0.35]);
	}
}