class Report(db.Model):
__tablename__ = 'Report'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(255), index=True, nullable=False)
sections = db.relationship('Section', backref='report', lazy='dynamic', cascade='all, delete-orphan')
lang_id = db.Column(db.Integer, db.ForeignKey('Lang.id'))
user_reports = db.relationship('UserReport', backref='template', lazy='dynamic', cascade='all, delete-orphan')
benchmark_reports = db.relationship('BenchmarkReport', backref='report', lazy='dynamic',
cascade='all, delete-orphan')
def __repr__(self):
return '<Report {0}>'.format(self.id)
def __init__(self, title, lang_id):
self.title = title
self.lang_id = lang_id
def output_obj(self):
return {
'id': self.id,
'title': self.title,
'lang_id': self.lang_id,
'sections': [s.output_obj() for s in self.ordered_sections],
'benchmark_reports': [b.id for b in self.benchmark_reports]
}
@property
def ordered_sections(self):
return sorted(self.sections, key=lambda section: section.order_in_report)
class Answer(db.Model):
__tablename__ = 'Answer'
id = db.Column(db.Integer, primary_key=True)
answer = db.Column(db.String(255), nullable=False, index=True)
value = db.Column(db.Integer, nullable=True, default=1)
order_in_question = db.Column(db.Integer, nullable=False, default=0)
lang_id = db.Column(db.Integer, db.ForeignKey('Lang.id'))
question_answers = db.relationship('QuestionAnswer', backref='answer_template', lazy='dynamic')
benchmark = db.relationship('Benchmark', backref='answer', lazy='dynamic')
def __repr__(self):
return u'<Answer {0}: {1}>'.format(self.id, self.answer)
def __init__(self, answer, lang_id, value=1, order=0):
self.answer = answer
self.lang_id = lang_id
self.value = value
self.order_in_question = order
def output_obj(self):
return {
'id': self.id,
'answer': self.answer,
'value': self.value,
'lang_id': self.lang_id,
'order_in_question': self.order_in_question
}
class QuestionAnswer(db.Model):
__tablename__ = 'QuestionAnswer'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('User.id'))
question_id = db.Column(db.Integer, db.ForeignKey('Question.id'))
answer_id = db.Column(db.Integer, db.ForeignKey('Answer.id'))
user_report_id = db.Column(db.Integer, db.ForeignKey(UserReport.id))
def __init__(self, user_id, question_id, answer_id, user_report_id):
self.user_id = user_id
self.question_id = question_id
self.answer_id = answer_id
self.user_report_id = user_report_id
@property
def score(self):
return self.question_template.weight * self.answer_template.value * self.question_template.risk_factor.value
def output_obj(self):
return {
'id': self.id,
'user_id': self.user_id,
'question_id': self.question_id,
'answer_id': self.answer_id,
'score': self.score,
'user_report_id': self.user_report_id
}
class OrganisationType(db.Model):
__tablename__ = 'OrganisationType'
id = db.Column(db.Integer, primary_key=True)
type = db.Column(db.String(190), index=True, unique=True)
organisations = db.relationship('Organisation',
backref='type',
lazy='dynamic')
def __init__(self, type):
self.type = type
class Role(db.Model):
__tablename__ = 'roles'
id = db.Column(db.Integer, primary_key=True)
role = db.Column(db.String(190), index=True, unique=True)
def __repr__(self):
return '<Role {0}>'.format(self.role)
def __init__(self, role):
self.role = role
class Organisation(db.Model):
__tablename__ = 'Organisation'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), index=True)
type_id = db.Column(db.Integer, db.ForeignKey(OrganisationType.id))
size = db.Column(db.String(255))
users = db.relationship('User', backref='organisation', lazy='dynamic')
def __init__(self, name, size=None):
self.name = name
if size:
self.size = size
class MenuLink(db.Model):
__tablename__ = 'MenuLink'
id = db.Column(db.Integer, primary_key=True)
menu_link = db.Column(db.String(255), index=True)
pages = db.relationship('Page', backref='menu_link', lazy='dynamic')
def __init__(self, menu_link):
self.menu_link = menu_link
def __repr__(self):
return u'<MenuLink {0}: {1}>'.format(self.id, self.menu_link)
def output_obj(self):
return {'menu_link': self.menu_link}
class BenchmarkReport(db.Model):
__tablename__ = 'BenchmarkReport'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(255), index=True)
report_id = db.Column(db.Integer, db.ForeignKey(Report.id))
benchmarks = db.relationship('Benchmark', backref='benchmark_report', lazy='dynamic', cascade='all, delete-orphan')
def __init__(self, title):
self.title = title
def output_obj(self):
return {
'id': self.id,
'title': self.title,
'report_id': self.report_id,
'benchmarks': [b.output_obj() for b in self.benchmarks],
'benchmarks_by_section': []
}
class Page(db.Model):
__tablename__ = 'Page'
id = db.Column(db.Integer, primary_key=True)
menu_link_id = db.Column(db.Integer, db.ForeignKey(MenuLink.id))
content = db.Column(db.Text)
lang_id = db.Column(db.Integer, db.ForeignKey(Lang.id))
def __init__(self, content):
self.content = content
def __repr__(self):
return u'<Page {0}: {1}>'.format(self.id, self.menu_link.menu_link)
def output_obj(self):
return {
'id': self.id,
'menu_link_id': self.menu_link_id,
'menu_link': self.menu_link.menu_link,
'content': self.content,
'lang_id': self.lang_id,
'lang': self.lang.lang
}
class RiskFactor(db.Model):
__tablename__ = 'RiskFactor'
id = db.Column(db.Integer, primary_key=True)
risk_factor = db.Column(db.String(190), index=True, unique=True)
value = db.Column(db.Integer, nullable=False, default=1)
lang_id = db.Column(db.Integer, db.ForeignKey('Lang.id'))
questions_single = db.relationship('Question', backref='risk_factor', lazy='dynamic')
def __repr__(self):
return '<RiskFactor {0}>'.format(self.id)
def __init__(self, risk_factor, lang_id, value=None):
self.risk_factor = risk_factor
self.lang_id = lang_id
if value:
self.value = value
def output_obj(self):
return {
'id': self.id,
'risk_factor': self.risk_factor,
'lang_id': self.lang_id,
'value': self.value
}
class Benchmark(db.Model):
__tablename__ = 'Benchmark'
id = db.Column(db.Integer, primary_key=True)
question_id = db.Column(db.Integer, db.ForeignKey(Question.id))
answer_id = db.Column(db.Integer, db.ForeignKey(Answer.id))
benchmark_report_id = db.Column(db.Integer, db.ForeignKey(BenchmarkReport.id))
not_in_benchmark = db.Column(db.Boolean, default=False)
def __init__(self):
pass
@property
def score(self):
if self.answer is None:
return 0
else:
return self.question.weight * self.answer.value * self.question.risk_factor.value
def output_obj(self):
# The last case shouldn't happen: TODO: fix
if self.not_in_benchmark or self.answer is None or self.question.risk_factor is None:
return {
'id': self.id,
'question_id': self.question_id,
'benchmark_report_id': self.benchmark_report_id,
'not_in_benchmark': self.not_in_benchmark
}
else:
return {
'id': self.id,
'question_id': self.question_id,
'answer_id': self.answer_id,
'score': self.score,
'benchmark_report_id': self.benchmark_report_id,
'not_in_benchmark': self.not_in_benchmark
}
class Lang(db.Model):
__tablename__ = 'Lang'
id = db.Column(db.Integer, primary_key=True)
lang = db.Column(db.String(255), index=True)
pages = db.relationship('Page', backref='lang', lazy='dynamic')
documents = db.relationship('Document', backref='lang', lazy='dynamic')
reports = db.relationship('Report', backref='lang', lazy='dynamic')
answers = db.relationship('Answer', backref='lang', lazy='dynamic')
risk_factors = db.relationship('RiskFactor',
backref='lang',
lazy='dynamic')
users = db.relationship('User', backref='lang', lazy='dynamic')
def __init__(
self,
lang,
):
self.lang = lang
def __repr__(self):
return u'<Lang {0}: {1}>'.format(self.id, self.lang)
def output_obj(self):
return {'lang': self.lang}
class Document(db.Model):
__tablename__ = 'Document'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), index=True, nullable=False)
filename = db.Column(db.String(255), index=True)
original_filename = db.Column(db.String(255))
description = db.Column(db.Text)
lang_id = db.Column(db.Integer, db.ForeignKey(Lang.id))
def __init__(self,
name,
filename=None,
original_filename=None,
description=None):
self.name = name
if filename:
self.filename = filename
if original_filename:
self.original_filename = original_filename
if description:
self.description = description
def __repr__(self):
return u'<Document {0}: {1}>'.format(self.id, self.filename)
def output_obj(self):
return {
'id': self.id,
'name': self.name,
'filename': self.filename,
'original_filename': self.original_filename,
'mimetype': self.mimetype,
'description': self.description,
'lang_id': self.lang_id,
'lang': self.lang.lang
}
@property
def mimetype(self):
if self.filename:
guessed = mimetypes.guess_type(self.filename)
return guessed[0]
else:
return u''
@property
def js_media_type(self):
media_type = self.mimetype.split('/')
return media_type[0]
self.lang_id = lang_id
self.value = value
self.order_in_question = order
def output_obj(self):
return {
'id': self.id,
'answer': self.answer,
'value': self.value,
'lang_id': self.lang_id,
'order_in_question': self.order_in_question
}
answers = db.Table('answer_question',
db.Column('answer_id', db.Integer, db.ForeignKey('Answer.id')),
db.Column('question_id', db.Integer, db.ForeignKey('Question.id'))
)
class RiskFactor(db.Model):
__tablename__ = 'RiskFactor'
id = db.Column(db.Integer, primary_key=True)
risk_factor = db.Column(db.String(190), index=True, unique=True)
value = db.Column(db.Integer, nullable=False, default=1)
lang_id = db.Column(db.Integer, db.ForeignKey('Lang.id'))
questions_single = db.relationship('Question', backref='risk_factor', lazy='dynamic')
def __repr__(self):
return '<RiskFactor {0}>'.format(self.id)
class User(db.Model):
__tablename__ = 'User'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(190),
index=True,
unique=True,
nullable=False)
email = db.Column(db.String(190), index=True, unique=True, nullable=False)
password_hash = db.Column(db.String(255), nullable=False)
questions = db.relationship('QuestionAnswer',
backref='user',
lazy='dynamic')
reports = db.relationship('UserReport', backref='user', lazy='dynamic')
authenticated = db.Column(db.Boolean, default=False)
lang_id = db.Column(db.Integer, db.ForeignKey('Lang.id'))
api_key = db.Column(db.String(190), nullable=False, unique=True)
session_token = db.Column(db.String(190), nullable=False, unique=True)
organisation_id = db.Column(db.Integer, db.ForeignKey(Organisation.id))
roles = db.relationship('Role',
secondary=users_roles,
primaryjoin=(users_roles.c.user_id == id),
secondaryjoin=(users_roles.c.role_id == Role.id),
backref=db.backref('users', lazy='dynamic'),
lazy='dynamic')
def __init__(self, email, password):
self.email = email
self.username = self.email
self.set_password(password)
self.set_api_key()
self.set_session_token()
def __repr__(self):
return '<User {0}>'.format(self.username)
def output_obj(self):
return {
'id': self.id,
'username': self.username,
'posts': [p.id for p in self.posts],
'roles': [r.id for r in self.roles]
}
def set_password(self, input_password):
bit_input = input_password.encode('utf-8')
self.password_hash = bcrypt.hashpw(bit_input, bcrypt.gensalt())
def verify_password(self, input_password):
bit_input = input_password.encode('utf-8')
# Check needed because mysql returns strings (in unicode), but sqlite returns bits
if isinstance(self.password_hash, str):
password_hash = self.password_hash.encode('utf-8')
else:
password_hash = self.password_hash
if bcrypt.hashpw(bit_input, password_hash) == password_hash:
return True
else:
return False
def is_active(self):
return True
def is_anonymous(self):
return False
def get_id(self):
"""
Returns the session_token, not the ID (see #Alternative Tokens at https://flask-login.readthedocs.io/en/latest/)
:return:
"""
return self.session_token
def is_authenticated(self):
return self.authenticated
def has_role(self, role_name):
for role in self.roles:
if role.role == role_name:
return True
return False
def set_api_key(self):
self.api_key = Token().unique_token(User, 'api_key', 64)
def set_session_token(self):
self.session_token = Token().unique_token(User, 'session_token', 64)
@property
def locale(self):
if self.lang:
return self.lang.lang
return app.config['BABEL_DEFAULT_LOCALE']
class Question(db.Model):
__tablename__ = 'Question'
id = db.Column(db.Integer, primary_key=True)
question = db.Column(db.String(512), index=True, nullable=False)
context = db.Column(db.Text)
risk = db.Column(db.Text)
example = db.Column(db.Text)
weight = db.Column(db.Integer, nullable=False, default=1)
order_in_section = db.Column(db.Integer, nullable=False, default=0)
section_id = db.Column(db.Integer, db.ForeignKey(Section.id))
action = db.Column(db.Text)
maximum_score = db.Column(db.Integer, nullable=False, default=0)
risk_factor_id = db.Column(db.Integer, db.ForeignKey(RiskFactor.id))
risk_factors = db.relationship('RiskFactor',
secondary=risk_factors,
primaryjoin=(risk_factors.c.question_id == id),
secondaryjoin=(risk_factors.c.risk_factor_id == RiskFactor.id),
backref=db.backref('questions', lazy='dynamic'),
lazy='dynamic'
)
answers = db.relationship('Answer',
secondary=answers,
primaryjoin=(answers.c.question_id == id),
secondaryjoin=(answers.c.answer_id == Answer.id),
backref=db.backref('questions', lazy='dynamic'),
lazy='dynamic'
)
question_answers = db.relationship('QuestionAnswer', backref='question_template', lazy='dynamic',
cascade='all, delete-orphan')
benchmark = db.relationship('Benchmark', backref='question', lazy='dynamic', cascade='all, delete-orphan')
# TODO: make weight dependent on risk_factors! (risk_factors must have a weight: hoog: 3, midden: 2, laag: 1
def __repr__(self):
return u'<Question {0}: {1}>'.format(self.id, self.question)
def __init__(self, question, context=None, risk=None, example=None, weight=1, order=0, action=None):
self.question = question
self.context = context
self.risk = risk
self.example = example
self.weight = weight
self.order_in_section = order
self.action = action
def selected_answer(self, user_report_id):
"""
Returns the selected answer for a specific question in a specific user report
:param user_report_id:
:return:
"""
return QuestionAnswer.query.filter(and_(QuestionAnswer.question_id == self.id,
QuestionAnswer.user_report_id == user_report_id)).first()
def ordered_risk_factors(self):
return sorted(self.risk_factors, key=lambda r: r.value, reverse=True)
@property
def ordered_answers(self):
return sorted(self.answers, key=lambda a: a.value, reverse=True)
def output_obj(self):
return {
'id': self.id,
'question': self.question,
'context': self.context,
'risk': self.risk,
'example': self.example,
'weight': self.weight,
'order_in_section': self.order_in_section,
'section_id': self.section_id,
'action': self.action,
'risk_factor_id': self.risk_factor_id,
'answers': [a.output_obj() for a in self.answers],
'ordered_answers': [a.output_obj() for a in self.ordered_answers]
}
class Role(db.Model):
__tablename__ = 'roles'
id = db.Column(db.Integer, primary_key=True)
role = db.Column(db.String(190), index=True, unique=True)
def __repr__(self):
return '<Role {0}>'.format(self.role)
def __init__(self, role):
self.role = role
users_roles = db.Table(
'users_roles', db.Column('user_id', db.Integer, db.ForeignKey('User.id')),
db.Column('role_id', db.Integer, db.ForeignKey('roles.id')))
class OrganisationType(db.Model):
__tablename__ = 'OrganisationType'
id = db.Column(db.Integer, primary_key=True)
type = db.Column(db.String(190), index=True, unique=True)
organisations = db.relationship('Organisation',
backref='type',
lazy='dynamic')
def __init__(self, type):
self.type = type
class UserReport(db.Model):
__tablename__ = 'UserReport'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), index=True)
creation_time = db.Column(db.DateTime)
last_modified = db.Column(db.DateTime)
user_id = db.Column(db.Integer, db.ForeignKey('User.id'))
report_id = db.Column(db.Integer, db.ForeignKey('Report.id'))
question_answers = db.relationship('QuestionAnswer',
backref='user_report',
lazy='dynamic',
cascade='all, delete-orphan')
def __init__(self, name, user_id, report_id, creation_time=None):
self.name = name
self.user_id = user_id
self.report_id = report_id
if creation_time is None:
creation_time = datetime.datetime.now()
self.creation_time = creation_time
self.last_modified = creation_time
@property
def creation_date(self):
return self.creation_time.date()
@property
def by_section(self):
questions_by_section = {
s.id: {
'title': s.title,
'questions': []
}
for s in self.template.sections
}
for question in self.question_answers:
if question.question_template.section_id in questions_by_section:
questions_by_section[question.question_template.
section_id]['questions'].append(question)
else:
questions_by_section[
question.question_template.section_id]['questions'] = [
question
]
return questions_by_section
def output_obj(self):
return {
'id':
self.id,
'name':
self.name,
'user_id':
self.user_id,
'report_id':
self.report_id,
'total_score':
self.total_score,
'question_answers':
[q.output_obj() for q in self.question_answers],
'question_answers_by_section': [{
'section_id':
key,
'section_title':
value['title'],
'question_answers':
[q.output_obj() for q in value['questions']]
} for (key, value) in self.by_section.items()]
}
@property
def total_score(self):
total_score = 0
for qa in self.question_answers:
total_score += qa.score * qa.question_template.section.weight
return total_score
class Section(db.Model):
__tablename__ = 'Section'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(255), index=True, nullable=False)
context = db.Column(db.Text)
order_in_report = db.Column(db.Integer, nullable=False, default=0)
weight = db.Column(db.Integer, nullable=False, default=1)
questions = db.relationship('Question', backref='section', lazy='dynamic', cascade='all, delete-orphan')
report_id = db.Column(db.Integer, db.ForeignKey(Report.id))
maximum_score = db.Column(db.Integer, nullable=False, default=0)
def __repr__(self):
return u'<Section {0}: {1}>'.format(self.id, self.title)
def __init__(self, title, context=None, order=0, weight=1):
self.title = title
self.context = context
self.order_in_report = order
self.weight = weight
def output_obj(self):
if not self.next_in_report:
next_section_id = None
else:
next_section_id = self.next_in_report.id
if not self.previous_in_report:
previous_section_id = None
else:
previous_section_id = self.previous_in_report.id
return {
'id': self.id,
'title': self.title,
'context': self.context,
'order_in_report': self.order_in_report,
'questions': [q.output_obj() for q in self.ordered_questions],
'report_id': self.report_id,
'weight': self.weight,
'next_section_id': next_section_id,
'previous_section_id': previous_section_id
}
@property
def highest_order(self):
return self.ordered_questions[-1].order_in_section
@property
def ordered_questions(self):
return sorted(self.questions, key=lambda question: question.order_in_section)
@property
def next_in_report(self):
current_pos = self.report.ordered_sections.index(self)
next_pos = current_pos + 1
if next_pos >= len(self.report.ordered_sections):
return None
else:
return self.report.ordered_sections[next_pos]
@property
def previous_in_report(self):
current_pos = self.report.ordered_sections.index(self)
previous_pos = current_pos - 1
if previous_pos < 0:
return None
else:
return self.report.ordered_sections[previous_pos]
