Merge branch 'master' of http://10.153.19.25:10105/r/YiWuProject

ZengTao

2025-05-12 21887ddd12bdba4e2b00456c94a3cb8e1568c000

Merge branch 'master' of http://10.153.19.25:10105/r/YiWuProject

 hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/controller/LargenScreenController.java

@@ -1,6 +1,7 @@
package com.mes.largenscreen.controller;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mes.largenscreen.entity.DailyProductionData;
import com.mes.largenscreen.entity.DailyProductionVO;
import com.mes.largenscreen.entity.DateRequest;
import com.mes.largenscreen.service.LargenScreenService;
@@ -14,6 +15,7 @@
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;

/**
 * @Author : zhoush
@@ -34,5 +36,17 @@
        return Result.build(200, "查询成功", largenScreenService.queryDailyProduction(query));
    }

    @ApiOperation("按照条件统计每日生产情况导出")
    @PostMapping("/exportDailyProduction")
    public void exportDailyProduction(@RequestBody @Validated DateRequest query) {
        largenScreenService.exportDailyProduction(query);
    }

    @ApiOperation("按照条件统计生产情况")
    @PostMapping("/queryProduction")
    public Result<List<DailyProductionData>> queryProduction(@RequestBody @Validated DateRequest query) {
        return Result.build(200, "查询成功", largenScreenService.queryProduction(query));
    }


}

 hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/entity/DailyProductionData.java

New file
@@ -0,0 +1,98 @@
package com.mes.largenscreen.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.mes.utils.excel.ExcelMerge;
import lombok.Data;

/**
 * @Author : zhoush
 * @Date: 2025/3/12 14:30
 * @Description:
 */
@Data
public class DailyProductionData {

    @ExcelProperty(value = "日期")
    @ExcelMerge(merge = true)
    private String productDate;

    @ExcelProperty(value = {"切割", "开始时间"})
    @ExcelMerge(merge = true)
    private String edgBeginTime;
    @ExcelProperty(value = {"切割", "结束时间"})
    @ExcelMerge(merge = true)
    private String edgEndTime;
    @ExcelProperty(value = {"切割", "总耗时"})
    @ExcelMerge(merge = true)
    private String edgTimeTotal;
    @ExcelProperty(value = {"切割", "空闲时长"})
    @ExcelMerge(merge = true)
    private String edgTimeFree;
    @ExcelProperty(value = {"切割", "工作时长"})
    @ExcelMerge(merge = true)
    private String edgTimeDiff;
    @ExcelProperty(value = {"切割", "总片数"})
    @ExcelMerge(merge = true)
    private String countOut;
    @ExcelProperty(value = {"切割", "总面积(m^2)"})
    @ExcelMerge(merge = true)
    private String totalAreaOut;


    @ExcelProperty(value = {"钢化", "开始时间"})
    @ExcelMerge(merge = true)
    private String bigBeginTime;
    @ExcelProperty(value = {"钢化", "结束时间"})
    @ExcelMerge(merge = true)
    private String bigEndTime;
    @ExcelProperty(value = {"钢化", "总耗时"})
    @ExcelMerge(merge = true)
    private String bigTimeTotal;
    @ExcelMerge(merge = true)
    @ExcelProperty(value = {"钢化", "空闲时长"})
    private String bigTimeFree;
    @ExcelProperty(value = {"钢化", "工作时长"})
    @ExcelMerge(merge = true)
    private String bigTimeDiff;
    @ExcelProperty(value = {"钢化", "总炉数"})
    @ExcelMerge(merge = true)
    private String temperingLayoutCount;
    @ExcelProperty(value = {"钢化", "总片数"})
    @ExcelMerge(merge = true)
    private String temperingGlassCount;
    @ExcelProperty(value = {"钢化", "总面积(m^2)"})
    @ExcelMerge(merge = true)
    private String temperingArea;


    @ExcelProperty(value = {"中空", "开始时间"})
    @ExcelMerge(merge = true)
    private String hollowBeginTime;
    @ExcelProperty(value = {"中空", "结束时间"})
    @ExcelMerge(merge = true)
    private String hollowEndTime;
    @ExcelProperty(value = {"中空", "总耗时"})
    @ExcelMerge(merge = true)
    private String hollowTimeTotal;
    @ExcelProperty(value = {"中空", "空闲时长"})
    @ExcelMerge(merge = true)
    private String hollowTimeFree;
    @ExcelProperty(value = {"中空", "工作时长"})
    @ExcelMerge(merge = true)
    private String hollowTimeDiff;
    @ExcelProperty(value = {"中空", "总片数"})
    @ExcelMerge(merge = true)
    private String hollowGlassCount;
    @ExcelProperty(value = {"中空", "总面积(m^2)"})
    @ExcelMerge(merge = true)
    private String hollowArea;

    @ExcelProperty(value = {"工程信息", "工程号"})
    private String engineerId;
    @ExcelProperty(value = {"工程信息", "总片量"})
    private String glassTotal;
    @ExcelProperty(value = {"工程信息", "总面积(m^2)"})
    private String glassTotalArea;


}

 hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/mapper/LargenScreenMapper.java

@@ -1,5 +1,6 @@
package com.mes.largenscreen.mapper;

import com.mes.largenscreen.entity.DailyProductionData;
import com.mes.largenscreen.entity.DailyProductionVO;

import java.util.List;
@@ -13,4 +14,6 @@
public interface LargenScreenMapper {

    List<DailyProductionVO> queryDailyProduction(String beginDate, String endDate);

    List<DailyProductionData> exportDailyProduction(String beginDate, String endDate);
}

 hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/service/LargenScreenService.java

@@ -1,9 +1,9 @@
package com.mes.largenscreen.service;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mes.largenscreen.entity.DailyProductionData;
import com.mes.largenscreen.entity.DailyProductionVO;
import com.mes.largenscreen.entity.DateRequest;
import com.mes.largenscreen.entity.PieChartVO;

import java.util.List;

@@ -18,4 +18,7 @@

    List<DailyProductionVO> querySameDayProduction(DateRequest query);

    void exportDailyProduction(DateRequest query);

    List<DailyProductionData> queryProduction(DateRequest query);
}

 hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/service/impl/LargenScreenServiceImpl.java

@@ -1,15 +1,22 @@
package com.mes.largenscreen.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mes.largenscreen.entity.DailyProductionData;
import com.mes.largenscreen.entity.DailyProductionVO;
import com.mes.largenscreen.entity.DateRequest;
import com.mes.largenscreen.mapper.LargenScreenMapper;
import com.mes.largenscreen.service.LargenScreenService;
import com.mes.tools.PageUtil;
import com.mes.utils.excel.ExcelMergeStrategy;
import com.mes.utils.excel.ExcelUtil;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
@@ -25,6 +32,8 @@

    @Resource
    private LargenScreenMapper largenScreenMapper;
    @Resource
    private HttpServletResponse response;

    @Override
    public Page<DailyProductionVO> queryDailyProduction(DateRequest query) {
@@ -50,5 +59,40 @@
        return list;
    }

    @Override
    public void exportDailyProduction(DateRequest query) {
        try {
            if (StringUtils.isEmpty(query.getBeginDate())) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Calendar calendar = Calendar.getInstance();
                calendar.add(Calendar.DAY_OF_MONTH, -6);
                query.setBeginDate(sdf.format(calendar.getTime()));
                query.setEndDate(sdf.format(new Date()));
            }
            ExcelUtil.setExcelResponseProp(response, "供应商列表-合并");
            List<DailyProductionData> providerList = largenScreenMapper.exportDailyProduction(query.getBeginDate(), query.getEndDate());
            EasyExcel.write(response.getOutputStream())
                    .head(DailyProductionData.class)
                    .registerWriteHandler(new ExcelMergeStrategy(DailyProductionData.class))
                    .excelType(ExcelTypeEnum.XLSX).sheet().doWrite(providerList);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<DailyProductionData> queryProduction(DateRequest query) {
        if (StringUtils.isEmpty(query.getBeginDate())) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Calendar calendar = Calendar.getInstance();
            calendar.add(Calendar.DAY_OF_MONTH, -6);
            query.setBeginDate(sdf.format(calendar.getTime()));
            query.setEndDate(sdf.format(new Date()));
        }
        return largenScreenMapper.exportDailyProduction(query.getBeginDate(), query.getEndDate());


    }


}

 hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelMerge.java

New file
@@ -0,0 +1,27 @@
package com.mes.utils.excel;

import java.lang.annotation.*;

/**
 * @Author : zhoush
 * @Date: 2025/5/12 16:51
 * @Description:
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelMerge {
    /**
     * 是否合并单元格
     *
     * @return true || false
     */
    boolean merge() default true;

    /**
     * 是否为主键（即该字段相同的行合并）
     *
     * @return true || false
     */
    boolean isPrimaryKey() default false;
}

 hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelMergeStrategy.java

New file
@@ -0,0 +1,118 @@
package com.mes.utils.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author : zhoush
 * @Date: 2025/5/12 16:55
 * @Description:
 */
public class ExcelMergeStrategy implements RowWriteHandler {

    /**
     * 主键下标
     */
    private Integer primaryKeyIndex;

    /**
     * 需要合并的列的下标集合
     */
    private final List<Integer> mergeColumnIndexList = new ArrayList<>();

    /**
     * 数据类型
     */
    private final Class<?> elementType;

    public ExcelMergeStrategy(Class<?> elementType) {
        this.elementType = elementType;
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 判断是否为标题
        if (isHead) {
            return;
        }
        // 获取当前工作表
        Sheet sheet = writeSheetHolder.getSheet();
        // 初始化主键下标和需要合并字段的下标
        if (primaryKeyIndex == null) {
            this.initPrimaryIndexAndMergeIndex(writeSheetHolder);
        }
        // 判断是否需要和上一行进行合并
        // 不能和标题合并，只能数据行之间合并
        if (row.getRowNum() <= 1) {
            return;
        }
        // 获取上一行数据
        Row lastRow = sheet.getRow(row.getRowNum() - 1);
        // 将本行和上一行是同一类型的数据（通过主键字段进行判断），则需要合并
        if (lastRow.getCell(primaryKeyIndex).getStringCellValue().equalsIgnoreCase(row.getCell(primaryKeyIndex).getStringCellValue())) {
            for (Integer mergeIndex : mergeColumnIndexList) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(), mergeIndex, mergeIndex);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
            }
        }
    }

    /**
     * 初始化主键下标和需要合并字段的下标
     *
     * @param writeSheetHolder WriteSheetHolder
     */
    private void initPrimaryIndexAndMergeIndex(WriteSheetHolder writeSheetHolder) {
        // 获取当前工作表
        Sheet sheet = writeSheetHolder.getSheet();
        // 获取标题行
        Row titleRow = sheet.getRow(0);
        // 获取所有属性字段
        Field[] fields = this.elementType.getDeclaredFields();
        // 遍历所有字段
        for (Field field : fields) {
            // 获取@ExcelProperty注解，用于获取该字段对应列的下标
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            // 判断是否为空
            if (null == excelProperty) {
                continue;
            }
            // 获取自定义注解，用于合并单元格
            ExcelMerge excelMerge = field.getAnnotation(ExcelMerge.class);
            // 判断是否需要合并
            if (null == excelMerge) {
                continue;
            }
            for (int i = 0; i < fields.length; i++) {
                Cell cell = titleRow.getCell(i);
                if (null == cell) {
                    continue;
                }
                // 将字段和表头匹配上
                if (excelProperty.value()[0].equalsIgnoreCase(cell.getStringCellValue())) {
                    if (excelMerge.isPrimaryKey()) {
                        primaryKeyIndex = i;
                    }
                    if (excelMerge.merge()) {
                        mergeColumnIndexList.add(i);
                    }
                }
            }
        }

        // 没有指定主键，则异常
        if (null == this.primaryKeyIndex) {
            throw new IllegalStateException("使用@ExcelMerge注解必须指定主键");
        }
    }
}

 hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelUtil.java

New file
@@ -0,0 +1,19 @@
package com.mes.utils.excel;

import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

/**
 * @Author : zhoush
 * @Date: 2025/5/12 17:03
 * @Description:
 */
public class ExcelUtil {
    public static void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }
}

 hangzhoumesParent/common/servicebase/src/main/resources/mapper/LargenScreenMapper.xml

@@ -136,6 +136,167 @@
                                                     t6.product_date
        order by t.date
    </select>
    <select id="exportDailyProduction" resultType="com.mes.largenscreen.entity.DailyProductionData">
        with edg_storage_history_temp as (
            select t.glass_id_in as glass_id, t.create_time, STR_TO_DATE(t.create_time, '%Y-%m-%d') as product_date
            from edg_storage_device_task_history t
            where t.task_type in
                  (1, 3)
              and STR_TO_DATE(t.create_time, '%Y-%m-%d') BETWEEN #{beginDate} and #{endDate}
        )
           , big_storage_cage_history_temp as (
            select t.glass_id, t.create_time, STR_TO_DATE(t.create_time, '%Y-%m-%d') as product_date
            from big_storage_cage_history_task t
            where t.task_type = 2
              and STR_TO_DATE(t.create_time, '%Y-%m-%d') BETWEEN #{beginDate} and #{endDate}
        )
           , hollow_big_storage_cage_history_temp as (
            select t.glass_id, t.create_time, STR_TO_DATE(t.create_time, '%Y-%m-%d') as product_date
            from hollow_big_storage_cage_history_task t
            where t.task_type = 5
              and STR_TO_DATE(t.create_time, '%Y-%m-%d') BETWEEN #{beginDate} and #{endDate}
        )
           , engineer_temp as (
            select STR_TO_DATE(create_time, '%Y-%m-%d') as product_date, engineer_id, glass_total, glass_total_area
            from engineering
            where STR_TO_DATE(create_time, '%Y-%m-%d') BETWEEN #{beginDate} and #{endDate}
        )
           , edg_temp as (
            select t.product_date,
                   count(t.glass_id)                             as
                                                                    count_out,
                   round(sum(t1.width * t1.height) / 1000000, 1) as total_area_out
            from edg_storage_history_temp t
                     inner join glass_info t1 on t.glass_id = t1.glass_id
            group by product_date
        )
           , edg_time_one as (
            select product_date,
                   min(create_time)                                          as edg_begin_time,
                   max(create_time)                                          as edg_end_time,
                   TIMESTAMPDIFF(MINUTE, min(create_time), max(create_time)) AS edg_time_total
            from edg_storage_history_temp
            group by product_date
        )
           , edg_time_two as (
            SELECT t1.product_date,
                   sum(TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time)) AS edg_time_free
            FROM (SELECT product_date,
                         create_time,
                         LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
                  FROM edg_storage_history_temp
                 ) t1
                     JOIN
                 (SELECT create_time,
                         LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
                  FROM edg_storage_history_temp) t2 ON t1.next_timestamp = t2.create_time
            WHERE TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time) between 6 and 500
            group by t1.product_date
        )
           , big_storage_temp as (
            select t.product_date,
                   count(distinct t1.engineer_id, t1.tempering_layout_id) as tempering_layout_count,
                   count(distinct t1.glass_id)                            as tempering_glass_count,
                   round(sum(t1.width * t1.height) / 1000000, 1)          as tempering_area
            from big_storage_cage_history_temp t
                     inner join glass_info t1
                                on t.glass_id = t1.glass_id
            group by t.product_date
        )
           , big_storage_time_base as (
            select distinct product_date, create_time from big_storage_cage_history_temp
        )
           , big_storage_time_one as (select product_date,
                                             min(create_time)                                          as big_begin_time,
                                             max(create_time)                                          as big_end_time,
                                             TIMESTAMPDIFF(MINUTE, min(create_time), max(create_time)) AS big_time_total
                                      from big_storage_time_base
                                      group by product_date)
           , big_storage_time_two as (
            SELECT t1.product_date,
                   IFNULL(sum(TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time)), 0) AS big_time_free
            FROM (SELECT product_date,
                         create_time,
                         LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
                  FROM big_storage_time_base) t1
                     JOIN
                 (SELECT create_time,
                         LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
                  FROM big_storage_time_base) t2 ON t1.next_timestamp = t2.create_time
            WHERE TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time) between 11 and 500
            group by t1.product_date
        )
           , hollow_big_storage_temp as (
            select t.product_date,
                   count(distinct t1.glass_id)                   as hollow_glass_count,
                   round(sum(t1.width * t1.height) / 1000000, 1) as hollow_area
            from hollow_big_storage_cage_history_temp t
                     inner join glass_info t1
                                on t.glass_id = t1.glass_id
            group by t.product_date
        )
           , hollow_big_storage_time_base as (
            select distinct product_date, create_time from hollow_big_storage_cage_history_temp
        )
           , hollow_big_storage_time_one as (select product_date,
                                                    min(create_time)                                          as hollow_begin_time,
                                                    max(create_time)                                          as hollow_end_time,
                                                    TIMESTAMPDIFF(MINUTE, min(create_time), max(create_time)) AS hollow_time_total
                                             from hollow_big_storage_time_base
                                             group by product_date)
           , hollow_big_storage_time_two as (
            SELECT t1.product_date,
                   IFNULL(sum(TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time)), 0) AS hollow_time_free
            FROM (SELECT product_date,
                         create_time,
                         LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
                  FROM hollow_big_storage_time_base) t1
                     JOIN
                 (SELECT create_time,
                         LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
                  FROM hollow_big_storage_time_base) t2 ON t1.next_timestamp = t2.create_time
            WHERE TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time) between 11 and 500
            group by t1.product_date
        )
           , result as (
            select t.count_out,
                   t.total_area_out,
                   t1.*,
                   t2.edg_time_free,
                   t1.edg_time_total - t2.edg_time_free       as edg_time_diff,
                   t3.tempering_layout_count,
                   t3.tempering_glass_count,
                   t3.tempering_area,
                   t4.big_begin_time,
                   t4.big_end_time,
                   t4.big_time_total,
                   t5.big_time_free,
                   t4.big_time_total - t5.big_time_free       as big_time_diff,
                   t6.hollow_glass_count,
                   t6.hollow_area,
                   t7.hollow_begin_time,
                   t7.hollow_end_time,
                   t7.hollow_time_total,
                   t8.hollow_time_free,
                   t7.hollow_time_total - t8.hollow_time_free as hollow_time_diff,
                   t9.engineer_id,
                   t9.glass_total,
                   t9.glass_total_area
            from edg_temp t
                     LEFT JOIN edg_time_one t1 on t.product_date = t1.product_date
                     LEFT JOIN edg_time_two t2 on t.product_date = t2.product_date
                     LEFT JOIN big_storage_temp t3 on t.product_date = t3.product_date
                     LEFT JOIN big_storage_time_one t4 on t.product_date = t4.product_date
                     LEFT JOIN big_storage_time_two t5 on t.product_date = t5.product_date
                     LEFT JOIN hollow_big_storage_temp t6 on t.product_date = t6.product_date
                     LEFT JOIN hollow_big_storage_time_one t7 on t.product_date = t7.product_date
                     LEFT JOIN hollow_big_storage_time_two t8 on t.product_date = t8.product_date
                     LEFT JOIN engineer_temp t9 on t.product_date = t9.product_date
        )
        select *
        from result
        order by product_date
    </select>


</mapper>