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

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

<?php

//// COMPATIBILITY IF YOU COME FROM ARTICLE CARD /////////////////////////////////////////////////////////////////////////////////////////////

				if (count($_POST)==0)
					{
					print "<br><br><center><form name=\"formu00\" action=\"reg-region-new.php\" method=\"\">";
					print "<input type=\"submit\" value=\"Create a new regulatory region\">";
					print "</form></center><br><br>";
					}


				if (count($_POST)==0) foreach ($_GET as $key => $val) $_POST[$key]=$val;


//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

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

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

if( $_POST["rtype"] !== '-----ANY-----' ){
  	if($first){
		$khoueiry .= "SELECT \"PROMOTER_ID\" FROM \"PROMOTER\" WHERE \"TYPE\"='".$_POST["rtype"]."' ";
		$first = false;
	} else {
		$khoueiry .= "INTERSECT SELECT \"PROMOTER_ID\" FROM \"PROMOTER\" WHERE \"TYPE\"='".$_POST["rtype"]."' ";
	}  	
}

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(":",trim($_POST["pmid"]));
	foreach($pmids as $pmid){
  		if($first){
			$khoueiry .= "SELECT \"PROMOTER_ID\" FROM \"PROMOTER\", \"REFERENCE\" WHERE \"PROMOTER_ID\"=\"BIO_ID\" AND \"PUBMED_ID\"=".$pmid." ";	
			$first = false;
		} else {
			$khoueiry .= " INTERSECT SELECT \"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"]."') ";
	}    
}

if($khoueiry==""){
  	//then select all...
	$khoueiry = "SELECT \"PROMOTER_ID\" FROM \"PROMOTER\" WHERE \"BASE_REG_ID\"=\"PROMOTER_ID\""; 
	echo '<h3>Only the main regions are printed</h3>';
	
} 

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

if($_POST["news"]==1)
    $extra_sql .=" AND \"CHECKED\"='n'";

if($_POST["being"]==1)
    $extra_sql .=" AND \"BEING_CURATED\"='y'";

if($_POST["private"]==1)
    $extra_sql .=" AND \"PRIVATE\"='n'";
		

$proms = array();
foreach($promids as $promid){
	
	//$khoueiry = "SELECT * FROM \"PROMOTER\" WHERE \"PROMOTER_ID\"=".$promid." ".$extra_sql;
	$khoueiry = "SELECT * FROM \"PROMOTER\" WHERE \"PROMOTER_ID\"=".$promid." ".$extra_sql." AND
			\"PROMOTER_ID\" IN ( (SELECT \"PROMOTER_ID\" FROM \"PROMOTER\" WHERE \"PRIVATE\"='n' AND \"CHECKED\"='y') UNION (SELECT \"BIO_ID\" from \"ENTRY_ORIGIN\" where \"AUTHOR_ID\"='".$_SESSION["curationauthor"]."' OR \"ANNOTATOR_ID\"='".$_SESSION["curationauthor"]."' OR \"AUTHOR_ID\" in (select \"AUTHOR_ID\" from \"AUTHOR\" where \"TEAM\"=( select \"TEAM\" from \"AUTHOR\" where \"AUTHOR_ID\"='".$_SESSION["curationauthor"]."')) OR \"ANNOTATOR_ID\" in (select \"AUTHOR_ID\" from \"AUTHOR\" where \"TEAM\"=( select \"TEAM\" from \"AUTHOR\" where \"AUTHOR_ID\"='".$_SESSION["curationauthor"]."'))))";
			
//print $khoueiry."<br>";

	$stmt = pg_query($c,$khoueiry); 
	if($row=pg_fetch_array($stmt)){ 	
		$proms[] = $row;
	} else {		
		//otherwise if 
		$requete0="SELECT \"IS_CURATOR\",\"IS_ADMIN\" from \"AUTHOR\" where \"AUTHOR_ID\"='".$_SESSION["curationauthor"]."'";
		$stmt0=pg_query($c,$requete0);
		$row0=pg_fetch_array($stmt0);

		if ($row0["IS_ADMIN"]=="y") {
			$khoueiry = "SELECT * FROM \"PROMOTER\" WHERE \"PROMOTER_ID\"=".$promid." ".$extra_sql." AND \"PRIVATE\"='n'";
			$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"].";";	
	//to the pubilc search site - add the name of the gene...
	//$khoueiry = "SELECT \"GENE_ID\" FROM \"GENE_REGULATION\" \"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["STABLE_ID"]=$prom["STABLE_ID"];
		$new_data["PROMOTER_ID"]=$prom["PROMOTER_ID"];		
		//$new_data["TYPE"]=$row["TYPE"];
		$new_data["TYPE"]=$prom["TYPE"];
		$new_data["CHECKED"]=$prom["CHECKED"];	
	
		//only store the Id to later get the name: 	to the pubilc search site - add the name of the gene...
		//$new_data["GENE_ID"]=$row["GENE_ID"];
		
		$new_data["GENE_REGULATED"]=$row["NAME"];	
		
		$data[] = $new_data;
	
	}
	if($nbrG==0){
	  	$new_data = array();
	  	$new_data["NAME"]=$prom["NAME"];
		$new_data["STABLE_ID"]=$prom["STABLE_ID"];
		$new_data["PROMOTER_ID"]=$prom["PROMOTER_ID"];		
		$new_data["TYPE"]=$prom["TYPE"];
		$new_data["CHECKED"]=$prom["CHECKED"];		
		$data[] = $new_data;
	} else {
		//Get real names!!
		foreach ($data as $entry){
			
		}
	}	
	
}

//order by Name...
foreach ($data as $idx => $line) {
   $names[$idx] = $line["NAME"];
}
$names = $array_lowercase = array_map('strtolower', $names);
array_multisort($names,SORT_ASC,SORT_STRING,$data);

	//display
	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: left;">Identifier
 	</td>
 	<td
 style="background-color: rgb(153, 0, 0); color: rgb(255, 255, 255); text-align: left;">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 Region</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;">
      </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.' text-align: left;">'.$data_item["STABLE_ID"].'</td>
      <td style="'.$colour.' text-align: left;"><a target="_blank" href="reg-region-edit.php?id='.$data_item["PROMOTER_ID"].'">'.$data_item["NAME"].'</a></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;">-</td>';
	  }
	  
	  if(isset($data_item["TYPE"])){
	  	echo '<td style="'.$colour.' width: 150px;">'.$data_item["TYPE"].'</td>';
	  } else {
		echo '<td style="'.$colour.' width: 150px;">-</td>';
	  }
	
	  if(isset($data_item["CHECKED"]) && $data_item["CHECKED"]=='y'){	          
	  	echo '<td style="'.$colour.' width: 100px;"><font color="white">Yes</font></td>';
	  } else {
		echo '<td style="'.$colour.' width: 100px;"><font color="red">No</font></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>';
	
	
		$region_id_list=$promids[0];
		for($i=1;$i<count($promids);$i++){
			$region_id_list = $region_id_list.":".$promids[$i];
		}
	
		echo '<form Method="POST" Action="reg-region-export-xml.php" name="formulaire" target="_blank">';
		print "<input name=\"region_id_list\" type=\"hidden\" value=\"$region_id_list\">";
		echo '<input name="submit" type="image" title="Export regions as XML" src="pictures/button-export-xml.jpg" onClick="submit">';
		echo '</form>';
	}
		
?>

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