fn-database.c 31 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
#include "utils.h"
#include "func.h"
#include "sheet.h"

/* Type definitions */

typedef struct {
17
        int    row;
18
19
20
21
22
23
        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
179
parse_criteria(const char *criteria, criteria_test_fun_t *fun,
	       Value **test_value)
180
181
182
183
184
185
{
	char    *p;
	float_t tmp;
	int     len;

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

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

}

217
218
219
/* Parses the criteria cell range.
 */
static GSList *
220
221
parse_database_criteria (const EvalPosition *ep, Value *database,
			 Value *criteria)
222
223
224
225
226
227
{
	Sheet               *sheet;
	database_criteria_t *new_criteria;
	GSList              *criterias;
	GSList              *conditions;
	Cell                *cell;
228

229
230
        int   i, j;
	int   b_col, b_row, e_col, e_row;
231
232
233
234
	int   *field_ind;

	func_criteria_t *cond;
	gchar           *cell_str;
235

236
	sheet = eval_sheet (database->v.cell_range.cell_a.sheet, ep->sheet);
237
238
239
240
241
242
243
244
	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;

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

	/* Find the index numbers for the columns of criterias */
248
249
250
251
	for (i=b_col; i<=e_col; i++) {
	        cell = sheet_cell_get(sheet, i, b_row);
		if (cell == NULL || cell->value == NULL)
		        continue;
252
253
254
255
256
		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;
257
		}
258
	}
259

260
261
262
	for (i=b_row+1; i<=e_row; i++) {
	        new_criteria = g_new(database_criteria_t, 1);
		conditions = NULL;
263

264
265
		for (j=b_col; j<=e_col; j++) {
		        cell = sheet_cell_get(sheet, j, i);
266
			if (cell == NULL || cell->value == NULL)
267
268
			        continue;

269
			cond = g_new(func_criteria_t, 1);
270
271

			/* Equality condition (in number format) */
272
			if (VALUE_IS_NUMBER(cell->value)) {
273
274
275
276
277
278
			        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;
279
			}
280
281

			/* Other conditions (in string format) */
282
			cell_str = cell_get_text(cell);
283
			parse_criteria(cell_str, &cond->fun, &cond->x);
284
			cond->column = field_ind[j-b_col];
285
			g_free (cell_str);
286
	    
287
288
			conditions = g_slist_append(conditions, cond);
		}
289

290
291
292
		new_criteria->conditions = conditions;
		criterias = g_slist_append(criterias, new_criteria);
	}
293

294
	g_free(field_ind);
295
296
297
298
299
300
	return criterias;
}

/* Finds the cells from the given column that match the criteria.
 */
static GSList *
301
302
find_cells_that_match (const EvalPosition *ep, Value *database,
		       int field, GSList *criterias)
303
304
305
306
{
        Sheet  *sheet;
	GSList *current, *conditions, *cells;
        int    row, first_row, last_row, add_flag;
307
	sheet = eval_sheet (database->v.cell_range.cell_a.sheet, ep->sheet);
308
309
310
311
312
313
314
315
316
317
318
	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;
319
	       add_flag = 1;
320
321
322
323
	       for (current = criterias; current != NULL;
		    current=current->next) {
		       database_criteria_t *current_criteria;

324
		       add_flag = 1;
325
326
		       current_criteria = current->data;
		       conditions = current_criteria->conditions;
327

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

331
332
333
334
335
336
337
338
			       test_cell =
				 sheet_cell_get(sheet, cond->column, row);
			       if (test_cell == NULL ||
				   test_cell->value == NULL)
				       continue;

			       if (! cond->fun (test_cell->value, cond->x)) {
				       add_flag = 0;
339
340
341
342
				       break;
			       }
			       conditions = conditions->next;
		       }
343
344

		       if (add_flag)
345
346
			       break;
	       }
347
	       if (add_flag)
348
349
		       cells = g_slist_append(cells, cell);
	}
350

351
352
353
	return cells;
}

354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
#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"


395
/***************************************************************************/
396
397
398
399
400
401
402
403
404

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"
405
	   DB_ARGUMENT_HELP
406
	   "\n"
407
408
409
410
           "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"
411
	   "\n"
412
413
414
415
           "@SEEALSO=DCOUNT")
};

static Value *
416
gnumeric_daverage (FunctionEvalInfo *ei, Value **argv)
417
418
419
420
421
422
423
424
425
426
427
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	float_t     sum;
	int         count;

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

428
	field = find_column_of_field (&ei->pos, database, argv[1]);
429
	if (field < 0)
430
		return value_new_error (&ei->pos, gnumeric_err_NUM);
431

432
	criterias = parse_database_criteria (&ei->pos, database, criteria);
433
	if (criterias == NULL)
434
		return value_new_error (&ei->pos, gnumeric_err_NUM);
435

436
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
437
438
439
440
441
442
443
444

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

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

Michael Meeks's avatar
Michael Meeks committed
445
446
447
448
		if (VALUE_IS_NUMBER(cell->value)) {
			count++;
			sum += value_get_as_float (cell->value);
		}
449
450
451
452
453
454
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
455
        return value_new_float (sum / count);
456
457
}

458
459
/***************************************************************************/

460
461
462
463
464
465
466
467
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"
468
	   DB_ARGUMENT_HELP
469
	   "\n"
470
471
472
           "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"
473
	   "\n"
474
475
476
477
           "@SEEALSO=DAVERAGE")
};

static Value *
478
gnumeric_dcount (FunctionEvalInfo *ei, Value **argv)
479
480
481
482
483
484
485
486
487
488
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	int         count;

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

489
	field = find_column_of_field (&ei->pos, database, argv[1]);
490
	if (field < 0)
491
		return value_new_error (&ei->pos, gnumeric_err_NUM);
492

493
	criterias = parse_database_criteria (&ei->pos, database, criteria);
494
	if (criterias == NULL)
495
		return value_new_error (&ei->pos, gnumeric_err_NUM);
496

497
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
498
499
500
501

	current = cells;
	count = 0;

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
502
503
504
505
506
507
508
509
510
511
512
	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
513
        return value_new_int (count);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
514
515
}

516
517
/***************************************************************************/

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
518
519
520
521
522
523
524
525
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"
526
	   DB_ARGUMENT_HELP
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
527
	   "\n"
528
529
530
           "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"
531
	   "\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
532
533
534
535
           "@SEEALSO=DCOUNT")
};

static Value *
536
gnumeric_dcounta (FunctionEvalInfo *ei, Value **argv)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
537
538
539
540
541
542
543
544
545
546
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	int         count;

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

547
	field = find_column_of_field (&ei->pos, database, argv[1]);
548
	if (field < 0)
549
		return value_new_error (&ei->pos, gnumeric_err_NUM);
550

551
	criterias = parse_database_criteria (&ei->pos, database, criteria);
552
	if (criterias == NULL)
553
		return value_new_error (&ei->pos, gnumeric_err_NUM);
554

555
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
556
557
558
559

	current = cells;
	count = 0;

560
561
562
563
564
565
566
567
	while (current != NULL) {
	        count++;
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
568
        return value_new_int (count);
569
570
}

571
572
/***************************************************************************/

573
574
575
576
577
578
579
580
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"
581
	   DB_ARGUMENT_HELP
582
	   "\n"
583
584
           "DGET(A1:C7, \"Salary\", A9:A10) equals 34323.\n"
           "DGET(A1:C7, \"Name\", A9:A10) equals \"Clark\".\n"
585
586
587
588
589
590
591
592
593
594
           "\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 *
595
gnumeric_dget (FunctionEvalInfo *ei, Value **argv)
596
597
598
599
600
601
602
603
604
605
606
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	Cell        *cell = NULL;
	int         count;

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

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

609
	if (field < 0)
610
		return value_new_error (&ei->pos, gnumeric_err_NUM);
611

612
	criterias = parse_database_criteria (&ei->pos, database, criteria);
613

614
	if (criterias == NULL)
615
		return value_new_error (&ei->pos, gnumeric_err_NUM);
616

617
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
618
619
620
621
622
623
624
625
626
627
628
629
630

	current = cells;
	count = 0;

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

	g_slist_free(cells);
	free_criterias(criterias);

631
	if (count == 0)
632
		return value_new_error (&ei->pos, gnumeric_err_VALUE);
633
634

	if (count > 1)
635
		return value_new_error (&ei->pos, gnumeric_err_NUM);
636

637
        return value_duplicate (cell->value);
638
639
640
641
642
643
644
645
646
647
}

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"
648
	   DB_ARGUMENT_HELP
649
	   "\n"
650
651
652
           "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"
653
	   "\n"
654
655
656
           "@SEEALSO=DMIN")
};

657
658
/***************************************************************************/

659
static Value *
660
gnumeric_dmax (FunctionEvalInfo *ei, Value **argv)
661
662
663
664
665
666
667
668
669
670
671
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	Cell        *cell;
	int         field;
	float_t     max;

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

672
	field = find_column_of_field (&ei->pos, database, argv[1]);
673
	if (field < 0)
674
		return value_new_error (&ei->pos, gnumeric_err_NUM);
675

676
	criterias = parse_database_criteria (&ei->pos, database, criteria);
677
	if (criterias == NULL)
678
		return value_new_error (&ei->pos, gnumeric_err_NUM);
679

680
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
681
	if (cells == NULL)
682
		return value_new_error (&ei->pos, gnumeric_err_NUM);
683

684
685
	current = cells;
	cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
686
	max = value_get_as_float (cell->value);
687
688
689
690
691

	while (current != NULL) {
	        float_t v;

	        cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
692
693
694
695
696
		if (VALUE_IS_NUMBER(cell->value)) {
			v = value_get_as_float (cell->value);
			if (max < v)
				max = v;
		}
697
698
699
700
701
702
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
703
        return value_new_float (max);
704
705
}

706
707
/***************************************************************************/

708
709
710
711
712
713
714
715
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"
716
	   DB_ARGUMENT_HELP
717
	   "\n"
718
719
           "DMIN(A1:C7, \"Salary\", A9:B11) equals 34323.\n"
           "DMIN(A1:C7, \"Age\", A9:B11) equals 29.\n"
720
	   "\n"
721
722
723
724
           "@SEEALSO=DMAX")
};

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

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

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

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

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

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

	while (current != NULL) {
	        float_t v;

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

	g_slist_free(cells);
	free_criterias(criterias);

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

773
774
/***************************************************************************/

775
776
777
778
779
780
781
782
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"
783
	   DB_ARGUMENT_HELP
784
	   "\n"
785
           "DPRODUCT(A1:C7, \"Age\", A9:B11) equals 1247.\n"
786
	   "\n"
787
788
789
790
           "@SEEALSO=DSUM")
};

static Value *
791
gnumeric_dproduct (FunctionEvalInfo *ei, Value **argv)
792
793
794
795
796
797
798
799
800
801
802
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	Cell        *cell;
	int         field;
	float_t     product;

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

803
	field = find_column_of_field (&ei->pos, database, argv[1]);
804
	if (field < 0)
805
		return value_new_error (&ei->pos, gnumeric_err_NUM);
806

807
	criterias = parse_database_criteria (&ei->pos, database, criteria);
808
809

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

812
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
813

814
815
	if (cells == NULL) {
		free_criterias (criterias);
816
		return value_new_error (&ei->pos, gnumeric_err_NUM);
817
	}
818

819
820
821
822
823
824
825
826
	current = cells;
	product = 1;
	cell = current->data;

	while (current != NULL) {
	        float_t v;

	        cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
827
		v = value_get_as_float (cell->value);
828
829
830
831
832
833
834
		product *= v;
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
835
        return value_new_float (product);
836
837
}

838
839
/***************************************************************************/

840
841
842
843
844
845
846
847
848
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"
849
	   DB_ARGUMENT_HELP
850
	   "\n"
851
852
           "DSTDEV(A1:C7, \"Age\", A9:B11) equals 9.89949.\n"
           "DSTDEV(A1:C7, \"Salary\", A9:B11) equals 9135.112506.\n"
853
	   "\n"
854
855
856
857
           "@SEEALSO=DSTDEVP")
};

static Value *
858
gnumeric_dstdev (FunctionEvalInfo *ei, Value **argv)
859
860
861
862
863
864
865
866
867
868
{
        Value          *database, *criteria;
	GSList         *criterias;
	GSList         *cells, *current;
	int            field;
	stat_closure_t p;

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

869
	field = find_column_of_field (&ei->pos, database, argv[1]);
870
	if (field < 0)
871
		return value_new_error (&ei->pos, gnumeric_err_NUM);
872

873
	criterias = parse_database_criteria (&ei->pos, database, criteria);
874
	if (criterias == NULL)
875
		return value_new_error (&ei->pos, gnumeric_err_NUM);
876

877
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
878
	if (cells == NULL)
879
		return value_new_error (&ei->pos, gnumeric_err_NUM);
880

881
882
883
884
	current = cells;
	setup_stat_closure (&p);

	while (current != NULL) {
885
886
887
888
	        Cell *cell = current->data;

		/* FIXME : What about errors ? */
		callback_function_stat (NULL, cell->value, &p);
889
890
891
892
893
894
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

895
	if (p.N - 1 == 0)
896
		return value_new_error (&ei->pos, gnumeric_err_NUM);
897

Michael Meeks's avatar
Michael Meeks committed
898
        return value_new_float (sqrt(p.Q / (p.N - 1)));
899
900
}

901
902
/***************************************************************************/

903
904
905
906
907
908
909
910
911
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"
912
	   DB_ARGUMENT_HELP
913
	   "\n"
914
915
           "DSTDEVP(A1:C7, \"Age\", A9:B11) equals 7.\n"
           "DSTDEVP(A1:C7, \"Salary\", A9:B11) equals 6459.5.\n"
916
	   "\n"
917
918
919
920
           "@SEEALSO=DSTDEV")
};

static Value *
921
gnumeric_dstdevp (FunctionEvalInfo *ei, Value **argv)
922
923
924
925
926
927
928
929
930
931
{
        Value          *database, *criteria;
	GSList         *criterias;
	GSList         *cells, *current;
	int            field;
	stat_closure_t p;

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

932
	field = find_column_of_field (&ei->pos, database, argv[1]);
933
	if (field < 0)
934
		return value_new_error (&ei->pos, gnumeric_err_NUM);
935

936
	criterias = parse_database_criteria (&ei->pos, database, criteria);
937
	if (criterias == NULL)
938
		return value_new_error (&ei->pos, gnumeric_err_NUM);
939

940
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
941
942
	if (cells == NULL) {
		free_criterias (criterias);
943
		return value_new_error (&ei->pos, gnumeric_err_NUM);
944
	}
945

946
947
948
949
	current = cells;
	setup_stat_closure (&p);

	while (current != NULL) {
950
951
952
953
	        Cell *cell = current->data;

		/* FIXME : What about errors ? */
		callback_function_stat (NULL, cell->value, &p);
954
955
956
957
958
959
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

960
	if (p.N == 0)
961
		return value_new_error (&ei->pos, gnumeric_err_NUM);
962

Michael Meeks's avatar
Michael Meeks committed
963
        return value_new_float (sqrt(p.Q / p.N));
964
965
}

966
967
/***************************************************************************/

968
969
970
971
972
973
974
975
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"
976
	   DB_ARGUMENT_HELP
977
	   "\n"
978
979
           "DSUM(A1:C7, \"Age\", A9:B11) equals 72.\n"
           "DSUM(A1:C7, \"Salary\", A9:B11) equals 81565.\n"
980
	   "\n"
981
982
983
984
           "@SEEALSO=DPRODUCT")
};

static Value *
985
gnumeric_dsum (FunctionEvalInfo *ei, Value **argv)
986
987
988
989
990
991
992
993
994
995
996
{
        Value       *database, *criteria;
	GSList      *criterias;
	GSList      *cells, *current;
	Cell        *cell;
	int         field;
	float_t     sum;

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

997
	field = find_column_of_field (&ei->pos, database, argv[1]);
998
	if (field < 0)
999
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1000

1001
	criterias = parse_database_criteria (&ei->pos, database, criteria);
1002
	if (criterias == NULL)
1003
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1004

1005
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
1006
1007
	if (cells == NULL) {
		free_criterias (criterias);
1008
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1009
	}
1010

1011
1012
1013
1014
1015
1016
1017
1018
	current = cells;
	sum = 0;
	cell = current->data;

	while (current != NULL) {
	        float_t v;

	        cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
1019
		v = value_get_as_float (cell->value);
1020
1021
1022
1023
1024
1025
1026
		sum += v;
		current = g_slist_next(current);
	}

	g_slist_free(cells);
	free_criterias(criterias);

Michael Meeks's avatar
Michael Meeks committed
1027
        return value_new_float (sum);
1028
1029
}

1030
1031
/***************************************************************************/

1032
1033
1034
1035
1036
1037
1038
1039
1040
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"
1041
	   DB_ARGUMENT_HELP
1042
	   "\n"
1043
1044
           "DVAR(A1:C7, \"Age\", A9:B11) equals 98.\n"
           "DVAR(A1:C7, \"Salary\", A9:B11) equals 83450280.5.\n"
1045
	   "\n"
1046
1047
1048
1049
           "@SEEALSO=DVARP")
};

static Value *
1050
gnumeric_dvar (FunctionEvalInfo *ei, Value **argv)
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
{
        Value          *database, *criteria;
	GSList         *criterias;
	GSList         *cells, *current;
	int            field;
	stat_closure_t p;

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

1061
	field = find_column_of_field (&ei->pos, database, argv[1]);
1062
	if (field < 0)
1063
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1064

1065
	criterias = parse_database_criteria (&ei->pos, database, criteria);
1066
	if (criterias == NULL)
1067
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1068

1069
	cells = find_cells_that_match (&ei->pos, database, field, criterias);
1070
1071
	if (cells == NULL) {
		free_criterias (criterias);
1072
		return value_new_error (&ei->pos, gnumeric_err_NUM);
1073
	}
1074