<%@ page import="java.sql.*"%>
<%@ page import="java.util.*"%>
<%@ page import="net.sf.json.*" %>
<%@ page import="egurkha.EgDBConnectUI"%>
<%@ page import="com.egurkha.util.StringUtils"%>
<%@ page import="com.eg.EgConMonHelper"%>
<%@ page import = "com.eg.IniFile"%>
<%@ page import = "com.eg.EgConfigInfo"%>
<%@ page import = "com.eg.EgDbInfo"%>

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>

<%
	JSONObject rootJSON = new JSONObject();
	JSONArray columnArray = new JSONArray();
	JSONArray rowArray = new JSONArray();

	response.setContentType("application/json;charset=UTF-8");
	String s_query = egurkha.EgUIUtils.decodeSecurityChar(request.getParameter("s_query"));
	long rec_limit = Long.parseLong(request.getParameter("rec_limit"));
	int colCount = 0;
	int rowCount = 0;
	String result = "";
	boolean dataFetched = false;
	//List csvData = new ArrayList();

	if(s_query != null)
	{
		if(s_query.toLowerCase().startsWith("select"))
		{
			/*csvData.add("TOOLKIT - VIEW DATA");
			csvData.add("");
			csvData.add("");
			csvData.add("Query ,"+"\""+s_query+"\"");
			csvData.add("");
			csvData.add("");*/
			Vector colNameHeader = new Vector();
			String colNames = "";
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			ResultSetMetaData rsmd = null;
			EgDBConnectUI dbconnect = new EgDBConnectUI();
			try
			{
				conn = dbconnect.getConnection();
				stmt = conn.createStatement();
				rs = stmt.executeQuery(rebuildQuery(s_query.trim()));
				rsmd = rs.getMetaData();
				if(rsmd != null)
				{
					colCount = rsmd.getColumnCount();
					JSONObject columnHeader = new JSONObject();
					columnHeader.put("title","S.NO");
					columnArray.add(columnHeader);
					for(int c=1; c<=colCount; c++)
					{
						String col_name = rsmd.getColumnName(c);
						if(s_query.toLowerCase().indexOf("count(") >0 && colCount == 1 && col_name.trim().length() ==0)
						{
							col_name = "COUNT";
						}
						columnHeader = new JSONObject();
						columnHeader.put("title",col_name);
						columnArray.add(columnHeader);
						if(colNames.length() > 0)
						{
							colNames += ","+col_name;
						}
						else
						{
							colNames += col_name;
						}
					}
				}
				colNameHeader.add(colNames);
				//csvData.add(colNameHeader);
				while(rs.next())
				{
					String dataString = "";
					rowCount ++;
					JSONArray cellDataArray = new JSONArray();
					cellDataArray.add("");
					for(int c=1; c<=colCount; c++)
					{
						String data = rs.getString(c);
						if (data != null && data.indexOf("<") != -1)
						{
							data = StringUtils.replace(data,"<","&lt;");
						}
						if (data != null && data.indexOf(">") != -1)
						{
							data = StringUtils.replace(data,">","&gt;");
						}
						if(data == null)
						{
							data = "NULL";
						}
						/*String csvCellData = data;
						if(csvCellData != null)
						{
							if(csvCellData.length() == 0)
							{
								csvCellData = " ";
							}
							else if(csvCellData.indexOf(",") >-1)
							{
								csvCellData = StringUtils.replace(csvCellData,",","^");
							}
						}
						if(dataString.length() > 0)
						{
							dataString += "," + csvCellData;
						}
						else
						{
							dataString += csvCellData;
						}*/
						cellDataArray.add(data);
					}
					rowArray.add(cellDataArray);
					//csvData.add(dataString);
				}
				//csvData.add(4,"Total Records,"+rowCount);
				if(colCount >0 || rowCount >0)
				{
					dataFetched = true;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
				dbconnect.changeConn();
				dataFetched = false;
				result = "Problem executing query... "+e.toString();
			}
			finally
			{
				try
				{
					if(rs != null)
						rs.close();
					if(stmt != null)
						stmt.close();
					dbconnect.releaseConnection();
				}
				catch(Exception e1)
				{
					e1.printStackTrace();
				}
				rs=null;
				stmt=null;
				rsmd=null;
			}
		}
		else
		{
			Connection conn = null;
			Statement stmt = null;
			EgDBConnectUI dbconnect = new EgDBConnectUI();
			try
			{
				conn = dbconnect.getConnection();
				stmt = conn.createStatement();
				rowCount = stmt.executeUpdate(s_query);
				result = "Number of rows updated ... "+rowCount;
			}
			catch(Exception e)
			{
				e.printStackTrace();
				dbconnect.changeConn();
				dataFetched = false;
				result = "Problem executing query... "+e.toString();
			}
			finally
			{
				try
				{
					if(stmt != null)
						stmt.close();
					dbconnect.releaseConnection();
				}
				catch(Exception e1)
				{
					e1.printStackTrace();
				}
				stmt=null;
			}
		}
	}
	if(colCount > 0 && rowCount > 0 && rowCount <= rec_limit)
	{
		//session.setAttribute("csvListFromTk",csvData);
	}
	if(result != null && result.trim().length() > 0)
	{
		rootJSON.put("msgHeader",result.startsWith("Problem") ? "Error!" : "Success!");
		rootJSON.put("msgBody",result);
	}
	else
	{
		rootJSON.put("tableHeader",columnArray);
		rootJSON.put("tableData",rowArray);
	}
	rootJSON.put("dataFetched",dataFetched);
	out.println(rootJSON);
%>

<%!
	private String rebuildQuery(String query)
	{
		int ui_record_Limit = 10000;
		IniFile iniUIFile = new IniFile(EgConfigInfo.egMgrConfigDir+"eg_ui.ini");
		String ui_limitStr = iniUIFile.getValue("TOOLKIT_VIEWDATA_SETTINGS","UI_Record_Limit");
		if (ui_limitStr != null && ui_limitStr.trim().length() > 0)
		{
			try
			{
				ui_record_Limit = Integer.parseInt(ui_limitStr);
				if (ui_record_Limit <= 0)
				{
					ui_record_Limit = 10000;
				}
			}
			catch(Exception e)
			{
				ui_record_Limit = 10000;
			}
		}
		String str_rec_limit = String.valueOf(ui_record_Limit);
		String tmpQry = query.toLowerCase();
		String db_type = new EgDbInfo("manager").checkDBType();

		if (db_type != null && db_type.equalsIgnoreCase("EgMsSqlAdapter"))
		{
			if (tmpQry.indexOf(" count(*) ") >= 6  || tmpQry.indexOf(" count(*) ") >= 6 || tmpQry.indexOf(" top ") >= 6)
			{
				return query;
			}
			int idx = query.indexOf("select") + 6;
			StringBuffer sb_query = new StringBuffer();
			sb_query.append(query.substring(0,idx));
			sb_query.append(" top " + str_rec_limit + " ");
			sb_query.append(query.substring(idx));
			query = sb_query.toString();
		}
		else if (db_type != null && db_type.equalsIgnoreCase("EgOracleAdapter"))
		{
			if (tmpQry.indexOf(" count(*) ") >= 6 || tmpQry.indexOf(" count(1) ") >= 6)
			{
				return query;
			}
			query = "select * from ( " + query + " ) where rownum <= " + str_rec_limit;
		}
		return query;
	}
%>



