package com.skua.modules.equipment.controller;

import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.skua.modules.system.datestandard.entity.SysMetricDict;
import com.skua.modules.system.datestandard.service.ISysMetricDictService;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.skua.aop.annotation.CustomExceptionAnno;
import com.skua.common.util.ChineseInital;
import com.skua.constant.BaseConstant;
import com.skua.core.api.vo.Result;
import com.skua.core.aspect.annotation.AutoLog;
import com.skua.core.service.IFactoryInfoService;
import com.skua.core.service.IPgQueryService;
import com.skua.core.util.ConvertUtils;
import com.skua.core.util.DateUtils;
import com.skua.modules.common.service.ICommonSqlService;
import com.skua.modules.common.service.ICrudSqlService;
import com.skua.modules.equipment.dto.EquipmentDTO;
import com.skua.modules.equipment.dto.EquipmentStatisticDTO;
import com.skua.modules.equipment.entity.EquipmentAsset;
import com.skua.modules.equipment.entity.EquipmentBrand;
import com.skua.modules.equipment.entity.EquipmentCategory;
import com.skua.modules.equipment.entity.EquipmentExt;
import com.skua.modules.equipment.entity.EquipmentInfo;
import com.skua.modules.equipment.pojo.Equipment;
import com.skua.modules.equipment.service.IEquipmentInfoService;
import com.skua.modules.equipment.util.EquipmentUtils;
import com.skua.modules.equipment.util.ExcelUtil;
import com.skua.modules.equipment.vo.EquipmentInfoForMonitorVO;
import com.skua.modules.equipment.vo.EquipmentRealTimeVO;
import com.skua.modules.equipment.vo.EquipmentStatisticStatusVO;
import com.skua.modules.equipment.vo.EquipmentStatisticVO;
import com.skua.modules.equipment.vo.EquipmentVO;
import com.skua.modules.system.datestandard.entity.SysMonitorMetricInfo;
import com.skua.modules.system.datestandard.entity.SysStructDict;
import com.skua.modules.system.datestandard.service.ISysMonitorMetricInfoService;
import com.skua.modules.system.datestandard.service.ISysStructDictService;
import com.skua.redis.component.Key2ValueService;
import com.skua.redis.util.CustomRedisUtil;
import com.skua.tool.mpp.Base;
import com.skua.tool.mpp.BaseFactory;
import com.skua.tool.query.WrapperFactory;
import com.skua.tool.util.JoinSqlUtils;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;

/**
 * @author sonin
 * @date 2021/9/23 16:01
 */
@Slf4j
@Api(tags = "设备管理/设备台账")
@RestController
@RequestMapping("/equipment")
public class EquipmentController {

    @Autowired
    private CustomRedisUtil customRedisUtil;
    @Autowired
    private ICommonSqlService iCommonSqlService;
    @Autowired
    private TransactionTemplate transactionTemplate;
    @Autowired
    private ICrudSqlService iCrudSqlService;
    @Autowired
    private ISysMonitorMetricInfoService sysMonitorMetricInfoService;
    @Autowired
    private ISysStructDictService sysStructDictService;
    @Autowired
    private ISysMetricDictService sysMetricDictService;
    @Autowired
    private IFactoryInfoService iFactoryInfoService;
    @Autowired
    private IPgQueryService pgQueryService;
    @Autowired
    private IEquipmentInfoService equipmentInfoService;
    @Autowired
    private Key2ValueService key2ValueService;

    @CustomExceptionAnno(description = "设备台账-设备排序")
    @AutoLog(value = "设备台账-设备排序")
    @ApiOperation(value = "设备台账-设备排序", notes = "设备台账-设备排序")
    @GetMapping(value = "/orderByTime")
    public Result<IPage<EquipmentVO>> orderByTimeCtrl(@RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo, @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
                                                      @RequestParam(name = "column", defaultValue = "") String column,
                                                      @RequestParam(name = "order", defaultValue = "desc") String order) throws Exception {
        Result<IPage<EquipmentVO>> result = new Result<>();
        // 多重对象不带拼接条件
        String baseSql = JoinSqlUtils.multiJoinSqlQuery(new Equipment());
        String sql = "select * from (" + baseSql + ") as tmp order by tmp.EquipmentInfo_adviceReplaceDate " + order;
        // 分页查询
        Page page = new Page(pageNo, pageSize);
        Page<Map<String, Object>> pageList = iCommonSqlService.queryForPage(page, sql);
        List<Equipment> equipmentList = JoinSqlUtils.multiMaps2Beans(pageList.getRecords(), Equipment.class);
        List<EquipmentVO> equipmentVOList = EquipmentUtils.equipEntities2VOs(equipmentList);
        page.setRecords(equipmentVOList);
        result.setResult(page);
        return result;
    }

    @AutoLog(value = "设备台账-实时点位配置列表")
    @ApiOperation(value = "设备台账-实时点位配置列表", notes = "设备台账-实时点位配置列表")
    @GetMapping(value = "/monitorIndexEquipmentPage")
    public Result<IPage<EquipmentInfoForMonitorVO>> monitorIndexEquipmentPage(EquipmentInfoForMonitorVO equipmentInfoForMonitorVO,
            @RequestParam(name="pageNo", defaultValue="1") Integer pageNo,
            @RequestParam(name="pageSize", defaultValue="10") Integer pageSize,
            HttpServletRequest req) throws Exception {

    	Result<IPage<EquipmentInfoForMonitorVO>> result = new Result<IPage<EquipmentInfoForMonitorVO>>();
		Page<EquipmentInfoForMonitorVO> pageList = new Page<EquipmentInfoForMonitorVO>(pageNo,pageSize);
		pageList = equipmentInfoService.queryCustomPageList(pageList,equipmentInfoForMonitorVO);//自定义查询
		result.setSuccess(true);
		result.setResult(pageList);
		return result;
    }

    @CustomExceptionAnno(description = "设备台账-统计")
    @AutoLog(value = "设备台账-统计")
    @ApiOperation(value = "设备台账-统计", notes = "设备台账-统计")
    @GetMapping(value = "/statistics")
    public Result<List<EquipmentStatisticVO>> statisticsCtrl(EquipmentStatisticDTO equipmentStatisticDTO) throws Exception {
        Result<List<EquipmentStatisticVO>> result = new Result<>();
        List<EquipmentStatisticVO> voList = new ArrayList<>();
        // 统计 departId => 所属机构
        if (equipmentStatisticDTO.getShowDepartId()) {
            List<Map<String, Object>> departIdMapList = WrapperFactory.joinWrapper()
                    .select("count(*) as total", "depart_id as departId")
                    .from(EquipmentAsset.class)
                    .innerJoin(EquipmentInfo.class, EquipmentInfo.class.getDeclaredField("assetId"), EquipmentAsset.class.getDeclaredField("id"))
                    .innerJoin(EquipmentExt.class, EquipmentExt.class.getDeclaredField("infoId"), EquipmentInfo.class.getDeclaredField("id"))
                    .where()
                    .eq(StringUtils.isNotEmpty(equipmentStatisticDTO.getIsSpecial()), "is_special", equipmentStatisticDTO.getIsSpecial())
                    .groupBy(true, "depart_id")
                    .queryWrapperForList();
            EquipmentStatisticVO departIdVO = new EquipmentStatisticVO();
            List<EquipmentStatisticVO.EChartsVO> departIdList = new ArrayList<>();
            for (Map<String, Object> item : departIdMapList) {
                EquipmentStatisticVO.EChartsVO eChartsVO = new EquipmentStatisticVO.EChartsVO();
                int value = Integer.parseInt(String.valueOf(item.get("total")));
                if (item.get("departId") != null && !"".equals(item.get("departId"))) {
                    eChartsVO.setName(customRedisUtil.hget("sys_depart", item.get("departId").toString(), "id", "depart_name").toString());
                } else {
                    eChartsVO.setName("");
                }
                eChartsVO.setValue(value);
                departIdList.add(eChartsVO);
            }
            departIdVO.setStatisticType("departId");
            departIdVO.setEChartsList(departIdList);
            voList.add(departIdVO);
        }
        // assetType => 资产类型
        if (equipmentStatisticDTO.getShowAssetType()) {
            List<Map<String, Object>> assetTypeMapList = WrapperFactory.joinWrapper()
                    .select("count(*) as total", "asset_type as assetType")
                    .from(EquipmentAsset.class)
                    .innerJoin(EquipmentInfo.class, EquipmentInfo.class.getDeclaredField("assetId"), EquipmentAsset.class.getDeclaredField("id"))
                    .innerJoin(EquipmentExt.class, EquipmentExt.class.getDeclaredField("infoId"), EquipmentInfo.class.getDeclaredField("id"))
                    .where()
                    .eq(StringUtils.isNotEmpty(equipmentStatisticDTO.getIsSpecial()), "is_special", equipmentStatisticDTO.getIsSpecial())
                    .groupBy(true, "asset_type")
                    .queryWrapperForList();
            EquipmentStatisticVO assetTypeVO = new EquipmentStatisticVO();
            List<EquipmentStatisticVO.EChartsVO> assetTypeList = new ArrayList<>();
            Map<String, String> assetTypeVal2KeyMap = EquipmentUtils.convertFunc("assetType", false);
            for (Map<String, Object> item : assetTypeMapList) {
                EquipmentStatisticVO.EChartsVO eChartsVO = new EquipmentStatisticVO.EChartsVO();
                int value = Integer.parseInt(String.valueOf(item.get("total")));
                eChartsVO.setName(assetTypeVal2KeyMap.get("" + item.get("assetType")));
                eChartsVO.setValue(value);
                assetTypeList.add(eChartsVO);
            }
            assetTypeVO.setStatisticType("assetType");
            assetTypeVO.setEChartsList(assetTypeList);
            voList.add(assetTypeVO);
        }
        // suppliesType => 物资类型
        if (equipmentStatisticDTO.getShowSuppliesType()) {
            List<Map<String, Object>> suppliesTypeMapList = WrapperFactory.joinWrapper()
                    .select("count(*) as total", "supplies_type as suppliesType")
                    .from(EquipmentAsset.class)
                    .innerJoin(EquipmentInfo.class, EquipmentInfo.class.getDeclaredField("assetId"), EquipmentAsset.class.getDeclaredField("id"))
                    .innerJoin(EquipmentExt.class, EquipmentExt.class.getDeclaredField("infoId"), EquipmentInfo.class.getDeclaredField("id"))
                    .where()
                    .eq(StringUtils.isNotEmpty(equipmentStatisticDTO.getIsSpecial()), "is_special", equipmentStatisticDTO.getIsSpecial())
                    .eq(true, "asset_type", "assets-material-type")
                    .groupBy(true, "supplies_type")
                    .queryWrapperForList();
            EquipmentStatisticVO suppliesTypeVO = new EquipmentStatisticVO();
            List<EquipmentStatisticVO.EChartsVO> suppliesTypeList = new ArrayList<>();
            Map<String, String> suppliesTypeVal2KeyMap = EquipmentUtils.convertFunc("suppliesType", false);
            for (Map<String, Object> item : suppliesTypeMapList) {
                EquipmentStatisticVO.EChartsVO eChartsVO = new EquipmentStatisticVO.EChartsVO();
                int value = Integer.parseInt(String.valueOf(item.get("total")));
                eChartsVO.setName(suppliesTypeVal2KeyMap.get("" + item.get("suppliesType")));
                eChartsVO.setValue(value);
                suppliesTypeList.add(eChartsVO);
            }
            suppliesTypeVO.setStatisticType("suppliesType");
            suppliesTypeVO.setEChartsList(suppliesTypeList);
            voList.add(suppliesTypeVO);
        }
        // equipmentType => 设备类型
        if (equipmentStatisticDTO.getShowEquipmentType()) {
            List<Map<String, Object>> equipmentTypeMapList = WrapperFactory.joinWrapper()
                    .select("count(*) as total", "equipment_top_type as equipmentType")
                    .from(EquipmentAsset.class)
                    .innerJoin(EquipmentInfo.class, EquipmentInfo.class.getDeclaredField("assetId"), EquipmentAsset.class.getDeclaredField("id"))
                    .leftJoin(EquipmentExt.class, EquipmentExt.class.getDeclaredField("infoId"), EquipmentInfo.class.getDeclaredField("id"))
                    .where()
                    .eq(StringUtils.isNotEmpty(equipmentStatisticDTO.getIsSpecial()), "is_special", equipmentStatisticDTO.getIsSpecial())
                    .groupBy(true, "equipment_top_type")
                    .queryWrapperForList();
            EquipmentStatisticVO equipmentTypeVO = new EquipmentStatisticVO();
            List<EquipmentStatisticVO.EChartsVO> equipmentTypeList = new ArrayList<>();
            Map<String, String> equipmentTypeVal2KeyMap = EquipmentUtils.convertFunc("equipmentType", false);
            for (Map<String, Object> item : equipmentTypeMapList) {
                EquipmentStatisticVO.EChartsVO eChartsVO = new EquipmentStatisticVO.EChartsVO();
                int value = Integer.parseInt(String.valueOf(item.get("total")));
                eChartsVO.setName(equipmentTypeVal2KeyMap.get("" + item.get("equipmentType")));
                eChartsVO.setValue(value);
                equipmentTypeList.add(eChartsVO);
            }
            equipmentTypeVO.setStatisticType("equipmentType");
            equipmentTypeVO.setEChartsList(equipmentTypeList);
            voList.add(equipmentTypeVO);
        }
        result.setResult(voList);
        return result;
    }

    @CustomExceptionAnno(description = "设备台账-设备使用状态统计")
    @AutoLog(value = "设备台账-设备使用状态统计")
    @ApiOperation(value = "设备台账-设备使用状态统计", notes = "设备台账-设备使用状态统计")
    @GetMapping(value = "/equipmentStatus")
    public Result<Object> equipmentStatusCtrl(String isSpecial) throws Exception {
        Result<Object> result = new Result<>();
        // equipmentStatus: 设备类型value:key的转换
        Map<String, String> equipmentStatusVal2KeyMap = EquipmentUtils.convertFunc("equipmentStatus", false);
        Map<String, Integer> key2ValMap = new LinkedHashMap<>(2);
        for (String key : equipmentStatusVal2KeyMap.keySet()) {
            key2ValMap.put(key, 0);
        }
        // 多重对象不带拼接条件
        boolean flag = false;
        Equipment equipment = new Equipment();
        EquipmentInfo equipmentInfo = new EquipmentInfo();
        if(ConvertUtils.isNotEmpty(isSpecial)){
            flag = true;
            equipmentInfo.setIsSpecial(isSpecial);
        }
        equipment.setEquipmentInfo(equipmentInfo);
        String sql = JoinSqlUtils.multiJoinSqlQuery(equipment);
        sql = "select res.EquipmentInfo_equipmentStatus, count(*) as total from (" + sql + ") as res";
        QueryWrapper<?> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq(flag, "EquipmentInfo_isSpecial", isSpecial)
                .groupBy("EquipmentInfo_equipmentStatus");//EquipmentInfo_isSpecial
        List<Map<String, Object>> countMapList = iCommonSqlService.queryWrapperForList(sql, queryWrapper);
        for (Map<String, Object> item : countMapList) {
            String key = "" + item.get("EquipmentInfo_equipmentStatus");
            if (key2ValMap.containsKey(key)) {
                key2ValMap.put(key, Integer.parseInt("" + item.get("total")));
            }
        }
        EquipmentStatisticStatusVO equipmentStatisticStatusVO = new EquipmentStatisticStatusVO();
        for (Map.Entry<String, Integer> item : key2ValMap.entrySet()) {
            equipmentStatisticStatusVO.getXData().add(equipmentStatusVal2KeyMap.get(item.getKey()));
            equipmentStatisticStatusVO.getYData().add(item.getValue());
        }
        result.setResult(equipmentStatisticStatusVO);
        return result;
    }

    @CustomExceptionAnno(description = "设备台账-删除")
    @AutoLog(value = "设备台账-删除")
    @ApiOperation(value = "设备台账-删除", notes = "设备台账-删除")
    @DeleteMapping(value = "/delete")
    public Result<?> deleteCtrl(@RequestBody EquipmentDTO equipmentDTO) throws Exception {
        Result<?> result = new Result<>();
        Equipment equipment = EquipmentUtils.equipDTO2Entity(equipmentDTO);
        // todo 前端未传，特殊处理
        equipment.getEquipmentInfo().setAssetId(equipment.getEquipmentAsset().getId());
       // equipment.getEquipmentExt().setInfoId(equipment.getEquipmentInfo().getId());
        // 校验ID是否一致
        JoinSqlUtils.checkSqlIdFunc(equipment);
        // 与点表有关联的台账不允许删除
        List<SysMonitorMetricInfo> monitorMetricInfo = sysMonitorMetricInfoService.list(new QueryWrapper<SysMonitorMetricInfo>().eq("equipment_code", equipmentDTO.getEquipmentInfo().getId()));
        if (monitorMetricInfo!=null&&monitorMetricInfo.size()>0) {
            result.error500("与运行点表有关联的设备台账不允许删除");
            return result;
        }
        iCrudSqlService.delete(equipment);
        return Result.ok("删除成功!");
    }

    @CustomExceptionAnno(description = "设备台账-编辑")
    @AutoLog(value = "设备台账-编辑")
    @ApiOperation(value = "设备台账-编辑", notes = "设备台账-编辑")
    @PutMapping(value = "/edit")
    public Result<EquipmentVO> editCtrl(@RequestBody EquipmentDTO equipmentDTO) throws Exception {
    	Result<EquipmentVO> result = new Result<>();
        equipmentInfoService.editData(result,equipmentDTO);
        return result;

    }

    @CustomExceptionAnno(description = "设备台账-添加")
    @AutoLog(value = "设备台账-添加")
    @ApiOperation(value = "设备台账-添加", notes = "设备台账-添加")
    @PostMapping(value = "/add")
    public Result<EquipmentVO> addCtrl(@RequestBody EquipmentDTO equipmentDTO) throws Exception {
        Result<EquipmentVO> result = new Result<>();
        equipmentInfoService.addData(result,equipmentDTO);
        return result;
    }

    @AutoLog(value = "设备台账-扩展字段获取")
    @ApiOperation(value = "设备台账-扩展字段获取", notes = "设备台账-扩展字段获取")
    @GetMapping(value = "/getExtField")
    public Result<Map<String,List<Map<String,Object>>>> getExtField(String categoryId,String equipmentId,String departId) throws Exception {
        Result<Map<String,List<Map<String,Object>>>> result = new Result<>();
        Map<String,List<Map<String,Object>>> map = equipmentInfoService.getExtField(categoryId,equipmentId,departId);
        result.setResult(map);
        return result;
    }


    @CustomExceptionAnno(description = "设备台账-批量添加运行点")
    @AutoLog(value = "设备台账-批量添加运行点")
    @ApiOperation(value = "设备台账-批量添加运行点", notes = "设备台账-批量添加运行点")
    @PostMapping(value = "/addRun")
    public Result<EquipmentVO> addRun(@RequestBody EquipmentDTO equipmentDTO) throws Exception {
        Result<EquipmentVO> result = new Result<>();
        QueryWrapper<SysMonitorMetricInfo> sysMonitorMetricInfoQueryWrapper = new QueryWrapper<>();
        sysMonitorMetricInfoQueryWrapper.eq("metric_type", 1).like("metric_name", "运行");
        List<SysMonitorMetricInfo> sysMonitorMetricInfoList = sysMonitorMetricInfoService.list(sysMonitorMetricInfoQueryWrapper);
        for (SysMonitorMetricInfo sysMonitorMetricInfo : sysMonitorMetricInfoList) {
            String metricName = sysMonitorMetricInfo.getMetricName();
            String replaceName = metricName.replace("#", "号").replace("：", "区间").replace("运行", "").replace(" ", "");
            String structCode = sysMonitorMetricInfo.getStructCode();
            String departId = sysMonitorMetricInfo.getDepartId();
            // 新增资产设备信息
            Equipment equipment = new Equipment();
            // 设置主键
            JoinSqlUtils.setJoinSqlIdFunc(equipment);
            // 资产信息
            EquipmentAsset equipmentAsset = equipment.getEquipmentAsset();
            equipmentAsset.setAssetName(replaceName);
            equipmentAsset.setAssetOrganization(replaceName);
            equipmentAsset.setAssetType("工艺设备及仪表");
            // 设备信息
            EquipmentInfo equipmentInfo = equipment.getEquipmentInfo();
            equipmentInfo.setDepartId(departId);
            equipmentInfo.setEquipmentName(replaceName);
            equipmentInfo.setEquipmentType("process");
            equipmentInfo.setInstallPosition(structCode);
            equipmentInfo.setStartUseDate(LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
            equipmentInfo.setQrCode(equipmentInfoService.createQrCode(equipmentInfo.getId()));
            if (StringUtils.isNotEmpty(equipmentInfo.getStartUseDate()) && equipmentInfo.getLimitUseYear() != null) {
                String[] items = equipmentInfo.getStartUseDate().split("-");
                equipmentInfo.setAdviceReplaceDate(Integer.parseInt(items[0]) + equipmentInfo.getLimitUseYear() + "-" + items[1] + "-" + items[2]);
            }
            SysStructDict struct = sysStructDictService.getOne(new QueryWrapper<SysStructDict>().eq("id", structCode));
            String allFirstLetter = ChineseInital.getAllFirstLetter(struct.getStructName() + replaceName);
            equipmentInfo.setEquipmentCode(allFirstLetter);
            sysMonitorMetricInfo.setEquipmentCode(equipmentInfo.getId());
            // 点表保存设备的主键
            transactionTemplate.execute(new TransactionCallbackWithoutResult() {
                @SneakyThrows
                @Override
                protected void doInTransactionWithoutResult(TransactionStatus transactionStatus) {
                    boolean val0 = sysMonitorMetricInfoService.updateById(sysMonitorMetricInfo);
                    boolean val1 = iCrudSqlService.save(equipment);
                    if (!val0 || !val1) {
                        throw new Exception("新增点表失败");
                    }
                }
            });
        }
        return result;
    }

    @CustomExceptionAnno(description = "设备台账-分页列表查询")
    @AutoLog(value = "设备台账-分页列表查询")
    @ApiOperation(value = "设备台账-分页列表查询", notes = "设备台账-分页列表查询")
    @PostMapping(value = "/page")
    public Result<Object> queryPageCtrl(@RequestBody EquipmentDTO equipmentDTO) throws Exception {
    	Result<Object> result = new Result<>();
        String equipmentName = equipmentDTO.getEquipmentInfo().getEquipmentName();
        String equipmentCode = equipmentDTO.getEquipmentInfo().getEquipmentCode();
        String equipmentStatus = equipmentDTO.getEquipmentInfo().getEquipmentStatus();
        String installWay = equipmentDTO.getEquipmentInfo().getInstallWay();
        String equipmentId = equipmentDTO.getEquipmentInfo().getId();
        String equipmentCategory = equipmentDTO.getEquipmentInfo().getEquipmentType();
        String equipmentLevel = equipmentDTO.getEquipmentInfo().getEquipmentLevel();
        String installPosition = equipmentDTO.getEquipmentInfo().getInstallPosition();
        String structures = equipmentDTO.getEquipmentInfo().getStructures();
        String isSpecial = equipmentDTO.getEquipmentInfo().getIsSpecial();
        String departId = equipmentDTO.getEquipmentInfo().getDepartId();

        String energyLevel = equipmentDTO.getEquipmentInfo().getEnergyLevel();//能耗等级
        //Integer isSpecial = equipmentDTO.getEquipmentInfo().getIsSpecial();
        String isMeterage = equipmentDTO.getEquipmentInfo().getIsMeterage();//是否计量设备

        List<String> equipmentLevelList = null;
        Base base = BaseFactory.JOIN().select(EquipmentAsset.class).select(EquipmentInfo.class).select(EquipmentBrand.class).select(EquipmentCategory.class)
        		.select(" (SELECT MAX(real_start_time) FROM equipment_maintain_task WHERE equipment_id = equipment_info.id and results_enforcement='4') as equipmentExt_maintainLastTime ")
        		.select(" (select TIMESTAMPDIFF(HOUR, NOW(),(select min(start_time) from equipment_maintain_task where start_time>NOW() and equipment_id = equipment_info.id))) as  equipmentExt_maintainCountdown ")
        		.select(" (select max(repair_date) from equipment_repair where info_id=equipment_info.id) as  equipmentExt_repairLastTime ")
        		.from(EquipmentAsset.class)
                .innerJoin(EquipmentInfo.class, EquipmentInfo::getAssetId, EquipmentAsset::getId)
                .leftJoin(EquipmentBrand.class, EquipmentInfo::getEquipmentBrand, EquipmentBrand::getId)
                .leftJoin(EquipmentCategory.class, EquipmentInfo::getEquipmentType, EquipmentCategory::getId)
                .where()
                .like(StringUtils.isNotEmpty(equipmentCategory), "equipment_info.equipment_type_tree_path", equipmentCategory)
                .in(equipmentLevelList != null && !equipmentLevelList.isEmpty(), "equipment_level", equipmentLevelList)
                .eq(StringUtils.isNotEmpty(installWay), "install_way", installWay)
                .eq(StringUtils.isNotEmpty(equipmentId), "equipment_info.id", equipmentId)
                .eq(StringUtils.isNotEmpty(equipmentLevel), "equipment_level", equipmentLevel)
                .eq(StringUtils.isNotEmpty(isSpecial), "is_special", isSpecial)//特种设备
                .eq(StringUtils.isNotEmpty(isMeterage), "is_meterage", isMeterage) //计量设备
                .eq(StringUtils.isNotEmpty(energyLevel), "energy_level", energyLevel) // 能耗等级
                .eq(StringUtils.isNotEmpty(departId), "depart_id", departId)
                .eq(StringUtils.isNotEmpty(structures), "structures", structures)
                .like(StringUtils.isNotEmpty(equipmentName), "equipment_name", equipmentName)
                .like(StringUtils.isNotEmpty(equipmentCode), "equipment_code", equipmentCode)
                .eq(StringUtils.isNotEmpty(equipmentStatus), "equipment_status", equipmentStatus)
                .like(StringUtils.isNotEmpty(installPosition), EquipmentInfo::getInstallPosition, installPosition)

                .eq(StringUtils.isNotEmpty(equipmentDTO.getEquipmentInfo().getId()), "equipment_info.id", equipmentDTO.getEquipmentInfo().getId());

        Page<Map<String, Object>> mapPage = (Page<Map<String, Object>>) base.queryForPage(new Page<>(equipmentDTO.getPageNo(), equipmentDTO.getPageSize()));

        // 转义
        Map<String, String> sysDepartVal2KeyMap = key2ValueService.dictKey2Val("sysDepart", false);
        Map<String, String> equipmentTypeVal2KeyMap = EquipmentUtils.convertFunc("equipmentType", false);
        Map<String, String> equipmentBrandVal2KeyMap = EquipmentUtils.convertFunc("equipmentBrand", false);
        Map<String, String> equipmentStatusVal2KeyMap = EquipmentUtils.convertFunc("equipmentStatus", false);
        Map<String, String> maintenanceCycleUnitVal2KeyMap = key2ValueService.dictKey2Val("loop_unit", false);
        Map<String, String> installVal2KeyMap = key2ValueService.dictKey2Val("equipment-installWay", false);
        // 封装结果
        Page<Map<String, Map<String, Object>>> resPage = new Page<>(equipmentDTO.getPageNo(), equipmentDTO.getPageSize());
        resPage.setTotal(mapPage.getTotal());
        resPage.setRecords(new ArrayList<>());
        for (int i = 0; i < mapPage.getRecords().size(); i++) {
            Map<String, Map<String, Object>> resMap = new LinkedHashMap<>();
            for (Map.Entry<String, Object> item : mapPage.getRecords().get(i).entrySet()) {
                String key = item.getKey();
                Object value = item.getValue();
                // 修改时间格式
                if (value instanceof Date) {
                    value = com.skua.tool.util.DateUtils.date2Str((Date) item.getValue(), BaseConstant.dateFormat);
                }
                String[] keys = key.split("_");
                // 首字母大写转小写
                keys[0] = keys[0].substring(0, 1).toLowerCase() + keys[0].substring(1);
                resMap.putIfAbsent(keys[0], new LinkedHashMap<>());
                resMap.get(keys[0]).put(keys[1], value);
                // 补充翻译信息
                if ("departId".equals(keys[1])) {
                    resMap.get(keys[0]).put(keys[1] + "_dictText", sysDepartVal2KeyMap.get(ConvertUtils.getString(value)));
                } else if ("equipmentType".equals(keys[1])) {
                    resMap.get(keys[0]).put(keys[1] + "_dictText", equipmentTypeVal2KeyMap.get(ConvertUtils.getString(value)));
                } else if ("equipmentBrand".equals(keys[1])) {
                    resMap.get(keys[0]).put(keys[1] + "_dictText", equipmentBrandVal2KeyMap.get(ConvertUtils.getString(value)));
                } else if ("equipmentStatus".equals(keys[1])) {
                    resMap.get(keys[0]).put(keys[1] + "_dictText", equipmentStatusVal2KeyMap.get(ConvertUtils.getString(value)));
                } else if ("cycleUnit".equals(keys[1])) {
                    resMap.get(keys[0]).put(keys[1] + "_dictText", maintenanceCycleUnitVal2KeyMap.get(ConvertUtils.getString(value)));
                } else if ("installWay".equals(keys[1])) {
                    resMap.get(keys[0]).put(keys[1] + "_dictText", installVal2KeyMap.get(ConvertUtils.getString(value)));
                }
            }
            resPage.getRecords().add(resMap);
        }
        result.setResult(resPage);
        return result;
    }

    @CustomExceptionAnno(description = "设备-采集实时数据")
    @AutoLog(value = "设备-采集实时数据")
    @ApiOperation(value = "设备-采集实时数据", notes = "设备-采集实时数据")
    @GetMapping(value = "/realTimeData")
    public Result<List<Map<String,Object>>> equipRealTimeDataCtrl(@RequestParam(value = "infoId") String infoId,
                                                             @RequestParam(value = "departId") String departId) {
        Result<List<Map<String,Object>>> result = new Result<>();
        QueryWrapper<SysMonitorMetricInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("equipment_code", infoId)
                .eq("depart_id", departId);
        List<SysMonitorMetricInfo> list = sysMonitorMetricInfoService.list(queryWrapper);
        if (list.isEmpty()) {
            result.error500("MYSQL无此数据");
            return result;
        }
        //获取实时指标
        String fields = "";
        for(SysMonitorMetricInfo monitorInfo : list) {
        	fields = fields+","+monitorInfo.getId();
        }
        if(!StringUtils.isEmpty(fields)) {
        	fields = fields.substring(1);
        }
        //List<Map<String, Object>> pgDataList = iFactoryInfoService.queryFactoryInfos(departId, fields, startTime, endTime, "0");
        List<Map<String, Object>> pgDataList = pgQueryService.queryFactoryMonitorFromRealTimeData(departId, fields);
        Map<String, Object> dataMap = new HashMap<String,Object>();
        if(pgDataList!=null&&pgDataList.size()>0) {
        	dataMap = pgDataList.get(0);
        }
        List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();
        for(SysMonitorMetricInfo monitorInfo : list) {
        	Map<String,Object> tempMap = new HashMap<String,Object>();
        	tempMap.put("time", DateUtils.formatTime(Long.parseLong(dataMap.get("time") + "000")));
        	tempMap.put("indexName", monitorInfo.getMetricName());
            tempMap.put("indexCode", monitorInfo.getId());
        	tempMap.put("indexValue", dataMap.get(monitorInfo.getId()));
            tempMap.put("indexUnit",StringUtils.isNotBlank(monitorInfo.getMetricUnit()) ? monitorInfo.getMetricUnit():"");
        	tempMap.put("indexType", monitorInfo.getMetricType());
        	resultList.add(tempMap);
        }
        result.setResult(resultList);
        return result;
    }

    @CustomExceptionAnno(description = "设备-采集历史数据")
    @AutoLog(value = "设备-采集历史数据")
    @ApiOperation(value = "设备-采集历史数据", notes = "设备-采集历史数据")
    @GetMapping(value = "/realTimeHistoryData")
    public Result<EquipmentRealTimeVO> equipRealTimeHistoryDataCtrl(@RequestParam(value = "monitorIndexCode") String monitorIndexCode,
                                                             @RequestParam(value = "departId") String departId,
                                                             @RequestParam(value = "startTime") String startTime,
                                                             @RequestParam(value = "endTime") String endTime) {
        Result<EquipmentRealTimeVO> result = new Result<>();
        QueryWrapper<SysMonitorMetricInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("id", monitorIndexCode);
        List<SysMonitorMetricInfo> list = sysMonitorMetricInfoService.list(queryWrapper);
        if (list.isEmpty()) {
            result.error500("MYSQL无此数据");
            return result;
        }
        SysMonitorMetricInfo sysMonitorMetricInfo = list.get(0);
        List<Map<String, Object>> pgDataList = iFactoryInfoService.queryFactoryInfos(departId, sysMonitorMetricInfo.getId(), startTime, endTime, "1");
        List<String> xData = new ArrayList<>();
        List<String> yData = new ArrayList<>();
        for (Map<String, Object> item : pgDataList) {
            xData.add(DateUtils.formatTime(Long.parseLong(item.get("time") + "000")));
            yData.add("" + item.get(sysMonitorMetricInfo.getId()));
        }
        EquipmentRealTimeVO equipmentRealTimeVO = new EquipmentRealTimeVO();
        equipmentRealTimeVO.setXData(xData);
        equipmentRealTimeVO.setYData(yData);
        result.setResult(equipmentRealTimeVO);
        return result;
    }

    @AutoLog(value = "设备台账-excel导出")
    @ApiOperation(value = "设备台账-excel导出", notes = "设备台账-excel导出")
    @RequestMapping(value = "/exportDoneXls")
    public void exportDoneXlsCtrl(HttpServletRequest request, HttpServletResponse response) throws Exception {
        // 多重对象带拼接条件
        String baseSql = JoinSqlUtils.multiJoinSqlQuery(new Equipment());
        List<Map<String, Object>> mapList = iCommonSqlService.queryForList(baseSql);
        Workbook workbook = ExcelUtil.exportEquipment(mapList);
        workbook.write(response.getOutputStream());
        response.flushBuffer();
    }

    @AutoLog(value = "设备台账-excel模板")
    @ApiOperation(value = "设备台账-excel模板", notes = "设备台账-excel模板")
    @GetMapping(value = "/downLoadTemplate")
    public void downLoadTemplateCtrl(HttpServletRequest request, HttpServletResponse response) {
        try {
            Resource resource = new ClassPathResource("templates" + File.separator + "template-equipment.xls");
            InputStream inputStream = resource.getInputStream();
            OutputStream outputStream = response.getOutputStream();
            byte[] buf = new byte[1024];
            int len;
            while ((len = inputStream.read(buf)) > 0) {
                outputStream.write(buf, 0, len);
            }
            response.flushBuffer();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("设备台账-excel模板fail: {}", e.getMessage());
        }
    }

    @AutoLog(value = "设备台账-excel导入")
    @ApiOperation(value = "设备台账-excel导入", notes = "设备台账-excel导入")
    @RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            // 获取上传文件对象
            MultipartFile multipartFile = entity.getValue();
            String filename = multipartFile.getOriginalFilename();
            if (StringUtils.isEmpty(filename)) {
                continue;
            }
            InputStream inputStream = multipartFile.getInputStream();
            Workbook workbook;
            if (ExcelUtil.judgeExcelEdition(filename)) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                workbook = new HSSFWorkbook(inputStream);
            }
            List<EquipmentDTO> equipmentDTOList =null;
            try {
            // 获取第一个sheet
            Sheet sheet0 = workbook.getSheetAt(0);
            equipmentDTOList = ExcelUtil.importEquipment(sheet0);
            }catch (Exception e){
                return Result.error(e.getMessage());
            }
            for (EquipmentDTO equipmentDTO : equipmentDTOList) {
                Equipment equipment = EquipmentUtils.equipDTO2Entity(equipmentDTO);
                // 设置主键
                JoinSqlUtils.setJoinSqlIdFunc(equipment);
                EquipmentInfo equipmentInfo = equipment.getEquipmentInfo();
                equipmentInfo.setQrCode(equipmentInfoService.createQrCode(equipmentInfo.getId()));
                if (StringUtils.isNotEmpty(equipmentInfo.getStartUseDate()) && equipmentInfo.getLimitUseYear() != null) {
                    String[] items = equipmentInfo.getStartUseDate().split("-");
                    equipmentInfo.setAdviceReplaceDate(Integer.parseInt(items[0]) + equipmentInfo.getLimitUseYear() + "-" + items[1] + "-" + items[2]);
                }
                iCrudSqlService.save(equipment);
            }
            inputStream.close();
        }
        return Result.ok("文件导入成功！");
    }

    /**
     * 通过id查询
     *
     * @param id
     * @return
     */
    @AutoLog(value = "设备台账-通过id查询")
    @ApiOperation(value = "设备台账-通过id查询", notes = "设备台账-通过id查询")
    @GetMapping(value = "/queryById")
    public Result<EquipmentInfo> queryById(@RequestParam(name = "id", required = true) String id) {
        Result<EquipmentInfo> result = new Result<>();
        EquipmentInfo equipmentInfo = equipmentInfoService.getById(id);
        if (equipmentInfo == null) {
            result.error500("未找到对应实体");
        } else {
            result.setResult(equipmentInfo);
            result.setSuccess(true);
        }
        return result;
    }

    @ApiOperation(value = "设备台账-设备生命树", notes = "设备台账-设备生命树")
    @GetMapping(value = "/getEquipmentLifeTree")
    public Result<List<Map<String,Object>>> getEquipmentLifeTree(String id,String eventType) throws Exception {
    	Result<List<Map<String,Object>>> result = new Result<>();
		try {
			 List<Map<String, Object>> mapList = equipmentInfoService.getEquipmentLifeTree(id,eventType);
			 result.setSuccess(true);
		     result.setResult(mapList);
		} catch (Exception e) {
			e.printStackTrace();
			result.error500("统计数据失败");
		}
		return result;
    }

}