<?php
/*******************************************************************************
 *
 * LEIDEN OPEN VARIATION DATABASE (LOVD)
 *
 * Created     : 2008-03-17
 * Modified    : 2009-07-07
 * For LOVD    : 2.0-19
 *
 * Access      : Curators and up.
 * Purpose     : Provide additional data edit tools; "Find & Replace" and "Copy
 *               column".
 *
 * Copyright   : 2004-2009 Leiden University Medical Center; http://www.LUMC.nl/
 * Programmers : Ing. Ivo F.A.C. Fokkema <I.F.A.C.Fokkema@LUMC.nl>
 *               Ir. Gerard C.P. Schaafsma <G.C.P.Schaafsma@LUMC.nl>
 * Last edited : Ing. Ivo F.A.C. Fokkema <I.F.A.C.Fokkema@LUMC.nl>
 *
 *
 * 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 2 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, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 *
 *************/

define('ROOT_PATH', './');
require ROOT_PATH . 'inc-init.php';

if (HAS_AUTH) {
    // If authorized, check for updates.
    require ROOT_PATH . 'inc-upgrade.php';
}

// Require manager clearance.
lovd_requireAUTH(LEVEL_CURATOR);

// Need to have a gene.
if (!GENE_COUNT) {
    require ROOT_PATH . 'inc-top.php';
    lovd_showInfoTable('There are currently no databases installed.', 'stop');
    require ROOT_PATH . 'inc-bot.php';
    exit;
}





if ($_GET['action'] == 'fnr' || $_GET['action'] == 'copy') {
    // Find & Replace: Find certain values in a specific column and replace it with a different value.
    // Copy column: Copy certain values from a specific column to another column.

    // Require form functions.
    require ROOT_PATH . 'inc-lib-form.php';
    require ROOT_PATH . 'inc-lib-list.php'; // Only for lovd_escapeSearchTerm().
    require ROOT_PATH . 'inc-lib-columns.php'; // Only for lovd_getColumnLength().

    // Find genes we're applying this to.
    $aGenes = array();
    if ($_AUTH['level'] == LEVEL_CURATOR) {
        $sQ = 'SELECT c.symbol, CONCAT(c.symbol, " (", g.gene, ")") AS gene FROM ' . TABLE_CURATES . ' AS c LEFT JOIN ' . TABLE_DBS . ' AS g USING (symbol) WHERE userid = "' . $_AUTH['userid'] . '" ORDER BY c.symbol';
    } else {
        $sQ = 'SELECT symbol, CONCAT(symbol, " (", gene, ")") AS gene FROM ' . TABLE_DBS . ' ORDER BY symbol';
    }
    $qGenes = mysql_query($sQ);
    $nGenes = mysql_num_rows($qGenes);
    while ($r = mysql_fetch_row($qGenes)) {
        $aGenes[$r[0]] = $r[1];
    }

    // Match types.
    $aTypes =
             array(
                    'full' => 'Field value is...',
                    'part' => 'Field contains...',
                    'start' => 'Field starts with...',
                    'end' => 'Field ends with...',
                  );

    if ($_GET['action'] == 'copy') {
        // Extra match type.
        $aTypes['none'] = 'Field contains anything';

        // Append or overwrite.
        $aActions =
                 array(
                        'append' => 'Append to existing value',
                        'overwrite' => 'Overwrite existing value',
                      );
    }



    if (isset($_GET['sent'])) {
        lovd_errorClean();

        // Must have gene selected.
        if (empty($_POST['genes']) || !is_array($_POST['genes'])) {
            lovd_errorAdd('Please select at least one gene to perform ' . ($_GET['action'] == 'fnr'? 'Find &amp; Replace' : 'Copy columns') . ' on.');
        } else {
            // Gene(s) must exist!
            foreach ($_POST['genes'] as $sSymbol) {
                if (!array_key_exists($sSymbol, $aGenes)) {
                    lovd_errorAdd('Symbol not understood: ' . htmlspecialchars($sSymbol));
                }
            }
        }

        // Mandatory fields.
        if ($_GET['action'] == 'fnr') {
            $aCheck = array('col' => 'Apply find &amp; replace to');
        } else {
            $aCheck =
                     array(
                            'col' => 'Copy values from',
                            'coltocopyto' => 'Copy values to',
                          );
        }

        foreach ($aCheck as $key => $val) {
            if (empty($_POST[$key])) {
                lovd_errorAdd('Please fill in the \'' . $val . '\' field.');
            }
        }

        if (empty($_POST['type']) || !array_key_exists($_POST['type'], $aTypes)) {
            lovd_errorAdd('Please fill in the \'Match type\' field.');
        }

        // If find text is empty, and type is "field value contains..." you get fucked up results.
        if (empty($_POST['find']) && $_POST['type'] == 'part') {
            lovd_errorAdd('You did not fill in a search text, but you are looking for a partial match. This will create garbage in non empty fields. Type in a text to search for or select a different match type.');
        }

        // Copying to the same column not allowed.
        if ($_GET['action'] == 'copy' && $_POST['col'] == $_POST['coltocopyto']) {
            lovd_errorAdd('The \'Copy values from\' column and the \'Copy values to\' column can not be the same.');
        }

        // 2009-07-02; 2.0-19; Check if column to copy to is of type INT; then you can not append.
        if ($_GET['action'] == 'copy' && $_POST['action'] == 'append' && @lovd_getColumnType((substr($_POST['coltocopyto'], 0, 7) == 'Variant'? TABLE_CURRDB_VARS : TABLE_PATIENTS), $_POST['coltocopyto']) == 'INT') {
            lovd_errorAdd('Appending values to the \'' . htmlspecialchars($_POST['coltocopyto']) . '\' column (INT) could result in a loss of data. This copy command is not supported.');
        }

        // Update cancelled.
        if (isset($_GET['confirm']) && isset($_POST['cancel'])) {
            lovd_errorAdd('Please adjust your find criteria and submit again.');
        }

        // XSS attack prevention. Simply deny input of HTML, PHP other stuff blocked by strip_tags().
        lovd_checkXSS();



        if (!lovd_error()) {
            // First find, then replace...
            require ROOT_PATH . 'class/currdb.php';
            $aCURRDBs = array();
            $nMatched = 0;
            $nUpdated = 0;
            $nFieldTooShort = 0;
            $sTable = substr($_POST['col'], 0, strpos($_POST['col'] . '/', '/')); // Variant, Patient or submitter.
            if ($_GET['action'] == 'copy') {
                // Table to copy to: Variant, Patient or submitter.
                $sTableToCopyTo = substr($_POST['coltocopyto'], 0, strpos($_POST['coltocopyto'] . '/', '/'));
                $aForbiddenColCombinations =
                         array(
                                array('CHAR', 'INT'),
                                array('CHAR', 'DEC'),
                                array('CHAR', 'DATETIME'),
                                array('CHAR', 'DATE'),
                                array('INT', 'DATETIME'),
                                array('INT', 'DATE'),
                                array('DEC', 'INT'),
                                array('DEC', 'DATETIME'),
                                array('DEC', 'DATE'),
                                array('DATETIME', 'INT'),
                                array('DATETIME', 'DEC'),
                                array('DATETIME', 'DATE'),
                                array('DATE', 'INT'),
                                array('DATE', 'DEC'),
                              );
            }

            // If Variant/ or Patient/ column, we check availability (Patient/ column actually requires just one loop).
            // Other columns will simply return no match, if not mapped in the in_array() check below.
            foreach ($_POST['genes'] as $sSymbol) {
                $aCURRDBs[$sSymbol] = new CurrDB(true, $sSymbol);
                $sType = $aCURRDBs[$sSymbol]->getFieldType($_POST['col']);
                if ($_GET['action'] == 'copy') {
                    // 2009-06-30; 2.0-19; getFieldType does not work in case of submitterid
                    $sTypeToCopyTo = ($_POST['coltocopyto'] != 'submitterid' ? $aCURRDBs[$sSymbol]->getFieldType($_POST['coltocopyto']) : lovd_getColumnType(TABLE_PATIENTS, $_POST['coltocopyto']));
                }
                // We only get here, if the gene exists in the database.
                if (in_array($sTable, array('Variant', 'Patient'))) {
                    // Do we even have this column enabled?
                    if (!$aCURRDBs[$sSymbol]->colExists($_POST['col'])) {
                        // Nope, report that the column does not exist for this gene.
                        lovd_errorAdd('The column \'' . htmlspecialchars($_POST['col']) . '\' does not exist in the ' . $sSymbol . ' gene database.');
                        unset($aCURRDBs[$sSymbol]);
                        continue; // Continue; see if other genes have this error, too.
                    } elseif ($_GET['action'] == 'fnr' && $_POST['replace']) {
                        // 2009-02-26; 2.0-16; Check for text input in a integer column.
                        switch ($sType) {
                            case 'INT':
                                // FIXME; This allows negative values in possibly UNSIGNED integer fields.
                                if (!preg_match('/^\-?[0-9]+$/', $_POST['replace'])) {
                                    lovd_errorAdd('This field has to contain a valid numeric value.');
                                    break 2; // Break; fatal error, having this repeated for all genes is useless.
                                }
                                break;
                            case 'DEC':
                                // FIXME; This allows negative values in possibly UNSIGNED decimal fields.
                                if (!preg_match('/^\-?[0-9]+([.,][0-9]+)?$/', $_POST['replace'])) {
                                    lovd_errorAdd('This field has to contain a valid decimal value.');
                                    break 2; // Break; fatal error, having this repeated for all genes is useless.
                                }
                                break;
                            case 'DATE':
                                if (!lovd_matchDate($_POST['replace'])) {
                                    lovd_errorAdd('This field has to contain a valid date value.');
                                    break 2; // Break; fatal error, having this repeated for all genes is useless.
                                }
                                break;
                            case 'DATETIME':
                                if (!lovd_matchDate($_POST['replace']) && !lovd_matchDate($_POST['replace'], true)) {
                                    lovd_errorAdd('This field has to contain a valid date value, optionally including a timestamp.');
                                    break 2; // Break; fatal error, having this repeated for all genes is useless.
                                }
                                break;
                        }
                    }

                } elseif (!in_array($sTable, array('submitterid'))) {
                    // This column does not exist / does not have the "Free edit" scripts enabled.
                    // True, this error will show up for every selected gene, but it's only shown to users trying to hack this script, anyway.
                    lovd_errorAdd('The column \'' . htmlspecialchars($_POST['col']) . '\' does not have "' . ($_GET['action'] == 'fnr'? 'Find &amp; Replace' : 'Copy column') . '" enabled.');
                    continue; // Continue; see if other genes have this error, too.
                }



                // Now, if "Copy column" is active, check coltocopyto.
                if ($_GET['action'] == 'copy') {
                    if (in_array($sTableToCopyTo, array('Variant', 'Patient'))) {
                        // Do we even have this column enabled?
                        if (!$aCURRDBs[$sSymbol]->colExists($_POST['coltocopyto'])) {
                            // Nope, report that the column does not exist for this gene.
                            lovd_errorAdd('The column \'' . htmlspecialchars($_POST['coltocopyto']) . '\' does not exist in the ' . $sSymbol . ' gene database.');
                            unset($aCURRDBs[$sSymbol]);
                            continue; // Continue; see if other genes have this error, too.
                        } else {
                            // We need to check if the two column types match. Some combinations are not allowed!
                            // Data loss is not allowed.
                            if (in_array(array($sType, $sTypeToCopyTo), $aForbiddenColCombinations)) {
                                lovd_errorAdd('Copying values from a \'' . $sType . '\' column to a \'' . $sTypeToCopyTo . '\' column could result in a loss of data. This copy command is not supported. If you\'re sure the data is formatted properly, first change the type of the \'' . htmlspecialchars($_POST['col']) . '\' column to \'' . $sTypeToCopyTo . '\'.');
                            }
                        }

                    } elseif (!in_array($sTableToCopyTo, array('submitterid'))) {
                        // This column does not exist / does not have the "Free edit" scripts enabled.
                        // True, this error will show up for every selected gene, but it's only shown to users trying to hack this script, anyway.
                        lovd_errorAdd('The column \'' . htmlspecialchars($_POST['coltocopyto']) . '\' does not have "Copy column" enabled.');
                        continue; // Continue; see if other genes have this error, too.
                    }
                }
            }

            // 2009-02-26; 2.0-16; SubmitterID should only allow for numeric values.
            if ($_GET['action'] == 'fnr' && $sTable == 'submitterid') {
                $_POST['find'] = str_pad($_POST['find'], 5, '0', STR_PAD_LEFT);
                if ($_POST['replace'] && (!preg_match('/^[0-9]{1,5}$/', $_POST['replace']) || $_POST['replace'] >= 65535)) {
                    lovd_errorAdd('This field has to contain a valid numeric value lower than 65535.');
                }
            } elseif ($_GET['action'] == 'copy' && $sTableToCopyTo == 'submitterid' && $sType != 'INT') {
                lovd_errorAdd('Copying values from the \'' . htmlspecialchars($_POST['col']) . '\' column (' . $sType . ') to the submitterid column (INT) could result in a loss of data. This copy command is not supported. If you\'re sure the data is formatted properly, first change the type of the \'' . htmlspecialchars($_POST['col']) . '\' column to \'INT\'.');
            }



            if (!count($aCURRDBs)) {
                lovd_errorAdd('None of the selected genes have ' . ($_GET['action'] == 'fnr'? 'this column' : 'both these two columns')  . ' enabled!');
            } elseif (lovd_error()) {
                // 2009-02-26; 2.0-16; This will stop processing, to throw an error.
                $aCURRDBs = array();
            }



            // The foreach will not work if we have no genes, so no check with lovd_error() necessary.
            // Check if we have matching entries.
            $sMatch = '';
            // 2008-08-01; 2.0-10; Added the slash as a delimiter in preg_quote(), to fix errors when the pattern contained a slash.
            switch ($_POST['type']) {
                case 'full':
                    // 2008-08-01; 2.0-10; Removed lovd_escapeSearchTerm() since this is not a LIKE statement.
                    $sMatch = '= "' . $_POST['find'] . '"';
                    $sPattern = '^' . preg_quote($_POST['find'], '/') . '$';
                    break;
                case 'start':
                    $sMatch = 'LIKE "' . lovd_escapeSearchTerm($_POST['find']) . '%"';
                    $sPattern = '^' . preg_quote($_POST['find'], '/');
                    break;
                case 'part':
                    $sMatch = 'LIKE "%' . lovd_escapeSearchTerm($_POST['find']) . '%"';
                    $sPattern = preg_quote($_POST['find'], '/');
                    break;
                case 'end':
                    $sMatch = 'LIKE "%' . lovd_escapeSearchTerm($_POST['find']) . '"';
                    $sPattern = preg_quote($_POST['find'], '/') . '$';
                    break;
                case 'none':
                    $sMatch = 'LIKE "%"'; // Not a very elegant solution
                    $sPattern = '';
                    break;
            }

            // 2009-03-10; 2.0-19; This whole foreach was edited by Gerard, removed the switch Variant vs. Patient/submitterid
            $aQueries = array();
            foreach ($aCURRDBs as $sSymbol => $CurrDB) {
                $bUpdated = false;
                $bSortUpdated = false; // 2009-06-30; 2.0-19; Flag for updated columns Variant/DNA or Variant/Exon.
                // Determine the column types to select (Variant or Patient/submitter column)
                $sPrefix = ($sTable == 'Variant' ? 'v.' : 'p.');
                // Build the query for the column you want to find (select)
                $sQ = 'SELECT ' . ($sPrefix == 'v.' ? 'v.variantid' : 'p.patientid') . ', ' . $sPrefix . '`'. $_POST['col'] . '` ' .
                      'FROM ' . TABLEPREFIX . '_' . $sSymbol . '_variants AS v ' .
                      'LEFT JOIN ' . TABLE_PAT2VAR . ' AS p2v ON (p2v.symbol = "' . $sSymbol . '" AND v.variantid = p2v.variantid) ' .
                      'LEFT JOIN ' . TABLE_PATIENTS . ' AS p USING (patientid) ' .
                      'LEFT OUTER JOIN ' . TABLE_VAR_STATUS . ' AS stat ON (p2v.status = stat.status) ' .
                      'LEFT OUTER JOIN ' . TABLE_PATHOGENIC . ' AS path ON (p2v.pathogenic = path.pathogenic) ' .
                      'WHERE ' . $sPrefix . '`' . $_POST['col'] . '` ' . $sMatch;

                // SEARCH: Advanced text search.
                $aSearchText = array_merge(
                         array('search_pathogenic_' => 'path.pathogenic_text'),
                         $CurrDB->buildSearchList(),
                         array(
                                 'search_submitterid' => 'p.submitterid',
                                 'search_status_' => 'stat.status_text',
                              ));

                // Add preselected columns (selected in variants.php) to your query
                foreach ($aSearchText as $key => $val) {
                    if (isset($_GET[$key]) && trim($_GET[$key])) {
                        $a = explode(' ', trim($_GET[$key]));
                        foreach ($a as $sTerm) {
                            if ($sTerm) {
                                if (substr_count($sTerm, '|') && preg_match('/^[^|]+(\|[^|]+)+$/', $sTerm)) {
                                    // OR.
                                    $aOR = explode('|', $sTerm);
                                    $sQ .= ' AND (';
                                    foreach ($aOR as $nTerm => $sTerm) {
                                        if (substr($sTerm, 0, 1) == '!') {
                                            // NOT.
                                            $sQ .= ($nTerm? ' OR ' : '') . $val . ' NOT LIKE "%' . lovd_escapeSearchTerm(substr($sTerm, 1)) . '%"';
                                        } else {
                                            // Common search term.
                                            $sQ .= ($nTerm? ' OR ' : '') . $val . ' LIKE "%' . lovd_escapeSearchTerm($sTerm) . '%"';
                                        }
                                    }
                                    $sQ .= ')';
                                } elseif (substr($sTerm, 0, 1) == '!') {
                                    // NOT.
                                    $sQ .= ' AND ' . $val . ' NOT LIKE "%' . lovd_escapeSearchTerm(substr($sTerm, 1)) . '%"';
                                } else {
                                    // Common search term.
                                    $sQ .= ' AND ' . $val . ' LIKE "%' . lovd_escapeSearchTerm($sTerm) . '%"';
                                }
                            }
                        }
                    }
                }
                $sQ .= ($sPrefix == 'v.' ? ' GROUP BY v.variantid' : ' GROUP BY p.patientid');

                // Put the queries in an array.
                $aQueries[$sSymbol] = $sQ;
            }

            // Now determine the number of entries that will be updated.
            foreach ($aQueries as $sSymbol => $sQ) {
                $qData[$sSymbol] = mysql_query($sQ);
                if ($qData[$sSymbol]) {
                    $nMatched += mysql_num_rows($qData[$sSymbol]);
                }
            }
            if (!$nMatched) {
                lovd_errorAdd('No matches found to your search!');
            }





            if (!lovd_error()) {
                if (isset($_GET['confirm'])) {
                    // Mandatory fields.
                    if (empty($_POST['password'])) {
                        lovd_errorAdd('Please fill in the \'Enter your password for authorization\' field.');
                    }

                    // User had to enter his/her password for authorization.
                    if ($_POST['password'] && md5($_POST['password']) != $_AUTH['password']) {
                        lovd_errorAdd('Please enter your correct password for authorization.');
                    }

                    if (!lovd_error()) {
                        // Need this to see if a value from a copied column was an empty string.
                        $nFieldEmpty = 0;

                        // Update
                        foreach ($aCURRDBs as $sSymbol => $CurrDB) {
                            while (list($nFindID, $sCopiedColumnValue) = mysql_fetch_row($qData[$sSymbol])) {
                                if ($_GET['action'] == 'fnr') {
                                    // Find, replace & update.
                                    $sNew = preg_replace('/' . $sPattern . '/', $_POST['replace'], $sCopiedColumnValue);
                                    // 2008-08-01; 2.0-10; An error in the preg_replace could empty a whole field...
                                    if ($sNew === NULL) {
                                        // Preg_replace failed! Don't execute anything, but throw an error...
                                        lovd_displayError('FreeEditFNR', 'The preg_replace() in the Find &amp; Replace returned NULL. This is a bug in LOVD or in one of it\'s modules. Please <A href="' . $_SETT['upstream_URL'] . 'bugs/" target="_blank">file a bug</A> and include the below messages to help us solve the problem.<BR><BR>Tried to replace: "' . $sPattern . '"<BR>By: "' . stripslashes($_POST['replace']) . '"<BR>In: "' . stripslashes($_POST['col']) . '"', true);
                                    } elseif (strlen($sNew) > ($sTable != 'submitterid'? $CurrDB->getFieldLength($_POST['col']) : lovd_getColumnLength(TABLE_PATIENTS, $_POST['col']))) {
                                        // 2009-04-14; 2.0-19; use lovd_getColumnLength in case of submitterid.
                                        // Do not update; result does not fit in.
                                        $nFieldTooShort ++;
                                    } elseif ($sNew != $sCopiedColumnValue) {
                                        $sQupdate = 'UPDATE ' . ($sPrefix == 'v.'? TABLEPREFIX . '_' . $sSymbol . '_variants' : TABLE_PATIENTS) .
                                                    ' SET `' . $_POST['col'] . '` = "' . mysql_real_escape_string($sNew) . '" ' .
                                                    'WHERE ' . ($sPrefix == 'v.'? 'variantid' : 'patientid') . ' = "' . $nFindID . '"';
                                        $q = mysql_query($sQupdate);
                                        if ($q) {
                                            $bUpdated = true;
                                            // 2009-06-30; 2.0-19; Added flag for updated columns Variant/DNA or Variant/Exon.
                                            if (in_array($_POST['col'], array('Variant/DNA', 'Variant/Exon'))) {
                                                $bSortUpdated = true;
                                            }
                                            $nUpdated += mysql_affected_rows();
                                            if ($sPrefix == 'v.') {
                                                // Variant column.
                                                @mysql_query('UPDATE ' . TABLE_PAT2VAR . ' SET edited_by = "' . $_AUTH['userid'] . '", edited_date = NOW() WHERE symbol = "' . $sSymbol . '" AND variantid = "' . $nFindID . '"');
                                            } elseif ($sPrefix == 'p.') {
                                                // Patient column.
                                                @mysql_query('UPDATE ' . TABLE_PATIENTS . ' SET edited_by = "' . $_AUTH['userid'] . '", edited_date = NOW() WHERE patientid = "' . $nFindID . '"');
                                            }
                                        } else {
                                            // Throw some error.
                                            require ROOT_PATH . 'inc-top.php';
                                            lovd_printHeader('config_free_edit_fnr', 'LOVD Configuration - Find & Replace');
                                            lovd_dbFout('FreeEditFNR', $sQupdate, mysql_error());
                                            require ROOT_PATH . 'inc-bot.php';
                                            exit;
                                        }
                                    }



                                } else {
                                    // Copy.
                                    if ($sTableToCopyTo == 'Variant') {
                                        // Copy the value of a selected column to a Variant column
                                        // Select the Variant column you want to copy to
                                        $sQToCopyTo = 'SELECT v.variantid, v.`' . $_POST['coltocopyto'] . '` FROM ' . TABLEPREFIX . '_' . $sSymbol . '_variants AS v ' .
                                                      'LEFT JOIN ' . TABLE_PAT2VAR . ' AS p2v USING (variantid) ' .
                                                      'LEFT JOIN ' . TABLE_PATIENTS . ' AS p USING (patientid) ' .
                                                      'WHERE p2v.symbol = "' . $sSymbol . '" AND ' . ($sPrefix == 'v.'? 'v.variantid' : 'p.patientid') . ' = "' . $nFindID . '"';
                                        // 2009-06-30; 2.0-19; removed the GROUP BY when copying from a Patient to a Variant column
                                        if ($sPrefix == 'v.') {
                                            $sQToCopyTo .= ' GROUP BY v.variantid';
                                        }
                                    } else {
                                        // Copy the value of a selected column to a Patient column.
                                        // Select the Patient column you want to copy to
                                        $sQToCopyTo = 'SELECT p.patientid, p.`' . $_POST['coltocopyto'] . '` FROM ' . TABLE_PATIENTS . ' AS p ' .
                                                      'LEFT JOIN ' . TABLE_PAT2VAR . ' AS p2v USING (patientid) ' .
                                                      'LEFT JOIN ' . TABLEPREFIX . '_' . $sSymbol . '_variants AS v USING (variantid) ' .
                                                      'WHERE p2v.symbol = "' . $sSymbol . '" AND ' . ($sPrefix == 'v.' ? 'v.variantid' : 'p.patientid') . ' = "' . $nFindID . '"';
                                        // 2009-06-30; 2.0-19; removed the GROUP BY when copying from a Variant to a Patient column
                                        if ($sPrefix == 'p.') {
                                            $sQToCopyTo .= ' GROUP BY p.patientid';
                                        }
                                    }

                                    $qCopyTo = mysql_query($sQToCopyTo);

                                    // You may have to update more than one variant
                                    while (list($nCopyToID, $sColumnToCopyToValue) = mysql_fetch_row($qCopyTo)) {
                                        // Append or overwrite?; && !empty($sCopiedColumnValue) in case $sCopiedColumnValue is empty
                                        if ($_POST['action'] == 'append' && !empty($sCopiedColumnValue)) {
                                            $sNew = ($sColumnToCopyToValue ? $sColumnToCopyToValue . '; ' : '') . $sCopiedColumnValue;
                                        } else {
                                            $sNew = $sCopiedColumnValue;
                                        }

                                        if (strlen($sNew) > ($sTableToCopyTo != 'submitterid' ? $CurrDB->getFieldLength($_POST['coltocopyto']) : lovd_getColumnLength(TABLE_PATIENTS, $_POST['coltocopyto']))) {
                                            // Do not update; result does not fit in.
                                            $nFieldTooShort ++;
                                        } elseif ($sCopiedColumnValue == '' && !$_POST['empty_copied_field']) {
                                            // Do not update; copied column was an empty string ; EXCEPT IN THE CASE OF MOVE
                                            $nFieldEmpty ++;
                                        } else {
                                            if ($sTableToCopyTo == 'Variant') {
                                                // Update a Variant column
                                                $sQupdate = 'UPDATE ' . TABLE_PAT2VAR . ' AS p2v ' .
                                                            'LEFT JOIN ' . TABLE_PATIENTS . ' AS p USING (patientid) ' .
                                                            'LEFT JOIN ' . TABLEPREFIX . '_' . $sSymbol . '_variants AS v USING (variantid) ' .
                                                            'SET v.`' . $_POST['coltocopyto'] . '` = "' . mysql_real_escape_string($sNew) . '" ' .
                                                            'WHERE p2v.symbol="' . $sSymbol . '" ' .
                                                            'AND v.variantid = "' . $nCopyToID . ($sPrefix == 'v.' ? '"' : '" AND patientid = "' . $nFindID . '"');
                                            } else {
                                                // Update a Patient column
                                                $sQupdate = 'UPDATE ' . TABLE_PAT2VAR . ' AS p2v ' .
                                                            'LEFT JOIN ' . TABLE_PATIENTS . ' AS p USING (patientid) ' .
                                                            'LEFT JOIN ' . TABLEPREFIX . '_' . $sSymbol . '_variants AS v USING (variantid) ' .
                                                            'SET p.`' . $_POST['coltocopyto'] . '` = "' . mysql_real_escape_string($sNew) . '" ' .
                                                            'WHERE p2v.symbol="' . $sSymbol . '" ' .
                                                            'AND p.patientid = "' . $nCopyToID . ($sPrefix == 'p.' ? '"' : '" AND variantid = "' . $nFindID . '"');
                                            }

                                            $q = mysql_query($sQupdate);
                                            if ($q) {
                                                $bUpdated = true;
                                                // 2009-06-30; 2.0-19; Added flag for updated columns Variant/DNA or Variant/Exon.
                                                if (in_array($_POST['coltocopyto'], array('Variant/DNA', 'Variant/Exon'))) {
                                                    $bSortUpdated = true;
                                                }
                                                $nUpdated += mysql_affected_rows();
                                                if ($sTableToCopyTo == 'Variant') {
                                                    @mysql_query('UPDATE ' . TABLE_PAT2VAR . ' SET edited_by = "' . $_AUTH['userid'] . '", edited_date = NOW() WHERE symbol = "' . $sSymbol . '" AND variantid = "' . $nCopyToID . ($sPrefix == 'v.' ? '"' : '" AND patientid = "' . $nFindID . '"'));
                                                } else {
                                                    @mysql_query('UPDATE ' . TABLE_PATIENTS . ' SET edited_by = "' . $_AUTH['userid'] . '", edited_date = NOW() WHERE patientid = "' . $nCopyToID . '"');
                                                }
                                            } else {
                                                // Throw some error.
                                                require ROOT_PATH . 'inc-top.php';
                                                lovd_printHeader('config_free_edit_copy', 'LOVD Configuration - Copy column');
                                                lovd_dbFout('FreeEditCC', $sQupdate, mysql_error());
                                                require ROOT_PATH . 'inc-bot.php';
                                                exit;
                                            }
                                        }
                                    }

                                    // Empty the copied column.
                                    if ($_POST['empty_copied_field']) {
                                        if ($sPrefix == 'v.') {
                                            mysql_query('UPDATE ' . TABLEPREFIX . '_' . $sSymbol . '_variants SET `' . $_POST['col'] . '` = "" WHERE variantid = "' . $nFindID . '"');
                                            // 2009-06-30; 2.0-19; Added a flag for updated columns Variant/DNA or Variant/Exon.
                                            if (in_array($_POST['col'], array('Variant/DNA', 'Variant/Exon'))) {
                                                $bSortUpdated = true;
                                            }
                                            mysql_query('UPDATE ' . TABLE_PAT2VAR . ' SET edited_by = "' . $_AUTH['userid'] . '", edited_date = NOW() WHERE symbol = "' . $sSymbol . '" AND variantid = "' . $nFindID . '"');
                                        } elseif ($sPrefix == 'p.') {
                                            mysql_query('UPDATE ' . TABLE_PATIENTS . ' SET edited_by = "' . $_AUTH['userid'] . '", edited_date = NOW(), `' . $_POST['col'] . '` = "" WHERE patientid = "' . $nFindID . '"');
                                        }
                                    }
                                }
                            }



                            if ($bUpdated) {
                                // Update the DBS table.
                                lovd_setUpdatedDate($sSymbol);
                                // 2009-07-02; 2.0-19; Update the sort column.
                                if ($bSortUpdated && ($aCURRDBs[$sSymbol]->colExists('Variant/DNA') || $aCURRDBs[$sSymbol]->colExists('Variant/RNA'))) {
                                    $qSort = mysql_query('SELECT variantid, `' . $aCURRDBs[$sSymbol]->getMutationCol() . '`' . ($aCURRDBs[$sSymbol]->colExists('Variant/Exon') ? ', `Variant/Exon`' : '') . ' FROM ' . TABLEPREFIX . '_' . $sSymbol . '_variants');
                                    while ($aColSort = @mysql_fetch_row($qSort)) {
                                        mysql_query('UPDATE ' . TABLEPREFIX . '_' . $sSymbol . '_variants SET sort = "' . lovd_sort($aColSort[1], (!empty($aColSort[2])? $aColSort[2] : 0)) . '" WHERE variantid = "' . $aColSort[0] . '"');
                                    }
                                }
                            }
                        } // end of foreach



                        // All OK!
                        // Write to log...
                        if ($_GET['action'] == 'fnr') {
                            // replace
                            lovd_writeLog('MySQL:Event', 'FreeEditFNR', $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ') successfully ran find &amp; replace on ' . $_POST['col'] . ' in ' . implode(', ', $_POST['genes']));
                        } else {
                            // move or copy
                            lovd_writeLog('MySQL:Event', 'FreeEditCC', $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ') successfully ran copy column' . ($_POST['empty_copied_field']? ' (moved values)' : '') . ' from ' . $_POST['col'] . ' to ' . $_POST['coltocopyto'] . ' in ' . implode(', ', $_POST['genes']));
                        }

                        // Thank the user...
                        if (!$nFieldTooShort) {
                            header('Refresh: 5; url=' . PROTOCOL . $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/') . '/config.php' . lovd_showSID());
                        }

                        require ROOT_PATH . 'inc-top.php';
                        lovd_printHeader('config_free_edit_fnr', 'LOVD Configuration - Find & Replace');
                        lovd_showInfoTable('Successfully applied find &amp; replace!<BR>Entries matched: ' . $nMatched . ', entries updated: ' . $nUpdated . '.', 'success');
                        if ($nFieldTooShort) {
                            lovd_showInfoTable($nFieldTooShort . ' entr' . ($nFieldTooShort == 1? 'y ' : 'ies ') . ($nFieldTooShort == 1? 'was' : 'were') . ' skipped because the field is too short to fit the new value in.' . ($_AUTH['level'] >= LEVEL_MANAGER? ' <A href="' . ROOT_PATH . 'setup_columns_global.php?action=edit&amp;edit=' . rawurlencode($_POST['col']) . '">Click here to increase the size of the field</A>.' : ''), 'warning');
                        }
                        // In case of an empty copied field
                        if ($nFieldEmpty) {
                            lovd_showInfoTable($nFieldEmpty . ' entr' . ($nFieldEmpty == 1? 'y ' : 'ies ') . ($nFieldEmpty == 1? 'was' : 'were') . ' skipped because the copied field was empty.', 'warning');
                        }
                        require ROOT_PATH . 'inc-bot.php';
                        exit;
                    } // end of if (!lovd_error())
                } // end of if (isset($_GET['confirm'] {



                // Unquote at all times.
                lovd_magicUnquoteAll();

                // To continue, we need to create a form and send all data.
                require ROOT_PATH . 'inc-top.php';

                if ($_GET['action'] == 'fnr') {
                    lovd_printHeader('config_free_edit_fnr', 'LOVD Configuration - Find & Replace');
                } else {
                    lovd_printHeader('config_free_edit_copy', 'LOVD Configuration - Copy column');
                }

                lovd_showInfoTable('Your find matched ' . $nMatched . ' entr' . ($nMatched == 1? 'y' : 'ies') . '. Are you sure you want to continue?');

                lovd_errorPrint();

                // 2009-04-23; 2.0-19 by Gerard: to pass through $_GET variables
                $sSearchLink = '';
                foreach ($_GET as $key => $val) {
                    if (substr($key, 0, 7) == 'search_') {
                        $sSearchLink .= '&amp;' . $key . '=' . rawurlencode($val);
                    }
                }

                print('<FORM action="' . $_SERVER['PHP_SELF'] . '?action=' . $_GET['action'] . $sSearchLink . '&amp;confirm=true&amp;sent=true" method="post">' . "\n");

                foreach ($_POST['genes'] as $key => $value) {
                    print('      <INPUT type="hidden" name="genes[]" value="' . $_POST['genes'][$key] . '">' . "\n");
                }
                if ($_GET['action'] == 'copy') {
                    print('      <INPUT type="hidden" name="coltocopyto" value="' . $_POST['coltocopyto'] . '">' . "\n" .
                          '      <INPUT type="hidden" name="empty_copied_field" value="' . $_POST['empty_copied_field'] . '">' . "\n" .
                          '      <INPUT type="hidden" name="action" value="' . $_POST['action'] . '">' . "\n");
                } else {
                    // 2009-06-29; 2.0-19; Moved this to avoid notice when action is copy.
                    print('        <INPUT type="hidden" name="replace" value="' . htmlspecialchars($_POST['replace']) . '">' . "\n");
                }
                print('        <INPUT type="hidden" name="col" value="' . $_POST['col'] . '">' . "\n" .
                      '        <INPUT type="hidden" name="type" value="' . $_POST['type'] . '">' . "\n" .
                      '        <INPUT type="hidden" name="find" value="' . htmlspecialchars($_POST['find']) . '">' . "\n");

                print('        <TABLE border="0" cellpadding="0" cellspacing="1" width="760">' . "\n" .
                      '          <TR>' . "\n" .
                      '            <TD valign="top" width="40%">Enter your password for authorization</TD>' . "\n" .
                      '            <TD width="60%"><INPUT type="password" name="password" size="20" value=""></TD></TR>' . "\n" .
                      '          <TR>' . "\n" .
                      '            <TD valign="top" width="40%"></TD>' . "\n" .
                      '            <TD width="60%"><INPUT type="submit" value="' . ($_GET['action'] == 'fnr'? 'Find &amp; Replace' : 'Copy column') . '"> <INPUT type="submit" value="Cancel" name="cancel"></TD></TR></TABLE></FORM>');

                require ROOT_PATH . 'inc-bot.php';
                exit;
            } // end of if (!lovd_error())
        }

    } else {
        if ($_SESSION['currdb']) {
            $_POST['genes'] = array($_SESSION['currdb']);
        }
    }



    // Unquote at all times.
    lovd_magicUnquoteAll();

    require ROOT_PATH . 'inc-top.php';
    if ($_GET['action'] == 'fnr') {
        lovd_printHeader('config_free_edit_fnr', 'LOVD Configuration - Find & Replace');
    } else {
        lovd_printHeader('config_free_edit_copy', 'LOVD Configuration - Copy column');
    }

    lovd_showInfoTable(($_GET['action'] == 'fnr'? 'Find &amp; Replace' : 'Copy Column') . ' applies to all values of a certain field in the database. Be extremely careful when using this function since misuse can render an entire field from the database useless. It would be wise to create a backup of the relevant gene databases first.', 'warning');

    lovd_errorPrint();

    // 2009-04-23; 2.0-19; added by Gerard: to pass through $_GET variables
    $sSearchLink = '';
    foreach ($_GET as $key => $val) {
        if (substr($key, 0, 7) == 'search_') {
            $sSearchLink .= '&amp;' . $key . '=' . rawurlencode($val);
        }
    }

    // Table.
    print('      <FORM action="' . $_SERVER['PHP_SELF'] . '?action=' . $_GET['action'] . $sSearchLink . '&amp;sent=true" method="post">' . "\n" .
          '        <TABLE border="0" cellpadding="0" cellspacing="1" width="760">');

    // Find which column to apply this to. Because this can be applied in any of the genes, we're just going to show all columns and figure out later which gene this applies to, then.
    $aColumns = array();
    // Variant and patient columns.
    $qCols = mysql_query('SELECT colid, CONCAT(colid, " (", head_column, ")") AS head_column FROM ' . TABLE_COLS . ' ORDER BY colid');
    while ($r = mysql_fetch_row($qCols)) {
        $aColumns[$r[0]] = $r[1];
    }
    $aColumns['submitterid'] = 'submitterid (Submitter)';

    // Array which will make up the form table.
    $aForm = array(
                    array('POST', '', '', '40%', '60%'),
                    array('', 'print', '<B>Apply this action to these genes</B>'),
                    array('Select genes', 'select', 'genes', ($nGenes > 5? 5 : $nGenes), $aGenes, false, true, true),
                    'skip',
                  );
    if ($_GET['action'] == 'fnr') {
        $aForm = array_merge($aForm,
                 array(
                    array('Apply find &amp; replace to', 'select', 'col', 1, $aColumns, true, false, false),
                    'skip',
                    array('Match type', 'select', 'type', 1, $aTypes, false, false, false),
                    array('Find text', 'text', 'find', 20),
                    array('Replace text', 'text', 'replace', 20),
                      ));
    } else {
        $aForm = array_merge($aForm,
                 array(
                    array('Copy values from', 'select', 'col', 1, $aColumns, true, false, false),
                    'skip',
                    array('Match type', 'select', 'type', 1, $aTypes, false, false, false),
                    array('Find text', 'text', 'find', 20),
                    array('Copy value to', 'select', 'coltocopyto', 1, $aColumns, true, false, false),
                    array('Append or overwrite', 'select', 'action', 1, $aActions, false, false, false),
                      ));
    }
    // 2009-03-09; 2.0-19 by Gerard
    if ($sSearchLink) {
        $aForm[] = array('', 'print', 'Only on entries matching these additional rules:');

        foreach ($_GET as $key => $value) {
            if (substr($key, 0, 7) == 'search_') {
                $a = explode(' ', htmlspecialchars(trim($value)));
                foreach ($a as $sTerm) {
                    if ($sTerm) {
                        if (substr_count($sTerm, '|') && preg_match('/^[^|]+(\|[^|]+)+$/', $sTerm)) {
                            // OR.
                            $aOR = explode('|', $sTerm);
                            $sPrintValue = ' (';
                            foreach ($aOR as $nTerm => $sTerm) {
                                if (substr($sTerm, 0, 1) == '!') {
                                    // NOT.
                                    $sPrintValue .= ($nTerm? ' or ' : '') . 'no ' . substr($sTerm, 1);
                                } else {
                                    // Common search term.
                                    $sPrintValue .= ($nTerm? ' or ' : '') . ($sTerm);
                                }
                            }
                            $sPrintValue .= ')';
                            $aForm[] = array('', 'print', '"' . rtrim(substr($key, 7), '_') . ' contains ' . $sPrintValue . '"');
                        } elseif (substr($sTerm, 0, 1) == '!') {
                            // NOT.
                            $aForm[] = array('', 'print', '"' . rtrim(substr($key, 7), '_') . ' does not contain ' . substr($sTerm, 1) . '"');
                        } else {
                            // Common search term.
                            $aForm[] = array('', 'print', '"' . rtrim(substr($key, 7), '_') . ' contains ' . $sTerm . '"');
                        }
                    }
                }
            }
        }
    }
    if ($_GET['action'] == 'copy') {
        $aForm[] = array('Move the copied field', 'checkbox', 'empty_copied_field', 1);
    }
    $aForm[] = 'skip';
    $aForm[] = array('', 'submit', 'Continue');
    $_MODULES->processForm('ConfigFreeEdit' . ($_GET['action'] == 'fnr'? 'FNR' : 'CC'), $aForm);
    lovd_viewForm($aForm);

    print('</TABLE></FORM>' . "\n\n");
    
    require ROOT_PATH . 'inc-bot.php';
    exit;





} else {
    // Default action:
    require ROOT_PATH . 'inc-top.php';
    lovd_printHeader('config_free_edit', 'LOVD Configuration - Free edit');

    // Provide choice.
    print('            <TABLE border="0" cellpadding="2" cellspacing="0" class="setup" width="100%">' . "\n" .
          '              <TR>' . "\n" .
          '                <TD colspan="2"><B>Please select your choice</B></TD></TR>' . "\n" .
          '              <TR class="setup" onclick="window.location.href=\'' . $_SERVER['PHP_SELF'] . '?action=fnr' . lovd_showSID(true, true) . '\';">' . "\n" .
          '                <TD align="center" width="40"><IMG src="' . ROOT_PATH . 'gfx/lovd_free_edit_fnr.png" alt="Find & Replace" width="32" height="32"></TD>' . "\n" .
          '                <TD>Find &amp; Replace: Find certain values in a specific column and replace it with a different value.</TD></TR>' . "\n" .
          '              <TR class="setup" onclick="window.location.href=\'' . $_SERVER['PHP_SELF'] . '?action=copy' . lovd_showSID(true, true) . '\';">' . "\n" .
          '                <TD align="center" width="40"><IMG src="' . ROOT_PATH . 'gfx/lovd_free_edit_copy.png" alt="Copy column" width="32" height="32"></TD>' . "\n" .
          '                <TD>Copy Column: Copy or move one column\'s contents into another column.</TD></TR></TABLE><BR>' . "\n");

    require ROOT_PATH . 'inc-bot.php';
    exit;
}
?>