def view_updateavatar(environ):
"""
Set the avatar to an img.eve.is address.
"""
user = environ["emapps.user"]
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("SELECT corp FROM auth_user "
"WHERE username = %s AND authenticated = 1",
user.username)
if c.rowcount == 0:
return kgi.template_response('403.html',
reason='Not authenticated.')
api = eveapi.EVEAPIConnection()
charids = api.eve.CharacterID(names=user.username)
charid = None
for char in charids.characters:
charid = char.characterID
if charid is None:
return kgi.redirect_response('http://www.electusmatari.com/auth/')
db = kgi.connect('dbforums')
c = db.cursor()
url = 'http://img.eve.is/serv.asp?s=64&c=%s' % charid
c.execute("UPDATE mybb_users SET avatar = %s "
"WHERE LOWER(username) = LOWER(%s)",
(url, user.username))
return kgi.redirect_response('http://www.electusmatari.com/auth/')
def update_userdetails(username, userid, apikey, message, authenticated,
disabled, corp=None, alliance=None):
"""
Update the user API key details.
message is displayed to the user.
authenticated is a boolean to check whether the API key is valid.
disabled is a boolean saying whether to try authentication again.
"""
db = kgi.connect('dbforcer')
c = db.cursor()
sql = ("UPDATE auth_user "
"SET userid = %s, "
" apikey = %s, "
" message = %s, "
" authenticated = %s, "
" disabled = %s, ")
args = [userid, apikey, message, authenticated, disabled]
if corp is not None:
sql += " corp = %s, "
args.append(corp)
if alliance is not None:
sql += " alliance = %s, "
args.append(alliance)
else:
sql += " alliance = '', "
sql += (" last_attempt = NOW() "
"WHERE username = %s")
args.append(username)
c.execute(sql, args)
if c.rowcount == 0:
c.execute("INSERT INTO auth_user (userid, apikey, message, "
" authenticated, disabled, "
" corp, alliance, "
" username, last_attempt) "
"VALUES (%s, %s, %s, "
" %s, %s, "
" %s, %s, "
" %s, NOW())",
(userid, apikey, message,
authenticated, disabled,
corp, alliance,
username))
if corp is not None:
db2 = kgi.connect('dbforums')
c2 = db2.cursor()
if username in TITLES:
usertitle = TITLES[username]
elif alliance is None:
usertitle = "%s" % corp
else:
usertitle = "%s<br />%s" % (corp, alliance)
c2.execute("UPDATE mybb_users SET usertitle = %s "
"WHERE username = %s",
(usertitle, username))
def sql_update_order(orderid, customer=None, source=None, state=None,
producer=False, price=False, ordertext=None):
db = kgi.connect('dbforcer')
c = db.cursor()
sets = []
args = []
if customer is not None:
sets.append("customer = %s")
args.append(customer)
if source is not None:
sets.append("source = %s")
args.append(source)
if state is not None:
sets.append("state = %s")
args.append(state)
if producer is not False:
sets.append("producer = %s")
args.append(producer)
if price is not False:
sets.append("price = %s")
args.append(price)
if ordertext is not None:
sets.append("ordertext = %s")
args.append(ordertext)
args.append(orderid)
c.execute("UPDATE market_order_old SET "
+ ", ".join(sets)
+ " WHERE id = %s",
args)
def sql_get_orderlist(producer, sources=[], states=[]):
db = kgi.connect('dbforcer')
c = db.cursor()
where = ["state != 'done'"]
or_where = []
args = []
if producer is not None:
or_where.append("producer = %s")
args.append(producer)
if len(sources) > 0:
or_where.append("(producer is NULL OR producer = '') AND source IN (%s)"
% ", ".join(["%s"] * len(sources)))
args.extend(sources)
if len(or_where) > 0:
where.append("(%s)" % (" OR ".join([("(%s)" % x) for x in or_where]),))
if len(states) > 0:
where.append("status IN (%s)"
% ", ".join(["%s"] * len(states)))
args.extend(states)
c.execute("SELECT * FROM market_order_old "
"WHERE " + " AND ".join(where) + " "
"ORDER BY source ASC, created DESC",
args)
orders = kgi.fetchbunches(c)
for order in orders:
c = sql_get_comment(order.id)
if c is not None:
order['lastcomment'] = c
else:
order['lastcomment'] = None
return orders
def add_comment(imagename, author, comment):
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("INSERT INTO gallery_comments "
" (imagename, author, comment) "
"VALUES (%s, %s, %s)",
(imagename, author, comment))
def view_info(environ):
"""
Show authentication status.
"""
user = environ["emapps.user"]
if not user.is_authenticated():
return kgi.html_response(
unauthorized(user, 'You need to log in to the forums.')
)
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("SELECT last_attempt, message, authenticated, disabled "
"FROM auth_user WHERE username = %s LIMIT 1",
(user.username,))
if c.rowcount == 1:
(last_attempt, message, authenticated, disabled) = c.fetchone()
else:
(last_attempt, message, authenticated, disabled) = (None, None,
None, None)
return kgi.template_response('auth/info.html',
user=user,
last_attempt=last_attempt,
message=message,
authenticated=authenticated,
disabled=disabled)
def sql_add_comment(contractid, author, text):
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("INSERT INTO market_contract_comments (contract_id, "
" author, comment) "
"VALUES (%s, %s, %s)",
(contractid, author, text))
def sql_save_order(username, orders, type):
db = kgi.connect('dbforcer')
c = db.cursor()
ordertexts = {}
comments = {}
totals = {}
args = orders.keys()
args.append(type)
contractids = []
c.execute("SELECT * FROM market_order "
"WHERE id IN (%s) "
" AND type = %%s "
"ORDER BY item ASC" % ", ".join(["%s"] * len(orders)),
args)
bunches = kgi.fetchbunches(c)
for b in bunches:
amount = orders[b.id]
if b.amount <= 0 or b.amount < amount:
continue
b.amount -= amount
if b.owner not in ordertexts:
ordertexts[b.owner] = ""
if b.owner not in comments:
comments[b.owner] = ""
if b.owner not in totals:
totals[b.owner] = 0
ordertexts[b.owner] += ("%ix %s\n" % (amount, b.item))
comments[b.owner] += ("%15s %s x %s (%s p.u., %s)\n"
% (humane(amount * b.price),
b.item,
humane(amount),
humane(b.price),
b.comment))
totals[b.owner] += amount * b.price
for (owner, comment) in comments.items():
comments[owner] += "---------------\n"
comments[owner] += "%15s sum" % humane(totals[owner])
for owner in ordertexts.keys():
if type == 'sell':
creator = username
handler = owner
else:
creator = owner
handler = username
contractids.append(sql_create_contract(creator=creator,
contracttext=ordertexts[owner],
handler=handler,
state="in progress"))
sql_add_comment(contractids[-1], creator, comments[owner])
for b in bunches:
if b.amount == 0:
c.execute("DELETE FROM market_order WHERE id = %s",
(b.id,))
else:
c.execute("UPDATE market_order SET amount = %s WHERE id = %s",
(b.amount, b.id))
if len(contractids) == 1:
return contractids[0]
else:
return None
def sql_create_order(username, type, item, amount, price, comment, expires):
db = kgi.connect('dbforcer')
c = db.cursor()
expires = datetime.datetime.utcnow() + datetime.timedelta(days=expires)
c.execute("INSERT INTO market_order (expires, type, amount, item, "
" price, owner, comment) "
"VALUES (%s, %s, %s, %s, %s, %s, %s)",
(expires, type, amount, item, price, username, comment))
def sql_get_comments(contractid):
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("SELECT * FROM market_contract_comments "
"WHERE contract_id = %s "
"ORDER BY created ASC",
(contractid,))
return kgi.fetchbunches(c)
def get_comments(imagename):
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("SELECT date, author, comment FROM gallery_comments "
"WHERE imagename = %s "
"ORDER BY date ASC",
(imagename))
return c.fetchall()
def sql_create_contract(creator, contracttext, handler=None,
state="new", queue=None):
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("INSERT INTO market_contract (contracttext, creator, "
" handler, queue, state) "
"VALUES (%s, %s, %s, %s, %s)",
(contracttext, creator, handler, queue, state))
return c.lastrowid
def view_stats(environ):
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("SELECT COUNT(*) AS c, submitter "
"FROM locator_trace "
"GROUP BY submitter "
"ORDER BY c DESC")
return kgi.template_response('intel/stats.html',
user=environ['emapps.user'],
stats=c.fetchall())
def get_apikey(username):
"""
Get the userid and apikey of a given user.
"""
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("SELECT userid, apikey FROM auth_user "
"WHERE username = %s",
(username,))
return c.fetchone()
def sql_get_contract(contractid):
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("SELECT * FROM market_contract "
"WHERE id = %s "
"LIMIT 1",
(contractid,))
if c.rowcount == 1:
return kgi.fetchbunches(c)[0]
else:
return None
def has_permission(self, name):
if name == 'em':
return self.is_emuser
if self.permissions is None:
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("""
SELECT permission FROM userpermissions
WHERE username = %s
""", (self.username,))
self.permissions = [x for (x,) in c.fetchall()]
return name in self.permissions
def sql_get_lastcomment(contractid):
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("SELECT * FROM market_contract_comments "
"WHERE contract_id = %s "
"ORDER BY created DESC "
"LIMIT 1",
(contractid,))
if c.rowcount == 1:
return kgi.fetchbunches(c)[0]
else:
return None
def view_info(environ):
user = environ['emapps.user']
if environ['REQUEST_METHOD'] == 'POST':
# FIXME! Check FC permission here.
form = cgi.FieldStorage()
optitle = form.getfirst("title")
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("INSERT INTO opwarn_list (title) VALUES (%s)",
(optitle,))
return kgi.redirect_response('http://www.electusmatari.com/ops/')
return kgi.template_response('ops/info.html',
user=user)
def view_corp(environ):
"""
Show the members of your corp.
"""
user = environ["emapps.user"]
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("SELECT corp FROM auth_user "
"WHERE username = %s AND authenticated = 1",
user.username)
if c.rowcount == 0:
return kgi.template_response('auth/corp.html',
user=environ['emapps.user'],
error="You are not authenticated.")
corpname = c.fetchone()[0]
c.execute("SELECT username FROM auth_user "
"WHERE corp = %s "
" AND authenticated = 1 "
"ORDER BY username ASC",
corpname)
apiusers = [x for (x,) in c.fetchall()]
db2 = kgi.connect('dbforums')
c2 = db2.cursor()
c2.execute("SELECT username, usertitle FROM mybb_users "
"WHERE CONCAT(',', usergroup, ',', additionalgroups, ',') "
" LIKE '%,56,%' "
"ORDER BY username ASC")
c2.execute("SELECT username, lastactive FROM mybb_users")
active = dict((username, datetime.datetime.utcfromtimestamp(lastactive))
for (username, lastactive) in c2.fetchall())
users = [(username, active[username], False)
for username in apiusers]
users.sort(lambda a, b: cmp(a[0].lower(), b[0].lower()))
return kgi.template_response('auth/corp.html',
user=user,
corp=corpname,
error=None,
users=users,
now=datetime.datetime.utcnow())
def get_sales(sortby=None):
db = kgi.connect('dbforcer')
c = db.cursor()
query = "SELECT * FROM market_sale "
if sortby == 'age':
query += "ORDER BY created DESC"
else:
query += "ORDER BY item ASC"
c.execute(query)
now = datetime.datetime.now()
bunches = kgi.fetchbunches(c)
for b in bunches:
b.age = (now - b.created).days
return bunches
def sql_create_order(username, ordertext, source, comment=None, price=None,
producer=None, state='unclaimed'):
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("INSERT INTO market_order_old (ordertext, price, "
"customer, producer, source, state) "
"VALUES (%s, %s, %s, %s, %s, %s)",
(ordertext, price, username, producer, source, state))
if comment is not None:
# This is non-portable. PostgreSQL doesn't do c.lastrowid.
c.execute("INSERT INTO market_comments (order_id, "
"author, comment) "
"VALUES (%s, %s, %s)",
(c.lastrowid, username, comment))
def remove_users_without_api():
"""
Throw all users out of "our" groups that don't have configured API
keys.
"""
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("SELECT username FROM auth_user")
users = [x for (x,) in c.fetchall()]
db = kgi.connect('dbforums')
c = db.cursor()
c.execute("SELECT gid FROM mybb_usergroups "
"WHERE title IN (%s)" % ", ".join(["%s"]*len(REQUIRES +
GRDGROUPS)),
[groupname for (groupname, _, _) in REQUIRES] +
GRDGROUPS)
gids = ["%%,%s,%%" % x for (x,) in c.fetchall()]
c.execute("SELECT username FROM mybb_users "
"WHERE "
+
" OR ".join(["CONCAT(',', usergroup, ',', additionalgroups, ',')"
" LIKE %s"] * len(REQUIRES + GRDGROUPS)),
gids)
for (username,) in c.fetchall():
if username not in users:
user = MyBBUser(username)
groups = ([groupname for (groupname, _, require_key)
in REQUIRES if require_key] +
GRDGROUPS)
for groupname in groups:
if user.has_group(groupname):
user.remove_group(groupname)
log.info("User %s loses group %s: No API key stored."
% (username, groupname))
user.save()
def sql_get_own_orderlist(customer):
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("SELECT * FROM market_order_old "
"WHERE state != 'done' "
" AND customer = %s "
"ORDER BY created DESC",
(customer,))
orders = kgi.fetchbunches(c)
for order in orders:
c = sql_get_comment(order.id)
if c is not None:
order['lastcomment'] = c
else:
order['lastcomment'] = None
return orders
def mybb_auth(environ):
cookie = Cookie.SimpleCookie(environ.get("HTTP_COOKIE"))
try:
(uid, loginkey) = cookie["mybbuser"].value.split("_", 1)
except KeyError:
return ("Anonymous", False)
except ValueError:
return ("Anonymous", False)
db = kgi.connect("dbforums")
c = db.cursor()
c.execute(
"""SELECT username, usergroup, additionalgroups
FROM mybb_users
WHERE uid=%s AND loginkey=%s
LIMIT 1
""",
(uid, loginkey),
)
if c.rowcount < 1:
return ("Anonymous", False)
(username, usergroup, additionalgroups) = c.fetchone()
groups = [int(usergroup)]
if additionalgroups != "":
groups.extend([int(x) for x in additionalgroups.split(",")])
groupexpr = ",".join(["%s"] * len(groups))
auth_flags = {}
for auth_group in forum_names:
c.execute(
"""
SELECT COUNT(*)
FROM mybb_forums AS forums
INNER JOIN mybb_forumpermissions AS perm
ON forums.fid = perm.fid
WHERE forums.name = %%s
AND perm.gid IN (%s)
AND perm.canview = 1;
"""
% groupexpr,
tuple([forum_names[auth_group]] + groups),
)
count = c.fetchone()[0]
auth_flags[auth_group] = count > 0
return (username, auth_flags)
def sql_save_order(username, orders):
db = kgi.connect('dbforcer')
c = db.cursor()
ordertexts = {}
comments = {}
totals = {}
c.execute("SELECT * FROM market_sale "
"WHERE id IN (%s) "
"ORDER BY item ASC" % ", ".join(["%s"] * len(orders)),
orders.keys())
bunches = kgi.fetchbunches(c)
for b in bunches:
amount = orders[b.id]
if b.amount == 0 or b.amount < amount:
return
b.amount -= amount
if b.owner not in ordertexts:
ordertexts[b.owner] = ""
if b.owner not in comments:
comments[b.owner] = ""
if b.owner not in totals:
totals[b.owner] = 0
ordertexts[b.owner] += ("%ix %s\n" % (amount, b.item))
comments[b.owner] += ("%15s %s x %s (%s p.u., %s)\n"
% (humane(amount * b.price),
b.item,
humane(amount),
humane(b.price),
b.comment))
totals[b.owner] += amount * b.price
for (owner, comment) in comments.items():
comments[owner] += "---------------\n"
comments[owner] += "%15s sum" % humane(totals[owner])
for owner in ordertexts.keys():
sql_create_order(username, ordertexts[owner], "Alliance Market",
comments[owner], price=totals[owner],
producer=owner, state="claimed")
for b in bunches:
if b.amount == 0:
c.execute("DELETE FROM market_sale WHERE id = %s",
(b.id,))
else:
c.execute("UPDATE market_sale SET amount = %s WHERE id = %s",
(b.amount, b.id))
def get_orders(ordertype, sortby=None):
expire_orders()
db = kgi.connect('dbforcer')
c = db.cursor()
query = "SELECT * FROM market_order "
if ordertype == 'sell':
query += " WHERE type = 'sell' "
else:
query += " WHERE type = 'buy' "
if sortby == 'age':
query += "ORDER BY created DESC"
else:
query += "ORDER BY item ASC"
c.execute(query)
now = datetime.datetime.now()
bunches = kgi.fetchbunches(c)
for b in bunches:
b.age = (now - b.created).days
return bunches
def __init__(self, username):
self.username = username
db = kgi.connect('dbforums')
c = db.cursor()
if gid2group is None:
c.execute("SELECT title, gid FROM mybb_usergroups")
global gid2group
gid2group = dict(c.fetchall())
c.execute("SELECT usergroup, additionalgroups FROM mybb_users "
"WHERE username = %s",
(username,))
if c.rowcount == 0:
log.error("User %s does not exist." % username)
raise RuntimeError, "User does not exist"
(usergroup, additionalgroups) = c.fetchone()
self.groups = ([int(usergroup)]
+ [int(x) for x in additionalgroups.split(",")
if x != ''])
self.modified = False
def view_reputation(environ):
db = kgi.connect('dbforums')
c = db.cursor()
c.execute("SELECT u.uid, u.username, addu.uid, addu.username, "
" r.reputation, r.dateline, r.comments, r.pid "
"FROM mybb_reputation r "
" INNER JOIN mybb_users u ON r.uid = u.uid "
" INNER JOIN mybb_users addu ON r.adduid = addu.uid "
"ORDER BY r.dateline DESC "
"LIMIT 23")
reputation = [(uid, username, adduid, addusername, reputation,
datetime.datetime.utcfromtimestamp(dateline),
comments, pid)
for (uid, username, adduid, addusername, reputation,
dateline, comments, pid)
in c.fetchall()]
return kgi.template_response('forums/reputation.html',
user=environ["emapps.user"],
reputation=reputation)
def view_oplist(environ):
import simplejson as json
db = kgi.connect('dbforcer')
c = db.cursor()
c.execute("SELECT id, created, title FROM opwarn_list "
"WHERE created > NOW() - INTERVAL 1 HOUR "
"ORDER BY created DESC LIMIT 1")
if c.rowcount == 0:
return kgi.html_response(json.dumps({}),
header=[('Content-Type', 'application/json')]
)
(id, created, title) = c.fetchone()
delta = datetime.datetime.utcnow() - created
return kgi.html_response(json.dumps({'id': id,
'created': eve_time(created),
'seconds': delta.seconds,
'title': title}),
header=[('Content-Type', 'application/json')]
)
def save(self):
"""
Save the new group memberships of this user.
"""
if not self.modified:
return
if len(self.groups) > 0:
(usergroup, additionalgroups) = (self.groups[0],
self.groups[1:])
else:
(usergroup, additionalgroups) = (2, # Registered
[])
db = kgi.connect('dbforums')
c = db.cursor()
c.execute("UPDATE mybb_users "
"SET usergroup = %s, "
" additionalgroups = %s "
"WHERE username = %s",
(usergroup,
",".join(str(x) for x in additionalgroups),
self.username))
