WHMCS Dedicated Server Revenue Report

<?php

if (!defined("WHMCS"))
    die("This file cannot be accessed directly");

# The title of your report
$reportdata["title"] = "Dedicated Server Revenue";

# The description of your report
$reportdata["description"] = "";

# Header text - this gets displayed above the report table of data
$reportdata["headertext"] = "";

# Report Table of Data Column Headings - should be an array of values
$reportdata["tableheadings"] = array("Server Name","Base Cost per Month","Base Profit per Server","Servers","Monthly Earnings");

# Report Table Values - one of these lines for each row you want in the table
# should be an array of values to match the column headings
$query = "SELECT
  p.id,
  p.name
FROM tblproducts AS p
  LEFT JOIN tblpricing pr
    ON (p.id = pr.relid)
  LEFT JOIN tblcustomfields c
    ON (p.id = c.relid)
  LEFT JOIN tblcustomfieldsvalues v
    ON (v.fieldid = c.id)
WHERE c.fieldname = 'Cost' AND p.type = 'server' AND pr.type = 'product'
GROUP BY p.id";
$result=mysql_query($query);
$s = array();
while ($d = mysql_fetch_array($result)) {
    $s[$d[0]]['name'] = $d[1];
    $s[$d[0]]['count'] = 0;
}

$query = "SELECT
  p.id,
  p.name,
  pr.monthly,
  c.fieldname,
  c.fieldoptions,
  v.value,
  h.amount
FROM tblproducts AS p
  LEFT JOIN tblpricing pr
    ON (p.id = pr.relid)
  LEFT JOIN tblcustomfields c
    ON (p.id = c.relid)
  RIGHT JOIN tblhosting h
    ON (h.packageid = p.id)
  LEFT JOIN tblcustomfieldsvalues v
    ON (v.fieldid = c.id AND v.relid = h.id)
WHERE c.fieldname = 'Cost' AND p.type = 'server' AND pr.type = 'product' AND h.domainstatus = 'Active'
GROUP BY h.id, p.id";
$result1=mysql_query($query);
$c = 0;
while ($data = mysql_fetch_array($result1)) {
    $cost = ($data[5] > '' ? $data[5] : $data[4]);
    $prof = ($data[2] <> $data[6] ? $data[6] - $cost : $data[2] - $cost);

    if($data[2] <> $data[6] || $data[5] > '') $n = $data[0]+$c;
    else $n = $data[0];

    $s[$n]['name'] = $data[1].($data[2] <> $data[6] || $data[5] > '' ? ' (Custom)' : '');
    $s[$n]['cost'] = $cost;
    $s[$n]['profit'] = $prof;
    $s[$n]['count'] += 1;

    $c++;
}

foreach($s as $f) {
    $totalexpenditure+=($f['cost']*$f['count']);
    $totalgrossprofit+=($f['profit']*$f['count']);
    $reportdata["tablevalues"][] = array($f['name'],number_format($f['cost'],2),number_format($f['profit'],2),$f['count'],number_format($f['profit']*$f['count'],2));
}

# Report Footer Text - this gets displayed below the report table of data
$data["footertext"]="<B>Total Gross Profit:</B> ".number_format($totalgrossprofit,2)."<br><B>Total Expenses:</B> ".number_format($totalexpenditure,2)."<br><b>Total Server's:</b> $c";

?>