<?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.bonus.achieve.mapper.CuMemberAchieveMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="CuMemberAchieve" type="com.hzs.common.domain.member.achieve.ext.CuMemberAchieveExt">
        <id column="PK_ID" property="pkId"/>
        <result column="PK_COUNTRY" property="pkCountry"/>
        <result column="PK_MEMBER" property="pkMember"/>
        <result column="PK_RATE" property="pkRate"/>
        <result column="A_BALANCE" property="aBalance"/>
        <result column="B_BALANCE" property="bBalance"/>
        <result column="A_SUM_AMOUNT" property="aSumAmount"/>
        <result column="B_SUM_AMOUNT" property="bSumAmount"/>
        <result column="A_SUM_PV" property="aSumPv"/>
        <result column="B_SUM_PV" property="bSumPv"/>
        <result column="A_NEW_AMOUNT" property="aNewAmount"/>
        <result column="B_NEW_AMOUNT" property="bNewAmount"/>
        <result column="A_NEW_PV" property="aNewPv"/>
        <result column="B_NEW_PV" property="bNewPv"/>
        <result column="A_SUM_REAL_AMOUNT" property="aSumRealAmount"/>
        <result column="B_SUM_REAL_AMOUNT" property="bSumRealAmount"/>
        <result column="A_SUM_REAL_PV" property="aSumRealPv"/>
        <result column="B_SUM_REAL_PV" property="bSumRealPv"/>
        <result column="REP_A_BALANCE" property="repABalance"/>
        <result column="REP_B_BALANCE" property="repBBalance"/>
        <result column="REP_A_NEW_AMOUNT" property="repANewAmount"/>
        <result column="REP_B_NEW_AMOUNT" property="repBNewAmount"/>
        <result column="REP_A_NEW_PV" property="repANewPv"/>
        <result column="REP_B_NEW_PV" property="repBNewPv"/>
        <result column="REP_A_SUM_AMOUNT" property="repASumAmount"/>
        <result column="REP_B_SUM_AMOUNT" property="repBSumAmount"/>
        <result column="REP_A_SUM_PV" property="repASumPv"/>
        <result column="REP_B_SUM_PV" property="repBSumPv"/>
        <result column="REGISTER_AMOUNT" property="registerAmount"/>
        <result column="REGISTER_PV" property="registerPv"/>
        <result column="REGISTER_NEW_PV" property="registerNewPv"/>
        <result column="UPGRADE_AMOUNT" property="upgradeAmount"/>
        <result column="UPGRADE_PV" property="upgradePv"/>
        <result column="UPGRADE_NEW_PV" property="upgradeNewPv"/>
        <result column="REPURCHASE_AMOUNT" property="repurchaseAmount"/>
        <result column="REPURCHASE_PV" property="repurchasePv"/>
        <result column="REPURCHASE_NEW_PV" property="repurchaseNewPv"/>
        <result column="HI_FUN_AMOUNT" property="hiFunAmount"/>
        <result column="HI_FUN_PV" property="hiFunPv"/>
        <result column="HI_FUN_NEW_PV" property="hiFunNewPv"/>
        <result column="MALL_AMOUNT" property="mallAmount"/>
        <result column="MALL_PV" property="mallPv"/>
        <result column="MALL_NEW_PV" property="mallNewPv"/>
        <result column="REGISTER_Blo_NEW_PV" property="registerBloNewPv" />
        <result column="UPGRADE_Blo_NEW_PV" property="upgradeBloNewPv" />
        <result column="REPURCHASE_Blo_NEW_PV" property="repurchaseBloNewPv" />
        <result column="HI_FUN_Blo_NEW_PV" property="hiFunBloNewPv" />
        <result column="MALL_Blo_NEW_PV" property="mallBloNewPv" />
        <result column="R_A_BALANCE" property="rABalance"/>
        <result column="R_B_BALANCE" property="rBBalance"/>
        <result column="R_A_SUM_AMOUNT" property="rASumAmount"/>
        <result column="R_B_SUM_AMOUNT" property="rBSumAmount"/>
        <result column="R_A_SUM_PV" property="rASumPv"/>
        <result column="R_B_SUM_PV" property="rBSumPv"/>
        <result column="R_A_SUM_REAL_AMOUNT" property="rASumRealAmount"/>
        <result column="R_B_SUM_REAL_AMOUNT" property="rBSumRealAmount"/>
        <result column="R_A_SUM_REAL_PV" property="rASumRealPv"/>
        <result column="R_B_SUM_REAL_PV" property="rBSumRealPv"/>
        <result column="R_REP_A_BALANCE" property="rRepABalance"/>
        <result column="R_REP_B_BALANCE" property="rRepBBalance"/>
        <result column="R_REP_A_SUM_AMOUNT" property="rRepASumAmount"/>
        <result column="R_REP_B_SUM_AMOUNT" property="rRepBSumAmount"/>
        <result column="R_REP_A_SUM_PV" property="rRepASumPv"/>
        <result column="ALGEBRA" property="algebra"/>
        <result column="VERTEX_NAME" property="vertexName"/>
        <result column="TEAM_NAME" property="teamName"/>
    </resultMap>

    <sql id="CuMemberAchieveColumn">
            pk_id         NUMBER(20) primary key,
            period            number(6) not null,
            pk_member         NUMBER(20) not null,
            pk_rate           NUMBER(6) not null,
            a_balance         NUMBER(17,6) default 0 not null,
            b_balance         NUMBER(17,6) default 0 not null,
            a_sum_amount      NUMBER(17,6) default 0 not null,
            b_sum_amount      NUMBER(17,6) default 0 not null,
            a_sum_pv          NUMBER(17,6) default 0 not null,
            b_sum_pv          NUMBER(17,6) default 0 not null,
            a_new_amount      NUMBER(17,6) default 0 not null,
            b_new_amount      NUMBER(17,6) default 0 not null,
            a_new_pv          NUMBER(17,6) default 0 not null,
            b_new_pv          NUMBER(17,6) default 0 not null,
            a_sum_real_amount NUMBER(17,6) default 0 not null,
            b_sum_real_amount NUMBER(17,6) default 0 not null,
            a_sum_real_pv     NUMBER(17,6) default 0 not null,
            b_sum_real_pv     NUMBER(17,6) default 0 not null,
            rep_a_balance     NUMBER(17,6) default 0 not null,
            rep_b_balance     NUMBER(17,6) default 0 not null,
            rep_a_new_amount  NUMBER(17,6) default 0 not null,
            rep_b_new_amount  NUMBER(17,6) default 0 not null,
            rep_a_new_pv      NUMBER(17,6) default 0 not null,
            rep_b_new_pv      NUMBER(17,6) default 0 not null,
            rep_a_sum_amount  NUMBER(17,6) default 0 not null,
            rep_b_sum_amount  NUMBER(17,6) default 0 not null,
            rep_a_sum_pv      NUMBER(17,6) default 0 not null,
            rep_b_sum_pv      NUMBER(17,6) default 0 not null,
            register_amount  NUMBER(17,6) default 0 not null,
            register_pv     NUMBER(17,6) default 0 not null,
            register_new_amount     NUMBER(17,6) default 0 not null,
            register_new_pv NUMBER(17,6) default 0 not null,
            upgrade_amount  NUMBER(17,6) default 0 not null,
            upgrade_pv      NUMBER(17,6) default 0 not null,
            upgrade_new_amount       NUMBER(17,6) default 0 not null,
            upgrade_new_pv NUMBER(17,6) default 0 not null,
            repurchase_amount NUMBER(17,6) default 0 not null,
            repurchase_pv   NUMBER(17,6) default 0 not null,
            repurchase_new_amount    NUMBER(17,6) default 0 not null,
            repurchase_new_pv NUMBER(17,6) default 0 not null,
            hi_fun_amount NUMBER(17,6) default 0 not null,
            hi_fun_pv   NUMBER(17,6) default 0 not null,
            hi_fun_new_amount        NUMBER(17,6) default 0 not null,
            hi_fun_new_pv NUMBER(17,6) default 0 not null,
            mall_amount NUMBER(17,6) default 0 not null,
            mall_pv     NUMBER(17,6) default 0 not null,
            mall_new_amount          NUMBER(17,6) default 0 not null,
            mall_new_pv NUMBER(17,6) default 0 not null,
            REGISTER_Blo_NEW_PV  NUMBER(17,6) default 0 not null,
            UPGRADE_Blo_NEW_PV    NUMBER(17,6) default 0 not null,
            REPURCHASE_Blo_NEW_PV    NUMBER(17,6) default 0 not null,
            HI_FUN_Blo_NEW_PV    NUMBER(17,6) default 0 not null,
            MALL_Blo_NEW_PV    NUMBER(17,6) default 0 not null,
            r_a_balance         NUMBER(17,6) default 0 not null,
            r_b_balance         NUMBER(17,6) default 0 not null,
            r_a_sum_amount      NUMBER(17,6) default 0 not null,
            r_b_sum_amount      NUMBER(17,6) default 0 not null,
            r_a_sum_pv          NUMBER(17,6) default 0 not null,
            r_b_sum_pv          NUMBER(17,6) default 0 not null,
            r_a_sum_real_amount NUMBER(17,6) default 0 not null,
            r_b_sum_real_amount NUMBER(17,6) default 0 not null,
            r_a_sum_real_pv     NUMBER(17,6) default 0 not null,
            r_b_sum_real_pv     NUMBER(17,6) default 0 not null,
            r_rep_a_balance     NUMBER(17,6) default 0 not null,
            r_rep_b_balance     NUMBER(17,6) default 0 not null,
            r_rep_a_sum_amount  NUMBER(17,6) default 0 not null,
            r_rep_b_sum_amount  NUMBER(17,6) default 0 not null,
            r_rep_a_sum_pv      NUMBER(17,6) default 0 not null,
            r_rep_b_sum_pv      NUMBER(17,6) default 0 not null,
            pk_country        NUMBER(6) not null
    </sql>

    <update id="createCuMemberAchieveTable">
        create table ${tableName}
        (
        <include refid="CuMemberAchieveColumn"></include>
        )
    </update>

    <update id="createCuMemberMonthTable">
        create table ${tableName}
        (
            pk_member         NUMBER(20) not null,
            pk_parent         NUMBER(20) not null,
            pk_place_parent   NUMBER(20),
            place_dept        NUMBER(3),
            member_code       VARCHAR2(30) not null,
            member_name       VARCHAR2(100) not null,
            customer_type     NUMBER(2) not null,
            phone             VARCHAR2(40) not null,
            pk_grade          NUMBER(4) not null,
            pk_awards         NUMBER(4) not null,
            pk_team_code      NUMBER(20),
            pk_center_code    NUMBER(20),
            pk_vertex         NUMBER(20) default 1,
            pk_settle_country NUMBER(4) not null,
            a_sum_pv          NUMBER(17,6) default 0 not null,
            b_sum_pv          NUMBER(17,6) default 0 not null,
            rep_a_sum_pv      NUMBER(17,6) default 0 not null,
            rep_b_sum_pv      NUMBER(17,6) default 0 not null,
            register_pv       NUMBER(17,6) default 0 not null,
            upgrade_pv        NUMBER(17,6) default 0 not null,
            repurchase_pv     NUMBER(17,6) default 0 not null,
            hi_fun_pv         NUMBER(17,6) default 0 not null,
            mall_pv           NUMBER(17,6) default 0 not null
        )
    </update>

    <update id="mergeCuMemberMonth">
        merge into cu_member_month${date} a
            using(
                select a.*,b.A_SUM_PV,b.b_SUM_PV,b.REGISTER_PV,b.UPGRADE_PV,
                       b.REPURCHASE_PV,b.HI_FUN_PV,b.MALL_PV from(
                     select ct.pk_member,ct.pk_parent,ct.pk_place_parent,ct.place_dept,ct.member_code,ct.member_name,
                            ct.customer_type,ct.phone,ct.pk_grade,ct.pk_awards,ct.pk_team_code,ct.pk_center_code,
                            ct.pk_vertex,ct.pk_settle_country from cu_member_tree${date} ct where period=#{period}) a
                     inner join(
                    select pk_member,sum(A_NEW_PV+REP_A_NEW_PV) A_SUM_PV,sum(b_NEW_PV+REP_B_NEW_PV) b_SUM_PV,
                           sum(register_new_pv) REGISTER_PV,sum(upgrade_new_pv) UPGRADE_PV,sum(repurchase_new_pv) REPURCHASE_PV,
                           sum(hi_fun_new_pv) HI_FUN_PV,sum(mall_new_pv) MALL_PV from cu_member_achieve${date}
                    group by pk_member) b
                   on a.pk_member=b.pk_member
                ) b
            on (a.pk_member=b.pk_member)
            when matched then
                update set
                    a.pk_parent=b.pk_parent,a.pk_place_parent=b.pk_place_parent,a.place_dept=b.place_dept,
                    a.member_code=b.member_code,a.member_name=b.member_name,a.customer_type=b.customer_type,
                    a.phone=b.phone,a.pk_grade=b.pk_grade,a.pk_awards=b.pk_awards,a.pk_team_code=b.pk_team_code,
                    a.pk_center_code=b.pk_center_code,a.pk_vertex=b.pk_vertex,a.pk_settle_country=b.pk_settle_country,
                    a.A_SUM_PV=b.A_SUM_PV,a.b_SUM_PV=b.b_SUM_PV,
                    a.REGISTER_PV=b.REGISTER_PV,a.UPGRADE_PV=b.UPGRADE_PV,
                    a.REPURCHASE_PV =b.REPURCHASE_PV,a.HI_FUN_PV=b.HI_FUN_PV,a.MALL_PV=b.MALL_PV
            WHEN NOT MATCHED THEN
                insert(pk_member,pk_parent,pk_place_parent,place_dept,member_code,
                       member_name,customer_type,phone,pk_grade,pk_awards,pk_team_code,
                       pk_center_code,pk_vertex,pk_settle_country,a_sum_pv,b_sum_pv,
                       register_pv,upgrade_pv,repurchase_pv,
                       hi_fun_pv,mall_pv)
                    values (b.pk_member,b.pk_parent,b.pk_place_parent,b.place_dept,b.member_code,
                            b.member_name,b.customer_type,b.phone,b.pk_grade,b.pk_awards,b.pk_team_code,
                            b.pk_center_code,b.pk_vertex,b.pk_settle_country,b.a_sum_pv,b.b_sum_pv,
                            b.register_pv,b.upgrade_pv,b.repurchase_pv,
                            b.hi_fun_pv,b.mall_pv)
    </update>
    <update id="chargeBackSaOrder">
        merge into ${targetTable} a
        using (
        select pk_member,max(a.pk_rate) pk_rate,sum(a_new_amount/bc.out_exchange_rate) a_new_amount,sum(a_new_pv)
        a_new_pv,
        sum(b_new_amount/bc.out_exchange_rate) b_new_amount,sum(b_new_pv) b_new_pv,
        sum(rep_a_new_amount/bc.out_exchange_rate) rep_a_new_amount,sum(rep_b_new_amount/bc.out_exchange_rate)
        rep_b_new_amount,
        sum(rep_a_new_pv) rep_a_new_pv,sum(rep_b_new_pv) rep_b_new_pv,sum(a_new_real_amount/bc.out_exchange_rate)
        a_new_real_amount,
        sum(a_new_real_pv) a_new_real_pv,sum(b_new_real_amount/bc.out_exchange_rate)
        b_new_real_amount,sum(b_new_real_pv) b_new_real_pv
        from (
            select pk_place_parent pk_member,#{achieve.pkRate} pk_rate,
            (case when place_dept =1 then #{achieve.newAmount} else 0 end) a_new_amount,
            (case when place_dept =1 then #{achieve.newPv} else 0 end) a_new_pv,
            (case when place_dept =1 then #{achieve.repNewAmount} else 0 end) rep_a_new_amount,
            (case when place_dept =1 then #{achieve.repNewPv} else 0 end) rep_a_new_pv,
            (case when place_dept =1 then #{achieve.newRealAmount} else 0 end) a_new_real_amount,
            (case when place_dept =1 then #{achieve.newRealPv} else 0 end) a_new_real_pv,
            (case when place_dept =2 then #{achieve.newAmount} else 0 end) b_new_amount,
            (case when place_dept =2 then #{achieve.newPv} else 0 end) b_new_pv,
            (case when place_dept =2 then #{achieve.repNewAmount} else 0 end) rep_b_new_amount,
            (case when place_dept =2 then #{achieve.repNewPv} else 0 end) rep_b_new_pv,
            (case when place_dept =2 then #{achieve.newRealAmount} else 0 end) b_new_real_amount,
            (case when place_dept =2 then #{achieve.newRealPv} else 0 end) b_new_real_pv
            from(
            select pk_place_parent,place_dept from(
            select * from(select * from ${sourceTable}
            where period= #{period} )
            start with pk_member = #{achieve.pkMember}
            connect by pk_member = prior pk_place_parent
            )
            where nvl(pk_place_parent,0) !=0
            )
        ) a
        inner join bd_currency bc
        on a.pk_rate = bc.pk_id
        group by pk_member
        ) b
        on (a.pk_member = b.pk_member)
        when matched then
        update set
        a.a_sum_amount = a.a_sum_amount - b.a_new_amount,
        a.b_sum_amount = a.b_sum_amount - b.b_new_amount,
        a.a_sum_pv = a.a_sum_pv - b.a_new_pv,
        a.b_sum_pv = a.b_sum_pv - b.b_new_pv,
        a.rep_a_sum_amount = a.rep_a_sum_amount - b.rep_a_new_amount,
        a.rep_b_sum_amount = a.rep_b_sum_amount - b.rep_b_new_amount,
        a.rep_a_sum_pv = a.rep_a_sum_pv - b.rep_a_new_pv,
        a.rep_b_sum_pv = a.rep_b_sum_pv - b.rep_b_new_pv,
        a.a_sum_real_amount = a.a_sum_real_amount - b.a_new_amount - b.rep_a_new_amount,
        a.b_sum_real_amount = a.b_sum_real_amount - b.b_new_amount - b.rep_b_new_amount,
        a.a_sum_real_pv = a.a_sum_real_pv - b.a_new_pv - b.rep_a_new_pv,
        a.b_sum_real_pv = a.b_sum_real_pv - b.b_new_pv - b.rep_b_new_pv,
        a.r_a_sum_amount = a.r_a_sum_amount - b.a_new_amount,
        a.r_b_sum_amount = a.r_b_sum_amount - b.b_new_amount,
        a.r_a_sum_pv = a.r_a_sum_pv - b.a_new_pv,
        a.r_b_sum_pv = a.r_b_sum_pv - b.b_new_pv,
        a.r_rep_a_sum_amount =a.r_rep_a_sum_amount - b.rep_a_new_amount,
        a.r_rep_b_sum_amount =a.r_rep_b_sum_amount - b.rep_b_new_amount,
        a.r_rep_a_sum_pv = a.r_rep_a_sum_pv - b.rep_a_new_pv,
        a.r_rep_b_sum_pv = a.r_rep_b_sum_pv - b.rep_b_new_pv,
        a.r_a_sum_real_amount = a.r_a_sum_real_amount - b.a_new_amount - b.rep_a_new_amount,
        a.r_b_sum_real_amount = a.r_b_sum_real_amount - b.b_new_amount - b.rep_b_new_amount,
        a.r_a_sum_real_pv = a.r_a_sum_real_pv - b.a_new_pv - b.rep_a_new_pv,
        a.r_b_sum_real_pv = a.r_b_sum_real_pv - b.b_new_pv - b.rep_b_new_pv
    </update>

</mapper>
