From 544afa4dabaccb0248bf0b6fcfdc7b9b6c2ea9f2 Mon Sep 17 00:00:00 2001
From: gongchunyi <deslre0381@gmail.com>
Date: 星期二, 13 一月 2026 17:56:38 +0800
Subject: [PATCH] 合格率统计重构：根据物料属性分类返回对应的合格/不合格数据量
---
inspect-server/src/main/java/com/ruoyi/inspect/dto/MaterialPropTableDTO.java | 26 ++
inspect-server/src/main/resources/mapper/DataAnalysisMapper.xml | 492 +++++++++++++++++++++++++++-----------------
inspect-server/src/main/java/com/ruoyi/inspect/dto/DataAnalysisDto.java | 3
inspect-server/src/main/java/com/ruoyi/inspect/controller/DataAnalysisController.java | 28 ++
inspect-server/src/main/java/com/ruoyi/inspect/service/impl/DataAnalysisServiceImpl.java | 68 +++++
inspect-server/src/main/java/com/ruoyi/inspect/mapper/DataAnalysisMapper.java | 17 +
inspect-server/src/main/java/com/ruoyi/inspect/service/DataAnalysisService.java | 11
7 files changed, 438 insertions(+), 207 deletions(-)
diff --git a/inspect-server/src/main/java/com/ruoyi/inspect/controller/DataAnalysisController.java b/inspect-server/src/main/java/com/ruoyi/inspect/controller/DataAnalysisController.java
index 130e075..933df44 100644
--- a/inspect-server/src/main/java/com/ruoyi/inspect/controller/DataAnalysisController.java
+++ b/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
*/
diff --git a/inspect-server/src/main/java/com/ruoyi/inspect/dto/DataAnalysisDto.java b/inspect-server/src/main/java/com/ruoyi/inspect/dto/DataAnalysisDto.java
index 437239b..a1910c6 100644
--- a/inspect-server/src/main/java/com/ruoyi/inspect/dto/DataAnalysisDto.java
+++ b/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;
diff --git a/inspect-server/src/main/java/com/ruoyi/inspect/dto/MaterialPropTableDTO.java b/inspect-server/src/main/java/com/ruoyi/inspect/dto/MaterialPropTableDTO.java
new file mode 100644
index 0000000..2e76cd1
--- /dev/null
+++ b/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;
+}
diff --git a/inspect-server/src/main/java/com/ruoyi/inspect/mapper/DataAnalysisMapper.java b/inspect-server/src/main/java/com/ruoyi/inspect/mapper/DataAnalysisMapper.java
index 6cb9f0a..123c9fe 100644
--- a/inspect-server/src/main/java/com/ruoyi/inspect/mapper/DataAnalysisMapper.java
+++ b/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();
diff --git a/inspect-server/src/main/java/com/ruoyi/inspect/service/DataAnalysisService.java b/inspect-server/src/main/java/com/ruoyi/inspect/service/DataAnalysisService.java
index 20e4a0c..329880b 100644
--- a/inspect-server/src/main/java/com/ruoyi/inspect/service/DataAnalysisService.java
+++ b/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);
/**
* 鏌ヨ妫�楠岄」鍚嶇О
diff --git a/inspect-server/src/main/java/com/ruoyi/inspect/service/impl/DataAnalysisServiceImpl.java b/inspect-server/src/main/java/com/ruoyi/inspect/service/impl/DataAnalysisServiceImpl.java
index 33b9b28..5a9ea09 100644
--- a/inspect-server/src/main/java/com/ruoyi/inspect/service/impl/DataAnalysisServiceImpl.java
+++ b/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) {
diff --git a/inspect-server/src/main/resources/mapper/DataAnalysisMapper.xml b/inspect-server/src/main/resources/mapper/DataAnalysisMapper.xml
index 6e4e410..3c19453 100644
--- a/inspect-server/src/main/resources/mapper/DataAnalysisMapper.xml
+++ b/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 < 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 < 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 < 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 < 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
+ <
+ 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
--
Gitblit v1.9.3