grid.pivot.js 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505
  1. /*jshint eqeqeq:false */
  2. /*global jQuery */
  3. (function($){
  4. /**
  5. * jqGrid pivot functions
  6. * Tony Tomov tony@trirand.com
  7. * http://trirand.com/blog/
  8. * Dual licensed under the MIT and GPL licenses:
  9. * http://www.opensource.org/licenses/mit-license.php
  10. * http://www.gnu.org/licenses/gpl-2.0.html
  11. */
  12. "use strict";
  13. // To optimize the search we need custom array filter
  14. // This code is taken from
  15. // https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/filter
  16. function _pivotfilter (fn, context) {
  17. var i,
  18. value,
  19. result = [],
  20. length;
  21. if (!this || typeof fn !== 'function' || (fn instanceof RegExp)) {
  22. throw new TypeError();
  23. }
  24. length = this.length;
  25. for (i = 0; i < length; i++) {
  26. if (this.hasOwnProperty(i)) {
  27. value = this[i];
  28. if (fn.call(context, value, i, this)) {
  29. result.push(value);
  30. // We need break in order to cancel loop
  31. // in case the row is found
  32. break;
  33. }
  34. }
  35. }
  36. return result;
  37. }
  38. $.assocArraySize = function(obj) {
  39. // http://stackoverflow.com/a/6700/11236
  40. var size = 0, key;
  41. for (key in obj) {
  42. if (obj.hasOwnProperty(key)) {
  43. size++;
  44. }
  45. }
  46. return size;
  47. };
  48. $.jgrid.extend({
  49. pivotSetup : function( data, options ){
  50. // data should come in json format
  51. // The function return the new colModel and the transformed data
  52. // again with group setup options which then will be passed to the grid
  53. var columns =[],
  54. pivotrows =[],
  55. summaries = [],
  56. member=[],
  57. labels=[],
  58. groupOptions = {
  59. grouping : true,
  60. groupingView : {
  61. groupField : [],
  62. groupSummary: [],
  63. groupSummaryPos:[]
  64. }
  65. },
  66. headers = [],
  67. o = $.extend ( {
  68. rowTotals : false,
  69. rowTotalsText : 'Total',
  70. // summary columns
  71. colTotals : false,
  72. groupSummary : true,
  73. groupSummaryPos : 'header',
  74. frozenStaticCols : false
  75. }, options || {});
  76. this.each(function(){
  77. var
  78. row,
  79. rowindex,
  80. i,
  81. rowlen = data.length,
  82. xlen, ylen, aggrlen,
  83. tmp,
  84. newObj,
  85. r=0;
  86. // utility funcs
  87. /*
  88. * Filter the data to a given criteria. Return the firt occurance
  89. */
  90. function find(ar, fun, extra) {
  91. var res;
  92. res = _pivotfilter.call(ar, fun, extra);
  93. return res.length > 0 ? res[0] : null;
  94. }
  95. /*
  96. * Check if the grouped row column exist (See find)
  97. * If the row is not find in pivot rows retun null,
  98. * otherviese the column
  99. */
  100. function findGroup(item, index) {
  101. var j = 0, ret = true, i;
  102. for(i in item) {
  103. if(item[i] != this[j]) {
  104. ret = false;
  105. break;
  106. }
  107. j++;
  108. if(j>=this.length) {
  109. break;
  110. }
  111. }
  112. if(ret) {
  113. rowindex = index;
  114. }
  115. return ret;
  116. }
  117. /*
  118. * Perform calculations of the pivot values.
  119. */
  120. function calculation(oper, v, field, rc) {
  121. var ret;
  122. switch (oper) {
  123. case "sum" :
  124. ret = parseFloat(v||0) + parseFloat((rc[field]||0));
  125. break;
  126. case "count" :
  127. if(v==="" || v == null) {
  128. v=0;
  129. }
  130. if(rc.hasOwnProperty(field)) {
  131. ret = v+1;
  132. } else {
  133. ret = 0;
  134. }
  135. break;
  136. case "min" :
  137. if(v==="" || v == null) {
  138. ret = parseFloat(rc[field]||0);
  139. } else {
  140. ret =Math.min(parseFloat(v),parseFloat(rc[field]||0));
  141. }
  142. break;
  143. case "max" :
  144. if(v==="" || v == null) {
  145. ret = parseFloat(rc[field]||0);
  146. } else {
  147. ret = Math.max(parseFloat(v),parseFloat(rc[field]||0));
  148. }
  149. break;
  150. }
  151. return ret;
  152. }
  153. /*
  154. * The function agragates the values of the pivot grid.
  155. * Return the current row with pivot summary values
  156. */
  157. function agregateFunc ( row, aggr, value, curr) {
  158. // default is sum
  159. var arrln = aggr.length, i, label, j, jv, mainval="",swapvals=[];
  160. if($.isArray(value)) {
  161. jv = value.length;
  162. swapvals = value;
  163. } else {
  164. jv = 1;
  165. swapvals[0]=value;
  166. }
  167. member = [];
  168. labels = [];
  169. member.root = 0;
  170. for(j=0;j<jv;j++) {
  171. var tmpmember = [], vl;
  172. for(i=0; i < arrln; i++) {
  173. if(value == null) {
  174. label = $.trim(aggr[i].member)+"_"+aggr[i].aggregator;
  175. vl = label;
  176. swapvals[0]= vl;
  177. } else {
  178. vl = value[j].replace(/\s+/g, '');
  179. try {
  180. label = (arrln === 1 ? mainval + vl : mainval + vl+"_"+aggr[i].aggregator+"_" + String(i));
  181. } catch(e) {}
  182. }
  183. label = !isNaN(parseInt(label,10)) ? label + " " : label;
  184. curr[label] = tmpmember[label] = calculation( aggr[i].aggregator, curr[label], aggr[i].member, row);
  185. if(j<=1 && vl !== '_r_Totals' && mainval === "") { // this does not fix full the problem
  186. mainval = vl;
  187. }
  188. }
  189. //vl = !isNaN(parseInt(vl,10)) ? vl + " " : vl;
  190. member[label] = tmpmember;
  191. labels[label] = swapvals[j];
  192. }
  193. return curr;
  194. }
  195. // Making the row totals without to add in yDimension
  196. if(o.rowTotals && o.yDimension.length > 0) {
  197. var dn = o.yDimension[0].dataName;
  198. o.yDimension.splice(0,0,{dataName:dn});
  199. o.yDimension[0].converter = function(){ return '_r_Totals'; };
  200. }
  201. // build initial columns (colModel) from xDimension
  202. xlen = $.isArray(o.xDimension) ? o.xDimension.length : 0;
  203. ylen = o.yDimension.length;
  204. aggrlen = $.isArray(o.aggregates) ? o.aggregates.length : 0;
  205. if(xlen === 0 || aggrlen === 0) {
  206. throw("xDimension or aggregates optiona are not set!");
  207. }
  208. var colc;
  209. for(i = 0; i< xlen; i++) {
  210. colc = {name:o.xDimension[i].dataName, frozen: o.frozenStaticCols};
  211. if(o.xDimension[i].isGroupField == null) {
  212. o.xDimension[i].isGroupField = true;
  213. }
  214. colc = $.extend(true, colc, o.xDimension[i]);
  215. columns.push( colc );
  216. }
  217. var groupfields = xlen - 1, tree={};
  218. //tree = { text: 'root', leaf: false, children: [] };
  219. //loop over alll the source data
  220. while( r < rowlen ) {
  221. row = data[r];
  222. var xValue = [];
  223. var yValue = [];
  224. tmp = {};
  225. i = 0;
  226. // build the data from xDimension
  227. do {
  228. xValue[i] = $.trim(row[o.xDimension[i].dataName]);
  229. tmp[o.xDimension[i].dataName] = xValue[i];
  230. i++;
  231. } while( i < xlen );
  232. var k = 0;
  233. rowindex = -1;
  234. // check to see if the row is in our new pivotrow set
  235. newObj = find(pivotrows, findGroup, xValue);
  236. if(!newObj) {
  237. // if the row is not in our set
  238. k = 0;
  239. // if yDimension is set
  240. if(ylen>=1) {
  241. // build the cols set in yDimension
  242. for(k=0;k<ylen;k++) {
  243. yValue[k] = $.trim(row[o.yDimension[k].dataName]);
  244. // Check to see if we have user defined conditions
  245. if(o.yDimension[k].converter && $.isFunction(o.yDimension[k].converter)) {
  246. yValue[k] = o.yDimension[k].converter.call(this, yValue[k], xValue, yValue);
  247. }
  248. }
  249. // make the colums based on aggregates definition
  250. // and return the members for late calculation
  251. tmp = agregateFunc( row, o.aggregates, yValue, tmp );
  252. } else if( ylen === 0 ) {
  253. // if not set use direct the aggregates
  254. tmp = agregateFunc( row, o.aggregates, null, tmp );
  255. }
  256. // add the result in pivot rows
  257. pivotrows.push( tmp );
  258. } else {
  259. // the pivot exists
  260. if( rowindex >= 0) {
  261. k = 0;
  262. // make the recalculations
  263. if(ylen>=1) {
  264. for(k=0;k<ylen;k++) {
  265. yValue[k] = $.trim(row[o.yDimension[k].dataName]);
  266. if(o.yDimension[k].converter && $.isFunction(o.yDimension[k].converter)) {
  267. yValue[k] = o.yDimension[k].converter.call(this, yValue[k], xValue, yValue);
  268. }
  269. }
  270. newObj = agregateFunc( row, o.aggregates, yValue, newObj );
  271. } else if( ylen === 0 ) {
  272. newObj = agregateFunc( row, o.aggregates, null, newObj );
  273. }
  274. // update the row
  275. pivotrows[rowindex] = newObj;
  276. }
  277. }
  278. var kj=0, current = null,existing = null, kk;
  279. // Build a JSON tree from the member (see aggregateFunc)
  280. // to make later the columns
  281. //
  282. for (kk in member) {
  283. if(member.hasOwnProperty( kk )) {
  284. if(kj === 0) {
  285. if (!tree.children||tree.children === undefined){
  286. tree = { text: kk, level : 0, children: [], label: kk };
  287. }
  288. current = tree.children;
  289. } else {
  290. existing = null;
  291. for (i=0; i < current.length; i++) {
  292. if (current[i].text === kk) {
  293. //current[i].fields=member[kk];
  294. existing = current[i];
  295. break;
  296. }
  297. }
  298. if (existing) {
  299. current = existing.children;
  300. } else {
  301. current.push({ children: [], text: kk, level: kj, fields: member[kk], label: labels[kk] });
  302. current = current[current.length - 1].children;
  303. }
  304. }
  305. kj++;
  306. }
  307. }
  308. r++;
  309. }
  310. var lastval=[], initColLen = columns.length, swaplen = initColLen;
  311. if(ylen>0) {
  312. headers[ylen-1] = { useColSpanStyle: false, groupHeaders: []};
  313. }
  314. /*
  315. * Recursive function which uses the tree to build the
  316. * columns from the pivot values and set the group Headers
  317. */
  318. function list(items) {
  319. var l, j, key, k, col;
  320. for (key in items) { // iterate
  321. if (items.hasOwnProperty(key)) {
  322. // write amount of spaces according to level
  323. // and write name and newline
  324. if(typeof items[key] !== "object") {
  325. // If not a object build the header of the appropriate level
  326. if( key === 'level') {
  327. if(lastval[items.level] === undefined) {
  328. lastval[items.level] ='';
  329. if(items.level>0 && items.text !== '_r_Totals') {
  330. headers[items.level-1] = {
  331. useColSpanStyle: false,
  332. groupHeaders: []
  333. };
  334. }
  335. }
  336. if(lastval[items.level] !== items.text && items.children.length && items.text !== '_r_Totals') {
  337. if(items.level>0) {
  338. headers[items.level-1].groupHeaders.push({
  339. titleText: items.label,
  340. numberOfColumns : 0
  341. });
  342. var collen = headers[items.level-1].groupHeaders.length-1,
  343. colpos = collen === 0 ? swaplen : initColLen+aggrlen;
  344. if(items.level-1=== (o.rowTotals ? 1 : 0)) {
  345. if(collen>0) {
  346. var l1 = headers[items.level-1].groupHeaders[collen-1].numberOfColumns;
  347. if(l1) {
  348. colpos = l1 + 1 + o.aggregates.length;
  349. }
  350. }
  351. }
  352. headers[items.level-1].groupHeaders[collen].startColumnName = columns[colpos].name;
  353. headers[items.level-1].groupHeaders[collen].numberOfColumns = columns.length - colpos;
  354. initColLen = columns.length;
  355. }
  356. }
  357. lastval[items.level] = items.text;
  358. }
  359. // This is in case when the member contain more than one summary item
  360. if(items.level === ylen && key==='level' && ylen >0) {
  361. if( aggrlen > 1){
  362. var ll=1;
  363. for( l in items.fields) {
  364. if(ll===1) {
  365. headers[ylen-1].groupHeaders.push({startColumnName: l, numberOfColumns: 1, titleText: items.text});
  366. }
  367. ll++;
  368. }
  369. headers[ylen-1].groupHeaders[headers[ylen-1].groupHeaders.length-1].numberOfColumns = ll-1;
  370. } else {
  371. headers.splice(ylen-1,1);
  372. }
  373. }
  374. }
  375. // if object, call recursively
  376. if (items[key] != null && typeof items[key] === "object") {
  377. list(items[key]);
  378. }
  379. // Finally build the coulumns
  380. if( key === 'level') {
  381. if(items.level >0){
  382. j=0;
  383. for(l in items.fields) {
  384. if(items.fields.hasOwnProperty( l )) {
  385. col = {};
  386. for(k in o.aggregates[j]) {
  387. if(o.aggregates[j].hasOwnProperty(k)) {
  388. switch( k ) {
  389. case 'member':
  390. case 'label':
  391. case 'aggregator':
  392. break;
  393. default:
  394. col[k] = o.aggregates[j][k];
  395. }
  396. }
  397. }
  398. if(aggrlen>1) {
  399. col.name = l;
  400. col.label = o.aggregates[j].label || items.label;
  401. } else {
  402. col.name = items.text;
  403. col.label = items.text==='_r_Totals' ? o.rowTotalsText : items.label;
  404. }
  405. columns.push (col);
  406. j++;
  407. }
  408. }
  409. }
  410. }
  411. }
  412. }
  413. }
  414. list( tree );
  415. var nm;
  416. // loop again trougth the pivot rows in order to build grand total
  417. if(o.colTotals) {
  418. var plen = pivotrows.length;
  419. while(plen--) {
  420. for(i=xlen;i<columns.length;i++) {
  421. nm = columns[i].name;
  422. if(!summaries[nm]) {
  423. summaries[nm] = parseFloat(pivotrows[plen][nm] || 0);
  424. } else {
  425. summaries[nm] += parseFloat(pivotrows[plen][nm] || 0);
  426. }
  427. }
  428. }
  429. }
  430. // based on xDimension levels build grouping
  431. if( groupfields > 0) {
  432. for(i=0;i<groupfields;i++) {
  433. if(columns[i].isGroupField) {
  434. groupOptions.groupingView.groupField.push(columns[i].name);
  435. groupOptions.groupingView.groupSummary.push(o.groupSummary);
  436. groupOptions.groupingView.groupSummaryPos.push(o.groupSummaryPos);
  437. }
  438. }
  439. } else {
  440. // no grouping is needed
  441. groupOptions.grouping = false;
  442. }
  443. groupOptions.sortname = columns[groupfields].name;
  444. groupOptions.groupingView.hideFirstGroupCol = true;
  445. });
  446. // return the final result.
  447. return { "colModel" : columns, "rows": pivotrows, "groupOptions" : groupOptions, "groupHeaders" : headers, summary : summaries };
  448. },
  449. jqPivot : function( data, pivotOpt, gridOpt, ajaxOpt) {
  450. return this.each(function(){
  451. var $t = this;
  452. function pivot( data) {
  453. var pivotGrid = jQuery($t).jqGrid('pivotSetup',data, pivotOpt),
  454. footerrow = $.assocArraySize(pivotGrid.summary) > 0 ? true : false,
  455. query= $.jgrid.from(pivotGrid.rows), i;
  456. for(i=0; i< pivotGrid.groupOptions.groupingView.groupField.length; i++) {
  457. query.orderBy(pivotGrid.groupOptions.groupingView.groupField[i], "a", 'text', '');
  458. }
  459. jQuery($t).jqGrid($.extend(true, {
  460. datastr: $.extend(query.select(),footerrow ? {userdata:pivotGrid.summary} : {}),
  461. datatype: "jsonstring",
  462. footerrow : footerrow,
  463. userDataOnFooter: footerrow,
  464. colModel: pivotGrid.colModel,
  465. viewrecords: true,
  466. sortname: pivotOpt.xDimension[0].dataName // ?????
  467. }, pivotGrid.groupOptions, gridOpt || {}));
  468. var gHead = pivotGrid.groupHeaders;
  469. if(gHead.length) {
  470. for( i = 0;i < gHead.length ; i++) {
  471. if(gHead[i] && gHead[i].groupHeaders.length) {
  472. jQuery($t).jqGrid('setGroupHeaders',gHead[i]);
  473. }
  474. }
  475. }
  476. if(pivotOpt.frozenStaticCols) {
  477. jQuery($t).jqGrid("setFrozenColumns");
  478. }
  479. }
  480. if(typeof data === "string") {
  481. $.ajax($.extend({
  482. url : data,
  483. dataType: 'json',
  484. success : function(response) {
  485. pivot($.jgrid.getAccessor(response, ajaxOpt && ajaxOpt.reader ? ajaxOpt.reader: 'rows') );
  486. }
  487. }, ajaxOpt || {}) );
  488. } else {
  489. pivot( data );
  490. }
  491. });
  492. }
  493. });
  494. })(jQuery);