Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
0.00% |
0 / 100 |
|
0.00% |
0 / 8 |
CRAP | |
0.00% |
0 / 1 |
| AutomatedQuotationsController | |
0.00% |
0 / 100 |
|
0.00% |
0 / 8 |
2256 | |
0.00% |
0 / 1 |
| list | |
0.00% |
0 / 22 |
|
0.00% |
0 / 1 |
6 | |||
| applySort | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
42 | |||
| applyFilterModel | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
90 | |||
| applyTextFilter | |
0.00% |
0 / 26 |
|
0.00% |
0 / 1 |
110 | |||
| applyNumberFilter | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
12 | |||
| applyDateFilter | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
12 | |||
| applySetFilter | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
30 | |||
| parseNumber | |
0.00% |
0 / 13 |
|
0.00% |
0 / 1 |
90 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace App\Http\Controllers; |
| 4 | |
| 5 | use Illuminate\Database\Query\Builder; |
| 6 | use Illuminate\Http\JsonResponse; |
| 7 | use Illuminate\Http\Request; |
| 8 | use Illuminate\Support\Facades\DB; |
| 9 | |
| 10 | /** |
| 11 | * Admin-only view of budgets/quotations created automatically by the |
| 12 | * fluencylabs integration (identified by tbl_quotations.user_create_by_g3w = |
| 13 | * 'flu'). Feeds the "Automated Quotations" tab. |
| 14 | * |
| 15 | * Advanced search is SERVER-SIDE: the front-end <app-filter-bar> posts its |
| 16 | * `filterModel` and it is applied to the SQL query here (mirroring the |
| 17 | * per-column filtering the Orders/Leaves tables do). Gated by |
| 18 | * `auth.token + require.admin` in routes/api.php (role_id = 1). |
| 19 | */ |
| 20 | class AutomatedQuotationsController extends Controller |
| 21 | { |
| 22 | /** |
| 23 | * The G3W user code fluencylabs writes as the creator of the automated |
| 24 | * quotations. Single source for the base WHERE clause. |
| 25 | */ |
| 26 | private const FLUENCYLABS_G3W_USER = 'flu'; |
| 27 | |
| 28 | /** |
| 29 | * Maps a filter-bar field name to its qualified SQL column. Also acts as |
| 30 | * the allow-list — any field not present here is ignored (no injection of |
| 31 | * arbitrary column names). |
| 32 | */ |
| 33 | private const FIELD_COLUMNS = [ |
| 34 | 'id' => 'tq.id', |
| 35 | 'quote_id' => 'tq.quote_id', |
| 36 | 'internal_quote_id' => 'tq.internal_quote_id', |
| 37 | 'company_name' => 'tc.name', |
| 38 | 'amount' => 'tq.amount', |
| 39 | 'status_name' => 'tbs.name', |
| 40 | 'created_at' => 'tq.created_at', |
| 41 | 'issue_date' => 'tq.issue_date', |
| 42 | 'sent_date' => 'tq.sent_date', |
| 43 | 'acceptance_date' => 'tq.acceptance_date', |
| 44 | ]; |
| 45 | |
| 46 | public function list(Request $request): JsonResponse |
| 47 | { |
| 48 | $filterModel = $request->input('filterModel', []); |
| 49 | if (! is_array($filterModel)) { |
| 50 | $filterModel = []; |
| 51 | } |
| 52 | |
| 53 | $query = DB::table('tbl_quotations as tq') |
| 54 | ->join('tbl_budget_status as tbs', 'tbs.budget_status_id', '=', 'tq.budget_status_id') |
| 55 | ->leftJoin('tbl_companies as tc', 'tc.company_id', '=', 'tq.company_id') |
| 56 | ->where('tq.user_create_by_g3w', self::FLUENCYLABS_G3W_USER) |
| 57 | ->select( |
| 58 | 'tq.id as id', |
| 59 | 'tq.quote_id as quote_id', |
| 60 | 'tq.internal_quote_id as internal_quote_id', |
| 61 | 'tc.name as company_name', |
| 62 | 'tq.amount as amount', |
| 63 | 'tbs.name as status_name', |
| 64 | 'tq.created_at as created_at', |
| 65 | 'tq.issue_date as issue_date', |
| 66 | 'tq.sent_date as sent_date', |
| 67 | 'tq.acceptance_date as acceptance_date', |
| 68 | ); |
| 69 | |
| 70 | $this->applyFilterModel($query, $filterModel); |
| 71 | $this->applySort($query, $request->input('sort')); |
| 72 | |
| 73 | return response()->json($query->get()); |
| 74 | } |
| 75 | |
| 76 | /** |
| 77 | * Apply the active sort server-side. `sort` is { field, dir } as emitted by |
| 78 | * the data-table; field is validated against the allow-list. Falls back to |
| 79 | * newest-first (tq.id desc) when no valid sort is given. |
| 80 | */ |
| 81 | private function applySort(Builder $query, $sort): void |
| 82 | { |
| 83 | $field = is_array($sort) ? ($sort['field'] ?? null) : null; |
| 84 | $dir = is_array($sort) ? strtolower((string) ($sort['dir'] ?? '')) : ''; |
| 85 | |
| 86 | if ($field !== null && isset(self::FIELD_COLUMNS[$field]) && in_array($dir, ['asc', 'desc'], true)) { |
| 87 | $query->orderBy(self::FIELD_COLUMNS[$field], $dir); |
| 88 | } else { |
| 89 | $query->orderByDesc('tq.id'); |
| 90 | } |
| 91 | } |
| 92 | |
| 93 | /** |
| 94 | * Apply the front-end filter model (one entry per active field) to the |
| 95 | * query. Entry shapes match what <app-filter-bar> emits: |
| 96 | * text → { type:'text', operator, value } |
| 97 | * number → { type:'number', min, max } |
| 98 | * date → { type:'date', from, to } (from/to are 'YYYY-MM-DD') |
| 99 | * set → { type:'set', values: [] } ('__BLANK__' = empty value) |
| 100 | */ |
| 101 | private function applyFilterModel(Builder $query, array $filterModel): void |
| 102 | { |
| 103 | foreach ($filterModel as $field => $entry) { |
| 104 | if (! isset(self::FIELD_COLUMNS[$field]) || ! is_array($entry)) { |
| 105 | continue; |
| 106 | } |
| 107 | $col = self::FIELD_COLUMNS[$field]; |
| 108 | |
| 109 | match ($entry['type'] ?? null) { |
| 110 | 'text' => $this->applyTextFilter($query, $col, $entry), |
| 111 | 'number' => $this->applyNumberFilter($query, $col, $entry), |
| 112 | 'date' => $this->applyDateFilter($query, $col, $entry), |
| 113 | 'set' => $this->applySetFilter($query, $col, $entry), |
| 114 | default => null, |
| 115 | }; |
| 116 | } |
| 117 | } |
| 118 | |
| 119 | private function applyTextFilter(Builder $query, string $col, array $e): void |
| 120 | { |
| 121 | $op = $e['operator'] ?? 'contains'; |
| 122 | $val = (string) ($e['value'] ?? ''); |
| 123 | |
| 124 | switch ($op) { |
| 125 | case 'blank': |
| 126 | $query->where(fn (Builder $q) => $q->whereNull($col)->orWhere($col, '=', '')); |
| 127 | break; |
| 128 | case 'notBlank': |
| 129 | $query->whereNotNull($col)->where($col, '!=', ''); |
| 130 | break; |
| 131 | case 'equals': |
| 132 | $query->where($col, '=', $val); |
| 133 | break; |
| 134 | case 'notEqual': |
| 135 | $query->where($col, '!=', $val); |
| 136 | break; |
| 137 | case 'startsWith': |
| 138 | $query->where($col, 'like', $val.'%'); |
| 139 | break; |
| 140 | case 'endsWith': |
| 141 | $query->where($col, 'like', '%'.$val); |
| 142 | break; |
| 143 | case 'notContains': |
| 144 | $query->where($col, 'not like', '%'.$val.'%'); |
| 145 | break; |
| 146 | case 'contains': |
| 147 | default: |
| 148 | $query->where($col, 'like', '%'.$val.'%'); |
| 149 | break; |
| 150 | } |
| 151 | } |
| 152 | |
| 153 | private function applyNumberFilter(Builder $query, string $col, array $e): void |
| 154 | { |
| 155 | $min = $this->parseNumber($e['min'] ?? ''); |
| 156 | $max = $this->parseNumber($e['max'] ?? ''); |
| 157 | |
| 158 | if ($min !== null) { |
| 159 | $query->where($col, '>=', $min); |
| 160 | } |
| 161 | if ($max !== null) { |
| 162 | $query->where($col, '<=', $max); |
| 163 | } |
| 164 | } |
| 165 | |
| 166 | private function applyDateFilter(Builder $query, string $col, array $e): void |
| 167 | { |
| 168 | $from = (string) ($e['from'] ?? ''); |
| 169 | $to = (string) ($e['to'] ?? ''); |
| 170 | |
| 171 | if ($from !== '') { |
| 172 | $query->whereDate($col, '>=', $from); |
| 173 | } |
| 174 | if ($to !== '') { |
| 175 | $query->whereDate($col, '<=', $to); |
| 176 | } |
| 177 | } |
| 178 | |
| 179 | private function applySetFilter(Builder $query, string $col, array $e): void |
| 180 | { |
| 181 | $values = $e['values'] ?? []; |
| 182 | if (! is_array($values) || count($values) === 0) { |
| 183 | return; |
| 184 | } |
| 185 | |
| 186 | $hasBlank = in_array('__BLANK__', $values, true); |
| 187 | $concrete = array_values(array_filter($values, fn ($v) => $v !== '__BLANK__')); |
| 188 | |
| 189 | $query->where(function (Builder $q) use ($col, $concrete, $hasBlank): void { |
| 190 | if (count($concrete) > 0) { |
| 191 | $q->whereIn($col, $concrete); |
| 192 | } |
| 193 | if ($hasBlank) { |
| 194 | $q->orWhereNull($col)->orWhere($col, '=', ''); |
| 195 | } |
| 196 | }); |
| 197 | } |
| 198 | |
| 199 | /** |
| 200 | * Parse a numeric string that may be es-formatted ("1.234,56") or plain |
| 201 | * ("1234.56"): the last-occurring separator is treated as the decimal. |
| 202 | * Returns null when unset/unparseable so the caller can skip the bound. |
| 203 | */ |
| 204 | private function parseNumber($v): ?float |
| 205 | { |
| 206 | if ($v === null || $v === '') { |
| 207 | return null; |
| 208 | } |
| 209 | if (is_numeric($v)) { |
| 210 | return (float) $v; |
| 211 | } |
| 212 | |
| 213 | $s = trim((string) $v); |
| 214 | if ($s === '') { |
| 215 | return null; |
| 216 | } |
| 217 | |
| 218 | $lastComma = strrpos($s, ','); |
| 219 | $lastDot = strrpos($s, '.'); |
| 220 | |
| 221 | if ($lastComma !== false && ($lastDot === false || $lastComma > $lastDot)) { |
| 222 | $s = str_replace(['.', ','], ['', '.'], $s); |
| 223 | } else { |
| 224 | $s = str_replace(',', '', $s); |
| 225 | } |
| 226 | |
| 227 | return is_numeric($s) ? (float) $s : null; |
| 228 | } |
| 229 | } |