1 /** initializes reports, some basid display settings,
2 * grabs and builds the IDL tree
4 function oilsInitReportBuilder() {
5 if(!oilsInitReports()) return false;
6 oilsReportBuilderReset();
7 DOM.oils_rpt_table.onclick =
8 function(){hideMe(DOM.oils_rpt_column_editor)};
11 hideMe(DOM.oils_rpt_tree_loading);
12 unHideMe(DOM.oils_rpt_table);
16 DOM.oils_rpt_builder_save_template.onclick = oilsReportBuilderSave;
19 function oilsReportBuilderReset() {
20 var n = (oilsRpt) ? oilsRpt.name : "";
21 oilsRpt = new oilsReport();
23 oilsRptDisplaySelector = DOM.oils_rpt_display_selector;
24 oilsRptFilterSelector = DOM.oils_rpt_filter_selector;
25 oilsRptAggFilterSelector= DOM.oils_rpt_agg_filter_selector;
26 removeChildren(oilsRptDisplaySelector);
27 removeChildren(oilsRptFilterSelector);
28 removeChildren(oilsRptAggFilterSelector);
33 function oilsReportBuilderSave() {
36 tmpl.name(DOM.oils_rpt_builder_new_name.value);
37 tmpl.description(DOM.oils_rpt_builder_new_desc.value);
38 tmpl.owner(USER.id());
39 tmpl.folder(new CGI().param('folder'));
40 tmpl.data(js2JSON(oilsRpt.def));
42 _debug('folder = ' + tmpl.folder());
44 if(!confirm('Name : '+tmpl.name() + '\nDescription: ' + tmpl.description()+'\nSave Template?'))
48 var req = new Request(OILS_RPT_CREATE_TEMPLATE, SESSION, tmpl);
51 var res = r.getResultObject();
53 oilsRptAlertSuccess();
64 /* adds an item to the display window */
65 function oilsAddRptDisplayItem(path, name, tform, params) {
66 if( ! oilsAddSelectorItem(oilsRptDisplaySelector, path, name) )
69 /* add the selected columns to the report output */
70 name = (name) ? name : oilsRptPathCol(path);
71 if( !tform ) tform = 'Bare';
73 /* add this item to the select blob */
75 relation: oilsRptPathRel(path),
77 column: { transform: tform, colname: oilsRptPathCol(path) }
80 if( params ) sel.column.params = params;
81 oilsRpt.def.select.push(sel);
83 mergeObjects( oilsRpt.def.from, oilsRptBuildFromClause(path));
87 /* takes a column path and builds a from-clause object for the path */
88 function oilsRptBuildFromClause(path) {
90 /* the path is the full path (relation) from the source
91 object to the column in question (e.g. au-home_ou-aou-name)*/
92 var parts = path.split(/-/);
94 /* the final from clause */
97 /* reference to the current position in the from clause */
102 /* walk the path, fleshing the from clause as we go */
103 for( var i = 0; i < parts.length; i += 2 ) {
105 var cls = parts[i]; /* class name (id) */
106 var col = parts[i+1]; /* column name */
108 /* a "node" is a class description from the IDL, it
109 contains relevant info, plus a list of "fields",
111 var node = oilsIDL[cls];
112 var pkey = oilsRptFindField(node, node.pkey);
114 /* a "field" is a parsed version of a column from the IDL,
115 contains datatype, column name, etc. */
116 var field = oilsRptFindField(node, col);
118 /* re-construct the path as we go so
119 we know what all we've seen thus far */
120 newpath = (newpath) ? newpath + '-'+ cls : cls;
122 /* extract relevant info */
123 tobj.table = node.table;
124 tobj.alias = newpath;
125 _debug('field type is ' + field.type);
126 if( i == (parts.length - 2) ) break;
128 /* we still have columns left in the path, keep adding join's */
130 if(field.reltype != 'has_a')
131 col = pkey.name + '-' + col;
138 if( field.type == 'link' )
139 tobj.key = field.key;
141 newpath = newpath + '-'+ path_col;
144 _debug("built 'from' clause: path="+path+"\n"+formatJSON(js2JSON(obj)));
149 /* removes a specific item from the display window */
150 function oilsDelDisplayItem(val) {
151 oilsDelSelectorItem(oilsRptDisplaySelector, val);
154 /* removes selected items from the display window */
155 function oilsDelSelectedDisplayItems() {
156 var list = oilsDelSelectedItems(oilsRptDisplaySelector);
158 /* remove the de-selected columns from the report output */
159 oilsRpt.def.select = grep( oilsRpt.def.select,
161 for( var j = 0; j < list.length; j++ ) {
165 if( oilsRptPathRel(d) == i.relation && oilsRptPathCol(d) == col.colname ) {
166 //var param = (i.alias) ? i.alias.match(/::PARAM\d*/) : null;
167 // if( param ) delete oilsRpt.params[param];
175 if(!oilsRpt.def.select) {
176 oilsRpt.def.select = [];
177 //oilsReportBuilderReset();
180 for( var j = 0; j < list.length; j++ )
181 /* if there are no items left in the "select", "where", or "having" clauses
182 for the given relation, trim this relation from the "from" clause */
183 if( !grep(oilsRpt.def.select,
184 function(i){ return (i.relation == oilsRptPathRel(list[j])); })
185 && !grep(oilsRpt.def.where,
186 function(i){ return (i.relation == oilsRptPathRel(list[j])); })
187 && !grep(oilsRpt.def.having,
188 function(i){ return (i.relation == oilsRptPathRel(list[j])); })
189 ) oilsRptPruneFromClause(oilsRptPathRel(list[j]));
196 /* for each item in the path list, remove the associated data
197 from the "from" clause */
199 function oilsRptPruneFromClause(relation, node) {
200 _debug("removing relation from 'from' clause " + relation);
201 if(!node) node = oilsRpt.def.from.join;
202 for( var i in node ) {
203 _debug("looking at node "+node[i].alias);
204 // first, descend into the tree, and prune leaves first
206 oilsRptPruneFromClause(relation, node[i].join);
207 if(oilsRptObjectKeys(node[i].join).length == 0) delete node[i].join;
211 // if we're at an unused empty leaf, remove it
212 if( !node[i].join ) {
213 if( !grep(oilsRpt.def.select,
214 function(i){ return (i.relation == node[i].alias)})
215 && !grep(oilsRpt.def.where,
216 function(i){ return (i.relation == node[i].alias)})
217 && !grep(oilsRpt.def.having,
218 function(i){ return (i.relation == node[i].alias)})
228 function oilsRptMkFilterTags(path, tform, filter) {
229 var name = oilsRptMakeLabel(path);
230 if(tform) name += ' ('+tform+')';
231 name += ' "' + filter + '"';
232 var epath = path + ':'+filter+':';
233 if(tform) epath += tform;
235 return [ name, epath ];
239 /* adds an item to the display window */
240 function oilsAddRptFilterItem(path, tform, filter) {
241 _debug("Adding filter item for "+path+" tform="+tform+" filter="+filter);
243 var name = oilsRptMkFilterTags(path, tform, filter);
247 if( ! oilsAddSelectorItem(oilsRptFilterSelector, epath, name) )
251 relation: oilsRptPathRel(path),
252 column: { transform: tform, colname: oilsRptPathCol(path) },
255 where.condition[filter] = oilsRptNextParam();
258 case 'substring' : where.column.params = oilsRptNextParam();
261 oilsRpt.def.where.push(where);
262 mergeObjects( oilsRpt.def.from, oilsRptBuildFromClause(path));
266 /* removes a specific item from the display window */
267 //function oilsDelFilterItem(path) {
268 //oilsDelSelectorItem(oilsRptFilterSelector, path);
271 /* removes selected items from the display window */
272 function oilsDelSelectedFilterItems() {
274 /* the values in this list are formed: <path>:<operation>:<transform> */
275 var list = oilsDelSelectedItems(oilsRptFilterSelector);
279 for( var i = 0; i < list.length; i++ ) {
282 path: item.replace(/:.*/,''),
283 operation: item.replace(/.*:(.*):.*/,'$1'),
284 tform: item.replace(/.*?:.*?:(.*)/,'$1')
289 /* XXX refactor the below to take operation and transform into account
290 since the same path can be used multiple times as long as a different
291 filter and/or transform is used */
293 /* remove the de-selected columns from the report output */
294 oilsRpt.def.where = grep( oilsRpt.def.where,
296 for( var j = 0; j < flist.length; j++ ) {
299 var frel = oilsRptPathRel(fil.path);
300 var fcol = oilsRptPathCol(fil.path);
302 var op = oilsRptObjectKeys(i.condition)[0];
304 if( frel == i.relation &&
305 fcol == col.colname &&
306 fil.operation == op &&
307 fil.tform == col.transform ) {
308 /* we have found a where clause with the same
309 relation, column, operation and transform */
311 /* we aren't setting params on template build.. */
312 //var param = (i.column.params) ? i.columns.params.match(/::P\d*/) : null;
313 //if( param ) delete oilsRpt.params[param];
314 //param = (i.condition[op]) ? i.condition[op].match(/::P\d*/) : null;
315 //if( param ) delete oilsRpt.params[param];
324 if(!oilsRpt.def.where)
325 oilsRpt.def.where = [];
328 for( var j = 0; j < flist.length; j++ ) {
329 var path = flist[j].path;
330 var rel = oilsRptPathRel(path);
331 /* if there are no items left in the "select", "where", or "having" clauses
332 for the given relation, trim this relation from the "from" clause */
334 var func = function(i){ return (i.relation == rel); };
336 if( !grep(oilsRpt.def.select, func) &&
337 !grep(oilsRpt.def.where, func) &&
338 !grep(oilsRpt.def.having, func) ) {
340 _debug("pruning item with path "+ path + ' and relation '+ rel);
342 oilsRptPruneFromClause(oilsRptPathRel(path));
349 /* adds an item to the display window */
350 function oilsAddRptAggFilterItem(val) {
351 oilsAddSelectorItem(oilsRptAggFilterSelector, val);
354 /* removes a specific item from the display window */
355 function oilsDelAggFilterItem(val) {
356 oilsDelSelectorItem(oilsRptAggFilterSelector, val);
359 /* removes selected items from the display window */
360 function oilsDelSelectedAggFilterItems() {
361 var list = oilsDelSelectedItems(oilsRptAggFilterSelector);
363 /* remove the de-selected columns from the report output */
364 oilsRpt.def.having = grep( oilsRpt.def.having,
366 for( var j = 0; j < list.length; j++ ) {
370 /* if this columsn has a transform,
371 it will be an object { tform => column } */
372 if( typeof col != 'string' )
373 for( var c in col ) col = col[c];
375 /* if this transform requires params, the column
376 will be the first item in the param set array */
377 if( typeof col != 'string' ) col = col[0];
379 if( oilsRptPathRel(d) == i.relation && oilsRptPathCol(d) == col ) {
380 var param = (i.alias) ? i.alias.match(/::P\d*/) : null;
381 if( param ) delete oilsRpt.params[param];
389 if(!oilsRpt.def.having) {
390 oilsRpt.def.having = [];
391 oilsReportBuilderReset();
394 for( var j = 0; j < list.length; j++ )
395 /* if there are no items left in the "select", "where", or "having" clauses
396 for the given relation, trim this relation from the "from" clause */
397 if( !grep(oilsRpt.def.select,
398 function(i){ return (i.relation == oilsRptPathRel(list[j])); })
399 && !grep(oilsRpt.def.where,
400 function(i){ return (i.relation == oilsRptPathRel(list[j])); })
401 && !grep(oilsRpt.def.having,
402 function(i){ return (i.relation == oilsRptPathRel(list[j])); })
403 ) oilsRptPruneFromClause(oilsRptPathRel(list[j]));
410 /* adds an item to the display window */
411 function oilsAddSelectorItem(sel, val, name) {
412 name = (name) ? name : oilsRptMakeLabel(val);
413 _debug("adding selector item "+name+' = ' +val);
414 for( var i = 0; i < sel.options.length; i++ ) {
415 var opt = sel.options[i];
416 if( opt.value == val ) return false;
418 insertSelectorVal( sel, -1, name, val );
423 /* removes a specific item from the display window */
424 function oilsDelSelectorItem(sel, val) {
425 _debug("deleting selector item "+val);
426 var opts = sel.options;
427 for( var i = 0; i < opts.length; i++ ) {
429 if( opt.value == val ) {
430 if( i == opts.length - 1 )
432 else opts[i] = opts[i+1];
438 /* removes selected items from the display window */
439 function oilsDelSelectedItems(sel) {
440 var list = getSelectedList(sel);
441 for( var i = 0; i < list.length; i++ )
442 oilsDelSelectorItem(sel, list[i]);
447 /* hides the different field editor tabs */
448 function oilsRptHideEditorDivs() {
449 hideMe(DOM.oils_rpt_tform_div);
450 hideMe(DOM.oils_rpt_filter_div);
451 hideMe(DOM.oils_rpt_agg_filter_div);
456 This draws the 3-tabbed window containing the transform,
457 filter, and aggregate filter picker window
459 function oilsRptDrawDataWindow(path) {
460 var col = oilsRptPathCol(path);
461 var cls = oilsRptPathClass(path);
462 var field = oilsRptFindField(oilsIDL[cls], col);
464 appendClear(DOM.oils_rpt_editor_window_label, text(oilsRptMakeLabel(path)));
465 appendClear(DOM.oils_rpt_editor_window_datatype, text(field.datatype));
467 _debug("setting update data window for column "+col+' on class '+cls);
469 var div = DOM.oils_rpt_column_editor;
470 /* set a preliminary top position so the page won't bounce around */
471 div.setAttribute('style','top:'+oilsMouseX+'px');
473 /* unhide the div so we can determine the dimensions */
476 /* don't let them see the floating div until the position is fully determined */
477 div.style.visibility='hidden';
479 oilsRptDrawTransformWindow(path, col, cls, field);
480 oilsRptDrawFilterWindow(path, col, cls, field);
482 //oilsRptSetFilters(field.datatype);
484 //oilsRptDoFilterWidgets();
486 //DOM.oils_rpt_filter_tform_selector.onchange = oilsRptDoFilterWidgets;
488 buildFloatingDiv(div, 600);
490 /* now let them see it */
491 div.style.visibility='visible';
493 oilsRptSetDataWindowActions(div);
497 function oilsRptSetDataWindowActions(div) {
498 /* give the tab links behavior */
499 DOM.oils_rpt_tform_tab.onclick =
500 function(){oilsRptHideEditorDivs();unHideMe(DOM.oils_rpt_tform_div)};
501 DOM.oils_rpt_filter_tab.onclick =
502 function(){oilsRptHideEditorDivs();unHideMe(DOM.oils_rpt_filter_div)};
503 DOM.oils_rpt_agg_filter_tab.onclick =
504 function(){oilsRptHideEditorDivs();unHideMe(DOM.oils_rpt_agg_filter_div)};
506 DOM.oils_rpt_tform_tab.onclick();
507 DOM.oils_rpt_column_editor_close_button.onclick = function(){hideMe(div);};
511 function oilsRptDrawFilterWindow(path, col, cls, field) {
513 var fsel = $n(DOM.oils_rpt_filter_op_table,'selector');
514 for( var i = 0; i < fsel.options.length; i++ ){
515 var opt = fsel.options[i];
516 var dt = opt.getAttribute('datatype');
519 /* add a special case for boolean objects, since the only
520 operation that makes sense is "=" */
521 if( field.datatype == 'bool' ) {
523 if( opt.getAttribute('value') == '=' )
527 if( dt && dt != field.datatype )
534 DOM.oils_rpt_filter_submit.onclick = function() {
535 var tsel = $n(DOM.oils_rpt_filter_tform_table,'selector');
536 var tform = getSelectorVal(tsel);
537 var filter = getSelectorVal(fsel);
538 oilsAddRptFilterItem(path, tform, filter);
540 oilsRptShowFilters($n(DOM.oils_rpt_filter_tform_table,'selector'), field.datatype, false, true);
543 oilsRptCurrentFilterTform = new oilsRptTFormManager(DOM.oils_rpt_filter_tform_table);
544 oilsRptCurrentFilterTform.build(field.datatype, false, true);
545 oilsRptCurrentFilterOpManager = new oilsRptOpManager(DOM.oils_rpt_filter_op_table);
549 function oilsRptShowFilters( selector, dtype, show_agg, show_noagg ) {
550 for( var i = 0; i < selector.options.length; i++ ) {
551 var opt = selector.options[i];
552 var t = opt.getAttribute('datatype');
553 if( t && t != dtype ){
556 var ag = opt.getAttribute('aggregate');
559 else if( ag && ! show_agg )
561 else if( !ag && show_noagg )
569 /* draws the transform window */
570 function oilsRptDrawTransformWindow(path, col, cls, field) {
571 DOM.oils_rpt_tform_label_input.value = oilsRptMakeLabel(path);
572 var dtype = field.datatype;
574 DOM.oils_rpt_tform_submit.onclick =
576 var sel = $n(DOM.oils_rpt_tform_table,'selector');
577 var tform = getSelectorVal(sel);
578 oilsAddRptDisplayItem(path, DOM.oils_rpt_tform_label_input.value, tform )
582 DOM.oils_rpt_tform_label_input.focus();
583 DOM.oils_rpt_tform_label_input.select();
586 oilsRptCurrentTform = new oilsRptTFormManager(DOM.oils_rpt_tform_table);
587 oilsRptCurrentTform.build(dtype, true, true);
590 _debug($n(DOM.oils_rpt_tform_table,'selector'));
592 oilsRptShowFilters($n(DOM.oils_rpt_tform_table,'selector'), dtype, true, true);
595 oilsRptHideTformFields();
596 oilsRptUnHideTformFields(dtype);
599 _debug("Building transform window for datatype "+dtype);
602 unHideMe($('oils_rpt_tform_'+dtype+'_div'));
603 $('oils_rpt_tform_all_raw').checked = true;
608 function oilsRptHideTformFields() {
609 var rows = DOM.oils_rpt_tform_tbody.childNodes;
610 for( var i = 0; i < rows.length; i++ )
611 if( rows[i] && rows[i].nodeType == 1 )
615 function oilsRptUnHideTformFields(dtype) {
616 var rows = DOM.oils_rpt_tform_tbody.childNodes;
617 for( var i = 0; i < rows.length; i++ ) {
619 if( row && row.nodeType == 1 &&
620 (row.getAttribute('datatype')=='all'
621 || row.getAttribute('datatype') == dtype)) {
628 function oilsRptGetTform(datatype) {
629 for( var i in oilsRptTransforms[datatype] )
630 if( $('oils_rpt_tform_'+datatype+'_'+oilsRptTransforms[datatype][i]).checked )
631 return oilsRptTransforms[datatype][i];
632 for( var i in oilsRptTransforms.all )
633 if( $('oils_rpt_tform_all_'+oilsRptTransforms.all[i]).checked )
634 return oilsRptTransforms.all[i];
641 function getRptTformParams(type, tform) {
647 DOM.oils_rpt_tform_string_substring_offset.value,
648 DOM.oils_rpt_tform_string_substring_length.value];
655 /* given a transform selector, this displays the appropriate
656 transforms for the given datatype.
657 if aggregate is true, is displays the aggregate transforms */
659 function oilsRptSetTransforms(sel, dtype, show_agg, show_noagg) {
660 for( var i = 0; i < sel.options.length; i++ ) {
661 var opt = sel.options[i];
662 var t = opt.getAttribute('datatype');
663 if( t && t != dtype ){
666 var ag = opt.getAttribute('aggregate');
669 else if( ag && ! show_agg )
671 else if( !ag && show_noagg )
681 /* displays the correct filter-transforms for the given datatype */
683 function oilsRptSetFilters(dtype) {
685 DOM.oils_rpt_filter_submit.onclick = function() {
686 var data = oilsRptDoFilterWidgets();
687 alert(js2JSON(data));
690 var sel = DOM.oils_rpt_filter_tform_selector;
691 for( var i = 0; i < sel.options.length; i++ ) {
692 var opt = sel.options[i];
693 _debug(opt.getAttribute('op'));
694 var t = opt.getAttribute('datatype');
695 if( t && t != dtype ) hideMe(opt);
701 /* hides all of the filter widgets */
702 function oilsRptHideFilterWidgets(node) {
704 node = DOM.oils_rpt_filter_tform_widget_td;
705 if( node.nodeType != 1 ) return;
706 if( node.getAttribute('widget') ) {
709 var cs = node.childNodes;
710 for( var i = 0; cs && i < cs.length; i++ )
711 oilsRptHideFilterWidgets(cs[i]);
715 /* what does this need to do? */
716 function oilsRptSetFilterOpActions() {
721 /* hides/unhides the appropriate widgets and returns the parameter
722 array appropriate for the selected widget */
723 function oilsRptDoFilterWidgets() {
724 filter = getSelectorVal(DOM.oils_rpt_filter_tform_selector);
725 oilsRptHideFilterWidgets();
732 /* generic transforms */
734 if(!op) op = 'equals';
738 if(!op) op = 'ilike';
750 if(!op) op = 'not in';
752 if(!op) op = 'between';
754 if(!op) op = 'not between';
755 unHideMe(DOM.oils_rpt_filter_tform_input);
756 params = [DOM.oils_rpt_filter_tform_input.value];
759 /* timestamp transforms */
761 if(!op) op = 'between';
762 case 'date_not_between':
763 if(!op) op = 'not between';
766 unHideMe(DOM.oils_rpt_filter_tform_date_1);
767 unHideMe(DOM.oils_rpt_filter_tform_date_2);
768 unHideMe(DOM.oils_rpt_filter_tform_date_hint);
769 DOM.oils_rpt_filter_tform_date_1.value = mkYearMonDay();
770 DOM.oils_rpt_filter_tform_date_2.value = mkYearMonDay();
772 DOM.oils_rpt_filter_tform_date_1.value,
773 DOM.oils_rpt_filter_tform_date_2.value
779 if(!tform) tform = 'dow';
780 case 'dow_not_between':
781 if(!op) op = 'not between';
782 if(!tform) tform = 'dow';
787 if(!tform) tform = 'dom';
788 case 'dom_not_between':
789 if(!op) op = 'not between';
790 if(!tform) tform = 'dom';
793 case 'month_between':
795 if(!tform) tform = 'moy';
796 case 'month_not_between':
797 if(!op) op = 'not between';
798 if(!tform) tform = 'moy';
801 case 'quarter_between':
803 if(!tform) tform = 'qoy';
804 case 'quarter_not_between':
805 if(!op) op = 'not between';
806 if(!tform) tform = 'qoy';
810 if(!op) op = 'between';
811 if(!tform) tform = 'year_trunc';
812 case 'year_not_between':
813 if(!op) op = 'not between';
814 if(!tform) tform = 'year_trunc';
818 if(!op) op = 'between';
819 if(!tform) tform = 'age';
820 case 'age_not_between':
821 if(!op) op = 'not between';
822 if(!tform) tform = 'age';
825 /* string transforms */
827 if(!tform) tform = 'substring';
832 if(!tform) tform = 'dow';
836 if(!tform) tform = 'dow';
838 /* numeric transforms */
841 if(!tform) tform = 'dow';
845 if(!tform) tform = 'dow';
848 return { op : op, params : params, tform : tform };