USE SRO_VT_SYSTEMS
GO
CREATE TABLE _Uniques
(
ID BIGINT NOT NULL IDENTITY (1,1),
CharName VARCHAR(255),
MonsterCodeName VARCHAR(255),
Time DATETIME,
CONSTRAINT PK_UniqueKills PRIMARY KEY CLUSTERED (ID)
)
GO
CREATE TABLE _GlobalChat
(
ID BIGINT NOT NULL IDENTITY (1,1),
CharName VARCHAR(255),
Message VARCHAR(255),
Time DATETIME,
CONSTRAINT PK_GlobalChat PRIMARY KEY CLUSTERED (ID)
)
GO
CREATE TABLE _AllChat
(
ID BIGINT NOT NULL IDENTITY (1,1),
CharName VARCHAR(255),
Message VARCHAR(255),
Time DATETIME,
CONSTRAINT PK_AllChat PRIMARY KEY CLUSTERED (ID)
)
GO
CREATE TABLE _PrivateChat
(
ID BIGINT NOT NULL IDENTITY (1,1),
Sender VARCHAR(255),
Receiver VARCHAR(255),
Message VARCHAR(255),
Time DATETIME,
CONSTRAINT PK_PrivateChat PRIMARY KEY CLUSTERED (ID)
)
GO
CREATE TABLE _GuildChat
(
ID BIGINT NOT NULL IDENTITY (1,1),
Guild VARCHAR(255),
CharName VARCHAR(255),
Message VARCHAR(255),
Time DATETIME,
CONSTRAINT PK_GuildChat PRIMARY KEY CLUSTERED (ID)
)
GO
CREATE TABLE _AcademyChat
(
ID BIGINT NOT NULL IDENTITY (1,1),
GuardianName VARCHAR(255),
CharName VARCHAR(255),
Message VARCHAR(255),
Time DATETIME,
CONSTRAINT PK_AcademyChat PRIMARY KEY CLUSTERED (ID)
)
GO
CREATE TABLE _UnionChat
(
ID BIGINT NOT NULL IDENTITY (1,1),
UnionLeader VARCHAR(255),
Guild VARCHAR(255),
CharName VARCHAR(255),
Message VARCHAR(255),
Time DATETIME,
CONSTRAINT PK_UnionChat PRIMARY KEY CLUSTERED (ID)
)
GO
CREATE PROCEDURE _LogGuildAndUnionChat
@CharName VARCHAR(255),
@message VARCHAR(255)
AS
DECLARE @GuiLdName VARCHAR(255),
@GuiLdID VARCHAR(255),
@StrToCheck VARCHAR(255),
@unionLeader VARCHAR(255)
SELECT @GuiLdID = ID, @GuiLdName = Name FROM SHARD_VICTOR.dbo._Guild WHERE ID = (SELECT GuildID FROM SHARD_VICTOR.dbo._GuildMember WHERE CharName = @CharName)
SELECT @StrToCheck = '-' + @GuiLdName +'): '
SELECT @unionLeader = Name
FROM SHARD_VICTOR.dbo._Guild
WHERE ID = (
SELECT Ally1 FROM SHARD_VICTOR.dbo._AlliedClans WHERE
Ally1 = @GuiLdID OR
Ally2 = @GuiLdID OR
Ally3 = @GuiLdID OR
Ally4 = @GuiLdID OR
Ally5 = @GuiLdID OR
Ally6 = @GuiLdID OR
Ally7 = @GuiLdID OR
Ally8 = @GuiLdID
)
IF (@message LIKE '%' + @StrToCheck + '%')
BEGIN
DECLARE @unionMessage VARCHAR(255) = REPLACE (@message, @StrToCheck, '')
INSERT INTO _UnionChat (UnionLeader, Guild, CharName, Message, Time) VALUES (@unionLeader, @GuiLdName, @CharName, @unionMessage, GETDATE())
END
ELSE
BEGIN
INSERT INTO _GuildChat (Guild, CharName, Message, Time) VALUES (@GuiLdName, @CharName, @message, GETDATE())
END
GO
CREATE PROCEDURE _LogGlobalChat
@CharName VARCHAR(255),
@message VARCHAR(255)
AS
INSERT INTO _GlobalChat (CharName, Message, Time) VALUES (@CharName, @message, GETDATE())
GO
CREATE PROCEDURE _LogAllChat
@CharName VARCHAR(255),
@message VARCHAR(255)
AS
INSERT INTO _AllChat (CharName, Message, Time) VALUES (@CharName, @message, GETDATE())
GO
CREATE PROCEDURE _LogPrivateChat
@SenderName VARCHAR(255),
@ReceiverName VARCHAR(255),
@message VARCHAR(255)
AS
INSERT INTO _PrivateChat (Sender, Receiver, Message, Time) VALUES (@SenderName, @ReceiverName, @message, GETDATE())
GO
CREATE PROCEDURE _LogAcademyChat
@CharName VARCHAR(255),
@message VARCHAR(255)
AS
DECLARE @GuardianName VARCHAR(255) = (
SELECT CharName
FROM SHARD_VICTOR.dbo._TrainingCampMember
WHERE CampID =
(
SELECT CampID
FROM SHARD_VICTOR.dbo._TrainingCampMember
WHERE CharName = @CharName
) AND MemberClass = 0
)
INSERT INTO _AcademyChat (GuardianName, CharName, Message, Time) VALUES (@GuardianName , @CharName, @message, GETDATE())
GO
CREATE PROCEDURE _LogUniques
@CharName VARCHAR(255),
@MonsterCodeName VARCHAR(255)
AS
INSERT INTO _Uniques (CharName, MonsterCodeName, Time) VALUES (@CharName, @MonsterCodeName, GETDATE())