Code: Select all
-- WHEN GAME DOESN'T COUNT NO ELO CHANGES ARE RECORDED.
DECLARE @GameCounted int
SET @GameCounted = 1
-- TO BE OUTPUT VIA THE WEBSERVICE
DECLARE @GameReason varchar(100)
SET @GameReason = 'Game Counted'
-- GET GAME LENGTH
DECLARE @GameDuration real
SET @GameDuration = (SELECT DATEDIFF(second,GameStartTime,GameEndTime) FROM Game WHERE GameIdentID = @GameID)
-- SHOULDN'T HAPPEN
IF @GameDuration < 1
BEGIN
RAISERROR('Game Duration was Zero or Negative',16,1)
RETURN
END
-- WHOLE GAME LESS THAN 5 MINUTES (300 SECONDS)
IF @GameDuration < 300
BEGIN
SET @GameCounted = 0
SET @GameReason = 'Game duration was less than 5 minutes'
END
-- GET ACCOUNT LISTS FOR REFERENCING
DECLARE @Accounts TABLE (Member_ID int, Callsign varchar(50), TeamNumber int, GameTime real, AdjustedGameTime real, Winner int)
INSERT INTO @Accounts (Member_ID, Callsign, TeamNumber, GameTime, AdjustedGameTime, Winner)
SELECT DISTINCT gtm.GameTeamMemberMemberID,gtm.GameTeamMemberCallsign, gt.GameTeamNumber, gtm.GameTeamMemberDuration, gtm.GameTeamMemberDuration, gt.GameTeamWinner
FROM GameTeamMember gtm
INNER JOIN GameTeam gt ON gt.GameTeamIdentID = gtm.GameTeamID
WHERE gt.GameID = @GameID
-- IF THEY PLAYED LESS THAN 5 MINUTES, THEY DIDN'T PLAY AT ALL
UPDATE @Accounts
SET GameTime = 0
WHERE GameTime < 300
-- WORKAROUND FOR EARLY DURATION BUG
UPDATE @Accounts
SET GameTime = @GameDuration
WHERE GameTime > @GameDuration
-- LOSERS WHO DROP GET DEDUCTED FULL POINTS
UPDATE @Accounts
SET AdjustedGameTime = dbo.ASGSGetPlayerMaxGameTime(@GameID, Member_ID)
WHERE Winner = 0
AND AdjustedGameTime >= 300
-- LOSERS WHO SWITCH TEAMS ONLY GET CREDIT FOR LOSING
DELETE FROM @Accounts
WHERE Winner = 1
AND Member_ID IN (SELECT Member_ID FROM @Accounts WHERE Winner = 0)
-- DON'T COUNT GAMES WITH LESS THAN 10 FOR AT LEAST HALF THE GAME
IF (SELECT COUNT(*) FROM @Accounts WHERE ABS(@GameDuration - GameTime) < (@GameDuration / 2)) < 10
BEGIN
SET @GameCounted = 0
SET @GameReason = 'Less than 10 players for at least half of the game'
END
-- INIT PLAYERLIST
DECLARE @PlayerList TABLE (Member_ID int, Playtime int, Modifier decimal(4,2), AdjustedModifier decimal(4,2), TeamNumber int)
INSERT INTO @PlayerList (Member_ID, Playtime, Modifier, AdjustedModifier,TeamNumber)
SELECT MAX(Member_ID), SUM(GameTime), SUM(GameTime/@GameDuration), SUM(AdjustedGameTime/@GameDuration), TeamNumber
FROM @Accounts
GROUP BY Member_ID, TeamNumber
-- DO THE TWO STEP BECAUSE SQL DOESN'T LIKE THE AGGREGATES
DECLARE @Players TABLE (Member_ID int, ELORating int, Wins int, Losses int, Adjustment int, Playtime int, Modifier decimal(4,2), AdjustedModifier decimal(4,2), Expected real, TeamNumber int)
INSERT INTO @Players (Member_ID, ELORating, Wins, Losses, Adjustment, Playtime, Modifier, AdjustedModifier, Expected, TeamNumber)
SELECT MAX(Member_ID), 1500, 0, 0, 0, MAX(Playtime), MAX(Modifier), MAX(AdjustedModifier), .50, TeamNumber
FROM @PlayerList
GROUP BY Member_ID, TeamNumber
-- FILL IN TRUE ELO VALUES FOR PLAYERS THAT HAVE EXISTING ELO RECORDS
UPDATE @Players
SET ELORating = e.ELORanking
FROM ELO e
INNER JOIN @Players p ON p.Member_ID = e.Member_ID
-- CALCULATION VARIABLES
DECLARE @TeamID int
DECLARE @PlayerELO real
DECLARE @OpponentELO real
DECLARE @PlayerES real
DECLARE @OpponentES real
DECLARE @SCORE real
DECLARE @Adjustment int
DECLARE @K int
-- INIT WORKING LISTS
DECLARE @TeamELO TABLE (TeamNumber int, TeamPlayerCount int, ELORating int, Winner real, OpponentELO int, Expected real, Adjustment int, Playtime int, MaxPlayertime int)
DECLARE @Teams TABLE (TeamID int)
-- BEGIN PROCESSING GAME
INSERT INTO @TeamELO(TeamNumber, TeamPlayerCount, ELORating, Winner, Playtime, MaxPlayertime)
SELECT gt.GameTeamNumber, Count(p.Member_ID), SUM(((p.ELORating - 1500) * p.Modifier) + 1500) / Count(*), gt.GameTeamWinner, SUM(p.Playtime), MAX(p.Playtime)
FROM GameTeamMember gtm, GameTeam gt, @Players p
WHERE gt.GameTeamIdentID = gtm.GameTeamID
AND p.Member_ID = gtm.GameTeamMemberMemberID
AND p.TeamNumber = gt.GameTeamNumber
AND gt.GameID = @GameID
AND p.Playtime > 0
GROUP BY gt.GameTeamNumber, gt.GameTeamWinner, p.TeamNumber
-- ADJUST TEAM ELO TO ACCOUNT FOR IMBALANCE
DECLARE @MaxPlaytime int
SET @MaxPlaytime = (SELECT MAX(Playtime) FROM @TeamELO)
DECLARE @MaxPlayertime int
SET @MaxPlayertime = (SELECT MAX(MaxPlayertime) FROM @TeamELO)
UPDATE @TeamELO
SET ELORating = ELORating * (CONVERT(real,Playtime) / CONVERT(real,@MaxPlaytime))
-- PROCESS GAME
INSERT INTO @Teams(TeamID)
SELECT TeamNumber FROM @TeamELO
-- INNER LOOP - RUNS ONCE FOR EACH TEAM
WHILE (SELECT COUNT(*) FROM @Teams) > 0
BEGIN
SET @TeamID = (SELECT TOP 1 TeamID FROM @Teams)
-- ELO IS 1 VS 1 BASED, SO CREATE AN AGGREGATE OPPONENT SCORE
UPDATE @TeamELO
SET OpponentELO = (SELECT SUM(ELORating * Playtime) / SUM(Playtime) FROM @TeamELO WHERE TeamNumber <> @TeamID)
WHERE TeamNumber = @TeamID
-- IF NOBODY WON, DECLARE A DRAW
IF (SELECT MAX(Winner) FROM @TeamELO) = 0
BEGIN
UPDATE @TeamELO SET Winner = .5
END
-- ELO FORMULA BASED ON STANDARD ELO
SET @PlayerELO = (SELECT ELORating FROM @TeamELO WHERE TeamNumber = @TeamID)
SET @OpponentELO = (SELECT OpponentELO FROM @TeamELO WHERE TeamNumber = @TeamID)
SET @PlayerES = (1 / (1 + (POWER(CONVERT(real,10),((@OpponentELO - @PlayerELO) / 400)))))
SET @OpponentES = (1 / (1 + (POWER(CONVERT(real,10),((@PlayerELO - @OpponentELO) / 400)))))
SET @SCORE = (SELECT Winner FROM @TeamELO WHERE TeamNumber = @TeamID)
SET @K = 32
SET @Adjustment = @K * (@Score - @PlayerES)
-- UPDATE THE WORK TABLE
UPDATE @TeamELO
SET Adjustment = @Adjustment,
Expected = @PlayerES
WHERE TeamNumber = @TeamID
DELETE FROM @Teams WHERE TeamID = @TeamID
END
-- USED DURING DEBUGGING ONLY
IF @OutputResults = 1
BEGIN
SELECT * FROM @TeamELO
END
-- GAME PROCESSING COMPLETE
-- CHECK FOR ELO IMBALANCE (STACK)
IF (SELECT MAX(Expected) FROM @TeamELO WHERE Winner = 1) > .70
BEGIN
SET @GameCounted = 0
SET @GameReason = 'Game imbalance was excessive'
END
-- USED DURING DEBUGGING ONLY
IF @DebugMode = 1
BEGIN
SELECT * FROM @TeamELO
SELECT * FROM @Players
SELECT * FROM @Accounts
RETURN
END
-- IF GAME COUNTED, UPDATE ALL THE STUFF
IF @GameCounted = 1
BEGIN
UPDATE @Players
SET Wins = Wins + gt.GameTeamWinner,
Losses = Losses + 1 - gt.GameTeamWinner,
Adjustment = t.Adjustment,
Expected = t.Expected
FROM @TeamELO t
INNER JOIN GameTeam gt ON gt.GameTeamNumber = t.TeamNumber
INNER JOIN GameTeamMember gtm ON gtm.GameTeamID = gt.GameTeamIdentID
INNER JOIN @Accounts a ON a.Callsign = gtm.GameTeamMemberCallsign
INNER JOIN @Players p ON p.Member_ID = a.Member_ID
WHERE gt.GameID = @GameID
INSERT INTO GamePlayerELO (GameID, GamePlayerELOMemberID, GamePlayerELORating, GamePlayerELOAdjustment, GamePlayerELOModifier)
SELECT @GameID, Member_ID, ELORating, Adjustment, Modifier
FROM @Players
-- USED DURING DEBUGGING ONLY
IF @OutputResults = 1
BEGIN
SELECT * FROM @Players
END
-- CACHE KILLCOUNTS
DECLARE @AWE TABLE (Member_ID int, Kills int, Ejects int)
INSERT INTO @AWE(Member_ID, Kills, Ejects)
SELECT Member_ID, Kills, Ejects
FROM dbo.ASGSNetGetGameAWECount(@GameID)
--UPDATE LOOP
DECLARE @Member_ID int
WHILE (SELECT COUNT(*) FROM @Players) > 0
BEGIN
SET @Member_ID = (SELECT TOP 1 Member_ID FROM @Players)
-- IF PLAYER ALREADY HAS AN ELO RECORD
IF EXISTS(SELECT Member_ID FROM ELO WHERE Member_ID = @Member_ID)
BEGIN
UPDATE ELO
SET Member_ID = p.Member_ID,
ELORanking = CASE
WHEN ELORanking < 800 THEN ELORanking + (p.Adjustment * (p.AdjustedModifier * 4))
WHEN ELORanking BETWEEN 800 AND 1500 THEN ELORanking + (p.Adjustment * (p.AdjustedModifier * 2))
WHEN ELORanking BETWEEN 1500 AND 2200 THEN ELORanking + (p.Adjustment * (p.AdjustedModifier * 1))
WHEN ELORanking > 2200 THEN ELORanking + (p.Adjustment * (p.AdjustedModifier * .5))
END,
ELOWins = ELOWins + p.Wins,
ELOLosses = ELOLosses + p.Losses,
ELOExpected = ((ELOExpected * (ELOWins + ELOLosses)) + p.Expected) / (ELOWins + ELOLosses + 1),
ELOKills = ELOKills + awe.Kills,
ELOEjects = ELOEjects + awe.Ejects
FROM @Players p
INNER JOIN ELO e ON e.Member_ID = p.Member_ID
INNER JOIN @AWE awe ON awe.Member_ID = p.Member_ID
WHERE p.Member_ID = @Member_ID
AND p.Member_ID > 0
END
-- IF PLAYER HAS NO ELO RECORDS (NEW)
ELSE
BEGIN
INSERT INTO ELO (Member_ID, ELORanking, ELOWins, ELOLosses, ELOExpected, ELOKills, ELOEjects)
SELECT p.Member_ID, p.ELORating, p.Wins, p.Losses, p.Expected, awe.Kills, awe.Ejects
FROM @Players p
INNER JOIN @AWE awe ON awe.Member_ID = p.Member_ID
WHERE p.Member_ID = @Member_ID
AND p.Member_ID > 0
END
DELETE FROM @Players WHERE Member_ID = @Member_ID
END
END
-- ARCHIVE GAME ELO HISTORY
INSERT INTO GameELO (GameID, GameELOTeamNumber, GameELORating, GameELOScore, GameELOOpponentRating, GameELOExpectedOutcome, GameELOAdjustment, GameELOCounted, GameELOReason)
SELECT @GameID, TeamNumber, ELORating, Winner, OpponentELO, Expected, Adjustment, @GameCounted, @GameReason
FROM @TeamELO
--CLEAN UP
DELETE FROM @Teams
DELETE FROM @TeamELO
" border="0" alt="blush.gif" /> 
