<?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.ruixuan.isc.mapper.KuaishouItemListMapper">
    <resultMap type="com.ruixuan.isc.entity.KuaishouItemList" id="KuaishouItemListResult">
        <result property="itemId" column="item_id"/>
        <result property="mediaId" column="media_id"/>
        <result property="itemTitle" column="item_title"/>
        <result property="itemPrice" column="item_price"/>
        <result property="itemDesc" column="item_desc"/>
        <result property="zkFinalPrice" column="zk_final_price"/>
        <result property="itemImgUrl" column="item_img_url"/>
        <result property="itemDescUrls" column="item_desc_urls"/>
        <result property="shopStar" column="shop_star"/>
        <result property="mallLogisticsScore" column="mall_logistics_score"/>
        <result property="activityItemStatus" column="activity_item_status"/>
        <result property="shopScore" column="shop_score"/>
        <result property="categoryId" column="category_id"/>
        <result property="categoryName" column="category_name"/>
        <result property="itemGalleryUrls" column="item_gallery_urls"/>
        <result property="identity" column="identity"/>
        <result property="activityId" column="activity_id"/>
        <result property="shopTitle" column="shop_title"/>
        <result property="commissionRate" column="commission_rate"/>
        <result property="mallServiceScore" column="mall_service_score"/>
        <result property="mallQualityScore" column="mall_quality_score"/>
        <result property="skuList" column="sku_list"/>
        <result property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="examineId" column="examine_id"/>
        <result property="examineName" column="examine_name"/>
        <result property="examineStatus" column="examine_status"/>
        <result property="refuseDetail" column="refuse_detail"/>
        <result property="postArea" column="post_area"/>
        <result property="noPostArea" column="no_post_area"/>
        <result property="regimentalPromotionRate" column="regimental_promotion_rate"/>
        <result property="regimentalPromotion" column="regimental_promotion"/>
        <result property="sampleCount" column="sample_count"/>
        <result property="sampleRequirement" column="sample_requirement"/>
        <result property="deliveryRate" column="delivery_rate"/>
        <result property="partnerRecommendText" column="partner_recommend_text"/>
        <result property="detailUrl" column="detail_url"/>
        <result property="samplesReceived" column="samples_received"/>
        <result property="samplesPeoples" column="samples_peoples"/>
        <result property="relateId" column="relate_id"/>
        <result property="ruleId" column="rule_id"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <sql id="selectKuaishouItemListVo">
        select item_id, media_id, item_title, item_price, item_desc, zk_final_price, item_img_url, item_desc_urls, shop_star, mall_logistics_score, activity_item_status, shop_score, category_id, category_name, item_gallery_urls, identity, activity_id, shop_title, commission_rate, mall_service_score, mall_quality_score, sku_list, user_id, user_name, examine_id, examine_name, examine_status, refuse_detail, post_area, no_post_area, regimental_promotion_rate, regimental_promotion, sample_count, sample_requirement, delivery_rate, partner_recommend_text, detail_url,samples_received, samples_peoples,relate_id,rule_id,create_time, update_time from kuaishou_item_list
    </sql>
    <select id="getItemList" resultType="com.ruixuan.isc.entity.KuaishouItemList">
        select t1.item_id, t1.item_title, t1.item_price, t1.item_desc, t1.zk_final_price, t1.item_img_url,
        t1.item_desc_urls, t1.shop_star,
        t1.mall_logistics_score, t1.activity_item_status, t1.shop_score, t1.category_id, t1.category_name,
        t1.item_gallery_urls, t1.identity,
        t1.activity_id, t1.shop_title, t1.commission_rate, t1.mall_service_score, t1.mall_quality_score, t1.sku_list,
        t1.user_id, t1.user_name,
        t1.examine_id, t1.examine_name, t1.examine_status, t1.refuse_detail, t1.post_area, t1.no_post_area,
        t1.regimental_promotion_rate,
        t1.regimental_promotion, t1.sample_count, t1.sample_requirement, t1.delivery_rate, t1.partner_recommend_text,
        t1.detail_url,t1.samples_received, t1.samples_peoples,t1.relate_id,t1.rule_id,t1.create_time, t1.update_time,
        t2.rule_name,
        t1.media_id,
        (
        case when t1.media_id = 2
        then '快手'
       when t1.media_id = 1
       then '抖音'
       else '-'
       end
        ) as 'media_name'
        from kuaishou_item_list
        t1
        left join kuaishou_settlement_rules t2
        on t1.rule_id = t2.id
        where 1 = 1
        <if test="userId != null and userId != ''">
            and t1.user_id = #{userId}
        </if>
        <if test='userIds != null and userIds.size() > 0'>
            AND t1.user_id in
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="examineStatus != null and examineStatus != ''">
            and t1.examine_status = #{examineStatus}
        </if>
        <if test="itemId != null and itemId != ''">
            and t1.item_id = #{itemId}
        </if>
        <if test="itemTitle != null and itemTitle != ''">
            and t1.item_title like concat('%', #{itemTitle}, '%')
        </if>
        <if test="categoryId != null and categoryId != ''">
            and t1.category_id = #{categoryId}
        </if>
        <if test="mediaId != null and mediaId != ''">
            and t1.media_id = #{mediaId}
        </if>
        order by t1.create_time desc
    </select>
    <select id="getItemInfoByItemId" resultType="com.ruixuan.isc.entity.KuaishouItemList"
            parameterType="java.lang.Long">
        <include refid="selectKuaishouItemListVo"/>
        where item_id = #{itemId}
    </select>
    <select id="getCategoryInfo" resultType="com.ruixuan.isc.entity.KuaishouItemCategory"
            parameterType="java.lang.Long">
        select  *  from kuaishou_item_category where  category_id = #{categoryId}

    </select>
    <select id="getIdByName" resultType="java.lang.Long" parameterType="java.lang.String">
        select  category_id from kuaishou_item_category  where  category_name = #{categoryName} and category_sort = 1 limit 1
    </select>
    <select id="getCategoryIds" resultType="com.alibaba.fastjson.JSONObject">
         select
         category_id as 'categoryId' ,
         category_name as 'categoryName'
         from kuaishou_item_category  where  category_sort = 1 order by category_id
    </select>
    <select id="getActivityIds" resultType="java.lang.Long" parameterType="java.lang.Long">
        select activity_id  from kuaishou_item_list where user_id = #{userId} group by activity_id order by activity_id desc
    </select>
    <select id="selectItemInfo" resultType="com.alibaba.fastjson.JSONObject">
        select item_id as 'itemId',
        valid_order_num as 'validOrderNum',
        valid_amount as 'validAmount'
        from item_oerder_amount where item_id in
        <foreach collection="map.values" item="v" separator="," open="(" close=")">
            ${v}
        </foreach>
    </select>
    <select id="getSampleCount" resultType="java.lang.Integer" parameterType="java.lang.Long">
        select  count(1)  from kuaishou_item_collect_samples
        where
        item_id = #{itemId}
        and collect_sample_status  in(
        1,3,4,5,6
        )
    </select>
    <select id="getRegimental" resultType="com.alibaba.fastjson.JSONObject">
        select  id as 'id', reg_name as 'regName'  from kuaishou_access_token
    </select>
    <select id="selectMaxRelateId" resultType="java.lang.Long">
        select  max(relate_id)  from kuaishou_item_list
    </select>
    <select id="getListByRelateId" resultType="com.alibaba.fastjson.JSONObject" parameterType="java.lang.Long">
        select item_id as 'itemId',item_title as 'itemTitle'  from kuaishou_item_list where relate_id = #{relateId} order by create_time desc

    </select>


    <insert id="insertItem" parameterType="com.ruixuan.isc.entity.KuaishouItemList">
        insert into kuaishou_item_list
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="itemId != null">item_id,</if>
            <if test="mediaId != null">media_id,</if>
            <if test="itemTitle != null">item_title,</if>
            <if test="itemPrice != null">item_price,</if>
            <if test="itemDesc != null">item_desc,</if>
            <if test="zkFinalPrice != null">zk_final_price,</if>
            <if test="itemImgUrl != null">item_img_url,</if>
            <if test="itemDescUrls != null">item_desc_urls,</if>
            <if test="shopStar != null">shop_star,</if>
            <if test="mallLogisticsScore != null">mall_logistics_score,</if>
            <if test="activityItemStatus != null">activity_item_status,</if>
            <if test="shopScore != null">shop_score,</if>
            <if test="categoryId != null">category_id,</if>
            <if test="categoryName != null">category_name,</if>
            <if test="itemGalleryUrls != null">item_gallery_urls,</if>
            <if test="identity != null">identity,</if>
            <if test="activityId != null">activity_id,</if>
            <if test="shopTitle != null">shop_title,</if>
            <if test="commissionRate != null">commission_rate,</if>
            <if test="mallServiceScore != null">mall_service_score,</if>
            <if test="mallQualityScore != null">mall_quality_score,</if>
            <if test="skuList != null">sku_list,</if>
            <if test="userId != null">user_id,</if>
            <if test="userName != null">user_name,</if>
            <if test="examineId != null">examine_id,</if>
            <if test="examineName != null">examine_name,</if>
            <if test="examineStatus != null">examine_status,</if>
            <if test="refuseDetail != null">refuse_detail,</if>
            <if test="postArea != null">post_area,</if>
            <if test="noPostArea != null">no_post_area,</if>
            <if test="regimentalPromotionRate != null">regimental_promotion_rate,</if>
            <if test="regimentalPromotion != null">regimental_promotion,</if>
            <if test="sampleCount != null">sample_count,</if>
            <if test="sampleRequirement != null">sample_requirement,</if>
            <if test="deliveryRate != null">delivery_rate,</if>
            <if test="partnerRecommendText != null">partner_recommend_text,</if>
            <if test="detailUrl != null">detail_url,</if>
            <if test="samplesReceived != null">samples_received,</if>
            <if test="samplesPeoples != null">samples_peoples,</if>
            <if test="relateId != null">relate_id,</if>
            <if test="ruleId != null">rule_id,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="itemId != null">#{itemId},</if>
            <if test="mediaId != null">#{mediaId},</if>
            <if test="itemTitle != null">#{itemTitle},</if>
            <if test="itemPrice != null">#{itemPrice},</if>
            <if test="itemDesc != null">#{itemDesc},</if>
            <if test="zkFinalPrice != null">#{zkFinalPrice},</if>
            <if test="itemImgUrl != null">#{itemImgUrl},</if>
            <if test="itemDescUrls != null">#{itemDescUrls},</if>
            <if test="shopStar != null">#{shopStar},</if>
            <if test="mallLogisticsScore != null">#{mallLogisticsScore},</if>
            <if test="activityItemStatus != null">#{activityItemStatus},</if>
            <if test="shopScore != null">#{shopScore},</if>
            <if test="categoryId != null">#{categoryId},</if>
            <if test="categoryName != null">#{categoryName},</if>
            <if test="itemGalleryUrls != null">#{itemGalleryUrls},</if>
            <if test="identity != null">#{identity},</if>
            <if test="activityId != null">#{activityId},</if>
            <if test="shopTitle != null">#{shopTitle},</if>
            <if test="commissionRate != null">#{commissionRate},</if>
            <if test="mallServiceScore != null">#{mallServiceScore},</if>
            <if test="mallQualityScore != null">#{mallQualityScore},</if>
            <if test="skuList != null">#{skuList},</if>
            <if test="userId != null">#{userId},</if>
            <if test="userName != null">#{userName},</if>
            <if test="examineId != null">#{examineId},</if>
            <if test="examineName != null">#{examineName},</if>
            <if test="examineStatus != null">#{examineStatus},</if>
            <if test="refuseDetail != null">#{refuseDetail},</if>
            <if test="postArea != null">#{postArea},</if>
            <if test="noPostArea != null">#{noPostArea},</if>
            <if test="regimentalPromotionRate != null">#{regimentalPromotionRate},</if>
            <if test="regimentalPromotion != null">#{regimentalPromotion},</if>
            <if test="sampleCount != null">#{sampleCount},</if>
            <if test="sampleRequirement != null">#{sampleRequirement},</if>
            <if test="deliveryRate != null">#{deliveryRate},</if>
            <if test="partnerRecommendText != null">#{partnerRecommendText},</if>
            <if test="detailUrl != null">#{detailUrl},</if>
            <if test="samplesReceived != null">#{samplesReceived},</if>
            <if test="samplesPeoples != null">#{samplesPeoples},</if>
            <if test="relateId != null">#{relateId},</if>
            <if test="ruleId != null">#{ruleId},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
        </trim>
    </insert>

    <insert id="insertItemBindUser">
        replace into kuaishou_item_bind_user(
        item_id,
        user_id,
        user_name
        )
        values
            (
            #{itemId},
            #{userId},
            #{userName}
            )
    </insert>

    <insert id="batchCategory">
        replace into kuaishou_item_category(
        category_id,
        category_name,
        parent_id,
        category_sort
        )
        values
        <foreach collection="adds" item="add" separator=",">
            (
            #{add.categoryId},
            #{add.categoryName},
            #{add.parentId},
            #{add.categorySort}
            )
        </foreach>

    </insert>


    <update id="updateKuaishouItemList" parameterType="com.ruixuan.isc.entity.KuaishouItemList">
        update kuaishou_item_list
        <trim prefix="SET" suffixOverrides=",">
            <if test="itemTitle != null">item_title = #{itemTitle},</if>
            <if test="itemPrice != null">item_price = #{itemPrice},</if>
            <if test="itemDesc != null">item_desc = #{itemDesc},</if>
            <if test="zkFinalPrice != null">zk_final_price = #{zkFinalPrice},</if>
            <if test="itemImgUrl != null">item_img_url = #{itemImgUrl},</if>
            <if test="itemDescUrls != null">item_desc_urls = #{itemDescUrls},</if>
            <if test="shopStar != null">shop_star = #{shopStar},</if>
            <if test="mallLogisticsScore != null">mall_logistics_score = #{mallLogisticsScore},</if>
            <if test="activityItemStatus != null">activity_item_status = #{activityItemStatus},</if>
            <if test="shopScore != null">shop_score = #{shopScore},</if>
            <if test="categoryId != null">category_id = #{categoryId},</if>
            <if test="categoryName != null">category_name = #{categoryName},</if>
            <if test="itemGalleryUrls != null">item_gallery_urls = #{itemGalleryUrls},</if>
            <if test="identity != null">identity = #{identity},</if>
            <if test="activityId != null">activity_id = #{activityId},</if>
            <if test="shopTitle != null">shop_title = #{shopTitle},</if>
            <if test="commissionRate != null">commission_rate = #{commissionRate},</if>
            <if test="mallServiceScore != null">mall_service_score = #{mallServiceScore},</if>
            <if test="mallQualityScore != null">mall_quality_score = #{mallQualityScore},</if>
            <if test="skuList != null">sku_list = #{skuList},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="userName != null">user_name = #{userName},</if>
            <if test="examineId != null">examine_id = #{examineId},</if>
            <if test="examineName != null">examine_name = #{examineName},</if>
            <if test="examineStatus != null">examine_status = #{examineStatus},</if>
            <if test="refuseDetail != null">refuse_detail = #{refuseDetail},</if>
            <if test="postArea != null">post_area = #{postArea},</if>
            <if test="noPostArea != null">no_post_area = #{noPostArea},</if>
            <if test="regimentalPromotionRate != null">regimental_promotion_rate = #{regimentalPromotionRate},</if>
            <if test="regimentalPromotion != null">regimental_promotion = #{regimentalPromotion},</if>
            <if test="sampleCount != null">sample_count = #{sampleCount},</if>
            <if test="sampleRequirement != null">sample_requirement = #{sampleRequirement},</if>
            <if test="deliveryRate != null">delivery_rate = #{deliveryRate},</if>
            <if test="partnerRecommendText != null">partner_recommend_text = #{partnerRecommendText},</if>
            <if test="detailUrl != null">detail_url = #{detailUrl},</if>
            <if test="samplesReceived != null">samples_received = #{samplesReceived},</if>
            <if test="samplesPeoples != null">samples_peoples = #{samplesPeoples},</if>
            <if test="relateId != null">relate_id = #{relateId},</if>
            <if test="ruleId != null">rule_id = #{ruleId},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where item_id = #{itemId}
    </update>
    <update id="updateCount">
        update kuaishou_item_list
        set
        samples_received = #{sampleCount},
        samples_peoples = #{userCount}
        where item_id = #{itemId}
    </update>
    <update id="batchItemProcess">
        update kuaishou_item_list set examine_id = #{examineId},examine_name = #{examineName},examine_status = #{status}
        where 1 = 1
        <if test='itemIds != null and itemIds.size() > 0'>
            AND item_id in
            <foreach collection="itemIds" item="itemId" separator=","
                     open="(" close=")">
                #{itemId}
            </foreach>
        </if>
    </update>
    <delete id="deleteKuaishouItemListByItemIds" parameterType="java.lang.Long">
        delete from kuaishou_item_list where item_id = #{itemId}
    </delete>
    <delete id="removeById" parameterType="java.lang.Long">
         delete from kuaishou_item_list where item_id = #{itemId}
    </delete>
</mapper>