t3_report.php

<?php
/**
 * Content Status
 *
 * PHP version 5.3
 *
 * @author  Clay Birkett <clb343@cornell.edu>
 * @license http://triticeaetoolbox.org/wheat/docs/LICENSE Berkeley-based
 * @link    http://triticeaetoolbox.org/wheat/t3_report.php
 */

set_time_limit(0);
ini_set('memory_limit', '4G');

require 'config.php';
require $config['root_dir'].'includes/bootstrap.inc';

//query for count of genotyping_data table takes too long
//cache the results of the queries
//updating the cache is done every 24 hours

$mysqli = connecti();

$user_agent = $_SERVER['HTTP_USER_AGENT'];
$accept = $_SERVER['HTTP_ACCEPT'];

if (isset($_GET['output'])) {
    $output = $_GET['output'];
} else {
    $output = "";
}
if (isset($_REQUEST['query'])) {
    $query = $_REQUEST['query'];
    $opt = intval($_REQUEST['opt']);
} else {
    $query = "";
    $opt = "";
}
if (isset($_POST['startdate'])) {
    $startdate = $_POST['startdate'];
} else {
    $startdate = "";
}
if (isset($_POST['enddate'])) {
    $enddate = $_POST['enddate'];
} else {
    $enddate = "";
}

$sql = "select database()";
$res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli) . "<br>$sql");
if ($row = mysqli_fetch_row($res)) {
    $db = $row[0];
} else {
    die("Can not identify database\n");
}
if (! file_exists('/tmp/tht')) {
    mkdir('/tmp/tht');
}
$cachefile = '/tmp/tht/cache_' . $db . '.txt';
if ($query == 'geno') {
    $count = 0;
    include $config['root_dir'].'theme/admin_header2.php';
    print "markers with no genotype data<br>\n";
    print "<table border=0>";
    print "<tr><td>marker_uid<td>marker_name\n";
    $sql = "select markers.marker_uid, markers.marker_name from markers where marker_uid NOT IN (Select marker_uid from allele_frequencies)";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $marker_uid = $row[0];
        $marker_name = $row[1];
        print "<tr><td>$marker_uid<td>$marker_name\n";
        $count++;
    }
    print "</table>\n";
    print "total $count markers missing genotype data<br>\n";
} elseif ($query == 'geno2') {
    $total_count = 0;
    include $config['root_dir'].'theme/admin_header2.php';
    print "<h1>Genotyping data by experiment</h1>\n";
    print "<table border=0>";
    print "<tr><td>Trial Code<td>experiment name<td>platform<td>lines<td>markers<td>genotype data\n";
    if (preg_match('/THT/', $db)) {
        $sql = "select experiment_short_name, count(marker_uid) from experiments as e, tht_base as tb, genotyping_data as gd where e.experiment_uid = tb.experiment_uid AND gd.tht_base_uid = tb.tht_base_uid group by e.experiment_uid";
        $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
        while ($row = mysqli_fetch_row($res)) {
            $total_count += $row[2];
            print "<tr><td><a href='".$config['base_url']."genotyping/display_genotype.php?trial_code=$row[0]'>$row[0]</a><td>$row[1]<td>$row[2]\n";
        }
    } else {
        $sql = "select experiment_uid, platform_name from genotype_experiment_info, platform where genotype_experiment_info.platform_uid = platform.platform_uid";
        $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
        while ($row = mysqli_fetch_row($res)) {
            $euid = $row[0];
            $platform_name = $row[1];
            $platform_list[$euid] = $platform_name;
        }
        $sql = "select experiments.experiment_uid, trial_code, experiment_short_name, count(marker_uid) from experiments, allele_frequencies where experiments.experiment_uid = allele_frequencies.experiment_uid group by allele_frequencies.experiment_uid";
        $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
        while ($row = mysqli_fetch_row($res)) {
            $experiment_uid = $row[0];
            $trial_code = $row[1];
            $experiment_short_name = $row[2];
            $num_mark = $row[3];
            $sql = "select count(line_record_uid) from tht_base where experiment_uid = $experiment_uid";
            $res2 = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
            $row2 = mysqli_fetch_row($res2);
            $line_count = $row2[0];
            $geno_count = $line_count * $num_mark;
            $total_count += $geno_count;
            $geno_count = number_format($geno_count);
            print "<tr><td><a href='".$config['base_url']."genotyping/display_genotype.php?trial_code=$trial_code'>$trial_code</a><td>$experiment_short_name<td>$platform_list[$experiment_uid]<td>$line_count<td>$num_mark<td>$geno_count\n";
        }
    }
    $total_count = number_format($total_count);
    print "<tr><td>total<td><td><td><td>$total_count\n";
    print "</table>";
} elseif ($query == 'linegeno') {
    include $config['root_dir'].'theme/admin_header2.php';
    print "Lines with genotyping data\n";
    print "<table border=0>";
    print "<tr><td>breeding program code<td>count\n";
    $sql = "select distinct(breeding_program_code) from line_records";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $program_code = $row[0];
        if (preg_match("/[A-Z0-9]+/", $program_code)) {
            $sql2 = "select count(distinct(line_records.line_record_uid)) from line_records, tht_base, genotyping_data where (line_records.line_record_uid = tht_base.line_record_uid) and (tht_base.tht_base_uid = genotyping_data.tht_base_uid) and (line_records.breeding_program_code = '$program_code')";
            $res2 = mysqli_query($mysqli, $sql2) or die(mysqli_error($mysqli));
            $row2 = mysqli_fetch_row($res2);
            $count = $row2[0];
            print "<tr><td>$program_code<td>$count\n";
        }
    }
    print "</table>\n";
} elseif ($query == 'linephen') {
    include $config['root_dir'].'theme/admin_header2.php';
    print "Lines with phenotype data\n";
    print "<table border=0>";
    print "<tr><td>breeding program code<td>count\n";
    $sql = "select distinct(breeding_program_code) from line_records";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $program_code = $row[0];
        if (preg_match("/[A-Z0-9]+/", $program_code)) {
            $sql2 = "select count(distinct(line_records.line_record_uid)) from line_records, tht_base, phenotype_data where (line_records.line_record_uid = tht_base.line_record_uid) and (tht_base.tht_base_uid = phenotype_data.tht_base_uid) and (line_records.breeding_program_code = '$program_code')";
            $res2 = mysqli_query($mysqli, $sql2) or die(mysqli_error($mysqli));
            $row2 = mysqli_fetch_row($res2);
            $count = $row2[0];
            print "<tr><td>$program_code<td>$count\n";
        }
    }
} elseif ($query == 'lineant') {
    include $config['root_dir'].'theme/admin_header2.php';
    print "Lines with annotation\n";
    $sql = "select distinct(line_records.line_record_uid) from line_records, barley_pedigree_catalog_ref where line_records.line_record_uid = barley_pedigree_catalog_ref.line_record_uid";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $line_record_uid = $row[0];
        $sql2 = "SELECT b_ref.barley_ref_number as value, b.link_out
        FROM barley_pedigree_catalog_ref AS b_ref, barley_pedigree_catalog AS b
        WHERE b_ref.line_record_uid = $line_record_uid 
        AND b.barley_pedigree_catalog_uid = b_ref.barley_pedigree_catalog_uid";
        $res2 = mysqli_query($mysqli, $sql2) or die($mysqli_error($mysqli));
        while ($row2 = mysqli_fetch_assoc($res2)) {
            $value = urlencode($row2['value']);
            $url_bpc = str_replace('XXXX', $value, $row2['link_out']);
            print "<a href=\"$url_bp\"c>$line_record_uid</a>";
        }
        print "<br>\n";
    }
} elseif ($query == 'Lines') {
    include $config['root_dir'].'theme/admin_header2.php';
    print "Top 100 Line names ordered by creation date<br><br>\n";
    print "<form action=t3_report.php method='POST'>";
    print "<input type=hidden name=query value=Lines />";
    print "Start Date: <input type=text name=startdate />";
    print "End Date: <input type=text name=enddate />";
    print "<input type=submit /> Use date format 2012-08-27";
    print "</form><br>";
    $sql = "select line_record_uid, line_record_name, date_format(created_on,'%m-%d-%y') from line_records";
    if (empty($startdate) || empty($enddate)) {
        $sql .= " order by created_on desc limit 100";
    } else {
        $sql .= " where (created_on > '$startdate') and (created_on < '$enddate')";
        $sql .= " order by created_on desc";
    }
    print "<table border=0>";
    print "<tr><td>Line name<td>created on\n";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $uid = $row[0];
        $name = $row[1];
        $date = $row[2];
        print "<tr><td><a href='".$config['base_url']."view.php?table=line_records&uid=$uid'>$name</a><td>$date\n";
    }
} elseif ($query == 'Markers') {
    include $config['root_dir'].'theme/admin_header2.php';
    if ($opt == "") {
        $msg_opt = "";
    } else {
        if ($stmt = mysqli_prepare($mysqli, "select marker_type_name from marker_types where marker_type_uid = ?")) {
            mysqli_stmt_bind_param($stmt, "i", $opt);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_bind_result($stmt, $msg_opt);
            mysqli_stmt_fetch($stmt);
            mysqli_stmt_close($stmt);
        }
    }
    print "Top 100 $msg_opt names ordered by creation date<br><br>\n";
    print "<form action=t3_report.php method='POST'>";
    print "<input type=hidden name=query value=Markers />";
    print "Start Date: <input type=text name=startdate />";
    print "End Date: <input type=text name=enddate />";
    print "<input type=submit /> Use date format 2012-08-27";
    print "</form><br>";
    print "<table border=0>";
    $sql = "select markers.marker_uid, marker_name, date_format(markers.created_on,'%m-%d-%y'), marker_type_name from markers, marker_types";
    if ($opt == "") {
        $sql_opt = "";
    } else {
        $sql_opt = " and markers.marker_type_uid = $opt";
    }
    if (empty($startdate) || empty($enddate)) {
        $sql .= " where markers.marker_type_uid = marker_types.marker_type_uid $sql_opt order by markers.created_on desc limit 100";
    } else {
        $sql .= " where (markers.created_on > '$startdate') and (markers.created_on < '$enddate') $sql_opt";
        $sql .= " and markers.marker_type_uid = marker_types.marker_type_uid order by markers.created_on desc";
    }
    print "<tr><td>Marker name<td>type<td>created on\n";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $uid = $row[0];
        $name = $row[1];
        $date = $row[2];
        $type = $row[3];
        print "<tr><td><a href='".$config['base_url']."view.php?table=markers&uid=$uid'>$name</a><td>$type<td>$date\n";
    }
    print "</table>";
} elseif ($query == 'PExps') {
    include $config['root_dir'].'theme/admin_header2.php';
    print "Phenotype experiments<br><br>\n";
    print "<table border=0>";
    print "<tr><td>Name<td>Description<td>Coordinator\n";
    $sql = "select experiment_set_name, description, coordinator from experiment_set order by experiment_set_name";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        print "<tr><td>$row[0]<td>$row[1]<td>$row[2]\n";
    }
    print "</table>";
} elseif ($query == 'phenotype') {
    include $config['root_dir'].'theme/admin_header2.php';
    print "Phenotype trials ordered by creation date<br><br>\n";
    print "<table border=0>";
    print "<tr><td>Trial Code<td>Experiment Name<td>Plot Level data<td>created on\n";
    $sql = "select distinct(experiment_uid) from phenotype_plot_data";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $uid = $row[0];
        $plot_level[$uid] = 1;
    }
    $sql = "select trial_code, experiment_short_name, date_format(experiments.created_on, '%m-%d-%y'), experiment_uid from experiments, experiment_types
    where experiments.experiment_type_uid = experiment_types.experiment_type_uid and experiment_types.experiment_type_name = 'phenotype'";
    if (!authenticate(array(USER_TYPE_PARTICIPANT, USER_TYPE_CURATOR, USER_TYPE_ADMINISTRATOR))) {
        $sql .= " and data_public_flag > 0";
    }
    $sql .= " order by experiments.created_on desc";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $trial_code = $row[0];
        $short_name = $row[1];
        $date = $row[2];
        $uid = $row[3];
        if (isset($plot_level[$uid])) {
            $type = "Yes";
        } else {
            $type = "";
        }
        print "<tr><td><a href='".$config['base_url']."display_phenotype.php?trial_code=$trial_code'>$trial_code</a><td>$short_name<td>$type<td>$date\n";
    }
    print "</table>";
} elseif ($query == 'genotype') {
    include $config['root_dir'].'theme/admin_header2.php';
    print "Trials ordered by creation date<br><br>\n";
    print "<table border=0>";
    print "<tr><td>Trial Code<td>Experiment Name<td>type<td>created on\n";
    $sql = "select trial_code, experiment_short_name, date_format(experiments.created_on, '%m-%d-%y'), experiment_type_name from experiments, experiment_types
      where experiments.experiment_type_uid = experiment_types.experiment_type_uid and experiment_types.experiment_type_name = 'genotype'";
    if (!authenticate(array(USER_TYPE_PARTICIPANT,
                            USER_TYPE_CURATOR,
                            USER_TYPE_ADMINISTRATOR))) {
                        $sql .= " and data_public_flag > 0";
    }
    $sql .= " order by experiments.created_on desc";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $trial_code = $row[0];
        $short_name = $row[1];
        $date = $row[2];
        $type = $row[3];
        print "<tr><td><a href='".$config['base_url']."genotyping/display_genotype.php?trial_code=$trial_code'>$trial_code</a><td>$short_name<td>$type<td>$date\n";
    }
    print "</table>";
} elseif ($query == 'metabolite') {
    include $config['root_dir'].'theme/admin_header2.php';
    print "Metabolite trials ordered by creation date<br><br>\n";
    print "<table border=0>";
    print "<tr><td>Trial Code<td>Experiment Name<td>Plot Level data<td>created on\n";
    $sql = "select distinct(experiment_uid) from phenotype_plot_data";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $uid = $row[0];
        $plot_level[$uid] = 1;
    }
    $sql = "select trial_code, experiment_short_name, date_format(experiments.created_on, '%m-%d-%y'), experiment_uid from experiments, experiment_types
    where experiments.experiment_type_uid = experiment_types.experiment_type_uid and experiment_types.experiment_type_name = 'metabolite'";
    if (!authenticate(array(USER_TYPE_PARTICIPANT, USER_TYPE_CURATOR, USER_TYPE_ADMINISTRATOR))) {
        $sql .= " and data_public_flag > 0";
    }
    $sql .= " order by experiments.created_on desc";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $trial_code = $row[0];
        $short_name = $row[1];
        $date = $row[2];
        $uid = $row[3];
        if (isset($plot_level[$uid])) {
            $type = "Yes";
        } else {
            $type = "";
        }
        print "<tr><td><a href='".$config['base_url']."compounds/display_metabolite.php?trial_code=$trial_code'>$trial_code</a><td>$short_name<td>$type<td>$date\n";
    }
    print "</table>";
} elseif ($query == 'cache') {
    echo "Cache slow queries\n";
    $sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    $sql = "select count(genotyping_data_uid) from genotyping_data";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row = mysqli_fetch_row($res)) {
        $allele_count = $row[0];
    } else {
        print "error $sql<br>\n";
    }
    // now get a count including GBS
    $allele_count2 = 0;
    $sql = "select experiments.experiment_uid, trial_code, experiment_short_name, count(marker_uid) from experiments, allele_frequencies where experiments.experiment_uid = allele_frequencies.experiment_uid group by allele_frequencies.experiment_uid";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $experiment_uid = $row[0];
        $trial_code = $row[1];
        $experiment_short_name = $row[2];
        $num_mark = $row[3];
        $sql = "select count(line_record_uid) from tht_base where experiment_uid = $experiment_uid";
        $res2 = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
        $row2 = mysqli_fetch_row($res2);
        $line_count = $row2[0];
        $geno_count = $line_count * $num_mark;
        $allele_count2 += $geno_count;
    }

    $sql = "select date_format(max(created_on),'%m-%d-%Y') from genotyping_data";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row = mysqli_fetch_row($res)) {
        $allele_update = $row[0];
    } else {
        print "error $sql<br>\n";
    }
    $sql = "select count(distinct(line_records.line_record_uid)) from line_records, tht_base, genotyping_data where (line_records.line_record_uid = tht_base.line_record_uid) and (tht_base.tht_base_uid = genotyping_data.tht_base_uid)";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row = mysqli_fetch_row($res)) {
        $LinesWithGeno = $row[0];
    }
    $sql = "select count(markers.marker_uid) from markers where marker_uid IN (Select marker_uid from allele_frequencies)";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row=mysqli_fetch_row($res)) {
        $MarkersWithGeno = $row[0];
    }
    $sql = "select count(markers.marker_uid) from markers where marker_uid NOT IN (Select marker_uid from allele_frequencies)";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row=mysqli_fetch_row($res)) {
        $MarkersNoGeno = $row[0];
    }

    $fp = fopen($cachefile, 'w');
    if ($fp == false) {
        echo "Error: could not create cache file\n";
    } else {
        fwrite($fp, "$allele_count\n");
        fwrite($fp, "$allele_update\n");
        fwrite($fp, "$LinesWithGeno\n");
        fwrite($fp, "$MarkersWithGeno\n");
        fwrite($fp, "$MarkersNoGeno\n");
        fwrite($fp, "$allele_count2\n");
        fclose($fp);
    }
    return;
} elseif ($query == "csr1") {
    include $config['root_dir'].'theme/admin_header2.php';
    print "<h3>Trials with Canopy Spectral Reflectance (CSR) data</h3>\n";
    print "<table border=0>";
    print "<tr><td>Trial Code<td>Year<td>Files loaded\n";
    $sql = "select distinct(csr_measurement.experiment_uid), experiments.trial_code, experiments.experiment_year from csr_measurement, experiments where csr_measurement.experiment_uid = experiments.experiment_uid order by experiments.experiment_year";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $uid = $row[0];
        $trial_code = $row[1];
        $year = $row[2];
        $sql = "select count(measurement_uid) from csr_measurement where experiment_uid = $uid";
        $res2 = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
        if ($row2 = mysqli_fetch_row($res2)) {
            $count = $row2[0];
        } else {
            $count = "";
        }
        print "<tr><td><a href='".$config['base_url']."display_phenotype.php?trial_code=$trial_code'>$trial_code</a><td>$year<td>$count\n";
    }
    print "</table>";
} else {
    if ($output == 'excel') {
        require 'Spreadsheet/Excel/Writer.php';
        $workbook = new Spreadsheet_Excel_Writer();
        $workbook->send('t3_report.xls');
        $format_header =& $workbook->addFormat();
        $format_header->setBold();
        $format_title =& $workbook->addFormat();
        $format_title->setBold();
        $format_title->setAlign('merge');
        $worksheet =& $workbook->addWorksheet() or die("can not open workbook");
    } else {
        include($config['root_dir'].'theme/admin_header2.php');
        print "<div class=box>";
        $worksheet = null;
    }
  
    $sql = "select database()";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row = mysqli_fetch_row($res)) {
        $db = $row[0];
    } else {
        print "error $sql<br>\n";
    }
    /** read in from cache */
    $cachefile = '/tmp/tht/cache_' . $db . '.txt';
    $cachetime = 24 * 60 * 60; //24 hours
    $cmd = "wget --no-check-certificate " . $config['base_url'] . "t3_report.php?query=cache > /dev/null &";
    $cmd = "curl -ks " . $config['base_url'] . "t3_report.php?query=cache > /dev/null &";
    if (file_exists($cachefile)) {
        $fp = fopen($cachefile, 'r');
        $allele_count = fgets($fp);
        $allele_update = fgets($fp);
        $LinesWithGeno = fgets($fp);
        $MarkersWithGeno = fgets($fp);
        $MarkersNoGeno = fgets($fp);
        $allele_count2 = fgets($fp);
        fclose($fp);
    } elseif ($output != "excel") {
        exec($cmd);
        echo "Regenerating cache, check back in 20 minutes";
    }
    if (file_exists($cachefile)) {
        if (time() - $cachetime > filemtime($cachefile)) {
            exec($cmd);
        }
    } else {
        exec($cmd);
    }
    $allele_count = number_format($allele_count);
    $allele_count2 = number_format($allele_count2);
    $date = date_create(date('Y-m-d'));
    $date = $date->format('Y-m-d');
    if ($output == "excel") {
        $worksheet->write(0, 0, "$db Data Submission Report $date", $format_title);
        $worksheet->write(0, 1, "", $format_title);
        $worksheet->write(0, 2, "", $format_title);
        $worksheet->write(0, 3, "", $format_title);
    } elseif ($output == "") {
        print "<h2>$db Data Submission Report $date</h2>";
    }
    if ($output == "") {
        print "<form action=t3_report.php method='get'>";
        print "<input type=hidden name='output' value='excel'>";
        print "<input type='submit' value='Download tables to MS Excel'>";
        print "</form><br>";
    }

    $this_week = date_create(date('Y-m-d'));
    $this_month = date_create(date('Y-m-d'));
    $this_week->sub(new DateInterval('P7D'));
    $this_month->sub(new DateInterval('P30D'));
    $this_week = $this_week->format('Y-m-d');
    $this_month = $this_month->format('Y-m-d');

    $index = 1;
    if ($output == "excel") {
        $worksheet->write($index, 0, "Trials", $format_header);
    } else {
        print "<b>Experiments and Trials</b>\n";
        print "<table>\n";
    }
    $index++;
    $sql = "select count(experiment_set_uid) from experiment_set";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row = mysqli_fetch_row($res)) {
        $count = $row[0];
        if ($output == "excel") {
            $worksheet->write($index, 0, "Phenotype Experiments");
            $worksheet->write($index, 1, "$count");
        } else {
            print "<tr><td>Phenotype Experiments<td>$count<td><a href='".$config['base_url']."t3_report.php?query=PExps'>List all experiments</a>\n";
        }
    }
    $index++;
    $sql = "select experiment_type_name, count(experiment_types.experiment_type_uid) from experiments, experiment_types
        where experiments.experiment_type_uid = experiment_types.experiment_type_uid group by experiment_types.experiment_type_uid";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $type = $row[0];
        $count = $row[1];
        if ($output == "excel") {
            $worksheet->write($index, 0, "$type Trials submitted");
            $worksheet->write($index, 1, "$count");
        } else {
            print "<tr><td>$type Trials</td><td>$count<td><a href='".$config['base_url']."t3_report.php?query=$type'>List all trials</a></td></tr>\n";
        }
        $index++;
    }

    $sql = "select count(distinct(capdata_programs_uid)) from experiments";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row = mysqli_fetch_row($res)) {
        $count = $row[0];
    } else {
        print "error $sql<br>\n";
    }
    if ($output == "excel") {
        $worksheet->write(5, 0, "CAP data programs");
        $worksheet->write(5, 1, "$count");
    } elseif ($output == "") {
        print "<tr><td>CAP data programs</td><td>$count</td></tr>\n";
        print "</table><br>";
    }

    $sql = "select count(line_record_uid) from line_records";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row = mysqli_fetch_row($res)) {
        $count = $row[0];
    } else {
        print "error $sql<br>\n";
    }
    if ($output == "excel") {
        $worksheet->write(6, 0, "Lines", $format_header);
        $worksheet->write(7, 0, "Line records");
        $worksheet->write(7, 1, $count);
    } elseif ($output == "") {
        print "<b>Lines</b><table><tr><td>Line records<td>$count<td><a href='".$config['base_url']."t3_report.php?query=Lines'>List or query line names by creation date</a>\n";
    }
    $sql = "select count(distinct(breeding_program_code)) from line_records";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row = mysqli_fetch_row($res)) {
        $count = $row[0];
    }
    if ($output == "excel") {
        $worksheet->write(8, 0, "Breeding programs");
        $worksheet->write(8, 1, $count);
    } elseif ($output == "") {
        print "<tr><td>Breeding programs</td><td>$count</td>\n";
    }
    if ($output == "excel") {
        $worksheet->write(9, 0, "Lines with genotypeing data");
        $worksheet->write(9, 1, $LinesWithGeno);
    } elseif ($output == "") {
        print "<tr><td>Lines with genotyping data<td>$LinesWithGeno<td><a href='".$config['base_url']."t3_report.php?query=linegeno'>List lines with genotyping data</a>\n";
    }
    $sql = "select count(distinct(line_records.line_record_uid)) from line_records, tht_base, phenotype_data where (line_records.line_record_uid = tht_base.line_record_uid) and (tht_base.tht_base_uid = phenotype_data.tht_base_uid)";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row = mysqli_fetch_row($res)) {
        $count1 = $row[0];
    }
    $sql = "select count(distinct(line_records.line_record_uid)) from line_records, barley_pedigree_catalog_ref where line_records.line_record_uid = barley_pedigree_catalog_ref.line_record_uid";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row = mysqli_fetch_row($res)) {
        $count2 = $row[0];
    }
    if ($output == "excel") {
        $worksheet->write(10, 0, "Lines with phenotype data");
        $worksheet->write(10, 1, $count);
        $worksheet->write(11, 0, "Species");
    } elseif ($output == "") {
        print "<tr><td>Lines with phenotype data<td>$count1<td><a href='".$config['base_url']."t3_report.php?query=linephen'>List lines with phenotype data</a>\n";
        print "<tr><td>Lines with annotation links<td>$count2<td><a href='".$config['base_url']."t3_report.php?query=lineant'>List lines with annotation</a>\n";
        print "<tr><td>Species<td>";
    }
    $count = "";
    $sql = "select pv.value from property_values pv, properties p 
          where p.name = 'species' 
          and p.properties_uid = pv.property_uid";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    while ($row = mysqli_fetch_row($res)) {
        $count = $count . "$row[0] ";
    }
    if ($output == "excel") {
        $worksheet->write(11, 1, $count);
    } elseif ($output == "") {
        print "$count\t";
    }

    $index = 12;
    $sql = "select date_format(max(created_on),'%m-%d-%Y') from line_records";
    $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
    if ($row = mysqli_fetch_row($res)) {
        $count = $row[0];
    }
    if ($output == "excel") {
        $worksheet->write($index, 0, "latest addition");
        $worksheet->write($index, 1, $count);
        $index++;
    } elseif ($output == "") {
        print "<tr><td>last addition<td>$count\n";
        print "</table><br>\n";
    }

  //* Phenotype data */
  $sql = "select count(phenotype_uid) from phenotypes";
  $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
  if ($row=mysqli_fetch_row($res)) {
    $count = $row[0];
  }
  if ($output == "excel") {
    $worksheet->write($index, 0, "Phenotype Data", $format_header);
    $index++;
    $worksheet->write($index, 0, "Traits");
    $worksheet->write($index, 1, $count);
    $index++;
  } else {
    print "<b>Phenotype Data</b>\n";
    print "<table>\n";
    print "<tr><td>Traits<td>$count<td><a href='".$config['base_url']."traits.php'>Trait descriptions and units</a>\n";
  }
  $sql = "select count(phenotype_uid) from phenotype_data";
  $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
  if ($row=mysqli_fetch_row($res)) {
    $count = $row[0];
  }
    if ($output == "excel") {
      $worksheet->write($index, 0, "Total phenotype data");
      $worksheet->write($index, 1, $count);
      $index++;
    } else {
        print "<tr><td>Total phenotype data<td>$count<td><a href='".$config['base_url']."phenotype_report.php'>List phenotype data by year and trait</a>\n";
  }
  $sql = "select date_format(max(created_on),'%m-%d-%Y') from phenotype_data";
  $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
  if ($row = mysqli_fetch_row($res)) {
      $count = $row[0];
  }
  if ($output == "excel") {
      $worksheet->write($index, 0, "last addition");
      $worksheet->write($index, 1, $count);
      $index++;
  } else {
      print "<tr><td>last addition<td>$count\n";
      print "</table><br>\n";
  }

  //* CSR data */
  $sql = "select count(distinct(experiment_uid)) from csr_measurement"; 
  $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
  if ($row=mysqli_fetch_row($res)) {
    $count = $row[0];
  }
  if ($output == "excel") {
  } else {
    print "<b>Canopy Spectral Reflectance (CSR) Data</b>\n";
    print "<table>\n";
    print "<tr><td>Trials<td>$count<td><a href='".$config['base_url']."t3_report.php?query=csr1'>List of experiments</a>\n";
  }
  $sql = "select count(measurement_uid) from csr_measurement";
  $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
  if ($row=mysqli_fetch_row($res)) {
    $count = $row[0];
  }
  if ($output == "excel") {
  } else {
    print "<tr><td>Files loaded<td>$count\n";
    print "</table><br>\n";
  }

  $count = "";
  $name = "";
  if ($output == "excel") {
    $worksheet->write($index, 0, "Genotype Data", $format_header);
    $index++;
  } else {
    print "<b>Genotype Data</b>\n";
    print "<table>\n";
  }
  $sql = "select count(marker_uid), marker_type_name, markers.marker_type_uid from markers, marker_types
    where markers.marker_type_uid = marker_types.marker_type_uid
    group by markers.marker_type_uid";
  $res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
  while ($row=mysqli_fetch_row($res)) {
    $count = $row[0];
    $name = $row[1];
    $marker_type_uid = $row[2];
    if ($output == "excel") {
      $worksheet->write($index, 0, "Markers $name");
      $worksheet->write($index, 1, $count);
      $index++;
    } else {
      print "<tr><td>Markers $name<td>$count<td><a href='".$config['base_url']."t3_report.php?query=Markers&opt=$marker_type_uid'>List or query markers by creation date</a>\n";
    }
  }
  if ($output == "excel") {
    $worksheet->write($index, 0, "Markers with genotyping data");
    $worksheet->write($index, 1, $MarkersWithGeno);
    $index++;
  } else {
    print "<tr><td>Markers with genotyping data<td>$MarkersWithGeno\n";
  } 
  if ($output == "excel") {
    $worksheet->write($index, 0, "Markers without genotyping data");
    $worksheet->write($index, 1, $MarkersNoGeno);
    $index++;
  } else {
    print "<tr><td>Markers without genotyping data<td>$MarkersNoGeno<td><a href='".$config['base_url']."t3_report.php?query=geno'>Markers without genotyping data</a>\n";
  }
  if ($output == "excel") {
	$worksheet->write($index, 0, "Total genotype data");
        $worksheet->write($index, 1, "$allele_count");
        $index++;
  } else {
        echo "<tr><td>Total genotype data, consensus<td>$allele_count<td><a href=\"" . $config['base_url'] . "t3_report.php?query=geno2\">List genotyping data by experiment</a>";
        echo "<tr><td>Total genotype data<td>$allele_count2<td><a href=\"" . $config['base_url'] . "t3_report.php?query=geno2\">List genotyping data by experiment</a>";
  }

  if ($output == "excel") {
    $worksheet->write($index, 0, "last addition");
    $worksheet->write($index, 1, $allele_update);
    $index++;
  } else {
    print "<tr><td>last addition<td>$allele_update\n";
    print "</table><br>\n";
    print "</div>";
  }
}
if ($output == "excel") {
    $workbook->close();
} else {
    print "</div>";
    include $config['root_dir'] . 'theme/footer.php';
}