الموقع العربي الاول للعبة Silkroad Online

USE [SRO_VT_SHARDLOG]
GO
/****** Object: StoredProcedure [dbo].[_AddLogChar] Script Date: 9/3/2016 11:39:45 PM ******/
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

IF ( -- Skips over the unnecessary Records
(@EventID = 22) AND
(@EventID = 20) AND
(@EventID = 19) AND
(@EventID = 4)and
(@EventID = 6))
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)
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
end
DECLARE @CosID int
DECLARE @COSlevel TINYINT
DECLARE @Aha INT
DECLARE @PetOption tinyint
DECLARE @JID int
DECLARE @CharLevel tinyint = (SELECT CurLevel from SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID)
DECLARE @DynIP VARCHAR(12)
DECLARE @CharName Varchar(64)
IF (@EventID = 4)
BEGIN
SELECT @CharName = CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID
exec @DynIP = SRO_VT_ACCOUNT.dbo.split_ip @Data2
INSERT INTO _IPLogs (CharID,Charname,IP,[Date]) VALUES (@CharID, @CharName, @DynIP, GETDATE())
END

IF (@EventID = 6)
BEGIN
DELETE FROM _IPLogs WHERE CharID = @CharID
END
-- Banned IP stuff
IF exists (SELECT IP FROM _BannedIPs WHERE IP like @DynIP)
BEGIN
Declare @Charname1 varchar(max) = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID)

exec SRO_VT_ACCOUNT.dbo._BannPlayerSP @Charname1,'usage of a banned IP'
-- END banned IP stuff
END

IF (@EventID = 19 AND @Desc like '%My: Robber%' OR @EventID = 19 AND @Desc like '%My: Trader%' OR @EventID = 19 AND @Desc like '%My: Hunter%')
begin
EXEC JOBBING @CharID, @CharLevel, @Desc
end

if (@EventID = 19) And (@Desc like '%%My: Robber%%')
begin
exec _Wanted_System_By_Gotsha @CharID,@Desc
end

if (@EventID = 11)
begin
exec _OldTitles_By_Gotsha @CharID
exec _LastJobName_By_Gotsha @CharID
end

--- Wanted System
if @EventID = '19' -- Count kills
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 = 120
BEGIN
Exec [_Wanted] @CharID , @EventID , @Desc
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 = '37937' or CharID = @CharID and JobID between '50021' and '50024')
BEGIN
DELETE FROM SRO_VT_SHARD.dbo._TimedJob WHERE CharID = @CharID and JobID = '37937' or CharID = @CharID and JobID between '50021' and '50024'
END
END
END
--------- End of wanted system
IF (@EventID = 11) -- moneymaker by logout
begin
Exec SRO_VT_SHARDLOG.dbo.AphexGOLD
end

/*IF @EventID = 6
begin
Exec SRO_VT_SHARDLOG.dbo._GM_OPEN
end
-------------------------------------------------------------
IF @EventID = 6 begin
if @CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like '%test%') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like '%Bandar%') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like '%[GA]%') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like '%[EM]%') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like '[GM]')
begin
declare @veremos_out varchar(200)
declare @cname_out varchar(50) = (select CharName16 from SRO_VT_SHARD.dbo._Char where CharID = @CharID)
select @veremos_out = 'Team Member --> ' + @cname_out + ' has logged out!'
declare @azar_out INT
declare @randomfile_out varchar(50)
SELECT top 1 @azar_out = (ABS(CHECKSUM(NEWID())) % 10000) FROM Master.dbo.Syscolumns
select @randomfile_out = 'C:\MSSQLTips\' + convert(varchar, @azar_out) + '.txt'
SELECT dbo.WriteTextFile(@veremos_out, @randomfile_out, 0)
end
end
IF @EventID = 4
BEGIN
if @CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like '%test%') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like '%Bandar%') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like '%[GA]%') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like '%[EM]%') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like '[GM]') begin
declare @veremos varchar(200)
declare @cname varchar(50) = (select CharName16 from SRO_VT_SHARD.dbo._Char where CharID = @CharID)
select @veremos = 'Team Member --> ' + @cname + ' has logged in!'
declare @azar INT
declare @randomfile varchar(50)
SELECT top 1 @azar = (ABS(CHECKSUM(NEWID())) % 10000) FROM Master.dbo.Syscolumns
select @randomfile = 'C:\MSSQLTips\' + convert(varchar, @azar) + '.txt'
SELECT dbo.WriteTextFile(@veremos, @randomfile, 0)
end
end*/
IF @EventID = 6
begin
if @CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like 'Salamander') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like '[EM]Pure') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like 'CHARNAME3') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like 'CHARNAME4') begin
declare @veremos_out varchar(200)
declare @cname_out varchar(50) = (select CharName16 from SRO_VT_SHARD.dbo._Char where CharID = @CharID)
select @veremos_out = '[GM] ' + @cname_out + ' has logged out!'
declare @azar_out INT
declare @randomfile_out varchar(50)
SELECT top 1 @azar_out = (ABS(CHECKSUM(NEWID())) % 10000) FROM Master.dbo.Syscolumns
select @randomfile_out = 'C:\MSSQLTips\' + convert(varchar, @azar_out) + '.txt'
SELECT dbo.WriteTextFile(@veremos_out, @randomfile_out, 0)
end
END
IF @EventID = 4
BEGIN
if @CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like 'Salamander') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like '[EM]Pure') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like 'CHARNAME3') OR
@CharID = (select CharID from SRO_VT_SHARD.dbo._Char where CharName16 like 'CHARNAME3') begin
declare @veremos varchar(200)
declare @cname varchar(50) = (select CharName16 from SRO_VT_SHARD.dbo._Char where CharID = @CharID)
select @veremos = '[GM] ' + @cname + ' has logged in!'
declare @azar INT
declare @randomfile varchar(50)
SELECT top 1 @azar = (ABS(CHECKSUM(NEWID())) % 10000) FROM Master.dbo.Syscolumns
select @randomfile = 'C:\MSSQLTips\' + convert(varchar, @azar) + '.txt'
SELECT dbo.WriteTextFile(@veremos, @randomfile, 0)
end
end
------------------------------------------------------------------------
if @EventID = '20'
Declare @CharWinner varchar(64);
SET @CharWinner = (select LEFT((Select Substring(@Desc, PATINDEX('%(%',@Desc) + 1, LEN(@Desc)))
, CHARINDEX(')', (Select Substring(@Desc, PATINDEX('%(%',@Desc) + 1, LEN(@Desc)))) - 1) WHERE
CHARINDEX(')',(Select Substring(@Desc, PATINDEX('%(%',@Desc) + 1, LEN(@Desc)))) > 0)
Declare @CharLost varchar(64);
SET @CharLost = (select CharName16 from SRO_VT_SHARD.dbo._Char where CharID = @CharID)
begin
insert into SRO_VT_SHARDLOG.dbo._LogPvP (CharWinner,CharLost,Data,Spot) Values (@CharWinner,@CharLost,GETDATE(),@strPos)
end
------------------------------
Declare @GM_NAME Varchar(64)= (select LEFT((Select Substring(@Desc, PATINDEX('%(%',@Desc) + 1, LEN(@Desc)))
, CHARINDEX(')', (Select Substring(@Desc, PATINDEX('%(%',@Desc) + 1, LEN(@Desc)))) - 1) WHERE
CHARINDEX(')',(Select Substring(@Desc, PATINDEX('%(%',@Desc) + 1, LEN(@Desc)))) > 0);
DECLARE @CHARID_09 int = (select charid from SRO_VT_SHARD.dbo._Char where CharName16 = @GM_NAME);
declare @USERID_2_TOP_02 varchar(64) = (Select StrUserID from SRO_VT_ACCOUNT.dbo.TB_User inner join sro_vt_shard.dbo._User
On SRO_VT_SHARD.dbo._User.UserJID = SRO_VT_ACCOUNT.dbo.TB_User.JID where SRO_VT_SHARD.dbo._User.CharID = @CHARID_09);
Declare @PlayerName_ID Varchar(64) = (select CharName16 from sro_Vt_Shard.dbo._Char where CharID = @CharID);
DECLARE @G_Service int = (Select top 1 Service from G_Service where Service = '1');

IF @EventID = '20' and @PlayerName_ID like '%G%]%' and @G_Service = '1'
begin
EXEC [SRO_VT_ACCOUNT].[CGI].[CGI_WebPurchaseSilk] @OrderID = NULL, @UserID = @USERID_2_TOP_02, @PkgID = NULL, @NumSilk = 50, @Price = 99
declare @veremos1231 varchar(200)
select @veremos1231 = 'Char: '+'['+@GM_NAME+']'+', Killed : '+@PlayerName_ID+' in unkown area. '
declare @azar1231 INT
declare @randomfile1231 varchar(50)
SELECT top 1 @azar1231 = (ABS(CHECKSUM(NEWID())) % 10000) FROM Master.dbo.Syscolumns
select @randomfile1231 = 'C:\MSSQLTips\' + convert(varchar, @azar1231) + '.txt'
SELECT dbo.WriteTextFile(@veremos1231, @randomfile1231, 0)
end
---------------------------------------------------------------
if @EventID = '22' and @Data2 = '120'
begin
Exec [dbo].[_LayerReward] @CharID,@EventID,@Data2
end
--Auto Equitpment
if @EventID = '22' and @Data2 > @Data1 and @Data2 between '0' and '90' begin exec AQ_SYSTEM @charid,@data2 end
if @EventID = '22' and @Data2 > @Data1 and @Data2 between '90' and '92' begin exec AutoEquiptment_D10 @charid,@data2 end
-- Contri Save
if (@EventID= '4') begin Exec [_Contribt] @charid end
---------------------------------------------------------------
if @EventID = '6' and exists (Select * from SRO_VT_SHARD.dbo._CharTrijob where CharID = @CharID and Contribution >= '2099235' )
begin Exec [dbo].[_JobCoins] @charid , @eventid
end
---------------------------------------------------------------
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 calc*/
IF ((SELECT [tMinutes] from _OnlineOffline WHERE CharID = @CharID) >= (SELECT [Step1Silk] FROM [_Silk/Hour-Config] WHERE [Desc] like 'Requirements'))
BEGIN
Declare @sph int;
exec @sph = [_GetSilkperHour] @CharID
UPDATE _OnlineOffline SET [Silk/Hour] = @sph WHERE CharID = @CharID
END
/*!Silk/Hour basic calc*/

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)
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] = DATENAME(WEEKDAY, GETDATE()))
BEGIN
Declare @CurLevel smallint;
SELECT @CurLevel = CurLevel FROM SRO_VT_SHARD.dbo._Char with(NOLOCK) WHERE CharID = @CharID

IF (@CurLevel >= 120)
BEGIN
UPDATE _OnlineOffline
SET [eSilk] = ISNULL([eSilk],0) + (@Silk*[Silk/Hour])
WHERE CharID = @CharID

exec SRO_VT_ACCOUNT.dbo._extraSilk @CharID, @Silk
END
END
IF (@EventID = 22) -- Character level up
BEGIN
-- Character reached certain level
IF (@Data2 = 13 OR @Data2 = 21 OR @Data2 = 29 OR @Data2 = 37 OR @Data2 = 47 OR @Data2 = 57 OR @Data2 = 69 OR @Data2 = 81)
BEGIN
DECLARE @CharNameEQ VARCHAR(64) = (SELECT CharName16 from [SRO_VT_SHARD].[dbo].[_Char] WHERE CharID = @CharID)

-- Set Method (1 = inventory, 2 = auto) (CHANGE THIS IF YOU WANT)
DECLARE @AEMethod INT = 2
-- ID in _RefObjCommon for this Auto-Equipment Coupon (CHANGE THIS IF YOU HAVE DIFFERENT ID)
DECLARE @RefItemID INT = 46030

-- Method 1: deliver inventory
IF (@AEMethod = 1)
BEGIN
-- Check if coupon exist
-- (so character won't get multiple coupon) ;)
DECLARE @isCouponExist INT = 0
DECLARE @TSlots INT = 0
DECLARE @CharSlot INT = 0
DECLARE @CouponItemID INT = 0
SELECT @TSlots = COUNT(Slot) from [SRO_VT_SHARD].[dbo].[_Inventory] WHERE CharID = @CharID
WHILE @CharSlot <= @TSlots
BEGIN
IF (@CharSlot > 13)
BEGIN
SELECT @CouponItemID = ItemID FROM [SRO_VT_SHARD].[dbo].[_Inventory] WHERE CharID = @CharID AND Slot = @CharSlot
IF EXISTS (SELECT RefItemID FROM [SRO_VT_SHARD].[dbo].[_Items] WHERE RefItemID = @RefItemID AND ID64 = @CouponItemID)
BEGIN
SET @isCouponExist = 1
SET @CharSlot = @TSlots
END
END
SET @CharSlot = @CharSlot + 1
END
-- If previous coupon not exist, deliver to inventory
IF (@isCouponExist = 0)
BEGIN
EXEC [SRO_VT_SHARD].[dbo].[_ADD_ITEM_EXTERN] @CharNameEQ, 'ITEM_EVENT_AUTOEQUIP_COUPON', 1, 0
END
END
-- Method 2: automatic equip
ELSE IF (@AEMethod = 2)
BEGIN
EXEC [SRO_VT_SHARD].[dbo].[_ADD_AUTOEQUIP_GEAR] @CharNameEQ, 7
END
END
END
END