合格率统计重构：根据物料属性分类返回对应的合格/不合格数据量

gongchunyi

2 天以前 544afa4dabaccb0248bf0b6fcfdc7b9b6c2ea9f2

合格率统计重构：根据物料属性分类返回对应的合格/不合格数据量

 inspect-server/src/main/java/com/ruoyi/inspect/controller/DataAnalysisController.java

@@ -25,30 +25,44 @@
    private DataAnalysisService dataAnalysisService;

    /**
     * æŸ¥è¯¢åŽŸææ–™æŸ±çŠ¶ç»Ÿè®¡
     * æŸ¥è¯¢ç‰©æ–™å±žæ€§æŸ±çŠ¶ç»Ÿè®¡
     *
     * @param dataAnalysisDto
     * @return
     */
    @ApiOperation(value = "查询原材料柱状统计")
    @ApiOperation(value = "查询物料属性柱状统计")
    @GetMapping("/getRawPassRateByBarChart")
    public Result getRawPassRateByBarChart(DataAnalysisDto dataAnalysisDto) {
        return Result.success(dataAnalysisService.getRawPassRateByBarChart(dataAnalysisDto));
    }

    /**
     * æŸ¥è¯¢åŽŸææ–™æŸ±çŠ¶ç»Ÿè®¡
     * æŸ¥è¯¢ç‰©æ–™å±žæ€§æŸ±çŠ¶ç»Ÿè®¡
     *
     * @param dataAnalysisDto
     * @return
     */
    @ApiOperation(value = "查询原材料合格率饼状态")
    @ApiOperation(value = "查询物料属性合格率饼状图")
    @GetMapping("/getRawPassRateByCake")
    public Result getRawPassRateByCake(DataAnalysisDto dataAnalysisDto) {
        return Result.success(dataAnalysisService.getRawPassRateByCake(dataAnalysisDto));
    }

    /**
     * æŸ¥è¯¢ç‰©æ–™å±žæ€§åˆæ ¼çŽ‡è¡¨æ ¼
     *
     * @param dataAnalysisDto
     * @return
     */
    @ApiOperation(value = "查询物料属性合格率表格")
    @GetMapping("/getMaterialPropTable")
    public Result getMaterialPropTable(DataAnalysisDto dataAnalysisDto) {
        return Result.success(dataAnalysisService.getMaterialPropTable(dataAnalysisDto));
    }

    /**
     * æŸ¥è¯¢åŽŸææ–™é¡¹
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -60,6 +74,7 @@

    /**
     * æŸ¥è¯¢åŽŸææ–™é¡¹æ£€åˆ†æž
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -71,6 +86,7 @@

    /**
     * æŸ¥è¯¢åŽŸææ–™é¡¹æ£€åˆ†æžåˆ—表
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -82,6 +98,7 @@

    /**
     * æŸ¥è¯¢åŽŸææ–™é¡¹æ£€åˆ†æžåˆ—表
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -93,6 +110,7 @@

    /**
     * æŸ¥è¯¢åŽŸææ–™é¡¹æ£€å’ŒåŽ‚家数据对比
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -104,6 +122,7 @@

    /**
     * æŸ¥è¯¢æœ¬æœˆä¸Žä¸Šä¸ªæœˆåˆæ ¼çŽ‡å¯¹æ¯”
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -115,6 +134,7 @@

    /**
     * æŸ¥è¯¢æ£€éªŒé¡¹ç±»åž‹é¥¼å›¾
     *
     * @param dataAnalysisDto
     * @return
     */

 inspect-server/src/main/java/com/ruoyi/inspect/dto/DataAnalysisDto.java

@@ -36,6 +36,9 @@
    @ApiModelProperty("分组类型, 0: é»˜è®¤æŒ‰ç…§æ ·å“åŒºåˆ† 1: åŒä¸€åŽ‚家, åŒä¸€åž‹å·, ä¸åŒæ‰¹æ¬¡, 2 : åŒä¸€åž‹å·, ä¸åŒåŽ‚家")
    private String groupType;

    @ApiModelProperty("物料属性")
    private String materialProp;

    @ApiModelProperty("选择的检验项名称")
    private List<String> itemNames;


 inspect-server/src/main/java/com/ruoyi/inspect/dto/MaterialPropTableDTO.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,26 @@
package com.ruoyi.inspect.dto;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

@Data
public class MaterialPropTableDTO {

    @ApiModelProperty(value = "修改后批次号")
    private String updateBatchNo;

    @ApiModelProperty(value = "抵达的采购数量")
    private BigDecimal qtyArrived;

    @ApiModelProperty(value = "零件描述")
    private String partDesc;

    @ApiModelProperty(value = "合格状态")
    private Integer inspectStatus;

    @ApiModelProperty(value = "下发时间")
    private Date sendTime;
}

 inspect-server/src/main/java/com/ruoyi/inspect/mapper/DataAnalysisMapper.java

@@ -2,6 +2,7 @@

import com.ruoyi.basic.dto.IfsInventoryQuantitySupplierDto;
import com.ruoyi.inspect.dto.DataAnalysisDto;
import com.ruoyi.inspect.dto.MaterialPropTableDTO;
import com.ruoyi.inspect.vo.RawMaterialSupplierVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@@ -19,32 +20,39 @@
public interface DataAnalysisMapper {

    /**
     * èŽ·å–本周的原材料信息
     * èŽ·å–本周的物料属性信息
     *
     * @return
     */
    List<Map<String, Object>> getRawPassRateByBarChartByWeek(@Param("dto") DataAnalysisDto dataAnalysisDto);

    /**
     * èŽ·å–本月的原材料信息
     * èŽ·å–本月的物料属性信息
     *
     * @return
     */
    List<Map<String, Object>> getRawPassRateByBarChartByDay(@Param("dto") DataAnalysisDto dataAnalysisDto);

    /**
     * èŽ·å–本年的原材料信息
     * èŽ·å–本年的物料属性信息
     *
     * @return
     */
    List<Map<String, Object>> getRawPassRateByBarChartByYear(@Param("dto") DataAnalysisDto dataAnalysisDtor);

    /**
     * æŸ¥çœ‹åŽŸææ–™é¥¼çŠ¶å›¾
     *
     * @return
     */
    Map<String, Object> getRawPassRateByCake(@Param("dto") DataAnalysisDto dataAnalysisDto);

    List<MaterialPropTableDTO> getMaterialPropTable(@Param("dto") DataAnalysisDto dataAnalysisDto);


    /**
     * æŸ¥è¯¢æ£€æµ‹é¡¹é›†åˆ
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -52,6 +60,7 @@

    /**
     * æŸ¥è¯¢æ‰€æœ‰çš„
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -62,12 +71,14 @@

    /**
     * æŸ¥è¯¢æœ¬æœˆä¸Žä¸Šæœˆåˆæ ¼çŽ‡å¯¹æ¯”
     *
     * @return
     */
    List<Map<String, Object>> getRawUpMonth();

    /**
     * æŸ¥è¯¢æ£€éªŒé¡¹ç±»åž‹é¥¼å›¾
     *
     * @return
     */
    Map<String, Object> getOrderTypeCookie();

 inspect-server/src/main/java/com/ruoyi/inspect/service/DataAnalysisService.java

@@ -3,6 +3,7 @@

import com.ruoyi.basic.dto.IfsInventoryQuantitySupplierDto;
import com.ruoyi.inspect.dto.DataAnalysisDto;
import com.ruoyi.inspect.dto.MaterialPropTableDTO;
import com.ruoyi.inspect.vo.DeviationAnalyzeVo;
import com.ruoyi.inspect.vo.RawProductAnalysisVo;

@@ -18,18 +19,24 @@
public interface DataAnalysisService {

    /**
     * æŸ¥è¯¢åŽŸææ–™æŸ±çŠ¶ç»Ÿè®¡
     * æŸ¥è¯¢ç‰©æ–™å±žæ€§æŸ±çŠ¶ç»Ÿè®¡
     * @return
     */
    List<Map<String, Object>> getRawPassRateByBarChart(DataAnalysisDto dataAnalysisDto);

    /**
     * æŸ¥è¯¢åŽŸææ–™åˆæ ¼çŽ‡é¥¼çŠ¶å›¾
     * æŸ¥è¯¢ç‰©æ–™å±žæ€§åˆæ ¼çŽ‡é¥¼çŠ¶å›¾
     * @param dataAnalysisDto
     * @return
     */
    Map<String, Object> getRawPassRateByCake(DataAnalysisDto dataAnalysisDto);

    /**
     * æŸ¥è¯¢ç‰©æ–™å±žæ€§åˆæ ¼çŽ‡é¥¼çŠ¶å›¾
     * @param dataAnalysisDto
     * @return
     */
    List<MaterialPropTableDTO> getMaterialPropTable(DataAnalysisDto dataAnalysisDto);

    /**
     * æŸ¥è¯¢æ£€éªŒé¡¹åç§°

 inspect-server/src/main/java/com/ruoyi/inspect/service/impl/DataAnalysisServiceImpl.java

@@ -9,6 +9,7 @@
import com.ruoyi.common.constant.InsOrderTypeConstants;
import com.ruoyi.framework.exception.ErrorException;
import com.ruoyi.inspect.dto.DataAnalysisDto;
import com.ruoyi.inspect.dto.MaterialPropTableDTO;
import com.ruoyi.inspect.dto.SampleProductRawAnalysisDto;
import com.ruoyi.inspect.mapper.DataAnalysisMapper;
import com.ruoyi.inspect.mapper.InsProductMapper;
@@ -43,7 +44,8 @@
    private InsProductMapper insProductMapper;

    /**
     * æŸ¥è¯¢åŽŸææ–™æŸ±çŠ¶ç»Ÿè®¡
     * æŸ¥è¯¢ç‰©æ–™å±žæ€§æŸ±çŠ¶ç»Ÿè®¡
     *
     * @return searchTime  æ—¶é—´
     * @return passRate  åˆæ ¼çŽ‡
     * @return sum  æ€»æ•°
@@ -64,7 +66,8 @@
            DateTime endOfWeek = DateUtil.endOfWeek(now);
            dataAnalysisDto.setBeginDate(DateUtil.format(beginOfWeek, "yyyy-MM-dd HH:mm:ss"));
            dataAnalysisDto.setEndDate(DateUtil.format(endOfWeek, "yyyy-MM-dd HH:mm:ss"));
            return dataAnalysisMapper.getRawPassRateByBarChartByWeek(dataAnalysisDto);
            List<Map<String, Object>> maps = dataAnalysisMapper.getRawPassRateByBarChartByWeek(dataAnalysisDto);
            return maps;
        } else if (dataAnalysisDto.getDateType().equals("2")) {
            // èŽ·å–当前月的开始时间（每月1号 00:00:00）
            DateTime beginOfMonth = DateUtil.beginOfMonth(now);
@@ -72,7 +75,8 @@
            DateTime endOfMonth = DateUtil.endOfMonth(now);
            dataAnalysisDto.setBeginDate(DateUtil.format(beginOfMonth, "yyyy-MM-dd HH:mm:ss"));
            dataAnalysisDto.setEndDate(DateUtil.format(endOfMonth, "yyyy-MM-dd HH:mm:ss"));
            return dataAnalysisMapper.getRawPassRateByBarChartByDay(dataAnalysisDto);
            List<Map<String, Object>> maps = dataAnalysisMapper.getRawPassRateByBarChartByDay(dataAnalysisDto);
            return maps;
        } else if (dataAnalysisDto.getDateType().equals("3")) {
            // èŽ·å–当前年的开始时间（每年1月1日 00:00:00）
            DateTime beginOfYear = DateUtil.beginOfYear(now);
@@ -80,7 +84,8 @@
            DateTime endOfYear = DateUtil.endOfYear(now);
            dataAnalysisDto.setBeginDate(DateUtil.format(beginOfYear, "yyyy-MM-dd HH:mm:ss"));
            dataAnalysisDto.setEndDate(DateUtil.format(endOfYear, "yyyy-MM-dd HH:mm:ss"));
            return dataAnalysisMapper.getRawPassRateByBarChartByYear(dataAnalysisDto);
            List<Map<String, Object>> maps = dataAnalysisMapper.getRawPassRateByBarChartByYear(dataAnalysisDto);
            return maps;
        }

        return null;
@@ -88,9 +93,9 @@

    /**
     * æŸ¥è¯¢åŽŸææ–™é¥¼çŠ¶å›¾
     *
     * @param dataAnalysisDto
     * @return
     * sum          : æ€»æ•°
     * @return sum          : æ€»æ•°
     * unQualified  : ä¸åˆæ ¼æ•°é‡
     * qualified  : åˆæ ¼æ•°é‡
     * passRate  : åˆæ ¼çŽ‡
@@ -125,12 +130,51 @@
            dataAnalysisDto.setBeginDate(DateUtil.format(beginOfYear, "yyyy-MM-dd HH:mm:ss"));
            dataAnalysisDto.setEndDate(DateUtil.format(endOfYear, "yyyy-MM-dd HH:mm:ss"));
        }
        return dataAnalysisMapper.getRawPassRateByCake(dataAnalysisDto);

        Map<String, Object> rawPassRateByCake = dataAnalysisMapper.getRawPassRateByCake(dataAnalysisDto);

        return rawPassRateByCake;
    }

    @Override
    public List<MaterialPropTableDTO> getMaterialPropTable(DataAnalysisDto dataAnalysisDto) {
        // æ ¼å¼åŒ–字段, é¿å…æŠ¥é”™
        this.formatDataAnalysisDto(dataAnalysisDto);
        // èŽ·å–当前日期
        DateTime now = DateUtil.date();
        if (StrUtil.isNotBlank(dataAnalysisDto.getBeginDate()) && StrUtil.isNotBlank(dataAnalysisDto.getEndDate())) {

        } else if (dataAnalysisDto.getDateType().equals("1")) {
            // èŽ·å–本周的开始时间（周一 00:00:00）
            DateTime beginOfWeek = DateUtil.beginOfWeek(now);
            // èŽ·å–本周的结束时间（周日 23:59:59）
            DateTime endOfWeek = DateUtil.endOfWeek(now);
            dataAnalysisDto.setBeginDate(DateUtil.format(beginOfWeek, "yyyy-MM-dd HH:mm:ss"));
            dataAnalysisDto.setEndDate(DateUtil.format(endOfWeek, "yyyy-MM-dd HH:mm:ss"));
        } else if (dataAnalysisDto.getDateType().equals("2")) {
            // èŽ·å–当前月的开始时间（每月1号 00:00:00）
            DateTime beginOfMonth = DateUtil.beginOfMonth(now);
            // èŽ·å–当前月的结束时间（本月最后一天 23:59:59）
            DateTime endOfMonth = DateUtil.endOfMonth(now);
            dataAnalysisDto.setBeginDate(DateUtil.format(beginOfMonth, "yyyy-MM-dd HH:mm:ss"));
            dataAnalysisDto.setEndDate(DateUtil.format(endOfMonth, "yyyy-MM-dd HH:mm:ss"));
        } else if (dataAnalysisDto.getDateType().equals("3")) {
            // èŽ·å–当前年的开始时间（每年1月1日 00:00:00）
            DateTime beginOfYear = DateUtil.beginOfYear(now);
            // èŽ·å–当前年的结束时间（每年12月31日 23:59:59）
            DateTime endOfYear = DateUtil.endOfYear(now);
            dataAnalysisDto.setBeginDate(DateUtil.format(beginOfYear, "yyyy-MM-dd HH:mm:ss"));
            dataAnalysisDto.setEndDate(DateUtil.format(endOfYear, "yyyy-MM-dd HH:mm:ss"));
        }

        List<MaterialPropTableDTO> rawPassRateByCake = dataAnalysisMapper.getMaterialPropTable(dataAnalysisDto);

        return rawPassRateByCake;
    }

    /**
     * æŸ¥è¯¢æ£€éªŒé¡¹åç§°
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -151,6 +195,7 @@

    /**
     * æŸ¥è¯¢åŽŸææ–™é¡¹æ£€åˆ†æž
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -202,6 +247,7 @@

    /**
     * æŸ¥è¯¢æ£€æµ‹é¡¹åˆ†æžåˆ—表
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -214,6 +260,7 @@

    /**
     * æŸ¥è¯¢é¡¹æ£€åˆ†æžåˆæ ¼çŽ‡
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -386,6 +433,7 @@

    /**
     * æŸ¥è¯¢æœ¬æœˆä¸Žä¸Šæœˆåˆæ ¼çŽ‡å¯¹æ¯”
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -396,6 +444,7 @@

    /**
     * æŸ¥è¯¢æ£€éªŒé¡¹ç±»åž‹é¥¼å›¾
     *
     * @param dataAnalysisDto
     * @return
     */
@@ -406,6 +455,7 @@

    /**
     * è®¡ç®—返回数据
     *
     * @param lastValues
     * @return
     */
@@ -473,6 +523,7 @@

    /**
     * è®¡ç®—极差
     *
     * @param lastValues
     * @return
     */
@@ -509,6 +560,7 @@

    /**
     * è®¡ç®—平均值
     *
     * @param values
     * @return
     */
@@ -531,6 +583,7 @@

    /**
     * è®¡ç®—标准偏差
     *
     * @return
     */
    private static BigDecimal computeStandardDeviation(List<String> lastValues) {
@@ -588,6 +641,7 @@

    /**
     * *****格式化字段****
     *
     * @param dataAnalysisDto
     */
    private void formatDataAnalysisDto(DataAnalysisDto dataAnalysisDto) {

 inspect-server/src/main/resources/mapper/DataAnalysisMapper.xml

@@ -1,195 +1,304 @@
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.ruoyi.inspect.mapper.DataAnalysisMapper">
    <!-- èŽ·å–本周的原材料信息 -->

    <!-- èŽ·å–本周的物料属性信息 -->
    <select id="getRawPassRateByBarChartByWeek" resultType="java.util.Map">
        WITH RECURSIVE AllWeeks AS (SELECT 1 AS week
                                     UNION ALL
                                     SELECT week + 1
                                     FROM AllWeeks
                                     WHERE week &lt; 7)
        SELECT COALESCE(sub.sum, 0)      AS sum,
               CASE am.week
                   WHEN 1 THEN '星期日'
                   WHEN 2 THEN '星期一'
                   WHEN 3 THEN '星期二'
                   WHEN 4 THEN '星期三'
                   WHEN 5 THEN '星期四'
                   WHEN 6 THEN '星期五'
                   WHEN 7 THEN '星期六'
                END                   AS searchTime,
               COALESCE(sub.passRate, 0) AS passRate
        WITH RECURSIVE AllWeeks AS (
        SELECT 1 AS week
        UNION ALL
        SELECT week + 1 FROM AllWeeks WHERE week &lt; 7
        )
        SELECT
        COALESCE(sub.qualified, 0) AS qualified,
        COALESCE(sub.unQualified, 0) AS unQualified,
        CASE am.week
        WHEN 1 THEN '星期日'
        WHEN 2 THEN '星期一'
        WHEN 3 THEN '星期二'
        WHEN 4 THEN '星期三'
        WHEN 5 THEN '星期四'
        WHEN 6 THEN '星期五'
        WHEN 7 THEN '星期六'
        END AS searchTime
        FROM AllWeeks am
                 LEFT JOIN (select COUNT(*)                 sum,
                                   DAYOFWEEK(io1.send_time) searchTime,
                                   ROUND((COUNT(*) - SUM(CASE WHEN inspect_status in (2, 4) THEN 1 ELSE 0 END)) / COUNT(*) *
                                         100,
                                         2)                 passRate
                            from ifs_inventory_quantity iiq
                                     LEFT JOIN ins_order io1 on io1.ifs_inventory_id = iiq.id
                                and io1.order_type = #{dto.orderType}
                                and io1.state != -1
                                     left join (select is2.ins_order_id,
                                                       is2.sample_type,
                                                       is2.model,
                                                       is2.sample
                                                from ins_sample is2
                                                group by is2.ins_order_id) ins on ins.ins_order_id = io1.id
                            where iiq.is_finish = 1
                              and iiq.inspect_status not in (0, 3)
                              and (io1.send_time between #{dto.beginDate} and #{dto.endDate})
        <if test="dto.sampleName != null and dto.sampleName != ''">
            and ins.sample like concat('%', #{dto.sampleName}, '%')
        LEFT JOIN (
        SELECT
        DAYOFWEEK(io1.send_time) AS week,
        SUM(CASE WHEN iiq.inspect_status = 1 THEN 1 ELSE 0 END) AS qualified,
        SUM(CASE WHEN iiq.inspect_status = 2 THEN 1 ELSE 0 END) AS unQualified
        FROM ifs_inventory_quantity iiq
        INNER JOIN ins_order io1
        ON io1.ifs_inventory_id = iiq.id
        LEFT JOIN (
        SELECT ins_order_id, MAX(sample) AS sample, MAX(model) AS model
        FROM ins_sample
        GROUP BY ins_order_id
        ) ins ON ins.ins_order_id = io1.id
        WHERE
        iiq.is_finish = 1
        AND iiq.inspect_status IN (1, 2)
        AND io1.order_type = #{dto.orderType}
        AND io1.state != -1
        AND io1.send_time BETWEEN #{dto.beginDate} AND #{dto.endDate}

        <if test="dto.materialProp != null and dto.materialProp.trim() != ''">
            AND IFNULL(iiq.material_prop,'') LIKE CONCAT('%',#{dto.materialProp},'%')
        </if>
        <if test="dto.modelName != null and dto.modelName != ''">
            and ins.model like concat('%', #{dto.modelName}, '%')
        <if test="dto.sampleName != null and dto.sampleName.trim() != ''">
            AND IFNULL(ins.sample,'') LIKE CONCAT('%',#{dto.sampleName},'%')
        </if>
        <if test="dto.supplierName != null and dto.supplierName != ''">
            and iiq.supplier_name like concat('%', #{dto.supplierName}, '%')
        <if test="dto.modelName != null and dto.modelName.trim() != ''">
            AND IFNULL(ins.model,'') LIKE CONCAT('%',#{dto.modelName},'%')
        </if>
                            GROUP BY DAYOFWEEK(io1.send_time)
                            having searchTime is not null) sub ON am.week = sub.searchTime
        WHERE am.week BETWEEN 1 AND 7
        <if test="dto.supplierName != null and dto.supplierName.trim() != ''">
            AND IFNULL(iiq.supplier_name,'') LIKE CONCAT('%',#{dto.supplierName},'%')
        </if>

        GROUP BY DAYOFWEEK(io1.send_time)
        ) sub ON am.week = sub.week
        ORDER BY am.week

    </select>

    <!-- èŽ·å–本月的原材料信息 -->

    <!-- èŽ·å–本月的物料属性信息 -->
    <!-- èŽ·å–本月的物料属性信息（按天，补全日期） -->
    <select id="getRawPassRateByBarChartByDay" resultType="java.util.Map">
        select COUNT(*)                         sum,
               DATE_FORMAT(io1.send_time, '%Y-%m-%d') searchTime,
               ROUND((COUNT(*) - SUM(CASE WHEN inspect_status in (2, 4) THEN 1 ELSE 0 END)) / COUNT(*) * 100,
                     2)                         passRate
        from ifs_inventory_quantity iiq
                 LEFT JOIN ins_order io1 on io1.ifs_inventory_id = iiq.id
            and io1.order_type = #{dto.orderType}
            and io1.state != -1
                 left join (select is2.ins_order_id,
                                   is2.sample_type,
                                   is2.model,
                                   is2.sample
                            from ins_sample is2
                            group by is2.ins_order_id) ins on ins.ins_order_id = io1.id
        where iiq.is_finish = 1
          and iiq.inspect_status not in (0, 3)
        and (io1.send_time between #{dto.beginDate} and #{dto.endDate})
        WITH RECURSIVE AllDays AS (
        SELECT DATE(#{dto.beginDate}) AS day
        UNION ALL
        SELECT DATE_ADD(day, INTERVAL 1 DAY)
        FROM AllDays
        WHERE day &lt; DATE(#{dto.endDate})
        )
        SELECT
        DATE_FORMAT(ad.day,'%Y-%m-%d') AS searchTime,
        COALESCE(sub.qualified,0) AS qualified,
        COALESCE(sub.unQualified,0) AS unQualified,
        CASE
        WHEN COALESCE(sub.total,0) = 0 THEN 0
        ELSE ROUND(sub.qualified / sub.total * 100, 2)
        END AS passRate
        FROM AllDays ad
        LEFT JOIN (
        SELECT
        DATE(io1.send_time) AS day,
        COUNT(*) AS total,
        SUM(CASE WHEN iiq.inspect_status = 1 THEN 1 ELSE 0 END) AS qualified,
        SUM(CASE WHEN iiq.inspect_status = 2 THEN 1 ELSE 0 END) AS unQualified
        FROM ifs_inventory_quantity iiq
        LEFT JOIN ins_order io1
        ON io1.ifs_inventory_id = iiq.id
        AND io1.order_type = #{dto.orderType}
        AND io1.state != -1
        AND io1.send_time BETWEEN #{dto.beginDate} AND #{dto.endDate}
        LEFT JOIN (
        SELECT ins_order_id, MAX(sample) AS sample, MAX(model) AS model
        FROM ins_sample
        GROUP BY ins_order_id
        ) ins ON ins.ins_order_id = io1.id
        WHERE
        iiq.is_finish = 1
        AND iiq.inspect_status IN (1,2)

        <if test="dto.materialProp != null and dto.materialProp != ''">
            AND IFNULL(iiq.material_prop,'') LIKE CONCAT('%',#{dto.materialProp},'%')
        </if>
        <if test="dto.sampleName != null and dto.sampleName != ''">
            and ins.sample like concat('%', #{dto.sampleName}, '%')
            AND IFNULL(ins.sample,'') LIKE CONCAT('%',#{dto.sampleName},'%')
        </if>
        <if test="dto.modelName != null and dto.modelName != ''">
            and ins.model like concat('%', #{dto.modelName}, '%')
            AND IFNULL(ins.model,'') LIKE CONCAT('%',#{dto.modelName},'%')
        </if>
        <if test="dto.supplierName != null and dto.supplierName != ''">
            and iiq.supplier_name like concat('%', #{dto.supplierName}, '%')
            AND IFNULL(iiq.supplier_name,'') LIKE CONCAT('%',#{dto.supplierName},'%')
        </if>
        GROUP BY DATE_FORMAT(io1.send_time, '%Y-%m-%d')
        having searchTime is not null
        order by searchTime

        GROUP BY DATE(io1.send_time)
        ) sub ON ad.day = sub.day
        ORDER BY ad.day
    </select>

    <!-- èŽ·å–本年的原材料信息 -->

    <!-- èŽ·å–本年的物料属性信息 -->
    <!-- èŽ·å–本年的物料属性信息 -->
    <select id="getRawPassRateByBarChartByYear" resultType="java.util.Map">
        WITH RECURSIVE AllMonths AS (SELECT 1 AS month
                                     UNION ALL
                                     SELECT month + 1
                                     FROM AllMonths
                                     WHERE month &lt; 12)
        SELECT COALESCE(sub.sum, 0)      AS sum,
               CASE am.month
                   WHEN 1 THEN '1月'
                   WHEN 2 THEN '2月'
                   WHEN 3 THEN '3月'
                   WHEN 4 THEN '4月'
                   WHEN 5 THEN '5月'
                   WHEN 6 THEN '6月'
                   WHEN 7 THEN '7月'
                   WHEN 8 THEN '8月'
                   WHEN 9 THEN '9月'
                   WHEN 10 THEN '10月'
                   WHEN 11 THEN '11月'
                   WHEN 12 THEN '12月'
                END                   AS searchTime,
               COALESCE(sub.passRate, 0) AS passRate
        WITH RECURSIVE AllMonths AS (
        SELECT 1 AS month
        UNION ALL
        SELECT month + 1 FROM AllMonths WHERE month
        &lt;
        12
        )
        SELECT
        CASE am.month
        WHEN 1 THEN '1月'
        WHEN 2 THEN '2月'
        WHEN 3 THEN '3月'
        WHEN 4 THEN '4月'
        WHEN 5 THEN '5月'
        WHEN 6 THEN '6月'
        WHEN 7 THEN '7月'
        WHEN 8 THEN '8月'
        WHEN 9 THEN '9月'
        WHEN 10 THEN '10月'
        WHEN 11 THEN '11月'
        WHEN 12 THEN '12月'
        END AS searchTime,
        COALESCE(sub.qualified,0) AS qualified,
        COALESCE(sub.unQualified,0) AS unQualified,
        CASE
        WHEN COALESCE(sub.total,0) = 0 THEN 0
        ELSE ROUND(sub.qualified / sub.total * 100, 2)
        END AS passRate
        FROM AllMonths am
                 LEFT JOIN (select COUNT(*)                         sum,
                                   DATE_FORMAT(io1.send_time, '%m') searchTime,
                                   ROUND((COUNT(*) - SUM(CASE WHEN inspect_status in (2, 4) THEN 1 ELSE 0 END)) /
                                         COUNT(*) * 100,
                                         2)                         passRate
                            from ifs_inventory_quantity iiq
                                     LEFT JOIN ins_order io1 on io1.ifs_inventory_id = iiq.id
                                and io1.order_type = #{dto.orderType}
                                and io1.state != -1
                                     left join (select is2.ins_order_id,
                                                       is2.sample_type,
                                                       is2.model,
                                                       is2.sample
                                                from ins_sample is2
                                                group by is2.ins_order_id) ins on ins.ins_order_id = io1.id
                            where iiq.is_finish = 1
                              and iiq.inspect_status not in (0, 3)
                              and (io1.send_time between #{dto.beginDate} and #{dto.endDate})
        LEFT JOIN (
        SELECT
        MONTH(io1.send_time) AS month,
        COUNT(*) AS total,
        SUM(CASE WHEN iiq.inspect_status = 1 THEN 1 ELSE 0 END) AS qualified,
        SUM(CASE WHEN iiq.inspect_status = 2 THEN 1 ELSE 0 END) AS unQualified
        FROM ifs_inventory_quantity iiq
        LEFT JOIN ins_order io1
        ON io1.ifs_inventory_id = iiq.id
        AND io1.order_type = #{dto.orderType}
        AND io1.state != -1
        AND io1.send_time BETWEEN #{dto.beginDate} AND #{dto.endDate}
        LEFT JOIN (
        SELECT ins_order_id, MAX(sample) AS sample, MAX(model) AS model
        FROM ins_sample
        GROUP BY ins_order_id
        ) ins ON ins.ins_order_id = io1.id
        WHERE
        iiq.is_finish = 1
        AND iiq.inspect_status IN (1,2)

        <if test="dto.materialProp != null and dto.materialProp != ''">
            AND IFNULL(iiq.material_prop,'') LIKE CONCAT('%',#{dto.materialProp},'%')
        </if>
        <if test="dto.sampleName != null and dto.sampleName != ''">
            and ins.sample like concat('%', #{dto.sampleName}, '%')
            AND IFNULL(ins.sample,'') LIKE CONCAT('%',#{dto.sampleName},'%')
        </if>
        <if test="dto.modelName != null and dto.modelName != ''">
            and ins.model like concat('%', #{dto.modelName}, '%')
            AND IFNULL(ins.model,'') LIKE CONCAT('%',#{dto.modelName},'%')
        </if>
        <if test="dto.supplierName != null and dto.supplierName != ''">
            and iiq.supplier_name like concat('%', #{dto.supplierName}, '%')
            AND IFNULL(iiq.supplier_name,'') LIKE CONCAT('%',#{dto.supplierName},'%')
        </if>
                            GROUP BY DATE_FORMAT(io1.send_time, '%Y-%m')
                            having searchTime is not null) sub ON am.month = sub.searchTime
        WHERE am.month BETWEEN 1 AND 12

        GROUP BY MONTH(io1.send_time)
        ) sub ON am.month = sub.month
        ORDER BY am.month
    </select>

    <!-- æŸ¥çœ‹åŽŸææ–™é¥¼çŠ¶å›¾ -->

    <!-- æŸ¥çœ‹ç‰©æ–™å±žæ€§é¥¼çŠ¶å›¾ -->
    <select id="getRawPassRateByCake" resultType="java.util.Map">
        select COUNT(*)                                                                                             sum,
               (COUNT(*) - SUM(CASE WHEN inspect_status in (2, 4) THEN 1 ELSE 0 END))                               qualified,
               (COUNT(*) - SUM(CASE WHEN inspect_status = 1 THEN 1 ELSE 0 END))                                     unQualified,
               ROUND((COUNT(*) - SUM(CASE WHEN inspect_status in (2, 4) THEN 1 ELSE 0 END)) / COUNT(*) * 100,
                     2) AS passRate
        from ifs_inventory_quantity iiq
                 LEFT JOIN ins_order io1 on io1.ifs_inventory_id = iiq.id
            and io1.order_type = #{dto.orderType}
            and io1.state != -1
                 left join (select is2.ins_order_id,
                                   is2.sample_type,
                                   is2.model,
                                   is2.sample
                            from ins_sample is2
                            group by is2.ins_order_id) ins on ins.ins_order_id = io1.id
        where iiq.is_finish = 1
          and iiq.inspect_status not in (0, 3)
          and (io1.send_time between #{dto.beginDate} and #{dto.endDate})
        SELECT
        COUNT(*) AS sum,
        /* åˆæ ¼ */
        SUM(CASE WHEN iiq.inspect_status = 1 THEN 1 ELSE 0 END) AS qualified,
        /* ä¸åˆæ ¼ */
        SUM(CASE WHEN iiq.inspect_status = 2 THEN 1 ELSE 0 END) AS unQualified,
        ROUND(
        SUM(CASE WHEN iiq.inspect_status = 1 THEN 1 ELSE 0 END)
        / COUNT(*) * 100,
        2
        ) AS passRate
        FROM ifs_inventory_quantity iiq
        INNER JOIN ins_order io1
        ON io1.ifs_inventory_id = iiq.id
        LEFT JOIN (
        SELECT
        is2.ins_order_id,
        MAX(is2.sample_type) AS sample_type,
        MAX(is2.model) AS model,
        MAX(is2.sample) AS sample
        FROM ins_sample is2
        GROUP BY is2.ins_order_id
        ) ins ON ins.ins_order_id = io1.id
        WHERE
        iiq.is_finish = 1
        AND iiq.inspect_status IN (1, 2)
        AND io1.order_type = #{dto.orderType}
        AND io1.state != -1
        AND io1.send_time BETWEEN #{dto.beginDate} AND #{dto.endDate}

        <if test="dto.materialProp != null and dto.materialProp.trim() != ''">
            AND IFNULL(iiq.material_prop, '') LIKE CONCAT('%', #{dto.materialProp}, '%')
        </if>
        <if test="dto.sampleName != null and dto.sampleName.trim() != ''">
            AND IFNULL(ins.sample, '') LIKE CONCAT('%', #{dto.sampleName}, '%')
        </if>
        <if test="dto.modelName != null and dto.modelName.trim() != ''">
            AND IFNULL(ins.model, '') LIKE CONCAT('%', #{dto.modelName}, '%')
        </if>
        <if test="dto.supplierName != null and dto.supplierName.trim() != ''">
            AND IFNULL(iiq.supplier_name, '') LIKE CONCAT('%', #{dto.supplierName}, '%')
        </if>
    </select>

    <select id="getMaterialPropTable" resultType="com.ruoyi.inspect.dto.MaterialPropTableDTO">
        SELECT
        iiq.update_batch_no AS updateBatchNo, -- ä¿®æ”¹åŽæ‰¹æ¬¡å·
        iiq.qty_arrived AS qtyArrived, -- æŠµè¾¾çš„采购数量
        iiq.part_desc AS partDesc, -- é›¶ä»¶æè¿°
        iiq.inspect_status AS inspectStatus, -- åˆæ ¼çŠ¶æ€
        io1.send_time AS sendTime -- ä¸‹å‘时间
        FROM ifs_inventory_quantity iiq
        INNER JOIN ins_order io1
        ON io1.ifs_inventory_id = iiq.id
        AND io1.order_type = #{dto.orderType}
        AND io1.state != -1
        AND io1.send_time BETWEEN #{dto.beginDate} AND #{dto.endDate}
        LEFT JOIN (
        SELECT
        ins_order_id,
        MAX(sample) AS sample,
        MAX(model) AS model
        FROM ins_sample
        GROUP BY ins_order_id
        ) ins ON ins.ins_order_id = io1.id
        WHERE
        iiq.is_finish = 1
        AND iiq.inspect_status IN (1,2)

        <if test="dto.materialProp != null and dto.materialProp != ''">
            AND IFNULL(iiq.material_prop,'') LIKE CONCAT('%',#{dto.materialProp},'%')
        </if>

        <if test="dto.sampleName != null and dto.sampleName != ''">
            and ins.sample like concat('%', #{dto.sampleName}, '%')
            AND IFNULL(ins.sample,'') LIKE CONCAT('%',#{dto.sampleName},'%')
        </if>

        <if test="dto.modelName != null and dto.modelName != ''">
            and ins.model like concat('%', #{dto.modelName}, '%')
            AND IFNULL(ins.model,'') LIKE CONCAT('%',#{dto.modelName},'%')
        </if>

        <if test="dto.supplierName != null and dto.supplierName != ''">
            and iiq.supplier_name like concat('%', #{dto.supplierName}, '%')
            AND IFNULL(iiq.supplier_name,'') LIKE CONCAT('%',#{dto.supplierName},'%')
        </if>

        ORDER BY io1.send_time DESC
    </select>

    <select id="getRawProductAnalysisAllSample" resultType="com.ruoyi.basic.dto.IfsInventoryQuantitySupplierDto">
        select iiq.*,
               io1.entrust_code,
               io1.id     enter_order_id,
               ins.id     sample_id,
               ins.sample_type,
               ins.sample sample_name,
               ins.model  sample_model,
               io1.send_time
        io1.entrust_code,
        io1.id enter_order_id,
        ins.id sample_id,
        ins.sample_type,
        ins.sample sample_name,
        ins.model sample_model,
        io1.send_time
        from ifs_inventory_quantity iiq
                 LEFT JOIN ins_order io1 on io1.ifs_inventory_id = iiq.id
            and io1.order_type = #{dto.orderType}
            and io1.state != -1
                 left join ins_sample  ins on ins.ins_order_id = io1.id
        LEFT JOIN ins_order io1 on io1.ifs_inventory_id = iiq.id
        and io1.order_type = #{dto.orderType}
        and io1.state != -1
        left join ins_sample ins on ins.ins_order_id = io1.id
        where iiq.is_finish = 1
          and iiq.inspect_status not in (0, 3)
        and iiq.inspect_status not in (0, 3)
        <if test="dto.beginDate != null and dto.beginDate != '' and dto.endDate != null and dto.endDate != ''">
            and (io1.send_time between #{dto.beginDate} and #{dto.endDate})
        </if>
@@ -207,29 +316,29 @@
    <!-- æŸ¥è¯¢æ£€æµ‹é¡¹é›†åˆ -->
    <select id="getRawProductAnalysisList" resultType="com.ruoyi.basic.dto.IfsInventoryQuantitySupplierDto">
        select iiq.*,
               io1.entrust_code,
               io1.id     enter_order_id,
               ins.id     sample_id,
               ins.sample_type,
               ins.sample sample_name,
               ins.model  sample_model,
               u.name     user_name,
               io1.send_time
        io1.entrust_code,
        io1.id enter_order_id,
        ins.id sample_id,
        ins.sample_type,
        ins.sample sample_name,
        ins.model sample_model,
        u.name user_name,
        io1.send_time
        from ifs_inventory_quantity iiq
                 LEFT JOIN ins_order io1 on io1.ifs_inventory_id = iiq.id
            and io1.order_type = #{dto.orderType}
            and io1.state != -1
                 left join user u on io1.create_user = u.id
                 left join (select is2.ins_order_id,
                                   is2.sample_type,
                                   is2.sample_code,
                                   is2.model,
                                   is2.sample,
                                   is2.id
                            from ins_sample is2
                            group by is2.ins_order_id) ins on ins.ins_order_id = io1.id
        LEFT JOIN ins_order io1 on io1.ifs_inventory_id = iiq.id
        and io1.order_type = #{dto.orderType}
        and io1.state != -1
        left join user u on io1.create_user = u.id
        left join (select is2.ins_order_id,
        is2.sample_type,
        is2.sample_code,
        is2.model,
        is2.sample,
        is2.id
        from ins_sample is2
        group by is2.ins_order_id) ins on ins.ins_order_id = io1.id
        where iiq.is_finish = 1
          and iiq.inspect_status not in (0, 3)
        and iiq.inspect_status not in (0, 3)
        <if test="dto.beginDate != null and dto.beginDate != '' and dto.endDate != null and dto.endDate != ''">
            and (io1.send_time between #{dto.beginDate} and #{dto.endDate})
        </if>
@@ -244,26 +353,26 @@
        </if>
    </select>
    <select id="getItemValueByOrderIds" resultType="com.ruoyi.inspect.vo.RawMaterialSupplierVo">
        select io2.id                   orderId,
               io2.entrust_code,
               ins.sample,
               ins.model,
               iiq.supplier_name,
               (select ip.`last_value`
                from ins_product ip
                where concat(ip.inspection_item, ip.inspection_item_subclass) = #{itemName}
                  and ip.ins_sample_id = ins.id
                limit 1) lastValue
        select io2.id orderId,
        io2.entrust_code,
        ins.sample,
        ins.model,
        iiq.supplier_name,
        (select ip.`last_value`
        from ins_product ip
        where concat(ip.inspection_item, ip.inspection_item_subclass) = #{itemName}
        and ip.ins_sample_id = ins.id
        limit 1) lastValue
        from ins_order io2
                 left join ifs_inventory_quantity iiq on io2.ifs_inventory_id = iiq.id
                 left join (select is2.ins_order_id,
                                   is2.sample_type,
                                   is2.sample_code,
                                   is2.model,
                                   is2.sample,
                                   is2.id
                            from ins_sample is2
                            group by is2.ins_order_id) ins on ins.ins_order_id = io2.id
        left join ifs_inventory_quantity iiq on io2.ifs_inventory_id = iiq.id
        left join (select is2.ins_order_id,
        is2.sample_type,
        is2.sample_code,
        is2.model,
        is2.sample,
        is2.id
        from ins_sample is2
        group by is2.ins_order_id) ins on ins.ins_order_id = io2.id
        where io2.id in
        <foreach collection="insOrderIds" index="index" open="(" separator="," close=")" item="val">
            #{val}
@@ -272,7 +381,7 @@

    <!-- æŸ¥è¯¢æœ¬æœˆä¸Žä¸Šæœˆåˆæ ¼çŽ‡å¯¹æ¯” -->
    <select id="getRawUpMonth" resultType="java.util.Map">
        select DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') as month,
        select DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') as                           month,
               (select ROUND((COUNT(*) - SUM(CASE WHEN inspect_status in (2, 4) THEN 1 ELSE 0 END)) / COUNT(*) *
                             100,
                             2) passRate
@@ -282,10 +391,11 @@
                    and io1.state != -1
                where iiq.is_finish = 1
                  and iiq.inspect_status not in (0, 3)
                  and (io1.send_time between DATE_FORMAT(DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH), '%Y-%m-%d %H:%i:%s')
                  and (io1.send_time between DATE_FORMAT(DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH),
                                                         '%Y-%m-%d %H:%i:%s')
                    and DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59'))) passRate
        UNION ALL
        select DATE_FORMAT(CURRENT_DATE, '%Y-%m') as month,
        select DATE_FORMAT(CURRENT_DATE, '%Y-%m') as                            month,
               (select ROUND((COUNT(*) - SUM(CASE WHEN inspect_status in (2, 4) THEN 1 ELSE 0 END)) / COUNT(*) *
                             100,
                             2) passRate