I have spent several hours researching and experimenting (well groping) with this issue to no avail so I am going to ask for help (again).
The overall program logic:
1. Input $employeeID
2. Query mysql database for employee’s fname, lname, title, rank and supervisorID based on WHERE eid =’$employeeID’ statement
3. Print employee’s first name, last name, title and ranking
4. Return employee’s direct supervisorID as $next_level_supervisor
5. Call next_level_supervisor sub routine
The next_level_supervisor sub routine logic:
WHILE $next_level_supervisor ne ‘CEO’ {
1. Input $next_level_supervisor as argument to sub routine
2. Query database for $next_level_supervisor’s fname, lname, title, rank and (his/her) next_level_supervisor2 based on WHERE supervisor =’$next_level_supervisor’ statement
3. Print $next_level_supervisor first name, last name, title and ranking
4. Return next_level_supervisor2
}
Input next_level_supervisor2 as $next_level_supervisor to next_level_supervisor sub routine (what could go wrong? ;-)
The key challenge is the conversion of the $next_level_supervisor variable. When I approach the problem iteratively, I just change the variable name each time so the variable returned is $supervisor2, $supervisor3…. until there are no additional supervisors. (Although I can add a WHILE $supervisorX ne ‘CEO’ the program seems to close properly even though I iterate through 10 supervisors without that WHILE condition).
How to I differentiate the input $next_level_supervisor from the output $next_level_supervisor? Somehow I have to successfully convert the $next_level_supervisor2 value into the $next_level_supervisor as input to the sub routine. I’m stumped.
Here is the revised iterative code that works:
use strict;
use warnings;
use DBI;
use DBD::mysql;
use Spreadsheet::WriteExcel;
our $lname;
our $fname;
our $title;
our $rank;
our $eid;
our $employee ='employeeID';
our $supervisor;
our $supervisor2;
our $supervisor3;
our $supervisor4;
our $supervisor5;
our $supervisor6;
our $supervisor7;
our $supervisor8;
our $supervisor9;
my $row=23;
sub add_aref_to_sheet{
my ($aref, $worksheet0,) =@_;
my ($fname, $lname, $title, $rank, $supervisor) = @$aref;
#write data to screen & spreadsheet row by row
#print captured output
print "$fname\n";
print "$lname\n";
print "$title\n";
print "$rank\n";
print "\n";
print "=====================\n";
print "\n";
$worksheet0->write($row++, 2, $fname, $format_top2
+);
$worksheet0->write($row++, 2, $lname, $format_midd
+le);
$worksheet0->write($row++, 2, $title, $format_midd
+le);
$worksheet0->write($row++, 2, $rank, $format_botto
+m);
$worksheet0->write($row++, 2, $supervisor, $format
+_bottom);
$row++;
return $supervisor;
}
#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor
+FROM acnse WHERE eid ='$employee';");
$sth->execute or die $sth->errstr;
while ( my $aref = $sth ->fetchrow_arrayref)
{$supervisor2 = add_aref_to_sheet($aref, $worksheet0);
#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor
+FROM acnse WHERE eid ='$supervisor2';");
$sth->execute or die $sth->errstr;
while ( my $aref = $sth ->fetchrow_arrayref)
{$supervisor3 = add_aref_to_sheet($aref, $worksheet0);
#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor
+FROM acnse WHERE eid ='$supervisor3';");
$sth->execute or die $sth->errstr;
while ( my $aref = $sth ->fetchrow_arrayref)
{$supervisor4 = add_aref_to_sheet($aref, $worksheet0);
#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor
+FROM acnse WHERE eid ='$supervisor4';");
$sth->execute or die $sth->errstr;
while ( my $aref = $sth ->fetchrow_arrayref)
{$supervisor5 = add_aref_to_sheet($aref, $worksheet0);
#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor
+FROM acnse WHERE eid ='$supervisor5';");
$sth->execute or die $sth->errstr;
while ( my $aref = $sth ->fetchrow_arrayref)
{$supervisor6 = add_aref_to_sheet($aref, $worksheet0);
#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor
+FROM acnse WHERE eid ='$supervisor6';");
$sth->execute or die $sth->errstr;
while ( my $aref = $sth ->fetchrow_arrayref)
{$supervisor7 = add_aref_to_sheet($aref, $worksheet0);
#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor
+FROM acnse WHERE eid ='$supervisor7';");
$sth->execute or die $sth->errstr;
while ( my $aref = $sth ->fetchrow_arrayref)
{$supervisor8 = add_aref_to_sheet($aref, $worksheet0);
#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor
+FROM acnse WHERE eid ='$supervisor8';");
$sth->execute or die $sth->errstr;
while ( my $aref = $sth ->fetchrow_arrayref)
{$supervisor9 = add_aref_to_sheet($aref, $worksheet0);
}
}
}
}
}
}
}
}