fn-database.c 35.1 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
11
12
13
14
15
16
17
18
19
20
21
22
23
#include "utils.h"
#include "func.h"
#include "sheet.h"

/* Type definitions */

typedef struct {
        int    column;
        GSList *conditions;
} database_criteria_t;


/* Callback functions */

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

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

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

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

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

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


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

	offset = database->v.cell_range.cell_b.col -
	  database->v.cell_range.cell_a.col;

	if (field->type == VALUE_INTEGER) 
	        return value_get_as_int (field) + offset - 1;

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

123
	sheet = eval_sheet (database->v.cell_range.cell_a.sheet, ep->sheet);
Michael Meeks's avatar
Michael Meeks committed
124
	field_name = value_get_as_string (field);
125
126
127
128
129
130
131
	column = -1;

	/* find the column that is labeled with `field_name' */
	begin_col = database->v.cell_range.cell_a.col;
	end_col = database->v.cell_range.cell_b.col;
	row = database->v.cell_range.cell_a.row;

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

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

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

149
	g_free (field_name);
150
151
152
153
154
155
156
157
158
159
160
	return column;
}

/* Frees the allocated memory.
 */
static void
free_criterias(GSList *criterias)
{
        GSList *list = criterias;

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

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

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

177
void
178
parse_criteria(const char *criteria, criteria_test_fun_t *fun, Value **test_value)
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
{
	char    *p;
	float_t tmp;
	int     len;

        if (strncmp(criteria, "<=", 2) == 0) {
	        *fun = (criteria_test_fun_t) 
		  criteria_test_less_or_equal;
		len=2;
	} else if (strncmp(criteria, ">=", 2) == 0) {
	        *fun = (criteria_test_fun_t)
		  criteria_test_greater_or_equal;
		len=2;
	} else if (strncmp(criteria, "<>", 2) == 0) {
	        *fun = (criteria_test_fun_t)
		  criteria_test_unequal;
		len=2;
	} else if (*criteria == '<') {
	        *fun = (criteria_test_fun_t)
		  criteria_test_less;
		len=1;
	} else if (*criteria == '=') {
	        *fun = (criteria_test_fun_t)
		  criteria_test_equal;
		len=1;
	} else if (*criteria == '>') {
	        *fun = (criteria_test_fun_t)
		  criteria_test_greater;
		len=1;
	} else {
	        *fun = (criteria_test_fun_t)
		  criteria_test_equal;
		len=0;
	}
	
	tmp = strtod(criteria+len, &p);

	if (p == criteria+len || *p != '\0')
Michael Meeks's avatar
Michael Meeks committed
217
	        *test_value = value_new_string (criteria+len);
218
	else
Michael Meeks's avatar
Michael Meeks committed
219
	        *test_value = value_new_float (tmp);
220
221
222

}

223
224
225
/* Parses the criteria cell range.
 */
static GSList *
226
parse_database_criteria (const EvalPosition *ep, Value *database, Value *criteria)
227
228
229
230
231
232
233
234
235
236
{
	Sheet               *sheet;
	database_criteria_t *new_criteria;
	GSList              *criterias;
	GSList              *conditions;
	Cell                *cell;
        int   i, j;
	int   b_col, b_row, e_col, e_row;
	int   field_ind;

237
	sheet = eval_sheet (database->v.cell_range.cell_a.sheet, ep->sheet);
238
239
240
241
242
243
244
245
246
247
248
249
	b_col = criteria->v.cell_range.cell_a.col;
	b_row = criteria->v.cell_range.cell_a.row;
	e_col = criteria->v.cell_range.cell_b.col;
	e_row = criteria->v.cell_range.cell_b.row;

	conditions = NULL;
	criterias = NULL;

	for (i=b_col; i<=e_col; i++) {
	        cell = sheet_cell_get(sheet, i, b_row);
		if (cell == NULL || cell->value == NULL)
		        continue;
250
	        field_ind = find_column_of_field (ep, database, cell->value);
251
252
253
254
		if (field_ind == -1) {
		        free_criterias(criterias);
		        return NULL;
		}
255
		new_criteria = g_new(database_criteria_t, 1);
256
257
258
259
		new_criteria->column = field_ind;
		conditions = NULL;

	        for (j=b_row+1; j<=e_row; j++) {
260
		        func_criteria_t *cond;
261
262
263
264
265
			gchar       *cell_str;

			cell = sheet_cell_get(sheet, i, j);
			if (cell == NULL || cell->value == NULL)
			       continue;
266
			cond = g_new(func_criteria_t, 1);
267
			if (VALUE_IS_NUMBER(cell->value)) {
268
			       cond->x = value_duplicate (cell->value);
269
			       cond->fun =
270
				 (criteria_test_fun_t) criteria_test_equal;
271
272
273
274
			       conditions = g_slist_append(conditions, cond);
			       continue;
			}
			cell_str = cell_get_text(cell);
275
			parse_criteria(cell_str, &cond->fun, &cond->x);
276
			g_free (cell_str);
277
278
279
280
281
282

			conditions = g_slist_append(conditions, cond);
		}
		new_criteria->conditions = conditions;
		criterias = g_slist_append(criterias, new_criteria);
	}
283

284
285
286
287
288
289
	return criterias;
}

/* Finds the cells from the given column that match the criteria.
 */
static GSList *
290
find_cells_that_match (const EvalPosition *ep, Value *database, int field, GSList *criterias)
291
292
293
294
{
        Sheet  *sheet;
	GSList *current, *conditions, *cells;
        int    row, first_row, last_row, add_flag;
295
	sheet = eval_sheet (database->v.cell_range.cell_a.sheet, ep->sheet);
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
	last_row = database->v.cell_range.cell_b.row;
	cells = NULL;
	first_row = database->v.cell_range.cell_a.row + 1;

	for (row=first_row; row<=last_row; row++) {
	       Cell   *cell, *test_cell;

	       cell = sheet_cell_get(sheet, field, row);
	       if (cell == NULL || cell->value == NULL)
		       continue;
	       current = criterias;
	       add_flag = 0;
	       for (current = criterias; current != NULL;
		    current=current->next) {
		       database_criteria_t *current_criteria;

		       current_criteria = current->data;
		       test_cell = sheet_cell_get(sheet, 
						  current_criteria->column,
						  row);
		       if (test_cell == NULL || test_cell->value == NULL)
			       continue;
		       conditions = current_criteria->conditions;
		       add_flag = 0;
		       while (conditions != NULL) {
321
			       func_criteria_t *cond = conditions->data;
322

Michael Meeks's avatar
Michael Meeks committed
323
			       if (cond->fun (test_cell->value, cond->x)) {
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
				       add_flag = 1;
				       break;
			       }
			       conditions = conditions->next;
		       }
		       if (add_flag == 0)
			       break;
	       }
	       if (add_flag) {
		       cells = g_slist_append(cells, cell);
	       }
	}
	return cells;
}

339
/***************************************************************************/
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357

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"
	   "@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. "
	   "\n"
	   "@criteria is the range of cells which contains the specified "
358
	   "conditions. The first row of a @criteria should contain the labels "
359
360
361
	   "of the fields for which the criterias are for. Cells below the "
	   "label specify coditions, for example, ``>3'' or ``<9''. "
           "\n"
362
363
	   "@EXAMPLES=\n"
	   "\n"
364
365
366
367
           "@SEEALSO=DCOUNT")
};

static Value *
368
gnumeric_daverage (FunctionEvalInfo *ei, Value **argv)
369
370
371
372
373
374
375
376
377
378
379
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	float_t     sum;
	int         count;

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

380
	field = find_column_of_field (&ei->pos, database, argv[1]);
381
	if (field < 0)
382
		return value_new_error (&ei->pos, gnumeric_err_NUM);
383

384
	criterias = parse_database_criteria (&ei->pos, database, criteria);
385
	if (criterias == NULL)
386
		return value_new_error (&ei->pos, gnumeric_err_NUM);
387

388
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
389
390
391
392
393
394
395
396

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

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

Michael Meeks's avatar
Michael Meeks committed
397
398
399
400
		if (VALUE_IS_NUMBER(cell->value)) {
			count++;
			sum += value_get_as_float (cell->value);
		}
401
402
403
404
405
406
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
407
        return value_new_float (sum / count);
408
409
}

410
411
/***************************************************************************/

412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
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"
	   "@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. "
	   "\n"
	   "@criteria is the range of cells which contains the specified "
429
	   "conditions. The first row of a @criteria should contain the labels "
430
431
432
	   "of the fields for which the criterias are for. Cells below the "
	   "label specify coditions, for example, ``>3'' or ``<9''. "
           "\n"
433
434
	   "@EXAMPLES=\n"
	   "\n"
435
436
437
438
           "@SEEALSO=DAVERAGE")
};

static Value *
439
gnumeric_dcount (FunctionEvalInfo *ei, Value **argv)
440
441
442
443
444
445
446
447
448
449
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	int         count;

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

450
	field = find_column_of_field (&ei->pos, database, argv[1]);
451
	if (field < 0)
452
		return value_new_error (&ei->pos, gnumeric_err_NUM);
453

454
	criterias = parse_database_criteria (&ei->pos, database, criteria);
455
	if (criterias == NULL)
456
		return value_new_error (&ei->pos, gnumeric_err_NUM);
457

458
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
459
460
461
462

	current = cells;
	count = 0;

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
463
464
465
466
467
468
469
470
471
472
473
	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
474
        return value_new_int (count);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
475
476
}

477
478
/***************************************************************************/

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
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"
	   "@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. "
	   "\n"
	   "@criteria is the range of cells which contains the specified "
496
	   "conditions. The first row of a @criteria should contain the labels "
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
497
498
499
	   "of the fields for which the criterias are for. Cells below the "
	   "label specify coditions, for example, ``>3'' or ``<9''. "
           "\n"
500
501
	   "@EXAMPLES=\n"
	   "\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
502
503
504
505
           "@SEEALSO=DCOUNT")
};

static Value *
506
gnumeric_dcounta (FunctionEvalInfo *ei, Value **argv)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
507
508
509
510
511
512
513
514
515
516
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	int         count;

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

517
	field = find_column_of_field (&ei->pos, database, argv[1]);
518
	if (field < 0)
519
		return value_new_error (&ei->pos, gnumeric_err_NUM);
520

521
	criterias = parse_database_criteria (&ei->pos, database, criteria);
522
	if (criterias == NULL)
523
		return value_new_error (&ei->pos, gnumeric_err_NUM);
524

525
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
526
527
528
529

	current = cells;
	count = 0;

530
531
532
533
534
535
536
537
	while (current != NULL) {
	        count++;
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
538
        return value_new_int (count);
539
540
}

541
542
/***************************************************************************/

543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
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"
	   "@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. "
	   "\n"
	   "@criteria is the range of cells which contains the specified "
560
	   "conditions. The first row of a @criteria should contain the labels "
561
562
563
564
565
566
567
568
	   "of the fields for which the criterias are for. Cells below the "
	   "label specify coditions, for example, ``>3'' or ``<9''. "
           "\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"
569
570
	   "@EXAMPLES=\n"
	   "\n"
571
572
573
574
           "@SEEALSO=DCOUNT")
};

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

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

587
	field = find_column_of_field (&ei->pos, database, argv[1]);
588

589
	if (field < 0)
590
		return value_new_error (&ei->pos, gnumeric_err_NUM);
591

592
	criterias = parse_database_criteria (&ei->pos, database, criteria);
593

594
	if (criterias == NULL)
595
		return value_new_error (&ei->pos, gnumeric_err_NUM);
596

597
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
598
599
600
601
602
603
604
605
606
607
608
609
610

	current = cells;
	count = 0;

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

	g_slist_free(cells);
	free_criterias(criterias);

611
	if (count == 0)
612
		return value_new_error (&ei->pos, gnumeric_err_VALUE);
613
614

	if (count > 1)
615
		return value_new_error (&ei->pos, gnumeric_err_NUM);
616

Michael Meeks's avatar
Michael Meeks committed
617
        return value_new_float (value_get_as_float (cell->value));
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
}

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"
	   "@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. "
	   "\n"
	   "@criteria is the range of cells which contains the specified "
637
	   "conditions. The first row of a @criteria should contain the labels "
638
639
640
	   "of the fields for which the criterias are for. Cells below the "
	   "label specify coditions, for example, ``>3'' or ``<9''. "
           "\n"
641
642
	   "@EXAMPLES=\n"
	   "\n"
643
644
645
           "@SEEALSO=DMIN")
};

646
647
/***************************************************************************/

648
static Value *
649
gnumeric_dmax (FunctionEvalInfo *ei, Value **argv)
650
651
652
653
654
655
656
657
658
659
660
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	Cell        *cell;
	int         field;
	float_t     max;

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

661
	field = find_column_of_field (&ei->pos, database, argv[1]);
662
	if (field < 0)
663
		return value_new_error (&ei->pos, gnumeric_err_NUM);
664

665
	criterias = parse_database_criteria (&ei->pos, database, criteria);
666
	if (criterias == NULL)
667
		return value_new_error (&ei->pos, gnumeric_err_NUM);
668

669
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
670
	if (cells == NULL)
671
		return value_new_error (&ei->pos, gnumeric_err_NUM);
672

673
674
	current = cells;
	cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
675
	max = value_get_as_float (cell->value);
676
677
678
679
680

	while (current != NULL) {
	        float_t v;

	        cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
681
682
683
684
685
		if (VALUE_IS_NUMBER(cell->value)) {
			v = value_get_as_float (cell->value);
			if (max < v)
				max = v;
		}
686
687
688
689
690
691
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
692
        return value_new_float (max);
693
694
}

695
696
/***************************************************************************/

697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
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"
	   "@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. "
	   "\n"
	   "@criteria is the range of cells which contains the specified "
714
	   "conditions. The first row of a @criteria should contain the labels "
715
716
717
	   "of the fields for which the criterias are for. Cells below the "
	   "label specify coditions, for example, ``>3'' or ``<9''. "
           "\n"
718
719
	   "@EXAMPLES=\n"
	   "\n"
720
721
722
723
           "@SEEALSO=DMAX")
};

static Value *
724
gnumeric_dmin (FunctionEvalInfo *ei, Value **argv)
725
726
727
728
729
730
731
732
733
734
735
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	Cell        *cell;
	int         field;
	float_t     min;

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

736
	field = find_column_of_field (&ei->pos, database, argv[1]);
737
	if (field < 0)
738
		return value_new_error (&ei->pos, gnumeric_err_NUM);
739

740
	criterias = parse_database_criteria (&ei->pos, database, criteria);
741
	if (criterias == NULL)
742
		return value_new_error (&ei->pos, gnumeric_err_NUM);
743

744
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
745
746
	if (cells == NULL) {
		free_criterias (criterias);
747
		return value_new_error (&ei->pos, gnumeric_err_NUM);
748
	}
749

750
751
	current = cells;
	cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
752
	min = value_get_as_float (cell->value);
753
754
755
756
757

	while (current != NULL) {
	        float_t v;

	        cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
758
759
760
761
762
		if (VALUE_IS_NUMBER(cell->value)) {
			v = value_get_as_float (cell->value);
			if (min > v)
				min = v;
		}
763
764
765
766
767
768
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
769
        return value_new_float (min);
770
771
}

772
773
/***************************************************************************/

774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
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"
	   "@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. "
	   "\n"
	   "@criteria is the range of cells which contains the specified "
791
	   "conditions. The first row of a @criteria should contain the labels "
792
793
794
	   "of the fields for which the criterias are for. Cells below the "
	   "label specify coditions, for example, ``>3'' or ``<9''. "
           "\n"
795
796
	   "@EXAMPLES=\n"
	   "\n"
797
798
799
800
           "@SEEALSO=DSUM")
};

static Value *
801
gnumeric_dproduct (FunctionEvalInfo *ei, Value **argv)
802
803
804
805
806
807
808
809
810
811
812
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	Cell        *cell;
	int         field;
	float_t     product;

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

813
	field = find_column_of_field (&ei->pos, database, argv[1]);
814
	if (field < 0)
815
		return value_new_error (&ei->pos, gnumeric_err_NUM);
816

817
	criterias = parse_database_criteria (&ei->pos, database, criteria);
818
819

	if (criterias == NULL)
820
		return value_new_error (&ei->pos, gnumeric_err_NUM);
821

822
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
823

824
825
	if (cells == NULL) {
		free_criterias (criterias);
826
		return value_new_error (&ei->pos, gnumeric_err_NUM);
827
	}
828

829
830
831
832
833
834
835
836
	current = cells;
	product = 1;
	cell = current->data;

	while (current != NULL) {
	        float_t v;

	        cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
837
		v = value_get_as_float (cell->value);
838
839
840
841
842
843
844
		product *= v;
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
845
        return value_new_float (product);
846
847
}

848
849
/***************************************************************************/

850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
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"
	   "@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. "
	   "\n"
	   "@criteria is the range of cells which contains the specified "
868
	   "conditions. The first row of a @criteria should contain the labels "
869
870
871
	   "of the fields for which the criterias are for. Cells below the "
	   "label specify coditions, for example, ``>3'' or ``<9''. "
           "\n"
872
873
	   "@EXAMPLES=\n"
	   "\n"
874
875
876
877
           "@SEEALSO=DSTDEVP")
};

static Value *
878
gnumeric_dstdev (FunctionEvalInfo *ei, Value **argv)
879
880
881
882
883
884
885
886
887
888
{
        Value          *database, *criteria;
	GSList         *criterias;
	GSList         *cells, *current;
	int            field;
	stat_closure_t p;

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

889
	field = find_column_of_field (&ei->pos, database, argv[1]);
890
	if (field < 0)
891
		return value_new_error (&ei->pos, gnumeric_err_NUM);
892

893
	criterias = parse_database_criteria (&ei->pos, database, criteria);
894
	if (criterias == NULL)
895
		return value_new_error (&ei->pos, gnumeric_err_NUM);
896

897
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
898
	if (cells == NULL)
899
		return value_new_error (&ei->pos, gnumeric_err_NUM);
900

901
902
903
904
	current = cells;
	setup_stat_closure (&p);

	while (current != NULL) {
905
906
907
908
	        Cell *cell = current->data;

		/* FIXME : What about errors ? */
		callback_function_stat (NULL, cell->value, &p);
909
910
911
912
913
914
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

915
	if (p.N - 1 == 0)
916
		return value_new_error (&ei->pos, gnumeric_err_NUM);
917

Michael Meeks's avatar
Michael Meeks committed
918
        return value_new_float (sqrt(p.Q / (p.N - 1)));
919
920
}

921
922
/***************************************************************************/

923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
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"
	   "@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. "
	   "\n"
	   "@criteria is the range of cells which contains the specified "
941
	   "conditions. The first row of a @criteria should contain the labels "
942
943
944
	   "of the fields for which the criterias are for. Cells below the "
	   "label specify coditions, for example, ``>3'' or ``<9''. "
           "\n"
945
946
	   "@EXAMPLES=\n"
	   "\n"
947
948
949
950
           "@SEEALSO=DSTDEV")
};

static Value *
951
gnumeric_dstdevp (FunctionEvalInfo *ei, Value **argv)
952
953
954
955
956
957
958
959
960
961
{
        Value          *database, *criteria;
	GSList         *criterias;
	GSList         *cells, *current;
	int            field;
	stat_closure_t p;

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

962
	field = find_column_of_field (&ei->pos, database, argv[1]);
963
	if (field < 0)
964
		return value_new_error (&ei->pos, gnumeric_err_NUM);
965

966
	criterias = parse_database_criteria (&ei->pos, database, criteria);
967
	if (criterias == NULL)
968
		return value_new_error (&ei->pos, gnumeric_err_NUM);
969

970
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
971
972
	if (cells == NULL) {
		free_criterias (criterias);
973
		return value_new_error (&ei->pos, gnumeric_err_NUM);
974
	}
975

976
977
978
979
	current = cells;
	setup_stat_closure (&p);

	while (current != NULL) {
980
981
982
983
	        Cell *cell = current->data;

		/* FIXME : What about errors ? */
		callback_function_stat (NULL, cell->value, &p);
984
985
986
987
988
989
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

990
	if (p.N == 0)
991
		return value_new_error (&ei->pos, gnumeric_err_NUM);
992

Michael Meeks's avatar
Michael Meeks committed
993
        return value_new_float (sqrt(p.Q / p.N));
994
995
}

996
997
/***************************************************************************/

998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
static char *help_dsum = {
        N_("@FUNCTION=DSUM\n"
           "@SYNTAX=DSUM(database,field,criteria)\n"

           "@DESCRIPTION="
           "DSUM function returns the sum of numbers in a column "
	   "that match conditions specified. "
	   "\n"
	   "@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. "
	   "\n"
	   "@criteria is the range of cells which contains the specified "
1015
	   "conditions. The first row of a @criteria should contain the labels "
1016
1017
1018
	   "of the fields for which the criterias are for. Cells below the "
	   "label specify coditions, for example, ``>3'' or ``<9''. "
           "\n"
1019
1020
	   "@EXAMPLES=\n"
	   "\n"
1021
1022
1023
1024
           "@SEEALSO=DPRODUCT")
};

static Value *
1025
gnumeric_dsum (FunctionEvalInfo *ei, Value **argv)
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	Cell        *cell;
	int         field;
	float_t     sum;

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

1037
	field = find_column_of_field (&ei->pos, database, argv[1]);
1038
	if (field < 0)
1039
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1040

1041
	criterias = parse_database_criteria (&ei->pos, database, criteria);
1042
	if (criterias == NULL)
1043
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1044

1045
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
1046
1047
	if (cells == NULL) {
		free_criterias (criterias);
1048
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1049
	}
1050

1051
1052
1053
1054
1055
1056
1057
1058
	current = cells;
	sum = 0;
	cell = current->data;

	while (current != NULL) {
	        float_t v;

	        cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
1059
		v = value_get_as_float (cell->value);
1060
1061
1062
1063
1064
1065
1066
		sum += v;
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
1067
        return value_new_float (sum);
1068
1069
}

1070
1071
/***************************************************************************/

1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
static char *help_dvar = {
        N_("@FUNCTION=DVAR\n"
           "@SYNTAX=DVAR(database,field,criteria)\n"

           "@DESCRIPTION="
           "DVAR function returns the estimate of variance of a population "
	   "based on a sample. The populations consists of numbers "
	   "that match conditions specified. "
	   "\n"
	   "@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. "
	   "\n"
	   "@criteria is the range of cells which contains the specified "
1090
	   "conditions. The first row of a @criteria should contain the labels "
1091
1092
1093
	   "of the fields for which the criterias are for. Cells below the "
	   "label specify coditions, for example, ``>3'' or ``<9''. "
           "\n"
1094
1095
	   "@EXAMPLES=\n"
	   "\n"
1096
1097
1098
1099
           "@SEEALSO=DVARP")
};

static Value *
1100
gnumeric_dvar (FunctionEvalInfo *ei, Value **argv)
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
{
        Value          *database, *criteria;
	GSList         *criterias;
	GSList         *cells, *current;
	int            field;
	stat_closure_t p;

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

1111
	field = find_column_of_field (&ei->pos, database, argv[1]);
1112
	if (field < 0)
1113
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1114

1115
	criterias = parse_database_criteria (&ei->pos, database, criteria);
1116
	if (criterias == NULL)
1117
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1118

1119
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
1120
1121
	if (cells == NULL) {
		free_criterias (criterias);
1122
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1123
	}
1124

1125
1126
1127
1128
	current = cells;
	setup_stat_closure (&p);

	while (current != NULL) {
1129
1130
1131
1132
		Cell *cell = current->data;

		/* FIXME : What about errors ? */
		callback_function_stat (NULL, cell->value, &p);
1133
1134
1135
1136
1137
1138
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

1139
	if (p.N - 1 == 0)
1140
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1141

Michael Meeks's avatar
Michael Meeks committed
1142
        return value_new_float (p.Q / (p.N - 1));
1143
1144
}

1145
1146
/***************************************************************************/

1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
static char *help_dvarp = {
        N_("@FUNCTION=DVARP\n"
           "@SYNTAX=DVARP(database,field,criteria)\n"

           "@DESCRIPTION="
           "DVARP function returns the variance of a population based "
	   "on the entire populations. The populations consists of numbers "
	   "that match conditions specified. "
	   "\n"
	   "@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. "
	   "\n"
	   "@criteria is the range of cells which contains the specified "
1165
	   "conditions. The first row of a @criteria should contain the labels "
1166
1167
1168
	   "of the fields for which the criterias are for. Cells below the "
	   "label specify coditions, for example, ``>3'' or ``<9''. "
           "\n"
1169
1170
	   "@EXAMPLES=\n"
	   "\n"
1171
1172
1173
1174
           "@SEEALSO=DVAR")
};

static Value *
1175
gnumeric_dvarp (FunctionEvalInfo *ei, Value **argv)
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
{
        Value          *database, *criteria;
	GSList         *criterias;
	GSList         *cells, *current;
	int            field;
	stat_closure_t p;

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

1186
	field = find_column_of_field (&ei->pos, database, argv[1]);
1187
	if (field < 0)
1188
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1189

1190
	criterias = parse_database_criteria (&ei->pos, database, criteria);
1191
1192

	if (criterias == NULL)
1193
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1194

1195
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
1196

1197
1198
	if (cells == NULL) {
		free_criterias (criterias);
1199
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1200
	}
1201