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 row

function 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 " ;
         	  ret = ret + f.value + d[i].col + 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 + ", " ;
	        ret = ret + m[i].col + ' Asc';
	      } else {
	        ret = ret == "" ? "\norder by ": ret + ", " ;
	        ret = ret + m[i].col + ' ' + 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 + ", " ;
          ret = ret + d[i].col;
        }
   }
   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;
}