I was able to modify the Sales Tax Liability report to show company name next to client name and invoice number.
But from our accounting they are requesting us to make a report which will also show client VAT # (a client custom filed) which where we put the company VAT number.
The custom field name/id is: customfield14
Also is it possible to show Tax rate , and how ?
This is the report current code:
<?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', 'invoicenum'),
$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', 'tblclients.companyname')
->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} - {$result->companyname}";
$invoicenum = "{$result->invoicenum}";
$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}",
"{$invoicenum}",
"{$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.";
You can easily get custom field value from tblcustomfieldsvalues
using a left join
with tblclients
. It has a fieldid
and relid
columns where relid
is the client's id.
->leftJoin('tblcustomfieldsvalues', function ($join) {
$join->on('tblcustomfieldsvalues.relid', '=', 'tblclients.id')->where('tblcustomfieldsvalues.fieldid', 14);
})
You can also get tax rate from tbltax
using left join
with tblclients
. Both tables have country
column which can be used to get their tax rate
.
->leftJoin('tbltax', 'tblclients.country', '=', 'tbltax.country')
So, your code will look like this after these changes:
$results = Capsule::table('tblinvoices')
->select('tblinvoices.*', 'tblclients.firstname', 'tblclients.lastname', 'tblclients.companyname', Capsule::raw("tblcustomfieldsvalues.value AS vatnumber"), Capsule::raw("tbltax.taxrate AS clienttax"))
->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"'));
});
})
->leftJoin('tblcustomfieldsvalues', function ($join) {
$join->on('tblcustomfieldsvalues.relid', '=', 'tblclients.id')->where('tblcustomfieldsvalues.fieldid', 14);
})
->leftJoin('tbltax', 'tblclients.country', '=', 'tbltax.country')
->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} - {$result->companyname}";
$vatnum = $result->vatnumber;
$invoicenum = "{$result->invoicenum}";
$date = fromMySQLDate($result->date);
$datepaid = fromMySQLDate($result->datepaid);
$currency = getCurrency($userid);
$subtotal = $result->subtotal;
$credit = $result->credit;
$tax = ($result->tax + $result->tax2);
$taxrate = $result->clienttax;
$total = ($result->total + $credit);
$reportdata["tablevalues"][] = [
"{$id}",
"{$client}",
"{$vatnum}",
"{$invoicenum}",
"{$date}",
"{$datepaid}",
format_as_currency($subtotal),
format_as_currency($tax),
"{$taxrate}",
format_as_currency($credit),
format_as_currency($total),
];
}