############################
#
#   Set the Perl Modules
#
############################

use strict;
use DBI;
use Spreadsheet::WriteExcel::Big;
use Mail::Sender;

my $i = 0;
my $j = 1;

##################################
#
#  Connect to Oracle database.
#
###################################  

my $connection = DBI->connect('dbi:Oracle:xxxx','xxxx','xxxx',{
    AutoCommit => 0,
    RaiseError => 1,
    PrintError => 1,
    }) 
|| die "Database connection not made: $DBI::errstr";


############################
#
#   Set up Query for Store
#
############################

my $stmt_1 = "select alignment from rd_list"; 

############################
#
#   Prepare Query
#
############################

my $query = $connection->prepare($stmt_1); 


############################
#
#   Execute Query
#
############################

$query->execute() or die $connection->errstr;

###############################
#
#   Declaration.
#
###############################

my ($alignment);

############################################
#
#   binds each column to a scalar reference
#
############################################

$query->bind_columns(undef,\$alignment);

###############################
#
#   Create Array_ref for array.
#
###############################

my $rows = $query->fetchall_arrayref;

###############################
#
#   Load data in array.
#
###############################

foreach $i(0..$#{$rows})
{
   foreach $j (0..$#{$rows->[$i]} ) 
   {
    $rows->[$i][$j];
   }
}

my $num = $query->rows;

print "\nThis process returns $num of Rd's.\n\n";


$query->finish();
$connection->rollback;
$connection->disconnect();


######################################################################
+################################################



while ($i<=2) 
{
my $placeholder = ":p_$j";

print "$placeholder\n";

my $rd = "$rows->[$i][0]";

print "$rd\n";

################################
#
#   create a new EXCEL instance
#
################################

my $Excelfile = "Q4 $rd PERFORMANCE RPT.xls"; 
my $excel = Spreadsheet::WriteExcel::Big->new("$Excelfile");
my $worksheet = $excel->addworksheet("Q4_STORE");

######################################################################
+#######################
#
#  Start Store process...
#
######################################################################
+#######################

########Create Worksheet format for first worksheet########

################################
#
#   format worksheet for Headers
#
#################################

my $headings = $excel->add_format();
$headings->set_bold();
$headings->set_color('white');
$headings->set_bg_color('blue');
$headings->set_align('top');
$headings->set_text_wrap();

####################################
#
#   format / merge cells for Header
#
#####################################

$worksheet->merge_range('A1:D1',"4th Quarter District Manager Quarterl
+y Performance Incentive!",$headings);
$worksheet->merge_range('A2:D2'," ", $headings);
$worksheet->merge_range('A3:D3', "Period Measured: 10-01-2008 through 
+12-31-2008", $headings);
$worksheet->merge_range('A4:D4', " ", $headings);
$worksheet->merge_range('A5:D5', "Growth:", $headings);
$worksheet->merge_range('A6:D6', "plus 18 Customers OR", $headings);
$worksheet->merge_range('A7:D7', "plus 27 BOR OR", $headings);
$worksheet->merge_range('A8:D8', "plus 3300 in SMRR", $headings);
$worksheet->merge_range('A9:D9', " ", $headings);
$worksheet->merge_range('A10:D11', "Each store that meets one of the g
+rowth goals must also achieve a 5.9% average for Q4.", $headings);

########################################
#
#   set column widths for Column Headers
#
#########################################

$worksheet->set_column('A:A',16.57);
$worksheet->set_column('D:D',23.71);
$worksheet->set_column('F:F',16.71); 
$worksheet->set_column('G:G',16.29); 
$worksheet->set_column('H:H',15.29);
$worksheet->set_column('I:I',14.86);
$worksheet->set_column('J:J',16.29);
$worksheet->set_column('K:K',15.86);
$worksheet->set_column('L:L',26.86);
$worksheet->set_column('M:M',19.00);

########################################
#
#   format worksheet for Column Headers
#
#########################################

my $col_headings = $excel->add_format();
$col_headings->set_bold();
$col_headings->set_size(10);
$col_headings->set_color('white');
$col_headings->set_align('center');
$col_headings->set_bg_color('blue');
$col_headings->set_border(2);
$col_headings->set_shrink();

################################
#
#  Write Heading(s) to worksheet
#
################################

$worksheet->write(13, 0, "WEEK_END_DATE", $col_headings);
$worksheet->write(13, 1, "SVP", $col_headings);
$worksheet->write(13, 2, "RD",  $col_headings);
$worksheet->write(13, 3, "DM", $col_headings);
$worksheet->write(13, 4, "STORE", $col_headings);
$worksheet->write(13, 5, "WTD_SMRR_GAIN", $col_headings);
$worksheet->write(13, 6, "QTD_SMRR_GAIN", $col_headings);
$worksheet->write(13, 7, "WTD_BOR_GAIN", $col_headings);
$worksheet->write(13, 8, "QTD_BOR_GAIN", $col_headings);
$worksheet->write(13, 9, "WTD_CUST_GAIN", $col_headings);
$worksheet->write(13, 10, "QTD_CUST_GAIN", $col_headings);
$worksheet->write(13, 11, "WTD_CARD_CLOSED", $col_headings);
$worksheet->write(13, 12, "QTD_AVG_CARD_CL", $col_headings);

######################
#
# Freeze panes
#
######################

$worksheet->freeze_panes(14,5);

############################
#
#  format rest of data data
#
############################

my $format = $excel->add_format();
$format->set_size(10);
$format->set_align('center');
$format->set_border(1);
$format->set_num_format('General;[Red](-General);General');


########Create Function / Subroutine to Get Data and insert into Works
+heet########

sub get_store_data {

##################################
#
#  Connect to Oracle database.
#
###################################  

my $dbh = DBI->connect('dbi:Oracle:xxxx','xxxx','xxxx',{
    AutoCommit => 0,
    RaiseError => 1,
    PrintError => 1,
    }) 
|| die "Database connection not made: $DBI::errstr";


#DBI-> trace(2);

############################
#
#   Set up Query for Store
#
############################

my $stmt = "select 
                  week_end_date, SVP, RD,
                  DM, store, wtd_smrr_gain,QTD_SMRR_GAIN,
                  wtd_bor_gain,QTD_BOR_GAIN,
                  wtd_cust_gain,QTD_CUST_GAIN,
                  WTD_CARD_CLOSED,QTD_AVG_CARD_CL
             from 
                   bonus_4Q_store
             where
                   SVP <> 'RD' and RD = ?
             order by
                   svp,dm,store";   
    
        
############################
#
#   Prepare Query
#
############################

my $sth = $dbh->prepare($stmt);


############################
#
#   Bind Parameters.
#
############################

#$sth->bind_param($placeholder, $rd);

############################
#
#   Execute Query
#
############################

$sth->execute($rd) or die $dbh->errstr; 

###############################
#
#   Declaration.
#
###############################

#my ( $week_end_date,$SVP,$RD,$DM,$store,
#    $wtd_smrr_gain,$QTD_SMRR_GAIN,
#    $wtd_bor_gain,$QTD_BOR_GAIN,
#    $wtd_cust_gain,$QTD_CUST_GAIN,
#   $WTD_CARD_CLOSED,$QTD_AVG_CARD_CL);


############################################
#
#   binds each column to a scalar reference
#
############################################

#$sth->bind_columns(undef,\$week_end_date,\$SVP,\$RD,\$DM,\$store,
#                   \$wtd_smrr_gain,\$QTD_SMRR_GAIN,
#                   \$wtd_bor_gain,\$QTD_BOR_GAIN,
#                   \$wtd_cust_gain,\$QTD_CUST_GAIN,
#                   \$WTD_CARD_CLOSED,\$QTD_AVG_CARD_CL);

###############################
#
#   Create an array reference.
#
###############################

my $a_row = $sth->fetchall_arrayref();

###############################
#
#  insert Data into spreadsheet
#
###############################

foreach $stmt (@{$a_row}) 
{ 
   $worksheet->write_col(14,0,$a_row,$format); 
   last;
} 

##################
#
#  Finish part 1
#
##################


$sth->finish();
$dbh->rollback;
$dbh->disconnect();


}###end of function1###

###############################
#
#  Call Functions / Subroutines
#
###############################

&get_store_data;
print "$rd DONE WITH Q4 Store DATA! \n";
$excel->close();



##################################
#
#   Create email addresses for RD.
#
##################################

my $at = "@";

my $rentacenter_com = "rentacenter.com";

my $rd_at_rentacenter_com = "$rd$at$rentacenter_com";

print "$rd_at_rentacenter_com\n";

##################################
#
#   Create email addresses for ME.
#
##################################

my $cory_clay = "cory.clay";

my $cory_clay_at_rentacenter_com = "$cory_clay$at$rentacenter_com";

print "$cory_clay_at_rentacenter_com\n";

##################################
#
#   Create email Headding for RD.
#
##################################

my $Q4 = "Q4 ";

my $PERFORMANCE_RPT = " PERFORMANCE RPT.xls";

my $Q4_RD_PERFORMANCE_RPT = "$Q4$rd$PERFORMANCE_RPT";

print "$Q4_RD_PERFORMANCE_RPT\n\n";

#########################################
#
#   Email the email with Send attachment.
#
##########################################

my $sender = new Mail::Sender
{
    smtp => 'xx.xx.x.xx',
    from => 'cory.clay@rentacenter.com'
};

$sender->MailFile(
{
    to => $cory_clay_at_rentacenter_com,
    subject => $Q4_RD_PERFORMANCE_RPT,
    msg     => "All,
Test Body of email.

Thanks,

Cory Clay
$cory_clay_at_rentacenter_com
\n",
    file    => $Q4_RD_PERFORMANCE_RPT,
});

$sender->Close;

$i++;
$j++;

}

exit(0);
[download]