Ejemplo n.º 1
0
function findDuplicates($sqlQuery, $originalDisplayType)
{
    global $tableRefs, $tableUserData;
    // defined in 'db.inc.php'
    global $alnum, $alpha, $cntrl, $dash, $digit, $graph, $lower, $print, $punct, $space, $upper, $word, $patternModifiers;
    // defined in 'transtab_unicode_charset.inc.php' and 'transtab_latin1_charset.inc.php'
    // re-assign the correct display type (i.e. the view that was active when the user clicked the 'dups' link in the header):
    if (!empty($originalDisplayType)) {
        $displayType = $originalDisplayType;
    }
    // Extract form variables provided by the 'duplicateSearch' form in 'duplicate_search.php':
    if (isset($_REQUEST['matchFieldsSelector'])) {
        if (is_string($_REQUEST['matchFieldsSelector'])) {
            // we accept a string containing a (e.g. comma delimited) list of field names
            $selectedFieldsArray = preg_split("/[^a-z_]+/", $_REQUEST['matchFieldsSelector'], -1, PREG_SPLIT_NO_EMPTY);
        } else {
            // the field list is already provided as array:
            $selectedFieldsArray = $_REQUEST['matchFieldsSelector'];
        }
    } else {
        $selectedFieldsArray = array();
    }
    if (isset($_REQUEST['ignoreWhitespace']) and $_REQUEST['ignoreWhitespace'] == "1") {
        $ignoreWhitespace = "1";
    } else {
        $ignoreWhitespace = "0";
    }
    if (isset($_REQUEST['ignorePunctuation']) and $_REQUEST['ignorePunctuation'] == "1") {
        $ignorePunctuation = "1";
    } else {
        $ignorePunctuation = "0";
    }
    if (isset($_REQUEST['ignoreCharacterCase']) and $_REQUEST['ignoreCharacterCase'] == "1") {
        $ignoreCharacterCase = "1";
    } else {
        $ignoreCharacterCase = "0";
    }
    if (isset($_REQUEST['ignoreAuthorInitials']) and $_REQUEST['ignoreAuthorInitials'] == "1") {
        $ignoreAuthorInitials = "1";
    } else {
        $ignoreAuthorInitials = "0";
    }
    if (isset($_REQUEST['nonASCIIChars'])) {
        $nonASCIIChars = $_REQUEST['nonASCIIChars'];
    } else {
        $nonASCIIChars = "keep";
    }
    // VALIDATE FORM DATA:
    $errors = array();
    // Validate the field selector:
    if (empty($selectedFieldsArray)) {
        $errors["matchFieldsSelector"] = "You must select at least one field:";
    }
    // Validate the 'SQL Query' field:
    if (empty($sqlQuery)) {
        $errors["sqlQuery"] = "You must specify a query string:";
    } elseif (!preg_match("/^SELECT/i", $sqlQuery)) {
        $errors["sqlQuery"] = "You can only execute SELECT queries:";
    }
    // Check if there were any errors:
    if (count($errors) > 0) {
        // In case of an error, we write all form variables back to the '$formVars' array
        // (which 'duplicate_search.php' requires to reload form values):
        foreach ($_REQUEST as $varname => $value) {
            $formVars[$varname] = $value;
        }
        // Since checkbox form fields do only get included in the '$_REQUEST' array if they were marked,
        // we have to add appropriate array elements for all checkboxes that weren't set:
        if (!isset($formVars["ignoreWhitespace"])) {
            $formVars["ignoreWhitespace"] = "0";
        }
        if (!isset($formVars["ignorePunctuation"])) {
            $formVars["ignorePunctuation"] = "0";
        }
        if (!isset($formVars["ignoreCharacterCase"])) {
            $formVars["ignoreCharacterCase"] = "0";
        }
        if (!isset($formVars["ignoreAuthorInitials"])) {
            $formVars["ignoreAuthorInitials"] = "0";
        }
        if (!isset($formVars["showLinks"])) {
            $formVars["showLinks"] = "0";
        }
        // Write back session variables:
        saveSessionVariable("errors", $errors);
        // function 'saveSessionVariable()' is defined in 'include.inc.php'
        saveSessionVariable("formVars", $formVars);
        // There are errors. Relocate back to 'duplicate_search.php':
        header("Location: duplicate_search.php");
        exit;
        // >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> !EXIT! <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    }
    // CONSTRUCT SQL QUERY (1. DUPLICATE SEARCH):
    // To identify any duplicates within the results of the original query, we build a new query based on the original SQL query:
    $query = $sqlQuery;
    // Replace SELECT list of columns with those from '$selectedFieldsArray' (plus the 'serial' column):
    $selectedFieldsString = implode(", ", $selectedFieldsArray);
    $query = newSELECTclause("SELECT " . $selectedFieldsString . ", serial", $query, false);
    // function 'newSELECTclause()' is defined in 'include.inc.php'
    // Replace any existing ORDER BY clause with the list of columns given in '$selectedFieldsArray':
    $query = newORDERclause("ORDER BY " . $selectedFieldsString, $query, false);
    // function 'newORDERclause()' is defined in 'include.inc.php'
    // Fix escape sequences within the SQL query:
    $query = stripSlashesIfMagicQuotes($query);
    // RUN the query on the database through the connection:
    $result = queryMySQLDatabase($query);
    // function 'queryMySQLDatabase()' is defined in 'include.inc.php'
    // PROCESS RESULTS:
    $recordSerialsArray = array();
    $duplicateRecordSerialsArray = array();
    $rowsFound = @mysql_num_rows($result);
    // Identify any records with matching field data:
    if ($rowsFound > 0) {
        // Count the number of fields:
        $fieldsFound = mysql_num_fields($result);
        // Loop over each row in the result set:
        for ($rowCounter = 0; $row = @mysql_fetch_array($result); $rowCounter++) {
            $recordIdentifier = "";
            // make sure our buffer variable is empty
            // For each row, loop over each field (except for the last one which is the 'serial' field):
            for ($i = 0; $i < $fieldsFound - 1; $i++) {
                // fetch the current attribute name:
                $fieldName = getMySQLFieldInfo($result, $i, "name");
                // function 'getMySQLFieldInfo()' is defined in 'include.inc.php'
                // normalize author names:
                if ($fieldName == "author" and $ignoreAuthorInitials == "1") {
                    // this is a stupid hack that maps the names of the '$row' array keys to those used
                    // by the '$formVars' array (which is required by function 'parsePlaceholderString()')
                    // (eventually, the '$formVars' array should use the MySQL field names as names for its array keys)
                    $formVars = buildFormVarsArray($row);
                    // function 'buildFormVarsArray()' is defined in 'include.inc.php'
                    // ignore initials in author names:
                    $row[$i] = parsePlaceholderString($formVars, "<:authors[0||]:>", "");
                    // function 'parsePlaceholderString()' is defined in 'include.inc.php'
                }
                $recordIdentifier .= $row[$i];
                // merge all field values to form a unique record identifier string
            }
            // Normalize record identifier string:
            if ($ignoreWhitespace == "1") {
                // ignore whitespace
                $recordIdentifier = preg_replace("/\\s+/", "", $recordIdentifier);
            }
            if ($ignorePunctuation == "1") {
                // ignore punctuation
                $recordIdentifier = preg_replace("/[{$punct}]+/{$patternModifiers}", "", $recordIdentifier);
            }
            if ($ignoreCharacterCase == "1") {
                // ignore character case
                $recordIdentifier = strtolower($recordIdentifier);
            }
            if ($nonASCIIChars == "strip") {
                // strip non-ASCII characters
                $recordIdentifier = handleNonASCIIAndUnwantedCharacters($recordIdentifier, "\\S\\s", "strip");
            } elseif ($nonASCIIChars == "transliterate") {
                // transliterate non-ASCII characters
                $recordIdentifier = handleNonASCIIAndUnwantedCharacters($recordIdentifier, "\\S\\s", "transliterate");
            }
            // Check whether the record identifier string has occurred already:
            if (isset($recordSerialsArray[$recordIdentifier])) {
                // this record identifier string has already been seen
                $recordSerialsArray[$recordIdentifier][] = $row["serial"];
            } else {
                // new record identifier string
                $recordSerialsArray[$recordIdentifier] = array($row["serial"]);
            }
            // add a new array element for this record's identifier string (and store its serial number as value within a sub-array)
        }
        // Collect all array elements from '$recordSerialsArray' where their sub-array contains more than one serial number:
        foreach ($recordSerialsArray as $recordSerials) {
            if (count($recordSerials) > 1) {
                foreach ($recordSerials as $recordSerial) {
                    $duplicateRecordSerialsArray[] = $recordSerial;
                }
            }
            // add this record's serial number to the array of duplicate record serials
        }
    } else {
        // TODO!
    }
    if (empty($duplicateRecordSerialsArray)) {
        $duplicateRecordSerialsArray[] = "0";
    }
    // if no duplicate records were found, the non-existing serial number '0' will result in a "nothing found" feedback
    // CONSTRUCT SQL QUERY (2. DUPLICATES DISPLAY):
    // To display any duplicates that were found within the results of the original query, we build again a new query based on the original SQL query:
    $query = $sqlQuery;
    // Replace WHERE clause:
    // TODO: maybe make this into a generic function? (compare with function 'extractWHEREclause()' in 'include.inc.php')
    $duplicateRecordSerialsString = implode("|", $duplicateRecordSerialsArray);
    $query = preg_replace("/(?<=WHERE )(.+?)(?= ORDER BY| LIMIT| GROUP BY| HAVING| PROCEDURE| FOR UPDATE| LOCK IN|[ ;]+(SELECT|INSERT|UPDATE|DELETE|CREATE|ALTER|DROP|FILE)\\b|\$)/i", "serial RLIKE \"^(" . $duplicateRecordSerialsString . ")\$\"", $query);
    // Replace any existing ORDER BY clause with the list of columns given in '$selectedFieldsArray':
    $query = newORDERclause("ORDER BY " . $selectedFieldsString, $query, false);
    return array($query, $displayType);
}
Ejemplo n.º 2
0
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);
}