package com.yfd.platform.modules.basedata.service.impl;

import cn.hutool.core.util.DesensitizedUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yfd.platform.modules.basedata.domain.*;
import com.yfd.platform.modules.basedata.mapper.*;
import com.yfd.platform.modules.basedata.service.ISubstationService;
import com.yfd.platform.system.domain.SysUser;
import com.yfd.platform.system.service.IUserService;
import com.yfd.platform.utils.FileUtil;
import com.yfd.platform.utils.SecurityUtils;
import com.yfd.platform.utils.StringUtils;
import freemarker.template.utility.NullArgumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 * 变电站 服务实现类
 * </p>
 *
 *
 * @since 2023-03-28
 */
@Service
@Transactional
public class SubstationServiceImpl extends ServiceImpl<SubstationMapper, Substation> implements ISubstationService {

    @Resource
    private SubstationMapper substationMapper;

    @Resource
    private SubstationAreaMapper substationAreaMapper;

    @Resource
    private SubstationBayMapper substationBayMapper;

    @Resource
    private IUserService userService;

    @Resource
    private SubstationMaindeviceMapper substationMaindeviceMapper;

    @Resource
    private SubstationDeviceMapper substationDeviceMapper;
    /**
     * 用户头像图片路径
     */
    @Value("${file-space.system}")
    private String systempath;

    /**********************************
     * 用途说明: 获取变电站区域间隔导航树
     * 参数说明
     * 返回值说明: java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     ***********************************/
    @Override
    public List<Map<String, Object>> getSubstationTree(String stationId) {
        List<Map<String, Object>> stationList = new ArrayList<>();
        Substation substation = this.substationMapper.selectById(stationId);
        List<Map<String, Object>> substationBays = substationBayMapper.getSubstationBayInfo(stationId);
        Map<String, List<Map<String, Object>>> collect =
                substationBays.stream().collect(Collectors.groupingBy(s -> s.get("areaName").toString()));
        Map<String, Object> stationMap = new HashMap<>();
        stationMap.put("name", substation.getStationName());
        List<Map<String, Object>> treeList = new ArrayList<>();
        for (String areaName : collect.keySet()) {
            Map<String, Object> areaMap = new HashMap<>();
            areaMap.put("name", areaName);
            List<Map<String, Object>> substationBays1 = collect.get(areaName);
            areaMap.put("children", substationBays1);
            treeList.add(areaMap);
        }
        stationMap.put("children", treeList);
        stationList.add(stationMap);
        return stationList;
    }
    //    @Override
    //    public List<Map<String, Object>> getSubstationTree(String stationId) {
    //        Substation substation = this.substationMapper.selectById(stationId);
    //        List<SubstationArea> substationAreas =
    //                substationAreaMapper.selectList(new LambdaQueryWrapper<SubstationArea>().eq
    //                (SubstationArea::getDatastatus, "1").eq(SubstationArea::getStationCode, substation
    //                .getStationCode()).orderByAsc(SubstationArea::getCustom1));
    //        List<Map<String, Object>> stationList = new ArrayList<>();
    //        Map<String, Object> stationMap = new HashMap<>();
    //        stationMap.put("name", substation.getStationName());
    //        List<Map<String, Object>> treeList = new ArrayList<>();
    //        for (SubstationArea substationArea : substationAreas) {
    //            Map<String, Object> areaMap = new HashMap<>();
    //            List<SubstationBay> substationBays =
    //                    substationBayMapper.selectList(new LambdaQueryWrapper<SubstationBay>().eq
    //                    (SubstationBay::getDatastatus, "1").eq(SubstationBay::getAreaId, substationArea.getAreaId()
    //                    ).orderByAsc(SubstationBay::getCustom1));
    //            if (substationBays == null || substationBays.size() <= 0) {
    //                continue;
    //            }
    //            areaMap.put("name", substationArea.getAreaName());
    //            List<Map<String, Object>> bayList = new ArrayList<>();
    //            for (SubstationBay substationBay : substationBays) {
    //                Map<String, Object> bayMap = new HashMap<>();
    //                Integer count =
    //                        substationMaindeviceMapper.selectCount(new LambdaQueryWrapper<SubstationMaindevice>()
    //                        .eq(SubstationMaindevice::getBayId, substationBay.getBayId()));
    //                if (count == null) {
    //                    count = 0;
    //                }
    //                Integer deviceCount =
    //                        substationDeviceMapper.selectCount(new LambdaQueryWrapper<SubstationDevice>().eq
    //                        (SubstationDevice::getBayId, substationBay.getBayId()));
    //                if (deviceCount == null) {
    //                    deviceCount = 0;
    //                }
    //                bayMap.put("bayId", substationBay.getBayId());
    //                bayMap.put("name", substationBay.getBayName());
    //                bayMap.put("deviceCount", deviceCount);
    //                bayMap.put("count", count);
    //                bayMap.put("stationCode", substationBay.getStationCode());
    //                bayMap.put("stationName", substationBay.getStationName());
    //                bayMap.put("areaId", substationBay.getAreaId());
    //                bayMap.put("areaName", substationBay.getAreaName());
    //                bayMap.put("orderNo", substationBay.getCustom1());
    //                bayMap.put("stationId", substation.getStationId());
    //                bayList.add(bayMap);
    //            }
    //            areaMap.put("children", bayList);
    //            treeList.add(areaMap);
    //        }
    //        stationMap.put("children", treeList);
    //        stationList.add(stationMap);
    //        return stationList;
    //    }

    /**********************************
     * 用途说明: 获取变电站区域导航树
     * 参数说明
     * 返回值说明: java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     ***********************************/
    @Override
    public List<Map<String, Object>> getSubstationAreaTree(String stationId) {
        Substation substation = this.substationMapper.selectById(stationId);
        List<SubstationArea> list =
                substationAreaMapper.selectList(new LambdaQueryWrapper<SubstationArea>().eq(SubstationArea::getDatastatus, "1").eq(SubstationArea::getStationCode, substation.getStationCode()).orderByAsc(SubstationArea::getCustom1));
        return buildTreeArea(list);
    }


    /**********************************
     * 用途说明: 获取区域下所有间隔
     * 参数说明
     * 返回值说明: java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     ***********************************/
    @Override
    public List<Map<String, Object>> getSubstationBayByArea(String stationCode, String areaId, String bayName) {
        LambdaQueryWrapper<SubstationBay> queryWrapper = new LambdaQueryWrapper<>();
        if (StrUtil.isNotBlank(stationCode)) {
            queryWrapper.eq(SubstationBay::getStationCode, stationCode);
        }
        if (StrUtil.isNotBlank(bayName)) {
            queryWrapper.like(SubstationBay::getBayName, bayName);
        }
        if (StrUtil.isNotBlank(areaId)) {
            queryWrapper.eq(SubstationBay::getAreaId, areaId);
        }
        queryWrapper.eq(SubstationBay::getDatastatus, "1").select(SubstationBay::getAreaId,SubstationBay::getAreaName,SubstationBay::getBayId,SubstationBay::getBayName,SubstationBay::getStationCode,SubstationBay::getStationName).orderByAsc(SubstationBay::getCustom1);
        List<Map<String, Object>> list = substationBayMapper.selectMaps(queryWrapper);
        return list;
    }

    /**********************************
     * 用途说明: 修改变电站
     * 参数说明 substation
     * 返回值说明: boolean
     ***********************************/
    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean updateSubstation(Substation substation) {
        LambdaQueryWrapper<Substation> queryWrapper = new LambdaQueryWrapper<>();
        String stationId = substation.getStationId();
        String stationCode = substation.getStationCode();
        queryWrapper.ne(Substation::getStationId, stationId).eq(Substation::getStationCode, stationCode);
        int count = this.count(queryWrapper);
        if (count > 0) {
            throw new RuntimeException("当前变电站已经存在");
        }
        //        LambdaQueryWrapper<SubstationArea> areaQueryWrapper = new LambdaQueryWrapper<>();
        //        areaQueryWrapper.eq(SubstationArea::getStationCode, stationCode);
        //        int count1 = substationAreaMapper.selectCount(areaQueryWrapper);
        //        if (count1 > 0) {
        //            throw new RuntimeException("当前变电站有区域关联不能修改");
        //        }
        //        int count2 =
        //                substationPatroldeviceMapper.selectCount(new LambdaQueryWrapper<SubstationPatroldevice>().eq(SubstationPatroldevice::getStationId, stationId));
        //        if (count2 > 0) {
        //            throw new RuntimeException("当前变电站有巡视设备关联不能修改");
        //        }
        String username = userService.getUsername();
        substation.setLastmodifier(username);
        substation.setLastmodifydate(LocalDateTime.now());
        return this.updateById(substation);

    }

    @Override
    public int getStationOnline(String stationcode) {
        int online = 1;
        LambdaQueryWrapper<Substation> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(Substation::getStationCode, stationcode).eq(Substation::getIsStationFlag, "1").eq(Substation::getOnline, "0");
        int count = substationMapper.selectCount(queryWrapper);
        if (count > 0) {
            online = 0;//边缘节点不在线
        }
        return online;
    }

    /**********************************
     * 用途说明: 获取变电站树
     * 参数说明
     * 返回值说明: java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     ***********************************/
    @Override
    public List<Map<String, Object>> getSubstationBayTree(String name, Set<String> orgCodeByUser) {
        SysUser userInfo = userService.getUserInfo();
        Integer usertype = userInfo.getUsertype();
        LambdaQueryWrapper<Substation> queryWrapper = new LambdaQueryWrapper<>();
        if (usertype != 0) {
            if (orgCodeByUser == null || orgCodeByUser.size() == 0) {
                return null;
            } else {
                queryWrapper.in(Substation::getStationCode, orgCodeByUser);
            }
        }
        // 如果是带参数的就不返回树
        if (StrUtil.isNotBlank(name)) {
            queryWrapper.like(Substation::getStationName, name).orderByAsc(Substation::getCustom1);
            List<Map<String, Object>> list = this.listMaps(queryWrapper);
            list.forEach(l -> {
                if (ObjectUtil.isNotEmpty(l.get("contactPhone"))) {
                    String contactPhone = l.get("contactPhone").toString();
                    l.put("contactPhone", DesensitizedUtil.mobilePhone(contactPhone));
                }
            });
            list.forEach(m -> m.put("type", "变电站"));
            List<Map<String, Object>> allMap = new ArrayList<>(list);
            LambdaQueryWrapper<SubstationArea> areaQueryWrapper = new LambdaQueryWrapper<>();
            areaQueryWrapper.like(SubstationArea::getAreaName, name).orderByAsc(SubstationArea::getCustom1);
            List<Map<String, Object>> list1 = substationAreaMapper.selectMaps(areaQueryWrapper);
            list1.forEach(m -> m.put("type", "区域"));
            allMap.addAll(list1);
            LambdaQueryWrapper<SubstationBay> bayQueryWrapper = new LambdaQueryWrapper<>();
            bayQueryWrapper.like(SubstationBay::getBayName, name).orderByAsc(SubstationBay::getCustom1);
            List<Map<String, Object>> list2 = substationBayMapper.selectMaps(bayQueryWrapper);
            list2.forEach(m -> m.put("type", "间隔"));
            allMap.addAll(list2);
            return allMap;
        }
        queryWrapper.orderByAsc(Substation::getCustom1);
        List<Map<String, Object>> list = this.listMaps(queryWrapper);
        list.forEach(l -> {
            if (ObjectUtil.isNotEmpty(l.get("contactPhone"))) {
                String contactPhone = l.get("contactPhone").toString();
                l.put("contactPhone", DesensitizedUtil.mobilePhone(contactPhone));
            }
        });
        List<Map<String, Object>> listMap = new ArrayList<>();
        for (Map<String, Object> map : list) {
            String stationCode = (String) map.get("stationCode");
            LambdaQueryWrapper<SubstationArea> areaQueryWrapper = new LambdaQueryWrapper<>();
            // 关联区间
            areaQueryWrapper.eq(SubstationArea::getStationCode, stationCode);
            areaQueryWrapper.orderByAsc(SubstationArea::getCustom1);
            List<Map<String, Object>> areaList = substationAreaMapper.selectMaps(areaQueryWrapper);
            List<Map<String, Object>> listAreaMap = new ArrayList<>();
            for (Map<String, Object> areaMap : areaList) {
                String areaId = (String) areaMap.get("areaId");
                LambdaQueryWrapper<SubstationBay> bayQueryWrapper = new LambdaQueryWrapper<>();
                // 关联间隔
                bayQueryWrapper.eq(SubstationBay::getAreaId, areaId);
                bayQueryWrapper.orderByAsc(SubstationBay::getCustom1);
                List<Map<String, Object>> list1 = substationBayMapper.selectMaps(bayQueryWrapper);
                List<Map<String, Object>> listBay = new ArrayList<>();
                for (Map<String, Object> stringObjectMap : list1) {
                    stringObjectMap.put("type", "间隔");
                    listBay.add(stringObjectMap);
                }
                if (listBay.size() > 0) {
                    areaMap.put("children", listBay);
                }
                areaMap.put("type", "区域");
                listAreaMap.add(areaMap);
            }
            if (listAreaMap.size() > 0) {
                map.put("children", listAreaMap);
            }
            map.put("type", "变电站");
            listMap.add(map);
        }
        return listMap;
    }

    /***********************************
     * 用途说明：上传图片
     * 参数说明
     * image        图片
     * 返回值说明:   是否上传成功
     ***********************************/
    @Override
    public String uploadIcon(MultipartFile image) {
        // 图片存储地址
        String imgPath = systempath + "station";
        String iconname =
                IdUtil.fastSimpleUUID() + "." + FileUtil.getExtensionName(image.getOriginalFilename());
        //上传图标并获取图标名称 (图片改为png格式)
        return Objects.requireNonNull(FileUtil.upload(image, imgPath, iconname)).getName();
    }

    /**********************************
     * 用途说明: 删除图片
     * 参数说明 id id
     * 参数说明 filename 图片名称
     * 返回值说明: boolean
     ***********************************/
    @Override
    public boolean deleteImage(String id, String filename) {
        // 图片存储地址
        String imgPath = systempath + "station" + File.separator + filename;
        if (StrUtil.isBlank(id)) {
            FileUtil.del(imgPath);
            return true;
        }
        Substation substation = this.getById(id);
        String images = substation.getImages();
        if (StrUtil.isBlank(images)) {
            return true;
        }
        String[] split = images.split(",");
        List<String> stringList = new ArrayList<>(split.length);
        stringList.addAll(Arrays.asList(split));
        stringList.remove(filename);
        String img = StringUtils.join(stringList, ",");
        substation.setImages(img);
        boolean ok = this.updateById(substation);
        if (ok) {
            FileUtil.del(imgPath);
            return true;
        } else {
            return false;
        }
    }

    @Override
    public boolean cancelImage(String id, String images) {
        // 图片存储地址
        String imgPath = systempath + "station" + File.separator;
        if (StrUtil.isBlank(images)) {
            return true;
        }
        String[] split = images.split(",");
        if (StrUtil.isBlank(id)) {
            for (String img : split) {
                String filePath = imgPath + img;
                FileUtil.del(filePath);
            }
            return true;
        }
        Substation substation = this.getById(id);
        String image = substation.getImages();
        String[] split1 = image.split(",");
        List<String> strImg = new ArrayList<>(Arrays.asList(split1));
        for (String img : split) {
            if (strImg.contains(img)) {
                continue;
            }
            String filePath = imgPath + img;
            FileUtil.del(filePath);
        }
        return true;
    }

    /**********************************
     * 用途说明: 删除变电站
     * 参数说明 stationId
     * 返回值说明: boolean
     ***********************************/
    @Transactional(rollbackFor = Exception.class)
    @Override
    public boolean deleteSubstation(String stationId) {
        Substation substation = substationMapper.selectById(stationId);
        Integer integer =
                substationAreaMapper.selectCount(new LambdaQueryWrapper<SubstationArea>().eq(SubstationArea::getStationCode, substation.getStationCode()));
        if (integer > 0) {
            throw new RuntimeException("当前变电站已经绑定区域无法删除");
        }


        int i = substationMapper.deleteById(stationId);
        if (i <= 0) {
            return false;
        }
        List<Substation> list = this.list(new LambdaQueryWrapper<Substation>().orderByAsc(Substation::getCustom1));
        for (int i1 = 0; i1 < list.size(); i1++) {
            Substation substation1 = list.get(i1);
            substation1.setCustom1(i1 + 1);
            this.updateById(substation1);
        }
//        String stationCode = substation.getStationCode();
//        List<SubstationArea> substationAreas =
//                substationAreaMapper.selectList(new LambdaQueryWrapper<SubstationArea>().eq(SubstationArea::getStationCode,
//                        stationCode));
//        for (SubstationArea substationArea : substationAreas) {
//            String areaId = substationArea.getAreaId();
//            this.deleteSubstationArea(areaId);
//        }
        return true;
    }

    /**********************************
     * 用途说明: 删除区域
     * 参数说明 stationId
     * 返回值说明: boolean
     ***********************************/
    @Transactional(rollbackFor = Exception.class)
    @Override
    public boolean deleteSubstationArea(String areaId) {

        Integer integer =
                substationBayMapper.selectCount(new LambdaQueryWrapper<SubstationBay>().eq(SubstationBay::getAreaId, areaId));
        if (integer > 0) {
            throw new RuntimeException("当前区域已经绑定间隔无法删除");
        }
        int i = substationAreaMapper.deleteById(areaId);
        if (i <= 0) {
            return false;
        }
        List<SubstationArea> substationAreas =
                substationAreaMapper.selectList(new LambdaQueryWrapper<SubstationArea>().orderByAsc(SubstationArea::getCustom1));
        for (int i1 = 0; i1 < substationAreas.size(); i1++) {
            SubstationArea substationArea = substationAreas.get(i1);
            substationArea.setCustom1(i1 + 1);
            substationAreaMapper.updateById(substationArea);
        }

//        LambdaQueryWrapper<SubstationBay> queryWrapper = new LambdaQueryWrapper<>();
//        queryWrapper.eq(SubstationBay::getAreaId, areaId);
//        substationBayMapper.delete(queryWrapper);
//        List<SubstationBay> substationBays =
//                substationBayMapper.selectList(new LambdaQueryWrapper<SubstationBay>().orderByAsc(SubstationBay::getCustom1));
//        for (int i1 = 0; i1 < substationBays.size(); i1++) {
//            SubstationBay substationBay = substationBays.get(i1);
//            substationBay.setCustom1(i1 + 1);
//            substationBayMapper.updateById(substationBay);
//        }
        return true;
    }

    /**********************************
     * 用途说明: 删除间隔
     * 参数说明 stationId
     * 返回值说明: boolean
     ***********************************/
    @Override
    public boolean deleteSubstationBay(String bayId) {
        Integer integer =
                substationMaindeviceMapper.selectCount(new LambdaQueryWrapper<SubstationMaindevice>().eq(SubstationMaindevice::getBayId, bayId));
        if (integer > 0) {
            throw new RuntimeException("当前间隔已经绑定主设备无法删除");
        }
        int i = substationBayMapper.deleteById(bayId);
        List<SubstationBay> substationBays =
                substationBayMapper.selectList(new LambdaQueryWrapper<SubstationBay>().orderByAsc(SubstationBay::getCustom1));
        for (int i1 = 0; i1 < substationBays.size(); i1++) {
            SubstationBay substationBay = substationBays.get(i1);
            substationBay.setCustom1(i1 + 1);
            substationBayMapper.updateById(substationBay);
        }
        return i > 0;
    }

    /**********************************
     * 用途说明: 修改变电站状态
     * 参数说明 stationId
     * 返回值说明: boolean
     ***********************************/
    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean setSubstationStatus(String stationId, String dataStatus) {
        Substation substation = substationMapper.selectById(stationId);
        substation.setDatastatus(dataStatus);
        substation.setLastmodifier(SecurityUtils.getCurrentUsername());
        substation.setLastmodifydate(LocalDateTime.now());
        int i = substationMapper.updateById(substation);
        if ("0".equals(dataStatus)) {
            List<SubstationArea> substationAreas =
                    substationAreaMapper.selectList(new LambdaQueryWrapper<SubstationArea>().eq(SubstationArea::getStationCode, substation.getStationCode()));
            for (SubstationArea substationArea : substationAreas) {
                String areaId = substationArea.getAreaId();
                this.setSubstationAreaStatus(areaId, dataStatus);
            }
        }
        return i > 0;
    }

    /**********************************
     * 用途说明: 修改区域状态
     * 参数说明 stationId
     * 返回值说明: boolean
     ***********************************/
    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean setSubstationAreaStatus(String areaId, String dataStatus) {
        SubstationArea substationArea = substationAreaMapper.selectById(areaId);
        substationArea.setDatastatus(dataStatus);
        substationArea.setLastmodifier(SecurityUtils.getCurrentUsername());
        substationArea.setLastmodifydate(LocalDateTime.now());
        int i = substationAreaMapper.updateById(substationArea);
        if ("0".equals(dataStatus)) {
            List<SubstationBay> substationBays =
                    substationBayMapper.selectList(new LambdaQueryWrapper<SubstationBay>().eq(SubstationBay::getAreaId, areaId));
            for (SubstationBay substationBay : substationBays) {
                String bayId = substationBay.getBayId();
                this.setSubstationBayStatus(bayId, dataStatus);
            }
        } else {
            String stationCode = substationArea.getStationCode();
            Substation one = this.getOne(new LambdaQueryWrapper<Substation>().eq(Substation::getStationCode,
                    stationCode));
            String datastatus = one.getDatastatus();
            if (!dataStatus.equals(datastatus)) {
                one.setDatastatus(dataStatus);
                this.updateById(one);
            }
        }
        return i > 0;
    }

    /**********************************
     * 用途说明: 修改间隔状态
     * 参数说明 stationId
     * 返回值说明: boolean
     ***********************************/
    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean setSubstationBayStatus(String bayId, String dataStatus) {
        SubstationBay substationBay = substationBayMapper.selectById(bayId);
        String areaId = substationBay.getAreaId();
        // 如果间隔开启而区域状态未开启在，则将区域开启
        if ("1".equals(dataStatus)) {
            SubstationArea substationArea = substationAreaMapper.selectById(areaId);
            String datastatus = substationArea.getDatastatus();
            if (!dataStatus.equals(datastatus)) {
                substationArea.setDatastatus(dataStatus);
                substationAreaMapper.updateById(substationArea);
            }
            String stationCode = substationArea.getStationCode();
            List<Substation> list = this.list(new LambdaQueryWrapper<Substation>().eq(Substation::getStationCode,
                    stationCode));
            if (list != null && list.size() > 0) {
                Substation substation = list.get(0);
                String datastatus1 = substation.getDatastatus();
                if (!dataStatus.equals(datastatus1)) {
                    substation.setDatastatus(dataStatus);
                    this.updateById(substation);
                }
            }
        }
        substationBay.setDatastatus(dataStatus);
        substationBay.setLastmodifier(SecurityUtils.getCurrentUsername());
        substationBay.setLastmodifydate(LocalDateTime.now());
        int i = substationBayMapper.updateById(substationBay);
        return i > 0;
    }

    /**********************************
     * 用途说明: 导入文件
     * 参数说明 file
     * 返回值说明: java.lang.String
     ***********************************/
    @Override
    public String uploadExcel(MultipartFile file, String type) throws Exception {
        if (file == null) {
            throw new NullArgumentException();
        }
        String filename = file.getOriginalFilename();
        if (filename == null) {
            throw new NullArgumentException();
        }
        String a = "";
        try {
            // 调用解析文件方法
            a = parseRowCell(filename, file.getInputStream(), type);
            return a;
        } catch (IOException e) {
            throw new Exception(e.getMessage());
        }

    }

    /**********************************
     * 用途说明: 解析文件方法
     * 参数说明 filename 文件名
     * 参数说明 inputStream
     * 返回值说明: java.lang.String
     ***********************************/
    @Transactional(rollbackFor = Exception.class)
    public String parseRowCell(String filename, InputStream inputStream, String type) {
        try {
            Workbook workbook = null;
            // 判断excel的后缀，不同的后缀用不同的对象去解析
            // xls是低版本的Excel文件
            if (filename.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            }
            // xlsx是高版本的Excel文件
            if (filename.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
            if (workbook == null) {
                throw new RuntimeException("上传失败");
            }

            // 取到excel 中的第一张工作表
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                throw new NullArgumentException();
            }
            // 工作表中第一行是表头，不获取，从第二行开始获取
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                // 获取到这一行的数据
                Row row = sheet.getRow(rowNum);
                if (row == null || row.getCell(0) == null) {
                    continue;
                }
                if (StrUtil.isBlank(row.getCell(0).toString())) {
                    continue;
                }
                importExcel(row, type);
            }
            return "";
        } catch (IOException e) {
            return e.getMessage();
        }
    }

    private void importExcel(Row row, String type) {
        if ("01".equals(type)) {
            Substation substation = new Substation();
            substation.setDatastatus("1");
            // 变电站编号
            String stationCode = "";
            if (row.getCell(1) != null) {
                row.getCell(1).setCellType(CellType.STRING);
                stationCode = row.getCell(1).getStringCellValue().trim();
                substation.setStationCode(stationCode);
            }
            // 变电站名称
            String stationName = "";
            if (row.getCell(2) != null) {
                row.getCell(2).setCellType(CellType.STRING);
                stationName = row.getCell(2).getStringCellValue().trim();
                substation.setStationName(stationName);
            }
            // 变电站IP
            String stationIp = "";
            if (row.getCell(4) != null) {
                row.getCell(4).setCellType(CellType.STRING);
                stationIp = row.getCell(4).getStringCellValue().trim();
                substation.setStationIp(stationIp);
            }
            // 变电站节点ID
            String stationId = "";
            if (row.getCell(5) != null) {
                row.getCell(5).setCellType(CellType.STRING);
                stationId = row.getCell(5).getStringCellValue().trim();
                substation.setStationId(stationId);
            }
            // 电压等级
            String voltLevel = "";
            if (row.getCell(6) != null) {
                row.getCell(6).setCellType(CellType.STRING);
                voltLevel = row.getCell(6).getStringCellValue().trim();
                substation.setVoltLevel(voltLevel);
            }
            // 所属省份
            String provinceName = "";
            if (row.getCell(7) != null) {
                row.getCell(7).setCellType(CellType.STRING);
                provinceName = row.getCell(7).getStringCellValue().trim();
                substation.setProvinceName(provinceName);
            }
            // 所属地市
            String cityName = "";
            if (row.getCell(8) != null) {
                row.getCell(8).setCellType(CellType.STRING);
                cityName = row.getCell(8).getStringCellValue().trim();
                substation.setCityName(cityName);
            }
            // 公司名称
            String companyName = "";
            if (row.getCell(9) != null) {
                row.getCell(9).setCellType(CellType.STRING);
                companyName = row.getCell(9).getStringCellValue().trim();
                substation.setCompanyName(companyName);
            }
            // 联系电话
            String contactPhone = "";
            if (row.getCell(10) != null) {
                row.getCell(10).setCellType(CellType.STRING);
                contactPhone = row.getCell(10).getStringCellValue().trim();
                substation.setContactPhone(contactPhone);
            }
            // 联系人
            String contactPerson = "";
            if (row.getCell(11) != null) {
                row.getCell(11).setCellType(CellType.STRING);
                contactPerson = row.getCell(11).getStringCellValue().trim();
                substation.setContactPerson(contactPerson);
            }
            // 变电站地址
            String stationAddress = "";
            if (row.getCell(12) != null) {
                row.getCell(12).setCellType(CellType.STRING);
                stationAddress = row.getCell(12).getStringCellValue().trim();
                substation.setStationAddress(stationAddress);
            }
            // 是否边缘节点
            String isStationFlag = "";
            if (row.getCell(13) != null) {
                row.getCell(13).setCellType(CellType.STRING);
                isStationFlag = row.getCell(13).getStringCellValue().trim();
                substation.setIsStationFlag(isStationFlag);
            }
            // 变电站介绍
            String introduction = "";
            if (row.getCell(14) != null) {
                row.getCell(14).setCellType(CellType.STRING);
                introduction = row.getCell(14).getStringCellValue().trim();
                substation.setIntroduction(introduction);
            }
            int count1 = this.count() + 1;
            substation.setCustom1(count1);
            substation.setLastmodifier(SecurityUtils.getCurrentUsername());
            substation.setLastmodifydate(LocalDateTime.now());
            boolean ok = this.save(substation);
            if (!ok) {
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(CellType.STRING);
                    throw new RuntimeException("序号" + row.getCell(0).getStringCellValue().trim() + "数据不正确,请检查");
                }
            }
        } else if ("02".equals(type)) {
            SubstationArea substationArea = new SubstationArea();
            substationArea.setDatastatus("1");
            // 取到这一行的第一列数据 (变电站编号)，赋值给stationCode
            String stationCode = "";
            if (row.getCell(1) != null) {
                row.getCell(1).setCellType(CellType.STRING);
                stationCode = row.getCell(1).getStringCellValue().trim();
                substationArea.setStationCode(stationCode);
            }

            // 变电站名称
            String stationName = "";
            if (row.getCell(2) != null) {
                row.getCell(2).setCellType(CellType.STRING);
                stationName = row.getCell(2).getStringCellValue().trim();
                substationArea.setStationName(stationName);
            }

            Integer count2 =
                    substationMapper.selectCount(new LambdaQueryWrapper<Substation>().eq(Substation::getStationCode,
                            stationCode).eq(Substation::getStationName, stationName));
            if (count2 <= 0) {
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(CellType.STRING);
                    throw new RuntimeException("序号" + row.getCell(0).getStringCellValue().trim() + "不存在该变电站,请检查");
                }
            }
            // 区域名称
            String areaName = "";
            if (row.getCell(3) != null) {
                row.getCell(3).setCellType(CellType.STRING);
                areaName = row.getCell(3).getStringCellValue().trim();
                substationArea.setAreaName(areaName);
            }
            LambdaQueryWrapper<SubstationArea> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(SubstationArea::getStationCode, stationCode).eq(SubstationArea::getAreaName, areaName);
            int count = substationAreaMapper.selectCount(queryWrapper);
            if (count > 0) {
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(CellType.STRING);
                    throw new RuntimeException("序号" + row.getCell(0).getStringCellValue().trim() + "区域已存在,请检查");
                }
            }
            int count1 =
                    substationAreaMapper.selectCount(new LambdaQueryWrapper<SubstationArea>().eq(SubstationArea::getStationCode, stationCode).orderByAsc(SubstationArea::getCustom1)) + 1;
            substationArea.setCustom1(count1);
            substationArea.setLastmodifier(SecurityUtils.getCurrentUsername());
            substationArea.setLastmodifydate(LocalDateTime.now());
            int insert = substationAreaMapper.insert(substationArea);
            if (insert <= 0) {
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(CellType.STRING);
                    throw new RuntimeException("序号" + row.getCell(0).getStringCellValue().trim() + "数据不正确,请检查");
                }
            }
        } else {
            SubstationBay substationBay = new SubstationBay();
            substationBay.setDatastatus("1");

            // 变电站编号
            String stationCode = "";
            if (row.getCell(1) != null) {
                row.getCell(1).setCellType(CellType.STRING);
                stationCode = row.getCell(1).getStringCellValue().trim();
                substationBay.setStationCode(stationCode);
            }

            // 变电站名称
            String stationName = "";
            if (row.getCell(2) != null) {
                row.getCell(2).setCellType(CellType.STRING);
                stationName = row.getCell(2).getStringCellValue().trim();
                substationBay.setStationName(stationName);
            }

            //            Integer count2 =
            //                    substationMapper.selectCount(new LambdaQueryWrapper<Substation>().eq
            //                    (Substation::getStationCode,
            //                            stationCode).eq(Substation::getStationName, stationName));
            //            if (count2 <= 0) {
            //                if (row.getCell(0) != null) {
            //                    row.getCell(0).setCellType(CellType.STRING);
            //                    throw new RuntimeException("序号" + row.getCell(0).getStringCellValue().trim() +
            //                    "不存在该变电站,请检查");
            //                }
            //            }

            // 区域名称
            String areaName = "";
            if (row.getCell(3) != null) {
                row.getCell(3).setCellType(CellType.STRING);
                areaName = row.getCell(3).getStringCellValue().trim();
                substationBay.setAreaName(areaName);
            }

            // (间隔名称)，赋值给bayName
            String bayName = "";
            if (row.getCell(4) != null) {
                row.getCell(4).setCellType(CellType.STRING);
                bayName = row.getCell(4).getStringCellValue().trim();
                substationBay.setBayName(bayName);
            }
            // 获取区域Id
            List<SubstationArea> substationAreas =
                    substationAreaMapper.selectList(new LambdaQueryWrapper<SubstationArea>().eq(SubstationArea::getStationCode, stationCode).eq(SubstationArea::getStationName, stationName).eq(SubstationArea::getAreaName, areaName));
            if (substationAreas == null || substationAreas.size() == 0) {
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(CellType.STRING);
                    throw new RuntimeException("序号" + row.getCell(0).getStringCellValue().trim() + "数据不正确,请检查");
                }
            }
            SubstationArea substationArea = substationAreas.get(0);
            int count1 =
                    substationBayMapper.selectCount(new LambdaQueryWrapper<SubstationBay>().eq(SubstationBay::getAreaId,
                            substationArea.getAreaId()).orderByAsc(SubstationBay::getCustom1)) + 1;
            LambdaQueryWrapper<SubstationBay> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(SubstationBay::getAreaName, substationArea.getAreaName()).eq(SubstationBay::getBayName,
                    bayName);
            int count = substationBayMapper.selectCount(queryWrapper);
            if (count > 0) {
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(CellType.STRING);
                    throw new RuntimeException("序号" + row.getCell(0).getStringCellValue().trim() + "数据不正确,请检查");
                }
            }
            substationBay.setCustom1(count1);
            substationBay.setAreaId(substationArea.getAreaId());
            substationBay.setLastmodifier(SecurityUtils.getCurrentUsername());
            substationBay.setLastmodifydate(LocalDateTime.now());
            int insert = substationBayMapper.insert(substationBay);
            if (insert <= 0) {
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(CellType.STRING);
                    throw new RuntimeException("序号" + row.getCell(0).getStringCellValue().trim() + "数据不正确,请检查");
                }
            }
        }
    }

    /**********************************
     * 用途说明: 构造树结构
     * 参数说明 list
     * 返回值说明: java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     ***********************************/
    private List<Map<String, Object>> buildTree(List<SubstationBay> list) {
        // 按照变电站名称分组
        Map<String, List<SubstationBay>> collect =
                list.stream().collect(Collectors.groupingBy(SubstationBay::getStationCode));
        List<Map<String, Object>> maps = new ArrayList<>();
        // 循环查询结构树
        for (String key : collect.keySet()) {
            Map<String, Object> map = new HashMap<>();
            List<Substation> list1 = this.list(new LambdaQueryWrapper<Substation>().eq(Substation::getStationCode,
                    key));
            if (list1 == null || list1.size() == 0) {
                continue;
            }
            Substation substation = list1.get(0);
            map.put("name", substation.getStationName());
            List<SubstationBay> substationBays = collect.get(key);
            Map<String, List<SubstationBay>> areaCollect =
                    substationBays.stream().collect(Collectors.groupingBy(SubstationBay::getAreaName));
            List<Map<String, Object>> maps1 = new ArrayList<>();
            for (String areaKey : areaCollect.keySet()) {
                Map<String, Object> areaMap = new HashMap<>();
                areaMap.put("name", areaKey);
                List<SubstationBay> substationBays1 = areaCollect.get(areaKey);
                List<Map<String, Object>> bayMap = new ArrayList<>();
                for (SubstationBay substationBay : substationBays1) {
                    Map<String, Object> bayCollect = new HashMap<>();
                    bayCollect.put("bayId", substationBay.getBayId());
                    Integer count =
                            substationMaindeviceMapper.selectCount(new LambdaQueryWrapper<SubstationMaindevice>().eq(SubstationMaindevice::getBayId, substationBay.getBayId()));
                    if (count == null) {
                        count = 0;
                    }
                    Integer deviceCount =
                            substationDeviceMapper.selectCount(new LambdaQueryWrapper<SubstationDevice>().eq(SubstationDevice::getBayId, substationBay.getBayId()));
                    if (deviceCount == null) {
                        deviceCount = 0;
                    }
                    bayCollect.put("deviceCount", deviceCount);
                    bayCollect.put("count", count);
                    bayCollect.put("name", substationBay.getBayName());
                    bayCollect.put("stationCode", substationBay.getStationCode());
                    bayCollect.put("stationName", substationBay.getStationName());
                    bayCollect.put("areaId", substationBay.getAreaId());
                    bayCollect.put("areaName", substationBay.getAreaName());
                    bayCollect.put("orderNo", substationBay.getCustom1());
                    bayCollect.put("stationId", substation.getStationId());
                    bayMap.add(bayCollect);
                }
                if (bayMap.size() > 0) {
                    bayMap.sort(Comparator.comparing(r -> r.get("custom1").toString()));
                    areaMap.put("children", bayMap);
                }
                maps1.add(areaMap);
            }
            //            maps1.sort(Comparator.comparing(r -> r.get("custom1").toString()));
            map.put("children", maps1);
            maps.add(map);
        }
        return maps;
    }

    /**********************************
     * 用途说明: 构造区域树结构
     * 参数说明 list
     * 返回值说明: java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     ***********************************/
    private List<Map<String, Object>> buildTreeArea(List<SubstationArea> list) {
        // 按照变电站名称分组
        Map<String, List<SubstationArea>> collect =
                list.stream().collect(Collectors.groupingBy(SubstationArea::getStationCode));
        List<Map<String, Object>> maps = new ArrayList<>();
        // 循环查询结构树
        for (String key : collect.keySet()) {
            List<Substation> substations =
                    substationMapper.selectList(new LambdaQueryWrapper<Substation>().eq(Substation::getStationCode,
                            key));
            Substation substation = substations.get(0);
            Map<String, Object> map = new HashMap<>();
            map.put("name", substation.getStationName());
            map.put("stationCode", substation.getStationCode());
            map.put("stationId", substation.getStationId());
            List<SubstationArea> substationAreas = collect.get(key);
            List<Map<String, Object>> maps1 = new ArrayList<>();
            for (SubstationArea substationArea : substationAreas) {
                Map<String, Object> areaMap = new HashMap<>();
                areaMap.put("name", substationArea.getAreaName());
                areaMap.put("stationCode", substationArea.getStationCode());
                areaMap.put("stationName", substationArea.getStationName());
                areaMap.put("stationId", substation.getStationId());
                areaMap.put("areaId", substationArea.getAreaId());
                maps1.add(areaMap);
            }
            map.put("children", maps1);
            maps.add(map);
        }
        return maps;
    }

}
