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

<?php

Lib::loadClass('RouteBase');
Lib::loadClass('Response');
Lib::loadClass('SchemaFactory');

class Route_UrlAction_ChangeOwner extends RouteBase {

	function defaultAction() {
		if ('changeOwnerPostTask' === V::get('_postTask', '', $_POST)) {
			UI::layout([ $this, 'changeOwnerView' ]);
		} else {
			UI::layout([ $this, 'defaultView' ]);
		}
	}
	function titleView() {
		echo UI::h('h3', [], [
			"Zmiana osoby odpowiedzialnej",
			'<br>',
			UI::h('small', [], "Zmiany w polu 'Osoba odpowiedzialna' we wszystkich rekordach wybranej tabeli."),
		]);
	}
	function defaultView() {
		$this->titleView();

		$SELECTED_TABLE_ID = V::get('id_table', '', $_GET);
		if (!$SELECTED_TABLE_ID) {
			$fromNs = V::get('_fromNamespace', '', $_GET);
			if (!empty($fromNs)) {
				try {
					$acl = ACL::getAclByNamespace($fromNs);
					$SELECTED_TABLE_ID = $acl->getID();
				} catch (Exception $e) {
					DBG::log($e);
				}
			}
		}
		$SELECTED_FROM_OWNER_ID = V::get('id_from', '', $_GET);
		$SELECTED_TO_OWNER_ID = V::get('id_to', '', $_GET);
		$SELECTED_TABLE_INFO = ($SELECTED_TABLE_ID) ? $this->getTableInfo($SELECTED_TABLE_ID) : [];
		$SELECTED_FROM_OWNER_INFO = ($SELECTED_FROM_OWNER_ID) ? $this->getUserInfo($SELECTED_FROM_OWNER_ID) : [];
		$SELECTED_TO_OWNER_INFO = ($SELECTED_TO_OWNER_ID) ? $this->getUserInfo($SELECTED_TO_OWNER_ID) : [];
		DBG::log($SELECTED_TABLE_INFO, 'array', "SELECTED_TABLE_INFO ({$SELECTED_TABLE_ID})");
		DBG::log($SELECTED_FROM_OWNER_INFO, 'array', "SELECTED_FROM_OWNER_INFO ({$SELECTED_FROM_OWNER_ID})");
		DBG::log($SELECTED_TO_OWNER_INFO, 'array', "SELECTED_TO_OWNER_INFO ({$SELECTED_TO_OWNER_ID})");
		echo UI::h('div', [ 'id' => "p5-change-owner-widget" ]);
		echo UI::h('style', [], "
			.dropdown-menu>li>a { max-width: 800px; }
		");
		echo UI::h('script', [ 'src' => "static/vendor.js" ]); // window.p5VendorJs: {React, ReactDOM, createReactClass, Redux}
		UI::inlineJS(__FILE__ . '.view.js', [
			'HTML_ID' => "p5-change-owner-widget",
			'URL_SEARCH_TABLE_LIST' => $this->getLink('searchTableListAjaxTask'),
			'URL_SEARCH_USER_LIST' => $this->getLink('searchUserListAjaxTask'),
			'URL_FETCH_INFO' => $this->getLink('fetchInfoAjaxTask'),
			'SELECTED_TABLE_ID' => $SELECTED_TABLE_ID,
			'SELECTED_TABLE_INFO' => $SELECTED_TABLE_INFO,
			'SELECTED_FROM_OWNER_ID' => $SELECTED_FROM_OWNER_ID,
			'SELECTED_FROM_OWNER_INFO' => $SELECTED_FROM_OWNER_INFO,
			'SELECTED_TO_OWNER_ID' => $SELECTED_TO_OWNER_ID,
			'SELECTED_TO_OWNER_INFO' => $SELECTED_TO_OWNER_INFO,
			'CHANGE_OWNER_POST_TASK_NAME' => 'changeOwnerPostTask',
			'URL_BASE' => $this->getLink(),
		]);
	}

	function changeOwnerView() {
		$this->titleView();
		$id_table = V::get('id_table', '', $_POST);
		$id_from = V::get('id_from', '', $_POST);
		$id_to = V::get('id_to', '', $_POST);
		if (!$id_table) throw new Exception("Missing id_table");
		if (!$id_from) throw new Exception("Missing id_from");
		if (!$id_to) throw new Exception("Missing id_to");
		$stats = $this->fetchInfoAjaxTask([
			'id_table' => $id_table,
			'id_from' => $id_from,
			'id_to' => $id_to,
		]);
		DBG::log($stats, 'array', '$stats');
		$tableInfo = $this->getTableInfo($id_table);
		DBG::log($tableInfo, 'array', '$tableInfo');
		$tableLabel = (!empty($tableInfo)) ? $tableInfo[0]['label'] : "";
		echo UI::h('div', [ 'class' => "alert alert-info" ], "Wprowadzanie zmian w tabeli <em>[{$id_table}]</em> {$tableLabel}");
		$totalToUpdate = $stats['body']['fromInfo']['total'];
		if (!$totalToUpdate) throw new Exception("Brak rekordów do aktualizacji");

		$namespace = DB::getPDO()->fetchValue(" select o.namespace from `CRM_#CACHE_ACL_OBJECT` o where o.idZasob = '{$id_table}' limit 1 ");
		$acl = ACL::getAclByNamespace($namespace);
		$rootTableName = $acl->getRootTableName();
		$primaryKeyField = $acl->getPrimaryKeyField();
		$sqlTable = DB::getPDO()->identifierQuote($rootTableName);
		{ // TODO: loop / sql limit
			try {
				$rowsToUpdate = DB::getPDO()->fetchAll("
					select t.ID, t.L_APPOITMENT_USER
					from {$sqlTable} t
					where t.L_APPOITMENT_USER = :id_from
					limit 100
				", [
					':id_from' => $id_from,
				]);
				UI::table(['rows'=>$rowsToUpdate]);
			} catch (Exception $e) {
				// msg: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.L_APPOITMENT_USER' in 'field list'"
				DBG::log($e);
				if ($e->getCode() === '42S22') {
					throw new Exception("Brak pola Osoba odpowiedzialna w tabeli '{$namespace}'");
				}
				throw $e;
			}
			foreach ($rowsToUpdate as $item) {
				try {
					DB::getPDO()->update($rootTableName, $primaryKeyField, $item['ID'], [
						'L_APPOITMENT_USER' => $id_to,
						'A_RECORD_UPDATE_DATE' => "NOW()",
						'A_RECORD_UPDATE_AUTHOR' => User::getLogin(),
					]);
					DB::getPDO()->insert($rootTableName . "_HIST", [
						'ID_USERS2' => $item['ID'],
						'L_APPOITMENT_USER' => $id_to,
						'A_RECORD_UPDATE_DATE' => "NOW()",
						'A_RECORD_UPDATE_AUTHOR' => User::getLogin(),
					]);
				} catch (Exception $e) {
					DBG::log($e);
					UI::alert('danger', [], "Nie udało się zaktualizować rekordu '{$item['ID']}': " . $e->getMessage());
				}
			}
		}
		UI::alert('success', "Koniec");
	}

	function getTableInfo($idTable) {
		$userObject = SchemaFactory::loadDefaultObject('UserObject');
		$userObject->setIdUser(User::getID());
		// $userObject->setIdProcesFilter($filterIdProces);
		$items = $userObject->getItems([
			'cols' => [ 'ID_TABLE', 'name', 'label' ], // , 'opis' ],
			'primaryKey' => $idTable,
		]);
		// UI::table([ 'rows' => $items ]);

		$items = array_map(function ($item) {
			// [16] => Array:
			// [ID_TABLE] => 16
			// [ID_USER] => 4517
			// [ID_PROCES] => 
			// [db] => 36
			// [name] => BUILDINGS
			// [label] => Budynki
			// [opis] => Budynki - tabela budynków w zasięgu lub zainteresowanych podłączeniem do sieci BIALL-NET.
			// [_rootTableName] => BUILDINGS
			// [namespace] => default_db/BUILDINGS
			return [
				'id' => $item['ID_TABLE'],
				'label' => $this->getTableLabelFromRow($item),
				'_item_name' => $item['name'],
				'_item_label' => $item['label'],
				'_item_name_substr' => substr($item['name'], 0, strlen($item['label'])),
				'_item' => $item,
			];
		}, $items);

		return (!empty($items)) ? [ reset($items) ] : [];
	}
	function getUserInfo($userLogin) {
		return DB::getPDO()->fetchAll("
			select u.ADM_ACCOUNT as id
				, u.ADM_NAME as label
			from ADMIN_USERS u
			where u.ADM_ACCOUNT = :login
		", [ ':login' => $userLogin ]);
	}

	function searchTableListAjaxTaskAction() { Response::sendTryCatchJson([$this, 'searchTableListAjaxTask'], $args = 'JSON_FROM_REQUEST_BODY'); }
	function searchTableListAjaxTask($args) {
		$query = V::get('query', '', $args);
		$wordsQuery = $this->convertQueryToSearchWords($query);
		$items = $this->fetchAllowedTableList($wordsQuery);

		return [
			'type' => 'success',
			'msg' => 'OK',
			'__DBG__args' => $args,
			'body' => [
				'items' => array_values($items),
			]
		];
	}

	function searchUserListAjaxTaskAction() { Response::sendTryCatchJson([$this, 'searchUserListAjaxTask'], $args = 'JSON_FROM_REQUEST_BODY'); }
	function searchUserListAjaxTask($args) {
		$query = V::get('query', '', $args);
		$wordsQuery = $this->convertQueryToSearchWords($query);
		$sqlListWhere = [];
		$pdo = DB::getPDO();
		foreach ($wordsQuery as $word) {
			$sqlListWhere[] = implode(" or ", [
				"u.ID like " . $pdo->quote("%{$word}%"),
				"u.ADM_ACCOUNT like " . $pdo->quote("%{$word}%"),
				"u.ADM_NAME like " . $pdo->quote("%{$word}%"),
			]);
		}
		$sqlWhere = (!empty($sqlListWhere)) ? implode(" and ", $sqlListWhere) : "1=1";
		$items = DB::getPDO()->fetchAll("
			select u.ADM_ACCOUNT as id
				, u.ADM_NAME as label
			from ADMIN_USERS u
			where {$sqlWhere}
		", [ ':id' => $idUser ]);

		return [
			'type' => 'success',
			'msg' => 'OK',
			'body' => [
				'items' => array_values($items),
			]
		];
	}

	function convertQueryToSearchWords($query) {
		$query = trim($query);
		if (strlen($query) < 3) throw new Exception("Query must be more then 2 chars");

		$wordsQuery = [];
		if (false !== strpos($query, ' ')) {
			$words = explode(' ', $query);
			DBG::log($words, 'array', "\$words from \$query({$query}) - 1");
			$words = array_map(function ($word) {
				return trim(trim($word), '0');
			}, $words);
			DBG::log($words, 'array', "\$words from \$query({$query}) - 2");
			$words = array_filter($words, function ($word) {
				return (strlen($word) > 2);
			});
			DBG::log($words, 'array', "\$words from \$query({$query}) - 3");
			if (empty($words)) throw new Exception("Query words must be more then 2 chars");
			$wordsQuery = $words;
		} else {
			$wordsQuery[] = $query;
		}
		return $wordsQuery;
	}

	function fetchAllowedTableList($wordsQuery) {
		$userObject = SchemaFactory::loadDefaultObject('UserObject');
		$userObject->setIdUser(User::getID());
		// $userObject->setIdProcesFilter($filterIdProces);
		$items = $userObject->getItems([
			'cols' => [ 'ID_TABLE', 'name', 'label' ], // , 'opis' ],
		]);
		// UI::table([ 'rows' => $items ]);
		$items = array_filter($items, function ($item) use ($wordsQuery) {
			foreach ($wordsQuery as $word) {
				// DBG::log([
				// 	'is1' => ($item['ID_TABLE'] == $word),
				// 	'is2' => (false !== stripos($item['name'], $word)),
				// 	'is3' => (false !== stripos($item['label'], $word)),
				// 	'$item' => $item,
				// ], 'array', "DBG:array_filter({$item['ID_TABLE']}) '{$word}'");
				if ($item['ID_TABLE'] == $word) return true;
				if (false !== stripos($item['name'], $word)) return true;
				if (false !== stripos($item['label'], $word)) return true;
			}
			return false;
		});
		// UI::table([ 'rows' => $items ]);

		return array_map(function ($item) {
			// [16] => Array:
			// [ID_TABLE] => 16
			// [ID_USER] => 4517
			// [ID_PROCES] => 
			// [db] => 36
			// [name] => BUILDINGS
			// [label] => Budynki
			// [opis] => Budynki - tabela budynków w zasięgu lub zainteresowanych podłączeniem do sieci BIALL-NET.
			// [_rootTableName] => BUILDINGS
			// [namespace] => default_db/BUILDINGS
			return [
				'id' => $item['ID_TABLE'],
				'_item_name' => $item['name'],
				'_item_label' => $item['label'],
				'_item_name_substr' => substr($item['name'], 0, strlen($item['label'])),
				'label' => $this->getTableLabelFromRow($item),
				'_item' => $item,
			];
		}, $items);
	}
	function getTableLabelFromRow($item) {
		$label = $item['name'];
		if (!empty($item['label'])) {
			$tableName = (false !== strpos($item['name'], '/')) ? substr($item['name'], strrpos($item['name'], '/') + 1) : $item['name'];
			if (strlen($item['label']) < strlen($tableName) && substr($tableName, 0, strlen($item['label'])) === $item['label']) {
				$label = $tableName;	
			} else {
				$label = $item['label'];
			}
		}
		// if (!empty($item['opis']) && !empty($item['label'])) {

		// }
		return $label;
	}

	function fetchInfoAjaxTaskAction() { Response::sendTryCatchJson([$this, 'fetchInfoAjaxTask'], $args = 'JSON_FROM_REQUEST_BODY'); }
	function fetchInfoAjaxTask($args) {
		$id_table = V::get('id_table', '', $args);
		$id_from = V::get('id_from', '', $args);
		$id_to = V::get('id_to', '', $args);
		if (!$id_table) throw new Exception("Missing id table");
		$responseBody = [];
		$responseBody['by_owner'] = []; // [ { login, total }, ... ]
		$responseBody['fromInfo'] = []; // { id, name, status, total, ... }
		$responseBody['toInfo'] = [];   // { id, name, status, total, ... }
		$responseBody['errors'] = []; // [ { type, msg } ]
		$responseBody['error_missing_owner_field'] = false; // bool

		$namespace = DB::getPDO()->fetchValue(" select o.namespace from `CRM_#CACHE_ACL_OBJECT` o where o.idZasob = '{$id_table}' limit 1 ");
		$acl = ACL::getAclByNamespace($namespace);
		$rootTableName = $acl->getRootTableName();
		$sqlTable = DB::getPDO()->identifierQuote($rootTableName);
		try {
			$responseBody['by_owner'] = DB::getPDO()->fetchAll("
				select t.L_APPOITMENT_USER as id, count(*) as total
				from {$sqlTable} t
				where t.L_APPOITMENT_USER = :id_from or t.L_APPOITMENT_USER = :id_to
				group by t.L_APPOITMENT_USER
				limit 10
			", [
				':id_from' => ($id_from) ? $id_from : "XXXXX",
				':id_to' => ($id_to) ? $id_to : "XXXXX",
			]);
		} catch (Exception $e) {
			// msg: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.L_APPOITMENT_USER' in 'field list'"
			DBG::log($e);
			if ($e->getCode() === '42S22') {
				$responseBody['error_missing_owner_field'] = true;
			}
			$responseBody['errors']['by_owner'] = $e->getMessage();
		}
		$fromInfo = $this->_fetchOwnerInfo($id_from);
		$toInfo = $this->_fetchOwnerInfo($id_to);

		$responseBody['fromInfo'] = (!$fromInfo) ? null : array_merge($fromInfo, [
			'total' => array_reduce($responseBody['by_owner'], function ($ret, $item) use ($id_from) {
				return ($item['id'] === $id_from) ? $item['total'] : $ret;
			}, 0)
		]);
		$responseBody['toInfo'] = (!$toInfo) ? null : array_merge($toInfo, [
			'total' => array_reduce($responseBody['by_owner'], function ($ret, $item) use ($id_to) {
				return ($item['id'] === $id_to) ? $item['total'] : $ret;
			}, 0)
		]);

		return [
			'type' => 'success',
			'msg' => 'OK',
			'__DBG__namespace' => $namespace,
			'__DBG__acl' => $acl,
			'__DBG__args' => $args,
			'body' => $responseBody,
		];
	}
	function _fetchOwnerInfo($id) { // @param $id: string || null
		return DB::getPDO()->fetchFirst("
			select t.ADM_ACCOUNT as id
				, t.ADM_NAME as name
				, t.EMPLOYEE_TYPE as type
				, t.A_STATUS as status
			from `ADMIN_USERS` t
			where t.ADM_ACCOUNT = :id_from
		", [
			':id_from' => $id,
		]);
	}

}