def create(self, data=None, commit=True):
if data is None:
data = request.json or {}
handle_password(data)
status, = db_session.execute(
"SELECT GET_LOCK('member_number', 20)").fetchone()
if not status:
raise InternalServerError(
"Failed to create member, try again later.",
log="failed to aquire member_number lock")
try:
if data.get('member_number') is None:
sql = "SELECT COALESCE(MAX(member_number), 999) FROM membership_members"
max_member_number, = db_session.execute(sql).fetchone()
data['member_number'] = max_member_number + 1
obj = self.to_obj(self._create_internal(data, commit=commit))
return obj
except Exception:
# Rollback session if anything went wrong or we can't release the lock.
db_session.rollback()
raise
finally:
db_session.execute("DO RELEASE_LOCK('member_number')")
def register_login_success(ip, user_id):
db_session.execute(
"INSERT INTO login (success, user_id, ip) VALUES (1, :user_id, :ip)",
{
'user_id': user_id,
'ip': ip
})
def get_failed_login_count(ip):
count, = db_session.execute(
"SELECT count(1) FROM login"
" WHERE ip = :ip AND NOT success AND date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)",
{
'ip': ip
}).fetchone()
return count
def create(self, data=None, commit=True):
if data is None:
data = request.json or {}
status, = db_session.execute("SELECT GET_LOCK('display_order', 20)").fetchone()
if not status:
raise InternalServerError("Failed to create, try again later.",
log="failed to aquire display_order lock")
try:
if data.get('display_order') is None:
data['display_order'] = (db_session.query(func.max(self.model.display_order)).scalar() or 0) + 1
obj = self.to_obj(self._create_internal(data, commit=commit))
return obj
except Exception:
# Rollback session if anything went wrong or we can't release the lock.
db_session.rollback()
raise
finally:
db_session.execute("DO RELEASE_LOCK('display_order')")
def commit_transaction_to_db(member_id=None,
total_amount=None,
contents=None,
stripe_card_source_id=None,
activates_member=False):
""" Save as new transaction with transaction content in db and return it transaction. """
transaction = Transaction(member_id=member_id,
amount=total_amount,
status=Transaction.PENDING)
db_session.add(transaction)
db_session.flush()
for content in contents:
content.transaction_id = transaction.id
db_session.add(content)
db_session.flush()
db_session.execute(
"""
INSERT INTO webshop_transaction_actions (content_id, action_type, value, status)
SELECT :content_id AS content_id, action_type, SUM(:count * value) AS value, :pending AS status
FROM webshop_product_actions WHERE product_id=:product_id AND deleted_at IS NULL GROUP BY action_type
""", {
'content_id': content.id,
'count': content.count,
'pending': TransactionAction.PENDING,
'product_id': content.product_id
})
if activates_member:
# Mark this transaction as one that is for registering a member.
db_session.add(PendingRegistration(transaction_id=transaction.id))
db_session.add(
StripePending(transaction_id=transaction.id,
stripe_token=stripe_card_source_id))
return transaction
def get_member_number(self):
# Ugly but will work most of the time.
sql = "SELECT COALESCE(MAX(member_number), 0) FROM membership_members"
member_number = db_session.execute(sql).fetchone()[0] + randint(1000, 2000)
return member_number
def quiz_statistics(quiz_id: int):
# How many members have answered the quiz that should have
# Correct percentage per question
# Average correct percentage per member
questions = db_session.query(QuizQuestion).filter(
QuizQuestion.deleted_at == None, QuizQuestionOption.deleted_at == None,
QuizQuestion.quiz_id == quiz_id).join(QuizQuestion.options).all()
# Note: counts each member at most once per question. So multiple mistakes on the same question are not counted
incorrect_answers_by_question = mapify(
db_session.query(QuizAnswer.question_id,
func.count(distinct(QuizAnswer.member_id))).join(
QuizAnswer.question).filter(
QuizQuestion.quiz_id == quiz_id).filter(
QuizAnswer.correct == False).group_by(
QuizAnswer.question_id).all())
answers_by_question = mapify(
db_session.query(QuizAnswer.question_id,
func.count(distinct(QuizAnswer.member_id))).join(
QuizAnswer.question).filter(
QuizQuestion.quiz_id).filter(
QuizAnswer.deleted_at == None).group_by(
QuizAnswer.question_id).all())
first_answer_by_member = db_session.query(
QuizAnswer.member_id, QuizAnswer.question_id,
func.min(QuizAnswer.id).label("id")).filter(
QuizAnswer.deleted_at == None).group_by(
QuizAnswer.member_id, QuizAnswer.question_id).subquery()
answers_by_option = mapify(
db_session.query(QuizAnswer.option_id, func.count(distinct(QuizAnswer.member_id))) \
.join(first_answer_by_member, (QuizAnswer.question_id == first_answer_by_member.c.question_id) & (QuizAnswer.member_id == first_answer_by_member.c.member_id)) \
.join(QuizAnswer.question)
.filter(QuizAnswer.id == first_answer_by_member.c.id) \
.filter(QuizQuestion.quiz_id == quiz_id)
.group_by(QuizAnswer.option_id) \
.all()
)
seconds_to_answer_quiz = list(
db_session.execute(
"select TIME_TO_SEC(TIMEDIFF(max(quiz_answers.created_at), min(quiz_answers.created_at))) as t from quiz_answers JOIN quiz_questions ON question_id=quiz_questions.id where quiz_questions.quiz_id=:quiz_id group by member_id order by t asc;",
{"quiz_id": quiz_id}))
print(seconds_to_answer_quiz)
median_seconds_to_answer_quiz = seconds_to_answer_quiz[
len(seconds_to_answer_quiz) //
2][0] if len(seconds_to_answer_quiz) > 0 else 0
return {
"median_seconds_to_answer_quiz":
median_seconds_to_answer_quiz,
# Maximum number members that have answered any question.
# This ensures we also account for questions added later (which may not have as many answers)
# We iterate through the questions list to ensure we dont count deleted questions
"answered_quiz_member_count":
max([
answers_by_question.get(question.id, 0) for question in questions
]),
"questions": [
{
"question":
quiz_question_entity.to_obj(question),
"options": [{
"answer_count":
answers_by_option.get(option.id, 0),
"option":
quiz_question_option_entity.to_obj(option)
} for option in question.options],
# Number of unique members that have answered this question
"member_answer_count":
answers_by_question.get(question.id, 0),
"incorrect_answer_fraction":
sum(
answers_by_option.get(option.id, 0)
for option in question.options if not option.correct) /
answers_by_question.get(question.id, 1),
} for question in questions
]
}
def remove(self, entity_ids, related_entity_id):
for entity_id in entity_ids:
db_session.execute(self.delete, {
'entity_id': entity_id,
'related_entity_id': related_entity_id
})
def add(self, entity_ids, related_entity_id):
for entity_id in entity_ids:
db_session.execute(self.insert, {
'entity_id': entity_id,
'related_entity_id': related_entity_id
})
def register_login_failed(ip):
db_session.execute("INSERT INTO login (success, ip) VALUES (0, :ip)",
{'ip': ip})