'." window.location.href='{$url}'; ".''; echo "\n".''; echo'

'.''."dalej".''.'

'; } } $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'])) { ?>

Nie wybrano roku.

getCostsByYear($args['year']); if (empty($costs)) { ?>

Brak danych na wybrany rok.

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"); ?>

css(); if(V::get('DBG','',$_GET)){echo'

';print_r($costs);echo'

';} ?>

Zestawienie kosztów projektów na rok

$projId) : ?> costsByMonth)) : ?> costsByMonth); ?> COST_CHILD > 0) : ?> COST_SELF} / koszt podprojektów {$vCost->COST_CHILD}"; ?> COST_SELF > 0) : ?>

pokaż tylko zaznaczone miesiąc
	path; ?>	M_DIST_DESC; ?>	COST_TOTAL; ?>	COST_TOTAL; ?>	COST_TOTAL; ?>

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'

projPaths: ';print_r($projPaths);echo'

'; uksort($projPaths, array($this, 'sortPathsCallback')); //echo'

projPaths sorted: ';print_r($projPaths);echo'

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

$projectTree: ';print_r($projectTree);echo'

'; //echo'

projectCost: ';print_r($this->projectCost);echo'

'; $this->test_fetchDataFromTables__genTableProjectCost($projectTree); echo'

tableProjectCost: ';print_r($this->tableProjectCost);echo'

'; } function test_fetchDataFromTables__genTableProjectCost($tree) { //if (!empty($tree)) echo '

DBG: F.' . __FUNCTION__ . ": tree(" . count($tree) . ")" . '

'; if (isset($tree)) { foreach ($tree as $id => $child) { //echo '

DBG: F.' . __FUNCTION__ . ": tree=>child({$id}) isset(" . (isset($this->projectCost[$id])) . ")" . '

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