<?php
/*******************************************************************************
 *
 * LEIDEN OPEN VARIATION DATABASE (LOVD)
 *
 * Created     : 2009-10-21
 * Modified    : 2020-02-10
 * For LOVD    : 3.0-23
 *
 * Copyright   : 2004-2020 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?
    var $nCount = '';





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





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





    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['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->query($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 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->query($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.
            if (in_array($sName, $this->aCheckMandatory) && (!isset($aData[$sName]) || $aData[$sName] === '')) {
                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();
                    }
                }
                // Simple check on non-custom columns (custom columns have their own function 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 (!is_string($sFieldvalue) || isset($aErroredFields[$sFieldname])) {
                // Do not process non-string values at the moment (currently there are no checks for them),
                //  and 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)) {
                // FIXME; we're assuming here, that $sName equals the database name. Which is true in probably most/every case, but even so...
                // FIXME; select fields might also benefit from having this check (especially for import).

                // Check max length.
                $nMaxLength = lovd_getColumnLength(constant($this->sTable), $sNameClean);
                if (!empty($sFieldvalue)) {
                    // For numerical columns, maxlength works differently!
                    if (in_array($sMySQLType, array('DECIMAL', 'DECIMAL_UNSIGNED', 'FLOAT', 'FLOAT_UNSIGNED', 'INT', 'INT_UNSIGNED'))) {
                        // SIGNED cols: negative values.
                        if (in_array($sMySQLType, array('DECIMAL', 'INT')) && (int) $sFieldvalue < (int) ('-' . str_repeat('9', $nMaxLength))) {
                            lovd_errorAdd($sFieldname, 'The \'' . $sHeader . '\' field is limited to numbers no lower than -' . str_repeat('9', $nMaxLength) . '.');
                        }
                        // ALL numerical cols (except floats): positive values.
                        if (substr($sMySQLType, 0, 5) != 'FLOAT' && (int) $sFieldvalue > (int) str_repeat('9', $nMaxLength)) {
                            lovd_errorAdd($sFieldname, 'The \'' . $sHeader . '\' field is limited to numbers no higher than ' . str_repeat('9', $nMaxLength) . '.');
                        }
                    } 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 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->query($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->query(
                '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 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)';
        }

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





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





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





    function getCount ($ID = false)
    {
        // Returns the number of entries in the database table.
        // ViewEntry() and ViewList() call this function to see if data exists at all, and actually don't require a precise number.
        // $ID = Can be an integer/numeric string, or an array. If an integer/numeric string: ID to check for existance.
        //   If an associative array (for linking tables), use array('geneid' => 'IVD', 'userid' => 1).
        // FIXME: This function's name is wrong, and it should be renamed to isNotEmpty() or entryExist() or so.
        // FIXME: Also the $this->nCount should be renamed, and the custom VL's functions for filling nCount can be simplified.
        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);
            }

            $nCount = (int) $_DB->query('SELECT 1 FROM ' . constant($this->sTable) . '
                                         WHERE ' . implode(' = ? AND ', array_keys($aIDs)) . ' = ? LIMIT 1',
                                array_values($aIDs))->fetchColumn();
        } else {
            if ($this->nCount !== '') {
                return $this->nCount;
            }
            $nCount = (int) $_DB->query('SELECT 1 FROM ' . constant($this->sTable) . '
                                         LIMIT 1')->fetchColumn();
            $this->nCount = $nCount;
        }
        return $nCount;
    }





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





    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[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[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->query($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->query($this->buildSQL($this->aSQLViewList), $aArgs);
                $nCount = $_DB->query('SELECT FOUND_ROWS()')->fetchColumn();
            } else {
                // Super inefficient, only for low-volume (sqlite) databases!
                $nCount = count($_DB->query($this->buildSQL($this->aSQLViewList), $aArgs)->fetchAllColumn());
            }
        }

        return $nCount;
    }





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





    function insertEntry ($aData, $aFields = array())
    {
        // 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->query($sSQL, $aSQL, true, true);

        $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->query($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? $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->query($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->query($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;
    }





    public static function lovd_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

        $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 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']) {
                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['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.
            $zData['owned_by_'] = '';
            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.
                    $zData['owned_by_'] .= (!$zData['owned_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['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, [' . ($sView == 'list'? '-200' : '0') . ', 0]);">' . $sName . '</SPAN>';
                }
            }
        }
        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['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.
        $this->aColumnsViewList = 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)) {
                                        $sOperator = (substr($sTerm, 0, 1) == '!'? 'NOT ' : '') . 'LIKE';
                                        $$CLAUSE .= '(' . $aCol['db'][0] . ' ' . $sOperator . ' ?' . ($sOperator == 'NOT LIKE'? ' OR ' . $aCol['db'][0] . ' IS NULL)' : ')');
                                        $aArguments[$CLAUSE][] = '%' . $aMatches[1] . '%';
                                    } 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)) {
                                        $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']) && $aRequestMVSCols = explode(';', $aRequest['MVSCols'])) {
            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 inserted.
        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->query('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->query($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->query('
                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->query($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.
        $n = $this->getCount($ID);
        if (!$n) {
            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->query($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->query($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>' . ($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.
            ),
            $aOptions);

        // Disallow F&R and multivalue search when options menu is hidden.
        $aOptions['find_and_replace'] &= $aOptions['show_options'];
        $aOptions['multi_value_filter'] &= $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.
        // FIXME: This check, meant for optimization, proves to be slowing down the entire VL on larger DBs.
        // FIXME: It would be better to remove this and remove the nCount calculators in the custom VL object completely.
        // FIXME: Currently anyway we don't provide correct counts there for performance reasons, so the variable name isn't correct.
        $nTotal = $this->getCount();
        if (!$nTotal && 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 = explode(',', $_GET['order']);
        } 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('/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)
            $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];
            }
            $this->aSQLViewList['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_CHROMOSOMES . ' AS chr ON (' . (!$sAlias? '' : $sAlias . '.') . 'chromosome = chr.name)';
            $sSQLOrderBy = 'chr.sort_id ' . $aOrder[1];
            if ($aOrder[0] == 'VariantOnGenome/DNA') {
                $sSQLOrderBy .= ', position_g_start ' . $aOrder[1] . ', position_g_end ' . $aOrder[1] . ', `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] . ', `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] : null);
        // Display name of selected field.
        $sFRFieldDisplayname = (isset($_GET['FRFieldDisplayname_' . $sViewListID])?
                                $_GET['FRFieldDisplayname_' . $sViewListID] : null);
        // Search query for find & replace.
        $sFRSearchValue =      (isset($_GET['FRSearch_' . $sViewListID])?
                                $_GET['FRSearch_' . $sViewListID] : null);
        // Replace value for find & replace.
        $sFRReplaceValue =     (isset($_GET['FRReplace_' . $sViewListID])?
                                $_GET['FRReplace_' . $sViewListID] : null);
        // 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; // Overwrites the previous $nTotal.
        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->query($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']);
                        }
                    } 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->query($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->query('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");

                if ($aOptions['show_navigation']) {
                    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="' . join(' ', $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="' . $nTotal . '" disabled>' . "\n" .
                          '        <INPUT type="hidden" name="page_size" value="' . $_GET['page_size'] . '">' . "\n" .
                          '        <INPUT type="hidden" name="page" value="' . $_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_ViewListMOD') {
                    $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($val), $zData['row_link']);
                        $zData['row_link'] = preg_replace('/\{\{zData_' . preg_quote($key, '/') . '\}\}/', rawurlencode($val), $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="' . join(' ', $aCSSClasses) . '"' : '') . '>' . ($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="' . $_GET['page_size'] . '">' . "\n" .
                      '        <INPUT type="hidden" name="page" value="' . $_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 (!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;
    }
}
?>
