package com.wxpn.tutorial.servlet;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import com.wxpn.tutorial.db.ConnectionPool;
import com.wxpn.tutorial.db.DB;

/**
 * 描述: 描述留言信息管理類
 * 
 * @Copyright (c) 2005-2008 Wang Xining
 * @author 王夕寧
 * @version 1.0
 */

public class BBSMsgMgr {

	public int compose(BBSMsg msg) {
		// 創(chuàng)建數(shù)據(jù)庫連接對象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			int maxid = 1;
			String sql = "select max(item_id) as maxid from bbsitems";
			// 創(chuàng)建數(shù)據(jù)記錄集對象：
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				maxid = rs.getInt(1) + 1;
			}
			// sql語句：
			sql = "insert into bbsitems(item_id,item_title,item_content,author,"
					+ "compose_date,is_origional,fathers_id,replyed_times,browsed_times,"
					+ "latest_replication_id,modify_date,face) values('"
					+ maxid
					+ "','"
					+ msg.getItem_title()
					+ "','"
					+ msg.getItem_content()
					+ "','"
					+ msg.getAuthor()
					+ "','"
					+ msg.getStrCompose_date()
					+ "','1','"
					+ maxid
					+ "','0','0','" + maxid + "','','" + msg.getFace() + "')";
//			System.out.println(sql);
			sql = new String(sql.getBytes("ISO8859-1"), "UTF-8");
//			System.out.println(sql);
			// 執(zhí)行sql語句：
			int i = stmt.executeUpdate(sql);
			return i;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 關(guān)閉連接，釋放數(shù)據(jù)庫資源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public int modify(BBSMsg msg) {
		// 創(chuàng)建數(shù)據(jù)庫連接對象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 創(chuàng)建數(shù)據(jù)記錄集對象：
			stmt = conn.createStatement();

			// sql語句：
			String sql = "update bbsitems set item_title='"
					+ msg.getItem_title() + "',item_content='"
					+ msg.getItem_content() + "',modify_date='"
					+ msg.getStrModify_date() + "',face='"+msg.getFace()+"' where item_id='"
					+ msg.getItem_id() + "'";
			// 執(zhí)行sql語句：
			System.out.println(sql);
			sql = new String(sql.getBytes("ISO8859-1"), "UTF-8");
			System.out.println(sql);
			int i = stmt.executeUpdate(sql);
			return i;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 關(guān)閉連接，釋放數(shù)據(jù)庫資源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public int read(int id) {
		// 創(chuàng)建數(shù)據(jù)庫連接對象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 創(chuàng)建數(shù)據(jù)記錄集對象：
			stmt = conn.createStatement();

			// sql語句：
			String sql = "update bbsitems set BROWSED_TIMES=BROWSED_TIMES+1 "
					+ "where item_id='" + id + "'";
			// System.out.println(sql);
			// 執(zhí)行sql語句：
			int i = stmt.executeUpdate(sql);
			return i;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 關(guān)閉連接，釋放數(shù)據(jù)庫資源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public int reply(BBSMsg msg, int fatherId) {
		// 創(chuàng)建數(shù)據(jù)庫連接對象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			int maxid = 1;
			String sql = "select max(item_id) as maxid from bbsitems";
			// 創(chuàng)建數(shù)據(jù)記錄集對象：
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				maxid = rs.getInt(1) + 1;
			}
			// sql語句：
			sql = "insert into bbsitems(item_id,item_title,item_content,author,"
					+ "compose_date,is_origional,fathers_id,replyed_times,browsed_times,"
					+ "latest_replication_id,face) values('"
					+ maxid
					+ "','"
					+ msg.getItem_title()
					+ "','"
					+ msg.getItem_content()
					+ "','"
					+ msg.getAuthor()
					+ "','"
					+ msg.getStrCompose_date()
					+ "','0','"
					+ fatherId
					+ "','0','0','" + maxid + "','" + msg.getFace() + "')";
			sql = new String(sql.getBytes("ISO8859-1"), "UTF-8");
			stmt.addBatch(sql);
			sql = "update bbsitems set replyed_times=replyed_times+1,latest_replication_id='"
					+ maxid + "' where item_id='" + fatherId + "'";
			stmt.addBatch(sql);
			// 執(zhí)行sql語句：
			stmt.executeBatch();
			return 1;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 關(guān)閉連接，釋放數(shù)據(jù)庫資源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public int delete(int id) {
		// 創(chuàng)建數(shù)據(jù)庫連接對象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 創(chuàng)建數(shù)據(jù)記錄集對象：
			stmt = conn.createStatement();

			// sql語句：
			String sql = "delete from bbsitems where item_id = '" + id + "'";
			stmt.addBatch(sql);
			Collection c = getFamily(id);
			if (c != null) {
				Iterator iterator = c.iterator();
				while (iterator.hasNext()) {
					sql = "delete from bbsitems where item_id = '"
							+ ((Integer) iterator.next()).intValue() + "'";
//					System.out.println("sql:" + sql);
					stmt.addBatch(sql);
				}
			}
			// 執(zhí)行sql語句：
			stmt.executeBatch();
			return 1;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 關(guān)閉連接，釋放數(shù)據(jù)庫資源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Collection getFamily(int id) {
		// 創(chuàng)建數(shù)據(jù)庫連接對象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;

		try {
			// 創(chuàng)建數(shù)據(jù)記錄集對象：
			stmt = conn.createStatement();

			// sql語句：
			String sql = "select item_id from bbsitems where fathers_id='" + id
					+ "' and item_id!='" + id + "'";
			// System.out.println(sql);
			// 執(zhí)行sql語句，返回一個記錄集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();

			while (rs.next()) {
				if (c == null) {
					c = new ArrayList();
				}
				int item_id = rs.getInt("item_id");
				c.add(new Integer(item_id));
				c.addAll(getFamily(item_id));
			}
			return c;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 關(guān)閉連接，釋放數(shù)據(jù)庫資源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Collection getFamilyLevel(int id, int level) {
		// 創(chuàng)建數(shù)據(jù)庫連接對象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 創(chuàng)建數(shù)據(jù)記錄集對象：
			stmt = conn.createStatement();

			// sql語句：
			String sql = "select item_id from bbsitems where fathers_id='" + id
					+ "' and item_id!='" + id + "'";
			// System.out.println(sql);
			// 執(zhí)行sql語句，返回一個記錄集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			level++;
			while (rs.next()) {
				if (c == null) {
					c = new ArrayList();
				}
				int item_id = rs.getInt("item_id");
				c
						.add(new Integer[] { new Integer(item_id),
								new Integer(level) });
				c.addAll(getFamilyLevel(item_id, level));
			}
			return c;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 關(guān)閉連接，釋放數(shù)據(jù)庫資源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public BBSMsg getMessage(int id) {
		// 創(chuàng)建數(shù)據(jù)庫連接對象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;