eventdaoimp.java

事件提醒系统

package com.myoa.cal.imp;

//import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.myoa.cal.dao.DAOFactory;
import com.myoa.cal.dao.DBFactoryDAO;
import com.myoa.cal.dao.EventDAO;
import com.myoa.cal.model.CalEvent;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

import com.myoa.cal.util.*;

public class EventDAOImp implements EventDAO{
	
	public String getOneEvent(int eventid){
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = "select * from cal_events where eventid="+eventid;
		DBFactoryDAO t=new DAOFactory().getDBFacotryDAO();
		JSONArray array = new JSONArray(); 
		JSONObject obj=null;
		try {
			conn = t.get_DBMysql();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				obj = new JSONObject();
                obj.put("eventid", rs.getString("eventid"));
                obj.put("eventname", rs.getString("eventname"));
                obj.put("eventdescribe", rs.getString("eventdescribe"));
                obj.put("userid", rs.getInt("userid"));
                obj.put("typeid", rs.getInt("typeid"));
                obj.put("createtime", rs.getString("createtime"));
                obj.put("occurtime", rs.getTime("occurtime").toString());
                obj.put("early_remindtime", rs.getInt("early_remindtime"));
                obj.put("space_remindtime", rs.getInt("space_remindtime"));
                //List list = new DateUtil().remindTime(rs.getDate("createtime"), rs.getInt("early_remindtime"), rs.getInt("space_remindtime"));
                array.add(obj);
                }
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			t.closeStatement(stmt);
			t.closeResultSet(rs);
			t.closeConnection(conn);
		}
		return array.toString();

	}

	public int addEvent(CalEvent event) {
		Connection conn = null;
		PreparedStatement stmt = null;
		String sql = 
			"insert into cal_events(eventname,eventdescribe,userid,typeid,createtime,occurtime,early_remindtime,space_remindtime) values(?,?,?,?,NOW(),?,?,?)";
		DBFactoryDAO t=new DAOFactory().getDBFacotryDAO();
		//System.out.println(new DateUtil().UDateToSDate(event.getOccurtime())+"ddddddddddddd");
		int eventid = 0;
		try {
			conn = t.get_DBMysql();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, event.getEventname());
			stmt.setString(2, event.getEventdescribe());
			stmt.setInt(3, event.getUserid());
			stmt.setInt(4, event.getTypeid());
			stmt.setTimestamp(5, event.getOccurtime());
			stmt.setInt(6, event.getEarlyRemindtime());
			stmt.setInt(7, event.getSpaceRemindtime());
			stmt.executeUpdate();
			ResultSet res=stmt.getGeneratedKeys(); 
			while(res.next()){ 
			eventid=res.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			t.closeStatement(stmt);
			t.closeConnection(conn);
		}
		return eventid;
	}
	
	/**
	 * 根据eventid删除一条事件记录
	 * @param eventid
	 */
	public boolean deleteEvent(int eventid) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement stmt = null;
		boolean temp = false;
		String sql = "delete from cal_events where eventid=?";
		DBFactoryDAO t=new DAOFactory().getDBFacotryDAO();
		try {
			
			conn = t.get_DBMysql();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, eventid);
			int result = stmt.executeUpdate();
			
			if(result==1){
				temp = true;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			temp = false;
		}finally{
			t.closeStatement(stmt);
			t.closeConnection(conn);
		}
		return temp;
	}

//	public List<CalEvent> getAllEvents(int userid) {
//		Connection conn = null;
//		PreparedStatement stmt = null;
//		ResultSet rs = null;
//		String sql = "select * from cal_events,cal_types where cal_events.userid=" + userid + " and cal_events.typeid=cal_types.typeid";
//		List<CalEvent> ls = new ArrayList<CalEvent>();
//
//		DBFactoryDAO t=new DAOFactory().getDBFacotryDAO();
//		try {
//			conn = t.get_DBMysql();
//			stmt = conn.prepareStatement(sql);
//			rs = stmt.executeQuery();
//			while(rs.next()){
//				CalEvent event = new CalEvent();
//				event.setEventid(rs.getInt("eventid"));
//				event.setEventname(rs.getString("eventname"));
//				event.setEventdescribe(rs.getString("eventdescribe"));
//				event.setCreatetime(rs.getTimestamp("createtime"));
//				event.setOccurtime(rs.getTimestamp("occurtime"));
//				event.setEarlyRemindtime(rs.getInt("early_remindtime"));
//				event.setSpaceRemindtime(rs.getInt("space_remindtime"));
//				event.setTypename(rs.getString("eventype"));
//				//查询出所有的提示时间
//				event.setRemindlist(new DateUtil().remindTime(event.getOccurtime(), event.getEarlyRemindtime(), event.getSpaceRemindtime()));
//				
//				ls.add(event);
//			}
//
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally{
//			t.closeStatement(stmt);
//			t.closeConnection(conn);
//		}
//		return ls;
//	}
	
	public String getAllEvents(int userid,String date){
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		JSONArray array = new JSONArray(); 
		String sql = "select * from cal_events,cal_types where cal_events.userid="+userid+" and occurtime like '"+date+"%' and cal_events.typeid=cal_types.typeid";
		DBFactoryDAO t=new DAOFactory().getDBFacotryDAO();

		try {
			conn = t.get_DBMysql();
			stmt = conn.prepareStatement(sql);
//			stmt.setInt(1, userid);
//			stmt.setDate(2, new DateUtil().UDateToSDate(date));
			rs = stmt.executeQuery();
			while(rs.next()){
				JSONObject obj = new JSONObject();
                obj.put("eventid", rs.getString("eventid"));
                obj.put("eventname", rs.getString("eventname"));
                obj.put("eventdescribe", rs.getString("eventdescribe"));
                obj.put("userid", rs.getInt("userid"));
                obj.put("typeid", rs.getInt("typeid"));
                obj.put("createtime", rs.getString("createtime"));
                obj.put("occurtime", new DateUtil().StrToStrDate(rs.getString("occurtime")));
                obj.put("early_remindtime", rs.getInt("early_remindtime"));
                obj.put("space_remindtime", rs.getInt("space_remindtime"));
                List list = new DateUtil().remindTime(rs.getDate("createtime"), rs.getInt("early_remindtime"), rs.getInt("space_remindtime"));
                //obj.put("remindtime",list);
                array.add(obj);
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			t.closeConnection(conn);
			t.closeStatement(stmt);
			t.closeResultSet(rs);
		}
			
		return array.toString();
	}

	
	public boolean updateEvent(CalEvent event) {
		Connection conn = null;
		PreparedStatement stmt = null;
		boolean temp = false;
		String sql = 
			"update cal_events set eventname=?,eventdescribe=?,typeid=?,occurtime=?,early_remindtime=?,space_remindtime=? where eventid=" + event.getEventid();
		DBFactoryDAO t=new DAOFactory().getDBFacotryDAO();
		try {
			
			conn = t.get_DBMysql();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, event.getEventname());
			stmt.setString(2, event.getEventdescribe());
			stmt.setInt(3, event.getTypeid());
			stmt.setTimestamp(4, event.getOccurtime());
			stmt.setInt(5, event.getEarlyRemindtime());
			stmt.setInt(6, event.getSpaceRemindtime());
			int result = stmt.executeUpdate();
			
			if(result==1){
				temp = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			temp = false;
		}finally{
			t.closeStatement(stmt);
			t.closeConnection(conn);
		}
		return temp;

	}
	
//	判断当天事件是否提醒，及提醒时间
	public String getNowRemind(String userid,String nowdate,Date nowtime){
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String user_id = null;
		JSONArray array = new JSONArray();
		String sql = 
			"select * from cal_events,cal_types where cal_events.userid="+userid+" and occurtime like '"+nowdate+"%' and cal_events.typeid=cal_types.typeid";
		DBFactoryDAO t=new DAOFactory().getDBFacotryDAO();
		try {
			conn = t.get_DBMysql();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				Date occurtime = rs.getTimestamp("occurtime");
				int early_remindtime = rs.getInt("early_remindtime");
				int space_remindtime = rs.getInt("space_remindtime");
				List list = new DateUtil().remindTime(occurtime , early_remindtime, space_remindtime );
				Date d1 = (Date) list.get(0);

				if(nowtime.after(d1) && nowtime.before(occurtime)){
					JSONObject obj = new JSONObject();
					obj.put("eventid", rs.getString("eventid").toString());
	                obj.put("eventname", rs.getString("eventname"));
	                obj.put("eventdescribe", rs.getString("eventdescribe"));
	                //obj.put("userid", rs.getInt("userid"));
	                //obj.put("typeid", rs.getInt("typeid"));
	                //obj.put("createtime", rs.getDate("createtime"));
	                obj.put("occurtime", rs.getTime("occurtime").toString());
	                //obj.put("early_remindtime", rs.getInt("early_remindtime"));
	                //obj.put("space_remindtime", rs.getInt("space_remindtime"));
	                //obj.put("remindtime",list);
					array.add(obj);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			t.closeConnection(conn);
			t.closeStatement(stmt);
			t.closeResultSet(rs);
		}
		return array.toString();
	}
	
	public boolean isNeedRemind(String userid,String nowdate,Date nowtime){
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String user_id = null;
		boolean temp = false;
		JSONArray array = new JSONArray();
		String sql = 
			"select * from cal_events,cal_types where cal_events.userid="+userid+" and occurtime like '"+nowdate+"%' and cal_events.typeid=cal_types.typeid";
		DBFactoryDAO t=new DAOFactory().getDBFacotryDAO();
		try {
			conn = t.get_DBMysql();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				Date occurtime = rs.getTimestamp("occurtime");
				int early_remindtime = rs.getInt("early_remindtime");
				int space_remindtime = rs.getInt("space_remindtime");
				List list = new DateUtil().remindTime(occurtime , early_remindtime, space_remindtime );
				Date d1 = (Date) list.get(0);
				if(nowtime.after(d1) && nowtime.before(occurtime)){
					temp = true;
					}	
				}
			}catch(SQLException e){
				e.printStackTrace();
			}finally{
				t.closeConnection(conn);
				t.closeStatement(stmt);
				t.closeResultSet(rs);
			}
			return temp;
	}
	
//	public List<CalEvent> remindEvent(int userid){
//		Connection conn = null;
//		PreparedStatement stmt = null;
//		ResultSet rs = null;
//		String sql = "select * from cal_events,cal_types where cal_events.userid=" + userid + " and cal_events.typeid=cal_types.typeid";
//		List<CalEvent> ls = new ArrayList<CalEvent>();
//		//System.out.println(sql);
//		DBFactoryDAO t=new DAOFactory().getDBFacotryDAO();
//		try {
//			conn = t.get_DBMysql();
//			stmt = conn.prepareStatement(sql);
//			rs = stmt.executeQuery();
//			while(rs.next()){
//				CalEvent event = new CalEvent();
//				event.setEventid(rs.getInt("eventid"));
//				event.setEventname(rs.getString("eventname"));
//				event.setEventdescribe(rs.getString("eventdescribe"));
//				event.setCreatetime(rs.getTimestamp("createtime"));
//				event.setOccurtime(rs.getTimestamp("occurtime"));
//				event.setEarlyRemindtime(rs.getInt("early_remindtime"));
//				event.setSpaceRemindtime(rs.getInt("space_remindtime"));
//				event.setRemindlist(new DateUtil().remindTime(event.getOccurtime(), event.getEarlyRemindtime(), event.getSpaceRemindtime()));
//				event.setTypename(rs.getString("eventype"));
//				
//				ls.add(event);
//			}
//
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally{
//			t.closeStatement(stmt);
//			t.closeConnection(conn);
//		}
//		return ls;
//		
//	}


/*	
	public static void main(String[] args){
		
		EventDAO dao = new DAOFactory().getEventDAO();
		System.out.println(dao.getOneEvent(10005));
		
	}*/
	
}