var qb_checkCols=0;var qb_suspendGen=false;function qb_linkDblClick(){ // put some code here var x=this;}function qb_tableDblClick(){ // put some code here var x=this;}function qb_columnDblClick(){ // put some code here var x=this;}function qb_getAlias(obj) { var tname = obj.getAttribute("htmldb:name") var tcnt = obj.getAttribute("htmldb:cnt") var cId = ''; if (tcnt > 0) { cId = tname+'_'+tcnt; } else { cId = tname; } return cId;} function qb_rmTableConditions(id) {// var t = html_GetElement(id); var t = html_GetElement(id+"_thold");// var zx = id; if (t) {// var tname = t.innerHTML;// var tname = t.getAttribute("htmldb:name")// var tcnt = t.getAttribute("htmldb:cnt")// var cId = '';// if (tcnt > 0) {// cId = tname+'_'+tcnt;// } else {// cId = tname;// } var x=html_GetElement('conditions').getElementsByTagName('TR'); for(var i=0;i<x.length;i++){ // var foo = x[i].id; if(x[i].table && x[i].table == qb_getAlias(t)) { qb_checkCols--; var zz = x[i].table; x[i].parentNode.removeChild(x[i]); i--; } } }else{ return false; } qb_genSql();}function qb_rmCondition(x) { x = html_GetElement(x); x.parentNode.removeChild(x); qb_checkCols--; qb_genSql();}function qb_rmConditionImg(x) { var chkBox = html_GetElement('selectColumns_'+x); chkBox.checked = false; x = html_GetElement(x+'_condition'); x.parentNode.removeChild(x); qb_checkCols--; qb_genSql();}function qb_toggleOut(id){ var x = html_GetElement(id); var y = html_GetElement(x.col+"_grp"); if (x.checked) { x.checked=true; x.defaultChecked=true; } else { x.checked=false; y.checked=false; x.defaultChecked=false; y.defaultChecked=false; } qb_genSql();}function qb_toggleGrp(id){ var x = html_GetElement(id); cDebug(x) var y = html_GetElement(x.col+"_out"); cDebug(y +':'+x.col+"_out") if (x.checked) { x.checked=true; y.checked=true; x.defaultChecked=true; y.defaultChecked=true; } else { x.defaultChecked=false; x.checked=false; } qb_genSql();}function qb_setFun(obj) { var s = obj.value; var count = 0; if (s!="") { var matches = s.match(/\(/g); if (matches) count = matches.length; } obj.paren = count; qb_genSql();}function qb_rendCharSelect(obj){ obj.options.length = 0; obj.options[obj.options.length]=new Option('',''); obj.options[obj.options.length]=new Option('COUNT','count('); obj.options[obj.options.length]=new Option('COUNT DISTINCT ','count( distinct '); obj.options[obj.options.length]=new Option('INITCAP','initcap('); obj.options[obj.options.length]=new Option('LENGTH','length('); obj.options[obj.options.length]=new Option('LOWER','lower('); obj.options[obj.options.length]=new Option('LTRIM','ltrim('); obj.options[obj.options.length]=new Option('RTRIM','rtrim('); obj.options[obj.options.length]=new Option('TRIM','rtrim(ltrim('); obj.options[obj.options.length]=new Option('UPPER','upper('); obj = null; return;} function qb_rendNumberSelect(obj){ obj.options.length = 0; obj.options[obj.options.length]=new Option('',''); obj.options[obj.options.length]=new Option('COUNT','count('); obj.options[obj.options.length]=new Option('COUNT DISTINCT ','count( distinct '); obj.options[obj.options.length]=new Option('AVG','avg('); obj.options[obj.options.length]=new Option('MAX','max('); obj.options[obj.options.length]=new Option('MIN','min('); obj.options[obj.options.length]=new Option('SUM','sum('); obj = null; return;} function qb_rendDateSelect(obj){ obj.options.length = 0; obj.options[obj.options.length]=new Option('',''); obj.options[obj.options.length]=new Option('COUNT','count('); obj.options[obj.options.length]=new Option('COUNT DISTINCT ','count( distinct ');// obj.options[obj.options.length]=new Option('YEAR','year');// obj.options[obj.options.length]=new Option('QUARTER','quarter');// obj.options[obj.options.length]=new Option('MONTH','month');// obj.options[obj.options.length]=new Option('DAY','weekday');// obj.options[obj.options.length]=new Option('DAY OF YEAR','doy');// obj.options[obj.options.length]=new Option('WEEK','week'); obj = null; return;} // add a condition rowfunction qb_addCondition(box){ qb_checkCols++; // get table and create new row var x = box; var table = html_GetElement("conditions"); cTestClone(box.value.substring(0,box.value.indexOf('.')),box.value.substring(box.value.indexOf('.')+1),box) if ( ! qb_suspendGen ) qb_genSql();}function cTestClone(p_Obj,p_Col,p_Box){ var startTime = new Date(); var l_Obj_Col = p_Obj + '.' + p_Col; var table = html_GetElement("conditions"); var l_cRow = html_GetElement('clone'); var newTR = l_cRow.cloneNode(true); var x = p_Box.value.substring(0,p_Box.value.indexOf('.')); newTR.table = x;/* if(document.all){ table.firstChild.appendChild(newTR); }else{ table.appendChild(newTR); }*/ newTR.table = p_Obj; var l_Tds = newTR.getElementsByTagName('TD'); newTR.id = p_Box.value+"_condition"; //cDebug(newTR.id) l_Tds[8].getElementsByTagName('SELECT')[0].id = l_Obj_Col + '_fun'; // Function var l_ColIn = l_Tds[1]; l_ColIn.id = l_Obj_Col + '_Column'; // Column l_ColIn.innerHTML = p_Col; // Column var l_ObjIn = l_Tds[3]; l_Tds[3].id = l_Obj_Col + '_Object'; // Object l_ObjIn.innerHTML = p_Obj; // Object l_Tds[2].getElementsByTagName('INPUT')[0].id = l_Obj_Col + '_alias'; // Alias id var l_Inp = table.getElementsByTagName('INPUT'); var c = 0; var r = 0; for (j=0;j<l_Inp.length;j++) { var aid = l_Inp[j].id.substring(l_Inp[j].id.indexOf('.')+1); var aid2 = l_Inp[j].value.substring(l_Inp[j].value.indexOf('_')+1); if (aid == p_Col+'_alias') { c = c+1; if ( aid2 > r) r = aid2; } } var l_Alias = ''; if ( c == 1 ) l_Alias = ''; if ( c == 2 ) l_Alias = '_1'; r = parseInt(r)+1; if ( c > 2 ) l_Alias = '_'+r; l_Tds[2].getElementsByTagName('INPUT')[0].value = p_Col+l_Alias; // alias value var l_outIN = l_Tds[7].getElementsByTagName('INPUT')[0]; l_outIN.id = l_Obj_Col + '_out'; // Output? l_outIN.out = "1"; l_outIN.col = p_Box.value; l_Tds[5].getElementsByTagName('SELECT')[0].id = l_Obj_Col + '_st'; // Sort Type var l_soIN = l_Tds[6].getElementsByTagName('INPUT')[0]; l_soIN.id = l_Obj_Col + '_so'; // Sort Order l_soIN.sort = "1"; l_soIN.st = p_Box.value+"_st"; l_soIN.col = p_Box.value; var l_grpIN = l_Tds[9].getElementsByTagName('INPUT')[0] l_grpIN.id = l_Obj_Col + '_grp'; // Group By l_grpIN.grp = "1"; l_grpIN.col = p_Box.value; var l_Del = l_Tds[10].getElementsByTagName('IMG')[0]; l_Del.id = l_Obj_Col + '_del'; // Output? l_Del.value = l_Obj_Col; var l_conIN = l_Tds[4].getElementsByTagName('INPUT')[0]; l_conIN.id = l_Obj_Col + '_con'; // Condition l_conIN.where = "1"; l_conIN.col = p_Box.value; if(document.all){ table.firstChild.appendChild(newTR); }else{ table.appendChild(newTR); } if (p_Box.alt=='DATE')qb_rendDateSelect(html_GetElement(l_Obj_Col + '_fun')); if (p_Box.alt=='VARCHAR2')qb_rendCharSelect(html_GetElement(l_Obj_Col + '_fun')); if (p_Box.alt=='NUMBER')qb_rendNumberSelect(html_GetElement(l_Obj_Col + '_fun')); newTR = null; l_Tds = null; cDebug("Elap:"+(new Date() - startTime)); return;}function qb_RowUp(pThis){ oElement = html_RowUp(pThis); qb_genSql(); return oElement; }function qb_RowDown(pThis){ oElement = html_RowDown(pThis); qb_genSql(); return oElement;}function qb_checkColumn(box){ var x = html_GetElement(box.value+"_condition"); if (box.checked == true && x ) { return; } else if (box.checked == true && !x ) { if ( qb_checkCols + 1 > 60 ) { box.checked=false; alert(l_maxCols); return; } else { qb_addCondition(box); } } else if (box.checked == false && x ) { qb_rmCondition(x); } else if (box.checked == false && !x ) { return; }}function qb_genSql(){ //if(g_SkipWriteSQL){return true;} var cols = qb_getColumns(); var tabs = qb_getTables(); var l = qb_getLinks(); var w = qb_getWhere(); var g = qb_getGroup(); var s = qb_getSort(); var ret; if ( cols != "" && tabs != "" ) { ret = "select\t" + cols + " \n from\t" + tabs; if ( l!="" ) { if (w!="") { ret = ret + " \n where " + l +"\n and "+ w; } else { ret = ret + " \n where " + l; } } else { if (w!="") ret = ret + " \n where " + w; } ret = ret + g + s; } else { ret = ""; } //cDebug(ret) var d = html_GetElement("QUERYTEXT"); if (d){d.value = ret}; //set the value on the page var q = html_GetElement("P1002_QUERY"); if (q){q.value = ret}; // set the value in session //qb_postQUERY(ret); qb_resultsOnClick=true;}function qb_getLinks(){ var ret=""; var tab = ""; for(var i=0;i<qb_links.length;i++){ ret = ret == "" ? " ": ret + "\n and\t " tab = qb_links[i].field1.id.split('.'); ret = ret + '"'+tab[0]+'"."'+tab[1]+'"'; if ( qb_links[i].condition == 'L') ret = ret + '(+) ='; else ret = ret + '='; tab = qb_links[i].field2.id.split('.'); ret = ret + '"'+tab[0]+'"."'+tab[1]+'"'; if ( qb_links[i].condition == 'R') ret = ret + '(+)'; } return ret;}function qb_getTables(){ var d=html_GetElement('qbTableHolder').getElementsByTagName('DIV'); var ret=""; for(var i=0;i<d.length;i++){ if ( d[i].id && d[i].id.substring(d[i].id.length-5) == "thold" ) { var tname = d[i].getAttribute("htmldb:name"); ret = ret == "" ? " ": ret + ",\n\t "; ret = ret +'"'+ tname+'"' + ' "' + qb_getAlias(d[i])+'"'; } } return ret;}function qb_getColumns(){ var d=html_GetElement('conditions').getElementsByTagName('INPUT'); var ret=""; for(var i=0;i<d.length;i++){ //cDebug('qb_getColumns :' + d[i].checked +':'+d[i].out) if ( d[i].checked && d[i].out) { var a = html_GetElement(d[i].col+"_alias"); var f = html_GetElement(d[i].col+"_fun"); // is a function assigned if (f && f.value != "") { if (f.paren>0) { var par = ""; for (var zz=0;zz<f.paren;zz++) { par = par + ")"; } ret = ret == "" ? " ": ret + ",\n\t " ; var tab = d[i].col.split('.'); ret = ret + f.value + '"'+tab[0]+'"."'+tab[1] + '"' + par + ' as "' + a.value +'"'; } else { // do my to_char stuff here ret = ret == "" ? " ": ret + ",\n\t " ; var l_col = " "; switch (f.value) { case "year": l_col = "to_char("+d[i].col+",'YYYY')"; break; case "quarter": l_col = "to_char("+d[i].col+",'Q')"; break; case "month": l_col = "to_char("+d[i].col+",'MONTH')"; break; case "doy": l_col = "to_char("+d[i].col+",'DDD')"; break; case "week": l_col = "to_char("+d[i].col+",'WW')"; break; case "weekday": l_col = "to_char("+d[i].col+",'DAY')"; break; } ret = ret + l_col + ' as "' + a.value +'"'; } } else { ret = ret == "" ? " ": ret + ",\n\t " ; var tab = d[i].col.split('.'); ret = ret + '"'+tab[0]+'"."'+tab[1] + '" as "' + a.value +'"'; } } } return ret;}function qb_getWhere(){ var d=html_GetElement('conditions').getElementsByTagName('INPUT'); var ret=""; for(var i=0;i<d.length;i++){ var foo = d[i].value; if ( d[i].where && d[i].value != "" ) { ret = ret == "" ? "\t ": ret + " \n and\t " ; var tab = d[i].col.split('.'); ret = ret + '"'+tab[0]+'"."'+tab[1] + '" ' + d[i].value ; //ret = ret + d[i].col + ' ' + d[i].value ; } } return ret;}function qb_getSort(){ var d=html_GetElement('conditions').getElementsByTagName('INPUT'); var m = new Array; var zz = 0; var pos=1; var maxpos=1; var ret=""; // make a smaller array from the input arrays that are sort value // and get the maximum value for(var i=0;i<d.length;i++){ if (d[i].sort && d[i].value != "" ) { m[zz] = d[i]; if(m[zz].value > maxpos){maxpos = m[zz].value}; zz++ } } while (pos<=maxpos) { for(var i=0;i<m.length;i++){ if ( m[i].sort && m[i].value != "" ) { if (m[i].value == pos) { var x = html_GetElement(m[i].st); if (x.value == "None") { ret = ret == "" ? "\norder by ": ret + ", " ; var tab = m[i].col.split('.'); ret = ret + '"' + tab[0] + '"."' + tab[1] + '" Asc'; } else { ret = ret == "" ? "\norder by ": ret + ", " ; var tab = m[i].col.split('.'); ret = ret + '"' + tab[0] + '"."' + tab[1] + '" ' + x.value ; } } } } pos++; } return ret;}function qb_getGroup(){ var d=html_GetElement('conditions').getElementsByTagName('INPUT'); var ret=""; for(var i=0;i<d.length;i++){ if ( d[i].grp && d[i].checked ) { ret = ret == "" ? " \ngroup by ": ret + ", " ; var tab = d[i].col.split('.'); ret = ret + '"' + tab[0] + '"."' + tab[1] + '"'; } } return ret;}/* sets up and executes savetab*/function qb_SaveTab(pThis){ html_ShowElement(html_MakeParent('R7170925778234838','Save_table')); html_ShowElement(html_MakeParent('R7099926313598093','Save_table')); html_TabClick(pThis,'Save_table'); return;}function qb_newCondition(col,alias,func_value,func_paren,out,sort_type,sort_order,group_by,condition) { var c=html_GetElement("selectColumns_"+col); c.checked="true"; qb_checkColumn(c); var f = html_GetElement(col+"_fun"); f.value = func_value; f.paren = func_paren; var a = html_GetElement(col+"_alias"); a.value = alias; var o = html_GetElement(col+"_out"); o.checked = out == "false" ? "": "true"; var st = html_GetElement(col+"_st"); st.value = sort_type; var so = html_GetElement(col+"_so"); so.value = sort_order; var g = html_GetElement(col+"_grp"); g.checked = group_by == "false" ? "": "true"; var con = html_GetElement(col+"_con"); con.value = condition;}