KOffice – TDE office suite
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

538 lines
15KB

  1. /* This file is part of the KDE project
  2. Copyright (C) 1998-2002 The KSpread Team
  3. www.koffice.org/kspread
  4. Copyright (C) 2005 Tomas Mecir <mecirt@gmail.com>
  5. This library is free software; you can redistribute it and/or
  6. modify it under the terms of the GNU Library General Public
  7. License as published by the Free Software Foundation; either
  8. version 2 of the License.
  9. This library is distributed in the hope that it will be useful,
  10. but WITHOUT ANY WARRANTY; without even the implied warranty of
  11. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  12. Library General Public License for more details.
  13. You should have received a copy of the GNU Library General Public License
  14. along with this library; see the file COPYING.LIB. If not, write to
  15. the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
  16. * Boston, MA 02110-1301, USA.
  17. */
  18. // built-in database functions
  19. #include "functions.h"
  20. #include "valuecalc.h"
  21. #include "valueconverter.h"
  22. using namespace KSpread;
  23. // prototypes
  24. Value func_daverage (valVector args, ValueCalc *calc, FuncExtra *);
  25. Value func_dcount (valVector args, ValueCalc *calc, FuncExtra *);
  26. Value func_dcounta (valVector args, ValueCalc *calc, FuncExtra *);
  27. Value func_dget (valVector args, ValueCalc *calc, FuncExtra *);
  28. Value func_dmax (valVector args, ValueCalc *calc, FuncExtra *);
  29. Value func_dmin (valVector args, ValueCalc *calc, FuncExtra *);
  30. Value func_dproduct (valVector args, ValueCalc *calc, FuncExtra *);
  31. Value func_dstdev (valVector args, ValueCalc *calc, FuncExtra *);
  32. Value func_dstdevp (valVector args, ValueCalc *calc, FuncExtra *);
  33. Value func_dsum (valVector args, ValueCalc *calc, FuncExtra *);
  34. Value func_dvar (valVector args, ValueCalc *calc, FuncExtra *);
  35. Value func_dvarp (valVector args, ValueCalc *calc, FuncExtra *);
  36. Value func_getpivotdata (valVector args, ValueCalc *calc, FuncExtra *);
  37. // registers all database functions
  38. void RegisterDatabaseFunctions()
  39. {
  40. FunctionRepository* repo = FunctionRepository::self();
  41. Function *f;
  42. f = new Function ("DAVERAGE", func_daverage);
  43. f->setParamCount (3);
  44. f->setAcceptArray ();
  45. repo->add (f);
  46. f = new Function ("DCOUNT", func_dcount);
  47. f->setParamCount (3);
  48. f->setAcceptArray ();
  49. repo->add (f);
  50. f = new Function ("DCOUNTA", func_dcounta);
  51. f->setParamCount (3);
  52. f->setAcceptArray ();
  53. repo->add (f);
  54. f = new Function ("DGET", func_dget);
  55. f->setParamCount (3);
  56. f->setAcceptArray ();
  57. repo->add (f);
  58. f = new Function ("DMAX", func_dmax);
  59. f->setParamCount (3);
  60. f->setAcceptArray ();
  61. repo->add (f);
  62. f = new Function ("DMIN", func_dmin);
  63. f->setParamCount (3);
  64. f->setAcceptArray ();
  65. repo->add (f);
  66. f = new Function ("DPRODUCT", func_dproduct);
  67. f->setParamCount (3);
  68. f->setAcceptArray ();
  69. repo->add (f);
  70. f = new Function ("DSTDEV", func_dstdev);
  71. f->setParamCount (3);
  72. f->setAcceptArray ();
  73. repo->add (f);
  74. f = new Function ("DSTDEVP", func_dstdevp);
  75. f->setParamCount (3);
  76. f->setAcceptArray ();
  77. repo->add (f);
  78. f = new Function ("DSUM", func_dsum);
  79. f->setParamCount (3);
  80. f->setAcceptArray ();
  81. repo->add (f);
  82. f = new Function ("DVAR", func_dvar);
  83. f->setParamCount (3);
  84. f->setAcceptArray ();
  85. repo->add (f);
  86. f = new Function ("DVARP", func_dvarp);
  87. f->setParamCount (3);
  88. f->setAcceptArray ();
  89. repo->add (f);
  90. f = new Function ("GETPIVOTDATA", func_getpivotdata); // partially Excel-compatible
  91. f->setParamCount (2);
  92. f->setAcceptArray ();
  93. repo->add (f);
  94. }
  95. int getFieldIndex (ValueCalc *calc, Value fieldName,
  96. Value database)
  97. {
  98. if (fieldName.isNumber())
  99. return fieldName.asInteger() - 1;
  100. if (!fieldName.isString ())
  101. return -1;
  102. TQString fn = fieldName.asString();
  103. int cols = database.columns ();
  104. for (int i = 0; i < cols; ++i)
  105. if (fn.lower() ==
  106. calc->conv()->asString (database.element (i, 0)).asString().lower())
  107. return i;
  108. return -1;
  109. }
  110. // ***********************************************************
  111. // *** DBConditions class - maintains an array of conditions ***
  112. // ***********************************************************
  113. class DBConditions {
  114. public:
  115. DBConditions (ValueCalc *vc, Value database, Value conds);
  116. ~DBConditions ();
  117. /** Does a specified row of the database match the given criteria?
  118. The row with column names is ignored - hence 0 specifies first data row. */
  119. bool matches (unsigned row);
  120. private:
  121. void parse (Value conds);
  122. ValueCalc *calc;
  123. Condition **cond;
  124. int rows, cols;
  125. Value db;
  126. };
  127. DBConditions::DBConditions (ValueCalc *vc, Value database,
  128. Value conds) : calc(vc), cond(0), rows(0), cols(0), db(database)
  129. {
  130. parse (conds);
  131. }
  132. DBConditions::~DBConditions () {
  133. int count = rows*cols;
  134. for (int r = 0; r < count; ++r)
  135. delete cond[r];
  136. delete[] cond;
  137. }
  138. void DBConditions::parse (Value conds)
  139. {
  140. // initialize the array
  141. rows = conds.rows() - 1;
  142. cols = db.columns();
  143. int count = rows*cols;
  144. cond = new Condition* [count];
  145. for (int r = 0; r < count; ++r)
  146. cond[r] = 0;
  147. // perform the parsing itself
  148. int cc = conds.columns ();
  149. for (int c = 0; c < cc; ++c)
  150. {
  151. // first row contains column names
  152. int col = getFieldIndex (calc, conds.element (c, 0), db);
  153. if (col < 0) continue; // failed - ignore the column
  154. // fill in the conditions for a given column name
  155. for (int r = 0; r < rows; ++r) {
  156. Value cnd = conds.element (c, r+1);
  157. if (cnd.isEmpty()) continue;
  158. int idx = r * cols + col;
  159. if (cond[idx]) delete cond[idx];
  160. cond[idx] = new Condition;
  161. calc->getCond (*cond[idx], cnd);
  162. }
  163. }
  164. }
  165. bool DBConditions::matches (unsigned row)
  166. {
  167. if (row >= db.rows() - 1)
  168. return false; // out of range
  169. // we have a match, if at least one row of criteria matches
  170. for (int r = 0; r < rows; ++r) {
  171. // within a row, all criteria must match
  172. bool match = true;
  173. for (int c = 0; c < cols; ++c) {
  174. int idx = r * cols + c;
  175. if (!cond[idx]) continue;
  176. if (!calc->matches (*cond[idx], db.element (c, row + 1))) {
  177. match = false; // didn't match
  178. break;
  179. }
  180. }
  181. if (match) // all conditions in this row matched
  182. return true;
  183. }
  184. // no row matched
  185. return false;
  186. }
  187. // *******************************************
  188. // *** Function implementations start here ***
  189. // *******************************************
  190. // Function: DSUM
  191. Value func_dsum (valVector args, ValueCalc *calc, FuncExtra *)
  192. {
  193. Value database = args[0];
  194. Value conditions = args[2];
  195. int fieldIndex = getFieldIndex (calc, args[1], database);
  196. if (fieldIndex < 0)
  197. return Value::errorVALUE();
  198. DBConditions conds (calc, database, conditions);
  199. int rows = database.rows() - 1; // first row contains column names
  200. Value res( Value::Float );
  201. for (int r = 0; r < rows; ++r)
  202. if (conds.matches (r)) {
  203. Value val = database.element (fieldIndex, r + 1);
  204. // include this value in the result
  205. if (!val.isEmpty ())
  206. res = calc->add (res, val);
  207. }
  208. return res;
  209. }
  210. // Function: DAVERAGE
  211. Value func_daverage (valVector args, ValueCalc *calc, FuncExtra *)
  212. {
  213. Value database = args[0];
  214. Value conditions = args[2];
  215. int fieldIndex = getFieldIndex (calc, args[1], database);
  216. if (fieldIndex < 0)
  217. return Value::errorVALUE();
  218. DBConditions conds (calc, database, conditions);
  219. int rows = database.rows() - 1; // first row contains column names
  220. Value res;
  221. int count = 0;
  222. for (int r = 0; r < rows; ++r)
  223. if (conds.matches (r)) {
  224. Value val = database.element (fieldIndex, r + 1);
  225. // include this value in the result
  226. if (!val.isEmpty ()) {
  227. res = calc->add (res, val);
  228. count++;
  229. }
  230. }
  231. if (count) res = calc->div (res, count);
  232. return res;
  233. }
  234. // Function: DCOUNT
  235. Value func_dcount (valVector args, ValueCalc *calc, FuncExtra *)
  236. {
  237. Value database = args[0];
  238. Value conditions = args[2];
  239. int fieldIndex = getFieldIndex (calc, args[1], database);
  240. if (fieldIndex < 0)
  241. return Value::errorVALUE();
  242. DBConditions conds (calc, database, conditions);
  243. int rows = database.rows() - 1; // first row contains column names
  244. int count = 0;
  245. for (int r = 0; r < rows; ++r)
  246. if (conds.matches (r)) {
  247. Value val = database.element (fieldIndex, r + 1);
  248. // include this value in the result
  249. if ((!val.isEmpty()) && (!val.isBoolean()) && (!val.isString()))
  250. count++;
  251. }
  252. return Value (count);
  253. }
  254. // Function: DCOUNTA
  255. Value func_dcounta (valVector args, ValueCalc *calc, FuncExtra *)
  256. {
  257. Value database = args[0];
  258. Value conditions = args[2];
  259. int fieldIndex = getFieldIndex (calc, args[1], database);
  260. if (fieldIndex < 0)
  261. return Value::errorVALUE();
  262. DBConditions conds (calc, database, conditions);
  263. int rows = database.rows() - 1; // first row contains column names
  264. int count = 0;
  265. for (int r = 0; r < rows; ++r)
  266. if (conds.matches (r)) {
  267. Value val = database.element (fieldIndex, r + 1);
  268. // include this value in the result
  269. if (!val.isEmpty())
  270. count++;
  271. }
  272. return Value (count);
  273. }
  274. // Function: DGET
  275. Value func_dget (valVector args, ValueCalc *calc, FuncExtra *)
  276. {
  277. Value database = args[0];
  278. Value conditions = args[2];
  279. int fieldIndex = getFieldIndex (calc, args[1], database);
  280. if (fieldIndex < 0)
  281. return Value::errorVALUE();
  282. DBConditions conds (calc, database, conditions);
  283. bool match = false;
  284. Value result = Value::errorVALUE();
  285. int rows = database.rows() - 1; // first row contains column names
  286. for (int r = 0; r < rows; ++r)
  287. if (conds.matches (r)) {
  288. if (match) {
  289. // error on multiple matches
  290. result = Value::errorVALUE();
  291. break;
  292. }
  293. result = database.element (fieldIndex, r + 1);
  294. match = true;
  295. }
  296. return result;
  297. }
  298. // Function: DMAX
  299. Value func_dmax (valVector args, ValueCalc *calc, FuncExtra *)
  300. {
  301. Value database = args[0];
  302. Value conditions = args[2];
  303. int fieldIndex = getFieldIndex (calc, args[1], database);
  304. if (fieldIndex < 0)
  305. return Value::errorVALUE();
  306. DBConditions conds (calc, database, conditions);
  307. int rows = database.rows() - 1; // first row contains column names
  308. Value res;
  309. bool got = false;
  310. for (int r = 0; r < rows; ++r)
  311. if (conds.matches (r)) {
  312. Value val = database.element (fieldIndex, r + 1);
  313. // include this value in the result
  314. if (!val.isEmpty ()) {
  315. if (!got) {
  316. res = val;
  317. got = true;
  318. }
  319. else
  320. if (calc->greater (val, res))
  321. res = val;
  322. }
  323. }
  324. return res;
  325. }
  326. // Function: DMIN
  327. Value func_dmin (valVector args, ValueCalc *calc, FuncExtra *)
  328. {
  329. Value database = args[0];
  330. Value conditions = args[2];
  331. int fieldIndex = getFieldIndex (calc, args[1], database);
  332. if (fieldIndex < 0)
  333. return Value::errorVALUE();
  334. DBConditions conds (calc, database, conditions);
  335. int rows = database.rows() - 1; // first row contains column names
  336. Value res;
  337. bool got = false;
  338. for (int r = 0; r < rows; ++r)
  339. if (conds.matches (r)) {
  340. Value val = database.element (fieldIndex, r + 1);
  341. // include this value in the result
  342. if (!val.isEmpty ()) {
  343. if (!got) {
  344. res = val;
  345. got = true;
  346. }
  347. else
  348. if (calc->lower (val, res))
  349. res = val;
  350. }
  351. }
  352. return res;
  353. }
  354. // Function: DPRODUCT
  355. Value func_dproduct (valVector args, ValueCalc *calc, FuncExtra *)
  356. {
  357. Value database = args[0];
  358. Value conditions = args[2];
  359. int fieldIndex = getFieldIndex (calc, args[1], database);
  360. if (fieldIndex < 0)
  361. return Value::errorVALUE();
  362. DBConditions conds (calc, database, conditions);
  363. int rows = database.rows() - 1; // first row contains column names
  364. Value res = 1.0;
  365. bool got = false;
  366. for (int r = 0; r < rows; ++r)
  367. if (conds.matches (r)) {
  368. Value val = database.element (fieldIndex, r + 1);
  369. // include this value in the result
  370. if (!val.isEmpty ()) {
  371. got = true;
  372. res = calc->mul (res, val);
  373. }
  374. }
  375. if (got)
  376. return res;
  377. return Value::errorVALUE ();
  378. }
  379. // Function: DSTDEV
  380. Value func_dstdev (valVector args, ValueCalc *calc, FuncExtra *)
  381. {
  382. // sqrt (dvar)
  383. return calc->sqrt (func_dvar (args, calc, 0));
  384. }
  385. // Function: DSTDEVP
  386. Value func_dstdevp (valVector args, ValueCalc *calc, FuncExtra *)
  387. {
  388. // sqrt (dvarp)
  389. return calc->sqrt (func_dvarp (args, calc, 0));
  390. }
  391. // Function: DVAR
  392. Value func_dvar (valVector args, ValueCalc *calc, FuncExtra *)
  393. {
  394. Value database = args[0];
  395. Value conditions = args[2];
  396. int fieldIndex = getFieldIndex (calc, args[1], database);
  397. if (fieldIndex < 0)
  398. return Value::errorVALUE();
  399. DBConditions conds (calc, database, conditions);
  400. int rows = database.rows() - 1; // first row contains column names
  401. Value avg;
  402. int count = 0;
  403. for (int r = 0; r < rows; ++r)
  404. if (conds.matches (r)) {
  405. Value val = database.element (fieldIndex, r + 1);
  406. // include this value in the result
  407. if (!val.isEmpty ()) {
  408. avg = calc->add (avg, val);
  409. count++;
  410. }
  411. }
  412. if (count < 2) return Value::errorDIV0();
  413. avg = calc->div (avg, count);
  414. Value res;
  415. for (int r = 0; r < rows; ++r)
  416. if (conds.matches (r)) {
  417. Value val = database.element (fieldIndex, r + 1);
  418. // include this value in the result
  419. if (!val.isEmpty ())
  420. res = calc->add (res, calc->sqr (calc->sub (val, avg)));
  421. }
  422. // res / (count-1)
  423. return calc->div (res, count - 1);
  424. }
  425. // Function: DVARP
  426. Value func_dvarp (valVector args, ValueCalc *calc, FuncExtra *)
  427. {
  428. Value database = args[0];
  429. Value conditions = args[2];
  430. int fieldIndex = getFieldIndex (calc, args[1], database);
  431. if (fieldIndex < 0)
  432. return Value::errorVALUE();
  433. DBConditions conds (calc, database, conditions);
  434. int rows = database.rows() - 1; // first row contains column names
  435. Value avg;
  436. int count = 0;
  437. for (int r = 0; r < rows; ++r)
  438. if (conds.matches (r)) {
  439. Value val = database.element (fieldIndex, r + 1);
  440. // include this value in the result
  441. if (!val.isEmpty ()) {
  442. avg = calc->add (avg, val);
  443. count++;
  444. }
  445. }
  446. if (count == 0) return Value::errorDIV0();
  447. avg = calc->div (avg, count);
  448. Value res;
  449. for (int r = 0; r < rows; ++r)
  450. if (conds.matches (r)) {
  451. Value val = database.element (fieldIndex, r + 1);
  452. // include this value in the result
  453. if (!val.isEmpty ())
  454. res = calc->add (res, calc->sqr (calc->sub (val, avg)));
  455. }
  456. // res / count
  457. return calc->div (res, count);
  458. }
  459. // Function: GETPIVOTDATA
  460. // FIXME implement more things with this, see Excel !
  461. Value func_getpivotdata (valVector args, ValueCalc *calc, FuncExtra *)
  462. {
  463. Value database = args[0];
  464. int fieldIndex = getFieldIndex (calc, args[1], database);
  465. if (fieldIndex < 0)
  466. return Value::errorVALUE();
  467. // the row at the bottom
  468. int row = database.rows() - 1;
  469. return database.element (fieldIndex, row);
  470. }