function getSql($type, $user, $ofs = 0, $orderBy = '') { if (DB_DRIVER != 'postgre' && $orderBy[0] == 'bbox') { $this->sortBy = getSortBy(SORT_BY, $ret = 'array'); $orderBy = ''; } if ($this->bbox == NULL && is_array($orderBy) && $orderBy[0] == 'bbox') { $this->sortBy = getSortBy(SORT_BY, $ret = 'array'); $orderBy = ''; } if ($orderBy == '') { $orderBy = $this->sortBy; } $sql = ''; if ($type == '' || $user == '') { setMickaLog('type or user is null', 'WARNING', "MdExport.getSql"); return $sql; } $sortBy = $orderBy[0] . ' ' . $orderBy[1]; //echo "SORT BY: $sortBy<br>"; //$sortBy = 'recno'; // řazení dle bbox $selectBbox = ''; if (is_array($this->bbox) && count($this->bbox) == 4 && is_array($orderBy) && $orderBy[0] == 'bbox') { list($x1, $y1, $x2, $y2) = $this->bbox; $a = ($x2 - $x1) * ($y2 - $y1); //$selectBbox = "abs(x2 - x1 - $x2 + $x1) + abs(x1 + x2 - $x1 - $x2)/2 + abs(y2 - y1 - $y2 + $y1) + abs(y1 + y2 - $y1 - $y2)/2"; $selectBbox = "greatest((x2-x1)*(y2-y1),{$a})/least(greatest((x2-x1)*(y2-y1),0.00000000000001),{$a})"; $selectBbox = ", " . $selectBbox . " AS bbox"; } $sortBy_mdpath = ''; if ($this->xml_from == 'cache') { //$sortBy = 'recno'; //$sql_spol['md_select'] = "SELECT recno, uuid, xmldata"; $sql_spol['md_select'] = "SELECT recno, uuid, md_standard, lang, data_type, create_user, create_date, last_update_user, last_update_date, edit_group, view_group, valid, prim, xmldata AS pxml, server_name"; $sql_spol['md_from'] = " FROM md WHERE (recno IN (SELECT recno FROM("; $sql_spol['md_order'] = ""; $sql_spol['md_count'] = "\n\t\t\t\tSELECT \tcount(DISTINCT recno) AS Celkem\n\t\t\t"; } elseif ($this->xml_from == 'data') { $sortBy = 'recno'; $sortBy_mdpath = ', md_values.md_path'; $sql_spol['md_select'] = "\n\t\t\t\tSELECT md_values.recno, md_values.md_path, " . setNtext2Text('md_values.', 'md_value') . ", md_values.lang, md.md_standard\n\t\t\t"; $sql_spol['md_from'] = "\n\t\t\t\tFROM (md JOIN md_values ON md.recno = md_values.recno) JOIN standard ON md.md_standard = standard.md_standard\n\t\t\t\tWHERE (md_values.recno IN (SELECT recno FROM(\n\t\t\t"; $sql_spol['md_order'] = " ORDER BY md_values.recno, md_values.md_path"; $sql_spol['md_count'] = "\n\t\t\t\tSELECT \tcount(DISTINCT md.recno) AS Celkem\n\t\t\t"; } else { $sql_spol['md_select'] = "\n\t\t\t\tSELECT\n\t\t\t\t\t\t\t\tmd.recno,\n\t\t\t\t\t\t\t\tmd.uuid,\n\t\t\t\t\t\t\t\tmd.md_standard,\n\t\t\t\t\t\t\t\tmd.edit_group,\n\t\t\t\t\t\t\t\tmd.create_user,\n\t\t\t\t\t\t\t\tmd.valid,\n\t\t\t\t\t\t\t\tmd.data_type,\n\t\t\t\t\t\t\t\t" . setNtext2Text('md_values.', 'md_value') . ",\n\t\t\t\t\t\t\t\tmd_values.md_id,\n\t\t\t\t\t\t\t\tmd_values.lang,\n\t\t\t\t\t\t\t\tstandard.md_standard_short_name,\n\t\t\t\t\t\t\t\tmd_summary.md_summary\n\t\t\t"; $sql_spol['md_from'] = "\n\t\t\t\tFROM (md_values JOIN (md JOIN md_summary ON md.md_standard = md_summary.md_standard)\n\t\t\t\t\tON (md_values.md_id = md_summary.md_id) AND (md_values.recno = md.recno))\n\t\t\t\t\tJOIN standard ON md_summary.md_standard = standard.md_standard\n\t\t\t\tWHERE (md_summary.md_summary IS NOT NULL AND md_values.recno IN (SELECT recno FROM(\n\t\t\t"; $sql_spol['md_order'] = " ORDER BY md_values.recno, md_values.md_path"; $sql_spol['md_count'] = "\n\t\t\t\tSELECT \tcount(DISTINCT md.recno) AS Celkem\n\t\t\t"; } $sql_spol['md_in_end'] = ")"; $sql_spol['md_where_end'] = ")"; if ($type == 'count') { /* $sql_final = str_replace('SELECT DISTINCT md.recno, md.last_update_date, md.title', 'SELECT DISTINCT md.recno', $this->sql_final[0]); */ if ($this->useOrderByXmlPath === TRUE) { switch (DB_DRIVER) { case 'postgre': $sql_final = str_replace('SELECT DISTINCT md.recno, md.last_update_date, md.title', 'SELECT DISTINCT md.recno, md.last_update_date, COALESCE((xpath(\'//gmd:identificationInfo/*/gmd:citation/*/gmd:title//gmd:LocalisedCharacterString[contains(@locale, "' . MICKA_LANG . '")]/text()\', pxml, ARRAY\\[ARRAY\\[\'gmd\', \'http://www.isotc211.org/2005/gmd\'\\]\\]))\\[1\\]::text, title) AS title', $this->sql_final[0]); break; case 'oracle': $sql_final = str_replace('SELECT DISTINCT md.recno, md.last_update_date, md.title', 'SELECT DISTINCT md.recno, md.last_update_date, COALESCE(extractValue(pxml,\'//gmd:identificationInfo/*/gmd:citation/*/gmd:title//gmd:LocalisedCharacterString[contains(@locale, "' . MICKA_LANG . '")]/node()\',\'xmlns:gmd=http://www.isotc211.org/2005/gmd\'), title) title', $this->sql_final[0]); break; default: $sql_final = $this->sql_final[0]; break; } } else { $sql_final = $this->sql_final[0]; } $sql = "SELECT \tcount(DISTINCT recno) AS Celkem FROM md WHERE (recno IN (SELECT recno FROM(" . $sql_final . ') jojo))'; } elseif ($type == 'sample') { // odhad pro oracle $sql = "SELECT \tcount(recno) * 10000 AS Celkem FROM md SAMPLE (0.01) WHERE (recno IN (SELECT recno FROM(" . $this->sql_final[0] . ') jojo))'; } elseif ($type == 'find') { // stránkování podle typu databáze switch (DB_DRIVER) { case 'postgre': $sql_final = $this->sql_final[0]; if ($this->useOrderByXmlPath === TRUE) { $sql_final = str_replace('SELECT DISTINCT md.recno, md.last_update_date, md.title', 'SELECT DISTINCT md.recno, md.last_update_date, COALESCE((xpath(\'//gmd:identificationInfo/*/gmd:citation/*/gmd:title//gmd:LocalisedCharacterString[contains(@locale, "' . MICKA_LANG . '")]/text()\', pxml, ARRAY\\[ARRAY\\[\'gmd\', \'http://www.isotc211.org/2005/gmd\'\\]\\]))\\[1\\]::text, title) AS title', $sql_final); $sql_spol['md_select'] .= ', COALESCE((xpath(\'//gmd:identificationInfo/*/gmd:citation/*/gmd:title//gmd:LocalisedCharacterString[contains(@locale, "' . MICKA_LANG . '")]/text()\', pxml, ARRAY\\[ARRAY\\[\'gmd\', \'http://www.isotc211.org/2005/gmd\'\\]\\]))\\[1\\]::text, title) AS title'; } $sql_final = str_replace('AS title', 'AS title ' . $selectBbox, $sql_final); $select_limit = " LIMIT " . $this->maxRecords . " OFFSET {$ofs}"; $sql = $sql_spol['md_select'] . $selectBbox . $sql_spol['md_from'] . $sql_final . ") jojo ORDER BY {$sortBy} {$select_limit})) ORDER BY {$sortBy} {$sortBy_mdpath}"; break; case 'oracle': $from = $ofs + 1; $to = $ofs + $this->maxRecords; $sql_ora = $this->sql_final[0]; if ($this->useOrderByXmlPath === TRUE) { $sql_ora = str_replace('SELECT DISTINCT md.recno, md.last_update_date, md.title', 'SELECT DISTINCT md.recno, md.last_update_date, COALESCE(extractValue(pxml,\'//gmd:identificationInfo/*/gmd:citation/*/gmd:title//gmd:LocalisedCharacterString[contains(@locale, "' . MICKA_LANG . '")]/node()\',\'xmlns:gmd=http://www.isotc211.org/2005/gmd\'), title) title', $this->sql_final[0]); $sql_spol['md_select'] .= ', COALESCE(extractValue(pxml,\'//gmd:identificationInfo/*/gmd:citation/*/gmd:title//gmd:LocalisedCharacterString[contains(@locale, "' . MICKA_LANG . '")]/node()\',\'xmlns:gmd=http://www.isotc211.org/2005/gmd\'), title) title'; } $sql = $sql_spol['md_select'] . $sql_spol['md_from'] . 'SELECT t.*, ROWNUM AS rnum FROM (' . $sql_ora . " ORDER BY {$sortBy}" . ') t) WHERE rnum' . " BETWEEN {$from} AND {$to}" . '))' . " ORDER BY {$sortBy} {$sortBy_mdpath}"; break; case 'mssql2005': $from = $ofs + 1; $to = $ofs + $this->maxRecords; $sql_ora = $this->sql_final[0]; $sql = $sql_spol['md_select'] . $sql_spol['md_from'] . "SELECT ROW_NUMBER() OVER (ORDER BY {$sortBy}) AS rnum, recno, last_update_date, title FROM (" . $sql_ora . ') AS jo) AS jojo WHERE rnum' . " BETWEEN {$from} AND {$to}" . '))' . " ORDER BY {$sortBy} {$sortBy_mdpath}"; break; } } setMickaLog(array($sql), 'DEBUG', "MdExport.getSql.return"); return $sql; }
/** * Funkce pro hledání a obsluhu $_SESSION['micka']['search'] * starý způsob, používá už pouze ak=md_search * * @link http://www.bnhelp.cz * @package Micka * @category Metadata * @version 20140530 * @authors DZ */ function getFoundsRecords($post, $user) { // ošetření vstupu XSS if (is_array($post)) { foreach ($post as $key => $value) { $post[$key] = htmlspecialchars($value); } } $rs = array(); $rs['paginator']['records'] = 0; $restore = FALSE; // zjišťuje současný stav hledání podle session a jeho zobrazení $exist_where = FALSE; $page_number = ''; $form_view = -1; $export = FALSE; // řazení výsledku if (isset($post['sort0']) && isset($post['sort1'])) { setSortBy(trim($post['sort0']) . ',' . trim($post['sort1'])); } // kontrola módů hledání if (isset($post['mode']) && ($post['mode'] == 'normal' || $post['mode'] == 'myrecords')) { $_SESSION['micka']['search']['mode'] = $post['mode']; } elseif (isset($post['mode']) && $post['mode'] == 'master') { $_SESSION['micka']['search']['mode'] = $post['mode']; } else { // nepodporovaný mód, vrátí nenalezeno if ($_SESSION['micka']['search']['mode'] == '') { return $rs; } else { $restore = TRUE; } } if (isset($_SESSION['micka']['search']['where']) && $_SESSION['micka']['search']['where'] != '') { $exist_where = true; } if ($_SESSION['micka']['search']['mode'] == 'master') { $fc = isset($post['fc']) ? $post['fc'] : ''; // feature katalog $form_view = isset($post['fv']) ? $post['fv'] : ''; // prvotní zobrazení, pokud nezadáno tak se hledá, jinak se jen zobrazí formulář if ($form_view == '') { $form_view = -1; } else { $form_view = 1; } } if ($restore) { if ($_SESSION['micka']['search']['mode'] == 'master') { $page_number = isset($_SESSION['micka']['search_m']['page']) ? $_SESSION['micka']['search_m']['page'] : 1; } else { $page_number = isset($_SESSION['micka']['search']['page']) ? $_SESSION['micka']['search']['page'] : 1; } } else { if (isset($post['pg'])) { $page_number = $post['pg']; } } if ($page_number == '') { $page_number = 1; setSessionMickaSearch($post); } // sestaveni dotazu pro hledani if ($_SESSION['micka']['search']['mode'] == 'myrecords') { $select_all = getSearchUser($user); $export = TRUE; } else { if ($_SESSION['micka']['search']['mode'] == 'normal' && $exist_where == FALSE) { $select_all = getSearchArrayNormal(); } elseif ($form_view == -1 && $_SESSION['micka']['search']['mode'] == 'master') { $select_all = getSearchArrayMaster($fc); } if ($_SESSION['micka']['search']['mode'] == 'normal' && $exist_where === TRUE) { $select_all = $_SESSION['micka']['search']['where']; $page_number = 1; $export = TRUE; } elseif ($form_view == -1 && $_SESSION['micka']['search']['mode'] == 'master' || $_SESSION['micka']['search']['mode'] == 'normal') { $export = TRUE; } } if ($export) { // získání dat require_once PHPPRG_DIR . '/MdExport.php'; $ofs = ($page_number - 1) * MAXRECORDS; if ($ofs > 0) { $ofs++; } $data = new MdExport(MICKA_USER, $ofs, MAXRECORDS, getSortBy($in = '', $ret = 'string')); $data->page_number = $page_number; $data->only_public = FALSE; $data->xml_from = 'summary'; $rs = $data->getData($select_all); if ($rs['paginator']['records'] > 0) { // ulozeni stranky pro navrat pri editaci if (isset($_SESSION['micka']['search']['mode']) && $_SESSION['micka']['search']['mode'] == 'master') { $_SESSION['micka']['search_m']['page'] = $page_number; } else { $_SESSION['micka']['search']['page'] = $page_number; } } } //$rs['data'] //$rs['paginator'] //$rs['akce'] return $rs; }