<?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.bst.lightingNum.mapper.LightingNumMapper">

    <resultMap type="LightingNumVO" id="LightingNumResult" autoMapping="true">
        <result property="id"    column="id"    />
        <result property="userId"    column="user_id"    />
        <result property="storeId"    column="store_id"    />
        <result property="number"    column="number"    />
        <result property="createTime"    column="create_time"    />
    </resultMap>

    <sql id="selectLightingNumVo">
        select
            bln.id,
            bln.user_id,
            bln.store_id,
            bln.number,
            bln.create_time,
            bs.store_name,
            bs.user_id as mch_id,
            su.user_name,
            su.nick_name
        from <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        bst_lighting_num bln
            left join bst_store bs on bln.store_id = bs.store_id
            left join sys_user su on bln.user_id = su.user_id
    </sql>


    <sql id="searchCondition">
        <if test="query.id != null "> and id = #{query.id}</if>
        <if test="query.userId != null "> and bln.user_id = #{query.userId}</if>
        <if test="query.storeId != null "> and bln.store_id = #{query.storeId}</if>
        <if test="query.number != null "> and bln.number = #{query.number}</if>
        <if test="query.userName != null "> and bs.user_name like concat('%',#{query.userName},'%') </if>
        <if test="query.storeName != null "> and bs.store_name like concat('%',#{query.storeName},'%')</if>
        <if test="query.mchId != null "> and bs.user_id  = #{query.mchId}</if>
        ${@com.ruoyi.framework.util.DataScopeUtil@create(query.scope)
        .userSetAlias("bs.user_id")
         .build()
         }
        ${query.params.dataScope}
    </sql>

    <select id="selectLightingNumList" parameterType="LightingNumQuery" resultMap="LightingNumResult">
        <include refid="selectLightingNumVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectLightingNumById" parameterType="Long" resultMap="LightingNumResult">
        <include refid="selectLightingNumVo"/>
        where id = #{id}
    </select>

    <select id="selectLightingNumberForUpdate" resultType="java.lang.Integer">
            select bln.number
            from bst_lighting_num bln
            where bln.user_id = #{userId} and bln.store_id = #{storeId}
    </select>

    <select id="selectLightingNumByUserId" resultType="com.ruoyi.bst.lightingNum.domain.LightingNumVO">
        <include refid="selectLightingNumVo"/>
        where bln.user_id = #{userId} and bln.store_id = #{storeId}
    </select>

    <select id="assembleLightingNum" resultType="com.ruoyi.common.domain.vo.LongIntegerVO">
        select
        bln.user_id AS `key`,
        sum(bln.number) AS `value`
        from bst_lighting_num bln
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        select
        sum(bln.number)
        from bst_lighting_num bln
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <insert id="insertLightingNum" parameterType="LightingNum" useGeneratedKeys="true" keyProperty="id">
        insert into bst_lighting_num
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="storeId != null">store_id,</if>
            <if test="number != null">number,</if>
            <if test="createTime != null">create_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="storeId != null">#{storeId},</if>
            <if test="number != null">#{number},</if>
            <if test="createTime != null">#{createTime},</if>
         </trim>
    </insert>

    <insert id="batchInsert" parameterType="LightingNum" useGeneratedKeys="true" keyProperty="id">
        insert into bst_lighting_num
        <trim prefix="(" suffix=")" suffixOverrides=",">
            user_id,
            store_id,
            number,
            create_time,
        </trim>
        values
        <foreach collection="list" item="i" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="i.userId != null ">#{i.userId},</if>
                <if test="i.userId == null ">default,</if>
                <if test="i.storeId != null ">#{i.storeId},</if>
                <if test="i.storeId == null ">default,</if>
                <if test="i.number != null ">#{i.number},</if>
                <if test="i.number == null ">default,</if>
                <if test="i.createTime != null ">#{i.createTime},</if>
                <if test="i.createTime == null ">default,</if>
            </trim>
        </foreach>
    </insert>


    <!-- addLightingNum -->
    <update id="addLightingNum">
        update bst_lighting_num bln
        set bln.number = bln.number + #{number}
        where bln.store_id = #{storeId} and bln.user_id = #{userId}
    </update>

    <update id="batchUpdate">
        update bst_lighting_num
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="user_id = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.userId != null ">
                        WHEN #{item.id} THEN #{item.userId}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `user_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="store_id = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.storeId != null ">
                        WHEN #{item.id} THEN #{item.storeId}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `store_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="number = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.number != null ">
                        WHEN #{item.id} THEN #{item.number}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `number`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="create_time = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.createTime != null ">
                        WHEN #{item.id} THEN #{item.createTime}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `create_time`
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where id in
        <foreach item="item" collection="list" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </update>

    <update id="updateLightingNum" parameterType="LightingNum">
        update bst_lighting_num
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <update id="reduceLightingNum">
        UPDATE bst_lighting_num bln
        SET number = number - #{data.number}
        WHERE user_id = #{query.userId} AND store_id = #{query.storeId} AND number > 0;
    </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.number != null">number = #{data.number},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
    </sql>

    <delete id="deleteLightingNumById" parameterType="Long">
        delete from bst_lighting_num where id = #{id}
    </delete>

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