package com.ibm.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ibm.common.DBConnection;
import com.ibm.dto.TopicDTO;
import com.ibm.vo.BoardVO;
import com.ibm.vo.TopicVO;

public class TopicDAO {
   private static TopicDAO topicdao=null;
private PreparedStatement ps;
private ResultSet rs;
   
   public TopicDAO()
   {
	   
   }
   
   public static TopicDAO getInstance()
   {
	   if(topicdao== null)
	   {
		   topicdao=new TopicDAO();
		   return topicdao;
	   }else
	   {
		   return topicdao;
	   }
   }
   
   public boolean delTopic(Connection con,int topicid)
   {
	   boolean flag=false;
	   String sql="delete from topic where topicid=?";
	   try {
		ps=con.prepareStatement(sql);
		ps.setInt(1, topicid);
		
		ps.executeUpdate();
		flag=true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return flag;
   }
   
   /**
    * 更新主題信息
    * @param con
    * @param topicdto
    * @return
    */
   public boolean updateTopic(Connection con,TopicDTO topicdto)
   {
	   boolean flag=false;
	   String sql="update topic set title=?,content=?,modifytime=? where topicid=?";
	   try {
		ps=con.prepareStatement(sql);
		ps.setString(1, topicdto.getTitle());
		ps.setString(2, topicdto.getContent());
		ps.setTimestamp(3, topicdto.getModifytime());
		ps.setInt(4, topicdto.getTopicid());
		ps.executeUpdate();
		
		flag=true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return flag;
   }
   /**
    * 插入主題
    * @param con
    * @param topicdto
    * @return
    */
   public boolean insertTopic(Connection con,TopicDTO topicdto)
   {
	   boolean flag=false;
	   String sql="insert into topic(title,content,publishtime,modifytime,uid,boardid) values(?,?,?,?,?,?)";
	   try {
		ps=con.prepareStatement(sql);
		
		ps.setString(1,topicdto.getTitle());
		ps.setString(2, topicdto.getContent());
		ps.setTimestamp(3,topicdto.getPublishtime());
		ps.setTimestamp(4,topicdto.getModifytime());
		ps.setInt(5,topicdto.getUid());
		ps.setInt(6,topicdto.getBoardid());
		
		ps.executeUpdate();
		flag=true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		System.out.println("TopicDAO.insertTopic");
	    System.out.println("插入主題失敗");
	}
	
	return flag;
   }
   /**
    * 根據主題id返回該id下所有的信息
    * @param con
    * @param topicid
    * @return
    */
   public TopicVO queryByTopicid(Connection con,int topicid)
   {
	   String sql="select * from topic where topicid = ?";
	   TopicVO topicvo=new TopicVO();
	   try {
		ps=con.prepareStatement(sql);
		ps.setInt(1, topicid);
		rs=ps.executeQuery();
		
		while(rs.next())
		{
			topicvo.setTopicid(rs.getInt("topicid"));
			topicvo.setTitle(rs.getString("title"));
			topicvo.setContent(rs.getString("content"));
			topicvo.setPublishtime(rs.getTimestamp("publishtime"));
			topicvo.setModifytime(rs.getTimestamp("modifytime"));
			topicvo.setUid(rs.getInt("uid"));
			topicvo.setBoardid(rs.getInt("boardid"));
			
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	 return topicvo;
   }
   /**
    * 通過子板塊的boardid返回該版塊下的所有主題列表
    * @param con
    * @param boardid
    * @return
    */
   public List queryByBoardid(Connection con,int boardid)
   {
	   String sql= "select * from topic where boardid = ?";
	   List list=new ArrayList();
	   try {
		ps=con.prepareStatement(sql);
		ps.setInt(1, boardid);
		
		rs=ps.executeQuery();
		
		while(rs.next())
		{
			TopicVO topicvo=new TopicVO();
			topicvo.setTopicid(rs.getInt("topicid"));
			topicvo.setTitle(rs.getString("title"));
			topicvo.setContent(rs.getString("content"));
			topicvo.setPublishtime(rs.getTimestamp("publishtime"));
			topicvo.setModifytime(rs.getTimestamp("modifytime"));
			topicvo.setUid(rs.getInt("uid"));
			topicvo.setBoardid(rs.getInt("boardid"));
			
			list.add(topicvo);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		System.out.println("TopicDAO.queryByBoardid");
	}
	return list;
   }
   
   /**
    * 返回按時間排序的第一個主題信息
    * @param con
    * @param boardid
    * @return
    */
   public TopicVO queryTopByBoardid(Connection con,int boardid)
   {
	   String sql="select * from topic where boardid=? and modifytime=(select max(modifytime) from topic where boardid=?)";
	   TopicVO topicvo =new TopicVO();
	   try {
		ps=con.prepareStatement(sql);
		ps.setInt(1, boardid);
		ps.setInt(2, boardid);
		
		rs=ps.executeQuery();
		while(rs.next())
		{
			topicvo.setTopicid(rs.getInt("topicid"));
			topicvo.setTitle(rs.getString("title"));
			topicvo.setContent(rs.getString("content"));
			topicvo.setPublishtime(rs.getTimestamp("publishtime"));
			topicvo.setModifytime(rs.getTimestamp("modifytime"));
			topicvo.setUid(rs.getInt("uid"));
			topicvo.setBoardid(rs.getInt("boardid"));
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		System.out.println("TopicDAO.queryTopByBoardid");
	}
	return topicvo;
   }
   
   /**
    * 根據子板塊的boardid返回該板塊下的主題數
    * @param con
    * @param boardid
    * @return
    */
   public int queryTopicNumByBoardid(Connection con,int boardid)
   {
	   int topicNum=0;
//	   String sql="select count(*) as count_topicNum from topic where boardid=?";
	   String sql="select * from topic where boardid=?";
	   try {
		ps=con.prepareStatement(sql);
		ps.setInt(1,boardid);
		rs=ps.executeQuery();
//		while(rs.next())
//		{
//			BigDecimal topicNum=(BigDecimal)rs.getObject("count_topicNum"); 
//		}
		while(rs.next())
		{
			++topicNum;
		}
		//rs.next();
//		topicNum=rs.getInt(1);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		System.out.println("TopicDAO.queryTopicNumByBoardid");
	}
	
	return topicNum;
	   
   }
   
   
}