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

<?php

Lib::loadClass('RouteBase');

class Route_Budget extends RouteBase {

	private $_costs = array();
	private $_plan = array();
	private $_projectInfo = array();
	private $_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');

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

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

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

		if (empty($args['year'])) {
			?>
			<div class="alert alert-warning">
				Nie wybrano roku.
			</div>
			<?php
			SE_Layout::dol();
			exit;
		}

		$hasData = $this->fetchDataByYear($args['year']);
		if (!$hasData) {
			?>
			<div class="alert alert-warning">
				Brak danych na wybrany rok.
			</div>
			<?php
			return;
		}
		//echo'<pre style="border:1px solid red;overflow:auto;max-height:400px">$costs: ';print_r($costs);echo'</pre>';

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

		SE_Layout::dol();
	}

	private function menu($selectedYear) {
		//SE_Layout::menu();
		$year = ($selectedYear)? $selectedYear : date("Y");
		?>
<div class="jumbotron">
  <div class="container">
		<form class="form-inline" method="POST">
			<input type="hidden" name="_task" value="yearBudget" />
			<label for="year">Zestawienie kosztów projektów na podstawie korespondencji:</label>
			<div class="input-group date" id="fldZestYear">
				<input type="text" name="year" class="form-control" value="" />
				<span class="input-group-addon"><span class="glyphicon glyphicon-time"></span></span>
			</div>
			<button type="submit" id="fldZestYearBtn" class="btn btn-primary" autocomplete="off">
				Pokaż
			</button>
		</form>
	</div>
</div>
<script type="text/javascript">
jQuery(document).ready(function () {
  jQuery('#fldZestYearBtn').on('click', function () {
    jQuery(this).text(jQuery(this).text() + '...').attr('disabled', 'disabled');
		jQuery(this).parent().submit();
  })

	jQuery("#fldZestYear").datetimepicker({
		format: "YYYY",
		defaultDate: new Date(<?php echo $year; ?>, <?php echo intval(date("m")); ?>, 1),
		// minDate: new Date(2014, 11, 1),
		// maxDate: "<?php echo date("Y"); ?>"
	});
});
</script>
		<?php
	}

	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-over200 { 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 }
	</style>
		<?php
	}

	function printCostsForYear($year) {
		$months = array();
		for ($i = 0; $i < 12; $i++) {
			$months[] = $i + 1;
		}
		$this->css();
		if(V::get('DBG','',$_GET)){echo'<pre style="width:600px;border:1px solid red;max-height:300px;overflow:auto;display:none">';print_r($this->_costs);echo'</pre>';}
?>
<div class="container">
	<div style="float:right;color:#aaa;"><?php echo date("Y-m-d"); ?></div>
	<h1>Zestawienie kosztów projektów na rok <?php echo $year; ?></h1>
</div>
	<table cellspacing="0" cellpadding="0" border="0" id="zestawienie-kosztow-projektow" class="zestawienie-kosztow-tbl">
	<thead>
		<tr>
			<td colspan="3" class="p2">
				<span class="pull-right"><b>miesiąc</b></span>
			</td>
			<?php foreach ($months as $month) { ?>
				<th class="c" colspan="3"><?php echo sprintf("%02d", $month); ?></th>
			<?php } ?>
		</tr>
		<tr>
			<td colspan="3" class="p2">
				<span class="pull-left">
					<input type="checkbox" onclick="return showHideAll(this);"/> pokaż tylko zaznaczone
				</span>
			</td>
			<?php foreach ($months as $month) { ?>
				<th class="c" title="Koszty wprowadzone do korespondencji">Koszty</th>
				<th class="c" title="Plan budżetu">Plan</th>
				<th class="c" title="Procent przekroczenia planu">%</th>
			<?php } ?>
		</tr>
	</thead>
	<tbody>
	<?php $t = 1; ?>
	<?php foreach ($this->_projectPathsOrder as $projPath => $projId) : ?>
		<?php $projectID   = $projId; ?>
		<?php $projectDesc = $this->_projectInfo[$projId]->M_DIST_DESC; ?>
		<?php $projectPath = $this->_projectInfo[$projId]->path; ?>
		<tr class="row-<?php echo ($t = 1 - $t); ?>"
				data-proj_id="<?php echo $projectID; ?>"
				data-path="<?php echo $projectPath; ?>">
			<td class="p2 r nr">
				<input type="checkbox" name="selectedProject" onclick="return selectProject(this);" value="<?php echo $projectID; ?>" />
			</td>
			<td class="p2 l nr"><nobr><?php echo $projectPath; ?></nobr></td>
			<td class="p2" style="max-width:300px;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;" title="<?php echo $projectDesc; ?>"><?php echo $projectDesc; ?></td>
			<?php foreach ($months as $month) : ?>
				<?php $vMonthCost = $this->getCost($projectID, $month); ?>
				<?php $monthCostTotal = ($vMonthCost)? $vMonthCost->COST_TOTAL : 0; ?>
				<?php if (!$vMonthCost) : ?>
			<td style="min-width:30px">&nbsp;</td>
				<?php else : ?>
					<?php
						$vCostChildOut = number_format($vMonthCost->COST_CHILD, 2);
						$vCostSelfOut = number_format($vMonthCost->COST_SELF, 2);
						$vCostTotalOut = number_format($vMonthCost->COST_TOTAL, 2);
						$title = "Koszt projektu {$vCostSelfOut} / koszt podprojektów $vCostChildOut";
						$cellCostCls = '';
						if ($vMonthCost->COST_CHILD > 0) {
							if ($vMonthCost->COST_SELF > 0) {
								$cellCostCls = 'cell-cost-self_and_child';
							} else {
								$cellCostCls = 'cell-cost-only_child';
							}
						} else {
							$cellCostCls = 'cell-cost-only_self';
						}
					?>
			<td class="cell-cost <?php echo $cellCostCls; ?>"
					data-month_num="<?php echo $month; ?>"
					data-cost="<?php echo $vCostTotalOut; ?>"
					data-proj_path="<?php echo $projectPath; ?>"
					data-proj_id="<?php echo $projectID; ?>">
				<span class="ttip" title="<?php echo $title; ?>"><?php echo $vCostTotalOut; ?></span>
			</td>
				<?php endif; ?>
			<td class="cell-plan">
				<?php $monthPlan = $this->getPlan($projectID, $month); ?>
				<?php $monthPlanOut = number_format($monthPlan, 2); ?>
				<?php if ($monthPlan > 0) : ?>
					<?php echo $monthPlan; ?>
				<?php else : ?>
					&nbsp;
				<?php endif; ?>
			</td>
			<?php
				$cellProcentCls = '';
				$procentOut = '&nbsp;';
				$monthPlan = $this->getPlan($projectID, $month);
				if ($monthPlan > 0) {
					$procentOut = round(($monthCostTotal * 100) / $monthPlan);
					if ($procentOut > 200) {
						$cellProcentCls = 'cell-procent-over200';
					} else if ($procentOut > 100) {
						$cellProcentCls = 'cell-procent-over100';
					} else if ($procentOut == 100) {
						$cellProcentCls = 'cell-procent-100';
					} else {
						$cellProcentCls = 'cell-procent-below100';
					}
				}
			?>
			<td class="cell-procent <?php echo $cellProcentCls; ?>"><?php echo $procentOut; ?></td>
			<?php endforeach; ?>
		</tr>
	<?php endforeach; ?>
	</tbody>
	</table>
	<script>
jQuery(document).ready(function() {
	jQuery('#zestawienie-kosztow-projektow')
		.find('.cell-cost')
		.on('click', function(e) {
			var $n = jQuery(this),
				$modal = jQuery('#projectMonthCostDetails'),
				projId = $n.data('proj_id'),
				monthNum = $n.data('month_num'),
				projPath = '' + $n.data('proj_path'),
				projPathLength = projPath.length,
				modalBody = document.createElement('table'),
				totalDescrCell = document.createElement('td'),
				totalCostCell = document.createElement('td'),
				totalEmptyCell = document.createElement('td'),
				totalRow = document.createElement('tr');
			modalBody.className = 'zestawienie-kosztow-tbl';
			modalBody.style.width = '100%';
			// $n.data() = {proj_id: 3943, proj_path: "0-4132-3943", month_num: 2}
			// table#proj-koresp-info > tbody#row-proj-2055-koresp-by-month-2
			jQuery('#proj-koresp-info').find('tbody').each(function(i, tbody) {
				var vPath = jQuery(tbody).data('proj_path'),
						vMonthNum = jQuery(tbody).data('month_num');
				if (monthNum != vMonthNum) return;
				if (projPathLength > vPath.length) return;
				if (projPath == vPath
						|| (projPathLength < vPath.length && projPath + '-' == vPath.substr(0, projPathLength + 1))
					 ) {
					modalBody.appendChild(tbody.cloneNode(true));
				}
			});
			{
				totalDescrCell.className = 'p2';
				totalDescrCell.style.textAlign = 'right';
				totalDescrCell.setAttribute('colspan', '2');
				totalDescrCell.appendChild(document.createTextNode('Suma:'));
				totalCostCell.className = 'cell-cost';
				totalCostCell.style.textAlign = 'right';
				totalCostCell.appendChild(document.createTextNode($n.data('cost')));
				totalRow.appendChild(totalDescrCell);
				totalRow.appendChild(totalCostCell);
				modalBody.appendChild(totalRow);
			}
			$modal.find('.modal-title').text('Koszty projektu nr ' + projId + ' w miesiącu <?php echo $year; ?>-' + ((monthNum > 9)? monthNum : '0' + monthNum));
			$modal.find('.modal-body').html(modalBody);
			$modal.modal({});
		})
})
	</script>

	<!-- Modal -->
	<div class="modal fade" id="projectMonthCostDetails" tabindex="-1" role="dialog" aria-labelledby="projectCostDMonthetailsLabel">
		<div class="modal-dialog" role="document" style="min-width:800px;">
			<div class="modal-content">
				<div class="modal-header">
					<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
					<h4 class="modal-title" id="projectCostDMonthetailsLabel">Modal title</h4>
				</div>
				<div class="modal-body">
					TODO: projectMonthCostDetails...
				</div>
				<div class="modal-footer">
					<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
				</div>
			</div>
		</div>
	</div>
	<table id="proj-koresp-info" style="display:none">
		<?php foreach ($this->_projectPathsOrder as $projPath => $projId) : ?>
			<?php
				$projectInfo = $this->_costs[$projId];
				if (!$projectInfo) {
					continue;
				}
				$projectID   = $projId;
				$projectDesc = $this->_projectInfo[$projId]->M_DIST_DESC;
				$projectPath = $this->_projectInfo[$projId]->path;
			?>
			<?php if (!empty($projectInfo->korespByMonth)) : ?>
				<?php foreach ($projectInfo->korespByMonth as $kMonth => $vKorespMonthList) : ?>
					<tbody id="row-proj-<?php echo $projectID; ?>-koresp-by-month-<?php echo $kMonth; ?>"
								 data-month_num="<?php echo $kMonth; ?>"
								 data-proj_path="<?php echo $projectPath; ?>"
								 data-proj_id="<?php echo $projectID; ?>">
						<tr>
							<td style="padding:3px;font-size:1em;background:#eee;"><nobr><?php echo $projectPath; ?></nobr></td>
							<td colspan="3" style="padding:3px;font-size:1.1em;background:#eee;max-width:500px;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;" title="<?php echo $projectDesc; ?>">
								<!-- Koszty projektu nr <?php echo $projectID; ?> -->
								<!-- - miesiąc <?php echo $year; ?>-<?php echo sprintf("%02d", $kMonth); ?> -->
								<?php echo $projectDesc; ?>
							</td>
						</tr>
						<?php foreach ($vKorespMonthList as $kKorespIdx => $vKorespInfo) : ?>
							<?php /*
								*	[ID] => 41235
								*	[MONTH] => 2015-02
								*	[K_ZAWARTOS] => Faktura za pomiar powykonawczy sieci telekomunikacyjnej
								*	[COST] => 1000.00
								*	[INCOME] => 0.00
								? [TRANSFER_OPPOSITE_ID_PROJECT] => int
								*/ ?>
							<tr>
								<td class="p2 r nr"><?php echo $vKorespInfo->ID; ?></td>
								<td class="p2" style="max-width:400px;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;" title="<?php echo $vKorespInfo->K_ZAWARTOS; ?>"><?php echo $vKorespInfo->K_ZAWARTOS; ?></td>
								<?php $vKorespCostOut = number_format($vKorespInfo->COST, 2); ?>
								<td class="cell-cost cell-cost-only_child"><?php echo $vKorespCostOut; ?></td>
								<td>
									<?php $dekretId = V::get('TRANSFER_OPPOSITE_ID_PROJECT', 0, $vKorespInfo) ?>
									<?php if ($dekretId > 0) : ?>
										Dekret z projektu nr <?php echo $dekretId; ?>
									<?php endif; ?>
								</td>
							</tr>
						<?php endforeach; ?>
					</tbody>
				<?php endforeach; ?>
			<?php endif; ?>
		<?php endforeach; ?>
	</table>
<script>
	jQuery(document).ready(function(){
		jQuery('.ttip').tooltip();
	});

	function selectProject(n) {
		var $n = jQuery(n);
		var $p = $n.parent().parent();
		if (n.checked) {
			$p.addClass('row-selected');
		} else {
			$p.removeClass('row-selected');
		}
		markSubProjects($p, $p.data('path'), n.checked);

		function markSubProjects($p, path, checked) {
			var $nextRow = $p.next('tr'),
					nextPath = $nextRow.data('path'),
					nextCheckbox = $nextRow.find('input[type="checkbox"]').get(0);
			if (0 !== nextPath.indexOf(path)) {
				return;
			}
			if (checked) {
				$nextRow.addClass('row-selected');
			} else {
				$nextRow.removeClass('row-selected');
			}
			nextCheckbox.checked = checked;
			markSubProjects($nextRow, path, checked);
		}
	}

	function showHideAll(n) {
		if (n.checked) {
			jQuery('#zestawienie-kosztow-projektow').find('tbody').addClass('showOnlySelected');
		} else {
			jQuery('#zestawienie-kosztow-projektow').find('tbody').removeClass('showOnlySelected');
		}
	}

</script>
<?php
	}

	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) {
		$this->_fetchCostsByYear($year);
		$this->_fetchPlanByYear($year);
		$this->_fetchProjectInfo();
		$this->_buildProjectTree();
		$this->_reacountCostsFromKoresp();
		return count($this->_projectInfo) > 1;// $this->_projectInfo[0] - Wszystkie projekty
	}

	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 _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`
				, 0 as `TRANSFER_OPPOSITE_ID_PROJECT` -- 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
				, '' as TRANSFER_OPPOSITE_PROJECT_PATH
				, k.`K_ZAWARTOS`
			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;
			$monthNum = intval(substr($r->MONTH, 5, 2));
			if ($r->TRANSFER_OPPOSITE_ID_PROJECT > 0) {
				$korespInfo->COST = $r->COST;
				$korespInfo->INCOME = $r->INCOME;
				$korespInfo->TRANSFER_OPPOSITE_ID_PROJECT = $r->TRANSFER_OPPOSITE_ID_PROJECT;
				$this->_costs[$r->TRANSFER_OPPOSITE_ID_PROJECT]->korespByMonth[$monthNum][] = $korespInfo;
			} else if ($r->ID_PROJECT) {
				$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;
	}

	private function _fetchProjectInfo() {
		$db = DB::getDB();
		$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`
			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[0]->path = '0';
		$this->_projectInfo[0]->M_DIST_DESC = "Wszystkie projekty";
	}

	private function _reacountCostsFromKoresp() {
		$projMonthHasCostSelfIds = 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;
			}
		}
	}

	private 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()));
			}
		}
	}

}