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.RoomRepairApply;
import imis_hous.com.dao.RoomRepairApplyDAO;
import cmis.database.DBPoolManager;
import imis_hous.com.pub.interfaces.PageCount;
import imis_hous.com.db.DBClose;

public class RoomRepairApplyDAOImp implements RoomRepairApplyDAO, PageCount {

	public RoomRepairApplyDAOImp() {
		// 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(RoomRepairApply rra) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		String s = "全部";
		DBClose dbclose = new DBClose();
//		if (new ExistSql().exist("select * from Room_Info where RoomNu='"
//				+ rra.getRoomNo() + "'")) {
		if(rra.getRoomNo().equals("s"))
		{
			rra.setRoomNo(s);
		}
			String sql = "insert into RoomRepairApply(RoomRepairApplyId, RoomRepairApplyUser, RoomRepairReq, "
					+ "RoomRepaireApplyDate, RoomRepairLeader, RoomRepairAgreenDate, HouseIs, RoomNo, "
					+ "RoomRepairWorker, RoomRepairResult, MainFee, RoomRepairOkDate, ReMark,buildingnu) values(RoomRepairApplyId.nextVal,'"
					+ rra.getRoomRepairApplyUser()
					+ "', '"
					+ rra.getRoomRepairReq()
					+ "', "
					+ "to_date('"
					+ rra.getRoomRepaireApplyDate()
					+ "','yyyy-mm-dd'), '"
					+ rra.getRoomRepairLeader()
					+ "', to_date('"
					+ rra.getRoomRepairAgreenDate()
					+ "','yyyy-mm-dd'), "
					+ rra.getHouseIs()
					+ ", '"+rra.getRoomNo()+"', "
					+ "'"
					+ rra.getRoomRepairWorker()
					+ "', '"
					+ rra.getRoomRepairResult()
					+ "', "
					+ rra.getMainFee()
					+ ", to_date('"
					+ rra.getRoomRepairOkDate()
					+ "','yyyy-mm-dd'), "
					+ rra.getReMark()  + ", '"
					+ rra.getBuildingNu()+"')";
			
			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

			} 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 RoomRepairApply where RoomRepairApplyId="
				+ 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 RoomRepairApplyId, String roomRepaireApplyDate,
			String roomRepairOkDate) {
		// TODO Auto-generated method stub
		
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RoomRepairApply rra = null;
		DBClose dbclose = new DBClose();
		List L_RoomRepairApply = new ArrayList();
		String sql = "select * from RoomRepairApply where 1=1";
		if (RoomRepairApplyId != -1) {
			sql += " and RoomRepairApplyId=" + RoomRepairApplyId + " ";
		}
		if (roomRepaireApplyDate.length() != 0
				&& roomRepairOkDate.length() != 0) {
			sql += " and roomRepaireApplyDate between to_date('"
					+ roomRepaireApplyDate + "','yyyy-mm-dd') and to_date('"
					+ roomRepairOkDate + "', 'yyyy-mm-dd')";
		}
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				rra = new RoomRepairApply();
				rra.setBuildingNu(rs.getString("buildingnu"));
				rra.setRoomRepairApplyUser(rs.getString("roomRepairApplyUser"));
				rra.setRoomRepaireApplyDate(rs
						.getDate("roomRepaireApplyDate"));
				rra.setRoomRepairLeader(rs.getString("roomRepairLeader"));
				rra.setRoomRepairAgreenDate(rs
						.getDate("roomRepairAgreenDate"));
				rra.setHouseIs(rs.getInt("houseIs"));
				rra.setRoomNo(rs.getString("roomNo"));
				rra.setRoomRepairWorker(rs.getString("roomRepairWorker"));
				rra.setRoomRepairResult(rs.getString("roomRepairResult"));
				rra.setMainFee(rs.getFloat("mainFee"));
				rra.setRoomRepairOkDate(rs.getDate("roomRepairOkDate"));
				rra.setReMark(rs.getInt("reMark"));
				L_RoomRepairApply.add(rra);
			}
		} catch (SQLException e) {
			
		} finally {
			dbclose.CloseResultSet(rs);
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}

		return L_RoomRepairApply;
	}

	public boolean update(int id) {
	
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		RoomRepairApply rra = new RoomRepairApply();
		String sql = "update RoomRepairApply set RoomRepairApplyUser='"
				+ rra.getRoomRepairApplyUser() + "', RoomRepairReq='"
				+ rra.getRoomRepairReq() + "', " + "RoomRepaireApplyDate='"
				+ rra.getRoomRepaireApplyDate() + "', RoomRepairLeader='"
				+ rra.getRoomRepairLeader() + "', RoomRepairAgreenDate='"
				+ rra.getRoomRepairAgreenDate() + "', HouseIs="
				+ rra.getHouseIs() + ", RoomNo='" + rra.getRoomNo() + "', "
				+ "RoomRepairWorker='" + rra.getRoomRepairWorker()
				+ "', RoomRepairResult='" + rra.getRoomRepairResult()
				+ "', MainFee=" + rra.getMainFee() + ", RoomRepairOkDate='"
				+ rra.getRoomRepairOkDate() + "', ReMark=" + rra.getReMark()
				+ " where RoomRepairApplyId=" + 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;
		RoomRepairApply rra = null;
		DBClose dbclose = new DBClose();
		String sql = "select * from roomRepairApply ";
		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()) {
				rra = new RoomRepairApply();
				rra.setBuildingNu(rs.getString("buildingnu"));
				rra.setRoomRepairApplyId(rs.getInt("roomRepairApplyId"));
				rra.setRoomRepairApplyUser(rs.getString("roomRepairApplyUser"));
				rra.setRoomRepaireApplyDate(rs
						.getDate("roomRepaireApplyDate"));
				rra.setRoomRepairLeader(rs.getString("roomRepairLeader"));
				rra.setRoomRepairAgreenDate(rs
						.getDate("roomRepairAgreenDate"));
				rra.setHouseIs(rs.getInt("houseIs"));
				rra.setRoomNo(rs.getString("roomNo"));
				rra.setRoomRepairWorker(rs.getString("roomRepairWorker"));
				rra.setRoomRepairResult(rs.getString("roomRepairResult"));
				rra.setMainFee(rs.getFloat("mainFee"));
				rra.setRoomRepairOkDate(rs.getDate("roomRepairOkDate")
						);
				rra.setReMark(rs.getInt("reMark"));

				list.add(rra);

				i++;

				rs.next();
			}

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

	public List pageCount(int page, String roomNo, String roomRepaireApplyDate,
			String roomRepairOkDate) {
		List list = new ArrayList();
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RoomRepairApply rra = null;
		DBClose dbclose = new DBClose();
		String sql = "select * from roomRepairApply where 1=1";
		
		if (!roomRepaireApplyDate.equals("")) {
			if (!roomRepaireApplyDate.equals("")) {
				sql += " and roomRepaireApplyDate between to_date('"
						+ roomRepaireApplyDate
						+ "', 'yyyy-mm-dd') and to_date('" + roomRepairOkDate
						+ "','yyyy-mm-dd')";
			}
		}
//			if (roomRepairOkDate.equals("")) {
//				sql += " and roomRepaireApplyDate=to_date('"
//						+ roomRepaireApplyDate + "', 'yyyy-mm-dd')";
//			}
//		}
//		if (!roomRepairOkDate.equals("")) {
//			if (!roomRepaireApplyDate.equals("")) {
//				sql += " and roomRepaireApplyDate between to_date('"
//						+ roomRepaireApplyDate
//						+ "', 'yyyy-mm-dd') and to_date('" + roomRepairOkDate
//						+ "','yyyy-mm-dd')";
//			}
//			if (roomRepaireApplyDate.equals("")) {
//				sql += " and roomRepairOkDate=to_date('" + roomRepairOkDate
//						+ "', 'yyyy-mm-dd')";
//			}
//		}
		sql+=" order by roomRepaireApplyDate";
		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()) {
				while (rs.next()) {
				rra = new RoomRepairApply();
				rra.setBuildingNu(rs.getString("buildingnu"));
				rra.setRoomRepairApplyId(rs.getInt("roomRepairApplyId"));
				rra.setRoomRepairApplyUser(rs.getString("roomRepairApplyUser"));
				rra.setRoomRepaireApplyDate(rs
						.getDate("roomRepaireApplyDate"));
				rra.setRoomRepairReq(rs.getString("roomRepairReq"));
				rra.setRoomRepairLeader(rs.getString("roomRepairLeader"));

				rra.setRoomRepairAgreenDate(rs.getDate("roomRepairAgreenDate"));
				rra.setHouseIs(rs.getInt("houseIs"));
				rra.setRoomNo(rs.getString("roomNo"));
				rra.setRoomRepairWorker(rs.getString("roomRepairWorker"));
				rra.setRoomRepairResult(rs.getString("roomRepairResult"));
				rra.setMainFee(rs.getFloat("mainFee"));
				rra.setRoomRepairOkDate(rs.getDate("roomRepairOkDate")
						);
				rra.setReMark(rs.getInt("reMark"));

				list.add(rra);


				
			}

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

}