From 5d7a3018a6bee66bac0e3a915198a9886a091880 Mon Sep 17 00:00:00 2001
From: Natasha Sherstneva <n.shertneva@gmail.com>
Date: Fri, 8 Nov 2013 11:10:46 +0000
Subject: [PATCH] fix query log info, jobs by counter

---
 .../cassandra/CassandraNativeConnector.java        |   46 +++++++++++++++-----
 datadb/compbio/cassandra/DataBase.java             |    9 ++++
 datadb/compbio/cassandra/StructureJobLog.java      |   45 +++++++++++++++++++
 .../{LogServlet.java => ServletLogInfo.java}       |   13 +++---
 .../compbio/listeners/ServletSequenceProtein.java  |    2 +-
 server/compbio/statistic/CassandraRequester.java   |   27 ++++++++++--
 website/AllReports.jsp                             |    2 +-
 website/{LogQuery.jsp => QueryLogInfo.jsp}         |    2 +-
 website/{ReportLog.jsp => ReportLogInfo.jsp}       |   14 ++----
 website/ReportSequenceProtein.jsp                  |    2 +-
 10 files changed, 130 insertions(+), 32 deletions(-)
 create mode 100644 datadb/compbio/cassandra/StructureJobLog.java
 rename server/compbio/listeners/{LogServlet.java => ServletLogInfo.java} (80%)
 rename website/{LogQuery.jsp => QueryLogInfo.jsp} (90%)
 rename website/{ReportLog.jsp => ReportLogInfo.jsp} (65%)

diff --git a/datadb/compbio/cassandra/CassandraNativeConnector.java b/datadb/compbio/cassandra/CassandraNativeConnector.java
index b038457..5593594 100644
--- a/datadb/compbio/cassandra/CassandraNativeConnector.java
+++ b/datadb/compbio/cassandra/CassandraNativeConnector.java
@@ -79,7 +79,6 @@ public class CassandraNativeConnector {
 	 */
 	public void InsertData(long jobtime, String startdate, String enddate, String ip, String jobid, String statusEx, String statusFinal,
 			String protein, List<FastaSequence> predictions) {
-
 		String check1 = "SELECT * FROM ProteinKeyspace.ProteinLog WHERE JobID = '" + jobid + "';";
 		ResultSet results1 = session.execute(check1);
 		if (results1.isExhausted()) {
@@ -87,11 +86,9 @@ public class CassandraNativeConnector {
 					+ "(JobID, IP, DataBegin, DataEnd, FinalStatus, ExecutionStatus, Protein)" + " VALUES ('" + jobid + "','" + ip + "','"
 					+ startdate + "','" + enddate + "','" + statusFinal + "','" + statusEx + "','" + protein + "');";
 			session.execute(com1);
-
 			String com2 = "INSERT INTO ProteinKeyspace.ProteinData " + "(jobtime, JobID, Protein)" + " VALUES (" + jobtime + ",'" + jobid
 					+ "','" + protein + "');";
 			session.execute(com2);
-
 			String allpredictions = "";
 			for (FastaSequence pred : predictions) {
 				String predictionname = pred.getId();
@@ -102,14 +99,14 @@ public class CassandraNativeConnector {
 			if (null != allpredictions) {
 				final_prediction = allpredictions.substring(0, allpredictions.length() - 1);
 			}
-
-			String check2 = "SELECT * FROM ProteinKeyspace.ProteinRow WHERE JobID = '" + jobid + "';";
+			String check2 = "SELECT * FROM ProteinKeyspace.ProteinRow WHERE JobID = '" + jobid + "' ALLOW FILTERING;";
 			ResultSet results2 = session.execute(check2);
 			if (results2.isExhausted()) {
 				String com3 = "INSERT INTO ProteinKeyspace.ProteinRow " + "(Protein, JobID, Predictions)" + " VALUES ('" 
 			+ protein + "','" + jobid + "',{" + final_prediction + "});";
 				session.execute(com3);
 			}
+			String check3 = "SELECT * FROM ProteinKeyspace.ProteinRow WHERE JobID = '" + jobid + "';";
 		}
 	}
 
@@ -212,9 +209,9 @@ public class CassandraNativeConnector {
 	/*
 	 * getting protein sequences by counter
 	 */
-	public List<Pair<String, Integer>>  ReadProteinDataByCounter(int counter) {
+	public Map<String, Integer>  ReadProteinDataByCounter() {
 		final long startTime = System.currentTimeMillis();
-		String com = "SELECT DISTINCT Protein FROM ProteinKeyspace.ProteinRow;";
+		String com = "SELECT Protein FROM ProteinKeyspace.ProteinRow;";
 		System.out.println("Command: " + com);
 		ResultSet results = session.execute(com);
 		if (results.isExhausted())
@@ -223,17 +220,46 @@ public class CassandraNativeConnector {
 		List<Row> rows = results.all();
 		System.out.println ("Query time is " + (queryTime - startTime) + " msec");   
 		System.out.println (" rows analysed,  " + rows.size());
-		List<Pair<String, Integer>>  res = new ArrayList<Pair<String, Integer>>();
+		Map<String, Integer> res = new HashMap<String, Integer>();
 		int c = 0;
 		for (Row r : rows) {
-			String prot = r.getString("Protein");
-			
+			String protein = r.getString("Protein");
+			if (res.containsKey(protein)) 
+				res.put(protein, res.get(protein) + 1);
+			else
+				res.put(protein, 1);
 		}
 		final long endTime = System.currentTimeMillis();
 		System.out.println (c + " rows analysed, execution time is " + (endTime - startTime) + " msec");
 		return res;
 	}
 	
+	
+	/*
+	 * getting protein sequences by counter
+	 */
+	public StructureJobLog ReadJobLog(String jobid) {
+		final long startTime = System.currentTimeMillis();
+		String com = "SELECT * FROM ProteinKeyspace.ProteinLog WHERE JobID = '" + jobid + "';";
+		System.out.println("Command: " + com);
+		ResultSet results = session.execute(com);
+		if (results.isExhausted())
+			return null;
+		final long queryTime = System.currentTimeMillis();
+		Row row = results.one();
+		String com1 = "SELECT * FROM ProteinKeyspace.ProteinRow WHERE JobID = '" + jobid + "' ALLOW FILTERING;";
+		System.out.println("Command: " + com1);
+		ResultSet results1 = session.execute(com1);
+		if (results1.isExhausted())
+			return null;
+		Row row1 = results1.one();
+		StructureJobLog res = new StructureJobLog(row.getString("Protein"), row.getString("JobID"), row.getString("DataBegin"), row.getString("DataEnd"), row.getString("ip"), row1.getMap("Predictions", String.class, String.class));
+		System.out.println ("Query time is " + (queryTime - startTime) + " msec");   
+		final long endTime = System.currentTimeMillis();
+		System.out.println (" rows analysed, execution time is " + (endTime - startTime) + " msec");
+		return res;
+	}
+	
 	/*
 	 * getting earlest date of jobs from the db
 	 */
diff --git a/datadb/compbio/cassandra/DataBase.java b/datadb/compbio/cassandra/DataBase.java
index cccf64b..91eabf0 100644
--- a/datadb/compbio/cassandra/DataBase.java
+++ b/datadb/compbio/cassandra/DataBase.java
@@ -15,6 +15,7 @@ public class DataBase {
 	private List<String> subProt;
 	private List<Integer> timeRez;
 	private List<Integer> timeTotalExec;
+	private StructureJobLog logInfo;
 
 	public DataBase() {
 	}
@@ -104,5 +105,13 @@ public class DataBase {
 	public List<Integer> getTimeTotalExec() {
 		return timeTotalExec;
 	}
+	
+	public void setLogInfo(StructureJobLog logInfo){
+		this.logInfo = logInfo;
+	}
+	
+	public StructureJobLog getLogInfo() {
+		return logInfo;
+	}
 
 }
diff --git a/datadb/compbio/cassandra/StructureJobLog.java b/datadb/compbio/cassandra/StructureJobLog.java
new file mode 100644
index 0000000..4f2414b
--- /dev/null
+++ b/datadb/compbio/cassandra/StructureJobLog.java
@@ -0,0 +1,45 @@
+package compbio.cassandra;
+
+import java.util.Map;
+
+public class StructureJobLog {
+	private String jobid;
+	private String dateStart;
+	private String dateEnd;
+	private String sequence;
+	private String ip;	
+	private Map<String,String> prediction;
+	
+	public StructureJobLog (String seq, String id, String  dateStart, String  dateEnd, String ip, Map<String,String> pred) {
+		this.sequence = seq;
+		this.jobid = id;
+		this.dateStart = dateStart;
+		this.dateEnd = dateEnd;
+		this.ip = ip;
+		this.prediction = pred;
+	}
+	
+	public String getSequence () {
+		return sequence;
+	}
+	
+	public String getJobid () {
+		return jobid;
+	}
+	
+	public String getDateStart () {
+		return dateStart;
+	}
+	
+	public String getDateEnd () {
+		return dateEnd;
+	}
+	
+	public String getIP () {
+		return ip;
+	}
+	
+	public Map<String,String> getPrediction () {
+		return prediction;
+	}
+}
diff --git a/server/compbio/listeners/LogServlet.java b/server/compbio/listeners/ServletLogInfo.java
similarity index 80%
rename from server/compbio/listeners/LogServlet.java
rename to server/compbio/listeners/ServletLogInfo.java
index 0f6d007..87c8f92 100644
--- a/server/compbio/listeners/LogServlet.java
+++ b/server/compbio/listeners/ServletLogInfo.java
@@ -4,16 +4,19 @@ import java.io.IOException;
 
 import javax.servlet.RequestDispatcher;
 import javax.servlet.ServletException;
+import javax.servlet.annotation.WebServlet;
 import javax.servlet.http.HttpServlet;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 
-import compbio.statistic.StatisticsProt;
+import compbio.statistic.CassandraRequester;
+
 
 /**
  * Servlet implementation class LogServlet
  */
-public class LogServlet extends HttpServlet {
+@WebServlet("/ServletLogInfo")
+public class ServletLogInfo extends HttpServlet {
 	private static final long serialVersionUID = 1L;
        
     
@@ -24,12 +27,12 @@ public class LogServlet extends HttpServlet {
 	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 		final long startTime = System.currentTimeMillis();
 		String id = request.getParameter("IdJob");
-		StatisticsProt sp = new StatisticsProt();
-//		request.setAttribute("result", sp.readJobLog(id));
+		CassandraRequester cr = new CassandraRequester();
+		request.setAttribute("result", cr.readJobLog(id));
 		final long endTime = System.currentTimeMillis();
 		request.setAttribute("timeExecution", (endTime - startTime));
 		request.setAttribute("IdJob", id);
-		RequestDispatcher rd = request.getRequestDispatcher("/ReportLog.jsp");
+		RequestDispatcher rd = request.getRequestDispatcher("/ReportLogInfo.jsp");
 		rd.forward(request, response);
 	}
 
diff --git a/server/compbio/listeners/ServletSequenceProtein.java b/server/compbio/listeners/ServletSequenceProtein.java
index 6a10b94..94df90c 100644
--- a/server/compbio/listeners/ServletSequenceProtein.java
+++ b/server/compbio/listeners/ServletSequenceProtein.java
@@ -31,7 +31,7 @@ public class ServletSequenceProtein extends HttpServlet {
 		int counter = Integer.parseInt(request.getParameter("counterJob"));
 		CassandraRequester cr = new CassandraRequester();
 		if (search.equals("Search counter")) {
-	//		request.setAttribute("result", sp.readProtID(counter));
+	 		request.setAttribute("result", cr.readProteinByCounter(counter));
 		} else {
 				request.setAttribute("result", cr.readProteins(prot, flag));
 		}
diff --git a/server/compbio/statistic/CassandraRequester.java b/server/compbio/statistic/CassandraRequester.java
index b7f83a8..5682cdb 100755
--- a/server/compbio/statistic/CassandraRequester.java
+++ b/server/compbio/statistic/CassandraRequester.java
@@ -12,6 +12,7 @@ import java.util.Map;
 import compbio.cassandra.CassandraNativeConnector;
 import compbio.cassandra.DataBase;
 import compbio.cassandra.Pair;
+import compbio.cassandra.StructureJobLog;
 import compbio.cassandra.StructureProteinPrediction;
 
 public class CassandraRequester {
@@ -182,12 +183,32 @@ public class CassandraRequester {
 	 * */
 	public List<DataBase> readProteinByCounter(int counter) {
 		query = new ArrayList<DataBase>();
-	//	List<Pair<String, String>> res = DBInstance.ReadProteinDataByCounter(counter);
-		
+		Map<String, Integer> map = DBInstance.ReadProteinDataByCounter();
+		for (Map.Entry<String, Integer> entry : map.entrySet()) {
+			if (entry.getValue() > counter) {
+				DataBase db = new DataBase();
+				db.setTotalId(entry.getValue());
+				db.setProt(entry.getKey());
+				query.add(db);
+			}
+		}
 		return query;
 	}
 	
-	
+	/*
+	 * query jobs log info
+	 */
+	public DataBase readJobLog(String jobid) {
+	//	query = new ArrayList<DataBase>();
+		StructureJobLog res = DBInstance.ReadJobLog(jobid);
+		DataBase query = new DataBase();
+		query.setLogInfo(res);
+	//	query.setres);
+		return query;
+	}
+	/*
+	 * create list of parts of protein sequence;
+	 */
 	private static List<String> CreateSubprot (String protein, String subprot) {
 		List<String> sub = new ArrayList<String>();
 		String subStr = protein;
diff --git a/website/AllReports.jsp b/website/AllReports.jsp
index fabdf6d..ea597a9 100644
--- a/website/AllReports.jsp
+++ b/website/AllReports.jsp
@@ -7,5 +7,5 @@
 	else if (query.equals("protein"))
 		response.sendRedirect("QuerySequenceProtein.jsp");
 	else if (query.equals("log"))
-		response.sendRedirect("LogQuery.jsp");
+		response.sendRedirect("QueryLogInfo.jsp");
 %>
\ No newline at end of file
diff --git a/website/LogQuery.jsp b/website/QueryLogInfo.jsp
similarity index 90%
rename from website/LogQuery.jsp
rename to website/QueryLogInfo.jsp
index a14fb87..34f0f9f 100644
--- a/website/LogQuery.jsp
+++ b/website/QueryLogInfo.jsp
@@ -8,7 +8,7 @@
 </head>
 <body>
 	<br/>
-	<form method="get" action="LogServlet">
+	<form method="get" action="ServletLogInfo">
 		<h3>Enter job ID</h3>	
 		<input type="text" name="IdJob"><br/>
 		<input type="submit" name="Search" value="Search"/><br/><br/>
diff --git a/website/ReportLog.jsp b/website/ReportLogInfo.jsp
similarity index 65%
rename from website/ReportLog.jsp
rename to website/ReportLogInfo.jsp
index e3cc33e..d4079ad 100644
--- a/website/ReportLog.jsp
+++ b/website/ReportLogInfo.jsp
@@ -5,13 +5,7 @@
 <%@ taglib uri="http://displaytag.sf.net" prefix="dt" %>
 <h3>Jobs log information for: ${IdJob}</h3>
 <h3>Time execution: ${timeExecution} ms</h3>
-<c:forEach items="${result}" var="res">
-<p>${res.nameColumn} :
-${res.valueColumn}
-</p>
-</c:forEach>
-<c:forEach items="${result}" var="res">
-<p>${res.namePrediction} :
-${res.valuePrediction}
-</p>
-</c:forEach>
\ No newline at end of file
+<p> Date Start : ${result.logInfo.dateStart}</p>
+<p> Date End   : ${result.logInfo.dateEnd}</p>
+<p> Sequence   : ${result.logInfo.sequence}</p>
+
diff --git a/website/ReportSequenceProtein.jsp b/website/ReportSequenceProtein.jsp
index 3f6028a..0459668 100644
--- a/website/ReportSequenceProtein.jsp
+++ b/website/ReportSequenceProtein.jsp
@@ -29,7 +29,7 @@
 						<td
 							style="text-align: left; border-buttom: dotted; font-family: monospace"><a
 							title="Click to view predictions"
-							href="ProtServlet?prot=${res.prot}&protein=whole&Search=Search+sequence&counterJob=${counter}">${res.prot}</a>
+							href="ServletSequenceProtein?prot=${res.prot}&protein=whole&Search=Search+sequence&counterJob=${counter}">${res.prot}</a>
 						</td>
 					</tr>
 				</c:forEach>
-- 
1.7.10.2