/*
*
*  UTF-8 data encode / decode
*  http://www.webtoolkit.info/
*
*/
 
var SPSql = {

	parseSQL : function(sqlQueryIn) {
		var sqlQuery = sqlQueryIn.replace(/\n/g, ' ').replace(/\r/g, '');
        
        sqlQuery = sqlQuery.replace(/ AS ([_a-zA-z0-9]+)/g, ".AS('$1')");

        var err = function(errMsg) { 
            throw ("[ERROR: " + errMsg + " in query: " + sqlQueryIn + "]"); 
        };
        
        var query_type = sqlQuery.split(/\s+/)[0];

        if (SPSql.strip_whitespace(query_type) != 'SELECT')
            err("not a valid query type");     

        var fromSplit = sqlQuery.substring(7).split(" FROM ");
        
		var findClause = function(str, regexp) {
	        var clauseEnd = str.search(regexp);
	        if (clauseEnd < 0)
	            clauseEnd = str.length;
	        return str.substring(0, clauseEnd);
	    }
        
        if (fromSplit.length != 2)
        	err("missing a FROM clause");
         
        //SELECT Invoice.*, Customer.* FROM Invoice, Customer
        //SELECT * FROM Invoice, Customer
        //DELETE things, relationships FROM relationships LEFT OUTER JOIN things ON things.relationship_id = relationships.id WHERE relationships.id = 2
        //SELECT * FROM relationships LEFT OUTER JOIN users ON relationships.created_by = users.id AND relationships.updated_by = users.id LEFT OUTER JOIN things ON things.relatedrelationship_id = relationships.id  ORDER BY relationships.updated_at DESC LIMIT 0, 20
        var columnsClause = SPSql.strip_whitespace(fromSplit[0].replace(/\.\*/g, ".ALL"));
        var remaining     = fromSplit[1];
        var fromClause    = findClause(remaining, /\sWHERE\s|\sGROUP BY\s|\sHAVING\s|\sORDER BY\s|\sLIMIT/);
        var fromTableClause = findClause(fromClause, /\sLEFT OUTER JOIN\s/);
        var fromTables = SPSql.strip_whitespace(fromTableClause);
                             
		var lists = SPSql.getLists(columnsClause.split(","), columnsClause.split(","));
		
		return TrimPath.makeQueryLang(lists['schema']).parseSQL(sqlQueryIn).filter(lists['data']);
	},
	
	strip_whitespace : function(str) {
		return str.replace(/\s+/g, '');
    },
    
    err : function(errMsg) { 
    	if (typeof(console) != "undefined")    		
    		console.warn(errMsg);
    },

	getLists : function(fromTables, fromFields) {
		var schema = new Array();
		var data = new Array();
		var fields = new Array();
	
		for (var i = 0; i < fromTables.length; i++) {
		
			schema[i] 	= fromTables[i] + ' : { ';
			data[i] 	= fromTables[i] + ' : [ ';
			
			$().SPServices({
				operation:'GetList',
				async: false,
				listName: fromTables[i],
				completefunc: function (xData, Status) {
					
					$(xData.responseXML).find("Field").each(function() {
					//	if ($(this).attr('FromBaseType') == null){
							
							var type = 'String';
							
							switch($(this).attr('Type')){
								case 'Note':
								case 'Computed':
								case 'Text':
								  type = 'String';
								  break;
								case 'Number':
								case 'Integer':
								case 'Float':
								case 'Lookup':
								case 'Int':
								  type = 'Number';
								  break;
								default:
								  type = 'String';
								  break;
							}
							// validate fromFields
							if (in_array(fromTables[i]+'.ALL',fromFields) || in_array(fromTables[i]+'.'+$(this).attr('Name'),fromFields)){
								fields.push( new Array( $(this).attr('Name') , $(this).attr('DisplayName') , type ) );
							} else {
								SPSql.err('Skipping '+$(this).attr('Name')+' field in '+fromTables[i]+' list.');
							}
					//	}
					});
				}
			});
			
			var caml_fields = '';
			
			for (var j = 0; j < fields.length; j++) {
				var temp_field = fields[j];
				caml_fields += '<FieldRef Name="'+temp_field[0]+'"/>';
				
				if (j != fields.length - 1){
					schema[i] 	+= temp_field[0] + ' : { type : "'+temp_field[2]+'" }, \n';
				} else {
					schema[i] 	+= temp_field[0] + ' : { type : "'+temp_field[2]+'" } \n';
				}
				temp_field = null;
			}
			
			$().SPServices({
				operation:'GetListItems',
				async: false,
				listName: fromTables[i],
				CAMLQuery:'',
				CAMLViewFields:'<ViewFields>'+caml_fields+'</ViewFields>',
				//CAMLRowLimit:9999,
				completefunc: function (xData, Status) {
		
					var rows = new Array();
				
					$(xData.responseXML).find("z\\:row").each(function() {
						
						var row = '{ ';
				
						for (var j = 0; j < fields.length; j++) {
							var temp_field = fields[j];
							
							if ($(this).attr('ows_'+ temp_field[0]) == null)
								var value = '';
							else
								var value = $(this).attr('ows_'+ temp_field[0]); 
							
							if (temp_field[2] == "String"){
								row 	+= temp_field[0] +' : \''+ (value) + '\'';
							} else {
								row 	+= temp_field[0] +' : '+ (value) + ', ';
							}

							if (j != fields.length - 1){ //{ id: 1, total: 100, custId: 10 }
								row 	+= ', ';
							} 
							
							temp_field = null;
						}
						
						row += ' }';
						
						rows.push(row);
					});
					
					data[i]  += rows.join(', \n');
				}
			});
			
			schema[i] 	+= ' } ';
			data[i] 	+= ' ] ';
		}
		var ret = new Array();
		ret['schema'] = eval("({" + schema.join(',') + "})");
		ret['data'] = eval("({" + data.join(',') + "})");
		//ret['data'] = data.join(',');
	
		return ret;
	}
	
}

function nl2br(myString) {
	var regX = /\n/gi ;

	s = new String(myString);
	s = s.replace(regX, "<br /> \n");
	return s;
}

function clean(myString) {
	var regX = /\n/gi ;

	s = new String(myString);
	s = s.replace(regX, "");
	return s;
}

/**
*
*  UTF-8 data encode / decode
*  http://www.webtoolkit.info/
*
**/
 
var Utf8 = {
 
	// public method for url encoding
	encode : function (string) {
		string = string.replace(/\r\n/g,"\n");
		var utftext = "";
 
		for (var n = 0; n < string.length; n++) {
 
			var c = string.charCodeAt(n);
 
			if (c < 128) {
				utftext += String.fromCharCode(c);
			}
			else if((c > 127) && (c < 2048)) {
				utftext += String.fromCharCode((c >> 6) | 192);
				utftext += String.fromCharCode((c & 63) | 128);
			}
			else {
				utftext += String.fromCharCode((c >> 12) | 224);
				utftext += String.fromCharCode(((c >> 6) & 63) | 128);
				utftext += String.fromCharCode((c & 63) | 128);
			}
 
		}
 
		return utftext;
	},
 
	// public method for url decoding
	decode : function (utftext) {
		var string = "";
		var i = 0;
		var c = c1 = c2 = 0;
 
		while ( i < utftext.length ) {
 
			c = utftext.charCodeAt(i);
 
			if (c < 128) {
				string += String.fromCharCode(c);
				i++;
			}
			else if((c > 191) && (c < 224)) {
				c2 = utftext.charCodeAt(i+1);
				string += String.fromCharCode(((c & 31) << 6) | (c2 & 63));
				i += 2;
			}
			else {
				c2 = utftext.charCodeAt(i+1);
				c3 = utftext.charCodeAt(i+2);
				string += String.fromCharCode(((c & 15) << 12) | ((c2 & 63) << 6) | (c3 & 63));
				i += 3;
			}
 
		}
 
		return string;
	}
 
}

function in_array( what, where ){
	var a=false;
	for(var i=0;i<where.length;i++){
	//alert(where);
	  if(what == where[i]){
	    a=true;
        break;
	  }
	}
	return a;
}