def drawHeader(CurrentWorkerStatus, threadcounter):
arenaHealth = QueryArena.healthCheck(cfg.getConfigString("SiteNameReal"))
print_at (0,0,"/----- LF Profiler --------------------------------------------------\\ ",PI=2)
print_at(1,0,"Dates: [ ] to [ ] | " )
print_at(1,10,cfg.getConfigString("StartDate"),1)
print_at(1,28,cfg.getConfigString("EndDate"),1)
renderBar((CurrentWorkerStatus["CurEntry"]/CurrentWorkerStatus["TotalEntries"]),1,48,4,1)
print_at(2,0,"Target site: [ ] | ")
print_at(2,19,(cfg.getConfigString("SiteNameShort")+ " "*20)[0:20],arenaHealth+1)
print_at(2,48,CurrentWorkerStatus["CurrentAction"],1)
print_at(3,0,"active threads: [ ] | " )
if threadcounter < 4:
threadColour = 1
elif threadcounter < 8:
threadColour = 2
else:
threadColour = 3
print_at(3,28,"%s threads"[:10] % threadcounter,threadColour)
print_at(3,48,"%s"%(CurrentWorkerStatus["ETA"]),1)
#1print_at (3,0,outStr)
print_at (4,0,"")
def recentGames(targetID):
sql = '''
with Ranks as
(
select GameTimestamp,games.GameUUID, GameName, Players.PlayerID, GamerTag, Score,
ROW_NUMBER() over (partition by GameTimestamp order by score desc) as gamePosition
from Games
join Participation on Games.GameUUID = Participation.GameUUID
join Players on Participation.PlayerID = Players.PlayerID
where Games.ArenaName = %s
),
totalPlayersPerGame as
(
select g.GameUUID, count(*) as playerCount
from Participation p join Games g on p.GameUUID = g.GameUUID
group by g.GameUUID
)
select ranks.GameUUID
,to_char(GameTimestamp,'DD Mon YYYY')
,GameName
,PlayerID
,GamerTag
,gamePosition
,playerCount
,playerCount * ( playerCount / gamePosition ) as SQ
from ranks join totalPlayersPerGame tppg
on ranks.GameUUID = tppg.GameUUID
where PlayerID = %s
order by GameTimestamp desc
limit 3
'''
global ordinal
global cursor
global config
arena = cfg.getConfigString("SiteNameReal")
cursor.execute(sql, (arena, targetID))
results = cursor.fetchall()
feedbackQueue.q.put("%s%s**Recent Games: for %s at %s **%s\n" %
(Back.BLACK, Fore.WHITE, targetID,
cfg.getConfigString("SiteNameShort"), Fore.WHITE))
for result in results:
#print(result)
temptStr = "%s: %s rank %s, of %s" % (result[1], (
result[2] + " " * 15)[:15], ordinal[int(result[5])], result[6])
feedbackQueue.q.put("%s%s(%s%s%s)%s\n" %
(Back.BLACK, Fore.WHITE, Fore.YELLOW, temptStr,
Fore.WHITE, Fore.WHITE))
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 QueryGamesLoad(scope,
interval="Null",
ArenaName=None,
offset=None,
ID=None):
params = {}
params["scope"] = scope
params["arenaName"] = cfg.getConfigString("SiteNameReal")
if scope == "full":
targetIDs = getInterestingPlayersRoster(
True,
cfg.getConfigString("StartDate"),
cfg.getConfigString("ChurnDuration"),
offset=offset)
if ID == None: #new job
ID = jobStart("Fetch games, all players",
0,
"FetchPlayerAndGames.executeQueryGames",
params,
len(targetIDs),
delay=-2)
elif scope == "activePlayers":
targetIDs = getInterestingPlayersRoster(
False,
cfg.getConfigString("StartDate"),
cfg.getConfigString("ChurnDuration"),
offset=offset,
siteName=None)
if ID == None: #new job
ID = jobStart("Fetch games, All arenas active players ",
0,
"FetchPlayerAndGames.executeQueryGames",
params,
len(targetIDs),
delay=-2)
else: #local
targetIDs = getInterestingPlayersRoster(
False,
cfg.getConfigString("StartDate"),
cfg.getConfigString("ChurnDuration"),
offset=offset,
siteName=params["arenaName"])
if ID == None: #new job
ID = jobStart("Fetch games, [%s] active players " %
(cfg.getConfigString("SiteNameShort")),
0,
"FetchPlayerAndGames.executeQueryGames",
params,
len(targetIDs),
delay=-2)
for targetID in targetIDs:
wpq.gamesQ.put(targetID)
return ID
def queueWeekly():
cfg = ConfigHelper.getConfig()
#WEEKLY UPDATE
#66
params = {}
params["scope"] = "activePlayers"
params["arenaName"] = ConfigHelper.getConfigString("SiteNameReal")
targetIDs = SQLHelper.getInterestingPlayersRoster(
False,
ConfigHelper.getConfigString("StartDate"),
ConfigHelper.getConfigString("ChurnDuration"),
siteName=None)
gamesID = SQLHelper.jobStart("Fetch games, All arenas active players ",
0,
"FetchPlayerAndGames.executeQueryGames",
params,
len(targetIDs),
delay=-2)
#67
params = {}
params["scope"] = "activePlayers"
targetIDs = SQLHelper.getInterestingPlayersRoster(
False,
ConfigHelper.getConfigString("StartDate"),
ConfigHelper.getConfigString("ChurnDuration"),
siteName=None)
achievesID = SQLHelper.jobStart(
"Fetch achievements, players from the last 7 days",
0,
"FetchAchievements.executeFetchAchievements",
params,
len(targetIDs),
delay=-2)
SQLHelper.jobBlock(gamesID, achievesID)
#6
renderID = SQLHelper.jobStart(
"Render all blobs",
0,
"buildAllForAllArenasSequentially.buildAllForAllArenasSequentially",
None,
len(cfg["configs"]),
delay=-2)
SQLHelper.jobBlock(achievesID, renderID)
def findPlayer(targetID):
global cursor
if cursor == None:
cursor = SQLHelper.connectToSource().cursor()
query = """select PlayerID from Players where PlayerID = %s or PlayerID =%s%s or GamerTag like %s order by missions desc limit 1"""
prefix = cfg.getConfigString("ID Prefix")
data = (targetID, prefix, targetID, '%%%s%%' % (targetID))
cursor.execute(query, data)
result = cursor.fetchone()
if result != None and len(result) == 1:
return result[0]
return "0-0-0"
def FetchAchievementsLoad(scope, jobID=None, offset=0):
params = {}
params["scope"] = scope
if scope == "full":
targetIDs = getInterestingPlayersRoster(
True,
cfg.getConfigString("StartDate"),
cfg.getConfigString("ChurnDuration"),
offset=offset)
if jobID == None:
jobID = jobStart("Fetch achievements, inactive players", 0,
"FetchAchievements.executeFetchAchievements",
params, len(targetIDs))
elif scope == "partial" or scope == "activePlayers":
targetIDs = getInterestingPlayersRoster(
False,
cfg.getConfigString("StartDate"),
cfg.getConfigString("ChurnDuration"),
offset=offset)
if jobID == None:
jobID = jobStart("Fetch achievements, active players", 0,
"FetchAchievements.executeFetchAchievements",
params, len(targetIDs))
elif scope == "recent":
targetIDs = getPlayersWhoMightNeedAchievementUpdates(scope,
offset=offset)
if jobID == None:
jobID = jobStart(
"Fetch achievements, players from the last 7 days", 0,
"FetchAchievements.executeFetchAchievements", params,
len(targetIDs))
print("Scope : %s" % (scope))
FetchAchievementsLoop(targetIDs, jobID=jobID)
return jobID
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 placesVisited(targetID):
sql = '''
select count (*) as gamesPlayed, TO_CHAR (max(g.GameTimestamp),'DD Mon YYYY') as mostRecentVisit, ArenaName from Participation p join Games g on p.GameUUID = g.GameUUID
where p.playerID = %s and ArenaName != %s
group by ArenaName
order by max(g.GameTimestamp) desc
limit 1'''
global cursor
results = cursor.execute(sql,
(targetID, cfg.getConfigString("SiteNameReal")))
feedbackQueue.q.put("%s%s**Recent travels:**%s\n" %
(Back.BLACK, Fore.WHITE, Fore.WHITE))
for result in cursor.fetchall():
feedbackQueue.q.put(
"%s%s(%svisited %s, on %s. %i observed games played there. %s)%s,"
% (Back.BLACK, Fore.WHITE, Fore.YELLOW, result[2], result[1],
result[0], Fore.WHITE, Fore.WHITE))
def PlaysWhen(targetID):
sql = '''
with dateData as (
select distinct case
when EXTRACT(DOW FROM GameTimestamp) = 1 then 'Sunday'
when EXTRACT(DOW FROM GameTimestamp) = 2 then 'Monday'
when EXTRACT(DOW FROM GameTimestamp) = 3 then 'Tuesday'
when EXTRACT(DOW FROM GameTimestamp) = 4 then 'Wednesday'
when EXTRACT(DOW FROM GameTimestamp) = 5 then 'Thursday'
when EXTRACT(DOW FROM GameTimestamp) = 6 then 'Friday'
when EXTRACT(DOW FROM GameTimestamp) = 7 then 'Saturday'
end as dayName,
EXTRACT(DOW FROM GameTimestamp) as day
from Games
),
data as (
select count (*) as games, EXTRACT(DOW FROM GameTimestamp) as day, EXTRACT(hour FROM GameTimestamp) as hour
from players pl join Participation p on pl.PlayerID = p.PlayerID
join games g on p.GameUUID = g.GameUUID
where p.playerID = %s
and g.ArenaName = %s
group by day, hour
)
select games,dayName,hour :: INTEGER from data d join dateData dd on d.day =dd.day
order by games desc
limit 2
'''
global ordinal
global cursor
global config
data = (targetID, cfg.getConfigString("SiteNameReal"))
cursor.execute(sql, data)
feedbackQueue.q.put("%s%s**Common Game Times:**%s\n" %
(Back.BLACK, Fore.WHITE, Fore.WHITE))
for result in cursor.fetchall():
#print(result)
temptStr = "on %s at %s:00ish" % (result[1], result[2])
feedbackQueue.q.put("%s%s(%s%s%s)%s," %
(Back.BLACK, Fore.WHITE, Fore.YELLOW, temptStr,
Fore.WHITE, Fore.WHITE))
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 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)
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 executeAchievementBuild():
targetArena = cfg.getConfigString("SiteNameReal")
query = sql.SQL('''
with PA as (
select pl.PlayerID, GamerTag,
sum ( case when achievedDate is null then 0 when achievedDate is not null then 1 end) as AchievementsCompleted
from players pl join PlayerAchievement pa on pl.PlayerID = pa.PlayerID
join AllAchievements aa on aa.achID = pa.achID
where aa.ArenaName = %s or aa.ArenaName = 'Global Achievements'
group by pl.PlayerID, GamerTag, AchievementScore
),
top15 as (
select *
from PA
order by AchievementsCompleted desc
limit 15
),
CountOfAcqusitions as (
select count(*) acCount, min(pa.achievedDate) earliest, pa.AchID, aa.AchName from
PlayerAchievement pa join AllAchievements aa on pa.AchID = aa.AchID
where ArenaName = %s and achievedDate is not null
group by pa.AchID, aa.AchName
),
acquiredAAwithRarity as (
select pa.AchID, acCount, earliest, playerID, AchName
from CountOfAcqusitions coa join PlayerAchievement pa on coa.achID = pa.AchID
where achievedDate is not null
),
finalResults as (
select row_number() over (partition by aa.playerID order by acCount, earliest) rarityIndex,
AchName,aa.AchID, acCount, top15.playerID
from acquiredAAwithRarity aa join top15 on aa.PlayerID = top15.PlayerID
--order by AchievementsCompleted desc, count
)
select top15.PlayerID,GamerTag,AchievementsCompleted,AchName,acCount
from top15 join finalResults on top15.PlayerID = finalResults.PlayerID
where rarityIndex = 1
order by Achievementscompleted desc
limit 15
''')
data = (targetArena, targetArena)
conn = connectToSource()
cursor = conn.cursor()
cursor.execute(query, data)
JSON = {
'ScoreTitle':
"Achievement score & total achievements completed",
'Player': [{
# 'Name' : "C'tri",
# 'AchievementScore' : -1,
# 'AchievementsCompleted' : -1,
}],
}
for result in cursor.fetchall():
#print (result)
JSON['Player'].append({
'Name': result[1],
'AchievementsCompleted': result[2],
'RarestAchievement': result[3],
'OthersWith': "(%i)" % (result[4])
})
filepart = "Achievements"
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("Achievement score blob written!", 3)
def executeBuildPlayerBlobs(jobID=None, counter=None):
cachedconfig = cfg.getConfig()
targetIDs = getTop5PlayersRoster(cachedconfig["StartDate"],
cachedconfig["EndDate"],
cachedconfig["SiteNameReal"])
DBG("Building big 5 player blobs", 3)
if jobID is not None and counter is not None:
jobHeartbeat(jobID, counter)
#print ("Player profile blobs written!")
JSONobject = {}
if len(targetIDs) >= 1:
#fetchIndividualWithID(targetIDs[0][0])
JSONobject["GoldenPlayer"] = buildPlayerBlob(cachedconfig["StartDate"],
cachedconfig["EndDate"],
targetIDs[0][0])
if jobID is not None and counter is not None:
jobHeartbeat(jobID, counter)
if len(targetIDs) >= 2:
#fetchIndividualWithID(targetIDs[1][0])
JSONobject["SilverPlayer"] = buildPlayerBlob(cachedconfig["StartDate"],
cachedconfig["EndDate"],
targetIDs[1][0])
if jobID is not None and counter is not None:
jobHeartbeat(jobID, counter)
if len(targetIDs) >= 3:
#fetchIndividualWithID(targetIDs[2][0])
JSONobject["BronzePlayer"] = buildPlayerBlob(cachedconfig["StartDate"],
cachedconfig["EndDate"],
targetIDs[2][0])
if jobID is not None and counter is not None:
jobHeartbeat(jobID, counter)
if len(targetIDs) >= 4:
fetchIndividualWithID(targetIDs[3][0])
JSONobject["OtherPlayer1"] = buildPlayerBlob(cachedconfig["StartDate"],
cachedconfig["EndDate"],
targetIDs[3][0])
if jobID is not None and counter is not None:
jobHeartbeat(jobID, counter)
if jobID is not None and counter is not None:
jobHeartbeat(jobID, counter)
if jobID is not None and counter is not None:
jobHeartbeat(jobID, counter)
if len(targetIDs) >= 5:
fetchIndividualWithID(targetIDs[4][0])
JSONobject["OtherPlayer2"] = buildPlayerBlob(cachedconfig["StartDate"],
cachedconfig["EndDate"],
targetIDs[4][0])
if len(targetIDs) < 5:
DBGstring = "Big 5 returned %i: " % (len(targetIDs))
for target in targetIDs:
DBGstring = DBGstring + "[%i %s]," % (target[2], target[3])
DBG(DBGstring, 2)
JSONobject["SiteNameShort"] = cachedconfig["SiteNameShort"]
filepart = "playerBlob"
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(JSONobject, indent=4))
f.close()
def blobs(targetID):
sql = '''
with Ranks as
(
select GameTimestamp,games.GameUUID, GameName, Players.PlayerID, GamerTag, Score,
ROW_NUMBER() over (partition by GameTimestamp order by score desc) as gamePosition
from Games
join Participation on Games.GameUUID = Participation.GameUUID
join Players on Participation.PlayerID = Players.PlayerID
where Games.ArenaName = %s
),
totalPlayersPerGame as
(
select g.GameUUID, count(*) as playerCount
from Participation p join Games g on p.GameUUID = g.GameUUID
group by g.GameUUID
)
, starData0 as (
select to_char(gametimestamp, 'YYYY-MM') as month
, ranks.GameUUID,GameTimestamp
, GameName
, PlayerID,GamerTag
, gamePosition
, playerCount
from ranks join totalPlayersPerGame tppg
on ranks.GameUUID = tppg.GameUUID
)
,starData1 as (
select month
, PlayerID,count(*) games
, round(avg(cast(gamePosition as numeric)),2) avgRank
, round(avg(cast(playercount as numeric)),2) avgOpponents
, round(avg(cast(playercount as numeric)) * (avg(cast(playercount as numeric)) / avg(cast(gamePosition as numeric))),2) as avgSQ
from starData0
group by month, PlayerID
)
, starData2 as (
select ROW_NUMBER() over (partition by month order by month desc, avgSQ desc) SQrank, * from starData1
)
, StdData0 as( select
p.PlayerID,
GamerTag,
avg(Score) :: INTEGER 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 convert(varchar(7),GameTimestamp,126) in (@curMonth,@lastMonth)
and (g.GameName in ('Team','3 Teams','4 Teams', 'Colour Ranked','Individual') or
g.GameName in ('Continous Ind','Standard 2 Team','Standard 3 Team','Standard 4 Team','Standard Individual','Standard Multi team' )
)
and g.ArenaName = %s
GROUP BY p.PlayerID, pl.GamerTag, to_char(GameTimestamp, 'YYYY-MM')
)
, stdData1 as (
select *, ROW_NUMBER() over (partition by GameMonth order by averageScore desc) as stdRank from StdData0
)
-- players * (players/rank)
select month,SQrank,avgSQ,games,stdRank,averageScore,gamesPlayed
from starData2 sq
join StdData1 sd on sq.PlayerID = sd.PlayerID and sq.month = sd.GameMonth
where sq.PlayerID = %s
order by month desc
limit 3
'''
global cursor
global config
global ordinal
data = (cfg.getConfigString("SiteNameReal"),
cfg.getConfigString("SiteNameReal"), targetID)
cursor.execute(sql, data)
feedbackQueue.q.put("%s%s**Month to Month Stats:**%s\n" %
(Back.BLACK, Fore.WHITE, Fore.WHITE))
for result in cursor.fetchall():
#print(result)
if int(result[4]) > 50 or int(result[4]) < 0:
ordinalString = result[4]
else:
ordinalString = ordinal[int(result[4])]
temptStr = "%s: Stars %s (%s)\t Std %s (%s)" % (result[0], ordinal[int(
result[1])], result[2], ordinalString, result[5])
feedbackQueue.q.put("%s%s(%s%s%s)%s\n" %
(Back.BLACK, Fore.WHITE, Fore.YELLOW, temptStr,
Fore.WHITE, Fore.WHITE))
def queueMonthly():
cfg = ConfigHelper.getConfig()
#667 - find new players, this should happen first.
newPlayersIDs = []
for site in cfg["configs"]:
params = {}
params["siteName"] = site["SiteNameReal"]
newPlayersID = SQLHelper.jobStart(
" new players at [%s]" % params["siteName"],
0,
"FetchPlayerUpdatesAndNewPlayers.findNewPlayers",
params,
delay=-2)
newPlayersIDs.append(newPlayersID)
#661 - load summaries. This should happen after the new player updates, before the games updates.
targetIDs = SQLHelper.getPlayers(0)
summaryID = SQLHelper.jobStart(
"Fetch summaries, all known players",
0,
"FetchPlayerUpdatesAndNewPlayers.updateExistingPlayers",
None,
len(targetIDs),
delay=-2)
#666 - load details. This should happen after the summaries updates.
params = {}
params["scope"] = "full"
params["arenaName"] = ConfigHelper.getConfigString("SiteNameReal")
targetIDs = SQLHelper.getInterestingPlayersRoster(
True,
ConfigHelper.getConfigString("StartDate"),
ConfigHelper.getConfigString("ChurnDuration"),
offset=0)
gamesID = SQLHelper.jobStart("Fetch games, all players",
0,
"FetchPlayerAndGames.executeQueryGames",
params,
len(targetIDs),
delay=-2)
for newPlayerID in newPlayersIDs:
SQLHelper.jobBlock(newPlayerID, summaryID)
SQLHelper.jobBlock(summaryID, gamesID)
#677
targetIDs = SQLHelper.getInterestingPlayersRoster(
False,
ConfigHelper.getConfigString("StartDate"),
ConfigHelper.getConfigString("ChurnDuration"),
offset=0)
if len(targetIDs) > 0:
achievesID = SQLHelper.jobStart(
"Fetch achievements, active players",
0,
"FetchAchievements.executeFetchAchievements",
params,
len(targetIDs),
delay=-2)
SQLHelper.jobBlock(gamesID, achievesID)
else:
DBG.DBG(
"WARNING, no known active players at the time of queuing achievements. May need a re-run.",
2)
achievesID = gamesID #sets it so that the render job will be blocked by games, not achieves.
#6
renderID = SQLHelper.jobStart(
"Render all blobs",
0,
"buildAllForAllArenasSequentially.buildAllForAllArenasSequentially",
None,
len(cfg["configs"]),
delay=-2)
SQLHelper.jobBlock(achievesID, renderID)
def drawArenaMenu():
counter = 5
print_at (5,0,"/***** Pick arena ***************************************************\\",PI=2)
for arena in cfg.getConfigString("configs"):
counter = counter + 1
print_at (counter,0,"[%i] %s" % (counter -5 ,arena["SiteNameShort"]))