86 lines
2.5 KiB
Transact-SQL
86 lines
2.5 KiB
Transact-SQL
USE [LosaGame]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[game_PCBangPlayTime_Reward] Script Date: 2018-01-12 오전 10:31:38 ******/
|
|
DROP PROCEDURE [dbo].[game_PCBangPlayTime_Reward]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[game_PCBangPlayTime_Reward] Script Date: 2018-01-12 오전 10:31:38 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
--IF OBJECT_ID('dbo.game_PCBangPlayTime_Reward','P') IS NOT NULL
|
|
--
|
|
-- DROP PROC dbo.game_PCBangPlayTime_Reward
|
|
--
|
|
--GO
|
|
|
|
-- =============================================
|
|
-- Subject : dbo.game_PCBangPlayTime_Reward
|
|
-- DBA :
|
|
-- DATE : 2015-06-17
|
|
-- Description : 훈련 데이터 생성
|
|
-- =============================================
|
|
|
|
|
|
CREATE PROCEDURE [dbo].[game_PCBangPlayTime_Reward]
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @LimitTime INT = 30
|
|
DECLARE @DateTime NVARCHAR(10) = CONVERT(NVARCHAR(10), GetDate()-1, 112)
|
|
|
|
DECLARE @sendIDX int, @receiveIDX int
|
|
SET @sendIDX = (
|
|
SELECT accountIDX
|
|
FROM [LosaGame].dbo.userMemberDB WITH(NOLOCK)
|
|
WHERE nickName='개발자K'
|
|
)
|
|
|
|
IF (@sendIDX IS NULL)
|
|
BEGIN
|
|
SET @sendIDX = (
|
|
SELECT accountIDX
|
|
FROM [LosaGame].dbo.userMemberDB WITH(NOLOCK)
|
|
WHERE nickName='DeveloperK'
|
|
)
|
|
END
|
|
|
|
INSERT INTO LosaGame.dbo.userPCBangPregentDB(PCBangDate, AccountIDX, PCBangCount, PCBangBonusCash)
|
|
SELECT PCBangDate, AccountIDX, max(PCBangIndexCount.PCBangCount), max(presentGold)
|
|
FROM LosaGame.dbo.userPCBangPlayTimeDB AS PPT
|
|
INNER JOIN ( SELECT PCBangIndex, count(PCBangIndex) AS PCBangCount, CASE WHEN count(PCBangIndex) >= 5 THEN 1250
|
|
WHEN count(PCBangIndex) >= 4 THEN 750
|
|
WHEN count(PCBangIndex) >= 3 THEN 450
|
|
WHEN count(PCBangIndex) >= 2 THEN 250
|
|
ELSE 100
|
|
END AS presentGold FROM LosaGame.dbo.userPCBangPlayTimeDB
|
|
WHERE AccountIDX NOT IN (SELECT AccountIDX FROM [LosaGame].[dbo].[userMemberDB] WHERE limitType = 100 AND limitDate > Getdate())
|
|
AND PCBangPlayTime >= 60 AND PCBangDate = @DateTime
|
|
GROUP by PCBangDate, PCBangIndex) AS PCBangIndexCount
|
|
ON PCBangIndexCount.PCBangIndex = PPT.PCBangIndex
|
|
WHERE PCBangDate = @DateTime
|
|
Group By PCBangDate, PPT.AccountIDX
|
|
|
|
INSERT INTO LosaGame.dbo.userPresentDB
|
|
(
|
|
sendIDX, receiveIDX, presentType, value1, value2, value3, value4, msgType, flag, limitDate, regDate
|
|
)
|
|
SELECT @sendIDX, AccountIDX, 13, PCBangBonusCash, 72, 0, 0, 1132, 0, GETDATE() + 3, Getdate()
|
|
FROM LosaGame.dbo.userPCBangPregentDB
|
|
WHERE PCBangDate = @DateTime
|
|
|
|
|
|
END
|
|
|
|
GO
|
|
|
|
|