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('Core_AclHelper');

// - [ ] 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);

			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());
		}
		if (1 != V::get('_print', '', $_GET)) UI::dol();
	}

	public function syncStataAjaxAction() {
		Response::sendTryCatchJson(array($this, 'syncStataAjaxReponseCallback'), $args = 'JSON_FROM_REQUEST_BODY');// try to read json from request body
	}
	public function syncStataAjaxReponseCallback($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::getAclByNamespace("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(Core_AclHelper::getAclByNamespace($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 = Core_AclHelper::getAclByNamespace($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);

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

		$acl = Core_AclHelper::getAclByNamespace("default_db/ZALICZKA/Zaliczka");

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

		UI::table([
			'caption' => "Zaliczki pracownika {$user['name']} " . '<br>' . UI::hButtonPost("Dodaj nową zaliczkę", [
				'data' => [
					'_route' => "UrlAction_UserProNetMediaZaliczka",
					'_task' => "add",
					'idUser' => $idUser,
				],
				'fields' => [
					[ 'name' => "kwota", 'type' => 'text', 'class' => 'form-control input-sm', 'style' => "width:200px", 'placeholder' => "kwota" ]
				],
				'class' => 'btn-sm btn-primary',
				'form.style' => 'display:block; margin-top:4px'
			]),
			'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,
		]);

		UI::endContainer();
	}

	public function addAction() {
		$idUser = V::get('idUser', User::getID(), $_REQUEST);
		$kwota = V::get('kwota', 0, $_REQUEST, 'price');
		try {
			$acl = Core_AclHelper::getAclByNamespace("default_db/ZALICZKA/Zaliczka");
			$id = $acl->addItem([
				'kwota' => $kwota,
				'worker' => [
					'id' => $idUser
				]
			]);
			if (!$id) throw new Exception("Nie udało się utworzyć zaliczki");
		} 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}&idZaliczka={$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 {
			Core_AclHelper::getAclByNamespace("default_db/ZALICZKA/Zaliczka")->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",
				'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\_\_%'
							)
						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
							, t.A_STATUS
							, t.L_APPOITMENT_USER
							, t.KWOTA
							, t.NIEROZLICZONA_KWOTA
							, sum(COALESCE(p.kwota, 0)) as rozliczona_kwota
					from ZALICZKA t
						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
				");

				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::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 = Core_AclHelper::getAclByNamespace("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=syncStataAjax&idUser={$idUser}&idZaliczka={$idZaliczka}",
			'DBG' => (DBG::isActive())
		]);
		UI::tag('script', ['src'=>'static/vendor.js' . (V::get('DBG', '', $_GET) ? '?_ts=' . time() : ''), 'type'=>'text/javascript']);
		UI::tag('script', ['src'=>'static/zaliczka/main.js' . (V::get('DBG', '', $_GET) ? '?_ts=' . time() : ''), 'type'=>'text/javascript']);
	}

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

			$acl = Core_AclHelper::getAclByNamespace("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;


*/