<?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.jiaoyang.mapper.JiaoYangReportMapper">

    <select id="item" resultType="com.alibaba.fastjson.JSONObject">
        select t.* from (
        select
        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.commission_rate as 'itemCommissionRate'
        from jy_order_list t1
        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="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
        order by ${fieId} ${sort}
    </select>
    <select id="itemDetail" resultType="com.alibaba.fastjson.JSONObject">
        select t.*,tt.headImg,ttt.type,ttt.user_name as 'userName'
        from (
        select
        clip_nick_name as 'promoterNickName',
        clip_user_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 jy_order_list
        where 1= 1
        <if test="itemId != null and itemId != ''">
            and item_id = #{itemId}
        </if>
        <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="promoterId != null and promoterId != ''">
            and clip_user_id = #{promoterId}
        </if>
        <if test="promoterNickName != null and promoterNickName != ''">
            and clip_nick_name like concat(concat('%',#{promoterNickName}),'%')
        </if>
        group by clip_user_id) t
        left join (select clip_id,head_img as 'headImg' from jy_clip_cooperation where stat_date =
        DATE_FORMAT(NOW(),'%Y%m%d') ) tt on t.promoterId = tt.clip_id
        left join jy_kuaishou_promoter ttt on t.promoterId = ttt.promoter_id
        order by t.orderNum desc


    </select>
    <select id="promoter" resultType="com.alibaba.fastjson.JSONObject">
        select t.*,
        tt.head_img as 'headImg',
        ttt.type as 'type',
        ttt.user_name as 'userName'
        from (
        select clip_user_id as 'promoterId',
        clip_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 mcn_promotion_amount else 0 end) as 'regimentalPromotionAmount'
        from jy_order_list
        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="promoterId != null and promoterId != '' ">
            and clip_user_id = #{promoterId}
        </if>
        <if test="promoterNickName != null and promoterNickName != '' ">
            and clip_nick_name like concat(concat('%',#{promoterNickName}),'%')
        </if>
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by clip_user_id) t
        left join ( select * from jy_clip_cooperation where stat_date = DATE_FORMAT(NOW(),'%Y%m%d')) tt on t.promoterId
        = tt.clip_id
        left join jy_kuaishou_promoter ttt on t.promoterId = ttt.promoter_id
        where 1 = 1
        <if test="type != null and type != '' ">
            and ttt.type = #{type}
        </if>
        order by t.orderNum desc
    </select>
    <select id="anchorOrderDetail" resultType="com.alibaba.fastjson.JSONObject">
        select t.*
        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 jy_order_list
        where 1= 1
        <if test="promoterId != null and promoterId != ''">
            and clip_user_id = #{promoterId}
        </if>

        <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="itemId != null and itemId != ''">
            and item_id = #{itemId}
        </if>
        <if test="itemTitle != null and itemTitle != ''">
            and item_title like concat(concat('%',#{itemTitle}),'%')
        </if>
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by item_id) t
        order by t.orderNum desc
    </select>
    <select id="promoterStatistics" resultType="com.alibaba.fastjson.JSONObject">
        select date_format(t.stat_date, '%Y-%m-%d') as 'date',
        count(t.oid) as 'orderNum'
        from (select oid,
        stat_date
        from jy_order_list
        where 1 = 1
        <if test="promoterId != null and promoterId != ''">
            and clip_user_id = #{promoterId}
        </if>
        <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='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t
        group by t.stat_date
        order by t.stat_date asc
    </select>
    <select id="getClipCooperation" resultType="com.alibaba.fastjson.JSONObject">
        select clip_id as 'clipId',
        clip_name as 'clipName',
        head_img as 'headImg',
        fans_num as 'fansNum',
        gmv_30d as 'gmv30d',
        start_time as 'startTime',
        end_time as 'endTime',
        (case when status = 1
        then '上架'
        when status = 3
        then '已结束'
        when status = 2
        then '待确认'
        else '-'
        end
        ) as 'status',
        termination_reason as 'terminationReason',
        concat(commission_rate_view,'%') as 'commissionRateView',
        shop_score as 'shopScore',
        clip_gmv_30d as 'clipGmv30d',
        auth_item_gmv_30d as 'authItemGmv30d',
        video_count as 'videoCount',
        video_count as 'videoShow',
        stat_date as 'statDate'
        from jy_clip_cooperation
        where
        stat_date = #{date}
        <if test="promoterId != null and promoterId != ''">
            and clipId = #{promoterId}
        </if>
        <if test="promoterName != null and promoterName != ''">
            and clipName like concat(concat('%',#{promoterName}),'%')
        </if>
    </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 jy_order_list
        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='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </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 jy_order_list
        where 1= 1
        <if test="nowDate != null and nowDate != ''">
            and stat_date = #{nowDate}
        </if>
        <if test="hour != null and hour != ''">
            and stat_hour &lt;= #{hour}
        </if>
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t1
        join (select count(oid) as 'oid'
        from jy_order_list
        where 1=1
        <if test="yesDate != null and yesDate != ''">
            and stat_date = #{yesDate}
        </if>
        <if test="hour != null and hour != ''">
            and stat_hour &lt;= #{hour}
        </if>
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t2
    </select>
    <select id="getServiceAmountTotal" resultType="com.alibaba.fastjson.JSONObject">
        select
        count(distinct clip_user_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 jy_order_list
        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='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </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 mcn_promotion_amount else 0 end) as 'regimentalPromotionAmount',
        sum(case when order_status != 80 then total_regimental_settle_amount else 0 end) as
        'totalRegimentalSettleAmount'
        from jy_order_list
        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='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </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 jy_order_list
        where 1= 1
        <if test="nowDate != null and nowDate != ''">
            and stat_date = #{nowDate}
        </if>
        <if test="hour != null and hour != ''">
            and stat_hour &lt;= #{hour}
        </if>
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t1
        join (select sum(pay_amount) as 'payAmount'
        from jy_order_list
        where 1=1
        <if test="yesDate != null and yesDate != ''">
            and stat_date = #{yesDate}
        </if>
        <if test="hour != null and jhour != ''">
            and stat_hour &lt;= #{hour}
        </if>
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t2
    </select>
    <select id="getPromoterRankRatioList" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.*,
        round((t1.orderNum - t2.orderNum) / t2.orderNum,4) as 'radio'
        from (
        select
        clip_user_id as 'promoterId',
        clip_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 jy_order_list
        where 1= 1
        <if test="nowDate != null and nowDate != ''">
            and stat_date = #{nowDate}
        </if>
        <if test="hour != null and hour != ''">
            and stat_hour &lt;= #{hour}
        </if>
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by clip_user_id
        ) t1
        left join (select
        clip_user_id as 'promoterId',
        count(oid) as 'orderNum'
        from jy_order_list
        where 1=1
        <if test="yesDate != null and yesDate != ''">
            and stat_date = #{yesDate}
        </if>
        <if test="hour != null and hour != ''">
            and stat_hour &lt;= #{hour}
        </if>
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by clip_user_id
        ) t2 on t1.promoterId = t2.promoterId
        order by t1.orderNum desc
    </select>
    <select id="getPromoterRatioList" resultType="com.alibaba.fastjson.JSONObject">
        select * from (
        select
        clip_user_id as 'promoterId',
        clip_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 jy_order_list
        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='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by clip_user_id) t
        order by t.orderNum desc
    </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 jy_order_list
        where 1= 1
        <if test="nowDate != null and nowDate != ''">
            and stat_date = #{nowDate}
        </if>
        <if test="hour != null and hour != ''">
            and stat_hour &lt;= #{hour}
        </if>
        group by item_id
        ) t1
        left join (select
        item_id as 'itemId',
        count(oid) as 'orderNum'
        from jy_order_list
        where 1=1
        <if test="yesDate != null and yesDate != ''">
            and stat_date = #{yesDate}
        </if>
        <if test="hour != null and hour != ''">
            and stat_hour &lt;= #{hour}
        </if>
        group by item_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 jy_order_list
        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>
        group by item_id) t
        order by t.orderNum desc
    </select>
    <select id="indexStatistics" resultType="com.alibaba.fastjson.JSONObject">
        select date_format(t.stat_date,'%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,
        stat_date
        from jy_order_list
        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='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t
        group by t.stat_date
        order by t.stat_date asc
    </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 jy_order_list
        where 1= 1
        <if test="thisCycleStartTemp != null and thisCycleStartTemp != ''">
            and stat_date &gt;= #{thisCycleStartTemp}
        </if>
        <if test="thisCycleEndTemp != null and thisCycleEndTemp != ''">
            and stat_date &lt;= #{thisCycleEndTemp}
        </if>
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t1
        join (
        select count(oid) as 'oid',
        sum(order_amount) as 'orderAmount'
        from jy_order_list
        where 1=1
        <if test="lastCycleStartTemp != null and lastCycleStartTemp != ''">
            and stat_date &gt;= #{lastCycleStartTemp}
        </if>
        <if test="lastCycleStartTemp != null and lastCycleStartTemp != ''">
            and stat_date &lt;= #{lastCycleEndTemp}
        </if>
        <if test='promoterIds != null and promoterIds.size() > 0'>
            AND clip_user_id in
            <foreach collection="promoterIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t2
    </select>
    <select id="getPromoterIdsByUserIds" resultType="java.lang.Long">
        select promoter_id from jy_kuaishou_promoter where
        1 = 1
        and user_id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
    </select>


</mapper>