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

<?php

Lib::loadClass('Api_WfsNs');

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 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" ]);
		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(),
			'API_URL' => $this->getLink('getRaportNetworkGraphDataAjax'),
			'DBG' => (V::get('DBG', 0, $_GET, 'int') > 0),
		]);

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

	}
	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 = [];
		$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';

		$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');

		$fromPointRefTableAliases = array_map(function ($objNs) {
			return str_replace([ 'BI_audit_' ], '', substr($objNs, strrpos($objNs, ':') + 1));
		}, array_merge($listObjectsNs));
		DBG::log($fromPointRefTableAliases, 'array', '$fromPointRefTableAliases');
		$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 ($fromPointRefTableAliases) {
				return "t_{$key}.ID as pk_{$fromPointRefTableAliases[$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}
		";
		return DB::getPDO()->fetchAll($sql);
	}

}