<?php
/*******************************************************************************
 *
 * LEIDEN OPEN VARIATION DATABASE (LOVD)
 *
 * Created     : 2006-11-01
 * Modified    : 2011-09-30
 * For LOVD    : 2.0-33
 *
 * Access      : Public
 * Purpose     : Generate overview of database content. Summary per gene
 *               and total numbers.
 *
 * Copyright   : 2004-2011 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';
}

// Provide gene homepage.
require ROOT_PATH . 'inc-top.php';
lovd_printHeader('status', 'LOVD - Current system status');

print('      ' . date('Y/m/d H:i:s T \- l, F jS Y') . '<BR><BR>' . "\n\n");

$nTotal = 0;
$nUnique = 0;
$q = mysql_query('SELECT symbol, gene FROM ' . TABLE_DBS . ' ORDER BY symbol');
$nGenes = mysql_num_rows($q);



// 2008-04-18; 2.0-06; Take Patient/Times_reported into account.
// To check availability of the Variant/DNA and Patient/Times_reported columns, we need the CurrDB class.
require ROOT_PATH . 'class/currdb.php';
$aCURRDBs = array();

// Table.
print('      <TABLE border="0" cellpadding="0" cellspacing="1" width="950" class="data" style="font-size : 13px;">' . "\n" .
      '        <TR>' . "\n" .
      '          <TH style="background : #8CAFF0;">Gene symbol</TH>' . "\n" .
      '          <TH>Gene name</TH>' . "\n" .
      '          <TH align="right">Total variants</TH>' . "\n" .
      '          <TH align="right">Unique variants</TH>' . "\n" .
      '          <TH align="right">Patients</TH></TR>');

// Loop per gene; curated entries only.
while ($zData = mysql_fetch_assoc($q)) {
    $aCURRDBs[$zData['symbol']] = new CurrDB(true, $zData['symbol']);
    $_CURRDB = & $aCURRDBs[$zData['symbol']];
    $sMutationCol = $_CURRDB->getMutationCol();

    // Number of unique variants.
    if (!$sMutationCol) {
        $nUniqueVariants = '??';
    } else {
        list($nUniqueVariants) = mysql_fetch_row(mysql_query('SELECT COUNT(DISTINCT v.`' . $sMutationCol . '`) FROM ' . TABLE_PAT2VAR . ' AS p2v LEFT JOIN ' . TABLEPREFIX . '_' . $zData['symbol'] . '_variants AS v USING (variantid) WHERE p2v.symbol = "' . $zData['symbol'] . '" AND p2v.status >= ' . STATUS_MARKED . ' AND `' . $sMutationCol . '` NOT IN ("c.=", "c.0")'));
        $nUnique += $nUniqueVariants;
    }

    if ($_CURRDB->colExists('Patient/Times_Reported')) {
        // Total number of variants found in this gene.
        $sQ = 'SELECT SUM(p.`Patient/Times_Reported`) FROM ' . TABLE_PATIENTS . ' AS p LEFT JOIN ' . TABLE_PAT2VAR . ' AS p2v USING (patientid)';
    } else {
        // Total number of variants found in this gene.
        $sQ = 'SELECT COUNT(*) FROM ' . TABLE_PAT2VAR . ' AS p2v';
    }
    $sQ .= ' LEFT JOIN ' . TABLEPREFIX . '_' . $zData['symbol'] . '_variants AS v USING (variantid) WHERE p2v.symbol = "' . $zData['symbol'] . '" AND p2v.status >= ' . STATUS_MARKED . ' AND `' . $sMutationCol . '` NOT IN ("c.=", "c.0")';
    list($nTotalVariants) = mysql_fetch_row(mysql_query($sQ));
    settype($nTotalVariants, 'int'); // Convert NULL to 0.
    $nTotal += $nTotalVariants;

    // 2011-09-30; 2.0-33; #50; Display patient number on Status page.
    // Number of individuals with variant(s) reported in this gene.
    if ($_CURRDB->colExists('Patient/Times_Reported')) {
        // Backwards compatible with 4.0 and earlier.
        $nPatients = 0;
        $qPatients = mysql_query('SELECT p.`Patient/Times_Reported` FROM ' . TABLE_PATIENTS . ' AS p INNER JOIN ' . TABLE_PAT2VAR . ' AS p2v USING (patientid) WHERE p2v.symbol = "' . $zData['symbol'] . '" AND p2v.status >= ' . STATUS_MARKED . ' GROUP BY p.patientid');
        while ($r = mysql_fetch_row($qPatients)) {
            $nPatients += $r[0];
        }
    } else {
        list($nPatients) = mysql_fetch_row(mysql_query('SELECT COUNT(DISTINCT patientid) FROM ' . TABLE_PAT2VAR . ' WHERE symbol = "' . $zData['symbol'] . '" AND status >= ' . STATUS_MARKED));
    }

    print("\n" .
          '        <TR style="cursor : pointer;" onmouseover="this.className = \'hover\';" onmouseout="this.className = \'\';" onclick="window.location.href = \'' . ROOT_PATH . 'home.php?select_db=' . $zData['symbol'] . lovd_showSID(true, true) . '\';">' . "\n" .
          '          <TD class="ordered"><A href="' . ROOT_PATH . 'home.php?select_db=' . $zData['symbol'] . '" class="data"><B>' . $zData['symbol'] . '</B></A></TD>' . "\n" .
          '          <TD>' . $zData['gene'] . '</TD>' . "\n" .
          '          <TD align="right">' . $nTotalVariants . '</TD>' . "\n" .
          '          <TD align="right">' . $nUniqueVariants . '</TD>' . "\n" .
          '          <TD align="right">' . $nPatients . '</TD></TR>');
}

// Total number of (public!!!) patients in this database.
if ($_CURRDB->colExists('Patient/Times_Reported')) {
    $nTotalPatients = 0;
    $qTotalPatients = mysql_query('SELECT p.`Patient/Times_Reported` FROM ' . TABLE_PATIENTS . ' AS p INNER JOIN ' . TABLE_PAT2VAR . ' AS p2v USING (patientid) WHERE p2v.status >= ' . STATUS_MARKED . ' GROUP BY p.patientid');
    while ($r = mysql_fetch_row($qTotalPatients)) {
        $nTotalPatients += $r[0];
    }
} else {
    list($nTotalPatients) = mysql_fetch_row(mysql_query('SELECT COUNT(DISTINCT patientid) FROM ' . TABLE_PAT2VAR . ' WHERE status >= ' . STATUS_MARKED));
}

// Total cumulative numbers.
print("\n" .
      '        <TR align="right">' . "\n" .
      '          <TD style="background : #8CAFF0;">&nbsp;</TD>' . "\n" .
      '          <TD style="background : #AFC8FA;"><B>Total of ' . $nGenes . ' gene entr' . ($nGenes == 1? 'y' : 'ies') . '</B></TD>' . "\n" .
      '          <TD style="background : #AFC8FA;"><B>' . $nTotal . '</B></TD>' . "\n" .
      '          <TD style="background : #AFC8FA;"><B>' . $nUnique . '</B></TD>' . "\n" .
      '          <TD style="background : #AFC8FA;"><B>' . $nTotalPatients . '</B></TD></TR></TABLE><BR><BR>' . "\n\n");

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