src/main/java/com/ruoyi/consumables/controller/ConsumablesInventoryController.java
@@ -82,13 +82,13 @@ consumablesInventoryService.exportConsumablesInventory(response, consumablesInventoryDto); } @GetMapping("ConsumablesInventoryPage") @GetMapping("/ConsumablesInventoryPage") @ApiOperation("库存报表查询") public R ConsumablesInventoryPage(Page page, ConsumablesInventoryDto consumablesInventoryDto) { return R.ok(consumablesInventoryService.consumablesInventoryPage(consumablesInventoryDto,page)); } @GetMapping("ConsumablesInAndOutRecord") @GetMapping("/ConsumablesInAndOutRecord") @ApiOperation("统计各个产品的入库和出库记录") public R ConsumablesInAndOutRecord(ConsumablesInventoryDto consumablesInventoryDto, Page page) { return R.ok(consumablesInventoryService.consumablesInAndOutRecord(consumablesInventoryDto,page)); src/main/java/com/ruoyi/consumables/dto/ConsumablesInRecordDto.java
@@ -31,7 +31,7 @@ private String createBy; //现存量 private String currentconsumables; private String currentStock; //现净重 private BigDecimal currentWeight; src/main/java/com/ruoyi/consumables/dto/ConsumablesInventoryDto.java
@@ -36,9 +36,9 @@ @JsonFormat(pattern = "yyyy-MM-dd") private LocalDate endMonth; private BigDecimal totalconsumablesIn; private BigDecimal totalconsumablesOut; private BigDecimal currentconsumables; private BigDecimal totalStockIn; private BigDecimal totalStockOut; private BigDecimal currentStock; private BigDecimal unLockedQuantity;
@@ -36,8 +36,8 @@ public class ConsumablesUnInventoryServiceImpl extends ServiceImpl<ConsumablesUnInventoryMapper, ConsumablesUnInventory> implements ConsumablesUnInventoryService { private ConsumablesUnInventoryMapper consumablesUnInventoryMapper; private ConsumablesOutRecordService ConsumablesOutRecordService; private ConsumablesInRecordService ConsumablesInRecordService; private ConsumablesOutRecordService consumablesOutRecordService; private ConsumablesInRecordService consumablesInRecordService; @Override public IPage<ConsumablesUnInventoryDto> pageConsumablesUnInventory(Page page, ConsumablesUnInventoryDto consumablesUnInventoryDto) { @@ -54,7 +54,7 @@ consumablesInRecordDto.setStockInNum(consumablesUnInventoryDto.getQualitity()); consumablesInRecordDto.setProductModelId(consumablesUnInventoryDto.getProductModelId()); consumablesInRecordDto.setType("1"); ConsumablesInRecordService.add(consumablesInRecordDto); consumablesInRecordService.add(consumablesInRecordDto); //再进行新增库存数量库存 //先查询库存表中的产品是否存在，不存在新增，存在更新 ConsumablesUnInventory oldConsumablesUnInventory = consumablesUnInventoryMapper.selectOne(new QueryWrapper<ConsumablesUnInventory>().lambda().eq(ConsumablesUnInventory::getProductModelId, consumablesUnInventoryDto.getProductModelId())); @@ -82,7 +82,7 @@ consumablesOutRecordDto.setStockOutNum(consumablesUnInventoryDto.getQualitity()); consumablesOutRecordDto.setProductModelId(consumablesUnInventoryDto.getProductModelId()); consumablesOutRecordDto.setType("1"); ConsumablesOutRecordService.add(consumablesOutRecordDto); consumablesOutRecordService.add(consumablesOutRecordDto); ConsumablesUnInventory oldConsumablesInventory = consumablesUnInventoryMapper.selectOne(new QueryWrapper<ConsumablesUnInventory>().lambda().eq(ConsumablesUnInventory::getProductModelId, consumablesUnInventoryDto.getProductModelId())); if (ObjectUtils.isEmpty(oldConsumablesInventory)) { throw new RuntimeException("产品库存不存在"); src/main/java/com/ruoyi/stock/dto/StockInventoryDto.java
@@ -1,6 +1,5 @@ package com.ruoyi.stock.dto; import com.baomidou.mybatisplus.annotation.TableField; import com.fasterxml.jackson.annotation.JsonFormat; import com.ruoyi.stock.pojo.StockInventory; import io.swagger.annotations.ApiModelProperty; @@ -71,6 +70,8 @@ private String weighbridgeDocPath; @ApiModelProperty("产品类型") @TableField(exist = false) private Integer productType; @ApiModelProperty("现净重(吨)") private BigDecimal currentWeight; } src/main/resources/mapper/consumables/ConsumablesInventoryMapper.xml
@@ -85,7 +85,7 @@ select product_model_id, sum(net_weight) as total_net_weight from Consumables_in_record from consumables_in_record group by product_model_id ) sir on si.product_model_id = sir.product_model_id -- 出库净重 @@ -93,7 +93,7 @@ select product_model_id, sum(net_weight) as total_net_weight from Consumables_out_record from consumables_out_record group by product_model_id ) sor on si.product_model_id = sor.product_model_id @@ -126,7 +126,7 @@ select product_model_id, sum(net_weight) as total_net_weight from Consumables_in_record from consumables_in_record group by product_model_id ) sir on si.product_model_id = sir.product_model_id -- 出库净重 @@ -134,7 +134,7 @@ select product_model_id, sum(net_weight) as total_net_weight from Consumables_out_record from consumables_out_record group by product_model_id ) sor on si.product_model_id = sor.product_model_id where 1 = 1 @@ -146,7 +146,7 @@ <select id="consumablesInventoryPage" resultType="com.ruoyi.consumables.dto.ConsumablesInRecordDto"> select sir.*, si.qualitity as current_consumables, si.qualitity as current_stock, pm.model, pm.unit, p.product_name, @@ -155,7 +155,7 @@ -- 当前净重 = 入库净重 - 出库净重 IFNULL(inWeight.total_in_weight,0) - IFNULL(outWeight.total_out_weight,0) as current_weight from Consumables_in_record sir from consumables_in_record sir left join consumables_inventory si on sir.product_model_id = si.product_model_id @@ -174,7 +174,7 @@ select product_model_id, sum(net_weight) as total_in_weight from Consumables_in_record from consumables_in_record group by product_model_id ) inWeight on sir.product_model_id = inWeight.product_model_id @@ -184,7 +184,7 @@ select product_model_id, sum(net_weight) as total_out_weight from Consumables_out_record from consumables_out_record group by product_model_id ) outWeight on sir.product_model_id = outWeight.product_model_id @@ -208,9 +208,9 @@ pm.model, pm.unit, p.product_name, MAX(current_inventory) as current_consumables, SUM(CASE WHEN record_type = 'in' THEN amount ELSE 0 END) as total_Consumables_in, SUM(CASE WHEN record_type = 'out' THEN amount ELSE 0 END) as total_Consumables_out MAX(current_inventory) as current_stock, SUM(CASE WHEN record_type = 'in' THEN amount ELSE 0 END) as total_stock_in, SUM(CASE WHEN record_type = 'out' THEN amount ELSE 0 END) as total_stock_out FROM ( SELECT product_model_id, @@ -225,16 +225,16 @@ SELECT product_model_id, 0 as current_inventory, SUM(Consumables_in_num) as amount, SUM(stock_in_num) as amount, 'in' as record_type FROM Consumables_in_record FROM consumables_in_record <where> type = 0 <if test="ew.startMonth != null"> and Consumables_in_record.create_time >= #{ew.startMonth} and consumables_in_record.create_time >= #{ew.startMonth} </if> <if test="ew.endMonth != null"> and Consumables_in_record.create_time <= #{ew.endMonth} and consumables_in_record.create_time <= #{ew.endMonth} </if> </where> GROUP BY product_model_id @@ -244,16 +244,16 @@ SELECT product_model_id, 0 as current_inventory, SUM(Consumables_out_num) as amount, SUM(stock_out_num) as amount, 'out' as record_type FROM Consumables_out_record FROM consumables_out_record <where> type = 0 <if test="ew.startMonth != null"> and Consumables_out_record.create_time >= #{ew.startMonth} and consumables_out_record.create_time >= #{ew.startMonth} </if> <if test="ew.endMonth != null"> and Consumables_out_record.create_time <= #{ew.endMonth} and consumables_out_record.create_time <= #{ew.endMonth} </if> </where> GROUP BY product_model_id @@ -300,7 +300,7 @@ <select id="selectDailyConsumablesInCounts" resultType="java.util.Map"> SELECT DATE(sir.create_time) AS date, SUM(sir.Consumables_in_num) AS count SUM(sir.stock_in_num) AS count FROM consumables_in_record sir JOIN product_model pm ON sir.product_model_id = pm.id JOIN product p ON pm.product_id = p.id @@ -313,7 +313,7 @@ <select id="selectDailyConsumablesOutCounts" resultType="java.util.Map"> SELECT DATE(sor.create_time) AS date, SUM(sor.Consumables_out_num) AS count SUM(sor.stock_out_num) AS count FROM consumables_out_record sor JOIN product_model pm ON sor.product_model_id = pm.id JOIN product p ON pm.product_id = p.id src/main/resources/mapper/stock/StockInventoryMapper.xml
@@ -103,7 +103,7 @@ <select id="listStockInventoryExportData" resultType="com.ruoyi.stock.execl.StockInventoryExportData"> select si.qualitity, -- 当前净重 = 入库净重 - 出库净重 (COALESCE(sir.total_net_weight, 0) - COALESCE(sor.total_net_weight, 0)) AS net_weight,- (COALESCE(sir.total_net_weight, 0) - COALESCE(sor.total_net_weight, 0)) AS net_weight, pm.model, pm.unit, p.product_name, @@ -197,7 +197,8 @@ p.product_name, MAX(current_inventory) as current_stock, SUM(CASE WHEN record_type = 'in' THEN amount ELSE 0 END) as total_stock_in, SUM(CASE WHEN record_type = 'out' THEN amount ELSE 0 END) as total_stock_out SUM(CASE WHEN record_type = 'out' THEN amount ELSE 0 END) as total_stock_out, (COALESCE(in_weight.total_net_weight, 0) - COALESCE(out_weight.total_net_weight, 0)) AS current_weight FROM ( SELECT product_model_id, @@ -245,6 +246,42 @@ </where> GROUP BY product_model_id ) combined_data -- 左连接入库净重合计 LEFT JOIN ( SELECT product_model_id, SUM(net_weight) AS total_net_weight FROM stock_in_record <where> type = 0 <if test="ew.startMonth != null"> and create_time >= #{ew.startMonth} </if> <if test="ew.endMonth != null"> and create_time <= #{ew.endMonth} </if> </where> GROUP BY product_model_id ) in_weight ON combined_data.product_model_id = in_weight.product_model_id -- 左连接出库净重合计 LEFT JOIN ( SELECT product_model_id, SUM(net_weight) AS total_net_weight FROM stock_out_record <where> type = 0 <if test="ew.startMonth != null"> and create_time >= #{ew.startMonth} </if> <if test="ew.endMonth != null"> and create_time <= #{ew.endMonth} </if> </where> GROUP BY product_model_id ) out_weight ON combined_data.product_model_id = out_weight.product_model_id LEFT JOIN product_model pm ON pm.id = combined_data.product_model_id LEFT JOIN product p ON p.id = pm.product_id <where> @@ -258,7 +295,9 @@ GROUP BY pm.model, pm.unit, p.product_name p.product_name, in_weight.total_net_weight, out_weight.total_net_weight </select> <select id="selectTotal" resultType="java.math.BigDecimal"> select ifnull(sum(qualitity), 0)
