se/SE/se-lib/Route/ViewTableAjax.php

<?php

Lib::loadClass('RouteBase');
Lib::loadClass('ProcesHelper');
Lib::loadClass('TableAjax');
// Lib::loadClass('Request');
Lib::loadClass('Response');
Lib::loadClass('UI');
Lib::loadClass('Api_WfsNs');
Lib::loadClass('Core_AclHelper');
Lib::loadClass('Route_UrlAction');
Lib::loadClass('Router');
Lib::loadClass('Typespecial');
Lib::loadClass('UserProfile');

class Route_ViewTableAjax extends RouteBase {

	public function getTableAjaxWidget($acl) {
		$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->addRowFunction('edit');
		$tbl->addRowFunction('hist');
		$tbl->addRowFunction('files');
		$tbl->addRowFunction('cp');
		$tbl->addRowFunction('msgs');
		return $tbl;
	}

	public function defaultAction() {
		UI::gora();
		UI::menu();
		try {
			$namespace = V::get('namespace', '', $_GET, 'word');
			if (!$namespace) {
				$typeName = V::get('typeName', '', $_GET, 'word');
				if (!$typeName) throw new Exception("Wrong param typeName");
				$namespace = Api_WfsNs::getBaseWfsUri() . '/' . str_replace(':', '/', $typeName);
			}
			$acl = Core_AclHelper::getAclByNamespace($namespace, $forceTblAclInit = ('1' == V::get('_force', '', $_GET)));

			$forceFilterInit = array();
			$filterInit = new stdClass();
			$filterInit->currSortCol = $acl->getPrimaryKeyField();
			$filterInit->currSortFlip = 'desc';
			foreach ($_GET as $k => $v) {
				if (strlen($k) > 3 && substr($k, 0, 2) == 'f_' && !empty($v)) {// filter prefix
					$filterInit->$k = $v;
				}
				else if (strlen($k) > 4 && substr($k, 0, 3) == 'sf_' && !empty($v)) {// special filter prefix
					$filterInit->$k = $v;
				}
				else if (strlen($k) > 4 && substr($k, 0, 3) == 'ff_' && !empty($v)) {// force filter prefix
					$fldName = substr($k, 3);
					$forceFilterInit[$fldName] = $v;
				}
			}

			$tbl = $this->getTableAjaxWidget($acl);
			$tbl->setFilterInit($filterInit);
			if (!empty($forceFilterInit)) $tbl->setForceFilterInit($forceFilterInit);
			echo $tbl->render();

			if (DBG::isActive() && V::get('DBG_ACL', '', $_GET)) {// test load perms
				Lib::loadClass('DebugExecutionTime');
				$dbgExecTime = new DebugExecutionTime();
				$dbgExecTime->activate();
				$dbgExecTime->log('start');
				UI::startContainer(['style'=>'border:1px solid red']);
				UI::tag('p', null, "TEST - load perms from db");
				$idTable = $acl->getID();
				UI::tag('p', null, "DBG idTable({$idTable})");
				if ($idTable > 0) {
					$dbgExecTime->log('before sql');
					$aclTableRows = DB::getPDO()->fetchAll("select * from `CRM_PROCES_idx_TABLE_TO_PROCES_PERMS_VIEW` where ID_TABLE = {$idTable}");
					$dbgExecTime->log('after sql', ['sql']);
					UI::table(['caption' => "from CRM_PROCES_idx_TABLE_TO_PROCES_PERMS_VIEW", 'rows' => $aclTableRows]);
					$csvIdProces = array();
					foreach ($aclTableRows as $row) {
						if (!in_array($row['ID_PROCES'], $csvIdProces)) $csvIdProces[] = $row['ID_PROCES'];
					}
				}

				$tableName = $acl->getName();
				$databaseName = DB::getPDO()->getDatabaseName();
				UI::table([
					'caption' => "Cell to process",
					'rows' => array_map(
						function ($row) use ($aclTableRows, $idTable) {
							$row['proces'] = array();
							$row['id_zasob'] = 0;
							$row['PERM_R'] = 0;
							$row['PERM_W'] = 0;
							$row['PERM_X'] = 0;
							$row['PERM_C'] = 0;
							$row['PERM_S'] = 0;
							$row['PERM_O'] = 0;
							$row['PERM_V'] = 0;
							$row['PERM_E'] = 0;
							foreach ($aclTableRows as $aclInfo) {
								if (strtolower($aclInfo['CELL_NAME']) == strtolower($row['COLUMN_NAME'])) {
									$row['proces'][] = $aclInfo['ID_PROCES'];
									$row['id_zasob'] = $aclInfo['ID_CELL'];
									$row['PERM_R'] += $aclInfo['PERM_R'];
									$row['PERM_W'] += $aclInfo['PERM_W'];
									$row['PERM_X'] += $aclInfo['PERM_X'];
									$row['PERM_C'] += $aclInfo['PERM_C'];
									$row['PERM_S'] += $aclInfo['PERM_S'];
									$row['PERM_O'] += $aclInfo['PERM_O'];
									$row['PERM_V'] += $aclInfo['PERM_V'];
									$row['PERM_E'] += $aclInfo['PERM_E'];
								}
							}
							$row['proces'] = (empty($row['proces']))
								? "<i style=\"color:red\">Brak</i>"
								: implode(", ", $row['proces']);
							if (!$row['id_zasob']) $row['id_zasob'] = DB::getPDO()->fetchValue("select ID from CRM_LISTA_ZASOBOW where `DESC` = '{$row['COLUMN_NAME']}' and PARENT_ID = {$idTable} limit 1");
							return $row;
						}, 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 '{$tableName}'
						")
					)
				]);

				if (!empty($csvIdProces)) {
					$csvIdProces = implode(",", $csvIdProces);
					UI::tag('p', null, "DBG csvIdProces({$csvIdProces})");
					$userLogin = User::getLogin();
					$dbgExecTime->log('before sql');
					$rows = DB::getPDO()->fetchAll("select ID_PROCES from `CRM_PROCES_idx_USER_to_PROCES_VIEW` where ADM_ACCOUNT = '{$userLogin}' and ID_PROCES in({$csvIdProces}) group by ID_PROCES");
					$dbgExecTime->log('after sql', ['sql']);
					UI::table(['caption' => "from CRM_PROCES_idx_USER_to_PROCES_VIEW", 'rows' => $rows]);
					$userIdProces = array(); foreach ($rows as $row) $userIdProces[] = $row['ID_PROCES'];
					$userTablePerms = array();
					foreach ($aclTableRows as $row) {
						if (!in_array($row['ID_PROCES'], $userIdProces)) continue;
						if (array_key_exists($row['CELL_NAME'], $userTablePerms)) {
							$userTablePerms[ $row['CELL_NAME'] ][ 'PERM_R' ] += $row['PERM_R'];
							$userTablePerms[ $row['CELL_NAME'] ][ 'PERM_W' ] += $row['PERM_W'];
							$userTablePerms[ $row['CELL_NAME'] ][ 'PERM_X' ] += $row['PERM_X'];
							$userTablePerms[ $row['CELL_NAME'] ][ 'PERM_C' ] += $row['PERM_C'];
							$userTablePerms[ $row['CELL_NAME'] ][ 'PERM_S' ] += $row['PERM_S'];
							$userTablePerms[ $row['CELL_NAME'] ][ 'PERM_O' ] += $row['PERM_O'];
							$userTablePerms[ $row['CELL_NAME'] ][ 'PERM_V' ] += $row['PERM_V'];
							$userTablePerms[ $row['CELL_NAME'] ][ 'PERM_E' ] += $row['PERM_E'];
						} else {
							$userTablePerms[ $row['CELL_NAME'] ] = $row;
							unset($userTablePerms[ $row['CELL_NAME'] ][ 'TABLE_DESCRIPTION' ]);
							unset($userTablePerms[ $row['CELL_NAME'] ][ 'ID_PROCES' ]);
							unset($userTablePerms[ $row['CELL_NAME'] ][ 'FORM_TREAT' ]);
						}
					}
					UI::table(['caption' => "\$userTablePerms", 'rows' => $userTablePerms]);
				} else UI::alert('warning', "brak \$csvIdProces");
				$dbgExecTime->printDebug();
				UI::endContainer();
			}
		} catch (Exception $e) {
			UI::startContainer();
			UI::alert('danger', "<strong>Wystąpiły błędy!</strong> " . $e->getMessage());
			UI::endContainer();
		}
		UI::dol();
	}

	public function revertFromHistAjaxAction() {
		Response::sendTryCatchJson(array($this, 'revertFromHistAjax'));
	}
	public function revertFromHistAjax() {
		$typeName = V::get('typeName', '', $_REQUEST, 'word');
		if (!$typeName) throw new Exception("Wrong param typeName");

		// TODO: use namespace from url
		// $namespace = V::get('namespace', '', $_GET, 'word');
		// if (!$namespace) {
		// 	$typeName = V::get('typeName', '', $_GET, 'word');
		// 	if (!$typeName) throw new Exception("Wrong param typeName");
		// 	$namespace = Api_WfsNs::getBaseWfsUri() . '/' . str_replace(':', '/', $typeName);
		// }
		// $acl = Core_AclHelper::getAclByNamespace($namespace, $forceTblAclInit = ('1' == V::get('_force', '', $_GET)));

		$id = V::get('ID', '', $_REQUEST, 'word');
		if (!$id) throw new Exception("Wrong param ID");
		$idHist = V::get('idHist', '', $_REQUEST, 'word');
		if (!$idHist) throw new Exception("Wrong param idHist");
		$fieldName = V::get('fieldName', '', $_REQUEST, 'word');
		if (!$fieldName) throw new Exception("Wrong param fieldName");
		$acl = Core_AclHelper::getAclByTypeName($typeName);

		$item = $acl->getItem($id);
		if (!$item) throw new HttpException("Item not found", 404);
		if (!$acl->canWriteObjectField($fieldName, $record)) throw new Exception("Missing perm Write for field {$fieldName}");
		$histItem = $acl->getHistItem($id, $idHist);
		if (!$histItem) throw new HttpException("Hist Item not found", 404);

		$histValue = V::get($fieldName, 'N/S;', $histItem);
		if ('N/S;' == $histValue) throw new Exception("Missing field value in hist[{$idHist}] for field({$fieldName}) from item[{$id}]");

		if ($acl->isGeomField($fieldName)) {
			$wktType = strtoupper($acl->getGeomFieldType($fieldName));
			if (!$wktType) throw new Exception("Wrong geometry type for field {$fieldName}");
			if ($wktType != strtoupper(substr($histValue, 0, strlen($wktType)))) throw new Exception("Wrong geometry type for field {$fieldName} in hist value");
			$coords = trim(substr($histValue, strlen($wktType)), '()');
			$wktValue = $acl->convertGmlCoordsToWkt($wktType, $coords, ['cs'=>' ', 'ts'=>',']);
			if (!$wktValue) throw new Exception("BUG in hist record");

			$sqlObj = array();
			$sqlObj['ID'] = $id;
			$sqlObj[$fieldName] = "GeomFromText('{$wktValue}')";
			$affected = DB::getDB()->UPDATE_OBJ($acl->getName(), (object)$sqlObj);
			if (0 == $affected) throw new AlertInfoException("Nie wprowadzono żadnych zmian");
			else if ($affected < 0) throw new Exception("Wystąpiły błędy podczas aktualizacji rekordu [{$id}]");
			$jsonResponse = array();
			$jsonResponse['type'] = 'success';
			$jsonResponse['msg'] = "Zaktualizowano dane na podstawie wcześniejszej wartości dla rekordu [{$id}]";
			$jsonResponse['actions'] = array();
			$jsonResponse['actions'][] = ['jsFunction'=>'TableAjax__HIST_Route', 'args'=>[$id]];
			return $jsonResponse;
		} else {
			throw new HttpException("Not implemented - update from hist only for the geom field", 501);
		}
		throw new Exception("BUG: update field '{$fieldName}' in item[{$id}] from hist[{$idHist}]", 501);
	}

	public function removeTheGeomAjaxAction() {
		Response::sendTryCatchJson(array($this, 'removeTheGeomAjax'), $args = 'JSON_FROM_REQUEST_BODY');
	}
	public function removeTheGeomAjax($args) {
		$namespace = V::get('namespace', '', $args, 'word');
		if (!$namespace) throw new HttpException("Bad Request - missing namespace", 400);
		$acl = Core_AclHelper::getAclByNamespace($namespace, $forceTblAclInit = ('1' == V::get('_force', '', $_GET)));
		$primaryKeyField = $acl->getPrimaryKeyField();
		$primaryKey = V::get($primaryKeyField, 0, $args, 'int');
		$geomFieldName = 'the_geom';
		$response = new stdClass();

		if ($primaryKey <= 0) throw new HttpException("Bad Request - Wrong param ID", 400);

		$record = $acl->getItem($primaryKey);
		if (!$record) throw new HttpException("Nie odnaleziono rekordu nr {$primaryKey}", 404);
		if (!$acl->canWriteObjectField($geomFieldName, $record)) throw new HttpException("Brak dostępu do zapisu dla pola {$geomFieldName}", 403);

		if (empty($record->{$geomFieldName})) {
			$response->type = 'info';
			$response->msg = "Rekord nie jest powiązany z żadnym obiektem na mapie";
			$response->record = $record;
			return $response;
		}

		$itemPatch = array();
		$itemPatch[$geomFieldName] = "NULL";
		$itemPatch[$primaryKeyField] = $primaryKey;

		$response = new stdClass();
		try {
			$affected = $acl->updateItem($itemPatch);

			if ($affected > 0) {
				$response->type = 'success';
				$response->msg = "Usunięto obiekt z mapy dla rekordu {$primaryKey}";// Rekord zapisany pomyślnie
			} else if ($affected == 0) {
				$response->type = 'info';
				$response->msg = "Nie wprowadzono żadnych zmian";
			}
			$response->record = $acl->getItem($primaryKey);
		}
		catch (Exception $e) {
			$response->type = 'error';
			$response->msg = $e->getMessage();
		}

		return $response;
	}

	public function moreFunctionsCellAjaxAction() {
		Response::sendTryCatchJson(array($this, 'moreFunctionsCell'), $args = $_GET);
	}
	public function moreFunctionsCell($args) {// ajax task 'MORE_FUNCTIONS_CELL'
		$id = V::get('ID', 0, $args, 'int');
		if ($id <= 0) throw new HttpException("404", 404);
		$namespace = V::get('namespace', '', $args, 'word');
		if (!$namespace) throw new HttpException("Bad Request - missing namespace", 400);
		$acl = Core_AclHelper::getAclByNamespace($namespace, $forceTblAclInit = ('1' == V::get('_force', '', $args)));

		$response = new stdClass();
		$response->type = 'success';
		$response->msg = 'Funkcje';
		$response->rowFunctions = Core_AclHelper::getMoreFunctionsCell($acl, array('primary_key' => $id));
		return $response;
	}

	public function editFormAction() {// namespace, _hash, _primaryKey
		$args = $_REQUEST;
		$id = V::get('_primaryKey', 0, $args, 'int');
		if ($id <= 0) throw new HttpException("Bad Request - missing primaryKey", 400);
		$namespace = V::get('namespace', '', $args, 'word');
		if (!$namespace) throw new HttpException("Bad Request - missing namespace", 400);
		$acl = Core_AclHelper::getAclByNamespace($namespace);
		$tbl = $this->getTableAjaxWidget($acl);
		$tbl->sendAjaxEdit($id, $args);
	}
	public function editSaveAjaxAction() {
		Response::sendTryCatchJson(array($this, 'editSaveAjax'), $args = 'JSON_FROM_REQUEST_BODY');
	}
	public function editSaveAjax($args) {
		$namespace = V::get('namespace', '', $args, 'word');
		if (!$namespace) throw new HttpException("Bad Request - missing namespace", 400);
		$acl = Core_AclHelper::getAclByNamespace($namespace);

		$primaryKeyField = $acl->getPrimaryKeyField();
		$primaryKey = V::get('primaryKey', 0, $args, 'int');
		if (empty($primaryKey)) throw new HttpException("Bad Request - missing primaryKey!", 400);

		$item = $acl->getItem($primaryKey);
		if (!$item) throw new HttpException("Item not exists!", 404);

		$itemFromUser = $acl->convertObjectFromUserInput($args['form'], $type = 'array_by_id', $prefix = 'f');

		$response = new stdClass();
		$response->primaryKey = $primaryKey;
		try {
			$itemFromUser[$primaryKeyField] = $primaryKey;
			$affected = $acl->updateItem($itemFromUser);

			if ($affected > 0) {
				$response->type = 'success';
				$response->msg = "Rekord zapisany pomyślnie";//"Record saved successfully";
			} else if ($affected == 0) {
				$response->type = 'info';
				$response->msg = "Nie wprowadzono żadnych zmian";
			}
			$response->record = $acl->getItem($primaryKey);
			$rowFunList = Core_AclHelper::getMoreFunctionsCell($acl, array('primary_key'=>$primaryKey, 'record'=>$response->record));
			if (!empty($rowFunList)) $response->rowFunctions = $rowFunList;
		}
		catch (Exception $e) {
			$response->type = 'error';
			$response->msg = "Wystąpiły błędy!";
			$response->msg .= $e->getMessage();
		}

		return $response;
	}

	public function typeSpecialCellAction() {
		Response::sendTryCatchJson(array($this, 'typeSpecialCell'), $args = $_REQUEST);
	}
	public function typeSpecialCell($args) {
		$namespace = V::get('namespace', '', $args, 'word');
		if (!$namespace) throw new HttpException("Bad Request - missing namespace", 400);
		$acl = Core_AclHelper::getAclByNamespace($namespace);
		$id = V::get('ID', 0, $args, 'int');
		$fieldName = V::get('col', '', $args);
		if ($id <= 0 || empty($fieldName)) throw new HttpException("Bad Request - missing id or col", 400);

		$col = $fieldName;// TODO: RM $col

		$jsonData = new stdClass();
		$idField = $acl->getFieldIdByName($fieldName);
		if (!$idField) throw new Exception("Wrong field");

		$item = $acl->getItem($id);
		if (!$acl->canReadObjectField($fieldName, $item)) throw new Exception("Brak dostępu");

		$typeSpecial = Typespecial::getInstance($idField, $fieldName);
		if ($typeSpecial) {
			$jsonData->data = $typeSpecial->getReturnData($acl->getID(), $id, $fieldName, '');
			$jsonData->namespace = 'default_db/' . V::get('tbl_name', '', $jsonData->data);
		}
		return $jsonData;
	}

	/**
	 * @param $_GET['namespace'] = AclNamespace
	 * @param $_GET['format'] = 'csv' | 'html'
	 * @param $_GET['flds'] = csv - coma separated field names
	 * @param $_GET['sortCol'] = FieldName
	 * @param $_GET['sortDir'] = SortDir ('desc' | 'asc')
	 * @param $_GET['f_{$fieldName}'] = filter
	 * @param $_GET['sf_{$fieldName}'] = force filter
	 */
	public function exportAction() {
		$args = $_GET;
		$namespace = V::get('namespace', '', $args, 'word');
		if (!$namespace) throw new HttpException("Bad Request - missing namespace", 400);
		$acl = Core_AclHelper::getAclByNamespace($namespace);

		$exportLimit = 10000;
		$params = array();
		$params['limit'] = $exportLimit;
		// $params['limitstart'] = 0;
		$params['order_by'] = V::get('sortCol', '', $args);
		$params['order_dir'] = V::get('sortDir', '', $args);
		$params['cols'] = array($acl->getPrimaryKeyField());
		$toExportFields = explode(',', V::get('flds', '', $_GET));
		if (empty($toExportFields)) throw new Exception("Nie wybrano żandych pól do exportu.");
		$allowedExportFieldList = Core_AclHelper::getExportFieldList($acl);
		foreach ($toExportFields as $fieldName) {
			if ($fieldName == $acl->getPrimaryKeyField()) continue;
			if (!in_array($fieldName, $allowedExportFieldList)) throw new Exception("Brak uprawnień do exportu pola '{$fieldName}'");
			$params['cols'][] = $fieldName;
		}

		$labels = array();
		foreach ($toExportFields as $fieldName) {
			$labels[ $fieldName ] = $acl->getFieldLabel($fieldName);
		}

		foreach ($args as $k => $v) {
			if (strlen($k) > 3 && substr($k, 0, 2) == 'f_' && strlen($v) > 0) {// filter prefix
				$params[$k] = $v;
			}
			else if (strlen($k) > 4 && substr($k, 0, 3) == 'sf_' && strlen($v) > 0) {// special filter prefix
				$params[$k] = $v;
			}
		}
		$total = $acl->getTotal($params);
		// if ($total > $exportLimit) $params['limit'] = $exportLimit;
		$items = $acl->getItems($params);

		$format = V::get('format', 'html', $_GET);
		if ('html' == $format) {
			UI::gora();
			UI::startTag('table', ['class'=>'table table-bordered table-hover']);
				UI::startTag('thead');
					UI::startTag('tr');
					foreach ($labels as $fldName => $label) {
						UI::tag('th', [], $label);
					}
					UI::endTag('tr');
				UI::endTag('thead');
				UI::startTag('tbody');
				foreach ($items as $item) :
					UI::startTag('tr');
					foreach ($labels as $fldName => $label) :
						UI::tag('td', [], $item->{$fldName});
					endforeach;
					UI::endTag('tr');
				endforeach;
				UI::endTag('tbody');
			UI::endTag('table');
			UI::dol();
		}
		else if ('csv' == $format) {
			$csvFileName = "Tabela-" . $acl->getName() . "-" . date("Y-m-d_H_s");
			header('Content-Type: text/csv; charset=utf-8');
			header("Content-Disposition: attachment; filename={$csvFileName}.csv");
			$csvSeparator = ';';

			$labelsLine = array();
			foreach ($labels as $fldName => $label) {
				$labelsLine[] = '"' . addslashes($label) . '"';
			}
			echo implode($csvSeparator, $labelsLine) . "\n";

			foreach ($items as $item) {
				$itemLine = array();
				foreach ($labels as $fldName => $label) {
					$itemLine[] = '"' . addslashes($item->{$fldName}) . '"';
				}
				echo implode($csvSeparator, $itemLine) . "\n";
			}
		}
		else {
			die("Nieobsługiwany format danych.");
		}
	}

	public function loadDataAjaxAction() {
		$namespace = V::get('namespace', '', $_REQUEST, 'word');
		if (!$namespace) throw new HttpException("Bad Request - missing namespace", 400);
		$acl = Core_AclHelper::getAclByNamespace($namespace);
		$tbl = $this->getTableAjaxWidget($acl);
		Response::sendTryCatchJson(array($tbl, 'ajaxData'), $args = $_GET);
	}

}