#!c:\perl\bin\perl.exe

use lib "C:\\Perl";
use DBI;
use DBD::mysql;

# # # # # # # # # # # #
#Variable declarations#
# # # # # # # # # # # #

my @tblname;

###########
#DB1 START#
###########
my ($user, $pass) = ("root", "pass"); #credentials for SQL database
my $table_data = q/DBI:mysql:database=information_schema;host=localhost;port=13308/;
#Connect to the data source and get a handle for that connection
my $dbtbl = DBI->connect($table_data, $user, $pass) || die "Can't connect to $data_source: $DBI::errstr";

my $tblname = "SELECT table_name FROM `TABLES` where table_name like 'event\_%'";
my $sthtbl=$dbtbl->prepare($tblname);
$sthtbl->execute();
while ( @row = $sthtbl->fetchrow_array ) {
	my $table_name = $row[0];
	push(@tblname, $table_name); 
}
$sthtbl->finish;
$dbtbl->disconnect;

###########
#DB2 START#
###########
my $data_source = q/DBI:mysql:database=probedb;host=localhost;port=13308/;
#Connect to the data source and get a handle for that connection
my $dbh = DBI->connect($data_source, $user, $pass) || die "Can't connect to $data_source: $DBI::errstr";
#open handle for error/log file

foreach (@tblname){
	$table_name = $_;
	my $NCquery= "SELECT id, text FROM ‘$table_name’ WHERE alarmcode LIKE '%Trap%Calltouch%'";
	my $sth=$dbh->prepare($NCquery);
	$sth->execute();
	while ( @row = $sth->fetchrow_array ) { #error here#
		$NCid = $row[0];
		$NCsevno = $row[1];
		if  ($NCsevno =~ /alarmState=(\d)/i){
			$newstat = 1 if $1 == 2;
			$newstat = 5 if $1 == 1;
			$newstat = 3 if $1 == 3; #ack?#
		}
		my $updateNC="UPDATE '$table_name' SET severity = '$newstat' WHERE id = '$NCid' AND modtime > '$ntime'";
		my $sth=$dbh->prepare($updateNC);
		$sth->execute();
	}
	print "$table_name\n";
}

my $query="SELECT name FROM managedobject WHERE displayname like '%TSEPAL%-CT%' AND type LIKE '%Windows%'";
my $sth=$dbh->prepare($query);
$sth->execute();
while ( @row = $sth->fetchrow_array ) {
	$id = $row[0];
	#update all matching alarms to newest severity
	&update();
}
$sth->finish;
$dbh->disconnect;

sub update {
	my $alquery="SELECT source, severity, ttime, text FROM '$table_name' WHERE source = '$id' AND alarmcode LIKE '%Trap%Calltouch%'";
	my $sth=$dbh->prepare($alquery);
	$sth->execute();
	while ( @row = $sth->fetchrow_array ) { #error here#
		$NCsource = $row[0];
		$NCsev = $row[1];
		$NCmodtime = $row[2];
		$NCmmessage = $row[3];
		if  ($NCmsg =~ /alarmDescription=([^\s+])/i){
			$NCmmessage = $1;
		}
		my $updateNC="UPDATE '$table_name' SET severity = '$NCsev' WHERE source = '$NCsource' AND ttime < '$NCmodtime' and text like '%$NCmmessage%' and alarmcode LIKE '%Trap%Calltouch%'";
		my $sth=$dbh->prepare($updateNC);
		$sth->execute();
	}
	my $query="SELECT severity FROM alert WHERE source = '$id' ORDER BY severity ASC LIMIT 1";
	my $sth=$dbh->prepare($query);
	$sth->execute();
	while ( @row = $sth->fetchrow_array ) {
		$sev = $row[0];
	}
	my $updatemo="UPDATE managedobject SET status = '$sev' WHERE name = '$id'";
	my $sth=$dbh->prepare($updatemo);
	$sth->execute();
}