<%@page contentType="text/html"%>

<%@page import="java.util.*"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Timestamp"%>
<%@page import="vkc.view.WebShare"%>
<%@page import="vkc.db.serializer.VKCSysParameterSerializer"%>

<%

String order = request.getParameter("order");
if (order == null)
    order = "" + 1 + "";

String sort = request.getParameter("sort");
if (sort == null)
    sort = "desc";

String sqlStatement = "SELECT COUNT(*) AS count_docs, SUM(docsize) as sum_docs" +
                      "  FROM documents";

String sqlStatement2 = "SELECT CAST(published AS DATE) as pub_date, COUNT(*) AS count_docs, SUM(docsize) as sum_docs" +
                      "  FROM documents " +
                      " GROUP BY 1 " +
                      " ORDER BY " + order + " " + sort;

String sqlStatement3 = "SELECT COUNT(*) AS count_cats" +
                      "  FROM categories";

Connection conn = WebShare.database.getConnection();

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sqlStatement);
rs.next();

long CountAllDocs = rs.getLong("count_docs");
long AmountAllDocs = rs.getLong("sum_docs");
rs.close();

rs = stmt.executeQuery(sqlStatement3);
rs.next();
long CountAllCats = rs.getLong("count_cats");
rs.close();

rs = stmt.executeQuery(sqlStatement2);
%>
<html>
<head>
<BODY>
<H1>Published Document Statistics</H1>
<p>
<table border=1>
  <tr>
     <td colspan=2>RootUrl: <%=VKCSysParameterSerializer.getRootURL()%></td>
  </tr>
  <tr>
     <td>Count of all created categories  : </td><td align=right><%=CountAllCats%></td>
  </tr>
  <tr>
     <td>Count of all published documents : </td><td align=right><%=CountAllDocs%></td>
  </tr>
  <tr>
     <td>Amount of all published documents: </td><td align=right><%=AmountAllDocs%> Bytes</td>
  </tr>
</table>
<P>
<table border=1>
  <tr bgcolor="c0c0c0">
    <td align=center>Date<BR><A HREF="<%=request.getContextPath()%>/common/ShowDocStatistics.jsp?order=1&sort=ASC">up</A> <A HREF="<%=request.getContextPath()%>/common/ShowDocStatistics.jsp?order=1&sort=desc">down</A></td>
    <td align=center>Count<br>published documents<BR><A HREF="<%=request.getContextPath()%>/common/ShowDocStatistics.jsp?order=2&sort=ASC">up</A> <A HREF="<%=request.getContextPath()%>/common/ShowDocStatistics.jsp?order=2&sort=DESC">down</A></td>
    <td align=center>Amount<br>published documents (in Byte)<BR><A HREF="<%=request.getContextPath()%>/common/ShowDocStatistics.jsp?order=3&sort=ASC">up</A> <A HREF="<%=request.getContextPath()%>/common/ShowDocStatistics.jsp?order=3&sort=DESC">down</A></td>
  </tr>
<%
  while (rs.next()) { %>
  <tr>
    <td><%=rs.getDate("pub_date")%></td>
    <td align=right><%=rs.getInt("count_docs")%></td>
    <td align=right><%=rs.getInt("sum_docs")%></td>
  </tr>
<%}%>
</table>
<%
rs.close();
stmt.close();
conn.close();
%>
</body>
</html>