pipIrr-platform/pipIrr-global/src/main/java/com/dy/pipIrrGlobal/daoRm/RmIntakeAmountDayMapper.java
@@ -2,9 +2,10 @@ import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.dy.pipIrrGlobal.pojoRm.RmIntakeAmountDay; import com.dy.pipIrrGlobal.voSt.VoDayIntakeAmount; import com.dy.pipIrrGlobal.voRm.VoIntakeAmountDay; import com.dy.pipIrrGlobal.voSt.VoDayIntakeAmount; import com.dy.pipIrrGlobal.voSt.VoIntakeAmountStatistics; import com.dy.pipIrrGlobal.voSt.VoMonthIntakeAmount; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; @@ -105,4 +106,17 @@ */ List<VoDayIntakeAmount> getDayIntakeAmount(Map<?, ?> params); /** * 统计指定年份各月用水量记录数量 * @param params * @return */ Long getMonthIntakeAmountCount(Map<?, ?> params); /** * 统计指定年份各月用水量 * @param params * @return */ List<VoMonthIntakeAmount> getMonthIntakeAmount(Map<?, ?> params); } pipIrr-platform/pipIrr-global/src/main/java/com/dy/pipIrrGlobal/voSt/VoMonthIntakeAmount.java
New file @@ -0,0 +1,43 @@ package com.dy.pipIrrGlobal.voSt; import com.alibaba.fastjson2.annotation.JSONField; import com.alibaba.fastjson2.writer.ObjectWriterImplToString; import com.fasterxml.jackson.annotation.JsonPropertyOrder; import lombok.Data; /** * @author ZhuBaoMin * @date 2024-08-08 14:16 * @LastEditTime 2024-08-08 14:16 * @Description 取水口月用水视图 */ @Data @JsonPropertyOrder({ "intakeId", "intakeNum", "month1" , "month2" , "month3" , "month4" , "month5" , "month6" , "month7" , "month8" , "month9" , "month10" , "month11" , "month12" }) public class VoMonthIntakeAmount { private static final long serialVersionUID = 202408081417001L; /** * 取水口ID */ @JSONField(serializeUsing= ObjectWriterImplToString.class) private Long intakeId; /** * 取水口编号 */ private String intakeNum; private Double month1; private Double month2; private Double month3; private Double month4; private Double month5; private Double month6; private Double month7; private Double month8; private Double month9; private Double month10; private Double month11; private Double month12; } pipIrr-platform/pipIrr-global/src/main/resources/mapper/RmIntakeAmountDayMapper.xml
@@ -211,10 +211,10 @@ </if> </where> ORDER BY riad.id DESC <!-- <if test="pageCurr != null and pageSize != null">--> <!-- LIMIT ${(pageCurr-1)*pageSize}, ${pageSize}--> <!-- </if>--> <trim prefix="limit " > <!-- <if test="pageCurr != null and pageSize != null">--> <!-- LIMIT ${(pageCurr-1)*pageSize}, ${pageSize}--> <!-- </if>--> <trim prefix="limit "> <if test="start != null and count != null"> #{start,javaType=Integer,jdbcType=INTEGER}, #{count,javaType=Integer,jdbcType=INTEGER} </if> @@ -234,7 +234,12 @@ <select id="getDayIntakeAmountCount" resultType="java.lang.Long"> SELECT COUNT(*) AS recordCount FROM pr_intake inta WHERE inta.deleted = 0 <where> AND inta.deleted = 0 <if test="intakeNum != null and intakeNum != ''"> AND inta.name = #{intakeNum} </if> </where> </select> <!--统计指定月份各天用水量--> @@ -428,7 +433,115 @@ AND MONTH(intaDay.dt) = #{month} AND DAY(intaDay.dt) = 31), 0) AS amount31 FROM pr_intake inta WHERE inta.deleted = 0 <where> AND inta.deleted = 0 <if test="intakeNum != null and intakeNum != ''"> AND inta.name = #{intakeNum} </if> </where> ORDER BY inta.id <trim prefix="limit "> <if test="start != null and count != null"> #{start,javaType=Integer,jdbcType=INTEGER}, #{count,javaType=Integer,jdbcType=INTEGER} </if> </trim> </select> <!--统计指定年份各月用水量记录数量--> <select id="getMonthIntakeAmountCount" resultType="java.lang.Long"> SELECT COUNT(*) AS recordCount FROM pr_intake inta <where> AND inta.deleted = 0 <if test="intakeNum != null and intakeNum != ''"> AND inta.name = #{intakeNum} </if> </where> </select> <!--统计指定年份各月用水量--> <select id="getMonthIntakeAmount" resultType="com.dy.pipIrrGlobal.voSt.VoMonthIntakeAmount"> SELECT inta.id AS intakeId, inta.name AS intakeNum, IFNULL((SELECT SUM(amount) AS amount FROM rm_intake_amount_day intaDay WHERE intaDay.intake_id = inta.id AND YEAR(intaDay.dt) = #{year} AND MONTH(intaDay.dt) = 1 GROUP BY(intaDay.intake_id)), 0) AS month1, IFNULL((SELECT SUM(amount) AS amount FROM rm_intake_amount_day intaDay WHERE intaDay.intake_id = inta.id AND YEAR(intaDay.dt) = #{year} AND MONTH(intaDay.dt) = 2 GROUP BY(intaDay.intake_id)), 0) AS month2, IFNULL((SELECT SUM(amount) AS amount FROM rm_intake_amount_day intaDay WHERE intaDay.intake_id = inta.id AND YEAR(intaDay.dt) = #{year} AND MONTH(intaDay.dt) = 3 GROUP BY(intaDay.intake_id)), 0) AS month3, IFNULL((SELECT SUM(amount) AS amount FROM rm_intake_amount_day intaDay WHERE intaDay.intake_id = inta.id AND YEAR(intaDay.dt) = #{year} AND MONTH(intaDay.dt) = 4 GROUP BY(intaDay.intake_id)), 0) AS month4, IFNULL((SELECT SUM(amount) AS amount FROM rm_intake_amount_day intaDay WHERE intaDay.intake_id = inta.id AND YEAR(intaDay.dt) = #{year} AND MONTH(intaDay.dt) = 5 GROUP BY(intaDay.intake_id)), 0) AS month5, IFNULL((SELECT SUM(amount) AS amount FROM rm_intake_amount_day intaDay WHERE intaDay.intake_id = inta.id AND YEAR(intaDay.dt) = #{year} AND MONTH(intaDay.dt) = 6 GROUP BY(intaDay.intake_id)), 0) AS month6, IFNULL((SELECT SUM(amount) AS amount FROM rm_intake_amount_day intaDay WHERE intaDay.intake_id = inta.id AND YEAR(intaDay.dt) = #{year} AND MONTH(intaDay.dt) = 7 GROUP BY(intaDay.intake_id)), 0) AS month7, IFNULL((SELECT SUM(amount) AS amount FROM rm_intake_amount_day intaDay WHERE intaDay.intake_id = inta.id AND YEAR(intaDay.dt) = #{year} AND MONTH(intaDay.dt) = 8 GROUP BY(intaDay.intake_id)), 0) AS month8, IFNULL((SELECT SUM(amount) AS amount FROM rm_intake_amount_day intaDay WHERE intaDay.intake_id = inta.id AND YEAR(intaDay.dt) = #{year} AND MONTH(intaDay.dt) = 9 GROUP BY(intaDay.intake_id)), 0) AS month9, IFNULL((SELECT SUM(amount) AS amount FROM rm_intake_amount_day intaDay WHERE intaDay.intake_id = inta.id AND YEAR(intaDay.dt) = #{year} AND MONTH(intaDay.dt) = 10 GROUP BY(intaDay.intake_id)), 0) AS month10, IFNULL((SELECT SUM(amount) AS amount FROM rm_intake_amount_day intaDay WHERE intaDay.intake_id = inta.id AND YEAR(intaDay.dt) = #{year} AND MONTH(intaDay.dt) = 11 GROUP BY(intaDay.intake_id)), 0) AS month11, IFNULL((SELECT SUM(amount) AS amount FROM rm_intake_amount_day intaDay WHERE intaDay.intake_id = inta.id AND YEAR(intaDay.dt) = #{year} AND MONTH(intaDay.dt) = 12 GROUP BY(intaDay.intake_id)), 0) AS month12 FROM pr_intake inta <where> AND inta.deleted = 0 <if test="intakeNum != null and intakeNum != ''"> AND inta.name = #{intakeNum} </if> </where> ORDER BY inta.id <trim prefix="limit "> <if test="start != null and count != null"> pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/intake/IntakeSv.java
@@ -646,7 +646,7 @@ * @param qo * @return */ public QueryResultVo<List<VoDayIntakeAmount>> getDayIntakeAmount(DayIntakeAmountQO qo) { public QueryResultVo<List<VoDayIntakeAmount>> getDayIntakeAmount(IntakeAmountQO qo) { Calendar calendar = Calendar.getInstance(); Integer year = Optional.ofNullable(qo.getYear()).orElse(calendar.get(Calendar.YEAR)); Integer month = Optional.ofNullable(qo.getMonth()).orElse(calendar.get(Calendar.MONTH)); @@ -668,4 +668,30 @@ rsVo.obj = rmIntakeAmountDayMapper.getDayIntakeAmount(params); return rsVo ; } /** * 统计指定年份各月用水量 * @param qo * @return */ public QueryResultVo<List<VoMonthIntakeAmount>> getMonthIntakeAmount(IntakeAmountQO qo) { Calendar calendar = Calendar.getInstance(); Integer year = Optional.ofNullable(qo.getYear()).orElse(calendar.get(Calendar.YEAR)); qo.setYear(year); // 生成查询参数 Map<String, Object> params = (Map<String, Object>) PojoUtils.generalize(qo) ; // 获取符合条件的记录数 Long itemTotal = Optional.ofNullable(rmIntakeAmountDayMapper.getMonthIntakeAmountCount(params)).orElse(0L); QueryResultVo<List<VoMonthIntakeAmount>> rsVo = new QueryResultVo<>() ; rsVo.pageSize = qo.pageSize ; rsVo.pageCurr = qo.pageCurr ; rsVo.calculateAndSet(itemTotal, params); rsVo.obj = rmIntakeAmountDayMapper.getMonthIntakeAmount(params); return rsVo ; } } pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/intake/IntkeCtrl.java
@@ -334,7 +334,7 @@ */ @GetMapping(path = "/getDayIntakeAmount") @SsoAop() public BaseResponse<QueryResultVo<List<VoDayIntakeAmount>>> getDayIntakeAmount(DayIntakeAmountQO qo) { public BaseResponse<QueryResultVo<List<VoDayIntakeAmount>>> getDayIntakeAmount(IntakeAmountQO qo) { try { return BaseResponseUtils.buildSuccess(intakeSv.getDayIntakeAmount(qo)); } catch (Exception e) { @@ -342,4 +342,20 @@ return BaseResponseUtils.buildException(e.getMessage()) ; } } /** * 统计指定年份各月用水量 * @param qo * @return */ @GetMapping(path = "/getMonthIntakeAmount") @SsoAop() public BaseResponse<QueryResultVo<List<VoMonthIntakeAmount>>> getMonthIntakeAmount(IntakeAmountQO qo) { try { return BaseResponseUtils.buildSuccess(intakeSv.getMonthIntakeAmount(qo)); } catch (Exception e) { log.error("获取记录异常", e); return BaseResponseUtils.buildException(e.getMessage()) ; } } } pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/intake/qo/IntakeAmountQO.java
File was renamed from pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/intake/qo/DayIntakeAmountQO.java @@ -11,7 +11,7 @@ */ @Data public class DayIntakeAmountQO extends QueryConditionVo { public class IntakeAmountQO extends QueryConditionVo { /** * 年 @@ -22,4 +22,9 @@ * 月 */ private Integer month; /** * 取水口编号 */ private String intakeNum; }
