一个小抽奖程序处理

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



ALTER   PROCEDURE [dbo].[wishwall_init_prize_data]

as
/*
select * from dbo.wall_prize_basedatas

select * from dbo.wall_set_prizies

--exec [wishwall_init_prize_data]

select count(*) as count from wall_set_prizies

select count(prizetype) as count from wall_set_prizies GROUP BY prizetype

select * from dbo.wall_prize_infos


*/
    declare @prizetype        int
    declare @allcount        int
    declare @startdate        datetime
    declare @enddate        datetime
    declare @lucknumber        int
    declare @curdate        datetime
    declare @curallcount        int
    declare @everycount        int
    declare @time            int
   
    set @time=0

    start:

    truncate table wall_set_prizies

    set @time=@time+1
   
   
    DECLARE cur CURSOR FOR
    SELECT type,allcount,startdate,enddate
    FROM wall_prize_basedatas where type<>3
   
    OPEN cur
    FETCH next FROM cur INTO @prizetype, @allcount,    @startdate,    @enddate

 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        set @curdate=@startdate

        while @curdate<=@enddate
        begin
            select @curallcount = sum(edatecount) from wall_set_prizies where prizetype=@prizetype
            if @curallcount is null
            begin
                set @curallcount = 0
            end

            if @prizetype=1
            begin
                set @lucknumber = cast(500*rand() as int)
            end
            else
            begin
                set @lucknumber = cast(500*rand() as int)
            end

            if @curallcount<@allcount
            begin
                if @prizetype=1
                begin
                    select top 1 @everycount=ecount from
                    (select 2 as 'ecount' union select 3 as 'ecount' union select 1 as 'ecount')as temptable order by newid()
                end
                else
                begin
                    select top 1 @everycount=ecount from
                    (select 1 as 'ecount' union select 0 as 'ecount' union select 1 as 'ecount' union select 1 as 'ecount')as temptable
                    order by newid()
                end

                insert wall_set_prizies (prizetype,lucknumber,edatecount,prizedate,created_at)
                values(@prizetype,@lucknumber,@everycount,@curdate,getdate())
            end
            else
            begin
                insert wall_set_prizies (prizetype,lucknumber,edatecount,prizedate,created_at)
                values(@prizetype,@lucknumber,0,@curdate,getdate())
            end

            set @curdate = @curdate + 1
        end

        FETCH next FROM cur INTO @prizetype, @allcount,    @startdate,    @enddate
    END

    --***********************************************************************************
  
    DECLARE temp CURSOR FOR
    select sum(edatecount) as 'allcount',prizetype from dbo.wall_set_prizies group by prizetype
  
    OPEN temp
    FETCH next FROM temp INTO @allcount, @prizetype
   
    WHILE @@FETCH_STATUS = 0
    BEGIN
        if @prizetype=1
        begin
            if @allcount<>80
            begin
                truncate table wall_set_prizies
                CLOSE temp
                DEALLOCATE temp   

                CLOSE cur
                DEALLOCATE cur   
                goto start   
            end
        end
        else if @prizetype=2
        begin
            if @allcount<>20
            begin
                truncate table wall_set_prizies
                CLOSE temp
                DEALLOCATE temp   

                CLOSE cur
                DEALLOCATE cur   
                goto start   
            end
        end

        FETCH next FROM temp INTO @allcount, @prizetype
    end

    CLOSE temp
    DEALLOCATE temp   

    CLOSE cur
    DEALLOCATE cur               

    select @time '@time'



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

 

 

 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER  PROCEDURE [dbo].[wishwall_start_draw]
(
    @prizetype        int,
    @mo_content        nvarchar(50),
    @fetion_no        varchar(20),
    @current_date        varchar(21)
    --,@can_winning    int        --是否可以中奖    0不可以，1可以
)
as
/*
select * from dbo.wall_prize_basedatas

select * from dbo.wall_set_prizies

select * from dbo.wall_prize_infos

exec [wishwall_start_draw] 1, 'c', '644513457', '2008-12-18 16:06:03.0'

*/    declare @can_winning    int   
    declare @luckNumber int
    declare @randNumber int
    declare @isPrize    int    --0没中奖    1中奖
    declare @drawTime    int
    declare @prizeAllCount        int
    declare @curPrizeAllCount    int
    declare @curDayPrizeCount    int
    declare @everyDayPrizeCount    int

    set @can_winning = 0
    set @drawtime = 0
    set @isprize = 0

    if NOT EXISTS (SELECT fetion_no FROM wall_prize_infos
    WHERE convert(varchar(10),created_at,121)=convert(varchar(10),@current_date,121)
        and fetion_no=@fetion_no)
    begin
        --允许中奖总数
        select @prizeallcount = allcount from dbo.wall_prize_basedatas where type=@prizetype
        --已中奖数量
        select @curprizeallcount = count(fetion_no) from wall_prize_infos
            where prizetype=@prizetype and isprize=1
        --中奖名额少于限制总数
        if  @curprizeallcount < @prizeallcount
        begin
            set @can_winning=1
        end
        else
        begin
            set @can_winning=0
        end

        begin tran
            --当天充许的中奖号码
            select @luckNumber=lucknumber, @everyDayPrizeCount=edatecount from wall_set_prizies
            where prizetype=@prizetype and
            convert(varchar(10),prizedate,121)=convert(varchar(10),@current_date,121)

            --当天中奖数量
            select @curprizeallcount = count(fetion_no) from wall_prize_infos
            with (tablockx)    --锁定
                    where prizetype=@prizetype and isprize=1 and
                convert(varchar(10),created_at,121)=convert(varchar(10),@current_date,121)
    
            --当天还有中奖名额
            if @curprizeallcount < @everyDayPrizeCount
            begin
                set @can_winning=1
            end
            else
            begin
                set @can_winning=0
            end

            if @can_winning=1    --可以中奖
            begin
                set @randNumber = cast(500*rand() as int)
            end
            else
            begin
                reDraw:
                set @drawtime = @drawtime + 1
                set @randNumber = cast(500*rand() as int)
                if @randNumber = @luckNumber
                begin
                    goto reDraw
                end
            end

            if @randNumber = @luckNumber
            begin
                set @isprize=1
            end
            else
            begin
                set @isprize=0
            end
           
            insert into wall_prize_infos (fetion_no,mocontent,mtcontent,prizetype,isprize,lucknumber,created_at)
            values(@fetion_no,@mo_content,'',@prizetype,@isprize,@randNumber,cast(@current_date as datetime))
        commit

        print 'not exist'
    end
    else
    begin
        print 'exist'
    end

    SELECT @isprize as 'prizevalue'
    --SELECT @LUCKNUMBER 'lucknumber',@randnumber 'randnumber',@drawtime 'drawTime',@isprize 'isprize'
   


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

 

 

 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







ALTER   PROCEDURE [dbo].[wishwall_user_score]
(
    @promise_id        int,
    @recv_fetion    varchar(20)
)
as
/*
select * from dbo.wall_prize_basedatas

select * from dbo.wall_set_prizies

select * from dbo.wall_prize_infos

select * from promise where pid = 81

exec [wishwall_user_score] 81,'726534971'

select * from wall_user_scores

select * from dbo.wall_user_wish_relations

delete from wall_user_wish_relations


*/    declare @promiseContent    nvarchar(1000)
    -- status    0字条不存在    1查看者不正确 2查看者正确 3多次查询
    declare @status            int
    declare @send_fetion    varchar(20)
    declare @send_score_id    int
    declare @recv_score_id    int
    declare @recv_date        varchar(10)
   

    select top 1 @promiseContent = promiseContent from promise
    where pid = @promise_id
   
    if @promiseContent is null
    begin
        set @status = 0
    end
    else
    begin
        select top 1 @send_fetion = sendFetion ,@recv_date = convert(varchar(10),sendTime,121)
        from promise where pid=@promise_id and acceptFetion = @recv_fetion
        --查看者不正确
        if @send_fetion is null    --NOT EXISTS()
        begin
            set @status = 1   
        end
        else
        begin
            --第一次两者交互
            if NOT EXISTS(select top 1 id from dbo.wall_user_wish_relations
                where send_fetion=@send_fetion and recv_fetion=@recv_fetion)
            begin
                set @status = 2

                insert into wall_user_wish_relations (send_fetion, recv_fetion)
                values(@send_fetion, @recv_fetion)

                select top 1 @send_score_id = id from wall_user_scores
                    where fetion_no = @send_fetion

                select top 1 @recv_score_id = id from wall_user_scores
                    where fetion_no = @recv_fetion

                --发送者积分处理
                if @send_score_id is null
                begin
                    insert into wall_user_scores (fetion_no,send_score)
                    values(@send_fetion,1)
                end
                else
                begin
                    update wall_user_scores set send_score = send_score + 1
                    where id = @send_score_id
                end

                --查看者积分处理
                if @recv_score_id is null
                begin
                    insert into wall_user_scores (fetion_no,recv_score)
                    values(@recv_fetion, 1)
                end
                else
                begin
                    update wall_user_scores set recv_score = recv_score + 1
                    where id = @recv_score_id
                end
            end
            else
            begin
                set @status = 3
            end
        end
    end

    if @status not in (2,3)
    begin
        set @promiseContent = ''
    end

    select @status as 'status', @promiseContent as 'content',
        month(@recv_date) as 'month',year(@recv_date) as 'year',
        day(@recv_date) as 'day' ,@send_fetion as 'send_fetion'










GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO