NCDS_Procedure/CALCSTAT.sql

DROP PROCEDURE IF EXISTS `CALCSTAT`;
delimiter ;;
CREATE PROCEDURE `CALCSTAT`(IN `PCPID` INT)
BEGIN
	INSERT INTO AR_CALC_BONUS_BS_STAT (
		PERIOD_NUM,
		TOTAL_PV,
		BONUS10,
		PERCENT10,
		BONUS20,
		PERCENT20,
		BONUS30,
		PERCENT30,
		BONUS40,
		PERCENT40,
		BONUS50,
		PERCENT50,
		BONUS60,
		PERCENT60,
		BONUS70,
		PERCENT70,
		BONUS80,
		PERCENT80,
		BONUS90,
		PERCENT90,
		BONUS100,
		PERCENT100,
		BONUS110,
		PERCENT110,
		BONUS,
		PERCENT,
		CHECK_STATUS ,
		BONUS_ABBR,
        BONUS_ABBR_PERCENT,
        BONUS_MNT,
        BONUS_MNT_PERCENT
	) SELECT
	PCPID,
	SUM( PV ),
	SUM( BONUS10 ),
	0,
	SUM( BONUS20 ),
	0,
	SUM( BONUS30 ),
	0,
	SUM( BONUS40 ),
	0,
	SUM( BONUS50 ),
	0,
	SUM( BONUS60 ),
	0,
	SUM( BONUS70 ),
	0,
	SUM( BONUS80 ),
	0,
	SUM( BONUS90 ),
	0,
	SUM( BONUS100 ),
	0,
	SUM( BONUS110 ),
	0,
	SUM( BONUS ),
	0,
	0 ,
	SUM( BONUSYJ),
	0,
	SUM(BONUSGL),
	0
	FROM
		AR_BS_BONUS_103_CALC;
	UPDATE AR_CALC_BONUS_BS_STAT 
	SET PERCENT10 = BONUS10 / TOTAL_PV,
	PERCENT20 = BONUS20 / TOTAL_PV,
	PERCENT30 = BONUS30 / TOTAL_PV,
	PERCENT40 = BONUS40 / TOTAL_PV,
	PERCENT50 = BONUS50 / TOTAL_PV,
	PERCENT60 = BONUS60 / TOTAL_PV,
	PERCENT70 = BONUS70 / TOTAL_PV,
	PERCENT80 = BONUS80 / TOTAL_PV,
	PERCENT90 = BONUS90 / TOTAL_PV,
	PERCENT100 = BONUS100 / TOTAL_PV,
	PERCENT110 = BONUS110 / TOTAL_PV,
	PERCENT = BONUS / TOTAL_PV,
	BONUS_ABBR_PERCENT = BONUS_ABBR / TOTAL_PV,
	BONUS_MNT_PERCENT = BONUS_MNT /TOTAL_PV
	WHERE
		PERIOD_NUM = PCPID 
		AND TOTAL_PV > 0;

END
;;