#! perl -w scipt

use strict;
use warnings;
use DBI;

use DBD::ODBC;


use File::Copy;

#use vars qw(@row_) = ();


my ($data_source, $database, $user_id, $password) = qw(ip_address database_name user_name password ); 
my $conn_string = "driver={SQL Server}; Server=$data_source; Database=$database; Trusted_Connection=yes"; 



my $dbh = DBI->connect( "DBI:ODBC:$conn_string" ) or die $DBI::errstr;



 my $inputfile = $ARGV[0];
 my $outputfile = $ARGV[1];




my $Request_id;	

my $Variable_number;	
my $Variable;
my $Choice;

my @VARIABLE;
my @CHOICE;

my $Command; 

my $Return_results;


my $SPROC;
my $unique;

my $elements_in_array;


my $Component_number_old = 1;
my $Component_number = 1;



#my $flat_file = "Flat_file_test_C.txt";


my $flat_file = $inputfile;


open (FLAT_FILE, "<$flat_file");



while (<FLAT_FILE>){

  chomp;
  s/"//g;   # This " is generated by excel when there is a comma in the line that is printed by excel

  
  $Component_number_old = $Component_number;
  

  if ($_ =~ /^$/) {

    last;

  }
  elsif ($_ =~ /SPROC\sName\:\s([a-z|A-Z|0-9|_]{1,100})/){
    print "Hi_A\n";
    $SPROC = $1; 
  }

  elsif ($_ =~ /^\*(.{36});(\d{1,5});(\d{1,5});([a-z|A-Z|0-9|_]{1,50});(.{1,150})/){
   
    print "$_\n";
    push (@VARIABLE, $Variable);
    push (@CHOICE, $Choice);
    print "Hi_B\n";
    $unique = $1;
    $Component_number = $2;
    $elements_in_array = $3;
    $Variable = $4;

    $Choice = $5;

   
  }

  elsif ($_ =~ /^\*(.{36})\;(\d{1,5})\;(\d{1,5})\;([a-z|A-Z|0-9|_]{1,50})\;$/){
    print "$_\n";
    push (@VARIABLE, $Variable);
    push (@CHOICE, $Choice);
    print "Hi_B\n";
    $unique = $1;
    $Component_number = $2;
    $elements_in_array = $3;
    $Variable = $4;

    $Choice = "NULL";

  }


  
    if ($_ =~ /^\sEND/){
      
      print "Hi_E\n";
      push (@VARIABLE, $Variable);
      push (@CHOICE, $Choice);
      
      
      $Command = join(' ', 
                'EXEC', 
                 $SPROC, 
                 join(', ', 
                         @CHOICE[1 .. $elements_in_array])) . ';';
      
      my $Request_id = $CHOICE[$elements_in_array];


      print "$Command\n";
      Got_Command($Command, $Request_id, $outputfile);

      undef @CHOICE;

      my @CHOICE;

    }

}


close FLAT_FILE;






sub Got_Command  {

 



  my($Command_B,$Request_id_B, $outputfile_B) = @_;



open (OUTPUT_FILE, ">>", $outputfile_B)
  or die "Unable to open $outputfile: $^E\n";


  print "Hi_F\n";

  print "$outputfile\n";

 

 


    
    print "$Command_B\n";
  
  
  my $sthB_A =  $dbh->do($Command_B) or die "Couldn't do query: ".$dbh->errstr;







  my $Return_results = "Select DISTINCT Colls FROM Database";

  print "$Return_results";
  

  my $sth_C = $dbh->prepare($Return_results) or die "Couldn't prepare query: ".$dbh->errstr;
  
  $sth_C->execute() or die "Couldn't execute query: ".$sth_C->errstr;
  


  while (my @row = $sth_C->fetchrow_array) {


    print "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n";
    print  join("\t", @row);
    print  "\n";
    print "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n";

    print OUTPUT_FILE join("\t", @row);
    print OUTPUT_FILE "\n";

    undef @row;

  }

}