def determineFirstTimeSetup(serverId, allianceId): sql = 'SELECT AllianceID FROM Alliance WHERE ServerID={} AND SubAlliance=0'.format( serverId) alliancesExist = queryDatabase(sql) sql = 'SELECT AllianceID FROM Alliance WHERE ServerID={} AND AllianceID="{}"'.format( serverId, allianceId) thisAllianceExists = queryDatabase(sql) # Not first time set up if server is registered and this alliance id is not if len(alliancesExist) and not len(thisAllianceExists): return alliancesExist[0][0] return None
def getResourceResults(resource, tier, region): sql = 'select * FROM Resources' whereClause = ' WHERE' filter = '' numFilters = 0 if resource: numFilters += 1 filter += ' Resource="{}"'.format(resource.lower()) if tier: numFilters += 1 if numFilters > 1: filter += ' AND Tier="{}"'.format(int(tier)) else: filter += ' Tier="{}"'.format(int(tier)) if region: numFilters += 1 if numFilters > 1: filter += ' AND Region="{}"'.format(region.lower()) else: filter += ' region="{}"'.format(region.lower()) if numFilters > 0: sql = sql + whereClause return queryDatabase( sql + filter + ' ORDER BY Resource == "dilithium", Resource, Tier DESC, Region')
def getAllianceIds(serverId): sql = ''' SELECT A.AllianceID FROM Alliance AS A WHERE A.ServerID={} '''.format(serverId) res = queryDatabase(sql) return reduceResults(res)
def hasIntel(serverId): sql = ''' SELECT * FROM AllianceIntelligence WHERE ServerID={} '''.format(serverId) res = queryDatabase(sql) return len(res)
def getAllianceName(serverId): sql = ''' SELECT S.AllianceName FROM Server AS S WHERE S.ServerID={} '''.format(serverId) res = queryDatabase(sql) return res[0][0]
def hasGeneralInfo(serverId): sql = ''' SELECT * FROM GeneralAllianceInfo WHERE ServerID={} '''.format(serverId) res = queryDatabase(sql) return len(res)
def getAllyRoles(serverId): sql = ''' SELECT R.Role FROM AllianceRolePermissions AS R WHERE R.ServerID={} AND R.AllyRole=1 '''.format(serverId) return reduceResults(queryDatabase(sql))
def getAmbassadorCategory(serverId): sql = ''' SELECT S.ChannelCategory FROM Server AS S WHERE S.ServerID={} '''.format(serverId) res = queryDatabase(sql) return res[0][0]
def getSettings(serverId, roles, categories): sql = ''' SELECT S.AllianceName, S.CreateChannel, S.ChannelCategory, R.Role, R.MemberRole, R.AmbassadorRole, R.AllyRole, R.AdminRole, R.AccessAmbassadorChannels, S.AllowAllyIntelAccess FROM Server AS S JOIN AllianceRolePermissions As R ON S.ServerID=R.ServerID WHERE S.ServerID={} '''.format(serverId) resp = queryDatabase(sql) memberRoles = [] ambassadorRoles = [] allyRoles = [] registerRoles = [] accessRoles = [] for i in range(len(resp)): memberRoles.append({ "role": getRole(roles, resp[i][3]), "selected": False }) ambassadorRoles.append({ "role": getRole(roles, resp[i][3]), "selected": False }) allyRoles.append({ "role": getRole(roles, resp[i][3]), "selected": False }) registerRoles.append({ "role": getRole(roles, resp[i][3]), "selected": False }) accessRoles.append({ "role": getRole(roles, resp[i][3]), "selected": False }) if resp[i][4]: memberRoles[i]["selected"] = True if resp[i][5]: ambassadorRoles[i]["selected"] = True if resp[i][6]: allyRoles[i]["selected"] = True if resp[i][7]: registerRoles[i]["selected"] = True if resp[i][8]: accessRoles[i]["selected"] = True return { "alliance": resp[0][0], "manualRegister": resp[0][1], "createChannel": resp[0][2], "channelCategory": getCategory(categories, resp[0][3]), "memberRoles": memberRoles, "ambassadorRoles": ambassadorRoles, "allyRoles": allyRoles, "canRegisterUserRoles": registerRoles, "canAccessPrivateChannelRoles": accessRoles, "allowAllyIntelAccess": resp[0][9] }
def getMasterAllianceId(serverId): sql = ''' Select MasterAlliance FROM Alliance WHERE ServerID={} AND SubAlliance=0 '''.format(serverId) res = queryDatabase(sql) return res[0][0].upper()
def manualRegisterAllowed(serverId): sql = ''' SELECT S.ManualRegister FROM Server AS S WHERE S.ServerID={} '''.format(serverId) res = queryDatabase(sql) return res[0]
def getWarPointsChannel(serverId): sql = ''' SELECT WarPointsChannel FROM Server WHERE ServerID={} '''.format(serverId) res = queryDatabase(sql) if len(res): return res[0][0] return '' sql += ' ORDER BY KillCount desc' resp = queryDatabase(sql) if not len(resp): return ['**No Player Kill Counts'] return resp
def serverRegistered(serverId): sql = ''' SELECT A.ServerID FROM Alliance AS A WHERE A.ServerID={} '''.format(serverId) res = queryDatabase(sql) return len(res)
def getHomeInfo(serverId): sql = ''' Select HomeInfo FROM GeneralAllianceInfo WHERE ServerID={} '''.format(serverId) resp = queryDatabase(sql) if len(resp): return resp[0][0] return ''
def getTotalKillCounts(serverId): sql = ''' SELECT SUM(KillCount) FROM AllianceMember WHERE ServerID={} '''.format(serverId) resp = queryDatabase(sql) if len(resp): return resp[0][0] return 0
def getPlayerIntel(serverId, allianceId, player): sql = ''' SELECT * FROM PlayerIntelligence WHERE ServerID={} AND PlayerName="{}" '''.format(serverId, player) res = queryDatabase(sql) if len(res): return res[0] return []
def createChannelAllowed(serverId): sql = ''' SELECT S.CreateChannel FROM Server AS S WHERE S.ServerID={} '''.format(serverId) res = queryDatabase(sql) if len(res): return res[0][0] return False
def getIntelPlayers(serverId): sql = ''' SELECT * FROM PlayerIntelligence WHERE ServerID={} ORDER BY PlayerAlliance, LastUpdate DESC '''.format(serverId) res = queryDatabase(sql) if len(res): return res return []
def getKillCount(playerId): sql = ''' SELECT KillCount FROM AllianceMember WHERE PlayerID="{}" '''.format(playerId) res = queryDatabase(sql) if len(res): return res[0][0] return 'No Information Yet!'
def isInAlliance(serverId, newAllianceId): sql = ''' SELECT A.AllianceID FROM Alliance AS A WHERE A.ServerID={} '''.format(serverId) res = queryDatabase(sql) for id in res: if id[0].lower() == newAllianceId.lower(): return True return False
def canAccessPrivateChannel(serverId, role): sql = ''' SELECT R.AccessAmbassadorChannels FROM AllianceRolePermissions AS R WHERE R.ServerID={} AND R.Role='{}' '''.format(serverId, role.name.lower()) res = queryDatabase(sql) if len(res): return res[0][0] return False
def hasAdminPermission(serverId, roles): for role in roles: sql = ''' SELECT R.AdminRole FROM AllianceRolePermissions AS R WHERE R.ServerID={} AND R.Role='{}' '''.format(serverId, role.name.lower()) res = queryDatabase(sql) if len(res) and res[0][0]: return True return False
def getResourceReliability(resource, tier, system): addition = '' sql = ''' SELECT ReliabilityScore FROM Resources WHERE Resource="{}" AND System="{}" '''.format(resource.lower(), system.lower()) if tier: addition = ' AND Tier={}'.format(tier) res = queryDatabase(sql + addition) return res[0][0]
def isAllyWithIntelPermission(serverId, userRoles): sql = ''' SELECT Role From AllianceRolePermissions WHERE ServerID={} AND AllyRole=1 AND AmbassadorRole=0; '''.format(serverId) res = queryDatabase(sql) if len(res): allyRole = res[0][0] for role in userRoles: if role.name.lower() == allyRole.lower(): return True return False
def getROEViolations(serverId, query): cleanROEviolations(serverId, query) sql = ''' SELECT AllianceID, Violations, PlayerName FROM ROE WHERE ServerID={} '''.format(serverId) if query: sql += 'AND AllianceID="{}"'.format(query) resp = queryDatabase(sql) if not len(resp): return ['**No ROE Violations'] return resp
def getWar(serverId): sql = ''' SELECT AllianceID FROM AllianceIntelligence WHERE ServerID={} AND War=1 '''.format(serverId) resp = queryDatabase(sql) if len(resp): reduced = reduceResults(resp) strResult = '' for r in reduced: strResult += '{}, '.format(r) return strResult return ''
def cleanROEviolations(serverId, query): now = datetime.datetime.now() sql = ''' SELECT LastUpdated, AllianceID, PlayerName FROM ROE WHERE ServerID={} '''.format(serverId) results = queryDatabase(sql) for r in results: dateObj = datetime.datetime.strptime( '{} {}'.format(r[0], '12:00:00.0'), '%Y-%m-%d %H:%M:%S.%f') diff = now - dateObj if diff.days > 14: removeROE(serverId, r[1], r[2])
def getMemberKillCounts(serverId, alliance): sql = ''' SELECT AllianceID, PlayerName, KillCount FROM AllianceMember WHERE ServerID={} '''.format(serverId) if alliance: sql += ' AND AllianceID="{}"'.format(alliance) sql += ' ORDER BY KillCount desc' resp = queryDatabase(sql) if not len(resp): return ['**No Player Kill Counts'] return resp
def getSetupSummary(title, serverId, alliance, allianceId, manual, private, allyAcces, category, memberRoles, ambassadorRoles, allyRoles, registerRoles, pvtRoles): res = queryDatabase( 'SELECT AllianceID FROM Alliance WHERE ServerID={}'.format(serverId)) summary = '**{}**\n\nBelow is your server settings.\n\n'.format(title) summary += '**ALLIANCE:** {}\n'.format(alliance) if not len(res): summary += '**ALLIANCE-ID:** {}\n\n'.format(allianceId) else: summary += '**ALLIANCE-IDS:**\n' for id in res: summary += '× {}\n'.format(id[0]) summary += '\n' if manual: summary += '**[YES]** [NO] Users can self register\n' else: summary += '[YES] **[NO]** Users can self register\n' if allyAcces: summary += '**[YES]** [NO] Allies can access intel\n' else: summary += '[YES] **[NO]** Allies can access intel\n' if private: summary += '**[YES]** [NO] Create private channel\n\n' else: summary += '[YES] **[NO]** Create private channel\n\n' if category != None: summary += '**Private Channel Category:** {}\n'.format(category.name) summary += '\n__**REGISTER COMMAND**__\n***New roles to give to new members:***\n' for r in memberRoles: if r['selected']: summary += '× {}\n'.format(r['role'].name) summary += '\n***Roles to give new ambassadors:***\n' for r in ambassadorRoles: if r['selected']: summary += '× {}\n'.format(r['role'].name) summary += '\n***Roles to give new ally ambassadors:***\n' for r in allyRoles: if r['selected']: summary += '× {}\n'.format(r['role'].name) summary += '\n***Admin Roles:***\n' for r in registerRoles: if r['selected']: summary += '× {}\n'.format(r['role'].name) summary += '\n' summary += '**PRIVATE CHANNEL PERMS**\n***Roles that have access to new privte channels:***\n' for r in pvtRoles: if r['selected']: summary += '× {}\n'.format(r['role'].name) return summary