<?php
/*
AUTHOR: Daniel Sobral
December 2006

This script creates a child of a given regulatory region
For this, it copies all its information and adds the given regulatory region as a parent
It creates new entities for all copied elements of the parent region, including sequence and motifs..

//CAUTION: Use of Magic Word regulatory_region

*/


include ("frames_header.php"); 
include ("connection.php");
	
	if ( (!isset($_SESSION["login"]))or(!isset($_SESSION["pass"])) ) {
	    print "<strong>you need to <a href=\"login.php\">login</a> as an authorised curator to proceed.</strong>";
		include ("frames_footer.php"); 
		return;
	}

	//From a number, creates a Stable ID "REG + 8 digits"
	function create_stable_id($number){
		$digits = 1;
		$tmp = $number;
		while(floor($tmp / 10)>0){
			$digits++;
			$tmp = $tmp / 10;
		}
		$st_id = "REG";
		for($int = 1; $int<=8-$digits; $int++){
			$st_id = $st_id."0";
		}
		$st_id = $st_id.$number;
		return $st_id;
	} 	
	
	
 	
  	$id = $_POST["id"];  


	//Get the maximum stable_id
	$khoueiry="SELECT MAX(\"STABLE_ID\") AS \"STABLE_ID\" FROM \"PROMOTER\"";
	$stmt = pg_query($c,$khoueiry) or die("<strong>ERROR </strong>".pg_last_error($c));
	if($row=pg_fetch_array($stmt)){ 
		//extract the number
		$max_stable_id = substr($row["STABLE_ID"],3);
		$new_stable_id = create_stable_id($max_stable_id + 1);
	}	
  	
  	$khoueiry = "SELECT * FROM \"PROMOTER\" WHERE \"PROMOTER_ID\"=$id;";  
  	$stmt = pg_query($c,$khoueiry);
	if ($row=pg_fetch_array($stmt)) {
	  
		$name=$row["NAME"];
		//name is apparently unique!
		$name = $name." rnd".rand(1,100);

		$begin=$row["BEGIN"];
		if(is_null($begin)){
			$begin=0;  
		}
		$end=$row["END"];
		if(is_null($end)){
			$end=0;  
		}
		$comments=$row["COMMENTS"];	
		$type=$row["TYPE"];	
		$is_natural=$row["IS_NATURAL"];
	} else {
		print '<strong>Error: Id not available</strong>';
		include ("frames_footer.php"); 
    	return;
	}
  	  	  	
	pg_query($c, "BEGIN") or die("<strong>SQL Error: </strong>".pg_last_error($c));

	//new entity
	$khoueiry="INSERT INTO \"BIO_ENTITY\" (\"BIO_ID\", \"CURATED\" ) values ( nextval('\"S_BIO_ID\"'),'n')";	  	
	pg_query($c,$khoueiry) or die('<Strong>Error inserting BIO_ENTITY: </Strong>'.pg_last_error($c));
	
	
	//COPY REFERENCES
	$khoueiry="SELECT * FROM \"REFERENCE\" WHERE \"BIO_ID\"=$id;";
	$stmt = pg_query($c,$khoueiry);
	while($row=pg_fetch_array($stmt)) {
		if(!is_null($row["PUBMED_ID"])){
			$khoueiry="INSERT INTO \"REFERENCE\" (\"BIO_ID\",\"PUBMED_ID\",\"TITLE\",\"PAPER\",\"AUTHORS\",\"URL\",\"COMMENTS\") VALUES (currval('\"S_BIO_ID\"'),".$row["PUBMED_ID"].",'".$row["TITLE"]."','".$row["PAPER"]."','".$row["AUTHORS"]."','".$row["URL"]."','".$row["COMMENTS"]."');";	
			pg_query($c,$khoueiry) or die('<strong>Error inserting REFERENCE: </strong>'.pg_last_error($c));					
		}
	}			
	
	$khoueiry = "SELECT CURRVAL('\"S_BIO_ID\"');";
	$stmt=pg_query($c,$khoueiry); 
  	$cur_prom_id=pg_fetch_result($stmt,0);		
	
	$khoueiry = "SELECT \"SPECIES\" FROM \"MOL_ENTITY\" WHERE \"MOL_ID\"=$id;";    
	$stmt = pg_query($c,$khoueiry);
	if ($row=pg_fetch_array($stmt)) {
		$species = $row["SPECIES"];	  
		if($is_natural=='y'){
		  	//CAUTION: Use of Magic Word regulatory_region
			$khoueiry = "INSERT INTO \"MOL_ENTITY\" (\"MOL_ID\", \"NAME\", \"SPECIES\",\"TYPE\") VALUES (currval('\"S_BIO_ID\"'),'$name','$species','regulatory_region');";  		      	    		  
		} else {
			$khoueiry = "INSERT INTO \"MOL_ENTITY\" (\"MOL_ID\", \"NAME\", \"TYPE\") VALUES (currval('\"S_BIO_ID\"'),'$name','regulatory_region');";  		      	    
		}
		pg_query($c,$khoueiry) or die('<strong>Error inserting MOL_ENTITY: </strong>'.pg_last_error($c));
		
	} else {
	  	echo '<strong>Error: Molecule for region '.$name.', with id '.$id.' does not exist</strong>';
	  	include ("frames_footer.php");
	  	return;
	}	
		
	$khoueiry="select \"SEQUENCE\", \"LENGTH\", \"COMMENTS\", \"MOL_ID\" from \"SEQUENCE\" where \"MOL_ID\"=$id";
	$stmt = pg_query($c,$khoueiry);
		
	if($row=pg_fetch_array($stmt)){
	    $lob = $row["SEQUENCE"];
	    $commSeq = $row["COMMENTS"];

		$handle_lo = pg_lo_open($c,$lob,"r");
		$seq=pg_lo_read($handle_lo);		
	    pg_lo_close($handle_lo);
		
		$size = strlen($seq);			
		$lob_id = pg_lo_create($c);
		$khoueiry="INSERT INTO \"SEQUENCE\" (\"MOL_ID\", \"SEQUENCE\", \"LENGTH\", \"COMMENTS\") values (currval('\"S_BIO_ID\"'),$lob_id,$size,'$commSeq')";
		
		pg_query($c,$khoueiry) or die ('<strong>Error inserting SEQUENCE: </strong>'.pg_last_error($c));
		
		$lob_handle = pg_lo_open($c, $lob_id, "w");    
    	pg_lo_write($lob_handle, $seq);
	    pg_lo_close($lob_handle);
	}					    	         		
       
   	$khoueiry = "INSERT INTO \"PROMOTER\" (\"STABLE_ID\",\"COMMENTS\", \"PROMOTER_ID\", \"NAME\", \"BEGIN\", \"END\", \"BASE_REG_ID\",\"TYPE\",\"DELETION\",\"MUTATION\",\"ADDITION\",\"PRIVATE\",\"CHECKED\",\"IS_NATURAL\") VALUES ('$new_stable_id', '$comments',currval('\"S_BIO_ID\"'),'$name',$begin,$end,$id,'$type','n','n','n','y','n','$is_natural');";  

	pg_query($c,$khoueiry) or die("<strong>Failed insertion in PROMOTER: </strong>".pg_last_error($c));
	                    
	//Copy all genes...
	$khoueiry="select * FROM \"GENE_REGULATION\" WHERE \"PROMOTER_ID\"=$id";
	$stmt = pg_query($c,$khoueiry);
			
	while($row=pg_fetch_array($stmt)){
	    $gene_id = $row["GENE_ID"];
	    $sure = $row["SURE"];
	    if(is_null($sure)){
		  $sure='n';
		}
	    
	    $khoueiry = "INSERT INTO \"GENE_REGULATION\" (\"PROMOTER_ID\", \"GENE_ID\", \"SURE\") VALUES (currval('\"S_BIO_ID\"'),$gene_id,'$sure');";
		
	    pg_query($c,$khoueiry) or die ("<strong>Failed insertion in GENE_REGULATION</strong>".pg_last_error($c));
	    
	}		  					  			
	  
	//Copy all motifs...	
	$requete="select * FROM \"MOTIF_COORD\" WHERE \"PROMOTER_ID\"=$id";
	$stmt = pg_query($c,$requete);
			
	while($row=pg_fetch_array($stmt)){
	    $idMotif = $row["MOTIF_ID"];
		$idCoord = $row["MOTIF_COORD_ID"];
		$seqMotif= $row["SEQUENCE"];
	    $beginMotif = $row["BEGIN"];
	    $comments = $row["COMMENTS"];
	    if(is_null($beginMotif)){
			$beginMotif = 0;
		}
	    $endMotif = $row["STOP"];	    
	    if(is_null($endMotif)){
			$endMotif = 0;
		}
				
		$khoueiry="INSERT INTO \"BIO_ENTITY\" (\"BIO_ID\", \"CURATED\" ) values ( nextval('\"S_BIO_ID\"'),'n')";	  	
		pg_query($c,$khoueiry) or die ("<p>Error Creating BIO_ENTITY</p>"); 
	    $khoueiry = "INSERT INTO \"MOTIF_COORD\" (\"MOTIF_COORD_ID\", \"MOTIF_ID\", \"PROMOTER_ID\", \"SEQUENCE\", \"BEGIN\", \"STOP\", \"COMMENTS\") VALUES (currval('\"S_BIO_ID\"'),$idMotif,$cur_prom_id,'$seqMotif',$beginMotif,$endMotif,'$comments');";
	    pg_query($c,$khoueiry) or die ("<strong>Failed insertion in MOTIF_COORD</strong>".pg_last_error($c));
		
		//Insert all MOTIF_COORD_GENES...		
		$khoueiry="select * FROM \"MOTIF_COORD_GENE\" WHERE \"MOTIF_COORD_ID\"=$idCoord";
		$stmt2 = pg_query($c,$khoueiry);
		$gene_ids = (array)null;
		while($row2=pg_fetch_array($stmt2)){
			$gene_ids[count($gene_ids)] = $row2["GENE_ID"];
		}		
		foreach($gene_ids as $gene_id){
			$khoueiry="INSERT INTO \"MOTIF_COORD_GENE\" (\"MOTIF_COORD_ID\", \"GENE_ID\") values (currval('\"S_BIO_ID\"'), $gene_id)";
			pg_query($c,$khoueiry) or die ("<strong>Failed insertion in MOTIF_COORD</strong>".pg_last_error($c));	
		}
		
	}  	
	
	$khoueiry="SELECT \"AUTHOR_ID\" FROM \"ENTRY_ORIGIN\" WHERE \"BIO_ID\"=".$id.";";
	$stmt = pg_query($c,$khoueiry);
    if (!$row=pg_fetch_array($stmt) ){
		echo "<strong>Error: Failed copying author information</strong>";
		include ("frames_footer.php");
		return;
    } else {
		$author = $row["AUTHOR_ID"];
		//COPY THE AUTHOR... but not the ANNOTATOR!
		$khoueiry="INSERT INTO \"ENTRY_ORIGIN\" (\"AUTHOR_ID\",\"BIO_ID\",\"ENTRY_DATE\",\"ANNOTATOR_ID\",\"ANNOTATION_DATE\") values ('$author',$cur_prom_id,current_date,".$_SESSION['curationauthor'].",current_date)";
		pg_query($c,$khoueiry) or die ("<strong>Error: copying Author information: </strong>".pg_last_error($c));
	}		 		
	
	pg_query($c, "COMMIT") or die ("<strong>Error: could not update the database: </strong>".pg_last_error($c));
	
	//edit the new region, if successfull			
	print '<META HTTP-EQUIV="Refresh" CONTENT="0; URL=reg-region-edit.php?id='.$cur_prom_id.'">'; 	 
			
	include ("frames_footer.php"); 

?> 