网狐类级差日结数据库脚本，目标数据库表RYTreasureDB，以供参考！
脚本内容：
USE [msdb]
GO
/****** Object: Job 级差日结 Script Date: 02/28/2020 12:13:44 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 02/28/2020 12:13:45 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'级差日结',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'缉查',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [step] Script Date: 02/28/2020 12:13:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE RYTreasureDB
go
DECLARE @PreStartDate DATETIME,@PreEndDate DATETIME;
SET @PreStartDate = CONVERT(VARCHAR(10),DATEADD(DAY,-1,GETDATE()),120)
SET @PreEndDate= CONVERT(VARCHAR(10),GETDATE(),120)
--昨天日期
DECLARE @PreWeekDate INT;
SET @PreWeekDate = CONVERT(VARCHAR(8),DATEADD(DAY,-1,GETDATE()),112);
--SELECT @PreWeekDate
IF EXISTS(SELECT 1 FROM RYTreasureDB..RecordAgentRevenue_QM WHERE TimeKey= @PreWeekDate)
BEGIN
RETURN;
END
DECLARE @TodayKey INT;
SET @TodayKey = CAST(CONVERT(VARCHAR(13), GETDATE(), 112) AS INT);
BEGIN TRAN
BEGIN TRY
IF OBJECT_ID(''tempdb.dbo.#AgentRevenue_QM'') IS NOT NULL
BEGIN
DROP TABLE tempdb.dbo.#AgentRevenue_QM
END
SELECT ROW_NUMBER() OVER ( ORDER BY ai.SpreaderID ) AS Num ,
aa.UserID,ai.SpreaderID AS ParentUserID
INTO #AgentRevenue_QM
FROM RYAccountsDB..AccountsAgent AS aa
LEFT JOIN RYAccountsDB..AccountsInfo AS ai
ON aa.UserID = ai.UserID
WHERE aa.Nullity = 0;
--SELECT ROW_NUMBER() OVER ( ORDER BY aa.UserID DESC ) AS Num,aa.UserID,aa.SpreaderID AS ParentUserID
--INTO #AgentRevenue_QM
--FROM RYAccountsDB..AccountsInfo AS aa WHERE aa.IsAndroid=0 AND aa.Nullity = 0;
DECLARE @AgentCount INT;
SELECT @AgentCount = COUNT(1)
FROM #AgentRevenue_QM;
IF @AgentCount <= 0
BEGIN
RETURN;
END
DECLARE @num INT;
SET @num = @AgentCount;
WHILE @num>0
BEGIN
DECLARE @AgentUserID INT,@ParentUserID INT;
SET @AgentUserID=NULL;
SET @ParentUserID=NULL;
SELECT @AgentUserID=UserID,@ParentUserID=ParentUserID FROM #AgentRevenue_QM WHERE Num = @num;
IF @ParentUserID IS NULL
BEGIN
SET @ParentUserID = 0;
END
--获取直属会员业绩
DECLARE @PlayerScore DECIMAL(18,2);
SET @PlayerScore= NULL;
SELECT @PlayerScore=SUM(ABS(CASE WHEN ISNULL(TotalAdd,0)=0 THEN ABS(Score) ELSE TotalAdd END)) FROM RYTreasureDB..RecordDrawScore(NOLOCK) AS rds
WHERE rds.InsertTime BETWEEN @PreStartDate AND @PreEndDate AND EXISTS(SELECT 1 FROM RYAccountsDB..AccountsInfo(NOLOCK) AS ai WHERE ai.SpreaderID = @AgentUserID AND ai.UserID=rds.UserID)
IF @PlayerScore IS NULL
BEGIN
SET @PlayerScore = 0;
END
SET @PlayerScore = @PlayerScore/100;
INSERT INTO dbo.RecordAgentRevenue_QM
( TodayKey ,TimeKey ,AgentUserID,ParentUserID ,AgentLevel ,
ChildPlayerScore ,ChildAgentScore ,ChildPlayerMoney ,ChildAgentMoney ,
TotalMoney ,MyMoney ,
RecordDate
)
VALUES (@TodayKey,@PreWeekDate,@AgentUserID,@ParentUserID,0,
@PlayerScore,0,0,0,
0,0,GETDATE()
)
SET @num=@num-1;
END
--计算下级推广员业绩
SET @num = @AgentCount;
WHILE @num>0
BEGIN
DECLARE @AgentUserID2 INT;
SET @AgentUserID2 = NULL;
SELECT @AgentUserID2=UserID FROM #AgentRevenue_QM WHERE Num = @num;
DECLARE @ChildAgentScore DECIMAL(18,2);
SET @ChildAgentScore = NULL;
SELECT @ChildAgentScore=SUM(ChildPlayerScore+ChildAgentScore) FROM dbo.RecordAgentRevenue_QM(NOLOCK) WHERE ParentUserID=@AgentUserID2 AND TimeKey=@PreWeekDate;
IF @ChildAgentScore IS NULL
BEGIN
SET @ChildAgentScore = 0;
END
UPDATE dbo.RecordAgentRevenue_QM SET ChildAgentScore = @ChildAgentScore WHERE AgentUserID=@AgentUserID2 AND TimeKey = @PreWeekDate;
SET @num=@num-1;
END
IF OBJECT_ID(''tempdb.dbo.#AgentRevenue_QM_calc'') IS NOT NULL
BEGIN
DROP TABLE tempdb.dbo.#AgentRevenue_QM_calc
END
SELECT * INTO #AgentRevenue_QM_calc FROM dbo.RecordAgentRevenue_QM WHERE TimeKey = @PreWeekDate
--计算总业
SET @num = @AgentCount;
WHILE @num>0
BEGIN
DECLARE @AgentUserID3 INT;
SET @AgentUserID3 = NULL;
SELECT @AgentUserID3=UserID FROM #AgentRevenue_QM WHERE Num = @num;
DECLARE @ChildAgentTotalScore DECIMAL(18,2);
SET @ChildAgentTotalScore = NULL;
SELECT @ChildAgentTotalScore=SUM(ChildPlayerScore+ChildAgentScore) FROM tempdb.dbo.#AgentRevenue_QM_calc(NOLOCK) WHERE ParentUserID=@AgentUserID3 AND TimeKey=@PreWeekDate;
IF @ChildAgentTotalScore IS NULL
BEGIN
SET @ChildAgentTotalScore = 0;
END
UPDATE dbo.RecordAgentRevenue_QM SET ChildAgentScore = @ChildAgentTotalScore WHERE AgentUserID=@AgentUserID3 AND TimeKey = @PreWeekDate;
DECLARE @@ChildPlayerScore DECIMAL(18,2),@@ChildAgentScore DECIMAL(18,2);
SET @@ChildPlayerScore = NULL;
SET @@ChildAgentScore = NULL;
SELECT @@ChildPlayerScore=ChildPlayerScore,@@ChildAgentScore=ChildAgentScore FROM dbo.RecordAgentRevenue_QM(NOLOCK) WHERE AgentUserID=@AgentUserID3 AND TimeKey = @PreWeekDate;
--计算等级
DECLARE @AgentLevel INT,@BaodiAgentLevel INT,@ReturnMoney DECIMAL(18,2),@ChildPlayerMoney DECIMAL(18,3),@ChildAgentMoney DECIMAL(18,3),@TotalMoney DECIMAL(18,3),@MyMoney DECIMAL(18,3);
SET @AgentLevel = NULL;
SET @BaodiAgentLevel = NULL;
SET @ReturnMoney = NULL;
SET @ChildPlayerMoney = NULL;
SET @ChildAgentMoney = NULL;
SET @TotalMoney = NULL;
SET @MyMoney = NULL;
SET @AgentLevel = NULL;
SELECT @BaodiAgentLevel=Level FROM RYAccountsDB..AgentLevel_JF WHERE UserID = @AgentUserID3;
IF @BaodiAgentLevel IS NULL
BEGIN
SET @BaodiAgentLevel =0;
END
SELECT @AgentLevel=Level FROM RYAccountsDB..AgentLevel_QM(NOLOCK) WHERE (@@ChildPlayerScore+@@ChildAgentScore) BETWEEN MinScore AND MaxScore-1 ORDER BY Level DESC;
IF @AgentLevel IS NULL
BEGIN
SET @AgentLevel =0;
END
SET @AgentLevel = CASE WHEN @AgentLevel>@BaodiAgentLevel THEN @AgentLevel ELSE @BaodiAgentLevel END;
SELECT @ReturnMoney=ReturnMoney FROM RYAccountsDB..AgentLevel_QM WHERE Level=@AgentLevel;
--直属会员佣
SET @ChildPlayerMoney = ISNULL(@@ChildPlayerScore,0)/10000;
SET @ChildPlayerMoney = @ChildPlayerMoney * ISNULL(@ReturnMoney,0);
--下级推广员佣
SET @ChildAgentMoney = ISNULL(@@ChildAgentScore,0)/10000;
DECLARE @ChildAgentReturnMoney DECIMAL(18,2);
SET @ChildAgentReturnMoney = NULL;
SELECT @ChildAgentReturnMoney=ReturnMoney FROM RYAccountsDB..AgentLevel_QM(NOLOCK) WHERE @@ChildAgentScore BETWEEN MinScore AND MaxScore-1 ORDER BY Level DESC;
IF @ChildAgentReturnMoney IS NULL
BEGIN
SET @ChildAgentReturnMoney = 0;
END
SET @ChildAgentMoney = ISNULL(@ChildAgentMoney,0) * ISNULL(@ChildAgentReturnMoney,0);
--获取所有下级计算差
IF OBJECT_ID(''tempdb.dbo.#TMP_Child'') IS NOT NULL
BEGIN
DROP TABLE tempdb.dbo.#TMP_Child
END
select ROW_NUMBER() over(order by AgentUserID )as Num,* into #TMP_Child from tempdb.dbo.#AgentRevenue_QM_calc(NOLOCK) WHERE ParentUserID=@AgentUserID3;
declare @ChildCount int,@ChildIndex int,@C_Total_AgentMoney decimal(18,2);
set @C_Total_AgentMoney = 0;
set @ChildIndex = 1;
select @ChildCount=COUNT(1) from #TMP_Child;
while(@ChildCount>=@ChildIndex)
begin
declare @C_Score decimal(18,2),@C_Return int,@CC_UserID int,@CC_Level int;
SET @C_Score=NULL;
SET @C_Return=NULL;
SET @CC_UserID=NULL;
SET @CC_Level=NULL;
select @C_Score=ISNULL(tc.ChildAgentScore,0)+ISNULL(tc.ChildPlayerScore,0),@CC_UserID=AgentUserID from tempdb.dbo.#TMP_Child(NOLOCK) as tc where tc.Num=@ChildIndex;
SELECT @CC_Level=Level FROM RYAccountsDB..AgentLevel_JF WHERE UserID = @CC_UserID;
IF @CC_Level IS NULL
BEGIN
SELECT @CC_Level=Level,@C_Return=ReturnMoney FROM RYAccountsDB..AgentLevel_QM(NOLOCK) WHERE (@C_Score) BETWEEN MinScore AND MaxScore-1 ORDER BY Level DESC;
END
ELSE
BEGIN
SELECT @C_Return=ReturnMoney FROM RYAccountsDB..AgentLevel_QM WHERE Level=@CC_Level;
END
declare @tmp_Return int;
set @tmp_Return = @ReturnMoney-@C_Return;
if @tmp_Return<=0
begin
set @ChildIndex = @ChildIndex+1;
continue;
end
set @C_Total_AgentMoney = @C_Total_AgentMoney+(@C_Score/10000*@tmp_Return)
set @ChildIndex = @ChildIndex+1;
END
IF @C_Total_AgentMoney IS NULL
BEGIN
SET @C_Total_AgentMoney = 0;
END
--总佣
SET @TotalMoney = (ISNULL(@@ChildPlayerScore,0))/10000;
SET @TotalMoney = @TotalMoney * ISNULL(@ReturnMoney,0);
set @TotalMoney=@TotalMoney+@C_Total_AgentMoney;
IF @TotalMoney>0
BEGIN
PRINT @TotalMoney;
END
--本周我的佣
SET @MyMoney = @TotalMoney;
IF @TotalMoney IS NULL
BEGIN
SET @TotalMoney =0;
END
IF @MyMoney IS NULL
BEGIN
SET @MyMoney= 0;
END
UPDATE dbo.RecordAgentRevenue_QM SET AgentLevel = @AgentLevel,ChildPlayerMoney = @ChildPlayerMoney,ChildAgentMoney=@C_Total_AgentMoney,TotalMoney=@TotalMoney,MyMoney=@MyMoney
WHERE AgentUserID=@AgentUserID3 AND TimeKey = @PreWeekDate;
UPDATE RYAccountsDB..AccountBalance_QM SET Balance = Balance+ISNULL(@MyMoney,0) WHERE UserID = @AgentUserID3;
UPDATE RYAccountsDB..AccountBalance_QM SET Balance = Balance-PreWithdraw WHERE UserID = @AgentUserID3;
UPDATE RYAccountsDB..AccountBalance_QM SET PreWithdraw = 0 WHERE UserID = @AgentUserID3;
IF @@ROWCOUNT =0
BEGIN
INSERT INTO RYAccountsDB..AccountBalance_QM(UserID,Balance) VALUES(@AgentUserID3,@MyMoney);
END
SET @num=@num-1;
END
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH',
@database_name=N'RYTreasureDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'plan',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20200113,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'9063315d-1e8d-49ad-ae3a-53a138428c3d'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GOEND！！！
13131
发表评论