package Dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//import java.sql.Date;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;

import Entities.ImportMessage;
import Utils.ExcelUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import Entities.HouseEntity;
import Entities.PageEntity;
import Utils.DBUtil;
import Utils.UUIDUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

//import org.springframework.web.multipart.MultipartFile;

/**对房源信息进行操作的方法类
 * 
 * @author wentao
 */
public class HouseDao implements Serializable {
	Logger log= Logger.getLogger("houseCaoZuo-log");
	/**
	 * 小区名称正则
	 */
	private static final String CODE_HOUSE_NAME = "^[\\u4e00-\\u9fa5]{2,33}$";
	/**
	 * 栋号正则
	 */
	private static final String CODE_BUILDNUMBER = "^.{0,10}$";
	/**
	 * 房号正则
	 */
	private static final String CODE_ROOMNUMBER = "^.{0,10}$";
	/**
	 * 户型正则
	 */
	private static final String CODE_HOUSETYPE = "^[a-zA-Z0-9_\\u4e00-\\u9fa5]+$";
	/**
	 * 面积正则
	 */
	private static final String CODE_AREA = "^.{0,20}$";
	/**
	 * 价格正则
	 */
	private static final String CODE_PRICE = "^[0-9]+(.[0-9]{1,3})?$";
	/**
	 * 建成年限正则
	 */
	private static final String CODE_COMPLETEYEAR = "([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8])))";
	/**
	 * 手机号正则
	 */
	private static final String CODE_PHONE = "^.{0,50}$";
	/**
	 * 备注正则
	 */
	private static final String CODE_REMARKS = "^.{0,50}$";


	
	
	/**去重查询小区名称，用于筛选
	 * @author wentao
	 * @return
	 */
	public List<String> queryHouseName(){
		 List<String> house_names= new ArrayList<String>();
	        Connection conn=null;
			try {
				 conn=DBUtil.getConnection();
				 conn.setAutoCommit(false);
				String sql="SELECT DISTINCT HOUSE_NAME FROM HOUSE WHERE DELETE_FLAG='0' ORDER BY HOUSE_ID";
				PreparedStatement smt=conn.prepareStatement(sql);
				ResultSet rs=smt.executeQuery();
				conn.commit();
				while(rs.next()) {		
					house_names.add(rs.getString("house_name"));
				}
				return house_names;
			} catch (SQLException e) {
				log.error("去重查询房源小区名称信息失败",e);
				DBUtil.rollBack(conn);
				e.printStackTrace();
				return house_names;
				//throw new RuntimeException("分页查询房源信息失败",e);
			}finally {
				DBUtil.closeConnection(conn);
			}
	}
	
	/**去重查询房源栋号信息，用于筛选
	 * @author wentao
	 * @return
	 */
	public List<String> queryBuildNumber(){
		 List<String> buildNumbers= new ArrayList<String>();
	        Connection conn=null;
			try {
				 conn=DBUtil.getConnection();
				 conn.setAutoCommit(false);
				String sql="SELECT DISTINCT BUILD_NUMBER FROM HOUSE WHERE DELETE_FLAG='0' ORDER BY HOUSE_ID";
				PreparedStatement smt=conn.prepareStatement(sql);
				ResultSet rs=smt.executeQuery();
				conn.commit();
				while(rs.next()) {		
					buildNumbers.add(rs.getString("build_number"));
				}
				return buildNumbers;
			} catch (SQLException e) {
				log.error("去重查询房源栋号信息失败",e);
				DBUtil.rollBack(conn);
				e.printStackTrace();
				return buildNumbers;
				//throw new RuntimeException("分页查询房源信息失败",e);
			}finally {
				DBUtil.closeConnection(conn);
			}
	}
	
	/**去重查询房源户型信息，用于筛选
	 * @author wentao
	 * @return
	 */
	public List<String> queryHouseType(){
		 List<String> houseTypes= new ArrayList<String>();
	        Connection conn=null;
			try {
				 conn=DBUtil.getConnection();
				 conn.setAutoCommit(false);
				String sql="SELECT DISTINCT HOUSE_TYPE FROM HOUSE WHERE DELETE_FLAG='0' ORDER BY HOUSE_ID";
				PreparedStatement smt=conn.prepareStatement(sql);
				ResultSet rs=smt.executeQuery();
				conn.commit();
				while(rs.next()) {		
					houseTypes.add(rs.getString("house_type"));
				}
				return houseTypes;
			} catch (SQLException e) {
				log.error("去重查询房源户型信息失败",e);
				DBUtil.rollBack(conn);
				e.printStackTrace();
				return houseTypes;
				//throw new RuntimeException("分页查询房源信息失败",e);
			}finally {
				DBUtil.closeConnection(conn);
			}
	}
	
	/**去重查询房源装修程度信息，用于筛选
	 * @author wentao
	 * @return
	 */
	public List<String> queryRoomStatus(){
		 List<String> roomStatus= new ArrayList<String>();
	        Connection conn=null;
			try {
				 conn=DBUtil.getConnection();
				 conn.setAutoCommit(false);
				String sql="SELECT DISTINCT ROOM_STATUS FROM HOUSE WHERE DELETE_FLAG='0' ORDER BY HOUSE_ID";
				PreparedStatement smt=conn.prepareStatement(sql);
				ResultSet rs=smt.executeQuery();
				conn.commit();
				while(rs.next()) {		
					roomStatus.add(rs.getString("room_status"));
				}
				return roomStatus;
			} catch (SQLException e) {
				log.error("去重查询房源装修程度信息失败",e);
				DBUtil.rollBack(conn);
				e.printStackTrace();
				return roomStatus;
				//throw new RuntimeException("分页查询房源信息失败",e);
			}finally {
				DBUtil.closeConnection(conn);
			}
	}
	
	
	/**分页查询房源信息
	 * @author wentao
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	public List<HouseEntity> queryCurrentData(Integer currentPage,Integer pageSize,String queryCurrentDataSql){
		//计算查询的起始行  
        int startNo = (currentPage-1)*pageSize; 
        List<HouseEntity> houses= new ArrayList<HouseEntity>();
        Connection conn=null;
		try {
			 conn=DBUtil.getConnection();
			 conn.setAutoCommit(false);
			PreparedStatement smt=conn.prepareStatement(queryCurrentDataSql);
			smt.setInt(1,startNo);
			smt.setInt(2, pageSize);
			ResultSet rs=smt.executeQuery();
			conn.commit();
			while(rs.next()) {
				HouseEntity house=new HouseEntity();
				house.setHouseID(rs.getObject("house_id"));
				house.setHouseName(rs.getString("house_name"));
				house.setBuildNumber(rs.getString("build_number"));
				house.setRoomNumber(rs.getString("room_number"));
				house.setHouseType(rs.getString("house_type"));
				house.setArea(rs.getString("area"));
				house.setPrice(rs.getFloat("price"));
				house.setRoomStatus(rs.getInt("room_status"));
				house.setCompleteYear(rs.getDate("complete_year"));
				house.setPhone(rs.getString("phone"));
				house.setOrderName(rs.getString("order_name"));
				house.setRemarks(rs.getString("remarks"));
				houses.add(house);
			}
			return houses;
		} catch (SQLException e) {
			log.error("分页查询房源信息失败",e);
			DBUtil.rollBack(conn);
			e.printStackTrace();
			return houses;
			//throw new RuntimeException("分页查询房源信息失败",e);
		}finally {
			DBUtil.closeConnection(conn);
		}
	}
	
	/**提供查询总记录数的方法 
	 * @author wentao
	 * @return
	 */
	public Integer queryTotalCount(String queryTotalCountSql){ 
		Integer count=0;
		Connection conn=null;
		try {
			 conn=DBUtil.getConnection();
			 conn.setAutoCommit(false);
			PreparedStatement smt=conn.prepareStatement(queryTotalCountSql);
			ResultSet rs=smt.executeQuery();
			conn.commit();
			while(rs.next()) {
				count=rs.getInt(1);
			}
			//System.out.println(count);
			return count;
		} catch (SQLException e) {
			log.error("查询房源全部信息失败",e);
			DBUtil.rollBack(conn);
			e.printStackTrace();
			return count;
			//throw new RuntimeException("查询房源全部信息失败",e);
		}finally {
			DBUtil.closeConnection(conn);
		}
	}
	
	 /**向页面返回查询的结果实体
	 * @author wentao
	 * @param 
	 * @throws 
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	public PageEntity queryPageEntity(Integer currentPage,Integer pageSize,String queryTotalCountSql,String queryCurrentDataSql){  
	        //封装PageBean分页对象数据  
		 PageEntity pageEntity = new PageEntity();     
	        //设置当前页  
		 pageEntity.setCurrentPage(currentPage);  
	        //设置每页显示的记录数  
		 pageEntity.setPageSize(pageSize);   
	        HouseDao houseDao = new HouseDao();  
	        /** 
	         * 从数据库中查询出总记录数 
	         */  
	        int totalCount = houseDao.queryTotalCount(queryTotalCountSql);  
	        //设置总记录数  
	        pageEntity.setTotalCount(totalCount);      
	        //设置当前页的数据  
	        /** 
	         * 从数据库中查询出当前页的房源数据 
	         */  
	        List<HouseEntity> list = houseDao.queryCurrentData(pageEntity.getCurrentPage(), pageEntity.getPageSize(),queryCurrentDataSql);  
	        pageEntity.setData(list);      
	        return pageEntity;  
	    } 
	
	/**根据houseid查询房源信息
	 * @author wentao
	 * @param house_id
	 * @return
	 */
	public HouseEntity queryHouseByID(String house_id){
    	HouseEntity house=new HouseEntity();
        Connection conn=null;
		try {
			 conn=DBUtil.getConnection();
			 conn.setAutoCommit(false);
			 //delete_flag:删除标记 0：未删除 1：已删除
			String sql="SELECT * FROM HOUSE WHERE HOUSE_ID=?";
			PreparedStatement smt=conn.prepareStatement(sql);
			smt.setString(1,house_id);
			ResultSet rs=smt.executeQuery();
			conn.commit();
			while(rs.next()) {
				house.setHouseID(rs.getObject("house_id"));
				house.setHouseName(rs.getString("house_name"));
				house.setBuildNumber(rs.getString("build_number"));
				house.setRoomNumber(rs.getString("room_number"));
				house.setHouseType(rs.getString("house_type"));
				house.setArea(rs.getString("area"));
				house.setPrice(rs.getFloat("price"));
				house.setRoomStatus(rs.getInt("room_status"));
				house.setCompleteYear(rs.getDate("complete_year"));
				house.setPhone(rs.getString("phone"));
				house.setOrderName(rs.getString("order_name"));
				house.setRemarks(rs.getString("remarks"));
			}
			return house;
		} catch (SQLException e) {
			log.error("根据UUID查询房源信息失败",e);
			DBUtil.rollBack(conn);
			e.printStackTrace();
			return house;
			//throw new RuntimeException("分页查询房源信息失败",e);
		}finally {
			DBUtil.closeConnection(conn);
		}
	}

	/**
	 *查询房源信息是否存在
	 *
	 *
	 * @param houseName
	 * @param buildNumber
	 * @param roomNumber
	 * @return
	 * @author wentao
	 * @time 2018/2/20
	 */
	public List<HouseEntity> checkHouse(String houseName,String buildNumber,String roomNumber){

		List<HouseEntity> houseList=new ArrayList<HouseEntity>();
		//List<List<HouseEntity>> resultList=new ArrayList<List<HouseEntity>>();
		Connection conn=null;
		try {
			conn=DBUtil.getConnection();
			conn.setAutoCommit(false);
			//delete_flag:删除标记 0：未删除 1：已删除
			String sql="SELECT * FROM HOUSE WHERE HOUSE_NAME=? AND BUILD_NUMBER=? AND ROOM_NUMBER=? AND " +
					"DELETE_FLAG=0";
			PreparedStatement smt=conn.prepareStatement(sql);
			smt.setString(1,houseName);
			smt.setString(2,buildNumber);
			smt.setString(3,roomNumber);
			ResultSet rs=smt.executeQuery();
			conn.commit();
			while(rs.next()) {
				HouseEntity house=new HouseEntity();
				house.setHouseID(rs.getObject("house_id"));
				house.setHouseName(rs.getString("house_name"));
				house.setBuildNumber(rs.getString("build_number"));
				house.setRoomNumber(rs.getString("room_number"));
				houseList.add(house);
			}
			return houseList;
		} catch (SQLException e) {
			log.error("查询房源信息是否存在失败",e);
			DBUtil.rollBack(conn);
			e.printStackTrace();
			HouseEntity house=new HouseEntity();
			house.setHouseName("温涛");
			houseList.add(house);
			return houseList;
		}finally {
			DBUtil.closeConnection(conn);
		}

	}
	/**编辑时查询房源信息是否存在
	 *
	 * @param houseName
	 * @param buildNumber
	 * @param roomNumber
	 * @param houseID
	 * @return
	 * @author wentao
	 * @time 2018/2/22 0022
	 */

	public List<HouseEntity> checkHouse(String houseName,String buildNumber,String roomNumber,String houseID){
		List<HouseEntity> houseList=new ArrayList<HouseEntity>();
		String house_ID=UUIDUtils.addSepc(houseID);
		Connection conn=null;
		try {
			conn=DBUtil.getConnection();
			conn.setAutoCommit(false);
			//delete_flag:删除标记 0：未删除 1：已删除
			String sql="SELECT * FROM HOUSE WHERE HOUSE_NAME=? AND BUILD_NUMBER=? AND ROOM_NUMBER=? AND " +
					"DELETE_FLAG=0 AND HOUSE_ID !=?";
			PreparedStatement smt=conn.prepareStatement(sql);
			smt.setString(1,houseName);
			smt.setString(2,buildNumber);
			smt.setString(3,roomNumber);
			smt.setString(4,house_ID);
			ResultSet rs=smt.executeQuery();
			conn.commit();
			while(rs.next()) {
				HouseEntity house=new HouseEntity();
				house.setHouseID(rs.getObject("house_id"));
				house.setHouseName(rs.getString("house_name"));
				house.setBuildNumber(rs.getString("build_number"));
				house.setRoomNumber(rs.getString("room_number"));
				houseList.add(house);
			}
			return houseList;
		} catch (SQLException e) {
			log.error("查询房源信息是否存在失败",e);
			DBUtil.rollBack(conn);
			e.printStackTrace();
			HouseEntity house=new HouseEntity();
			house.setHouseName("温涛");
			houseList.add(house);
			return houseList;
		}finally {
			DBUtil.closeConnection(conn);
		}

	}
	/**删除房源信息
	 * @author wentao
	 * @param houseID
	 * @param userID
	 * @return
	 */
	public boolean delHouse(String houseID,String userID){
		Date deletime=new Date();
		SimpleDateFormat time = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
		Connection conn=null;
		try {
			conn=DBUtil.getConnection();
			conn.setAutoCommit(false);
			String sql="update house set delete_flag='1',deleter_id=?,deletime=? where house_id=?";
			PreparedStatement smt=conn.prepareStatement(sql);
			smt.setString(1,userID);
			smt.setString(2, time.format(deletime));
			smt.setString(3, houseID);
			int delResult=smt.executeUpdate();
			conn.commit();
			if(delResult==1){
				return true;
			}else{
				return false;
			}
		} catch (SQLException e) {
			log.error("删除房源信息失败",e);
			DBUtil.rollBack(conn);
			e.printStackTrace();
			return false;
		}finally {
			DBUtil.closeConnection(conn);
		}
	}
	
	/**编辑房源信息
	 * @author wentao
	 * @param 
	 * @throws 
	 * @param edit_house
	 * @param user_id
	 * @return
	 */
	public boolean editHouse(HouseEntity edit_house,String user_id) {
		Date updatetime=new Date();
		SimpleDateFormat time = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
		 String houseID=UUIDUtils.addSepc(edit_house.getHouseID());
		Connection editHouseConn=null;
		try {
			editHouseConn=DBUtil.getConnection();
			editHouseConn.setAutoCommit(false);
			String editHouse="UPDATE HOUSE SET HOUSE_NAME=?,BUILD_NUMBER=?,ROOM_NUMBER=?,HOUSE_TYPE=?,AREA=?,PRICE=?,"
					+ "ROOM_STATUS=?,COMPLETE_YEAR=?,PHONE=?,REMARKS=?,UPDATER_ID=?,UPDATE_TIME=?"
					+ "WHERE HOUSE_ID=?";
			PreparedStatement smt=editHouseConn.prepareStatement(editHouse);
			smt.setString(1, edit_house.getHouseName());
			smt.setString(2, edit_house.getBuildNumber());
			smt.setString(3, edit_house.getRoomNumber());
			smt.setString(4, edit_house.getHouseType());
			smt.setString(5, edit_house.getArea());
			smt.setDouble(6, edit_house.getPrice());
			smt.setInt(7, edit_house.getRoomStatus());
			smt.setDate(8, edit_house.getCompleteYear());
			smt.setString(9, edit_house.getPhone());
			smt.setString(10, edit_house.getRemarks());
			smt.setString(11, user_id);
			smt.setString(12, time.format(updatetime));
			smt.setString(13, houseID);
			int editResult=smt.executeUpdate();
			editHouseConn.commit();
			if(editResult==1){
				return true;
			}else{
				return false;
			}	
		} catch (SQLException e) {
			DBUtil.rollBack(editHouseConn);
			log.error("修改房源信息失败",e);
			e.printStackTrace();
			return false;
		}finally{
			DBUtil.closeConnection(editHouseConn);
		}	
	}


/**添加房源信息实体方法
 * @author wentao
 * @param add_house
 * @return
 */
public boolean addHouse(HouseEntity add_house,String userID) {
	Connection addHouseConn=null;
	try {
		addHouseConn=DBUtil.getConnection();
		addHouseConn.setAutoCommit(false);
		String addHouse="INSERT INTO HOUSE (HOUSE_ID,HOUSE_NAME,BUILD_NUMBER,ROOM_NUMBER,HOUSE_TYPE,AREA,PRICE,"
				+ "ROOM_STATUS,COMPLETE_YEAR,PHONE,REMARKS,ORDER_NAME,CREATER_ID) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement smt=addHouseConn.prepareStatement(addHouse,new String[]{"house_id"});
		smt.setString(1, UUIDUtils.addSepc(add_house.getHouseID()));
		//System.out.println(add_house.getHouseID());
		smt.setString(2, add_house.getHouseName());
		smt.setString(3, add_house.getBuildNumber());
		smt.setString(4, add_house.getRoomNumber());
		smt.setString(5, add_house.getHouseType());
		smt.setString(6, add_house.getArea());
		smt.setFloat(7, add_house.getPrice());
		smt.setInt(8, add_house.getRoomStatus());
		smt.setDate(9, add_house.getCompleteYear());
		smt.setString(10, add_house.getPhone());
		smt.setString(11, add_house.getRemarks());
		smt.setString(12, add_house.getOrderName());
		smt.setString(13,userID);
		smt.executeUpdate();
		addHouseConn.commit();
		//从smt中获取生成的主键
		//结果集中包含1行1列
		ResultSet rs = smt.getGeneratedKeys();
		if(rs.next()){
			//这种场景下的结果集,只能通过字段的序号获取值.
			String addSuccessID = rs.getString(1);	
			log.info("新增的房源主键id是"+addSuccessID);
			return true;
		}else{
			return false;
		}	
	} catch (SQLException e) {
		DBUtil.rollBack(addHouseConn);
		log.error("新增房源信息失败",e);
		e.printStackTrace();
		return false;
	}finally{
		DBUtil.closeConnection(addHouseConn);
	}	
}

	/**批量添加房源信息实体方法
	 * @author wentao
	 * @param
	 * @return
	 */
	public List<String>  addUploadFile(MultipartFile uploadFile,String userID) {
        Workbook wookbook = ExcelUtil.createWorkbook(uploadFile);
        //ImportMessage listMessage=new ImportMessage();
        List<String> errorList = new ArrayList<String>();
        int sheetNumber = wookbook.getNumberOfSheets();
        if (sheetNumber != 1) {
            errorList.add("该Excel【sheet】数量不正确,请下载【正确模板】进行上传操作");
            return errorList;
        }
        Sheet sheet = wookbook.getSheetAt(0);// 第一个sheet
        int rows = sheet.getPhysicalNumberOfRows();
        if (rows <= 3) {//数据条数校验，第一行为表头，第二行开始为数据
            errorList.add("您上传的表格数据为空!");
            return errorList;
        }
        Row cellsTitle = sheet.getRow(0);
        int cellNumber = cellsTitle.getLastCellNum();
        if (cellNumber != 8) {
            errorList.add("该Excel列数量不正确,请下载【正确模板】进行上传操作");
            return errorList;
        }
        String[] titles = {"小区名称", "栋号", "房号", "户型", "面积", "价格", "装修程度", "建成年限",
                "房主手机号", "备注"};
        List<HouseEntity> houseList = new ArrayList<HouseEntity>();
        //List<HouseEntity> houseList = new ArrayList<HouseEntity>();
        for (int i = 3; i < rows; i++) {//数据校验
            Row row = sheet.getRow(i);
            if (row == null){
                continue;}
            for (int c = 0; c < row.getLastCellNum(); c++) {//设置excel内容为String
                if (row.getCell(c) != null)
                    row.getCell(c).setCellType(Cell.CELL_TYPE_STRING);
            }
            HouseEntity house = new HouseEntity();
            int cellColumnIndex = 0;//从第一列开始读取数据

            // 小区名称列
            String houseName = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
            if (StringUtils.isEmpty(houseName)) {
                errorList.add("第" + (i + 1) + "行小区名称是空的！");
            } else {
                house.setHouseName(houseName);
                if (!house.getHouseName().matches(CODE_HOUSE_NAME)) {
                    errorList.add("第" + (i + 1) + "行小区名称不符合规则！");
                }
            }
            // 栋号列
            String buildNumber = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
            if (StringUtils.isEmpty(buildNumber)) {
                errorList.add("第" + (i + 1) + "行栋号是空的！");
            } else {
				if (!buildNumber.matches(CODE_BUILDNUMBER)) {
					errorList.add("第" + (i + 1) + "行栋号超出字数限制！");
				}else{
				try{
						house.setBuildNumber(buildNumber);

				}catch (NumberFormatException e){
					errorList.add("第" + (i + 1) + "行栋号不符合规则！");
				}}

            }
            // 房号列
            String roomNumber = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
            if (StringUtils.isEmpty(roomNumber)) {
                errorList.add("第" + (i + 1) + "行房号是空的！");
            } else {
				if (!roomNumber.matches(CODE_ROOMNUMBER)) {
					errorList.add("第" + (i + 1) + "行房号超出字数限制！");
				}else{
                	try{
                        house.setRoomNumber(roomNumber);
                	}catch (NumberFormatException e){
                    errorList.add("第" + (i + 1) + "行房号不符合规则！");
                	}}
            }
            // 户型列
            String houseType = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
            if (StringUtils.isEmpty(houseType)) {
                errorList.add("第" + (i + 1) + "行户型是空的！");
            } else {
                house.setHouseType(houseType);
                if (!house.getHouseType().matches(CODE_HOUSETYPE)) {
                    errorList.add("第" + (i + 1) + "行户型不符合规则！");
                }
            }
            // 面积列
            String area = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
            if (StringUtils.isEmpty(area)) {
                errorList.add("第" + (i + 1) + "行面积是空的！");
            } else {
                try{
                    if (!area.matches(CODE_AREA)) {
                        errorList.add("第" + (i + 1) + "行面积不符合规则！");
                    }else{
                        house.setArea(area);
                    }
                }catch (NumberFormatException e){
                    errorList.add("第" + (i + 1) + "行面积不符合规则！");
                }
            }
            // 价格列
            String price = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
            if (StringUtils.isEmpty(price)) {
                errorList.add("第" + (i + 1) + "行价格是空的！");
            } else {
                try{
                    if (!price.matches(CODE_PRICE)) {
                        errorList.add("第" + (i + 1) + "行价格不符合规则！");
                    }else{
                        house.setPrice(Float.valueOf(price));
                    }
                }catch (NumberFormatException e){
                    errorList.add("第" + (i + 1) + "行价格不符合规则！");
                }
            }
            // 装修程度列
            String roomStatus = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
            if (StringUtils.isEmpty(roomStatus)) {
				house.setRoomStatus(5);
            } else {
               Integer status;
               if(roomStatus.equals("毛坯")){
                   status=0;
                   house.setRoomStatus(status);
               }else if(roomStatus.equals("简装")){
                   status=1;
                   house.setRoomStatus(status);
               }else if(roomStatus.equals("中装")){
                   status=2;
                   house.setRoomStatus(status);
               }else if(roomStatus.equals("精装")){
                   status=3;
                   house.setRoomStatus(status);
               }else if(roomStatus.equals("豪华装")){
                   status=4;
                   house.setRoomStatus(status);
               }else{
                   errorList.add("第" + (i + 1) + "行装修程度不是选择的！");
               }
            }

            // 建成年限列


            String completeYear = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
			DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
            if (StringUtils.isEmpty(completeYear)) {
				house.setCompleteYear(null);
            } else {
                try{
                    Double year=Double.parseDouble(completeYear);
					String time=formater.format(HSSFDateUtil.getJavaDate(year));
                    if (!time.matches(CODE_COMPLETEYEAR)) {
                        errorList.add("第" + (i + 1) + "行建成年限不符合规则！");
                    }else{
                        house.setCompleteYear(java.sql.Date.valueOf(time));
                    }
                }catch (NumberFormatException e){
                    errorList.add("第" + (i + 1) + "行建成年限不符合规则！");
                }
            }


            // 房主手机号列
            String phone = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
            if (StringUtils.isEmpty(phone)) {
                errorList.add("第" + (i + 1) + "行房主手机号是空的！");
            } else {
				if (!phone.matches(CODE_PHONE)) {
					errorList.add("第" + (i + 1) + "行手机号超出字数限制！");
				}else{
                house.setPhone(phone);}

            }
            // 备注列
            String remarks = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
            if (!StringUtils.isEmpty(remarks)) {
                house.setRemarks(remarks);
                if (!house.getRemarks().matches(CODE_REMARKS)) {
                    errorList.add("第" + (i + 1) + "行备注不符合规则！");
                }
            }
			if(errorList.size()==0) {
				for (HouseEntity houses : houseList) {
					if (houses.getHouseName().equals(house.getHouseName()) &&
							houses.getRoomNumber().equals(house.getRoomNumber())
							&& houses.getBuildNumber().equals(house.getBuildNumber())) {
						errorList.add("第" + (i + 1) + "行的信息已经在表格里存在！请检查表格内容。");
					}
				}
			}
			houseList.add(house);
        }

		if(errorList.size()==0) {
        	int i=4;
			for (HouseEntity houses : houseList) {
				List<HouseEntity> check = checkHouse(houses.getHouseName(), houses.getBuildNumber(), houses.getRoomNumber());
				if (check.size() > 0) {
					for (HouseEntity h : check) {
						if ("温涛".equals(h.getHouseName())) {
							errorList.add("第" + (i) + "行的信息查询是否存在时出错，请把表格重新上传一次。");
						} else {
							errorList.add("第" + (i) + "行的房源信息已经在系统中存在了！请检查表格内容是否跟系统中的房源信息重复了。");
						}
					}
				}
				i++;
			}
		}
        if (errorList.size() > 0)
            return errorList;
        else {
            Connection addHouseConn=null;
            try {
                addHouseConn = DBUtil.getConnection();
                addHouseConn.setAutoCommit(false);
                String addHouse = "INSERT INTO HOUSE (HOUSE_ID,HOUSE_NAME,BUILD_NUMBER,ROOM_NUMBER,HOUSE_TYPE,AREA,PRICE,"
                        + "ROOM_STATUS,COMPLETE_YEAR,PHONE,REMARKS,ORDER_NAME,CREATER_ID) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
                PreparedStatement smt = addHouseConn.prepareStatement(addHouse);
                List<String> correctList = new ArrayList<String>();
                int i=0;//用作控制多少条数据时往数据库里插
                for (HouseEntity houses : houseList) {

                    smt.setString(1, UUIDUtils.getUUIDStr());
                    smt.setString(2, houses.getHouseName());
                    smt.setString(3, houses.getBuildNumber());
                    smt.setString(4, houses.getRoomNumber());
                    smt.setString(5, houses.getHouseType());
                    smt.setString(6, houses.getArea());
                    smt.setFloat(7, houses.getPrice());
                    smt.setInt(8, houses.getRoomStatus());
                    smt.setDate(9, houses.getCompleteYear());
                    smt.setString(10, houses.getPhone());
                    smt.setString(11, houses.getRemarks());
                    smt.setString(12,"批量上传的房源");
                    smt.setString(13,userID);
                    smt.addBatch();
                    i++;
                    if(i%10==0){
                        smt.executeBatch();
                        //清空暂存的数据，便于下一批
                        smt.clearBatch();
                       // i=0;
                    }
                }
                smt.executeBatch();
                addHouseConn.commit();
                correctList.add("导入成功！");
                return correctList;

            }catch (SQLException e) {
                DBUtil.rollBack(addHouseConn);
                log.error("批量插入数据库失败",e);
                e.printStackTrace();
                errorList.add("批量插入数据库失败");
                return errorList;
            }finally{
                DBUtil.closeConnection(addHouseConn);
            }

        }
    }


}