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