package com.mole.struts.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.mole.struts.bean.MessageBean;

public class MessageCenterDAO {
	private Connection conn;

	public MessageCenterDAO() {
		try {
			Context ctx = new InitialContext();
			if (ctx == null)
				throw new Exception("Failed to initial context!");
			DataSource ds = (DataSource) ctx
					.lookup("java:comp/env/jdbc/crmdata");
			conn = ds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public int getNewMessageCount(int ID, String ReceiverType, int state) {
		int count = 0;
		String sql = "";
		if (state == 0)
			sql = "SELECT COUNT(*) FROM [MessageCenter] WHERE [ReceiverID]=? AND [ReceiverType]=?";
		else
			sql = "SELECT COUNT(*) FROM [MessageCenter] WHERE [ReceiverID]=? AND [ReceiverType]=? AND [State]=0";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, ID);
			ps.setString(2, ReceiverType);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	public ArrayList<MessageBean> getMessage(int ID, String Type, int state,
			int currentPage, int pageSize) throws Exception {
		ResultSet rs = null;
		CallableStatement stmt = null;
		ArrayList<MessageBean> al = new ArrayList<MessageBean>();
		try {
			conn.setAutoCommit(true);
			stmt = conn.prepareCall("{call sp_GetMessage(?,?,?,?,?)}");
			stmt.setInt(1, ID);
			stmt.setString(2, Type);
			stmt.setInt(3, state);
			stmt.setInt(4, currentPage);
			stmt.setInt(5, pageSize);
			rs = stmt.executeQuery();
			while (rs.next()) {
				MessageBean bean = new MessageBean();
				bean.setID(rs.getInt(1));
				bean.setSenderID(rs.getInt(2));
				bean.setReceiverID(rs.getInt(3));
				bean.setSenderName(rs.getString(4));
				bean.setReceiverName(rs.getString(5));
				bean.setSenderType(rs.getString(6));
				bean.setReceiverType(rs.getString(7));
				bean.setTitle(rs.getString(8));
				bean.setMessage(rs.getString(9));
				bean.setReleaseDate(rs.getString(10).substring(0, 18));
				bean.setState(state);
				al.add(bean);
			}
			return al;
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public MessageBean getOneMessage(int ID) throws Exception {
		ResultSet rs = null;
		MessageBean bean = new MessageBean();
		CallableStatement stmt = null;
		try {
			conn.setAutoCommit(true);
			stmt = conn.prepareCall("{call sp_GetOneMessage(?)}");
			stmt.setInt(1, ID);
			rs = stmt.executeQuery();
			if (rs.next()) {
				bean.setID(ID);
				bean.setSenderID(rs.getInt(1));
				bean.setSenderName(rs.getString(2));
				bean.setSenderType(rs.getString(3));
				bean.setReleaseDate(rs.getString(4).substring(0, 18));
				bean.setTitle(rs.getString(5));
				bean.setMessage(rs.getString(6));
			}
			return bean;
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void insertMessage(String SenderID, String ReceiverID,
			String SenderType, String ReceiverType, String Title, String Message)
			throws Exception {
		PreparedStatement ps = null;
		String sql = "INSERT INTO [MessageCenter]([SenderID],[ReceiverID],[SenderType],[ReceiverType],[Title],[Message]) VALUES(?,?,?,?,?,?)";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, SenderID);
			ps.setObject(2, ReceiverID);
			ps.setObject(3, SenderType);
			ps.setObject(4, ReceiverType);
			ps.setObject(5, Title);
			ps.setObject(6, Message);
			ps.execute();
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	public void deleteMessage(String ID) throws Exception {
		PreparedStatement ps = null;
		String sql = "DELETE FROM [MessageCenter] WHERE ID=" + ID;
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			ps.execute();
		} finally {
			if (ps != null)
				ps.close();
		}
	}

}