198 lines
9.1 KiB
Transact-SQL
198 lines
9.1 KiB
Transact-SQL
USE [LosaGame]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[game_practice_rank_reward] Script Date: 11/08/2017 10:09:35 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[game_practice_rank_reward]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[game_practice_rank_reward]
|
|
GO
|
|
|
|
USE [LosaGame]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[game_practice_rank_reward] Script Date: 11/08/2017 10:09:35 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
--IF OBJECT_ID('dbo.game_practice_rank_reward','P') IS NOT NULL
|
|
--
|
|
-- DROP PROC dbo.game_practice_rank_reward
|
|
--
|
|
--GO
|
|
|
|
-- =============================================
|
|
-- Subject : dbo.game_practice_rank_reward
|
|
-- DBA :
|
|
-- DATE : 2015-06-17
|
|
-- Description : 훈련 데이터 생성
|
|
-- =============================================
|
|
|
|
|
|
CREATE PROCEDURE [dbo].[game_practice_rank_reward]
|
|
@RankAStart Int, @RankAEnd Int, @RankAMent Int, @RankAPeriad Int,
|
|
@RankAType01 Int, @RankACode01 Int, @RankACount01 Int,
|
|
@RankAType02 Int, @RankACode02 Int, @RankACount02 Int,
|
|
@RankAType03 Int, @RankACode03 Int, @RankACount03 Int,
|
|
@RankAType04 Int, @RankACode04 Int, @RankACount04 Int,
|
|
@RankAType05 Int, @RankACode05 Int, @RankACount05 Int,
|
|
@RankBStart Int, @RankBEnd Int, @RankBMent Int, @RankBPeriad Int,
|
|
@RankBType01 Int, @RankBCode01 Int, @RankBCount01 Int,
|
|
@RankBType02 Int, @RankBCode02 Int, @RankBCount02 Int,
|
|
@RankBType03 Int, @RankBCode03 Int, @RankBCount03 Int,
|
|
@RankBType04 Int, @RankBCode04 Int, @RankBCount04 Int,
|
|
@RankBType05 Int, @RankBCode05 Int, @RankBCount05 Int,
|
|
@RankCStart Int, @RankCEnd Int, @RankCMent Int, @RankCPeriad Int,
|
|
@RankCType01 Int, @RankCCode01 Int, @RankCCount01 Int,
|
|
@RankCType02 Int, @RankCCode02 Int, @RankCCount02 Int,
|
|
@RankCType03 Int, @RankCCode03 Int, @RankCCount03 Int,
|
|
@RankCType04 Int, @RankCCode04 Int, @RankCCount04 Int,
|
|
@RankCType05 Int, @RankCCode05 Int, @RankCCount05 Int,
|
|
@RankDStart Int, @RankDEnd Int, @RankDMent Int, @RankDPeriad Int,
|
|
@RankDType01 Int, @RankDCode01 Int, @RankDCount01 Int,
|
|
@RankDType02 Int, @RankDCode02 Int, @RankDCount02 Int,
|
|
@RankDType03 Int, @RankDCode03 Int, @RankDCount03 Int,
|
|
@RankDType04 Int, @RankDCode04 Int, @RankDCount04 Int,
|
|
@RankDType05 Int, @RankDCode05 Int, @RankDCount05 Int,
|
|
@RankDID varchar(20)
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @sendIDX int, @receiveIDX int
|
|
SET @sendIDX = (
|
|
SELECT accountIDX
|
|
FROM [LosaGame].dbo.userMemberDB WITH(NOLOCK)
|
|
WHERE nickName=@RankDID
|
|
)
|
|
|
|
IF (@sendIDX IS NULL)
|
|
BEGIN
|
|
SET @sendIDX = (
|
|
SELECT accountIDX
|
|
FROM [LosaGame].dbo.userMemberDB WITH(NOLOCK)
|
|
WHERE nickName='DeveloperK'
|
|
)
|
|
END
|
|
|
|
|
|
BEGIN TRAN
|
|
|
|
TRUNCATE TABLE [LosaGame].[dbo].[userPracticeDB_Ranking]
|
|
TRUNCATE TABLE [LosaGame].[dbo].[userPracticeDB_Present]
|
|
|
|
Declare @CurrentDate DateTime
|
|
declare @StartDate NVARCHAR(10), @EndDate NVARCHAR(10), @TableDate NVARCHAR(10)
|
|
SET @CurrentDate = GetDate()
|
|
SET @StartDate = CONVERT(NVARCHAR(10), @CurrentDate - 7, 23)
|
|
SET @EndDate = CONVERT(NVARCHAR(10), @CurrentDate, 23)
|
|
SET @TableDate = CONVERT(NVARCHAR(10), @CurrentDate , 112)
|
|
|
|
INSERT INTO [LosaGame].[dbo].[userPracticeDB_Present]
|
|
(
|
|
PresentValue, PresentType1, Code1 , Value1, PresentType2, Code2 , Value2, PresentType3, Code3 , Value3, PresentType4, Code4 , Value4, PresentType5, Code5 , Value5
|
|
)
|
|
VALUES
|
|
( 1, @RankAType01, @RankACode01, @RankACount01, @RankAType02, @RankACode02, @RankACount02, @RankAType03, @RankACode03, @RankACount03, @RankAType04, @RankACode04, @RankACount04, @RankAType05, @RankACode05, @RankACount05 ),
|
|
( 2, @RankBType01, @RankBCode01, @RankBCount01, @RankBType02, @RankBCode02, @RankBCount02, @RankBType03, @RankBCode03, @RankBCount03, @RankBType04, @RankBCode04, @RankBCount04, @RankBType05, @RankBCode05, @RankBCount05 ),
|
|
( 3, @RankCType01, @RankCCode01, @RankCCount01, @RankCType02, @RankCCode02, @RankCCount02, @RankCType03, @RankCCode03, @RankCCount03, @RankCType04, @RankCCode04, @RankCCount04, @RankCType05, @RankCCode05, @RankCCount05 ),
|
|
( 4, @RankDType01, @RankDCode01, @RankDCount01, @RankDType02, @RankDCode02, @RankDCount02, @RankDType03, @RankDCode03, @RankDCount03, @RankDType04, @RankDCode04, @RankDCount04, @RankDType05, @RankDCode05, @RankDCount05 )
|
|
|
|
|
|
INSERT INTO [LosaGame].[dbo].[userPracticeDB_Ranking]
|
|
(
|
|
StartDate, EndDate, AccountIDX, PracticeIDX, PracticeRank, sendIDX, value, msgType, limitDate
|
|
)
|
|
SELECT @StartDate, @EndDate, AccountIDX as AccountIDX, PracticeIDX AS PracticeIDX, PracticeRank AS PracticeRank, @sendIDX AS sendIDX
|
|
, CASE WHEN @RankAStart <= PracticeRank AND PracticeRank <= @RankAEnd THEN 1
|
|
WHEN @RankBStart <= PracticeRank AND PracticeRank <= @RankBEnd THEN 2
|
|
WHEN @RankCStart <= PracticeRank AND PracticeRank <= @RankCEnd THEN 3
|
|
WHEN @RankDStart <= PracticeRank AND PracticeRank <= @RankDEnd THEN 4
|
|
ELSE 0
|
|
END AS presentType
|
|
, CASE WHEN @RankAStart <= PracticeRank AND PracticeRank <= @RankAEnd THEN @RankAMent
|
|
WHEN @RankBStart <= PracticeRank AND PracticeRank <= @RankBEnd THEN @RankBMent
|
|
WHEN @RankCStart <= PracticeRank AND PracticeRank <= @RankCEnd THEN @RankCMent
|
|
WHEN @RankDStart <= PracticeRank AND PracticeRank <= @RankDEnd THEN @RankDMent
|
|
END AS msgType
|
|
, CASE WHEN @RankAStart <= PracticeRank AND PracticeRank <= @RankAEnd THEN GETDATE() + @RankAPeriad
|
|
WHEN @RankBStart <= PracticeRank AND PracticeRank <= @RankBEnd THEN GETDATE() + @RankBPeriad
|
|
WHEN @RankCStart <= PracticeRank AND PracticeRank <= @RankCEnd THEN GETDATE() + @RankCPeriad
|
|
WHEN @RankDStart <= PracticeRank AND PracticeRank <= @RankDEnd THEN GETDATE() + @RankDPeriad
|
|
END AS limitDate
|
|
FROM
|
|
(
|
|
SELECT [AccountIDX], RANK()over(PARTITION BY PracticeIDX ORDER BY PracticeTime, UpdateDate) AS PracticeRank, PracticeIDX, PracticeTime
|
|
FROM [LosaGame].[dbo].[userPracticeDB]
|
|
WHERE AccountIDX NOT IN (SELECT AccountIDX FROM [LosaGame].[dbo].[userMemberDB] WHERE limitType = 100 AND limitDate > Getdate())
|
|
AND AccountIDX IN (SELECT AccountIDX FROM [LosaGame].[dbo].[userMemberDB])
|
|
) PracticeDB
|
|
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
|
|
BEGIN
|
|
ROLLBACK TRAN
|
|
RETURN;
|
|
END
|
|
|
|
INSERT INTO dbo.userPresentDB
|
|
(
|
|
sendIDX, receiveIDX, presentType, value1, value2, value3, value4, msgType, flag, limitDate, regDate
|
|
)
|
|
SELECT sendIDX, AccountIDX, PresentType1, Code1, Value1, 0, 0, msgType, 0, limitDate, @CurrentDate FROM [LosaGame].[dbo].[userPracticeDB_Ranking] AS UPR
|
|
INNER JOIN [LosaGame].[dbo].[userPracticeDB_Present] AS UPP ON UPR.value = UPP.PresentValue
|
|
WHERE Code1 > 0
|
|
|
|
INSERT INTO dbo.userPresentDB
|
|
(
|
|
sendIDX, receiveIDX, presentType, value1, value2, value3, value4, msgType, flag, limitDate, regDate
|
|
)
|
|
SELECT sendIDX, AccountIDX, PresentType2, Code2, Value2, 0, 0, msgType, 0, limitDate, @CurrentDate FROM [LosaGame].[dbo].[userPracticeDB_Ranking] AS UPR
|
|
INNER JOIN [LosaGame].[dbo].[userPracticeDB_Present] AS UPP ON UPR.value = UPP.PresentValue
|
|
WHERE Code2 > 0
|
|
|
|
INSERT INTO dbo.userPresentDB
|
|
(
|
|
sendIDX, receiveIDX, presentType, value1, value2, value3, value4, msgType, flag, limitDate, regDate
|
|
)
|
|
SELECT sendIDX, AccountIDX, PresentType3, Code3, Value3, 0, 0, msgType, 0, limitDate, @CurrentDate FROM [LosaGame].[dbo].[userPracticeDB_Ranking] AS UPR
|
|
INNER JOIN [LosaGame].[dbo].[userPracticeDB_Present] AS UPP ON UPR.value = UPP.PresentValue
|
|
WHERE Code3 > 0
|
|
|
|
INSERT INTO dbo.userPresentDB
|
|
(
|
|
sendIDX, receiveIDX, presentType, value1, value2, value3, value4, msgType, flag, limitDate, regDate
|
|
)
|
|
SELECT sendIDX, AccountIDX, PresentType4, Code4, Value4, 0, 0, msgType, 0, limitDate, @CurrentDate FROM [LosaGame].[dbo].[userPracticeDB_Ranking] AS UPR
|
|
INNER JOIN [LosaGame].[dbo].[userPracticeDB_Present] AS UPP ON UPR.value = UPP.PresentValue
|
|
WHERE Code4 > 0
|
|
|
|
INSERT INTO dbo.userPresentDB
|
|
(
|
|
sendIDX, receiveIDX, presentType, value1, value2, value3, value4, msgType, flag, limitDate, regDate
|
|
)
|
|
SELECT sendIDX, AccountIDX, PresentType5, Code5, Value5, 0, 0, msgType, 0, limitDate, @CurrentDate FROM [LosaGame].[dbo].[userPracticeDB_Ranking] AS UPR
|
|
INNER JOIN [LosaGame].[dbo].[userPracticeDB_Present] AS UPP ON UPR.value = UPP.PresentValue
|
|
WHERE Code5 > 0
|
|
|
|
UPDATE dbo.userGameDB SET practice = 1
|
|
|
|
--------------------------------------------------------------------
|
|
-- 0. 프로시져 시작 할때 userGameDB 백업 하자.
|
|
--------------------------------------------------------------------
|
|
DECLARE @tblName varchar(256), @sql varchar(256)
|
|
SET @tblName = '[Backup].dbo.LosaGame_' + @TableDate + '_userPracticeDB_Ranking'
|
|
SET @sql = 'SELECT * INTO ' + @tblName + ' FROM [LosaGame].[dbo].[userPracticeDB_Ranking] AS UPR
|
|
INNER JOIN [LosaGame].[dbo].[userPracticeDB_Present] AS UPP ON UPR.value = UPP.PresentValue'
|
|
execute (@sql)
|
|
|
|
DECLARE @tblName1 varchar(256), @sqlo varchar(256)
|
|
SET @tblName1 = '[Backup].dbo.LosaGame_' + @TableDate + '_userPracticeDB'
|
|
SET @sqlo = 'SELECT * INTO ' + @tblName1 + ' FROM [LosaGame].[dbo].[userPracticeDB]'
|
|
execute (@sqlo)
|
|
|
|
TRUNCATE TABLE [LosaGame].[dbo].[userPracticeDB]
|
|
|
|
COMMIT TRAN
|
|
|
|
END
|