src/func-builtin.c

   1 /* vim: set sw=8: -*- Mode: C; tab-width: 8; indent-tabs-mode: t; c-basic-offset: 8 -*- */
   2 /*
   3  * func-builtin.c:  Built in functions.
   4  *
   5  * Authors:
   6  *   Morten Welinder (terra@gnome.org)
   7  *   Jody Goldberg (jody@gnome.org)
   8  *
   9  * This program is free software; you can redistribute it and/or modify
  10  * it under the terms of the GNU General Public License as published by
  11  * the Free Software Foundation; either version 2 of the License, or
  12  * (at your option) any later version.
  13  *
  14  * This program is distributed in the hope that it will be useful,
  15  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  16  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  17  * GNU General Public License for more details.
  18  *
  19  * You should have received a copy of the GNU General Public License
  20  * along with this program; if not, see <https://www.gnu.org/licenses/>.
  21  */
  22 #include <gnumeric-config.h>
  23 #include <glib/gi18n-lib.h>
  24 #include <gnumeric.h>
  25 #include <func.h>
  26 #include <func-builtin.h>
  27 #include <rangefunc.h>
  28 #include <collect.h>
  29 #include <value.h>
  30 #include <selection.h>
  31 #include <expr.h>
  32 #include <expr-deriv.h>
  33 #include <expr-impl.h>
  34 #include <sheet.h>
  35 #include <cell.h>
  36 #include <application.h>
  37 #include <number-match.h>
  38 #include <gutils.h>
  39
  40 /***************************************************************************/
  41
  42 static GnmFuncHelp const help_sum[] = {
  43         /* xgettext : see po-functions/README.translators */
  44         { GNM_FUNC_HELP_NAME, N_("SUM:sum of the given values")},
  45         /* xgettext : see po-functions/README.translators */
  46         { GNM_FUNC_HELP_ARG, N_("values:a list of values to add")},
  47         { GNM_FUNC_HELP_DESCRIPTION, N_("SUM computes the sum of all the values and cells referenced in the argument list.")},
  48         { GNM_FUNC_HELP_EXCEL, N_("This function is Excel compatible.") },
  49         { GNM_FUNC_HELP_ODF, N_("This function is OpenFormula compatible.") },
  50         { GNM_FUNC_HELP_EXAMPLES, "=SUM(11,15,17,21,43)" },
  51         { GNM_FUNC_HELP_SEEALSO, "AVERAGE,COUNT"},
  52         { GNM_FUNC_HELP_END }
  53 };
  54
  55 static GnmValue *
  56 gnumeric_sum (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
  57 {
  58         return float_range_function (argc, argv, ei,
  59                                      gnm_range_sum,
  60                                      COLLECT_IGNORE_STRINGS |
  61                                      COLLECT_IGNORE_BOOLS |
  62                                      COLLECT_IGNORE_BLANKS,
  63                                      GNM_ERROR_VALUE);
  64 }
  65
  66 static GnmExpr const *
  67 gnumeric_sum_deriv (GnmExpr const *expr,
  68                     GnmEvalPos const *ep,
  69                     GnmExprDeriv *info)
  70 {
  71         GnmExprList *l, *args = gnm_expr_deriv_collect (expr, ep, info);
  72         GnmFunc *fsum = gnm_expr_get_func_def (expr);
  73         gboolean bad = FALSE;
  74
  75         for (l = args; l; l = l->next) {
  76                 GnmExpr const *e = l->data;
  77                 GnmExpr const *d = gnm_expr_deriv (e, ep, info);
  78                 if (d) {
  79                         gnm_expr_free (e);
  80                         l->data = (gpointer)d;
  81                 } else {
  82                         bad = TRUE;
  83                         break;
  84                 }
  85         }
  86
  87         if (bad) {
  88                 for (l = args; l; l = l->next)
  89                         gnm_expr_free (l->data);
  90                 gnm_expr_list_free (args);
  91                 return NULL;
  92         } else
  93                 return gnm_expr_new_funcall (fsum, args);
  94 }
  95
  96 /***************************************************************************/
  97
  98 static GnmFuncHelp const help_product[] = {
  99         /* xgettext : see po-functions/README.translators */
 100         { GNM_FUNC_HELP_NAME, N_("PRODUCT:product of the given values")},
 101         /* xgettext : see po-functions/README.translators */
 102         { GNM_FUNC_HELP_ARG, N_("values:a list of values to multiply")},
 103         { GNM_FUNC_HELP_DESCRIPTION, N_("PRODUCT computes the product of all the values and cells referenced in the argument list.")},
 104         { GNM_FUNC_HELP_NOTE, N_("If all cells are empty, the result will be 0.") },
 105         { GNM_FUNC_HELP_EXCEL, N_("This function is Excel compatible.") },
 106         { GNM_FUNC_HELP_ODF, N_("This function is OpenFormula compatible.") },
 107         { GNM_FUNC_HELP_EXAMPLES, "=PRODUCT(2,5,9)" },
 108         { GNM_FUNC_HELP_SEEALSO, "SUM,COUNT,G_PRODUCT"},
 109         { GNM_FUNC_HELP_END }
 110 };
 111
 112 static int
 113 range_bogusproduct (gnm_float const *xs, int n, gnm_float *res)
 114 {
 115         if (n == 0) {
 116                 *res = 0;  /* Severe Excel brain damange.  */
 117                 return 0;
 118         } else
 119                 return gnm_range_product (xs, n, res);
 120 }
 121
 122 static GnmValue *
 123 gnumeric_product (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
 124 {
 125         return float_range_function (argc, argv, ei,
 126                                      range_bogusproduct,
 127                                      COLLECT_IGNORE_STRINGS |
 128                                      COLLECT_IGNORE_BOOLS |
 129                                      COLLECT_IGNORE_BLANKS,
 130                                      GNM_ERROR_VALUE);
 131 }
 132
 133 /***************************************************************************/
 134
 135 static GnmFuncHelp const help_gnumeric_version[] = {
 136         /* xgettext : see po-functions/README.translators */
 137         { GNM_FUNC_HELP_NAME, N_("GNUMERIC_VERSION:the current version of Gnumeric")},
 138         { GNM_FUNC_HELP_DESCRIPTION, N_("GNUMERIC_VERSION returns the version of gnumeric as a string.")},
 139         { GNM_FUNC_HELP_EXAMPLES, "=GNUMERIC_VERSION()" },
 140         { GNM_FUNC_HELP_SEEALSO, ""},
 141         { GNM_FUNC_HELP_END }
 142 };
 143
 144 static GnmValue *
 145 gnumeric_version (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 146 {
 147         return value_new_string (GNM_VERSION_FULL);
 148 }
 149
 150 /***************************************************************************/
 151
 152 static GnmFuncHelp const help_table[] = {
 153         { GNM_FUNC_HELP_NAME, N_("TABLE:internal function for data tables")},
 154         { GNM_FUNC_HELP_DESCRIPTION, N_("This function should not be called directly.")},
 155         { GNM_FUNC_HELP_SEEALSO, ""},
 156         { GNM_FUNC_HELP_END }
 157 };
 158
 159
 160 static GnmDependentFlags
 161 gnumeric_table_link (GnmFuncEvalInfo *ei, gboolean qlink)
 162 {
 163         GnmDependent *dep = ei->pos->dep;
 164         GnmRangeRef rr;
 165
 166         if (!qlink)
 167                 return DEPENDENT_NO_FLAG;
 168
 169         if (!eval_pos_is_array_context (ei->pos))
 170                 return DEPENDENT_IGNORE_ARGS;
 171
 172         rr.a.col_relative = rr.a.row_relative =
 173         rr.b.col_relative = rr.b.row_relative = FALSE;
 174         rr.a.sheet = rr.b.sheet = dep->sheet;
 175
 176         g_return_val_if_fail (ei->pos->eval.col > 0, DEPENDENT_IGNORE_ARGS);
 177         rr.a.col = rr.b.col = ei->pos->eval.col - 1;
 178         rr.a.row = ei->pos->eval.row;
 179         rr.b.row = rr.a.row + ei->pos->array->rows - 1;
 180         dependent_add_dynamic_dep (dep, &rr);
 181
 182         g_return_val_if_fail (ei->pos->eval.row > 0, DEPENDENT_IGNORE_ARGS);
 183         rr.a.row = rr.b.row = ei->pos->eval.row - 1;
 184         rr.a.col = ei->pos->eval.col;
 185         rr.b.col = rr.a.col + ei->pos->array->cols - 1;
 186         dependent_add_dynamic_dep (dep, &rr);
 187
 188         return DEPENDENT_IGNORE_ARGS;
 189 }
 190
 191 static GnmValue *
 192 gnumeric_table (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
 193 {
 194         GnmCell       *in[3], *x_iter, *y_iter;
 195         GnmValue      *val[3], *res;
 196         GnmCellPos     pos;
 197         GnmEvalPos const *ep = ei->pos;
 198         int x, y;
 199
 200         /* evaluation clears the dynamic deps */
 201         gnumeric_table_link (ei, TRUE);
 202
 203         if (argc != 2 ||
 204             ep->eval.col < 1 ||
 205             ep->eval.row < 1 ||
 206             !eval_pos_is_array_context (ep))
 207                 return value_new_error_REF (ep);
 208
 209         for (x = 0; x < 2 ; x++) {
 210                 GnmExpr const *arg = argv[x];
 211                 in[x] = NULL;
 212                 val[x] = NULL;
 213
 214                 if (arg && GNM_EXPR_GET_OPER (arg) == GNM_EXPR_OP_CELLREF) {
 215                         gnm_cellpos_init_cellref (&pos, &arg->cellref.ref,
 216                                                   &ep->eval, ep->sheet);
 217                         in[x] = sheet_cell_get (ep->sheet, pos.col, pos.row);
 218                         if (NULL == in[x])
 219                                 in[x] = sheet_cell_fetch (ep->sheet, pos.col, pos.row);
 220                         else {
 221                                 val[x] = value_dup (in[x]->value);
 222                                 if (gnm_cell_has_expr (in[x]) &&
 223                                     gnm_cell_expr_is_linked (in[x]))
 224                                         dependent_unlink (GNM_CELL_TO_DEP (in[x]));
 225                         }
 226                 }
 227         }
 228
 229         in[2] = NULL;
 230         val[2] = NULL;
 231         if (NULL != in[0] && NULL != in[1]) {
 232                 in[2] = sheet_cell_get (ep->sheet,
 233                                         ep->eval.col - 1, ep->eval.row - 1);
 234                 if (NULL == in[2])
 235                         in[2] = sheet_cell_fetch (ep->sheet,
 236                                 ep->eval.col - 1, ep->eval.row - 1);
 237                 else
 238                         val[2] = value_dup (in[2]->value);
 239         }
 240
 241         res = value_new_array (ep->array->cols, ep->array->rows);
 242         for (x = ep->array->cols ; x-- > 0 ; ) {
 243                 x_iter = sheet_cell_get (ep->sheet,
 244                         x + ep->eval.col, ep->eval.row-1);
 245                 if (NULL == x_iter)
 246                         continue;
 247                 gnm_cell_eval (x_iter);
 248                 if (NULL != in[0]) {
 249                         GnmValue *v0 = value_dup (x_iter->value);
 250                         value_release (in[0]->value);
 251                         in[0]->value = v0;
 252                         dependent_queue_recalc (GNM_CELL_TO_DEP (in[0]));
 253                         gnm_app_recalc_clear_caches ();
 254                 } else {
 255                         value_release (val[0]);
 256                         val[0] = value_dup (x_iter->value);
 257                 }
 258
 259                 for (y = ep->array->rows ; y-- > 0 ; ) {
 260                         g_signal_emit_by_name (gnm_app_get_app (), "recalc-finished");
 261                         y_iter = sheet_cell_get (ep->sheet,
 262                                 ep->eval.col-1, y + ep->eval.row);
 263                         if (NULL == y_iter)
 264                                 continue;
 265                         gnm_cell_eval (y_iter);
 266                         if (NULL != in[1]) {
 267                                 GnmValue *v1 = value_dup (in[1]->value);
 268                                 GnmValue *vy = value_dup (y_iter->value);
 269                                 value_release (in[1]->value);
 270                                 in[1]->value = vy;
 271                                 dependent_queue_recalc (GNM_CELL_TO_DEP (in[1]));
 272                                 gnm_app_recalc_clear_caches ();
 273                                 if (NULL != in[0]) {
 274                                         gnm_cell_eval (in[2]);
 275                                         value_array_set (res, x, y, value_dup (in[2]->value));
 276                                 } else {
 277                                         gnm_cell_eval (x_iter);
 278                                         value_array_set (res, x, y, value_dup (x_iter->value));
 279                                 }
 280                                 value_release (in[1]->value);
 281                                 in[1]->value = v1;
 282                         } else
 283                                 value_array_set (res, x, y, value_dup (y_iter->value));
 284                 }
 285                 if (in[0]) {
 286                         value_release (in[0]->value);
 287                         in[0]->value = value_dup (val[0]);
 288                 }
 289         }
 290         if (NULL != in[2]) {
 291                 value_release (in[2]->value);
 292                 in[2]->value = NULL;
 293         }
 294         for (x = 0 ; x < 2 ; x++)
 295                 if (in[x] &&
 296                     gnm_cell_has_expr (in[x]) &&
 297                     !gnm_cell_expr_is_linked (in[x]))
 298                         dependent_link (&in[x]->base);
 299
 300         for (x = 0 ; x < 3 ; x++) {
 301                 int y;
 302                 for (y = x + 1; y < 3; y++) {
 303                         if (in[x] == in[y])
 304                                 in[y] = NULL;
 305                 }
 306
 307                 if (in[x]) {
 308                         gboolean had_cell = (val[x] != NULL);
 309
 310                         value_release (in[x]->value);
 311                         in[x]->value = val[x];
 312                         val[x] = NULL;
 313
 314                         dependent_queue_recalc (GNM_CELL_TO_DEP (in[x]));
 315
 316                         /* always assign, we still point at a released value */
 317                         if (!had_cell) {
 318                                 sheet_cell_remove (ep->sheet, in[x], FALSE, FALSE);
 319                                 in[x] = NULL;
 320                         }
 321                         gnm_app_recalc_clear_caches ();
 322                 }
 323         }
 324
 325         for (x = 0 ; x < 3 ; x++) {
 326                 if (in[x])
 327                         gnm_cell_eval (in[x]);
 328                 value_release (val[x]);
 329         }
 330
 331         return res;
 332 }
 333
 334 /***************************************************************************/
 335
 336 static GnmFuncHelp const help_if[] = {
 337         /* xgettext : see po-functions/README.translators */
 338         { GNM_FUNC_HELP_NAME, N_("IF:conditional expression") },
 339         /* xgettext : see po-functions/README.translators */
 340         { GNM_FUNC_HELP_ARG, N_("cond:condition") },
 341         /* xgettext : see po-functions/README.translators */
 342         { GNM_FUNC_HELP_ARG, N_("trueval:value to use if condition is true") },
 343         /* xgettext : see po-functions/README.translators */
 344         { GNM_FUNC_HELP_ARG, N_("falseval:value to use if condition is false") },
 345         { GNM_FUNC_HELP_DESCRIPTION, N_("This function first evaluates the condition.  If the result is true, it will then evaluate and return the second argument.  Otherwise, it will evaluate and return the last argument.") },
 346         { GNM_FUNC_HELP_EXAMPLES, "=IF(1+2=3,\"x\",\"y\")" },
 347         { GNM_FUNC_HELP_SEEALSO, "AND,OR,XOR,NOT,IFERROR" },
 348         { GNM_FUNC_HELP_END }
 349 };
 350
 351 GnmValue *
 352 gnumeric_if (GnmFuncEvalInfo *ei, GnmValue const * const *args)
 353 {
 354         gboolean err;
 355         int res = value_get_as_bool (args[0], &err) ? 1 : 2;
 356
 357         if (args[res])
 358                 return value_dup (args[res]);
 359
 360         if (ei->func_call->argc < res + 1)
 361                 /* arg-not-there: default to TRUE/FALSE.  */
 362                 return value_new_bool (res == 1);
 363         else
 364                 /* arg blank: default to 0.  */
 365                 return value_new_int (0);
 366 }
 367
 368
 369 GnmValue *
 370 gnumeric_if2 (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv,
 371               GnmExprEvalFlags flags)
 372 {
 373         gboolean err;
 374         int i, branch;
 375         GnmValue *args[3];
 376         GnmValue *res;
 377
 378         g_return_val_if_fail (argc >= 1 && argc <= 3,
 379                               value_new_error_VALUE (ei->pos));
 380
 381         /*
 382          * In this version of IF, we evaluate the arguments ourselves,
 383          * then call the regular IF.  However, arguments we do not need
 384          * we do not evaluate.
 385          *
 386          * IF is sometimes used to avoid expensive calculations.  Always
 387          * computing both branches destroys that intent.  See bug 326595.
 388          */
 389
 390         /* Evaluate condition.  */
 391         res = gnm_expr_eval (argv[0], ei->pos, 0);
 392         if (VALUE_IS_ERROR (res))
 393                 return res;
 394         args[0] = res;
 395
 396         branch = value_get_as_bool (args[0], &err) ? 1 : 2;
 397         for (i = 1; i <= 2; i++) {
 398                 args[i] = NULL;
 399                 if (i < argc && i == branch && !gnm_expr_is_empty (argv[i])) {
 400                         args[i] = gnm_expr_eval (argv[i], ei->pos, flags);
 401                         if (!args[i])
 402                                 args[i] = value_new_empty ();
 403                 }
 404         }
 405
 406         res = gnumeric_if (ei, (GnmValue const * const *)args);
 407
 408         for (i = 0; i <= 2; i++)
 409                 value_release (args[i]);
 410
 411         return res;
 412 }
 413
 414 /***************************************************************************/
 415
 416 static GnmFuncHelp const help_number_match[] = {
 417         /* Not for public consumption. */
 418         { GNM_FUNC_HELP_END }
 419 };
 420
 421 static GnmValue *
 422 gnumeric_number_match (GnmFuncEvalInfo *ei, GnmValue const * const *args)
 423 {
 424         const char *text = value_peek_string (args[0]);
 425         const char *fmttxt = args[1] ? value_peek_string (args[1]) : NULL;
 426         GOFormat *fmt = NULL;
 427         GnmValue *v;
 428         GODateConventions const *date_conv = NULL;
 429
 430         if (fmttxt && *fmttxt != 0) {
 431                 fmt = go_format_new_from_XL (fmttxt);
 432                 if (go_format_is_invalid (fmt)) {
 433                         v = value_new_error_VALUE (ei->pos);
 434                         goto out;
 435                 }
 436         }
 437
 438         v = format_match (text, fmt, date_conv);
 439         if (!v) v = value_new_string (text);
 440
 441  out:
 442         go_format_unref (fmt);
 443         return v;
 444 }
 445
 446 /***************************************************************************/
 447
 448 static GnmFuncGroup *math_group = NULL;
 449 static GnmFuncGroup *gnumeric_group = NULL;
 450 static GnmFuncGroup *logic_group = NULL;
 451
 452 void
 453 func_builtin_init (void)
 454 {
 455         const char *gname;
 456         const char *textdomain = GETTEXT_PACKAGE;
 457         int i = 0;
 458
 459         static GnmFuncDescriptor const builtins [] = {
 460                 /* --- Math --- */
 461                 {       "sum",          NULL,
 462                         help_sum,       NULL,   gnumeric_sum,
 463                         NULL, NULL, GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
 464                         GNM_FUNC_IMPL_STATUS_COMPLETE,
 465                         GNM_FUNC_TEST_STATUS_BASIC
 466                 },
 467                 {       "product",              NULL,
 468                         help_product,   NULL,   gnumeric_product,
 469                         NULL, NULL, GNM_FUNC_SIMPLE,
 470                         GNM_FUNC_IMPL_STATUS_COMPLETE,
 471                         GNM_FUNC_TEST_STATUS_BASIC
 472                 },
 473                 /* --- Gnumeric --- */
 474                 {       "gnumeric_version",     "",
 475                         help_gnumeric_version,  gnumeric_version, NULL,
 476                         NULL, NULL, GNM_FUNC_SIMPLE,
 477                         GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC,
 478                         GNM_FUNC_TEST_STATUS_EXHAUSTIVE
 479                 },
 480                 {       "table",        "",
 481                         help_table,             NULL,   gnumeric_table,
 482                         gnumeric_table_link,
 483                         NULL, GNM_FUNC_SIMPLE + GNM_FUNC_INTERNAL,
 484                         GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC,
 485                         GNM_FUNC_TEST_STATUS_EXHAUSTIVE
 486                 },
 487                 {       "number_match", "s|s",
 488                         help_number_match, gnumeric_number_match, NULL,
 489                         NULL, NULL,
 490                         GNM_FUNC_SIMPLE,
 491                         GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC,
 492                         GNM_FUNC_TEST_STATUS_BASIC },
 493                 /* --- Logic --- */
 494                 {       "if", "b|EE",
 495                         help_if, gnumeric_if, NULL,
 496                         NULL, NULL,
 497                         GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_SECOND,
 498                         GNM_FUNC_IMPL_STATUS_COMPLETE,
 499                         GNM_FUNC_TEST_STATUS_BASIC },
 500                 { NULL }
 501         };
 502
 503         gname = N_("Mathematics");
 504         math_group = gnm_func_group_fetch (gname, _(gname));
 505         gnm_func_add (math_group, builtins + i++, textdomain);
 506         gnm_func_add (math_group, builtins + i++, textdomain);
 507
 508         gname = N_("Gnumeric");
 509         gnumeric_group = gnm_func_group_fetch (gname, _(gname));
 510         gnm_func_add (gnumeric_group, builtins + i++, textdomain);
 511         gnm_func_add (gnumeric_group, builtins + i++, textdomain);
 512         if (gnm_debug_flag ("testsuite"))
 513                 gnm_func_add (gnumeric_group, builtins + i, textdomain);
 514         i++;
 515
 516         gname = N_("Logic");
 517         logic_group = gnm_func_group_fetch (gname, _(gname));
 518         gnm_func_add (logic_group, builtins + i++, textdomain);
 519
 520         gnm_expr_deriv_install_handler (gnm_func_lookup ("sum", NULL),
 521                                         gnumeric_sum_deriv,
 522                                         GNM_EXPR_DERIV_NO_CHAIN);
 523 }
 524
 525 static void
 526 shutdown_cat (GnmFuncGroup *group)
 527 {
 528         GSList *ptr, *list = g_slist_copy (group->functions);
 529         for (ptr = list; ptr; ptr = ptr->next)
 530                 gnm_func_free (ptr->data);
 531         g_slist_free (list);
 532 }
 533
 534 void
 535 func_builtin_shutdown (void)
 536 {
 537         shutdown_cat (math_group);
 538         shutdown_cat (gnumeric_group);
 539         shutdown_cat (logic_group);
 540 }