[Release]Working Clans & Clan War

[WhiteMoga]

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:

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:

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:

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):

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:

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):

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:

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

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:

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):

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:

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:

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]

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

 

[RepublicOfAstra]

Nice release.

 

[Omer]

Added to the top of the DL Center !

 

[alduath]

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

 

[pahapoisu]

Heh all about the clans in pserver is here ^^

 

[mts]

Niceeeee

 

[kekku500]

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

 

[kochon]

You Are My Best Friend

 

[kekku500]

how u got it work?

 

[kochon]

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

 

[Crypto37]

Nice release, you should make the Emblems work

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

your a retard.
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]

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]

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

 

[kochon]

Nice release, you should make the Emblems work


your a retard.
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]

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

what you cant understand in me?

 

[Vicio]

hmm in the add clan member and remove

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

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]

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]

i did it...

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

 

[magician911]

how can u enable quest AND clans on the same port