NCDS_Procedure/CALCINIT.sql

DROP PROCEDURE IF EXISTS `CALCINIT`;
delimiter ;;
CREATE PROCEDURE `CALCINIT`(IN `PCPID` INT, IN `PCALCMONTH` TINYINT, IN `PCALCYEAR` INT)
BEGIN
    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,
				PV_ZC,
				PV_FX,
				PV_UP_ZC
    ) 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,
		(SELECT
            IFNULL( SUM( PV ), 0 ) PV
        FROM
            AR_PERF_ORDER 
        WHERE
            PERIOD_NUM IN ( SELECT PERIOD_NUM FROM AR_PERIOD WHERE CALC_YEAR = PCALCYEAR AND CALC_MONTH = PCALCMONTH)
            AND USER_ID=AU.ID AND DEC_TYPE='ZC')PV_ZC,
		(SELECT
            IFNULL( SUM( PV ), 0 ) PV
        FROM
            AR_PERF_ORDER 
        WHERE
            PERIOD_NUM IN ( SELECT PERIOD_NUM FROM AR_PERIOD WHERE CALC_YEAR = PCALCYEAR AND CALC_MONTH = PCALCMONTH)
            AND USER_ID=AU.ID AND DEC_TYPE='FX')PV_FX,
		(SELECT
            IFNULL( SUM( O.PV ), 0 ) PV
        FROM
            AR_PERF_ORDER O
						LEFT JOIN AR_USER_RELATION_NEW R ON R.USER_ID = O.USER_ID
        WHERE
            O.PERIOD_NUM IN ( SELECT PERIOD_NUM FROM AR_PERIOD WHERE CALC_YEAR = PCALCYEAR AND CALC_MONTH = PCALCMONTH)
            AND R.PARENT_UID=AU.ID AND O.DEC_TYPE='ZC')PV_UP_ZC
    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 
    WHERE
        AU.DELETED = 0;

END
;;