<?php
/*******************************************************************************
 *
 * LEIDEN OPEN VARIATION DATABASE (LOVD)
 *
 * Created     : 2006-11-13
 * Modified    : 2009-09-04
 * For LOVD    : 2.0-21
 *
 * Access      : Administrator and managers.
 * Purpose     : Create and manage selected LOVD custom patient columns.
 *
 * Copyright   : 2004-2009 Leiden University Medical Center; http://www.LUMC.nl/
 * Programmer  : Ing. Ivo F.A.C. Fokkema <I.F.A.C.Fokkema@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_MANAGER);





if ($_GET['action'] == 'view_all') {
    // View all patient cols.

    require ROOT_PATH . 'inc-lib-list.php';
    require ROOT_PATH . 'inc-lib-columns.php';
    require ROOT_PATH . 'inc-top.php';
    lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');

    list($nTotal) = mysql_fetch_row(mysql_query('SELECT COUNT(*) FROM ' . TABLE_PATIENTS_COLS));
    if (!$nTotal) {
        // Can this actually happen?
        lovd_showInfoTable('The system hasn\'t got any custom patient columns!', 'information');
        require ROOT_PATH . 'inc-bot.php';
        exit;
    }

    lovd_showInfoTable('Please note that the variant columns are missing from this overview because they are specific to the genes.', 'information', 950);

    // Standard query, will be extended later on.
    $sQ = 'SELECT t1.*, t2.* FROM ' . TABLE_COLS . ' AS t1 LEFT JOIN ' . TABLE_PATIENTS_COLS . ' AS t2 USING (colid) WHERE t1.colid = t2.colid';

    // SORT: Current settings.
    // 2008-08-07; 2.0-10; Implement XSS check on order variable.
    if (isset($_GET['order']) && $_GET['order'] && $_GET['order'] == strip_tags($_GET['order'])) {
        $aOrder = explode(',', $_GET['order']);
    } else {
        $aOrder = array('', '');
    }

    // SORT: Column data.
    $aOrderList = array('colid' => array('t1.colid', 'ASC'), 'hgvs' => array('t1.hgvs', 'DESC'), 'standard' => array('t1.standard', 'DESC'), 'head_column' => array('t1.head_column', 'ASC'), 'public' => array('t2.public', 'DESC'), 'col_order' => array('t2.col_order', 'ASC'));
    if (!array_key_exists($aOrder[0], $aOrderList)) {
        $aOrder[0] = 'col_order';
    }
    if ($aOrder[1] != 'ASC' && $aOrder[1] != 'DESC') {
        $aOrder[1] = $aOrderList[$aOrder[0]][1];
    }

    $sQueryLimit = lovd_pagesplitInit($nTotal, 25);
    $sQ .= ' ORDER BY ' . $aOrderList[$aOrder[0]][0] . ' ' . $aOrder[1] . ', t1.colid ' . $sQueryLimit;



    // Show form; required for sorting and searching.
    print('      <FORM action="' . $_SERVER['PHP_SELF'] . '" method="get" style="margin : 0px;">' . "\n" .
          '        <INPUT type="hidden" name="action" value="' . $_GET['action'] . '">' . "\n" .
          '        <INPUT type="hidden" name="order" value="' . implode(',', $aOrder) . '">');
    print('</FORM>' . "\n\n");

    $q = mysql_query($sQ);
    if (!$q) {
        lovd_dbFout('Cols' . ucfirst($_GET['action']), $sQ, mysql_error());
    }

    $n = (isset($nResults)? $nResults : $nTotal);
    print('      <SPAN class="S11">' . $n . ' entr' . ($n == 1? 'y' : 'ies') . '</SPAN><BR>' . "\n");

    // Array which will make up the table (header and data).
    $aTable =
             array(
                    'col_order' => array('Order', '60'),
                    'colid' => array('ID', '*'),
                    'hgvs' => array('HGVS', '60', 'align="center"'),
                    'head_column' => array('Column heading', '*'),
                    'form_type' => array('Form type', '*'),
                    'width' => array('Width (px)', '75', 'align="right"'),
                    'public' => array('Public', '60', 'align="center"'),
                  );

    // Table.
    print('      <TABLE border="0" cellpadding="0" cellspacing="1" width="950" class="data">' . "\n" .
          '        <TR>');

    foreach ($aTable as $sField => $aCol) {
        print("\n" . '          <TH' . (!empty($aCol[2])? ' ' . $aCol[2] : '') . (is_numeric($aCol[1])? ' width="' . $aCol[1] . '"' : '') . (array_key_exists($sField, $aOrderList)? ' class="order' . ($aOrder[0] == $sField? 'ed' : '') . '" onclick="document.forms[0].order.value=\'' . $sField . ',' . ($aOrder[0] == $sField? ($aOrder[1] == 'ASC'? 'DESC' : 'ASC') : $aOrderList[$sField][1]) . '\';document.forms[0].submit();"' : '') . '>' .
              (array_key_exists($sField, $aOrderList)? "\n" .
                                                           '            <TABLE border="0" cellpadding="0" cellspacing="0" width="100%" class="S11">' . "\n" .
                                                           '              <TR>' . "\n" .
                                                           '                <TH>' . str_replace(' ', '&nbsp;', $aCol[0]) . '</TH>' . "\n" .
                                                           '                <TD align="right"><IMG src="gfx/order_arrow_desc' . ($aOrder[0] == $sField && $aOrder[1] == 'DESC'? '_sel' : '') . '.png" alt="Descending" title="Descending" width="13" height="6"><BR><IMG src="gfx/order_arrow_asc' . ($aOrder[0] == $sField && $aOrder[1] == 'ASC'? '_sel' : '') . '.png" alt="Ascending" title="Ascending" width="13" height="6"></TD></TR></TABLE>' : $aCol[0]) . '</TH>');
    }
    print('</TR>');

    while ($zData = mysql_fetch_assoc($q)) {
        print("\n" .
              '        <TR style="cursor : pointer; cursor : hand;" onmouseover="this.className = \'hover\';" onmouseout="this.className = \'\';" onclick="window.location.href = \'' . $_SERVER['PHP_SELF'] . '?action=view&amp;view=' . rawurlencode($zData['colid']) . lovd_showSID(true, true) . '\';">');

        $zData['col_order'] ++; // From 1 - n in stead of 0 - n.
        // Put first part of colid in bold.
        $zData['colid']     = '<A href="' . $_SERVER['PHP_SELF'] . '?action=view&amp;view=' . rawurlencode($zData['colid']) . '" class="data">' . preg_replace('/^([[:alnum:]]+)\/(.*)$/', '<B>$1</B>/$2', $zData['colid']) . '</A>';
        $zData['hgvs']      = '<IMG src="' . ROOT_PATH . 'gfx/mark_' . $zData['hgvs'] . '.png" alt="" width="11" height="11">';
        $zData['form_type'] = lovd_describeFormType($zData);
        $zData['public']    = '<IMG src="' . ROOT_PATH . 'gfx/mark_' . $zData['public'] . '.png" alt="" width="11" height="11">';

        foreach ($aTable as $sField => $aCol) {
            print("\n" . '          <TD' . (!empty($aCol[2])? ' ' . $aCol[2] : '') . (is_numeric($aCol[1])? ' width="' . $aCol[1] . '"' : '') . ($aOrder[0] == $sField? ' class="ordered"' : '') . '>' . (!$zData[$sField]? '-' : $zData[$sField]) . '</TD>');
        }
        print('</TR>');
    }
    print('</TABLE>' . "\n\n");

    // URL pagelink.
    $sPageLink = 'order=' . rawurlencode(implode(',', $aOrder));

    lovd_pagesplitShowNav($sPageLink);

    print('      <FORM action="">' . "\n" .
          '        <INPUT type="button" value="Change column order" onclick="javascript:lovd_openWindow(\'' . $_SERVER['PHP_SELF'] . '?action=order' . lovd_showSID(true, true) . '\', \'ColOrder\', 550, 375);"' . ($nTotal > 1? '' : ' style="color : #999999;" disabled') . '><BR>' . "\n");

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





} elseif ($_GET['action'] == 'view' && !empty($_GET['view'])) {
    // View specific patient column.

    require ROOT_PATH . 'inc-lib-columns.php';
    require ROOT_PATH . 'inc-top.php';
    lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');

    $zData = @mysql_fetch_assoc(mysql_query('SELECT c1.*, c2.*, u_c.name AS created_by_, u_e.name AS edited_by_ FROM ' . TABLE_COLS . ' AS c1 LEFT JOIN ' . TABLE_PATIENTS_COLS . ' AS c2 USING (colid) LEFT OUTER JOIN ' . TABLE_USERS . ' AS u_c ON (c2.created_by = u_c.userid) LEFT OUTER JOIN ' . TABLE_USERS . ' AS u_e ON (c2.edited_by = u_e.userid) WHERE c1.colid = "' . $_GET['view'] . '" AND c1.colid = c2.colid'));
    if (!$zData) {
        // Wrong ID, apparently.
        lovd_showInfoTable('No such ID!', 'stop');
        require ROOT_PATH . 'inc-bot.php';
        exit;
    }

    // Array which will make up the data table.
    $aTable =
             array(
                    'col_order' => 'Column order',
                    'colid' => 'Column ID',
                    'width' => 'Column width (pixels)',
                    'hgvs_' => 'HGVS column',
                    'standard' => 'Standard',
                    'mandatory' => 'Mandatory',
                    'head_column' => 'Column heading',
                    'description_form' => 'Description on form',
                    'description_legend_short' => 'Description on short legend',
                    'description_legend_full' => 'Description on full legend',
                    'mysql_type' => 'Database type',
                    'form_type' => 'Form type',
                    'select_options' => 'Select options',
                    'preg_pattern' => 'Regular expression pattern',
                    'public' => 'Show to public',
                    'public_form' => 'Show on public form',
                    'created_by_' => 'Added by',
                    'created_date' => 'Date added',
                    'edited_by_' => 'Last edited by',
                    'edited_date' => 'Date last edited',
                  );

    // Prepare information for created by column.
    if (!$zData['created_by_']) {
        // LOVD custom column.
        $zData['created_by_'] = 'LOVD';
    }

    // Remove unnecessary columns.
    if ($zData['edited_by_'] == NULL) {
        // Never been edited.
        unset($aTable['edited_by_'], $aTable['edited_date']);
    }

    // Remove columns based on form type?
    $aFormType = explode('|', $zData['form_type']);
    if ($aFormType[1] != 'select') {
        unset($aTable['select_options']);
    } else {
        unset($aTable['preg_pattern']);
    }

    // Table.
    print('      <TABLE border="0" cellpadding="0" cellspacing="1" width="600" class="data">');

    $zData['col_order'] ++; // From 1 - n in stead of 0 - n.
    $zData['hgvs_']          = '<IMG src="' . ROOT_PATH . 'gfx/mark_' . $zData['hgvs'] . '.png" alt="" width="11" height="11">';
    $zData['standard']       = '<IMG src="gfx/mark_' . $zData['standard'] . '.png" alt="" width="11" height="11">';
    $zData['mandatory']      = '<IMG src="gfx/mark_' . $zData['mandatory'] . '.png" alt="" width="11" height="11">';
    $zData['form_type']      = lovd_describeFormType($zData) . '<BR>' . $zData['form_type'];
    $zData['select_options'] = str_replace(array("\r\n", "\r", "\n"), '<BR>', $zData['select_options']);
    $zData['public']         = '<IMG src="gfx/mark_' . $zData['public'] . '.png" alt="" width="11" height="11">';
    $zData['public_form']    = '<IMG src="gfx/mark_' . $zData['public_form'] . '.png" alt="" width="11" height="11">';

    foreach ($aTable as $sField => $sHeader) {
        print("\n" .
              '        <TR>' . "\n" .
              '          <TH valign="top">' . str_replace(' ', '&nbsp;', $sHeader) . '</TH>' . "\n" .
              '          <TD>' . (!$zData[$sField]? '-' : $zData[$sField]) . '</TD></TR>');
    }
    print('</TABLE>' . "\n\n");

    $sNavigation = '';
    // Authorized user (admin or manager) is logged in. Provide tools.
    $sNavigation = '<A href="' . $_SERVER['PHP_SELF'] . '?action=edit&amp;edit=' . rawurlencode($zData['colid']) . '">Edit column display settings</A>';
    $sNavigation .= ' | <A href="' . $_SERVER['PHP_SELF'] . '?action=reset&amp;edit=' . rawurlencode($zData['colid']) . '" onclick="if (!confirm(\'Are you sure you want to reset this column to the current default settings?\')) { return false; } return true;">Reset column to current defaults</A>';
    if (!$zData['hgvs']) {
        $sNavigation .= ' | <A href="' . $_SERVER['PHP_SELF'] . '?action=drop&amp;drop=' . rawurlencode($zData['colid']) . '">Delete column from patient table</A>';
    } else {
        $sNavigation .= ' | <A style="color : #999999;">Delete column from patient table</A>';
    }

    if ($sNavigation) {
        print('      <IMG src="gfx/trans.png" alt="" width="1" height="5"><BR>' . "\n");
        lovd_viewNavigation($sNavigation);
    }

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





} elseif ($_GET['action'] == 'order') {
    // Change column order.

    $qData = mysql_query('SELECT t1.colid, CONCAT(t1.colid, " (", t1.head_column, ")") AS name FROM ' . TABLE_COLS . ' AS t1 LEFT JOIN ' . TABLE_PATIENTS_COLS . ' AS t2 USING (colid) WHERE t1.colid = t2.colid ORDER BY t2.col_order');
    $nData = mysql_num_rows($qData);

    if (!$nData) {
        // Can this actually happen?
        require ROOT_PATH . 'inc-top-clean.php';
        lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');
        lovd_showInfoTable('The system hasn\'t got any custom patient columns!', 'information');
        require ROOT_PATH . 'inc-bot-clean.php';
        exit;
    }

    if (isset($_GET['sent']) && count($_POST['columns']) == $nData) {
        foreach ($_POST['columns'] as $nOrderID => $sColID) {
            // Query text.
            $sQ = 'UPDATE ' . TABLE_PATIENTS_COLS . ' SET col_order = ' . $nOrderID . ' WHERE colid = "' . $sColID . '"';

            $q = mysql_query($sQ);
            if (!$q) {
                require ROOT_PATH . 'inc-top-clean.php';
                lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');
                lovd_dbFout('ColOrder' . str_pad($nOrderID + 1, 2, '0', STR_PAD_LEFT), $sQ, mysql_error());
                require 'inc-bot-clean.php';
                exit;
            }
        }

        // Thank the user...
        require ROOT_PATH . 'inc-top-clean.php';
        lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');
        print('      Columns have successfully been resorted!<BR><BR>' . "\n\n");
        print('      <SCRIPT type="text/javascript">' . "\n" .
              '        <!--' . "\n" .
              '        opener.window.location.href="' . PROTOCOL . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] . '?action=view_all' . lovd_showSID(true, true) . '";' . "\n" .
              '        setTimeout("self.close()", 1000);' . "\n" .
              '        // --> ' . "\n" .
              '      </SCRIPT>' . "\n");

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



    require ROOT_PATH . 'inc-top-clean.php';
    lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');

    $nData = ($nData > 15? 15 : ($nData < 6? 6 : $nData));

    print('      <SCRIPT type="text/javascript" src="inc-js-columnsort.js"></SCRIPT>' . "\n\n");
    print('      <FORM action="' . $_SERVER['PHP_SELF'] . '?action=' . $_GET['action'] . '&amp;sent=true" method="post" onsubmit="javascript:lovd_orderSubmit()">' . "\n" .
          '        <TABLE border="0" cellpadding="0" cellspacing="0">' . "\n" .
          '          <TR>' . "\n" .
          '            <TD valign="top" rowspan="2">' . "\n" .
          '              <SELECT name="columns[]" multiple size="' . $nData . '" id="select_order">' . "\n");

    while ($zData = mysql_fetch_array($qData)) {
        print('            <OPTION value="' . $zData['colid'] . '">' . $zData['name'] . '</OPTION>' . "\n");
    }

    print('              </SELECT></TD>' . "\n" .
          '            <TD valign="top"><A href="#" onclick="lovd_orderHome(); return false;"><IMG src="gfx/order_button_home.png" alt="Top" width="24" height="24" id="butHome" border="0" onmouseover="lovd_imageSwitch(\'butHome\', \'H\');" onmouseout="lovd_imageSwitch(\'butHome\', \'B\');"></A><BR><A href="#" onclick="lovd_orderUp(); return false;"><IMG src="gfx/order_button_up.png" alt="Up" width="24" height="24" id="butUp" border="0" onmouseover="lovd_imageSwitch(\'butUp\', \'H\');" onmouseout="lovd_imageSwitch(\'butUp\', \'B\');"></A></TD></TR>' . "\n" .
          '          <TR>' . "\n" .
          '            <TD valign="bottom"><A href="#" onclick="lovd_orderDown(); return false;"><IMG src="gfx/order_button_down.png" alt="Down" width="24" height="24" id="butDown" border="0" onmouseover="lovd_imageSwitch(\'butDown\', \'H\');" onmouseout="lovd_imageSwitch(\'butDown\', \'B\');"></A><BR><A href="#" onclick="lovd_orderEnd(); return false;"><IMG src="gfx/order_button_end.png" alt="Bottom" width="24" height="24" id="butEnd" border="0" onmouseover="lovd_imageSwitch(\'butEnd\', \'H\');" onmouseout="lovd_imageSwitch(\'butEnd\', \'B\');"></A></TD></TR></TABLE>' . "\n" .
          '        <INPUT TYPE="submit" value="Change column order"><BR>' . "\n" .
          '      </FORM>' . "\n");

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





} elseif ($_GET['action'] == 'add') {
    // Add specific patient column to system.

    if (!empty($_GET['add'])) {
        $zData = @mysql_fetch_assoc(mysql_query('SELECT c1.* FROM ' . TABLE_COLS . ' AS c1 LEFT OUTER JOIN ' . TABLE_PATIENTS_COLS . ' AS c2 USING (colid) WHERE c1.colid = "' . $_GET['add'] . '" AND c2.colid IS NULL AND c1.colid LIKE "Patient/%"'));
        if (!$zData) {
            // Wrong ID, apparently.
            require ROOT_PATH . 'inc-top.php';
            lovd_printHeader('setup_columns_add', 'LOVD Setup - Add column to patients');
            print('      No such ID!<BR>' . "\n");
            require ROOT_PATH . 'inc-bot.php';
            exit;
        }

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

        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()) {
                require ROOT_PATH . 'class/currdb.php';
                $_CURRDB = new CurrDB(false);

                // Gather info on custom column.
                $aColsToCopy = array('colid', 'col_order', 'width', 'mandatory', 'description_form', 'description_legend_short', 'description_legend_full', 'select_options', 'public', 'public_form', 'created_by', 'created_date');

                // $zData comes from the database, and is therefore not quoted.
                lovd_magicQuote($zData);

                // We'll add the column to the end of the list. Max order number?
                list($zData['col_order']) = mysql_fetch_row(mysql_query('SELECT MAX(col_order) FROM ' . TABLE_PATIENTS_COLS));
                $zData['col_order'] ++;

                // Calculate the standard width of the column based on the maximum number of characters.
                $nHeadLength = strlen($zData['head_column']);
                $nColLength = $_CURRDB->getFieldLength($zData['colid']) / 2;
                $nColLength = ($nColLength < $nHeadLength? $nHeadLength : $nColLength);
                // Compensate for small/large fields.
                $nColLength = ($nColLength < 5? 5 : ($nColLength > 35? 35 : $nColLength));
                if ($nColLength < 10) {
                    $zData['width'] = 10*$nColLength;
                } else {
                    $zData['width'] = 8*$nColLength;
                }
                $zData['width'] = ($zData['width'] > 200? 200 : $zData['width']);

                // Created_* columns...
                $zData['created_by'] = $_AUTH['userid'];
                $zData['created_date'] = date('Y-m-d H:i:s');

                $sQ = 'INSERT INTO ' . TABLE_PATIENTS_COLS . ' (';
                $aCol = array();
                foreach ($aColsToCopy as $sCol) {
                    if (isset($zData[$sCol])) {
                        $sQ .= (substr($sQ, -1) == '('? '' : ', ') . $sCol;
                        $aCol[] = $zData[$sCol];
                    }
                }
                $sQ .= ') VALUES (';

                foreach ($aCol as $key => $val) {
                    $sQ .= ($key? ', ' : '') . '"' . $aCol[$key] . '"';
                }
                $sQ .= ')';

                // Insert custom column.
                $q = @mysql_query($sQ);
                if (!$q) {
                    require ROOT_PATH . 'inc-top.php';
                    lovd_printHeader('setup_columns_add', 'LOVD Setup - Add column to patients');
                    lovd_dbFout('ColAdd_A', $sQ, mysql_error());
                }

                // The whole transaction stuff is useless here; alter table will commit and there's just one query before that.

                // Alter patient table.
                // 2009-02-16; 2.0-16; Added stripslashes to allow receiving quotes. This variable has been checked using regexps, so can be considered safe.
                $sQ = 'ALTER TABLE ' . TABLE_PATIENTS . ' ADD COLUMN `' . $zData['colid'] . '` ' . stripslashes($zData['mysql_type']) . ' NOT NULL';
                $q = mysql_query($sQ);
                if (!$q) {
                    // Save the mysql_error before it disappears.
                    $sError = mysql_error();

                    // Rollback;
                    @mysql_query('DELETE FROM ' . TABLE_PATIENTS_COLS . ' WHERE colid = "' . $zData['colid'] . '"');

                    require ROOT_PATH . 'inc-top.php';
                    lovd_printHeader('setup_columns_add', 'LOVD Setup - Add column to patients');
                    lovd_dbFout('ColAdd_B', $sQ, $sError);
                }

                // Write to log...
                lovd_writeLog('MySQL:Event', 'ColAdd', $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ') successfully added column ' . $zData['colid'] . ' (' . mysql_real_escape_string($zData['head_column']) . ') to patient table');

                // Thank the user...
                header('Refresh: 3; url=' . PROTOCOL . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] . '?action=view_all' . lovd_showSID(true));

                require ROOT_PATH . 'inc-top.php';
                lovd_printHeader('setup_columns_add', 'LOVD Setup - Add column to patients');
                print('      Successfully added column "' . $zData['head_column'] . '"!<BR><BR>' . "\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('setup_columns_add', 'LOVD Setup - Add column to patients');

        lovd_errorPrint();

        // Table.
        print('      <FORM action="' . $_SERVER['PHP_SELF'] . '?action=' . $_GET['action'] . '&amp;add=' . rawurlencode($zData['colid']) . '&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('Adding column to patient table', 'print', $zData['colid'] . ' (' . $zData['head_column'] . ')'),
                        'skip',
                        array('Enter your password for authorization', 'password', 'password', 20),
                        array('', 'submit', 'Add column to patient table'),
                      );
        $_MODULES->processForm('SetupColumnsAdd', $aForm);
        lovd_viewForm($aForm);

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

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

    require ROOT_PATH . 'inc-lib-list.php';
    require ROOT_PATH . 'inc-lib-columns.php';
    require ROOT_PATH . 'inc-top.php';
    lovd_printHeader('setup_columns_add', 'LOVD Setup - Add column to patients');

    list($nTotal) = mysql_fetch_row(mysql_query('SELECT COUNT(*) FROM ' . TABLE_COLS . ' AS c1 LEFT OUTER JOIN ' . TABLE_PATIENTS_COLS . ' AS c2 USING (colid) WHERE c2.colid IS NULL AND c1.colid LIKE "Patient/%"'));
    if (!$nTotal) {
        // FIXME; show create column link when functional.
        lovd_showInfoTable('There are no additional custom columns configured in this system.' . ($_AUTH['level'] < LEVEL_MANAGER? ' You need a manager account to create new variant columns.' : ' Need to <A href="setup_columns_global.php?action=create">create a new column</A>?'), 'information');
        require ROOT_PATH . 'inc-bot.php';
        exit;
    }

    lovd_showInfoTable('This is a list of existing LOVD patient columns currently not added to the system.', 'information', 720);

    // Standard query, will be extended later on.
    $sQ = 'SELECT c1.* FROM ' . TABLE_COLS . ' AS c1 LEFT OUTER JOIN ' . TABLE_PATIENTS_COLS . ' AS c2 USING (colid) WHERE c2.colid IS NULL AND c1.colid LIKE "Patient/%"';

    // SORT: Current settings.
    // 2008-08-07; 2.0-10; Implement XSS check on order variable.
    if (isset($_GET['order']) && $_GET['order'] && $_GET['order'] == strip_tags($_GET['order'])) {
        $aOrder = explode(',', $_GET['order']);
    } else {
        $aOrder = array('', '');
    }

    // SORT: Column data.
    $aOrderList = array('colid' => array('c1.colid', 'ASC'), 'head_column' => array('c1.head_column', 'ASC'), 'public' => array('c1.public', 'DESC'));
    if (!array_key_exists($aOrder[0], $aOrderList)) {
        $aOrder[0] = 'colid';
    }
    if ($aOrder[1] != 'ASC' && $aOrder[1] != 'DESC') {
        $aOrder[1] = $aOrderList[$aOrder[0]][1];
    }

    $sQueryLimit = lovd_pagesplitInit($nTotal, 25);
    $sQ .= ' ORDER BY ' . $aOrderList[$aOrder[0]][0] . ' ' . $aOrder[1] . ', c1.colid ' . $sQueryLimit;



    // Show form; required for sorting and searching.
    print('      <FORM action="' . $_SERVER['PHP_SELF'] . '" method="get" style="margin : 0px;">' . "\n" .
          '        <INPUT type="hidden" name="action" value="' . $_GET['action'] . '">' . "\n" .
          '        <INPUT type="hidden" name="order" value="' . implode(',', $aOrder) . '">');
    print('</FORM>' . "\n\n");

    $q = mysql_query($sQ);
    if (!$q) {
        lovd_dbFout('Cols' . ucfirst($_GET['action']), $sQ, mysql_error());
    }

    $n = (isset($nResults)? $nResults : $nTotal);
    print('      <SPAN class="S11">' . $n . ' entr' . ($n == 1? 'y' : 'ies') . '</SPAN><BR>' . "\n");

    // Array which will make up the table (header and data).
    $aTable =
             array(
                    'colid' => array('ID', '*'),
                    'head_column' => array('Column heading', '*'),
                    'form_type' => array('Form type', '*'),
                    'public' => array('Public', '60', 'align="center"'),
                  );

    // Table.
    print('      <TABLE border="0" cellpadding="0" cellspacing="1" width="720" class="data">' . "\n" .
          '        <TR>');

    foreach ($aTable as $sField => $aCol) {
        print("\n" . '          <TH' . (!empty($aCol[2])? ' ' . $aCol[2] : '') . (is_numeric($aCol[1])? ' width="' . $aCol[1] . '"' : '') . (array_key_exists($sField, $aOrderList)? ' class="order' . ($aOrder[0] == $sField? 'ed' : '') . '" onclick="document.forms[0].order.value=\'' . $sField . ',' . ($aOrder[0] == $sField? ($aOrder[1] == 'ASC'? 'DESC' : 'ASC') : $aOrderList[$sField][1]) . '\';document.forms[0].submit();"' : '') . '>' .
              (array_key_exists($sField, $aOrderList)? "\n" .
                                                           '            <TABLE border="0" cellpadding="0" cellspacing="0" width="100%" class="S11">' . "\n" .
                                                           '              <TR>' . "\n" .
                                                           '                <TH>' . str_replace(' ', '&nbsp;', $aCol[0]) . '</TH>' . "\n" .
                                                           '                <TD align="right"><IMG src="gfx/order_arrow_desc' . ($aOrder[0] == $sField && $aOrder[1] == 'DESC'? '_sel' : '') . '.png" alt="Descending" title="Descending" width="13" height="6"><BR><IMG src="gfx/order_arrow_asc' . ($aOrder[0] == $sField && $aOrder[1] == 'ASC'? '_sel' : '') . '.png" alt="Ascending" title="Ascending" width="13" height="6"></TD></TR></TABLE>' : $aCol[0]) . '</TH>');
    }
    print('</TR>');

    while ($zData = mysql_fetch_assoc($q)) {
        print("\n" .
              '        <TR style="cursor : pointer; cursor : hand;" onmouseover="this.className = \'hover\';" onmouseout="this.className = \'\';" onclick="window.location.href = \'' . $_SERVER['PHP_SELF'] . '?action=add&amp;add=' . rawurlencode($zData['colid']) . lovd_showSID(true, true) . '\';">');

        // Put first part of colid in bold.
        $zData['colid']     = '<A href="' . $_SERVER['PHP_SELF'] . '?action=add&amp;add=' . rawurlencode($zData['colid']) . '" class="data">' . preg_replace('/^([[:alnum:]]+)\/(.*)$/', '<B>$1</B>/$2', $zData['colid']) . '</A>';
        $zData['form_type'] = lovd_describeFormType($zData);
        $zData['public']    = '<IMG src="' . ROOT_PATH . 'gfx/mark_' . $zData['public'] . '.png" alt="" width="11" height="11">';

        foreach ($aTable as $sField => $aCol) {
            print("\n" . '          <TD' . (!empty($aCol[2])? ' ' . $aCol[2] : '') . (is_numeric($aCol[1])? ' width="' . $aCol[1] . '"' : '') . ($aOrder[0] == $sField? ' class="ordered"' : '') . '>' . (!$zData[$sField]? '-' : $zData[$sField]) . '</TD>');
        }
        print('</TR>');
    }
    print('</TABLE>' . "\n\n");

    // URL pagelink.
    $sPageLink = 'order=' . rawurlencode(implode(',', $aOrder));

    lovd_pagesplitShowNav($sPageLink);

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





} elseif (($_GET['action'] == 'edit' || $_GET['action'] == 'reset') && !empty($_GET['edit'])) {
    // Edit specific patient column.

    if ($_GET['action'] == 'edit') {
        $zData = @mysql_fetch_assoc(mysql_query('SELECT t1.*, t2.* FROM ' . TABLE_COLS . ' AS t1 LEFT JOIN ' . TABLE_PATIENTS_COLS . ' AS t2 USING (colid) WHERE t2.colid = "' . $_GET['edit'] . '"'));
    } elseif ($_GET['action'] == 'reset') {
        // 2007-12-21; 2.0-02; Added option for selected columns to be reset to the system's default.
        $zData = @mysql_fetch_assoc(mysql_query('SELECT * FROM ' . TABLE_COLS . ' WHERE colid = "' . $_GET['edit'] . '"'));
    }
    
    if (!$zData) {
        // Wrong ID, apparently.
        require ROOT_PATH . 'inc-top.php';
        lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');
        lovd_showInfoTable('No such ID!', 'stop');
        require ROOT_PATH . 'inc-bot.php';
        exit;
    }

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

    if ($_GET['action'] == 'reset') {
        require ROOT_PATH . 'class/currdb.php';
        $_CURRDB = new CurrDB(false);

        // Gather info on custom column.
        $aColsToCopy = array('width', 'mandatory', 'description_form', 'description_legend_short', 'description_legend_full', 'select_options', 'public', 'public_form');

        // $zData comes from the database, and is therefore not quoted.
        lovd_magicQuote($zData);

        // Calculate the standard width of the column based on the maximum number of characters.
        $nHeadLength = strlen($zData['head_column']);
        $nColLength = $_CURRDB->getFieldLength($zData['colid']) / 2;
        $nColLength = ($nColLength < $nHeadLength? $nHeadLength : $nColLength);
        // Compensate for small/large fields.
        $nColLength = ($nColLength < 5? 5 : ($nColLength > 35? 35 : $nColLength));
        if ($nColLength < 10) {
            $zData['width'] = 10*$nColLength;
        } else {
            $zData['width'] = 8*$nColLength;
        }
        $zData['width'] = ($zData['width'] > 200? 200 : $zData['width']);

        // Load data into $_POST.
        foreach ($aColsToCopy as $sCol) {
            $_POST[$sCol] = $zData[$sCol];
        }
        $_CURRDB->transformSelectValues($_POST, true);
        // Make it look like the form has been sent.
        $_GET['sent'] = '';
    }

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

        // Mandatory fields.
        $aCheck =
                 array(
                        'width' => 'Column displayed width in pixels',
                        'description_legend_short' => 'Description on short legend',
                        'description_legend_full' => 'Description on full legend',
                      );

        if (preg_match('/^[^|]*\|select\|.*$/', $zData['form_type'])) {
            $aCheck['select_options'] = 'List of possible options';
            $_POST['select_options'] = trim($_POST['select_options']);
        }

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

        // Numeric field.
        if ($_POST['width'] && !is_numeric($_POST['width'])) {
            lovd_errorAdd('The \'Column displayed width in pixels\' field has to contain a numeric value.');
        }

        // Select_options.
        if (!empty($_POST['select_options'])) {
            $aOptions = explode("\r\n", $_POST['select_options']);
            foreach ($aOptions as $n => $sOption) {
                if (!preg_match('/^([^=]+|[A-Z0-9 \/\()?._+-]+ *= *[^=]+)$/i', $sOption)) {
                    // 2009-02-16; 2.0-16; Added stripslashes().
                    lovd_errorAdd('Select option #' . ($n + 1) . ' &quot;' . htmlspecialchars(stripslashes($sOption)) . '&quot; not understood.');
                }
            }
        }

        if (!lovd_error()) {
            // Query text.
            $sQ = 'UPDATE ' . TABLE_PATIENTS_COLS . ' SET ';

            // Standard fields to be used.
            $aQ = array('width', 'mandatory', 'description_form', 'description_legend_short', 'description_legend_full', 'public', 'public_form');

            // In case the field is a selection field...
            if (preg_match('/^[^|]*\|select\|.*$/', $zData['form_type'])) {
                $aQ[] = 'select_options';
            }

            foreach ($aQ as $key => $val) {
                $sQ .= ($key? ', ' : '') . $val . ' = "' . $_POST[$val] . '"';
            }

            $sQ .= ', edited_by = "' . $_AUTH['userid'] . '", edited_date = NOW() WHERE colid = "' . $zData['colid'] . '"';

            $q = mysql_query($sQ);
            if (!$q) {
                require ROOT_PATH . 'inc-top.php';
                lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');
                lovd_dbFout(($_GET['action'] == 'reset'? 'ColReset' : 'ColEdit'), $sQ, mysql_error());
            }

            // Write to log...
            lovd_writeLog('MySQL:Event', ($_GET['action'] == 'reset'? 'ColReset' : 'ColEdit'), $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ') successfully edited patient column ' . $zData['colid'] . ' (' . mysql_real_escape_string($zData['head_column']) . ')');

            // Thank the user...
            // 2008-02-14; 2.0-04; Why wait? Just directly forward!
            //header('Refresh: 3; url=' . PROTOCOL . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] . '?action=view&view=' . rawurlencode($_GET['edit']) . lovd_showSID(true));
            header('Location: ' . PROTOCOL . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] . '?action=view&view=' . rawurlencode($_GET['edit']) . lovd_showSID(true));

            require ROOT_PATH . 'inc-top.php';
            lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');
            print('      Successfully ' . ($_GET['action'] == 'reset'? 'reset' : 'updated') . ' column \'' . $zData['colid'] . '\'!<BR><BR>' . "\n\n");

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

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

    } else {
        foreach ($zData as $key => $val) {
            if (!isset($_POST[$key]) || !$_POST[$key]) {
                $_POST[$key] = $val;
            }
        }
    }



    require ROOT_PATH . 'inc-top.php';
    lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');

    lovd_errorPrint();

    // Table.
    print('      <FORM action="' . $_SERVER['PHP_SELF'] . '?action=' . $_GET['action'] . '&amp;edit=' . rawurlencode($zData['colid']) . '&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', '', '', '40%', '60%'),
                    array('', 'print', '<B>Column descriptions</B>'),
                    array('Description on form', 'textarea', 'description_form', 40, 2),
                    array('Description on short legend', 'textarea', 'description_legend_short', 40, 2),
                    array('Description on full legend', 'textarea', 'description_legend_full', 40, 2),
                   );

    if (preg_match('/^[^|]*\|select\|.*$/', $zData['form_type'])) {
        $aForm = array_merge($aForm,
             array(
                    array('List of possible options', 'print', '<TEXTAREA name="select_options" cols="70" rows="5" class="S11">' . $_POST['select_options'] . '</TEXTAREA>'),
                    array('', 'print', '<SPAN class="form_note">This is used to build the available options for the selection list.<BR>One option per line.<BR>If you want to use abbreviations, use: Abbreviation = Long name<BR>Example: &quot;DMD = Duchenne Muscular Dystrophy&quot;</SPAN>'),
                   ));
    }

    $aForm = array_merge($aForm,
             array(
                    'skip',
                    array('', 'print', '<B>Column settings</B>'),
                    array('Column displayed width in pixels', 'text', 'width', 5),
                    array('', 'print', '<IMG src="' . ROOT_PATH . '/gfx/trans.png" alt="" width="' . $_POST['width'] . '" height="3" style="background : #000000;"><BR><SPAN class="form_note">(This is ' . $_POST['width'] . ' pixels)</SPAN>'),
                    array('Mandatory field', 'checkbox', 'mandatory', 1),
                    array('Show contents to public', 'checkbox', 'public', 1),
                    array('Show field on public forms', 'checkbox', 'public_form', 1),
                    'skip',
                    array('', 'submit', 'Edit column display settings'),
                  ));
    $_MODULES->processForm('SetupColumnsEdit', $aForm);
    lovd_viewForm($aForm);

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

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





} elseif ($_GET['action'] == 'drop' && !empty($_GET['drop'])) {
    // Drop specific patient column.

    $zData = @mysql_fetch_assoc(mysql_query('SELECT c1.hgvs, c1.head_column, c2.* FROM ' . TABLE_COLS . ' AS c1 LEFT JOIN ' . TABLE_PATIENTS_COLS . ' AS c2 USING (colid) WHERE c1.colid = "' . $_GET['drop'] . '" AND c1.colid = c2.colid'));
    if (!$zData) {
        // Wrong ID, apparently.
        require ROOT_PATH . 'inc-top.php';
        lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');
        lovd_showInfoTable('No such ID!', 'stop');
        require ROOT_PATH . 'inc-bot.php';
        exit;
    }

    if ($zData['hgvs']) {
        // This is a hack-attempt.
        require ROOT_PATH . 'inc-top.php';
        lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');
        lovd_writeLog('MySQL:Error', 'HackAttempt', $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ') tried to remove ' . $zData['colid'] . ' (' . mysql_real_escape_string($zData['head_column']) . ')');
        print('      Hack Attempt.<BR>' . "\n");
        require ROOT_PATH . 'inc-bot.php';
        exit;
    }

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

    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()) {
            // Query text; remove column registration first.
            $sQ = 'DELETE FROM ' . TABLE_PATIENTS_COLS . ' WHERE colid = "' . $zData['colid'] . '"';
            $q = mysql_query($sQ);
            if (!$q) {
                require ROOT_PATH . 'inc-top.php';
                lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');
                lovd_dbFout('ColRemoveA', $sQ, mysql_error());
            }

            // The whole transaction stuff is useless here; alter table will commit and there's just one query before that.

            // Alter patient table.
            $sQ = 'ALTER TABLE ' . TABLE_PATIENTS . ' DROP COLUMN `' . $zData['colid'] . '`';
            $q = mysql_query($sQ);
            if (!$q) {
                require ROOT_PATH . 'inc-top.php';
                lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');
                lovd_dbFout('ColRemoveB', $sQ, mysql_error());
            }

            // Write to log...
            lovd_writeLog('MySQL:Event', 'ColRemove', $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ') successfully removed column ' . $zData['colid'] . ' (' . mysql_real_escape_string($zData['head_column']) . ')');

            // Thank the user...
            header('Refresh: 3; url=' . PROTOCOL . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] . '?action=view_all' . lovd_showSID(true));

            require ROOT_PATH . 'inc-top.php';
            lovd_printHeader('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');
            print('      Successfully removed column "' . $zData['head_column'] . '"!<BR><BR>' . "\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('setup_columns_manage_selected', 'LOVD Setup - Manage selected columns');

    lovd_errorPrint();

    // Table.
    print('      <FORM action="' . $_SERVER['PHP_SELF'] . '?action=' . $_GET['action'] . '&amp;drop=' . rawurlencode($zData['colid']) . '&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 column from patient table', 'print', $zData['colid'] . ' (' . $zData['head_column'] . ')'),
                    'skip',
                    array('Enter your password for authorization', 'password', 'password', 20),
                    array('', 'submit', 'Delete column from patient table'),
                  );
    $_MODULES->processForm('SetupColumnsRemove', $aForm);
    lovd_viewForm($aForm);

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

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





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