DROP PROCEDURE IF EXISTS `p_sendGold`; 

CREATE PROCEDURE p_sendGold()  

BEGIN  DECLARE done INT DEFAULT 0;

   DECLARE id INT;

   DECLARE num INT;

   DECLARE flag INT;

	 DECLARE accessory1 INT;

   DECLARE cur1 CURSOR FOR SELECT u.user_id,sum(p.num) as sum_num FROM user_info u,pay_his p where u.user_id=p.user_id and date(sub_time) = date_sub(curdate(),interval 1 day) GROUP BY p.user_id ORDER BY sum_num DESC;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  

   OPEN cur1;

		

	 set flag = 0;



   REPEAT      

      FETCH cur1 INTO id,num;  

      

     

      if(num>=1000&&num<2000) then

      	 set accessory1 = 7081;

      

      ELSEIF(num>=2000&&num<5000) then

      	 set accessory1 = 7082;				

      

      ELSEIF(num>=5000&&num<10000) then

      	 set accessory1 = 7083;				 

      

      ELSEIF(num>=10000&&num<20000) then

      	 set accessory1 = 7084;				

      

      ELSEIF(num>=20000&&num<50000) then

      	 set accessory1 = 7085;				

      

      ELSEIF(num>=50000&&num<100000) then

      	 set accessory1 = 7086;				

      

      ELSEIF(num>=100000) then

      	 set accessory1 = 7087; 

			ELSE

				set accessory1 = 0;

			end IF;

			

			IF(accessory1>0) then

				insert into email_info(send_user_id,get_user_id,title,content,mail_type,send_time,readed,accessory1,accessory2,accessory3,accessory4,accessory1_num,accessory2_num,accessory3_num,accessory4_num,order_code,is_del) values(0,id,'系統(tǒng)郵件','充值獎(jiǎng)勵(lì)',0,now(),0,accessory1,0,0,0,1,0,0,0,0,0);     

			end IF;

		#set flag =flag+1;#記錄循環(huán)次數(shù)

   UNTIL done=1 END REPEAT;

		

	 

	 #SELECT flag,num,id,accessory1;



   CLOSE cur1;

#不知道為什么上面循環(huán)最好一條記錄老是插入兩條記錄，所以這里刪除一條。

	SELECT max(email_id) into id from email_info where content='充值獎(jiǎng)勵(lì)';

	delete from email_info where email_id=id ;

	

  END