def get_overview_record(user, domain):
cursor = connection.cursor()
cmd = """ SELECT user_level, price FROM answerer_overview
WHERE user_id = %s and domain_id = %s """
cursor.execute(cmd, [user.id, domain.id])
res = utils.dictfetchall(cursor)
return res[0]
def get_batch_overview(batch_id):
cursor = connection.cursor()
cmd = """ SELECT question_id, number_completed, user_confs, total_price
FROM batch_overview as o
WHERE batch_id = %s"""
cursor.execute(cmd, (batch_id,))
return utils.dictfetchall(cursor)
def get_histograms(self):
cmd = """ SELECT dist, conf
FROM alloc_stems
WHERE domain = %s
ORDER BY conf DESC """
cursor = connection.cursor()
cursor.execute(cmd, (self.domain_id,))
return utils.dictfetchall(cursor)
def get_global_user_overview():
cursor = connection.cursor()
cmd = """ SELECT username, short_name, question_quota,
accuracy, user_level, num_pending
FROM answerer_overview as o
ORDER BY o.accuracy DESC, o.short_name """
cursor.execute(cmd)
res = utils.dictfetchall(cursor)
return res
def get_level_number_extrema(self):
cmd = """ SELECT MIN(level_number) as min, MAX(level_number) as max
FROM ui_level
WHERE domain_id = %s """
cursor = connection.cursor()
cursor.execute(cmd, (self.domain_id,))
res = utils.dictfetchall(cursor)
assert len(res) == 1
return res[0]
def get_user_list(domain):
cursor = connection.cursor()
cmd = """ SELECT user_id, user_level, price,
num_to_answer - num_pending as avail
FROM answerer_overview
WHERE domain_id = %s
ORDER BY user_level DESC, avail DESC """
cursor.execute(cmd, (domain,))
user_list = {}
for row in utils.dictfetchall(cursor):
l = user_list.setdefault(row['user_level'], [])
l.append(row)
return user_list
def get_availability_histo(domain):
cursor = connection.cursor()
cmd = """ SELECT user_level as level,
SUM(num_to_answer - num_pending) as avail
FROM answerer_overview
WHERE domain_id = %s
GROUP BY user_level
ORDER BY user_level ASC """
cursor.execute(cmd, (domain,))
histo_raw = utils.dictfetchall(cursor)
histo = Counter()
for row in histo_raw:
histo[row['level']] = row['avail']
return histo
def register_schema_map(self, sourceid, domain_shortname="IT"):
cur = connections[settings.TAMER_DB].cursor()
mappings = self.get_field_mappings_by_source(sourceid, only_unmapped=True)
cmd = "SELECT id, name FROM global_attributes"
cur.execute(cmd)
cur.connection.commit()
global_attributes = dictfetchall(cur)
batch_rec = Batch()
batch_rec.owner = self.asker
batch_rec.question_type = self.qt
batch_rec.source_name = str(sourceid)
batch_rec.save()
for fid in mappings.keys():
smq = SchemaMapQuestion()
smq.batch = batch_rec
smq.asker = batch_rec.owner
smq.domain = Domain.objects.get(short_name=domain_shortname)
smq.question_type = self.qt
smq.local_field_id = fid
smq.local_field_name = mappings[fid]["name"]
smq.save()
choices = mappings[fid]["matches"]
ids = list()
choice_count = 10
for c in choices:
if choice_count > 0:
smc = SchemaMapChoice()
smc.question = smq
smc.global_attribute_id = c["id"]
smc.global_attribute_name = c["name"]
smc.confidence_score = c["score"]
smc.save()
ids.append(c["id"])
choice_count -= 1
id_set = set(ids)
for a in global_attributes:
if a["id"] not in id_set:
smc = SchemaMapChoice()
smc.question = smq
smc.global_attribute_id = a["id"]
smc.global_attribute_name = a["name"]
smc.save()
def get_candidates(domain, criterion, method, limit=100):
cursor = connection.cursor()
avail_arry = get_avail_array(domain)
if method == 'min_price':
cmd = """ SELECT dist, conf, price
FROM alloc_stems as a
WHERE all_gte(%s, dist)
AND conf >= %s
AND domain = %s
ORDER BY price ASC
LIMIT %s; """
elif method == 'max_conf':
cmd = """ SELECT dist, conf, price
FROM alloc_stems as a
WHERE all_gte(%s, dist)
AND price <= %s
AND domain = %s
ORDER BY conf DESC
LIMIT %s; """
cursor.execute(cmd, (avail_arry, criterion, domain, limit,))
return utils.dictfetchall(cursor)
def get_answerer_domain_overview(user):
cursor = connection.cursor()
cmd = """ SELECT o.*, o.num_answered + o.num_pending as total_answered
FROM answerer_overview as o WHERE user_id = %s """
cursor.execute(cmd, [user.id])
return utils.dictfetchall(cursor)
