#!/perl -w

use strict;
use Win32::Daemon; 

my %service = (name    =>  'test',
	           display =>  'test',
	           path    =>  'c:\\Perl\\bin\\perl.exe',
	           pwd     =>  '',
	           parameters =>'q:\\test.pl',);
if( Win32::Daemon::CreateService( \%service ) )
{
	print "Successfully added.\n";
}
else
{
	print "Failed to add service: " . Win32::FormatMessage( Win32::Daemon::GetLastError() ) . "\n";
}

##</code><code>##

use Win32::Daemon;

Win32::Daemon::StartService();

while(SERVICE_STOPPED != Win32::Daemon::State()) 
     {sleep(1);}

Win32::Daemon::State( SERVICE_RUNNING );

##</code><code>##

BEGIN {
    open( STDERR, ">>c:/daemon.err" );
    warn "$0 started ".localtime().$/;
}
##</code><code>##

#!/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 $_::$!"; writeLogEntry("$_ 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."::".$!; writeLogEntry($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;
}