<?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.SupplyChainMapper">
    <insert id="bindUser">
        insert into kuaishou_item_bind_user(item_id, user_id, user_name)
            value
            (#{itemId},#{userId},#{nikeName})
    </insert>
    <delete id="unbindUser" parameterType="java.lang.Long">
        delete
        from kuaishou_item_bind_user
        where item_id = #{itemId}
    </delete>


    <select id="getOrderList" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from (
        select promoter_id as 'promoterId',
        promoter_nick_name as 'promoterNickName',
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when order_status != 80 then 1 else 0 end) / count(oid),4) as 'validRadio',
        sum(pay_amount) as 'payAmount',
        sum(case when order_status != 80 then pay_amount else 0 end) as 'validPayAmount',
        sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when send_status = 0 and order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) / sum(case when order_status != 80
        then 1 else 0 end),4) as 'deliveryRadio',
        sum(case when order_status != 80 then regimental_promotion_amount else 0 end) as 'regimentalPromotionAmount'
        from kuaishou_supply_chain
        where 1= 1
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>
        <if test="promoterId != null and promoterId != ''">
            and promoter_id = #{promoterId}
        </if>
        <if test="promoterNickName != null and promoterNickName != ''">
            and promoter_nick_name like concat(concat('%',#{promoterNickName}),'%')
        </if>
        group by promoter_id) t
        order by t.orderNum desc
    </select>
    <select id="getTopOrders" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from
        (select item_id as 'itemId',
        item_title as 'itemTitle',
        image_url as 'imageUrl',
        count(oid) as 'orderNum'
        from kuaishou_supply_chain
        where promoter_id = #{promoterId}
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>
        group by item_id) t
        order by t.orderNum desc limit 3
    </select>
    <select id="anchorOrderDetail" resultType="com.alibaba.fastjson.JSONObject">
        select t.*,
        t2.status,
        (
        case
        when t3.activity_item_status = 1 then '待审核'
        when t3.activity_item_status = 2 then '已上架'
        when t3.activity_item_status = 3 then '审核拒绝'
        when t3.activity_item_status = 4 then '活动失效'
        when t3.activity_item_status = 5 then '商品下架'
        else ''
        end
        ) as 'activityItemStatus'
        from (select
        item_title as 'itemTitle',
        item_id as 'itemId',
        reserve_price as 'reservePrice',
        image_url 'imageUrl',
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when order_status != 80 then 1 else 0 end) / count(oid),4) as 'validRadio',
        sum(case when order_status != 80 then pay_amount else 0 end) as 'payAmount',
        regimental_promotion_rate as 'regimentalPromotionRate',
        sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when send_status = 0 and order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) / sum(case when order_status != 80
        then 1 else 0 end),4) as 'deliveryRadio'
        from kuaishou_supply_chain
        where 1= 1
        <if test="promoterId != null and promoterId != ''">
            and promoter_id = #{promoterId}
        </if>

        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>
        <if test="itemId != null and itemId != ''">
            and item_id = #{itemId}
        </if>
        <if test="itemTitle != null and itemTitle != ''">
            and item_title like concat(concat('%',#{itemTitle}),'%')
        </if>
        group by item_id) t left join ( select item_id as 'itemId', collect_sample_status as 'status' from
        kuaishou_item_collect_samples where promoter_id = #{promoterId}) t2
        on t.itemId = t2.itemId
        left join kuaishou_item_list t3
        on t.itemId = t3.item_id

        order by t.orderNum desc
    </select>
    <select id="orderStatistics" resultType="com.alibaba.fastjson.JSONObject">
        select FROM_UNIXTIME(t.order_create_time / 1000, '%y-%m-%d') as 'date',
        count(t.oid) as 'orderNum'
        from (select oid,
        order_create_time
        from kuaishou_supply_chain
        where 1 = 1
        <if test="promoterId != null and promoterId != ''">
            and promoter_id = #{promoterId}
        </if>
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>
        ) t
        group by FROM_UNIXTIME(t.order_create_time / 1000, '%y-%m-%d')
        order by FROM_UNIXTIME(t.order_create_time / 1000, '%y-%m-%d') asc
    </select>
    <select id="itemList" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from (select t2.user_id as 'userId',
        t2.user_name as 'userName',
        t1.item_id as 'itemId',
        t1.reserve_price as 'reservePrice',
        t1.item_title as 'itemTitle',
        t1.image_url as 'imageUrl',
        count(t1.oid) as 'orderNum',
        sum(case when t1.order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when t1.order_status != 80 then 1 else 0 end) / count(t1.oid), 4) as 'validRadio',
        sum(case when t1.send_status = 1 and t1.order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when t1.send_status = 0 and t1.order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when t1.send_status = 1 and t1.order_status != 80 then 1 else 0 end) / sum(case when
        t1.order_status != 80 then 1 else 0 end), 4) as 'deliveryRadio',
        t1.item_commission_rate as 'itemCommissionRate'
        from kuaishou_supply_chain t1
        left join kuaishou_item_bind_user t2
        on t1.item_id = t2.item_id
        where 1 = 1
        <if test="start != null and start != ''">
            and t1.order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and t1.order_create_time &lt;= #{end}
        </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(concat('%',#{itemTitle}),'%')
        </if>
        group by t1.item_id) t
        where 1 = 1
        <if test="userId != null and userId != ''">
            and t.userId = #{userId}
        </if>
        order by ${fieId} ${sort}
    </select>


    <select id="itemBindList" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from (select item_id as 'itemId', item_title as 'itemTitle', user_id as 'userId', user_name as 'userName'
        from kuaishou_item_list
        where
        create_time &gt;= str_to_date(concat(#{bindStartDate},' 00:00:01'),'%Y-%m-%d
        %H:%i:%s')
        and
        create_time &lt;= str_to_date(concat(#{bindEndDate},' 23:59:59'),'%Y-%m-%d
        %H:%i:%s')
        ) t1
        left join
        (select item_id as 'itemId',
        reserve_price as 'reservePrice',
        item_title as 'itemTitle',
        image_url as 'imageUrl',
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when order_status != 80 then 1 else 0 end) / count(oid),
        4) as 'validRadio',
        sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when send_status = 0 and order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) / sum(case
        when
        order_status != 80
        then 1
        else 0 end),
        4) as 'deliveryRadio',
        item_commission_rate as 'itemCommissionRate'
        from kuaishou_supply_chain t1
        where order_create_time &gt;= #{start}
        group by item_id) t2 on t1.itemId = t2.itemId
        where 1 = 1
        <if test="itemId != null and itemId != ''">
            and t1.itemId = #{itemId}
        </if>
        <if test="itemTitle != null and itemTitle != ''">
            and t1.itemTitle like concat(concat('%',#{itemTitle}),'%')
        </if>
        <if test="userId != null and userId != ''">
            and t1.userId = #{userId}
        </if>
        order by ${fieId} ${sort}

    </select>


    <select id="getTopPromoterByItemId" resultType="com.alibaba.fastjson.JSONObject">
        select
        *
        from
        (select promoter_id as 'promoterId',
        promoter_nick_name as 'promoterNickName',
        count(oid) as 'orderNum'
        from kuaishou_supply_chain
        where item_id = #{itemId}
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>
        group by promoter_id) t
        order by t.orderNum desc limit 3
    </select>

    <select id="itemDetail" resultType="com.alibaba.fastjson.JSONObject">
        select t.*,tt.userName
        from (
        select
        promoter_nick_name as 'promoterNickName',
        promoter_id as 'promoterId',
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then pay_amount else 0 end) as 'payAmount',
        regimental_promotion_rate as 'regimentalPromotionRate',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when order_status != 80 then 1 else 0 end) / count(oid),4) as 'validRadio',
        sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when send_status = 0 and order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) / sum(case when order_status != 80
        then 1 else 0 end),4) as 'deliveryRadio'
        from kuaishou_supply_chain
        where 1= 1
        <if test="itemId != null and itemId != ''">
            and item_id = #{itemId}
        </if>
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>
        <if test="promoterId != null and promoterId != ''">
            and promoter_id = #{promoterId}
        </if>
        <if test="promoterNickName != null and promoterNickName != ''">
            and promoter_nick_name like concat(concat('%',#{promoterNickName}),'%')
        </if>
        group by promoter_id) t left join (select promoter_id,group_concat(user_name) as 'userName' from
        kuaishou_promoter group by promoter_id) tt
        on t.promoterId = tt.promoter_id
        order by t.orderNum desc
    </select>
    <select id="itemServiceCharge" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from (
        select
        item_id as 'itemId',
        item_title as 'itemTitle',
        image_url as 'imageUrl',
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when order_status != 80 then 1 else 0 end) / count(oid),4) as 'validRadio',
        sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when send_status = 0 and order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) / sum(case when order_status != 80
        then 1 else 0 end),4) as 'deliveryRadio',
        sum(case when order_status = 80 then 1 else 0 end) as 'invalidOrderNUm',
        regimental_promotion_rate as 'regimentalPromotionRate',
        sum(case when order_status != 80 then pay_amount else 0 end) as 'payAmount',
        sum(case when order_status != 80 then regimental_promotion_amount else 0 end) as 'regimentalPromotionAmount',
        sum(case when order_status != 80 then total_regimental_settle_amount else 0 end) as
        'totalRegimentalSettleAmount',
        item_commission_rate as 'itemCommissionRate'
        from kuaishou_supply_chain
        where 1= 1
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>
        <if test="itemId != null and itemId != ''">
            and item_id = #{itemId}
        </if>
        <if test="itemTitle != null and itemTitle != ''">
            and item_title like concat(concat('%',#{itemTitle}),'%')
        </if>
        group by item_id) t
        order by t.orderNum desc
    </select>
    <select id="getOrderTotal" resultType="com.alibaba.fastjson.JSONObject">
        select
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        sum(case when order_status = 80 then 1 else 0 end) as 'invalidOrderNUm'
        from kuaishou_supply_chain
        where 1= 1
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>

    </select>
    <select id="getOrderTotalRatio" resultType="java.lang.Double">
        select
        round((t1.oid -t2.oid) / t2.oid , 4) as 'orderTotalRatio'
        from (select count(oid) as 'oid'
        from kuaishou_supply_chain
        where 1= 1
        <if test="nowStartHourTemp != null and nowStartHourTemp != ''">
            and order_create_time &gt;= #{nowStartHourTemp}
        </if>
        <if test="nowEndHourTemp != null and nowEndHourTemp != ''">
            and order_create_time &lt;= #{nowEndHourTemp}
        </if>
        ) t1
        join (select count(oid) as 'oid'
        from kuaishou_supply_chain
        where 1=1
        <if test="yesterdayStartHourTemp != null and yesterdayStartHourTemp != ''">
            and order_create_time &gt;= #{yesterdayStartHourTemp}
        </if>
        <if test="yesterdayEndHourTemp != null and yesterdayEndHourTemp != ''">
            and order_create_time &lt;= #{yesterdayEndHourTemp}
        </if>
        ) t2

    </select>
    <select id="getAmountTotal" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(order_amount) as 'orderAmount',
        sum(base_amount) as 'baseAmount',
        sum(case when order_status != 80 then regimental_promotion_amount else 0 end) as 'regimentalPromotionAmount',
        sum(case when order_status != 80 then total_regimental_settle_amount else 0 end) as
        'totalRegimentalSettleAmount'
        from kuaishou_supply_chain
        where 1= 1
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>

    </select>
    <select id="getAmountTotalRatio" resultType="java.lang.Double">
        select
        round((t1.payAmount -t2.payAmount) / t2.payAmount , 4) as 'amountTotalRatio'
        from (select sum(pay_amount) as 'payAmount'
        from kuaishou_supply_chain
        where 1= 1
        <if test="nowStartHourTemp != null and nowStartHourTemp != ''">
            and order_create_time &gt;= #{nowStartHourTemp}
        </if>
        <if test="nowEndHourTemp != null and nowEndHourTemp != ''">
            and order_create_time &lt;= #{nowEndHourTemp}
        </if>
        ) t1
        join (select sum(pay_amount) as 'payAmount'
        from kuaishou_supply_chain
        where 1=1
        <if test="yesterdayStartHourTemp != null and yesterdayStartHourTemp != ''">
            and order_create_time &gt;= #{yesterdayStartHourTemp}
        </if>
        <if test="yesterdayEndHourTemp != null and yesterdayEndHourTemp != ''">
            and order_create_time &lt;= #{yesterdayEndHourTemp}
        </if>
        ) t2
    </select>
    <select id="getServiceAmountTotal" resultType="com.alibaba.fastjson.JSONObject">
        select
        count(distinct promoter_id) as 'promoterNum',
        sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when send_status = 0 and order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when send_status = 1 then 1 else 0 end) / sum(case when order_status != 80 then 1 else 0 end),4)
        as 'deliveryRadio'
        from kuaishou_supply_chain
        where 1= 1
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>
    </select>
    <select id="getOrderRankRatioList" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.*,
        round((t1.orderNum - t2.orderNum) / t2.orderNum,4) as 'radio'
        from (select
        item_id as 'itemId',
        item_title as 'itemTitle',
        image_url as 'imageUrl',
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when order_status != 80 then 1 else 0 end) / count(oid),4) as 'validRadio',
        sum(case when send_status = 1 then 1 else 0 end) as 'deliveryNum',
        sum(case when send_status = 0 and order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when send_status = 1 then 1 else 0 end) / sum(case when order_status != 80 then 1 else 0 end),4)
        as 'deliveryRadio'
        from kuaishou_supply_chain
        where 1= 1
        <if test="nowStartHourTemp != null and nowStartHourTemp != ''">
            and order_create_time &gt;= #{nowStartHourTemp}
        </if>
        <if test="nowEndHourTemp != null and nowEndHourTemp != ''">
            and order_create_time &lt;= #{nowEndHourTemp}
        </if>
        group by item_id
        ) t1
        left join (select
        item_id as 'itemId',
        count(oid) as 'orderNum'
        from kuaishou_supply_chain
        where 1=1
        <if test="yesterdayStartHourTemp != null and yesterdayStartHourTemp != ''">
            and order_create_time &gt;= #{yesterdayStartHourTemp}
        </if>
        <if test="yesterdayEndHourTemp != null and yesterdayEndHourTemp != ''">
            and order_create_time &lt;= #{yesterdayEndHourTemp}
        </if>
        group by item_id
        ) t2 on t1.itemId = t2.itemId
        order by t1.orderNum desc
    </select>

    <select id="getOrderRankRatioListBytedance" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.*,
        round((t1.orderNum - t2.orderNum) / t2.orderNum,4) as 'radio'
        from (
        select
        product_id as 'itemId',
        product_name as 'itemTitle',
        product_img as 'imageUrl',
        count(order_id) as 'orderNum',
        sum(case when flow_point != 'REFUND' then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when flow_point != 'REFUND' then 1 else 0 end) / count(order_id),4) as 'validRadio'
        from bytedance_order_list
        where 1= 1
        <if test="nowStartHourTemp != null and nowStartHourTemp != ''">
            and pay_success_time &gt;= #{nowStartHourTemp}
        </if>
        <if test="nowEndHourTemp != null and nowEndHourTemp != ''">
            and pay_success_time &lt;= #{nowEndHourTemp}
        </if>
        group by product_id
        ) t1
        left join (select
        product_id as 'itemId',
        count(order_id) as 'orderNum'
        from bytedance_order_list
        where 1=1
        <if test="yesterdayStartHourTemp != null and yesterdayStartHourTemp != ''">
            and pay_success_time &gt;= #{yesterdayStartHourTemp}
        </if>
        <if test="yesterdayEndHourTemp != null and yesterdayEndHourTemp != ''">
            and pay_success_time &lt;= #{yesterdayEndHourTemp}
        </if>
        group by product_id
        ) t2 on t1.itemId = t2.itemId
        order by t1.orderNum desc
    </select>

    <select id="getOrderRankList" resultType="com.alibaba.fastjson.JSONObject">
        select * from (
        select
        item_id as 'itemId',
        item_title as 'itemTitle',
        image_url as 'imageUrl',
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when order_status != 80 then 1 else 0 end) / count(oid),4) as 'validRadio',
        sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when send_status = 0 and order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) / sum(case when order_status != 80
        then 1 else 0 end),4) as 'deliveryRadio'
        from kuaishou_supply_chain
        where 1= 1
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>
        group by item_id) t
        order by t.orderNum desc
    </select>

    <select id="getOrderRankListBytedance" resultType="com.alibaba.fastjson.JSONObject">
        select * from (
        select
        product_id as 'itemId',
        product_name as 'itemTitle',
        product_img as 'imageUrl',
        count(order_id) as 'orderNum',
        sum(case when flow_point != 'REFUND' then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when flow_point != 'REFUND' then 1 else 0 end) / count(order_id),4) as 'validRadio'
        from bytedance_order_list
        where 1= 1
        <if test="start != null and start != ''">
            and pay_success_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and pay_success_time &lt;= #{end}
        </if>
        group by product_id) t
        order by t.orderNum desc
    </select>

    <select id="getPromoterRankRatioList" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.*,
        round((t1.orderNum - t2.orderNum) / t2.orderNum,4) as 'radio'
        from (
        select
        promoter_id as 'promoterId',
        promoter_nick_name as 'promoterNickName',
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when order_status != 80 then 1 else 0 end) / count(oid),4) as 'validRadio'
        from kuaishou_supply_chain
        where 1= 1
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND promoter_id IN
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="nowStartHourTemp != null and nowStartHourTemp != ''">
            and order_create_time &gt;= #{nowStartHourTemp}
        </if>
        <if test="nowEndHourTemp != null and nowEndHourTemp != ''">
            and order_create_time &lt;= #{nowEndHourTemp}
        </if>
        group by promoter_id
        ) t1
        left join (select
        promoter_id as 'promoterId',
        count(oid) as 'orderNum'
        from kuaishou_supply_chain
        where 1=1
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND promoter_id IN
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="yesterdayStartHourTemp != null and yesterdayStartHourTemp != ''">
            and order_create_time &gt;= #{yesterdayStartHourTemp}
        </if>
        <if test="yesterdayEndHourTemp != null and yesterdayEndHourTemp != ''">
            and order_create_time &lt;= #{yesterdayEndHourTemp}
        </if>
        group by promoter_id
        ) t2 on t1.promoterId = t2.promoterId
        order by t1.orderNum desc
    </select>

    <select id="getPromoterRankRatioListBytedance" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.*,
        round((t1.orderNum - t2.orderNum) / t2.orderNum,4) as 'radio'
        from (
        select
        author_short_id as 'promoterId',
        author_account as 'promoterNickName',
        count(order_id) as 'orderNum',
        sum(case when flow_point != 'REFUND' then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when flow_point != 'REFUND' then 1 else 0 end) / count(order_id),4) as 'validRadio'
        from bytedance_order_list
        where author_short_id is not null
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND author_short_id IN
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="nowStartHourTemp != null and nowStartHourTemp != ''">
            and pay_success_time &gt;= #{nowStartHourTemp}
        </if>
        <if test="nowEndHourTemp != null and nowEndHourTemp != ''">
            and pay_success_time &lt;= #{nowEndHourTemp}
        </if>
        group by author_short_id
        ) t1
        left join (
        select
        author_short_id as 'promoterId',
        count(order_id) as 'orderNum'
        from bytedance_order_list
        where 1=1
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND author_short_id IN
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="yesterdayStartHourTemp != null and yesterdayStartHourTemp != ''">
            and pay_success_time &gt;= #{yesterdayStartHourTemp}
        </if>
        <if test="yesterdayEndHourTemp != null and yesterdayEndHourTemp != ''">
            and pay_success_time &lt;= #{yesterdayEndHourTemp}
        </if>
        group by author_short_id
        ) t2 on t1.promoterId = t2.promoterId
        order by t1.orderNum desc
    </select>

    <select id="getPromoterRatioList" resultType="com.alibaba.fastjson.JSONObject">
        select * from (
        select
        promoter_id as 'promoterId',
        promoter_nick_name as 'promoterNickName',
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when order_status != 80 then 1 else 0 end) / count(oid),4) as 'validRadio'
        from kuaishou_supply_chain
        where 1= 1
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>
        group by promoter_id) t
        order by t.orderNum desc
    </select>

    <select id="getPromoterRatioListBytedance" resultType="com.alibaba.fastjson.JSONObject">
        select * from (
        select
        author_short_id as 'promoterId',
        author_account as 'promoterNickName',
        count(order_id) as 'orderNum',
        sum(case when flow_point != 'REFUND' then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when flow_point != 'REFUND' then 1 else 0 end) / count(order_id),4) as 'validRadio'
        from bytedance_order_list
        where author_short_id is not null
        <if test="start != null and start != ''">
            and pay_success_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and pay_success_time &lt;= #{end}
        </if>
        group by author_short_id) t
        order by t.orderNum desc
    </select>

    <select id="exportPromoterTotal" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from (
        select
        promoter_id as 'promoterId',
        promoter_nick_name as 'promoterNickName',
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when order_status != 80 then 1 else 0 end) / count(oid) * 100,2) as 'validRadio',
        sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when send_status = 0 and order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) / sum(case when order_status != 80
        then 1 else 0 end) * 100,2) as 'deliveryRadio',
        round(sum(pay_amount) / 100,2) as 'payAmount',
        round(sum(case when order_status != 80 then pay_amount else 0 end) / 100 , 2 ) as 'validPayAmount',
        round(sum(case when order_status != 80 then regimental_promotion_amount else 0 end) / 100, 2) as
        'regimentalPromotionAmount'
        from kuaishou_supply_chain
        where 1= 1
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>
        <if test="promoterId != null and promoterId != ''">
            and promoter_id = #{promoterId}
        </if>
        <if test="promoterNickName != null and promoterNickName != ''">
            and promoter_nick_name like concat(concat('%',#{promoterNickName}),'%')
        </if>
        <if test="itemId != null and itemId != ''">
            and item_id = #{itemId}
        </if>
        <if test="itemTitle != null and itemTitle != ''">
            and item_title like concat(concat('%',#{itemTitle}),'%')
        </if>
        group by promoter_id) t
        order by t.orderNum desc

    </select>

    <select id="exportPromoter" 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',
        image_url as 'imageUrl',
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when order_status != 80 then 1 else 0 end) / count(oid) * 100,2) as 'validRadio',
        sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when send_status = 0 and order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) / sum(case when order_status != 80
        then 1 else 0 end) * 100,2) as 'deliveryRadio'
        from kuaishou_supply_chain
        where 1= 1
        <if test="start != null and start != ''">
            and order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and order_create_time &lt;= #{end}
        </if>
        <if test="promoterId != null and promoterId != ''">
            and promoter_id = #{promoterId}
        </if>
        <if test="promoterNickName != null and promoterNickName != ''">
            and promoter_nick_name like concat(concat('%',#{promoterNickName}),'%')
        </if>
        <if test="itemId != null and itemId != ''">
            and item_id = #{itemId}
        </if>
        <if test="itemTitle != null and itemTitle != ''">
            and item_title like concat(concat('%',#{itemTitle}),'%')
        </if>
        group by promoter_id,item_id) t
        order by t.promoterId,t.orderNum desc

    </select>
    <select id="exportOrder" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from (select
        t2.user_id as 'userId',
        t2.user_name as 'userName',
        t1.item_id as 'itemId',
        t1.item_title as 'itemTitle',
        t1.image_url as 'imageUrl',
        count(t1.oid) as 'orderNum',
        sum(case when t1.order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when t1.order_status != 80 then 1 else 0 end) / count(t1.oid) * 100, 2) as 'validRadio',
        sum(case when t1.send_status = 1 and t1.order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when t1.send_status = 0 and t1.order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when t1.send_status = 1 and order_status != 80 then 1 else 0 end) / sum(case when t1.order_status
        != 80 then 1 else 0 end) * 100, 2) as 'deliveryRadio',
        round(t1.item_commission_rate / 10,2) as 'itemCommissionRate'
        from kuaishou_supply_chain t1
        left join kuaishou_item_bind_user t2
        on t1.item_id = t2.item_id
        where 1 = 1
        <if test="start != null and start != ''">
            and t1.order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and t1.order_create_time &lt;= #{end}
        </if>
        <if test="promoterId != null and promoterId != ''">
            and t1.promoter_id = #{promoterId}
        </if>
        <if test="promoterNickName != null and promoterNickName != ''">
            and t1.promoter_nick_name like concat(concat('%',#{promoterNickName}),'%')
        </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(concat('%',#{itemTitle}),'%')
        </if>
        group by t1.item_id) t
        where 1 = 1
        <if test="userId != null and userId != ''">
            and t.userId = #{userId}
        </if>
        order by t.orderNum desc
    </select>
    <select id="bindUserItemList" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from (select t1.item_id as 'itemId',
        t1.item_title as 'itemTitle',
        t1.reserve_price as 'reservePrice',
        t1.image_url as 'imageUrl',
        t1.activity_id as 'activityId',
        t1.regimental_promotion_rate as 'regimentalPromotionRate',
        t2.user_id as 'userId',
        case when t2.user_id is null then 1 else 2 end as 'status',
        t2.user_name as 'userName',
        t2.create_time as 'bindTime',
        t1.item_commission_rate as 'itemCommissionRate'
        from kuaishou_item_info t1
        left join kuaishou_item_bind_user t2 on t1.item_id = t2.item_id
        where 1 = 1
        <if test="userId != null and userId != ''">
            and t2.user_id = #{userId}
        </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(concat('%',#{itemTitle}),'%')
        </if>
        <if test="activityId != null and activityId != ''">
            and t1.activity_id = #{activityId}
        </if>
        order by t1.create_time desc) t
        where 1 = 1
        <if test="status != null and status != ''">
            and t.status = #{status}
        </if>

    </select>
    <select id="getSupplyChainUserList" resultType="com.alibaba.fastjson.JSONObject">
        select t3.user_id as 'userId', t3.nick_name as 'nickMame', t1.role_name as 'roleName', t3.status
        from sys_role t1
                 left join sys_user_role t2 on t1.role_id = t2.role_id
                 left join sys_user t3 on t2.user_id = t3.user_id
        where t1.role_key in (
                              'association',
                              'associationManager',
                              'bd',
                              'bdManager'
            )
        group by t3.user_id
        order by t3.create_time asc
    </select>
    <select id="adminReportList" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from (select t1.user_id as 'userId',
        t1.user_name as 'userName',
        count(distinct t2.item_id) as 'itemCount',
        count(t2.oid) as 'orderNum',
        sum(case when t2.order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when t2.order_status != 80 then 1 else 0 end) / count(t2.oid),4) as 'validRadio',
        sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when t2.send_status = 0 and t2.order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end) / sum(case
        when t2.order_status != 80
        then 1
        else 0 end),
        4) as 'deliveryRadio',
        sum(case when t2.order_status = 80 then 1 else 0 end) as 'invalidOrderNUm',
        sum(case when t2.order_status != 80 then t2.pay_amount else 0 end) as 'payAmount',
        sum(case
        when t2.order_status != 80 then t2.regimental_promotion_amount
        else 0 end) as 'regimentalPromotionAmount',
        sum(case when t2.order_status != 80 then t2.total_regimental_settle_amount else 0 end) as
        'totalRegimentalSettleAmount'
        from kuaishou_item_bind_user t1
        left join kuaishou_supply_chain t2
        on t1.item_id = t2.item_id
        where 1= 1
        <if test="start != null and start != ''">
            and t2.order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and t2.order_create_time &lt;= #{end}
        </if>
        group by t1.user_id) t left join (
        select item_create_id,count(1) as 'sendSampleCount' from kuaishou_item_collect_samples
        where courier_number_time >= from_unixtime(#{start}/1000,'%Y-%m-%d %H:%i:%s')
        and courier_number_time &lt;= from_unixtime(#{end}/1000,'%Y-%m-%d %H:%i:%s')
        group by item_create_id

        ) t1 on t.userId = t1.item_create_id
        where 1 =1
        <if test="userId != null and userId != ''">
            and t.userId = #{userId}
        </if>
        order by t.orderNum desc
    </select>
    <select id="userItemDetail" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        *
        FROM
        (
        SELECT
        t1.user_id,
        t1.user_name AS 'userName',
        t2.item_id AS 'itemId',
        t2.item_title AS 'itemTitle',
        t2.image_url AS 'imageUrl',
        t2.reserve_price AS 'reservePrice',
        count(t2.oid) AS 'orderNum',
        sum(
        CASE
        WHEN t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) AS 'validOrderNUm',
        round(
        sum(
        CASE
        WHEN t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) / count(t2.oid),
        4
        ) AS 'validRadio',
        sum(
        CASE
        WHEN t2.send_status = 1
        AND t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) AS 'deliveryNum',
        sum(case when t2.send_status = 0 and t2.order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(
        sum(
        CASE
        WHEN t2.send_status = 1
        AND t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) / sum(
        CASE
        WHEN t2.order_status != 80 THEN 1
        ELSE 0
        END
        ),
        4
        ) AS 'deliveryRadio',
        sum(
        CASE
        WHEN t2.order_status = 80 THEN 1
        ELSE 0
        END
        ) AS 'invalidOrderNUm',
        sum(
        CASE
        WHEN t2.order_status != 80 THEN t2.pay_amount
        ELSE 0
        END
        ) AS 'payAmount',
        t2.regimental_promotion_rate AS 'regimentalPromotionRate',
        sum(
        CASE
        WHEN t2.order_status != 80 THEN t2.regimental_promotion_amount
        ELSE 0
        END
        ) AS 'regimentalPromotionAmount',
        sum(case when t2.order_status != 80 then t2.total_regimental_settle_amount else 0 end) as
        'totalRegimentalSettleAmount',
        t2.item_commission_rate as 'itemCommissionRate'
        FROM
        kuaishou_item_bind_user t1
        LEFT JOIN
        kuaishou_supply_chain t2 ON t1.item_id = t2.item_id
        WHERE
        1 = 1
        <if test="start != null and start != ''">
            and t2.order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and t2.order_create_time &lt;= #{end}
        </if>
        <if test="itemId != null and itemId != ''">
            and t1.item_id = #{itemId}
        </if>
        <if test="itemTitle != null and itemTitle != ''">
            and t2.item_title like concat(concat('%',#{itemTitle}),'%')
        </if>
        GROUP BY
        t1.item_id
        ) t
        where 1 = 1
        <if test="userId != null and userId != ''">
            and t.user_id = #{userId}
        </if>
        order by ${fieId} ${sort}
    </select>
    <select id="getTimeIntervalRatio" resultType="com.alibaba.fastjson.JSONObject">
        select
        round((t1.oid -t2.oid) / t2.oid , 4) as 'orderTotalRatio',
        round((t1.orderAmount -t2.orderAmount) / t2.orderAmount , 4) as 'orderAmountRatio'
        from (
        select count(oid) as 'oid',
        sum(order_amount) as 'orderAmount'
        from kuaishou_supply_chain
        where 1= 1
        <if test="thisCycleStartTemp != null and thisCycleStartTemp != ''">
            and order_create_time &gt;= #{thisCycleStartTemp}
        </if>
        <if test="thisCycleEndTemp != null and thisCycleEndTemp != ''">
            and order_create_time &lt;= #{thisCycleEndTemp}
        </if>
        ) t1
        join (
        select count(oid) as 'oid',
        sum(order_amount) as 'orderAmount'
        from kuaishou_supply_chain
        where 1=1
        <if test="lastCycleStartTemp != null and lastCycleStartTemp != ''">
            and order_create_time &gt;= #{lastCycleStartTemp}
        </if>
        <if test="lastCycleStartTemp != null and lastCycleStartTemp != ''">
            and order_create_time &lt;= #{lastCycleEndTemp}
        </if>
        ) t2

    </select>
    <select id="indexStatistics" resultType="com.alibaba.fastjson.JSONObject">
        select FROM_UNIXTIME(t.order_create_time / 1000, '%y-%m-%d') as 'date',
        <if test="type == 'orderNum' ">
            count(t.oid) as 'orderNum'
        </if>
        <if test="type == 'validOrderNum' ">
            sum(case when t.order_status = 80 then 1 else 0 end) as 'validOrderNum'
        </if>
        <if test="type == 'orderAmount' ">
            sum(t.order_amount) as 'orderAmount'
        </if>
        <if test="type == 'baseAmount' ">
            sum(t.base_amount) as 'baseAmount'
        </if>
        <if test="type == 'deliveryNum' ">
            sum(case when t.send_status = 1 and t.order_status != 80 then 1 else 0 end) as 'deliveryNum'
        </if>
        from (select
        oid,
        order_status,
        order_amount,
        base_amount,
        send_status,
        order_create_time
        from kuaishou_supply_chain
        where 1 = 1
        <if test="startTemp != null and startTemp != ''">
            and order_create_time &gt;= #{startTemp}
        </if>
        <if test="endTemp != null and endTemp != ''">
            and order_create_time &lt;= #{endTemp}
        </if>
        ) t
        group by FROM_UNIXTIME(t.order_create_time / 1000, '%y-%m-%d')
        order by FROM_UNIXTIME(t.order_create_time / 1000, '%y-%m-%d') asc
    </select>
    <select id="itemBindSummary" resultType="com.alibaba.fastjson.JSONObject">
        select count(t1.item_id) as 'itemTotal', sum(case when t2.item_id is not null then 1 else 0 end) as 'claimCount', sum(case when t2.item_id is null then 1 else 0 end) as 'unClaimCount'
        from kuaishou_item_info t1
                 left join kuaishou_item_bind_user t2
                           on t1.item_id = t2.item_id
        where 1 = 1
    </select>
    <select id="userItemTotal" resultType="com.alibaba.fastjson.JSONObject">
        SELECT count(t2.oid) AS 'orderNum',
        sum(
        CASE
        WHEN t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) AS 'validOrderNUm',
        round(
        sum(
        CASE
        WHEN t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) / count(t2.oid),
        4
        ) AS 'validRadio',
        sum(
        CASE
        WHEN t2.send_status = 1
        AND t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) AS 'deliveryNum',
        sum(case when t2.send_status = 0 and t2.order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(
        sum(
        CASE
        WHEN t2.send_status = 1
        AND t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) / sum(
        CASE
        WHEN t2.order_status != 80 THEN 1
        ELSE 0
        END
        ),
        4
        ) AS 'deliveryRadio',
        sum(
        CASE
        WHEN t2.order_status = 80 THEN 1
        ELSE 0
        END
        ) AS 'invalidOrderNUm',
        sum(
        CASE
        WHEN t2.order_status != 80 THEN t2.pay_amount
        ELSE 0
        END
        ) AS 'payAmount',
        sum(
        CASE
        WHEN t2.order_status != 80 THEN t2.regimental_promotion_amount
        ELSE 0
        END
        ) AS 'regimentalPromotionAmount',
        sum(case when t2.order_status != 80 then t2.total_regimental_settle_amount else 0 end) as
        'totalRegimentalSettleAmount'
        FROM kuaishou_item_bind_user t1
        LEFT JOIN
        kuaishou_supply_chain t2 ON t1.item_id = t2.item_id
        WHERE 1 = 1
        <if test="start != null and start != ''">
            and t2.order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and t2.order_create_time &lt;= #{end}
        </if>
        <if test="userId != null and userId != ''">
            and t1.user_id = #{userId}
        </if>
    </select>
    <select id="exportUserItemDetail" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        *
        FROM
        (
        SELECT
        t1.user_id as 'user_id',
        t1.user_name AS 'userName',
        t2.item_id AS 'itemId',
        t2.item_title AS 'itemTitle',
        t2.image_url AS 'imageUrl',
        round(t2.reserve_price / 100,2) AS 'reservePrice',
        count(t2.oid) AS 'orderNum',
        sum(
        CASE
        WHEN t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) AS 'validOrderNUm',
        round(
        sum(
        CASE
        WHEN t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) / count(t2.oid) * 100,
        2
        ) AS 'validRadio',
        sum(
        CASE
        WHEN t2.send_status = 1
        AND t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) AS 'deliveryNum',
        sum(case when t2.send_status = 0 and t2.order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(
        sum(
        CASE
        WHEN t2.send_status = 1
        AND t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) / sum(
        CASE
        WHEN t2.order_status != 80 THEN 1
        ELSE 0
        END
        ) * 100,
        2
        ) AS 'deliveryRadio',
        sum(
        CASE
        WHEN t2.order_status = 80 THEN 1
        ELSE 0
        END
        ) AS 'invalidOrderNUm',

        round( sum(
        CASE
        WHEN t2.order_status != 80 THEN t2.pay_amount
        ELSE 0
        END
        )/ 100,2 )AS 'payAmount',
        concat(round(t2.regimental_promotion_rate / 10 ,2),'%') AS 'regimentalPromotionRate',
        round(
        sum(
        CASE
        WHEN t2.order_status != 80 THEN t2.regimental_promotion_amount
        ELSE 0
        END
        ) / 100 , 2)AS 'regimentalPromotionAmount',
        round(
        sum(case when t2.order_status != 80 then t2.total_regimental_settle_amount else 0 end) / 100,2) as
        'totalRegimentalSettleAmount',
        concat(round(t2.item_commission_rate / 10),'%') as 'itemCommissionRate'
        FROM
        kuaishou_item_bind_user t1
        LEFT JOIN
        kuaishou_supply_chain t2 ON t1.item_id = t2.item_id
        WHERE
        1 = 1
        <if test="start != null and start != ''">
            and t2.order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and t2.order_create_time &lt;= #{end}
        </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(concat('%',#{itemTitle}),'%')
        </if>
        GROUP BY
        t1.item_id
        ) t
        where 1 = 1
        <if test="userId != null and userId != ''">
            and t.user_id = #{userId}
        </if>
        order by t.orderNum desc
    </select>
    <select id="getCookie" resultType="java.lang.String">
        select cookie
        from ruixuan.kuaishou_supply_chain_cookie
        where id = #{regId} limit 1
    </select>

    <select id="selectOrderInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        COUNT(DISTINCT promoter_id) as 'promoterCount',
        COUNT(oid) as 'allNumber',
        IFNULL(SUM(CASE WHEN order_status != 80 THEN 1 ELSE 0 END), 0) AS 'validOrderNUm',
        IFNULL(SUM(CASE WHEN order_status != 80 THEN order_amount ELSE 0 END), 0) AS 'orderAmount',
        IFNULL(SUM(CASE WHEN order_status != 80 THEN regimental_promotion_amount ELSE 0 END),0) AS
        'regimentalPromotionAmount',
        IFNULL(sum(base_amount), 0) as 'baseAmount'
        FROM kuaishou_supply_chain
        WHERE stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND promoter_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="selectOrderInfoBytedance" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        COUNT(DISTINCT author_short_id) as 'promoterCount',
        COUNT(order_id) as 'allNumber',
        IFNULL(SUM(CASE WHEN flow_point != 'REFUND' THEN 1 ELSE 0 END), 0) AS 'validOrderNUm',
        IFNULL(SUM(CASE WHEN flow_point != 'REFUND' THEN total_pay_amount ELSE 0 END), 0) AS 'orderAmount',
        IFNULL(SUM(CASE WHEN flow_point != 'REFUND' THEN colonel_estimated_commission ELSE 0 END),0) AS
        'regimentalPromotionAmount'
        FROM bytedance_order_list
        WHERE pay_success_time >= #{startDate}
        AND pay_success_time &lt;= #{endDate}
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND author_short_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="selectOrderInfoByItemIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t.*,
        CONCAT(IFNULL(ROUND(sendCount / allNumber * 100, 2),0), '%') as 'sendRate'
        FROM (
        SELECT
        COUNT(DISTINCT promoter_id) as 'promoterCount',
        COUNT(oid) as 'allNumber',
        IFNULL(SUM(CASE WHEN order_status != 80 THEN 1 ELSE 0 END), 0) AS 'validOrderNUm',
        IFNULL(SUM(CASE WHEN order_status != 80 THEN order_amount ELSE 0 END), 0) AS 'orderAmount',
        IFNULL(SUM(CASE WHEN order_status != 80 THEN regimental_promotion_amount ELSE 0 END),0) AS
        'regimentalPromotionAmount',
        IFNULL(sum(base_amount), 0) as 'baseAmount',
        IFNULL(COUNT(CASE WHEN send_status = 1 THEN oid ELSE null END), 0) AS 'sendCount'
        FROM kuaishou_supply_chain
        WHERE stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test='itemIds != null and itemIds.size() > 0'>
            AND item_id in
            <foreach collection="itemIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t
    </select>

    <select id="selectOrderInfoByItemIdsBytedance" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        COUNT(DISTINCT author_short_id) as 'promoterCount',
        COUNT(order_id) as 'allNumber',
        IFNULL(SUM(CASE WHEN flow_point != 'REFUND' THEN 1 ELSE 0 END), 0) AS 'validOrderNUm',
        IFNULL(SUM(CASE WHEN flow_point != 'REFUND' THEN total_pay_amount ELSE 0 END)/100, 0) AS 'orderAmount',
        IFNULL(SUM(CASE WHEN flow_point != 'REFUND' THEN colonel_estimated_commission ELSE 0 END)/100,0) AS
        'regimentalPromotionAmount'
        FROM bytedance_order_list
        WHERE pay_success_time >= #{startDate}
        AND pay_success_time &lt;= #{endDate}
        <if test='itemIds != null and itemIds.size() > 0'>
            AND product_id in
            <foreach collection="itemIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="selectOrderRate" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        count(oid) as 'allNumber',
        IFNULL(sum(
        CASE
        WHEN order_status != 80 THEN order_amount
        ELSE 0
        END
        ),0) AS 'orderAmount'
        FROM kuaishou_supply_chain
        WHERE order_create_time >= #{statDate}
        AND order_create_time &lt;= #{endDate}
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND promoter_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='itemIds != null and itemIds.size() > 0'>
            AND item_id in
            <foreach collection="itemIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="selectOrderRateBytedance" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        count(order_id) as 'allNumber',
        IFNULL(sum(
        CASE
        WHEN flow_point != 'REFUND' THEN total_pay_amount
        ELSE 0
        END
        )/100,0) AS 'orderAmount'
        FROM bytedance_order_list
        WHERE pay_success_time >= #{statDate}
        AND pay_success_time &lt;= #{endDate}
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND author_short_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='itemIds != null and itemIds.size() > 0'>
            AND product_id in
            <foreach collection="itemIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>


    <select id="getMonthOrderAmount" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        FROM_UNIXTIME(order_create_time / 1000, '%Y-%m-%d') as 'date',
        count(1) AS 'orderCount',
        IFNULL(SUM(CASE WHEN order_status != 80 THEN 1 ELSE 0 END), 0) AS 'validOrderCount',
        IFNULL(SUM(CASE WHEN order_status != 80 THEN order_amount ELSE 0 END),0) AS 'orderAmount',
        IFNULL(SUM(CASE WHEN order_status != 80 THEN regimental_promotion_amount ELSE 0 END),0) AS
        'regimentalPromotionAmount',
        COUNT(DISTINCT promoter_id) as 'promoterCount'
        FROM kuaishou_supply_chain t1
        WHERE order_create_time >= #{startLong}
        AND order_create_time &lt;= #{endLong}
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND promoter_id in
            <foreach collection="promoterIds" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY FROM_UNIXTIME(order_create_time / 1000, '%Y-%m-%d')
    </select>

    <select id="getMonthOrderAmountBytedance" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        DATE_FORMAT(pay_success_time, '%Y-%m-%d') as 'date',
        count(1) AS 'orderCount',
        IFNULL(SUM(CASE WHEN flow_point != 'REFUND' THEN 1 ELSE 0 END), 0) AS 'validOrderCount',
        IFNULL(SUM(CASE WHEN flow_point != 'REFUND' THEN total_pay_amount ELSE 0 END),0) AS 'orderAmount',
        IFNULL(SUM(CASE WHEN flow_point != 'REFUND' THEN colonel_estimated_commission ELSE 0 END),0) AS
        'regimentalPromotionAmount',
        COUNT(DISTINCT author_short_id) as 'promoterCount'
        FROM bytedance_order_list
        WHERE pay_success_time >= #{startLong}
        AND pay_success_time &lt;= #{endLong}
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND author_short_id in
            <foreach collection="promoterIds" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY pay_success_time
    </select>

    <select id="getMonthOrderAmountByItems" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        FROM_UNIXTIME(order_create_time / 1000, '%Y-%m-%d') as 'date',
        count(1) AS 'orderCount',
        IFNULL(SUM(CASE WHEN order_status != 80 THEN 1 ELSE 0 END), 0) AS 'validOrderCount',
        IFNULL(SUM(CASE WHEN order_status != 80 THEN order_amount ELSE 0 END),0) AS 'orderAmount',
        IFNULL(SUM(CASE WHEN order_status != 80 THEN regimental_promotion_amount ELSE 0 END),0) AS
        'regimentalPromotionAmount'
        FROM kuaishou_supply_chain
        WHERE order_create_time >= #{startLong}
        AND order_create_time &lt;= #{endLong}
        <if test='itemIds != null and itemIds.size() > 0'>
            AND item_id in
            <foreach collection="itemIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY FROM_UNIXTIME(order_create_time / 1000, '%Y-%m-%d')
    </select>

    <select id="getMonthOrderAmountByItemsBytedance" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        DATE_FORMAT(pay_success_time, '%Y-%m-%d') as 'date',
        count(1) AS 'orderCount',
        IFNULL(SUM(CASE WHEN flow_point != 'REFUND' THEN 1 ELSE 0 END), 0) AS 'validOrderCount',
        IFNULL(SUM(CASE WHEN flow_point != 'REFUND' THEN total_pay_amount ELSE 0 END),0) AS 'orderAmount',
        IFNULL(SUM(CASE WHEN flow_point != 'REFUND' THEN colonel_estimated_commission ELSE 0 END),0) AS
        'regimentalPromotionAmount'
        FROM bytedance_order_list
        WHERE pay_success_time >= #{startLong}
        AND pay_success_time &lt;= #{endLong}
        <if test='itemIds != null and itemIds.size() > 0'>
            AND product_id in
            <foreach collection="itemIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY pay_success_time
    </select>

    <select id="getMonthOrderAmountTotal" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.orderAmount,
        t1.regimentalPromotionAmount,
        CONCAT(IFNULL(ROUND(t2.orderAmount/t1.orderAmount*100,2),0),'%') as 'orderAmountRate',
        CONCAT(IFNULL(ROUND(t2.regimentalPromotionAmount/t1.regimentalPromotionAmount*100,2),0),'%') as
        'regimentalPromotionAmountRate'
        FROM
        (
        SELECT
        IFNULL(SUM(order_amount),0) AS 'orderAmount',
        IFNULL(SUM(regimental_promotion_amount),0) AS 'regimentalPromotionAmount'
        FROM kuaishou_supply_chain t1
        WHERE order_status != 80
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND promoter_id in
            <foreach collection="promoterIds" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND order_create_time >= #{startLong}
        AND order_create_time &lt;= #{endLong}) t1
        LEFT JOIN
        (
        SELECT
        IFNULL(SUM(order_amount),0) AS 'orderAmount',
        IFNULL(SUM(regimental_promotion_amount),0) AS 'regimentalPromotionAmount'
        FROM kuaishou_supply_chain t1
        WHERE order_status != 80
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND promoter_id in
            <foreach collection="promoterIds" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND order_create_time >= #{toStartLong}
        AND order_create_time &lt;= #{endLong})t2 ON 1=1
    </select>

    <select id="getMonthOrderAmountTotalBytedance" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.orderAmount,
        t1.regimentalPromotionAmount,
        CONCAT(IFNULL(ROUND(t2.orderAmount/t1.orderAmount*100,2),0),'%') as 'orderAmountRate',
        CONCAT(IFNULL(ROUND(t2.regimentalPromotionAmount/t1.regimentalPromotionAmount*100,2),0),'%') as
        'regimentalPromotionAmountRate'
        FROM
        (
        SELECT
        IFNULL(SUM(total_pay_amount),0) AS 'orderAmount',
        IFNULL(SUM(colonel_estimated_commission),0) AS 'regimentalPromotionAmount'
        FROM bytedance_order_list t1
        WHERE flow_point != 'REFUND'
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND author_short_id in
            <foreach collection="promoterIds" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND pay_success_time >= #{startLong}
        AND pay_success_time &lt;= #{endLong}) t1
        LEFT JOIN
        (
        SELECT
        IFNULL(SUM(total_pay_amount),0) AS 'orderAmount',
        IFNULL(SUM(colonel_estimated_commission),0) AS 'regimentalPromotionAmount'
        FROM bytedance_order_list t1
        WHERE flow_point != 'REFUND'
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND author_short_id in
            <foreach collection="promoterIds" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND pay_success_time >= #{toStartLong}
        AND pay_success_time &lt;= #{endLong})t2 ON 1=1
    </select>

    <select id="getMonthOrderAmountTotalByItems" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.orderAmount,
        t1.regimentalPromotionAmount,
        t1.orderCount,
        CONCAT(IFNULL(ROUND(t2.orderAmount/t1.orderAmount*100,2),0),'%') as 'orderAmountRate',
        CONCAT(IFNULL(ROUND(t2.orderCount/t1.orderCount*100,2),0),'%') as 'orderCountRate',
        CONCAT(IFNULL(ROUND(t2.regimentalPromotionAmount/t1.regimentalPromotionAmount*100,2),0),'%') as
        'regimentalPromotionAmountRate'
        FROM
        (
        SELECT
        IFNULL(SUM(order_amount),0) AS 'orderAmount',
        IFNULL(SUM(regimental_promotion_amount),0) AS 'regimentalPromotionAmount',
        COUNT(oid) as 'orderCount'
        FROM kuaishou_supply_chain t1
        WHERE order_status != 80
        <if test='itemIds != null and itemIds.size() > 0'>
            AND item_id in
            <foreach collection="itemIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND order_create_time >= #{startLong}
        AND order_create_time &lt;= #{endLong}
        )t1
        LEFT JOIN
        (
        SELECT
        IFNULL(SUM(order_amount),0) AS 'orderAmount',
        IFNULL(SUM(regimental_promotion_amount),0) AS 'regimentalPromotionAmount',
        COUNT(oid) as 'orderCount'
        FROM kuaishou_supply_chain t1
        WHERE order_status != 80
        <if test='itemIds != null and itemIds.size() > 0'>
            AND item_id in
            <foreach collection="itemIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND order_create_time >= #{toStartLong}
        AND order_create_time &lt;= #{endLong}
        )t2 ON 1=1
    </select>

    <select id="getMonthOrderAmountTotalByItemsBytedance" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.orderAmount,
        t1.regimentalPromotionAmount,
        t1.orderCount,
        CONCAT(IFNULL(ROUND(t2.orderAmount/t1.orderAmount*100,2),0),'%') as 'orderAmountRate',
        CONCAT(IFNULL(ROUND(t2.orderCount/t1.orderCount*100,2),0),'%') as 'orderCountRate',
        CONCAT(IFNULL(ROUND(t2.regimentalPromotionAmount/t1.regimentalPromotionAmount*100,2),0),'%') as
        'regimentalPromotionAmountRate'
        FROM
        (
        SELECT
        IFNULL(SUM(total_pay_amount),0) AS 'orderAmount',
        IFNULL(SUM(colonel_estimated_commission),0) AS 'regimentalPromotionAmount',
        COUNT(order_id) as 'orderCount'
        FROM bytedance_order_list t1
        WHERE flow_point != 'REFUND'
        <if test='itemIds != null and itemIds.size() > 0'>
            AND product_id in
            <foreach collection="itemIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND pay_success_time >= #{IntStart}
        AND pay_success_time &lt;= #{IntEnd}
        )t1
        LEFT JOIN
        (
        SELECT
        IFNULL(SUM(total_pay_amount),0) AS 'orderAmount',
        IFNULL(SUM(colonel_estimated_commission),0) AS 'regimentalPromotionAmount',
        COUNT(order_id) as 'orderCount'
        FROM bytedance_order_list t1
        WHERE flow_point != 'REFUND'
        <if test='itemIds != null and itemIds.size() > 0'>
            AND product_id in
            <foreach collection="itemIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND pay_success_time >= #{IntStart}
        AND pay_success_time &lt;= #{IntEnd}
        )t2 ON 1=1
    </select>


    <select id="bdReportList" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.collectSampleId,
        t1.collectSampleName,
        t2.promoterCount,
        t3.sampleCount,
        round(t3.sampleCount / t2.promoterCount * 100, 2) as 'sampleRate',
        t2.promoterCount - t3.sampleCount as 'notSampleCount',
        t1.orderNum,
        t1.validOrderNum,
        t1.validOrderRate,
        t1.orderAmount,
        t1.regimentalPromotionAmount,
        t1.totalRegimentalSettleAmount,
        t4.sendTotalCount,
        t4.sendPromoterCount,
        t4.sendItemCount,
        t1.voucherOrderNum,
        t1.voucherValidOrderNum,
        t1.voucherOrderAmount,
        t1.voucherRegimentalPromotionAmount,
        t1.voucherTotalRegimentalSettleAmount,
        IFNULL(t5.promoters_count,'-') as 'promotersCount'
        from (
        select collect_sample_id as 'collectSampleId',
        collect_sample_name as 'collectSampleName',
        sum(order_num) as 'orderNum',
        IFNULL(SUM(CASE WHEN sample_voucher_status = 2 THEN order_num ELSE 0 END), 0) AS 'voucherOrderNum',
        sum(valid_order_num) as 'validOrderNum',
        IFNULL(SUM(CASE WHEN sample_voucher_status = 2 THEN valid_order_num ELSE 0 END), 0) AS 'voucherValidOrderNum',
        round(sum(valid_order_num) / sum(order_num) * 100, 2) as 'validOrderRate',
        sum(order_amount) as 'orderAmount',
        IFNULL(SUM(CASE WHEN sample_voucher_status = 2 THEN order_amount ELSE 0 END), 0) AS 'voucherOrderAmount',
        sum(regimental_promotion_amount) as 'regimentalPromotionAmount',
        IFNULL(SUM(CASE WHEN sample_voucher_status = 2 THEN regimental_promotion_amount ELSE 0 END), 0)
        AS'voucherRegimentalPromotionAmount',
        sum(total_regimental_settle_amount) as 'totalRegimentalSettleAmount',
        IFNULL(SUM(CASE WHEN sample_voucher_status = 2 THEN total_regimental_settle_amount ELSE 0 END), 0) AS
        'voucherTotalRegimentalSettleAmount'
        from ruixuan.promoter_item_order_count
        where 1 = 1
        <if test="start != null and start != ''">
            and stat_date &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and stat_date &lt;= #{end}
        </if>
        <if test="collectSampleId != null and collectSampleId != ''">
            and collect_sample_id = #{collectSampleId}
        </if>
        group by collect_sample_id) t1
        left join (
        select user_id as 'userId',
        count(distinct promoter_id) as 'promoterCount'
        from kuaishou_promoter
        group by user_id) t2
        on t1.collectSampleId = t2.userId
        left join (
        select collect_sample_id as 'collectSampleId',
        count(distinct promoter_id) as 'sampleCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        group by collect_sample_id) t3
        on t2.userId = t3.collectSampleId
        left join (
        select collect_sample_id as 'collectSampleId',
        count(1) as 'sendTotalCount',
        count(distinct promoter_id) as 'sendPromoterCount',
        count(distinct item_id) as 'sendItemCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        <if test="collectSampleId != null and collectSampleId != ''">
            and collect_sample_id = #{collectSampleId}
        </if>
        <if test="start != null and start != ''">
            and create_time &gt;= str_to_date(concat(date_format(#{start}, '%Y-%m-%d'), '00:00:01'), '%Y-%m-%d
            %H:%i:%s')
        </if>
        <if test="end != null and end != ''">
            and create_time &lt;= str_to_date(concat(date_format(#{end}, '%Y-%m-%d'), '23:59:59'), '%Y-%m-%d %H:%i:%s')
        </if>
        group by collect_sample_id
        ) t4
        on t1.collectSampleId = t4.collectSampleId
        left join (
        select user_id,promoters_count
        from user_promoters_count
        where media_id = 2
        ) t5
        on t1.collectSampleId = t5.user_id
        order by t1.orderNum desc
    </select>
    <select id="bdDetailList" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.promoterId,
        t1.promoterName,
        t1.promoterUrl,
        t2.sampleCount,
        t1.orderNum,
        t1.validOrderNum,
        t1.validOrderRate,
        t1.orderAmount,
        t1.regimentalPromotionAmount,
        t1.totalRegimentalSettleAmount
        from (
        select promoter_id as 'promoterId',
        promoter_name as 'promoterName',
        promoter_url as 'promoterUrl',
        sum(order_num) as 'orderNum',
        sum(valid_order_num) as 'validOrderNum',
        round(sum(valid_order_num) / sum(order_num) * 100, 2) as 'validOrderRate',
        sum(order_amount) as 'orderAmount',
        sum(regimental_promotion_amount) as 'regimentalPromotionAmount',
        sum(total_regimental_settle_amount) as 'totalRegimentalSettleAmount'
        from ruixuan.promoter_item_order_count
        where 1 = 1
        <if test="start != null and start != ''">
            and stat_date &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and stat_date &lt;= #{end}
        </if>
        <if test="collectSampleId != null and collectSampleId != ''">
            and collect_sample_id = #{collectSampleId}
        </if>
        group by promoter_id) t1
        left join (
        select promoter_id as 'promoterId',
        count(distinct item_id) as 'sampleCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        <if test="collectSampleId != null and collectSampleId != ''">
            and collect_sample_id = #{collectSampleId}
        </if>
        group by promoter_id) t2
        on t1.promoterId = t2.promoterId
        order by ${fieId} ${sort}
    </select>
    <select id="bdTotal" resultType="com.alibaba.fastjson.JSONObject">
        select
        t2.sampleCount,
        t1.orderNum,
        t1.validOrderNum,
        t1.validOrderRate,
        t1.orderAmount,
        t1.regimentalPromotionAmount,
        t1.totalRegimentalSettleAmount
        from (
        select
        sum(order_num) as 'orderNum',
        sum(valid_order_num) as 'validOrderNum',
        round(sum(valid_order_num) / sum(order_num) * 100, 2) as 'validOrderRate',
        sum(order_amount) as 'orderAmount',
        sum(regimental_promotion_amount) as 'regimentalPromotionAmount',
        sum(total_regimental_settle_amount) as 'totalRegimentalSettleAmount'
        from ruixuan.promoter_item_order_count
        where 1 = 1
        <if test="start != null and start != ''">
            and stat_date &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and stat_date &lt;= #{end}
        </if>
        <if test="collectSampleId != null and collectSampleId != ''">
            and collect_sample_id = #{collectSampleId}
        </if>
        ) t1
        join (
        select
        count(distinct item_id) as 'sampleCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        <if test="collectSampleId != null and collectSampleId != ''">
            and collect_sample_id = #{collectSampleId}
        </if>
        ) t2

    </select>
    <select id="exportBdDetail" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.promoterId,
        t1.promoterName,
        t1.promoterUrl,
        t2.sampleCount,
        t1.orderNum,
        t1.validOrderNum,
        t1.validOrderRate,
        t1.orderAmount,
        t1.regimentalPromotionAmount,
        t1.totalRegimentalSettleAmount
        from (
        select promoter_id as 'promoterId',
        promoter_name as 'promoterName',
        promoter_url as 'promoterUrl',
        sum(order_num) as 'orderNum',
        sum(valid_order_num) as 'validOrderNum',
        round(sum(valid_order_num) / sum(order_num) * 100, 2) as 'validOrderRate',
        sum(order_amount) as 'orderAmount',
        sum(regimental_promotion_amount) as 'regimentalPromotionAmount',
        sum(total_regimental_settle_amount) as 'totalRegimentalSettleAmount'
        from ruixuan.promoter_item_order_count
        where 1 = 1
        <if test="start != null and start != ''">
            and stat_date &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and stat_date &lt;= #{end}
        </if>
        <if test="collectSampleId != null and collectSampleId != ''">
            and collect_sample_id = #{collectSampleId}
        </if>
        group by promoter_id) t1
        left join (
        select promoter_id as 'promoterId',
        count(distinct item_id) as 'sampleCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        <if test="collectSampleId != null and collectSampleId != ''">
            and collect_sample_id = #{collectSampleId}
        </if>
        group by promoter_id) t2
        on t1.promoterId = t2.promoterId
        order by t1.orderNum desc
    </select>
    <select id="getUserList" resultType="com.alibaba.fastjson.JSONObject">
        select promoter_id as 'promoterId', group_concat(user_name) as 'userName'
        from kuaishou_promoter where promoter_id in
        <foreach collection="map.values" item="v" separator="," open="(" close=")">
            ${v}
        </foreach>
        GROUP BY promoter_id
    </select>
    <select id="getUserName" resultType="java.lang.String" parameterType="java.lang.Long">
        select group_concat(user_name) as 'userName'
        from kuaishou_promoter
        where promoter_id = #{promoterId}

    </select>
    <select id="exportBdReportList" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.collectSampleId,
        t1.collectSampleName,
        t2.promoterCount,
        t3.sampleCount,
        round(t3.sampleCount / t2.promoterCount * 100, 2) as 'sampleRate',
        t2.promoterCount - t3.sampleCount as 'notSampleCount',
        t1.orderNum,
        t1.validOrderNum,
        t1.validOrderRate,
        round(t1.orderAmount / 100,2) as 'orderAmount',
        round(t1.regimentalPromotionAmount / 100 ,2 ) as 'regimentalPromotionAmount',
        round(t1.totalRegimentalSettleAmount / 100 , 2) as 'totalRegimentalSettleAmount',
        t4.sendTotalCount,
        t4.sendPromoterCount,
        t4.sendItemCount,
        t1.voucherOrderNum,
        t1.voucherValidOrderNum,
        round(t1.voucherOrderAmount / 100 ,2) as 'voucherOrderAmount',
        round(t1.voucherRegimentalPromotionAmount / 100 ,2) as 'voucherRegimentalPromotionAmount',
        round( t1.voucherTotalRegimentalSettleAmount / 100 , 2) as 'voucherTotalRegimentalSettleAmount'
        from (
        select collect_sample_id as 'collectSampleId',
        collect_sample_name as 'collectSampleName',
        sum(order_num) as 'orderNum',
        IFNULL(SUM(CASE WHEN sample_voucher_status = 2 THEN order_num ELSE 0 END), 0) AS 'voucherOrderNum',
        sum(valid_order_num) as 'validOrderNum',
        IFNULL(SUM(CASE WHEN sample_voucher_status = 2 THEN valid_order_num ELSE 0 END), 0) AS 'voucherValidOrderNum',
        round(sum(valid_order_num) / sum(order_num) * 100, 2) as 'validOrderRate',
        sum(order_amount) as 'orderAmount',
        IFNULL(SUM(CASE WHEN sample_voucher_status = 2 THEN order_amount ELSE 0 END), 0) AS 'voucherOrderAmount',
        sum(regimental_promotion_amount) as 'regimentalPromotionAmount',
        IFNULL(SUM(CASE WHEN sample_voucher_status = 2 THEN regimental_promotion_amount ELSE 0 END), 0)
        AS'voucherRegimentalPromotionAmount',
        sum(total_regimental_settle_amount) as 'totalRegimentalSettleAmount',
        IFNULL(SUM(CASE WHEN sample_voucher_status = 2 THEN total_regimental_settle_amount ELSE 0 END), 0) AS
        'voucherTotalRegimentalSettleAmount'
        from ruixuan.promoter_item_order_count
        where 1 = 1
        <if test="start != null and start != ''">
            and stat_date &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and stat_date &lt;= #{end}
        </if>
        <if test="collectSampleId != null and collectSampleId != ''">
            and collect_sample_id = #{collectSampleId}
        </if>
        group by collect_sample_id) t1
        left join (
        select user_id as 'userId',
        count(distinct promoter_id) as 'promoterCount'
        from kuaishou_promoter
        group by user_id) t2
        on t1.collectSampleId = t2.userId
        left join (
        select collect_sample_id as 'collectSampleId',
        count(distinct promoter_id) as 'sampleCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        group by collect_sample_id) t3
        on t2.userId = t3.collectSampleId
        left join (
        select collect_sample_id as 'collectSampleId',
        count(1) as 'sendTotalCount',
        count(distinct promoter_id) as 'sendPromoterCount',
        count(distinct item_id) as 'sendItemCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        <if test="collectSampleId != null and collectSampleId != ''">
            and collect_sample_id = #{collectSampleId}
        </if>
        <if test="start != null and start != ''">
            and create_time &gt;= str_to_date(concat(date_format(#{start}, '%Y-%m-%d'), '00:00:01'), '%Y-%m-%d
            %H:%i:%s')
        </if>
        <if test="end != null and end != ''">
            and create_time &lt;= str_to_date(concat(date_format(#{end}, '%Y-%m-%d'), '23:59:59'), '%Y-%m-%d %H:%i:%s')
        </if>
        group by collect_sample_id
        ) t4
        on t1.collectSampleId = t4.collectSampleId
        order by t1.orderNum desc

    </select>
    <select id="exportAdminReportList" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from (select t1.user_id as 'userId',
        t1.user_name as 'userName',
        count(distinct t2.item_id) as 'itemCount',
        count(t2.oid) as 'orderNum',
        sum(case when t2.order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when t2.order_status != 80 then 1 else 0 end) / count(t2.oid) * 100,2) as 'validRadio',
        sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when t2.send_status = 0 and t2.order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end) / sum(case
        when t2.order_status != 80
        then 1
        else 0 end) * 100,
        2) as 'deliveryRadio',
        sum(case when t2.order_status = 80 then 1 else 0 end) as 'invalidOrderNUm',
        round(sum(case when t2.order_status != 80 then t2.pay_amount else 0 end) / 100 , 2) as 'payAmount',
        round(sum(case
        when t2.order_status != 80 then t2.regimental_promotion_amount
        else 0 end) / 100, 2 )as 'regimentalPromotionAmount',
        round(sum(case when t2.order_status != 80 then t2.total_regimental_settle_amount else 0 end) / 100 , 2) as
        'totalRegimentalSettleAmount'
        from kuaishou_item_bind_user t1
        left join kuaishou_supply_chain t2
        on t1.item_id = t2.item_id
        where 1= 1
        <if test="start != null and start != ''">
            and t2.order_create_time &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and t2.order_create_time &lt;= #{end}
        </if>
        group by t1.user_id) t left join (
        select item_create_id,count(1) as 'sendSampleCount' from kuaishou_item_collect_samples
        where courier_number_time >= from_unixtime(#{start}/1000,'%Y-%m-%d %H:%i:%s')
        and courier_number_time &lt;= from_unixtime(#{end}/1000,'%Y-%m-%d %H:%i:%s')
        group by item_create_id

        ) t1 on t.userId = t1.item_create_id
        where 1 =1
        <if test="userId != null and userId != ''">
            and t.userId = #{userId}
        </if>
        order by t.orderNum desc
    </select>
    <select id="getSupplyChainCourtshipUserList" resultType="com.alibaba.fastjson.JSONObject">
        select t3.user_id as 'userId', t3.nick_name as 'nickMame', t1.role_name as 'roleName', t3.status
        from sys_role t1
                 left join sys_user_role t2 on t1.role_id = t2.role_id
                 left join sys_user t3 on t2.user_id = t3.user_id
        where t1.role_key in (
                              'courtship',
                              'courtshipManager'
            )
        group by t3.user_id
        order by t3.create_time asc
    </select>
    <select id="adminReportListByBind" resultType="com.alibaba.fastjson.JSONObject">
        select * from (
        select t1.user_id as 'userId',
        t1.user_name as 'userName',
        count(distinct t1.item_id) as 'itemCount',
        count(t2.oid) as 'orderNum',
        sum(case when t2.order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when t2.order_status != 80 then 1 else 0 end) / count(t2.oid), 4) as 'validRadio',
        sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when t2.send_status = 0 and t2.order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end) / sum(case
        when t2.order_status != 80
        then 1
        else 0 end),
        4) as 'deliveryRadio',
        sum(case when t2.order_status = 80 then 1 else 0 end) as 'invalidOrderNUm',
        sum(case when t2.order_status != 80 then t2.pay_amount else 0 end) as 'payAmount',
        sum(case
        when t2.order_status != 80 then t2.regimental_promotion_amount
        else 0 end) as 'regimentalPromotionAmount',
        sum(case when t2.order_status != 80 then t2.total_regimental_settle_amount else 0 end) as
        'totalRegimentalSettleAmount'
        from kuaishou_item_list t1
        left join (select item_id,
        oid,
        order_status,
        send_status,
        pay_amount,
        regimental_promotion_amount,
        total_regimental_settle_amount
        from kuaishou_supply_chain
        where order_create_time &gt;= #{start}) t2
        on t1.item_id = t2.item_id
        where t1.create_time >= from_unixtime(#{start}/1000,'%Y-%m-%d %H:%i:%s')
        and t1.create_time &lt;= from_unixtime(#{end}/1000,'%Y-%m-%d %H:%i:%s')
        <if test="userId != null and userId != ''">
            and t1.userId = #{userId}
        </if>
        group by t1.user_id ) t left join (
        select t1.user_id,count(distinct courier_number) as 'sendSampleCount' from kuaishou_item_list t1 left join
        kuaishou_item_collect_samples t2
        on t1.item_id = t2.item_id
        where t1.create_time >= from_unixtime(#{start}/1000,'%Y-%m-%d %H:%i:%s')
        and t1.create_time &lt;= from_unixtime(#{end}/1000,'%Y-%m-%d %H:%i:%s')
        and t2.courier_number_time >= from_unixtime(#{start}/1000,'%Y-%m-%d %H:%i:%s')
        group by t1.user_id
        ) t2 on t.userId = t2.user_id order by t.orderNum desc
    </select>
    <select id="exportAdminReportListByBind" resultType="com.alibaba.fastjson.JSONObject">
        select * from (
        select t1.user_id as 'userId',
        t1.user_name as 'userName',
        count(distinct t1.item_id) as 'itemCount',
        count(t2.oid) as 'orderNum',
        sum(case when t2.order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when t2.order_status != 80 then 1 else 0 end) / count(t2.oid) * 100,2) as 'validRadio',
        sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when t2.send_status = 0 and t2.order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end) / sum(case
        when t2.order_status != 80
        then 1
        else 0 end) * 100,
        2) as 'deliveryRadio',
        sum(case when t2.order_status = 80 then 1 else 0 end) as 'invalidOrderNUm',
        round(sum(case when t2.order_status != 80 then t2.pay_amount else 0 end) / 100 , 2) as 'payAmount',
        round(sum(case
        when t2.order_status != 80 then t2.regimental_promotion_amount
        else 0 end) / 100, 2 )as 'regimentalPromotionAmount',
        round(sum(case when t2.order_status != 80 then t2.total_regimental_settle_amount else 0 end) / 100 , 2) as
        'totalRegimentalSettleAmount'
        from kuaishou_item_list t1
        left join (select item_id,
        oid,
        order_status,
        send_status,
        pay_amount,
        regimental_promotion_amount,
        total_regimental_settle_amount
        from kuaishou_supply_chain
        where order_create_time &gt;= #{start}) t2
        on t1.item_id = t2.item_id
        where t1.create_time >= from_unixtime(#{start}/1000,'%Y-%m-%d %H:%i:%s')
        and t1.create_time &lt;= from_unixtime(#{end}/1000,'%Y-%m-%d %H:%i:%s')
        <if test="userId != null and userId != ''">
            and t1.userId = #{userId}
        </if>
        group by t1.user_id ) t left join (
        select t1.user_id,count(distinct courier_number) as 'sendSampleCount' from kuaishou_item_list t1 left join
        kuaishou_item_collect_samples t2
        on t1.item_id = t2.item_id
        where t1.create_time >= from_unixtime(#{start}/1000,'%Y-%m-%d %H:%i:%s')
        and t1.create_time &lt;= from_unixtime(#{end}/1000,'%Y-%m-%d %H:%i:%s')
        and t2.courier_number_time >= from_unixtime(#{start}/1000,'%Y-%m-%d %H:%i:%s')
        group by t1.user_id
        ) t2 on t.userId = t2.user_id order by t.orderNum desc;

    </select>
    <select id="bdReportListV2" resultType="com.alibaba.fastjson.JSONObject">
        select t.*,
        tt.promoterCount,
        ttt.sampleCount,
        tttt.sendTotalCount,
        tttt.sendPromoterCount,
        tttt.sendItemCount,
        IFNULL( ttttt.promoters_count,'-') as 'promotersCount',
        concat(round(ttt.sampleCount / tt.promoterCount * 100, 2), '%') as 'sampleRate',
        tt.promoterCount - ttt.sampleCount as 'notSampleCount'
        from (
        select t1.user_id as 'collectSampleId',
        t1.user_name as 'collectSampleName',
        sum(t2.order_num) as 'orderNum',
        sum(t2.valid_order_num) as 'validOrderNum',
        concat(round(sum(t2.valid_order_num) / sum(t2.order_num) * 100, 2), '%') as 'validOrderRate',
        round(sum(t2.order_amount) / 100, 2) as 'orderAmount',
        round(sum(t2.regimental_promotion_amount) / 100, 2) as 'regimentalPromotionAmount',
        round(sum(t2.total_regimental_settle_amount) / 100, 2) as 'totalRegimentalSettleAmount'
        from promoter_bind_channel_date t1
        left join promoter_item_order_count t2
        on t1.promoter_id = t2.promoter_id and t1.stat_date = t2.stat_date
        where 1 = 1
        and t1.media_id = 2
        <if test="start != null and start != ''">
            and t1.stat_date &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and t1.stat_date &lt;= #{end}
        </if>
        <if test="collectSampleId != null and collectSampleId != ''">
            and t1.user_id = #{collectSampleId}
        </if>
        group by t1.user_id) t
        left join (select user_id,
        count(distinct promoter_id) as 'promoterCount'
        from kuaishou_promoter
        group by user_id) tt on t.collectSampleId = tt.user_id
        left join (select collect_sample_id as 'user_id',
        count(distinct promoter_id) as 'sampleCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        group by collect_sample_id) ttt on t.collectSampleId = ttt.user_id
        left join (select collect_sample_id as 'user_id',
        count(1) as 'sendTotalCount',
        count(distinct promoter_id) as 'sendPromoterCount',
        count(distinct item_id) as 'sendItemCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        <if test="collectSampleId != null and collectSampleId != ''">
            and collect_sample_id = #{collectSampleId}
        </if>
        <if test="start != null and start != ''">
            and create_time &gt;= str_to_date(concat(date_format(#{start}, '%Y-%m-%d'), '00:00:01'), '%Y-%m-%d
            %H:%i:%s')
        </if>
        <if test="end != null and end != ''">
            and create_time &lt;= str_to_date(concat(date_format(#{end}, '%Y-%m-%d'), '23:59:59'), '%Y-%m-%d %H:%i:%s')
        </if>
        group by collect_sample_id) tttt on t.collectSampleId = tttt.user_id
        left join (select user_id, promoters_count
        from user_promoters_count
        where media_id = 2) ttttt
        on t.collectSampleId = ttttt.user_id
        order by t.orderNum desc
    </select>
    <select id="bdTotalV2" resultType="com.alibaba.fastjson.JSONObject">
        select t.*,tt.sampleCount
        from (select t1.user_id as 'collectSampleId',
        t1.user_name as 'collectSampleName',
        sum(t2.order_num) as 'orderNum',
        sum(t2.valid_order_num) as 'validOrderNum',
        concat(round(sum(t2.valid_order_num) / sum(t2.order_num) * 100, 2), '%') as 'validOrderRate',
        round(sum(t2.order_amount) / 100, 2) as 'orderAmount',
        round(sum(t2.regimental_promotion_amount) / 100, 2) as 'regimentalPromotionAmount',
        round(sum(t2.total_regimental_settle_amount) / 100, 2) as 'totalRegimentalSettleAmount'
        from promoter_bind_channel_date t1
        left join promoter_item_order_count t2
        on t1.promoter_id = t2.promoter_id and t1.stat_date = t2.stat_date
        where 1 = 1
        <if test="start != null and start != '' ">
            and t1.stat_date &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and t1.stat_date &lt;= #{end}
        </if>
        <if test="collectSampleId != null and collectSampleId != ''">
            and t1.user_id = #{collectSampleId}
        </if>
        group by t1.user_id) t
        join (select count(distinct item_id) as 'sampleCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        and collect_sample_id = #{collectSampleId} ) tt

    </select>
    <select id="bdDetailListV2" resultType="com.alibaba.fastjson.JSONObject">
        select t.*, tt.sampleCount
        from (select t1.promoter_id as 'promoterId',
        t1.promoter_name as 'promoterName',
        t1.promoter_url as 'promoterUrl',
        sum(t2.order_num) as 'orderNum',
        sum(t2.valid_order_num) as 'validOrderNum',
        concat(round(sum(t2.valid_order_num) / sum(t2.order_num) * 100, 2), '%') as 'validOrderRate',
        round(sum(t2.order_amount) / 100, 2) as 'orderAmount',
        round(sum(t2.regimental_promotion_amount) / 100, 2) as 'regimentalPromotionAmount',
        round(sum(t2.total_regimental_settle_amount) / 100, 2) as 'totalRegimentalSettleAmount'
        from promoter_bind_channel_date t1
        left join promoter_item_order_count t2
        on t1.promoter_id = t2.promoter_id and t1.stat_date = t2.stat_date
        where 1 = 1
        <if test="start != null and start != '' ">
            and t1.stat_date &gt;= #{start}
        </if>
        <if test="end != null and end != ''">
            and t1.stat_date &lt;= #{end}
        </if>
        <if test="collectSampleId != null and collectSampleId != ''">
            and t1.user_id = #{collectSampleId}
        </if>
        group by t1.promoter_id) t
        left join (select promoter_id,
        count(distinct item_id) as 'sampleCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        and collect_sample_id = #{collectSampleId}
        group by promoter_id) tt on t.promoterId = tt.promoter_id
        where t.orderNum > 0
        order by ${fieId} ${sort}
    </select>
    <select id="bdReportListV2ByBind" resultType="com.alibaba.fastjson.JSONObject">
        select t.*,
        tt.promoterCount,
        ttt.sampleCount,
        tttt.sendTotalCount,
        tttt.sendPromoterCount,
        tttt.sendItemCount,
        IFNULL(ttttt.promoters_count, '-') as 'promotersCount'
        from (select t1.user_id as 'collectSampleId',
        t1.user_name as 'collectSampleName',
        sum(t2.order_num) as 'orderNum',
        sum(t2.valid_order_num) as 'validOrderNum',
        concat(round(sum(t2.valid_order_num) / sum(t2.order_num) * 100, 2), '%') as 'validOrderRate',
        round(sum(t2.order_amount) / 100, 2) as 'orderAmount',
        round(sum(t2.regimental_promotion_amount) / 100, 2) as 'regimentalPromotionAmount',
        round(sum(t2.total_regimental_settle_amount) / 100, 2) as 'totalRegimentalSettleAmount'
        from kuaishou_promoter t1
        left join(select *
        from promoter_item_order_count
        where stat_date &gt;= #{bindStartDate}) t2
        on t1.promoter_id = t2.promoter_id
        where 1 = 1
        <if test="bindStartDate != null and bindStartDate != '' ">
            and t1.create_time &gt;= str_to_date(concat(date_format(#{bindStartDate}, '%Y-%m-%d'), '00:00:01'),
            '%Y-%m-%d %H:%i:%s')
        </if>
        <if test="bindEndDate != null and bindEndDate != '' ">
            and t1.create_time &lt;= str_to_date(concat(date_format(#{bindEndDate}, '%Y-%m-%d'), '23:59:59'), '%Y-%m-%d
            %H:%i:%s')
        </if>
        <if test="collectSampleId != null and collectSampleId != '' ">
            and t1.user_id = #{collectSampleId}
        </if>
        group by t1.user_id) t
        left join (select user_id,
        count(distinct promoter_id) as 'promoterCount'
        from kuaishou_promoter
        where 1 = 1
        <if test="collectSampleId != null and collectSampleId != '' ">
            and user_id = #{collectSampleId}
        </if>
        group by user_id) tt on t.collectSampleId = tt.user_id
        left join (select collect_sample_id as 'user_id',
        count(distinct promoter_id) as 'sampleCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        <if test="collectSampleId != null and collectSampleId != '' ">
            and collect_sample_id = #{collectSampleId}
        </if>
        group by collect_sample_id) ttt on t.collectSampleId = ttt.user_id
        left join (select collect_sample_id as 'user_id',
        count(1) as 'sendTotalCount',
        count(distinct promoter_id) as 'sendPromoterCount',
        count(distinct item_id) as 'sendItemCount'
        from kuaishou_item_collect_samples
        where collect_sample_status > 2
        <if test="collectSampleId != null and collectSampleId != '' ">
            and collect_sample_id = #{collectSampleId}
        </if>
        <if test="bindStartDate != null and bindStartDate != '' ">
            and create_time &gt;= str_to_date(concat(date_format(#{bindStartDate}, '%Y-%m-%d'), '00:00:01'),
            '%Y-%m-%d %H:%i:%s')
        </if>
        <if test="bindEndDate != null and bindEndDate != '' ">
            and create_time &lt;= str_to_date(concat(date_format(#{bindEndDate}, '%Y-%m-%d'), '23:59:59'), '%Y-%m-%d
            %H:%i:%s')
        </if>
        group by collect_sample_id) tttt on t.collectSampleId = tttt.user_id
        left join (select user_id, promoters_count
        from user_promoters_count
        where media_id = 2
        <if test="collectSampleId != null and collectSampleId != '' ">
            and user_id = #{collectSampleId}
        </if>
        ) ttttt
        on t.collectSampleId = ttttt.user_id
        order by t.orderNum desc


    </select>
    <select id="exportBindOrder" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from (select item_id as 'itemId', item_title as 'itemTitle', user_id as 'userId', user_name as 'userName'
        from kuaishou_item_list
        where
        create_time &gt;= str_to_date(concat(#{bindStartDate},' 00:00:01'),'%Y-%m-%d
        %H:%i:%s')
        and
        create_time &lt;= str_to_date(concat(#{bindEndDate},' 23:59:59'),'%Y-%m-%d
        %H:%i:%s')
        ) t1
        left join
        (select item_id as 'itemId',
        image_url as 'imageUrl',
        count(oid) as 'orderNum',
        sum(case when order_status != 80 then 1 else 0 end) as 'validOrderNUm',
        round(sum(case when order_status != 80 then 1 else 0 end) / count(oid) * 100, 2) as 'validRadio',
        sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) as 'deliveryNum',
        sum(case when send_status = 0 and order_status != 80 then 1 else 0 end) as 'notDeliveryNum',
        round(sum(case when send_status = 1 and order_status != 80 then 1 else 0 end) / sum(case when order_status
        != 80 then 1 else 0 end) * 100, 2) as 'deliveryRadio',
        round(item_commission_rate / 10,2) as 'itemCommissionRate'
        from kuaishou_supply_chain
        where order_create_time &gt;= #{start}
        group by item_id) t2 on t1.itemId = t2.itemId
        where 1 = 1
        <if test="itemId != null and itemId != ''">
            and t1.item_id = #{itemId}
        </if>
        <if test="itemTitle != null and itemTitle != ''">
            and t1.item_title like concat(concat('%',#{itemTitle}),'%')
        </if>
        <if test="userId != null and userId != ''">
            and t1.userId = #{userId}
        </if>
         order by t2.orderNum desc
    </select>
</mapper>