USE [Pangya_S4_TH]
GO
/****** Object: StoredProcedure [dbo].[USP_GUILD_MEMBER_LIST] Script Date: 02/02/2011 02:40:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[USP_GUILD_MEMBER_LIST] (
@UID INT = 0
, @CARD_TYPE TINYINT = 9 -- add : 2009-06-04
, @CARD_TYPEID INT = 0
, @QTY INT = 0
, @TYPES TINYINT = 1
, @OUTPUT INT = -1
, @RESULT INT = 0 OUTPUT
, @OUT_CARD_ITEMID BIGINT = 0 OUTPUT
)
-- -- WITH ENCRYPTION
AS
/*********************************************
카드 추가 대부분 카드팩/개별카드
2009-04-29, GOMI97
2009-06-03, GOMI97
- USP_CARDBOX_USRS 통합
# CARD_TYPE
0 : 캐릭터
1 : 캐디
2 : 스페셜
3 : 팡야카드팩 V1, 골드, 실버, 브론즈
4 : 카드박스
# ERRCODE
0 : 성공
1 : 실패 (SYS ERR)
9 : CARD_TYPE 없음
4 : 요청된 CARD_TYPEID ROWCOUNT 중복
6 : 해당 UID 없음
2 : ERR 횟수가 2 - 비정상적인 케이스임
#USAGE :
DECLARE @CARD_PUSH_ERR INT
EXEC USP_CARD_PUSH_S4 3556218, 2088763599, 1, 'SHOP', @CARD_PUSH_ERR OUTPUT
SELECT @CARD_PUSH_ERR
***********************************************/
BEGIN
SET NOCOUNT ON
DECLARE @CARD_ITEMID BIGINT, @ERR TINYINT, @TOT_QTY INT
SET @ERR = 0
SET @CARD_ITEMID = 0
SET @TOT_QTY = 0
IF EXISTS ( SELECT 1 FROM PANGYA_MEMBER_INFO WHERE UID = @UID ) BEGIN
-- #0. CHECK CARD
SELECT @CARD_ITEMID = CARD_ITEMID, @TOT_QTY = ( QTY + @QTY )
FROM DBO.TD_CARD_USRS
WHERE UID = @UID
AND CARD_TYPEID = @CARD_TYPEID
AND USE_YN = 'Y'
AND QTY > 0
AND END_DT IS NULL
BEGIN TRAN
IF ( @CARD_ITEMID > 0 AND @CARD_TYPE < 4 ) BEGIN
UPDATE DBO.TD_CARD_USRS
SET QTY = @TOT_QTY
, GET_DT = GETDATE()
WHERE UID = @UID
AND CARD_ITEMID = @CARD_ITEMID
SELECT @ERR = CASE WHEN (@@ERROR != 0) THEN @ERR + 1 ELSE 0 END
END ELSE IF ( @CARD_ITEMID = 0 OR @CARD_TYPE >= 4 ) BEGIN
IF ( @CARD_TYPE = 9 ) BEGIN
SELECT @CARD_TYPE = COM0
FROM DBO.PANGYA_ITEM_TYPELIST
WHERE TYPEID = @CARD_TYPEID
AND TYPE = 31
END
IF ( @CARD_TYPE = 9 ) BEGIN
SET @ERR = 9 -- 해당 @CARD_TYPE 없음
END ELSE BEGIN
INSERT INTO DBO.TD_CARD_USRS (UID, CARD_TYPEID, CARD_TYPE, QTY, GET_DT, USE_YN)
SELECT @UID, @CARD_TYPEID, @CARD_TYPE, @QTY, GETDATE(), 'Y'
SELECT @ERR = CASE WHEN (@@ERROR != 0) THEN @ERR + 1 ELSE 0 END, @CARD_ITEMID = SCOPE_IDENTITY(), @TOT_QTY = @QTY
END
END
IF ( @ERR = 0 ) BEGIN
COMMIT TRAN
SELECT @RESULT = @ERR, @OUT_CARD_ITEMID = @CARD_ITEMID
IF ( @OUTPUT <> -1 ) SELECT ERRCODE = @ERR, CARD_ITEMID = @CARD_ITEMID, QTY = @TOT_QTY, ADD_QTY = @QTY
END ELSE BEGIN
ROLLBACK TRAN
SELECT @RESULT = @ERR, @OUT_CARD_ITEMID = @CARD_ITEMID
IF ( @OUTPUT <> -1 ) SELECT ERRCODE = @ERR, CARD_ITEMID = @CARD_ITEMID, QTY = @TOT_QTY, ADD_QTY = 0
END
END ELSE BEGIN
-- PANGYA_UID NOT MATCH
SELECT @ERR = 6
SELECT @RESULT = @ERR, @OUT_CARD_ITEMID = @CARD_ITEMID
IF ( @OUTPUT <> -1 ) SELECT ERRCODE = @ERR, CARD_ITEMID = @CARD_ITEMID, QTY = @TOT_QTY, ADD_QTY = @QTY
END
-- FINAL NEW CARD LOG
INSERT INTO Pangya_S4_TH.DBO.TZ_CARD_LOG (TYPES, ERRCODE, UID, ORI_ITEMID, CARD_ITEMID, CARD_TYPEID, QTY, FINAL_QTY, REGDATE)
SELECT @TYPES, ERRCODE = @ERR, @UID, ORI_CARD_ITEMID = 0, CARD_ITEMID = @CARD_ITEMID, CARD_TYPEID = @CARD_TYPEID,
ADD_QTY = CASE WHEN (@ERR != 0) THEN 0 ELSE @QTY END, QTY = @TOT_QTY, GETDATE()
-- FINAL NEW CARD LOG
END
