package com.ibm.dao;

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.dto.ReplyDTO;
import com.ibm.vo.ReplyVO;

public class ReplyDAO {
    private PreparedStatement ps;
	private ResultSet rs;

	/**
	 * 通過replyid刪除回復
	 * @param con
	 * @param replyid
	 * @return
	 */
	public boolean delByReplyid(Connection con,int replyid)
	{
		boolean flag=false;
		String sql="delect from reply where replyid=?";
		try {
			ps=con.prepareStatement(sql);
			
			ps.executeUpdate();
			
			flag=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}
	
	/**
	 * 根據topicid返回該主題下的所有回復
	 * @param con
	 * @param topicid
	 * @return
	 */
	public List queryByTopicid(Connection con,int topicid)
	{
		List queryList=new ArrayList();
		String sql="select * from reply where topicid=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, topicid);
			rs=ps.executeQuery();
			
			while(rs.next())
			{
				ReplyVO replyvo=new ReplyVO();
				replyvo.setTopicid(rs.getInt("topicid"));
				replyvo.setTitle(rs.getString("title"));
				replyvo.setContent(rs.getString("content"));
				replyvo.setPublishtime(rs.getTimestamp("publishtime"));
				replyvo.setModifytime(rs.getTimestamp("modifytime"));
				replyvo.setUid(rs.getInt("uid"));
				replyvo.setTopicid(rs.getInt("topicid"));
				
				queryList.add(replyvo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("ReplyDAO.queryByTopicid");
		}
		return queryList;
	}
    
	/**
	 * 根據topicid返回該回復下得數量
	 * @param con
	 * @param topicid
	 * @return
	 */
	public int ReplyNumByTopicid(Connection con,int topicid)
	{
		int num=0;
		
		String sql="select * from reply where topicid=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, topicid);
			rs=ps.executeQuery();
			
			while(rs.next())
			{
				++num;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("ReplyDAO.queryByTopicid");
		}
		return num;
	}
    /**
	 * 向回復表中插入數據
	 * @param con
	 * @param replydto
	 * @return
	 */
    public boolean insertReply(Connection con,ReplyDTO replydto)
    {
    	boolean flag=false;
		String sql="insert into reply(title,content,publishtime,modifytime,uid,topicid) values(?,?,?,?,?,?)";
    	try {
			ps=con.prepareStatement(sql);
			
			ps.setString(1, replydto.getTitle());
			ps.setString(2, replydto.getContent());
			ps.setTimestamp(3, replydto.getPublishtime());
			ps.setTimestamp(4, replydto.getModifytime());
			ps.setInt(5, replydto.getUid());
			ps.setInt(6, replydto.getTopicid());
			
			ps.executeUpdate();
			flag=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("ReplyDAO.insertReply");
		}
		return flag;
    }
}