package cn.com.countrygarden.bi.main.service.erp.impl;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.ServletContext;

import org.apache.commons.net.ntp.TimeStamp;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.com.countrygarden.bi.main.model.erp.CacheEntity;
import cn.com.countrygarden.bi.main.model.erp.HouseOwnerMsg;
import cn.com.countrygarden.bi.main.model.erp.JdbcConstants;
import cn.com.countrygarden.bi.main.model.erp.UniqueBean;
import cn.com.countrygarden.bi.main.repository.erp.JdbcOwnerRepository;
import cn.com.countrygarden.bi.main.repository.erp.JdbcPublicRepository;
import cn.com.countrygarden.bi.main.service.erp.ExcelHouseOwnerService;
import cn.com.countrygarden.bi.main.util.RedisUtils;
import cn.com.countrygarden.bi.main.util.erp.ExcelData;
import cn.com.countrygarden.bi.main.util.erp.ImportExcelUtils;
import cn.com.countrygarden.bi.main.util.erp.StringUtil;

;

/**
 * @Author christ.lai
 * @Description 房屋业主租户资料
 * @Date 14:31 2018/5/20
 */
@Service
@Scope("prototype")
public class ExcelHouseOwnerServiceImpl implements ExcelHouseOwnerService {

    private final Logger logger = LoggerFactory.getLogger(ExcelHouseOwnerServiceImpl.class);

    @Override
    @Transactional(rollbackFor = Exception.class)
    public String getExcel(JdbcConstants jdbcConstants,  ServletContext application) throws Exception {
        logger.info("上传文件开始......");
        ImportExcelUtils excelRead = new ImportExcelUtils();
        //读取excel文件，然后把读出的结果以行的形式存储在List中
        List<ArrayList<String>> arrayLists = excelRead.readExcel(jdbcConstants.getFile(),jdbcConstants.getSheet());
        List<HouseOwnerMsg> arrearageMsgsList = new ArrayList<HouseOwnerMsg>();
        Set<String> fwbhSet = new HashSet<String>(); //保存模板中的房屋编号
        Set<Integer> commidSet = new HashSet<Integer>(); //保存项目编号
        List<UniqueBean> ublist = new ArrayList<UniqueBean>();//保存模板中楼宇名称+单元+楼层+序号的组合
        Map<String,String> ubmap = new HashMap<String,String>();//保存模板中楼宇名称+组团区域,刚好可以利用Map不允许重复的键值对
        boolean flag = true;
        JdbcPublicRepository jpr = new JdbcPublicRepository(jdbcConstants); //校验查询工具类
        int cid = Integer.parseInt(arrayLists.get(0).get(0)); //取第一行的第一个commid为参数
        Set<String> roomSet = null;
        Set<String> custSet = null;
        Set<String> parkSet = null;
        //缓存中不存在，查询数据库，得到要缓存的数据，缓存到redis
//		if(!RedisUtils.exits("ERPdataUpload-roomSign-"+cid)){
			CacheEntity ce = jpr.getCacheData(cid);
			roomSet = ce.getRoomSet();
			custSet = ce.getCustSet();
			parkSet = ce.getParkSet();
			//集合不为空，则缓存到redis
			if(roomSet != null && roomSet.size() != 0){
				for(String value : roomSet){
					RedisUtils.sadd("ERPdataUpload-roomSign-"+cid,value);
				}
			}
			if(custSet != null && custSet.size() != 0){
				for(String value : custSet){
					RedisUtils.sadd("ERPdataUpload-custName-"+cid,value);
				}
			}
			if(parkSet != null && parkSet.size() != 0){
				for(String value : parkSet){
					RedisUtils.sadd("ERPdataUpload-parkName-"+cid,value);
				}
			}
//		}
        for (ArrayList<String> stringArrayList : arrayLists) {
            StringBuilder errorBuilder = new StringBuilder();
            // 字段验证
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(0))){
                errorBuilder.append(" 项目编号为空,");
                flag = false;
            }else{
            	int commid = Integer.parseInt(stringArrayList.get(0));
            	//不存在就去数据库查
            	if(commidSet.contains(commid)){
            		if(!jpr.checkCommid(commid)){
                		errorBuilder.append("项目编号不存在,");
                		flag = false;
                	}else{
                		commidSet.add(commid); //找到之后，添加到commmidSet
                	}
            	}
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(1))) {
                errorBuilder.append(" 组团区域为空,");
                flag = false;
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(2))) {
                errorBuilder.append(" 楼宇名称为空,");
                flag = false;
            }else{
            	String key = stringArrayList.get(2).trim();//楼宇名称
                if(ubmap.containsKey(key)){
                	String value = ubmap.get(key); //根据楼宇名称取得对应组团区域
                	String otherValue = stringArrayList.get(1).trim(); //获得当前的组团区域
                	if(value != null){
                		if(!value.equals(otherValue)){
                			errorBuilder.append("楼宇名称重复,"); //同名的楼宇只能属于一个组团
                			flag = false;
                		}else{
                			ubmap.put(stringArrayList.get(2).trim(), stringArrayList.get(1).trim());
                		}
                	}
                }else{
                	ubmap.put(stringArrayList.get(2).trim(), stringArrayList.get(1).trim());
                }
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(3))) {
                errorBuilder.append(" 房屋编号为空,");
                flag = false;
            }else{
            	if (fwbhSet.contains(stringArrayList.get(3))){
                    errorBuilder.append("房屋编号重复, ");
                    flag = false;
                }else {
                	fwbhSet.add(stringArrayList.get(3));
                	int commid = Integer.parseInt(stringArrayList.get(0));
                	String roomSign = stringArrayList.get(3);
                	if(RedisUtils.sismember("ERPdataUpload-roomSign-"+commid,roomSign)){
                		errorBuilder.append("房屋编号已存在 ,");
	                 	flag = false;
    				}else{
    					RedisUtils.sadd("ERPdataUpload-roomSign-"+commid, roomSign); //缓存中不存在，则存到redis
    				}
                }
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(4))) {
                errorBuilder.append(" 房屋名称为空,");
                flag = false;
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(5))) {
                errorBuilder.append(" 单元为空,");
                flag = false;
            }else{
            	if(!StringUtil.checkNum(stringArrayList.get(5))){
            		errorBuilder.append("单元格式不正确,");
            		flag = false;
            	}
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(6))) {
                errorBuilder.append(" 楼层为空,");
                flag = false;
            }else{
            	if(!StringUtil.checkNum(stringArrayList.get(6))){
            		errorBuilder.append("楼层格式不正确,");
            		flag = false;
            	}
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(7))) {
                errorBuilder.append(" hx为空,");
                flag = false;
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(8))) {
                errorBuilder.append(" 面积类型为空,");
                flag = false;
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(9))) {
                errorBuilder.append(" 建筑面积为空,");
                flag = false;
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(13))) {
                errorBuilder.append(" 产权性质为空,");
                flag = false;
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(14))) {
                errorBuilder.append(" 使用性质为空,");
                flag = false;
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(15))) {
                errorBuilder.append(" 使用状态为空,");
                flag = false;
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(16))) {
                errorBuilder.append(" 房屋状态为空,");
                flag = false;
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(17))) {
                errorBuilder.append(" 房屋类型为空,");
                flag = false;
            }
            if(!StringUtil.isBlankOrEmpty(stringArrayList.get(18))){
            	if(!StringUtil.checkDate(stringArrayList.get(18).trim())){
            		errorBuilder.append("合同交房时间格式不正确,");
                    flag = false;
            	}
            }
            if(!StringUtil.isBlankOrEmpty(stringArrayList.get(19))){
            	if(!StringUtil.checkDate(stringArrayList.get(19).trim())){
            		errorBuilder.append("实际交房时间格式不正确,");
                    flag = false;
            	}
            }
            if(!StringUtil.isBlankOrEmpty(stringArrayList.get(20))){
            	if(!StringUtil.checkDate(stringArrayList.get(20).trim())){
            		errorBuilder.append("物业接管时间格式不正确,");
                    flag = false;
            	}
            }
			if(!StringUtil.isBlankOrEmpty(stringArrayList.get(21))){
				if(!StringUtil.checkDate(stringArrayList.get(21).trim())){
            		errorBuilder.append("开始缴费时间格式不正确,");
                    flag = false;
            	}
			}
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(22))) {
                errorBuilder.append(" 业主名称为空,");
                flag = false;
            }else{
            	if(!StringUtil.checkName(stringArrayList.get(22))){
            		errorBuilder.append("业主名称格式不正确,");
            		flag = false;
            	}else{
            		String custName = stringArrayList.get(22);
            		RedisUtils.sadd("ERPdataUpload-custName-"+cid, custName); //缓存中不存在，则存到redis
            	}
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(26))) {
                errorBuilder.append("证件名称为空,");
                flag = false;
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(27))) {
                errorBuilder.append(" 证件号码为空,");
                flag = false;
            }else{
            	String certificate = stringArrayList.get(26); //证件名称
            	if("身份证".equals(certificate)){ //只要求验证身份证
            		if(!StringUtil.checkPaperCode(stringArrayList.get(27))){
                		errorBuilder.append("身份证号码格式不正确,");
                		flag = false;
                	}
            	}
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(29))) {
                errorBuilder.append(" 移动电话为空,");
                flag = false;
            }else{
            	if(!StringUtil.checkCellphone(stringArrayList.get(29))){
                	errorBuilder.append(" 移动电话格式不正确, ");
                	flag = false;
                }
            }
            //保存 楼宇名称+单元+楼层+序号
            UniqueBean ub = new UniqueBean();
            ub.setDy(stringArrayList.get(5).trim());
            ub.setLc(stringArrayList.get(6).trim());
            ub.setLymc(stringArrayList.get(2).trim());
            ub.setXh(stringArrayList.get(7).trim());
            
            if(ublist.contains(ub)){
            	errorBuilder.append("楼宇名称+单元+楼层+序号的组合重复");
            	flag = false;
            }else{
            	ublist.add(ub);
            }
            if (flag) {
                HouseOwnerMsg houseOwnerMsg = new HouseOwnerMsg();
                houseOwnerMsg.setCommid(Integer.parseInt(stringArrayList.get(0).trim()));
                houseOwnerMsg.setZtqy(stringArrayList.get(1).trim());
                houseOwnerMsg.setLymc(stringArrayList.get(2).trim());
                houseOwnerMsg.setFwbh(stringArrayList.get(3).trim());
                houseOwnerMsg.setFwmc(stringArrayList.get(4).trim());
                houseOwnerMsg.setDy(Integer.parseInt(stringArrayList.get(5).trim()));
                houseOwnerMsg.setLc(Integer.parseInt(stringArrayList.get(6).trim()));
                houseOwnerMsg.setXh(Integer.parseInt(stringArrayList.get(7).trim()));
                houseOwnerMsg.setMjlx(stringArrayList.get(8).trim());
                if("".equals(stringArrayList.get(9).trim())){
                	houseOwnerMsg.setJzmj(null);
                }else{
                	houseOwnerMsg.setJzmj(new BigDecimal(stringArrayList.get(9).trim()));
                }
                if("".equals(stringArrayList.get(10).trim())){
                	houseOwnerMsg.setTnmj(null);
                }else{
                	houseOwnerMsg.setTnmj(new BigDecimal(stringArrayList.get(10).trim()));
                }
                if("".equals(stringArrayList.get(11).trim())){
                	houseOwnerMsg.setGtmj(null);
                }else{
                	houseOwnerMsg.setGtmj(new BigDecimal(stringArrayList.get(11).trim()));
                }
                if("".equals(stringArrayList.get(12).trim())){
                	houseOwnerMsg.setHymj(null);
                }else{
                	 houseOwnerMsg.setHymj(new BigDecimal(stringArrayList.get(12).trim()));
                }
                if("".equals(stringArrayList.get(18).trim())){
                	houseOwnerMsg.setHtjfsj(null);
                }else{
                	houseOwnerMsg.setHtjfsj(new Date(stringArrayList.get(18).trim()));
                }
                if("".equals(stringArrayList.get(19).trim())){
                	houseOwnerMsg.setSjjfsj(null);
                }else{
                	houseOwnerMsg.setSjjfsj(new Date(stringArrayList.get(19).trim()));
                }
                if("".equals(stringArrayList.get(20).trim())){
                	houseOwnerMsg.setWyjgsj(null);
                }else{
                	houseOwnerMsg.setWyjgsj(new Date(stringArrayList.get(20).trim()));
                }
                if("".equals(stringArrayList.get(21).trim())){
                	houseOwnerMsg.setKsjfsj(null);
                }else{
                	houseOwnerMsg.setKsjfsj(new Date(stringArrayList.get(21).trim()));
                }
                if("".equals(stringArrayList.get(25).trim())){
                	houseOwnerMsg.setCsrq(null);
                }else{
                	houseOwnerMsg.setCsrq(new Date(stringArrayList.get(25).trim()));
                }
                houseOwnerMsg.setCqxz(stringArrayList.get(13).trim());
                houseOwnerMsg.setSyxz(stringArrayList.get(14).trim());
                houseOwnerMsg.setSyzt(stringArrayList.get(15).trim());
                houseOwnerMsg.setFwzt(stringArrayList.get(16).trim());
                houseOwnerMsg.setFwlx(stringArrayList.get(17).trim());
                houseOwnerMsg.setYzmc(stringArrayList.get(22).trim());
                houseOwnerMsg.setXb(stringArrayList.get(23).trim());
                houseOwnerMsg.setGj(stringArrayList.get(24).trim());
                houseOwnerMsg.setZjmc(stringArrayList.get(26).trim());
                houseOwnerMsg.setZjhm(stringArrayList.get(27).trim());
                houseOwnerMsg.setGddh(stringArrayList.get(28).trim());
                houseOwnerMsg.setYddh(stringArrayList.get(29).trim());
                houseOwnerMsg.setCzdh(stringArrayList.get(30).trim());
                houseOwnerMsg.setLxdz(stringArrayList.get(31).trim());
                houseOwnerMsg.setYzbm(stringArrayList.get(32).trim());
                houseOwnerMsg.setSjr(stringArrayList.get(33).trim());
                houseOwnerMsg.setDzyj(stringArrayList.get(34).trim());
                arrearageMsgsList.add(houseOwnerMsg);
            }else {
            	stringArrayList.add(errorBuilder.toString().split(",")[0]); //添加 Error Message 列
            }
        }
    	
        System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+" :校验模板结束.........");
    	
    	if (flag) {
            new JdbcOwnerRepository().jdbcConnect(jdbcConstants,arrearageMsgsList);//批量插入数据
            logger.info("上传文件结束......");
            System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+" :数据入库结束.......");
        } else {
        	//校验失败，清除Redis缓存
        	if(RedisUtils.exits("ERPdataUpload-roomSign-"+cid) 
        			&& RedisUtils.exits("ERPdataUpload-custName-"+cid)){
        		RedisUtils.del("ERPdataUpload-roomSign-"+cid);
    			RedisUtils.del("ERPdataUpload-custName-"+cid);
    			//再查一次数据库，缓存已经入库的数据
    			CacheEntity ce1 = jpr.getCacheData(cid);
    			roomSet = ce1.getRoomSet();
    			custSet = ce1.getCustSet();
    			//集合不为空，则缓存到redis
    			if(roomSet != null && roomSet.size() != 0){
    				for(String value : roomSet){
    					RedisUtils.sadd("ERPdataUpload-roomSign-"+cid,value);
    				}
    			}
    			if(custSet != null && custSet.size() != 0){
    				for(String value : custSet){
    					RedisUtils.sadd("ERPdataUpload-custName-"+cid,value);
    				}
    			}
    		}
        	ExcelData data = new ExcelData();
            data.setName("房屋业主租户资料");
            List<String> titles = new ArrayList<String>();
            titles.add("项目编号");
            titles.add("组团区域");
            titles.add("楼宇名称");
            titles.add("房屋编号");
            titles.add("房屋名称");
            titles.add("单元");
            titles.add("楼层");
            titles.add("hx");
            titles.add("面积类型");
            titles.add("建筑面积");
            titles.add("套内面积");
            titles.add("公摊面积");
            titles.add("花园面积");
            titles.add("产权性质");
            titles.add("使用性质");
            titles.add("使用状态");
            titles.add("房屋状态");
            titles.add("房屋类型");
            titles.add("合同交房时间");
            titles.add("实际交房时间");
            titles.add("物业接管时间");
            titles.add("开始缴费时间");
            titles.add("业主名称   ");
            titles.add("性别");
            titles.add("国籍");
            titles.add("出生日期");
            titles.add("证件名称");
            titles.add("证件号码  ");
            titles.add("固定电话  ");
            titles.add("移动电话");
            titles.add("传真电话  ");
            titles.add("联系地址  ");
            titles.add("邮政编码   ");
            titles.add("收件人");
            titles.add("电子邮件");
            titles.add("Error Message");
            data.setTitles(titles);
            data.setRows(arrayLists);
            
            //使用域做缓存
            String key = "house";
            String current_key = key +"-"+ TimeStamp.getCurrentTime().toString();
            //使用域做缓存
            application.setAttribute(current_key,data);
            return current_key;
        }
        return "success";
    }
}
