def faq request customer_uuid customer get_object_or_404 Customer uuid

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
def faq(request, customer_uuid):
customer = get_object_or_404(Customer, uuid=customer_uuid)
lang = request.REQUEST.get("lang", "en")
questions = Question.objects.annotate(answer_counter=Count("answer")).filter(customer=customer, answer_counter__gt=0, lang=lang).order_by("-date")
#questions = Question.objects.raw("""SELECT question.id, content, date, plus, minus, category.id as category_id, title
# FROM question, category WHERE question.customer_id=%s AND question.category_id=category.id
# ORDER BY date DESC""", (customer.id,))
if request.method == "POST":
return HttpResponse(json.dumps([{"question_id": question.id, "category_id": question.category.id, "category_title": question.category.title, "plus": question.plus, "minus": question.minus, "content": question.content, "answers": [{"answer_id": i.id, "content": i.content, 'plus': i.plus, 'minus': i.minus} for i in question.answer_set.all()]} for question in questions]))
else:
return HttpResponse(request.GET['callback']+"("+json.dumps([{"question_id": question.id, "category_id": question.category.id, "category_title": question.category.title, "plus": question.plus, "minus": question.minus, "content": question.content, "answers": [{"answer_id": i.id, "content": i.content, 'plus': i.plus, 'minus': i.minus} for i in question.answer_set.all()]} for question in questions])+")")
def faq_search(request, customer_uuid):
conn = pool.getconn()
conn.autocommit = True
postgres = conn.cursor()
customer = get_object_or_404(Customer, uuid=customer_uuid)
query = " | ".join([i.strip() for i in request.REQUEST['query'].split(" ") if i != ""])
try:
postgres.execute("""SELECT id,
ts_headline('english', content, to_tsquery('english', %s)) as content,
customer_answer,
rank,
plus,
minus,
title,
category_id
FROM (SELECT question.id,
content,
customer_answer,
ts_rank_cd(
setweight(to_tsvector('english', question.content), 'A') || setweight(to_tsvector('english', question.customer_answer), 'D'),
to_tsquery('english', %s)
) AS rank,
plus,
minus,
title,
category.id as category_id
FROM question,
category
WHERE question.customer_id=%s
AND question.is_published=%s
AND category.id = question.category_id
AND to_tsvector('english', question.content || ' ' || question.customer_answer) @@ to_tsquery('english', %s)
ORDER BY rank DESC
)
AS foo""", (query, query, customer.id, True, query))
questions = postgres.fetchall()
except:
questions = []
last_query = redis.hgetall("query:%s" % request.REQUEST['visitor_id'])
try:
last_query_length = len(last_query['content'].decode("utf8"))
except:
last_query_length = 0
cur_query_length = len(request.REQUEST['query'])
if last_query:
if last_query_length < cur_query_length:
if distance(last_query['content'].decode("utf8"), request.REQUEST['query']) > 5:
postgres.execute("""INSERT INTO query (visitor_id, customer_id, content, date, counter, address, is_found) VALUES (%s, %s, %s, %s, 1, %s, %s) RETURNING id""",
(request.REQUEST['visitor_id'], request.REQUEST['customer_id'], request.REQUEST['query'], datetime.datetime.utcnow(), request.REQUEST['address'], False)
)
redis.hmset("query:%s" % request.REQUEST['visitor_id'], {'id': postgres.fetchone()[0], 'content': request.REQUEST['query']})
else:
postgres.execute("""UPDATE query SET content=%s, date=%s, is_found=%s WHERE id=%s""", (request.REQUEST['query'], datetime.datetime.utcnow(), False, last_query['id']))
redis.hmset("query:%s" % request.REQUEST['visitor_id'], {'content': request.REQUEST['query']})
else:
if distance(last_query['content'].decode("utf8"), request.REQUEST['query']) > 5:
postgres.execute("""INSERT INTO query (visitor_id, customer_id, content, date, counter, address, is_found) VALUES (%s, %s, %s, %s, 1, %s, %s) RETURNING id""",
(request.REQUEST['visitor_id'], request.REQUEST['customer_id'], request.REQUEST['query'], datetime.datetime.utcnow(), request.REQUEST['address'], False)
)
redis.hmset("query:%s" % request.REQUEST['visitor_id'], {'id': postgres.fetchone()[0], 'content': request.REQUEST['query']})
else:
postgres.execute("""INSERT INTO query (visitor_id, customer_id, content, date, counter, address, is_found) VALUES (%s, %s, %s, %s, 1, %s, %s) RETURNING id""",
(request.REQUEST['visitor_id'], request.REQUEST['customer_id'], request.REQUEST['query'], datetime.datetime.utcnow(), request.REQUEST['address'], False)
)
redis.hmset("query:%s" % request.REQUEST['visitor_id'], {'id': postgres.fetchone()[0], 'content': request.REQUEST['query']})
output = []
for question in questions:
tmp = {}
tmp['question'] = question[1]
tmp['id'] = question[0]
tmp['category'] = question[6]
tmp['category_id'] = question[7]
tmp['plus'] = question[4]
tmp['minus'] = question[5]
tmp['answers'] = []
postgres.execute("SELECT id, content, is_customer, plus, minus FROM answer WHERE question_id=%s ORDER BY date DESC", (question[0],))
answers = postgres.fetchall()
for answer in answers:
data = {}
data['answer'] = answer[1]
data['type'] = 'customer' if answer[2] else 'user'
data['plus'] = question[3]
data['minus'] = question[4]
data['id'] = question[0]
tmp['answers'].append(data)
if answers:
output.append(tmp)
if request.method == "POST":
return HttpResponse(json.dumps(output))
else:
return HttpResponse(request.GET['callback']+"("+json.dumps(output)+")")