def entity(request):
"""Get entity details."""
etypes = ('company', ) + m2m
etype = request.GET.get('type')
nq = request.GET.get('q')
if not nq:
try:
eid = int(request.GET.get('id', ''))
except ValueError:
return None
if etype not in etypes:
return None
q = 'SELECT id, name FROM {} WHERE id = %s'.format(etype)
return obj(sql(q, eid)[0])
q = 'SELECT id, name FROM {} WHERE LOWER(name) LIKE %s LIMIT 3'
nq = '%' + nq.lower() + '%'
if etype:
etypes = set(etype.split(',')) & set(etypes)
results = []
for e in etypes:
results.extend({'type': e, **obj(i)} for i in sql(q.format(e), nq))
return results
def feedback(request):
"""Get or submit feedback for item."""
# use username and bookname as input
q = """SELECT id FROM auth_user WHERE username = {}""".format(
request.username)
pg = pagination(request)
try:
user_id = sql(q + page(**pg))[0][0]
except:
return None
q = """SELECT id FROM item WHERE name = {}""".format(request.bookname)
try:
book_id = sql(q + page(**pg))[0][0]
except:
return None
if request.func == 'get':
q = """SELECT review FROM feedback WHERE user_id = {0} AND item_id = {1}""".format(
user_id, book_id)
for row in sql(q + page(**pg)):
yield {'review': row[0]}
else:
q = """INSERT INTO feedback (item_id, user_id, score, review, made_on) VALUES ({0},{1},{2},{3},{4})""".format(
book_id, user_id, request.score, request.review,
datetime.datetime.now())
sql(q + page(**pg))
return None
def item(request, item_id=None):
"""Get item details."""
keys = ('id', 'name', 'price', 'quantity', 'description', 'date_created',
'company.id', 'company.name')
if item_id:
q = """SELECT item.id, item.name, price, quantity, description,
date_created, company.id, company.name FROM item
INNER JOIN company ON company.id = company_id
WHERE item.id = %s"""
try:
it = obj(sql(q, item_id)[0], keys)
except IndexError:
raise Http404
cq = """SELECT {0}.id, {0}.name FROM item_{0}
INNER JOIN {0} ON item_{0}.{0}_id = {0}.id
WHERE item_id = %s"""
for k in m2m:
it[k + 's'] = tuple(obj(i) for i in sql(cq.format(k), item_id))
return it
q = 'SELECT id, name FROM item'
pg = pagination(request)
return {
'meta': {
'total': count(q)
},
'data': tuple(obj(i) for i in sql(q + page(**pg))),
}
def order(request, order_id=None, details=None):
"""Get details of an order."""
if details is None:
q = 'SELECT user_id, made_on, total FROM purchase WHERE id = %s'
try:
uid, made_on, total = sql(q, order_id)[0]
except IndexError:
return None
if uid != request.user.id:
raise PermissionDenied(WRONG_ACCOUNT)
else:
order_id, made_on, total = details
q = """SELECT item_id, name, p.quantity
FROM purchase_item p INNER JOIN item
ON p.item_id = item.id
WHERE purchase_id = %s"""
items = tuple({
'id': iid,
'name': name,
'quantity': qty,
} for iid, name, qty in sql(q, order_id))
return {
'id': order_id,
'total': total,
'items': items,
'made_on': made_on,
}
def order(request, order_id=None, details=None):
"""Get details of an order."""
if details is None:
q = 'SELECT user_id, made_on, total FROM purchase WHERE id = %s'
try:
uid, made_on, total = sql(q, order_id)[0]
except IndexError:
return Http404
if uid != request.user.id:
raise PermissionDenied(WRONG_ACCOUNT)
else:
order_id, made_on, total = details
keys = ('id', 'name', 'quantity')
q = """SELECT item_id, name, p.quantity FROM purchase_item p
INNER JOIN item ON p.item_id = item.id
WHERE purchase_id = %s"""
return {
'id': order_id,
'total': total,
'made_on': made_on,
'items': tuple(obj(i, keys) for i in sql(q, order_id)),
}
def feedback(request, item_id):
"""Get or submit feedback for item."""
keys = ('score', 'review', 'made_on', 'usefulness', 'user.id',
'user.username')
q = """SELECT score, review, made_on, usefulness, f.user_id, username
FROM feedback f
INNER JOIN auth_user ON f.user_id = auth_user.id
LEFT JOIN (SELECT item_id, user_id, AVG(usefulness) AS usefulness
FROM rating GROUP BY item_id, user_id) r
ON f.item_id = r.item_id AND f.user_id = r.user_id
WHERE f.item_id = %s"""
if request.method == 'POST':
if not request.user.is_authenticated:
raise PermissionDenied(NOT_LOGGED_IN)
uid = request.user.id
s = """INSERT INTO feedback (user_id, item_id, score, review, made_on)
VALUES (%s, %s, %s, %s, NOW())"""
try:
rq = loads(request.body)
sql(s, uid, item_id, int(rq['score']), rq['review'])
except (ValueError, KeyError):
return None
pg = pagination(request)
pg['sort'].append('-usefulness')
return (obj(i, keys) for i in sql(q + page(**pg), item_id))
def item(request, item_id=None):
"""Get item details."""
if item_id:
q = """SELECT name, price, quantity, description, date_created, company_id
FROM item WHERE id = %s"""
try:
it = sql(q, item_id)[0]
except IndexError:
return None
q = """SELECT {0}.id, {0}.name FROM item_{0}
INNER JOIN {0} ON item_{0}.{0}_id = {0}.id
WHERE item_id = %s"""
cs = {}
for k in ('creator', 'category'):
c = sql(q.format(k), item_id)
cs[k + 's'] = tuple({'id': i[0], 'name': i[1]} for i in c)
return {
**cs,
'id': item_id,
'name': it[0],
'price': it[1],
'quantity': it[2],
'description': it[3],
'date_created': it[4],
'company': entity.__wrapped__(None, 'company', it[5]),
}
q = 'SELECT id, name FROM item'
pg = pagination(request)
return ({'id': i[0], 'name': i[1]} for i in sql(q + page(**pg)))
def submit(request):
"""Inserts order into database."""
if not request.user.is_authenticated:
raise PermissionDenied(NOT_LOGGED_IN)
q = 'SELECT price FROM item WHERE id = %s'
sp = """INSERT INTO purchase (id, made_on, user_id, total)
VALUES (DEFAULT, NOW(), %s, %s)"""
si = """INSERT INTO purchase_item (purchase_id, item_id, quantity)
VALUES (%s, %s, %s)"""
uid = request.user.id
pid = None
items = []
total = 0
try:
rq = loads(request.body)
for item in rq['order']:
iid = int(item['id'])
qty = int(item['quantity'])
try:
total += qty * sql(q, iid)[0][0]
items.append((iid, qty))
except IndexError:
pass
except (ValueError, KeyError):
return {}
if items:
pid = sql(sp, uid, total)
for iid, qty in items:
sql(si, pid, iid, qty)
request.session.pop('cart', None)
return {'id': pid, 'total': total}
def recommends(request):
"""Get recommended items."""
q = """SELECT id FROM item WHERE name = {}""".format(request.bookname)
pg = pagination(request)
try:
book_id = sql(q + page(**pg))[0][0]
except:
return None
q = """SELECT review, user_name FROM (SELECT review, user_id FROM (SELECT review, AVG(usefulness) as score, user_id FROM rating WHERE item_id = {0} GROUP BY user_id) ORDER BY score LIMIT {1}), auth_user WHERE auth_user.id=user_id""".format(
book_id, request.topn)
pg = pagination(request)
for row in sql(q + page(**pg)):
yield {'review': row[0], 'user_name': row[1]}
def entity(request, e, e_id=None):
"""Get entity details."""
if e_id:
q = 'SELECT name FROM {} WHERE id = %s'.format(e)
try:
return {
'id': e_id,
'name': sql(q, int(e_id))[0][0],
}
except IndexError:
return None
q = 'SELECT id, name FROM {}'.format(e)
pg = pagination(request)
return ({'id': i[0], 'name': i[1]} for i in sql(q + page(**pg)))
def ratings(request):
"""Get feedback rated by logged-in user."""
if not request.user.is_authenticated:
raise PermissionDenied(NOT_LOGGED_IN)
q = """SELECT i.id, i.name, u.id, u.username, score, made_on, usefulness
FROM rating r INNER JOIN feedback f
ON r.item_id = f.item_id AND r.user_id = f.user_id
INNER JOIN auth_user u ON r.user_id = u.id
INNER JOIN item i ON r.item_id = i.id
WHERE rater_id = %s"""
pg = pagination(request)
pg['sort'].append('-usefulness')
for row in sql(q + page(**pg), request.user.id):
yield {
'item': {
'id': row[0],
'name': row[1],
},
'user': {
'id': row[2],
'username': row[3],
},
'score': row[4],
'made_on': row[5],
'usefulness': row[6],
}
def orders(request):
"""Get order history of logged-in user."""
if not request.user.is_authenticated:
raise PermissionDenied(NOT_LOGGED_IN)
q = 'SELECT id, made_on, total FROM purchase WHERE user_id = %s'
pg = pagination(request)
for row in sql(q + page(**pg), request.user.id):
yield order.__wrapped__(request, details=row)
def rate(request):
"""Get or submit rating for feedback."""
# use username and bookname as input
q = """SELECT id FROM auth_user WHERE username = {}""".format(
request.username)
pg = pagination(request)
try:
user_id = sql(q + page(**pg))[0][0]
except:
return None
q = """SELECT id FROM item WHERE name = {}""".format(request.bookname)
try:
book_id = sql(q + page(**pg))[0][0]
except:
return None
q = """SELECT id FROM auth_user WHERE username = {}""".format(
request.ratername)
pg = pagination(request)
try:
rater_id = sql(q + page(**pg))[0][0]
except:
return None
if request.func == 'get':
q = """SELECT review FROM feedback WHERE user_id = {0} AND item_id = {1} AND rater_id ={2}""".format(
user_id, book_id, rater_id)
for row in sql(q + page(**pg)):
yield {'review': row[0]}
else:
q = """INSERT INTO rating (item_id, user_id, rater_id, usefulness) VALUES ({0},{1},{2},{3})""".format(
book_id, user_id, rater_id, request.usefulness)
sql(q + page(**pg))
return None
def rate(request, item_id, user_id):
"""Get or submit rating for feedback."""
q = """SELECT u FROM (SELECT item_id, user_id, AVG(usefulness) AS u
FROM rating GROUP BY item_id, user_id) r
WHERE item_id = %s AND user_id = %s"""
if request.method == 'POST':
if not request.user.is_authenticated:
raise PermissionDenied(NOT_LOGGED_IN)
uid = request.user.id
s = """INSERT INTO rating (rater_id, item_id, user_id, usefulness)
VALUES (%s, %s, %s, %s)"""
try:
rq = loads(request.body)
sql(s, uid, item_id, user_id, int(rq['usefulness']))
except (ValueError, KeyError):
pass
try:
return sql(q, item_id, user_id)[0][0]
except IndexError:
raise Http404
def feedbacks(request):
"""Get feedback made by logged-in user."""
if not request.user.is_authenticated:
raise PermissionDenied(NOT_LOGGED_IN)
keys = ('item.id', 'item.name', 'score', 'review', 'made_on')
q = """SELECT item_id, name, score, review, made_on
FROM feedback INNER JOIN item
ON item.id = feedback.item_id
WHERE user_id = %s"""
pg = pagination(request)
for row in sql(q + page(**pg), request.user.id):
yield obj(row, keys)
def recommends(request, item_id):
"""Get recommended items."""
keys = ('id', 'name', 'users', 'sales')
q = """SELECT item.id, name, COUNT(DISTINCT p1.user_id) AS users,
SUM(i1.quantity) AS sales FROM item
INNER JOIN purchase_item i1 ON i1.item_id = id
INNER JOIN purchase p1 ON i1.purchase_id = p1.id
INNER JOIN purchase p2 ON p1.user_id = p2.user_id
INNER JOIN purchase_item i2 ON i2.purchase_id = p2.id
AND i1.item_id <> i2.item_id AND i2.item_id = %s
GROUP BY item.id
"""
pg = pagination(request)
pg['sort'].append('-sales')
return (obj(i, keys) for i in sql(q + page(**pg), item_id))
def ratings(request):
"""Get feedback rated by logged-in user."""
if not request.user.is_authenticated:
raise PermissionDenied(NOT_LOGGED_IN)
keys = ('item.id', 'item.name', 'user.id', 'user.username', 'score',
'review', 'made_on', 'usefulness')
q = """SELECT i.id, i.name, u.id, u.username,
score, review, made_on, usefulness
FROM rating r INNER JOIN feedback f
ON r.item_id = f.item_id AND r.user_id = f.user_id
INNER JOIN auth_user u ON r.user_id = u.id
INNER JOIN item i ON r.item_id = i.id
WHERE rater_id = %s"""
pg = pagination(request)
pg['sort'].append('-usefulness')
for row in sql(q + page(**pg), request.user.id):
yield obj(row, keys)
def search(request):
"""Search for item."""
keys = ('id', 'name', 'score', 'date_created')
q = """SELECT id, name, score, date_created FROM item
LEFT JOIN (SELECT item_id, AVG(score) AS score FROM feedback
GROUP BY item_id) f ON item.id = f.item_id"""
pg = pagination(request)
i = 'id IN (SELECT id FROM item {0} WHERE {1})'
sq = ((' ', 'INNER JOIN item_{0} {0}{1} ON item.id = {0}{1}.item_id'),
(' AND ', '{0}{1}.{0}_id = %s'))
mfil = tuple(((k, i), v) for k in m2m for i, v in enumerate(
v for v in request.GET.get(k, '').split(',') if v))
if mfil:
ks, vs = zip(*mfil)
vals = list(vs)
fils = [i.format(*(j.join(s.format(*k) for k in ks) for j, s in sq))]
else:
vals = []
fils = []
cf = request.GET.get('company')
try:
vals.append(int(cf))
fils.append('company_id = %s')
except (TypeError, ValueError):
pass
nf = request.GET.get('name')
if nf:
vals.append('%' + nf.lower() + '%')
fils.append('LOWER(name) LIKE %s')
if fils:
q += ' WHERE ' + ' AND '.join(fils)
return {
'meta': {
'total': count(q, *vals)
},
'data': tuple(obj(i, keys) for i in sql(q + page(**pg), *vals)),
}
def search(request):
"""Search for item."""
q = """SELECT item.name, item.id, item.company_id, item.description, item.price, item.quantity, item.date_created, item_creator.creator_id, item_category.category_id, feedback.avg_score
FROM item, item_creator, item_category WHERE item.id = item_category.id AND item.id = item_creator.id"""
q1 = """name = {0}""".format(request.name)
q2 = """company_id = {0}""".format(request.company_id)
q3 = """category_id = {0}""".format(request.category_id)
q4 = """creator_id = {0}""".format(request.creator_id)
q5 = """ORDER BY item.date_created"""
q6 = """ORDER BY avg_score"""
q7 = """UNION (SELECT AVERAGE(score) AS avg_score FROM feedback GROUP BY item_id)"""
if request.name == 'None':
q1 = """"""
elif request.company_id == 'None':
q2 = """"""
elif request.category_id == 'None':
q3 = """"""
elif request.creator_id == 'None':
q4 = """"""
elif request.sortmethod == 'year':
q6 = """"""
elif request.sortmethod == 'avergae score':
q5 = """"""
q = q + """AND""" + q1 + """AND""" + q2 + """AND""" + q3 + """AND""" + q4 + """AND""" + q7 + q5 + q6
pg = pagination(request)
for it in sql(q + page(**pg)):
yield {
'name': it[0],
'id': it[1],
'company_id': it[2],
'description': it[3],
'price': it[4],
'quantity': it[5],
'date_created': it[6],
'creator_id': it[7],
'category_id': it[8],
'average_score': it[9]
}
def feedbacks(request):
"""Get feedback made by logged-in user."""
if not request.user.is_authenticated:
raise PermissionDenied(NOT_LOGGED_IN)
q = """SELECT item_id, name, score, review, made_on
FROM feedback INNER JOIN item
ON item.id = feedback.item_id
WHERE user_id = %s"""
pg = pagination(request)
for row in sql(q + page(**pg), request.user.id):
yield {
'item': {
'id': row[0],
'name': row[1],
},
'score': row[2],
'review': row[3],
'made_on': row[4],
}
