1<?php
2
3use WHMCS\Billing\Currency;
4use WHMCS\Carbon;
5use WHMCS\Database\Capsule;
6
7if (!defined("WHMCS")) {
8 die("This file cannot be accessed directly");
9}
10
11$reportdata["title"] = "Transactions";
12
13$filterfields = [
14 'id' => 'ID',
15 'userid' => 'User ID',
16 'clientname' => 'Client Name',
17 'currency' => 'Currency',
18 'gateway' => 'Payment Method',
19 'date' => 'Date',
20 'description' => 'Description',
21 'invoiceid' => 'Invoice ID',
22 'transid' => 'Transaction ID',
23 'amountin' => 'Amount In',
24 'fees' => 'Fees',
25 'amountout' => 'Amount Out',
26 'rate' => 'Exchange Rate',
27 'refundid' => 'Refund ID'
28];
29
30$dateRangeFields = [
31 'date' => 'Date',
32];
33
34$removedDateRangeFields = array_diff($filterfields, $dateRangeFields);
35
36$reportdata["description"] = $reportdata["headertext"] = '';
37
38$incfields = $whmcs->get_req_var('incfields');
39$filterfield = $whmcs->get_req_var('filterfield');
40$filtertype = $whmcs->get_req_var('filtertype');
41$filterq = $whmcs->get_req_var('filterq');
42
43$range = App::getFromRequest('range');
44
45if (!is_array($incfields)) {
46 $incfields = [];
47}
48if (!is_array($filterfield)) {
49 $filterfield = [];
50}
51if (!is_array($filtertype)) {
52 $filtertype = [];
53}
54if (!is_array($filterq)) {
55 $filterq = [];
56}
57
58if (!$print) {
59 $reportdata["description"] = "This report can be used to generate a custom export of"
60 . " transactions by applying up to 5 filters. CSV Export is available via the"
61 . " Tools menu to the right.";
62
63 $reportdata["headertext"] = '<form method="post" action="reports.php?report=' . $report . '">
64<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
65<tr><td width="20%" class="fieldlabel">Fields to Include</td><td class="fieldarea"><table width="100%"><tr>';
66 $i=0;
67 foreach ($filterfields as $k => $v) {
68 $reportdata["headertext"] .= '<td width="20%"><input type="checkbox" name="incfields[]" value="' . $k . '" id="fd' . $k . '"';
69 if (in_array($k, $incfields)) {
70 $reportdata["headertext"] .= ' checked';
71 }
72 $reportdata["headertext"] .= ' /> <label for="fd' . $k . '">'.$v.'</label></td>';
73 $i++;
74 if (($i%5)==0) {
75 $reportdata["headertext"] .= '</tr><tr>';
76 }
77 }
78 $reportdata["headertext"] .= '</tr></table></td></tr>';
79
80 for ($i = 1; $i <= 5; $i ++) {
81 $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>';
82 foreach ($removedDateRangeFields as $k => $v) {
83 $reportdata["headertext"] .= '<option value="' . $k . '"';
84 if (isset($filterfield[$i]) && $filterfield[$i]==$k) {
85 $reportdata["headertext"] .= ' selected';
86 }
87 $reportdata["headertext"] .= '>' . $v . '</option>';
88 }
89 $reportdata["headertext"] .= '</select> <select name="filtertype[' . $i . ']" class="form-control select-inline">'
90 . '<option value="=">Exact Match</option><option value="like"';
91 if (isset($filtertype[$i]) && $filtertype[$i]=="like") {
92 $reportdata["headertext"] .= ' selected';
93 }
94 $reportdata["headertext"] .= '>Containing</option></select>'
95 . ' <input type="text" name="filterq[' . $i . ']" class="form-control select-inline input-250" value="' . (isset($filterq[$i]) ? $filterq[$i] : '') . '" /></td></tr>';
96 }
97
98 $reportdata["headertext"] .= <<<HTML
99 <tr>
100 <td width="20%" class="fieldlabel">Date Range</td>
101 <td class="fieldarea">
102 <div class="form-group date-picker-prepend-icon">
103 <label for="inputFilterDate" class="field-icon">
104 <i class="fal fa-calendar-alt"></i>
105 </label>
106 <input id="inputFilterDate"
107 type="text"
108 name="range"
109 value="{$range}"
110 class="form-control date-picker-search"
111 />
112 </div>
113 </td>
114 </tr>
115 </table>
116 <p align="center"><input type="submit" value="Filter" class="btn btn-primary"/></p>
117</form>
118HTML;
119}
120
121if (count($incfields)) {
122 $query = Capsule::table('tblaccounts');
123
124 foreach ($filterfield as $i => $val) {
125 if ($val && array_key_exists($val, $filterfields)) {
126 if ($filtertype[$i] == 'like') {
127 $filterq[$i] = "%{$filterq[$i]}%";
128 }
129 if ($val == 'clientname') {
130 $query->whereRaw(
131 "concat(tblclients.firstname, ' ', tblclients.lastname) "
132 . "{$filtertype[$i]} '{$filterq[$i]}'"
133 );
134 } elseif ($val == 'currency') {
135 $currencyCode = strtoupper(trim($filterq[$i]));
136 $currencyId = Currency::where('code', $currencyCode)
137 ->value('id');
138 $query->where(function ($query) use ($currencyId) {
139 return $query->where('tblclients.currency', (int) $currencyId)
140 ->orWhere(function ($query) use ($currencyId) {
141 return $query->where('tblaccounts.userid', 0)
142 ->where('tblaccounts.currency', (int) $currencyId);
143 });
144 });
145 } else {
146 $query->where(
147 "tblaccounts.{$filterfield[$i]}",
148 $filtertype[$i],
149 $filterq[$i]
150 );
151 }
152 }
153 }
154
155 foreach ($incfields as $fieldname) {
156 if (array_key_exists($fieldname, $filterfields)) {
157 $reportdata["tableheadings"][] = $filterfields[$fieldname];
158 if ($fieldname == "clientname") {
159 $query->addSelect(Capsule::raw("concat(tblclients.firstname, ' ', tblclients.lastname)"));
160 } else {
161 $query->addSelect("tblaccounts.{$fieldname}");
162 }
163 }
164 }
165
166 if (in_array('currency', $incfields) && !in_array('userid', $incfields)) {
167 $query->addSelect('tblaccounts.userid');
168 }
169
170 if ($range) {
171 $dateRange = Carbon::parseDateRangeValue($range);
172 $fromdate = $dateRange['from']->toDateTimeString();
173 $todate = $dateRange['to']->toDateTimeString();
174 $query->whereBetween('date', [$fromdate, $todate]);
175 }
176
177 $results = $query->leftJoin('tblclients', 'tblclients.id', '=', 'tblaccounts.userid')
178 ->orderBy('date', 'asc')
179 ->get()
180 ->all();
181 foreach ($results as $result) {
182 $result = (array) $result;
183 if (isset($result['currency'])) {
184 $currency = getCurrency($result['userid'], $result['currency']);
185 $result['currency'] = $currency['code'];
186
187 if (!in_array('userid', $incfields)) {
188 unset($result['userid']);
189 }
190 }
191 if (isset($result['gateway'])) {
192 $result['gateway'] = $gateways->getDisplayName($result['gateway']);
193 }
194 $reportdata["tablevalues"][] = $result;
195 }
196}
197