$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"] = "";