def member_quiz_statistics(member_id: int):
'''Returns information about all quizzes and if the given member has completed them'''
quizzes = db_session.query(Quiz).filter(Quiz.deleted_at == None).all()
answered_questions_per_quiz_query = (db_session.query(
QuizQuestion.quiz_id,
func.count(func.distinct(QuizAnswer.option_id))).join(
QuizAnswer, QuizQuestion.id == QuizAnswer.question_id).filter(
QuizAnswer.member_id == member_id).filter(
((QuizAnswer.id == None) | QuizAnswer.correct)
& (QuizAnswer.deleted_at == None)
& (QuizQuestion.deleted_at == None)).group_by(
QuizQuestion.quiz_id))
answered_questions_per_quiz = mapify(
answered_questions_per_quiz_query.all())
total_questions_in_quiz = mapify(
(db_session.query(QuizQuestion.quiz_id,
func.count(func.distinct(QuizQuestion.id))).filter(
QuizQuestion.deleted_at == None).group_by(
QuizQuestion.quiz_id)).all())
return [{
"quiz":
quiz_entity.to_obj(quiz),
"total_questions_in_quiz":
total_questions_in_quiz[quiz.id],
"correctly_answered_questions":
answered_questions_per_quiz[quiz.id]
if quiz.id in answered_questions_per_quiz else 0,
} for quiz in quizzes]
def quiz_member_answer_stats(quiz_id: int):
''' Returns all members which haven't completed the quiz'''
# Calculates how many questions each member has answered correctly
# Includes an entry for all members, even if it is zero
correctly_answered_questions = db_session.query(Member.member_id, func.count(distinct(QuizAnswer.option_id)).label("count")) \
.join(QuizAnswer, Member.member_id==QuizAnswer.member_id, isouter=True) \
.join(QuizAnswer.question, isouter=True) \
.filter(QuizQuestion.quiz_id == quiz_id) \
.filter((QuizAnswer.id == None) | ((QuizAnswer.correct) & (QuizAnswer.deleted_at == None) & (QuizQuestion.deleted_at == None))) \
.group_by(Member.member_id) \
.subquery()
question_count = db_session.query(
QuizQuestion).filter((QuizQuestion.quiz_id == quiz_id)
& (QuizQuestion.deleted_at == None)).count()
members = db_session.query(Member.member_id, correctly_answered_questions.c.count) \
.join(correctly_answered_questions, (correctly_answered_questions.c.member_id==Member.member_id)) \
.filter(Member.deleted_at == None)
return [
QuizMemberStat(
member_id=member[0],
remaining_questions=question_count - member[1],
correctly_answered_questions=member[1],
) for member in members.all()
]
def next_question(quiz_id: int, include_correct=False):
# Find all questions that the user has correctly answered
correct_questions = db_session.query(QuizQuestion.id) \
.filter(QuizQuestion.quiz_id == quiz_id) \
.join(QuizQuestion.answers) \
.filter(QuizAnswer.member_id == g.user_id) \
.filter((QuizAnswer.correct) & (QuizAnswer.deleted_at == None))
# Find questions which the user has not yet answered correctly
q = db_session.query(QuizQuestion) \
.filter(QuizQuestion.id.notin_(correct_questions)) \
.filter(QuizQuestion.quiz_id == quiz_id) \
.filter(QuizQuestion.deleted_at == None) \
.order_by(func.random())
# Pick the first one
question = q.first()
if question is None:
return None
json = quiz_question_entity.to_obj(question)
json["options"] = []
for option in question.options:
option = quiz_question_option_entity.to_obj(option)
if option["deleted_at"] is None:
del option["correct"]
del option["answer_description"]
json["options"].append(option)
del json["answer_description"]
return json
def test_request_password_reset_with_correct_email_creates_message_with_token(
self):
member = self.db.create_member()
self.api.post("/oauth/request_password_reset", data=dict(user_identification=member.email), headers={})\
.expect(code=200)
reset_token = db_session.query(PasswordResetToken).filter_by(
member_id=member.member_id).one()
message = db_session.query(Message).filter_by(
member_id=member.member_id).one()
self.assertIn(reset_token.token, message.body)
def get_member_by_user_identification(user_identification):
try:
if user_identification.isdigit():
return db_session.query(Member).filter_by(
member_number=int(user_identification), deleted_at=None).one()
return db_session.query(Member).filter_by(email=user_identification,
deleted_at=None).one()
except NoResultFound:
raise NotFound(
f"Could not find any user with the name or email '{user_identification}'.",
fields='user_identification',
status="not found")
def send_key_updated_email(member_id, extended_days, end_date):
member = db_session.query(Member).get(member_id)
send_message(MessageTemplate.ADD_LABACCESS_TIME,
member,
extended_days=extended_days,
end_date=date_to_str(end_date))
def box_terminator_nag(member_number=None, box_label_id=None, nag_type=None):
try:
box = db_session.query(Box).filter(
Box.box_label_id == box_label_id,
Member.member_number == member_number).one()
except NoResultFound:
raise NotFound("Bloop, lådan finns i Lettland")
try:
template = {
"nag-warning": MessageTemplate.BOX_WARNING,
"nag-last-warning": MessageTemplate.BOX_FINAL_WARNING,
"nag-terminated": MessageTemplate.BOX_TERMINATED,
}[nag_type]
except KeyError:
raise BadRequest(f"Bad nag type {nag_type}")
today = date.today()
end_date = get_labacess_end_date(box)
terminate_date = get_expire_date_from_labaccess_end_date(end_date)
send_message(
template,
box.member,
labaccess_end_date=date_to_str(end_date),
to_termination_days=(terminate_date - today).days,
days_after_expiration=(today - end_date).days,
)
box.last_nag_at = datetime.utcnow()
def test_reminder_message_is_not_created_if_member_has_pending_labaccess_days(
self):
member = self.db.create_member()
self.db.create_span(type=Span.LABACCESS,
enddate=self.date(LABACCESS_REMINDER_DAYS_BEFORE))
p0_count = 1
expected_sum = self.p0_price * p0_count
cart = [
{
"id": self.p0_id,
"count": p0_count
},
]
transaction = create_transaction(member_id=member.member_id,
purchase=dict(
cart=cart,
expected_sum=expected_sum),
stripe_reference_id="not_used")
transaction.status = Transaction.COMPLETED
db_session.add(transaction)
db_session.flush()
self.send_labaccess()
self.assertEqual(0, db_session.query(Message).count())
def answer_question(question_id):
data = request.json
option_id = int(data["option_id"])
option = db_session \
.query(QuizQuestionOption) \
.join(QuizQuestionOption.question) \
.filter((QuizQuestion.id == question_id) & (QuizQuestionOption.id == option_id) & (QuizQuestionOption.deleted_at == None)) \
.one_or_none()
if option == None:
return (
400,
f"Option id {option_id} is not an option for question id {question_id}"
)
db_session.add(
QuizAnswer(question_id=question_id,
option_id=option_id,
member_id=g.user_id,
correct=option.correct))
db_session.flush()
question = db_session.query(QuizQuestion).get(question_id)
json = quiz_question_entity.to_obj(question)
json["options"] = []
for option in question.options:
option = quiz_question_option_entity.to_obj(option)
json["options"].append(option)
return json
def create_admin(admins):
banner(BLUE, "Admin User")
s = input(
"Do you want to create a new admin user"
" (you can later use the create_user.py script to create users)? [Y/n]: "
)
if s not in {"", "y", "yes"}:
return
while True:
try:
member = member_entity.create(
dict(
firstname=input("First name: "),
lastname=input("Last name: "),
email=input("Email: "),
unhashed_password=getpass("Password: "******"Addmin new menber {member_id} to admin group.")
admins.members.append(db_session.query(Member).get(member_id))
db_session.commit()
break
except Exception as e:
# This may fail when for example the password was too weak
print(e)
print(
"Something went wrong while creating the new user. Please try again."
)
def test_queue_message_for_sending_with_group_and_member_recipient_list(
self):
message = self.obj.create_message()
message['recipients'] = [
{
'id': self.group_id,
'type': 'group'
},
{
'id': self.member_2_id,
'type': 'member'
},
{
'id': self.member_3_id,
'type': 'member'
},
]
self.api.post("/messages/message", message).expect(201)
db_messages = db_session.query(Message).filter_by(
subject=message['subject']).all()
self.assertCountEqual([
self.member_1['email'], self.member_2['email'],
self.member_3['email']
], [m.recipient for m in db_messages])
self.assertCountEqual(
[self.member_1_id, self.member_2_id, self.member_3_id],
[m.member_id for m in db_messages])
self.assertEqual({'queued'}, {m.status for m in db_messages})
self.assertEqual({message['subject']},
{m.subject
for m in db_messages})
self.assertEqual({message['body']}, {m.body for m in db_messages})
def send_membership_updated_email(member_id, extended_days, end_date):
member = db_session.query(Member).get(member_id)
send_message(MessageTemplate.ADD_MEMBERSHIP_TIME,
member,
extended_days=extended_days,
end_date=date_to_str(end_date))
def read(self, entity_id):
entity = db_session.query(self.model).get(entity_id)
if not entity:
raise NotFound(
"Could not find any entity with specified parameters.")
obj = self.to_obj(entity)
return obj
def payment_success(transaction):
complete_transaction(transaction)
ship_orders(ship_add_labaccess=False, transaction=transaction)
if db_session.query(PendingRegistration).filter(
PendingRegistration.transaction_id == transaction.id).count():
activate_member(transaction.member)
def labaccess_reminder(render_template):
now = datetime.utcnow()
end_date_reminder_target = now.date() + timedelta(
days=LABACCESS_REMINDER_DAYS_BEFORE)
query = db_session.query(Member)
query = query.join(Span)
query = query.filter(
Member.deleted_at.is_(None),
Span.type == Span.LABACCESS,
Span.deleted_at.is_(None),
Span.enddate == end_date_reminder_target,
)
for member in query:
# We have a candidate, now check if we should send a reminder.
# First double check the end date so we don't send reminder if there is another span further in the future.
end_date = db_session.query(func.max(Span.enddate)).filter(
Span.member == member, Span.type == Span.LABACCESS,
Span.deleted_at.is_(None)).scalar()
if end_date != end_date_reminder_target:
continue
# Don't send a reminder if we sent a reminder the last 28 days.
if already_sent_message(MessageTemplate.LABACCESS_REMINDER, member,
LABACCESS_REMINDER_GRACE_PERIOD):
continue
already_purchased = \
pending_action_value_sum(member_id=member.member_id, action_type=ProductAction.ADD_LABACCESS_DAYS) > 0
if already_purchased:
continue
logger.info(
f'sending labaccess reminder to member with id {member.member_id}')
send_message(
template=MessageTemplate.LABACCESS_REMINDER,
member=member,
db_session=db_session,
render_template=render_template,
expiration_date=end_date,
)
def already_sent_message(template: MessageTemplate, member: Member, days: int):
''' True if a message has been sent with the given template to the member in the last #days days'''
now = datetime.utcnow()
reminder_sent = db_session.query(Message).filter(
Message.member == member,
Message.template == template.value,
now - timedelta(days=days) < Message.created_at,
).count()
return reminder_sent > 0
def get_or_create(model, defaults=None, **kwargs):
entity = db_session.query(model).filter_by(**kwargs).first()
if entity:
return entity
entity = model(**{**kwargs, **defaults})
db_session.add(entity)
db_session.flush()
return entity
def create_message(self, data, commit=True):
# Validate and fetch recipients.
recipients = data.pop('recipients', [])
if not isinstance(recipients, list):
raise UnprocessableEntity("Recipients should be a list.")
member_ids = set()
for recipient in recipients:
type_ = recipient.get('type')
if type_ not in ('member', 'group'):
raise UnprocessableEntity(what=BAD_VALUE, message='Recipient type should be member or group')
try:
id_ = natural1(recipient.get('id'))
except (ValueError, TypeError):
raise UnprocessableEntity(what=BAD_VALUE, message=f'Recipient id should be positive int.')
if type_ == 'member':
member_ids.add(id_)
else:
member_ids.update(
{i for i, in db_session.query(member_group.c.member_id).filter(member_group.c.group_id == id_)}
)
members = db_session.query(Member).filter(Member.member_id.in_(member_ids)).all()
if len(members) != len(member_ids):
raise UnprocessableEntity('Recipient id is missing in the database.')
for member in members:
message = self._create_internal({
**data,
'recipient': member.email,
'member_id': member.member_id,
'status': 'queued'
}, commit=False)
if commit:
db_session.commit()
return message
def memberbooth_member(member_number=Arg(int)):
member = db_session.query(Member).filter(
Member.member_number == member_number,
Member.deleted_at.is_(None)).first()
if not member:
return None
membership_data = get_membership_summary(member.member_id)
return memberbooth_response_object(member, membership_data)
def list_for_user(user_id):
return [
dict(
access_token=access_token.access_token,
browser=access_token.browser,
ip=access_token.ip,
expires=access_token.expires.isoformat(),
) for access_token in db_session.query(AccessToken).filter(
AccessToken.user_id == user_id)
]
def test_reminder_message_is_not_created_if_membership_active(self):
member = self.db.create_member()
self.db.create_span(type=Span.MEMBERSHIP,
enddate=self.date(MEMBERSHIP_REMINDER_DAYS_BEFORE +
20))
self.send_membership()
self.assertEqual(
0,
db_session.query(Message).filter(Message.member == member).count())
def delete(self, entity_id, commit=True):
entity = db_session.query(self.model).get(entity_id)
if not entity:
raise NotFound(
"Could not find any entity with specified parameters.")
if not entity.deleted_at:
entity.deleted_at = datetime.utcnow()
if commit:
db_session.commit()
def create_token_with_permission(self, permission):
""" Return a logged in token that has a user with the given permission. """
member = self.db.create_member()
group = self.db.create_group()
group.members.append(member)
p = db_session.query(Permission).filter_by(
permission=permission).first()
group.permissions.append(p)
res = create_access_token("", "", member.member_id)
db_session.commit()
return res['access_token']
def get_product_data(product_id):
try:
product = db_session.query(Product).filter_by(id=product_id,
deleted_at=None).one()
except NoResultFound:
raise NotFound()
return {
"product": product_entity.to_obj(product),
"productData": all_product_data(),
}
def add_membership_days(member_id=None,
span_type=None,
days=None,
creation_reason=None,
default_start_date=None):
assert days >= 0
old_span = db_session.query(Span).filter_by(
creation_reason=creation_reason).first()
if old_span:
if days == (old_span.enddate -
old_span.startdate).days and span_type == old_span.type:
# Duplicate add days can happend because the code that handles the transactions is not yet done in a db
# transaction, there are also an external script for handling puchases in ticktail that can create
# dupllicates.
return get_membership_summary(member_id)
raise UnprocessableEntity(f"Duplicate entry.",
fields='creation_reason',
what=NOT_UNIQUE)
if not default_start_date:
default_start_date = date.today()
last_end, = db_session.query(func.max(Span.enddate)).filter(
Span.member_id == member_id, Span.type == span_type,
Span.deleted_at.is_(None)).first()
if not last_end or last_end < default_start_date:
last_end = default_start_date
end = last_end + timedelta(days=days)
span = Span(member_id=member_id,
startdate=last_end,
enddate=end,
type=span_type,
creation_reason=creation_reason)
db_session.add(span)
db_session.flush()
return get_membership_summary(member_id)
def list_service_tokens():
return [
dict(
user_id=access_token.user_id,
service_name=SERVICE_NAMES.get(access_token.user_id,
"unknown service"),
access_token=access_token.access_token,
permissions=",".join(
SERVICE_PERMISSIONS.get(access_token.user_id, [])),
) for access_token in db_session.query(AccessToken).filter(
AccessToken.user_id < 0)
]
def authenticate(username=None, password=None):
""" Authenticate a member trough username and password, returns member_id if authenticated, used from core. """
member = db_session.query(Member).filter_by(email=username).first()
if not member or not verify_password(password, member.password):
raise Unauthorized(
"The username and/or password you specified was incorrect.",
fields='username,password',
what=BAD_VALUE)
return member.member_id
def member_history(member_id):
query = (db_session.query(Transaction).options(
joinedload('contents'), joinedload('contents.product')).filter(
Transaction.member_id == member_id).order_by(desc(Transaction.id)))
return [{
**transaction_entity.to_obj(transaction), 'contents': [{
**transaction_content_entity.to_obj(content),
'product':
product_entity.to_obj(content.product),
} for content in transaction.contents]
} for transaction in query.all()]
def test_reminder_message_is_created_20_days_before_expiry(self):
member = self.db.create_member()
self.db.create_span(type=Span.LABACCESS,
enddate=self.date(LABACCESS_REMINDER_DAYS_BEFORE))
self.send_labaccess()
message, = db_session.query(Message).all()
self.assertEqual(member.member_id, message.member_id)
self.assertEqual(MessageTemplate.LABACCESS_REMINDER.value,
message.template)
def test_reminder_message_is_created_20_days_before_expiry_even_if_other_span_after(
self):
member = self.db.create_member()
self.db.create_span(type=Span.LABACCESS,
enddate=self.date(LABACCESS_REMINDER_DAYS_BEFORE))
self.db.create_span(type=Span.MEMBERSHIP, enddate=self.date(200))
self.send_labaccess()
self.assertEqual(
1,
db_session.query(Message).filter(Message.member == member).count())
