<?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.iamberry.wechat.service.mapper.UseIntegralMapper">
    <sql id="AllUseField">
        use_integral_id useIntegralId,
        user_openid userOpenId,
        use_integral_type useIntegralType,
        use_integral_number useIntegralNumber,
        use_type useType,
        use_config_id useConfigId,
        use_integral_remarks useIntegralRemarks,
        use_integral_create_time useIntegralCreateTime,
        integral_before_number integralBeforeNumber,
        integral_end_number integralEndNumber
    </sql>
    <!--全部积分记录-->
    <select id="listIntegralInfo" resultType="UseIntegral" parameterType="UseIntegral">
        SELECT
          <include refid="AllUseField"></include>,
          c.integral_config_name integralConfigName
        FROM
          tb_iamberry_use_integral i
        LEFT JOIN tb_iamberry_integral_config c ON i.use_config_id = c.integral_config_id
        WHERE i.user_openid=#{userOpenId}
            <if test="useType != null||useType != ''">
               AND i.use_type = #{useType}
            </if>
        ORDER BY i.use_integral_id DESC
        <if test="page!=null and page.recordBegin>0 and page.pageSize>0 ">
            limit ${page.recordBegin},${page.pageSize}
        </if>
        <if test="page!=null and page.recordBegin==0 and page.pageSize>0 ">
            limit ${page.pageSize}
        </if>
    </select>

    <!--获得的总积分数-->
    <select id="getGainIntegralCount" parameterType="String" resultType="int">
        SELECT avg(use_integral_number)
        FROM tb_iamberry_use_integral
        WHERE user_openid=#{userOpenid} AND use_type=1
    </select>
    <!--使用的总积分数-->
    <select id="getUseIntegralCount" parameterType="String" resultType="int">
        SELECT avg(use_integral_number)
        FROM tb_iamberry_use_integral
        WHERE user_openid=#{userOpenid} AND use_type=2
    </select>
    <!--待入账的总积分数-->
    <select id="getStayIntegralCount" parameterType="String" resultType="int">
        SELECT avg(stay_number)
        FROM tb_iamberry_stay_integral
        WHERE user_openid=#{userOpenid} AND stay_status=1
    </select>
    <!--查询积分等级信息-->
    <select id="getRankRule" resultType="RankRule">
        SELECT *
        FROM tb_iamberry_rank_rule
    </select>
    <!-- 查询积分来源信息 -->
    <select id="listIntegralConfig" parameterType="IntegralConfig" resultType="IntegralConfig">
        SELECT
          *
        FROM
          tb_iamberry_integral_config
        <where>
          <if test="integralConfigId != null and integralConfigId != ''">
              integral_config_id = #{integralConfigId}
          </if>
        </where>
    </select>

    <!-- 添加积分日志 -->
    <insert id="addUseIntegral" parameterType="UseIntegral" keyProperty="useIntegralId" useGeneratedKeys="true">
        INSERT INTO tb_iamberry_use_integral
        (
          user_openid, use_type, use_integral_type, use_config_id,
          use_integral_number, use_integral_remarks, use_integral_create_time
        )
        VALUES
        (
          #{userOpenId},#{useType},#{useIntegralType},#{useConfigId},
          #{useIntegralNumber},#{useIntegralRemarks},NOW()
        )
    </insert>

    <!-- 查询会员积分等级规则信息 -->
    <select id="listRankRule" parameterType="RankRule" resultType="RankRule">
      SELECT
        *
      FROM
        tb_iamberry_rank_rule
      ORDER BY rank_rule_integral DESC
    </select>

    <!-- 添加会员信息 -->
    <insert id="addMemberIntegral" parameterType="MemberIntegral"
            useGeneratedKeys="true" keyProperty="memberId">
        INSERT INTO tb_iamberry_member_integral
        (
          rank_rule_id, user_openid, member_status, member_all_integral,
          member_surplus_integral, member_used_integral, member_create_time,
          member_update_time
        )
        VALUES
        (
          #{rankRuleId},#{userOpenid},#{memberStatus},#{memberAllIntegral},
          #{memberSurplusIntegral},#{memberUsedIntegral},NOW(),NOW()
        )
    </insert>

</mapper>