/**
 * Copyright
 * All right reserved.
 * 项目名称:
 * 创建日期:2022/5/22
 */
package org.springblade.task;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.excel.read.listener.ReadListener;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import lombok.extern.slf4j.Slf4j;
import org.springblade.common.cache.DictBizCache;
import org.springblade.core.excel.listener.DataListener;
import org.springblade.core.excel.util.ExcelUtil;
import org.springblade.core.oss.model.BladeFile;
import org.springblade.core.tool.utils.ConcurrentDateFormat;
import org.springblade.core.tool.utils.Func;
import org.springblade.modules.baseinfo.rtu.entity.RtuInfoEntity;
import org.springblade.modules.baseinfo.rtu.service.IRtuBaseInfoService;
import org.springblade.modules.manage.entity.RtuImportRecordDetailEntity;
import org.springblade.modules.manage.entity.RtuImportRecordEntity;
import org.springblade.modules.manage.entity.RtuInfoTableDataEntity;
import org.springblade.modules.manage.service.IRtuImportRecordDetailService;
import org.springblade.modules.manage.service.IRtuImportRecordService;
import org.springblade.modules.system.entity.DictBiz;

import java.io.BufferedInputStream;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.*;
import java.util.concurrent.Callable;

/***
 * Date:2022/5/22
 * Title: 河道水位抽取
 * Description：
 * @author swp
 * @version 1.0
 * Remark：认为有必要的其他信息
 */
@Slf4j
public class RtuImportTask implements Callable<Integer> {

	private BladeFile bladeFile;
	private RtuImportRecordEntity recordEntity;
	private IRtuBaseInfoService rtuBaseInfoService;
	private IRtuImportRecordService rtuImportRecordService;
	private IRtuImportRecordDetailService rtuImportRecordDetailService;

	public RtuImportTask(BladeFile bladeFile, RtuImportRecordEntity recordEntity, IRtuBaseInfoService rtuBaseInfoService, IRtuImportRecordService rtuImportRecordService, IRtuImportRecordDetailService rtuImportRecordDetailService) {
		this.bladeFile = bladeFile;
		this.recordEntity = recordEntity;
		this.rtuBaseInfoService = rtuBaseInfoService;
		this.rtuImportRecordService = rtuImportRecordService;
		this.rtuImportRecordDetailService = rtuImportRecordDetailService;
	}

	@Override
	public Integer call() {
		try {
			log.info("RTU导入任务开始执行*********************");
			URL url = new URL(bladeFile.getLink());
			HttpURLConnection conn = (HttpURLConnection) url.openConnection();
			//设置超时间为3秒
			conn.setConnectTimeout(3 * 1000);
			//防止屏蔽程序抓取而返回403错误
			conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
			//处理百分比
//			float recordSize = 0;
//			float processCount = 0;
			//得到输入流
			InputStream inputStream = conn.getInputStream();
			String fileName = bladeFile.getOriginalName();
//			LambdaQueryWrapper<RtuInfoEntity> statusWwrapper = Wrappers.<RtuInfoEntity>query().lambda();
//			statusWwrapper.eq(RtuInfoEntity::getIsDeleted, 0);
//			List<RtuInfoEntity> rtuList = rtuBaseInfoService.list(statusWwrapper);
//			Map<String, Integer> rtuStatus = new HashMap<>(rtuList.size());
//			for (RtuInfoEntity entity : rtuList) {
//				rtuStatus.put(entity.getRtuCode(), 0);
//			}
			if(!fileName.endsWith("xlsx")){
				recordEntity.setImportStatus(2L);
				rtuImportRecordService.saveOrUpdate(recordEntity);
			    return 0;
			}
			DataListener<RtuInfoTableDataEntity> dataListener = new DataListener<>();
			InputStream inputStreambuff = new BufferedInputStream(inputStream);
			ExcelReaderBuilder builder = EasyExcel.read(inputStreambuff, RtuInfoTableDataEntity.class, dataListener);
			((ExcelReaderSheetBuilder)builder.sheet(0).headRowNumber(3)).doRead();
			List<RtuInfoTableDataEntity> list= dataListener.getDataList();
			if (null != list && list.size()>0){
				for (RtuInfoTableDataEntity entity:list){
                       log.info(entity.getRtuCode());
				}
			}

			//List<NoticeExcel> list = ExcelUtil.read(inputStream, 0, RtuInfoTableDataEntity.class);

		//	List<RtuInfoTableDataEntity> list = new LinkedList<>();
			if (fileName.endsWith("xlsx")) {
				EasyExcel.read(inputStream, RtuInfoTableDataEntity.class, new ReadListener() {
					@Override
					public void invoke(Object o, AnalysisContext analysisContext) {
						RtuInfoTableDataEntity dataEntity = (RtuInfoTableDataEntity) o;
						list.add(dataEntity);
					}

					@Override
					public void doAfterAllAnalysed(AnalysisContext analysisContext) {

						boolean dataErrorCheck = false;
						for (int i = 2; i < list.size(); i++) {
							RtuInfoTableDataEntity dataEntity = list.get(i);
							if (null != dataEntity.getCity()) {
								String city = dataEntity.getCity().trim();
								if (city.length() == 0) {
									dataErrorCheck = true;
									break;
								}
							} else {
								dataErrorCheck = true;
								break;
							}
							if (null != dataEntity.getDist()) {
								String dist = dataEntity.getDist().trim();
								if (dist.length() == 0) {
									dataErrorCheck = true;
									break;
								}
							} else {
								dataErrorCheck = true;
								break;
							}
							if (null != dataEntity.getRtuCode()) {
								String rtuCode = dataEntity.getRtuCode().trim();
								if (rtuCode.length() > 0) {
									if (!isLetterDigit(rtuCode)) {
										dataErrorCheck = true;
										break;
									}
								} else {
									dataErrorCheck = true;
									break;
								}
							} else {
								dataErrorCheck = true;
								break;
							}
							if (null != dataEntity.getRtuName()) {
								String rtuName = dataEntity.getRtuName().trim();
								if (rtuName.length() == 0) {
									dataErrorCheck = true;
									break;
								}
							} else {
								dataErrorCheck = true;
								break;
							}
							if (null != dataEntity.getRtuKind()) {
								String rtuKind = dataEntity.getRtuKind().trim();
								if (rtuKind.length() == 0) {
									dataErrorCheck = true;
									break;
								}
							} else {
								dataErrorCheck = true;
								break;
							}
							if (null != dataEntity.getManageCompany()) {
								String company = dataEntity.getManageCompany().trim();
								if (company.length() == 0) {
									dataErrorCheck = true;
									break;
								}
							} else {
								dataErrorCheck = true;
								break;
							}
						}
						if (dataErrorCheck) {
							recordEntity.setImportStatus(2L);
							rtuImportRecordService.saveOrUpdate(recordEntity);
						} else {
							for (int i = 2; i < list.size(); i++) {
								RtuInfoTableDataEntity dataEntity = list.get(i);
								String rtuCode = dataEntity.getRtuCode().trim();

								RtuInfoEntity rtuInfoEntity = new RtuInfoEntity();
								rtuInfoEntity.setRtuCode(rtuCode);
								if (null != dataEntity.getCity()) {
									rtuInfoEntity.setAdCity(dataEntity.getCity().trim());
								}
								if (null != dataEntity.getDist()) {
									rtuInfoEntity.setAdDist(dataEntity.getDist().trim());
								}
								if (null != dataEntity.getRtuName()) {
									rtuInfoEntity.setRtuName(dataEntity.getRtuName().trim());
								}
								if (null != dataEntity.getRtuKind()) {
									rtuInfoEntity.setIsRain(0);
									rtuInfoEntity.setIsRiver(0);
									rtuInfoEntity.setIsRes(0);
									rtuInfoEntity.setIsGround(0);
									String kinds = dataEntity.getRtuKind().trim();
									if (kinds.length() > 0) {
										List<DictBiz> bizs = DictBizCache.getList("rtu_kind");
										String kindList = "";
										if (kinds.contains("水位")) {
											rtuInfoEntity.setIsRes(1);
											rtuInfoEntity.setIsRiver(1);
											for (DictBiz biz : bizs) {
												if (biz.getDictKey().equals("river")) {
													if (kindList.length() > 0) {
														kindList += ",";
													}
													kindList += Long.toString(biz.getId());
												} else if (biz.getDictKey().equals("rsvr")) {
													if (kindList.length() > 0) {
														kindList += ",";
													}
													kindList += Long.toString(biz.getId());
												}
											}
											rtuInfoEntity.setRtuKinds(kindList);
										}
										if (kinds.contains("雨量")) {
											rtuInfoEntity.setIsRain(1);
											for (DictBiz biz : bizs) {
												if (biz.getDictKey().equals("rain")) {
													if (kindList.length() > 0) {
														kindList += ",";
													}
													kindList += Long.toString(biz.getId());
													break;
												}
											}
										}
										if (kinds.contains("墒情")) {
											rtuInfoEntity.setIsGround(1);
											for (DictBiz biz : bizs) {
												if (biz.getDictKey().equals("ground")) {
													if (kindList.length() > 0) {
														kindList += ",";
													}
													kindList += Long.toString(biz.getId());
													break;
												}
											}
										}
										if (kinds.contains("视频")) {
											rtuInfoEntity.setIsVideo(1);
//											for (DictBiz biz : bizs) {
//												if (biz.getDictKey().equals("ground")) {
//													if (kindList.length() > 0) {
//														kindList += ",";
//													}
//													kindList += Long.toString(biz.getId());
//													break;
//												}
//											}
										}
										rtuInfoEntity.setRtuKinds(kindList);
									}
								}
								if (null != dataEntity.getLocationDesc()) {
									rtuInfoEntity.setLocationDesc(dataEntity.getLocationDesc().trim());
								}
								if (null != dataEntity.getLng()) {
									ArrayList<String> textArr = new ArrayList<>();
									textArr.add("");
									String location = dataEntity.getLng().trim();
									for (int l = 0; l < location.length(); l++) {
										String subtext = location.substring(l, l + 1);
										if (Func.isNumeric(subtext)) {
											String str = textArr.get(textArr.size() - 1);
											str += subtext;
											textArr.set(textArr.size() - 1, str);
										} else {
											textArr.add("");
										}
									}
									ArrayList<String> tmp = new ArrayList<>();
									for (String text : textArr) {
										if (text.length() > 0) {
											tmp.add(text);
										}
									}
									if (tmp.size() >= 4) {
										//度分秒，秒有小数点
										String v = tmp.get(2) + "." + tmp.get(3);
										double lat = Double.parseDouble(v);
										lat = lat / 60.0;
										v = tmp.get(1);
										lat = (Double.parseDouble(v) + lat) / 60.0;
										v = tmp.get(0);
										lat = Double.parseDouble(v) + lat;
										rtuInfoEntity.setLng(String.format("%.06f", lat));
									} else if (tmp.size() == 3) {
										//度分秒
										String v = tmp.get(2);
										double lat = Double.parseDouble(v);
										lat = lat / 60.0;
										v = tmp.get(1);
										lat = (Double.parseDouble(v) + lat) / 60.0;
										v = tmp.get(0);
										lat = Double.parseDouble(v) + lat;
										rtuInfoEntity.setLng(String.format("%.06f", lat));

									} else if (tmp.size() == 2) {
										//度
										String v = tmp.get(0) + "." + tmp.get(1);
										double lat = Double.parseDouble(v);
										rtuInfoEntity.setLng(String.format("%.06f", lat));
									}

								}
								if (null != dataEntity.getLat()) {
									ArrayList<String> textArr = new ArrayList<>();
									textArr.add("");
									String location = dataEntity.getLat().trim();
									for (int l = 0; l < location.length(); l++) {
										String subtext = location.substring(l, l + 1);
										if (Func.isNumeric(subtext)) {
											String str = textArr.get(textArr.size() - 1);
											str += subtext;
											textArr.set(textArr.size() - 1, str);
										} else {
											textArr.add("");
										}
									}
									ArrayList<String> tmp = new ArrayList<>();
									for (String text : textArr) {
										if (text.length() > 0) {
											tmp.add(text);
										}
									}
									if (tmp.size() >= 4) {
										//度分秒，秒有小数点
										String v = tmp.get(2) + "." + tmp.get(3);
										double lat = Double.parseDouble(v);
										lat = lat / 60.0;
										v = tmp.get(1);
										lat = (Double.parseDouble(v) + lat) / 60.0;
										v = tmp.get(0);
										lat = Double.parseDouble(v) + lat;
										rtuInfoEntity.setLat(String.format("%.06f", lat));
									} else if (tmp.size() == 3) {
										//度分秒
										String v = tmp.get(2);
										double lat = Double.parseDouble(v);
										lat = lat / 60.0;
										v = tmp.get(1);
										lat = (Double.parseDouble(v) + lat) / 60.0;
										v = tmp.get(0);
										lat = Double.parseDouble(v) + lat;
										rtuInfoEntity.setLat(String.format("%.06f", lat));

									} else if (tmp.size() == 2) {
										//度
										String v = tmp.get(0) + "." + tmp.get(1);
										double lat = Double.parseDouble(v);
										rtuInfoEntity.setLat(String.format("%.06f", lat));
									}

								}
								if (null != dataEntity.getManageCompany()) {
									rtuInfoEntity.setManageCompany(dataEntity.getManageCompany().trim());
								}
								if (null != dataEntity.getRtuBrand()) {
									rtuInfoEntity.setRtuBrand(dataEntity.getRtuBrand().trim());
								}
								if (null != dataEntity.getRtuModel()) {
									rtuInfoEntity.setRtuModel(dataEntity.getRtuModel().trim());
								}
								if (null != dataEntity.getRtuReplaceDate()) {
									ArrayList<String> textArr = new ArrayList<>();
									textArr.add("");
									String replaceDate = dataEntity.getRtuReplaceDate().trim();
									for (int l = 0; l < replaceDate.length(); l++) {
										String subtext = replaceDate.substring(l, l + 1);
										if (Func.isNumeric(subtext)) {
											String str = textArr.get(textArr.size() - 1);
											str += subtext;
											textArr.set(textArr.size() - 1, str);
										} else {
											textArr.add("");
										}
									}
									ArrayList<String> tmp = new ArrayList<>();
									for (String text : textArr) {
										if (text.length() > 0) {
											tmp.add(text);
										}
									}
									if (tmp.size() >= 3) {
										String v = tmp.get(0) + "-" + tmp.get(1) + "-" + tmp.get(2);
										Date dt = Func.parse(v, ConcurrentDateFormat.of("yyyy-MM-dd"));
										rtuInfoEntity.setRtuReplaceDate(dt);
									} else if (tmp.size() == 2) {
										String v = tmp.get(0) + "-" + tmp.get(1) + "-01";
										Date dt = Func.parse(v, ConcurrentDateFormat.of("yyyy-MM-dd"));
										rtuInfoEntity.setRtuReplaceDate(dt);
									}
								}
								if (null != dataEntity.getToSheng()) {
									if (dataEntity.getToSheng().trim().equals("是")) {
										rtuInfoEntity.setDatagramToSheng(1);
									} else {
										rtuInfoEntity.setDatagramToSheng(0);
									}
								} else {
									rtuInfoEntity.setDatagramToSheng(0);
								}
								if (null != dataEntity.getToShi()) {
									if (dataEntity.getToShi().trim().equals("是")) {
										rtuInfoEntity.setDatagramToShi(1);
									} else {
										rtuInfoEntity.setDatagramToShi(0);
									}
								} else {
									rtuInfoEntity.setDatagramToShi(0);
								}
								if (null != dataEntity.getToQu()) {
									if (dataEntity.getToQu().trim().equals("是")) {
										rtuInfoEntity.setDatagramToQu(1);
									} else {
										rtuInfoEntity.setDatagramToQu(0);
									}
								} else {
									rtuInfoEntity.setDatagramToQu(0);
								}
								if (null != dataEntity.getRainSensorBrand()) {
									rtuInfoEntity.setRainSensorBrand(dataEntity.getRainSensorBrand().trim());
								}
								if (null != dataEntity.getRainSensorModel()) {
									rtuInfoEntity.setRainSensorModel(dataEntity.getRainSensorModel().trim());
								}
								if (null != dataEntity.getRainSensorReplaceDate()) {
									ArrayList<String> textArr = new ArrayList<>();
									textArr.add("");
									String replaceDate = dataEntity.getRainSensorReplaceDate().trim();
									for (int l = 0; l < replaceDate.length(); l++) {
										String subtext = replaceDate.substring(l, l + 1);
										if (Func.isNumeric(subtext)) {
											String str = textArr.get(textArr.size() - 1);
											str += subtext;
											textArr.set(textArr.size() - 1, str);
										} else {
											textArr.add("");
										}
									}
									ArrayList<String> tmp = new ArrayList<>();
									for (String text : textArr) {
										if (text.length() > 0) {
											tmp.add(text);
										}
									}
									if (tmp.size() >= 3) {
										String v = tmp.get(0) + "-" + tmp.get(1) + "-" + tmp.get(2);
										Date dt = Func.parse(v, ConcurrentDateFormat.of("yyyy-MM-dd"));
										rtuInfoEntity.setRainSensorReplaceDate(dt);
									} else if (tmp.size() == 2) {
										String v = tmp.get(0) + "-" + tmp.get(1) + "-01";
										Date dt = Func.parse(v, ConcurrentDateFormat.of("yyyy-MM-dd"));
										rtuInfoEntity.setRainSensorReplaceDate(dt);
									}
								}
								if (null != dataEntity.getWaterSensorType()) {
									List<DictBiz> bizs = DictBizCache.getList("water_level_sensor_type");
									for (DictBiz biz : bizs) {
										if (dataEntity.getWaterSensorType().trim().contains(biz.getDictValue())) {
											rtuInfoEntity.setWaterSensorType(biz.getId());
											break;
										}
									}
								}
								if (null != dataEntity.getWaterSensorBrand()) {
									rtuInfoEntity.setWaterSensorBrand(dataEntity.getWaterSensorBrand().trim());
								}
								if (null != dataEntity.getWaterSensorModel()) {
									rtuInfoEntity.setWaterSensorModel(dataEntity.getWaterSensorModel().trim());
								}
								if (null != dataEntity.getWaterSensorReplaceDate()) {
									ArrayList<String> textArr = new ArrayList<>();
									textArr.add("");
									String replaceDate = dataEntity.getWaterSensorReplaceDate().trim();
									for (int l = 0; l < replaceDate.length(); l++) {
										String subtext = replaceDate.substring(l, l + 1);
										if (Func.isNumeric(subtext)) {
											String str = textArr.get(textArr.size() - 1);
											str += subtext;
											textArr.set(textArr.size() - 1, str);
										} else {
											textArr.add("");
										}
									}
									ArrayList<String> tmp = new ArrayList<>();
									for (String text : textArr) {
										if (text.length() > 0) {
											tmp.add(text);
										}
									}
									if (tmp.size() >= 3) {
										String v = tmp.get(0) + "-" + tmp.get(1) + "-" + tmp.get(2);
										Date dt = Func.parse(v, ConcurrentDateFormat.of("yyyy-MM-dd"));
										rtuInfoEntity.setWaterSensorReplaceDate(dt);
									} else if (tmp.size() == 2) {
										String v = tmp.get(0) + "-" + tmp.get(1) + "-01";
										Date dt = Func.parse(v, ConcurrentDateFormat.of("yyyy-MM-dd"));
										rtuInfoEntity.setWaterSensorReplaceDate(dt);
									}
								}
								if (null != dataEntity.getGroundSensorType()) {
									List<DictBiz> bizs = DictBizCache.getList("ground_water_sensor_type");
									for (DictBiz biz : bizs) {
										if (dataEntity.getGroundSensorType().trim().contains(biz.getDictValue())) {
											rtuInfoEntity.setGroundSensorType(biz.getId());
											break;
										}
									}
								}
								if (null != dataEntity.getGroundSensorBrand()) {
									rtuInfoEntity.setGroundSensorBrand(dataEntity.getGroundSensorBrand().trim());
								}
								if (null != dataEntity.getGroundSensorModel()) {
									rtuInfoEntity.setGroundSensorModel(dataEntity.getGroundSensorModel().trim());
								}
								if (null != dataEntity.getGroundSensorReplaceDate()) {
									ArrayList<String> textArr = new ArrayList<>();
									textArr.add("");
									String replaceDate = dataEntity.getGroundSensorReplaceDate().trim();
									for (int l = 0; l < replaceDate.length(); l++) {
										String subtext = replaceDate.substring(l, l + 1);
										if (Func.isNumeric(subtext)) {
											String str = textArr.get(textArr.size() - 1);
											str += subtext;
											textArr.set(textArr.size() - 1, str);
										} else {
											textArr.add("");
										}
									}
									ArrayList<String> tmp = new ArrayList<>();
									for (String text : textArr) {
										if (text.length() > 0) {
											tmp.add(text);
										}
									}
									if (tmp.size() >= 3) {
										String v = tmp.get(0) + "-" + tmp.get(1) + "-" + tmp.get(2);
										Date dt = Func.parse(v, ConcurrentDateFormat.of("yyyy-MM-dd"));
										rtuInfoEntity.setGroundSensorReplaceDate(dt);
									} else if (tmp.size() == 2) {
										String v = tmp.get(0) + "-" + tmp.get(1) + "-01";
										Date dt = Func.parse(v, ConcurrentDateFormat.of("yyyy-MM-dd"));
										rtuInfoEntity.setGroundSensorReplaceDate(dt);
									}
								}
								if (null != dataEntity.getBatteryModel()) {
									rtuInfoEntity.setBatteryModel(dataEntity.getBatteryModel().trim());
								}
								if (null != dataEntity.getBatteryReplaceDate()) {
									ArrayList<String> textArr = new ArrayList<>();
									textArr.add("");
									String replaceDate = dataEntity.getBatteryReplaceDate().trim();
									for (int l = 0; l < replaceDate.length(); l++) {
										String subtext = replaceDate.substring(l, l + 1);
										if (Func.isNumeric(subtext)) {
											String str = textArr.get(textArr.size() - 1);
											str += subtext;
											textArr.set(textArr.size() - 1, str);
										} else {
											textArr.add("");
										}
									}
									ArrayList<String> tmp = new ArrayList<>();
									for (String text : textArr) {
										if (text.length() > 0) {
											tmp.add(text);
										}
									}
									if (tmp.size() >= 3) {
										String v = tmp.get(0) + "-" + tmp.get(1) + "-" + tmp.get(2);
										Date dt = Func.parse(v, ConcurrentDateFormat.of("yyyy-MM-dd"));
										rtuInfoEntity.setBatteryReplaceDate(dt);
									} else if (tmp.size() == 2) {
										String v = tmp.get(0) + "-" + tmp.get(1) + "-01";
										Date dt = Func.parse(v, ConcurrentDateFormat.of("yyyy-MM-dd"));
										rtuInfoEntity.setBatteryReplaceDate(dt);
									}
								}
								if (null != dataEntity.getSunModel()) {
									rtuInfoEntity.setSunPowerModel(dataEntity.getSunModel().trim());
								}
								if (null != dataEntity.getSimId()) {
									rtuInfoEntity.setNetworkSimId(dataEntity.getSimId().trim());
								}
								if (null != dataEntity.getSimPay()) {
									rtuInfoEntity.setNetworkPayer(dataEntity.getSimPay().trim());
								}
								if (null != dataEntity.getSimOverdate()) {
									ArrayList<String> textArr = new ArrayList<>();
									textArr.add("");
									String replaceDate = dataEntity.getSimOverdate().trim();
									for (int l = 0; l < replaceDate.length(); l++) {
										String subtext = replaceDate.substring(l, l + 1);
										if (Func.isNumeric(subtext)) {
											String str = textArr.get(textArr.size() - 1);
											str += subtext;
											textArr.set(textArr.size() - 1, str);
										} else {
											textArr.add("");
										}
									}
									ArrayList<String> tmp = new ArrayList<>();
									for (String text : textArr) {
										if (text.length() > 0) {
											tmp.add(text);
										}
									}
									if (tmp.size() >= 3) {
										String v = tmp.get(0) + "-" + tmp.get(1) + "-" + tmp.get(2);
										Date dt = Func.parse(v, ConcurrentDateFormat.of("yyyy-MM-dd"));
										rtuInfoEntity.setNetworkPayEndDate(dt);
									} else if (tmp.size() == 2) {
										String v = tmp.get(0) + "-" + tmp.get(1) + "-01";
										Date dt = Func.parse(v, ConcurrentDateFormat.of("yyyy-MM-dd"));
										rtuInfoEntity.setNetworkPayEndDate(dt);
									}
								}
								if (null != dataEntity.getSatellite()) {
									if (dataEntity.getSatellite().trim().equals("是")) {
										rtuInfoEntity.setIsSatellite(1);
									} else {
										rtuInfoEntity.setIsSatellite(0);
									}
								} else {
									rtuInfoEntity.setIsSatellite(0);
								}
								if (null != dataEntity.getSatelliteModel()) {
									List<DictBiz> bizs = DictBizCache.getList("satellite_model");
									for (DictBiz biz : bizs) {
										if (dataEntity.getSatelliteModel().trim().contains(biz.getDictValue())) {
											rtuInfoEntity.setSatelliteModel(biz.getId());
											break;
										}
									}
								}
								if (null != dataEntity.getRemark()) {
									rtuInfoEntity.setRemark(dataEntity.getRemark().trim());
								}

								//查找行政区划
								RtuInfoEntity adcd= rtuBaseInfoService.getAdcd(rtuInfoEntity);
                                if (adcd!= null){
									rtuInfoEntity.setAdName(adcd.getAdName());
									rtuInfoEntity.setAdCode(adcd.getAdCode());
								}

								System.out.println("save record********************");
								if (recordEntity.getSaveType() == 0) {
									//新增
									RtuInfoEntity entity = rtuBaseInfoService.rtuInfoByRtuCode(rtuInfoEntity.getRtuCode());
									if (null == entity) {
//										System.out.println("save ********************");
//										System.out.println(JSON.toJSON(rtuInfoEntity).toString());
										rtuBaseInfoService.save(rtuInfoEntity);
										RtuImportRecordDetailEntity detailEntity = new RtuImportRecordDetailEntity();
										detailEntity.setRecordId(recordEntity.getId());
										detailEntity.setRtuCode(rtuInfoEntity.getRtuCode());
										detailEntity.setRtuName(rtuInfoEntity.getRtuName());
										detailEntity.setSaveType(0);
										rtuImportRecordDetailService.save(detailEntity);
									}
								} else if (recordEntity.getSaveType() == 1) {
									//覆盖
									RtuInfoEntity entity = rtuBaseInfoService.rtuInfoByRtuCode(rtuInfoEntity.getRtuCode());
									if (null != entity) {
										//更新列
										entity.setRtuName(rtuInfoEntity.getRtuName());
										entity.setAdCity(rtuInfoEntity.getAdCity());
										entity.setAdDist(rtuInfoEntity.getAdDist());
										entity.setRtuKinds(rtuInfoEntity.getRtuKinds());
										entity.setLocationDesc(rtuInfoEntity.getLocationDesc());
										entity.setLng(rtuInfoEntity.getLng());
										entity.setLat(rtuInfoEntity.getLat());
										entity.setManageCompany(rtuInfoEntity.getManageCompany());
										entity.setRtuBrand(rtuInfoEntity.getRtuBrand());
										entity.setRtuModel(rtuInfoEntity.getRtuModel());
										entity.setRtuReplaceDate(rtuInfoEntity.getRtuReplaceDate());
										entity.setDatagramToQu(rtuInfoEntity.getDatagramToQu());
										entity.setDatagramToShi(rtuInfoEntity.getDatagramToShi());
										entity.setDatagramToSheng(rtuInfoEntity.getDatagramToSheng());
										entity.setRainSensorBrand(rtuInfoEntity.getRainSensorBrand());
										entity.setRainSensorModel(rtuInfoEntity.getRainSensorModel());
										entity.setRainSensorReplaceDate(rtuInfoEntity.getRainSensorReplaceDate());
										entity.setWaterSensorBrand(rtuInfoEntity.getWaterSensorBrand());
										entity.setWaterSensorModel(rtuInfoEntity.getWaterSensorModel());
										entity.setWaterSensorReplaceDate(rtuInfoEntity.getWaterSensorReplaceDate());
										entity.setWaterSensorType(rtuInfoEntity.getWaterSensorType());
										entity.setGroundSensorBrand(rtuInfoEntity.getGroundSensorBrand());
										entity.setGroundSensorModel(rtuInfoEntity.getGroundSensorModel());
										entity.setGroundSensorReplaceDate(rtuInfoEntity.getGroundSensorReplaceDate());
										entity.setGroundSensorType(rtuInfoEntity.getGroundSensorType());
										entity.setBatteryModel(rtuInfoEntity.getBatteryModel());
										entity.setBatteryReplaceDate(rtuInfoEntity.getBatteryReplaceDate());
										entity.setSunPowerModel(rtuInfoEntity.getSunPowerModel());
										entity.setNetworkSimId(rtuInfoEntity.getNetworkSimId());
										entity.setNetworkPayer(rtuInfoEntity.getNetworkPayer());
										entity.setNetworkPayEndDate(rtuInfoEntity.getNetworkPayEndDate());
										entity.setSatelliteModel(rtuInfoEntity.getSatelliteModel());
										entity.setIsSatellite(rtuInfoEntity.getIsSatellite());
										entity.setRemark(rtuInfoEntity.getRemark());
										entity.setAdCode(rtuInfoEntity.getAdCode());
										entity.setAdName(rtuInfoEntity.getAdName());

                                        entity.setUpdateTime(new Date());
										rtuBaseInfoService.updateById(entity);
										RtuImportRecordDetailEntity detailEntity = new RtuImportRecordDetailEntity();
										detailEntity.setRecordId(recordEntity.getId());
										detailEntity.setRtuCode(rtuInfoEntity.getRtuCode());
										detailEntity.setRtuName(rtuInfoEntity.getRtuName());

//										System.out.println("update ********************");
//										System.out.println(JSON.toJSON(rtuInfoEntity).toString());
										detailEntity.setSaveType(1);

										rtuImportRecordDetailService.save(detailEntity);
									}
								} else if (recordEntity.getSaveType() == 2) {
									//新增或修改
									RtuImportRecordDetailEntity detailEntity = new RtuImportRecordDetailEntity();
									detailEntity.setRecordId(recordEntity.getId());
									detailEntity.setRtuCode(rtuInfoEntity.getRtuCode());
									detailEntity.setRtuName(rtuInfoEntity.getRtuName());

									RtuInfoEntity entity = rtuBaseInfoService.rtuInfoByRtuCode(rtuInfoEntity.getRtuCode());
									if (null != entity) {
										detailEntity.setSaveType(1);
										entity.setUpdateTime(new Date());
										//更新列
										entity.setRtuName(rtuInfoEntity.getRtuName());
										entity.setAdCity(rtuInfoEntity.getAdCity());
										entity.setAdDist(rtuInfoEntity.getAdDist());
										entity.setRtuKinds(rtuInfoEntity.getRtuKinds());
										entity.setLocationDesc(rtuInfoEntity.getLocationDesc());
										entity.setLng(rtuInfoEntity.getLng());
										entity.setLat(rtuInfoEntity.getLat());
										entity.setManageCompany(rtuInfoEntity.getManageCompany());
										entity.setRtuBrand(rtuInfoEntity.getRtuBrand());
										entity.setRtuModel(rtuInfoEntity.getRtuModel());
										entity.setRtuReplaceDate(rtuInfoEntity.getRtuReplaceDate());
										entity.setDatagramToQu(rtuInfoEntity.getDatagramToQu());
										entity.setDatagramToShi(rtuInfoEntity.getDatagramToShi());
										entity.setDatagramToSheng(rtuInfoEntity.getDatagramToSheng());
										entity.setRainSensorBrand(rtuInfoEntity.getRainSensorBrand());
										entity.setRainSensorModel(rtuInfoEntity.getRainSensorModel());
										entity.setRainSensorReplaceDate(rtuInfoEntity.getRainSensorReplaceDate());
										entity.setWaterSensorBrand(rtuInfoEntity.getWaterSensorBrand());
										entity.setWaterSensorModel(rtuInfoEntity.getWaterSensorModel());
										entity.setWaterSensorReplaceDate(rtuInfoEntity.getWaterSensorReplaceDate());
										entity.setWaterSensorType(rtuInfoEntity.getWaterSensorType());
										entity.setGroundSensorBrand(rtuInfoEntity.getGroundSensorBrand());
										entity.setGroundSensorModel(rtuInfoEntity.getGroundSensorModel());
										entity.setGroundSensorReplaceDate(rtuInfoEntity.getGroundSensorReplaceDate());
										entity.setGroundSensorType(rtuInfoEntity.getGroundSensorType());
										entity.setBatteryModel(rtuInfoEntity.getBatteryModel());
										entity.setBatteryReplaceDate(rtuInfoEntity.getBatteryReplaceDate());
										entity.setSunPowerModel(rtuInfoEntity.getSunPowerModel());
										entity.setNetworkSimId(rtuInfoEntity.getNetworkSimId());
										entity.setNetworkPayer(rtuInfoEntity.getNetworkPayer());
										entity.setNetworkPayEndDate(rtuInfoEntity.getNetworkPayEndDate());
										entity.setSatelliteModel(rtuInfoEntity.getSatelliteModel());
										entity.setIsSatellite(rtuInfoEntity.getIsSatellite());
										entity.setRemark(rtuInfoEntity.getRemark());

										entity.setAdCode(rtuInfoEntity.getAdCode());
										entity.setAdName(rtuInfoEntity.getAdName());
										rtuBaseInfoService.updateById(entity);
									}else{
										rtuBaseInfoService.save(rtuInfoEntity);
										detailEntity.setSaveType(0);
									}
									rtuImportRecordDetailService.save(detailEntity);
								}
							}
							recordEntity.setImportStatus(1L);
							rtuImportRecordService.saveOrUpdate(recordEntity);
						}

//						Set<String> rtuCodes = rtuStatus.keySet();
//						for (String key : rtuCodes) {
//							Integer status = rtuStatus.get(key);
//							if (status == 0) {
//								LambdaQueryWrapper<RtuInfoEntity> wrapper = Wrappers.<RtuInfoEntity>query().lambda();
//								wrapper.eq(RtuInfoEntity::getIsDeleted, 0);
//								wrapper.eq(RtuInfoEntity::getRtuCode, key);
//								RtuInfoEntity entity = rtuBaseInfoService.getOne(wrapper);
//								if (null != entity) {
//									System.out.println("remove ********************");
//									System.out.println(JSON.toJSON(entity).toString());
//
//									rtuBaseInfoService.removeById(entity.getId());
//									RtuImportRecordDetailEntity detailEntity = new RtuImportRecordDetailEntity();
//									detailEntity.setRecordId(recordEntity.getId());
//									detailEntity.setRtuCode(entity.getRtuCode());
//									detailEntity.setRtuName(entity.getRtuName());
//									detailEntity.setSaveType(2);
//									rtuImportRecordDetailService.save(detailEntity);
//								}
//							}
//						}

					}
				}).sheet().doRead();
			} else {
				recordEntity.setImportStatus(2L);
				rtuImportRecordService.saveOrUpdate(recordEntity);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("RTU导入任务异常 {}", e.getMessage());
			try {
				recordEntity.setImportStatus(2L);
				rtuImportRecordService.saveOrUpdate(recordEntity);
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		return 0;
	}

	private static boolean isLetterDigit(String str) {
		String regex = "^[a-z0-9A-Z]+$";
		return str.matches(regex);
	}

	private Double tranformPos(String lng) {
		lng = lng
			.trim()
			.replace("g", "");
		if (lng.indexOf("o") >= 0 || lng.indexOf("°") >= 0 || lng.indexOf("°") >= 0 || lng.indexOf("°") >= 0) {
			lng = lng
				.trim()
				.replace("o", ":")
				.replace("°", ":")
				.replace("°", ":")
				.replace("°", ":")
				.replace("′", ":")
				.replace("'", ":")
				.replace("\"", "")
				.replace("″", "")
				.replace(".", ":");

			String[] lntArr = lng.split(":");
			Double result = 0D;
			if (lntArr.length == 1) {
				String[] lntArr2 = new String[3];
				lntArr2[0] = lntArr[0];
				lntArr2[1] = "0";
				lntArr2[2] = "0";
				lntArr = lntArr2;
			} else if (lntArr.length == 2) {
				String[] lntArr2 = new String[3];
				lntArr2[0] = lntArr[0];
				lntArr2[1] = lntArr[1];
				lntArr2[2] = "0";
				lntArr = lntArr2;
			}
			for (int i = lntArr.length; i > 0; i--) {
				String str = lntArr[i - 1];
				if (null != str) {
					str = str.trim();
					if (null != str && str.length() > 0) {
						double v = Double.parseDouble(str);
						if (i == 1) {
							result = v + result;
						} else {
							result = (result + v) / 60;
						}
					} else {
						double v = 0.0;
						if (i == 1) {
							result = v + result;
						} else {
							result = (result + v) / 60;
						}
					}
				} else {
					double v = 0.0;
					if (i == 1) {
						result = v + result;
					} else {
						result = (result + v) / 60;
					}
				}
			}
			return result;
		} else {
			String lng2 = lng;
			lng2 = lng2
				.trim()
				.replace(".", ":");
			String[] vs = lng2.split(":");
			if (vs.length > 1) {
				lng = lng
					.trim()
					.replace("o", ":")
					.replace("°", ":")
					.replace("°", ":")
					.replace("°", ":")
					.replace("′", ":")
					.replace("'", ":")
					.replace("\"", "")
					.replace("″", "")
					.replace(".", ":");

				String[] lntArr = lng.split(":");
				Double result = 0D;
				if (lntArr.length == 1) {
					String[] lntArr2 = new String[3];
					lntArr2[0] = lntArr[0];
					lntArr2[1] = "0";
					lntArr2[2] = "0";
					lntArr = lntArr2;
				} else if (lntArr.length == 2) {
					String[] lntArr2 = new String[3];
					lntArr2[0] = lntArr[0];
					lntArr2[1] = lntArr[1];
					lntArr2[2] = "0";
					lntArr = lntArr2;
				}
				for (int i = lntArr.length; i > 0; i--) {
					String str = lntArr[i - 1];
					if (null != str) {
						str = str.trim();
						if (null != str && str.length() > 0) {
							double v = Double.parseDouble(str);
							if (i == 1) {
								result = v + result;
							} else {
								result = (result + v) / 60;
							}
						} else {
							double v = 0.0;
							if (i == 1) {
								result = v + result;
							} else {
								result = (result + v) / 60;
							}
						}
					} else {
						double v = 0.0;
						if (i == 1) {
							result = v + result;
						} else {
							result = (result + v) / 60;
						}
					}
				}
				return result;
			} else {
				double v = Double.parseDouble(lng);
				return v;
			}

		}

	}
}