修改用水户日、月、年用水量统计查询中的bug

liurunyu

12 小时以前 bdb2f52b04d53f4e0faaa384238244f253b5334f

修改用水户日、月、年用水量统计查询中的bug

 pipIrr-platform/pipIrr-global/src/main/java/com/dy/pipIrrGlobal/daoSt/StClientAmountDayMapper.java

@@ -4,6 +4,7 @@
import com.dy.pipIrrGlobal.pojoSt.StClientAmountDay;
import com.dy.pipIrrGlobal.voSt.VoClientAmountStatistics;
import com.dy.pipIrrGlobal.voSt.VoStClientAmountDay;
import com.dy.pipIrrGlobal.voSt.VoStClientIdAmountYearRecord;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@@ -74,6 +75,9 @@
     * @return
     */
    Long selectCountDayStatistics(Map<?, ?> params) ;

    List<VoStClientIdAmountYearRecord> selectClientIds4DayStatistics(Map<?, ?> params) ;

    /**
     * 查询分页数据
     * @param params

 pipIrr-platform/pipIrr-global/src/main/java/com/dy/pipIrrGlobal/daoSt/StClientAmountMonthMapper.java

@@ -3,6 +3,7 @@
import com.dy.pipIrrGlobal.pojoSt.StClientAmountMonth;
import com.dy.pipIrrGlobal.voSt.VoClientAmountStatistics;
import com.dy.pipIrrGlobal.voSt.VoStClientAmountMonth;
import com.dy.pipIrrGlobal.voSt.VoStClientIdAmountYearRecord;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@@ -74,6 +75,9 @@
     * @return
     */
    Long selectCountMonthStatistics(Map<?, ?> params) ;

    List<VoStClientIdAmountYearRecord> selectClientIds4MonthStatistics(Map<?, ?> params) ;

    /**
     * 查询分页数据
     * @param params

 pipIrr-platform/pipIrr-global/src/main/resources/mapper/StClientAmountDayMapper.xml

@@ -1214,27 +1214,14 @@
    </trim>
  </select>

  <select id="selectDayStatistics" resultType="com.dy.pipIrrGlobal.voSt.VoStClientAmountDay">
  <select id="selectClientIds4DayStatistics" resultType="com.dy.pipIrrGlobal.voSt.VoStClientIdAmountYearRecord">
    select
    mtb.id as clientId,
    mtb.clientNum as clientNum,
    mtb.name as clientName,
    mtb.address as clientAddress,
    <include refid="Base_Column_List_with_alias" >
      <property name="alias" value="stTb"/>
    </include>
    mtb.id as clientId
    from se_client mtb
    LEFT JOIN st_client_amount_day stTb on stTb.client_id = mtb.id
    where mtb.deleted != 1
    <trim prefix="and" suffixOverrides="and">
      <if test = "year != null">
        stTb.year = #{year, jdbcType=INTEGER}
      </if>
      <if test = "month != null">
        AND stTb.month = #{month, jdbcType=INTEGER}
      </if>
      <if test="name != null and name != ''">
        AND mtb.name like concat('%', #{name}, '%')
        mtb.name like concat('%', #{name}, '%')
      </if>
    </trim>
    order by mtb.id DESC
@@ -1245,7 +1232,51 @@
    </trim>
  </select>

  <!-- ////////////////////////////////// -->

  <select id="selectDayStatistics" resultType="com.dy.pipIrrGlobal.voSt.VoStClientAmountDay">
    select
    mtb.id as clientId,
    mtb.clientNum as clientNum,
    mtb.name as clientName,
    mtb.address as clientAddress,
    <include refid="Base_Column_List_with_alias" >
      <property name="alias" value="stTb"/>
    </include>
    from se_client mtb
    <if test="clientIdsJson != null and clientIdsJson !=''">
      INNER JOIN JSON_TABLE(
      <!--'[{"clientId":"37142501020100215"},{"clientId":"37142501020100215"}]'-->
      #{clientIdsJson},
      '$[*]' COLUMNS (
      clientId BIGINT PATH '$.clientId'
      )
      ) clientIdTb ON clientIdTb.clientId = mtb.id
    </if>
    LEFT JOIN
    (
      select *
      from st_client_amount_day
      <where>
        <if test="year != null">
          `year` = #{year,jdbcType=INTEGER}
        </if>
        <if test = "month != null">
          AND month = #{month, jdbcType=INTEGER}
        </if>
      </where>
    ) as stTb on stTb.client_id = mtb.id
    where mtb.deleted != 1
    order by mtb.id DESC
  <!-- 2025-07-14 因为有clientIdsJson限制了数量，所以不再用limit了
  <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="statisticsByClient" resultType="com.dy.pipIrrGlobal.voSt.VoClientAmountStatistics">

 pipIrr-platform/pipIrr-global/src/main/resources/mapper/StClientAmountMonthMapper.xml

@@ -528,24 +528,14 @@
    </trim>
  </select>

  <select id="selectMonthStatistics" resultType="com.dy.pipIrrGlobal.voSt.VoStClientAmountMonth">
  <select id="selectClientIds4MonthStatistics" resultType="com.dy.pipIrrGlobal.voSt.VoStClientIdAmountYearRecord">
    select
    mtb.clientNum as clientNum,
    mtb.name as clientName,
    mtb.phone as clientPhone,
    mtb.address as clientAddress,
    <include refid="Base_Column_List_with_alias" >
      <property name="alias" value="stTb"/>
    </include>
    mtb.id as clientId
    from se_client mtb
    LEFT JOIN st_client_amount_month stTb on stTb.client_id = mtb.id
    where mtb.deleted != 1
    <trim prefix="and" suffixOverrides="and">
      <if test = "year != null">
        stTb.year = #{year, jdbcType=INTEGER}
      </if>
      <if test="name != null and name != ''">
        AND mtb.name like concat('%', #{name}, '%')
        mtb.name like concat('%', #{name}, '%')
      </if>
    </trim>
    order by mtb.id DESC
@@ -556,8 +546,48 @@
    </trim>
  </select>

  <select id="selectMonthStatistics" resultType="com.dy.pipIrrGlobal.voSt.VoStClientAmountMonth">
    select
    mtb.clientNum as clientNum,
    mtb.name as clientName,
    mtb.phone as clientPhone,
    mtb.address as clientAddress,
    <include refid="Base_Column_List_with_alias" >
      <property name="alias" value="stTb"/>
    </include>
    from se_client mtb
    <if test="clientIdsJson != null and clientIdsJson !=''">
      INNER JOIN JSON_TABLE(
      <!--'[{"clientId":"37142501020100215"},{"clientId":"37142501020100215"}]'-->
      #{clientIdsJson},
      '$[*]' COLUMNS (
      clientId BIGINT PATH '$.clientId'
      )
      ) clientIdTb ON clientIdTb.clientId = mtb.id
    </if>
    LEFT JOIN
    (
      select *
      from st_client_amount_month
      <where>
        <if test="year != null">
          `year` = #{year,jdbcType=INTEGER}
        </if>
      </where>
    ) as stTb on stTb.client_id = mtb.id
    where mtb.deleted != 1
    order by mtb.id DESC
  <!-- 2025-07-14 因为有clientIdsJson限制了数量，所以不再用limit了
  <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="selectByYear" resultMap="BaseResultMap">

 pipIrr-platform/pipIrr-global/src/main/resources/mapper/StClientAmountYearMapper.xml

@@ -166,9 +166,10 @@
    LEFT JOIN
    (
      select *
      from st_client_amount_year cayTb_
      from st_client_amount_year
      <where>
        <if test="year != null">(cayTb_.`year` = #{year,jdbcType=INTEGER})
        <if test="year != null">
          `year` = #{year,jdbcType=INTEGER}
        </if>
      </where>
    ) as cayTb
@@ -182,7 +183,7 @@
       -->
    </trim>
    order by ctb.id DESC
    <!-- 2025-07-14 因为有clientIdsJson限制的数量，所以不再用limit了
    <!-- 2025-07-14 因为有clientIdsJson限制了数量，所以不再用limit了
    <trim prefix="limit " >
      <if test="start != null and count != null">
        #{start,javaType=Integer,jdbcType=INTEGER}, #{count,javaType=Integer,jdbcType=INTEGER}

 pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/stClient/StClientSv.java

@@ -60,6 +60,10 @@
            rsVo.pageCurr = qo.pageCurr;
            rsVo.calculateAndSet(itemTotal, params);

            List<VoStClientIdAmountYearRecord> clientIds = stClientAmountDayDao.selectClientIds4DayStatistics(params);
            String clientIdsJson = JSON.toJSONString(clientIds) ;
            params.put("clientIdsJson", clientIdsJson);

            rsVo.obj = stClientAmountDayDao.selectDayStatistics(params);
        }
        return rsVo ;
@@ -83,6 +87,10 @@
            rsVo.pageCurr = qo.pageCurr;
            rsVo.calculateAndSet(itemTotal, params);

            List<VoStClientIdAmountYearRecord> clientIds = stClientAmountMonthDao.selectClientIds4MonthStatistics(params);
            String clientIdsJson = JSON.toJSONString(clientIds) ;
            params.put("clientIdsJson", clientIdsJson);

            rsVo.obj = stClientAmountMonthDao.selectMonthStatistics(params);
        }
        return rsVo ;
@@ -105,15 +113,7 @@
        // 生成查询参数
        Map<String, Object> params = (Map<String, Object>) PojoUtils.generalize(qo) ;
        // 获取符合条件的记录数
        Long itemTotal = 0L ;
        String clientIdsJson = null ;
        /* 2025-07-14 采用农户总数
        if(oneYear){
            itemTotal = stClientAmountYearDao.selectCountYearStatistics4OneYear(params) ;
        }else{
            itemTotal = stClientAmountYearDao.selectCountYearStatistics(params) ;
        }*/
        itemTotal = stClientAmountYearDao.selectCountYearStatistics(params) ;
        Long itemTotal = stClientAmountYearDao.selectCountYearStatistics(params) ;

        if(itemTotal != null && itemTotal > 0) {
            rsQrVo.pageSize = qo.pageSize;
@@ -121,7 +121,7 @@
            rsQrVo.calculateAndSet(itemTotal, params);

            List<VoStClientIdAmountYearRecord> clientIds = stClientAmountYearDao.selectClientIds4YearStatistics(params);
            clientIdsJson = JSON.toJSONString(clientIds) ;
            String clientIdsJson = JSON.toJSONString(clientIds) ;

            List<VoStClientAmountYearRecords> group = new ArrayList<>();
            int count = 1 ;
@@ -129,16 +129,6 @@
                params.put("year", year);
                params.put("clientIdsJson", clientIdsJson);
                List<VoStClientAmountYearRecord> list = stClientAmountYearDao.selectYearStatistics(params);
                /* 2025-07-14
                if(oneYear){
                    list = stClientAmountYearDao.selectYearStatistics4OneYear(params);
                }else{
                    if(clientIdsJson != null){
                        params.put("clientIdsJson", clientIdsJson);
                    }
                    list = stClientAmountYearDao.selectYearStatistics(params);
                }*/

                if(group.isEmpty()){
                    this.completion(group, list);
                }