抽奖sql存储过程

抽奖实现以下需求,注释比较全,可以根据自己的需求改动

• 必须到店才能抽到奖品,不到店抽吸引到店的奖品
• 按奖品优先级来计算中奖概率
• 朋友抽中奖品则抽奖概率下降
• 抽到过奖品中奖概率下降
• 根据商家奖品数量将奖品分布到营业时间的不同时区
• 店铺连续未抽中则中奖概率上升
• 根据性别抽到不同奖品概率不同

CREATE DEFINER=`root`@`%` PROCEDURE `SP_LotteryDraw`(in _luck_draw_account_id int,
in _shop_id  int,
in _is_inshop int)
BEGIN
	#定义变量
	DECLARE _GIFT_1 INT;    #抽到撒红包的次数
	DECLARE _GIFT_2 INT;    #抽到交友的次数
	DECLARE _GIFT_3 INT;    #抽到折扣商品的次数
	DECLARE _GIFT_4 INT;    #抽到免费商品的次数
	 
	DECLARE _TODAY_1 INT; 	#今日在该店抽到的免费商品数量
	DECLARE _TODAY_2 INT; 	#今日在该店抽到的优惠商品数量
	DECLARE _TODAY INT; 	#今日是否还可以抽到折扣或免费商品奖品

	DECLARE _HAS_GIFT_1 INT;    #有效撒红包的奖励个数
	DECLARE _HAS_GIFT_2 INT;    #有效交友的奖励个数
	DECLARE _HAS_GIFT_3 INT;    #有效折扣商品的奖励个数
	DECLARE _HAS_GIFT_4 INT;    #有效免费商品的奖励个数
	
	#DECLARE _HAS_GIFT_QUANTITY_3 INT;    #有效折扣商品的奖励个数
	#DECLARE _HAS_GIFT_QUANTITY_4 INT;    #有效免费商品的奖励个数
	
	DECLARE _BUSINESSMIN INT;	   #该店每日营业分钟数
	DECLARE _NOGIFTTIMES INT; 	 #该店连续未抽到奖励的次数
	#DECLARE _FRIENDTIMES INT;	   #朋友（父子ID）抽到同店奖励次数【15分钟内】
	DECLARE _FRIENDGIFTTIMES INT; #朋友（父子ID）抽到同店折扣或免费商品的次数【30分钟内】
	
	#DECLARE _SIGNVAL INT;   #偏差值
	DECLARE _SEX INT;       #性别
	
	#以下定义抽奖变量
	DECLARE _GIFT_TYPE INT;     #奖品类型
	DECLARE _GIFT_KEY INT;     #奖品主键
	DECLARE _NUMERATOR INT;     #分子
	DECLARE _DENOMINATOR INT;   #分母
	
	#定义事务
	DECLARE t_error INTEGER DEFAULT 0;    
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;    
	START TRANSACTION;    #开启事务
	
	#开始参数赋值	
	#SET _SIGNVAL = 2;	
	
	#性别赋值
	SELECT sex INTO _SEX FROM app_user WHERE user_id = _luck_draw_account_id;

	
	#该店每日营业分钟数
	SELECT IF
		(timestampdiff(MINUTE, business_time_strat,business_time_end) < 0,
		timestampdiff(MINUTE, business_time_strat,business_time_end) + 1440,
		timestampdiff(MINUTE, business_time_strat,business_time_end))  INTO _BUSINESSMIN
	FROM app_shop WHERE shop_id = _shop_id;
	

	
	#该店四种有效奖励赋值
	SELECT COUNT(0) INTO _HAS_GIFT_4 FROM app_shop_coupon WHERE coupon_type = 1 AND shop_id = _shop_id AND coupon_quantity > 0  AND dr=0;
	SELECT COUNT(0) INTO _HAS_GIFT_3 FROM app_shop_coupon WHERE coupon_type = 2 AND shop_id = _shop_id AND coupon_quantity > 0 AND dr=0;	
	SELECT COUNT(0) INTO _HAS_GIFT_2 FROM app_user_publish WHERE publish_type = 1 AND shop_id = _shop_id AND (want_sex = 0 OR want_sex =_SEX)
	AND user_id != _luck_draw_account_id
	AND	NOT EXISTS (SELECT * FROM app_lottery_log WHERE app_user_publish.user_id = app_lottery_log.publish_user_id 
	AND app_lottery_log.creatime_time  > DATE_SUB(NOW(), INTERVAL 240 MINUTE));		
	SELECT COUNT(0) INTO _HAS_GIFT_1 FROM app_user_publish WHERE publish_type = 2 AND publish_status = 1 AND red_envelope_surplus_quantity > 0 
	AND shop_id = _shop_id AND only_available_nearby <= _is_inshop AND (want_sex = 0 OR want_sex =_SEX)
	AND user_id != _luck_draw_account_id
	AND	NOT EXISTS (SELECT * FROM app_user_red_envelope_log WHERE app_user_publish.publish_id = app_user_red_envelope_log.publish_id 
	AND app_user_red_envelope_log.user_id = _luck_draw_account_id);
	
	#抽到4种奖励次数赋值
	SELECT COUNT(0) INTO _GIFT_1 FROM app_lottery_log WHERE user_id = _luck_draw_account_id AND prize_type = 1;
	SELECT COUNT(0) INTO _GIFT_2 FROM app_lottery_log WHERE user_id = _luck_draw_account_id AND prize_type = 2;
	SELECT COUNT(0) INTO _GIFT_3 FROM app_lottery_log WHERE user_id = _luck_draw_account_id AND prize_type = 3;
	SELECT COUNT(0) INTO _GIFT_4 FROM app_lottery_log WHERE user_id = _luck_draw_account_id AND prize_type = 4;
	

	#为当天在该店抽到的奖励次数赋值
#当天在该店抽到的免费奖励次数
SELECT COUNT(0) INTO _TODAY_1 FROM app_lottery_log WHERE user_id = _luck_draw_account_id AND prize_type = 4 AND shop_id = _shop_id AND   creatime_time >= date(now())
AND creatime_time < DATE_ADD(date(now()),INTERVAL 1 DAY);
#当天在该店抽到的折扣奖励次数
SELECT COUNT(0) INTO _TODAY_2 FROM app_lottery_log WHERE user_id = _luck_draw_account_id AND prize_type = 3 AND shop_id = _shop_id  AND   creatime_time >= date(now())
AND creatime_time < DATE_ADD(date(now()),INTERVAL 1 DAY);


if _TODAY_1<1 or _TODAY_2 <1 then 
select 1 INTO _TODAY;
ELSE
select 0 INTO _TODAY;
end if;


	#该店连续未抽到奖的次数
	SELECT COUNT(0) INTO _NOGIFTTIMES FROM app_lottery_log WHERE user_id = _luck_draw_account_id AND shop_id = _shop_id 
	AND log_id > (SELECT IFNULL(max(log_id),0) FROM app_lottery_log WHERE prize_type != 5);
	
	#朋友（父子ID）抽到同店折扣或免费商品的次数【30分钟内】
	SELECT COUNT(0)+1 INTO _FRIENDGIFTTIMES FROM app_lottery_log WHERE (prize_type = 3 or prize_type = 4) AND shop_id = _shop_id AND prize_status = 1 
	AND update_time > DATE_SUB(NOW(), INTERVAL 30 MINUTE) AND user_id in (
	SELECT user_id FROM app_user WHERE parent_id = _luck_draw_account_id
	UNION ALL
	SELECT parent_id FROM app_user WHERE user_id = _luck_draw_account_id);


	
	#以上是参数赋值，以下是抽奖逻辑
	IF _GIFT_3 = 0 AND _GIFT_4 = 0 THEN
		IF _HAS_GIFT_3 = 0 AND _HAS_GIFT_4 = 0 THEN
			#没有抽过免费折扣奖励，并且店内无免费折扣奖励时，抽交友红包奖励	
			#在店内
			if _is_inshop > 0  then
			
			SELECT publish_id INTO _GIFT_KEY FROM (
			SELECT * FROM app_user_publish WHERE publish_type = 1 AND shop_id = _shop_id AND (want_sex = 0 OR want_sex =_SEX)
			AND user_id != _luck_draw_account_id
			AND publish_status=1
			AND	NOT EXISTS (SELECT * FROM app_lottery_log WHERE app_user_publish.user_id = app_lottery_log.publish_user_id 
			AND app_lottery_log.creatime_time  > DATE_SUB(NOW(), INTERVAL 240 MINUTE))
			
			UNION ALL
			SELECT * FROM app_user_publish WHERE publish_type = 2 AND publish_status = 1 AND red_envelope_surplus_quantity > 0 
			AND shop_id = _shop_id AND only_available_nearby <= _is_inshop AND (want_sex = 0 OR want_sex =_SEX)
			AND user_id != _luck_draw_account_id
			AND publish_status=1
			AND	NOT EXISTS (SELECT * FROM app_user_red_envelope_log WHERE app_user_publish.publish_id = app_user_red_envelope_log.publish_id 
			AND app_user_red_envelope_log.user_id = _luck_draw_account_id)) t ORDER BY RAND() LIMIT 1;
			
			else 
			
				SELECT publish_id INTO _GIFT_KEY FROM (
			SELECT * FROM app_user_publish WHERE publish_type = 1 AND shop_id = _shop_id AND (want_sex = 0 OR want_sex =_SEX)
			AND user_id != _luck_draw_account_id
			AND publish_status=1
			AND	NOT EXISTS (SELECT * FROM app_lottery_log WHERE app_user_publish.user_id = app_lottery_log.publish_user_id 
			AND app_lottery_log.creatime_time  > DATE_SUB(NOW(), INTERVAL 240 MINUTE))
			UNION ALL
			SELECT * FROM app_user_publish WHERE publish_type = 1 AND publish_status = 1 AND red_envelope_surplus_quantity > 0 
			AND shop_id = _shop_id AND only_available_nearby <= _is_inshop AND (want_sex = 0 OR want_sex =_SEX)
			AND user_id != _luck_draw_account_id
			AND publish_status=1
			AND	NOT EXISTS (SELECT * FROM app_user_red_envelope_log WHERE app_user_publish.publish_id = app_user_red_envelope_log.publish_id 
			AND app_user_red_envelope_log.user_id = _luck_draw_account_id)) t ORDER BY RAND() LIMIT 1;
			end if;


			IF    _GIFT_KEY > 0  THEN #有奖品 				
				INSERT INTO app_lottery_log(shop_id,prize_type,prize_id,prize_status,publish_user_id,user_id,creatime_time,update_time)
				SELECT _shop_id,CASE publish_type WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE 5 END,
				publish_id,CASE publish_type WHEN 1 THEN 1 WHEN 2 THEN 0 ELSE 1 END,
				user_id,_luck_draw_account_id,NOW(),NOW() FROM app_user_publish where publish_id = _GIFT_KEY;
			ELSE #无奖品
				INSERT INTO app_lottery_log(shop_id,prize_type,prize_status,user_id,creatime_time,update_time)
				VALUES(_shop_id,5,1,_luck_draw_account_id,NOW(),NOW());
			END IF;
			
		ELSE
			#没有抽过免费折扣奖励，店内有免费折扣奖励时，抽免费折扣奖励			
			SELECT coupon_id,coupon_quantity,
			CASE reset_cycle
			WHEN 1 THEN
			1440 - TIMESTAMPDIFF(MINUTE,last_reset_time,NOW())
			WHEN 2 THEN
			1440*7 - TIMESTAMPDIFF(MINUTE,last_reset_time,NOW())
			WHEN 3 THEN
			1440*7*30 - TIMESTAMPDIFF(MINUTE,last_reset_time,NOW())
			ELSE
			1440*7*365 - TIMESTAMPDIFF(MINUTE,last_reset_time,NOW())
			END INTO _GIFT_KEY,_NUMERATOR,_DENOMINATOR
			FROM (
			SELECT * FROM app_shop_coupon WHERE coupon_type = 1 AND shop_id = _shop_id AND coupon_quantity > 0 AND dr=0
			UNION ALL
			SELECT * FROM app_shop_coupon WHERE coupon_type = 2 AND shop_id = _shop_id AND coupon_quantity > 0  AND dr=0) t ORDER BY RAND() LIMIT 1;
		
			IF  _GIFT_KEY > 0 AND FLOOR(RAND()*100000) MOD (_DENOMINATOR/10*_FRIENDGIFTTIMES) < _NUMERATOR*_NOGIFTTIMES THEN #有奖品	

			IF  _TODAY_1>0 and _TODAY_2<1 THEN
				INSERT INTO app_lottery_log(shop_id,prize_type,prize_id,prize_status,user_id,creatime_time,update_time)
				SELECT _shop_id,CASE coupon_type  WHEN 2 THEN 3 ELSE 5 END,
						 CASE coupon_type  WHEN 2 THEN coupon_id ELSE null END,CASE coupon_type  WHEN 2 THEN 0 ELSE 1 END,_luck_draw_account_id,NOW(),NOW() FROM app_shop_coupon where coupon_id = _GIFT_KEY;
			elseif  _TODAY_2>0 and _TODAY_1<1 then 
					INSERT INTO app_lottery_log(shop_id,prize_type,prize_id,prize_status,user_id,creatime_time,update_time)
				SELECT _shop_id,CASE coupon_type  WHEN 1 THEN 4 ELSE 5 END,
				 CASE coupon_type  WHEN 1 THEN coupon_id ELSE null END,CASE coupon_type  WHEN 1 THEN 0 ELSE 1 END,_luck_draw_account_id,NOW(),NOW() FROM app_shop_coupon where coupon_id = _GIFT_KEY;
				elseif  _TODAY_2<1 and _TODAY_1<1 then 
	INSERT INTO app_lottery_log(shop_id,prize_type,prize_id,prize_status,user_id,creatime_time,update_time)
				SELECT _shop_id,CASE coupon_type  WHEN 1 THEN 4  WHEN 2 THEN 3 ELSE 5 END,
				coupon_id,0,_luck_draw_account_id,NOW(),NOW() FROM app_shop_coupon where coupon_id = _GIFT_KEY;
		else 
			INSERT INTO app_lottery_log(shop_id,prize_type,prize_status,user_id,creatime_time,update_time)
				VALUES(_shop_id,5,1,_luck_draw_account_id,NOW(),NOW());
			end if;		
	ELSE #无奖品
				INSERT INTO app_lottery_log(shop_id,prize_type,prize_status,user_id,creatime_time,update_time)
				VALUES(_shop_id,5,1,_luck_draw_account_id,NOW(),NOW());
			END IF;	
			
		END IF;
	ELSE
		IF FLOOR(RAND()*1000) MOD 2 THEN
			#如果抽到过免费折扣奖励，随机抽，随机数除以2余1，抽交友红包奖励
			if _is_inshop > 0  then
			SELECT publish_id INTO _GIFT_KEY FROM (
			SELECT * FROM app_user_publish WHERE publish_type = 1 AND shop_id = _shop_id AND (want_sex = 0 OR want_sex =_SEX)
			AND user_id != _luck_draw_account_id
			AND	NOT EXISTS (SELECT * FROM app_lottery_log WHERE app_user_publish.user_id = app_lottery_log.publish_user_id 
			AND app_lottery_log.creatime_time  > DATE_SUB(NOW(), INTERVAL 240 MINUTE))	
			AND publish_status=1
			UNION ALL
			SELECT * FROM app_user_publish WHERE publish_type = 2 AND publish_status = 1 AND red_envelope_surplus_quantity > 0 
			AND shop_id = _shop_id AND only_available_nearby <= _is_inshop AND (want_sex = 0 OR want_sex =_SEX)
			AND user_id != _luck_draw_account_id
			AND	NOT EXISTS (SELECT * FROM app_user_red_envelope_log WHERE app_user_publish.publish_id = app_user_red_envelope_log.publish_id 
			AND app_user_red_envelope_log.user_id = _luck_draw_account_id)) t ORDER BY RAND() LIMIT 1;
			else
			
				SELECT publish_id INTO _GIFT_KEY FROM (
			SELECT * FROM app_user_publish WHERE publish_type = 1 AND shop_id = _shop_id AND (want_sex = 0 OR want_sex =_SEX)
			AND user_id != _luck_draw_account_id
			AND	NOT EXISTS (SELECT * FROM app_lottery_log WHERE app_user_publish.user_id = app_lottery_log.publish_user_id 
			AND app_lottery_log.creatime_time  > DATE_SUB(NOW(), INTERVAL 240 MINUTE))
			and publish_type = 1
			AND publish_status=1
			UNION ALL
			SELECT * FROM app_user_publish WHERE publish_type = 1 AND publish_status = 1 AND red_envelope_surplus_quantity > 0 
			AND shop_id = _shop_id AND only_available_nearby <= _is_inshop AND (want_sex = 0 OR want_sex =_SEX)
			AND user_id != _luck_draw_account_id
			AND	NOT EXISTS (SELECT * FROM app_user_red_envelope_log WHERE app_user_publish.publish_id = app_user_red_envelope_log.publish_id 
			AND app_user_red_envelope_log.user_id = _luck_draw_account_id)) t ORDER BY RAND() LIMIT 1;
			end if;
			
			
			IF   _GIFT_KEY > 0  THEN #有奖品 
				INSERT INTO app_lottery_log(shop_id,prize_type,prize_id,prize_status,publish_user_id,user_id,creatime_time,update_time)
				SELECT _shop_id,CASE publish_type WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE 5 END,
				publish_id,CASE publish_type WHEN 1 THEN 1 WHEN 2 THEN 0 ELSE 1 END,
				user_id,_luck_draw_account_id,NOW(),NOW() FROM app_user_publish where publish_id = _GIFT_KEY;
			ELSE #无奖品
				INSERT INTO app_lottery_log(shop_id,prize_type,prize_status,user_id,creatime_time,update_time)
				VALUES(_shop_id,5,1,_luck_draw_account_id,NOW(),NOW());
			END IF;
			
		ELSE
			#如果抽到过免费折扣奖励，随机抽，随机数除以2余0，抽免费折扣奖励
			
			SELECT coupon_id, coupon_quantity,
			CASE reset_cycle
			WHEN 1 THEN
			1440 - TIMESTAMPDIFF(MINUTE,last_reset_time,NOW())
			WHEN 2 THEN
			1440*7 - TIMESTAMPDIFF(MINUTE,last_reset_time,NOW())
			WHEN 3 THEN
			1440*7*30 - TIMESTAMPDIFF(MINUTE,last_reset_time,NOW())
			ELSE
			1440*7*365 - TIMESTAMPDIFF(MINUTE,last_reset_time,NOW())
			END INTO _GIFT_KEY,_NUMERATOR,_DENOMINATOR
			FROM (
			SELECT * FROM app_shop_coupon WHERE coupon_type = 1 AND shop_id = _shop_id AND coupon_quantity > 0  AND dr=0
			UNION ALL
			SELECT * FROM app_shop_coupon WHERE coupon_type = 2 AND shop_id = _shop_id AND coupon_quantity > 0  AND dr=0) t ORDER BY RAND() LIMIT 1;
			IF   _GIFT_KEY > 0 AND FLOOR(RAND()*100000) MOD (_DENOMINATOR/10*_FRIENDGIFTTIMES) < _NUMERATOR*_NOGIFTTIMES  THEN #有奖品
			#如果抽到过免费品未抽到折扣商品
				if _TODAY_1 >0 and _TODAY_2<1 then 
					INSERT INTO app_lottery_log(shop_id,prize_type,prize_id,prize_status,user_id,creatime_time,update_time)
				SELECT _shop_id,CASE coupon_type  WHEN 2 THEN 3 ELSE 5 END,
					 CASE coupon_type  WHEN 2 THEN coupon_id ELSE null END,CASE coupon_type  WHEN 2 THEN 0 ELSE 1 END,_luck_draw_account_id,NOW(),NOW() FROM app_shop_coupon where coupon_id = _GIFT_KEY;
		#如果抽到过折扣商品未抽到免费商品
		elseif _TODAY_2 >0 and _TODAY_1<1 then 
		INSERT INTO app_lottery_log(shop_id,prize_type,prize_id,prize_status,user_id,creatime_time,update_time)
				SELECT _shop_id,CASE coupon_type  WHEN 1 THEN 4 ELSE 5 END,
					 CASE coupon_type  WHEN 1 THEN coupon_id ELSE null END,CASE coupon_type  WHEN 1 THEN 0 ELSE 1 END,_luck_draw_account_id,NOW(),NOW() FROM app_shop_coupon where coupon_id = _GIFT_KEY;
	#如果折扣商品与免费商品均未抽到
	elseif _TODAY_2 <1 and _TODAY_1<1 then 
			INSERT INTO app_lottery_log(shop_id,prize_type,prize_id,prize_status,user_id,creatime_time,update_time)
				SELECT _shop_id,CASE coupon_type WHEN 1 THEN 4 WHEN 2 THEN 3 ELSE 5 END,
				coupon_id,0,_luck_draw_account_id,NOW(),NOW() FROM app_shop_coupon where coupon_id = _GIFT_KEY;
			else 
				INSERT INTO app_lottery_log(shop_id,prize_type,prize_status,user_id,creatime_time,update_time)
				VALUES(_shop_id,5,1,_luck_draw_account_id,NOW(),NOW());
		end if;
			ELSE #无奖品
				INSERT INTO app_lottery_log(shop_id,prize_type,prize_status,user_id,creatime_time,update_time)
				VALUES(_shop_id,5,1,_luck_draw_account_id,NOW(),NOW());
			END IF;	
			
		END IF;
	END IF;
	IF t_error = 1 THEN    
		ROLLBACK;   
		SELECT	-1;  
	ELSE    
		COMMIT;    
		SELECT max(log_id) from app_lottery_log where shop_id = _shop_id AND user_id = _luck_draw_account_id;
	END IF;  
END