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( Value::Float );
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 + 1);
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 + 1);
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 + 1);
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 bool match = false;
00325 Value result = Value::errorVALUE();
00326 int rows = database.rows() - 1;
00327 for (int r = 0; r < rows; ++r)
00328 if (conds.matches (r)) {
00329 if (match) {
00330
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
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;
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
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
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;
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
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
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;
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
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
00434 Value func_dstdev (valVector args, ValueCalc *calc, FuncExtra *)
00435 {
00436
00437 return calc->sqrt (func_dvar (args, calc, 0));
00438 }
00439
00440
00441 Value func_dstdevp (valVector args, ValueCalc *calc, FuncExtra *)
00442 {
00443
00444 return calc->sqrt (func_dvarp (args, calc, 0));
00445 }
00446
00447
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;
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
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
00478 if (!val.isEmpty ())
00479 res = calc->add (res, calc->sqr (calc->sub (val, avg)));
00480 }
00481
00482
00483 return calc->div (res, count - 1);
00484 }
00485
00486
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;
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
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
00517 if (!val.isEmpty ())
00518 res = calc->add (res, calc->sqr (calc->sub (val, avg)));
00519 }
00520
00521
00522 return calc->div (res, count);
00523 }
00524
00525
00526
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
00534 int row = database.rows() - 1;
00535
00536 return database.element (fieldIndex, row);
00537 }