roomapplyassigndaoimp.java

JSP移动商品管理平台源代码.........

package imis_hous.com.impl;

import java.sql.Connection;

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

import imis_hous.com.bean.RoomApplyAssign;
import imis_hous.com.dao.RoomApplyAssignDAO;
import cmis.database.DBPoolManager;
import imis_hous.com.pub.interfaces.PageCount;
import imis_hous.com.db.DBClose;

public class RoomApplyAssignDAOImp implements RoomApplyAssignDAO, PageCount {

	public RoomApplyAssignDAOImp() {
		// TODO Auto-generated constructor stub
	}

	public int totalRecord = 0;

	public int record_per_page = 15;

	public int getAllRecords() {
		return totalRecord;
	}

	public int getAllPages() {
		return (totalRecord + record_per_page - 1) / record_per_page;
	}

	public boolean add(RoomApplyAssign raa) {
		boolean flag = false;
		String s = "全部";
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		if (new ExistSql().exist("select * from Room_Info where RoomNu='"
				+ raa.getRoomNu() + "'")) {
			if (new UpdateSql().update("update Room_Info set UseDept='"
					+ raa.getUseDept() + "',RoomUseFlag=2 where RoomNu='"
					+ raa.getRoomNu() + "'")) {
				String sql = "insert into RoomApplyAssign(RoomAppId, AppBy, AppDate, AppDes, "
						+ "AssiType, RoomNu, BuildingNu, RoomAssignDate, UseDate, AssiIs, "
						+ "RoomAssignExcuter, UseDept, RoomUser, AssiBy, AssiDesc, ReMark, Memo) values(RoomAppId.nextVal,'"
						+ raa.getAppBy()
						+ "',to_date('"
						+ raa.getAppDate()
						+ "','yyyy-mm-dd'),'"
						+ raa.getAppDes()
						+ "',"
						+ raa.getAssiType()
						+ ",'"
						+ raa.getRoomNu()
						+ "','"
						+ raa.getBuildingNu()
						+ "',to_date('"
						+ raa.getRoomAssignDate()
						+ "','yyyy-mm-dd'),to_date('"
						+ raa.getUseDate()
						+ "','yyyy-mm-dd'),"
						+ raa.getAssiIs()
						+ ",'"
						+ raa.getRoomAssignExcuter()
						+ "',"
						+ raa.getUseDept()
						+ ", '"
						+ raa.getRoomUser()
						+ "','"
						+ raa.getAssiBy()
						+ "','"
						+ raa.getAssiDesc()
						+ "'," + raa.getReMark() + ",'" + raa.getMemo() + "')";

				dbpool.getConnection();
				try {
					stmt = dbpool.conn.createStatement();

					int i = stmt.executeUpdate(sql);

					flag = i > 0 ? true : false;
					dbpool.commit();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					flag = false;
					
				} finally {
					dbclose.CloseStmt(stmt);
					dbpool.freeConnection();
				}
			}
		}else if(raa.getRoomNu().equals("s"))
		{
			raa.setRoomNu(s);
			String sql = "insert into RoomApplyAssign(RoomAppId, AppBy, AppDate, AppDes, "
				+ "AssiType, RoomNu, BuildingNu, RoomAssignDate, UseDate, AssiIs, "
				+ "RoomAssignExcuter, UseDept, RoomUser, AssiBy, AssiDesc, ReMark, Memo) values(RoomAppId.nextVal,'"
				+ raa.getAppBy()
				+ "',to_date('"
				+ raa.getAppDate()
				+ "','yyyy-mm-dd'),'"
				+ raa.getAppDes()
				+ "',"
				+ raa.getAssiType()
				+ ",'"
				+ raa.getRoomNu()
				+ "','"
				+ raa.getBuildingNu()
				+ "',to_date('"
				+ raa.getRoomAssignDate()
				+ "','yyyy-mm-dd'),to_date('"
				+ raa.getUseDate()
				+ "','yyyy-mm-dd'),"
				+ raa.getAssiIs()
				+ ",'"
				+ raa.getRoomAssignExcuter()
				+ "',"
				+ raa.getUseDept()
				+ ", '"
				+ raa.getRoomUser()
				+ "','"
				+ raa.getAssiBy()
				+ "','"
				+ raa.getAssiDesc()
				+ "'," + raa.getReMark() + ",'" + raa.getMemo() + "')";

		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();

			int i = stmt.executeUpdate(sql);

			flag = i > 0 ? true : false;
			dbpool.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag = false;
			
		} finally {
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
		}
		// TODO Auto-generated method stub
		return flag;
	}

	public boolean del(int id) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		String sql = "delete from RoomApplyAssign where RoomAppId=" + id + " ";
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();
			int i = stmt.executeUpdate(sql);

			flag = i > 0 ? true : false;
			dbpool.commit();
		} catch (SQLException e) {
			
		} finally {
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
		// TODO Auto-generated method stub
		return flag;
	}

	public List getSelect(int UseDept, String roomAssignDate, String useDate,String roomAssignDateed) {
		// TODO Auto-generated method stub
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		DBClose dbclose = new DBClose();
		RoomApplyAssign raa = null;
		List L_RoomApplyAssign = new ArrayList();
		String sql = "select * from RoomApplyAssign where 1=1";

		if (null != roomAssignDate)
			if (!roomAssignDate.equals("")) {
				sql += " and  roomassigndate between to_date('" + roomAssignDate
						+ "','yyyy-mm-dd') and to_date('"+roomAssignDateed+"','yyyy-mm-dd')";
			}
		
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				raa = new RoomApplyAssign();

				raa.setAppBy(rs.getString("appBy"));
				raa.setAppDate(rs.getDate("appDate"));
				raa.setAppDes(rs.getString("appDes"));
				raa.setAssiType(rs.getInt("assiType"));
				raa.setRoomNu(rs.getString("roomNu"));
				raa.setBuildingNu(rs.getString("buildingNu"));
				raa.setRoomAssignDate(rs.getDate("roomAssignDate"));
				raa.setUseDate(rs.getDate("useDate"));
				raa.setAssiIs(rs.getInt("assiIs"));
				raa.setRoomAssignExcuter(rs.getString("roomAssignExcuter"));
				raa.setUseDept(rs.getInt("useDept"));
				raa.setRoomUser(rs.getString("roomUser"));
				raa.setAssiBy(rs.getString("assiBy"));
				raa.setAssiDesc(rs.getString("assiDesc"));
				raa.setReMark(rs.getInt("reMark"));
				raa.setMemo(rs.getString("memo"));

				L_RoomApplyAssign.add(raa);
			}

		} catch (SQLException e) {
			
		} finally {
			dbclose.CloseResultSet(rs);
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
		return L_RoomApplyAssign;
	}

	public boolean update(int id) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		RoomApplyAssign raa = new RoomApplyAssign();
		String sql = "update RoomApplyAssign set  AppBy='" + raa.getAppBy()
				+ "', AppDae='" + raa.getAppDate() + "', AppDes='"
				+ raa.getAppDes() + "', " + "AssiType=" + raa.getAssiType()
				+ ", RoomNu='" + raa.getRoomNu() + "', BuildingNu='"
				+ raa.getBuildingNu() + "', RoomAssignDate='"
				+ raa.getRoomAssignDate() + "', UseDate='" + raa.getUseDate()
				+ "', AssiIs=" + raa.getAssiIs() + ", " + "RoomAssignExcuter='"
				+ raa.getRoomAssignExcuter() + "', UseDept=" + raa.getUseDept()
				+ ", RoomUser='" + raa.getRoomUser() + "', AssiBy='"
				+ raa.getAssiBy() + "', AssiDesc='" + raa.getAssiDesc()
				+ "', ReMark=" + raa.getReMark() + ", Memo='" + raa.getMemo()
				+ "' where RoomAppId=" + id + " ";
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();

			int i = stmt.executeUpdate(sql);

			flag = i > 0 ? true : false;
			dbpool.commit();
		} catch (SQLException e) {
		
		} finally {
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
		// TODO Auto-generated method stub
		return flag;
	}

	public List pageCount(int page) {
		List list = new ArrayList();
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RoomApplyAssign raa = null;
		DBClose dbclose = new DBClose();
		String sql = "select * from roomApplyAssign ";
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			rs.last();
			totalRecord = rs.getRow();

			int startRow = (page - 1) * record_per_page + 1;

			rs.absolute(startRow);

			int i = 0;

			while (i < record_per_page && !rs.isAfterLast()) {
				raa = new RoomApplyAssign();
				raa.setRoomAppId(rs.getInt("roomAppId"));
				raa.setAppBy(rs.getString("appBy"));
				raa.setAppDate(rs.getDate("appDate"));
				raa.setAppDes(rs.getString("appDes"));
				raa.setAssiType(rs.getInt("assiType"));
				raa.setRoomNu(rs.getString("roomNu"));
				raa.setBuildingNu(rs.getString("buildingNu"));
				raa.setRoomAssignDate(rs.getDate("roomAssignDate"));
				raa.setUseDate(rs.getDate("useDate"));
				raa.setAssiIs(rs.getInt("assiIs"));
				raa.setRoomAssignExcuter(rs.getString("roomAssignExcuter"));
				raa.setUseDept(rs.getInt("useDept"));
				raa.setRoomUser(rs.getString("roomUser"));
				raa.setAssiBy(rs.getString("assiBy"));
				raa.setAssiDesc(rs.getString("assiDesc"));
				raa.setReMark(rs.getInt("reMark"));
				raa.setMemo(rs.getString("memo"));

				list.add(raa);

				i++;

				rs.next();
			}

		} catch (SQLException e) {
			
		} finally {
			dbclose.CloseResultSet(rs);
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}

		return list;
	}

	public List pageCount(int page, int useDept, String roomAssignDate,
			String useDate,String s) {
		List list = new ArrayList();
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RoomApplyAssign raa = null;
		DBClose dbclose = new DBClose();
		
		String sql = "select * from roomApplyAssign where 1=1 ";
		if (useDept != -1) {
			sql += " and useDept=" + useDept + " ";
		}

		if (null != roomAssignDate)
			if (!roomAssignDate.equals("")) {
				sql += " and  roomassigndate between to_date('" + roomAssignDate
						+ "','yyyy-mm-dd') and to_date('"+s+"','yyyy-mm-dd')";
			}
		sql+=" order by AppDate";
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
//			rs.last();
//			totalRecord = rs.getRow();
//
//			int startRow = (page - 1) * record_per_page + 1;
//
//			rs.absolute(startRow);
//
//			int i = 0;

			while (rs.next()) {
				raa = new RoomApplyAssign();
				raa.setRoomAppId(rs.getInt("roomAppId"));
				raa.setAppBy(rs.getString("appBy"));
				raa.setAppDate(rs.getDate("appDate"));
				raa.setAppDes(rs.getString("appDes"));
				raa.setAssiType(rs.getInt("assiType"));
				raa.setRoomNu(rs.getString("roomNu"));
				raa.setBuildingNu(rs.getString("buildingNu"));
				raa.setRoomAssignDate(rs.getDate("roomAssignDate"));
				raa.setUseDate(rs.getDate("useDate"));
				raa.setAssiIs(rs.getInt("assiIs"));
				raa.setRoomAssignExcuter(rs.getString("roomAssignExcuter"));
				raa.setUseDept(rs.getInt("useDept"));
				raa.setRoomUser(rs.getString("roomUser"));
				raa.setAssiBy(rs.getString("assiBy"));
				raa.setAssiDesc(rs.getString("assiDesc"));
				raa.setReMark(rs.getInt("reMark"));
				raa.setMemo(rs.getString("memo"));

				list.add(raa);


				
			}

		} catch (SQLException e) {
			
		} finally {
			dbclose.CloseResultSet(rs);
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}

		return list;
	}


}