DELIMITER $$DROP PROCEDURE IF EXISTS `SP_BACK_MANAGE_EAT`$$CREATE PROCEDURE `SP_BACK_MANAGE_EAT`(IN p_drawdateId BIGINT, IN p_gameId INT)proc: BEGINDECLARE _number VARCHAR(4);DECLARE _big DECIMAL(13,6);DECLARE _small DECIMAL(13,6);DECLARE _stockMaxPrize DECIMAL(20,6);DECLARE _weekDay INT;DECLARE _number_game_draw VARCHAR(20);DECLARE _firstBigPrize DECIMAL(13,6);DECLARE _firstSmlPrize DECIMAL(13,6);DECLARE _eatBig DECIMAL(13,6);DECLARE _eatSml DECIMAL(13,6)DECLARE _maxBig DECIMAL(13,6);DECLARE _maxSmall DECIMAL(13,6);DECLARE _putBig DECIMAL(13,6);DECLARE _putSml DECIMAL(13,6);DECLARE done, error BOOLEAN DEFAULT FALSE;DECLARE i INT;DECLARE _values MEDIUMTEXT;DECLARE c CURSOR FOR SELECT number,SUM(big) big,SUM(small) small FROM TB_BACK_EAT WHERE game_id=p_gameId AND drawDate_id=p_drawdateId AND STATUS=0 GROUP BY number;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = TRUE,done=TRUE;DECLARE CONTINUE HANDLER FOR 1329 SET done = TRUE; SELECT stock_max_prize,week_day INTO _stockMaxPrize,_weekDay FROM TB_BACK_DRAW_DATE WHERE id = p_drawdateId;SELECT first_big,first_sml INTO _firstBigPrize,_firstSmlPrize FROM TB_BACK_GAME WHERE id=p_gameId;SET _maxBig = _stockMaxPrize/_firstBigPrize;SET _maxSmall = _stockMaxPrize/_firstSmlPrize;OPEN c; IF error THEN SELECT error; LEAVE proc; END IF; SET autocommit=0; SET i=0;UPDATE TB_BACK_EAT_REPORT SET fromBig=0,fromSml=0,big=0,small=0,putBig=0,putSml=0 WHERE gameId=p_gameId AND drawDateId=p_drawDateId;REPEAT FETCH c INTO _number,_big,_small; IF NOT done THEN IF _big>=_maxBig THEN SET _eatBig = _maxBig;SET _eatSml = 0; ELSESET _eatBig = _big;SET _maxSmall = FLOOR((_stockMaxPrize - _big*_firstBigPrize)/_firstSmlPrize);IF _small>=_maxSmall THEN SET _eatSml = _maxSmall;ELSE SET _eatSml = _small;END IF; END IF; SET _putBig = _big - _eatBig; SET _putSml = _small-_eatSml; SET _number_game_draw = CONCAT(_number,'_',p_gameId,'_',p_drawDateId); IF i=0 THEN SET _values = CONCAT('(',p_gameId,',',p_drawDateId,',',_weekDay,',\'',_number,'\',',_big,',',_small,',',_eatBig,',',_eatSml,',',_putBig,',',_putSml,',',0,',',0,',\'',_number_game_draw,'\')'); ELSESET _values = CONCAT(_values,',','(',p_gameId,',',p_drawDateId,',',1,',\'',_number,'\',',_big,',',_small,',',_eatBig,',',_eatSml,',',_putBig,',',_putSml,',',0,',',0,',\'',_number_game_draw,'\')'); END IF; SET i=i+1; END IF;UNTIL done END REPEAT;CLOSE c;SET @sqltext=CONCAT('INSERT INTO TB_BACK_EAT_REPORT(gameId, drawDateId,WEEKDAY,number,fromBig,fromSml,big,small,putBig,putSml,outBig,outSml,num_game_draw) VALUES',_values,'ON DUPLICATE KEY UPDATE fromBig=VALUES(fromBig),fromSml=VALUES(fromSml),big=VALUES(big),small=VALUES(small),putBig=VALUES(putBig),putSml=VALUES(putSml);');PREPARE stmt FROM @sqltext;EXECUTE stmt;DEALLOCATE PREPARE stmt;COMMIT;SELECT error; END$$DELIMITER ;
