#!/usr/bin/perl -w

use strict;
use DBI;
use Term::ReadKey;

#Declarations

my ($File, $Entry, $username, $password, $entry, $dsn, $resultfile, $dbserver, @dbserverlist, $dbh, @data, $data, $sth);
my ($sp, $output, @splist, $command, @mtable, $mtable, @databases, $database, @rtable, $rtable, $sth2, $sth3, @tablesps);
my (%attr, $tablesp, @names, $name, $sth4, $sth5, $fh, $dsn2, $dbh2, $dsn3, $dbh3);

print "What is the username you would like to use to connect? (e.g. 'sa')\n";
print "-->";
$Entry = <STDIN>;
chomp($Entry);
$username = $Entry;
print "\n";
print "What is the password? (Password will not show on the console as you type.)\n";
ReadMode 'noecho';
print "-->";
$Entry = <STDIN>;
chomp($Entry);
$password = $Entry;
ReadMode 'restore';
print "\n";
open(ERRFILE, ">>script\\Output\\Errorlog.txt") or print "An error log file could not be created.  The error given is: $!\n";
open(INFILE, "Script\\Input\\DB_Server_List.txt") or print "The input file could not be opened. The error given is: $!\n";
@dbserverlist = <INFILE>;
%attr = (PrintError => 0, RaiseError => 0);

DB: foreach $dbserver (@dbserverlist) {
	#Create DB handle
	chomp $dbserver;
	$dsn = "driver={SQL Server};Server=$dbserver;";
	$dbh = DBI->connect("dbi:ODBC:$dsn", $username, $password, \%attr) or print ERRFILE join("\t", $dbserver, $DBI::errstr, "\n", "\n") and next DB;
	print "Connected to $dbserver.\n  Running data scripts...\n";
	#Run subs 
	storedprocedures ($dbserver);
	#mastertables ($dbserver);
	#tabsps ($dbserver);
	#regtables ($dbserver);
	#$dbh->disconnect;
	SKIP:
	print "  Complete.\n";
	}
$password = "";
close(INFILE);
close(ERRFILE);
exit;

sub storedprocedures {
	open(INFILE, "script\\Input\\Master_Level_Stored_Procedures.txt") or print "The input file could not be opened.  The error given is: $!\n";
	@splist = <INFILE>;
	my $dbserver=shift;
	SP: foreach $sp (@splist) {	
		chomp $sp;
		#Open output file
		$output = 'Script\\Output\\' . $sp . '.txt';
		open(OUTFILE, ">>$output") or warn "The Output file could not be opened.";
		binmode(OUTFILE, ":utf8");
		#Send commands
		$command = 'exec ' . $sp;
		print "$command. \n";
		$sth = $dbh->prepare("$command");
		$sth->execute or print ERRFILE join("\t", $dbserver, "$DBI::errstr", $sp, "\n") and next SP;
		while(@data = $sth->fetchrow_array) { 
			foreach (@data) {
  				$_ = 'Nada' unless defined;
				}
			print OUTFILE join("\t", $dbserver, @data), "\n";
			}
		close(OUTFILE);
		}
	close(INFILE);
	}