作业调用的游标 --作者: 2006-03-09 --用途:每天晚上执行,对所有未赠送积分的订单进行赠送积分 --修改 07-03-12 添加的积分激活 --修改 07-03-13 注册验证积分激活限制在3个月 --修改 07-03-14 注册验证积分07-03--15前不受3个月限制 CREATE PROCEDURE [dbo].[GetEtOrderScore] AS declare @get_id int, --获取积分记录表ID @pkid int, --机票订单ID @dicsountPrice int, --订单优惠金额 @ticket_type smallint, --机票类型 @t_id int, --会员ID @source int, --消费平台 @sailType smallint, --航程类型 @memberLevel int --会员级别 begin --循环找出所有可赠送积分的机票订单 DECLARE cur_order CURSOR FOR select et.pkid,et.Discount_Price,et.Ticket_Type,et.T_ID,left(et.Order_Source,3) Order_Source,et.Sail_Type ,cm.Client_Rank from et_order et join cms_member cm on et.t_id=cm.t_id where --原sql is_return <> 1 --没有退票,部分退票 --原sql and (Status = 11 or Status = 20) --已退票 ,完成 status = 20 and (Sail_Type =1 or Sail_Type = 4) and (DATEDIFF(day,Dep_Time,getdate()) >=0) --当天起飞 and et.get_score = 0 OPEN cur_order FETCH NEXT FROM cur_order into @pkid,@dicsountPrice,@ticket_type,@t_id,@source,@sailType,@memberLevel WHILE @@FETCH_STATUS = 0 BEGIN print @pkid if ( exists(select 1 from score_get where GET_WORKFLOW_ID = 1003 and GET_ORDERID = @pkid ) ) begin --1.获取积分记录表中的记录ID select @get_id = GET_ID from score_get where GET_WORKFLOW_ID = 1003 and GET_ORDERID = @pkid and get_status <>-1--部分退票已经失效的积分不能被激活 --2.更新积分记录表的状态为已激活,添加备注 update score_get set GET_STATUS = 1, GET_REMARK = '系统自动确认已赠送积分 <BR>' + ISNULL(GET_REMARK,'') where GET_ID = @get_id --3.记录积分日志SCORE_LOG 先不做 --4.更新Et_Order表的get_score为1 已获得 update et_order set get_score = 1 where pkid = @pkid end --因有效消费,相关积分被激活(07-03-12) --注册验证积分激活,有三个月激活限制 begin --07-03-15之后 update score_get set GET_STATUS = 1, GET_REMARK = '系统自动确认已赠送积分 <BR>' + ISNULL(GET_REMARK,'') where GET_T_ID = @t_id AND DATEADD(mm,3,GET_START_DATE) > getdate() AND (GET_WORKFLOW_ID = 1001 OR GET_WORKFLOW_ID = 1002) AND GET_STATUS = 0 AND GET_START_DATE >= '2007-03-15 00:00:00' --限制在3个月 --07-03-15之前 update score_get set GET_STATUS = 1, GET_REMARK = '系统自动确认已赠送积分 <BR>' + ISNULL(GET_REMARK,'') where GET_T_ID = @t_id AND (GET_WORKFLOW_ID = 1001 OR GET_WORKFLOW_ID = 1002) AND GET_STATUS = 0 AND GET_START_DATE < '2007-03-15 00:00:00' --不受限制在3个月限制 end --推荐人积分激活 DECLARE @GetScore_ID int DECLARE @RecommendGetScoreID int DECLARE @History_ID int if ( exists(select 1 from SCORE_RECOMMEND where RECOMMEND_T_id = @t_id )) begin SELECT top 1 @GetScore_ID = RECOMMEND_GetScore_ID, @RecommendGetScoreID = RECOMMEND_RecommendGetScoreID, @History_ID = RECOMMEND_History_ID from SCORE_RECOMMEND where RECOMMEND_T_id = @t_id if ( @RecommendGetScoreID is not null) begin update score_get set GET_STATUS = 1, GET_REMARK = '系统自动确认已赠送积分 <BR>' + ISNULL(GET_REMARK,'') where GET_ID = @RecommendGetScoreID end --print ( convert(varchar(1000),@RecommendGetScoreID)) if ( @History_ID is not null) begin update SCORE_RECOMMEND_HISTORY set History_ScoreState = 1, --推荐积分状态True:激活 History_STATE = 2, --被推荐用户的状态2.有效消费会员 History_UpdateTime = getdate() --最后更新的日期 where History_ID = @History_ID AND History_ScoreState = 0 end end FETCH NEXT FROM cur_order into @pkid,@dicsountPrice,@ticket_type,@t_id,@source,@sailType,@memberLevel --游标下移,继续处理 END CLOSE cur_order DEALLOCATE cur_order end