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