def checkDespawnReputation(conn, spawnID, spawnName, entered, galaxy):
# open events for this despawned resource
users = {}
lastEventTime = None
alreadyRemovedFlag = False
editedFlag = False
cursor = conn.cursor()
cursor.execute("SELECT galaxy, userID, eventTime, eventType, planetID, eventDetail FROM tResourceEvents WHERE spawnID={0} ORDER BY eventTime DESC;".format(spawnID))
row = cursor.fetchone()
if row != None:
lastEventTime = row[2]
# Summarize reputation bonus for each user involved
while row != None:
if row[1] not in users:
users[row[1]] = 0
if row[3] == 'a':
if editedFlag == False:
users[row[1]] = users[row[1]] + 3
else:
users[row[1]] = users[row[1]] + 1
if row[3] == 'p':
users[row[1]] = users[row[1]] + 1
if row[3] == 'v':
users[row[1]] = users[row[1]] + 2
if row[3] == 'r':
users[row[1]] = users[row[1]] + 1
if row[3] == 'r' and row[4] == 0:
users[row[1]] = users[row[1]] + 2
if row[3] == 'e':
users[row[1]] = users[row[1]] + 2
editedFlag = True
if row[3] == 'w':
users[row[1]] = users[row[1]] + 2
if row[3] == 'n':
users[row[1]] = users[row[1]] + 2
if row[3] == 'g':
users[row[1]] = users[row[1]] + 2
if row[5] == 'previously unavailable':
alreadyRemovedFlag = True
row = cursor.fetchone()
cursor.close()
if lastEventTime != None and alreadyRemovedFlag == False:
timeSinceEntered = lastEventTime - entered
tmpDays = timeSinceEntered.days
# If resource has not been available for at least a few days its being removed prematurely and not valid for rep awards
if tmpDays > 3:
link = "/resource.py/" + str(galaxy) + "/" + spawnName
message = "You gained reputation for your contribution to tracking resource " + spawnName + "!"
for k, v in users.iteritems():
# Award rep for users contributing at least "4 points" and exclude automated users
if v >= 4 and k != "etas" and k != "default" and k != "c0pp3r":
dbShared.logEvent("INSERT INTO tUserEvents (userID, targetType, targetID, eventType, eventTime) VALUES ('" + k + "', 'r', " + str(spawnID) + ", '+', NOW());", "+", k, galaxy, spawnID)
cursor = conn.cursor()
cursor.execute("INSERT INTO tAlerts (userID, alertType, alertTime, alertMessage, alertLink, alertStatus) VALUES ('" + k + "', 1, NOW(), '" + message + "', '" + link + "', 0);")
cursor.close()
def addResPlanet(spawn, planet, spawnName):
# Add resource to a planet
returnStr = ""
conn = dbShared.ghConn()
cursor = conn.cursor()
cursor.execute("SELECT trp.enteredBy, trp.unavailable, tr.unavailable AS tru, trp.planetID, tr.enteredBy, trp.verified, trp.verifiedBy FROM tResources tr LEFT JOIN (SELECT * FROM tResourcePlanet WHERE tResourcePlanet.spawnID=" + str(spawn) + " AND tResourcePlanet.planetID=" + str(planet) + ") trp ON tr.spawnID = trp.spawnID WHERE tr.spawnID=" + str(spawn) + ";")
row = cursor.fetchone()
if row[3] == None:
# insert spawn planet record
tempSQL = "INSERT INTO tResourcePlanet (spawnID, planetID, entered, enteredBy) VALUES (" + str(spawn) + "," + str(planet) + ",NOW(),'" + currentUser + "');"
cursor.execute(tempSQL)
result = cursor.rowcount
if (result < 1):
returnStr = "Error: Could not add resource to planet."
else:
returnStr = spawnName + " added to " + str(ghNames.getPlanetName(planet))
# add resource planet add event
dbShared.logEvent("INSERT INTO tResourceEvents (spawnID, userID, eventTime, eventType, planetID) VALUES (" + str(spawn) + ",'" + currentUser + "',NOW(),'p'," + str(planet) + ");",'p',currentUser, galaxy, spawn)
if row[2] != None:
# update main resource table when becoming re-available
tempSQL = "UPDATE tResources SET unavailable=NULL WHERE spawnID = " + str(spawn) + ";"
cursor.execute(tempSQL)
returnStr += " and marked re-available"
else:
if (row[1] == None and row[2] == None):
if ((row[0] == None) or (row[0].lower() != currentUser.lower() and row[4].lower() != currentUser.lower())):
if (row[6] == None or row[6].lower() != currentUser.lower()):
tempSQL = "UPDATE tResourcePlanet SET verified=NOW(), verifiedBy='" + currentUser + "' WHERE spawnID=" + str(spawn) + " AND planetID=" + str(planet) + ";"
result = cursor.execute(tempSQL)
if (result < 1):
returnStr = "Error: Resource " + spawnName + " was marked available on " + str(ghNames.getPlanetName(planet)) + " by " + row[0] + " and there was an error entering your verification."
else:
returnStr = "Resource " + spawnName + " has been verified by you. It was marked available on " + str(ghNames.getPlanetName(planet)) + " by " + row[0] + "."
# add event for verification
dbShared.logEvent("INSERT INTO tResourceEvents (spawnID, userID, eventTime, eventType, planetID) VALUES (" + str(spawn) + ",'" + currentUser + "',NOW(),'v'," + str(planet) + ");",'v',currentUser,galaxy,spawn)
# update main resource table when verifying
tempSQL = "UPDATE tResources SET verified=NOW(), verifiedBy='" + currentUser + "' WHERE spawnID = " + str(spawn) + ";"
cursor.execute(tempSQL)
else:
returnStr = "You already verified " + spawnName + " on " + str(row[5]) + "."
else:
returnStr = "You already entered resource " + spawnName
else:
# update resource status available for planet
tempSQL = "UPDATE tResourcePlanet SET unavailable = NULL WHERE spawnID=" + str(spawn) + " AND planetID=" + str(planet) + ";"
cursor.execute(tempSQL)
result = cursor.rowcount
# update main resource table when becoming re-available
tempSQL = "UPDATE tResources SET unavailable=NULL WHERE spawnID = " + str(spawn) + ";"
cursor.execute(tempSQL)
returnStr = spawnName + " marked re-available on " + ghNames.getPlanetName(planet)
cursor.close()
conn.close()
return returnStr
def addResStats(spawn, resType, CR, CD, DR, FL, HR, MA, PE, OQ, SR, UT, ER, forceOp, userID, galaxy):
# Update stats for a spawn
returnStr = ""
needStat = 0
hasStats = 0
resStats = [CR,CD,DR,FL,HR,MA,PE,OQ,SR,UT,ER]
statStr = ""
conn = dbShared.ghConn()
cursor = conn.cursor()
cursor.execute("SELECT CR, CD, DR, FL, HR, MA, PE, OQ, SR, UT, ER, CRmax, CDmax, DRmax, FLmax, HRmax, MAmax, PEmax, OQmax, SRmax, UTmax, ERmax, tResources.resourceType, enteredBy FROM tResources INNER JOIN tResourceType ON tResources.resourceType=tResourceType.resourceType WHERE spawnID=" + str(spawn) + ";")
row = cursor.fetchone()
if row != None:
for i in range(11):
if row[i] != None or row[i] > 0:
statStr = ",".join((statStr, str(row[i])))
if row[i+11] > 0 and row[i] == None:
needStat = 1
if (resStats[i]>0 and resStats[i] != "" and resStats[i] != None):
hasStats = 1
# override normal behavior of only updating
# when there are no stats if forceOp is set to edit
if ( (not needStat) and forceOp != "edit"):
returnStr = "Resource stats already entered."
else:
# update resource stats
# Only allow update if user has positive reputation or was the one who entered resource
stats = dbShared.getUserStats(userID, galaxy).split(",")
admin = dbShared.getUserAdmin(conn, userID, galaxy)
if int(stats[2]) < ghShared.MIN_REP_VALS['EDIT_RESOURCE_STATS_TYPE'] and row[23] != userID and not admin:
returnStr = "Error: You must earn a little reputation on the site before you can edit resources. Try adding or verifying some first. \r\n"
elif hasStats:
tempSQL = "UPDATE tResources SET enteredBy='" + userID + "', CR=" + n2n(CR) + ", CD=" + n2n(CD) + ", DR=" + n2n(DR) + ", FL=" + n2n(FL) + ", HR=" + n2n(HR) + ", MA=" + n2n(MA) + ", PE=" + n2n(PE) + ", OQ=" + n2n(OQ) + ", SR=" + n2n(SR) + ", UT=" + n2n(UT) + ", ER=" + n2n(ER) + " WHERE spawnID=" + str(spawn) + ";"
#sys.stderr.write("sql: " + tempSQL + "\n")
cursor.execute(tempSQL)
result = cursor.rowcount
returnStr = "spawn " + str(spawn) + " stats updated"
# add resource edit event
if needStat:
dbShared.logEvent("INSERT INTO tResourceEvents (galaxy, spawnID, userID, eventTime, eventType) VALUES (" + str(galaxy) + "," + str(spawn) + ",'" + userID + "',NOW(),'a');", 'a', userID, galaxy, spawn)
else:
dbShared.logEvent("INSERT INTO tResourceEvents (galaxy, spawnID, userID, eventTime, eventType, eventDetail) VALUES (" + str(galaxy) + "," + str(spawn) + ",'" + userID + "',NOW(),'e', 'Previous stats: " + statStr + "');", 'e', userID, galaxy, spawn)
if (row[22] != resType and len(resType)>0):
tempSQL = "UPDATE tResources SET resourceType='" + resType + "' WHERE spawnID=" + str(spawn) + ";"
cursor.execute(tempSQL)
returnStr = returnStr + " type updated"
else:
returnStr = "Error: could not find that resource " + str(spawn) + "."
cursor.close()
conn.close()
return returnStr
def removeSpawn(spawnID, planets, userID, galaxy):
markAll = 0
try:
conn = dbShared.ghConn()
cursor = conn.cursor()
except Exception:
result = "Error: could not connect to database"
if (cursor):
if (planets == "all"):
markAll = 1
sqlStr = "UPDATE tResourcePlanet SET unavailable=NOW(), unavailableBy='" + userID + "' WHERE spawnID=" + str(
spawnID) + ";"
else:
# try to look up planet by name if an ID was not provided
if (planets.isdigit() != True):
planets = dbShared.getPlanetID(planets)
sqlStr = "UPDATE tResourcePlanet SET unavailable=NOW(), unavailableBy='" + userID + "' WHERE spawnID=" + str(
spawnID) + " AND planetID=" + str(planets) + ";"
# Only allow removal if user has positive reputation
stats = dbShared.getUserStats(userID, galaxy).split(",")
cursor.execute(
"SELECT enteredBy, unavailable FROM tResources WHERE spawnID=%s;",
[spawnID])
row = cursor.fetchone()
if int(
stats[2]
) < ghShared.MIN_REP_VALS['REMOVE_RESOURCE'] and row[0] != userID:
result = "Error: You must earn a little reputation on the site before you can remove resources. Try adding or verifying some first. \r\n"
elif row[1] != None:
result = "Error: You cannot remove that resource because it is already removed."
else:
cursor.execute(sqlStr)
# add cleanup event
if not planets.isdigit():
planets = 0
dbShared.logEvent(
"INSERT INTO tResourceEvents (galaxy, spawnID, userID, eventTime, eventType, planetID) VALUES ("
+ str(galaxy) + "," + str(spawnID) + ",'" + userID +
"',NOW(),'r'," + str(planets) + ");", 'r', userID, galaxy,
str(spawnID))
result = "Spawn marked unavailable."
cursor.close()
else:
result = "Error: Could not connect to database"
conn.close()
return result
def addResStats(spawn, resType, CR, CD, DR, FL, HR, MA, PE, OQ, SR, UT, ER, forceOp):
# Update stats for a spawn
returnStr = ""
needStat = 0
hasStats = 0
resStats = [CR,CD,DR,FL,HR,MA,PE,OQ,SR,UT,ER]
conn = dbShared.ghConn()
cursor = conn.cursor()
cursor.execute("SELECT CR, CD, DR, FL, HR, MA, PE, OQ, SR, UT, ER, CRmax, CDmax, DRmax, FLmax, HRmax, MAmax, PEmax, OQmax, SRmax, UTmax, ERmax, tResources.resourceType FROM tResources INNER JOIN tResourceType ON tResources.resourceType=tResourceType.resourceType WHERE spawnID=" + str(spawn) + ";")
row = cursor.fetchone()
if row != None:
for i in range(11):
if row[i+11] > 0 and row[i] == None:
needStat = 1
if (resStats[i]>0 and resStats[i] != "" and resStats[i] != None):
hasStats = 1
# override normal behavior of only updating
# when there are no stats if forceOp is set to edit
if ( (not needStat) and forceOp != "edit"):
returnStr = "Resource stats already entered."
else:
# update resource stats
if hasStats:
tempSQL = "UPDATE tResources SET enteredBy='" + currentUser + "', CR=" + n2n(CR) + ", CD=" + n2n(CD) + ", DR=" + n2n(DR) + ", FL=" + n2n(FL) + ", HR=" + n2n(HR) + ", MA=" + n2n(MA) + ", PE=" + n2n(PE) + ", OQ=" + n2n(OQ) + ", SR=" + n2n(SR) + ", UT=" + n2n(UT) + ", ER=" + n2n(ER) + " WHERE spawnID=" + str(spawn) + ";"
#sys.stderr.write("sql: " + tempSQL + "\n")
cursor.execute(tempSQL)
result = cursor.rowcount
returnStr = str(spawnName) + " stats updated"
# add resource edit event
if needStat:
dbShared.logEvent("INSERT INTO tResourceEvents (spawnID, userID, eventTime, eventType) VALUES (" + str(spawn) + ",'" + currentUser + "',NOW(),'a');",'a',currentUser,galaxy,spawn)
else:
dbShared.logEvent("INSERT INTO tResourceEvents (spawnID, userID, eventTime, eventType) VALUES (" + str(spawn) + ",'" + currentUser + "',NOW(),'e');",'e',currentUser,galaxy,spawn)
if (row[22] != resType and len(resType)>0):
tempSQL = "UPDATE tResources SET resourceType='" + resType + "' WHERE spawnID=" + str(spawn) + ";"
cursor.execute(tempSQL)
returnStr = returnStr + " type updated"
else:
returnStr = "Error: could not find that resource " + str(spawnName) + "."
cursor.close()
conn.close()
return returnStr
def addResource(resName, galaxy, resType, CR, CD, DR, FL, HR, MA, PE, OQ, SR,
UT, ER, userID):
# Add new resource
returnStr = ""
conn = dbShared.ghConn()
cursor = conn.cursor()
# clear invalid stat values incase type was switched in the UI
tempSQL = "SELECT CRmin, CRMax, CDmin, CDmax, DRmin, DRmax, FLmin, FLmax, HRmin, HRmax, MAmin, MAmax, PEmin, PEmax, OQmin, OQmax, SRmin, SRmax, UTmin, UTmax, ERmin, ERmax FROM tResourceType WHERE resourceType='" + resType + "';"
cursor.execute(tempSQL)
row = cursor.fetchone()
if row != None:
if row[0] == 0: CR = ''
if row[2] == 0: CD = ''
if row[4] == 0: DR = ''
if row[6] == 0: FL = ''
if row[8] == 0: HR = ''
if row[10] == 0: MA = ''
if row[12] == 0: PE = ''
if row[14] == 0: OQ = ''
if row[16] == 0: SR = ''
if row[18] == 0: UT = ''
if row[20] == 0: ER = ''
tempSQL = "INSERT INTO tResources (spawnName, galaxy, entered, enteredBy, resourceType, CR, CD, DR, FL, HR, MA, PE, OQ, SR, UT, ER) VALUES ('" + resName + "'," + n2n(
galaxy) + ",NOW(),'" + userID + "','" + resType + "'," + n2n(
CR) + "," + n2n(CD) + "," + n2n(DR) + "," + n2n(FL) + "," + n2n(
HR) + "," + n2n(MA) + "," + n2n(PE) + "," + n2n(
OQ) + "," + n2n(SR) + "," + n2n(UT) + "," + n2n(ER) + ");"
cursor.execute(tempSQL)
result = cursor.rowcount
if (result < 1):
returnStr = "Error: resource not added."
else:
returnStr = "1st entry."
# add event for add if stats included
if OQ.isdigit() and OQ != '0':
spawnID = dbShared.getSpawnID(resName, galaxy)
dbShared.logEvent(
"INSERT INTO tResourceEvents (galaxy, spawnID, userID, eventTime, eventType) VALUES ("
+ str(galaxy) + "," + str(spawnID) + ",'" + userID +
"',NOW(),'a');", "a", userID, galaxy, spawnID)
cursor.close()
conn.close()
return returnStr
def addWaypoint(spawnID, planetID, price, concentration, lattitude, longitude, wpName, shareLevel):
# Add new waypoint
returnStr = ""
conn = dbShared.ghConn()
cursor = conn.cursor()
tempSQL = "INSERT INTO tWaypoint (spawnID, planetID, owner, price, concentration, lattitude, longitude, waypointType, waypointName, shareLevel, entered) VALUES (" + str(spawnID) + "," + str(planetID) + ",'" + currentUser + "'," + price + "," + str(concentration) + "," + str(lattitude) + "," + str(longitude) + ",'u','" + wpName + "'," + str(shareLevel) + ",NOW());"
try:
cursor.execute(tempSQL)
returnStr = "Waypoint added."
waypointID = cursor.lastrowid
except:
returnStr = 'Error: Add Failed.'
if str(waypointID).isdigit():
dbShared.logEvent("INSERT INTO tResourceEvents (spawnID, userID, eventTime, eventType, planetID) VALUES (" + str(spawnID) + ",'" + currentUser + "',NOW(),'w'," + str(planetID) + ");","w",currentUser, galaxy, str(spawnID))
cursor.close()
conn.close()
return returnStr
def addWaypoint(spawnID, planetID, price, concentration, lattitude, longitude, wpName, shareLevel):
# Add new waypoint
returnStr = ""
conn = dbShared.ghConn()
cursor = conn.cursor()
tempSQL = "INSERT INTO tWaypoint (spawnID, planetID, owner, price, concentration, lattitude, longitude, waypointType, waypointName, shareLevel, entered) VALUES (" + str(spawnID) + "," + str(planetID) + ",'" + currentUser + "'," + price + "," + str(concentration) + "," + str(lattitude) + "," + str(longitude) + ",'u','" + wpName + "'," + str(shareLevel) + ",NOW());"
try:
cursor.execute(tempSQL)
returnStr = "Waypoint added."
waypointID = cursor.lastrowid
except:
returnStr = 'Error: Add Failed.'
if str(waypointID).isdigit():
dbShared.logEvent("INSERT INTO tResourceEvents (galaxy, spawnID, userID, eventTime, eventType, planetID) VALUES (" + str(galaxy) + "," + str(spawnID) + ",'" + currentUser + "',NOW(),'w'," + str(planetID) + ");","w",currentUser, galaxy, str(spawnID))
cursor.close()
conn.close()
return returnStr
def addResource(resName, galaxy, resType, CR, CD, DR, FL, HR, MA, PE, OQ, SR, UT, ER):
# Add new resource
returnStr = ""
conn = dbShared.ghConn()
cursor = conn.cursor()
# clear invalid stat values incase type was switched in the UI
tempSQL = "SELECT CRmin, CRMax, CDmin, CDmax, DRmin, DRmax, FLmin, FLmax, HRmin, HRmax, MAmin, MAmax, PEmin, PEmax, OQmin, OQmax, SRmin, SRmax, UTmin, UTmax, ERmin, ERmax FROM tResourceType WHERE resourceType='" + resType + "';"
cursor.execute(tempSQL)
row = cursor.fetchone()
if row != None:
if row[0] == 0: CR = ''
if row[2] == 0: CD = ''
if row[4] == 0: DR = ''
if row[6] == 0: FL = ''
if row[8] == 0: HR = ''
if row[10] == 0: MA = ''
if row[12] == 0: PE = ''
if row[14] == 0: OQ = ''
if row[16] == 0: SR = ''
if row[18] == 0: UT = ''
if row[20] == 0: ER = ''
tempSQL = "INSERT INTO tResources (spawnName, galaxy, entered, enteredBy, resourceType, CR, CD, DR, FL, HR, MA, PE, OQ, SR, UT, ER) VALUES ('" + resName + "'," + n2n(galaxy) + ",NOW(),'" + currentUser + "','" + resType + "'," + n2n(CR) + "," + n2n(CD) + "," + n2n(DR) + "," + n2n(FL) + "," + n2n(HR) + "," + n2n(MA) + "," + n2n(PE) + "," + n2n(OQ) + "," + n2n(SR) + "," + n2n(UT) + "," + n2n(ER) + ");"
cursor.execute(tempSQL)
result = cursor.rowcount
if (result < 1):
returnStr = "Error: resource not added."
else:
returnStr = "1st entry."
# add event for add if stats included
if OQ.isdigit():
spawnID = dbShared.getSpawnID(resName,galaxy)
dbShared.logEvent("INSERT INTO tResourceEvents (spawnID, userID, eventTime, eventType) VALUES (" + str(spawnID) + ",'" + currentUser + "',NOW(),'a');","a",currentUser, galaxy,spawnID)
cursor.close()
conn.close()
return returnStr
# First make sure the new name does not already exist
checkCursor2 = conn.cursor()
checkCursor2.execute(
"SELECT spawnID FROM tResources WHERE spawnName=%s AND galaxy=%s",
(newSpawnName, finalGalaxy))
checkRow2 = checkCursor2.fetchone()
if checkRow2 == None:
fixCursor.execute(
"UPDATE tResources SET spawnName=%s WHERE spawnID=%s;",
(newSpawnName, spawnID))
if fixCursor.rowcount > 0:
updated = "resource name"
finalName = newSpawnName
dbShared.logEvent(
"INSERT INTO tResourceEvents (galaxy, spawnID, userID, eventTime, eventType, eventDetail) VALUES ("
+ str(finalGalaxy) + "," + str(spawnID) + ",'" +
currentUser + "',NOW(),'n', '" + checkRow[0] + " to " +
newSpawnName + "');", 'n', currentUser, finalGalaxy,
spawnID)
else:
result = "Error: The name you are updating to already has been entered for this galaxy."
checkCursor2.close()
elif finalGalaxy != newGalaxyId:
# First make sure the name does not already exist in new galaxy
checkCursor2 = conn.cursor()
checkCursor2.execute(
"SELECT spawnID FROM tResources WHERE spawnName=%s AND galaxy=%s",
(finalName, newGalaxyId))
checkRow2 = checkCursor2.fetchone()
if checkRow2 == None:
fixCursor.execute(
"UPDATE tResources SET galaxy=%s WHERE spawnID=%s;",
# Only allow removal if user has positive reputation
stats = dbShared.getUserStats(currentUser, galaxy).split(",")
if int(stats[2]) < ghShared.MIN_REP_VALS[
'REMOVE_RESOURCE'] and row[14] != currentUser:
result = "Error: You must earn a little reputation on the site before you can remove resources. Try adding or verifying some first. \r\n"
elif row[13] != None:
result = "Error: You cannot remove that resource because it is already removed."
else:
cursor.execute(sqlStr)
# add cleanup event
if not planets.isdigit():
planets = 0
dbShared.logEvent(
"INSERT INTO tResourceEvents (galaxy, spawnID, userID, eventTime, eventType, planetID) VALUES ("
+ str(galaxy) + "," + str(spawnID) + ",'" + currentUser +
"',NOW(),'r'," + str(planets) + ");", 'r', currentUser,
galaxy, str(spawnID))
result = spawnName
cursor.close()
else:
result = "Error: That Galaxy is Inactive."
else:
result = "Error: Could not connect to database"
conn.close()
else:
result = "Error: You must be logged in to mark a resource unavailable."
print result
if (result.find("Error:") > -1):
sys.exit(500)
# Mark Unavailable resources that have been available over maximum spawn durations
agingCursor = conn.cursor()
if (agingCursor):
# Non JTL inorganics max spawn length 11 days
agingSQL = "SELECT spawnID, galaxy FROM tResources INNER JOIN tResourceType ON tResources.resourceType = tResourceType.resourceType INNER JOIN tResourceTypeGroup ON tResourceType.resourceType = tResourceTypeGroup.resourceType WHERE unavailableBy IS NULL AND entered < (NOW() - INTERVAL 11 DAY) AND tResourceTypeGroup.resourceGroup = 'inorganic' AND tResourceType.resourceType NOT IN ('aluminum_perovskitic', 'copper_borocarbitic', 'ore_siliclastic_fermionic', 'radioactive_polymetric', 'steel_arveshian', 'steel_bicorbantium', 'fiberplast_gravitonic', 'gas_reactive_organometallic', 'aluminum_galvanicyn', 'copper_cagunese', 'gas_inert_rylon', 'iron_hemalite', 'ore_carbonate_calabite', 'ore_extrusive_maganite', 'ore_intrusive_galatite');"
agingCursor.execute(agingSQL)
sys.stdout.write("Updating " + str(agingCursor.rowcount) +
" rows Non-JTL inorganics for auto mark unavailable.\n")
cleanupCursor = conn.cursor()
row = agingCursor.fetchone()
while row != None:
cleanupSQL = 'UPDATE tResources SET unavailableBy="default", unavailable=NOW() WHERE spawnID={0}'.format(
row[0])
cleanupCursor.execute(cleanupSQL)
dbShared.logEvent(
"INSERT INTO tResourceEvents (galaxy, spawnID, userID, eventTime, eventType, planetID) VALUES ("
+ str(row[1]) + "," + str(row[0]) + ",'default',NOW(),'r',0);",
'r', 'default', row[1], str(row[0]))
row = agingCursor.fetchone()
cleanupCursor.close()
# Everything else max spawn length 22 days
agingSQL = "SELECT spawnID, galaxy FROM tResources WHERE unavailableBy IS NULL AND entered < (NOW() - INTERVAL 22 DAY);"
agingCursor.execute(agingSQL)
sys.stdout.write("Updating " + str(agingCursor.rowcount) +
" JTL/organics rows for auto mark unavailable.\n")
cleanupCursor = conn.cursor()
row = agingCursor.fetchone()
while row != None:
cleanupSQL = 'UPDATE tResources SET unavailableBy="default", unavailable=NOW() WHERE spawnID={0}'.format(
row[0])
def addResPlanet(spawn, planet, spawnName, userID, galaxy):
# Add resource to a planet
returnStr = ""
detailCol = ""
detailVal = ""
conn = dbShared.ghConn()
cursor = conn.cursor()
cursor.execute("SELECT trp.enteredBy, trp.unavailable, tr.unavailable AS tru, trp.planetID, tr.enteredBy, trp.verified, trp.verifiedBy, tr.verified, tr.verifiedBy FROM tResources tr LEFT JOIN (SELECT * FROM tResourcePlanet WHERE tResourcePlanet.spawnID=" + str(spawn) + " AND tResourcePlanet.planetID=" + str(planet) + ") trp ON tr.spawnID = trp.spawnID WHERE tr.spawnID=" + str(spawn) + ";")
row = cursor.fetchone()
if row[3] == None:
# insert spawn planet record
tempSQL = "INSERT INTO tResourcePlanet (spawnID, planetID, entered, enteredBy) VALUES (" + str(spawn) + "," + str(planet) + ",NOW(),'" + userID + "');"
cursor.execute(tempSQL)
result = cursor.rowcount
if (result < 1):
returnStr = "Error: Could not add resource to planet."
else:
returnStr = spawnName + " added to " + str(ghNames.getPlanetName(planet))
if row[2] != None:
# update main resource table when becoming re-available
tempSQL = "UPDATE tResources SET unavailable=NULL WHERE spawnID = " + str(spawn) + ";"
cursor.execute(tempSQL)
returnStr += " and marked re-available"
detailCol = ", eventDetail"
detailVal = ", 'previously unavailable'"
# add resource planet add event
dbShared.logEvent("INSERT INTO tResourceEvents (galaxy, spawnID, userID, eventTime, eventType, planetID" + detailCol + ") VALUES (" + str(galaxy) + "," + str(spawn) + ",'" + userID + "',NOW(),'p'," + str(planet) + detailVal + ");", 'p', userID, galaxy, spawn)
else:
if (row[1] == None and row[2] == None):
if ((row[0] == None) or (row[0].lower() != userID.lower() and row[4].lower() != userID.lower())):
if (row[6] == None or row[6].lower() != userID.lower()):
tempSQL = "UPDATE tResourcePlanet SET verified=NOW(), verifiedBy='" + userID + "' WHERE spawnID=" + str(spawn) + " AND planetID=" + str(planet) + ";"
result = cursor.execute(tempSQL)
if (result < 1):
returnStr = "Error: Resource " + spawnName + " was marked available on " + str(ghNames.getPlanetName(planet)) + " by " + row[0] + " and there was an error entering your verification."
else:
returnStr = "Resource " + spawnName + " has been verified by you. It was marked available on " + str(ghNames.getPlanetName(planet)) + " by " + row[0] + "."
# add event for verification
if row[7] != None:
detailCol = ", eventDetail"
detailVal = ", 'previously verified by " + row[8] + " on " + str(row[7]) + "'"
dbShared.logEvent("INSERT INTO tResourceEvents (galaxy, spawnID, userID, eventTime, eventType, planetID" + detailCol + ") VALUES (" + str(galaxy) + "," + str(spawn) + ",'" + userID + "',NOW(),'v'," + str(planet) + detailVal + ");",'v', userID, galaxy, spawn)
# update main resource table when verifying
tempSQL = "UPDATE tResources SET verified=NOW(), verifiedBy='" + userID + "' WHERE spawnID = " + str(spawn) + ";"
cursor.execute(tempSQL)
else:
returnStr = "You already verified " + spawnName + " on " + str(row[5]) + "."
else:
returnStr = "You already entered resource " + spawnName
else:
# update resource status available for planet
tempSQL = "UPDATE tResourcePlanet SET unavailable = NULL WHERE spawnID=" + str(spawn) + " AND planetID=" + str(planet) + ";"
cursor.execute(tempSQL)
result = cursor.rowcount
# update main resource table when becoming re-available
tempSQL = "UPDATE tResources SET unavailable=NULL WHERE spawnID = " + str(spawn) + ";"
cursor.execute(tempSQL)
returnStr = spawnName + " marked re-available on " + ghNames.getPlanetName(planet)
detailCol = ", eventDetail"
detailVal = ", 'previously unavailable'"
# add resource planet add event
dbShared.logEvent("INSERT INTO tResourceEvents (galaxy, spawnID, userID, eventTime, eventType, planetID" + detailCol + ") VALUES (" + str(galaxy) + "," + str(spawn) + ",'" + userID + "',NOW(),'p'," + str(planet) + detailVal + ");", 'p', userID, galaxy, spawn)
cursor.close()
conn.close()
return returnStr
cursor.execute(sqlStr)
# check if any planets still available
cursor.execute("SELECT enteredBy FROM tResourcePlanet WHERE spawnID=" + str(spawnID) + " AND unavailable IS NULL;")
row = cursor.fetchone()
if (row == None):
markAll = 1
# update main table when all planets unavailable
if (markAll == 1):
sqlStr = "UPDATE tResources SET unavailable=NOW(), unavailableBy='" + currentUser + "' WHERE spawnID=" + str(spawnID) + ";"
cursor.execute(sqlStr)
# add cleanup event
if not planets.isdigit():
planets = 0
dbShared.logEvent("INSERT INTO tResourceEvents (spawnID, userID, eventTime, eventType, planetID) VALUES (" + str(spawnID) + ",'" + currentUser + "',NOW(),'r'," + str(planets) + ");",'r',currentUser,galaxy,str(spawnID))
result = spawnName
cursor.close()
else:
result = "Error: That Galaxy is Inactive."
else:
result = "Error: Could not connect to database"
conn.close()
else:
result = "Error: You must be logged in to mark a resource unavailable."
print result
if (result.find("Error:") > -1):
sys.exit(500)
else:
sys.exit(200)
