<?php
include ("frames_header.php");
include("connection.php"); 	
?>

<h2><strong><em>Results of regulatory region search:</em></strong> </h2>

<?php

$khoueiry = "";
$first = true;
if(strlen($_POST["species"]) > 0 ){
  	if($first){
		$khoueiry .= "SELECT \"PROMOTER_ID\" FROM \"PROMOTER\", \"MOLECULAR_ENTITY\" WHERE \"PROMOTER_ID\"=\"MOL_ID\" AND \"SPECIES\"='".$_POST["species"]."' ";	    
		$first = false;
	} else {
		$khoueiry .= " INTERSECT SELECT \"PROMOTER_ID\" FROM \"PROMOTER\", \"MOLECULAR_ENTITY\" WHERE \"PROMOTER_ID\"=\"MOL_ID\" AND \"SPECIES\"='".$_POST["species"]."' ";	    	  
	}
}

if(strlen($_POST["name"]) > 0 ){
  	if($first){
		$khoueiry .= "SELECT \"PROMOTER_ID\" FROM \"PROMOTER\" WHERE UPPER(\"NAME\") LIKE '%".strtoupper($_POST["name"])."%' ";
		$first = false;
	} else {
		$khoueiry .= " INTERSECT SELECT \"PROMOTER_ID\" FROM \"PROMOTER\" WHERE UPPER(\"NAME\") LIKE '%".strtoupper($_POST["name"])."%' ";
	}  
  		
}

if(strlen($_POST["author"]) > 0 ){
	$authors=split(":",$_POST["author"]);	
	foreach($authors as $author){
  		if($first){
			$khoueiry .= "SELECT A.\"PROMOTER_ID\" FROM \"PROMOTER\" A, \"ENTRY_ORIGIN\" B, \"AUTHOR\" C WHERE A.\"PROMOTER_ID\"=B.\"BIO_ID\" AND B.\"AUTHOR_ID\"=C.\"AUTHOR_ID\" AND UPPER(C.\"NAME\") LIKE = '%".strtoupper($author)."%' ";	  
			$first = false;
		} else {
			$khoueiry .= " INTERSECT SELECT A.\"PROMOTER_ID\" FROM \"PROMOTER\" A, \"ENTRY_ORIGIN\" B, \"AUTHOR\" C WHERE A.\"PROMOTER_ID\"=B.\"BIO_ID\" AND B.\"AUTHOR_ID\"=C.\"AUTHOR_ID\" AND UPPER(C.\"NAME\") LIKE = '%".strtoupper($author)."%' ";	  
		}	  	  	  	  
	}
}

if(strlen($_POST["target"]) > 0 ){
	$targets=split(":",$_POST["target"]);
	foreach($targets as $target){
  		if($first){
			$khoueiry .= "SELECT A.\"PROMOTER_ID\" FROM \"PROMOTER\" A, \"GENE_REGULATION\" B, \"GENE\" C WHERE A.\"PROMOTER_ID\"=B.\"PROMOTER_ID\" AND B.\"GENE_ID\"=C.\"GENE_ID\" AND C.\"NAME\"='".$target."' ";
			$first = false;
		} else {
			$khoueiry .= " INTERSECT SELECT A.\"PROMOTER_ID\" FROM \"PROMOTER\" A, \"GENE_REGULATION\" B, \"GENE\" C WHERE A.\"PROMOTER_ID\"=B.\"PROMOTER_ID\" AND B.\"GENE_ID\"=C.\"GENE_ID\" AND C.\"NAME\"='".$target."' ";
		}	  	  
	}
}

if(strlen($_POST["annotator"]) > 0 ){
	$annotators=split(":",$_POST["annotator"]);	
	foreach($annotators as $annotator){
  		if($first){
			$khoueiry .= "SELECT A.\"PROMOTER_ID\" FROM \"PROMOTER\" A, \"ENTRY_ORIGIN\" B, \"AUTHOR\" C WHERE A.\"PROMOTER_ID\"=B.\"BIO_ID\" AND B.\"ANNOTATOR_ID\"=C.\"AUTHOR_ID\" AND UPPER(C.\"NAME\") LIKE = '%".strtoupper($annotator)."%' ";	  
			$first = false;
		} else {
			$khoueiry .= " INTERSECT SELECT A.\"PROMOTER_ID\" FROM \"PROMOTER\" A, \"ENTRY_ORIGIN\" B, \"AUTHOR\" C WHERE A.\"PROMOTER_ID\"=B.\"BIO_ID\" AND B.\"ANNOTATOR_ID\"=C.\"AUTHOR_ID\" AND UPPER(C.\"NAME\") LIKE = '%".strtoupper($annotator)."%' ";	  
		}	  	  
	}
}

if(strlen($_POST["pmid"]) > 0 ){
	$pmids=split(":",$_POST["pmid"]);
	foreach($pmids as $pmid){
  		if($first){
			$khoueiry .= "SELECT A.\"PROMOTER_ID\" FROM \"PROMOTER\", \"REFERENCE\" WHERE \"PROMOTER_ID\"=\"BIO_ID\" AND \"PUBMED_ID\"=".$pmid." ";	
			$first = false;
		} else {
			$khoueiry .= " INTERSECT SELECT A.\"PROMOTER_ID\" FROM \"PROMOTER\", \"REFERENCE\" WHERE \"PROMOTER_ID\"=\"BIO_ID\" AND \"PUBMED_ID\"=".$pmid." ";	
		}	  	  	    
	}
}

if (strlen($_POST["datefrom"])>0) {
  	if($first){
		$khoueiry .= " INTERSECT SELECT \"PROMOTER_ID\" FROM \"PROMOTER\" WHERE \"PROMOTER_ID\" IN ( select \"BIO_ID\" from \"ENTRY_ORIGIN\" where \"ENTRY_DATE\" BETWEEN '".$_POST["datefrom"]."' and MAX(\"ENTRY_DATE\")) ";
		$first = false;
	} else {
		$khoueiry .= " INTERSECT SELECT \"PROMOTER_ID\" FROM \"PROMOTER\" WHERE \"PROMOTER_ID\" IN ( select \"BIO_ID\" from \"ENTRY_ORIGIN\" where \"ENTRY_DATE\" BETWEEN '".$_POST["datefrom"]."' and MAX(\"ENTRY_DATE\")) ";
	}  
}

if (strlen($_POST["dateto"])>0) {
  	if($first){
		$khoueiry .= " INTERSECT SELECT \"PROMOTER_ID\" FROM \"PROMOTER\" WHERE \"PROMOTER_ID\" IN ( select \"BIO_ID\" from \"ENTRY_ORIGIN\" where \"ENTRY_DATE\" BETWEEN MIN(\"ENTRY_DATE\") AND '".$_POST["dateto"]."') ";
		$first = false;
	} else {
		$khoueiry .= " INTERSECT SELECT \"PROMOTER_ID\" FROM \"PROMOTER\" WHERE \"PROMOTER_ID\" IN ( select \"BIO_ID\" from \"ENTRY_ORIGIN\" where \"ENTRY_DATE\" BETWEEN MIN(\"ENTRY_DATE\") AND '".$_POST["dateto"]."') ";
	}    
}

//debug!
//echo '<p>'.$khoueiry.'</p>';

$promids = array();
$stmt = pg_query($c,$khoueiry); 
while($row=pg_fetch_array($stmt)){
	$promids[] = $row["PROMOTER_ID"];
}

$proms = array();
foreach($promids as $promid){
	$khoueiry = "SELECT * FROM \"PROMOTER\" WHERE \"PROMOTER_ID\"=".$promid." AND \"PRIVATE\"!='y'";
	$stmt = pg_query($c,$khoueiry); 
	if($row=pg_fetch_array($stmt)){
		$proms[] = $row;
	}	 		
}

$data = array();
foreach($proms as $prom){
	$nbrG = 0;	
	$khoueiry = "SELECT A.\"TYPE\", B.\"NAME\" FROM \"GENE_REGULATION\" A, \"GENE\" B WHERE A.\"GENE_ID\"=B.\"GENE_ID\" AND A.\"PROMOTER_ID\"=".$prom["PROMOTER_ID"].";";
	$stmt = pg_query($c,$khoueiry); 
	while($row=pg_fetch_array($stmt)){
	  	$nbrG++;
	  	$new_data = array();
	  	$new_data["NAME"]=$prom["NAME"];
		$new_data["PROMOTER_ID"]=$prom["PROMOTER_ID"];		
		$new_data["TYPE"]=$row["TYPE"];
		$new_data["GENE_REGULATED"]=$row["NAME"];		
		$data[] = $new_data;
	}
	if($nbrG==0){
	  	$new_data = array();
	  	$new_data["NAME"]=$prom["NAME"];
		$new_data["PROMOTER_ID"]=$prom["PROMOTER_ID"];		
		$data[] = $new_data;
	}	
	
}

	if(count($data)>0){
		echo '		  
<table style="text-align: center;" border="0" cellpadding="2" cellspacing="2">
    <tr>
      <td
 style="background-color: rgb(153, 0, 0); color: rgb(255, 255, 255); text-align: center; width: 300px;">Name</td>
      <td
 style="background-color: rgb(153, 0, 0); color: rgb(255, 255, 255); text-align: center; width: 150px;">Regulated
Gene</td>
      <td
 style="background-color: rgb(153, 0, 0); color: rgb(255, 255, 255); text-align: center; width: 150px;">Type
of Regulation</td>
      <td
 style="background-color: rgb(153, 0, 0); color: rgb(255, 255, 255); text-align: center; width: 100px;">Curated
      </td>
      <td
 style="background-color: rgb(153, 0, 0); color: rgb(255, 255, 255); text-align: center; width: 180px;">
      </td>
    </tr>';  
	} else {
	  echo '<h2>No Match</h2>';
	}
	
	$pair = false;
	foreach($data as $data_item){	  
	  if($pair){
	  	$colour = 'background-color: rgb(200, 200, 200);';
	  } else {
	    $colour = 'background-color: rgb(100, 100, 100);';
	 }	  
		echo ' 
    <tr>
      <td style="'.$colour.' width: 300px;">'.$data_item["NAME"].'</td>';
      if(isset($data_item["GENE_REGULATED"])){
	    echo '<td style="'.$colour.' width: 150px;">'.$data_item["GENE_REGULATED"].'</td>';
	  } else {
	    echo '<td style="'.$colour.' width: 150px;">Undefined</td>';
	  }
	  
	  if(isset($data_item["TYPE"])){
	  	echo '<td style="'.$colour.' width: 150px;">'.$data_item["TYPE"].'</td>';
	  } else {
		echo '<td style="'.$colour.' width: 150px;">Undefined</td>';
	  }
	
	  if(isset($data_item["CHECKED"]) && $data_item["CHECKED"]=='y'){	          
	  	echo '<td style="'.$colour.' width: 100px;">Yes</td>';
	  } else {
		echo '<td style="'.$colour.' width: 100px;">No</td>';
	  }	
			  
      echo '         
      <td style="'.$colour.' width: 180px;"><a target="_blank" href="reg-region-edit.php?id='.$data_item["PROMOTER_ID"].'">
	  <img style="width: 75px; height: 30px;" alt="Details" title="See details"  src="pictures/details.gif"></a>
	<!--<a target="_blank" href="http://not.ready.yet">
	  <img style="width: 75px; height: 30px;" alt="GBrowse" title="See region in Genome Browser" 
	  		src="pictures/gbrowse.gif"></a>--></td>
    </tr>';	
	
		$pair = !$pair;	
	}
	
	if(count($data)>0){
	  echo '</table>';
	}
	
?>

<?php
include ("frames_footer.php");
?>