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
