fn-database.c 36.2 KB
Newer Older
1
/* vim: set sw=8: */
2 3 4 5 6 7
/*
 * fn-database.c:  Built in database functions and functions registration
 *
 * Author:
 *  Jukka-Pekka Iivonen (iivonen@iki.fi)
 */
8 9 10 11 12 13 14 15 16 17
#include <gnumeric-config.h>
#include <gnumeric.h>
#include <func.h>

#include <func-util.h>
#include <parse-util.h>
#include <str.h>
#include <cell.h>
#include <sheet.h>
#include <number-match.h>
18

Jody Goldberg's avatar
Jody Goldberg committed
19 20 21
#include <math.h>
#include <string.h>

22 23 24
/* Type definitions */

typedef struct {
25
        int    row;
26 27 28 29 30 31
        GSList *conditions;
} database_criteria_t;


/* Callback functions */

Jody Goldberg's avatar
Jody Goldberg committed
32 33
gboolean
criteria_test_equal (Value const *x, Value const *y)
34
{
35 36
	g_return_val_if_fail (x != NULL, FALSE);
	g_return_val_if_fail (y != NULL, FALSE);
Morten Welinder's avatar
Morten Welinder committed
37
        if (VALUE_IS_NUMBER (x) && VALUE_IS_NUMBER (y))
Michael Meeks's avatar
Michael Meeks committed
38
	        if (value_get_as_float (x) == value_get_as_float (y))
39 40 41 42
		        return 1;
		else
		        return 0;
	else if (x->type == VALUE_STRING && y->type == VALUE_STRING
43
		 && g_strcasecmp (x->v_str.val->str, y->v_str.val->str) == 0)
44 45 46 47 48
	        return 1;
	else
	        return 0;
}

Jody Goldberg's avatar
Jody Goldberg committed
49 50
gboolean
criteria_test_unequal (Value const *x, Value const *y)
51
{
52 53
	g_return_val_if_fail (x != NULL, FALSE);
	g_return_val_if_fail (y != NULL, FALSE);
Morten Welinder's avatar
Morten Welinder committed
54
        if (VALUE_IS_NUMBER (x) && VALUE_IS_NUMBER (y))
Michael Meeks's avatar
Michael Meeks committed
55
	        if (value_get_as_float (x) != value_get_as_float (y))
56 57 58 59
		        return 1;
		else
		        return 0;
	else if (x->type == VALUE_STRING && y->type == VALUE_STRING
Morten Welinder's avatar
Morten Welinder committed
60
		 && g_strcasecmp (x->v_str.val->str, y->v_str.val->str) != 0)
61 62 63 64 65
	        return 1;
	else
	        return 0;
}

Jody Goldberg's avatar
Jody Goldberg committed
66 67
gboolean
criteria_test_less (Value const *x, Value const *y)
68
{
69 70
	g_return_val_if_fail (x != NULL, FALSE);
	g_return_val_if_fail (y != NULL, FALSE);
Morten Welinder's avatar
Morten Welinder committed
71
        if (VALUE_IS_NUMBER (x) && VALUE_IS_NUMBER (y))
Michael Meeks's avatar
Michael Meeks committed
72
	        if (value_get_as_float (x) < value_get_as_float (y))
73 74 75
		        return 1;
		else
		        return 0;
76 77 78 79
	else
	        return 0;
}

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

Jody Goldberg's avatar
Jody Goldberg committed
94 95
gboolean
criteria_test_less_or_equal (Value const *x, Value const *y)
96
{
97 98
	g_return_val_if_fail (x != NULL, FALSE);
	g_return_val_if_fail (y != NULL, FALSE);
Morten Welinder's avatar
Morten Welinder committed
99
        if (VALUE_IS_NUMBER (x) && VALUE_IS_NUMBER (y))
Michael Meeks's avatar
Michael Meeks committed
100
	        if (value_get_as_float (x) <= value_get_as_float (y))
101 102 103
		        return 1;
		else
		        return 0;
104 105 106 107
	else
	        return 0;
}

Jody Goldberg's avatar
Jody Goldberg committed
108 109
gboolean
criteria_test_greater_or_equal (Value const *x, Value const *y)
110
{
111 112
	g_return_val_if_fail (x != NULL, FALSE);
	g_return_val_if_fail (y != NULL, FALSE);
Morten Welinder's avatar
Morten Welinder committed
113
        if (VALUE_IS_NUMBER (x) && VALUE_IS_NUMBER (y))
Michael Meeks's avatar
Michael Meeks committed
114
	        if (value_get_as_float (x) >= value_get_as_float (y))
115 116 117
		        return 1;
		else
		        return 0;
118 119 120 121 122
	else
	        return 0;
}


123
/* Finds a column index of a field.
124 125
 */
static int
126
find_column_of_field (const EvalPos *ep, Value *database, Value *field)
127 128 129 130 131 132 133
{
        Sheet *sheet;
        Cell  *cell;
	gchar *field_name;
	int   begin_col, end_col, row, n, column;
	int   offset;

134 135
	offset = database->v_range.cell.b.col -
	  database->v_range.cell.a.col;
136

137
	if (field->type == VALUE_INTEGER)
138 139 140 141 142
	        return value_get_as_int (field) + offset - 1;

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

143
	sheet = eval_sheet (database->v_range.cell.a.sheet, ep->sheet);
Michael Meeks's avatar
Michael Meeks committed
144
	field_name = value_get_as_string (field);
145 146
	column = -1;

147
	/* find the column that is labeled after `field_name' */
148 149 150
	begin_col = database->v_range.cell.a.col;
	end_col = database->v_range.cell.b.col;
	row = database->v_range.cell.a.row;
151

Michael Meeks's avatar
Michael Meeks committed
152
	for (n = begin_col; n <= end_col; n++) {
153 154 155
		char *txt;
		gboolean match;

Morten Welinder's avatar
Morten Welinder committed
156
	        cell = sheet_cell_get (sheet, n, row);
157 158
		if (cell == NULL)
		        continue;
159

160
		txt = cell_get_rendered_text (cell);
Michael Meeks's avatar
Michael Meeks committed
161
		match = (g_strcasecmp (field_name, txt) == 0);
162 163
		g_free (txt);
		if (match) {
164 165 166 167 168
		        column = n;
			break;
		}
	}

169
	g_free (field_name);
170 171 172 173 174
	return column;
}

/* Frees the allocated memory.
 */
175
void
Morten Welinder's avatar
Morten Welinder committed
176
free_criterias (GSList *criterias)
177 178 179 180
{
        GSList *list = criterias;

        while (criterias != NULL) {
181
		GSList *l;
182 183
	        database_criteria_t *criteria = criterias->data;

184 185 186 187 188 189
		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
190
		g_slist_free (criteria->conditions);
191
		g_free (criteria);
192 193
	        criterias = criterias->next;
	}
Morten Welinder's avatar
Morten Welinder committed
194
	g_slist_free (list);
195 196
}

197
void
Jody Goldberg's avatar
Jody Goldberg committed
198 199
parse_criteria (char const *criteria, criteria_test_fun_t *fun,
		Value **test_value)
200
{
201
	int len;
202

Morten Welinder's avatar
Morten Welinder committed
203
        if (strncmp (criteria, "<=", 2) == 0) {
204
	        *fun = (criteria_test_fun_t) criteria_test_less_or_equal;
Morten Welinder's avatar
Morten Welinder committed
205 206
		len = 2;
	} else if (strncmp (criteria, ">=", 2) == 0) {
207
	        *fun = (criteria_test_fun_t) criteria_test_greater_or_equal;
Morten Welinder's avatar
Morten Welinder committed
208 209
		len = 2;
	} else if (strncmp (criteria, "<>", 2) == 0) {
210
	        *fun = (criteria_test_fun_t) criteria_test_unequal;
Morten Welinder's avatar
Morten Welinder committed
211
		len = 2;
212
	} else if (*criteria == '<') {
213
	        *fun = (criteria_test_fun_t) criteria_test_less;
Morten Welinder's avatar
Morten Welinder committed
214
		len = 1;
215
	} else if (*criteria == '=') {
216
	        *fun = (criteria_test_fun_t) criteria_test_equal;
Morten Welinder's avatar
Morten Welinder committed
217
		len = 1;
218
	} else if (*criteria == '>') {
219
	        *fun = (criteria_test_fun_t) criteria_test_greater;
Morten Welinder's avatar
Morten Welinder committed
220
		len = 1;
221
	} else {
222
	        *fun = (criteria_test_fun_t) criteria_test_equal;
Morten Welinder's avatar
Morten Welinder committed
223
		len = 0;
224
	}
225

226
	*test_value = format_match (criteria + len, NULL);
227 228
	if (*test_value == NULL)
		*test_value = value_new_string (criteria + len);
229 230
}

231 232 233 234

GSList *
parse_criteria_range(Sheet *sheet, int b_col, int b_row, int e_col, int e_row,
		     int   *field_ind)
235 236
{
	database_criteria_t *new_criteria;
237
	GSList              *criterias = NULL;
238
	GSList              *conditions;
239
	Cell const          *cell;
240 241
	func_criteria_t     *cond;
	gchar               *cell_str;
242

243
        int i, j;
244

Morten Welinder's avatar
Morten Welinder committed
245 246
	for (i = b_row; i <= e_row; i++) {
	        new_criteria = g_new (database_criteria_t, 1);
247
		conditions = NULL;
248

Morten Welinder's avatar
Morten Welinder committed
249 250
		for (j = b_col; j <= e_col; j++) {
		        cell = sheet_cell_get (sheet, j, i);
251
			if (cell_is_blank (cell))
252 253
			        continue;

Morten Welinder's avatar
Morten Welinder committed
254
			cond = g_new (func_criteria_t, 1);
255 256

			/* Equality condition (in number format) */
Morten Welinder's avatar
Morten Welinder committed
257
			if (VALUE_IS_NUMBER (cell->value)) {
258 259 260
			        cond->x = value_duplicate (cell->value);
				cond->fun =
				  (criteria_test_fun_t) criteria_test_equal;
Morten Welinder's avatar
Morten Welinder committed
261 262
				cond->column = field_ind[j - b_col];
				conditions = g_slist_append (conditions, cond);
263
				continue;
264
			}
265 266

			/* Other conditions (in string format) */
267
			cell_str = cell_get_rendered_text (cell);
Morten Welinder's avatar
Morten Welinder committed
268
			parse_criteria (cell_str, &cond->fun, &cond->x);
269
			if (field_ind != NULL)
Morten Welinder's avatar
Morten Welinder committed
270
			        cond->column = field_ind[j - b_col];
271
			else
Morten Welinder's avatar
Morten Welinder committed
272
			        cond->column = j - b_col;
273
			g_free (cell_str);
274

Morten Welinder's avatar
Morten Welinder committed
275
			conditions = g_slist_append (conditions, cond);
276
		}
277

278
		new_criteria->conditions = conditions;
Morten Welinder's avatar
Morten Welinder committed
279
		criterias = g_slist_append (criterias, new_criteria);
280
	}
281

282 283 284 285 286
	return criterias;
}

/* Parses the criteria cell range.
 */
287
GSList *
288
parse_database_criteria (const EvalPos *ep, Value *database,
289 290 291 292
			 Value *criteria)
{
	Sheet               *sheet;
	GSList              *criterias;
293
	Cell const          *cell;
294 295 296 297 298

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

299
	sheet = eval_sheet (criteria->v_range.cell.a.sheet, ep->sheet);
300 301 302 303
	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;
304

Morten Welinder's avatar
Morten Welinder committed
305
	field_ind = g_new (int, (e_col - b_col + 1));
306 307

	/* Find the index numbers for the columns of criterias */
Morten Welinder's avatar
Morten Welinder committed
308 309
	for (i = b_col; i <= e_col; i++) {
	        cell = sheet_cell_get (sheet, i, b_row);
310
		if (cell_is_blank (cell))
311
		        continue;
Morten Welinder's avatar
Morten Welinder committed
312
		field_ind[i - b_col] =
313
		        find_column_of_field (ep, database, cell->value);
Morten Welinder's avatar
Morten Welinder committed
314 315
		if (field_ind[i - b_col] == -1) {
		        g_free (field_ind);
316 317 318 319
			return NULL;
		}
	}

Morten Welinder's avatar
Morten Welinder committed
320 321
	criterias = parse_criteria_range (sheet, b_col, b_row + 1,
					  e_col, e_row,
322 323
					  field_ind);

Morten Welinder's avatar
Morten Welinder committed
324
	g_free (field_ind);
325 326 327
	return criterias;
}

Jody Goldberg's avatar
fix.  
Jody Goldberg committed
328 329 330
/**
 * find_cells_that_match :
 * Finds the cells from the given column that match the criteria.
331 332
 */
static GSList *
333
find_cells_that_match (Sheet *sheet, Value *database,
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
334
		       int col, GSList *criterias)
335
{
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
336 337 338
	GSList *ptr, *condition, *cells;
	int    row, first_row, last_row;
	gboolean add_flag;
339
	Cell *cell;
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
340

341
	cells = NULL;
342
	/* TODO : Why ignore the first row ?  What if there is no header ? */
343
	first_row = database->v_range.cell.a.row + 1;
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
344
	last_row  = database->v_range.cell.b.row;
345

Jody Goldberg's avatar
fix.  
Jody Goldberg committed
346
	for (row = first_row; row <= last_row; row++) {
347 348
		cell = sheet_cell_get (sheet, col, row);
		if (cell_is_blank (cell))
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
349 350 351 352 353 354 355 356 357 358 359 360 361 362
			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);

363
				if (cell_is_blank (tmp) ||
364
				    !cond->fun (tmp->value, cond->x)) {
365 366
					add_flag = FALSE;
					break;
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
367 368 369 370 371 372 373
				}
			}

			if (add_flag)
				break;
		}
		if (add_flag)
374
			cells = g_slist_prepend (cells, cell);
375
	}
376

377
	return g_slist_reverse (cells);
378 379
}

380 381 382 383

/* Finds the rows from the given database that match the criteria.
 */
GSList *
384
find_rows_that_match (Sheet *sheet, int first_col, int first_row,
385 386 387 388
		      int last_col, int last_row,
		      GSList *criterias, gboolean unique_only)
{
	GSList *current, *conditions, *rows;
389
	Cell const *test_cell;
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
390
	int    row, add_flag;
391 392
	rows = NULL;

Morten Welinder's avatar
Morten Welinder committed
393
	for (row = first_row; row <= last_row; row++) {
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
394 395 396 397

		current = criterias;
		add_flag = 1;
		for (current = criterias; current != NULL;
Morten Welinder's avatar
Morten Welinder committed
398
		     current = current->next) {
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
399 400 401 402 403 404 405
			database_criteria_t *current_criteria;

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

			while (conditions != NULL) {
406
				func_criteria_t const *cond = conditions->data;
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
407

408 409 410
				test_cell = sheet_cell_get (sheet,
					first_col + cond->column, row);
				if (cell_is_blank (test_cell))
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
411 412
					continue;

Morten Welinder's avatar
Morten Welinder committed
413
				if (!cond->fun (test_cell->value, cond->x)) {
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431
					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
432
				for (c = rows; c != NULL; c = c->next) {
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
433
					trow = *((gint *) c->data);
Morten Welinder's avatar
Morten Welinder committed
434
					for (i = first_col; i <= last_col; i++) {
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
435
						test_cell =
Morten Welinder's avatar
Morten Welinder committed
436
							sheet_cell_get (sheet, i, trow);
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
437
						cell =
Morten Welinder's avatar
Morten Welinder committed
438
							sheet_cell_get (sheet, i, row);
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
439 440 441 442 443 444 445 446 447 448 449 450
						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;
451
			rows = g_slist_prepend (rows, (gpointer) p);
Jody Goldberg's avatar
fix.  
Jody Goldberg committed
452 453 454
filter_row:
			;
		}
455 456
	}

457
	return g_slist_reverse (rows);
458 459
}

460 461 462 463 464 465
#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 " \
466 467 468
	   "@field is an integer, for example. 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. " \
469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500
	   "\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"


501
/***************************************************************************/
502 503 504 505 506 507 508 509 510

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"
511
	   DB_ARGUMENT_HELP
512
	   "\n"
Valek Frob's avatar
Valek Frob committed
513
	   "@EXAMPLES=\n"
514 515 516 517
           "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"
518
	   "\n"
519 520 521 522
           "@SEEALSO=DCOUNT")
};

static Value *
523
gnumeric_daverage (FunctionEvalInfo *ei, Value **argv)
524 525
{
        Value       *database, *criteria;
526
	Sheet       *sheet;
527 528 529
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
Jody Goldberg's avatar
Jody Goldberg committed
530
	gnum_float     sum;
531 532 533 534 535
	int         count;

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

536
	field = find_column_of_field (ei->pos, database, argv[1]);
537
	if (field < 0)
538
		return value_new_error (ei->pos, gnumeric_err_NUM);
539

540
	criterias = parse_database_criteria (ei->pos, database, criteria);
541
	if (criterias == NULL)
542
		return value_new_error (ei->pos, gnumeric_err_NUM);
543

544
	sheet = eval_sheet (database->v_range.cell.a.sheet,
545 546
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
547 548 549 550 551 552 553 554

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

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

Morten Welinder's avatar
Morten Welinder committed
555
		if (VALUE_IS_NUMBER (cell->value)) {
Michael Meeks's avatar
Michael Meeks committed
556 557 558
			count++;
			sum += value_get_as_float (cell->value);
		}
Morten Welinder's avatar
Morten Welinder committed
559
		current = g_slist_next (current);
560 561
	}

Morten Welinder's avatar
Morten Welinder committed
562 563
	g_slist_free (cells);
	free_criterias (criterias);
564

565 566 567 568
	if ( count > 0 )
	        return value_new_float (sum / count);
	else
	        return value_new_error (ei->pos, gnumeric_err_NUM);
569 570
}

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

573 574 575 576 577 578 579 580
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"
581
	   DB_ARGUMENT_HELP
582
	   "\n"
Valek Frob's avatar
Valek Frob committed
583
	   "@EXAMPLES=\n"
584 585 586
           "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"
587
	   "\n"
588 589 590 591
           "@SEEALSO=DAVERAGE")
};

static Value *
592
gnumeric_dcount (FunctionEvalInfo *ei, Value **argv)
593 594
{
        Value       *database, *criteria;
595
	Sheet       *sheet;
596 597 598 599 600 601 602 603
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	int         count;

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

604
	field = find_column_of_field (ei->pos, database, argv[1]);
605
	if (field < 0)
606
		return value_new_error (ei->pos, gnumeric_err_NUM);
607

608
	criterias = parse_database_criteria (ei->pos, database, criteria);
609
	if (criterias == NULL)
610
		return value_new_error (ei->pos, gnumeric_err_NUM);
611

612
	sheet = eval_sheet (database->v_range.cell.a.sheet,
613 614
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
615 616 617 618

	current = cells;
	count = 0;

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
619 620 621
	while (current != NULL) {
	        Cell *cell = current->data;

Morten Welinder's avatar
Morten Welinder committed
622
		if (VALUE_IS_NUMBER (cell->value))
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
623
		        count++;
Morten Welinder's avatar
Morten Welinder committed
624
		current = g_slist_next (current);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
625 626
	}

Morten Welinder's avatar
Morten Welinder committed
627 628
	g_slist_free (cells);
	free_criterias (criterias);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
629

Michael Meeks's avatar
Michael Meeks committed
630
        return value_new_int (count);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
631 632
}

633 634
/***************************************************************************/

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
635 636 637 638 639 640 641 642
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"
643
	   DB_ARGUMENT_HELP
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
644
	   "\n"
Valek Frob's avatar
Valek Frob committed
645
	   "@EXAMPLES=\n"
646 647 648
           "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"
649
	   "\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
650 651 652 653
           "@SEEALSO=DCOUNT")
};

static Value *
654
gnumeric_dcounta (FunctionEvalInfo *ei, Value **argv)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
655 656
{
        Value       *database, *criteria;
657
	Sheet       *sheet;
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
658 659 660 661 662 663 664 665
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	int         count;

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

666
	field = find_column_of_field (ei->pos, database, argv[1]);
667
	if (field < 0)
668
		return value_new_error (ei->pos, gnumeric_err_NUM);
669

670
	criterias = parse_database_criteria (ei->pos, database, criteria);
671
	if (criterias == NULL)
672
		return value_new_error (ei->pos, gnumeric_err_NUM);
673

674
	sheet = eval_sheet (database->v_range.cell.a.sheet,
675 676
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
677 678 679 680

	current = cells;
	count = 0;

681 682
	while (current != NULL) {
	        count++;
Morten Welinder's avatar
Morten Welinder committed
683
		current = g_slist_next (current);
684 685
	}

Morten Welinder's avatar
Morten Welinder committed
686 687
	g_slist_free (cells);
	free_criterias (criterias);
688

Michael Meeks's avatar
Michael Meeks committed
689
        return value_new_int (count);
690 691
}

692 693
/***************************************************************************/

694 695 696 697 698 699 700 701
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"
702
	   DB_ARGUMENT_HELP
703 704 705 706 707
	   "\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. "
708
	   "\n"
Valek Frob's avatar
Valek Frob committed
709
	   "@EXAMPLES=\n"
710 711
           "DGET(A1:C7, \"Salary\", A9:A10) equals 34323.\n"
           "DGET(A1:C7, \"Name\", A9:A10) equals \"Clark\".\n"
712 713 714 715 716
	   "\n"
           "@SEEALSO=DCOUNT")
};

static Value *
717
gnumeric_dget (FunctionEvalInfo *ei, Value **argv)
718 719
{
        Value       *database, *criteria;
720
	Sheet       *sheet;
721 722 723 724 725 726 727 728 729
	GSList      *criterias;
	GSList      *cells, *current;
	int         field;
	Cell        *cell = NULL;
	int         count;

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

730
	field = find_column_of_field (ei->pos, database, argv[1]);
731
	if (field < 0)
732
		return value_new_error (ei->pos, gnumeric_err_NUM);
733

734
	criterias = parse_database_criteria (ei->pos, database, criteria);
735
	if (criterias == NULL)
736
		return value_new_error (ei->pos, gnumeric_err_NUM);
737

738
	sheet = eval_sheet (database->v_range.cell.a.sheet,
739 740
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
741 742 743 744 745 746 747

	current = cells;
	count = 0;

	while (current != NULL) {
	        cell = current->data;
	        count++;
Morten Welinder's avatar
Morten Welinder committed
748
		current = g_slist_next (current);
749 750
	}

Morten Welinder's avatar
Morten Welinder committed
751 752
	g_slist_free (cells);
	free_criterias (criterias);
753

754
	if (count == 0)
755
		return value_new_error (ei->pos, gnumeric_err_VALUE);
756 757

	if (count > 1)
758
		return value_new_error (ei->pos, gnumeric_err_NUM);
759

760
        return value_duplicate (cell->value);
761 762 763 764 765 766 767 768 769 770
}

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"
771
	   DB_ARGUMENT_HELP
772
	   "\n"
Valek Frob's avatar
Valek Frob committed
773
	   "@EXAMPLES=\n"
774 775 776
           "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"
777
	   "\n"
778 779 780
           "@SEEALSO=DMIN")
};

781 782
/***************************************************************************/

783
static Value *
784
gnumeric_dmax (FunctionEvalInfo *ei, Value **argv)
785 786
{
        Value       *database, *criteria;
787
	Sheet       *sheet;
788 789 790 791
	GSList      *criterias;
	GSList      *cells, *current;
	Cell        *cell;
	int         field;
Jody Goldberg's avatar
Jody Goldberg committed
792
	gnum_float     max;
793 794 795 796

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

797
	field = find_column_of_field (ei->pos, database, argv[1]);
798
	if (field < 0)
799
		return value_new_error (ei->pos, gnumeric_err_NUM);
800

801
	criterias = parse_database_criteria (ei->pos, database, criteria);
802
	if (criterias == NULL)
803
		return value_new_error (ei->pos, gnumeric_err_NUM);
804

805
	sheet = eval_sheet (database->v_range.cell.a.sheet,
806 807
			    ei->pos->sheet);
	cells = find_cells_that_match (sheet, database, field, criterias);
808
	if (cells == NULL)
809
		return value_new_error (ei->pos, gnumeric_err_NUM);
810

811 812
	current = cells;
	cell = current->data;
Michael Meeks's avatar
Michael Meeks committed
813
	max = value_get_as_float (cell->value);
814 815

	while (current != NULL) {
Jody Goldberg's avatar
Jody Goldberg committed
816
	        gnum_float v;
817 818

	        cell = current->data;
Morten Welinder's avatar
Morten Welinder committed
819
		if (VALUE_IS_NUMBER (cell->value)) {
Michael Meeks's avatar
Michael Meeks committed
820 821 822 823
			v = value_get_as_float (cell->value);
			if (max < v)
				max = v;
		}
Morten Welinder's avatar
Morten Welinder committed
824
		current = g_slist_next (current);
825