display_phenotype.php.bkp

<?php session_start();

/*
//A php script to dynamically read data related to a particular experiment from the database and to 
//display it in a nice table format. Utilizes the the tableclass Class by Manuel Lemos to display the 
//table.

//Author: Kartic Ramesh; drastically rewritten by Julie Dickerson, 2009 to make usable and use sessions

// 2/18/2010 J.Lee Fix "Download Raw Data" button not showing with IE browser 
// 3/01/2010 J.Lee Handle missing Raw Data files 
// 6/24/2010 J.Lee Merged with Julie's changes 
// 6/29/2010 J.Lee Fixed table display issue with MSIE7 and realign dataset download button
*/


require 'config.php';
include($config['root_dir'].'includes/bootstrap.inc');
include($config['root_dir'].'theme/normal_header.php');
$delimiter = "\t";


connect();
    //-----------------------------------------------------------------------------------
    $trial_code=$_GET['trial_code'];
    //echo $trial_code."<br>";
    $sql_auth="SELECT data_public_flag FROM experiments WHERE trial_code='$trial_code'";
    $res_auth=mysql_query($sql_auth) or die(mysql_error());
    $row_auth=mysql_fetch_array($res_auth);
    
    // if data is public or if the user is CAP certified, then show data
    $data_public_flag=$row_auth['data_public_flag'];
    if (($data_public_flag ==1)|| ((authenticate(array(USER_TYPE_PARTICIPANT,
			USER_TYPE_CURATOR, USER_TYPE_ADMINISTRATOR)))&&($data_public_flag ==0)))
    {
        $sql="SELECT experiment_uid FROM experiments WHERE trial_code='$trial_code'";
        $result=mysql_query($sql);
        $row=mysql_fetch_array($result);
        $experiment_uid=$row['experiment_uid'];
        $datasets_exp_uid=$experiment_uid;
        //echo $experiment_uid."<br>";
        
        // Display Header information about the experiment
        $display_name=ucwords($trial_code); //used to display a beautiful name as the page header
        echo "<h1>".$display_name."</h1>";
        
        
        $query="SELECT * FROM phenotype_experiment_info WHERE experiment_uid='$experiment_uid'"; //used to display the annotation details 
        $result_pei=mysql_query($query) or die(mysql_error());
        $row_pei=mysql_fetch_array($result_pei);
        echo "<table>";
        
        echo "<tr> <td>Location (Latitude/Longitude)</td><td>".$row_pei['location']."  ".$row_pei['latitude_longitude']."</td></tr>";
        echo "<tr> <td>Planting Date</td><td>".$row_pei['planting_date']."</td></tr>";
        echo "<tr> <td>Harvest Date</td><td>".$row_pei['harvest_date']."</td></tr>";
        echo "<tr> <td>Seeding Rate</td><td>".$row_pei['seeding_rate']."</td></tr>";
        echo "<tr> <td>Experiment Design</td><td>".$row_pei['experiment_design']."</td></tr>";
        echo "<tr> <td>Plot Size</td><td>".$row_pei['plot_size']."</td></tr>";
        echo "<tr> <td>Harvest Area</td><td>".$row_pei['harvest_area']."</td></tr>";
        echo "<tr> <td>Irrigation</td><td>".$row_pei['irrigation']."</td></tr>";
        echo "<tr> <td>Number of Replications</td><td>".$row_pei['number_replications']."</td></tr>";
        echo "<tr> <td>Comments</td><td>".$row_pei['other_remarks']."</td></tr>";
        echo "</table>";

        $rawsql="SELECT raw_data_file_name FROM experiments WHERE trial_code='$trial_code'";
        $rawres=mysql_query($rawsql) or die(mysql_error());
        $rawrow=mysql_fetch_array($rawres);
        $rawfilename=$rawrow['raw_data_file_name'];
		$rawfile="raw/phenotype/".$rawfilename;
		
		if (empty($rawfilename)) {
			echo "<h3><b>NOTE: Sorry raw data file not available.</b> </h3>"; 
        } else {
        	echo "<form action='$rawfile' ?>";
        	echo "<input type='submit' value='Download Raw File'></input>";
       		echo "</form>";
		}
        // get all line data for this experiment
        $sql="SELECT tht_base_uid, line_record_uid, check_line FROM tht_base WHERE experiment_uid='$experiment_uid'";
        $result_thtbase=mysql_query($sql) or die(mysql_error());
        
        while($row_thtbase=mysql_fetch_array($result_thtbase))
        {
            $thtbase_uid[] = $row_thtbase['tht_base_uid'];
            $linerecord_uid[] = $row_thtbase['line_record_uid'];
            $check_line[] = $row_thtbase['check_line'];
            //echo $row_thtbase['tht_base_uid']."  ".$row_thtbase['line_record_uid']."  ".$row_thtbase['check_line']."<br>";
        }
        $num_lines = count($linerecord_uid);
        //echo $num_lines."<br>";
        $titles=array('Line Name'); //stores the titles for the display table with units
        $titles[]="BP Code";//add BP column to titles
        
        $thtbasestring = implode(",",$thtbase_uid);
        
        $sql1="SELECT DISTINCT p.phenotypes_name as name, p.phenotype_uid as uid, units.unit_name as unit, units.sigdigits_display as sigdig
                FROM phenotype_data as pd, phenotypes as p, units
                WHERE p.phenotype_uid = pd.phenotype_uid
                AND units.unit_uid = p.unit_uid
                AND pd.tht_base_uid IN ($thtbasestring)";
        //echo $sql1."<br>";
        $result1=mysql_query($sql1) or die(mysql_error());
        $num_phenotypes = mysql_num_rows($result1);

        //echo "$num_phenotypes Rows\n";
        while($row1=mysql_fetch_array($result1))
        {
            $phenotype_data_name[]=$row1['name'];
            $phenotype_uid[]=$row1['uid'];
            $unit_sigdigits[]=$row1['sigdig'];
            $unit_name[]=$row1['unit'];
            $titles[]=ucwords($row1['name'])." (".strtolower($row1['unit']).")";         
        }
        
        $titles[]="Check"; //add the check column to the display table
        
         
        $all_rows=array(); //2D array that will hold the values in table format to be displayed
        $single_row=array(); //1D array which will hold each row values in the table format to be displayed
        
        $myFile = "THTdownload.txt";//auto generate a delimited file with the queried data
		
		$dir =$config['root_dir'].'downloads/temp/';		
        // create a download file
		$fh = fopen($dir.$myFile, "w");


        
        $stringData = implode(",",$titles);
       // echo $stringData."<br>";
        $stringData .= "\n";
        fwrite($fh, $stringData);
         
        //---------------------------------------------------------------------------------------------------------------
        //Go through lines to create a data table for display
        for ($lr_i=0;$lr_i<$num_lines;$lr_i++)
        {
            $thtbaseuid=$thtbase_uid[$lr_i];
            $linerecorduid=$linerecord_uid[$lr_i];
            //echo $linerecorduid."  ".$thtbaseuid."<br>";
            
            $sql_lnruid="SELECT line_record_name FROM line_records WHERE line_record_uid='$linerecorduid'";
            $result_lnruid=mysql_query($sql_lnruid) or die(mysql_error());
            $row_lnruid=mysql_fetch_assoc($result_lnruid);
            $lnrname=$row_lnruid['line_record_name'];
            $single_row[0]=$lnrname;
            
            //get the bp code

            $sql_dpc="SELECT cap.data_program_code
                        FROM CAPdata_programs as cap, tht_base as tb, datasets_experiments as de,datasets as d
                        WHERE tb.tht_base_uid='$thtbaseuid'
                            AND de.datasets_experiments_uid = tb.datasets_experiments_uid
                            AND d.datasets_uid = de.datasets_uid
                            AND cap.CAPdata_programs_uid = d.CAPdata_programs_uid";
            $res_dpc=mysql_query($sql_dpc) or die(mysql_error());
            $num_dp = mysql_num_rows($res_dpc);
            $dpc='';
            if ($num_dp>0) {
                $row_dpc=mysql_fetch_assoc($res_dpc);
                $dpc=$row_dpc['data_program_code'];
             }
            $single_row[1]=$dpc;
            
            for($i=0;$i<$num_phenotypes;$i++)
            {
                $puid=$phenotype_uid[$i];
                $sigdig=$unit_sigdigits[$i];
                $sql_val="SELECT value FROM phenotype_data
                    WHERE tht_base_uid='$thtbaseuid'
                    AND phenotype_uid = '$puid'";
                //echo $sql_val."<br>";
                $result_val=mysql_query($sql_val);
                if (mysql_num_rows($result_val)>0){
                	$row_val=mysql_fetch_assoc($result_val);
                	$val=$row_val['value'];
                    if ($sigdig>=0)
                        $val=number_format($val,$sigdig);
                }else {
                    $val = "--"; 
                }
                $single_row[$i+2]=$val;
            }
        //-----------------------------------------check line addition

            if($check_line[$lr_i]=='yes') $check=1;
            else $check=0;
            //echo $check;
            $single_row[$num_phenotypes+2]=$check;
            //-----------------------------------------
           //var_dump($single_row);
			$stringData= implode($delimiter,$single_row);
            //echo $stringData."<br>";
            $stringData.="\n";
            
            fwrite($fh, $stringData);
            $all_rows[]=$single_row;
        }
            //-----------------------------------------get statistics
        $mean_arr=array('Mean','');
        $se_arr=array('Standard Error','');
        $nr_arr=array('Number Replicates','');
        $prob_arr=array('Prob > F','');
            
        $fmean="Mean,";
        $fse="SE,";
        $fnr="Number Replicates,";
        $fprob="Prob gt F,";
        
        
        for($i=0;$i<$num_phenotypes;$i++)
        {
            $puid=$phenotype_uid[$i];
            $sigdig=$unit_sigdigits[$i];
            
            $sql_mdata="SELECT mean_value,standard_error,number_replicates,prob_gt_F
                FROM phenotype_mean_data
                WHERE phenotype_uid='$puid'
                AND experiment_uid='$experiment_uid'";
            $res_mdata=mysql_query($sql_mdata) or die(mysql_error());
            $row_mdata=mysql_fetch_array($res_mdata);
            $mean=$row_mdata['mean_value'];
            $se=$row_mdata['standard_error'];
            $nr=$row_mdata['number_replicates'];
            $prob=$row_mdata['prob_gt_F'];
        
            if($mean!=0) {	
                if ($sigdig>=0)
                    $mean=number_format($mean,$sigdig);
                $mean_arr[] = $mean;
            } else {
                $mean_arr[]="--";
            }
            
            if($se!=0) {	
                if ($sigdig>=0)
                    $se=number_format($se,$sigdig);
                $se_arr[] = $se;
            } else {	
                $se_arr[]="--";
            }
            
            if($nr==0) {
                $nr="--";
            }
            $nr_arr[]=$nr;
            
            if($prob!=0) {
                $prob=number_format($prob,2);
                $prob_arr[]=$prob;
             } else {
                $prob_arr[]="--";
            }
        
        }
        
        $fmean= implode($delimiter,$mean_arr)."\n";
        $fse= implode($delimiter,$se_arr)."\n";
        $fnr= implode($delimiter,$nr_arr)."\n";
        $fprob= implode($delimiter,$prob_arr)."\n";
        
        fwrite($fh,$fmean);
        fwrite($fh,$fse);
        fwrite($fh,$fnr);
        fwrite($fh,$fprob);
        
        $all_rows[]=$mean_arr;
        $all_rows[]=$se_arr;
        $all_rows[]=$nr_arr;
        $all_rows[]=$prob_arr;
        
        //-----------------------------------------
        
        
        $total_rows=count($all_rows); //used to determine the number of rows to be displayed in the result page
        
        
        fclose($fh);
        
        
         ?>
       
			 <!--Style sheet for better user interface-->
		
		<style type="text/css">
			th {background: #5B53A6 !important; color: white !important; border-left: 2px solid #5B53A6}
			table {background: none; border-collapse: collapse}
			td {border: 1px solid #eee !important;}
			h3 {border-left: 4px solid #5B53A6; padding-left: .5em;}
		</style>
		<style type="text/css">
        	table.marker {background: none; border-collapse: collapse}
			th.marker { background: #5b53a6; color: #fff; padding: 5px 0; border: 0; }
            td.marker { padding: 5px 0; border: 0 !important; }
        </style>
			  
        <table>
        <thead>
        <tr> 
		
        <?php
        for($i=0;$i<count($titles);$i++)
        {
        ?>
				<th style="width: 500px;"> 
				<?php echo $titles[$i]?>
				</th>
        <?php
        }
        ?>
        </tr>
        </thead>

			<tbody style="padding: 0; height: 300px; overflow: scroll;border: 1px solid #5b53a6;">		
			<!--[if IE]>
				<tbody style="padding:0;overflow-x:auto;overflow-y:auto;;border:1px solid #5b53a6;"> 
			<![endif]-->	 

			<?php
				for ($i = 0; $i < count($all_rows); $i++)
				{
			
			?>
			
			<tr>
			
			<?php
				for ($j = 0; $j < count($single_row); $j++)
				{
			?>
			<td style="width:500px;"> 	
			<?php echo $all_rows[$i][$j] ?>
			</td> 
			
			<?php
				}/* end of for loop */
			?>
			</tr>
			
			<?php
			}
			?>
			</tbody>
			</table>
			
        
        <?php
        
        echo "<br>";
        echo "<form action='downloads/temp/THTdownload.txt' ?>";
        //echo "<form action=<?php header("Location: ".$dir.$myFile);
        //header("Location: ".$dir.$filename);
        echo "<input type='submit' value='Download Experiment Data'></input>";
        echo "</form>";
       // header("Location: ".$dir.$myFile);
	
  	echo "</div>";
        
    } else {
        	?>	<div class="section">
			<p> There are no publicly available datasets for this program and year in THT at this time. Participants in the BarleyCAP project will need to login to see additional datasets.</p>
            </div>
            <?php
    }
    
    //-----------------------------------------------------------------------------------
    $footer_div = 1;
    include($config['root_dir'].'theme/footer.php'); 
    ?>