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

<?php

Lib::loadClass('RouteBase');

class Route_UrlAction_ProjektyKosztyWstepnychRobot extends RouteBase {// TODO: UrlActionBase @see Route_UrlAction

	public function handleAuth() {
		if (!User::logged()) {
			throw new HttpException('Unauthorized', 401);
		}
	}

	public function defaultAction() {
		// TODO: check if user is allowed to run this action
		SE_Layout::gora();
		try {
			$idProject = V::get('ID_PROJECT', '', $_REQUEST, 'int');
			if (!$idProject) throw new Exception("Wrong param in 'ID_PROJECT' - expected integer!");
			$this->kosztorys($idProject);
		} catch (Exception $e) {
			SE_Layout::alert('danger', "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage());
		}
		SE_Layout::dol();
	}

	public function getArgsList() {// TODO: override UrlActionBase::getArgsList action
		$args = array();
		$args[] = 'ID_PROJECT';
		return $args;
	}

	public function reinstallAction() {// TODO: mv to UrlActionBase
		$jsonData = new stdClass();
		$jsonData->type = 'success';
		$jsonData->msg = 'Gotowe';
		try {
			$this->reinstall();
		} catch (Exception $e) {
			$jsonData->type = 'danger';
			$jsonData->msg = $e->getMessage();
		}
		echo json_encode($jsonData);
	}

	public function reinstall() {// TODO: mv to UrlActionBase
		/* required Zasoby tree structure - XML (parent rel in PARENT_ID field):
<zasob:tree>
	<zasob:TYPESPECIALS>
		<zasob:URL_ACTION zasob:key=":action_id" zasob:desc=":url_action_name">
			<zasob:PARAM_IN zasob:key=":action_param_1_id" zasob:desc=":url_action_param_1_name"/>
		</zasob:URL_ACTION>
	</zasob:TYPESPECIALS>
	<zasob:BAZA_DANYCH zasob:key=":main_db_id" altername_types="DATABASE_MYSQL,DATABASE_POSTGRESQL">
		<zasob:TABELA zasob:key=":tbl_id" zasob:desc="IN7_MK_BAZA_DYSTRYBUCJI">
			<zasob:KOMORKA zasob:key=":cell_id" zasob:desc="ID"/>
			<zasob:URL_ACTION zasob:key=":link_action_id" zasob:desc=":url_action_name" zasob:alias_id=":action_id">
				<zasob:PARAM_IN zasob:key=":cell_id" zasob:desc="ID_PROJECT" zasob:alias_id=":cell_id"/>
			</zasob:URL_ACTION>
		</zasob:TABELA>
	</zasob:BAZA_DANYCH>
</zasob:tree>
		*/
		/* required Zasoby tree structure:
- required base structure
[:ts_main_id] TYPESPECIALS
  [:action_id] URL_ACTION :url_action_name
    [:action_param_1_id] PARAM_IN :url_action_param_1_name
- current function from schema
[:main_db_id] (DATABASE_MYSQL, BAZA_DANYCH, DATABASE_POSTGRESQL)
  [:tbl_id] TABELA 'IN7_MK_BAZA_DYSTRYBUCJI'
    [:cell_id] KOMORKA 'ID'
    [:link_action_id] (ALIAS DO :action_id) URL_ACTION :url_action_name
      [:link_param_id] (ALIAS DO :cell_id) PARAM_IN :url_action_param_1_name

:url_action_name = 'ProjektyKosztyWstepnychRobot'
:url_action_param_1_name = 'ID_PROJECT'
		*/
		$flatConfig_main = array();
		{
			$flatConfig_main[] = (object)array(// Zasob::assert(TYPESPECIALS)
				'query' => "
					select z.ID from CRM_LISTA_ZASOBOW z
					where z.TYPE='TYPESPECIALS'
						and z.A_STATUS not in('DELETED')
				",
				'createTable' => 'CRM_LISTA_ZASOBOW',
				'createArgs' => array('TYPE' => 'TYPESPECIALS', 'DESC' => 'Typespecials'),
				'out' => array(':ts_main_id' => 'ID')
			);
			$flatConfig_main[] = (object)array(// Zasob::assert(TYPESPECIALS/:url_action_name)
				'query' => "
					select z.ID from CRM_LISTA_ZASOBOW z
					where z.PARENT_ID = :ts_main_id
						and z.TYPE = 'URL_ACTION'
						and z.DESC = :url_action_name
						and z.A_STATUS not in('DELETED')
				",
				'createTable' => 'CRM_LISTA_ZASOBOW',
				'createArgs' => array('TYPE' => 'URL_ACTION', 'PARENT_ID' => ':ts_main_id', 'DESC' => ':url_action_name'),
				'in' => array(':ts_main_id', ':url_action_name'),
				'out' => array(':action_id' => 'ID')
			);
			$flatConfig_main[] = (object)array(// Zasob::assert(TYPESPECIALS/:url_action_name/:url_action_param_1_name)
				'query' => "
					select z.ID from CRM_LISTA_ZASOBOW z
					where z.PARENT_ID = :action_id
						and z.TYPE = 'PARAM_IN'
						and z.DESC = :url_action_param_1_name
						and z.A_STATUS not in('DELETED')
				",
				'createTable' => 'CRM_LISTA_ZASOBOW',
				'createArgs' => array('TYPE' => 'PARAM_IN', 'PARENT_ID' => ':action_id', 'DESC' => ':url_action_param_1_name'),
				'in' => array(':action_id', ':url_action_param_1_name'),
				'out' => array(':action_param_1_id' => 'ID')
			);
		}
		//DBG::_(true, true, "flatConfig_main", $flatConfig_main, __CLASS__, __FUNCTION__, __LINE__);
		$flatConfig_link_in_projekt = array();
		{
			$flatConfig_link_in_projekt[] = (object)array(// Zasob::assert(default_db)
				'query' => "
					select z.ID from CRM_LISTA_ZASOBOW z
					where z.ID = :main_db_id
						and z.TYPE in('DATABASE_MYSQL', 'BAZA_DANYCH', 'DATABASE_POSTGRESQL')
						and z.A_STATUS not in('DELETED')
				",
				'createTable' => 'CRM_LISTA_ZASOBOW',
				'createArgs' => array('TYPE' => 'BAZA_DANYCH', 'DESC' => 'Baza danych'),
				'in' => array(':main_db_id'),
				'out' => array(':main_db_id' => 'ID')
			);
			$flatConfig_link_in_projekt[] = (object)array(// Zasob::assert(default_db/IN7_MK_BAZA_DYSTRYBUCJI)
				'query' => "
					select z.ID from CRM_LISTA_ZASOBOW z
					where z.PARENT_ID = :main_db_id
						and z.TYPE = 'TABELA'
						and z.DESC = 'IN7_MK_BAZA_DYSTRYBUCJI'
						and z.A_STATUS not in('DELETED')
				",
				'createTable' => 'CRM_LISTA_ZASOBOW',
				'createArgs' => array('TYPE' => 'TABELA', 'PARENT_ID' => ':main_db_id', 'DESC' => 'IN7_MK_BAZA_DYSTRYBUCJI'),
				'in' => array(':main_db_id'),
				'out' => array(':tbl_id' => 'ID')
			);
			$flatConfig_link_in_projekt[] = (object)array(// Zasob::assert(default_db/IN7_MK_BAZA_DYSTRYBUCJI/ID)
				'query' => "
					select z.ID from CRM_LISTA_ZASOBOW z
					where z.PARENT_ID = :tbl_id
						and z.TYPE = 'KOMORKA'
						and z.DESC = 'ID'
						and z.A_STATUS not in('DELETED')
				",
				'createTable' => 'CRM_LISTA_ZASOBOW',
				'createArgs' => array('TYPE' => 'KOMORKA', 'PARENT_ID' => ':tbl_id', 'DESC' => 'ID'),
				'in' => array(':tbl_id'),
				'out' => array(':cell_id' => 'ID')
			);
			$flatConfig_link_in_projekt[] = (object)array(// Zasob::assert(default_db/IN7_MK_BAZA_DYSTRYBUCJI/:url_action_name)
				'query' => "
					select z.ID from CRM_LISTA_ZASOBOW z
					where z.PARENT_ID = :tbl_id
						and z.ALIAS_ID = ':action_id'
						and z.TYPE = 'URL_ACTION'
						and z.DESC = ':url_action_name'
						and z.A_STATUS not in('DELETED')
				",
				'createTable' => 'CRM_LISTA_ZASOBOW',
				'createArgs' => array('TYPE' => 'URL_ACTION', 'PARENT_ID' => ':tbl_id', 'ALIAS_ID' => ':action_id', 'DESC' => ':url_action_name'),
				'in' => array(':tbl_id', ':action_id', ':url_action_name'),
				'out' => array(':link_action_id' => 'ID')
			);
//      [:link_param_id] (ALIAS DO :cell_id) PARAM_IN :url_action_param_1_name
			$flatConfig_link_in_projekt[] = (object)array(// Zasob::assert(default_db/IN7_MK_BAZA_DYSTRYBUCJI/:url_action_name/:url_action_param_1_name)
				'query' => "
					select z.ID from CRM_LISTA_ZASOBOW z
					where z.PARENT_ID = :link_action_id
						and z.ALIAS_ID = ':cell_id'
						and z.TYPE = 'PARAM_IN'
						and z.DESC = ':url_action_param_1_name'
						and z.A_STATUS not in('DELETED')
				",
				'createTable' => 'CRM_LISTA_ZASOBOW',
				'createArgs' => array('TYPE' => 'PARAM_IN', 'PARENT_ID' => ':link_action_id', 'ALIAS_ID' => ':cell_id', 'DESC' => ':url_action_param_1_name'),
				'in' => array(':cell_id', ':link_action_id', ':url_action_param_1_name'),
				'out' => array(':link_action_id' => 'ID')
			);
		}
		//DBG::_(true, true, "flatConfig_link_in_projekt", $flatConfig_link_in_projekt, __CLASS__, __FUNCTION__, __LINE__);
		$flatConf = array_merge($flatConfig_main, $flatConfig_link_in_projekt);
		$knownArgs = array();
		$knownArgs[':url_action_name'] = 'Test';
		$knownArgs[':url_action_param_1_name'] = 'ID_PROJECT';
		$knownArgs[':main_db_id'] = '36';// from DB::getDB()->getZasobId(); or DB::getPDO()->getZasobId();
		$this->_debugFlatConfig($flatConf, $knownArgs);
		$pdo = DB::getPDO();

		$args = $this->getArgsList();
		$clsName = __CLASS__;
		$urlActionName = str_replace('Route_UrlAction_', '', $clsName);
		DBG::_(true, true, "reinstall class", __CLASS__, __CLASS__, __FUNCTION__, __LINE__);
		DBG::_(true, true, "reinstall funName", $urlActionName, __CLASS__, __FUNCTION__, __LINE__);

		DBG::_(true, true, "reinstall test", $args, __CLASS__, __FUNCTION__, __LINE__);
		$dbFunction = $this->fetchZasobInfo();
		$usrLogin = User::getLogin();
		{// check if URL_ACTION already exists
			if (!$dbFunction) throw new Exception("Brak id zasobu typu TYPESPECIALS - TODO: CREATE");// $this->createZasobTypespecials();
			DBG::_(true, true, "dbFunction", $dbFunction, __CLASS__, __FUNCTION__, __LINE__);
			if ($dbFunction['action_id_is_null']) {
				$sth = $pdo->prepare("
					insert into CRM_LISTA_ZASOBOW (
						TYPE,
						PARENT_ID,
						`DESC`,
						A_RECORD_CREATE_AUTHOR,
						A_RECORD_CREATE_DATE
					)
					values (
						'URL_ACTION',
						:ts_id,
						:url_action_name,
						'system-by-{$usrLogin}',
						NOW()
					)
				");
				//$sth->bindValue('ts_id', $dbFunction['ts_id'], PDO::PARAM_INT);
				//$sth->bindValue('url_action_name', $urlActionName, PDO::PARAM_STR);
				$bindValues = array();
				$bindValues['ts_id'] = array($dbFunction['ts_id'], PDO::PARAM_INT);
				$bindValues['url_action_name'] = $urlActionName;
				$pdo->bindValues($sth, $bindValues);
				DBG::_(true, true, "sql", $pdo->getRawSql($sth), __CLASS__, __FUNCTION__, __LINE__);
				$sth->execute();
				$dbFunction = $this->fetchZasobInfo();
				if (!$dbFunction || $dbFunction['action_id_is_null']) throw new Exception("Brak zasobu typu URL_ACTION, nie udało się go utworzyć!");
			}
		}
		{// check params
			if (!empty($args)) {
				$todoArgs = array();
				foreach ($args as $argName) {
					if (empty($dbFunction['url_params'])) {
						$todoArgs[$argName] = true;
					} else if (!array_key_exists($argName, $dbFunction['url_params'])) {
						$todoArgs[$argName] = true;
					}
					// OK PARAM_IN exists
				}
				$todoArgs = array_keys($todoArgs);
				DBG::_(true, true, "todoArgs", $todoArgs, __CLASS__, __FUNCTION__, __LINE__);
				if (!empty($todoArgs)) {
					$sqlValues = array();
					foreach ($todoArgs as $argName) {
						$sqlValues[] = "(
								'PARAM_IN',
								{$dbFunction['action_id']},
								'{$argName}',
								'system-by-{$usrLogin}',
								NOW()
							)
						";
					}
					$sqlValues = implode(", ", $sqlValues);
					$sth = $pdo->prepare("
						insert into CRM_LISTA_ZASOBOW (
							TYPE,
							PARENT_ID,
							`DESC`,
							A_RECORD_CREATE_AUTHOR,
							A_RECORD_CREATE_DATE
						)
						values {$sqlValues}
					");
					DBG::_(true, true, "sql", $pdo->getRawSql($sth), __CLASS__, __FUNCTION__, __LINE__);
					//$sth->execute();
					$dbFunction = $this->fetchZasobInfo();
				}
				if (empty($dbFunction['url_params'])) {
					throw new Exception("Brak zdefiniowanych parametrów, nie udało się ich utworzyć!");
				}
			}
		}
		{// check links in tables
			// $info['links']['IN7_MK_BAZA_DYSTRYBUCJI'] = $linkInfo;
			if (empty($dbFunction['links']['IN7_MK_BAZA_DYSTRYBUCJI'])) throw new Exception("Brak poprawne wprowadzonej tabeli 'IN7_MK_BAZA_DYSTRYBUCJI' w drzewie zasobów!");
			$projInfo = $dbFunction['links']['IN7_MK_BAZA_DYSTRYBUCJI'];
			/*
				[link_action_id] =>
				[db_id] => 36
				[tbl_id] => 636
				[cell_id] => 763
				[param1_id] =>
			*/
			if (!$projInfo['link_action_id']) {
				$sth = $pdo->prepare("
					insert into CRM_LISTA_ZASOBOW (
						TYPE,
						PARENT_ID,
						ALIAS_ID,
						`DESC`,
						A_RECORD_CREATE_AUTHOR,
						A_RECORD_CREATE_DATE
					)
					values (
						'URL_ACTION',
						{$projInfo['tbl_id']},
						{$dbFunction['action_id']},
						'{$urlActionName}',
						'system-by-{$usrLogin}',
						NOW()
					)
				");
				//DBG::_(true, true, "sql", $pdo->getRawSql($sth), __CLASS__, __FUNCTION__, __LINE__);
				$sth->execute();
				$dbFunction = $this->fetchZasobInfo();
				$projInfo = $dbFunction['links']['IN7_MK_BAZA_DYSTRYBUCJI'];
				if (!$projInfo['link_action_id']) throw new Exception("Brak zainstalowanej akcji dla tabeli 'IN7_MK_BAZA_DYSTRYBUCJI' - nie udało się zainstalować");
			}
			if (!$projInfo['param1_id']) {// TODO: get from params
				$sth = $pdo->prepare("
					insert into CRM_LISTA_ZASOBOW (
						TYPE,
						PARENT_ID,
						ALIAS_ID,
						`DESC`,
						A_RECORD_CREATE_AUTHOR,
						A_RECORD_CREATE_DATE
					)
					values (
						'PARAM_IN',
						{$projInfo['link_action_id']},
						{$projInfo['cell_id']},
						'ID_PROJECT',
						'system-by-{$usrLogin}',
						NOW()
					)
				");
				$sth->execute();
				$dbFunction = $this->fetchZasobInfo();
				$projInfo = $dbFunction['links']['IN7_MK_BAZA_DYSTRYBUCJI'];
				if (!$projInfo['param1_id']) throw new Exception("Brak zainstalowanego parametru 1 w akcji dla tabeli 'IN7_MK_BAZA_DYSTRYBUCJI' - nie udało się zainstalować");
			}

		}
	}

	public function _debugFlatConfig($flatConfig, $args = array()) {
		//DBG::_(true, true, "flatConfig", $flatConfig, __CLASS__, __FUNCTION__, __LINE__);
		echo '<pre style="border:1px solid green">';
		foreach ($flatConfig as $conf) {
			/*
				[query] => string
				[createTable] => srting - eg. CRM_LISTA_ZASOBOW
				[createArgs] => array - eg. [ TYPE: 'TYPESPECIALS', DESC: 'Typespecials' ]
				[in] => array - eg. [ ':ts_main_id', ':url_action_name' ]
				[out] => array - eg. [ ':ts_main_id': 'ID' ]
			*/
			echo "Zasob::assert({$conf->createTable}): " . json_encode($conf->createArgs) . " returns: " . json_encode($conf->out) . "\n";
		}
		echo '</pre>';

		echo '<pre style="border:1px solid green">';
		echo "DBG: Simulate iter loop:\n";
		$simulateGeneratedId = 1000;
		$i = 0; $limit = 10; $todoIdx = array(); $knownArgsMap = array();
		foreach ($flatConfig as $idx => $conf) $todoIdx[] = $idx;
		foreach ($args as $arg => $val) $knownArgsMap[$arg] = $val;
		for ($i = 0; $i < $limit; $i++) {
			echo ">> DBG loop({$i}):\n";
			$isExecuted = false;
			foreach ($todoIdx as $idx) {
				$conf = $flatConfig[$idx];
				/*
					[query] => string
					[createTable] => srting - eg. CRM_LISTA_ZASOBOW
					[createArgs] => array - eg. [ TYPE: 'TYPESPECIALS', DESC: 'Typespecials' ]
					[in] => array - eg. [ ':ts_main_id', ':url_action_name' ]
					[out] => array - eg. [ ':ts_main_id': 'ID' ]
				*/
				echo ">>>> Loop({$i}): idx({$idx}) Zasob::assert({$conf->createTable}): " . json_encode($conf->createArgs) . " returns: " . json_encode($conf->out) . "\n";
				$canExecute = true;
				foreach ($conf->in as $argName) {
					if (!array_key_exists($argName, $knownArgsMap)) {
						$canExecute = false;
					}
				}
				if ($canExecute) {
					// TODO: execute - $r = $this->_assertZasobConf($conf, $knownArgsMap);
					// fetch params out to $knownArgsMap
					foreach ($conf->out as $argName => $outFieldName) {
						$knownArgsMap[$argName] = $simulateGeneratedId++;// TODO: fetch from returned object ($r->{$outFieldName})
					}
					$todoIdx = array_diff($todoIdx, array($idx));// remove $idx from $todoIdx
					echo ">>>> Loop({$i}): knownArgsMap: " . json_encode($knownArgsMap) . " \n";
					$isExecuted = true;
				} else {
					echo ">>>> Loop({$i}): cannot execute conf [{$idx}] missing args: " . json_encode($conf->in) . " \n";
				}
			}
			if (!$isExecuted) {
				echo "DBG: Stop";
				if (empty($todoIdx)) {
					echo " - OK";
				} else {
					echo " - Error - TODO:\n";
					foreach ($todoIdx as $idx) {
						$conf = $flatConfig[$idx];
						echo "Zasob::assert({$conf->createTable}): " . json_encode($conf->createArgs) . " returns: " . json_encode($conf->out) . "\n";
					}
				}
				break;
			}
		}
		echo '</pre>';
	}

	public function fetchZasobInfo() {
		$pdo = DB::getPDO();
		$args = $this->getArgsList();
		$clsName = __CLASS__;
		$urlActionName = str_replace('Route_UrlAction_', '', $clsName);
		$sth = $pdo->prepare("
			select zp.ID as ts_id
				, IF(z.ID is null, 1, 0) as action_id_is_null
				, z.ID as action_id
				, z.DESC as action_desc
			from CRM_LISTA_ZASOBOW zp
				left join CRM_LISTA_ZASOBOW z on(z.PARENT_ID = zp.ID
						and z.TYPE = 'URL_ACTION'
						and z.DESC = :url_action_name
						and z.A_STATUS not in('DELETED')
					)
			where zp.TYPE = 'TYPESPECIALS'
				and zp.A_STATUS not in('DELETED')
		");
		$sth->bindValue('url_action_name', $urlActionName);
		$sth->execute();
		$info = $sth->fetch();
		if (!$info) return $info;
		$info['url_params'] = array();
		$info['links'] = array();// where action is installed - expected `IN7_MK_BAZA_DYSTRYBUCJI`

		if ($info['action_id'] > 0) {
			$sthParams = $pdo->prepare("
				select z.ID as param_id
					, z.DESC as param_desc
				from CRM_LISTA_ZASOBOW z
				where z.TYPE = 'PARAM_IN'
					and z.A_STATUS not in('DELETED')
					and z.PARENT_ID = :url_action_id
			");
			$sthParams->bindValue('url_action_id', $info['action_id']);
			$sthParams->execute();
			$urlParams = array();
			$rawUrlParams = $sthParams->fetchAll();
			foreach ($rawUrlParams as $urlParam) {
				$urlParams[$urlParam['param_desc']] = $urlParam;
			}
			$info['url_params'] = $urlParams;
		}
		{
			$main_db_id = $pdo->getZasobId();
			$sth_dbInfo = $pdo->prepare("
				select za.id as link_action_id
					, zd.id as db_id
					, zt.id as tbl_id
					, zi.id as cell_id
					, zp1.id as param1_id -- for every param
				from CRM_LISTA_ZASOBOW zt
					join CRM_LISTA_ZASOBOW zd on(zd.ID = zt.PARENT_ID
						and zd.TYPE in('DATABASE_MYSQL', 'BAZA_DANYCH', 'DATABASE_POSTGRESQL')
						and zd.ID = {$main_db_id}
					)
					join CRM_LISTA_ZASOBOW zi on(zi.PARENT_ID = zt.ID
						and zi.TYPE = 'KOMORKA'
						and zi.DESC = 'ID'
					)
					left join CRM_LISTA_ZASOBOW za on(za.PARENT_ID = zt.ID
						and za.TYPE = 'URL_ACTION'
						and za.ALIAS_ID = {$info['action_id']}
					)
					-- TODO: for every param:
					left join CRM_LISTA_ZASOBOW zp1 on(zp1.PARENT_ID = za.ID
						and zp1.TYPE = 'PARAM_IN'
						and zp1.ALIAS_ID = zi.id
						and zp1.DESC = 'ID_PROJECT'
					)
				where zt.TYPE = 'TABELA'
					and zt.A_STATUS not in('DELETED')
					and zt.DESC = 'IN7_MK_BAZA_DYSTRYBUCJI'
			");
			DBG::_(true, true, "dbInfo query", $pdo->getRawSql($sth_dbInfo), __CLASS__, __FUNCTION__, __LINE__);
			$sth_dbInfo->execute();
			$linkInfo = $sth_dbInfo->fetch();
			if ($linkInfo) {
				$info['links']['IN7_MK_BAZA_DYSTRYBUCJI'] = $linkInfo;
			}
		}
		return $info;
	}

	public function kosztorysXmlAction() {
		$idProject = 1921;
		$schema = $this->_getKosztorysSchema();
		$data = $this->_fetchKosztorysData($idProject);
		//DBG::_(true, true, "XMLWriter", class_exists('XMLWriter'), __CLASS__, __FUNCTION__, __LINE__);
		//header('Content-type: application/xml; charset=utf-8');
		header('Content-type: text/plain; charset=utf-8');
		$xmlWriter = new XMLWriter();
		$xmlWriter->openUri('php://output');
		$xmlWriter->setIndent(true);
		if ($xmlWriter) {
			$xmlWriter->startDocument('1.0','UTF-8');
			//$xmlWriter->startElementNS(null, 'kosztorysy', 'https://biuro.biall-net.pl/wfs');// adds @xmlns=...
			$xmlWriter->startElement('kosztorysy');
			$xmlWriter->writeAttribute('targetNamespace', 'https://biuro.biall-net.pl/wfs');
			$xmlWriter->writeAttributeNS('xmlns', 'p5', 'http://www.w3.org/2000/xmlns/', 'https://biuro.biall-net.pl/wfs');
			for ($i = 1; $i <= 10; $i++) {
				$idProject += 1;
				$schema = $this->_getKosztorysSchema();
				$data = $this->_fetchKosztorysData($idProject);
				$xmlWriter->startElement('kosztorys');
					$xmlWriter->startElement('projekt');
						$xmlWriter->writeAttribute('id', $idProject);
						$xmlWriter->startElement('projekt');
							$xmlWriter->text("TODO L." . __LINE__);
						$xmlWriter->endElement();
					$xmlWriter->endElement();
				$xmlWriter->endElement();
			}
/*
			$memXmlWriter = new XMLWriter();
			$memXmlWriter->openMemory();
			$memXmlWriter->setIndent(true);

			for ($i = 1; $i <= 10; $i++) {
				$idProject += 1;
				$schema = $this->_getKosztorysSchema();
				$data = $this->_fetchKosztorysData($idProject);

				$memXmlWriter->startElement('kosztorys');
					$memXmlWriter->writeAttribute('id', $idProject);
					$memXmlWriter->writeAttributeNS('p5', 'typeName', 'https://biuro.biall-net.pl/wfs', 'Kosztorys');
				$memXmlWriter->text('book_'.$i);
				$memXmlWriter->endElement();

				if ($i % 5 == 0) {
					$batchXmlString = $memXmlWriter->outputMemory(true);
					$xmlWriter->writeRaw($batchXmlString);
				}
			}
			$memXmlWriter->flush();
			unset($memXmlWriter);
*/
			$xmlWriter->endElement();
			$xmlWriter->endDocument();
		}
	}

	public function kosztorys($idProject) {
		$schema = $this->_getKosztorysSchema();
		$data = $this->_fetchKosztorysData($idProject);
?>
<div class="container">
	<h1>Kosztorys wstępnych robót telekomunikacyjnych</h1>
	<table class="table">
		<tr>
			<th><?php echo $schema['nr']; ?></th>
			<th><?php echo $schema['title']; ?></th>
			<th><?php echo $schema['owner_name']; ?></th>
			<th><?php echo $schema['cost_total']; ?></th>
		</tr>
		<tr>
			<td><?php echo $data['nr']; ?></td>
			<td><?php echo $data['title']; ?></td>
			<td><?php echo $data['owner_name']; ?></td>
			<td><?php echo $data['cost_total']; ?></td>
		</tr>
	</table>
	<?php foreach ($schema['sub_costs'] as $layerName => $layerConf) : ?>
		<h4><?php echo $layerConf['label']; ?>:</h4>
		<table class="table" style="width:auto">
			<tr>
				<?php foreach ($layerConf['_agr_fields_to_cols'] as $fldName => $label) : ?>
					<th><?php echo $label; ?></th>
				<?php endforeach; ?>
			</tr>
			<tr>
				<?php foreach ($layerConf['_agr_fields_to_cols'] as $fldName => $label) : ?>
					<td><?php echo V::get($fldName, '', $data['sub_costs'][$layerName]); ?></td>
				<?php endforeach; ?>
			</tr>
		</table>
		<?php if (!empty($layerConf['sub_costs'])) : ?>
			<table class="table" style="width:auto">
				<tr>
					<?php foreach ($layerConf['sub_costs']['labels'] as $fldName => $label) : ?>
						<th><?php echo $label; ?></th>
					<?php endforeach; ?>
				</tr>
				<?php foreach ($data['sub_costs'][$layerName]['sub_costs'] as $subCost) : ?>
					<tr>
						<?php foreach ($layerConf['sub_costs']['labels'] as $fldName => $label) : ?>
							<td><?php echo V::get($fldName, '', $subCost); ?></td>
						<?php endforeach; ?>
					</tr>
				<?php endforeach; ?>
			</table>
		<?php endif; ?>
	<?php endforeach; ?>
</div>
		<?php
		DBG::_('DBG', '>0', "data", $data, __CLASS__, __FUNCTION__, __LINE__);
		DBG::_('DBG', '>0', "schema", $schema, __CLASS__, __FUNCTION__, __LINE__);
	}

	public function _getKosztorysSchema() {
		$schema['nr'] = "Nr projektu";
		$schema['title'] = "Tytuł projektu";
		$schema['owner_name'] = "Osoba prowadząca";
		$schema['cost_total'] = "Szacowany koszt projektu [zł]";
		$schema['sub_costs'] = array();
		{
			$layerConf = array();
			$layerConf['label'] = "Wykop";
			$layerConf['_agr_fields_to_cols'] = array();
			$layerConf['_agr_fields_to_cols']['ilosc'] = "ilość [m]";
			$layerConf['_agr_fields_to_cols']['cena'] = "podstawowa cena [zł/m]";
			$layerConf['_agr_fields_to_cols']['koszt'] = "koszt [zł]";
			$schema['sub_costs']['Rozdzielcza_Wykop_przedmiar_na_mikrorurki'] = $layerConf;
		}
		{
			$layerConf = array();
			$layerConf['label'] = "Mikrokanalizacja do klienta";
			$layerConf['_agr_fields_to_cols'] = array();
			$layerConf['_agr_fields_to_cols']['ilosc'] = "ilość [m]";
			$layerConf['_agr_fields_to_cols']['cena'] = "podstawowa cena [zł/m]";
			$layerConf['_agr_fields_to_cols']['koszt'] = "koszt [zł]";
			$schema['sub_costs']['Rozdzielcza_Mikrokanalizacja_do_klienta'] = $layerConf;
		}
		{
			$layerConf = array();
			$layerConf['label'] = "Przeciski";
			$layerConf['_agr_fields_to_cols'] = array();
			$layerConf['_agr_fields_to_cols']['ilosc'] = "ilość [m]";
			$layerConf['_agr_fields_to_cols']['cena'] = "podstawowa cena [zł/m]";
			$layerConf['_agr_fields_to_cols']['koszt'] = "koszt [zł]";
			$schema['sub_costs']['Rozdzielcza_Przeciski_110mm'] = $layerConf;
		}
		{
			$layerConf = array();
			$layerConf['label'] = "Zabruki";
			$layerConf['_agr_fields_to_cols'] = array();
			$layerConf['_agr_fields_to_cols']['ilosc'] = "ilość [m]";
			$layerConf['_agr_fields_to_cols']['cena'] = "podstawowa cena [zł/m]";
			$layerConf['_agr_fields_to_cols']['koszt'] = "koszt [zł]";
			$schema['sub_costs']['Rozdzielcza_Zabruki'] = $layerConf;
		}
		{
			$layerConf = array();
			$layerConf['label'] = "Światłowód";
			$layerConf['_agr_fields_to_cols'] = array();
			$layerConf['_agr_fields_to_cols']['ilosc'] = "ilość [m]";
			$layerConf['_agr_fields_to_cols']['cena'] = "podstawowa cena [zł/m]";
			$layerConf['_agr_fields_to_cols']['koszt'] = "koszt [zł]";
			{
				$layerSubCostsConf = array();
				$layerSubCostsConf['group_by_field'] = 'wlokien_j';
				$layerSubCostsConf['sql_agr_func'] = array();
				$layerSubCostsConf['sql_agr_func']['sum_dlugosc'] = array();//sum(Dlugosc) as sum_dlugosc
				$layerSubCostsConf['sql_agr_func']['sum_dlugosc']['func'] = 'sum';
				$layerSubCostsConf['sql_agr_func']['sum_dlugosc']['field'] = 'Dlugosc';
				$layerSubCostsConf['labels'] = array();
				$layerSubCostsConf['labels']['cost_type'] = "rodzaj kosztu";
				$layerSubCostsConf['labels']['sum_dlugosc'] = "suma długości";
				$layerConf['sub_costs'] = $layerSubCostsConf;
			}
			$schema['sub_costs']['Rozdzielcza_Kabel_Swiatlowodowy_wsg84'] = $layerConf;
		}
		{
			$layerConf = array();
			$layerConf['label'] = "Studnie - TODO (Lokalizacje)";
			$layerConf['_agr_fields_to_cols'] = array();
			$schema['sub_costs']['__STUDNIE__'] = $layerConf;
		}
		{
			$layerConf = array();
			$layerConf['label'] = "Rury osłonowe - TODO";
			$layerConf['_agr_fields_to_cols'] = array();
			$schema['sub_costs']['Rura_oslonowa_rozdzielcza_magistralna'] = $layerConf;
		}
		{
			$layerConf = array();
			$layerConf['label'] = "Pakiet mikrorurek - TODO";
			$layerConf['_agr_fields_to_cols'] = array();
			$schema['sub_costs']['__PAKIET_MIKRORUREK__'] = $layerConf;
		}
		{
			$layerConf = array();
			$layerConf['label'] = "Koszty dodatkowe - TODO";
			$layerConf['_agr_fields_to_cols'] = array();
			$schema['sub_costs']['__KOSZTY_DODATKOWE__'] = $layerConf;
		}
		return $schema;
	}

	public function _fetchKosztorysData($idProject) {
		$project = array();
		$schema = $this->_getKosztorysSchema();

		if(0){// TEST join by ogc
$exampleOgcJoin = <<<OGC_JOIN
<wfs:Query typeNames="p5_default_db:IN7_MK_BAZA_DYSTRYBUCJI p5_default_db:ADMIN_USERS" aliases="p u">
	<fes:Filter>
		 <fes:And>
				<fes:PropertyIsEqualTo>
					 <fes:ValueReference>p/ID</fes:ValueReference>
					 <fes:Literal>{$idProject}</fes:Literal>
				</fes:PropertyIsEqualTo>
				<fes:PropertyIsEqualTo>
					 <fes:ValueReference>u/ADM_ACCOUNT<fes:ValueReference>
					 <fes:ValueReference>p/L_APPOITMENT_USER</fes:ValueReference>
				</fes:PropertyIsEqualTo>
		 </fes:And>
	</fes:Filter>
</wfs:Query>
OGC_JOIN;
		}

		$pdo = DB::getPDO();
		{
			$sth = $pdo->prepare("
				select p.ID, p.M_DIST_DESC, p.L_APPOITMENT_USER
					, p.koszt_wspolny
					, p.koszt_na_budynek
					, p.koszt_na_mieszkanie
					, p.Agr_Rozdzielcza_Wykop_przedmiar_na_mikrorurki_ilosc
					, p.Agr_Rozdzielcza_Wykop_przedmiar_na_mikrorurki_cena
					, p.Agr_Rozdzielcza_Wykop_przedmiar_na_mikrorurki_koszt
					, p.Agr_Rozdzielcza_Mikrokanalizacja_do_klienta_ilosc
					, p.Agr_Rozdzielcza_Mikrokanalizacja_do_klienta_cena
					, p.Agr_Rozdzielcza_Mikrokanalizacja_do_klienta_koszt
					, p.Agr_metrow_mikrorurek_5szt
					, p.Agr_metrow_mikrorurek_5szt_cena
					, p.Agr_Rozdzielcza_Przeciski_110mm_ilosc
					, p.Agr_Rozdzielcza_Przeciski_110mm_cena
					, p.Agr_Rozdzielcza_Przeciski_110mm_koszt
					, p.Agr_Rozdzielcza_Zabruki_ilosc
					, p.Agr_Rozdzielcza_Zabruki_cena
					, p.Agr_Rozdzielcza_Zabruki_koszt
					, p.Agr_Rozdzielcza_wezly_ilosc
					, p.Agr_Rozdzielcza_wezly_cena
					, p.Agr_Rozdzielcza_wezly_koszt
					, p.Agr_Rozdzielcza_koszty_dodatkowe_wsg84
					, p.Agr_Rozdzielcza_rurociag_wsg84_ilosc
					, p.Agr_Rozdzielcza_rurociag_wsg84_cena
					, p.Agr_Rozdzielcza_rurociag_wsg84_koszt
					, p.Agr_Rozdzielcza_Kabel_Swiatlowodowy_wsg84_ilosc
					, p.Agr_Rozdzielcza_Kabel_Swiatlowodowy_wsg84_cena
					, p.Agr_Rozdzielcza_Kabel_Swiatlowodowy_wsg84_koszt
					, p.Agr_USERS2_MARKETING_ilosc
					, p.Agr_USERS2_MARKETING_cena
					, p.Agr_USERS2_MARKETING_koszt
					, p.Agr_BUILDINGS_ilosc
				from IN7_MK_BAZA_DYSTRYBUCJI p
				where p.ID = :ID_PROJECT
				-- TODO: check perms!
			");
			$sth->bindValue('ID_PROJECT', $idProject, PDO::PARAM_INT);
			$sth->execute();
			$projectList = $sth->fetchAll();
			if (empty($projectList)) throw new Exception("404 - Project Not Found");
			$projectRaw = reset($projectList);
		}
		{
			$project['nr'] = $projectRaw['ID'];
			$project['title'] = $projectRaw['M_DIST_DESC'];
			$project['owner_name'] = $this->fetchUserName($projectRaw['L_APPOITMENT_USER']);
			$project['cost_total'] = $projectRaw['koszt_wspolny'];
			foreach ($schema['sub_costs'] as $layerName => $layerConf) {
				$values = array();
				foreach ($layerConf['_agr_fields_to_cols'] as $fldName => $label) {
					$values[$fldName] = V::get("Agr_{$layerName}_{$fldName}", '', $projectRaw);
				}
				$project['sub_costs'][$layerName] = $values;
				if (!empty($layerConf['sub_costs'])) {
					$sub_costs = array();
					{
						$groupByField = $layerConf['sub_costs']['group_by_field'];
						$sqlFields = array();
						foreach ($layerConf['sub_costs']['sql_agr_func'] as $fldName => $funcConf) {
							$sqlFuncName = $funcConf['func'];
							$sqlFuncField = $funcConf['field'];
							$sqlFields[] = "{$sqlFuncName}(l.{$sqlFuncField}) as {$fldName}";
						}
						$sqlFields = implode(", ", $sqlFields);
						$sth = $pdo->prepare("
							select l.{$groupByField} as cost_type
								, $sqlFields
							from Rozdzielcza_Kabel_Swiatlowodowy_wsg84 l
							where l.ID_PROJECT = :ID_PROJECT
							group by l.{$groupByField}
						");
						$sth->bindValue('ID_PROJECT', $idProject, PDO::PARAM_INT);
						$sth->execute();
						$sub_costs = $sth->fetchAll();
					}
					$project['sub_costs'][$layerName]['sub_costs'] = $sub_costs;
				}
			}
		}
		$project['_raw'] = $projectRaw;

		return $project;
	}

	public function fetchUserName($userLogin) {
		$userName = $userLogin;
		// TODO: sql from ADMIN_USERS
		return $userName;
	}

}