<?php
/*******************************************************************************
 *
 * LEIDEN OPEN VARIATION DATABASE (LOVD)
 *
 * Created     : 2007-03-23
 * Modified    : 2010-07-29
 * For LOVD    : 2.0-28
 *
 * Access      : Public
 * Purpose     : Download (selection of) selected gene's variants.
 *
 * Copyright   : 2004-2010 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';
}

// If no gene selected, forward to the select list.
if (!$_SESSION['currdb']) {
    header('Location: ' . PROTOCOL . $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/') . '/home.php' . lovd_showSID());
    exit;
}

// Whether or not a user can see non-public data.
if (lovd_isCurator($_SESSION['currdb'])) {
    define('IS_CURATOR', true);
} else {
    define('IS_CURATOR', false);
}

if (!$_SETT['currdb']['allow_download'] && !IS_CURATOR) {
    // Require manager clearance.
    lovd_requireAUTH(LEVEL_MANAGER);
}





if (in_array($_GET['action'], array('view_all', 'search_all', 'view_unique', 'search_unique'))) {
    // View all / search variants in this gene.

    require ROOT_PATH . 'inc-lib-list.php';
    require ROOT_PATH . 'class/currdb.php';
    $_CURRDB = new CurrDB();
    $_CURRDB->hideCols('public', IS_CURATOR);
    $_CURRDB->hideColsByTable((substr($_GET['action'], -6) == 'unique'? 'Variant' : ''));

    // 2007-03-20; 2.0-alpha-09; Isn't *not* joining to TABLE_PATIENTS much faster? Joining prevents orphaned entries to show up.
    //list($nTotal) = mysql_fetch_row(mysql_query('SELECT COUNT(*) FROM ' . TABLE_PAT2VAR . ' AS t1 LEFT JOIN ' . TABLE_PATIENTS . ' AS t2 USING (patientid) WHERE t1.symbol = "' . $_SESSION['currdb'] . '" AND ' . (IS_CURATOR? '1=1' : 't1.status >= ' . STATUS_MARKED)));
    list($nTotal) = mysql_fetch_row(mysql_query('SELECT COUNT(*) FROM ' . TABLE_PAT2VAR . ' AS t1 WHERE t1.symbol = "' . $_SESSION['currdb'] . '" AND ' . (IS_CURATOR? '1=1' : 't1.status >= ' . STATUS_MARKED)));
    if (!$nTotal && !IS_CURATOR) {
        // Curators just get the column headers; no matter if there are no variants.
        require ROOT_PATH . 'inc-top.php';
        lovd_printHeader('variant_listings', 'LOVD - Variant listings for ' . $_SESSION['currdb']);
        print('      There are currently no ' . (IS_CURATOR? '' : 'public ') . 'variants in this gene.<BR>' . "\n");
        require ROOT_PATH . 'inc-bot.php';
        exit;
    }

    // Standard query, will be extended later on. THESE QUERIES ARE SLIGHTLY DIFFERENT FROM THE VARIANTS LIST.
    if (substr($_GET['action'], -6) == 'unique') {
        // 2010-07-20; 2.0-28; Fixed bug; unpublic variants should never be downloaded if $_CONF['hide_nonpublic'] is turned on!!!
        // View unique. View all variants, exclude non public entries depending on $_CONF['hide_nonpublic'].
        $sQ = 'SELECT v.* FROM ' . TABLE_CURRDB_VARS . ' AS v LEFT JOIN ' . TABLE_PAT2VAR . ' AS p2v ON (p2v.symbol = "' . $_SESSION['currdb'] . '" AND v.variantid = p2v.variantid) WHERE ' . (!IS_CURATOR && $_CONF['hide_nonpublic']? 'p2v.status >= ' . STATUS_MARKED : '1=1');
    } else {
        // View all.
        $sQ = 'SELECT v.*, p.*, p2v.allele, p2v.status, p2v.created_by AS ID_variant_created_by_, p2v.created_date AS variant_created_date_, p2v.edited_by AS ID_variant_edited_by_, p2v.edited_date AS variant_edited_date_' . (IS_CURATOR? ', stat.status_text AS status_' : '') . ', path.pathogenic, path.pathogenic_text AS pathogenic_, uvc.name AS variant_created_by_, uve.name AS variant_edited_by_, upc.name AS patient_created_by_, upe.name AS patient_edited_by_, CONCAT(s.firstname, " ", s.lastname) AS submitter_ FROM ' . TABLE_CURRDB_VARS . ' AS v LEFT JOIN ' . TABLE_PAT2VAR . ' AS p2v ON (p2v.symbol = "' . $_SESSION['currdb'] . '" AND v.variantid = p2v.variantid) LEFT JOIN ' . TABLE_PATIENTS . ' AS p USING (patientid)' . (IS_CURATOR? ' 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) LEFT OUTER JOIN ' . TABLE_USERS . ' AS uvc ON (p2v.created_by = uvc.userid) LEFT OUTER JOIN ' . TABLE_USERS . ' AS uve ON (p2v.edited_by = uve.userid) LEFT OUTER JOIN ' . TABLE_USERS . ' AS upc ON (p.created_by = upc.userid) LEFT OUTER JOIN ' . TABLE_USERS . ' AS upe ON (p.edited_by = upe.userid) LEFT OUTER JOIN ' . TABLE_SUBS . ' AS s ON (p.submitterid = s.submitterid) WHERE ' . (IS_CURATOR? '1=1' : 'p2v.status >= ' . STATUS_MARKED);
    }

    // Guess the column used to describe the mutation.
    $sMutationCol = $_CURRDB->getMutationCol();

    if (substr($_GET['action'], 0, 6) == 'search') {
        // Here starts the actual building of the query.
        // SEARCH: Exception for $_GET['search_MutCol'].
        if (!empty($_GET['search_MutCol'])) {
            if (!empty($_GET['search_' . $sMutationCol])) {
                $_GET['search_' . $sMutationCol] .= ' ' . $_GET['search_MutCol'];
            } else {
                $_GET['search_' . $sMutationCol] = $_GET['search_MutCol'];
            }
        }

        // SEARCH: Simple text search.
        $aSearchText = array_merge(
                 array('search_pathogenic_' => 'path.pathogenic_text'),
                 $_CURRDB->buildSearchList());
        if (IS_CURATOR) {
            // Add status.
            // 2010-03-11; Fixed bug; When downloading search results, search terms in the SubmitterID field were ignored.
            $aSearchText['search_submitterid'] = 'p.submitterid';
            $aSearchText['search_status_'] = 'stat.status_text';
        }
        // FIXME; document advanced searching.
        foreach ($aSearchText as $key => $val) {
            if (isset($_GET[$key]) && trim($_GET[$key])) {
                // 2010; 2.0-28; Allow for searches where the order of words is forced by enclosing the values with double quotes; ["Fokkema et al 2005"] will only
                // return hits with these words in exactly this order while [Fokkema et al 2005] will return hits with all of these words, in whatever order.
                $sSearch = preg_replace_callback('/\\\"([^"]+)\\\"/', create_function('$aRegs', 'return str_replace(\' \', \'{{SPACE}}\', $aRegs[1]);'), trim($_GET[$key]));
                $a = explode(' ', $sSearch);
                foreach ($a as $sTerm) {
                    if ($sTerm) {
                        if (substr_count($sTerm, '|') && preg_match('/^[^|]+(\|[^|]+)+$/', $sTerm)) {
                            // OR.
                            $aOR = explode('|', $sTerm);
                            $sQ .= ' AND (';
                            foreach ($aOR as $nTerm => $sTerm) {
                                // 2009-03-03; 2.0-17; Advanced searching did not allow to combine NOT and OR searches.
                                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) . '%"';
                        }
                    }
                }
            }
        }
    }

    if (substr($_GET['action'], -6) == 'unique') {
        $sQ .= ' GROUP BY v.`' . $sMutationCol . '`';
    } else {
        // Add p2v.allele here to show homozygous mutations twice in the list.
        // 2008-02-27; 2.0-04; OF COURSE WE WANT TO SHOW HOMOZYGOUS MUTATIONS TWICE!!! @#%$*!!! THIS IS A DOWNLOAD!!!
        $sQ .= ' GROUP BY v.variantid, p.patientid, p2v.allele';
    }

    // 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_merge(
             array('pathogenic_' => array('path.pathogenic', 'ASC')),
             array('sort' => array('v.sort', 'ASC')),
             $_CURRDB->buildOrderList());
    if (substr($_GET['action'], -3) == 'all' && IS_CURATOR) {
        // Add status.
        $aOrderList['status_'] = array('p2v.status', 'ASC');
    }
    if (!array_key_exists($aOrder[0], $aOrderList)) {
        // 2008-08-07; 2.0-10; Applied 2.0-beta-10 change of variants.php to download.php as well: Changed default sorting column from Exon to Variant/DNA.
        $aOrder[0] = $sMutationCol;
    }
    if ($aOrder[1] != 'ASC' && $aOrder[1] != 'DESC') {
        $aOrder[1] = $aOrderList[$aOrder[0]][1];
    }

    // 2008-08-07; 2.0-10; Applied 2.0-beta-10 change of variants.php to download.php as well: Fixed bug #18 - Variants do not sort correctly when selecting DNA column to sort on.
    // 2009-07-21; 2.0-21; Changed again, since the sort column now also contains the Exon information.
    // SORT: Additional sort columns (faking a perfect sort).
    $sOrderPre = '';
    $sOrderPost = '';
    if ($aOrder[0] == 'Variant/DNA') {
        // 1) In case of the mutation column, sort FIRST on sort.
        $sOrderPre .= 'v.sort ' . $aOrder[1] . ', ';
    } else {
        // 2) In case of anything else, sort on sort AFTER.
        $sOrderPost .= ', v.sort ' . $aOrder[1];
    }

    $sQ .= ' ORDER BY ' . $sOrderPre . $aOrderList[$aOrder[0]][0] . ' ' . $aOrder[1] . $sOrderPost . (substr($_GET['action'], -6) == 'unique'? '' : ', p.patientid');



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

    $nResults = mysql_num_rows($q);
    if (!$nResults && !IS_CURATOR) {
        // Searched, but no results (Curators just get the column headers; no matter if there are no results).
        require ROOT_PATH . 'inc-top.php';
        lovd_printHeader('variant_listings', 'LOVD - Variant listings for ' . $_SESSION['currdb']);
        print('      No results have been found that match your criteria.<BR>' . "\n\n");
        require ROOT_PATH . 'inc-bot.php';
        exit;
    }

    // Array which will make up the table (header and data).
    // 2008-03-11; 2.0-05; Removed some columns because they appear to confuse people. Only the necessary ID columns are included.
    $aTable = array();
    /*
    if (substr($_GET['action'], -3) == 'all') {
        $aTable =
                 array(
                        'allele_' => array(),
                        'pathogenic_' => array('Path.', 50),
                      );
    } else {
        $aTable = array();
    }
    */
    $aTable = array_merge($aTable, $_CURRDB->buildTable('list'));

    // Additional columns for authorized users.
    if (substr($_GET['action'], -3) == 'all' && IS_CURATOR) {
        // 2008-03-11; 2.0-05; Removed some columns because they appear to confuse people. Only the necessary ID columns are included.
        $aTable = array_merge($aTable,
                 array(
                        'ID_variantid_' => array(),
                        'ID_patientid_' => array(),
                        'ID_allele_' => array(),
                        'ID_pathogenic_' => array(),
                        'ID_status_' => array(),
                        'ID_sort_' => array(),
                        'ID_submitterid_' => array(),
                        'ID_variant_created_by_' => array(),
                        'variant_created_date_' => array(),
                        'ID_variant_edited_by_' => array(),
                        'variant_edited_date_' => array(),
                        'ID_patient_created_by_' => array(),
                        'patient_created_date_' => array(),
                        'ID_patient_edited_by_' => array(),
                        'patient_edited_date_' => array(),
                      ));
        /*
        $aTable = array_merge($aTable,
                 array(
                        'status_' => array('Status', 75),
                        'submitter_' => array(),
                        'variant_created_by_' => array(),
                        'variant_created_date_' => array(),
                        'variant_edited_by_' => array(),
                        'variant_edited_date_' => array(),
                        'patient_created_by_' => array(),
                        'patient_created_date_' => array(),
                        'patient_edited_by_' => array(),
                        'patient_edited_date_' => array(),
                        'ID_sort_' => array(),
                        'ID_allele_' => array(),
                        'ID_pathogenic_' => array(),
                        'ID_status_' => array(),
                        'ID_variantid_' => array(),
                        'ID_patientid_' => array(),
                        'ID_submitterid_' => array(),
                        'ID_variant_created_by_' => array(),
                        'ID_variant_edited_by_' => array(),
                        'ID_patient_created_by_' => array(),
                        'ID_patient_edited_by_' => array(),
                      ));
        */
    }



    // Headers.
    header('Content-Type: text/plain; charset=ISO-8859-1');
    header('Content-Disposition: attachment; filename="LOVD_' . $_SESSION['currdb'] . '_' . date('Y-m-d_H.i.s') . '.txt"');
    // 2008-08-27; 2.0-11; Fix IE download issue when using SSL.
    header('Pragma: public');
    // 2008-02-29; 2.0-04; Add format version number.
    // 2009-01-27; Add datatype information.
    print('### LOVD-version ' . lovd_calculateVersion($_SETT['system']['version']) . ' ### Variants' . (substr($_GET['action'], -6) == 'unique'? '' : '/Patients') . ' ### Do not remove this line, unless importing in versions before 2.0-04 ###' . "\n");

    $i = 0;
    foreach ($aTable as $sField => $aCol) {
        print(($i? "\t" : '') . '"{{ ' . $sField . ' }}"');
        $i ++;
    }

    while ($zData = mysql_fetch_assoc($q)) {
        print("\n");
        // New data.
        if (substr($_GET['action'], -3) == 'all') {
            $zData['allele_'] = $_SETT['var_allele'][$zData['allele']];
            if (IS_CURATOR) {
                // For curators only, because if you're not, you lack non-public data so what's the use of importing it back in...
                if (!$zData['variant_created_by_']) {
                    $zData['variant_created_by_'] = $zData['submitter_'];
                }
                if (!$zData['variant_edited_by_'] && $zData['variant_edited_date_']) {
                    $zData['variant_edited_by_'] = $zData['submitter_'];
                }
                $zData['patient_created_date_'] = $zData['created_date'];
                $zData['patient_edited_date_'] = $zData['edited_date'];
                if (!$zData['patient_created_by_']) {
                    $zData['patient_created_by_'] = $zData['submitter_'];
                }
                if (!$zData['patient_edited_by_'] && $zData['patient_edited_date_']) {
                    $zData['patient_edited_by_'] = $zData['submitter_'];
                }
                $zData['ID_sort_'] = $zData['sort'];
                $zData['ID_allele_'] = $zData['allele'];
                $zData['ID_pathogenic_'] = $zData['pathogenic'];
                $zData['ID_status_'] = $zData['status'];
                $zData['ID_variantid_'] = $zData['variantid'];
                $zData['ID_patientid_'] = $zData['patientid'];
                $zData['ID_submitterid_'] = $zData['submitterid'];
                $zData['ID_patient_created_by_'] = $zData['created_by'];
                $zData['ID_patient_edited_by_'] = $zData['edited_by'];
            }
        }
        
        // Quote data.
        lovd_magicQuote($zData);

        $i = 0;
        foreach ($aTable as $sField => $aCol) {
            // Replace line endings.
            $zData[$sField] = str_replace(array("\r\n", "\r", "\n"), array('\r\n', '\r', '\n'), $zData[$sField]);
            print(($i? "\t" : '') . '"' . $zData[$sField] . '"');
            $i ++;
        }
    }
    exit;





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