1<?php
2
3use WHMCS\Carbon;
4use WHMCS\Database\Capsule;
5
6if (!defined("WHMCS")) {
7 die("This file cannot be accessed directly");
8}
9
10$reportdata["title"] = "Clients";
11
12$filterfields = [
13 'id' => 'ID',
14 'firstname' => 'First Name',
15 'lastname' => 'Last Name',
16 'companyname' => 'Company Name',
17 'email' => 'Email',
18 'address1' => 'Address 1',
19 'address2' => 'Address 2',
20 'city' => 'City',
21 'state' => 'State',
22 'postcode' => 'Postcode',
23 'country' => 'Country',
24 'phonenumber' => 'Phone Number',
25 'currency' => 'Currency',
26 'groupid' => 'Client Group ID',
27 'credit' => 'Credit',
28 'datecreated' => 'Creation Date',
29 'notes' => 'Notes',
30 'status' => 'Status'
31];
32
33$dateRangeFields = [
34 'datecreated' => 'Creation Date',
35];
36
37$removedDateRangeFields = array_diff($filterfields, $dateRangeFields);
38
39$reportdata["description"] = $reportdata["headertext"] = '';
40
41$incfields = $whmcs->get_req_var('incfields');
42$filterfield = $whmcs->get_req_var('filterfield');
43$filtertype = $whmcs->get_req_var('filtertype');
44$filterq = $whmcs->get_req_var('filterq');
45
46$createDateRange = App::getFromRequest('createDateRange');
47
48if (!is_array($incfields)) {
49 $incfields = [];
50}
51if (!is_array($filterfield)) {
52 $filterfield = [];
53}
54if (!is_array($filtertype)) {
55 $filtertype = [];
56}
57if (!is_array($filterq)) {
58 $filterq = [];
59}
60
61if (!$print) {
62 $reportdata["description"] = "This report can be used to generate a custom export"
63 . " of clients by applying up to 5 filters. CSV Export is available via the Tools menu to the right.";
64
65 $reportdata["headertext"] = '<form method="post" action="reports.php?report=' . $report . '">
66<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
67<tr><td width="20%" class="fieldlabel">Fields to Include</td><td class="fieldarea"><table width="100%"><tr>';
68 $i=0;
69 foreach ($filterfields as $k => $v) {
70 $reportdata["headertext"] .= '<td width="20%"><input type="checkbox" name="incfields[]" value="' . $k . '" id="fd' . $k . '"';
71 if (in_array($k, $incfields)) {
72 $reportdata["headertext"] .= ' checked';
73 }
74 $reportdata["headertext"] .= ' /> <label for="fd' . $k . '">' . $v . '</label></td>';
75 $i++;
76 if (($i%5)==0) {
77 $reportdata["headertext"] .= '</tr><tr>';
78 }
79 }
80 $reportdata["headertext"] .= '</tr></table></td></tr>';
81
82 for ($i = 1; $i <= 5; $i ++) {
83 $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>';
84 foreach ($removedDateRangeFields as $k => $v) {
85 $reportdata["headertext"] .= '<option value="'.$k.'"';
86 if (isset($filterfield[$i]) && $filterfield[$i]==$k) {
87 $reportdata["headertext"] .= ' selected';
88 }
89 $reportdata["headertext"] .= '>'.$v.'</option>';
90 }
91 $reportdata["headertext"] .= '</select> <select name="filtertype[' . $i . ']" class="form-control select-inline">';
92 $reportdata["headertext"] .= '<option value="=">Exact Match</option><option value="like"';
93 if (isset($filtertype[$i]) && $filtertype[$i] == "like") {
94 $reportdata["headertext"] .= ' selected';
95 }
96 $reportdata["headertext"] .= '>Containing</option></select>'
97 . ' <input type="text" name="filterq[' . $i . ']" class="form-control input-inline input-250" value="' . (isset($filterq[$i]) ? $filterq[$i] : '') . '" /></td></tr>';
98 }
99
100 $reportdata["headertext"] .= <<<HTML
101 <tr>
102 <td width="20%" class="fieldlabel">Creation Date Range</td>
103 <td class="fieldarea">
104 <div class="form-group date-picker-prepend-icon">
105 <label for="inputFilterDate" class="field-icon">
106 <i class="fal fa-calendar-alt"></i>
107 </label>
108 <input id="inputFilterDate"
109 type="text"
110 name="createDateRange"
111 value="{$createDateRange}"
112 class="form-control date-picker-search"
113 />
114 </div>
115 </td>
116 </tr>
117 </table>
118 <p align="center"><input type="submit" value="Filter" class="btn btn-primary"/></p>
119</form>
120HTML;
121}
122
123if (count($incfields)) {
124 $filters = [];
125 foreach ($filterfield as $i => $val) {
126 if ($val && array_key_exists($val, $filterfields)) {
127 if ($filtertype[$i] === 'like') {
128 $filterq[$i] = '%' . $filterq[$i] . '%';
129 }
130 $filters[] = [
131 "name" => $filterfield[$i],
132 "operator" => $filtertype[$i],
133 "value" => $filterq[$i],
134 ];
135 }
136 }
137
138 $fieldlist = [];
139 foreach ($incfields as $fieldname) {
140 if (array_key_exists($fieldname, $filterfields)) {
141 $reportdata["tableheadings"][] = $filterfields[$fieldname];
142 $fieldlist[] = $fieldname;
143 }
144 }
145
146 $query = Capsule::table('tblclients')
147 ->addSelect($fieldlist)
148 ->where($filters);
149
150 if ($createDateRange) {
151 $dateRange = Carbon::parseDateRangeValue($createDateRange);
152 $fromdate = $dateRange['from']->toDateTimeString();
153 $todate = $dateRange['to']->toDateTimeString();
154 $query->whereBetween('datecreated', [$fromdate, $todate]);
155 }
156
157 $results = $query->get()->all();
158
159 foreach ($results as $result) {
160 $result = (array) $result;
161 if (isset($result['currency'])) {
162 $result['currency'] = Capsule::table('tblcurrencies')
163 ->where('id', '=', $result['currency'])
164 ->value('code');
165 }
166 $reportdata["tablevalues"][] = $result;
167 }
168}
169