asked on
#!/usr/bin/perl5.10 -w
use warnings;
use Spreadsheet::WriteExcel::Big;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use strict;
use IO::Scalar;
use Data::Dumper;
use DBI;
use DBD::MSDB2;
my @area_id = 6;
my @upemplid;
my $sql_parser = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("DataSource_Information.xls");
my $sql_worksheet = $sql_workbook->worksheet("Connection");
my ( $sql_row_min, $sql_row_max ) = $sql_worksheet->row_range();
foreach my $area_id (@area_id) {
print "\nNow processing AREA_ID $area_id\n";
for my $sql_row ($sql_row_min+1 .. $sql_row_max) {
my $status_cell = $sql_worksheet->get_cell($sql_row, 2)->value;
next unless defined $status_cell;
if ($status_cell eq "Y") {
my $server = $sql_worksheet->get_cell($sql_row, 1)->value;
next unless defined $server;
# print "The server value is $server\n\n";
chomp $server;
$server =~ s/\$server/$server/;
my $sel_dbh = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
die "Can't connect to database: $DBI::errstr";
my $tr_dbh = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
die "Can't connect to database: $DBI::errstr";
my $tr_dbh1 = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
die "Can't connect to database: $DBI::errstr";
my $tr_dbh2 = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
die "Can't connect to database: $DBI::errstr";
my $tr_dbh3 = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
die "Can't connect to database: $DBI::errstr";
my $sel_dbh2 = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
die "Can't connect to database: $DBI::errstr";
my $tr_sql = "insert into pfnd.AREA_INFO (AREA_ID,EMPL_ID_UP,EMPL_NME_UP,EMPL_TYPE_UP, IGNORE_UP,EMPL_FLAG) select distinct sv.AREA_ID,sv.EMPL_ID_UP,sv.EMPL_NME_UP,sv.EMPL_TYPE_UP,sv.IGNORE_UP,'N' from PFND.AREA_SOURCE_VW sv where sv.AREA_ID = $area_id";
$tr_sql =~ s/\$area_id/$area_id/;
$tr_dbh->do($tr_sql);
my $sel_sql = "select distinct fsi.EMPL_ID_UP FROM AREA_INFO fsi where fsi.EMPL_TYPE_UP = 'CONTR' and fsi.EMPL_FLAG = 'N'";
my $sel_sth = $sel_dbh->prepare($sel_sql);
$sel_sth->execute();
while (my @row = $sel_sth->fetchrow_array()) {
push @upemplid, @row;
}
if(grep(/ /, @upemplid) == @ upemplid) { push @ upemplid, 0};
do {
foreach my $upemplid(@upemplid) {
print "The current distinct upstream empl id is $upemplid\n";
my $tr_sql3 = "update AREA_INFO fsi set fsi.EMPL_FLAG = 'N' where fsi.EMPL_ID_UP = $upemplid and fsi.EMPL_TYPE_UP = 'CONTR'";
$tr_sql3 =~ s/\$upemplid /$upemplid /;
$tr_dbh3->do($tr_sql3);
my $tr_sql1 = "insert into AREA_INFO (AREA_ID,EMPL_ID_UP,EMPL_NME_UP,EMPL_TYPE_UP,IGNORE_UP,EMPL_FLAG) select distinct sv.AREA_ID,sv.EMPL_ID_UP,sv.EMPL_NME_UP,sv.EMPL_TYPE_UP,sv.IGNORE_UP,'N' from AREA_SOURCE_VW sv where sv.EMPL_ID_DOWN in (select distinct fsi.EMPL_ID_UP FROM AREA_INFO fsi where fsi.EMPL_TYPE_UP = 'CONTR' and fsi.EMPL_FLAG = 'N')";
$tr_dbh1->do($tr_sql1);
my $tr_sql2 = "update AREA_INFO fsi set fsi.EMPL_FLAG = 'Y' where fsi.EMPL_ID_UP = $upemplid ";
$tr_sql2 =~ s/\$upemplid /$upemplid/;
$tr_dbh2->do($tr_sql2);
}
@upemplid = ();
my $sel_sql2 = "select distinct fsi.EMPL_ID_UP FROM AREA_INFO fsi where fsi.EMPL_TYPE_UP = 'CONTR' and fsi.EMPL_FLAG = 'N'";
my $sel_sth2 = $sel_dbh2->prepare($sel_sql2);
$sel_sth2->execute();
while (my @row = $sel_sth2->fetchrow_array()) {
print "@row\n";
push @upemplid, @row;
}
} while ("@upemplid " ne "0");
if(grep(/ /, @upemplid) == @upemplid) { push @upemplid, 0};
if ("@upemplid " eq "0") {exit;}
}
}
}