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;

	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" 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 = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA',
			'default_db__x3A__BI_audit_ENERGA_PRACOWNICY:BI_audit_ENERGA_PRACOWNICY');
		foreach ($prID as $v) DB::getPDO()->insert($refPowiazaniaToPracownicy, ['PRIMARY_KEY' => $powiazanieID, 'REMOTE_PRIMARY_KEY' => $v]);

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

		$refPowiazaniaToKwRequestedPreson = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA',
			'default_db__x3A__BI_audit_KW_requested_person:BI_audit_KW_requested_person');
		foreach ($requestedPersons as $requestedPerson) DB::getPDO()->insert($refPowiazaniaToKwRequestedPreson, ['PRIMARY_KEY' => $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_([[: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_([[: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[1], '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 = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA',
			'default_db__x3A__BI_audit_ENERGA_PRACOWNICY:BI_audit_ENERGA_PRACOWNICY');
		$query = "select `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":
					$this->importKrsToPracownicySearch($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 importKrsToPracownicy() {
		$action = V::get('action', '', $_POST);
		switch ($action) {
			case "search":
				$this->importKrsToPracownicySearch();
				break;
			default: $this->importKrsToPracownicyForm();
		}
	}

	private function importKrsToPracownicyForm() {
?>
<div class="container" style="margin-top:20px">
  <legend>
    Importowanie podmiotów z KRS 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 importKrsToPracownicySearch($krsId = null) {
		$formItems = [
			"nazwa" => 'like',
			"krs" => '=',
			"nip" => '=',
			"regon" => '=',
		];

		try {
			$subaction = V::get('subaction', '', $_POST);
			switch ($subaction) {
				case "listKrsPerson":
					$krsId = V::get('krsId', 0, $_POST, int);
					break;
				case "addKrsPersonToPracownicy":
					$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 BI_audit_KRS_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 "addKrsToKontrahenci":
					$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 BI_audit_KRS 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 KRS', nazwa, regon, nip, krs from BI_audit_KRS 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->importKrsToPracownicyForm();
			return;
		}

		$query = "select * from `BI_audit_KRS` 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->importKrsToPracownicyForm();
			return;
		} elseif (count($result) == 1) {
			$krsId = $result[0]['ID'];
		}
?>
<div class="container" style="margin-top:20px">
  <form method="post">
    <legend>
      Importowanie podmiotów z KRS 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="listKrsPerson" 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 BI_audit_KRS 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);

			$refKrsToKrsPerson = ACL::getRefTable('default_db/BI_audit_KRS/BI_audit_KRS', "default_db__x3A__BI_audit_KRS_person:BI_audit_KRS_person");
			$query = "select person.*, pracownicy.ID as pracownicyId from `{$refKrsToKrsPerson}` ref join `BI_audit_KRS_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="addKrsToKontrahenci"<?=($kontrahentExists ? " disabled" : "")?>>Dodaj firmę do tabeli kontrahentów</button>
        <button type="submit" class="btn btn-primary" name="subaction" value="addKrsPersonToPracownicy" 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 = ACL::getRefTable('default_db/BI_audit_KRS/BI_audit_KRS', "default_db__x3A__BI_audit_KRS_person:BI_audit_KRS_person");
		$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
	}

	public function defaultAction() {
		SE_Layout::gora();
		SE_Layout::menu();
		if (isset($_SESSION['REFERER'])) {
			$this->REFERER = $_SESSION['REFERER'];
			unset($_SESSION['REFERER']);
		} elseif (V::get('REFERER', '', $_POST) != '') $this->REFERER = V::get('REFERER', '', $_POST);
		else $this->REFERER=$_SERVER['HTTP_REFERER'];
		if (($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_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)) {
			switch ($TABLE) {
				case "default_db/BI_audit_ENERGA_PRACOWNICY":
					$this->importKrsToPracownicy();
					break;
				default: SE_Layout::alert('danger', 'Błąd parametru');
			}
		}
		elseif(($ID = V::get('doReloadCache', 0, $_GET, 'string')) == 'full')  $this->doReloadCache('full');
		elseif(($ID = V::get('doReloadCache', 0, $_GET, 'string'))) $this->doReloadCache();
		else SE_Layout::alert('danger', 'Błąd parametru');
		SE_Layout::dol();
	}

	private static function getRefsTables($namespace) {
		try {
			$item = SchemaFactory::loadDefaultObject('SystemObject')->getItem($namespace, [ 'propertyName' => '*,field' ]);
			if (!($item['hasStruct'] && $item['isStructInstalled'] && $item['isObjectActive'])) throw new Exception("Object error");
			$fields = $item['field'];

			$refsTables = [];
			foreach ($fields as $field) {
				list($type, $child) = explode(":", $field['xsdType'], 2);
				if ($type == "ref" && $field['idZasob'] && $field['isActive'] && (!$field['isLocal'])) $refsTables[] = ACL::getRefTable($namespace, $child);
			}
			return $refsTables;
		} catch (Exception $e) {
//			echo "{$namespace} - {$e->getMessage}";
			return [];
		}
	}

	private static function truncatePowiazaniaFromDB($ID) {
		$refsTables = self::getRefsTables('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA');
		if (!$refsTables) return null;
		$refPowiazaniaToPowiazaniaRow = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA',
			'default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row:BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row');
		$refsTables = array_diff($refsTables, [$refPowiazaniaToPowiazaniaRow]);

		$query = "delete from `" . implode("`, `", $refsTables) . "` using `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA`";
		foreach ($refsTables 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 = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA',
			'default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row:BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row');
		$refPowiazaniaRowToPowiazaniaRowObject = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row/BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row',
			'default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object:BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object');
		$refsTables = self::getRefsTables('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 ($refsTables 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("log_errors", "1");
ini_set("error_log", "/var/log/apache2/error_log");

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

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

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


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


			### Ustawienie zmiennych
			$powiazaniaDirLocation = 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, 0755, true);
			if (!file_exists($tasksDirLocation)) throw new Exception('Error during creating temporary directory.');


			### Wyłączenie wcześniej używanego mechanizmu wymuszania częściowego 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') 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));
			}


			### 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));
					}
				}

			}


			### 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' => 'W trakcie generowania powiązań',
					];
					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, 0777, false);
						if (!file_exists($powiazanieDirLocation)) $error = "Nie udało się wgrać pliku xml - nie można utworzyć katalogu dla rekordu";
						else $success = true;
					} else $error = "Wystąpił błąd podczas generowania powiązań ({$generatePowiazaniaResult['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_{$partNo}.xml";
							$destPdfFile = "{$powiazanieDirLocation}/relations_{$partNo}.pdf";
							$destHtmlDir = "{$powiazanieDirLocation}/html_{$partNo}";
                                        	        $destHtmlZipFile = "{$powiazanieDirLocation}/html_{$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.xml";
						$destPdfFile = "{$powiazanieDirLocation}/relations.pdf";
						$destHtmlDir = "{$powiazanieDirLocation}/html";
						$destHtmlZipFile = "{$powiazanieDirLocation}/html.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() { //TODO
		$fields = [
			'BI_audit_ENERGA_PRACOWNICY' => [
				'PERNR',
				'imiona',
				'nazwisko',
				'pesel',
				'nr_konta',
				'jednostka_organizacyjna',
				'nip',
				'regon',
				'source',
			],
			'BI_audit_ENERGA_PRACOWNICY_adresy' => [
				'kodPocztowy',
				'miejscowosc',
				'ulica',
				'nrBudynku',
				'nrLokalu',
			],
		];

		$query = "select * from `BI_audit_ENERGA_PRACOWNICY_import` where `importResult` = 'none'";
		$pracownicy = DB::getPDO()->query($query);
		while ($pracownik = $pracownicy->fetch()) {
			$ID_pracownik = DB::getPDO()->fetchValue("select `ID` from `BI_audit_ENERGA_PRACOWNICY` where `pesel` = '{$pracownik['pesel']}'");
			$sqlArr = [];
			foreach ($fields['BI_audit_ENERGA_PRACOWNICY'] as $field) $sqlArr[$field] = trim($pracownik[$field]);
			if ($ID_pracownik) DB::getPDO()->update('BI_audit_ENERGA_PRACOWNICY', 'ID', $ID, $sqlArr);
			else $ID_pracownik = DB::getPDO()->insert('BI_audit_ENERGA_PRACOWNICY', $sqlArr);

			$sqlArr = [];
			foreach ($fields['BI_audit_ENERGA_PRACOWNICY_adresy'] as $field) {
				$sqlArr[$field] = trim($pracownik[$field]);
				if (!$sqlArr[$field]) unset($sqlArr[$field]);
			}
			if (!$sqlArr) break;
			$sql = [];
//			foreach ($fields['BI_audit_ENERGA_PRACOWNICY_adresy'] as $field) {
//				if (isset($sqlArr[$field])) $sql[] = "`{$field}` = " . DUPA;
//			}
		}
	}

	private static function reloadCache_parseUmowy($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' => [
				'"Zaimportowanoz KRS na podstawie 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 umowy"',
				'firma',
				'nip',
				'regon',
				'null',
				'kraj',
				'kodPocztowy',
				'miejscowosc',
				'ulica',
				'budynek',
				'lokal',
				'TERYT_SYM',
				'TERYT_SYM_UL',
			],
		];

		$refTable = ACL::getRefTable('default_db/BI_audit_ENERGA_RUM_UMOWY/BI_audit_ENERGA_RUM_UMOWY', 'default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI:BI_audit_ENERGA_RUM_KONTRAHENCI');
		if ($full) {
			DB::getPDO()->query("truncate table `{$refTable}`");
			DB::getPDO()->query("update `BI_audit_ENERGA_RUM_UMOWY` set `A_STATUS` = 'WAITING' where `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`, `Strony_umowy` from `BI_audit_ENERGA_RUM_UMOWY` where `A_STATUS` = 'WAITING'";
		$result = DB::getPDO()->fetchAll($query);
		foreach ($result as $row) {
			$ID = $row['ID'];
			$strony = array_map('trim', explode("|", $row['Strony_umowy']));
			$K_IDs = [];

			foreach ($strony as $item) {
				$strona = array_map('trim', explode(";", $item));
				$nr = null;
				if (count($strona) == 3) $i = 1;//$nr = $strona[1];
				elseif (count($strona) == 4) $i = 2;//$nr = $strona[2];
				elseif (count($strona) >= 5 && count($strona) <= 6) $i = 3;//$nr = $strona[3];
				else $i = null;
				if ($i) $nr = $strona[$i];
				$notFound = false;
				if (is_numeric($nr)) {
					$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI where Numer_kontrahenta = '{$nr}'";
					if ($K_ID = DB::getPDO()->fetchValue($query)) {
						$K_IDs[] = $K_ID;
					} else {
						$nazwa = $strona[$i+1];
						$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI where Pelna_nazwa_kontrahenta = '" . addslashes($nazwa) . "'";
						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}'";
							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}'";
								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 `{$refTable}` where `PRIMARY_KEY` = {$ID} and `REMOTE_PRIMARY_KEY` = {$K_ID}"))) {
					DB::getPDO()->query("insert into `{$refTable}` (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}'");
			}
		}
	}

	private static function reloadCache_reTeryt($full = false) {

		Lib::loadClass('Teryt');

		$tables = [
			'BI_audit_CEIDG' => [
				'fields' => [
					'wojewodztwo' => 'wojewodztwo',
					'powiat' => 'powiat',
					'gmina' => 'gmina',
					'miejscowosc' => 'miejscowosc',
					'ulica' => 'ulica',
					'kodPocztowy' => 'kodPocztowy',
				],
				'full' => false,
			],
			'BI_audit_CEIDG_pelnomocnicy' => [
				'fields' => [
					'wojewodztwo' => 'wojewodztwo',
					'powiat' => 'powiat',
					'gmina' => 'gmina',
					'miejscowosc' => 'miejscowosc',
					'ulica' => 'ulica',
					'kodPocztowy' => 'kodPocztowy',
				],
				'full' => false,
			],
			'BI_audit_ENERGA_PRACOWNICY' => [
				'fields' => [
					'miejscowosc' => 'miejscowosc',
					'kodPocztowy' => 'kodPocztowy',
					'ulica' => 'ulica',
				],
				'full' => true,
			],
			'BI_audit_ENERGA_PRACOWNICY_adresy' => [
				'fields' => [
					'kodPocztowy' => 'kodPocztowy',
					'miejscowosc' => 'miejscowosc',
					'ulica' => 'ulica',
				],      
				'full' => true,
			],
			'BI_audit_ENERGA_RUM_KONTRAHENCI' => [
				'fields' => [
					'kodPocztowy' => 'Kod_pocztowy',
					'miejscowosc' => 'Miejscowosc',
					'ulica' => 'Ulica',
				],
				'full' => true,
			],
			'BI_audit_KRS' => [ 
				'fields' => [
					'wojewodztwo' => 'S_wojewodztwo',
					'powiat' => 'S_powiat',
					'gmina' => 'S_gmina',
					'miejscowosc' => 'A_miejscowosc',
					'ulica' => 'A_ulica',
					'kodPocztowy' => 'A_kod',
				],
				'full' => false,
			],
			'BI_audit_KRS_address' => [
				'fields' => [
					'wojewodztwo' => 'S_wojewodztwo',
					'powiat' => 'S_powiat',
					'gmina' => 'S_gmina',
					'miejscowosc' => 'A_miejscowosc',
					'ulica' => 'A_ulica',
					'kodPocztowy' => 'A_kod',
				],
				'full' => false,
			],
			'BI_audit_MSIG_address' => [
				'fields' => [
					'wojewodztwo' => 'S_wojewodztwo',
					'powiat' => 'S_powiat',
					'gmina' => 'S_gmina',
					'miejscowosc' => 'A_miejscowosc',
					'ulica' => 'A_ulica',
					'kodPocztowy' => 'A_kod',
				],
				'full' => false,
			],
		];

		foreach ($tables as $table => $tableConf) {
			$where = ($full && $tableConf['full']) ? "" : "where `TERYT_SYM` is null and `cached` = 0";
			$query = "select * from `{$table}` {$where}";
			try {
				$adresy = DB::getPDO()->query($query);
				while ($adres = $adresy->fetch()) {
					$search = [];
					foreach ($tableConf['fields'] as $field => $param) if (!empty($adres[$param])) $search[$field] = $adres[$param];
					try {
						$teryt = Teryt::search($search);
						if (!empty($teryt['SYM'])) {
							$sqlArr = ['TERYT_SYM' => $teryt['SYM']];
							$sqlArr['TERYT_SYM_UL'] = (isset($teryt['SYM_UL']) ? $teryt['SYM_UL'] : 0);
							DB::getPDO()->update($table, "ID", $adres['ID'], $sqlArr);
						}
					} catch (Exception $e) {
					}
				}
			} catch (Exception $e) {
			}
		}
	}

	private static function reloadCache_updateAll($full, $onlyBase) {
		$tablesConf = [
			'BI_audit_CEIDG' => [
				'fields' => [
					'nip' => 'nip',
					'regon' => 'regon',
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'budynek',
					'nrLokalu' => 'lokal',
				],
				'base' => '1',
			],
			'BI_audit_CEIDG_pelnomocnicy' => [
				'fields' => [
					'nip' => 'nip',
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'budynek',
					'nrLokalu' => 'lokal',
				],
				'base' => '1',
			],
			'BI_audit_CEIDG_powiazania' => [
				'fields' => [
					'nip' => 'nip',
					'regon' => 'regon',
				],
				'base' => '1',
			],
			'BI_audit_ENERGA_PRACOWNICY' => [
				'fields' => [
					'nip' => 'nip',
					'regon' => 'regon',
					'pesel' => 'pesel',
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'nr',
					'nrLokalu' => 'nrLokalu',
				],
				'base' => '0',
				//'where' => '',
			],
			'BI_audit_ENERGA_PRACOWNICY_adresy' => [
				'fields' => [
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'nrBudynku',
					'nrLokalu' => 'nrLokalu',
				],
				'base' => '0',
				//'where' => '',
			],
                        
			'BI_audit_ENERGA_RUM_KONTRAHENCI' => [
				'fields' => [
					'krs' => 'KRS',
					'nip' => 'NIP',
					'regon' => 'REGON',
					'pesel' => 'PESEL',
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'Numer_budynku',
					'nrLokalu' => 'Numer_mieszkania_lokalu',
				],
				'base' => '0',
				'where' => "and ownCompany != 'Y'",
			],
			'BI_audit_KRS' => [
				'fields' => [
					'krs' => 'krs',
					'nip' => 'nip',
					'regon' => 'regon',
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'A_nrDomu',
					'nrLokalu' => 'A_nrLokalu',
				],
				'base' => '1',
			],
			'BI_audit_KRS_address' => [
				'fields' => [
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'A_nrDomu',
					'nrLokalu' => 'A_nrLokalu',
				],
				'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' => [
					'TERYT_SYM' => 'TERYT_SYM',
					'TERYT_SYM_UL' => 'TERYT_SYM_UL',
					'nrDomu' => 'A_nrDomu',
					'nrLokalu' => 'A_nrLokalu',
				],
				'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' => '',
//			],
		];

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

		];
		
		$RefExtraTables = [
								'BI_audit_MSIG_person' => [
										'refTablesTo' => [
												'BI_audit_KRS_person','BI_audit_MSIG','BI_audit_CEIDG','BI_audit_ENERGA_PRACOWNICY',
														 ],
								],					
								'BI_audit_MSIG' => [
											'refTablesTo' => [
												'BI_audit_KRS','BI_audit_ENERGA_RUM_KONTRAHENCI',
											],
								],
								'BI_audit_MSIG_address' => [
									'refTablesTo' => [
										'BI_audit_KRS_address', 'BI_audit_CEIDG', 'BI_audit_ENERGA_PRACOWNICY_adresy','BI_audit_MSIG_address',
										],
								
								],	
								'BI_audit_KRS_person' => [
									'refTablesTo' => [
										'BI_audit_KRS','BI_audit_ENERGA_PRACOWNICY','BI_audit_MSIG_person',
										],
								],	
								'BI_audit_MSIG_company' => [
									'refTablesTo' => [
										'BI_audit_MSIG',
										],
								],	
								'BI_audit_KRS' => [
									'refTablesTo' => [
										'BI_audit_MSIG', 'BI_audit_ENERGA_RUM_KONTRAHENCI',
										],
								],	
								'BI_audit_CEIDG' => [
									'refTablesTo' => [
										'BI_audit_ENERGA_PRACOWNICY', 'BI_audit_ENERGA_RUM_KONTRAHENCI',
										],
								],	
								
								
							];
		
		$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[] = "delete from BI_audit_ALL_ref where BASE = 0";

			foreach ($refTablesConf as $refTableFrom => $refTableConf) {
				if ($refTableConf['base']) continue;
				foreach ($refTableConf['refTablesTo'] as $refTableTo) {
					$refTable = ACL::getRefTable("default_db/{$refTableFrom}/{$refTableFrom}", "default_db__x3A__{$refTableTo}:{$refTableTo}");
					$queries[] = "update `{$refTable}` set `A_STATUS` = 'WAITNG' where `A_STATUS` = 'NORMAL'";
					$queryes[] = "delete from `{$refTable}` where `TRANSACTION_ID` = '-1'";
				}
			}
			

			
		}

		foreach ($tablesConf as $table => $tableConf) {
			if ($onlyBase && (!$tableConf['base'])) continue;
			$where = "where `cached` = 0";
			if (!empty($tableConf['where'])) $where .= " {$tableConf['where']}";
//			$queries[] = "lock tables `{$table}` write";
			$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}";
			$queries[] = "update `{$table}` set `cached` = 1 {$where}";
//			$queries[] = "unlock tables";
		}

		$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[] = "update `BI_audit_ALL` set `TERYT_SYM` = null where `TERYT_SYM` = 0";
		$queries[] = "update `BI_audit_ALL` set `TERYT_SYM_UL` = null where `TERYT_SYM_UL` = 0";
		$queries[] = "update `BI_audit_ALL` set `nrDomu` = null where trim(`nrDomu`) = ''";
		$queries[] = "update `BI_audit_ALL` set `nrLokalu` = null where trim(`nrLokalu`) = ''";

		$queries[] = "update `BI_audit_ALL` set `nrDomu` = substring(`nrDomu`, 1, position('/' in `nrDomu`) - 1), `nrLokalu` = substring(`nrDomu`, position('/' in `nrDomu`) + 1) where `reffed` = 0 and `nrDomu` rlike '^[[:digit:]]+.*/[[:digit:]]+.*$' and `nrLokalu` is null";

		$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 = ACL::getRefTable("default_db/{$refTableFrom}/{$refTableFrom}", "default_db__x3A__{$refTableTo}:{$refTableTo}");
//				$queries[] = "lock tables `{$refTable}` write";
				$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`";
				$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'";
//				$queries[] = "unlock tables";
			}
		}
//		echo implode(";\n", $queries) . ";\n";
		foreach ($queries as $query) {
			echo "
			#1842 echo base queries SQL: {$query}\n";
			error_log("\n #1842 error base queries SQL: ".$query);
			DB::getPDO()->query($query);
		}

		$BiAuditRelations = new BiAuditRelations();
//		DB::getPDO()->query("lock tables `BI_audit_ALL` write");
		$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");
//		DB::getPDO()->query("unlock tables");
		
		$queries = array();
		//uzupelnienie extra refow dla dzialania schematow
		foreach ($RefExtraTables as $refTableFrom => $refTableConf) {
					foreach ($refTableConf['refTablesTo'] as $refTableTo) {
						echo "
						#1858 looking for ACL::getRefTable {$refTableFrom} to {$refTableTo}";
					$refTable = ACL::getRefTable("default_db/{$refTableFrom}/{$refTableFrom}", "default_db__x3A__{$refTableTo}:{$refTableTo}");
						echo "
						#1860 found getRefTable={$getRefTable}";
					$queries[] = "update `{$refTable}` set `A_STATUS` = 'WAITNG' "; //where `A_STATUS` = 'NORMAL'
					$queries[] = "delete from `{$refTable}` where `TRANSACTION_ID` = '0' and `A_STATUS` = 'NORMAL' "; //-1 nie moze byc
					$queries[] = " insert into `{$refTable}` (
									  `PRIMARY_KEY` ,
									  `REMOTE_PRIMARY_KEY`
									) 
									 select 
										LT.REMOTE_ID as PRIMARY_KEY, RT.REMOTE_ID as REMOTE_PRIMARY_KEY 
										from BI_audit_ALL as LT  
										left join BI_audit_ALL_ref as LT_ref on ( LT_ref.ID1=LT.ID ) 
										left join BI_audit_ALL as RT on (  LT_ref.ID2 = RT.ID  )
									 where 
										LT.REMOTE_TABLE='{$refTableFrom}' 
										and RT.REMOTE_TABLE='{$refTableTo}' ";
				}
		}
		
		print_r($queries);
		
		foreach ($queries as $ind=>$query) {
			echo "
			#1878 echo extra refs SQL:[{$ind}] {$query}\n";
			error_log("\n 
			#1878 error extra refs SQL:[{$ind}] ".$query);
			DB::getPDO()->query($query);
		}
		
	}

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

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

	public static function doGeneratePowiazania($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 {
			$query = "select count(*) from `BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA` where `FILE_STATUS` = 'IN_PROGRESS' and `BI_analiza_reloadCache` = 'Part' 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 częściowego odświeżania cache przez inne zadanie");
					sleep(30);
				}
			} while ($activeReloadingCache);
			
			BiAuditPowiazania::saveToLog("Uruchamiam częściowe odświeżenie cache");
			self::doReloadCache();
			BiAuditPowiazania::saveToLog("Odświeżono częściowo cache");

			$sqlArr = [
				'BI_analiza_reloadCache' => 'No',
				'FILE_STATUS_info' => 'Zakończono odświeżanie cache i wyzwolono funkcję szukania powiązań',
			];
			DB::getPDO()->update('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', "ID", $ID, $sqlArr);

			$BiAuditPowiazania = new BiAuditPowiazania($ID, $tasksDirLocation);
			if (!$BiAuditPowiazania->powiazaniaFound()) throw new Exception("Nie znaleziono żadnych powiązań");
			//file_put_contents($xmlFile, $BiAuditPowiazania->asXml());
			$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 $refTables;

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

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

	public function __construct() {
		$this->refTables = new BiAuditRefTables();
		$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 = $this->refTables->getRefTable($fromTable, $toTable)) {
			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']);
			} else  { error_log('\n
				#2000 error DEBUG found some records in REFs for table fromTable:'.$fromTable.' toTable:'.$toTable); 
				echo '\n
				#2000 echo DEBUG found some records in REFs for table fromTable:'.$fromTable.' toTable:'.$toTable;
			}
		} else { 
		//error_log('\n
		//	#2001 error DEBUG not found ref table fromTable:'.$fromTable.' toTable:'.$toTable);
		//		 echo '\n
		//		 #2001 echo DEBUG not found ref table fromTable:'.$fromTable.' toTable:'.$toTable ;
		}
	}

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

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

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

		$query = "select t2.ID as ID, t1.REMOTE_TABLE as REMOTE_TABLE_1, t2.REMOTE_TABLE as REMOTE_TABLE_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'], $ID, $row['ID']);
				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_2'], $row['REMOTE_TABLE_1'], $row['ID'], $ID);
				DB::getPDO()->query($query);
			}
		}
	}
}

class BiAuditRefTables {
	private $refTables = [];

	public function getRefTable($tableFrom, $tableTo) {
		if (!isset($this->refTables[$tableFrom][$tableTo])) {
			try {
				$this->refTables[$tableFrom][$tableTo] = ACL::getRefTable("default_db/{$tableFrom}/{$tableFrom}", "default_db__x3A__{$tableTo}:{$tableTo}");
			} catch (Exception $e) {
				$this->refTables[$tableFrom][$tableTo] = false;
			}
		}
		return $this->refTables[$tableFrom][$tableTo];
	}
}

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 $destTables = ['BI_audit_ENERGA_RUM_KONTRAHENCI', 'BI_audit_KW_requested_person'];
	private $refTables = [];
	private $step = 0;
	private $tasksDirLocation;
	private $progressFile;
	private $progress = [];
	private $lastProgress = 0;
	private $lastResults = 0;
	private $startTimestamp = null;
	private $lastTimestamp = null;
	private $lowProgressCount = 0;
	private $count, $i;
	private $splittedResults = [];
	private $maxResults = 1000000;

	private function getRefTable($tableFrom, $tableTo, $returnException = true) {
		if (!isset($this->refTables[$tableFrom][$tableTo])) {
			try {
				$this->refTables[$tableFrom][$tableTo] = ACL::getRefTable("default_db/{$tableFrom}/{$tableFrom}", "default_db__x3A__{$tableTo}:{$tableTo}");
			} catch (Exception $e) {
				if ($returnException) throw new Exception($e->getMessage());
				$this->refTables[$tableFrom][$tableTo] = false;
			}
		}
		return $this->refTables[$tableFrom][$tableTo];
	}

	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) {
		echo "CMD :: {$cmd}\n";
		shell_exec($cmd);
	}

	private function saveProgress($progress, $message = null) {
		$timestamp = microtime(true);
		if ($progress !== null && $this->i) {
			$elapsed = $timestamp - $this->startTimestamp;
			if ($progress) {
				//$lastElapsed = $timestamp - $this->lastTimestamp;
				//$lastProgress = $progress - $this->lastProgress;
				//$estimated = (($elapsed * (1 - $progress)) / $progress + ($lastElapsed * (1 - $lastProgress)) / $lastProgress) / 2;
				$estimated = ($elapsed * (1 - $progress)) / $progress;
			} else $estimated = "N/A";
			$this->lastProgress = $progress;
			$this->lastTimestamp = $timestamp;
			$progressJson = self::generateJson('progress', ['progress' => $progress, 'item' => $this->i . "/" . $this->count, 'results' => count($this->results), 'step' => $this->step, 'elapsed' => $elapsed, 'estimated' => $estimated]);
			echo $progressJson . "\n";
			$this->progress[$this->i] = ['progress' => $progress, 'results' => (count($this->results) - $this->lastResults), 'elapsed' => $elapsed, 'estimated' => $estimated];
		}
		if ($message !== null) $this->progress[0]['message'] = $message;
		$this->progress[0]['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->tasksDirLocation = $tasksDirLocation;

		$query = "select BI_analiza_minDepth, BI_analiza_maxDepth, BI_analiza_onlyTargets from BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA where ID = '{$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");
		$this->ID = $ID;

		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 = [];
		foreach ($this->destTables as $destTable) {
			$refTable = $this->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}'";
		}
		$query = implode(" union ", $subQueries);
		$result = DB::getPDO()->fetchAll($query);
		if (!$result) self::throwException("Błąd danych - nie zdefinowano żadnego końcowego obiektu");
		$this->endNodes = array_map('reset', $result);

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

		$this->progressFile = "{$this->tasksDirLocation}/generatePowiazania-{$this->ID}.progress";
		$refPowiazaniaToPracownicy = $this->getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', 'BI_audit_ENERGA_PRACOWNICY');
		$query = "select `all`.ID as ID from BI_audit_ALL `all` join `{$refPowiazaniaToPracownicy}` ref
			on `all`.REMOTE_TABLE = 'BI_audit_ENERGA_PRACOWNICY' and `all`.REMOTE_ID = ref.REMOTE_PRIMARY_KEY and ref.PRIMARY_KEY = '{$this->ID}' order by `all`.`ID` asc";
		$result = DB::getPDO()->fetchAll($query);
		$this->count = count($result);
		$this->progress[0]['count'] = $this->count;
		$this->origMaxDepth = $this->maxDepth;

		try {
			foreach ($result as $i => $row) {
				$this->i = $i + 1;
				$this->startTimestamp = microtime(true);
				$this->lastProgress = 0;
				$this->saveProgress(0, "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(1, "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(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->saveResults();
		//$this->saveToDb(); 2017-12-14 - przerobiono na zapisywanie danych do bazy w locie
		$this->splitResults();
	}

	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, $message);
			throw new Exception('');
		}
		$this->results[] = $this->path;
		$this->saveToDb($this->path);           
	}

	public function findPowiazania($ID, $progress = 0, $steps = 1, $relation = "", $end = 0) {
		if (((++$this->step) % 1000000) == 0) {
			$progressDiff = $progress - $this->lastProgress;
			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 pracownika");
				}
			} else $this->lowProgressCount = 0;
			$this->saveProgress($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 ($end) {
			if (in_array($ID, $this->endNodes) && 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])) {
			if ($relation) $where = "and ({$relation} & ref.RELATION_ID) != {$relation}";
			$query = "select ref.ID2, RELATION_ID, END from BI_audit_ALL_ref ref where ref.ID1 = {$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['ID2']])) return false;
			return true;
		}));
		$count = count($nodes);
		foreach ($nodes as $i => $node) $this->findPowiazania($node['ID2'], ($progress + ($i / ($count * $steps))), ($count * $steps), $node['RELATION_ID'], $node['END']);

		array_pop($this->path);
	}

	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($this->results)));
		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;
		}
		$this->results = $results;
		self::saveToLog("Wczytano wcześniej wyliczone dane");

		return true;
	}

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

	private function splitResults() {
		$MAX = 1000;

		$countBySource = [];
		$resultsKeys = [];
		foreach ($this->results as $resultKey => $result) $countBySource[reset(array_keys($result))][] = $resultKey;
		foreach ($countBySource as $resultKeys) {
			if (count($resultKeys) > $MAX) {
				$countByDest = [];
				foreach ($resultKeys as $resultKey) $countByDest[end(array_keys($this->results[$resultKey]))][] = $resultKey;
				foreach ($countByDest as $resultKeys2) {
					while (count($resultKeys2) > $MAX) $resultsKeys[] = array_splice($resultKeys2, 0, $MAX);
					$resultsKeys[] = $resultKeys2;
				}
			} 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 next($resultsKeys);
			}
			$results[] = array_map(function ($resultKey) {return $this->results[$resultKey];}, $resultKeys);
		}

		$this->splittedResults = $results;
	}

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

	private function generateItemsResults($resultsPart = null) {
		self::saveToLog("Generuję dane na potrzeby utworzenia pliku XML ({$resultsPart})");
		$xmlRoot2 = "BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA";
		$xmlElements = "BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row";

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

		$refUmowyToKontrahenci = $this->getRefTable('BI_audit_ENERGA_RUM_UMOWY', 'BI_audit_ENERGA_RUM_KONTRAHENCI');
		$refKwRequestedToKwRequestedPerson = $this->getRefTable('BI_audit_KW_requested', 'BI_audit_KW_requested_person');

		if ($resultsPart === null) $results = $this->results;
		else $results = (!empty($this->splittedResults[$resultsPart])) ? $this->splittedResults[$resultsPart] : [];

		foreach ($results as $result) {
			$item_results = [];
			foreach ($result as $ID => $rel) {
				$query = "select REMOTE_TABLE, REMOTE_ID from BI_audit_ALL where ID = '{$ID}'";
				$row = DB::getPDO()->fetchFirst($query);
				$query = "select * from `{$row['REMOTE_TABLE']}` where ID = '{$row['REMOTE_ID']}'";
				$obj = DB::fetch(DB::query($query));
				$item_result = [$row['REMOTE_TABLE'] => (array) $obj];

				if ($refTable = $this->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 = $this->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'] = $taxpayer;
				}

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

				if ($row['REMOTE_TABLE'] == "BI_audit_ENERGA_RUM_KONTRAHENCI" && (!in_array($row['REMOTE_ID'], $kontrahenci))) {
					$kontrahenci[] = $row['REMOTE_ID'];
					$query = "select umowy.* from BI_audit_ENERGA_RUM_UMOWY umowy join `{$refUmowyToKontrahenci}` ref on umowy.ID = ref.PRIMARY_KEY where ref.REMOTE_PRIMARY_KEY = '{$row['REMOTE_ID']}'";
					$res = DB::query($query);
					if (mysql_num_rows($res)) {
						$umowy = [];
						while ($obj = DB::fetch($res)) $umowy[] = (array) $obj;
						$item_kontrahenci['ID'] = $row['REMOTE_ID'];
						$item_kontrahenci['BI_audit_ENERGA_RUM_UMOWY'] = $umowy;
						$items_kontrahenci[] = $item_kontrahenci;
					}
				} elseif ($row['REMOTE_TABLE'] == "BI_audit_KW_requested_person" && (!in_array($row['REMOTE_ID'], $kw_person))) {
					$kw_person[] = $row['REMOTE_ID'];
					$query = "select kw.* from BI_audit_KW_requested kw join `{$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)) {
						$kw = [];
						while ($obj = DB::fetch($res)) $kw[] = (array) $obj;
						$item_kw_person['ID'] = $row['REMOTE_ID'];
						$item_kw_person['BI_audit_KW_requested'] = $kw;
						$items_kw_person[] = $item_kw_person;
					}
				}
			}
			$this->items_results[$resultsPart][$xmlRoot2][$xmlElements][] = $item_results;
		}
		$this->items_results[$resultsPart][$xmlRoot2]['BI_audit_ENERGA_RUM_KONTRAHENCI'] = $items_kontrahenci;
		$this->items_results[$resultsPart][$xmlRoot2]['BI_audit_KW_requested_person'] = $items_kw_person;
		self::saveToLog("Wygenerowano dane na potrzeby utworzenia pliku XML ({$resultsPart})");
	}

	private function saveToDb($result = 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 $result) $this->saveToDb($result);
			self::saveToLog("Zapisano wyliczone dane do bazy");
		} else {
			$refPowiazaniaToPowiazaniaRow = $this->getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA', 'BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row');
			$refPowiazaniaRowToPowiazaniaRowObject = $this->getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row', 'BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object');
			$IdPowiazaniaRow = DB::getPDO()->insert('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row', []);
			DB::getPDO()->insert($refPowiazaniaToPowiazaniaRow, ['PRIMARY_KEY' => $this->ID, 'REMOTE_PRIMARY_KEY' => $IdPowiazaniaRow]);
			foreach ($result as $ID => $rel) {
				$query = "select REMOTE_TABLE, REMOTE_ID from BI_audit_ALL where ID = '{$ID}'";
				$object = DB::getPDO()->fetchFirst($query);
				$idPowiazaniaRowObject = DB::getPDO()->insert('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object', ['relation_from' => $this->relations[$rel]]);
				DB::getPDO()->insert($refPowiazaniaRowToPowiazaniaRowObject, ['PRIMARY_KEY' => $IdPowiazaniaRow, 'REMOTE_PRIMARY_KEY' => $idPowiazaniaRowObject]);
				$ref = $this->getRefTable('BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object', $object['REMOTE_TABLE']);
				DB::getPDO()->insert($ref, ['PRIMARY_KEY' => $idPowiazaniaRowObject, 'REMOTE_PRIMARY_KEY' => $object['REMOTE_ID']]);
			}
		}
	}

	public function saveXml() {
		if ($this->getPartsCount()) {
			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 {
			$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), nie wygenerowano pliku XML ({$resultsPart})");
			return null;
		}
		if (!$this->items_results[$resultsPart]) $this->generateItemsResults($resultsPart);
		if (!$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() {
		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;
		$pdfFile = "/Library/Server/Web/Data/Sites/Default/SE/schema/WPS_Functions/default_db/CRM_PROCES_tree/temp/relations-{$id_part}/pdf/relations-{$id_part}.pdf";
		$htmlDir = "/Library/Server/Web/Data/Sites/Default/SE/schema/WPS_Functions/default_db/CRM_PROCES_tree/temp/relations-{$id_part}/html";
		if (!file_exists($antDir)) mkdir($antDir, 0755, 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})");
		} else {
			copy($xmlFile, $antXmlFile);
			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}");
			self::saveToLog("Generuję plik PDF ({$resultsPart})");
			self::exec("cd \"/Library/Server/Web/Data/Sites/Default/SE/schema/WPS_Functions/default_db/CRM_PROCES_tree/temp/relations-{$id_part}\" && /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})");
			self::saveToLog("Generuję pliki HTML ({$resultsPart})");
			self::exec("cd \"/Library/Server/Web/Data/Sites/Default/SE/schema/WPS_Functions/default_db/CRM_PROCES_tree/temp/relations-{$id_part}\" && /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)) {
				self::exec("cd \"/Library/Server/Web/Data/Sites/Default/SE/schema/WPS_Functions/default_db/CRM_PROCES_tree/temp/relations-{$id_part}\" && 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})");
		}
	}
}