NCDS_Procedure/CALCCHECKING.sql

DROP PROCEDURE IF EXISTS `CALCCHECKING`;
delimiter ;;
CREATE PROCEDURE `CALCCHECKING`(IN `PCPID` INT)
LABEL : BEGIN-- 遍历数据结束标志
	DECLARE
		MCOLUMNNAME,
		MVALUES,
		MVALUES0,
		MVALUES1,
		MVALUES2 VARCHAR ( 100 );-- 定义预处理SQL语句
	DECLARE
		SQL_FOR_SELECT VARCHAR ( 2000 );
	DECLARE
		MCOUNT INT DEFAULT 0;
	DECLARE
		DONE INT DEFAULT FALSE;
	DECLARE
		MISMONTH,
		MISSEND TINYINT;-- 游标
	DECLARE
		CUR_ACCOUNT CURSOR FOR SELECT
		COLUMN_NAME 
	FROM
		INFORMATION_SCHEMA.COLUMNS 
	WHERE
		TABLE_NAME = 'AR_CALC_BONUS_BS_DETAIL' 
		AND TABLE_SCHEMA = 'aikang_db_28' 
		AND COLUMN_NAME NOT IN ( 'CALC_YEAR', 'CALC_MONTH', 'P_CALC_MONTH', 'CREATED_AT', 'ID', 'LAYER' ) 
		AND DATA_TYPE IN ( 'DECIMAL', 'DOUBLE', 'INT' );-- 将结束标志绑定到游标
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET DONE = TRUE;
	START TRANSACTION;
	SELECT
		AP.IS_MONTH,
		AP.IS_SENT INTO MISMONTH,
		MISSEND 
	FROM
		AR_PERIOD AP 
	WHERE
		AP.PERIOD_NUM = PCPID;-- 查看是否月结
	IF
		( MISMONTH = 0 OR MISSEND = 1 ) THEN
			LEAVE LABEL;
		
	END IF;-- 打开游标
	DELETE 
	FROM
		AR_CALC_BONUS_BS_CHECKING 
	WHERE
		PERIOD_NUM >= PCPID;
	OPEN CUR_ACCOUNT;-- 遍历
	READ_LOOP :
	LOOP-- 取值 取多个字段
		FETCH NEXT 
		FROM
			CUR_ACCOUNT INTO MCOLUMNNAME;
		IF
			DONE THEN
				LEAVE READ_LOOP;
			
		END IF;
		
		SET SQL_FOR_SELECT = CONCAT(
			"SELECT COUNT(*) INTO @MVALUES0 FROM (SELECT IFNULL(A1.USER_ID,'-1') MAIN_ID,IFNULL(A2.USER_ID,'-1') TEST_ID,IFNULL(A1.",
			MCOLUMNNAME,
			",0) NAME1,IFNULL(A2.",
			MCOLUMNNAME,
			",0) NAME2 FROM AR_CALC_BONUS_BS_DETAIL A1 LEFT JOIN AR_CALC_BONUS_BS_DETAIL_CALC A2 ON A1.USER_ID = A2.USER_ID WHERE A1.PERIOD_NUM = ",
			PCPID,
			" AND A2.PERIOD_NUM = ",
			PCPID,
			" UNION SELECT IFNULL(A2.USER_ID,'-1') MAIN_ID,IFNULL(A1.USER_ID,'-1') TEST_ID,IFNULL(A2.",
			MCOLUMNNAME,
			",0) NAME1,IFNULL(A1.",
			MCOLUMNNAME,
			",0) NAME2 FROM AR_CALC_BONUS_BS_DETAIL_CALC A1 LEFT JOIN AR_CALC_BONUS_BS_DETAIL A2 ON A1.USER_ID = A2.USER_ID WHERE A1.PERIOD_NUM = ",
			PCPID,
			" AND A2.PERIOD_NUM = ",
			PCPID,
			") B WHERE B.MAIN_ID != B.TEST_ID OR ABS(B.NAME1-B.NAME2)>0.5" 
		);-- 拼接查询SQL语句
		
		SET @SQL = SQL_FOR_SELECT;
		PREPARE STMT 
		FROM
			@SQL;-- 预处理动态SQL语句
		EXECUTE STMT;-- 执行SQL语句
		DEALLOCATE PREPARE STMT;
		
		SET MVALUES0 := @MVALUES0;
		
		SET SQL_FOR_SELECT = CONCAT( "SELECT IFNULL(SUM(", MCOLUMNNAME, "),0) INTO @MVALUES1 FROM  AR_CALC_BONUS_BS_DETAIL  WHERE PERIOD_NUM = '", PCPID, "'" );-- 拼接查询SQL语句
		
		SET @SQL = SQL_FOR_SELECT;
		PREPARE STMT 
		FROM
			@SQL;-- 预处理动态SQL语句
		EXECUTE STMT;-- 执行SQL语句
		DEALLOCATE PREPARE STMT;
		
		SET MVALUES1 := @MVALUES1;
		
		SET SQL_FOR_SELECT = CONCAT( "SELECT IFNULL(SUM(", MCOLUMNNAME, "),0) INTO @MVALUES2 FROM  AR_CALC_BONUS_BS_DETAIL_CALC  WHERE USER_ID != '0' AND PERIOD_NUM = '", PCPID, "'" );-- 拼接查询SQL语句
		
		SET @SQL = SQL_FOR_SELECT;
		PREPARE STMT 
		FROM
			@SQL;-- 预处理动态SQL语句
		EXECUTE STMT;-- 执行SQL语句
		DEALLOCATE PREPARE STMT;
		
		SET MVALUES2 := @MVALUES2;
		INSERT INTO AR_CALC_BONUS_BS_CHECKING ( PERIOD_NUM, COLUMN_NAME, DIFF_COUNT, MAIN_SUM, CALC_SUM, DIFF_SUM )
		VALUES
			(
				PCPID,
				MCOLUMNNAME,
				MVALUES0,
				MVALUES1,
				MVALUES2,
				ABS(
					CONVERT (
						MVALUES1,
						DECIMAL ( 12, 2 ))- CONVERT (
						MVALUES2,
					DECIMAL ( 12, 2 ))));
		COMMIT;
		
	END LOOP;
	CLOSE CUR_ACCOUNT;
	SELECT
		COUNT(*) INTO MCOUNT 
	FROM
		AR_CALC_BONUS_BS_CHECKING 
	WHERE
		( DIFF_COUNT > 0 OR DIFF_SUM >= 1 ) 
		AND COLUMN_NAME IN (
			'USER_TYPE10',
			'USER_TYPE',
			'BONUS10',
			'BONUS20',
			'BONUS30',
			'BONUS40',
			'BONUS50',
			'BONUS60',
			'BONUS70',
			'BONUS80',
			'BONUS90',
			'BONUS100',
			'BONUS110',
			'ORI_BONUS',
			'AMOUNT',
			'PRODUCT_POINT' 
		);
	IF
		( MCOUNT = 0 ) THEN
			UPDATE AR_CALC_BONUS_BS_STAT T 
			SET T.CHECK_STATUS = 1 
		WHERE
			T.PERIOD_NUM = PCPID;
		
	END IF;
	COMMIT;

END
;
;;