<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yami.shop.dao.PointsRecordMapper">

    <select id="statisticsPoint" resultType="java.lang.Long">
        SELECT SUM(
                       CASE
                           WHEN points_audit = 1 THEN points
                           WHEN points_audit = 2 THEN points - variable_points
                           END
               ) AS total_available_points
        FROM tz_points_record
        WHERE user_id = #{userId}
          AND channel_id = #{platform}
          AND points_type IN (1, 3)
          AND points_audit IN (1, 2)
          AND (expiry_date > NOW() OR expiry_date IS NULL)
    </select>

      <select id="statisticsAllScorePoint" resultType="com.yami.shop.bean.dto.ScoreDataDto">
          SELECT
              (
                  SUM(CASE WHEN points_type = 1 THEN points ELSE 0 END)
                      - SUM(
                          CASE WHEN points_audit IN (1, 2) AND expiry_date &lt;= NOW() THEN
                                   CASE
                                       WHEN points_audit = 1 THEN
                                           CASE points_type
                                               WHEN 1 THEN points
                                               WHEN 3 THEN points
                                               ELSE 0
                                               END
                                       WHEN points_audit = 2 THEN
                                           CASE points_type
                                               WHEN 1 THEN points - variable_points
                                               WHEN 3 THEN points
                                               END
                                       END
                               ELSE 0 END
                        )
                      - SUM(CASE WHEN points_type = 2 THEN variable_points ELSE 0 END)
                      + SUM(CASE WHEN points_type = 3 THEN points ELSE 0 END)
                  ) AS total_available_points,
              SUM(CASE WHEN points_type = 1 THEN points ELSE 0 END) AS total_recharged_points,
              SUM(
                      CASE WHEN points_audit IN (1, 2) AND expiry_date &lt;= NOW() THEN
                               CASE
                                   WHEN points_audit = 1 THEN
                                       CASE points_type
                                           WHEN 1 THEN points
                                           WHEN 3 THEN points
                                           ELSE 0
                                           END
                                   WHEN points_audit = 2 THEN
                                       CASE points_type
                                           WHEN 1 THEN points - variable_points
                                           WHEN 3 THEN points
                                           END
                                   END
                           ELSE 0 END
              ) AS expired_points,
              (SUM(CASE WHEN points_type = 2 THEN variable_points ELSE 0 END) - SUM(CASE WHEN points_type = 3 THEN points ELSE 0 END)) AS consumed_points,
              SUM(CASE WHEN points_type = 3 THEN points ELSE 0 END) AS refund_points
          FROM
              tz_points_record
          WHERE
          user_id = #{userId}
          AND channel_id = #{platform}
          AND points_type IN (1, 2, 3)
      </select>

</mapper>
