<?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.ruoyi.ss.balance.mapper.UserBalanceMapper">

    <resultMap type="UserBalanceVO" id="EUserBalanceResult" autoMapping="true" />

    <sql id="selectEUserBalanceVo">
        select
            balance_id,
            user_id,
            store_id,
            balance
        from ss_user_balance
    </sql>

    <sql id="searchCondition">
        <if test="query.userId != null "> and user_id = #{query.userId}</if>
        <if test="query.storeId != null "> and store_id = #{query.storeId}</if>
        <if test="query.balance != null "> and balance = #{query.balance}</if>
    </sql>

    <select id="selectEUserBalanceList" parameterType="UserBalanceQuery" resultMap="EUserBalanceResult">
        <include refid="selectEUserBalanceVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectEUserBalanceByBalanceId" parameterType="Long" resultMap="EUserBalanceResult">
        <include refid="selectEUserBalanceVo"/>
        where balance_id = #{balanceId}
    </select>

    <select id="selectUserBalance" parameterType="UserBalanceQuery" resultType="java.math.BigDecimal">
        SELECT  SUM(CASE WHEN type = '1' THEN amount ELSE 0 END) -
                SUM(CASE WHEN type = '2' THEN amount ELSE 0 END) AS netBalance
        FROM ss_change_balance
        WHERE 1=1
        <if test="startTime != null">
            AND DATE(create_time) &gt;= #{startTime}
        </if>
        <if test="endTime != null">
            AND DATE(create_time) &lt;= #{endTime}
        </if>
    </select>

    <insert id="insertEUserBalance" parameterType="UserBalance" useGeneratedKeys="true" keyProperty="balanceId">
        insert into ss_user_balance
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="storeId != null">store_id,</if>
            <if test="balance != null">balance,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="storeId != null">#{storeId},</if>
            <if test="balance != null">#{balance},</if>
         </trim>
    </insert>

    <update id="updateEUserBalance" parameterType="UserBalance">
        update ss_user_balance
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where balance_id = #{data.balanceId}
    </update>

    <sql id="updateColumns">
        <if test="data.userId != null">user_id = #{data.userId},</if>
        <if test="data.storeId != null">store_id = #{data.storeId},</if>
        <if test="data.balance != null">balance = #{data.balance},</if>
    </sql>

    <delete id="deleteEUserBalanceByBalanceId" parameterType="Long">
        delete from ss_user_balance where balance_id = #{balanceId}
    </delete>

    <delete id="deleteEUserBalanceByBalanceIds" parameterType="String">
        delete from ss_user_balance where balance_id in
        <foreach item="balanceId" collection="array" open="(" separator="," close=")">
            #{balanceId}
        </foreach>
    </delete>
</mapper>
