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.HashSet;
import java.util.List;
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.JdbcConstants;
import cn.com.countrygarden.bi.main.model.erp.ParkingMsg;
import cn.com.countrygarden.bi.main.repository.erp.JdbcParkingRepository;
import cn.com.countrygarden.bi.main.repository.erp.JdbcPublicRepository;
import cn.com.countrygarden.bi.main.service.erp.ExcelParkingService;
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 ExcelParkingServiceImpl implements ExcelParkingService {

    private final Logger logger = LoggerFactory.getLogger(ExcelParkingServiceImpl.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<ParkingMsg> parkingMsgList = new ArrayList<ParkingMsg>();
        Set<String> carIdSet = new HashSet<String>();
        Set<Integer> commidSet = new HashSet<Integer>(); //缓存项目编号
        Set<String> costStandardSet = new HashSet<String>(); //缓存收费标准
        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(3))) {
                errorBuilder.append("车位编号为空,");
                flag = false;
            }else{
            	if (carIdSet.contains(stringArrayList.get(3))){
                    errorBuilder.append("车位编号重复 ,");
                    flag = false;
                }else {
                	int commid = Integer.parseInt(stringArrayList.get(0));
                	String parkName = stringArrayList.get(3).trim();
                	if(RedisUtils.sismember("ERPdataUpload-parkName-"+commid, parkName)){
    					errorBuilder.append("车位编号已存在,");
                		flag = false;
    				}else{
    					RedisUtils.sadd("ERPdataUpload-parkName-"+commid, parkName); //缓存中不存在，则存到redis
    				}
                }
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(4))) {
                errorBuilder.append("产权性质为空,");
                flag = false;
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(5))) {
                errorBuilder.append("业主名称为空,");
                flag = false;
            }else{
            	String custName = stringArrayList.get(5);
            	if(!StringUtil.checkName(stringArrayList.get(5))){
            		errorBuilder.append("业主名称格式不正确,");
                    flag = false;
            	}
            	if(!RedisUtils.sismember("ERPdataUpload-custName-"+cid, custName)){
            		RedisUtils.sadd("ERPdataUpload-custName-"+cid, custName); //缓存临时业主
            	}
            }
            if(!StringUtil.isBlankOrEmpty(stringArrayList.get(6))){
            	int commid = Integer.parseInt(stringArrayList.get(0));
            	String roomSign = stringArrayList.get(6);
            	if(!RedisUtils.sismember("ERPdataUpload-roomSign-"+commid,roomSign)){
            		errorBuilder.append("业主房屋编号不存在 ,");
                 	flag = false;
				}
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(7))) {
                errorBuilder.append("使用状态为空,");
                flag = false;
            }
            
            if(!StringUtil.isBlankOrEmpty(stringArrayList.get(9))){
            	int commid = Integer.parseInt(stringArrayList.get(0));
            	String roomSign = stringArrayList.get(9);
            	if(!RedisUtils.sismember("ERPdataUpload-roomSign-"+commid,roomSign)){
            		errorBuilder.append("客户房屋编号不存在 ,");
                 	flag = false;
				}
            }
            
            if (!StringUtil.isBlankOrEmpty(stringArrayList.get(10))) {
            	if(!StringUtil.checkDate(stringArrayList.get(10).trim())){
            		errorBuilder.append("开始时间格式不正确,");
                    flag = false;
            	}
            }
            if(!StringUtil.isBlankOrEmpty(stringArrayList.get(11))){
            	if(!StringUtil.checkDate(stringArrayList.get(11).trim())){
            		errorBuilder.append("结束时间格式不正确,");
                    flag = false;
            	}
            }
            if(!StringUtil.isBlankOrEmpty(stringArrayList.get(12))){
            	if(!StringUtil.checkDate(stringArrayList.get(12).trim())){
            		errorBuilder.append("合同交付时间格式不正确,");
                    flag = false;
            	}
            }
            if(!StringUtil.isBlankOrEmpty(stringArrayList.get(13))){
            	if(!StringUtil.checkDate(stringArrayList.get(13).trim())){
            		errorBuilder.append("实际交付时间格式不正确,");
                    flag = false;
            	}
            }
            if("".equals(stringArrayList.get(14))||stringArrayList.get(14)==null){
            	errorBuilder.append("接管时间为空,");
                flag = false;
            }else if(!StringUtil.checkDate(stringArrayList.get(14).trim())){
        		errorBuilder.append("接管时间格式不正确,");
                flag = false;
        	}
            
            if (!StringUtil.isBlankOrEmpty(stringArrayList.get(17))) {
                int commid = Integer.parseInt(stringArrayList.get(0));
                String stanName = stringArrayList.get(17);
                if(!costStandardSet.contains(stanName)){
                	if(!jpr.checkCostStandard(commid, stanName)){
                		errorBuilder.append("收费标准不存在,");
                        flag = false;
                	}else{
                		costStandardSet.add(stanName); //数据库中存在，则添加到set中
                	}
                }
            }
            if (flag) {
                ParkingMsg parkingMsg = new ParkingMsg();
                parkingMsg.setCommid(Integer.parseInt(stringArrayList.get(0).trim()));
                parkingMsg.setCwqy(stringArrayList.get(1).trim());
                parkingMsg.setCwlx(stringArrayList.get(2).trim());
                parkingMsg.setCwbh(stringArrayList.get(3).trim());
                parkingMsg.setCqxz(stringArrayList.get(4).trim());
                parkingMsg.setYzmc(stringArrayList.get(5).trim());
                parkingMsg.setYzFwbh(stringArrayList.get(6).trim());
                parkingMsg.setSyzt(stringArrayList.get(7).trim());
                parkingMsg.setKhmc(stringArrayList.get(8).trim());
                parkingMsg.setKhFwbh(stringArrayList.get(9).trim());
                parkingMsg.setCphm(stringArrayList.get(15).trim());
                if("".equals(stringArrayList.get(10).trim())){
                	parkingMsg.setKssj(null);
                }else{
                	parkingMsg.setKssj(new Date(stringArrayList.get(10).trim()));
                }
                if("".equals(stringArrayList.get(11).trim())){
                	parkingMsg.setJssj(null);
                }else{
                	parkingMsg.setJssj(new Date(stringArrayList.get(11).trim()));
                }
                
                
                
                if("".equals(stringArrayList.get(12).trim())){
                	parkingMsg.setHtjfsj(null);
                }else{
                	parkingMsg.setHtjfsj(new Date(stringArrayList.get(12).trim()));
                }
                if("".equals(stringArrayList.get(13).trim())){
                	parkingMsg.setSjjfsj(null);
                }else{
                	parkingMsg.setSjjfsj(new Date(stringArrayList.get(13).trim()));
                }
                if("".equals(stringArrayList.get(14).trim())){
                	parkingMsg.setJgsj(null);
                }else{
                	parkingMsg.setJgsj(new Date(stringArrayList.get(14).trim()));
                }
                
                
                
                
                if("".equals(stringArrayList.get(16).trim())){
                	parkingMsg.setJzmj(null); 
                }else{
                	parkingMsg.setJzmj(new BigDecimal(stringArrayList.get(16).trim()));
                }
                parkingMsg.setSfbz(stringArrayList.get(17).trim());
                parkingMsgList.add(parkingMsg);
            } 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) {
            JdbcParkingRepository.jdbcConnect(jdbcConstants,parkingMsgList);//批量插入数据
            logger.info("上传文件结束......");
            System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+" :数据入库结束.......");
        } else {
        	//校验失败，清除Redis缓存
        	if(RedisUtils.exits("ERPdataUpload-parkName-"+cid)){
        		RedisUtils.del("ERPdataUpload-parkName-"+cid);
        		//再查一次数据库，缓存已经入库的数据
        		CacheEntity ce1 = jpr.getCacheData(cid);
        		parkSet = ce1.getParkSet();
        		//集合不为空，则缓存到redis
        		if(parkSet != null && parkSet.size() != 0){
        			for(String value : parkSet){
        				RedisUtils.sadd("ERPdataUpload-parkName-"+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("使用状态");
            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 = "park";
            String current_key = key +"-"+ TimeStamp.getCurrentTime().toString();
            //使用域做缓存
            application.setAttribute(current_key,data);
            logger.info("上传文件异常......");
            return current_key;
        }
        return "success";
    }
}
