package com.dotj.web.struts;

import java.util.Properties;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletContext;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import com.dotj.io.DataSet;
import com.dotj.grid.interfaces.IGridCommandEventArgs;
import com.dotj.form.interfaces.IFormValidator;
import com.dotj.form.FormValidatorTag;
import com.dotj.test.MyGridSublistListener;
import com.dotj.test.Message;


public final class ProcessSublistAction extends Action {
    public static final String  REQ_MESSAGE_LIST = "msgList";

	/**
	 * The <code>Log</code> instance for this application.
	 */
	protected static Log log =
	        LogFactory.getLog(ProcessSublistAction.class);


	// --------------------------------------------------------- Public Methods


	/**
	 * Process the specified HTTP request, and create the corresponding HTTP
	 * response (or forward to another web component that will create it).
	 * Return an <code>ActionForward</code> instance describing where and how
	 * control should be forwarded, or <code>null</code> if the response has
	 * already been completed.
	 *
	 * @param mapping  The ActionMapping used to select this instance
	 * @param form     The optional ActionForm bean for this request (if any)
	 * @param request  The HTTP request we are processing
	 * @param response The HTTP response we are creating
	 * @throws Exception if the application business logic throws
	 *                   an exception
	 */
	public ActionForward execute(ActionMapping mapping,
	                             ActionForm form,
	                             HttpServletRequest request,
	                             HttpServletResponse response)
	        throws Exception {

		// Set a transactional control token to prevent double posting
		saveToken(request);

		Long start2 = new Long(System.currentTimeMillis());
		request.setAttribute("startTimer", start2);

		// Array constants to match column fields and sort order.
		String[] dataFields = {"", "-RowNumber-", "-Icon-", "MessageId", "Forum", "ThreadID", "Name", "Subject", "Email"};
		String[] sortTypes = {"", "ASC", "DESC"};

		// Default sorted column is the 4th column (Forum).
		int sortColumn = 4;
		String sortField = dataFields[sortColumn];

		// Default sort order is Ascending.
		int sortType = DataSet.SORT_ASCENDING;
		String sortOrder = sortTypes[sortType];

		// Default initialization. These wil be overloaded by what the user performed as an action.
        int rowCount = 0;
        int pageCount = 1;
        int displayPage = 1;
		int pageSize = 0;

		// Get a handle to the dotJ form validator object.
		IFormValidator formValidator = FormValidatorTag.getDotJFormValidator(request);

		// Only process POST events.
		if (formValidator.IsPost()) {
			// Check for a Grid command event.
			if (formValidator.getCommandEvent() instanceof IGridCommandEventArgs) {
				IGridCommandEventArgs cmdEvt = (IGridCommandEventArgs) formValidator.getCommandEvent();

				// Store some important paging and sorting information.
                displayPage = cmdEvt.getDisplayPage();
				pageSize = cmdEvt.getPageSize();
				sortColumn = cmdEvt.getSortColumnNumber();
                if (sortColumn > 0) {
    				sortField = dataFields[sortColumn];
                }
				sortType = cmdEvt.getSortType();
				sortOrder = sortTypes[sortType];
			}
		}

		// Build the SQL.  Get all the messages from the database.
        String sql = "SELECT A.MessageID, B.Name, A.Subject, A.ThreadID, B.Email, D.Name AS Forum" +
                   " FROM jiveMessage A, jiveUser B," +
                   "      jiveThread C, jiveForum D" +
                   " WHERE A.userID = B.userID" +
                   " AND A.threadID = C.threadID" +
                   " AND C.forumID = D.forumID" +
                   " ORDER BY " + sortField + " " + sortOrder;

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Message message = null;
        ArrayList messageList = new ArrayList();

		// Cache the message list in the request. We'll bind the <dotj:grid> to this in the JSP.
        request.setAttribute(REQ_MESSAGE_LIST, messageList);

		// Create the sublist grid listener. This is required because we are doing grid sublist processing.
		MyGridSublistListener listener = new MyGridSublistListener();

        try {
	        // Issue the SQL call. Use a scrollable Resultset in order to skip rows.
            conn = getConnection(servlet.getServletContext());
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(sql);

            if (rs != null) {
                int startRow, endRow;
	            // Calculate the starting row.
                startRow = (displayPage - 1) * pageSize + 1;

                if (rs.last()) rowCount = rs.getRow();

	            // Calculate the end row.
	            if (pageSize <= 0) endRow = rowCount;
                else endRow = startRow + pageSize - 1;

                if (endRow > rowCount) endRow = rowCount;

	            // Using a scrollable ResultSet, go to the starting row.  Then iterate over the Resultset until
	            // reaching the end row.  This avoids processing every single row in the ResultSet.
                for (int i = startRow; i <= endRow; i++) {
                    if (rs.absolute(i)) {
                        message = new Message();
                        message.setId(rs.getLong("MessageID"));
                        message.setForumName(rs.getString("Forum"));
                        message.setUserName(rs.getString("Name"));
                        message.setSubject(rs.getString("Subject"));
                        message.setEmail(rs.getString("Email"));
                        message.setThreadId(rs.getLong("ThreadID"));
                        messageList.add(message);
                    }
                }

	            // Calculate the page count.
                pageCount = rowCount / MyGridSublistListener.PAGE_SIZE;
            }

	        // Now get the thread total using SQL. This is necessary since we're not processing all rows and the only
	        // way to sum the column is via SQL.  Normally, dotJ handles this automatically, but it can't since we're
	        // doing sublist processing and dotJ won't see every row.
	        String sql2 = "SELECT SUM(ThreadID)" +
	                   " FROM jiveMessage";
	        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(sql2);

            if (rs != null && rs.next()) {
                int threadTotal = rs.getInt(1);
	            listener.setThreadTotal(threadTotal);
            }
        } finally {
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException sqle) {
                ; // Ok to eat since we're cleaning up.
            }
        }

		// Transfer this sort state to the grid listener so the grid in the JSP will know about everything.
		// Also, cache the listener in the request, so the JSP can use it.
		listener.setPost(formValidator.IsPost());
		listener.setSortColumn(sortColumn);
		listener.setSortType(sortType);
        listener.setRowCount(rowCount);
        listener.setPageCount(pageCount);
		request.setAttribute("listener", listener);

		// Forward control to the edit user registration page
		return (mapping.findForward("success"));

	}

    private static Connection getConnection(ServletContext application) throws SQLException {
        Connection conn = null;
        Properties prop = new java.util.Properties();

        try {
            prop.load(new java.io.FileInputStream(application.getRealPath("/WEB-INF/database.properties")));

            String driver = prop.getProperty("databaseDriver");
            String uri = prop.getProperty("databaseURI");
            String userid = prop.getProperty("databaseUserid");
            String password = prop.getProperty("databasePassword");
            Class.forName(driver);
            conn = java.sql.DriverManager.getConnection(uri, userid, password);
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        }

        return conn;
    }

}