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