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.JdbcConstants;
import cn.com.countrygarden.bi.main.model.erp.RealAmountMsg;
import cn.com.countrygarden.bi.main.repository.erp.JdbcPublicRepository;
import cn.com.countrygarden.bi.main.repository.erp.JdbcRealRepository;
import cn.com.countrygarden.bi.main.service.erp.ExcelRealAmountService;
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 ExcelRealAmountServiceImpl implements ExcelRealAmountService {

	private final Logger logger = LoggerFactory.getLogger(ExcelRealAmountServiceImpl.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<RealAmountMsg> realAmountMsgList = new ArrayList<RealAmountMsg>();
		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;
			}else{
				if(!StringUtil.checkNum(stringArrayList.get(0).trim())){
            		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(!RedisUtils.sismember("ERPdataUpload-custName-"+commid,yzmc)){
					errorBuilder.append("客户名称不存在, ");
					flag = false;
				}
			}
			if(!StringUtil.isBlankOrEmpty(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;
				}
			}
            if(!StringUtil.isBlankOrEmpty(stringArrayList.get(5))){
				int commid = Integer.parseInt(stringArrayList.get(0).trim());
				String parkName = stringArrayList.get(5);
				if(!RedisUtils.sismember("ERPdataUpload-parkName-"+commid,parkName)){
					errorBuilder.append("车位编号不存在,");
            		flag = false;
				}
			}
			if (StringUtil.isBlankOrEmpty(stringArrayList.get(6))) {
				errorBuilder.append("费用名称为空,");
				flag = false;
			}else{
				int commid = Integer.parseInt(stringArrayList.get(0));
				String feeItem = stringArrayList.get(6).trim();
				if(!costItemSet.contains(feeItem)){
					if(!jpr.checkCostItem(commid, feeItem)){
						errorBuilder.append("费用项目不存在,");
						flag = false;
					}else{
						costItemSet.add(feeItem); //如果存在，添加到Set
					}
				}
			}
			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(8).trim())){
					errorBuilder.append("应交日期格式不正确 ,");
					flag = false;
				}
			}
			if (StringUtil.isBlankOrEmpty(stringArrayList.get(9))) {
				errorBuilder.append("开始时间为空,");
				flag = false;
			}else{
				if(!StringUtil.checkDate(stringArrayList.get(9).trim())){
					errorBuilder.append("开始时间格式不正确 ,");
					flag = false;
				}
			}
			if (StringUtil.isBlankOrEmpty(stringArrayList.get(10))) {
				errorBuilder.append("结束时间为空,");
				flag = false;
			}else{
				if(!StringUtil.checkDate(stringArrayList.get(10).trim())){
					errorBuilder.append("结束时间格式不正确 ,");
					flag = false;
				}
			}
			if (StringUtil.isBlankOrEmpty(stringArrayList.get(11))) {
				errorBuilder.append("实收金额（元）为空,");
				flag = false;
			}
			if (StringUtil.isBlankOrEmpty(stringArrayList.get(12))) {
				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 (flag) {
				RealAmountMsg realAmountMsg = new RealAmountMsg();
				realAmountMsg.setCommid(Integer.parseInt(stringArrayList.get(0).trim()));
				realAmountMsg.setKhlb(stringArrayList.get(1).trim());
				realAmountMsg.setKhmc(stringArrayList.get(2).trim());
				realAmountMsg.setFwbh(stringArrayList.get(3).trim());
				realAmountMsg.setFwmc(stringArrayList.get(4).trim());
				realAmountMsg.setCwbh(stringArrayList.get(5).trim());
				realAmountMsg.setFymc(stringArrayList.get(6).trim());
				realAmountMsg.setFyrq(stringArrayList.get(7).trim());
				realAmountMsg.setYjrq(stringArrayList.get(8).trim());
				realAmountMsg.setKssj(stringArrayList.get(9).trim());
				realAmountMsg.setJssj(stringArrayList.get(10).trim());
				realAmountMsg.setJe(stringArrayList.get(11).trim());
				realAmountMsg.setSkfs(stringArrayList.get(12).trim());
				realAmountMsg.setSkr(stringArrayList.get(13).trim());
				realAmountMsg.setSksj(stringArrayList.get(14).trim());
				realAmountMsg.setSjh(stringArrayList.get(15).trim());
				realAmountMsg.setBz(stringArrayList.get(16).trim());
				realAmountMsgList.add(realAmountMsg);
			} 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) {
			JdbcRealRepository.jdbcConnect(jdbcConstants, realAmountMsgList);// 批量插入数据
			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("收据号");
			titles.add("备注");
			titles.add("Error Message");
			data.setTitles(titles);
			data.setRows(arrayLists);
			// 使用域做缓存
			String key = "real";
			String current_key = key +"-"+ TimeStamp.getCurrentTime().toString();
            //使用域做缓存
            application.setAttribute(current_key,data);
			logger.info("上传文件异常......");
			return current_key;
		}
		return "success";
	}
}
