<?php
/*******************************************************************************
 *
 * LEIDEN OPEN VARIATION DATABASE (LOVD)
 *
 * Created     : 2006-11-21
 * Modified    : 2011-01-20
 * For LOVD    : 2.0-30
 *
 * Access      : Submitters and up.
 * Purpose     : Edit & drop patients.
 *
 * Copyright   : 2004-2011 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 : Ir. Gerard C.P. Schaafsma <G.C.P.Schaafsma@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 curator clearance.
// 2008-04-21; 2.0-06; Allow submitters to access the edit form.
if (!(!empty($_GET['action']) && $_GET['action'] == 'edit' && HAS_SUBS && $_CONF['my_submissions'])) {
    lovd_requireAUTH(LEVEL_CURATOR);
}

// If curator, the patient can't have any variants associated with genes not curated by the curator.
if ($_GET['action'] == 'edit' && !empty($_GET['edit'])) {
    $_GET['patientid'] = $_GET['edit'];
} elseif ($_GET['action'] == 'drop' && !empty($_GET['drop'])) {
    $_GET['patientid'] = $_GET['drop'];
}

// Check if user is allowed to edit patients.
if (empty($_GET['patientid'])) {
    $bEditable = false;

} elseif (HAS_SUBS) {
    // 2008-04-21; 2.0-06 Submitters can now also edit patients when My Submissions is activated.
    list($nSubmitterID) = mysql_fetch_row(mysql_query('SELECT submitterid FROM ' . TABLE_PATIENTS . ' WHERE patientid = "' . $_GET['patientid'] . '"'));
    if ($nSubmitterID == $_SUBS['submitterid']) {
        $bEditable = true;
    } else {
        // Instead of the default error, throw in a specific one.
        require ROOT_PATH . 'inc-top.php';
        lovd_showInfoTable('This patient has not been submitted by you!', 'stop');
        require ROOT_PATH . 'inc-bot.php';
        exit;
    }

} elseif ($_AUTH['level'] < LEVEL_MANAGER) {
    if (empty($_AUTH['curates'])) {
        $bEditable = false;
    } else {
        $bEditable = !mysql_num_rows(mysql_query('SELECT symbol FROM ' . TABLE_PAT2VAR . ' WHERE patientid = "' . $_GET['patientid'] . '" AND symbol NOT IN ("' . implode('", "', $_AUTH['curates']) . '") GROUP BY symbol'));
    }
} else {
    $bEditable = true;
}

if (!$bEditable) {
    require ROOT_PATH . 'inc-top.php';
    lovd_showInfoTable('You are not allowed access to this patient, because the patient has sequence variants from genes you do not control.', 'stop');
    require ROOT_PATH . 'inc-bot.php';
    exit;
}





if ($_GET['action'] == 'edit' && is_numeric($_GET['patientid'])) {
    // Edit specific patient.

    $zData = @mysql_fetch_assoc(mysql_query('SELECT p.* FROM ' . TABLE_PATIENTS . ' AS p WHERE p.patientid = "' . $_GET['patientid'] . '"'));
    if (!$zData) {
        // Wrong ID, apparently.
        require ROOT_PATH . 'inc-top.php';
        lovd_printHeader('patients_manage', 'LOVD Manage patients');
        lovd_showInfoTable('No such ID!', 'stop');
        require ROOT_PATH . 'inc-bot.php';
        exit;
    }

    // Require form functions.
    require ROOT_PATH . 'inc-lib-form.php';
    require ROOT_PATH . 'class/currdb.php';
    $_CURRDB = new CurrDB();
    // 2008-04-21; 2.0-06; Submitters can now also edit patients when My Submissions is activated.
    if (HAS_SUBS) {
        $_CURRDB->hideCols('public_form');
    }

    // 2008-05-30; 2.0-07; Add agree to submitter's changes option.
    if (isset($_GET['agree']) && !isset($_GET['sent'])) {
        // Agree to this entry's changes... nothing edited.
        foreach ($zData as $key => $val) {
            // 2009-04-29; 2.0-18; Added mysql_real_escape_string() to prevent MySQL errors and SQL injection problem.
            $_POST[$key] = mysql_real_escape_string($val);
        }
        // Transform select lists' values.
        $_CURRDB->transformSelectValues($_POST, true);
        $_POST['password'] = $_AUTH['password'];
        $_GET['sent'] = true;
    }

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

        // Mandatory fields.
        $_CURRDB->checkMandatory('Patient');

        // Field lengths.
        $_CURRDB->checkInputLength();

        // Field types.
        $_CURRDB->checkInputType();

        // Regular expressions.
        $_CURRDB->checkInputRegExp();

        // Mandatory fields.
        $aCheck =
                 array(
                        'password' => 'Enter your password for authorization',
                      );

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

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

        // 2006-12-07; 2.0-alpha-02
        // XSS attack prevention. Simply deny input of HTML, PHP other stuff blocked by strip_tags().
        lovd_checkXSS();

        if (!lovd_error()) {
            // 2009-07-02; 2.0-19; Don't do anything when a submitter edits, and nothing is changed.
            if (HAS_SUBS) {
                // Check if something changed.
                $bChanged = false;

                // Get columns to update in the database.
                $aCols = $_CURRDB->getColList('Patient');
                foreach ($aCols as $sCol) {
                    $sVal = (empty($_POST[$sCol])? '' : $_POST[$sCol]);
                    // Selection list to value.
                    if (is_array($sVal)) {
                        $sVal = implode(';', $sVal);
                    }
                    if ($sVal != $zData[$sCol]) {
                        $bChanged = true;
                        break;
                    }
                }

                // Now, if nothing changed, just kick the user out right here.
                if (!$bChanged) {
                    header('Refresh: 3; url=' . PROTOCOL . $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/') . '/variants.php?select_db=' . $_SESSION['currdb'] . '&action=view&view=' . $zData['patientid'] . lovd_showSID(true));

                    require ROOT_PATH . 'inc-top.php';
                    lovd_printHeader('patients_manage', 'LOVD Manage patients');

                    lovd_showInfoTable('There were no changes submitted!', 'success');

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



            // Query text.
            $sQ = 'UPDATE ' . TABLE_PATIENTS . ' SET ';

            // Get columns to update in the database.
            $aCols = $_CURRDB->getColList('Patient');
            foreach ($aCols as $key => $val) {
                // 2007-06-28; 2.0-beta-05; sometimes fields are not sent. HTTP posting faked or empty selection list?
                if (!isset($_POST[$val])) {
                    $_POST[$val] = '';
                }

                // Selection list to value.
                if (is_array($_POST[$val])) {
                    $_POST[$val] = implode(';', $_POST[$val]);
                }
                $sQ .= ($key? ', ' : '') . '`' . $val . '` = "' . $_POST[$val] . '"';
            }

            $sQ .= ', edited_by = "' . (HAS_AUTH? $_AUTH['userid'] : 0) . '", edited_date = NOW() WHERE patientid = "' . $_GET['patientid'] . '"';

            $q = mysql_query($sQ);
            if (!$q) {
                $sError = mysql_error(); // Save the mysql_error before it disappears.
                require ROOT_PATH . 'inc-top.php';
                lovd_printHeader('patients_manage', 'LOVD Manage patients');
                lovd_dbFout('PatientEdit', $sQ, $sError);
            }

            // 2007-12-05; 2.0-02; Fixed bug #20 - Gene's "Last update" field not updated.
            // Fetch list of genes this patient is involved in.
            $qGenes = mysql_query('SELECT DISTINCT symbol FROM ' . TABLE_PAT2VAR . ' WHERE patientid = "' . $_GET['patientid'] . '"');
            while (list($sGene) = mysql_fetch_row($qGenes)) {
                lovd_setUpdatedDate($sGene);
            }

            // Write to log...
            // 2010-07-19; 2.0-28; Added lovd_reportChanges() to report in the log entry which changes were made.
            lovd_writeLog('MySQL:Event', 'PatientEdit', $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ') successfully ' . (isset($_GET['agree'])? 'agreed to ' : '') . 'edited patient #' . $zData['patientid'] . lovd_reportChanges($zData, $aCols, $_POST));



            // 2008-04-21; 2.0-06; Submitters can now also edit patients when My Submissions is activated.
            if (HAS_SUBS) {
                // This was a submitter's work. Some extra options and send email to curator(s).

                // Grab email adresses of addressees, to prevent double emails.
                $aEmails = array();
                $aGenes = array();

                // Find me the curator of all genes...
                $qCurators = mysql_query('SELECT u.name, u.email, c.symbol FROM ' . TABLE_USERS . ' AS u LEFT JOIN ' . TABLE_CURATES . ' AS c USING (userid) LEFT JOIN ' . TABLE_PAT2VAR . ' AS p2v USING (symbol) WHERE p2v.patientid = "' . $_GET['patientid'] . '" ORDER BY u.level DESC, u.name');
                $nCurators = 0;
                $sCurators = '';
                while ($r = mysql_fetch_row($qCurators)) {
                    // If email hasn't been added before, add it.
                    if (!in_array($r[1], $aEmails)) {
                        // 2009-02-27; 2.0-16; Sending emails on Windows requires removal of names from the email addresses.
                        $sCurators .= ($sCurators? ', ' : '') . (ON_WINDOWS? '' : '"' . $r[0] . '" ') . '<' . $r[1] . '>';
                        $aEmails[] = $r[1];
                        $nCurators ++;
                    }
                    // Add gene to the list.
                    $aGenes[] = $r[2];
                }

                // 2009-02-27; 2.0-16; Sending emails on Windows requires removal of names from the email addresses.
                $sSubmitter = (ON_WINDOWS? '' : '"' . $_AUTH['name'] . '" ') . '<' . str_replace(array("\r\n", "\r", "\n"), '>, <', trim($_AUTH['email'])) . '>';
                $aGenes = array_unique($aGenes);
                $nGenes = count($aGenes);
                $sGenes = '';
                if ($nGenes == 1) {
                    $sGenes = $aGenes[0] . ' database';
                } else {
                    foreach ($aGenes as $n => $sGene) {
                        if ($n == ($nGenes - 1)) {
                            $sGenes .= ' and ' . $sGene . ' databases';
                        } else {
                            $sGenes .= ($n? ', ' : '') . $sGene;
                        }
                    }
                }

                $sBody = 'LOVD ' . $_SETT['system']['version'] . ' @ ' . $_CONF['location_name'] . "\n" .
                         'To Curator' . ($nCurators == 1? ' ' : 's') . '  : ' . str_replace('"', '', $sCurators) . "\n" .
                         'CC Submitter : ' . str_replace('"', '', $sSubmitter) . "\n\n" .
                         'Dear Curator' . ($nCurators == 1? '' : 's') . ',' . "\n\n" .
                         $_AUTH['name'] . ' has edited a patient entry with variants in the ' . $sGenes . '.' . "\n" .
                         'Below is a copy of the updated patient information. Changes are indicated with an asterisk.' . "\n\n";
                // 2009-04-06; 2.0-17; Add the location of the entry, so that the curator can just click the link.
                if ($_CONF['location_url']) {
                    $sBody .= 'To view the entry, click this link (you may need to log in first):' . "\n" .
                              $_CONF['location_url'] . 'variants.php?select_db=' . $aGenes[0] . '&action=view&view=' . $_GET['patientid'] . "\n\n";
                }
                $sBody .= 'Regards,' . "\n" .
                          '    LOVD system at ' . $_CONF['location_name'] . "\n\n";

                // Submitter information.
                $sBody .= str_repeat('-', 80) . "\n" .
                          '  SUBMITTER DETAILS' . "\n" .
                          str_repeat('-', 80) . "\n";

                // Array containing the fields.
                $aMail =
                         array(
                                'submitterid' => 'Submitter ID',
                                'name' => 'Name',
                                'institute' => 'Institute',
                                'department' => 'Department',
                                'address' => 'Address',
                                'city' => 'City',
                                'country_' => 'Country',
                                'email' => 'Email address',
                                'telephone' => 'Telephone',
                              );

                // Fetching country name.
                list($_AUTH['country_']) = mysql_fetch_row(mysql_query('SELECT country FROM ' . TABLE_COUNTRIES . ' WHERE code = "' . $_AUTH['country'] . '"'));

                // Padding to...
                $lPad = 0;
                foreach ($aMail as $val) {
                    $l = strlen($val);
                    if ($l > $lPad) {
                        $lPad = $l;
                    }
                }

                foreach ($aMail as $key => $val) {
                    $sBody .= str_pad($val, $lPad) . ' : ' . str_replace("\n", "\n" . str_repeat(' ', $lPad + 3), lovd_wrapText($_AUTH[$key], 80 - $lPad - 3)) . "\n";
                }
                $sBody .= str_repeat('-', 80) . "\n\n";

                // Patient information.
                $sBody .= str_repeat('-', 80) . "\n" .
                          '  PATIENT DETAILS' . "\n" .
                          str_repeat('-', 80) . "\n";

                // Array containing the fields.
                $aMail = $_CURRDB->buildTable('detail');

                // Remove columns not for variants.
                $aCols = $_CURRDB->getColList('Patient');
                foreach ($aMail as $sCol => $aCol) {
                    if (!in_array($sCol, $aCols)) {
                        unset($aMail[$sCol]);
                    }
                }

                // Padding to...
                $lPad = 0;
                foreach ($aMail as $val) {
                    $l = strlen($val);
                    if ($l > $lPad) {
                        $lPad = $l;
                    }
                }

                // Good practice when mailing and printing.
                lovd_magicUnquote($_POST);

                // Transform select lists' values.
                $_CURRDB->transformSelectValues($_POST);
                $_CURRDB->transformSelectValues($zData); // So we can match the two to track changes.

                foreach ($aMail as $key => $val) {
                    // $lPad '± 2' specific for editing!
                    $sBody .= str_pad($val, $lPad) . (isset($_POST[$key]) && $zData[$key] != $_POST[$key]? ' *' : '  ') . ' : ' . str_replace("\n", "\n" . str_repeat(' ', $lPad + 3 + 2), lovd_wrapText($_POST[$key], 80 - $lPad - 3 - 2)) . "\n";
                }
                $sBody .= str_repeat('-', 80) . "\n";

                // Set proper subject.
                $sSubject = 'LOVD submission update';

                // Send mail.
                $bMail = @mail($sCurators,
                               $sSubject,
                               lovd_wrapText($sBody),
                               $_SETT['headers'] . $sEol .
                               'Reply-To: ' . $sSubmitter . $sEol .
                               'Cc: ' . $sSubmitter);



                // Mail the database admin if he's not the curator(s).
                if ($bMail) {
                    if ($_CONF['send_fwd'] && !in_array($_SETT['admin']['email'], $aEmails)) {
                        // Sent mail to curator(s), send copy to database administrator.
                        $sBody = 'Dear ' . $_SETT['admin']['name'] . ",\n\n" .
                                 'As requested, a copy of the message I\'ve just sent.' . "\n\n" .
                                 str_repeat('-', 30) . ' Forwarded  Message ' . str_repeat('-', 30) . "\n\n" .
                                 rtrim($sBody) . "\n\n" .
                                 str_repeat('-', 27) . ' End of Forwarded Message ' . str_repeat('-', 27) . "\n";

                        // Send mail.
                        // 2009-02-27; 2.0-16; Sending emails on Windows requires removal of names from the email addresses.
                        $bMailA = @mail((ON_WINDOWS? '' : '"' . $_SETT['admin']['name'] . '" ') . '<' . $_SETT['admin']['email'] . '>',
                                        'Fw: ' . $sSubject,
                                        lovd_wrapText($sBody),
                                        $_SETT['headers'] . $sEol .
                                        'Reply-To: ' . $sSubmitter);

                        if (!$bMailA) {
                            // Couldn't sent confirmation to admin...
                            lovd_writeLog('MySQL:Error', 'EmailNotify', $_SERVER['PHP_SELF'] . ' returned SubmitUpdateNotifyAdmin error for submitter ' . $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ')');
                        }
                    }

                } else {
                    // Couldn't sent confirmation...
                    lovd_writeLog('MySQL:Error', 'EmailNotify', $_SERVER['PHP_SELF'] . ' returned SubmitUpdateNotify error for submitter ' . $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ')');
                }
            }



            // Thank the user...
            // 2007-08-09; 2.0-beta-07; View patient information without considering any variants is now possible.
 //            header('Refresh: 3; url=' . PROTOCOL . $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/') . '/variants.php?action=view_all' . lovd_showSID(true));
            header('Refresh: ' . (isset($_GET['agree'])? '0' : '3') . '; url=' . PROTOCOL . $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/') . '/variants.php?select_db=' . $_SESSION['currdb'] . '&action=view&view=' . $zData['patientid'] . lovd_showSID(true));

            require ROOT_PATH . 'inc-top.php';
            lovd_printHeader('patients_manage', 'LOVD Manage patients');

            $sMessage = 'Successfully edited patient!';
            if (HAS_SUBS) {
                if ($bMail) {
                    $sMessage .= '<BR>The curator' . ($nCurators == 1? '' : 's') . ' of the ' . $sGenes . ' ha' . ($nCurators == 1? 's' : 've') . ' been sent an email containing your changes. A copy has been sent to you.';
                } else {
                    $sMessage .= '<BR>Due to an unknown error, no email regarding the submission could be sent to the curator' . ($nCurators == 1? '' : 's') . ' of the ' . $sGenes . '. Your changes have successfully been added to the database.';
                }
            }
            lovd_showInfoTable($sMessage, 'success');

            require ROOT_PATH . 'inc-bot.php';
            exit;

        } else {
            // Errors, so the whole lot returns to the form.
            lovd_magicUnquoteAll();

            // Because we're sending the data back to the form, I need to unset the password fields!
            unset($_POST['password']);
        }

    } else {
        foreach ($zData as $key => $val) {
            if (!isset($_POST[$key]) || !$_POST[$key]) {
                $_POST[$key] = $val;
            }
        }
        // Transform select lists' values.
        $_CURRDB->transformSelectValues($_POST, true);
        $_POST['password'] = '';
    }



    require ROOT_PATH . 'inc-top.php';
    lovd_printHeader('patients_manage', 'LOVD Manage patients');

    if (!isset($_GET['sent'])) {
        print('      To edit the patient data, please fill out the form below.<BR>' . "\n" .
              '      <BR>' . "\n\n");
    }

    lovd_errorPrint();

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

    // Array which will make up the form table.
    $aForm = array_merge(
                         array(
                                array('POST', '', '', '40%', '60%'),
                              ),
                         $_CURRDB->buildFormTable('Patient'),
                         array(
                                'skip',
                                array('Enter your password for authorization', 'password', 'password', 20),
                                array('', 'submit', 'Edit patient data'),
                              ));
    $_MODULES->processForm('SubmitPatientsEdit', $aForm);
    lovd_viewForm($aForm);

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

    require ROOT_PATH . 'inc-bot.php';
    exit;





} elseif ($_GET['action'] == 'drop' && is_numeric($_GET['patientid'])) {
    // Drop specific patient, including all of it's links.

    // Require form functions.
    require ROOT_PATH . 'inc-lib-form.php';
    require ROOT_PATH . 'class/currdb.php';
    $_CURRDB = new CurrDB();

    $zData = @mysql_fetch_assoc(mysql_query('SELECT p.* FROM ' . TABLE_PATIENTS . ' AS p WHERE p.patientid = "' . $_GET['patientid'] . '"'));
    if (!$zData) {
        // Wrong ID, apparently.
        require ROOT_PATH . 'inc-top.php';
        lovd_printHeader('patients_manage', 'LOVD Manage patients');
        lovd_showInfoTable('No such ID!', 'stop');
        require ROOT_PATH . 'inc-bot.php';
        exit;
    }

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

        // Mandatory fields.
        $aCheck =
                 array(
                        'password' => 'Enter your password for authorization',
                      );

        foreach ($aCheck as $key => $val) {
            if (empty($_POST[$key])) {
                lovd_errorAdd('Please fill in the \'' . $val . '\' 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()) {
            // Drop Patient links, Remove orphaned patients from TABLE_PATIENTS and orphaned variants from TABLE_CURRDB_VARS.

            require ROOT_PATH . 'inc-top.php';
            lovd_printHeader('patients_manage', 'LOVD Manage patients');

            // If using transactional tables; begin transaction.
            if ($_INI['database']['engine'] == 'InnoDB') {
                // FIXME; It's better to use 'START TRANSACTION', but that's only available from 4.0.11.
                //   This works from the introduction of InnoDB in 3.23
                @mysql_query('BEGIN WORK');
            }

            // Start with the patient's connection.
            print('      Removing patient...<BR>' . "\n" .
                  '      Removing variant <-> patient link(s) ... ');
            flush();
            // Before we start deleting this stuff, I need this information for removing orphaned variants in other genes.
            $aGenes = array($_SESSION['currdb']);
            $sQ = 'SELECT DISTINCT symbol FROM ' . TABLE_PAT2VAR . ' WHERE patientid = "' . $_GET['patientid'] . '" AND symbol != "' . $_SESSION['currdb'] . '" ORDER BY symbol';
            $q = @mysql_query($sQ);
            if (!$q) {
                // OK, this failed, but we will continue. We've got the current gene anyway.
                lovd_dbFout('PatientDropFindGenes', $sQ, mysql_error(), false);
            } else {
                while ($r = mysql_fetch_row($q)) {
                    $aGenes[] = $r[0];
                }
            }

            // Remove...
            $sQ = 'DELETE FROM ' . TABLE_PAT2VAR . ' WHERE patientid = "' . $_GET['patientid'] . '"';
            $q = mysql_query($sQ);
            if (!$q) {
                lovd_dbFout('PatientDropA', $sQ, mysql_error());
            }
            print('OK<BR>' . "\n");

            // Orphaned patients.
            print('      Removing obsolete patients ... ');
            flush();
            // Backwards compatible with MySQL 4.0 and earlier. These versions do not support subqueries, which would really come in handy now.
            // Fist, determine the ID's of the orphaned patients. Then construct the DELETE query.
            $aOrphaned = array();
            $qOrphaned = mysql_query('SELECT p.patientid FROM ' . TABLE_PATIENTS . ' AS p LEFT OUTER JOIN ' . TABLE_PAT2VAR . ' AS p2v USING (patientid) WHERE p2v.symbol IS NULL');
            while ($rOrphaned = mysql_fetch_row($qOrphaned)) {
                $aOrphaned[] = $rOrphaned[0];
            }
            if (count($aOrphaned)) {
                // Construct DELETE query.
                $sQ = 'DELETE FROM ' . TABLE_PATIENTS . ' WHERE patientid IN (' . implode(', ', $aOrphaned) . ')';
                $q = mysql_query($sQ);
                if (!$q) {
                    lovd_dbFout('PatientDropB', $sQ, mysql_error());
                }
                print('OK<BR>' . "\n");
            } else {
                print('N/A<BR>' . "\n");
            }

            // Orphaned variants (multiple genes).
            print('      Removing obsolete variants...<BR>' . "\n");
            flush();
            foreach ($aGenes as $sGene) {
                print('      &nbsp;&nbsp;&nbsp;&nbsp;From ' . $sGene . ' ... ');
                flush();
                // Backwards compatible with MySQL 4.0 and earlier. These versions do not support subqueries, which would really come in handy now.
                // Fist, determine the ID's of the orphaned patients. Then construct the DELETE query.
                $aOrphaned = array();
                $qOrphaned = mysql_query('SELECT v.variantid FROM ' . TABLE_CURRDB_VARS . ' AS v LEFT OUTER JOIN ' . TABLE_PAT2VAR . ' AS p2v ON (v.variantid = p2v.variantid AND p2v.symbol = "' . $sGene . '") WHERE p2v.symbol IS NULL');
                while ($rOrphaned = mysql_fetch_row($qOrphaned)) {
                    $aOrphaned[] = $rOrphaned[0];
                }
                if (count($aOrphaned)) {
                    // Construct DELETE query.
                    $sQ = 'DELETE FROM ' . TABLEPREFIX . '_' . $sGene . '_variants WHERE variantid IN (' . implode(', ', $aOrphaned) . ')';
                    $q = mysql_query($sQ);
                    if (!$q) {
                        lovd_dbFout('PatientDropC', $sQ, mysql_error());
                    }
                    print('OK<BR>' . "\n");
                } else {
                    print('N/A<BR>' . "\n");
                }
            }
            print('      <BR>' . "\n\n");

            // If we don't do this, we haven't done anything in the DB... duh!
            if ($_INI['database']['engine'] == 'InnoDB') {
                // Could this actually fail?!!??
                @mysql_query('COMMIT');
            }

            // 2007-12-05; 2.0-02; Fixed bug #20 - Gene's "Last update" field not updated.
            foreach ($aGenes as $sGene) {
                lovd_setUpdatedDate($sGene);
            }

            // Write to log...
            lovd_writeLog('MySQL:Event', 'PatientDrop', $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ') successfully deleted patient #' . $_GET['patientid']);

            // Thank the user...
            lovd_showInfoTable('Successfully deleted patient!', 'success');

            // Alternate refresh; since we can't send a HTTP header...
            print('      <SCRIPT type="text/javascript">' . "\n" .
                  '      <!--' . "\n" .
                  '        setTimeout(\'window.location.href = "' . PROTOCOL . $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/') . '/variants.php?action=view_all' . lovd_showSID(true, true) . '"\', 5000);' . "\n" .
                  '      // -->' . "\n" .
                  '      </SCRIPT>' . "\n\n");

            require ROOT_PATH . 'inc-bot.php';
            exit;

        } else {
            // Errors, so the whole lot returns to the form.
            lovd_magicUnquoteAll();

            // Because we're sending the data back to the form, I need to unset the password fields!
            unset($_POST['password']);
        }
    }



    require ROOT_PATH . 'inc-top.php';
    lovd_printHeader('patients_manage', 'LOVD Manage patients');

    lovd_errorPrint();

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

    // Array which will make up the form table.
    $aForm = array(
                    array('POST', '', '', '50%', '50%'),
                    array('Deleting patient', 'print', ($zData['Patient/Patient_ID']? $zData['Patient/Patient_ID'] : '#' . $zData['patientid'])),
                    'skip',
                    array('Enter your password for authorization', 'password', 'password', 20),
                    array('', 'submit', 'Delete patient'),
                  );
    $_MODULES->processForm('SubmitPatientsDelete', $aForm);
    lovd_viewForm($aForm);

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

    require ROOT_PATH . 'inc-bot.php';
    exit;





} elseif ($_GET['action'] == 'assign_submitter' && is_numeric($_GET['patientid'])) {
    // Assign submitter to this patient.

    $zData = @mysql_fetch_assoc(mysql_query('SELECT p.patientid, p.submitterid FROM ' . TABLE_PATIENTS . ' AS p WHERE p.patientid = "' . $_GET['patientid'] . '"'));
    if (!$zData) {
        // Wrong ID, apparently.
        require ROOT_PATH . 'inc-top-clean.php';
        lovd_showInfoTable('No such ID!', 'stop');
        require ROOT_PATH . 'inc-bot-clean.php';
        exit;
    }

    // Require form functions.
    require ROOT_PATH . 'inc-lib-form.php';

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

        if (!lovd_error()) {
            // Query text.
            $sQ = 'UPDATE ' . TABLE_PATIENTS . ' SET submitterid = "' . $_POST['submitterid'] . '" WHERE patientid = "' . $_GET['patientid'] . '"';

            $q = mysql_query($sQ);
            if (!$q) {
                $sError = mysql_error(); // Save the mysql_error before it disappears.
                require ROOT_PATH . 'inc-top-clean.php';
                lovd_dbFout('PatientEdit', $sQ, $sError);
            }

            // Write to log...
            lovd_writeLog('MySQL:Event', 'SubmitterAssign', $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ') successfully ' . ($_POST['submitterid']? 'assigned submitter #' . $_POST['submitterid'] . ' to' : 'removed submitter association from') . ' patient #' . $zData['patientid']);

            // Thank the user...
            require ROOT_PATH . 'inc-top-clean.php';
            if ($_POST['submitterid']) {
                lovd_showInfoTable('Successfully assigned submitter #' . $_POST['submitterid'] . ' to patient!', 'success');
            } else {
                lovd_showInfoTable('Successfully cleared submitterid from patient!', 'success');
            }

            print('      <SCRIPT type="text/javascript">' . "\n" .
                  '        <!--' . "\n" .
                  '        opener.location.reload();' . "\n" .
                  '        setTimeout("self.close()", 1000);' . "\n" .
                  '        // -->' . "\n" .
                  '      </SCRIPT>' . "\n\n");

            require ROOT_PATH . 'inc-bot-clean.php';
            exit;

        } else {
            // Errors, so the whole lot returns to the form.
            lovd_magicUnquoteAll();

            // Because we're sending the data back to the form, I need to unset the password fields!
            unset($_POST['password']);
        }

    } else {
        $_POST['submitterid'] = $zData['submitterid'];
        $_POST['password'] = '';
    }



    require ROOT_PATH . 'inc-top-clean.php';

    if (!isset($_GET['sent'])) {
        print('      Please select the submitter you wish to assign to this patient.<BR>' . "\n" .
              '      <BR>' . "\n\n");
    }

    lovd_errorPrint();

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

    // 2008-01-29; 2.0-04; Added check for s.deleted = 0 to exclude deleted submitters.
    $qSubmitters = mysql_query('SELECT s.submitterid, CONCAT(c.country, " / ", s.city, " - ", s.firstname, " ", s.lastname, " (#", s.submitterid, ")") AS name FROM ' . TABLE_SUBS . ' AS s LEFT JOIN ' . TABLE_COUNTRIES . ' AS c ON (s.country = c.code) WHERE s.deleted = 0 ORDER BY name');

    // Array which will make up the form table.
    $aForm = array_merge(
                         array(
                                array('POST', '', '', '18%', '82%'),
                                array('Submitter', 'select', 'submitterid', 1, $qSubmitters, '-- None --', false, false),
                                'skip',
                                array('', 'submit', 'Assign submitter'),
                              ));
    $_MODULES->processForm('SubmitPatientsEdit', $aForm);
    lovd_viewForm($aForm);

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

    require ROOT_PATH . 'inc-bot-clean.php';
    exit;





} else {
    // Default action:
    header('Location: ' . PROTOCOL . $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/') . '/variants.php?action=view_all' . lovd_showSID(true));
    exit;
}
?>
