Code
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-27
-- Description: 注册推广员
-- parameter:
-- @UID 用户名
-- @Key 激活码
-- @Email 邮箱
-- @RealName 真实姓名
-- @Add 详细地址
-- @ZipCode 邮编
-- @OutCheck
-- 0 执行成功
-- 255 执行期间发生错误
-- 1 邮箱重复
-- =============================================
alter PROCEDURE [dbo].[P_RegPromotion]
@UID bigint,
@Key varchar(32),
@Email varchar(100),
@RealName varchar(20),
@Add nvarchar(150),
@ZipCode int,
@IDCard varchar(18),
@OutCheck tinyint OUTPUT
AS
BEGIN
SET @OutCheck = 255
--插入推广员信息表
SET NOCOUNT ON
SET XACT_ABORT ON
IF EXISTS (SELECT 1 FROM [99UserDB]..T_PasswordSafe WHERE F_UID <> @UID and F_Email = @Email)
BEGIN
SET @OutCheck = 1 -- 邮箱重复,或已注册
RETURN
END
BEGIN TRANSACTION
INSERT INTO [T_promotion] (
F_PromotionID,
F_Key,
F_Date
)
VALUES (
@UID,
@Key,
getdate()
)
--修改联系信息
UPDATE [99UserDB]..T_PasswordSafe SET
F_Email = @Email,
F_RealName = @RealName,
F_Address = @Add,
F_ZipCode = @ZipCode,
F_IDCard = @IDCard
WHERE F_UID = @UID
SET @OutCheck = 0
COMMIT TRANSACTION
SET XACT_ABORT OFF
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-27
-- Description: 激活推广员
-- parameter:
-- @UID 用户名
-- @Key 激活码
-- @Email 邮箱
-- @OutCheck
-- 0 执行成功
-- 255 执行期间发生错误
-- 1 失效
--
-- =============================================
ALTER PROCEDURE [dbo].[P_UpdatePromotionState]
@UID bigint,
@Key varchar(32),
@OutCheck tinyint OUTPUT
AS
BEGIN
DECLARE @NowTime datetime
SET @NowTime = getdate()
SET @OutCheck =255
SET NOCOUNT ON
SET XACT_ABORT ON
IF EXISTS(
SELECT 1 FROM T_promotion
WHERE F_PromotionID=@UID AND F_Key = @Key and
F_State = 0 AND datediff(hh,F_Date,@NowTime)<=24
)
BEGIN
UPDATE T_promotion SET
F_State = 1,F_Date=getdate() WHERE F_PromotionID=@UID
SET @OutCheck =0
END
ELSE
BEGIN
DELETE FROM T_promotion WHERE F_State = 0 AND datediff(hh,F_Date,@NowTime)>24 --删除所有未激活超时的
SET @OutCheck =1
END
SET XACT_ABORT OFF
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-27
-- Description: 验证是否是推广员
-- parameter:
-- @UID 用户名
-- @OutCheck
-- 0 不是
-- 1 是
-- 2 是,未激活
--
-- =============================================
create procedure [dbo].[P_ViliDatePromotion]
@UID bigint,
@OutCheck tinyint output
AS
BEGIN
DECLARE @State tinyint
SET NOCOUNT ON
if exists(select 1 from T_promotion where F_PromotionID=@UID)
BEGIN
SELECT @State=F_State FROM T_promotion WHERE F_PromotionID = @UID
IF @State=0
begin
SET @OutCheck=2
end
else
begin
SET @OutCheck=1
end
END
ELSE
BEGIN
SET @OutCheck=0
END
SET NOCOUNT OFF
END
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-28
-- Description: 获取用户联系资料
-- parameter:
-- @UID 用户名
-- =============================================
CREATE PROCEDURE [dbo].[P_GetUserByContactInfo]
@UID bigint
AS
BEGIN
SET NOCOUNT ON
BEGIN
SELECT F_Email,F_RealName,F_Address,F_ZipCode,F_IDCard FROM [99UserDB]..T_PasswordSafe WHERE F_UID = @UID
END
SET NOCOUNT OFF
END
go
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-29
-- Description: 推广员成员活跃度按周统计统计,每周一统计一次
-- =============================================
alter PROCEDURE [dbo].[P_PromotionMemberWeekScore]
@Date datetime
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN
insert into T_Promotion_MemberWeek
select d.F_Player,sum(d.F_GameTime) as GameTime,(@Date-7) as Stardate,(@Date-1) as Enddate,@Date as Date from
[99GameDB]..T_Game_War3 as c join [99GameDB]..T_Player_War3 as d
on(c.F_GameID=d.F_GameID) where datediff(day,c.F_SaveTime,@Date)<=7
and datediff(day,c.F_SaveTime,@Date)>=1 and
d.F_Player in (
select b.F_FriendID from T_Promotion as a
join [99EventDB]..T_InvitLog as b ON(a.F_PromotionID=b.F_UID AND a.F_Date<=b.F_Date)
WHERE a.F_State=1
)
group by d.F_Player
--获取活跃用户上级推广员ID 并添加相应竞豆
--定义游标
declare @uid bigint
Declare S_Cursor CURSOR FOR
-- select e.F_UID from [99EventDB]..T_InvitLog as e
-- join T_Promotion_MemberWeek as f on(e.F_FriendID=f.F_PlayerID and datediff(day,f.F_Date,@Date)=0)
SELECT e.F_UID FROM T_Promotion_MemberWeek AS f
join [99EventDB]..T_InvitLog as e ON(f.F_PlayerID=e.F_FriendID)
WHERE datediff(day,f.F_Date,@Date)=0 and f.F_GameTime>36000
--创建游标
Open S_Cursor
--移动或提取列值
FETCH NEXT FROM S_Cursor
--利用循环处理游标中的列值
While @@Fetch_Status=0
Begin
FETCH FROM S_Cursor into @uid
-- print @uid
-- 赠送90竞豆
EXEC [99ScoreDB]..[P_UpdateScore] @uid, 90, 0, 0
--更新推广员竞豆收益
EXEC [dbo].[P_UpdatePromotionScore] @uid,90
UPDATE T_promotion SET F_ActiveCount = F_ActiveCount+ 1 WHERE F_PromotionID=@uid
End
--关闭/释放游标
Close S_Cursor
Deallocate S_Cursor
--统计活跃成员人数
DECLARE @PID bigint --推广员ID
DECLARE @ActivDate datetime --激活日期
DECLARE Member_Cursor CURSOR FOR
SELECT F_PromotionID,F_Date FROM T_promotion
OPEN Member_Cursor
FETCH NEXT FROM Member_Cursor
While @@Fetch_Status=0
BEGIN
FETCH FROM Member_Cursor into @PID,@ActivDate
EXEC [dbo].[P_PromotionMemberWeekCount] @PID,@ActivDate,@Date
END
CLOSE Member_Cursor
DEALLOCATE Member_Cursor
END
SET XACT_ABORT OFF
SET NOCOUNT OFF
END
go
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-29
-- Description: 根据推广员用户名获取线下成员数量(用于P_PromotionMemberWeekScore)
-- =============================================
alter PROCEDURE [dbo].[P_PromotionMemberWeekCount]
@UID bigint,
@ActivDate datetime, --激活日期
@Date datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE @OutCount INT --人头数
DECLARE @Scroe int --竞豆数
BEGIN
SELECT @OutCount = COUNT(1) FROM [99EventDB]..T_InvitLog WHERE F_UID = @UID
AND DATEDIFF(DAY,F_Date,@Date)<=7 and DATEDIFF(DAY,F_Date,@Date)<=1 and F_Date >=@ActivDate
IF @OutCount IS NULL OR @OutCount<30
BEGIN
if @OutCount IS NULL
begin
SET @OutCount=0
end
SET @Scroe=0
END
ELSE IF @OutCount>=30 AND @OutCount<50
BEGIN
SET @Scroe=3000
--EXEC [99ScoreDB]..[P_UpdateScore] @uid, 3000, 0, 0
END
ELSE IF @OutCount>=50 AND @OutCount<100
BEGIN
SET @Scroe=6000
END
ELSE
BEGIN
SET @Scroe=15000
END
IF @OutCount>30
BEGIN
--奖励竞豆
EXEC [99ScoreDB]..[P_UpdateScore] @UID, @Scroe, 0, 0
--记录推广员总收益
IF @Scroe>0
BEGIN
EXEC [dbo].[P_UpdatePromotionScore] @UID,@Scroe
END
END
--记录汇总
IF @OutCount>0
BEGIN
INSERT INTO T_PromotionWeekCount (
F_UID,
F_StarDate,
F_EndDate,
F_Count,
F_AddedScore
)
VALUES(
@UID,
@Date-7,
@Date-1,
@OutCount,
@Scroe
)
--更新邀请总人数
UPDATE T_promotion SET F_InviteCount = F_InviteCount+ @OutCount WHERE F_PromotionID=@UID
END
END
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-30
-- Description: 更新推广员所得竞豆总数(用于P_PromotionMemberWeekCount)
-- =============================================
CREATE PROCEDURE [dbo].[P_UpdatePromotionScore]
@UID bigint,
@Score int
AS
BEGIN
SET NOCOUNT ON
BEGIN
UPDATE T_promotion SET F_Score = F_Score+@Score where F_PromotionID = @UID
END
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-11-4
-- Description: 查看推广员排行 每周之星
-- =============================================
ALTER PROCEDURE [dbo].[P_GetPromotionRank]
@PageSize int --显示几条
AS
BEGIN
SET NOCOUNT ON
BEGIN
SELECT TOP(@PageSize) a.F_AddedScore,b.F_UID,b.F_Name,b.F_Nick,b.F_Face,b.F_Gender FROM T_PromotionWeekCount as a
join [99UserDB]..T_User as b ON(a.F_UID=b.F_UID)
where datediff(wk,a.F_StarDate,getdate())=1 order by a.F_AddedScore desc
END
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-11-4
-- Description: 推广员收益排行
-- =============================================
alter procedure [dbo].[P_GetPromotionScoreRank]
@PageSize int
AS
BEGIN
SET NOCOUNT ON
BEGIN
SELECT TOP(@PageSize) a.F_Score,b.F_UID,b.F_Name,b.F_Nick,b.F_Face,b.F_Gender FROM T_promotion as a
join [99UserDB]..T_User as b ON(a.F_PromotionID=b.F_UID) order by a.F_Score desc
END
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-30
-- Description: 按时间查看下线用户每周的游戏时间和用户的详细信息
-- =============================================
ALTER procedure [dbo].[P_GetPromotionMemberInfo]
@UID bigint,
@Page int,
@PageSize int,
@Count bit,
@OutCount int output
AS
BEGIN
DECLARE @ID bigint
SET NOCOUNT ON
BEGIN
IF @Page =1
BEGIN
select TOP(@PageSize) b.F_FriendID,b.F_Date,d.F_Name,d.F_Nick,d.F_Face,d.F_Gender,d.F_LastLogin from T_Promotion as a
join [99EventDB]..T_InvitLog as b ON(a.F_PromotionID=b.F_UID and a.F_Date<=b.F_Date)
join [99UserDB]..T_User as d ON(b.F_FriendID=d.F_UID)
where F_PromotionID=@UID ORDER BY b.F_FriendID DESC
END
ELSE
BEGIN
--取最后一个ID
select TOP((@Page-1)*@PageSize) @ID=b.F_FriendID from T_Promotion as a
join [99EventDB]..T_InvitLog as b ON(a.F_PromotionID=b.F_UID and a.F_Date<=b.F_Date)
join [99UserDB]..T_User as d ON(b.F_FriendID=d.F_UID)
where F_PromotionID=@UID ORDER BY b.F_FriendID DESC
--翻页
select TOP(@PageSize) b.F_FriendID,b.F_Date,d.F_Name,d.F_Nick,d.F_Face,d.F_Gender,d.F_LastLogin from T_Promotion as a
join [99EventDB]..T_InvitLog as b ON(a.F_PromotionID=b.F_UID and a.F_Date<=b.F_Date)
join [99UserDB]..T_User as d ON(b.F_FriendID=d.F_UID)
where F_PromotionID=@UID and b.F_FriendID<@ID ORDER BY b.F_FriendID DESC
END
--
IF @Count=1
BEGIN
select @OutCount = COUNT(1) from T_Promotion as a
join [99EventDB]..T_InvitLog as b ON(a.F_PromotionID=b.F_UID and a.F_Date<=b.F_Date)
join [99UserDB]..T_User as d ON(b.F_FriendID=d.F_UID)
where F_PromotionID=@UID
END
ELSE
BEGIN
SET @OutCount=-1
END
END
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-30
-- Description: 按时间查看自己每周收益(只能查看历史,不能查询本周)
-- =============================================
alter procedure [dbo].[P_GetPromotionWeekScore]
@UID bigint,
@Year int,
@Month int
AS
BEGIN
DECLARE @DefaultDate datetime
SET NOCOUNT ON
BEGIN
IF @Year=0 or @Month=0
BEGIN
SET @Year = YEAR(getdate())
SET @Month = MONTH(getdate())
END
SELECT * FROM T_PromotionWeekCount WHERE F_UID=@UID AND YEAR(F_StarDate)=@Year AND MONTH(F_StarDate)=@Month
END
SET NOCOUNT OFF
END
GO
-- =====================================================
-- author:luomingliang
-- create date:2008
-- description:获取本周玩家游戏时间
-- =====================================================
alter procedure [dbo].[P_GetPlayerWeekGametime]
@UID bigint
AS
BEGIN
SET NOCOUNT ON
IF EXISTS(
select 1 from
[99GameDB]..T_Game_War3 as c join [99GameDB]..T_Player_War3 as d
on(c.F_GameID=d.F_GameID and d.F_Player=@UID) where datediff(wk,c.F_SaveTime,getdate())=0
)
BEGIN
select d.F_Player, sum(d.F_GameTime) as GameTime from
[99GameDB]..T_Game_War3 as c join [99GameDB]..T_Player_War3 as d
on(c.F_GameID=d.F_GameID and d.F_Player=@UID) where datediff(wk,c.F_SaveTime,getdate())=0
group by d.F_Player
END
ELSE
BEGIN
select @UID as F_Player,0 as GameTime
END
SET NOCOUNT OFF
END
GO
-- =====================================================
-- author:luomingliang
-- create date:2008-11-4
-- description:获取推广员收益信息
-- =====================================================
alter procedure [dbo].[P_GetPromotionScore]
@UID bigint
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM T_promotion WHERE F_PromotionID=@UID
SET NOCOUNT OFF
END
go
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-11-5
-- Description: 获取活跃用户的信息
-- =============================================
alter procedure [dbo].[P_GetActiveInfo]
@ID bigint,
@UID bigint
AS
BEGIN
SET NOCOUNT ON
BEGIN
SELECT b.F_GameTime,c.F_Name,c.F_Nick,c.F_Face FROM T_PromotionWeekCount as a
join T_Promotion_MemberWeek as b ON(datediff(day,a.F_StarDate,b.F_StarDate)=0
And datediff(day,a.F_EndDate,b.F_EndDate)=0
and b.F_GameTime>=36000 )
join [99EventDB]..T_InvitLog as d ON(b.F_PlayerID=d.F_FriendID and d.F_UID=@UID)
join [99UserDB]..[T_User] as c ON(d.F_FriendID=c.F_UID) WHERE a.F_PCID=@ID and a.F_UID=@UID
END
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-27
-- Description: 注册推广员
-- parameter:
-- @UID 用户名
-- @Key 激活码
-- @Email 邮箱
-- @RealName 真实姓名
-- @Add 详细地址
-- @ZipCode 邮编
-- @OutCheck
-- 0 执行成功
-- 255 执行期间发生错误
-- 1 邮箱重复
-- =============================================
alter PROCEDURE [dbo].[P_RegPromotion]
@UID bigint,
@Key varchar(32),
@Email varchar(100),
@RealName varchar(20),
@Add nvarchar(150),
@ZipCode int,
@IDCard varchar(18),
@OutCheck tinyint OUTPUT
AS
BEGIN
SET @OutCheck = 255
--插入推广员信息表
SET NOCOUNT ON
SET XACT_ABORT ON
IF EXISTS (SELECT 1 FROM [99UserDB]..T_PasswordSafe WHERE F_UID <> @UID and F_Email = @Email)
BEGIN
SET @OutCheck = 1 -- 邮箱重复,或已注册
RETURN
END
BEGIN TRANSACTION
INSERT INTO [T_promotion] (
F_PromotionID,
F_Key,
F_Date
)
VALUES (
@UID,
@Key,
getdate()
)
--修改联系信息
UPDATE [99UserDB]..T_PasswordSafe SET
F_Email = @Email,
F_RealName = @RealName,
F_Address = @Add,
F_ZipCode = @ZipCode,
F_IDCard = @IDCard
WHERE F_UID = @UID
SET @OutCheck = 0
COMMIT TRANSACTION
SET XACT_ABORT OFF
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-27
-- Description: 激活推广员
-- parameter:
-- @UID 用户名
-- @Key 激活码
-- @Email 邮箱
-- @OutCheck
-- 0 执行成功
-- 255 执行期间发生错误
-- 1 失效
--
-- =============================================
ALTER PROCEDURE [dbo].[P_UpdatePromotionState]
@UID bigint,
@Key varchar(32),
@OutCheck tinyint OUTPUT
AS
BEGIN
DECLARE @NowTime datetime
SET @NowTime = getdate()
SET @OutCheck =255
SET NOCOUNT ON
SET XACT_ABORT ON
IF EXISTS(
SELECT 1 FROM T_promotion
WHERE F_PromotionID=@UID AND F_Key = @Key and
F_State = 0 AND datediff(hh,F_Date,@NowTime)<=24
)
BEGIN
UPDATE T_promotion SET
F_State = 1,F_Date=getdate() WHERE F_PromotionID=@UID
SET @OutCheck =0
END
ELSE
BEGIN
DELETE FROM T_promotion WHERE F_State = 0 AND datediff(hh,F_Date,@NowTime)>24 --删除所有未激活超时的
SET @OutCheck =1
END
SET XACT_ABORT OFF
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-27
-- Description: 验证是否是推广员
-- parameter:
-- @UID 用户名
-- @OutCheck
-- 0 不是
-- 1 是
-- 2 是,未激活
--
-- =============================================
create procedure [dbo].[P_ViliDatePromotion]
@UID bigint,
@OutCheck tinyint output
AS
BEGIN
DECLARE @State tinyint
SET NOCOUNT ON
if exists(select 1 from T_promotion where F_PromotionID=@UID)
BEGIN
SELECT @State=F_State FROM T_promotion WHERE F_PromotionID = @UID
IF @State=0
begin
SET @OutCheck=2
end
else
begin
SET @OutCheck=1
end
END
ELSE
BEGIN
SET @OutCheck=0
END
SET NOCOUNT OFF
END
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-28
-- Description: 获取用户联系资料
-- parameter:
-- @UID 用户名
-- =============================================
CREATE PROCEDURE [dbo].[P_GetUserByContactInfo]
@UID bigint
AS
BEGIN
SET NOCOUNT ON
BEGIN
SELECT F_Email,F_RealName,F_Address,F_ZipCode,F_IDCard FROM [99UserDB]..T_PasswordSafe WHERE F_UID = @UID
END
SET NOCOUNT OFF
END
go
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-29
-- Description: 推广员成员活跃度按周统计统计,每周一统计一次
-- =============================================
alter PROCEDURE [dbo].[P_PromotionMemberWeekScore]
@Date datetime
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN
insert into T_Promotion_MemberWeek
select d.F_Player,sum(d.F_GameTime) as GameTime,(@Date-7) as Stardate,(@Date-1) as Enddate,@Date as Date from
[99GameDB]..T_Game_War3 as c join [99GameDB]..T_Player_War3 as d
on(c.F_GameID=d.F_GameID) where datediff(day,c.F_SaveTime,@Date)<=7
and datediff(day,c.F_SaveTime,@Date)>=1 and
d.F_Player in (
select b.F_FriendID from T_Promotion as a
join [99EventDB]..T_InvitLog as b ON(a.F_PromotionID=b.F_UID AND a.F_Date<=b.F_Date)
WHERE a.F_State=1
)
group by d.F_Player
--获取活跃用户上级推广员ID 并添加相应竞豆
--定义游标
declare @uid bigint
Declare S_Cursor CURSOR FOR
-- select e.F_UID from [99EventDB]..T_InvitLog as e
-- join T_Promotion_MemberWeek as f on(e.F_FriendID=f.F_PlayerID and datediff(day,f.F_Date,@Date)=0)
SELECT e.F_UID FROM T_Promotion_MemberWeek AS f
join [99EventDB]..T_InvitLog as e ON(f.F_PlayerID=e.F_FriendID)
WHERE datediff(day,f.F_Date,@Date)=0 and f.F_GameTime>36000
--创建游标
Open S_Cursor
--移动或提取列值
FETCH NEXT FROM S_Cursor
--利用循环处理游标中的列值
While @@Fetch_Status=0
Begin
FETCH FROM S_Cursor into @uid
-- print @uid
-- 赠送90竞豆
EXEC [99ScoreDB]..[P_UpdateScore] @uid, 90, 0, 0
--更新推广员竞豆收益
EXEC [dbo].[P_UpdatePromotionScore] @uid,90
UPDATE T_promotion SET F_ActiveCount = F_ActiveCount+ 1 WHERE F_PromotionID=@uid
End
--关闭/释放游标
Close S_Cursor
Deallocate S_Cursor
--统计活跃成员人数
DECLARE @PID bigint --推广员ID
DECLARE @ActivDate datetime --激活日期
DECLARE Member_Cursor CURSOR FOR
SELECT F_PromotionID,F_Date FROM T_promotion
OPEN Member_Cursor
FETCH NEXT FROM Member_Cursor
While @@Fetch_Status=0
BEGIN
FETCH FROM Member_Cursor into @PID,@ActivDate
EXEC [dbo].[P_PromotionMemberWeekCount] @PID,@ActivDate,@Date
END
CLOSE Member_Cursor
DEALLOCATE Member_Cursor
END
SET XACT_ABORT OFF
SET NOCOUNT OFF
END
go
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-29
-- Description: 根据推广员用户名获取线下成员数量(用于P_PromotionMemberWeekScore)
-- =============================================
alter PROCEDURE [dbo].[P_PromotionMemberWeekCount]
@UID bigint,
@ActivDate datetime, --激活日期
@Date datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE @OutCount INT --人头数
DECLARE @Scroe int --竞豆数
BEGIN
SELECT @OutCount = COUNT(1) FROM [99EventDB]..T_InvitLog WHERE F_UID = @UID
AND DATEDIFF(DAY,F_Date,@Date)<=7 and DATEDIFF(DAY,F_Date,@Date)<=1 and F_Date >=@ActivDate
IF @OutCount IS NULL OR @OutCount<30
BEGIN
if @OutCount IS NULL
begin
SET @OutCount=0
end
SET @Scroe=0
END
ELSE IF @OutCount>=30 AND @OutCount<50
BEGIN
SET @Scroe=3000
--EXEC [99ScoreDB]..[P_UpdateScore] @uid, 3000, 0, 0
END
ELSE IF @OutCount>=50 AND @OutCount<100
BEGIN
SET @Scroe=6000
END
ELSE
BEGIN
SET @Scroe=15000
END
IF @OutCount>30
BEGIN
--奖励竞豆
EXEC [99ScoreDB]..[P_UpdateScore] @UID, @Scroe, 0, 0
--记录推广员总收益
IF @Scroe>0
BEGIN
EXEC [dbo].[P_UpdatePromotionScore] @UID,@Scroe
END
END
--记录汇总
IF @OutCount>0
BEGIN
INSERT INTO T_PromotionWeekCount (
F_UID,
F_StarDate,
F_EndDate,
F_Count,
F_AddedScore
)
VALUES(
@UID,
@Date-7,
@Date-1,
@OutCount,
@Scroe
)
--更新邀请总人数
UPDATE T_promotion SET F_InviteCount = F_InviteCount+ @OutCount WHERE F_PromotionID=@UID
END
END
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-30
-- Description: 更新推广员所得竞豆总数(用于P_PromotionMemberWeekCount)
-- =============================================
CREATE PROCEDURE [dbo].[P_UpdatePromotionScore]
@UID bigint,
@Score int
AS
BEGIN
SET NOCOUNT ON
BEGIN
UPDATE T_promotion SET F_Score = F_Score+@Score where F_PromotionID = @UID
END
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-11-4
-- Description: 查看推广员排行 每周之星
-- =============================================
ALTER PROCEDURE [dbo].[P_GetPromotionRank]
@PageSize int --显示几条
AS
BEGIN
SET NOCOUNT ON
BEGIN
SELECT TOP(@PageSize) a.F_AddedScore,b.F_UID,b.F_Name,b.F_Nick,b.F_Face,b.F_Gender FROM T_PromotionWeekCount as a
join [99UserDB]..T_User as b ON(a.F_UID=b.F_UID)
where datediff(wk,a.F_StarDate,getdate())=1 order by a.F_AddedScore desc
END
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-11-4
-- Description: 推广员收益排行
-- =============================================
alter procedure [dbo].[P_GetPromotionScoreRank]
@PageSize int
AS
BEGIN
SET NOCOUNT ON
BEGIN
SELECT TOP(@PageSize) a.F_Score,b.F_UID,b.F_Name,b.F_Nick,b.F_Face,b.F_Gender FROM T_promotion as a
join [99UserDB]..T_User as b ON(a.F_PromotionID=b.F_UID) order by a.F_Score desc
END
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-30
-- Description: 按时间查看下线用户每周的游戏时间和用户的详细信息
-- =============================================
ALTER procedure [dbo].[P_GetPromotionMemberInfo]
@UID bigint,
@Page int,
@PageSize int,
@Count bit,
@OutCount int output
AS
BEGIN
DECLARE @ID bigint
SET NOCOUNT ON
BEGIN
IF @Page =1
BEGIN
select TOP(@PageSize) b.F_FriendID,b.F_Date,d.F_Name,d.F_Nick,d.F_Face,d.F_Gender,d.F_LastLogin from T_Promotion as a
join [99EventDB]..T_InvitLog as b ON(a.F_PromotionID=b.F_UID and a.F_Date<=b.F_Date)
join [99UserDB]..T_User as d ON(b.F_FriendID=d.F_UID)
where F_PromotionID=@UID ORDER BY b.F_FriendID DESC
END
ELSE
BEGIN
--取最后一个ID
select TOP((@Page-1)*@PageSize) @ID=b.F_FriendID from T_Promotion as a
join [99EventDB]..T_InvitLog as b ON(a.F_PromotionID=b.F_UID and a.F_Date<=b.F_Date)
join [99UserDB]..T_User as d ON(b.F_FriendID=d.F_UID)
where F_PromotionID=@UID ORDER BY b.F_FriendID DESC
--翻页
select TOP(@PageSize) b.F_FriendID,b.F_Date,d.F_Name,d.F_Nick,d.F_Face,d.F_Gender,d.F_LastLogin from T_Promotion as a
join [99EventDB]..T_InvitLog as b ON(a.F_PromotionID=b.F_UID and a.F_Date<=b.F_Date)
join [99UserDB]..T_User as d ON(b.F_FriendID=d.F_UID)
where F_PromotionID=@UID and b.F_FriendID<@ID ORDER BY b.F_FriendID DESC
END
--
IF @Count=1
BEGIN
select @OutCount = COUNT(1) from T_Promotion as a
join [99EventDB]..T_InvitLog as b ON(a.F_PromotionID=b.F_UID and a.F_Date<=b.F_Date)
join [99UserDB]..T_User as d ON(b.F_FriendID=d.F_UID)
where F_PromotionID=@UID
END
ELSE
BEGIN
SET @OutCount=-1
END
END
SET NOCOUNT OFF
END
GO
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-10-30
-- Description: 按时间查看自己每周收益(只能查看历史,不能查询本周)
-- =============================================
alter procedure [dbo].[P_GetPromotionWeekScore]
@UID bigint,
@Year int,
@Month int
AS
BEGIN
DECLARE @DefaultDate datetime
SET NOCOUNT ON
BEGIN
IF @Year=0 or @Month=0
BEGIN
SET @Year = YEAR(getdate())
SET @Month = MONTH(getdate())
END
SELECT * FROM T_PromotionWeekCount WHERE F_UID=@UID AND YEAR(F_StarDate)=@Year AND MONTH(F_StarDate)=@Month
END
SET NOCOUNT OFF
END
GO
-- =====================================================
-- author:luomingliang
-- create date:2008
-- description:获取本周玩家游戏时间
-- =====================================================
alter procedure [dbo].[P_GetPlayerWeekGametime]
@UID bigint
AS
BEGIN
SET NOCOUNT ON
IF EXISTS(
select 1 from
[99GameDB]..T_Game_War3 as c join [99GameDB]..T_Player_War3 as d
on(c.F_GameID=d.F_GameID and d.F_Player=@UID) where datediff(wk,c.F_SaveTime,getdate())=0
)
BEGIN
select d.F_Player, sum(d.F_GameTime) as GameTime from
[99GameDB]..T_Game_War3 as c join [99GameDB]..T_Player_War3 as d
on(c.F_GameID=d.F_GameID and d.F_Player=@UID) where datediff(wk,c.F_SaveTime,getdate())=0
group by d.F_Player
END
ELSE
BEGIN
select @UID as F_Player,0 as GameTime
END
SET NOCOUNT OFF
END
GO
-- =====================================================
-- author:luomingliang
-- create date:2008-11-4
-- description:获取推广员收益信息
-- =====================================================
alter procedure [dbo].[P_GetPromotionScore]
@UID bigint
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM T_promotion WHERE F_PromotionID=@UID
SET NOCOUNT OFF
END
go
-- =============================================
-- Author: luomingliang
-- Create Date: 2008-11-5
-- Description: 获取活跃用户的信息
-- =============================================
alter procedure [dbo].[P_GetActiveInfo]
@ID bigint,
@UID bigint
AS
BEGIN
SET NOCOUNT ON
BEGIN
SELECT b.F_GameTime,c.F_Name,c.F_Nick,c.F_Face FROM T_PromotionWeekCount as a
join T_Promotion_MemberWeek as b ON(datediff(day,a.F_StarDate,b.F_StarDate)=0
And datediff(day,a.F_EndDate,b.F_EndDate)=0
and b.F_GameTime>=36000 )
join [99EventDB]..T_InvitLog as d ON(b.F_PlayerID=d.F_FriendID and d.F_UID=@UID)
join [99UserDB]..[T_User] as c ON(d.F_FriendID=c.F_UID) WHERE a.F_PCID=@ID and a.F_UID=@UID
END
SET NOCOUNT OFF
END