fn-database.c 35.3 KB
Newer Older
1
2
3
4
5
6
7
/*
 * fn-database.c:  Built in database functions and functions registration
 *
 * Author:
 *  Jukka-Pekka Iivonen (iivonen@iki.fi)
 */
#include <config.h>
8
#include <math.h>
9
#include <glib.h>
10
#include "parse-util.h"
Jody Goldberg's avatar
Jody Goldberg committed
11
#include "func-util.h"
12
#include "func.h"
13
#include "cell.h"
14
15
16
17

/* Type definitions */

typedef struct {
18
        int    row;
19
20
21
22
23
24
        GSList *conditions;
} database_criteria_t;


/* Callback functions */

25
26
int
criteria_test_equal(Value *x, Value *y)
27
{
28
        if (VALUE_IS_NUMBER(x) && VALUE_IS_NUMBER(y))
Michael Meeks's avatar
Michael Meeks committed
29
	        if (value_get_as_float (x) == value_get_as_float (y))
30
31
32
33
		        return 1;
		else
		        return 0;
	else if (x->type == VALUE_STRING && y->type == VALUE_STRING
34
		 && g_strcasecmp (x->v_str.val->str, y->v_str.val->str) == 0)
35
36
37
38
39
	        return 1;
	else
	        return 0;
}

40
41
int
criteria_test_unequal(Value *x, Value *y)
42
{
43
        if (VALUE_IS_NUMBER(x) && VALUE_IS_NUMBER(y))
Michael Meeks's avatar
Michael Meeks committed
44
	        if (value_get_as_float (x) != value_get_as_float (y))
45
46
47
48
		        return 1;
		else
		        return 0;
	else if (x->type == VALUE_STRING && y->type == VALUE_STRING
49
		 && g_strcasecmp(x->v_str.val->str, y->v_str.val->str) != 0)
50
51
52
53
54
	        return 1;
	else
	        return 0;
}

55
56
int
criteria_test_less(Value *x, Value *y)
57
{
58
        if (VALUE_IS_NUMBER(x) && VALUE_IS_NUMBER(y))
Michael Meeks's avatar
Michael Meeks committed
59
	        if (value_get_as_float (x) < value_get_as_float (y))
60
61
62
		        return 1;
		else
		        return 0;
63
64
65
66
	else
	        return 0;
}

67
68
int
criteria_test_greater(Value *x, Value *y)
69
{
70
        if (VALUE_IS_NUMBER(x) && VALUE_IS_NUMBER(y))
Michael Meeks's avatar
Michael Meeks committed
71
	        if (value_get_as_float (x) > value_get_as_float (y))
72
73
74
		        return 1;
		else
		        return 0;
75
76
77
78
	else
	        return 0;
}

79
80
int
criteria_test_less_or_equal(Value *x, Value *y)
81
{
82
        if (VALUE_IS_NUMBER(x) && VALUE_IS_NUMBER(y))
Michael Meeks's avatar
Michael Meeks committed
83
	        if (value_get_as_float (x) <= value_get_as_float (y))
84
85
86
		        return 1;
		else
		        return 0;
87
88
89
90
	else
	        return 0;
}

91
92
int
criteria_test_greater_or_equal(Value *x, Value *y)
93
{
94
        if (VALUE_IS_NUMBER(x) && VALUE_IS_NUMBER(y))
Michael Meeks's avatar
Michael Meeks committed
95
	        if (value_get_as_float (x) >= value_get_as_float (y))
96
97
98
		        return 1;
		else
		        return 0;
99
100
101
102
103
	else
	        return 0;
}


104
/* Finds a column index of a field.
105
106
 */
static int
107
find_column_of_field (const EvalPos *ep, Value *database, Value *field)
108
109
110
111
112
113
114
{
        Sheet *sheet;
        Cell  *cell;
	gchar *field_name;
	int   begin_col, end_col, row, n, column;
	int   offset;

115
116
	offset = database->v_range.cell.b.col -
	  database->v_range.cell.a.col;
117

118
	if (field->type == VALUE_INTEGER)
119
120
121
122
123
	        return value_get_as_int (field) + offset - 1;

	if (field->type != VALUE_STRING)
	        return -1;

124
	sheet = eval_sheet (database->v_range.cell.a.sheet, ep->sheet);
Michael Meeks's avatar
Michael Meeks committed
125
	field_name = value_get_as_string (field);
126
127
	column = -1;

128
	/* find the column that is labeled after `field_name' */
129
130
131
	begin_col = database->v_range.cell.a.col;
	end_col = database->v_range.cell.b.col;
	row = database->v_range.cell.a.row;
132

Michael Meeks's avatar
Michael Meeks committed
133
	for (n = begin_col; n <= end_col; n++) {
134
135
136
		char *txt;
		gboolean match;

137
138
139
	        cell = sheet_cell_get(sheet, n, row);
		if (cell == NULL)
		        continue;
140

141
		txt = cell_get_rendered_text (cell);
Michael Meeks's avatar
Michael Meeks committed
142
		match = (g_strcasecmp (field_name, txt) == 0);
143
144
		g_free (txt);
		if (match) {
145
146
147
148
149
		        column = n;
			break;
		}
	}

150
	g_free (field_name);
151
152
153
154
155
	return column;
}

/* Frees the allocated memory.
 */
156
void
157
158
159
160
161
free_criterias(GSList *criterias)
{
        GSList *list = criterias;

        while (criterias != NULL) {
162
		GSList *l;
163
164
	        database_criteria_t *criteria = criterias->data;

165
166
167
168
169
170
		for (l = criteria->conditions; l; l = l->next) {
			func_criteria_t *cond = l->data;
			value_release (cond->x);
			g_free (cond);
		}

171
		g_slist_free(criteria->conditions);
172
		g_free (criteria);
173
174
175
176
177
	        criterias = criterias->next;
	}
	g_slist_free(list);
}

178
void
179
180
parse_criteria(const char *criteria, criteria_test_fun_t *fun,
	       Value **test_value)
181
182
{
	char    *p;
Jody Goldberg's avatar
Jody Goldberg committed
183
	gnum_float tmp;
184
185
186
	int     len;

        if (strncmp(criteria, "<=", 2) == 0) {
187
	        *fun = (criteria_test_fun_t) criteria_test_less_or_equal;
188
189
		len=2;
	} else if (strncmp(criteria, ">=", 2) == 0) {
190
	        *fun = (criteria_test_fun_t) criteria_test_greater_or_equal;
191
192
		len=2;
	} else if (strncmp(criteria, "<>", 2) == 0) {
193
	        *fun = (criteria_test_fun_t) criteria_test_unequal;
194
195
		len=2;
	} else if (*criteria == '<') {
196
	        *fun = (criteria_test_fun_t) criteria_test_less;
197
198
		len=1;
	} else if (*criteria == '=') {
199
	        *fun = (criteria_test_fun_t) criteria_test_equal;
200
201
		len=1;
	} else if (*criteria == '>') {
202
	        *fun = (criteria_test_fun_t) criteria_test_greater;
203
204
		len=1;
	} else {
205
	        *fun = (criteria_test_fun_t) criteria_test_equal;
206
207
		len=0;
	}
208
209

	tmp = g_strtod(criteria+len, &p);
210
211

	if (p == criteria+len || *p != '\0')
Michael Meeks's avatar
Michael Meeks committed
212
	        *test_value = value_new_string (criteria+len);
213
	else
Michael Meeks's avatar
Michael Meeks committed
214
	        *test_value = value_new_float (tmp);
215
216
}

217
218
219
220

GSList *
parse_criteria_range(Sheet *sheet, int b_col, int b_row, int e_col, int e_row,
		     int   *field_ind)
221
222
{
	database_criteria_t *new_criteria;
223
	GSList              *criterias = NULL;
224
225
	GSList              *conditions;
	Cell                *cell;
226
227
	func_criteria_t     *cond;
	gchar               *cell_str;
228

229
        int i, j;
230

231
	for (i=b_row; i<=e_row; i++) {
232
233
	        new_criteria = g_new(database_criteria_t, 1);
		conditions = NULL;
234

235
236
		for (j=b_col; j<=e_col; j++) {
		        cell = sheet_cell_get(sheet, j, i);
237
			if (cell == NULL || cell->value == NULL)
238
239
			        continue;

240
			cond = g_new(func_criteria_t, 1);
241
242

			/* Equality condition (in number format) */
243
			if (VALUE_IS_NUMBER(cell->value)) {
244
245
246
247
248
249
			        cond->x = value_duplicate (cell->value);
				cond->fun =
				  (criteria_test_fun_t) criteria_test_equal;
				cond->column = field_ind[j-b_col];
				conditions = g_slist_append(conditions, cond);
				continue;
250
			}
251
252

			/* Other conditions (in string format) */
253
			cell_str = cell_get_rendered_text (cell);
254
			parse_criteria(cell_str, &cond->fun, &cond->x);
255
256
257
258
			if (field_ind != NULL)
			        cond->column = field_ind[j-b_col];
			else
			        cond->column = j-b_col;
259
			g_free (cell_str);
260

261
262
			conditions = g_slist_append(conditions, cond);
		}
263

264
265
266
		new_criteria->conditions = conditions;
		criterias = g_slist_append(criterias, new_criteria);
	}
267

268
269
270
271
272
273
	return criterias;
}

/* Parses the criteria cell range.
 */
static GSList *
274
parse_database_criteria (const EvalPos *ep, Value *database,
275
276
277
278
279
280
281
282
283
284
			 Value *criteria)
{
	Sheet               *sheet;
	GSList              *criterias;
	Cell                *cell;

        int   i;
	int   b_col, b_row, e_col, e_row;
	int   *field_ind;

285
286
287
288
289
	sheet = eval_sheet (database->v_range.cell.a.sheet, ep->sheet);
	b_col = criteria->v_range.cell.a.col;
	b_row = criteria->v_range.cell.a.row;
	e_col = criteria->v_range.cell.b.col;
	e_row = criteria->v_range.cell.b.row;
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308

	field_ind = g_new(int, (e_col-b_col+1));

	/* Find the index numbers for the columns of criterias */
	for (i=b_col; i<=e_col; i++) {
	        cell = sheet_cell_get(sheet, i, b_row);
		if (cell == NULL || cell->value == NULL)
		        continue;
		field_ind[i-b_col] =
		        find_column_of_field (ep, database, cell->value);
		if (field_ind[i-b_col] == -1) {
		        g_free(field_ind);
			return NULL;
		}
	}

	criterias = parse_criteria_range (sheet, b_col, b_row+1, e_col, e_row,
					  field_ind);

309
	g_free(field_ind);
310
311
312
	return criterias;
}

Jody Goldberg's avatar
fix.    
Jody Goldberg committed
313
314
315
/**
 * find_cells_that_match :
 * Finds the cells from the given column that match the criteria.
316
317
 */
static GSList *
318
find_cells_that_match (Sheet *sheet, Value *database,
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
319
		       int col, GSList *criterias)
320
{
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
321
322
323
324
	GSList *ptr, *condition, *cells;
	int    row, first_row, last_row;
	gboolean add_flag;

325
	cells = NULL;
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
326
	/* TODO : Why ignore the first row ? */
327
	first_row = database->v_range.cell.a.row + 1;
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
328
	last_row  = database->v_range.cell.b.row;
329

Jody Goldberg's avatar
fix.    
Jody Goldberg committed
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
	for (row = first_row; row <= last_row; row++) {
		Cell *cell = sheet_cell_get (sheet, col, row);
		if (cell == NULL || cell->value == NULL)
			continue;

		add_flag = TRUE;
		for (ptr = criterias; ptr != NULL; ptr = ptr->next) {
			database_criteria_t const *current_criteria = ptr->data;

			add_flag = TRUE;
			condition = current_criteria->conditions;

			for (;condition != NULL ; condition = condition->next) {
				func_criteria_t const *cond = condition->data;
				Cell const *tmp = sheet_cell_get (sheet,
					cond->column, row);

				if (tmp != NULL && tmp->value != NULL) {
					if (! cond->fun (tmp->value, cond->x)) {
						add_flag = FALSE;
						break;
					}
				}
			}

			if (add_flag)
				break;
		}
		if (add_flag)
			cells = g_slist_append (cells, cell);
360
	}
361

362
363
364
	return cells;
}

365
366
367
368

/* Finds the rows from the given database that match the criteria.
 */
GSList *
369
find_rows_that_match (Sheet *sheet, int first_col, int first_row,
370
371
372
373
		      int last_col, int last_row,
		      GSList *criterias, gboolean unique_only)
{
	GSList *current, *conditions, *rows;
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
374
	int    row, add_flag;
375
376
377
	rows = NULL;

	for (row=first_row; row<=last_row; row++) {
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
		Cell   *test_cell;

		current = criterias;
		add_flag = 1;
		for (current = criterias; current != NULL;
		     current=current->next) {
			database_criteria_t *current_criteria;

			add_flag = 1;
			current_criteria = current->data;
			conditions = current_criteria->conditions;

			while (conditions != NULL) {
				func_criteria_t *cond = conditions->data;

				test_cell =
					sheet_cell_get(sheet,
						       first_col + cond->column, row);
				if (test_cell == NULL ||
				    test_cell->value == NULL)
					continue;

				if (! cond->fun (test_cell->value, cond->x)) {
					add_flag = 0;
					break;
				}
				conditions = conditions->next;
			}

			if (add_flag)
				break;
		}
		if (add_flag) {
			gint *p;

			if (unique_only) {
				GSList *c;
				Cell   *cell;
				gint    i, trow;
				gchar  *t1, *t2;

				for (c=rows; c != NULL; c=c->next) {
					trow = *((gint *) c->data);
					for (i=first_col; i<=last_col; i++) {
						test_cell =
							sheet_cell_get(sheet, i, trow);
						cell =
							sheet_cell_get(sheet, i, row);
						t1 = cell_get_rendered_text (cell);
						t2 = cell_get_rendered_text (test_cell);
						if (strcmp (t1, t2) != 0)
							goto row_ok;
					}
					goto filter_row;
row_ok:
					;
				}
			}
			p = g_new (gint, 1);
			*p = row;
			rows = g_slist_append(rows, (gpointer) p);
filter_row:
			;
		}
442
443
444
445
446
	}

	return rows;
}

447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
#define DB_ARGUMENT_HELP \
	   "@database is a range of cells in which rows of related " \
	   "information are records and columns of data are fields. " \
	   "The first row of a database contains labels for each column. " \
	   "\n" \
	   "@field specifies which column is used in the function.  If " \
	   "@field is an integer, i.e. 2, the second column is used. Field " \
	   "can also be the label of a column.  For example, ``Age'' refers " \
	   "to the column with the label ``Age'' in @database range. " \
	   "\n" \
	   "@criteria is the range of cells which contains the specified " \
	   "conditions.  The first row of a @criteria should contain the " \
	   "labels of the fields for which the criterias are for.  Cells " \
	   "below the labels specify conditions, for example, ``>3'' or " \
	   "``<9''.  Equality condition can be given simply by specifing a " \
	   "value, e.g. ``3'' or ``John''.  Each row in @criteria specifies " \
	   "a separate condition, i.e. if a row in @database matches with " \
	   "one of the rows in @criteria then that row is counted in " \
	   "(technically speaking boolean OR between the rows in " \
	   "@criteria).  If @criteria specifies more than one columns then " \
	   "each of the conditions in these columns should be true that " \
	   "the row in @database matches (again technically speaking " \
	   "boolean AND between the columns in each row in @criteria). " \
           "\n" \
	   "@EXAMPLES=\n" \
	   "Let us assume that the range A1:C7 contain the following " \
	   "values:\n" \
	   "Name    Age     Salary\n" \
	   "John    34      54342\n" \
	   "Bill    35      22343\n" \
	   "Clark   29      34323\n" \
	   "Bob     43      47242\n" \
	   "Susan   37      42932\n" \
	   "Jill    45      45324\n" \
	   "\n" \
	   "In addition, the cells A9:B11 contain the following values:\n" \
	   "Age     Salary\n" \
	   "<30\n" \
	   ">40     >46000\n"


488
/***************************************************************************/
489
490
491
492
493
494
495
496
497

static char *help_daverage = {
        N_("@FUNCTION=DAVERAGE\n"
           "@SYNTAX=DAVERAGE(database,field,criteria)\n"

           "@DESCRIPTION="
           "DAVERAGE function returns the average of the values in a list "
	   "or database that match conditions specified. "
	   "\n"
498
	   DB_ARGUMENT_HELP
499
	   "\n"
500
501
502
503
           "DAVERAGE(A1:C7, \"Salary\", A9:A11) equals 42296.3333.\n"
	   "DAVERAGE(A1:C7, \"Age\", A9:A11) equals 39.\n"
           "DAVERAGE(A1:C7, \"Salary\", A9:B11) equals 40782.5.\n"
	   "DAVERAGE(A1:C7, \"Age\", A9:B11) equals 36.\n"
504
	   "\n"
505
506
507
508
           "@SEEALSO=DCOUNT")
};

static Value *
509
gnumeric_daverage (FunctionEvalInfo *ei, Value **argv)
510
511
{
        Value       *database, *criteria;
512
	Sheet       *sheet;
513
514
515
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
Jody Goldberg's avatar
Jody Goldberg committed
516
	gnum_float     sum;
517
518
519
520
521
	int         count;

	database = argv[0];
	criteria = argv[2];

522
	field = find_column_of_field (ei->pos, database, argv[1]);
523
	if (field < 0)
524
		return value_new_error (ei->pos, gnumeric_err_NUM);
525

526
	criterias = parse_database_criteria (ei->pos, database, criteria);
527
	if (criterias == NULL)
528
		return value_new_error (ei->pos, gnumeric_err_NUM);
529

530
	sheet = eval_sheet (database->v_range.cell.a.sheet,
531
532
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
533
534
535
536
537
538
539
540

	current = cells;
	count = 0;
	sum = 0;

	while (current != NULL) {
	        Cell *cell = current->data;

Michael Meeks's avatar
Michael Meeks committed
541
542
543
544
		if (VALUE_IS_NUMBER(cell->value)) {
			count++;
			sum += value_get_as_float (cell->value);
		}
545
546
547
548
549
550
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

551
552
553
554
	if ( count > 0 )
	        return value_new_float (sum / count);
	else
	        return value_new_error (ei->pos, gnumeric_err_NUM);
555
556
}

557
558
/***************************************************************************/

559
560
561
562
563
564
565
566
static char *help_dcount = {
        N_("@FUNCTION=DCOUNT\n"
           "@SYNTAX=DCOUNT(database,field,criteria)\n"

           "@DESCRIPTION="
           "DCOUNT function counts the cells that contain numbers in a "
	   "database that match conditions specified. "
	   "\n"
567
	   DB_ARGUMENT_HELP
568
	   "\n"
569
570
571
           "DCOUNT(A1:C7, \"Salary\", A9:A11) equals 3.\n"
           "DCOUNT(A1:C7, \"Salary\", A9:B11) equals 2.\n"
           "DCOUNT(A1:C7, \"Name\", A9:B11) equals 0.\n"
572
	   "\n"
573
574
575
576
           "@SEEALSO=DAVERAGE")
};

static Value *
577
gnumeric_dcount (FunctionEvalInfo *ei, Value **argv)
578
579
{
        Value       *database, *criteria;
580
	Sheet       *sheet;
581
582
583
584
585
586
587
588
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	int         count;

	database = argv[0];
	criteria = argv[2];

589
	field = find_column_of_field (ei->pos, database, argv[1]);
590
	if (field < 0)
591
		return value_new_error (ei->pos, gnumeric_err_NUM);
592

593
	criterias = parse_database_criteria (ei->pos, database, criteria);
594
	if (criterias == NULL)
595
		return value_new_error (ei->pos, gnumeric_err_NUM);
596

597
	sheet = eval_sheet (database->v_range.cell.a.sheet,
598
599
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
600
601
602
603

	current = cells;
	count = 0;

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
604
605
606
607
608
609
610
611
612
613
614
	while (current != NULL) {
	        Cell *cell = current->data;

		if (VALUE_IS_NUMBER(cell->value))
		        count++;
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
615
        return value_new_int (count);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
616
617
}

618
619
/***************************************************************************/

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
620
621
622
623
624
625
626
627
static char *help_dcounta = {
        N_("@FUNCTION=DCOUNTA\n"
           "@SYNTAX=DCOUNTA(database,field,criteria)\n"

           "@DESCRIPTION="
           "DCOUNTA function counts the cells that contain data in a "
	   "database that match conditions specified. "
	   "\n"
628
	   DB_ARGUMENT_HELP
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
629
	   "\n"
630
631
632
           "DCOUNTA(A1:C7, \"Salary\", A9:A11) equals 3.\n"
           "DCOUNTA(A1:C7, \"Salary\", A9:B11) equals 2.\n"
           "DCOUNTA(A1:C7, \"Name\", A9:B11) equals 2.\n"
633
	   "\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
634
635
636
637
           "@SEEALSO=DCOUNT")
};

static Value *
638
gnumeric_dcounta (FunctionEvalInfo *ei, Value **argv)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
639
640
{
        Value       *database, *criteria;
641
	Sheet       *sheet;
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
642
643
644
645
646
647
648
649
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	int         count;

	database = argv[0];
	criteria = argv[2];

650
	field = find_column_of_field (ei->pos, database, argv[1]);
651
	if (field < 0)
652
		return value_new_error (ei->pos, gnumeric_err_NUM);
653

654
	criterias = parse_database_criteria (ei->pos, database, criteria);
655
	if (criterias == NULL)
656
		return value_new_error (ei->pos, gnumeric_err_NUM);
657

658
	sheet = eval_sheet (database->v_range.cell.a.sheet,
659
660
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
661
662
663
664

	current = cells;
	count = 0;

665
666
667
668
669
670
671
672
	while (current != NULL) {
	        count++;
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
673
        return value_new_int (count);
674
675
}

676
677
/***************************************************************************/

678
679
680
681
682
683
684
685
static char *help_dget = {
        N_("@FUNCTION=DGET\n"
           "@SYNTAX=DGET(database,field,criteria)\n"

           "@DESCRIPTION="
           "DGET function returns a single value from a column that "
	   "match conditions specified. "
	   "\n"
686
	   DB_ARGUMENT_HELP
687
	   "\n"
688
689
           "DGET(A1:C7, \"Salary\", A9:A10) equals 34323.\n"
           "DGET(A1:C7, \"Name\", A9:A10) equals \"Clark\".\n"
690
691
692
693
694
695
696
697
698
699
           "\n"
	   "If none of the items match the conditions, DGET returns #VALUE! "
	   "error. "
	   "If more than one items match the conditions, DGET returns #NUM! "
	   "error. "
	   "\n"
           "@SEEALSO=DCOUNT")
};

static Value *
700
gnumeric_dget (FunctionEvalInfo *ei, Value **argv)
701
702
{
        Value       *database, *criteria;
703
	Sheet       *sheet;
704
705
706
707
708
709
710
711
712
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	Cell        *cell = NULL;
	int         count;

	database = argv[0];
	criteria = argv[2];

713
	field = find_column_of_field (ei->pos, database, argv[1]);
714
	if (field < 0)
715
		return value_new_error (ei->pos, gnumeric_err_NUM);
716

717
	criterias = parse_database_criteria (ei->pos, database, criteria);
718
	if (criterias == NULL)
719
		return value_new_error (ei->pos, gnumeric_err_NUM);
720

721
	sheet = eval_sheet (database->v_range.cell.a.sheet,
722
723
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
724
725
726
727
728
729
730
731
732
733
734
735
736

	current = cells;
	count = 0;

	while (current != NULL) {
	        cell = current->data;
	        count++;
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

737
	if (count == 0)
738
		return value_new_error (ei->pos, gnumeric_err_VALUE);
739
740

	if (count > 1)
741
		return value_new_error (ei->pos, gnumeric_err_NUM);
742

743
        return value_duplicate (cell->value);
744
745
746
747
748
749
750
751
752
753
}

static char *help_dmax = {
        N_("@FUNCTION=DMAX\n"
           "@SYNTAX=DMAX(database,field,criteria)\n"

           "@DESCRIPTION="
           "DMAX function returns the largest number in a column that "
	   "match conditions specified. "
	   "\n"
754
	   DB_ARGUMENT_HELP
755
	   "\n"
756
757
758
           "DMAX(A1:C7, \"Salary\", A9:A11) equals 47242.\n"
           "DMAX(A1:C7, \"Age\", A9:A11) equals 45.\n"
           "DMAX(A1:C7, \"Age\", A9:B11) equals 43.\n"
759
	   "\n"
760
761
762
           "@SEEALSO=DMIN")
};

763
764
/***************************************************************************/

765
static Value *
766
gnumeric_dmax (FunctionEvalInfo *ei, Value **argv)
767
768
{
        Value       *database, *criteria;
769
	Sheet       *sheet;
770
771
772
773
	GSList      *criterias;
	GSList      *cells, *current;
	Cell        *cell;
	int         field;
Jody Goldberg's avatar
Jody Goldberg committed
774
	gnum_float     max;
775
776
777
778

	database = argv[0];
	criteria = argv[2];

779
	field = find_column_of_field (ei->pos, database, argv[1]);
780
	if (field < 0)
781
		return value_new_error (ei->pos, gnumeric_err_NUM);
782

783
	criterias = parse_database_criteria (ei->pos, database, criteria);
784
	if (criterias == NULL)
785
		return value_new_error (ei->pos, gnumeric_err_NUM);
786

787
	sheet = eval_sheet (database->v_range.cell.a.sheet,
788
789
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
790
	if (cells == NULL)
791
		return value_new_error (ei->pos, gnumeric_err_NUM);
792

793
794
	current = cells;
	cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
795
	max = value_get_as_float (cell->value);
796
797

	while (current != NULL) {
Jody Goldberg's avatar
Jody Goldberg committed
798
	        gnum_float v;
799
800

	        cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
801
802
803
804
805
		if (VALUE_IS_NUMBER(cell->value)) {
			v = value_get_as_float (cell->value);
			if (max < v)
				max = v;
		}
806
807
808
809
810
811
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
812
        return value_new_float (max);
813
814
}

815
816
/***************************************************************************/

817
818
819
820
821
822
823
824
static char *help_dmin = {
        N_("@FUNCTION=DMIN\n"
           "@SYNTAX=DMIN(database,field,criteria)\n"

           "@DESCRIPTION="
           "DMIN function returns the smallest number in a column that "
	   "match conditions specified. "
	   "\n"
825
	   DB_ARGUMENT_HELP
826
	   "\n"
827
828
           "DMIN(A1:C7, \"Salary\", A9:B11) equals 34323.\n"
           "DMIN(A1:C7, \"Age\", A9:B11) equals 29.\n"
829
	   "\n"
830
831
832
833
           "@SEEALSO=DMAX")
};

static Value *
834
gnumeric_dmin (FunctionEvalInfo *ei, Value **argv)
835
836
{
        Value       *database, *criteria;
837
	Sheet       *sheet;
838
839
840
841
	GSList      *criterias;
	GSList      *cells, *current;
	Cell        *cell;
	int         field;
Jody Goldberg's avatar
Jody Goldberg committed
842
	gnum_float     min;
843
844
845
846

	database = argv[0];
	criteria = argv[2];

847
	field = find_column_of_field (ei->pos, database, argv[1]);
848
	if (field < 0)
849
		return value_new_error (ei->pos, gnumeric_err_NUM);
850

851
	criterias = parse_database_criteria (ei->pos, database, criteria);
852
	if (criterias == NULL)
853
		return value_new_error (ei->pos, gnumeric_err_NUM);
854

855
	sheet = eval_sheet (database->v_range.cell.a.sheet,
856
857
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
858
859
	if (cells == NULL) {
		free_criterias (criterias);
860
		return value_new_error (ei->pos, gnumeric_err_NUM);
861
	}
862

863
864
	current = cells;
	cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
865
	min = value_get_as_float (cell->value);
866
867

	while (current != NULL) {
Jody Goldberg's avatar
Jody Goldberg committed
868
	        gnum_float v;
869
870

	        cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
871
872
873
874
875
		if (VALUE_IS_NUMBER(cell->value)) {
			v = value_get_as_float (cell->value);
			if (min > v)
				min = v;
		}
876
877
878
879
880
881
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
882
        return value_new_float (min);
883
884
}

885
886
/***************************************************************************/

887
888
889
890
891
892
893
894
static char *help_dproduct = {
        N_("@FUNCTION=DPRODUCT\n"
           "@SYNTAX=DPRODUCT(database,field,criteria)\n"

           "@DESCRIPTION="
           "DPRODUCT function returns the product of numbers in a column "
	   "that match conditions specified. "
	   "\n"
895
	   DB_ARGUMENT_HELP
896
	   "\n"
897
           "DPRODUCT(A1:C7, \"Age\", A9:B11) equals 1247.\n"
898
	   "\n"
899
900
901
902
           "@SEEALSO=DSUM")
};

static Value *
903
gnumeric_dproduct (FunctionEvalInfo *ei, Value **argv)
904
905
{
        Value       *database, *criteria;
906
	Sheet       *sheet;
907
908
909
910
	GSList      *criterias;
	GSList      *cells, *current;
	Cell        *cell;
	int         field;
Jody Goldberg's avatar
Jody Goldberg committed
911
	gnum_float     product;
912
913
914
915

	database = argv[0];
	criteria = argv[2];

916
	field = find_column_of_field (ei->pos, database, argv[1]);
917
	if (field < 0)
918
		return value_new_error (ei->pos, gnumeric_err_NUM);
919

920
	criterias = parse_database_criteria (ei->pos, database, criteria);
921
	if (criterias == NULL)
922
		return value_new_error (ei->pos, gnumeric_err_NUM);
923

924
	sheet = eval_sheet (database->v_range.cell.a.sheet,
925
926
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
927

928
929
	if (cells == NULL) {
		free_criterias (criterias);
930
		return value_new_error (ei->pos, gnumeric_err_NUM);
931
	}
932

933
934
935
936
937
	current = cells;
	product = 1;
	cell = current->data;

	while (current != NULL) {
Jody Goldberg's avatar
Jody Goldberg committed
938
	        gnum_float v;
939
940

	        cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
941
		v = value_get_as_float (cell->value);
942
943
944
945
946
947
948
		product *= v;
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
949
        return value_new_float (product);
950
951
}

952
953
/***************************************************************************/

954
955
956
957
958
959
960
961
962
static char *help_dstdev = {
        N_("@FUNCTION=DSTDEV\n"
           "@SYNTAX=DSTDEV(database,field,criteria)\n"

           "@DESCRIPTION="
           "DSTDEV function returns the estimate of the standard deviation "
	   "of a population based on a sample. The populations consists of "
	   "numbers that match conditions specified. "
	   "\n"
963
	   DB_ARGUMENT_HELP
964
	   "\n"
965
966
           "DSTDEV(A1:C7, \"Age\", A9:B11) equals 9.89949.\n"
           "DSTDEV(A1:C7, \"Salary\", A9:B11) equals 9135.112506.\n"
967
	   "\n"
968
969
970
971
           "@SEEALSO=DSTDEVP")
};

static Value *
972
gnumeric_dstdev (FunctionEvalInfo *ei, Value **argv)
973
974
{
        Value          *database, *criteria;
975
	Sheet          *sheet;
976
977
978
979
980
981
982
983
	GSList         *criterias;
	GSList         *cells, *current;
	int            field;
	stat_closure_t p;

	database = argv[0];
	criteria = argv[2];

984
	field = find_column_of_field (ei->pos, database, argv[1]);
985
	if (field < 0)
986
		return value_new_error (ei->pos, gnumeric_err_NUM);
987

988
	criterias = parse_database_criteria (ei->pos, database, criteria);
989
	if (criterias == NULL)
990
		return value_new_error (ei->pos, gnumeric_err_NUM);
991

992
	sheet = eval_sheet (database->v_range.cell.a.sheet,
993
994
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
995
	if (cells == NULL)
996
		return value_new_error (ei->pos, gnumeric_err_NUM);
997

998
999
1000
1001
	current = cells;
	setup_stat_closure (&p);

	while (current != NULL) {
1002
1003
1004
1005
	        Cell *cell = current->data;

		/* FIXME : What about errors ? */
		callback_function_stat (NULL, cell->value, &p);
1006
1007
1008
1009
1010
1011
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

1012
	if (p.N - 1 == 0)
1013
		return value_new_error (ei->pos, gnumeric_err_NUM);
1014

Michael Meeks's avatar
Michael Meeks committed
1015
        return value_new_float (sqrt(p.Q / (p.N - 1)));
1016
1017
}

1018
1019
/***************************************************************************/

1020
1021
1022
1023
1024
1025
1026
1027
1028
static char *help_dstdevp = {
        N_("@FUNCTION=DSTDEVP\n"
           "@SYNTAX=DSTDEVP(database,field,criteria)\n"

           "@DESCRIPTION="
           "DSTDEVP function returns the standard deviation of a population "
	   "based on the entire populations. The populations consists of "
	   "numbers that match conditions specified. "
	   "\n"
1029
	   DB_ARGUMENT_HELP
1030
	   "\n"
1031
1032
           "DSTDEVP(A1:C7, \"Age\", A9:B11) equals 7.\n"
           "DSTDEVP(A1:C7, \"Salary\", A9:B11) equals 6459.5.\n"
1033
	   "\n"
1034
1035
1036
1037
           "@SEEALSO=DSTDEV")
};

static Value *
1038
gnumeric_dstdevp (FunctionEvalInfo *ei, Value **argv)
1039
1040
{
        Value          *database, *criteria;
1041
	Sheet          *sheet;
1042
1043
1044
1045
1046
1047
1048
1049
	GSList         *criterias;
	GSList         *cells, *current;
	int            field;
	stat_closure_t p;

	database = argv[0];
	criteria = argv[2];

1050
	field = find_column_of_field (ei->pos, database, argv[1]);
1051
	if (field < 0)
1052
		return value_new_error (ei->pos, gnumeric_err_NUM);
1053

1054
	criterias = parse_database_criteria (ei->pos, database, criteria);
1055
	if (criterias == NULL)
1056
		return value_new_error (ei->pos, gnumeric_err_NUM);
1057

1058
	sheet = eval_sheet (database->v_range.cell.a.sheet,
1059
1060
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
1061
1062
	if (cells == NULL) {
		free_criterias (criterias);
1063
		return value_new_error (ei->pos, gnumeric_err_NUM);