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

<?php

Lib::loadClass('RouteBase');
Lib::loadClass('ProcesHelper');
Lib::loadClass('TableAjax');
Lib::loadClass('UserStorageFactory');
Lib::loadClass('Response');
Lib::loadClass('UI');

class Route_Budget extends RouteBase {

	public $_costs = array();
	public $_plan = array();
	public $_projectInfo = array();
	public $_projectPathsOrder = array();

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

	public function defaultAction() {
		$args = array();
		$args['year'] = V::get('year', '', $_REQUEST, 'int');
		$args['_print'] = V::get('_print', '', $_REQUEST, 'int');

		UI::gora();
		UI::menu();
		if (!$args['_print']) {
			$this->menu($args['year']);
		}
		UI::dol();
	}

	public function yearBudgetAction() {
		$args = array();
		$args['year'] = V::get('year', '', $_REQUEST, 'int');
		$args['groups'] = V::get('fltrGroups', array(), $_REQUEST, 'array', array('V', 'filterPositiveInteger'));
		$args['_print'] = V::get('_print', '', $_REQUEST, 'int');

		$hasData = false;
		$groups = null;
		if ($args['year'] > 0) {
			$hasData = $this->fetchDataByYear($args['year'], $args['groups']);
			$groups = $this->getUsedUserGroups();
		}

		UI::gora();
		UI::menu();
		if (!$args['_print']) {
			$this->menu($args['year'], $groups, $args['groups']);
		}

		if (empty($args['year'])) {
			UI::alert('warning', "Nie wybrano roku.");
			UI::dol();
			exit;
		}

		if (!$hasData) {
			UI::alert('warning', "Brak danych na wybrany rok.");
			return;
		}
		//echo'<pre style="border:1px solid red;overflow:auto;max-height:400px">$costs: ';print_r($costs);echo'</pre>';

		$this->printCostsForYear($args['year'], $args['groups']);

		UI::dol();
	}

	public function menu($selectedYear, $groups = array(), $selectedGroups = array()) {
		//UI::menu();
		$year = ($selectedYear)? $selectedYear : date("Y");
		UI::startTag('div', ['class'=>"jumbotron"]);
		UI::startContainer();
		UI::startTag('form', ['class'=>"form-inline", 'method'=>"POST"]);
			UI::emptyTag('input', ['type'=>"hidden", 'name'=>"_task", 'value'=>"yearBudget"]);
			UI::tag('label', ['for'=>"year"], "Zestawienie kosztów projektów. Wybierz rok:");
			UI::startTag('div', ['class'=>"input-group date", 'id'=>"fldZestYear"]);
				UI::emptyTag('input', ['type'=>"text", 'name'=>"year", 'class'=>"form-control", 'value'=>""]);
				UI::tag('span', ['class'=>"input-group-addon"], "<span class=\"glyphicon glyphicon-time\"></span>");
			UI::endTag('div');

			if (!empty($groups)) {
				UI::startTag('div', ['style'=>"margin:8px 0"]);
					UI::tag('label', ['for'=>"fltrGroups"], "Pokaż tylko projekty dostępne dla grup:");
					UI::startTag('select', ['multiple'=>"multiple", 'name'=>"fltrGroups[]", 'size'=>min(5, count($groups) + 1), 'class'=>"form-control"]);
						UI::tag('option', ['value'=>""], " [ Wszystkie ] ");
						foreach ($groups as $idGroup => $groupLdapName) {
							if (in_array($idGroup, $selectedGroups)) {
								UI::tag('option', ['value'=>$idGroup, 'selected'=>"selected"], $groupLdapName);
							} else {
								UI::tag('option', ['value'=>$idGroup], $groupLdapName);
							}
						}
					UI::endTag('select');
				UI::endTag('div');
			}
			UI::tag('button', ['type'=>"submit", 'id'=>"fldZestYearBtn", 'class'=>"btn btn-primary", 'autocomplete'=>"off"], "Pokaż");

		UI::endTag('form');
		UI::startTag('div', ['style'=>"text-align:right"]);
			echo "Edytuj ";
			UI::tag('a', ['href'=>"index.php?_route=Budget&_task=plan&year={$year}",
				'class'=>"btn btn-xs btn-default",
				'title'=>"Plan budżetu (projects_budget_year_month)"], "plan budżetu");
			echo "na rok {$year}";
		UI::endTag('div');

		UI::endContainer();
		UI::endTag('div');
		UI::inlineJS(__FILE__ . '.menu.js', [
			'year' => $year,
			'month' => intval(date("m"))
		]);
	}

	function css() {
		?>
	<style type="text/css">
		.c { text-align:center; }
		.r { text-align:right; }

		.zestawienie-kosztow-tbl { border-collapse:collapse; border:1px solid #aaa; }
		.zestawienie-kosztow-tbl td { border:1px solid #aaa; }
		.zestawienie-kosztow-tbl .p2 { padding:0 2px; }
		.zestawienie-kosztow-tbl .nr { color:#7A7A7A; }
		.zestawienie-kosztow-tbl thead th { border:1px solid #aaa; }
		.zestawienie-kosztow-tbl tbody tr:hover td { background:#cafbfd; }
		.row-selected td {background-color:#d8fded;}
		.showOnlySelected tr { display:none; }
		.showOnlySelected tr.row-selected { display:table-row; }

		.cell-cost { padding:0 2px; min-width:30px; text-align:right; }
		.cell-cost-only_self      { color:#197fe6; }
		.cell-cost-self_and_child { color:#33b2cc; }
		.cell-cost-only_child     { color:#59a680; }
		.cell-plan { padding:0 2px; min-width:30px; text-align:right; color:#777; }
		.cell-procent { padding:0 2px; min-width:20px; text-align:right; color:#777; }
		.cell-procent-below100 { color:#777; }
		.cell-procent-100 { color:#777; }
		.cell-procent-over100 { color:#ff9b00; }
		.cell-procent-over150 { color:#f00; }

		/* print table background colors */
		table td, table th { -webkit-print-color-adjust:exact; }
		table { page-break-after:auto }
		tr    { page-break-inside:avoid; page-break-after:auto; position:relative; }
		td    { page-break-inside:avoid; page-break-after:auto; position:relative; }
		thead { display:table-header-group }
		tfoot { display:table-footer-group }

		#zestawienie-kosztow-projektow.hidden_month_1 .col_month_1 {display:none}
		#zestawienie-kosztow-projektow.hidden_month_2 .col_month_2 {display:none}
		#zestawienie-kosztow-projektow.hidden_month_3 .col_month_3 {display:none}
		#zestawienie-kosztow-projektow.hidden_month_4 .col_month_4 {display:none}
		#zestawienie-kosztow-projektow.hidden_month_5 .col_month_5 {display:none}
		#zestawienie-kosztow-projektow.hidden_month_6 .col_month_6 {display:none}
		#zestawienie-kosztow-projektow.hidden_month_7 .col_month_7 {display:none}
		#zestawienie-kosztow-projektow.hidden_month_8 .col_month_8 {display:none}
		#zestawienie-kosztow-projektow.hidden_month_9 .col_month_9 {display:none}
		#zestawienie-kosztow-projektow.hidden_month_10 .col_month_10 {display:none}
		#zestawienie-kosztow-projektow.hidden_month_11 .col_month_11 {display:none}
		#zestawienie-kosztow-projektow.hidden_month_12 .col_month_12 {display:none}
		.thead__cols_summary,
		.row__summary__cost,
		.row__summary__plan {background-color:#fcf8e3;}
		.thead_col_month .col_month_remove { opacity:0.2; }
		.thead_col_month:hover .col_month_remove { opacity:0.6; }
</style>
		<?php
	}

	function printCostsForYear($year, $groups) {
		$months = array();
		for ($i = 0; $i < 12; $i++) {
			$months[] = $i + 1;
		}
		$this->css();
		//DBG::_('DBG', '>1', "costs", $this->_costs, __CLASS__, __FUNCTION__, __LINE__);

		//echo'<pre>' . json_encode(array_keys($this->_projectPathsOrder)) . '</pre>';
		//echo'<pre>' . json_encode($this->_projectPathsOrder) . '</pre>';
		//echo'<pre>' . json_encode($this->_projectInfo) . '</pre>';
		//echo'<pre>' . json_encode($this->_costs) . '</pre>';
		//echo'<pre>' . json_encode($this->_plan) . '</pre>';
		$projectPathsOrder = array_keys($this->_projectPathsOrder);
		foreach ($projectPathsOrder as $key => $value) {
			$projectPathsOrder[$key] = "" . $value;
		}

		UI::tag('div', ['id' => "widget-budget"]);
		UI::tag('script', ['src'=>"stuff/vendors.js"]);
		UI::tag('script', ['src'=>"stuff/bundle.se_route_budget.js?v=2.1"]);
		UI::inlineJS(__FILE__ . '.yearBudget.js', [
			'year' => $year,
			'today' => date('Y-m-d'),
			'projectPathsOrder' => $projectPathsOrder,
			'projectPathsMap' => $this->_projectPathsOrder,
			'projectInfo' => $this->_projectInfo,
			'costs' => $this->_costs,
			'plan' => $this->_plan,
			'dbg' => false,
		]);
	}

	public function getCost($idProject, $month) {
		if (!array_key_exists($idProject, $this->_costs)) {
			return null;
		}
		if (!array_key_exists($month, $this->_costs[$idProject]->costsByMonth)) {
			return null;
		}
		return $this->_costs[$idProject]->costsByMonth[$month];
	}

	public function getPlan($idProject, $month) {
		if (!array_key_exists($idProject, $this->_plan)) {
			return 0;
		}
		if (!array_key_exists($month, $this->_plan[$idProject])) {
			return 0;
		}
		return $this->_plan[$idProject][$month];
	}

	public function fetchDataByYear($year, $groups) {
		$this->_fetchCostsByYear($year);
		$this->_fetchPlanByYear($year);
		$this->_fetchProjectInfo();
		$this->_buildProjectTree();
		$this->_reacountCostsFromKoresp();
		$this->_filterProjectsByGroups($groups);
		return count($this->_projectInfo) > 1;// $this->_projectInfo[0] - Wszystkie projekty
	}

	public function _filterProjectsByGroups($groups) {
		if (!empty($groups)) {
			foreach ($this->_projectInfo as $idProject => $projInfo) {
				if (!$this->hasGroupsAccessToProjects($idProject, $groups)) {
					$this->_projectInfo[$idProject]->filteredByGroups = true;
				}
			}
		}
	}

	public function _fetchPlanByYear($year) {
		$db = DB::getDB();
		$this->_plan = array();
		$sql = "
			select plan.`ID`
				, plan.`ID_PROJECT` AS `ID_PROJECT`
				, plan.`MONTH_1_VALUE`
				, plan.`MONTH_2_VALUE`
				, plan.`MONTH_3_VALUE`
				, plan.`MONTH_4_VALUE`
				, plan.`MONTH_5_VALUE`
				, plan.`MONTH_6_VALUE`
				, plan.`MONTH_7_VALUE`
				, plan.`MONTH_8_VALUE`
				, plan.`MONTH_9_VALUE`
				, plan.`MONTH_10_VALUE`
				, plan.`MONTH_11_VALUE`
				, plan.`MONTH_12_VALUE`
			from `projects_budget_year_month` plan
			where plan.`year`='{$year}'
			-- TODO: acl
		";
		//echo'<pre style="border:1px solid red;overflow:auto;max-height:400px">';print_r($sql);echo'</pre>';
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$plan = array();
			for ($i = 1; $i <= 12; $i++) {
				$plan[$i] = V::get("MONTH_{$i}_VALUE", 0, $r);
			}
			$this->_plan[$r->ID_PROJECT] = $plan;
		}
		return $this->_plan;
	}

	public function getCostsCategoryDBGAction() {// TODO: RMME
		UI::gora();
		$year = V::get('year', 0, $_GET, 'int');
		if ($year <= 0) throw new Exception("Year not set!");
		$costsCat = $this->_fetchCostsCategoryByYear($year);
		DBG::table("costsCat", $costsCat, __CLASS__, __FUNCTION__, __LINE__);
/* TEST COST_TYPE for proj 0-4132-3934
		SELECT k.ID, k.COST_VALUE, k.INCOME_VALUE, k.COST_TYPE, k.ID_PROJECT
		FROM  `IN7_DZIENNIK_KORESP` k
		WHERE  k.`ID_PROJECT` =3934
			and k.K_DATA_OTRZYMANEJ_KORESP like '2016-01-%'
*/
		$rows = DB::getPDO()->fetchAll("
			SELECT k.ID
				, k.COST_VALUE
				, k.INCOME_VALUE
				, k.COST_TYPE
				, k.ID_PROJECT
			FROM `IN7_DZIENNIK_KORESP` k
			where ((k.COST_VALUE != 0) or (k.INCOME_VALUE != 0))
				and k.K_DATA_OTRZYMANEJ_KORESP like '{$year}-%'
			ORDER BY k.`ID` desc
		");
		DBG::table("rows", $rows, __CLASS__, __FUNCTION__, __LINE__);
	}
	public function getCostsCategoryAction() {
		$year = V::get('year', 0, $_GET, 'int');
		try {
			if ($year <= 0) throw new Exception("Year not set!");
			$costsCat = $this->_fetchCostsCategoryByYear($year);
			Response::sendJsonExit($costsCat);
		} catch (Exception $e) {
			$response = array();
			$response['type'] = 'danger';
			$response['msg'] = $e->getMessage();
			Response::sendJsonExit($response);
		}
	}

	public function _fetchCostsCategoryByYear($year) {
		// TODO: mv KATEGORIA_KOSZTU (enum) to COST_TYPE (varchar(32)) in pro-netmedia.pl
		return DB::getPDO()->fetchAll("
			select k.ID
				, k.COST_TYPE
			from IN7_DZIENNIK_KORESP k
			where ((k.COST_VALUE != 0) or (k.INCOME_VALUE != 0))
				and k.K_DATA_OTRZYMANEJ_KORESP like '{$year}-%'
				and k.COST_TYPE != ''
			-- TODO: acl
		");
	}

	public function _fetchCostsByYear($year) {
		$db = DB::getDB();
		$this->_costs = array();
		$sql = "
			select k.`ID`
				, k.`ID_PROJECT` AS `ID_PROJECT`
				, date_format(k.`K_DATA_OTRZYMANEJ_KORESP`,'%Y-%m') AS `MONTH`
				, k.`COST_VALUE` AS `COST`
				, k.`INCOME_VALUE` AS `INCOME`
				, k.`TRANSFER_OPPOSITE_ID_PROJECT`
				, k.`path`
				, IF(k.`TRANSFER_OPPOSITE_ID_PROJECT`>0
					, (select p.`path`
							from `IN7_MK_BAZA_DYSTRYBUCJI` p
							where p.`ID`=k.`TRANSFER_OPPOSITE_ID_PROJECT`
							limit 1
						)
					, '') as TRANSFER_OPPOSITE_PROJECT_PATH
				, k.`K_ZAWARTOS`
				, k.`K_DATA_OTRZYMANEJ_KORESP`
				, k.`K_NR_OTRZYM_KORESP`
				, k.`K_OD_KOGO`
			from `IN7_DZIENNIK_KORESP` k
			where ((k.`COST_VALUE` != 0) or (k.`INCOME_VALUE` != 0))
				and k.`K_DATA_OTRZYMANEJ_KORESP` like '{$year}-%'
			-- TODO: acl
		";
		//echo'<pre style="border:1px solid red;overflow:auto;max-height:400px">';print_r($sql);echo'</pre>';
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$vProjId = ($r->ID_PROJECT > 0)? $r->ID_PROJECT : 0;
			$vProjPaths = array();
			$vProjIds = array(0 => true);
			if ($r->ID_PROJECT > 0) {
				$vProjPaths[] = $r->ID_PROJECT;
				if (!empty($r->path)) {
					$vProjPaths[] = $r->path;
				}
			}
			if ($r->TRANSFER_OPPOSITE_ID_PROJECT > 0) {
				$vProjPaths[] = $r->TRANSFER_OPPOSITE_ID_PROJECT;
				if (!empty($r->TRANSFER_OPPOSITE_PROJECT_PATH)) {
					$vProjPaths[] = $r->TRANSFER_OPPOSITE_PROJECT_PATH;
				}
			}
			if (!empty($vProjPaths)) {
				//echo'<p>DBG:$r->ID_PROJECT['.$r->ID_PROJECT.']: $vProjPaths = ' . json_encode($vProjPaths) . '</p>';
				$vProjPaths = implode('-', $vProjPaths);
				$projIds = explode('-', $vProjPaths);
				//echo'<p>DBG:$r->ID_PROJECT['.$r->ID_PROJECT.']: $projIds = ' . json_encode($projIds) . '</p>';
				foreach ($projIds as $vProjId) {
					if ($vProjId > 0) $vProjIds[$vProjId] = true;
				}
			}
			{
				$projectZeroInfo = new stdClass();
				$projectZeroInfo->ID_PROJECT = 0;
				$projectZeroInfo->costsByMonth = array();
				$projectZeroInfo->korespByMonth = array();
				$this->_costs[0] = $projectZeroInfo;
			}
			foreach ($vProjIds as $vProjId => $vBool) {
				if (!array_key_exists($vProjId, $this->_costs)) {
					$projectInfo = new stdClass();
					$projectInfo->ID_PROJECT = $vProjId;
					$projectInfo->costsByMonth = array();
					$projectInfo->korespByMonth = array();
					$this->_costs[$vProjId] = $projectInfo;
				}
			}
			$korespInfo = new stdClass();
			$korespInfo->ID = $r->ID;
			$korespInfo->MONTH = $r->MONTH;
			$korespInfo->K_ZAWARTOS = $r->K_ZAWARTOS;
			$korespInfo->K_DATA_OTRZYMANEJ_KORESP = $r->K_DATA_OTRZYMANEJ_KORESP;
			$korespInfo->K_NR_OTRZYM_KORESP = $r->K_NR_OTRZYM_KORESP;
			$korespInfo->K_OD_KOGO = $r->K_OD_KOGO;
			$monthNum = intval(substr($r->MONTH, 5, 2));
			if ($r->ID_PROJECT > 0) {
				if ($r->TRANSFER_OPPOSITE_ID_PROJECT > 0) {
					$korespOppositeInfo = clone $korespInfo;

					$korespInfo->COST = $r->COST;
					$korespInfo->INCOME = $r->INCOME;
					$korespInfo->TRANSFER_OPPOSITE_ID_PROJECT_TO = $r->TRANSFER_OPPOSITE_ID_PROJECT;
					$this->_costs[$r->ID_PROJECT]->korespByMonth[$monthNum][] = $korespInfo;

					$korespOppositeInfo->COST = -1 * $r->COST;
					$korespOppositeInfo->INCOME = -1 * $r->INCOME;
					$korespOppositeInfo->TRANSFER_OPPOSITE_ID_PROJECT_FROM = $r->ID_PROJECT;
					$this->_costs[$r->TRANSFER_OPPOSITE_ID_PROJECT]->korespByMonth[$monthNum][] = $korespOppositeInfo;
				} else {
					$korespInfo->COST = $r->COST;
					$korespInfo->INCOME = $r->INCOME;
					$this->_costs[$r->ID_PROJECT]->korespByMonth[$monthNum][] = $korespInfo;
				}
			} else {
				$korespInfo->COST = $r->COST;
				$korespInfo->INCOME = $r->INCOME;
				$this->_costs[0]->korespByMonth[$monthNum][] = $korespInfo;
			}
		}
		return $this->_costs;
	}

	public function _fetchProjectInfo() {
		$db = DB::getDB();
		$hasAccessForAllProjects = true;
		$projectIds = array();
		$projectsFromCostIds = array_keys($this->_costs);
		foreach ($projectsFromCostIds as $idProject) $projectIds[$idProject] = true;
		$projectsFromPlanIds = array_keys($this->_plan);
		foreach ($projectsFromPlanIds as $idProject) $projectIds[$idProject] = true;
		foreach ($projectIds as $idProject => $vBool) $this->_projectInfo[$idProject] = new stdClass();
		$projectIds = array_keys($projectIds);
		$sqlProjIds = "'" . implode("','", $projectIds) . "'";
		$sql = "
			select p.`ID`
				, p.`P_ID`
				, p.`path`
				, p.`M_DIST_DESC`
				, p.`A_ADM_COMPANY` as aclGroupWrite
				, p.`A_CLASSIFIED` as aclGroupRead
				, p.`L_APPOITMENT_USER` as aclOwner
			from `IN7_MK_BAZA_DYSTRYBUCJI` p
			where p.`ID` in({$sqlProjIds})
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$this->_projectInfo[$r->ID]->path = $r->path;
			$this->_projectInfo[$r->ID]->M_DIST_DESC = $r->M_DIST_DESC;
			$this->_projectInfo[$r->ID]->aclGroupRead = $r->aclGroupRead;
			$this->_projectInfo[$r->ID]->hasAccess = $this->_userHasAccessToProject($r);
			if (!$this->_projectInfo[$r->ID]->hasAccess) $hasAccessForAllProjects = false;
		}
		$this->_projectInfo[0]->path = "0";
		$this->_projectInfo[0]->M_DIST_DESC = "Wszystkie projekty";
		$this->_projectInfo[0]->hasAccess = $hasAccessForAllProjects;

		// TODO: fetch every missing parent project by path
	}

	public function hasAccessToProject($idProject) {
		if ($idProject >= 0) {
			if (array_key_exists($idProject, $this->_projectInfo)) {
				return V::get('hasAccess', false, $this->_projectInfo[$idProject]);
			}
		}
		return false;
	}

	public function hasGroupsAccessToProjects($idProject, $groups) {
		$selectedUserGroupNames = array();
		$userGroups = $this->_getLdapGroupsNames();
		DBG::_('DBG', '>1', "hasGroupsAccessToProjects({$idProject}). userGroups", $userGroups, __CLASS__, __FUNCTION__, __LINE__);
		foreach ($groups as $idGroup) {
			$selectedUserGroupNames[$idGroup] = $userGroups[$idGroup];
		}
		DBG::_('DBG', '>1', "hasGroupsAccessToProjects({$idProject}). selectedUserGroupNames", $selectedUserGroupNames, __CLASS__, __FUNCTION__, __LINE__);
		if ($idProject >= 0) {
			if (array_key_exists($idProject, $this->_projectInfo)) {
				DBG::_('DBG', '>1', "hasGroupsAccessToProjects({$idProject}). _projectInfo[$idProject]", $this->_projectInfo[$idProject], __CLASS__, __FUNCTION__, __LINE__);
				$alcGroupRead = V::get('aclGroupRead', null, $this->_projectInfo[$idProject]);
				if (!$alcGroupRead) {
					return false;
				}
				if (in_array($alcGroupRead, $selectedUserGroupNames)) {
					return true;
				}
			}
		}
		return false;
	}

	public function _userHasAccessToProject($project) {
		$groups = $this->_getLdapGroupsNames();
		$userLogin = User::getLogin();
		if ($project->aclOwner == $userLogin) {
			return true;
		}
		else if (in_array($project->aclGroupRead, $groups)) {
			return true;
		}
		return false;
	}

	public function getUsedUserGroups() {
		$groups = array();
		$userGroups = $this->_getLdapGroupsNames();
		DBG::_('DBG', '>2', "getUsedUserGroups(). userGroups:", $userGroups, __CLASS__, __FUNCTION__, __LINE__);
		foreach ($this->_projectInfo as $projectInfo) {
			if (!empty($projectInfo->aclGroupRead)) {
				$groupKey = array_search($projectInfo->aclGroupRead, $userGroups);
				if ($groupKey !== false) {
					$groups[$groupKey] = $projectInfo->aclGroupRead;
				}
			}
		}
		DBG::_('DBG', '>2', "getUsedUserGroups(). groups:", $groups, __CLASS__, __FUNCTION__, __LINE__);
		return $groups;
	}

	public function _getLdapGroupsNames() {
		$userGroupsByZasobId = array();
		$usrStorageMacOSX = UserStorageFactory::getStorage('MacOSX');
		if (!$usrStorageMacOSX) {
			// throw new Exception("Error storage 'MacOSX' not exists!");
			$userGroups = User::getGroups();
			$usrStorageDB = UserStorageFactory::getStorage('DB');
			//DBG::_(true, true, "userGroups", $userGroups, __CLASS__, __FUNCTION__, __LINE__);
			$rows = DB::getPDO()->fetchAll("
				select distinct a.perm_group_uid
					from (
						SELECT DISTINCT `A_ADM_COMPANY` as perm_group_uid FROM `IN7_MK_BAZA_DYSTRYBUCJI`
						union
						SELECT DISTINCT `A_CLASSIFIED` as perm_group_uid FROM `IN7_MK_BAZA_DYSTRYBUCJI`
						union
						SELECT DISTINCT `A_ADM_COMPANY` as perm_group_uid FROM `IN7_DZIENNIK_KORESP`
						union
						SELECT DISTINCT `A_CLASSIFIED` as perm_group_uid FROM `IN7_DZIENNIK_KORESP`
					) as a
			");
			//DBG::_(true, true, "rows", $rows, __CLASS__, __FUNCTION__, __LINE__);
			DBG::_('DBG', '>3', "userGroups:", $userGroups, __CLASS__, __FUNCTION__, __LINE__);
			$parentGroups = array();
			foreach ($userGroups as $idGroup => $group) {
				$parentGroups = $usrStorageDB->fetchParentGroups($idGroup);
				foreach ($parentGroups as $idParentGroup => $parentGroup) {
					$parentGroups[$idParentGroup] = true;
				}
			}
			DBG::_('DBG', '>3', "parentGroups:", $parentGroups, __CLASS__, __FUNCTION__, __LINE__);
			$grandParentGroups = array();
			foreach ($parentGroups as $idGroup => $group) {
				$parentGroups = $usrStorageDB->fetchParentGroups($idGroup);
				foreach ($parentGroups as $idParentGroup => $parentGroup) {
					$grandParentGroups[$idParentGroup] = true;
				}
			}
			DBG::_('DBG', '>3', "grandParentGroups:", $grandParentGroups, __CLASS__, __FUNCTION__, __LINE__);
			$userGroupIdsAll = array();
			foreach ($userGroups as $idGroup => $group) $userGroupIdsAll[$idGroup] = true;
			foreach ($parentGroups as $idGroup => $group) $userGroupIdsAll[$idGroup] = true;
			foreach ($grandParentGroups as $idGroup => $group) $userGroupIdsAll[$idGroup] = true;
			DBG::_('DBG', '>3', "userGroupIdsAll:", $userGroupIdsAll, __CLASS__, __FUNCTION__, __LINE__);
			foreach ($rows as $row) {
				$perm_group_uid = $row['perm_group_uid'];
				if (empty($perm_group_uid)) continue;
				$parts = explode('_', $perm_group_uid, 2);
				DBG::_('DBG', '>3', "loop - $perm_group_uid:", $parts, __CLASS__, __FUNCTION__, __LINE__);
				$idGroup = $parts[0];
				DBG::_('DBG', '>3', "loop - $perm_group_uid - idGroup({$idGroup}):", array_key_exists($idGroup, $userGroups), __CLASS__, __FUNCTION__, __LINE__);
				if (array_key_exists($idGroup, $userGroupIdsAll)) {
					$userGroupsByZasobId[$idGroup] = $perm_group_uid;
				}
			}
			DBG::_('DBG', '>3', "_getLdapGroupsNames(). userGroupsByZasobId:", $userGroupsByZasobId, __CLASS__, __FUNCTION__, __LINE__);
		} else {
			$userGroups = User::getLdapGroupsNames();
			DBG::_('DBG', '>3', "_getLdapGroupsNames(). userGroups:", $userGroups, __CLASS__, __FUNCTION__, __LINE__);
			foreach ($userGroups as $uidGroup) {
				$idZasob = $usrStorageMacOSX->getGroupIdFromUid($uidGroup);
				if ($idZasob) {
					$userGroupsByZasobId[$idZasob] = $uidGroup;
				} else {
					//$userGroupsByZasobId[$uidGroup] = $uidGroup;
				}
			}
		}
		DBG::_('DBG', '>3', "_getLdapGroupsNames(). userGroupsByZasobId:", $userGroupsByZasobId, __CLASS__, __FUNCTION__, __LINE__);
		return $userGroupsByZasobId;
	}

	public function _reacountCostsFromKoresp() {
		$projHasCostSelfIds = array();
		foreach ($this->_costs as $kProjId => $vProjInfo) {
			$projectPath = $this->_projectInfo[$kProjId]->path;
			foreach ($vProjInfo->korespByMonth as $kMonthNum => $vKorespList) {
				$this->_createCostIfNotDefined($kProjId, $kMonthNum);
				foreach ($vKorespList as $vKoresp) {
					$this->_costs[$kProjId]->costsByMonth[$kMonthNum]->COST_SELF += $vKoresp->COST;
					$this->_costs[$kProjId]->costsByMonth[$kMonthNum]->INCOME_SELF += $vKoresp->INCOME;
				}
				$projHasCostSelfIds[$kProjId][$kMonthNum] = $projectPath;
			}
		}
		//echo'<pre style="width:600px;border:1px solid red;max-height:300px;overflow:auto;">$projHasCostSelfIds: ';print_r($projHasCostSelfIds);echo'</pre>';
		foreach ($projHasCostSelfIds as $kProjId => $vProjMonthsList) {
			if ($kProjId <= 0) continue;
			foreach ($vProjMonthsList as $kMonthNum => $vProjPath) {
				$vProjPathIds = explode('-', $vProjPath);
				$vProjPathIds = array_reverse($vProjPathIds);
				$vProjMonthCostSelf = $this->_costs[$kProjId]->costsByMonth[$kMonthNum]->COST_SELF;
				$vProjMonthIncomeSelf = $this->_costs[$kProjId]->costsByMonth[$kMonthNum]->INCOME_SELF;
				foreach ($vProjPathIds as $vProjId) {
					if ($vProjId == $kProjId) continue;
					$this->_createCostIfNotDefined($vProjId, $kMonthNum);
					$this->_costs[$vProjId]->costsByMonth[$kMonthNum]->COST_CHILD += $vProjMonthCostSelf;
					$this->_costs[$vProjId]->costsByMonth[$kMonthNum]->INCOME_CHILD += $vProjMonthIncomeSelf;
				}
			}
		}
		// recount total
		foreach ($this->_costs as $vProjId => $vProjInfo) {
			foreach ($vProjInfo->costsByMonth as $kMonthNum => $vCost) {
				$this->_createCostIfNotDefined($vProjId, $kMonthNum);
				$this->_costs[$vProjId]->costsByMonth[$kMonthNum]->COST_TOTAL = $vCost->COST_SELF + $vCost->COST_CHILD;
				$this->_costs[$vProjId]->costsByMonth[$kMonthNum]->INCOME_TOTAL = $vCost->INCOME_SELF + $vCost->INCOME_CHILD;
			}
		}
	}

	public function _createCostIfNotDefined($projId, $monthNum) {
		if (empty($this->_costs[$projId]->costsByMonth[$monthNum])) {
			$vEmptyCost = new stdClass();
			$vEmptyCost->COST_SELF = 0;
			$vEmptyCost->INCOME_SELF = 0;
			$vEmptyCost->COST_CHILD = 0;
			$vEmptyCost->INCOME_CHILD = 0;
			$vEmptyCost->COST_TOTAL = 0;
			$vEmptyCost->INCOME_TOTAL = 0;
			$this->_costs[$projId]->costsByMonth[$monthNum] = $vEmptyCost;
		}
	}

	public function _buildProjectTree() {
		$this->_projectPathsOrder = array();
		foreach ($this->_projectInfo as $idProject => $projectInfo) {
			$this->_projectPathsOrder[$projectInfo->path] = $idProject;
		}
		//echo'<pre style="width:600px;border:1px solid red;max-height:300px;overflow:auto;">projPaths: ';print_r($this->_projectPathsOrder);echo'</pre>';
		uksort($this->_projectPathsOrder, array($this, 'sortPathsCallback'));
		//echo'<pre style="width:600px;border:1px solid red;max-height:300px;overflow:auto;">projPaths sorted: ';print_r($this->_projectPathsOrder);echo'</pre>';
		return $this->_projectPathsOrder;
	}

	public function sortPathsCallback($a, $b) {
		$ea = explode('-', $a);
		$eb = explode('-', $b);
		$la = count($ea);
		$lb = count($eb);
		$lmin = min($la, $lb);
		for ($i = 0; $i < $lmin; $i++) {
			if ($ea[$i] < $eb[$i]) {
				return -1;
			} else if ($ea[$i] > $eb[$i]) {
				return 1;
			}
		}
		return $la - $lb;
	}

	public function updatePaths() {
		$sqlList = array();
		$sqlList['updateAllPaths'] = <<<SQL
update `projects_budget_year_month` b
set path = (select coalesce(
										(select p.`path` from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=b.`ID_PROJECT` limit 1)
										, '?'));
SQL;
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		foreach ($sqlList as $sqlName => $sql) {
			$res = $db->query($sql);
			if ($db->has_errors()) {
				throw new Exception("DB Errors at sql '{$sqlName}': " . implode("\n<br>", $db->get_errors()));
			}
		}
	}

	public function reinstall() {
		$sqlList = array();
		$sqlList['RemoveTrigger_BudgetPlan_BeforeInsert'] = "DROP TRIGGER IF EXISTS `projects_budget_year_month_BEFORE_INSERT`";
		$sqlList['CreateTrigger_BudgetPlan_BeforeInsert'] = "
CREATE DEFINER=`root`@`localhost` TRIGGER `projects_budget_year_month_BEFORE_INSERT` BEFORE INSERT ON `projects_budget_year_month`
FOR EACH ROW BEGIN
	IF NEW.ID_PROJECT IS NOT NULL and NEW.ID_PROJECT>0 THEN
		SET NEW.path = (select coalesce(
			(select p.`path` from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=NEW.`ID_PROJECT` limit 1)
			, '?'
		));
	END IF;
END
		";
		$sqlList['RemoveTrigger_BudgetPlan_BeforeUpdate'] = "DROP TRIGGER IF EXISTS `projects_budget_year_month_BEFORE_UPDATE `";
		// throws errors:
		//  #1146 - Table '{DATABASE_NAME}.P5-MSG:Route_FixZasobPath:ERROR: Loop detected ID=PARENT_ID' doesn't exist
		//  #1146 - Table '{DATABASE_NAME}.P5-MSG:Route_FixZasobPath:ERROR: Parent item not exists' doesn't exist
		//  #1146 - Table '{DATABASE_NAME}.P5-MSG:Route_FixZasobPath:ERROR: Loop detected in path' doesn't exist
		$sqlList['CreateTrigger_BudgetPlan_BeforeUpdate'] = "
CREATE DEFINER=`root`@`localhost` TRIGGER `projects_budget_year_month_BEFORE_UPDATE` BEFORE UPDATE ON `projects_budget_year_month`
FOR EACH ROW BEGIN
	IF NEW.ID_PROJECT IS NULL THEN
		SET NEW.path = '';
	ELSEIF OLD.ID_PROJECT IS NULL or NEW.ID_PROJECT<>OLD.ID_PROJECT THEN
		IF NEW.ID_PROJECT>0 THEN
			SET NEW.path = (select coalesce(
							(select p.`path` from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=NEW.`ID_PROJECT` limit 1)
							, '?'));
		ELSE
			SET NEW.path = '';
		END IF;
	END IF;
END
		";
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		foreach ($sqlList as $sqlName => $sql) {
			$res = $db->query($sql);
			if ($db->has_errors()) {
				throw new Exception("DB Errors at sql '{$sqlName}': " . implode("\n<br>", $db->get_errors()));
			}
		}
	}

	public function planAction() {
		UI::gora();
		UI::menu();

		$args = array();
		$args['year'] = V::get('year', '', $_REQUEST, 'int');
		if ($args['year'] > 0) {
			$_REQUEST['ff_YEAR'] = $_GET['ff_YEAR'] = $args['year'];
		}

		$this->menu($args['year']);

		if ($args['year'] > 0) {
			UI::startContainer();
			UI::tag('a', ['class'=>"btn btn-xs btn-default", 'href'=>"#"], "Utwórz plan na kolejny rok na podstawie danych z {$args['year']} roku");
			UI::endContainer();
		}

		$zasobObj = ProcesHelper::getZasobTableInfoByUri('default_db/projects_budget_year_month');
		if (!$zasobObj) {
			UI::alert('danger', "Zasob Tabela Plan budżetu (projects_budget_year_month) nie istnieje");
			// TODO: btn utwórz
			UI::dol();
			return;
		}

		$userAcl = User::getAcl();
		$userAcl->fetchGroups();

		if (!$userAcl->hasTableAcl($zasobObj->ID)) {
			UI::alert('danger', "Brak uprawnień do tabeli Plan budżetu (projects_budget_year_month)");
			UI::dol();
			return;
		}

		$tblAcl = $userAcl->getTableAcl($zasobObj->ID);

		$forceTblAclInit = ('1' == V::get('_force', '', $_GET));
		$tblAcl->init($forceTblAclInit);

		$forceFilterInit = array();
		$filterInit = new stdClass();
		$filterInit->currSortCol = 'ID';
		$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;
			}
		}

		$syncUrl = Request::getPathUri() . 'index.php?_route=ViewTableAjax&namespace=' . $tblAcl->getNamespace();
		$tbl = new TableAjax($tblAcl);
		$tbl->setSyncUrl($syncUrl);
		$tblLabel = array();
		if (!empty($zasobObj->DESC_PL)) $tblLabel []= $zasobObj->DESC_PL;
		if (!empty($zasobObj->OPIS))    $tblLabel []= $zasobObj->OPIS;
		$tblLabel = implode(" - ", $tblLabel);
		$tbl->setLabel($tblLabel);
		$tbl->setFilterInit($filterInit);
		if (!empty($forceFilterInit)) $tbl->setForceFilterInit($forceFilterInit);
		$tbl->addRowFunction('edit');
		$tbl->addRowFunction('hist');
		$tbl->addRowFunction('files');
		$tbl->addRowFunction('cp');
		echo $tbl->render();

		UI::dol();
	}

}