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

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.FamilyMemberMsg;
import cn.com.countrygarden.bi.main.model.erp.JdbcConstants;
import cn.com.countrygarden.bi.main.repository.erp.JdbcMemberRepository;
import cn.com.countrygarden.bi.main.repository.erp.JdbcPublicRepository;
import cn.com.countrygarden.bi.main.service.erp.ExcelFamilyMemberService;
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 ExcelFamilyMemberServiceImpl implements ExcelFamilyMemberService {

    private final Logger logger = LoggerFactory.getLogger(ExcelFamilyMemberServiceImpl.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<FamilyMemberMsg> familyMemberMsgList = new ArrayList<FamilyMemberMsg>();
        Set<Integer> commidSet = new HashSet<Integer>(); //缓存commid
        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();
            //commid项目编号字段验证
            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{
            	int commid = Integer.parseInt(stringArrayList.get(0));
            	String roomSign = stringArrayList.get(2);
            	if(!RedisUtils.sismember("ERPdataUpload-roomSign-"+commid,roomSign)){
            		errorBuilder.append("房屋编号不存在 ,");
                 	flag = false;
				}
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(3))) {
                errorBuilder.append("业主名称字段为空 ,");
                flag = false;
            }else{
            	if(!StringUtil.checkName(stringArrayList.get(3))){
            		 errorBuilder.append("业主名称格式不正确 ,");
                     flag = false;
            	}
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(4))) {
                errorBuilder.append("成员名称字段为空, ");
                flag = false;
            }else{
            	if(!StringUtil.checkName(stringArrayList.get(4))){
            		errorBuilder.append("成员名称格式不正确 ,");
                    flag = false;
            	}
            }
            if(!StringUtil.isBlankOrEmpty(stringArrayList.get(5))){
            	if(!StringUtil.checkGender(stringArrayList.get(5))){
            		errorBuilder.append("性别格式不正确 ,");
                    flag = false;
            	}
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(10))) {
                errorBuilder.append("证件名称字段为空, ");
                flag = false;
            }
            if (StringUtil.isBlankOrEmpty(stringArrayList.get(11))) {
                errorBuilder.append("证件号码字段为空 ,");
                flag = false;
            }else{
            	String certificate = stringArrayList.get(10); //证件名称
            	if("身份证".equals(certificate)){ //只要求验证身份证
            		if(!StringUtil.checkPaperCode(stringArrayList.get(11))){
                		errorBuilder.append("身份证号码格式不正确,");
                		flag = false;
                	}
            	}
            }
            if (flag) {
                FamilyMemberMsg familyMemberMsg = new FamilyMemberMsg();
                familyMemberMsg.setCommid(Integer.parseInt(stringArrayList.get(0).trim()));//项目id
                familyMemberMsg.setLymc(stringArrayList.get(1).trim());//楼宇名称
                familyMemberMsg.setFwbh(stringArrayList.get(2).trim());//房屋编号
                familyMemberMsg.setYzmc(stringArrayList.get(3).trim());//业主名称
                familyMemberMsg.setCymc(stringArrayList.get(4).trim());//成员名称
                familyMemberMsg.setXb(stringArrayList.get(5).trim());//性别
                familyMemberMsg.setGj(stringArrayList.get(6).trim());//国籍
                familyMemberMsg.setCsrq(stringArrayList.get(7).trim());//出生日期
                familyMemberMsg.setGzdw(stringArrayList.get(8).trim());//工作单位
                familyMemberMsg.setZy(stringArrayList.get(9).trim());//职业
                familyMemberMsg.setZjmc(stringArrayList.get(10).trim());//证件名称
                familyMemberMsg.setZjhm(stringArrayList.get(11).trim());//证件号码
                familyMemberMsg.setYyzgx(stringArrayList.get(12).trim());//与业主关系
                familyMemberMsg.setGddh(stringArrayList.get(13).trim());//固定电话
                familyMemberMsg.setYddh(stringArrayList.get(14).trim());//移动电话
                familyMemberMsgList.add(familyMemberMsg);//添加到List
            } 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) {
            JdbcMemberRepository.jdbcConnect(jdbcConstants,familyMemberMsgList);//批量插入数据
            logger.info("上传文件结束......");
            System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+" :数据入库结束.......");
        } else {
            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("Error Message");
            data.setTitles(titles);
            data.setRows(arrayLists);
            String key = "member";
            String current_key = key +"-"+ TimeStamp.getCurrentTime().toString();
            //使用域做缓存
            application.setAttribute(current_key,data);
            logger.info("上传文件异常......");
            return current_key;
        }
        return "success";
    }
}
