1 #include <criteria.h>
2
3 typedef enum { CRIT_NULL, CRIT_FLOAT, CRIT_WRONGTYPE, CRIT_STRING } CritType;
4
5 static CritType
criteria_inspect_values(GnmValue const * x,gnm_float * xr,gnm_float * yr,GnmCriteria * crit,gboolean coerce_to_float)6 criteria_inspect_values (GnmValue const *x, gnm_float *xr, gnm_float *yr,
7 GnmCriteria *crit, gboolean coerce_to_float)
8 {
9 GnmValue const *y = crit->x;
10
11 if (x == NULL || y == NULL)
12 return CRIT_NULL;
13
14 switch (y->v_any.type) {
15 case VALUE_BOOLEAN:
16 /* If we're searching for a bool -- even one that is
17 from a string search value -- we match only bools. */
18 if (!VALUE_IS_BOOLEAN (x))
19 return CRIT_WRONGTYPE;
20 *xr = value_get_as_float (x);
21 *yr = value_get_as_float (y);
22 return CRIT_FLOAT;
23
24 case VALUE_EMPTY:
25 return CRIT_WRONGTYPE;
26
27 case VALUE_STRING:
28 if (!VALUE_IS_STRING (x))
29 return CRIT_WRONGTYPE;
30 return CRIT_STRING;
31
32 default:
33 g_warning ("This should not happen. Please report.");
34 return CRIT_WRONGTYPE;
35
36 case VALUE_FLOAT: {
37 GnmValue *vx;
38 *yr = value_get_as_float (y);
39
40 if (VALUE_IS_BOOLEAN (x) || VALUE_IS_ERROR (x))
41 return CRIT_WRONGTYPE;
42 else if (VALUE_IS_FLOAT (x)) {
43 *xr = value_get_as_float (x);
44 return CRIT_FLOAT;
45 }
46
47 if (!coerce_to_float)
48 return CRIT_WRONGTYPE;
49
50 vx = format_match (value_peek_string (x), NULL, crit->date_conv);
51 if (VALUE_IS_EMPTY (vx) ||
52 VALUE_IS_BOOLEAN (y) != VALUE_IS_BOOLEAN (vx)) {
53 value_release (vx);
54 return CRIT_WRONGTYPE;
55 }
56
57 *xr = value_get_as_float (vx);
58 value_release (vx);
59 return CRIT_FLOAT;
60 }
61 }
62 }
63
64
65 static gboolean
criteria_test_equal(GnmValue const * x,GnmCriteria * crit)66 criteria_test_equal (GnmValue const *x, GnmCriteria *crit)
67 {
68 gnm_float xf, yf;
69 GnmValue const *y = crit->x;
70
71 switch (criteria_inspect_values (x, &xf, &yf, crit, TRUE)) {
72 default:
73 g_assert_not_reached ();
74 case CRIT_NULL:
75 case CRIT_WRONGTYPE:
76 return FALSE;
77 case CRIT_FLOAT:
78 return xf == yf;
79 case CRIT_STRING:
80 /* FIXME: _ascii_??? */
81 return g_ascii_strcasecmp (value_peek_string (x),
82 value_peek_string (y)) == 0;
83 }
84 }
85
86 static gboolean
criteria_test_unequal(GnmValue const * x,GnmCriteria * crit)87 criteria_test_unequal (GnmValue const *x, GnmCriteria *crit)
88 {
89 gnm_float xf, yf;
90
91 switch (criteria_inspect_values (x, &xf, &yf, crit, FALSE)) {
92 default:
93 g_assert_not_reached ();
94 case CRIT_NULL:
95 case CRIT_WRONGTYPE:
96 return TRUE;
97 case CRIT_FLOAT:
98 return xf != yf;
99 case CRIT_STRING:
100 /* FIXME: _ascii_??? */
101 return g_ascii_strcasecmp (value_peek_string (x),
102 value_peek_string (crit->x)) != 0;
103 }
104 }
105
106 static gboolean
criteria_test_less(GnmValue const * x,GnmCriteria * crit)107 criteria_test_less (GnmValue const *x, GnmCriteria *crit)
108 {
109 gnm_float xf, yf;
110 GnmValue const *y = crit->x;
111
112 switch (criteria_inspect_values (x, &xf, &yf, crit, FALSE)) {
113 default:
114 g_assert_not_reached ();
115 case CRIT_NULL:
116 case CRIT_WRONGTYPE:
117 return FALSE;
118 case CRIT_STRING:
119 return go_utf8_collate_casefold (value_peek_string (x),
120 value_peek_string (y)) < 0;
121 case CRIT_FLOAT:
122 return xf < yf;
123 }
124 }
125
126 static gboolean
criteria_test_greater(GnmValue const * x,GnmCriteria * crit)127 criteria_test_greater (GnmValue const *x, GnmCriteria *crit)
128 {
129 gnm_float xf, yf;
130 GnmValue const *y = crit->x;
131
132 switch (criteria_inspect_values (x, &xf, &yf, crit, FALSE)) {
133 default:
134 g_assert_not_reached ();
135 case CRIT_NULL:
136 case CRIT_WRONGTYPE:
137 return FALSE;
138 case CRIT_STRING:
139 return go_utf8_collate_casefold (value_peek_string (x),
140 value_peek_string (y)) > 0;
141 case CRIT_FLOAT:
142 return xf > yf;
143 }
144 }
145
146 static gboolean
criteria_test_less_or_equal(GnmValue const * x,GnmCriteria * crit)147 criteria_test_less_or_equal (GnmValue const *x, GnmCriteria *crit)
148 {
149 gnm_float xf, yf;
150 GnmValue const *y = crit->x;
151
152 switch (criteria_inspect_values (x, &xf, &yf, crit, FALSE)) {
153 default:
154 g_assert_not_reached ();
155 case CRIT_NULL:
156 case CRIT_WRONGTYPE:
157 return FALSE;
158 case CRIT_STRING:
159 return go_utf8_collate_casefold (value_peek_string (x),
160 value_peek_string (y)) <= 0;
161 case CRIT_FLOAT:
162 return xf <= yf;
163 }
164 }
165
166 static gboolean
criteria_test_greater_or_equal(GnmValue const * x,GnmCriteria * crit)167 criteria_test_greater_or_equal (GnmValue const *x, GnmCriteria *crit)
168 {
169 gnm_float xf, yf;
170 GnmValue const *y = crit->x;
171
172 switch (criteria_inspect_values (x, &xf, &yf, crit, FALSE)) {
173 default:
174 g_assert_not_reached ();
175 case CRIT_NULL:
176 case CRIT_WRONGTYPE:
177 return FALSE;
178 case CRIT_STRING:
179 return go_utf8_collate_casefold (value_peek_string (x),
180 value_peek_string (y)) >= 0;
181 case CRIT_FLOAT:
182 return xf >= yf;
183 }
184 }
185
186 static gboolean
criteria_test_match(GnmValue const * x,GnmCriteria * crit)187 criteria_test_match (GnmValue const *x, GnmCriteria *crit)
188 {
189 if (!crit->has_rx)
190 return FALSE;
191
192 // Only strings are matched
193 if (!VALUE_IS_STRING (x))
194 return FALSE;
195
196 return go_regexec (&crit->rx, value_peek_string (x), 0, NULL, 0) ==
197 GO_REG_OK;
198 }
199
200 static gboolean
criteria_test_empty(GnmValue const * x,GnmCriteria * crit)201 criteria_test_empty (GnmValue const *x, GnmCriteria *crit)
202 {
203 return VALUE_IS_EMPTY (x);
204 }
205
206 static gboolean
criteria_test_blank(GnmValue const * x,GnmCriteria * crit)207 criteria_test_blank (GnmValue const *x, GnmCriteria *crit)
208 {
209 if (VALUE_IS_EMPTY (x))
210 return TRUE;
211 if (!VALUE_IS_STRING (x))
212 return FALSE;
213 return *value_peek_string (x) == 0;
214 }
215
216 static gboolean
criteria_test_nonempty(GnmValue const * x,GnmCriteria * crit)217 criteria_test_nonempty (GnmValue const *x, GnmCriteria *crit)
218 {
219 return !VALUE_IS_EMPTY (x);
220 }
221
222 static gboolean
criteria_test_nothing(GnmValue const * x,GnmCriteria * crit)223 criteria_test_nothing (GnmValue const *x, GnmCriteria *crit)
224 {
225 return FALSE;
226 }
227
228 /*
229 * Finds a column index of a field.
230 */
231 int
find_column_of_field(GnmEvalPos const * ep,GnmValue const * database,GnmValue const * field)232 find_column_of_field (GnmEvalPos const *ep,
233 GnmValue const *database, GnmValue const *field)
234 {
235 Sheet *sheet;
236 GnmCell *cell;
237 gchar *field_name;
238 int begin_col, end_col, row, n, column;
239 int offset;
240
241 // I'm not certain we should demand this, but the code clearly wants
242 // it.
243 if (!VALUE_IS_CELLRANGE (database))
244 return -1;
245
246 offset = database->v_range.cell.a.col;
247
248 if (VALUE_IS_FLOAT (field))
249 return value_get_as_int (field) + offset - 1;
250
251 if (!VALUE_IS_STRING (field))
252 return -1;
253
254 sheet = eval_sheet (database->v_range.cell.a.sheet, ep->sheet);
255 field_name = value_get_as_string (field);
256 column = -1;
257
258 /* find the column that is labeled after `field_name' */
259 begin_col = database->v_range.cell.a.col;
260 end_col = database->v_range.cell.b.col;
261 row = database->v_range.cell.a.row;
262
263 for (n = begin_col; n <= end_col; n++) {
264 char const *txt;
265 gboolean match;
266
267 cell = sheet_cell_get (sheet, n, row);
268 if (cell == NULL)
269 continue;
270 gnm_cell_eval (cell);
271
272 txt = cell->value
273 ? value_peek_string (cell->value)
274 : "";
275 match = (g_ascii_strcasecmp (field_name, txt) == 0);
276 if (match) {
277 column = n;
278 break;
279 }
280 }
281
282 g_free (field_name);
283 return column;
284 }
285
286 void
gnm_criteria_unref(GnmCriteria * criteria)287 gnm_criteria_unref (GnmCriteria *criteria)
288 {
289 if (!criteria || criteria->ref_count-- > 1)
290 return;
291 value_release (criteria->x);
292 if (criteria->has_rx)
293 go_regfree (&criteria->rx);
294 g_free (criteria);
295 }
296
297 static GnmCriteria *
gnm_criteria_ref(GnmCriteria * criteria)298 gnm_criteria_ref (GnmCriteria *criteria)
299 {
300 criteria->ref_count++;
301 return criteria;
302 }
303
304 GType
gnm_criteria_get_type(void)305 gnm_criteria_get_type (void)
306 {
307 static GType t = 0;
308
309 if (t == 0) {
310 t = g_boxed_type_register_static ("GnmCriteria",
311 (GBoxedCopyFunc)gnm_criteria_ref,
312 (GBoxedFreeFunc)gnm_criteria_unref);
313 }
314 return t;
315 }
316
317 /**
318 * free_criterias:
319 * @criterias: (element-type GnmCriteria) (transfer full): the criteria to be
320 * freed.
321 * Frees the allocated memory.
322 */
323 void
free_criterias(GSList * criterias)324 free_criterias (GSList *criterias)
325 {
326 GSList *list = criterias;
327
328 while (criterias != NULL) {
329 GnmDBCriteria *criteria = criterias->data;
330 g_slist_free_full (criteria->conditions,
331 (GFreeFunc)gnm_criteria_unref);
332 g_free (criteria);
333 criterias = criterias->next;
334 }
335 g_slist_free (list);
336 }
337
338 /**
339 * parse_criteria:
340 * @crit_val: #GnmValue
341 * @date_conv: #GODateConventions
342 *
343 * Returns: (transfer full): GnmCriteria which caller must free.
344 *
345 * ">=value"
346 * "<=value"
347 * "<>value"
348 * "<value"
349 * ">value"
350 * "=value"
351 * "pattern"
352 **/
353 GnmCriteria *
parse_criteria(GnmValue const * crit_val,GODateConventions const * date_conv,gboolean anchor_end)354 parse_criteria (GnmValue const *crit_val, GODateConventions const *date_conv,
355 gboolean anchor_end)
356 {
357 int len;
358 char const *criteria;
359 GnmCriteria *res = g_new0 (GnmCriteria, 1);
360 GnmValue *empty;
361
362 res->iter_flags = CELL_ITER_IGNORE_BLANK;
363 res->date_conv = date_conv;
364 res->ref_count = 1;
365
366 if (VALUE_IS_NUMBER (crit_val)) {
367 res->fun = criteria_test_equal;
368 res->x = value_dup (crit_val);
369 return res;
370 }
371
372 if (VALUE_IS_EMPTY (crit_val)) {
373 // Empty value
374 res->fun = criteria_test_nothing;
375 res->x = value_new_empty ();
376 return res;
377 }
378
379 criteria = value_peek_string (crit_val);
380 if (*criteria == 0) {
381 res->fun = criteria_test_blank;
382 len = 0;
383 } else if (strncmp (criteria, "<=", 2) == 0) {
384 res->fun = criteria_test_less_or_equal;
385 len = 2;
386 } else if (strncmp (criteria, ">=", 2) == 0) {
387 res->fun = criteria_test_greater_or_equal;
388 len = 2;
389 } else if (strncmp (criteria, "<>", 2) == 0) {
390 /* "<>" by itself is special: */
391 res->fun = (criteria[2] == 0) ? criteria_test_nonempty : criteria_test_unequal;
392 len = 2;
393 } else if (*criteria == '<') {
394 res->fun = criteria_test_less;
395 len = 1;
396 } else if (*criteria == '=') {
397 /* "=" by itself is special: */
398 res->fun = (criteria[1] == 0) ? criteria_test_empty : criteria_test_equal;
399 len = 1;
400 } else if (*criteria == '>') {
401 res->fun = criteria_test_greater;
402 len = 1;
403 } else {
404 res->fun = criteria_test_match;
405 res->has_rx = (gnm_regcomp_XL (&res->rx, criteria, GO_REG_ICASE, TRUE, anchor_end) == GO_REG_OK);
406 len = 0;
407 }
408
409 res->x = format_match_number (criteria + len, NULL, date_conv);
410 if (res->x == NULL)
411 res->x = value_new_string (criteria + len);
412 else if (len == 0 && VALUE_IS_NUMBER (res->x))
413 res->fun = criteria_test_equal;
414
415 empty = value_new_empty ();
416 if (res->fun (empty, res))
417 res->iter_flags &= ~CELL_ITER_IGNORE_BLANK;
418 value_release (empty);
419
420 return res;
421 }
422
423
424 static GSList *
parse_criteria_range(Sheet * sheet,int b_col,int b_row,int e_col,int e_row,int * field_ind,gboolean anchor_end)425 parse_criteria_range (Sheet *sheet, int b_col, int b_row, int e_col, int e_row,
426 int *field_ind, gboolean anchor_end)
427 {
428 GSList *criterias = NULL;
429 GODateConventions const *date_conv = sheet_date_conv (sheet);
430 int i, j;
431
432 for (i = b_row; i <= e_row; i++) {
433 GnmDBCriteria *new_criteria = g_new (GnmDBCriteria, 1);
434 GSList *conditions = NULL;
435
436 for (j = b_col; j <= e_col; j++) {
437 GnmCriteria *cond;
438 GnmCell *cell = sheet_cell_get (sheet, j, i);
439 if (cell != NULL)
440 gnm_cell_eval (cell);
441 if (gnm_cell_is_empty (cell))
442 continue;
443
444 cond = parse_criteria (cell->value, date_conv,
445 anchor_end);
446 cond->column = (field_ind != NULL)
447 ? field_ind[j - b_col]
448 : j - b_col;
449 conditions = g_slist_prepend (conditions, cond);
450 }
451
452 new_criteria->conditions = g_slist_reverse (conditions);
453 criterias = g_slist_prepend (criterias, new_criteria);
454 }
455
456 return g_slist_reverse (criterias);
457 }
458
459 /**
460 * parse_database_criteria:
461 * @ep: #GnmEvalPos
462 * @database: #GnmValue
463 * @criteria: #GnmValue
464 *
465 * Parses the criteria cell range.
466 * Returns: (element-type GnmDBCriteria) (transfer full):
467 */
468 GSList *
parse_database_criteria(GnmEvalPos const * ep,GnmValue const * database,GnmValue const * criteria)469 parse_database_criteria (GnmEvalPos const *ep, GnmValue const *database, GnmValue const *criteria)
470 {
471 Sheet *sheet;
472 GnmCell *cell;
473 int i;
474 int b_col, b_row, e_col, e_row;
475 int *field_ind;
476 GSList *res;
477
478 g_return_val_if_fail (VALUE_IS_CELLRANGE (criteria), NULL);
479
480 sheet = eval_sheet (criteria->v_range.cell.a.sheet, ep->sheet);
481 b_col = criteria->v_range.cell.a.col;
482 b_row = criteria->v_range.cell.a.row;
483 e_col = criteria->v_range.cell.b.col;
484 e_row = criteria->v_range.cell.b.row;
485
486 if (e_col < b_col) {
487 int tmp = b_col;
488 b_col = e_col;
489 e_col = tmp;
490 }
491
492 /* Find the index numbers for the columns of criterias */
493 field_ind = g_new (int, e_col - b_col + 1);
494 for (i = b_col; i <= e_col; i++) {
495 cell = sheet_cell_get (sheet, i, b_row);
496 if (cell == NULL)
497 continue;
498 gnm_cell_eval (cell);
499 if (gnm_cell_is_empty (cell))
500 continue;
501 field_ind[i - b_col] =
502 find_column_of_field (ep, database, cell->value);
503 if (field_ind[i - b_col] == -1) {
504 g_free (field_ind);
505 return NULL;
506 }
507 }
508
509 res = parse_criteria_range (sheet, b_col, b_row + 1,
510 e_col, e_row, field_ind,
511 FALSE);
512 g_free (field_ind);
513 return res;
514 }
515
516 /**
517 * find_rows_that_match:
518 * @sheet: #Sheet
519 * @first_col: first column.
520 * @first_row: first row.
521 * @last_col: last column.
522 * @last_row: last row.
523 * @criterias: (element-type GnmDBCriteria): the criteria to use.
524 * @unique_only:
525 *
526 * Finds the rows from the given database that match the criteria.
527 * Returns: (element-type int) (transfer full): the list of matching rows.
528 **/
529 GSList *
find_rows_that_match(Sheet * sheet,int first_col,int first_row,int last_col,int last_row,GSList * criterias,gboolean unique_only)530 find_rows_that_match (Sheet *sheet, int first_col, int first_row,
531 int last_col, int last_row,
532 GSList *criterias, gboolean unique_only)
533 {
534 GSList *rows = NULL;
535 GSList const *crit_ptr, *cond_ptr;
536 int row;
537 gboolean add_flag;
538 char const *t1, *t2;
539 GnmCell *test_cell;
540 GnmValue const *empty = value_new_empty ();
541
542 for (row = first_row; row <= last_row; row++) {
543 add_flag = TRUE;
544 for (crit_ptr = criterias; crit_ptr; crit_ptr = crit_ptr->next) {
545 GnmDBCriteria const *crit = crit_ptr->data;
546 add_flag = TRUE;
547 for (cond_ptr = crit->conditions;
548 cond_ptr != NULL ; cond_ptr = cond_ptr->next) {
549 GnmCriteria *cond = cond_ptr->data;
550 test_cell = sheet_cell_get (sheet, cond->column, row);
551 if (test_cell != NULL)
552 gnm_cell_eval (test_cell);
553 if (!cond->fun (test_cell ? test_cell->value : empty, cond)) {
554 add_flag = FALSE;
555 break;
556 }
557 }
558
559 if (add_flag)
560 break;
561 }
562 if (add_flag) {
563 if (unique_only) {
564 GSList *c;
565 GnmCell *cell;
566 gint i;
567
568 for (c = rows; c != NULL; c = c->next) {
569 int trow = GPOINTER_TO_INT (c->data);
570 for (i = first_col; i <= last_col; i++) {
571 test_cell = sheet_cell_get (sheet, i, trow);
572 cell = sheet_cell_get (sheet, i, row);
573
574 /* FIXME: this is probably not right, but crashing is more wrong. */
575 if (test_cell == NULL || cell == NULL)
576 continue;
577
578 t1 = cell->value
579 ? value_peek_string (cell->value)
580 : "";
581 t2 = test_cell->value
582 ? value_peek_string (test_cell->value)
583 : "";
584 if (strcmp (t1, t2) != 0)
585 goto row_ok;
586 }
587 goto filter_row;
588 row_ok:
589 ;
590 }
591 }
592 rows = g_slist_prepend (rows, GINT_TO_POINTER (row));
593 filter_row:
594 ;
595 }
596 }
597
598 return g_slist_reverse (rows);
599 }
600
601 /****************************************************************************/
602
603 /**
604 * gnm_ifs_func:
605 * @data: (element-type GnmValue):
606 * @crits: (element-type GnmCriteria): criteria
607 * @vals:
608 * @fun: (scope call): function to evaluate on filtered data
609 * @err: error value in case @fun fails.
610 * @ep: evaluation position
611 * @flags: #CollectFlags flags describing the collection and interpretation
612 * of values from @data.
613 *
614 * This implements a Gnumeric sheet database function of the "*IFS" type
615 * This function collects the arguments and uses @fun to do
616 * the actual computation.
617 *
618 * Returns: (transfer full): Function result or error value.
619 */
620 GnmValue *
gnm_ifs_func(GPtrArray * data,GPtrArray * crits,GnmValue const * vals,float_range_function_t fun,GnmStdError err,GnmEvalPos const * ep,CollectFlags flags)621 gnm_ifs_func (GPtrArray *data, GPtrArray *crits, GnmValue const *vals,
622 float_range_function_t fun, GnmStdError err,
623 GnmEvalPos const *ep, CollectFlags flags)
624 {
625 int sx, sy, x, y;
626 unsigned ui, N = 0, nalloc = 0;
627 gnm_float *xs = NULL;
628 GnmValue *res = NULL;
629 gnm_float fres;
630
631 g_return_val_if_fail (data->len == crits->len, NULL);
632
633 if (flags & ~(COLLECT_IGNORE_STRINGS |
634 COLLECT_IGNORE_BOOLS |
635 COLLECT_IGNORE_BLANKS |
636 COLLECT_IGNORE_ERRORS)) {
637 g_warning ("unsupported flags in gnm_ifs_func %x", flags);
638 }
639
640 sx = value_area_get_width (vals, ep);
641 sy = value_area_get_height (vals, ep);
642 for (ui = 0; ui < data->len; ui++) {
643 GnmValue const *datai = g_ptr_array_index (data, ui);
644 if (value_area_get_width (datai, ep) != sx ||
645 value_area_get_height (datai, ep) != sy)
646 return value_new_error_VALUE (ep);
647 }
648
649 for (y = 0; y < sy; y++) {
650 for (x = 0; x < sx; x++) {
651 GnmValue const *v;
652 gboolean match = TRUE;
653
654 for (ui = 0; match && ui < crits->len; ui++) {
655 GnmCriteria *crit = g_ptr_array_index (crits, ui);
656 GnmValue const *datai = g_ptr_array_index (data, ui);
657 v = value_area_get_x_y (datai, x, y, ep);
658
659 match = crit->fun (v, crit);
660 }
661 if (!match)
662 continue;
663
664 // Match. Maybe collect the data point.
665
666 v = value_area_get_x_y (vals, x, y, ep);
667 if ((flags & COLLECT_IGNORE_STRINGS) && VALUE_IS_STRING (v))
668 continue;
669 if ((flags & COLLECT_IGNORE_BOOLS) && VALUE_IS_BOOLEAN (v))
670 continue;
671 if ((flags & COLLECT_IGNORE_BLANKS) && VALUE_IS_EMPTY (v))
672 continue;
673 if ((flags & COLLECT_IGNORE_ERRORS) && VALUE_IS_ERROR (v))
674 continue;
675
676 if (VALUE_IS_ERROR (v)) {
677 res = value_dup (v);
678 goto out;
679 }
680
681 if (N >= nalloc) {
682 nalloc = (2 * nalloc) + 100;
683 xs = g_renew (gnm_float, xs, nalloc);
684 }
685 xs[N++] = value_get_as_float (v);
686 }
687 }
688
689 if (fun (xs, N, &fres)) {
690 res = value_new_error_std (ep, err);
691 } else
692 res = value_new_float (fres);
693
694 out:
695 g_free (xs);
696 return res;
697 }
698
699 /****************************************************************************/
700