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

<?php

Lib::loadClass('Api_WfsNs');
Lib::loadClass('Response');
Lib::loadClass('Core_XmlWriter');

class Route_UrlAction_BiAuditGraph extends RouteBase {

	function defaultAction() {
		$args = [];
		$args['raportId'] = V::get('raportId', 0, $_GET, 'int');
		$args['featureId'] = V::get('featureId', '', $_GET);
		$args['primaryKey'] = V::get('primaryKey', '', $_GET);
		if ($args['raportId']) return UI::layout([$this, 'raportView'], [ 'args' => $args ]);
		UI::layout([$this, 'defaultView']);
	}
	function defaultView() {
		// typeName = "default_db__x3A__BI_audit_KRS_person:BI_audit_KRS_person"
		$namespace = 'default_db/BI_audit_KRS_person/BI_audit_KRS_person'; // V::get('namespace', '', $_GET);
		if (!$namespace) throw new Exception("Missing namespace");
		$primaryKey = V::get('primaryKey', '', $_GET);
		if (!$primaryKey) throw new Exception("Missing primaryKey");
		echo UI::h('div', [ 'id' => "ref_graph_node", 'style' => "width:98%; height:500px; border:1px solid silver; margin:8px auto; padding:0" ], [
			"Pobieranie danych..."
		]);

		echo UI::h('script', [ 'src' => "static/visjs/vis.min.js" ]);
		echo UI::h('style', [ 'type' => "text/css", 'src' => "static/visjs/vis.min.css" ]);
		UI::inlineJS(__FILE__ . '.view.js', [
			'HTML_ID_REF_GRAPH' => 'ref_graph_node',
			'TYPENAME' => Api_WfsNs::typeName($namespace),
			'PRIMARY_KEY' => $primaryKey,
			'WFS_URL' => Router::getRoute('WfsBiAudit')->getLink(),
			'DBG' => (V::get('DBG', 0, $_GET, 'int') > 0),
		]);
	}

	function raportOutputMatrixAction() {
		$args = [];
		$args['raportId'] = V::get('raportId', 0, $_GET, 'int');
		$args['outputFormat'] = V::get('outputFormat', '', $_GET, 'word');
		try {
			if (!$args['raportId']) throw new Exception("Wrong param raportId");
		} catch (\Exception $e) {
			switch ($args['outputFormat']) {
				case 'json': return $this->sendJsonErrorMsg($e->getMessage());
				case 'xml': return $this->sendXmlErrorMsg($e->getMessage());
				case 'html':
				default: {
					return $this->sendHtmlErrorMsg($e->getMessage());
				}
			}
		}

		$matrix = $this->getRaportOutputMatrix($args['raportId']);
		switch ($args['outputFormat']) {
			case 'json': return $this->sendJsonMatrix($args['raportId'], $matrix);
			case 'xml': return $this->sendXmlMatrix($args['raportId'], $matrix);
			case 'html':
			default: {
				return $this->sendHtmlMatrix($args['raportId'], $matrix);
			}
		}
	}
	function sendJsonMatrix($raportId, $matrix) { // TODO: ..
		print_r($matrix);
	}
	function sendXmlMatrix($raportId, $matrix) { // TODO: ...
		print_r($matrix);
	}
	function sendHtmlMatrix($raportId, $matrix) {
		UI::gora();
		UI::table([
			'caption' => "Wyniki raportu Nr {$raportId}",
			'rows' => $matrix,
		]);
		UI::dol();
	}
	function sendJsonErrorMsg($msg) { // TODO: ..
		echo $e->getMessage();
	}
	function sendXmlErrorMsg($msg) { // TODO: ...
		echo $e->getMessage();
	}
	function sendHtmlErrorMsg($msg) {
		UI::gora();
		UI::alert('danger', $e->getMessage());
		UI::dol();
	}

	function raportOutputStatsAction() {
		session_write_close();
		$args = [];
		$args['raportId'] = V::get('raportId', 0, $_GET, 'int');
		$args['outputFormat'] = V::get('outputFormat', '', $_GET, 'word');
		try {
			if (!$args['raportId']) throw new Exception("Wrong param raportId");
		} catch (\Exception $e) {
			switch ($args['outputFormat']) {
				case 'json': return $this->sendJsonErrorMsg($e->getMessage());
				case 'xml': return $this->sendXmlErrorMsg($e->getMessage());
				case 'html':
				default: {
					return $this->sendHtmlErrorMsg($e->getMessage());
				}
			}
		}

		$stats = $this->getOutputStats($args['raportId']);

		switch ($args['outputFormat']) {
			case 'json': return $this->sendJsonStats($args['raportId'], $stats);
			case 'xml': return $this->sendXmlStats($args['raportId'], $stats);
			case 'html':
			default: {
				return $this->sendHtmlStats($args['raportId'], $stats);
			}
		}
	}
	function getOutputStats($raportId) {
		$matrix = $this->getRaportOutputMatrix($raportId);
		$listObjectsNs = $this->_getListPointChildNs();
		$listPointChildAlias = $this->_getListPointChildAlias();

		$getPointFeatureIdFromPath = function ($item) {
			$listPointChildAlias = $this->_getListPointChildAlias();
			return array_reduce($listPointChildAlias, function ($ret, $objAlias) use ($item) {
				$pk = V::get("pk_{$objAlias}", 0, $item);
				if ($pk) $ret = "{$objAlias}.{$pk}";
				return $ret;
			}, null);
		};

		$lastPkPath = 0;
		$lastPos = 0;
		$fidMatrix = array_map(function ($item) use ($getPointFeatureIdFromPath, &$lastPkPath, &$lastPos) {
			if ($lastPkPath != $item['pk_Path']) {
				$lastPos = 0;
			}
			$lastPos += 1;
			$lastPkPath = $item['pk_Path'];
			return [
				'pk_Path' => $item['pk_Path'],
				'pos' => $lastPos,
				'fid' => $getPointFeatureIdFromPath($item),
			];
		}, $matrix);
		DBG::log($fidMatrix, 'array', '$fidMatrix');

		$listPathLength = array_reduce($matrix, function ($ret, $item) {
			$ret[ $item['pk_Path'] ] = 1 + V::get($item['pk_Path'], 0, $ret);
			return $ret;
		}, []);
		$listPathPk = array_keys($listPathLength);
		$pointOccurs = array_reduce($matrix, function ($ret, $item) use ($listPointChildAlias) {
			foreach ($listPointChildAlias as $alias) {
				$pkField = "pk_{$alias}";
				if ($item[$pkField]) {
					$featureId = "{$alias}.{$item[$pkField]}";
					$ret[$featureId] = 1 + V::getValue($ret[$featureId], 0);
				}
			}
			return $ret;
		}, []);
		asort($pointOccurs);
		DBG::log($pointOccurs, 'array', '$pointOccurs');

		$prevItem = [];
		$edges = array_reduce($matrix, function ($ret, $item) use (&$prevItem, $getPointFeatureIdFromPath) {
			if (!empty($prevItem) && $prevItem['pk_Path'] === $item['pk_Path']) {
				$ret[] = [ 'source' => $getPointFeatureIdFromPath($prevItem), 'target' => $getPointFeatureIdFromPath($item), 'pathId' => $item['pk_Path'] ];
				$prevItem = array_merge([], $item);
				return $ret;
			} else {
				$prevItem = array_merge([], $item);
				return $ret;
			}
		}, []);

		DBG::log("edges - done");
		$prevFeaturesOnPath = [];
		$prevPathId = 0;
		$allToAllEdges = array_reduce($matrix, function ($ret, $item) use (&$prevFeaturesOnPath, &$prevPathId, $getPointFeatureIdFromPath) {
			$curFeatureId = $getPointFeatureIdFromPath($item);
			if (!empty($prevFeaturesOnPath) && $prevPathId === $item['pk_Path']) {
				foreach ($prevFeaturesOnPath as $idx => $prevFeatureId) {
					$ret[] = [ 'source' => $prevFeatureId, 'target' => $curFeatureId, 'pathId' => $item['pk_Path'], 'length' => (1 + $idx) ];
				}
				array_unshift($prevFeaturesOnPath, $curFeatureId);
				$prevPathId = $item['pk_Path'];
				return $ret;
			} else {
				$prevFeaturesOnPath = [ $curFeatureId ];
				$prevPathId = $item['pk_Path'];
				return $ret;
			}
		}, []);
		DBG::log("edges_all_to_all - done");

		$stats = [];
		// $stats['path_length'] = $listPathLength;
		$stats['path_total'] = count($listPathLength);
		$stats['point_total'] = count($matrix);
		$stats['point_occurs'] = $pointOccurs;
		$stats['edges_all_to_all'] = $allToAllEdges;
		$stats['edges'] = $edges;
		$stats['nodes'] = array_map([ $this, 'getFeature' ], array_keys($pointOccurs));
		$stats['path_list'] = array_values(array_reduce($fidMatrix, function ($ret, $item) {
			$pkPath = $item['pk_Path'];
			if (!array_key_exists($pkPath, $ret)) $ret[ $pkPath ] = [ 'pkPath' => $pkPath, 'fids' => [] ];
			$ret[ $pkPath ][ 'fids' ][] = $item['fid'];
			return $ret;
		}, []));
		return $stats;
	}

	function getFeature($featureId) {
		list($objectName, $primaryKey) = explode(".", $featureId);
		if (!$objectName) throw new Exception("Wrong featureId '{$featureId}' - missing objectName");
		if (!$primaryKey) throw new Exception("Wrong featureId '{$featureId}' - missing primaryKey");
		$tableName = $objectName;
		$item = DB::getPDO()->fetchFirst(" select * from `{$tableName}` where id = :id ", [ ':id' => $primaryKey ]);
		$getLabel = [ $this, '_getFeatureLabel' ];
		return array_merge($item, [
			'@label' => $getLabel($objectName, $item), // TODO: label depend on table / later format from xsd
			'@object' => $objectName,
			'@primaryKey' => $primaryKey,
		]);
	}
	function _getFeatureLabel($objectName, $item) {
		$primaryKey = $item['@primaryKey'];
		switch ($objectName) {
			case 'BI_audit_ENERGA_RUM_KONTRAHENCI': return V::get('Pelna_nazwa_kontrahenta', $primaryKey, $item);
			case 'BI_audit_MSIG': return V::get('nazwa', $primaryKey, $item);
			case 'BI_audit_MSIG_person': return V::getValue(
				implode(" ", array_filter([
					V::get('imiona', '', $item),
					V::get('nazwisko', '', $item)
				], 'strlen'))
				, "{$objectName} Nr {$primaryKey}"
			);
			case 'BI_audit_CEIDG': return V::get('firma', $primaryKey, $item);
			case 'BI_audit_KRS': return V::get('nazwa', $primaryKey, $item);
			case 'BI_audit_KRS_person': return V::getValue(
				implode(" ", array_filter([
					V::get('imiona', '', $item),
					V::get('nazwisko', '', $item)
				], 'strlen'))
				, "{$objectName} Nr {$primaryKey}"
			);
			case 'BI_audit_MSIG_company': return V::get('nazwa', $primaryKey, $item);
			case 'BI_audit_KRS_company': return V::get('nazwa', $primaryKey, $item);
			case 'TERYT_adresy': return "TERYT({$item['TERYT_SYM']}/{$item['TERYT_SYM_UL']})";
			case 'BI_audit_MSIG_address': return V::getValue(
				implode(", ", array_filter([
					implode(" ", array_filter([
						V::get('A_kod', '', $item),
						V::get('S_miejscowosc', '', $item)
					], 'strlen')),
					implode(" ", array_filter([
						V::get('A_nrDomu', '', $item),
						V::get('A_nrDomu', '', $item),
						V::get('A_nrLokalu', '', $item)
					], 'strlen'))
				], 'strlen'))
				, "{$objectName} Nr {$primaryKey}"
			);

			default: return "TODO: {$objectName} Nr {$primaryKey}";
		}
	}
	function sendJsonStats($raportId, $stats) {
		Response::sendJson([
			'type' => "success",
			'msg' => "OK",
			'body' => $stats
		]);
		die();
	}
	function sendXmlStats($raportId, $stats) {
		header('Content-type: application/xml; charset=utf-8');
		$xmlWriter = new Core_XmlWriter();
		if (!$xmlWriter) throw new HttpException("Error no XMLWriter", 404);
		$xmlWriter->openUri('php://output');
		$xmlWriter->setIndent(true);
		$xmlWriter->startDocument('1.0','UTF-8');
		$xmlWriter->startElement('p5:reportGraph');
		// $xmlWriter->writeAttribute('xml:lang', "en-GB");
		$xmlWriter->writeAttribute('xmlns:p5', Api_WfsNs::getBaseWfsUri());
		$xmlWriter->writeAttribute('xmlns:xlink', "http://www.w3.org/1999/xlink");
		$xmlWriter->writeAttribute('xmlns:wps', "http://www.opengis.net/wps/1.0.0");
		$xmlWriter->writeAttribute('xmlns:ows', "http://www.opengis.net/ows/1.1");
		$xmlWriter->writeAttribute('xmlns:ogc', "http://www.opengis.net/ogc");
		$xmlWriter->writeAttribute('xmlns:xsi', "http://www.w3.org/2001/XMLSchema-instance");
		$xmlWriter->writeAttribute('id', $raportId);
		$xmlWriter->h('p5:nodes', [], array_map(function ($node) {
			return [
				'p5:node', [
					'objectName' => $node['@object'],
					'primaryKey' => $node['@primaryKey'],
					'label' => $node['@label'],
				], []
			];
		}, $stats['nodes']) );
		$xmlWriter->h('p5:edges', [], array_map(function ($node) {
			return [
				'p5:edge', [
					'source' => $node['source'],
					'target' => $node['target'],
					'pathId' => $node['pathId'],
				], []
			];
		}, $stats['edges']) );
		// [edges] => Array:
		//         [0] => Array:
		//             [source] => BI_audit_KRS_person.747000
		//             [target] => BI_audit_MSIG_person.856408
		//             [pathId] => 396191
		$xmlWriter->endElement(); // root
		$xmlWriter->endDocument();
		exit;
	}
	function sendHtmlStats($raportId, $stats) {
		UI::gora();
		DBG::nicePrint($stats, "\$stats");
		$dbgNode = function ($item) {
			return [
				'object' => $item['@object'],
				'primaryKey' => $item['@primaryKey'],
				'label' => $item['@label'],
			];
		};
		UI::table([ 'rows' => array_map($dbgNode, $stats['nodes']) ]);
		UI::dol();
	}


	function raportView($args) {
		$raportId = V::get('raportId', 0, $args, 'int');
		$featureId = V::get('featureId', '', $args, '');
		$h = [ 'UI', 'h' ];
		echo $h('div', [ 'id' => "bi_audit_raport-network_graph" ], "TODO: loading data...");
		echo $h('script', ['src'=>"static/vendor.js?v=a76e2988", 'type'=>"text/javascript"]);
		echo $h('script', [ 'src' => "static/visjs/vis.min.js" ]);
		echo UI::h('link', [ 'href' => 'static/react-bootstrap-typeahead.min.css', 'type' => 'text/css', 'rel' => 'stylesheet' ]);
		UI::inlineJS(__FILE__ . '.network-graph.js', [
			'HTML_ID_REF_GRAPH' => 'bi_audit_raport-network_graph',
			// 'TYPENAME' => Api_WfsNs::typeName($namespace),
			// 'PRIMARY_KEY' => $primaryKey,
			// 'WFS_URL' => Router::getRoute('WfsBiAudit')->getLink(),
			'RAPORT_ID' => $raportId,
			'API_URL' => $this->getLink('raportOutputStats', [ 'outputFormat' => "json", 'raportId' => $raportId ]),
			'STATS' => $this->getOutputStats($raportId),
			'DATA_MATRIX' => $this->getRaportOutputMatrix($raportId),
			'DBG' => (V::get('DBG', 0, $_GET, 'int') > 0),
		]);

		$this->getRaportOutputMatrix($raportId); // TODO: DBG
	}
	function getRaportNetworkGraphDataAjaxAction() {

	}
	function _getListPointChildNs() {
		$listObjectsNs = [];
		$listObjectsNs[] = 'default_db__x3A__BI_audit_CEIDG:BI_audit_CEIDG';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_CEIDG_pelnomocnicy:BI_audit_CEIDG_pelnomocnicy';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_CEIDG_powiazania:BI_audit_CEIDG_powiazania';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_ENERGA_PRACOWNICY:BI_audit_ENERGA_PRACOWNICY';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_ENERGA_PRACOWNICY_adresy:BI_audit_ENERGA_PRACOWNICY_adresy';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI:BI_audit_ENERGA_RUM_KONTRAHENCI';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_KRS:BI_audit_KRS';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_KRS_address:BI_audit_KRS_address';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_KRS_company:BI_audit_KRS_company';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_KRS_person:BI_audit_KRS_person';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_KW_requested_person:BI_audit_KW_requested_person';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_MSIG:BI_audit_MSIG';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_MSIG_address:BI_audit_MSIG_address';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_MSIG_company:BI_audit_MSIG_company';
		$listObjectsNs[] = 'default_db__x3A__BI_audit_MSIG_person:BI_audit_MSIG_person';
		$listObjectsNs[] = 'default_db__x3A__TERYT_adresy:TERYT_adresy';
		return $listObjectsNs;
	}
	function _getListPointChildAlias() {
		return array_map(function ($objNs) {
			// return str_replace([ 'BI_audit_' ], '', substr($objNs, strrpos($objNs, ':') + 1));
			return substr($objNs, strrpos($objNs, ':') + 1);
		}, $this->_getListPointChildNs());
	}
	function getRaportOutputMatrix($raportId) {
		// Main Raport Acl NS: default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA:BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA
		// - Found Paths in: default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row:BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row
		// - Path Points in: default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object:BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object
		$mainNs = [
			'Raport' => 'default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA:BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA',
			'Path' => 'default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row:BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row',
			'Point' => 'default_db__x3A__BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object:BI_audit_ENERGA_RUM_KONTRAHENCI_POWIAZANIA_row_object',
		];

		$listObjectsNs = $this->_getListPointChildNs();

		$outRefTables = array_merge(
			[ [ 'label' => 'Raport_to_Path', 'table' => ACL::getRefTable(Api_WfsNs::toNamespace($mainNs['Raport']), $mainNs['Path']) ] ],
			[ [ 'label' => 'Path_to_Point', 'table' => ACL::getRefTable(Api_WfsNs::toNamespace($mainNs['Path']), $mainNs['Point']) ] ],
			array_map(function ($objNs) use ($mainNs) {
				$objLabel = str_replace([ 'BI_audit_' ], '', substr($objNs, strrpos($objNs, ':') + 1));
				return [ 'label' => "Point_to_{$objLabel}", 'table' => ACL::getRefTable(Api_WfsNs::toNamespace($mainNs['Point']), $objNs) ];
			}, $listObjectsNs)
		);
		$nsToTableName = function ($objNs) {
			return substr($objNs, strrpos($objNs, ':') + 1);
		};
		$mainTables = array_map($nsToTableName, array_merge($mainNs));
		$refFromPointTables = array_map($nsToTableName, $listObjectsNs);
		$allTables = array_merge($mainTables, $refFromPointTables);
		DBG::log($allTables, 'array', '$allTables');

		$refTables = array_combine( array_map(V::makePick('label'), $outRefTables), array_map(V::makePick('table'), $outRefTables) );
		DBG::log($refTables, 'array', '$refTables');

		$listPointChildAlias = $this->_getListPointChildAlias();
		DBG::log($listPointChildAlias, 'array', '$listPointChildAlias');
		$sqlSelect = implode("\n , ", array_merge(
			array_map(function ($tbl, $key) {
				return "t_{$key}.ID as pk_{$key}";
			}, $mainTables, array_keys($mainTables)),
			array_map(function ($tbl, $key) use ($listPointChildAlias) {
				return "t_{$key}.ID as pk_{$listPointChildAlias[$key]}";
			}, $refFromPointTables, array_keys($refFromPointTables))
		));
		$sqlTablesFrom = [];
		// return "\n" . "join `{$tableName}` `t_{$key}` on ( `t_{$key}`.ID = `t_{}` ) ";
		$sqlListFromJoin = [];
		$fromPointRefTables = array_slice(array_values($refTables), 2);
		for ($i = 0; $i < count($listObjectsNs); $i++) {
			$sqlListFromJoin[] = "
				left join `{$fromPointRefTables[$i]}` as r_Point_to_{$i} on ( r_Point_to_{$i}.PRIMARY_KEY = t_Point.ID )
				left join `{$allTables[$i]}` as t_{$i} on ( t_{$i}.ID = r_Point_to_{$i}.REMOTE_PRIMARY_KEY )
			";
		}
		$sqlFromJoin = implode("\n", $sqlListFromJoin);
		$sql = "
			select {$sqlSelect}
			from `{$allTables['Raport']}` as t_Raport
				left join `{$refTables['Raport_to_Path']}` as r_Raport_to_Path on ( r_Raport_to_Path.PRIMARY_KEY = t_Raport.ID )
				left join `{$allTables['Path']}` as t_Path on ( t_Path.ID = r_Raport_to_Path.REMOTE_PRIMARY_KEY )
				left join `{$refTables['Path_to_Point']}` as r_Path_to_Point on ( r_Path_to_Point.PRIMARY_KEY = t_Path.ID )
				left join `{$allTables['Point']}` as t_Point on ( t_Point.ID = r_Path_to_Point.REMOTE_PRIMARY_KEY )
				{$sqlFromJoin}
			where t_Raport.ID = {$raportId}
			
		"; //TODO DEBUG LIMIT OPTIMALIZATION - HANGS limit 300
		return DB::getPDO()->fetchAll($sql);
	}

}