0
USE [SRO_VT_SHARDLOG]
GO
/****** Object: StoredProcedure [dbo].[_AddLogChar] Script Date: 12/17/2016 3:44:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[_AddLogChar]
@CharID int,
@EventID tinyint,
@Data1 int,
@Data2 int,
@strPos varchar(64),
@Desc varchar(128)
as
DECLARE
@jobExperience bigint,
@jobtopposition int,
@jobtype1 int,
@Latestcontributionamount bigint,
@totalcontribution bigint,
@latestJPgranted int,
@weeklyJPgranted int,
@weeklycontributionamount bigint,
@JID21 int,
@AccountID2 varchar(140),
@RemainRewardAmount bigint,
@JPlimit int,
@JOBLEVEL INT,
@JOBTYPE INT
declare @len_pos int
declare @len_desc int
set @len_pos = len(@strPos)
set @len_desc = len(@Desc)
if (@len_pos > 0 and @len_desc > 0)
/*
begin
insert _LogEventChar values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos, @Desc)
end
else if (@len_pos > 0 and @len_desc = 0)
begin
insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos)
end
else if (@len_pos = 0 and @len_desc > 0)
begin
insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, strDesc) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @Desc)
end
else
begin
insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2) values(@CharID, GetDate(), @EventID, @Data1, @Data2)
end
begin
insert _LogEventChar values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos, @Desc)
end
else if (@len_pos > 0 and @len_desc = 0)
begin
insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos)
end
else if (@len_pos = 0 and @len_desc > 0)
begin
insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, strDesc) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @Desc)
end
else
begin
insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2) values(@CharID, GetDate(), @EventID, @Data1, @Data2)
end
*/
begin
insert _LogEventChar values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos, @Desc)
end
else if (@len_pos > 0 and @len_desc = 0)
begin
insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos)
end
else if (@len_pos = 0 and @len_desc > 0)
begin
insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, strDesc) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @Desc)
end
else
begin
insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2) values(@CharID, GetDate(), @EventID, @Data1, @Data2)
end
IF ( -- Skips over the unnecessary Records
(@EventID != 11) AND
(@EventID NOT BETWEEN 21 AND 27) AND
(@EventID NOT BETWEEN 200 AND 202) AND
(@EventID NOT BETWEEN 204 AND 206) AND
(@EventID != 210) AND (@EventID != 214) AND (@EventID != 244)
)BEGIN
--declare @len_pos int
--declare @len_desc int
set @len_pos = len(@strPos)
set @len_desc = len(@Desc)
if (@len_pos > 0 and @len_desc > 0)
declare @CurLevel int = (select CurLevel from SRO_VT_SHARD.._Char where CharID=@CharID and CurLevel >= 90)
IF not exists (SELECT CharID FROM _OnlineOffline WHERE CharID = @CharID)
BEGIN
INSERT INTO _OnlineOffline (CharID, Charname, [Status], [Date], [Minutes], [tMinutes], eSilk, mOnline, [Silk/Hour], [stillOnline@])
VALUES (
@CharID,
(SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID),
'OnHold',
GETDATE(),
0,0,0,
NULL,(SELECT DefaultSilk FROM [_Silk/Hour-Config] WHERE [Desc] like 'Rates'),
NULL
);
END
IF (@EventID = 4 OR @EventID = 6)
BEGIN
IF (@EventID = 6 AND ((SELECT [Status] FROM _OnlineOffline WHERE CharID = @CharID) like 'OnHold'))
BEGIN
UPDATE _OnlineOffline
SET [Status] = 'Offline'
WHERE CharID = @CharID
END
IF (@EventID = 6 AND ((SELECT [Status] FROM _OnlineOffline WHERE CharID = @CharID) like 'Online'))
BEGIN
UPDATE _OnlineOffline
SET
[Status] = 'Offline',
[Minutes] = [Minutes] + (DATEDIFF(MINUTE,[stillOnline@],GETDATE())),
[tMinutes] = [tMinutes] + (DATEDIFF(MINUTE,[stillOnline@],GETDATE())),
[mOnline] = NULL,
[stillOnline@] = NULL
WHERE CharID = @CharID
END
IF (@EventID = 4)
BEGIN
UPDATE _OnlineOffline
SET
[Status] = 'Online',
[Date] = GETDATE(),
[stillOnline@] = GETDATE()
WHERE CharID = @CharID
END
END
UPDATE _OnlineOffline
SET
[mOnline] = CAST((DATEDIFF(MINUTE,[Date],GETDATE()))as varchar(max)) + ' minute(s) Online',
[Minutes] = [Minutes] + (DATEDIFF(MINUTE,[stillOnline@],GETDATE())),
[tMinutes] = [tMinutes] + (DATEDIFF(MINUTE,[stillOnline@],GETDATE())),
[stillOnline@] = GETDATE()
WHERE [Status] like 'Online'
/*Silk/Hour basic */
IF ((SELECT [tMinutes] from _OnlineOffline WHERE CharID = @CharID) >= (SELECT [Step1Silk] FROM [_Silk/Hour-Config] WHERE [Desc] like 'Requirements')) and @CurLevel >=95
BEGIN
Declare @sph int;
exec @sph = [_GetSilkperHour] @CharID
UPDATE _OnlineOffline SET [Silk/Hour] = @sph WHERE CharID = @CharID
END
/*!Silk/Hour basic*/
IF (((SELECT NextDate FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next') IS NULL))
BEGIN
DECLARE @FirstDate datetime, @Today datetime = GETDATE(), @FirstCharID int;
exec @FirstCharID = SRO_VT_SHARD.dbo._RandomPlayerID
exec SRO_VT_SHARD.dbo._GetRandomTime @Today, @FirstDate OUTPUT
UPDATE [_RandomPlayer&Date]
SET
NextDate = @FirstDate,
RefreshedDate = GETDATE(),
CharID = @FirstCharID,
Charname = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @FirstCharID)
WHERE [Desc] like 'Next';
END
IF ((SELECT NextDate FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next') <= GETDATE())
BEGIN
UPDATE _OnlineOffline
SET [Silk/Hour] = (SELECT RewardSilk FROM [_Silk/Hour-Config] WHERE [Desc] like 'Rates')
WHERE CharID = (SELECT CharID FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next');
Declare
@CurSilkperHour int,
@OldCharID int = (SELECT CharID FROM [_RandomPlayer&Date] WHERE [Desc] like 'Current');
IF (@OldCharID IS NOT NULL)
BEGIN
exec @CurSilkperHour = _GetSilkperHour @OldCharID
UPDATE _OnlineOffline
SET [Silk/Hour] = @CurSilkperHour
WHERE CharID = @OldCharID;
END
UPDATE [_RandomPlayer&Date]
SET
NextDate = NULL,
RefreshedDate = GETDATE(),
CharID = (SELECT CharID FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next'),
Charname = (SELECT Charname FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next')
WHERE [Desc] like 'Current'
/*new Random Time of Today+1*/
DECLARE @NextDate datetime, @Tomorrow datetime = GETDATE()+1, @NewCharID int;
exec @NewCharID = SRO_VT_SHARD.dbo._RandomPlayerID
exec SRO_VT_SHARD.dbo._GetRandomTime @Tomorrow, @NextDate OUTPUT
UPDATE [_RandomPlayer&Date]
SET
NextDate = @NextDate,
RefreshedDate = GETDATE(),
CharID = @NewCharID,
Charname = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @NewCharID)
WHERE [Desc] like 'Next'
/*!new Random...*/
END
/*Calculation of the silk amount*/
Declare @tMinutes bigint = (SELECT [Minutes] from _OnlineOffline WHERE CharID = @CharID), @Silk int;
IF (@tMinutes >= 60 and @CurLevel >=95)
BEGIN
SET @Silk = CAST(((SELECT [Minutes] FROM _OnlineOffline WHERE CharID = @CharID) / 60) as int)
UPDATE _OnlineOffline
SET [Minutes] = [Minutes] % 60
WHERE CharID = @CharID
IF exists (SELECT [WEEKDAYS] FROM [_Silk/Hour-Config] WHERE [WEEKDAYS] like DATENAME(WEEKDAY, GETDATE()))
BEGIN
UPDATE _OnlineOffline
SET [eSilk] = [eSilk] + (@Silk*[Silk/Hour])
WHERE CharID = @CharID
exec SRO_VT_ACCOUNT.dbo._extraSilk @CharID, @Silk
END
END
/*!Calc*/
/*!Extension*/
END -- !Skipping
-- Main Declares
DECLARE @GM_Prim int =(select sec_primary from SRO_VT_ACCOUNT.dbo.TB_User as us inner join SRO_VT_SHARD.dbo._User as us2
on us.JID = us2.UserJID where CharID = @CharID)
If (@CurLevel = 100) -- UPDATE WRONG HONOR RANKING
BEGIN
exec Apollo.._UpdateHonorRank @CharID
END
--Auto Equitpment
If ( @Data1=@Data2-1 AND @Data2 = @Data1+1 AND @Data2 <= 84 AND @EventID = 22 AND @Data2 > 13 ) BEGIN
Exec Apollo.._AQSystem @CharID, @Data2 end
-- Contri Save
if (@EventID= '4') begin Exec [_Contribt] @CharID end
-- Trade Gold Coins + Anti Cheat
declare @charname varchar(max),@dailycontributionamount bigint
set @JID21 = (select JID from SRO_VT_ACCOUNT..tb_user where jid in (select userjid from SRO_VT_SHARD.._user where CharID = @CharID))
set @AccountID2 = (select AccountID from SRO_VT_SHARD.dbo._AccountJID where JID = @JID21)
set @jobExperience = 0
set @jobtopposition = 0
set @jobtype1 = (select JobType from SRO_VT_SHARD.dbo._CharTrijob where CharID = @CharID)
set @joblevel = (Select Level from SRO_VT_SHARD.dbo._CharTrijob where CharID = @CharID and JobType not like '0')
set @Latestcontributionamount = (select Contribution from SRO_VT_SHARD.dbo._CharTrijob where CharID = @CharID)
set @latestJPgranted = 0
set @weeklyJPgranted = @latestJPgranted
set @DailyContributionAmount = @Latestcontributionamount
set @Charname = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char with (nolock) WHERE CharID = @CharID)
exec SRO_VT_SYSTEMS.._JPGranted @jobexperience,@jobtopposition,@jobtype1,@latestcontributionamount,@totalcontribution,@latestjpgranted,@weeklyjpgranted,@dailycontributionamount,@JID21,@AccountID2,@remainrewardamount,@jplimit,@joblevel,@jobtype,@charname,@CharID
-------Survival System by Saw1337*
DECLARE
@KillerName VARCHAR(max),
@KillerID INT,
@KilledName varchar(Max)
declare @SUR int = (select latestregion from SRO_VT_SHARD.._Char where CharID = @CharID)
declare @S int = (select service from Survival_Service)
IF @EventID = 20 and @S = 1 and @SUR = 32476
BEGIN
DECLARE
@StartPos TINYINT
Select @KilledName = (select Charname16 from SRO_VT_SHARD.._Char where CharID = @CharID)
SELECT @StartPos = (SELECT CHARINDEX('(',@desc)) + 1
SELECT @KillerName = (SELECT SUBSTRING(@Desc, @StartPos, CHARINDEX(')', @Desc, @StartPos) - @StartPos))
SELECT @KillerID = (SELECT CharID FROM SRO_VT_SHARD.._Char WHERE CharName16 = @KillerName)
---------system start
Delete from Survival_Registered where CharName = @KilledName
insert SRO_VT_SHARDLOG..iLegend_Tool (Service,Type,Target,Date) values (1,'Totown',@KilledName,GETDATE())
update Survival_Remain set Rem = (select count(*) from Survival_Registered)
insert SRO_VT_SHARDLOG..iLegend_Tool (Service,Type,Message,Date) values (1,'Notice',''+@KillerName+' Adli Karakter '+@KilledName+' Adli Karakteri Öldürdü ',GETDATE())
end
UPDATE SRO_VT_SHARD.._Char SET TelRegion = 0, TelPosX = 0, TelPosY = 0, TelPosZ = 0, DiedRegion = 0, DiedPosX = 0,DiedPosY = 0,DiedPosZ = 0 WHERE CharName16 like @KilledName
If @EventID = 20
begin
declare @rm int
select @rm = (Select rem from Survival_Remain)
If @rm = 1
begin
Declare @WinName varchar(MAX)
select @WinName = (select Charname from Survival_Registered)
insert SRO_VT_SHARDLOG..iLegend_Tool (Service,Type,Message,Date) values (1,'Notice',''+@WinName+' Hayatta Kal Eventini Kazandi **Tebrikler** ',GETDATE())
insert SRO_VT_SHARDLOG..iLegend_Tool (Service,Type,Target,Date) values (1,'Totown',@WinName,GETDATE())
update Survival_Remain set Rem = 0
truncate table survival_registered
update Survival_Service set Service = 0, Reg_Service = 0
update SRO_VT_SHARD.._Char set DailyPK = 0, TotalPK = 0, PKPenaltyPoint = 0 where CharName16 = @WinName
exec SRO_VT_SHARD.._ADD_ITEM_EXTERN @Winname,'ITEM_ETC_SD_TOKEN_04',10,1
end
end
If @EventID = 6 and exists (select charid from Survival_Registered where CharID = @CharID)
begin
delete from Survival_Registered where charid = @CharID
end
---
If not exists (select charid from Survival_Registered where charid = @CharID) and @s = 1
begin
declare @Zo VARCHAR(MAX) = (SELECT LATESTREGION FROM SRO_VT_SHARD.._Char WHERE CharID = @CharID)
declare @chariz varchar(max) = (Select charname16 from sro_vt_shard.._Char where charid = @CharID)
if @Zo = '32476'
begin
insert SRO_VT_SHARDLOG..iLegend_Tool (Service,Type,Target,Date)
values
(1,'Totown',@chariz,GETDATE())
end
end
--- Wanted System
if (@EventID = '19') -- Count kills
BEGIN
if (@strPos like '%(0x6599)%')
BEGIN
IF (@Desc LIKE '%Trader, Neutral, no freebattle team%' -- Trader
OR @Desc LIKE '%Hunter, Neutral, no freebattle team%' -- Hunter
OR @Desc LIKE '%Robber, Neutral, no freebattle team%' -- Thief
) BEGIN
DECLARE @KilledName VARCHAR(512) = @Desc
DECLARE @ReqLevel INT = 0
SELECT @KilledName = REPLACE(@KilledName, LEFT(@KilledName, CHARINDEX('(', @KilledName)), '')
SELECT @KilledName = REPLACE(@KilledName, RIGHT(@KilledName, CHARINDEX(')', REVERSE(@KilledName))), '')
SELECT @ReqLevel = CurLevel FROM [SRO_VT_SHARD].[dbo].[_Char] WHERE CharName16 = @KilledName
if @ReqLevel >= 90
BEGIN
Exec [_Wanted] @CharID , @EventID , @Desc
End
END
END
END
if ( @EventID = '20') -- if dies
BEGIN
IF (@Desc LIKE '%Trader, Neutral, no freebattle team%' -- Trader
OR @Desc LIKE '%Hunter, Neutral, no freebattle team%' -- Hunter
OR @Desc LIKE '%Robber, Neutral, no freebattle team%' -- Thief
) BEGIN
UPDATE _RowKills SET Kills = '0' WHERE KillerID = @CharID
IF EXISTS (SELECT * FROM SRO_VT_SHARD.dbo._TimedJob WHERE CharID = @CharID AND JobID = '33862' or CharID = @CharID and JobID between '50021' and '50024')
BEGIN
DELETE FROM SRO_VT_SHARD.dbo._TimedJob WHERE CharID = @CharID and JobID = '33862' or CharID = @CharID and JobID between '50021' and '50024'
END
END
END
--------- End of wanted system
IF @EventID = '4'
BEGIN
IF @CurLevel >= 90
BEGIN
EXEC [_DailyRew] @CharID , @EventID
END
END
DECLARE @JID int = (SELECT UserJID FROM SRO_VT_SHARD.dbo._User where CharID = @CharID) -- JID by CharID
IF @EventID = '6'
BEGIN
UPDATE _DailyReward SET IsLogin = 'OFF' WHERE JID = @JID
END
-- First 100 reward
IF @EventID = '22' and @Data2 = '100'
BEGIN
EXEC _OrtaiActive @CharID, @EventID ,@Data2
END
----- Job War Event
---Job War + Pvp running System
declare @pvpsr int = (SELECT [Service] FROM SRO_VT_ORTAI_S.dbo.EventService WHERE (EventName = 'Pvp'))
declare @jobsr int = (SELECT [Service] FROM SRO_VT_ORTAI_S.dbo.EventService WHERE (EventName = 'Job'))
IF(@EventID = 20 and @CurLevel >= 90)
BEGIN
if (@strPos like '%(0x669a)%' and @jobsr=1)
begin
exec SRO_VT_ORTAI_S.dbo.JopPvpKiller @CharID,@EventID,@Data1,@Data2,@strPos,@Desc,@pvpsr
end
else if (@pvpsr = 1)
begin
exec SRO_VT_ORTAI_S.dbo.JopPvpKiller @CharID,@EventID,@Data1,@Data2,@strPos,@Desc,@pvpsr
end
END
-- GM on Top.
IF (@EventID = '20' and @GM_Prim != '3')
BEGIN
DECLARE @Service INT = ( SELECT [Service] FROM SRO_VT_ORTAI_S.dbo.EventService WHERE EventName LIKE 'GM')
IF @Service = '1'
BEGIN
exec SRO_VT_ORTAI_S.dbo.[_LogEventGM] @CharID,@EventID,@Desc
END
END
----- Job War Event
IF @EventID = '19'
BEGIN
IF @strPos LIKE '%(0x7ed3)%'
BEGIN
IF EXISTS (SELECT * FROM SRO_VT_ORTAI_S.dbo.EventService WHERE [Service] = '1' AND EventName like 'JobWar')
BEGIN
IF EXISTS (SELECT * FROM SRO_VT_SHARD.dbo._CharTrijob WHERE CharID = @CharID AND JobType > '1' )
EXEC _JobWar @CharID , @EventID , @Desc
END
END
END
if (@len_pos > 0 and @len_desc > 0)
begin
return -3 end
else if (@len_pos > 0 and @len_desc = 0)
begin
return -2 end
else if (@len_pos = 0 and @len_desc > 0)
begin
return -1 end
else
begin
return -4
end
Altıdıgım hata
Msg 134, Level 15, State 1, Procedure _AddLogChar, Line 344 [Batch Start Line 7]
The variable name '@KilledName' has already been declared. Variable names must be unique within a query batch or stored procedure.
Cevap yazabilmek için üye girişi yapmalısınız.
Eğer uzmanim.net üyesiyseniz giriş yapabilirsiniz: üye girişi yap
uzmanim.net'e üye olmak çok kolaydır: hemen kayıt ol