Hibernate统计、求和写法

1、前提

数据表：
标签表：标签名称，删除状态等
工作表：创建人，工作内容，审批人，审批状态，审批时间，工作时长，删除状态等
用户表：用户姓名、登录账号、删除状态等
关系：
标签:工作 = 1:N
用户:工作 = 1:N

2、需求

（1）工作审批统计：统计在某个时间段内，哪个审批人审批了多少工作

代码如下：

@Query(value = "select count(t) as count,u.userName as approvename from Routinework t " +
    "left join SysUser u on u.loginCode = t.approver " +
    "where 1 = 1 " +
    "and ((:startDate is null and :endDate is null) or t.approverTime between :startDate and :endDate) " +
    "and (u.status = :status) " +
    "and (not t.approver is null ) " +
    "and (not t.approverTime is null ) " +
    "and (t.deleted = false ) " +
    "and (t.status = '审批通过') " +
    "group by t.approver")
List<Map> workApproveTotalByTime(@Param("startDate") Instant startDate,
                                 @Param("endDate") Instant endDate,
                                 @Param("status") UserStatusType status);

解析：where条件中，审批人或者审批时间不能为空，审批人所映射的用户不能为已删除的用户，工作不能为已删除的工作，而且状态必须为审批通过，最后通过审批人进行分组，得到结果。

（2）工作来源统计1：统计在某个时间段内，每个人有多少个工时的工作

代码如下：

@Query(value = "select sum(t.manhour) as hourSum,u.userName as userName from Routinework t " +
    "left join SysUser u on u.loginCode = t.createdBy " +
    "where 1 = 1 " +
    "and ((:startDate is null and :endDate is null) or t.approverTime between :startDate and :endDate) " +
    "and (u.status = :status) " +
    "and (not t.approverTime is null) " +
    "and (not t.approver is null) " +
    "and (t.deleted = false) " +
    "and (t.status = '审批通过') " +
    "group by t.createdBy")
List<Map> workManhourTotalByTime(@Param("startDate") Instant startDate,
                                 @Param("endDate") Instant endDate,
                                 @Param("status") UserStatusType status);

解析：与上面一个条件大致相同，求和的工时需要在代码中再进行四舍五入才能用。
四舍五入代码：

/**
 * double数据：保留小数点后bit位，四舍五入
 * @param d
 * @param bit
 * @return
 */
public static double doubleBitUp(double d,int bit) {
    if (d == 0.0){
        return d;
    }
    double pow = Math.pow(10, bit);
    return (double)Math.round(d*pow)/pow;
}

（3）工作来源统计2：统计在某个时间段内，每个人有多少个工作

代码如下：

@Query(value = "select count(t) as count,u.userName as userName from Routinework t " +
    "left join SysUser u on u.loginCode = t.createdBy " +
    "where 1 = 1 " +
    "and ((:startDate is null and :endDate is null) or t.approverTime between :startDate and :endDate) " +
    "and (u.status = :status) " +
    "and (not t.approver is null) " +
    "and (not t.approverTime is null) " +
    "and (t.deleted = false) " +
    "and (t.status = '审批通过') " +
    "group by t.createdBy")
List<Map> workTaskNumberTotalByTime(@Param("startDate") Instant startDate,
                                    @Param("endDate") Instant endDate,
                                    @Param("status") UserStatusType status);

（4）工作类型统计1：统计每个标签下有多少工时的工作

代码如下：

@Query(value = "select sum(work.manhour) as manhours,lable.name as lableName from Routinework work " +
    "left join work.routinelabels lable on lable.deleted = false " +
    "where 1 = 1 " +
    "and ((:startDate is null and :endDate is null) or work.approverTime between :startDate and :endDate) " +
    "and (not work.approver is null) " +
    "and (not work.approverTime is null) " +
    "and (work.deleted = false) " +
    "and (work.status = '审批通过') " +
    "group by lable.id")
List<Map> workTypeTotalByTimeAndManhour(@Param("startDate") Instant startDate,
                                        @Param("endDate") Instant endDate);

如果要求比列，还要再加上一个求总工时的方法：

@Query(value = "select sum(work.manhour) as manhours from Routinework work " +
    "left join work.routinelabels lable on lable.deleted = false " +
    "where 1 = 1 " +
    "and ((:startDate is null and :endDate is null) or work.approverTime between :startDate and :endDate) " +
    "and (not work.approver is null) " +
    "and (not work.approverTime is null) " +
    "and (work.deleted = false) " +
    "and (work.status = '审批通过') ")
Double sumManhour(@Param("startDate") Instant startDate,
                @Param("endDate") Instant endDate);

（5）工作类型统计2：统计每个工作标签下有多少个工作

代码如下：

@Query(value = "select count(work) as taskNos,lable.name as lableName from Routinework work " +
    "left join work.routinelabels lable on lable.deleted = false " +
    "where 1 = 1 " +
    "and ((:startDate is null and :endDate is null) or work.approverTime between :startDate and :endDate)" +
    "and (not work.approver is null) " +
    "and (not work.approverTime is null) " +
    "and (work.deleted = false) " +
    "and (work.status = '审批通过') " +
    "group by lable.id")
List<Map> workTypeTotalByTimeAndTaskNo(@Param("startDate") Instant startDate,
                                        @Param("endDate") Instant endDate);

如果要求比列，还要加上一个求总任务数的方法：

@Query(value = "select count(work) as taskNos from Routinework work " +
    "left join work.routinelabels lable on lable.deleted = false " +
    "where 1 = 1 " +
    "and ((:startDate is null and :endDate is null) or work.approverTime between :startDate and :endDate)" +
    "and (not work.approver is null) " +
    "and (not work.approverTime is null) " +
    "and (work.deleted = false) " +
    "and (work.status = '审批通过') ")
Long sumTasks(@Param("startDate") Instant startDate,
              @Param("endDate") Instant endDate);

3、如果只传开始时间或者结束时间，后端给一个公用方法去处理时间格式

/**
 * 初始化时间：如果不传开始时间或者结束时间的话，给一个默认值
 * @param startDate
 * @param endDate
 */
public void initTimeIfNull(Instant startDate,Instant endDate){
    if (startDate == null){
        startDate = Instant.now().minus(365, ChronoUnit.DAYS); //当前时间的一年前
    }
    if (endDate == null){
        endDate = Instant.now(); //当前时间
    }
}