package zeta.handler.statistic;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import zeta.ZetaServlet;
import zeta.util.CachedQueries;
import zeta.util.DatabaseUtils;
import zeta.util.QueryWithSum;
import zeta.util.Table;
public class TeamMembersHandler extends AbstractHandler {
public TeamMembersHandler(ZetaServlet servlet) {
super(servlet, 600000, 0, 21600000); }
Table getTopTeamTable() {
return teamTable;
}
public String createPage(Connection con) throws SQLException, ServletException {
StringBuffer buffer = new StringBuffer(100*1024);
Map teamNames = CachedQueries.getTeamNames(con);
Map userNames = CachedQueries.getUserNames(con);
buffer.append("<tr><td colspan=\"6\">");
buffer.append("<br><center>");
HtmlTableGenerator generator = new HtmlTableGenerator(servlet, servlet.getHandlerAddress(TopProducersHandler.class), "user");
Table table = new Table(6);
table.setColumnName(0, "team");
table.setType(0, Types.VARCHAR);
table.setAlignment(0, Table.LEFT);
table.setColumnName(1, "user");
table.setType(1, Types.VARCHAR);
table.setAlignment(1, Table.LEFT);
table.setColumnName(2, "joined");
table.setType(2, Types.TIMESTAMP);
table.setAlignment(2, Table.CENTER);
table.setFormat(2, new SimpleDateFormat("MM/dd/yyyy"));
table.setColumnName(3, "work units");
table.setType(3, Types.INTEGER);
table.setAlignment(3, Table.RIGHT);
table.setColumnName(4, "computers used");
table.setType(4, Types.INTEGER);
table.setAlignment(4, Table.RIGHT);
table.setColumnName(5, "zeros");
table.setType(5, Types.INTEGER);
table.setAlignment(5, Table.RIGHT);
Table teamTable = new Table(7);
teamTable.setColumnName(0, "place");
teamTable.setType(0, Types.VARCHAR);
teamTable.setAlignment(0, teamTable.LEFT);
teamTable.setColumnName(1, "team");
teamTable.setType(1, Types.VARCHAR);
teamTable.setAlignment(1, teamTable.LEFT);
teamTable.setColumnName(2, "age (days)");
teamTable.setType(2, Types.INTEGER);
teamTable.setAlignment(2, teamTable.RIGHT);
teamTable.setColumnName(3, "work units");
teamTable.setType(3, Types.INTEGER);
teamTable.setAlignment(3, teamTable.RIGHT);
teamTable.setColumnName(4, "members");
teamTable.setType(4, Types.INTEGER);
teamTable.setAlignment(4, teamTable.RIGHT);
teamTable.setColumnName(5, "computers used");
teamTable.setType(5, Types.INTEGER);
teamTable.setAlignment(5, teamTable.RIGHT);
teamTable.setColumnName(6, "zeros");
teamTable.setType(6, Types.INTEGER);
teamTable.setAlignment(6, teamTable.RIGHT);
Statement stmt = null;
try {
String previousTeamname = "";
int age = 0;
int workUnits = 0;
int members = 0;
int computersUsed = 0;
long zeros = 0;
stmt = con.createStatement();
long maxDays = CachedQueries.getMaxDays(stmt);
ResultSet rs = stmt.executeQuery("SELECT LOWER(RTRIM(b.team_name)) AS \"team\","
+ " LOWER(b.name) AS \"user\","
+ " MIN(b.join_in_team) AS \"joined\""
+ " FROM zeta.computation a,zeta.user b,zeta.result r"
+ " WHERE r.task_id=a.task_id"
+ " AND r.work_unit_id=a.work_unit_id"
+ " AND a.user_id=b.id"
+ " AND a.server_id=b.server_id"
+ " AND NOT b.team_name IS NULL"
+ " AND LENGTH(RTRIM(b.team_name))>0"
+ " GROUP BY LOWER(RTRIM(b.team_name)),LOWER(b.name) ORDER BY LOWER(RTRIM(b.team_name)),MIN(b.join_in_team)");
while (rs.next()) {
String teamname = rs.getString(1);
String name = rs.getString(2);
Timestamp t = rs.getTimestamp(3);
Object[] o = CachedQueries.getUserData(name, teamname);
if (!teamname.equals(previousTeamname)) {
insertTeam(teamTable, (String)teamNames.get(previousTeamname), age, workUnits, members, computersUsed, zeros);
previousTeamname = teamname;
age = 0;
workUnits = 0;
members = 0;
computersUsed = 0;
zeros = 0;
}
int row = table.getRowCount();
table.addRow();
table.setValue(row, 0, teamNames.get(teamname));
table.setValue(row, 1, userNames.get(name));
table.setValue(row, 2, t);
table.setValue(row, 3, o[1]);
table.setValue(row, 4, o[0]);
table.setValue(row, 5, o[2]);
if (age == 0) {
age = (int)(maxDays-DatabaseUtils.convertTimeMillisToDays(t.getTime()));
}
workUnits += ((Integer)o[1]).intValue();
++members;
computersUsed += ((Integer)o[0]).intValue();
zeros += ((Long)o[2]).longValue();
}
rs.close();
insertTeam(teamTable, (String)teamNames.get(previousTeamname), age, workUnits, members, computersUsed, zeros);
for (int place = 1, l = teamTable.getRowCount(); place <= l; ++place) {
teamTable.setValue(place-1, 0, String.valueOf(place)+'.');
}
} finally {
DatabaseUtils.close(stmt);
}
this.teamTable = teamTable;
buffer.append(generator.generate(table));
teamTable = new Table(2);
teamTable.setAlignment(0, Table.LEFT);
teamTable.setAlignment(1, Table.LEFT);
Table userDataTable = CachedQueries.getUserData(con);
if (userDataTable != null) {
int l = table.getRowCount()-2;
String lastTeamName = "";
for (int i = 0; i < l; ++i) {
String teamName = (String)table.getValue(i, 0);
if (!lastTeamName.equals(teamName)) {
lastTeamName = teamName;
String teamhomepage = null;
String teamintroduction = null;
while (i < l && (teamhomepage == null || teamintroduction == null)) {
teamName = (String)table.getValue(i, 0);
if (!lastTeamName.equals(teamName)) {
--i;
break;
}
String user = (String)table.getValue(i, 1);
int userDataIdx = userDataTable.indexOfRow(user, 0);
if (userDataIdx >= 0) {
Object[] userData = userDataTable.getRow(userDataIdx);
Properties properties = new Properties();
if (userData[7] == null) {
userData[7] = "";
}
try {
properties.load(new ByteArrayInputStream(((String)userData[7]).getBytes()));
} catch (IOException ioe) {
}
if (teamhomepage == null) {
String s = properties.getProperty("homepage", "");
if (s.length() > 0 && properties.getProperty("teamhomepage", "").equals("true")) {
teamhomepage = s;
}
}
if (teamintroduction == null) {
String s = properties.getProperty("teamintroduction", "");
if (s.length() > 0) {
teamintroduction = s;
}
}
}
++i;
}
if (teamhomepage != null || teamintroduction != null) {
Properties properties = new Properties();
if (teamhomepage != null) {
properties.setProperty("teamhomepage", "true");
properties.setProperty("homepage", teamhomepage);
}
if (teamintroduction != null) {
properties.setProperty("teamintroduction", teamintroduction);
}
try {
ByteArrayOutputStream out = new ByteArrayOutputStream(1024);
properties.store(out, null);
out.close();
teamTable.addRow();
teamTable.setValue(teamTable.getRowCount()-1, 0, lastTeamName.toLowerCase());
teamTable.setValue(teamTable.getRowCount()-1, 1, out.toString());
} catch (IOException ioe) {
}
}
}
}
}
generator = new HtmlTableGenerator(servlet);
buffer.append(generator.generate(teamTable));
buffer.append("</center></td></tr>");
return buffer.toString();
}
protected String createPage(HttpServletRequest req, Connection con) throws SQLException, ServletException {
String team = req.getParameter("team");
if (team == null) {
StringBuffer buffer = new StringBuffer(1024);
inputTeam(null, buffer);
return buffer.toString();
}
InnerPageBuffer innerPageBuffer = null;
try {
innerPageBuffer = getInnerPageBuffer();
if (innerPageBuffer == null) {
return "<tr><td colspan=\"8\"><p>This statistic is not available at moment! Please try again later.</td></tr>";
}
String search = "><td>" + team + "</td><td><a href=\"";
String[] teamTable = innerPageBuffer.getLines(search, true);
if (teamTable == null) {
StringBuffer buffer = new StringBuffer(1024);
buffer.append("<tr><td colspan=\"6\"><p><b>Error:</b> Team '");
buffer.append(team);
buffer.append("' is unknown.</td></tr>");
inputTeam(null, buffer);
return buffer.toString();
} else {
StringBuffer buffer = new StringBuffer(10*1024);
buffer.delete(0, buffer.length());
inputTeam(team, buffer);
buffer.append("<tr><td colspan=\"6\" height=\"30pt\" class=\"second-head-gray\"><center>Members of top team '");
buffer.append(team);
buffer.append("'</center></td></tr><tr><td colspan=\"6\"><br><center><p>");
innerPageBuffer.skip("</table>", false);
String teamhomepage = null;
String teamintroduction = null;
String prop = innerPageBuffer.between("><td>" + team.toLowerCase() + "</td><td", "</td></tr>", false);
if (prop != null) {
int idx = prop.indexOf('>');
if (idx >= 0) {
Properties properties = new Properties();
try {
properties.load(new ByteArrayInputStream(prop.substring(idx+1).getBytes()));
} catch (IOException ioe) {
}
if (teamhomepage == null) {
String s = properties.getProperty("homepage", "");
if (s.length() > 0 && properties.getProperty("teamhomepage", "").equals("true")) {
teamhomepage = s;
}
}
if (teamintroduction == null) {
String s = properties.getProperty("teamintroduction", "");
if (s.length() > 0) {
teamintroduction = s;
}
}
}
}
if (teamhomepage != null || teamintroduction != null) {
buffer.append("<center><table cellspacing=\"0\" width=\"90%\" border=\"1\"><colgroup><col width=\"30%\"><col width=\"70%\"></colgroup>");
if (teamhomepage != null) {
buffer.append("<tr><td>Homepage </td><td><a href=\"");
buffer.append(teamhomepage);
buffer.append("\">");
buffer.append(teamhomepage);
buffer.append("</a></td></tr>");
}
if (teamintroduction != null) {
buffer.append("<tr bgcolor=\"#eeeeee\"><td colspan=\"2\">");
buffer.append(teamintroduction);
buffer.append("</td></tr>");
}
buffer.append("</table></center><p> <br>");
}
buffer.append("\n<style type=\"text/css\">\n.c { text-align:center }\n.r { text-align:right }\n</style>\n<center><table border=\"1\" cellspacing=\"0\" width=\"90%\"><colgroup><col width=\"8%\"/><col width=\"24%\"/><col width=\"16%\"/><col width=\"16%\"/><col width=\"16%\"/><col width=\"16%\"/></colgroup>");
buffer.append("<tr bgcolor=\"#dddddd\"><th>pos</th><th>user</th><th>joined</th><th>work units</th><th>computers used</th><th>zeros</th></tr>\n");
for (int number = 0; number < teamTable.length; ++number) {
if ((number&1) == 0) {
buffer.append("<tr><td>");
} else {
buffer.append("<tr bgcolor=\"#eeeeee\"><td>");
}
buffer.append(number+1);
buffer.append(".</td>");
int idx = teamTable[number].indexOf("</td><td");
buffer.append((idx < 0)? teamTable[number] : teamTable[number].substring(idx+5));
}
buffer.append("</table></center></td></tr>");
return buffer.toString();
}
} finally {
if (innerPageBuffer != null) {
innerPageBuffer.close();
}
}
}
private void inputTeam(String team, StringBuffer buffer) {
buffer.append("<tr><td colspan=\"6\"><form action=\"");
buffer.append(servlet.getHandlerAddress(getClass()));
buffer.append("\"><p>Please type your registered team name (exactly as it appears in the list) into the entry field.<p>Team name <input name=\"team\" type=\"text\" size=\"60\" maxlength=\"100");
if (team != null) {
buffer.append("\" value=\"");
buffer.append(team);
}
buffer.append("\"> <input type=\"submit\" value=\"View\"></form>");
buffer.append("</td></tr>");
}
private void insertTeam(Table teamTable, String teamname, int age, int workUnits, int members, int computersUsed, long zeros) {
if (teamname != null && teamname.length() > 0 && zeros > 0) {
int l = teamTable.getRowCount();
int i = 0;
while (i < l) {
if (zeros > ((Long)teamTable.getValue(i, 6)).longValue()) {
break;
}
++i;
}
teamTable.addRow(i);
teamTable.setValue(i, 1, teamname);
teamTable.setValue(i, 2, new Integer(age));
teamTable.setValue(i, 3, new Integer(workUnits));
teamTable.setValue(i, 4, new Integer(members));
teamTable.setValue(i, 5, new Integer(computersUsed));
teamTable.setValue(i, 6, new Long(zeros));
}
}
private Table teamTable = null;
}