package com.yxq.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.yxq.toolsbean.DB;
import com.yxq.valuebean.ArticleBean;

public class ArticleDao {
	private DB connection = null;
	private ArticleBean articleBean = null;

	public ArticleDao() {
		connection = new DB();
	}

	/**
	 * @功能 實(shí)現(xiàn)對(duì)文章進(jìn)行增、刪、改的操作
	 * @參數(shù) oper為一個(gè)String類型變量，用來(lái)表示要進(jìn)行的操作；single為ArticleBean類對(duì)象，用來(lái)存儲(chǔ)某個(gè)文章的信息
	 * @返回值 boolean型值
	 */
	public boolean operationArticle(String oper, ArticleBean single) {		
		/* 生成SQL語(yǔ)句 */
		String sql = null;
		if (oper.equals("add"))					//發(fā)表新文章
			sql = "insert into tb_article values ('" + single.getTypeId() + "','"+ single.getTitle() + "','" + single.getContent() + "','"+ single.getSdTime()+ "','"+single.getCreate()+"','" + single.getInfo()+"',"+single.getCount() + ")";
		if (oper.equals("modify"))				//修改文章
			sql = "update tb_article set article_typeID=" + single.getTypeId()+ ",article_title='" + single.getTitle() + "',article_content='"+ single.getContent() +"',article_create='"+single.getCreate()+ "',article_info='"+single.getInfo()+"' where id=" + single.getId();
		if (oper.equals("delete"))				//刪除文章
			sql = "delete from tb_article where id=" + single.getId();
		if (oper.equals("readTimes"))			//累加閱讀次數(shù)
			sql = "update tb_article set article_count=article_count+1 where id="+ single.getId();
		
		/* 執(zhí)行SQL語(yǔ)句 */	
		boolean flag =connection.executeUpdate(sql);
		return flag;
	}

	/** 
	 * @功能 查詢指定類別的文章
	 * @參數(shù) typeId表示文章類別ID值
	 * @返回值 List集合
	 */
	public List queryArticle(int typeId,String type) {
		List articlelist = new ArrayList();
		String sql = "";
		if (typeId <=0)					//不按類別查詢，查詢出前3條記錄
			sql = "select top 3 * from tb_article order by article_sdTime DESC";
		else							//按類別查詢
			if(type==null||type.equals("")||!type.equals("all"))
				sql = "select top 5 * from tb_article where article_typeID=" + typeId+ " order by article_sdTime DESC";
			else
				sql = "select * from tb_article where article_typeID=" + typeId+ " order by article_sdTime DESC";
		ResultSet rs = connection.executeQuery(sql);
		if(rs!=null){
			try {
				while (rs.next()) {
					/* 獲取文章信息 */
					articleBean = new ArticleBean();
					articleBean.setId(rs.getInt(1));
					articleBean.setTypeId(rs.getInt(2));
					articleBean.setTitle(rs.getString(3));
					articleBean.setContent(rs.getString(4));
					articleBean.setSdTime(rs.getString(5));
					articleBean.setCreate(rs.getString(6));
					articleBean.setInfo(rs.getString(7));					
					articleBean.setCount(rs.getInt(8));
					
					/* 查詢tb_review數(shù)據(jù)表統(tǒng)計(jì)當(dāng)前文章的評(píng)論數(shù) */
					sql="select count(id) from tb_review where review_articleId="+articleBean.getId();
					ResultSet rsr=connection.executeQuery(sql);
					if(rsr!=null){
						rsr.next();
						articleBean.setReview(rsr.getInt(1));
						rsr.close();						
					}					
					articlelist.add(articleBean);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally{
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				connection.closed();
			}			
		}
		return articlelist;
	}

	/**
	 * @功能 查詢指定文章的詳細(xì)內(nèi)容
	 * @參數(shù) id為文章ID值
	 * @返回值 ArticleBean類對(duì)象，封裝了文章信息
	 */
	public ArticleBean queryArticleSingle(int id) {
		String sql = "select * from tb_article where id='" + id + "'";
		ResultSet rs = connection.executeQuery(sql);
		try {
			while (rs.next()) {
				articleBean = new ArticleBean();
				articleBean.setId(rs.getInt(1));
				articleBean.setTypeId(rs.getInt(2));
				articleBean.setTitle(rs.getString(3));
				articleBean.setContent(rs.getString(4));
				articleBean.setSdTime(rs.getString(5));
				articleBean.setCreate(rs.getString(6));
				articleBean.setInfo(rs.getString(7));					
				articleBean.setCount(rs.getInt(8));
				
				/* 查詢tb_review數(shù)據(jù)表統(tǒng)計(jì)當(dāng)前文章的評(píng)論數(shù) */
				sql="select count(id) from tb_review where review_articleId="+articleBean.getId();
				ResultSet rsr=connection.executeQuery(sql);
				if(rsr!=null){
					rsr.next();
					articleBean.setReview(rsr.getInt(1));
					rsr.close();						
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return articleBean;
	}
}