<?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 = #{channelId}
          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 OR points_type = 3 THEN points ELSE 0 END)
          -
          GREATEST(
          SUM(
          CASE WHEN expiry_date &lt;= NOW() THEN
          CASE
          WHEN points_type = 1 OR points_type = 3 THEN points
          WHEN points_type = 2 THEN -variable_points
          ELSE 0
          END
          ELSE 0 END
          ),
          0
          )
          - SUM(CASE WHEN points_type = 2 THEN variable_points ELSE 0 END)
          ) AS total_available_points,
          SUM(CASE WHEN points_type = 1 THEN points ELSE 0 END) AS total_recharged_points,
          GREATEST(
          SUM(
          CASE WHEN expiry_date &lt;= NOW() THEN
          CASE
          WHEN points_type = 1 OR points_type = 3 THEN points
          WHEN points_type = 2 THEN -variable_points
          ELSE 0
          END
          ELSE 0 END
          ),
          0
          ) 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 = #{channelId}
          AND points_type IN (1, 2, 3)
      </select>
    <select id="findByOrderNumber" resultType="com.yami.shop.bean.model.PointsRecord">
        select id,user_id,channel_id,order_number,
               code,parent_code,points,variable_points,points_type,points_audit,expiry_date,creation_date,currently_available_points,points_id,
               CASE
               WHEN expiry_date >= NOW() THEN 1
               ELSE 2 END AS expiryStatus
        from tz_points_record where order_number = #{orderNumber} and points_type =#{pointsType} order by  expiry_date desc
    </select>
    <select id="findPage" resultType="com.yami.shop.bean.model.PointsRecord">
        select * from (
        select id,user_id,channel_id,order_number,points_id,`code`,parent_code,points,variable_points,points_type,points_audit,expiry_date,creation_date,currently_available_points from tz_points_record where channel_id = #{channelId}  and user_id = #{userId}
        UNION ALL
        SELECT id,user_id,channel_id,order_number,points_id,`code`,parent_code,points,variable_points, 4 as points_type,points_audit,expiry_date,creation_date,currently_available_points from tz_points_record where channel_id = #{channelId}  and user_id = #{userId} and expiry_date &lt;=creation_date and points>0 and points_type =3
        ) a
        order by a.creation_date DESC,a.order_number

    </select>
    <select id="findByExpiredPoints" resultType="com.yami.shop.bean.model.PointsRecord">
        select  * from   (
                             SELECT id,user_id,channel_id,null as order_number,points_id,`code`,parent_code,
                                    (GREATEST(
                                             SUM(
                                                     CASE WHEN expiry_date =#{today} and creation_date &lt; #{today} THEN
                                                              CASE
                                                                  WHEN points_type = 1 OR points_type = 3 THEN points
                                                                  WHEN points_type = 2 THEN -variable_points
                                                                  ELSE 0
                                                                  END
                                                          ELSE 0 END
                                                 ),
                                             0
                                         ) -- 今天之前已过期的  =0
                                        - GREATEST(
                                             SUM(
                                                     CASE WHEN expiry_date = #{yesterday}  and creation_date &lt; #{yesterday} THEN
                                                              CASE
                                                                  WHEN points_type = 1 OR points_type = 3 THEN points
                                                                  WHEN points_type = 2 THEN -variable_points
                                                                  ELSE 0
                                                                  END
                                                          ELSE 0 END
                                                 ),
                                             0
                                         ) -- 昨天之前已过期的
                                        -
                                     GREATEST(
                                             SUM(CASE WHEN points_type = 3 and  creation_date &gt;= #{yesterday}  and creation_date &lt; #{today} and expiry_date = #{yesterday}  THEN points ELSE 0 END),
                                             0
                                         ) -- 昨天过期退款的金额
                                        ) as
                                        points,variable_points, 4 as points_type,points_audit,expiry_date,expiry_date as creation_date,currently_available_points from tz_points_record   where   expiry_date =#{today}  GROUP BY user_id ,channel_id
                         ) a where a.points>0
    </select>
    <select id="backendPointsRecordList" resultType="com.yami.shop.bean.vo.PointsRecordVo">
        SELECT a.`code`, b.real_name,b.user_mobile mobile ,IF(a.points_type in(1,3),CONCAT('+',a.variable_points),CONCAT('-',a.variable_points)) variable_points,a.currently_available_points,a.creation_date,
               CASE
                   WHEN a.points_type = 1 THEN '充值'
                   WHEN a.points_type = 2 THEN '下单'
                   WHEN a.points_type = 3 THEN '退款'
                   WHEN a.points_type = 4 THEN '过期'
                   ELSE '其他'
                   END AS business_type
        FROM `tz_points_record` a
                 LEFT JOIN tz_user b on a.user_id=b.user_id
        <where>
            <if test="po.mobile != null and po.mobile != ''">
                AND b.user_mobile LIKE CONCAT("%",#{po.mobile},"%")
            </if>
            <if test="po.startTime != null and po.startTime != ''">
                and a.creation_date&gt;= #{po.startTime}
            </if>
            <if test="po.endTime != null and po.endTime != ''">
                and a.creation_date&lt;= #{po.endTime}
            </if>
            <if test="po.businessType != null and po.businessType != ''">
                <if test="po.businessType == 'CZ'">
                    AND a.points_type = 1
                </if>
                <if test="po.businessType == 'XD'">
                    AND a.points_type = 2
                </if>
                <if test="po.businessType == 'TK'">
                    AND a.points_type = 3
                </if>
                <if test="po.businessType == 'GQ'">
                    AND a.points_type = 4
                </if>
            </if>
        </where>
        order by a.creation_date desc
    </select>

</mapper>
