Free Allegiance Forums

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

Code: Select all

-- LOSERS WHO DROP GET DEDUCTED FULL POINTS
         UPDATE @Accounts
            SET AdjustedGameTime = dbo.ASGSGetPlayerMaxGameTime(@GameID, Member_ID)
          WHERE Winner = 0
            AND AdjustedGameTime >= 300

Code: Select all

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

Code: Select all

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