Odyssey
ReportingTest.php
1 <?php
2 
3 use PHPUnit\Framework\TestCase;
4 
5 /**
6  * @var ReportingTest
7  * Contains all the test cases for the reporting.i include file.
8  */
9 class ReportingTest extends TestCase {
10 
11  /**
12  * @var $unprocessedDataArray
13  * This is a simulation of the data that gets entered into the sort and filter functions when the report is retrieved from a callback function versus from parsing a SQL.
14  * Actual data pulled from array results for the "E-Statement Processing History" report.
15  */
16  protected static $unprocessedDataArray;
17 
18  /**
19  * @var $cols
20  * This is the column definition from the "E-Statement Processing History" report.
21  */
22  protected static $cols;
23 
24  /**
25  * @var $reportList
26  * This is to test the callback functions themselves.
27  */
28  protected static $reportList;
29 
30  /**
31  * function setUp()
32  * This sets up all the test data needed for this report.
33  */
34  function setUp() {
35  $sharedLibrary = "/var/www/html/shared/library";
36  require_once("$sharedLibrary/hcuCommon.i");
37  require_once("$sharedLibrary/reporting.i");
38  require_once("$sharedLibrary/db.postgres.i");
39 
40  self::$unprocessedDataArray = array(
41  array("count" => 2142, "loadDate" => "2018-03-01 14:43:34", "period" => "Feb 2018", "pastCutoff" => "Y"),
42  array("count" => -1098, "loadDate" => "2018-02-01 15:01:33", "period" => "Jan 2018", "pastCutoff" => "Y"),
43  array("count" => 3240, "loadDate" => "2018-02-01 09:48:18", "period" => "Jan 2018", "pastCutoff" => "Y"),
44  array("count" => 2874, "loadDate" => "2018-01-02 16:12:00", "period" => "Quarter 4 2017", "pastCutoff" => "Y"),
45  array("count" => 2101, "loadDate" => "2017-12-01 15:02:34", "period" => "Nov 2017", "pastCutoff" => "Y"),
46  array("count" => 1585, "loadDate" => "2014-12-01 08:30:48", "period" => "Nov 2014", "pastCutoff" => "N"),
47  array("count" => 1624, "loadDate" => "2014-11-03 07:39:30", "period" => "Oct 2014", "pastCutoff" => "N"),
48  array("count" => 2269, "loadDate" => "2014-10-01 08:54:24", "period" => "Quarter 3 2014", "pastCutoff" => "N"),
49  array("count" => 1500, "loadDate" => "2014-09-02 08:37:38", "period" => "Aug 2014", "pastCutoff" => "N"),
50  array("count" => 101, "loadDate" => "2014-09-02 08:04:48", "period" => "Aug 2014", "pastCutoff" => "N"),
51  array("count" => 1597, "loadDate" => "2014-08-01 08:22:36", "period" => "Jul 2014", "pastCutoff" => "N")
52  );
53 
54  self::$cols = array("Count" => "number", "Load Date" => "date", "Period" => "string", "Past Cutoff" => "boolean");
55 
56  self::$reportList = array( // Not in list but useful for test cases for #744.
57  "test" => array("title" => "Test", "nonSqlDataFunc" => "GetTest", "cols" => array("a" => "string", "b" => "number", "c" => "date"))
58  );
59  }
60 
61  /**
62  * test_AddNonSqlFilter()
63  * This is an automated test to test filtering. The AddNonSqlFilter function gets called internally for any report using the callback function option: "nonSqlDataFunc."
64  * Normal input of this function is an associative array created from the callback function mapped to the "cols" array in the report definition.
65  */
66  function test_AddNonSqlFilter() {
67 
68  // Number tests
69  // -----------------------------
70  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "count", "operator" => "eq", "value" => 2142))));
71  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
72 
73  $this->assertEquals($results["status"], "000");
74  $this->assertEquals(count($results["data"]), 1);
75 
76  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "count", "operator" => "neq", "value" => 3240))));
77  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
78 
79  $this->assertEquals($results["status"], "000");
80  $this->assertEquals(count($results["data"]), 10);
81 
82  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "count", "operator" => "gt", "value" => 2269))));
83  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
84 
85  $this->assertEquals($results["status"], "000");
86  $this->assertEquals(count($results["data"]), 2); // 3240, 2874
87 
88  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "count", "operator" => "gte", "value" => 1500))));
89  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
90 
91  $this->assertEquals($results["status"], "000");
92  $this->assertEquals(count($results["data"]), 9);
93  // 2142, 3240, 2874, 2101, 1585, 1624, 2269, 1500, 1597
94 
95  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "count", "operator" => "lt", "value" => -1098))));
96  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
97 
98  $this->assertEquals($results["status"], "000");
99  $this->assertEquals(count($results["data"]), 0);
100 
101  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "count", "operator" => "lte", "value" => -1098))));
102  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
103 
104  $this->assertEquals($results["status"], "000");
105  $this->assertEquals(count($results["data"]), 1);
106 
107  // String tests
108  // -----------------------------
109  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "period", "operator" => "eq", "value" => "Nov 2017"))));
110  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
111 
112  $this->assertEquals($results["status"], "000");
113  $this->assertEquals(count($results["data"]), 1);
114 
115  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "period", "operator" => "neq", "value" => "Nov 2017"))));
116  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
117 
118  $this->assertEquals($results["status"], "000");
119  $this->assertEquals(count($results["data"]), 10);
120 
121  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "period", "operator" => "startswith", "value" => "Jan"))));
122  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
123 
124  $this->assertEquals($results["status"], "000");
125  $this->assertEquals(count($results["data"]), 2);
126 
127  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "period", "operator" => "endswith", "value" => "2014"))));
128  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
129 
130  $this->assertEquals($results["status"], "000");
131  $this->assertEquals(count($results["data"]), 6);
132 
133  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "period", "operator" => "contains", "value" => "u"))));
134  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
135 
136  $this->assertEquals($results["status"], "000");
137  $this->assertEquals(count($results["data"]), 5);
138 
139  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "period", "operator" => "doesnotcontain", "value" => "a"))));
140  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
141 
142  $this->assertEquals($results["status"], "000");
143  $this->assertEquals(count($results["data"]), 5);
144 
145  // Date tests
146  // --------------------------
147  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "loadDate", "operator" => "eq", "value" => "2018-03-01"))));
148  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
149 
150  $this->assertEquals($results["status"], "000");
151  $this->assertEquals(count($results["data"]), 1);
152 
153  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "loadDate", "operator" => "neq", "value" => "2018-03-01"))));
154  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
155 
156  $this->assertEquals($results["status"], "000");
157  $this->assertEquals(count($results["data"]), 10);
158 
159  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "loadDate", "operator" => "gt", "value" => "2014-12-01 08:30:48"))));
160  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
161 
162  $this->assertEquals($results["status"], "000");
163  $this->assertEquals(count($results["data"]), 5);
164 
165  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "loadDate", "operator" => "gte", "value" => "2014-12-01 08:30:48"))));
166  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
167 
168  $this->assertEquals($results["status"], "000");
169  $this->assertEquals(count($results["data"]), 6);
170 
171  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "loadDate", "operator" => "lt", "value" => "2014-10-01 08:54:24"))));
172  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
173 
174  $this->assertEquals($results["status"], "000");
175  $this->assertEquals(count($results["data"]), 3);
176 
177  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "loadDate", "operator" => "lte", "value" => "2014-10-01 08:54:24"))));
178  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
179 
180  $this->assertEquals($results["status"], "000");
181  $this->assertEquals(count($results["data"]), 4);
182 
183  // Boolean tests
184  // -------------------------
185  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "pastCutoff", "operator" => "eq", "value" => true))));
186  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
187 
188  $this->assertEquals($results["status"], "000");
189  $this->assertEquals(count($results["data"]), 5);
190 
191  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "pastCutoff", "operator" => "eq", "value" => false))));
192  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
193 
194  $this->assertEquals($results["status"], "000");
195  $this->assertEquals(count($results["data"]), 6);
196 
197  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "pastCutoff", "operator" => "isnull", "value" => null))));
198  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
199 
200  $this->assertEquals($results["status"], "000");
201  $this->assertEquals(count($results["data"]), 0);
202 
203  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "pastCutoff", "operator" => "isnotnull", "value" => null))));
204  $results = AddNonSqlFilter(self::$unprocessedDataArray, $filter, self::$cols);
205 
206  $this->assertEquals($results["status"], "000");
207  $this->assertEquals(count($results["data"]), 11);
208  }
209 
210  /**
211  * function test_AddNonSqlSort()
212  * This is an automated test to test sorting. The AddNonSqlSort function gets called internally for any report using the callback function option: "nonSqlDataFunc."
213  * Normal input of this function is an associative array created from the callback function mapped to the "cols" array in the report definition.
214  */
215  function test_AddNonSqlSort() {
216  // At some point, also test $groupBy, $groupDescription, $groupForceSortBy
217  $groupBy = "";
218  $groupDescription = "";
219  $groupForceSortBy = "";
220 
221  $sort = HCU_JsonEncode(array(array("field" => "count", "dir" => "asc")));
222  $results = AddNonSqlSort(self::$unprocessedDataArray, $sort, self::$cols, $groupBy, $groupDescription, $groupForceSortBy);
223 
224  $this->assertEquals($results["status"], "000");
225  $this->assertEquals($results["data"][0]["count"], -1098);
226 
227  $sort = HCU_JsonEncode(array(array("field" => "count", "dir" => "desc")));
228  $results = AddNonSqlSort(self::$unprocessedDataArray, $sort, self::$cols, $groupBy, $groupDescription, $groupForceSortBy);
229 
230  $this->assertEquals($results["status"], "000");
231  $this->assertEquals($results["data"][0]["count"], 3240);
232 
233  $sort = HCU_JsonEncode(array(array("field" => "loadDate", "dir" => "asc")));
234  $results = AddNonSqlSort(self::$unprocessedDataArray, $sort, self::$cols, $groupBy, $groupDescription, $groupForceSortBy);
235 
236  $this->assertEquals($results["status"], "000");
237  $this->assertEquals($results["data"][0]["loadDate"], "2014-08-01 08:22:36");
238 
239  $sort = HCU_JsonEncode(array(array("field" => "loadDate", "dir" => "desc")));
240  $results = AddNonSqlSort(self::$unprocessedDataArray, $sort, self::$cols, $groupBy, $groupDescription, $groupForceSortBy);
241 
242  $this->assertEquals($results["status"], "000");
243  $this->assertEquals($results["data"][0]["loadDate"], "2018-03-01 14:43:34");
244 
245  $sort = HCU_JsonEncode(array(array("field" => "period", "dir" => "asc")));
246  $results = AddNonSqlSort(self::$unprocessedDataArray, $sort, self::$cols, $groupBy, $groupDescription, $groupForceSortBy);
247 
248  $this->assertEquals($results["status"], "000");
249  $this->assertEquals($results["data"][0]["period"], "Aug 2014");
250 
251  $sort = HCU_JsonEncode(array(array("field" => "period", "dir" => "desc")));
252  $results = AddNonSqlSort(self::$unprocessedDataArray, $sort, self::$cols, $groupBy, $groupDescription, $groupForceSortBy);
253 
254  $this->assertEquals($results["status"], "000");
255  $this->assertEquals($results["data"][0]["period"], "Quarter 4 2017");
256 
257  $sort = HCU_JsonEncode(array(array("field" => "pastCutoff", "dir" => "asc")));
258  $results = AddNonSqlSort(self::$unprocessedDataArray, $sort, self::$cols, $groupBy, $groupDescription, $groupForceSortBy);
259 
260  $this->assertEquals($results["status"], "000");
261  $this->assertEquals($results["data"][0]["pastCutoff"], "N");
262 
263  $sort = HCU_JsonEncode(array(array("field" => "pastCutoff", "dir" => "desc")));
264  $results = AddNonSqlSort(self::$unprocessedDataArray, $sort, self::$cols, $groupBy, $groupDescription, $groupForceSortBy);
265 
266  $this->assertEquals($results["status"], "000");
267  $this->assertEquals($results["data"][0]["pastCutoff"], "Y");
268  }
269 
270  /**
271  * function test_nonSqlDataFuncAttribute()
272  * This function tests the callback functionality to get a report versus using the SQL method.
273  */
274  function test_nonSqlDataFuncAttribute() {
275  $reportListItem = array("title" => "Test", "nonSqlDataFunc" => "GetTestCallback1", "cols" => array("a" => "string", "b" => "number", "c" => "date"));
276  $report = "test";
277  $sort = "";
278  $filter = "";
279  $Cu = "";
280 
281  $results = ReadReportData($Cu, $reportListItem, null, -1, 0, true, false, true, false, $sort, $filter, $report, null);
282 
283  $this->assertEquals($results["status"], "000");
284  $this->assertEquals($results["reportData"]["total"], 26);
285  $this->assertEquals($results["reportData"]["data"][0]["a"], "abrir");
286  $this->assertEquals($results["reportData"]["data"][24]["a"], "yacer (lookup)");
287 
288  $reportListItem["nonSqlDataFunc"] = "GetTestCallback2";
289 
290  $results = ReadReportData($Cu, $reportListItem, null, -1, 0, true, false, true, false, $sort, $filter, $report, null);
291 
292  $this->assertNotEquals($results["status"], "000");
293  $this->assertEquals($results["error"][0], "This is a terrible test case.");
294 
295  $reportListItem["nonSqlDataFunc"] = "GetTestCallback3";
296 
297  $results = ReadReportData($Cu, $reportListItem, null, -1, 0, true, false, true, false, $sort, $filter, $report, null);
298 
299  $this->assertNotEquals($results["status"], "000");
300  $this->assertEquals($results["error"][0], "Data function is not formatted correctly.");
301  }
302 
303  /**
304  * function test_AddSort()
305  * This function tests the AddSort() function for applying sorts to the SQL. It also tests the new defaultSort option.
306  */
307  function test_AddSort() {
308  // At some point, also test $groupBy, $groupDescription, $groupForceSortBy
309  $sort = HCU_JsonEncode(array(array("field" => "date", "dir" => "desc")));
310  $cols = array("Account" => "number", "Date" => "date", "Old Stmt" => "string", "New Stmt" => "string", "Changed By" => "string");
311  $groupBy = "";
312  $groupDescription = "";
313  $groupForceSortBy = "";
314  $defaultSort = "";
315 
316  $results = AddSort($sort, $cols, $groupBy, $groupDescription, $groupForceSortBy, $defaultSort);
317 
318  $this->assertEquals($results["status"], "000");
319  $this->assertEquals($results["appendToSQL"], " order by (case when trim(coalesce(\"date\"::varchar, '')) = '' then null else \"date\" end)::timestamp desc");
320 
321 
322  $sort = HCU_JsonEncode(array(array("field" => "oldStmt", "dir" => "asc")));
323  $defaultSort = array(array("field" => "date", "dir" => "desc"));
324 
325  $results = AddSort($sort, $cols, $groupBy, $groupDescription, $groupForceSortBy, $defaultSort);
326 
327  $this->assertEquals($results["status"], "000");
328  $this->assertEquals($results["appendToSQL"], " order by lower(trim(coalesce(\"oldstmt\"::varchar, ''))) asc");
329 
330  $sort = "";
331 
332  $results = AddSort($sort, $cols, $groupBy, $groupDescription, $groupForceSortBy, $defaultSort);
333 
334  $this->assertEquals($results["status"], "000");
335  $this->assertEquals($results["appendToSQL"], " order by (case when trim(coalesce(\"date\"::varchar, '')) = '' then null else \"date\" end)::timestamp desc");
336 
337  $sort = HCU_JsonEncode(array(array("field" => "account", "dir" => "desc")));
338 
339  $results = AddSort($sort, $cols, $groupBy, $groupDescription, $groupForceSortBy, $defaultSort);
340 
341  $this->assertEquals($results["status"], "000");
342  $this->assertEquals($results["appendToSQL"], " order by \"account\"::decimal desc");
343  }
344 
345  /**
346  * function test_AddFilter()
347  * This function tests the AddFilter() function for applying filters to the SQL.
348  */
349  function test_AddFilter() {
350 
351  $cols = array("User Name" => "number", "Date" => "date", "Old Email" => "string", "Boolean" => "boolean");
352 
353  // Number tests
354  // -----------------------------
355  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "userName", "operator" => "eq", "value" => 2142))));
356  $results = AddFilter($filter, $cols);
357 
358  $this->assertEquals($results["status"], "000");
359  $this->assertEquals($results["appendToSQL"], " where (\"username\"::decimal = '2142')");
360 
361  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "userName", "operator" => "neq", "value" => 3240))));
362  $results = AddFilter($filter, $cols);
363 
364  $this->assertEquals($results["status"], "000");
365  $this->assertEquals($results["appendToSQL"], " where (\"username\"::decimal <> '3240')");
366 
367  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "userName", "operator" => "gt", "value" => 2269))));
368  $results = AddFilter($filter, $cols);
369 
370  $this->assertEquals($results["status"], "000");
371  $this->assertEquals($results["appendToSQL"], " where (\"username\"::decimal > '2269')");
372 
373  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "userName", "operator" => "gte", "value" => 1500))));
374  $results = AddFilter($filter, $cols);
375 
376  $this->assertEquals($results["status"], "000");
377  $this->assertEquals($results["appendToSQL"], " where (\"username\"::decimal >= '1500')");
378 
379  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "userName", "operator" => "lt", "value" => -1098))));
380  $results = AddFilter($filter, $cols);
381 
382  $this->assertEquals($results["status"], "000");
383  $this->assertEquals($results["appendToSQL"], " where (\"username\"::decimal < '-1098')");
384 
385  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "userName", "operator" => "lte", "value" => -1098))));
386  $results = AddFilter($filter, $cols);
387 
388  $this->assertEquals($results["status"], "000");
389  $this->assertEquals($results["appendToSQL"], " where (\"username\"::decimal <= '-1098')");
390 
391  // String tests
392  // -----------------------------
393  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "oldEmail", "operator" => "eq", "value" => "Nov 2017"))));
394  $results = AddFilter($filter, $cols);
395 
396  $this->assertEquals($results["status"], "000");
397  $this->assertEquals($results["appendToSQL"], " where (lower(trim(coalesce(\"oldemail\"::varchar, ''))) = 'nov 2017')");
398 
399  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "oldEmail", "operator" => "neq", "value" => "Nov 2017"))));
400  $results = AddFilter($filter, $cols);
401 
402  $this->assertEquals($results["status"], "000");
403  $this->assertEquals($results["appendToSQL"], " where (lower(trim(coalesce(\"oldemail\"::varchar, ''))) <> 'nov 2017')");
404 
405  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "oldEmail", "operator" => "startswith", "value" => "Jan"))));
406  $results = AddFilter($filter, $cols);
407 
408  $this->assertEquals($results["status"], "000");
409  $this->assertEquals($results["appendToSQL"], " where (lower(trim(coalesce(\"oldemail\"::varchar, ''))) like 'jan%')");
410 
411  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "oldEmail", "operator" => "endswith", "value" => "2014"))));
412  $results = AddFilter($filter, $cols);
413 
414  $this->assertEquals($results["status"], "000");
415  $this->assertEquals($results["appendToSQL"], " where (lower(trim(coalesce(\"oldemail\"::varchar, ''))) like '%2014')");
416 
417  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "oldEmail", "operator" => "contains", "value" => "u"))));
418  $results = AddFilter($filter, $cols);
419 
420  $this->assertEquals($results["status"], "000");
421  $this->assertEquals($results["appendToSQL"], " where (lower(trim(coalesce(\"oldemail\"::varchar, ''))) like '%u%')");
422 
423  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "oldEmail", "operator" => "doesnotcontain", "value" => "a"))));
424  $results = AddFilter($filter, $cols);
425 
426  $this->assertEquals($results["status"], "000");
427  $this->assertEquals($results["appendToSQL"], " where (lower(trim(coalesce(\"oldemail\"::varchar, ''))) not like '%a%')");
428 
429  // Date tests
430  // --------------------------
431  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "date", "operator" => "eq", "value" => "2018-03-01"))));
432  $results = AddFilter($filter, $cols);
433 
434  $this->assertEquals($results["status"], "000");
435  $this->assertEquals($results["appendToSQL"], " where ((case when trim(coalesce(\"date\"::varchar, '')) = '' then null else \"date\" end)::date = '2018-03-01')");
436 
437  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "date", "operator" => "neq", "value" => "2018-03-01"))));
438  $results = AddFilter($filter, $cols);
439 
440  $this->assertEquals($results["status"], "000");
441  $this->assertEquals($results["appendToSQL"], " where ((case when trim(coalesce(\"date\"::varchar, '')) = '' then null else \"date\" end)::date <> '2018-03-01')");
442 
443  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "date", "operator" => "gt", "value" => "2014-12-01 08:30:48"))));
444  $results = AddFilter($filter, $cols);
445 
446  $this->assertEquals($results["status"], "000");
447  $this->assertEquals($results["appendToSQL"], " where ((case when trim(coalesce(\"date\"::varchar, '')) = '' then null else \"date\" end)::date > '2014-12-01 08:30:48')");
448 
449  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "date", "operator" => "gte", "value" => "2014-12-01 08:30:48"))));
450  $results = AddFilter($filter, $cols);
451 
452  $this->assertEquals($results["status"], "000");
453  $this->assertEquals($results["appendToSQL"], " where ((case when trim(coalesce(\"date\"::varchar, '')) = '' then null else \"date\" end)::date >= '2014-12-01 08:30:48')");
454 
455  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "date", "operator" => "lt", "value" => "2014-10-01 08:54:24"))));
456  $results = AddFilter($filter, $cols);
457 
458  $this->assertEquals($results["status"], "000");
459  $this->assertEquals($results["appendToSQL"], " where ((case when trim(coalesce(\"date\"::varchar, '')) = '' then null else \"date\" end)::date < '2014-10-01 08:54:24')");
460 
461  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "date", "operator" => "lte", "value" => "2014-10-01 08:54:24"))));
462  $results = AddFilter($filter, $cols);
463 
464  $this->assertEquals($results["status"], "000");
465  $this->assertEquals($results["appendToSQL"], " where ((case when trim(coalesce(\"date\"::varchar, '')) = '' then null else \"date\" end)::date <= '2014-10-01 08:54:24')");
466 
467  // Boolean tests
468  // -------------------------
469  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "boolean", "operator" => "eq", "value" => true))));
470  $results = AddFilter($filter, $cols);
471 
472  $this->assertEquals($results["status"], "000");
473  $this->assertEquals($results["appendToSQL"], " where (lower(trim(coalesce(\"boolean\"::varchar, ''))) = '1')");
474 
475  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "boolean", "operator" => "eq", "value" => false))));
476  $results = AddFilter($filter, $cols);
477 
478  $this->assertEquals($results["status"], "000");
479  $this->assertEquals($results["appendToSQL"], " where (lower(trim(coalesce(\"boolean\"::varchar, ''))) = '')");
480 
481  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "boolean", "operator" => "isnull", "value" => null))));
482  $results = AddFilter($filter, $cols);
483 
484  $this->assertEquals($results["status"], "000");
485  $this->assertEquals($results["appendToSQL"], " where (\"boolean\" is null)");
486 
487  $filter = HCU_JsonEncode(array("logic" => "and", "filters" => array(array("field" => "boolean", "operator" => "isnotnull", "value" => null))));
488  $results = AddFilter($filter, $cols);
489 
490  $this->assertEquals($results["status"], "000");
491  $this->assertEquals($results["appendToSQL"], " where (\"boolean\" is not null)");
492  }
493 
494  function test_UpdateSelectSQL() {
495  $sql = null;
496  $colDefs = null;
497  $results = UpdateSelectSQL($sql, $colDefs);
498  $this->assertEquals($results["error"], "BaseSQL needs to be defined.");
499 
500  $sql = "select a, b, c from d";
501  $results = UpdateSelectSQL($sql, $colDefs);
502  $this->assertEquals($results["error"], "ColDefs needs to be defined.");
503 
504  $colDefs = array("two words" => "type", "123 numeric column" => "type", "one" => "type"); // The way that it is defined, the "type" part is useful but not for this function.
505  $results = UpdateSelectSQL($sql, $colDefs);
506  $this->assertEquals($results["status"], "000");
507  $this->assertEquals($results["modifiedSQL"], "select a as twoWords, b as n123NumericColumn, c as one from d");
508 
509  // Mismatched columns and *?
510  // ---------------------------
511 
512  // Too few columns in SQL: just modify the SQL.
513  $sql = "select a from d";
514  $results = UpdateSelectSQL($sql, $colDefs);
515  $this->assertEquals($results["status"], "000");
516  $this->assertEquals($results["modifiedSQL"], "select a as twoWords from d");
517 
518  // Too many columns in SQL: now have a problem.
519  $sql = "select a, b, c, d, e from f";
520  $results = UpdateSelectSQL($sql, $colDefs);
521  $this->assertEquals($results["error"], "Column mismatch found.");
522 
523  // Asterisk: it isn't supported and I have no plans to extend it especially because of the colDefs array for telling the report how to sort/filter/display.
524  $sql = "select * from a";
525  $results = UpdateSelectSQL($sql, $colDefs);
526  $this->assertEquals($results["modifiedSQL"], null);
527  $this->assertEquals($results["error"], "Asterisk is not supported at this time.");
528 
529 
530  $sql = "select a.a, b.*, c.c from a, b, c";
531  $results = UpdateSelectSQL($sql, $colDefs);
532  $this->assertEquals($results["modifiedSQL"], null);
533  $this->assertEquals($results["error"], "Asterisk is not supported at this time.");
534 
535  // Test complicated scenarios.
536  // ---------------------------------
537 
538  // ONLY the outermost select will receive the column labels. "With" will be ignored. Selects inside of case statements are also ignored.
539  $sql = "with blah as (select blah from blah) select case select 1, 2, 3 from a end, (select * from b limit 1), lala from (select a, b, c from d) inner join (select * from a)";
540  $results = UpdateSelectSQL($sql, $colDefs);
541  $this->assertEquals($results["status"], "000");
542  $expected = "with blah as (select blah from blah) select case select 1, 2, 3 from a end as twoWords, (select * from b limit 1) as n123NumericColumn, ";
543  $expected .= "lala as one from (select a, b, c from d) inner join (select * from a)";
544  $this->assertEquals($results["modifiedSQL"], $expected);
545 
546  // Has one or more "as"s in the SQL. These will be removed and it will use the columnname from the colDef.
547  $sql = "select a as a, b as b as b from c";
548  $results = UpdateSelectSQL($sql, $colDefs);
549  $this->assertEquals($results["modifiedSQL"], "select a as twoWords, b as n123NumericColumn from c");
550  }
551 
552 }
553 
554 
555 /**
556  * function GetTestCallback1($dbh)
557  * This function returns sample data for ensuring that the "nonSqlDataFunc" works and is usable.
558  *
559  * @param $dbh -- the database connection. This is not used in this callback but it is part of the parameter signature for #1315's benefit.
560  * @return array("data" => data of test data, "status" => 0, "error" => "")
561  */
562 function GetTestCallback1($dbh) {
563 
564  $data = array(
565  array("abrir", 1, "2001-01-01"),
566  array("bailar", 2, "2001-01-01"),
567  array("costar", 2, "2003-03-03"),
568  array("dar", 3, "2018-02-03"),
569  array("eligir", 4, "2019-09-23"),
570  array("fumar", 5, "2040-04-24"),
571  array("ganar", 6, "1000-01-01"),
572  array("hacer", 6, "1010-01-01"),
573  array("ir", 7, "2020-02-02"),
574  array("jugar", 8, "9090-09-09"),
575  array("k", 9, "8080-08-08"),
576  array("llamar", 10, "2010-05-03"),
577  array("mudar", 11, "7070-07-07"),
578  array("nadar", 12, "6060-06-06"),
579  array("olvidar", 13, "5050-05-05"),
580  array("pensar", 14, "4040-04-04"),
581  array("querer", 15, "2018-03-27"),
582  array("regalar", 16, "3030-03-03"),
583  array("ser", 17, "9999-12-31"),
584  array("tener", 18, "2020-02-02"),
585  array("utilizar", 19, "2019-04-04"),
586  array("vender", 20, "2018-03-28"),
587  array("w", 21, "2017-02-03"),
588  array("x", 22, "2018-05-04"),
589  array("yacer (lookup)", 23, "2001-01-01"),
590  array("zafar (lookup)", 24, "2001-01-01")
591  );
592 
593  return array("status" => "000", "error" => "", "data" => $data);
594 }
595 
596 /**
597  * function GetTestCallback2($dbh)
598  * This function throws an exception which should be passed on.
599  * @param $dbh -- the database connection
600  */
601 function GetTestCallback2($dbh) {
602  try {
603  throw new exception("This is a terrible test case.", 1);
604  } catch (exception $e) {
605  $returnArray = array("data" => array(), "status" => $e->getCode(), "error" => $e->getMessage());
606  }
607  return $returnArray;
608 }
609 
610 /**
611  * function GetTestCallback3($dbh)
612  * This function doesn't return anything so it should fail.
613  * @param $dbh -- the database connection
614  */
615 function GetTestCallback3($dbh) {
616 
617 }
ReportingTest\$unprocessedDataArray
static $unprocessedDataArray
Definition: ReportingTest.php:16
ReportingTest\test_nonSqlDataFuncAttribute
test_nonSqlDataFuncAttribute()
Definition: ReportingTest.php:274
ReportingTest\$reportList
static $reportList
Definition: ReportingTest.php:28
Generated by   doxygen 1.8.15