$result = full_query($query);
$data = mysql_fetch_array($result);
$newinvoices = $data[0];
$query = "SELECT COUNT(*) FROM tblinvoices WHERE `datepaid` LIKE '" . db_make_safe_date($date) . "%'";
$result = full_query($query);
$data = mysql_fetch_array($result);
$paidinvoices = $data[0];
$query = "SELECT COUNT(*) FROM tbltickets WHERE `date` LIKE '" . db_make_safe_date($date) . "%'";
$result = full_query($query);
$data = mysql_fetch_array($result);
$newtickets = $data[0];
$query = "SELECT COUNT(*) FROM tblticketreplies WHERE `date` LIKE '" . db_make_safe_date($date) . "%' AND admin!=''";
$result = full_query($query);
$data = mysql_fetch_array($result);
$ticketreplies = $data[0];
$query = "SELECT COUNT(*) FROM tblcancelrequests WHERE `date` LIKE '" . db_make_safe_date($date) . "%'";
$result = full_query($query);
$data = mysql_fetch_array($result);
$cancellations = $data[0];
$reportdata["tablevalues"][] = array($daytext . ' ' . fromMySQLDate($date), $neworders, $newinvoices, $paidinvoices, $newtickets, $ticketreplies, $cancellations);
$chartdata['rows'][] = array('c' => array(array('v' => fromMySQLDate($date)), array('v' => (int) $neworders), array('v' => (int) $newinvoices), array('v' => (int) $paidinvoices), array('v' => (int) $newtickets), array('v' => (int) $ticketreplies), array('v' => (int) $cancellations)));
}
$chartdata['cols'][] = array('label' => 'Day', 'type' => 'string');
$chartdata['cols'][] = array('label' => 'Completed Orders', 'type' => 'number');
$chartdata['cols'][] = array('label' => 'New Invoices', 'type' => 'number');
$chartdata['cols'][] = array('label' => 'Paid Invoices', 'type' => 'number');
$chartdata['cols'][] = array('label' => 'Opened Tickets', 'type' => 'number');
$chartdata['cols'][] = array('label' => 'Ticket Replies', 'type' => 'number');
$chartdata['cols'][] = array('label' => 'Cancellation Requests', 'type' => 'number');
$args = array();
$args['legendpos'] = 'right';
function db_make_safe_human_date($date)
{
$date = toMySQLDate($date);
return db_make_safe_date($date);
}
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Monthly Transactions Report for " . $months[(int) $month] . " " . $year;
$reportdata["description"] = "This report provides a summary of daily payments activity for a given month. The Amount Out figure includes both expenditure transactions and refunds.";
$reportdata["currencyselections"] = true;
$reportdata["tableheadings"] = array("Date", "Amount In", "Fees", "Amount Out", "Balance");
for ($counter = 1; $counter <= 31; $counter += 1) {
$counter = str_pad($counter, 2, "0", STR_PAD_LEFT);
$query = "SELECT SUM(amountin),SUM(fees),SUM(amountout) FROM tblaccounts INNER JOIN tblclients ON tblclients.id=tblaccounts.userid WHERE date LIKE '" . db_make_safe_date("{$year}-{$month}-{$counter}") . "%' AND tblclients.currency=" . (int) $currencyid;
$result = full_query($query);
$data = mysql_fetch_array($result);
$amountin = $data[0];
$fees = $data[1];
$amountout = $data[2];
$query = "SELECT SUM(amountin),SUM(fees),SUM(amountout) FROM tblaccounts WHERE date LIKE '" . db_make_safe_date("{$year}-{$month}-{$counter}") . "%' AND userid='0' AND currency=" . (int) $currencyid;
$result = full_query($query);
$data = mysql_fetch_array($result);
$amountin += $data[0];
$fees += $data[1];
$amountout += $data[2];
$dailybalance = $amountin - $fees - $amountout;
$overallbalance += $dailybalance;
$chartdata['rows'][] = array('c' => array(array('v' => $counter), array('v' => $amountin, 'f' => formatCurrency($amountin)), array('v' => $fees, 'f' => formatCurrency($fees)), array('v' => $amountout, 'f' => formatCurrency($amountout))));
$amountin = formatCurrency($amountin);
$fees = formatCurrency($fees);
$amountout = formatCurrency($amountout);
$dailybalance = formatCurrency($dailybalance);
$reportdata["tablevalues"][] = array(fromMySQLDate("{$year}-{$month}-{$counter}"), $amountin, $fees, $amountout, $dailybalance);
}
$overallbalance = formatCurrency($overallbalance);
$currencyamount = 0;
}
$totals[$currencyid] += $currencyamount;
$currency = getCurrency('', $currencyid);
$rowdata[] = formatCurrency($currencyamount);
if ($currencyid == $defaultcurrencyid) {
$chartdata['rows'][] = array('c' => array(array('v' => "{$day} - " . ($day + 30)), array('v' => $currencyamount, 'f' => formatCurrency($currencyamount))));
}
}
$reportdata["tablevalues"][] = $rowdata;
}
$startdate = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 120, date("Y")));
$rowdata = array();
$rowdata[] = "120 +";
$currencytotals = array();
$query = "SELECT tblclients.currency,SUM(tblinvoices.total) FROM tblinvoices INNER JOIN tblclients ON tblclients.id=tblinvoices.userid WHERE tblinvoices.duedate<='" . db_make_safe_date($startdate) . "' AND tblinvoices.status='Unpaid' GROUP BY tblclients.currency";
$result = full_query($query);
while ($data = mysql_fetch_array($result)) {
$currencytotals[$data[0]] = $data[1];
}
foreach ($currencies as $currencyid => $currencyname) {
$currencyamount = $currencytotals[$currencyid];
if (!$currencyamount) {
$currencyamount = 0;
}
$totals[$currencyid] += $currencyamount;
$currency = getCurrency('', $currencyid);
$rowdata[] = formatCurrency($currencyamount);
}
$reportdata["tablevalues"][] = $rowdata;
$rowdata = array();
if ($month == "") {
$month = date("m");
$year = date("Y");
}
$pmonth = str_pad($month, 2, "0", STR_PAD_LEFT);
$reportdata["title"] = "Support Ticket Replies for " . $months[$month - 1] . " " . $year;
$reportdata["description"] = "This report shows a breakdown of support tickets dealt with per admin for a given month";
$reportdata["tableheadings"][] = "Admin";
for ($day = 1; $day <= 31; $day += 1) {
$reportdata["tableheadings"][] = $day;
}
$rowcount = 0;
$reportvalues = array();
for ($day = 1; $day <= 31; $day += 1) {
$date = $year . "-" . $pmonth . "-" . str_pad($day, 2, "0", STR_PAD_LEFT);
$query = "SELECT `admin`,COUNT(tid) AS totalreplies,COUNT(DISTINCT tid) AS totaltickets FROM `tblticketreplies` WHERE date LIKE '" . db_make_safe_date($date) . "%' AND admin!='' GROUP BY `admin` ORDER BY `admin`";
$result = full_query($query);
while ($data = mysql_fetch_array($result)) {
$adminname = $data[0];
$totalreplies = $data[1];
$totaltickets = $data[2];
$reportvalues[$adminname][$day] = array("totalreplies" => $totalreplies, "totaltickets" => $totaltickets);
}
}
foreach ($reportvalues as $adminname => $values) {
$reportdata["tablevalues"][$rowcount][] = "**{$adminname}";
$rowcount++;
$nextrow = $rowcount + 1;
$reportdata["tablevalues"][$rowcount][] = "Tickets";
$reportdata["tablevalues"][$nextrow][] = "Replies";
$i = 1;
