src/main/java/com/ruoyi/basic/dto/ProductModelDto.java
@@ -8,5 +8,8 @@ @Data public class ProductModelDto extends ProductModel { private Long productId; private List<ProductStructureDto> productStructureList; } src/main/java/com/ruoyi/basic/service/impl/ProductModelServiceImpl.java
@@ -21,6 +21,7 @@ import com.ruoyi.sales.mapper.SalesLedgerProductMapper; import com.ruoyi.sales.pojo.SalesLedgerProduct; import lombok.AllArgsConstructor; import org.springframework.dao.DuplicateKeyException; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import org.springframework.web.multipart.MultipartFile; @@ -44,16 +45,23 @@ @Override public int addOrEditProductModel(ProductModelDto productModelDto) { if (productModelDto.getId() == null) { ProductModel productModel = new ProductModel(); BeanUtils.copyProperties(productModelDto, productModel); return productModelMapper.insert(productModel); } else { return productModelMapper.updateById(productModelDto); ProductModel productModel = new ProductModel(); BeanUtils.copyProperties(productModelDto, productModel); try { if (productModelDto.getId() == null) { int result = productModelMapper.insert(productModel); productModelDto.setId(productModel.getId()); return result; } else { int result = productModelMapper.updateById(productModel); productModelDto.setId(productModel.getId()); return result; } } catch (DuplicateKeyException e) { // 捕获数据库唯一约束异常 throw new RuntimeException("该产品已存在相同的规格型号和单位", e); } } @Override public int delProductModel(Long[] ids) { src/main/java/com/ruoyi/common/enums/StockInQualifiedRecordTypeEnum.java
@@ -8,10 +8,10 @@ CUSTOMIZATION_STOCK_IN("0", "合格自定义入库"), PRODUCTION_REPORT_STOCK_IN("2", "生产报工-入库"), PRODUCTION_REPORT_STOCK_IN("2", "生产报工-入库"); // PURCHASE_STOCK_IN("7", "采购-入库"), QUALITYINSPECT_STOCK_IN("6", "质检-合格入库"), DEFECTIVE_PASS("11", "不合格-让步放行"); // QUALITYINSPECT_STOCK_IN("6", "质检-合格入库"), // DEFECTIVE_PASS("11", "不合格-让步放行"); private final String code; src/main/java/com/ruoyi/common/enums/StockOutQualifiedRecordTypeEnum.java
@@ -5,8 +5,8 @@ @Getter public enum StockOutQualifiedRecordTypeEnum implements BaseEnum<String> { CUSTOMIZATION_STOCK_OUT("1", "合格自定义出库"), PRODUCTION_REPORT_STOCK_OUT("3", "生产报工-出库"); CUSTOMIZATION_STOCK_OUT("1", "合格自定义出库"); // PRODUCTION_REPORT_STOCK_OUT("3", "生产报工-出库"); // SALE_STOCK_OUT("8", "销售-出库"), // SALE_SHIP_STOCK_OUT("13", "销售-发货出库");
@@ -4,7 +4,6 @@ import com.baomidou.mybatisplus.core.toolkit.ObjectUtils; import com.ruoyi.procurementrecord.mapper.ProcurementRecordMapper; import com.ruoyi.procurementrecord.mapper.ProcurementRecordOutMapper; import com.ruoyi.stock.dto.StockInRecordDto; import com.ruoyi.stock.dto.StockInventoryDto; import com.ruoyi.stock.dto.StockUninventoryDto; import com.ruoyi.stock.pojo.StockInRecord; @@ -13,15 +12,11 @@ import com.ruoyi.stock.service.StockInventoryService; import com.ruoyi.stock.service.StockOutRecordService; import com.ruoyi.stock.service.StockUninventoryService; import com.ruoyi.stock.service.impl.StockInRecordServiceImpl; import com.ruoyi.stock.service.impl.StockOutRecordServiceImpl; import lombok.RequiredArgsConstructor; import org.springframework.stereotype.Component; import java.math.BigDecimal; import java.util.Collections; import java.util.HashMap; import java.util.Map; @Component @RequiredArgsConstructor @@ -72,12 +67,13 @@ * @param recordType * @param recordId */ public void addStock(Long productModelId, BigDecimal quantity, String recordType,Long recordId) { public void addStock(Long productModelId, BigDecimal quantity, String recordType,Long recordId,Long productId) { StockInventoryDto stockInventoryDto = new StockInventoryDto(); stockInventoryDto.setRecordId(recordId); stockInventoryDto.setRecordType(String.valueOf(recordType)); stockInventoryDto.setQualitity(quantity); stockInventoryDto.setProductModelId(productModelId); stockInventoryDto.setProductId(productId); stockInventoryService.addstockInventory(stockInventoryDto); }
@@ -169,7 +169,7 @@ /*新增质检*/ List<ProductProcessRouteItem> productProcessRouteItems = productProcessRouteItemMapper.selectList(Wrappers.<ProductProcessRouteItem>lambdaQuery().eq(ProductProcessRouteItem::getProductRouteId, productProcessRouteItem.getProductRouteId())); //不管是否质检都直接入库 stockUtils.addStock(productProcessRouteItem.getProductModelId(), productQty, StockInQualifiedRecordTypeEnum.PRODUCTION_REPORT_STOCK_IN.getCode(), productionProductMain.getId()); stockUtils.addStock(productProcessRouteItem.getProductModelId(), productQty, StockInQualifiedRecordTypeEnum.PRODUCTION_REPORT_STOCK_IN.getCode(), productionProductMain.getId(),productModel.getProductId()); /*更新工单和生产订单*/ ProductWorkOrder productWorkOrder = productWorkOrderMapper.selectById(dto.getWorkOrderId()); productWorkOrder.setCompleteQuantity(productWorkOrder.getCompleteQuantity().add(productQty)); @@ -187,7 +187,7 @@ } if (productProcessRouteItem.getDragSort() == productProcessRouteItems.size()) { //如果是最后一道工序报工之后生产订单完成数量+ productOrder.setCompleteQuantity(productOrder.getCompleteQuantity().add(productQty)); productOrder.setCompleteQuantity(productWorkOrder.getCompleteQuantity()); if (productOrder.getCompleteQuantity().compareTo(productOrder.getQuantity()) == 0) { productOrder.setEndTime(LocalDateTime.now());//结束时间 } @@ -276,7 +276,7 @@ new LambdaQueryWrapper<QualityInspectParam>() .eq(QualityInspectParam::getInspectId, q.getId())); qualityInspectMapper.deleteById(q.getId()); stockUtils.deleteStockInRecord(q.getId(), StockInQualifiedRecordTypeEnum.QUALITYINSPECT_STOCK_IN.getCode()); stockUtils.deleteStockInRecord(q.getId(), StockInQualifiedRecordTypeEnum.PRODUCTION_REPORT_STOCK_IN.getCode()); }); // 删除产出记录 src/main/java/com/ruoyi/quality/pojo/QualityUnqualified.java
@@ -136,8 +136,13 @@ @ApiModelProperty("关联检测id") private Long inspectId; @ApiModelProperty("是否不合格处理自己新增") @TableField(exist = false) private Boolean method; @ApiModelProperty("批号") private String batchNo; @ApiModelProperty("检测类型") private Integer checkType; } src/main/java/com/ruoyi/quality/pojo/RawMaterial.java
@@ -1,13 +1,6 @@ package com.ruoyi.quality.pojo; import com.baomidou.mybatisplus.annotation.*; import java.io.Serializable; import java.math.BigDecimal; import java.time.LocalDate; import java.time.LocalDateTime; import java.util.Date; import com.fasterxml.jackson.annotation.JsonFormat; import com.ruoyi.framework.aspectj.lang.annotation.Excel; import io.swagger.annotations.ApiModel; @@ -15,6 +8,10 @@ import lombok.Getter; import lombok.Setter; import org.springframework.format.annotation.DateTimeFormat; import java.io.Serializable; import java.time.LocalDateTime; import java.util.Date; /** * <p> @@ -47,7 +44,7 @@ private String batchNo; @ApiModelProperty("检验结果 0合格 1不合格") @Excel(name = "检验结果", readConverterExp = "0=合格,1=不合格") @Excel(name = "检验结果", readConverterExp = "0=不合格,1=合格") private Integer checkResult; @ApiModelProperty("类别(0:未提交;1:已提交)") src/main/java/com/ruoyi/quality/service/impl/QualityInspectServiceImpl.java
@@ -9,7 +9,6 @@ import com.deepoove.poi.XWPFTemplate; import com.deepoove.poi.config.Configure; import com.ruoyi.common.enums.StockInQualifiedRecordTypeEnum; import com.ruoyi.common.enums.StockOutQualifiedRecordTypeEnum; import com.ruoyi.common.utils.HackLoopTableRenderPolicy; import com.ruoyi.common.utils.poi.ExcelUtil; import com.ruoyi.procurementrecord.service.ProcurementRecordService; @@ -98,7 +97,7 @@ qualityUnqualifiedMapper.insert(qualityUnqualified); } else { //合格直接入库 stockUtils.addStock(qualityInspect.getProductModelId(), qualityInspect.getQuantity(), StockInQualifiedRecordTypeEnum.QUALITYINSPECT_STOCK_IN.getCode(), qualityInspect.getId()); stockUtils.addStock(qualityInspect.getProductModelId(), qualityInspect.getQuantity(), StockInQualifiedRecordTypeEnum.PRODUCTION_REPORT_STOCK_IN.getCode(), qualityInspect.getId(),qualityInspect.getProductId()); } qualityInspect.setInspectState(1);//已提交 return qualityInspectMapper.updateById(qualityInspect); src/main/java/com/ruoyi/quality/service/impl/QualityReportServiceImpl.java
@@ -1,9 +1,6 @@ package com.ruoyi.quality.service.impl; import com.ruoyi.basic.service.IProductModelService; import com.ruoyi.basic.service.IProductService; import com.ruoyi.common.utils.StringUtils; import com.ruoyi.production.service.ProductOrderService; import com.ruoyi.quality.dto.*; import com.ruoyi.quality.mapper.QualityInspectMapper; import com.ruoyi.quality.service.QualityReportService; src/main/java/com/ruoyi/quality/service/impl/QualityUnqualifiedServiceImpl.java
@@ -8,7 +8,6 @@ import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.ruoyi.common.enums.StockInQualifiedRecordTypeEnum; import com.ruoyi.common.enums.StockOutQualifiedRecordTypeEnum; import com.ruoyi.common.enums.StockInUnQualifiedRecordTypeEnum; import com.ruoyi.common.utils.bean.BeanUtils; import com.ruoyi.common.utils.poi.ExcelUtil; @@ -17,7 +16,10 @@ import com.ruoyi.production.mapper.ProductProcessRouteMapper; import com.ruoyi.production.mapper.ProductWorkOrderMapper; import com.ruoyi.production.mapper.ProductionProductMainMapper; import com.ruoyi.production.pojo.*; import com.ruoyi.production.pojo.ProductOrder; import com.ruoyi.production.pojo.ProductProcessRoute; import com.ruoyi.production.pojo.ProductProcessRouteItem; import com.ruoyi.production.pojo.ProductWorkOrder; import com.ruoyi.production.service.ProductOrderService; import com.ruoyi.quality.mapper.QualityUnqualifiedMapper; import com.ruoyi.quality.pojo.QualityInspect; @@ -131,7 +133,7 @@ break; case "让步放行": //调用提交合格的接口 stockUtils.addStock(qualityInspect.getProductModelId(), unqualified.getQuantity(), StockInQualifiedRecordTypeEnum.DEFECTIVE_PASS.getCode(), unqualified.getId()); stockUtils.addStock(qualityInspect.getProductModelId(), unqualified.getQuantity(), StockInQualifiedRecordTypeEnum.PRODUCTION_REPORT_STOCK_IN.getCode(), unqualified.getId(),qualityInspect.getProductId()); break; default: break; @@ -146,7 +148,7 @@ break; case "让步放行": //调用提交合格的接口 stockUtils.addStock(modelId, unqualified.getQuantity(), StockInQualifiedRecordTypeEnum.DEFECTIVE_PASS.getCode(), unqualified.getId()); stockUtils.addStock(modelId, unqualified.getQuantity(), StockInQualifiedRecordTypeEnum.PRODUCTION_REPORT_STOCK_IN.getCode(), unqualified.getId(),qualityInspect.getProductId()); break; default: break; src/main/java/com/ruoyi/sales/service/impl/SalesLedgerProductServiceImpl.java
@@ -389,7 +389,7 @@ //删除出库记录 for (Long productMainId : productMainIds) { //删除生产出库记录 stockUtils.deleteStockOutRecord(productMainId, StockOutQualifiedRecordTypeEnum.PRODUCTION_REPORT_STOCK_OUT.getCode()); stockUtils.deleteStockOutRecord(productMainId, StockOutQualifiedRecordTypeEnum.CUSTOMIZATION_STOCK_OUT.getCode()); //删除报废的入库记录 stockUtils.deleteStockInRecord(productMainId, StockInUnQualifiedRecordTypeEnum.PRODUCTION_SCRAP.getCode()); } src/main/java/com/ruoyi/stock/execl/StockInventoryExportData.java
@@ -22,12 +22,12 @@ @Excel(name = "库存数量") private BigDecimal qualitity; @Excel(name = "预警数量") private BigDecimal warnNum; @Excel(name = "冻结数量") private BigDecimal lockedQuantity; @Excel(name = "净重(吨)") private BigDecimal netWeight; @Excel(name = "备注") private String remark; // src/main/java/com/ruoyi/stock/service/impl/StockInventoryServiceImpl.java
@@ -1,15 +1,19 @@ package com.ruoyi.stock.service.impl; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.core.toolkit.ObjectUtils; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.ruoyi.basic.mapper.ProductMapper; import com.ruoyi.basic.mapper.ProductModelMapper; import com.ruoyi.basic.pojo.Product; import com.ruoyi.basic.pojo.ProductModel; import com.ruoyi.common.enums.StockInQualifiedRecordTypeEnum; import com.ruoyi.common.utils.poi.ExcelUtil; import com.ruoyi.framework.web.domain.R; import com.ruoyi.sales.mapper.SalesLedgerProductMapper; import com.ruoyi.sales.pojo.SalesLedgerProduct; import com.ruoyi.stock.dto.StockInRecordDto; import com.ruoyi.stock.dto.StockInventoryDto; import com.ruoyi.stock.dto.StockOutRecordDto; @@ -21,6 +25,7 @@ import com.ruoyi.stock.service.StockOutRecordService; import com.ruoyi.stock.word.WeighbridgeDocGenerator; import lombok.RequiredArgsConstructor; import org.springframework.aop.framework.AopContext; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; @@ -29,7 +34,6 @@ import javax.servlet.http.HttpServletResponse; import java.math.BigDecimal; import java.util.ArrayList; import java.util.List; /** @@ -49,6 +53,8 @@ private final StockOutRecordService stockOutRecordService; private final SalesLedgerProductMapper salesLedgerProductMapper; private final WeighbridgeDocGenerator weighbridgeDocGenerator; private final ProductMapper productMapper; private final ProductModelMapper productModelMapper; @Override public IPage<StockInventoryDto> pagestockInventory(Page page, StockInventoryDto stockInventoryDto) { @@ -64,8 +70,57 @@ stockInRecordDto.setRecordId(stockInventoryDto.getRecordId()); stockInRecordDto.setRecordType(stockInventoryDto.getRecordType()); stockInRecordDto.setWeighingOperator(stockInventoryDto.getWeighingOperator()); stockInRecordDto.setProductModelId(stockInventoryDto.getProductModelId()); stockInRecordDto.setProductId(stockInventoryDto.getProductId()); if (stockInventoryDto.getProductId() != null) { stockInRecordDto.setProductId(stockInventoryDto.getProductId()); stockInRecordDto.setProductModelId(stockInventoryDto.getProductModelId()); }else { Product parent = productMapper.selectOne(new LambdaQueryWrapper<Product>().eq(Product::getProductName, "原材料").last("limit 1")); if (parent == null) { throw new RuntimeException("原材料分类不存在"); } Product product; Product existingProduct = productMapper.selectOne( new LambdaQueryWrapper<Product>() .eq(Product::getParentId, parent.getId()) .eq(Product::getProductName, stockInventoryDto.getProductName()) ); if (existingProduct != null) { // 已存在 product = existingProduct; } else { // 不存在 Product newProduct = new Product(); newProduct.setParentId(parent.getId()); newProduct.setProductName(stockInventoryDto.getProductName()); productMapper.insert(newProduct); product = newProduct; } // 先查询是否已存在相同的产品型号 ProductModel existingModel = productModelMapper.selectOne( new LambdaQueryWrapper<ProductModel>() .eq(ProductModel::getProductId, product.getId()) .eq(ProductModel::getUnit, stockInventoryDto.getUnit()) .eq(ProductModel::getModel, stockInventoryDto.getModel()) ); Long productModelId; if (existingModel != null) { // 已存在 productModelId = existingModel.getId(); } else { // 不存在 ProductModel productModel = new ProductModel(); productModel.setProductId(product.getId()); productModel.setUnit(stockInventoryDto.getUnit()); productModel.setModel(stockInventoryDto.getModel()); productModelMapper.insert(productModel); productModelId = productModel.getId(); } stockInRecordDto.setProductId(product.getId()); stockInRecordDto.setProductModelId(productModelId); stockInventoryDto.setProductModelId(productModelId); } stockInRecordDto.setRemark(stockInventoryDto.getRemark()); stockInRecordDto.setType("0"); @@ -97,7 +152,7 @@ // 新增库存 StockInventory newStockInventory = new StockInventory(); newStockInventory.setProductModelId(stockInventoryDto.getProductModelId()); newStockInventory.setQualitity(stockInventoryDto.getProductType() == 0 ? newStockInventory.setQualitity(stockInventoryDto.getProductType() != null && stockInventoryDto.getProductType() == 0 ? stockInventoryDto.getNetWeight() : stockInventoryDto.getQualitity()); newStockInventory.setVersion(1); newStockInventory.setRemark(stockInventoryDto.getRemark()); @@ -158,57 +213,36 @@ @Override public R importStockInventory(MultipartFile file) { try { // 查询所有的产品 List<SalesLedgerProduct> salesLedgerProducts = salesLedgerProductMapper.selectProduct(); ExcelUtil<StockInventoryExportData> util = new ExcelUtil<StockInventoryExportData>(StockInventoryExportData.class); List<StockInventoryExportData> list = util.importExcel(file.getInputStream()); // 记录未找到匹配项的数据 List<String> unmatchedRecords = new ArrayList<>(); for (StockInventoryExportData dto : list) { // 验证冻结数量 if (ObjectUtils.isNotEmpty(dto.getLockedQuantity()) && dto.getLockedQuantity().compareTo(dto.getQualitity()) > 0) { throw new RuntimeException(String.format("产品[%s %s]冻结数量不能超过库存数量", dto.getProductName(), dto.getModel())); } list.forEach(dto -> { boolean matched = false; for (SalesLedgerProduct item : salesLedgerProducts) { if (item.getProductCategory().equals(dto.getProductName()) && item.getSpecificationModel().equals(dto.getModel())) { StockInventoryDto stockInventoryDto = new StockInventoryDto(); stockInventoryDto.setRecordId(0L); stockInventoryDto.setRecordType(StockInQualifiedRecordTypeEnum.CUSTOMIZATION_STOCK_IN.getCode()); stockInventoryDto.setQualitity(dto.getQualitity()); stockInventoryDto.setRemark(dto.getRemark()); stockInventoryDto.setWarnNum(dto.getWarnNum()); if (ObjectUtils.isNotEmpty(dto.getLockedQuantity()) && dto.getLockedQuantity().compareTo(dto.getQualitity()) > 0) { throw new RuntimeException("冻结数量不能超过本次导入的库存数量"); } stockInventoryDto.setLockedQuantity(dto.getLockedQuantity()); stockInventoryDto.setProductModelId(item.getProductModelId()); this.addstockInventory(stockInventoryDto); matched = true; break; // 找到匹配项后跳出循环 } } if (!matched) { // 记录未匹配的数据 String unmatchedInfo = String.format("产品名称：%s，规格型号：%s", dto.getProductName(), dto.getModel()); unmatchedRecords.add(unmatchedInfo); } }); // 构建返回信息 StringBuilder message = new StringBuilder(); if (!unmatchedRecords.isEmpty()) { message.append("以下产品未找到匹配项：\n"); for (String record : unmatchedRecords) { message.append(record).append("\n"); } throw new RuntimeException(message.toString()); StockInventoryDto stockInventoryDto = new StockInventoryDto(); stockInventoryDto.setRecordId(0L); stockInventoryDto.setRecordType(StockInQualifiedRecordTypeEnum.CUSTOMIZATION_STOCK_IN.getCode()); stockInventoryDto.setQualitity(dto.getQualitity()); stockInventoryDto.setRemark(dto.getRemark()); stockInventoryDto.setNetWeight(dto.getNetWeight()); stockInventoryDto.setLockedQuantity(dto.getLockedQuantity()); stockInventoryDto.setProductName(dto.getProductName()); stockInventoryDto.setModel(dto.getModel()); stockInventoryDto.setUnit(dto.getUnit()); // 解决方案1：通过代理对象调用 ((StockInventoryService) AopContext.currentProxy()).addstockInventory(stockInventoryDto); } return R.ok("导入成功"); } catch (Exception e) { e.printStackTrace(); return R.fail("导入失败：" + e.getMessage()); } return R.ok("导入成功"); }
@@ -75,369 +75,277 @@ </delete> <select id="getInspectStatistics" resultType="com.ruoyi.quality.dto.QualityInspectStatDto"> SELECT CASE pp.product_name WHEN '原材料' THEN 0 WHEN '半成品' THEN 1 WHEN '成品' THEN 2 END AS modelType, IFNULL(SUM(qi.quantity), 0) AS totalCount, IFNULL(SUM(CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END), 0) AS completedCount FROM product p INNER JOIN product pp ON p.parent_id = pp.id LEFT JOIN product_model pm ON pm.product_id = p.id LEFT JOIN quality_inspect qi ON qi.product_model_id = pm.id WHERE pp.product_name IN ('原材料', '半成品', '成品') GROUP BY pp.product_name SELECT rm.check_type AS modelType, COUNT(*) AS totalCount, SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) AS completedCount, SUM(CASE WHEN rm.inspect_state = 0 THEN 1 ELSE 0 END) AS uncommittedCount FROM raw_material rm WHERE rm.inspect_state IN (0, 1) GROUP BY rm.check_type ORDER BY rm.check_type; </select> <select id="getPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityPassRateDto"> SELECT t.modelType, COALESCE(SUM(qi.quantity), 0) AS totalCount, COALESCE(SUM( CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END ), 0) AS completedCount, COALESCE(SUM( CASE WHEN qi.inspect_state = 1 AND qi.check_result = '合格' THEN qi.quantity ELSE 0 END ), 0) AS qualifiedCount, COALESCE(SUM( CASE WHEN qi.inspect_state = 1 AND qi.check_result = '不合格' THEN qi.quantity ELSE 0 END ), 0) AS unqualifiedCount, /* 完成率 */ IF(COALESCE(SUM(qi.quantity), 0) = 0, 0, ROUND( COALESCE(SUM( CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END ), 0) / SUM(qi.quantity) * 100, 2) ) AS completionRate, /* 合格率 */ IF(COALESCE(SUM( CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END ), 0) = 0, 0, ROUND( COALESCE(SUM( CASE WHEN qi.inspect_state = 1 AND qi.check_result = '合格' THEN qi.quantity ELSE 0 END ), 0) / COALESCE(SUM( CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END ), 0) * 100, 2) ) AS passRate FROM (SELECT 0 AS modelType UNION ALL SELECT 1 UNION ALL SELECT 2) t LEFT JOIN product p ON 1 = 1 LEFT JOIN product pp ON p.parent_id = pp.id LEFT JOIN product_model pm ON pm.product_id = p.id LEFT JOIN quality_inspect qi ON qi.product_model_id = pm.id AND ( (pp.product_name = '原材料' AND t.modelType = 0) OR (pp.product_name = '半成品' AND t.modelType = 1) OR (pp.product_name = '成品' AND t.modelType = 2) ) GROUP BY t.modelType SELECT t.modelType, COALESCE(rm.totalCount, 0) AS totalCount, COALESCE(rm.completedCount, 0) AS completedCount, COALESCE(rm.qualifiedCount, 0) AS qualifiedCount, COALESCE(rm.unqualifiedCount, 0) AS unqualifiedCount, COALESCE(rm.uncommittedCount, 0) AS uncommittedCount, COALESCE(rm.completionRate, 0) AS completionRate, COALESCE(rm.passRate, 0) AS passRate FROM ( SELECT 0 AS modelType UNION ALL SELECT 1 UNION ALL SELECT 2 ) t LEFT JOIN ( SELECT rm.check_type AS modelType, COUNT(*) AS totalCount, SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) AS completedCount, SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 1 THEN 1 ELSE 0 END) AS qualifiedCount, SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 0 THEN 1 ELSE 0 END) AS unqualifiedCount, SUM(CASE WHEN rm.inspect_state = 0 THEN 1 ELSE 0 END) AS uncommittedCount, IF(COUNT(*) = 0, 0, ROUND(SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) ) AS completionRate, IF(SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) = 0, 0, ROUND(SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 1 THEN 1 ELSE 0 END) / SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) * 100, 2) ) AS passRate FROM raw_material rm GROUP BY rm.check_type ) rm ON t.modelType = rm.modelType ORDER BY t.modelType; </select> <select id="getMonthlyPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityMonthlyPassRateDto"> WITH RECURSIVE months AS (SELECT 1 AS month_num UNION ALL SELECT month_num + 1 FROM months WHERE month_num < 12), types AS (SELECT 0 AS modelType UNION ALL SELECT 1 UNION ALL SELECT 2), base AS (SELECT m.month_num, t.modelType FROM months m CROSS JOIN types t) SELECT CASE b.month_num WHEN 1 THEN '一月' WHEN 2 THEN '二月' WHEN 3 THEN '三月' WHEN 4 THEN '四月' WHEN 5 THEN '五月' WHEN 6 THEN '六月' WHEN 7 THEN '七月' WHEN 8 THEN '八月' WHEN 9 THEN '九月' WHEN 10 THEN '十月' WHEN 11 THEN '十一月' WHEN 12 THEN '十二月' END AS month, b.modelType, /* 总数量 */ COALESCE(SUM(qi.quantity), 0) AS totalCount, /* 已完成 */ COALESCE(SUM( CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END ), 0) AS completedCount, /* 合格 */ COALESCE(SUM( CASE WHEN qi.inspect_state = 1 AND qi.check_result = '合格' THEN qi.quantity ELSE 0 END ), 0) AS qualifiedCount, /* 不合格 */ COALESCE(SUM( CASE WHEN qi.inspect_state = 1 AND qi.check_result = '不合格' THEN qi.quantity ELSE 0 END ), 0) AS unqualifiedCount, /* 完成率 */ IF(COALESCE(SUM(qi.quantity), 0) = 0, 0, ROUND( COALESCE(SUM( CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END ), 0) / SUM(qi.quantity) * 100, 2 ) ) AS completionRate, /* 合格率 */ IF(COALESCE(SUM( CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END ), 0) = 0, 0, ROUND( COALESCE(SUM( CASE WHEN qi.inspect_state = 1 AND qi.check_result = '合格' THEN qi.quantity ELSE 0 END ), 0) / COALESCE(SUM( CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END ), 0) * 100, 2 ) ) AS passRate months AS ( SELECT 1 AS month_num UNION ALL SELECT month_num + 1 FROM months WHERE month_num < 12 ), types AS ( SELECT 0 AS modelType UNION ALL SELECT 1 UNION ALL SELECT 2 ), base AS ( SELECT m.month_num, t.modelType FROM months m CROSS JOIN types t ) SELECT CASE b.month_num WHEN 1 THEN '一月' WHEN 2 THEN '二月' WHEN 3 THEN '三月' WHEN 4 THEN '四月' WHEN 5 THEN '五月' WHEN 6 THEN '六月' WHEN 7 THEN '七月' WHEN 8 THEN '八月' WHEN 9 THEN '九月' WHEN 10 THEN '十月' WHEN 11 THEN '十一月' WHEN 12 THEN '十二月' END AS month, b.modelType, COALESCE(rm.totalCount, 0) AS totalCount, COALESCE(rm.completedCount, 0) AS completedCount, COALESCE(rm.qualifiedCount, 0) AS qualifiedCount, COALESCE(rm.unqualifiedCount, 0) AS unqualifiedCount, COALESCE(rm.uncommittedCount, 0) AS uncommittedCount, COALESCE(rm.completionRate, 0) AS completionRate, COALESCE(rm.passRate, 0) AS passRate FROM base b LEFT JOIN product p ON 1 = 1 LEFT JOIN product pp ON p.parent_id = pp.id LEFT JOIN product_model pm ON pm.product_id = p.id LEFT JOIN quality_inspect qi ON qi.product_model_id = pm.id AND YEAR(qi.check_time) = #{year} AND MONTH(qi.check_time) = b.month_num AND ( (pp.product_name = '原材料' AND b.modelType = 0) OR (pp.product_name = '半成品' AND b.modelType = 1) OR (pp.product_name = '成品' AND b.modelType = 2) ) GROUP BY b.month_num, b.modelType LEFT JOIN ( SELECT MONTH(rm.create_time) AS month_num, rm.check_type AS modelType, COUNT(*) AS totalCount, SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) AS completedCount, SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 1 THEN 1 ELSE 0 END) AS qualifiedCount, SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 0 THEN 1 ELSE 0 END) AS unqualifiedCount, SUM(CASE WHEN rm.inspect_state = 0 THEN 1 ELSE 0 END) AS uncommittedCount, IF(COUNT(*) = 0, 0, ROUND(SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) ) AS completionRate, IF(SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) = 0, 0, ROUND(SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 1 THEN 1 ELSE 0 END) / SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) * 100, 2) ) AS passRate FROM raw_material rm WHERE YEAR(rm.create_time) = #{year} -- 按年份筛选 GROUP BY MONTH(rm.create_time), rm.check_type ) rm ON b.month_num = rm.month_num AND b.modelType = rm.modelType ORDER BY b.month_num, b.modelType; </select> <select id="getYearlyPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityPassRateDto"> SELECT t.modelType, COALESCE(SUM( CASE WHEN pp.product_name = '原材料' AND t.modelType = 0 THEN qi.quantity WHEN pp.product_name = '半成品' AND t.modelType = 1 THEN qi.quantity WHEN pp.product_name = '成品' AND t.modelType = 2 THEN qi.quantity ELSE 0 END ), 0) AS totalCount FROM (SELECT 0 AS modelType UNION ALL SELECT 1 UNION ALL SELECT 2) t LEFT JOIN product p ON 1 = 1 LEFT JOIN product pp ON p.parent_id = pp.id LEFT JOIN product_model pm ON pm.product_id = p.id LEFT JOIN quality_inspect qi ON qi.product_model_id = pm.id AND YEAR(qi.check_time) = #{year} AND qi.inspect_state = 1 AND qi.check_result = '合格' GROUP BY t.modelType SELECT t.modelType, COALESCE(rm.totalCount, 0) AS totalCount, COALESCE(rm.completedCount, 0) AS completedCount, COALESCE(rm.uncommittedCount, 0) AS uncommittedCount, COALESCE(rm.qualifiedCount, 0) AS qualifiedCount, COALESCE(rm.unqualifiedCount, 0) AS unqualifiedCount, COALESCE(rm.completionRate, 0) AS completionRate, COALESCE(rm.passRate, 0) AS passRate FROM ( SELECT 0 AS modelType UNION ALL SELECT 1 UNION ALL SELECT 2 ) t LEFT JOIN ( SELECT rm.check_type AS modelType, COUNT(*) AS totalCount, SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) AS completedCount, SUM(CASE WHEN rm.inspect_state = 0 THEN 1 ELSE 0 END) AS uncommittedCount, SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 1 THEN 1 ELSE 0 END) AS qualifiedCount, SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 0 THEN 1 ELSE 0 END) AS unqualifiedCount, -- 完成率 = 已提交数量 / 总数量 * 100 IF(COUNT(*) = 0, 0, ROUND(SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) ) AS completionRate, -- 合格率 = 合格数量 / 已提交数量 * 100 IF(SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) = 0, 0, ROUND(SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 1 THEN 1 ELSE 0 END) / SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) * 100, 2) ) AS passRate FROM raw_material rm WHERE rm.inspect_state IN (0, 1) AND YEAR(rm.create_time) = #{year} -- 按年份筛选，假设有时间字段 GROUP BY rm.check_type ) rm ON t.modelType = rm.modelType ORDER BY t.modelType; </select> <select id="getMonthlyCompletionDetails" resultType="com.ruoyi.quality.dto.QualityMonthlyDetailDto"> WITH RECURSIVE months AS (SELECT 1 AS month_num UNION ALL SELECT month_num + 1 FROM months WHERE month_num < 12) SELECT CASE m.month_num WHEN 1 THEN '一月' WHEN 2 THEN '二月' WHEN 3 THEN '三月' WHEN 4 THEN '四月' WHEN 5 THEN '五月' WHEN 6 THEN '六月' WHEN 7 THEN '七月' WHEN 8 THEN '八月' WHEN 9 THEN '九月' WHEN 10 THEN '十月' WHEN 11 THEN '十一月' WHEN 12 THEN '十二月' END AS month, /* 原材料 */ COALESCE(SUM( CASE WHEN pp.product_name = '原材料' THEN qi.quantity ELSE 0 END ), 0) AS rawMaterialCount, /* 半成品 */ COALESCE(SUM( CASE WHEN pp.product_name = '半成品' THEN qi.quantity ELSE 0 END ), 0) AS processCount, /* 成品 */ COALESCE(SUM( CASE WHEN pp.product_name = '成品' THEN qi.quantity ELSE 0 END ), 0) AS outgoingCount WITH RECURSIVE months AS ( SELECT 1 AS month_num UNION ALL SELECT month_num + 1 FROM months WHERE month_num < 12 ) SELECT CASE m.month_num WHEN 1 THEN '一月' WHEN 2 THEN '二月' WHEN 3 THEN '三月' WHEN 4 THEN '四月' WHEN 5 THEN '五月' WHEN 6 THEN '六月' WHEN 7 THEN '七月' WHEN 8 THEN '八月' WHEN 9 THEN '九月' WHEN 10 THEN '十月' WHEN 11 THEN '十一月' WHEN 12 THEN '十二月' END AS month, /* 入场检 (check_type = 0) */ COALESCE(SUM( CASE WHEN rm.check_type = 0 THEN 1 -- 统计记录数，因为原SQL用的是quantity字段 ELSE 0 END ), 0) AS rawMaterialCount, /* 车间检 (check_type = 1) */ COALESCE(SUM( CASE WHEN rm.check_type = 1 THEN 1 ELSE 0 END ), 0) AS processCount, /* 出厂检 (check_type = 2) */ COALESCE(SUM( CASE WHEN rm.check_type = 2 THEN 1 ELSE 0 END ), 0) AS outgoingCount FROM months m LEFT JOIN product p ON 1 = 1 LEFT JOIN product pp ON p.parent_id = pp.id LEFT JOIN product_model pm ON pm.product_id = p.id LEFT JOIN quality_inspect qi ON qi.product_model_id = pm.id AND qi.inspect_state = 1 AND YEAR(qi.check_time) = #{year} AND MONTH(qi.check_time) = m.month_num LEFT JOIN raw_material rm ON YEAR(rm.create_time) = #{year} AND MONTH(rm.create_time) = m.month_num AND rm.inspect_state = 1 -- 已提交 AND rm.check_result = 1 -- 合格 GROUP BY m.month_num ORDER BY m.month_num; </select> <select id="getTopParameters" resultType="com.ruoyi.quality.dto.QualityParameterStatDto"> WITH parameter_counts AS (SELECT qip.parameter_item AS name, COUNT(*) AS count FROM quality_inspect_param qip JOIN quality_inspect qi ON qip.inspect_id = qi.id JOIN product p ON qi.product_id = p.id JOIN product pp ON p.parent_id = pp.id WHERE qi.inspect_state = 1 AND ( (#{modelType} = 1 AND pp.product_name = '原材料') OR (#{modelType} = 2 AND pp.product_name = '半成品') OR (#{modelType} = 3 AND pp.product_name = '成品') ) GROUP BY qip.parameter_item), ranked AS (SELECT name, count, ROW_NUMBER() OVER (ORDER BY count DESC) AS rn FROM parameter_counts), total AS (SELECT SUM(count) AS total_count FROM parameter_counts) WITH parameter_counts AS ( SELECT qii.name, COUNT(*) AS count FROM raw_material rm LEFT JOIN raw_material_quality_inspect_item rmqi ON rmqi.raw_material_id = rm.id LEFT JOIN quality_inspect_item qii ON qii.id = rmqi.quality_inspect_item_id WHERE qii.name IS NOT NULL AND rm.check_type = CASE WHEN #{modelType} = 1 THEN 0 -- 前端1 → 数据库0 WHEN #{modelType} = 2 THEN 1 -- 前端2 → 数据库1 WHEN #{modelType} = 3 THEN 2 -- 前端3 → 数据库2 END GROUP BY qii.name ), ranked AS ( SELECT name, count, ROW_NUMBER() OVER (ORDER BY count DESC) AS rn FROM parameter_counts ), total AS ( SELECT SUM(count) AS total_count FROM parameter_counts ) SELECT name, count, CASE WHEN (SELECT total_count FROM total) = 0 THEN 0 ELSE ROUND(count / (SELECT total_count FROM total) * 100, 2) END AS percentage FROM (SELECT name, count, rn FROM ranked WHERE rn <= 4 UNION ALL SELECT '其他检测' AS name, SUM(count) AS count, 5 AS rn FROM ranked WHERE rn > 4 HAVING SUM(count) > 0) t SELECT name, count, CASE WHEN (SELECT total_count FROM total) = 0 THEN 0 ELSE ROUND(count / (SELECT total_count FROM total) * 100, 2) END AS percentage FROM ( SELECT name, count, rn FROM ranked WHERE rn <= 4 UNION ALL -- 其他合并 SELECT '其他检测' AS name, SUM(count) AS count, 5 AS rn FROM ranked WHERE rn > 4 HAVING SUM(count) > 0 ) t ORDER BY rn; </select>
@@ -16,6 +16,8 @@ qu.deal_result, qu.deal_name, qu.deal_time, qu.batch_no, qu.check_type, CASE WHEN qu.model = pm.id THEN pm.model ELSE qu.model
