DS_CALC_Procedure/NGDS_CALC_PROCEDURE/CALCINIT.sql

DROP PROCEDURE IF EXISTS `CALCINIT`;
delimiter ;;
CREATE 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),
          IF(DO.ACHIEVE_MEMBER_NUM >= AC_USER_NO, AC_PERF_PV, MLIMITPV)
    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(R.USER_ID) AS ACHIEVE_MEMBER_NUM
        FROM
            AR_PERF_ORDER O
            INNER JOIN AR_USER_RELATION_NEW R ON O.USER_ID = R.USER_ID
        WHERE
            O.DEC_TYPE = 'ZC' AND O.PERIOD_NUM IN ( SELECT PERIOD_NUM FROM AR_PERIOD WHERE CALC_YEAR = PCALCYEAR AND CALC_MONTH = PCALCMONTH )
        GROUP BY R.PARENT_UID
    ) DO ON DO.PARENT_UID = AU.ID
    WHERE
            AU.DELETED = 0;

END
;;