NCDS_Procedure/CALCKEEP.sql

DROP PROCEDURE IF EXISTS `CALCKEEP`;
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;
		
		
	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_ABBR,
        MANAGE_TAX_ABBR,
        AMOUNT_ABBR,
        ABBR_RECONSUME_POINTS,
        ORI_BONUS_MNT,
        MANAGE_TAX_MNT,
        AMOUNT_MNT,
        MNT_RECONSUME_POINTS
		
		
	) 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.BONUSYJ,0),
	0 ,
	IFNULL(A.BONUSYJ,0),
	0,
	IFNULL(A.BONUSGL,0),
	0,
	IFNULL(A.BONUSGL,0),
	0
	
	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_ABBR,

        AMOUNT_ABBR,
        ORI_BONUS_MNT,

        AMOUNT_MNT
		
		
	) 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.BONUSYJ,0),

	IFNULL(A.BONUSYJ,0),
	IFNULL(A.BONUSGL,0),

	IFNULL(A.BONUSGL,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
;;