一.详解
1.业务需求:
业务为 个人重大事项报告 和 单位重大事项报告 两种。业务类似于:发起办公流程-》部门领导审核-》报备给单位领导。流程发起时,两种流程时分开发起的,所以设计时设计了两张表来记录两种业务。流程发起、审核、报备,设计的是完全独立的。后来由于甲方的脑残需求,要求在单位领导查看报备报备信息时,两张业务合并在一个列表里,并且要按时间排序,要有分页。
2.解决思路:
自定义sql,利用union
将两张业务表的公共字段 合并成一个虚拟表
(
SELECT
gaform_lpt_30.file_id file_id,
gaform_lpt_30.unit unit,
gaform_lpt_30.`name` `name`,
gaform_lpt_30.gettime time
FROM
`gaform_lpt_30`
WHERE
allagree = 1
) UNION ALL
(
SELECT
gaform_lpt_29.file_id file_id,
gaform_lpt_29.report_unit unit,
gaform_lpt_29.`report_name` `name`,
gaform_lpt_29.gettime time
FROM
`gaform_lpt_29`
WHERE
allagree = 1
)
然后在上面union结果的外层套上排序
这里需要注意一下: 合并 union两边的数据列数(as的字段列名也要)必须一致,否则会合并失败
SELECT
*
FROM
(
(
SELECT
gaform_lpt_30.file_id file_id,
gaform_lpt_30.unit unit,
gaform_lpt_30.`name` `name`,
gaform_lpt_30.gettime time
FROM
`gaform_lpt_30`
WHERE
allagree = 1
) UNION ALL
(
SELECT
gaform_lpt_29.file_id file_id,
gaform_lpt_29.report_unit unit,
gaform_lpt_29.`report_name` `name`,
gaform_lpt_29.gettime time
FROM
`gaform_lpt_29`
WHERE
allagree = 1
)
) AS t #此处一定注意,union后务必派生声明虚拟表,不然会出现报错
ORDER BY
time DESC #time是派生的虚拟表的字段
这时 排序和两表合并就完成了。分页利用mybatis
框架,只要给dao层方法正常传IPage就可以了(本来怎么分页,这里就可以直接接分页对象了)
以下是java代码:
controller层
@ApiOperation("报备列表-29+30共用")
@PostMapping("selectBblist")
public ServiceResult selectBblist(@RequestParam Integer page,@RequestParam Integer pageCount) {
IPage<Lpt2930BaobeiVO> page2= gaformLpt29Service.selectBb(page,pageCount);
return success(page2);
}
service层
@Override
public IPage<Lpt2930BaobeiVO> selectBb(Integer page, Integer pageCount) {
return gaformLpt29Dao.selectBb( new Page<>(page, pageCount));
}
dao层
(只是把上边的sql拿到了这里)
@Select("SELECT\n" +
"\t*\n" +
"FROM\n" +
"\t(\n" +
"\t\t( SELECT gaform_lpt_30.file_id file_id, gaform_lpt_30.unit unit, gaform_lpt_30.`name` `name`, gaform_lpt_30.gettime time ,gaform_lpt_30.event_id event_id FROM `gaform_lpt_30` WHERE allagree = 1 ) \n" +
"\t\tUNION ALL\n" +
"\t\t( SELECT gaform_lpt_29.file_id file_id, gaform_lpt_29.report_unit unit, gaform_lpt_29.`report_name` `name`, gaform_lpt_29.gettime time,gaform_lpt_29.event_id event_id FROM `gaform_lpt_29` WHERE allagree = 1 ) \n" +
"\n" +
") as t ORDER BY time DESC")
IPage<Lpt2930BaobeiVO> selectBb(IPage page);//此处传入page 插件会自动实现分页
接收数据的vo
@Data
public class Lpt2930BaobeiVO {
public String fileId;
public String unit;
public String name;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
public Date time;
public String eventId;
}
原文链接:https://blog.youkuaiyun.com/qq_27037397/article/details/121329562
二.实例
这里我把整个类贴过来了,很长,不过很贴近实际
serviceImpl 类
package com.boot.reservation.impl.statistics;
import cn.hutool.core.bean.BeanUtil;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.ClientManagementEntity;
import com.boot.reservation.entity.domain.vo.StatisticsGranaryWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseDetailsWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseWebsiteVO;
import com.boot.reservation.entity.domain.vo.StorageInfoVO;
import com.boot.reservation.mapper.ClientManagementMapper;
import com.boot.reservation.mapper.PfuMapper;
import com.boot.reservation.mapper.StatisticsAppletMapper;
import com.boot.reservation.mapper.StatisticsWebsiteMapper;
import com.boot.reservation.service.project.ProjectManagerService;
import com.boot.reservation.service.statistics.StatisticsWebsiteService;
import com.iciyun.project.domain.security.PerFrontUserEntity;
import com.iciyun.ticket.user.authentication.UserSessionRedisUtil;
import com.iciyun.ticket.util.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import java.math.BigDecimal;
import java.util.LinkedList;
import java.util.List;
import java.util.stream.Collectors;
@Service
public class StatisticsWebsiteServiceImpl implements StatisticsWebsiteService {
protected Logger logger = LoggerFactory.getLogger(this.getClass());
@Autowired
private StatisticsWebsiteMapper statisticsWebsiteMapper;
@Autowired
private UserSessionRedisUtil userSessionRedisUtil;
@Value("${authentication.switch}")
private Boolean isSwitch;
@Autowired
private ProjectManagerService projectManagerService;
@Autowired
private StatisticsAppletMapper statisticsAppletMapper;
@Autowired
private ClientManagementMapper clientManagementMapper;
@Autowired
private PfuMapper pfuMapper;
@Override
public Page<StatisticsGranaryWebsiteVO> findGranaryByPage(StatisticsGranaryWebsiteVO query) {
List<String> storageCodeList = toDataPowerList();
if (null == storageCodeList || storageCodeList.size() == 0) {
Page<StatisticsGranaryWebsiteVO> voPage = new Page<>();
return voPage;
}
// 获取非资金方的库点
List<String> isFunderList = toIsFunderList("0");
Page<StatisticsGranaryWebsiteVO> granaryByPage = statisticsWebsiteMapper.findGranaryByPage(query,storageCodeList);
List<StatisticsGranaryWebsiteVO> records = granaryByPage.getRecords();
if (records != null && records.size() > 0) {
for (StatisticsGranaryWebsiteVO record : records) {
// 翻译货物名称
if (StringUtils.isNotBlank(record.getGoodsName())) {
record.setGoodsNameStr(statisticsWebsiteMapper.findGoodsNameStr(record.getGoodsName()));
}
Double alreadyOutStock = statisticsAppletMapper.findAlreadyOutStock(record.getGranaryAddress(), storageCodeList);
// 已开出库单数
record.setAlreadyOutStock(BigDecimal.valueOf(alreadyOutStock));
Double practicalOutStock = statisticsAppletMapper.findPracticalOutStock(record.getGranaryAddress(), "", storageCodeList);
// 剩余可出库数量
record.setResidueOutStockNum(BigDecimal.valueOf(alreadyOutStock).subtract(BigDecimal.valueOf(practicalOutStock)).doubleValue());
// 库存数量
record.setGrandTotalNum(BigDecimal.valueOf(record.getGrandTotalInStockNum()).subtract(BigDecimal.valueOf(record.getGrandTotalOutStockNum())).doubleValue());
// 质押中数量
record.setJustPledgeNum(BigDecimal.valueOf(record.getGrandTotalPledgeNum()).subtract(BigDecimal.valueOf(record.getGrandTotalRelieveNum())).doubleValue());
// 解除质押但未提货数量
if (isFunderList.contains(record.getGranaryAddress())) {
record.setNotLadingQualityNum(new Double(0));
} else {
record.setNotLadingQualityNum(BigDecimal.valueOf(record.getGrandTotalRelieveNum()).subtract(BigDecimal.valueOf(record.getGrandTotalOutStockNum())).doubleValue());
}
// 已入库未质押数量
record.setNotPledgeNum(BigDecimal.valueOf(record.getGrandTotalInStockNum()).subtract(BigDecimal.valueOf(record.getGrandTotalPledgeNum())).doubleValue());
//合作企业
if (StringUtil.isNotEmpty(record.getPartner())){
String partner = record.getPartner();
ClientManagementEntity clientManagementEntity = clientManagementMapper.selectById(partner);
if (BeanUtil.isNotEmpty(clientManagementEntity)){
record.setPartnerStr(clientManagementEntity.getEnterpriseName());
}
}
// 业务负责人
if (StringUtil.isNotEmpty(record.getBusinessLeader())){
String businessLeader = record.getBusinessLeader();
String name = pfuMapper.selectByPId(businessLeader);
record.setBusinessLeaderStr(name);
}
// 项目负责人
if (StringUtil.isNotEmpty(record.getProjectLeader())){
String projectLeader = record.getProjectLeader();
String name = pfuMapper.selectByPId(projectLeader);
record.setProjectLeaderStr(name);
}
if (StringUtil.isNotEmpty(record.getSupervisor())){
String supervisor = record.getSupervisor();
StringBuilder supervisorBuilder = new StringBuilder();
String[] splitStr = supervisor.split(",");
for (int i = 0; i < splitStr.length; i++) {
if (StringUtil.isNotEmpty(splitStr[i])){
String name = pfuMapper.selectByPId(splitStr[i]);
if ( i == splitStr.length - 1){
supervisorBuilder.append(name);
}else{
supervisorBuilder.append(name+",");
}
}
}
record.setSupervisorStr(supervisorBuilder.toString());
}
}
}
return granaryByPage;
}
@Override
public Page<StatisticsHouseWebsiteVO> findHouseByPage(StatisticsHouseWebsiteVO query) {
// 获取非资金方的库点
List<String> isFunderList = toIsFunderList("0");
Page<StatisticsHouseWebsiteVO> houseByPage = statisticsWebsiteMapper.findHouseByPage(query);
List<StatisticsHouseWebsiteVO> records = houseByPage.getRecords();
if (records != null && records.size() > 0) {
for (StatisticsHouseWebsiteVO record : records) {
// 翻译货物名称
if (StringUtils.isNotBlank(record.getGoodsName())) {
record.setGoodsNameStr(statisticsWebsiteMapper.findGoodsNameStr(record.getGoodsName()));
}
Double houseAlreadyOutStock = statisticsAppletMapper.findHouseAlreadyOutStock(record.getGranaryAddress(), record.getHouse(),null);
// 已开出库单数
record.setAlreadyOutStock(BigDecimal.valueOf(houseAlreadyOutStock));
Double practicalOutStock = statisticsAppletMapper.findPracticalOutStock(record.getGranaryAddress(), record.getHouse(),null);
// 剩余可出库数量
record.setResidueOutStockNum(BigDecimal.valueOf(houseAlreadyOutStock).subtract(BigDecimal.valueOf(practicalOutStock)).doubleValue());
// 库存数量
record.setGrandTotalNum(BigDecimal.valueOf(record.getGrandTotalInStockNum()).subtract(BigDecimal.valueOf(record.getGrandTotalOutStockNum())).doubleValue());
// 质押中数量
record.setJustPledgeNum(BigDecimal.valueOf(record.getGrandTotalPledgeNum()).subtract(BigDecimal.valueOf(record.getGrandTotalRelieveNum())).doubleValue());
// 解除质押但未提货数量
if (isFunderList.contains(record.getGranaryAddress())) {
record.setNotLadingQualityNum(new Double(0));
} else {
record.setNotLadingQualityNum(BigDecimal.valueOf(record.getGrandTotalRelieveNum()).subtract(BigDecimal.valueOf(record.getGrandTotalOutStockNum())).doubleValue());
}
// 已入库未质押数量
record.setNotPledgeNum(BigDecimal.valueOf(record.getGrandTotalInStockNum()).subtract(BigDecimal.valueOf(record.getGrandTotalPledgeNum())).doubleValue());
//合作企业
if (StringUtil.isNotEmpty(record.getPartner())){
String partner = record.getPartner();
ClientManagementEntity clientManagementEntity = clientManagementMapper.selectById(partner);
if (BeanUtil.isNotEmpty(clientManagementEntity)){
record.setPartnerStr(clientManagementEntity.getEnterpriseName());
}
}
// 业务负责人
if (StringUtil.isNotEmpty(record.getBusinessLeader())){
String businessLeader = record.getBusinessLeader();
String name = pfuMapper.selectByPId(businessLeader);
record.setBusinessLeaderStr(name);
}
// 项目负责人
if (StringUtil.isNotEmpty(record.getProjectLeader())){
String projectLeader = record.getProjectLeader();
String name = pfuMapper.selectByPId(projectLeader);
record.setProjectLeaderStr(name);
}
if (StringUtil.isNotEmpty(record.getSupervisor())){
String supervisor = record.getSupervisor();
StringBuilder supervisorBuilder = new StringBuilder();
String[] splitStr = supervisor.split(",");
for (int i = 0; i < splitStr.length; i++) {
if (StringUtil.isNotEmpty(splitStr[i])){
String name = pfuMapper.selectByPId(splitStr[i]);
if (i==splitStr.length){
supervisorBuilder.append(name);
}else{
supervisorBuilder.append(name+",");
}
}
}
record.setSupervisorStr(supervisorBuilder.toString());
}
}
}
return houseByPage;
}
@Override
public Page<StatisticsHouseDetailsWebsiteVO> findHouseDetailsByPage(StatisticsHouseDetailsWebsiteVO query) {
Page<StatisticsHouseDetailsWebsiteVO> houseDetailsByPage = statisticsWebsiteMapper.findHouseDetailsByPage(query);
List<StatisticsHouseDetailsWebsiteVO> records = houseDetailsByPage.getRecords();
if (records != null && records.size() > 0) {
for (StatisticsHouseDetailsWebsiteVO record : records) {
if ("入库".equalsIgnoreCase(record.getBusinessType())) {
record.setWeight(
BigDecimal.valueOf(record.getGrossWeight())
.subtract(BigDecimal.valueOf(record.getBareWeight()))
.subtract(BigDecimal.valueOf(record.getRealityReduceWeight()))
);
} else {
record.setWeight(
BigDecimal.valueOf(record.getGrossWeight())
.subtract(BigDecimal.valueOf(record.getBareWeight()))
);
}
}
}
return houseDetailsByPage;
}
private List<String> toDataPowerList(){
PerFrontUserEntity pu = userSessionRedisUtil.getSessionUserObjInfo();
List<StorageInfoVO> storages = new LinkedList<>();//库点列表
if (!isSwitch || pu == null) {
storages = projectManagerService.queryStoragesNoUser();
} else {
storages = projectManagerService.queryStoragesHasUser(String.valueOf(pu.getId()));
}
List<String> storageCodeList = storages.stream().map(StorageInfoVO::getStorageCode).collect(Collectors.toList());
return storageCodeList;
}
private List<String> toIsFunderList(String isFunder){
return statisticsWebsiteMapper.findGranaryByIsFunder(isFunder);
}
}
mapper:
package com.boot.reservation.mapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.vo.StatisticsGranaryWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseDetailsWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseWebsiteVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Mapper
@Repository
public interface StatisticsWebsiteMapper {
Page<StatisticsGranaryWebsiteVO> findGranaryByPage(@Param("query") StatisticsGranaryWebsiteVO query, List<String> storageCodeList);
Page<StatisticsHouseWebsiteVO> findHouseByPage(@Param("query") StatisticsHouseWebsiteVO query);
Page<StatisticsHouseDetailsWebsiteVO> findHouseDetailsByPage(@Param("query") StatisticsHouseDetailsWebsiteVO query);
List<String> findGranaryByIsFunder(@Param("isFunder") String isFunder);
String findGoodsNameStr(String goodsName);
}
Mapper.xml:
翻车事故(与本标题主干无关)
这里先提一下,这个场景下产生的一个翻车事故(与本标题主干无关),是数据统计伴生的一些问题,,这里记录一下,后来人避坑。
可以看到,这里在统计数据的时候,需要用到很多关联的表,当时写这个sql的时候大意了,直接使用了连接查询。因此便产生了数据查出来与实际数据相差甚远的事情。
举例说明:
由于左连接的特性,如果左边的表有多条编码相同的数据,然后右边的表只有一条数据与左边的表对应,这时候就会产生问题,右边表的一条数据会被拼接到左边每个编码相同的数据后边,就会造成数据重复统计的问题
。反过来也一样,右表编码不唯一,即便左边的表用了分组,但是如果右边的表有多条与连接条件匹配的数据,那左边的表也会复制多份,查出来的总数据条数就是,左表数据条乘右表符合条件的数量
,这样最右边的表会跟着匹配多份。总之就是,这种问题,使用连接查询的时候不可避免。
由此总结一下,连接查询不适合分组统计数据,查查匹配的编码唯一的数据还行(比如查字典项,已知有且仅有一条数据与查询数据对应),一旦数据不唯一,就会产生问题。就像上边的问题,库可以重复使用,所以左边或者右边的库的编码不可能只出现一次,就产生了问题。
举个栗子:
此时在这个基础上,无论对左表数据进行统计还是右表数据进行统计,都会有问题
解决办法就是用子查询
下边是正确示例
正确示例:
<?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.boot.reservation.mapper.StatisticsWebsiteMapper">
<select id="findGranaryByPage"
parameterType="com.boot.reservation.entity.domain.vo.StatisticsGranaryWebsiteVO"
resultType="com.boot.reservation.entity.domain.vo.StatisticsGranaryWebsiteVO">
select
lps.project_id as project,
lp.project_name as projectName,
lps.storage_code as granaryAddress,
lps.storage_name as granaryAddressName,
(select sins.kind_name from storage_in_stock sins
where sins.status in ('02','01') and sins.granary_address = lps.storage_code order by sins.unload_end_time desc limit 0,1) as kindName,
(select sins.goods_name from storage_in_stock sins
where sins.status in ('02','01') and sins.granary_address = lps.storage_code order by sins.unload_end_time desc limit 0,1) as goodsName,
(select ifnull(sum(sins.settlement_num),0) from storage_in_stock sins
where sins.status in ('02','01') and sins.granary_address = lps.storage_code) as grandTotalInStockNum,
(SELECT IFNULL(sum(souts.gross_weight),0) - IFNULL(sum(souts.bare_weight),0) from storage_out_stock souts
where souts.granary_address = lps.storage_code AND souts.status in ('02','01')) AS grandTotalOutStockNum,
(select ifnull(sum(lcl.lading_Quality),0) from storage_receipt sr
right join storage_pledge sp on (sr.receipt_serial_num = sp.receipt_serial_num and sp.status = '06')
right join lp_collateral_list lcl on sp.pledge_serial_num = lcl.pledge_number
where sr.granary_address = lps.storage_code and sr.status = '06') as grandTotalRelieveNum,
(select ifnull(sum(sr1.in_num),0) from storage_receipt sr1
where sr1.receipt_serial_num in (select sp1.receipt_serial_num from storage_pledge sp1 where sp1.status = '06')
and sr1.granary_address = lps.storage_code and sr1.status = '06') as grandTotalPledgeNum,
lp.partner as partner,
lps.business_leader as businessLeader,
lps.project_leader as projectLeader,
lps.supervisor as supervisor,
lps.supervise_start_time as superviseStartTime,
lps.supervise_end_time as superviseEndTime,
(select lc.clean_time from lp_cleanstorage lc
where lc.status = '02' and lc.storage_code = lps.storage_code order by lc.clean_time desc limit 0,1) as cleanTime
from lp_project_storage lps
left join lp_project lp on lp.id = lps.project_id
<where>
<if test="storageCodeList != null and storageCodeList.size>0">
lps.storage_code in
<foreach collection="storageCodeList" item="granaryAddress" open="(" separator="," close=")">
#{granaryAddress}
</foreach>
</if>
<if test="query.granaryAddressName != null and query.granaryAddressName != ''">-->
and lps.storage_name like CONCAT('%',#{query.granaryAddressName},'%')
</if>
<if test="query.projectName != null and query.projectName != ''">
and lps.project_name like CONCAT('%',#{query.projectName},'%')
</if>
<if test="query.granaryAddress != null and query.granaryAddress != ''">
and lps.storage_code = #{query.granaryAddress}
</if>
<if test="query.project != null and query.project != ''">
and lps.project_id = #{query.project}
</if>
<if test="query.kindName != null and query.kindName != ''">
and lps.storage_code in (select sins.granary_address from storage_in_stock sins where sins.status in ('02','01') and sins.kind_name = #{query.kindName})
</if>
<if test="query.goodsName != null and query.goodsName != ''">
and lps.storage_code in (select sins.granary_address from storage_in_stock sins where sins.status in ('02','01') and sins.goods_name = #{query.goodsName})
</if>
<if test="query.partner != null and query.partner != ''">
and lp.partner = #{query.partner}
</if>
<if test="query.partnerStr != null and query.partnerStr != ''">
and lp.partner in (select cm.client_id from client_management cm where cm.enterprise_name like CONCAT('%',#{query.partnerStr},'%'))
</if>
<if test="query.businessLeader != null and query.businessLeader != ''">
and lps.business_leader = #{query.businessLeader}
</if>
<if test="query.businessLeaderStr != null and query.businessLeaderStr != ''">
and lps.business_leader in (select pfu.id from per_front_user pfu where pfu.user_name like CONCAT('%',#{query.businessLeaderStr},'%'))
</if>
<if test="query.projectLeader != null and query.projectLeader != ''">
and lps.project_leader = #{query.projectLeader}
</if>
<if test="query.projectLeaderStr != null and query.projectLeaderStr != ''">
and lps.project_leader in (select pfu.id from per_front_user pfu where pfu.user_name like CONCAT('%',#{query.projectLeaderStr},'%'))
</if>
<if test="query.supervisor != null and query.supervisor != ''">
and lps.supervisor like CONCAT('%',#{query.supervisor},'%')
</if>
<if test="query.supervisorStr != null and query.supervisorStr != ''">
and lps.supervisor in (select pfu.id from per_front_user pfu where pfu.user_name like CONCAT('%',#{query.supervisorStr},'%'))
</if>
<if test="query.superviseStartTime != null and query.superviseStartTime != ''">
and lps.supervise_start_time = #{query.superviseStartTime}
</if>
<if test="query.superviseEndTime != null and query.superviseEndTime != ''">
and lps.supervise_end_time = #{query.superviseEndTime}
</if>
<if test="query.cleanTime != null">
and lps.storage_code in (select lc.storage_code from lp_cleanstorage lc
where lc.status = '02' and DATE_FORMAT(lc.clean_time,'%Y-%m-%d') = #{query.cleanTime})
</if>
<if test="query.startTime != null and query.endTime != null">
AND (lps.storage_code in (
(select DISTINCT(sins.granary_address) from storage_in_stock sins where
date_format(sins.bare_weight_time,'%y%m%d %H%i%s') >= date_format(#{query.startTime},'%y%m%d %H%i%s')
and date_format(sins.bare_weight_time,'%y%m%d %H%i%s') <= date_format(#{query.endTime},'%y%m%d %H%i%s')))
OR
lps.storage_code in (
(select DISTINCT(souts.granary_address) from storage_out_stock souts where
date_format(souts.gross_weight_time,'%y%m%d %H%i%s') >= date_format(#{query.startTime},'%y%m%d %H%i%s')
and date_format(souts.gross_weight_time,'%y%m%d %H%i%s') <= date_format(#{query.endTime},'%y%m%d %H%i%s'))))
</if>
</where>
order by lp.create_time desc
</select>
<select id="findHouseByPage"
parameterType="com.boot.reservation.entity.domain.vo.StatisticsHouseWebsiteVO"
resultType="com.boot.reservation.entity.domain.vo.StatisticsHouseWebsiteVO">
select
lps.storage_code as granaryAddress,
lpsh.storehouse_code as house,
lpsh.storehouse_name as houseName,
(select sins.kind_name from storage_in_stock sins
where sins.status in ('02','01') and sins.house = lpsh.storehouse_code order by sins.unload_end_time desc limit 0,1) as kindName,
(select sins.goods_name from storage_in_stock sins
where sins.status in ('02','01') and sins.house = lpsh.storehouse_code order by sins.unload_end_time desc limit 0,1) as goodsName,
(select ifnull(sum(sins.settlement_num),0) from storage_in_stock sins
where sins.status in ('02','01') and sins.house = lpsh.storehouse_code) as grandTotalInStockNum,
(SELECT IFNULL(sum(souts.gross_weight), 0) - IFNULL(sum(souts.bare_weight),0) from storage_out_stock souts
where souts.house = lpsh.storehouse_code and souts.status in ('02','01')) AS grandTotalOutStockNum,
(select ifnull(sum(lcl.lading_Quality),0) from storage_receipt sr
right join storage_pledge sp on (sr.receipt_serial_num = sp.receipt_serial_num and sp.status = '06')
right join lp_collateral_list lcl on sp.pledge_serial_num = lcl.pledge_number
where sr.house = lpsh.storehouse_code and sr.status = '06') as grandTotalRelieveNum,
(select ifnull(sum(sr1.in_num),0) from storage_receipt sr1
where sr1.receipt_serial_num in (select sp1.receipt_serial_num from storage_pledge sp1 where sp1.status = '06')
and sr1.house = lpsh.storehouse_code and sr1.status = 06) as grandTotalPledgeNum,
lp.partner as partner,
lps.business_leader as businessLeader,
lps.project_leader as projectLeader,
lps.supervisor as supervisor,
lps.supervise_start_time as superviseStartTime,
lps.supervise_end_time as superviseEndTime,
(select lc.clean_time from lp_cleanstorage lc
left join lp_cleanstorage_details lcd on lcd.clean_id = lc.id
where lc.status = '02' and lcd.storagehouse_code = lpsh.storehouse_code order by lc.clean_time desc limit 0,1) as cleanTime,
lpsh.status as status
from lp_project_storagehouse lpsh
left join lp_project_storage lps on lps.storage_code = lpsh.storage_Code
left join lp_project lp on lp.id = lps.project_id
<where>
<if test="query.granaryAddress != null and query.granaryAddress != ''">
lps.storage_code = #{query.granaryAddress}
</if>
<if test="query.house != null and query.house != ''">
and lpsh.storehouse_code = #{query.house}
</if>
<if test="query.houseName != null and query.houseName != ''">
and lpsh.storehouse_name like CONCAT('%',#{query.houseName},'%')
</if>
<if test="query.kindName != null and query.kindName != ''">
and lpsh.storehouse_code in (select sins.house from storage_in_stock sins where sins.status in ('02','01') and sins.kind_name = #{query.kindName})
</if>
<if test="query.goodsName != null and query.goodsName != ''">
and lpsh.storehouse_code in (select sins.house from storage_in_stock sins where sins.status in ('02','01') and sins.goods_name = #{query.goodsName})
</if>
<if test="query.partner != null and query.partner != ''">
and lp.partner = #{query.partner}
</if>
<if test="query.partnerStr != null and query.partnerStr != ''">
and lp.partner in (select cm.client_id from client_management cm where cm.enterprise_name like CONCAT('%',#{query.partnerStr},'%'))
</if>
<if test="query.businessLeader != null and query.businessLeader != ''">
and lps.business_leader = #{query.businessLeader}
</if>
<if test="query.businessLeaderStr != null and query.businessLeaderStr != ''">
and lps.business_leader in (select pfu.id from per_front_user pfu where pfu.user_name like CONCAT('%',#{query.businessLeaderStr},'%'))
</if>
<if test="query.projectLeader != null and query.projectLeader != ''">
and lps.project_leader = #{query.projectLeader}
</if>
<if test="query.projectLeaderStr != null and query.projectLeaderStr != ''">
and lps.project_leader in (select pfu.id from per_front_user pfu where pfu.user_name like CONCAT('%',#{query.projectLeaderStr},'%'))
</if>
<if test="query.supervisor != null and query.supervisor != ''">
and lps.supervisor like CONCAT('%',#{query.supervisor},'%')
</if>
<if test="query.supervisorStr != null and query.supervisorStr != ''">
and lps.supervisor in (select pfu.id from per_front_user pfu where pfu.user_name like CONCAT('%',#{query.supervisorStr},'%'))
</if>
<if test="query.superviseStartTime != null and query.superviseStartTime != ''">
and lps.supervise_start_time = #{query.superviseStartTime}
</if>
<if test="query.superviseEndTime != null and query.superviseEndTime != ''">
and lps.supervise_end_time = #{query.superviseEndTime}
</if>
<if test="query.cleanTime != null">
and lpsh.storehouse_code in (select lcd.storagehouse_code from lp_cleanstorage_details lcd
left join lp_cleanstorage lc on lc.id = lcd.clean_id
where lc.status = '02' and DATE_FORMAT(lc.clean_time,'%Y-%m-%d') = #{query.cleanTime})
</if>
<if test="query.status != null and query.status != ''">
lpsh.status = #{query.status}
</if>
<if test="query.startTime != null and query.endTime != null">
AND (lps.storage_code in (
(select DISTINCT(sins.granary_address) from storage_in_stock sins where
date_format(sins.bare_weight_time,'%y%m%d %H%i%s') >= date_format(#{query.startTime},'%y%m%d %H%i%s')
and date_format(sins.bare_weight_time,'%y%m%d %H%i%s') <= date_format(#{query.endTime},'%y%m%d %H%i%s')))
OR
lps.storage_code in (
(select DISTINCT(souts.granary_address) from storage_out_stock souts where
date_format(souts.gross_weight_time,'%y%m%d %H%i%s') >= date_format(#{query.startTime},'%y%m%d %H%i%s')
and date_format(souts.gross_weight_time,'%y%m%d %H%i%s') <= date_format(#{query.endTime},'%y%m%d %H%i%s'))))
</if>
</where>
order by str_to_date(lpsh.time, '%Y-%m-%d %H:%i:%S') desc
</select>
<select id="findHouseDetailsByPage"
parameterType="com.boot.reservation.entity.domain.vo.StatisticsHouseDetailsWebsiteVO"
resultType="com.boot.reservation.entity.domain.vo.StatisticsHouseDetailsWebsiteVO">
select
*
from
(
(select sins.granary_address as granaryAddress,
sins.house as house,
sins.in_stock_serial_num as serialNumber,
'入库' as businessType,
sins.unload_end_time as time,
sins.kind_name as kindName,
sins.gross_weight as grossWeight,
sins.bare_weight as bareWeight,
sins.reality_reduce_weight as realityReduceWeight
from storage_in_stock sins
where sins.status = 02
<if test="query.house != null and query.house != ''">
and sins.house = #{query.house}
</if>)
UNION ALL
(select souts.granary_address as granaryAddress,
souts.house as house,
souts.out_stock_serial_num as serialNumber,
'出库' as businessType,
souts.load_end_time as time,
souts.goods_name as kindName,
souts.gross_weight as grossWeight,
souts.bare_weight as bareWeight,
0.0 as realityReduceWeight
from storage_out_stock souts
where souts.status = 02
<if test="query.house != null and query.house != ''">
and souts.house = #{query.house}
</if>)
) as shdw
<where>
<if test="query.businessType != null and query.businessType != ''">
and shdw.businessType = #{query.businessType}
</if>
<if test="query.kindName != null and query.kindName != ''">
and shdw.kindName = #{query.kindName}
</if>
<if test="query.startTime != null">
AND date_format(shdw.time,'%y%m%d %H%i%s') >= date_format(#{query.startTime},'%y%m%d %H%i%s')
</if>
<if test="query.endTime != null">
AND date_format(shdw.time,'%y%m%d %H%i%s') <= date_format(#{query.endTime},'%y%m%d %H%i%s')
</if>
</where>
ORDER BY time desc
</select>
<select id="findGranaryByIsFunder" resultType="String">
select lps.storage_code
from lp_project_storage lps
left join lp_project lp on lp.id = lps.project_id
where lp.isfunder = #{isFunder}
</select>
<select id="findGoodsNameStr" resultType="String" parameterType="String">
select cargo_name from lp_cargo where cargo_code = #{goodsName}
</select>
</mapper>
以下是实体类:
package com.boot.reservation.entity.domain.vo;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.DictValue;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
/**
* 库点统计VO
*/
@Data
@ApiModel("库点统计VO")
public class StatisticsGranaryWebsiteVO extends Page implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 项目id
*/
@ApiModelProperty("项目id")
private String project;
/**
* 项目名称
*/
@ApiModelProperty("项目名称")
private String projectName;
/**
* 库点编号
*/
@ApiModelProperty("库点编号")
private String granaryAddress;
/**
* 库点名称
*/
@ApiModelProperty("库点名称")
private String granaryAddressName;
/**
* 货物类型
*/
@ApiModelProperty("货物类型")
@DictValue(dictCode = "cargoType")
private String kindName;
/**
* 商品名称
*/
@ApiModelProperty("商品名称")
private String goodsName;
/**
* 商品名称文字
*/
@ApiModelProperty("商品名称文字")
private String goodsNameStr;
/**
* 累计入库数量(kg)
*/
@ApiModelProperty("累计入库数量(kg)")
private Double grandTotalInStockNum;
/**
* 累计出库数量(kg)
*/
@ApiModelProperty("累计出库数量(kg)")
private Double grandTotalOutStockNum;
/**
* 累计解压数量(kg)
*/
@ApiModelProperty("累计解压数量(kg)")
private Double grandTotalRelieveNum;
/**
* 剩余可出库数量(kg)
*/
@ApiModelProperty("剩余可出库数量(kg)")
private Double residueOutStockNum;
/**
* 库存数量(kg)
*/
@ApiModelProperty("库存数量(kg)")
private Double grandTotalNum;
/**
* 累计质押数量(kg)
*/
@ApiModelProperty("累计质押数量(kg)")
private Double grandTotalPledgeNum;
/**
* 质押中数量(kg)
*/
@ApiModelProperty("质押中数量(kg)")
private Double justPledgeNum;
/**
* 解除质押但未提货数量(kg)
*/
@ApiModelProperty("解除质押但未提货数量(kg)")
private Double notLadingQualityNum;
/**
* 已入库未质押数量(kg)
*/
@ApiModelProperty("已入库未质押数量(kg)")
private Double notPledgeNum;
/**
* 合作企业
*/
@ApiModelProperty("合作企业")
private String partner;
/**
* 合作企业文字
*/
@ApiModelProperty("合作企业文字")
private String partnerStr;
/**
* 业务负责人
*/
@ApiModelProperty("业务负责人")
private String businessLeader;
/**
* 业务负责人文字
*/
@ApiModelProperty("业务负责人文字")
private String businessLeaderStr;
/**
* 项目负责人
*/
@ApiModelProperty("项目负责人")
private String projectLeader;
/**
* 项目负责人文字
*/
@ApiModelProperty("项目负责人文字")
private String projectLeaderStr;
/**
* 监管员
*/
@ApiModelProperty("监管员")
private String supervisor;
/**
* 监管员文字
*/
@ApiModelProperty("监管员文字")
private String supervisorStr;
/**
* 监管开始时间
*/
@ApiModelProperty("监管开始时间")
private String superviseStartTime;
/**
* 预计结束时间
*/
@ApiModelProperty("预计结束时间")
private String superviseEndTime;
/**
* 清库时间
*/
@JsonFormat(pattern = "yyyy-MM-dd")
@ApiModelProperty("清库时间")
private Date cleanTime;
/**
* 已开出库单数
*/
@ApiModelProperty("已开出库单数")
private BigDecimal alreadyOutStock;
/**
* 业务开始时间
*/
@ApiModelProperty("业务开始时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date startTime;
/**
* 业务结束时间
*/
@ApiModelProperty("业务结束时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date endTime;
}
package com.boot.reservation.entity.domain.vo;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.DictValue;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
/**
* 仓房统计VO
*/
@Data
@ApiModel("仓房统计VO")
public class StatisticsHouseWebsiteVO extends Page implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 库点编号
*/
@ApiModelProperty("库点编号")
private String granaryAddress;
/**
* 仓房编码
*/
@ApiModelProperty("仓房编码")
private String house;
/**
* 仓房名称
*/
@ApiModelProperty("仓房名称")
private String houseName;
/**
* 货物类型
*/
@ApiModelProperty("货物类型")
@DictValue(dictCode = "cargoType")
private String kindName;
/**
* 商品名称
*/
@ApiModelProperty("商品名称")
private String goodsName;
/**
* 商品名称文字
*/
@ApiModelProperty("商品名称文字")
private String goodsNameStr;
/**
* 累计入库数量(kg)
*/
@ApiModelProperty("累计入库数量(kg)")
private Double grandTotalInStockNum;
/**
* 累计出库数量(kg)
*/
@ApiModelProperty("累计出库数量(kg)")
private Double grandTotalOutStockNum;
/**
* 累计解压数量(kg)
*/
@ApiModelProperty("累计解压数量(kg)")
private Double grandTotalRelieveNum;
/**
* 剩余可出库数量(kg)
*/
@ApiModelProperty("剩余可出库数量(kg)")
private Double residueOutStockNum;
/**
* 库存数量(kg)
*/
@ApiModelProperty("库存数量(kg)")
private Double grandTotalNum;
/**
* 累计质押数量(kg)
*/
@ApiModelProperty("累计质押数量(kg)")
private Double grandTotalPledgeNum;
/**
* 质押中数量(kg)
*/
@ApiModelProperty("质押中数量(kg)")
private Double justPledgeNum;
/**
* 解除质押但未提货数量(kg)
*/
@ApiModelProperty("解除质押但未提货数量(kg)")
private Double notLadingQualityNum;
/**
* 已入库未质押数量(kg)
*/
@ApiModelProperty("已入库未质押数量(kg)")
private Double notPledgeNum;
/**
* 合作企业
*/
@ApiModelProperty("合作企业")
private String partner;
/**
* 合作企业文字
*/
@ApiModelProperty("合作企业文字")
private String partnerStr;
/**
* 业务负责人
*/
@ApiModelProperty("业务负责人")
private String businessLeader;
/**
* 业务负责人文字
*/
@ApiModelProperty("业务负责人文字")
private String businessLeaderStr;
/**
* 项目负责人
*/
@ApiModelProperty("项目负责人")
private String projectLeader;
/**
* 项目负责人文字
*/
@ApiModelProperty("项目负责人文字")
private String projectLeaderStr;
/**
* 监管员
*/
@ApiModelProperty("监管员")
private String supervisor;
/**
* 监管员文字
*/
@ApiModelProperty("监管员文字")
private String supervisorStr;
/**
* 监管开始时间
*/
@ApiModelProperty("监管开始时间")
private String superviseStartTime;
/**
* 预计结束时间
*/
@ApiModelProperty("预计结束时间")
private String superviseEndTime;
/**
* 清库时间
*/
@JsonFormat(pattern = "yyyy-MM-dd")
@ApiModelProperty("清库时间")
private Date cleanTime;
/**
* 已开出库单数
*/
@ApiModelProperty("已开出库单数")
private BigDecimal alreadyOutStock;
/**
* 仓房状态2
*/
@ApiModelProperty("仓房状态")
@DictValue(dictCode = "storagehouseStatus")
private String status;
/**
* 业务开始时间
*/
@ApiModelProperty("业务开始时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date startTime;
/**
* 业务结束时间
*/
@ApiModelProperty("业务结束时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date endTime;
}
package com.boot.reservation.entity.domain.vo;
import java.math.BigDecimal;
import java.util.Date;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.DictValue;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
/**
* 仓房明细查看统计VO
*/
@Data
@ApiModel("仓房明细查看统计VO")
public class StatisticsHouseDetailsWebsiteVO extends Page implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 仓房编码
*/
@ApiModelProperty("仓房编码")
private String house;
/**
* 库点编号
*/
@ApiModelProperty("库点编号")
private String granaryAddress;
/**
* 流水号
*/
@ApiModelProperty("流水号")
private String serialNumber;
/**
* 业务类型
*/
@ApiModelProperty("业务类型")
private String businessType;
/**
* 时间
*/
@ApiModelProperty("时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date time;
/**
* 业务开始时间
*/
@ApiModelProperty("业务开始时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date startTime;
/**
* 业务结束时间
*/
@ApiModelProperty("业务结束时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date endTime;
/**
* 货物类型
*/
@ApiModelProperty("货物类型")
@DictValue(dictCode = "cargoType")
private String kindName;
/**
* 商品净重
*/
@ApiModelProperty("商品净重")
private BigDecimal weight;
/**
* 毛重 - 与前端无关
*/
@ApiModelProperty("毛重 - 与前端无关")
private Double grossWeight;
/**
* 皮重 - 与前端无关
*/
@ApiModelProperty("皮重 - 与前端无关")
private Double bareWeight;
/**
* 实际扣量 - 与前端无关
*/
@ApiModelProperty("实际扣量 - 与前端无关")
private Double realityReduceWeight;
}