/* global variables declared here */

// create an array of table names available on the MySQL server
var aDBs = new Array('mcgooz5_db', 'mcgooz4_db', 'mcgooz2_db', 'mcgooz3_db', 'mcgooz6_db','mcgooz1_db');
// create an array on tablenames to display. they do not have to be the same as above
var aDBNames = new Array('movies', 'school', 'repairs', 'shares', 'pms','egypt' );
// a string variable to store the currently selected db actual name
var sDB = '';
// a string variable to store the currently selected db display name
var sDBName = '';
// a string variable to store the currently selected table name
var sTable = '';
// a string variable to store the currently selected field name
var sField = '';
// a boolean variable to store whether HTML 5  audio tags are available to play sounds.
var canPlay = false;
// a boolean variable to store whether CopytoClipboard(MSIE only) is available.
var canCopy = true;
// create an array for AJAX requests
var getData = new Array();



// useful functions
// strips whitespace from a string like a trim() that JS does not have
function no_whitespace(str)
{
	str=str.replace(/\s/g,'\ ');
	return str;
}

// decodes strings encoded by php HTMLEntities type functions
function unHTMLEntities(strSent)
{
     var strChanged = strSent.replace(/'/g,"'");
     var strChanged2 = strChanged.replace(/>/g,">");
     strChanged = strChanged2.replace(/&lt;/g,"<");
     strChanged2 = strChanged.replace(/&amp;/g,"&");
     strChanged = strChanged2.replace(/&quot;/g, '"');
	return strChanged;
}

// tries to get an AJAX requestS object
function GetXmlHttpObject()
{
	if (window.XMLHttpRequest)
  	{
  		// code for IE7+, Firefox, Chrome, Opera, Safari
  		return new XMLHttpRequest();
  	}
	if (window.ActiveXObject)
  	{
  		// code for IE6, IE5
  		return new ActiveXObject("Microsoft.XMLHTTP");
  	}
	alert ("Browser does not support HTTP Request");
	return null;
}


// run on page load to set up available databases
function Load_Databases()
{
	// if cannot access this property then not IE so
	if(!document.selection)
   	{
     	// hide copy buttons
		document.getElementById('btnCopy').style.display= 'none';
     	document.getElementById('btnCopy2').style.display= 'none';
     	// set canCopy to false
		canCopy = false;
	}
	// if cannot access this property then not HTML 5 compliant eg FireFox >3.5 so
	if(document.getElementById('audTest').play != undefined)
	{
     	// set canPlay to true
		canPlay=true;
	}
	// clear the query box
	Clear_Query();
	// init table select line as a table
	document.getElementById("table_select").innerHTML = "<table class='strip'><tr><th class='section'>Table: </th><th></th></tr></table>";
	// init field select line as a table
	document.getElementById("field_list").innerHTML = "<table class='strip'><tr><th class='section'>Columns: </th><th><span class='tip'>...</span></th></tr></table>";
	// set up db select row as table
	var textOut = "<table><tr><th class='section' title='Click one to select database'>Database: </th><th><span class='tip'>Please Select a Database --> </span></th>";
	// read db actual names
	var db_list  = aDBs;
	// read db display names
	var db_Names = aDBNames;
	// iterate thru arrays
	var i=0;
	for(i=0;i<db_list.length;i++)
	{
		// set actual name
		var dbCode = db_list[i];
		// set display name
		var dbName = db_Names[i];
		// set title
		var sTitle = "Click to show tables in database: "+dbName+".";
		// output db item
		textOut += "<th class='field' id='"+dbCode+"' title='"+sTitle+"' onclick='Set_DB(this);'>"+dbName+"</th>";
     }
	// finish table tag
	textOut += "</tr></table>";
	// output HTML to page
	document.getElementById("db_select").innerHTML = textOut;
	// init global actual db
	sDB = db_list[0];
	// init global display db
	sDBName = db_Names[0];
}

// select DB
function Set_DB(objThis)
{
	// set sDB to id of sender
	sDB = objThis.id;
	// init index
	var iIndex = -1;
	// iterate thru db array to find name of newly selected db
	var i=0;
	for(i=0;i<aDBs.length;i++)
	{
		if(aDBs[i]==sDB)
		{
			iIndex=i;
		}
	}
	sDBName = aDBNames[iIndex];
	// set up new table to dispaly dbs
	var textOut = "<table class='strip'><tr><th class='section' title='Click one to select database'>Database: </th>";
	textOut += "<th class='field' id='"+sDBName+"' title='Selected Database'>"+sDBName+"</th><th> is selected. </th>";
	textOut += "<th><span class='tip'>Databases available --> </span></th>";
	// read db actual names
	var db_list  = aDBs;
	// read db display names
	var db_Names = aDBNames;
	// iterate thru arrays
	for(i=0;i<db_list.length;i++)
	{
		// set actual name
		var dbCode = db_list[i];
		// set display name
		var dbName = db_Names[i];
		// set title
		var sTitle = "Click to show tables in database: "+dbName+".";
		// if db not the selected db
		if(dbName!=sDB)
		{
			// output db item
			textOut += "<th class='field' id='"+dbCode+"' title='"+sTitle+"' onclick='Set_DB(this);'>"+dbName+"</th>";
		}
     }
	// finish table tag
	textOut += "</tr></table>";
	// output HTML to page
	document.getElementById("db_select").innerHTML = textOut;
	// clear query box
	Clear_Query();
	// init fields list table
	document.getElementById("field_list").innerHTML = "<table class='strip'><tr><th class='section'>Columns: </th><th><span class='tip'></span></th></tr></table>";
	// list the tables in database
	List_Tables();
}

// updates page when tables data is returned
function tablesListed()
{
	// if data ready
	if (getData[0].readyState==4)
	{
		// insert output of php file
		document.getElementById("table_select").innerHTML=getData[0].responseText;
		// canPlay true?
		if(canPlay)
		{
			// play sound
			document.getElementById('audTest').play();
		}
	}
}

// load tables from selected database
function List_Tables()
{
	// get ajax request object
	getData[0]=GetXmlHttpObject();
	// is successful get
	if(getData[0]!=null)
	{
		// clear current element
		document.getElementById("table_select").innerHTML="<table class='strip'><tr><th class='section'>Tables:</th><th><span class='tip'>Loading ...</span></th></tr></table>";
		// set url
		var url="list_tables.php";
		// adds required parameters to URL string
		url += "?dbName=" + sDB;
		// adds a random number to URL string to prevent loading cached page in IE7/8
		url += "&sid="+Math.random();
		// set return function
		getData[0].onreadystatechange=tablesListed;
		// set up and send the request
		getData[0].open("GET",url,true);
		getData[0].send(null);
	}
}

// updates page when table data is returned
function tableSelected()
{
	// if data ready
	if (getData[4].readyState==4)
	{
		// insert output of php file
		document.getElementById("table_select").innerHTML=getData[4].responseText;
		// run the queey function
		Run_Query();
	}
}

// load tables from selected database
function Select_Table()
{
	// get ajax request object
	getData[4]=GetXmlHttpObject();
	// is successful get
	if(getData[4]!=null)
	{
		// clear current element
		document.getElementById("table_select").innerHTML="<table class='strip'><tr><th class='section'>Tables:</th><th><span class='tip'>Loading ...</span></th></tr></table>";
		// set url
		var url="table_select.php";
		// adds required parameters to URL string
		url += "?dbName=" + sDB + "&tableName=" + sTable;
		// adds a random number to URL string to prevent loading cached page in IE7/8
		url += "&sid="+Math.random();
		// set return function
		getData[4].onreadystatechange=tableSelected;
		// set up and send the request
		getData[4].open("GET",url,true);
		getData[4].send(null);
	}
}

// table is selected
function Set_Table(objThis)
{
	// set sTable to id of sender
	sTable = objThis.id;
	// clear query box
	Clear_Query();
	// run select table function
	Select_Table()
	// run list fields function
	List_Fields();
	// output table query to query box
	document.getElementById('Query_Text').value = 'SELECT * FROM ' + sTable;
}


// updates page when field data is returned
function fieldsListed()
{
	// if data ready
	if (getData[1].readyState==4)
	{
		// insert output of php file
		document.getElementById("field_list").innerHTML=getData[1].responseText;
	}
}

// load fields from selected table
function List_Fields()
{
	// get ajax request object
	getData[1]=GetXmlHttpObject();
	// is successful get
	if(getData[1]!=null)
	{
		// clear current element
		document.getElementById("field_list").innerHTML="<table class='strip'><tr><th class='section'>Fields:</th><th><span class='tip'>Loading ...</span></th></tr></table>";
		// set url
		var url="field_list.php";
		// adds required parameters to URL string
		url += "?dbName="+ sDB +"&tableName=" + sTable;
		// adds a random number to URL string to prevent loading cached page in IE7/8
		url += "&sid="+Math.random();
		// set return function
		getData[1].onreadystatechange=fieldsListed;
		// set up and send the request
		getData[1].open("GET",url,true);
		getData[1].send(null);
	}
}

// updates page when field data is returned
function selectedField()
{
	// if data ready
	if (getData[5].readyState==4)
	{
		// insert output of php file
		document.getElementById("field_list").innerHTML=getData[5].responseText;
		// run query function
		Run_Query();
	}
}

// a field is selected
function Field_Select(objThis)
{
	// set sField to id of sender
	sField = objThis.id;
	// output field query to query box
	document.getElementById('Query_Text').value = 'SELECT ' + sField + ' FROM ' + sTable;
	// get ajax request object
	getData[5]=GetXmlHttpObject();
	// is successful get
	if(getData[5]!=null)
	{
		var sQuery = no_whitespace(document.getElementById('Query_Text').value);
		sQuery = encodeURIComponent(sQuery);
		// set url
		var url="field_select.php";
		// adds required parameters to URL string
		url += "?dbName=" + sDB + "&tableName=" + sTable +"&fieldName=" + sField;
		// adds a random number to URL string to prevent loading cached page in IE7/8
		url += "&sid="+Math.random();
		// set return function
		getData[5].onreadystatechange=selectedField;
		// set up and send the request
		getData[5].open("GET",url,true);
		getData[5].send(null);
	}
}

// updates page when data is returned
function queryRun()
{
	// if data ready
	if (getData[2].readyState==4)
	{
		// insert output of php file
		document.getElementById("query_output").innerHTML=getData[2].responseText;
		// run display HTML function
		Display_HTML();
	}
}

// load tables from selected database
function Run_Query()
{
	// set sQuery = contents of Query_Text
	sQuery = document.getElementById('Query_Text').value;
	if(checkQuery(sQuery)==false)
	{
		// run function to kill the query
		Kill_Query(sQuery);
	}
	else
	{
		// get ajax request object
		getData[2]=GetXmlHttpObject();
		// is successful get
		if(getData[2]!=null)
		{
			var sQuery = no_whitespace(document.getElementById('Query_Text').value);
			sQuery = encodeURIComponent(sQuery);
			// set url
			var url="result.php";
			// adds required parameters to URL string
			url += "?sDB=" + sDB + "&sDBName=" + sDBName + "&sQuery=" + encodeURIComponent(sQuery);
			// adds a random number to URL string to prevent loading cached page in IE7/8
			url += "&sid="+Math.random();
			// set return function
			getData[2].onreadystatechange=queryRun;
			// set up and send the request
			getData[2].open("GET",url,true);
			getData[2].send(null);
		}
	}
}

// function to check query for disallowed commands
// Note: the if statement can be altered to allow any of these commands if required.
function checkQuery(sQuery)
{
	// search for keywords
	var iSelect = sQuery.search(/select/i);
	var iDelete = sQuery.search(/delete/i);
	var iDrop = sQuery.search(/drop/i);
	var iUpdate = sQuery.search(/update/i);
	var iInsert = sQuery.search(/insert/i);
	var iReplace = sQuery.search(/replace/i);
	var iAlter = sQuery.search(/alter/i);
	var iCreate = sQuery.search(/create/i);
	var iRename = sQuery.search(/rename/i);
	var iTruncate = sQuery.search(/truncate/i);
	// id "Select not in query or delete, drop, update, insert, replace, alter, create, rename or truncate present
	if(iSelect<0 || iDelete>-1 || iDrop>-1 || iUpdate>-1 || iInsert>-1  || iReplace>-1  || iAlter>-1  || iCreate>-1  || iRename>-1  || iTruncate>-1)
	{
		return false;
	}
	else
	{
		return true;
	}

}

function Kill_Query(strQuery)
{
	// if query is not blank
	if(strQuery.length>0)
	{
		// build warning string
		var sWarning ='Your query: \n "'+strQuery+'", \n could not be executed! \n';
		sWarning += 'Only SELECT statements allowed in the McGoo_Query engine! \n';
		sWarning += 'UPDATE, INSERT, REPLACE, DELETE, TRUNCATE, ALTER, RENAME, CREATE and DROP are not permitted.'
		//clear saved queries
		Clear_Query();
		// display warning
		alert(sWarning);
	}

}

// updates page when data is returned
function htmlDisplayed()
{
	// if data ready
	if (getData[3].readyState==4)
	{
		// insert output of php file
//		alert(encodeURI(getData[3].responseText));
		document.getElementById("save_HTML").value=getData[3].responseText;
		if(canPlay)
		{
		// play sound
		document.getElementById('audTest').play();
		}
	}
}

// load tables from selected database
function Display_HTML ()
{
	// get ajax request object
	getData[3]=GetXmlHttpObject();
	// is successful get
	if(getData[3]!=null)
	{
		var sQuery = no_whitespace(document.getElementById('Query_Text').value);
		sQuery = encodeURIComponent(sQuery);
		// set url
		var url="result_table.php";
		// adds required parameters to URL string
		url += "?sDB=" + sDB +  "&sDBName=" + sDBName + "&sQuery=" + encodeURIComponent(sQuery);
		// adds a random number to URL string to prevent loading cached page in IE7/8
		url += "&sid="+Math.random();
		// set return function
		getData[3].onreadystatechange=htmlDisplayed;
		// set up and send the request
		getData[3].open("GET",url,true);
		getData[3].send(null);
	}
}

// function to clear saved queries
function Clear_Query()
{
	document.getElementById('Query_Text').value = '';
	document.getElementById('query_output').innerHTML = '';
	document.getElementById('save_HTML').value = '';
	document.getElementById('text_HTML').value = '';
}

// updates page when data is returned
function helpShown()
{
	// if data ready
	if (getData[6].readyState==4)
	{
		// insert output of php file
		document.getElementById("query_output").innerHTML=getData[6].responseText;
		// run function to display output in HTML window
		Display_HTML();
	}
}

// load tables from selected database
function showHelp()
{
	// get ajax request object
	getData[6]=GetXmlHttpObject();
	// is successful get
	if(getData[6]!=null)
	{
		var sQuery = no_whitespace(document.getElementById('Query_Text').value);
		sQuery = encodeURIComponent(sQuery);
		// set url
		var url="help.php?canCopy=" + canCopy;
		// adds a random number to URL string to prevent loading cached page in IE7/8
		url += "&sid="+Math.random();
		// set return function
		getData[6].onreadystatechange=helpShown;
		// set up and send the request
		getData[6].open("GET",url,true);
		getData[6].send(null);
	}
}

// function to show source HTM for query and result
function View_HTML()
{
	// set sHTML
	var sHTML = document.getElementById('save_HTML').value;
	// display decoded HTML in text window
	document.getElementById('text_HTML').value = unHTMLEntities(sHTML);
	// make view_HTML visible
	document.getElementById('view_HTML').style.display = 'block';

}

// select contents of text_HTML textarea
function selectHTML()
{
	// foucs on textarea
	document.getElementById('text_HTML').focus();
	// select all
     document.getElementById('text_HTML').select();

}


// this function allows copying of HTML to clipboard. Only works in IE at this stage
function copyHTML()
{
	document.getElementById('copy_HTML').value = unHTMLEntities(document.getElementById('save_HTML').value);
	document.getElementById('copy_HTML').focus();
     document.getElementById('copy_HTML').select();
     selElement = document.getElementById('copy_HTML');
	if(document.selection)
   	{
    		CopiedTxt = document.selection.createRange();
     	CopiedTxt.execCommand("Copy");
   	}
   	else
   	{
   		alert('MSIE function only for now!');
   	}
}

// hides HTML display
function Close_HTML()
{
	document.getElementById('view_HTML').style.display = 'none';
}

// this function allows shortcut key SHIFT + CTRL to run query
function checkRun(evt)
{
     // if not IE
	if(evt)
	{
          this.evt = evt;
     }
	else  // if IE
	{
          this.evt = window.event;
     }
     // if both keys are pressed
	if(evt.ctrlKey && evt.shiftKey)
     {
		// run query
		Run_Query();
	}
}