<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<%@ include file="/includes.jsp" %>

<link rel="stylesheet" href="../js/jquery/datatables.min.css" type="text/css" />
<script src="../js/jquery/datatables.min.js"> </script>


<script type="text/javascript">
	function fetchTableColumns(){
		var token = $("meta[name='_csrf']").attr("content");
		var header = $("meta[name='_csrf_header']").attr("content");

		document.getElementById('columnCell').style.display = "none";
		var selectedTableName=document.getElementById("selectedTableName").value;
		$.ajax({
			type: "POST",
		   	url: "../emdt/fetchColumnQueries.form",
		   	data: "tableName="+selectedTableName,
			beforeSend: function(xhr) {
				console.log('set request headers for columns');
				xhr.setRequestHeader(header, token);
			},
			success: function(msg){
		   		$('#selectedColumnName').empty(); //remove all child nodes
		   		var emptyOption = $('<option value="">Select Column Name Here</option>');
		        $('#selectedColumnName').append(emptyOption);	
		   	for(var index=0;index<msg.columnNamesByTableListSize; index++){
		        var columnOption = $('<option value="'+msg.columnNamesByTableList[index]+'">'+msg.columnNamesByTableList[index]+'</option>');
		        $('#selectedColumnName').append(columnOption);
		   	} 
		    $('#selectedColumnName').trigger("chosen:updated");
		    document.getElementById('columnCell').style.display = "block";
		   }
	  });
	}
	function addToSqlQuery(idString){
		var selectedString=document.getElementById(idString).value;
		var query = document.getElementById("queryToExecute").value +" "+ selectedString;
		query = query.replace(/  +/g, ' ');	
		document.getElementById("queryToExecute").value=query;
	}
	function trimAll(sString){
		while (sString.substring(0,1) === ' '){
			sString = sString.substring(1, sString.length);
		}
		while (sString.substring(sString.length-1, sString.length) === ' '){
			sString = sString.substring(0,sString.length-1);
		}
		return sString;
	}
	function queryAction(actionRequired){
		var queryToExecute = document.getElementById('queryToExecute').value;
		if(queryToExecute==null || trimAll(queryToExecute).length===0){
			alert("SQL query cannot be empty");
			document.getElementById("queryToExecute").focus();
		}else{
			document.getElementById("actionRequired").value=actionRequired;
			document.getElementById("sqlQueryForm").submit();
		}
	}
	function executeSavedQuery(){
		$('#queryToExecute').val(	$('#selectedSavedQuery:checked').val() );
		queryAction('executeQuery');
	}
</script>

<meta name="_csrf" content="${_csrf.token}"/>
<meta name="_csrf_header" content="${_csrf.headerName}"/>


<div class="breadcrumb">
You are here: <a href="../emdt/emdtHome.form"><img alt="" src="../emdt/images/16x16/icon.png" style="vertical-align: top;"/> eMDT</a> &gt; <a href="../emdt/reportMenu.jsp">Reports</a> &gt; <a class="selected" href="../emdt/sqlqueries.form">SQL Queries</a>
</div>

<br/>

<style>
	fieldset	{
		width:	50em;
		text-align: left;
		padding: 2em;
	}
	ul#savedQueriesList {
		list-style: none;
	}
	button	{
		cursor: pointer;
		padding: 0.5em;
	}
</style>
<!-- because the queries output can be very wide, left-aligned and indented the form so that it's not centred in the middle of w very results table and hence not visible to the user -->

<div style="text-align: left">

	<div style="margin-left: 10em;">

		<form id="sqlQueryForm" action="../emdt/sqlqueries.form?${_csrf.parameterName}=${_csrf.token}" method="POST">

		<input type="hidden" id="actionRequired" name="actionRequired">

			<fieldset>
				<legend>SQL Query</legend>

				<label for="queryToExecute">SQL Query</label>
				<br/>

				<textarea rows="6" cols="80" id="queryToExecute" name="queryToExecute" placeholder="Enter SQL query here" style="width: 100%;"><c:out value="${queryToExecute}"/></textarea>
				<br/>
				<label for="selectedTableName">Tables: </label>
				<select id="selectedTableName" class="chosen" title="Pick a table to add to the query"  onchange="fetchTableColumns();">
					<option value=""></option>
					<c:forEach var="tableName" items="${reportingExportTableList}">
						<option value="<c:out value="${tableName}"/>"><c:out value="${tableName}"/></option>
					</c:forEach>
				</select>
				<img src="../images/add.png" alt="Add table Name" onclick="addToSqlQuery('selectedTableName')" title="Insert into query"/> <br/>



				<div id="columnCell">
					<label for="selectedColumnName">Columns: </label>
					<select id="selectedColumnName" class="chosen" title="Pick a Column to add to the query"></select>
					<img src="../images/add.png" alt="Add column Name" onclick="addToSqlQuery('selectedColumnName')"  title="Insert into query"/>
				</div>


				<div align="right">
					<button onclick="queryAction('executeQuery');"> Execute SQL <img src="../images/16x16/database_go.png"/></button>
				</div>

			</fieldset>

			<c:if test="${!empty (errors)}">
				<span class="warning">Error: <c:out value="${errors}"/></span>
			</c:if>

			<fieldset>
				<legend>Saved Queries</legend>

				<label for="queryToExecute">Select a saved query to execute:</label>



				<c:if test="${savedQueryList != null and fn:length(savedQueryList) gt 0}">
					<ul id="savedQueriesList">
						<c:forEach var="savedQuery" items="${savedQueryList}">
							<li><input id="selectedSavedQuery" type="radio" value="<c:out value="${savedQuery}"/>"><c:out value="${savedQuery}"/></li>
						</c:forEach>
					</ul>
					<div align="right">
						<button onclick="executeSavedQuery()">Execute saved query<img src="../images/16x16/database_go.png"/></button><br/><br/>
					</div>
				</c:if>
				<div align="right">
					<button onclick="queryAction('saveQuery');">Save current query <img src="../images/16x16/database_add.png"/></button>
				</div>

			</fieldset>


		</form>
	</div>
<c:if test="${results != null and fn:length(results) gt 0}">
	<br />
		<table width="90%"><tr><td><tags:table title="Results">
		<table class="display" id="resultsTable">
			<c:if test="${error ne null}">
			<tr><td align="center"><font color="Red"><c:out value="${error }"/></font></td></tr></c:if>
			<c:forEach var="row" items="${results }" varStatus="resultsStatus">
				<c:choose>
					<c:when test="${resultsStatus.count == 1}">
						<thead>
							<tr>
								<c:forEach var="item" items="${row}">
									<th><c:out value="${item }"/></th>
								</c:forEach>
							</tr>
						</thead>
					</c:when>
					<c:otherwise>
						<tr>
							<c:forEach var="item" items="${row}">
								<td align="center"><c:out value="${item }"/></td>
							</c:forEach>
						</tr>
					</c:otherwise>
				</c:choose>
			</c:forEach>
		</table></tags:table></td></tr></table>
	<br />
		&#160;<a href="sqlqueries.form?actionRequired=csvResult"
		title="Download CSV">Download CSV <img
		src="../images/16x16/database_save.png" alt="Download" />
	</a>
	<br />
	<br />
	<br />

	<script>
	$(document).ready(function(){
	    $('#resultsTable').DataTable();
	});
	</script>
</c:if>



</div>