package com.jxyd.dao;

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

import com.jxyd.sql.Mysql;
import com.jxyd.sql.SimpleSqlControl;
import com.jxyd.vo.NoticeBean;

public class NoticeDAO {
	private String notice_table = "notice";

	/**
	 * 添加公告
	 * 
	 * @param nb
	 * @return
	 */
	public boolean AddNotice(NoticeBean nb) {
		boolean flag = false;
		String sql = "insert into "+notice_table+" (Title,Content,addDate,yesno,type) values(?,?,?,?,?)";
		
		Mysql ms = new Mysql();
		try {
			ms.prepareStatement(sql);
			ms.setString(1, nb.getTitle());
			ms.setString(2, nb.getContent());
			ms.setString(3, nb.getAddDate());
			ms.setInt(4, nb.getYesno());
			ms.setInt(5, nb.getType());
			ms.execute();
			ms.commit();
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println(sql);
		return flag;
	}

	/**
	 * 查詢全部公告，并分頁顯示
	 * 
	 * @param start
	 * @param pageSize
	 * @return
	 */
	public ArrayList<?> SelectNotice(Integer start, Integer pageSize,Integer type) {
		String sql = "select * from " + notice_table;
		if(type!=null){
				sql=sql+" where type="+type;
		}
		sql = sql + " order by id desc limit " + start + "," + pageSize;
		SimpleSqlControl scon=new SimpleSqlControl();
		ArrayList<?> list=null;
		try {
			list=scon.sqlSearch(sql, "com.jxyd.vo.NoticeBean");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 滾動顯示最近發布的五條公告
	 * @param show
	 * @return
	 */
	public ArrayList<?> SelectNotice(Integer yn) {
		String sql = "select * from " + notice_table+" where yesno="+yn;
		sql = sql + " order by id desc limit 5";
		SimpleSqlControl scon=new SimpleSqlControl();
		ArrayList<?> list=null;
		try {
			list=scon.sqlSearch(sql, "com.jxyd.vo.NoticeBean");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	 /**
	  * 
	  * @param userCode
	  * @return
	  */
		public Integer count() {
			Mysql ms = new Mysql();
			String sql = "select count(*) from " + notice_table ;
			ResultSet rs;
			int count = 0;
			try {
				rs = ms.executeQuery(sql);
				if (rs.next()) {
					count = rs.getInt(1);
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				ms.freeResource();
			}
			return count;
		}
		
		
		//更新公告的顯示狀態
		public boolean UpdateNotice(Integer yn,Integer id){
			boolean flag=false;
			String sql="update "+notice_table+" set yesno="+yn+" where id="+id;
			Mysql ms=new Mysql();
			try {
				ms.prepareStatement(sql);
				ms.execute();
				ms.commit();
				flag=true;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return flag;
		}

}