<?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.mchApply.mapper.MchApplyMapper">

    <resultMap type="MchApplyVO" id="MchApplyResult" autoMapping="true">
        <result property="applyId"    column="apply_id"    />
        <result property="userId"    column="user_id"    />
        <result property="status"    column="status"    />
        <result property="name"    column="name"    />
        <result property="mobile"    column="mobile"    />
        <result property="content"    column="content"    />
        <result property="remark"    column="remark"    />
        <result property="callback"    column="callback"    />
        <result property="verifyBy"    column="verify_by"    />
        <result property="verifyTime"    column="verify_time"    />
        <result property="verifyRemark"    column="verify_remark"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="deleted"    column="deleted"    />
        <result property="hasDevice"    column="has_device"    />
        <result property="operateMode"    column="operate_mode"    />
        <result property="scenes"    column="scenes"    />
    </resultMap>

    <sql id="selectMchApplyVo">
        select
            bma.apply_id,
            bma.user_id,
            bma.status,
            bma.name,
            bma.mobile,
            bma.content,
            bma.remark,
            bma.callback,
            bma.verify_by,
            bma.verify_time,
            bma.verify_remark,
            bma.create_time,
            bma.update_time,
            bma.deleted,
            bma.has_device,
            bma.operate_mode,
            bma.scenes,
            su.user_name as userName,
            su_verify.user_name as verifyName
        from bst_mch_apply bma
        left join sys_user su on bma.user_id = su.user_id
        left join sys_user su_verify on bma.verify_by = su_verify.user_id
    </sql>

    <sql id="searchCondition">
        <if test="query.userId != null "> and bma.user_id = #{query.userId}</if>
        <if test="query.status != null"> and bma.status = #{query.status}</if>
        <if test="query.name != null"> and bma.name like concat('%', #{query.name}, '%')</if>
        <if test="query.userName != null"> and su.user_name like concat('%', #{query.userName}, '%')</if>
        <if test="query.mobile != null"> and bma.mobile like concat('%', #{query.mobile}, '%')</if>
        <if test="query.verifyBy != null "> and bma.verify_by = #{query.verifyBy}</if>
        <if test="query.verifyName != null "> and su.user_name like concat('%', #{query.verifyName}, '%')</if>
        <if test="query.deleted == null">and deleted = false</if>
        <if test="query.deleted != null">and deleted = #{query.deleted}</if>
        <if test="query.applyId != null">and apply_id = #{query.applyId}</if>
        <if test="query.hasDevice != null">and has_device = #{query.hasDevice}</if>
        <if test="query.operateMode != null">and operate_mode = #{query.operateMode}</if>
        <if test="query.createDateRange != null and query.createDateRange.size() >= 2">
            and date(bma.create_time) &gt;= #{query.createDateRange[0]}
            and date(bma.create_time) &lt;= #{query.createDateRange[1]}
        </if>
        ${@com.ruoyi.framework.util.DataScopeUtil@dataScope(
            null,
            "bma.user_id,bma.verify_by",
            null,
            null,
            query.scope
        )}
        ${query.params.dataScope}
    </sql>

    <select id="selectMchApplyList" parameterType="MchApplyQuery" resultMap="MchApplyResult">
        <include refid="selectMchApplyVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectMchApplyByApplyId" parameterType="Long" resultMap="MchApplyResult">
        <include refid="selectMchApplyVo"/>
        where apply_id = #{applyId}
    </select>

    <insert id="insertMchApply" parameterType="MchApply" useGeneratedKeys="true" keyProperty="applyId">
        insert into bst_mch_apply
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="status != null and status != ''">status,</if>
            <if test="name != null and name != ''">name,</if>
            <if test="mobile != null and mobile != ''">mobile,</if>
            <if test="content != null and content != ''">content,</if>
            <if test="remark != null">remark,</if>
            <if test="callback != null">callback,</if>
            <if test="verifyBy != null">verify_by,</if>
            <if test="verifyTime != null">verify_time,</if>
            <if test="verifyRemark != null">verify_remark,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="deleted != null">deleted,</if>
            <if test="hasDevice != null">has_device,</if>
            <if test="operateMode != null">operate_mode,</if>
            <if test="scenes != null">scenes,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="status != null and status != ''">#{status},</if>
            <if test="name != null and name != ''">#{name},</if>
            <if test="mobile != null and mobile != ''">#{mobile},</if>
            <if test="content != null and content != ''">#{content},</if>
            <if test="remark != null">#{remark},</if>
            <if test="callback != null">#{callback},</if>
            <if test="verifyBy != null">#{verifyBy},</if>
            <if test="verifyTime != null">#{verifyTime},</if>
            <if test="verifyRemark != null">#{verifyRemark},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="deleted != null">#{deleted},</if>
            <if test="hasDevice != null">#{hasDevice},</if>
            <if test="operateMode != null">#{operateMode},</if>
            <if test="scenes != null">#{scenes},</if>
         </trim>
    </insert>

    <insert id="batchInsert" parameterType="MchApply" useGeneratedKeys="true" keyProperty="applyId">
        insert into bst_mch_apply
        <trim prefix="(" suffix=")" suffixOverrides=",">
            user_id,
            status,
            name,
            mobile,
            content,
            remark,
            callback,
            verify_by,
            verify_time,
            verify_remark,
            create_time,
            update_time,
            deleted,
            has_device,
            operate_mode,
            scenes,
        </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.status != null  and i.status != ''">#{i.status},</if>
                <if test="i.status == null  or i.status == ''">default,</if>
                <if test="i.name != null  and i.name != ''">#{i.name},</if>
                <if test="i.name == null  or i.name == ''">default,</if>
                <if test="i.mobile != null  and i.mobile != ''">#{i.mobile},</if>
                <if test="i.mobile == null  or i.mobile == ''">default,</if>
                <if test="i.content != null  and i.content != ''">#{i.content},</if>
                <if test="i.content == null  or i.content == ''">default,</if>
                <if test="i.remark != null ">#{i.remark},</if>
                <if test="i.remark == null ">default,</if>
                <if test="i.callback != null ">#{i.callback},</if>
                <if test="i.callback == null ">default,</if>
                <if test="i.verifyBy != null ">#{i.verifyBy},</if>
                <if test="i.verifyBy == null ">default,</if>
                <if test="i.verifyTime != null ">#{i.verifyTime},</if>
                <if test="i.verifyTime == null ">default,</if>
                <if test="i.verifyRemark != null ">#{i.verifyRemark},</if>
                <if test="i.verifyRemark == null ">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>
                <if test="i.deleted != null ">#{i.deleted},</if>
                <if test="i.deleted == null ">default,</if>
                <if test="i.hasDevice != null ">#{i.hasDevice},</if>
                <if test="i.hasDevice == null ">default,</if>
                <if test="i.operateMode != null ">#{i.operateMode},</if>
                <if test="i.operateMode == null ">default,</if>
                <if test="i.scenes != null ">#{i.scenes},</if>
                <if test="i.scenes == null ">default,</if>
            </trim>
        </foreach>
    </insert>

    <update id="batchUpdate">
        update bst_mch_apply
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="user_id = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.userId != null ">
                        WHEN #{item.apply_id} THEN #{item.userId}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `user_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="status = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.status != null  and item.status != ''">
                        WHEN #{item.apply_id} THEN #{item.status}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `status`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="name = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.name != null  and item.name != ''">
                        WHEN #{item.apply_id} THEN #{item.name}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `name`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="mobile = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.mobile != null  and item.mobile != ''">
                        WHEN #{item.apply_id} THEN #{item.mobile}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `mobile`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="content = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.content != null  and item.content != ''">
                        WHEN #{item.apply_id} THEN #{item.content}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `content`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="remark = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.remark != null ">
                        WHEN #{item.apply_id} THEN #{item.remark}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `remark`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="callback = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.callback != null ">
                        WHEN #{item.apply_id} THEN #{item.callback}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `callback`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="verify_by = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.verifyBy != null ">
                        WHEN #{item.apply_id} THEN #{item.verifyBy}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `verify_by`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="verify_time = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.verifyTime != null ">
                        WHEN #{item.apply_id} THEN #{item.verifyTime}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `verify_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="verify_remark = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.verifyRemark != null ">
                        WHEN #{item.apply_id} THEN #{item.verifyRemark}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `verify_remark`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="create_time = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.createTime != null ">
                        WHEN #{item.apply_id} THEN #{item.createTime}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `create_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="update_time = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.updateTime != null ">
                        WHEN #{item.apply_id} THEN #{item.updateTime}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `update_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="deleted = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.deleted != null ">
                        WHEN #{item.apply_id} THEN #{item.deleted}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `deleted`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="has_device = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.hasDevice != null ">
                        WHEN #{item.apply_id} THEN #{item.hasDevice}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `has_device`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="operate_mode = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.operateMode != null ">
                        WHEN #{item.apply_id} THEN #{item.operateMode}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `operate_mode`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="scenes = CASE apply_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.scenes != null ">
                        WHEN #{item.apply_id} THEN #{item.scenes}
                    </when>
                    <otherwise>
                        WHEN #{item.apply_id} THEN `scenes`
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where apply_id in
        <foreach item="item" collection="list" open="(" separator="," close=")">
            #{item.applyId}
        </foreach>
    </update>

    <update id="updateMchApply" parameterType="MchApply">
        update bst_mch_apply
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where apply_id = #{data.applyId}
    </update>

    <update id="updateWithCondition">
        update bst_mch_apply bma
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        <where>
            <include refid="searchCondition"/>
        </where>
    </update>

    <sql id="updateColumns">
        <if test="data.userId != null">user_id = #{data.userId},</if>
        <if test="data.status != null and data.status != ''">status = #{data.status},</if>
        <if test="data.name != null and data.name != ''">name = #{data.name},</if>
        <if test="data.mobile != null and data.mobile != ''">mobile = #{data.mobile},</if>
        <if test="data.content != null and data.content != ''">content = #{data.content},</if>
        <if test="data.remark != null">remark = #{data.remark},</if>
        <if test="data.callback != null">callback = #{data.callback},</if>
        <if test="data.verifyBy != null">verify_by = #{data.verifyBy},</if>
        <if test="data.verifyTime != null">verify_time = #{data.verifyTime},</if>
        <if test="data.verifyRemark != null">verify_remark = #{data.verifyRemark},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.updateTime != null">update_time = #{data.updateTime},</if>
        <if test="data.deleted != null">deleted = #{data.deleted},</if>
        <if test="data.hasDevice != null">has_device = #{data.hasDevice},</if>
        <if test="data.operateMode != null">operate_mode = #{data.operateMode},</if>
        <if test="data.scenes != null">scenes = #{data.scenes},typeHandler=com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler},</if>
    </sql>

    <delete id="deleteMchApplyByApplyId" parameterType="Long">
        delete from bst_mch_apply where apply_id = #{applyId}
    </delete>

    <delete id="deleteMchApplyByApplyIds" parameterType="String">
        delete from bst_mch_apply where apply_id in
        <foreach item="applyId" collection="array" open="(" separator="," close=")">
            #{applyId}
        </foreach>
    </delete>

    <!-- selectCount --> 

    <select id="selectCount">
        select count(bma.apply_id)
        from bst_mch_apply bma
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>
    
</mapper>
