#!/perl -w

use strict;
use DBI;
use Win32::Daemon;

Win32::Daemon::StartService();

sleep (1) while(SERVICE_START_PENDING != Win32::Daemon::State());


Win32::Daemon::State(SERVICE_RUNNING);

#Connect to the Queue
my $queue = DBI->connect('dbi:ODBC:QUEUE','x','x',{AutoCommit=>1}) or 
+die "$!";

writeLogEntry(Win32::Daemon::State());

#Declare Daemon Variables
my $query;
my @queries;
my $sth;
my $status = Win32::Daemon::State();

while(1)
{    
    writeLogEntry("Waiting for Mining Request...\n");

    
    #Get the highest priority request that is not completed
    $query = q{SELECT MIN(priority),
                      ConfigFile
               FROM Queue 
               WITH (NOLOCK)
               WHERE Status <> ? AND
                     Status <> ?
               GROUP BY ConfigFile};
                
    $sth = $queue->prepare($query);
    $sth->execute("c","f");
    
    my @row = $sth->fetchrow_array;
    
    $sth->finish();
    
    #sleep if the priority is null (ie. the query returned the default
+ row)
    next if !$row[0];

    writeLogEntry("===========================================\n");
    
    writeLogEntry("Received request with priority: ".$row[0],"v");
    
    writeLogEntry("Retrieving Queries..","v");
    
    @queries = getBatchQueries($row[1]);
    
    #Get the queries to be ran
    if(@queries > 0)
    {    
        if(runBatchQueries(\@queries,\$queue))
        {
            $row[1] =~ m{([\w\d\.\+\-\=\(\)\%\^\$\#\@\!\~\`\']*)$};
            
            rename $row[1], "C:\\SQL_Server_Mining\\Queue\\Completed\\
+".$1;
            writeLogEntry("Completed Mining!\n");
            writeLogEntry("===========================================
+\n");
        }
    }
    else
    {
        writeLogEntry("Retrieval of Queries FAILED for ".$row[1]."!");
    }
    sleep(300);
}

Win32::Daemon::StopService();

#Runs queries specified in batch_list.txt
sub runBatchQueries
{
    #Get query list and db handle
    my ($queries,$q) = @_;

    my $client = shift @$queries;

    my $id = shift @$queries;
    
    my $query = q{SELECT DatabaseName  
                  FROM Databases
                  WITH (NOLOCK)
                  WHERE ClientCode = ?};
                  
    my $sth = $$q->prepare($query);              
    
    $sth->execute($client);
    
    my $dbname = $sth->fetchrow_arrayref;

    my $dbh = DBI->connect('dbi:ODBC:'.$$dbname[0],'x','x');
        
    my $rows = 0;
    
    #Get the values from localtime
    my @time = (localtime) [0..5];
        
    #Construct the date
    my $date_time = ($time[5] + 1900)."-".($time[4] + 1)."-".$time[3].
+"_".$time[2].".".$time[1].".".$time[0];

    mkdir "Q:\\Results\\$date_time";
    
    #Run each query
    foreach my $item(@$queries)
    {
        #Clean out query
        $query = undef;

        #remove the newline from the filename
        chomp $item;
        
        writeLogEntry("Loading ".$item."..");
        
        #Open the current file
        open(QUERY,"<".$item) || do{warn "Could not open $_::$!"; writ
+eLogEntry("$_ failed to open!");};
        
        #Read the file into a scalar for execution
        while(<QUERY>)
        {
            $query .= $_ . " ";
            chomp($query);
        }

        close QUERY;
        
        #Prepare the query
        $sth = $dbh->prepare($query);

        writeLogEntry("Executing ".$item."..","v");    
    
        #Execute the query
        $sth->execute();
        
        writeLogEntry("Writing results for ".$item."..","v");

        #grab the file name w/o extension
        $item =~ m{([\w\d\.\+\-\=\(\)\%\^\$\#\@\!\~\`\']+).sql$};

        #Open the results file
        open(OUTPUT,">C:\\SQL_Server_Mining\\Results\\".$date_time."\\
+".$1."_results.txt") || do{warn "Could not open ".$item."::".$!; writ
+eLogEntry($item." failed to open!");};
    
        $rows = 0;
        
        #Write the results
        while(my @row = $sth->fetchrow_array)
        {
                $rows++;
                
                #Set output delimiter to the |
                $,="|";
                
                #Remove trailing spaces
                foreach(@row)
                    {s/\s*$//g}

                print OUTPUT @row ,"\n";
        }

        #close the file
        close OUTPUT;
        
        #Output the number of rows affected
        writeLogEntry("Wrote ".$rows." results to ".$1."_results.txt."
+,"v");
    }
    
    ##UPDATE THE QUEUE##   
    $query = q{UPDATE Queue
               SET Status = ?
               WHERE RequestID = ?};
               
    $sth = $$q->prepare($query);
    
    $sth->execute("c",$id);
    ####################
    
    return 1;
}

#Gets the queries to be ran 
sub getBatchQueries
{
    my $file = shift;
    
      open(LIST,"<Q:\\Queue\\".$file) || do{return 0;};
 
     #Get the files to be executed
     my @queries = <LIST>;

    close LIST;
    
    #remove newlines and trailing spaces
    foreach(@queries)
    {
        chomp; 
        s/\s*$//g;
    }
    
    return @queries;
}

sub writeLogEntry
{
    #Get the parameters
    my ($entry,$mode) = @_;

    #Open the log file
    open(LOG,">>C:\\SQL_Server_Mining\\query_log.txt") || warn "Failed
+ to open query_log.txt see Administrator!\n";

    #Write the actual log entry
    print LOG  localtime()."::".$entry."\n";
    
    if($mode)
    {
        #If verbose mode is selected
        if(uc($mode) eq "V")
        {
            #echo the log entry
            print $entry."\n";    
        }
    }
    
    #Write dash seperator for next entry
    print LOG "-" for 0..50;

    #Write a newline
    print LOG "\n";

    close LOG;
}
[download]