ngds/sql/upgrade/1988.sql

-- 总监级别表增加推荐会员相关属性
ALTER TABLE `AR_EMPLOY_LEVEL` ADD COLUMN `ACHIEVE_MEMBER_NUM` INT NOT NULL DEFAULT '0' COMMENT '推荐会员达标数' AFTER `ACHIEVE_PV`;
ALTER TABLE `AR_EMPLOY_LEVEL` ADD COLUMN `ACHIEVE_PERF_PV` decimal(10, 2) NOT NULL DEFAULT '0.00' COMMENT '推荐会员及格后晋级达标业绩PV' AFTER `ACHIEVE_MEMBER_NUM`;

-- 蓝星管理奖增加推荐会员相关属性
ALTER TABLE `AR_BS_BONUS_103_CALC` ADD COLUMN `ACHIEVE_MEMBER_NUM` INT NOT NULL DEFAULT '0' COMMENT '推荐会员达标数';
ALTER TABLE `AR_BS_BONUS_103_CALC` ADD COLUMN `ACHIEVE_PERF_PV` decimal(10, 2) NOT NULL DEFAULT '0.00' COMMENT '推荐会员及格后晋级达标业绩PV';

ALTER TABLE `AR_BS_BONUS_103_TEST` ADD COLUMN `ACHIEVE_MEMBER_NUM` INT NOT NULL DEFAULT '0' COMMENT '推荐会员达标数';
ALTER TABLE `AR_BS_BONUS_103_TEST` ADD COLUMN `ACHIEVE_PERF_PV` decimal(10, 2) NOT NULL DEFAULT '0.00' COMMENT '推荐会员及格后晋级达标业绩PV';

ALTER TABLE `AR_CALC_BONUS_BS` ADD COLUMN `ACHIEVE_MEMBER_NUM` INT NOT NULL DEFAULT '0' COMMENT '推荐会员达标数';
ALTER TABLE `AR_CALC_BONUS_BS` ADD COLUMN `ACHIEVE_PERF_PV` decimal(10, 2) NOT NULL DEFAULT '0.00' COMMENT '推荐会员及格后晋级达标业绩PV';



-- 蓝星管理奖存储过程修改
CREATE DEFINER=`black_tea_0309`@`%` PROCEDURE `CALCINIT`(IN `PCPID` INT, IN `PCALCMONTH` TINYINT, IN `PCALCYEAR` INT)
BEGIN

    DECLARE
        AC_USER_NO INT;     -- 邀请会员达标数
    DECLARE
        MLIMITPV,   -- 邀请会员不及格达标PV
        AC_PERF_PV DECIMAL ( 10, 2 );   -- 邀请会员及格达标PV

    -- 查询第一个级别的邀请会员数、达标PV值以及等奖比例
    SELECT
        T.ACHIEVE_PV,
        T.ACHIEVE_PERF_PV,
        T.ACHIEVE_MEMBER_NUM
    INTO MLIMITPV, AC_PERF_PV, AC_USER_NO
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
        T.ID = 'E121497617216708615';

    INSERT INTO AR_BS_BONUS_103_CALC (
        LAST_DEC_LV,
        LAST_EMP_LV,
        LAST_STATUS,
        USER_ID,
        INTRODUCER_ID,
        LAYER,
        PV,
        GPV10,
        GPV,
        GPV_4_CALC,
        USER_TYPE10,
        USER_TYPE,
        BONUS10,
        BONUS20,
        BONUS30,
        BONUS40,
        BONUS50,
        BONUS60,
        BONUS70,
        BONUS80,
        BONUS90,
        BONUS100,
        BONUS110,
        BONUS,
        PRODUCT_POINT,
        CALC_PERIOD_ID,
        ACHIEVE_MEMBER_NUM,
        ACHIEVE_PERF_PV
    ) SELECT
          AU.LAST_DEC_LV,
          AU.EMP_LV,
          AU.STATUS,
          AU.ID,
          IFNULL( AURN.PARENT_UID, 0 ),
          AURN.TOP_DEEP,
          IFNULL( AO.PV, 0 ),
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          PCPID,
          IFNULL(DO.ACHIEVE_MEMBER_NUM, 0) AS ACHIEVE_NUM,
          IF(DO.ACHIEVE_MEMBER_NUM >= AC_USER_NO, AC_PERF_PV, MLIMITPV) AS ACHIEVE_PV
    FROM
        AR_USER AU
    INNER JOIN AR_USER_RELATION_NEW AURN ON AU.ID = AURN.USER_ID
    LEFT JOIN (
        SELECT
            USER_ID,
            SUM( PV ) PV
        FROM
            AR_PERF_ORDER
        WHERE
                PERIOD_NUM IN ( SELECT PERIOD_NUM FROM AR_PERIOD WHERE CALC_YEAR = PCALCYEAR AND CALC_MONTH = PCALCMONTH )
        GROUP BY
            USER_ID
    ) AO ON AO.USER_ID = AU.ID
    LEFT JOIN (
        SELECT
            R.PARENT_UID,
            COUNT(O.USER_ID) AS ACHIEVE_MEMBER_NUM,
            GROUP_CONCAT(CONCAT(O.USER_ID, '_', U.USER_NAME, '_', O.PERIOD_NUM) separator ' | ') AS ACHIEVE_MEMBER
        FROM
            AR_PERF_ORDER O
                INNER JOIN AR_USER_RELATION_NEW R ON O.USER_ID = R.USER_ID
                INNER JOIN AR_USER U ON O.USER_ID = U.ID
        WHERE
                O.CALC_MONTH = PCALCMONTH AND O.DEC_TYPE = 'ZC'
        GROUP BY R.PARENT_UID
    ) DO ON DO.PARENT_UID = AU.ID
    WHERE
            AU.DELETED = 0;

END



CREATE DEFINER=`black_tea_0309`@`%` PROCEDURE `CALC10`(IN `PCPID` INT)
BEGIN
    DECLARE
        MLAYER,
        MMAXLAYER INT;
    DECLARE
        MRATE,
        MLIMITPV DECIMAL ( 10, 2 );-- 查询第一个级别的限制以及等奖比例
    SELECT
        T.ACHIEVE_PV,
        T.BS_PERCENT / 100 INTO MLIMITPV,
        MRATE
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = 'E121497617216708615';
    SELECT
        MAX( LAYER ) INTO MLAYER
    FROM
        AR_BS_BONUS_103_CALC
    WHERE
            PV > 0;

    SET MMAXLAYER := MLAYER;
    WHILE
            MLAYER >= 0 DO
            IF
                ( MLAYER < MMAXLAYER ) THEN-- 向上累计
                UPDATE AR_BS_BONUS_103_CALC T1
                    INNER JOIN (
                        SELECT
                            INTRODUCER_ID,
                            SUM( GPV10 ) GPV10
                        FROM
                            AR_BS_BONUS_103_CALC
                        WHERE
                                LAYER = MLAYER + 1
                          AND USER_TYPE10 = 0
                          AND GPV10 > 0
                        GROUP BY
                            INTRODUCER_ID
                    ) T2 ON T1.USER_ID = T2.INTRODUCER_ID
                        AND T1.LAYER = MLAYER
                SET T1.GPV10 = T2.GPV10;-- 没有升级的会员需要将自己的小组业绩清零
                UPDATE AR_BS_BONUS_103_CALC T
                SET T.GPV10 = 0
                WHERE
                        T.GPV10 < T.ACHIEVE_PERF_PV AND T.LAYER = MLAYER + 1 AND USER_TYPE10 = 0 AND T.GPV10 > 0;

            END IF;
            UPDATE AR_BS_BONUS_103_CALC T1
            SET T1.GPV10 = T1.GPV10 + T1.PV
            WHERE
                    T1.LAYER = MLAYER
              AND T1.PV > 0;-- 是否达标 如果达标
            UPDATE AR_BS_BONUS_103_CALC T
            SET T.USER_TYPE10 = 10
            WHERE
                    T.GPV10 >= T.ACHIEVE_PERF_PV
              AND T.LAYER = MLAYER
              AND T.USER_TYPE10 = 0;
            IF
                ( MLAYER > 0 ) THEN-- 升级为蓝星A
                CALL CALCUPGARDE ( MLAYER );

            END IF;

            SET MLAYER := MLAYER - 1;

        END WHILE;
    UPDATE AR_BS_BONUS_103_CALC T
    SET T.BONUS10 = T.GPV10 * MRATE
    WHERE
            T.USER_TYPE10 = 10
      AND T.GPV10 > 0;
    UPDATE AR_BS_BONUS_103_CALC
    SET USER_TYPE = 0;

    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
    SELECT USER_ID, NULL, 10, GPV10 * MRATE, GPV10, MRATE ,PCPID FROM AR_BS_BONUS_103_CALC WHERE USER_TYPE10 = 10
                                                                                             AND GPV10 > 0;
END






CREATE DEFINER=`black_tea_0309`@`%` PROCEDURE `CALC20`(IN `PCPID` INT)
BEGIN
    DECLARE
        MLAYER,
        MMAXLAYER,
        MCOUNT INT;
    DECLARE
        MRATE,
        MLIMITPV DECIMAL ( 10, 2 );
    START TRANSACTION;-- 第一个级别的限制
    SELECT
        T.ACHIEVE_PV INTO MLIMITPV
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = 'E121497617216708615';-- 第二个级别得奖的比例
    SELECT
            T.BS_PERCENT / 100 INTO MRATE
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '67BE5FE7857C216AE055736AECE8644D';
    SELECT
        MAX( LAYER ) INTO MLAYER
    FROM
        AR_BS_BONUS_103_CALC
    WHERE
            PV > 0;

    SET MMAXLAYER := MLAYER;
    WHILE
            MLAYER >= 0 DO
            IF
                ( MLAYER < MMAXLAYER ) THEN-- 向上累计
                UPDATE AR_BS_BONUS_103_CALC T1
                    INNER JOIN (
                        SELECT
                            INTRODUCER_ID,
                            SUM( GPV ) GPV
                        FROM
                            AR_BS_BONUS_103_CALC
                        WHERE
                                LAYER = MLAYER + 1
                          AND USER_TYPE = 0
                          AND GPV > 0
                        GROUP BY
                            INTRODUCER_ID
                    ) T2 ON T1.USER_ID = T2.INTRODUCER_ID
                        AND T1.LAYER = MLAYER
                SET T1.GPV = T2.GPV;-- 没有升级的会员需要将自己的小组业绩清零
                UPDATE AR_BS_BONUS_103_CALC T
                SET T.GPV = 0
                WHERE
                        T.GPV < T.ACHIEVE_PERF_PV AND T.LAYER = MLAYER + 1 AND GPV > 0;

            END IF;
            UPDATE AR_BS_BONUS_103_CALC T1
            SET T1.GPV = T1.GPV + T1.PV
            WHERE
                    T1.LAYER = MLAYER;-- 是否达标 如果达标
            UPDATE AR_BS_BONUS_103_CALC T
            SET T.USER_TYPE = 10
            WHERE
                    T.GPV >= T.ACHIEVE_PERF_PV
              AND T.LAYER = MLAYER;

            SET MLAYER := MLAYER - 1;

        END WHILE;
    UPDATE AR_BS_BONUS_103_CALC
    SET GPV = 0
    WHERE
            USER_TYPE = 0
      AND GPV > 0;
    SELECT
        MAX( LAYER ) INTO MLAYER
    FROM
        AR_BS_BONUS_103_CALC
    WHERE
            USER_TYPE > 0;

    SET MMAXLAYER := MLAYER;-- 添加至新网体页面
    INSERT INTO AR_BS_BONUS_103_CALC_NET ( USER_ID, INTRODUCER_ID, CALC_PERIOD_ID, USER_TYPE, GPV, DIRECTLY_UNDER, LAYER, TEAM_GPV ) SELECT
                                                                                                                                         USER_ID,
                                                                                                                                         INTRODUCER_ID,
                                                                                                                                         CALC_PERIOD_ID,
                                                                                                                                         USER_TYPE,
                                                                                                                                         GPV,
                                                                                                                                         0,
                                                                                                                                         LAYER,
                                                                                                                                         0
    FROM
        AR_BS_BONUS_103_CALC
    WHERE
            LAYER <= MLAYER; -- 处理新的网体
    DELETE FROM AR_BS_BONUS_103_CALC_NET WHERE LAYER = MLAYER AND USER_TYPE = 0; SET MLAYER := MLAYER - 1; WHILE MLAYER >= 0 DO-- 计算直属宽度
    UPDATE AR_BS_BONUS_103_CALC_NET T1
        INNER JOIN ( SELECT INTRODUCER_ID, COUNT( * ) SONS FROM AR_BS_BONUS_103_CALC_NET WHERE LAYER = MLAYER + 1 GROUP BY INTRODUCER_ID ) T2 ON T1.USER_ID = T2.INTRODUCER_ID
            AND T1.LAYER = MLAYER
    SET T1.DIRECTLY_UNDER = T2.SONS;-- 本人达标并且下属满足一个及以上的达标数升级为第二个级别
    UPDATE AR_BS_BONUS_103_CALC_NET T1
    SET T1.USER_TYPE = 20
    WHERE
            T1.LAYER = MLAYER
      AND USER_TYPE = 10
      AND DIRECTLY_UNDER >= 1;-- 计算本层达标人员
    SELECT
        COUNT( * ) INTO MCOUNT
    FROM
        AR_BS_BONUS_103_CALC_NET C
    WHERE
            C.DIRECTLY_UNDER >= 2
      AND C.USER_TYPE = 0
      AND LAYER = MLAYER;
    IF
        ( MCOUNT > 0 ) THEN
        CALL CALC20_0 ( MLAYER, PCPID );

    END IF;-- 删除未达标人员
    CALL CALCDELNET ( MLAYER );

    SET MLAYER := MLAYER - 1;

        END WHILE;-- 计算GPV_4_CALC,会员级别
    UPDATE AR_BS_BONUS_103_CALC C1
        INNER JOIN AR_BS_BONUS_103_CALC_NET C2 ON C1.USER_ID = C2.USER_ID
    SET C1.GPV_4_CALC = C2.GPV,
        C1.USER_TYPE = C2.USER_TYPE;-- 计算见习奖金
    UPDATE AR_BS_BONUS_103_CALC C1
        INNER JOIN ( SELECT INTRODUCER_ID, SUM( GPV ) ALL_GPV FROM AR_BS_BONUS_103_CALC_NET GROUP BY INTRODUCER_ID ) C2 ON C1.USER_ID = C2.INTRODUCER_ID
            AND C1.USER_TYPE = 20
    SET C1.BONUS20 = C2.ALL_GPV * MRATE;
    COMMIT;
    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE, PERIOD_NUM ) SELECT
                                                                                                                           C1.USER_ID,
                                                                                                                           NULL,
                                                                                                                           20,
                                                                                                                           C2.ALL_GPV * MRATE,
                                                                                                                           C2.ALL_GPV,
                                                                                                                           MRATE,
                                                                                                                           PCPID
    FROM
        AR_BS_BONUS_103_CALC C1
            INNER JOIN ( SELECT INTRODUCER_ID, SUM( GPV ) ALL_GPV FROM AR_BS_BONUS_103_CALC_NET GROUP BY INTRODUCER_ID ) C2 ON C1.USER_ID = C2.INTRODUCER_ID
            AND C1.USER_TYPE = 20
            AND ALL_GPV > 0;

END




CREATE DEFINER=`black_tea_0309`@`%` PROCEDURE `CALCKEEP`(IN PCALCYEAR int, IN PCALCMONTH int)
BEGIN
    INSERT INTO AR_BS_BONUS_103_TEST SELECT
        *
    FROM
        AR_BS_BONUS_103_CALC;
    INSERT INTO AR_BS_BONUS_103_TEST_NET SELECT
        *
    FROM
        AR_BS_BONUS_103_CALC_NET;
    INSERT INTO AR_CALC_BONUS_BS (
        USER_ID,
        INTRODUCER_ID,
        LAST_DEC_LV,
        LAST_EMP_LV,
        LAST_STATUS,
        LEVEL_ID,
        ORI_BONUS,
        MANAGE_TAX,
        AMOUNT,
        PRODUCT_POINT,
        PERIOD_NUM,
        CALC_YEAR,
        CALC_MONTH,
        P_CALC_MONTH,
        CREATED_AT,
        ORI_BONUS_MNT,
        MANAGE_TAX_MNT,
        AMOUNT_MNT,
        ORI_BONUS_ABBR,
        MANAGE_TAX_ABBR,
        AMOUNT_ABBR,
        ACHIEVE_MEMBER_NUM,
        ACHIEVE_PERF_PV
    ) SELECT
          A.USER_ID,
          A.INTRODUCER_ID,
          A.LAST_DEC_LV,
          A.LAST_EMP_LV,
          A.LAST_STATUS,
          B.ID,
          IFNULL( A.BONUS, 0 ),
          0,
          IFNULL( A.BONUS, 0 ),
          IFNULL( A.PRODUCT_POINT, 0 ),
          CALC_PERIOD_ID,
          PCALCYEAR,
          PCALCYEAR*100+PCALCMONTH,
          NOW(),
          UNIX_TIMESTAMP(
                  NOW()),
          IFNULL( A.BONUS_MNT, 0 ),
          0,
          IFNULL( A.BONUS_MNT, 0 ),
          IFNULL( A.BONUS_ABBR, 0 ),
          0,
          IFNULL( A.BONUS_ABBR, 0 ),
          A.ACHIEVE_MEMBER_NUM,
          A.ACHIEVE_PERF_PV
    FROM
        AR_BS_BONUS_103_CALC A
            LEFT JOIN AR_EMPLOY_LEVEL B ON A.USER_TYPE = B.SORT * 10
    WHERE
            A.BONUS > 0
       OR A.PRODUCT_POINT > 0;
    INSERT INTO AR_CALC_BONUS_BS_DETAIL (
        USER_ID,
        INTRODUCER_ID,
        LAST_DEC_LV,
        LAST_EMP_LV,
        LAST_STATUS,
        LEVEL_ID,
        ORI_BONUS,
        MANAGE_TAX,
        AMOUNT,
        PRODUCT_POINT,
        PERIOD_NUM,
        LAYER,
        PV,
        GPV10,
        GPV,
        GPV_4_CALC,
        USER_TYPE10,
        USER_TYPE,
        BONUS10,
        BONUS20,
        BONUS30,
        BONUS40,
        BONUS50,
        BONUS60,
        BONUS70,
        BONUS80,
        BONUS90,
        BONUS100,
        BONUS110,
        CALC_YEAR,
        CALC_MONTH,
        P_CALC_MONTH,
        CREATED_AT,
        ORI_BONUS_MNT,
        MANAGE_TAX_MNT,
        AMOUNT_MNT,
        ORI_BONUS_ABBR,
        MANAGE_TAX_ABBR,
        AMOUNT_ABBR
    ) SELECT
          USER_ID,
          INTRODUCER_ID,
          LAST_DEC_LV,
          LAST_EMP_LV,
          LAST_STATUS,
          B.ID,
          IFNULL( BONUS, 0 ),
          0,
          IFNULL( BONUS, 0 ),
          IFNULL( PRODUCT_POINT, 0 ),
          CALC_PERIOD_ID,
          LAYER,
          PV,
          GPV10,
          GPV,
          GPV_4_CALC,
          USER_TYPE10,
          USER_TYPE,
          BONUS10,
          BONUS20,
          BONUS30,
          BONUS40,
          BONUS50,
          BONUS60,
          BONUS70,
          BONUS80,
          BONUS90,
          BONUS100,
          BONUS110,
          PCALCYEAR,
          PCALCYEAR*100+PCALCMONTH,
          NOW(),
          UNIX_TIMESTAMP(
                  NOW()),
          IFNULL( A.BONUS_MNT, 0 ),
          0,
          IFNULL( A.BONUS_MNT, 0 ),
          IFNULL( A.BONUS_ABBR, 0 ),
          0,
          IFNULL( A.BONUS_ABBR, 0 )
    FROM
        AR_BS_BONUS_103_CALC A
            LEFT JOIN ( SELECT ID, SORT FROM AR_EMPLOY_LEVEL ) B ON A.USER_TYPE = B.SORT * 10;

END