kspread

kspread_functions_database.cc

00001 /* This file is part of the KDE project
00002    Copyright (C) 1998-2002 The KSpread Team
00003                            www.koffice.org/kspread
00004    Copyright (C) 2005 Tomas Mecir <mecirt@gmail.com>
00005 
00006    This library is free software; you can redistribute it and/or
00007    modify it under the terms of the GNU Library General Public
00008    License as published by the Free Software Foundation; either
00009    version 2 of the License.
00010 
00011    This library is distributed in the hope that it will be useful,
00012    but WITHOUT ANY WARRANTY; without even the implied warranty of
00013    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00014    Library General Public License for more details.
00015 
00016    You should have received a copy of the GNU Library General Public License
00017    along with this library; see the file COPYING.LIB.  If not, write to
00018    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
00019  * Boston, MA 02110-1301, USA.
00020 */
00021 
00022 // built-in database functions
00023 
00024 #include "functions.h"
00025 #include "valuecalc.h"
00026 #include "valueconverter.h"
00027 
00028 using namespace KSpread;
00029 
00030 // prototypes
00031 Value func_daverage (valVector args, ValueCalc *calc, FuncExtra *);
00032 Value func_dcount (valVector args, ValueCalc *calc, FuncExtra *);
00033 Value func_dcounta (valVector args, ValueCalc *calc, FuncExtra *);
00034 Value func_dget (valVector args, ValueCalc *calc, FuncExtra *);
00035 Value func_dmax (valVector args, ValueCalc *calc, FuncExtra *);
00036 Value func_dmin (valVector args, ValueCalc *calc, FuncExtra *);
00037 Value func_dproduct (valVector args, ValueCalc *calc, FuncExtra *);
00038 Value func_dstdev (valVector args, ValueCalc *calc, FuncExtra *);
00039 Value func_dstdevp (valVector args, ValueCalc *calc, FuncExtra *);
00040 Value func_dsum (valVector args, ValueCalc *calc, FuncExtra *);
00041 Value func_dvar (valVector args, ValueCalc *calc, FuncExtra *);
00042 Value func_dvarp (valVector args, ValueCalc *calc, FuncExtra *);
00043 Value func_getpivotdata (valVector args, ValueCalc *calc, FuncExtra *);
00044 
00045 // registers all database functions
00046 void RegisterDatabaseFunctions()
00047 {
00048   FunctionRepository* repo = FunctionRepository::self();
00049   Function *f;
00050 
00051   f = new Function ("DAVERAGE",     func_daverage);
00052   f->setParamCount (3);
00053   f->setAcceptArray ();
00054   repo->add (f);
00055   f = new Function ("DCOUNT",       func_dcount);
00056   f->setParamCount (3);
00057   f->setAcceptArray ();
00058   repo->add (f);
00059   f = new Function ("DCOUNTA",      func_dcounta);
00060   f->setParamCount (3);
00061   f->setAcceptArray ();
00062   repo->add (f);
00063   f = new Function ("DGET",         func_dget);
00064   f->setParamCount (3);
00065   f->setAcceptArray ();
00066   repo->add (f);
00067   f = new Function ("DMAX",         func_dmax);
00068   f->setParamCount (3);
00069   f->setAcceptArray ();
00070   repo->add (f);
00071   f = new Function ("DMIN",         func_dmin);
00072   f->setParamCount (3);
00073   f->setAcceptArray ();
00074   repo->add (f);
00075   f = new Function ("DPRODUCT",     func_dproduct);
00076   f->setParamCount (3);
00077   f->setAcceptArray ();
00078   repo->add (f);
00079   f = new Function ("DSTDEV",       func_dstdev);
00080   f->setParamCount (3);
00081   f->setAcceptArray ();
00082   repo->add (f);
00083   f = new Function ("DSTDEVP",      func_dstdevp);
00084   f->setParamCount (3);
00085   f->setAcceptArray ();
00086   repo->add (f);
00087   f = new Function ("DSUM",         func_dsum);
00088   f->setParamCount (3);
00089   f->setAcceptArray ();
00090   repo->add (f);
00091   f = new Function ("DVAR",         func_dvar);
00092   f->setParamCount (3);
00093   f->setAcceptArray ();
00094   repo->add (f);
00095   f = new Function ("DVARP",        func_dvarp);
00096   f->setParamCount (3);
00097   f->setAcceptArray ();
00098   repo->add (f);
00099   f = new Function ("GETPIVOTDATA", func_getpivotdata); // partially Excel-compatible
00100   f->setParamCount (2);
00101   f->setAcceptArray ();
00102   repo->add (f);
00103 }
00104 
00105 int getFieldIndex (ValueCalc *calc, Value fieldName,
00106     Value database)
00107 {
00108   if (fieldName.isNumber())
00109     return fieldName.asInteger() - 1;
00110   if (!fieldName.isString ())
00111     return -1;
00112 
00113   QString fn = fieldName.asString();
00114   int cols = database.columns ();
00115   for (int i = 0; i < cols; ++i)
00116     if (fn.lower() ==
00117         calc->conv()->asString (database.element (i, 0)).asString().lower())
00118     return i;
00119   return -1;
00120 }
00121 
00122 // ***********************************************************
00123 // *** DBConditions class - maintains an array of conditions ***
00124 // ***********************************************************
00125 
00126 class DBConditions {
00127  public:
00128   DBConditions (ValueCalc *vc, Value database, Value conds);
00129   ~DBConditions ();
00132   bool matches (unsigned row);
00133  private:
00134   void parse (Value conds);
00135   ValueCalc *calc;
00136   Condition **cond;
00137   int rows, cols;
00138   Value db;
00139 };
00140 
00141 DBConditions::DBConditions (ValueCalc *vc, Value database,
00142     Value conds) : calc(vc), cond(0), rows(0), cols(0), db(database)
00143 {
00144   parse (conds);
00145 }
00146 
00147 DBConditions::~DBConditions () {
00148   int count = rows*cols;
00149   for (int r = 0; r < count; ++r)
00150     delete cond[r];
00151   delete[] cond;
00152 }
00153 
00154 void DBConditions::parse (Value conds)
00155 {
00156   // initialize the array
00157   rows = conds.rows() - 1;
00158   cols = db.columns();
00159   int count = rows*cols;
00160   cond = new Condition* [count];
00161   for (int r = 0; r < count; ++r)
00162     cond[r] = 0;
00163 
00164   // perform the parsing itself
00165   int cc = conds.columns ();
00166   for (int c = 0; c < cc; ++c)
00167   {
00168     // first row contains column names
00169     int col = getFieldIndex (calc, conds.element (c, 0), db);
00170     if (col < 0) continue;  // failed - ignore the column
00171 
00172     // fill in the conditions for a given column name
00173     for (int r = 0; r < rows; ++r) {
00174       Value cnd = conds.element (c, r+1);
00175       if (cnd.isEmpty()) continue;
00176       int idx = r * cols + col;
00177       if (cond[idx]) delete cond[idx];
00178       cond[idx] = new Condition;
00179       calc->getCond (*cond[idx], cnd);
00180     }
00181   }
00182 }
00183 
00184 bool DBConditions::matches (unsigned row)
00185 {
00186   if (row >= db.rows() - 1)
00187     return false;    // out of range
00188 
00189   // we have a match, if at least one row of criteria matches
00190   for (int r = 0; r < rows; ++r) {
00191     // within a row, all criteria must match
00192     bool match = true;
00193     for (int c = 0; c < cols; ++c) {
00194       int idx = r * cols + c;
00195       if (!cond[idx]) continue;
00196       if (!calc->matches (*cond[idx], db.element (c, row + 1))) {
00197         match = false;  // didn't match
00198         break;
00199       }
00200     }
00201     if (match)  // all conditions in this row matched
00202       return true;
00203   }
00204 
00205   // no row matched
00206   return false;
00207 }
00208 
00209 
00210 // *******************************************
00211 // *** Function implementations start here ***
00212 // *******************************************
00213 
00214 // Function: DSUM
00215 Value func_dsum (valVector args, ValueCalc *calc, FuncExtra *)
00216 {
00217   Value database = args[0];
00218   Value conditions = args[2];
00219   int fieldIndex = getFieldIndex (calc, args[1], database);
00220   if (fieldIndex < 0)
00221     return Value::errorVALUE();
00222 
00223   DBConditions conds (calc, database, conditions);
00224 
00225   int rows = database.rows() - 1;  // first row contains column names
00226   Value res( Value::Float );
00227   for (int r = 0; r < rows; ++r)
00228     if (conds.matches (r)) {
00229       Value val = database.element (fieldIndex, r + 1);
00230       // include this value in the result
00231       if (!val.isEmpty ())
00232         res = calc->add (res, val);
00233     }
00234 
00235   return res;
00236 }
00237 
00238 // Function: DAVERAGE
00239 Value func_daverage (valVector args, ValueCalc *calc, FuncExtra *)
00240 {
00241   Value database = args[0];
00242   Value conditions = args[2];
00243   int fieldIndex = getFieldIndex (calc, args[1], database);
00244   if (fieldIndex < 0)
00245     return Value::errorVALUE();
00246 
00247   DBConditions conds (calc, database, conditions);
00248 
00249   int rows = database.rows() - 1;  // first row contains column names
00250   Value res;
00251   int count = 0;
00252   for (int r = 0; r < rows; ++r)
00253     if (conds.matches (r)) {
00254       Value val = database.element (fieldIndex, r + 1);
00255       // include this value in the result
00256       if (!val.isEmpty ()) {
00257         res = calc->add (res, val);
00258         count++;
00259       }
00260     }
00261   if (count) res = calc->div (res, count);
00262   return res;
00263 }
00264 
00265 // Function: DCOUNT
00266 Value func_dcount (valVector args, ValueCalc *calc, FuncExtra *)
00267 {
00268   Value database = args[0];
00269   Value conditions = args[2];
00270   int fieldIndex = getFieldIndex (calc, args[1], database);
00271   if (fieldIndex < 0)
00272     return Value::errorVALUE();
00273 
00274   DBConditions conds (calc, database, conditions);
00275 
00276   int rows = database.rows() - 1;  // first row contains column names
00277   int count = 0;
00278   for (int r = 0; r < rows; ++r)
00279     if (conds.matches (r)) {
00280       Value val = database.element (fieldIndex, r + 1);
00281       // include this value in the result
00282       if ((!val.isEmpty()) && (!val.isBoolean()) && (!val.isString()))
00283         count++;
00284     }
00285 
00286   return Value (count);
00287 }
00288 
00289 // Function: DCOUNTA
00290 Value func_dcounta (valVector args, ValueCalc *calc, FuncExtra *)
00291 {
00292   Value database = args[0];
00293   Value conditions = args[2];
00294   int fieldIndex = getFieldIndex (calc, args[1], database);
00295   if (fieldIndex < 0)
00296     return Value::errorVALUE();
00297 
00298   DBConditions conds (calc, database, conditions);
00299 
00300   int rows = database.rows() - 1;  // first row contains column names
00301   int count = 0;
00302   for (int r = 0; r < rows; ++r)
00303     if (conds.matches (r)) {
00304       Value val = database.element (fieldIndex, r + 1);
00305       // include this value in the result
00306       if (!val.isEmpty())
00307         count++;
00308     }
00309 
00310   return Value (count);
00311 }
00312 
00313 // Function: DGET
00314 Value func_dget (valVector args, ValueCalc *calc, FuncExtra *)
00315 {
00316   Value database = args[0];
00317   Value conditions = args[2];
00318   int fieldIndex = getFieldIndex (calc, args[1], database);
00319   if (fieldIndex < 0)
00320     return Value::errorVALUE();
00321 
00322   DBConditions conds (calc, database, conditions);
00323 
00324   bool match = false;
00325   Value result = Value::errorVALUE();
00326   int rows = database.rows() - 1;  // first row contains column names
00327   for (int r = 0; r < rows; ++r)
00328     if (conds.matches (r)) {
00329       if (match) {
00330         // error on multiple matches
00331         result = Value::errorVALUE();
00332         break;
00333       }
00334       result = database.element (fieldIndex, r + 1);
00335       match = true;
00336     }
00337 
00338   return result;
00339 }
00340 
00341 // Function: DMAX
00342 Value func_dmax (valVector args, ValueCalc *calc, FuncExtra *)
00343 {
00344   Value database = args[0];
00345   Value conditions = args[2];
00346   int fieldIndex = getFieldIndex (calc, args[1], database);
00347   if (fieldIndex < 0)
00348     return Value::errorVALUE();
00349 
00350   DBConditions conds (calc, database, conditions);
00351 
00352   int rows = database.rows() - 1;  // first row contains column names
00353   Value res;
00354   bool got = false;
00355   for (int r = 0; r < rows; ++r)
00356     if (conds.matches (r)) {
00357       Value val = database.element (fieldIndex, r + 1);
00358       // include this value in the result
00359       if (!val.isEmpty ()) {
00360         if (!got) {
00361           res = val;
00362           got = true;
00363         }
00364         else
00365           if (calc->greater (val, res))
00366             res = val;
00367       }
00368     }
00369 
00370   return res;
00371 }
00372 
00373 // Function: DMIN
00374 Value func_dmin (valVector args, ValueCalc *calc, FuncExtra *)
00375 {
00376   Value database = args[0];
00377   Value conditions = args[2];
00378   int fieldIndex = getFieldIndex (calc, args[1], database);
00379   if (fieldIndex < 0)
00380     return Value::errorVALUE();
00381 
00382   DBConditions conds (calc, database, conditions);
00383 
00384   int rows = database.rows() - 1;  // first row contains column names
00385   Value res;
00386   bool got = false;
00387   for (int r = 0; r < rows; ++r)
00388     if (conds.matches (r)) {
00389       Value val = database.element (fieldIndex, r + 1);
00390       // include this value in the result
00391       if (!val.isEmpty ()) {
00392         if (!got) {
00393           res = val;
00394           got = true;
00395         }
00396         else
00397           if (calc->lower (val, res))
00398             res = val;
00399       }
00400     }
00401 
00402   return res;
00403 }
00404 
00405 // Function: DPRODUCT
00406 Value func_dproduct (valVector args, ValueCalc *calc, FuncExtra *)
00407 {
00408   Value database = args[0];
00409   Value conditions = args[2];
00410   int fieldIndex = getFieldIndex (calc, args[1], database);
00411   if (fieldIndex < 0)
00412     return Value::errorVALUE();
00413 
00414   DBConditions conds (calc, database, conditions);
00415 
00416   int rows = database.rows() - 1;  // first row contains column names
00417   Value res = 1.0;
00418   bool got = false;
00419   for (int r = 0; r < rows; ++r)
00420     if (conds.matches (r)) {
00421       Value val = database.element (fieldIndex, r + 1);
00422       // include this value in the result
00423       if (!val.isEmpty ()) {
00424         got = true;
00425         res = calc->mul (res, val);
00426       }
00427     }
00428   if (got)
00429     return res;
00430   return Value::errorVALUE ();
00431 }
00432 
00433 // Function: DSTDEV
00434 Value func_dstdev (valVector args, ValueCalc *calc, FuncExtra *)
00435 {
00436   // sqrt (dvar)
00437   return calc->sqrt (func_dvar (args, calc, 0));
00438 }
00439 
00440 // Function: DSTDEVP
00441 Value func_dstdevp (valVector args, ValueCalc *calc, FuncExtra *)
00442 {
00443   // sqrt (dvarp)
00444   return calc->sqrt (func_dvarp (args, calc, 0));
00445 }
00446 
00447 // Function: DVAR
00448 Value func_dvar (valVector args, ValueCalc *calc, FuncExtra *)
00449 {
00450   Value database = args[0];
00451   Value conditions = args[2];
00452   int fieldIndex = getFieldIndex (calc, args[1], database);
00453   if (fieldIndex < 0)
00454     return Value::errorVALUE();
00455 
00456   DBConditions conds (calc, database, conditions);
00457 
00458   int rows = database.rows() - 1;  // first row contains column names
00459   Value avg;
00460   int count = 0;
00461   for (int r = 0; r < rows; ++r)
00462     if (conds.matches (r)) {
00463       Value val = database.element (fieldIndex, r + 1);
00464       // include this value in the result
00465       if (!val.isEmpty ()) {
00466         avg = calc->add (avg, val);
00467         count++;
00468       }
00469     }
00470   if (count < 2) return Value::errorDIV0();
00471   avg = calc->div (avg, count);
00472 
00473   Value res;
00474   for (int r = 0; r < rows; ++r)
00475     if (conds.matches (r)) {
00476       Value val = database.element (fieldIndex, r + 1);
00477       // include this value in the result
00478       if (!val.isEmpty ())
00479         res = calc->add (res, calc->sqr (calc->sub (val, avg)));
00480     }
00481 
00482   // res / (count-1)
00483   return calc->div (res, count - 1);
00484 }
00485 
00486 // Function: DVARP
00487 Value func_dvarp (valVector args, ValueCalc *calc, FuncExtra *)
00488 {
00489   Value database = args[0];
00490   Value conditions = args[2];
00491   int fieldIndex = getFieldIndex (calc, args[1], database);
00492   if (fieldIndex < 0)
00493     return Value::errorVALUE();
00494 
00495   DBConditions conds (calc, database, conditions);
00496 
00497   int rows = database.rows() - 1;  // first row contains column names
00498   Value avg;
00499   int count = 0;
00500   for (int r = 0; r < rows; ++r)
00501     if (conds.matches (r)) {
00502       Value val = database.element (fieldIndex, r + 1);
00503       // include this value in the result
00504       if (!val.isEmpty ()) {
00505         avg = calc->add (avg, val);
00506         count++;
00507       }
00508     }
00509   if (count == 0) return Value::errorDIV0();
00510   avg = calc->div (avg, count);
00511 
00512   Value res;
00513   for (int r = 0; r < rows; ++r)
00514     if (conds.matches (r)) {
00515       Value val = database.element (fieldIndex, r + 1);
00516       // include this value in the result
00517       if (!val.isEmpty ())
00518         res = calc->add (res, calc->sqr (calc->sub (val, avg)));
00519     }
00520 
00521   // res / count
00522   return calc->div (res, count);
00523 }
00524 
00525 // Function: GETPIVOTDATA
00526 // FIXME implement more things with this, see Excel !
00527 Value func_getpivotdata (valVector args, ValueCalc *calc, FuncExtra *)
00528 {
00529   Value database = args[0];
00530   int fieldIndex = getFieldIndex (calc, args[1], database);
00531   if (fieldIndex < 0)
00532     return Value::errorVALUE();
00533   // the row at the bottom
00534   int row = database.rows() - 1;
00535 
00536   return database.element (fieldIndex, row);
00537 }
KDE Home | KDE Accessibility Home | Description of Access Keys