DS_CALC_Procedure/NGDS_CALC_PROCEDURE/CALCKEEP.sql

DROP PROCEDURE IF EXISTS `CALCKEEP`;
delimiter ;;
CREATE 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;
           
    UPDATE AR_BS_BONUS_103_CALC
    SET USER_TYPE = 10
    WHERE
            USER_TYPE = 0
      AND USER_TYPE10 = 10;
       
    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
       OR A.USER_TYPE = 10;
      
      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
;;