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())