public function getSummary($id = 0) { if (!$id) { return false; } App::import('model', 'DB'); $DB = new DB(); $results = array('survey' => $DB->selectAssoc("\n SELECT\n surveys.*\n FROM surveys\n WHERE\n surveys.id = {$id}"), 'questions' => $DB->selectAssocs("\n SELECT\n surveys_questions.*\n FROM surveys_questions\n WHERE\n surveys_questions.survey_id = {$id}")); foreach ($results['questions'] as $i => $question) { $results['questions'][$i]['answers'] = $DB->selectAssocs("\n SELECT\n surveys_answers.answer, COUNT(surveys_answers.id) as `count`\n FROM surveys_answers\n WHERE\n surveys_answers.question_id = " . $question['id'] . "\n GROUP BY surveys_answers.answer\n "); } return $results; }
public function all($args) { App::import('model', 'DB'); $DB = new DB(); $list_id = $DB->selectAssocs("SELECT DISTINCT kandydat_id FROM krs_kandydaci WHERE typ='" . $args['type'] . "' AND correct='" . $args['stan'] . "' ORDER BY id LIMIT " . ($args['page'] - 1) * 50 . "," . $args['page'] * 50); $ids = '('; foreach ($list_id as $single_id) { $ids .= $single_id['kandydat_id'] . ','; } $ids = trim($ids, ","); $ids .= ')'; if ($args['type'] == 'sejm') { $list_kand = $DB->selectAssocs("SELECT id, imiona, nazwisko, zawod,miejsce_zamieszkania, data_urodzenia FROM PKW_parlament_2015_kandydaci_sejm WHERE id IN {$ids} ORDER BY id"); } else { $list_kand = $DB->selectAssocs("SELECT * FROM PKW_parlament_2015_kandydaci_senat WHERE id IN {$ids} ORDER BY id"); } $ret = array(); foreach ($list_kand as $kandydat) { $kandydat['krs'] = $DB->selectAssocs("SELECT krs_kandydaci.id as kandydowanie_id, krs_osoby.imiona,krs_osoby.nazwisko, krs_osoby.data_urodzenia, krs_osoby.str, krs_osoby.id FROM krs_osoby JOIN krs_kandydaci ON krs_osoby.id=krs_kandydaci.krs_id WHERE krs_kandydaci.correct='" . $args['stan'] . "' AND krs_kandydaci.kandydat_id='" . $kandydat['id'] . "'"); $ret[] = $kandydat; } return $ret; }
public function grouped() { App::import('model', 'DB'); $DB = new DB(); $temp = $DB->selectAssocs("\n \tSELECT `pisma_szablony`.`id`, `pisma_szablony`.`nazwa`, `pisma_szablony`.`opis`, `pisma_kategorie`.`id` as `kategoria_id`, `pisma_kategorie`.`nazwa` as `kategoria_nazwa`\n\t\t\tFROM `pisma_szablony` \n\t\t\tJOIN `pisma_kategorie`\n\t\t\tON `pisma_szablony`.`pisma_kategorie_id` = `pisma_kategorie`.`id` \n\t\t\tWHERE pisma_szablony.akcept='1' AND `pisma_szablony`.`pisma_kategorie_id`='16' \n\t\t\tORDER BY pisma_kategorie.ord ASC, pisma_szablony.ord ASC\n\t\t"); $data = array(); foreach ($temp as $t) { $data[$t['kategoria_id']]['id'] = $t['kategoria_id']; $data[$t['kategoria_id']]['nazwa'] = $t['kategoria_nazwa']; $data[$t['kategoria_id']]['templates'][] = array('id' => $t['id'], 'nazwa' => $t['nazwa'], 'opis' => $t['opis']); } unset($temp); $data = array_values($data); $this->setSerialized('data', $data); }
public function getCountryDetails($countryCode) { App::import('model', 'DB'); $DB = new DB(); $countryCode = $DB->DB->real_escape_string($countryCode); $sql = <<<SQL SELECT l.iso2cc AS country_code, e.id AS wydarzenie_id, e.delegacja, kraj, miasto, e.wniosek_nr, e.liczba_dni, e.date_start AS od, e.date_stop AS do, w.id, w.koszt_transport, w.koszt_dieta, w.koszt_hotel, w.koszt_dojazd, w.koszt_ubezpieczenie, w.koszt_fundusz, w.koszt_kurs, w.koszt_zaliczki, w.koszt AS koszt_suma, p.nazwa AS posel, k.nazwa AS klub, k.glosowania_skrot AS klub_skrot FROM poslowie_wyjazdy w INNER JOIN poslowie_wyjazdy_wydarzenia e ON (w.wydarzenie_id = e.id) INNER JOIN poslowie_wyjazdy_lokalizacje l ON (l.lokalizacja = e.lokalizacja) INNER JOIN s_poslowie_kadencje p ON (w.posel_id = p.id) LEFT OUTER JOIN s_kluby k ON (w.klub_id = k.id) WHERE l.iso2cc = '{$countryCode}' AND e.deleted = '0' AND w.deleted = '0' ORDER BY e.date_start, e.id, w.id SQL; $rows = $DB->selectAssocs($sql); if (!$rows) { throw new NotFoundException(); } $tree = array(); $wydarzenie = null; $last_wydarzenie = null; for ($i = 0; $i < count($rows); $i++) { $row = $rows[$i]; if ($row['wydarzenie_id'] != $last_wydarzenie) { //if ($wydarzenie != null) $w = array_intersect_key($row, array_flip(array('wydarzenie_id', 'id', 'delegacja', 'country_code', 'kraj', 'miasto', 'wniosek_nr', 'liczba_dni', 'od', 'do'))); $w['poslowie'] = array(); array_push($tree, $w); $wydarzenie =& $tree[count($tree) - 1]; } array_push($wydarzenie['poslowie'], array_intersect_key($row, array_flip(array('posel', 'klub', 'klub_skrot', 'koszt_suma', 'koszt_transport', 'koszt_dieta', 'koszt_hotel', 'koszt_dojazd', 'koszt_ubezpieczenie', 'koszt_fundusz', 'koszt_kurs', 'koszt_zaliczki')))); // if ($i == count($rows) - 1 && $row['wydarzenie_id'] != $last_wydarzenie) { // array_push($tree, $wydarzenie); // push last // } $last_wydarzenie = $row['wydarzenie_id']; } return $tree; }
public function getTopSymbolsData($year) { App::import('model', 'DB'); $db = new DB(); $year = (int) $year; $limit = 5; $symbols = array('import' => array(), 'export' => array()); $symbols['import'] = $db->selectAssocs("\n SELECT\n hz_cn_symbole.nazwa,\n hz_cn_symbole.id,\n SUM(hz_data.wartosc_pln) AS wartosc_pln\n FROM hz_data\n JOIN hz_cn_symbole ON hz_cn_symbole.id = hz_data.symbol_id\n WHERE\n hz_data.rocznik = {$year} AND\n hz_cn_symbole.parent_id = 0 AND\n hz_data.typ = 'import'\n GROUP BY hz_data.symbol_id\n ORDER BY wartosc_pln DESC\n LIMIT {$limit}\n "); $symbols['export'] = $db->selectAssocs("\n SELECT\n hz_cn_symbole.nazwa,\n hz_cn_symbole.id,\n SUM(hz_data.wartosc_pln) AS wartosc_pln\n FROM hz_data\n JOIN hz_cn_symbole ON hz_cn_symbole.id = hz_data.symbol_id\n WHERE\n hz_data.rocznik = {$year} AND\n hz_cn_symbole.parent_id = 0 AND\n hz_data.typ = 'eksport'\n GROUP BY hz_data.symbol_id\n ORDER BY wartosc_pln DESC\n LIMIT {$limit}\n "); return $symbols; }
public function delete($id, $params) { App::import('model', 'DB'); $DB = new DB(); if (is_string($id)) { $id = array($id); } foreach ($id as &$i) { $i = addslashes($i); } $items = $DB->selectAssocs("SELECT `id`, `alphaid`, `saved` FROM `pisma_documents` WHERE `alphaid`='" . implode("' OR `alphaid`='", $id) . "' AND `from_user_type`='" . addslashes($params['from_user_type']) . "' AND `from_user_id`='" . addslashes($params['from_user_id']) . "'"); if ($items) { foreach ($items as $item) { $DB->q("UPDATE `pisma_documents` SET `deleted`='1', `deleted_at`=NOW() WHERE `id`='" . $item['id'] . "' LIMIT 1"); $ES = ConnectionManager::getDataSource('MPSearch'); $deleteParams = array(); $deleteParams['index'] = 'mojepanstwo_v1'; $deleteParams['type'] = 'letters'; $deleteParams['id'] = $item['alphaid']; $deleteParams['refresh'] = true; $deleteParams['ignore'] = array(404); $ES->API->delete($deleteParams); } return 200; } else { return 404; } }
public function getCompareData($p1, $p2) { App::import('model', 'DB'); $DB = new DB(); $wyd_czesci = $DB->selectAssocs("SELECT pl_budzety_wydatki.rocznik, pl_budzety_wydatki.czesc_str, pl_budzety_wydatki.tresc, SUM( pl_budzety_wydatki.plan ) AS plan\n FROM pl_budzety_wydatki\n WHERE pl_budzety_wydatki.rocznik\n IN ( {$p1}, {$p2} )\n AND pl_budzety_wydatki.type = 'czesc'\n AND pl_budzety_wydatki.czesc_id NOT\n IN ( 15, 90, 107 )\n GROUP BY pl_budzety_wydatki.czesc_str, pl_budzety_wydatki.rocznik"); $wyd_czesci2 = array(); foreach ($wyd_czesci as $row) { $czesc_str = str_pad($row['czesc_str'], 3, '0', STR_PAD_LEFT); if (!isset($wyd_czesci2[trim($czesc_str)])) { $wyd_czesci2[trim($czesc_str)] = array(); $wyd_czesci2[trim($czesc_str)]['tresc'] = $row['tresc']; } if ($row['rocznik'] == $p1) { $wyd_czesci2[trim($czesc_str)]['p1'] = $row['plan']; } else { $wyd_czesci2[trim($czesc_str)]['p2'] = $row['plan']; } } $wyd_czesci = array('wzrost' => array(), 'spadek' => array(), 'bd' => array()); foreach ($wyd_czesci2 as $row) { $zmiana = false; $zmiana2 = false; if (isset($row['p1']) && isset($row['p2']) && $row['p1'] !== 0) { $zmiana = $row['p2'] * 100 / $row['p1'] - 100; $zmiana2 = $row['p2'] / $row['p1']; } if ($zmiana > 0) { $wyd_czesci['wzrost'][] = array('tresc' => $row['tresc'], 'p1' => $row['p1'], 'p2' => $row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } elseif ($zmiana === false) { $wyd_czesci['bd'][] = array('tresc' => $row['tresc'], 'p1' => @$row['p1'], 'p2' => @$row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } else { $wyd_czesci['spadek'][] = array('tresc' => $row['tresc'], 'p1' => $row['p1'], 'p2' => $row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } } $wyd_dzial = $DB->selectAssocs("SELECT pl_budzety_wydatki.rocznik, pl_budzety_wydatki.dzial_str, pl_budzety_wydatki.tresc, SUM( pl_budzety_wydatki.plan ) AS plan\n FROM pl_budzety_wydatki\n WHERE pl_budzety_wydatki.rocznik\n IN ( {$p1}, {$p2} )\n AND pl_budzety_wydatki.type = 'dzial'\n AND pl_budzety_wydatki.czesc_id NOT\n IN ( 15, 90, 107 )\n GROUP BY pl_budzety_wydatki.dzial_str, pl_budzety_wydatki.rocznik"); $wyd_dzial2 = array(); foreach ($wyd_dzial as $row) { $dzial_str = str_pad($row['dzial_str'], 3, '0', STR_PAD_LEFT); if (!isset($wyd_dzial2[trim($dzial_str)])) { $wyd_dzial2[trim($dzial_str)] = array(); $wyd_dzial2[trim($dzial_str)]['tresc'] = $row['tresc']; } if ($row['rocznik'] == $p1) { $wyd_dzial2[trim($dzial_str)]['p1'] = $row['plan']; } else { $wyd_dzial2[trim($dzial_str)]['p2'] = $row['plan']; } } $wyd_dzial = array('wzrost' => array(), 'spadek' => array(), 'bd' => array()); foreach ($wyd_dzial2 as $row) { $zmiana = false; $zmiana2 = false; if (isset($row['p1']) && isset($row['p2'])) { $zmiana = $row['p2'] * 100 / $row['p1'] - 100; $zmiana2 = $row['p2'] / $row['p1']; } if ($zmiana > 0) { $wyd_dzial['wzrost'][] = array('tresc' => $row['tresc'], 'p1' => $row['p1'], 'p2' => $row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } elseif ($zmiana === false) { $wyd_dzial['bd'][] = array('tresc' => $row['tresc'], 'p1' => @$row['p1'], 'p2' => @$row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } else { $wyd_dzial['spadek'][] = array('tresc' => $row['tresc'], 'p1' => $row['p1'], 'p2' => $row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } } $wyd_rozdzial = $DB->selectAssocs("SELECT pl_budzety_wydatki.rocznik, pl_budzety_wydatki.rozdzial_str, pl_budzety_wydatki.tresc, SUM( pl_budzety_wydatki.plan ) AS plan FROM pl_budzety_wydatki\nJOIN pl_budzety_wydatki_rozdzialy\nON pl_budzety_wydatki.rozdzial_str = pl_budzety_wydatki_rozdzialy.src\nWHERE pl_budzety_wydatki.rocznik\nIN ( {$p1}, {$p2} )\nAND pl_budzety_wydatki.type = 'rozdzial'\nAND pl_budzety_wydatki.czesc_id\nNOT IN ( 15, 90, 107 )\nGROUP BY pl_budzety_wydatki.rozdzial_str, pl_budzety_wydatki.rocznik"); $wyd_rozdzial2 = array(); foreach ($wyd_rozdzial as $row) { $rozdzial_str = str_pad($row['rozdzial_str'], 5, '0', STR_PAD_LEFT); if (!isset($wyd_rozdzial2[trim($rozdzial_str)])) { $wyd_rozdzial2[trim($rozdzial_str)] = array(); $wyd_rozdzial2[trim($rozdzial_str)]['tresc'] = $row['tresc']; } if ($row['rocznik'] == $p1) { $wyd_rozdzial2[trim($rozdzial_str)]['p1'] = $row['plan']; } else { $wyd_rozdzial2[trim($rozdzial_str)]['p2'] = $row['plan']; } } $wyd_rozdzial = array('wzrost' => array(), 'spadek' => array(), 'bd' => array()); foreach ($wyd_rozdzial2 as $row) { $zmiana = false; $zmiana2 = false; if (isset($row['p1']) && isset($row['p2'])) { $zmiana = $row['p2'] * 100 / $row['p1'] - 100; $zmiana2 = $row['p2'] / $row['p1']; } if ($zmiana > 0) { $wyd_rozdzial['wzrost'][] = array('tresc' => $row['tresc'], 'p1' => $row['p1'], 'p2' => $row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } elseif ($zmiana === false) { $wyd_rozdzial['bd'][] = array('tresc' => $row['tresc'], 'p1' => @$row['p1'], 'p2' => @$row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } else { $wyd_rozdzial['spadek'][] = array('tresc' => $row['tresc'], 'p1' => $row['p1'], 'p2' => $row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } } $doch_dzial = $DB->selectAssocs("SELECT rocznik, dzial_str, tresc, SUM( plan ) AS plan\n FROM pl_budzety_wydatki\n WHERE rocznik\n IN ( {$p1},{$p2} )\n AND type = 'dzial'\n AND LENGTH(czesc_str) < 3\n GROUP BY dzial_str, rocznik"); $doch_dzial2 = array(); foreach ($doch_dzial as $row) { $dzial_str = str_pad($row['dzial_str'], 3, '0', STR_PAD_LEFT); if (!isset($doch_dzial2[trim($dzial_str)])) { $doch_dzial2[trim($dzial_str)] = array(); $doch_dzial2[trim($dzial_str)]['tresc'] = $row['tresc']; } if ($row['rocznik'] == $p1) { $doch_dzial2[trim($dzial_str)]['p1'] = $row['plan']; } else { $doch_dzial2[trim($dzial_str)]['p2'] = $row['plan']; } } $doch_dzial = array('wzrost' => array(), 'spadek' => array(), 'bd' => array()); foreach ($doch_dzial2 as $row) { $zmiana = false; $zmiana2 = false; if (isset($row['p1']) && isset($row['p2'])) { $zmiana = $row['p2'] * 100 / $row['p1'] - 100; $zmiana2 = $row['p2'] / $row['p1']; } if ($zmiana > 0) { $doch_dzial['wzrost'][] = array('tresc' => $row['tresc'], 'p1' => $row['p1'], 'p2' => $row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } elseif ($zmiana === false) { $doch_dzial['bd'][] = array('tresc' => $row['tresc'], 'p1' => @$row['p1'], 'p2' => @$row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } else { $doch_dzial['spadek'][] = array('tresc' => $row['tresc'], 'p1' => $row['p1'], 'p2' => $row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } } $doch_czesci = $DB->selectAssocs("SELECT rocznik, czesc_str, tresc, SUM( plan ) AS plan\n FROM pl_budzety_wydatki\n WHERE rocznik\n IN ( {$p1},{$p2} )\n AND type = 'czesc'\n AND LENGTH(czesc_str) < 3\n GROUP BY czesc_str, rocznik"); $doch_czesci2 = array(); foreach ($doch_czesci as $row) { $czesc_str = str_pad($row['czesc_str'], 3, '0', STR_PAD_LEFT); if (!isset($doch_czesci2[trim($czesc_str)])) { $doch_czesci2[trim($czesc_str)] = array(); $doch_czesci2[trim($czesc_str)]['tresc'] = $row['tresc']; } if ($row['rocznik'] == $p1) { $doch_czesci2[trim($czesc_str)]['p1'] = $row['plan']; } else { $doch_czesci2[trim($czesc_str)]['p2'] = $row['plan']; } } $doch_czesci = array('wzrost' => array(), 'spadek' => array(), 'bd' => array()); foreach ($doch_czesci2 as $row) { $zmiana = false; $zmiana2 = false; if (isset($row['p1']) && isset($row['p2'])) { $zmiana = $row['p2'] * 100 / $row['p1'] - 100; $zmiana2 = $row['p2'] / $row['p1']; } if ($zmiana > 0) { $doch_czesci['wzrost'][] = array('tresc' => $row['tresc'], 'p1' => $row['p1'], 'p2' => $row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } elseif ($zmiana === false) { $doch_czesci['bd'][] = array('tresc' => $row['tresc'], 'p1' => @$row['p1'], 'p2' => @$row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } else { $doch_czesci['spadek'][] = array('tresc' => $row['tresc'], 'p1' => $row['p1'], 'p2' => $row['p2'], 'zmiana' => $zmiana, 'zmiana2' => $zmiana2); } } usort($wyd_rozdzial['spadek'], function ($a, $b) { return $a['zmiana'] - $b['zmiana']; }); usort($wyd_rozdzial['wzrost'], function ($a, $b) { return $b['zmiana'] - $a['zmiana']; }); usort($wyd_dzial['spadek'], function ($a, $b) { return $a['zmiana'] - $b['zmiana']; }); usort($wyd_dzial['wzrost'], function ($a, $b) { return $b['zmiana'] - $a['zmiana']; }); usort($wyd_czesci['spadek'], function ($a, $b) { return $a['zmiana'] - $b['zmiana']; }); usort($wyd_czesci['wzrost'], function ($a, $b) { return $b['zmiana'] - $a['zmiana']; }); usort($doch_dzial['spadek'], function ($a, $b) { return $a['zmiana'] - $b['zmiana']; }); usort($doch_dzial['wzrost'], function ($a, $b) { return $b['zmiana'] - $a['zmiana']; }); usort($doch_czesci['spadek'], function ($a, $b) { return $a['zmiana'] - $b['zmiana']; }); usort($doch_czesci['wzrost'], function ($a, $b) { return $b['zmiana'] - $a['zmiana']; }); return array('wydatki' => array('czesci' => $wyd_czesci, 'dzialy' => $wyd_dzial, 'rozdzialy' => $wyd_rozdzial), 'dochody' => array('czesci' => $doch_czesci, 'dzialy' => $doch_dzial)); }
public function getCategories() { App::import('model', 'DB'); $DB = new DB(); $cache = new MPCache(); $cacheClient = $cache->getDataSource()->getRedisClient(); $cacheKey = 'bdl/getCategories'; if ($cacheClient->exists($cacheKey)) { $categories = json_decode($cacheClient->get($cacheKey)); } else { $categories = $DB->selectAssocs("\n SELECT id, w_tytul, tytul FROM BDL_kategorie WHERE deleted = '0' AND okres = 'R'\n "); foreach ($categories as $i => $category) { $categories[$i]['groups'] = $DB->selectAssocs("\n SELECT id, tytul FROM BDL_grupy WHERE kat_id = " . $category['id'] . " AND deleted = '0' AND okres = 'R'\n "); foreach ($categories[$i]['groups'] as $m => $group) { $categories[$i]['groups'][$m]['subgroups'] = $DB->selectAssocs("\n SELECT id, tytul FROM BDL_podgrupy WHERE grupa_id = " . $group['id'] . " AND deleted = '0' AND okres = 'R'\n "); } } } $this->setSerialized('categories', $categories); }