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

<?php

Lib::loadClass('RouteBase');
Lib::loadClass('ProcesHelper');

class Route_UrlAction_JPK extends RouteBase {

	private $REFERER, $LAST_REFERER;
	private $JPK, $BO;

	public function handleAuth() {
		if (!User::logged()) {
			throw new HttpException('Unauthorized', 401);
		}

		$this->LAST_REFERER = $_SERVER['HTTP_REFERER'];
		if (($this->REFERER=V::get('REFERER','',$_POST))=='') $this->REFERER = $_SERVER['HTTP_REFERER'];

		try {
			if (!($ID_JPK = V::get('ID_JPK',0,$_GET,'int'))) throw new Exception("Błąd parametru");
			if ($result = DB::getPDO()->fetchall("select * from JPK where ID='{$ID_JPK}'")) $this->JPK = $result[0]; 
			else throw new Exception("Dostęp zabroniony");
			if ($result = DB::getPDO()->fetchall("select * from BILLING_OWNER where ID='{$this->JPK['ID_BILLING_OWNER']}'")) $this->BO = $result[0];
			else throw new Exception("Błąd spójności danych");
		} catch (Exception $e) {
			SE_Layout::gora();
			SE_Layout::menu();
			SE_Layout::alert('danger',$e->getMessage());
?>
<div class="container" style="text-align:center">
  <a href="<?php echo $this->REFERER?>" class="btn btn-primary">Powrót</a>
</div>
<?php
			SE_Layout::dol();
			die();
		}
	}

	private function initialize() {
		SE_Layout::gora();
		SE_Layout::menu();
		try {

			if (V::get('action','',$_POST) == 'initialize') {
				$month = V::get('MONTH','',$_POST);
				$purpose = V::get('PURPOSE','',$_POST);
				if (!preg_match("/^[[:digit:]]{4}-[[:digit:]]{2}$/",$month)) throw new Exception("błędny format daty (YYYY-MM)");
				switch ($purpose) {
					case "1":
						$lastMonth = DB::getPDO()->fetchValue("select max(MONTH) from JPK where ID_BILLING_OWNER='{$this->JPK['ID_BILLING_OWNER']}' and TYPE='{$this->JPK['TYPE']}' and PURPOSE='1'");
						if (strtotime($month) <= strtotime($lastMonth)) throw new Exception("błędny miesiąc (deklaracja z tego okresu lub późniejszego już istnieje)");
						break;
					case "2":
						$JPKexists = DB::getPDO()->fetchValue("select ID from JPK where ID_BILLING_OWNER='{$this->JPK['ID_BILLING_OWNER']}' and TYPE='{$this->JPK['TYPE']}' and MONTH='{$month}' and PURPOSE='1'");
						if (!$JPKexists) throw new Exception("brak deklaracji z tego okresu - nie ma czego korygować");
						break;
					default:
						throw new Exception("błędne określenie celu złożenia deklaracji");
				}

				$sqlObj = new stdClass();
				$sqlObj->ID = $this->JPK['ID'];
				$sqlObj->A_STATUS = 'NORMAL';
				$sqlObj->A_STATUS_INFO = 'W trakcie tworzenia';
				$sqlObj->MONTH = $month;
				$sqlObj->PURPOSE = $purpose;

				$affected = DB::getDB()->UPDATE_OBJ('JPK', $sqlObj);
				if ($affected) {
					SE_Layout::alert('success','Pomyślnie zainicjalizowano deklarację JPK.');
?>
<div class="container" style="text-align:center">
  <form method="post">
    <input type="hidden" name="REFERER" value="<?php echo $this->REFERER?>"/>
    <button type="submit" class="btn btn-primary">Edytuj</button>
    <a href="<?php echo $this->REFERER?>" class="btn btn-default">Powrót</a>
  </form>
</div>
<?php
				}
				else {
					SE_Layout::alert('warning','Wystąpił nieznany błąd podczas inicjalizowania deklaracji JPK.');
?>
<div class="container" style="text-align:center">
  <a href="<?php echo $this->REFERER?>" class="btn btn-primary">Powrót</a>
</div>
<?php
				}
			} else {
?>
<div class="container" style="margin-top:20px">
  <form class="form-horizontal" method="post">
    <legend>Inicjalizacja deklaracji JPK</legend>
    <div class="form-group">
      <label class="col-sm-3 control-label">Operator</label>
      <div class="col-sm-9" style="margin-top:7px;"><?php echo $this->BO['name1']?></div>
    </div>
    <div class="form-group">
      <label class="col-sm-3 control-label">Typ JPK</label>
      <div class="col-sm-9" style="margin-top:7px;"><?php echo $this->JPK['TYPE']?></div>
    </div>
    <div class="form-group">
      <label class="col-sm-3 control-label">Miesiąc, którego dotyczy deklaracja</label>
      <div class="col-sm-2">
	<div class="input-group">
	  <input type="text" id="MONTH" class="form-control se_type-date" name="MONTH" value="2017-01" maxlength="7"/>
	    <span class="input-group-addon">
	      <span class="glyphicon glyphicon-calendar"/>
	    </span>
	  </input>
	</div>
      </div>
    </div>
    <div class="form-group">
      <label class="col-sm-3 control-label">Cel złożenia deklaracji za dany okres</label>
      <div class="col-sm-3">
	<select class="form-control" name="PURPOSE">
	  <option value="1">Złożenie po raz pierwszy</option>
	  <option value="2">Korekta</option>
	</select>
      </div>
    </div>
    <div class="form-group">
      <div class="col-sm-offset-3 col-sm-9">
        <button type="submit" class="btn btn-primary" name="action" value="initialize">Zapisz</button>
        <a href="<?php echo $this->REFERER?>" class="btn btn-default">Anuluj</a>
      </div>
    </div>
  <input type="hidden" name="REFERER" value="<?php echo $this->REFERER?>"/>
  </form>
</div>
<script>
jQuery(document.getElementById('MONTH')).parent().datetimepicker({
format: 'YYYY-MM'
, locale: 'pl'
, showTodayButton: false
, minDate: '2017-01-01'
, maxDate: '<?=date("Y-m", strtotime("-1 month"))?>-28'
});
</script>
<?php
			}
		} catch (Exception $e) {
			SE_Layout::alert('danger', "Wystąpił problem z inicjalizacją deklaracji JPK - " . $e->getMessage());
?>
<div class="container" style="text-align:center">
  <a href="<?php echo $this->REFERER?>" class="btn btn-primary">Powrót</a>
</div>
<?php
		}
		SE_Layout::dol();	
	}

	private $fieldsDescrJPK_VAT = array(
		"KodFormularza" => "Kod formularza",
		"WariantFormularza" => "Wariant formularza",
		"CelZlozenia" => "Cel złożenia",
		"DataWytworzeniaJPK" => "Data wytworzenia JPK",
		"DataOd" => "Data od",
		"DataDo" => "Data do",
		"DomyslnyKodWaluty" => "Waluta",
		"KodUrzedu" => "Kod urzędu skarbowego",
		"PelnaNazwa" => "Nazwa podmiotu",
		"NIP" => "NIP",
		"REGON" => "Regon",
		"KodKraju" => "Kod kraju",
		"Wojewodztwo" => "Województwo",
		"Powiat" => "Powiat",
		"Gmina" => "Gmina",
		"Miejscowosc" => "Miejscowość",
		"Ulica" => "Ulica",
		"NrDomu" => "Nr domu",
		"NrLokalu" => "Nr lokalu",
		"KodPocztowy" => "Kod pocztowy",
		"Poczta" => "Poczta",
		"LiczbaWierszySprzedazy" => "Liczba dokumentów sprzedaży",
		"PodatekNalezny" => "Podatek należny",
		"LiczbaWierszyZakupow" => "Liczba dokumentów zakupu",
		"PodatekNaliczony" => "Podatek naliczony"
	);

	private function showJPK_VAT($xml) {
		$ns = $xml->getNamespaces(true);
		$summaryJPK_VAT = $this->getSummaryJPK_VAT($xml);

		$result['KodFormularza'] = $xml->Naglowek->KodFormularza;
		$result['WariantFormularza'] = $xml->Naglowek->WariantFormularza;
		$result['CelZlozenia'] = $xml->Naglowek->CelZlozenia;
		$result['DataWytworzeniaJPK'] = date("Y-m-d H:i:s", strtotime($xml->Naglowek->DataWytworzeniaJPK));
		$result['DataOd'] = $xml->Naglowek->DataOd;
		$result['DataDo'] = $xml->Naglowek->DataDo;
		$result['DomyslnyKodWaluty'] = $xml->Naglowek->DomyslnyKodWaluty;
		$result['KodUrzedu'] = $xml->Naglowek->KodUrzedu;
		$result['NIP'] = $xml->Podmiot1->IdentyfikatorPodmiotu->children($ns['etd'])->NIP;
		$result['PelnaNazwa'] = $xml->Podmiot1->IdentyfikatorPodmiotu->children($ns['etd'])->PelnaNazwa;
		$result['REGON'] = $xml->Podmiot1->IdentyfikatorPodmiotu->children($ns['etd'])->REGON;
		$result['KodKraju'] = $xml->Podmiot1->AdresPodmiotu->KodKraju;
		$result['Wojewodztwo'] = $xml->Podmiot1->AdresPodmiotu->Wojewodztwo;
		$result['Powiat'] = $xml->Podmiot1->AdresPodmiotu->Powiat;
		$result['Gmina'] = $xml->Podmiot1->AdresPodmiotu->Gmina;
		$result['Ulica'] = $xml->Podmiot1->AdresPodmiotu->Ulica;
		$result['NrDomu'] = $xml->Podmiot1->AdresPodmiotu->NrDomu;
		$result['NrLokalu'] = $xml->Podmiot1->AdresPodmiotu->NrLokalu;
		$result['Miejscowosc'] = $xml->Podmiot1->AdresPodmiotu->Miejscowosc;
		$result['KodPocztowy'] = $xml->Podmiot1->AdresPodmiotu->KodPocztowy;
		$result['Poczta'] = $xml->Podmiot1->AdresPodmiotu->Poczta;
		$result['LiczbaWierszySprzedazy'] = $summaryJPK_VAT['LiczbaWierszySprzedazy'] ? $summaryJPK_VAT['LiczbaWierszySprzedazy'] : "";
		$result['PodatekNalezny'] = $summaryJPK_VAT['LiczbaWierszySprzedazy'] ? number_format($summaryJPK_VAT['PodatekNalezny'], 2, ',', '.'): "";
		$result['LiczbaWierszyZakupow'] = $summaryJPK_VAT['LiczbaWierszyZakupow'] ? $summaryJPK_VAT['LiczbaWierszyZakupow'] : "";
		$result['PodatekNaliczony'] = $summaryJPK_VAT['LiczbaWierszyZakupow'] ? number_format($summaryJPK_VAT['PodatekNaliczony'], 2, ',', '.'): "";

		return $result;
	}

	private function showTable() {
?>
    <div class="form-group">
      <div class="col-sm-12">
         <table class="table table-bordered table-hover table-striped">
           <thead>
              <tr style="text-align:center; background-color:lightgray"><td width="19%"></td><td witdh="27%">Insert</td><td width="27%">L1</td><td width="27%">Dane wynikowe</td></tr>
           </thead>
           <tbody>
<?php
		if ($this->JPK['IN_INSERT']) {
			$xmlIN_INSERT = simplexml_load_string($this->JPK['IN_INSERT']);
			$IN_INSERT = $this->showJPK_VAT($xmlIN_INSERT);
		}
		if ($this->JPK['IN_L1']) {
			$xmlIN_L1 = simplexml_load_string($this->JPK['IN_L1']);
			$IN_L1 = $this->showJPK_VAT($xmlIN_L1);
		}
		if ($this->JPK['OUT_MERGED']) {
			$xmlOUT_MERGED = simplexml_load_string($this->JPK['OUT_MERGED']);
			$OUT_MERGED = $this->showJPK_VAT($xmlOUT_MERGED);
		}

		foreach ($this->fieldsDescrJPK_VAT as $fieldKey => $fieldName) {
			echo "<tr><td>{$fieldName}</td><td>";
			if ($this->JPK['IN_INSERT']) echo $IN_INSERT[$fieldKey];
			echo "</td><td>";
			if ($this->JPK['IN_L1']) echo $IN_L1[$fieldKey];
			echo "</td><td>";
			if ($this->JPK['OUT_MERGED']) echo $OUT_MERGED[$fieldKey];
			echo "</td></tr>\n";
		}
?>
           </tbody>
         <table>
      </div>
    </div>
<?php
	}

	private function show() {
		SE_Layout::gora();
		SE_Layout::menu();
?>
<div class="container" style="margin-top:20px">
  <form class="form-horizontal" method="post" enctype="multipart/form-data">
    <legend>
      Podgląd deklaracji JPK :: <?=$this->JPK['TYPE']?> - <?=$this->JPK['MONTH']." (".($this->JPK['PURPOSE'] == 1 ? "pierwsze złożenie" : "korekta").")"?>
      <span class="pull-right">
        <?=$this->BO['name1']?>
      </span>
    </legend>
    <div class="form-group">
    <div class="form-group">
      <div class="col-sm-10">
	<a href="<?=$_SERVER['HTTP_REFERER']?>&dupa=test" target="_blank" class="btn-sm btn-primary">Pobierz plik JPK</a>
      </div>
    </div>
<?=$this->showTable();?>
    <div class="form-group" style="text-align:center">
      <div class="col-sm-12">
        <input type="hidden" name="REFERER" value="<?=$this->REFERER?>"/>
        <input type="hidden" name="_task" value="edit">
        <a href="<?=$this->REFERER?>" class="btn btn-primary">Powrót</a>
      </div>
    </div>
  </form>
</div>
<?php
		SE_Layout::dol();
	}

	private function edit() {
		SE_Layout::gora();
		SE_Layout::menu();
?>
<div class="container" style="margin-top:20px">
  <form class="form-horizontal" method="post" enctype="multipart/form-data">
    <legend>
      Edycja deklaracji JPK :: <?=$this->JPK['TYPE']?> - <?=$this->JPK['MONTH']." (".($this->JPK['PURPOSE'] == 1 ? "pierwsze złożenie" : "korekta").")"?>
      <span class="pull-right">
        <?=$this->BO['name1']?>
      </span>
    </legend>
    <div class="form-group">
      <label class="col-sm-3 control-label"><?=($this->JPK['IN_INSERT'] ? "Usuń dane zaimportowane" : "Dodaj plik JPK")?> z pakietu Insert</label>
<?php
		if ($this->JPK['IN_INSERT']) {
?>
      <div class="col-sm-9" style="margin-top:3px;">
	<button type="submit" class="btn-xs btn-default" name="action" value="delete_insert_jpk">Usuń</button>
      </div>
<?php
		} else {
?>
      <div class="col-sm-9" style="margin-top:7px;">
	<input type="hidden" name="action" value="upload_insert_jpk"/>
	<input type="file" name="IN_INSERT" onchange="javascript:this.form.submit();"/>
      </div>
<?php
		}
?>
    </div>
    <div class="form-group">
      <label class="col-sm-3 control-label"><?=($this->JPK['IN_L1'] ? "Usuń" : "Pobierz")?> dane z L1</label>
      <div class="col-sm-9" style="margin-top:3px;">
	<button type="submit" class="btn-xs btn-default" name="action" value=<?=($this->JPK['IN_L1'] ? '"delete_l1_jpk">Usuń' : '"import_l1_jpk">Pobierz')?></button>
      </div>
    </div>
<?php
		if (!($this->JPK['IN_INSERT'] || $this->JPK['IN_L1'])) {
?>
    <div class="form-group">
      <label class="col-sm-3 control-label">Reinicjalizuj deklarację</label>
      <div class="col-sm-9" style="margin-top:3px;">
        <button type="submit" class="btn-xs btn-default" name="action" value="reinitialize">Reinicjalizuj</button>
      </div>
    </div>
<?php
		}
		if ($this->JPK['IN_INSERT'] && $this->JPK['IN_L1'] && (!$this->JPK['OUT_MERGED'])) {
?>
    <div class="form-group">
      <label class="col-sm-3 control-label">Importuj dane podmiotu z</label>
      <div class="col-sm-9" style="margin-top:3px;">
	<input type="radio" name="SRC_PODMIOT" value="Insert" checked> Insert<br/>
	<input type="radio" name="SRC_PODMIOT" value="L1"> L1
      </div>
    </div>
    <div class="form-group">
      <label class="col-sm-3 control-label">Generuj deklarację wynikową</label>
      <div class="col-sm-9" style="margin-top:3px;">
        <button type="submit" class="btn-xs btn-default" name="action" value="generate_merged_jpk">Generuj</button>
      </div>
    </div>
<?php
		}
		if ($this->JPK['OUT_MERGED']) {
?>
    <div class="form-group">
      <label class="col-sm-3 control-label">Zamknij deklarację JPK</label>
      <div class="col-sm-9" style="margin-top:3px;">
        <button type="submit" class="btn-xs btn-default" name="action" value="close_jpk">Zamknij</button>
	<p class="help-block">(tej operacji nie można cofnąć)</p>
      </div>
    </div>
<?php
		}
?>
<?=$this->showTable()?>
    <div class="form-group" style="text-align:center">
      <div class="col-sm-12">
	<input type="hidden" name="REFERER" value="<?=$this->REFERER?>"/>
	<input type="hidden" name="_task" value="edit">
        <a href="<?=$this->REFERER?>" class="btn btn-primary">Powrót</a>
      </div>
    </div>
  </form>
</div>
<?php
		SE_Layout::dol();
	}

	public function editAction() {
		$allowedActions = array("upload_insert_jpk", "delete_insert_jpk", "import_l1_jpk", "delete_l1_jpk", "generate_merged_jpk", "close_jpk", "reinitialize");

		if (in_array(($action = V::get('action','',$_POST)), $allowedActions)) $this->$action();
		else {
			SE_Layout::gora();
			SE_Layout::menu();
			SE_Layout::alert('danger', "Wykryto abuse! Wysłano informację do administratora.");
?>
<div class="container" style="text-align:center">
  <form method="post" action="<?php echo $this->LAST_REFERER?>">
    <input type="hidden" name="REFERER" value="<?php echo $this->REFERER?>"/>
    <button type="submit" class="btn btn-primary">Powrót</button>
  </form>
</div>
<?php
			SE_Layout::dol();
		}
	}

	private function getSummaryJPK_VAT($xml) {
		$PodatekNaleznyFields = array("K_16" => "1", "K_18" => "1", "K_20" => "1", "K_24" => "1", "K_26" => "1", "K_28" => "1",
			"K_30" => "1", "K_33" => "1", "K_35" => "1", "K_36" => "1", "K_37" => "1", "K_38" => "-1", "K_39" => "-1");
		$PodatekNaliczonyFields = array("K_44" => 1, "K_46" => 1, "K_47" => 1, "K_48" => 1, "K_49" => 1, "K_50" => 1);

		$result = array(
			"LiczbaWierszySprzedazy" => 0,
			"PodatekNalezny" => 0,
			"LiczbaWierszyZakupow" => 0,
			"PodatekNaliczony" => 0);

		foreach ($xml->SprzedazWiersz as $SprzedazWiersz) {
			foreach ($PodatekNaleznyFields as $PodatekNaleznyField => $sign) {
				if (isset($SprzedazWiersz->$PodatekNaleznyField)) {
					$result["PodatekNalezny"] += round((float) $SprzedazWiersz->$PodatekNaleznyField * $sign, 2);
				}
			}
			$result["LiczbaWierszySprzedazy"]++;
		}

		foreach ($xml->ZakupWiersz as $ZakupWiersz) {
			foreach ($PodatekNaliczonyFields as $PodatekNaliczonyField => $sign) {
				if (isset($ZakupWiersz->$PodatekNaliczonyField)) {
					$result["PodatekNaliczony"] += round((float) $ZakupWiersz->$PodatekNaliczonyField * $sign, 2);
				}
			}
			$result["LiczbaWierszyZakupow"]++;
		}

		$result["PodatekNalezny"] = round($result["PodatekNalezny"], 2);
		$result["PodatekNaliczony"] = round($result["PodatekNaliczony"], 2);
		return $result;
	}

	private function validateJPK_VAT(&$xml) {
		$ns = $xml->getNamespaces(true);
		if ($xml->Naglowek->KodFormularza != "JPK_VAT") throw new Exception("Błędny typ deklaracji JPK");
		if ($xml->Naglowek->WariantFormularza != "2") throw new Exception("Błędna wersja deklaracji JPK");
		if ($xml->Naglowek->CelZlozenia != $this->JPK['PURPOSE']) throw new Exception("Niezgodny cel złożenia deklaracji JPK");
		if ($xml->Naglowek->DataOd != ($this->JPK['MONTH'] . "-01")) throw new Exception("Błędna data 'od' deklaracji JPK");
		if ($xml->Naglowek->DataDo != date("Y-m-d", strtotime($xml->Naglowek->DataOd . "+ 1 month - 1 day"))) throw new Exception("Błędna data 'do' deklaracji JPK");
		if ($xml->Naglowek->DomyslnyKodWaluty != "PLN") throw new Exception("Błędna waluta deklaracji JPK");
		if (trim(str_replace('-', '', $xml->Podmiot1->IdentyfikatorPodmiotu->children($ns['etd'])->NIP)) != trim(str_replace('-', '', $this->BO['nip']))) throw new Exception("Niezgodny NIP podmiotu w deklaracji JPK");
		$summaryJPK_VAT = $this->getSummaryJPK_VAT($xml);
		if ($summaryJPK_VAT["LiczbaWierszySprzedazy"]) {
			if ($xml->SprzedazCtrl->LiczbaWierszySprzedazy != $summaryJPK_VAT["LiczbaWierszySprzedazy"]) throw new Exception("Brak spójności liczby wierszy sprzedaży w deklaracji JPK");
			if (((float) $xml->SprzedazCtrl->PodatekNalezny) != $summaryJPK_VAT["PodatekNalezny"])  throw new Exception("Brak spójności wartości podatku należnego w deklaracji JPK");
		}
		if ($summaryJPK_VAT["LiczbaWierszyZakupow"]) {
			if ($xml->ZakupCtrl->LiczbaWierszyZakupow != $summaryJPK_VAT["LiczbaWierszyZakupow"]) throw new Exception("Brak spójności liczby wierszy zakupów w deklaracji JPK");
			if (((float) $xml->ZakupCtrl->PodatekNaliczony) != $summaryJPK_VAT["PodatekNaliczony"])  throw new Exception("Brak spójności wartości podatku naliczonego w deklaracji JPK");
		}

		function HandleXmlError($errno, $errstr, $errfile, $errline) {
			if ($errno==E_WARNING && (substr_count($errstr,"DOMDocument::loadXML()")>0)) throw new DOMException($errstr);
			else return false;
		}

		set_error_handler('HandleXmlError');
		try {
			$dom = new DOMDocument();
			$dom->preserveWhiteSpace = false;
			$dom->formatOutput = true;
			$dom->loadXML($xml->asXML());
		} catch (Exception $e) {
			throw new Exception("Błąd parsowania pliku JPK");
		}
		restore_error_handler();

		$xml = simplexml_load_string($dom->saveXML());
	}

	private function upload_insert_jpk() {
		try {
			if ($_FILES['IN_INSERT']['type'] != "text/xml") throw new Exception("Błędny plik JPK");
			$xml = simplexml_load_file($_FILES['IN_INSERT']['tmp_name']);
			$this->validateJPK_VAT($xml);
			if ($this->JPK['IN_L1']) $info = "Zaimportowano dane z Insert i L1";
			else $info = "Zaimportowano dane z Insert";

			$sqlObj = new stdClass();
			$sqlObj->ID = $this->JPK['ID'];
			$sqlObj->A_STATUS_INFO = $info;
			$sqlObj->IN_INSERT = $xml->asXml();
			if (!$affected = DB::getDB()->UPDATE_OBJ('JPK', $sqlObj)) throw new Exception("Wystąpił błąd, prawdopodobnie Ten plik JPK został wgrany już wcześniej");
			$this->handleAuth();
			$this->edit();
		} catch (Exception $e) {
			SE_Layout::gora();
			SE_Layout::menu();
			SE_Layout::alert('danger', $e->getMessage());
?>
<div class="container" style="text-align:center">
  <form method="post" action="<?php echo $this->LAST_REFERER?>">
    <input type="hidden" name="REFERER" value="<?php echo $this->REFERER?>"/>
    <button type="submit" class="btn btn-primary">Powrót</button>
  </form>
</div>
<?php
			SE_Layout::dol();
		}
	}

	private function delete_insert_jpk() {
		try {
			$info = "Usunięto dane z Insert";
			if ($this->JPK['IN_L1']) $info .= " (pozostawiono dane z L1)";

			$sqlObj = new stdClass();
			$sqlObj->ID = $this->JPK['ID'];
			$sqlObj->A_STATUS_INFO = $info;
			$sqlObj->IN_INSERT = null;
			$sqlObj->OUT_MERGED = null;
			if (!$affected = DB::getDB()->UPDATE_OBJ('JPK', $sqlObj)) throw new Exception("Wystąpił nieznany błąd");
			$this->handleAuth();
			$this->edit();
		} catch (Exception $e) {
			SE_Layout::gora();
			SE_Layout::menu();
			SE_Layout::alert('danger', $e->getMessage());
?>
<div class="container" style="text-align:center">
  <form method="post" action="<?php echo $this->LAST_REFERER?>">
    <input type="hidden" name="REFERER" value="<?php echo $this->REFERER?>"/>
    <button type="submit" class="btn btn-primary">Powrót</button>
  </form>
</div>
<?php
			SE_Layout::dol();
		}
	}

	private function import_l1_jpk() {

		$xmlSchema = <<<EOT
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<JPK xmlns="http://jpk.mf.gov.pl/wzor/2016/10/26/10261/" xmlns:etd="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2016/01/25/eD/DefinicjeTypy/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Naglowek>
    <KodFormularza kodSystemowy="JPK_VAT (2)" wersjaSchemy="1-0">JPK_VAT</KodFormularza>
    <WariantFormularza>2</WariantFormularza>
    <CelZlozenia></CelZlozenia>
    <DataWytworzeniaJPK></DataWytworzeniaJPK>
    <DataOd></DataOd>
    <DataDo></DataDo>
    <DomyslnyKodWaluty>PLN</DomyslnyKodWaluty>
    <KodUrzedu></KodUrzedu>
  </Naglowek>
  <Podmiot1>
    <IdentyfikatorPodmiotu>
      <etd:NIP></etd:NIP>
      <etd:PelnaNazwa></etd:PelnaNazwa>
      <etd:REGON></etd:REGON>
    </IdentyfikatorPodmiotu>
    <AdresPodmiotu>
      <KodKraju></KodKraju>
      <Wojewodztwo></Wojewodztwo>
      <Powiat></Powiat>
      <Gmina></Gmina>
      <Ulica></Ulica>
      <NrDomu></NrDomu>
      <NrLokalu></NrLokalu>
      <Miejscowosc></Miejscowosc>
      <KodPocztowy></KodPocztowy>
      <Poczta></Poczta>
    </AdresPodmiotu>
  </Podmiot1>
</JPK>
EOT;

		$queryFVAT = <<<EOT
select
bn.ID as BN_ID,
concat(bua.P_NAME, if(bua.P_NAME_SECOND='','',concat(' ',bua.P_NAME_SECOND))) AS NazwaKontrahenta,
concat(bua.P_ADDRESS_STREET,' ',bua.P_ADDRESS_HOUSE, if(bua.P_ADDRESS_HOME='','',concat('/',bua.P_ADDRESS_HOME)),', ',P_ADDRESS_POST_CODE,' ',P_ADDRESS_CITY) AS AdresKontrahenta,
bf.SELL_DATE AS 'DataWystawienia',
concat(bn.NUMBER,'/',bn.ID_BILLING_PREFIXES) AS 'DowodSprzedazy',
if(coalesce(bua.P_NIP,'')='','brak',coalesce(bua.P_NIP,'')) AS 'NrKontrahenta',
round(bfp.AMMOUNT * bfp.PRICE, 2) as Netto,
round(round(bfp.AMMOUNT * bfp.PRICE, 2) * bfp.VAT/100, 2) as VAT,
bfp.VAT_NAME as 'VAT_NAME'
from BILLING_NUMBERS bn
join BILLS_FVAT bf on bn.ID=bf.ID_BILLING_NUMBERS
join BILLS_FVAT_POS bfp on bf.ID=bfp.ID_BILLS_FVAT
join BILLING_USERS_ADD bua on bua.id_users=bn.ID_BILLING_USERS
join BILLING_USERS bu on bu.ID=bn.ID_BILLING_USERS
where bn.ID_BILLING_NUMBERS_TYPE='1' and bf.BILL_DATE like '{$this->JPK['MONTH']}%'
and bu.BILLING_OWNER='{$this->BO['ID']}'
order by bf.BILL_DATE,bn.ID
EOT;

		$queryKORV = <<<EOT
select
bn.ID AS BN_ID,
concat(bua.P_NAME, if(bua.P_NAME_SECOND='','',concat(' ',bua.P_NAME_SECOND))) AS NazwaKontrahenta,
concat(bua.P_ADDRESS_STREET,' ',bua.P_ADDRESS_HOUSE, if(bua.P_ADDRESS_HOME='','',concat('/',bua.P_ADDRESS_HOME)),', ',P_ADDRESS_POST_CODE,' ',P_ADDRESS_CITY) AS AdresKontrahenta,
bk.BILL_DATE AS 'DataWystawienia',
bk.SELL_DATE AS 'DataSprzedazy',
concat(bn.NUMBER,'/',bn.ID_BILLING_PREFIXES) AS 'DowodSprzedazy',
if(coalesce(bua.P_NIP,'')='','brak',coalesce(bua.P_NIP,'')) AS 'NrKontrahenta',
bkp.AMMOUNT AS AMMOUNT,
bkp.PRICE AS PRICE,
bkp.VAT AS VAT,
bkp.VAT_NAME AS VAT_NAME,
bkp.N_AMMOUNT AS N_AMMOUNT,
bkp.N_PRICE AS N_PRICE,
bkp.N_VAT AS N_VAT,
bkp.N_VAT_NAME AS N_VAT_NAME,
bkp.TYP_KOREKTY AS TYP_KOREKTY
from BILLING_NUMBERS bn
join BILLS_KORV bk on bn.ID=bk.ID_BILLING_NUMBERS
join BILLS_KORV_POS bkp on bk.ID=bkp.ID_BILLS_FVAT
join BILLING_USERS_ADD bua on bua.id_users=bn.ID_BILLING_USERS
join BILLING_USERS bu on bu.ID=bn.ID_BILLING_USERS
where bn.ID_BILLING_NUMBERS_TYPE='3'
and bk.BILL_DATE like '{$this->JPK['MONTH']}%'
and bkp.TYP_KOREKTY IS NOT NULL
and bu.BILLING_OWNER='{$this->BO['ID']}'
order by bk.BILL_DATE,bn.ID
EOT;

		$xmlVAT = array(
			'ZW' => array('netto' => 'K_10', 'vat' => ''),
			'0' => array('netto' => 'K_13', 'vat' => ''),
			'7' => array('netto' => 'K_17', 'vat' => 'K_18'),
			'8' => array('netto' => 'K_17', 'vat' => 'K_18'),
			'22' => array('netto' => 'K_19', 'vat' => 'K_20'),
			'23' => array('netto' => 'K_19', 'vat' => 'K_20')
		);

		try {
			if (DB::getPDO(931)->fetchValue("select sum(c) from (select count(*) as c from BILLS_FVAT where OPEN='y' union select count(*) as c from BILLS_KORV where OPEN='y') as c;")) {
				throw new Exception("wykryto niezamknięte faktury lub korekty faktur");
			}

			$xml = new SimpleXMLElement($xmlSchema);
			$ns = $ns = $xml->getNamespaces(true);

			$xml->Naglowek->CelZlozenia = $this->JPK['PURPOSE'];
			$xml->Naglowek->DataWytworzeniaJPK = date("Y-m-d\TH:i:s");
			$xml->Naglowek->DataOd = $this->JPK['MONTH'] . "-01";
			$xml->Naglowek->DataDo = date("Y-m-d", strtotime($xml->Naglowek->DataOd . "+ 1 month - 1 day"));
			if (!($xml->Naglowek->KodUrzedu = $this->BO['kodUrzeduSkarbowego'])) throw new Exception("blędne dane podmiotu - brak kodu urzędu skarbowego");
			if (!($xml->Podmiot1->IdentyfikatorPodmiotu->children($ns['etd'])->NIP = $this->BO['nip'])) throw new Exception("blędne dane podmiotu - brak NIP");
			if (!($xml->Podmiot1->IdentyfikatorPodmiotu->children($ns['etd'])->PelnaNazwa = $this->BO['name1'])) throw new Exception("blędne dane podmiotu - brak nazwy podmiotu");
			if ($this->BO['name2']) $xml->Podmiot1->IdentyfikatorPodmiotu->children($ns['etd'])->PelnaNazwa .= " " . $this->BO['name2'];
			if (!($xml->Podmiot1->IdentyfikatorPodmiotu->children($ns['etd'])->REGON = $this->BO['regon'])) unset($xml->Podmiot1->IdentyfikatorPodmiotu->children($ns['etd'])->REGON);
			if (!($xml->Podmiot1->AdresPodmiotu->KodKraju = $this->BO['kodKraju'])) throw new Exception("blędne dane podmiotu - brak kodu kraju");
			if (!($xml->Podmiot1->AdresPodmiotu->Wojewodztwo = $this->BO['wojewodztwo'])) unset($xml->Podmiot1->AdresPodmiotu->Wojewodztwo);
			if (!($xml->Podmiot1->AdresPodmiotu->Powiat = $this->BO['powiat'])) unset($xml->Podmiot1->AdresPodmiotu->Powiat);
			if (!($xml->Podmiot1->AdresPodmiotu->Gmina = $this->BO['gmina'])) unset($xml->Podmiot1->AdresPodmiotu->Gmina);
			if (!($xml->Podmiot1->AdresPodmiotu->Ulica = $this->BO['ulica'])) unset($xml->Podmiot1->AdresPodmiotu->Ulica);
			if (!($xml->Podmiot1->AdresPodmiotu->NrDomu = $this->BO['numer_dom'])) unset($xml->Podmiot1->AdresPodmiotu->NrDomu);
			if (!($xml->Podmiot1->AdresPodmiotu->NrLokalu = $this->BO['numer_pos'])) unset($xml->Podmiot1->AdresPodmiotu->NrLokalu);
			if (!($xml->Podmiot1->AdresPodmiotu->Miejscowosc = $this->BO['miasto'])) throw new Exception("blędne dane podmiotu - brak nazwy miejscowości");
			if (!($xml->Podmiot1->AdresPodmiotu->KodPocztowy = $this->BO['kod'])) unset($xml->Podmiot1->AdresPodmiotu->KodPocztowy);
			if (!($xml->Podmiot1->AdresPodmiotu->Poczta = $this->BO['poczta'])) unset($xml->Podmiot1->AdresPodmiotu->Poczta);

			$data = array();
			$allVAT = 0;

			$result = DB::getPDO(931)->fetchall($queryFVAT);
			foreach ($result as $row) {
				$data[$row['BN_ID']][0]['NrKontrahenta'] = $row['NrKontrahenta'];
				$data[$row['BN_ID']][0]['NazwaKontrahenta'] = $row['NazwaKontrahenta'];
				$data[$row['BN_ID']][0]['AdresKontrahenta'] = $row['AdresKontrahenta'];
				$data[$row['BN_ID']][0]['DowodSprzedazy'] = $row['DowodSprzedazy'];
				$data[$row['BN_ID']][0]['DataWystawienia'] = $row['DataWystawienia'];
				if (!in_array($row['VAT_NAME'],array_keys($xmlVAT))) throw new Exception("Problem ze stawką VAT (1)");
				$data[$row['BN_ID']][1][$xmlVAT[$row['VAT_NAME']]['netto']] += $row['Netto'];
				if ($row['VAT']) {
					if (!$xmlVAT[$row['VAT_NAME']]['vat']) throw new Exception("Problem ze stawką VAT (2)");
					$data[$row['BN_ID']][1][$xmlVAT[$row['VAT_NAME']]['vat']] += $row['VAT'];
					$allVAT += $row['VAT'];
				}
			}

			$result = DB::getPDO(931)->fetchall($queryKORV);
			foreach ($result as $row) {
				$data[$row['BN_ID']][0]['NrKontrahenta'] = $row['NrKontrahenta'];
				$data[$row['BN_ID']][0]['NazwaKontrahenta'] = $row['NazwaKontrahenta'];
				$data[$row['BN_ID']][0]['AdresKontrahenta'] = $row['AdresKontrahenta'];
				$data[$row['BN_ID']][0]['DowodSprzedazy'] = $row['DowodSprzedazy'];
				$data[$row['BN_ID']][0]['DataWystawienia'] = $row['DataWystawienia'];
				$data[$row['BN_ID']][0]['DataSprzedazy'] = $row['DataSprzedazy'];
				if (!(in_array($row['VAT_NAME'], array_keys($xmlVAT)) && in_array($row['N_VAT_NAME'], array_keys($xmlVAT)))) throw new Exception("Problem ze stawką VAT (1)");
				if (($row['VAT'] && !($xmlVAT[$row['VAT_NAME']]['vat'])) || ($row['N_VAT'] && !($xmlVAT[$row['N_VAT_NAME']]['vat']))) throw new Exception("Problem ze stawką VAT (2)");
				if ($row['VAT_NAME'] == $row['N_VAT_NAME']) {
					$data[$row['BN_ID']][1][$xmlVAT[$row['VAT_NAME']]['netto']] += round($row['AMMOUNT']*$row['PRICE'],2);
					if ($row['VAT']) {
						$data[$row['BN_ID']][1][$xmlVAT[$row['VAT_NAME']]['vat']] += round(round($row['AMMOUNT']*$row['PRICE'],2)*$row['VAT']/100,2);
						$allVAT += round(round($row['AMMOUNT']*$row['PRICE'],2)*$row['VAT']/100,2);
					}
				} else {
					$data[$row['BN_ID']][1][$xmlVAT[$row['VAT_NAME']]['netto']] += round($row['N_AMMOUNT']*$row['N_PRICE'],2) + round($row['AMMOUNT']*$row['PRICE'],2);
					$data[$row['BN_ID']][1][$xmlVAT[$row['N_VAT_NAME']]['netto']] -= round($row['N_AMMOUNT']*$row['N_PRICE'],2);
					if ($row['VAT']) {
						$data[$row['BN_ID']][1][$xmlVAT[$row['VAT_NAME']]['vat']] += round(round($row['N_AMMOUNT']*$row['N_PRICE'],2)*$row['VAT']/100,2)
							+ round(round($row['AMMOUNT']*$row['PRICE'],2)*$row['VAT']/100,2);
						$allVAT += round(round($row['N_AMMOUNT']*$row['N_PRICE'],2)*$row['VAT']/100,2) + round(round($row['AMMOUNT']*$row['PRICE'],2)*$row['VAT']/100,2);
					}
					if ($row['N_VAT']) {
						$data[$row['BN_ID']][1][$xmlVAT[$row['N_VAT_NAME']]['vat']] -= round(round($row['N_AMMOUNT']*$row['N_PRICE'],2)*$row['N_VAT']/100,2);
						$allVAT -= round(round($row['N_AMMOUNT']*$row['N_PRICE'],2)*$row['N_VAT']/100,2);
					}
				}
			}

			ksort($data);

			$i = 0;
			foreach ($data as $pos) {
				$x = $xml->addChild('SprzedazWiersz');
				$x->addAttribute('typ', 'G');
				$x->addChild('LpSprzedazy', ++$i);
				ksort($pos[1]);
				foreach ($pos as $subpos) foreach ($subpos as $key => $value) $x->addChild($key, $value);
			}

			$xml->SprzedazCtrl->LiczbaWierszySprzedazy = count($data);
			$xml->SprzedazCtrl->PodatekNalezny = $allVAT;

			$this->validateJPK_VAT($xml);
			if ($this->JPK['IN_INSERT']) $info = "Zaimportowano dane z Insert i L1";
			else $info = "Zaimportowano dane z L1";
			$sqlObj = new stdClass();
			$sqlObj->ID = $this->JPK['ID'];
			$sqlObj->A_STATUS_INFO = $info;
			$sqlObj->IN_L1 = $xml->asXml();
			if (!$affected = DB::getDB()->UPDATE_OBJ('JPK', $sqlObj)) throw new Exception("Wystąpił nieznany błąd bazy danych");
			$this->handleAuth();
			$this->edit();
		} catch (Exception $e) {
			SE_Layout::gora();
			SE_Layout::menu();
			SE_Layout::alert('danger', "Błąd w trakcie importu danych z L1 ({$e->getMessage()})");
?>
<div class="container" style="text-align:center">
  <form method="post" action="<?php echo $this->LAST_REFERER?>">
    <input type="hidden" name="REFERER" value="<?php echo $this->REFERER?>"/>
    <button type="submit" class="btn btn-primary">Powrót</button>
  </form>
</div>
<?php
			SE_Layout::dol();
		}
	}

	private function delete_l1_jpk() {
		try {
			$info = "Usunięto dane z L1";
			if ($this->JPK['IN_INSERT']) $info .= " (pozostawiono dane z Insert)";

			$sqlObj = new stdClass();
			$sqlObj->ID = $this->JPK['ID'];
			$sqlObj->A_STATUS_INFO = $info;
			$sqlObj->IN_L1 = null;
			$sqlObj->OUT_MERGED = null;
			if (!$affected = DB::getDB()->UPDATE_OBJ('JPK', $sqlObj)) throw new Exception("Wystąpił nieznany błąd");
			$this->handleAuth();
			$this->edit();
		} catch (Exception $e) {
			SE_Layout::gora();
			SE_Layout::menu();
			SE_Layout::alert('danger', $e->getMessage());
?>
<div class="container" style="text-align:center">
  <form method="post" action="<?php echo $this->LAST_REFERER?>">
    <input type="hidden" name="REFERER" value="<?php echo $this->REFERER?>"/>
    <button type="submit" class="btn btn-primary">Powrót</button>
  </form>
</div>
<?php
			SE_Layout::dol();
		}
	}

	private function generate_merged_jpk() {

		$xmlSchema = <<<EOT
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<JPK xmlns="http://jpk.mf.gov.pl/wzor/2016/10/26/10261/" xmlns:etd="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2016/01/25/eD/DefinicjeTypy/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</JPK>
EOT;

		function xml_append(SimpleXMLElement $to, SimpleXMLElement $from) {
			$toDom = dom_import_simplexml($to);
			$fromDom = dom_import_simplexml($from);
			$toDom->appendChild($toDom->ownerDocument->importNode($fromDom, true));
		}

		try {
			if (!($this->JPK['IN_INSERT'] && $this->JPK['IN_L1'])) throw new Exception("Błąd danych");

			$xmlInsert = simplexml_load_string($this->JPK['IN_INSERT']);
			$xmlL1 = simplexml_load_string($this->JPK['IN_L1']);
			$xml = simplexml_load_string($xmlSchema);

			switch (V::get('SRC_PODMIOT','',$_POST)) {
				case "Insert":
					xml_append($xml, $xmlInsert->Naglowek);
					xml_append($xml, $xmlInsert->Podmiot1);
					break;
				case "L1":
					xml_append($xml, $xmlL1->Naglowek);
					xml_append($xml, $xmlL1->Podmiot1);
					break;
				default:
					throw new Exception("Błąd formularza");
			}

			$xml->Naglowek->DataWytworzeniaJPK = date("Y-m-d\TH:i:s");

			$i = 0;
			foreach ($xmlInsert->SprzedazWiersz as $sprzedazWiersz) {
				$sprzedazWiersz->LpSprzedazy = ++$i;
				xml_append($xml, $sprzedazWiersz);
			}
			foreach ($xmlL1->SprzedazWiersz as $sprzedazWiersz) {
				$sprzedazWiersz->LpSprzedazy = ++$i;
				xml_append($xml, $sprzedazWiersz);
			}
			$xml->addChild("SprzedazCtrl");

			$i = 0;
			foreach ($xmlInsert->ZakupWiersz as $zakupWiersz) {
				$zakupWiersz->LpZakupu = ++$i;
				xml_append($xml, $zakupWiersz);
			}
			foreach ($xmlL1->ZakupWiersz as $zakupWiersz) {
				$zakupWiersz->LpZakupu = ++$i;
				xml_append($xml, $zakupWiersz);
			}
			$xml->addChild("ZakupCtrl");

			$sumaryJPK_VAT = $this->getSummaryJPK_VAT($xml);
			$xml->SprzedazCtrl->LiczbaWierszySprzedazy = $sumaryJPK_VAT['LiczbaWierszySprzedazy'];
			$xml->SprzedazCtrl->PodatekNalezny = $sumaryJPK_VAT['PodatekNalezny'];
			$xml->ZakupCtrl->LiczbaWierszyZakupow = $sumaryJPK_VAT['LiczbaWierszyZakupow'];
			$xml->ZakupCtrl->PodatekNaliczony = $sumaryJPK_VAT['PodatekNaliczony'];
			$this->validateJPK_VAT($xml);

			$sqlObj = new stdClass();
			$sqlObj->ID = $this->JPK['ID'];
			$sqlObj->A_STATUS_INFO = "Wygenerowano wynikowy plik JPK";
			$sqlObj->OUT_MERGED = $xml->asXml();
			if (!$affected = DB::getDB()->UPDATE_OBJ('JPK', $sqlObj)) throw new Exception("Wystąpił nieznany błąd bazy danych");
			$this->handleAuth();
			$this->edit();

		} catch (Exception $e) {
			SE_Layout::gora();
			SE_Layout::menu();
			SE_Layout::alert('danger', $e->getMessage());
?>
<div class="container" style="text-align:center">
  <form method="post" action="<?php echo $this->LAST_REFERER?>">
    <input type="hidden" name="REFERER" value="<?php echo $this->REFERER?>"/>
    <button type="submit" class="btn btn-primary">Powrót</button>
  </form>
</div>
<?php
			SE_Layout::dol();
		}
	}

	private function close_jpk() {
		try {
			$sqlObj = new stdClass();
			$sqlObj->ID = $this->JPK['ID'];
			$sqlObj->A_STATUS = 'OFF_HARD';
			$sqlObj->A_STATUS_INFO = "Zamknięto deklarację JPK";
			if (!$affected = DB::getDB()->UPDATE_OBJ('JPK', $sqlObj)) throw new Exception("Wystąpił nieznany błąd bazy danych");
			$this->handleAuth();
			$this->show();
		} catch (Exception $e) {
			SE_Layout::gora();
			SE_Layout::menu();
			SE_Layout::alert('danger', $e->getMessage());
?>
<div class="container" style="text-align:center">
  <form method="post" action="<?php echo $this->LAST_REFERER?>">
    <input type="hidden" name="REFERER" value="<?php echo $this->REFERER?>"/>
    <button type="submit" class="btn btn-primary">Powrót</button>
  </form>
</div>
<?php
			SE_Layout::dol();
		}
	}

	private function reinitialize() {
		$sqlObj = new stdClass();
		$sqlObj->ID = $this->JPK['ID'];
		$sqlObj->A_STATUS = 'WAITING';
		$sqlObj->A_STATUS_INFO = "Oczekuje na inicjalizację";
		$sqlObj->MONTH = "NULL";
		$sqlObj->PURPOSE = "NULL";
		DB::getDB()->UPDATE_OBJ('JPK', $sqlObj);
		$this->initialize();
}

	public function defaultAction() {
		switch ($this->JPK['A_STATUS']) {
			case "WAITING":
				$this->initialize();
				break;
			case "NORMAL":
				$this->edit();
				break;
			case "OFF_HARD":
				$this->show();
				break;
			default:
				SE_Layout::alert('danger', "Błędny status rekordu.");
		}
	}

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

	public function reinstall() {
		$sqlList = array();
/*		$sqlList['InstallLayerTable'] = <<<SQL
CREATE TABLE IF NOT EXISTS `WMS_LAYERS` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `the_geom` polygon NOT NULL,
  `ID_PROJECT` int(11) NOT NULL,
  `LAYER_NAME` varchar(100) NOT NULL,
  `LAYER_TYPE` enum('GeoTIFF','QGIS') NOT NULL DEFAULT 'GeoTIFF',
  `OLD_LAYER_TYPE` enum('GeoTIFF','QGIS') NOT NULL,
  `LAYER_DESC` text NOT NULL,
  `ID_WMS_MAPS` int(11) NOT NULL,
  `OLD_ID_WMS_MAPS` int(11) NOT NULL,
  `FILE_NAME` varchar(255) NOT NULL,
  `FILE_STATUS` enum('NONE','TO_GENERATE','IN_PROGRESS','GENERATED','ERROR') NOT NULL DEFAULT 'NONE',
  `FILE_STATUS_INFO` varchar(255) NOT NULL,
  `EPSG` int(11) NOT NULL,
  `EXTENT` varchar(100) NOT NULL,
  `MAP_STATUS` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'INACTIVE',
  `FILE_MD5` varchar(32) NOT NULL,
  `A_RECORD_CREATE_DATE` datetime NOT NULL,
  `A_RECORD_CREATE_AUTHOR` varchar(40) NOT NULL,
  `A_RECORD_UPDATE_DATE` varchar(18) NOT NULL,
  `A_RECORD_UPDATE_AUTHOR` varchar(40) NOT NULL,
  `L_APPOITMENT_DATE` datetime NOT NULL,
  `L_APPOITMENT_USER` varchar(40) NOT NULL,
  `L_APPOITMENT_PERIOD` varchar(30) NOT NULL,
  `L_APPOITMENT_INFO` varchar(200) NOT NULL,
  `L_APPOITMENT_TYPE` enum('','ARRANGED','TO_ARRANGE','CANCELLED','TO_CANCEL','RELATE','WAIT') NOT NULL,
  `A_PROBLEM` enum('','WARNING','PROBLEM','SERIOUS','UNVERIFIED') NOT NULL,
  `A_PROBLEM_DESC` varchar(200) NOT NULL,
  `A_PROBLEM_DATE` datetime NOT NULL,
  `A_STATUS` enum('DELETED','MONITOR','NORMAL','OFF_HARD','OFF_SOFT','WAITING','WARNING') NOT NULL DEFAULT 'WAITING',
  `A_STATUS_INFO` varchar(100) NOT NULL,
  `A_STATUS_LASTCHANGE_DATE` datetime NOT NULL,
  `A_ADM_COMPANY` varchar(100) NOT NULL,
  `A_CLASSIFIED` varchar(100) NOT NULL,
  `SYNC_SQIX_STATUS` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2;
SQL;

                $sqlList['InstallLayerHistTable'] = <<<SQL
CREATE TABLE IF NOT EXISTS `WMS_LAYERS_HIST` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ID_USERS2` int(11) NOT NULL,
  `the_geom` text,
  `ID_PROJECT` varchar(20) DEFAULT 'N/S;',
  `LAYER_NAME` varchar(100) DEFAULT 'N/S;',
  `LAYER_TYPE` varchar(10) DEFAULT 'N/S;',
  `OLD_LAYER_TYPE` varchar(10) DEFAULT 'N/S;',
  `LAYER_DESC` varchar(10000) DEFAULT 'N/S;',
  `ID_WMS_MAPS` varchar(20) DEFAULT 'N/S;',
  `OLD_ID_WMS_MAPS` varchar(20) DEFAULT 'N/S;',
  `FILE_NAME` varchar(255) DEFAULT 'N/S;',
  `FILE_STATUS` varchar(20) DEFAULT 'N/S;',
  `FILE_STATUS_INFO` varchar(255) DEFAULT 'N/S;',
  `EPSG` varchar(10) DEFAULT 'N/S;',
  `EXTENT` varchar(100) DEFAULT 'N/S;',
  `MAP_STATUS` varchar(20) DEFAULT 'N/S;',
  `FILE_MD5` varchar(32) DEFAULT 'N/S;',
  `A_RECORD_CREATE_DATE` varchar(30) DEFAULT 'N/S;',
  `A_RECORD_CREATE_AUTHOR` varchar(40) DEFAULT 'N/S;',
  `A_RECORD_UPDATE_DATE` varchar(18) DEFAULT 'N/S;',
  `A_RECORD_UPDATE_AUTHOR` varchar(40) DEFAULT 'N/S;',
  `L_APPOITMENT_DATE` varchar(30) DEFAULT 'N/S;',
  `L_APPOITMENT_USER` varchar(40) DEFAULT 'N/S;',
  `L_APPOITMENT_PERIOD` varchar(30) DEFAULT 'N/S;',
  `L_APPOITMENT_INFO` varchar(200) DEFAULT 'N/S;',
  `L_APPOITMENT_TYPE` varchar(255) DEFAULT 'N/S;',
  `A_PROBLEM` varchar(255) DEFAULT 'N/S;',
  `A_PROBLEM_DESC` varchar(200) DEFAULT 'N/S;',
  `A_PROBLEM_DATE` varchar(255) DEFAULT 'N/S;',
  `A_STATUS` varchar(255) DEFAULT 'N/S;',
  `A_STATUS_INFO` varchar(100) DEFAULT 'N/S;',
  `A_STATUS_LASTCHANGE_DATE` varchar(255) DEFAULT 'N/S;',
  `A_ADM_COMPANY` varchar(100) DEFAULT 'N/S;',
  `A_CLASSIFIED` varchar(100) DEFAULT 'N/S;',
  `SYNC_SQIX_STATUS` varchar(100) DEFAULT 'N/S;',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2;
SQL;

		$sqlList['InstallMapTable'] = <<<SQL
CREATE TABLE IF NOT EXISTS `WMS_MAPS` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `MAP_NAME` varchar(100) NOT NULL,
  `MAP_DESC` text NOT NULL,
  `MAP_STATUS` enum('NONE','TO_GENERATE','IN_PROGRESS','GENERATED','ERROR') NOT NULL DEFAULT 'NONE',
  `MAP_STATUS_INFO` varchar(255) NOT NULL,
  `MAP_URL` varchar(100) NOT NULL,
  `MAP_FILE` varchar(100) NOT NULL,
  `A_RECORD_CREATE_DATE` datetime NOT NULL,
  `A_RECORD_CREATE_AUTHOR` varchar(40) NOT NULL,
  `A_RECORD_UPDATE_DATE` varchar(18) NOT NULL,
  `A_RECORD_UPDATE_AUTHOR` varchar(40) NOT NULL,
  `L_APPOITMENT_DATE` datetime NOT NULL,
  `L_APPOITMENT_USER` varchar(40) NOT NULL,
  `L_APPOITMENT_PERIOD` varchar(30) NOT NULL,
  `L_APPOITMENT_INFO` varchar(200) NOT NULL,
  `L_APPOITMENT_TYPE` enum('','ARRANGED','TO_ARRANGE','CANCELLED','TO_CANCEL','RELATE','WAIT') NOT NULL,
  `A_PROBLEM` enum('','WARNING','PROBLEM','SERIOUS','UNVERIFIED') NOT NULL,
  `A_PROBLEM_DESC` varchar(200) NOT NULL,
  `A_PROBLEM_DATE` datetime NOT NULL,
  `A_STATUS` enum('DELETED','MONITOR','NORMAL','OFF_HARD','OFF_SOFT','WAITING','WARNING') NOT NULL DEFAULT 'WAITING',
  `A_STATUS_INFO` varchar(100) NOT NULL,
  `A_STATUS_LASTCHANGE_DATE` datetime NOT NULL,
  `A_ADM_COMPANY` varchar(100) NOT NULL,
  `A_CLASSIFIED` varchar(100) NOT NULL,
  `SYNC_SQIX_STATUS` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
SQL;

		$sqlList['InstallMapHistTable'] = <<<SQL
CREATE TABLE IF NOT EXISTS `WMS_MAPS_HIST` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ID_USERS2` int(11) NOT NULL,
  `MAP_NAME` varchar(100) DEFAULT 'N/S;',
  `MAP_DESC` varchar(10000) NOT NULL DEFAULT 'N/S;',
  `MAP_STATUS` varchar(20) NOT NULL DEFAULT 'N/S;',
  `MAP_STATUS_INFO` varchar(255) NOT NULL DEFAULT 'N/S;',
  `MAP_URL` varchar(100) NOT NULL DEFAULT 'N/S;',
  `MAP_FILE` varchar(100) NOT NULL DEFAULT 'N/S;',
  `A_RECORD_CREATE_DATE` varchar(30) DEFAULT 'N/S;',
  `A_RECORD_CREATE_AUTHOR` varchar(40) DEFAULT 'N/S;',
  `A_RECORD_UPDATE_DATE` varchar(18) DEFAULT 'N/S;',
  `A_RECORD_UPDATE_AUTHOR` varchar(40) DEFAULT 'N/S;',
  `L_APPOITMENT_DATE` varchar(30) DEFAULT 'N/S;',
  `L_APPOITMENT_USER` varchar(40) DEFAULT 'N/S;',
  `L_APPOITMENT_PERIOD` varchar(30) DEFAULT 'N/S;',
  `L_APPOITMENT_INFO` varchar(200) DEFAULT 'N/S;',
  `L_APPOITMENT_TYPE` varchar(255) DEFAULT 'N/S;',
  `A_PROBLEM` varchar(255) DEFAULT 'N/S;',
  `A_PROBLEM_DESC` varchar(200) DEFAULT 'N/S;',
  `A_PROBLEM_DATE` varchar(255) DEFAULT 'N/S;',
  `A_STATUS` varchar(255) DEFAULT 'N/S;',
  `A_STATUS_INFO` varchar(100) DEFAULT 'N/S;',
  `A_STATUS_LASTCHANGE_DATE` varchar(255) DEFAULT 'N/S;',
  `A_ADM_COMPANY` varchar(100) DEFAULT 'N/S;',
  `A_CLASSIFIED` varchar(100) DEFAULT 'N/S;',
  `SYNC_SQIX_STATUS` varchar(100) DEFAULT 'N/S;',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
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()));
			}
		}
	}


}