scheduledao.java

oa办公管理系统。一些小型的企业办公管理用的系统。一个月废寝忘食的结果。

package com.soft.schedule.dao;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.soft.pagecut.PageableResultSet;
import com.soft.pagecut.pageable;
import com.soft.util.DBConn;
import com.soft.vo.ScheduleInfo;
import com.soft.vo.UserInfo;

public class scheduleDAO {
	 
	 private DBConn tj = new DBConn();
	 DBConn db=new DBConn();
     private Connection conn=null;
     private PreparedStatement ps=null;
     private Statement st=null;
     private ResultSet rs=null;
     private pageable res=null;
    int PageCount;
 	int rowsCount;
 	int PageRowsCount;
 	
 	int PageSize;
 	int CurPage;
 	public int getPageCount() {
 		return PageCount;
 	}
 
 	

 	public int getPageRowsCount() {
 		return PageRowsCount;
 	}

 	

 	public int getPageSize() {
 		return PageSize;
 	}

 	public void setPageSize(int pageSize) {
 		PageSize = pageSize;
 	}

 	public int getCurPage() {
 		return CurPage;
 	}

 	public int getRowsCount() {
 		return rowsCount;
 	}

 	public void setCurPage(int curPage) {
 		CurPage = curPage;
 	}
 	public int insertSchedule(int employeeid,String starttime,String endtime,String content,int isfinish,int[]reader,String theme) throws SQLException, UnsupportedEncodingException
    {
   	 content=new String(content.getBytes("ISO-8859-1"),"utf-8");
     theme=new String(theme.getBytes("ISO-8859-1"),"utf-8");
   	 int id=0;
   	 int b=0;
   	 int c=0;
   	 try
   	 {
        conn=db.getConnection();
   	 String schedule="select max(scheduleid) from schedule";
   	 st=conn.createStatement();
   	 rs=st.executeQuery(schedule);
   	 if(!rs.next())
   	 {
   	    String sql="insert into schedule values("+1+","+employeeid+",to_date('"+starttime+"','yyyy-mm-dd'),to_date('"+endtime+"','yyyy-mm-dd'),'"+content+"',"+isfinish+",'"+theme+"')";
   	    st=conn.createStatement();
   	    b=st.executeUpdate(sql);
   	 }
   	 else
   	 {
   		 id=rs.getInt("max(scheduleid)");
   		 id=id+1;
   		 String sql="insert into schedule values("+id+","+employeeid+",to_date('"+starttime+"','yyyy-mm-dd'),to_date('"+endtime+"','yyyy-mm-dd'),'"+content+"',"+isfinish+",'"+theme+"')";
       	 st=conn.createStatement();
       	 b=st.executeUpdate(sql);
       	 if(b>0)
       	 {
       		for(int i=0;i<reader.length;i++)
            {
    	    String insertsql="insert into schedulereader values("+employeeid+","+reader[i]+","+id+")";
    	    st=conn.createStatement();
    	     c=st.executeUpdate(insertsql);
            }
       	 }
   	 }
   	 
   	 }
   	 catch(SQLException e)
   	 {
   		 e.printStackTrace();
   	 }
   	 finally
   	 {
   		 if(conn!=null)
   		 {
   			 conn.close();
   		 }
   		 if(st!=null)
   		 {
   			 st.close();
   		 }
   	 }
   	 return c;
    }
    public int  updatefinish(int scheduleid)
     {
    	int i=0;
    	 try
    	 {
            conn=db.getConnection();
    	    String sql="update schedule set isfinish=1 where scheduleid="+scheduleid;
    	    st=conn.createStatement();
    	    i=st.executeUpdate(sql);
    	 
    	 }
    	 catch(SQLException e)
    	 {
    		 e.printStackTrace();
    	 }
    	 finally
    	 {
    		 try{
    		 if(conn!=null)
    		 {
    			conn.close();
    		 }
    		 if(st!=null)
    		 {
    			st.close();
    		 }
    		 }
    		 catch(Exception e){}
    	 }
    	 return i;
     }
     public int insertScheDelegete(int certigierid,int delegator ) throws UnsupportedEncodingException, SQLException
     {
    	
    	 //delegator=new String(delegator.getBytes("ISO-8859-1"),"utf-8");
    	int b=0;
    	 try
    	 {
            conn=db.getConnection();
            String sql="select * from schedelegate where certigier="+certigierid;
    	    st=conn.createStatement();
    	    rs=st.executeQuery(sql);
            if(rs!=null)
            {
            	String delsql="delete from schedelegate where certigier="+certigierid;
        	    st=conn.createStatement();
        	    st.executeUpdate(delsql);
            }
           
            
    	    String insertsql="insert into schedelegate values("+certigierid+","+delegator+")";
    	    st=conn.createStatement();
    	     b=st.executeUpdate(insertsql);
         
    	 }
    	 catch(SQLException e)
    	 {
    		 e.printStackTrace();
    	 }
    	 finally
    	 {
    		 if(conn!=null)
    		 {
    			 conn.close();
    		 }
    		 if(st!=null)
    		 {
    			 st.close();
    		 }
    	 }
    	 return b;
     }
     
     public List searchSchedule(int employeeid,String isfinish,String date)
     {
    	 List<ScheduleInfo> lt = new ArrayList<ScheduleInfo>();
 		try {
 			conn=tj.getConnection();
 			conn.setAutoCommit(false);
 			 String sql = "select s.scheduleid,s.starttime,s.endtime,s.content,e.name,s.isfinish from schedule s,schedelegate d ,employee e where e.employeeid=s.employeeid and s.employeeid=d.certigier and 1=1 ";
 			sql+="and s.employeeid=?";
 		    if(isfinish!=null&&!isfinish.equals("")) //2-->all
 			{
 			  sql+="and s.isfinish=?";
 			}
 			if(date!=null&&!date.equals("")&&!date.equals("wrong"))
			{
 				sql+="and s.starttime=to_date(?,'yyyy-mm-dd')";
 		   System.out.println("dateeeeeeeeee"+date);
 			} 
 			ps=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			ps.setInt(1, employeeid);
 			if(isfinish!=null&&!isfinish.equals(""))
			{
				ps.setInt(2,Integer.parseInt(isfinish));
				if(date!=null&&!date.equals(""))
				{
					ps.setString(3, date);
				}
			}
			else
			{
				if(date!=null&&!date.equals("")&&!date.equals("wrong"))
				{
					ps.setString(2, date);
				}
			}
 			 		
 			 res=new PageableResultSet(ps.executeQuery());
 			
 			res.setPageSize(5);//每页5个记录
             System.out.println(sql+isfinish+date);
 			System.out.println("pagetcount:"+res.getPageCount()+"rowscount:"+res.getRowsCount()+"pagesize:"+res.getPageSize());
 			
 			res.gotoPage(getCurPage());//跳转到第n页
 			PageCount=res.getPageCount();
 			rowsCount=res.getRowsCount();
 			PageRowsCount=res.getPageRowsCount();
 			
 			System.out.println("current page:"+res.getCurPage());
 			for(int i=0; i<res.getPageRowsCount(); i++)
 				
			{
 				   ScheduleInfo uif = new ScheduleInfo();
 				  
 				   int scheduleId=res.getInt("scheduleid");
 				   Date startTime=res.getDate("starttime");
 				   Date endTime=res.getDate("endtime");
 				   String content=res.getString("content");
 				   String delegator=res.getString("name");
 				   int isfinishs=res.getInt("isfinish");
 				   uif.setScheduleId(scheduleId);
 				   uif.setStartTime(startTime);
 				   uif.setEndTime(endTime);
 				   uif.setContent(content);
 				   uif.setDelegator(delegator);
 			       uif.setIsfinish(isfinishs);
 				   lt.add(uif);	
 				   res.next();
 			}
 			
 		    conn.commit();
 		} catch (SQLException e) {
 			
 			try {
 				if(conn!=null)
 				{
 					conn.rollback();//事务回滚
 				}
 			} catch (SQLException e1) {
 				
 				e1.printStackTrace();
 			}
 			e.printStackTrace();
 		}
 		finally
 		{
 			try {
 				if(conn!=null)
 				{
 					conn.close();
 				}
 				if(ps!=null)
 				{
 					ps.close();
 				}
 				if(rs!=null)
 				{
 					rs.close();
 				}
 			} catch (SQLException e) {
 				e.printStackTrace();
 			}
 		}
 		  return lt; 
    	 
     }
     //测试
     public List searchPerSchedule(int employeeid)
     {
    	List<ScheduleInfo> lt = new ArrayList<ScheduleInfo>();
 		try {
 			conn=tj.getConnection();
 			conn.setAutoCommit(false);
 			String sql = "select * from schedule where employeeid="+employeeid;
 			ps=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);	
 			res=new PageableResultSet(ps.executeQuery());
 			while(res.next())
 			{
 				   ScheduleInfo uif = new ScheduleInfo();
 				   int scheduleId=res.getInt("scheduleid");
 				   String theme=res.getString("theme");
 				   int isfinish=res.getInt("isfinish");
 				   uif.setScheduleId(scheduleId);
 				   uif.setTheme(theme);
 				   uif.setIsfinish(isfinish);
 				   lt.add(uif);	
 			}
 			
 		    conn.commit();
 		} catch (SQLException e) {
 			
 			try {
 				if(conn!=null)
 				{
 					conn.rollback();//事务回滚
 				}
 			} catch (SQLException e1) {
 				
 				e1.printStackTrace();
 			}
 			e.printStackTrace();
 		}
 		finally
 		{
 			try {
 				if(conn!=null)
 				{
 					conn.close();
 				}
 				if(ps!=null)
 				{
 					ps.close();
 				}
 				if(rs!=null)
 				{
 					rs.close();
 				}
 			} catch (SQLException e) {
 				e.printStackTrace();
 			}
 		}
 		  return lt; 
    	 
     }
     
     public List searchAllSchedules()
     {
    	List<ScheduleInfo> lt = new ArrayList<ScheduleInfo>();
 		try {
 			conn=tj.getConnection();
 			conn.setAutoCommit(false);
 			String sql = "select * from schedule";
 			ps=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);	
 			res=new PageableResultSet(ps.executeQuery());
 			
 			res.setPageSize(15);//每页5个记录
             
 			System.out.println("pagetcount:"+res.getPageCount()+"rowscount:"+res.getRowsCount()+"pagesize:"+res.getPageSize());
 			
 			res.gotoPage(getCurPage());//跳转到第n页
 			PageCount=res.getPageCount();
 			rowsCount=res.getRowsCount();
 			PageRowsCount=res.getPageRowsCount();
 			
 			System.out.println("current page:"+res.getCurPage());
 			for(int i=0; i<res.getPageRowsCount(); i++)
 				
			{
 				   ScheduleInfo uif = new ScheduleInfo();
 				  
 				   int scheduleId=res.getInt("scheduleid");
 				   Date startTime=res.getDate("starttime");
 				   Date endTime=res.getDate("endtime");
 				   String content=res.getString("content");
 				   //String delegator=res.getString("name");
 				   uif.setScheduleId(scheduleId);
 				   uif.setStartTime(startTime);
 				   uif.setEndTime(endTime);
 				   uif.setContent(content);
 				   lt.add(uif);	
 				   res.next();
 			}
 			
 		    conn.commit();
 		} catch (SQLException e) {
 			
 			try {
 				if(conn!=null)
 				{
 					conn.rollback();//事务回滚
 				}
 			} catch (SQLException e1) {
 				
 				e1.printStackTrace();
 			}
 			e.printStackTrace();
 		}
 		finally
 		{
 			try {
 				if(conn!=null)
 				{
 					conn.close();
 				}
 				if(ps!=null)
 				{
 					ps.close();
 				}
 				if(rs!=null)
 				{
 					rs.close();
 				}
 			} catch (SQLException e) {
 				e.printStackTrace();
 			}
 		}
 		  return lt; 
    	 
     }
     
     public List searchSchedulebyid(int scheduleid)
     {
    	List<ScheduleInfo> lt = new ArrayList<ScheduleInfo>();
 		try {
 			conn=tj.getConnection();
 		    String sql = "select * from schedule where scheduleid='"+scheduleid+"'";
 		    st=conn.createStatement();
 		    rs=st.executeQuery(sql);
 			while(rs.next())
 			{
 				   ScheduleInfo uif = new ScheduleInfo();
 				  
 				   
 				   Date startTime=rs.getDate("starttime");
 				   Date endTime=rs.getDate("endtime");
 				   uif.setScheduleId(rs.getInt("scheduleid"));
 				   uif.setStartTime(startTime);
 				   uif.setEndTime(endTime);
 				   uif.setContent(rs.getString("content"));
 				   uif.setTheme(rs.getString("theme"));
 			       lt.add(uif);	
 				  
 			}
 			
 		    
 		} catch (SQLException e) {
 			
 			e.printStackTrace();
 		}
 		finally
 		{
 			try {
 				if(conn!=null)
 				{
 					conn.close();
 				}
 				if(ps!=null)
 				{
 					ps.close();
 				}
 				if(rs!=null)
 				{
 					rs.close();
 				}
 			} catch (SQLException e) {
 				e.printStackTrace();
 			}
 		}
 		  return lt; 
    	 
     }
     
     public int delschedule(int scheduleid) throws SQLException
     {
    	 int i=0;
     
    	 try
       	 {
         conn=db.getConnection();
       	 String sql="delete from schedule where scheduleid='"+scheduleid+"'";
       	 st=conn.createStatement();
       	 i=st.executeUpdate(sql);
       	 }
       	 catch(SQLException e)
       	 {
       		 e.printStackTrace();
       	 }
       	 finally
       	 {
       		 if(conn!=null)
       		 {
       			 conn.close();
       		 }
       		 if(st!=null)
       		 {
       			 st.close();
       		 }
       	 }
       	 return i;
       	 
     }
     
     public int updatesche(String starttime,String endtime,String contents,int scheduleid) throws SQLException
     {
    	 try {
			contents=new String(contents.getBytes("ISO-8859-1"),"utf-8");
		} catch (UnsupportedEncodingException e1) {
		
			e1.printStackTrace();
		}
         int i=0;
    	 try
       	 {
         conn=db.getConnection();
       	 String sql="update schedule set scheduleid="+scheduleid+",starttime=to_date('"+starttime+"','yyyy-mm-dd'),endtime=to_date('"+endtime+"','yyyy-mm-dd'),content='"+contents+"' where scheduleid="+scheduleid+"";
       	 st=conn.createStatement();
       	 i=st.executeUpdate(sql);
       	 }
       	 catch(SQLException e)
       	 {
       		 e.printStackTrace();
       	 }
       	 finally
       	 {
       		 if(conn!=null)
       		 {
       			 conn.close();
       		 }
       		 if(st!=null)
       		 {
       			 st.close();
       		 }
       	 }
       	 return i;
     }
     
    
       	 
     
    
}