[Release] Alex scripts

JujiPoli · 04/29/2017, 15:15

Hello Shaiya,

Today I am releasing my custom scripts (sql)

All the scripts I'm releasing here are my ideas and written by me

What you need for the script to work correctly:

1-

(won't work if you don't have it)

2- Go in PS_ChatLog -> Programmability -> Stored Procedures -> usp_Insert_Chat_Log_E click on modify

After this:

IF( LEN(@dd) = 1 )
BEGIN
SET @dd = '0' + @dd
END

Put this:

Quote:

PHP Code:

DECLARE @Charname varchar(max), @CharIDNotice varchar(max), @return_value int SET @Charname = (SELECT Charname FROM PS_GameData.dbo.Chars WHERE CharID = @CharID) SET @CharIDNotice = @CharID SET @return_value ='0'

3-

([OPTIONAL] Needed for the notice to player)

1 - First script is a bad word filter,
How does it work? Every time a player says a bad word like "bitch"
it will give a warning to the player.
Once the player has received 3 warnings they will be kicked.

2 - Second script is a Transfer DP
How does it work? PM a player and say the amount to transfer (! Alex !transfer 1000)

3 - Third script is a Auto Notice spawner
How does it work? It will spawn a random notice selected via a table each 1 hour

4 - Fourth script is my custom ChatLog with the scripts ^

------------------- BAD WORD FILTER -------------------

Bad Word Filter Script

1 - First we create a new table named Warning in PS_ChatLog

Code:

USE [PS_ChatLog]
GO

/****** Object:  Table [dbo].[Warning]    Script Date: 4/29/2017 14:51:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Warning](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[UserUID] [int] NOT NULL,
	[Warning] [int] NOT NULL
) ON [PRIMARY]

GO

2 - Now we will put the script in usp_Insert_Chat_Log_E
Put this after the insert of Chat Log:

The sentence: "Do not say bad words please" is an example, you can change the sentence to whatever you want

With the /ntplayer (notice to player):

Quote:

Code:

-- fist we select the bad words
IF (@ChatData like '%bitch%' or @ChatData like '%idiot%')
BEGIN -- 1

-- now we select the chat type
-- ChatType : 1 = Normal Chat | 2 = Whisper (!) | 3 = Guild | 4 = Group (@) (/raid) | 5 = Trade | 6 = /yelling | 7 = Area

IF (@ChatType = '1' or @ChatType = '4' or @ChatType = '5' or @ChatType = '6' or @ChatType = '7')
BEGIN -- 2

DECLARE @NoticeWarning varchar(max), @NoticeWarningMessage varchar(max)

IF (SELECT UserUID FROM PS_ChatLog.dbo.Warning WHERE UserUID = @UserUID) IS NULL
BEGIN -- 3

-- We insert in chatlog.dbo.warning the UserUID, CharID, Charname and the number of Warning
-- Since it the first warning of the user we will put warning to 0

INSERT INTO PS_ChatLog.dbo.Warning
		(UserUID, Warning)
VALUES (@UserUID, '0')

SET @NoticeWarningMessage = 'Do not say bad words please 1/3.'

SET @NoticeWarning = N'/ntplayer ' + @CharIDNotice + ' Hello ' + @Charname + '. ' + @NoticeWarningMessage
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @NoticeWarning

END -- 3

IF (SELECT UserUID FROM PS_ChatLog.dbo.Warning WHERE UserUID = @UserUID) IS NOT NULL
BEGIN -- 4

BEGIN
UPDATE PS_ChatLog.dbo.Warning SET Warning = Warning + 1 WHERE UserUID = @UserUID
END

DECLARE @HowManyWarning int

SET @HowManyWarning = (SELECT Warning FROM PS_ChatLog.dbo.Warning WHERE UserUID = @UserUID)

IF (@HowManyWarning = '1') -- Second warning
BEGIN -- 5

SET @NoticeWarningMessage = 'Do not say bad words please 2/3.'

SET @NoticeWarning = N'/ntplayer ' + @CharIDNotice + ' Hello ' + @Charname + '. ' + @NoticeWarningMessage
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @NoticeWarning

END -- 5

IF (@HowManyWarning = '2') -- Last warning
BEGIN -- 6

SET @NoticeWarningMessage = 'Do not say bad words please 3/3.'

SET @NoticeWarning = N'/ntplayer ' + @CharIDNotice + ' Hello ' + @Charname + '. ' + @NoticeWarningMessage
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @NoticeWarning

DECLARE @KickWarning varchar(max)

-- like this it will instant kick without let time to the player to see the notice
-- if you want put a delay before it kick the player you can do like this:
-- WAITFOR DELAY '00:05';
-- It will wait for 5 seconds before kick the player

SET @KickWarning = N'/kickcid ' + @CharIDNotice
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @KickWarning

BEGIN
DELETE FROM PS_ChatLog.dbo.Warning WHERE UserUID = @UserUID
END

END -- 6
END -- 4
END -- 2
END -- 1

With the /nt (public notice):

Quote:

Code:

-- fist we select the bad words
IF (@ChatData like '%bitch%' or @ChatData like '%idiot%')
BEGIN -- 1

-- now we select the chat type
-- ChatType : 1 = Normal Chat | 2 = Whisper (!) | 3 = Guild | 4 = Group (@) (/raid) | 5 = Trade | 6 = /yelling | 7 = Area

IF (@ChatType = '1' or @ChatType = '4' or @ChatType = '5' or @ChatType = '6' or @ChatType = '7')
BEGIN -- 2

DECLARE @NoticeWarning varchar(max), @NoticeWarningMessage varchar(max)

IF (SELECT UserUID FROM PS_ChatLog.dbo.Warning WHERE UserUID = @UserUID) IS NULL
BEGIN -- 3

-- We insert in chatlog.dbo.warning the UserUID, CharID, Charname and the number of Warning
-- Since it the first warning of the user we will put warning to 0

INSERT INTO PS_ChatLog.dbo.Warning
		(UserUID, Warning)
VALUES (@UserUID, '0')

SET @NoticeWarningMessage = 'Do not say bad words please 1/3.'

SET @NoticeWarning = N'/nt ' + ' Hello ' + @Charname + '. ' + @NoticeWarningMessage
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @NoticeWarning

END -- 3

IF (SELECT UserUID FROM PS_ChatLog.dbo.Warning WHERE UserUID = @UserUID) IS NOT NULL
BEGIN -- 4

BEGIN
UPDATE PS_ChatLog.dbo.Warning SET Warning = Warning + 1 WHERE UserUID = @UserUID
END

DECLARE @HowManyWarning int

SET @HowManyWarning = (SELECT Warning FROM PS_ChatLog.dbo.Warning WHERE UserUID = @UserUID)

IF (@HowManyWarning = '1') -- Second warning
BEGIN -- 5

SET @NoticeWarningMessage = 'Do not say bad words please 2/3.'

SET @NoticeWarning = N'/nt ' + ' Hello ' + @Charname + '. ' + @NoticeWarningMessage
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @NoticeWarning

END -- 5

IF (@HowManyWarning = '2') -- Last warning
BEGIN -- 6

SET @NoticeWarningMessage = 'Do not say bad words please 3/3.'

SET @NoticeWarning = N'/nt ' + ' Hello ' + @Charname + '. ' + @NoticeWarningMessage
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @NoticeWarning

DECLARE @KickWarning varchar(max)

-- like this it will instant kick without let time to the player to see the notice
-- if you want put a delay before it kick the player you can do like this:
-- WAITFOR DELAY '00:05';
-- It will wait for 5 seconds before kick the player

SET @KickWarning = N'/kickcid ' + @CharIDNotice
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @KickWarning

BEGIN
DELETE FROM PS_ChatLog.dbo.Warning WHERE UserUID = @UserUID
END

END -- 6
END -- 4
END -- 2
END -- 1

------------------- DP Transfer -------------------

DP TRANSFER Script

1 - First we create a new table named Transfer in PS_ChatLog

Code:

USE [PS_ChatLog]
GO

/****** Object:  Table [dbo].[Transfer]    Script Date: 4/29/2017 18:35:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Transfer](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Charname] [varchar](max) NOT NULL,
	[TotalTransfer] [int] NOT NULL,
	[TransferName] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

2 - Now we will put the script in usp_Insert_Chat_Log_E
Put this after the insert of Chat Log:

With the /ntplayer (notice to player):

Quote:

Code:

IF (@Chatdata like '%!transfer%' and @ChatType = '2')
BEGIN -- 1

DECLARE @TransferNotice varchar(max), @TransferPoint int, @TransferPoints varchar(max), @CheckPointsOkey varchar(max), @TransferPointsReward varchar(max), @CheckOkayTransfer int

-- Check if the user got point
SET @CheckPointsOkey = (SELECT Point FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)

-- remove the !transfer to get the amount
SET @TransferPoint = (select REPLACE (@chatdata, '!transfer ', '' ))

-- if the user got > or = 1000 points he can trade (can change)
IF (@TransferPoint >='1000')
BEGIN -- 2

-- we check if when we remove the !transfer only numbers left
SET @CheckOkayTransfer = (Select Isnumeric(@TransferPoint))

IF (@CheckOkayTransfer ='1')
BEGIN -- 3


SET @TransferPoints = @TransferPoint -- For the notice
IF (@CheckPointsOkey >= @TransferPoint)
BEGIN -- 4

INSERT INTO PS_ChatLog.dbo.Transfer
		(Charname, TotalTransfer, TransferName)
VALUES (@Charname, @TransferPoint, @TargetName)

DECLARE @UserUIDTransfer int, @CharIDNoticeTransfer varchar(max)

-- We select the UserUID of the player pmed
SET @UserUIDTransfer = (SELECT top 1 UserUID FROM PS_Gamedata.dbo.Chars WHERE Charname = @Targetname)

-- We remove the point of the user
UPDATE PS_UserData.dbo.Users_Master SET Point = Point - @TransferPoint WHERE UserUID = @UserUID

-- We add the point to the user pmed
UPDATE PS_UserData.dbo.Users_Master SET Point = Point + @TransferPoint WHERE UserUID = @UserUIDTransfer

SET @TransferNotice = N'/ntplayer ' + @CharIDNotice + ' You transfered ' + @TransferPoints + ' points to ' + @Targetname
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @TransferNotice

-- We select the charname of pmed
SET @CharIDNoticeTransfer = (SELECT top 1 CharID FROM PS_GameData.dbo.Chars WHERE Charname = @TargetName)

SET @TransferNotice = N'/ntplayer ' + @CharIDNoticeTransfer + ' ' + @Charname + ' transfered you ' + @TransferPoints + ' points.'
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @TransferNotice
END -- 4
END -- 3
END -- 2
END -- 1

With the /nt (public notice):

Quote:

Code:

IF (@Chatdata like '%!transfer%' and @ChatType = '2')
BEGIN -- 1

DECLARE @TransferNotice varchar(max), @TransferPoint int, @TransferPoints varchar(max), @CheckPointsOkey varchar(max), @TransferPointsReward varchar(max), @CheckOkayTransfer int

-- Check if the user got point
SET @CheckPointsOkey = (SELECT Point FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)

-- remove the !transfer to get the amount
SET @TransferPoint = (select REPLACE (@chatdata, '!transfer ', '' ))

-- if the user got > or = 1000 points he can trade (can change)
IF (@TransferPoint >='1000')
BEGIN -- 2

-- we check if when we remove the !transfer only numbers left
SET @CheckOkayTransfer = (Select Isnumeric(@TransferPoint))

IF (@CheckOkayTransfer ='1')
BEGIN -- 3


SET @TransferPoints = @TransferPoint -- For the notice
IF (@CheckPointsOkey >= @TransferPoint)
BEGIN -- 4

INSERT INTO PS_ChatLog.dbo.Transfer
		(Charname, TotalTransfer, TransferName)
VALUES (@Charname, @TransferPoint, @TargetName)

DECLARE @UserUIDTransfer int, @CharIDNoticeTransfer varchar(max)

-- We select the UserUID of the player pmed
SET @UserUIDTransfer = (SELECT top 1 UserUID FROM PS_Gamedata.dbo.Chars WHERE Charname = @Targetname)

-- We remove the point of the user
UPDATE PS_UserData.dbo.Users_Master SET Point = Point - @TransferPoint WHERE UserUID = @UserUID

-- We add the point to the user pmed
UPDATE PS_UserData.dbo.Users_Master SET Point = Point + @TransferPoint WHERE UserUID = @UserUIDTransfer

SET @TransferNotice = N'/nt ' + @Charname + ' transfered ' + @TransferPoints + ' points to ' + @Targetname
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @TransferNotice

END -- 4
END -- 3
END -- 2
END -- 1

------------------- Auto Notice -------------------

Auto Notice

1 - First we create a new table named Notice in PS_ChatLog

Quote:

Code:

USE [PS_ChatLog]
GO

/****** Object:  Table [dbo].[Notice]    Script Date: 4/29/2017 18:58:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Notice](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Notice] [varchar](max) NOT NULL,
	[Ok] [int] NOT NULL,
	[Date] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

2 - Now we will put the script in usp_Insert_Chat_Log_E
Put this after the insert of Chat Log:

Quote:

Code:

DECLARE @AutoNoticeDate varchar(max)
-- We select the date of the last notice spawn
SET @AutoNoticeDate = (SELECT Date FROM PS_ChatLog.dbo.Notice WHERE id = '1')


IF (@AutoNoticeDate < @ChatTime)
BEGIN
DECLARE @CheckNoticeOk int

-- we select the notice who did not spawn
SET @CheckNoticeOk = (SELECT Count(*) FROM PS_ChatLog.dbo.Notice WHERE id > 1 and Ok ='1')

-- we reset all the notice when they all got spawn
IF (@CheckNoticeOk = '3') BEGIN UPDATE PS_ChatLog.dbo.Notice SET ok = '0' WHERE ID > 1 and Ok = '1' END

-- we put the new date (notice each hour)
UPDATE PS_ChatLog.dbo.Notice SET Date = @Chattime + '01:00' WHERE id = '1'

DECLARE @AutoNotice varchar(max), @AutoNoticeSelect varchar(max), @AutoNoticeSelectID varchar(max)

-- random select a notice
SET @AutoNoticeSelect = (select top 1 Notice  from PS_ChatLog.dbo.Notice WHERE id > 1 and Ok = '0' order by newid())

SET @AutoNoticeSelectID = (select top 1 id from PS_ChatLog.dbo.Notice WHERE notice = @AutoNoticeSelect)

-- set ok = '1' to the notice who spawned
UPDATE PS_ChatLog.dbo.Notice SET Ok = '1' WHERE id = @AutoNoticeSelectID

SET @AutoNotice = N'/nt ' + @AutoNoticeSelect
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @AutoNotice

END

3 - Make it work Correctly
Open a New Query in SQL

Code:

-- Insert the date notice

INSERT INTO PS_ChatLog.dbo.Notice
		(Notice, Ok, Date)
VALUES ('None', 0, GETDATE())

Now we will add 3 notice

Code:

-- Insert the notice

INSERT INTO PS_ChatLog.dbo.Notice
		(Notice, Ok)
VALUES ('[AUTO] Hello 1', 0)

INSERT INTO PS_ChatLog.dbo.Notice
		(Notice, Ok)
VALUES ('[AUTO] Hello 2', 0)

INSERT INTO PS_ChatLog.dbo.Notice
		(Notice, Ok)
VALUES ('[AUTO] Hello 3', 0)

If you inserted with the "Hello 1 2 3"
Open the notice table and just dit the sentence
If you want to put more than 3 notices just edit the script

Quote:

Code:

-- replace the number in the script
IF (@CheckNoticeOk = '3')

------------------- Custom Chat Log -------------------

Custom Chat Log

1 - First we create the new table ChatLog in PS_ChatLog

WARNING : it will drop (delete) your old ChatLog table!

Make sure you read the warning!

2 - Open a new query table and execute this:

Quote:

Code:

USE [PS_ChatLog]
GO
/****** Object:  StoredProcedure [dbo].[usp_Insert_Chat_Log_E]    Script Date: 4/29/2017 19:59:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  Stored Procedure dbo.usp_Insert_Chat_Log_E    Script Date: 2008-6-7 18:30:55 ******/
ALTER     Proc [dbo].[usp_Insert_Chat_Log_E]

/* 
Created by humanws, 2005-10-14
채팅 로그 남기기
 */

@UserUID int,
@CharID int,
@ChatType tinyint,		-- 일반1, 귓말2, 길드3, 파티4, 거래5
@TargetName varchar(30),
@ChatData varchar(max),
@MapID smallint,
@ChatTime datetime

AS

DECLARE @Sql nvarchar(4000)
DECLARE @yyyy varchar(4)
DECLARE @mm varchar(2)
DECLARE @dd varchar(2)

SET @yyyy = DATEPART(yyyy, @ChatTime)
SET @mm = DATEPART(mm, @ChatTime)
SET @dd = DATEPART(dd, @ChatTime)
IF( LEN(@mm) = 1 )
BEGIN
	SET @mm = '0' + @mm
END

IF( LEN(@dd) = 1 )
BEGIN
	SET @dd = '0' + @dd
END
DECLARE @Charname varchar(max), @Faction varchar(20)
SET @Charname = (SELECT Charname FROM PS_GameData.dbo.Chars WHERE CharID = @CharID)
SET @Faction = (SELECT Country FROM PS_GameData.dbo.UserMaxGrow WHERE UserUID = @UserUID)
SET @ChatTime = GETDATE()
IF (@Faction = '0') BEGIN SET @Faction = 'Light' END
IF (@Faction = '1') BEGIN SET @Faction = 'Dark' END
BEGIN
INSERT INTO PS_ChatLog.dbo.ChatLog
		(UserUID, Charname, Faction, CharID, ChatType, TargetName, ChatData, MapID, ChatTime)
VALUES (@UserUID, @Charname, @Faction, @CharID, @ChatType, @TargetName, @ChatData, @MapID, @ChatTime)
END


DECLARE @CharIDNotice varchar(max), @return_value int
SET @CharIDNotice = @CharID
SET @return_value ='0'

-- fist we select the bad words
IF (@ChatData like '%bitch%' or @ChatData like '%idiot%')
BEGIN -- 1

-- now we select the chat type
-- ChatType : 1 = Normal Chat | 2 = Whisper (!) | 3 = Guild | 4 = Group (@) (/raid) | 5 = Trade | 6 = /yelling | 7 = Area

IF (@ChatType = '1' or @ChatType = '4' or @ChatType = '5' or @ChatType = '6' or @ChatType = '7')
BEGIN -- 2

DECLARE @NoticeWarning varchar(max), @NoticeWarningMessage varchar(max)

IF (SELECT UserUID FROM PS_ChatLog.dbo.Warning WHERE UserUID = @UserUID) IS NULL
BEGIN -- 3

-- We insert in chatlog.dbo.warning the UserUID, CharID, Charname and the number of Warning
-- Since it the first warning of the user we will put warning to 0

INSERT INTO PS_ChatLog.dbo.Warning
		(UserUID, Warning)
VALUES (@UserUID, '0')

SET @NoticeWarningMessage = 'Do not say bad words please 1/3.'

SET @NoticeWarning = N'/ntplayer ' + @CharIDNotice + ' Hello ' + @Charname + '. ' + @NoticeWarningMessage
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @NoticeWarning

END -- 3

IF (SELECT UserUID FROM PS_ChatLog.dbo.Warning WHERE UserUID = @UserUID) IS NOT NULL
BEGIN -- 4

BEGIN
UPDATE PS_ChatLog.dbo.Warning SET Warning = Warning + 1 WHERE UserUID = @UserUID
END

DECLARE @HowManyWarning int

SET @HowManyWarning = (SELECT Warning FROM PS_ChatLog.dbo.Warning WHERE UserUID = @UserUID)

IF (@HowManyWarning = '1') -- Second warning
BEGIN -- 5

SET @NoticeWarningMessage = 'Do not say bad words please 2/3.'

SET @NoticeWarning = N'/ntplayer ' + @CharIDNotice + ' Hello ' + @Charname + '. ' + @NoticeWarningMessage
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @NoticeWarning

END -- 5

IF (@HowManyWarning = '2') -- Last warning
BEGIN -- 6

SET @NoticeWarningMessage = 'Do not say bad words please 3/3.'

SET @NoticeWarning = N'/ntplayer ' + @CharIDNotice + ' Hello ' + @Charname + '. ' + @NoticeWarningMessage
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @NoticeWarning

DECLARE @KickWarning varchar(max)

-- like this it will instant kick without let time to the player to see the notice
-- if you want put a delay before it kick the player you can do like this:
-- WAITFOR DELAY '00:05';
-- It will wait for 5 seconds before kick the player

SET @KickWarning = N'/kickcid ' + @CharIDNotice
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @KickWarning

BEGIN
DELETE FROM PS_ChatLog.dbo.Warning WHERE UserUID = @UserUID
END

END -- 6
END -- 4
END -- 2
END -- 1

IF (@Chatdata like '%!transfer%' and @ChatType = '2')
BEGIN -- 1

DECLARE @TransferNotice varchar(max), @TransferPoint int, @TransferPoints varchar(max), @CheckPointsOkey varchar(max), @TransferPointsReward varchar(max), @CheckOkayTransfer int

-- Check if the user got point
SET @CheckPointsOkey = (SELECT Point FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID)

-- remove the !transfer to get the amount
SET @TransferPoint = (select REPLACE (@chatdata, '!transfer ', '' ))

-- if the user got > or = 1000 points he can trade (can change)
IF (@TransferPoint >='1000')
BEGIN -- 2

-- we check if when we remove the !transfer only numbers left
SET @CheckOkayTransfer = (Select Isnumeric(@TransferPoint))

IF (@CheckOkayTransfer ='1')
BEGIN -- 3


SET @TransferPoints = @TransferPoint -- For the notice
IF (@CheckPointsOkey >= @TransferPoint)
BEGIN -- 4

INSERT INTO PS_ChatLog.dbo.Transfer
		(Charname, TotalTransfer, TransferName)
VALUES (@Charname, @TransferPoint, @TargetName)

DECLARE @UserUIDTransfer int, @CharIDNoticeTransfer varchar(max)

-- We select the UserUID of the player pmed
SET @UserUIDTransfer = (SELECT top 1 UserUID FROM PS_Gamedata.dbo.Chars WHERE Charname = @Targetname)

-- We remove the point of the user
UPDATE PS_UserData.dbo.Users_Master SET Point = Point - @TransferPoint WHERE UserUID = @UserUID

-- We add the point to the user pmed
UPDATE PS_UserData.dbo.Users_Master SET Point = Point + @TransferPoint WHERE UserUID = @UserUIDTransfer

SET @TransferNotice = N'/ntplayer ' + @CharIDNotice + ' You transfered ' + @TransferPoints + ' points to ' + @Targetname
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @TransferNotice

-- We select the charname of pmed
SET @CharIDNoticeTransfer = (SELECT top 1 CharID FROM PS_GameData.dbo.Chars WHERE Charname = @TargetName)

SET @TransferNotice = N'/ntplayer ' + @CharIDNoticeTransfer + ' ' + @Charname + ' transfered you ' + @TransferPoints + ' points.'
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @TransferNotice
END -- 4
END -- 3
END -- 2
END -- 1

DECLARE @AutoNoticeDate varchar(max)
-- We select the date of the last notice spawn
SET @AutoNoticeDate = (SELECT Date FROM PS_ChatLog.dbo.Notice WHERE id = '1')


IF (@AutoNoticeDate < @ChatTime)
BEGIN
DECLARE @CheckNoticeOk int

-- we select the notice who did not spawn
SET @CheckNoticeOk = (SELECT Count(*) FROM PS_ChatLog.dbo.Notice WHERE id > 1 and Ok ='1')

-- we reset all the notice when they all got spawn
IF (@CheckNoticeOk = '3') BEGIN UPDATE PS_ChatLog.dbo.Notice SET ok = '0' WHERE ID > 1 and Ok = '1' END

-- we put the new date (notice each hour)
UPDATE PS_ChatLog.dbo.Notice SET Date = @Chattime + '01:00' WHERE id = '1'

DECLARE @AutoNotice varchar(max), @AutoNoticeSelect varchar(max), @AutoNoticeSelectID varchar(max)

-- random select a notice
SET @AutoNoticeSelect = (select top 1 Notice  from PS_ChatLog.dbo.Notice WHERE id > 1 and Ok = '0' order by newid())

SET @AutoNoticeSelectID = (select top 1 id from PS_ChatLog.dbo.Notice WHERE notice = @AutoNoticeSelect)

-- set ok = '1' to the notice who spawned
UPDATE PS_ChatLog.dbo.Notice SET Ok = '1' WHERE id = @AutoNoticeSelectID

SET @AutoNotice = N'/nt ' + @AutoNoticeSelect
EXEC	@return_value = [OMG_GameWEB].[dbo].[Command]
		@serviceName = N'ps_game',
		@cmmd = @AutoNotice

END

Trayne01 · 04/29/2017, 16:20

You could also create a Shaiya Server Extended plugin, wich avoid to edit SQL procedures, wich is not that easy to manage if you want to split your code.

(I might uplaod it as a plugin soon)

Thanks for sharing!

momocruz · 04/29/2017, 18:09

Thanks amigo !!!

Eric-Dutra16 · 04/29/2017, 22:05

Alex scripts? "battle only arena ep5.4" I made this script for you, for free. Why did not you tell me it was to share it in epvp?

JujiPoli · 04/29/2017, 22:10

I released it for a custom pvp script (sql)
I asked you it 1 year ago cause i don't know work on cheat engine, if you don't want me to release it, i will just remove it lol.

ShaiyaDead · 04/30/2017, 05:43

battle arena as what the title says the players can already battle in arena lmao you should ask the author first before releasing those credits are not enough without asking for their authorization

momocruz · 04/30/2017, 10:26

Quote:

Originally Posted by ShaiyaDead

battle arena as what the title says the players can already battle in arena lmao you should ask the author first before releasing those credits are not enough without asking for their authorization

Too Late Albert many people have download the script right NOW .

JujiPoli · 04/30/2017, 20:08

Instead of come skype/epvp message and try to find a deal (remove the script or just release before it was a free one) you need to post and get thanks for it? It amazing how the community of Shaiya in epvp is destroyed by those ridiculous people

Post a message just for thanks -> new Shaiya community 2017

Have fun, I won't share anything anymore, bored of hypocritical people.

sanguto · 05/08/2017, 05:00

Auto Notice

If not ask too much. As it would be to announce at a certain time, type the message to appear at 21h server time

JujiPoli · 05/08/2017, 13:54

sanguto · 05/08/2017, 21:11

thank you so much

IlusionXtreme · 09/08/2017, 09:05

[auto notice]
I have a problem.
already tried to position the code in the insertion area plus the problem continues
Could someone give me an example where to position without errors

Msg 137, Level 15, State 2, Procedure usp_Insert_Chat_Log_E, Line 77
Must declare the scalar variable "@return_value"

Velocity. · 09/09/2017, 01:29

Quote:

Originally Posted by IlusionXtreme

[auto notice]
I have a problem.
already tried to position the code in the insertion area plus the problem continues
Could someone give me an example where to position without errors

Msg 137, Level 15, State 2, Procedure usp_Insert_Chat_Log_E, Line 77
Must declare the scalar variable "@return_value"

it literally tells you the issue lmao

~~Wa7dani~~ · 11/22/2017, 08:55

Quote:

Originally Posted by doteroargentino

Wow, a welcoming massage surely will make people relax a lot more while playing!!

Code:

USE [PS_GameData]
GO
/****** Object:  StoredProcedure [dbo].[Welcome_Message]    Script Date: 7/22/2016 12:35:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Welcome_Message]
	-- Add the parameters for the stored procedure here
	@Char int
AS
BEGIN
	WAITFOR DELAY '00:00:30';
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @CharName varchar(32) = (SELECT CharName FROM PS_GameData.dbo.Chars where CharID = @Char)
	DECLARE [MENTION=2805776]UserU[/MENTION]ID int = (SELECT UserUID FROM PS_GameData.dbo.Chars where CharID = @Char)
	DECLARE [MENTION=1340890]faction[/MENTION] bit = (SELECT Country FROM PS_GameData.dbo.UserMaxGrow WHERE UserUID = [MENTION=2805776]UserU[/MENTION]ID)
	DECLARE @Datet varchar(32) = CONVERT(varchar(32),GETDATE())
	DECLARE @DFaction varchar(32)
	if  [MENTION=1340890]faction[/MENTION] = 1)
		SET @DFaction = 'Union of Fury'
	else
		SET @DFaction = 'Alliance of Light'


	DECLARE [MENTION=733771]welcome[/MENTION]msg nchar(200) = N'/nt ' + 'Welcome to ' + @CharName + ' (Faction: ' + @DFaction + ')' + ' who joined Assassin on the ' + @Datet


			DECLARE [MENTION=3807069]ReTuRn_[/MENTION]value int

			EXEC [MENTION=3807069]ReTuRn_[/MENTION]value = PS_GameDefs.[dbo].[Command]
				 [MENTION=1485710]service[/MENTION]Name = N'ps_game',
					@cmmd = [MENTION=733771]welcome[/MENTION]msg



END

and dont cry please

[GM]-RazoR-* · 01/25/2019, 12:02

Quote:
Originally Posted by JujiPoli
Hello Shaiya,

Today I am releasing my custom scripts (sql)

All the scripts I'm releasing here are my ideas and written by me

What you need for the script to work correctly:

1- (won't work if you don't have it)

2- Go in PS_ChatLog -> Programmability -> Stored Procedures -> usp_Insert_Chat_Log_E click on modify

After this:

IF( LEN(@dd) = 1 )
BEGIN
SET @dd = '0' + @dd
END

Put this:

3- ([OPTIONAL] Needed for the notice to player)

1 - First script is a bad word filter,
How does it work? Every time a player says a bad word like "bitch"
it will give a warning to the player.
Once the player has received 3 warnings they will be kicked.

2 - Second script is a Transfer DP
How does it work? PM a player and say the amount to transfer (! Alex !transfer 1000)

3 - Third script is a Auto Notice spawner
How does it work? It will spawn a random notice selected via a table each 1 hour

4 - Fourth script is my custom ChatLog with the scripts ^

------------------- BAD WORD FILTER -------------------
Bad Word Filter Script
1 - First we create a new table named Warning in PS_ChatLog
Code:
USE [PS_ChatLog]
GO

/****** Object:  Table [dbo].[Warning]    Script Date: 4/29/2017 14:51:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Warning](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[UserUID] [int] NOT NULL,
	[Warning] [int] NOT NULL
) ON [PRIMARY]

GO
2 - Now we will put the script in usp_Insert_Chat_Log_E
Put this after the insert of Chat Log:

The sentence: "Do not say bad words please" is an example, you can change the sentence to whatever you want

With the /ntplayer (notice to player):

With the /nt (public notice):
------------------- DP Transfer -------------------
DP TRANSFER Script
1 - First we create a new table named Transfer in PS_ChatLog
Code:
USE [PS_ChatLog]
GO

/****** Object:  Table [dbo].[Transfer]    Script Date: 4/29/2017 18:35:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Transfer](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Charname] [varchar](max) NOT NULL,
	[TotalTransfer] [int] NOT NULL,
	[TransferName] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
2 - Now we will put the script in usp_Insert_Chat_Log_E
Put this after the insert of Chat Log:

With the /ntplayer (notice to player):

With the /nt (public notice):
------------------- Auto Notice -------------------
Auto Notice
1 - First we create a new table named Notice in PS_ChatLog

2 - Now we will put the script in usp_Insert_Chat_Log_E
Put this after the insert of Chat Log:

3 - Make it work Correctly
Open a New Query in SQL
Code:
-- Insert the date notice

INSERT INTO PS_ChatLog.dbo.Notice
		(Notice, Ok, Date)
VALUES ('None', 0, GETDATE())
Now we will add 3 notice
Code:
-- Insert the notice

INSERT INTO PS_ChatLog.dbo.Notice
		(Notice, Ok)
VALUES ('[AUTO] Hello 1', 0)

INSERT INTO PS_ChatLog.dbo.Notice
		(Notice, Ok)
VALUES ('[AUTO] Hello 2', 0)

INSERT INTO PS_ChatLog.dbo.Notice
		(Notice, Ok)
VALUES ('[AUTO] Hello 3', 0)
If you inserted with the "Hello 1 2 3"
Open the notice table and just dit the sentence
If you want to put more than 3 notices just edit the script
------------------- Custom Chat Log -------------------

Custom Chat Log

1 - First we create the new table ChatLog in PS_ChatLog

WARNING : it will drop (delete) your old ChatLog table!

Make sure you read the warning!

2 - Open a new query table and execute this:

hi

I tested this beautiful scenario. Personal notices don't work. Does anyone have an idea for that?

N'/nt and N'/ntplayer I tried these two commands, but it doesn't work.

for N'/ntplayer SExtended.dll I injected it into ps_game.exe but it still didn't work.

Sorry about the bad english.