#!perl
use DBI;
use strict;

my $dbh = DBI->connect('dbi:ODBC:driver=Microsoft Access Driver (*.mdb, *.accdb);
dbq=D:\Project\Project\BSC_KPIMonitoring\CHN\E_BSC_KPIMonitorDB.mdb');

my @tables = (
  'SDCCHBLOCKING',
  'SDCCHDROP',
  'TCHBLOCKING',
  'TCHDROP', );

  my %abbr = (
  'SDCCHBLOCKING' => 'SB',
  'SDCCHDROP'     => 'SD',
  'TCHBLOCKING'   => 'TB',
  'TCHDROP'       => 'TD', );
  
my $regex = join '|',@tables;

# get all data
my %db=();
for my $tbl (@tables){
  $db{$tbl} = $dbh->selectall_arrayref("SELECT NE,* FROM $tbl");
}

my $sql = "SELECT * FROM KPI_Master";
my $sth = $dbh->prepare($sql);
$sth->execute;

while ( my ($bsc,$kpi,$thresh) = $sth->fetchrow_array() ){
  
  # search table corresponding to kpi
  if ($kpi =~ /($regex)/){
    my $tbl  = $1;
    for ( @{$db{$tbl}} ){
	  # select records with correct NE value in first column
      if (( $_->[0] eq $bsc ) && ( $_->[3] > $thresh )){
        print "$abbr{$tbl},$bsc,$thresh,/".$_->[3]."\n";
      }
    }
  }
}