<?php
/*
    ANISEED, NISEED System and Database
    Copyright (C) 2007 Olivier Tassy, Fabrice Daian, Patrick Lemaire

    This program 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 3 of the License, or
    (at your option) any later version.

    This program 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 this program.  If not, see <http://www.gnu.org/licenses/>.

*/
header('Content-Type: text/plain');
include("connection.php");
?>
<? 
	if( isset($_GET["option"]) ) $option=$_GET["option"];
	if( isset($_GET["offset"]) ) $offset=$_GET["offset"];
	if( isset($_GET["nbMAX"])  ) $nbMAX=$_GET["nbMAX"];
	
	if(! isset($option)) $option= 1;
	if(! isset($offset)) $offset= 0;
	if(! isset($nbMAX)) $nbMAX= 20;
	
	
	/////////////////////////////////////////////////////////////////////////////////
	////////////////////////////////////requete//////////////////////////////////////
	/////////////////////////////////////////////////////////////////////////////////
	
	
	//recup species
	if(isset($_GET["species"]))
		$species = $_GET["species"];
	
	//recup regions	
	/*Test sur la selection d'une partie anatomique dans la page précédente*/
	if ($_GET["idanat"]=="undefined:") {print "<br><br><center><h2><strong>Error, please select an anatomical part.</strong></h2></center><br><br>"; include ("frames_footer.php");return;}

	if( (strlen($_GET["idanat"])>1)&&($_GET["fatemode"]==0) ){
		$sql_region='';
		$tabanat=split(":",$_GET["idanat"]);
		array_pop($tabanat);
		
		if($_GET["BOOLanat"]==1){//and
			//signifie que l'on cherche aussi les parents des anat fournis			if($_GET["xref"]==1)
			{
/* SIMULATION D'UNE REQUETE SUR HIERARCHIE PAR UNE PROCEDURE DE REQUETES RECURSIVES */
$IN1="";
function select_recursif_descendant_2($conn,$start,$res)
{
global $IN1;

if ($start!='')
$requete2="SELECT * FROM \"ANAT_ENTITY\" WHERE \"ANAT_ID\"= $start ";
else return;

$stmt2=pg_query($conn,$requete2);
while($row2=pg_fetch_array($stmt2))
	{
	$IN1.=$row2["ANAT_ID"].",";
	$res=select_recursif_descendant_2($conn,$row2["CHILD_OF"],$res);
	}
return $res;
}
/* FIN DE LA SIMULATION */

				for($a=0;$a<count($tabanat);++$a)
					{
					$IN1="";
					$res=select_recursif_descendant_2($c,$tabanat[$a],$res);
					//$IN1=substr($IN1,0,strlen($IN1)-1);
					$IN1.=$tabanat[$a];
					
					if ($IN1=="") $IN1="0";	/*Cas du IN vide*/
					$sql_region.="INTERSECT SELECT \"IN_SITU_ID\" FROM \"TARGET\" WHERE \"REGION_ID\" IN(".
					$IN1.") ";
					}
		/*IN1=SELECT ANAT_ID FROM CIONA_DESIGN.ANAT_ENTITY CONNECT BY ANAT_ID = PRIOR CHILD_OF START WITH ANAT_ID= $tabanat[$a] */
			
			}
			//signifie que l'on cherche aussi les enfants des anat fournis			else if($_GET["xref"]==2)
			{
/* SIMULATION D'UNE REQUETE SUR HIERARCHIE PAR UNE PROCEDURE DE REQUETES RECURSIVES */
$IN2="";
function select_recursif_descendant($conn,$start,$res)
{
global $IN2;

if ($start!='')
$requete2="SELECT * FROM \"ANAT_ENTITY\" WHERE \"CHILD_OF\"= $start ";
else return;

$stmt2=pg_query($conn,$requete2);
while($row2=pg_fetch_array($stmt2))
	{
	$IN2.=$row2["ANAT_ID"].",";
	$res=select_recursif_descendant($conn,$row2["ANAT_ID"],$res);
	}
return $res;
}
/* FIN DE LA SIMULATION */





				for($a=0;$a<count($tabanat);++$a)
					{
					$IN2="";
					$res=select_recursif_descendant($c,$tabanat[$a],$res);
					//$IN2=substr($IN2,0,strlen($IN2)-1);
					$IN2.=$tabanat[$a];
					if ($IN2=="") $IN2="0"; /*Cas du IN vide*/
					$sql_region.="INTERSECT SELECT \"IN_SITU_ID\" FROM \"TARGET\" WHERE \"REGION_ID\" IN(".$IN2.") ";
					}






/*SELECT ANAT_ID FROM CIONA_DESIGN.ANAT_ENTITY CONNECT BY  CHILD_OF = PRIOR ANAT_ID START WITH ANAT_ID= $tabanat[$a]*/

			}
			//signifie que l'on cherche le terme exact			else{
				for($a=0;$a<count($tabanat);++$a){
					$sql_region.="INTERSECT SELECT \"IN_SITU_ID\" FROM \"TARGET\" WHERE \"REGION_ID\" = $tabanat[$a]";
				}
			}
			
			if($_GET["only"]==1){  //gestion du only  //avec le and on veut autant de trucs marques que de trucs selectionnes mais pas plus.
			    /*$sql_region.=" intersect select \"IN_SITU_ID\" from (select \"TARGET.*, count(*) over (partition by in_situ_id) count from CIONA_DESIGN.TARGET) where count = ".(count($tabanat));*/
			}
			
		}else{//or
			$zz=join(",",$tabanat);
			//if($_GET["only"]==2)  //gestion de l'option "not stained"
			//    $sql_region="INTERSECT SELECT IN_SITU_ID FROM CIONA_DESIGN.TARGET WHERE NOT EXIST( REGION_ID IN(".$zz.") )";
		    //else
			
			$sql_region=" INTERSECT (SELECT \"IN_SITU_ID\" FROM \"TARGET\" WHERE \"REGION_ID\" IN (".$zz.") ";
			if($_POST["only"]==1){  //gestion du only  //avec le or on veut juste l'un des trucs selectionnes comme marqué ou une combinaison mais pas plus.
			    $sql_region.=" EXCEPT SELECT \"IN_SITU_ID\" FROM \"TARGET\" WHERE \"REGION_ID\" NOT IN (".$zz.")";
			}
			$sql_region.=")";
			
			/*
			$sql_region="INTERSECT SELECT IN_SITU_ID FROM CIONA_DESIGN.TARGET WHERE ( REGION_ID IN(".$zz.") )";
			
			if($_GET["only"]==1){  //gestion du only  //avec le or on veut juste l'un des trucs selectionnes de marqué mais pas plus.
			    $sql_region.=" intersect select IN_SITU_ID from (select CIONA_DESIGN.TARGET.*, count(*) over (partition by in_situ_id) count from CIONA_DESIGN.TARGET) where count = 1";
			}
			*/
		    
		}
		
	}

	
	
	// Les Fates
$sql_fate="";
$tabfate="";
if(isset($_POST["fate"]))
$tabfate=$_POST["fate"];

$tabfatelist="";
if(is_array($tabfate)){
$tabfatelist=join("','",$tabfate);
}else{
$tabfatelist=$tabfate;
//$tabfate=split("','",$tabfatelist);
}
//print $tabfate[0];
if((strlen($tabfatelist)>0)&&($_POST["fatemode"]==1))
{ 
$sql_fate="INTERSECT(SELECT \"IN_SITU_ID\" FROM \"TARGET\" WHERE \"REGION_ID\" in (select \"ANAT_ID\" from \"FATE\" where \"KEYWORDS\" in ('".$tabfatelist."')))";
}
	
	
	
	//get dev stage id
	//ne s'utilise qu'en l'absence d'anat_id
	//if( (strlen($_GET["selectediddev"])>1)&&((! strlen($sql_region)>0)||$_GET["only"]==2) ){
	if( (strlen($_GET["selectediddev"])>1)&&(! strlen($sql_region)>0) ){
		$tabdev=split(":",$_GET["selectediddev"]);
		array_pop($tabdev);
		$zz=join(",",$tabdev);
		
		$sql_dev_stage="INTERSECT SELECT \"IN_SITU_ID\" FROM \"IN_SITU\" WHERE ( \"DEV_STAGE_ID\" IN(".$zz.") )";
	}
	
	//check authors
	if( strlen($_GET["author"])>1 ){
		$author=split(":",$_GET["author"]);
		$zz=join("','",$author);
		$zz=strtoupper($zz);
		
		$sql_author="INTERSECT SELECT \"IN_SITU_ID\" FROM \"IN_SITU\" WHERE \"IN_SITU_ID\" IN(  select \"BIO_ID\" from \"ENTRY_ORIGIN\" where \"AUTHOR_ID\" in (select \"AUTHOR_ID\" from \"AUTHOR\" where UPPER(\"NAME\") in ('".$zz."') ) ) ";
	
	}
	
	
	//get targets
	if(strlen($_GET["target"])>0){
		$targetTMP = split(":",strtoupper($_GET["target"]));
		
		if(count($targetTMP)>100){
		    print "<br><br><strong>ERROR:</strong> due to performance reasons, you cannot search for more than 100 molecules";
		    include ("frames_footer.php");
			return;
		}
		
		$sql_target='';
		if($_GET["BOOLtarg"]==1){//and

			if($_GET["MOLtype"]==2){ //si c un gene -> recup des clones correspondant
			/*
				for ($a=0; $a < count($targetTMP); ++$a){
                    $sql_target.=" INTERSECT SELECT IN_SITU_ID FROM CIONA_DESIGN.TARGET WHERE TARGET_ID IN ( select B.clone_id from ciona_design.clone_seq A, ciona_design.clone B where (A.clone_name=B.clone_name) and A.cluster_id=(select cluster_id from ciona_design.clustering where gene_id=(select gene_id from ciona_design.gene where name='$targetTMP[$a]')) ) OR TARGET_ID IN ( select clone_seq_id from ciona_design.clone_seq where cluster_id=(select cluster_id from ciona_design.clustering where gene_id=(select gene_id from ciona_design.gene where name='$targetTMP[$a]')) )";
				}
			*/
			
				////nouvelle version pour utiliser le clustering maison
				for ($a=0; $a < count($targetTMP); ++$a){
                    /*
					$sql_target.=" INTERSECT
					 SELECT IN_SITU_ID
					  FROM CIONA_DESIGN.TARGET
					   WHERE TARGET_ID IN (
					    select B.clone_id from ciona_design.clone_seq A, ciona_design.clone B where A.clone_name=B.clone_name and A.aniseed_cluster_id=(select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]') )
						 OR TARGET_ID IN ( select clone_seq_id from ciona_design.clone_seq where aniseed_cluster_id=(select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]'))
						  OR TARGET_ID IN (select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]')";
                    */
					
					$sql_target.=" INTERSECT
					 SELECT \"IN_SITU_ID\"
					  FROM \"TARGET\"
					   WHERE \"TARGET_ID\" IN ( 
					   select B.\"CLONE_ID\" from \"CLONE_SEQ\" A, \"CLONE\" B where A.\"CLONE_NAME\"=B.\"CLONE_NAME\" and A.\"ANISEED_CLUSTER_ID\"=(select \"GENE_ID\" from \"GENE\" where upper(\"NAME\")='$targetTMP[$a]') 
					   UNION select \"CLONE_SEQ_ID\" from \"CLONE_SEQ\" where \"ANISEED_CLUSTER_ID\"=(select \"GENE_ID\" from \"GENE\" where upper(\"NAME\")='$targetTMP[$a]')
					   UNION select \"GENE_ID\" from \"GENE\" where upper(\"NAME\")='$targetTMP[$a]')";

				}
			
			}else{
                //si c un clone
				for ($a=0; $a < count($targetTMP); ++$a){
					$sql_target.=" INTERSECT SELECT \"IN_SITU_ID\" FROM \"TARGET\" WHERE \"TARGET_ID\" IN (SELECT \"MOL_ID\" FROM \"MOL_ENTITY\" WHERE upper(\"NAME\")='$targetTMP[$a]' OR upper(\"FULL_NAME\")='$targetTMP[$a]')";
				}
			}
			
		}else{//or
			
			if($_GET["MOLtype"]==2){ //si c un gene -> recup des clones correspondant
				$name='';
			    /*	
				for ($a=0; $a < count($targetTMP); ++$a){
					if($a==0)
						$name.=" (TARGET_ID IN ( select B.clone_id from ciona_design.clone_seq A, ciona_design.clone B where (A.clone_name=B.clone_name) and A.cluster_id=(select cluster_id from ciona_design.clustering where gene_id=(select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]')) ) OR TARGET_ID IN ( select clone_seq_id from ciona_design.clone_seq where cluster_id=(select cluster_id from ciona_design.clustering where gene_id=(select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]')) )) ";
					else
						$name.=" OR (TARGET_ID IN ( select B.clone_id from ciona_design.clone_seq A, ciona_design.clone B where (A.clone_name=B.clone_name) and A.cluster_id=(select cluster_id from ciona_design.clustering where gene_id=(select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]')) ) OR TARGET_ID IN ( select clone_seq_id from ciona_design.clone_seq where cluster_id=(select cluster_id from ciona_design.clustering where gene_id=(select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]')) )) ";
				}
				$sql_target=" INTERSECT SELECT IN_SITU_ID FROM CIONA_DESIGN.TARGET WHERE ".$name;
				*/
				
				////nouvelle version pour utiliser le clustering maison
                for ($a=0; $a < count($targetTMP); ++$a){
					if($a==0)
						$name.=" (\"TARGET_ID\" IN ( 
						select B.\"CLONE_ID\" from \"CLONE_SEQ\" A, \"CLONE\" B where A.\"CLONE_NAME\"=B.\"CLONE_NAME\" and A.\"ANISEED_CLUSTER_ID\"=(select \"GENE_ID\" from \"GENE\" where upper(\"NAME\")='$targetTMP[$a]')
						UNION select \"CLONE_SEQ_ID\" from \"CLONE_SEQ\" where \"ANISEED_CLUSTER_ID\"=(select \"GENE_ID\" from \"GENE\" where upper(\"NAME\")='$targetTMP[$a]')
						UNION select \"GENE_ID\" from \"GENE\" where upper(\"NAME\")='$targetTMP[$a]'
						";
						//$name.=" (TARGET_ID IN ( select B.clone_id from ciona_design.clone_seq A, ciona_design.clone B where A.clone_name=B.clone_name and A.aniseed_cluster_id=(select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]') ) OR TARGET_ID IN ( select clone_seq_id from ciona_design.clone_seq where aniseed_cluster_id=(select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]')) OR TARGET_ID IN (select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]') )";
					else
						$name.= "UNION select B.\"CLONE_ID\" from \"CLONE_SEQ\" A, \"CLONE\" B where A.\"CLONE_NAME\"=B.\"CLONE_NAME\" and A.\"ANISEED_CLUSTER_ID\"=(select \"GENE_ID\" from \"GENE\" where upper(\"NAME\")='$targetTMP[$a]') 
						UNION select \"CLONE_SEQ_ID\" from \"CLONE_SEQ\" where \"ANISEED_CLUSTER_ID\"=(select \"GENE_ID\" from \"GENE\" where upper(\"NAME\")='$targetTMP[$a]')
						UNION select \"GENE_ID\" from \"GENE\" where upper(\"NAME\")='$targetTMP[$a]'
						";
						//$name.=" OR (TARGET_ID IN ( select B.clone_id from ciona_design.clone_seq A, ciona_design.clone B where A.clone_name=B.clone_name and A.aniseed_cluster_id=(select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]') ) OR TARGET_ID IN ( select clone_seq_id from ciona_design.clone_seq where aniseed_cluster_id=(select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]')) OR TARGET_ID IN (select gene_id from ciona_design.gene where upper(name)='$targetTMP[$a]') )";
				
				}
				$sql_target=" INTERSECT SELECT \"IN_SITU_ID\" FROM \"TARGET\" WHERE ".$name."))";
                
			}else{
                //si c un clone
				$name='';
				for ($a=0; $a < count($targetTMP); ++$a){
					if($a==0)
						$name.=" (\"TARGET_ID\" IN (SELECT \"MOL_ID\" FROM \"MOL_ENTITY\" WHERE(upper(\"NAME\")='$targetTMP[$a]' OR upper(\"FULL_NAME\")='$targetTMP[$a]'))) ";
					else
						$name.=" OR (\"TARGET_ID\" IN (SELECT \"MOL_ID\" FROM \"MOL_ENTITY\" WHERE(upper(\"NAME\")='$targetTMP[$a]' OR upper(\"FULL_NAME\")='$targetTMP[$a]'))) ";
				}
				$sql_target=" INTERSECT SELECT \"IN_SITU_ID\" FROM \"TARGET\" WHERE ".$name;
			}
			
		}
	}
	
	
	//recup des mutants
	if((strlen($_GET["mutant"])>0)&&($_GET["BOOLmut"]<2)){
		$mutantTMP = split(":",strtoupper($_GET["mutant"]));
		$sql_mutant='';
		if($_GET["BOOLmut"]==1){//and
			for ($a=0; $a < count($mutantTMP); ++$a){
				$sql_mutant.=" INTERSECT SELECT \"IN_SITU_ID\" FROM \"MUTANT\" WHERE \"BIO_ID\" IN (SELECT \"MOL_ID\" FROM \"MOL_ENTITY\" WHERE upper(\"NAME\")='$mutantTMP[$a]' OR upper(\"FULL_NAME\")='$mutantTMP[$a]')";
			}
		}else if($_GET["BOOLmut"]==0){//or
			$name='';
			for ($a=0; $a < count($mutantTMP); ++$a){
				if($a==0)
					$name.=" (\"BIO_ID\" IN (SELECT \"MOL_ID\" FROM \"MOL_ENTITY\" WHERE(upper(\"NAME\")='$mutantTMP[$a]' OR upper(\"FULL_NAME\")='$mutantTMP[$a]'))) ";
				else
					$name.=" OR (\"BIO_ID\" IN (SELECT \"MOL_ID\" FROM \"MOL_ENTITY\" WHERE(upper(\"NAME\")='$mutantTMP[$a]' OR upper(\"FULL_NAME\")='$mutantTMP[$a]'))) ";
			}
			$sql_mutant=" INTERSECT SELECT \"IN_SITU_ID\" FROM \"MUTANT\" WHERE ".$name;
		}
		
	}else if($_GET["BOOLmut"]==2){//any
		    //$sql_mutant=" INTERSECT select in_situ_id from ciona_design.in_situ where wild_type='n'";
			$sql_mutant=" INTERSECT select \"IN_SITU_ID\" from \"MUTANT\"";
	}else if($_GET["BOOLmut"]==3){//none
		    //$sql_mutant=" INTERSECT select in_situ_id from ciona_design.in_situ where wild_type='y'";
			$sql_mutant=" INTERSECT select \"IN_SITU_ID\" from \"IN_SITU\" where \"IN_SITU_ID\" not in (select \"IN_SITU_ID\" from \"MUTANT\")";
	}
	
	
	//recup des manip d'embryon
	$sql_manip='';
	if($_GET["BOOLmanip"]==1){
	    $sql_manip.=" INTERSECT select \"IN_SITU_ID\" from \"EMBRYO_MANIP\"";
	}else if($_GET["BOOLmanip"]==2){
		$sql_manip=" INTERSECT select \"IN_SITU_ID\" from \"IN_SITU\" where \"IN_SITU_ID\" not in (select \"IN_SITU_ID\" from \"EMBRYO_MANIP\")";
	}
	
	
	////////////////////////////////
	//MEGA requete //
	////////////////////////////////
	
	
	
	//$requete="SELECT IN_SITU_ID FROM CIONA_DESIGN.IN_SITU ";
	$requete="SELECT \"IN_SITU_ID\" FROM \"IN_SITU\" WHERE (\"CHECKED\"='y') AND (\"PRIVATE\" = 'n') ";
	
	if((strlen($_GET[idanat]==0))&&(strlen($species)>0))
		$requete.=" INTERSECT SELECT \"IN_SITU_ID\" FROM \"IN_SITU\" WHERE \"SPECIES\" = '$species' ";
//		$requete.=" WHERE SPECIES = '$species' ";
	
//	$requete.=" INTERSECT SELECT BIO_ID FROM CIONA_DESIGN.BIO_ENTITY WHERE CURATED='y'";
	
	if(strlen($sql_dev_stage)>0)
		$requete.= $sql_dev_stage;
	
	if(strlen($sql_region)>0)
		$requete .= $sql_region;
	
	if(strlen($sql_fate)>0)
	    $requete .= $sql_fate;
	
	if(strlen($sql_author)>0)
		$requete .= $sql_author;
	
	if(strlen($sql_tar