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"] = "Services";
 11
 12$filterfields = [
 13    'id' => 'ID',
 14    'userid' => 'User ID',
 15    'clientname' => 'Client Name',
 16    'orderid' => 'Order ID',
 17    'packageid' => 'Product ID',
 18    'server' => 'Server ID',
 19    'regdate' => 'Registration Date',
 20    'domain' => 'Domain Name',
 21    'dedicatedip' => 'Dedicated IP',
 22    'assignedips' => 'Assigned IPs',
 23    'firstpaymentamount' => 'First Payment Amount',
 24    'amount' => 'Recurring Amount',
 25    'billingcycle' => 'Billing Cycle',
 26    'nextduedate' => 'Next Due Date',
 27    'paymentmethod' => 'Payment Method',
 28    'termination_date' => 'Termination Date',
 29    'completed_date' => 'Completed Date',
 30    'domainstatus' => 'Status',
 31    'username' => 'Username',
 32    'password' => 'Password',
 33    'notes' => 'Notes',
 34    'subscriptionid' => 'Subscription ID',
 35    'suspendreason' => 'Suspend Reason'
 36];
 37
 38$dateRangeFields = [
 39    'regdate' => 'Registration Date',
 40    'nextduedate' => 'Next Due Date',
 41    'termination_date' => 'Termination Date',
 42    'completed_date' => 'Completed Date',
 43];
 44
 45$removedDateRangeFields = array_diff($filterfields, $dateRangeFields);
 46
 47$reportdata["description"] = $reportdata["headertext"] = '';
 48
 49$incfields = $whmcs->get_req_var('incfields');
 50$filterfield = $whmcs->get_req_var('filterfield');
 51$filtertype = $whmcs->get_req_var('filtertype');
 52$filterq = $whmcs->get_req_var('filterq');
 53
 54$regDateRange = App::getFromRequest('regDateRange');
 55$nextDueDateRange = App::getFromRequest('nextDueDateRange');
 56$termDateRange = App::getFromRequest('termDateRange');
 57$completedDateRange = App::getFromRequest('completedDateRange');
 58
 59if (!is_array($incfields)) {
 60    $incfields = [];
 61}
 62if (!is_array($filterfield)) {
 63    $filterfield = [];
 64}
 65if (!is_array($filtertype)) {
 66    $filtertype = [];
 67}
 68if (!is_array($filterq)) {
 69    $filterq = [];
 70}
 71
 72if (!$print) {
 73    $reportdata["description"] = "This report can be used to generate a custom export of"
 74        . " services by applying up to 5 filters. CSV Export is available via the Tools menu to the right.";
 75
 76    $reportdata["headertext"] = '<form method="post" action="reports.php?report=' . $report . '">
 77<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
 78<tr><td width="20%" class="fieldlabel">Fields to Include</td><td class="fieldarea"><table width="100%"><tr>';
 79    $i=0;
 80    foreach ($filterfields as $k => $v) {
 81        $reportdata["headertext"] .= '<td width="20%"><input type="checkbox" name="incfields[]" value="' . $k . '" id="fd' . $k . '"';
 82        if (in_array($k, $incfields)) {
 83            $reportdata["headertext"] .= ' checked';
 84        }
 85        $reportdata["headertext"] .= ' /> <label for="fd' . $k . '">' . $v . '</label></td>';
 86        $i++;
 87        if (($i%5)==0) {
 88            $reportdata["headertext"] .= '</tr><tr>';
 89        }
 90    }
 91    $reportdata["headertext"] .= '</tr></table></td></tr>';
 92
 93    for ($i = 1; $i <= 5; $i ++) {
 94        $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>';
 95        foreach ($removedDateRangeFields as $k => $v) {
 96            $reportdata["headertext"] .= '<option value="' . $k . '"';
 97            if (isset($filterfield[$i]) && $filterfield[$i] == $k) {
 98                $reportdata["headertext"] .= ' selected';
 99            }
100            $reportdata["headertext"] .= '>' . $v . '</option>';
101        }
102        $reportdata["headertext"] .= '</select> <select name="filtertype[' . $i . ']" class="form-control select-inline">'
103            . '<option value="=">Exact Match</option><option value="like"';
104        if (isset($filtertype[$i]) && $filtertype[$i] == "like") {
105            $reportdata["headertext"] .= ' selected';
106        }
107        $reportdata["headertext"] .= '>Containing</option></select>'
108            . ' <input type="text" name="filterq[' . $i . ']" class="form-control input-inline input-250" value="' . (isset($filterq[$i]) ? $filterq[$i] : '') . '" /></td></tr>';
109    }
110
111    $reportdata["headertext"] .= <<<HTML
112        <tr>
113            <td width="20%" class="fieldlabel">Registration Date Range</td>
114            <td class="fieldarea">
115                <div class="form-group date-picker-prepend-icon">
116                    <label for="inputFilterDate" class="field-icon">
117                        <i class="fal fa-calendar-alt"></i>
118                    </label>
119                    <input id="inputFilterDate"
120                           type="text"
121                           name="regDateRange"
122                           value="{$regDateRange}"
123                           class="form-control date-picker-search"
124                    />
125                </div>
126            </td>
127        </tr>
128        <tr>
129            <td width="20%" class="fieldlabel">Next Due Date Range</td>
130            <td class="fieldarea">
131                <div class="form-group date-picker-prepend-icon">
132                    <label for="inputFilterDate" class="field-icon">
133                        <i class="fal fa-calendar-alt"></i>
134                    </label>
135                    <input id="inputFilterDate"
136                           type="text"
137                           name="nextDueDateRange"
138                           value="{$nextDueDateRange}"
139                           class="form-control date-picker-search"
140                    />
141                </div>
142            </td>
143        </tr>
144        <tr>
145            <td width="20%" class="fieldlabel">Termination Date Range</td>
146            <td class="fieldarea">
147                <div class="form-group date-picker-prepend-icon">
148                    <label for="inputFilterDate" class="field-icon">
149                        <i class="fal fa-calendar-alt"></i>
150                    </label>
151                    <input id="inputFilterDate"
152                           type="text"
153                           name="termDateRange"
154                           value="{$termDateRange}"
155                           class="form-control date-picker-search"
156                    />
157                </div>
158            </td>
159        </tr>
160        <tr>
161            <td width="20%" class="fieldlabel">Completed Date Range</td>
162            <td class="fieldarea">
163                <div class="form-group date-picker-prepend-icon">
164                    <label for="inputFilterDate" class="field-icon">
165                        <i class="fal fa-calendar-alt"></i>
166                    </label>
167                    <input id="inputFilterDate"
168                           type="text"
169                           name="completedDateRange"
170                           value="{$completedDateRange}"
171                           class="form-control date-picker-search"
172                    />
173                </div>
174            </td>
175        </tr>
176    </table>
177    <p align="center"><input type="submit" value="Filter" class="btn btn-primary"/></p>
178</form>
179HTML;
180}
181
182if (count($incfields)) {
183    $query = Capsule::table('tblhosting');
184
185    foreach ($filterfield as $i => $val) {
186        if ($val && array_key_exists($val, $filterfields)) {
187            if ($filtertype[$i] == 'like') {
188                $filterq[$i] = "%{$filterq[$i]}%";
189            }
190            if ($val == 'clientname') {
191                $query->whereRaw(
192                    "concat(tblclients.firstname, ' ', tblclients.lastname) "
193                    . "{$filtertype[$i]} '{$filterq[$i]}'"
194                );
195            } else {
196                $query->where(
197                    "tblhosting.{$filterfield[$i]}",
198                    $filtertype[$i],
199                    $filterq[$i]
200                );
201            }
202        }
203    }
204
205    foreach ($incfields as $fieldname) {
206        if (array_key_exists($fieldname, $filterfields)) {
207            $reportdata["tableheadings"][] = $filterfields[$fieldname];
208            if ($fieldname == "clientname") {
209                $query->addSelect(Capsule::raw("concat(tblclients.firstname, ' ', tblclients.lastname)"));
210            } else {
211                $query->addSelect("tblhosting.{$fieldname}");
212            }
213        }
214    }
215
216    if ($regDateRange) {
217        $dateRange = Carbon::parseDateRangeValue($regDateRange);
218        $fromdate = $dateRange['from']->toDateTimeString();
219        $todate = $dateRange['to']->toDateTimeString();
220        $query->whereBetween('regdate', [$fromdate, $todate]);
221    }
222
223    if ($nextDueDateRange) {
224        $dateRange = Carbon::parseDateRangeValue($nextDueDateRange);
225        $fromdate = $dateRange['from']->toDateTimeString();
226        $todate = $dateRange['to']->toDateTimeString();
227        $query->whereBetween('nextduedate', [$fromdate, $todate]);
228    }
229
230    if ($termDateRange) {
231        $dateRange = Carbon::parseDateRangeValue($termDateRange);
232        $fromdate = $dateRange['from']->toDateTimeString();
233        $todate = $dateRange['to']->toDateTimeString();
234        $query->whereBetween('termination_date', [$fromdate, $todate]);
235    }
236
237    if ($completedDateRange) {
238        $dateRange = Carbon::parseDateRangeValue($completedDateRange);
239        $fromdate = $dateRange['from']->toDateTimeString();
240        $todate = $dateRange['to']->toDateTimeString();
241        $query->whereBetween('completed_date', [$fromdate, $todate]);
242    }
243
244    $results = $query
245        ->join('tblclients', 'tblclients.id', '=', 'tblhosting.userid')
246        ->get()
247        ->all();
248
249    foreach ($results as $result) {
250        $result = (array) $result;
251        if (isset($result['paymentmethod'])) {
252            $result['paymentmethod'] = $gateways->getDisplayName($result['paymentmethod']);
253        }
254        if (isset($result['password'])) {
255            $result['password'] = decrypt($result['password']);
256        }
257        $reportdata["tablevalues"][] = $result;
258    }
259}
260