sub acumulado_f #I've called a sub 'cause there's more than one type of report
{
$titulo = "Report by state";
$dbh = SysUtils::Connect;
$sthf = $dbh->prepare_cached("SELECT uf FROM filiais ORDER BY uf"); #selects each state
$xf = 0;
$sthf->execute;
while (@dataf = $sthf->fetchrow_array())
{
$filiais[$xf] = $dataf[0];
$xf++;
}
foreach $filiais(@filiais)#foreach state
{
$cnt =0;
$cnt2 = 0;
if ($cnt2 eq 0)# if it's the first result, assemble a header
{
$message .= "
| State: $filiais |
| |
Income |
Margin |
| |
Predicted |
Realized |
% |
Difference |
Predicted |
Realized |
% |
Difference |
";
$cnt2++;
}
for ($m=1; $m<=12; $m++)#Make an array of months
{
$months[$m] = $m;
}
foreach $months(@months)
{
if ($months ne "")#escapes the obligatory 0 position of the array - blame me later for this one :)
{
$sh_mes = SysUtils::Extenso_Mes("$months");#Return the month name
$fat = 0;
$mar = 0;
# In the database there are fields with the same number as months:
$fa = "A.f".$months;
$ma = "A.m".$months;
$rf = "A.rf".$months;
$rm = "A.rm".$months;
$tf = 0; # First predicted value
$trf = 0;# First realized value
$tm = 0;# Second Predicted value
$trm = 0;# Second realized value
$ctp = 0;
$sthn = $dbh->prepare_cached("SELECT DISTINCT ".$fa." , ".$ma." , ".$rf." , ".$rm." FROM metas A, usuarios B WHERE B.filial='$filiais' AND A.login=B.login") or die "Preparing error. ".$dbh->errstr;
$sthn->execute or die "executing error. ".$sthn->errstr; #This statement works fine. I've debugged it and tested it
while (@rown = $sthn->fetchrow_array())
{
$fat = $rown[0];
$mar = $rown[1];
$rfat = $rown[2];
$rmar = $rown[3];
$tf += $fat; #First predicted value
$tm += $mar; #Second predicted value
$trf += $rfat;#First realized value
$trm += $rmar;#Second realized value
$ctp++;
if ($months eq "1") #January, obviously
{
$tft = 0; #trimester predicted 1st total
$trft = 0; #trimester realized 1st total
$tmt = 0; #trimester predicted 2nd total
$trmt = 0; #trimester realized 2nd total
$tft += $tf;
$trft += $trf;
$tmt += $tm;
$trmt += $trm;
}
elsif ($months eq "2" or $months eq "3")
{
$tft += $tf;
$trft += $trf;
$tmt += $tm;
$trmt += $trm;
}
elsif ($months eq "4")
{
$tft = 0;
$trft = 0;
$tmt = 0;
$trmt = 0;
$tft += $tf;
$trft += $trf;
$tmt += $tm;
$trmt += $trm;
}
elsif ($months eq "5" or $months eq "6")
{
$tft += $tf;
$trft += $trf;
$tmt += $tm;
$trmt += $trm;
}
elsif ($months eq "7")
{
$tft = 0;
$trft = 0;
$tmt = 0;
$trmt = 0;
$tft += $tf;
$trft += $trf;
$tmt += $tm;
$trmt += $trm;
}
elsif ($months eq "8" or $months eq "9")
{
$tft += $tf;
$trft += $trf;
$tmt += $tm;
$trmt += $trm;
}
elsif ($months eq "10")
{
$tft = 0;
$trft = 0;
$tmt = 0;
$trmt = 0;
$tft += $tf;
$trft += $trf;
$tmt += $tm;
$trmt += $trm;
}
elsif ($months eq "11" or $months eq "12")
{
$tft += $tf;
$trft += $trf;
$tmt += $tm;
$trmt += $trm;
}
}
$sthn->finish;
if ($ctp ne 0)
{
ListaDados(); #brings value for each month
if ($months eq "3")# Is it a trimester?
{
$trim = 1;
ListaTrimestre(); #bring values (totals) for each trimester
}
elsif ($months eq "6")
{
$trim = 2;
ListaTrimestre();
}
elsif ($months eq "9")
{
$trim = 3;
ListaTrimestre();
}
elsif ($months eq "12")
{
$trim = 4;
ListaTrimestre();
}
}
else
{
if ($cnt eq 0)#No values to show
{
$message .= "
| State has no values. |
";
}
$cnt++;
}
}
}
}
$dbh->disconnect;
#open template to show values.
print "Content-type: text/html\r\n\r\n";
open (FH,"templates/relatorios/relatorio_andamento.htm") or die "Cant open body: $!";
$body = join('',);
close (FH);
eval "\$body = sprintf \"%s\",<