package com.x3408.notice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Vector;

import com.x3408.database.CNProvider;
import com.x3408.office.Constants;

public class NoticeADO {
	public static Vector allNoticeQuery() {
		Connection conn = CNProvider.getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		NoticeInfo noticeInfo=null;
		Vector<NoticeInfo> noticeList=new Vector<NoticeInfo>();
		try {
			pstat = conn.prepareStatement("select * from Notice order by noticeID desc");
			rs = pstat.executeQuery();
			while(rs.next()){
				noticeInfo=new NoticeInfo(rs.getString("noticeID"),rs.getString("caption"),rs.getString("content"),rs.getString("publisher"),
						rs.getString("publishTime"),new SimpleDateFormat("yyyy-MM-dd'&nbsp;'HH:mm").format(rs.getDate("planPubTime")),
						new SimpleDateFormat("yyyy-MM-dd'&nbsp;'HH:mm").format(rs.getDate("planDelTime")));
				noticeList.addElement(noticeInfo);
			}
			return noticeList;
		} catch (SQLException e) {
			// TODO 自動生成 catch 塊
			e.printStackTrace();
		}finally{
			CNProvider.release(rs,pstat,conn);
		}
		return null;
	}

	public static NoticeInfo noticeQuery(String pNoticeID) {
		Connection conn = CNProvider.getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		NoticeInfo noticeInfo=null;
		int noticeID;
		if (pNoticeID == null || "".equals(pNoticeID.trim())) {
			System.out.print("NoticeID is null or empty String");
			return null;
		}else{
			try{
				noticeID=Integer.parseInt(pNoticeID);
			}catch(NumberFormatException e){
				e.printStackTrace();
				return null;
			}
		}
		try {
			pstat = conn.prepareStatement("select * from Notice where noticeID=?");
			pstat.setInt(1, noticeID);
			rs = pstat.executeQuery();
			if(rs.next()){
				noticeInfo=new NoticeInfo(rs.getString("noticeID"),rs.getString("caption"),rs.getString("content"),rs.getString("publisher"),
						rs.getString("publishTime"),new SimpleDateFormat("yyyy-MM-dd HH:mm").format(rs.getDate("planPubTime")),
						new SimpleDateFormat("yyyy-MM-dd HH:mm").format(rs.getDate("planDelTime")));
			}
		} catch (SQLException e) {
			// TODO 自動生成 catch 塊
			e.printStackTrace();
		}finally{
			CNProvider.release(rs,pstat,conn);
		}
		return noticeInfo;
	}

	public static boolean noticeInsert(NoticeInfo noticeInfo) {
		Connection conn = CNProvider.getConnection();
		PreparedStatement pstat = null;
		if(!noticeInfo.isValid()){
			return false;
		}
		try {
			pstat = conn
					.prepareStatement("insert into Notice values(?,?,?,?,?,?)");
			pstat.setString(1, noticeInfo.getCaption());
			pstat.setString(2, noticeInfo.getContent());
			pstat.setString(3, noticeInfo.getPublisher());
			pstat.setString(4, noticeInfo.getPublishTime());
			pstat.setString(5, noticeInfo.getPlanPubTime());
			pstat.setString(6, noticeInfo.getPlanDelTime());
			pstat.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO 自動生成 catch 塊
			e.printStackTrace();
		} finally {
			CNProvider.release(pstat, conn);
		}
		return false;
	}

	public static boolean noticeUpdate(NoticeInfo noticeInfo) {
		Connection conn = CNProvider.getConnection();
		PreparedStatement pstat = null;
		if (!noticeInfo.isValid()) {
			System.out.print("NoticeID is null or empty String");
			return false;
		}
		try {
			pstat = conn
					.prepareStatement("update Notice set caption=?,content=?,publisher=?,publishTime=?,planpubtime=?,plandeltime=? where noticeID=?");
			pstat.setString(1, noticeInfo.getCaption());
			pstat.setString(2, noticeInfo.getContent());
			pstat.setString(3, noticeInfo.getPublisher());
			pstat.setString(4, noticeInfo.getPublishTime());
			pstat.setString(5, noticeInfo.getPlanPubTime());
			pstat.setString(6, noticeInfo.getPlanDelTime());
			pstat.setInt(7, noticeInfo.getNoticeID());
			pstat.executeUpdate();
			return true;

		} catch (SQLException e) {
			// TODO 自動生成 catch 塊
			e.printStackTrace();
		} finally {
			CNProvider.release(pstat, conn);
		}
		return false;
	}

	public static boolean noticeDelete(String pNoticeID) {
		Connection conn = CNProvider.getConnection();
		PreparedStatement pstat = null;
		int noticeID;
		if (pNoticeID == null || "".equals(pNoticeID.trim())) {
			System.out.print("NoticeID is null or empty String");
			return false;
		} else {
			noticeID = Integer.parseInt(pNoticeID);
		}
		try {
			pstat = conn
					.prepareStatement("delete from Notice where noticeID=?");
			pstat.setInt(1, noticeID);
			pstat.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO 自動生成 catch 塊
			e.printStackTrace();
		}finally{
			CNProvider.release(pstat,conn);
		}
		return false;
	}
	
	public static int newNoticeCount(){
		Connection conn=CNProvider.getConnection();
		PreparedStatement pstat=null;
		ResultSet rs=null;
		int count=0;
		try {
			pstat=conn.prepareStatement("SELECT COUNT(*) FROM Notice WHERE (DATEDIFF([day], PublishTime, GETDATE()) <?)");
			pstat.setInt(1,Constants.NEWNOTICEDAYS);
			rs=pstat.executeQuery();
			if(rs.next()){
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(rs,pstat,conn);
		}
		return count;
	}
}