<?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.hzs.member.bonus.mapper.CuMemberBonusPushMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="CuMemberBonusPush" type="com.hzs.common.domain.member.ext.CuMemberBonusPushExt">
        <id column="PK_ID" property="pkId" />
        <result column="DEL_FLAG" property="delFlag" />
        <result column="PK_COUNTRY" property="pkCountry" />
        <result column="PK_CREATOR" property="pkCreator" />
        <result column="CREATION_TIME" property="creationTime" />
        <result column="PK_MODIFIED" property="pkModified" />
        <result column="MODIFIED_TIME" property="modifiedTime" />
        <result column="PK_BONUS" property="pkBonus" />
        <result column="PK_ORDER" property="pkOrder" />
        <result column="PK_BONUS_ITEMS" property="pkBonusItems" />
        <result column="INCOME_STATUS" property="incomeStatus" />
        <result column="CAL_TYPE" property="calType" />
        <result column="CAL_ACHIEVE" property="calAchieve" />
        <result column="CAL_VALUE" property="calValue" />
        <result column="PRETAX_INCOME" property="pretaxIncome" />
        <result column="INCOME_TAX" property="incomeTax" />
        <result column="REAL_INCOME" property="realIncome" />
        <result column="ROUND" property="round" />
        <result column="SECOND" property="second" />
        <result column="REMARK" property="remark" />
        <result column="INCOME_RATIO" property="incomeRatio" />
        <result column="INCOME_DIAL_RATIO" property="incomeDialRatio" />
        <result column="ORDER_DIAL_RATIO" property="orderDialRatio" />
        <result column="ORDER_CODE" property="orderCode" />
        <result column="ORDER_TYPE" property="orderType" />
        <result column="PK_SOURCE_COUNTRY" property="pkSourceCountry" />
        <result column="SOURCE_COUNTRY_NAME" property="sourceCountryName" />
        <result column="SOURCE_MEMBER_CODE" property="sourceMemberCode" />
        <result column="SOURCE_MEMBER_NAME" property="sourceMemberName" />
        <result column="PK_RATE" property="pkRate" />
        <result column="ORDER_AMOUNT" property="orderAmount" />
        <result column="ORDER_ACHIEVE" property="orderAchieve" />
        <result column="MEMBER_CODE" property="memberCode"/>
        <result column="MEMBER_NAME" property="memberName"/>
        <result column="PK_SETTLE_GRADE" property="pkSettleGrade"/>
        <result column="PK_AWARDS" property="pkAwards"/>
        <result column="VERTEX_NAME" property="vertexName" />
        <result column="TEAM_NAME" property="teamName" />
        <result column="TEAM_CODE" property="teamCode" />
        <result column="SETTLE_DATE" property="settleDate" />
        <result column="SETTLE_COUNTRY" property="settleCountry" />
        <result column="PERIOD" property="period" />
        <result column="OUT_EXCHANGE_RATE" property="outExchangeRate" />
        <result column="PK_REGISTER_GRADE" property="pkRegisterGrade" />
        <result column="ORDER_TYPE" property="orderType" />
        <result column="memberPayTime" property="memberPayTime" />
        <result column="PK_VERTEX" property="pkVertex" />
        <result column="SERVICE_CODE" property="serviceCode" />
        <result column="NICK_NAME" property="nickName" />
    </resultMap>

    <select id="tripleCycleList" resultMap="CuMemberBonusPush" >
        select ce.member_name ,ce.MEMBER_CODE,so.ORDER_AMOUNT ,so.ORDER_ACHIEVE, ce.PK_REGISTER_GRADE,so.ORDER_TYPE,cp.ROUND + 1  ROUND  ,cp.SECOND SECOND,so.PAY_TIME memberPayTime,so.PK_CREATOR,so.PK_COUNTRY,
        ce.pk_vertex,
        ce.service_code,
        ce.nick_name
        from cu_member_bonus_push cp
        left join sa_order so  on so.pk_id =  cp.PK_ORDER
        left join CU_MEMBER ce on ce.PK_ID = so.PK_MEMBER
        where
         so.order_type in (1, 2,24,25)
        and so.order_status = 1 and cp.pk_bonus_items=3 and cal_type=1
        and so.pk_reference = #{pkMember}

        <if test="memberInfo != null and memberInfo != ''">
            and (ce.member_code=#{memberInfo} or ce.member_name=#{memberInfo})
        </if>
        <if test="memberCode != null and memberCode != ''">
            and (ce.member_code = #{memberCode} or ce.SERVICE_CODE = #{memberCode})
        </if>
        <if test="memberName != null and memberName != ''">
            and (ce.member_name = #{memberName} or ce.NICK_NAME = #{memberName})
        </if>
        <if test="startCreationTime != null">
            and so.PAY_TIME >=#{startCreationTime}
        </if>
        <if test="endCreationTime != null">
            and so.PAY_TIME &lt;#{endCreationTime}
        </if>
        <if test="pkRegisterGrade != null">
            and ce.PK_REGISTER_GRADE  = #{pkRegisterGrade}
        </if>
        order by round,second

<!--        select ce.member_name ,ce.MEMBER_CODE,so.ORDER_AMOUNT ,so.ORDER_ACHIEVE, ce.PK_REGISTER_GRADE,so.ORDER_TYPE,(case cp.SECOND when 0 then 0 else cp.ROUND+1 end)  ROUND,cp.SECOND,so.PAY_TIME memberPayTime,so.PK_CREATOR,so.PK_COUNTRY   from-->
<!--        sa_order so-->
<!--        left join CU_MEMBER_BONUS_PUSH  cp-->
<!--        on so.pk_id =  cp.PK_ORDER-->
<!--        left join CU_MEMBER ce on ce.PK_ID = so.PK_MEMBER-->
<!--        where so.del_flag=0 and so.ORDER_TYPE  in  (1, 2, 24,25)-->
<!--        &lt;!&ndash; 去除历史开网迁移数据 &ndash;&gt;-->
<!--        and so.PK_CREATOR != 1-->
<!--        and (so.pk_member  in (select cm.pk_id from CU_MEMBER cm where cm.pk_parent =  #{pkMember} and cm.del_flag = 0  ))-->
<!--        <if test="memberInfo != null and memberInfo != ''">-->
<!--            and (ce.member_code=#{memberInfo} or ce.member_name=#{memberInfo})-->
<!--        </if>-->
<!--        <if test="memberCode != null and memberCode != ''">-->
<!--            and ce.MEMBER_CODE = #{memberCode}-->
<!--        </if>-->
<!--        <if test="memberName != null and memberName != ''">-->
<!--            and ce.MEMBER_NAME = #{memberName}-->
<!--        </if>-->
<!--        <if test="startCreationTime != null">-->
<!--            and so.PAY_TIME >=#{startCreationTime}-->
<!--        </if>-->
<!--        <if test="endCreationTime != null">-->
<!--            and so.PAY_TIME &lt;#{endCreationTime}-->
<!--        </if>-->
<!--        <if test="pkRegisterGrade != null">-->
<!--            and ce.PK_REGISTER_GRADE  = #{pkRegisterGrade}-->
<!--        </if>-->
<!--        order by nvl(cp.ROUND, 0) desc, nvl(cp.SECOND, 0) desc-->
    </select>


    <select id="nextRound" resultMap="CuMemberBonusPush" >
        select bb.ROUND,bb.SECOND from (
                             select bp.* from CU_MEMBER_BONUS_PUSH bp
                                                  left join CU_MEMBER_BONUS mb on mb.PK_ID = bp.PK_BONUS
                             where PK_MEMBER = #{pkMember}    order by bp.ROUND desc,bp.SECOND desc) bb where rownum = 1
    </select>
</mapper>
