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 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 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 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 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 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 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)
