<?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.KuaishouItemCollectSamplesMapper">

    <resultMap type="com.ruixuan.isc.entity.KuaishouItemCollectSamples" id="KuaishouItemCollectSamplesResult">
        <result property="id" column="id"/>
        <result property="mediaId" column="media_id"/>
        <result property="itemId" column="item_id"/>
        <result property="itemTitle" column="item_title"/>
        <result property="itemPrice" column="item_price"/>
        <result property="itemImgUrl" column="item_img_url"/>
        <result property="itemDesc" column="item_desc"/>
        <result property="commissionRate" column="commission_rate"/>
        <result property="regimentalPromotion" column="regimental_promotion"/>
        <result property="sampleCount" column="sample_count"/>
        <result property="sampleRequirement" column="sample_requirement"/>
        <result property="itemCreateId" column="item_create_id"/>
        <result property="itemCreateName" column="item_create_name"/>
        <result property="collectSampleId" column="collect_sample_id"/>
        <result property="collectSampleName" column="collect_sample_name"/>
        <result property="partnerRecommendText" column="partner_recommend_text"/>
        <result property="promoterId" column="promoter_id"/>
        <result property="promoterNickName" column="promoter_nick_name"/>
        <result property="promoterUrl" column="promoter_url"/>
        <result property="promoterPhone" column="promoter_phone"/>
        <result property="promoterAddress" column="promoter_address"/>
        <result property="consignee" column="consignee"/>
        <result property="courierNumber" column="courier_number"/>
        <result property="courierNumberTime" column="courier_number_time"/>
        <result property="taskFileUrl" column="task_file_url"/>
        <result property="collectSampleStatus" column="collect_sample_status"/>
        <result property="collectSampleDesc" column="collect_sample_desc"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="sampleVoucherStatus" column="sample_voucher_status"/>
        <result property="sampleVoucher" column="sample_voucher"/>

    </resultMap>

    <sql id="selectKuaishouItemCollectSamplesVo">
        select id,
               media_id,
               item_id,
               item_title,
               item_price,
               item_img_url,
               item_desc,
               commission_rate,
               regimental_promotion,
               sample_count,
               sample_requirement,
               item_create_id,
               item_create_name,
               collect_sample_id,
               collect_sample_name,
               partner_recommend_text,
               promoter_id,
               promoter_nick_name,
               promoter_url,
               promoter_phone,
               promoter_address,
               consignee,
               courier_number,
               courier_number_time,
               task_file_url,
               collect_sample_status,
               collect_sample_desc,
               create_time,
               update_time,
               sample_voucher_status,
               sample_voucher

        from kuaishou_item_collect_samples
    </sql>

    <select id="selectKuaishouItemCollectSamplesList" parameterType="com.ruixuan.isc.entity.KuaishouItemCollectSamples"
            resultMap="KuaishouItemCollectSamplesResult">
        select t1.id,
        t1.media_id,
        t1.item_id,
        t1.item_title,
        t1.item_price,
        t1.item_img_url,
        t1.item_desc,
        t1.commission_rate,
        t1.regimental_promotion,
        t1.sample_count,
        t1.sample_requirement,
        t1.item_create_id,
        t1.item_create_name,
        t1.collect_sample_id,
        t1.collect_sample_name,
        t1.partner_recommend_text,
        t1.promoter_id,
        t1.promoter_nick_name,
        t1.promoter_url,
        t1.promoter_phone,
        t1.promoter_address,
        t1.consignee,
        t1.courier_number,
        t1.courier_number_time,
        t1.task_file_url,
        t1.collect_sample_status,
        t1.collect_sample_desc,
        t1.create_time,
        t1.update_time,
        t2.valid_order_num as 'promoterOrderNum',
        t1.sample_voucher_status,
        t1.sample_voucher
        from kuaishou_item_collect_samples t1 left join promoter_iterm_oerder_amount t2
        on t1.item_id = t2.item_id
        and t1.promoter_id = t2.promoter_id
        <where>
            <if test="itemId != null ">and t1.item_id = #{itemId}</if>
            <if test="promoterId != null ">and t1.promoter_id = #{promoterId}</if>
            <if test="collectSampleStatus != null ">and t1.collect_sample_status = #{collectSampleStatus}</if>
            <if test="itemCreateId != null and itemCreateId != ''">
                and t1.item_create_id = #{itemCreateId}
            </if>
            <if test='itemCreateIds != null and itemCreateIds.size() > 0'>
                AND t1.item_create_id in
                <foreach collection="itemCreateIds" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="collectSampleId != null and collectSampleId != ''">
                and t1.collect_sample_id = #{collectSampleId}
            </if>
            <if test='collectSampleIds != null and collectSampleIds.size() > 0'>
                AND t1.collect_sample_id in
                <foreach collection="collectSampleIds" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="itemTitle != null and itemTitle != ''">
                and t1.item_title like concat('%', #{itemTitle}, '%')
            </if>
            <if test="promoterNickName != null and promoterNickName != ''">
                and t1.promoter_nick_name like concat('%', #{promoterNickName}, '%')
            </if>
            <if test="promoterPhone != null and promoterPhone != ''">
                and t1.promoter_phone = #{promoterPhone}
            </if>
            <if test="consignee != null and consignee != ''">
                and t1.consignee like concat('%', #{consignee}, '%')
            </if>
            <if test="itemCreateId != null and itemCreateId != ''">
                and t1.item_create_id = #{itemCreateId}
            </if>
            <if test="collectSampleId != null and collectSampleId != ''">
                and t1.collect_sample_id = #{collectSampleId}
            </if>
            <if test="courierNumber != null and courierNumber != ''">
                and t1.courier_number = #{courierNumber}
            </if>
            <if test="collectSampleDesc != null and collectSampleDesc != ''">
                and t1.collect_sample_desc = #{collectSampleDesc}
            </if>
            <if test="mediaId != null and mediaId != ''">
                and t1.media_id = #{mediaId}
            </if>
        </where>
        order by ${fieId} ${sort}
    </select>

    <select id="selectKuaishouItemCollectSamplesById" parameterType="java.lang.Long"
            resultMap="KuaishouItemCollectSamplesResult">
        <include refid="selectKuaishouItemCollectSamplesVo"/>
        where id = #{id}
    </select>
    <select id="getItemList" resultType="com.alibaba.fastjson.JSONObject"
            parameterType="com.alibaba.fastjson.JSONArray">
        select
        item_id as 'itemId',
        media_id as 'mediaId',
        item_title as 'itemTitle',
        item_price as 'itemPrice',
        item_img_url as 'itemImgUrl',
        regimental_promotion as 'regimentalPromotion',
        commission_rate as 'commissionRate',
        user_id as 'userId',
        user_name as 'userName',
        relate_id as 'relateId'
        from kuaishou_item_list
        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>
        group by item_id
    </select>
    <select id="promoterList" resultType="com.alibaba.fastjson.JSONObject"
            parameterType="com.alibaba.fastjson.JSONArray">
        select
        id as 'id',
        media_id as 'mediaId',
        promoter_id as 'promoterId',
        promoter_nick_name as 'promoterNickName',
        promoter_url as 'promoterUrl',
        consignee as 'consignee',
        promoter_address as 'promoterAddress',
        phone as 'promoterPhone'
        from kuaishou_promoter
        where 1 =1
        <if test='ids != null and ids.size() > 0'>
            AND id in
            <foreach collection="ids" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
    </select>
    <select id="check" resultType="java.lang.Integer">
        select count(1)
        from kuaishou_item_collect_samples
        where item_id = #{itemId}
          and promoter_id = #{promoterId}
          and collect_sample_status != 2
    </select>
    <select id="getExpress" resultType="com.ruixuan.isc.entity.KuaishouItemCollectSampleExpress"
            parameterType="java.lang.String">
        select *
        from kuaishou_item_collect_sample_express
        where courier_number = #{courierNumber}

    </select>
    <select id="getStatusByCourierNumbe" resultType="java.lang.Integer" parameterType="java.lang.String">
        select collect_sample_status
        from kuaishou_item_collect_samples
        where courier_number = #{nu}
        order by update_time desc limit 1
    </select>
    <select id="courierNumberInfo" resultType="com.alibaba.fastjson.JSONObject"
            parameterType="java.lang.String">
        select courier_number as 'courierNumber', state as 'state', express_data as 'expressData', create_time as 'createTime', update_time as 'updateTime'
        from kuaishou_item_collect_sample_express
        where courier_number = #{courierNumber}
    </select>
    <select id="getLogList" resultType="com.ruixuan.isc.entity.KuaishouItemCollectSampleLog"
            parameterType="java.lang.Long">
        select *
        from kuaishou_item_collect_sample_log
        where sample_id = #{sampleId}
        order by collect_sample_status asc
    </select>
    <select id="exportCollectSamples" resultType="com.alibaba.fastjson.JSONObject">
        select
        id as 'id',
        item_id as 'itemId',
        item_title as 'itemTitle',
        promoter_id as 'promoterId',
        promoter_nick_name as 'promoterNickName',
        (
        case
        when collect_sample_status = 1 then '待审核'
        when collect_sample_status = 2 then '审核拒绝'
        when collect_sample_status = 3 then '待录入单号'
        when collect_sample_status = 4 then '已发货'
        when collect_sample_status = 5 then '快递已签收，待上传作业'
        when collect_sample_status = 6 then '作业已上传，待招商审核作业'
        when collect_sample_status = 7 then '审核通过'
        else ''
        end
        ) as 'status',
        sample_count as 'sampleCount',
        sample_requirement as 'sampleRequirement',
        consignee as 'consignee',
        promoter_phone as 'promoterPhone',
        promoter_address as 'promoterAddress'
        from kuaishou_item_collect_samples
        where 1= 1
        <if test='ids != null and ids.size() > 0'>
            AND id in
            <foreach collection="ids" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        order by create_time desc
    </select>
    <select id="getCollectSampleInfo" resultType="com.alibaba.fastjson.JSONObject"
            parameterType="java.lang.Long">
        select count(id) as 'sampleCount', count(distinct collect_sample_id) as 'userCount'
        from kuaishou_item_collect_samples
        where item_id = #{itemId}
          and collect_sample_status >= 3
    </select>
    <select id="promoterInfo" resultType="com.alibaba.fastjson.JSONObject" parameterType="java.lang.String">
        select total_sale as 'totalSale', fans_number as 'fansNumber'
        from kuaishou_promoter
        where promoter_id = #{promoterId} limit 1
    </select>

    <select id="getItemIdById" resultType="java.lang.Long" parameterType="java.lang.Long">
        select item_id
        from kuaishou_item_collect_samples
        where id = #{id}
    </select>

    <insert id="insertKuaishouItemCollectSamples" parameterType="com.ruixuan.isc.entity.KuaishouItemCollectSamples">
        insert into kuaishou_item_collect_samples
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <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="itemImgUrl != null">item_img_url,</if>
            <if test="itemDesc != null">item_desc,</if>
            <if test="commissionRate != null">commission_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="itemCreateId != null">item_create_id,</if>
            <if test="itemCreateName != null">item_create_name,</if>
            <if test="collectSampleId != null">collect_sample_id,</if>
            <if test="collectSampleName != null">collect_sample_name,</if>
            <if test="partnerRecommendText != null">partner_recommend_text,</if>
            <if test="promoterId != null">promoter_id,</if>
            <if test="promoterNickName != null">promoter_nick_name,</if>
            <if test="promoterUrl != null">promoter_url,</if>
            <if test="promoterPhone != null">promoter_phone,</if>
            <if test="promoterAddress != null">promoter_address,</if>
            <if test="consignee != null">consignee,</if>
            <if test="courierNumber != null">courier_number,</if>
            <if test="courierNumberTime != null">courier_number_time,</if>
            <if test="taskFileUrl != null">task_file_url,</if>
            <if test="collectSampleStatus != null">collect_sample_status,</if>
            <if test="collectSampleDesc != null">collect_sample_desc,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="sampleVoucher != null">sample_voucher,</if>
            <if test="sampleVoucherStatus != null">sample_voucher_status,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <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="itemImgUrl != null">#{itemImgUrl},</if>
            <if test="itemDesc != null">#{itemDesc},</if>
            <if test="commissionRate != null">#{commissionRate},</if>
            <if test="regimentalPromotion != null">#{regimentalPromotion},</if>
            <if test="sampleCount != null">#{sampleCount},</if>
            <if test="sampleRequirement != null">#{sampleRequirement},</if>
            <if test="itemCreateId != null">#{itemCreateId},</if>
            <if test="itemCreateName != null">#{itemCreateName},</if>
            <if test="collectSampleId != null">#{collectSampleId},</if>
            <if test="collectSampleName != null">#{collectSampleName},</if>
            <if test="partnerRecommendText != null">#{partnerRecommendText},</if>
            <if test="promoterId != null">#{promoterId},</if>
            <if test="promoterNickName != null">#{promoterNickName},</if>
            <if test="promoterUrl != null">#{promoterUrl},</if>
            <if test="promoterPhone != null">#{promoterPhone},</if>
            <if test="promoterAddress != null">#{promoterAddress},</if>
            <if test="consignee != null">#{consignee},</if>
            <if test="courierNumber != null">#{courierNumber},</if>
            <if test="courierNumberTime != null">#{courierNumberTime},</if>
            <if test="taskFileUrl != null">#{taskFileUrl},</if>
            <if test="collectSampleStatus != null">#{collectSampleStatus},</if>
            <if test="collectSampleDesc != null">#{collectSampleDesc},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="sampleVoucher != null">#{sampleVoucher},</if>
            <if test="sampleVoucher_status != null">#{sampleVoucherStatus},</if>
        </trim>
    </insert>
    <insert id="batchAdd">
        insert into kuaishou_item_collect_samples(
        item_id,
        media_id,
        item_title,
        item_price,
        item_img_url,
        item_desc,
        commission_rate,
        regimental_promotion,
        sample_count,
        sample_requirement,
        item_create_id,
        item_create_name,
        collect_sample_id,
        collect_sample_name,
        partner_recommend_text,
        promoter_id,
        promoter_nick_name,
        promoter_phone,
        promoter_address,
        consignee,
        courier_number,
        courier_number_time,
        task_file_url,
        promoter_url,
        collect_sample_status,
        collect_sample_desc,
        sample_voucher,
        sample_voucher_status
        )
        values
        <foreach collection="adds" item="add" separator=",">
            (
            #{add.itemId},
            #{add.mediaId},
            #{add.itemTitle},
            #{add.itemPrice},
            #{add.itemImgUrl},
            #{add.itemDesc},
            #{add.commissionRate},
            #{add.regimentalPromotion},
            #{add.sampleCount},
            #{add.sampleRequirement},
            #{add.itemCreateId},
            #{add.itemCreateName},
            #{add.collectSampleId},
            #{add.collectSampleName},
            #{add.partnerRecommendText},
            #{add.promoterId},
            #{add.promoterNickName},
            #{add.promoterPhone},
            #{add.promoterAddress},
            #{add.consignee},
            #{add.courierNumber},
            #{add.courierNumberTime},
            #{add.taskFileUrl},
            #{add.promoterUrl},
            #{add.collectSampleStatus },
            #{add.collectSampleDesc},
            #{add.sampleVoucher},
            #{add.sampleVoucherStatus}
            )
        </foreach>

    </insert>
    <insert id="replaceExpress" parameterType="com.ruixuan.isc.entity.KuaishouItemCollectSampleExpress">
        replace
        into kuaishou_item_collect_sample_express(
            courier_number,
            com,
            state,
            express_data
        )
        values
       (
        #{courierNumber},
        #{com},
        #{state},
        #{expressData}
        )
    </insert>
    <insert id="insertLog" parameterType="com.ruixuan.isc.entity.KuaishouItemCollectSampleLog">
        replace
        into kuaishou_item_collect_sample_log(
            sample_id,
            user_id,
            user_name,
            collect_sample_status,
            create_time,
            update_time
        )
        values
       (
        #{sampleId},
        #{userId},
        #{userName},
        #{collectSampleStatus},
        #{createTime},
        #{updateTime}
        )

    </insert>
    <insert id="batchAddLogs">
        replace into kuaishou_item_collect_sample_log(
        sample_id,
        user_id,
        user_name,
        collect_sample_status
        )
        values
        <foreach collection="adds" item="add" separator=",">
            (
            #{add.sampleId},
            #{add.userId},
            #{add.userName},
            #{add.collectSampleStatus}
            )
        </foreach>

    </insert>

    <update id="updateKuaishouItemCollectSamples" parameterType="com.ruixuan.isc.entity.KuaishouItemCollectSamples">
        update kuaishou_item_collect_samples
        <trim prefix="SET" suffixOverrides=",">
            <if test="itemId != null">item_id = #{itemId},</if>
            <if test="itemTitle != null">item_title = #{itemTitle},</if>
            <if test="itemPrice != null">item_price = #{itemPrice},</if>
            <if test="itemImgUrl != null">item_img_url = #{itemImgUrl},</if>
            <if test="itemDesc != null">item_desc = #{itemDesc},</if>
            <if test="commissionRate != null">commission_rate = #{commissionRate},</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="itemCreateId != null">item_create_id = #{itemCreateId},</if>
            <if test="itemCreateName != null">item_create_name = #{itemCreateName},</if>
            <if test="collectSampleId != null">collect_sample_id = #{collectSampleId},</if>
            <if test="collectSampleName != null">collect_sample_name = #{collectSampleName},</if>
            <if test="partnerRecommendText != null">partner_recommend_text = #{partnerRecommendText},</if>
            <if test="promoterId != null">promoter_id = #{promoterId},</if>
            <if test="promoterNickName != null">promoter_nick_name = #{promoterNickName},</if>
            <if test="promoterUrl != null">promoter_url = #{promoterUrl},</if>
            <if test="promoterPhone != null">promoter_phone = #{promoterPhone},</if>
            <if test="promoterAddress != null">promoter_address = #{promoterAddress},</if>
            <if test="consignee != null">consignee = #{consignee},</if>
            <if test="courierNumber != null">courier_number = #{courierNumber},</if>
            <if test="courierNumberTime != null">courier_number_time = #{courierNumberTime},</if>
            <if test="taskFileUrl != null">task_file_url = #{taskFileUrl},</if>
            <if test="collectSampleStatus != null">collect_sample_status = #{collectSampleStatus},</if>
            <if test="collectSampleDesc != null">collect_sample_desc = #{collectSampleDesc},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="sampleVoucher != null">sample_voucher = #{sampleVoucher},</if>
            <if test="sampleVoucherStatus != null">sample_voucher_status = #{sampleVoucherStatus},</if>
        </trim>
        where id = #{id}
    </update>
    <update id="updateByNum" parameterType="com.ruixuan.isc.entity.KuaishouItemCollectSamples">
        update kuaishou_item_collect_samples
        <trim prefix="SET" suffixOverrides=",">
            <if test="collectSampleStatus != null">collect_sample_status = #{collectSampleStatus},</if>
            <if test="collectSampleDesc != null">collect_sample_desc = #{collectSampleDesc}</if>
        </trim>
        where courier_number = #{courierNumber}

    </update>
    <update id="batchProcess">
        update kuaishou_item_collect_samples set collect_sample_status = #{status}
        where 1 = 1
        <if test='ids != null and ids.size() > 0'>
            AND id in
            <foreach collection="ids" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>

    </update>

    <delete id="deleteKuaishouItemCollectSamplesById" parameterType="java.lang.Long">
        delete
        from kuaishou_item_collect_samples
        where id = #{id}
    </delete>

    <delete id="deleteKuaishouItemCollectSamplesByIds" parameterType="java.lang.String">
        delete from kuaishou_item_collect_samples where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <delete id="cleanLogs">
        delete
        from kuaishou_item_collect_sample_log
        where sample_id = #{sampleId}
          and collect_sample_status &gt; #{status}
    </delete>


    <select id="getSampleCountByUserId" resultType="java.lang.Integer">
        select count(1) as 'sampleCount'
        from kuaishou_item_collect_samples
        where collect_sample_status = #{status}
        <if test="userList!=null and userType == 1">
            AND collect_sample_id in
            <foreach collection="userList" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="userList!=null and userType == 2">
            AND item_create_id in
            <foreach collection="userList" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>


    <!--     完成作业数/合格作业率-->
    <select id="queryTheNumberOfCompletedJobs" resultType="com.alibaba.fastjson.JSONObject">
        SELECT completeCount, allCount,
        IFNULL(CONCAT(ROUND(completeCount / allCount * 100, 2), '%'), '0%') as 'completeRate'
        FROM (SELECT IFNULL(SUM(sample_count), 0) as 'completeCount'
        FROM kuaishou_item_collect_samples
        WHERE DATE_FORMAT(create_time, '%Y-%m-%d') >= #{startDate}
        AND DATE_FORMAT(create_time, '%Y-%m-%d') &lt;= #{endDate}
        AND collect_sample_status = 7
        AND media_id =#{mediaId}
        <if test="promoterIds != null and promoterIds.size() > 0 ">
            AND promoter_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t1
        LEFT JOIN
        (SELECT IFNULL(SUM(sample_count), 0) as 'allCount'
        FROM kuaishou_item_collect_samples
        WHERE DATE_FORMAT(create_time, '%Y-%m-%d') >= #{startDate}
        AND DATE_FORMAT(create_time, '%Y-%m-%d') &lt;= #{endDate}
        AND media_id =#{mediaId}
        <if test="promoterIds != null and promoterIds.size() > 0 ">
            AND promoter_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t2 on 1 = 1
    </select>

    <select id="selectPromoterInfo" resultType="com.alibaba.fastjson.JSONObject">
        select promoter_id as 'promoterId',
        valid_order_num as 'validOrderNum',
        valid_amount as 'validAmount'
        from promoter_oerder_amount where promoter_id in
        <foreach collection="map.values" item="v" separator="," open="(" close=")">
            #{v, jdbcType=VARCHAR}
        </foreach>
    </select>

    <select id="getItemIdsByCreateId" resultType="java.lang.Long">
        select item_id
        from kuaishou_item_collect_samples
        <where>
            <if test="userList != null and userList.size() > 0 ">
                AND item_create_id in
                <foreach collection="userList" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="mediaId != null and mediaId !='' ">
                AND media_id =#{mediaId}
            </if>
        </where>
    </select>

    <select id="getListByPromoterId" resultMap="KuaishouItemCollectSamplesResult">
        <include refid="selectKuaishouItemCollectSamplesVo"/>
        where promoter_id = #{promoterId}
        and collect_sample_id = #{userId}
        and collect_sample_status not in(2,7)
    </select>

    <select id="queryProductList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT * from (SELECT
        promoter_id as 'promoterId',
        promoter_nick_name as 'promoterNickName',
        item_id as 'itemId',
        item_title as 'itemTitle',
        reserve_price as 'reservePrice',
        SUM(order_amount) as 'orderAmount',
        COUNT(case WHEN order_status!=80 then 1 ELSE null END) as 'validorder',
        regimental_promotion_amount as 'regimentalPromotionAmount'
        FROM kuaishou_supply_chain
        <where>
            <if test="startDate != null and startDate != ''">
                AND stat_date >= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                AND stat_date &lt;= #{endDate}
            </if>
            <if test="itemId != null and itemId != ''">
                AND item_id = #{itemId}
            </if>
            <if test="itemTitle != null and itemTitle != ''">
                AND item_title LIKE CONCAT('%', #{itemTitle}, '%')
            </if>
            <if test="promoterNickName != null and promoterNickName != ''">
                AND promoter_nick_name LIKE CONCAT('%', #{promoterNickName}, '%')
            </if>
            <if test="promoterId != null and promoterId != ''">
                AND promoter_id = #{promoterId}
            </if>
            <if test="reservePriceStart != null and reservePriceStart != ''">
                AND reserve_price >= #{reservePriceStart}
            </if>
            <if test="reservePriceEnd != null and reservePriceEnd != ''">
                AND reserve_price &lt;= #{reservePriceEnd}
            </if>
        </where>
        GROUP BY item_id,promoter_id
        ORDER BY create_time DESC
        )t
        where 1=1
        <if test="validorderStart != null and validorderStart != ''">
            AND validorder >= #{validorderStart}
        </if>
        <if test="validorderEnd != null and validorderEnd != ''">
            AND validorder &lt;= #{validorderEnd}
        </if>
    </select>

    <select id="checkByRelateIdAndPromoterId" resultType="java.lang.Integer">
        select count(1)
        from kuaishou_item_collect_samples
        where item_id in (
            select item_id
            from kuaishou_item_list
            where relate_id = #{relateId}
            group by item_id
        )
          and promoter_id = #{promoterId}
          and collect_sample_status != 2

    </select>

    <!--    出单量 >= 50 且 状态为5已签收待上传作业 的数据-->
    <select id="getAccordWithOrderQuantityList" resultType="java.lang.Long">
        SELECT t1.id
        FROM kuaishou_item_collect_samples t1
                 LEFT JOIN promoter_iterm_oerder_amount t2
                           ON t1.item_id = t2.item_id and t1.promoter_id = t2.promoter_id
        WHERE t1.collect_sample_status = 5
          AND t2.valid_order_num >= 50
    </select>

    <!--    出单量 >= 50 且 状态为5已签收待上传作业 的数据 改为 状态 7（审核通过）并备注-->
    <update id="updateByIds" parameterType="com.ruixuan.isc.entity.KuaishouItemCollectSamples">
        update kuaishou_item_collect_samples
        SET
        collect_sample_status = 7,
        collect_sample_desc = '出单量大于等于50单，自动审核通过'
        WHERE id IN
        <foreach collection="ids" item="id" separator=","
                 open="(" close=")">
            #{id}
        </foreach>
    </update>
</mapper>