From 16c02499a4eadc9d54c88bdb4402403823a86bd2 Mon Sep 17 00:00:00 2001
From: Sasha Sherstnev <a.sherstnev@dundee.ac.uk>
Date: Fri, 29 Nov 2013 12:14:17 +0000
Subject: [PATCH] Add servlets for queries with IP

---
 datadb/compbio/cassandra/CassandraReader.java      |   60 +++++++++++++++++++
 datadb/compbio/cassandra/DataBase.java             |    9 +++
 .../controllers/IPStatisticsController.java        |   62 ++++++++++++++++++++
 server/compbio/statistic/CassandraRequester.java   |   45 ++++++++++++++
 webapp/view/queryIP.jsp                            |   25 ++++++++
 webapp/view/queryIPStatistics.jsp                  |   24 ++++++++
 webapp/view/reportIP.jsp                           |   54 +++++++++++++++++
 webapp/view/reportIPstatistics.jsp                 |   49 ++++++++++++++++
 8 files changed, 328 insertions(+)
 create mode 100644 server/compbio/controllers/IPStatisticsController.java
 create mode 100644 webapp/view/queryIP.jsp
 create mode 100644 webapp/view/queryIPStatistics.jsp
 create mode 100644 webapp/view/reportIP.jsp
 create mode 100644 webapp/view/reportIPstatistics.jsp

diff --git a/datadb/compbio/cassandra/CassandraReader.java b/datadb/compbio/cassandra/CassandraReader.java
index cb2492e..e75f148 100644
--- a/datadb/compbio/cassandra/CassandraReader.java
+++ b/datadb/compbio/cassandra/CassandraReader.java
@@ -122,6 +122,37 @@ public class CassandraReader {
 		return res;
 	}
 
+	
+	/*
+	 * getting jobs by ip
+	 */
+	public List<Pair<String, String>> ReadIpWithJobs(String ip) {
+		final long startTime = System.currentTimeMillis();
+		String com = "SELECT JobID, Protein, FinalStatus FROM ProteinLog WHERE ip = '" + ip + "';";
+		System.out.println("Command: " + com);
+		ResultSet results = session.execute(com);
+		if (results.isExhausted())
+			return null;
+		final long queryTime = System.currentTimeMillis();
+		List<Row> rows = results.all();
+		List<Pair<String, String>> res = new ArrayList<Pair<String, String>>();
+		System.out.println("Query time is " + (queryTime - startTime) + " msec");
+		System.out.println(" rows analysed,  " + rows.size());
+		int c = 0;
+		for (Row r : rows) {
+			if (r.getString("FinalStatus").equals("OK")) { 
+				Pair<String, String> pair = new Pair<String, String>(r.getString("JobID"), r.getString("Protein"));
+				System.out.println(pair.getElement0());
+				System.out.println(pair.getElement1());
+				res.add(pair);
+				++c;
+			}
+		}
+		final long endTime = System.currentTimeMillis();
+		System.out.println(c + " rows analysed, execution time is " + (endTime - startTime) + " msec");
+		return res;
+	}
+
 	/*
 	 * getting part of protein sequence from the db ProteinRow
 	 */
@@ -182,6 +213,35 @@ public class CassandraReader {
 	}
 
 	/*
+	 * getting ip by counter
+	 */
+	public Map<String, Integer> ReadIpByCounter() {
+		final long startTime = System.currentTimeMillis();
+		String com = "SELECT JobID, ip FROM ProteinLog;";
+		System.out.println("Command: " + com);
+		ResultSet results = session.execute(com);
+		if (results.isExhausted())
+			return null;
+		final long queryTime = System.currentTimeMillis();
+		List<Row> rows = results.all();
+		System.out.println("Query time is " + (queryTime - startTime) + " msec");
+		System.out.println(" rows analysed,  " + rows.size());
+		Map<String, Integer> res = new HashMap<String, Integer>();
+		int c = 0;
+		for (Row r : rows) {
+			String protein = r.getString("ip");
+			String id = r.getString("JobID");
+			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 log info for jobid
 	 */
 	public StructureJobLog ReadJobLog(String jobid) {
diff --git a/datadb/compbio/cassandra/DataBase.java b/datadb/compbio/cassandra/DataBase.java
index c6777fc..26a019a 100644
--- a/datadb/compbio/cassandra/DataBase.java
+++ b/datadb/compbio/cassandra/DataBase.java
@@ -14,6 +14,7 @@ public class DataBase {
 	private int totalJobs;
 	private int totalId;				// total jobs for current protein sequence
 	private String id;
+	private String ip;
 	private String prot;				// protein sequence
 	private String jpred;
 	private List<String> subProt;		// protein sequence divided by several parts for highlighting the particular part
@@ -116,6 +117,14 @@ public class DataBase {
 	public String getId() {
 		return id;
 	}
+	
+	public void setIp(String ip) {
+		this.ip = ip;
+	}
+
+	public String getIp() {
+		return ip;
+	}
 
 	public void setSubProt(List<String> subProt) {
 		this.subProt = subProt;
diff --git a/server/compbio/controllers/IPStatisticsController.java b/server/compbio/controllers/IPStatisticsController.java
new file mode 100644
index 0000000..75615f9
--- /dev/null
+++ b/server/compbio/controllers/IPStatisticsController.java
@@ -0,0 +1,62 @@
+package compbio.controllers;
+
+import java.util.List;
+import java.util.Map;
+
+import org.springframework.stereotype.Controller;
+import org.springframework.web.bind.annotation.RequestMapping;
+import org.springframework.web.bind.annotation.RequestMethod;
+import org.springframework.web.bind.annotation.RequestParam;
+
+import compbio.cassandra.DataBase;
+import compbio.statistic.CassandraRequester;
+
+/**
+ * @author Alexander Sherstnev
+ * @author Natasha Sherstneva
+ */
+@Controller
+public class IPStatisticsController {
+
+	@RequestMapping(value = "/ip/stat", method = RequestMethod.GET)
+	public String initStatisticsForm(Map<String, Object> model) {
+		return "queryIPStatistics";
+	}
+
+	@RequestMapping(value = "/ip", method = RequestMethod.GET)
+	public String initOneIPForm(Map<String, Object> model) {
+		return "queryIP";
+	}
+	
+	@RequestMapping(value = "/ip/stat/querycounter", method = RequestMethod.GET)
+	public String findIPwithCounter(@RequestParam("JobCounter") int counter, Map<String, Object> model) {
+		final long startTime = System.currentTimeMillis();
+		CassandraRequester cr = new CassandraRequester();
+		List<DataBase> r = cr.readIpByCounter(counter);
+ 		model.put("results", r);
+		model.put("njobs", 0);
+		if (null != r) {
+			model.put("njobs", r.size());
+		}
+ 		final long endTime = System.currentTimeMillis();
+		model.put("timeExecution", (endTime - startTime));
+		model.put("counter", counter);
+		return "reportIPstatistics";
+	}
+
+	@RequestMapping(value = "/ip/query", method = RequestMethod.GET)
+	public String findIP(@RequestParam("ip") String ip, Map<String, Object> model) {
+		final long startTime = System.currentTimeMillis();
+		CassandraRequester cr = new CassandraRequester();
+		List<DataBase> r = cr.readIp(ip);
+ 		model.put("results", r);
+		model.put("njobs", 0);
+		if (null != r) {
+			model.put("njobs", r.size());
+		}
+ 		final long endTime = System.currentTimeMillis();
+		model.put("timeExecution", (endTime - startTime));
+		model.put("ip", ip);
+		return "reportIP";
+	}
+}
diff --git a/server/compbio/statistic/CassandraRequester.java b/server/compbio/statistic/CassandraRequester.java
index 72ec074..f4c4c18 100755
--- a/server/compbio/statistic/CassandraRequester.java
+++ b/server/compbio/statistic/CassandraRequester.java
@@ -235,6 +235,26 @@ public class CassandraRequester {
 		return query;
 	}
 	
+	/* 
+	 * query ip with number of jobs
+	 */
+	public List<DataBase> readIpByCounter(Integer minimalcounter) {
+		query = new ArrayList<DataBase>();
+		Map<String, Integer> map = db.ReadIpByCounter();
+		if (minimalcounter == null)
+			minimalcounter = 0;
+		if (map == null)
+			return null;
+		for (Map.Entry<String, Integer> entry : map.entrySet()) {
+			if (entry.getValue() > minimalcounter) {
+				DataBase bean = new DataBase();
+				bean.setTotalId(entry.getValue());
+				bean.setIp(entry.getKey());
+				query.add(bean);
+			}
+		}
+		return query;
+	}
 	/*
 	 * query jobs log info
 	 */
@@ -249,6 +269,31 @@ public class CassandraRequester {
 		return query;
 	}
 	
+	
+	/*
+	 * query jobs by ipStructureJobLog
+	 */
+	public List<DataBase> readIp(String ip) {
+		if (ip == null)
+			return null;
+		List<Pair<String, String>> res = db.ReadIpWithJobs(ip);
+	//	System.out.println(res.size());
+		if (res == null) 
+			return null;
+		query = new ArrayList<DataBase>();
+		for (Pair<String, String> entry : res) {	
+			System.out.println("ip " + ip);
+			System.out.println("id " + entry.getElement0());
+			DataBase bean = new DataBase();
+			bean.setIp(ip);
+			bean.setId(entry.getElement0());
+			bean.setProt(entry.getElement1());
+			query.add(bean);
+		}
+		System.out.println(query.size());
+		return query;
+	}
+	
 	/*
 	 * create list of parts of protein sequence;
 	 */
diff --git a/webapp/view/queryIP.jsp b/webapp/view/queryIP.jsp
new file mode 100644
index 0000000..39fa427
--- /dev/null
+++ b/webapp/view/queryIP.jsp
@@ -0,0 +1,25 @@
+<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
+<%@page import="java.util.Calendar"%>
+<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+
+<%@ taglib prefix="spring" uri="http://www.springframework.org/tags"%>
+<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
+<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
+<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
+
+<html>
+<jsp:include page="fragments/header.jsp" />
+<body>
+	<div class="container">
+	<jsp:include page="fragments/mainmenu.jsp" />
+
+	<spring:url value="/ip/query" var="formurl"/>
+	<form method="get" action="${fn:escapeXml(formurl)}">
+		<h3>Enter IP</h3>	
+		<input type="text" name="ip"><br/>
+		<input type="submit" name="Search" value="Search"/><br/><br/>
+	</form>
+	<jsp:include page="fragments/footer.jsp"/>
+	</div>
+</body>
+</html>
\ No newline at end of file
diff --git a/webapp/view/queryIPStatistics.jsp b/webapp/view/queryIPStatistics.jsp
new file mode 100644
index 0000000..6ef58e3
--- /dev/null
+++ b/webapp/view/queryIPStatistics.jsp
@@ -0,0 +1,24 @@
+<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
+<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+
+<%@ taglib prefix="spring" uri="http://www.springframework.org/tags"%>
+<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
+<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
+<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
+
+<html>
+<jsp:include page="fragments/header.jsp" />
+<body>
+	<div class="container">
+	<jsp:include page="fragments/mainmenu.jsp" />
+
+	<spring:url value="/ip/stat/querycounter" var="formurl"/>
+	<form method="get" action="${fn:escapeXml(formurl)}">
+		<h3>Enter minimum number of jobs executed from a given IP</h3>
+		<input type="text" name="JobCounter" value = 3> 
+		<input type="submit" name="Search" value="Search"/>
+	</form>
+	<jsp:include page="fragments/footer.jsp"/>
+	</div>
+</body>
+</html>
diff --git a/webapp/view/reportIP.jsp b/webapp/view/reportIP.jsp
new file mode 100644
index 0000000..b2dc4fd
--- /dev/null
+++ b/webapp/view/reportIP.jsp
@@ -0,0 +1,54 @@
+<%@ page language="java" contentType="text/html; charset=UTF-8"
+	pageEncoding="UTF-8"%>
+<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+
+<%@ taglib uri="http://www.springframework.org/tags" prefix="spring"%>
+<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
+<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn"%>
+<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
+<%@page import="java.util.ArrayList"%>
+
+<html>
+<jsp:include page="fragments/header.jsp" />
+<body>
+	<div class="container">
+		<jsp:include page="fragments/mainmenu.jsp" />
+		<spring:url value="/sequence/querysequence" var="searchquery" />
+		<spring:url value="/joblog/query" var="jobquery" />
+
+		<p style="font-weight:bold;">Job statistics for ${res.ip}</p>
+		<!-- <h3>Time execution: ${timeExecution} ms</h3>-->
+
+		<c:choose>
+			<c:when test="${results == null}">
+				<p>No jobs found...</p>
+			</c:when>
+			<c:otherwise>
+				<p>${njobs} jobs found:</p>
+				<div class="table-responsive">
+					<table class="table table-striped table-hover table-bordered">
+						<thead>
+							<tr>
+								<th style="text-align: centre">ID</th>
+								<th style="text-align: left">IP</th>
+								<th style="text-align: left">Sequence</th>
+							</tr>
+						</thead>
+						<tbody>
+							<c:forEach items="${results}" var="res" varStatus="status">
+								<tr>
+									<td><a href="{jobquery}?IdJob=${res.id}">${res.id}</a></td>
+									<td style="text-align: left; border-buttom: dotted; font-family: monospace">${res.ip}</td>
+									<td style="text-align: left; border-buttom: dotted; font-family: monospace">${res.prot}</td>
+								</tr>
+							</c:forEach>
+						</tbody>
+					</table>
+				</div>
+			</c:otherwise>
+		</c:choose>
+
+		<jsp:include page="fragments/footer.jsp" />
+	</div>
+</body>
+</html>
\ No newline at end of file
diff --git a/webapp/view/reportIPstatistics.jsp b/webapp/view/reportIPstatistics.jsp
new file mode 100644
index 0000000..ec4138b
--- /dev/null
+++ b/webapp/view/reportIPstatistics.jsp
@@ -0,0 +1,49 @@
+<%@ page language="java" contentType="text/html; charset=UTF-8"
+	pageEncoding="UTF-8"%>
+<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+
+<%@ taglib uri="http://www.springframework.org/tags" prefix="spring"%>
+<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
+<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn"%>
+<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
+<%@page import="java.util.ArrayList"%>
+
+<html>
+<jsp:include page="fragments/header.jsp" />
+<body>
+	<div class="container">
+		<jsp:include page="fragments/mainmenu.jsp" />
+		<spring:url value="/ip/query" var="ipquery" />
+
+		<!-- <h3>Time execution: ${timeExecution} ms</h3>-->
+		<c:choose>
+		<c:when test="${njobs == 0}">
+			<p>No IP with at least ${counter} jobs found</p>
+		</c:when>
+		<c:otherwise>
+			<p>${njobs} IP with at least ${counter} jobs found</p>
+			<div class="table-responsive">
+			<table class="table table-striped table-hover table-bordered">
+			<thead>
+				<tr>
+					<th style="text-align: centre">Number of jobs</th>
+					<th style="text-align: left">IP</th>
+				</tr>
+			</thead>
+			<tbody>
+				<c:forEach items="${results}" var="res">
+					<tr>
+						<td>${res.totalId}</td>
+						<td><a title="Click to view other jobs" href="${ipquery}?ip=${res.ip}&Search=Search">${res.ip}</a></td>
+					</tr>
+				</c:forEach>
+			</tbody>
+			</table>
+			</div>
+		</c:otherwise>
+		</c:choose>
+
+		<jsp:include page="fragments/footer.jsp" />
+	</div>
+</body>
+</html>
\ No newline at end of file
-- 
1.7.10.2