hello . i need some help with procedure . someone can help me
hello . i need some help with procedure . someone can help me
i have procedure and he have bug ... can someone help me ???
USE [SRO_VT_SHARDLOG]
GO
/****** Object: StoredProcedure [dbo].[_AddLogItem] Script Date: 2/28/2020 2:46:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------------------------------------------------------
-- Step 2
-- ±âÁ¸ ¾ÆÀÌÅÛ ·Î±× ÀÔ·Â Stored procedure ¼öÁ¤
-- DataBase : Shard Log
ALTER procedure [dbo].[_AddLogItem]
@CharID int,
@ItemRefID int,
@ItemSerial bigint,
@dwData int,
@TargetStorage tinyint,
@Operation tinyint,
@Slot_From tinyint,
@Slot_To tinyint,
@EventPos varchar(64),
@strDesc varchar(128),
@Gold bigint -- #ifdef EXTEND_GOLD_TYPE »ðÀÔ
as
----------------Title Scroll -------------------
IF (@Operation = '41') and (@ItemRefID between 402728 and 402733) ----- Change 47280 with your RefObjCommon scroll ID
begin
UPDATE [SRO_VT_SHARD].[dbo].[_Char] SET [HwanLevel] = @ItemRefID-402698 Where [CharID] = @CharID
end
IF (@Operation = '41') and (@ItemRefID = '402709') ----- Change 47280 with your RefObjCommon scroll ID
Begin
UPDATE [SRO_VT_SHARD].[dbo].[_Char] SET [HwanLevel] = '36' Where [CharID] = @CharID
End
IF (@Operation = '41') and (@ItemRefID between 402734 and 402738) ----- Change 47280 with your RefObjCommon scroll ID
begin
UPDATE [SRO_VT_SHARD].[dbo].[_Char] SET [HwanLevel] = @ItemRefID-402697 Where [CharID] = @CharID
end
IF (@Operation = '41') and (@ItemRefID = '402715') ----- Change 47280 with your RefObjCommon scroll ID
Begin
UPDATE [SRO_VT_SHARD].[dbo].[_Char] SET [HwanLevel] = '42' Where [CharID] = @CharID
End
IF (@Operation = '41') and (@ItemRefID between 402739 and 402747) ----- Change 47280 with your RefObjCommon scroll ID
begin
UPDATE [SRO_VT_SHARD].[dbo].[_Char] SET [HwanLevel] = @ItemRefID-402696 Where [CharID] = @CharID
end
----------------------lottery scroll------------
if (@ItemRefID = 402727 and @Operation = 16)
begin--Usage:--exec _ADD_ITEM_EXTERN 'CharName','ItemsCodeName',Count,OptLvl
declare @cname1 varchar(64)
declare @cname varchar(64)
select @cname = CharName16 from SRO_VT_SHARD.dbo._Char where CharID = @CharID
declare @sayi int
declare @adet int
declare @maxa int
declare @mina int
select @maxa = MAX(ID) FROM SRO_VT_SHARD .dbo._LotteryTABLE
select @mina = Min(ID) FROM SRO_VT_SHARD .dbo._LotteryTABLE
declare @itemname varchar(64)
etik:
SELECT @sayi = (CONVERT(INT, RAND() * (@maxa-@mina+1))+(@mina))
select @itemname = CodeName128 from SRO_VT_SHARD.dbo._LotteryTABLE where ID = @sayi
select @itemname = CodeName128 from SRO_VT_SHARD.dbo._LotteryTABLE where ID = @sayi
print @sayi
print @itemname
if(PATINDEX('%ITEM%',@itemname)>0)
begin
print 'not present'
end
else begin
print 'not present'
goto etik
end
exec SRO_VT_SHARD.dbo._ADD_ITEM_EXTERN @cname,@itemname,1,0
end
----------------------Recall Skills-------------
IF ((@Operation = 41 ) AND (@ItemRefID = 402725))
BEGIN
exec SRO_VT_SHARD.dbo._Scroll_Reset @CharID
END
---------------------FGW Reset
IF ((@Operation = 41 ) AND (@ItemRefID = 402726))
BEGIN
exec SRO_VT_SHARD.dbo._ForgottenWorldReset @CharID
END
------------------Remove premium----------------
IF (@Operation = 41) and (@ItemRefID = 402748)
BEGIN
-- Prem plus lines
DELETE FROM SRO_VT_SHARD.dbo._TimedJob WHERE JobID = '9278' AND CharID = @CharID
DELETE FROM SRO_VT_SHARD.dbo._TimedJob WHERE JobID = '9277' AND CharID = @CharID
DELETE FROM SRO_VT_SHARD.dbo._TimedJob WHERE JobID = '5412' AND CharID = @CharID
-- Gold Prem Lines
DELETE FROM SRO_VT_SHARD.dbo._TimedJob WHERE JobID = '35567' AND CharID = @CharID
DELETE FROM SRO_VT_SHARD.dbo._TimedJob WHERE JobID = '9278' AND CharID = @CharID
DELETE FROM SRO_VT_SHARD.dbo._TimedJob WHERE JobID = '34049' AND CharID = @CharID
END
-------------------silk scroll----------------100
IF (@Operation = 41) and (@ItemRefID = 402750)
BEGIN
DECLARE @USERJID int;
SET @USERJID = (SELECT USERJID FROM SRO_VT_SHARD.._USER WHERE CHARID = @CHARID);
IF (SELECT COUNT(*) FROM SRO_VT_ACCOUNT..SK_SILK WHERE JID = @USERJID) > 0
UPDATE SRO_VT_ACCOUNT..SK_SILK SET SILK_OWN = SILK_OWN + 100 WHERE JID = @USERJID;
End
---------------50 silk scroll
IF (@Operation = 41) and (@ItemRefID = 405688)
BEGIN
SET @USERJID = (SELECT USERJID FROM SRO_VT_SHARD.._USER WHERE CHARID = @CHARID);
IF (SELECT COUNT(*) FROM SRO_VT_ACCOUNT..SK_SILK WHERE JID = @USERJID) > 0
UPDATE SRO_VT_ACCOUNT..SK_SILK SET SILK_OWN = SILK_OWN + 50 WHERE JID = @USERJID;
End
----------------200 silk scroll
IF (@Operation = 41) and (@ItemRefID = 405689)
BEGIN
SET @USERJID = (SELECT USERJID FROM SRO_VT_SHARD.._USER WHERE CHARID = @CHARID);
IF (SELECT COUNT(*) FROM SRO_VT_ACCOUNT..SK_SILK WHERE JID = @USERJID) > 0
UPDATE SRO_VT_ACCOUNT..SK_SILK SET SILK_OWN = SILK_OWN + 200 WHERE JID = @USERJID;
End
--------------500 silk scroll
IF (@Operation = 41) and (@ItemRefID = 405690)
BEGIN
SET @USERJID = (SELECT USERJID FROM SRO_VT_SHARD.._USER WHERE CHARID = @CHARID);
IF (SELECT COUNT(*) FROM SRO_VT_ACCOUNT..SK_SILK WHERE JID = @USERJID) > 0
UPDATE SRO_VT_ACCOUNT..SK_SILK SET SILK_OWN = SILK_OWN + 500 WHERE JID = @USERJID;
End
-------------1k silk scroll
IF (@Operation = 41) and (@ItemRefID = 405691)
BEGIN
SET @USERJID = (SELECT USERJID FROM SRO_VT_SHARD.._USER WHERE CHARID = @CHARID);
IF (SELECT COUNT(*) FROM SRO_VT_ACCOUNT..SK_SILK WHERE JID = @USERJID) > 0
UPDATE SRO_VT_ACCOUNT..SK_SILK SET SILK_OWN = SILK_OWN + 1000 WHERE JID = @USERJID;
End
-------------2k silk scroll
IF (@Operation = 41) and (@ItemRefID = 405692)
BEGIN
SET @USERJID = (SELECT USERJID FROM SRO_VT_SHARD.._USER WHERE CHARID = @CHARID);
IF (SELECT COUNT(*) FROM SRO_VT_ACCOUNT..SK_SILK WHERE JID = @USERJID) > 0
UPDATE SRO_VT_ACCOUNT..SK_SILK SET SILK_OWN = SILK_OWN + 2000 WHERE JID = @USERJID;
End
----------------Remove Dur Scroll---------------
IF (@Operation = 41) and (@ItemRefID = 402749)
begin
Update SRO_VT_SHARD.._Items
Set MagParamNum = 0 , MagParam1 = NULL, MagParam2 = NULL, MagParam3 = NULL, MagParam4 = NULL, MagParam5 = NULL, MagParam6 = NULL, MagParam7 = NULL, MagParam8 = NULL, MagParam9 = NULL, MagParam10 = NULL, MagParam11 = NULL, MagParam12 = NULL where ID64 in (select ItemID from _Inventory where CharID = @CharID and Slot between 0 and 12 and ItemID Between 1 AND 999999999)
end
-------------------Devil Upgrade-------------
IF (@Operation = 41)
BEGIN
IF (@ItemRefID = 402751)
BEGIN
declare @RefCharID int
DECLARE @OldItemCodeName varchar(128)
DECLARE @SS varchar(128)
DECLARE @OldItemPlus int
DECLARE @NewItemPlus int
DECLARE @NewItemCodeName varchar(128)
Declare @RefMail INT = (Select ItemID From SRO_VT_SHARD.dbo._Inventory Where CharID=@CharID AND Slot ='1')
Declare @RefMailID INT = (Select RefItemID From SRO_VT_SHARD.dbo._Items Where ID64 = @RefMail)
select @OldItemCodeName = CodeName128 from SRO_VT_SHARD.dbo._RefObjCommon where ID = @RefMailID
if(@OldItemCodeName = 'ITEM_MALL_AVATAR_M_NASRUN' or @OldItemCodeName = 'ITEM_MALL_AVATAR_M_NASRUN_BLUE' or @OldItemCodeName = 'ITEM_MALL_AVATAR_M_NASRUN_YELLOW')
begin
set @NewItemCodeName = 'ITEM_MALL_AVATAR_M_NASRUN_UNIQUE'
end
if(@OldItemCodeName = 'ITEM_MALL_AVATAR_W_NASRUN' or @OldItemCodeName = 'ITEM_MALL_AVATAR_W_NASRUN_BLUE' or @OldItemCodeName = 'ITEM_MALL_AVATAR_W_NASRUN_YELLOW')
begin
set @NewItemCodeName = 'ITEM_MALL_AVATAR_W_NASRUN_UNIQUE'
end
declare @CharName16 varchar(64)
set @OldItemPlus = 7
set @NewItemPlus = 0
DECLARE @ReplaceID int
SET @REPLACEID = (select top 1 ItemID from SRO_VT_SHARD.dbo._Inventory as inv inner join SRO_VT_SHARD.dbo._Items as itm on inv.ItemID = itm.ID64 inner join SRO_VT_SHARD.dbo._RefObjCommon as common on itm.RefItemID = common.ID where inv.CharID = @CharID and common.CodeName128 = @OldItemCodeName and itm.OptLevel >= @OldItemPlus)
if (@ReplaceID > 0)
begin
Update SRO_VT_SHARD.dbo._Inventory set ItemID = 0 where ItemID = @ReplaceID and CharID = @CharID
select @CharName16 =CharName16 from SRO_VT_SHARD.dbo._Char where CharID=@CharID
exec SRO_VT_SHARD.dbo._ADD_ITEM_EXTERN @CharName16,@NewItemCodeName,1,@NewItemPlus
end
end
END
-- Mod Switch-------------------------------------
IF ( @Operation = '41') AND (@ItemRefID BETWEEN '402882' AND '402893')
BEGIN
EXEC SRO_VT_XEVENT.dbo._ModSwitcher @CharID,@ItemRefID
-----MOD > D13-14-15<-----
END
IF ( @Operation = '41') AND (@ItemRefID BETWEEN '406903' AND '406905')
BEGIN
EXEC SRO_VT_XEVENT.dbo._ModSwitcher @CharID,@ItemRefID
END
-----------------------------Remove jop penalty------------------------
IF ( @Operation = '41') AND @ItemRefID = '402752'
BEGIN DELETE FROM SRO_VT_SHARD.dbo._TimedJob where JobID = '2'AND CharID = @CharID END
----------------------------------------------------------------------
-- Random Silk
IF @Operation = 41 AND @ItemRefID = 402898
BEGIN
DECLARE @RNSOWN varchar(128) = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID)
DECLARE @RNJID INT = (SELECT USERJID FROM SRO_VT_SHARD.._USER WHERE CHARID = @CHARID)
DECLARE @upper_VTX INT = '5'
DECLARE @lower_VTX INT = '25'
DECLARE @random_VTX INT = ROUND(((@upper_VTX - @lower_VTX -1) * RAND() + @lower_VTX), 0)
IF EXISTS (SELECT JID FROM SRO_VT_ACCOUNT..SK_Silk WHERE JID = @RNJID)
BEGIN
UPDATE SRO_VT_ACCOUNT..SK_Silk SET silk_own += @random_VTX WHERE JID = @RNJID
Insert Into _MYTHIC_EXT..iLegend_Tool (Service,Type,Target,Message,Date) Values (1,'Private',@RNSOWN,'Congratulations, You`ve got [' + CAST(@random_VTX AS NVARCHAR) + '] Silk .',Getdate())
END
ELSE
BEGIN
INSERT INTO SRO_VT_ACCOUNT..SK_Silk SELECT @RNJID, @random_VTX, 0, 0
Insert Into _MYTHIC_EXT..iLegend_Tool (Service,Type,Target,Message,Date) Values (1,'Private',@RNSOWN,'Congratulations, You`ve got [' + CAST(@random_VTX AS NVARCHAR) + '] Silk .',Getdate())
END
END
-- Random Job Point
IF @Operation = 41 AND @ItemRefID = 402899
BEGIN
DECLARE @RNJOWN varchar(128) = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID)
DECLARE @JPJID INT = (SELECT USERJID FROM SRO_VT_SHARD.._USER WHERE CHARID = @CHARID)
DECLARE @jpper_VTX INT = '5'
DECLARE @jower_VTX INT = '25'
DECLARE @jrandom_VTX INT = ROUND(((@jpper_VTX - @jower_VTX -1) * RAND() + @jower_VTX), 0)
IF EXISTS (SELECT JID FROM SRO_VT_ACCOUNT..SK_Silk WHERE JID = @JPJID)
BEGIN
UPDATE SRO_VT_ACCOUNT..SK_Silk SET silk_point += @jrandom_VTX WHERE JID = @JPJID
Insert Into _MYTHIC_EXT..iLegend_Tool (Service,Type,Target,Message,Date) Values (1,'Private',@RNJOWN,'Congratulations, You`ve got [' + CAST(@jrandom_VTX AS NVARCHAR) + '] Job Points .',Getdate())
END
ELSE
BEGIN
INSERT INTO SRO_VT_ACCOUNT..SK_Silk SELECT @JPJID, 0, 0, @jrandom_VTX
Insert Into _MYTHIC_EXT..iLegend_Tool (Service,Type,Target,Message,Date) Values (1,'Private',@RNJOWN,'Congratulations, You`ve got [' + CAST(@jrandom_VTX AS NVARCHAR) + '] Job Points .',Getdate())
END
END
-- Random Gold
IF @Operation = 41 AND @ItemRefID = 402897
BEGIN
DECLARE @CharNames varchar(128)
DECLARE @upper_Gold int = '1000000'
DECLARE @lower_Gold int = '10000000'
DECLARE @random_Gold int = ROUND(((@upper_Gold - @lower_Gold -1) * RAND() + @lower_Gold), 0)
SET @CharNames = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID)
UPDATE SRO_VT_SHARD.dbo._Char SET RemainGold = RemainGold + @random_Gold WHERE CharName16 = @CharNames
Insert Into _MYTHIC_EXT..iLegend_Tool (Service,Type,Target,Message,Date) Values (1,'Private',@CharNames,'Congratulations, You`ve got [' + CAST(@random_Gold AS NVARCHAR) + '] Gold .',Getdate())
END
 |
