se/SE/superedit-BUDGET_ANALYTICS.php

<?php

function BUDGET_ANALYTICS() {

	$budgetAnalyticsRouter = new BudgetAnalyticsRouter();

	$args = array();
	$args['year'] = V::get('zest_year', '', $_REQUEST, 'int');
	$args['_print'] = V::get('_print', '', $_REQUEST, 'int');

	if (!$args['_print']) {
		$budgetAnalyticsRouter->menu($args['year']);
	}

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

	$costs = $budgetAnalyticsRouter->getCostsByYear($args['year']);
	if (empty($costs)) {
		?>
		<div class="alert alert-warning">
			Brak danych na wybrany rok.
		</div>
		<?
		return;
	}

	$projTree = $budgetAnalyticsRouter->buildProjectTree($costs);
	//$orderby = V::get('orderby', '', $_GET);
	//$view_type = V::get('view_type', '', $_GET);
	//$budgetAnalyticsRouter->cyfrowe($projekty, $grupyKosztow, $costs, $orderby, $view_type);
	$budgetAnalyticsRouter->printCostsForYear($projTree, $costs, $args['year']);
}

class BudgetAnalyticsRouter {

	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="zest_year" />
			<label for="zest_year">Zestawienie kosztów projektów na podstawie korespondencji:</label>
			<div class="input-group date" id="fldZestYear">
				<input type="text" name="zest_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; }

		.cost { padding:0 2px; min-width:30px; text-align:right; }
		.cost-only_child { color:#777; }
		.cost-only_self { color:red; }
		.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($projOrder, $costs, $year) {
		$months = array();
		for ($i = 0; $i < 12; $i++) {
			$months[] = $i + 1;
		}
		$this->css();
		//echo'<pre style="width:600px;border:1px solid red;max-height:300px;overflow:auto;">';print_r($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">miesiąc</span>
			</td>
			<?php foreach ($months as $month) { ?>
				<th class="c"><?php echo $month; ?></th>
			<?php } ?>
		</tr>
	</thead>
	<tbody>
	<?php $t = 1; ?>
	<?php foreach ($projOrder as $projPath => $projId) : ?>
		<?php $projectInfo = $costs[$projId]; ?>
		<tr class="row-<?php echo ($t = 1 - $t); ?>"
				data-projId="<?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 if ($vCost->COST_CHILD > 0) : ?>
						<?php  $title = "Koszt projektu {$vCost->COST_SELF} / koszt podprojektów {$vCost->COST_CHILD}"; ?>
						<?php if ($vCost->COST_SELF > 0) : ?>
			<td class="cost cost-self_and_child"><span class="ttip" title="<?php echo $title; ?>"><?php echo $vCost->COST_TOTAL; ?></span></td>
						<?php else : ?>
			<td class="cost cost-only_child"><span class="ttip" title="<?php echo $title; ?>"><?php echo $vCost->COST_TOTAL; ?></span></td>
						<?php endif; ?>
					<?php else : ?>
			<td class="cost cost-only_self"><?php echo $vCost->COST_TOTAL; ?></td>
					<?php endif; ?>
				<?php else : ?>
					<td style="min-width:30px">&nbsp;</td>
				<?php endif; ?>
			<?php endforeach; ?>
		</tr>
	<?php endforeach; ?>
	</tbody>
	</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($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 buildProjectTree($costs) {
		$projPaths = array();
		foreach ($costs as $idProject => $projectInfo) {
			$projPaths[$projectInfo->path] = $projectInfo->ID_PROJECT;
		}
		//echo'<pre style="width:600px;border:1px solid red;max-height:300px;overflow:auto;">projPaths: ';print_r($projPaths);echo'</pre>';
		uksort($projPaths, array($this, 'sortPathsCallback'));
		//echo'<pre style="width:600px;border:1px solid red;max-height:300px;overflow:auto;">projPaths sorted: ';print_r($projPaths);echo'</pre>';
		return $projPaths;
	}

	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 installView() {
		$sql = "
			CREATE VIEW `test_budget_project_analytics_view` AS
			select `IN7_DZIENNIK_KORESP`.`ID_PROJECT` AS `ID_PROJECT`
				, date_format(`IN7_DZIENNIK_KORESP`.`K_DATA_OTRZYMANEJ_KORESP`,'%Y-%m') AS `MONTH`
				, sum(`IN7_DZIENNIK_KORESP`.`COST_VALUE`) AS `COST`
				, sum(`IN7_DZIENNIK_KORESP`.`INCOME_VALUE`) AS `INCOME`
				, 'SELF' AS `TYPE`
			from `IN7_DZIENNIK_KORESP`
			where ((`IN7_DZIENNIK_KORESP`.`COST_VALUE` > 0) or (`IN7_DZIENNIK_KORESP`.`INCOME_VALUE` > 0))
			group by `IN7_DZIENNIK_KORESP`.`ID_PROJECT`
				, date_format(`IN7_DZIENNIK_KORESP`.`K_DATA_OTRZYMANEJ_KORESP`,'%Y-%m')
		union
			select `p`.`ID` AS `ID_PROJECT`
				, date_format(`k`.`K_DATA_OTRZYMANEJ_KORESP`,'%Y-%m') AS `MONTH`
				, sum(`k`.`COST_VALUE`) AS `COST`,sum(`k`.`INCOME_VALUE`) AS `INCOME`
				, 'CHILD' AS `TYPE`
			from (`IN7_MK_BAZA_DYSTRYBUCJI` `p`
				join `IN7_DZIENNIK_KORESP` `k` on((((`k`.`COST_VALUE` > 0) or (`k`.`INCOME_VALUE` > 0)) and (`k`.`path` like concat('%-',`p`.`ID`,'-%')))))
			group by `p`.`ID`, date_format(`k`.`K_DATA_OTRZYMANEJ_KORESP`, '%Y-%m')
		";
		$sql = "
			CREATE VIEW `test_budget_project_synthetics_view` AS
			select `t1`.`ID_PROJECT` AS `ID_PROJECT`
				,	`p`.`M_DIST_DESC` AS `M_DIST_DESC`
				,	`p`.`P_ID` AS `P_ID`
				,	`p`.`path` AS `path`
				,	`t1`.`MONTH` AS `MONTH`
				,	cast(sum(if((`t1`.`TYPE` = 'SELF'),`t1`.`COST`,'0')) as decimal(10,2)) AS `COST_SELF`
				, cast(sum(if((`t1`.`TYPE` = 'CHILD'),`t1`.`COST`,'0')) as decimal(10,2)) AS `COST_CHILD`
				, sum(`t1`.`COST`) AS `COST_TOTAL`
				, cast(sum(if((`t1`.`TYPE` = 'SELF'),`t1`.`INCOME`,'0')) as decimal(10,2)) AS `INCOME_SELF`
				, cast(sum(if((`t1`.`TYPE` = 'CHILD'),`t1`.`INCOME`,'0')) as decimal(10,2)) AS `INCOME_CHILD`
				, sum(`t1`.`INCOME`) AS `INCOME_TOTAL`
			from (`test_budget_project_analytics_view` `t1`
				join `IN7_MK_BAZA_DYSTRYBUCJI` `p` on((`t1`.`ID_PROJECT` = `p`.`ID`))
			)
			group by `t1`.`ID_PROJECT`, `t1`.`MONTH`
		";
	}
}