#!/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;}
}
}
}
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE