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;
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);
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);
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);
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   int rows = database.rows() - 1;  // first row contains column names
00325   for (int r = 0; r < rows; ++r)
00326     if (conds.matches (r)) {
00327       Value val = database.element (fieldIndex, r);
00328       return val;
00329     }
00330 
00331   // no match
00332   return Value::errorVALUE();
00333 }
00334 
00335 // Function: DMAX
00336 Value func_dmax (valVector args, ValueCalc *calc, FuncExtra *)
00337 {
00338   Value database = args[0];
00339   Value conditions = args[2];
00340   int fieldIndex = getFieldIndex (calc, args[1], database);
00341   if (fieldIndex < 0)
00342     return Value::errorVALUE();
00343 
00344   DBConditions conds (calc, database, conditions);
00345 
00346   int rows = database.rows() - 1;  // first row contains column names
00347   Value res;
00348   bool got = false;
00349   for (int r = 0; r < rows; ++r)
00350     if (conds.matches (r)) {
00351       Value val = database.element (fieldIndex, r);
00352       // include this value in the result
00353       if (!val.isEmpty ()) {
00354         if (!got) {
00355           res = val;
00356           got = true;
00357         }
00358         else
00359           if (calc->greater (val, res))
00360             res = val;
00361       }
00362     }
00363 
00364   return res;
00365 }
00366 
00367 // Function: DMIN
00368 Value func_dmin (valVector args, ValueCalc *calc, FuncExtra *)
00369 {
00370   Value database = args[0];
00371   Value conditions = args[2];
00372   int fieldIndex = getFieldIndex (calc, args[1], database);
00373   if (fieldIndex < 0)
00374     return Value::errorVALUE();
00375 
00376   DBConditions conds (calc, database, conditions);
00377 
00378   int rows = database.rows() - 1;  // first row contains column names
00379   Value res;
00380   bool got = false;
00381   for (int r = 0; r < rows; ++r)
00382     if (conds.matches (r)) {
00383       Value val = database.element (fieldIndex, r);
00384       // include this value in the result
00385       if (!val.isEmpty ()) {
00386         if (!got) {
00387           res = val;
00388           got = true;
00389         }
00390         else
00391           if (calc->lower (val, res))
00392             res = val;
00393       }
00394     }
00395 
00396   return res;
00397 }
00398 
00399 // Function: DPRODUCT
00400 Value func_dproduct (valVector args, ValueCalc *calc, FuncExtra *)
00401 {
00402   Value database = args[0];
00403   Value conditions = args[2];
00404   int fieldIndex = getFieldIndex (calc, args[1], database);
00405   if (fieldIndex < 0)
00406     return Value::errorVALUE();
00407 
00408   DBConditions conds (calc, database, conditions);
00409 
00410   int rows = database.rows() - 1;  // first row contains column names
00411   Value res = 1.0;
00412   bool got = false;
00413   for (int r = 0; r < rows; ++r)
00414     if (conds.matches (r)) {
00415       Value val = database.element (fieldIndex, r);
00416       // include this value in the result
00417       if (!val.isEmpty ()) {
00418         got = true;
00419         res = calc->mul (res, val);
00420       }
00421     }
00422   if (got)
00423     return res;
00424   return Value::errorVALUE ();
00425 }
00426 
00427 // Function: DSTDEV
00428 Value func_dstdev (valVector args, ValueCalc *calc, FuncExtra *)
00429 {
00430   // sqrt (dvar)
00431   return calc->sqrt (func_dvar (args, calc, 0));
00432 }
00433 
00434 // Function: DSTDEVP
00435 Value func_dstdevp (valVector args, ValueCalc *calc, FuncExtra *)
00436 {
00437   // sqrt (dvarp)
00438   return calc->sqrt (func_dvarp (args, calc, 0));
00439 }
00440 
00441 // Function: DVAR
00442 Value func_dvar (valVector args, ValueCalc *calc, FuncExtra *)
00443 {
00444   Value database = args[0];
00445   Value conditions = args[2];
00446   int fieldIndex = getFieldIndex (calc, args[1], database);
00447   if (fieldIndex < 0)
00448     return Value::errorVALUE();
00449 
00450   DBConditions conds (calc, database, conditions);
00451 
00452   int rows = database.rows() - 1;  // first row contains column names
00453   Value avg;
00454   int count = 0;
00455   for (int r = 0; r < rows; ++r)
00456     if (conds.matches (r)) {
00457       Value val = database.element (fieldIndex, r);
00458       // include this value in the result
00459       if (!val.isEmpty ()) {
00460         avg = calc->add (avg, val);
00461         count++;
00462       }
00463     }
00464   if (count < 2) return Value::errorDIV0();
00465   avg = calc->div (avg, count);
00466 
00467   Value res;
00468   for (int r = 0; r < rows; ++r)
00469     if (conds.matches (r)) {
00470       Value val = database.element (fieldIndex, r);
00471       // include this value in the result
00472       if (!val.isEmpty ())
00473         res = calc->add (res, calc->sqr (calc->sub (val, avg)));
00474     }
00475 
00476   // res / (count-1)
00477   return calc->div (res, count - 1);
00478 }
00479 
00480 // Function: DVARP
00481 Value func_dvarp (valVector args, ValueCalc *calc, FuncExtra *)
00482 {
00483   Value database = args[0];
00484   Value conditions = args[2];
00485   int fieldIndex = getFieldIndex (calc, args[1], database);
00486   if (fieldIndex < 0)
00487     return Value::errorVALUE();
00488 
00489   DBConditions conds (calc, database, conditions);
00490 
00491   int rows = database.rows() - 1;  // first row contains column names
00492   Value avg;
00493   int count = 0;
00494   for (int r = 0; r < rows; ++r)
00495     if (conds.matches (r)) {
00496       Value val = database.element (fieldIndex, r);
00497       // include this value in the result
00498       if (!val.isEmpty ()) {
00499         avg = calc->add (avg, val);
00500         count++;
00501       }
00502     }
00503   if (count == 0) return Value::errorDIV0();
00504   avg = calc->div (avg, count);
00505 
00506   Value res;
00507   for (int r = 0; r < rows; ++r)
00508     if (conds.matches (r)) {
00509       Value val = database.element (fieldIndex, r);
00510       // include this value in the result
00511       if (!val.isEmpty ())
00512         res = calc->add (res, calc->sqr (calc->sub (val, avg)));
00513     }
00514 
00515   // res / count
00516   return calc->div (res, count);
00517 }
00518 
00519 // Function: GETPIVOTDATA
00520 // FIXME implement more things with this, see Excel !
00521 Value func_getpivotdata (valVector args, ValueCalc *calc, FuncExtra *)
00522 {
00523   Value database = args[0];
00524   int fieldIndex = getFieldIndex (calc, args[1], database);
00525   if (fieldIndex < 0)
00526     return Value::errorVALUE();
00527   // the row at the bottom
00528   int row = database.rows() - 1;
00529 
00530   return database.element (fieldIndex, row);
00531 }
KDE Home | KDE Accessibility Home | Description of Access Keys