$todate = getTodaysDate();
}
$reportdata["headertext"] = "<form method=\"post\" action=\"?" . (isset($_REQUEST['module']) ? 'module=' . $_REQUEST['module'] . '&' : '') . "report={$report}¤cyid={$currencyid}&calculate=true\"><center>Staff Name: <select name=\"staffid\"><option value=\"0\">- Any -</option>";
$result = select_query("tbladmins", "id,CONCAT(firstname,' ',lastname)", "", "firstname", "ASC");
while ($data = mysql_fetch_array($result)) {
$reportdata["headertext"] .= "<option value=\"" . $data[0] . "\"" . ($data[0] == $staffid ? " selected" : "") . ">" . $data[1] . "</option>";
}
$reportdata["headertext"] .= "</select> Start Date: <input type=\"text\" name=\"fromdate\" value=\"{$fromdate}\" class=\"datepick\" /> End Date: <input type=\"text\" name=\"todate\" value=\"{$todate}\" class=\"datepick\" /> <input type=\"submit\" value=\"Generate Report\" /></form>";
$reportdata["tableheadings"][] = "Ticket ID";
$reportdata["tableheadings"][] = "Staff Name";
$reportdata["tableheadings"][] = "Subject";
$reportdata["tableheadings"][] = "Feedback Left";
$reportdata["tableheadings"][] = "Rating";
$reportdata["tableheadings"][] = "Comments";
$reportdata["tableheadings"][] = "IP Address";
$result = select_query("tblticketfeedback", "tblticketfeedback.*,(SELECT CONCAT(firstname,' ',lastname) FROM tbladmins WHERE tbladmins.id=tblticketfeedback.adminid) AS adminname,(SELECT CONCAT(tid,'|||',title) FROM tbltickets WHERE tbltickets.id=tblticketfeedback.ticketid) AS ticketinfo", "datetime>='" . db_make_safe_human_date($fromdate) . "' AND datetime<='" . db_make_safe_human_date($todate) . " 23:59:59'" . ($staffid ? " AND adminid=" . (int) $staffid : ""), "datetime", "ASC");
while ($data = mysql_fetch_array($result)) {
$id = $data['id'];
$ticketid = $data['ticketid'];
$ticketinfo = $data['ticketinfo'];
$adminid = $data['adminid'];
$adminname = $data['adminname'];
$rating = $data['rating'];
$comments = $data['comments'];
$datetime = $data['datetime'];
$ip = $data['ip'];
if ($adminid == 0) {
$adminname = 'Generic Feedback';
} elseif (!trim($adminname)) {
$adminname = 'Deleted Admin';
}
<?php
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Ticket Tags Overview";
$reportdata["description"] = "This report provides an overview of ticket tags assigned to tickets for a given date range";
if (!$fromdate) {
$fromdate = fromMySQLDate(date("Y-m-d", mktime(0, 0, 0, date("m") - 1, date("d"), date("Y"))));
}
if (!$todate) {
$todate = getTodaysDate();
}
$reportdata["headertext"] = "<form method=\"post\" action=\"" . $_SERVER['PHP_SELF'] . "?report={$report}\"><center>Start Date: <input type=\"text\" name=\"fromdate\" value=\"{$fromdate}\" class=\"datepick\" /> End Date: <input type=\"text\" name=\"todate\" value=\"{$todate}\" class=\"datepick\" /> <input type=\"submit\" value=\"Generate Report\" /></form>";
$reportdata["tableheadings"][] = "Tag";
$reportdata["tableheadings"][] = "Count";
$result = full_query("SELECT `tag`, COUNT(*) AS `count` FROM `tbltickettags` INNER JOIN tbltickets ON tbltickets.id=tbltickettags.ticketid WHERE tbltickets.date>='" . db_make_safe_human_date($fromdate) . " 00:00:00' AND tbltickets.date<='" . db_make_safe_human_date($todate) . " 23:59:59' GROUP BY tbltickettags.tag ORDER BY `count` DESC");
while ($data = mysql_fetch_array($result)) {
$tag = $data[0];
$count = $data[1];
$reportdata["tablevalues"][] = array($tag, $count);
$chartdata['rows'][] = array('c' => array(array('v' => $tag), array('v' => (int) $count, 'f' => $count)));
}
$chartdata['cols'][] = array('label' => 'Tag', 'type' => 'string');
$chartdata['cols'][] = array('label' => 'Count', 'type' => 'number');
$args = array();
$args['legendpos'] = 'right';
$reportdata["headertext"] .= $chart->drawChart('Pie', $chartdata, $args, '300px');
<?php
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Credits Reviewer";
$reportdata["description"] = "This report allows you to review all the credits issued to clients between 2 dates you specify";
$reportdata["headertext"] = '<form method="post" action="?report=' . $report . '">
<p align="center">Start Date: <input type="text" name="startdate" value="' . $startdate . '" class="datepick" /> End Date: <input type="text" name="enddate" value="' . $enddate . '" class="datepick" /> <input type="submit" value="Generate Report"></p>
</form>';
$reportdata["tableheadings"] = array("Credit ID", "Client Name", "Date", "Description", "Amount");
if ($startdate && $enddate) {
$query = "SELECT tblcredit.*,tblclients.firstname,tblclients.lastname FROM tblcredit INNER JOIN tblclients ON tblclients.id=tblcredit.clientid WHERE tblcredit.date BETWEEN '" . db_make_safe_human_date($startdate) . "' AND '" . db_make_safe_human_date($enddate) . "'";
$result = full_query($query);
while ($data = mysql_fetch_array($result)) {
$id = $data["id"];
$userid = $data["clientid"];
$clientname = $data["firstname"] . " " . $data["lastname"];
$date = fromMySQLDate($data["date"]);
$description = $data["description"];
$amount = $data["amount"];
$currency = getCurrency($userid);
$amount = formatCurrency($amount);
$reportdata["tablevalues"][] = array($id, '<a href="clientssummary.php?userid=' . $userid . '">' . $clientname . '</a>', $date, nl2br($description), $amount);
}
}
$data["footertext"] = '';
<form method="post" action="reports.php?report={$report}">
<table align="center">
<tr><td>Date From:</td><td><input type="text" name="datefrom" value="{$datefrom}" class="datepick" /></td><td width="20"></td><td>Date To:</td><td><input type="text" name="dateto" value="{$dateto}" class="datepick" /></td><td width="20"></td><td><input type="submit" value="Submit" /></tr>
</table>
</form>
EOF;
$reportdata["tableheadings"] = array("Coupon Code", "Discount Type", "Value", "Recurring", "Notes", "Usage Count", "Total Revenue");
$i = 0;
$result = select_query("tblpromotions", "", "", "code", "ASC");
while ($data = mysql_fetch_array($result)) {
$code = $data["code"];
$type = $data["type"];
$value = $data["value"];
$recurring = $data["recurring"];
$notes = $data["notes"];
$rowcount = $rowtotal = 0;
$reportdata["drilldown"][$i]["tableheadings"] = array("Order ID", "Order Date", "Order Number", "Order Total", "Order Status");
$result2 = select_query("tblorders", "", "promocode='" . db_escape_string($code) . "' AND date>='" . db_make_safe_human_date($datefrom) . "' AND date<='" . db_make_safe_human_date($dateto) . "'", "id", "ASC");
while ($data = mysql_fetch_array($result2)) {
$orderid = $data['id'];
$ordernum = $data['ordernum'];
$orderdate = $data['date'];
$ordertotal = $data['amount'];
$orderstatus = $data['status'];
$rowcount++;
$rowtotal += $ordertotal;
$reportdata["drilldown"][$i]["tablevalues"][] = array('<a href="orders.php?action=view&id=' . $orderid . '">' . $orderid . '</a>', fromMySQLDate($orderdate), $ordernum, $ordertotal, $orderstatus);
}
$reportdata["tablevalues"][$i] = array($code, $type, $value, $recurring, $notes, $rowcount, format_as_currency($rowtotal));
$i++;
}
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
require ROOTDIR . '/includes/ticketfunctions.php';
if (!$rating) {
$rating = '1';
}
if (!$startdate) {
$startdate = fromMySQLDate(date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 7, date("Y"))));
}
if (!$enddate) {
$enddate = fromMySQLDate(date("Y-m-d", mktime(0, 0, 0, date("m"), date("d"), date("Y"))));
}
$rsel[$rating] = ' selected';
$query = "SELECT tblticketreplies.*,tbltickets.tid AS ticketid FROM tblticketreplies INNER JOIN tbltickets ON tbltickets.id=tblticketreplies.tid WHERE tblticketreplies.admin!='' AND tblticketreplies.rating='" . (int) $rating . "' AND tblticketreplies.date BETWEEN '" . db_make_safe_human_date($startdate) . "' AND '" . db_make_safe_human_date($enddate) . "' ORDER BY date DESC";
$result = full_query($query);
$num_rows = mysql_num_rows($result);
$reportdata["title"] = "Support Ticket Ratings Reviewer";
$reportdata["description"] = "This report is showing all {$num_rows} ticket replies rated {$rating} between {$startdate} & {$enddate} for review";
$reportdata["headertext"] = '<form method="post" action="reports.php?report=ticket_ratings_reviewer">
<p align="center"><b>Filter:</b> Rating: <select name="rating"><option' . $rsel[1] . '>1</option><option' . $rsel[2] . '>2</option><option' . $rsel[3] . '>3</option><option' . $rsel[4] . '>4</option><option' . $rsel[5] . '>5</option></select> Between Dates: <input type="text" name="startdate" value="' . $startdate . '" class="datepick" /> and <input type="text" name="enddate" value="' . $enddate . '" class="datepick" /> <input type="submit" value="Filter List" /></p>
</form>';
$reportdata["tableheadings"] = array("Ticket #", "Date", "Message", "Admin", "Rating");
while ($data = mysql_fetch_array($result)) {
$tid = $data["tid"];
$ticketid = $data["ticketid"];
$date = $data["date"];
$message = $data["message"];
$admin = $data["admin"];
$rating = $data["rating"];
$fromdate = fromMySQLDate(date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 7, date("Y"))));
}
if (!$todate) {
$todate = getTodaysDate();
}
$customfieldid = get_query_val("tblcustomfields", "id", array("type" => "client", "fieldname" => "How did you find us?"));
if (!$customfieldid) {
$customfieldid = get_query_val("tblcustomfields", "id", array("type" => "client", "fieldname" => "Where did you hear about us?"));
}
if (!$customfieldid && isset($_REQUEST['fieldname']) && isset($_REQUEST['options'])) {
$customfieldid = insert_query("tblcustomfields", array("type" => "client", "fieldname" => $_REQUEST['fieldname'], "fieldtype" => "dropdown", "fieldoptions" => $_REQUEST['options'], "showorder" => "on"));
}
if (!$customfieldid) {
$reportdata["headertext"] = '<div style="margin:50px auto;width:50%;padding:15px;border:1px dashed #ccc;text-align:center;font-size:14px;">This report requires you to setup a custom field shown during the signup process with a name of "How Did You Find Us?" or "Where did you hear about us?" in order to collect this data from customers.<br /><br />You don\'t appear to have the custom field setup yet so we can do this now:<br /><br /><form method="post" action="reports.php?report=client_sources">Field Name: <select name="fieldname"><option>How did you find us?</option><option>Where did you hear about us?</option></select><br />Options: <input type="text" name="options" value="Google,Bing,Other Search Engine,Web Hosting Talk,Friend,Advertisement,Other" style="width:70%;" /><br /><br /><input type="submit" value="Create »" class="btn btn-primary" /></form></div>';
} else {
$reportdata["headertext"] = "<form method=\"post\" action=\"{$PHP_SELF}?report={$report}¤cyid={$currencyid}&calculate=true\"><center><strong>Signup Date Range</strong> Start Date: <input type=\"text\" name=\"fromdate\" value=\"{$fromdate}\" class=\"datepick\" /> End Date: <input type=\"text\" name=\"todate\" value=\"{$todate}\" class=\"datepick\" /> <input type=\"submit\" value=\"Generate Report\" /></form>";
}
$reportdata["tableheadings"][] = "Referral Location";
$reportdata["tableheadings"][] = "Count";
$result = select_query("tblcustomfieldsvalues", "value,COUNT(*) AS rows", "fieldid=" . (int) $customfieldid . " AND datecreated>='" . db_make_safe_human_date($fromdate) . "' AND datecreated<='" . db_make_safe_human_date($todate) . "' GROUP BY `value`", "value", "ASC", "", "tblclients ON tblclients.id=tblcustomfieldsvalues.relid");
while ($data = mysql_fetch_array($result)) {
$reportdata["tablevalues"][] = array($data[0], $data[1]);
$chartdata['rows'][] = array('c' => array(array('v' => $data[0]), array('v' => $data[1], 'f' => $data[1])));
}
$chartdata['cols'][] = array('label' => 'Referral Location', 'type' => 'string');
$chartdata['cols'][] = array('label' => 'Count', 'type' => 'number');
$args = array();
$args['legendpos'] = 'right';
if ($customfieldid) {
$reportdata["footertext"] = $chart->drawChart('Pie', $chartdata, $args, '300px');
}
$reportdata["description"] = "This report provides a summary of scores received on a per staff member basis for a given date range";
if (!$fromdate) {
$fromdate = fromMySQLDate(date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 7, date("Y"))));
}
if (!$todate) {
$todate = getTodaysDate();
}
$reportdata["headertext"] = "<form method=\"post\" action=\"" . '' . $_SERVER['PHP_SELF'] . '?' . (isset($_REQUEST['module']) ? 'module=' . $_REQUEST['module'] . '&' : '') . '' . "report={$report}¤cyid={$currencyid}&calculate=true\"><center>Start Date: <input type=\"text\" name=\"fromdate\" value=\"{$fromdate}\" class=\"datepick\" /> End Date: <input type=\"text\" name=\"todate\" value=\"{$todate}\" class=\"datepick\" /> <input type=\"submit\" value=\"Generate Report\" /></form>";
$reportdata["tableheadings"][] = "Staff Name";
for ($rating = 1; $rating <= 10; $rating++) {
$reportdata["tableheadings"][] = $rating;
}
$reportdata["tableheadings"][] = "Total Ratings";
$reportdata["tableheadings"][] = "Average Rating";
$adminnames = $ratingstats = array();
$result = select_query("tblticketfeedback", "(SELECT CONCAT(firstname,' ',lastname) FROM tbladmins WHERE tbladmins.id=tblticketfeedback.adminid) AS adminname,adminid,rating,COUNT(*)", "adminid>0 AND datetime>='" . db_make_safe_human_date($fromdate) . "' AND datetime<='" . db_make_safe_human_date($todate) . " 23:59:59' GROUP BY `rating`, `adminid`", "adminname", "ASC");
while ($data = mysql_fetch_array($result)) {
$adminname = $data[0];
$adminid = $data[1];
$rating = $data[2];
$count = $data[3];
$adminnames[$adminid] = $adminname;
$ratingstats[$adminid][$rating] = $count;
}
foreach ($adminnames as $adminid => $adminname) {
$rowtotal = $rowcount = 0;
$row = array();
$row[] = '<a href="' . $_SERVER['PHP_SELF'] . '?' . (isset($_REQUEST['module']) ? 'module=' . $_REQUEST['module'] . '&' : '') . 'report=ticket_feedback_comments&' . (isset($_REQUEST['module']) ? 'module=' . $_REQUEST['module'] . '&' : '') . 'staffid=' . $adminid . '">' . $adminname . '</a>';
for ($rating = 1; $rating <= 10; $rating++) {
$count = $ratingstats[$adminid][$rating];
$row[] = $count;
$reportdata["title"] = "Sales Tax Liability";
$reportdata["description"] = "This report shows sales tax liability for the selected period";
$reportdata["currencyselections"] = true;
$query = "select year(min(date)) as minimum, year(max(date)) as maximum from tblaccounts;";
$result = full_query($query);
$data = mysql_fetch_array($result);
$minyear = $data['minimum'];
$maxyear = $data['maximum'];
if (!$startdate) {
$startdate = fromMySQLDate(date('Y-m-d'));
}
if (!$enddate) {
$enddate = fromMySQLDate(date('Y-m-d'));
}
$queryStartDate = db_make_safe_human_date($startdate);
$queryEndDate = db_make_safe_human_date($enddate);
$currencyID = (int) $currencyid;
$reportdata["headertext"] = "<form method=\"post\" action=\"?report={$report}¤cyid={$currencyid}&calculate=true\"><center>Start Date: <input type=\"text\" name=\"startdate\" value=\"{$startdate}\" class=\"datepick\" /> End Date: <input type=\"text\" name=\"enddate\" value=\"{$enddate}\" class=\"datepick\" /> <input type=\"submit\" value=\"Generate Report\"></form>";
if ($calculate) {
$query = <<<QUERY
SELECT COUNT(*), SUM(total), SUM(tblinvoices.credit), SUM(tax), SUM(tax2)
FROM tblinvoices
INNER JOIN tblclients ON tblclients.id = tblinvoices.userid
WHERE datepaid >= '{$queryStartDate}'
AND datepaid <= '{$queryEndDate} 23:59:59'
AND tblinvoices.status = 'Paid'
AND currency = {$currencyID}
AND (SELECT count(tblinvoiceitems.id)
FROM tblinvoiceitems
WHERE invoiceid = tblinvoices.id
AND (type = 'AddFunds' OR type = 'Invoice')
$tax = $data[3];
$tax2 = $data[4];
if (!$total) {
$total = "0.00";
}
if (!$tax) {
$tax = "0.00";
}
if (!$tax2) {
$tax2 = "0.00";
}
$reportdata["headertext"] .= "<br>{$numinvoices} Invoices Found<br><B>Total Invoiced:</B> " . formatCurrency($total) . " <B>Tax Level 1 Liability:</B> " . formatCurrency($tax) . " <B>Tax Level 2 Liability:</B> " . formatCurrency($tax2);
}
$reportdata["headertext"] .= "</center>";
$reportdata["tableheadings"] = array("Invoice ID", "Client Name", "Invoice Date", "Date Paid", "Subtotal", "Tax", "Credit", "Total");
$query = "SELECT tblinvoices.*,tblclients.firstname,tblclients.lastname FROM tblinvoices INNER JOIN tblclients ON tblclients.id=tblinvoices.userid WHERE datepaid>='" . db_make_safe_human_date($startdate) . "' AND datepaid<='" . db_make_safe_human_date($enddate) . " 23:59:59' AND tblinvoices.status='Paid' AND currency=" . (int) $currencyid . " ORDER BY date ASC";
$result = full_query($query);
while ($data = mysql_fetch_array($result)) {
$id = $data["id"];
$userid = $data["userid"];
$client = $data["firstname"] . " " . $data["lastname"];
$date = fromMySQLDate($data["date"]);
$datepaid = fromMySQLDate($data["datepaid"]);
$currency = getCurrency($userid);
$subtotal = $data["subtotal"];
$credit = $data["credit"];
$tax = $data["tax"] + $data["tax2"];
$total = $data["total"] + $credit;
$reportdata["tablevalues"][] = array("{$id}", "{$client}", "{$date}", "{$datepaid}", "{$subtotal}", "{$tax}", "{$credit}", "{$total}");
}
$data["footertext"] = "";