package com.mvc.service.test;

import com.mvc.dao.StationAccMapper;
import com.mvc.entity.StationAcc;
import com.mvc.entity.StationAccExample;
import com.mvc.entity.TAiread;
import com.mvc.model.CustomizeGroupModel;
import com.mvc.model.StationAccModel;
import com.mvc.model.TAicbqCfgModel;
import com.mvc.model.TaskRemoteCfgModel;
import com.mvc.netty.SimpleResponse;
import com.mvc.service.copy.TAireadService;
import com.mvc.service.taizhang.CustomizeGroupService;
import com.mvc.service.taizhang.StationAccService;
import com.mvc.service.taizhang.TAicbqCfgService;
import com.mvc.service.taizhang.TaskRemoteCfgService;
import com.mvc.utils.PinyinUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 帮智慧院和运维厂商整理台账使用,非业务
 */
@Service
@Transactional
public class TaiZhangTest {
    private static Logger log = LoggerFactory.getLogger(SimpleResponse.class);

    @Resource
    private StationAccService stationAccService;

    private static boolean isRunning;

    @Resource
    private StationAccMapper stationAccMapper;

    @Resource
    private TAicbqCfgService aiCfgService;

    @Resource
    private TaskRemoteCfgService taskRemoteCfgService;

    @Resource
    private TAireadService aireadService;

    private List<String> mIgnoreStationCodes = new ArrayList<>(List.of());

    public void test() {
        if (isRunning) {
            return;
        }
        isRunning = true;
        String path = "C:/Users/Administrator/Desktop/tz/1113.xlsx";

        try {
            FileInputStream in = new FileInputStream(path);
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            XSSFSheet sheet1 = workbook.getSheetAt(0);//sheet1 全量水表台账
            XSSFSheet sheet2 = workbook.getSheetAt(1);//sheet2 计量设施

            List<JlssModel> jlssModels = readJlss(sheet2);

            int lastRowNum = sheet1.getLastRowNum();
            for (int i = 2; i < lastRowNum; i++) {
                XSSFRow currentrow = sheet1.getRow(i);//行
                XSSFCell cell = currentrow.getCell(0);//水表号单位格
                String sbh = cell.getStringCellValue();//第一列水表号的值
                List<JlssModel> tmps = jlssModels.stream().filter(s -> s.getXtsbh().equals(sbh)).collect(Collectors.toList());//匹配到的计量设施
                String qskbh = getQskbhString(tmps);

                cell = currentrow.getCell(1);//取水口编号单元格
                if (cell == null) {
                    cell = currentrow.createCell(1);
                }
                cell.setCellValue(qskbh);
            }

            FileOutputStream fileOutputStream = new FileOutputStream("C:/Users/Administrator/Desktop/tz/1113-1.xlsx");
            workbook.write(fileOutputStream);
            //关闭输出流
            fileOutputStream.close();
            System.out.println("文件生成完毕！");

            in.close();
            workbook.close();
            isRunning = false;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    //拼接取水口编号字符串
    private String getQskbhString(List<JlssModel> tmps) {
        StringBuilder sb = new StringBuilder();
        for (JlssModel model : tmps) {
            if (StringUtils.isNotEmpty(model.getQskbh())) {
                sb.append(model.getQskbh());
                sb.append(",");
            }
        }
        String value = sb.toString();
        if (value.length() > 0) {
            value = value.substring(0, value.length() - 1);//去逗号
        }
        return value;
    }

    private List<JlssModel> readJlss(XSSFSheet sheet) {
        List<JlssModel> jlssModels = new ArrayList<>();
        int lastRowNum = sheet.getLastRowNum();

        try {
            for (int i = 1; i < lastRowNum; i++) {
                JlssModel model = new JlssModel();
                XSSFRow currentrow = sheet.getRow(i);//行
                XSSFCell cell = currentrow.getCell(0);//水表号单位格
                String qsxkzh = cell.getStringCellValue();//第一列水表号的值
                model.setQsxkzh(qsxkzh == null ? "" : qsxkzh);

                cell = currentrow.getCell(2);//水表号单位格
                if (cell != null) {
                    String qskbh = cell.getStringCellValue();//第2列水表号的值
                    model.setQskbh(qskbh == null ? "" : qskbh);
                } else {
                    model.setQskbh("");
                }

                cell = currentrow.getCell(3);//系统水表号
                if (cell != null) {
                    String xtsbh = cell.getStringCellValue();//第4列水表号的值
                    model.setXtsbh(xtsbh == null ? "" : xtsbh);
                } else {
                    model.setXtsbh("");
                }

                jlssModels.add(model);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return jlssModels;
    }

    //数据汇总第二步
    public void sjhzdeb() {
        if (isRunning) {
            return;
        }
        isRunning = true;
        String path = "C:/Users/Administrator/Desktop/tz/1114.xlsx";

        try {
            FileInputStream in = new FileInputStream(path);
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            XSSFSheet sheet1 = workbook.getSheetAt(0);//sheet1 全量水表台账
            XSSFSheet sheet2 = workbook.getSheetAt(2);//sheet2 计量设施

            List<JlssModel> jlssModels = readQltz(sheet1);

            int lastRowNum = sheet2.getLastRowNum();
            for (int i = 2; i < lastRowNum; i++) {
                XSSFRow currentrow = sheet2.getRow(i);//行
                XSSFCell cell = currentrow.getCell(5);//水表号单位格
                if (cell == null) continue;
                String qskbh = cell.getStringCellValue();//第五列取水口编号
                if (StringUtils.isEmpty(qskbh)) continue;

                JlssModel jlssModel = getModelByQskbh(qskbh, jlssModels);
                if (jlssModel != null && StringUtils.isNotEmpty(jlssModel.getYczbm())) {
                    cell = currentrow.getCell(3);//取水口编号单元格
                    if (cell == null) {
                        cell = currentrow.createCell(3);
                    }
                    cell.setCellValue(jlssModel.getYczbm());
                }
            }

            FileOutputStream fileOutputStream = new FileOutputStream("C:/Users/Administrator/Desktop/tz/1114-1.xlsx");
            workbook.write(fileOutputStream);
            //关闭输出流
            fileOutputStream.close();
            System.out.println("文件生成完毕！");

            in.close();
            workbook.close();
            isRunning = false;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private JlssModel getModelByQskbh(String qskbh, List<JlssModel> jlssModels) {
        for (JlssModel model : jlssModels) {
            if (StringUtils.isNotEmpty(model.getQskbh())) {
                String[] arr = model.getQskbh().split(",");
                for (String str : arr) {
                    if (str.equals(qskbh)) {
                        return model;
                    }
                }
            }
        }
        return null;
    }

    //读取全量台账
    private List<JlssModel> readQltz(XSSFSheet sheet) {
        List<JlssModel> jlssModels = new ArrayList<>();
        int lastRowNum = sheet.getLastRowNum();

        try {
            for (int i = 2; i < lastRowNum; i++) {
                JlssModel model = new JlssModel();
                XSSFRow currentrow = sheet.getRow(i);//行
                XSSFCell cell = currentrow.getCell(0);//水表号单位格
                String qskbh = cell.getStringCellValue();//第一列取水口的值
                model.setQskbh(qskbh == null ? "" : qskbh);

                cell = currentrow.getCell(17);//站码单位格
                if (cell != null) {
                    String yczbm = cell.getStringCellValue();//第2列水表号的值
                    model.setYczbm(yczbm == null ? "" : yczbm);
                } else {
                    model.setYczbm("");
                }

                jlssModels.add(model);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return jlssModels;
    }

    //根据Excle台账更新数据库遥测站台账表
    public void updateStationAcc() {
        try {
            List<JlssModel> jlssModels = readNewAccTable();
            int i = 0;
            for (JlssModel model : jlssModels) {
                log.info("数据更新[" + i++ + "]:" + model.getQskbh());
                if (StringUtils.isEmpty(model.getQskbh()) || StringUtils.isEmpty(model.getYczbm())) {
                    log.info("台账表格信息不完整:" + model.getQskbh() + "--" + model.getYczbm());
                    continue;
                }

                String oldCode = model.getQskbh();
                String newCode = model.getYczbm();
                String stationName = model.getXtsbh();
                StationAccModel accModel = stationAccService.findStationAccByStationCode(oldCode);
                if (accModel == null) {
                    log.info("平台中未匹配到台账:" + oldCode);
                    continue;
                }
                accModel.setStationCode(newCode);
                if (newCode.length() < 10) {
                    log.info("新站码长度不足10位:" + newCode);
                }
                if (StringUtils.isNotEmpty(stationName) && !accModel.getStationName().equals(stationName)) {//表格中名称不为空,并且
                    accModel.setStationName(stationName);
                    accModel.setStationName(PinyinUtil.getPinYinHeadChar(stationName,true));
                    //accModel.setStationPy(ChineseToEn.getAllFirstLetter(stationName));//重新生成拼音码
                }

                StationAcc entity = new StationAcc();
                BeanUtils.copyProperties(accModel, entity);

                //注意,提供的表格中不能有重复的遥测站编码
                if (stationAccMapper.updateByPrimaryKey(entity) > 0) {
                    log.info("台账更新成功[" + accModel.getId() + "]:" + oldCode + "-->" + newCode);
                } else {
                    log.info("台账更新失败[" + accModel.getId() + "]:" + oldCode + "-->" + newCode);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    //读取新的台账表,包含新旧遥测站编码
    private List<JlssModel> readNewAccTable() {
        String path = "C:/Users/Administrator/Desktop/tz/kh1124.xlsx";//台账表路径
        List<JlssModel> jlssModels = new ArrayList<>();
        int startRowIndex = 2;
        int oldColIndex = 2;
        int newColIndex = 3;
        int stationNameColIndex = 1;

        try {
            FileInputStream in = new FileInputStream(path);
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            XSSFSheet sheet = workbook.getSheetAt(0);//sheet1 全量水表台账

            int lastRowNum = sheet.getLastRowNum();

            for (int i = startRowIndex; i < lastRowNum; i++) {
                JlssModel model = new JlssModel();
                XSSFRow currentrow = sheet.getRow(i);//行
                XSSFCell cell = currentrow.getCell(oldColIndex);//
                cell.setCellType(CellType.STRING);//解决异常:Cannot get a STRING value from a NUMERIC cell
                String oldCode = cell.getStringCellValue();//
                oldCode = oldCode.trim().replace("\'", "");

                if (oldCode.length() < 10) {
                    oldCode = StringUtils.leftPad(oldCode, 10, '0');
                } else if (oldCode.length() > 10) {
                    oldCode = oldCode.substring(oldCode.length() - 10);
                }
                model.setQskbh(oldCode == null ? "" : oldCode);//旧遥测站编码

                cell = currentrow.getCell(newColIndex);//新的遥测站编码
                if (cell != null) {
                    cell.setCellType(CellType.STRING);//解决异常:Cannot get a STRING value from a NUMERIC cell
                    String yczbm = cell.getStringCellValue();//第2列水表号的值
                    yczbm = yczbm.trim().replace("\'", "");
                    model.setYczbm(yczbm == null ? "" : yczbm);
                } else {
                    model.setYczbm("");
                }

                cell = currentrow.getCell(stationNameColIndex);//新遥测站名称
                if (cell != null) {
                    String yczmc = cell.getStringCellValue();//
                    yczmc = yczmc.trim().replace("\'", "");
                    model.setXtsbh(yczmc == null ? "" : yczmc);
                } else {
                    model.setXtsbh("");
                }
                jlssModels.add(model);
            }
            workbook.close();
            in.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return jlssModels;
    }

    @Resource
    private CustomizeGroupService customizeGroupService;

    public void updateCustomizeGroup() {
        List<CustomizeGroupModel> list = customizeGroupService.findAllCustomizeGroup(new CustomizeGroupModel());
        for (CustomizeGroupModel model : list) {
             if (StringUtils.isEmpty(model.getStationCodes())) {
                continue;
            }
            String[] codes = model.getStationCodes().split(",");
            List<String> newCodes = new ArrayList<>();
            for (String code : codes) {
                StationAcc acc = findStationAccByOldCode(code);
                if (acc == null) {//不需要改
                    newCodes.add(code);
                } else {//匹配到了
                    newCodes.add(acc.getStationCode());
                }
            }
            model.setStationCodes(StringUtils.join(newCodes,","));
            customizeGroupService.updateCustomizeGroup(model);
        }
    }

    private StationAcc findStationAccByOldCode(String code) {
        StationAccExample example = new StationAccExample();
        StationAccExample.Criteria criteria = example.createCriteria();
        criteria.andMeterIdEqualTo(code);
        List<StationAcc> accList = stationAccMapper.selectByExample(example);
        if (accList != null && accList.size() > 0) {
            return accList.get(0);
        }
        return null;
    }

    /**
     * 修改所有站点设备的IP2和IP3(服务器迁移时使用,主要针对非固定IP2类型设备)
     *
     * @param ip2Port2
     * @return
     */
    public int generateSystemBatchTasks(String ip2Port2) {
        List<StationAcc> findlist = stationAccMapper.selectByExample(null);

        Date now = new Date();
        long gl = 1000 * 60 * 60 * 24 * 7;//过滤5天没有数据的

        int num = 0;
        for (StationAcc acc : findlist) {
            /*if (!acc.getDeviceType().equals(DeviceType.WATER_METER.getCode())) {//非水表不处理
                continue;
            }

            if (!acc.getMeterId().equals("1")) {//非水务站点暂不处理
                continue;
            }

            //部分跳过的站点(1天一条的)
            if (mIgnoreStationCodes.contains(acc.getStationCode())) {
                continue;
            }*/

            String sn;
            TAiread tAiread = aireadService.findlastTAireadBydatatime(acc.getStationCode(), now);
            if (tAiread == null || StringUtils.isEmpty(tAiread.getDeviceSn())) continue;

            long sc = now.getTime() - tAiread.getSampleTime().getTime();
            if (sc < gl) {
                sn = tAiread.getDeviceSn();
            } else continue;

            String version = tAiread.getSoftVer();

            TaskRemoteCfgModel cfgModel = new TaskRemoteCfgModel();
            //关IP1,启用发大图
            //cfgModel.setSampleInterval(1440);
            cfgModel.setMaxDiscreteDuration(10);
            //cfgModel.setUpWholeImgWeekRule("1111111");
            cfgModel.setCreateMan("系统配置");

            TAicbqCfgModel aicbqCfg = aiCfgService.findTAicbqCfgModel(tAiread.getDeviceSn(), "", "1");
            if (aicbqCfg == null || aicbqCfg.getMaxDiscreteDuration() == null) {
                continue;
            }
            if (aicbqCfg.getMaxDiscreteDuration() > 0 && aicbqCfg.getMaxDiscreteDuration() < 10) {//符合时间范围不处理
                continue;
            }

            if (StringUtils.isNumeric(version)) {
                continue;
                /*long mv = Long.parseLong(version);
                if (mv < 220926190734L) {//都指向运维服务器,使用无效地址耗时会长
                    cfgModel.setIp1Port1("82.157.38.254:12323");
                    cfgModel.setIp3Port3("82.157.38.254:12323");
                } else {
                    cfgModel.setIp1Port1("110.43.204.229:0");
                    cfgModel.setEnableSendBjzhy("0");
                }*/
            } else {//新版本的设备
                /*cfgModel.setIp1Port1("110.43.204.229:0");
                cfgModel.setEnableSendBjzhy("0");*/
            }

            num++;
            cfgModel.setId(null);
            cfgModel.setDeviceSn(sn);
            taskRemoteCfgService.saveTaskRemoteCfg(cfgModel);
        }

        return num;
    }

    public class JlssModel {
        private String qsxkzh;//取水许可证号
        private String qskbh;//取水口编号(/旧遥测站编码)
        private String xtsbh;//系统水表号(/新遥测站名称)
        private String yczbm;//遥测站编码(/新遥测站编码)

        public String getQsxkzh() {
            return qsxkzh;
        }

        public void setQsxkzh(String qsxkzh) {
            this.qsxkzh = qsxkzh;
        }

        public String getQskbh() {
            return qskbh;
        }

        public void setQskbh(String qskbh) {
            this.qskbh = qskbh;
        }

        public String getXtsbh() {
            return xtsbh;
        }

        public void setXtsbh(String xtsbh) {
            this.xtsbh = xtsbh;
        }

        public String getYczbm() {
            return yczbm;
        }

        public void setYczbm(String yczbm) {
            this.yczbm = yczbm;
        }
    }
}
