package dao;

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

import conn.DBConnection;
import domain.Article;
import domain.Articles;

public class ArticleDao {
	public List<Article> getArticleTitle1() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Article> alist1 = new ArrayList<Article>();
		String sql1 = "SELECT a.title articletitle,ak.title articlekindtitle FROM webpk.articles a inner join webpk.articlekind ak WHERE a.kindid=ak.kindid and a.address=1 ORDER BY a.post_time desc limit 0,3";
        
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			Article a = null;
			while(rs.next()){
				a = new Article();
				a.setArticletitle(rs.getString("articletitle"));
				a.setArticlekindtitle(rs.getString("articlekindtitle"));
				alist1.add(a);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return alist1;
	}

	public List<Article> getArticleTitle2() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Article> alist2 = new ArrayList<Article>();
		String sql2 = "SELECT a.title articletitle,ak.title articlekindtitle FROM webpk.articles a inner join webpk.articlekind ak WHERE a.kindid=ak.kindid and a.address=2 ORDER BY a.post_time desc limit 0,2";
        
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql2);
			Article a = null;
			while(rs.next()){
				a = new Article();
				a.setArticletitle(rs.getString("articletitle"));
				a.setArticlekindtitle(rs.getString("articlekindtitle"));
				alist2.add(a);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return alist2;
	}

	public List<Article> getArticleTitle3() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Article> alist3 = new ArrayList<Article>();
		String sql3 = "SELECT a.title articletitle,ak.title articlekindtitle FROM webpk.articles a inner join webpk.articlekind ak WHERE a.kindid=ak.kindid and a.address=3 ORDER BY a.post_time desc limit 0,4";
        
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql3);
			Article a = null;
			while(rs.next()){
				a = new Article();
				a.setArticletitle(rs.getString("articletitle"));
				a.setArticlekindtitle(rs.getString("articlekindtitle"));
				alist3.add(a);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return alist3;
	}

	public List<Article> getArticleTitle4() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Article> alist4 = new ArrayList<Article>();
		String sql4 = "SELECT a.title articletitle,ak.title articlekindtitle FROM webpk.articles a inner join webpk.articlekind ak WHERE a.kindid=ak.kindid and a.address=4 ORDER BY a.post_time desc limit 0,5";
        
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql4);
			Article a = null;
			while(rs.next()){
				a = new Article();
				a.setArticletitle(rs.getString("articletitle"));
				a.setArticlekindtitle(rs.getString("articlekindtitle"));
				alist4.add(a);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return alist4;
	}

	public List<Article> getArticleTitle5() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Article> alist5 = new ArrayList<Article>();
		String sql5 = "SELECT a.title articletitle,ak.title articlekindtitle FROM webpk.articles a inner join webpk.articlekind ak WHERE a.kindid=ak.kindid and a.address=5 ORDER BY a.post_time desc limit 0,4";
        
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql5);
			Article a = null;
			while(rs.next()){
				a = new Article();
				a.setArticletitle(rs.getString("articletitle"));
				a.setArticlekindtitle(rs.getString("articlekindtitle"));
				alist5.add(a);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return alist5;
	}
	public Articles getArticleContent(String articletitle){
		
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt= null;
		ResultSet rs = null;
		String sql = "SELECT * FROM webpk.articles a WHERE a.title=?";
		Articles a = null;
		
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, articletitle);
			rs = pstmt.executeQuery();
			while(rs.next()){
				a = new Articles();
				a.setContent(rs.getString("content"));
				a.setHits(rs.getInt("hits"));
				a.setPhoto(rs.getString("photo"));
				a.setPost_time(rs.getDate("post_time"));
				a.setTitle(rs.getString("title"));
				a.setKindid(rs.getInt("kindid"));
			
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return a;
	}
	/**
	 * 获得表中数据记录的个数
	 *  
	 * @return totalRecord 表中的总记录数
	 */
	public static int getRowNumber() {
		Connection conn = DBConnection.getConn();
		int totalRecord = 0;
		try {
			Statement stmt = conn.createStatement();
			String tsql = "SELECT count(*) FROM webpk.articles";
			ResultSet rs = stmt.executeQuery(tsql);
			rs.next();
			totalRecord = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					DBConnection.closeConn();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		
		return totalRecord;
	}

	/**
	 * 获得总的页数
	 * 
	 * @param pageSize
	 *            每页显示的条数
	 * @return 返回总页数
	 */
	public static int getTotalPage(int pageSize) {
		int totalPage = 1;
		int tmpPage = 0;
		int rowNum = getRowNumber();
		tmpPage = rowNum % pageSize;
		if (tmpPage == 0) {
			totalPage = rowNum / pageSize;
		} else {
			totalPage = (int)(Math.floor(rowNum / pageSize) + 1);
		}
		if (totalPage == 0) {
			totalPage = 1;
		}
		return totalPage;
	}
	public int addArticle(String title,int kindid,int address,String content,String photo){
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		
		
		int count =0;
		String sql = "INSERT INTO webpk.articles VALUES(null,?,?,?,now(),1,?,?)";
		
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, title);
			pstmt.setInt(2, kindid);
			pstmt.setString(3, content);
			pstmt.setString(4, photo);
			pstmt.setInt(5, address);
			
			count = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return count;
	}
	public int delArticle(int articleid){
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		
		
		int count =0;
		String sql = "DELETE FROM webpk.articles where articles.articlesid=?";
		
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, articleid);
						
			count = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return count;
	}
	/*
	 * 获得文章的类别
	 */
public String getArticleType(int articleid){
		
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt= null;
		ResultSet rs = null;
		String sql = "SELECT ak.title FROM webpk.articlekind ak WHERE ak.kindid=?";
		String a = null;
		
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, articleid);
			rs = pstmt.executeQuery();
			rs.next();
			a = rs.getString("title");
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return a;
	}
	/*
	 * 提高文章点击率
	 */
public void addAticleHits(String articletitle){
	 
	Connection conn = DBConnection.getConn();
	PreparedStatement pstmt = null;
	
	String sql = "UPDATE webpk.articles SET articles.hits = articles.hits + 1 WHERE articles.title=?";
	
	try{
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, articletitle);
		pstmt.executeUpdate();
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		DBConnection.closeConn();
	}
 }
}