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

    <resultMap type="com.ruixuan.isc.entity.KuaishouPromoter" id="KuaishouPromoterResult">
        <result property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="mediaId" column="media_id"/>
        <result property="userName" column="user_name"/>
        <result property="promoterId" column="promoter_id"/>
        <result property="promoterNickName" column="promoter_nick_name"/>
        <result property="state" column="state"/>
        <result property="province" column="province"/>
        <result property="commissionRequirement" column="commission_requirement"/>
        <result property="categoryRequirement" column="category_requirement"/>
        <result property="createTime" column="create_time"/>
        <result property="promoterUrl" column="promoter_url"/>
        <result property="totalSale" column="total_sale"/>
        <result property="fansNumber" column="fans_number"/>
        <result property="phone" column="phone"/>
        <result property="consignee" column="consignee"/>
        <result property="promoterAddress" column="promoter_address"/>
        <result property="avgVideoSales" column="avg_video_sales"/>
        <result property="videoSales" column="video_sales"/>
        <result property="updateTime" column="update_time"/>
        <result property="monthDayGmv" column="30day_gmv"/>
        <result property="monthDayOrderNum" column="30day_order_num"/>
        <result property="validAmount" column="valid_amount"/>
        <result property="followerIncr" column="follower_incr"/>
        <result property="liveCount30" column="live_count_30"/>
        <result property="awemeAvgDiggCount30" column="aweme_avg_digg_count_30"/>
        <result property="awemeDiggFollowerRation" column="aweme_digg_follower_ration"/>
        <result property="liveAverageUser30" column="live_average_user_30"/>
        <result property="followUpRecord" column="txt"/>
    </resultMap>

    <sql id="selectKuaishouPromoterVo">
        select id,
               media_id,
               user_id,
               user_name,
               promoter_id,
               promoter_nick_name,
               `state`,
               province,
               commission_requirement,
               category_requirement,
               promoter_url,
               phone,
               consignee,
               promoter_address,
               total_sale,
               fans_number,
               avg_video_sales,
               video_sales,
               create_time,
               update_time
        from kuaishou_promoter
    </sql>

    <select id="selectKuaishouPromoterList" resultMap="KuaishouPromoterResult">
        SELECT
        t1.id,
        t1.user_id,
        t1.media_id,
        t1.user_name,
        t1.promoter_id,
        t1.promoter_nick_name,
        t1.`state`,
        t1.province,
        t1.commission_requirement,
        t1.category_requirement,
        t1.promoter_url,
        t1.phone,
        t1.consignee,
        t1.promoter_address,
        if(t1.media_id = 1, t1.total_sale,t3.total_sale ) as 'total_sale',
        if(t1.media_id = 1, t1.fans_number,t3.fans_number ) as 'fans_number',
        if(t1.media_id = 1, t1.avg_video_sales,t3.avg_video_sales ) as 'avg_video_sales',
        if(t1.media_id = 1,t1.video_sales ,t3.video_sales ) as 'video_sales',
        t1.create_time,
        t2.30day_gmv,
        t2.30day_order_num,
        t1.follower_incr,
        t1.live_count_30,
        t1.aweme_avg_digg_count_30,
        t1.aweme_digg_follower_ration,
        t1.live_average_user_30,
        case
        WHEN t3.total_sale like '%万%'
        then SUBSTRING_INDEX(t3.total_sale,'-',-1)+0
        else SUBSTRING_INDEX(t3.total_sale,'-',-1)/10000 end as 'totalSaleOrder',
        case
        WHEN t3.video_sales like '%万%'
        then SUBSTRING_INDEX(t3.video_sales,'-',-1)+0
        else SUBSTRING_INDEX(t3.video_sales,'-',-1)/10000 end as 'videoSalesOrder',
        case
        WHEN t3.avg_video_sales like '%万%'
        then SUBSTRING_INDEX(t3.avg_video_sales,'-',-1)+0
        else SUBSTRING_INDEX(t3.avg_video_sales,'-',-1)/10000 end as 'avgVideoSalesOrder',
        t4.rule_id,
        t4.rule_name
        FROM
        kuaishou_promoter t1
        LEFT JOIN promoter_oerder_amount t2 on t1.promoter_id = t2.promoter_id
        LEFT JOIN promoter_sales_info t3 ON t1.promoter_id = t3.promoter_id
        left join promoter_bind_rule t4 on t1.promoter_id = t4.promoter_id
        <where>
            <if test="userList != null and userList.size() > 0  ">
                and t1.user_id in
                <foreach collection="userList" item="userId" open="(" separator="," close=")">
                    #{userId}
                </foreach>
            </if>
            <if test="promoterId != null ">
                and t1.promoter_id = #{promoterId}
            </if>
            <if test="promoterNickName != null  and promoterNickName != ''">
                and t1.promoter_nick_name like concat('%', #{promoterNickName}, '%')
            </if>
            <if test="mediaId != null  and mediaId != ''">
                and t1.media_id = #{mediaId}
            </if>
        </where>
        order by ${parameter} ${orderBy}
    </select>

    <select id="selectKuaishouPromoterList3" resultType="com.ruixuan.isc.entity.JYKuaishouPromoter">
        SELECT
        t1.id,
        t1.user_id,
        t1.type,
        t1.media_id,
        t1.user_name,
        t1.promoter_id,
        t1.promoter_nick_name,
        t1.`state`,
        t1.province,
        t1.commission_requirement,
        t1.category_requirement,
        t1.promoter_url,
        t1.phone,
        t1.consignee,
        t1.promoter_address,
        if(t1.media_id = 1, t1.total_sale,t3.total_sale ) as 'total_sale',
        if(t1.media_id = 1, t1.fans_number,t3.fans_number ) as 'fans_number',
        if(t1.media_id = 1, t1.avg_video_sales,t3.avg_video_sales ) as 'avg_video_sales',
        if(t1.media_id = 1,t1.video_sales ,t3.video_sales ) as 'video_sales',
        t1.create_time,
        t2.30day_gmv,
        t2.30day_order_num,
        t1.follower_incr,
        t1.live_count_30,
        t1.aweme_avg_digg_count_30,
        t1.aweme_digg_follower_ration,
        t1.live_average_user_30,
        case
        WHEN t3.total_sale like '%万%'
        then SUBSTRING_INDEX(t3.total_sale,'-',-1)+0
        else SUBSTRING_INDEX(t3.total_sale,'-',-1)/10000 end as 'totalSaleOrder',
        case
        WHEN t3.video_sales like '%万%'
        then SUBSTRING_INDEX(t3.video_sales,'-',-1)+0
        else SUBSTRING_INDEX(t3.video_sales,'-',-1)/10000 end as 'videoSalesOrder',
        case
        WHEN t3.avg_video_sales like '%万%'
        then SUBSTRING_INDEX(t3.avg_video_sales,'-',-1)+0
        else SUBSTRING_INDEX(t3.avg_video_sales,'-',-1)/10000 end as 'avgVideoSalesOrder',
        t4.rule_id,
        t4.rule_name
        FROM
        jy_kuaishou_promoter t1
        LEFT JOIN promoter_oerder_amount t2 on t1.promoter_id = t2.promoter_id
        LEFT JOIN promoter_sales_info t3 ON t1.promoter_id = t3.promoter_id
        left join promoter_bind_rule t4 on t1.promoter_id = t4.promoter_id
        <where>
            <if test="userList != null and userList.size() > 0  ">
                and t1.user_id in
                <foreach collection="userList" item="userId" open="(" separator="," close=")">
                    #{userId}
                </foreach>
            </if>
            <if test="promoterId != null ">
                and t1.promoter_id = #{promoterId}
            </if>
            <if test="promoterNickName != null  and promoterNickName != ''">
                and t1.promoter_nick_name like concat('%', #{promoterNickName}, '%')
            </if>
            <if test="mediaId != null  and mediaId != ''">
                and t1.media_id = #{mediaId}
            </if>
            <if test="type != null  and type != ''">
                and t1.type = #{type}
            </if>
        </where>
        order by ${parameter} ${orderBy}
    </select>

    <select id="selectAllPromoterList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM (
        SELECT
        t1.promoter_id AS 'promoterId',
        (CASE WHEN t2.promoter_id IS NULL THEN 2 ELSE 1 END) AS 'status',
        IF(t0.promoter_name is null,t2.promoter_nick_name,t0.promoter_name) AS 'promoterNickName',
        IFNULL(t2.userName,'') AS 'userName',
        IFNULL(t2.commission_requirement,'') AS 'commissionRequirement',
        IF(t0.handurl is null,t2.promoter_url,t0.handurl) AS 'promoterUrl',
        t1.30day_gmv AS 'monthDayGmv',
        t1.30day_order_num AS 'monthDayOrderNum',+
        t1.valid_amount AS 'validAmount',
        t3.fans_number AS 'fansNumber',
        t3.video_sales AS 'videoSales',
        t3.avg_video_sales AS 'avgVideoSales',
        t3.total_sale AS 'totalSale',
        case
        WHEN t3.video_sales like '%万%'
        then SUBSTRING_INDEX(t3.video_sales,'-',-1)+0
        else SUBSTRING_INDEX(t3.video_sales,'-',-1)/10000 end as 'videoSalesOrder',
        case
        WHEN t3.avg_video_sales like '%万%'
        then SUBSTRING_INDEX(t3.avg_video_sales,'-',-1)+0
        else SUBSTRING_INDEX(t3.avg_video_sales,'-',-1)/10000 end as 'avgVideoSalesOrder'
        FROM
        promoter_oerder_amount t1
        LEFT JOIN promoter_info t0 ON t1.promoter_id = t0.promoter_id
        LEFT JOIN promoter_sales_info t3 ON t1.promoter_id = t3.promoter_id
        <choose>
            <when test="userId !=null and userId !='' ">
                RIGHT
            </when>
            <otherwise>
                LEFT
            </otherwise>
        </choose>
        JOIN (
        SELECT
        promoter_id,
        group_concat( user_name ) AS 'userName',
        fans_number, promoter_url,
        commission_requirement,
        promoter_nick_name,
        avg_video_sales,
        video_sales
        FROM kuaishou_promoter
        <where>
            <if test="userId != null ">
                and user_id = #{userId}
            </if>
        </where>
        GROUP BY promoter_id
        ) t2 ON t1.promoter_id = t2.promoter_id
        <where>
            <if test="promoterId != null ">
                and t1.promoter_id = #{promoterId}
            </if>
            <if test="promoterNickName != null  and promoterNickName != ''">
                and t0.promoter_name like concat('%', #{promoterNickName}, '%')
            </if>
        </where>
        ) t
        <where>
            <if test="status != null ">
                and `status` = #{status}
            </if>
        </where>
        order by ${parameter} ${orderBy}
    </select>

    <select id="selectKuaishouPromoterById" parameterType="Long" resultMap="KuaishouPromoterResult">
        <include refid="selectKuaishouPromoterVo"/>
        where id = #{id}
    </select>

    <select id="selectKuaishouPromoterById3" parameterType="Long" resultType="com.ruixuan.isc.entity.JYKuaishouPromoter">
        select id,
               media_id,
               user_id,
               user_name,
               promoter_id,
               promoter_nick_name,
               `state`,
               province,
               commission_requirement,
               category_requirement,
               promoter_url,
               phone,
               consignee,
               promoter_address,
               total_sale,
               fans_number,
               avg_video_sales,
               video_sales,
               create_time,
               update_time
        from jy_kuaishou_promoter
        where id = #{id}
    </select>

    <select id="getFailInfo" resultMap="KuaishouPromoterResult">
        <include refid="selectKuaishouPromoterVo"/>
        where (promoter_nick_name = '' or promoter_nick_name is null)
        AND state =1
    </select>


    <select id="getFailInfo3" resultType="com.ruixuan.isc.entity.JYKuaishouPromoter">
        select id,
               media_id,
               user_id,
               user_name,
               promoter_id,
               promoter_nick_name,
               `state`,
               province,
               commission_requirement,
               category_requirement,
               promoter_url,
               phone,
               consignee,
               promoter_address,
               total_sale,
               fans_number,
               avg_video_sales,
               video_sales,
               create_time,
               update_time
        from jy_kuaishou_promoter
        where (promoter_nick_name = '' or promoter_nick_name is null)
          AND state = 1
    </select>

    <select id="getBoundPromoterRoleKey" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.user_id,
               t3.role_key,
               t1.user_name
        FROM kuaishou_promoter t1
                 LEFT JOIN sys_user_role t2 ON t1.user_id = t2.user_id
                 LEFT JOIN sys_role t3 ON t2.role_id = t3.role_id
        Where t1.promoter_id = #{promoterId}
    </select>

    <select id="selectPromoterIdList" resultType="String">
        select promoter_id
        from kuaishou_promoter
        <where>
            <if test="userList != null ">
                AND user_id in
                <foreach collection="userList" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="mediaId != null ">
                AND media_id =#{mediaId}
            </if>
        </where>
    </select>

    <select id="selectPromoterIdListByTime" resultType="String">
        select promoter_id
        from promoter_bind_channel_date
        WHERE stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test="userList != null ">
            AND user_id in
            <foreach collection="userList" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getOneByIdAndPromoterId" resultMap="KuaishouPromoterResult">
        <include refid="selectKuaishouPromoterVo"/>
        where promoter_id = #{promoterId}
        <if test="userId != null ">
            AND user_id = #{userId}
        </if>
        limit 1
    </select>

    <select id="getOneByIdAndPromoterId3" resultType="com.ruixuan.isc.entity.JYKuaishouPromoter">
        select
        id,
        media_id,
        `type`,
        user_id,
        user_name,
        promoter_id,
        promoter_nick_name,
        `state`,
        province,
        commission_requirement,
        category_requirement,
        promoter_url,
        phone,
        consignee,
        promoter_address,
        total_sale,
        fans_number,
        avg_video_sales,
        video_sales,
        create_time,
        update_time
        from jy_kuaishou_promoter
        where promoter_id = #{promoterId}
        <if test="userId != null ">
            AND user_id = #{userId}
        </if>
        limit 1
    </select>


    <select id="getgetMonthPromoterTotal" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.count,
        CONCAT(ROUND(t2.count / t1.count * 100, 2), '%') as 'rate'
        FROM
        (
        SELECT count(promoter_id) as 'count'
        FROM kuaishou_promoter
        WHERE DATE_FORMAT(create_time, '%Y-%m-%d') >= #{start}
        AND DATE_FORMAT(create_time, '%Y-%m-%d') &lt;= #{end}
        AND media_id = #{mediaId}
        <if test="userList != null and userList.size() > 0 ">
            AND user_id in
            <foreach collection="userList" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t1
        LEFT JOIN
        (SELECT count(promoter_id) as 'count'
        FROM kuaishou_promoter
        WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = #{end}
        AND media_id = #{mediaId}
        <if test="userList != null and userList.size() > 0 ">
            AND user_id in
            <foreach collection="userList" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t2 ON 1 = 1
    </select>


    <insert id="insertKuaishouPromoter" parameterType="com.ruixuan.isc.entity.KuaishouPromoter" useGeneratedKeys="true"
            keyProperty="id">
        insert into kuaishou_promoter
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="mediaId != null">media_id,</if>
            <if test="userName != null">user_name,</if>
            <if test="promoterId != null">promoter_id,</if>
            <if test="promoterNickName != null">promoter_nick_name,</if>
            <if test="state != null">state,</if>
            <if test="province != null">province,</if>
            <if test="commissionRequirement != null">commission_requirement,</if>
            <if test="categoryRequirement != null">category_requirement,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="totalSale != null">total_sale,</if>
            <if test="fansNumber != null">fans_number,</if>
            <if test="promoterUrl != null">promoter_url,</if>
            <if test="phone != null">phone,</if>
            <if test="consignee != null">consignee,</if>
            <if test="promoterAddress != null">promoter_address,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="mediaId != null">#{mediaId},</if>
            <if test="userName != null">#{userName},</if>
            <if test="promoterId != null">#{promoterId},</if>
            <if test="promoterNickName != null">#{promoterNickName},</if>
            <if test="state != null">#{state},</if>
            <if test="province != null">#{province},</if>
            <if test="commissionRequirement != null">#{commissionRequirement},</if>
            <if test="categoryRequirement != null">#{categoryRequirement},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="totalSale != null">#{totalSale},</if>
            <if test="fansNumber != null">#{fansNumber},</if>
            <if test="promoterUrl != null">#{promoterUrl},</if>
            <if test="phone != null">#{phone},</if>
            <if test="consignee != null">#{consignee},</if>
            <if test="promoterAddress != null">#{promoterAddress},</if>
        </trim>
    </insert>


    <insert id="insertKuaishouPromoter3" parameterType="com.ruixuan.isc.entity.JYKuaishouPromoter" useGeneratedKeys="true"
            keyProperty="id">
        insert into jy_kuaishou_promoter
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="mediaId != null">media_id,</if>
            <if test="type != null">type,</if>
            <if test="userName != null">user_name,</if>
            <if test="promoterId != null">promoter_id,</if>
            <if test="promoterNickName != null">promoter_nick_name,</if>
            <if test="state != null">state,</if>
            <if test="province != null">province,</if>
            <if test="commissionRequirement != null">commission_requirement,</if>
            <if test="categoryRequirement != null">category_requirement,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="totalSale != null">total_sale,</if>
            <if test="fansNumber != null">fans_number,</if>
            <if test="promoterUrl != null">promoter_url,</if>
            <if test="phone != null">phone,</if>
            <if test="consignee != null">consignee,</if>
            <if test="promoterAddress != null">promoter_address,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="mediaId != null">#{mediaId},</if>
            <if test="type != null">#{type},</if>
            <if test="userName != null">#{userName},</if>
            <if test="promoterId != null">#{promoterId},</if>
            <if test="promoterNickName != null">#{promoterNickName},</if>
            <if test="state != null">#{state},</if>
            <if test="province != null">#{province},</if>
            <if test="commissionRequirement != null">#{commissionRequirement},</if>
            <if test="categoryRequirement != null">#{categoryRequirement},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="totalSale != null">#{totalSale},</if>
            <if test="fansNumber != null">#{fansNumber},</if>
            <if test="promoterUrl != null">#{promoterUrl},</if>
            <if test="phone != null">#{phone},</if>
            <if test="consignee != null">#{consignee},</if>
            <if test="promoterAddress != null">#{promoterAddress},</if>
        </trim>
    </insert>

    <insert id="addFollowUpRecords">
        insert into kuaishou_promoter_follow_up_record
        (promoter_id,
         user_id,
         txt)
        values (#{result.promoterId},
                #{result.userId},
                #{result.txt})
    </insert>

    <update id="updateFollowUpRecords">
        update kuaishou_promoter_follow_up_record
        Set txt =#{result.txt}
        where id = #{result.id}
    </update>

    <update id="updateKuaishouPromoter" parameterType="com.ruixuan.isc.entity.KuaishouPromoter">
        update kuaishou_promoter
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null">user_id = #{userId},</if>
            <if test="mediaId != null">media_id = #{mediaId},</if>
            <if test="userName != null">user_name = #{userName},</if>
            <if test="promoterId != null">promoter_id = #{promoterId},</if>
            <if test="promoterNickName != null">promoter_nick_name = #{promoterNickName},</if>
            <if test="totalSale != null">total_sale = #{totalSale},</if>
            <if test="fansNumber != null">fans_number = #{fansNumber},</if>
            <if test="promoterUrl != null">promoter_url = #{promoterUrl},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="consignee != null">consignee = #{consignee},</if>
            <if test="promoterAddress != null">promoter_address = #{promoterAddress},</if>
            <if test="state != null">state = #{state},</if>
            <if test="province != null">province = #{province},</if>
            <if test="commissionRequirement != null">commission_requirement = #{commissionRequirement},</if>
            <if test="categoryRequirement != null">category_requirement = #{categoryRequirement},</if>
            <if test="avgVideoSales != null">avg_video_sales = #{avgVideoSales},</if>
            <if test="videoSales != null">video_sales = #{videoSales},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="updateKuaishouPromoter3" parameterType="com.ruixuan.isc.entity.JYKuaishouPromoter">
        update jy_kuaishou_promoter
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null">user_id = #{userId},</if>
            <if test="mediaId != null">media_id = #{mediaId},</if>
            <if test="type != null">type = #{type},</if>
            <if test="userName != null">user_name = #{userName},</if>
            <if test="promoterId != null">promoter_id = #{promoterId},</if>
            <if test="promoterNickName != null">promoter_nick_name = #{promoterNickName},</if>
            <if test="totalSale != null">total_sale = #{totalSale},</if>
            <if test="fansNumber != null">fans_number = #{fansNumber},</if>
            <if test="promoterUrl != null">promoter_url = #{promoterUrl},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="consignee != null">consignee = #{consignee},</if>
            <if test="promoterAddress != null">promoter_address = #{promoterAddress},</if>
            <if test="state != null">state = #{state},</if>
            <if test="province != null">province = #{province},</if>
            <if test="commissionRequirement != null">commission_requirement = #{commissionRequirement},</if>
            <if test="categoryRequirement != null">category_requirement = #{categoryRequirement},</if>
            <if test="avgVideoSales != null">avg_video_sales = #{avgVideoSales},</if>
            <if test="videoSales != null">video_sales = #{videoSales},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="updateKuaishouPromoterByPromoterId" parameterType="com.ruixuan.isc.entity.KuaishouPromoter">
        update kuaishou_promoter
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null">user_id = #{userId},</if>
            <if test="mediaId != null">media_id = #{mediaId},</if>
            <if test="userName != null">user_name = #{userName},</if>
            <if test="promoterId != null">promoter_id = #{promoterId},</if>
            <if test="promoterNickName != null">promoter_nick_name = #{promoterNickName},</if>
            <if test="totalSale != null">total_sale = #{totalSale},</if>
            <if test="fansNumber != null">fans_number = #{fansNumber},</if>
            <if test="promoterUrl != null">promoter_url = #{promoterUrl},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="consignee != null">consignee = #{consignee},</if>
            <if test="promoterAddress != null">promoter_address = #{promoterAddress},</if>
            <if test="state != null">state = #{state},</if>
            <if test="province != null">province = #{province},</if>
            <if test="commissionRequirement != null">commission_requirement = #{commissionRequirement},</if>
            <if test="categoryRequirement != null">category_requirement = #{categoryRequirement},</if>
            <if test="avgVideoSales != null">avg_video_sales = #{avgVideoSales},</if>
            <if test="videoSales != null">video_sales = #{videoSales},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="followerIncr != null">follower_incr = #{followerIncr},</if>
            <if test="liveCount30 != null">live_count_30 = #{liveCount30},</if>
            <if test="awemeAvgDiggCount30 != null">aweme_avg_digg_count_30 = #{awemeAvgDiggCount30},</if>
            <if test="awemeDiggFollowerRation != null">aweme_digg_follower_ration = #{awemeDiggFollowerRation},</if>
            <if test="liveAverageUser30 != null">live_average_user_30 = #{liveAverageUser30},</if>
        </trim>
        where promoter_id = #{promoterId}
    </update>

    <update id="updateKuaishouPromoterByPromoterId3" parameterType="com.ruixuan.isc.entity.JYKuaishouPromoter">
        update jy_kuaishou_promoter
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null">user_id = #{userId},</if>
            <if test="type != null">`type` = #{type},</if>
            <if test="mediaId != null">media_id = #{mediaId},</if>
            <if test="userName != null">user_name = #{userName},</if>
            <if test="promoterId != null">promoter_id = #{promoterId},</if>
            <if test="promoterNickName != null">promoter_nick_name = #{promoterNickName},</if>
            <if test="totalSale != null">total_sale = #{totalSale},</if>
            <if test="fansNumber != null">fans_number = #{fansNumber},</if>
            <if test="promoterUrl != null">promoter_url = #{promoterUrl},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="consignee != null">consignee = #{consignee},</if>
            <if test="promoterAddress != null">promoter_address = #{promoterAddress},</if>
            <if test="state != null">`state` = #{state},</if>
            <if test="province != null">province = #{province},</if>
            <if test="commissionRequirement != null">commission_requirement = #{commissionRequirement},</if>
            <if test="categoryRequirement != null">category_requirement = #{categoryRequirement},</if>
            <if test="avgVideoSales != null">avg_video_sales = #{avgVideoSales},</if>
            <if test="videoSales != null">video_sales = #{videoSales},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="followerIncr != null">follower_incr = #{followerIncr},</if>
            <if test="liveCount30 != null">live_count_30 = #{liveCount30},</if>
            <if test="awemeAvgDiggCount30 != null">aweme_avg_digg_count_30 = #{awemeAvgDiggCount30},</if>
            <if test="awemeDiggFollowerRation != null">aweme_digg_follower_ration = #{awemeDiggFollowerRation},</if>
            <if test="liveAverageUser30 != null">live_average_user_30 = #{liveAverageUser30},</if>
        </trim>
        where promoter_id = #{promoterId}
    </update>

    <delete id="deleteKuaishouPromoterById" parameterType="Long">
        delete
        from kuaishou_promoter
        where id = #{id}
    </delete>

    <delete id="deleteKuaishouPromoterById3" parameterType="Long">
        delete
        from jy_kuaishou_promoter
        where id = #{id}
    </delete>

    <delete id="deleteKuaishouPromoterByIds" parameterType="String">
        delete from kuaishou_promoter where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <delete id="deletePromoterLabel" parameterType="String">
        delete
        from kuaishou_promoter_label_rel
        where promoter_id = #{promoterId}
          AND user_id = #{userId}
    </delete>

    <insert id="insertPromoterLabels">
        insert into kuaishou_promoter_label_rel
        (promoter_id,user_id, label_id)
        values
        <foreach collection="list" item="ent" separator=",">
            (#{ent.promoterId},#{ent.userId}, #{ent.labelId})
        </foreach>
    </insert>
    <select id="getNearlyMonthGmv" resultType="com.alibaba.fastjson.JSONObject">
        SELECT SUM(order_amount) as 'orderAmount', CONCAT(IFNULL(ROUND((SELECT COUNT(1)
                                                                        FROM kuaishou_item_collect_samples
                                                                        WHERE collect_sample_status = 7
                                                                          AND promoter_id = #{promoterId})
            / (SELECT COUNT(1)
               FROM kuaishou_item_collect_samples
               WHERE collect_sample_status!=1 AND collect_sample_status!=2 AND promoter_id = #{promoterId})*100,2), 0),
                                                          '%') as 'operationCompletionRate'
        FROM kuaishou_supply_chain
        WHERE promoter_id = #{promoterId}
          AND FROM_UNIXTIME(order_create_time / 1000, '%Y-%m-%d') >= #{start}
          AND FROM_UNIXTIME(order_create_time / 1000, '%Y-%m-%d') &lt;= #{end}
    </select>


    <insert id="insertKuaishouPromoterRecord" parameterType="com.alibaba.fastjson.JSONObject" useGeneratedKeys="true"
            keyProperty="id">
        insert into kuaishou_promoter_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="promoterId != null">promoter_id,</if>
            <if test="userId != null">user_id,</if>
            <if test="operatorId != null">operator_id,</if>
            <if test="roleName != null">role_name,</if>
            <if test="roleKey != null">role_key,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="promoterId != null">#{promoterId},</if>
            <if test="userId != null">#{userId},</if>
            <if test="operatorId != null">#{operatorId},</if>
            <if test="roleName != null">#{roleName},</if>
            <if test="roleKey != null">#{roleKey},</if>
        </trim>
    </insert>

    <select id="getFollowUpRecordsByUserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT id,
               txt
        FROM kuaishou_promoter_follow_up_record
        Where promoter_id = #{promoterId}
          AND user_id = #{userId}
        ORDER BY update_time DESC LIMIT 1
    </select>

    <select id="getFollowUpRecordListByPromoterId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.id,
        txt,
        t2.nick_name as 'userName',
        t1.update_time as 'updateTime'
        FROM kuaishou_promoter_follow_up_record t1
        LEFT JOIN sys_user t2 ON t1.user_id = t2.user_id
        Where t1.promoter_id = #{promoterId}
        <if test="id != null">
            AND t1.id != #{id}
        </if>
        ORDER BY t1.update_time DESC
    </select>

    <select id="getPromoterLabel" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t2.id as 'labelId', t2.label_colour as 'labelColour', t2.label_name as 'labelName'
        FROM kuaishou_promoter_label_rel t1
                 LEFT JOIN kuaishou_label t2 ON t1.label_id = t2.id
        WHERE t1.promoter_id = #{promoterId}
          AND user_id = #{userId}
        order by t2.id
    </select>

    <select id="getOnlyPromoterInfoByUserId" resultType="com.ruixuan.isc.entity.KuaishouPromoter">
        <include refid="selectKuaishouPromoterVo"/>
        where promoter_id = #{promoterId}
        <if test="userId != null ">
            AND user_id = #{userId}
        </if>
        limit 1
    </select>

    <select id="getUserPromotersCount" resultType="java.lang.Integer">
        select promoters_count
        from user_promoters_count
        where user_id = #{userId}
          AND media_id = #{mediaId}
    </select>

    <select id="getBoundCount" resultType="java.lang.Integer">
        SELECT COUNT(1)
        FROM `kuaishou_promoter`
        WHERE user_id = #{userId}
    </select>
    <select id="getRuleName" resultType="java.lang.String" parameterType="java.lang.Long">
        select rule_name
        from kuaishou_settlement_rules
        where id = #{ruleId}
    </select>


    <insert id="addPromotersCount">
        replace
        into user_promoters_count
        (user_id, promoters_count,media_id)
        values
        (
        #{userId},
        #{promotersCount},
        #{mediaId}
        )
    </insert>
    <insert id="addRule">
        replace
        into
         promoter_bind_rule(promoter_id,rule_id,rule_name,create_time,update_time)
          values
        (
        #{promoterId},
        #{ruleId},
        #{ruleName},
        now
        (
        ),
        now
        (
        )
        )

    </insert>
</mapper>