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

<?php

Lib::loadClass('RouteBase');
Lib::loadClass('Typespecial');
Lib::loadClass('TypespecialVariable');
Lib::loadClass('Request');
Lib::loadClass('Response');
Lib::loadClass('ProcesHelper');
Lib::loadClass('Route_UrlAction');
Lib::loadClass('UI');
Lib::loadClass('ACL');
Lib::loadClass('TableAjax');

// - [ ] zaliczkaListaView:
//   - [ ] btn "Dodaj Zaliczkę" $acl->addItem($item); // @require only 'worker' => [ 'id', 'login', 'name' ]
//   - [ ] lista wszystkich zaliczek danego usera + btn "load more ..."
//   - [x] Rozlicz / Podgląd - tylko najnowsza Zaliczka
//   - [ ] Rozlicz / Podgląd - edytowć może tylko właściciel / grupa / user connected by REF ?

class Route_UrlAction_UserProNetMediaZaliczka extends RouteBase {// TODO: UrlActionBase @see Route_UrlAction

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

	public function defaultAction() {
		UI::gora();
		// UI::emptyTag('link', ['href'=>'static/zaliczka/main.css', 'rel'=>'stylesheet']);
		if (1 != V::get('_print', '', $_GET)) UI::menu();
		try {
			$data = array();
			$data['idUser'] = V::get('idUser', User::getID(), $_REQUEST, 'int');
			$data['idZaliczka'] = V::get('idZaliczka', 0, $_REQUEST, 'int');// TODO: idZaliczka is always MAX(z.ID);
			$data['tab'] = V::get('tab', 'wnoiski', $_REQUEST, 'word');

			if (!$data['idZaliczka']) {
				$this->zaliczkaListaView($data);
			} else {
				echo UI::h('div', ['class' => "container", 'style' => 'margin-top:12px; margin-bottom:6px; padding-bottom:6px; border-bottom:1px solid #ddd'], [
					UI::h('a', [
						'href' => "index.php?_route=UrlAction_UserProNetMediaZaliczka&idUser={$data['idUser']}",
						'class' => "btn btn-link pull-left"
					], "<i class=\"glyphicon glyphicon-arrow-left\"></i> wróć"),
					UI::h('a', [
						'href' => "index.php?_route=ViewTableAjax&typeName=p5_default_db:IN7_DZIENNIK_KORESP#CREATE",
						'class' => "btn btn-sm btn-default pull-right"
					], "<span class=\"glyphicon glyphicon-plus\"></span> Dodaj dokument <i>(Korespondencję)</i>")
				]);
				$this->zaliczkaFormView($data);
			}
		} catch (Exception $e) {
			UI::alert('danger', "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage());
			DBG::log($e);
		}
		if (!empty($_POST)) echo UI::h('script', [], "history.replaceState(null, 'Zaliczka', window.location.href);");
		if (1 != V::get('_print', '', $_GET)) UI::dol();
	}

	public function syncStateAjaxAction() {
		Response::sendTryCatchJson(array($this, 'syncStateAjaxReponseCallback'), $args = 'JSON_FROM_REQUEST_BODY');// try to read json from request body
	}
	public function syncStateAjaxReponseCallback($args) {
		if (empty($args) || !array_key_exists('updates', $args)) throw new HttpException("Error Parsing Request - missing args", 400);
		$args['idUser'] = V::get('idUser', 0, $_REQUEST, 'int');// (int)$args['idUser'];
		if ($args['idUser'] <= 0) throw new HttpException("Error Parsing Request - missing idUser", 400);
		$args['idZaliczka'] = V::get('idZaliczka', 0, $_REQUEST, 'int');// (int)$args['idZaliczka'];
		if ($args['idZaliczka'] <= 0) throw new HttpException("Error Parsing Request - missing idZaliczka", 400);

		$acl = ACL::getStorageByNamespace("default_db/ZALICZKA/Zaliczka");
		$schema = $acl->getSimpleSchemaTree();
		if (!empty($args['updates'])) {
			$acl->updateItem($args['updates']);
		}

		$zaliczkaData = $acl->getItem($args['idZaliczka'], [
			'cols' => [
				'@instance',
				'id',
				'created',
				'kwota',
				'nierozliczona_kwota',
				'pozycja',
				'worker',
				'korespondencja',
			]
		]);// $this->fetchDataBySchema($schema, $args['idUser']);
		return [
			'type' => 'success',
			'nierozliczonaKwota' => V::get('nierozliczona_kwota', 0, $zaliczkaData),
			'schema' => [
				'zaliczka' => $schema,
			],
			'zaliczka' => $zaliczkaData,
		];
	}

	public function fetchDataAjaxAction() {
		Response::sendTryCatchJson(array($this, 'fetchDataAjaxReponseCallback'), $args = 'JSON_FROM_REQUEST_BODY');// try to read json from request body
	}
	public function fetchDataAjaxReponseCallback($args) {
		DBG::log(['msg' => 'args', 'args' => $args]);
		if (empty($args)) throw new HttpException("Error Parsing Request - missing args", 400);
		if (empty($args['schema']['@namespace']) && empty($args['objectNamespace'])) throw new HttpException("Error Parsing Request - missing @namespace", 400);
		if (!empty($args['schema']['@namespace'])) {
			if ('default_db/IN7_DZIENNIK_KORESP/ZaliczkaKoresp' == $args['schema']['@namespace']) {
				return [
					'type' => 'success',
					'options' => array_values(ACL::getStorageByNamespace($args['schema']['@namespace'])->getItems([
						'f_title' => V::get('query', '', $args)
					]))
				];
			} else {
				return [ 'type' => "error", 'msg' => "Unsupported @namespace" ];
			}
		} else if (!empty($args['objectNamespace'])) {
			// childName : "kategoria_kosztu"
			// objectNamespace : "default_db/ZALICZKA_POZYCJA/ZaliczkaPozycja"
			if (empty($args['childName'])) throw new Exception("Missing childName");

			$acl = ACL::getStorageByNamespace($args['objectNamespace']);
			return [
				'type' => 'success',
				'options' => $acl->getEnumValues($args['childName'])
			];
		}
		return [ 'type' => "error", 'msg' => "Unsupported @namespace or xsdType" ];
	}

	public function fetchDataBySchema($schema, $idUser, $primaryKey = null) {// if $primaryKey is null then search for last row
		return [// TODO: fetch data from DB
			'id' => 123,
			'created' => '2016-11-22', // data wydania zaliczki - data utworzenia rekordu
			'worker' => [
				'id' => 666,
				'name' => 'Kowalski', // 'TODO' => get name from admin_users - imię i nazwisko pracownika pobierającego zaliczkę
				'login' => 'kowalski'
			],
			'kwota' => 555.55, // kwota wypłaconej zaliczki
			'nierozliczona_kwota' => 123.56,
			'pozycja' => [
				[ 'id' => 1,
					'kwota' => 100.22,
					'korespondencja' => [ 'id' => 65432, 'title' => 'testowa koresp 1', 'kategoriaKosztu' => 'KOSZT1' ],
					'projekt' => [ 'nrBudowy' => 'Budowa/1' ]
				],
				[ 'id' => 2,
					'kwota' => 200.55,
					'korespondencja' => [ 'id' => 76543, 'title' => 'testowa koresp 2', 'kategoriaKosztu' => 'KOSZT2' ],
					'projekt' => [ 'id' => 123, 'nrBudowy' => 'Budowa/2' ]
				]
			]
		];
	}

	public function zaliczkaListaView($data) {
		$idUser = V::get('idUser', User::getID(), $data);
		$tab = V::get('tab', 'wnioski', $data);
		if (!in_array($tab, ['wnioski', 'zaliczki'])) $tab = 'wnioski';

		$ownerAcl = ACL::getStorageByNamespace("default_objects/AccessOwner");
		$user = $ownerAcl->getItem($idUser);// [id, login, name]
		if (!$user) throw new Exception("Nie znaleziono pracownika o nr '{$idUser}'");

		$acl = ACL::getStorageByNamespace("default_db/ZALICZKA/Zaliczka");

		UI::startContainer(['style' => "padding-top:20px"]);
		UI::setTitle("Zaliczka");

		echo UI::h('h2', [], "Zaliczki pracownika {$user['name']} ");
		echo UI::hButtonPost("Dodaj wniosek o zaliczkę", [
			'data' => [
				'_route' => "UrlAction_UserProNetMediaZaliczka",
				'_task' => 'add',
				'idUser' => $idUser,
			],
			'class' => "btn-sm btn-primary",
			'form.style' => "display:block; margin-top:4px; padding:20px; background:#eee"
		], [
			UI::h('input', [ 'name' => "kwota", 'type' => 'text', 'class' => 'form-control input-sm', 'style' => "width:200px;margin-right:4px", 'placeholder' => "kwota" ]),
			UI::h('div', ['class'=>"input-group", 'style'=>"margin-right:4px"], [
				UI::h('input', [ 'name' => "uwagi", 'type' => 'text', 'class' => 'form-control input-sm', 'style' => "width:400px", 'placeholder' => "uwagi" ]),
				UI::h('span', ['class'=>"input-group-btn"], [
					UI::h('button', ['class'=>"btn btn-sm btn-default", 'onClick' => "return false"], [
						UI::h('i', [
							'class' => "glyphicon glyphicon-question-sign",
							'title' => "Czego dotyczy zaliczka?"
						])
					])
				]),
			]),
		]);

		echo UI::h('ul', ['class' => "nav nav-tabs", 'style' => "margin-top:24px"], [
			UI::h('li', ['role' => "presentation", 'class' => ('wnioski' == $tab) ? "active" : ''], [
				UI::h('a', ['href'=>Request::getPathUri() . "index.php?_route=UrlAction_UserProNetMediaZaliczka&idUser={$idUser}&tab=wnioski"], "Wnioski")
			]),
			UI::h('li', ['role' => "presentation", 'class' => ('zaliczki' == $tab) ? "active" : ''], [
				UI::h('a', ['href'=>Request::getPathUri() . "index.php?_route=UrlAction_UserProNetMediaZaliczka&idUser={$idUser}&tab=zaliczki"], "Zaliczki")
			]),
		]);
		if ('wnioski' == $tab) {
			$this->viewWidgetUserWnioski($idUser);
		} else if ('zaliczki' == $tab) {
			$this->viewWidgetUserZaliczki($idUser);
		}
		UI::endContainer();
	}

	public function viewWidgetUserWnioski($idUser) {
		if (!$idUser) throw new Exception("Błędny numer pracownika");
		// $workerLogin = DB::getPDO()->fetchValue("select u.ADM_ACCOUNT from ADMIN_USERS u where u.ID={$idUser} limit 1");
		// if (!$workerLogin) throw new Exception("Pracownik nie został odnaleziony - nr '{$idUser}'");
		// $acl = ACL::getStorageByNamespace("default_db/ZALICZKA_WNIOSEK/ZaliczkaWniosek");
		$acl = ACL::getStorageByNamespace("default_db/zaliczka_wniosek_info_view");
		$syncUrl = Request::getPathUri() . 'index.php?_route=ViewTableAjax&namespace=' . $acl->getNamespace();
		$tbl = new TableAjax($acl);
		$tblLabel = $acl->getNamespace();
		if ('default_db' == $acl->getSourceName()) {
			$tblLabel = array();
			$zasobObj = ProcesHelper::getZasobTableInfo($acl->getID());
			if (!$zasobObj) throw new Exception("Zasob TABELA ID=" . $acl->getID() . " nie istnieje");
			if (!empty($zasobObj->DESC_PL)) $tblLabel[] = $zasobObj->DESC_PL;
			if (!empty($zasobObj->OPIS))    $tblLabel[] = $zasobObj->OPIS;
			$tblLabel = implode(" - ", $tblLabel);
		}
		$tbl->setSyncUrl($syncUrl);
		$tbl->setLabel($tblLabel);
		$tbl->setRowFunctions([]);

		$tbl->setFilterInit([
			'currSortCol' => "created",
			'currSortFlip' => "desc"
		]);
		$tbl->setForceFilterInit([
			'ID_USER' => $idUser
			// 'workerLogin' => $workerLogin
		]);
		echo $tbl->render();
	}

	public function viewWidgetUserZaliczki($idUser) {
		if (!$idUser) throw new Exception("Błędny numer pracownika");
		$workerLogin = DB::getPDO()->fetchValue("select u.ADM_ACCOUNT from ADMIN_USERS u where u.ID={$idUser} limit 1");
		if (!$workerLogin) throw new Exception("Pracownik nie został odnaleziony - nr '{$idUser}'");
		$acl = ACL::getStorageByNamespace("default_db/ZALICZKA_INFO_VIEW");// 'default_db/ZALICZKA/Zaliczka'
		$syncUrl = Request::getPathUri() . 'index.php?_route=ViewTableAjax&namespace=' . $acl->getNamespace();
		$tbl = new TableAjax($acl);
		$tblLabel = $acl->getNamespace();
		if ('default_db' == $acl->getSourceName()) {
			$tblLabel = array();
			$zasobObj = ProcesHelper::getZasobTableInfo($acl->getID());
			if (!$zasobObj) throw new Exception("Zasob TABELA ID=" . $acl->getID() . " nie istnieje");
			if (!empty($zasobObj->DESC_PL)) $tblLabel[] = $zasobObj->DESC_PL;
			if (!empty($zasobObj->OPIS))    $tblLabel[] = $zasobObj->OPIS;
			$tblLabel = implode(" - ", $tblLabel);
		}
		$tbl->setSyncUrl($syncUrl);
		$tbl->setLabel($tblLabel);
		$tbl->setRowFunctions([]);

		$tbl->setFilterInit([
			'currSortCol' => "ID",
			'currSortFlip' => "desc"
		]);
		$tbl->setForceFilterInit([
			'ID_USER' => $idUser
		]);
		echo $tbl->render();

		// $acl = ACL::getStorageByNamespace("default_db/ZALICZKA/Zaliczka");
		// UI::table([
		// 	'caption' => "Zaliczki",
		// 	'rows' => array_map(
		// 		function ($row) use ($idUser) {
		// 			$instance = DB::getPDO()->fetchValue("select INSTANCE_NAME from `ZALICZKA__#INSTANCE` where PRIMARY_KEY = {$row['id']}");
		// 			$editLink = Request::getPathUri() . "index.php?_route=UrlAction_UserProNetMediaZaliczka&idUser={$idUser}&idZaliczka={$row['id']}";
		// 			$editLabel = ('ZaliczkaAktywna' == $instance) ? "Rozlicz / Podgląd" : "Podgląd";
		// 			$histLink = Request::getPathUri() . "index.php?_route=UrlAction_UserProNetMediaZaliczka&_task=hist&idUser={$idUser}&idZaliczka={$row['id']}";
		// 			return [
		// 				'Nr' => $row['id'],
		// 				'Typ' => $instance,
		// 				'Pracownik' => $row['L_APPOITMENT_USER'],
		// 				'Kwota' => $row['kwota'],
		// 				'Nierozliczona kwota' => $row['nierozliczona_kwota'],
		// 				'#' => '<a href="' . $editLink . '">' . $editLabel . '</a>' . (V::get('DBG', '', $_GET)
		// 					? '<a href="' . $histLink . '">' . "DBG HIST" . '</a>'
		// 					: ''
		// 				),
		// 			];
		// 		},
		// 		$acl->getItems([
		// 			'#refFrom' => [
		// 				'namespace' => 'default_db__x3A__ADMIN_USERS/Worker',
		// 				'primaryKey' => $idUser
		// 			]
		// 		])
		// 	),
		// 	'empty_msg' => "Brak",
		// 	'disable_lp' => true,
		// ]);
	}

	public function addAction() {
		$idUser = V::get('idUser', User::getID(), $_REQUEST, 'int');
		$kwota = V::get('kwota', 0, $_REQUEST, 'price');
		$uwagi = V::get('uwagi', '', $_REQUEST);
		try {
			if (!$idUser) throw new Exception("Błędny numer pracownika");
			$workerLogin = DB::getPDO()->fetchValue("select u.ADM_ACCOUNT from ADMIN_USERS u where u.ID={$idUser} limit 1");
			if (!$workerLogin) throw new Exception("Pracownik nie został odnaleziony - nr '{$idUser}'");
			$acl = ACL::getStorageByNamespace("default_db/ZALICZKA_WNIOSEK/ZaliczkaWniosek");
			$id = $acl->addItem([
				'kwota' => $kwota,
				'uwagi' => $uwagi,
				'workerLogin' => $workerLogin
			]);
			if (!$id) throw new Exception("Nie udało się utworzyć wniosku o zaliczkę");
		} catch (Exception $e) {
			UI::gora();
			UI::startContainer();
				UI::alert('danger', $e->getMessage() . UI::h('p', [], [
					UI::h('a', [ 'href' => "index.php?_route=UrlAction_UserProNetMediaZaliczka&idUser={$idUser}" ], [
						'<i class="glyphicon glyphicon-arrow-left"></i>',
						" wróć",
					])
				]));
			UI::endContainer();
			UI::dol();
			DBG::log($e);
			exit;
		}

		$redirectUrl = Request::getPathUri() . "index.php?_route=UrlAction_UserProNetMediaZaliczka&idUser={$idUser}";// TODO:? &idWniosek={$id}
		if (!headers_sent()) {
			header("Location: {$redirectUrl}");
		} else {
			echo'<script type="text/javascript">'."
				window.location.href='{$redirectUrl}';
			".'</script>';
			echo "\n".'<noscript>';
			echo "\n".'<meta http-equiv="refresh" content="0;url='.$redirectUrl.'" />';
			echo "\n".'</noscript>';
			echo'<p>'.'<a href="'.$redirectUrl.'">'."dalej".'</a>'.'</p>';
		}
	}

	public function reinstallAction() {
		UI::gora();
		try {
			ACL::getStorageByNamespace("default_db/ZALICZKA/Zaliczka")->reinstall();
			ACL::getStorageByNamespace("default_db/ZALICZKA_WNIOSEK/ZaliczkaWniosek")->reinstall();
			UI::startContainer();
			UI::alert('success', "Structure for 'default_db/ZALICZKA/Zaliczka' created in a database");
			$backUrl = Request::getPathUri() . "index.php?_route=UrlAction_UserProNetMediaZaliczka";
			UI::tag('a', ['href'=>$backUrl, 'class'=>"btn btn-primary"], "wróć");

			$databaseName = DB::getPDO()->getDatabaseName();
			UI::table([
				'caption' => "Zaliczka Wniosek",
				'rows' => DB::getPDO()->fetchAll("
					select t.TABLE_NAME, t.COLUMN_NAME, t.DATA_TYPE, t.COLUMN_TYPE
					from `information_schema`.`COLUMNS` t
					where t.TABLE_SCHEMA = '{$databaseName}'
						and (t.TABLE_NAME like 'ZALICZKA_WNIOSEK'
							or t.TABLE_NAME like 'ZALICZKA_WNIOSEK_HIST'
						)
					order by t.TABLE_NAME asc, t.COLUMN_NAME asc
				")
			]);

			UI::table([
				'caption' => "Zaliczka",
				'rows' => DB::getPDO()->fetchAll("
					select t.TABLE_NAME, t.COLUMN_NAME, t.DATA_TYPE, t.COLUMN_TYPE
					from `information_schema`.`COLUMNS` t
					where t.TABLE_SCHEMA = '{$databaseName}'
						and (t.TABLE_NAME like 'ZALICZKA'
							or t.TABLE_NAME like 'ZALICZKA\_\_%'
							or t.TABLE_NAME like 'ZALICZKA_HIST'
							or t.TABLE_NAME like 'Zaliczka\_\_%'
						)
					order by t.TABLE_NAME asc, t.COLUMN_NAME asc
				")
			]);

			UI::table([
				'caption' => "Zaliczka Pozycja",
				'rows' => DB::getPDO()->fetchAll("
					select t.TABLE_NAME, t.COLUMN_NAME, t.DATA_TYPE, t.COLUMN_TYPE
					from `information_schema`.`COLUMNS` t
					where t.TABLE_SCHEMA = '{$databaseName}'
						and (t.TABLE_NAME like 'ZALICZKA_POZYCJA'
							or t.TABLE_NAME like 'ZALICZKA_POZYCJA\_\_%'
							or t.TABLE_NAME like 'ZALICZKA_POZYCJA_HIST'
							or t.TABLE_NAME like 'ZaliczkaPozycja\_\_%'
						)
					order by t.TABLE_NAME asc, t.COLUMN_NAME asc
				")
			]);

			$tableList = array_map(
				function($item) {
					return $item['TABLE_NAME'];
				}
				, DB::getPDO()->fetchAll("
						select t.TABLE_NAME
						from `information_schema`.`COLUMNS` t
						where t.TABLE_SCHEMA = '{$databaseName}'
							and (t.TABLE_NAME like 'ZALICZKA'
								or t.TABLE_NAME like 'ZALICZKA\_\_%'
								or t.TABLE_NAME like 'ZALICZKA_HIST'
								or t.TABLE_NAME like 'Zaliczka\_\_%'
								or t.TABLE_NAME like 'ZALICZKA_POZYCJA'
								or t.TABLE_NAME like 'ZALICZKA_POZYCJA\_\_%'
								or t.TABLE_NAME like 'ZALICZKA_POZYCJA_HIST'
								or t.TABLE_NAME like 'ZaliczkaPozycja\_\_%'
								or t.TABLE_NAME like 'ZALICZKA_WNIOSEK'
								or t.TABLE_NAME like 'ZALICZKA_WNIOSEK_HIST'
							)
						group by t.TABLE_NAME
					")
			);
			foreach ($tableList as $tableName) {
				UI::startTag('div', ['style'=>"margin:10px 0; max-width:100%; overflow:scroll; border:1px solid #ccc"]);
				UI::table([
					'caption' => "Tabela '{$tableName}'",
					'rows' => DB::getPDO()->fetchAll("
						select t.*
						from `{$tableName}` t
					")
				]);
				UI::endTag('div');
			}

			if ('1' == V::get('_clear', '', $_GET)) {
				foreach ($tableList as $tableName) {
					DB::getPDO()->exec("TRUNCATE `{$tableName}`");
				}
				UI::tag('a', ['class'=>"btn btn-primary", 'href'=>"index.php?_route=UrlAction_UserProNetMediaZaliczka&_task=reinstall"], "Dane usunięte - odśwież stronę");
			} else {
				UI::tag('a', ['class'=>"btn btn-danger", 'href'=>"index.php?_route=UrlAction_UserProNetMediaZaliczka&_task=reinstall&_clear=1"], "Wyczyść tabele");
			}

			UI::startTag('pre');
			foreach ($tableList as $tableName) {
				echo "DROP TABLE `{$tableName}`;\n";
			}
			UI::endTag('pre');

			ACL::getRefTable('default_db/ZALICZKA/Zaliczka', 'worker');
			ACL::getRefTable('default_db/ZALICZKA/Zaliczka', 'pozycja');
			ACL::getRefTable('default_db/ZALICZKA/Zaliczka', 'korespondencja');
			$refZaliczkaTables = DB::getPDO()->fetchAll("
				select t.*
				from `CRM_REF_CONFIG` t
				where t.ROOT_OBJECT_NS like 'default_db/ZALICZKA%'
			");
			UI::startTag('div', ['style'=>"margin:10px 0; max-width:100%; overflow:scroll; border:1px solid #ccc"]);
			UI::table([
				'caption' => "Tabela 'CRM_REF_CONFIG'",
				'rows' => $refZaliczkaTables
			]);
			UI::endTag('div');
			foreach ($refZaliczkaTables as $refInfo) {
				$tableName = "CRM__#REF_TABLE__" . $refInfo['ID'];
				UI::startTag('div', ['style'=>"margin:10px 0; max-width:100%; overflow:scroll; border:1px solid #ccc"]);
				UI::table([
					'caption' => "Tabela REF '{$tableName}' - <small><i>{$refInfo['ROOT_OBJECT_NS']} REF {$refInfo['CHILD_NAME']} ({$refInfo['CHILD_NS']})</i></small>",
					'rows' => DB::getPDO()->fetchAll("
						select t.*
						from `{$tableName}` t
					")
				]);
				UI::endTag('div');
			}
			if ('1' == V::get('_clear', '', $_GET)) {
				foreach ($refZaliczkaTables as $refInfo) {
					$tableName = "CRM__#REF_TABLE__" . $refInfo['ID'];
					DB::getPDO()->exec("TRUNCATE `{$tableName}`");
				}
				UI::tag('a', ['class'=>"btn btn-primary", 'href'=>"index.php?_route=UrlAction_UserProNetMediaZaliczka&_task=reinstall"], "Dane usunięte - odśwież stronę");
			}

			{// view grouped info
				$refTableWorker = ACL::getRefTable('default_db/ZALICZKA/Zaliczka', 'worker');
				$refTablePozycja = ACL::getRefTable('default_db/ZALICZKA/Zaliczka', 'pozycja');
				DB::getPDO()->execSql("
					CREATE OR REPLACE VIEW `ZALICZKA_INFO_VIEW` AS
					select t.ID
							, u.ID as ID_USER
							, t.L_APPOITMENT_USER
							, t.A_STATUS
							, t.KWOTA
							, t.UWAGI
							, t.NIEROZLICZONA_KWOTA
							, t.APPROVED_BY
							, t.PAYMENT_METHOD
							, t.DOKUMENTY
							, sum(COALESCE(p.kwota, 0)) as rozliczona_kwota
					from ZALICZKA t
						join ADMIN_USERS u on(u.ADM_ACCOUNT = t.L_APPOITMENT_USER)
						left join `{$refTablePozycja}` refPoz on(refPoz.PRIMARY_KEY = t.ID and refPoz.A_STATUS != 'DELETED')
						left join ZALICZKA_POZYCJA p on(p.ID = refPoz.REMOTE_PRIMARY_KEY and (p.A_STATUS not in ('DELETED')))
					where t.A_STATUS not in ('DELETED')
					group by t.ID
				");
				DB::getPDO()->execSql("
					CREATE OR REPLACE VIEW `ZALICZKA_USER_SALDO_VIEW` AS
					select u.ID, u.ADM_ACCOUNT, u.ADM_NAME
						, (
							select (v.KWOTA + v.NIEROZLICZONA_KWOTA - v.rozliczona_kwota) as saldo
							from ZALICZKA_INFO_VIEW v
							where v.L_APPOITMENT_USER = u.ADM_ACCOUNT
							order by v.ID desc
							limit 1
						) as saldo
						, sum(t.KWOTA) as suma_zaliczek
					from ZALICZKA t
						join ADMIN_USERS u on(u.ADM_ACCOUNT = t.L_APPOITMENT_USER)
					where t.A_STATUS not in ('DELETED')
					group by u.ADM_ACCOUNT
				");
				DB::getPDO()->execSql("
					CREATE OR REPLACE VIEW `ZALICZKA_WNIOSEK_INFO_VIEW` AS
					select t.ID
							, t.A_RECORD_CREATE_DATE as created
							, t.A_RECORD_CREATE_DATE
							, t.A_RECORD_CREATE_AUTHOR
							, t.A_RECORD_UPDATE_DATE
							, t.A_RECORD_UPDATE_AUTHOR
							, u.ID as ID_USER
							, t.L_APPOITMENT_USER
							, t.A_STATUS
							, t.KWOTA
							, t.UWAGI
							, t.APPROVED_BY
							, IF('WAITING' = t.A_STATUS, 'Oczekuje zatwierdzenia',
									IF ('NORMAL' = t.A_STATUS, 'Zatwierdzony',
										IF ('OFF_HARD' = t.A_STATUS, 'Odrzucony',
											IF ('DELETED' = t.A_STATUS, 'Anulowany',
												'nieznany'
											)
										)
									)
								) as status
								, (select v.saldo from ZALICZKA_USER_SALDO_VIEW v where v.ID = u.ID) as SALDO
								, (select count(1) as cnt from ZALICZKA as zal where zal.L_APPOITMENT_USER = u.ADM_ACCOUNT and zal.DOKUMENTY = 'TAK') as DOKUMENTY_TAK
								, (select count(1) as cnt from ZALICZKA as zal where zal.L_APPOITMENT_USER = u.ADM_ACCOUNT and zal.DOKUMENTY = 'NIE') as DOKUMENTY_NIE
					from ZALICZKA_WNIOSEK t
						join ADMIN_USERS u on(u.ADM_ACCOUNT = t.L_APPOITMENT_USER)
					-- where t.A_STATUS not in ('DELETED')
					group by t.ID
				");

				UI::table([
					'caption' => "Table `ZALICZKA_USER_SALDO_VIEW`",
					'rows' => DB::getPDO()->fetchAll(" select * from `ZALICZKA_USER_SALDO_VIEW` ")
				]);
				UI::table([
					'caption' => "Table `ZALICZKA_INFO_VIEW`",
					'rows' => DB::getPDO()->fetchAll(" select * from `ZALICZKA_INFO_VIEW` ")
				]);
				UI::table([
					'caption' => "Table `ZALICZKA_INFO_VIEW` - debug (with deleted)",
					'rows' => DB::getPDO()->fetchAll("
						select t.ID
								, t.A_STATUS
								, t.L_APPOITMENT_USER
								, t.KWOTA
								, t.NIEROZLICZONA_KWOTA
								, IF(p.ID is null, 'NULL', p.ID) as p__ID
								, p.A_STATUS as p__A_STATUS
								, p.L_APPOITMENT_USER as p__L_APPOITMENT_USER
								, p.nr_faktury as p__nr_faktury
								, p.kwota as p__kwota
								, p.kwota_netto as p__kwota_netto
								, p.vat as p__vat
								, p.kategoria_kosztu as p__kategoria_kosztu
						from ZALICZKA t
							left join `{$refTablePozycja}` refPoz on(refPoz.PRIMARY_KEY = t.ID)
							left join ZALICZKA_POZYCJA p on(p.ID = refPoz.REMOTE_PRIMARY_KEY)
					")
				]);
				UI::table([
					'caption' => "Table `ZALICZKA_WNIOSEK_INFO_VIEW`",
					'rows' => DB::getPDO()->fetchAll(" select * from `ZALICZKA_WNIOSEK_INFO_VIEW` ")
				]);
			}

			Router::getRoute('UrlAction_UserProNetMediaZaliczkaWniosekConfirm')->reinstall();
			Router::getRoute('UrlAction_UserProNetMediaZaliczkaWniosekRemove')->reinstall();

			UI::endContainer();
		} catch (Exception $e) {
			UI::alert('danger', $e->getMessage());
			DBG::log($e);
		}
		UI::dol();
	}

	public function zaliczkaFormView($data) {
		$idUser = V::get('idUser', User::getID(), $data);
		$idZaliczka = V::get('idZaliczka', 0, $data);

		UI::tag('div', ['id'=>"zaliczka-app", 'data-dbg'=>V::get('DBG', '', $_GET), 'data-sync-js-function'=>"syncZaliczkaState", 'data-fetch-data-js-function'=>"zaliczkaFetchData"]);
		UI::emptyTag('br');

		$acl = ACL::getStorageByNamespace("default_db/ZALICZKA/Zaliczka");
		$schema = $acl->getSimpleSchemaTree();
		// DBG::nicePrint($schema, '$schema');

		UI::inlineJS(__FILE__ . '.fetchData.js', [
			'SYNC_URL' => Request::getPathUri() . "index.php?_route=UrlAction_UserProNetMediaZaliczka&_task=fetchDataAjax&idUser={$idUser}&idZaliczka={$idZaliczka}",
			'DBG' => (DBG::isActive())
		]);
		UI::inlineJS(__FILE__ . '.syncState.js', [
			'SYNC_URL' => Request::getPathUri() . "index.php?_route=UrlAction_UserProNetMediaZaliczka&_task=syncStateAjax&idUser={$idUser}&idZaliczka={$idZaliczka}",
			'DBG' => (DBG::isActive())
		]);
		UI::inlineJS(APP_PATH_WWW . '/static/vendor.js');
		UI::inlineJS(APP_PATH_WWW . '/static/zaliczka/main.js');
	}

	public function histAction() {
		UI::gora();
		// UI::menu();
		try {
			$idZaliczka = V::get('idZaliczka', 0, $_GET, 'int');
			if ($idZaliczka <= 0) throw new Exception("Missing idZaliczka");

			$acl = ACL::getStorageByNamespace("default_db/ZALICZKA/Zaliczka");
			$zaliczka = $acl->getItem($idZaliczka);
			if (!$zaliczka) throw new Exception("Zaliczka not found!");

			DBG::nicePrint($zaliczka, '$zaliczka');


		} catch (Exception $e) {
			UI::alert('danger', $e->getMessage());
		}
		UI::dol();
	}

}

/*    Akcje

# dodanie pierwszej zaliczki - 500 zł
$data = [ worker => [ login => 'Kowalski' ], kwota => 500.00 ]
insert into `ZALICZKA` (`L_APPOITMENT_USER`, `KWOTA`) values('{$data['worker']['login']}', '{$data['kwota']}');
-- return id = 123

# rozliczenie częściowe pierwszej zaliczki np. 500 zł
$data = [ id => 123, pozycja => [ [ kwota => 200.00, korespondencja => [ ... ], projekt => [ ... ] ] ] ]
update `ZALICZKA__#REF__POZYCJA` set `A_STATUS` = 'DELETED' where `PRIMARY_KEY` = 123;

# 2017-04-11 - pozycje view do exportu
create or replace view `ZaliczkiPozycjeVIEW` as
	SELECT z.ID as NR_ZALICZKI
	   , z.L_APPOITMENT_USER as ZALICZKA__L_APPOITMENT_USER -- Wnioskujacy
	   , z.APPROVED_BY as ZALICZKA__APPROVED_BY -- Zatwierdzil
	   , p.*
	FROM `ZALICZKA` z
		join `Zaliczka__#REF__pozycja` r on(r.PRIMARY_KEY = z.ID and r.A_STATUS not in ('DELETED'))
		join `ZALICZKA_POZYCJA` p on(p.ID = r.REMOTE_PRIMARY_KEY)

# 2017-05-15 - added typ_dokumentu
alter table `ZALICZKA_POZYCJA` add `typ_dokumentu` enum('faktura', 'inne') not null default 'inne';
alter table `ZALICZKA_POZYCJA_HIST` add `typ_dokumentu` varchar(16) not null default 'N/S;';

*/