|
Server IP : 213.176.29.180 / Your IP : 3.145.103.119 Web Server : Apache System : Linux 213.176.29.180.hostiran.name 4.18.0-553.22.1.el8_10.x86_64 #1 SMP Tue Sep 24 05:16:59 EDT 2024 x86_64 User : webtaragh ( 1001) PHP Version : 7.4.33 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON Directory (0750) : /home/webtaragh/public_html/.well-known/../ |
| [ Home ] | [ C0mmand ] | [ Upload File ] |
|---|
pdf_batch.php 0000644 00000010673 14736262117 0007207 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
use WHMCS\Invoices;
use WHMCS\Module\GatewaySetting;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata['isPrintable'] = false;
$reportdata['canCsvExport'] = false;
$reportdata["title"] = "Batch PDF Invoice Export";
$reportdata["description"] = <<<DESCRIPTION
This tool can be used to generate and download a batch export of invoices in PDF format (one per page).<br />
Typical uses for this include producing hard paper copies for mailing to clients or record keeping.
DESCRIPTION;
require("../includes/gatewayfunctions.php");
if ($noresults) {
infoBox("No Invoices Match Criteria", "No invoices were found matching the criteria you specified");
$reportdata["description"] .= $infobox;
}
$range = App::getFromRequest('range');
if (!$range) {
$today = Carbon::today()->endOfDay();
$lastMonth = Carbon::today()->subDays(29)->startOfDay();
$range = $lastMonth->toAdminDateFormat() . ' - ' . $today->toAdminDateFormat();
}
$clientsDropDown = $aInt->clientsDropDown($userid, false, 'userid', true);
$gatewayOptions = '';
foreach (GatewaySetting::getActiveGatewayFriendlyNames() as $gateway => $friendlyName) {
$gatewayOptions .= "<option value=\"{$gateway}\" selected>{$friendlyName}</option>";
}
$statusOptions = '';
foreach (Invoices::getInvoiceStatusValues() as $invoiceStatusOption) {
if ($invoiceStatusOption == 'Unpaid') {
$isSelected = 'selected';
} else {
$isSelected = '';
}
$optionName = $aInt->lang('status', strtolower(str_replace(' ', '', $invoiceStatusOption)));
$statusOptions .= "<option value=\"{$invoiceStatusOption}\" {$isSelected}>{$optionName}</option>";
}
$reportdata["headertext"] = <<<HTML
<form method="post" action="csvdownload.php?type=pdfbatch">
<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
<tr>
<td width="20%" class="fieldlabel">
Client Name
</td>
<td class="fieldarea">
{$clientsDropDown}
</td>
</tr>
<tr>
<td class="fieldlabel">
Filter By
</td>
<td class="fieldarea">
<select name="filterby" class="form-control select-inline">
<option>Date Created</option>
<option>Due Date</option>
<option>Date Paid</option>
</select>
</td>
</tr>
<tr>
<td class="fieldlabel">
Date Range
</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="range"
value="{$range}"
class="form-control date-picker-search input-inline"
/>
</div>
</td>
</tr>
<tr>
<td class="fieldlabel">
Payment Methods
</td>
<td class="fieldarea">
<select name="paymentmethods[]" class="form-control input-250" size="8" multiple="true">
{$gatewayOptions}
</select>
</td>
</tr>
<tr>
<td class="fieldlabel">
Statuses
</td>
<td class="fieldarea">
<select name="statuses[]" class="form-control input-150" size="6" multiple="true">
{$statusOptions}
</select>
</td>
</tr>
<tr>
<td class="fieldlabel">
Sort Order
</td>
<td class="fieldarea">
<select name="sortorder" class="form-control select-inline">
<option>Invoice ID</option>
<option>Invoice Number</option>
<option>Date Paid</option>
<option>Due Date</option>
<option>Client ID</option>
<option>Client Name</option>
</select>
</td>
</tr>
</table>
<p align=center>
<input type="submit" value="Download File" class="btn btn-default">
</p>
</form>
HTML;
$report = '';
direct_debit_processing.php 0000644 00000005715 14736262117 0012153 0 ustar 00 <?php
use WHMCS\Billing\Invoice;
use WHMCS\Database\Capsule;
use WHMCS\Payment\PayMethod\Adapter\BankAccount;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
if (!function_exists('getClientDefaultBankDetails')) {
require ROOTDIR . DIRECTORY_SEPARATOR . 'includes' . DIRECTORY_SEPARATOR . 'clientfunctions.php';
}
$reportdata["title"] = "Direct Debit Processing";
$reportdata["description"] = "This report displays all Unpaid invoices assigned to the Direct Debit payment method and the associated bank account details stored for their owners ready for processing";
$reportdata["tableheadings"] = array("Invoice ID","Client Name","Invoice Date","Due Date","Subtotal","Tax","Credit","Total","Bank Name","Bank Account Type","Bank Code","Bank Account Number");
$defaultBankDetailsPerUser = [];
$results = Capsule::table('tblinvoices')
->select('tblinvoices.*', 'tblclients.firstname', 'tblclients.lastname')
->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
->where('tblinvoices.paymentmethod', '=', 'directdebit')
->where('tblinvoices.status', '=', 'Unpaid')
->orderBy('duedate', 'asc')
->get()
->all();
foreach ($results as $result) {
$id = $result->id;
$userid = $result->userid;
$client = $result->firstname . " " . $result->lastname;
$date = $result->date;
$duedate = $result->duedate;
$subtotal = $result->subtotal;
$credit = $result->credit;
$tax = ($result->tax + $result->tax2);
$total = $result->total;
$invoice = Invoice::find($id);
if ($invoice && $invoice->payMethod && $invoice->payMethod->payment->isBankAccount()) {
/** @var BankAccount $payment */
$payment = $invoice->payMethod->payment;
$bankDetails["bankname"] = $payment->getBankName();
$bankDetails["banktype"] = $payment->getAccountType();
$bankDetails["bankcode"] = $payment->getRoutingNumber();
$bankDetails["bankacct"] = $payment->getAccountNumber();
} else {
if (!isset($defaultBankDetailsPerUser[$userid])) {
$defaultBankDetailsPerUser[$userid] = getClientDefaultBankDetails($userid);
}
$bankDetails = $defaultBankDetailsPerUser[$userid];
}
$bankname = $bankDetails["bankname"];
$banktype = $bankDetails["banktype"];
$bankcode = $bankDetails["bankcode"];
$bankacct = $bankDetails["bankacct"];
$currency = getCurrency($userid);
$date = fromMySQLDate($date);
$duedate = fromMySQLDate($duedate);
$subtotal = formatCurrency($subtotal);
$credit = formatCurrency($credit);
$tax = formatCurrency($tax);
$total = formatCurrency($total);
$reportdata["tablevalues"][] = [
'<a href="invoices.php?action=edit&id=' . $id . '">' . $id . '</a>',
$client,
$date,
$duedate,
$subtotal,
$tax,
$credit,
$total,
$bankname,
$banktype,
$bankcode,
$bankacct,
];
}
$reportdata["footertext"] = "";
ticket_tags.php 0000644 00000005761 14736262117 0007600 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
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";
$range = App::getFromRequest('range');
if (!$range) {
$today = Carbon::today()->endOfDay();
$lastWeek = Carbon::today()->subMonth()->startOfDay();
$range = $lastWeek->toAdminDateFormat() . ' - ' . $today->toAdminDateFormat();
}
$dateRange = Carbon::parseDateRangeValue($range);
$startdate = $dateRange['from'];
$enddate = $dateRange['to'];
$reportdata['headertext'] = '';
if (!$print) {
$reportdata['headertext'] = <<<HTML
<form method="post" action="reports.php?report={$report}¤cyid={$currencyid}&calculate=true">
<div class="report-filters-wrapper">
<div class="inner-container">
<h3>Filters</h3>
<div class="row">
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterDate">{$dateRangeText}</label>
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="range"
value="{$range}"
class="form-control date-picker-search"
/>
</div>
</div>
</div>
</div>
<button type="submit" class="btn btn-primary">
{$aInt->lang('reports', 'generateReport')}
</button>
</div>
</div>
</form>
HTML;
}
$reportdata["tableheadings"][] = "Tag";
$reportdata["tableheadings"][] = "Count";
$results = Capsule::table('tbltickettags')
->select(Capsule::raw('tbltickettags.tag, count(*) as `count`'))
->join('tbltickets', 'tbltickets.id', '=', 'tbltickettags.ticketid')
->whereBetween(
'tbltickets.date',
[
$startdate->toDateTimeString(),
$enddate->toDateTimeString(),
]
)
->groupBy('tbltickettags.tag')
->orderBy('count', 'desc')
->get()
->all();
foreach ($results as $result) {
$tag = $result->tag;
$count = $result->count;
$reportdata["tablevalues"][] = [$tag, $count];
$chartdata['rows'][] = [
'c' => [
['v' => $tag],
['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');
domains.php 0000644 00000020630 14736262117 0006721 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Domains";
$filterfields = [
'id' => 'ID',
'userid' => 'User ID',
'clientname' => 'Client Name',
'orderid' => 'Order ID',
'type' => 'Reg Type',
'domain' => 'Domain Name',
'firstpaymentamount' => 'First Payment Amount',
'recurringamount' => 'Recurring Amount',
'registrationperiod' => 'Registration Period',
'registrationdate' => 'Registration Date',
'expirydate' => 'Expiry Date',
'nextduedate' => 'Next Due Date',
'registrar' => 'Registrar',
'paymentmethod' => 'Payment Method',
'status' => 'Status',
'additionalnotes' => 'Notes'
];
$dateRangeFields = [
'registrationdate' => 'Registration Date',
'expirydate' => 'Expiry Date',
'nextduedate' => 'Next Due Date',
];
$removedDateRangeFields = array_diff($filterfields, $dateRangeFields);
$reportdata["description"] = $reportdata["headertext"] = '';
$incfields = $whmcs->get_req_var('incfields');
$filterfield = $whmcs->get_req_var('filterfield');
$filtertype = $whmcs->get_req_var('filtertype');
$filterq = $whmcs->get_req_var('filterq');
$regDateRange = App::getFromRequest('regDateRange');
$expiryDateRange = App::getFromRequest('expiryDateRange');
$nextDueDateRange = App::getFromRequest('nextDueDateRange');
if (!is_array($incfields)) {
$incfields = [];
}
if (!is_array($filterfield)) {
$filterfield = [];
}
if (!is_array($filtertype)) {
$filtertype = [];
}
if (!is_array($filterq)) {
$filterq = [];
}
if (!$print) {
$reportdata["description"] = "This report can be used to generate a custom export of domains"
. " by applying up to 5 filters. CSV Export is available via the Tools menu to the right.";
$reportdata["headertext"] = '<form method="post" action="reports.php?report=' . $report . '">
<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
<tr><td width="20%" class="fieldlabel">Fields to Include</td><td class="fieldarea"><table width="100%"><tr>';
$i=0;
foreach ($filterfields as $k => $v) {
$reportdata["headertext"] .= '<td width="20%"><input type="checkbox" name="incfields[]" value="' . $k . '" id="fd' . $k . '"';
if (in_array($k, $incfields)) {
$reportdata["headertext"] .= ' checked';
}
$reportdata["headertext"] .= ' /> <label for="fd' . $k . '">'.$v.'</label></td>';
$i++;
if (($i%5)==0) {
$reportdata["headertext"] .= '</tr><tr>';
}
}
$reportdata["headertext"] .= '</tr></table></td></tr>';
for ($i = 1; $i <= 5; $i ++) {
$reportdata["headertext"] .= '<tr><td width="20%" class="fieldlabel">Filter ' . $i . '</td><td class="fieldarea"><select name="filterfield[' . $i . ']" class="form-control select-inline"><option value="">None</option>';
foreach ($removedDateRangeFields as $k => $v) {
$reportdata["headertext"] .= '<option value="' . $k . '"';
if (isset($filterfield[$i]) && $filterfield[$i] == $k) {
$reportdata["headertext"] .= ' selected';
}
$reportdata["headertext"] .= '>'.$v.'</option>';
}
$reportdata["headertext"] .= '</select> <select name="filtertype[' . $i . ']" class="form-control select-inline">'
. '<option value="=">Exact Match</option><option value="like"';
if (isset($filtertype[$i]) && $filtertype[$i] == "like") {
$reportdata["headertext"] .= ' selected';
}
$reportdata["headertext"] .= '>Containing</option></select>'
. ' <input type="text" name="filterq[' . $i . ']" class="form-control select-inline input-250" value="' . (isset($filterq[$i]) ? $filterq[$i] : '') . '" /></td></tr>';
}
$reportdata["headertext"] .= <<<HTML
<tr>
<td width="20%" class="fieldlabel">Registration Date Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="regDateRange"
value="{$regDateRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
<tr>
<td width="20%" class="fieldlabel">Expiry Date Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="expiryDateRange"
value="{$expiryDateRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
<tr>
<td width="20%" class="fieldlabel">Next Due Date Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="nextDueDateRange"
value="{$nextDueDateRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
</table>
<p align="center"><input type="submit" value="Filter" class="btn btn-primary"/></p>
</form>
HTML;
}
if (count($incfields)) {
$query = Capsule::table('tbldomains');
foreach ($filterfield as $i => $val) {
if ($val && array_key_exists($val, $filterfields)) {
if ($filtertype[$i] == 'like') {
$filterq[$i] = "%{$filterq[$i]}%";
}
if ($val == 'clientname') {
$query->whereRaw(
"concat(tblclients.firstname, ' ', tblclients.lastname) "
. "{$filtertype[$i]} '{$filterq[$i]}'"
);
} else {
$query->where(
"tbldomains.{$filterfield[$i]}",
$filtertype[$i],
$filterq[$i]
);
}
}
}
foreach ($incfields as $fieldname) {
if (array_key_exists($fieldname, $filterfields)) {
$reportdata["tableheadings"][] = $filterfields[$fieldname];
if ($fieldname=="clientname") {
$query->addSelect(Capsule::raw("concat(tblclients.firstname, ' ', tblclients.lastname)"));
} else {
$query->addSelect("tbldomains.{$fieldname}");
}
}
}
if ($regDateRange) {
$dateRange = Carbon::parseDateRangeValue($regDateRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('registrationdate', [$fromdate, $todate]);
}
if ($expiryDateRange) {
$dateRange = Carbon::parseDateRangeValue($expiryDateRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('expirydate', [$fromdate, $todate]);
}
if ($nextDueDateRange) {
$dateRange = Carbon::parseDateRangeValue($nextDueDateRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('nextduedate', [$fromdate, $todate]);
}
$results = $query
->join('tblclients', 'tblclients.id', '=', 'tbldomains.userid')
->get()
->all();
foreach ($results as $result) {
$result = (array) $result;
if (isset($result['registrar'])) {
$result['registrar'] = ucfirst($result['registrar']);
}
if (isset($result['paymentmethod'])) {
$result['paymentmethod'] = $gateways->getDisplayName($result['paymentmethod']);
}
$reportdata["tablevalues"][] = $result;
}
}
ticket_feedback_comments.php 0000644 00000012474 14736262117 0012272 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
use WHMCS\Utility\GeoIp;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Ticket Feedback Comments";
$reportdata["description"] = "This report allows you to review feedback comments submitted by customers.";
$staffid = App::getFromRequest('staffid');
$range = App::getFromRequest('range');
if (!$range) {
$today = Carbon::today()->endOfDay();
$lastWeek = Carbon::today()->subDays(6)->startOfDay();
$range = $lastWeek->toAdminDateFormat() . ' - ' . $today->toAdminDateFormat();
}
$module = App::getFromRequest('module');
$moduleString = '';
if ($module) {
$moduleString = 'module=' . $module . '&';
}
$admins = Capsule::table('tbladmins')
->orderBy('firstname')
->pluck(
Capsule::raw(
'CONCAT_WS(\' \', tbladmins.firstname, tbladmins.lastname) as name'
),
'id'
)
->all();
$adminDropdown = '';
foreach ($admins as $adminId => $adminName) {
$selected = '';
if ($adminId == $staffid) {
$selected = ' selected="selected"';
}
$adminDropdown .= "<option value=\"{$adminId}\"{$selected}>{$adminName}</option>";
}
$reportdata['headertext'] = '';
if (!$print) {
$reportdata['headertext'] = <<<HTML
<form method="post" action="?{$moduleString}report={$report}¤cyid={$currencyid}&calculate=true">
<div class="report-filters-wrapper">
<div class="inner-container">
<h3>Filters</h3>
<div class="row">
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterStaff">Staff Name</label>
<select id="inputFilterStaff" name="staffid" class="form-control">
<option value="0">- Any -</option>
{$adminDropdown}
</select>
</div>
</div>
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterDate">{$dateRangeText}</label>
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="range"
value="{$range}"
class="form-control date-picker-search"
/>
</div>
</div>
</div>
</div>
<button type="submit" class="btn btn-primary">
{$aInt->lang('reports', 'generateReport')}
</button>
</div>
</div>
</form>
HTML;
}
$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";
$dateRange = Carbon::parseDateRangeValue($range);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->endOfDay()->toDateTimeString();
$query = Capsule::table('tblticketfeedback')
->where('datetime', '>=', $fromdate)
->where('datetime', '<=', $todate)
->leftJoin(
'tbladmins',
'tbladmins.id',
'=',
'tblticketfeedback.adminid'
)
->leftJoin(
'tbltickets',
'tbltickets.id',
'=',
'tblticketfeedback.ticketid'
);
if ($staffid) {
$query = $query->where('adminid', (int) $staffid);
}
$query->orderBy('datetime')
->select(
[
'tblticketfeedback.*',
Capsule::raw('CONCAT(tbladmins.firstname, \' \', tbladmins.lastname) as adminname'),
Capsule::raw('CONCAT(tid, \'|||\', title) as ticketinfo')
]
);
$ticketUrl = 'supporttickets.php?action=viewticket&id=';
foreach ($query->get() as $data) {
$data = (array) $data;
$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';
}
if (!trim($comments)) {
$comments = 'No Comments Left';
}
$datetime = Carbon::createFromFormat('Y-m-d H:i:s', $datetime)
->toAdminDateTimeFormat();
$subject = '';
$tickettid = 'Not Found';
if ($ticketinfo) {
$ticketinfo = explode('|||', $ticketinfo);
$tickettid = $ticketinfo[0];
$subject = $ticketinfo[1];
$tickettid = "<a href=\"{$ticketUrl}{$ticketid}\" target=\"_blank\">{$tickettid}</a>";
}
$reportdata["tablevalues"][] = [
$tickettid,
$adminname,
$subject,
$datetime,
$rating,
nl2br($comments),
GeoIp::getLookupHtmlAnchor($ip),
];
}
clients_by_country.php 0000644 00000004335 14736262117 0011211 0 ustar 00 <?php
use WHMCS\Database\Capsule;
use WHMCS\Utility\Country;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Clients by Country";
$reportdata["description"] = "This report shows the total number of active services per country, as well as total active unique clients per country in the table below.";
$reportdata["tableheadings"] = array("Country","Active Services","Active Clients");
$countries = new Country();
$countries = $countries->getCountryNameArray();
$clientstats = array();
$results = Capsule::table('tblclients')
->select(Capsule::raw('country, count(*) as `count`'))
->where('Status', '=', 'Active')
->groupBy('country')
->orderBy('country', 'asc')
->get()
->all();
foreach ($results as $result) {
$clientstats[$result->country] = $result->count;
}
$results = Capsule::table('tblhosting')
->select(Capsule::raw('tblclients.country, count(*) as `count`'))
->join('tblclients', 'tblclients.id', '=', 'tblhosting.userid')
->where('domainstatus', '=', 'Active')
->groupBy('country')
->orderBy('country', 'asc')
->get()
->all();
foreach ($results as $result) {
$countryname = $countries[$result->country];
if ($countryname) {
$reportdata["tablevalues"][] = [
$countryname,
$result->count,
$clientstats[$result->country],
];
$chartdata['rows'][] = [
'c' => [
['v' => $result->country],
['v' => $result->count],
['v' => $clientstats[$result->country]],
]
];
unset($clientstats[$result->country]);
}
}
foreach ($clientstats AS $country=>$activeclient) {
$countryname = $countries[$country];
if ($countryname) {
$reportdata["tablevalues"][] = array($countryname,'0',$activeclient);
$chartdata['rows'][] = array('c'=>array(array('v'=>$country),array('v'=>0),array('v'=>$activeclient)));
}
}
$chartdata['cols'][] = array('label'=>'Country','type'=>'string');
$chartdata['cols'][] = array('label'=>'Active Services','type'=>'number');
$args = array();
$args['legendpos'] = 'right';
$reportdata["headertext"] = $chart->drawChart('Geo',$chartdata,$args,'600px');
promotions_usage.php 0000644 00000007043 14736262117 0010667 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Promotions Usage Report";
$reportdata["description"] = "This report shows usage statistics for each promotional code.";
$range = App::getFromRequest('range');
if (!$range) {
$today = Carbon::today()->endOfDay();
$lastWeek = Carbon::today()->subDays(6)->startOfDay();
$range = $lastWeek->toAdminDateFormat() . ' - ' . $today->toAdminDateFormat();
}
$reportdata['headertext'] = '';
if (!$print) {
$reportdata["headertext"] = <<<EOF
<form method="post" action="reports.php?report={$report}">
<div class="report-filters-wrapper">
<div class="inner-container">
<h3>Filters</h3>
<div class="row">
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterDate">{$dateRangeText}</label>
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="range"
value="{$range}"
class="form-control date-picker-search"
placeholder="{$optionalText}"
/>
</div>
</div>
</div>
</div>
<button type="submit" class="btn btn-primary">
{$aInt->lang('global', 'apply')}
</button>
</div>
</div>
</form>
EOF;
}
$reportdata["tableheadings"] = [
"Coupon Code",
"Discount Type",
"Value",
"Recurring",
"Notes",
"Usage Count",
"Total Revenue",
];
$i = 0;
$dateRange = Carbon::parseDateRangeValue($range);
$datefrom = $dateRange['from']->toDateTimeString();
$dateto = $dateRange['to']->toDateTimeString();
$results = Capsule::table('tblpromotions')
->orderBy('code', 'asc')
->get()
->all();
foreach ($results as $result) {
$code = $result->code;
$type = $result->type;
$value = $result->value;
$recurring = $result->recurring;
$notes = $result->notes;
$rowcount = $rowtotal = 0;
$reportdata["drilldown"][$i]["tableheadings"] = [
"Order ID",
"Order Date",
"Order Number",
"Order Total",
"Order Status",
];
$orders = Capsule::table('tblorders')
->where('promocode', '=', $code)
->whereBetween('date', [$datefrom, $dateto])
->orderBy('id', 'asc')
->get()
->all();
foreach ($orders as $order) {
$orderid = $order->id;
$ordernum = $order->ordernum;
$orderdate = $order->date;
$ordertotal = $order->amount;
$orderstatus = $order->status;
$rowcount++;
$rowtotal += $ordertotal;
$reportdata["drilldown"][$i]["tablevalues"][] = [
"<a href=\"orders.php?action=view&id={$orderid}\">{$orderid}</a>",
fromMySQLDate($orderdate),
$ordernum,
$ordertotal,
$orderstatus,
];
}
$reportdata["tablevalues"][$i] = [
$code,
$type,
$value,
$recurring,
$notes,
$rowcount,
format_as_currency($rowtotal),
];
$i++;
}
income_by_product.php 0000644 00000021662 14736262117 0011001 0 ustar 00 <?php
use Illuminate\Database\Query\Builder;
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$pmonth = str_pad((int)$month, 2, "0", STR_PAD_LEFT);
$reportdata["title"] = "Income by Product for ".$months[(int)$month]." ".$year;
$reportdata["description"] = "This report provides a breakdown per product/service of invoices paid in a given month. Please note this excludes overpayments & other payments made to deposit funds (credit), and includes invoices paid from credit added in previous months, and thus may not match the income total for the month.";
$reportdata["currencyselections"] = true;
$reportdata["tableheadings"] = array("Product Name","Units Sold","Value");
$products = $addons = array();
$dateRange = Carbon::create(
$year,
$month,
1
);
# Loop Through Products
$result = Capsule::table('tblinvoiceitems')
->join('tblinvoices', 'tblinvoices.id', '=', 'tblinvoiceitems.invoiceid')
->join('tblhosting', 'tblhosting.id', '=', 'tblinvoiceitems.relid')
->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
->whereBetween(
'tblinvoices.datepaid',
[
$dateRange->startOfMonth()->toDateTimeString(),
$dateRange->endOfMonth()->toDateTimeString(),
]
)
->where(function (Builder $query) {
$query->where('tblinvoiceitems.type', 'Hosting')
->orWhere('tblinvoiceitems.type', 'Setup')
->orWhere('tblinvoiceitems.type', 'like', 'ProrataProduct%');
})
->where('currency', $currencyid)
->groupBy('tblhosting.packageid')
->select(
[
Capsule::raw('tblhosting.packageid as packageId'),
Capsule::raw('COUNT(*) as unitsSold'),
Capsule::raw('SUM(tblinvoiceitems.amount) as amount')
]
)->get();
foreach ($result as $data) {
$products[$data->packageId] = [
'amount' => $data->amount,
'unitssold' => $data->unitsSold,
];
}
$result = Capsule::table('tblinvoiceitems')
->join('tblinvoices', 'tblinvoices.id', '=', 'tblinvoiceitems.invoiceid')
->join('tblhosting', 'tblhosting.id', '=', 'tblinvoiceitems.relid')
->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
->whereBetween(
'tblinvoices.datepaid',
[
$dateRange->startOfMonth()->toDateTimeString(),
$dateRange->endOfMonth()->toDateTimeString(),
]
)
->where('tblinvoiceitems.type', 'PromoHosting')
->where('currency', $currencyid)
->groupBy('tblhosting.packageid')
->select(
[
Capsule::raw('tblhosting.packageid as packageId'),
Capsule::raw('COUNT(*) as unitsSold'),
Capsule::raw('SUM(tblinvoiceitems.amount) as amount')
]
)
->get();
foreach ($result as $data) {
$products[$data->packageId]["amount"] += $data->amount;
}
# Loop Through Addons
$result = Capsule::table('tblinvoiceitems')
->join('tblinvoices', 'tblinvoices.id', '=', 'tblinvoiceitems.invoiceid')
->join('tblhostingaddons', 'tblhostingaddons.id', '=', 'tblinvoiceitems.relid')
->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
->whereBetween(
'tblinvoices.datepaid',
[
$dateRange->startOfMonth()->toDateTimeString(),
$dateRange->endOfMonth()->toDateTimeString(),
]
)
->where('tblinvoiceitems.type', 'Addon')
->where('currency', $currencyid)
->groupBy('tblhostingaddons.addonid')
->select(
[
Capsule::raw('tblhostingaddons.addonid as addonId'),
Capsule::raw('COUNT(*) as unitsSold'),
Capsule::raw('SUM(tblinvoiceitems.amount) as amount')
]
)->get()
->all();
foreach ($result as $data) {
$addons[$data->addonId] = [
'amount' => $data->amount,
'unitssold' => $data->unitsSold,
];
}
$total = 0;
$itemtotal = 0;
$firstdone = false;
$result = Capsule::table('tblproducts')
->join(
'tblproductgroups',
'tblproductgroups.id',
'=',
'tblproducts.gid'
)
->orderBy('tblproductgroups.order')
->orderBy('tblproducts.order')
->orderBy('tblproducts.name')
->get(
[
'tblproducts.id',
'tblproducts.name',
Capsule::raw('`tblproductgroups`.`name` as groupname')
]
)
->all();
foreach ($result as $data) {
$pid = $data->id;
$group = $data->groupname;
$prodname = $data->name;
if ($group!=$prevgroup) {
$total += $itemtotal;
if ($firstdone) {
$reportdata["tablevalues"][] = array('','<strong>Sub-Total</strong>','<strong>'.formatCurrency($itemtotal).'</strong>');
$chartdata['rows'][] = array('c'=>array(array('v'=>$prevgroup),array('v'=>$itemtotal,'f'=>formatCurrency($itemtotal))));
}
$reportdata["tablevalues"][] = array("**<strong>$group</strong>");
$itemtotal = 0;
}
$amount = $products[$pid]["amount"];
$number = $products[$pid]["unitssold"];
$itemtotal += $amount;
if (!$amount) $amount="0.00";
if (!$number) $number="0";
$amount = formatCurrency($amount);
$reportdata["tablevalues"][] = array($prodname,$number,$amount);
$prevgroup = $group;
$firstdone = true;
}
$total += $itemtotal;
$reportdata["tablevalues"][] = array('','<strong>Sub-Total</strong>','<strong>'.formatCurrency($itemtotal).'</strong>');
$chartdata['rows'][] = array('c'=>array(array('v'=>$group),array('v'=>$itemtotal,'f'=>formatCurrency($itemtotal))));
$reportdata["tablevalues"][] = array("**<strong>Addons</strong>");
$itemtotal = 0;
$result = Capsule::table('tbladdons')
->orderBy('name')
->get(
[
'id',
'name',
]
)
->all();
foreach ($result as $data) {
$addonid = $data->id;
$prodname = $data->name;
$amount = $addons[$addonid]["amount"];
$number = $addons[$addonid]["unitssold"];
$itemtotal += $amount;
if (!$amount) $amount="0.00";
if (!$number) $number="0";
$amount = formatCurrency($amount);
$reportdata["tablevalues"][] = array($prodname,$number,$amount);
$prevgroup = $group;
}
$itemtotal += $addons[0]["amount"];
$number = $addons[0]["unitssold"];
$amount = $addons[0]["amount"];
if (!$amount) $amount="0.00";
if (!$number) $number="0";
$reportdata["tablevalues"][] = array('Miscellaneous Custom Addons',$number,formatCurrency($amount));
$total += $itemtotal;
$reportdata["tablevalues"][] = array('','<strong>Sub-Total</strong>','<strong>'.formatCurrency($itemtotal).'</strong>');
$chartdata['rows'][] = array('c'=>array(array('v'=>"Addons"),array('v'=>$itemtotal,'f'=>formatCurrency($itemtotal))));
$itemtotal = 0;
$reportdata["tablevalues"][] = array("**<strong>Miscellaneous</strong>");
$data = Capsule::table('tblinvoiceitems')
->join('tblinvoices', 'tblinvoices.id', '=', 'tblinvoiceitems.invoiceid')
->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
->whereBetween(
'tblinvoices.datepaid',
[
$dateRange->startOfMonth()->toDateTimeString(),
$dateRange->endOfMonth()->toDateTimeString(),
]
)
->where('tblinvoiceitems.type', 'Item')
->where('tblclients.currency', $currencyid)
->first(
[
Capsule::raw('COUNT(*) as number'),
Capsule::raw('SUM(tblinvoiceitems.amount) as amount')
]
);
$itemtotal += $data->amount;
$number = $data->number;
$amount = $data->amount;
if (!$amount) $amount="0.00";
if (!$number) $number="0";
$reportdata["tablevalues"][] = array('Billable Items',$number,formatCurrency($amount));
$data = Capsule::table('tblinvoiceitems')
->join('tblinvoices', 'tblinvoices.id', '=', 'tblinvoiceitems.invoiceid')
->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
->whereBetween(
'tblinvoices.datepaid',
[
$dateRange->startOfMonth()->toDateTimeString(),
$dateRange->endOfMonth()->toDateTimeString(),
]
)
->where('tblinvoiceitems.type', '')
->where('tblclients.currency', $currencyid)
->first(
[
Capsule::raw('COUNT(*) as number'),
Capsule::raw('SUM(tblinvoiceitems.amount) as amount')
]
);
$itemtotal += $data->amount;
$number = $data->number;
$amount = $data->amount;
$reportdata["tablevalues"][] = array('Custom Invoice Line Items',$number,formatCurrency($amount));
$total += $itemtotal;
$reportdata["tablevalues"][] = array('','<strong>Sub-Total</strong>','<strong>'.formatCurrency($itemtotal).'</strong>');
$chartdata['rows'][] = array('c'=>array(array('v'=>"Miscellaneous"),array('v'=>$itemtotal,'f'=>formatCurrency($itemtotal))));
$total = formatCurrency($total);
$chartdata['cols'][] = array('label'=>'Days Range','type'=>'string');
$chartdata['cols'][] = array('label'=>'Value','type'=>'number');
$args = array();
$args['legendpos'] = 'right';
$reportdata["footertext"] = $chart->drawChart('Pie',$chartdata,$args,'300px');
$reportdata["monthspagination"] = true;
vat_moss.php 0000644 00000015747 14736262117 0007137 0 ustar 00 <?php
/**
* VAT MOSS Settlement Data Report
*
* This report is designed to provide the information necessary to be
* able to complete a VAT MOSS return.
*
* @package WHMCS
* @author WHMCS Limited <development@whmcs.com>
* @copyright Copyright (c) WHMCS Limited 2005-2019
* @license https://www.whmcs.com/license/ WHMCS Eula
* @version $Id$
* @link https://www.whmcs.com/
*/
use WHMCS\Billing\Tax\Vat;
use WHMCS\Database\Capsule;
use WHMCS\Utility\Country;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
// Define report parameters.
$reportdata['title'] = "VAT MOSS Settlement Data";
$reportdata['description'] = "This report provides the information needed to complete a VATMOSS return. "
. "Please check with your tax authority to confirm how you can upload your settlement data into the MOSS portal. "
. "You should also contact your MOSS registration country if you have any further questions in relation to the MOSS return.";
// Fetch input parameters.
$reportQuarter = $whmcs->get_req_var('reportquarter');
// List of EU Countries.
$euCountries = array_keys(Vat::EU_COUNTRIES);
// Quarter definitions.
$periodLabels = array(
1 => 'January - March',
2 => 'April - June',
3 => 'July - September',
4 => 'October - December',
);
// Initialise variables.
$queryStartDate = '';
$queryEndDate = '';
$selectHtml = '';
$currencyCode = (isset($currency['code'])) ? $currency['code'] : '';
// Build dropdown of quarters.
$periods = array();
for ($i = 2015; $i <= date("Y"); $i++) {
$quartersToShow = ($i < date("Y")) ? 4 : ceil(date("m") / 3);
for ($a = 1; $a <= $quartersToShow; $a++) {
$periodLabel = $i . ' Q' . $a . ' - ' . $periodLabels[$a];
$selectHtml .= '<option'
. ($periodLabel == $reportQuarter ? ' selected' : '')
. '>' . $periodLabel . '</option>';
if ($periodLabel == $reportQuarter) {
$queryStartDate = mktime(0, 0, 0, (($a-1)*3)+1, 1, $i);
$queryEndDate = mktime(0, 0, 0, ($a*3)+1, 0, $i);
}
}
}
// Form to select quarter.
$reportdata['description'] .= '<br /><br /><form method="post" action="?report=' . $report . '">
<div align="center">
Select Quarter:
<select name="reportquarter" class="form-control select-inline">
' . $selectHtml . '
</select>
<input type="submit" value="Generate Report" class="btn btn-primary" />
</div>
</form>
';
if (!$reportQuarter) {
$reportdata['headertext'] .= '<p align="center">Currency selection will become available on report generation.</p>';
}
// Generate report if period is selected.
if ($queryStartDate && $queryEndDate) {
$reportdata['currencyselections'] = true;
// Define table headings.
$reportdata['tableheadings'] = array(
'Country Name',
'Country Code',
'VAT Rate',
'Number of Invoices',
'Total Value Invoiced (Excl. VAT)',
'Total VAT Collected',
'Currency',
);
// Output reporting period.
$reportdata['headertext'] .= '<h2 style="margin:0;">For Period '
. date("jS F Y", $queryStartDate)
. ' to '
. date("jS F Y", $queryEndDate)
. '</h2>';
// Fetch country names.
$countries = new Country();
$countries = $countries->getCountryNameArray();
// Fetch all configured country based tax rates.
$taxRates = Capsule::table('tbltax')
->where('state', '')
->where('country', '!=', '')
->pluck('taxrate', 'country')
->all();
// Build query to calculate data for report.
$results = Capsule::table('tblinvoices')
->select(
'tblclients.country',
'tblinvoicedata.country as invoice_country',
Capsule::raw('count(tblinvoices.id) as `invoicecount`'),
Capsule::raw('sum(tblinvoices.subtotal) as `totalinvoiced`'),
Capsule::raw('sum(tblinvoices.tax + tblinvoices.tax2) as `totalvat`')
)
->distinct()
->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
->leftJoin('tblinvoicedata', 'tblinvoices.id', '=', 'tblinvoicedata.invoice_id')
->leftJoin('tblinvoiceitems', function ($join) {
$join->on('tblinvoiceitems.invoiceid', '=', 'tblinvoices.id');
$join->on(function ($join) {
$join
->on('tblinvoiceitems.type', '=', Capsule::raw('"Add Funds"'))
->orOn('tblinvoiceitems.type', '=', Capsule::raw('"Invoice"'));
});
})
->where(function ($query) {
$query->where('tblinvoices.tax', '>', '0')
->orWhere('tblinvoices.tax2', '>', '0');
})
->where(function ($query) use ($euCountries) {
$query->where(function ($query) use ($euCountries) {
$query->whereNotNull('tblinvoicedata.country')
->whereIn('tblinvoicedata.country', $euCountries);
})
->orWhere(function ($query) use ($euCountries) {
$query->whereNull('tblinvoicedata.country')
->whereIn('tblclients.country', $euCountries);
});
})
->whereBetween('datepaid', [
date("Y-m-d", $queryStartDate),
date("Y-m-d", $queryEndDate) . ' 23:59:59',
])
->where('tblinvoices.status', '=', 'Paid')
->where('currency', '=', (int) $currencyid)
->whereNull('tblinvoiceitems.id')
->groupBy('tblclients.country')
->orderBy('tblclients.country', 'asc')
->get();
foreach ($results as $result) {
$countryCode = $result->country;
if ($result->invoice_country) {
$countryCode = $result->invoice_country;
}
$invoiceCount = $result->invoicecount;
$totalInvoiced = $result->totalinvoiced;
$totalVat = $result->totalvat;
if (isset($countries[$countryCode])) {
$countryName = $countries[$countryCode];
} else {
$countryName = 'Unrecognised Country';
}
if (isset($taxRates[$countryCode])) {
$taxRate = $taxRates[$countryCode] . '%';
} else {
$taxRate = 'Tax Rate Not Found';
}
$reportdata['tablevalues'][] = [
$countryName,
$countryCode,
$taxRate,
$invoiceCount,
$totalInvoiced,
$totalVat,
$currencyCode,
];
}
$reportdata['footertext'] = "* If a country does not appear in the report, then no VAT was collected "
. "from customers in that country during the period selected.";
$reportdata['footertext'] .= "<br />Isle of Man (GB) and Monaco (FR) are listed in this report as "
. "EU Overseas Territories of their respective countries and should be included in any figures "
. "provided to tax authorities. "
. "<a href='http://europa.eu/youreurope/business/vat-customs/cross-border/index_en.htm' "
. "target='_blank'>More Information</a>";
}
top_10_clients_by_income.php 0000644 00000004555 14736262117 0012146 0 ustar 00 <?php
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Top 10 Clients by Income";
$reportdata["description"] = "This report shows the 10 clients with the highest net income according to the transactions entered in WHMCS.";
$reportdata["tableheadings"] = array("Client ID","Client Name","Total Amount In","Total Fees","Total Amount Out","Balance");
$results = Capsule::table('tblaccounts')
->select(
'tblclients.id',
'tblclients.firstname',
'tblclients.lastname',
Capsule::raw('SUM(tblaccounts.amountin/tblaccounts.rate) AS amountIn'),
Capsule::raw('SUM(tblaccounts.fees/tblaccounts.rate) AS fees'),
Capsule::raw('SUM(tblaccounts.amountout/tblaccounts.rate) AS amountOut'),
Capsule::raw('SUM((tblaccounts.amountin/tblaccounts.rate)-(tblaccounts.fees/tblaccounts.rate)-(tblaccounts.amountout/tblaccounts.rate)) AS balance'),
'tblaccounts.rate'
)
->join('tblclients', 'tblclients.id', '=', 'tblaccounts.userid')
->groupBy('userid')
->orderBy('balance', 'desc')
->take(10)
->get()
->all();
foreach ($results as $result) {
$userid = $result->id;
$currency = getCurrency();
$rate = ($result->rate == "1.00000") ? '' : '*';
$clientlink = '<a href="clientssummary.php?userid=' . $result->id . '">';
$reportdata["tablevalues"][] = [
$clientlink . $result->id . '</a>',
$clientlink . $result->firstname . ' ' . $result->lastname . '</a>',
formatCurrency($result->amountIn) . " $rate",
formatCurrency($result->fees) . " $rate",
formatCurrency($result->amountOut) . " $rate",
formatCurrency($result->balance) . " $rate",
];
$chartdata['rows'][] = [
'c' => [
[
'v' => $result->firstname . ' ' . $result->lastname,
],
[
'v' => round($result->balance, 2),
'f' => formatCurrency($result->balance),
]
]
];
}
$reportdata["footertext"] = "<p>* denotes converted to default currency</p>";
$chartdata['cols'][] = array('label'=>'Client','type'=>'string');
$chartdata['cols'][] = array('label'=>'Balance','type'=>'number');
$args = array();
$args['legendpos'] = 'right';
$reportdata["headertext"] = $chart->drawChart('Pie', $chartdata, $args, '300px');
client.php 0000644 00000011247 14736262117 0006551 0 ustar 00 <?php
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata['isPrintable'] = false;
$reportdata['canCsvExport'] = false;
$userid = App::getFromRequest('userid');
$onloadUserReplaceJs = '';
if ($userid) {
$onloadUserReplaceJs = 'jQuery("#selectUserid")[0].selectize.trigger("change");';
}
$reportdata["title"] = "Client Data Export";
$reportdata["description"] = "This report allows you to generate a JSON export of data relating to a given client. You can choose which data points you wish to be included in the export below.";
$reportdata["headertext"] = '
<form method="post" action="' . routePath('admin-client-export', 'xxx') . '" data-route="' . routePath('admin-client-export', 'xxx') . '" id="frmClientExport">
<input type="hidden" name="export" value="true">
<br>
<p>
Choose the client to export<br>
' . $aInt->clientsDropDown($userid) . '
</p>
<div style="background-color:#f8f8f8;margin:10px 0 20px;padding:20px;border-radius:4px;">
<div class="row">
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="profile" checked>
Profile Data
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="paymethods">
Pay Methods
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="contacts">
Contacts
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="services">
Products/Services
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="domains">
Domains
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="billableitems">
Billable Items
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="invoices">
Invoices
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="quotes">
Quotes
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="transactions">
Transactions
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="tickets">
Tickets
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="emails">
Emails
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="notes">
Notes
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="consenthistory">
Consent History
</label>
</div>
<div class="col-sm-3">
<label class="checkbox-inline">
<input type="checkbox" name="exportdata[]" value="activitylog">
Activity Log
</label>
</div>
</div>
</div>
<button type="submit" class="btn btn-default"' . ($userid ? '' : ' disabled="disabled"') . ' id="btnExport">
<i class="fas fa-download fa-fw"></i>
Generate and Download Export
</button>
<br><br>
<small>* Generating an export for a client with a substantial amount of history may take a while</small>
</form>
<script>
$(document).ready(function() {
$("#selectUserid")[0].selectize.on("change", function() {
var userId = this.getValue();
if (userId) {
$("#frmClientExport").attr("action", $("#frmClientExport").data("route").replace("xxx", userId));
$("#btnExport").removeProp("disabled");
}
});
' . $onloadUserReplaceJs . '
});
</script>
';
sales_tax_liability.php 0000644 00000013233 14736262117 0011315 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Sales Tax Liability";
$reportdata["description"] = "This report shows sales tax liability for the selected period";
$reportdata["currencyselections"] = true;
$range = App::getFromRequest('range');
if (!$range) {
$today = Carbon::today()->endOfDay();
$lastWeek = Carbon::today()->subDays(6)->startOfDay();
$range = $lastWeek->toAdminDateFormat() . ' - ' . $today->toAdminDateFormat();
}
$currencyID = (int) $currencyid;
$reportdata['headertext'] = '';
if (!$print) {
$reportdata['headertext'] = <<<HTML
<form method="post" action="reports.php?report={$report}¤cyid={$currencyid}&calculate=true">
<div class="report-filters-wrapper">
<div class="inner-container">
<h3>Filters</h3>
<div class="row">
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterDate">{$dateRangeText}</label>
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="range"
value="{$range}"
class="form-control date-picker-search"
/>
</div>
</div>
</div>
</div>
<button type="submit" class="btn btn-primary">
{$aInt->lang('reports', 'generateReport')}
</button>
</div>
</div>
</form>
HTML;
}
if ($calculate) {
$dateRange = Carbon::parseDateRangeValue($range);
$queryStartDate = $dateRange['from']->toDateTimeString();
$queryEndDate = $dateRange['to']->toDateTimeString();
$result = Capsule::table('tblinvoices')
->select(
Capsule::raw('count(*) as `count`'),
Capsule::raw('sum(total) as `total`'),
Capsule::raw('sum(tblinvoices.credit) as `credit`'),
Capsule::raw('sum(tax) as `tax`'),
Capsule::raw('sum(tax2) as `tax2`')
)
->distinct()
->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
->leftJoin('tblinvoiceitems', function ($join) {
$join->on('tblinvoiceitems.invoiceid', '=', 'tblinvoices.id');
$join->on(function ($join) {
$join
->on('tblinvoiceitems.type', '=', Capsule::raw('"Add Funds"'))
->orOn('tblinvoiceitems.type', '=', Capsule::raw('"Invoice"'));
});
})
->whereBetween('tblinvoices.datepaid', [$queryStartDate, $queryEndDate])
->where('tblinvoices.status', '=', 'Paid')
->where('tblclients.currency', '=', $currencyID)
->whereNull('tblinvoiceitems.id')
->first();
$numinvoices = $result->count;
$total = ($result->total + $result->credit);
$tax = $result->tax;
$tax2 = $result->tax2;
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(
$aInt->lang('fields', 'invoiceid'),
$aInt->lang('fields', 'clientname'),
$aInt->lang('fields', 'invoicedate'),
$aInt->lang('fields', 'datepaid'),
$aInt->lang('fields', 'subtotal'),
$aInt->lang('fields', 'tax'),
$aInt->lang('fields', 'credit'),
$aInt->lang('fields', 'total'),
);
$results = Capsule::table('tblinvoices')
->select('tblinvoices.*', 'tblclients.firstname', 'tblclients.lastname')
->distinct()
->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
->leftJoin('tblinvoiceitems', function ($join) {
$join->on('tblinvoiceitems.invoiceid', '=', 'tblinvoices.id');
$join->on(function ($join) {
$join
->on('tblinvoiceitems.type', '=', Capsule::raw('"Add Funds"'))
->orOn('tblinvoiceitems.type', '=', Capsule::raw('"Invoice"'));
});
})
->whereBetween('tblinvoices.datepaid', [$queryStartDate, $queryEndDate])
->where('tblinvoices.status', '=', 'Paid')
->where('tblclients.currency', '=', $currencyID)
->whereNull('tblinvoiceitems.id')
->orderBy('date', 'asc')
->get()
->all();
foreach ($results as $result) {
$id = $result->id;
$userid = $result->userid;
$client = "{$result->firstname} {$result->lastname}";
$date = fromMySQLDate($result->date);
$datepaid = fromMySQLDate($result->datepaid);
$currency = getCurrency($userid);
$subtotal = $result->subtotal;
$credit = $result->credit;
$tax = ($result->tax + $result->tax2);
$total = ($result->total + $credit);
$reportdata["tablevalues"][] = [
"{$id}",
"{$client}",
"{$date}",
"{$datepaid}",
format_as_currency($subtotal),
format_as_currency($tax),
format_as_currency($credit),
format_as_currency($total),
];
}
$data["footertext"]="This report excludes invoices that affect a clients credit balance "
. "since this income will be counted and reported when it is applied to invoices for products/services.";
clients.php 0000644 00000013457 14736262117 0006741 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Clients";
$filterfields = [
'id' => 'ID',
'firstname' => 'First Name',
'lastname' => 'Last Name',
'companyname' => 'Company Name',
'email' => 'Email',
'address1' => 'Address 1',
'address2' => 'Address 2',
'city' => 'City',
'state' => 'State',
'postcode' => 'Postcode',
'country' => 'Country',
'phonenumber' => 'Phone Number',
'currency' => 'Currency',
'groupid' => 'Client Group ID',
'credit' => 'Credit',
'datecreated' => 'Creation Date',
'notes' => 'Notes',
'status' => 'Status'
];
$dateRangeFields = [
'datecreated' => 'Creation Date',
];
$removedDateRangeFields = array_diff($filterfields, $dateRangeFields);
$reportdata["description"] = $reportdata["headertext"] = '';
$incfields = $whmcs->get_req_var('incfields');
$filterfield = $whmcs->get_req_var('filterfield');
$filtertype = $whmcs->get_req_var('filtertype');
$filterq = $whmcs->get_req_var('filterq');
$createDateRange = App::getFromRequest('createDateRange');
if (!is_array($incfields)) {
$incfields = [];
}
if (!is_array($filterfield)) {
$filterfield = [];
}
if (!is_array($filtertype)) {
$filtertype = [];
}
if (!is_array($filterq)) {
$filterq = [];
}
if (!$print) {
$reportdata["description"] = "This report can be used to generate a custom export"
. " of clients by applying up to 5 filters. CSV Export is available via the Tools menu to the right.";
$reportdata["headertext"] = '<form method="post" action="reports.php?report=' . $report . '">
<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
<tr><td width="20%" class="fieldlabel">Fields to Include</td><td class="fieldarea"><table width="100%"><tr>';
$i=0;
foreach ($filterfields as $k => $v) {
$reportdata["headertext"] .= '<td width="20%"><input type="checkbox" name="incfields[]" value="' . $k . '" id="fd' . $k . '"';
if (in_array($k, $incfields)) {
$reportdata["headertext"] .= ' checked';
}
$reportdata["headertext"] .= ' /> <label for="fd' . $k . '">' . $v . '</label></td>';
$i++;
if (($i%5)==0) {
$reportdata["headertext"] .= '</tr><tr>';
}
}
$reportdata["headertext"] .= '</tr></table></td></tr>';
for ($i = 1; $i <= 5; $i ++) {
$reportdata["headertext"] .= '<tr><td width="20%" class="fieldlabel">Filter ' . $i . '</td><td class="fieldarea"><select name="filterfield[' . $i . ']" class="form-control select-inline"><option value="">None</option>';
foreach ($removedDateRangeFields as $k => $v) {
$reportdata["headertext"] .= '<option value="'.$k.'"';
if (isset($filterfield[$i]) && $filterfield[$i]==$k) {
$reportdata["headertext"] .= ' selected';
}
$reportdata["headertext"] .= '>'.$v.'</option>';
}
$reportdata["headertext"] .= '</select> <select name="filtertype[' . $i . ']" class="form-control select-inline">';
$reportdata["headertext"] .= '<option value="=">Exact Match</option><option value="like"';
if (isset($filtertype[$i]) && $filtertype[$i] == "like") {
$reportdata["headertext"] .= ' selected';
}
$reportdata["headertext"] .= '>Containing</option></select>'
. ' <input type="text" name="filterq[' . $i . ']" class="form-control input-inline input-250" value="' . (isset($filterq[$i]) ? $filterq[$i] : '') . '" /></td></tr>';
}
$reportdata["headertext"] .= <<<HTML
<tr>
<td width="20%" class="fieldlabel">Creation Date Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="createDateRange"
value="{$createDateRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
</table>
<p align="center"><input type="submit" value="Filter" class="btn btn-primary"/></p>
</form>
HTML;
}
if (count($incfields)) {
$filters = [];
foreach ($filterfield as $i => $val) {
if ($val && array_key_exists($val, $filterfields)) {
if ($filtertype[$i] === 'like') {
$filterq[$i] = '%' . $filterq[$i] . '%';
}
$filters[] = [
"name" => $filterfield[$i],
"operator" => $filtertype[$i],
"value" => $filterq[$i],
];
}
}
$fieldlist = [];
foreach ($incfields as $fieldname) {
if (array_key_exists($fieldname, $filterfields)) {
$reportdata["tableheadings"][] = $filterfields[$fieldname];
$fieldlist[] = $fieldname;
}
}
$query = Capsule::table('tblclients')
->addSelect($fieldlist)
->where($filters);
if ($createDateRange) {
$dateRange = Carbon::parseDateRangeValue($createDateRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('datecreated', [$fromdate, $todate]);
}
$results = $query->get()->all();
foreach ($results as $result) {
$result = (array) $result;
if (isset($result['currency'])) {
$result['currency'] = Capsule::table('tblcurrencies')
->where('id', '=', $result['currency'])
->value('code');
}
$reportdata["tablevalues"][] = $result;
}
}
customer_retention_time.php 0000644 00000022112 14736262117 0012232 0 ustar 00 <?php
use Illuminate\Database\Capsule\Manager as Capsule;
use WHMCS\Product\Group;
use WHMCS\Product\Product;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
/** @var WHMCS\Application $whmcs */
$include_active = $whmcs->get_req_var('include_active');
$checked = $include_active ? ' checked' : '';
$reportdata["title"] = "Average Customer Retention Time";
$reportdata["description"] = "This report calculates and provides you with the average lifetime of products, "
. "services, addons and domains - that is the number of days between the registration date and the termination "
. "date. Averages are displayed by product and the associated billing cycle, and are displayed both as a "
. "number of days value and a years/months value.";
$reportdata["headertext"] = <<<EOT
<div class="text-center">
<form method="post" action="{$_SERVER['PHP_SELF']}?report={$report}">
<label class="checkbox-inline">
<input type="checkbox" value="true" onchange="this.form.submit()" name="include_active"{$checked}>
Include Active Products & Services (assuming active until Next Due Date) in Calculation of Average Retention Time
</label>
</form>
</div>
EOT;
$statuses = array('Cancelled', 'Terminated', 'Expired');
if ($include_active) {
$statuses = array_merge($statuses, array('Active', 'Suspended',));
}
$reportdata['tableheadings'] = array(
AdminLang::trans('products.productname'),
AdminLang::trans('fields.billingcycle'),
AdminLang::trans('reports.productCount'),
AdminLang::trans('reports.averageDaysActive'),
AdminLang::trans('reports.averageYearsMonthsActive'),
);
/** @var WHMCS\Product\Group[] $productGroups */
$productGroups = Group::all();
foreach ($productGroups as $productGroup) {
$groupRows = array();
/** @var WHMCS\Product\Product[] $products */
$products = Product::where('gid', '=', $productGroup->id)->get();
foreach ($products as $product) {
/** @var StdClass[] $services */
$services = Capsule::table('tblhosting')
->where('packageid', '=', $product->id)
->where('regdate', '!=', '0000-00-00')
->whereIn('domainstatus', $statuses)
->whereNotIn('billingcycle', array('Free Account', 'Free', 'One Time'))
->selectRaw('count(id) as count, billingcycle, AVG(DATEDIFF(IF(`termination_date` != \'0000-00-00\', `termination_date`, `nextduedate`), `regdate`)) as avg_days')
->groupBy('billingcycle')
->get()
->all();
if ($services) {
foreach ($services as $service) {
$dateTime = new DateTime();
$newDateTime = $dateTime->diff(
new DateTime(date("Y-m-d H:i:s", strtotime(sprintf('-%s Days', round($service->avg_days)))))
);
$yearsMonths = '';
if ($newDateTime->y) {
$yearsMonths .= $newDateTime->y . ' ' . AdminLang::trans('calendar.years');
}
$decimalDays = 0;
if ($newDateTime->d) {
$decimalDays = round($newDateTime->d / 30, 1);
}
$yearsMonths .= ' ' . ($newDateTime->m + $decimalDays) . ' ' . (
$newDateTime->m == 1 && $decimalDays == 0
? AdminLang::trans('calendar.month')
: AdminLang::trans('calendar.months')
);
$billingCycle = strtolower(str_replace(array(' ', '-',), '', $service->billingcycle));
$groupRows[] = array(
$product->name,
AdminLang::trans('billingcycles.' . $billingCycle),
$service->count,
round($service->avg_days),
$yearsMonths,
);
}
}
}
if ($groupRows) {
$reportdata['tablevalues'][][] = "**{$productGroup->name}";
foreach ($groupRows as $row) {
$reportdata['tablevalues'][] = $row;
}
}
}
/** @var StdClass[] $productAddons */
$productAddons = Capsule::table('tbladdons')->get()->all();
$addonRows = array();
foreach ($productAddons as $productAddon) {
/** @var StdClass[] $addons */
$addons = Capsule::table('tblhostingaddons')
->where('addonid', '=', $productAddon->id)
->where('regdate', '!=', '0000-00-00')
->whereIn('status', $statuses)
->whereNotIn('billingcycle', array('Free Account', 'Free', 'One Time'))
->selectRaw('count(id) as count, billingcycle, AVG(DATEDIFF(IF(`termination_date` != \'0000-00-00\', `termination_date`, `nextduedate`), `regdate`)) as avg_days')
->groupBy('billingcycle')
->get()
->all();
if ($addons) {
foreach ($addons as $addon) {
$dateTime = new DateTime();
$newDateTime = $dateTime->diff(
new DateTime(date("Y-m-d H:i:s", strtotime(sprintf('-%s Days', round($addon->avg_days)))))
);
$yearsMonths = '';
if ($newDateTime->y) {
$yearsMonths .= $newDateTime->y . ' ' . AdminLang::trans('calendar.years');
}
$decimalDays = 0;
if ($newDateTime->d) {
$decimalDays = round($newDateTime->d / 30, 1);
}
$yearsMonths .= ' ' . ($newDateTime->m + $decimalDays) . ' ' . (
$newDateTime->m == 1 && $decimalDays == 0
? AdminLang::trans('calendar.month')
: AdminLang::trans('calendar.months')
);
$billingCycle = strtolower(str_replace(array(' ', '-',), '', $addon->billingcycle));
$addonRows[] = array(
$productAddon->name,
AdminLang::trans('billingcycles.' . $billingCycle),
$addon->count,
round($addon->avg_days),
$yearsMonths,
);
}
}
}
/**
* Custom Defined Addons
*/
/** @var StdClass[] $addons */
$addons = Capsule::table('tblhostingaddons')
->where('addonid', '=', '0')
->where('regdate', '!=', '0000-00-00')
->whereIn('status', $statuses)
->whereNotIn('billingcycle', array('Free Account', 'Free', 'One Time'))
->selectRaw('name, count(id) as count, billingcycle, AVG(DATEDIFF(IF(`termination_date` != \'0000-00-00\', `termination_date`, `nextduedate`), `regdate`)) as avg_days')
->groupBy('name', 'billingcycle')
->get()
->all();
if ($addons) {
foreach ($addons as $addon) {
$dateTime = new DateTime();
$newDateTime = $dateTime->diff(
new DateTime(date("Y-m-d H:i:s", strtotime(sprintf('-%s Days', round($addon->avg_days)))))
);
$yearsMonths = '';
if ($newDateTime->y) {
$yearsMonths .= $newDateTime->y . ' ' . AdminLang::trans('calendar.years');
}
$decimalDays = 0;
if ($newDateTime->d) {
$decimalDays = round($newDateTime->d / 30, 1);
}
$yearsMonths .= ' ' . ($newDateTime->m + $decimalDays) . ' ' . (
$newDateTime->m == 1 && $decimalDays == 0
? AdminLang::trans('calendar.month')
: AdminLang::trans('calendar.months')
);
$billingCycle = strtolower(str_replace(array(' ', '-',), '', $addon->billingcycle));
$addonRows[] = array(
$addon->name,
AdminLang::trans('billingcycles.' . $billingCycle),
$addon->count,
round($addon->avg_days),
$yearsMonths,
);
}
}
if ($addonRows) {
$reportdata['tablevalues'][][] = '**' . AdminLang::trans('addons.productaddons');
foreach ($addonRows as $row) {
$reportdata['tablevalues'][] = $row;
}
}
/** @var StdClass[] $domainTlds */
$domainTlds = Capsule::table('tbldomainpricing')->get()->all();
$domainRows = array();
foreach ($domainTlds as $domainTld) {
/** @var StdClass[] $domains */
$domains = Capsule::table('tbldomains')
->where('domain', 'LIKE', "%{$domainTld->extension}")
->where('registrationdate', '!=', '0000-00-00')
->whereIn('status', $statuses)
->selectRaw('count(id) as count, registrationperiod, AVG(DATEDIFF(IF(`expirydate` != \'0000-00-00\', `expirydate`, `nextduedate`), `registrationdate`)) as avg_days')
->groupBy('registrationperiod')
->get()
->all();
if ($domains) {
foreach ($domains as $domain) {
$dateTime = new DateTime();
$newDateTime = $dateTime->diff(
new DateTime(date("Y-m-d H:i:s", strtotime(sprintf('-%s Days', round($domain->avg_days)))))
);
$domainRows[] = array(
$domainTld->extension,
$domain->registrationperiod . ' ' . AdminLang::trans('calendar.years'),
$domain->count,
round($domain->avg_days),
$newDateTime->y . ' ' . AdminLang::trans('calendar.years'),
);
}
}
}
if ($domainRows) {
$reportdata['tablevalues'][][] = '**' . AdminLang::trans('fields.tld');
foreach ($domainRows as $row) {
$reportdata['tablevalues'][] = $row;
}
}
transactions.php 0000644 00000016117 14736262117 0010004 0 ustar 00 <?php
use WHMCS\Billing\Currency;
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Transactions";
$filterfields = [
'id' => 'ID',
'userid' => 'User ID',
'clientname' => 'Client Name',
'currency' => 'Currency',
'gateway' => 'Payment Method',
'date' => 'Date',
'description' => 'Description',
'invoiceid' => 'Invoice ID',
'transid' => 'Transaction ID',
'amountin' => 'Amount In',
'fees' => 'Fees',
'amountout' => 'Amount Out',
'rate' => 'Exchange Rate',
'refundid' => 'Refund ID'
];
$dateRangeFields = [
'date' => 'Date',
];
$removedDateRangeFields = array_diff($filterfields, $dateRangeFields);
$reportdata["description"] = $reportdata["headertext"] = '';
$incfields = $whmcs->get_req_var('incfields');
$filterfield = $whmcs->get_req_var('filterfield');
$filtertype = $whmcs->get_req_var('filtertype');
$filterq = $whmcs->get_req_var('filterq');
$range = App::getFromRequest('range');
if (!is_array($incfields)) {
$incfields = [];
}
if (!is_array($filterfield)) {
$filterfield = [];
}
if (!is_array($filtertype)) {
$filtertype = [];
}
if (!is_array($filterq)) {
$filterq = [];
}
if (!$print) {
$reportdata["description"] = "This report can be used to generate a custom export of"
. " transactions by applying up to 5 filters. CSV Export is available via the"
. " Tools menu to the right.";
$reportdata["headertext"] = '<form method="post" action="reports.php?report=' . $report . '">
<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
<tr><td width="20%" class="fieldlabel">Fields to Include</td><td class="fieldarea"><table width="100%"><tr>';
$i=0;
foreach ($filterfields as $k => $v) {
$reportdata["headertext"] .= '<td width="20%"><input type="checkbox" name="incfields[]" value="' . $k . '" id="fd' . $k . '"';
if (in_array($k, $incfields)) {
$reportdata["headertext"] .= ' checked';
}
$reportdata["headertext"] .= ' /> <label for="fd' . $k . '">'.$v.'</label></td>';
$i++;
if (($i%5)==0) {
$reportdata["headertext"] .= '</tr><tr>';
}
}
$reportdata["headertext"] .= '</tr></table></td></tr>';
for ($i = 1; $i <= 5; $i ++) {
$reportdata["headertext"] .= '<tr><td width="20%" class="fieldlabel">Filter ' . $i . '</td><td class="fieldarea"><select name="filterfield[' . $i . ']" class="form-control select-inline"><option value="">None</option>';
foreach ($removedDateRangeFields as $k => $v) {
$reportdata["headertext"] .= '<option value="' . $k . '"';
if (isset($filterfield[$i]) && $filterfield[$i]==$k) {
$reportdata["headertext"] .= ' selected';
}
$reportdata["headertext"] .= '>' . $v . '</option>';
}
$reportdata["headertext"] .= '</select> <select name="filtertype[' . $i . ']" class="form-control select-inline">'
. '<option value="=">Exact Match</option><option value="like"';
if (isset($filtertype[$i]) && $filtertype[$i]=="like") {
$reportdata["headertext"] .= ' selected';
}
$reportdata["headertext"] .= '>Containing</option></select>'
. ' <input type="text" name="filterq[' . $i . ']" class="form-control select-inline input-250" value="' . (isset($filterq[$i]) ? $filterq[$i] : '') . '" /></td></tr>';
}
$reportdata["headertext"] .= <<<HTML
<tr>
<td width="20%" class="fieldlabel">Date Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="range"
value="{$range}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
</table>
<p align="center"><input type="submit" value="Filter" class="btn btn-primary"/></p>
</form>
HTML;
}
if (count($incfields)) {
$query = Capsule::table('tblaccounts');
foreach ($filterfield as $i => $val) {
if ($val && array_key_exists($val, $filterfields)) {
if ($filtertype[$i] == 'like') {
$filterq[$i] = "%{$filterq[$i]}%";
}
if ($val == 'clientname') {
$query->whereRaw(
"concat(tblclients.firstname, ' ', tblclients.lastname) "
. "{$filtertype[$i]} '{$filterq[$i]}'"
);
} elseif ($val == 'currency') {
$currencyCode = strtoupper(trim($filterq[$i]));
$currencyId = Currency::where('code', $currencyCode)
->value('id');
$query->where(function ($query) use ($currencyId) {
return $query->where('tblclients.currency', (int) $currencyId)
->orWhere(function ($query) use ($currencyId) {
return $query->where('tblaccounts.userid', 0)
->where('tblaccounts.currency', (int) $currencyId);
});
});
} else {
$query->where(
"tblaccounts.{$filterfield[$i]}",
$filtertype[$i],
$filterq[$i]
);
}
}
}
foreach ($incfields as $fieldname) {
if (array_key_exists($fieldname, $filterfields)) {
$reportdata["tableheadings"][] = $filterfields[$fieldname];
if ($fieldname == "clientname") {
$query->addSelect(Capsule::raw("concat(tblclients.firstname, ' ', tblclients.lastname)"));
} else {
$query->addSelect("tblaccounts.{$fieldname}");
}
}
}
if (in_array('currency', $incfields) && !in_array('userid', $incfields)) {
$query->addSelect('tblaccounts.userid');
}
if ($range) {
$dateRange = Carbon::parseDateRangeValue($range);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('date', [$fromdate, $todate]);
}
$results = $query->leftJoin('tblclients', 'tblclients.id', '=', 'tblaccounts.userid')
->orderBy('date', 'asc')
->get()
->all();
foreach ($results as $result) {
$result = (array) $result;
if (isset($result['currency'])) {
$currency = getCurrency($result['userid'], $result['currency']);
$result['currency'] = $currency['code'];
if (!in_array('userid', $incfields)) {
unset($result['userid']);
}
}
if (isset($result['gateway'])) {
$result['gateway'] = $gateways->getDisplayName($result['gateway']);
}
$reportdata["tablevalues"][] = $result;
}
}
annual_income_report.php 0000644 00000006417 14736262117 0011501 0 ustar 00 <?php
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata['title'] = "Annual Income Report for " . $currentyear;
$reportdata['description'] = "This report shows the income received broken down by month converted to the base currency using rates at the time of the transaction";
$reportdata['yearspagination'] = true;
$currency = getCurrency(null, 1);
$reportdata['tableheadings'] = array(
"Month",
"Amount In",
"Fees",
"Amount Out",
"Balance"
);
$reportvalues = array();
$results = Capsule::table('tblaccounts')
->select(
Capsule::raw("date_format(date,'%m') as month"),
Capsule::raw("date_format(date,'%Y') as year"),
Capsule::raw("SUM(amountin/rate) as amountin"),
Capsule::raw("SUM(fees/rate) fees"),
Capsule::raw("SUM(amountout/rate) as amountout")
)
->where('date', '>=', ($currentyear - 2) . '-01-01')
->groupBy(Capsule::raw("date_format(date,'%M %Y')"))
->orderBy('date', 'asc')
->get()
->all();
foreach ($results as $result) {
$month = (int) $result->month;
$year = (int) $result->year;
$amountin = $result->amountin;
$fees = $result->fees;
$amountout = $result->amountout;
$monthlybalance = $amountin - $fees - $amountout;
$reportvalues[$year][$month] = [
$amountin,
$fees,
$amountout,
$monthlybalance,
];
}
foreach ($months as $k => $monthName) {
if ($monthName) {
$amountin = $reportvalues[$currentyear][$k][0];
$fees = $reportvalues[$currentyear][$k][1];
$amountout = $reportvalues[$currentyear][$k][2];
$monthlybalance = $reportvalues[$currentyear][$k][3];
$reportdata['tablevalues'][] = array(
$monthName . ' ' . $currentyear,
formatCurrency($amountin),
formatCurrency($fees),
formatCurrency($amountout),
formatCurrency($monthlybalance),
);
$overallbalance += $monthlybalance;
}
}
$reportdata['footertext'] = '<p align="center"><strong>Balance: ' . formatCurrency($overallbalance) . '</strong></p>';
$chartdata['cols'][] = array('label'=>'Days Range','type'=>'string');
$chartdata['cols'][] = array('label'=>$currentyear-2,'type'=>'number');
$chartdata['cols'][] = array('label'=>$currentyear-1,'type'=>'number');
$chartdata['cols'][] = array('label'=>$currentyear,'type'=>'number');
for ($i = 1; $i <= 12; $i++) {
$chartdata['rows'][] = array(
'c'=>array(
array(
'v'=>$months[$i],
),
array(
'v'=>$reportvalues[$currentyear-2][$i][3],
'f'=>formatCurrency($reportvalues[$currentyear-2][$i][3])->toFull(),
),
array(
'v'=>$reportvalues[$currentyear-1][$i][3],
'f'=>formatCurrency($reportvalues[$currentyear-1][$i][3])->toFull(),
),
array(
'v'=>$reportvalues[$currentyear][$i][3],
'f'=>formatCurrency($reportvalues[$currentyear][$i][3])->toFull(),
),
),
);
}
$args = array();
$args['colors'] = '#3070CF,#F9D88C,#cb4c30';
$args['chartarea'] = '80,20,90%,350';
$reportdata['headertext'] = $chart->drawChart('Column',$chartdata,$args,'400px');
server_revenue_forecasts.php 0000644 00000007200 14736262117 0012375 0 ustar 00 <?php
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Server Revenue Forecasts";
$reportdata["description"] = "This report shows income broken down by billing cycle for each of your servers."
. " It then uses the monthly cost entered for each server to estimate the annual gross profit for"
. " each server.";
$reportdata["tableheadings"] = [
"Server Income",
"Monthly",
"Quarterly",
"Semi-Annual",
"Annual",
"Biennial",
"Triennial",
"Monthly Costs",
"Annual Gross Profit"
];
$currency = getCurrency(null, 1);
$results = Capsule::table('tblservers')
->where('disabled', '=', '0')
->orderBy('name', 'asc')
->get()
->all();
foreach ($results as $result) {
$id = $result->id;
$name = $result->name;
$monthlycost = $result->monthlycost;
$monthly = $quarterly = $semiannually = $annually = $biennially = $triennially = 0;
$services = Capsule::table('tblhosting')
->select(
'tblhosting.billingcycle',
Capsule::raw('tblhosting.amount/tblcurrencies.rate AS reportamt')
)
->join('tblclients', 'tblclients.id', '=', 'tblhosting.userid')
->join('tblcurrencies', 'tblcurrencies.id', '=', 'tblclients.currency')
->where('server', '=', (int) $id)
->whereIn('domainstatus', ['Active', 'Suspended'])
->whereNotIn('billingcycle', ['Free Account', 'One Time'])
->get()
->all();
foreach ($services as $service) {
$amount = $service->reportamt;
$billingcycle = $service->billingcycle;
if ($billingcycle == "Monthly") {
$monthly += $amount;
} elseif ($billingcycle == "Quarterly") {
$quarterly += $amount;
} elseif ($billingcycle == "Semi-Annually") {
$semiannually += $amount;
} elseif ($billingcycle == "Annually") {
$annually += $amount;
} elseif ($billingcycle == "Biennially") {
$biennially += $amount;
} elseif ($billingcycle == "Triennially") {
$triennially += $amount;
}
}
$monthly = number_format($monthly, 2, ".", "");
$quarterly = number_format($quarterly, 2, ".", "");
$semiannually = number_format($semiannually, 2, ".", "");
$annually = number_format($annually, 2, ".", "");
$biennially = number_format($biennially, 2, ".", "");
$triennially = number_format($triennially, 2, ".", "");
$totalserverincome = (
($monthly * 12)
+ ($quarterly * 4)
+ ($semiannually * 2)
+ $annually
+ ($biennially / 2)
+ ($triennially / 3)
);
$totalserverexpenditure = ($monthlycost * 12);
$servertotal = number_format(($totalserverincome - $totalserverexpenditure), 2, ".", "");
$totalincome += $totalserverincome;
$totalexpenditure += $totalserverexpenditure;
$totalgrossprofit += $servertotal;
$reportdata["tablevalues"][] = [
"$name",
formatCurrency($monthly),
formatCurrency($quarterly),
formatCurrency($semiannually),
formatCurrency($annually),
formatCurrency($biennially),
formatCurrency($triennially),
formatCurrency($monthlycost),
formatCurrency($servertotal)
];
}
$totalincome = formatCurrency($totalincome);
$totalexpenditure = formatCurrency($totalexpenditure);
$totalgrossprofit = formatCurrency($totalgrossprofit);
$data["footertext"] = "<strong>Total Income:</strong> {$totalincome}<br />"
. "<strong>Total Expenses:</strong> {$totalexpenditure}<br />"
. "<strong>Gross Profit:</strong> {$totalgrossprofit}";
daily_performance.php 0000644 00000011727 14736262117 0010761 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$dateFilter = Carbon::create(
$year,
$month,
1
);
/** @var Carbon $today */
$startOfMonth = $dateFilter->startOfMonth()->toDateTimeString();
$endOfMonth = $dateFilter->endOfMonth()->toDateTimeString();
$reportdata["title"] = "Daily Performance for " . $months[(int) $month] . " " . $year;
$reportdata["description"] = "This report shows a daily activity summary for a given month.";
$reportdata["monthspagination"] = true;
$reportdata["tableheadings"] = array(
"Date",
"Completed Orders",
"New Invoices",
"Paid Invoices",
"Opened Tickets",
"Ticket Replies",
"Cancellation Requests",
);
$reportvalues = array();
$dateFormat = Capsule::raw('date_format(`date`, "%e")');
$reportvalues['orders_active'] = Capsule::table('tblorders')
->where('status', 'Active')
->whereBetween(
'date',
[
$startOfMonth,
$endOfMonth
]
)
->groupBy($dateFormat)
->orderBy('date')
->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`date`, "%e") as day'))->all();
$reportvalues['invoices_new'] = Capsule::table('tblinvoices')
->whereBetween(
'date',
[
$startOfMonth,
$endOfMonth
]
)
->groupBy($dateFormat)
->orderBy('date')
->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`date`, "%e") as day'))->all();
$reportvalues['invoices_paid'] = Capsule::table('tblinvoices')
->whereBetween(
'date',
[
$startOfMonth,
$endOfMonth
]
)
->groupBy(Capsule::raw('date_format(`datepaid`, "%e")'))
->orderBy('date')
->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`datepaid`, "%e") as day'))->all();
$reportvalues['tickets_new'] = Capsule::table('tbltickets')
->whereBetween(
'date',
[
$startOfMonth,
$endOfMonth
]
)
->groupBy($dateFormat)
->orderBy('date')
->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`date`, "%e") as day'))->all();
$reportvalues['tickets_staff_replies'] = Capsule::table('tblticketreplies')
->whereBetween(
'date',
[
$startOfMonth,
$endOfMonth
]
)
->where('admin', '!=', '')
->groupBy($dateFormat)
->orderBy('date')
->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`date`, "%e") as day'))->all();
$reportvalues['cancellations_new'] = Capsule::table('tblcancelrequests')
->whereBetween(
'date',
[
$startOfMonth,
$endOfMonth
]
)
->groupBy($dateFormat)
->orderBy('date')
->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`date`, "%e") as day'))->all();
for ($day = 1; $day <= $dateFilter->endOfMonth()->day; $day++) {
$date = Carbon::create($year, $month, $day);
$daytext = $date->format('l');
$date = $date->toDateString();
$neworders = isset($reportvalues['orders_active'][$day]) ? $reportvalues['orders_active'][$day] : '0';
$newinvoices = isset($reportvalues['invoices_new'][$day]) ? $reportvalues['invoices_new'][$day] : '0';
$paidinvoices = isset($reportvalues['invoices_paid'][$day]) ? $reportvalues['invoices_paid'][$day] : '0';
$newtickets = isset($reportvalues['tickets_new'][$day]) ? $reportvalues['tickets_new'][$day] : '0';
$ticketreplies = isset($reportvalues['tickets_staff_replies'][$day]) ? $reportvalues['tickets_staff_replies'][$day] : '0';
$cancellations = isset($reportvalues['cancellations_new'][$day]) ? $reportvalues['cancellations_new'][$day] : '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';
$reportdata["headertext"] = $chart->drawChart('Area',$chartdata,$args,'400px');
ticket_feedback_scores.php 0000644 00000011002 14736262117 0011725 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Ticket Feedback Scores";
$reportdata["description"] = "This report provides a summary of scores received on a per staff member basis for a given date range";
$range = App::getFromRequest('range');
if (!$range) {
$today = Carbon::today()->endOfDay();
$lastWeek = Carbon::today()->subDays(6)->startOfDay();
$range = $lastWeek->toAdminDateFormat() . ' - ' . $today->toAdminDateFormat();
}
$module = App::getFromRequest('module');
$moduleString = '';
if ($module) {
$moduleString = 'module=' . $module . '&';
}
$reportdata['headertext'] = '';
if (!$print) {
$reportdata['headertext'] = <<<HTML
<form method="post" action="?{$moduleString}report={$report}¤cyid={$currencyid}&calculate=true">
<div class="report-filters-wrapper">
<div class="inner-container">
<h3>Filters</h3>
<div class="row">
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterDate">{$dateRangeText}</label>
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="range"
value="{$range}"
class="form-control date-picker-search"
/>
</div>
</div>
</div>
</div>
<button type="submit" class="btn btn-primary">
{$aInt->lang('reports', 'generateReport')}
</button>
</div>
</div>
</form>
HTML;
}
$reportdata["tableheadings"][] = "Staff Name";
for ( $rating = 1; $rating <= 10; $rating++ ) $reportdata["tableheadings"][] = $rating;
$reportdata["tableheadings"][] = "Total Ratings";
$reportdata["tableheadings"][] = "Average Rating";
$dateRange = Carbon::parseDateRangeValue($range);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->endOfDay()->toDateTimeString();
$adminnames = $ratingstats = array();
$query = Capsule::table('tblticketfeedback')
->select(
[
Capsule::raw('CONCAT_WS(\' \', `firstname`, `lastname`) as adminname'),
'adminid',
'rating',
Capsule::raw('count(rating) as counts'),
]
)
->where('adminid', '>', 0)
->where('datetime', '>=', $fromdate)
->where('datetime', '<=', $todate)
->join(
'tbladmins',
'tbladmins.id',
'=',
'tblticketfeedback.adminid'
)
->groupBy(
[
'rating',
'adminid',
]
);
foreach ($query->get() as $data) {
$adminname = $data->adminname;
$adminid = $data->adminid;
$rating = $data->rating;
$count = $data->counts;
$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;
$rowcount += $count;
$rowtotal += $count*$rating;
}
$average = round($rowtotal/$rowcount,2);
$row[] = $rowcount;
$row[] = $average;
$reportdata["tablevalues"][] = $row;
$chartdata['rows'][] = array('c'=>array(array('v'=>$adminname),array('v'=>$average,'f'=>$average)));
}
$chartdata['cols'][] = array('label'=>'Staff Name','type'=>'string');
$chartdata['cols'][] = array('label'=>'Average Rating','type'=>'number');
$args = array();
$args['colors'] = '#F9D88C,#3070CF';
$args['minyvalue'] = '0';
$args['maxyvalue'] = '10';
$args['gridlinescount'] = '11';
$args['minorgridlinescount'] = '3';
$args['ylabel'] = 'Average Rating';
$args['xlabel'] = 'Staff Name';
$args['legendpos'] = 'none';
$reportdata["headertext"] .= $chart->drawChart('Column',$chartdata,$args,'500px');
disk_usage_summary.php 0000644 00000006276 14736262117 0011174 0 ustar 00 <?php
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Disk Space & Bandwidth Usage Summary";
$reportdata["description"] = "This report shows the Disk Space & Bandwidth Usage Statistics for hosting accounts";
$reportdata["tableheadings"] = array("Client Name/Domain","Disk Usage","Disk Limit","% Used","BW Usage","BW Limit","% Used");
if ($_GET["action"]=="updatestats") {
require("../includes/modulefunctions.php");
ServerUsageUpdate();
}
$results = Capsule::table('tblservers')
->orderBy('name', 'asc')
->get()
->all();
foreach ($results as $result) {
$serverid = $result->id;
$name = $result->name;
$ipaddress = $result->ipaddress;
$reportdata["tablevalues"][] = ["**<strong>{$name}</strong> - {$ipaddress}"];
$services = Capsule::table('tblhosting')
->select(
'tblhosting.domain',
'tblhosting.diskusage',
'tblhosting.disklimit',
'tblhosting.bwlimit',
'tblhosting.bwusage',
'tblhosting.domainstatus',
'tblclients.firstname',
'tblclients.lastname',
'tblclients.companyname',
'tblhosting.lastupdate'
)
->join('tblclients', 'tblclients.id', '=', 'tblhosting.userid')
->where('tblhosting.server', '=', (int) $serverid)
->where('tblhosting.lastupdate', '!=', '0000-00-00 00:00:00')
->whereIn('domainstatus', ['Active', 'Suspended'])
->orderBy('tblhosting.domain', 'asc')
->get()
->all();
foreach ($services as $service) {
$name = "{$service->firstname} {$service->lastname}";
$companyname = $service->companyname;
if ($companyname != "") {
$name .= " ({$companyname})";
}
$domain = $service->domain;
$diskusage = $service->diskusage;
$disklimit = $service->disklimit;
$bwusage = $service->bwusage;
$bwlimit = $service->bwlimit;
$lastupdate = $service->lastupdate;
if ($disklimit == "0") {
$percentused = "N/A";
} else {
@$percentused = number_format((($diskusage / $disklimit) * 100), 0, '.', '');
}
if ($disklimit=="0") {
$disklimit="Unlimited";
}
if ($bwlimit == "0") {
$bwpercentused = "N/A";
} else {
@$bwpercentused = number_format((($bwusage / $bwlimit) * 100), 0, '.', '');
}
if ($bwlimit == "0") {
$bwlimit = "Unlimited";
}
if ($percentused != "N/A") {
$percentused .= "%";
}
if ($bwpercentused != "N/A") {
$bwpercentused .= "%";
}
$reportdata["tablevalues"][] = [
"{$name}<br />{$domain}",
"{$diskusage} MB",
"{$disklimit} MB",
"{$percentused}",
"{$bwusage} MB",
"{$bwlimit} MB",
"{$bwpercentused}"
];
}
}
$data["footertext"] = "<p>Disk Space Usage Stats Last Updated at "
. fromMySQLDate($lastupdate, "time")
. " - <a href=\"{$_SERVER["PHP_SELF"]}?report={$_GET["report"]}&action=updatestats\">Update Now</a></p>";
product_suspensions.php 0000644 00000003064 14736262117 0011422 0 ustar 00 <?php
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Product Suspensions";
$reportdata["description"] = "This report allows you to review all suspended products and the reasons specified for their suspensions";
$reportdata["tableheadings"] = array("Service ID","Client Name","Product Name","Domain","Next Due Date","Suspend Reason");
$results = Capsule::table('tblhosting')
->select('tblhosting.*', 'tblclients.firstname', 'tblclients.lastname', 'tblproducts.name')
->join('tblclients', 'tblclients.id', '=', 'tblhosting.userid')
->join('tblproducts', 'tblproducts.id', '=', 'tblhosting.packageid')
->where('domainstatus', '=', 'Suspended')
->orderBy('id', 'asc')
->get()
->all();
foreach ($results as $result) {
$serviceid = $result->id;
$userid = $result->userid;
$clientname = $result->firstname . " " . $result->lastname;
$productname = $result->name;
$domain = $result->domain;
$nextduedate = $result->nextduedate;
$suspendreason = $result->suspendreason;
if (!$suspendreason) {
$suspendreason = 'Overdue on Payment';
}
$nextduedate = fromMySQLDate($nextduedate);
$reportdata["tablevalues"][] = [
'<a href="clientshosting.php?userid=' . $userid . '&id=' . $serviceid . '">' . $serviceid . '</a>',
'<a href="clientssummary.php?userid=' . $userid . '">' . $clientname . '</a>',
$productname,
$domain,
$nextduedate,
$suspendreason,
];
}
$data["footertext"] = '';
credits_reviewer.php 0000644 00000014074 14736262117 0010641 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
use WHMCS\User\Admin;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$range = App::getFromRequest('range');
$userId = (int) App::getFromRequest('userid');
$min = App::getFromRequest('min');
$max = App::getFromRequest('max');
$adminId = App::getFromRequest('adminid');
$reportdata["title"] = "Credits Reviewer";
$reportdata["description"] = "This report allows you to review all the credits issued to clients between 2 dates you specify";
$activeAdminUserOptions = [];
foreach (Admin::where('disabled', 0)->get() as $user) {
$activeAdminUserOptions[] = '<option value="' . $user->id . '"'
. ($user->id == $adminId ? ' selected' : '') . '>' . $user->fullName . '</option>';
}
$disabledAdminUserOptions = [];
foreach (Admin::where('disabled', 1)->get() as $user) {
$disabledAdminUserOptions[] = '<option value="' . $user->id . '"'
. ($user->id == $adminId ? ' selected' : '') . '>' . $user->fullName . '</option>';
}
$adminUserOptions = '<option value="0">' . AdminLang::trans('global.-any-') . '</option>'
. '<optgroup label="Active Users">' . implode($activeAdminUserOptions);
if (count($disabledAdminUserOptions) > 0) {
$adminUserOptions .= '<optgroup label="Disabled Users">' . implode($disabledAdminUserOptions);
}
$langRequired = AdminLang::trans('global.required');
$reportdata['headertext'] = '';
if (!$print) {
$reportdata["headertext"] = <<<HTML
<form method="post" action="reports.php?report={$report}">
<div class="report-filters-wrapper">
<div class="inner-container">
<h3>Filters</h3>
<div class="row">
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterClient">{$aInt->lang('fields', 'client')}</label>
{$aInt->clientsDropDown($userId)}
</div>
</div>
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterDate">{$dateRangeText}</label>
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="range"
value="{$range}"
class="form-control date-picker-search date-picker-search-100pc"
placeholder="{$langRequired}"
/>
</div>
</div>
</div>
<div class="col-md-2 col-sm-3">
<div class="form-group">
<label for="inputFilterMin">Min. Amount</label>
<input type="number" name="min" value="{$min}" class="form-control" id="inputFilterMin" step="any" placeholder="Any">
</div>
</div>
<div class="col-md-2 col-sm-3">
<div class="form-group">
<label for="inputFilterMax">Max. Amount</label>
<input type="number" name="max" value="{$max}" class="form-control" id="inputFilterMax" step="any" placeholder="Any">
</div>
</div>
<div class="col-md-2 col-sm-6">
<div class="form-group">
<label for="inputAdmin">Admin User</label>
<select name="adminid" class="form-control">
{$adminUserOptions}
</select>
</div>
</div>
</div>
<button type="submit" class="btn btn-primary">
{$aInt->lang('reports', 'generateReport')}
</button>
</div>
</div>
</form>
HTML;
}
$reportdata["tableheadings"] = array(
"Credit ID",
"Client ID",
"Client Name",
"Date",
"Description",
"Amount",
"Admin User",
);
if ($range) {
$dateRange = Carbon::parseDateRangeValue($range);
$dateFrom = $dateRange['from']->toDateTimeString();
$dateTo = $dateRange['to']->toDateTimeString();
$query = Capsule::table('tblcredit')
->join('tblclients', 'tblclients.id', '=', 'tblcredit.clientid')
->whereBetween(
'tblcredit.date',
[
$dateFrom,
$dateTo,
]
);
if ($userId) {
$query->where('clientid', $userId);
}
if ($min != '') {
$query->where('amount', '>=', $min);
}
if ($max != '' && ($min == '' || ($max > $min))) {
$query->where('amount', '<=', $max);
}
if ($adminId) {
$query->where('admin_id', $adminId);
}
$result = $query
->orderBy('date')
->get(['tblcredit.*', 'tblclients.firstname', 'tblclients.lastname'])
->all();
/** @var stdClass $data */
foreach ($result as $data) {
$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);
$adminName = '-';
if ($data->admin_id) {
$adminName = getAdminName($data->admin_id);
if (!trim($adminName)) {
$adminName = '-';
}
}
$reportdata["tablevalues"][] = array(
$id,
'<a href="clientssummary.php?userid=' . $userid . '">' . $userid . '</a>',
'<a href="clientssummary.php?userid=' . $userid . '">' . $clientname . '</a>',
$date,
nl2br($description),
$amount,
$adminName,
);
}
}
$reportdata["footertext"] = '';
monthly_transactions.php 0000644 00000010063 14736262117 0011550 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$dateFilter = Carbon::create(
$year,
$month,
1
);
$startOfMonth = $dateFilter->startOfMonth()->toDateTimeString();
$endOfMonth = $dateFilter->endOfMonth()->toDateTimeString();
$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["monthspagination"] = true;
$reportdata["tableheadings"] = array(
"Date",
"Amount In",
"Fees",
"Amount Out",
"Balance"
);
$reportvalues = array();
$dateFormat = Capsule::raw('date_format(date, "%e")');
$result = Capsule::table('tblaccounts')
->join('tblclients', 'tblclients.id', '=', 'tblaccounts.userid')
->where('tblclients.currency', $currencyid)
->whereBetween(
'date',
[
$startOfMonth,
$endOfMonth
]
)
->orderBy('date')
->groupBy($dateFormat)
->get(
[
Capsule::raw('date_format(date, "%e") as day_of_month'),
Capsule::raw('SUM(amountin) as in_amount'),
Capsule::raw('SUM(fees) as fee_amount'),
Capsule::raw('SUM(amountout) as out_amount'),
]
)
->all();
foreach ($result as $data) {
$reportvalues[$data->day_of_month] = array(
'amountin' => $data->in_amount,
'fees' => $data->fee_amount,
'amountout' => $data->out_amount,
);
}
$result = Capsule::table('tblaccounts')
->where('userid', 0)
->where('currency', $currencyid)
->whereBetween(
'date',
[
$startOfMonth,
$endOfMonth
]
)
->orderBy('date')
->groupBy($dateFormat)
->get(
[
Capsule::raw('date_format(date, "%e") as day_of_month'),
Capsule::raw('SUM(amountin) as in_amount'),
Capsule::raw('SUM(fees) as fee_amount'),
Capsule::raw('SUM(amountout) as out_amount'),
]
)
->all();
foreach ($result as $data) {
$reportvalues[$data->day_of_month] = array(
'amountin' => $data->in_amount,
'fees' => $data->fee_amount,
'amountout' => $data->out_amount,
);
}
for ($dayOfTheMonth = 1; $dayOfTheMonth <= $dateFilter->lastOfMonth()->day; $dayOfTheMonth++) {
$amountin = isset($reportvalues[$dayOfTheMonth]['amountin']) ? $reportvalues[$dayOfTheMonth]['amountin'] : '0';
$fees = isset($reportvalues[$dayOfTheMonth]['fees']) ? $reportvalues[$dayOfTheMonth]['fees'] : '0';
$amountout = isset($reportvalues[$dayOfTheMonth]['amountout']) ? $reportvalues[$dayOfTheMonth]['amountout'] : '0';
$dailybalance = $amountin-$fees-$amountout;
$overallbalance += $dailybalance;
$chartdata['rows'][] = array('c'=>array(array('v'=>$dayOfTheMonth),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);
$dayOfTheMonth = str_pad($dayOfTheMonth, 2, "0", STR_PAD_LEFT);
$reportdata["tablevalues"][] = array(
fromMySQLDate("$year-$month-$dayOfTheMonth"),
$amountin,
$fees,
$amountout,
$dailybalance,
);
}
$reportdata["footertext"] = '<p align="center"><strong>Balance: ' . formatCurrency($overallbalance) . '</strong></p>';
$chartdata['cols'][] = array('label'=>'Days Range','type'=>'string');
$chartdata['cols'][] = array('label'=>'Amount In','type'=>'number');
$chartdata['cols'][] = array('label'=>'Fees','type'=>'number');
$chartdata['cols'][] = array('label'=>'Amount Out','type'=>'number');
$args['colors'] = '#80D044,#F9D88C,#CC0000';
$reportdata["headertext"] = $chart->drawChart('Area', $chartdata, $args, '450px');
services.php 0000644 00000023312 14736262117 0007112 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Services";
$filterfields = [
'id' => 'ID',
'userid' => 'User ID',
'clientname' => 'Client Name',
'orderid' => 'Order ID',
'packageid' => 'Product ID',
'server' => 'Server ID',
'regdate' => 'Registration Date',
'domain' => 'Domain Name',
'dedicatedip' => 'Dedicated IP',
'assignedips' => 'Assigned IPs',
'firstpaymentamount' => 'First Payment Amount',
'amount' => 'Recurring Amount',
'billingcycle' => 'Billing Cycle',
'nextduedate' => 'Next Due Date',
'paymentmethod' => 'Payment Method',
'termination_date' => 'Termination Date',
'completed_date' => 'Completed Date',
'domainstatus' => 'Status',
'username' => 'Username',
'password' => 'Password',
'notes' => 'Notes',
'subscriptionid' => 'Subscription ID',
'suspendreason' => 'Suspend Reason'
];
$dateRangeFields = [
'regdate' => 'Registration Date',
'nextduedate' => 'Next Due Date',
'termination_date' => 'Termination Date',
'completed_date' => 'Completed Date',
];
$removedDateRangeFields = array_diff($filterfields, $dateRangeFields);
$reportdata["description"] = $reportdata["headertext"] = '';
$incfields = $whmcs->get_req_var('incfields');
$filterfield = $whmcs->get_req_var('filterfield');
$filtertype = $whmcs->get_req_var('filtertype');
$filterq = $whmcs->get_req_var('filterq');
$regDateRange = App::getFromRequest('regDateRange');
$nextDueDateRange = App::getFromRequest('nextDueDateRange');
$termDateRange = App::getFromRequest('termDateRange');
$completedDateRange = App::getFromRequest('completedDateRange');
if (!is_array($incfields)) {
$incfields = [];
}
if (!is_array($filterfield)) {
$filterfield = [];
}
if (!is_array($filtertype)) {
$filtertype = [];
}
if (!is_array($filterq)) {
$filterq = [];
}
if (!$print) {
$reportdata["description"] = "This report can be used to generate a custom export of"
. " services by applying up to 5 filters. CSV Export is available via the Tools menu to the right.";
$reportdata["headertext"] = '<form method="post" action="reports.php?report=' . $report . '">
<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
<tr><td width="20%" class="fieldlabel">Fields to Include</td><td class="fieldarea"><table width="100%"><tr>';
$i=0;
foreach ($filterfields as $k => $v) {
$reportdata["headertext"] .= '<td width="20%"><input type="checkbox" name="incfields[]" value="' . $k . '" id="fd' . $k . '"';
if (in_array($k, $incfields)) {
$reportdata["headertext"] .= ' checked';
}
$reportdata["headertext"] .= ' /> <label for="fd' . $k . '">' . $v . '</label></td>';
$i++;
if (($i%5)==0) {
$reportdata["headertext"] .= '</tr><tr>';
}
}
$reportdata["headertext"] .= '</tr></table></td></tr>';
for ($i = 1; $i <= 5; $i ++) {
$reportdata["headertext"] .= '<tr><td width="20%" class="fieldlabel">Filter ' . $i . '</td><td class="fieldarea"><select name="filterfield[' . $i . ']" class="form-control select-inline"><option value="">None</option>';
foreach ($removedDateRangeFields as $k => $v) {
$reportdata["headertext"] .= '<option value="' . $k . '"';
if (isset($filterfield[$i]) && $filterfield[$i] == $k) {
$reportdata["headertext"] .= ' selected';
}
$reportdata["headertext"] .= '>' . $v . '</option>';
}
$reportdata["headertext"] .= '</select> <select name="filtertype[' . $i . ']" class="form-control select-inline">'
. '<option value="=">Exact Match</option><option value="like"';
if (isset($filtertype[$i]) && $filtertype[$i] == "like") {
$reportdata["headertext"] .= ' selected';
}
$reportdata["headertext"] .= '>Containing</option></select>'
. ' <input type="text" name="filterq[' . $i . ']" class="form-control input-inline input-250" value="' . (isset($filterq[$i]) ? $filterq[$i] : '') . '" /></td></tr>';
}
$reportdata["headertext"] .= <<<HTML
<tr>
<td width="20%" class="fieldlabel">Registration Date Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="regDateRange"
value="{$regDateRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
<tr>
<td width="20%" class="fieldlabel">Next Due Date Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="nextDueDateRange"
value="{$nextDueDateRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
<tr>
<td width="20%" class="fieldlabel">Termination Date Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="termDateRange"
value="{$termDateRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
<tr>
<td width="20%" class="fieldlabel">Completed Date Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="completedDateRange"
value="{$completedDateRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
</table>
<p align="center"><input type="submit" value="Filter" class="btn btn-primary"/></p>
</form>
HTML;
}
if (count($incfields)) {
$query = Capsule::table('tblhosting');
foreach ($filterfield as $i => $val) {
if ($val && array_key_exists($val, $filterfields)) {
if ($filtertype[$i] == 'like') {
$filterq[$i] = "%{$filterq[$i]}%";
}
if ($val == 'clientname') {
$query->whereRaw(
"concat(tblclients.firstname, ' ', tblclients.lastname) "
. "{$filtertype[$i]} '{$filterq[$i]}'"
);
} else {
$query->where(
"tblhosting.{$filterfield[$i]}",
$filtertype[$i],
$filterq[$i]
);
}
}
}
foreach ($incfields as $fieldname) {
if (array_key_exists($fieldname, $filterfields)) {
$reportdata["tableheadings"][] = $filterfields[$fieldname];
if ($fieldname == "clientname") {
$query->addSelect(Capsule::raw("concat(tblclients.firstname, ' ', tblclients.lastname)"));
} else {
$query->addSelect("tblhosting.{$fieldname}");
}
}
}
if ($regDateRange) {
$dateRange = Carbon::parseDateRangeValue($regDateRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('regdate', [$fromdate, $todate]);
}
if ($nextDueDateRange) {
$dateRange = Carbon::parseDateRangeValue($nextDueDateRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('nextduedate', [$fromdate, $todate]);
}
if ($termDateRange) {
$dateRange = Carbon::parseDateRangeValue($termDateRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('termination_date', [$fromdate, $todate]);
}
if ($completedDateRange) {
$dateRange = Carbon::parseDateRangeValue($completedDateRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('completed_date', [$fromdate, $todate]);
}
$results = $query
->join('tblclients', 'tblclients.id', '=', 'tblhosting.userid')
->get()
->all();
foreach ($results as $result) {
$result = (array) $result;
if (isset($result['paymentmethod'])) {
$result['paymentmethod'] = $gateways->getDisplayName($result['paymentmethod']);
}
if (isset($result['password'])) {
$result['password'] = decrypt($result['password']);
}
$reportdata["tablevalues"][] = $result;
}
}
smarty_compatibility.php 0000644 00000010542 14736262117 0011540 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Config\Setting;
use WHMCS\Utility\Environment\WebHelper;
use WHMCS\Utility\Smarty\TagScanner;
if (!defined('WHMCS')) {
die('This file cannot be accessed directly.');
}
$rescanRequested = (App::getFromRequest('rescan') && !$print);
$tagUsageOutput = (new TagScanner())->findTagUsageAndRequeue(
TagScanner::DEPRECATED_SMARTY_BC_TAGS,
TagScanner::DEPRECATED_SMARTY_BC_TAGS_CACHE_KEY,
$rescanRequested
);
$isAllowSmartyPhpTagsEnabled = (bool) Setting::getValue('AllowSmartyPhpTags');
$lastScanned = (new Carbon($tagUsageOutput['timestamp']))->toAdminDateTimeFormat();
$tagList = TagScanner::DEPRECATED_SMARTY_BC_TAGS;
$tagList = array_map(
static function (string $tag) {
$tag = trim(trim($tag), '{}');
return "{{$tag}}";
},
$tagList
);
$lastTag = array_pop($tagList);
$tagList = implode(', ', $tagList);
$tagList = "{$tagList} and $lastTag";
$reportdata['title'] = 'Smarty Compatibility';
$reportdata['description'] = '';
if ($isAllowSmartyPhpTagsEnabled) {
$settingsUrl = WebHelper::getAdminBaseUrl(). '/configgeneral.php?tab=10';
$transParams= [
':anchorSettings' => "<a href=\"{$settingsUrl}\">",
':anchorDocs' => '<a href="https://go.whmcs.com/1733/legacy-smarty-tags">',
':anchorClose' => '</a>',
];
$settingsString = AdminLang::trans('healthCheck.legacySmartyTags.body.settingOnly', $transParams);
$reportdata['description'] .= <<<HTML
<h3>Smarty PHP Tags Setting</h3>
<p>{$settingsString}</p>
HTML;
}
$reportdata['description'] .= <<<HTML
<h3>Smarty Template Compatibility</h3>
<p>This report lists the Smarty {$tagList} tags that the system detected within your templates and provides an indication of whether the Allow Smarty PHP Tags setting is enabled on the installation.<br>
<small>Last Scanned: {$lastScanned}</small></p>
HTML;
if (!$print) {
$reportdata['description'] .= <<<HTML
<p><form method="post" action="?report={$report}" id="smartyPhpTagReport">
<input type="hidden" name="rescan" value="1">
<button type="submit" class="btn btn-primary btn-rescan" id="btnRescan">
Rescan Now
<span class="hidden">
<i class="fas fa-spinner fa-spin" aria-hidden="true"></i>
<span class="sr-only">Performing rescan.</span>
</span>
</button>
</form></p>
HTML;
}
$reportdata['tableheadings'] = [
'File Name/Email Template Name',
'File Path/Email Template Type',
'Line Numbers',
];
$tagUsageResultsCollection = collect($tagUsageOutput['results']);
$fileTagUsageCollection = $tagUsageResultsCollection->where('type', TagScanner::TYPE_FILE);
$emailTagUsageCollection = $tagUsageResultsCollection->where('type', TagScanner::TYPE_EMAIL);
$noRecordsArray = ['No Records Found', '', ''];
$reportdata['tablevalues'][] = ['**<strong>Template Files</strong>'];
foreach ($fileTagUsageCollection->sortBy('filePath')->all() as $fileTagUsageItem) {
$reportdata['tablevalues'][] = [
basename($fileTagUsageItem['filePath']),
dirname($fileTagUsageItem['filePath']),
implode(', ', $fileTagUsageItem['lineNumbers']),
];
}
if ($fileTagUsageCollection->count() === 0) {
$reportdata['tablevalues'][] = $noRecordsArray;
}
$reportdata['tablevalues'][] = ['**<strong>Email Templates</strong>'];
foreach ($emailTagUsageCollection->sortBy('templateType')->all() as $emailTagUsageItem) {
$templateName = $emailTagUsageItem['templateName'];
if ($emailTagUsageItem['templateLanguage']) {
$languageString = ucfirst($emailTagUsageItem['templateLanguage']);
$templateName .= " ($languageString Version)";
}
if ($aInt->hasPermission('View Email Templates')) {
$templateName = "<a href=\"configemailtemplates.php?action=edit&id={$emailTagUsageItem['templateId']}\" target='_blank'>{$templateName}</a>";
}
$reportdata['tablevalues'][] = [
$templateName,
AdminLang::trans("emailtpls.type.{$emailTagUsageItem['templateType']}"),
implode(', ', $emailTagUsageItem['lineNumbers']),
];
}
if ($emailTagUsageCollection->count() === 0) {
$reportdata['tablevalues'][] = $noRecordsArray;
}
$reportdata['footertext'] = <<<HTML
<script>
jQuery('#smartyPhpTagReport').submit(function() {
var submitButton = jQuery('.btn-rescan');
submitButton.find('span').removeClass('hidden');
submitButton.attr('disabled', 'disabled');
});
</script>
HTML;
client_sources.php 0000644 00000011124 14736262117 0010306 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Client Sources";
$reportdata["description"] = "This report provides a summary of the answers clients have given to the How Did You Find Us? or Where did you hear about us? custom field signup question";
$range = App::getFromRequest('range');
if (!$range) {
$today = Carbon::today()->endOfDay();
$lastWeek = Carbon::today()->subDays(6)->startOfDay();
$range = $lastWeek->toAdminDateFormat() . ' - ' . $today->toAdminDateFormat();
}
$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'])) {
check_token('WHMCS.admin.default');
$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'] = '';
if (!$print) {
$reportdata['headertext'] = <<<HTML
<form method="post" action="?report={$report}¤cyid={$currencyid}&calculate=true">
<div class="report-filters-wrapper">
<div class="inner-container">
<h3>Filters</h3>
<div class="row">
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterDate">{$dateRangeText}</label>
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="range"
value="{$range}"
class="form-control date-picker-search"
/>
</div>
</div>
</div>
</div>
<button type="submit" class="btn btn-primary">
{$aInt->lang('reports', 'generateReport')}
</button>
</div>
</div>
</form>
HTML;
}
}
$reportdata["tableheadings"][] = "Referral Location";
$reportdata["tableheadings"][] = "Count";
$dateRange = Carbon::parseDateRangeValue($range);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$results = Capsule::table('tblcustomfieldsvalues')
->select(Capsule::raw('tblcustomfieldsvalues.value, count(*) as `rows`'))
->join('tblclients', 'tblclients.id', '=', 'tblcustomfieldsvalues.relid')
->where('fieldid', '=', (int) $customfieldid)
->whereBetween(
'datecreated',
[
$fromdate,
$todate,
]
)
->orderBy('value', 'asc')
->groupBy('value')
->get()
->all();
foreach ($results as $result) {
$reportdata["tablevalues"][] = [$result->value, $result->rows];
$chartdata['rows'][] = [
'c' => [
['v' => $result->value],
['v' => $result->rows, 'f' => $result->rows],
],
];
}
$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');
ticket_ratings_reviewer.php 0000644 00000010154 14736262117 0012211 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
use WHMCS\View\Markup\Markup;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$rating = App::getFromRequest('rating');
if (!$rating) {
$rating = '1';
}
$range = App::getFromRequest('range');
if (!$range) {
$today = Carbon::today()->endOfDay();
$lastWeek = Carbon::today()->subDays(6)->startOfDay();
$range = $lastWeek->toAdminDateFormat() . ' - ' . $today->toAdminDateFormat();
}
$dateRange = Carbon::parseDateRangeValue($range);
$startdate = $dateRange['from'];
$enddate = $dateRange['to'];
$rsel[$rating] = ' selected="selected"';
$markup = new Markup;
$results = Capsule::table('tblticketreplies')
->select(Capsule::raw('tblticketreplies.*, tbltickets.tid AS ticketid'))
->join('tbltickets', 'tbltickets.id', '=', 'tblticketreplies.tid')
->where('tblticketreplies.admin', '!=', '')
->where('tblticketreplies.rating', '=', (int) $rating)
->whereBetween(
'tblticketreplies.date',
[
$startdate->toDateString(),
$enddate->toDateString(),
]
)
->orderBy('date', 'desc')
->get()
->all();
$num_rows = count($results);
$reportdata["title"] = "Support Ticket Ratings Reviewer";
$reportdata["description"] = "This report is showing all $num_rows ticket replies rated $rating between"
. " {$startdate->toAdminDateFormat()} & {$enddate->toAdminDateFormat()} for review";
$reportdata['headertext'] = '';
if (!$print) {
$reportdata['headertext'] = <<<HTML
<form method="post" action="reports.php?report={$report}¤cyid={$currencyid}&calculate=true">
<div class="report-filters-wrapper">
<div class="inner-container">
<h3>Filters</h3>
<div class="row">
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterRating">Rating</label>
<select id="inputFilterRating" name="rating" class="form-control">
<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>
</div>
</div>
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterDate">{$dateRangeText}</label>
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="range"
value="{$range}"
class="form-control date-picker-search"
/>
</div>
</div>
</div>
</div>
<button type="submit" class="btn btn-primary">
{$aInt->lang('reports', 'generateReport')}
</button>
</div>
</div>
</form>
HTML;
}
$reportdata["tableheadings"] = array(
"Ticket #",
"Date",
"Message",
"Admin",
"Rating",
);
foreach ($results as $result) {
$tid = $result->tid;
$ticketid = $result->ticketid;
$date = $result->date;
$message = $result->message;
$admin = $result->admin;
$rating = $result->rating;
$editor = $result->editor;
$date = fromMySQLDate($date, true);
$markupFormat = $markup->determineMarkupEditor('ticket_reply', $editor);
$message = $markup->transform($message, $markupFormat);
$reportdata["tablevalues"][] = array(
'<a href="supporttickets.php?action=viewticket&id=' . $tid . '">' . $ticketid . '</a>',
$date,
'<div align="left">' . $message . '</div>',
$admin,
$rating,
);
}
income_forecast.php 0000644 00000010414 14736262117 0010426 0 ustar 00 <?php
use WHMCS\Billing\Cycles;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$months = array('January','February','March','April','May','June','July','August','September','October','November','December');
$cyclesMax = 36; // in months
$reportdata["title"] = "Income Forecast";
$reportdata["description"] = "This report shows the projected income for each month of the year if all active services are renewed within that month";
$reportdata["currencyselections"] = true;
$reportdata['tableheadings'] = [
'Month',
'Monthly',
'Quarterly',
'Semi-Annual',
'Annual',
'Biennial',
'Triennial',
'Total',
];
$totals = array();
$cycles = new Cycles;
$results = Capsule::table('tblhosting')
->where('tblhosting.domainstatus', '=', 'Active')
->where('tblclients.currency', '=', (int) $currencyid)
->whereIn('tblhosting.billingcycle', array_values($cycles->getRecurringCycles()))
->join('tblclients', 'tblclients.id', '=', 'tblhosting.userid')
->get()
->all();
foreach ($results as $result) {
$recurringamount = $result->amount;
$nextduedate = $result->nextduedate;
$billingcycle = $result->billingcycle;
$nextduedate = explode("-", $nextduedate);
$year = $nextduedate[0];
$month = $nextduedate[1];
if ($cycles->isFree($billingcycle)) {
continue;
}
try {
$recurrence = $cycles->getNumberOfMonths($billingcycle);
} catch (Exception $e) {
$recurrence = $cyclesMax;
}
for ($i = 0; $i <= $cyclesMax; $i += $recurrence) {
$new_time = mktime(0, 0, 0, ($month + $i), 1, $year);
if ($new_time === false) {
continue;
}
$totals[date("Y", $new_time)][date("m", $new_time)][$recurrence] += $recurringamount;
}
}
$results = Capsule::table('tbldomains')
->where('tbldomains.status', '=', 'Active')
->where('tblclients.currency', '=', (int) $currencyid)
->join('tblclients', 'tblclients.id', '=', 'tbldomains.userid')
->get()
->all();
foreach ($results as $result) {
$recurringamount = $result->recurringamount;
$nextduedate = $result->nextduedate;
$regperiod = $result->registrationperiod;
$nextduedate = explode("-", $nextduedate);
$year = $nextduedate[0];
$month = $nextduedate[1];
if (!$regperiod) {
$regperiod = 1;
}
$recurrence = ($regperiod * 12);
for ($i = 0; $i <= $cyclesMax; $i += $recurrence) {
$new_time = mktime(0, 0, 0, ($month + $i), 1, $year);
if ($new_time === false) {
continue;
}
$totals[date("Y", $new_time)][date("m", $new_time)][$recurrence] += $recurringamount;
}
}
for ($i = 0; $i <= $cyclesMax; $i++) {
$new_time = mktime(0,0,0,date("m")+$i,1,date("Y"));
if ($new_time === false) {
continue;
}
$months_array[date("Y",$new_time)][date("m",$new_time)] = "x";
}
$overallincome = 0;
foreach ($months_array AS $year=>$month) {
foreach ($month AS $mon=>$x) {
$monthlyincome = $totals[$year][$mon][1]
+ $totals[$year][$mon][3]
+ $totals[$year][$mon][6]
+ $totals[$year][$mon][12]
+ $totals[$year][$mon][24]
+ $totals[$year][$mon][$cyclesMax];
$overallincome += $monthlyincome;
$chartdata['rows'][] = array('c'=>array(array('v'=>$months[$mon-1]." ".$year),array('v'=>$overallincome,'f'=>formatCurrency($overallincome))));
$reportdata["tablevalues"][] = array(
$months[$mon-1]." ".$year,
formatCurrency($totals[$year][$mon][1]),
formatCurrency($totals[$year][$mon][3]),
formatCurrency($totals[$year][$mon][6]),
formatCurrency($totals[$year][$mon][12]),
formatCurrency($totals[$year][$mon][24]),
formatCurrency($totals[$year][$mon][$cyclesMax]),
formatCurrency($monthlyincome));
}
}
$reportdata["footertext"] = "<p align=\"center\"><b>Total Projected Income: ".formatCurrency($overallincome)."</b></p>";
$chartdata['cols'][] = array('label'=>'Month','type'=>'string');
$chartdata['cols'][] = array('label'=>'Cumulative Income Forecast Total','type'=>'number');
#$args['colors'] = '#80D044,#F9D88C,#CC0000';
$reportdata["headertext"] = $chart->drawChart('Area',$chartdata,$args,'450px');
index.php 0000644 00000000052 14736262117 0006372 0 ustar 00 <?php
header("Location: ../../index.php"); invoices.php 0000644 00000024565 14736262117 0007121 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Invoices";
$filterfields = [
'id' => 'ID',
'userid' => 'User ID',
'clientname' => 'Client Name',
'invoicenum' => 'Invoice Number',
'date' => 'Creation Date',
'duedate' => 'Due Date',
'datepaid' => 'Date Paid',
'date_refunded' => 'Date Refunded',
'date_cancelled' => 'Date Cancelled',
'subtotal' => 'Subtotal',
'credit' => 'Credit',
'tax' => 'Tax',
'tax2' => 'Tax2',
'total' => 'Total',
'taxrate' => 'Tax Rate',
'taxrate2' => 'Tax Rate 2',
'status' => 'Status',
'paymentmethod' => 'Payment Method',
'notes' => 'Notes',
];
$dateRangeFields = [
'date' => 'Creation Date',
'duedate' => 'Due Date',
'datepaid' => 'Date Paid',
'date_refunded' => 'Date Refunded',
'date_cancelled' => 'Date Cancelled',
];
$removedDateRangeFields = array_diff($filterfields, $dateRangeFields);
$reportdata["description"] = $reportdata["headertext"] = '';
$incfields = $whmcs->get_req_var('incfields');
$filterfield = $whmcs->get_req_var('filterfield');
$filtertype = $whmcs->get_req_var('filtertype');
$filterq = $whmcs->get_req_var('filterq');
$createDateRange = App::getFromRequest('createDateRange');
$dueDateRange = App::getFromRequest('dueDateRange');
$datePaidRange = App::getFromRequest('datePaidRange');
$dateRefundedRange = App::getFromRequest('dateRefundedRange');
$dateCancelledRange = App::getFromRequest('dateCancelledRange');
if (!is_array($incfields)) {
$incfields = array();
}
if (!is_array($filterfield)) {
$filterfield = array();
}
if (!is_array($filtertype)) {
$filtertype = array();
}
if (!is_array($filterq)) {
$filterq = array();
}
if (!$print) {
$reportdata["description"] = "This report can be used to generate a custom export of"
. " invoices by applying up to 5 filters. CSV Export is available via the Tools menu to the right.";
$reportdata["headertext"] = '<form method="post" action="reports.php?report=' . $report . '">
<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
<tr><td width="20%" class="fieldlabel">Fields to Include</td><td class="fieldarea"><table width="100%"><tr>';
$i=0;
foreach ($filterfields as $k => $v) {
$reportdata["headertext"] .= '<td width="20%"><input type="checkbox" name="incfields[]" value="' . $k . '" id="fd' . $k . '"';
if (in_array($k, $incfields)) {
$reportdata["headertext"] .= ' checked';
}
$reportdata["headertext"] .= ' /> <label for="fd' . $k . '">' . $v . '</label></td>';
$i++;
if (($i%5) == 0) {
$reportdata["headertext"] .= '</tr><tr>';
}
}
$reportdata["headertext"] .= '</tr></table></td></tr>';
for ($i = 1; $i <= 5; $i ++) {
$reportdata["headertext"] .= '<tr><td width="20%" class="fieldlabel">Filter ' . $i . '</td><td class="fieldarea"><select name="filterfield[' . $i . ']" class="form-control select-inline"><option value="">None</option>';
foreach ($removedDateRangeFields as $k => $v) {
$reportdata["headertext"] .= '<option value="' . $k . '"';
if (isset($filterfield[$i]) && $filterfield[$i] == $k) {
$reportdata["headertext"] .= ' selected';
}
$reportdata["headertext"] .= '>' . $v . '</option>';
}
$reportdata["headertext"] .= '</select> <select name="filtertype[' . $i . ']" class="form-control select-inline">'
. '<option value="=">Exact Match</option><option value="like"';
if (isset($filtertype[$i]) && $filtertype[$i] == "like") {
$reportdata["headertext"] .= ' selected';
}
$reportdata["headertext"] .= '>Containing</option></select>'
. ' <input type="text" name="filterq[' . $i . ']" class="form-control input-inline input-250" value="' . (isset($filterq[$i]) ? $filterq[$i] : '') . '" /></td></tr>';
}
$reportdata["headertext"] .= <<<HTML
<tr>
<td width="20%" class="fieldlabel">Creation Date Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="createDateRange"
value="{$createDateRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
<tr>
<td width="20%" class="fieldlabel">Due Date Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="dueDateRange"
value="{$dueDateRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
<tr>
<td width="20%" class="fieldlabel">Date Paid Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="datePaidRange"
value="{$datePaidRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
<tr>
<td width="20%" class="fieldlabel">Date Refunded Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="dateRefundedRange"
value="{$dateRefundedRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
<tr>
<td width="20%" class="fieldlabel">Date Cancelled Range</td>
<td class="fieldarea">
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="dateCancelledRange"
value="{$dateCancelledRange}"
class="form-control date-picker-search"
/>
</div>
</td>
</tr>
</table>
<p align="center"><input type="submit" value="Filter" class="btn btn-primary"/></p>
</form>
HTML;
}
if (count($incfields)) {
$query = Capsule::table('tblinvoices');
foreach ($filterfield as $i => $val) {
if ($val && array_key_exists($val, $filterfields)) {
if ($filtertype[$i] == 'like') {
$filterq[$i] = "%{$filterq[$i]}%";
}
if ($val == 'clientname') {
$query->whereRaw(
"concat(tblclients.firstname, ' ', tblclients.lastname) "
. "{$filtertype[$i]} '{$filterq[$i]}'"
);
} else {
$query->where(
"tblinvoices.{$filterfield[$i]}",
$filtertype[$i],
$filterq[$i]
);
}
}
}
foreach ($incfields as $fieldname) {
if (array_key_exists($fieldname, $filterfields)) {
$reportdata["tableheadings"][] = $filterfields[$fieldname];
if ($fieldname == "clientname") {
$query->addSelect(Capsule::raw("concat(tblclients.firstname, ' ', tblclients.lastname)"));
} else {
$query->addSelect("tblinvoices.{$fieldname}");
}
}
}
if ($createDateRange) {
$dateRange = Carbon::parseDateRangeValue($createDateRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('date', [$fromdate, $todate]);
}
if ($dueDateRange) {
$dateRange = Carbon::parseDateRangeValue($dueDateRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('duedate', [$fromdate, $todate]);
}
if ($datePaidRange) {
$dateRange = Carbon::parseDateRangeValue($datePaidRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('datepaid', [$fromdate, $todate]);
}
if ($dateRefundedRange) {
$dateRange = Carbon::parseDateRangeValue($dateRefundedRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('date_refunded', [$fromdate, $todate]);
}
if ($dateCancelledRange) {
$dateRange = Carbon::parseDateRangeValue($dateCancelledRange);
$fromdate = $dateRange['from']->toDateTimeString();
$todate = $dateRange['to']->toDateTimeString();
$query->whereBetween('date_cancelled', [$fromdate, $todate]);
}
$results = $query
->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
->get()
->all();
foreach ($results as $result) {
$result = (array) $result;
if (isset($result['paymentmethod'])) {
$result['paymentmethod'] = $gateways->getDisplayName($result['paymentmethod']);
}
$reportdata["tablevalues"][] = $result;
}
}
monthly_orders.php 0000644 00000006713 14736262117 0010345 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Sales by Product for " . $months[(int) $month] . " " . $year;
$reportdata["description"] = "This report gives a breakdown of the number of units sold of each product per month";
$reportdata["currencyselections"] = true;
$total = 0;
$prevgroup = '';
$datefilter = Carbon::create(
$year,
$month,
1
);
$reportdata["tableheadings"] = ["Product Name", "Units Sold", "Value",];
$results = Capsule::table('tblproducts')
->select(
'tblproducts.id',
'tblproducts.name',
'tblproductgroups.name as groupname'
)
->join('tblproductgroups', 'tblproducts.gid', '=', 'tblproductgroups.id')
->orderBy('tblproductgroups.order', 'asc')
->orderBy('tblproducts.order', 'asc')
->orderBy('tblproducts.name', 'asc')
->get()
->all();
foreach ($results as $result) {
$pid = $result->id;
$group = $result->groupname;
$prodname = $result->name;
if ($group != $prevgroup) {
$reportdata["tablevalues"][] = ["**<b>$group</b>",];
}
$data = Capsule::table('tblhosting')
->select(
[
Capsule::raw('COUNT(tblhosting.id) as total'),
Capsule::raw('SUM(tblhosting.firstpaymentamount) as amount')
]
)
->where('tblhosting.packageid', $pid)
->where('tblhosting.domainstatus', 'Active')
->whereBetween(
'tblhosting.regdate',
[
$datefilter->startOfMonth()->toDateTimeString(),
$datefilter->endOfMonth()->toDateTimeString()
]
)
->where('tblclients.currency', $currencyid)
->join('tblclients', 'tblclients.id', '=', 'tblhosting.userid')
->first();
$number = $data->total;
$amount = $data->amount;
$total += $amount;
$amount = formatCurrency($amount);
$reportdata["tablevalues"][] = [$prodname, $number, $amount,];
$prevgroup = $group;
}
$reportdata["tablevalues"][] = ["**<b>Addons</b>",];
$results = Capsule::table('tbladdons')
->orderBy('name', 'asc')
->get()
->all();
foreach ($results as $result) {
$pid = $result->id;
$prodname = $result->name;
$data = Capsule::table('tblhostingaddons')
->select(
[
Capsule::raw('COUNT(tblhostingaddons.id) as total'),
Capsule::raw('SUM(tblhostingaddons.setupfee + tblhostingaddons.recurring) as amount')
]
)
->where('tblhostingaddons.addonid', $pid)
->where('tblhostingaddons.status', 'Active')
->whereBetween(
'tblhostingaddons.regdate',
[
$datefilter->startOfMonth()->toDateTimeString(),
$datefilter->endOfMonth()->toDateTimeString()
]
)
->where('tblclients.currency', $currencyid)
->join('tblhosting', 'tblhosting.id', '=', 'tblhostingaddons.hostingid')
->join('tblclients', 'tblclients.id', '=', 'tblhosting.userid')
->first();
$number = $data->total;
$amount = $data->amount;
$total += $amount;
$amount = formatCurrency($amount);
$reportdata["tablevalues"][] = [$prodname, $number, $amount,];
$prevgroup = $group;
}
$total = formatCurrency($total);
$reportdata["footertext"] = '<p align="center"><strong>Total: ' . $total . '</strong></p>';
$reportdata["monthspagination"] = true;
aging_invoices.php 0000644 00000011114 14736262117 0010250 0 ustar 00 <?php
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Aging Invoices";
$reportdata["description"] = "A summary of outstanding invoices broken down "
. "into the period of which they are overdue";
$reportdata["tableheadings"][] = "Period";
foreach ($currencies as $currencyid => $currencyname) {
$reportdata["tableheadings"][] = "{$currencyname} Amount";
}
$totals = [];
for ( $day = 0; $day < 120; $day += 30) {
$startdate = date(
"Y-m-d",
mktime(0, 0, 0, date("m"), (date("d") - $day), date("Y"))
);
$enddate = date(
"Y-m-d",
mktime(0, 0, 0, date("m"), (date("d") - ($day + 30)), date("Y"))
);
$rowdata = [];
$rowdata[] = "{$day} - " . ($day + 30);
$currencytotals = [];
$subQuery = Capsule::table('tblaccounts')
->select(Capsule::raw('sum(amountin - amountout)'))
->join('tblinvoices', 'tblinvoices.id', '=', 'tblaccounts.invoiceid')
->join('tblclients as t2', 't2.id', '=', 'tblinvoices.userid')
->where('tblinvoices.duedate', '<=', $startdate)
->where('tblinvoices.duedate', '>=', $enddate)
->where('tblinvoices.status', '=', 'Unpaid')
->where('t2.currency', '=', 'tblclients.currency');
$results = Capsule::table('tblinvoices')
->select(
'tblclients.currency',
Capsule::raw('sum(tblinvoices.total) as `sum`'),
Capsule::raw('(' . $subQuery->toSQL() . ') as `sum2`')
)
->mergeBindings($subQuery)
->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
->where('tblinvoices.duedate', '<=', $startdate)
->where('tblinvoices.duedate', '>=', $enddate)
->where('tblinvoices.status', '=', 'Unpaid')
->groupBy('tblclients.currency')
->get()
->all();
foreach ($results as $result) {
$currencytotals[$result->currency] = ($result->sum - $result->sum2);
}
foreach ($currencies as $currencyid => $currencyname) {
$currencyamount = $currencytotals[$currencyid];
if (!$currencyamount) {
$currencyamount = 0;
}
$totals[$currencyid] += $currencyamount;
$currency = getCurrency(null, $currencyid);
$rowdata[] = formatCurrency($currencyamount);
if ($currencyid == $defaultcurrencyid) {
$chartdata['rows'][] = [
'c' => [
['v' => "{$day} - " . ($day + 30)],
[
'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 = [];
$rowdata[] = "120 +";
$currencytotals = [];
$results = Capsule::table('tblinvoices')
->select(
'tblclients.currency',
Capsule::raw('sum(tblinvoices.total) as `sum`')
)
->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
->where('tblinvoices.duedate', '<=', $startdate)
->where('tblinvoices.status', '=', 'Unpaid')
->groupBy('tblclients.currency')
->get()
->all();
foreach ($results as $result) {
$currencytotals[$result->currency] = $result->sum;
}
foreach ($currencies as $currencyid => $currencyname) {
$currencyamount = $currencytotals[$currencyid];
if (!$currencyamount) {
$currencyamount=0;
}
$totals[$currencyid] += $currencyamount;
$currency = getCurrency(null, $currencyid);
$rowdata[] = formatCurrency($currencyamount);
if ($currencyid == $defaultcurrencyid) {
$chartdata['rows'][] = [
'c' => [
['v' => "{$day} + "],
[
'v' => $currencyamount,
'f' => formatCurrency($currencyamount),
],
]
];
}
}
$reportdata["tablevalues"][] = $rowdata;
$rowdata = [];
$rowdata[] = "<b>Total</b>";
foreach ($currencies as $currencyid => $currencyname) {
$currencytotal = $totals[$currencyid];
if (!$currencytotal) {
$currencytotal=0;
}
$currency = getCurrency(null, $currencyid);
$rowdata[] = "<b>" . formatCurrency($currencytotal) . "</b>";
}
$reportdata["tablevalues"][] = $rowdata;
$chartdata['cols'][] = ['label'=>'Days Range', 'type'=>'string'];
$chartdata['cols'][] = ['label'=>'Value', 'type'=>'number'];
$args = [];
$args['legendpos'] = 'right';
$reportdata["footertext"] = $chart->drawChart('Pie', $chartdata, $args, '300px');
support_ticket_replies.php 0000644 00000004356 14736262117 0012100 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
/** @type string $currentmonth */
/** @type int $currentyear */
$dateRange = Carbon::create($year, $month, 1);
$reportdata['title'] = "Support Ticket Replies for " . $currentmonth . " " . $currentyear;
$reportdata['description'] = "This report shows a breakdown of support tickets dealt with per admin for a given month";
$reportdata['monthspagination'] = true;
$reportdata['tableheadings'][] = "Admin";
for ($day = 1; $day <= $dateRange->endOfMonth()->day; $day++) {
$reportdata['tableheadings'][] = $day;
}
$reportvalues = array();
$result = Capsule::table('tblticketreplies')
->where('admin', '!=', '')
->whereBetween(
'date',
[
$dateRange->firstOfMonth()->toDateTimeString(),
$dateRange->endOfMonth()->toDateTimeString(),
]
)
->orderBy('admin')
->orderBy('date')
->groupBy(
[
'admin',
Capsule::raw('date_format(date, \'%e\')'),
]
)
->get(
[
'admin',
Capsule::raw('date_format(date, \'%e\') as day_of_month'),
Capsule::raw('COUNT(tid) as total_replies'),
Capsule::raw('COUNT(DISTINCT tid) as total_tickets'),
]
)
->all();
foreach ($result as $data) {
$adminname = $data->admin;
$day = $data->day_of_month;
$reportvalues[$adminname][$day] = array(
"totalreplies" => $data->total_replies,
"totaltickets" => $data->total_tickets,
);
}
$rc = 0;
foreach ($reportvalues as $adminname => $values) {
$reportdata['tablevalues'][$rc][] = "**$adminname";
$rc++;
$reportdata['tablevalues'][$rc][] = "Tickets";
$reportdata['tablevalues'][$rc+1][] = "Replies";
for ($day = 1; $day <= $dateRange->endOfMonth()->day; $day++) {
$reportdata['tablevalues'][$rc][] = isset($reportvalues[$adminname][$day]['totaltickets'])
? $reportvalues[$adminname][$day]['totaltickets']
: '';
$reportdata['tablevalues'][$rc+1][] = isset($reportvalues[$adminname][$day]['totalreplies'])
? $reportvalues[$adminname][$day]['totalreplies']
: '';
}
$rc += 2;
}
affiliates_overview.php 0000644 00000005135 14736262117 0011327 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$today = Carbon::today();
$reportdata["title"] = "Affiliates Overview";
$reportdata["description"] = "An overview of affiliates for the current year";
$reportdata["tableheadings"] = [
'Affiliate ID',
'Affiliate Name',
'Visitors',
'Pending Commissions',
'Available to Withdraw',
'Withdrawn Amount',
'YTD Total Commissions Paid',
];
$results = Capsule::table('tblaffiliates')
->select(
'tblaffiliates.id',
'tblaffiliates.clientid',
'tblaffiliates.visitors',
'tblaffiliates.balance',
'tblaffiliates.withdrawn',
'tblclients.firstname',
'tblclients.lastname',
'tblclients.companyname'
)
->join('tblclients', 'tblclients.id', '=', 'tblaffiliates.clientid')
->orderBy('visitors', 'desc')
->get()
->all();
foreach ($results as $result) {
$affid = $result->id;
$clientid = $result->clientid;
$visitors = $result->visitors;
$balance = $result->balance;
$withdrawn = $result->withdrawn;
$firstname = $result->firstname;
$lastname = $result->lastname;
$companyname = $result->companyname;
$name = $firstname . ' ' . $lastname;
if ($companyname) {
$name .= ' (' . $companyname . ')';
}
$pendingcommissionsamount = Capsule::table('tblaffiliatespending')
->join('tblaffiliatesaccounts', 'tblaffiliatesaccounts.id', '=', 'tblaffiliatespending.affaccid')
->join('tblhosting', 'tblhosting.id', '=', 'tblaffiliatesaccounts.relid')
->join('tblproducts', 'tblproducts.id', '=', 'tblhosting.packageid')
->join('tblclients', 'tblclients.id', '=', 'tblhosting.userid')
->where('affiliateid', '=', $affid)
->orderBy('clearingdate', 'desc')
->sum('tblaffiliatespending.amount');
$ytdtotal = Capsule::table('tblaffiliateshistory')
->where('affiliateid', $affid)
->whereBetween(
'date',
[
$today->startOfYear()->toDateTimeString(),
$today->endOfYear()->toDateTimeString(),
]
)
->sum('amount');
$currency = getCurrency($clientid);
$pendingcommissionsamount = formatCurrency($pendingcommissionsamount);
$ytdtotal = formatCurrency($ytdtotal);
$reportdata["tablevalues"][] = [
'<a href="affiliates.php?action=edit&id=' . $affid . '">' . $affid . '</a>',
$name,
$visitors,
$pendingcommissionsamount,
$balance,
$withdrawn,
$ytdtotal,
];
}
ssl_certificate_monitoring.php 0000644 00000017655 14736262117 0012714 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
use WHMCS\Domain\Ssl\Status;
/** @type array $reportdata */
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$refreshButtonNumber = 25;
$showAll = App::getFromRequest('showall');
$title = 'SSL Certificate Monitoring';
$reportdata['title'] = $title;
$reportdata['description'] = 'Displays a list of domains with their SSL status, if available.'
. '<p>';
if (!$showAll) {
$reportdata['description'] .= '<a href="' . $requeststr . '&showall=1" class="btn btn-default btn-sm">'
. 'Show inactive domains'
. '</a>';
} else {
$reportdata['description'] .= '<a href="' . $requeststr . '&showall=0" class="btn btn-default btn-sm">'
. 'Hide inactive domains'
. '</a> ';
}
$reportdata['description'] .= ' <button type="button" class="refresh-ssl btn btn-default btn-sm">'
. 'Re-validate SSL Status'
. '</button></p>';
$reportdata['tableheadings'][] = 'Domain Name';
$reportdata['tableheadings'][] = 'Status';
$reportdata['tableheadings'][] = 'Issuer';
$reportdata['tableheadings'][] = 'Expiry';
$reportdata['tableheadings'][] = 'Last Update';
$unionQuery = Capsule::table('tbldomains')
->select(['domain', 'userid']);
if (!$showAll) {
$unionQuery->whereIn('status', ['Active', 'Grace']);
}
$data = Capsule::table('tblhosting')
->where('domain', '!=', '')
->select(['domain', 'userid'])
->union($unionQuery)
->orderBy('domain');
if (!$showAll) {
$data->whereIn('domainstatus', ['Active', 'Completed']);
}
$reportKeys = [
'in30Days',
'in90Days',
'in180Days',
'moreThan180Days',
'noSsl'
];
$reportData = [
$reportKeys[0] => [],
$reportKeys[1] => [],
$reportKeys[2] => [],
$reportKeys[3] => [],
$reportKeys[4] => [],
];
$today = Carbon::now();
$domainsArray = [];
/** @var stdClass $record */
foreach ($data->get() as $record) {
$isDomain = (str_replace('.', '', $record->domain) != $record->domain);
if (!$isDomain) {
continue;
}
if (in_array($domain, $domainsArray)) {
continue;
}
$sslStatus = Status::factory($record->userid, $record->domain)->disableAutoResync();
$expiryDate = $sslStatus->expiryDate;
$reportKey = $reportKeys[4];
if ($expiryDate) {
$daysUntilExpiry = $expiryDate->diffInDays($today);
$expiryDate = $expiryDate->endOfDay();
switch (true) {
case ($daysUntilExpiry <= 30):
$reportKey = $reportKeys[0];
break;
case ($daysUntilExpiry <= 90):
$reportKey = $reportKeys[1];
break;
case ($daysUntilExpiry <= 180):
$reportKey = $reportKeys[2];
break;
default:
$reportKey = $reportKeys[3];
}
}
if (!$expiryDate) {
$expiryDate = '-';
}
$html = '<img src="%s" data-toggle="tooltip" title="%s" class="%s" data-domain="%s" data-user-id="%d" />';
$image = sprintf(
$html,
$sslStatus->getImagePath(),
$sslStatus->getTooltipContent(),
'ssl-state',
$record->domain,
$record->userid
);
$issuerName = '';
if ($sslStatus->issuerName) {
$issuerName = $sslStatus->issuerOrg;
if (!$issuerName) {
$issuerName = $sslStatus->issuerName;
}
}
$reportData[$reportKey][] = [
'domain' => $record->domain,
'image' => $image,
'issuer' => ($issuerName ?: '-'),
'expiry' => $expiryDate,
'updated' => ($sslStatus->updated_at ? $sslStatus->updated_at->diffForHumans() : '-'),
];
$domainsArray[] = $record->domain;
}
$rowCount = 0;
foreach ($reportKeys as $reportKey) {
if (
count($reportData[$reportKey]) === 0
&& $reportKey == $reportKeys[(count($reportKeys) - 1)]
) {
continue;
}
$reportdata['tablevalues'][$rowCount][] = "**" . AdminLang::trans('sslState.' . $reportKey);
$rowCount++;
if (count($reportData[$reportKey]) === 0) {
$reportdata['tablevalues'][$rowCount][] = '*+' . AdminLang::trans('global.norecordsfound');
$rowCount++;
continue;
}
if ($reportKey != $reportKeys[(count($reportKeys) - 1)]) {
usort($reportData[$reportKey], function ($first, $second) {
if ($first['expiry'] == $second['expiry']) {
return 0;
}
return (($first['expiry']->lt($second['expiry'])) ? -1 : 1);
});
}
foreach ($reportData[$reportKey] as $reportDatum) {
$spanElements = [
'issuer',
'updated',
'expiry',
];
$expiryDate = $reportDatum['expiry'];
if ($expiryDate != '-') {
$reportDatum['expiry'] = $expiryDate->toAdminDateTimeFormat();
}
foreach ($reportDatum as $key => $value) {
if (in_array($key, $spanElements)) {
$reportDatum[$key] = '<span class="' . $key . '">' . $value . '</span>';
}
}
$reportdata['tablevalues'][$rowCount] = $reportDatum;
$rowCount++;
}
}
$langLoading = AdminLang::trans('global.loading');
$loadingImg = DI::make('asset')->getImgPath() . '/ssl/ssl-loading.gif';
$lastUpdated = Carbon::now()->diffForHumans();
$reportdata['footertext'] = <<<JAVASCRIPT
<script>
jQuery(document).ready(function() {
jQuery('[data-toggle="tooltip"]').tooltip();
var startCount = 0,
totalCount = jQuery('.ssl-state').length,
processing = 0,
refreshAmount = {$refreshButtonNumber},
timed = null;
if (refreshAmount > totalCount) {
refreshAmount = totalCount;
}
jQuery(document).on('click', '.refresh-ssl', function() {
jQuery(this).attr('disabled', true).addClass('disabled');
jQuery('.ssl-state').each(function (index) {
if (index >= startCount) {
processing += 1;
var self = jQuery(this);
var domain = self.data('domain');
var userid = self.data('user-id');
self.attr('src', '{$loadingImg}')
.attr('title', '{$langLoading}')
.tooltip('fixTitle');
WHMCS.http.jqClient.post(
WHMCS.adminUtils.getAdminRouteUrl('/domains/ssl-check'),
{
'domain': domain,
'userid': userid,
'details': true,
'token': csrfToken
},
function (data) {
self.attr('src', data.image)
.attr('title', data.tooltip)
.tooltip('fixTitle')
.attr('class', data.class)
.closest('tr').find('span.issuer').html(data.issuerName).end()
.find('span.expiry').html(data.expiryDate).end()
.find('span.updated').html('{$lastUpdated}');
processing -= 1;
}
);
}
if (
index === ((startCount + refreshAmount) - 1)
|| index === (totalCount - 1)
) {
timed = setInterval(checkProcessing, 1000);
startCount = index + 1;
if (startCount >= totalCount) {
startCount = 0;
}
return false;
}
});
});
function checkProcessing()
{
if (processing === 0) {
jQuery('.refresh-ssl').attr('disabled', false)
.removeClass('disabled');
clearInterval(timed);
}
}
});
</script>
JAVASCRIPT;
domain_renewal_emails.php 0000644 00000014132 14736262117 0011605 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
use WHMCS\Input\Sanitize;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
if (!function_exists('getRegistrarsDropdownMenu')) {
require(ROOTDIR . '/includes/registrarfunctions.php');
}
$reportdata["title"] = $aInt->lang('reports', 'domainRenewalEmailsTitle');
$userID = App::getFromRequest('userid');
$domain = App::getFromRequest('domain');
$range = App::getFromRequest('range');
$registrar = App::getFromRequest('registrar');
$print = App::getFromRequest('print');
/**
* Replace the "None" string with the "Any" string
*/
$registrarList = str_replace(
[
$aInt->lang('global', 'none'),
],
[
$aInt->lang('global', 'any'),
],
getRegistrarsDropdownMenu($registrar)
);
$registrarList = str_replace(' select-inline', '', $registrarList);
$reportdata["description"] = $aInt->lang('reports', 'domainRenewalEmailsDescription');
$reportHeader = '';
if (!$print) {
$reportHeader = <<<REPORT_HEADER
<form method="post" action="reports.php?report={$report}">
<div class="report-filters-wrapper">
<div class="inner-container">
<h3>Filters</h3>
<div class="row">
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterClient">{$aInt->lang('fields', 'client')}</label>
{$aInt->clientsDropDown($userID, '', 'userid', true)}
</div>
</div>
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterRegistrar">{$aInt->lang('fields', 'registrar')}</label>
{$registrarList}
</div>
</div>
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterDomain">{$aInt->lang('fields', 'domain')}</label>
<input type="text" name="domain" value="{$domain}" class="form-control" id="inputFilterDomain" placeholder="{$optionalText}">
</div>
</div>
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterDate">{$dateRangeText}</label>
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="range"
value="{$range}"
class="form-control date-picker-search"
data-opens="left"
placeholder="{$optionalText}"
/>
</div>
</div>
</div>
</div>
<button type="submit" class="btn btn-primary">
{$aInt->lang('global', 'apply')}
</button>
</div>
</div>
</form>
REPORT_HEADER;
}
$reportdata["headertext"] = $reportHeader;
$reportdata["tableheadings"] = array(
$aInt->lang('fields', 'client'),
$aInt->lang('fields', 'domain'),
$aInt->lang('fields', 'dateSent'),
$aInt->lang('domains', 'reminder'),
$aInt->lang('emails', 'recipients'),
$aInt->lang('domains', 'sent'),
);
$typeMap = array(
1 => $aInt->lang('domains', 'firstReminder'),
2 => $aInt->lang('domains', 'secondReminder'),
3 => $aInt->lang('domains', 'thirdReminder'),
4 => $aInt->lang('domains', 'fourthReminder'),
5 => $aInt->lang('domains', 'fifthReminder'),
);
# Report Footer Text - this gets displayed below the report table of data
$data["footertext"] = "";
$query = Capsule::table('tbldomainreminders')
->join(
'tbldomains',
'tbldomains.id',
'=',
'tbldomainreminders.domain_id'
)
->join(
'tblclients',
'tblclients.id',
'=',
'tbldomains.userid'
)
->select(
[
'tbldomainreminders.id AS reminder_id',
'tbldomainreminders.date',
'tbldomainreminders.type',
'tbldomainreminders.days_before_expiry',
'tbldomainreminders.recipients',
'tblclients.firstname',
'tblclients.lastname',
'tblclients.companyname',
'tbldomains.domain',
]
)
->orderBy('reminder_id', 'desc');
$where = array();
if ($userID) {
$query->where('tblclients.id', (int) $userID);
}
if ($domain) {
$query->where('tbldomains.domain', Sanitize::encode($domain));
}
if ($range) {
$dateRange = Carbon::parseDateRangeValue($range);
$dateFrom = $dateRange['from']->toDateTimeString();
$dateTo = $dateRange['to']->toDateTimeString();
$query->whereBetween(
'tbldomainreminders.date',
[
$dateFrom,
$dateTo,
]
);
}
if ($registrar) {
$query->where('tbldomains.registrar', $registrar);
}
foreach ($query->get() as $data) {
$data = (array) $data;
$companyName = '';
if ($data['companyname']) {
$companyName = ' ' . $data['companyname'];
}
$client = sprintf(
'%s %s%s',
$data['firstname'],
$data['lastname'],
$companyName
);
$domain = $data['domain'];
$date = $data['date'];
$type = $typeMap[$data['type']];
$recipients = $data['recipients'];
$days_before_expiry = sprintf(
$aInt->lang('domains', 'beforeExpiry'),
$data['days_before_expiry']
);
if ($data['days_before_expiry'] < 0) {
$days_before_expiry = sprintf(
$aInt->lang('domains', 'afterExpiry'),
($data['days_before_expiry'] * -1)
);
}
$reportdata["tablevalues"][] = array(
$client,
$domain,
$date,
$type,
$recipients,
$days_before_expiry
);
}
new_customers.php 0000644 00000010562 14736262117 0010167 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
/** @var string $requeststr */
$reportdata["title"] = "New Customers";
$reportdata["description"] = "This report shows the total number of new customers, orders and complete orders and compares each of these to the previous year on the graph.";
$reportdata["tableheadings"] = array("Month","New Signups","Orders Placed","Orders Completed");
$show = App::getFromRequest('show');
if (!$show) {
$show = 'signups';
}
for ($rawmonth = 1; $rawmonth <= 12; $rawmonth++) {
$dateRange = Carbon::create($year, $rawmonth, 1);
$dateRangeLastYear = Carbon::create(($year - 1), $rawmonth, 1);
$firstOfMonth = $dateRange->firstOfMonth()->toDateTimeString();
$firstOfMonth2 = $dateRangeLastYear->firstOfMonth()->toDateTimeString();
$lastOfMonth = $dateRange->endOfMonth()->toDateTimeString();
$lastOfMonth2 = $dateRangeLastYear->endOfMonth()->toDateTimeString();
$newsignups = Capsule::table('tblclients')
->whereBetween(
'datecreated',
[
$firstOfMonth,
$lastOfMonth
]
)->count();
$totalorders = Capsule::table('tblorders')
->whereBetween(
'date',
[
$firstOfMonth,
$lastOfMonth
]
)->count();
$completedorders = Capsule::table('tblorders')
->where('status', 'Active')
->whereBetween(
'date',
[
$firstOfMonth,
$lastOfMonth
]
)->count();
$newsignups2 = Capsule::table('tblclients')
->whereBetween(
'datecreated',
[
$firstOfMonth2,
$lastOfMonth2
]
)->count();
$totalorders2 = Capsule::table('tblorders')
->whereBetween(
'date',
[
$firstOfMonth2,
$lastOfMonth2
]
)->count();
$completedorders2 = Capsule::table('tblorders')
->where('status', 'Active')
->whereBetween(
'date',
[
$firstOfMonth2,
$lastOfMonth2
]
)->count();
$reportdata["tablevalues"][] = array(
$months[$rawmonth] . ' ' . $year,
$newsignups,
$totalorders,
$completedorders
);
switch ($show) {
case 'orders':
$chartdata['rows'][] = array(
'c' => array(
array('v' => $months[$rawmonth]),
array('v' => (int) $totalorders),
array('v' => (int) $totalorders2),
),
);
break;
case 'orderscompleted':
$chartdata['rows'][] = array(
'c' => array(
array('v' => $months[$rawmonth]),
array('v' => (int) $completedorders),
array('v' => (int) $completedorders2),
),
);
break;
case 'show':
default:
$chartdata['rows'][] = array(
'c'=>array(
array('v' => $months[$rawmonth]),
array('v' => (int) $newsignups),
array('v' => (int) $newsignups2),
),
);
}
}
$chartdata['cols'][] = array('label' => 'Month','type' => 'string');
$chartdata['cols'][] = array('label' => $year,'type' => 'number');
$chartdata['cols'][] = array('label' => ($year - 1),'type' => 'number');
$args = array();
if (!$show || $show=="signups") {
$args['title'] = 'New Signups';
$args['colors'] = '#3366CC,#888888';
}
if ($show=="orders") {
$args['title'] = 'Orders Placed';
$args['colors'] = '#DC3912,#888888';
}
if ($show=="orderscompleted") {
$args['title'] = 'Orders Completed';
$args['colors'] = '#FF9900,#888888';
}
$args['legendpos'] = 'right';
$reportdata["headertext"] = $chart->drawChart('Area', $chartdata, $args, '400px').
'<p align="center">'.
'<a href="reports.php' . $requeststr . '&show=signups">New Signups</a>'
. ' | <a href="reports.php' . $requeststr . '&show=orders">Orders Placed</a>'
. ' | <a href="reports.php' . $requeststr . '&show=orderscompleted">Orders Completed</a>'
. '</p>';
$reportdata["yearspagination"] = true;
client_statement.php 0000644 00000015610 14736262117 0010633 0 ustar 00 <?php
use WHMCS\Carbon;
use WHMCS\Database\Capsule;
if (!defined("WHMCS")) {
die("This file cannot be accessed directly");
}
$reportdata["title"] = "Client Account Register Balance";
$reportdata["description"] = "This report provides a statement of account for individual client accounts.";
$userid = '';
if (App::isInRequest('userid') && App::getFromRequest('userid')) {
$userid = App::getFromRequest('userid');
}
$range = App::getFromRequest('range');
$reportdata['headertext'] = '';
if (!$print) {
$reportdata["headertext"] = <<<HTML
<form method="post" action="reports.php?report={$report}">
<div class="report-filters-wrapper">
<div class="inner-container">
<h3>Filters</h3>
<div class="row">
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterClient">{$aInt->lang('fields', 'client')}</label>
{$aInt->clientsDropDown($userid)}
</div>
</div>
<div class="col-md-3 col-sm-6">
<div class="form-group">
<label for="inputFilterDate">{$dateRangeText}</label>
<div class="form-group date-picker-prepend-icon">
<label for="inputFilterDate" class="field-icon">
<i class="fal fa-calendar-alt"></i>
</label>
<input id="inputFilterDate"
type="text"
name="range"
value="{$range}"
class="form-control date-picker-search"
placeholder="{$optionalText}"
/>
</div>
</div>
</div>
</div>
<button type="submit" class="btn btn-primary">
{$aInt->lang('global', 'apply')}
</button>
</div>
</div>
</form>
HTML;
}
$currency = getCurrency($userid);
$statement = array();
$count = $balance = $totalcredits = $totaldebits = 0;
if ($userid) {
$results = Capsule::table('tblinvoices')
->where('userid', '=', $userid)
->whereIn(
'status',
[
'Unpaid',
'Paid',
'Collections',
]
)
->orderBy('date', 'asc')
->get()
->all();
foreach ($results as $result) {
$invoiceid = $result->id;
$date = $result->date;
$total = ($result->credit + $result->total);
$addfunds = Capsule::table('tblinvoiceitems')
->where('invoiceid', '=', $invoiceid)
->whereIn(
'type',
[
'AddFunds',
'Invoice',
]
)
->value('id');
if (!$addfunds) {
$statement[str_replace('-', '', $date) . "_" . $count] = [
'type' => 'Invoice',
'date' => Carbon::safeCreateFromMySqlDate($date),
'description' => "<a href=\"invoices.php?action=edit&id={$invoiceid}\" target=\"_blank\">#{$invoiceid}</a>",
'credits' => 0,
'debits' => $total,
];
}
$count++;
}
$results = Capsule::table('tblaccounts')
->where('userid', '=', $userid)
->orderBy('date', 'asc')
->get()
->all();
foreach ($results as $result) {
$transid = $result->id;
$date = $result->date;
$description = $result->description;
$amountin = $result->amountin;
$amountout = $result->amountout;
$invoiceid = $result->invoiceid;
$date = substr($date, 0, 10);
$itemtype = Capsule::table('tblinvoiceitems')
->where('invoiceid', '=', $invoiceid)
->value('type');
if ($itemtype == "AddFunds") {
$description = "Credit Prefunding";
} elseif ($itemtype == "Invoice") {
$description = "Mass Invoice Payment - ";
$relids = Capsule::table('tblinvoiceitems')
->where('invoiceid', '=', $invoiceid)
->orderBy('relid', 'asc')
->pluck('relid')
->all();
foreach ($relids as $relid) {
$description .= "<a href=\"invoices.php?action=edit&id={$relid}\" target=\"_blank\">#{$relid}</a>, ";
}
$description = substr($description, 0, -2);
} else {
if ($invoiceid) {
$description .= " - <a href=\"invoices.php?action=edit&id=$invoiceid\" target=\"_blank\">"
. "#$invoiceid</a>";
}
}
$statement[str_replace('-', '', $date) . "_" . $count] = [
'type' => 'Transaction',
'date' => Carbon::safeCreateFromMySqlDate($date),
'description' => $description,
'credits' => $amountin,
'debits' => $amountout,
];
$count++;
}
}
$datefrom = $dateto = '';
if ($range) {
$dateRange = Carbon::parseDateRangeValue($range);
$datefrom = $dateRange['from'];
$dateto = $dateRange['to'];
}
$reportdata['tableheadings'] = ['Type', 'Date', 'Description', 'Credits', 'Debits', 'Balance'];
ksort($statement);
$previousBalance = null;
foreach ($statement as $entry) {
/** @var Carbon $carbonDate */
$carbonDate = $entry['date'];
// only update the total balance to include previous balance through to ending date of report
if ($carbonDate->lte($dateto)) {
$balance += ($entry['credits'] - $entry['debits']);
}
if (!empty($range) && $carbonDate->lt($datefrom)) {
$previousBalance = $balance;
}
if (empty($range) || $carbonDate->betweenIncluded($datefrom, $dateto)) {
$reportdata['tablevalues'][] = [
$entry['type'],
$carbonDate->toClientDateFormat(),
$entry['description'],
formatCurrency($entry['credits']),
formatCurrency($entry['debits']),
formatCurrency($balance),
];
$totalcredits += $entry['credits'];
$totaldebits -= $entry['debits'];
}
}
if (!empty($previousBalance)) {
$previousBalance = formatCurrency($previousBalance);
} else {
$previousBalance = AdminLang::trans('global.na');
}
$previousBalanceLabel = AdminLang::trans('reports.clientStatement.previousBalance');
$reportdata['tablePreface'] = <<<PREFACE
<p id="clientSummaryPreviousBalance" style="float:right">
<strong>{$previousBalanceLabel}:</strong> {$previousBalance}
</p>
PREFACE;
$reportdata["tablevalues"][] = array(
'#efefef',
'',
'',
'<b>Ending Balance</b>',
'<b>'.formatCurrency($totalcredits).'</b>',
'<b>'.formatCurrency($totaldebits).'</b>',
'<b>'.formatCurrency($balance).'</b>'
);