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

public class RentInfoDAOImp implements RentInfoDAO, PageCount {

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

	public int totalRecord = 0;

	public int record_per_page = 15;

	public boolean add(RentInfo ri) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
    	String s = "全部";
//		if(ri.getRoomNu().equals("s"))
//		{
//			ri.setRoomNu(s);
//		}
//		if(ri.getBuildingNu().equals("s"))
//		{
//			ri.setBuildingNu(s);
//		}
		if (new ExistSql().exist("select * from Room_Info where RoomNu='"
				+ ri.getRoomNu() + "'")) {
			if (new UpdateSql()
					.update("update Room_Info set RoomUseFlag=2 where RoomNu='"
							+ ri.getRoomNu() + "'")) {
				String sql = "insert into Rent_info(RoomApplyId, RentFor, RentType, "
						+ "RoomNu, BuildingNu, AppBy, AppDate, AppDes, RentBeginDate, "
						+ "RentEndDate, RentFee, CheckBy, CheckDes, CheckDate, ReMark, Memo) values(RoomApplyId.nextVal, "
						+ ri.getRentFor()
						+ ", "
						+ ri.getRentType()
						+ ", '"
						+ ri.getRoomNu()
						+ "', '"
						+ ri.getBuildingNu()
						+ "', '"
						+ ri.getAppBy()
						+ "', to_date('"
						+ ri.getAppDate()
						+ "','yyyy-mm-dd'), '"
						+ ri.getAppDes()
						+ "', to_date('"
						+ ri.getRentBeginDate()
						+ "','yyyy-mm-dd'), to_date('"
						+ ri.getRentEndDate()
						+ "','yyyy-mm-dd'), "
						+ ri.getRentFee()
						+ ",'"
						+ ri.getCheckBy()
						+ "', '"
						+ ri.getCheckDes()
						+ "', to_date('"
						+ ri.getCheckDate()
						+ "','yyyy-mm-dd'), "
						+ ri.getReMark()
						+ ", '"
						+ ri.getMemo() + "')";
                
				dbpool.getConnection();
				try {
					stmt = dbpool.conn.createStatement();

					int i = stmt.executeUpdate(sql);

					flag = i > 0 ? true : false;
				} catch (SQLException e) {
					
				} finally {
					dbclose.CloseStmt(stmt);
					dbpool.freeConnection();
				}
			}
		}else if(ri.getRoomNu().equals("s"))
		{
			ri.setRoomNu(s);
			String sql = "insert into Rent_info(RoomApplyId, RentFor, RentType, "
				+ "RoomNu, BuildingNu, AppBy, AppDate, AppDes, RentBeginDate, "
				+ "RentEndDate, RentFee, CheckBy, CheckDes, CheckDate, ReMark, Memo) values(RoomApplyId.nextVal, "
				+ ri.getRentFor()
				+ ", "
				+ ri.getRentType()
				+ ", '"
				+ ri.getRoomNu()
				+ "', '"
				+ ri.getBuildingNu()
				+ "', '"
				+ ri.getAppBy()
				+ "', to_date('"
				+ ri.getAppDate()
				+ "','yyyy-mm-dd'), '"
				+ ri.getAppDes()
				+ "', to_date('"
				+ ri.getRentBeginDate()
				+ "','yyyy-mm-dd'), to_date('"
				+ ri.getRentEndDate()
				+ "','yyyy-mm-dd'), "
				+ ri.getRentFee()
				+ ",'"
				+ ri.getCheckBy()
				+ "', '"
				+ ri.getCheckDes()
				+ "', to_date('"
				+ ri.getCheckDate()
				+ "','yyyy-mm-dd'), "
				+ ri.getReMark()
				+ ", '"
				+ ri.getMemo() + "')";
        
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();

			int i = stmt.executeUpdate(sql);

			flag = i > 0 ? true : false;
		} catch (SQLException e) {
			
		} finally {
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
	}
		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 Rent_info where RoomApplyId=" + id + " ";
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();

			int i = stmt.executeUpdate(sql);

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

	public List getSelect(int RoomApplyId, String rentBeginDate,
			String rentEndDate) {
		// TODO Auto-generated method stub
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RentInfo ri = null;
		DBClose dbclose = new DBClose();
		List L_RentInfo = new ArrayList();
		String sql = "select * from Rent_info where 1=1";
		if (RoomApplyId != -1) {
			sql += " and RoomNo = " + RoomApplyId + "";
		}
		if (rentBeginDate.length() != 0) {
			sql += " and rentBeginDate=to_date('" + rentBeginDate
					+ "','yyyy-mm-dd')";
		}
		if (rentEndDate.length() != 0) {
			sql += " and rentEndDate=to_date('" + rentEndDate
					+ "','yyyy-mm-dd') ";
		}
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				ri = new RentInfo();

				ri.setRentFor(rs.getInt("rentFor"));
				ri.setRentType(rs.getInt("rentType"));
				ri.setRoomNu(rs.getString("roomNu"));
				ri.setBuildingNu(rs.getString("buildingNu"));
				ri.setAppBy(rs.getString("appBy"));
				ri.setAppDate(rs.getDate("appDate"));
				ri.setAppDes(rs.getString("appDes"));
				ri.setRentBeginDate(rs.getDate("rentBeginDate"));
				ri.setRentEndDate(rs.getDate("rentEndDate"));
				ri.setRentFee(rs.getFloat("rentFee"));
				ri.setCheckBy(rs.getString("checkBy"));
				ri.setCheckDes(rs.getString("checkDes"));
				ri.setCheckDate(rs.getDate("checkDate"));
				ri.setReMark(rs.getInt("reMark"));
				ri.setMemo(rs.getString("memo"));

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

	public boolean udpate(int id) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		RentInfo ri = new RentInfo();
		String sql = "update Rent_info set RentFor=" + ri.getRentFor()
				+ ", RentType=" + ri.getRentType() + ", " + "RoomNu='"
				+ ri.getRoomNu() + "', BuildingNu='" + ri.getBuildingNu()
				+ "', AppBy='" + ri.getAppBy() + "', AppDate='"
				+ ri.getAppDate() + "', AppDes='" + ri.getAppDes()
				+ "', RentBeginDate='" + ri.getRentBeginDate() + "', "
				+ "RentEndDate='" + ri.getRentEndDate() + "', RentFee="
				+ ri.getRentFee() + ", CheckBy='" + ri.getCheckBy()
				+ "', CheckDes='" + ri.getCheckDes() + "', CheckDate='"
				+ ri.getCheckDate() + "', ReMark=" + ri.getReMark()
				+ ", Memo='" + ri.getMemo() + "' where RoomApplyId=" + id + " ";
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();

			int i = stmt.executeUpdate(sql);

			flag = i > 0 ? true : false;
		} 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 pstmt = null;
		ResultSet rs = null;
		RentInfo ri = null;
		DBClose dbclose = new DBClose();
		String sql = "select * from rent_info";
		dbpool.getConnection();
		try {
			pstmt = dbpool.conn
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = pstmt.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()) {
				ri = new RentInfo();
				ri.setRoomApplyId(rs.getInt("roomApplyId"));
				ri.setRentFor(rs.getInt("rentFor"));
				ri.setRentType(rs.getInt("rentType"));
				ri.setRoomNu(rs.getString("roomNu"));
				ri.setBuildingNu(rs.getString("buildingNu"));
				ri.setAppBy(rs.getString("appBy"));
				ri.setAppDate(rs.getDate("appDate"));
				ri.setAppDes(rs.getString("appDes"));
				ri.setRentBeginDate(rs.getDate("rentBeginDate"));
				ri.setRentEndDate(rs.getDate("rentEndDate"));
				ri.setRentFee(rs.getFloat("rentFee"));
				ri.setCheckBy(rs.getString("checkBy"));
				ri.setCheckDes(rs.getString("checkDes"));
				ri.setCheckDate(rs.getDate("checkDate"));
				ri.setReMark(rs.getInt("reMark"));
				ri.setMemo(rs.getString("memo"));

				list.add(ri);

				i++;

				rs.next();
			}

		} catch (SQLException e) {
			
		} finally {

		}
		return list;
	}

	public List pageCount(int page, String roomNu, String rentBeginDate,
			String rentEndDate) {
		List list = new ArrayList();
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		RentInfo ri = null;
		DBClose dbclose = new DBClose();
		String sql = "select * from rent_info where 1=1";
		if (!roomNu.equals("-1")) {
			sql+=" and BuildingNu='"+roomNu+"' ";
		}
//		if(!rentBeginDate.equals("")){	
//			if(!rentEndDate.equals(""))
//			{
//				sql+=" and rentBeginDate between to_date('"+rentBeginDate+"', 'yyyy-mm-dd') and to_date('"+rentEndDate+"','yyyy-mm-dd')";
//			}
//			if(rentEndDate.equals(""))
//			{
//				sql+=" and rentBeginDate=to_date('"+rentBeginDate+"', 'yyyy-mm-dd')";
//			}
//		}
//		if(!rentEndDate.equals(""))
//		{
//			if(!rentBeginDate.equals(""))
//			{
//				sql+=" and rentBeginDate between to_date('"+rentBeginDate+"', 'yyyy-mm-dd') and to_date('"+rentEndDate+"','yyyy-mm-dd')";
//			}
//			if(rentBeginDate.equals(""))
//			{
//				sql+=" and rentEndDate=to_date('"+rentEndDate+"', 'yyyy-mm-dd')";
//			}
//		}
		if(!rentBeginDate.equals("")){
		sql +=	" and (rentBeginDate > to_date('"+rentBeginDate+"', 'yyyy-mm-dd') or rentBeginDate = to_date('"+rentBeginDate+"', 'yyyy-mm-dd'))";
		}
		if(!rentEndDate.equals(""))
		{
			sql+=" and (rentEndDate <= to_date('"+rentEndDate+"', 'yyyy-mm-dd') or rentEndDate <= to_date('"+rentEndDate+"', 'yyyy-mm-dd'))";
		}			
		sql+=" order by AppDate";
		dbpool.getConnection();
		try {
			pstmt = dbpool.conn
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = pstmt.executeQuery(sql);
//			rs.last();
//			totalRecord = rs.getRow();
//
//			int startRow = (page - 1) * record_per_page + 1;
//
//			rs.absolute(startRow);
//
//			int i = 0;

			while (rs.next()) {
				ri = new RentInfo();
				ri.setRoomApplyId(rs.getInt("roomApplyId"));
				ri.setRentFor(rs.getInt("rentFor"));
				ri.setRentType(rs.getInt("rentType"));
				ri.setRoomNu(rs.getString("roomNu"));
				ri.setBuildingNu(rs.getString("buildingNu"));
				ri.setAppBy(rs.getString("appBy"));
				ri.setAppDate(rs.getDate("appDate"));
				ri.setAppDes(rs.getString("appDes"));
				ri.setRentBeginDate(rs.getDate("rentBeginDate"));
				ri.setRentEndDate(rs.getDate("rentEndDate"));
				ri.setRentFee(rs.getFloat("rentFee"));
				ri.setCheckBy(rs.getString("checkBy"));
				ri.setCheckDes(rs.getString("checkDes"));
				ri.setCheckDate(rs.getDate("checkDate"));
				ri.setReMark(rs.getInt("reMark"));
				ri.setMemo(rs.getString("memo"));

				list.add(ri);

				
			}

		} catch (SQLException e) {
			
		} finally {

		}
		return list;
	}

	public int getAllRecords() {
		return totalRecord;
	}

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

}