<%@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 sqlStatement = "SELECT COUNT(*) AS count_docs, SUM(docsize) as sum_docs" +
                      "  FROM documents";

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

String sqlStatement4 = "SELECT MIN(EXTRACT(YEAR from published)) AS min_year, MAX(EXTRACT(YEAR from published)) AS max_year" +
                       "  FROM documents";

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(sqlStatement4);
rs.next();
int startYear = rs.getInt("max_year");
int endYear = rs.getInt("min_year");
rs.close();
%>
<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>
<%

String sqlStatement5 = "SELECT EXTRACT(YEAR from published) AS currYear, EXTRACT(MONTH from published) AS currMonth, COUNT(*) AS count_docs, SUM(docsize) as sum_docs" +
                       "  FROM documents" +
                      "  GROUP BY 1, 2" +
                      "  ORDER BY 1 DESC, 2 ASC";

rs = stmt.executeQuery(sqlStatement5);
rs.next();
%>
<table border=1>
  <tr bgcolor=c0c0c0>
    <TD>&nbsp;</TD>
    <TD>&nbsp;1</TD>
    <TD>&nbsp;2</TD>
    <TD>&nbsp;3</TD>
    <TD>&nbsp;4</TD>
    <TD>&nbsp;5</TD>
    <TD>&nbsp;6</TD>
    <TD>&nbsp;7</TD>
    <TD>&nbsp;8</TD>
    <TD>&nbsp;9</TD>
    <TD>10</TD>
    <TD>11</TD>
    <TD>12</TD>
  </tr>
<%
int currYear = 0;
for (int month = 1; month <= 12; month++) {

        // new year ==> new row
        if (rs.getInt("currYear") != currYear) {
            currYear = rs.getInt("currYear");
%>
           <TR>
             <TD><%=rs.getInt("currYear")%></TD>
<%             
        }        
      
        // iterate through all months
        if (month == rs.getInt("currMonth")) {
%>
            <TD ALIGN="right"><%=rs.getInt("count_docs")%><BR><%=rs.getInt("sum_docs")%></TD> 
<%
            rs.next(); 
        } else {
%>
            <TD>&nbsp;</TD>
<%
        } 

        // end of row
        if (rs.getInt("currYear") != currYear) {
%>
           </TR>
<%
        }        
}
%>
</table>

<%
rs.close();
stmt.close();
conn.close();
%>
</body>
</html>