<?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.realName.mapper.RealNameMapper">

    <resultMap type="RealNameVO" id="RealNameResult" autoMapping="true">
        <result property="id"    column="id"    />
        <result property="userName"    column="user_name"    />
        <result property="idCard"    column="id_card"    />
        <result property="picture"    column="picture"    />
        <result property="score"    column="score"    />
        <result property="mobile"    column="mobile"    />
        <result property="status"    column="status"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>

    <sql id="selectRealNameVo">
        select
            brn.id,
            brn.user_name,
            brn.id_card,
            brn.picture,
            brn.score,
            brn.mobile,
            brn.status,
            brn.create_time,
            brn.update_time
        from bst_real_name brn
    </sql>

    <sql id="searchCondition">
        <if test="query.userName != null  and query.userName != ''"> and user_name like concat('%', #{query.userName}, '%')</if>
        <if test="query.idCard != null  and query.idCard != ''"> and id_card = #{query.idCard}</if>
        <if test="query.picture != null  and query.picture != ''"> and picture = #{query.picture}</if>
        <if test="query.score != null "> and score = #{query.score}</if>
        <if test="query.mobile != null  and query.mobile != ''"> and mobile = #{query.mobile}</if>
        <if test="query.status != null  and query.status != ''"> and status = #{query.status}</if>
        ${query.params.dataScope}
    </sql>

    <select id="selectRealNameList" parameterType="RealNameQuery" resultMap="RealNameResult">
        <include refid="selectRealNameVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectRealNameById" parameterType="Long" resultMap="RealNameResult">
        <include refid="selectRealNameVo"/>
        where id = #{id}
    </select>
    <select id="checkIdCard" resultType="java.lang.Boolean">
        select count(1) from bst_real_name where id_card = #{idCard}
    </select>

    <select id="selectRealNameByUserId" resultType="com.ruoyi.bst.realName.domain.RealNameVO">
        select brn.id,
               brn.user_name,
               brn.id_card,
               brn.picture,
               brn.score,
               brn.mobile,
               brn.status,
               brn.create_time,
               brn.update_time
        from bst_real_name brn where user_id = #{userId}
    </select>

    <insert id="insertRealName" parameterType="RealName" useGeneratedKeys="true" keyProperty="id">
        insert into bst_real_name
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userName != null">user_name,</if>
            <if test="idCard != null">id_card,</if>
            <if test="picture != null">picture,</if>
            <if test="score != null">score,</if>
            <if test="mobile != null">mobile,</if>
            <if test="status != null and status != ''">status,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="userId != null">user_id</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userName != null">#{userName},</if>
            <if test="idCard != null">#{idCard},</if>
            <if test="picture != null">#{picture},</if>
            <if test="score != null">#{score},</if>
            <if test="mobile != null">#{mobile},</if>
            <if test="status != null and status != ''">#{status},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="userId != null">#{userId}</if>
         </trim>
    </insert>

    <insert id="batchInsert" parameterType="RealName" useGeneratedKeys="true" keyProperty="id">
        insert into bst_real_name
        <trim prefix="(" suffix=")" suffixOverrides=",">
            user_name,
            id_card,
            picture,
            score,
            mobile,
            status,
            create_time,
            update_time,
        </trim>
        values
        <foreach collection="list" item="i" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="i.userName != null ">#{i.userName},</if>
                <if test="i.userName == null ">default,</if>
                <if test="i.idCard != null ">#{i.idCard},</if>
                <if test="i.idCard == null ">default,</if>
                <if test="i.picture != null ">#{i.picture},</if>
                <if test="i.picture == null ">default,</if>
                <if test="i.score != null ">#{i.score},</if>
                <if test="i.score == null ">default,</if>
                <if test="i.mobile != null ">#{i.mobile},</if>
                <if test="i.mobile == null ">default,</if>
                <if test="i.status != null  and i.status != ''">#{i.status},</if>
                <if test="i.status == null  or i.status == ''">default,</if>
                <if test="i.createTime != null ">#{i.createTime},</if>
                <if test="i.createTime == null ">default,</if>
                <if test="i.updateTime != null ">#{i.updateTime},</if>
                <if test="i.updateTime == null ">default,</if>
            </trim>
        </foreach>
    </insert>

    <update id="batchUpdate">
        update bst_real_name
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="user_name = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.userName != null ">
                        WHEN #{item.id} THEN #{item.userName}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `user_name`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="id_card = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.idCard != null ">
                        WHEN #{item.id} THEN #{item.idCard}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `id_card`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="picture = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.picture != null ">
                        WHEN #{item.id} THEN #{item.picture}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `picture`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="score = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.score != null ">
                        WHEN #{item.id} THEN #{item.score}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `score`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="mobile = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.mobile != null ">
                        WHEN #{item.id} THEN #{item.mobile}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `mobile`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="status = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.status != null  and item.status != ''">
                        WHEN #{item.id} THEN #{item.status}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `status`
                    </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>
            <foreach open="update_time = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.updateTime != null ">
                        WHEN #{item.id} THEN #{item.updateTime}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `update_time`
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where id in
        <foreach item="item" collection="list" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </update>

    <update id="updateRealName" parameterType="RealName">
        update bst_real_name
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id} OR user_id = #{data.userId}
    </update>

    <sql id="updateColumns">
        <if test="data.userName != null">user_name = #{data.userName},</if>
        <if test="data.idCard != null">id_card = #{data.idCard},</if>
        <if test="data.picture != null">picture = #{data.picture},</if>
        <if test="data.score != null">score = #{data.score},</if>
        <if test="data.mobile != null">mobile = #{data.mobile},</if>
        <if test="data.status != null and data.status != ''">status = #{data.status},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.updateTime != null">update_time = #{data.updateTime},</if>
        <if test="data.userId != null">user_id = #{data.userId},</if>

    </sql>

    <delete id="deleteRealNameById" parameterType="Long">
        delete from bst_real_name where id = #{id}
    </delete>

    <delete id="deleteRealNameByIds" parameterType="String">
        delete from bst_real_name where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <delete id="deleteRealNameByUserId">
        DELETE from bst_real_name where user_id = #{userId}
    </delete>
</mapper>
