DS_CALC_Procedure/NGDS_CALC_PROCEDURE/QTRCALC.sql

DROP PROCEDURE IF EXISTS `QTRCALC`;
delimiter ;;
CREATE PROCEDURE `QTRCALC`(IN `PCPID` INT)
LABEL: BEGIN
-- DECLARE QTRSTARTPID, QTRSTARTMONTH, THISYEAR, THISMONTH, MISMONTH INT;

DECLARE TBONUS,BONUSPERU DECIMAL; -- 奖金总额,每份奖金
DECLARE QTRSTARTMONTH, THISYEAR, THISMONTH, MISMONTH INT; -- 这个月

-- DECLARE OCROWN1NUM,OCROWN2NUM,OCROWN3NUM,OCROWN4NUM,OCROWN5NUM,OCROWN6NUM,OCROWN7NUM INT; -- 各星级的人数，原始不加权
-- DECLARE  CROWN1U, CROWN2U, CROWN3U, CROWN4U, CROWN5U, CROWN6U, CROWN7U INT; -- 各星级的人数（加权）

DECLARE TOTALU INT; -- 加权的总份数

DECLARE MAXID, USERTBID INT;
DECLARE Q, R INT;

START TRANSACTION;
-- 日志;
DELETE FROM AR_QTR_CALC_RECORD ;
CALL QTRCALCRECORD(-1,'开始',PCPID,NOW());
COMMIT;

CALL QTRCLEAN(PCPID);
CALL QTRCALCRECORD(1,'清除数据',PCPID,NOW());
COMMIT;

SELECT
 CALC_YEAR, CALC_MONTH, IS_MONTH INTO THISYEAR, THISMONTH, MISMONTH FROM AR_PERIOD WHERE PERIOD_NUM = PCPID;

CALL QTRCALCRECORD(2,'初始化',PCPID,NOW());
COMMIT;

-- 查看是否季度结

IF(THISMONTH!=3 AND THISMONTH!=6 AND THISMONTH!=9 AND THISMONTH!=12) OR MISMONTH!=1 THEN
  CALL QTRCALCRECORD(12,'非季结，结束',PCPID,NOW());
	COMMIT;
  LEAVE LABEL;
END IF;

CALL QTRCALCRECORD(3,'计算总奖金',PCPID,NOW());
COMMIT;

-- 按照条件，查询当前季度所有月的总PV，计算总奖金
SET QTRSTARTMONTH = THISMONTH - 2;

SELECT 
   SUM(PV_PCS) * 0.02 
INTO TBONUS
  FROM AR_PERF_MONTH 
WHERE CALC_MONTH >= CONCAT(THISYEAR, lpad(QTRSTARTMONTH,2,0)) AND CALC_MONTH <= CONCAT(THISYEAR, lpad(THISMONTH,2,0));

CALL QTRCALCRECORD(4,'计算总人数及加权',PCPID,NOW());
COMMIT;


-- 查找，所有星级用户
INSERT INTO AR_QTR_CALC_WEIGHT
	SELECT 
		NULL AS ID,
		QY.USER_ID,
		MAX(CL.SORT) AS MAX_SORT,
		(MAX(CL.SORT) - 1)/2 + 1 AS WEIGHT,
		PERIOD_NUM,
		CALC_MONTH,
		CALC_YEAR
	FROM
		AR_CALC_BONUS_QY AS QY
	 INNER JOIN 
		AR_CROWN_LEVEL AS CL
	 ON QY.LAST_CROWN_LV = CL.ID
	 INNER JOIN 
		AR_USER AS U
	 ON QY.USER_ID = U.ID
	 
	WHERE
		QY.LAST_CROWN_LV != (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 0)
		AND U.DEC_LV = '67ABCE0ECE705575E055736AECE8644D'
		AND QY.CALC_MONTH >= CONCAT(THISYEAR, lpad(QTRSTARTMONTH,2,0)) 
		AND QY.CALC_MONTH <= CONCAT(THISYEAR, lpad(THISMONTH,2,0))

	GROUP BY USER_ID, CALC_MONTH;

COMMIT;

INSERT INTO AR_QTR_CALC_SCORE
	SELECT
		NULL AS ID,
		USER_ID, 
		COUNT(*) AS CNT,
		SUM(WEIGHT) AS SCORE,
		CALC_MONTH,
		CALC_YEAR
	FROM 
		AR_QTR_CALC_WEIGHT
	GROUP BY USER_ID;

COMMIT;
	
SELECT SUM(SCORE) INTO TOTALU FROM AR_QTR_CALC_SCORE;

SET BONUSPERU = TBONUS / TOTALU;


CALL QTRCALCRECORD(5,'计算每一份奖金',PCPID,NOW());
COMMIT;

INSERT INTO AR_QTR_CALC_USER
	SELECT 
		NULL AS ID,
		SC.USER_ID,
		BONUSPERU * SC.SCORE AS AMOUNT,
		CALC_YEAR,
		CALC_MONTH,
		PCPID AS PERIOD_NUM
	FROM
		AR_QTR_CALC_SCORE AS SC;

CALL QTRCALCRECORD(6,'写入user表',PCPID,NOW());

COMMIT;

INSERT INTO AR_CALC_BONUS_QUARTER
	SELECT
		NULL AS ID,
		AU.USER_ID,
		AU.AMOUNT AS ORI_BONUS,
		0 AS MANAGE_TAX,
		AU.AMOUNT,
		0 AS RECONSUME_POINTS,
		U.DEC_LV,
		AU.CALC_MONTH, 
		AU.PERIOD_NUM,
		NOW() AS CREATE_AT
	FROM
		AR_QTR_CALC_USER AS AU
	 LEFT JOIN
	  AR_USER AS U
	 ON AU.USER_ID = U.ID;

CALL QTRCALCRECORD(20,'写入季度奖金表',PCPID,NOW());
COMMIT;	

END
;;