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