se/SE/superedit-BUDGET_ANALYTICS.php

<?php

function BUDGET_ANALYTICS() {

	$TURN_OFF = true;// OFF 2015-06-24
	if ($TURN_OFF) {
		$url = "{$_SERVER['SCRIPT_URI']}?_route=Budget";
		if (!headers_sent()) {
			header('HTTP/1.1 303 See Other');
			header('Location: '.$url);
		} else {
			echo'<script type="text/javascript">'."
window.location.href='{$url}';
			".'</script>';
			echo "\n".'<noscript>';
			echo "\n".'<meta http-equiv="refresh" content="0;url='.$url.'" />';
			echo "\n".'</noscript>';
			echo'<p>'.'<a href="'.$url.'">'."dalej".'</a>'.'</p>';
		}
	}

	$budgetAnalyticsRouter = new BudgetAnalyticsRouter();

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

	if ('test_fetchDataFromTables' == $args['_task']) {
		$budgetAnalyticsRouter->test_fetchDataFromTables();
		die('.EOF TEST test_fetchDataFromTables');
	}

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

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

	$costs = $budgetAnalyticsRouter->getCostsByYear($args['year']);
	if (empty($costs)) {
		?>
		<div class="alert alert-warning">
			Brak danych na wybrany rok.
		</div>
		<?php
		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();
		if(V::get('DBG','',$_GET)){echo'<pre style="width:600px;border:1px solid red;max-height:300px;overflow:auto;display:none">';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`
		";
	}

	public function test_fetchDataFromTables() {
		$this->projectCost = array();
		$this->projectPath = array();
		$this->projectDesc = array();
		$this->tableProjectCost = array();
		$db = DB::getDB();

		$sql = "select `ID`, `P_ID`, `M_DIST_DESC`
			from `IN7_MK_BAZA_DYSTRYBUCJI`
			order by `ID`
		";
		$res = $db->query($sql);
		while ($row = $db->fetch($res)) {
			$project_P_ID[$row->P_ID][] = $row->ID;
			$this->projectDesc[$row->ID] = $row->M_DIST_DESC;
		}

		function buildProjectTree($flat,$id,$tree) {
			if (isset($flat[$id])) {
				foreach ($flat[$id] as $child) {
					$tree[$id][$child] = @buildProjectTree($flat, $child, $tree[$id]);
				}
				return $tree[$id];
			}
		}

		$projectTree[0] = buildProjectTree($project_P_ID, 0, null);
		//print_r($projectTree);

		$this->test_fetchDataFromTables__buildProjectPath($projectTree, null);
		//print_r($this->projectPath);

		$sql = "
			select `ID_PROJECT`
				, `TRANSFER_OPPOSITE_ID_PROJECT`
				, date_format(`K_DATA_OTRZYMANEJ_KORESP`, '%Y-%m') as MONTH
				, sum(`COST_VALUE`) as COST
			from `IN7_DZIENNIK_KORESP`
			where `COST_VALUE`>0
				and `K_DATA_OTRZYMANEJ_KORESP` like '2015%'
			group by `ID_PROJECT`, `MONTH`
			order by `MONTH`, `ID_PROJECT`, `TRANSFER_OPPOSITE_ID_PROJECT`
		";
		$res = $db->query($sql);
		while ($row = $db->fetch($res)) {
			foreach ($this->projectPath[$row->ID_PROJECT] as $id) {
				if ($row->ID_PROJECT == $id) @$this->projectCost[$id][$row->MONTH]['SELF'] += $row->COST;
				else @$this->projectCost[$id][$row->MONTH]['CHILD'] += $row->COST;
			}
			if ($row->TRANSFER_OPPOSITE_ID_PROJECT) {
				foreach ($this->projectPath[$row->TRANSFER_OPPOSITE_ID_PROJECT] as $id) {
					if ($row->TRANSFER_OPPOSITE_ID_PROJECT == $id) @$this->projectCost[$id][$row->MONTH]['SELF']-=$row->COST;
					else @$this->projectCost[$id][$row->MONTH]['CHILD'] -= $row->COST;
				}
			}
		}

		//echo'<pre>$projectTree: ';print_r($projectTree);echo'</pre>';
		//echo'<pre>projectCost: ';print_r($this->projectCost);echo'</pre>';

		$this->test_fetchDataFromTables__genTableProjectCost($projectTree);

		echo'<pre>tableProjectCost: ';print_r($this->tableProjectCost);echo'</pre>';
	}

	function test_fetchDataFromTables__genTableProjectCost($tree) {
		//if (!empty($tree)) echo '<p>DBG: F.' . __FUNCTION__ . ": tree(" . count($tree) . ")" . '</p>';
		if (isset($tree)) {
			foreach ($tree as $id => $child) {
				//echo '<p>DBG: F.' . __FUNCTION__ . ": tree=>child({$id}) isset(" . (isset($this->projectCost[$id])) . ")" . '</p>';
				if (isset($this->projectCost[$id])) {
					$this->tableProjectCost[$id]['PATH'] = implode('-', $this->projectPath[$id]);
					$this->tableProjectCost[$id]['DESC'] = @$this->projectDesc[$id];
					$this->tableProjectCost[$id]['COST'] = $this->projectCost[$id];
				}
				$this->test_fetchDataFromTables__genTableProjectCost($child);
			}
		}
	}

	function test_fetchDataFromTables__buildProjectPath($tree, $ppath) {
		if (isset($tree)) {
			foreach ($tree as $id => $child) {
				$this->projectPath[$id] = $ppath;
				$this->projectPath[$id][] = $id;
				$this->test_fetchDataFromTables__buildProjectPath($child, $this->projectPath[$id]);
			}
		}
	}

	/**
	 * 2015-06-22
	 * EVENT `view__budget_project_to_realization_main_event`
	 *       Write into TABLE `projects_budget_year_month`
	 * VIEW  `in7_dziennik_koresp_budget_view`
	 *       Read from TABLE `IN7_DZIENNIK_KORESP`
	 * VIEW  `in7_dziennik_koresp_budget_main_view`
	 *       Read from TABLE `IN7_DZIENNIK_KORESP`
	 */
	function test_reinstallViewsAndEvents() {
		$sqls['RemoveView_korespMain'] = " drop view if exists `in7_dziennik_koresp_budget_main_view` ";
		$sqls['CreateView_korespMain'] = "
			CREATE VIEW `in7_dziennik_koresp_budget_main_view` AS
				select cast(substring_index(substring_index(k.`path`,'-',2),'-',-(1)) as signed) AS `ID`
				, substring_index(k.`path`,'-',2) AS `path`
				, cast(concat(',',group_concat(distinct k.`ID_PROJECT` order by k.`ID_PROJECT` ASC separator ','),',') as char charset utf8) AS `ID_PROJECT`
				, year(k.`K_DATA_OTRZYMANEJ_KORESP`) AS `rok`
				, sum(k.`COST_VALUE`) AS `sum_k`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '0') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_0`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '1') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_1`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '2') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_2`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '3') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_3`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '4') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_4`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '5') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_5`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '6') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_6`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '7') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_7`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '8') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_8`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '9') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_9`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '10') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_10`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '11') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_11`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '12') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_12`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '13') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_13`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '0') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_0`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '1') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_1`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '2') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_2`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '3') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_3`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '4') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_4`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '5') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_5`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '6') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_6`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '7') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_7`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '8') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_8`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '9') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_9`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '10') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_10`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '11') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_11`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '12') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_12`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '13') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_13`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '0') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_0`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '1') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_1`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '2') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_2`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '3') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_3`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '4') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_4`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '5') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_5`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '6') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_6`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '7') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_7`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '8') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_8`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '9') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_9`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '10') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_10`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '11') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_11`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '12') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_12`
				, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '13') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_13`
			from `IN7_DZIENNIK_KORESP` k
			where (k.`COST_VALUE` <> '')
			group by substring_index(k.`path`,'-',2)
		";
		$sqls['RemoveView_korespMain'] = " drop view if exists `in7_dziennik_koresp_budget_view` ";
		$sqls['CreateView_korespMain'] = "
			CREATE VIEW `in7_dziennik_koresp_budget_view` AS
				select k.`ID` AS `ID`
					, k.`path` AS `path`
					, k.`ID_PROJECT` AS `ID_PROJECT`
					, year(k.`K_DATA_OTRZYMANEJ_KORESP`) AS `rok`
					, month(k.`K_DATA_OTRZYMANEJ_KORESP`) AS `miesiac`
					, date_format(k.`K_DATA_OTRZYMANEJ_KORESP`,'%Y-%m') AS `data`
					, sum(k.`COST_VALUE`) AS `sum_k`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '0') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_0`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '1') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_1`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '2') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_2`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '3') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_3`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '4') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_4`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '5') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_5`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '6') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_6`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '7') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_7`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '8') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_8`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '9') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_9`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '10') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_10`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '11') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_11`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '12') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_12`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '13') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = year(now()))),k.`COST_VALUE`,0)) AS `sum_13`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '0') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_0`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '1') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_1`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '2') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_2`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '3') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_3`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '4') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_4`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '5') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_5`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '6') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_6`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '7') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_7`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '8') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_8`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '9') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_9`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '10') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_10`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '11') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_11`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '12') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_12`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '13') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) - 1))),k.`COST_VALUE`,0)) AS `sum_ly_13`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '0') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_0`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '1') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_1`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '2') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_2`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '3') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_3`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '4') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_4`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '5') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_5`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '6') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_6`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '7') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_7`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '8') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_8`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '9') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_9`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '10') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_10`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '11') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_11`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '12') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_12`
					, sum(if(((month(k.`K_DATA_OTRZYMANEJ_KORESP`) = '13') and (year(k.`K_DATA_OTRZYMANEJ_KORESP`) = (year(now()) + 1))),k.`COST_VALUE`,0)) AS `sum_ny_13`
				from `IN7_DZIENNIK_KORESP` k
				where (k.`COST_VALUE`!='')
				group by k.`ID_PROJECT`
		";
		$sqlList['RemoveEvent_everyDay'] = "DROP EVENT IF EXISTS `view__budget_project_to_realization_main_event`";
		$sqlList['CreateEvent_everyDay'] = "
			CREATE EVENT `view__budget_project_to_realization_main_event`
				ON SCHEDULE EVERY 1 DAY STARTS '2015-05-12 00:04:30'
				ON COMPLETION NOT PRESERVE ENABLE
			DO BEGIN
				INSERT IGNORE INTO `projects_budget_year_month`(`ID_PROJECT`, `M_DIST_DESC`, `YEAR` ,`A_RECORD_CREATE_AUTHOR`, `A_RECORD_CREATE_DATE`)
					SELECT t1.`ID`
						, t2.`M_DIST_DESC`
						, t1.`rok`
						, 'import z widoku budzetu glownych spraw (view__budget_project_to_realization_main_event)'
						, NOW()
					FROM `in7_dziennik_koresp_budget_main_view` AS t1
						LEFT JOIN IN7_MK_BAZA_DYSTRYBUCJI AS t2 ON(t1.ID=t2.ID);
			END
		";
	}

}