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