<?php
/*******************************************************************************
 *
 * LEIDEN OPEN VARIATION DATABASE (LOVD)
 *
 * Created     : 2007-02-27 (included in LOVD 2008-05-26)
 * Modified    : 2009-07-21
 * For LOVD    : 2.0-21
 *
 * Access      : Curators and up.
 * Purpose     : Download variant data of selected databases in common Central
 *               Repository format.
 *
 * 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';

// 2009-03-10; 2.0-17; WikiProfessional export format.
if (!empty($_GET['format']) && $_GET['format'] == 'wiki') {
    if (!lovd_validateIP($_SETT['wikiprofessional_iprange'], $_SERVER['REMOTE_ADDR']) && !HAS_AUTH) {
        exit;
    }
    // Visitor from the CMBI.RU.NL range or authorized user who found this URL.
    // We will assume the user wants all genes, or all accessible genes at least.

    // We need to know which cols are available to us...
    require ROOT_PATH . 'class/currdb.php';

    // Text output...
    header('Content-type: text/plain; charset=ISO-8859-1');

    // 2009-04-29; 2.0-18; Added increased execution time to script to help export large databases.
    if ((int) ini_get('max_execution_time') < 60) {
        set_time_limit(60);
    }

    $aGenes = array();
    $qGenes = mysql_query('SELECT symbol FROM ' . TABLE_DBS . ' WHERE allow_index_wiki = 1 ORDER BY symbol');
    if (!mysql_num_rows($qGenes)) {
        die('No genes available for indexing.');
    }

    // Gather all columns.
    $aCols = array();
    $aTable =
             array(
                    'Gene/Symbol',
                    'allele_',
                    'pathogenic_',
                  );

    while ($r = mysql_fetch_row($qGenes)) {
        list($sGene) = $r;
        $sSymbol = substr($sGene, 0, strpos($sGene . '_', '_'));

        // Curator and not the owner?
        if (!lovd_validateIP($_SETT['wikiprofessional_iprange'], $_SERVER['REMOTE_ADDR']) && !lovd_isCurator($sGene)) {
            // Skipping, access denied.
            continue;
        }

        // Store for later use.
        $aGenes[$sGene] = $sSymbol;

        // Variant columns.
        $aCols[$sGene] = array();
        $qCols = mysql_query('SELECT colid FROM ' . TABLEPREFIX . '_' . $sGene . '_columns WHERE public = 1 ORDER BY col_order, colid');
        while ($r = mysql_fetch_row($qCols)) {
            if (!in_array($r[0], $aTable)) {
                $aTable[] = $r[0]; // All columns.
            }
            $aCols[$sGene][] = $r[0]; // This gene's columns.
        }
    }

    // Add patient columns.
    $qCols = mysql_query('SELECT colid FROM ' . TABLE_PATIENTS_COLS . ' WHERE public = 1 ORDER BY col_order, colid');
    while ($r = mysql_fetch_row($qCols)) {
        if (!in_array($r[0], $aTable)) {
            $aTable[] = $r[0];
        }
    }

    // Additional columns.
    $aTable = array_merge($aTable,
             array(
                    'status_',
                    'submitter_',
                    'variant_created_by_',
                    'variant_created_date_',
                    'variant_edited_by_',
                    'variant_edited_date_',
                    'patient_created_by_',
                    'patient_created_date_',
                    'patient_edited_by_',
                    'patient_edited_date_',
                    'ID_variantid_',
                    'ID_patientid_',
                    'ID_allele_',
                    'ID_pathogenic_',
                    'ID_status_',
                    'ID_submitterid_',
                    'URL',
                  ));

    // Print header information.
    $i = 0;
    foreach ($aTable as $sField) {
        print(($i? "\t" : '') . '"{{ ' . $sField . ' }}"');
        $i ++;
    }

    foreach ($aGenes as $sGene => $sSymbol) {
        $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_, 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 ' . TABLEPREFIX . '_' . $sGene . '_variants AS v LEFT JOIN ' . TABLE_PAT2VAR . ' AS p2v ON (p2v.symbol = "' . $sGene . '" AND v.variantid = p2v.variantid) LEFT JOIN ' . TABLE_PATIENTS . ' AS p USING (patientid) LEFT OUTER JOIN ' . TABLE_VAR_STATUS . ' AS stat ON (p2v.status = stat.status) LEFT OUTER JOIN ' . TABLE_PATHOGENIC . ' AS path ON (p2v.pathogenic = path.pathogenic) 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 p2v.status >= ' . STATUS_MARKED . ' GROUP BY v.variantid, p.patientid, p2v.allele';
        // 2009-07-21; 2.0-21; Do not sort on Variant/Exon anymore; sort column contains Exon information.
        $sQ .= ' ORDER BY v.sort, v.`Variant/DNA`, p.patientid';

        $q = mysql_query($sQ);
        if (!$q) {
            lovd_dbFout('WikiExport', $sQ, mysql_error());
        }

        while ($zData = mysql_fetch_assoc($q)) {
            print("\r\n");
            $zData['Gene/Symbol'] = $sSymbol;
            $zData['allele_'] = $_SETT['var_allele'][$zData['allele']];
            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_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['URL'] = $_CONF['location_url'] . 'variants.php?select_db=' . $sGene . '&action=view&view=' . $zData['patientid'] . '%2C' . $zData['variantid'] . '%2C' . $zData['allele'];

            // Quote data.
            lovd_magicQuote($zData);

            $i = 0;
            foreach ($aTable as $sField) {
                // Should we be seeing this?
                if (substr($sField, 0, 8) == 'Variant/' && !in_array($sField, $aCols[$sGene])) {
                    $zData[$sField] = '';
                }

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





// Require curator clearance.
lovd_requireAUTH(LEVEL_CURATOR);

if (isset($_GET['all_genes'])) {
    // Just so that it will start downloading if all other options are specified.
    $_GET['genes'] = array(1);
}

// First show the available genes.
if (empty($_GET['genes']) || !is_array($_GET['genes']) || !isset($_GET['filter_status']) || !isset($_GET['filter_unsure']) || !isset($_GET['display'])) {
    require ROOT_PATH . 'inc-lib-form.php';
    require ROOT_PATH . 'inc-top.php';
    lovd_printHeader('export_data', 'LOVD - Export variant data');

    if (empty($_GET['genes']) || !is_array($_GET['genes'])) {
        $_GET['genes'] = array();
    }

    // Fetch genes.
    $qGenes = mysql_query('SELECT symbol, gene FROM ' . TABLE_DBS . ' ORDER BY symbol');
    $nGenes = mysql_num_rows($qGenes);
    $aGenes = array();
    while ($zGenes = mysql_fetch_assoc($qGenes)) {
        // 2008-07-22; 2.0-09; Curators only see the genes they have access to.
        if ($_AUTH['level'] == LEVEL_CURATOR && !lovd_isCurator($zGenes['symbol'])) {
            $nGenes --;
            continue;
        }

        if (isset($_GET['all_genes'])) {
            $_GET['genes'][] = $zGenes['symbol'];
        }
        $aGenes[$zGenes['symbol']] = $zGenes['symbol'] . ' (' . lovd_shortenString($zGenes['gene'], 50) . ')';
    }

    $aFilterStatus = array(STATUS_MARKED => 'Only public variants', STATUS_NEW => 'All variants');
    $aFilterUnsure = array('Don\'t filter unsure variants', 'Filter unsure variants', 'Show unsure variants only');
    $aDisplay      = array('screen' => 'Display directly on screen', 'dl' => 'Download as separate file');

    // Table.
    print('      <FORM action="' . $_SERVER['PHP_SELF'] . '" method="get">' . "\n" .
          '        <TABLE border="0" cellpadding="0" cellspacing="1" width="950">');

    // Array which will make up the form table.
    $aForm = array(
                    array('GET', '', '', '40%', '60%'),
                    array('<B>Select the gene(s) to download variant data from</B>', 'select', 'genes', ($nGenes > 5? 5 : $nGenes), $aGenes, false, true, true),
                    'skip',
                    array('', 'print', '<B>Filter options</B>'),
                    array('On status', 'select', 'filter_status', 1, $aFilterStatus, false, false, false),
                    array('Unsure variants', 'select', 'filter_unsure', 1, $aFilterUnsure, false, false, false),
                    array('', 'print', '<SPAN class="form_note">Unsure in this case means variants where the DNA field contains a questionmark at the beginning or the end of the variant field, i.e. "c.?34C>G" or "c.34C>G?", or variants without a proper DB-ID value.</SPAN>'),
                    'skip',
                    array('Retrieve results', 'select', 'display', 1, $aDisplay, false, false, false),
                    'skip',
                    array('', 'submit', 'Download variant data'),
                  );
    $_MODULES->processForm('ExportDataChooseGenes', $aForm);
    lovd_viewForm($aForm);

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





// We need to know which cols are available to us...
require ROOT_PATH . 'class/currdb.php';

// Text output...
header('Content-type: text/plain; charset=ISO-8859-1');
if ($_GET['display'] == 'dl') {
    // Download to external file...
    header('Content-Disposition: attachment; filename="LOVD_export_' . date('Y-m-d_H.i.s') . '.txt"');
    // 2008-08-27; 2.0-11; Fix IE download issue when using SSL.
    header('Pragma: public');
}
print("Gene\tDNA\tDBID\tDbSNP\tOMIM\n");

$qGenes = mysql_query('SELECT symbol FROM ' . TABLE_DBS . (isset($_GET['all_genes'])? '' : ' WHERE symbol in ("' . implode($_GET['genes'], '", "') . '")') . ' ORDER BY symbol');
if (!mysql_num_rows($qGenes)) {
    die('No genes found!');
}

$aPrev  = array('DNA' => '', 'DBID' => '', 'dbsnp' => '', 'omim' => '');
while ($r = mysql_fetch_row($qGenes)) {
    list($sGene) = $r;
    $sSymb = substr($sGene, 0, strpos($sGene . '_', '_'));

    if (!lovd_isCurator($sGene)) {
        print('Access denied to ' . $sGene . ' database!' . "\n");
        continue;
    }

    // We're just going to assume that Variant/DNA and Variant/DBID are here,
    // because they're HGVS recommended and therefore they're hard to remove.
    // But to order on Variant/Exon and to read out Variant/DNA_remark or
    // Variant/Remarks, we do need a CURRDB set.
    $CurrDB = new CurrDB(true, $sGene);
    $sQ = 'SELECT DISTINCT v.`Variant/DNA` AS DNA, v.`Variant/DBID` AS DBID';

    // Retrieve more cols?
    if ($CurrDB->colExists('Variant/Remarks')) {
        $sQ .= ', v.`Variant/Remarks` AS Remarks';
    } elseif ($CurrDB->colExists('Variant/DNA_remark')) {
        // FIXME; This is actually DMD specific, as only some of the Leiden databases use Variant/DNA_remark.
        $sQ .= ', v.`Variant/DNA_remark` AS Remarks';
    } else {
        $sQ .= ', "" AS Remarks';
    }

    if ($_GET['filter_status'] > 1) {
        // 2009-03-10; 2.0-17; We of course need to join to records from the *same gene* in PAT2VAR!!! Added p2v.symbol = "' . $sGene . '" check.
        $sQ .= ' FROM ' . TABLEPREFIX . '_' . $sGene . '_variants AS v LEFT JOIN ' . TABLEPREFIX . '_patients2variants AS p2v ON (p2v.symbol = "' . $sGene . '" AND v.variantid = p2v.variantid) WHERE p2v.status >= "' . $_GET['filter_status'] . '"';
    } else {
        $sQ .= ' FROM ' . TABLEPREFIX . '_' . $sGene . '_variants AS v WHERE 1=1';
    }

    // Filter unsure variants?
    if ($_GET['filter_unsure'] == 1) {
        $sQ .= ' AND v.`Variant/DNA` NOT LIKE "%?" AND v.`Variant/DNA` NOT LIKE "_.(%?)" AND v.`Variant/DNA` NOT LIKE "?%" AND v.`Variant/DBID` NOT LIKE "' . $sSymb . '_00000%" AND v.`Variant/DBID` != ""';
    } elseif ($_GET['filter_unsure'] == 2) {
        $sQ .= ' AND (v.`Variant/DNA` LIKE "%?" OR v.`Variant/DNA` LIKE "_.(%?)" OR v.`Variant/DNA` LIKE "?%" OR v.`Variant/DBID` LIKE "' . $sSymb . '_00000%" OR v.`Variant/DBID` = "")';
    }    

    // 2009-07-21; 2.0-21; Do not sort on Variant/Exon anymore; sort column contains Exon information.
    $sQ .= ' ORDER BY v.sort, v.`Variant/DNA`, v.`Variant/DBID`';
    $qVariants = mysql_query($sQ);

    while ($zData = mysql_fetch_assoc($qVariants)) {
        // Isolate IDs.
        if (substr_count($zData['DBID'], '{dbSNP')) {
            $zData['dbsnp'] = preg_replace('/^.*(\{dbSNP[0-9]+\}).*$/', "$1", $zData['DBID']);
        } elseif (substr_count($zData['Remarks'], '{dbSNP')) {
            $zData['dbsnp'] = preg_replace('/^.*(\{dbSNP[0-9]+\}).*$/', "$1", $zData['Remarks']);
        } else {
            $zData['dbsnp'] = '';
        }
        if (substr_count($zData['DBID'], '{OMIM')) {
            $zData['omim'] = preg_replace('/^.*(\{OMIM[0-9]+:[0-9]+\}).*$/', "$1", $zData['DBID']);
        } elseif (substr_count($zData['Remarks'], '{OMIM')) {
            $zData['omim'] = preg_replace('/^.*(\{OMIM[0-9]+:[0-9]+\}).*$/', "$1", $zData['Remarks']);
        } else {
            $zData['omim'] = '';
        }
        unset($zData['Remarks']); // So we can compare $zData vars.
        $zData['DBID'] = preg_replace('/^.*(' . $sSymb . '_([0-9]{5}|(SO|MP|e)[0-9]{1,2}((SO|MP|e)[0-9]{1,2})?b?)).*$/', "$1", $zData['DBID']);

        if ($zData !== $aPrev) {
            print("$sGene\t{$zData['DNA']}\t{$zData['DBID']}\t{$zData['dbsnp']}\t{$zData['omim']}\n");
        }
        $aPrev = $zData;
    }
}
?>