网狐类代理详细统计脚本,给有需要的参考。
脚本内容:
------------------------------------------------------------------ ALTER PROC [dbo].[WSP_PM_GetAgentFinanceNew] @dwUserID INT AS --设置属性 SET NOCOUNT ON BEGIN --代理名字 DECLARE @agentname VARCHAR(31) SELECT @agentname= ISNULL(Accounts,'') FROM RYAccountsDB.dbo.AccountsInfo WHERE UserID IN (SELECT SpreaderID FROM RYAccountsDB.dbo.AccountsInfo WHERE UserID= @dwUserID) --B级代理人数 DECLARE @MyBPlayer BIGINT SELECT @MyBPlayer=COUNT(UserID) FROM RYAccountsDB.dbo.AccountsInfo WHERE SpreaderID= @dwUserID --C级代理人数 DECLARE @MyCPlayer BIGINT SELECT @MyCPlayer =COUNT(UserID) FROM RYAccountsDB.dbo.AccountsInfo WHERE SpreaderID IN (SELECT UserID FROM RYAccountsDB.dbo.AccountsInfo WHERE SpreaderID= @dwUserID ) --D级代理人数 DECLARE @MyDPlayer BIGINT SELECT @MyDPlayer =COUNT(UserID) FROM RYAccountsDB.dbo.AccountsInfo WHERE SpreaderID IN (SELECT UserID FROM RYAccountsDB.dbo.AccountsInfo WHERE SpreaderID IN (SELECT UserID FROM RYAccountsDB.dbo.AccountsInfo WHERE SpreaderID= @dwUserID )) -- 一/级/代/理/税/收 DECLARE @GameAgentRevenue DECIMAL(18,2) --获/取/所/有/的/一/级/代/理/返/利/信/息(当前代理直系玩家和当前代理本身) SELECT @GameAgentRevenue=ISNULL(SUM(AgentRevenue),0) FROM RecordUserRevenue WHERE UserID IN (SELECT UserID FROM RYAccountsDB.dbo.AccountsInfo WHERE SpreaderID= @dwUserID) AND AgentUserID=@dwUserID -- 二/级/代/理/税/收 DECLARE @SecondAgentRevenue DECIMAL(18,2) SELECT @SecondAgentRevenue=ISNULL(SUM(AgentRevenue),0) FROM RecordUserRevenue WHERE UserID IN (SELECT UserID FROM RYAccountsDB.dbo.AccountsInfo WHERE SpreaderID IN (SELECT UserID FROM RYAccountsDB.dbo.AccountsInfo WHERE SpreaderID = @dwUserID)) AND AgentUserID=@dwUserID -- 已/提/现/金/额 DECLARE @AgentOut DECIMAL(18,2) SELECT @AgentOut=ISNULL(-SUM(Score),0) FROM RecordAgentInfo WHERE UserID=@dwUserID AND TypeID=3 DECLARE @MyPlayer BIGINT SELECT @MyPlayer=COUNT(UserID) FROM RYAccountsDB.dbo.AccountsInfo WHERE SpreaderID = @dwUserID DECLARE @AgentRemain DECIMAL(18,2) SET @AgentRemain = @SecondAgentRevenue + @GameAgentRevenue - @AgentOut IF @MyPlayer IS NULL BEGIN SET @MyPlayer=0 END SELECT @MyPlayer AS MyPlayer, @GameAgentRevenue AS GameAgentRevenue, @SecondAgentRevenue AS SecondAgentRevenue, @AgentRemain AS AgentRemain, @agentname AS Agentname, @MyBPlayer as MyBplayer, @MyCPlayer as MyCplayer, @MyDPlayer as MyDplayer, @AgentOut as AgentOut END
END!!!
发表评论