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_middle);
					$worksheet0->write($row++, 2, $title, $format_middle);
					$worksheet0->write($row++, 2, $rank, $format_bottom);
					$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);
				
			}
		}
	}
}
}
}
}
}