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)