USE [Pangya_S4_TH]
GO
/******
* USP_ITEM_RECYCLE - Fixed by chreadie and hvdaedalus!
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[USP_ITEM_RECYCLE] (
@UID AS INT ,
@RECYCLEDATA VARCHAR(8000)
) -- WITH ENCRYPTION
AS
/*********************
ADD, 20080219 AMY HAEJIN HYUN
CARD V1.1 카드 붙은 의상: 마법상자
2009-05-26, GOMI97
- @@IDENTITY 패치
295 SET @ITEM_ID_0 = @@IDENTITY
307 SET @ITEM_ID_0 = @@IDENTITY
315 SET @ITEM_ID_0 = @@IDENTITY
**********************/
BEGIN
SET NOCOUNT ON
-- 0. 카디에의 마법상자
DECLARE @ITEMS INT
DECLARE @ITEM_ID_1 bigint
DECLARE @TYPEID_1 int
DECLARE @C0_1 smallint
DECLARE @ISPARTS_1 smallint
DECLARE @ITEM_ID_2 bigint
DECLARE @TYPEID_2 int
DECLARE @C0_2 smallint
DECLARE @ISPARTS_2 smallint
DECLARE @ITEM_ID_3 bigint
DECLARE @TYPEID_3 int
DECLARE @C0_3 smallint
DECLARE @ISPARTS_3 smallint
DECLARE @ITEM_ID_4 bigint
DECLARE @TYPEID_4 int
DECLARE @C0_4 smallint
DECLARE @ISPARTS_4 smallint
DECLARE @ITEM_ID_0 bigint
DECLARE @TYPEID_0 int
DECLARE @C0_0 smallint
DECLARE @ISPARTS_0 smallint
SET @C0_1 = 0
SET @ISPARTS_1 = 0
SET @ITEM_ID_1 = 0
SET @TYPEID_1 = 0
SET @C0_2 = 0
SET @ISPARTS_2 = 0
SET @ITEM_ID_2 = 0
SET @TYPEID_2 = 0
SET @C0_2 = 0
SET @ISPARTS_3 = 0
SET @ITEM_ID_3 = 0
SET @TYPEID_3 = 0
SET @C0_4 = 0
SET @ISPARTS_4 = 0
SET @ITEM_ID_4 = 0
SET @TYPEID_4 = 0
DECLARE @hdlXmlDoc INT
--DECLARE @Entry2 VARCHAR(1000)
exec sp_xml_preparedocument @hdlXmlDoc OUTPUT, @RECYCLEDATA
SELECT @C0_1 = C0, @ISPARTS_1 = IsParts, @ITEM_ID_1 = Item_ID, @TYPEID_1 = TypeID FROM Openxml(@hdlXmlDoc,'/RECYCLE/MINUS')
WITH (SEQ int '@SEQ',
TypeID BIGINT '@TYPEID',
Item_ID INT '@ITEM_ID',
C0 int '@C0',
IsParts int '@ISPARTS')
WHERE SEQ = 1
SELECT @C0_2 = C0, @ISPARTS_2 = IsParts, @ITEM_ID_2 = Item_ID, @TYPEID_2 = TypeID FROM Openxml(@hdlXmlDoc,'/RECYCLE/MINUS')
WITH (SEQ int '@SEQ',
TypeID BIGINT '@TYPEID',
Item_ID INT '@ITEM_ID',
C0 int '@C0',
IsParts int '@ISPARTS')
WHERE SEQ = 2
SELECT @C0_3 = C0, @ISPARTS_3 = IsParts, @ITEM_ID_3 = Item_ID, @TYPEID_3 = TypeID FROM Openxml(@hdlXmlDoc,'/RECYCLE/MINUS')
WITH (SEQ int '@SEQ',
TypeID BIGINT '@TYPEID',
Item_ID INT '@ITEM_ID',
C0 int '@C0',
IsParts int '@ISPARTS')
WHERE SEQ = 3
SELECT @C0_4 = C0, @ISPARTS_4 = IsParts, @ITEM_ID_4 = Item_ID, @TYPEID_4 = TypeID FROM Openxml(@hdlXmlDoc,'/RECYCLE/MINUS')
WITH (SEQ int '@SEQ',
TypeID BIGINT '@TYPEID',
Item_ID INT '@ITEM_ID',
C0 int '@C0',
IsParts int '@ISPARTS')
WHERE SEQ = 4
SELECT @C0_0 = C0, @ISPARTS_0 = IsParts, @ITEM_ID_0 = Item_ID, @TYPEID_0 = TypeID FROM Openxml(@hdlXmlDoc,'/RECYCLE/PLUS')
WITH (SEQ int '@SEQ',
TypeID BIGINT '@TYPEID',
Item_ID INT '@ITEM_ID',
C0 int '@C0',
IsParts int '@ISPARTS')
WHERE SEQ = 1
EXEC sp_xml_removedocument @hdlXmlDoc
DECLARE @C0_1_ORI SMALLINT
, @C0_2_ORI SMALLINT
, @C0_3_ORI SMALLINT
, @C0_4_ORI SMALLINT
-- 0 : SUCCESS , 1 : FAILURE
DECLARE @RET_1 TINYINT
, @RET_2 TINYINT
, @RET_3 TINYINT
, @RET_4 TINYINT
DECLARE @CADDIECLEVEL TINYINT
, @CADDIEEXP INT
DECLARE @RET_0 AS TINYINT
-- INITIALIZE - 초기화
SET @RET_1 = 1
SET @RET_2 = 1
SET @RET_3 = 1
SET @RET_4 = 1
SET @RET_0 = 1
BEGIN TRAN
---- 1. CHECK THE ITEM CAN BE CONSUMED. ------------------------------------------------------------------------------
IF (@ITEM_ID_1 > 0) BEGIN
IF ( @ISPARTS_1 = 3 ) BEGIN
SELECT TOP 1 @C0_1_ORI = 1
, @CADDIECLEVEL = CLEVEL
, @CADDIEEXP = [EXP]
FROM DBO.PANGYA_CADDIE_INFO WITH(READUNCOMMITTED)
WHERE UID = @UID
AND ITEM_ID = @ITEM_ID_1
AND TYPEID = @TYPEID_1
AND VALID = 1
-- 아이템이 삭제할 개수만큼 있어야
IF ( @@ROWCOUNT = 1 ) BEGIN
SET @RET_1 = 0 -- 성공
END ELSE BEGIN
SET @RET_1 = 2 -- 실패
END
END ELSE BEGIN
SELECT TOP 1 @C0_1_ORI = C0
FROM DBO.PANGYA_ITEM_WAREHOUSE WITH(READUNCOMMITTED)
WHERE UID = @UID
AND ITEM_ID = @ITEM_ID_1
AND TYPEID = @TYPEID_1 AND VALID = 1
IF ( @@ROWCOUNT = 1 ) -- 아이템이 삭제할 개수만큼 있어야
AND ( ( @C0_1_ORI >= @C0_1 AND @ISPARTS_1 = 0 ) OR ( @ISPARTS_1 = 1) ) BEGIN
SET @RET_1 = 0 -- 성공
END ELSE BEGIN
SET @RET_1 = 2 -- 실패
END
END
END
---- 2. CHECK THE ITEM CAN BE CONSUMED. ------------------------------------------------------------------------------
IF (@ITEM_ID_2 > 0) BEGIN
SELECT TOP 1 @C0_2_ORI = C0
FROM DBO.PANGYA_ITEM_WAREHOUSE WITH(READUNCOMMITTED)
WHERE UID = @UID
AND ITEM_ID = @ITEM_ID_2
AND TYPEID = @TYPEID_2
AND VALID = 1
IF ( @@ROWCOUNT = 1 ) -- 아이템이 삭제할 개수만큼 있어야
AND ( ( @C0_2_ORI >= @C0_2 AND @ISPARTS_2 = 0 ) OR ( @ISPARTS_2 = 1) ) BEGIN
SET @RET_2 = 0 -- 성공
END ELSE BEGIN
SET @RET_2 = 2 -- 실패
END
END
---- 3. CHECK THE ITEM CAN BE CONSUMED. ------------------------------------------------------------------------------
IF (@ITEM_ID_3 > 0) BEGIN
SELECT TOP 1 @C0_3_ORI = C0
FROM DBO.PANGYA_ITEM_WAREHOUSE WITH(READUNCOMMITTED)
WHERE UID = @UID
AND ITEM_ID = @ITEM_ID_3
AND TYPEID = @TYPEID_3
AND VALID = 1
IF ( @@ROWCOUNT = 1 ) -- 아이템이 삭제할 개수만큼 있어야
AND ( ( @C0_3_ORI >= @C0_3 AND @ISPARTS_3 = 0 ) OR ( @ISPARTS_3 = 1) ) BEGIN
SET @RET_3 = 0 -- 성공
END ELSE BEGIN
SET @RET_3 = 2 -- 실패
END
END
---- 4. CHECK THE ITEM CAN BE CONSUMED. ------------------------------------------------------------------------------
IF (@ITEM_ID_4 > 0) BEGIN
SELECT TOP 1 @C0_4_ORI = C0
FROM DBO.PANGYA_ITEM_WAREHOUSE WITH(READUNCOMMITTED)
WHERE UID = @UID
AND ITEM_ID = @ITEM_ID_4
AND TYPEID = @TYPEID_4
AND VALID = 1
IF ( @@ROWCOUNT = 1 ) -- 아이템이 삭제할 개수만큼 있어야
AND ( ( @C0_4_ORI >= @C0_4 AND @ISPARTS_4 = 0 ) OR ( @ISPARTS_4 = 1) ) BEGIN
SET @RET_4 = 0 -- 성공
END ELSE BEGIN
SET @RET_4 = 2 -- 실패
END
END
IF ( ( @RET_1 IN (0, 1) )
AND ( @RET_2 IN (0, 1) )
AND ( @RET_3 IN (0, 1) )
AND ( @RET_4 IN (0, 1) ) ) BEGIN
-- 0.
SET @RET_0 = 0
---- 1. CONSUME THE ITEM. ------------------------------------------------------------------------------
IF ( @RET_1 = 0 ) BEGIN
IF ( @ISPARTS_1 = 1 ) BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET VALID = 0
WHERE UID = @UID
AND TYPEID = @TYPEID_1
AND VALID = 1
AND ITEM_ID = @ITEM_ID_1
SET @C0_1 = 0
END ELSE IF ( @ISPARTS_1 = 3 ) BEGIN
UPDATE DBO.PANGYA_CADDIE_INFO
SET VALID = 0
WHERE UID = @UID
AND TYPEID = @TYPEID_1
AND VALID = 1
AND ITEM_ID = @ITEM_ID_1
SET @C0_1 = 0
END ELSE BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET C0 = C0 - @C0_1
, VALID = CASE WHEN C0 <= @C0_1 THEN 0 ELSE 1 END
WHERE UID = @UID
AND TYPEID = @TYPEID_1
AND VALID = 1
AND ITEM_ID = @ITEM_ID_1
AND C0 >= @C0_1
IF (@C0_1_ORI >= @C0_1) BEGIN --CODE BY 김종열 2007 08 09
SET @C0_1 = @C0_1_ORI - @C0_1
END ELSE BEGIN
SET @C0_1 = 0
END
END
END
---- 2. CONSUME THE ITEM. ------------------------------------------------------------------------------
IF ( @RET_2 = 0 ) BEGIN
IF ( @ISPARTS_2 = 1 ) BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET VALID = 0
WHERE UID = @UID
AND TYPEID = @TYPEID_2
AND VALID = 1
AND ITEM_ID = @ITEM_ID_2
SET @C0_2 = 0
END ELSE BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET C0 = C0 - @C0_2
, VALID = CASE WHEN C0 <= @C0_2 THEN 0 ELSE 1 END
WHERE UID = @UID
AND TYPEID = @TYPEID_2
AND VALID = 1
AND ITEM_ID = @ITEM_ID_2
AND C0 >= @C0_2
IF (@C0_2_ORI >= @C0_2) BEGIN --CODE BY 김종열 2007 08 09
SET @C0_2 = @C0_2_ORI - @C0_2
END ELSE BEGIN
SET @C0_2 = 0
END
END
END
---- 3. CONSUME THE ITEM. ------------------------------------------------------------------------------
IF ( @RET_3 = 0 ) BEGIN
IF ( @ISPARTS_3 = 1 ) BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET VALID = 0
WHERE UID = @UID
AND TYPEID = @TYPEID_3
AND VALID = 1
AND ITEM_ID = @ITEM_ID_3
SET @C0_3 = 0
END ELSE BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET C0 = C0 - @C0_3
, VALID = CASE WHEN C0 <= @C0_3 THEN 0 ELSE 1 END
WHERE UID = @UID
AND TYPEID = @TYPEID_3
AND VALID = 1
AND ITEM_ID = @ITEM_ID_3
AND C0 >= @C0_3
IF (@C0_3_ORI >= @C0_3) BEGIN --CODE BY 김종열 2007 08 09
SET @C0_3 = @C0_3_ORI - @C0_3
END ELSE BEGIN
SET @C0_3 = 0
END
END
END
---- 4. CONSUME THE ITEM. ------------------------------------------------------------------------------
IF ( @RET_4 = 0 ) BEGIN
IF ( @ISPARTS_4 = 1 ) BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET VALID = 0
WHERE UID = @UID
AND TYPEID = @TYPEID_4
AND VALID = 1
AND ITEM_ID = @ITEM_ID_4
SET @C0_4 = 0
END ELSE BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET C0 = C0 - @C0_4
, VALID = CASE WHEN C0 <= @C0_4 THEN 0 ELSE 1 END
WHERE UID = @UID
AND TYPEID = @TYPEID_4
AND VALID = 1
AND ITEM_ID = @ITEM_ID_4
AND C0 >= @C0_4
IF (@C0_4_ORI >= @C0_4) BEGIN --CODE BY 김종열 2007 08 09
SET @C0_4 = @C0_4_ORI - @C0_4
END ELSE BEGIN
SET @C0_4 = 0
END
END
END
END ELSE BEGIN
SET @RET_0 = 1 -- 0. 소모 실패
END
IF (@RET_0 = 0 ) BEGIN -- 회수 성공시
IF ( @ISPARTS_0 IN (1, 2) ) BEGIN
-- 패키지 아이템의 경우 구성물 들을 모두 지급한다.
INSERT INTO DBO.PANGYA_ITEM_WAREHOUSE (UID,TYPEID,VALID,REGDATE,GIFT_FLAG,FLAG,APPLYTIME,ENDDATE,C0,C1,C2,C3,C4,PURCHASE,ITEMTYPE)
VALUES(@UID,@TYPEID_0,1,GETDATE(),0,1,GETDATE(),GETDATE(),0,0,0,0,0,0,0)
SET @ITEM_ID_0 = SCOPE_IDENTITY()
SET @C0_0 = 1
END ELSE IF ( @ISPARTS_0 = 3 ) BEGIN
-- 패키지 아이템의 경우 구성물 들을 모두 지급한다.
SET @CADDIECLEVEL = ISNULL(@CADDIECLEVEL , 0)
SET @CADDIEEXP = ISNULL(@CADDIEEXP , 0)
INSERT INTO DBO.PANGYA_CADDIE_INFO(UID,TYPEID,PARTS_TYPEID,GIFT_FLAG,PERIOD,ENDDATE,RENTFLAG,PURCHASE, CLEVEL ,[EXP] )
VALUES(@UID,@TYPEID_0,0,0,0, DATEADD(DAY,0,GETDATE()),0,0, @CADDIECLEVEL, @CADDIEEXP)
SET @ITEM_ID_0 = SCOPE_IDENTITY()
SET @C0_0 = 1
END ELSE BEGIN
IF (@ITEM_ID_0 = 0 ) BEGIN
INSERT INTO DBO.PANGYA_ITEM_WAREHOUSE(UID,TYPEID,VALID,REGDATE,GIFT_FLAG,FLAG,APPLYTIME,ENDDATE,C0,C1,C2,C3,C4,PURCHASE,ITEMTYPE)
VALUES(@UID,@TYPEID_0,1,GETDATE(),0,1,GETDATE(),GETDATE(),@C0_0,0,0,0,0,0,0)
SET @ITEM_ID_0 = SCOPE_IDENTITY()
END ELSE BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET C0 = C0 + @C0_0
WHERE UID = @UID
AND TYPEID = @TYPEID_0
AND VALID = 1
AND ITEM_ID = @ITEM_ID_0
SELECT TOP 1 @C0_0 = C0
FROM DBO.PANGYA_ITEM_WAREHOUSE
WHERE UID = @UID
AND TYPEID = @TYPEID_0
AND VALID = 1
AND ITEM_ID = @ITEM_ID_0
END
END
/***********카드에 쓰려고 **********/
--DECLARE @JOIN_ITEMID_NEW BIGINT
DECLARE @CARD_MAGICBOX TABLE (
IDX TINYINT IDENTITY(1,1)
, UID INT
, JOIN_ITEMID BIGINT
, JOIN_TYPEID INT
, CARD_TYPEID INT
, CARD_ITEMID BIGINT
, CARD_TYPE TINYINT
, SOKET_NUM TINYINT
, IN_DATE DATETIME )
DECLARE @JOIN_IDX INT
SELECT TOP 1 @JOIN_IDX = JOIN_IDX
FROM DBO.TD_CARD_JOIN2
WHERE UID = @UID AND JOIN_TYPEID = @TYPEID_1 AND JOIN_ITEMID = @ITEM_ID_1 AND END_DT IS NULL AND USE_YN = 'Y'
IF (@JOIN_IDX IS NOT NULL) BEGIN
UPDATE DBO.TD_CARD_JOIN2
SET UID = @UID
, JOIN_TYPEID = @TYPEID_0
, JOIN_ITEMID = @ITEM_ID_0
, USE_DT = GETDATE()
WHERE UID = @UID
AND JOIN_TYPEID = @TYPEID_1
AND JOIN_ITEMID = @ITEM_ID_1
AND USE_DT < GETDATE()
AND END_DT IS NULL
AND USE_YN = 'Y'
END
/*************************************/
SET @RET_0 = 0
END
ELSE BEGIN
SET @ITEM_ID_1 = 0
SET @TYPEID_1 = 0
SET @C0_1 = 0
SET @ITEM_ID_2 = 0
SET @TYPEID_2 = 0 SET @C0_2 = 0
SET @ITEM_ID_3 = 0
SET @TYPEID_3 = 0
SET @C0_3 = 0
SET @ITEM_ID_4 = 0
SET @TYPEID_4 = 0
SET @C0_4 = 0
SET @ITEM_ID_0 = 0
SET @TYPEID_0 = 0
SET @C0_0 = 0
SET @RET_0 = 1
END
IF @RET_0 = 0 BEGIN
SELECT 1
CREATE TABLE #temp (
SEQ TINYINT
, ITEM_ID INT
, TYPE_ID_ BIGINT
, C0 INT
, ISPARTS INT)
IF @RET_1 = 0 BEGIN
INSERT INTO #temp VALUES(1, @TYPEID_1, @ITEM_ID_1, @C0_1, @ISPARTS_1)
END
IF @RET_2 = 0 BEGIN
INSERT INTO #temp VALUES(2, @TYPEID_2, @ITEM_ID_2, @C0_2, @ISPARTS_2)
END
IF @RET_3 = 0 BEGIN
INSERT INTO #temp VALUES(3, @TYPEID_3, @ITEM_ID_3, @C0_3, @ISPARTS_3)
END
IF @RET_4 = 0 BEGIN
INSERT INTO #temp VALUES(4, @TYPEID_4, @ITEM_ID_4, @C0_4, @ISPARTS_4)
END
SELECT * FROM #temp
DROP TABLE #temp
SELECT @TYPEID_0 AS TYPEID_0, @ITEM_ID_0 AS ITEM_ID_0, @C0_0 AS [C0_0], @ISPARTS_0 AS [ISPARTS_0]
INSERT INTO PANGYA_RECYCLE_ITEM_LOG(UID,RET,TYPEID_1,C0_1,TYPEID_2,C0_2,TYPEID_3,C0_3,TYPEID_4,C0_4,TYPEID_0,C0_0,REGDATE)
VALUES (@UID,@RET_0,@TYPEID_1,@C0_1,@TYPEID_2,@C0_2,@TYPEID_3,@C0_3,@TYPEID_4,@C0_4,@TYPEID_0,@C0_0,GETDATE())
COMMIT
RETURN 1
END ELSE BEGIN
ROLLBACK
SELECT 0
RETURN 0
END
SET NOCOUNT OFF
END
Originally Posted by chreadie and hvdaedalus
Spoiler:
Reply With Quote![[sql] usp_item_recycle](http://ragezone.com/hyper728.png)
