function extractFormElementsGroup($sqlQuery, $showLinks, $userID, $displayType, $originalDisplayType)
{
global $tableRefs, $tableUserData;
// defined in 'db.inc.php'
$groupSearchSelector = $_REQUEST['groupSearchSelector'];
// extract the user group chosen by the user
$groupSearchSelector = preg_quote($groupSearchSelector, "/");
// escape possible meta characters within group names (otherwise the RLIKE SQL query that's generated below might fail)
if (!empty($originalDisplayType)) {
// NOTE: if the user submits the 'groupSearch' form from Browse view, we currently don't display
// the group's entries in Browse view but switch to the user's default view instead
if ($originalDisplayType == "Browse") {
// note that, as long as the 'groupSearch' form doesn't transmit a 'submit' parameter, the next line
// isn't strictly necessary (since then '$displayType' will already contain the user's default view)
$displayType = $_SESSION['userDefaultView'];
} else {
// re-assign the correct display type (i.e. the view that was active when the user clicked the 'Show' button of the 'groupSearch' form):
$displayType = $originalDisplayType;
}
}
if (preg_match("/^(Cite|Display)\$/i", $displayType)) {
if (preg_match("/^Display\$/i", $displayType) and isset($_SESSION['lastDetailsViewQuery'])) {
$previousSelectClause = extractSELECTclause($_SESSION['lastDetailsViewQuery']);
// function 'extractSELECTclause()' is defined in 'include.inc.php'
$query = buildSELECTclause($displayType, $showLinks, "", false, true, $previousSelectClause);
// function 'buildSELECTclause()' is defined in 'include.inc.php'
} else {
// generate a new SELECT clause that's appropriate for Citation view (or Details view):
$query = buildSELECTclause($displayType, $showLinks);
}
} elseif ($originalDisplayType != "Browse" and !empty($sqlQuery)) {
// use the custom set of colums chosen by the user:
$previousSelectClause = extractSELECTclause($sqlQuery);
$query = buildSELECTclause("", $showLinks, "", false, true, $previousSelectClause);
} else {
// use the default SELECT statement:
$query = buildSELECTclause("", $showLinks, "user_groups", false, true);
}
if ($originalDisplayType != "Browse" and !empty($sqlQuery)) {
// use the custom ORDER BY clause chosen by the user:
$queryOrderBy = extractORDERBYclause($sqlQuery);
} else {
// add the default ORDER BY clause:
$queryOrderBy = "author, year DESC, publication";
}
$query .= " FROM {$tableRefs} LEFT JOIN {$tableUserData} ON serial = record_id AND user_id = " . $userID;
// add FROM clause
$query .= " WHERE user_groups RLIKE " . quote_smart("(^|.*;) *" . $groupSearchSelector . " *(;.*|\$)");
// add WHERE clause
$query .= " ORDER BY " . $queryOrderBy;
// add ORDER BY clause
return array($query, $displayType);
}
// Since the latter is of minor importance we'll require $citeStyle == "Text Citation" here:
if (!empty($userID)) {
// if the 'userID' parameter was specified...
$additionalFields = "cite_key";
}
// add user-specific fields which are required in Citation view
} elseif (!preg_match("/^Display\$/i", $displayType)) {
if (!empty($recordIDSelector)) {
// if a record identifier (either 'serial', 'call_number' or 'cite_key') was entered via the 'show.php' web form
$additionalFields = escapeSQL($recordIDSelector);
}
// display the appropriate column
}
if (preg_match("/^Display\$/i", $displayType) and isset($_SESSION['lastDetailsViewQuery'])) {
// get SELECT clause from any previous Details view query:
$query = "SELECT " . extractSELECTclause($_SESSION['lastDetailsViewQuery']);
} else {
// generate new SELECT clause:
$query = buildSELECTclause($displayType, $showLinks, $additionalFields, false, false, "", $browseByField);
}
// function 'buildSELECTclause()' is defined in 'include.inc.php'
// Build FROM clause:
// We'll explicitly add the 'LEFT JOIN...' part to the 'FROM' clause of the SQL query if '$userID' isn't empty. This is done since the 'verifySQLQuery()' function
// (mentioned above) excludes the 'selected' field from its magic. By that we allow the 'selected' field to be queried by any user (using 'show.php')
// (e.g., by URLs of the form: 'show.php?author=...&userID=...&selected=yes').
if (!empty($userID)) {
// the 'userID' parameter was specified -> we include user specific fields
$query .= " FROM {$tableRefs} LEFT JOIN {$tableUserData} ON serial = record_id AND user_id = " . quote_smart($userID);
} else {
$query .= " FROM {$tableRefs}";
}
function extractFormElementsGroup($sqlQuery)
{
global $tableUsers;
// defined in 'db.inc.php'
if (!empty($sqlQuery)) {
// use the custom set of colums chosen by the user:
$query = "SELECT " . extractSELECTclause($sqlQuery);
// function 'extractSELECTclause()' is defined in 'include.inc.php'
// user the custom ORDER BY clause chosen by the user:
$queryOrderBy = extractORDERBYclause($sqlQuery);
// function 'extractORDERBYclause()' is defined in 'include.inc.php'
} else {
$query = "SELECT first_name, last_name, abbrev_institution, email, last_login, logins, user_id";
// use the default SELECT statement
$queryOrderBy = "last_login DESC, last_name, first_name";
// add the default ORDER BY clause
}
$groupSearchSelector = $_REQUEST['groupSearchSelector'];
// extract the user group chosen by the user
$query .= ", user_id";
// add 'user_id' column (although it won't be visible the 'user_id' column gets included in every search query)
// (which is required in order to obtain unique checkbox names as well as for use in the 'getUserID()' function)
$query .= " FROM {$tableUsers}";
// add FROM clause
$query .= " WHERE user_groups RLIKE " . quote_smart("(^|.*;) *" . $groupSearchSelector . " *(;.*|\$)");
// add WHERE clause
$query .= " ORDER BY " . $queryOrderBy;
// add ORDER BY clause
return $query;
}
function extractFormElementsRefineDisplay($queryTable, $displayType, $originalDisplayType, $query, $showLinks, $citeOrder, $userID)
{
global $tableRefs, $tableUserData, $tableUsers;
// defined in 'db.inc.php'
global $loc;
// '$loc' is made globally available in 'core.php'
$encodedDisplayType = encodeHTML($displayType);
// note that we need to HTML encode '$displayType' for comparison with the HTML encoded locales
// extract form variables:
if ($encodedDisplayType == $loc["ButtonTitle_Search"]) {
$fieldSelector = $_REQUEST['refineSearchSelector'];
// extract field name chosen by the user
$refineSearchName = $_REQUEST['refineSearchName'];
// extract search text entered by the user
if (isset($_REQUEST['refineSearchExclude'])) {
// extract user option whether matched records should be included or excluded
$refineSearchActionCheckbox = $_REQUEST['refineSearchExclude'];
} else {
$refineSearchActionCheckbox = "0";
}
// the user did NOT mark the checkbox next to "Exclude matches"
} elseif (preg_match("/^(" . $loc["ButtonTitle_Show"] . "|" . $loc["ButtonTitle_Hide"] . "|" . $loc["ButtonTitle_Browse"] . ")\$/", $encodedDisplayType)) {
if (isset($_REQUEST['displayOptionsSelector'])) {
$fieldSelector = $_REQUEST['displayOptionsSelector'];
} else {
$fieldSelector = "";
}
} else {
$fieldSelector = "";
}
// this avoids 'Undefined variable...' messages when a user has changed the language setting on the options page, and then reloads an existing page (whose URL still has a 'submit' value in the previously used language)
// extract the fields of the SELECT clause from the current SQL query:
$previousSelectClause = extractSELECTclause($query);
// ensure to add any required fields to the SELECT clause:
if ($queryTable == $tableRefs) {
// 'search.php':
$addRequiredFields = true;
} elseif ($queryTable == $tableUsers) {
// 'users.php':
$addRequiredFields = false;
}
// we'll add any required fields to the 'users.php' SELECT clause below
// TODO: this wouldn't be necessary if function 'buildSELECTclause()' would handle the requirements of 'users.php'
$additionalFields = "";
if ($encodedDisplayType == $loc["ButtonTitle_Search"]) {
// rebuild the current SELECT clause:
$newSelectClause = buildSELECTclause($originalDisplayType, $showLinks, $additionalFields, false, $addRequiredFields, $previousSelectClause);
// replace current SELECT clause:
$query = newSELECTclause($newSelectClause, $query, false);
if ($refineSearchName != "") {
// Depending on the chosen output action, construct an appropriate SQL query:
if ($refineSearchActionCheckbox == "0") {
// for the fields 'marked=no', 'copy=false' and 'selected=no', force NULL values to be matched:
if ($fieldSelector == "marked" and $refineSearchName == "no" or $fieldSelector == "copy" and $refineSearchName == "false" or $fieldSelector == "selected" and $refineSearchName == "no") {
$query = preg_replace("/ WHERE /i", " WHERE ({$fieldSelector} RLIKE " . quote_smart($refineSearchName) . " OR {$fieldSelector} IS NULL) AND ", $query);
} else {
// add default 'WHERE' clause:
$query = preg_replace("/ WHERE /i", " WHERE {$fieldSelector} RLIKE " . quote_smart($refineSearchName) . " AND ", $query);
}
// ...add search field name & value to the SQL query
} else {
$query = preg_replace("/ WHERE /i", " WHERE ({$fieldSelector} NOT RLIKE " . quote_smart($refineSearchName) . " OR {$fieldSelector} IS NULL) AND ", $query);
// ...add search field name & value to the SQL query
}
$query = preg_replace('/ AND serial RLIKE "\\.\\+"/i', '', $query);
// remove any 'AND serial RLIKE ".+"' which isn't required anymore
}
// else, if the user did NOT type a search string into the text entry field, we simply keep the old WHERE clause...
} elseif (preg_match("/^(" . $loc["ButtonTitle_Show"] . "|" . $loc["ButtonTitle_Hide"] . ")\$/", $encodedDisplayType)) {
if (preg_match("/^Cite\$/i", $originalDisplayType)) {
// generate a SELECT clause that's appropriate for Citation view (or Details view):
$newSelectClause = buildSELECTclause($originalDisplayType, $showLinks, $additionalFields, false, $addRequiredFields);
// rebuild the current ORDER clause:
if (preg_match("/^(author|year|type|type-year|creation-date)\$/i", $citeOrder)) {
if ($citeOrder == "year") {
// sort records first by year (descending):
$newORDER = "ORDER BY year DESC, first_author, author_count, author, title";
} elseif ($citeOrder == "type") {
// sort records first by record type and thesis type (descending):
$newORDER = "ORDER BY type DESC, thesis DESC, first_author, author_count, author, year, title";
} elseif ($citeOrder == "type-year") {
// sort records first by record type and thesis type (descending), then by year (descending):
$newORDER = "ORDER BY type DESC, thesis DESC, year DESC, first_author, author_count, author, title";
} elseif ($citeOrder == "creation-date") {
// sort records such that newly added/edited records get listed top of the list:
$newORDER = "ORDER BY created_date DESC, created_time DESC, modified_date DESC, modified_time DESC, serial DESC";
} elseif ($citeOrder == "author") {
// supply the default ORDER BY pattern (which is suitable for citation in a journal etc.):
$newORDER = "ORDER BY first_author, author_count, author, year, title";
}
// replace current ORDER clause:
$query = newORDERclause($newORDER, $query, false);
}
// else if any other or no '$citeOrder' parameter is specified, we keep the current ORDER BY clause
// NOTE: this behaviour is different from functions 'extractFormElementsQueryResults()' and 'extractFormElementsExtract()'
// where we always use 'ORDER BY first_author, author_count, author, year, title' as default ORDER BY clause
// (to ensure correct sorting for output to bibliographic reference lists)
} elseif (preg_match("/^Display\$/i", $originalDisplayType)) {
// NOTE: the below code for displaying & hiding of fields in Details view must be adopted if either layout or field names are changed!
$fieldsList = "";
if ($fieldSelector == "all fields") {
// generate a SELECT clause that shows all fields in Details view:
$newSelectClause = buildSELECTclause($originalDisplayType, $showLinks, $additionalFields, true, $addRequiredFields);
} else {
if ($encodedDisplayType == $loc["ButtonTitle_Show"]) {
$matchField = "pages";
if ($fieldSelector == "keywords, abstract") {
$fieldsList = ", keywords, abstract";
} elseif ($fieldSelector == "additional fields") {
$fieldsList = ", address, corporate_author, thesis, publisher, place, editor, language, summary_language, orig_title, series_editor, series_title, abbrev_series_title, series_volume, series_issue, edition, issn, isbn, medium, area, expedition, conference, notes, approved";
if (isset($_SESSION['loginEmail'])) {
$fieldsList .= ", location";
}
// we only add the 'location' field if the user is logged in
if (preg_match("/\\babstract\\b/i", $previousSelectClause)) {
$matchField = "abstract";
}
} elseif ($fieldSelector == "my fields") {
$fieldsList = ", marked, copy, selected, user_keys, user_notes, user_file, user_groups, cite_key";
if (preg_match("/\\bserial\\b/i", $previousSelectClause)) {
$matchField = "serial";
} elseif (preg_match("/\\babstract\\b/i", $previousSelectClause)) {
$matchField = "abstract";
}
}
if (!empty($fieldsList) and !preg_match("/\\b" . $fieldsList . "\\b/i", $previousSelectClause)) {
// if none of the chosen fields are currently displayed...
$previousSelectClause = preg_replace("/(?<=\\b" . $matchField . "\\b)/i", $fieldsList, $previousSelectClause);
}
// ...add the chosen fields to the current SELECT clause:
}
if ($encodedDisplayType == $loc["ButtonTitle_Hide"]) {
if ($fieldSelector == "keywords, abstract") {
$fieldsList = "\\b(keywords|abstract)\\b";
} elseif ($fieldSelector == "additional fields") {
$fieldsList = "\\b(corporate_author|thesis|address|publisher|place|editor|language|summary_language|orig_title|series_editor|series_title|abbrev_series_title|series_volume|series_issue|edition|issn|isbn|medium|area|expedition|conference|notes|approved|location)\\b";
} elseif ($fieldSelector == "my fields") {
$fieldsList = "\\b(marked|copy|selected|user_keys|user_notes|user_file|user_groups|cite_key)\\b";
}
if (!empty($fieldsList) and preg_match("/\\b" . $fieldsList . "\\b/i", $previousSelectClause)) {
// ...remove the chosen fields from the fields given in the current SELECT clause:
$previousSelectClause = preg_replace("/ *, *" . $fieldsList . " */i", "", $previousSelectClause);
// all columns except the first
$previousSelectClause = preg_replace("/ *" . $fieldsList . " *, */i", "", $previousSelectClause);
// all columns except the last
}
}
// rebuild the current SELECT clause, but include (or exclude) the chosen fields:
$newSelectClause = buildSELECTclause($originalDisplayType, $showLinks, $additionalFields, false, $addRequiredFields, $previousSelectClause);
}
} else {
if ($encodedDisplayType == $loc["ButtonTitle_Show"]) {
if (!preg_match("/\\b" . $fieldSelector . "\\b/i", $previousSelectClause)) {
// ...and the chosen field is *not* already displayed...
$additionalFields = $fieldSelector;
}
// ...add the chosen field to the current SELECT clause
} elseif ($encodedDisplayType == $loc["ButtonTitle_Hide"]) {
if (preg_match("/\\b" . $fieldSelector . "\\b/i", $previousSelectClause)) {
// ...remove the chosen field from the fields given in the current SELECT clause:
$previousSelectClause = preg_replace("/ *, *\\b" . $fieldSelector . "\\b */i", "", $previousSelectClause);
// all columns except the first
$previousSelectClause = preg_replace("/ *\\b" . $fieldSelector . "\\b *, */i", "", $previousSelectClause);
// all columns except the last
}
}
// rebuild the current SELECT clause, but include (or exclude) the chosen field:
$newSelectClause = buildSELECTclause("", $showLinks, $additionalFields, false, $addRequiredFields, $previousSelectClause);
}
// replace current SELECT clause:
$query = newSELECTclause($newSelectClause, $query, false);
} elseif ($encodedDisplayType == $loc["ButtonTitle_Browse"]) {
$previousField = preg_replace("/^SELECT (\\w+).+/i", "\\1", $query);
// extract the field that was previously used in Browse view
if (!preg_match("/^" . $fieldSelector . "\$/i", $previousField)) {
// ...modify the SQL query to show a summary for the new field that was chosen by the user:
// (NOTE: these replace patterns aren't 100% safe and may fail if the user has modified the query using 'sql_search.php'!)
$query = preg_replace("/^SELECT {$previousField}/i", "SELECT {$fieldSelector}", $query);
// use the field that was chosen by the user for Browse view
$query = preg_replace("/GROUP BY {$previousField}/i", "GROUP BY {$fieldSelector}", $query);
// group data by the field that was chosen by the user
$query = preg_replace("/ORDER BY( records( DESC)?,)? {$previousField}/i", "ORDER BY\\1 {$fieldSelector}", $query);
// order data by the field that was chosen by the user
}
}
// re-establish the original display type:
// (resetting '$displayType' to its original value is required for Browse view; for List view, it does also correct incorrect
// display types such as 'Search' or 'Show'/'Hide' which stem from the submit buttons in the forms of the results header)
$displayType = $originalDisplayType;
// the following changes to the SQL query are performed for both forms ("Search within Results" and "Display Options"):
if ($queryTable == $tableRefs) {
// if the chosen field is one of the user-specific fields from table 'user_data': 'marked', 'copy', 'selected', 'user_keys', 'user_notes', 'user_file', 'user_groups', 'cite_key' or 'related'
if (preg_match("/^(marked|copy|selected|user_keys|user_notes|user_file|user_groups|cite_key|related|my fields)\$/i", $fieldSelector)) {
// 'my fields' is used in Details view as an alias for all user-specific fields
if (!preg_match("/LEFT JOIN {$tableUserData}/i", $query)) {
// ...and if the 'LEFT JOIN...' statement isn't already part of the 'FROM' clause...
$query = preg_replace("/ FROM {$tableRefs}/i", " FROM {$tableRefs} LEFT JOIN {$tableUserData} ON serial = record_id AND user_id = {$userID}", $query);
}
}
// ...add the 'LEFT JOIN...' part to the 'FROM' clause
} elseif ($queryTable == $tableUsers) {
// TODO: this wouldn't be necessary if function 'buildSELECTclause()' would handle the requirements of 'users.php' (see also above)
$query = preg_replace("/ FROM {$tableUsers}/i", ", user_id FROM {$tableUsers}", $query);
// add 'user_id' column (although it won't be visible the 'user_id' column gets included in every search query)
// (which is required in order to obtain unique checkbox names as well as for use in the 'getUserID()' function)
}
return array($query, $displayType);
}
