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 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 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 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 moderations(request):
if request.user.role not in user_models.get_internal_roles():
return http.HttpResponseForbidden()
context = {
'title': 'Moderations',
}
context = pagination(request, context, reverse_lazy('employee-moderations'))
objs = employee_models.Employee.objects.filter(updated=True)
context['page_obj'] = paginate_moderations(request, objs)
if (len(objs) and (context['page_obj'].has_next() or
context['page_obj'].has_previous())):
context['is_paginated'] = True
else:
context['is_paginated'] = False
context['header_values'] = [('id', 'ID'), ('first_name', _('First name')),
('last_name', _('Last name')),
('email', _('E-mail')),
('host_entity', _('Host entity'))]
context['object_list'] = context['page_obj'].object_list
context['dont_show_search_bar'] = True
context['item_url'] = 'employee-moderation'
return render(request, 'lgc/sub_generic_list.html', context)
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 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 get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
context['search_url'] = reverse_lazy('hr-employees')
context['title'] = _('Employees')
context['update_url'] = 'hr-update-account'
context['item_url'] = 'hr-employee-file'
context['search_form'] = self.get_search_form()
return pagination(self.request, context, reverse_lazy('hr-employees'))
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 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 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 get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
context['title'] = _('Users')
context['create_url'] = reverse_lazy('user-create')
context['item_url'] = 'user'
context['ajax_search_url'] = reverse_lazy(
'user-local-user-search-ajax')
context['search_url'] = reverse_lazy('user-list')
context['header_values'] = [('email', 'E-mail'),
('first_name', _('First Name')),
('last_name', _('Last Name')),
('is_active', _('Active')),
('role', _('Role')),
('billing', _('Billing')),
('is_staff', _('Staff'))]
return pagination(self.request, context, reverse_lazy('user-list'))
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],
}
