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

<?php

Lib::loadClass('RouteBase');

class Route_Budget extends RouteBase {

	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;
		}

		$costs = $this->getCostsByYear($args['year']);
		if (empty($costs)) {
			?>
			<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 #7EC5FF; }
		.zestawienie-kosztow-tbl td { border:1px solid #7EC5FF; }
		.zestawienie-kosztow-tbl .p2 { padding:0 2px; }
		.zestawienie-kosztow-tbl .nr { color:#7A7A7A; }
		.zestawienie-kosztow-tbl thead th { border:1px solid #7EC5FF; }
		.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_child { color:#777; }
		.cell-cost-only_self { color:red; }
		.cell-cost-self_and_child { color:orange; }

		/* 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) {
		$this->_costs = $this->_costs;
		$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>
	<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-left">
					<input type="checkbox" onclick="return showHideAll(this);"/> pokaż tylko zaznaczone
				</span>
				<span class="pull-right"><b>miesiąc</b></span>
			</td>
			<?php foreach ($months as $month) { ?>
				<th class="c"><?php echo sprintf("%02d", $month); ?></th>
			<?php } ?>
		</tr>
	</thead>
	<tbody>
	<?php $t = 1; ?>
	<?php foreach ($this->_projectPathsOrder as $projPath => $projId) : ?>
		<?php $projectInfo = $this->_costs[$projId]; ?>
		<?php if (empty($projectInfo)) {
			//echo'<tr><td colspan="10"><pre>ERROR:EMPTY: Path('.$projPath.') id('.$projId.')</pre></td></tr>';
			continue;
		} ?>
		<tr class="row-<?php echo ($t = 1 - $t); ?>"
				data-proj_id="<?php echo $projectInfo->ID_PROJECT; ?>"
				data-path="<?php echo $projectInfo->path; ?>">
			<td class="p2 r nr">
				<input type="checkbox" name="selectedProject" onclick="return selectProject(this);" value="<?php echo $projectInfo->ID_PROJECT; ?>" />
			</td>
			<td class="p2 l nr"><?php echo $projectInfo->path; ?></td>
			<td class="p2" style="max-width:300px;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;" title="<?php echo $projectInfo->M_DIST_DESC; ?>"><?php echo $projectInfo->M_DIST_DESC; ?></td>
			<?php foreach ($months as $month) : ?>
				<?php if (array_key_exists($month, $projectInfo->costsByMonth)) : ?>
					<?php $vCost = V::get($month, '', $projectInfo->costsByMonth); ?>
					<?php $vCostChildOut = number_format($vCost->COST_CHILD, 2); ?>
					<?php $vCostSelfOut = number_format($vCost->COST_SELF, 2); ?>
					<?php $vCostTotalOut = number_format($vCost->COST_TOTAL, 2); ?>
					<?php if ($vCost->COST_CHILD > 0) : ?>
						<?php  $title = "Koszt projektu {$vCostSelfOut} / koszt podprojektów $vCostChildOut"; ?>
						<?php if ($vCost->COST_SELF > 0) : ?>
			<td class="cell-cost cell-cost-self_and_child"
					data-month_num="<?php echo $month; ?>"
					data-proj_path="<?php echo $projectInfo->path; ?>"
					data-proj_id="<?php echo $projectInfo->ID_PROJECT; ?>">
				<span class="ttip" title="<?php echo $title; ?>"><?php echo $vCostTotalOut; ?></span>
			</td>
						<?php else : ?>
			<td class="cell-cost cell-cost-only_child"
					data-month_num="<?php echo $month; ?>"
					data-proj_path="<?php echo $projectInfo->path; ?>"
					data-proj_id="<?php echo $projectInfo->ID_PROJECT; ?>">
				<span class="ttip" title="<?php echo $title; ?>"><?php echo $vCostTotalOut; ?></span>
			</td>
						<?php endif; ?>
					<?php else : ?>
			<td class="cell-cost cell-cost-only_self"
					data-month_num="<?php echo $month; ?>"
					data-proj_path="<?php echo $projectInfo->path; ?>"
					data-proj_id="<?php echo $projectInfo->ID_PROJECT; ?>">
				<?php echo $vCostTotalOut; ?>
			</td>
					<?php endif; ?>
				<?php else : ?>
					<td style="min-width:30px">&nbsp;</td>
				<?php endif; ?>
			<?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);
			console.log('node', $n, 'data', $n.data());
		})
})
	</script>
	<table id="proj-koresp-info" style="display:none">
		<?php foreach ($this->_costs as $projId => $projectInfo) : ?>
			<?php if (!empty($projectInfo->korespByMonth)) : ?>
				<?php foreach ($projectInfo->korespByMonth as $kMonth => $vKorespMonthList) : ?>
					<tbody id="row-proj-<?php echo $projectInfo->ID_PROJECT; ?>-koresp-by-month-<?php echo $kMonth; ?>">
						<tr>
							<td colspan="10">
								Projekt nr <?php echo $projectInfo->ID_PROJECT; ?>
								- miesiąc <?php echo $year; ?>-<?php echo sprintf("%02d", $kMonth); ?>
							</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
							*/ ?>
							<tr>
								<td class="p2 l nr"><?php echo $vKorespInfo->ID; ?></td>
								<td class="p2" style="max-width:400px;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;" title="<?php echo $projectInfo->M_DIST_DESC; ?>"><?php echo $vKorespInfo->K_ZAWARTOS; ?></td>
								<?php $vKorespCostOut = number_format($vKorespInfo->COST, 2); ?>
								<td class="cell-cost cell-cost-only_self"><?php echo $vKorespCostOut; ?></td>
							</tr>
						<?php endforeach; ?>
					</tbody>
				<?php endforeach; ?>
			<?php endif; ?>
		<?php endforeach; ?>
	</table>
</div>
<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 getCostsByYear__OLD($year) {
		$db = DB::getDB();
		$costs = array();
		$sql = "
			select
				t.`ID_PROJECT`
				, t.`M_DIST_DESC`
				, t.`P_ID`
				, t.`path`
				, t.`MONTH`
				, t.`COST_SELF`
				, t.`COST_CHILD`
				, t.`COST_TOTAL`
				, t.`INCOME_SELF`
				, t.`INCOME_CHILD`
				, t.`INCOME_TOTAL`
			from `test_budget_project_synthetics_view` as t
			where 1=1
				and t.`MONTH` like '{$year}-%'
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			if (!array_key_exists($r->ID_PROJECT, $costs)) {
				$projectInfo = new stdClass();
				$projectInfo->ID_PROJECT = $r->ID_PROJECT;
				$projectInfo->M_DIST_DESC = $r->M_DIST_DESC;
				$projectInfo->path = $r->path;
				$projectInfo->costsByMonth = array();
				$costs[$r->ID_PROJECT] = $projectInfo;
			}
			$cost = new stdClass();
			$cost->MONTH = $r->MONTH;
			$cost->COST_SELF = $r->COST_SELF;
			$cost->COST_CHILD = $r->COST_CHILD;
			$cost->COST_TOTAL = $r->COST_TOTAL;
			$cost->INCOME_SELF = $r->INCOME_SELF;
			$cost->INCOME_CHILD = $r->INCOME_CHILD;
			$cost->INCOME_TOTAL = $r->INCOME_TOTAL;
			$monthNum = intval(substr($r->MONTH, 5, 2));
			$costs[$r->ID_PROJECT]->costsByMonth[$monthNum] = $cost;
		}
		return $costs;
	}

	public function getCostsByYear($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`
			from `IN7_DZIENNIK_KORESP` k
			where ((k.`COST_VALUE` > 0) or (k.`INCOME_VALUE` > 0))
				and k.`K_DATA_OTRZYMANEJ_KORESP` like '{$year}-%'
		";
		//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->M_DIST_DESC = 'Wszystkie projekty';
				$projectZeroInfo->path = '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->M_DIST_DESC = '';
					$projectInfo->path = '';
					$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->INCOME;
				$korespInfo->INCOME = $r->COST;
				$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;
			}
		}
		$this->_fetchProjectInfo();
		$this->_buildProjectTree();
		$this->_reacountCostsFromKoresp();
		return $this->_costs;
	}

	private function _fetchProjectInfo() {
		$db = DB::getDB();
		$projectIds = array_keys($this->_costs);
		$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->_costs[$r->ID]->path = $r->path;
			$this->_costs[$r->ID]->M_DIST_DESC = $r->M_DIST_DESC;
		}
	}

	private function _reacountCostsFromKoresp() {
		$projMonthHasCostSelfIds = array();
		foreach ($this->_costs as $kProjId => $vProjInfo) {
			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] = $vProjInfo->path;
			}
		}
		//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->_costs as $idProject => $projectInfo) {
			$this->_projectPathsOrder[$projectInfo->path] = $projectInfo->ID_PROJECT;
		}
		//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;
	}

}