Open-ILS/web/reports/oils_rpt_builder.js

   1 /** initializes reports, some basid display settings,
   2   * grabs and builds the IDL tree
   3   */
   4 function oilsInitReportBuilder() {
   5         if(!oilsInitReports()) return false;
   6         oilsReportBuilderReset();
   7         DOM.oils_rpt_table.onclick =
   8                 function(){hideMe(DOM.oils_rpt_column_editor)};
   9         oilsDrawRptTree(
  10                 function() {
  11                         hideMe(DOM.oils_rpt_tree_loading);
  12                         unHideMe(DOM.oils_rpt_table);
  13                 }
  14         );
  15
  16         DOM.oils_rpt_builder_save_template.onclick = oilsReportBuilderSave;
  17 }
  18
  19 function oilsReportBuilderReset() {
  20         var n = (oilsRpt) ? oilsRpt.name : "";
  21         oilsRpt = new oilsReport();
  22         oilsRpt.name = n;
  23         oilsRptDisplaySelector  = DOM.oils_rpt_display_selector;
  24         oilsRptFilterSelector   = DOM.oils_rpt_filter_selector;
  25         oilsRptHavingSelector= DOM.oils_rpt_agg_filter_selector;
  26         removeChildren(oilsRptDisplaySelector);
  27         removeChildren(oilsRptFilterSelector);
  28         removeChildren(oilsRptHavingSelector);
  29         oilsRptResetParams();
  30 }
  31
  32 function oilsReportBuilderSave() {
  33
  34         var tmpl = new rt();
  35         tmpl.name(DOM.oils_rpt_builder_new_name.value);
  36         tmpl.description(DOM.oils_rpt_builder_new_desc.value);
  37         tmpl.owner(USER.id());
  38         tmpl.folder(new CGI().param('folder'));
  39         tmpl.data(js2JSON(oilsRpt.def));
  40
  41         if(!confirm('Name : '+tmpl.name() + '\nDescription: ' + tmpl.description()+'\nSave Template?'))
  42                 return;
  43
  44         debugFMObject(tmpl);
  45         //return; /* XXX */
  46
  47
  48         var req = new Request(OILS_RPT_CREATE_TEMPLATE, SESSION, tmpl);
  49         req.callback(
  50                 function(r) {
  51                         var res = r.getResultObject();
  52                         if( res != 0 ) {
  53                                 oilsRptAlertSuccess();
  54                                 _l('oils_rpt.xhtml');
  55                         }
  56                 }
  57         );
  58
  59         req.send();
  60 }
  61
  62
  63
  64 /* adds an item to the display window */
  65 function oilsAddRptDisplayItem(path, name, tform, params) {
  66         if( ! oilsAddSelectorItem(oilsRptDisplaySelector, path, name) )
  67                 return;
  68
  69         /* add the selected columns to the report output */
  70         name = (name) ? name : oilsRptPathCol(path);
  71         if( !tform ) tform = 'Bare';
  72
  73         var aggregate = oilsRptGetIsAgg(tform);
  74
  75         /* add this item to the select blob */
  76         var sel = {
  77                 relation: hex_md5(oilsRptPathRel(path)),
  78                 path : path,
  79                 alias:    name,
  80                 column:   { transform: tform, colname: oilsRptPathCol(path) }
  81         };
  82
  83         if( params ) sel.column.params = params;
  84
  85         if(!oilsRptGetIsAgg(tform)) {
  86                 var select = [];
  87                 var added = false;
  88                 for( var i = 0; i < oilsRpt.def.select.length; i++ ) {
  89                         var item = oilsRpt.def.select[i];
  90                         if( !added && oilsRptGetIsAgg( item.column.transform ) ) {
  91                                 select.push(sel);
  92                                 added = true;
  93                         }
  94                         select.push(item);
  95                 }
  96                 if(!added) select.push(sel);
  97                 oilsRpt.def.select = select;
  98         } else {
  99                 oilsRpt.def.select.push(sel);
 100         }
 101
 102
 103         mergeObjects( oilsRpt.def.from, oilsRptBuildFromClause(path));
 104         oilsRptDebug();
 105 }
 106
 107 function oilsRptGetIsAgg(tform) {
 108         return OILS_RPT_TRANSFORMS[tform].aggregate;
 109
 110         /* DEPRECATED */
 111         var sel = $n(DOM.oils_rpt_tform_table,'selector');
 112         for( var i = 0; i < sel.options.length; i++ ) {
 113                 var opt = sel.options[i];
 114                 if( opt.getAttribute('value') == tform )
 115                         return opt.getAttribute('aggregate');
 116         }
 117 }
 118
 119 /* takes a column path and builds a from-clause object for the path */
 120 function oilsRptBuildFromClause(path) {
 121
 122         /* the path is the full path (relation) from the source
 123                 object to the column in question (e.g. au-home_ou-aou-name)*/
 124         var parts = path.split(/-/);
 125
 126         /* the final from clause */
 127         var obj = {};
 128
 129         /* reference to the current position in the from clause */
 130         var tobj = obj;
 131
 132         var newpath = "";
 133
 134         /* walk the path, fleshing the from clause as we go */
 135         for( var i = 0; i < parts.length; i += 2 ) {
 136
 137                 var cls = parts[i]; /* class name (id) */
 138                 var col = parts[i+1]; /* column name */
 139
 140                 /* a "node" is a class description from the IDL, it
 141                         contains relevant info, plus a list of "fields",
 142                         or column objects */
 143                 var node = oilsIDL[cls];
 144                 var pkey = oilsRptFindField(node, node.pkey);
 145
 146                 /* a "field" is a parsed version of a column from the IDL,
 147                         contains datatype, column name, etc. */
 148                 var field = oilsRptFindField(node, col);
 149
 150                 /* re-construct the path as we go so
 151                         we know what all we've seen thus far */
 152                 newpath = (newpath) ? newpath + '-'+ cls : cls;
 153
 154                 /* extract relevant info */
 155                 tobj.table = node.table;
 156                 tobj.path = newpath;
 157                 tobj.alias = hex_md5(newpath);
 158
 159                 _debug('field type is ' + field.type);
 160                 if( i == (parts.length - 2) ) break;
 161
 162                 /* we still have columns left in the path, keep adding join's */
 163                 var path_col = col;
 164                 if(field.reltype != 'has_a')
 165                         col = pkey.name + '-' + col;
 166
 167                 tobj.join = {};
 168                 tobj = tobj.join;
 169
 170                 tobj[col] = {};
 171                 tobj = tobj[col];
 172                 if( field.type == 'link' )
 173                         tobj.key = field.key;
 174
 175                 newpath = newpath + '-'+ path_col;
 176         }
 177
 178         _debug("built 'from' clause: path="+path+"\n"+formatJSON(js2JSON(obj)));
 179         return obj;
 180 }
 181
 182
 183 /* removes a specific item from the display window */
 184 function oilsDelDisplayItem(val) {
 185         oilsDelSelectorItem(oilsRptDisplaySelector, val);
 186 }
 187
 188 /* removes selected items from the display window */
 189 function oilsDelSelectedDisplayItems() {
 190         var list = oilsDelSelectedItems(oilsRptDisplaySelector);
 191
 192         _debug('deleting list: ' + list);
 193
 194         /* remove the de-selected columns from the report output */
 195         oilsRpt.def.select = grep( oilsRpt.def.select,
 196                 function(i) {
 197                         for( var j = 0; j < list.length; j++ ) {
 198                                 var d = list[j]; /* path */
 199                                 var col = i.column;
 200
 201                                 _debug('in delete, looking at list = '+d+' : col = ' +
 202                                         col.colname + ' : relation = ' + i.relation + ' : encoded = ' + hex_md5(oilsRptPathRel(d)) );
 203
 204                                 if( hex_md5(oilsRptPathRel(d)) == i.relation && oilsRptPathCol(d) == col.colname ) {
 205                                         return false;
 206                                 }
 207                         }
 208                         return true;
 209                 }
 210         );
 211
 212         if(!oilsRpt.def.select) oilsRpt.def.select = [];
 213
 214         for( var j = 0; j < list.length; j++ ) {
 215                 /* if there are no items left in the "select", "where", or "having" clauses
 216                         for the given relation, trim this relation from the "from" clause */
 217                 debug('seeing if we can prune from clause with relation = ' + hex_md5(oilsRptPathRel(list[j])));
 218                 if(     !grep(oilsRpt.def.select,
 219                                 function(i){ return (i.relation == hex_md5(oilsRptPathRel(list[j]))); })
 220                         && !grep(oilsRpt.def.where,
 221                                 function(i){ return (i.relation == hex_md5(oilsRptPathRel(list[j]))); })
 222                         && !grep(oilsRpt.def.having,
 223                                 function(i){ return (i.relation == hex_md5(oilsRptPathRel(list[j]))); })
 224                 ) {
 225                         _debug('pruning from clause');
 226                         oilsRptPruneFromClause(oilsRptPathRel(list[j]));
 227                 }
 228         }
 229
 230         oilsRptDebug();
 231 }
 232
 233
 234 /* for each item in the path list, remove the associated data
 235         from the "from" clause */
 236
 237 function oilsRptPruneFromClause(relation, node) {
 238         _debug("removing relation from 'from' clause " + relation);
 239         if(!node) node = oilsRpt.def.from.join;
 240
 241         for( var i in node ) {
 242                 _debug("looking at node "+node[i].path);
 243                 // first, descend into the tree, and prune leaves first
 244                 if( node[i].join ) {
 245                         oilsRptPruneFromClause(relation, node[i].join);
 246                         if(oilsRptObjectKeys(node[i].join).length == 0) delete node[i].join;
 247                 }
 248         }
 249
 250         _debug(js2JSON(node));
 251
 252         // if we're at an unused empty leaf, remove it
 253         if(  !node.join ) {
 254
 255                 var key = oilsRptObjectKeys(node)[0];
 256                 _debug("pruning from clause with "+node[key].alias);
 257
 258                 if(     !grep(oilsRpt.def.select,
 259                                 function(n){ _debug(n.relation); return (n.relation == node[key].alias)})
 260                         && !grep(oilsRpt.def.where,
 261                                 function(n){ _debug(n.relation); return (n.relation == node[key].alias)})
 262                         && !grep(oilsRpt.def.having,
 263                                 function(n){ _debug(n.relation); return (n.relation == node[key].alias)})
 264                 ) {
 265                         delete node[i];
 266                         return true;
 267                 }
 268         }
 269
 270         return false;
 271 }
 272
 273 function oilsRptMkFilterTags(path, tform, filter) {
 274         var name = oilsRptMakeLabel(path);
 275         if(tform) name += ' ('+tform+')';
 276         name += ' "' + filter + '"';
 277         var epath = path + ':'+filter+':';
 278         if(tform) epath += tform;
 279
 280         return [ name, epath ];
 281 }
 282
 283
 284 function oilsAddRptFilterItem(path, tform, filter) {
 285         _debug("Adding filter item for "+path+" tform="+tform+" filter="+filter);
 286
 287         var name = oilsRptMkFilterTags(path, tform, filter);
 288         var epath = name[1];
 289         name = name[0];
 290
 291         if( ! oilsAddSelectorItem(oilsRptFilterSelector, epath, name) )
 292                 return;
 293
 294         var where = {
 295                 relation: hex_md5(oilsRptPathRel(path)),
 296                 path : path,
 297                 column:   { transform: tform, colname: oilsRptPathCol(path) },
 298                 condition : {}
 299         };
 300         if( filter == 'is' || filter == 'is not' )
 301                 where.condition[filter] = null;
 302         else where.condition[filter] = oilsRptNextParam();
 303
 304         switch(tform) {
 305                 case 'substring' : where.column.params = oilsRptNextParam();
 306         }
 307
 308         oilsRpt.def.where.push(where);
 309         mergeObjects( oilsRpt.def.from, oilsRptBuildFromClause(path));
 310         oilsRptDebug();
 311 }
 312
 313
 314 function oilsAddRptHavingItem(path, tform, filter) {
 315         _debug("Adding filter item for "+path+" tform="+tform+" filter="+filter);
 316
 317         var name = oilsRptMkFilterTags(path, tform, filter);
 318         var epath = name[1];
 319         name = name[0];
 320
 321         if( ! oilsAddSelectorItem(oilsRptHavingSelector, epath, name) )
 322                 return;
 323
 324         var having = {
 325                 relation: hex_md5(oilsRptPathRel(path)),
 326                 path : path,
 327                 column:   { transform: tform, colname: oilsRptPathCol(path) },
 328                 condition : {}
 329         };
 330         if( filter == 'is' || filter == 'is not' )
 331                 having.condition[filter] = null;
 332         else having.condition[filter] = oilsRptNextParam();
 333
 334         switch(tform) {
 335                 case 'substring' : having.column.params = oilsRptNextParam();
 336         }
 337
 338         oilsRpt.def.having.push(having);
 339         mergeObjects( oilsRpt.def.from, oilsRptBuildFromClause(path));
 340         oilsRptDebug();
 341 }
 342
 343
 344
 345 /* removes selected items from the display window */
 346 function oilsDelSelectedFilterItems() {
 347
 348         /* the values in this list are formed:  <path>:<operation>:<transform> */
 349         var list = oilsDelSelectedItems(oilsRptFilterSelector);
 350
 351         var flist = [];
 352
 353         for( var i = 0; i < list.length; i++ ) {
 354                 var item = list[i];
 355                 flist.push( {
 356                         path:           item.replace(/:.*/,''),
 357                         operation:      item.replace(/.*:(.*):.*/,'$1'),
 358                         tform:  item.replace(/.*?:.*?:(.*)/,'$1')
 359                 });
 360         }
 361
 362
 363         /* XXX refactor the below to take operation and transform into account
 364                 since the same path can be used multiple times as long as a different
 365                 filter and/or transform is used */
 366
 367         /* remove the de-selected columns from the report output */
 368         oilsRpt.def.where = grep( oilsRpt.def.where,
 369                 function(i) {
 370                         for( var j = 0; j < flist.length; j++ ) {
 371                                 var fil = flist[j];
 372                                 var col = i.column;
 373                                 var frel = oilsRptPathRel(fil.path);
 374                                 var fcol = oilsRptPathCol(fil.path);
 375
 376                                 var op = oilsRptObjectKeys(i.condition)[0];
 377
 378                                 if(     frel == i.relation &&
 379                                                 fcol == col.colname &&
 380                                                 fil.operation == op &&
 381                                                 fil.tform == col.transform ) {
 382                                                 /* we have found a where clause with the same
 383                                                         relation, column,  operation and transform */
 384
 385                                                 /* we aren't setting params on template build.. */
 386                                                 //var param = (i.column.params) ? i.columns.params.match(/::P\d*/) : null;
 387                                                 //if( param ) delete oilsRpt.params[param];
 388                                                 //param = (i.condition[op]) ? i.condition[op].match(/::P\d*/) : null;
 389                                                 //if( param ) delete oilsRpt.params[param];
 390
 391                                                 return false;
 392                                 }
 393                         }
 394                         return true;
 395                 }
 396         );
 397
 398         if(!oilsRpt.def.where)
 399                 oilsRpt.def.where = [];
 400
 401
 402         for( var j = 0; j < flist.length; j++ ) {
 403                 var path = flist[j].path;
 404                 var rel = oilsRptPathRel(path);
 405                 /* if there are no items left in the "select", "where", or "having" clauses
 406                         for the given relation, trim this relation from the "from" clause */
 407
 408                 var func = function(i){ return (i.relation == rel); };
 409
 410                 if(     !grep(oilsRpt.def.select, func) &&
 411                                 !grep(oilsRpt.def.where, func) &&
 412                                 !grep(oilsRpt.def.having, func) ) {
 413
 414                         _debug("pruning item with path "+ path + ' and relation '+ rel);
 415
 416                         oilsRptPruneFromClause(oilsRptPathRel(path));
 417                 }
 418         }
 419
 420         oilsRptDebug();
 421 }
 422
 423 /* adds an item to the display window */
 424 function oilsAddRptAggFilterItem(val) {
 425         oilsAddSelectorItem(oilsRptAggFilterSelector, val);
 426 }
 427
 428 /* removes a specific item from the display window */
 429 function oilsDelAggFilterItem(val) {
 430         oilsDelSelectorItem(oilsRptAggFilterSelector, val);
 431 }
 432
 433 /* removes selected items from the display window */
 434 function oilsDelSelectedAggFilterItems() {
 435         var list = oilsDelSelectedItems(oilsRptAggFilterSelector);
 436
 437         /* remove the de-selected columns from the report output */
 438         oilsRpt.def.having = grep( oilsRpt.def.having,
 439                 function(i) {
 440                         for( var j = 0; j < list.length; j++ ) {
 441                                 var d = list[j];
 442                                 var col = i.column;
 443
 444                                 /* if this columsn has a transform,
 445                                         it will be an object { tform => column } */
 446                                 if( typeof col != 'string' )
 447                                         for( var c in col ) col = col[c];
 448
 449                                 /* if this transform requires params, the column
 450                                         will be the first item in the param set array */
 451                                 if( typeof col != 'string' ) col = col[0];
 452
 453                                 if( oilsRptPathRel(d) == i.relation && oilsRptPathCol(d) == col ) {
 454                                         var param = (i.alias) ? i.alias.match(/::P\d*/) : null;
 455                                         if( param ) delete oilsRpt.params[param];
 456                                         return false;
 457                                 }
 458                         }
 459                         return true;
 460                 }
 461         );
 462
 463         if(!oilsRpt.def.having) {
 464                 oilsRpt.def.having = [];
 465                 oilsReportBuilderReset();
 466
 467         } else {
 468                 for( var j = 0; j < list.length; j++ )
 469                         /* if there are no items left in the "select", "where", or "having" clauses
 470                                 for the given relation, trim this relation from the "from" clause */
 471                         if(     !grep(oilsRpt.def.select,
 472                                         function(i){ return (i.relation == oilsRptPathRel(list[j])); })
 473                                 && !grep(oilsRpt.def.where,
 474                                         function(i){ return (i.relation == oilsRptPathRel(list[j])); })
 475                                 && !grep(oilsRpt.def.having,
 476                                         function(i){ return (i.relation == oilsRptPathRel(list[j])); })
 477                         ) oilsRptPruneFromClause(oilsRptPathRel(list[j]));
 478         }
 479
 480         oilsRptDebug();
 481 }
 482
 483
 484 /* adds an item to the display window */
 485 function oilsAddSelectorItem(sel, val, name) {
 486         name = (name) ? name : oilsRptMakeLabel(val);
 487         _debug("adding selector item "+name+' = ' +val);
 488         for( var i = 0; i < sel.options.length; i++ ) {
 489                 var opt = sel.options[i];
 490                 if( opt.value == val ) return false;
 491         }
 492         insertSelectorVal( sel, -1, name, val );
 493         return true;
 494 }
 495
 496
 497 /* removes a specific item from the display window */
 498 function oilsDelSelectorItem(sel, val) {
 499         _debug("deleting selector item "+val);
 500         var opts = sel.options;
 501         for( var i = 0; i < opts.length; i++ ) {
 502                 var opt = opts[i];
 503                 if( opt.value == val )  {
 504                         if( i == opts.length - 1 )
 505                                 opts[i] = null;
 506                         else opts[i] = opts[i+1];
 507                         return;
 508                 }
 509         }
 510 }
 511
 512 /* removes selected items from the display window */
 513 function oilsDelSelectedItems(sel) {
 514         var list = getSelectedList(sel);
 515         for( var i = 0; i < list.length; i++ )
 516                 oilsDelSelectorItem(sel, list[i]);
 517         return list;
 518 }
 519
 520
 521 /* hides the different field editor tabs */
 522 function oilsRptHideEditorDivs() {
 523         hideMe(DOM.oils_rpt_tform_div);
 524         hideMe(DOM.oils_rpt_filter_div);
 525         hideMe(DOM.oils_rpt_agg_filter_div);
 526 }
 527
 528
 529 /**
 530   This draws the 3-tabbed window containing the transform,
 531   filter, and aggregate filter picker window
 532   */
 533 function oilsRptDrawDataWindow(path) {
 534         var col = oilsRptPathCol(path);
 535         var cls = oilsRptPathClass(path);
 536         var field = oilsRptFindField(oilsIDL[cls], col);
 537
 538         appendClear(DOM.oils_rpt_editor_window_label, text(oilsRptMakeLabel(path)));
 539         appendClear(DOM.oils_rpt_editor_window_datatype, text(field.datatype));
 540
 541         _debug("setting update data window for column "+col+' on class '+cls);
 542
 543         var div = DOM.oils_rpt_column_editor;
 544         /* set a preliminary top position so the page won't bounce around */
 545         div.setAttribute('style','top:'+oilsMouseX+'px');
 546
 547         /* unhide the div so we can determine the dimensions */
 548         unHideMe(div);
 549
 550         /* don't let them see the floating div until the position is fully determined */
 551         div.style.visibility='hidden';
 552
 553         oilsRptDrawTransformWindow(path, col, cls, field);
 554         oilsRptDrawFilterWindow(path, col, cls, field);
 555         oilsRptDrawHavingWindow(path, col, cls, field);
 556
 557         //oilsRptSetFilters(field.datatype);
 558
 559         //oilsRptDoFilterWidgets();
 560
 561         //DOM.oils_rpt_filter_tform_selector.onchange = oilsRptDoFilterWidgets;
 562
 563         buildFloatingDiv(div, 600);
 564
 565         /* now let them see it */
 566         div.style.visibility='visible';
 567
 568         oilsRptSetDataWindowActions(div);
 569 }
 570
 571
 572 function oilsRptSetDataWindowActions(div) {
 573         /* give the tab links behavior */
 574         DOM.oils_rpt_tform_tab.onclick =
 575                 function(){oilsRptHideEditorDivs();unHideMe(DOM.oils_rpt_tform_div)};
 576         DOM.oils_rpt_filter_tab.onclick =
 577                 function(){oilsRptHideEditorDivs();unHideMe(DOM.oils_rpt_filter_div)};
 578         DOM.oils_rpt_agg_filter_tab.onclick =
 579                 function(){oilsRptHideEditorDivs();unHideMe(DOM.oils_rpt_agg_filter_div)};
 580
 581         DOM.oils_rpt_tform_tab.onclick();
 582         DOM.oils_rpt_column_editor_close_button.onclick = function(){hideMe(div);};
 583 }
 584
 585
 586 function oilsRptDrawFilterWindow(path, col, cls, field) {
 587
 588         var tformPicker = new oilsRptTformPicker( {
 589                         node : DOM.oils_rpt_filter_tform_table,
 590                         datatype : field.datatype,
 591                         non_aggregate : true
 592                 }
 593         );
 594
 595         var filterPicker = new oilsRptFilterPicker({
 596                         node : DOM.oils_rpt_filter_op_table,
 597                         datatype : field.datatype
 598                 }
 599         );
 600
 601         DOM.oils_rpt_filter_submit.onclick = function() {
 602                 oilsAddRptFilterItem(
 603                         path, tformPicker.getSelected(), filterPicker.getSelected());
 604         }
 605 }
 606
 607
 608 function oilsRptDrawHavingWindow(path, col, cls, field) {
 609         var tformPicker = new oilsRptTformPicker( {
 610                         node : DOM.oils_rpt_agg_filter_tform_table,
 611                         datatype : field.datatype,
 612                         aggregate : true
 613                 }
 614         );
 615
 616         var filterPicker = new oilsRptFilterPicker({
 617                         node : DOM.oils_rpt_agg_filter_op_table,
 618                         datatype : field.datatype
 619                 }
 620         );
 621
 622         DOM.oils_rpt_agg_filter_submit.onclick = function() {
 623                 oilsAddRptHavingItem(
 624                         path, tformPicker.getSelected(), filterPicker.getSelected());
 625         }
 626 }
 627
 628 /* draws the transform window */
 629 function oilsRptDrawTransformWindow(path, col, cls, field) {
 630         DOM.oils_rpt_tform_label_input.value = oilsRptMakeLabel(path);
 631         var dtype = field.datatype;
 632
 633         var tformPicker = new oilsRptTformPicker( {
 634                         node : DOM.oils_rpt_tform_table,
 635                         datatype : field.datatype,
 636                         non_aggregate : true,
 637                         aggregate : true
 638                 }
 639         );
 640
 641         DOM.oils_rpt_tform_submit.onclick =
 642                 function(){
 643                         oilsAddRptDisplayItem(path,
 644                                 DOM.oils_rpt_tform_label_input.value, tformPicker.getSelected() );
 645                 };
 646
 647
 648         DOM.oils_rpt_tform_label_input.focus();
 649         DOM.oils_rpt_tform_label_input.select();
 650
 651         _debug("Building transform window for datatype "+dtype);
 652 }