finhagen:
Sure thing. Just take it step by step. First, notice that your code is structured something like this:
initialization
execute an SQL statement
while (my $aref=$sth->fetchrow_arrayref) {
... write to spreadsheet ...
prepare & execute an SQL statement
while (my $aref=$sth->fetchrow_arrayref) {
... write to spreadsheet ...
prepare & execute an SQL statement
while (my $aref=$sth->fetchrow_arrayref) {
... write to spreadsheet ...
prepare & execute an SQL statement
while (...) {
<<< same thing for several more levels
}
}
}
}
The first thing you need to do is identify a repeated chunk of code that's relatively easy to separate from the surrounding code. There are several things you might choose, but the one that jumped out at me was the code that converts the array reference into a set of variables and then writes the variables to a worksheet.
It's easy to separate from the surrounding code because there's only three variables on the input side ($aref, $worksheet0 and $row) and one variable on the output side ($supervisor). Since the $row is going to change everywhere, and since it would be pain to have to pass it around and return it everywhere, I decided to leave it a global variable. So what I wound up with is this:
sub add_aref_to_sheet {
my ($aref, $worksheet0) = @_;
my ($fname, $lname, $title, $supervisor) = @$aref;
#write data to spreadsheet row by row
$worksheet0->write($row++, 2, $fname, $format_HRtop2);
$worksheet0->write($row++, 2, $lname, $format_HRmiddle);
$worksheet0->write($row++, 2, $title, $format_HRmiddle);
$worksheet0->write($row++, 2, $supervisor, $format_HRbottom);
$row++;
return $supervisor;
}
We return the supervisor variable so you can use it in the next level. When you pull that code out into a subroutine, your code simplifies a good bit:
our $lname;
our $fname;
our $title;
our $eid;
our $supervisor;
our $supervisor2;
our $supervisor3;
our $supervisor4;
our $supervisor5;
our $supervisor6;
our $supervisor7;
our $supervisor8;
#select fname, lname, csg from acnse where supervisor ='pierre.nanterm
+e';
my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FROM a
+cnse WHERE eid ='employeID';");
$sth->execute or die $sth->errstr;
my $row=23;
while ( my $aref = $sth ->fetchrow_arrayref)
{
$supervisor2 = add_aref_to_sheet($aref, $worksheet0);
my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FRO
+M acnse WHERE eid ='$supervisor2';");
$sth->execute or die $sth->errstr;
while ( my $aref = $sth ->fetchrow_arrayref)
{
$supervisor3 = add_aref_to_sheet($aref, $worksheet0);
my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor
+FROM acnse WHERE eid ='$supervisor3';");
$sth->execute or die $sth->errstr;
while ( my $aref = $sth ->fetchrow_arrayref)
{
$supervisor4 = ....
... continue for several more levels ...
}
}
}
sub aref {
... same as shown earlier ...
}
There are a good few tuneups you can do to this program. You might want to read up on placeholders in DBI. That can simplify your code a bit, too. Then, depending on how many supervisors a person may have, whether you want to go as many levels as required without hardcoding, etc., there are several ways you could go from here. I'll stop here, as I don't want to take *all* the fun out of it! ;^) But I *will* provide a hint: If each person has only one supervisor, you can turn the successive level of while loops into a simple loop. Give it a try and let me know if you run into any stumbling blocks.
...roboticus
When your only tool is a hammer, all problems look like your thumb.
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.