<%@page import="java.sql.*,java.util.*,java.text.*"%>
<% response.setHeader("Cache-Control","no-cache"); %>
<%!
private boolean isSchemaCreated(Connection connection)
    throws SQLException
{
    // sprawd nazwy wszystkich tabel w bazie danych
    // porównaj z list¹ znanych tabel
    DatabaseMetaData meta = connection.getMetaData();
    ResultSet rs =
        meta.getTables(null,null,null,new String[] { "TABLE" });
    int found = 0;
    while(rs.next()) {
        String tableName = rs.getString("TABLE_NAME");
        if(tableName.equalsIgnoreCase("resource")
            || tableName.equalsIgnoreCase("booking"))
            found++;
    }
    rs.close();
    return 2 == found;
}

protected void doUpdates(Connection connection,
                         String[] statements)
    throws SQLException
{
    Statement stmt = connection.createStatement();
    SQLException e = null;
    try {
        for(int i = 0;i < statements.length;i++)
           try {
               stmt.executeUpdate(statements[i]);
           }
           catch(SQLException x)
               { e = e != null ? e : x; }
        if(null != e) {
            throw e;
        }
    }
    finally {
        stmt.close();
    }
}

private boolean isEmpty(String st)
{
   if(null != st)
       return st.trim().length() == 0;
   else
       return true;
}

private void doInsertResource(Connection connection,
                              String name,
                              String description)
    throws SQLException
{
    PreparedStatement stmt =
        connection.prepareStatement(
           "insert into resource (name, description) values (?,?)");
    try {
        stmt.setString(1,name);
        stmt.setString(2,description);
        stmt.executeUpdate();
    }
    finally {
        stmt.close();
    }
}
%>
<%
DateFormat dateFormat =
    DateFormat.getDateInstance(DateFormat.MEDIUM,
                               Locale.US);
Class.forName("org.hsqldb.jdbcDriver");
Connection connection =
    DriverManager.getConnection("jdbc:hsqldb:db/resourceful",
                                "sa",
                                null);
try
{
    connection.setAutoCommit(true);
%>
<html>
<head>
<title>Intranet centrum biznesowego</title>
</head>
<body>
<h1>Intranet centrum biznesowego</h1>
<%
    String todo = request.getParameter("todo"),
           message = null;
    if(todo != null) {
        if(todo.equals("drop"))
            doUpdates(connection,new String[] {
               "drop index resource.idx_name",
               "drop index booking.idx_start",
               "drop index booking.idx_end",
               "drop table resource",
               "drop table booking",
            });
       else if(todo.equals("create"))
           doUpdates(connection,new String[]
           {
              "set ignorecase true",
              "create table resource (id integer not null " +
                 "identity primary key, name varchar(50) not " +
                 "null, description varchar(100) not null, " +
                 "unique (name))",
              "create table booking (id integer not null " +
                 "identity primary key, resourceid integer not " +
                 "null, email varchar(80), start datetime not " +
                 "null, end datetime not null, foreign key " +
                 "(resourceid) references resource (id))",
              "create index idx_name on resource (name)",
              "create index idx_start on booking (start)",
              "create index idx_end on booking (end)",
           });
       else if(todo.equals("insert"))
           doInsertResource(connection,
                            request.getParameter("name"),
                            request.getParameter("description"));
    }
%>
<%
    if(isSchemaCreated(connection)) {
%>
<%-- tabela z list¹ danych --%>
<table>
<tr><td><b>Nazwa</b></td>
    <td><b>Opis</b></td></tr>
<%
        Statement stmt = connection.createStatement();
        PreparedStatement pstmt =
            connection.prepareStatement("select id, start, end, " +
                      "email from booking where resourceid = ?");
        try {
           ResultSet resources =
               stmt.executeQuery("select id, name, " +
                                 "description from resource");
           while(resources.next()) {
%>
<tr><td bgcolor="#bbbbbb"
    valign="top"><%= resources.getString(2) %></td>
    <td valign="top" bgcolor="#bbbbbb"
       colspan="2"><%= resources.getString(3) %></td></tr>
<%
               pstmt.setInt(1,resources.getInt(1));
               try {
                   ResultSet bookings = pstmt.executeQuery();
                   while(bookings.next()) {
%>
<tr><td colspan="2">booked by
    <%= bookings.getString(4) %>
    (<%= dateFormat.format(bookings.getTimestamp(2)) %> -
    <%= dateFormat.format(bookings.getTimestamp(3)) %>)</td></tr>
<%
                  }
               }
               finally {
                  pstmt.close();
               }
%>
<%
           }
       }
       finally {
           stmt.close();
       }
%>
</table>
<%-- formularz do wprowadzania nowych pozycji --%>
<form action="<%= request.getRequestURI() %>" method="post">
<table>
<tr><td>Nazwa:</td><td>
<input type="text" name="name"></td></tr>
<tr><td>Opis:</td><td>
<input type="text" name="description"></td></tr>
</table>
<input type="hidden" name="todo" value="insert">
<input type="submit" value="Wstaw nowe">
</form>
<%
    }
%>
<%-- niewielki formularz do wstawiania i usuwania tabel w bazie danych --%>
<form action="<%= request.getRequestURI() %>" method="post">
<%
    if(isSchemaCreated(connection)) {
%>
<input type="hidden" name="todo" value="drop">
<input type="submit" value="Usuń tabele"><br>
<%
    }
    else {
%>
<input type="hidden" name="todo" value="create">
<input type="submit" value="Wstaw tabele"><br>
<%
    }
%>
</form>
<%
}
finally {
   connection.close();
}
%>
</body>
</html>
