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