<?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.detail.mapper.CuMemberAssessMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="CuMemberAssess" type="com.hzs.common.domain.member.ext.CuMemberAssessExt">
        <result column="DEL_FLAG" property="delFlag"/>
        <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_COUNTRY" property="pkCountry"/>
        <result column="PK_MEMBER" property="pkMember"/>
        <result column="PERIOD" property="period"/>
        <result column="SETTLE_MONTH" property="settleMonth"/>
        <result column="ASSESS_TYPE" property="assessType"/>
        <result column="PK_ORIGINAL_AWARDS" property="pkOriginalAwards"/>
        <result column="PK_AWARDS" property="pkAwards"/>
        <result column="ASSESS_TARGET" property="assessTarget"/>
        <result column="IS_EXAMINE" property="isExamine"/>
        <result column="ASSESS_STATUS" property="assessStatus"/>
        <result column="MONTH_BALANCE" property="monthBalance"/>
        <result column="MONTH_ADD" property="monthAdd"/>
        <result column="CUR_MONTH_ADD" property="curMonthAdd"/>
        <result column="MONTH_USED" property="monthUsed"/>
        <result column="MONTH_SURPLUS" property="monthSurplus"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="CuMemberAssessColumn">
        pk_member     NUMBER(20) not null,
        period        NUMBER(6) default 0 not null,
        settle_month  VARCHAR2(10),
        assess_type   NUMBER(2) not null,
        pk_awards     NUMBER(6) default 1 not null,
        assess_target NUMBER(17,6) default 0 not null,
        assess_pk_awards NUMBER(6) default 1 not null,
        is_examine    NUMBER(1) default 1 not null,
        assess_status NUMBER(2) not null,
        month_balance NUMBER(17,6) default 0 not null,
        month_add     NUMBER(17,6) default 0 not null,
        cur_month_add NUMBER(17,6) default 0 not null,
        month_used    NUMBER(17,6) default 0 not null,
        month_surplus NUMBER(17,6) default 0 not null,
        del_flag      NUMBER(1) default 0 not null,
        creation_time DATE default sysdate not null,
        modified_time DATE,
        pk_country    NUMBER(4) default 1 not null,
        pk_creator    NUMBER(20) not null,
        pk_modified   NUMBER(20),
        start_date    DATE,
        end_date      DATE
    </sql>

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

    <update id="createCuMemberTableUniqueIndex">
        create index
        ${tableName}_MEMBER
        on ${tableName}
        (
        PK_MEMBER,
        ASSESS_TYPE
        )
    </update>

    <insert id="insertRepCuMemberAssess">
        merge into ${tableName} a
            using (
                select pk_member,
                       #{period}                period,
                       #{month}                 settle_month,
                       0                        assess_type,
                       pk_awards,
                       nvl(b.purchase_check, 0) ASSESS_TARGET,
                       1                        IS_EXAMINE,
                       2                        assess_status,
                       ct.pk_settle_country     PK_COUNTRY,
                       1                        pk_creator
                from ${sourceTableName} ct
                         left join bd_awards b
                                   on ct.pk_awards = b.pk_id
                where category = 0
            ) b
            on (a.pk_member = b.pk_member and a.period = b.period and a.assess_type = b.assess_type)
            WHEN MATCHED THEN
                update set a.SETTLE_MONTH = b.SETTLE_MONTH,a.PK_AWARDS = b.PK_AWARDS,a.ASSESS_TARGET = b.ASSESS_TARGET,
                    a.IS_EXAMINE = b.IS_EXAMINE,a.ASSESS_STATUS = b.ASSESS_STATUS
            when not matched then
                insert (PK_MEMBER, period, SETTLE_MONTH, ASSESS_TYPE, PK_AWARDS, ASSESS_TARGET,
                        IS_EXAMINE, ASSESS_STATUS, PK_COUNTRY, PK_CREATOR)
                    values (b.PK_MEMBER, b.period, b.SETTLE_MONTH, b.ASSESS_TYPE, b.PK_AWARDS, b.ASSESS_TARGET,
                            b.IS_EXAMINE, b.ASSESS_STATUS, b.PK_COUNTRY, b.PK_CREATOR)

    </insert>

    <insert id="insertRepCuMemberRangeAssess">
        merge into ${tableName} a
            using (
                select pk_member,
                       #{period}                period,
                       #{month}                 settle_month,
                       0                        assess_type,
                       pk_range_awards          pk_awards,
                       nvl(b.purchase_check, 0) ASSESS_TARGET,
                       1                        IS_EXAMINE,
                       2                        assess_status,
                       ct.pk_settle_country     PK_COUNTRY,
                       1                        pk_creator
                from ${sourceTableName} ct
                         left join bd_range_awards b
                                   on ct.pk_range_awards = b.pk_id
                where category = 0
            ) b
            on (a.pk_member = b.pk_member and a.period = b.period and a.assess_type = b.assess_type)
            WHEN MATCHED THEN
                update set a.SETTLE_MONTH = b.SETTLE_MONTH,a.PK_AWARDS = b.PK_AWARDS,a.ASSESS_TARGET = b.ASSESS_TARGET,
                    a.IS_EXAMINE = b.IS_EXAMINE,a.ASSESS_STATUS = b.ASSESS_STATUS
            when not matched then
                insert (PK_MEMBER, period, SETTLE_MONTH, ASSESS_TYPE, PK_AWARDS, ASSESS_TARGET,
                        IS_EXAMINE, ASSESS_STATUS, PK_COUNTRY, PK_CREATOR)
                    values (b.PK_MEMBER, b.period, b.SETTLE_MONTH, b.ASSESS_TYPE, b.PK_AWARDS, b.ASSESS_TARGET,
                            b.IS_EXAMINE, b.ASSESS_STATUS, b.PK_COUNTRY, b.PK_CREATOR)
    </insert>

    <update id="updateAssessPeriodBeginByBeforeMonth">
        merge into ${tableName} a
            using (select pk_member, assess_type, month_surplus
                   from ${beforeTableName} cs
                            inner join cu_member cm
                                       on cs.pk_member = cm.pk_id and cm.del_flag = 0
                   where period = #{sourcePeriod}
                     and assess_type = #{assessType}
                     and cm.system_type = 0) b
            on (a.pk_member = b.pk_member and a.assess_type = b.assess_type)
            when matched then
                update set a.month_balance = b.month_surplus where a.del_flag= 0 and a.period= #{period}
    </update>
    <update id="updateAssessPeriodAddBySaOrder">
        merge into ${tableName} a
        using (select pk_member,#{period} period,#{assessType} assess_type,sum(order_achieve) order_achieve from(
        select pk_member,(case when #{assessType} = 0 then order_achieve else order_ass_achieve end) order_achieve from sa_order
        where del_flag = 0 and order_status = 1 and order_type in
        <foreach collection="orderTypeList" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
        and pay_time >= #{startDate, jdbcType=DATE} and pay_time &lt; #{endDate, jdbcType=DATE}
        )
        group by pk_member
        ) b
        on (a.pk_member = b.pk_member and a.period= b.period and a.assess_type= b.assess_type)
        when matched then
        update set a.MONTH_ADD=b.order_achieve
    </update>

    <update id="updateAssessPeriodExemptBalanceByUsed">
        update ${tableName}
        set month_surplus = month_balance + month_add
        where del_flag = 0
          and period = #{period}
    </update>

    <update id="updateAssessPeriodCheckBalanceByUsed">
        update ${tableName}
        set assess_status = 0,
            month_used    = assess_target,
            month_surplus = month_balance + month_add - assess_target
        where del_flag = 0
          and assess_status = 2
          and period = #{period}
          and assess_target &lt;= month_balance + month_add
    </update>

    <update id="updateAssessCuMemberToRepExempt">
        merge into ${tableName} a
            using (
                select distinct pk_member, #{period} period, 0 assess_type
                from (
                         select pk_id pk_member
                         from cu_member
                         where del_flag = 0
                           and category = 0
                           and pay_status = 1
                           and pay_time >= #{startDate, jdbcType=DATE}
                           and pay_time &lt; #{endDate, jdbcType=DATE}
                         union
                         select pk_member
                         from cu_member_awards
                         where del_flag = 0
                           and purchase_status = 0
                           and period >= #{startPeriod}
                           and period &lt; #{endPeriod}
                         union
                         select pk_member
                         from ${settleTableName} ct
                                  inner join bd_awards ba
                                             on ct.pk_awards = ba.pk_id
                         where ct.purchase_status = 0
                           and ba.purchase_check > 0)
            ) b
            on (a.pk_member = b.pk_member and a.period = b.period and a.assess_type = b.assess_type)
            when matched then
                update set a.is_examine = 0
    </update>

    <update id="updateCuMemberSettleToRepExempt">
        merge into ${settleTableName} a
        using ( select distinct pk_id pk_member from cu_member where del_flag =0 and category =0
        and pay_status = 1
        and pay_time >= #{startDate, jdbcType=DATE} and pay_time &lt; #{endDate, jdbcType=DATE}
        <!--union
        select distinct pk_member from cu_member_awards
        where del_flag=0 and purchase_status= 0 and
            period >= #{startPeriod} and period &lt; #{endPeriod}-->
        ) b
        on (a.pk_member = b.pk_member)
        when matched then
        update set a.purchase_status=0
    </update>

    <delete id="deleteCuMemberAssessByPeriod">
        delete
        from ${tableName}
        where period = #{period}
          and del_flag = 0
    </delete>

    <select id="queryAssessPeriodByRevokeOrder" resultMap="CuMemberAssess">
        select sa.pk_id,sa.pk_member,sa.assess_type,sa.assess_target,sa.is_examine,sa.assess_status,
        sa.month_balance,sa.month_add,sa.month_used,sa.month_surplus,
        st.pk_awards,sa.pk_awards pk_original_awards,sa.pk_country from ${tableName} sa
        inner join ${settleTableName} st
        on sa.pk_member=st.pk_member
        where sa.del_flag= 0 and sa.is_examine = 0 and sa.period = #{period}
        and sa.pk_member in
        <foreach collection="saOrderList" item="item" open="(" close=")" separator=",">
            #{item.pkMember}
        </foreach>
    </select>

    <select id="queryCuMemberAssessByMember" resultMap="CuMemberAssess">
        select pk_member, assess_status from ${tableName}
        where period= #{period} and assess_type = #{assessType} and assess_status in (0, 1)
        and pk_member in
        <foreach collection="cuMemberSettleExtList" item="item" open="(" close=")" separator=",">
            #{item.pkMember}
        </foreach>
    </select>

    <select id="queryCuMemberShareAssess" resultType="com.hzs.common.domain.member.detail.CuMemberAssess">
        select pk_member, PERIOD, PK_AWARDS, assess_status, pk_country
        from ${tableName}
        where period = #{period}
          and assess_type = 1
          and assess_status in (0, 1)
          and pk_country = 1
        union
        select ca.pk_member, ca.PERIOD, ca.PK_AWARDS, ca.assess_status, ca.pk_country
        from ${tableName} ca
                 inner join bd_awards ba
                            on ca.PK_AWARDS = ba.pk_id
        where period = #{period}
          and assess_type = 0
          and assess_status in (0, 1)
          and ca.pk_country > 1
          and ba.awards_value > 0
    </select>


    <!--   会员-考核明细列表-->
    <select id="selectAssessList"
            resultType="com.hzs.bonus.detail.vo.CuMemberAssessVO">

        select * from (
        SELECT
        a.PK_ASSESS pkAssess,
        a.PK_MEMBER pkMember,
        a.TRADE_TIME tradeTime,
        a.PK_ORDER pkOrder,
        a.CHANGE_TYPE changeType,
        a.TRADE_ACHIEVE tradeAchieve,
        0 orderAssAchieve,
        0 orderAchieve,
        '' orderCode
        FROM
        CU_MEMBER_ASSESS_DETAIL a
        LEFT JOIN ${tableName} b on b.PK_ID=a.PK_ASSESS
        WHERE
        a.DEL_FLAG = 0
        <if test="pkMember != null ">
            AND a.PK_MEMBER = #{pkMember}
        </if>
        <if test="pkCountry != null ">
            AND a.PK_COUNTRY = #{pkCountry}
        </if>

        <if test="changeType != null ">
            AND a.CHANGE_TYPE = #{changeType}
        </if>
        <if test="assessType != null ">
            AND b.ASSESS_TYPE = #{assessType}
        </if>
        <if test="assessStatus != null ">
            AND b.ASSESS_STATUS = #{assessStatus}
        </if>

        <if test="changeType ==null and assessStatus==null ">
            UNION ALL
            select
            null pkAssess,
            PK_MEMBER pkMember,
            PAY_TIME tradeTime,
            null pkOrder,
            null changeType,
            null tradeAchieve,
            ORDER_ASS_ACHIEVE orderAssAchieve,
            ORDER_ACHIEVE orderAchieve,
            ORDER_CODE orderCode
            from SA_ORDER
            WHERE
            DEL_FLAG = 0
            <if test="pkMember != null ">
                AND PK_MEMBER = #{pkMember}
            </if>
            <if test="orderTypeList != null ">
                and ORDER_TYPE in
                <foreach collection="orderTypeList" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="assessType == 0 ">
                AND order_achieve > 0
            </if>
            <if test="assessType == 1 ">
                AND ORDER_ASS_ACHIEVE > 0
            </if>

            <if test="pkCountry != null ">
                AND PK_COUNTRY = #{pkCountry}
            </if>

            <if test="startDate != null">
                and PAY_TIME &gt;= #{startDate, jdbcType=DATE}
            </if>
            <if test="endDate != null">
                and PAY_TIME &lt; #{endDate, jdbcType=DATE}
            </if>
        </if>
        )
        order by tradeTime desc
    </select>

    <!--查询会员月度考核-->
    <select id="getMemberAssessMonthList"
            resultType="com.hzs.bonus.detail.vo.CuMemberAssessVO">
        SELECT
        a.pk_id pkId,
        a.SETTLE_MONTH settleMonth,
        a.ASSESS_STATUS assessStatus,
        a.MONTH_SURPLUS monthSurplus,
        a.CREATION_TIME creationTime
        FROM
        ${tableName} a
        WHERE
        a.DEL_FLAG = 0
        <if test="period != null ">
            AND a.period=#{period}
        </if>
        <if test="pkMember != null ">
            AND a.PK_MEMBER = #{pkMember}
        </if>
        <if test="pkCountry != null ">
            AND a.PK_COUNTRY = #{pkCountry}
        </if>
        <if test="assessType != null ">
            AND a.ASSESS_TYPE = #{assessType}
        </if>
    </select>

    <!--根据考核类型查询会员订单信息-->
    <select id="getOrderByAssessList"
            resultType="com.hzs.bonus.detail.vo.CuMemberAssessVO">
        select PK_MEMBER pkMember,
        PAY_TIME tradeTime,
        ORDER_ASS_ACHIEVE orderAssAchieve,
        ORDER_ACHIEVE orderAchieve,
        ORDER_TYPE orderType,
        ORDER_CODE orderCode
        from SA_ORDER
        WHERE
        DEL_FLAG = 0
        <if test="pkMember != null ">
            AND PK_MEMBER = #{pkMember}
        </if>
        <if test="pkCountry != null ">
            AND PK_COUNTRY = #{pkCountry}
        </if>
        <if test="orderType != null ">
            AND ORDER_TYPE = #{orderType}
        </if>
        <if test="startDate != null">
            and PAY_TIME &gt;= #{startDate, jdbcType=DATE}
        </if>
        <if test="endDate != null">
            and PAY_TIME &lt; #{endDate, jdbcType=DATE}
        </if>
        order by PAY_TIME desc
    </select>

    <!--查询会员订单考核积分-->
    <select id="getOrderAssessList" parameterType="com.hzs.bonus.detail.vo.CuMemberAssessVO"
            resultType="com.hzs.bonus.detail.vo.CuMemberAssessVO">
        SELECT
        sum (ORDER_ASS_ACHIEVE) orderAssAchieve,
        sum (ORDER_ACHIEVE) orderAchieve
        FROM
        SA_ORDER a
        WHERE
        a.DEL_FLAG = 0
        <if test="orderTypeList != null ">
            AND a.order_type in
            <foreach collection="orderTypeList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="pkMember != null ">
            AND a.PK_MEMBER = #{pkMember}
        </if>
        <if test="pkCountry != null ">
            AND a.PK_COUNTRY = #{pkCountry}
        </if>
        <if test="startDate != null">
            and a.PAY_TIME &gt;= #{startDate, jdbcType=DATE}
        </if>
        <if test="endDate != null">
            and a.PAY_TIME &lt; #{endDate, jdbcType=DATE}
        </if>
    </select>

    <select id="queryCuMemberAssTotal" resultType="com.hzs.common.domain.member.detail.CuMemberAssess">
        select cm.pk_awards pkAwards, count(cm.pk_id) assessStatus
        from ${settleTable} cm
                 inner join bd_awards ba
                            on cm.pk_awards = ba.pk_id
        where period = #{period}
          and cm.pk_country = #{pkCountry}
          and ASSESS_TYPE = 1
          and ASSESS_STATUS in (0, 1)
          and ba.awards_value > 0
        group by cm.pk_awards
        order by pk_awards
    </select>

    <!-- 创建免考会员数据 -->
    <update id="createPassMemberAssess">
        merge into ${accessTableName} cma
            using (
                select cm.pk_id,
                       case
                           when cm.pay_time >= #{monthStartDate} and #{endDate} >= cm.pay_time then 0
                           else 1
                           end is_examine,
                       case
                           when cm.pay_time >= #{monthStartDate} and #{endDate} >= cm.pay_time then 1
                           else 2
                           end assess_status
                from cu_member cm
                where cm.del_flag = 0
                  and cm.category = 0
                  and cm.pay_status = 1
            ) tmp
            on (cma.pk_member = tmp.pk_id and assess_type = 5 and cma.del_flag = 0)
            when not matched then
                insert
                    (pk_member,
                     assess_type,
                     is_examine,
                     assess_status,
                     pk_creator,
                     start_date,
                     end_date)
                    values (tmp.pk_id,
                            5,
                            tmp.is_examine,
                            tmp.assess_status,
                            tmp.pk_id,
                            #{startDate},
                            #{endDate})
    </update>

    <!-- 处理会员考核 -->
    <update id="updatePassMemberAssess">
        merge into ${accessTableName} cma
            using (select tmp.pk_member,
                          tmp.pay_time,
                          case
                              when tmp.pay_time is null then 2
                              else 0
                              end assess_status
                   from (select cma.pk_member, tmp.pay_time
                         from ${accessTableName} cma
                                  left join (select so.pk_member, min(so.pay_time) pay_time
                                             from sa_order so
                                             where so.del_flag = 0
                                               and so.order_type = 3
                                               and so.order_status = 1
                                               and so.order_achieve > 0
                                               and so.pay_time >= #{startDate}
                                               and #{endDate} >= so.pay_time
                                             group by so.pk_member) tmp
                                            on tmp.pk_member = cma.pk_member
                         where cma.assess_type = 5
                           and cma.is_examine = 1
                           and cma.del_flag = 0) tmp
                   where tmp.pk_member not in
                         (select t.pk_member
                          from ${accessTableName} t
                          where t.del_flag = 0
                            and t.assess_type = 5
                            and t.is_examine = 0)
                ) tmp
            on (cma.pk_member = tmp.pk_member and assess_type = 5 and cma.is_examine = 1 and cma.del_flag = 0)
            when matched then
                update set cma.assess_status = tmp.assess_status,
                    cma.start_date = tmp.pay_time
            when not matched then
                insert
                    (pk_member,
                     assess_type,
                     is_examine,
                     assess_status,
                     pk_creator,
                     start_date,
                     end_date)
                    values
                    (tmp.pk_member,
                     5,
                     1,
                     tmp.assess_status,
                     tmp.pk_member,
                     trunc(tmp.pay_time),
                     #{endDate})
    </update>

    <!-- 初始化区域考核数据 -->
    <insert id="initAreaAssess">
    merge into ${accessTableName} cma
    using (select cmrr.pk_member,
           case
           when cmrr.city = 0 and cmrr.county = 0 then 6
           when cmrr.city != 0 and cmrr.county = 0 then 7
           when cmrr.city != 0 and cmrr.county != 0 then 8
           end assess_type,
           case
           when cmrr.city = 0 and cmrr.county = 0 then #{province.pkAwards}
           when cmrr.city != 0 and cmrr.county = 0 then #{city.pkAwards}
           when cmrr.city != 0 and cmrr.county != 0 then #{county.pkAwards}
           end assess_pk_awards,
           case
           when cmrr.city = 0 and cmrr.county = 0 then #{province.smallAssess}
           when cmrr.city != 0 and cmrr.county = 0 then #{city.smallAssess}
           when cmrr.city != 0 and cmrr.county != 0 then #{county.smallAssess}
           end assess_target,
           case
           when #{assessDate} >= cmrr.creation_time then 1
           else 0
           end is_examine,
           case
           when #{assessDate} >= cmrr.creation_time then 2
           else 1
           end assess_status,
           100000000 pk_creator,
           #{currentSettleDate} end_date
           from cu_member_retail_region cmrr
           where cmrr.del_flag = 0
           and cmrr.effective = 0) tmp
    on (cma.pk_member = tmp.pk_member and cma.assess_type = tmp.assess_type and cma.del_flag = 0)
    when not matched then
    insert (
    pk_member,
    assess_type,
    assess_pk_awards,
    is_examine,
    assess_target,
    assess_status,
    pk_creator,
    end_date)
    values (
    tmp.pk_member,
    tmp.assess_type,
    tmp.assess_pk_awards,
    tmp.is_examine,
    tmp.assess_target,
    tmp.assess_status,
    tmp.pk_creator,
    tmp.end_date)
    </insert>

    <!-- 更新区域考核月度业绩 -->
    <update id="updateAreaSmallPV">
        merge into ${accessTableName} cma
            using (select t.pk_member,
                          t.pk_awards,
                          case
                              when t.a_sum_pv > t.b_sum_pv then
                                  t.b_sum_pv
                              else
                                  t.a_sum_pv
                              end small_pv
                   from (select a.pk_member,
                                a.pk_awards,
                                nvl(a.a_sum_pv, 0) + nvl(a.rep_a_sum_pv, 0) -
                                nvl(b.a_sum_pv, 0) - nvl(b.rep_a_sum_pv, 0) a_sum_pv,
                                nvl(a.b_sum_pv, 0) + nvl(a.rep_b_sum_pv, 0) -
                                nvl(b.b_sum_pv, 0) - nvl(b.rep_b_sum_pv, 0) b_sum_pv
                         from (select cms.pk_member,
                                      cms.pk_awards,
                                      cms.a_sum_pv,
                                      cms.b_sum_pv,
                                      cms.rep_a_sum_pv,
                                      cms.rep_b_sum_pv
                               from ${currentSettleTableName} cms
                               where cms.pk_member in
                                     (select distinct cma.pk_member
                                      from ${accessTableName} cma
                                      where cma.del_flag = 0
                                        and cma.is_examine = 1
                                        and cma.assess_type in (6, 7, 8))) a
                            left join (select cms.pk_member,
                                              cms.a_sum_pv,
                                              cms.b_sum_pv,
                                              cms.rep_a_sum_pv,
                                              cms.rep_b_sum_pv
                                       from ${beforeMonthSettleTableName} cms
                                       where cms.pk_member in
                                             (select distinct cma.pk_member
                                              from ${accessTableName} cma
                                              where cma.del_flag = 0
                                                and cma.is_examine = 1
                                                and cma.assess_type in (6, 7, 8))) b
            on a.pk_member = b.pk_member) t
                ) tmp
            on (cma.pk_member = tmp.pk_member and cma.is_examine = 1 and cma.assess_type in (6, 7, 8) and cma.del_flag = 0)
            when matched then
        update set cma.pk_awards = tmp.pk_awards,
            cma.month_add = tmp.small_pv,
            cma.assess_status = (case
            when cma.assess_status = 1 then 1
            else 2
            end)
    </update>

    <!-- 更新区域考核考核状态 -->
    <update id="updateAreaAssessStatus">
        merge into ${accessTableName} cma
            using (select cma.pk_member, cma.assess_type
                   from ${accessTableName} cma
                            left join bd_awards baa
                                      on baa.pk_id = cma.assess_pk_awards
                            left join bd_awards ba
                                      on ba.pk_id = cma.pk_awards
                   where cma.del_flag = 0
                     and cma.assess_type in (6, 7, 8)
                     and cma.is_examine = 1
                     and cma.assess_status != 1
                     and ba.awards_value >= baa.awards_value
                     and cma.month_add >= cma.assess_target
            ) tmp
            on (cma.pk_member = tmp.pk_member and cma.assess_type = tmp.assess_type)
            when matched then
                update set cma.assess_status = 0
    </update>

    <!-- 更新会员区域失效 -->
    <update id="updateRetailRegionEffective">
        merge into cu_member_retail_region cmrr
            using (select cmrr.pk_id
                   from ${accessTableName} cma
                            left join (select t.pk_id,
                                              t.pk_member,
                                              case
                                                  when t.city = 0 and t.county = 0 then 6
                                                  when t.city != 0 and t.county = 0 then 7
                                                  when t.city != 0 and t.county != 0 then 8
                                              end assess_type
                                       from cu_member_retail_region t
                                       where t.del_flag = 0
                                         and t.effective = 0) cmrr
                                      on cmrr.pk_member = cma.pk_member and cmrr.assess_type = cma.assess_type
                   where cma.del_flag = 0
                     and cma.assess_type in (6, 7, 8)
                     and cma.assess_status = 2
            ) tmp
            on (cmrr.pk_id = tmp.pk_id)
            when matched then
                update set cmrr.effective = 1,
                    cmrr.effective_end_date = #{currentSettleDate}
    </update>

</mapper>
