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

    <resultMap type="com.ruixuan.isc.entity.KuaishouRuleSettlementData" id="KuaishouRuleSettlementDataResult">
        <result property="id" column="id"/>
        <result property="ruleId" column="rule_id"/>
        <result property="ruleType" column="rule_type"/>
        <result property="ruleName" column="rule_name"/>
        <result property="sonRuleId" column="son_rule_id"/>
        <result property="relateId" column="relate_id"/>
        <result property="itemIds" column="item_ids"/>
        <result property="promoterId" column="promoter_id"/>
        <result property="promoterName" column="promoter_name"/>
        <result property="itemCreateId" column="item_create_id"/>
        <result property="itemCreateName" column="item_create_name"/>
        <result property="promoterCreateId" column="promoter_create_id"/>
        <result property="promoterCreateName" column="promoter_create_name"/>
        <result property="orderNum" column="order_num"/>
        <result property="validOrderNum" column="valid_order_num"/>
        <result property="yesterdayOrderNum" column="yesterday_order_num"/>
        <result property="yesterdayValidOrderNum" column="yesterday_valid_order_num"/>
        <result property="statDate" column="stat_date"/>
        <result property="anchorAmount" column="anchor_amount"/>
        <result property="referenceAmount" column="reference_amount"/>
        <result property="settlementStatus" column="settlement_status"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <sql id="selectKuaishouRuleSettlementDataVo">
        select id, rule_id, rule_type,rule_name, son_rule_id, relate_id, item_ids, promoter_id, promoter_name, item_create_id, item_create_name, promoter_create_id, promoter_create_name, order_num, valid_order_num, yesterday_order_num, yesterday_valid_order_num, stat_date, anchor_amount, reference_amount, settlement_status, create_time, update_time from kuaishou_rule_settlement_data
    </sql>

    <select id="selectKuaishouRuleSettlementDataList" parameterType="com.ruixuan.isc.entity.KuaishouRuleSettlementData"
            resultMap="KuaishouRuleSettlementDataResult">
        <include refid="selectKuaishouRuleSettlementDataVo"/>
        <where>
            <if test="ruleId != null ">and rule_id = #{ruleId}</if>
            <if test="ruleName != null ">and rule_name like concat('%', #{ruleName}, '%')</if>
            <if test="sonRuleId != null ">and son_rule_id = #{sonRuleId}</if>
            <if test="relateId != null ">and relate_id = #{relateId}</if>
            <if test="itemIds != null  and itemIds != ''">and item_ids = #{itemIds}</if>
            <if test="promoterId != null ">and promoter_id = #{promoterId}</if>
            <if test="promoterName != null  and promoterName != ''">and promoter_name like concat('%', #{promoterName},
                '%')
            </if>
            <if test="itemCreateId != null ">and item_create_id = #{itemCreateId}</if>
            <if test="itemCreateName != null  and itemCreateName != ''">and item_create_name like concat('%',
                #{itemCreateName}, '%')
            </if>
            <if test="promoterCreateId != null ">and promoter_create_id = #{promoterCreateId}</if>
            <if test="promoterCreateName != null  and promoterCreateName != ''">and promoter_create_name like
                concat('%', #{promoterCreateName}, '%')
            </if>
            <if test="orderNum != null ">and order_num = #{orderNum}</if>
            <if test="validOrderNum != null ">and valid_order_num = #{validOrderNum}</if>
            <if test="yesterdayOrderNum != null ">and yesterday_order_num = #{yesterdayOrderNum}</if>
            <if test="yesterdayValidOrderNum != null ">and yesterday_valid_order_num = #{yesterdayValidOrderNum}</if>
            <if test="statDate != null ">and stat_date = #{statDate}</if>
            <if test="anchorAmount != null ">and anchor_amount = #{anchorAmount}</if>
            <if test="referenceAmount != null ">and reference_amount = #{referenceAmount}</if>
            <if test="settlementStatus != null ">and settlement_status = #{settlementStatus}</if>
        </where>
    </select>

    <select id="selectKuaishouRuleSettlementDataById" parameterType="java.lang.Long"
            resultMap="KuaishouRuleSettlementDataResult">
        <include refid="selectKuaishouRuleSettlementDataVo"/>
        where id = #{id}
    </select>


    <insert id="insertKuaishouRuleSettlementData" parameterType="com.ruixuan.isc.entity.KuaishouRuleSettlementData">
        insert into kuaishou_rule_settlement_data
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="ruleId != null">rule_id,</if>
            <if test="ruleType != null">rule_type,</if>
            <if test="ruleName != null">rule_name,</if>
            <if test="sonRuleId != null">son_rule_id,</if>
            <if test="relateId != null">relate_id,</if>
            <if test="itemIds != null">item_ids,</if>
            <if test="promoterId != null">promoter_id,</if>
            <if test="promoterName != null">promoter_name,</if>
            <if test="itemCreateId != null">item_create_id,</if>
            <if test="itemCreateName != null">item_create_name,</if>
            <if test="promoterCreateId != null">promoter_create_id,</if>
            <if test="promoterCreateName != null">promoter_create_name,</if>
            <if test="orderNum != null">order_num,</if>
            <if test="validOrderNum != null">valid_order_num,</if>
            <if test="yesterdayOrderNum != null">yesterday_order_num,</if>
            <if test="yesterdayValidOrderNum != null">yesterday_valid_order_num,</if>
            <if test="statDate != null">stat_date,</if>
            <if test="anchorAmount != null">anchor_amount,</if>
            <if test="referenceAmount != null">reference_amount,</if>
            <if test="settlementStatus != null">settlement_status,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="ruleId != null">#{ruleId},</if>
            <if test="ruleType != null">#{ruleType},</if>
            <if test="ruleName != null">#{ruleName},</if>
            <if test="sonRuleId != null">#{sonRuleId},</if>
            <if test="relateId != null">#{relateId},</if>
            <if test="itemIds != null">#{itemIds},</if>
            <if test="promoterId != null">#{promoterId},</if>
            <if test="promoterName != null">#{promoterName},</if>
            <if test="itemCreateId != null">#{itemCreateId},</if>
            <if test="itemCreateName != null">#{itemCreateName},</if>
            <if test="promoterCreateId != null">#{promoterCreateId},</if>
            <if test="promoterCreateName != null">#{promoterCreateName},</if>
            <if test="orderNum != null">#{orderNum},</if>
            <if test="validOrderNum != null">#{validOrderNum},</if>
            <if test="yesterdayOrderNum != null">#{yesterdayOrderNum},</if>
            <if test="yesterdayValidOrderNum != null">#{yesterdayValidOrderNum},</if>
            <if test="statDate != null">#{statDate},</if>
            <if test="anchorAmount != null">#{anchorAmount},</if>
            <if test="referenceAmount != null">#{referenceAmount},</if>
            <if test="settlementStatus != null">#{settlementStatus},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
        </trim>
    </insert>


    <update id="updateKuaishouRuleSettlementData" parameterType="com.ruixuan.isc.entity.KuaishouRuleSettlementData">
        update kuaishou_rule_settlement_data
        <trim prefix="SET" suffixOverrides=",">
            <if test="ruleId != null">rule_id = #{ruleId},</if>
            <if test="ruleType != null">rule_type = #{ruleType},</if>
            <if test="ruleName != null">rule_name = #{ruleName},</if>
            <if test="sonRuleId != null">son_rule_id = #{sonRuleId},</if>
            <if test="relateId != null">relate_id = #{relateId},</if>
            <if test="itemIds != null">item_ids = #{itemIds},</if>
            <if test="promoterId != null">promoter_id = #{promoterId},</if>
            <if test="promoterName != null">promoter_name = #{promoterName},</if>
            <if test="itemCreateId != null">item_create_id = #{itemCreateId},</if>
            <if test="itemCreateName != null">item_create_name = #{itemCreateName},</if>
            <if test="promoterCreateId != null">promoter_create_id = #{promoterCreateId},</if>
            <if test="promoterCreateName != null">promoter_create_name = #{promoterCreateName},</if>
            <if test="orderNum != null">order_num = #{orderNum},</if>
            <if test="validOrderNum != null">valid_order_num = #{validOrderNum},</if>
            <if test="yesterdayOrderNum != null">yesterday_order_num = #{yesterdayOrderNum},</if>
            <if test="yesterdayValidOrderNum != null">yesterday_valid_order_num = #{yesterdayValidOrderNum},</if>
            <if test="statDate != null">stat_date = #{statDate},</if>
            <if test="anchorAmount != null">anchor_amount = #{anchorAmount},</if>
            <if test="referenceAmount != null">reference_amount = #{referenceAmount},</if>
            <if test="settlementStatus != null">settlement_status = #{settlementStatus},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where id = #{id}
    </update>
    <update id="updatePromoterStatus">
        update kuaishou_rule_settlement_data set settlement_status = #{status}
        where promoter_id = #{promoterId}
        and stat_date = #{statDate}
        and settlement_status = 2
    </update>

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

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


    <select id="data" resultType="com.alibaba.fastjson.JSONObject" parameterType="java.lang.Long">

      select  *  from (
      select t1.relate_id                      as 'relateId',
       1                        as 'ruleId',
       group_concat(t1.item_id)          as 'itemIds',
       t1.promoter_id                    as 'promoterId',
       t1.promoter_name                  as 'promoterName',
       t1.item_create_id                 as 'itemCreateId',
       t1.item_create_name               as 'itemCreateName',
       t1.promoter_create_id             as 'promoterCreateId',
       t1.promoter_create_name           as 'promoterCreateName',
       ifnull(sum(t1.order_num),0)                 as 'orderNum',
       ifnull(sum(t1.valid_order_num),0 )         as 'validOrderNum',
       ifnull(sum(t1.yesterday_order_num),0 )      as 'yesterdayOrderNum',
       ifnull(sum(t1.yesterday_valid_order_num),0) as 'yesterdayValidOrderNum',
       t1.stat_date                      as 'statDate'
       from kuaishou_rule_order_data t1
         left join kuaishou_item_list t2 on t1.item_id = t2.item_id
    where t1.stat_date = #{statDate}
    and t2.rule_id is   null
    and t1.relate_id is not null
    group by t1.relate_id, t1.promoter_id ) t order by t.validOrderNum
    </select>
    <select id="getRuleList" resultType="com.alibaba.fastjson.JSONObject" parameterType="java.lang.Long">
        select
        id as 'id',
        rule_id as 'ruleId',
        start_item_count as 'startItemCount',
        end_item_count as 'endItemCount',
        anchor_amount as 'anchorAmount',
        reference_amount as 'referenceAmount',
        sort_count as 'sortCount'
        from kuaishou_rule_detail where rule_id = #{ruleId}
    </select>
    <select id="lastRule" resultType="com.alibaba.fastjson.JSONObject">
        select son_rule_id  as 'sonRuleId',
        (select  sort_count  from kuaishou_rule_detail where  id = t1.son_rule_id) as 'sortCount',
         IFNULL(valid_order_num,0)  as 'validOrderNum'
        from  kuaishou_rule_settlement_data t1
        where 1 = 1
        <if test="relateId != null and relateId != ''">
         and   relate_id = #{relateId}
        </if>
        and  promoter_id = #{promoterId}
        and  rule_id = #{ruleId}
        and settlement_status != 1
        order by create_time desc limit 1
    </select>
    <select id="getTotalAmount" resultType="com.alibaba.fastjson.JSONObject">
        select
        ifnull(sum(anchor_amount),0) as 'anchorAmount',
        ifnull(sum(reference_amount),0) as 'referenceAmount'
        from kuaishou_rule_detail
        where  rule_id = #{ruleId}
        and sort_count &gt; #{lastSortCount}
        and sort_count &lt;= #{currentSortCount}
    </select>
    <select id="selectIncentiveLog" resultType="java.lang.Integer">
        select  ifnull(incentive_count,0)  from kuaishou_rule_incentive_log where relate_id = #{relateId}
        and promoter_id = #{promoterId} order by create_time desc limit 1
    </select>

    <select id="selectPromoterIncentiveLog" resultType="java.lang.Integer" parameterType="java.lang.Long">

  select  ifnull(incentive_count,0)  from kuaishou_protomer_rule_incentive_log where  promoter_id = #{promoterId} order by create_time desc limit 1
    </select>



    <insert id="replaceIncentiveLog">
        replace into kuaishou_rule_incentive_log (
        relate_id,
        promoter_id,
        rule_id,
        incentive_count,
        last_incentive_count
        ) values (
        #{relateId},
        #{promoterId},
        #{ruleId},
        #{incentivizedCount},
        #{lastIncentivizedCount}
        )

    </insert>


    <insert id="replacePromoterIncentiveLog">
        replace into kuaishou_protomer_rule_incentive_log (
        promoter_id,
        rule_id,
        incentive_count,
        last_incentive_count
        ) values (
        #{promoterId},
        #{ruleId},
        #{incentivizedCount},
        #{lastIncentivizedCount}
        )

    </insert>



    <select id="getTotalByCountAndRuleId" resultType="com.alibaba.fastjson.JSONObject">
        select
        ifnull(round(sum(anchor_amount) * #{incentivizedCount}, 2),0)    as 'anchorAmount',
        ifnull(round(sum(reference_amount) * #{incentivizedCount}, 2) ,0) as 'referenceAmount'
        from kuaishou_rule_detail where rule_id = #{ruleId}

    </select>
    <select id="getMaxRuleCountByRuleId" resultType="java.lang.Integer" parameterType="java.lang.Long">
        select  max(sort_count)  from kuaishou_rule_detail where rule_id = #{ruleId}

    </select>
    <select id="getSettlementDate" resultType="com.alibaba.fastjson.JSONObject" parameterType="java.lang.Long">
        <!--        select t.* from (
                select
                promoter_id as 'promoterId',
                promoter_name as 'promoterName',
                sum(anchor_amount) as 'anchorAmount',
                sum(reference_amount) as 'referenceAmount'
                from kuaishou_rule_settlement_data
                where stat_date = #{statDate}
                and settlement_status != 3
                <if test="promoterId != null and promoterId != '' ">
                    and promoter_id = #{promoterId}
                </if>
                <if test="promoterName != null and promoterName != '' ">
                    AND promoter_name LIKE CONCAT('%', #{promoterName}, '%')
                </if>
                group by promoter_id) t order by t.anchorAmount desc-->

        select t.*, t2.item_title as 'itemName',
         t2.user_name as  'itemUserName',
        (select  user_name  from  kuaishou_promoter where promoter_id = t.promoterId limit 1) as 'promoterUserName'
        from (select id as 'id',
        rule_name as 'ruleName',
        item_ids as 'itemIds',
        substring_index(item_ids, ',', 1) as 'itemId',
        promoter_id as 'promoterId',
        promoter_name as 'promoterName',
        order_num as 'orderNum',
        valid_order_num as 'validOrderNum',
        last_settlement_order_num as 'lastSettlementOrderNum',
        anchor_amount as 'anchorAmount',
        reference_amount as 'referenceAmount',
        settlement_status as 'settlementStatus',
        (
        case
         when settlement_status = 1 then '未达到结算规则'
         when settlement_status = 2 then '待结算'
         when settlement_status = 3 then '已结算'
        else '-'
        end
        ) as 'settlementStatusDesc'
        from kuaishou_rule_settlement_data t1
        where t1.stat_date = #{statDate}
        and settlement_status >= 2
        and anchor_amount > 0
        <if test="promoterId != null and promoterId != '' ">
            and t1.promoter_id = #{promoterId}
        </if>
        <if test="promoterName != null and promoterName != '' ">
            AND t1.promoter_name LIKE CONCAT('%', #{promoterName}, '%')
        </if>

        ) t
        left join kuaishou_item_list t2 on t.itemId = t2.item_id
        order by t.settlementStatus asc
    </select>
    <select id="getPromoterSettlementDetail" resultType="com.alibaba.fastjson.JSONObject">
        select
        id as 'id',
        rule_id as 'ruleId',
        rule_name as 'ruleName',
        son_rule_id as 'sonRuleId',
        item_ids as 'itemIds',
        promoter_id as 'promoterId',
        promoter_name as 'promoterName',
        item_create_id as 'itemCreateId',
        item_create_name as 'itemCreateName',
        promoter_create_id as 'promoterCreateId',
        promoter_create_name as 'promoterCreateName',
        order_num as 'orderNum',
        valid_order_num as 'validOrderNum',
        last_settlement_order_num as 'lastSettlementOrderNum',
        anchor_amount as 'anchorAmount',
        reference_amount as 'referenceAmount',
        (case
        when settlement_status = 1 then '未达到结算要求'
        when settlement_status = 2 then '待结算'
        when settlement_status = 3 then '已结算'
        else ''
        end
        ) as 'settlementStatusStr',
        settlement_status as 'settlementStatus'
        from kuaishou_rule_settlement_data
        where promoter_id = #{promoterId}
        and stat_date = #{date}
        <if test="settlementStatus != null and settlementStatus != ''">
            and settlement_status = #{settlementStatus}
        </if>
        order by anchor_amount desc

    </select>
    <select id="getRuleJson" resultType="com.alibaba.fastjson.JSONObject" parameterType="java.lang.Long">
         select
        id as 'id',
        rule_id as 'ruleId',
        start_item_count as 'startItemCount',
        anchor_amount as 'anchorAmount',
        reference_amount as 'referenceAmount',
        sort_count as 'sortCount'
        from kuaishou_rule_detail where rule_id = #{ruleId}
        order by  create_time desc limit 1
    </select>
    <select id="getData" resultType="com.alibaba.fastjson.JSONObject">
  select t.*, tt.rule_type
      from (select *
      from (select t1.relate_id                                 as 'relateId',
                   (case
                        when t2.rule_id is not null
                            then t2.rule_id
                        else 1
                       end
                       )                                        as 'ruleId',
                   group_concat(t1.item_id)                     as 'itemIds',
                   t1.promoter_id                               as 'promoterId',
                   t1.promoter_name                             as 'promoterName',
                   t1.item_create_id                            as 'itemCreateId',
                   t1.item_create_name                          as 'itemCreateName',
                   t1.promoter_create_id                        as 'promoterCreateId',
                   t1.promoter_create_name                      as 'promoterCreateName',
                   ifnull(sum(t1.order_num), 0)                 as 'orderNum',
                   ifnull(sum(t1.valid_order_num), 0)           as 'validOrderNum',
                   ifnull(sum(t1.yesterday_order_num), 0)       as 'yesterdayOrderNum',
                   ifnull(sum(t1.yesterday_valid_order_num), 0) as 'yesterdayValidOrderNum',
                   t1.stat_date                                 as 'statDate',
                   round(t2.item_price / 100, 2)                as 'itemPrice'
            from ruixuan.kuaishou_rule_order_data t1
                     left join ruixuan.kuaishou_item_list t2 on t1.item_id = t2.item_id
            where t1.item_id  = 20789864366937
            and t1.promoter_id = 3495457926
            and t1.stat_date = 20230911
              and t1.relate_id is not null
            group by t1.relate_id, t1.promoter_id) t
      where t.itemPrice >= 6
      order by t.validOrderNum desc) t
         left join ruixuan.kuaishou_settlement_rules tt
                   on t.ruleId = tt.id
    </select>


    <insert id="replaceDatas">
        replace into kuaishou_rule_settlement_data(
        rule_id,
        rule_type,
        rule_name,
        son_rule_id,
        relate_id,
        item_ids,
        promoter_id,
        promoter_name,
        item_create_id,
        item_create_name,
        promoter_create_id,
        promoter_create_name,
        order_num,
        valid_order_num,
        yesterday_order_num,
        yesterday_valid_order_num,
        stat_date,
        anchor_amount,
        reference_amount,
        settlement_status,
        last_settlement_order_num
        )
        values
        <foreach collection="adds" item="add" separator=",">
            (
            #{add.ruleId},
            #{add.ruleType},
            #{add.ruleName},
            #{add.sonRuleId},
            #{add.relateId},
            #{add.itemIds},
            #{add.promoterId},
            #{add.promoterName},
            #{add.itemCreateId},
            #{add.itemCreateName},
            #{add.promoterCreateId},
            #{add.promoterCreateName},
            #{add.orderNum},
            #{add.validOrderNum},
            #{add.yesterdayOrderNum},
            #{add.yesterdayValidOrderNum},
            #{add.statDate},
            #{add.anchorAmount},
            #{add.referenceAmount},
            #{add.settlementStatus},
            #{add.lastSettlementOrderNum}
            )
        </foreach>

    </insert>



</mapper>