补贴页面相关接口

xuefei

2020-12-11 299918864d460132dfcb210007dfb9564be15f04

 platform-modules/platform-zhyl/src/main/java/cn/cetc54/platform/zhyl/mapper/SubsidyLogMapper.java

@@ -2,13 +2,132 @@

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import cn.cetc54.platform.zhyl.entity.SubsidyLog;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;


/**
 * 补贴日志数据处理层
 * @author 
 */
public interface SubsidyLogMapper extends BaseMapper<SubsidyLog> {
    //获取补贴总人数
    @Select("<script>" +
            " select SUM(duixiang_id) from t_yl_subsidy_log" +
            " <where> " +
            " <if test='areaId!= null'>" +
            " and area_id = #{areaId}" +
            " </if>" +
            " <if test='type!= null'>" +
            " and type = #{type}" +
            " </if>" +
            " </where>" +
            " GROUP BY duixiang_id"+
            " </script>")
    Integer getTotalNum(@Param(value = "areaId")String areaId, @Param(value = "type")Integer type);

    /**
     * 获取总金额
     * @param areaId
     * @param type
     * @return
     */
    @Select("<script>" +
            " select SUM(money) from t_yl_subsidy_log" +
            " <where> " +
            " <if test='areaId!= null'>" +
            " and area_id = #{areaId}" +
            " </if>" +
            " <if test='type!= null'>" +
            " and type = #{type}" +
            " </if>" +
            " </where>" +
            " GROUP BY duixiang_id"+
            " </script>")
    Double getTotalMoney(@Param(value = "areaId")String areaId, @Param(value = "type")Integer type);

    /**
     * 获取月度统计
     * @param areaId
     * @param type
     * @return
     * SELECT
     *    CONCAT(YEAR(create_time),'-',MONTH(create_time)) AS releaseYearMonth,
     *     SUM(money) as sum
     * FROM t_yl_subsidy_log
     * GROUP BY releaseYearMonth ORDER BY releaseYearMonth desc LIMIT 12
     */
    @Select("<script>" +
            " select CONCAT(YEAR(create_time),'-',MONTH(create_time)) AS time," +
            "SUM(money) as sum FROM t_yl_subsidy_log" +
            " <where> " +
            " <if test='areaId!= null'>" +
            " and area_id = #{areaId}" +
            " </if>" +
            " <if test='type!= null'>" +
            " and type = #{type}" +
            " </if>" +
            " </where>" +
            " GROUP BY time ORDER BY time desc LIMIT 12"+
            " </script>")
    List<Map<String,Object>> getMonthTotal(@Param(value = "areaId")String areaId, @Param(value = "type")Integer type);

    /**
     * 获取年度统计
     * @param areaId
     * @param type
     * @return
     */
    @Select("<script>" +
            " select CONCAT(YEAR(create_time)) AS time," +
            "SUM(money) as sum FROM t_yl_subsidy_log" +
            " <where> " +
            " <if test='areaId!= null'>" +
            " and area_id = #{areaId}" +
            " </if>" +
            " <if test='type!= null'>" +
            " and type = #{type}" +
            " </if>" +
            " </where>" +
            " GROUP BY time ORDER BY time desc LIMIT 12"+
            " </script>")
    List<Map<String,Object>> getYearTotal(@Param(value = "areaId")String areaId, @Param(value = "type")Integer type);

    /**
     *统计每种补贴的金额数
     * @param areaId
     * @return
     */
    @Select("<script>" +
            " select type," +
            "SUM(money) as sum FROM t_yl_subsidy_log" +
            " <where> " +
            " <if test='areaId!= null'>" +
            " and area_id = #{areaId}" +
            " </if>" +
            " </where>" +
            " GROUP BY type ORDER BY money desc"+
            " </script>")
    List<Map<String,Object>> getTypeMoney(@Param(value = "areaId")String areaId);

    /**
     * 每种补贴的次数
     * @param areaId
     * @return
     */
    @Select("<script>" +
            " select type," +
            "conunt(id) as num FROM t_yl_subsidy_log" +
            " <where> " +
            " <if test='areaId!= null'>" +
            " and area_id = #{areaId}" +
            " </if>" +
            " </where>" +
            " GROUP BY type ORDER BY num desc"+
            " </script>")
    List<Map<String,Object>> getTypeNum(@Param(value = "areaId")String areaId);

}