<?php

	header('Content-disposition: attachment; filename=regulatory_regions.xml'); 
	header('Content-Type: application/force-download');

	include ("connection.php");

	function print_region($c, $region_id){
		//Get all info...
		$author_date = "<author_date>";
		$author = "<author>";
		$khoueiry="SELECT A.\"NAME\", A.\"SURNAME\", B.\"ENTRY_DATE\" FROM \"AUTHOR\" A, \"ENTRY_ORIGIN\" B WHERE  (B.\"BIO_ID\"=$region_id) AND (A.\"AUTHOR_ID\"=B.\"AUTHOR_ID\")";	
    	$stmt = pg_query($c,$khoueiry);
    	while ($row=pg_fetch_array($stmt) ){
	  		$author = $author.$row["NAME"].", ".$row["SURNAME"].". ";	  	
			$author_date .= $row["ENTRY_DATE"];	
		}
		$author = $author."</author>\n";
		$author_date .= "</author_date>";

		$annotator = "<annotator>";
		$annotator_date = "<annotator_date>";
		$khoueiry="SELECT A.\"NAME\", A.\"SURNAME\", B.\"ANNOTATION_DATE\" FROM \"AUTHOR\" A, \"ENTRY_ORIGIN\" B WHERE  (B.\"BIO_ID\"=$region_id) AND (A.\"AUTHOR_ID\"=B.\"ANNOTATOR_ID\")";	
    	$stmt = pg_query($c,$khoueiry);
    	while ($row=pg_fetch_array($stmt) ){
	  		$annotator = $annotator.$row["NAME"].", ".$row["SURNAME"].". ";	  
			$annotator_date .= $row["ANNOTATION_DATE"];		
		}
		$annotator = $annotator."</annotator>\n";
		$annotator_date .= "</annotator_date>";

		$curator = "<curator>";
		$curator_date = "<curator_date>";
		$khoueiry="SELECT A.\"NAME\", A.\"SURNAME\", B.\"CURATION_DATE\" FROM \"AUTHOR\" A, \"ENTRY_ORIGIN\" B WHERE  (B.\"BIO_ID\"=$region_id) AND (A.\"AUTHOR_ID\"=B.\"CURATOR_ID\")";	
    	$stmt = pg_query($c,$khoueiry);
    	while ($row=pg_fetch_array($stmt) ){
	  		$curator = $curator.$row["NAME"].", ".$row["SURNAME"].". ";
			$curator_date .= $row["CURATION_DATE"];	  		
		}
		$curator = $curator."</curator>\n";
		$curator_date .= "</curator_date>";
		  
		$khoueiry = "SELECT * FROM \"PROMOTER\" WHERE \"PROMOTER_ID\"=".$region_id.";";	
		$stmt = pg_query($c,$khoueiry);
		if($row=pg_fetch_array($stmt)){	  
  	    
		$status_private="<status_private>".$row["PRIVATE"]."</status_private>";
		$status_being="<status_being>".$row["BEING_CURATED"]."</status_being>";
		$status_checked="<status_checked>".$row["CHECKED"]."</status_checked>";

			$stable_id="<stable_id>".$row["STABLE_ID"]."</stable_id>";
	    	$name="<name>".$row["NAME"]."</name>";
	    	$type="<type>".$row["TYPE"]."</type>";
			$is_natural = $row["IS_NATURAL"];	    	
	    	$comments="<comments>".strip_tags($row["COMMENTS"])."</comments>";	    	
	    	$begin = $row["BEGIN"];
		    $end = $row["END"];
		    $coordinates = "<coordinates>\n\t<begin>".$begin."</begin>\n\t<end>".$end."</end>\n</coordinates>";

   	    	$deletion = $row["DELETION"];
	   	    $mutation = $row["MUTATION"];
   		    $addition = $row["ADDITION"];
	    	$base_id=$row["BASE_REG_ID"];	
	    	
			$parent_region = "";
	    	//Modification...
	    	if(!is_null($base_id) && $base_id!=$region_id){	              	        									  		      	
		    	$khoueiry = "SELECT * FROM \"PROMOTER\" WHERE \"PROMOTER_ID\"=".$base_id.";";	
				$stmt = pg_query($c,$khoueiry);
				if($row=pg_fetch_array($stmt)){
					$parent_region = "<parent_region>\n\t<name>".$row["NAME"]."</name>\n";
					$parent_region = $parent_region."\t<stable_id>".$row["STABLE_ID"]."</stable_id>\n";
					if($deletion=="y"){$parent_region = $parent_region."\t<deletion>y</deletion>\n"; }
					if($mutation=="y"){$parent_region = $parent_region."\t<mutation>y</mutation>\n"; }
					if($addition=="y"){$parent_region = $parent_region."\t<addition>y</addition>\n"; }							
					$parent_region = $parent_region."</parent_region>";	
				}
			}	    	
	    }	    
		
		//ORIGIN
		$is_natural = $row["IS_NATURAL"];
		if($is_natural=='y'){
			$origin =  '<natural>';
			$khoueiry = "SELECT \"SPECIES\" FROM \"MOL_ENTITY\" WHERE \"MOL_ID\"=".$region_id.";";
			$stmt = pg_query($c,$khoueiry);
			if($row=pg_fetch_array($stmt)){
				$origin .= $row["SPECIES"];  
			} else {
				$origin .= "Unable to determine species";    
			}
			$origin .= "</natural>";
		} else {
		  $origin = "<artificial>y</artificial>";
		}	
		
		//REFERENCES
		$khoueiry="SELECT * FROM \"REFERENCE\" WHERE \"BIO_ID\"=$region_id;";
		$stmt = pg_query($c,$khoueiry);
	  	$references = "<references>\n";
		while($row=pg_fetch_array($stmt)){
		  	$references = $references."\t<description>".$row["AUTHORS"]."\t".$row["TITLE"]."\t".$row["PAPER"]."</description>\n";
		  	$references = $references."\t<pubmed_id>".$row["PUBMED_ID"]."</pubmed_id>\n";        
		}
	  	$references = $references."</references>";
		
		///////////////////////////////////////REGULATED GENES///////////////////////////////////////////////
		$regulated_genes = "<regulated_genes>\n";
		$khoueiry="select a.\"NAME\", a.\"GENE_ID\", b.\"TYPE\", b.\"SURE\" from \"GENE\" a, \"GENE_REGULATION\" b where (b.\"PROMOTER_ID\"='$region_id') and (a.\"GENE_ID\"=b.\"GENE_ID\")";
		$stmt = pg_query($c,$khoueiry);
		while($row=pg_fetch_array($stmt)){
 		    $regulated_genes = $regulated_genes."\t<regulated_gene>\n";
   		    $regulated_genes = $regulated_genes."\t\t<name>".$row["NAME"]."</name>\n";
   		    $regulated_genes = $regulated_genes."\t\t<type>".$row["TYPE"]."</type>\n";
		    $regulated_genes = $regulated_genes."\t\t<sure>".$row["SURE"]."</sure>\n";
			
   		    $regulated_genes = $regulated_genes."\t</regulated_gene>\n";
		}
		$regulated_genes = $regulated_genes."</regulated_genes>\n";
		
		///////////////////////////////////////CONSTRUCTS//////////////////////////////////////////////////////
		$constructs = "<constructs>\n";
		$khoueiry="SELECT * FROM \"CONSTRUCTION\" WHERE \"PROMOTER_ID\"=$region_id;";
		$stmt = pg_query($c,$khoueiry);

		while($row=pg_fetch_array($stmt)){		  

		  	$const_name=$row["NAME"];
			$const_stable_id = $row["STABLE_ID"];
			$const_id = $row["CONSTRUCTION_ID"];

	    	$const_comments=$row["COMMENTS"];
			//$const_comments=str_replace("<br>",".",$const_comments);
			$const_comments = strip_tags($const_comments);
	    	$promoter_id = $row["PROMOTER_ID"];
	    	$reporter = $row["REPORTER_ID"];
	    	$min_prom_id=$row["MIN_PROMOT_ID"];
			
			    
	    	$min_prom_name = "Unable to determine";
	    	if($promoter_id==$min_prom_id){
				$min_prom_name = "Endogenous Promoter";
			} else {
				$khoueiry2 = "SELECT \"NAME\" FROM \"PROMOTER\" WHERE \"PROMOTER_ID\"=".$min_prom_id.";";		
				$stmt2 = pg_query($c,$khoueiry2);
				if($row2=pg_fetch_array($stmt2)){			  
					$min_prom_name = $row2["NAME"];
				}
			}
			
			$khoueiry3 = "SELECT \"NAME\" FROM \"MOLECULAR_TOOL\" WHERE \"MOLECULAR_TOOL_ID\"=$reporter;";
			$stmt3 = pg_query($c,$khoueiry3);		
			$reporter = "Unable to determine";
			if($row3=pg_fetch_array($stmt3)){ 
			  	$reporter = $row3["NAME"];
			}
					  		  
 		    $constructs = $constructs."\t<construct>\n";
 		    $constructs = $constructs."\t\t<name>".$const_name."</name>\n";
			$constructs = $constructs."\t\t<stable_id>".$const_stable_id."</stable_id>\n";
		    $constructs = $constructs."\t\t<comments>".$const_comments."</comments>\n";
   		    $constructs = $constructs."\t\t<basal_promoter>".$min_prom_name."</basal_promoter>\n";
   		    $constructs = $constructs."\t\t<reporter>".$reporter."</reporter>\n";   		    
			
			//////////////////////////////////////////SEQUENCE/////////////////////////////////////////////
			$seq="";
			$khoueiry4="select \"SEQUENCE\", \"LENGTH\", \"COMMENTS\", \"MOL_ID\" from \"SEQUENCE\" where \"MOL_ID\"=$const_id";
			$stmt4 = pg_query($c,$khoueiry4);	
			if($row4=pg_fetch_array($stmt4)){
				$lob = $row4["SEQUENCE"];

				pg_query ($c, "begin");		
				$handle_lo = pg_lo_open($c,$lob,"r");
				$seq=pg_lo_read($handle_lo);
				pg_query($c,"end");
			
				$constructs = $constructs."\t\t<sequence>".$seq."</sequence>";		    			
				$constructs = $constructs."\t\t<seq_comments>".$row4["COMMENTS"]."</seq_comments>";
			}
			
 		    $constructs = $constructs."\t</construct>\n";
			
		}
		$constructs = $constructs."</constructs>\n";
		
		///////////////////////////////////////REGULATORY MOTIFS///////////////////////////////////////////////	
		$motifs ="<motifs>\n";
			
		$khoueiry="select A.\"SEQUENCE\", A.\"MOTIF_ID\",A.\"BEGIN\", A.\"STOP\", A.\"ORIENTATION\", A.\"UPDATE_MATRIX\", B.\"NAME\", B.\"COMMENTS\" from \"MOTIF_COORD\" A, \"MOTIF_REGUL\" B where (A.\"MOTIF_ID\"=B.\"MOTIF_ID\") and (A.\"PROMOTER_ID\"=$region_id) order by A.\"BEGIN\" ";
		$stmt = pg_query($c,$khoueiry);
		while($row=pg_fetch_array($stmt)){	    
   			$motifs = $motifs ."\t<motif>\n";
	        $motifs = $motifs ."\t\t<name>".$row["NAME"]."</name>\n";		
			$motifs = $motifs ."\t\t<coordinates><begin>".$row["BEGIN"]."</begin><end>".$row["STOP"]."</end></coordinates>\n";
   			$motifs = $motifs ."\t\t<sequence>".$row["SEQUENCE"]."</sequence>\n";   			
   			$motifs = $motifs ."\t</motif>\n";
		}
		
		$motifs = $motifs ."</motifs>\n";
		
		//////////////////////////////////////////SEQUENCE/////////////////////////////////////////////
		$seq = "";
		$khoueiry="select \"SEQUENCE\", \"LENGTH\", \"COMMENTS\", \"MOL_ID\" from \"SEQUENCE\" where \"MOL_ID\"=$region_id";
		$stmt = pg_query($c,$khoueiry);	
 		if($row=pg_fetch_array($stmt)){
	    	$lob = $row["SEQUENCE"];

			pg_query ($c, "begin");		
			$handle_lo = pg_lo_open($c,$lob,"r");
			$seq=pg_lo_read($handle_lo);
			pg_query($c,"end");
		}	
		
		$sequence = "<sequence>".$seq."</sequence>";
		$comment_sequence="<seq_comments>".$row["COMMENTS"]."</seq_comments>";
		
		$reg_region_url = "<region-url>reg-region-edit.php?id=".$region_id."</region-url>";			
		
		print "<regulatory_region>\n";
	
		print $stable_id."\n";
		print $name."\n";
		print $origin."\n";		
		print $type."\n";
		print $coordinates."\n";
		print $author."\n";
		print $author_date."\n";
		print $annotator."\n";
		print $annotator_date."\n";
		print $curator."\n";
		print $curator_date."\n";
		print $status_private."\n";
		print $status_being."\n";
		print $status_checked."\n";
		print $comments."\n";
		print $parent_region."\n";
		print $references."\n";
		print $regulated_genes."\n";
		print $motifs."\n";	
		print $constructs."\n";		
		print $sequence."\n";
		print $comment_sequence."\n";
		print $reg_region_url."\n";		
		
		print "</regulatory_region>\n";			
	}	
	
	$region_id_list = $_POST["region_id_list"];
	
	$regions = array();
	$tok = strtok($region_id_list,":");
	while($tok!==false){
	  	$regions[] = $tok;
		$tok = strtok(":");
	}		
	
	print "<?xml version=\"1.0\"?>\n";
	print "<data>\n";
	
	//echo "<!DOCTYPE note SYSTEM \"regulatory-regions.dtd\">\n";
	//echo "<regulatory-regions>\n";
	foreach($regions as $region_id){			
		print_region($c, $region_id);						
	}
	
	print "</data>\n";									
						
?>