package cn.cetc54.platform.zhyl.mapper;

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>")
    List<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>" +
            " </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 sum desc"+
            " </script>")
    List<Map<String,Object>> getTypeMoney(@Param(value = "areaId")String areaId);

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

}