/**
 *  Title  新闻管理系统
 *  @author: 王夕宁
 *  @version 1.0
 * 对news_master表的一些增，删，改，查询操作
 */
package com.wxpn.tutorial.control.news;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;

import com.wxpn.tutorial.bean.news.Master;
import com.wxpn.tutorial.db.DBConnect;

public class MasterMgr{
	/*
	 * 管理员发新闻次数加一
	 */
	public void addnewsnum(String username) {
		DBConnect dbc = null;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("UPDATE news_master SET total = total+1 WHERE username = ?");
			dbc.setString(1, new String(username.getBytes("ISO8859_1"),"UTF-8"));
			dbc.executeUpdate();
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/*
	 * 管理员登陆次数加一
	 */
	public void addloginnum(String username) {
		DBConnect dbc = null;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("UPDATE news_master SET loginnum = loginnum+1 WHERE username = ?");
			dbc.setString(1, new String(username.getBytes("ISO8859_1"),"UTF-8"));
				dbc.executeUpdate();
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/*
	 * 添加新的管理员
	 */
	public void add(Master master) {
		DBConnect dbc = null;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("INSERT INTO news_master ( username,userpwd,total,categoryid,loginnum,registertime) VALUES ( ?,?,?,?,?,? )");
			dbc.setString(1, new String(master.getUsername().getBytes("ISO8859_1"),"UTF-8"));
			dbc.setString(2, new String(master.getUserpwd().getBytes("ISO8859_1"),"UTF-8"));
			dbc.setInt(3, 0);
			dbc.setInt(4, master.getCategoryid());
			dbc.setInt(5, 1);
			dbc.setDate(6, new java.sql.Date(new java.util.Date().getTime()));
			dbc.executeUpdate();			
		} catch (Exception e) {
			System.out.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/*
	 * 删除管理员
	 */
	public void delete(int id) {
		DBConnect dbc = null;
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("delete from news_master WHERE id=?");
			dbc.setInt(1, id);
			dbc.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/*
	 * 修改管理员信息
	 */
	public void modify(Master master) {
		DBConnect dbc = null;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("UPDATE news_master SET username=?,userpwd=?,total=?,categoryid=?,loginnum=? WHERE id=?");
			dbc.setString(1, new String(master.getUsername().getBytes("ISO8859_1"),"UTF-8"));
			dbc.setString(2, new String(master.getUserpwd().getBytes("ISO8859_1"),"UTF-8"));
			dbc.setInt(3, master.getTotal());
			dbc.setInt(4, master.getCategoryid());
			dbc.setInt(5, master.getLoginnum());
			dbc.setInt(6, master.getId());
			dbc.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public boolean checkLogin(String username, String userpwd) {
		DBConnect dbc = null;
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("SELECT * FROM news_master WHERE username = ? and userpwd = ?");
			dbc.setString(1, new String(username.getBytes("ISO8859_1"),"UTF-8"));
			dbc.setString(2, new String(userpwd.getBytes("ISO8859_1"),"UTF-8"));
			rs = dbc.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return false;
	}

	/*
	 * 查询所有的管理员,检查是否重复
	 */
	public boolean isExist(String username) {
		DBConnect dbc = null;
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("SELECT * FROM news_master WHERE username = ?");
			dbc.setString(1, new String(username.getBytes("ISO8859_1"),"UTF-8"));
			rs = dbc.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return false;
	}

	/*
	 * 查询所有的管理员(按发新闻数排序)
	 */
	public Collection getAllByTotalDesc() {
		DBConnect dbc = null;
		Collection c = new ArrayList();
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			rs = dbc.executeQuery("SELECT * FROM news_master order by total desc");
			Master master = null;
			while (rs.next()) {
				master = new Master();
				master.setId(rs.getInt("id"));
				master.setUsername(rs.getString("username"));
				master.setUserpwd(rs.getString("userpwd"));
				master.setStrRegistertime(rs.getString("registertime"));
				master.setTotal(rs.getInt("total"));
				master.setCategoryid(rs.getInt("categoryid"));
				master.setLoginnum(rs.getInt("loginnum"));
				c.add(master);
				master = null;
			}
		} catch (Exception e) {
			System.err.println("error:" + e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return c;
	}

	/*
	 * 查询所有的管理员(按id排序)
	 */
	public Collection getAllByIdAsc() {
		DBConnect dbc = null;
		Collection c = new ArrayList();
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("SELECT * FROM news_master order by id asc");
			rs = dbc.executeQuery();
			Master master = null;
			while (rs.next()) {
				master = new Master();
				master.setId(rs.getInt("id"));
				master.setUsername(rs.getString("username"));
				master.setUserpwd(rs.getString("userpwd"));
				master.setStrRegistertime(rs.getString("registertime"));
				master.setTotal(rs.getInt("total"));
				master.setCategoryid(rs.getInt("categoryid"));
				master.setLoginnum(rs.getInt("loginnum"));
				c.add(master);
				master = null;
			}
		} catch (Exception e) {
			System.err.println("error:" + e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return c;
	}

	/*
	 * 计算管理员的总数
	 */
	public int getTotal() {
		DBConnect dbc = null;
		int masterCount = 0;
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("SELECT count(*) FROM news_master");
			rs = dbc.executeQuery();
			if (rs.next())
				masterCount = rs.getInt(1);
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return masterCount;
	}

	/*
	 * 根据ID得到管理员
	 */
	public Master getById(int id) {
		DBConnect dbc = null;
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("SELECT * FROM news_master WHERE id = ?");
			dbc.setInt(1, id);
			rs = dbc.executeQuery();
			if (rs.next()) {
				Master master = new Master();
				master.setId(rs.getInt("id"));
				master.setUsername(rs.getString("username"));
				master.setUserpwd(rs.getString("userpwd"));
				master.setStrRegistertime(rs.getString("registertime"));
				master.setTotal(rs.getInt("total"));
				master.setCategoryid(rs.getInt("categoryid"));
				master.setLoginnum(rs.getInt("loginnum"));
				return master;
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/*
	 * 根据用户名称得到管理员
	 */
	public Master getByUsername(String username) {
		DBConnect dbc = null;
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("SELECT * FROM news_master WHERE username = ?");
			dbc.setString(1, new String(username.getBytes("ISO8859_1"),"UTF-8"));
			rs = dbc.executeQuery();
			if (rs.next()) {
				Master master = new Master();
				master.setId(rs.getInt("id"));
				master.setUsername(rs.getString("username"));
				master.setUserpwd(rs.getString("userpwd"));
				master.setStrRegistertime(rs.getString("registertime"));
				master.setTotal(rs.getInt("total"));
				master.setCategoryid(rs.getInt("categoryid"));
				master.setLoginnum(rs.getInt("loginnum"));
				return master;
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}
}