Originally Posted by
Twilight
Example:
1. Configure SQL Profiler (enable all values and events) and set to record a trace in the log file for later viewing ...
2. Start the SQL Profiler
3. Give the gift and you will see a log window which procedure works for this.
4. Open this procedure and change the function Update ...Users_Master .... .. Point ...
necessary mathematical deiystvie subtraction ...
if the subtraction result returns a negative value of the action to cancel the purchase because it is not enough AP
I think the error is in that Procedur:
PHP Code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
/*==================================================
@author lenasoft
@date 2006-09-22
@return @Ret
0 = 성공
1 = 해당서버에 케릭터명이 존재하지 않음.
2 = 같은 개정임
3 = 아이템 보유수량 초과(포인트개정슬롯 꽉참)
4 = 슬롯부족(아이템을 추가할수 없음)
5 = 인서트 오류
6 = 상품테이블 데이타오류(해당 상품코드 존재하지 않음)
7 = 포인트 차감 실패.
8 = 로그기록 실패.
10= 포인트부족
@brief 유저에게 포인트아이템을 선물한다.
==================================================*/
ALTER Proc [dbo].[usp_Save_User_GiftPointItems_NCash]
@TargetUserID int output,
@TargetCharName varchar(30),
@ProductCode varchar(20),
@BuyUserUID int,
@BuyCharID int,
@UsePoint int,
@UseDateZ varchar (50),
@BuyUserID varchar(30)
AS
DECLARE @UseDate as datetime
SELECT @UseDate = CONVERT(datetime, @UseDateZ, 120)
SET NOCOUNT ON
--SET XACT_ABORT ON
DECLARE @SlotCnt int
DECLARE @SlotMax int
DECLARE @SlotUse int
DECLARE @I int
DECLARE @J int
DECLARE @StrItemID varchar(20)
DECLARE @StrItemCount varchar(20)
DECLARE @Query nvarchar(1000)
DECLARE @ItemID int
DECLARE @ItemCount tinyint
DECLARE @Ret int
-----------------------------------------------
--DECLARE @UserUID int
DECLARE @UseType int
--DECLARE @OrderNumber int
--DECLARE @RemainPoint int
SET @UseType = 2 --선물
SET @TargetUserID = 0
-----------------------------------------------
SET @Ret = 0
SET @SlotMax = 240
SELECT @TargetUserID = UserUID FROM Chars WHERE CharName = @TargetCharName AND Del = 0
IF( @TargetUserID = 0 OR @TargetUserID IS NULL )
BEGIN
-- 해당서버에 케릭터명이 존재하지 않음.
SET @Ret = 1
RETURN @Ret
END
IF( @TargetUserID = @BuyUserUID )
BEGIN
-- 같은 개정임
SET @Ret = 2
RETURN @Ret
END
BEGIN TRANSACTION
CREATE TABLE #SlotList( SlotNum int NOT NULL )
CREATE TABLE #InsertSlotList( SlotNum tinyint NOT NULL, ItemID int NOT NULL, ItemCount tinyint NOT NULL)
INSERT INTO #SlotList SELECT Slot FROM UserStoredPointItems WHERE UserUID=@TargetUserID ORDER BY Slot
SET @SlotCnt = ( SELECT ISNULL(COUNT(*),0) FROM #SlotList )
IF( @SlotCnt >= @SlotMax )
BEGIN
-- 아이템 보유수량 초과(포인트개정슬롯 꽉참)
SET @Ret = 3
GOTO ERROR_RETURN
END
SET @I = 1
WHILE( @I <= 24 )
BEGIN
SET @StrItemID = 'ItemID' + CAST( @I AS varchar(5) )
SET @StrItemCount = 'ItemCount' + CAST( @I AS varchar(5) )
SET @Query = 'SELECT @ItemID='+@StrItemID+', @ItemCount='+@StrItemCount+' FROM PS_DEFINEDB.PS_GameDefs.dbo.ProductList WHERE ProductCode=''' +@ProductCode+''''
EXEC sp_executesql @Query, N'@ItemID int OUTPUT, @ItemCount tinyint OUTPUT', @ItemID OUTPUT, @ItemCount OUTPUT
IF( (@ItemID IS NOT NULL) AND (@ItemCount IS NOT NULL) AND (@ItemID <> 0) )
BEGIN
SET @J = 0
WHILE( @J < @SlotMax )
BEGIN
SET @SlotUse = ( SELECT ISNULL(COUNT(*),0) FROM #SlotList WHERE SlotNum=@J )
IF( @SlotUse = 0 )
BEGIN
INSERT INTO UserStoredPointItems(UserUID,Slot,ItemID,ItemCount) VALUES(@TargetUserID,@J,@ItemID,@ItemCount)
IF( @@ERROR = 0 )
BEGIN
INSERT INTO #SlotList(SlotNum) VALUES(@J)
INSERT INTO #InsertSlotList(SlotNum, ItemID, ItemCount ) VALUES(@J, @ItemID, @ItemCount)
BREAK
END
ELSE
BEGIN
-- 인서트 오류
SET @Ret = 5
GOTO ERROR_RETURN
END
END
SET @J = @J + 1
END
IF( @J >= @SlotMax )
BEGIN
-- 슬롯부족(아이템을 추가할수 없음)
SET @Ret = 4
GOTO ERROR_RETURN
END
END
ELSE IF( @I = 1 )
BEGIN
-- 상품테이블 데이타오류(해당 상품코드 존재하지 않음)
SET @Ret = 6
GOTO ERROR_RETURN
END
SET @I = @I + 1
END
-- Insert Log
INSERT INTO PointLog(UseType,UserUID,CharID,UsePoint,ProductCode,UseDate,TargetName)
VALUES(@UseType,@BuyUserUID,@BuyCharID,@UsePoint,@ProductCode,@UseDate,@TargetCharName)
IF( @@ERROR<>0 )
BEGIN
SET @Ret = 8
GOTO ERROR_RETURN
END
-- Select
SELECT SlotNum, ItemID, ItemCount FROM #InsertSlotList
ERROR_RETURN:
DROP TABLE #SlotList
DROP TABLE #InsertSlotList
IF( @@ERROR = 0 AND @Ret = 0 )
BEGIN
COMMIT TRAN
RETURN 0
END
ELSE
BEGIN
ROLLBACK TRAN
RETURN @Ret
END
SET NOCOUNT OFF
--SET XACT_ABORT OFF
and
PHP Code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Save_User_NCash]
@BuyUserUID int,
@TargetUserID int,
@ProductCode varchar(20)
AS
SET NOCOUNT ON
DECLARE @Ret int
DECLARE @OrderNumber int
DECLARE @RemainPoint int
SET @Ret = 0
/*========================================
??? ??
??? ?? ?? ?? UID ? ?? ?? UID? ??? ???? ???.
procRequestOrderProductByGame
???? UID @buyClientUserNumber BIGINT
???? UID @receiveClientUserNumber BIGINT
????? @itemCode VARCHAR(50)
?? ?? @resultCode SMALLINT OUTPUT
?? ? ?? @cashBalanceAfterOrder INT OUTPUT
???? @orderNumber INT OUTPUT
resultCode
0 ??
1 ????
2 ?? ??? ???? ??
3 ?? ???? ???? ??
5 DB??
6 ????
=========================================*/
EXEC [PS_NCASH].[Billcrux30].dbo.procRequestOrderProductByGame @BuyUserUID,@TargetUserID,@ProductCode,@Ret OUTPUT, @RemainPoint OUTPUT, @OrderNumber OUTPUT
IF( @@ERROR <> 0 )
BEGIN
INSERT INTO PointErrorLog( UserUID, CharID, ProductCode, Ret) VALUES( @BuyUserUID, NULL, @ProductCode, 100 )
SET @Ret = 7
RETURN @Ret
END
IF ( @Ret <> 0 )
BEGIN
INSERT INTO PointErrorLog( UserUID, CharID, ProductCode, Ret) VALUES( @BuyUserUID, NULL, @ProductCode, @Ret )
END
SET @Ret = CASE @Ret
WHEN 0 THEN 0
WHEN 1 THEN 10
WHEN 2 THEN 1
WHEN 3 THEN 6
ELSE 7
END
RETURN @Ret
SET NOCOUNT OFF
--SET XACT_ABORT OFF
DBCC SHOW_STATISTICS(["PS_GameDefs"."dbo"."ProductList"],"PK_ProductList") WITH HISTOGRAM_STEPS
go
DBCC SHOW_STATISTICS(["PS_GameDefs"."dbo"."ProductList"],"PK_ProductList") WITH HISTOGRAM_STEPS
go
ROLLBACK TRANSACTION
go
ROLLBACK TRANSACTION
go
exec sp_reset_connection
go
exec sp_reset_connection
go
[PLANGUIDE]
go
SELECT @ItemID=ItemID11, @ItemCount=ItemCount11 FROM PS_DEFINEDB.PS_GameDefs.dbo.ProductList WHERE ProductCode='PK_primm0001'
go
server_id = 5
go
SET XACT_ABORT OFF
go
SET XACT_ABORT OFF
go
BEGIN TRANSACTION
go
BEGIN TRANSACTION
go
declare @p1 int
set @p1=NULL
declare @p2 bigint
set @p2=NULL
exec [sys].sp_getschemalock @p1 output,@p2 output,N'"PS_GameDefs"."dbo"."ProductList"'
select @p1, @p2
go
2010-09-16 23:58:44.54 spid169 Starting up database 'PS_GameDefs'.
go
Starting up database 'PS_GameDefs'.
go
Reply With Quote![[help]Gift Bug !](http://ragezone.com/hyper728.png)