class PushedMockTest(db.Model): __tablename__ = 'pushed_mock_tests' id = db.Column(db.Integer, primary_key=True) mock_test_id = db.Column(db.Integer, db.ForeignKey('mock_tests.id')) batch_id = db.Column(db.Integer, db.ForeignKey('batches.id')) pushed_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) expires_at = db.Column(db.DateTime)
class StudentBatches(db.Model): __tablename__ = 'student_batches' __table_args__ = (db.UniqueConstraint('student_id', 'batch_id'), ) id = db.Column(db.Integer, primary_key=True) batch_id = db.Column(db.Integer, db.ForeignKey('batches.id')) student_id = db.Column(db.Integer, db.ForeignKey('students.id')) joined_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) left_at = db.Column(db.DateTime)
class CategoryApproval(db.Model): __tablename__ = 'category_approvals' id = db.Column(db.Integer, primary_key=True) submission_id = db.Column( db.Integer, db.ForeignKey('category_submissions.id', ondelete='CASCADE')) approved_by_type = db.Column(db.Integer, db.ForeignKey('user_types.id')) approved_by_id = db.Column(db.Integer) approved_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) @classmethod def create(cls, submission_id, approved_by_type, approved_by_id): approval = cls(submission_id=submission_id, approved_by_type=approved_by_type, approved_by_id=approved_by_id) db.session.add(approval) db.session.commit()
class AttemptedMockTest(db.Model): __tablename__ = 'attempted_mock_tests' id = db.Column(db.Integer, primary_key=True) pushed_mock_test_id = db.Column( db.Integer, db.ForeignKey( 'pushed_mock_tests.id', ondelete="CASCADE")) # null when independently test chosen mock_test_id = db.Column( db.Integer, db.ForeignKey('mock_tests.id', ondelete="CASCADE")) student_id = db.Column(db.Integer, db.ForeignKey('students.id', ondelete="CASCADE")) answers = db.Column( JSON ) # pg json with question id as key and chosen options, time, is_correct, marks as values analysis = db.Column(JSON) # pg json with static analysis data score = db.Column(db.Float) attempted_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) pdf_report_url = db.Column(db.String)
class QuestionUploadSet(db.Model): __tablename__ = 'upload_sets' id = db.Column(db.Integer, primary_key=True) name = db.Column( db.String(app.config['QUESTION_UPLOAD_SET_NAME_MAX_LENGTH'])) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) errors_exist = db.Column(db.Boolean, default=False) mock_test_id = db.Column(db.Integer, db.ForeignKey('mock_tests.id')) questions_added = db.Column(db.Boolean, default=False) parsed_questions = db.Column(JSON) parsed_comprehensions = db.Column(JSON) @property def parsed_questions_decoded(self): questions = json.loads(self.parsed_questions) return questions @property def parsed_comprehensions_decoded(self): comprehensions = json.loads(self.parsed_comprehensions) return comprehensions @property def mock_test(self): mock_test = MockTest.query.get(self.mock_test_id) return mock_test @classmethod def create(cls, name, errors_exist, mock_test_id, parsed_questions, parsed_comprehensions): upload_set = cls(name=name, errors_exist=errors_exist, mock_test_id=mock_test_id, parsed_questions=parsed_questions, parsed_comprehensions=parsed_comprehensions) db.session.add(upload_set) db.session.commit() return upload_set @classmethod def delete(cls, id): upload_set = cls.query.get(id) if upload_set is not None: cls.query.filter_by(id=id).delete() db.session.commit()
class DataOperator(User, db.Model): __tablename__ = 'data_operators' type = db.Column(db.Integer, db.ForeignKey('user_types.id'), nullable=False) @classmethod def get_list(cls, page=1, limit=10): """ Get a list of active data operators with their stats :param page: :param limit: :return: """ result = {} pag_obj = cls.query.filter_by(is_active=True).paginate(page, limit) data_operators = pag_obj.items total = pag_obj.total if total == 0: return [], 0 for op in data_operators: result[op.id] = {'data_operator': op} data_operator_type = data_operators[0].type data_operator_ids = result.keys() ques_counts = db.session.query( Question.created_by_id, func.count(Question.id)).filter( Question.created_by_type == data_operator_type, Question.created_by_id.in_(data_operator_ids)).group_by( Question.created_by_id) for id, count in ques_counts: result[id]['questions_added'] = count text_sol_counts = db.session.query(Question.text_solution_by_id, func.count(Question.id)).filter( Question.text_solution_by_type == data_operator_type, Question.text_solution_by_id.in_(data_operator_ids))\ .group_by(Question.text_solution_by_id) for id, count in text_sol_counts: result[id]['text_solutions_added'] = count video_sol_counts = db.session.query(Question.video_solution_by_id, func.count(Question.id)).filter( Question.video_solution_by_type == data_operator_type, Question.video_solution_by_id.in_(data_operator_ids))\ .group_by(Question.video_solution_by_id) for id, count in video_sol_counts: result[id]['video_solutions_added'] = count return result.values(), total @classmethod def get(cls, id): """ Get a single data operator with his stats :param id: :return: """ data_operator = cls.query.get(id) if data_operator is None: raise InvalidDataOperatorId ques_count = Question.query.filter( Question.created_by_type == data_operator.type, Question.created_by_id == data_operator.id).count() text_sol_count = Question.query.filter( Question.text_solution_by_type == data_operator.type, Question.text_solution_by_id == data_operator.id).count() video_sol_count = Question.query.filter( Question.video_solution_by_type == data_operator.type, Question.video_solution_by_id == data_operator.id).count() return { 'data_operator': data_operator, 'questions_added': ques_count, 'text_solutions_added': text_sol_count, 'video_solutions_added': video_sol_count } @classmethod def create(cls, name, email, password): """ Create a data operator. If need encrypted password, then provide one. No encryption is done in this function. :param name: :param email: :param password: encrypted/unencrypted password :return: """ user_type = UserTypes.query.filter_by(name='data_operator').first() data_operator = cls(name=name, email=email, password=password, type=user_type.id) db.session.add(data_operator) try: db.session.commit() except IntegrityError: db.session.rolback() raise EmailAlreadyRegistered return data_operator
class Student(User, db.Model): __tablename__ = 'students' type = db.Column(db.Integer, db.ForeignKey('user_types.id'), nullable=False) mobile_no = db.Column(db.String(app.config['MOBILE_NO_MAX_LENGTH']), unique=True) city = db.Column(db.String(100)) area = db.Column(db.String(100)) pin = db.Column(db.String(10)) school = db.Column(db.String(100)) ntse_score = db.Column(db.Float) roll_no = db.Column(db.String(20)) branches = db.Column(ARRAY(db.String(1))) target_exams = db.Column(ARRAY(db.String(1))) target_exam_roll_nos = db.Column(ARRAY(db.String(100))) target_year = db.Column(db.Integer) father_name = db.Column(db.String(app.config['NAME_MAX_LENGTH'])) father_mobile_no = db.Column(db.String(app.config['MOBILE_NO_MAX_LENGTH'])) father_email = db.Column(db.String(app.config['EMAIL_MAX_LENGTH'])) payment_plan_id = db.Column(db.Integer, db.ForeignKey('payment_plans.id')) registered_from = db.Column(db.Enum('institute', 'independent', name='registered_from_enum')) fp_token = db.Column(db.String(64)) refcode = db.Column(db.String(200), nullable=True) @classmethod def create(cls, name, email, password, mobile_no=None, city=None, area=None, pin=None, school=None, ntse_score=None, roll_no=None, branches=None, target_exams=None, target_exam_roll_nos=None, target_year=None, father_name=None, father_mobile_no=None, father_email=None, payment_plan_id=None, registered_from=None, refcode=None): """ :param name: :param email: :param password: :param mobile_no: :param city: :param area: :param pin: :param school: :param ntse_score: :param roll_no: :param branches: :param target_exams: :param target_exam_roll_nos: :param target_year: :param father_name: :param father_email: :param father_mobile_no: :param payment_plan_id: :param registered_from: :return: """ user_type = UserTypes.query.filter_by(name='student').first() student = cls(name=name, email=email, password=password, mobile_no=mobile_no, city=city, area=area, pin=pin, school=school, ntse_score=ntse_score, roll_no=roll_no, branches=branches, target_exams=target_exams, target_exam_roll_nos=target_exam_roll_nos, target_year=target_year, father_name=father_name, father_mobile_no=father_mobile_no, payment_plan_id=payment_plan_id, registered_from=registered_from, type=user_type.id, refcode=refcode) db.session.add(student) try: db.session.commit() except IntegrityError as e: db.session.rollback() if 'email' in e.message: raise EmailAlreadyRegistered if 'mobile_no' in e.message: raise MobileNoAlreadyRegistered raise e return student @classmethod def get(cls, id): """ Get details of a single student :param id: student id :return: student object """ student = cls.query.get(id) if student is not None: return student else: raise InvalidStudentId @classmethod def get_list(cls, page=1, limit=10): """ Get a list of students :param page: :param limit: :return: """ students_pag_obj = cls.query.filter_by(is_active=True).paginate(page, limit) students = students_pag_obj.items total = students_pag_obj.total return students, total @classmethod def get_attempted_mock_tests(cls, id): """ :param id: :return: """ attempted_mock_tests = AttemptedMockTest.query.filter_by(student_id=id).all() return attempted_mock_tests @classmethod def get_pushed_mock_tests(cls, id): """ :param id: :return: """ # get batches that the student is currently part of student_current_batches = StudentBatches.query.filter(StudentBatches.student_id == id, StudentBatches.left_at == None).all() if len(student_current_batches) > 0: # get mock tests that are pushed to batches that the student is currently part of and have not expired batch_ids = [sb.batch_id for sb in student_current_batches] pushed_mock_tests = PushedMockTest.query.filter(PushedMockTest.batch_id.in_(batch_ids), or_(PushedMockTest.expires_at == None, PushedMockTest.expires_at > datetime.datetime.utcnow())).all() else: pushed_mock_tests = [] return pushed_mock_tests
class Question(db.Model): __tablename__ = 'questions' id = db.Column(db.Integer, primary_key=True) content = db.Column(db.Text, nullable=False) status = db.Column(MutableDict.as_mutable(HSTORE), default={}) # contains flags with values 0 and 1 comprehension_id = db.Column( db.Integer, db.ForeignKey('comprehensions.id', ondelete='CASCADE')) all_options = db.Column(ARRAY(db.Text), default=[]) correct_options = db.Column(ARRAY(db.Integer), default=[]) option_reasons = db.Column(ARRAY(db.Text), default=[]) average_time = db.Column(db.Integer) ontology = db.Column(ARRAY(db.Integer)) nature = db.Column( db.Enum(*app.config['QUESTION_NATURE'].keys(), name='question_nature_enum')) difficulty = db.Column( db.Enum(*app.config['QUESTION_DIFFICULTY_LEVEL'], name='question_difficulty_enum')) type = db.Column( db.Enum(*app.config['QUESTION_TYPE'], name='question_type_enum')) text_solution = db.Column(db.Text) # null until approved video_solution_url = db.Column(db.String( app.config['URL_MAX_LENGTH'])) # null until approved text_solution_by_type = db.Column(db.Integer, db.ForeignKey('user_types.id')) text_solution_by_id = db.Column(db.Integer) video_solution_by_type = db.Column(db.Integer, db.ForeignKey('user_types.id')) video_solution_by_id = db.Column(db.Integer) similar_question_ids = db.Column(ARRAY(db.Integer)) # null if unfilled created_by_type = db.Column(db.Integer, db.ForeignKey('user_types.id')) created_by_id = db.Column(db.Integer) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) is_similarity_marked = db.Column(db.Boolean, default=False) @validates('video_solution_url') def validate_video_url(self, key, url): if url is None: return url parsed_url = urlparse.urlparse(url) netloc = parsed_url.netloc if netloc not in ('youtube.com', 'www.youtube.com', 'youtu.be', 'www.youtu.be'): raise UnAcceptableVideoUrl return url img_base64_pat = re.compile('<img [^>]*?src="(data:image.*?)".*?>') @classmethod def parse_content(cls, content): """ Parse and return the content. Convert base64 images to s3 image urls. :param content: :return: string """ match = cls.img_base64_pat.search(content) s3obj = S3() while match is not None: mimetype, image_data = parse_base64_string(match.groups()[0]) url = s3obj.upload(image_data, mimetype) img_tag = '<img src="%s" />' % url content = content[:match.start()] + img_tag + content[match.end():] match = cls.img_base64_pat.search(content) return content @classmethod def create(cls, content, status, comprehension_id=None, all_options=None, correct_options=None, option_reasons=None, ontology_id=None, average_time=app.config['DEFAULT_AVERAGE_TIME'], nature=None, difficulty=None, type=None, text_solution=None, video_solution_url=None, text_solution_by_type=None, text_solution_by_id=None, video_solution_by_type=None, video_solution_by_id=None, similar_question_ids=None, created_by_type=None, created_by_id=None): """ Create a new question and return the newly created object. :param content: question test :param status: status dict :param comprehension_id: if question is part of comprehension :param all_options: :param correct_options: indices of correct options :param option_reasons: :param ontology_id: :param average_time: :param nature: :param difficulty: :param type: :param text_solution: :param video_solution_url: :param text_solution_by_type: the type of user if text solution was provided :param text_solution_by_id: the id of user if text solution was provided :param video_solution_by_type: the type of user if video solution was provided :param video_solution_by_id: the id of user if video solution was provided :param similar_question_ids: array of question ids :param created_by_type: the type of user who entered this question :param created_by_id: the id of user who entered this question :return: """ if ontology_id is not None: ontology_obj = Ontology.query.get(ontology_id) if ontology_obj is None: raise InvalidOntologyNodeId ontology = ontology_obj.absolute_path else: ontology = None content = content.strip() content = cls.parse_content(content) if all_options is not None: for i, option in enumerate(all_options): if option is not None: all_options[i] = cls.parse_content(option) if option_reasons is not None: for i, reason in enumerate(option_reasons): if reason is not None: option_reasons[i] = cls.parse_content(reason) if text_solution is not None: text_solution = cls.parse_content(text_solution) question = cls(content=content, status=status, comprehension_id=comprehension_id, all_options=all_options, correct_options=correct_options, option_reasons=option_reasons, ontology=ontology, average_time=average_time, nature=nature, difficulty=difficulty, type=type, text_solution=text_solution, video_solution_url=video_solution_url, text_solution_by_type=text_solution_by_type, text_solution_by_id=text_solution_by_id, video_solution_by_type=video_solution_by_type, video_solution_by_id=video_solution_by_id, similar_question_ids=similar_question_ids, created_by_type=created_by_type, created_by_id=created_by_id) db.session.add(question) db.session.commit() return question @classmethod def get(cls, id): """ Get details of a single question :param id: question id :return: question object """ question = cls.query.get(id) if question is not None: if question.text_solution is not None or question.video_solution_url is not None: ss = SolutionSubmission.query.filter_by( question_id=question.id).all() for item in ss: if item.solution_type == 'video': video_submission = item if item.solution_type == 'text': text_submission = item if question.text_solution is not None: question.text_solution_by_type = text_submission.submitted_by_type question.text_solution_by_id = text_submission.submitted_by_id if question.video_solution_url is not None: question.video_solution_by_type = video_submission.submitted_by_type question.video_solution_by_id = video_submission.submitted_by_id return question else: raise InvalidQuestionId def update(self, content, status, comprehension_id=None, all_options=None, correct_options=None, option_reasons=None, ontology_id=None, average_time=app.config['DEFAULT_AVERAGE_TIME'], nature=None, difficulty=None, type=None, text_solution=None, video_solution_url=None, text_solution_by_type=None, text_solution_by_id=None, video_solution_by_type=None, video_solution_by_id=None, similar_question_ids=None): """ Update the details of single question :param id: question_id :param content: :param status: status dict :param comprehension_id: if question is part of comprehension :param all_options: :param correct_options: indices of correct options :param option_reasons: :param ontology_id: :param average_time: :param nature: :param difficulty: :param type: :param text_solution: :param video_solution_url: :param text_solution_by_type: the type of user if text solution was provided :param text_solution_by_id: the id of user if text solution was provided :param video_solution_by_type: the type of user if video solution was provided :param video_solution_by_id: the id of user if video solution was provided :param similar_question_ids: array of question ids :return: """ if ontology_id is not None: ontology_obj = Ontology.query.get(ontology_id) if ontology_obj is None: raise InvalidOntologyNodeId ontology = ontology_obj.absolute_path else: ontology = None content = self.__class__.parse_content(content) if all_options is not None: for i, option in enumerate(all_options): if option is not None: all_options[i] = self.__class__.parse_content(option) if option_reasons is not None: for i, reason in enumerate(option_reasons): if reason is not None: option_reasons[i] = self.__class__.parse_content(reason) if text_solution is not None: text_solution = self.__class__.parse_content(text_solution) question = self question.content = content question.status = status question.comprehension_id = comprehension_id question.all_options = all_options question.correct_options = correct_options question.option_reasons = option_reasons question.ontology = ontology question.average_time = average_time question.nature = nature question.difficulty = difficulty question.type = type question.text_solution = text_solution question.video_solution_url = video_solution_url question.text_solution_by_type = text_solution_by_type question.text_solution_by_id = text_solution_by_id question.video_solution_by_type = video_solution_by_type question.video_solution_by_id = video_solution_by_id question.similar_question_ids = similar_question_ids db.session.commit() return question @classmethod def get_filtertered_list(cls, nature=None, type=None, difficulty=None, average_time=None, categorized=None, proof_read_categorization=None, proof_read_text_solution=None, proof_read_video_solution=None, finalized=None, error_reported=None, text_solution_added=None, video_solution_added=None, ontology=None, is_comprehension=None, similar_questions_to=None, not_similar_questions_to=None, exclude_question_ids=None, include_question_ids=None, page=1, limit=None): """ Get a list of question after applying filters :param nature: :param type: :param difficulty: :param average_time: :param categorized: :param proof_read_categorization: :param proof_read_text_solution: :param proof_read_video_solution: :param finalized: :param text_solution_added: :param video_solution_added: :param ontology: :param is_comprehension: :param similar_questions_to: questions which are similar to the question id :param not_similar_questions_to: questions which are not similar to the question id :param exclude_question_ids: dont return questions with these ids :param include_question_ids: return questions with these ids :param page: :param limit: :return: """ exprs = [] if nature is not None: exprs.append(Question.nature == nature) if type is not None: exprs.append(Question.type == type) if difficulty is not None: exprs.append(Question.difficulty == difficulty) if average_time is not None: exprs.append(Question.average_time == average_time) if categorized is not None: exprs.append(Question.status['categorized'] == categorized) if proof_read_categorization is not None: exprs.append(Question.status['proof_read_categorization'] == proof_read_categorization) if proof_read_text_solution is not None: exprs.append(Question.status['proof_read_text_solution'] == proof_read_text_solution) if proof_read_video_solution is not None: exprs.append(Question.status['proof_read_video_solution'] == proof_read_video_solution) if finalized is not None: exprs.append(Question.status['finalized'] == finalized) if error_reported is not None: exprs.append(Question.status['error_reported'] == error_reported) if ontology is not None: exprs.append(Question.ontology.contains(ontology)) if is_comprehension is not None: if is_comprehension == 0: exprs.append(Question.comprehension_id == None) else: exprs.append(Question.comprehension_id != None) if text_solution_added is not None: exprs.append(Question.status['text_solution_added'] == str( text_solution_added)) if video_solution_added is not None: exprs.append(Question.status['video_solution_added'] == str( video_solution_added)) if exclude_question_ids is not None: exprs.append(not_(Question.id.in_(exclude_question_ids))) if include_question_ids is not None: exprs.append(Question.id.in_(include_question_ids)) if similar_questions_to is not None or not_similar_questions_to is not None: # Need to select only categorized questions categorized_expr = Question.status['categorized'] == '1' if categorized_expr not in exprs: exprs.append(categorized_expr) if similar_questions_to is not None: # questions need to be compared only when ontology matches exactly ques = cls.get(similar_questions_to) exprs.append(Question.ontology == ques.ontology) # exclude itself exprs.append(Question.id != ques.id) exprs.append( Question.similar_question_ids.any(similar_questions_to)) if not_similar_questions_to is not None: # questions need to be compared only when ontology matches exactly ques = cls.get(not_similar_questions_to) exprs.append(Question.ontology == ques.ontology) # exclude itself exprs.append(Question.id != ques.id) exprs.append( or_( not_( Question.similar_question_ids.any( not_similar_questions_to)), Question.similar_question_ids == None)) if limit is not None: questions_pag_obj = Question.query.filter(*exprs).order_by( Question.created_at.desc()).paginate(page, limit) questions = questions_pag_obj.items total = questions_pag_obj.total else: q = Question.query.filter(*exprs).order_by( Question.created_at.desc()) questions = q.all() total = len(questions) question_dict = OrderedDict() for q in questions: question_dict[q.id] = q submissions = SolutionSubmission.query.filter( SolutionSubmission.question_id.in_(question_dict.keys())).all() for item in submissions: question = question_dict[item.question_id] if item.solution_type == 'text' and question.text_solution is not None: question.text_solution_by_type = item.submitted_by_type question.text_solution_by_id = item.submitted_by_id if item.solution_type == 'video' and question.video_solution_url is not None: question.video_solution_by_type = item.submitted_by_type question.video_solution_by_id = item.submitted_by_id return {'questions': question_dict.values(), 'total': total} @classmethod def reset_categorization(cls, id): """ Reset the categorization flag and categorization fields :param id: :return: """ question = cls.query.get(id) question.status['categorized'] = '0' question.status['proof_read_categorization'] = '0' question.status['finalized'] = '0' if question.ontology is not None: question.ontology = question.ontology[0:1] question.average_time = None question.nature = None question.difficulty = None question.type = None db.session.commit() return question @classmethod def approve_categorization(cls, id): """ Approve the categorization. Set the categorization flag and proof read categorization flag :param id: :return: """ question = cls.query.get(id) question.status['categorized'] = '1' question.status['proof_read_categorization'] = '1' if '0' not in (question.status['proof_read_categorization'], question.status['proof_read_text_solution'], question.status['proof_read_video_solution']): # question has been proof read in all 3 respects so its finalized question.status['finalized'] = '1' db.session.commit() return question @classmethod def reset_solution(cls, id, sol_type): """ Reset the solution flags and solution field :param id: :param sol_type: Solution type. Can be text or video :return: """ question = cls.query.get(id) if sol_type == 'text': question.status['text_solution_added'] = '0' question.status['proof_read_text_solution'] = '0' question.text_solution = None question.text_solution_by_type = None question.text_solution_by_id = None if sol_type == 'video': question.status['video_solution_added'] = '0' question.status['proof_read_video_solution'] = '0' question.video_solution_url = None question.video_solution_by_type = None question.video_solution_by_id = None question.status['finalized'] = '0' db.session.commit() return question @classmethod def approve_solution(cls, id, sol_type): """ Approve the solution. Set the solution flag and proof read solution flag :param id: :param sol_type: Solution type. Can be text or video :return: """ question = cls.query.get(id) if sol_type == 'text': question.status['text_solution_added'] = '1' question.status['proof_read_text_solution'] = '1' if sol_type == 'video': question.status['video_solution_added'] = '1' question.status['proof_read_video_solution'] = '1' if '0' not in (question.status['proof_read_categorization'], question.status['proof_read_text_solution'], question.status['proof_read_video_solution']): # question has been proof read in all 3 respects so its finalized question.status['finalized'] = '1' db.session.commit() return question
class MockTest(db.Model): __tablename__ = 'mock_tests' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(app.config['TEST_NAME_MAX_LENGTH'])) created_by_type = db.Column(db.Integer, db.ForeignKey('user_types.id')) created_by_id = db.Column(db.Integer) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) question_ids = db.Column(JSON) target_exam = db.Column( db.Enum(*app.config['TARGET_EXAMS'].keys(), name='target_exams_enum')) difficulty = db.Column( db.Enum(*app.config['MOCK_TEST_DIFFICULTY_LEVEL'].keys(), name='mock_test_difficulty_enum')) description = db.Column(db.Text) for_institutes = db.Column(db.Boolean, default=False) is_locked = db.Column(db.Boolean, default=False) type = db.Column( db.Enum(*app.config['MOCK_TEST_TYPES'].keys(), name='mock_test_types_enum')) type_id = db.Column(db.Integer, db.ForeignKey('ontology.id', ondelete='CASCADE')) prerequisite_id = db.Column( db.Integer, db.ForeignKey('mock_tests.id', ondelete='CASCADE')) duration = db.Column(db.Integer) syllabus = db.Column(db.Text) cutoff = db.Column(db.Float) date_closed = db.Column( db.Boolean, default=False) # will the test open on a date or is it by default open opening_date = db.Column(db.Date, nullable=True) @classmethod def create(cls, name, difficulty, target_exam, for_institutes, question_ids, type, type_id, prerequisite_id, duration, created_by_type, created_by_id, date_closed, description=None, syllabus=None, cutoff=None, opening_date=None): mock_test = cls(name=name, difficulty=difficulty, target_exam=target_exam, for_institutes=for_institutes, question_ids=question_ids, description=description, syllabus=syllabus, type=type, type_id=type_id, prerequisite_id=prerequisite_id, duration=duration, cutoff=cutoff, created_by_type=created_by_type, created_by_id=created_by_id, date_closed=date_closed, opening_date=opening_date) db.session.add(mock_test) db.session.commit() return mock_test
class Teacher(User, db.Model): __tablename__ = 'teachers' type = db.Column(db.Integer, db.ForeignKey('user_types.id'), nullable=False) subject_expert = db.Column(db.String(100)) specialization = db.Column(db.String(100)) qualification = db.Column(db.String(100)) @classmethod def create(cls, name, email, password, subject_expert=None, specialization=None, qualification=None): """ Create a new teacher. If need encrypted password, then provide one. No encryption is done in this function. :param name: :param email: :param password: :param subject_expert: :param specialization: :param qualification: :return: """ user_type = UserTypes.query.filter_by(name='teacher').first() teacher = cls(name=name, email=email, password=password, subject_expert=subject_expert, specialization=specialization, qualification=qualification, type=user_type.id) db.session.add(teacher) try: db.session.commit() except IntegrityError: db.session.rollback() raise EmailAlreadyRegistered return teacher @classmethod def get_list(cls, page=1, limit=10): """ Get a list of active teacher with their stats :param page: :param limit: :return: """ result = {} pag_obj = cls.query.filter_by(is_active=True).paginate(page, limit) teachers = pag_obj.items total = pag_obj.total if total == 0: return [], 0 for op in teachers: result[op.id] = {'teacher': op} teacher_type = teachers[0].type teacher_ids = result.keys() reported_resolved_count = db.session.query( ReportedQuestion.resolved_by_id, func.count(ReportedQuestion.id)).filter( ReportedQuestion.resolved_by_type == teacher_type, ReportedQuestion.resolved_by_id.in_(teacher_ids), ReportedQuestion.is_resolved == True).group_by( ReportedQuestion.resolved_by_id) for id, count in reported_resolved_count: result[id]['reported_resolved'] = count cat_sub_counts = db.session.query(CategorySubmission.submitted_by_id, func.count(CategorySubmission.id)).filter( CategorySubmission.submitted_by_type == teacher_type, CategorySubmission.submitted_by_id.in_(teacher_ids))\ .group_by(CategorySubmission.submitted_by_id) for id, count in cat_sub_counts: result[id]['questions_categorized'] = count cat_app_counts = db.session.query(CategoryApproval.approved_by_id, func.count(CategoryApproval.id))\ .filter(CategoryApproval.approved_by_id.in_(teacher_ids), CategoryApproval.approved_by_type == teacher_type).group_by(CategoryApproval.approved_by_id) for id, count in cat_app_counts: result[id]['questions_approved'] = count sol_counts = db.session.query(SolutionSubmission.submitted_by_id, SolutionSubmission.solution_type, func.count(SolutionSubmission.id))\ .filter(SolutionSubmission.submitted_by_type == teacher_type, SolutionSubmission.submitted_by_id.in_(teacher_ids))\ .group_by(SolutionSubmission.submitted_by_id, SolutionSubmission.solution_type) for id, sol_type, count in sol_counts: if sol_type == 'text': result[id]['text_solutions_submitted'] = count if sol_type == 'video': result[id]['video_solutions_submitted'] = count sol_app_counts = db.session.query(SolutionApproval.approved_by_id, SolutionSubmission.solution_type, func.count(SolutionApproval.id))\ .filter(SolutionApproval.approved_by_type == teacher_type, SolutionApproval.approved_by_id.in_(teacher_ids), SolutionApproval.submission_id == SolutionSubmission.id).group_by(SolutionApproval.approved_by_id, SolutionSubmission.solution_type) for id, sol_type, count in sol_app_counts: if sol_type == 'text': result[id]['text_solutions_approved'] = count if sol_type == 'video': result[id]['video_solutions_approved'] = count return result.values(), total @classmethod def get(cls, id): """ Get a single teacher with his stats :param id: :return: """ teacher = cls.query.get(id) if teacher is None: raise InvalidTeacherId reported_resolved = db.session.query(func.count( ReportedQuestion.id)).filter( ReportedQuestion.is_resolved == True, ReportedQuestion.resolved_by_type == teacher.type, ReportedQuestion.resolved_by_id == teacher.id).first()[0] cat_sub_count = db.session.query(func.count( CategorySubmission.id)).filter( CategorySubmission.submitted_by_type == teacher.type, CategorySubmission.submitted_by_id == teacher.id).first()[0] cat_app_count = db.session.query(func.count(CategoryApproval.id))\ .filter(CategorySubmission.submitted_by_type == teacher.type, CategorySubmission.submitted_by_id == teacher.id, CategoryApproval.submission_id == CategorySubmission.id).first()[0] sol_counts = db.session.query(SolutionSubmission.solution_type, func.count(SolutionSubmission.id))\ .filter(SolutionSubmission.submitted_by_type == teacher.type, SolutionSubmission.submitted_by_id == teacher.id)\ .group_by(SolutionSubmission.solution_type) text_solutions_submitted = 0 video_solutions_submitted = 0 for sol_type, count in sol_counts: if sol_type == 'text': text_solutions_submitted = count if sol_type == 'video': video_solutions_submitted = count sol_app_counts = db.session.query(SolutionSubmission.solution_type, func.count(SolutionApproval.id))\ .filter(CategorySubmission.submitted_by_type == teacher.type, CategorySubmission.submitted_by_id == teacher.id, SolutionApproval.submission_id == SolutionSubmission.id)\ .group_by(SolutionSubmission.solution_type) text_solutions_approved = 0 video_solutions_approved = 0 for sol_type, count in sol_app_counts: if sol_type == 'text': text_solutions_approved = count if sol_type == 'video': video_solutions_approved = count return { 'teacher': teacher, 'questions_categorized': cat_sub_count, 'questions_approved': cat_app_count, 'text_solutions_submitted': text_solutions_submitted, 'video_solutions_submitted': video_solutions_submitted, 'text_solutions_approved': text_solutions_approved, 'video_solutions_approved': video_solutions_approved, 'reported_resolved': reported_resolved }
class CategorySubmission(db.Model): __tablename__ = 'category_submissions' id = db.Column(db.Integer, primary_key=True) submitted_by_type = db.Column(db.Integer, db.ForeignKey('user_types.id')) submitted_by_id = db.Column(db.Integer) # JSON in the form of {"ontology": [1,10,13], "type": "", "nature": , ...} category = db.Column(JSON) question_id = db.Column(db.Integer, db.ForeignKey('questions.id', ondelete='CASCADE')) submitted_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) status = db.Column( db.Enum(*app.config['SUBMISSION_STATUS'], name='submission_status_enum')) @classmethod def create(cls, submitted_by_type, submitted_by_id, question_id, ontology=None, nature=None, type=None, difficulty=None, average_time=None, status=None): """ Create a new categorization submission :param submitted_by_type: the user type who submitted :param submitted_by_id: the user id who submitted :param question_id: the question id this submission corresponds too :param ontology: the ontology node's complete path :param type: :param difficulty: :param average_time: :param status: :return: """ category = { 'ontology': ontology, 'type': type, 'difficulty': difficulty, 'average_time': average_time, 'nature': nature } submission = cls(submitted_by_type=submitted_by_type, submitted_by_id=submitted_by_id, question_id=question_id, category=json.dumps(category), status=status) db.session.add(submission) db.session.commit() return submission @classmethod def get(cls, nature=None, type=None, difficulty=None, average_time=None, ontology=None, page=1, limit=10): """ Get filtered category submissions :param nature: :param type: :param difficulty: :param average_time: :param ontology: :return: """ data = Question.get_filtertered_list(nature=nature, type=type, difficulty=difficulty, average_time=average_time, ontology=ontology, categorized='1', proof_read_categorization='0', page=page, limit=limit) if data['total'] > 0: temp = OrderedDict() for q in data['questions']: temp[q.id] = {'question': q} submissions = cls.query.filter(cls.question_id.in_(temp.keys()), cls.status == None).all() for submission in submissions: temp[submission.question_id]['submission_id'] = submission.id return temp.values(), data['total'] else: return [], 0
class ReportedQuestion(db.Model): __tablename__ = 'reported_questions' id = db.Column(db.Integer, primary_key=True) reported_by_type = db.Column(db.Integer, db.ForeignKey('user_types.id')) reported_by_id = db.Column(db.Integer) is_resolved = db.Column(db.Boolean, default=False) question_id = db.Column(db.Integer, db.ForeignKey('questions.id', ondelete='CASCADE')) reported_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) resolved_by_type = db.Column(db.Integer, db.ForeignKey('user_types.id')) resolved_by_id = db.Column(db.Integer) resolved_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) @classmethod def create(cls, reported_by_type, reported_by_id, question_id): """ Report a question :param reported_by_type: :param reported_by_id: :param question_id: :return: """ report = cls(reported_by_type=reported_by_type, reported_by_id=reported_by_id, question_id=question_id) question = Question.query.get(question_id) question.status['error_reported'] = '1' db.session.add(report) db.session.commit() return report @classmethod def get(cls, nature=None, type=None, difficulty=None, average_time=None, ontology=None, page=1, limit=10): """ Get reported questions :param nature: :param type: :param difficulty: :param average_time: :param ontology: :param page: :param limit: :return: """ exprs = [] if nature is not None: exprs.append(Question.nature == nature) if type is not None: exprs.append(Question.type == type) if difficulty is not None: exprs.append(Question.difficulty == difficulty) if average_time is not None: exprs.append(Question.average_time == average_time) if ontology is not None: exprs.append(Question.ontology.contains(ontology)) result = db.session.query(cls, Question).filter(cls.is_resolved == False, cls.question_id == Question.id, *exprs).order_by(Question.created_at.desc()) total = result.count() reported_questions = [] for row in result.offset((page-1)*limit).limit(limit): reported_questions.append({ 'question': row[1], 'report_id': row[0].id }) return reported_questions, total @classmethod def delete(cls, id): report = cls.query.get(id) if report is not None: Question.query.filter_by(id=report.question_id).delete() cls.query.filter_by(id=id).delete() db.session.commit()
class Intern(User, db.Model): __tablename__ = 'interns' type = db.Column(db.Integer, db.ForeignKey('user_types.id'), nullable=False) @classmethod def get_list(cls, page=1, limit=10): """ Get a list of active interns with their stats :param page: :param limit: :return: """ result = {} pag_obj = cls.query.filter_by(is_active=True).paginate(page, limit) interns = pag_obj.items total = pag_obj.total if total == 0: return [], 0 for intern in interns: result[intern.id] = {'intern': intern} intern_type = interns[0].type intern_ids = result.keys() reported_count = db.session.query(ReportedQuestion.reported_by_id, func.count(ReportedQuestion.id)).filter( ReportedQuestion.reported_by_type == intern_type, ReportedQuestion.reported_by_id.in_(intern_ids))\ .group_by(ReportedQuestion.reported_by_id) for id, count in reported_count: result[id]['reported_questions'] = count cat_sub_counts = db.session.query(CategorySubmission.submitted_by_id, func.count(CategorySubmission.id)).filter( CategorySubmission.submitted_by_type == intern_type, CategorySubmission.submitted_by_id.in_(intern_ids))\ .group_by(CategorySubmission.submitted_by_id) for id, count in cat_sub_counts: result[id]['questions_categorized'] = count cat_app_counts = db.session.query(CategorySubmission.submitted_by_id, func.count(CategoryApproval.id))\ .filter(CategorySubmission.submitted_by_type == intern_type, CategorySubmission.submitted_by_id .in_(intern_ids), CategoryApproval.submission_id == CategorySubmission.id).group_by(CategorySubmission.submitted_by_id) for id, count in cat_app_counts: result[id]['questions_approved'] = count sol_counts = db.session.query(SolutionSubmission.submitted_by_id, SolutionSubmission.solution_type, func.count(SolutionSubmission.id))\ .filter(SolutionSubmission.submitted_by_type == intern_type, SolutionSubmission.submitted_by_id.in_(intern_ids))\ .group_by(SolutionSubmission.submitted_by_id, SolutionSubmission.solution_type) for id, sol_type, count in sol_counts: if sol_type == 'text': result[id]['text_solutions_submitted'] = count if sol_type == 'video': result[id]['video_solutions_submitted'] = count sol_app_counts = db.session.query(SolutionSubmission.submitted_by_id, SolutionSubmission.solution_type, func.count(SolutionApproval.id))\ .filter(SolutionSubmission.submitted_by_type == intern_type, SolutionSubmission.submitted_by_id.in_(intern_ids), SolutionApproval.submission_id == SolutionSubmission.id)\ .group_by(SolutionSubmission.submitted_by_id, SolutionSubmission.solution_type) for id, sol_type, count in sol_app_counts: if sol_type == 'text': result[id]['text_solutions_approved'] = count if sol_type == 'video': result[id]['video_solutions_approved'] = count return result.values(), total @classmethod def create(cls, name, email, password): """ Create an intern. If need encrypted password, then provide one. No encryption is done in this function. :param name: :param email: :param password: encrypted/unencrypted password :return: """ user_type = UserTypes.query.filter_by(name='intern').first() intern = cls(name=name, email=email, password=password, type=user_type.id) db.session.add(intern) try: db.session.commit() except IntegrityError: db.session.rolback() raise EmailAlreadyRegistered return intern @classmethod def get(cls, id): """ Get a single teacher with his stats :param id: :return: """ intern = cls.query.get(id) if intern is None: raise InvalidInternId reported_count = db.session.query(func.count( ReportedQuestion.id)).filter( ReportedQuestion.reported_by_type == intern.type, ReportedQuestion.reported_by_id == intern.id).first()[0] cat_sub_count = db.session.query(func.count( CategorySubmission.id)).filter( CategorySubmission.submitted_by_type == intern.type, CategorySubmission.submitted_by_id == intern.id).first()[0] cat_app_count = db.session.query(func.count(CategoryApproval.id))\ .filter(CategorySubmission.submitted_by_type == intern.type, CategorySubmission.submitted_by_id == intern.id, CategoryApproval.submission_id == CategorySubmission.id).first()[0] sol_counts = db.session.query(SolutionSubmission.solution_type, func.count(SolutionSubmission.id))\ .filter(SolutionSubmission.submitted_by_type == intern.type, SolutionSubmission.submitted_by_id == intern.id)\ .group_by(SolutionSubmission.solution_type) text_solutions_submitted = 0 video_solutions_submitted = 0 for sol_type, count in sol_counts: if sol_type == 'text': text_solutions_submitted = count if sol_type == 'video': video_solutions_submitted = count sol_app_counts = db.session.query(SolutionSubmission.solution_type, func.count(SolutionApproval.id))\ .filter(CategorySubmission.submitted_by_type == intern.type, CategorySubmission.submitted_by_id == intern.id, SolutionApproval.submission_id == SolutionSubmission.id)\ .group_by(SolutionSubmission.solution_type) text_solutions_approved = 0 video_solutions_approved = 0 for sol_type, count in sol_app_counts: if sol_type == 'text': text_solutions_approved = count if sol_type == 'video': video_solutions_approved = count return { 'intern': intern, 'reported_questions': reported_count, 'questions_categorized': cat_sub_count, 'questions_approved': cat_app_count, 'text_solutions_submitted': text_solutions_submitted, 'video_solutions_submitted': video_solutions_submitted, 'text_solutions_approved': text_solutions_approved, 'video_solutions_approved': video_solutions_approved }
class Batch(db.Model): __tablename__ = 'batches' __table_args__ = (db.UniqueConstraint('name', 'institute_id'), ) id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(app.config['NAME_MAX_LENGTH'])) on_weekdays = db.Column(db.Boolean, default=False) on_weekends = db.Column(db.Boolean, default=False) clazz = db.Column(db.Enum('11', '12', name='classes_enum')) target_year = db.Column(db.Integer) type = db.Column(db.Enum(*app.config['BATCH_TYPE'].keys(), name='batch_types_enum')) target_exam = db.Column(db.Enum(*app.config['TARGET_EXAMS'].keys(), name='target_exams_enum')) other = db.Column(db.Text) batch_timings = db.Column(db.String(20)) institute_id = db.Column(db.Integer, db.ForeignKey('institutes.id')) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) status = db.Column(db.Integer, default=1) @classmethod def create(cls, name, on_weekdays, on_weekends, clazz, target_year, target_exam, type, other, batch_timings, institute_id): """ Create a new batch :param name: :param on_weekdays: :param on_weekends: :param clazz: :param target_year: :param target_exam: :param type: :param other: some text about batch :param batch_timings: string in the form ``h1:m1-h2:m2`` :param institute_id: :return: """ batch = cls(name=name, on_weekdays=on_weekdays, on_weekends=on_weekends, clazz=clazz, target_year=target_year, target_exam=target_exam, type=type, other=other, batch_timings=batch_timings, institute_id=institute_id) db.session.add(batch) try: db.session.commit() except IntegrityError: db.session.rollback() raise BatchNameAlreadyTaken return batch @classmethod def get(cls, id): """ Get a single batch :param id: :return: """ batch = cls.query.get(id) if batch is None: raise InvalidBatchId return batch @classmethod def get_filtered(cls, days=None, type=None, target_year=None, target_exam=None, include_ids=None, institute_id=None, status=None, branches=None): """ Get a list of batches after applying filters :param days: :param type: :param target_year: :param target_exam: :param include_ids: :param institute_id: :param status: :param target_exam_list: :return: """ exprs = [] if days is not None: if days == 'weekdays': exprs.append(Batch.on_weekdays == True) if days == 'weekends': exprs.append(Batch.on_weekends == True) if type is not None: exprs.append(Batch.type == type) if target_year is not None: exprs.append(Batch.target_year == target_year) if target_exam is not None: exprs.append(Batch.target_exam == target_exam) if institute_id is not None: exprs.append(Batch.institute_id == institute_id) if include_ids is not None and (isinstance(include_ids, list) or isinstance(include_ids, tuple) or isinstance(include_ids, set)): exprs.append(Batch.id.in_(list(include_ids))) if branches is not None and (isinstance(branches, list) or isinstance(branches, tuple) or isinstance(branches, set)): target_exam_list = [] engineering_exams = ['1', '2', '3'] medical_exams = ['4', '5'] if '1' in branches: target_exam_list.extend(engineering_exams) if '2' in branches: target_exam_list.extend(medical_exams) exprs.append(Batch.target_exam.in_(list(target_exam_list))) if status is None: status = 1 # explicitly ignoring status if status != -1: exprs.append(Batch.status == status) return Batch.query.filter(*exprs).order_by(Batch.created_at.desc()).all()
class SolutionSubmission(db.Model): __tablename__ = 'solution_submissions' id = db.Column(db.Integer, primary_key=True) submitted_by_type = db.Column(db.Integer, db.ForeignKey('user_types.id')) submitted_by_id = db.Column(db.Integer) solution_type = db.Column( db.Enum('text', 'video', name='solution_types_enum')) solution = db.Column(db.Text) question_id = db.Column(db.Integer, db.ForeignKey('questions.id', ondelete='CASCADE')) submitted_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) status = db.Column( db.Enum(*app.config['SUBMISSION_STATUS'], name='submission_status_enum')) @classmethod def create(cls, submitted_by_type, submitted_by_id, question_id, solution_type, solution, status=None): """ Create a new solution submission :param submitted_by_type: the user type who submitted :param submitted_by_id: the user id who submitted :param question_id: the question id this submission corresponds too :param solution_type: text/video :param solution: :param status: :return: """ submission = cls(submitted_by_type=submitted_by_type, submitted_by_id=submitted_by_id, question_id=question_id, solution_type=solution_type, solution=solution, status=status) db.session.add(submission) db.session.commit() return submission @classmethod def get(cls, nature=None, type=None, difficulty=None, average_time=None, ontology=None, sol_type='text', page=1, limit=10): """ Get filtered category submissions :param nature: :param type: :param difficulty: :param average_time: :param ontology: :return: """ if sol_type == 'text': data = Question.get_filtertered_list(nature=nature, type=type, difficulty=difficulty, average_time=average_time, ontology=ontology, text_solution_added='1', proof_read_text_solution='0', page=page, limit=limit) else: data = Question.get_filtertered_list(nature=nature, type=type, difficulty=difficulty, average_time=average_time, ontology=ontology, video_solution_added='1', proof_read_video_solution='0', page=page, limit=limit) if data['total'] > 0: temp = OrderedDict() for q in data['questions']: temp[q.id] = {'question': q} submissions = cls.query.filter( cls.question_id.in_(temp.keys()), cls.status == None, cls.solution_type == sol_type).all() for submission in submissions: temp[submission.question_id]['submission_id'] = submission.id return temp.values(), data['total'] else: return [], 0
class Institute(User, db.Model): __tablename__ = 'institutes' type = db.Column(db.Integer, db.ForeignKey('user_types.id'), nullable=False) location = db.Column(db.String(100)) logo_url = db.Column(db.String(app.config['URL_MAX_LENGTH'])) username = db.Column(db.String(app.config['USERNAME_MAX_LENGTH']), unique=True) mobile_no = db.Column(db.String(app.config['MOBILE_NO_MAX_LENGTH']), unique=True) batches = db.relationship('Batch', backref='institute', lazy='dynamic') fp_token = db.Column(db.String(64)) @classmethod def authenticate_by_username(cls, username, password): """ Used in institute login :param username: chosen username of the institute :param password: :return: the institute row if authentication successful, none otherwise """ with app.app_context(): ins = cls.query.filter(cls.username == username, cls.password == password).first() return ins @classmethod def create(cls, name, email, password, username, location=None, mobile_no=None, logo_url=None): """ Create an institute :param name: :param email: :param password: :param username: :param location: :param mobile_no: :param logo_url: :return: """ user_type = UserTypes.query.filter_by(name='teacher').first() institute = cls(name=name, email=email, password=password, username=username, location=location, mobile_no=mobile_no, logo_url=logo_url, type=user_type.id) db.session.add(institute) try: db.session.commit() except IntegrityError as e: db.session.rollback() if 'email' in e.message: raise EmailAlreadyRegistered if 'username' in e.message: raise UsernameAlreadyRegistered if 'mobile_no' in e.message: raise MobileNoAlreadyRegistered raise e return institute @classmethod def get(cls, id): """ Get details of a single institute :param id: institute id :return: institute object """ institute = cls.query.get(id) if institute is not None: return institute else: raise InvalidInstituteId @classmethod def get_list(cls, page=1, limit=10): """ Get a list of institutes :param page: :param limit: :return: """ institutes_pag_obj = cls.query.filter_by(is_active=True).paginate( page, limit) institutes = institutes_pag_obj.items total = institutes_pag_obj.total return institutes, total