Calculating grand total from multiple queries

[lspiehler]

I separated these queries for the sake of the layout I was trying to achieve, and I need to calculate a sub total for each of my 3 categories, plus a grand total which is just the sum of the three categories. I used "with rollup" to get my sub totals for each category, but I'm not sure how to go about getting the grand total. I was trying to get the subtotals into an array, that I could just sum up later. I was attempting to return the last value of my fetch array (which should have been the rollup value) into a new array to do array_sum afterward, but I couldn't even find a way to echo the value after the while loop. Resetting the array didn't work. Anyway here's the code:

 

echo "<p align=\"center\"><font size=\"6\">Productivity Report</font></p>";

$types=array( 'Field', 'Phone', 'Office' );

foreach ($types as $type) {

if($type=="Field") { $hourly=$configrow[fieldhourrate];
} elseif($type=="Phone") { $hourly=$configrow[phonehourrate];
} elseif($type=="Office") { $hourly=$configrow[officehourrate];
}

setlocale(LC_MONETARY, 'en_US');

$query = "SELECT COUNT(fieldtickets.ticketnumber) AS numtickets, fieldtickets.type, SUM(fieldtickets.hours) AS totalhours , SUM( fieldtickets.hours * $hourly ) AS totalamt , fieldtickets.type, CONCAT( fname, ' ', lname ) AS fullname
FROM fieldtickets
RIGHT JOIN users ON fieldtickets.assigntech = users.id
WHERE type = '$type' AND (status = 'Closed' OR status = 'Pending')
GROUP BY fullname
WITH ROLLUP";

$result = mysql_query($query) or die(mysql_error());

echo "<table width=\"600\" align=\"center\">
<tr class=\"row\"><td align=\"center\" colspan=\"5\"><font size=\"5\"><b>$type</b></font></td></tr>
<tr><td><b>Name</b></td><td><b>Tickets</b></td><td><b>Hours</b></td><td><b>Rate</b></td><td><b>Total</b></td></tr>\n";

while($row = mysql_fetch_array($result)){

echo "<tr><td>".$row[fullname]."</td><td>".$row[numtickets]."</td><td>".$row[totalhours]."</td><td>".money_format('%(#10n', $hourly)."/hr</td><td>".money_format('%(#10n', $row[totalamt])."</td></tr>\n";

}

echo "</table><br />\n";

 

Which outputs:

 

Productivity Report

Field

Name Tickets Hours Rate Total

Keith Dean 11 7.00 $ 95.00/hr $ 665.00

Lyas Spiehler 10 10.50 $ 95.00/hr $ 997.50

Melissa Burk 1 0.00 $ 95.00/hr $ 0.00

Mike Bieller 11 10.00 $ 95.00/hr $ 950.00

33 27.50 $ 95.00/hr $ 2,612.50

 

Phone

Name Tickets Hours Rate Total

Lyas Spiehler 2 2.00 $ 35.00/hr $ 70.00

Melissa Burk 1 1.00 $ 35.00/hr $ 35.00

3 3.00 $ 35.00/hr $ 105.00

 

Office

Name Tickets Hours Rate Total

Lyas Spiehler 1 1.00 $ 65.00/hr $ 65.00

Melissa Burk 2 2.00 $ 65.00/hr $ 130.00

Mike Bieller 1 1.00 $ 65.00/hr $ 65.00

4 4.00 $ 65.00/hr $ 260.00

 

It's a lot prettier in html... Any help is greatly appreciated. Thanks a lot!

 

 

[lspiehler]

Trashed rollup and did all calculations with PHP. Here's the modified code:

 

echo "<p align=\"center\"><font size=\"6\">Productivity Report</font></p>
$types=array( 'Field', 'Phone', 'Office' );

foreach ($types as $type) {

if($type=="Field") { $hourly=$configrow[fieldhourrate];
} elseif($type=="Phone") { $hourly=$configrow[phonehourrate];
} elseif($type=="Office") { $hourly=$configrow[officehourrate];
}

setlocale(LC_MONETARY, 'en_US');

$query = "SELECT COUNT(fieldtickets.ticketnumber) AS numtickets, fieldtickets.type, SUM(fieldtickets.hours) AS totalhours , SUM( fieldtickets.hours * $hourly ) AS totalamt , fieldtickets.type, CONCAT( fname, ' ', lname ) AS fullname
FROM fieldtickets
RIGHT JOIN users ON fieldtickets.assigntech = users.id
WHERE type = '$type' AND (status = 'Closed' OR status = 'Pending') $sqltech $sqldate
GROUP BY fullname";
//$includerollup

$result = mysql_query($query) or die(mysql_error());

echo "<table width=\"600\" align=\"center\">
<tr class=\"row\"><td align=\"center\" colspan=\"5\"><font size=\"5\"><b>$type - \$ $hourly/hr</b></font></td></tr>
<tr><td width=\"195\"><b>Name</b></td><td width=\"135\"><b>Tickets</b></td><td width=\"135\"><b>Hours</b></td><td width=\"135\"><b>Total</b></td></tr>\n";

while($row = mysql_fetch_array($result)){

echo "<tr><td>".$row[fullname]."</td><td>".$row[numtickets]."</td><td>".$row[totalhours]."</td><td>".money_format('%(#8n', $row[totalamt])."</td></tr>\n";
$ticket_{$type}[] = $row[numtickets];
$hours_{$type}[] = $row[totalhours];
$total_{$type}[] = $row[totalamt];

}

echo "<tr><td> </td><td><b>".array_sum($ticket_{$type})."</b></td><td><b>".array_sum($hours_{$type})."</b></td><td><b>".money_format('%(#8n', array_sum($total_{$type}))."</b></td></tr>\n
</table><p> </p>\n";

}

} else { echo "<p align=\"center\">This report generates the total tickets, hours, and billed amount for each type<br />
of ticket for the specified technician(s) within a given range.</p>"; }

$totaltickets = array_sum($ticket_{Phone})+array_sum($ticket_{Office})+array_sum($ticket_{Field});
$totalhours = array_sum($hours_{Phone})+array_sum($hours_{Office})+array_sum($hours_{Field});
$totaltotal = array_sum($total_{Phone})+array_sum($total_{Office})+array_sum($total_{Field});

echo "<hr width=\"600\" />
<table width=\"600\" align=\"center\">
<tr><td width=\"195\"> </td><td width=\"135\"><font size=\"5\"><b>$totaltickets</b></font></td><td width=\"135\"><font size=\"5\"><b>$totalhours</b></font></td><td width=\"135\"><font size=\"5\"<b>".money_format('%(#8n', $totaltotal)."</b></font></td></tr>
</table>";