DS_CALC_Procedure/NGDS_CALC_PROCEDURE/CALCREFEREE.sql

DROP PROCEDURE IF EXISTS `CALCREFEREE`;
delimiter ;;
CREATE PROCEDURE `CALCREFEREE`(IN `PBONUSID` VARCHAR ( 50 ),
	OUT `PNEWBONUSID` VARCHAR ( 50 ))
BEGIN
	DECLARE
		MUSERID,
		MBONUSID VARCHAR ( 50 );
	DECLARE
		MUSERTYPE INT;
	
	/*SET MUSERID := PBONUSID;
	OUTER_LABEL :
	WHILE
			(
			ISNULL( MBONUSID )) DO
		SELECT
			INTRODUCER_ID INTO MUSERID 
		FROM
			AR_BS_BONUS_103_CALC_NET T 
		WHERE
			T.USER_ID = MUSERID;
		IF
			( MUSERID = 0 ) THEN
				
				SET PNEWBONUSID := NULL;
			LEAVE OUTER_LABEL;
			
		END IF;
		SELECT
			USER_TYPE INTO MUSERTYPE 
		FROM
			AR_BS_BONUS_103_CALC_NET T 
		WHERE
			T.USER_ID = MUSERID;
		IF
			( MUSERTYPE >= 30 ) THEN
				
				SET PNEWBONUSID := MUSERID;
			LEAVE OUTER_LABEL;
			
		END IF;
		
	END WHILE;*/
  SELECT INTRODUCER_ID30 INTO PNEWBONUSID FROM AR_BS_BONUS_103_CALC_NET WHERE USER_ID = PBONUSID;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCSTAT
-- ----------------------------
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_MNT,
        BONUS_MNT_PERCENT,
        BONUS_ABBR,
        BONUS_ABBR_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(BONUS_MNT),
    0,
	SUM(BONUS_ABBR),
    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_MNT_PERCENT = BONUS_MNT / TOTAL_PV,
        BONUS_ABBR_PERCENT = BONUS_ABBR / TOTAL_PV
	WHERE
		PERIOD_NUM = PCPID AND TOTAL_PV > 0;

END
;;