[Release]Working Clans & Clan War

WhiteMoga · Mar 10, 2007

Working Clans & Clan War, By Buga and Alduath's GetClanInfo
Update V2: Noticed that it didn't actually do anything with points. Also Made TotalPoints count all the total positive point values you've earned

Ok everyone, being Clans is the one thing people are really missing, Ill release what I have built in just today, It updates Points and the like, but it doesnt update Ranking, and I believe thats all it wont update.
I'll give you the tables, and the PROCs. I apologize for anything I may have done wrong in them, and your free to distribute them so long as you include Alduath's and I's credit.

If something particular doesn't work, tell me and I'll try to fix it.

If you found something nice, please share.

Here they are:

Tables
Clan Table:

Code:

USE [GunzDB]
GO
/****** Object:  Table [dbo].[Clans]    Script Date: 03/09/2007 21:02:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Clans](
    [CLID] [int] IDENTITY(1,1) NOT NULL,
    [Level] [int] NULL,
    [Name] [varchar](50)  NULL,
    [ClanMaster] [varchar](50)  NULL,
    [EmblemUrl] [varchar](50)  NULL,
    [TotalPoint] [int] NULL,
    [Point] [int] NULL,
    [Wins] [int] NULL,
    [Losses] [int] NULL,
    [MemberCount] [int] NULL,
    [Ranking] [int] NULL,
    [EmblemChecksum] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Clanwar Log Table:

Code:

USE [GunzDB]
GO
/****** Object:  Table [dbo].[WinTheClanGame]    Script Date: 03/09/2007 21:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WinTheClanGame](
    [ClanOne] [int] NULL,
    [ClanTwo] [int] NULL,
    [IsDraw] [int] NULL,
    [ClanOnePoint] [int] NULL,
    [ClanTwoPoint] [int] NULL,
    [ClanOneName] [nvarchar](max)  NULL,
    [ClanTwoName] [nvarchar](max)  NULL,
    [ClanOneScore] [int] NULL,
    [ClanTwoScore] [int] NULL,
    [MapID] [int] NULL,
    [GameType] [int] NULL,
    [ClanOneMembers] [nvarchar](max)  NULL,
    [ClanTwoMembers] [nvarchar](max)  NULL
) ON [PRIMARY]

Procedures
Add Clan Member:

Code:

USE [GunzDB]
GO
/****** Object:  StoredProcedure [dbo].[spAddClanMember]    Script Date: 03/09/2007 21:10:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spAddClanMember]
    @nCLID INT,
    @nCID INT,
    @nLevel INT
AS
    DECLARE @nName varchar(50)
    SELECT @nName = Name FROM Clans WHERE CLID = @nCLID
    UPDATE Character SET ClanName = @nName, CLID = @nCLID, ClanGrade = @nLevel WHERE CID = @nCID

    
    UPDATE Clans SET MemberCount = MemberCount+1 WHERE CLID = @nCLID

    SELECT 1 Ret

Clan Create(I didn't test this, but it looks correct):

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spCreateClan]
    @nClanName varchar(50),
    @nMaster varchar(50),
    @nUser1 INT,
    @nUser2 INT,
    @nUser3 INT,
    @nUser4 INT
AS
    INSERT INTO Clans (Name,ClanMaster,TotalPoint,Point,Wins,Losses,MemberCount,Ranking)
    VALUES(@nClanName,@nMaster,0,1000,0,0,5,0)

    UPDATE Character SET ClanName = @nClanName 
    WHERE CID = @nMaster OR CID = @nUser1 OR CID = @nUser2 OR CID = @nUser3 OR CID = @nUser4
    
    DECLARE @nV INT
    SELECT @nV = CLID FROM Clans WHERE Name = @nClanName
    UPDATE Character SET CLID = @nV 
    WHERE CID = @nMaster OR CID = @nUser1 OR CID = @nUser2 OR CID = @nUser3 OR CID = @nUser4
    
    UPDATE Character SET ClanGrade = 1 WHERE CID = @nMaster

    SELECT 1 Ret,CLID FROM Clans WHERE Name = @nClanName

GetCharClan:

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spGetCharClan]
    @nCID INT
AS
    SELECT CLID FROM Character WHERE CID = @nCID

GetClanInfo(made by Alduath, but I made it one line, and it works, but it doesnt for him apparently):

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spGetClanInfo]
    @nCLID INT
AS
    SET NOCOUNT ON;
    SELECT CLID, Level, Name, ClanMaster, TotalPoint, Point, Wins, Losses, MemberCount, Ranking, EmblemUrl, EmblemChecksum FROM Clans WHERE CLID=@nCLID
    --SELECT EmblemChecksum FROM Clans WHERE CLID=@nCLID

GetCLIDFromClanName:

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spGetCLIDFromClanName]
    @nClanName varchar(50)
AS
    SELECT CLID FROM Clans WHERE Name = @nClanName

RemoveClanMember

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spRemoveClanMember]
    @nCLID INT,
    @nCID INT
AS
    UPDATE Character SET ClanName = NULL, CLID = NULL, ClanGrade = 0 WHERE CID = @nCID

    
    UPDATE Clans SET MemberCount = MemberCount-1 WHERE CLID = @nCLID

RemoveClanMemberByName:

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spRemoveClanMemberFromCharName]
    @nCLID INT,
    @nCID INT,
    @nCharName varchar(50)
AS
    DECLARE @szUsername varchar(50)
    SELECT @szUsername = ClanMaster FROM Clans WHERE CLID = @nCLID
    IF NOT  @nCharName = @szUsername
	BEGIN
              UPDATE Character SET ClanName = NULL, CLID = NULL, ClanGrade = 0 WHERE CID = @nCID

              DECLARE @nCount INT
              SELECT @nCount = MemberCount From Clans WHERE CLID = @nCLID
              UPDATE Clans SET MemberCount = @nCount-1 WHERE CLID = @nCLID
        END
    SELECT 1 Ret

UpdateCharClanContPoint(Not certain this is correct):

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spUpdateCharClanContPoint]
    @nCLID INT,
    @nCID INT,
    @nContPoint INT
AS
BEGIN
    UPDATE Character SET ClanContPoint = ClanContPoint+@nContPoint WHERE CID = @nCID
END

UpdateClanGrade:

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spUpdateClanGrade] 
    @nCLID INT,
    @nCID INT,
    @nLEVEL INT
AS

    UPDATE Character SET ClanGrade = @nLEVEL WHERE CID = @nCID

WinTheGame:

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spWinTheClanGame]
    @nClanOne INT,
    @nClanTwo INT,
    @nIsDraw INT,
    @nClanOnePoint INT,
    @nClanTwoPoint INT,
    @nClanOneName nvarchar(MAX),
    @nClanTwoName nvarchar(MAX),
    @nClanOneScore INT,
    @nClanTwoScore INT,
    @nMapID INT,
    @nGameType INT,
    @nClanOneMembers nvarchar(MAX),
    @nClanTwoMembers nvarchar(MAX)
AS
    INSERT INTO WinTheClanGame VALUES(@nClanOne,@nClanTwo,@nIsDraw,@nClanOnePoint,@nClanTwoPoint,@nClanOneName,@nClanTwoName,@nClanOneScore,@nClanTwoScore,@nMapID,@nGameType,@nClanOneMembers,@nClanTwoMembers)
    

    UPDATE Clans SET Point = Point + @nClanOnePoint, Wins = Wins+1, TotalPoint=TotalPoint + @nClanOnePoint WHERE CLID = @nClanOne
    
    UPDATE Clans SET Point = Point + @nClanTwoPoint, Losses = Losses+1 WHERE CLID = @nClanTwo

magician911 · Mar 10, 2007

holy crap, man you rock, was lookin for this and even wrote my own version, now im almost bug free

RepublicOfAstra · Mar 10, 2007

Nice release.

Omer · Mar 10, 2007

Added to the top of the DL Center

!

alduath · Mar 10, 2007

Nice 1

, if that spGetClanInfo works for you ill need to try again.

pahapoisu · Mar 10, 2007

Heh all about the clans in pserver is here ^^

mts · Mar 10, 2007

Niceeeee

kekku500 · Mar 10, 2007

cannot format clan that sad to me!!!i mean if itryed create clan!!.. i deleted all my old clan procs and added these

kochon · Mar 10, 2007

You Are My Best Friend

kekku500 · Mar 10, 2007

how u got it work?

kochon · Mar 10, 2007

i still cant see clan war gametype in game...

Crypto37 · Mar 10, 2007

Nice release, you should make the Emblems work

kochon said:
i still cant see clan war gametype in game...

your a Special person.
You dont see CLAN WAR in the game... In your server.ini in matchserver you have to change MODE="test" or w/e to MODE="clan"
geez...

kekku500 · Mar 10, 2007

in matchserver look server.ini or something like that ..
find mode"test" change test to clan then is clan type

EDIT:crypto37 we posted at same time XD

WhiteMoga · Mar 10, 2007

Yes, Mode must be clan.
Also kekku I cannot understand you.

kochon · Mar 10, 2007

Crypto37 said:
Nice release, you should make the Emblems work

your a Special person.
You dont see CLAN WAR in the game... In your server.ini in matchserver you have to change MODE="test" or w/e to MODE="clan"
geez...

i did it...

kekku500 · Mar 10, 2007

WhiteMoga said:
Yes, Mode must be clan.
Also kekku I cannot understand you.

what you cant understand in me?

Vicio · Mar 11, 2007

hmm in the add clan member and remove

WhiteMoga said:
DECLARE @nCount INT
SELECT @nCount = MemberCount From Clans WHERE CLID = @nCLID
UPDATE Clans SET MemberCount = @nCount-1 WHERE CLID = @nCLID

u can do directly "UPDATE Clans SET MemberCount = MemberCount-1 WHERE CLID = @nCLID"

isnt necesary make a count of rows.

and about the UpdateCharClanContPoint

the correct query is

UPDATE Character SET ClanContPoint = ClanContPoint+@nContPoint WHERE CID = @nCID

WhiteMoga said:
DECLARE @nLosses INT
SELECT @nLosses = Losses FROM Clans WHERE CLID = @nClanTwo
DECLARE @nPoints INT
DECLARE @nTotalPoints INT
SELECT @nPoints = Point FROM Clans WHERE CLID = @nClanOne
SELECT @nTotalPoints = TotalPoint FROM Clans WHERE CLID = @nClanOne
DECLARE @nWins INT
SELECT @nWins = Wins FROM Clans WHERE CLID = @nClanOne

wtfffffffffff they're so many querys.. =/

the correct is

INSERT INTO WinTheClanGame VALUES(@nClanOne,@nClanTwo,@nIsDraw,@nClanOnePoint,@nClanTwoPoint,@nClanOneName,@nClanTwoName,@nClanOneScore,@nClanTwoScore,@nMapID,@nGameType,@nClanOneMembers,@nClanTwoMembers)

UPDATE Clans SET Point = Point+@nClanOnePoint, Wins = Wins+1, TotalPoint=TotalPoint+@nClanOnePoint WHERE CLID = @nClanOne

UPDATE Clans SET Point = Point+@nClanTwoPoint, Losses = Losses+1 WHERE CLID = @nClanTwo

WhiteMoga · Mar 11, 2007

Im very much aware of that, take a look at the Legacy Database's GetClanInfo, and then complain to me about that.

Anyways, it comes from my programming background, I have to be exact, but seeing as your method works, and with a lot less clutter, I shall use it.

alduath · Mar 11, 2007

kochon said:
i did it...

Is your character in a clan? otherwise you will see a regular match server.

magician911 · Mar 11, 2007

how can u enable quest AND clans on the same port

Welcome!

[Release]Working Clans & Clan War

Junior Spellweaver

Newbie Spellweaver

Supreme Arcanarch

Omer

Newbie Spellweaver

Newbie Spellweaver

Experienced Elementalist

RageZ Hell

Custom Title Activated

RageZ Hell

Custom Title Activated

Junior Spellweaver

RageZ Hell

Junior Spellweaver

Custom Title Activated

RageZ Hell

Newbie Spellweaver

Junior Spellweaver

Newbie Spellweaver

Newbie Spellweaver

About Us

Online statistics

Forum statistics

RaGEZONE Sponsor