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)