<?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.ad.mapper.AdMapper">

    <resultMap type="AdVO" id="AdResult" autoMapping="true">
        <result property="adId"    column="ad_id"    />
        <result property="type"    column="type"    />
        <result property="belong"    column="belong"    />
        <result property="storeId"    column="store_id"    />
        <result property="areaId"    column="area_id"    />
        <result property="picture"    column="picture"    />
        <result property="url"    column="url"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <result property="deleted"    column="deleted"    />
        <result property="urlType"    column="url_type"    />
    </resultMap>

    <sql id="selectAdVo">
        select DISTINCT
            bad.ad_id,
            bad.store_id,
            bad.area_id,
            bad.type,
            bad.belong,
            bad.picture,
            bad.url,
            bad.create_by,
            bad.create_time,
            bad.update_by,
            bad.update_time,
            bad.remark,
            bad.deleted,
            bad.url_type,
            ba.name AS areaName,
            su.agent_id
            from bst_ad bad
            left join bst_area ba on bad.area_id = ba.id
            left join bst_order bo ON ba.id = bo.area_id
            left join sys_user su on bo.user_id = su.user_id
    </sql>

    <sql id="searchCondition">
        <if test="query.areaId != null  and query.areaId != ''"> and bad.area_id = #{query.areaId} </if>
        <if test="query.storeId != null  and query.storeId != ''"> and bad.store_id = #{query.storeId}</if>
        <if test="query.adId != null  and query.adId != ''"> and bad.ad_id = #{query.adId} </if>
        <if test="query.type != null  and query.type != ''"> and bad.type = #{query.type}</if>
        <if test="query.belong != null  and query.belong != ''"> and bad.belong = #{query.belong}</if>
        <if test="query.picture != null  and query.picture != ''"> and bad.picture = #{query.picture}</if>
        <if test="query.url != null  and query.url != ''"> and bad.url like concat('%',#{query.url},'%') </if>
        <if test="query.deleted != null "> and bad.deleted = #{query.deleted}</if>
        <if test="query.urlType != null  and query.urlType != ''"> and bad.url_type = #{query.urlType}</if>
        ${@com.ruoyi.framework.util.DataScopeUtil@dataScope(
          null,
          "bad.store_id,su.agent_id",
          null,
          null,
          query.scope
      )}
        ${query.params.dataScope}
    </sql>

    <select id="selectAdList" parameterType="AdQuery" resultMap="AdResult">
        <include refid="selectAdVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectAdByAdId" parameterType="Long" resultMap="AdResult">
        <include refid="selectAdVo"/>
        where ad_id = #{adId}
    </select>

    <select id="selectOne"  parameterType="AdQuery" resultMap="AdResult">
        <include refid="selectAdVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        order by bad.create_time desc limit 1
    </select>

    <select id="countByAreaId" resultType="java.lang.Integer">
        SELECT COUNT(1)
        FROM bst_ad bad
        WHERE bad.area_id = #{areaId}
          AND bad.ad_id != #{adId}
          AND deleted = 0
    </select>

    <select id="selectAdByAreaId" resultType="com.ruoyi.bst.ad.domain.AdVO">
        <include refid="selectAdVo"/>
        <where>
            bad.area_id = #{areaId}
        </where>
    </select>

    <insert id="insertAd" parameterType="Ad" useGeneratedKeys="true" keyProperty="adId">
        insert into bst_ad
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="storeId != null">store_id,</if>
            <if test="areaId != null">area_id,</if>
            <if test="type != null">type,</if>
            <if test="belong != null">belong,</if>
            <if test="picture != null">picture,</if>
            <if test="url != null">url,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
            <if test="deleted != null">deleted,</if>
            <if test="urlType != null and urlType != ''">url_type,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="storeId != null">#{storeId},</if>
            <if test="areaId != null">#{areaId},</if>
            <if test="type != null">#{type},</if>
            <if test="belong != null">#{belong},</if>
            <if test="picture != null">#{picture},</if>
            <if test="url != null">#{url},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="deleted != null">#{deleted},</if>
            <if test="urlType != null and urlType != ''">#{urlType},</if>
         </trim>
    </insert>

    <insert id="batchInsert" parameterType="Ad" useGeneratedKeys="true" keyProperty="adId">
        insert into bst_ad
        <trim prefix="(" suffix=")" suffixOverrides=",">
            type,
            picture,
            url,
            create_by,
            create_time,
            update_by,
            update_time,
            remark,
            deleted,
            url_type,
        </trim>
        values
        <foreach collection="list" item="i" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="i.type != null ">#{i.type},</if>
                <if test="i.type == null ">default,</if>
                <if test="i.picture != null ">#{i.picture},</if>
                <if test="i.picture == null ">default,</if>
                <if test="i.url != null ">#{i.url},</if>
                <if test="i.url == null ">default,</if>
                <if test="i.createBy != null ">#{i.createBy},</if>
                <if test="i.createBy == null ">default,</if>
                <if test="i.createTime != null ">#{i.createTime},</if>
                <if test="i.createTime == null ">default,</if>
                <if test="i.updateBy != null ">#{i.updateBy},</if>
                <if test="i.updateBy == null ">default,</if>
                <if test="i.updateTime != null ">#{i.updateTime},</if>
                <if test="i.updateTime == null ">default,</if>
                <if test="i.remark != null ">#{i.remark},</if>
                <if test="i.remark == null ">default,</if>
                <if test="i.deleted != null ">#{i.deleted},</if>
                <if test="i.deleted == null ">default,</if>
                <if test="i.urlType != null  and i.urlType != ''">#{i.urlType},</if>
                <if test="i.urlType == null  or i.urlType == ''">default,</if>
            </trim>
        </foreach>
    </insert>

    <update id="batchUpdate">
        update bst_ad
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="type = CASE ad_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.type != null ">
                        WHEN #{item.ad_id} THEN #{item.type}
                    </when>
                    <otherwise>
                        WHEN #{item.ad_id} THEN `type`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="picture = CASE ad_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.picture != null ">
                        WHEN #{item.ad_id} THEN #{item.picture}
                    </when>
                    <otherwise>
                        WHEN #{item.ad_id} THEN `picture`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="url = CASE ad_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.url != null ">
                        WHEN #{item.ad_id} THEN #{item.url}
                    </when>
                    <otherwise>
                        WHEN #{item.ad_id} THEN `url`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="create_by = CASE ad_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.createBy != null ">
                        WHEN #{item.ad_id} THEN #{item.createBy}
                    </when>
                    <otherwise>
                        WHEN #{item.ad_id} THEN `create_by`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="create_time = CASE ad_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.createTime != null ">
                        WHEN #{item.ad_id} THEN #{item.createTime}
                    </when>
                    <otherwise>
                        WHEN #{item.ad_id} THEN `create_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="update_by = CASE ad_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.updateBy != null ">
                        WHEN #{item.ad_id} THEN #{item.updateBy}
                    </when>
                    <otherwise>
                        WHEN #{item.ad_id} THEN `update_by`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="update_time = CASE ad_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.updateTime != null ">
                        WHEN #{item.ad_id} THEN #{item.updateTime}
                    </when>
                    <otherwise>
                        WHEN #{item.ad_id} THEN `update_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="remark = CASE ad_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.remark != null ">
                        WHEN #{item.ad_id} THEN #{item.remark}
                    </when>
                    <otherwise>
                        WHEN #{item.ad_id} THEN `remark`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="deleted = CASE ad_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.deleted != null ">
                        WHEN #{item.ad_id} THEN #{item.deleted}
                    </when>
                    <otherwise>
                        WHEN #{item.ad_id} THEN `deleted`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="url_type = CASE ad_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.urlType != null  and item.urlType != ''">
                        WHEN #{item.ad_id} THEN #{item.urlType}
                    </when>
                    <otherwise>
                        WHEN #{item.ad_id} THEN `url_type`
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where ad_id in
        <foreach item="item" collection="list" open="(" separator="," close=")">
            #{item.adId}
        </foreach>
    </update>

    <update id="updateAd" parameterType="Ad">
        update bst_ad
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where ad_id = #{data.adId}
    </update>

    <update id="logicalDel">
        update bst_ad ba set ba.deleted = 1 where ba.ad_id in
        <foreach item="adId" collection="array" open="(" separator="," close=")">
            #{adId}
        </foreach>
    </update>

    <sql id="updateColumns">
        <if test="data.storeId != null">store_id = #{data.storeId},</if>
        <if test="data.areaId != null">area_id = #{data.areaId},</if>
        <if test="data.type != null">type = #{data.type},</if>
        <if test="data.picture != null">picture = #{data.picture},</if>
        <if test="data.url != null">url = #{data.url},</if>
        <if test="data.belong != null">belong = #{data.belong},</if>
        <if test="data.createBy != null">create_by = #{data.createBy},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.updateBy != null">update_by = #{data.updateBy},</if>
        <if test="data.updateTime != null">update_time = #{data.updateTime},</if>
        <if test="data.remark != null">remark = #{data.remark},</if>
        <if test="data.deleted != null">deleted = #{data.deleted},</if>
        <if test="data.urlType != null and data.urlType != ''">url_type = #{data.urlType},</if>
    </sql>

    <delete id="deleteAdByAdId" parameterType="Long">
        delete from bst_ad where ad_id = #{adId}
    </delete>

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