fn-database.c 35.4 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"
Jody Goldberg's avatar
Jody Goldberg committed
13
#include "str.h"
14
#include "cell.h"
Jody Goldberg's avatar
Jody Goldberg committed
15
#include "sheet.h"
16
17
18
19

/* Type definitions */

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


/* Callback functions */

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

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

Jody Goldberg's avatar
Jody Goldberg committed
57
58
gboolean
criteria_test_less (Value const *x, Value const *y)
59
{
Morten Welinder's avatar
Morten Welinder committed
60
        if (VALUE_IS_NUMBER (x) && VALUE_IS_NUMBER (y))
Michael Meeks's avatar
Michael Meeks committed
61
	        if (value_get_as_float (x) < value_get_as_float (y))
62
63
64
		        return 1;
		else
		        return 0;
65
66
67
68
	else
	        return 0;
}

Jody Goldberg's avatar
Jody Goldberg committed
69
70
gboolean
criteria_test_greater (Value const *x, Value const *y)
71
{
Morten Welinder's avatar
Morten Welinder committed
72
        if (VALUE_IS_NUMBER (x) && VALUE_IS_NUMBER (y))
Michael Meeks's avatar
Michael Meeks committed
73
	        if (value_get_as_float (x) > value_get_as_float (y))
74
75
76
		        return 1;
		else
		        return 0;
77
78
79
80
	else
	        return 0;
}

Jody Goldberg's avatar
Jody Goldberg committed
81
82
gboolean
criteria_test_less_or_equal (Value const *x, Value const *y)
83
{
Morten Welinder's avatar
Morten Welinder committed
84
        if (VALUE_IS_NUMBER (x) && VALUE_IS_NUMBER (y))
Michael Meeks's avatar
Michael Meeks committed
85
	        if (value_get_as_float (x) <= value_get_as_float (y))
86
87
88
		        return 1;
		else
		        return 0;
89
90
91
92
	else
	        return 0;
}

Jody Goldberg's avatar
Jody Goldberg committed
93
94
gboolean
criteria_test_greater_or_equal (Value const *x, Value const *y)
95
{
Morten Welinder's avatar
Morten Welinder committed
96
        if (VALUE_IS_NUMBER (x) && VALUE_IS_NUMBER (y))
Michael Meeks's avatar
Michael Meeks committed
97
	        if (value_get_as_float (x) >= value_get_as_float (y))
98
99
100
		        return 1;
		else
		        return 0;
101
102
103
104
105
	else
	        return 0;
}


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

117
118
	offset = database->v_range.cell.b.col -
	  database->v_range.cell.a.col;
119

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

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

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

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

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

Morten Welinder's avatar
Morten Welinder committed
139
	        cell = sheet_cell_get (sheet, n, row);
140
141
		if (cell == NULL)
		        continue;
142

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

152
	g_free (field_name);
153
154
155
156
157
	return column;
}

/* Frees the allocated memory.
 */
158
void
Morten Welinder's avatar
Morten Welinder committed
159
free_criterias (GSList *criterias)
160
161
162
163
{
        GSList *list = criterias;

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

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

Morten Welinder's avatar
Morten Welinder committed
173
		g_slist_free (criteria->conditions);
174
		g_free (criteria);
175
176
	        criterias = criterias->next;
	}
Morten Welinder's avatar
Morten Welinder committed
177
	g_slist_free (list);
178
179
}

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

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

Morten Welinder's avatar
Morten Welinder committed
211
	*test_value = format_match (criteria + len, NULL, NULL);
212
213
}

214
215
216
217

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

226
        int i, j;
227

Morten Welinder's avatar
Morten Welinder committed
228
229
	for (i = b_row; i <= e_row; i++) {
	        new_criteria = g_new (database_criteria_t, 1);
230
		conditions = NULL;
231

Morten Welinder's avatar
Morten Welinder committed
232
233
		for (j = b_col; j <= e_col; j++) {
		        cell = sheet_cell_get (sheet, j, i);
234
			if (cell == NULL || cell->value == NULL)
235
236
			        continue;

Morten Welinder's avatar
Morten Welinder committed
237
			cond = g_new (func_criteria_t, 1);
238
239

			/* Equality condition (in number format) */
Morten Welinder's avatar
Morten Welinder committed
240
			if (VALUE_IS_NUMBER (cell->value)) {
241
242
243
			        cond->x = value_duplicate (cell->value);
				cond->fun =
				  (criteria_test_fun_t) criteria_test_equal;
Morten Welinder's avatar
Morten Welinder committed
244
245
				cond->column = field_ind[j - b_col];
				conditions = g_slist_append (conditions, cond);
246
				continue;
247
			}
248
249

			/* Other conditions (in string format) */
250
			cell_str = cell_get_rendered_text (cell);
Morten Welinder's avatar
Morten Welinder committed
251
			parse_criteria (cell_str, &cond->fun, &cond->x);
252
			if (field_ind != NULL)
Morten Welinder's avatar
Morten Welinder committed
253
			        cond->column = field_ind[j - b_col];
254
			else
Morten Welinder's avatar
Morten Welinder committed
255
			        cond->column = j - b_col;
256
			g_free (cell_str);
257

Morten Welinder's avatar
Morten Welinder committed
258
			conditions = g_slist_append (conditions, cond);
259
		}
260

261
		new_criteria->conditions = conditions;
Morten Welinder's avatar
Morten Welinder committed
262
		criterias = g_slist_append (criterias, new_criteria);
263
	}
264

265
266
267
268
269
270
	return criterias;
}

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

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

282
283
284
285
286
	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;
287

Morten Welinder's avatar
Morten Welinder committed
288
	field_ind = g_new (int, (e_col - b_col + 1));
289
290

	/* Find the index numbers for the columns of criterias */
Morten Welinder's avatar
Morten Welinder committed
291
292
	for (i = b_col; i <= e_col; i++) {
	        cell = sheet_cell_get (sheet, i, b_row);
293
294
		if (cell == NULL || cell->value == NULL)
		        continue;
Morten Welinder's avatar
Morten Welinder committed
295
		field_ind[i - b_col] =
296
		        find_column_of_field (ep, database, cell->value);
Morten Welinder's avatar
Morten Welinder committed
297
298
		if (field_ind[i - b_col] == -1) {
		        g_free (field_ind);
299
300
301
302
			return NULL;
		}
	}

Morten Welinder's avatar
Morten Welinder committed
303
304
	criterias = parse_criteria_range (sheet, b_col, b_row + 1,
					  e_col, e_row,
305
306
					  field_ind);

Morten Welinder's avatar
Morten Welinder committed
307
	g_free (field_ind);
308
309
310
	return criterias;
}

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

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

Jody Goldberg's avatar
fix.    
Jody Goldberg committed
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
	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) {
Morten Welinder's avatar
Morten Welinder committed
346
					if (!cond->fun (tmp->value, cond->x)) {
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
347
348
349
350
351
352
353
354
355
356
357
						add_flag = FALSE;
						break;
					}
				}
			}

			if (add_flag)
				break;
		}
		if (add_flag)
			cells = g_slist_append (cells, cell);
358
	}
359

360
361
362
	return cells;
}

363
364
365
366

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

Morten Welinder's avatar
Morten Welinder committed
375
	for (row = first_row; row <= last_row; row++) {
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
376
377
378
379
380
		Cell   *test_cell;

		current = criterias;
		add_flag = 1;
		for (current = criterias; current != NULL;
Morten Welinder's avatar
Morten Welinder committed
381
		     current = current->next) {
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
382
383
384
385
386
387
388
389
390
391
			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 =
Morten Welinder's avatar
Morten Welinder committed
392
					sheet_cell_get (sheet,
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
393
394
395
396
397
						       first_col + cond->column, row);
				if (test_cell == NULL ||
				    test_cell->value == NULL)
					continue;

Morten Welinder's avatar
Morten Welinder committed
398
				if (!cond->fun (test_cell->value, cond->x)) {
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
					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;

Morten Welinder's avatar
Morten Welinder committed
417
				for (c = rows; c != NULL; c = c->next) {
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
418
					trow = *((gint *) c->data);
Morten Welinder's avatar
Morten Welinder committed
419
					for (i = first_col; i <= last_col; i++) {
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
420
						test_cell =
Morten Welinder's avatar
Morten Welinder committed
421
							sheet_cell_get (sheet, i, trow);
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
422
						cell =
Morten Welinder's avatar
Morten Welinder committed
423
							sheet_cell_get (sheet, i, row);
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
424
425
426
427
428
429
430
431
432
433
434
435
						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;
Morten Welinder's avatar
Morten Welinder committed
436
			rows = g_slist_append (rows, (gpointer) p);
Jody Goldberg's avatar
fix.    
Jody Goldberg committed
437
438
439
filter_row:
			;
		}
440
441
442
443
444
	}

	return rows;
}

445
446
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
#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"


486
/***************************************************************************/
487
488
489
490
491
492
493
494
495

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"
496
	   DB_ARGUMENT_HELP
497
	   "\n"
498
499
500
501
           "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"
502
	   "\n"
503
504
505
506
           "@SEEALSO=DCOUNT")
};

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

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

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

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

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

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

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

Morten Welinder's avatar
Morten Welinder committed
539
		if (VALUE_IS_NUMBER (cell->value)) {
Michael Meeks's avatar
Michael Meeks committed
540
541
542
			count++;
			sum += value_get_as_float (cell->value);
		}
Morten Welinder's avatar
Morten Welinder committed
543
		current = g_slist_next (current);
544
545
	}

Morten Welinder's avatar
Morten Welinder committed
546
547
	g_slist_free (cells);
	free_criterias (criterias);
548

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

555
556
/***************************************************************************/

557
558
559
560
561
562
563
564
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"
565
	   DB_ARGUMENT_HELP
566
	   "\n"
567
568
569
           "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"
570
	   "\n"
571
572
573
574
           "@SEEALSO=DAVERAGE")
};

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

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

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

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

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

	current = cells;
	count = 0;

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
602
603
604
	while (current != NULL) {
	        Cell *cell = current->data;

Morten Welinder's avatar
Morten Welinder committed
605
		if (VALUE_IS_NUMBER (cell->value))
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
606
		        count++;
Morten Welinder's avatar
Morten Welinder committed
607
		current = g_slist_next (current);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
608
609
	}

Morten Welinder's avatar
Morten Welinder committed
610
611
	g_slist_free (cells);
	free_criterias (criterias);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
612

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

616
617
/***************************************************************************/

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
618
619
620
621
622
623
624
625
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"
626
	   DB_ARGUMENT_HELP
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
627
	   "\n"
628
629
630
           "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"
631
	   "\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
632
633
634
635
           "@SEEALSO=DCOUNT")
};

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

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

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

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

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

	current = cells;
	count = 0;

663
664
	while (current != NULL) {
	        count++;
Morten Welinder's avatar
Morten Welinder committed
665
		current = g_slist_next (current);
666
667
	}

Morten Welinder's avatar
Morten Welinder committed
668
669
	g_slist_free (cells);
	free_criterias (criterias);
670

Michael Meeks's avatar
Michael Meeks committed
671
        return value_new_int (count);
672
673
}

674
675
/***************************************************************************/

676
677
678
679
680
681
682
683
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"
684
	   DB_ARGUMENT_HELP
685
	   "\n"
686
687
           "DGET(A1:C7, \"Salary\", A9:A10) equals 34323.\n"
           "DGET(A1:C7, \"Name\", A9:A10) equals \"Clark\".\n"
688
689
690
691
692
693
694
695
696
697
           "\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 *
698
gnumeric_dget (FunctionEvalInfo *ei, Value **argv)
699
700
{
        Value       *database, *criteria;
701
	Sheet       *sheet;
702
703
704
705
706
707
708
709
710
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	Cell        *cell = NULL;
	int         count;

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

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

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

719
	sheet = eval_sheet (database->v_range.cell.a.sheet,
720
721
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
722
723
724
725
726
727
728

	current = cells;
	count = 0;

	while (current != NULL) {
	        cell = current->data;
	        count++;
Morten Welinder's avatar
Morten Welinder committed
729
		current = g_slist_next (current);
730
731
	}

Morten Welinder's avatar
Morten Welinder committed
732
733
	g_slist_free (cells);
	free_criterias (criterias);
734

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

	if (count > 1)
739
		return value_new_error (ei->pos, gnumeric_err_NUM);
740

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

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"
752
	   DB_ARGUMENT_HELP
753
	   "\n"
754
755
756
           "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"
757
	   "\n"
758
759
760
           "@SEEALSO=DMIN")
};

761
762
/***************************************************************************/

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

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

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

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

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

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

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

	        cell = current->data;
Morten Welinder's avatar
Morten Welinder committed
799
		if (VALUE_IS_NUMBER (cell->value)) {
Michael Meeks's avatar
Michael Meeks committed
800
801
802
803
			v = value_get_as_float (cell->value);
			if (max < v)
				max = v;
		}
Morten Welinder's avatar
Morten Welinder committed
804
		current = g_slist_next (current);
805
806
	}

Morten Welinder's avatar
Morten Welinder committed
807
808
	g_slist_free (cells);
	free_criterias (criterias);
809

Michael Meeks's avatar
Michael Meeks committed
810
        return value_new_float (max);
811
812
}

813
814
/***************************************************************************/

815
816
817
818
819
820
821
822
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"
823
	   DB_ARGUMENT_HELP
824
	   "\n"
825
826
           "DMIN(A1:C7, \"Salary\", A9:B11) equals 34323.\n"
           "DMIN(A1:C7, \"Age\", A9:B11) equals 29.\n"
827
	   "\n"
828
829
830
831
           "@SEEALSO=DMAX")
};

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

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

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

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

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

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

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

	        cell = current->data;
Morten Welinder's avatar
Morten Welinder committed
869
		if (VALUE_IS_NUMBER (cell->value)) {
Michael Meeks's avatar
Michael Meeks committed
870
871
872
873
			v = value_get_as_float (cell->value);
			if (min > v)
				min = v;
		}
Morten Welinder's avatar
Morten Welinder committed
874
		current = g_slist_next (current);
875
876
	}

Morten Welinder's avatar
Morten Welinder committed
877
878
	g_slist_free (cells);
	free_criterias (criterias);
879

Michael Meeks's avatar
Michael Meeks committed
880
        return value_new_float (min);
881
882
}

883
884
/***************************************************************************/

885
886
887
888
889
890
891
892
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"
893
	   DB_ARGUMENT_HELP
894
	   "\n"
895
           "DPRODUCT(A1:C7, \"Age\", A9:B11) equals 1247.\n"
896
	   "\n"
897
898
899
900
           "@SEEALSO=DSUM")
};

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

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

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

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

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

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

931
932
933
934
935
	current = cells;
	product = 1;
	cell = current->data;

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

	        cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
939
		v = value_get_as_float (cell->value);
940
		product *= v;
Morten Welinder's avatar
Morten Welinder committed
941
		current = g_slist_next (current);
942
943
	}

Morten Welinder's avatar
Morten Welinder committed
944
945
	g_slist_free (cells);
	free_criterias (criterias);
946

Michael Meeks's avatar
Michael Meeks committed
947
        return value_new_float (product);
948
949
}

950
951
/***************************************************************************/

952
953
954
955
956
957
958
959
960
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"
961
	   DB_ARGUMENT_HELP
962
	   "\n"
963
964
           "DSTDEV(A1:C7, \"Age\", A9:B11) equals 9.89949.\n"
           "DSTDEV(A1:C7, \"Salary\", A9:B11) equals 9135.112506.\n"
965
	   "\n"
966
967
968
969
           "@SEEALSO=DSTDEVP")
};

static Value *