#!/usr/local/bin/perl 


use strict;
use warnings;

use DBI;
use DBD::mysql;


my $dbh = DBI->connect('DBI:mysql:e:XXX.XXX.XXX.XX', 'x.xxxxxxx', 'xxxxxx')
    or die "Couldn't connect to database: " . DBI->errstr;


sub retrieve_ESLIM_codes {

    my $Multiple_lines = "Lines_string.txt";
    my $lines_one_col = "List_of_lines_relating_to_particular_gene_knockouts.txt";
    

    my $sql_A = "SELECT DISTINCT parameter_id FROM e.measurement WHERE parameter_id LIKE 'M%' OR parameter_id LIKE 'G%' OR parameter_id LIKE 'E%' AND parameter_id NOT LIKE '%_________8__' ORDER BY parameter_id;";

    my $Output_A = "List_parameters_common_to_centers.txt";  
    
    open (OUTFILE_A, ">>$Output_A") || die "Error opening outfile.$!,stopped";

    
    my $sth_A = $dbh->prepare($sql_A) or die "Cannot prepare: " . $dbh->errstr();
    $sth_A->execute() or die "$sth_A->errstr\n";
    
    my @row_A; 
    my @parameters_A; 
    my @record_A; 
    
    while(@row_A = $sth_A->fetchrow_array()) {
	@record_A = @row_A;
	push(@parameters_A, @record_A);
	print OUTFILE_A "@record_A\n";    
    }
    
    $sth_A->finish();
    
    my @sorted_parameters_A = sort { $a cmp $b } @parameters_A ;
    
    return (@sorted_parameters_A);
}




sub look_through_file ($$) {   
    
    my ($start_point, $continue_tag) = @_;   

    my $entry_no_new = $start_point;  # entry number new
    my @array_of_lines;
    
    
    my $Line_input = "List_of_lines_relating_to_particular_gene_knockouts.txt";
        
    open (LINE_INPUT, "<$Line_input") || die "Error opening outfile.$!,stopped";
    print "I am here A\n";
    
  LINE: while (<LINE_INPUT>){
      
      print "I am here B\n";
      chomp;
      
      my $entry_no_old = $entry_no_new;   
      
      if ($_ =~ /^(\d{1,3})\t(\d{3,5})/){

	  next LINE if $1 < $start_point;
	  print "I am here C\n";
	  $entry_no_new = $1;
	  
	  print "Entry no new:  ".$entry_no_new."\n";    
	  
	  my $line = $2;
	  
	  push (@array_of_lines, $entry_no_new);
	  	  
	  last LINE if ($entry_no_new > $entry_no_old);
	  	  
      }
      else {
	  
	  $continue_tag = "FALSE";
	  	  
      }      
  }
    print @array_of_lines;
    print "\n";
    
    close (LINE_INPUT); 
    return (@array_of_lines, $start_point, $entry_no_new, $continue_tag);
}






sub create_output (\@$)  {
    
    my (@array_of_lines, $entry_no_new) = @_;   
    
    print "\n\n$entry_no_new";
    
    mkdir "Output";
    
    my $Line_set_Output_A = 'Output/Line_set_Output_A_'.$entry_no_new.'.txt';        
    
    
    open (LINE_SET_OUTPUT_A, ">>$Line_set_Output_A") || die "Error opening outfile.$!,stopped";
        
    my $sql_command_A = "CREATE TEMPORARY TABLE Temp_Table ( Key_m INT NOT NULL AUTO_INCREMENT, Value  INT NOT NULL)";
    
    my @insert_statements;
    
    print LINE_SET_OUTPUT_A @array_of_lines;
    print LINE_SET_OUTPUT_A "\n";

    foreach (@array_of_lines){

	my $element_line = $_;
	my $sql_command_B = 'INSERT INTO Temp_table (Line) VALUES ('.$element_line.');';  # uninitialized value $element_line ???????? 

	print  LINE_SET_OUTPUT_A "$sql_command_B\n";
    }
    
    my $sql_command_C = "DROP TABLE Temp_table\n"; 

    my $sql_command_D = "SELECT * from eur.annotation WHERE Line IN (SELECT Line FROM #Temporary_table) AND entity_name LIKE '_%' ORDER BY evidence_code;";  
    
    print LINE_SET_OUTPUT_A "$sql_command_D\n";

    my $dbh;
    
    my $sth_A = $dbh->prepare($sql_command_D) or die "Cannot prepare: " . $dbh->errstr();
    $sth_A->execute() or die "$sth_A->errstr\n";
        
    while(my @row_A = $sth_A->fetchrow_array()) {

	print LINE_SET_OUTPUT_A @row_A;
	print LINE_SET_OUTPUT_A "\n";

	my @record_A = @row_A;
	push(my @fields_A, @record_A);
	
    }
    $sth_A->finish();
}




retrieve_ESLIM_codes();



my $starting_point = 0;
my $continue_tag = "TRUE";

my @array_of_lines;
my $entry_no_new;




while ($continue_tag eq "TRUE"){
    
    look_through_file ($starting_point, $continue_tag);
    create_output     (@array_of_lines, $entry_no_new);

}