<?php
/*******************************************************************************
 *
 * LEIDEN OPEN VARIATION DATABASE (LOVD)
 *
 * Created     : 2009-10-21
 * Modified    : 2024-05-07
 * For LOVD    : 3.0-30
 *
 * Copyright   : 2004-2024 Leiden University Medical Center; http://www.LUMC.nl/
 * Programmers : Ivo F.A.C. Fokkema <I.F.A.C.Fokkema@LUMC.nl>
 *               Ivar C. Lugtenburg <I.C.Lugtenburg@LUMC.nl>
 *               Daan Asscheman <D.Asscheman@LUMC.nl>
 *               M. Kroon <m.kroon@lumc.nl>
 *               Anthony Marty <anthony.marty@unimelb.edu.au>
 *
 *
 * This file is part of LOVD.
 *
 * LOVD is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * LOVD is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with LOVD.  If not, see <http://www.gnu.org/licenses/>.
 *
 *************/

// Don't allow direct access.
if (!defined('ROOT_PATH')) {
    exit;
}


class LOVD_Object
{
    // This class is the base class which is inherited by all other object classes.
    // It provides functionality for setting up forms and for showing, checking, inserting, updating and deleting data.
    var $sObject = '';
    var $sTable = '';
    var $aFormData = array();
    var $aCheckMandatory = array();
    var $sSQLPreLoadEntry = '';     // Query to be executed before $sSQLLoadEntry (as preparation)
    var $sSQLLoadEntry = '';
    var $sSQLPreViewEntry = '';     // Query to be executed before $aSQLViewEntry (as preparation)
    var $aSQLViewEntry =
             array(
                    'SELECT' => '',
                    'FROM' => '',
                    'WHERE' => '',
                    'GROUP_BY' => '',
                  );
    var $aSQLViewList =
             array(
                    'SELECT' => '',
                    'FROM' => '',
                    'WHERE' => '',
                    'GROUP_BY' => '',
                    'HAVING' => '',
                    'ORDER_BY' => '',
                    'LIMIT' => '',
                  );
    var $aColumnsViewEntry = array();
    var $aColumnsViewList = array();
    var $sSortDefault = '';
    var $nID = 0;
    var $sRowID = ''; // FIXME; needs getter and setter?
    var $sRowLink = ''; // FIXME; needs getter and setter?
    protected $bEntryExists = NULL;





    function __construct()
    {
        // Default constructor.
        if (!$this->sTable) {
            $this->sTable = 'TABLE_' . strtoupper($this->sObject) . 'S';
        }
        if (!defined($this->sTable)) {
            lovd_displayError('ObjectError', 'Object::' . $this->sObject . ' requested with non-existing table \'' . $this->sTable . '\'.');
        }

        // Default query.
        if (!$this->aSQLViewList['SELECT']) { $this->aSQLViewList['SELECT'] = '*'; }
        if (!$this->aSQLViewList['FROM']) { $this->aSQLViewList['FROM'] = constant($this->sTable); }

        // Set default row ID and link for viewList().
//        $this->sRowID = strtolower($this->sObject) . '_{{ID}}';
        $this->sRowID = '{{ID}}'; // FIXME; having the object in front of it seems better, but then we need to isolate the ID using JS if we need it.
        // Default link example: users/00001.
        $this->sRowLink = strtolower($this->sObject) . 's/{{ID}}';
    }





    public function applyColumnFindAndReplace ($sFRViewlistCol, $sFRSearchValue, $sFRReplaceValue,
                                               $aArgs, $aOptions) {
        // Perform a find and replace action for given viewlist column. Return
        // false if anything fails.
        global $_DB, $_AUTH;

        // Column should be configured to allow Find & Replace.
        if (empty($this->aColumnsViewList[$sFRViewlistCol]['allow_find_replace'])) {
            lovd_displayError('FindAndReplace', 'Find and Replace requested on field "' .
                $sFRViewlistCol . '", which does not have that feature enabled.');
        }

        // FIXME: This check should be done earlier, not just when running it.
        // Check user authorization needed to perform find and replace action.
        // FIXME: check if authorization level is correctly set for viewlist data.
        if (!$_AUTH || $_AUTH['level'] < LEVEL_CURATOR) {
            $sErr = 'You do not have authorization to perform this action.';
            lovd_displayError('FindAndReplace', $sErr);
            return false;
        }

        // Determine field name from column definitions.
        list($sFieldname, $sTablename, $sTableRef) = $this->getFieldInfo($sFRViewlistCol);

        if (is_null($sTablename)) {
            // No table is defined for this object, it is unclear what table to
            // perform find and replace on.
            $sErr = 'Cannot run update query for object with unknown table (object=' .
                get_class($this) . ', fieldname=' . $sFRViewlistCol . ').';
            lovd_displayError('FindAndReplace', $sErr);
            return false;
        }

        // Name of viewlist query to run as subquery.
        $sSubqueryAlias = 'subq';

        // Get replace statement to use inside subquery. This is needed to
        // analyse the changed values with checkFields() later on.
        $sReplaceStmtInsideSubq = $this->generateViewListFRReplaceStatement(
            $sTableRef,
            $sFieldname,
            $sFRSearchValue,
            $sFRReplaceValue,
            $aOptions
        );

        // Construct find & replace search condition to select only records
        // that match the find & replace search options.
        $sFRSearchCondition = $this->generateFRSearchCondition(
            $sFRSearchValue,
            $sSubqueryAlias,
            $sFieldname,
            $aOptions
        );

        // Run checkFields() on all changed records.
        list($bSuccess,) = $this->checkFieldFRResult(
            $sFieldname,
            $sTablename,
            $sTableRef,
            $sFRSearchCondition,
            $aArgs,
            $sReplaceStmtInsideSubq
        );
        if (!$bSuccess) {
            return false;
        }

        // Construct query to use for updating relevant records, without ORDER
        // BY and LIMIT clauses.
        $sSelectSQL = $this->buildSQL(array(
            'SELECT' => $this->aSQLViewList['SELECT'],
            'FROM' => $this->aSQLViewList['FROM'],
            'WHERE' => $this->aSQLViewList['WHERE'],
            'GROUP_BY' => $this->aSQLViewList['GROUP_BY'],
            'HAVING' => $this->aSQLViewList['HAVING'],
        ));

        // ID field to connect rows from the original viewlist select query with rows in the
        // update query.
        // Note: this is hard-coded for now, meaning that each table must have this as its
        // ID field and each viewlist select query must include it. A more involved approach
        // would be to get the primary key in a separate query and include that in both the
        // update query and select subquery.
        $sIDField = $sSubqueryIDField = 'id';
        if (!is_null($sTableRef)) {
            $sSubqueryIDField = $sTableRef . 'id';
        }

        // Generate replacement statement for outside the subquery.
        $sReplaceStmtOutsideSubq = $this->generateViewListFRReplaceStatement(
            $sSubqueryAlias,
            $sFieldname,
            $sFRSearchValue,
            $sFRReplaceValue,
            $aOptions
        );

        // Construct and apply update query.
        $sUpdateSQL = 'UPDATE ' . $sTablename .  ', (' . $sSelectSQL . ') AS ' .
            $sSubqueryAlias . ' SET ' . $sTablename . '.`' . $sFieldname .
            '` = ' . $sReplaceStmtOutsideSubq . ', ' . $sTablename . '.edited_by = ?, ' .
            $sTablename . '.edited_date = ? WHERE ' . $sFRSearchCondition . ' AND ' .
            $sTablename . '.' . $sIDField . ' = ' . $sSubqueryAlias . '.' . $sSubqueryIDField;

        if ($sTablename == TABLE_VARIANTS_ON_TRANSCRIPTS) {
            // Update edited_by/-date fields of variant on genome table if query changes values on
            // variant on transcript.
            $sUpdateSQL = '
                UPDATE ' . TABLE_VARIANTS .  ' vog
                  INNER JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' vot ON (vog.id = vot.id),
                  (' . $sSelectSQL . ') AS ' . $sSubqueryAlias . '
                SET vot.`' . $sFieldname . '` = ' . $sReplaceStmtOutsideSubq . ',
                  vog.edited_by = ?, vog.edited_date = ?
                WHERE ' . $sFRSearchCondition . '
                  AND vot.' . $sIDField . ' = ' . $sSubqueryAlias . '.' . $sSubqueryIDField . '
                  AND vot.transcriptid = '  . $sSubqueryAlias . '.transcriptid';
        }

        // Add edit fields to SQL arguments.
        $aArgs[] = $_AUTH['id'];
        $aArgs[] = date('Y-m-d H:i:s');

        $q = $_DB->q($sUpdateSQL, $aArgs);
        $bSuccess = (bool) $q;
        if ($bSuccess) {
            // Create a log entry, too.
            $n = $q->rowCount();
            // FIXME: Add VL description? Add other filters that have been used?
            lovd_writeLog('Event', 'FindAndReplace', 'Find and Replace successfully run on ' . $sFRViewlistCol . ', replacing "' . $sFRSearchValue . '" (matching ' . ($aOptions['sFRMatchType'] == 1? 'anywhere' : 'at the ' . ($aOptions['sFRMatchType'] == 2? 'beginning' : 'end')) . ') with "' . $sFRReplaceValue . '"' . (empty($aOptions['bFRReplaceAll'])? '' : ', overwriting the entire field'). ', updating ' . $n . ' row' . ($n == 1? '' : 's') . '.' . ($sTablename != TABLE_VARIANTS_ON_TRANSCRIPTS? '' : ' Note: the number of updated rows may be an overcalculation since variant data is stored in multiple rows.'));
        }
        return $bSuccess;
    }





    function autoExplode ($zData)
    {
        // Automatically explode GROUP_CONCAT values based on their name.
        foreach ($zData as $sKey => $sVal) {
            if ($sKey[0] == '_') {
                unset($zData[$sKey]);
                if (!empty($sVal)) {
                    if ($sKey[1] == '_') {
                        // Explode GROUP_CONCAT nested array
                        $aValues = explode(';;', $sVal);
                        $zData[ltrim($sKey, '_')] = array_map('explode', array_fill(0, count($aValues), ';'), $aValues);
                    } else {
                        // Explode GROUP_CONCAT array
                        $zData[ltrim($sKey, '_')] = explode(';', $sVal);
                    }
                } else {
                    $zData[ltrim($sKey, '_')] = array();
                }
            }
        }
        return $zData;
    }





    function buildSQL ($aSQL)
    {
        // Takes an $aSQL as commonly used in LOVD objects, and turns it into a normal SQL string.
        $sSQLOut = '';
        foreach ($aSQL as $sClause => $sValue) {
            if ($sValue !== '') {
                $sSQLOut .= (!$sSQLOut? '' : ' ') . str_replace('_', ' ', $sClause) . ' ' . $sValue;
            }
        }
        return $sSQLOut;
    }





    protected function checkFieldFRResult ($sFieldname, $sTablename, $sTableRef,
                                           $sFRSearchCondition, $aArgs, $sReplaceStmt)
    {
        global $_DB, $_ERROR;

        // Generate and execute query to get records that are affected by the
        // find & replace action, in order to run checkFields() on them. Note,
        // there is no ORDER BY in this query, the errors reported may have a
        // different order from how they are present in the shown viewlist.
        $sPlaceholderName = 'FRFieldResult';
        $sSelectSQL = $this->buildSQL(array(
            'SELECT' => $this->aSQLViewList['SELECT'] . ', ' . $sReplaceStmt . ' AS `' .
                $sPlaceholderName . '`',
            'FROM' => $this->aSQLViewList['FROM'],
            'WHERE' => $this->aSQLViewList['WHERE'],
            'GROUP_BY' => $this->aSQLViewList['GROUP_BY'],
            'HAVING' => $this->aSQLViewList['HAVING'],
        ));
        $sSubqueryID = 'subq.' . $sTableRef . 'id';

        $oQuery = 'SELECT tab.*, `' . $sPlaceholderName . '` FROM (' . $sSelectSQL .
            ') AS subq JOIN ' . $sTablename . ' AS tab ON (tab.id = ' . $sSubqueryID . ') WHERE ' .
            $sFRSearchCondition;
        if ($sTablename == TABLE_VARIANTS_ON_TRANSCRIPTS) {
            // Select by variant ID and transcript ID for VOT records.
            $oQuery = 'SELECT tab.*, `' . $sPlaceholderName . '` FROM (' . $sSelectSQL .
                ') AS subq JOIN ' . $sTablename . ' AS tab ON (tab.id = ' . $sSubqueryID .
                ' AND tab.transcriptid = subq.transcriptid) WHERE ' .
                $sFRSearchCondition;
        }
        $oResult = $_DB->q($oQuery, $aArgs);
        require_once ROOT_PATH . 'inc-lib-form.php';

        // Determine LOVD object to which F&R column belongs.
        $object = $this;
        if ($this->sObject == 'Custom_ViewList') {
            // Determine class based on prefix of custom column name.
            $sCategory = $this->getCategoryFromCustomColName($sFieldname);
            switch ($sCategory) {
                case 'VariantOnGenome':
                    require_once ROOT_PATH . 'class/object_genome_variants.php';
                    $object = new LOVD_GenomeVariant();
                    break;
                case 'VariantOnTranscript':
                    require_once ROOT_PATH . 'class/object_transcript_variants.php';
                    // Create gene-specific VOT object with a single transcript.
                    // VLs with VOT columns that allow F&R *must* be Custom VLs with a gene ID!
                    // Otherwise, the data can not be checked properly.
                    $object = new LOVD_TranscriptVariant($this->nID, '', false);
                    break;
                case 'Screening':
                    require_once ROOT_PATH . 'class/object_screenings.php';
                    $object = new LOVD_Screening();
                    break;
                case 'Individual':
                    require_once ROOT_PATH . 'class/object_individuals.php';
                    $object = new LOVD_Individual();
                    break;
            }
        }

        $aForm = $object->getForm();
        if (!isset($aForm[$sFieldname])) {
            // Given field is not part of the object's form, therefore
            // checkFields() will ignore it and data could get corrupted. Return
            // false to disallow this.
            lovd_errorAdd($sFieldname, 'LOVD is unable to verify contents of field ' .
                $sFieldname . ', please contact the administrator.');
            return array(false, 0);
        }

        // Run checkFields() on records with find & replace action applied.
        // Other fields in the table are left unchanged.
        $aCFOptions = array(
            'trim_fields' => false,
            'mandatory_password' => false);
        $nAffectedRows = 0;
        while (($aData = $oResult->fetchAssoc()) !== false) {
            $aData[$sFieldname] = $aData[$sPlaceholderName];
            $object->checkFields($aData, false, $aCFOptions);
            $nAffectedRows++;
        }

        if (lovd_error()) {
            // Remove errors relating to fields other than on which Find &
            // Replace is performed on.
            foreach ($_ERROR['fields'] as $i => $sField) {
                if ($sField != $sFieldname) {
                    unset($_ERROR['fields'][$i], $_ERROR['messages'][$i]);
                }
            }
        }

        return array(!lovd_error(), $nAffectedRows);
    }





    function checkFields ($aData, $zData = false, $aOptions = array())
    {
        // Checks fields before submission of data.
        global $_AUTH, $_SETT;

        // Prepare default options.
        if (empty($aOptions) || !is_array($aOptions)) {
            $aOptions = array();
        }
        $aOptions = array_replace(
            array(
                'mandatory_password' => true,   // Ensure password field is mandatory.
                'fieldname_as_header' => false, // Use field name as header.
                'trim_fields' => true,          // Trim all whitespace from fields in data array
                                                // and input (e.g. $_POST or $_GET)
                'explode_strings' => false,     // Do multiple-selection lists need to be converted to arrays manually?
                'show_select_alts' => false,    // Show alternatives in errors for select fields.
            ),
            $aOptions);

        $aForm = $this->getForm();
        $aFormInfo = array();
        if ($aForm) {
            $aFormInfo = $aForm[0];
            if (!in_array($aFormInfo[0], array('GET', 'POST'))) {
                // We're not working on a full form array, possibly an incomplete VOT form.
                $aFormInfo = array('POST');
            } else {
                unset($aForm[0]);
            }
        } else {
            // No form information available.
            $aForm = array();
        }

        if ($aOptions['mandatory_password']) {
            // Password is not mandatory for importing or F&R, check only when mandatory.
            $this->aCheckMandatory[] = 'password';
        }

        $aHeaders = array();

        // Array to store fieldnames as keys for fields where an error is
        // reported in this checkfields() call. Note that this is different
        // from $_ERROR['fields'] which contains fieldnames for multiple
        // records during import, not just the current checkfields() call.
        $aErroredFields = array();

        // Validate form by looking at the form itself, and check what's needed.
        foreach ($aForm as $aField) {
            if (!is_array($aField)) {
                // 'skip', 'hr', etc...
                continue;
            }
            @list($sHeader, $sHelp, $sType, $sName) = $aField;
            if ($aOptions['fieldname_as_header']) {
                // During import, we don't mention the field names how they appear on screen, but using their IDs which are used in the file.
                $sHeader = $sName;
            }
            $aHeaders[$sName] = $sHeader;

            // Trim() all fields. We don't want those spaces in the database anyway.
            if ($aOptions['trim_fields'] && isset($aData[$sName]) && !is_array($aData[$sName])) {
                $GLOBALS['_' . $aFormInfo[0]][$sName] = trim($GLOBALS['_' . $aFormInfo[0]][$sName]);
                $aData[$sName] = trim($aData[$sName]);
            }

            // Mandatory fields, as defined by child object.
            // Empty multiple selection fields are not sent by the browser, but they are by the quick curation code,
            //  so we have to check for an empty array() as well. Just make sure we don't complain about '0'.
            if (in_array($sName, $this->aCheckMandatory) && (!isset($aData[$sName]) || $aData[$sName] === '' || $aData[$sName] === array())) {
                lovd_errorAdd($sName, 'Please fill in the \'' . $sHeader . '\' field.');
                $aErroredFields[$sName] = true;
            }

            if ($sType == 'select') {
                if (!empty($aField[7])) {
                    // The browser fails to send value if selection list w/ multiple selection options is left empty.
                    // This is causing notices in the code.
                    if (!isset($aData[$sName])) {
                        $GLOBALS['_' . $aFormInfo[0]][$sName] = array();
                        $aData[$sName] = array();
                    }
                }

                if ($aOptions['explode_strings'] && !is_array($aData[$sName])
                    && isset($GLOBALS['aLine'][$sName]) && $GLOBALS['aLine'][$sName] == $aData[$sName]) {
                    // Selection lists imported with spaces around their values
                    //  (e.g., "PCR; SEQ") will be checked *without* the space, but
                    //  imported *with* the space. Fix that.
                    // Selection values can't contain an ";", so this is safe.
                    $GLOBALS['aLine'][$sName] = $aData[$sName] =
                        implode(';', array_map('trim', explode(';', $aData[$sName])));
                }

                // Simple check on non-custom columns (custom columns have
                //  checkSelectedInput() for this) to see if the given value is
                //  actually allowed. "0" is a valid entry for the check for
                //  mandatory fields, so we should also check if "0" is a valid
                //  entry in the selection list!
                if (strpos($sName, '/') === false && isset($aData[$sName]) && $aData[$sName] !== '') {
                    $Val = $aData[$sName];
                    $aSelectOptions = array_keys($aField[5]);
                    if ($aOptions['explode_strings'] && !is_array($Val)) {
                        $Val = explode(';', $Val); // Normally the form sends an array, but from the import I need to create an array.
                    } elseif (!is_array($Val)) {
                        $Val = array($Val);
                    }
                    foreach ($Val as $sValue) {
                        $sValue = trim($sValue); // Trim whitespace from $sValue to ensure match independent of whitespace.
                        if (!in_array($sValue, $aSelectOptions)) {
                            if (!empty($aOptions['show_select_alts'])) {
                                lovd_errorAdd($sName, 'Please select a valid entry from the \'' . $sHeader . '\' selection box, \'' . strip_tags($sValue) . '\' is not a valid value. Please choose from these options: \'' . implode('\', \'', $aSelectOptions) . '\'.');
                                $aErroredFields[$sName] = true;
                            } else {
                                lovd_errorAdd($sName, 'Please select a valid entry from the \'' . $sHeader . '\' selection box, \'' . strip_tags($sValue) . '\' is not a valid value.');
                                $aErroredFields[$sName] = true;
                            }
                        }
                    }
                }

            } elseif ($sType == 'checkbox') {
                // The browser fails to send value if checkbox is left empty.
                // This is causing problems sometimes with MySQL, since INT
                // columns can't receive an empty string if STRICT is on.
                if (!isset($aData[$sName])) {
                    $GLOBALS['_' . $aFormInfo[0]][$sName] = 0;
                    $aData[$sName] = 0;
                } elseif (!in_array($aData[$sName], array('0', '1'))) {
                    lovd_errorAdd($sName, 'The field \'' . $sHeader . '\' must contain either a \'0\' or a \'1\'.');
                    $aErroredFields[$sName] = true;
                }
            }

            if ($sName == 'password') {
                // Password is in the form, it must be checked. Assuming here that it is also considered mandatory.
                if (!empty($aData['password']) && !lovd_verifyPassword($aData['password'], $_AUTH['password'])) {
                    lovd_errorAdd('password', 'Please enter your correct password for authorization.');
                    $aErroredFields[$sName] = true;
                }
            }
        }

        // Check all fields that we receive for data type and maximum length.
        // No longer do this through $aForm, because when importing,
        //  we do have data to check but no $aForm entry linked to it.
        foreach ($aData as $sFieldname => $sFieldvalue) {
            if (isset($aErroredFields[$sFieldname])) {
                // Do not process fields for which an (more specific) error has already been reported earlier.
                continue;
            }

            $sNameClean = preg_replace('/^\d{' . $_SETT['objectid_length']['transcripts'] . '}_/', '', $sFieldname); // Remove prefix (transcriptid) that LOVD_TranscriptVariants puts there.
            if (isset($aHeaders[$sFieldname])) {
                $sHeader = $aHeaders[$sFieldname];
            } else {
                $sHeader = $sFieldname;
            }

            // Checking free text fields for max length, data types, etc.
            if ($sMySQLType = lovd_getColumnType(constant($this->sTable), $sNameClean)) {
                // The rest of the checks here all assume that $sFieldvalue is not an array. Make sure it isn't.
                if (is_array($sFieldvalue)) {
                    $sFieldvalue = implode(';', $sFieldvalue);
                }

                // Check max length.
                $nMaxLength = lovd_getColumnLength(constant($this->sTable), $sNameClean);
                if (!empty($sFieldvalue)) {
                    // For numerical columns, maxlength works differently!
                    if (in_array($sMySQLType, array('FLOAT', 'FLOAT_UNSIGNED'))) {
                        // Floats don't have a real min/max value. We'll check
                        //  if they contain valid values further below.

                    } elseif (in_array($sMySQLType, array('DECIMAL', 'DECIMAL_UNSIGNED', 'INT', 'INT_UNSIGNED'))) {
                        // Although the data type wizard makes sure that a
                        //  numeric field allowing 3 characters can actually
                        //  contain 999, somebody could still override this and
                        //  create a TINYINT(3) UNSIGNED column.
                        // lovd_getColumnLength() then considers 999 to be the
                        //  maximum value, which isn't true. We actually define
                        //  a MEDIUMINT(8) ourselves, which needs proper checks.
                        // lovd_getColumnMinMax() does a much better job.
                        list($nMin, $nMax) = lovd_getColumnMinMax(constant($this->sTable), $sNameClean);
                        if ((float) $sFieldvalue < $nMin) {
                            lovd_errorAdd($sFieldname, 'The \'' . $sHeader . '\' field is limited to numbers no lower than ' . $nMin . '.');
                        } elseif ((float) $sFieldvalue > $nMax
                            && !in_array($sNameClean, array('id', 'individualid', 'screeningid'))) {
                            // Uhm, yeah, but remember that when we're importing new data, we're OK with IDs that are too
                            //  high, because the import will change them. We don't necessarily need to check if we're
                            //  currently importing, we can just see the field names.
                            // (this mostly applies to LOVD+, but is also true for LOVD)
                            lovd_errorAdd($sFieldname, 'The \'' . $sHeader . '\' field is limited to numbers no higher than ' . $nMax . '.');
                        }

                    } elseif (strlen($sFieldvalue) > $nMaxLength) {
                        lovd_errorAdd($sFieldname, 'The \'' . $sHeader . '\' field is limited to ' . $nMaxLength . ' characters, you entered ' . strlen($sFieldvalue) . '.');
                    }
                }

                // Check data type.
                if (!empty($sFieldvalue)) {
                    switch ($sMySQLType) {
                        case 'DATE':
                            if (!lovd_matchDate($sFieldvalue)) {
                                lovd_errorAdd($sFieldname, 'The field \'' . $sHeader . '\' must contain a valid date in the format YYYY-MM-DD, "' . htmlspecialchars($sFieldvalue) . '" does not match.');
                            }
                            break;
                        case 'DATETIME':
                            if (!preg_match('/^[0-9]{4}[.\/-][0-9]{2}[.\/-][0-9]{2}( [0-9]{2}\:[0-9]{2}\:[0-9]{2})?$/', $sFieldvalue)) {
                                lovd_errorAdd($sFieldname, 'The field \'' . $sHeader . '\' must contain a date, possibly including a time, in the format YYYY-MM-DD HH:MM:SS, "' . htmlspecialchars($sFieldvalue) . '" does not match.');
                            }
                            break;
                        case 'DECIMAL':
                        case 'DECIMAL_UNSIGNED':
                        case 'FLOAT':
                        case 'FLOAT_UNSIGNED':
                            if (!is_numeric($sFieldvalue) || (substr($sMySQLType, -8) == 'UNSIGNED' && $sFieldvalue < 0)) {
                                lovd_errorAdd($sFieldname, 'The field \'' . $sHeader . '\' must contain a' . (substr($sMySQLType, -8) != 'UNSIGNED' ? '' : ' positive') . ' number, "' . htmlspecialchars($sFieldvalue) . '" does not match.');
                            }
                            break;
                        case 'INT':
                        case 'INT_UNSIGNED':
                            if (!preg_match('/^' . ($sMySQLType != 'INT' ? '' : '\-?') . '[0-9]*$/', $sFieldvalue)) {
                                lovd_errorAdd($sFieldname, 'The field \'' . $sHeader . '\' must contain a' . ($sMySQLType == 'INT' ? 'n' : ' positive') . ' integer, "' . htmlspecialchars($sFieldvalue) . '" does not match.');
                            }
                            break;
                    }
                }
            }
        }
        return $aData;
    }





    function deleteEntry ($ID, $sReason = '-')
    {
        // Deletes an entry from the database, handles revisions as well.
        // $ID = Can be an integer/numeric string, or an array. If an integer/numeric string: ID to change.
        //   If an associative array (for linking tables), use array('geneid' => 'IVD', 'userid' => 1).
        // $sReason = The reason the entry as deleted, as given by the user.
        global $_DB, $_AUTH;

        // Check to see if an ID has been passed and there is data to process.
        if ((!is_array($ID) && !trim($ID)) || (is_array($ID) && (!$ID || in_array('', array_map('trim', $ID))))) {
            lovd_displayError('LOVD-Lib', 'Objects::(' . $this->sObject . ')::deleteEntry() - Method didn\'t receive ID');
        }

        // Prepare ID variable, to always be in the array('id' => 1) format.
        $aIDs = $ID;
        if (!is_array($ID)) {
            $aIDs = array('id' => $ID);
        }

        // Check to see if this table uses revisions.
        $bRev = defined($this->sTable . '_REV');
        // Check if maybe we already were in a transaction when we started this function.
        // If so, we won't create a new transaction, nor run a commit() in the end.
        $bWasAlreadyInTransaction = (method_exists($_DB, 'inTransaction') && $_DB->inTransaction());
        if ($bRev) {
            if (!$bWasAlreadyInTransaction) {
                $_DB->beginTransaction();
            }
        }

        $sSQL = 'DELETE FROM ' . constant($this->sTable) . ' WHERE ' . implode(' = ? AND ', array_keys($aIDs)) . ' = ?';
        if (!defined('LOG_EVENT')) {
            define('LOG_EVENT', $this->sObject . '::deleteEntry()');
        }
        $q = $_DB->q($sSQL, array_values($aIDs));

        // Setup and run the revision table SQL.
        if ($bRev) {
            $sReason = 'Record deleted: ' . $sReason;
            // Update the existing revision record.
            // We could just check for a valid_to of 9999-12-31, but this
            //  is more flexible if we choose a different format.
            $qRevisionUpdate = $_DB->q(
                'UPDATE ' . constant($this->sTable . '_REV') . '
                 SET valid_to = NOW(), deleted = 1, deleted_by = ?, reason = CONCAT(reason, "\r\n", ?)
                 WHERE ' . implode(' = ? AND ', array_keys($aIDs)) . ' = ? AND valid_to >= NOW()
                 ORDER BY valid_to DESC LIMIT 1',
                array_merge(array($_AUTH['id'], $sReason), array_values($aIDs)), true, true);
            // If any of the queries failed, then rollback and return false. Otherwise, commit.
            if (!$q || !$qRevisionUpdate) {
                $_DB->rollBack();
                return false;
            } else {
                if (!$bWasAlreadyInTransaction) {
                    $_DB->commit();
                }
            }
        }
        return $q->rowCount();
    }





    function describeFormType ($zData)
    {
        // Returns sensible form type information based on form type code.

        if (!is_array($zData) || empty($zData['form_type']) || substr_count($zData['form_type'], '|') < 2) {
            return false;
        }

        $aFormType = explode('|', $zData['form_type']);
        $sFormType = ucfirst($aFormType[2]);
        switch ($aFormType[2]) {
            case 'text':
            case 'password':
                $sFormType .= ' (' . $aFormType[3] . ' chars)';
                break;
            case 'textarea':
                $sFormType .= ' (' . $aFormType[3] . ' cols, ' . $aFormType[4] . ' rows)';
                break;
            case 'select':
                $nOptions = substr_count($zData['select_options'], "\r\n") + 1;
                if ($nOptions) {
                    $sFormType .= ' (' . ($aFormType[5] == 'true'? 'multiple; ' : '') . $nOptions . ' option' . ($nOptions == 1? '' : 's') . ')';
                }
                break;
        }

        return $sFormType;
    }





    function entryExist ($ID = false)
    {
        // Checks if any, or a specific, entry exists in the database table.
        // ViewEntry() and ViewList() call this function to see if data exists.
        // $ID = Can be an integer, string, or an array.
        // - If an integer/string: ID to check for.
        // - If an associative array (for linking tables), use
        //   array('geneid' => 'IVD', 'userid' => 1).
        global $_DB;

        if ($ID) {
            // Prepare ID variable, to always be in the array('id' => 1) format.
            $aIDs = $ID;
            if (!is_array($ID)) {
                $sIDColumn = 'id';
                // In case a different column name is used, better use that instead.
                if (!empty($this->aColumnsViewList['id']['db'][0])) {
                    $sIDColumn = $this->aColumnsViewList['id']['db'][0];
                    // But take the optional table alias off.
                    $sIDColumn = substr(strrchr('.' . $sIDColumn, '.'), 1);
                }
                $aIDs = array($sIDColumn => $ID);
            }

            $bEntryExists = (bool) $_DB->q('
                SELECT 1
                FROM ' . constant($this->sTable) . '
                WHERE ' . implode(' = ? AND ', array_keys($aIDs)) . ' = ? LIMIT 1',
                array_values($aIDs))->fetchColumn();
            return $bEntryExists;
        } else {
            if (isset($this->bEntryExists)) {
                return $this->bEntryExists;
            }
            $bEntryExists = (bool) $_DB->q('
                SELECT 1
                FROM ' . constant($this->sTable) . ' LIMIT 1')->fetchColumn();
            $this->bEntryExists = $bEntryExists;
            return $this->bEntryExists;
        }
    }





    function formatArrayToTable ($aData, $nNesting = 0)
    {
        // Formats an Array to an data table, used for JSON data to be formatted in an VE.

        if (!is_array($aData) || !$aData) {
            return '(no data)';
        }

        // Handle simple setting arrays differently.
        // Added SORT_REGULAR to prevent array_unique() to transform nested
        //  arrays to string to make the comparison (as it does by default).
        //  Adding SORT_REGULAR makes it actually compare the arrays instead of
        //  comparing "Array" and then taking the first only.
        if (!$nNesting && current(array_unique(array_values($aData), SORT_REGULAR)) == 1) {
            // This is a simple array with just settings that are turned on.
            return implode('<BR>', array_map(function ($sKey) {
                return ucfirst(str_replace('_', ' ', $sKey)) . ' <IMG src="gfx/mark_1.png" alt="" width="11" height="11">';
            }, array_keys($aData)));
        }

        $s = '<TABLE class="S11" width="100%">';
        foreach ($aData as $sKey => $Value) {
            // We will handle simple values as if it's an array.
            if (!is_array($Value)) {
                $Value = array($Value);
            }

            if ($Value === array()) {
                // We won't print a key with an empty array.
                continue;
            }

            if (!$nNesting) {
                $s .= '<TR><TH>' . $sKey . '</TH></TR><TR><TD>';
            } elseif ($nNesting == 1) {
                $s .= '<TR><TD><B>' . $sKey . '</B></TD></TR><TR><TD>';
            } else {
                $s .= '<TR><TD valign="top" style="border-top: 1px solid #AFC8FA;">' . $sKey .
                      '</TD><TD style="border-top: 1px solid #AFC8FA;"><TABLE class="S11" cellpadding="0" cellspacing="0"><TR><TD>';
            }

            // Can be array of values, in which case they are printed, one value on each line.
            // Can be an associative array; for nested arrays we print the key in an TR and loop through the values,
            //  normal key => value pairs are printed in separate TDs.

            // Measure keys.
            $bList = (count(array_filter(array_keys($Value), 'is_int')) == count($Value));
            $bContainsArrays = (count(array_filter($Value, 'is_array')) > 0);
            if ($bList && !$bContainsArrays) {
                // Simple list.
                $s .= implode('</TD></TR><TR><TD style="border-top: 1px solid #AFC8FA;">', $Value);

            } else {
                $s .= $this->formatArrayToTable($Value, $nNesting + 1);
            }

            $s .= '</TD></TR>';
            if ($nNesting > 1) {
                $s .= '</TD></TR></TABLE>';
            }
        }
        $s .= '</TABLE>';

        return $s;
    }





    private function generateFRSearchCondition ($sFRSearchValue, $sTablename, $sFieldname,
                                                $aOptions)
    {
        // Return an SQL search condition for given search string, field name and match options.
        // Default is to match the search string anywhere in the field.
        global $_DB;

        // FIXME: Search value (and fieldname, unless we're sure it can't be
        //  tainted) should never be used directly in the query.
        // Putting it in an arguments array however, will mean redesign of much of the code.
        // $_DB->quote() isn't perfect and should be avoided, but if we don't
        //  want to redo this part again, we need this for now.
        // NOTE: We can't $_DB->quote() the thing here, because that makes the code weird
        //  (it adds quotes around the value) and it makes strlen() fail for quoted strings.

        $sCompositeFieldname = (!$sTablename? '' : $sTablename . '.') . '`' . $sFieldname . '`';
        if ($sFRSearchValue == '') {
            // When searching an empty string, match NULL field as well.
            $sCompositeFieldname = 'CAST(IFNULL(' . $sCompositeFieldname . ', "") AS CHAR)';
        }
        if ($sFRSearchValue == '' && (isset($aOptions['sFRMatchType']) && $aOptions['sFRMatchType'] == '1')) {
            // Searching for nothing anywhere in the field, means field must be empty.
            $sFRSearchCondition = $sCompositeFieldname . ' = ""';
        } elseif (isset($aOptions['sFRMatchType']) && $aOptions['sFRMatchType'] == '2') {
            // Match search string at beginning of field.
            $sFRSearchCondition = 'SUBSTRING(' . $sCompositeFieldname . ', 1, ' .
                                  strlen($sFRSearchValue) . ') = ' . $_DB->quote($sFRSearchValue);
        } elseif (isset($aOptions['sFRMatchType']) && $aOptions['sFRMatchType'] == '3') {
            // Match search string at end of field.
            $sFRSearchCondition = 'SUBSTRING(' . $sCompositeFieldname . ', -' .
                                  strlen($sFRSearchValue) . ') = ' . $_DB->quote($sFRSearchValue);
        } else {
            // Throw the search term through lovd_escapeSearchTerm() to make sure special LIKE characters (%, _) are not interpreted by MySQL.
            // However, REPLACE(), doing the actual replacing, does not interpret these special characters, so there is no real danger.
            $sFRSearchCondition = $sCompositeFieldname . ' LIKE ' . $_DB->quote('%' . lovd_escapeSearchTerm($sFRSearchValue) . '%');
        }
        return $sFRSearchCondition;
    }





    function generateRowID ($zData = false)
    {
        // Generates the row_id for the viewList rows.
        if ($zData && is_array($zData) && !empty($zData)) {
            if (!isset($zData['row_id'])) {
                if (isset($zData['id'])) {
                    if ($this->sRowID !== '') {
                        $zData['row_id'] = str_replace('{{ID}}', rawurlencode($zData['id']), $this->sRowID);
                        foreach ($zData as $key => $val) {
                            $zData['row_id'] = preg_replace('/\{\{' . preg_quote($key, '/') . '\}\}/', rawurlencode($val ?: ''), $zData['row_id']);
                        }
                    } else {
                        $zData['row_id'] = $zData['id'];
                    }
                } else {
                    $zData['row_id'] = '';
                }
            }
        }
        return $zData;
    }





    private function generateViewListFRReplaceStatement ($sTablename, $sFieldname, $sFRSearchValue,
                                                         $sFRReplaceValue, $aOptions)
    {
        // Return a SQL REPLACE statement for given field name from the
        // viewlist select query. Note that the generated replace statement
        // does not work when the value of the field is transformed in the
        // SELECT clause.
        // FIXME: Allow for replacement of transformed fields in viewlist select query.
        // Params:
        // - $sTableName        Name of the table.
        // - $sFieldname        Name of the table's field on which replace will be called.
        // - $sFRSearchValue    Find & replace search value.
        // - $sFRReplaceValue   Find & replace replace value.
        // - $aOptions          Array with options on how to perform replace.
        global $_DB;

        $nSearchStrLen = strlen($sFRSearchValue);
        $sCompositeFieldname = (!$sTablename? '' : $sTablename . '.') . '`' . $sFieldname . '`';
        $sReplacement = $_DB->quote($sFRReplaceValue);
        $aOptions['sFRMatchType'] = (!isset($aOptions['sFRMatchType'])? 1 : $aOptions['sFRMatchType']);

        // FIXME: Search value and replace value (and fieldname, unless we're sure it can't be
        //  tainted) should never be used directly in the query.
        // Putting it in an arguments array however, will mean redesign of much of the code.
        // $_DB->quote() isn't perfect and should be avoided, but if we don't
        //  want to redo this part again, we need this for now.
        // NOTE: We can't $_DB->quote() the thing here, because that makes the code weird
        //  (it adds quotes around the value) and it makes strlen() fail for quoted strings.

        if ($sFRSearchValue == '' && $aOptions['sFRMatchType'] == '1') {
            // When searching on empty string anywhere, we can assume we're replacing the whole
            // field.
            $sReplacement = $_DB->quote($sFRReplaceValue);
        } elseif ($aOptions['sFRMatchType'] == '1' && empty($aOptions['bFRReplaceAll'])) {
            // Default is to replace occurrences anywhere in the field.
            return 'REPLACE(' . $sCompositeFieldname . ', ' . $_DB->quote($sFRSearchValue) . ', ' .
                $_DB->quote($sFRReplaceValue) . ')';
        } elseif (!empty($aOptions['bFRReplaceAll'])) {
            // Whole field is replaced with a single value.
            $sReplacement = $_DB->quote($sFRReplaceValue);
        } elseif ($aOptions['sFRMatchType'] == '2') {
            // Replace search string at beginning of field.
            // E.g.:
            // CASE WHEN SUBSTRING(table.`field`, 1, 6) = "search"
            // THEN CONCAT("replace", SUBSTRING(table.`field`, 6))
            // ELSE table.`field` END
            $sReplacement = 'CONCAT(' . $_DB->quote($sFRReplaceValue) . ', SUBSTRING(' .
                            $sCompositeFieldname . ', ' . strval($nSearchStrLen + 1) . '))';

        } elseif ($aOptions['sFRMatchType'] == '3') {
            // Replace search string at end of field.
            // E.g.:
            // CASE WHEN SUBSTRING(table.`field`, - 6) = "search"
            // THEN CONCAT(SUBSTRING(table.`field`, 1, CHAR_LENGTH(table.`field`) - 6), "replace")
            // ELSE table.`field` END
            $sReplacement = 'CONCAT(' . 'SUBSTRING(' . $sCompositeFieldname . ', 1, CHAR_LENGTH(' .
                            $sCompositeFieldname . ') - ' . strval($nSearchStrLen) . '), ' .
                            $_DB->quote($sFRReplaceValue) . ')';
        }

        $sFRSearchCondition = $this->generateFRSearchCondition($sFRSearchValue, $sTablename,
                                                               $sFieldname, $aOptions);

        // Return replace statement.
        return 'CASE WHEN ' . $sFRSearchCondition . ' THEN ' . $sReplacement . ' ELSE ' .
               $sCompositeFieldname . ' END';
    }





    private function getCategoryFromCustomColName ($sName)
    {
        // Returns category (object type) for custom column fieldname. Fieldname
        // may be anything used in code or SQL to refer to that column.
        // Examples:
        //      "Phenotype/Age" => "Phenotype"
        //      "vot.`VariantOnTranscript/DNA`" => "VariantOnTranscript"
        //      "`VariantOnTranscript/Enzyme/Kinase_activity`" =>
        //          "VariantOnTranscript"

        preg_match('/^(\w+\.)?`?(\w+)\/.+$/', $sName, $aMatches);
        if ($aMatches) {
            return $aMatches[2];
        }

        // Unable to parse name.
        return false;
    }





    private function getFieldInfo ($sViewListCol)
    {
        // Get information on viewlist column. Returns:
        // sFieldname   Name of field in database.
        // sTablename   Name of database table containing field.
        // sTableRef    Name of reference to table in viewlist query (e.g.
        //              alias). May be null if there's no explicit reference.

        if (!isset($this->aColumnsViewList[$sViewListCol])) {
            lovd_displayError('', 'Cannot find table and field name for undefined column "' .
                $sViewListCol . '".');
        }

        // Get database field name and table reference from 'db' specification
        // in aColumnsViewList.
        $sTableRef = null;
        $sFieldname = $this->aColumnsViewList[$sViewListCol]['db'][0];
        if (preg_match('/^([A-Za-z0-9_`]+)\.([A-Za-z0-9_`\/]+)$/', $sFieldname, $aRegs)) {
            $sTableRef = $aRegs[1];
            $sFieldname = $aRegs[2];
        }

        // Trim any backticks for standardization.
        $sFieldname = trim($sFieldname, '`');

        // Get tablename.
        // FIXME: Solve for non-custom columns in custom viewlists.
        $sTablename = null;
        if ($this instanceof LOVD_CustomViewList) {
            $sCat = $this->getCategoryFromCustomColName($sViewListCol);
            if ($sCat !== false) {
                $aTableInfo = lovd_getTableInfoByCategory($sCat);
                if ($aTableInfo !== false) {
                    $sTablename = $aTableInfo['table_sql'];
                }
            }
        } elseif (isset($this->sTable) && defined($this->sTable)) {
            $sTablename = constant($this->sTable);
        }

        return array($sFieldname, $sTablename, $sTableRef);
    }





    function getForm ()
    {
        // Returns the $this->aFormData variable, to build a form.
        return $this->aFormData;
    }





    public static function getObjectLinksHTML ($aIDs, $sURLFormat)
    {
        // Returns a list of object links in HTML format.
        // Parameter $aIDs is an array with object IDs, and optionally, values.
        // Parameter $sURLFormat designates the target of the links, where any
        //   sprintf() recognized format (like %s) will be substituted with the
        //   object ID, e.g. "genes/%s".
        // For more information on formats to use, see:
        //   http://php.net/manual/en/function.sprintf.php
        // FIXME: There's no real reason why this is in the object.

        $sShortDescription = '';
        $sHTMLoutput = '';
        $i = 0;
        foreach ($aIDs as $key => $val) {
            if (is_array($val)) {
                $sObjectID = $val[0];
                $sObjectValue = $val[1];
            } else {
                $sObjectID = $sObjectValue = $val;
            }

            $sHTMLoutput .= (!$key ? '' : ', ') . '<A href="' . sprintf($sURLFormat, $sObjectID) .
                '">' . $sObjectValue . '</A>';
            if ($i < 20) {
                $sShortDescription .= (!$key ? '' : ', ') . '<A href="' .
                    sprintf($sURLFormat, $sObjectID) . '">' . $sObjectValue . '</A>';
                $i++;
            }
        }
        if (count($aIDs) > 22) {
            // Replace long gene list by shorter one, allowing expand.
            $sHTMLoutput = '<SPAN>' . $sShortDescription . ', <A href="#" onclick="$(this).parent().hide(); $(this).parent().next().show(); return false;">' . (count($aIDs) - $i) . ' more...</A></SPAN><SPAN style="display : none;">' . $sHTMLoutput . '</SPAN>';
        }
        return $sHTMLoutput;
    }





    function getRowCountForViewList ($aSQL, $aArgs = array(), $bDebug = false)
    {
        // Attempt to speed up the "counting" part of the VL queries.
        // ViewList queries are counting the number of total hits using the
        // MySQL extension SQL_CALC_FOUND_ROWS. This works well for queries
        // sorted on non-indexed fields, where the query itself also requires a
        // full scan through the results. However,for queries that are normally
        // fast when LIMITed, this slows down the query a lot.
        // This function here will attempt to reduce the given query to a simple
        // SELECT COUNT(*) statement with as few joins as needed, resulting in
        // an as fast query as possible.
        // The $bDebug argument lets this function just return the SQL that is produced.
        global $_DB, $_INI;

        // We never need an ORDER BY to get the number of results, so... (ORDER BY code removed 2019-02-18)
        $aSQL['ORDER_BY'] = '';

        // If we don't have a HAVING clause, we can simply drop the SELECT information.
        $aColumnsNeeded = array();
        $aTablesNeeded = array();
        if (!$aSQL['GROUP_BY'] && !$aSQL['HAVING']) {
            $aSQL['SELECT'] = '';
        } else {
            if ($aSQL['GROUP_BY']) {
                // We do have GROUP BY... We'll need to keep only the columns in the SELECT that are aliases,
                // but non-alias columns that are used for grouping must also be kept in the JOIN!
                // Parse GROUP BY! Can be a mix of real columns and aliases.
                if (preg_match_all('/\b(?:(\w+)\.)?(\w+)\b/', $aSQL['GROUP_BY'], $aRegs)) {
                    for ($i = 0; $i < count($aRegs[0]); $i ++) {
                        // 1: table referred to (real columns without alias only);
                        // 2: alias, or column name in given table.
                        if ($aRegs[1][$i]) {
                            // Real table. We don't need this in the SELECT unless it's also in the HAVING, but we definitely need this in the JOIN.
                            $aTablesNeeded[] = $aRegs[1][$i];
                        } elseif ($aRegs[2][$i]) {
                            // Alias only. Keep this column for the SELECT. When parsing the SELECT, we'll find out from which table(s) it is.
                            $aColumnsNeeded[] = $aRegs[2][$i];
                        }
                    }
                }
            }
            if ($aSQL['HAVING']) {
                // We do have HAVING, so now we'll have to see what we need to keep, the rest we toss out.
                // Parse HAVING! These are *mostly* no fields directly from tables, but all aliases, so this parsing is different from parsing WHERE.
                // We don't care about AND/OR or anything... we just want the aliases.
                if (preg_match_all('/\b(\w+)\s(?:[!><=]+|IS (?:NOT )?NULL|LIKE )/', $aSQL['HAVING'], $aRegs)) {
                    $aColumnsNeeded = array_merge($aColumnsNeeded, $aRegs[1]);
                } elseif (preg_match_all('/\b(?:(\w+)\.)?(`\w+\/[A-Za-z0-9_\/]+`)/', $aSQL['HAVING'], $aRegs)) {
                    // However, for the multi value filter, we do have a full column here.
                    // If we have a table name, we can just add that to the $aTablesNeeded array and be done.
                    // Otherwise, add the column to the list of needed columns and hope we find it in the SELECT.
                    for ($i = 0; $i < count($aRegs[0]); $i ++) {
                        if ($aRegs[1][$i]) {
                            // Table alias given.
                            $aTablesNeeded[] = $aRegs[1][$i];
                        } else {
                            $aColumnsNeeded[] = $aRegs[2][$i];
                        }
                    }
                }
            }
        }
        $aColumnsNeeded = array_unique($aColumnsNeeded);
        if (!$aColumnsNeeded) {
            $aSQL['SELECT'] = '';
        }



        // Now that we know which columns we should keep, we can parse the SELECT clause to see what we can remove.
        $aColumnsUsed = array(); // Will contain limited information on the columns defined in the SELECT syntax.
        if ($aSQL['SELECT'] && $aColumnsNeeded) {
            // Analyzing the SELECT. This is quite difficult as we can have simple SELECTs but also really complicated ones,
            // such as GROUP_CONCAT() or subselects. These should all be parsed and needed tables should be identified.
            //                    t.* || t.col                    [t.col || "value" || (t.col ... val) || FUNCTION() || CASE ... END] AS alias
            if (preg_match_all('/(([a-z0-9_]+)\.(?:\*|[a-z0-9_]+)|(?:(?:([a-z0-9_]+)\.[a-z0-9_]+|".*"|[A-Z_]*\(.+\)|CASE .+ END)\s+AS\s+([A-Za-z0-9_]+|`[A-Za-z0-9_\/]+`)))(?:,|$)/Us', $aSQL['SELECT'], $aRegs)) {
                for ($i = 0; $i < count($aRegs[0]); $i ++) {
                    // First we'll store the column information, later we'll loop though it to see which tables they refer to.
                    // 1: entire SELECT string incl. possible alias;
                    // 2: table referred to (fields without alias only);
                    // 3: table referred to (simple fields with alias only);
                    // 4: alias, if present.
                    // Try to see which table(s) is/are used here.
                    $aTables = array();
                    $sTable = ($aRegs[2][$i]?: $aRegs[3][$i]);
                    if ($sTable) {
                        $aTables[] = $sTable;
                    } else {
                        // OK, this was no simple SELECT string. This was GROUP_CONCAT, COUNT() or similar.
                        // Especially (GROUP_)CONCAT can contain quite some different columns and even tables.
                        // Analyzing the field definition... We don't care about its structure or anything... we just want tables.
                        // There should *always* be table aliases, so it's going to be easy.
                        // With subqueries however, this will fail a bit. It will find table aliases that may be of tables in the subquery.
                        //  However, in the worst case scenario it will keep tables that are not necessary to be kept.
                        if (preg_match_all('/\b(\w+)\.(?:`|[A-Za-z]|\*)/', $aRegs[1][$i], $aRegsTables)) {
                            $aTables = array_unique($aRegsTables[1]);
                        }
                    }
                    // Key: alias or, when not available, the SELECT statement (table.col).
                    $aColumnsUsed[($aRegs[4][$i]?: $aRegs[1][$i])] = array(
                        'SQL' => $aRegs[1][$i],
                        'tables' => $aTables,
                    );
                    // We don't need more info anyway.
                }
            }

            // Now, loop the parsed columns, check which fields are needed, rebuild the SELECT statement, and store which tables will be needed.
            $aSQL['SELECT'] = '';
            foreach ($aColumnsUsed as $sCol => $aCol) {
                if (in_array($sCol, $aColumnsNeeded)) {
                    $aSQL['SELECT'] .= (!$aSQL['SELECT']? '' : ', ') . $aCol['SQL'];
                    $aTablesNeeded = array_merge($aTablesNeeded, $aCol['tables']);
                }
            }
        }



        // Analyzing the WHERE... We don't care about AND/OR or anything... we just want tables.
        // WHERE clauses *always* contain the table aliases, so it's going to be easy.
        if (preg_match_all('/\b(\w+)\.(?:`|[A-Za-z])/', $aSQL['WHERE'], $aRegs)) {
            $aTablesNeeded = array_merge($aTablesNeeded, $aRegs[1]);
        }

        // When we're running filters on the custom columns, we never use a table alias,
        // because we don't know where the column comes from.
        // To solve this, we must parse the column and fetch the used alias from the query.
        // We're specifically looking for custom columns *not* prefixed by a table alias.
        if (preg_match_all('/[^.](?:`(\w+)\/[A-Za-z0-9_\/]+`)/', $aSQL['WHERE'], $aRegs)) {
            // To not reproduce code, we'll use lovd_getTableInfoByCategory().
            // Loop columns and find tables.
            foreach ($aRegs[1] as $sCategory) {
                $aTableInfo = lovd_getTableInfoByCategory($sCategory);
                if (isset($aTableInfo['table_sql']) && preg_match_all('/' . $aTableInfo['table_sql'] . ' AS (\w+)\b/i', $aSQL['FROM'], $aRegsTables)) {
                    $aTablesNeeded = array_merge($aTablesNeeded, $aRegsTables[1]);
                } else {
                    // OK, this really shouldn't happen. Either the column wasn't a
                    // category we recognized, or the SQL was too complicated?
                    // Let's log this.
                    lovd_writeLog('Error', 'LOVD-Lib', 'LOVD_Object::' . __FUNCTION__ . '() - Function identified custom column category ' . $sCategory . ', but couldn\'t find corresponding table alias in query.' . "\n" . 'URL: ' . preg_replace('/^' . preg_quote(rtrim(lovd_getInstallURL(false), '/'), '/') . '/', '', $_SERVER['REQUEST_URI']) . "\n" . 'From: ' . $aSQL['FROM']);
                }
            }
        }
        $aTablesNeeded = array_unique($aTablesNeeded);



        // Now, SELECT should be as small as possible. What's left in the SELECT is needed.
        // See which tables we can't remove from the JOIN because they're in SELECT, or because they're in the WHERE.
        // (INNER JOINs will never be removed).
        // Now shorten the JOIN as much as possible!
        // Tables *always* use aliases so we'll just search for those.
        // While matching, we add a space before the FROM so that we can match the first table as well, but it won't have a JOIN statement captured.
        $aTablesUsed = array();
        // This regexp is incredibly complex. Perhaps rewrite it using simpler code using multiple preg_match() calls?
        // Splitting on "JOIN" might already help a lot.
        if (preg_match_all(
            '/\s?((?:LEFT(?: OUTER)?|INNER) JOIN)?\s' . // The JOIN syntax.
            '(' . preg_quote(TABLEPREFIX, '/') . '_[a-z0-9_]+) AS ([a-z0-9_]+)\s+' . // The table and its alias.
            '(?:FORCE INDEX FOR JOIN \([^)]+\)\s+)?' . // The optional FORCE INDEX syntax as in use by the custom VL.
            '(ON\s+\((?:(\()?[^()]+(\()?[^()]+(\()?[^()]+(?(5)\))(?(6)\))(?(7)\)))+\))?' . // The ON clause. Optional, because we ignore the USING clause (we need table aliases).
            '/', ' ' . $aSQL['FROM'], $aRegs)) {
            for ($i = 0; $i < count($aRegs[0]); $i ++) {
                // 0: table's full SQL syntax;
                // 1: JOIN syntax;
                // 2: full table name;
                // 3: table alias;
                // 4: full JOIN's ON clause.
                $aTablesUsed[$aRegs[3][$i]] = array(
                    'name' => $aRegs[2][$i], // We don't actually use the name, but well...
                    'join' => $aRegs[1][$i],
                    'join_dependencies' => array(),
                    'SQL' => trim($aRegs[0][$i]),
                );
                // Now, gather the JOIN's dependencies. Doing this separately, an ON clause can be very complex.
                foreach (explode(' ', $aRegs[4][$i]) as $sWord) {
                    if (preg_match('/^([a-z0-9_]+)\./', ltrim($sWord, '('), $aRegs2)) {
                        // Exclude this table's own alias.
                        if ($aRegs2[1] != $aRegs[3][$i]) {
                            $aTablesUsed[$aRegs[3][$i]]['join_dependencies'][] = $aRegs2[1];
                        }
                    }
                }
                $aTablesUsed[$aRegs[3][$i]]['join_dependencies'] = array_unique($aTablesUsed[$aRegs[3][$i]]['join_dependencies']);
            }
        }

        // Loop these tables in reverse, and remove JOINs as much as possible!
        foreach (array_reverse(array_keys($aTablesUsed)) as $sTableAlias) {
            if (!$aTablesUsed[$sTableAlias]['join']) {
                // We've reached the first table, abort now.
                break;
            } elseif (in_array($sTableAlias, $aTablesNeeded)) {
                // This is a table that we need. Collect its dependencies and continue.
                foreach ($aTablesUsed[$sTableAlias]['join_dependencies'] as $sTable) {
                    $aTablesNeeded[] = $sTable;
                }
                continue;
            } elseif ($aTablesUsed[$sTableAlias]['join'] != 'INNER JOIN') {
                // OK, this table is not needed. Get rid of it.
                $aSQL['FROM'] = rtrim(str_replace($aTablesUsed[$sTableAlias]['SQL'] . ' ', '', $aSQL['FROM'] . ' '));
                unset($aTablesUsed[$sTableAlias]);
            }
        }



        // If we have no SELECT left, we can surely do a simple SELECT COUNT(*) FROM ... or
        // a SELECT COUNT(*) FROM (SELECT ...)A. We can't do a simple SELECT COUNT(*) if
        // we have a GROUP_BY, because it will separate the counts.
        // In case we still have a SELECT, and we create a subquery while the
        // SELECT has double columns (happens rarely), we get a query error. In
        // that case we could drop the first column's declaration, or otherwise
        // keep using the SQL_CALC_FOUND_ROWS().
        // For now, we'll just take our chances. If this query will fail, LOVD
        // will fall back on the original SQL_CALC_FOUND_ROWS() method.
        $bInSubQuery = false;
        if (!$aSQL['SELECT']) {
            // If we just have one table left, we might be able to drop the GROUP BY.
            // If so, we can use a simple COUNT(*) query instead of a nested one.
            // In 99%, if not all, of the cases we can just drop the GROUP BY since
            // we "always" put it on the first table's ID, but just to be sure:
            if (count($aTablesUsed) == 1 && $aSQL['GROUP_BY'] == current(array_keys($aTablesUsed)) . '.id') {
                // Using one table, and grouping on its ID.
                $aSQL['GROUP_BY'] = '';
            }

            if (!$aSQL['GROUP_BY']) {
                // Simple SELECT COUNT(*) FROM ...
                $aSQL['SELECT'] = 'COUNT(*)';
            } else {
                // We'll have to create a bigger query around this...
                // We'll build that query in the end.
                $bInSubQuery = true;
                $aSQL['SELECT'] = '1';
            }
        } else {
            // SELECT is left (meaning we had a HAVING), we have to use a subquery!
            $bInSubQuery = true;
        }

        // Delete LIMIT, we don't want that anymore...
        $aSQL['LIMIT'] = '';



        $sSQLOut = $this->buildSQL($aSQL);
        // Now, build the subquery if we need it.
        if ($bInSubQuery) {
            $sSQLOut = 'SELECT COUNT(*) FROM (' . $sSQLOut . ')A';
        }

        if ($bDebug) {
            return $sSQLOut;
        }

        // Run the query, fetch the result and return.
        // We'll return false when we failed.
        $nCount = false;
        $qCount = $_DB->q($sSQLOut, $aArgs, false);
        if ($qCount !== false) {
            $nCount = $qCount->fetchColumn();
        }

        if ($nCount === false) {
            // We failed, log this. Actually, why aren't query errors logged if they're not fatal?
            lovd_queryError('QueryOptimizer', $sSQLOut, 'Error in ' . __FUNCTION__ . '() while executing optimized query.', false);

            // As a fallback, use SQL_CALC_FOUND_ROWS() for MySQL instances, or
            // a count() on a full result set otherwise. The latter is super
            // inefficient, and only meant for small SQLite databases.
            if ($_INI['database']['driver'] == 'mysql') {
                $this->aSQLViewList['SELECT'] = 'SQL_CALC_FOUND_ROWS ' . $this->aSQLViewList['SELECT'];
                $this->aSQLViewList['LIMIT'] = '0';
                $_DB->q($this->buildSQL($this->aSQLViewList), $aArgs);
                $nCount = $_DB->q('SELECT FOUND_ROWS()')->fetchColumn();
            } else {
                // Super inefficient, only for low-volume (sqlite) databases!
                $nCount = count($_DB->q($this->buildSQL($this->aSQLViewList), $aArgs)->fetchAllColumn());
            }
        }

        return $nCount;
    }





    function getSortDefault ()
    {
        return $this->sSortDefault;
    }





    function insertEntry ($aData, $aFields = array(), $bHalt = true)
    {
        // Inserts data in $aData into the database, using only fields defined in $aFields.
        // $aData = Associative array with values to be inserted. Keys should equal database column names.
        // $aFields = The keys of the associative array that will actually get inserted.
        global $_DB;

        // Check to see if this table uses revisions.
        $bRev = defined($this->sTable . '_REV');
        // Check if maybe we already were in a transaction when we started this function.
        // If so, we won't create a new transaction, nor run a commit() in the end.
        $bWasAlreadyInTransaction = (method_exists($_DB, 'inTransaction') && $_DB->inTransaction());
        if ($bRev) {
            if (!$bWasAlreadyInTransaction) {
                $_DB->beginTransaction();
            }
        }

        if (!is_array($aData) || !count($aData)) {
            lovd_displayError('LOVD-Lib', 'Objects::(' . $this->sObject . ')::insertEntry() - Method didn\'t receive data array');
        } elseif (!is_array($aFields) || !count($aFields)) {
            $aFields = array_keys($aData);
        } else {
            // Non-numerical keys or a missing key 0 messes up the SQL creation.
            $aFields = array_values($aFields);
        }

        // Query text.
        $sSQL = 'INSERT INTO ' . constant($this->sTable) . ' (';
        $aSQL = array();
        foreach ($aFields as $key => $sField) {
            $sSQL .= (!$key? '' : ', ') . '`' . $sField . '`';
            if (!isset($aData[$sField])) {
                // Field may be not set, make sure it is (happens in very rare cases).
                $aData[$sField] = '';
            }
            if ($aData[$sField] === '' && in_array(substr(lovd_getColumnType(constant($this->sTable), $sField), 0, 3), array('INT', 'DAT', 'DEC', 'FLO'))) {
                $aData[$sField] = NULL;
            }
            $aSQL[] = $aData[$sField];
        }
        // If this is a revision table, we mostly use the same query.
        if ($bRev) {
            $sRevSQL = $sSQL;
        }
        $sSQL .= ') VALUES (?' . str_repeat(', ?', count($aFields) - 1) . ')';

        if (!defined('LOG_EVENT')) {
            define('LOG_EVENT', $this->sObject . '::insertEntry()');
        }
        $q = $_DB->q($sSQL, $aSQL, $bHalt, true);
        if (!$bHalt && $q === false) {
            return false;
        }

        $nID = $_DB->lastInsertId(); // Will not return an ID for linking tables.
        if ($nID && substr(lovd_getColumnType(constant($this->sTable), 'id'), 0, 3) == 'INT') {
            $nID = sprintf('%0' . lovd_getColumnLength(constant($this->sTable), 'id') . 'd', $nID);
        }

        // Setup and run the revision table SQL.
        if ($bRev) {
            // Replace the table name with the revision table name
            $sRevSQL = preg_replace('/^(INSERT INTO ' . constant($this->sTable) . ')/i', 'INSERT INTO ' . constant($this->sTable . '_REV'), $sRevSQL);
            // For tables that don't have an AUTO_INCREMENT column, the ID must have already been provided in the SQL.
            // For other tables, we add the field here. We're assuming it's "id".
            if ($nID) {
                // Auto-increment column in place, assuming the name is "id".
                $sRevSQL .= ', id';
            }
            // These are the extra columns that are required for inserting a record into the revision table.
            $sRevSQL .= ', valid_from, reason';
            // Add the ID to the SQL's arguments as well, for auto-incremented tables.
            if ($nID) {
                $aSQL[] = $nID;
            }
            // Use the created_date if provided, otherwise use the current date.
            $aSQL[] = (empty($aData['created_date'])? date('Y-m-d H:i:s') : $aData['created_date']);
            // Use the reason, if provided in the $aData array.
            $aSQL[] = 'Record created' . (empty($aData['reason'])? '' : ': ' . $aData['reason']);
            $sRevSQL .= ') VALUES (?' . str_repeat(', ?', count($aSQL) - 1) . ')';
            $qRevisionInsert = $_DB->q($sRevSQL, $aSQL, true, true);

            // If any of the queries fail, then rollback and return false. Otherwise, commit.
            if (!$q || !$qRevisionInsert) {
                $_DB->rollBack();
                return false;
            } else {
                if (!$bWasAlreadyInTransaction) {
                    $_DB->commit();
                }
            }
        }
        return ($nID?: true);
    }





    function loadEntry ($ID)
    {
        // Loads and returns an entry from the database.
        // $ID = Can be an integer/numeric string, or an array. If an integer/numeric string: ID to load.
        //   If an associative array (for linking tables), use array('geneid' => 'IVD', 'userid' => 1).
        global $_DB, $_T;

        // Check to see if an ID has been passed and there is data to process.
        if ((!is_array($ID) && !trim($ID)) || (is_array($ID) && (!$ID || in_array('', array_map('trim', $ID))))) {
            // We were called, but the class wasn't initiated with an ID. Fail.
            lovd_displayError('LOVD-Lib', 'Objects::(' . $this->sObject . ')::loadEntry() - Method didn\'t receive ID');
        }

        if ($this->sSQLPreLoadEntry !== '') {
            // $sSQLPreLoadEntry is defined, execute it.
            $_DB->q($this->sSQLPreLoadEntry);
        }

        // Prepare ID variable, to always be in the array('id' => 1) format.
        $aIDs = $ID;
        if (!is_array($ID)) {
            // FIXME: Other methods of this class try something intelligent to figure out if it's 'id' indeed.
            //  Put that code in a function perhaps?
            $aIDs = array('id' => $ID);
        }

        // Build query.
        if ($this->sSQLLoadEntry) {
            $sSQL = $this->sSQLLoadEntry;
        } else {
            $sSQL = 'SELECT * FROM ' . constant($this->sTable) . ' WHERE ' . implode(' = ? AND ', array_keys($aIDs)) . ' = ?';
        }
        $q = $_DB->q($sSQL, array_values($aIDs), false);
        if ($q) {
            $zData = $q->fetchAssoc();
        }
        if (!$q || !$zData) {
            $sError = $_DB->formatError(); // Save the PDO error before it disappears.

            $_T->printHeader();
            if (defined('PAGE_TITLE')) {
                $_T->printTitle();
            }

            if ($sError) {
                lovd_queryError($this->sObject . '::loadEntry()', $sSQL, $sError);
            }

            lovd_showInfoTable('No such ID!', 'stop');

            $_T->printFooter();
            exit;

        } else {
            // FIXME: Need to confirm what to do here. $this->nID is used throughout the code,
            //  sometimes passed to constructors that do nothing with it. Sometimes as a parent ID (object ID),
            //  sometimes as the object's own ID. Sometimes an integer, sometimes an array.
            // Here it's assigned and we don't even know why. We need to standardize all of those usages.
            $this->nID = $aIDs;
        }

        $zData = $this->autoExplode($zData);

        return $zData;
    }





    function prepareData ($zData = '', $sView = 'list')
    {
        // Prepares the data by "enriching" the variable received with links, pictures, etc.
        // Also quotes all data with htmlspecialchars(), to prevent XSS.
        global $_AUTH, $_SETT;

        if (!is_array($zData)) {
            $zData = array();
        }

        if (!in_array($sView, array('list', 'entry'))) {
            $sView = 'list';
        }

        // Quote special characters, disallowing HTML and other tricks.
        $zData = lovd_php_htmlspecialchars($zData);

        $aDateColumns = array('created_date', 'edited_date', 'updated_date', 'last_login', 'start_date', 'end_date', 'valid_from', 'valid_to');
        foreach ($aDateColumns as $sDateColumn) {
            // Replace empty date values with "N/A".
            $zData[$sDateColumn . ($sView == 'list'? '' : '_')] = (!empty($zData[$sDateColumn])? $zData[$sDateColumn] : 'N/A');
            if (preg_match('/^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$/', $zData[$sDateColumn . ($sView == 'list'? '' : '_')])) {
                // Add timezone to full datetime values. VLs usually have removed the times.
                $zData[$sDateColumn . ($sView == 'list'? '' : '_')] .= ' ' . date('P (T)', strtotime($zData[$sDateColumn]));
            }
        }

        if ($sView == 'list') {
            // By default, we put anchors in the id_ and DNA fields, if present.
            if ($zData['row_link']) {
                // We just used htmlspecialchars() on zData, which includes the rowlink. Undo that.
                $zData['row_link'] = htmlspecialchars_decode($zData['row_link']);
                if (substr($zData['row_link'], 0, 11) == 'javascript:') {
                    $zData['row_link'] = htmlspecialchars(rawurldecode($zData['row_link']));
                }
                if (isset($this->aColumnsViewList['id_']) && $zData['id']) {
                    $zData['id_'] = '<A href="' . $zData['row_link'] . '" class="hide">' . $zData['id'] . '</A>';
                }
                foreach (array('VariantOnGenome/DNA', 'VariantOnTranscript/DNA') as $sCol) {
                    if (isset($this->aColumnsViewList[$sCol])) {
                        $zData[$sCol] = '<A href="' . $zData['row_link'] . '" class="hide">' . $zData[$sCol] . '</A>';
                    }
                }
            }
            // Determine minimum status for all data in current VL row.
            $nRowStatus = STATUS_OK;
            // Status coloring will only be done, when we have authorization.
            // Instead of having the logic in separate objects and the custom VL object, put it together here.
            // In LOVD+, we disable the feature of coloring hidden and marked data, since all data is hidden.
            if (!LOVD_plus && $_AUTH && $_AUTH['level'] >= $_SETT['user_level_settings']['see_nonpublic_data']) {
                // Loop through possible status fields, always keep the minimum.
                foreach (array('statusid', 'var_statusid', 'ind_statusid') as $sField) {
                    if (!empty($zData[$sField])) {
                        // In the VariantOnTranscriptUnique view the var_statusid can contain multiple IDs, separated by ",".
                        // PHP always takes the first integer-like part of a string when converting to an integer.
                        // But to avoid problems in the future, only the first character is compared.
                        $nRowStatus = min($nRowStatus, (int) substr($zData[$sField], 0, 1));
                    }
                }
            }

            // Mark row according to the lowest status; Marked is red; lower will be gray.
            if (empty($zData['class_name'])) {
                if ($nRowStatus == STATUS_MARKED) {
                    $zData['class_name'] = 'marked';
                } elseif ($nRowStatus < STATUS_MARKED) {
                    $zData['class_name'] = 'del';
                }
            }

            // Handle JSON data (well, in a VL, we hide it).
            foreach ($zData as $sKey => $Value) {
                if (is_string($Value) && $Value && $Value[0] == '{'
                    && is_array(json_decode(htmlspecialchars_decode($Value), true))) {
                    // We don't show JSON data in the VLs.
                    $zData[$sKey] = '<I>(data)</I>';
                }
            }

        } else {
            // Add links to users from *_by fields.
            $aUserColumns = array('owned_by', 'created_by', 'edited_by', 'updated_by', 'deleted_by', 'analysis_by', 'analysis_approved_by');
            foreach ($aUserColumns as $sUserColumn) {
                if (empty($zData[$sUserColumn]) || !isset($this->aColumnsViewEntry[$sUserColumn . '_'])) {
                    $zData[$sUserColumn . '_'] = 'N/A';
                } elseif ($_AUTH && $zData[$sUserColumn] != '00000') {
                    $zData[$sUserColumn . '_'] = '<A href="users/' . $zData[$sUserColumn] . '">' . $zData[$sUserColumn . '_'] . '</A>';
                }
            }

            // Handle JSON well.
            foreach ($zData as $sKey => $Value) {
                if (is_string($Value) && $Value && $Value[0] == '{'
                    && is_array(json_decode(htmlspecialchars_decode($Value), true))) {
                    // Restructure the JSON.
                    $zData[$sKey] = $this->formatArrayToTable(json_decode(htmlspecialchars_decode($Value), true));
                }
            }
        }

        // If we find an owned_by_ field, and an owner array, we set up the popups as well.
        if (isset($zData['owned_by_']) && !empty($zData['owner'])) {
            if (!is_array($zData['owner'][0])) {
                $zData['owner'] = array($zData['owner']);
            }
            // We are going to overwrite the 'owned_by_' field.
            $sOwnedBy = '';
            foreach ($zData['owner'] as $aLinkData) {
                if (count($aLinkData) >= 6) {
                    list($nID, $sName, $sEmail, $sInstitute, $sDepartment, $sCountryID) = $aLinkData;
                    if (intval($nID) === 0) {
                        // Skip special "LOVD" user.
                        continue;
                    }
                    // For VLs, call the tooltip function with a request to move the tooltip left, because "Owner" is often the last column in the table,
                    //  and we don't want it to run off the page. I have found no way of moving the tooltip left whenever it's enlarging the document size.
                    $sOwnedBy .= (!$sOwnedBy? '' : ', ') .
                        '<SPAN class="custom_link" onmouseover="lovd_showToolTip(\'' .
                        addslashes(
                            '<TABLE border=0 cellpadding=0 cellspacing=0 width=350 class=S11><TR><TH valign=top>User&nbsp;ID</TH><TD>' .
                            (!$_AUTH || $_AUTH['level'] < LEVEL_MANAGER? $nID : '<A href=users/' . $nID . '>' . $nID . '</A>') .
                            '</TD></TR><TR><TH valign=top>Name</TH><TD>' . htmlspecialchars($sName) .
                            '</TD></TR><TR><TH valign=top>Email&nbsp;address</TH><TD>' . str_replace("\r\n", '<BR>', lovd_hideEmail($sEmail)) .
                            '</TD></TR><TR><TH valign=top>Institute</TH><TD>' . htmlspecialchars($sInstitute) .
                            '</TD></TR><TR><TH valign=top>Department</TH><TD>' . htmlspecialchars($sDepartment) .
                            '</TD></TR><TR><TH valign=top>Country</TH><TD>' . $sCountryID . '</TD></TR></TABLE>') .
                        '\', this, [' . ($sView == 'list'? '-200' : '0') . ', 0]);">' . $sName . '</SPAN>';
                }
            }
            if ($sOwnedBy) {
                // Overwrite only if we came up with something better
                //  (which won't happen for LOVD-owned data).
                $zData['owned_by_'] = $sOwnedBy;
            }
        }
        if (LOVD_plus) {
            // Analyzer's details like the owner's details.
            if (isset($zData['analysis_by']) && (int) $zData['analysis_by'] && !empty($zData['analyzer'])) {
                list($nID, $sName, $sEmail, $sInstitute, $sDepartment, $sCountryID) = $zData['analyzer'];
                $zData['analysis_by_'] = '<SPAN class="custom_link" onmouseover="lovd_showToolTip(\'' .
                    addslashes('<TABLE border=0 cellpadding=0 cellspacing=0 width=350 class=S11><TR><TH valign=top>User&nbsp;ID</TH><TD>' .
                        (!$_AUTH || $_AUTH['level'] < LEVEL_MANAGER? $nID : '<A href=users/' . $nID . '>' . $nID . '</A>') . '</TD></TR>' .
                        '<TR><TH valign=top>Name</TH><TD>' . $sName . '</TD></TR>' .
                        '<TR><TH valign=top>Email&nbsp;address</TH><TD>' . str_replace("\r\n", '<BR>', lovd_hideEmail($sEmail)) . '</TD></TR>' .
                        '<TR><TH valign=top>Institute</TH><TD>' . $sInstitute . '</TD></TR>' .
                        '<TR><TH valign=top>Department</TH><TD>' . $sDepartment . '</TD></TR>' .
                        '<TR><TH valign=top>Country</TH><TD>' . $sCountryID . '</TD></TR></TABLE>') . '\', this);">' . $sName . '</SPAN>';
            }
        }

        return $zData;
    }





    private function previewColumnFindAndReplace ($sFRViewListCol, $sFRFieldDisplayname,
                                                  $sFRSearchValue, $sFRReplaceValue, $aArgs, $aOptions)
    {
        // Append a field to the viewlist showing a preview of changes for a
        // find and replace (F&R) action. Also checks if the resulting data
        // records are valid through checkFields() and report any errors.
        // Returns the number of rows that will be affected by the F&R.
        // Params:
        // $sFRViewListCol      Name of field on which F&R to preview.
        // sFRFieldDisplayname  Display name of field for F&R.
        // sFRSearchValue       Search string.
        // sFRReplaceValue      Replace value.
        // aOptions             F&R options (e.g. match at start of field)

        // Column should be configured to allow Find & Replace.
        if (empty($this->aColumnsViewList[$sFRViewListCol]['allow_find_replace'])) {
            lovd_displayError('FindAndReplace', 'Find and Replace requested on field "' .
                $sFRViewListCol . '", which does not have that feature enabled.');
        }

        // Try to discover the tablename and fieldname in the database.
        list($sFieldname, $sTablename, $sTableRef) = $this->getFieldInfo($sFRViewListCol);

        // Get search condition for query to find all records matching F&R action.
        $sFRSearchCondition = $this->generateFRSearchCondition($sFRSearchValue, 'subq',
                                                               $sFieldname, $aOptions);

        // Construct replace statement for F&R action.
        $sReplaceStmt = $this->generateViewListFRReplaceStatement($sTableRef, $sFieldname,
            $sFRSearchValue, $sFRReplaceValue, $aOptions);

        // Set names for preview column.
        $sPreviewFieldname = $sFRViewListCol . '_FR';
        $sPreviewFieldDisplayname = $sFRFieldDisplayname . ' (PREVIEW)';

        // Check affected records with checkFields() and display any errors.
        list($bSuccess, $nAffectedRows) = $this->checkFieldFRResult($sFieldname, $sTablename,
             $sTableRef, $sFRSearchCondition, $aArgs, $sReplaceStmt);
        if (!$bSuccess) {
            lovd_showInfoTable('Some records become invalid after the replace action on ' .
                'column ' . $sFRViewListCol . '. Please tend to the error messages below and ' .
                'change the options accordingly.', 'warning');
            lovd_errorPrint();
        }

        // Edit sql in $this->aSQLViewList to include an F&R column.
        $this->aSQLViewList['SELECT'] .= ', ' . $sReplaceStmt . ' AS `' . $sPreviewFieldname . '`';

        // Add description of preview-field in $this->aColumnsViewList based on original field.
        $aFRColValues = $this->aColumnsViewList[$sFRViewListCol];
        if (!isset($aFRColValues['view'])) {
            $aFRColValues['view'] = array($sPreviewFieldDisplayname, 160, 'class="FRPreview"');
        } else {
            $aFRColValues['view'][0] = $sPreviewFieldDisplayname;
        }
        $aFRColValues['db'] = array();

        // Place preview column just behind column where F&R is performed on.
        lovd_arrayInsertAfter($sFRViewListCol, $this->aColumnsViewList,
            $sPreviewFieldname, $aFRColValues);

        // Copy any custom column info from the original field to the preview
        // field (e.g. custom links).
        if (isset($this->aColumns) && isset($this->aColumns[$sFRViewListCol])) {
            $this->aColumns[$sPreviewFieldname] = $this->aColumns[$sFRViewListCol];
            $this->aColumns[$sPreviewFieldname]['id'] = $sPreviewFieldname;
        }

        return $nAffectedRows;
    }





    public function processViewListSearchArgs ($aRequest)
    {
        // Generate WHERE and HAVING statements for search field content in viewlist.
        // Returns an array with:
        // - $WHERE         Array of strings to be added to the WHERE clause of the viewlist query.
        // - $HAVING        Array of strings to be added to the HAVING clause of the query.
        // - $aArguments    Array of arrays with arguments for placeholders in WHERE and HAVING
        //                  clauses.
        // - $aBadSyntaxColumns ?
        // - $aColTypes     ?

        global $_INI;

        // SEARCH: Advanced text search.
        $WHERE = '';
        $HAVING = '';
        $aArguments = array(
            'WHERE' => array(),
            'HAVING' => array()
        );
        $aBadSyntaxColumns = array();
        $aColTypes = array(); // For describing the search expressions in the mouseover of the input field.
        foreach ($this->aColumnsViewList as $sColumn => $aCol) {
            if (!empty($aCol['db'][2]) && isset($aRequest['search_' . $sColumn]) && trim($aRequest['search_' . $sColumn]) !== '') {
                $CLAUSE = (strpos($aCol['db'][0], '.') === false && strpos($aCol['db'][0], '/') === false? 'HAVING' : 'WHERE');
                if ($aCol['db'][2] !== true) {
                    // Column type of an alias is given by LOVD.
                    $sColType = $aCol['db'][2];
                } else {
                    if (preg_match('/^[a-z0-9]+\.[a-z_]+$/i', $aCol['db'][0])) {
                        list($sAlias, $sColName) = explode('.', $aCol['db'][0]);
                        if (preg_match('/(' . TABLEPREFIX . '_[a-z0-9_]+) AS ' . $sAlias . '\b/', $this->aSQLViewList['FROM'], $aMatches)) {
                            $sTable = $aMatches[1];
                        } else {
                            // Alias was not valid, default col type to TEXT.
                            $sTable = '';
                        }
                    } else {
                        $sColName = trim($aCol['db'][0], '`');
                        $sTable = constant($this->sTable);
                    }
                    $sColType = lovd_getColumnType($sTable, $sColName);
                }
                $aColTypes[$sColumn] = $sColType;
                // Allow for searches where the order of words is forced by enclosing the values with double quotes;
                // Replace spaces in sentences between double quotes so they don't get exploded.
                if ($sColType == 'DATETIME') {
                    $sSearch = preg_replace('/ (\d)/', "{{SPACE}}$1", trim($aRequest['search_' . $sColumn]));
                } else {
                    $sSearch = preg_replace_callback(
                        '/("[^"]*")/',
                        function ($aRegs)
                        {
                            return str_replace(' ', '{{SPACE}}', $aRegs[1]);
                        }, trim($aRequest['search_' . $sColumn]));
                }
                $aWords = explode(' ', $sSearch);
                foreach ($aWords as $sWord) {
                    if ($sWord !== '') {
                        $sWord = lovd_escapeSearchTerm($sWord);
                        $aOR = (preg_match('/^[^|]+(\|[^|]+)+$/', $sWord)? explode('|', $sWord) : array($sWord));
                        $$CLAUSE .= ($$CLAUSE? ' AND ' : '') . (!empty($aOR[1])? '(' : '');
                        foreach ($aOR as $nTerm => $sTerm) {
                            $$CLAUSE .= ($nTerm? ' OR ' : '');
                            switch ($sColType) {
                                case 'DECIMAL_UNSIGNED':
                                case 'DECIMAL':
                                case 'FLOAT_UNSIGNED':
                                case 'FLOAT':
                                case 'INT_UNSIGNED':
                                case 'INT':
                                    if (preg_match('/^([><]=?|!)?(-?\d+(\.\d+)?)$/', $sTerm, $aMatches)) {
                                        $sOperator = $aMatches[1];
                                        $sTerm = $aMatches[2];
                                        if ($sOperator) {
                                            $sOperator = (substr($sOperator, 0, 1) == '!'? '!=' : $sOperator);
                                        } else {
                                            $sOperator = '=';
                                        }
                                        $$CLAUSE .= '(' . $aCol['db'][0] . ' ' . $sOperator . ' ' . ($_INI['database']['driver'] != 'sqlite'? '?' : 'CAST(? AS NUMERIC)') . ($sOperator == '!='? ' OR ' . $aCol['db'][0] . ' IS NULL)' : ')');
                                        $aArguments[$CLAUSE][] = $sTerm;
                                    } elseif (preg_match('/^!?=""$/', $sTerm)) {
                                        // Numeric fields cannot be empty, they are NULL. So searching for ="" must return all NULL values.
                                        $$CLAUSE .= $aCol['db'][0] . ' IS ' . (substr($sTerm, 0, 1) == '!'? 'NOT ' : '') . 'NULL';
                                    } else {
                                        // Bad syntax! Report. LOVD doesn't actually complain about bad syntax columns
                                        // when they're not viewed, but we do need that var to be filled.
                                        $aBadSyntaxColumns[] = $aCol[($aCol['view']? 'view' : 'db')][0];
                                    }
                                    break;
                                case 'DATE':
                                case 'DATETIME':
                                    if (preg_match('/^([><]=?|!)?(\d{4})(?:(-\d{2})' . ($sColType == 'DATETIME'? '(?:(-\d{2})(?:( \d{2})(?:(:\d{2})(:\d{2})?)?)?)?)?' : '(-\d{2})?)?') . '$/', $sTerm, $aMatches)) {
                                        @list(, $sOperator, $nYear, $nMonth, $nDay, $nHour, $nMinute, $nSecond) = $aMatches;
                                        if (!checkdate(($nMonth? substr($nMonth, 1) : '01'), ($nDay? substr($nDay, 1) : '01'), $nYear) && $aCol['view']) {
                                            $aBadSyntaxColumns[] = $aCol['view'][0];
                                        }
                                        if (((isset($nHour) && ($nHour < 0 || $nHour > 23)) || (isset($nMinute) && ($nMinute < 0 || $nMinute > 59)) || (isset($nSecond) && ($nSecond < 0 || $nSecond > 59))) && $aCol['view']) {
                                            $aBadSyntaxColumns[] = $aCol['view'][0];
                                        }
                                        // Create $aTerms arrays, pre-filled with date components. These components will be overwritten later by $aMatches, if they are given by the user.
                                        switch ($sOperator) {
                                            case '>':
                                            case '<=':
                                                $aTerms = array(3 => '-12', '-31', ' 23', ':59', ':59'); // FIXME; some databases may not like this on DATE columns.
                                                break;
                                            case '<':
                                            case '>=':
                                                $aTerms = array(3 => '-01', '-01', ' 00', ':00', ':00'); // FIXME; some databases may not like this on DATE columns.
                                                break;
                                            case '!':
                                            default:
                                                if (($sColType == 'DATE' && isset($nDay)) || ($sColType == 'DATETIME' && isset($nSecond))) {
                                                    $sOperator .= '='; // != or =
                                                } else {
                                                    $sOperator = ($sOperator == '!'? 'NOT ' : '') . 'LIKE';
                                                }
                                                $aTerms = array(3 => '', '', '', '', '');
                                                break;
                                        }
                                        unset($aMatches[0], $aMatches[1]);
                                        // Replace our default date components by the ones given by the user.
                                        $aTerms = $aMatches + $aTerms;
                                        ksort($aTerms);
                                        $sTerms = implode($aTerms);
                                        $$CLAUSE .= '(' . $aCol['db'][0] . ' ' . $sOperator . ' ?' . ($sOperator == 'NOT LIKE'? ' OR ' . $aCol['db'][0] . ' IS NULL)' : ')');
                                        $aArguments[$CLAUSE][] = $sTerms . (substr($sOperator, -4) == 'LIKE'? '%' : '');
                                    } elseif (preg_match('/^!?=""$/', $sTerm)) {
                                        // DATE(TIME) fields cannot be empty, they are NULL. So searching for ="" must return all NULL values.
                                        $$CLAUSE .= $aCol['db'][0] . ' IS ' . (substr($sTerm, 0, 1) == '!'? 'NOT ' : '') . 'NULL';
                                    } else {
                                        // Bad syntax! Report. LOVD doesn't actually complain about bad syntax columns
                                        // when they're not viewed, but we do need that var to be filled.
                                        $aBadSyntaxColumns[] = $aCol[($aCol['view']? 'view' : 'db')][0];
                                    }
                                    break;
                                default:
                                    if (preg_match('/^!?"?([^"]+)"?$/', $sTerm, $aMatches)) {
                                        // ASDF, !ASDF, "ASDF", !"ASDF".
                                        // Added support for ^term and term$, ^term$ being the same as ="term".
                                        $sOperator = (substr($sTerm, 0, 1) == '!'? 'NOT ' : '') . 'LIKE';
                                        $sTerm = ltrim($sTerm, '!');
                                        $sPrefix = $sSuffix = '%';
                                        $$CLAUSE .= '(' . $aCol['db'][0] . ' ' . $sOperator . ' ?' . ($sOperator == 'NOT LIKE'? ' OR ' . $aCol['db'][0] . ' IS NULL)' : ')');
                                        if (substr($sTerm, 0, 1) == '^') {
                                            $sPrefix = '';
                                            $aMatches[1] = substr($aMatches[1], 1);
                                        }
                                        if (substr($sTerm, -1) == '$') {
                                            $sSuffix = '';
                                            $aMatches[1] = substr($aMatches[1], 0, -1);
                                        }
                                        $aArguments[$CLAUSE][] = $sPrefix . $aMatches[1] . $sSuffix;
                                    } elseif (preg_match('/^!?=""$/', $sTerm)) {
                                        // ="", !="".
                                        $bNot = (substr($sTerm, 0, 1) == '!');
                                        if ($bNot) {
                                            $$CLAUSE .= '(' . $aCol['db'][0] . ' != "" AND ' . $aCol['db'][0] . ' IS NOT NULL)';
                                        } else {
                                            $$CLAUSE .= '(' . $aCol['db'][0] . ' = "" OR ' . $aCol['db'][0] . ' IS NULL)';
                                        }
                                    } elseif (preg_match('/^!?="([^"]*)"$/', $sTerm, $aMatches)) {
                                        // ="ASDF", !="ASDF".
                                        $sOperator = (substr($sTerm, 0, 1) == '!'? '!=' : '=');
                                        $$CLAUSE .= '(' . $aCol['db'][0] . ' ' . $sOperator . ' ?' . ($sOperator == '!='? ' OR ' . $aCol['db'][0] . ' IS NULL)' : ')');
                                        // 2013-07-25; 3.0-07; When not using LIKE, undo escaping done by lovd_escapeSearchTerm().
                                        $aArguments[$CLAUSE][] = str_replace(array('\%', '\_'), array('%', '_'), $aMatches[1]);
                                    } else {
                                        // Bad syntax! Report. LOVD doesn't actually complain about bad syntax columns
                                        // when they're not viewed, but we do need that var to be filled.
                                        $aBadSyntaxColumns[] = $aCol[($aCol['view']? 'view' : 'db')][0];
                                    }
                                    break;
                            }
                        }
                        $$CLAUSE .= (!empty($aOR[1])? ')' : '');
                    }
                }
            }
        }

        // Handle multivalue filter request. I.e., show only records that have more than one
        // value for certain aggregated columns.
        if (!empty($aRequest['MVSCols'])) {
            // But at least add some basic checks. Drop all strings that aren't a column.
            // These values are user-provided, so should be considered tainted.
            $aRequestMVSCols = array_intersect(explode(';', $aRequest['MVSCols']), array_keys($this->aColumnsViewList));
            foreach ($aRequestMVSCols as $sMVSCol) {
                list($sField,, $sTableRef) = $this->getFieldInfo($sMVSCol);

                // Enclose fieldname with backticks and append it to the having clause.
                $sMVSColQuoted = ($sTableRef? $sTableRef . '.' : '') . '`' . trim($sField, '`') . '`';
                $HAVING .= ($HAVING? ' AND ' : '') . 'COUNT(DISTINCT ' . $sMVSColQuoted . ') > 1';
            }
        }

        return array($WHERE, $HAVING, $aArguments, $aBadSyntaxColumns, $aColTypes);
    }





    function setDefaultValues ()
    {
        // Stub; this thing is just here to prevent an error message when a child class has not defined this function.
        return true;
    }





    function setRowID ($sViewListID, $sRowID)
    {
        // Set the RowID for the specified viewList & set the $_SESSION variable so that it will not keep reloading the previous ID.
        $this->sRowID = $sRowID;
        $sViewListID = str_replace('viewlistForm_', '', $sViewListID);
        if (isset($_SESSION['viewlists'][$sViewListID]['row_id'])) {
            $_SESSION['viewlists'][$sViewListID]['row_id'] = $sRowID;
        }
    }





    function setRowLink ($sViewListID, $sRowLink)
    {
        // Set the RowLink for the specified viewList & set the $_SESSION variable so that it will not keep reloading the previous link.
        $this->sRowLink = $sRowLink;
        $sViewListID = str_replace('viewlistForm_', '', $sViewListID);
        if (isset($_SESSION['viewlists'][$sViewListID]['row_link'])) {
            $_SESSION['viewlists'][$sViewListID]['row_link'] = $sRowLink;
        }
    }





    function setSortDefault ($sCol)
    {
        if (!empty($sCol) && array_key_exists($sCol, $this->aColumnsViewList)) {
            $this->sSortDefault = $sCol;
            return true;
        } else {
            return false;
        }
    }





    function unsetColsByAuthLevel ()
    {
        // Unset columns not allowed to be visible for the current user level.
        global $_AUTH;

        foreach ($this->aColumnsViewEntry as $sCol => $Col) {
            if (is_array($Col) && (!$_AUTH || $_AUTH['level'] < $Col[1])) {
                unset($this->aColumnsViewEntry[$sCol]);
            }
        }

        foreach ($this->aColumnsViewList as $sCol => $aCol) {
            if (isset($aCol['auth']) && (!$_AUTH || $_AUTH['level'] < $aCol['auth'])) {
                unset($this->aColumnsViewList[$sCol]);
            }
        }
    }





    function updateEntry ($ID, $aData, $aFields = array())
    {
        // Updates entry $ID with data from $aData in the database, changing only fields defined in $aFields.
        // $ID = Can be an integer/numeric string, or an array. If an integer/numeric string: ID to change.
        //   If an associative array (for linking tables), use array('geneid' => 'IVD', 'userid' => 1).
        // $aData = Associative array with values to be inserted. Keys should equal database column names.
        // $aFields = The keys of the associative array that will actually get updated.
        global $_DB;

        // Check to see if an ID has been passed and there is data to process.
        if ((!is_array($ID) && !trim($ID)) || (is_array($ID) && (!$ID || in_array('', array_map('trim', $ID))))) {
            lovd_displayError('LOVD-Lib', 'Objects::(' . $this->sObject . ')::updateEntry() - Method didn\'t receive ID');
        } elseif (!is_array($aData) || !count($aData)) {
            lovd_displayError('LOVD-Lib', 'Objects::(' . $this->sObject . ')::updateEntry() - Method didn\'t receive data array');
        } elseif (!is_array($aFields) || !count($aFields)) {
            $aFields = array_keys($aData);
        }

        // Prepare ID variable, to always be in the array('id' => 1) format.
        $aIDs = $ID;
        if (!is_array($ID)) {
            $aIDs = array('id' => $ID);
        }

        // Check to see if this table uses revisions.
        $bRev = defined($this->sTable . '_REV');
        $aDataOld = array(); // Will contain the current data, to compare with what we receive now.
        // Check if maybe we already were in a transaction when we started this function.
        // If so, we won't create a new transaction, nor run a commit() in the end.
        $bWasAlreadyInTransaction = (method_exists($_DB, 'inTransaction') && $_DB->inTransaction());
        if ($bRev) {
            if (!$bWasAlreadyInTransaction) {
                $_DB->beginTransaction();
            }
            // Read in the existing record from the revision table to be used to compare against the changes.
            // TODO What if we do not find any records in the revision table? Currently it will crasy with an error.
            $aDataOld = $_DB->q('SELECT * FROM ' . constant($this->sTable) . ' WHERE ' . implode(' = ? AND ', array_keys($aIDs)) . ' = ?',
                array_values($aIDs))->fetchAssoc();
        }

        // Query text.
        $sSQL = 'UPDATE ' . constant($this->sTable) . ' SET ';
        $aSQL = array();
        $i = 0;
        foreach ($aFields as $sField) {
            // But skip the IDs that may be in the list.
            if (isset($aIDs[$sField])) {
                continue;
            }
            $sSQL .= (!$i? '' : ', ') . '`' . $sField . '` = ?';
            $i ++;
            if (!isset($aData[$sField])) {
                // Field may be not set, make sure it is (happens in very rare cases).
                $aData[$sField] = '';
            }
            if ($aData[$sField] === '' && in_array(substr(lovd_getColumnType(constant($this->sTable), $sField), 0, 3), array('INT', 'DAT', 'DEC', 'FLO'))) {
                $aData[$sField] = NULL;
            }
            $aSQL[$sField] = $aData[$sField];
        }
        $sSQL .= ' WHERE ' . implode(' = ? AND ', array_keys($aIDs)) . ' = ?';
        $aSQL += $aIDs;

        // Calculate diff with past entry. We do this, to see if we need to run at all.
        // If we don't run, we don't run on any table (to prevent mismatch in edited_date between data and REV).
        // Fill the columns that are not available in $aSQL (non-updatable fields).
        $aSQLTotal = $aSQL + $aDataOld; // Doesn't overwrite existing values like array_merge() does (good in this case).
        // Find the differences between the old and new records
        $aDiff = array_diff_assoc($aSQLTotal, $aDataOld);
        // Remove the columns we are not interested in.
        unset($aDiff['edited_by'], $aDiff['edited_date']);

        // No diff? Do nothing. Don't return false, though.
        if (!$aDiff) {
            if ($bRev && !$bWasAlreadyInTransaction) {
                $_DB->commit(); // Or a rollback()? We only did a select...
            }
            return -1; // Doesn't evaluate to false, but can serve as a specific value that you can check for.
        }

        if (!defined('LOG_EVENT')) {
            define('LOG_EVENT', $this->sObject . '::updateEntry()');
        }
        $q = $_DB->q($sSQL, array_values($aSQL), true, true);

        // Setup and run the revision table SQL.
        if ($bRev) {
            // If we have passed a reason then use this as the first line otherwise just say the record was modified.
            $sReason = 'Record modified' . (empty($aData['reason'])? '' : ': ' . $aData['reason']);
            // Have we detected any differences?
            // (we could remove this check now since we returned already, but just in case we change that behavior)
            if (count($aDiff)) {
                // Loop through each of these differences and create a reason string.
                foreach ($aDiff as $sKey => $sValue) {
                    $sReason .= "\r\n" . $sKey . ': "' . $aDataOld[$sKey] . '" => "' . $sValue . '"';
                }
            } else {
                $sReason .= ', no changes detected.';
            }

            // Modify the columns relevant to the revision table.
            $aSQLTotal['reason'] = $sReason;
            $aSQLTotal['valid_from'] = (!empty($aSQLTotal['edited_date'])? $aSQLTotal['edited_date'] : date('Y-m-d H:i:s'));

            // Update the existing revision record.
            // We could just check for a valid_to of 9999-12-31, but this
            //  is more flexible if we choose a different format.
            $qRevisionUpdate = $_DB->q('
                UPDATE ' . constant($this->sTable . '_REV') . '
                SET valid_to = ?
                WHERE ' . implode(' = ? AND ', array_keys($aIDs)) . ' = ? AND valid_to >= NOW()
                ORDER BY valid_to DESC LIMIT 1',
                    array_merge(array($aSQLTotal['valid_from']), array_values($aIDs)), true, true);

            // Create the SQL for inserting a new revision record.
            $sRevSQL = 'INSERT INTO ' . constant($this->sTable . '_REV') . ' (`' . implode('`, `', array_keys($aSQLTotal)) . '`) VALUES (?' . str_repeat(', ?', count($aSQLTotal) - 1) . ')';
            $qRevisionInsert = $_DB->q($sRevSQL, array_values($aSQLTotal), true, true);

            // If any of the queries fail, then rollback and return false. Otherwise, commit.
            if (!$q || !$qRevisionInsert || !$qRevisionUpdate) {
                $_DB->rollBack();
                return false;
            } else {
                if (!$bWasAlreadyInTransaction) {
                    $_DB->commit();
                }
            }
        }
        return $q->rowCount();
    }





    function viewEntry ($ID)
    {
        // Views just one entry from the database.
        // $ID = Can be an integer/numeric string, or an array. If an integer/numeric string: ID to change.
        //   If an associative array (for linking tables), use array('geneid' => 'IVD', 'userid' => 1).
        global $_DB, $_T;

        // Check to see if an ID has been passed and there is data to process.
        if ((!is_array($ID) && !trim($ID)) || (is_array($ID) && (!$ID || in_array('', array_map('trim', $ID))))) {
            lovd_displayError('LOVD-Lib', 'Objects::(' . $this->sObject . ')::viewEntry() - Method didn\'t receive ID');
        }

        $bAjax = (substr(lovd_getProjectFile(), 0, 6) == '/ajax/');

        // Check existence of entry.
        if (!$this->entryExist($ID)) {
            lovd_showInfoTable('No such ID!', 'stop');
            if (!$bAjax) {
                $_T->printFooter();
            }
            exit;
        }

        if (!defined('LOG_EVENT')) {
            define('LOG_EVENT', $this->sObject . '::viewEntry()');
        }

        if ($this->sSQLPreViewEntry !== '') {
            $_DB->q($this->sSQLPreViewEntry);
        }

        // Because a ViewEntry query often contains many tables, find out the table's alias, if used.
        if (preg_match('/' . constant($this->sTable) . ' AS ([a-z0-9]+)\b/', $this->aSQLViewEntry['FROM'], $aRegs)) {
            // An alias was defined. Use it.
            $sTableAlias = $aRegs[1];
        } else {
            // Use the normal table name.
            $sTableAlias = constant($this->sTable);
        }

        // Prepare ID variable, to always be in the array('id' => 1) format.
        if (!is_array($ID)) {
            $sIDColumn = 'id';
            // In case a different column name is used, better use that instead.
            if (!empty($this->aColumnsViewList['id']['db'][0])) {
                $sIDColumn = $this->aColumnsViewList['id']['db'][0];
                // But take the optional table alias off.
                $sIDColumn = substr(strrchr('.' . $sIDColumn, '.'), 1);
            }
            $aIDs = array($sTableAlias . '.' . $sIDColumn => $ID);
        } else {
            // Apply the table's alias to each of the IDs.
            $aIDs = array();
            foreach ($ID as $sKey => $sValue) {
                $aIDs[$sTableAlias . '.' . $sKey] = $sValue;
            }
        }

        // Manipulate WHERE to include ID, and build query.
        $this->aSQLViewEntry['WHERE'] = implode(' = ? AND ', array_keys($aIDs)) . ' = ?' . (!$this->aSQLViewEntry['WHERE']? '' : ' AND ' . $this->aSQLViewEntry['WHERE']);
        $sSQL = 'SELECT ' . $this->aSQLViewEntry['SELECT'] .
               ' FROM ' . $this->aSQLViewEntry['FROM'] .
               ' WHERE ' . $this->aSQLViewEntry['WHERE'] .
              (!$this->aSQLViewEntry['GROUP_BY']? '' :
               ' GROUP BY ' . $this->aSQLViewEntry['GROUP_BY']);

        // Run the actual query.
        $zData = $_DB->q($sSQL, array_values($aIDs))->fetchAssoc();
        // If the user has no rights based on the statusid column, we don't have a $zData.
        if (!$zData) {
            // Don't give away information about the ID: just pretend the entry does not exist.
            lovd_showInfoTable('No such ID!', 'stop');
            $_T->printFooter();
            exit;
        }

        $zData = $this->autoExplode($zData);

        $zData = $this->prepareData($zData, 'entry');

        // Print the data.
        print('      <TABLE border="0" cellpadding="0" cellspacing="1" width="600" class="data">');
        foreach ($this->aColumnsViewEntry as $sField => $header) {
            $sHeader = (is_array($header)? $header[0] : $header);
            if (preg_match("/TableStart/", $sField)) {
                print('      <TABLE border="0" cellpadding="0" cellspacing="1" width="600" class="data">');
            } elseif (preg_match("/TableHeader/", $sField)) {
                print("\n" .
                      '        <TR>' . "\n" .
                      '          <TH colspan="2" class="S15" valign="top">' . $sHeader . '</TH></TR>');
            } elseif (preg_match("/TableEnd/", $sField)) {
                print('</TABLE>' . "\n\n");
            } elseif (preg_match("/HR/", $sField)) {
                print("\n" .
                      '      <HR>' . "\n");
            } else {
                print("\n" .
                      '        <TR>' . "\n" .
                      '          <TH valign="top">' . str_replace(' ', '&nbsp;', $sHeader) . '</TH>' . "\n" .
                      '          <TD>' . (!isset($zData[$sField]) || $zData[$sField] === ''? '-' : str_replace(array("\r\n", "\r", "\n"), '<BR>', $zData[$sField])) . '</TD></TR>');
            }
        }
        print('</TABLE>' . "\n\n");
        return $zData;
    }





    function viewList ($sViewListID = false, $aOptions = array())
    {
        // Show a viewlist for the current object.
        // Params:
        // bFindReplace     if true, find & replace option is shown in viewlist options menu.

        // Views list of entries in the database, allowing search.
        global $_AUTH, $_DB, $_INI, $_SETT;

        if (empty($aOptions) || !is_array($aOptions)) {
            $aOptions = array();
        }

        // Append existing options from session, if available.
        if (isset($_SESSION['viewlists'][$sViewListID]['options'])) {
            $aOptions = array_replace($_SESSION['viewlists'][$sViewListID]['options'], $aOptions);
        }

        // Append defaults for any remaining options.
        $aOptions = array_replace(
            array(
                'cols_to_skip' => array(),      // Names of columns to hide.
                'track_history' => true,        // Track actions of AJAX VL requests in the browser history.
                'show_navigation' => true,      // Show paging and navigation buttons.
                'show_options' => false,        // Show options menu.
                'only_rows' => false,           // Only put the table rows in the output.
                'find_and_replace' => false,    // Allow find and replace on columns.
                'multi_value_filter' => false,  // Allow multi valued search on columns.
                'curate_set' => false,          // Allow "curate entries" option.
                'merge_set' => false,           // Allow "merge entries" option.
            ),
            $aOptions);

        // Disallow options when options menu is hidden.
        $aOptions['find_and_replace'] &= $aOptions['show_options'];
        $aOptions['multi_value_filter'] &= $aOptions['show_options'];
        $aOptions['curate_set'] &= $aOptions['show_options'];
        $aOptions['merge_set'] &= $aOptions['show_options'];

        // Save viewlist options to session.
        $_SESSION['viewlists'][$sViewListID]['options'] = array_merge(
            $aOptions,
            array(
                'only_rows' => false, // only_rows should never be stored in SESSION.
            ));

        if (!defined('LOG_EVENT')) {
           define('LOG_EVENT', $this->sObject . '::viewList()');
        }
        if (FORMAT == 'text/plain' && !defined('FORMAT_ALLOW_TEXTPLAIN')) {
            die('text/plain not allowed here');
        }

        $bAjax = (substr(lovd_getProjectFile(), 0, 6) == '/ajax/');

        // ViewLists need an ID to identify the specific viewList, in case there are a few in one document.
        if (!$sViewListID || !is_string($sViewListID)) {
            $sViewListID = lovd_generateRandomID();
        } else {
            $sViewListID = preg_replace('/[^A-Z0-9._-]+/i', '', $sViewListID);
        }

        if (!is_array($aOptions['cols_to_skip'])) {
            $aOptions['cols_to_skip'] = array($aOptions['cols_to_skip']);
        }
        foreach ($this->aColumnsViewList as $sCol => $aCol) {
            if (!$aCol['view'] && !in_array($sCol, $aOptions['cols_to_skip'])) {
                $aOptions['cols_to_skip'][] = $sCol;
            }
        }

        require_once ROOT_PATH . 'inc-lib-viewlist.php';

        // First, check if entries are in the database at all.
        if (!$this->entryExist() && FORMAT == 'text/html') {
            if ($aOptions['only_rows']) {
                die('0'); // Silent error.
            }
            lovd_showInfoTable('No entries in the database yet!', 'stop');
            return 0;
        }

        // Process search fields (i.e. $_GET['search_...'] values) for viewlist.
        list($WHERE, $HAVING, $aArguments, $aBadSyntaxColumns, $aColTypes) = $this->processViewListSearchArgs($_GET);
        if ($WHERE) {
            $this->aSQLViewList['WHERE'] .= ($this->aSQLViewList['WHERE']? ' AND ' : '') . $WHERE;
        }
        if ($HAVING) {
            $this->aSQLViewList['HAVING'] .= ($this->aSQLViewList['HAVING']? ' AND ' : '') . $HAVING;
        }

        // SORT: Current settings, also implementing XSS check.
        if (!empty($_GET['order']) && $_GET['order'] === strip_tags($_GET['order'])) {
            $aOrder = array_pad(explode(',', $_GET['order']), 2, '');
        } else {
            $aOrder = array('', '');
        }

        // SORT: Verify request and set default.
        if (empty($this->aColumnsViewList[$aOrder[0]]['db'][1])) {
            $aOrder[0] = $this->sSortDefault;
        }
        if ($aOrder[1] != 'ASC' && $aOrder[1] != 'DESC') {
            $aOrder[1] = $this->aColumnsViewList[$aOrder[0]]['db'][1];
        }

        $sSQLOrderBy = $this->aColumnsViewList[$aOrder[0]]['db'][0] . ' ' . $aOrder[1];
        if (preg_match('/^\w\./', $this->aColumnsViewList[$aOrder[0]]['db'][0]) // Only for normal columns.
            && preg_match('/AS\s+`?' . preg_quote($aOrder[0], '/') . '`?\b/i', $this->aSQLViewList['SELECT'])) {
            // Current field name is present as an alias in SELECT clause, use
            // this instead in the ORDER BY clause. (needed for aggregated
            // fields)
            // But we should *only* do this when the db field was just a given column ("table.column").
            $sSQLOrderBy = '`' . trim($aOrder[0], '`') . '` ' . $aOrder[1];
        }

        if (in_array($aOrder[0], array('chromosome', 'VariantOnGenome/DNA'))) {
            // 2014-03-07; 3.0-10; We need to find the table alias of the VOG or genes table, because otherwise MySQL fails here ('chromosome' is ambiguous) if both are joined.
            // 2014-04-28; 3.0-10; Prefer the genes table, since it joins to VOG as well, but may not have results which messes up the order.
            $sAlias = '';
            if (preg_match('/' . TABLE_GENES . ' AS ([a-z]+)/i', $this->aSQLViewList['FROM'], $aRegs)) {
                $sAlias = $aRegs[1];
            } elseif (preg_match('/' . TABLE_VARIANTS . ' AS ([a-z]+)/i', $this->aSQLViewList['FROM'], $aRegs)) {
                $sAlias = $aRegs[1];
            }
            if (!$_SETT['customization_settings']['variants_VL_quick_dirty_sort']) {
                // The quick dirty sort sorts on chromosomes alphabetically to use the VOG's keys.
                // It's ugly because 2 sorts after 10-19, but it's 1000x faster if also the sort on VOG/DNA is dropped (which we do below).
                $this->aSQLViewList['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_CHROMOSOMES . ' AS chr ON (' . (!$sAlias? '' : $sAlias . '.') . 'chromosome = chr.name)';
                $sSQLOrderBy = 'chr.sort_id ' . $aOrder[1];
            } else {
                $sSQLOrderBy = $sAlias . '.chromosome ' . $aOrder[1];
            }
            if ($aOrder[0] == 'VariantOnGenome/DNA') {
                $sSQLOrderBy .= ', position_g_start ' . $aOrder[1] . ', position_g_end ' . $aOrder[1];
                if (!$_SETT['customization_settings']['variants_VL_quick_dirty_sort']) {
                    // Only actually sort on VOG/DNA if we're not dirty-sorting.
                    $sSQLOrderBy .= ', `VariantOnGenome/DNA` ' . $aOrder[1];
                }
            }
        } elseif ($aOrder[0] == 'VariantOnTranscript/DNA') {
            $sSQLOrderBy = 'position_c_start ' . $aOrder[1] . ', position_c_start_intron ' . $aOrder[1] . ', position_c_end ' . $aOrder[1] . ', position_c_end_intron ' . $aOrder[1];
            if (!$_SETT['customization_settings']['variants_VL_quick_dirty_sort']) {
                // Only actually sort on VOT/DNA if we're not dirty-sorting.
                $sSQLOrderBy .= ', `VariantOnTranscript/DNA` ' . $aOrder[1];
            }
        }
        // At this point, we're not sure if we'll actually use the ORDER BY at all.
        $this->aSQLViewList['ORDER_BY'] = $sSQLOrderBy . (empty($this->aSQLViewList['ORDER_BY'])? '' : ', ' . $this->aSQLViewList['ORDER_BY']);



        // Only print stuff if we're not in Ajax right now.
        if (!$bAjax && FORMAT == 'text/html') {
            // Keep the URL clean; disable any fields that are not used.
            lovd_includeJS('inc-js-viewlist.php' . ($aOptions['track_history']? '' : '?nohistory'));
            lovd_includeJS('inc-js-tooltip.php');

            // Print form; required for sorting and searching.
            // Because we don't want the form to submit itself while we are waiting for the Ajax response, we need to kill the native submit() functionality.
            print('      <FORM action="' . CURRENT_PATH . '" method="get" id="viewlistForm_' . $sViewListID . '" style="margin : 0px;" onsubmit="return false;">' . "\n" .
                  '        <INPUT type="hidden" name="viewlistid" value="' . $sViewListID . '">' . "\n" .
                  '        <INPUT type="hidden" name="object" value="' . $this->sObject . '">' . "\n" .
                  (!isset($this->sObjectID)? '' :
                  '        <INPUT type="hidden" name="object_id" value="' . $this->sObjectID . '">' . "\n") . // The ID of the gene for VOT viewLists, the ID of the disease for phenotype viewLists, the object list for custom viewLists.
                  (!isset($this->nID)? '' :
                  '        <INPUT type="hidden" name="id" value="' . $this->nID . '">' . "\n") . // The ID of the VOG for VOT viewLists, the ID of the individual for phenotype viewLists, the (optional) gene for custom viewLists.
// FIXME; do we ever use ACTION in a ViewList? Wait until we've made variants.php to know for sure.
// FIXME; if we do need to send action, we can't do it this way... URL?action=&bla=bla does not get ACTION recognized.
                  (!ACTION? '' :
                  '        <INPUT type="hidden" name="' . ACTION . '" value="">' . "\n") .
                  '        <INPUT type="hidden" name="order" value="' . implode(',', $aOrder) . '">' . "\n" .
                  '        <INPUT type="hidden" name="MVSCols" value="' . (!isset($_GET['MVSCols'])?
                      '' : htmlspecialchars($_GET['MVSCols'])) . '">' . "\n");
            // Skipping (permanently hiding) columns.
            foreach ($aOptions['cols_to_skip'] as $sCol) {
                // Check if we're skipping columns, that do have a search value. If so, it needs to be sent on like this.
                if (array_key_exists($sCol, $this->aColumnsViewList) && isset($_GET['search_' . $sCol])) {
                    print('        <INPUT type="hidden" name="search_' . $sCol . '" value="' . htmlspecialchars($_GET['search_' . $sCol]) . '">' . "\n");
                }
            }
            print("\n");
        }

        // Make a reference variable of the session for cleaner code.
        $aSessionViewList =& $_SESSION['viewlists'][$sViewListID];

        // To make row ids persist when the viewList is refreshed, we must store the row id in $_SESSION.
        if (!empty($aSessionViewList['row_id'])) {
            $this->sRowID = $aSessionViewList['row_id'];
        } else {
            $aSessionViewList['row_id'] = $this->sRowID; // Implies array creation.
        }

        // To make row links persist when the viewList is refreshed, we must store the row link in $_SESSION.
        if (!empty($aSessionViewList['row_link'])) {
            $this->sRowLink = $aSessionViewList['row_link'];
        } else {
            $aSessionViewList['row_link'] = $this->sRowLink; // Implies array creation.
        }

        // Process input values regarding find & replace.
        // User clicked preview.
        $bFRPreview =          (!empty($_GET['FRPreviewClicked_' . $sViewListID]));
        // Selected field name for replace.
        $sFRViewListCol =      (isset($_GET['FRFieldname_' . $sViewListID])?
                                $_GET['FRFieldname_' . $sViewListID] : '');
        // Display name of selected field.
        $sFRFieldDisplayname = (isset($_GET['FRFieldDisplayname_' . $sViewListID])?
                                $_GET['FRFieldDisplayname_' . $sViewListID] : '');
        // Search query for find & replace.
        $sFRSearchValue =      (isset($_GET['FRSearch_' . $sViewListID])?
                                $_GET['FRSearch_' . $sViewListID] : '');
        // Replace value for find & replace.
        $sFRReplaceValue =     (isset($_GET['FRReplace_' . $sViewListID])?
                                $_GET['FRReplace_' . $sViewListID] : '');
        // Type of matching.
        $sFRMatchType =        (isset($_GET['FRMatchType_' . $sViewListID])?
                                $_GET['FRMatchType_' . $sViewListID] : null);
        // Flag stating whether all field content sould be replaced.
        $bFRReplaceAll =       (!empty($_GET['FRReplaceAll_' . $sViewListID]));
        // Predicted affected row count.
        $nFRRowsAffected = null;
        // Find & replace options parameter.
        $aFROptions = array(
            'sFRMatchType' =>   $sFRMatchType,
            'bFRReplaceAll' =>  $bFRReplaceAll
        );

        $nTotal = 0;
        if (!count($aBadSyntaxColumns)) {
            // Build argument list.
            $aArgs = array_merge($aArguments['WHERE'], $aArguments['HAVING']);

            if ($bFRPreview) {
                // User clicked 'preview' in Find&Replace form, add F&R changes as a separate
                // column in the query.
                $nFRRowsAffected = $this->previewColumnFindAndReplace($sFRViewListCol,
                    $sFRFieldDisplayname, $sFRSearchValue, $sFRReplaceValue, $aArgs, $aFROptions);
            }

            // First find the amount of rows returned. We can use the SQL_CALC_FOUND_ROWS()
            // function, but we'll try to avoid that due to extreme slowness in some cases.
            // getRowCountForViewList() will take care of that.
            // We used to have a 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' here which
            //  made sure the VL result count matched the VL results, but as the two queries
            //  might need other tables to be locked, it could cause deadlocks. Removing the
            //  isolation level setting, to prevent deadlocks from happening. A possible
            //  difference in VL result count and the actual results is hypothetical only anyway.
            $_DB->beginTransaction();

            // For ALL viewlists, we store the number of hits that we get, including the current filters.
            // For large tables, getting a count can take a long time (especially when using SQL_CALC_FOUND_ROWS).
            // ORDER BY is absolutely killing on large result sets.
            // So, long time to retrieve count (>1s) => don't count again, and no sort.
            // Count OK (<=1s), but big result set (250K) => no sort. ($_SETT['lists']['max_sortable_rows'])

            // 1) If we don't have a count in memory, request count separately.
            // Also if last count was >15min ago, request again.
            $bTrueCount = false; // Indicates whether or not we are sure about the number of results.
            // FIXME: this has to be done better, we can't see what we're filtering for, because it's in the arguments!
            $sFilterMD5 = md5($this->aSQLViewList['WHERE'] . '||' . $this->aSQLViewList['HAVING'] . '||' . implode('|', $aArgs)); // A signature for the filters, NOTE that this depends on the column order!
            // FIXME: If this count takes longer than 1s, we don't estimate anymore like we used to (see line 1543).
            if (true || !isset($aSessionViewList['counts'][$sFilterMD5]['n'])) {
                $t = microtime(true);
                // Now, get the total number of hits if no LIMIT was used. Note that $nTotal gets overwritten here.
                $nTotal = $this->getRowCountForViewList($this->aSQLViewList, $aArgs);
                $tQ = microtime(true) - $t;
                $aSessionViewList['counts'][$sFilterMD5]['n'] = $nTotal;
                $aSessionViewList['counts'][$sFilterMD5]['t'] = $tQ;
                $aSessionViewList['counts'][$sFilterMD5]['d'] = time();
                $bTrueCount = true;
            }



            // Manipulate SELECT to include SQL_CALC_FOUND_ROWS.
            $bSQLCALCFOUNDROWS = false;
            // TODO: Remove this block. For now, this will be bypassed because $bTrueCount will always be true.
            if (!$bTrueCount && $_INI['database']['driver'] == 'mysql' && ($aSessionViewList['counts'][$sFilterMD5]['t'] < 1 || $aSessionViewList['counts'][$sFilterMD5]['d'] < (time() - (60*15)))) {
                // But only if we're using MySQL and it takes less than a second to get the correct number of results, or it's been more than 15 minutes since the last check!
                $this->aSQLViewList['SELECT'] = 'SQL_CALC_FOUND_ROWS ' . $this->aSQLViewList['SELECT'];
                $bSQLCALCFOUNDROWS = true;
            }

            if ($aOptions['show_options']) {
                // If the session variable does not exist, create it!
                if (!isset($aSessionViewList['checked'])) {
                    $aSessionViewList['checked'] = array();
                }

                if (isset($_GET['ids_changed'])) {
                    if ($_GET['ids_changed'] == 'all') {
                        // If the select all button was clicked, fetch all entries and mark them as 'checked' in session.
                        // This query is the same as the viewList query, but without the ORDER BY and LIMIT, so that we can get the full result
                        // of the query.
                        $sSQL = $this->buildSQL(array(
                            'SELECT' => $this->aSQLViewList['SELECT'],
                            'FROM' => $this->aSQLViewList['FROM'],
                            'WHERE' => $this->aSQLViewList['WHERE'],
                            'GROUP_BY' => $this->aSQLViewList['GROUP_BY'],
                            'HAVING' => $this->aSQLViewList['HAVING'],
                        ));
                        $q = $_DB->q($sSQL, $aArgs);
                        while ($zData = $q->fetchAssoc()) {
                            $zData = $this->generateRowID($zData);
                            // We only need the row_id here for knowing which ones we need to check.
                            // 2015-09-18; 3.0-14; We need to run rawurldecode() or else Columns are not selectable this way.
                            $aSessionViewList['checked'][] = rawurldecode($zData['row_id']);
                        }
                        // Now, unique() the list since we don't want to keep adding the same IDs.
                        $aSessionViewList['checked'] = array_unique($aSessionViewList['checked']);
                    } elseif ($_GET['ids_changed'] == 'none') {
                        // If the unselect all button was clicked, reset the 'checked' array.
                        $aSessionViewList['checked'] = array();
                    } else {
                        // Get the changed ids and remove them from or add them to the session.
                        $aIDsChanged = explode(';', $_GET['ids_changed']);
                        // Flip the keys & values, so that we can do a simple isset() to see if the id is already present.
                        $aSessionViewList['checked'] = array_flip($aSessionViewList['checked']);
                        // Determine the highest key number, so we can use that later when adding new values to the array.
                        $nIndex = (count($aSessionViewList['checked'])? max($aSessionViewList['checked']) + 1 : 0);
                        foreach ($aIDsChanged as $nID) {
                            if (isset($aSessionViewList['checked'][$nID])) {
                                // ID is found in the array, but is also in the 'ids_changed' array, so remove it!
                                unset($aSessionViewList['checked'][$nID]);
                            } else {
                                // ID is not found in the array, but IS in the 'ids_changed' array, so add it using the $nIndex as value we determined earlier.
                                // Also add 1 to the $nIndex so that the next id that needs to be added will not overwrite this one.
                                $aSessionViewList['checked'][$nID] = ++$nIndex;
                            }
                        }
                        // Flip the array back to its original state.
                        $aSessionViewList['checked'] = array_flip($aSessionViewList['checked']);
                    }
                }
            }

            // ORDER BY will only occur when we estimate we have time for it.
            if ($aSessionViewList['counts'][$sFilterMD5]['t'] < 1 && $aSessionViewList['counts'][$sFilterMD5]['n'] <= $_SETT['lists']['max_sortable_rows']) {
                $bSortableVL = true;
            } else {
                // Not sortable, indicate this on the VL...
                $aOrder = array('', '');
                $bSortableVL = false;
                // 2013-07-03; 3.0-07; However, we do try and sort because in principle, the order is random and this may cause confusion while paginating.
                //   So, as a result we'll try and sort on the PK. We attempt to determine this from the GROUP BY or ID col in the VL columns list.
                $sCol = '';
                if (isset($this->aSQLViewList['GROUP_BY'])) {
                    $sCol = $this->aSQLViewList['GROUP_BY'];
                } elseif ($this->aColumnsViewList['id']) {
                    $sCol = $this->aColumnsViewList['id']['db'][0];
                } elseif ($this->aColumnsViewList['id_']) {
                    $sCol = $this->aColumnsViewList['id_']['db'][0];
                }
                $this->aSQLViewList['ORDER_BY'] = $sCol;
            }

            if ($aOptions['show_navigation'] && FORMAT == 'text/html') {
                // Implement LIMIT only if navigation is not hidden.
                // We have a problem here, because we don't know how many hits there are,
                // because we're using SQL_CALC_FOUND_ROWS which only gives us the number
                // of hits AFTER we run the whole query. This means we should just assume
                // the page number is possible.
                $this->aSQLViewList['LIMIT'] = lovd_pagesplitInit(); // Function requires variable names $_GET['page'] and $_GET['page_size'].
            }

            $sSQL = $this->buildSQL($this->aSQLViewList);

            // Run the viewList query.
            // FIXME; what if using AJAX? Probably we should generate a number here, if this query fails, telling the system to try once more. If that fails also, the JS should throw a general error, maybe.
            $q = $_DB->q($sSQL, $aArgs);

            // Now, get the total number of hits as if no LIMIT was used (when we have used the proper SELECT syntax). Note that $nTotal gets overwritten here.
            if ($bSQLCALCFOUNDROWS) {
                // FIXME: 't' needs to be recalculated as well!
                $nTotal = $_DB->q('SELECT FOUND_ROWS()')->fetchColumn();
                $aSessionViewList['counts'][$sFilterMD5]['n'] = $nTotal;
                $aSessionViewList['counts'][$sFilterMD5]['d'] = time();
                $bTrueCount = true;
            } else {
                // Estimate the number of results!
                $nTotal = $aSessionViewList['counts'][$sFilterMD5]['n'];
            }
            $_DB->commit(); // To end the transaction and the locks that come with it.
        } else {
            // Set certain values that are needed for hiding notices, applicable for the "incorrect syntax" error message.
            $bTrueCount = true; // Yes, we're sure we have 0 results.
            $bSortableVL = false; // Sorting makes no sense when you have no results.
        }

        // If no results are found, try to figure out if it was because of the user's searching or not.
        if (!$nTotal) {
            $bSearched = false;
            $aSearchOnSkippedCols = array();
            $bSearchNonViewableCol = true;
            foreach ($_GET as $key => $value) {
                if (substr($key, 0, 7) == 'search_') {
                    $sColumn = substr($key, 7);
                    if (!in_array($sColumn, $aOptions['cols_to_skip'])) {
                        // All visible columns that have been searched on ($aOptions['cols_to_skip'] also
                        // includes everything in aColumnsViewList with false for 'view' key).
                        // I.e. anything that can be searched by the user.
                        $bSearched = true;
                    } elseif ($this->aColumnsViewList[$sColumn]['view']) {
                        // Search on hidden columns that have display information in aColumnsViewList,
                        //  i.e. columns in the $aOptions['cols_to_skip'] argument to the ViewList function.
                        $sColHeader = $this->aColumnsViewList[$sColumn]['view'][0];
                        // Make sure all hidden ID columns have "ID" in the header, so we can recognize them.
                        if (substr(rtrim($sColumn, '_'), -2) == 'id' && substr($sColHeader, -3) != ' ID') {
                            $sColHeader .= ' ID';
                        }
                        $aSearchOnSkippedCols[$sColHeader] = $value;
                    } else {
                        // Search on hidden columns that have no display information in
                        //  aColumnsViewList, so columns that are meant to always be hidden.
                        $bSearchNonViewableCol = true;
                    }
                }
            }
        }

        // FIXME; this is a temporary hack just to get the genes?authorize working when all users have been selected.
        //   There is no longer a viewList when all users have been selected, but we need one for the JS execution.
        //   Possibly, this code can be standardized a bit and, if necessary for other viewLists as well, can be kept here.
        if (!$nTotal && !$bSearched && (($this->sObject == 'User' && !empty($_GET['search_userid'])))) {
            // FIXME; Maybe check for JS contents of the rowlink?
            // There has been searched, but apparently the ID column is forced hidden. This must be the authorize page.
            $bSearched = true; // This will trigger the creation of the viewList table.
        }

        if (FORMAT == 'text/html' && ($nTotal || $bSearched)) {
            // Unpack MVSCols, used to give CSS class to HTML TD tags.
            $aRequestMVSCols = array();
            if (isset($_GET['MVSCols'])) {
                $aRequestMVSCols = explode(';', $_GET['MVSCols']);
            }

            // Only print stuff if we're not just loading one entry right now.
            if (!$aOptions['only_rows']) {
                if (!$bAjax) {
                    print('      <DIV id="viewlistDiv_' . $sViewListID . '">' . "\n"); // These contents will be replaced by Ajax.
                }

                // If we have a legend, create a hidden DIV that will be used for the full legend.
                print('      <DIV id="viewlistLegend_' . $sViewListID . '" title="Legend" style="display : none;">' . "\n" .
                      '        <H2 class="LOVD">Legend</H2>' . "\n\n" .
                      '        <I class="S11">Please note that a short description of a certain column can be displayed when you move your mouse cursor over the column\'s header and hold it still. Below, a more detailed description is shown per column.</I><BR><BR>' . "\n\n");
                $bLegend = false; // We need to check if we have a legend at all.
                foreach ($this->aColumnsViewList as $sField => $aCol) {
                    if (!empty($aCol['legend'])) {
                        $bLegend = true;
                        if (empty($aCol['legend'][1])) {
                            $aCol['legend'][1] = $aCol['legend'][0];
                        }
                        print('        <B>' . strip_tags($aCol['view'][0]) . '</B>: ' . $aCol['legend'][1]);
                        if (substr($aCol['legend'][1], -5) == '</UL>') {
                            // No additional breaks, no possible listing of selection options. Column has its own UL already.
                            print("\n\n");
                            continue;
                        }
                        if (isset($this->aColumns[$sField]) && $this->aColumns[$sField]['form_type'][2] == 'select') {
                            // This is a custom column and it has a selection list with options. List the options below.
                            print('<BR>' . "\n" .
                                  '        All options:' . "\n" .
                                  '        <UL style="margin-top : 0px;">' . "\n");
                            foreach ($this->aColumns[$sField]['select_options'] as $sOption) {
                                print('          <LI>' . $sOption . '</LI>' . "\n");
                            }
                            print('      </UL>' . "\n\n");
                        } else {
                            print('<BR><BR>' . "\n\n");
                        }
                    }
                }
                print('      </DIV>' . "\n\n");

                // Add an info DIV about how to query. Print it only once.
                if (!defined('PrintedViewListHowToQuery')) {
                    define('PrintedViewListHowToQuery', true);
                    print('
        <DIV id="viewlistHowToQuery" title="How to query this table" style="display : none;">
          <H2 class="LOVD">How to query this table</H2>
          All list views have search fields which can be used to search data.
          You can search for a complete word or you can search for a part of a search term.
          If you enclose two or more words in double quotes, LOVD will search for the combination of those words only exactly in the order you specify.
          Note that search terms are case-insensitive and that wildcards such as * are treated as normal text!
          For all options, like &quot;and&quot;, &quot;or&quot;, and &quot;not&quot; searches, or searching for prefixes or suffixes, see the table below.<BR>
          <BR>

          <TABLE cellspacing="0" width="750">
            <TR>
              <TH style="border-bottom: 1px solid black;">Operator</TH>
              <TH style="border-bottom: 1px solid black;">Column type</TH>
              <TH style="border-bottom: 1px solid black;">Example</TH>
              <TH style="border-bottom: 1px solid black;">Matches</TH></TR>
            <TR>
              <TH>&nbsp;</TH>
              <TD>Text</TD>
              <TD>Arg</TD>
              <TD>all entries containing \'Arg\'</TD></TR>
            <TR>
              <TH>space</TH>
              <TD>Text</TD>
              <TD>Arg Ser</TD>
              <TD>all entries containing \'Arg\' and \'Ser\'</TD></TR>
            <TR>
              <TH>|</TH>
              <TD>Text</TD>
              <TD>Arg|Ser</TD>
              <TD>all entries containing \'Arg\' or \'Ser\'</TD></TR>
            <TR>
              <TH>!</TH>
              <TD>Text</TD>
              <TD>!fs</TD>
              <TD>all entries not containing \'fs\'</TD></TR>
            <TR>
              <TH>^</TH>
              <TD>Text</TD>
              <TD>^p.(Arg</TD>
              <TD>all entries beginning with \'p.(Arg\'</TD></TR>
            <TR>
              <TH>$</TH>
              <TD>Text</TD>
              <TD>Ser)$</TD>
              <TD>all entries ending with \'Ser)\'</TD></TR>
            <TR>
              <TH>=&quot;&quot;</TH>
              <TD>Text</TD>
              <TD>=&quot;&quot;</TD>
              <TD>all entries with this field empty</TD></TR>
            <TR>
              <TH>=&quot;&quot;</TH>
              <TD>Text</TD>
              <TD>=&quot;p.0&quot;</TD>
              <TD>all entries exactly matching \'p.0\'</TD></TR>
            <TR>
              <TH>!=&quot;&quot;</TH>
              <TD>Text</TD>
              <TD>!=&quot;&quot;</TD>
              <TD>all entries with this field not empty</TD></TR>
            <TR>
              <TH>!=&quot;&quot;</TH>
              <TD>Text</TD>
              <TD>!=&quot;p.0&quot;</TD>
              <TD>all entries not exactly matching \'p.0?\'</TD></TR>
            <TR>
              <TH>combination</TH>
              <TD>Text</TD>
              <TD>*|Ter !fs</TD>
              <TD>all entries containing \'*\' or \'Ter\' but not containing \'fs\'</TD></TR>
            <TR>
              <TH style="border-top: 1px solid black;"></TH>
              <TD style="border-top: 1px solid black;">Date</TD>
              <TD style="border-top: 1px solid black;">2020</TD>
              <TD style="border-top: 1px solid black;">all entries matching the year 2020</TD></TR>
            <TR>
              <TH>|</TH>
              <TD>Date</TD>
              <TD>2020-03|2020-04</TD>
              <TD>all entries matching March or April, 2020</TD></TR>
            <TR>
              <TH>!</TH>
              <TD>Date</TD>
              <TD>!2020-03</TD>
              <TD>all entries not matching March, 2020</TD></TR>
            <TR>
              <TH>&lt;</TH>
              <TD>Date</TD>
              <TD>&lt;2020</TD>
              <TD>all entries before the year 2020</TD></TR>
            <TR>
              <TH>&lt;=</TH>
              <TD>Date</TD>
              <TD>&lt;=2020-06</TD>
              <TD>all entries in or before June, 2020</TD></TR>
            <TR>
              <TH>&gt;</TH>
              <TD>Date</TD>
              <TD>&gt;2020-06</TD>
              <TD>all entries after June, 2020</TD></TR>
            <TR>
              <TH>&gt;=</TH>
              <TD>Date</TD>
              <TD>&gt;=2020-06-15</TD>
              <TD>all entries on or after June 15th, 2020</TD></TR>
            <TR>
              <TH>combination</TH>
              <TD>Date</TD>
              <TD>2019|2020 &lt;2020-03</TD>
              <TD>all entries in 2019 or 2020, and before March, 2020</TD></TR>
            <TR>
              <TH style="border-top: 1px solid black;"></TH>
              <TD style="border-top: 1px solid black;">Numeric</TD>
              <TD style="border-top: 1px solid black;">23</TD>
              <TD style="border-top: 1px solid black;">all entries exactly matching 23</TD></TR>
            <TR>
              <TH>|</TH>
              <TD>Numeric</TD>
              <TD>23|24</TD>
              <TD>all entries exactly matching 23 or 24</TD></TR>
            <TR>
              <TH>!</TH>
              <TD>Numeric</TD>
              <TD>!23</TD>
              <TD>all entries not exactly matching 23</TD></TR>
            <TR>
              <TH>&lt;</TH>
              <TD>Numeric</TD>
              <TD>&lt;23</TD>
              <TD>all entries lower than 23</TD></TR>
            <TR>
              <TH>&lt;=</TH>
              <TD>Numeric</TD>
              <TD>&lt;=23</TD>
              <TD>all entries lower than, or equal to, 23</TD></TR>
            <TR>
              <TH>&gt;</TH>
              <TD>Numeric</TD>
              <TD>&gt;23</TD>
              <TD>all entries higher than 23</TD></TR>
            <TR>
              <TH>&gt;=</TH>
              <TD>Numeric</TD>
              <TD>&gt;=23</TD>
              <TD>all entries higher than, or equal to, 23</TD></TR>
            <TR>
              <TH>combination</TH>
              <TD>Numeric</TD>
              <TD>&gt;=20 &lt;30 !23</TD>
              <TD>all entries with values from 20 to 29, but not equal to 23</TD></TR></TABLE><BR><BR>

          Some more advanced examples:<BR><BR>
          <TABLE cellspacing="0" width="750">
            <TR>
              <TH style="border-bottom: 1px solid black;">Example</TH>
              <TH style="border-bottom: 1px solid black;">Matches</TH></TR>
            <TR>
              <TH>Asian</TH>
              <TD>all entries containing \'Asian\', \'asian\', including \'Caucasian\', \'caucasian\', etc.</TD></TR>
            <TR>
              <TH>Asian !Caucasian</TH>
              <TD>all entries containing \'Asian\' but not containing \'Caucasian\'</TD></TR>
            <TR>
              <TH>Asian|African !Caucasian</TH>
              <TD>all entries containing \'Asian\' or \'African\', but not containing \'Caucasian\'</TD></TR>
            <TR>
              <TH>&quot;South Asian&quot;</TH>
              <TD>all entries containing \'South Asian\', but not containing \'South East Asian\'</TD></TR></TABLE><BR><BR>

          To sort on a certain column, click on the column header or on the arrows.
          If that column is already selected to sort on, the sort order will be swapped.
          The column currently sorted on has a darker blue background color than the other columns.
          The up and down arrows next to the column name indicate the current sorting direction.
          When sorting on any field other than the default, LOVD will sort secondarily on the default sort column.
        </DIV>' . "\n\n");
                }

                if ($aOptions['show_navigation']) {
                    lovd_pagesplitInit(); // Has not run yet when we have bad search syntax.
                    lovd_pagesplitShowNav($sViewListID, $nTotal, $bTrueCount, $bSortableVL, $bLegend);
                }

                // 'checked' attribute values for find & replace menu options.
                $sFRMatchtypeCheck1 = (!isset($sFRMatchType) || $sFRMatchType == '1'? 'checked' : '');
                $sFRMatchtypeCheck2 = ($sFRMatchType == '2'? 'checked' : '');
                $sFRMatchtypeCheck3 = ($sFRMatchType == '3'? 'checked' : '');
                $sFRReplaceAllCheck = ($bFRReplaceAll? 'checked' : '');
                $sFRRowsAffected = (!is_null($nFRRowsAffected)? strval($nFRRowsAffected) : '');
                $sFRViewListCol = htmlspecialchars($sFRViewListCol);
                $sFRFieldDisplayname = htmlspecialchars($sFRFieldDisplayname);
                $sFRSearchValue = htmlspecialchars($sFRSearchValue);
                $sFRReplaceValue = htmlspecialchars($sFRReplaceValue);

                // Print options menu for find & replace (hidden by default).
                if ($aOptions['find_and_replace']) {
                    print(<<<FROptions
<DIV id="viewlistFRFormContainer_$sViewListID" class="fnroptionsmenu" style="display: none;">
    <SPAN><B>Note that using Find &amp; Replace may have destructive consequences.<BR>Make a download or backup of the data you're about to edit. If uncertain, use the edit form of the data entries instead.</B><BR><BR>
        Applying find &amp; replace to column
        &quot;<B id="viewlistFRColDisplay_$sViewListID">$sFRViewListCol</B>&quot;.
        <INPUT id="FRFieldname_$sViewListID" type="hidden" name="FRFieldname_$sViewListID"
               value="$sFRViewListCol" />
        <INPUT id="FRFieldDisplayname_$sViewListID" type="hidden"
               name="FRFieldDisplayname_$sViewListID" value="$sFRFieldDisplayname" />
        <INPUT id="FRRowsAffected_$sViewListID" type="hidden" value="$sFRRowsAffected" />
    </SPAN>
    <BR />
    <TABLE>
        <TR>
            <TD>Text to find</TD>
            <TD>
                <INPUT type="text" name="FRSearch_$sViewListID" value="$sFRSearchValue"
                       style="width: 110px" />
            </TD>
            <TD>
                <INPUT type="radio" name="FRMatchType_$sViewListID" value="1" $sFRMatchtypeCheck1 />Match anywhere
                <INPUT type="radio" name="FRMatchType_$sViewListID" value="2" $sFRMatchtypeCheck2 />Match at beginning of field
                <INPUT type="radio" name="FRMatchType_$sViewListID" value="3" $sFRMatchtypeCheck3 />Match at end of field
            </TD>
        </TR>
        <TR>
            <TD>Replace with</TD>
            <TD>
                <INPUT type="text" name="FRReplace_$sViewListID" value="$sFRReplaceValue"
                       style="width: 110px" />
            </TD>
            <TD>
                <INPUT type="checkbox" name="FRReplaceAll_$sViewListID" value="1" $sFRReplaceAllCheck />Replace everything in field
            </TD>
        </TR>
    </TABLE>
    <INPUT id="FRPreview_$sViewListID" type="button" value="Preview" />
    <INPUT id="FRCancel_$sViewListID" type="button" value="Cancel" style="border : 1px solid #FF4422;" />
    <DIV id="FRSubmitDiv_$sViewListID">
        <BR>
        Enter your password to apply find and replace:<BR> 
        <INPUT type="password" name="password" size="20" />
        <INPUT name="FRSubmit_$sViewListID" id="FRSubmit_$sViewListID" type="submit" value="Submit" />
    </DIV>
</DIV>
FROptions
                    );
                }

                // Table and search headers (if applicable).
                print('      <TABLE border="0" cellpadding="0" cellspacing="1" class="data" id="viewlistTable_' . $sViewListID . '">' . "\n" .
                      '        <THEAD>' . "\n" .
                      '        <TR>' .
   ($aOptions['show_options']? "\n" . '          <TH valign="center" style="text-align:center;">' . "\n" .
                      '            <IMG id="viewlistOptionsButton_' . $sViewListID . '" src="gfx/options.png" width="16" height="16" style="cursor : pointer;"></TH>' : ''));

                foreach ($this->aColumnsViewList as $sField => $aCol) {
                    if (in_array($sField, $aOptions['cols_to_skip'])) {
                        continue;
                    }

                    $bSortable   = !empty($aCol['db'][1]) && $bSortableVL; // If we can't sort at all, nothing is sortable.
                    $bSearchable = !empty($aCol['db'][2]);
                    $nAllowFindAndReplace = (int) !empty($aCol['allow_find_replace']); // Later allow other columns as well, such as owned_by or statusid or so.
                    $sImg = '';
                    $sAlt = '';

                    // Determine column-specific CSS style.
                    $aCSSClasses = array();
                    if ($aOrder[0] == $sField) {
                        $aCSSClasses[] = 'ordered';
                    } elseif ($bSortable) {
                        $aCSSClasses[] = 'order';
                    }
                    if (in_array($sField, $aRequestMVSCols)) {
                        $aCSSClasses[] = 'mvs';
                        // Amend column legend specifying it's being used in multivalued search.
                        if (empty($aCol['legend'][0])) {
                            // Make sure this column has a legend.
                            $aCol['legend'][0] = '';
                        }
                        $aCol['legend'][0] .= (!$aCol['legend'][0]? '' : "\n") . 'Only showing rows with multiple values for this column.';
                    }

                    if ($bSortable && $aOrder[0] == $sField) {
                        $sImg = ($aOrder[1] == 'DESC'? '_desc' : '_asc');
                        $sAlt = ($aOrder[1] == 'DESC'? 'Descending' : 'Ascending');
                    }
                    print("\n" . '          <TH valign="top"' . (!empty($aCSSClasses)? ' class="' . implode(' ', $aCSSClasses) . '"' : '') . (empty($aCol['legend'][0])? '' : ' title="' . htmlspecialchars($aCol['legend'][0]) . '"') .
                                 ' data-allow_find_replace="' . $nAllowFindAndReplace . '" data-fieldname="' . $sField . '">' . "\n" .
                                 '            <IMG src="gfx/trans.png" alt="" width="' . $aCol['view'][1] . '" height="1" id="viewlistTable_' . $sViewListID . '_colwidth_' . $sField . '"><BR>' .
                            (!$bSortable? str_replace(' ', '&nbsp;', $aCol['view'][0]) . '<BR>' :
                                 "\n" .
                    // 2012-02-01; 3.0-beta-02; When resorting the ViewList, reset page to 1.
                                 '            <DIV onclick="document.forms[\'viewlistForm_' . $sViewListID . '\'].order.value=\'' . $sField . ',' . ($aOrder[0] == $sField? ($aOrder[1] == 'ASC'? 'DESC' : 'ASC') : $aCol['db'][1]) . '\'; if (document.forms[\'viewlistForm_' . $sViewListID . '\'].page) { document.forms[\'viewlistForm_' . $sViewListID . '\'].page.value=1; } lovd_AJAX_viewListSubmit(\'' . $sViewListID . '\');" style="position : relative;">' . "\n" .
                                 '              <IMG src="gfx/order_arrow' . $sImg . '.png" alt="' . $sAlt . '" title="' . $sAlt . '" width="13" height="12" style="position : absolute; top : 2px; right : 0px;">' . str_replace(' ', '&nbsp;', $aCol['view'][0]) . '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</DIV>') .
                            (!$bSearchable? '' :
                                 "\n" .
                                 // SetTimeOut() is necessary because if the function gets executed right away, selecting a previously used value from a *browser-generated* list in one of the fields, gets aborted and it just sends whatever is typed in at that moment.
                                 '            <INPUT type="text" name="search_' . $sField . '" value="' . (!isset($_GET['search_' . $sField])? '' : htmlspecialchars($_GET['search_' . $sField])) . '" title="' . strip_tags($aCol['view'][0]) . ' field should contain...' . (!empty($_GET['search_' . $sField])? "\nCurrent search:\n\n" . htmlspecialchars(lovd_formatSearchExpression($_GET['search_' . $sField], $aColTypes[$sField])) : '') .'" style="width : ' . ($aCol['view'][1] - 6) . 'px; font-weight : normal;" onkeydown="if (event.keyCode == 13) { if (document.forms[\'viewlistForm_' . $sViewListID . '\'].page) { document.forms[\'viewlistForm_' . $sViewListID . '\'].page.value=1; } setTimeout(\'lovd_AJAX_viewListSubmit(\\\'' . $sViewListID . '\\\')\', 0); return false;}">') .
                          '</TH>');
                }
                print('</TR></THEAD>');
            }

        } elseif (FORMAT == 'text/plain') {
            set_time_limit(60*5); // Very large, but not infinite.
            // Download format: show headers.
            $sObject = ($this->sObject == 'Custom_ViewList'? $this->sObjectID : $this->sObject . 's');
            header('Content-type: text/plain; charset=UTF-8');
            header('Content-Disposition: attachment; filename="LOVD_' . $sObject . '_' . date('Y-m-d_H.i.s') . '.txt"');
            header('Pragma: public');
            print('### LOVD-version ' . lovd_calculateVersion($_SETT['system']['version']) . ' ### ' . $sObject . ' Quick Download format ### This file can not be imported ###' . "\r\n");
            // FIXME: this has to be done better, we can't see what we're filtering for, because it's in the arguments!
            $sFilter = $this->aSQLViewList['WHERE'] . ($this->aSQLViewList['WHERE'] && $this->aSQLViewList['HAVING']? ' AND ' : '') . $this->aSQLViewList['HAVING'];
            if ($sFilter) {
                if (count($aArgs) == substr_count($sFilter, '?')) {
                    foreach ($aArgs as $sArg) {
                        $sFilter = preg_replace('/\?/', (ctype_digit($sArg)? $sArg : '"' . $sArg . '"'), $sFilter, 1);
                    }
                }
                print('## Filter: ' . $sFilter . "\r\n");
            }
            if (ACTION == 'downloadSelected') {
                print('## Filter: selected = ' . implode(',', $aSessionViewList['checked']) . "\r\n");
            }
            print('# charset = UTF-8' . "\r\n");
            $i = 0;
            foreach ($this->aColumnsViewList as $sField => $aCol) {
                if (in_array($sField, $aOptions['cols_to_skip'])) {
                    continue;
                }

                print(($i ++? "\t" : '') . '"{{' . $sField . '}}"');
            }
            print("\r\n");
        }

        if (!$nTotal && FORMAT == 'text/html') {
            if ($bSearched) {
                // Searched, but no results. FIXME: link to the proper documentation entry about search expressions
                $sBadSyntaxColumns = implode(', ', array_unique($aBadSyntaxColumns));
                // FIXME; use an IF here.
                $sMessageNormal = 'No results have been found that match your criteria.<BR>Please redefine your search criteria.';
                $sMessageBadSyntax = 'Your search column' . (count($aBadSyntaxColumns) > 1? 's contain' : ' contains') . ' incorrect search expression syntax at: ' . $sBadSyntaxColumns . '.';
                $sMessage = (empty($aBadSyntaxColumns)? $sMessageNormal : $sMessageBadSyntax);
                if ($aOptions['only_rows']) {
                    die('0'); // Silent error.
                }
                // FIXME; This code is sort of duplicated, some 100 lines below we also print this, *if* results are found.
                print('</TABLE><BR>' . "\n"); // <BR> is necessary to keep the InfoTable apart from the data headers.
                if ($aOptions['show_navigation']) {
                    print('        <INPUT type="hidden" name="total" value="' . (int) $nTotal . '" disabled>' . "\n" .
                          '        <INPUT type="hidden" name="page_size" value="' . (int) $_GET['page_size'] . '">' . "\n" .
                          '        <INPUT type="hidden" name="page" value="' . (int) $_GET['page'] . '">' . "\n");
                }
                lovd_showInfoTable($sMessage, 'stop');
                print('      </DIV></FORM>' . "\n\n");

            } else {
                if ($aOptions['only_rows']) {
                    die('0'); // Silent error.
                }

                print('      <DIV id="viewlistDiv_' . $sViewListID . '">' . "\n"); // These contents will be replaced by Ajax.

                if (substr($this->sObject, -7) == 'Variant') {
                    $sUnit = 'variants' . (substr($this->sObject, 0, 10) == 'Transcript'? ' on transcripts' : '');
                } elseif ($this->sObject == 'Custom_ViewList' || $this->sObject == 'Custom_ViewListPLUS') {
                    $sUnit = 'entries';
                } elseif ($this->sObject == 'Shared_Column') {
                    $sUnit = 'active columns';
                } else {
                    $sUnit = strtolower($this->sObject) . 's';
                }
                $sMessage = 'No ' . $sUnit . ' found';
                if (!empty($aSearchOnSkippedCols) && !$bSearchNonViewableCol) {
                    // When searched on columns that have been requested to be hidden for this VL,
                    //  then display the search items given to provide more information to the user.
                    // However, do not do this when we also searched on columns where no
                    //  display information is ever available ('view' set to false in aColumnsViewList).
                    $sWhere = '';
                    foreach ($aSearchOnSkippedCols as $sCol => $sValue) {
                        // If the hidden column has "ID" in its name, it is the primary filter column.
                        if (substr($sCol, -3) == ' ID') {
                            $sWhere .= ($sWhere? ' and ' : ' ') . 'for this ' . strtolower(substr($sCol, 0, -3));
                        } else {
                            $sWhere .= ($sWhere? ' and ' : ' where ') . strtolower($sCol) . ' is "' . str_replace('|', '" or "', trim($sValue, '="') . '"');
                        }
                    }
                    $sMessage .= $sWhere;
                }
                lovd_showInfoTable($sMessage . '!', 'stop');

                print('      </DIV></FORM>' . "\n\n");

                return 0;
            }
        }

        // Now loop through the data and print. But check for $q to be set; if we had a bad search syntax, we end up here as well, but without an $q.
        while (isset($q) && $nTotal && $zData = $q->fetchAssoc()) {
            // If row_id is not given by the database, but it should be created according to some format ($this->sRowID), put the data's ID in this format.
            $zData = $this->generateRowID($zData);
            // If row_link is not given by the database, but it should be created according to some format ($this->sRowLink), put the data's ID and the viewList's ID in this format.
            if (!isset($zData['row_link'])) {
                if ($this->sRowLink !== '' && $zData['row_id']) {
                    $zData['row_link'] = str_replace(array('{{ID}}', '{{ViewListID}}'), array(rawurlencode($zData['row_id']), $sViewListID), $this->sRowLink);
                    //$zData['row_link'] = preg_replace('/\{\{zData_(\w)+\}\}/', rawurlencode("$1"), $zData['row_link']);
                    //$zData['row_link'] = preg_replace_callback('/\{\{zData_(\w+)\}\}/', create_function('$aRegs', 'global $zData; return rawurlencode($zData[$aRegs[1]]);'), $zData['row_link']);
                    // FIXME; sorry, couldn't figure out how to do this in one line. Suggestions are welcome.
                    foreach ($zData as $key => $val) {
                        // Also allow data from $zData to be put into the row link & row id.
                        // FIXME; This is a temporary ugly solution, so we need to fix this later!!!!
                        $zData['row_link'] = preg_replace('/\{\{' . preg_quote($key, '/') . '\}\}/', rawurlencode(htmlspecialchars(addslashes($val ?: ''))), $zData['row_link']);
                        $zData['row_link'] = preg_replace('/\{\{zData_' . preg_quote($key, '/') . '\}\}/', rawurlencode(htmlspecialchars(addslashes($val ?: ''))), $zData['row_link']);
                        // But don't break C>G notation, variants can't be searched using row links otherwise.
                        $zData['row_link'] = str_replace('%26gt%3B', '%3E', $zData['row_link']);
                    }
                } else {
                    $zData['row_link'] = '';
                }
            }

            $zData = $this->autoExplode($zData);

            // Only the CustomViewList object has this 3rd argument, but other objects' prepareData()
            //  don't complain when called with this 3rd argument they didn't define.
            $zData = $this->prepareData($zData, 'list', $sViewListID);

            if (FORMAT == 'text/html') {
                // If defined, run the functions that define a row's class name.
                if (empty($zData['class_name'])) {
                    $zData['class_name'] = 'data';
                }

                // FIXME; rawurldecode() in the line below should have a better solution.
                // IE (who else) refuses to respect the BASE href tag when using JS. So we have no other option than to include the full path here.
                print("\n" .
                      '        <TR class="' . $zData['class_name'] . '"' . (!$zData['row_id']? '' : ' id="' . $zData['row_id'] . '"') . ' valign="top"' . (!$zData['row_link']? '' : ' style="cursor : pointer;"') .
                        (!$zData['row_link']? '' :
                            (substr($zData['row_link'], 0, 11) == 'javascript:'?
                                // Rowlink is javascript code, define it with an onClick attribute.
                                ' onclick="' . rawurldecode(substr($zData['row_link'], 11)) . '"' :
                                // Rowlink is a URL, define a data-href attribute which is used to
                                // by javascript open the page in the current window (onclick) or
                                // in a new window (middle-click event caught with jquery in
                                // inc-js-viewlist.php).
                                ' data-href="' . lovd_getInstallURL(false) . $zData['row_link'] . '"' .
                                // Note: older browsers will also trigger `onClick` events when the middle
                                // mouse button is clicked, this will interfere with functionality to open
                                // links in new tabs, provided in inc-js-viewlist.php.
                                ' onclick="javascript:window.location.href=this.getAttribute(\'data-href\');"')
                        ) . '>');
                if ($aOptions['show_options']) {
                    print("\n" . '          <TD align="center" class="checkbox" onclick="cancelParentEvent(event);"><INPUT id="check_' . $zData['row_id'] . '" class="checkbox" type="checkbox" name="check_' . $zData['row_id'] . '" onclick="lovd_recordCheckChanges(this, \'' . $sViewListID . '\'); event.stopPropagation();"' . (in_array($zData['row_id'], $aSessionViewList['checked'])? ' checked' : '') . '></TD>');
                }
                foreach ($this->aColumnsViewList as $sField => $aCol) {
                    if (in_array($sField, $aOptions['cols_to_skip'])) {
                        continue;
                    }

                    // Determine column-specific CSS classes.
                    $aCSSClasses = array();
                    if ($aOrder[0] == $sField) {
                        $aCSSClasses[] = 'ordered';
                    }
                    if (in_array($sField, $aRequestMVSCols)) {
                        $aCSSClasses[] = 'mvs';
                    }

                    print("\n" . '          <TD' . (!empty($aCol['view'][2])? ' ' . $aCol['view'][2] : '') . (!empty($aCSSClasses)? ' class="' . implode(' ', $aCSSClasses) . '"' : '') . '>' . (!isset($zData[$sField]) || $zData[$sField] === ''? '-' : $zData[$sField]) . '</TD>');
                }
                print('</TR>');

            } elseif (FORMAT == 'text/plain') {
                // Download format: print contents.
                if (ACTION == 'downloadSelected' && !in_array($zData['row_id'], $aSessionViewList['checked'])) {
                    // Only selected entries should be downloaded. And this one is not selected.
                    continue;
                }

                $i = 0;
                foreach ($this->aColumnsViewList as $sField => $aCol) {
                    if (in_array($sField, $aOptions['cols_to_skip'])) {
                        continue;
                    }
                    print(($i ++? "\t" : '') . '"' . str_replace(array("\r\n", "\r", "\n"), array('\r\n', '\r', '\n'), addslashes(html_entity_decode(strip_tags($zData[$sField])))) . '"');
                }
                print("\r\n");
            }
        }

        // Only print stuff if we're not just loading one entry right now.
        if ($nTotal && !$aOptions['only_rows'] && FORMAT == 'text/html') {
            print('</TABLE>' . "\n");
            if ($aOptions['show_navigation']) {
                print('        <INPUT type="hidden" name="total" value="' . $nTotal . '" disabled>' . "\n" .
                      '        <INPUT type="hidden" name="page_size" value="' . htmlspecialchars($_GET['page_size']) . '">' . "\n" .
                      '        <INPUT type="hidden" name="page" value="' . htmlspecialchars($_GET['page']) . '">' . "\n\n");

                lovd_pagesplitShowNav($sViewListID, $nTotal, $bTrueCount, $bSortableVL, $bLegend);
            }
            if (!$bAjax) {
                print('      </DIV></FORM><BR>' . "\n"); // These contents will be replaced by Ajax.
            }
        }

        if (!$bAjax && FORMAT == 'text/html') {
            // If sent using Ajax, the browser is not going to evaluate this code, anyways.
            print('      <SCRIPT type="text/javascript">' . "\n" .
                  '        // This has to be run when the document has finished loading everything, because only then can it get the proper width from IE7 and lower!' . "\n" .
                  '        $( function () {lovd_stretchInputs(\'' . $sViewListID . '\');});' . "\n");
            if ($aOptions['show_options']) {
                $sFRMenuOption = '';
                if ($aOptions['find_and_replace']) {
                    // Add find & replace menu item to viewlist options menu.
                    $sFRMenuOption = <<<FRITEM
'            <LI class="icon">' +
'                <A click="lovd_columnSelector(\'$sViewListID\', lovd_FRShowOptionsMenu, ' +
'                    \'Select column for find & replace\', \'allow_find_replace\');">' +
'                    <SPAN class="icon" style=""></SPAN>' +
'                    Find and replace text in column' +
'                </A>' +
'            </LI>' +
FRITEM;
                }

                $sMVSOption = '';
                if ($aOptions['multi_value_filter']) {
                    // Add menu option for selecting column to filter based on number of values.
                    $sMVSOption = <<<MVSItem
'            <LI class="icon">' +
'                <A click="lovd_columnSelector(\'$sViewListID\', lovd_toggleMVSCol, ' +
'                    \'Click column to select or deselect it for filtering on rows having multiple values\');">' +
'                    <SPAN class="icon" style=""></SPAN>' +
'                    Enable or disable filtering on multivalued rows' +
'                </A>' +
'            </LI>' +
MVSItem;
                }

                print(<<<OPMENU
        // If menu's UL doesn't exist yet, create it.
        if ($('#viewlistMenu_$sViewListID').attr('id') == undefined) {
          var oUL = window.document.createElement('ul');
          oUL.setAttribute('id', 'viewlistMenu_$sViewListID');
          oUL.className = 'jeegoocontext jeegooviewlist';
          window.document.body.appendChild(oUL);
        }
        // Fix the top border that could not be set through jeegoo's style.css.
        $('#viewlistMenu_$sViewListID').attr('style', 'border-top : 1px solid #000;');
        $('#viewlistMenu_$sViewListID').prepend(
'            <LI class="icon">' +
'                <A click="check_list[\'$sViewListID\'] = \'all\'; lovd_AJAX_viewListSubmit(\'$sViewListID\');">' +
'                    <SPAN class="icon" style="background-image: url(gfx/check.png);"></SPAN>' +
'                    Select all <SPAN>entries</SPAN>' +
'                </A>' +
'            </LI>' +
'            <LI class="icon">' +
'                <A click="check_list[\'$sViewListID\'] = \'none\'; lovd_AJAX_viewListSubmit(\'$sViewListID\');">' +
'                    <SPAN class="icon" style="background-image: url(gfx/cross.png);"></SPAN>' +
'                    Unselect all' +
'                </A>' +
'            </LI>' +
$sFRMenuOption
$sMVSOption
'            ');

OPMENU
);

                if ($aOptions['curate_set']) {
                    print('        // Add menu option for curating a selected set.' . "\n" .
                          '        $("#viewlistMenu_' . $sViewListID . '").append(\'<LI class="icon"><A click="lovd_AJAX_viewListSubmit(\\\'' . $sViewListID . '\\\', function(){$.get(\\\'ajax/curate_set.php?fromVL&vlid=' . $sViewListID . '\\\').fail(function(){alert(\\\'Request failed. Please try again.\\\');});});"><SPAN class="icon"></SPAN>Curate (publish) selected entries</A></LI>\');' . "\n\n");
                }
                if ($aOptions['merge_set']) {
                    print('        // Add menu option for merging a selected set of entries.' . "\n" .
                          '        $("#viewlistMenu_' . $sViewListID . '").append(\'<LI class="icon"><A click="lovd_AJAX_viewListSubmit(\\\'' . $sViewListID . '\\\', function(){$.get(\\\'ajax/merge_entries.php?fromVL&vlid=' . $sViewListID . '\\\').fail(function(){alert(\\\'Request failed. Please try again.\\\');});});"><SPAN class="icon"></SPAN>Merge selected entries</A></LI>\');' . "\n\n");
                }

                if (!LOVD_plus
                    || empty($_INSTANCE_CONFIG['viewlists']['restrict_downloads'])
                    || (!empty($_INSTANCE_CONFIG['viewlists'][$sViewListID]['allow_download_from_level'])
                        && $_INSTANCE_CONFIG['viewlists'][$sViewListID]['allow_download_from_level'] <= $_AUTH['level'])) {
                    print(<<<OPMENU
        $('#viewlistMenu_$sViewListID').append(
'            <LI class="icon">' +
'                <A click="lovd_AJAX_viewListSubmit(\'$sViewListID\', function(){lovd_AJAX_viewListDownload(\'$sViewListID\', true);});">' +
'                    <SPAN class="icon" style="background-image: url(gfx/menu_save.png);"></SPAN>' +
'                    Download all entries (summary data)' +
'                </A>' +
'            </LI>' +
'            <LI class="icon">' +
'                <A click="lovd_AJAX_viewListSubmit(\'$sViewListID\', function(){lovd_AJAX_viewListDownload(\'$sViewListID\', false);});">' +
'                    <SPAN class="icon" style="background-image: url(gfx/menu_save.png);"></SPAN>' +
'                    Download selected entries (summary data)' +
'                </A>' +
'            </LI>');

OPMENU
);
                }
                print("\n" .
                      '        lovd_activateMenu(\'' . $sViewListID . '\');' . "\n\n");
            }
            print('        check_list[\'' . $sViewListID . '\'] = [];' . "\n" .
                  '      </SCRIPT>' . "\n\n");
        }

        return $nTotal;
    }
}
?>
