def addPlayerAchievement(AchID, playerID, newAchievement, achievedDate,
progressA, progressB):
#do something
AchID = str(AchID)
conn = connectToSource()
cursor = conn.cursor()
if achievedDate == "0000-00-00":
achievedDate = None
query = """
insert into PlayerAchievement
(AchID,PlayerID,newAchievement,achievedDate,progressA,progressB)
VALUES (%s, %s, %s, %s, %s, %s)
ON CONFLICT (AchID, PlayerID) DO
UPDATE
SET newAchievement = %s,
achievedDate = %s,
progressA = %s,
progressB = %s
"""
results = cursor.execute(
query, (AchID, playerID, newAchievement, achievedDate, progressA,
progressB, newAchievement, achievedDate, progressA, progressB))
conn.commit()
closeConnection()
def addGame(timestamp, arena, gametype):
# returns UUID of existing game if already exists, otherwise creates
# games are never updated.
conn = connectToSource()
cursor = conn.cursor()
query = """select GameTimestamp, GameUUID from Games
where GameTimestamp = cast(%s as timestamp) AND
GameName = %s AND
ArenaName = %s"""
cursor.execute(query, (timestamp, gametype, arena))
result = cursor.fetchone()
if result == None:
query = """INSERT INTO Games
(GameTimestamp, ArenaName, GameName, GameUUID)
VALUES
(cast(%s as timestamp),%s,%s,%s)
"""
gameUUID = str(uuid.uuid4())
cursor.execute(query, (timestamp, arena, gametype, gameUUID))
#print ("SQLconnector.insertGame: Insert game check added a game! : %s" % result)
conn.commit()
closeConnection()
return gameUUID
else:
# print ("SQLconnector: Insert game check found an exiting game! : %s" % result)
return result[1]
return ''
def addPlayer(playerID, GamerTag, Joined, missions):
now = "%s" % datetime.datetime.now()
conn = connectToSource()
cursor = conn.cursor()
query = sql.SQL("""select missions from players where playerID = %s""")
playerneedsUpdate = False
try:
results = cursor.fetchone()
if results[0] != missions:
playerneedsUpdate = True
except Exception as e:
playerneedsUpdate = True
query = sql.SQL("""insert into Players
(PlayerID,GamerTag,Joined,Missions,firstsummaryupdate,lastsummaryupdate)
VALUES
(%s,%s,%s,%s,%s,%s)
ON CONFLICT (PlayerID) DO UPDATE
SET Missions = %s, lastsummaryupdate = %s
""")
data = (playerID, GamerTag, Joined, missions, now, now, missions, now)
try:
cursor.execute(query, data)
DBG(" DBG: SQLHelper.AddPlayer - Added new player %s" % playerID, 3)
except Exception as e:
DBG("Failed to UPSERT player %s because %s" % (playerID, e), 2)
conn.commit()
closeConnection()
return playerneedsUpdate
def fetchIndividualWithID(id):
conn = connectToSource()
cursor = conn.cursor()
SQL = """select PlayerID from Players where PlayerID = %s or PlayerID =%s%s or GamerTag ilike %s order by missions desc limit 1"""
prefix = cfg.getConfigString("ID Prefix")
data = (id, prefix, id, '%%%s%%' % (id))
#print (SQL % data)
cursor.execute(SQL, data)
results = cursor.fetchone()
if results is not None and len(results) == 1:
#print(results)
feedbackQueue.q.put("Found player, updating")
id = results[0]
manualTargetSummaryAndIncludeRank(id)
manualTargetForGames(id)
manualTargetAchievements(id)
from BuildAchievementScoresToJSON import executeAchievementBuild
from BuildHeadToHeadsToJSON import buildHeadToHeads
from BuildMonthlyScoresToJSON import executeMonthlyScoresBuild
from BuildPlayerBlob import executeBuildPlayerBlobs
from BuildMonthlyStarQualityToJSON import executeBuildMonthlyStars
#executeAchievementBuild()
#executeMonthlyScoresBuild()
#buildHeadToHeads()
#executeBuildPlayerBlobs()
#executeBuildMonthlyStars()
elif results is None:
feedbackQueue.q.put(
"Didn't find ID in database, performing summary search")
manualTargetSummary(id)
def jobBlock(jobWhichBlocks, jobWhichIsBlocked):
SQL = """INSERT INTO jobsblocking
(jobid, blockingid)
values (%s,%s)"""
conn = connectToSource()
cursor = conn.cursor()
cursor.execute(SQL, (jobWhichBlocks, jobWhichIsBlocked))
conn.commit()
closeConnection()
def jobEnd(ID):
SQL = """UPDATE jobsList
SET finished = now(),
resumeindex = NULL
where id = %s """
conn = connectToSource()
cursor = conn.cursor()
cursor.execute(SQL, (ID, ))
conn.commit()
closeConnection()
def jobHeartbeat(ID, progressIndex):
SQL = """UPDATE jobsList
SET lastHeartbeat = now(),
resumeIndex = %s
where ID = %s """
conn = connectToSource()
cursor = conn.cursor()
cursor.execute(SQL, (progressIndex, ID))
conn.commit()
closeConnection()
def updateGameFetchMetrics(playerID):
conn = connectToSource()
cursor = conn.cursor()
now = datetime.datetime.now()
SQL = """
UPDATE players SET
lastdetailupdate = %s,
firstdetailupdate = COALESCE(firstdetailupdate,%s)
where playerID = %s
"""
data = (now, now, playerID)
cursor.execute(SQL, data)
conn.commit()
closeConnection()
def addArenaRank(bigObj):
conn = connectToSource()
cursor = conn.cursor()
for obj in bigObj:
sql = '''insert into
ArenaRanksLookup (ArenaName,rankNumber,rankName)
VALUES (%s,%s,%s)
ON CONFLICT (arenaName, rankNumber) DO UPDATE
SET rankName = %s
'''
cursor.execute(sql, (obj['ArenaName'], obj['rankNumber'],
obj['rankName'], obj['rankName']))
conn.commit()
closeConnection()
return
def getPlayers(offset=0):
conn = connectToSource()
cursor = conn.cursor()
query = """with data as ( select row_number() over (order by Level desc, Missions desc) as ID, PlayerID, Missions, Level from Players)
select PlayerID from data
where (ID >= 0)
order by ID asc
offset %s
"""
cursor.execute(query, (offset, ))
results = cursor.fetchall()
closeConnection()
return results
def fetchAllAchievements(targetIDs, jobID=None):
conn = connectToSource()
cursor = conn.cursor()
totalToUpdate = len(targetIDs)
offset = 0
if jobID == None:
jobID = jobStart("Fetch achievements, all known players", 0,
"FetchAchievements.fetchAllAchievements", None)
startTime = datetime.datetime.now()
else:
query = """select ID, started,lastheartbeat,resumeindex, methodname from jobslist
where finished is null and ID = %s and methodname = 'FetchAchievements.executeFetchAchievements'
order by started desc"""
cursor.execute(query, (jobID, ))
results = cursor.fetchone()
if results is None:
DBG(
"Could not find valid achievement job for ID [%s] , aborting!"
% jobID, 1)
return
if results[2] is not None:
startTime = results[2]
else:
startTime = results[1]
if results[3] is not None:
offset = results[3]
playerCounter = offset
totalPlayerCount = len(targetIDs)
lastHeartbeat = startTime
for ID in targetIDs:
__heartbeat(jobID, lastHeartbeat, playerCounter, startTime,
totalPlayerCount, ID)
fetchIndividualsAchievements(ID)
playerCounter = playerCounter + 1
jobEnd(jobID)
endTime = datetime.datetime.now()
f = open("Stats.txt", "a+")
f.write(
"Queried {0} players' achievements, operation completed after {1}. \t\n"
.format(len(targetIDs), endTime - startTime))
f.close()
closeConnection()
def buildHeadToHeads():
query = """
with data as (
select p.PlayerID
, GamerTag
, GameName
, GameTimestamp
, Score
, count(p.PlayerID) over (partition by GameTimestamp order by GameTimestamp desc) countofPlayers
, TO_CHAR(gameTimestamp,'YYYY-MM') as gameMonth
from Participation p
join games g on p.GameUUID = g.GameUUID
join players pl on pl.PlayerID = p.PlayerID
where GameName in ('Individual', 'Colour Ranked', 'Highlander','Individual Supercharge','Gladiator (Individual)','Shadows (Individual)')
and ArenaName = %s
)
select d1.PlayerID, d1.Score, d1.GamerTag, d2.PlayerID, d2.GamerTag, d2.Score, d1.GameName, to_char(d1.GameTimestamp,'DD Mon YYYY') as GT, d1.gameMonth
from data d1 join data d2 on d1.GameTimestamp = d2.GameTimestamp and d1.PlayerID != d2.PlayerID and d1.Score >= d2.Score
where d1.countofPlayers = 2
order by d1.GameTimestamp desc
limit 7"""
data = (cfg.getConfigString("SiteNameReal"), )
conn = connectToSource()
cursor = conn.cursor()
results = cursor.execute(query, data)
JSONobject = {"ScoreTitle": "Recent Head to Head games!", "Match": []}
rows = cursor.fetchall()
for row in rows:
JSONobject["Match"].append({
"Player1Name": row[2],
"Player1Score": row[1],
"Player2Name": row[4],
"Player2Score": row[5],
"GameDate": row[7]
})
print(row)
f = open("JSONBlobs\\HeadToHeads.json", "w+")
f.write(json.dumps(JSONobject, indent=4))
f.close()
print("Head to Head matches written!")
def addPlayerArena(playerID, ArenaName, localMissions, localLevel,
localAvgScore):
conn = connectToSource()
cursor = conn.cursor()
sql = '''INSERT INTO public.playerarenasummary(
arenaname, localAvgStdScore, localMissions, localLevel, playerid)
VALUES (%s, %s, %s, %s, %s)
ON CONFLICT (arenaname,playerid) DO UPDATE
SET localAvgStdScore = %s,
localMissions = %s,
localLevel = %s'''
cursor.execute(sql, (ArenaName, localAvgScore, localMissions, localLevel,
playerID, localAvgScore, localMissions, localLevel))
conn.commit()
closeConnection()
return
def addPlayerAchievementScore(playerID, score):
conn = connectToSource()
cursor = conn.cursor()
query = "select playerID from players where playerID = %s "
data = (playerID, )
cursor.execute(query, data)
if cursor.fetchone() == None:
DBG(
"SQLHelper.addPlayerAchievementScore didn't find the player, could not update score",
2)
else:
#print ("SQLHelper.addPlayerAchievementScore found the player, updating their achievement score")
query = "update players set AchievementScore = ? where playerID = ?"
cursor.execute(query, (score, playerID))
conn.commit()
closeConnection()
def getPlayersWhoMightNeedAchievementUpdates(scope, offset=0):
conn = connectToSource()
cursor = conn.cursor()
query = """
select distinct PlayerID from Participation
where insertedTimestamp > current_date - INTERVAL '7 days'
offset %s
"""
cursor.execute(query, (offset, ))
results = cursor.fetchall()
playerList = []
for result in results:
#print (result[0])
playerList.append(result[0])
conn.commit()
closeConnection()
return playerList
def jobStart(description,
resumeIndex,
methodName,
methodParams,
completeIndex=None,
delay=0):
ID = str(uuid.uuid4())
SQL = """INSERT into jobsList ("desc","id","started","methodname","methodparams","completeindex") values
(%s,%s,now() + interval '%s minutes',%s,%s,%s)"""
conn = connectToSource()
cursor = conn.cursor()
cursor.execute(SQL, (description, ID, delay, methodName,
json.dumps(methodParams), completeIndex))
conn.commit()
closeConnection()
return ID
def getActiveJobs():
global _activeJobsCacheTime
global _activeJobsCacheResults
if _activeJobsCacheTime is None or (datetime.datetime.now() -
_activeJobsCacheTime).seconds >= 5:
if _activeJobsCacheTime is not None:
delta = (datetime.datetime.now() - _activeJobsCacheTime)
SQL = """ with data as (select row_number() over (partition by healthstatus order by finished desc ) as row, * from public."jobsView")
select * from data where finished is null or (finished is not null and row <= 3 and row > 0)
order by finished desc, started asc"""
conn = connectToSource()
cursor = conn.cursor()
cursor.execute(SQL)
results = cursor.fetchall()
closeConnection()
_activeJobsCacheResults = results
_activeJobsCacheTime = datetime.datetime.now()
return _activeJobsCacheResults
def addAchievement(achName, Description, image, arenaName):
#do something
conn = connectToSource()
cursor = conn.cursor()
#print("SQLHelper.addAchievement: Didn't find [{0}], adding it".format(achName))
AchID = "%s%s" % (achName, arenaName)
AchID = hashlib.md5(AchID.encode("utf-8")).hexdigest()
query = """
INSERT into AllAchievements
(AchID, AchName, image, Description, ArenaName)
VALUES (%s,%s,%s,%s,%s)
ON CONFLICT (AchID) DO UPDATE
SET image = %s,
Description = %s
"""
cursor.execute(query, (str(AchID), achName, image, Description, arenaName,
image, Description))
conn.commit()
closeConnection()
return AchID
def addParticipation(gameUUID, playerID, score):
conn = connectToSource()
cursor = conn.cursor()
query = """select count (*) from Participation
where GameUUID = %s AND
PlayerID = %s"""
cursor.execute(query, (gameUUID, playerID))
result = cursor.fetchone()
if result[0] == 0:
query = """INSERT INTO Participation
(GameUUID, PlayerID, Score, insertedTimestamp)
VALUES
(%s,%s,%s, CURRENT_TIMESTAMP)
"""
result = cursor.execute(query, (gameUUID, playerID, score))
#print ("SQLconnector.addParticipation: Added player to game! : %s" % gameUUID)
conn.commit()
#else:
#print ("SQLconnector.addParticipation: We already know this player played this game! : %s" % gameUUID)
closeConnection()
return ''
def updateExistingPlayersLoad(JobID=None):
conn = connectToSource()
cursor = conn.cursor()
offset = 0
if JobID != None:
query = """select ID, started,lastheartbeat,resumeindex, methodname from jobslist
where finished is null and ID = %s and methodname = 'FetchPlayerUpdatesAndNewPlayers.updateExistingPlayers'
order by started desc"""
cursor.execute(query, (JobID, ))
results = cursor.fetchone()
if results[2] is not None:
startTime = results[2]
else:
startTime = results[1]
if results[3] is not None:
offset = results[3]
results = getPlayers(offset=offset)
for playerID in results:
wpq.summaryQ.put(playerID)
totalTargetsToUpdate = len(results)
closeConnection()
from SQLconnector import connectToSource
import json
conn = connectToSource()
cursor = conn.cursor()
sqlfile = open(
"/home/ctri/github/LF-Profiler/SQL Queries/ArenaDemographics-allYear.sql")
SQL = sqlfile.read() #.replace('', '')
conn = connectToSource()
cursor = conn.cursor()
cursor.execute(SQL)
results = cursor.fetchall()
SQLdesc = cursor.description
descCount = 0
for desc in SQLdesc:
print("%s %s" % (descCount, desc[0]))
descCount = descCount + 1
currentMonth = ""
months = []
for result in results:
print(result)
def executeMonthlyScoresBuild():
startDate = cfg.getConfigString("StartDate")
endDate = cfg.getConfigString("EndDate")
LastMonthStart = cfg.getConfigString("LastMonthStart")
arenaName = cfg.getConfigString("SiteNameReal")
SQL = '''
with data as (
SELECT
p.PlayerID,
GamerTag,
avg(Score) as averageScore,
count(GamerTag) as gamesPlayed,
to_char(GameTimestamp,'YYYY-MM') as GameMonth
FROM Participation p
inner join Players pl on p.PlayerID = pl.PlayerID
inner join Games g on p.GameUUID = g.GameUUID
where to_char(GameTimestamp,'YYYY-MM') in (%s,%s)
and (
g.GameName in ('Team','3 Teams','4 Teams', 'Colour Ranked','Individual')
or g.GameName in ('Standard - Solo', 'Standard - Team','Standard [3 Team] (10)','Standard [3 Team] (15)','Standard 2 Team',
'Standard 3 Team','Standard 4 Team','Standard Individual','Standard Multi team','- Standard [2 Team] (15))')
)
and g.ArenaName = %s
GROUP BY p.PlayerID, pl.GamerTag, to_char(GameTimestamp,'YYYY-MM')
)
select d1.PlayerID, d1.GamerTag, cast(d1.averageScore as int),d1.gamesPlayed, round(cast(d1.averageScore -d2.averageScore as numeric),2) as changeInScore
from data d1 left join data d2 on d1.PlayerID = d2.PlayerID and d1.GameMonth != d2.GameMonth
where d1.GameMonth = %s
order by averageScore desc;
'''
conn = connectToSource()
cursor = conn.cursor()
cursor.execute(SQL,(startDate[0:7],LastMonthStart[0:7],arenaName,startDate[0:7]))
JSON = {
'ScoreTitle' : "Average Scores for known players, in Standard Games, between {1} and {0}" .format(startDate,endDate),
'ScoreGreaterOrEqualDate' : startDate,
'ScoreLessDate' : endDate,
'Player' : [{
# 'Name' : "C'tri",
# 'AverageScore' : -1,
# 'MissionsPlayed' : -1,
}],
}
for result in cursor.fetchall():
#print (result)
changeInScore = None
if result[4] is not None: changeInScore = "↑%s" % result[4] if result[4] > 0 else "↓%s" % abs(result[4])
JSON['Player'].append({'Name' : result[1], 'AverageScore' : result[2], 'MissionsPlayed' : result[3], "ChangeInScore": changeInScore})
filepart = "MonthlyScore"
if os.name == "nt":
divider = "\\"
elif os.name == "posix":
divider = "/"
f = open("JSONBlobs%s%sLatest.json" % (divider,filepart), "w+")
f.write(json.dumps(JSON,indent=4))
f = open("JSONBlobs%s%s%s.json" % (divider, cfg.getConfigString("ID Prefix"),filepart), "w+")
f.write(json.dumps(JSON,indent=4))
DBG ("Monthly average score blobs written!",3)
def execute():
SQL = """
with starQuality as
(
select playerID, gamertag
,round(avg(starsforgame),2) as avgQualityPerGame
,gamemonth
,count(*)::int as gamesPlayed
from public."participationWithStars"
where gameTimestamp >= %s
and gameTimestamp < %s
and arenaName ilike %s
group by 1,2,4
),
GoldenTopX as
(
select PlayerID, gamertag
, ROW_NUMBER() over (partition by gameMonth order by avgQualityPerGame desc) as playerRank
, gameMonth
from StarQuality
where StarQuality.gamesPlayed >= 3
order by AvgQualityPerGame desc
--limit 3
),
GoldenTop3 as
(
select * from GoldenTopX
where playerRank <= 3
)
select * from GoldenTop3 g3 join StarQuality sq on
sq.playerID = g3.playerID and
sq.gameMonth = g3.gameMonth
order by g3.gameMonth asc, g3.playerRank asc
"""
#--startDate,endDate, siteNameReal, name (sen) (sen) (sen) name
cfg = ConfigHelper.getConfig()
startYear = int(cfg["StartDate"][0:4])
endYear = startYear + 1
startYear = "%s-01-01" % startYear
endYear = "%s-01-01" % endYear
#startYear = '2019-08-01'
parameters = (startYear, endYear, cfg['SiteNameReal'])
conn = connectToSource()
cursor = conn.cursor()
cursor.execute(SQL, parameters)
results = cursor.fetchall()
SQLdesc = cursor.description
descCount = 0
for desc in SQLdesc:
#print("%s %s" % (descCount,desc[0]))
descCount = descCount + 1
currentMonth = ""
months = []
for result in results:
if result[3] != currentMonth:
currentMonth = result[3]
#print("== New month = [%s]" % (currentMonth,))
month = {}
months.append(month)
month["month"] = result[3]
players = []
month["players"] = players
#print(result)
player = {}
player["playerName"] = result[5]
player["gamePlayed"] = result[8]
player["averageStars"] = "%s" % (result[6])
players.append(player)
playerName = "%s%s" % (result[5], " " * 15)
playerName = playerName[0:10]
#print("%s %s, %s games played \t %s stars per game (avg) " % (result[1],playerName , result[6], result[8]) )
#print(json.dumps(months,indent=4))
#playerID, rank, month,
#ID, gamertag, avgopponents
#gamesplayed, averagerank, avgqual
#totalqual, avgscore, achievementscore
#gamemonth
filepart = "AnnualTop3s"
if os.name == "nt":
divider = "\\"
elif os.name == "posix":
divider = "/"
f = open(
"JSONBlobs%s%s%s-%s.json" %
(divider, cfg["ID Prefix"], filepart, startYear[0:4]), "w+")
f.write(json.dumps(months, indent=4))
DBG("Annual top3s complete!", 3)
def updateExistingPlayersLoop(JobID=None):
startTime = datetime.datetime.now()
conn = connectToSource()
cursor = conn.cursor()
offset = 0
TotalEntries = wpq.summaryQ.qsize()
if JobID == None:
JobID = jobStart(
"Fetch summaries, all known players", 0,
"FetchPlayerUpdatesAndNewPlayers.updateExistingPlayers", None,
TotalEntries)
startTime = datetime.datetime.now()
global WorkerStatus
lastHeartbeat = startTime
counter = offset
jobHeartbeat(JobID, counter)
while wpq.summaryQ.empty() == False:
targetID = wpq.summaryQ.get()
targetID = targetID[0]
heartbeatDelta = (datetime.datetime.now() -
lastHeartbeat).total_seconds()
if heartbeatDelta > 30:
jobHeartbeat(JobID, counter)
lastHeartbeat = datetime.datetime.now()
counter = TotalEntries - wpq.summaryQ.qsize()
WorkerStatus = {}
WorkerStatus["CurEntry"] = counter
WorkerStatus["TotalEntries"] = TotalEntries
WorkerStatus["CurrentAction"] = "summary of %s" % (targetID)
delta = "[ Calculating ]"
if counter >= 20:
delta = ((datetime.datetime.now() - startTime).total_seconds() /
counter)
delta = (TotalEntries - counter) * delta #seconds remaining
seconds = round(delta, 0)
minutes = 0
hours = 0
if (seconds > 60):
minutes = math.floor(seconds / 60)
seconds = seconds % 60
if (minutes > 60):
hours = math.floor(minutes / 60)
minutes = minutes % 60
delta = "%ih, %im, %is" % (hours, minutes, seconds)
wpq.updateQ(counter, TotalEntries, "summary of %s" % (targetID), delta)
ID = targetID.split('-')
player = fetchPlayer_root('', ID[0], ID[1], ID[2])
datetime_list = []
missions = 0
level = 0
for i in player["centre"]:
datetime_list.append(str(i["joined"]))
missions += int(i["missions"])
level = max(level, int(i["skillLevelNum"]))
joined = min(datetime_list)
codeName = str(player["centre"][0]["codename"])
#DBG("Summary update for player %s-%s-%s, [%i/%i]" % (ID[0],ID[1],ID[2],counter,TotalEntries),3)
addPlayer(targetID, codeName, joined, missions)
_parseCentresAndAdd(player["centre"], targetID)
jobEnd(JobID)
endTime = datetime.datetime.now()
f = open("Stats.txt", "a+")
f.write(
"Queried {0} players' aggregates, operation completed after {1}. \t\n".
format(TotalEntries, endTime - startTime))
f.close()
def findNewPlayers(siteName=None, jobID=None):
startTime = datetime.datetime.now()
lastHeartbeat = startTime
conn = connectToSource()
cursor = conn.cursor()
conn = connectToSource()
cursor = conn.cursor()
if siteName is not None:
targetID = cfg.findSiteIDFromName(siteName)
siteObj = cfg.getSiteWithoutActivatingByID(targetID)
sitePrefix = siteObj["ID Prefix"]
siteName = siteObj["SiteNameReal"]
else:
sitePrefix = cfg.getConfigString("ID Prefix")
siteName = cfg.getConfigString("SiteNameReal")
params = {}
params["siteName"] = siteName
if jobID is None:
jobID = jobStart(" new players at [%s]" % siteName, 0,
"FetchPlayerUpdatesAndNewPlayers.findNewPlayers",
params)
else:
jobHeartbeat(jobID, 0)
TickerIcon = ["|", "/", "-", '\\']
sitePrefixforSQL = sitePrefix + "%"
query = sql.SQL("""
with IDs as ( select
cast (split_part(pl.PlayerID,'-',3) as integer) as ID
from players pl
where playerID like %s
order by 1 desc
offset 5
)
select max (ID) from IDs
""")
cursor.execute(query, (sitePrefixforSQL, ))
result = cursor.fetchone()
if result == None or result[0] == None:
MaxPlayer = 199 #LaserForce seems to start numbering players at 100
else:
MaxPlayer = result[0]
region = sitePrefix.split("-")[0]
siteNumber = sitePrefix.split("-")[1]
ticker = 0
consecutiveMisses = 0
currentTarget = MaxPlayer - 100 #we've had situations where the system adds user IDs behind the maximum. This is a stopgap dragnet to catch trailing players.
AllowedMisses = 100
while consecutiveMisses <= AllowedMisses:
heartbeatDelta = (datetime.datetime.now() -
lastHeartbeat).total_seconds()
if heartbeatDelta > 30:
jobHeartbeat(jobID, 0)
lastHeartbeat = datetime.datetime.now()
conn.commit()
player = fetchPlayer_root('', region, siteNumber, currentTarget)
if 'centre' in player:
codeName = player["centre"][0]["codename"]
dateJoined = player["centre"][0]["joined"]
missionsPlayed = player["centre"][0]["missions"]
skillLevelNum = player["centre"][0]["skillLevelNum"]
addPlayer("%s%i" % (sitePrefix, currentTarget), codeName,
dateJoined, missionsPlayed)
_parseCentresAndAdd(
player["centre"],
'%s-%s-%s' % (region, siteNumber, currentTarget))
consecutiveMisses = 0
else:
DBG(
"DBG: FetchPlayerUpdatesAndNewPlayers.findNewPlayers - Missed a player 7-X-%s"
% (currentTarget), 3)
consecutiveMisses = consecutiveMisses + 1
wpq.updateQ(consecutiveMisses, AllowedMisses,
"Seeking new... %s" % TickerIcon[ticker % 4], "ETA ?")
currentTarget = currentTarget + 1
ticker = ticker + 1
endTime = datetime.datetime.now()
jobEnd(jobID)
f = open("Stats.txt", "a+")
f.write(
"searched for {0} players, operation completed after {1}. \t\n".format(
currentTarget - MaxPlayer, endTime - startTime))
wpq.updateQ(1, 1, "Seeking new... %s", "Complete")
f.close()
conn.commit()
closeConnection()
def fetchIndividualsAchievements(ID):
conn = connectToSource()
cursor = conn.cursor()
IDpieces = ID.split("-")
#step 1 get all known achieveements and put into Key Value pairs
#the key is the MD5 function of AchName + ArenaName
query = """select md5(concat(achname,arenaname)) as hash,achName,arenaName,achieveddate from allachievements aa join playerachievement pa
on aa.achID = pa.achID
where playerID = %s"""
cursor.execute(query, (ID, ))
results = cursor.fetchall()
knownAchievements = {}
if results is not None and len(results) != 0: #new player handling
for knownAchievement in results:
knownAchievements[knownAchievement[0]] = knownAchievement
#step 2 - get all achievements from IPL
allAchievements = fetchPlayerAcheivement_root('', IDpieces[0], IDpieces[1],
IDpieces[2])
totalAchievemnts = 0
if allAchievements.__len__() > 0:
if '1' in allAchievements["centre"]:
DBG(
"DBG: FetchAchievements.fetchAllAchivements: ABNORMAL RESPONSE handled for user %s"
% (ID), 2)
DBG(
"DBG: FetchAchievements.fetchAllAchivements: Manually check they don't have multiple sites' achievements",
2)
holdingVar = []
holdingVar.append(allAchievements["centre"]['1'])
allAchievements["centre"] = holdingVar
#print (json.dumps(allAchievements["centre"]))
for centre in allAchievements["centre"]:
#we don't filter by arena, becuase we do achievement searches seperately, and because IPL has to do all the hard work each request anyway.
#this means less requests against IPL if we do achieves globally.
#addPlayerAchievementScore(ID,centre["score"])
#print (allAchievements)
for achievement in centre["achievements"]:
#step3 - for each achievement, hash it, test if we have it
#if we have it, test if the player needs updated
#if the player needs updated, update
#if we don't have it, add it, and update the player.
tohash = "%s%s" % (achievement["name"], centre['name'])
tohash = tohash.encode(encoding="utf-8")
md5value = hashlib.md5(tohash).hexdigest()
if md5value in knownAchievements: #seen it before!
totest = knownAchievements[md5value][3]
if totest == None:
totest = "0000-00-00"
if achievement["achievedDate"] != "%s" % (
totest
): #player has achieved / learned about this achieve!
addPlayerAchievement(md5value, ID,
achievement["newAchievement"],
achievement["achievedDate"],
achievement["progressA"],
achievement["progressB"])
DBG(
"updated player progress for known achievement %s vs %s"
% (achievement["achievedDate"], totest), 3)
else: #new achievement!
newAchMD5 = addAchievement(achievement["name"],
achievement["description"],
achievement["image"],
centre['name'])
addPlayerAchievement(newAchMD5, ID,
achievement["newAchievement"],
achievement["achievedDate"],
achievement["progressA"],
achievement["progressB"])
DBG(
"updated player progress for NEW achievement: [%s][%s]"
% (achievement["name"], centre['name']), 3)
totalAchievemnts = totalAchievemnts + len(centre["achievements"])
#print ("Updated %i achievements for player %s. [%i/%i]" % (totalAchievemnts,ID,playerCounter,totalToUpdate))
closeConnection()
def getTop5PlayersRoster(startDate, endDate, ArenaName):
conn = connectToSource()
cursor = conn.cursor()
query = """with AverageScores as (
SELECT
Players.PlayerID,
avg(Score) as averageScore
FROM Participation
inner join Players on Participation.PlayerID = Players.PlayerID
inner join Games on Participation.GameUUID = Games.GameUUID
where GameTimestamp >= %s
and GameTimeStamp < %s
and games.ArenaName = %s
and (
Games.GameName in ('Team','3 Teams','4 Teams', 'Colour Ranked','Individual')
or Games.GameName in ('Standard - Solo', 'Standard - Team','Standard [3 Team] (10)','Standard [3 Team] (15)','Standard 2 Team',
'Standard 3 Team','Standard 4 Team','Standard Individual','Standard Multi team','- Standard [2 Team] (15))')
)
group by Players.PlayerID
),
StarQuality as
(
SELECT playerID, gamerTag, avg(starsforgame) as avgQualityPerGame, count(*) as gamesPlayed
from public."participationWithStars"
where GameTimestamp >= %s
and GameTimeStamp < %s
and ArenaName = %s
group by 1,2
),
GoldenTop3 as
(
select PlayerID, ROW_NUMBER() over (order by avgQualityPerGame desc) as playerRank from StarQuality
where StarQuality.gamesPlayed >= 3
order by AvgQualityPerGame desc
limit 3
),
BestScorer as (
SELECT PlayerID --, GamerTag, round(AverageOpponents,2) as AverageOpponents, gamesPlayed, round(AverageRank,2) as AverageRank,
--round((AverageOpponents * 1/(AverageRank/AverageOpponents)),2) as AvgQualityPerGame,
--round((AverageOpponents * gamesPlayed * 1/(AverageRank/AverageOpponents)),2) as TotalQualityScore, averageScore, AchievementScore
from AverageScores
where PlayerID not in (select PlayerID from GoldenTop3)
order by averageScore desc
limit 1
),
Achievers as (
select playerID, count(*) achievements
from PlayerAchievement pa join AllAchievements aa on aa.AchID = pa.AchID
where achievedDate is not null and aa.ArenaName = %s
group by playerID
),
BestAchiever as(
SELECT Ach.PlayerID
--GamerTag, round(AverageOpponents,2) as AverageOpponents, gamesPlayed, round(AverageRank,2) as AverageRank,
--round((AverageOpponents * 1/(AverageRank/AverageOpponents)),2) as AvgQualityPerGame,
--round((AverageOpponents * gamesPlayed * 1/(AverageRank/AverageOpponents)),2) as TotalQualityScore, averageScore, AchievementScore
from Achievers ach
where PlayerID not in (select PlayerID from GoldenTop3) and PlayerID not in (select PlayerID from BestScorer)
order by achievements desc
limit 1
)
select p.PlayerID , GamerTag, playerRank, 'Top3' as source from GoldenTop3 p
join Players pl on pl.PlayerID = p.PlayerID
union
select p.PlayerID , GamerTag, 4 as playerRank, 'BestScorer' as source from BestScorer p
join Players pl on pl.PlayerID = p.PlayerID
union
select p.PlayerID , GamerTag, 5 as playerRank, 'BestAchiever' as source from BestAchiever p
join Players pl on pl.PlayerID = p.PlayerID
order by playerRank asc
"""
data = (startDate, endDate, ArenaName, startDate, endDate, ArenaName,
ArenaName)
cursor.execute(query, data)
rows = cursor.fetchall()
if rows == None:
DBG(
" SQLHelper.getTop5Players didn't find any players. Is there data in all tables?/",
2)
else:
DBG("SQLHelper.getTop5Players found all 5 players", 3)
conn.commit()
closeConnection()
return rows
def getInterestingPlayersRoster(includeChurned,
startDate,
period,
siteName=None,
offset=0):
conn = connectToSource()
cursor = conn.cursor()
if includeChurned == True:
query = """
select * from InterestingPlayers
order by Missions desc, SeenIn60Days Asc
OFFSET %s;
"""
if offset == None:
offset = 0
cursor.execute(query, (abs(offset), ))
elif siteName is not None:
query = sql.SQL("""
with MostRecentPerArena as
(
select max(g.GameTimestamp) as mostRecent, p.playerID, missions, level
from Games g join Participation p on g.GameUUID = p.GameUUID
join players pl on p.PlayerID = pl.playerID
group by p.PlayerID,Missions,level
),
playerMaxLevel as
(
select max (locallevel) maxLevel, playerID
from playerarenasummary pas
group by playerID
)
select Missions, maxLevel, pml.PlayerID, MostRecent
from MostRecentPerArena mrpa join playerMaxLevel pml on pml.playerID = mrpa.playerID
where mostRecent > to_date(%s,'YYYY-MM-DD') - INTERVAL '1 day' * %s
order by Level desc, Missions desc, mostRecent Asc
offset %s;
""")
cursor.execute(query, (startDate, period, offset))
else:
query = sql.SQL("""
with MostRecentPerArena as
(select max(g.GameTimestamp) as mostRecent, p.playerID, missions, level
from Games g join Participation p on g.GameUUID = p.GameUUID
join players pl on p.PlayerID = pl.playerID
where ArenaName = %s
group by p.PlayerID,Missions,level)
select Missions, Level, PlayerID, MostRecent from MostRecentPerArena
where mostRecent > to_date(%s,'YYYY-MM-DD') - INTERVAL '1 day' * %s
order by Level desc, Missions desc, mostRecent Asc
offset %s;
""")
if siteName == None: #If not set, use default
siteName = cfg.getConfigString("SiteNameReal")
cursor.execute(query, (siteName, startDate, period, offset))
results = cursor.fetchall()
playerList = []
for result in results:
#print (result[0])
playerList.append(result[2])
conn.commit()
closeConnection()
return playerList
def executeMonitor():
time.sleep(5)
conn = connectToSource()
cursor = conn.cursor()
SQL = """
select age,"desc",ID,methodname,started,finished,lastheartbeat,resumeindex, methodParams, healthstatus,percenttocompletion, countofblocking
from public."jobsView"
where
healthstatus = 'dead'
order by lastheartbeat asc, started asc
"""
activeThreads = []
seconds = 29
while not isTerminated():
seconds = seconds + 1
#print("TIME: %s, remainder: %s" % (seconds,seconds % 30 ))
if seconds % 30 == 0: #every 30th second
seconds = 0
cursor.execute(SQL)
conn.commit()
#print ("---")
for result in cursor.fetchall():
if result[3] == "FetchPlayerAndGames.executeQueryGames":
MaxThreads = ConfigHelper.getConfigString(
"MaxWorkerThreads")
threadName = "%s:%s" % (result[2][0:3], result[1])
if checkThread(activeThreads, threadName) == 0:
params = json.loads(result[8])
FetchPlayerAndGames.QueryGamesLoad(params["scope"],
offset=result[7],
ID=result[2])
counter = 0
while checkThread(activeThreads,
threadName) < MaxThreads:
counter = counter + 1
offset = 0
if result[7] != None:
offset = result[7]
print("Debug insertion")
t = threading.Thread(
target=FetchPlayerAndGames.QueryGamesLoop,
args={result[2]},
kwargs={"counter": offset + counter}) #
t.name = threadName
t.start()
activeThreads.append(t)
TRQ.q.put(t)
elif result[
3] == "FetchPlayerUpdatesAndNewPlayers.updateExistingPlayers":
MaxThreads = ConfigHelper.getConfigString(
"MaxWorkerThreads")
threadName = "%s:%s" % (result[2][0:3], result[1])
if checkThread(activeThreads, threadName) == 0:
FetchPlayerUpdatesAndNewPlayers.updateExistingPlayersLoad(
JobID=result[2])
while checkThread(activeThreads, threadName) < MaxThreads:
t = threading.Thread(
target=FetchPlayerUpdatesAndNewPlayers.
updateExistingPlayersLoop,
kwargs={
"JobID": result[2]
}) #this method gets offset from the job ID
t.name = threadName
t.start()
activeThreads.append(t)
TRQ.q.put(t)
#execute known method.
elif result[3] == "FetchAchievements.executeFetchAchievements":
MaxThreads = ConfigHelper.getConfigString(
"MaxWorkerThreads")
threadName = "%s:%s" % (result[2][0:3], result[1])
params = json.loads(result[8])
if checkThread(activeThreads, threadName) == 0:
FetchAchievements.FetchAchievementsLoad(
params["scope"], jobID=result[2], offset=result[7])
while checkThread(activeThreads, threadName) < MaxThreads:
t = threading.Thread(
target=FetchAchievements.FetchAchievementsLoop,
args=(params["scope"], ),
kwargs={
"jobID": result[2],
}) #
t.name = "%s:%s" % (result[2][0:3], result[1])
t.start()
activeThreads.append(t)
TRQ.q.put(t)
elif result[
3] == "FetchPlayerUpdatesAndNewPlayers.findNewPlayers":
MaxThreads = ConfigHelper.getConfigString(
"MaxWorkerThreads")
params = json.loads(result[8])
t = threading.Thread(
target=FetchPlayerUpdatesAndNewPlayers.findNewPlayers,
#args=(params["siteName"],),
kwargs={
"jobID": result[2],
"siteName": params["siteName"]
}) #
t.name = "%s:%s" % (result[2][0:3], result[1])
if checkThread(activeThreads, t.name) == 0:
t.start()
activeThreads.append(t)
TRQ.q.put(t)
elif result[
3] == "buildAllForAllArenasSequentially.buildAllForAllArenasSequentially":
MaxThreads = ConfigHelper.getConfigString(
"MaxWorkerThreads")
t = threading.Thread(
target=BuildAllForAllArenasSequentially.
buildAllForAllArenasSequentially,
kwargs={
"jobID": result[2],
"startIndex": result[7]
})
t.name = "%s:%s" % (result[2][0:3], result[1])
if checkThread(activeThreads, t.name) == 0:
t.start()
activeThreads.append(t)
TRQ.q.put(t)
#print(result)
time.sleep(1) #sleep for a second to allow termination checks
def executeBuildMonthlyStars():
cachedconfig = cfg.getConfig()
curMonth = cachedconfig["StartDate"][0:7]
lastMonth = cachedconfig["LastMonthStart"][0:7]
endDate = cachedconfig["EndDate"]
arenaName = cachedconfig["SiteNameReal"]
SQL = '''
with subsetOfData as
(
select playerID
, gamerTag
, gamemonth
, avg(playercount) as AveragePlayerCount
, count(*) as GamesPlayed
, avg(Starsforgame) as AverageStarQuality
, sum(Starsforgame) as TotalStarQuality
, avg(rank) as AverageRank
from public."participationWithStars"
where arenaName = %s --
and GameMonth in (%s,%s) --
group by 1,2,3
)
select r1.PlayerID, r1.GamerTag,
round (cast(r1.AverageStarQuality as numeric),2) as AverageStarQuality,
round (cast(r1.AverageStarQuality * r1.gamesPlayed as numeric),2) as TotalStarQuality,
round (cast(r1.AveragePlayerCount as numeric),2) as AveragePlayerCount,
round (cast(r1.AverageRank as numeric),2) as AverageRank,
r1.gamesPlayed as GamesPlayed,
round (cast(r2.AverageRank - r1.AverageRank as numeric),2) as changeInRank,
round (cast(r1.AveragePlayerCount-r2.AveragePlayerCount as numeric),2) as changeInPlayers,
round (cast(r1.AverageStarQuality - r2.AverageStarQuality as numeric),2) as changeInStars
from subsetOfData r1 left join subsetOfData r2
on r1.PlayerID = r2.PlayerID and r1.GameMonth != r2.GameMonth
where r1.GameMonth = %s --
order by AverageStarQuality desc
'''
conn = connectToSource()
cursor = conn.cursor()
cursor.execute(SQL, (arenaName, curMonth, lastMonth, curMonth))
JSON = {
'ScoreTitle':
"Star Quality for all known players, between {1} and {0}".format(
curMonth, lastMonth),
'ScoreGreaterOrEqualDate':
curMonth,
'ScoreLessDate':
lastMonth,
'Player': [{
# 'Name' : "C'tri",
# 'AverageScore' : -1,
# 'MissionsPlayed' : -1,
}],
}
breakdownSQL = """
with data as (
select pl.PlayerID, pl.GamerTag, GameName, GameTimestamp, p.score,
ROW_NUMBER() over (partition by GameTimestamp order by GameTimestamp desc, score desc) as rank,
count(p.PlayerID) over (partition by GameTimestamp order by GameTimestamp desc) as playerCount,
TO_CHAR(GameTimestamp,'YYYY-MM') as GameMonth
from Participation p join Games g on p.GameUUID = g.GameUUID
join Players pl on p.PlayerID = pl.PlayerID
where g.ArenaName = %s
)
select gamename, rank, playerCount
, round(cast((cast(playerCount as float) * cast(playerCount as float)) / cast(rank as float) as numeric),2) as stars
from data
where GameMonth = %s
and playerID ilike %s
order by gametimestamp desc
"""
SQResults = cursor.fetchall()
counter = 0
for result in SQResults:
#print (result)
ChangeInRank = None
ChangeInPlayers = None
ChangeInStars = None
if result[7] is not None:
ChangeInRank = "↑%s" % result[7] if result[7] > 0 else "↓%s" % abs(
result[7])
if result[8] is not None:
ChangeInPlayers = "↑%s" % result[8] if result[
8] > 0 else "↓%s" % abs(result[8])
if result[9] is not None:
ChangeInStars = "↑%s" % result[9] if result[
9] > 0 else "↓%s" % abs(result[9])
SQObject = {
'JSID': counter,
'Name': result[1],
'StarQualityPerGame': "%s" % result[2],
'TotalStarQuality': "%s" % result[3],
'AverageOpponents': "%s" % result[4],
'gamesPlayed': result[6],
'AverageRank': "%s" % result[5],
'ChangeInRank': ChangeInRank,
'ChangeInPlayers': ChangeInPlayers,
'ChangeInSQPerGame': ChangeInStars,
'breakdown': []
}
cursor.execute(breakdownSQL, (arenaName, curMonth, result[0]))
breakdownResults = cursor.fetchall()
for breakdownEntry in breakdownResults:
SQBreakdown = {
"gameName": breakdownEntry[0],
"rank": breakdownEntry[1],
"totalPlayers": breakdownEntry[2],
"starsForGame": "%s" % breakdownEntry[3]
}
SQObject['breakdown'].append(SQBreakdown)
JSON['Player'].append(SQObject)
counter = counter + 1
filepart = "StarQuality"
if os.name == "nt":
divider = "\\"
elif os.name == "posix":
divider = "/"
f = open(
"JSONBlobs%s%s%s.json" %
(divider, cfg.getConfigString("ID Prefix"), filepart), "w+")
f.write(json.dumps(JSON, indent=4))
DBG("Star Quality blobs written!", 3)
