meetdao.java

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

package com.soft.meet.dao;

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

import com.soft.pagecut.PageableResultSet;
import com.soft.pagecut.pageable;
import com.soft.util.DBConn;
import com.soft.vo.EquipmentInfo;
import com.soft.vo.MeetInfo;

public class MeetDAO {

	

	private DBConn tj = new DBConn();
	private Connection conn = null; 
	private Statement st = null;
	private PreparedStatement ps = 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 List getUncheckMeet()
	{
		List<MeetInfo> lt = new ArrayList<MeetInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
	        res=new PageableResultSet(st.executeQuery("select to_char(starttime,'YYYY-MM-DD HH24:MI:SS'),to_char(endtime,'YYYY-MM-DD HH24:MI:SS'), a.*,m.meetingroomname,e.name from applymeetroom a ,meetingroom m,employee e where a.meetingroomid=m.meetingroomid and a.applicant=e.employeeid and  isauthorized=0"));//构造一个Pageable
			res.setPageSize(5);//每页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++)
			
			{
			    MeetInfo mif = new MeetInfo();
			    String starttimestr=res.getString(1);
				String endtimestr=res.getString(2);
				int applicant=res.getInt("applicant");
				int meetingroomid=res.getInt("meetingroomid");
				String theme=res.getString("theme");
				int numberofpeople=res.getInt("numberofpeople");
				String meetingpeople=res.getString("meetingpeople");
				int meetid=res.getInt("meetid");
				String meetingroomname=res.getString("meetingroomname");
				String name=res.getString("name");
				mif.setApplicant(applicant);
				mif.setMeetingroomid(meetingroomid);
				mif.setTheme(theme);
				mif.setNumberofpeople(numberofpeople);
				mif.setMeetingpeople(meetingpeople);
				mif.setMeetid(meetid);
				mif.setIsauthorized("否");
				mif.setStarttimestr(starttimestr);
				mif.setEndtimestr(endtimestr);
				mif.setMeetingroomname(meetingroomname);
				mif.setName(name);
				lt.add(mif);
				res.next();
				
			}
			System.out.println(lt.size());
		    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 getAllMeet()
	{
		List<MeetInfo> lt = new ArrayList<MeetInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
	        res=new PageableResultSet(st.executeQuery("select to_char(starttime,'YYYY-MM-DD HH24:MI:SS'),to_char(endtime,'YYYY-MM-DD HH24:MI:SS'),a.*,m.meetingroomname,e.name from applymeetroom a ,meetingroom m,employee e where a.meetingroomid=m.meetingroomid and a.applicant=e.employeeid"));//构造一个Pageable
			res.setPageSize(5);//每页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());
			System.out.println("current rowcount:"+res.getPageRowsCount());
			for(int i=0; i<res.getPageRowsCount(); i++)
			
			{
			    MeetInfo mif = new MeetInfo();
			    String isauthorizedstr="未审核";
			    String starttimestr=res.getString(1);
				String endtimestr=res.getString(2);
				System.out.println("dateeeeeeeee:=========="+starttimestr);
			    int applicant=res.getInt("applicant");
				int meetingroomid=res.getInt("meetingroomid");
				Date starttime=res.getDate("starttime");
				Date endtime=res.getDate("endtime");
				String theme=res.getString("theme");
				int numberofpeople=res.getInt("numberofpeople");
				String meetingpeople=res.getString("meetingpeople");
				int isauthorized=res.getInt("isauthorized");
				int meetid=res.getInt("meetid");
				String meetingroomname=res.getString("meetingroomname");
				String name=res.getString("name");
				if(isauthorized==1)
					isauthorizedstr="已审核";
				mif.setApplicant(applicant);
				mif.setMeetingroomid(meetingroomid);
				mif.setTheme(theme);
				mif.setNumberofpeople(numberofpeople);
				mif.setMeetingpeople(meetingpeople);
				mif.setMeetid(meetid);
				mif.setIsauthorized(isauthorizedstr);
				mif.setStarttimestr(starttimestr);
				mif.setEndtimestr(endtimestr);
				mif.setMeetingroomname(meetingroomname);
				mif.setName(name);
				lt.add(mif);
				res.next();
				
			}
			System.out.println(lt.size());
		    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 int updateMeet(int meetid,int meetingroomid)
	{
		int i = 0;
		int j=0;
		int isauthorized=1;
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement("update  applymeetroom set isauthorized=? where meetid=?");
			ps.setInt(1,isauthorized);
			ps.setInt(2,meetid);
			i = ps.executeUpdate();
			if(i>0)
			{
				ps = conn.prepareStatement("update  meetingroom set iscanapply=0 where meetingroomid=?");
				ps.setInt(1,meetingroomid);

				j = ps.executeUpdate();
			}
		    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 j;
	}
	
	
	public List getMeets(String select,int empid)
	{
		List<MeetInfo> lt = new ArrayList<MeetInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql="select to_char(starttime,'YYYY-MM-DD HH24:MI:SS'),to_char(endtime,'YYYY-MM-DD HH24:MI:SS'), a.*,m.meetingroomname,e.name from applymeetroom a,meetingroom m ,employee e where a.meetingroomid=m.meetingroomid and a.applicant=e.employeeid";
			if(select.equals("all"))
				sql=sql;
			if(select.equals("mestart"))
				sql=sql + " and applicant="+empid;
			if(select.equals("meattend"))
				sql= sql + " and meetingpeople like '%"+empid+"%'";
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
	        res=new PageableResultSet(st.executeQuery(sql));//构造一个Pageable
			res.setPageSize(5);//每页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++)
			
			{
			    MeetInfo mif = new MeetInfo();
			    String isauthorizedstr="未审核";
			    String starttimestr=res.getString(1);
				String endtimestr=res.getString(2);
			    int applicant=res.getInt("applicant");
				int meetingroomid=res.getInt("meetingroomid");
		        String theme=res.getString("theme");
				int numberofpeople=res.getInt("numberofpeople");
				String meetingpeople=res.getString("meetingpeople");
				int isauthorized=res.getInt("isauthorized");
				int meetid=res.getInt("meetid");
				String meetingroomname=res.getString("meetingroomname");
				String name=res.getString("name");
				if(isauthorized==1)
					isauthorizedstr="已审核";
				mif.setApplicant(applicant);
				mif.setMeetingroomid(meetingroomid);
				mif.setStarttimestr(starttimestr);
				mif.setEndtimestr(endtimestr);
				mif.setTheme(theme);
				mif.setNumberofpeople(numberofpeople);
				mif.setMeetingpeople(meetingpeople);
				mif.setMeetid(meetid);
				mif.setIsauthorized(isauthorizedstr);
				mif.setMeetingroomname(meetingroomname);
                mif.setName(name);
				lt.add(mif);
				res.next();
				
			}
			System.out.println(lt.size());
		    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 int delMeet(int meetid)
	{
		int i = 0;
		int isauthorized=0;
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement("delete from  applymeetroom where isauthorized=? and meetid=?");
			ps.setInt(1,isauthorized);
			ps.setInt(2,meetid);
			i = ps.executeUpdate();
		    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 i;
	}
	public int meetApp(int applicant,String theme,int meetingroomid,String starttime,String endtime,String meetingpeople)
	{
		int i = 0;
		int numberofpeople=0;
		int isauthorized=0;
	
		int meetid=1;
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			
			String sql="insert  into applymeetroom values("+applicant+","+meetingroomid+",to_date('"+starttime+"','yyyy-mm-dd hh24:mi:ss'),to_date('"+endtime+"','yyyy-mm-dd hh24:mi:ss'),'"+theme+"',"+numberofpeople+",'"+meetingpeople+"',"+isauthorized+",";
			ps=conn.prepareStatement("select max(meetid) from applymeetroom");
			System.out.println(sql);
			ResultSet rs=ps.executeQuery();
			if(!rs.next())
			{
				ps = conn.prepareStatement(sql+meetid+")");
			
			}
			else
			{
			   meetid=rs.getInt("max(meetid)");
			   System.out.println("max:"+meetid);
			   meetid=meetid+1;
			   ps = conn.prepareStatement(sql+meetid+")");
			  
			}
			
			i = ps.executeUpdate();
			
		    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 i;
}
}