/*
 * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/MessageDAOImplJDBC.java,v 1.37 2006/04/14 17:05:26 minhnn Exp $
 * $Author: minhnn $
 * $Revision: 1.37 $
 * $Date: 2006/04/14 17:05:26 $
 *
 * ====================================================================
 *
 * Copyright (C) 2002-2006 by MyVietnam.net
 *
 * All copyright notices regarding mvnForum MUST remain 
 * intact in the scripts and in the outputted HTML.
 * The "powered by" text/logo with a link back to
 * http://www.mvnForum.com and http://www.MyVietnam.net in 
 * the footer of the pages MUST remain visible when the pages
 * are viewed on the internet or intranet.
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 *
 * Support can be obtained from support forums at:
 * http://www.mvnForum.com/mvnforum/index
 *
 * Correspondence and Marketing Questions can be sent to:
 * info at MyVietnam net
 *
 * @author: Minh Nguyen  
 * @author: Mai  Nguyen  
 */
package com.mvnforum.db.jdbc;

import java.io.StringReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;

import com.mvnforum.db.*;
import net.myvietnam.mvncore.db.DBUtils;
import net.myvietnam.mvncore.exception.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class MessageDAOImplJDBC implements MessageDAO {

    private static Log log = LogFactory.getLog(MessageDAOImplJDBC.class);

    //this variable will support caching if cache for this class is needed
    private static boolean m_dirty = true;

    public MessageDAOImplJDBC() {
    }

    protected static boolean isDirty() {
        return m_dirty;
    }

    protected static void setDirty(boolean dirty) {
        m_dirty = dirty;
    }

    private int findMessageID(int memberID, int messageSenderID, Timestamp messageCreationDate)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT MessageID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE MemberID = ? AND MessageSenderID = ? AND MessageCreationDate = ? ");
        sql.append(" ORDER BY MessageID DESC");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, memberID);
            statement.setInt(2, messageSenderID);
            statement.setTimestamp(3, messageCreationDate);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the Message in table Message.");
            }

            return resultSet.getInt("MessageID");
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.findMessageID.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void findByPrimaryKey(int messageID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT MessageID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE MessageID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, messageID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the primary key (" + messageID + ") in table 'Message'.");
            }
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.findByPrimaryKey.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList,
     *                   MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType,
     *                   MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon,
     *                   MessageAttachCount, MessageIP, MessageCreationDate
     * Excluded columns: MessageID
     */
    public int create(String folderName, int memberID, int messageSenderID,
                        String messageSenderName, String messageToList, String messageCcList,
                        String messageBccList, String messageTopic, String messageBody,
                        int messageType, int messageOption, int messageStatus,
                        int messageReadStatus, int messageNotify, String messageIcon,
                        int messageAttachCount, String messageIP, Timestamp messageCreationDate)
        throws CreateException, DatabaseException, ForeignKeyNotFoundException {

        int messageID = 0;

        try {
            // @todo: modify the parameter list as needed
            // You may have to regenerate this method if the needed columns dont have attribute 'include'
            DAOFactory.getMessageFolderDAO().findByPrimaryKey(folderName, memberID);
        } catch(ObjectNotFoundException e) {
            throw new ForeignKeyNotFoundException("Foreign key refers to table 'MessageFolder' does not exist. Cannot create new Message.");
        }

        try {
            // @todo: modify the parameter list as needed
            // You may have to regenerate this method if the needed columns dont have attribute 'include'
            DAOFactory.getMemberDAO().findByPrimaryKey(memberID);
        } catch(ObjectNotFoundException e) {
            throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Message.");
        }

        try {
            // @todo: modify the parameter list as needed
            // You may have to regenerate this method if the needed columns dont have attribute 'include'
            DAOFactory.getMemberDAO().findByPrimaryKey2(messageSenderID, messageSenderName);
        } catch(ObjectNotFoundException e) {
            throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Message.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("INSERT INTO " + TABLE_NAME + " (FolderName, MemberID, MessageSenderID, MessageSenderName, MessageToList, MessageCcList, MessageBccList, MessageTopic, MessageBody, MessageType, MessageOption, MessageStatus, MessageReadStatus, MessageNotify, MessageIcon, MessageAttachCount, MessageIP, MessageCreationDate)");
        sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            statement.setString(1, folderName);
            statement.setInt(2, memberID);
            statement.setInt(3, messageSenderID);
            statement.setString(4, messageSenderName);
            statement.setString(5, messageToList);
            statement.setString(6, messageCcList);
            statement.setString(7, messageBccList);
            statement.setString(8, messageTopic);
            if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
                statement.setCharacterStream(9, new StringReader(messageBody), messageBody.length());
            } else {
                statement.setString(9, messageBody);
            }
            statement.setInt(10, messageType);
            statement.setInt(11, messageOption);
            statement.setInt(12, messageStatus);
            statement.setInt(13, messageReadStatus);
            statement.setInt(14, messageNotify);
            statement.setString(15, messageIcon);
            statement.setInt(16, messageAttachCount);
            statement.setString(17, messageIP);
            statement.setTimestamp(18, messageCreationDate);

            if (statement.executeUpdate() != 1) {
                throw new CreateException("Error adding a row into table 'Message'.");
            }
            m_dirty = true;
            // Search returned MessageID here
            try {
                messageID = findMessageID(memberID, messageSenderID, messageCreationDate);
            } catch (ObjectNotFoundException onfe) {
                // Hack the Oracle 9i problem.
                Timestamp roundTimestamp = new Timestamp((messageCreationDate.getTime()/1000)*1000);
                try {
                    messageID = findMessageID(memberID, messageSenderID, roundTimestamp);
                } catch (ObjectNotFoundException e) {
                   throw new CreateException("Cannot find the Message in table Message.");
                }
            }
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in MessageDAOImplJDBC.create.");
        } finally {