00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024 #include "functions.h"
00025 #include "valuecalc.h"
00026 #include "valueconverter.h"
00027
00028 using namespace KSpread;
00029
00030
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
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);
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
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
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
00165 int cc = conds.columns ();
00166 for (int c = 0; c < cc; ++c)
00167 {
00168
00169 int col = getFieldIndex (calc, conds.element (c, 0), db);
00170 if (col < 0) continue;
00171
00172
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;
00188
00189
00190 for (int r = 0; r < rows; ++r) {
00191
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;
00198 break;
00199 }
00200 }
00201 if (match)
00202 return true;
00203 }
00204
00205
00206 return false;
00207 }
00208
00209
00210
00211
00212
00213
00214
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;
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
00231 if (!val.isEmpty ())
00232 res = calc->add (res, val);
00233 }
00234
00235 return res;
00236 }
00237
00238
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;
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
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
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;
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
00282 if ((!val.isEmpty()) && (!val.isBoolean()) && (!val.isString()))
00283 count++;
00284 }
00285
00286 return Value (count);
00287 }
00288
00289
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;
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
00306 if (!val.isEmpty())
00307 count++;
00308 }
00309
00310 return Value (count);
00311 }
00312
00313
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;
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
00332 return Value::errorVALUE();
00333 }
00334
00335
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;
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
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
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;
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
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
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;
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
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
00428 Value func_dstdev (valVector args, ValueCalc *calc, FuncExtra *)
00429 {
00430
00431 return calc->sqrt (func_dvar (args, calc, 0));
00432 }
00433
00434
00435 Value func_dstdevp (valVector args, ValueCalc *calc, FuncExtra *)
00436 {
00437
00438 return calc->sqrt (func_dvarp (args, calc, 0));
00439 }
00440
00441
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;
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
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
00472 if (!val.isEmpty ())
00473 res = calc->add (res, calc->sqr (calc->sub (val, avg)));
00474 }
00475
00476
00477 return calc->div (res, count - 1);
00478 }
00479
00480
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;
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
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
00511 if (!val.isEmpty ())
00512 res = calc->add (res, calc->sqr (calc->sub (val, avg)));
00513 }
00514
00515
00516 return calc->div (res, count);
00517 }
00518
00519
00520
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
00528 int row = database.rows() - 1;
00529
00530 return database.element (fieldIndex, row);
00531 }