pipIrr-platform/pipIrr-global/src/main/java/com/dy/pipIrrGlobal/daoRm/RmOpenCloseValveHistoryMapper.java
@@ -3,6 +3,7 @@ import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.dy.pipIrrGlobal.pojoRm.RmOpenCloseValveHistory; import com.dy.pipIrrGlobal.voRm.VoOpenCloseValve; import com.dy.pipIrrGlobal.voSt.VoClient; import com.dy.pipIrrGlobal.voSt.VoIntake; import com.dy.pipIrrGlobal.voSt.VoIntakeOpenCount; import org.apache.ibatis.annotations.Mapper; @@ -121,4 +122,18 @@ * @return */ List<VoIntakeOpenCount> getOpenValveLtIntakes(Map<String, Object> params); /** * 获取指定时间段内开阀次数超过指定值的农户数量 * @param params * @return */ Long getLargeOpenCountClientsCount(Map<String, Object> params); /** * 获取指定时间段内开阀次数超过指定值的农户 * @param params * @return */ List<VoClient> getLargeOpenCountClients(Map<String, Object> params); } pipIrr-platform/pipIrr-global/src/main/java/com/dy/pipIrrGlobal/voSt/VoClient.java
New file @@ -0,0 +1,48 @@ package com.dy.pipIrrGlobal.voSt; import com.alibaba.fastjson2.annotation.JSONField; import com.alibaba.fastjson2.writer.ObjectWriterImplToString; import lombok.Data; /** * @author ZhuBaoMin * @date 2024-08-06 9:45 * @LastEditTime 2024-08-06 9:45 * @Description 农户视图对象 */ @Data public class VoClient { private static final long serialVersionUID = 202408060947001L; /** * 农户ID */ @JSONField(serializeUsing= ObjectWriterImplToString.class) private Long clientId; /** * 农户姓名 */ private String clientName; /** * 农户编号 */ private String clientNum; /** * 农户地址 */ private String address; /** * 手机号 */ private String phone; /** * 身份证号 */ private String idCard; } pipIrr-platform/pipIrr-global/src/main/resources/application-global.yml
@@ -72,7 +72,7 @@ pipIrr: global: dev: flase #是否开发阶段，true或false dev: false #是否开发阶段，true或false dsName: ym #开发阶段，设置临时的数据库名称 mw: webPort: 8070 pipIrr-platform/pipIrr-global/src/main/resources/mapper/RmOpenCloseValveHistoryMapper.xml
@@ -435,30 +435,29 @@ where id = #{id,jdbcType=BIGINT} </update> <!--根据指定条件获取开关阀报历史记录数量--> <select id="getOpenCloseValveReportsCount_history" resultType="java.lang.Long"> SELECT COUNT(*) AS recordCount FROM rm_open_close_valve_history oh INNER JOIN pr_intake inta ON inta.id = oh.intake_id <where> <if test="intakeId != null and intakeId >0"> AND oh.intake_id = #{intakeId} </if> <if test = "intakeNum != null and intakeNum !=''"> AND inta.name LIKE CONCAT('%',#{intakeNum},'%') </if> <if test = "rtuAddr != null and rtuAddr !=''"> AND oh.rtu_addr LIKE CONCAT('%',#{rtuAddr},'%') </if> <if test = "timeStart_open != null and timeStart_open != '' and timeStop_open != null and timeStop_open != '' "> AND oh.op_dt BETWEEN #{timeStart_open} AND #{timeStop_open} </if> <if test = "timeStart_close != null and timeStart_close != '' and timeStop_close != null and timeStop_close != '' "> AND oh.cl_dt BETWEEN #{timeStart_close} AND #{timeStop_close} </if> </where> </select> <!--根据指定条件获取开关阀报历史记录数量--> <select id="getOpenCloseValveReportsCount_history" resultType="java.lang.Long"> SELECT COUNT(*) AS recordCount FROM rm_open_close_valve_history oh INNER JOIN pr_intake inta ON inta.id = oh.intake_id <where> <if test="intakeId != null and intakeId > 0"> AND oh.intake_id = #{intakeId} </if> <if test="intakeNum != null and intakeNum != ''"> AND inta.name LIKE CONCAT('%', #{intakeNum}, '%') </if> <if test="rtuAddr != null and rtuAddr != ''"> AND oh.rtu_addr LIKE CONCAT('%', #{rtuAddr}, '%') </if> <if test="timeStart_open != null and timeStart_open != '' and timeStop_open != null and timeStop_open != ''"> AND oh.op_dt BETWEEN #{timeStart_open} AND #{timeStop_open} </if> <if test="timeStart_close != null and timeStart_close != '' and timeStop_close != null and timeStop_close != ''"> AND oh.cl_dt BETWEEN #{timeStart_close} AND #{timeStop_close} </if> </where> </select> <!--根据指定条件获取开关阀报历史记录数量--> <select id="getOpenCloseValveReportsCount_history" resultType="java.lang.Long"> SELECT COUNT(*) AS recordCount @@ -552,170 +551,208 @@ </if> </trim> </select> <!--根据指定条件获取开关阀报历史记录--> <select id="getOpenCloseValveReports_history" resultType="com.dy.pipIrrGlobal.voRm.VoOpenCloseValve"> SELECT oh.intake_id AS intakeId, inta.name AS intakenum, oh.rtu_addr AS rtuAddr, oh.client_name AS clientName, oh.op_ic_card_no AS openIcNum, oh.op_ic_card_addr AS openIcAddr, oh.op_dt AS openTime, CASE WHEN oh.op_type = 1 THEN "刷卡开阀" WHEN oh.op_type = 3 THEN "中心站开阀" WHEN oh.op_type = 5 THEN "欠费关阀" WHEN oh.op_type = 8 THEN "用户远程开阀" WHEN oh.op_type = 11 THEN "开关阀卡开阀" ELSE "未知" END AS openType, oh.op_order_no AS openOrderNo, oh.op_total_amount AS openTotalAmount, oh.op_remain_money AS openRemainMoney, oh.op_water_remain_user AS openWaterRemain, oh.op_ele_total_amount AS openEleTotalAmount, oh.cl_ic_card_no AS closeIcNum, oh.cl_ic_card_addr AS closeIcAddr, oh.cl_dt AS closeTime, CASE WHEN oh.cl_type = 2 THEN "刷卡关阀" WHEN oh.cl_type = 4 THEN "中心站关阀" WHEN oh.cl_type = 5 THEN "欠费关阀" WHEN oh.cl_type = 6 THEN "流量计故障关阀" WHEN oh.cl_type = 7 THEN "紧急关闭" WHEN oh.cl_type = 9 THEN "用户远程关阀" WHEN oh.cl_type = 10 THEN "开关阀卡关阀" WHEN oh.cl_type = 12 THEN "黑名单命令关阀" WHEN oh.cl_type = 13 THEN "用户远程定时关阀" WHEN oh.cl_type = 14 THEN "用户远程定量关阀" ELSE "未知" END AS closeType, oh.cl_this_amount AS closeThisAmount, oh.cl_this_time AS thisTime, oh.cl_this_money AS thisMoney, oh.cl_remain_money AS closeRemainMoney, oh.cl_total_amount AS closeTotalAmount FROM rm_open_close_valve_history oh INNER JOIN pr_intake inta ON inta.id = oh.intake_id <where> <if test="intakeId != null and intakeId >0"> AND oh.intake_id = #{intakeId} </if> <if test = "intakeNum != null and intakeNum !=''"> AND inta.name LIKE CONCAT('%',#{intakeNum},'%') </if> <if test = "rtuAddr != null and rtuAddr !=''"> AND oh.rtu_addr LIKE CONCAT('%',#{rtuAddr},'%') </if> <if test = "timeStart_open != null and timeStart_open != '' and timeStop_open != null and timeStop_open != '' "> AND oh.op_dt BETWEEN #{timeStart_open} AND #{timeStop_open} </if> <if test = "timeStart_close != null and timeStart_close != '' and timeStop_close != null and timeStop_close != '' "> AND oh.cl_dt BETWEEN #{timeStart_close} AND #{timeStop_close} </if> </where> ORDER BY oh.op_dt DESC <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="getNeverOpenValveIntakesCount" resultType="java.lang.Long"> SELECT COUNT(*) AS recordCount FROM pr_intake inta INNER JOIN ba_block blo ON blo.id = inta.blockId WHERE inta.deleted = 0 AND NOT EXISTS(SELECT * FROM rm_open_close_valve_history WHERE op_dt BETWEEN #{timeStart} AND #{timeStop} AND intake_id = inta.id) <!--根据指定条件获取开关阀报历史记录--> <select id="getOpenCloseValveReports_history" resultType="com.dy.pipIrrGlobal.voRm.VoOpenCloseValve"> SELECT oh.intake_id AS intakeId, inta.name AS intakenum, oh.rtu_addr AS rtuAddr, oh.client_name AS clientName, oh.op_ic_card_no AS openIcNum, oh.op_ic_card_addr AS openIcAddr, oh.op_dt AS openTime, CASE WHEN oh.op_type = 1 THEN '刷卡开阀' WHEN oh.op_type = 3 THEN '中心站开阀' WHEN oh.op_type = 5 THEN '欠费关阀' WHEN oh.op_type = 8 THEN '用户远程开阀' WHEN oh.op_type = 11 THEN '开关阀卡开阀' ELSE '未知' END AS openType, oh.op_order_no AS openOrderNo, oh.op_total_amount AS openTotalAmount, oh.op_remain_money AS openRemainMoney, oh.op_water_remain_user AS openWaterRemain, oh.op_ele_total_amount AS openEleTotalAmount, oh.cl_ic_card_no AS closeIcNum, oh.cl_ic_card_addr AS closeIcAddr, oh.cl_dt AS closeTime, CASE WHEN oh.cl_type = 2 THEN '刷卡关阀' WHEN oh.cl_type = 4 THEN '中心站关阀' WHEN oh.cl_type = 5 THEN '欠费关阀' WHEN oh.cl_type = 6 THEN '流量计故障关阀' WHEN oh.cl_type = 7 THEN '紧急关闭' WHEN oh.cl_type = 9 THEN '用户远程关阀' WHEN oh.cl_type = 10 THEN '开关阀卡关阀' WHEN oh.cl_type = 12 THEN '黑名单命令关阀' WHEN oh.cl_type = 13 THEN '用户远程定时关阀' WHEN oh.cl_type = 14 THEN '用户远程定量关阀' ELSE '未知' END AS closeType, oh.cl_this_amount AS closeThisAmount, oh.cl_this_time AS thisTime, oh.cl_this_money AS thisMoney, oh.cl_remain_money AS closeRemainMoney, oh.cl_total_amount AS closeTotalAmount FROM rm_open_close_valve_history oh INNER JOIN pr_intake inta ON inta.id = oh.intake_id <where> <if test="intakeId != null and intakeId > 0"> AND oh.intake_id = #{intakeId} </if> <if test="intakeNum != null and intakeNum != ''"> AND inta.name LIKE CONCAT('%', #{intakeNum}, '%') </if> <if test="rtuAddr != null and rtuAddr != ''"> AND oh.rtu_addr LIKE CONCAT('%', #{rtuAddr}, '%') </if> <if test="timeStart_open != null and timeStart_open != '' and timeStop_open != null and timeStop_open != ''"> AND oh.op_dt BETWEEN #{timeStart_open} AND #{timeStop_open} </if> <if test="timeStart_close != null and timeStart_close != '' and timeStop_close != null and timeStop_close != ''"> AND oh.cl_dt BETWEEN #{timeStart_close} AND #{timeStop_close} </if> </where> ORDER BY oh.op_dt DESC <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="getNeverOpenValveIntakes" resultType="com.dy.pipIrrGlobal.voSt.VoIntake"> SELECT inta.id AS intakeId, inta.name AS intakeNum, blo.name AS blockName FROM pr_intake inta INNER JOIN ba_block blo ON blo.id = inta.blockId WHERE inta.deleted = 0 AND NOT EXISTS(SELECT * FROM rm_open_close_valve_history WHERE op_dt BETWEEN #{timeStart} AND #{timeStop} AND intake_id = inta.id) 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="getOpenValveGtIntakesCount" resultType="java.lang.Long"> select count(*) from (SELECT COUNT(*) AS recordCount, inta.id AS intakeId, inta.name AS intakeNum, blo.name AS blockName FROM pr_intake inta LEFT JOIN (SELECT * FROM rm_open_close_valve_history WHERE op_dt BETWEEN #{timeStart} AND #{timeStop}) his ON his.intake_id = inta.id INNER JOIN ba_block blo ON blo.id = inta.blockId WHERE inta.deleted = 0 GROUP BY intakeId,intakeNum,blockName HAVING recordCount > #{value}) a </select> <!--获取指定时间段内开阀次数超过指定值的取水口--> <select id="getOpenValveGtIntakes" resultType="com.dy.pipIrrGlobal.voSt.VoIntakeOpenCount"> SELECT COUNT(*) AS recordCount, inta.id AS intakeId, inta.name AS intakeNum, blo.name AS blockName FROM pr_intake inta LEFT JOIN (SELECT * FROM rm_open_close_valve_history WHERE op_dt BETWEEN #{timeStart} AND #{timeStop}) his ON his.intake_id = inta.id INNER JOIN ba_block blo ON blo.id = inta.blockId WHERE inta.deleted = 0 GROUP BY intakeId,intakeNum,blockName HAVING recordCount > #{value} 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="getOpenValveLtIntakesCount" resultType="java.lang.Long"> select count(*) from (SELECT COUNT(*) AS recordCount, inta.id AS intakeId, inta.name AS intakeNum, blo.name AS blockName FROM pr_intake inta LEFT JOIN (SELECT * FROM rm_open_close_valve_history WHERE op_dt BETWEEN #{timeStart} AND #{timeStop}) his ON his.intake_id = inta.id INNER JOIN ba_block blo ON blo.id = inta.blockId WHERE inta.deleted = 0 GROUP BY intakeId,intakeNum,blockName HAVING recordCount < #{value}) a </select> <!--获取指定时间段内开阀次数低于指定值的取水口--> <select id="getOpenValveLtIntakes" resultType="com.dy.pipIrrGlobal.voSt.VoIntakeOpenCount"> SELECT COUNT(*) AS recordCount, inta.id AS intakeId, inta.name AS intakeNum, blo.name AS blockName FROM pr_intake inta LEFT JOIN (SELECT * FROM rm_open_close_valve_history WHERE op_dt BETWEEN #{timeStart} AND #{timeStop}) his ON his.intake_id = inta.id INNER JOIN ba_block blo ON blo.id = inta.blockId WHERE inta.deleted = 0 GROUP BY intakeId,intakeNum,blockName HAVING recordCount < #{value} 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="getNeverOpenValveIntakesCount" resultType="java.lang.Long"> SELECT COUNT(*) AS recordCount FROM pr_intake inta INNER JOIN ba_block blo ON blo.id = inta.blockId WHERE inta.deleted = 0 AND NOT EXISTS(SELECT * FROM rm_open_close_valve_history WHERE op_dt BETWEEN #{timeStart} AND #{timeStop} AND intake_id = inta.id) </select> <!--获取指定时间段内从未开过阀的取水口--> <select id="getNeverOpenValveIntakes" resultType="com.dy.pipIrrGlobal.voSt.VoIntake"> SELECT inta.id AS intakeId, inta.name AS intakeNum, blo.name AS blockName FROM pr_intake inta INNER JOIN ba_block blo ON blo.id = inta.blockId WHERE inta.deleted = 0 AND NOT EXISTS(SELECT * FROM rm_open_close_valve_history WHERE op_dt BETWEEN #{timeStart} AND #{timeStop} AND intake_id = inta.id) 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="getOpenValveGtIntakesCount" resultType="java.lang.Long"> select count(*) from (SELECT COUNT(*) AS recordCount, inta.id AS intakeId, inta.name AS intakeNum, blo.name AS blockName FROM pr_intake inta LEFT JOIN (SELECT * FROM rm_open_close_valve_history WHERE op_dt BETWEEN #{timeStart} AND #{timeStop}) his ON his.intake_id = inta.id INNER JOIN ba_block blo ON blo.id = inta.blockId WHERE inta.deleted = 0 GROUP BY intakeId, intakeNum, blockName HAVING recordCount > #{value}) a </select> <!--获取指定时间段内开阀次数超过指定值的取水口--> <select id="getOpenValveGtIntakes" resultType="com.dy.pipIrrGlobal.voSt.VoIntakeOpenCount"> SELECT COUNT(*) AS recordCount, inta.id AS intakeId, inta.name AS intakeNum, blo.name AS blockName FROM pr_intake inta LEFT JOIN (SELECT * FROM rm_open_close_valve_history WHERE op_dt BETWEEN #{timeStart} AND #{timeStop}) his ON his.intake_id = inta.id INNER JOIN ba_block blo ON blo.id = inta.blockId WHERE inta.deleted = 0 GROUP BY intakeId, intakeNum, blockName HAVING recordCount > #{value} <!-- 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="getOpenValveLtIntakesCount" resultType="java.lang.Long"> select count(*) from (SELECT COUNT(*) AS recordCount, inta.id AS intakeId, inta.name AS intakeNum, blo.name AS blockName FROM pr_intake inta LEFT JOIN (SELECT * FROM rm_open_close_valve_history WHERE op_dt BETWEEN #{timeStart} AND #{timeStop}) his ON his.intake_id = inta.id INNER JOIN ba_block blo ON blo.id = inta.blockId WHERE inta.deleted = 0 GROUP BY intakeId, intakeNum, blockName HAVING recordCount < #{value}) a </select> <!--获取指定时间段内开阀次数低于指定值的取水口--> <select id="getOpenValveLtIntakes" resultType="com.dy.pipIrrGlobal.voSt.VoIntakeOpenCount"> SELECT COUNT(*) AS recordCount, inta.id AS intakeId, inta.name AS intakeNum, blo.name AS blockName FROM pr_intake inta LEFT JOIN (SELECT * FROM rm_open_close_valve_history WHERE op_dt BETWEEN #{timeStart} AND #{timeStop}) his ON his.intake_id = inta.id INNER JOIN ba_block blo ON blo.id = inta.blockId WHERE inta.deleted = 0 GROUP BY intakeId, intakeNum, blockName HAVING recordCount < #{value} <!-- 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="getLargeOpenCountClientsCount" resultType="java.lang.Long"> SELECT COUNT(*) AS recordCount FROM se_client cli WHERE (SELECT COUNT(*) FROM rm_open_close_valve_history his WHERE his.client_id = cli.id AND his.op_dt BETWEEN #{timeStart} AND #{timeStop}) > #{openCount} </select> <!--获取指定时间段内开阀次数超过指定值的农户--> <select id="getLargeOpenCountClients" resultType="com.dy.pipIrrGlobal.voSt.VoClient"> SELECT cli.id AS clientId, cli.name AS clientName, cli.clientNum, CONCAT(cli.districtTitle, cli.address) AS address, cli.phone, cli.idCard FROM se_client cli WHERE (SELECT COUNT(*) FROM rm_open_close_valve_history his WHERE his.client_id = cli.id AND his.op_dt BETWEEN #{timeStart} AND #{timeStop}) > #{openCount} ORDER BY cli.id <trim prefix="limit "> <if test="start != null and count != null"> #{start,javaType=Integer,jdbcType=INTEGER}, #{count,javaType=Integer,jdbcType=INTEGER} </if> </trim> </select> </mapper> pipIrr-platform/pipIrr-global/src/main/resources/mapper/RmOpenCloseValveLastMapper.xml
@@ -454,101 +454,99 @@ where id = #{id,jdbcType=BIGINT} </update> <!--根据指定条件获取开关阀报最新记录数量--> <select id="getOpenCloseValveReportsCount_last" resultType="java.lang.Long"> SELECT COUNT(*) AS recordCount FROM rm_open_close_valve_last oh INNER JOIN pr_intake inta ON inta.id = oh.intake_id <where> <if test="intakeId != null and intakeId >0"> AND oh.intake_id = #{intakeId} </if> <if test = "intakeNum != null and intakeNum !=''"> AND inta.name LIKE CONCAT('%',#{intakeNum},'%') </if> <if test = "rtuAddr != null and rtuAddr !=''"> AND oh.rtu_addr LIKE CONCAT('%',#{rtuAddr},'%') </if> <if test = "timeStart_open != null and timeStart_open != '' and timeStop_open != null and timeStop_open != '' "> AND oh.op_dt BETWEEN #{timeStart_open} AND #{timeStop_open} </if> <if test = "timeStart_close != null and timeStart_close != '' and timeStop_close != null and timeStop_close != '' "> AND oh.cl_dt BETWEEN #{timeStart_close} AND #{timeStop_close} </if> </where> </select> <!--根据指定条件获取开关阀报最新记录数量--> <select id="getOpenCloseValveReportsCount_last" resultType="java.lang.Long"> SELECT COUNT(*) AS recordCount FROM rm_open_close_valve_last oh INNER JOIN pr_intake inta ON inta.id = oh.intake_id <where> <if test="intakeId != null and intakeId > 0"> AND oh.intake_id = #{intakeId} </if> <if test="intakeNum != null and intakeNum != ''"> AND inta.name LIKE CONCAT('%', #{intakeNum}, '%') </if> <if test="rtuAddr != null and rtuAddr != ''"> AND oh.rtu_addr LIKE CONCAT('%', #{rtuAddr}, '%') </if> <if test="timeStart_open != null and timeStart_open != '' and timeStop_open != null and timeStop_open != ''"> AND oh.op_dt BETWEEN #{timeStart_open} AND #{timeStop_open} </if> <if test="timeStart_close != null and timeStart_close != '' and timeStop_close != null and timeStop_close != ''"> AND oh.cl_dt BETWEEN #{timeStart_close} AND #{timeStop_close} </if> </where> </select> <!--根据指定条件获取开关阀报最新记录--> <select id="getOpenCloseValveReports_last" resultType="com.dy.pipIrrGlobal.voRm.VoOpenCloseValve"> SELECT oh.intake_id AS intakeId, inta.name AS intakeNum, oh.rtu_addr AS rtuAddr, oh.client_name AS clientName, oh.op_ic_card_no AS openIcNum, oh.op_ic_card_addr AS openIcAddr, oh.op_dt AS openTime, CASE WHEN oh.op_type = 1 THEN "刷卡开阀" WHEN oh.op_type = 3 THEN "中心站开阀" WHEN oh.op_type = 5 THEN "欠费关阀" WHEN oh.op_type = 8 THEN "用户远程开阀" WHEN oh.op_type = 11 THEN "开关阀卡开阀" ELSE "未知" END AS openType, oh.op_order_no AS openOrderNo, oh.op_total_amount AS openTotalAmount, oh.op_remain_money AS openRemainMoney, oh.op_water_remain_user AS openWaterRemain, oh.op_ele_total_amount AS openEleTotalAmount, oh.cl_ic_card_no AS closeIcNum, oh.cl_ic_card_addr AS closeIcAddr, oh.cl_dt AS closeTime, CASE WHEN oh.cl_type = 2 THEN "刷卡关阀" WHEN oh.cl_type = 4 THEN "中心站关阀" WHEN oh.cl_type = 5 THEN "欠费关阀" WHEN oh.cl_type = 6 THEN "流量计故障关阀" WHEN oh.cl_type = 7 THEN "紧急关闭" WHEN oh.cl_type = 9 THEN "用户远程关阀" WHEN oh.cl_type = 10 THEN "开关阀卡关阀" WHEN oh.cl_type = 12 THEN "黑名单命令关阀" WHEN oh.cl_type = 13 THEN "用户远程定时关阀" WHEN oh.cl_type = 14 THEN "用户远程定量关阀" ELSE "未知" END AS closeType, oh.cl_this_amount AS closeThisAmount, oh.cl_this_time AS thisTime, oh.cl_this_money AS thisMoney, oh.cl_remain_money AS closeRemainMoney, oh.cl_total_amount AS closeTotalAmount FROM rm_open_close_valve_last oh INNER JOIN pr_intake inta ON inta.id = oh.intake_id <where> <if test="intakeId != null and intakeId >0"> AND oh.intake_id = #{intakeId} </if> <if test = "intakeNum != null and intakeNum !=''"> AND inta.name LIKE CONCAT('%',#{intakeNum},'%') </if> <if test = "rtuAddr != null and rtuAddr !=''"> AND oh.rtu_addr LIKE CONCAT('%',#{rtuAddr},'%') </if> <if test = "timeStart_open != null and timeStart_open != '' and timeStop_open != null and timeStop_open != '' "> AND oh.op_dt BETWEEN #{timeStart_open} AND #{timeStop_open} </if> <if test = "timeStart_close != null and timeStart_close != '' and timeStop_close != null and timeStop_close != '' "> AND oh.cl_dt BETWEEN #{timeStart_close} AND #{timeStop_close} </if> </where> ORDER BY oh.op_dt DESC <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="getOpenCloseValveReports_last" resultType="com.dy.pipIrrGlobal.voRm.VoOpenCloseValve"> SELECT oh.intake_id AS intakeId, inta.name AS intakeNum, oh.rtu_addr AS rtuAddr, oh.client_name AS clientName, oh.op_ic_card_no AS openIcNum, oh.op_ic_card_addr AS openIcAddr, oh.op_dt AS openTime, CASE WHEN oh.op_type = 1 THEN '刷卡开阀' WHEN oh.op_type = 3 THEN '中心站开阀' WHEN oh.op_type = 5 THEN '欠费关阀' WHEN oh.op_type = 8 THEN '用户远程开阀' WHEN oh.op_type = 11 THEN '开关阀卡开阀' ELSE '未知' END AS openType, oh.op_order_no AS openOrderNo, oh.op_total_amount AS openTotalAmount, oh.op_remain_money AS openRemainMoney, oh.op_water_remain_user AS openWaterRemain, oh.op_ele_total_amount AS openEleTotalAmount, oh.cl_ic_card_no AS closeIcNum, oh.cl_ic_card_addr AS closeIcAddr, oh.cl_dt AS closeTime, CASE WHEN oh.cl_type = 2 THEN '刷卡关阀' WHEN oh.cl_type = 4 THEN '中心站关阀' WHEN oh.cl_type = 5 THEN '欠费关阀' WHEN oh.cl_type = 6 THEN '流量计故障关阀' WHEN oh.cl_type = 7 THEN '紧急关闭' WHEN oh.cl_type = 9 THEN '用户远程关阀' WHEN oh.cl_type = 10 THEN '开关阀卡关阀' WHEN oh.cl_type = 12 THEN '黑名单命令关阀' WHEN oh.cl_type = 13 THEN '用户远程定时关阀' WHEN oh.cl_type = 14 THEN '用户远程定量关阀' ELSE '未知' END AS closeType, oh.cl_this_amount AS closeThisAmount, oh.cl_this_time AS thisTime, oh.cl_this_money AS thisMoney, oh.cl_remain_money AS closeRemainMoney, oh.cl_total_amount AS closeTotalAmount FROM rm_open_close_valve_last oh INNER JOIN pr_intake inta ON inta.id = oh.intake_id <where> <if test="intakeId != null and intakeId > 0"> AND oh.intake_id = #{intakeId} </if> <if test="intakeNum != null and intakeNum != ''"> AND inta.name LIKE CONCAT('%', #{intakeNum}, '%') </if> <if test="rtuAddr != null and rtuAddr != ''"> AND oh.rtu_addr LIKE CONCAT('%', #{rtuAddr}, '%') </if> <if test="timeStart_open != null and timeStart_open != '' and timeStop_open != null and timeStop_open != ''"> AND oh.op_dt BETWEEN #{timeStart_open} AND #{timeStop_open} </if> <if test="timeStart_close != null and timeStart_close != '' and timeStop_close != null and timeStop_close != ''"> AND oh.cl_dt BETWEEN #{timeStart_close} AND #{timeStop_close} </if> </where> ORDER BY oh.op_dt DESC <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="getOpenCloseValveReports_last" resultType="com.dy.pipIrrGlobal.voRm.VoOpenCloseValve"> SELECT oh.intake_id AS intakeId, pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/ClientCtrl.java
New file @@ -0,0 +1,53 @@ package com.dy.pipIrrStatistics.client; import com.dy.common.aop.SsoAop; import com.dy.common.webUtil.BaseResponse; import com.dy.common.webUtil.BaseResponseUtils; import com.dy.common.webUtil.QueryResultVo; import com.dy.pipIrrGlobal.voSt.VoClient; import com.dy.pipIrrStatistics.client.qo.OpenCountQO; import jakarta.validation.Valid; import lombok.RequiredArgsConstructor; import lombok.extern.slf4j.Slf4j; import org.springframework.validation.BindingResult; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.Objects; /** * @author ZhuBaoMin * @date 2024-08-06 9:43 * @LastEditTime 2024-08-06 9:43 * @Description */ @Slf4j @RestController @RequestMapping(path="statistics") @RequiredArgsConstructor public class ClientCtrl { private final ClientSv clientSv; /** * 获取指定时间段内开阀次数超过指定值的农户 * @param qo * @return */ @GetMapping(path = "/getLargeOpenCountClients") @SsoAop() public BaseResponse<QueryResultVo<List<VoClient>>> getLargeOpenCountClients(@Valid OpenCountQO qo, BindingResult bindingResult) { if(bindingResult != null && bindingResult.hasErrors()){ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage()); } try { return BaseResponseUtils.buildSuccess(clientSv.getLargeOpenCountClients(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/client/ClientSv.java
New file @@ -0,0 +1,67 @@ package com.dy.pipIrrStatistics.client; import com.dy.common.webUtil.QueryResultVo; import com.dy.pipIrrGlobal.daoRm.RmOpenCloseValveHistoryMapper; import com.dy.pipIrrGlobal.voSt.VoClient; import com.dy.pipIrrStatistics.client.qo.OpenCountQO; import lombok.extern.slf4j.Slf4j; import org.apache.dubbo.common.utils.PojoUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.time.LocalDate; import java.util.List; import java.util.Map; import java.util.Optional; /** * @author ZhuBaoMin * @date 2024-08-06 9:43 * @LastEditTime 2024-08-06 9:43 * @Description */ @Slf4j @Service public class ClientSv { @Autowired private RmOpenCloseValveHistoryMapper rmOpenCloseValveHistoryMapper; /** * 获取指定时间段内开阀次数超过指定值的农户 * @param qo * @return */ public QueryResultVo<List<VoClient>> getLargeOpenCountClients(OpenCountQO qo) { /** * 补齐起止时间，如果开始时间为空，则默认为当前日期 */ String timeStart = qo.getTimeStart(); String timeStop = qo.getTimeStop(); if(timeStart != null) { timeStart = timeStart + " 00:00:00"; }else { timeStart = LocalDate.now() + " 00:00:00"; } if(timeStop != null) { timeStop = timeStop + " 23:59:59"; } qo.setTimeStart(timeStart); qo.setTimeStop(timeStop); // 生成查询参数 Map<String, Object> params = (Map<String, Object>) PojoUtils.generalize(qo) ; // 获取符合条件的记录数 Long itemTotal = Optional.ofNullable(rmOpenCloseValveHistoryMapper.getLargeOpenCountClientsCount(params)).orElse(0L); QueryResultVo<List<VoClient>> rsVo = new QueryResultVo<>() ; rsVo.pageSize = qo.pageSize ; rsVo.pageCurr = qo.pageCurr ; rsVo.calculateAndSet(itemTotal, params); rsVo.obj = rmOpenCloseValveHistoryMapper.getLargeOpenCountClients(params); return rsVo ; } } pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/qo/CloseCountQO.java
New file @@ -0,0 +1,22 @@ package com.dy.pipIrrStatistics.client.qo; import com.dy.pipIrrStatistics.intake.qo.CommonQO; import jakarta.validation.constraints.NotNull; import lombok.Data; /** * @author ZhuBaoMin * @date 2024-08-06 9:58 * @LastEditTime 2024-08-06 9:58 * @Description 关阀次数查询对象 */ @Data public class CloseCountQO extends CommonQO { /** * 关阀次数 */ @NotNull(message = "关阀次数不能为空") private Integer closeCount; } pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/qo/OpenCountQO.java
New file @@ -0,0 +1,22 @@ package com.dy.pipIrrStatistics.client.qo; import com.dy.pipIrrStatistics.intake.qo.CommonQO; import jakarta.validation.constraints.NotNull; import lombok.Data; /** * @author ZhuBaoMin * @date 2024-08-06 9:55 * @LastEditTime 2024-08-06 9:55 * @Description 开阀次数查询对象 */ @Data public class OpenCountQO extends CommonQO { /** * 开阀次数 */ @NotNull(message = "开阀次数不能为空") private Integer openCount; } pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/intake/IntakeSv.java
@@ -14,7 +14,7 @@ import com.dy.pipIrrGlobal.voSt.VoSignalIntensity; import com.dy.pipIrrStatistics.intake.qo.BatteryVoltQO; import com.dy.pipIrrStatistics.intake.qo.CumulativeFlowQO; import com.dy.pipIrrStatistics.intake.qo.IntakeQO; import com.dy.pipIrrStatistics.intake.qo.CommonQO; import com.dy.pipIrrStatistics.intake.qo.IntakeValueQO; import com.dy.pipIrrStatistics.intake.qo.SignalIntensityQO; import lombok.extern.slf4j.Slf4j; @@ -63,7 +63,7 @@ * @param qo * @return */ public QueryResultVo<List<VoIntake>> getNotOnlineIntakes(IntakeQO qo) { public QueryResultVo<List<VoIntake>> getNotOnlineIntakes(CommonQO qo) { DecimalFormat df = new DecimalFormat("0.00"); /** * 补齐起止时间，如果开始时间为空，则默认为当前日期 @@ -193,7 +193,7 @@ * 获取从未开过阀的取水口 * @return */ public QueryResultVo<List<VoIntake>> getNeverOpenValveIntakes(IntakeQO qo) { public QueryResultVo<List<VoIntake>> getNeverOpenValveIntakes(CommonQO qo) { String timeStart = qo.getTimeStart(); String timeStop = qo.getTimeStop(); pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/intake/IntkeCtrl.java
@@ -6,24 +6,17 @@ import com.dy.common.webUtil.QueryResultVo; import com.dy.pipIrrGlobal.voSt.*; import com.dy.pipIrrStatistics.intake.qo.*; import com.dy.pipIrrGlobal.voSt.VoBatteryVolt; import com.dy.pipIrrGlobal.voSt.VoCumulativeFlow; import com.dy.pipIrrGlobal.voSt.VoIntake; import com.dy.pipIrrGlobal.voSt.VoSignalIntensity; import com.dy.pipIrrStatistics.intake.qo.BatteryVoltQO; import com.dy.pipIrrGlobal.voSt.VoIntakeOpenCount; import com.dy.pipIrrStatistics.intake.qo.CumulativeFlowQO; import com.dy.pipIrrStatistics.intake.qo.IntakeQO; import com.dy.pipIrrStatistics.intake.qo.SignalIntensityQO; import com.dy.pipIrrStatistics.intake.qo.IntakeValueQO; import com.dy.pipIrrStatistics.result.StatisticlResultCode; import jakarta.validation.Valid; import lombok.RequiredArgsConstructor; import lombok.extern.slf4j.Slf4j; import org.springframework.validation.BindingResult; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.Objects; /** * @author ZhuBaoMin @@ -46,7 +39,11 @@ */ @GetMapping(path = "/getNotOnlineIntakes") @SsoAop() public BaseResponse<QueryResultVo<List<VoIntake>>> getNotOnlineIntakes(IntakeQO qo) { public BaseResponse<QueryResultVo<List<VoIntake>>> getNotOnlineIntakes(@Valid CommonQO qo, BindingResult bindingResult) { if(bindingResult != null && bindingResult.hasErrors()){ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage()); } try { return BaseResponseUtils.buildSuccess(intakeSv.getNotOnlineIntakes(qo)); } catch (Exception e) { @@ -62,7 +59,10 @@ */ @GetMapping(path = "/getLargeFlowIntakes") @SsoAop() public BaseResponse<QueryResultVo<List<VoCumulativeFlow>>> getLargeFlowIntakes(CumulativeFlowQO qo) { public BaseResponse<QueryResultVo<List<VoCumulativeFlow>>> getLargeFlowIntakes(@Valid CumulativeFlowQO qo, BindingResult bindingResult) { if(bindingResult != null && bindingResult.hasErrors()){ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage()); } try { return BaseResponseUtils.buildSuccess(intakeSv.getLargeFlowIntakes(qo)); } catch (Exception e) { @@ -78,7 +78,10 @@ */ @GetMapping(path = "/getSmallFlowIntakes") @SsoAop() public BaseResponse<QueryResultVo<List<VoCumulativeFlow>>> getSmallFlowIntakes(CumulativeFlowQO qo) { public BaseResponse<QueryResultVo<List<VoCumulativeFlow>>> getSmallFlowIntakes(@Valid CumulativeFlowQO qo, BindingResult bindingResult) { if(bindingResult != null && bindingResult.hasErrors()){ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage()); } try { return BaseResponseUtils.buildSuccess(intakeSv.getSmallFlowIntakes(qo)); } catch (Exception e) { @@ -94,7 +97,10 @@ */ @GetMapping(path = "/getUnderVoltIntakes") @SsoAop() public BaseResponse<QueryResultVo<List<VoBatteryVolt>>> getUnderVoltIntakes(BatteryVoltQO qo) { public BaseResponse<QueryResultVo<List<VoBatteryVolt>>> getUnderVoltIntakes(@Valid BatteryVoltQO qo, BindingResult bindingResult) { if(bindingResult != null && bindingResult.hasErrors()){ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage()); } try { return BaseResponseUtils.buildSuccess(intakeSv.getUnderVoltIntakes(qo)); } catch (Exception e) { @@ -110,7 +116,10 @@ */ @GetMapping(path = "/getSpecifiedSignalIntakes") @SsoAop() public BaseResponse<QueryResultVo<List<VoSignalIntensity>>> getSpecifiedSignalIntakes(SignalIntensityQO qo) { public BaseResponse<QueryResultVo<List<VoSignalIntensity>>> getSpecifiedSignalIntakes(@Valid SignalIntensityQO qo, BindingResult bindingResult) { if(bindingResult != null && bindingResult.hasErrors()){ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage()); } try { return BaseResponseUtils.buildSuccess(intakeSv.getSpecifiedSignalIntakes(qo)); } catch (Exception e) { @@ -126,7 +135,10 @@ */ @GetMapping(path = "/getCountByOpenType") @SsoAop() public BaseResponse<QueryResultVo<VoCountOfOpenType>> getCountByOpenType(OpenTypeQO qo) { public BaseResponse<QueryResultVo<VoCountOfOpenType>> getCountByOpenType(@Valid OpenTypeQO qo, BindingResult bindingResult) { if(bindingResult != null && bindingResult.hasErrors()){ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage()); } try { return BaseResponseUtils.buildSuccess(intakeSv.getCountByOpenType(qo)); } catch (Exception e) { @@ -142,7 +154,10 @@ */ @GetMapping(path = "/getCountByCloseType") @SsoAop() public BaseResponse<QueryResultVo<VoCountOfCloseType>> getCountByCloseType(CloseTypeQo qo) { public BaseResponse<QueryResultVo<VoCountOfCloseType>> getCountByCloseType(@Valid CloseTypeQo qo, BindingResult bindingResult) { if(bindingResult != null && bindingResult.hasErrors()){ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage()); } try { return BaseResponseUtils.buildSuccess(intakeSv.getCountByCloseType(qo)); } catch (Exception e) { @@ -159,7 +174,7 @@ */ @GetMapping(path = "/getNeverOpenValveIntakes") @SsoAop() public BaseResponse<QueryResultVo<List<VoIntake>>> getNeverOpenValveIntakes(IntakeQO qo) { public BaseResponse<QueryResultVo<List<VoIntake>>> getNeverOpenValveIntakes(CommonQO qo) { try { QueryResultVo<List<VoIntake>> res = intakeSv.getNeverOpenValveIntakes(qo); if(res.itemTotal == 0) { pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/intake/qo/CommonQO.java
File was renamed from pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/intake/qo/IntakeQO.java @@ -13,7 +13,7 @@ */ @Data public class IntakeQO extends QueryConditionVo { public class CommonQO extends QueryConditionVo { /** * 开始时间 pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/intake/qo/IntakeValueQO.java
@@ -1,7 +1,5 @@ package com.dy.pipIrrStatistics.intake.qo; import com.dy.common.webUtil.QueryConditionVo; import com.fasterxml.jackson.annotation.JsonFormat; import jakarta.validation.constraints.NotBlank; import lombok.Data; @@ -12,7 +10,7 @@ * @Description */ @Data public class IntakeValueQO extends IntakeQO { public class IntakeValueQO extends CommonQO { /**
