package com.mocha.report.article.dao.impl;

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

import com.mocha.report.article.dao.ArticleDao;
import com.mocha.report.entites.Article;
import com.mocha.report.util.JdbcUtil;

public class ArticleDaoImpl implements ArticleDao{
	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	
	@Override
	public List<Article> findByUsercode(String usercode ,int page, int perPage) {
		String sql = "select *from article where usercode=? limit ?,? ";
		List<Article> articles = new ArrayList<Article>();
		try {
			conn = JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1,usercode);
			ps.setInt(2, (page - 1) * perPage);
			ps.setInt(3, 10);
			rs = ps.executeQuery();
			while (rs.next()) {
				Article article = new Article(rs.getInt("aid"), rs.getString("aname"), 
						rs.getString("apath"),rs.getString("publishdate"),rs.getString("publishreader"),
						rs.getString("uploaddate"),rs.getString("summary"),rs.getString("usercode"));
				articles.add(article);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JdbcUtil.close(rs, ps, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return articles;
	}

	@Override
	public int getMaxPage(int perPage, String usercode) throws SQLException {
		String sql = "select count(*) from article where usercode=?";
		int maxPage = 0;
		int records;

		try {
			conn = JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, usercode);
			rs = ps.executeQuery();
			rs.next();
			records = rs.getInt(1);
			maxPage = records % perPage == 0 ? records / perPage : records
					/ perPage + 1;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return maxPage;
	}

	@Override
	public Article findByAid(int aid) {
		String sql = "select *from article where aid=? ";
		Article article = null;
		try {
			conn = JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, aid);
			rs = ps.executeQuery();
			while (rs.next()) {
				article = new Article(rs.getInt("aid"), rs.getString("aname"), 
						rs.getString("apath"),rs.getString("publishdate"),rs.getString("publishreader"),
						rs.getString("uploaddate"),rs.getString("summary"),rs.getString("usercode"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JdbcUtil.close(rs, ps, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return article;
	}

	@Override
	public void delete(int aid) {
		String sql = "delete from article where aid=? ";
		try {
			conn = JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, aid);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JdbcUtil.close(rs, ps, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public void save(Article article) {
		String sql = "insert into article(aid,aname,apath,publishdate,publishreader,uploaddate,summary,usercode) values(null,?,?,?,?,?,?,?)";
		try {
			conn = JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, article.getAname());
			ps.setString(2, article.getApath());
			ps.setString(3, article.getPublishdate());
			ps.setString(4, article.getPublishreader());
			ps.setString(5, article.getUploaddate());
			ps.setString(6, article.getSummary());
			ps.setString(7, article.getUsercode());
			
			System.out.println(article.getAname());
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JdbcUtil.close(rs, ps, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

	@Override
	public void update(Article article) {
		String sql = "update article set aname=?,apath=?,publishdate=?,publishreader=?,uploaddate=?,summary=?,usercode=? where aid=? ";
		try {
			conn = JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setString(1, article.getAname());
			ps.setString(2, article.getApath());
			ps.setString(3, article.getPublishdate());
			ps.setString(4, article.getPublishreader());
			ps.setString(5, article.getUploaddate());
			ps.setString(6, article.getSummary());
			ps.setString(7, article.getUsercode());
			ps.setInt(8, article.getAid());

			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JdbcUtil.close(rs, ps, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}


	@Override
	public List<Article> findByCondition(Article article, int page, int perPage) {
		List<Article> articles = new ArrayList<Article>();
		StringBuilder sql = new StringBuilder();
		sql.append("select * from article");
		sql.append(" where 1=1");

		Map<StringBuilder, List<Object>> map = this.sqlCondition(article);
		StringBuilder key = map.keySet().iterator().next();

		sql.append(key);

		sql.append(" limit ?,?");
		try {
			conn = JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());

			List<Object> conditionValues = map.get(key);

			int i = 0;
			for (i = 1; i <= conditionValues.size(); i++) {
				ps.setObject(i, conditionValues.get(i - 1));
			}
			ps.setInt(i++, perPage * (page - 1));
			ps.setInt(i++, perPage);
			rs = ps.executeQuery();
			while (rs.next()) {
				Article article1 = new Article(rs.getInt("aid"), rs.getString("aname"), 
						rs.getString("apath"),rs.getString("publishdate"),rs.getString("publishreader"),
						rs.getString("uploaddate"),rs.getString("summary"),rs.getString("usercode"));
				articles.add(article1);
				

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JdbcUtil.close(rs, ps, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return articles;
	}

	private Map<StringBuilder, List<Object>> sqlCondition(Article article) {
		Map<StringBuilder, List<Object>> maps = new HashMap<StringBuilder, List<Object>>();
		StringBuilder conditionSql = new StringBuilder();
		List<Object> conditionValues = new ArrayList<Object>();

		// 判断教师编号
		if (article.getUsercode() != null && article.getUsercode().length() > 0) {
			conditionSql.append("  and usercode=?");
			conditionValues.add(article.getUsercode());
		}
		maps.put(conditionSql, conditionValues);
		return maps;
	}

	@Override
	public int getMaxPage(int perPage, Article article) throws SQLException {
		String sql = "select count(*) from article ";
		int maxPage = 0;
		int records;

		try {
			conn = JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			records = rs.getInt(1);
			maxPage = records % perPage == 0 ? records / perPage : records
					/ perPage + 1;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return maxPage;
	}



	@Override
	public List<Map<String, String>> findTotal() {
		List<Map<String, String>> listss = new ArrayList<Map<String,String>>();
		
		String sql = "select count(aid) sum ,usercode from article group by usercode " ;
		try {
			conn = JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				Map<String,String> map = new HashMap<String,String>() ;
				
			Integer sum = rs.getInt(1);
			String sum1 = String.valueOf(sum);
			String usercode = rs.getString(2) ;
			map.put("name", usercode) ;
			map.put("value",sum1) ;
			listss.add(map);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		return listss;
	}

}