<?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.jarvis.mapper.FavoriteProductMapper">
    
    <resultMap type="FavoriteProduct" id="FavoriteProductResult">
        <result property="id"           column="id"           />
        <result property="skuid"        column="skuid"        />
        <result property="productName"  column="product_name" />
        <result property="shopName"     column="shop_name"    />
        <result property="shopId"       column="shop_id"      />
        <result property="productUrl"   column="product_url"  />
        <result property="productImage" column="product_image"/>
        <result property="price"        column="price"        />
        <result property="commissionInfo" column="commission_info"/>
        <result property="isTop"        column="is_top"       />
        <result property="sortWeight"   column="sort_weight"  />
        <result property="remark"       column="remark"       />
        <result property="createUserId" column="create_user_id"/>
        <result property="createUserName" column="create_user_name"/>
        <result property="erpProductIds" column="erp_product_ids"/>
        <result property="category"     column="category"     />
        <result property="brand"        column="brand"        />
        <result property="lastUsedTime" column="last_used_time"/>
        <result property="useCount"     column="use_count"    />
        <result property="productId"    column="product_id"   />
        <result property="productStatus" column="product_status"/>
        <result property="createTime"   column="create_time"  />
        <result property="updateTime"   column="update_time"  />
    </resultMap>

    <sql id="selectFavoriteProductVo">
        select id, skuid, product_name, shop_name, shop_id, product_url, product_image, 
               price, commission_info, is_top, sort_weight, remark, create_user_id, 
               create_user_name, erp_product_ids, category, brand, last_used_time, 
               use_count, product_id, product_status, create_time, update_time
        from favorite_product
    </sql>

    <select id="selectFavoriteProductList" parameterType="FavoriteProduct" resultMap="FavoriteProductResult">
        <include refid="selectFavoriteProductVo"/>
        <where>  
            <if test="skuid != null  and skuid != ''"> and skuid = #{skuid}</if>
            <if test="productName != null  and productName != ''"> and product_name like concat('%', #{productName}, '%')</if>
            <if test="shopName != null  and shopName != ''"> and shop_name like concat('%', #{shopName}, '%')</if>
            <if test="shopId != null  and shopId != ''"> and shop_id = #{shopId}</if>
            <if test="category != null  and category != ''"> and category like concat('%', #{category}, '%')</if>
            <if test="brand != null  and brand != ''"> and brand like concat('%', #{brand}, '%')</if>
            <if test="isTop != null "> and is_top = #{isTop}</if>
            <if test="createUserId != null "> and create_user_id = #{createUserId}</if>
            <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
                and date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
            </if>
            <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
                and date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
            </if>
        </where>
        order by is_top desc, sort_weight desc, create_time desc
    </select>
    
    <select id="selectFavoriteProductById" parameterType="Long" resultMap="FavoriteProductResult">
        <include refid="selectFavoriteProductVo"/>
        where id = #{id}
    </select>
    
    <select id="selectFavoriteProductBySkuid" parameterType="String" resultMap="FavoriteProductResult">
        <include refid="selectFavoriteProductVo"/>
        where skuid = #{skuid}
    </select>
    
    <select id="selectUserFavoriteProducts" parameterType="Long" resultMap="FavoriteProductResult">
        <include refid="selectFavoriteProductVo"/>
        where create_user_id = #{createUserId}
        order by is_top desc, sort_weight desc, last_used_time desc, create_time desc
    </select>
        
    <insert id="insertFavoriteProduct" parameterType="FavoriteProduct" useGeneratedKeys="true" keyProperty="id">
        insert into favorite_product
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="skuid != null and skuid != ''">skuid,</if>
            <if test="productName != null and productName != ''">product_name,</if>
            <if test="shopName != null and shopName != ''">shop_name,</if>
            <if test="shopId != null and shopId != ''">shop_id,</if>
            <if test="productUrl != null and productUrl != ''">product_url,</if>
            <if test="productImage != null and productImage != ''">product_image,</if>
            <if test="price != null and price != ''">price,</if>
            <if test="commissionInfo != null and commissionInfo != ''">commission_info,</if>
            <if test="isTop != null">is_top,</if>
            <if test="sortWeight != null">sort_weight,</if>
            <if test="remark != null and remark != ''">remark,</if>
            <if test="createUserId != null">create_user_id,</if>
            <if test="createUserName != null and createUserName != ''">create_user_name,</if>
            <if test="erpProductIds != null and erpProductIds != ''">erp_product_ids,</if>
            <if test="category != null and category != ''">category,</if>
            <if test="brand != null and brand != ''">brand,</if>
            <if test="lastUsedTime != null and lastUsedTime != ''">last_used_time,</if>
            <if test="useCount != null">use_count,</if>
            <if test="productId != null and productId != ''">product_id,</if>
            <if test="productStatus != null">product_status,</if>
            create_time,
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="skuid != null and skuid != ''">#{skuid},</if>
            <if test="productName != null and productName != ''">#{productName},</if>
            <if test="shopName != null and shopName != ''">#{shopName},</if>
            <if test="shopId != null and shopId != ''">#{shopId},</if>
            <if test="productUrl != null and productUrl != ''">#{productUrl},</if>
            <if test="productImage != null and productImage != ''">#{productImage},</if>
            <if test="price != null and price != ''">#{price},</if>
            <if test="commissionInfo != null and commissionInfo != ''">#{commissionInfo},</if>
            <if test="isTop != null">#{isTop},</if>
            <if test="sortWeight != null">#{sortWeight},</if>
            <if test="remark != null and remark != ''">#{remark},</if>
            <if test="createUserId != null">#{createUserId},</if>
            <if test="createUserName != null and createUserName != ''">#{createUserName},</if>
            <if test="erpProductIds != null and erpProductIds != ''">#{erpProductIds},</if>
            <if test="category != null and category != ''">#{category},</if>
            <if test="brand != null and brand != ''">#{brand},</if>
            <if test="lastUsedTime != null and lastUsedTime != ''">#{lastUsedTime},</if>
            <if test="useCount != null">#{useCount},</if>
            <if test="productId != null and productId != ''">#{productId},</if>
            <if test="productStatus != null">#{productStatus},</if>
            sysdate(),
        </trim>
    </insert>

    <update id="updateFavoriteProduct" parameterType="FavoriteProduct">
        update favorite_product
        <trim prefix="SET" suffixOverrides=",">
            <if test="skuid != null and skuid != ''">skuid = #{skuid},</if>
            <if test="productName != null and productName != ''">product_name = #{productName},</if>
            <if test="shopName != null and shopName != ''">shop_name = #{shopName},</if>
            <if test="shopId != null and shopId != ''">shop_id = #{shopId},</if>
            <if test="productUrl != null and productUrl != ''">product_url = #{productUrl},</if>
            <if test="productImage != null and productImage != ''">product_image = #{productImage},</if>
            <if test="price != null and price != ''">price = #{price},</if>
            <if test="commissionInfo != null and commissionInfo != ''">commission_info = #{commissionInfo},</if>
            <if test="isTop != null">is_top = #{isTop},</if>
            <if test="sortWeight != null">sort_weight = #{sortWeight},</if>
            <if test="remark != null and remark != ''">remark = #{remark},</if>
            <if test="createUserId != null">create_user_id = #{createUserId},</if>
            <if test="createUserName != null and createUserName != ''">create_user_name = #{createUserName},</if>
            <if test="erpProductIds != null and erpProductIds != ''">erp_product_ids = #{erpProductIds},</if>
            <if test="category != null and category != ''">category = #{category},</if>
            <if test="brand != null and brand != ''">brand = #{brand},</if>
            <if test="lastUsedTime != null and lastUsedTime != ''">last_used_time = #{lastUsedTime},</if>
            <if test="useCount != null">use_count = #{useCount},</if>
            <if test="productId != null and productId != ''">product_id = #{productId},</if>
            <if test="productStatus != null">product_status = #{productStatus},</if>
            update_time = sysdate(),
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteFavoriteProductById" parameterType="Long">
        delete from favorite_product where id = #{id}
    </delete>

    <delete id="deleteFavoriteProductByIds" parameterType="String">
        delete from favorite_product where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    
    <update id="updateUseCountAndTime" parameterType="Long">
        update favorite_product 
        set use_count = ifnull(use_count, 0) + 1,
            last_used_time = sysdate(),
            update_time = sysdate()
        where id = #{id}
    </update>
    
    <update id="updateTopStatus" parameterType="FavoriteProduct">
        update favorite_product 
        set is_top = #{isTop},
            sort_weight = case when #{isTop} = 1 then 100 else 0 end,
            update_time = sysdate()
        where id = #{id}
    </update>
    
    <update id="updateProductInfo" parameterType="FavoriteProduct">
        update favorite_product 
        set product_id = #{productId},
            product_status = #{productStatus},
            update_time = sysdate()
        where skuid = #{skuid}
    </update>
    
</mapper>
