def get_statistics(self, user_id):
cursor = DataBase.get_connection_default().cursor()
cursor.execute(
"select count(*) from {toreview}".format(toreview=self.toreview))
records_to_review = cursor.fetchone()[0]
cursor.execute(
"select count(*) from {user_reviewed} where UserId={user_id}".
format(user_reviewed=self.user_reviewed, user_id=user_id))
user_reviewed = cursor.fetchone()[0]
cursor.execute(
"SELECT * FROM ("
"SELECT UserId, Counts, Rank() OVER (ORDER BY Counts DESC) AS rank "
"FROM ("
"SELECT UserId, count(*) AS Counts "
"FROM {user_reviewed} "
"GROUP BY UserId) i) j "
"WHERE UserId={user_id}".format(user_reviewed=self.user_reviewed,
user_id=user_id))
rank = cursor.fetchone()
return {
'records_to_review': records_to_review,
'user_reviewed': user_reviewed,
'rank': -1 if rank is None else rank[0]
}
def save_to_db(self,
user_id,
record_id,
match,
time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')):
cursor = DataBase.get_connection_default().cursor()
cursor.execute(
"SELECT ViewCounts FROM {table} where RecordId={record_id}".format(
table=self.toreview, record_id=record_id))
reviewed_times = cursor.fetchone()[0]
if reviewed_times < self.votes_per_record - 1:
cursor.execute(
"UPDATE {table} SET ViewCounts={counts} where RecordId={record_id}"
.format(table=self.toreview,
counts=reviewed_times + 1,
record_id=record_id))
else:
cursor.execute("INSERT INTO {reviewed} SELECT * FROM {toreview} "
"WHERE RecordId={record_id}".format(
reviewed=self.reviewed,
toreview=self.toreview,
record_id=record_id))
cursor.execute(
"DELETE FROM {toreview} WHERE RecordId={record_id}".format(
toreview=self.toreview, record_id=record_id))
cursor.execute(
"INSERT INTO {user_reviewed} (UserId, RecordId, Match, Time) Values"
"({user_id}, {record_id}, {match}, '{time}')".format(
user_reviewed=self.user_reviewed,
user_id=user_id,
record_id=record_id,
match=match,
time=time))