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.ArrearageMsg;
import cn.com.countrygarden.bi.main.model.erp.CacheEntity;
import cn.com.countrygarden.bi.main.model.erp.JdbcConstants;
import cn.com.countrygarden.bi.main.repository.erp.JdbcArrearageRepository;
import cn.com.countrygarden.bi.main.repository.erp.JdbcPublicRepository;
import cn.com.countrygarden.bi.main.service.erp.ExcelArrearageService;
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 Owen
 * @Description 欠费资料
 */
@Service
@Scope("prototype")
public class ExcelArrearageServiceImpl implements ExcelArrearageService {

	private final Logger logger = LoggerFactory.getLogger(ExcelArrearageServiceImpl.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<ArrearageMsg> arrearageMsgsList = new ArrayList<ArrearageMsg>();
		Set<String> costItemSet = 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;
			}
			if (StringUtil.isBlankOrEmpty(stringArrayList.get(2))) {
				errorBuilder.append("客户名称为空, ");
				flag = false;
			}else{
				int commid = Integer.parseInt(stringArrayList.get(0));
				String yzmc = stringArrayList.get(2);
				if(!StringUtil.checkName(stringArrayList.get(2))){
					errorBuilder.append("客户名称格式不正确, ");
					flag = false;
				}
				if(!RedisUtils.sismember("ERPdataUpload-custName-"+commid,yzmc)){
					errorBuilder.append("客户名称不存在, ");
					flag = false;
				}
			}
			if(!StringUtil.isBlankOrEmpty(stringArrayList.get(3))){
				int commid = Integer.parseInt(stringArrayList.get(0).trim());
            	String roomSign = stringArrayList.get(3);
            	if(!RedisUtils.sismember("ERPdataUpload-roomSign-"+commid,roomSign)){
            		errorBuilder.append("房屋编号不存在,");
            		flag = false;
				}
			}
			if(!StringUtil.isBlankOrEmpty(stringArrayList.get(4))){
				int commid = Integer.parseInt(stringArrayList.get(0).trim());
				String parkName = stringArrayList.get(4);
				if(!RedisUtils.sismember("ERPdataUpload-parkName-"+commid,parkName)){
					errorBuilder.append("车位编号不存在,");
            		flag = false;
				}
			}
			if (StringUtil.isBlankOrEmpty(stringArrayList.get(5))) {
				errorBuilder.append("费用项目为空,");
				flag = false;
			}else{
				int commid = Integer.parseInt(stringArrayList.get(0));
				String feeItem = stringArrayList.get(5).trim();
				if(!costItemSet.contains(feeItem)){
					if(!jpr.checkCostItem(commid, feeItem)){
						errorBuilder.append("费用项目不存在,");
						flag = false;
					}else{
						costItemSet.add(feeItem); //费用项目存在，添加到Set中 
					}
				}
			}
			if (StringUtil.isBlankOrEmpty(stringArrayList.get(6))) {
				errorBuilder.append("费用日期为空 ,");
				flag = false;
			}else{
				if(!StringUtil.checkDate(stringArrayList.get(6).trim())){
					errorBuilder.append("费用日期格式不正确 ,");
					flag = false;
				}
			}
			if (StringUtil.isBlankOrEmpty(stringArrayList.get(7))) {
				errorBuilder.append("应收时间为空, ");
				flag = false;
			}else{
				if(!StringUtil.checkDate(stringArrayList.get(7).trim())){
					errorBuilder.append("应收时间格式不正确 ,");
					flag = false;
				}
			}
			if (StringUtil.isBlankOrEmpty(stringArrayList.get(8))) {
				errorBuilder.append("开始时间为空, ");
				flag = false;
			}else{
				if(!StringUtil.checkDate(stringArrayList.get(7).trim())){
					errorBuilder.append("开始时间格式不正确 ,");
					flag = false;
				}
			}
			if (StringUtil.isBlankOrEmpty(stringArrayList.get(9))) {
				errorBuilder.append("结束时间为空, ");
				flag = false;
			}else{
				if(!StringUtil.checkDate(stringArrayList.get(7).trim())){
					errorBuilder.append("结束时间格式不正确 ,");
					flag = false;
				}
			}
			if (StringUtil.isBlankOrEmpty(stringArrayList.get(10))) {
				errorBuilder.append("欠费金额为空,");
				flag = false;
			}else{
				if(!StringUtil.checkMoney(stringArrayList.get(10).trim())){
					errorBuilder.append("欠费金额格式不正确,");
					flag = false;
				}
			}
			if (flag) {
				ArrearageMsg arrearageMsg = new ArrearageMsg();//创建实体类
				arrearageMsg.setCommid(Integer.parseInt(stringArrayList.get(0).trim()));
				arrearageMsg.setKhlb(stringArrayList.get(1).trim());//客户类别
				arrearageMsg.setKhmc(stringArrayList.get(2).trim());//客户名称
				arrearageMsg.setFwbh(stringArrayList.get(3).trim());//房屋编码
				arrearageMsg.setCwbh(stringArrayList.get(4).trim());//车位编号
				arrearageMsg.setFyxm(stringArrayList.get(5).trim());//费用项目
				arrearageMsg.setFyrq(stringArrayList.get(6).trim());//费用日期
				arrearageMsg.setYsrq(stringArrayList.get(7).trim());//应收时间
				arrearageMsg.setKssj(stringArrayList.get(8).trim());//开始时间
				arrearageMsg.setJssj(stringArrayList.get(9).trim());//结束时间
				arrearageMsg.setJe(stringArrayList.get(10).trim());//欠费金额（元）
				arrearageMsg.setBz(stringArrayList.get(11).trim());//备注
				arrearageMsgsList.add(arrearageMsg);//添加到集合
			} 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) {
			JdbcArrearageRepository.jdbcConnect(jdbcConstants, arrearageMsgsList);
			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("备注");
			data.setTitles(titles);
			data.setRows(arrayLists);
			String key = "arrea";
			String current_key = key +"-"+ TimeStamp.getCurrentTime().toString();
            //使用域做缓存
            application.setAttribute(current_key,data);
			logger.info("上传文件异常......");
			return current_key;
		}
		return "success";
	}
}
