class Question_rate(db.Model):
__tablename__ = 'question_rate'
id = db.Column(db.Integer, nullable=False, primary_key=True)
question_id = db.Column(db.Integer, nullable=False)
module_id = db.Column(db.Integer, nullable=False)
rate = db.Column(db.Integer, nullable=False)
comment_id = db.Column(db.Integer, nullable=False)
def __init__(self, question_id, module_id, rate, comment_id):
self.question_id = question_id
self.module_id = module_id
self.rate = rate
self.comment_id = comment_id
def add_question_rate_list(question_rate_list):
try:
for q in question_rate_list:
db.session.add(q)
db.session.commit()
except:
return False
return True
def serialize(self):
return {
"id": self.id,
"question": self.question,
"owner_id": self.owner_id,
"module_id": self.module_id,
"rate": self.rate,
"comment_id": self.comment_id
}
class House(db.Model):
house_id = db.Column(db.Integer, primary_key=True)
house_keeper = db.Column(db.Integer)
year = db.Column(db.Integer)
color = db.Column(db.String)
house_name = db.Column(db.String(30))
def __init__(self, house_keeper, year, color, house_name):
self.house_keeper = house_keeper
self.year = year
self.color = color
self.house_name = house_name
def get_houselist_by_year(year):
houseList = House.query.filter_by(year=year).all()
return houseList
def get_house_by_id(house_id):
house = House.query.filter_by(house_id=house_id).first()
return house
def get_house_by_housekeeper(hkid):
house = House.query.filter_by(house_keeper=hkid).first()
return house
def serialize(self):
return {
"house_id": self.house_id,
"house_keeper": self.house_keeper,
"year": self.year,
"color": self.color,
"house_name": self.house_name
}
class Superuser(BaseModel):
__tablename__ = 'superusers'
email = db.Column(db.String(255), nullable=False, unique=True)
password_hash = db.Column(db.String(255), nullable=False)
fullname = db.Column(db.String(255), nullable=True)
last_login_at = db.Column(db.DateTime, nullable=True)
activities = db.relationship('Activity',
backref='superuser',
cascade='all, delete-orphan')
@property
def password(self):
raise AttributeError('password is not a readable attribue')
@password.setter
def password(self, password):
self.password_hash = generate_password_hash(password)
def verify_password(self, password):
return check_password_hash(self.password_hash, password)
def is_active(self):
return True
def is_anonymous(self):
return False
def get_id(self):
return self.id
def is_authenticated(self):
return True
class EmployeRelationMapping(Base):
__tablename__ = 'employee_relation_mapping_t'
map_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
cycle_id = db.Column(db.Integer,
db.ForeignKey(ClientCycleRepo.cycle_id),
nullable=False)
pat_emp_id = db.Column(db.String(20), nullable=False)
relation_id = db.Column(db.Integer,
db.ForeignKey(UserRelationRepo.relation_id),
nullable=False)
target_emp_id = db.Column(db.String(20), nullable=False)
AssessmentTestAnswers = relationship("AssessmentTestAnswers",
cascade="all,delete",
backref="EmployeRelationMapping",
lazy='joined')
FeedBackStatements = relationship("FeedBackStatements",
cascade="all,delete",
backref="EmployeRelationMapping",
lazy='joined')
def __repr__(self):
return "<EmployeRelationMapping(map_id='{}',cycle_id='{}',pat_emp_id='{}',relation_id='{}'," \
"target_emp_id='{}')>".format(self.map_id, self.cycle_id, self.pat_emp_id, self.relation_id, self.target_emp_id)
def get_json(self):
body = {
"map_id": self.map_id,
"cycle_id": self.cycle_id,
"pat_emp_id": self.pat_emp_id,
"relation_id": self.relation_id,
"target_emp_id": self.target_emp_id
}
return body
class User(BaseModel):
__tablename__ = 'users'
fullname = db.Column(db.String(255), nullable=False)
dob = db.Column(db.String(255), nullable=False)
home_address = db.Column(db.String(255), nullable=True)
work_address = db.Column(db.String(255), nullable=True)
phone_number = db.Column(db.String(255), nullable=False)
password_hash = db.Column(db.String(255), nullable=False)
occupation = db.Column(db.String(255), nullable=True)
status = db.Column(db.String(255), nullable=True)
last_login_at = db.Column(db.DateTime, nullable=True)
last_logout_at = db.Column(db.DateTime, nullable=True)
activities = db.relationship('Activity',
backref='user',
cascade='all, delete-orphan')
@property
def password(self):
raise AttributeError('password is not a readable attribue')
@password.setter
def password(self, password):
self.password_hash = generate_password_hash(password)
def verify_password(self, password):
return check_password_hash(self.password_hash, password)
def is_anonymous(self):
return False
def get_id(self):
return self.id
class AssessmentOptionsRepo(Base):
__tablename__ = 'assessment_options_m'
opt_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
opt_statement = db.Column(db.Text, nullable=False)
opt_score = db.Column(db.Integer, nullable=False)
AssessmentTestAnswers = relationship("AssessmentTestAnswers",
cascade="all,delete",
backref="AssessmentOptionsRepo",
lazy='joined')
QuestionOptionsMapping = relationship("QuestionOptionsMapping",
cascade="all,delete",
backref="AssessmentOptionsRepo",
lazy='joined')
def __repr__(self):
return "<AssessmentOptionsRepo(opt_id='{}',opt_statement='{}',opt_score='{}')>".format(
self.opt_id, self.opt_statement, self.opt_score)
def get_json(self):
body = {
"opt_id": self.opt_id,
"opt_statement": self.opt_statement,
"opt_score": self.opt_score
}
return body
class AssessmentQuestionsRepo(Base):
__tablename__ = 'assessment_questions_m'
que_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
que_statement = db.Column(db.Text, nullable=False)
competency_id = db.Column(db.Integer,
db.ForeignKey(CompetencyTypeRepo.competency_id))
AssessmentTestAnswers = relationship("AssessmentTestAnswers",
cascade="all,delete",
backref="AssessmentQuestionsRepo")
IndividualScores = relationship("IndividualScores",
cascade="all,delete",
backref="AssessmentQuestionsRepo")
QuestionOptionsMapping = relationship("QuestionOptionsMapping",
cascade="all,delete",
backref="AssessmentQuestionsRepo")
def __repr__(self):
return "<AssessmentQuestionsRepo(que_id='{}',que_statement='{}',competency_id='{}')>".format(
self.que_id, self.que_statement, self.competency_id)
def get_json(self):
body = {
"que_id": self.que_id,
"que_statement": self.que_statement,
"competency_id": self.competency_id
}
return body
class ClientRepo(Base):
__tablename__ = 'client_table_m'
client_id = db.Column(db.String(64), primary_key=True)
client_name = db.Column(db.String(64), nullable=False)
client_email_id = db.Column(db.String(64), nullable=False, unique=True)
client_uri = db.Column(db.String(64))
cycle = relationship("ClientCycleRepo",
backref='clientrepo',
cascade="all,delete",
lazy='dynamic')
employee = relationship("EmployeeDetailsRepo",
backref='clientrepo',
cascade="all,delete",
lazy='dynamic')
ClientAdminRepo = relationship("ClientAdminRepo",
backref='clientrepo',
cascade="all,delete",
lazy='dynamic')
def __repr__(self):
return "<Clients(client_id='{}',client_name='{}',client_email_id='{}',client_uri='{}')>".format(
self.client_id, self.client_name, self.client_email_id,
self.client_uri)
def get_json(self):
body = {
"client_id": self.client_id,
"client_name": self.client_name,
"client_email_id": self.client_email_id,
"client_uri": self.client_uri
}
return body
class Activity(BaseModel):
__tablename__ = 'activities'
channel = db.Column(db.String(255), nullable=True)
description = db.Column(db.String(255), nullable=False)
user_id = db.Column(db.Integer,
db.ForeignKey('users.id', ondelete='CASCADE'))
superuser_id = db.Column(
db.Integer, db.ForeignKey('superusers.id', ondelete='CASCADE'))
class test_question(db.Model):
__tablename__ = 'test_question'
testID = db.Column(db.Integer, db.ForeignKey('test.testID'), nullable=False, primary_key=True)
questionID = db.Column(db.Integer, db.ForeignKey('question.questionID'), nullable=False, primary_key=True)
# This function should return a printable representation of the test_question object
def __repr__(self):
return '<test_question: {}>'.format(self.testID)
class domain(db.Model):
__tablename__ = 'domain'
domainID = db.Column(db.Integer, nullable=False, primary_key=True, autoincrement=True)
name = db.Column(db.String(30), nullable=False)
# This function should return a printable representation of the domain object
def __repr__(self):
return '<domain: {}>'.format(self.name)
class topic(db.Model):
__tablename__ = 'topic'
topicID = db.Column(db.Integer, nullable=False, primary_key=True, autoincrement=True)
name = db.Column(db.String(30), nullable=False)
domainID = db.Column(db.Integer, db.ForeignKey('domain.domainID'), nullable=False)
# This function should return a printable representation of the topic object
def __repr__(self):
return '<topic: {}>'.format(self.name)
class IndividualScores(Base):
__tablename__ = 'individual_question_answer_score_t'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
cycle_id = db.Column(db.Integer, db.ForeignKey(ClientCycleRepo.cycle_id))
emp_id = db.Column(db.String(20),
db.ForeignKey(EmployeeDetailsRepo.emp_id))
que_id = db.Column(db.Integer,
db.ForeignKey(AssessmentQuestionsRepo.que_id))
self_score = db.Column(db.Integer)
avg_senior_score = db.Column(db.DECIMAL(4, 2))
avg_peer_score = db.Column(db.DECIMAL(4, 2))
avg_subordinates_score = db.Column(db.DECIMAL(4, 2))
avg_respondants_score = db.Column(db.DECIMAL(4, 2))
def __repr__(self):
return "<IndividualScores(id='{}')>".format(self.id)
def get_json(self):
body = {
"id": self.id,
"cycle_id": self.cycle_id,
"emp_id": self.emp_id,
"que_id": self.que_id,
"self_score": self.self_score,
"avg_senior_score": self.avg_senior_score,
"avg_peer_score": self.avg_peer_score,
"avg_subordinates_score": self.avg_subordinates_score,
"avg_others_score": self.avg_others_score,
"avg_respondants_score": self.avg_respondants_score
}
return body
class option(db.Model):
__tablename__ = 'option'
optionID = db.Column(db.Integer, nullable=False, primary_key=True, autoincrement=True)
description = db.Column(db.String(255), nullable=False)
correct = db.Column(db.SmallInteger, nullable=False)
questionID = db.Column(db.Integer, db.ForeignKey('question.questionID'), nullable=False)
# This function should return a printable representation of the option object
def __repr__(self):
return '<option: {}>'.format(self.description)
class answer(db.Model):
__tablename__ = 'answer'
answerID = db.Column(db.Integer, nullable=False, primary_key=True, autoincrement=True)
user_testID = db.Column(db.Integer, db.ForeignKey('user_test.user_testID'), nullable=False)
questionID = db.Column(db.Integer, db.ForeignKey('question.questionID'), nullable=False)
optionID = db.Column(db.Integer, db.ForeignKey('option.optionID'), nullable=False)
# This function should return a printable representation of the answer object
def __repr__(self):
return '<answer: {}>'.format(self.answerID)
class user(db.Model):
__tablename__ = 'user'
userID = db.Column(db.Integer, nullable=False, primary_key=True)
name = db.Column(db.String(255), nullable=False)
# 28/03/2021 - create relationship between 'user' and 'user_test'
testsTaken = db.relationship('user_test', backref='user', lazy='joined')
# This function should return a printable representation of the user object
def __repr__(self):
return '<user: {}>'.format(self.name)
class user_test(db.Model):
__tablename__ = 'user_test'
user_testID = db.Column(db.Integer, nullable=False, primary_key=True, autoincrement=True)
date = db.Column(db.Date, nullable=False, primary_key=True)
userID = db.Column(db.Integer, db.ForeignKey('user.userID'), nullable=False, primary_key=True)
testID = db.Column(db.Integer, db.ForeignKey('test.testID'), nullable=False, primary_key=True)
score = db.Column(db.Integer, nullable=False)
# This function should return a printable representation of the user_test object
def __repr__(self):
return '<user_test: {}>'.format(self.user_testID)
class User(UserMixin, db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
uname = db.Column(db.String(80), unique=True)
password_hash = db.Column(db.String(80))
email = db.Column(db.String(80), unique=True)
phone = db.Column(db.String(20), unique=True)
img = db.Column(db.String(80))
title = db.Column(db.Integer)
confirmed = db.Column(db.Boolean, nullable=False, default=False)
confirmed_on = db.Column(db.DateTime, nullable=True)
def __init__(self,
uname,
password,
email,
phone=None,
img=None,
title=None,
confirmed=False,
confirmed_on=None):
self.uname = uname
self.password_hash = generate_password_hash(password)
self.email = email
self.phone = phone
self.img = img
self.title = title
self.confirmed = confirmed
self.confirmed_on = confirmed_on
def serialize(self):
return {
"id": self.id,
"uname": self.uname,
"password": self.password_hash,
"role": self.title,
"email": self.email,
"phone": self.phone,
"img": self.img,
"title": self.title,
"confirmed": self.confirmed,
"confirmed_on": self.confirmed_on
}
def set_password(self, password):
self.password_hash = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.password_hash, password)
def get_user_by_id(id):
return User.query.filter_by(id=id).first()
def is_tourist(email):
if UserModule.query.filter(email=email).count() > 0:
return False
return True
class QuestionOptionsMapping(db.Model):
__tablename__ = 'assessment_question_options_t'
id = db.Column(db.Integer, primary_key=True)
que_id = db.Column(db.Integer,
db.ForeignKey(AssessmentQuestionsRepo.que_id),
nullable=False)
opt_id = db.Column(db.Integer,
db.ForeignKey(AssessmentOptionsRepo.opt_id),
nullable=False)
def __repr__(self):
return "<QuestionOptionsMapping(id='{}',que_id='{}',opt_id='{}')>".format(
self.id, self.que_id, self.opt_id)
class test(db.Model):
__tablename__ = 'test'
testID = db.Column(db.Integer, nullable=False, primary_key=True, autoincrement=True)
topicID = db.Column(db.Integer, db.ForeignKey('topic.topicID'), nullable=False)
questions = db.relationship('test_question', backref='test', lazy='joined')
# 28/03/2021 - create relationship between 'test' and 'user_test'
testsAvailable = db.relationship('user_test', backref='test', lazy='joined')
# This function should return a printable representation of the test object
def __repr__(self):
return '<test: {}>'.format(self.testID)
class HouseKeeper(db.Model):
user_id = db.Column(db.Integer, primary_key=True)
module_id = db.Column(db.Integer)
course_id = db.Column(db.Integer)
def __init__(self, user_id, module_id=None, course_id=None):
self.user_id = user_id
self.module_id = module_id
self.course_id = course_id
def serialize(self):
return {
"user_id": self.user_id,
"module_id": self.module_id,
"course_id": self.course_id
}
class Customer(STModel):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(NAME_LENGTH), index=True, nullable=False)
contact = db.Column(db.String(CONTACT_LENGTH))
address = db.Column(db.String(ADDRESS_LENGTH))
sale_transactions = db.relationship('SaleTransaction',
backref='customer',
lazy='dynamic',
cascade='save-update, merge, delete')
@classmethod
def check_exist(cls, name):
return db.session.query(exists().where(Customer.name == name)).scalar()
@classmethod
def try_add(cls, name, contact=None, address=None):
# no need to check exists in here because
# the form has handled it and the model fields has unique=True
new_cou = Customer(name=name, contact=contact, address=address)
try:
db.session.add(new_cou)
db.session.commit()
return True
except Exception as e:
cls.add_error(e)
db.session.rollback()
return False
@classmethod
def get_list(cls,
page_num=DEFAULT_PAGE_NUMBER,
list_per_page=DEFAULT_POSTS_PER_PAGE,
include_header=True,
order_by=None,
**kwargs):
q = Customer.query.with_entities(Customer.id, Customer.name,
Customer.address, Customer.contact)
q = paginate_query(q, page_num, list_per_page)
if order_by:
q = q.order_by(order_by)
c_list = q.all()
if include_header:
c_list.insert(0, tuple(x['name'] for x in q.column_descriptions))
return c_list
class UserModule(db.Model):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String)
module_id = db.Column(db.Integer)
status = db.Column(db.Integer)
def __init__(self, email, module_id, status):
self.email = email
self.module_id = module_id
self.status = status
def serialize(self):
return {
"email": self.email,
"module_id": self.module_id,
"status": self.status
}
class Manager(db.Model):
user_id = db.Column(db.Integer, primary_key=True)
def __init__(self, user_id):
self.user_id = user_id
def serialize(self):
return {"user_id": self.user_id}
class TransactionMedium(STModel):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(NAME_LENGTH),
index=True,
nullable=False,
unique=True)
sale_transactions = db.relationship('SaleTransaction',
backref='transaction_medium',
lazy='dynamic')
@classmethod
def check_exist(cls, name):
return db.session.query(
exists().where(TransactionMedium.name == name)).scalar()
@classmethod
def try_add(cls, name):
new_tm = TransactionMedium(name=name)
try:
db.session.add(new_tm)
db.session.commit()
return True
except Exception as e:
cls.add_error(e)
db.session.rollback()
return False
@classmethod
def get_list(cls,
page_num=DEFAULT_PAGE_NUMBER,
list_per_page=DEFAULT_POSTS_PER_PAGE,
include_header=True,
order_by=None,
**kwargs):
q = TransactionMedium.query.with_entities(TransactionMedium.id,
TransactionMedium.name)
q = paginate_query(q, page_num, list_per_page)
if order_by:
q = q.order_by(order_by)
tm_list = q.all()
if include_header:
tm_list.insert(0, tuple(x['name'] for x in q.column_descriptions))
return tm_list
class UserTagTable(db.Model):
""" Initialize the user_tag_table from MySQL database
+----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+----------------+
| userID | int(11) | NO | | NULL | |
| itemID | int(11) | NO | PRI | NULL | auto_increment |
| Link | varchar(1000) | YES | | NULL | |
| CreateTime | datetime | NO | | NULL | |
| Tag | varchar(1000) | YES | | NULL | |
| Title | varchar(1000) | YES | | NULL | |
| LastUpdateTime | datetime | NO | | NULL | |
+----------------+---------------+------+-----+---------+----------------+
"""
userID = db.Column(db.Integer,
unique=False,
nullable=False,
primary_key=False)
itemID = db.Column(db.Integer,
unique=True,
nullable=False,
primary_key=True)
Link = db.Column(db.String(1000))
Tag = db.Column(db.String(1000))
Title = db.Column(db.String(1000))
CreateTime = db.Column(db.DateTime)
LastUpdateTime = db.Column(db.DateTime)
class Comment(db.Model):
__tablename__ = 'comment'
id = db.Column(db.Integer, primary_key=True)
owner_id = db.Column(db.Integer, unique=True)
module_id = db.Column(db.Integer)
content = db.Column(db.String, nullable=False)
star = db.Column(db.Integer)
status = db.Column(db.Integer, nullable=False)
date = db.Column(db.String, nullable=False)
def __init__(self, owner_id, module_id, content, star, status, date):
self.owner_id = owner_id
self.module_id = module_id
self.content = content
self.star = star
self.status = status
self.date = date
def serialize(self):
return {
"id": self.id,
"owner_id": self.owner_id,
"module_id": self.module_id,
"content": self.content,
"star": self.star,
"status": self.status,
"date": self.date
}
class question(db.Model):
__tablename__ = 'question'
questionID = db.Column(db.Integer, nullable=False, primary_key=True, autoincrement=True)
description = db.Column(db.String(600), nullable=False)
testQuestions = db.relationship('test_question', backref='question', lazy='joined')
# 16/03/2021 - Create relationship between question and option classes, in order to load the multiple options related to a single question
# A relationship will allow for both questions and options to be pulled from the database together in a single query statement
options = db.relationship('option', backref='question', lazy='joined')
# 24/03/2021 - Create a relationship between question and answer classes, in order to load the option the user selected to answer the current question
answer = db.relationship('answer', backref='question', lazy='joined')
# This function should be return a printable representation of the question object
def __repr__(self):
return '<question: {}>'.format(self.description)
class Course(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80), unique=True)
description = db.Column(db.String)
module = db.Column(db.Integer)
def __init__(self, name, description, module):
self.name = name
self.description = description
self.module = module
def serialize(self):
return {
"id": self.id,
"name": self.name,
"description": self.description,
"module": self.module
}
class Questionnaire(db.Model):
__tablename__ = 'questionnaire'
id = db.Column(db.Integer, nullable=False, primary_key=True)
abbr = db.Column(db.String, nullable=False)
question = db.Column(db.String, nullable=False)
def __init__(self, id, abbr, question):
self.id = id
self.abbr = abbr
self.question = question
def serialize(self):
return {
"id": self.id,
"abbr": self.abbr,
"question": self.question,
"module_id": self.module_id
}
