class ParticipantsAssessmentStatus(Base):
__tablename__ = 'participant_ass_status_t'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
emp_id = db.Column(db.String(20),
db.ForeignKey(EmployeeDetailsRepo.emp_id))
cycle_id = db.Column(db.Integer, db.ForeignKey(ClientCycleRepo.cycle_id))
is_respondant_selective = db.Column(db.Boolean, default=False)
is_block_report = db.Column(db.Boolean, default=False)
total_respondant_count = db.Column(db.Integer)
seniors_count = db.Column(db.Integer)
subordinates_count = db.Column(db.Integer)
peer_count = db.Column(db.Integer)
others_count = db.Column(db.Integer)
ass_com_seniors_count = db.Column(db.Integer, default=0)
ass_com_subordinates_count = db.Column(db.Integer, default=0)
ass_com_peer_count = db.Column(db.Integer, default=0)
ass_com_others_count = db.Column(db.Integer, default=0)
ass_com_respondant_count = db.Column(db.Integer, default=0)
completion_status_id = db.Column(
db.Integer,
db.ForeignKey(AssessmentCompletionStatusRepo.status_id),
default=1)
is_generate_report = db.Column(db.Boolean, default=False)
def __repr__(self):
return "<ParticipantsAssessmentStatus(id='{}')>".format(self.id)
class CompetancyScores(Base):
__tablename__ = 'competancy_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))
competency_id = db.Column(db.Integer,
db.ForeignKey(CompetencyTypeRepo.competency_id))
avg_self_score = db.Column(db.DECIMAL(4, 2))
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_others_score = db.Column(db.DECIMAL(4, 2))
avg_respondants_score = db.Column(db.DECIMAL(4, 2))
gap_analysis_score = db.Column(db.DECIMAL(6, 2))
def __repr__(self):
return "<CompetancyScores(id='{}')>".format(self.id)
def get_json(self):
body = {
"id": self.id,
"cycle_id": self.cycle_id,
"emp_id": self.emp_id,
"competency_id": self.competency_id,
"avg_self_score": self.avg_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,
"gap_analysis_score": self.gap_analysis_score
}
return body
class ClientAdminRepo(Base):
__tablename__ = 'client_admin_details_m'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
cycle_id = db.Column(db.Integer, db.ForeignKey(ClientRepo.client_id))
client_id = db.Column(db.Integer,
db.ForeignKey(ClientCycleRepo.client_id),
nullable=False)
emp_id = db.Column(db.String(64),
db.ForeignKey(EmployeeDetailsRepo.emp_id),
nullable=False)
password_ = db.Column(db.Text)
is_super_admin = db.Column(db.Boolean, default=True)
def __repr__(self):
return "<ClientAdminRepo(cycle_id='{}',client_id='{}',emp_id='{}',password_='{}'," \
"is_super_admin='{}')>".format(self.cycle_id, self.client_id, self.emp_id, self.password_,
self.is_super_admin)
def get_json(self):
body = {
"cycle_id": self.cycle_id,
"client_id": self.client_id,
"emp_id": self.emp_id,
"password": self.password_,
"is_super_admin": self.is_super_admin
}
return body
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 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 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 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_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 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 ClientDepartmentsDetailsRepo(Base):
__tablename__ = 'client_department_details_m'
department_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
cycle_id = db.Column(db.Integer, db.ForeignKey(ClientCycleRepo.client_id))
_function = db.Column(db.String(20), nullable=False)
role = db.Column(db.String(20), nullable=False)
location = db.Column(db.String(20), nullable=False)
band = db.Column(db.String(10), nullable=False)
EmployeeDetailsRepo = relationship("EmployeeDetailsRepo",
cascade="all,delete",
backref="ClientDepartmentsDetailsRepo",
lazy='joined')
def __repr__(self):
return "<ClientDepartmentsDetailsRepo(department_id='{}',cycle_id='{}',_function='{}',role='{}',location='{}'," \
"band='{}')>".format(self.department_id, self.cycle_id, self._function, self.role, self.location,
self.band)
def get_json(self):
body = {
"department_id": self.department_id,
"cycle_id": self.cycle_id,
"function": self._function,
"role": self.role,
"location": self.location,
"band": self.band
}
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 EmployeeDetailsRepo(Base):
__tablename__ = 'employee_details_m'
emp_id = db.Column(db.String(20), primary_key=True)
client_id = db.Column(db.String(64),
db.ForeignKey(ClientRepo.client_id),
nullable=False)
emp_name = db.Column(db.String(64), nullable=False)
emp_email = db.Column(db.String(64), nullable=False)
department_id = db.Column(db.Integer,
db.ForeignKey(
ClientDepartmentsDetailsRepo.department_id),
nullable=False)
role_id = db.Column(db.Integer,
db.ForeignKey(UserRoleRepo.role_id),
nullable=False)
password = db.Column(db.Text, nullable=False)
ParticipantsAssessmentStatus = relationship("ParticipantsAssessmentStatus",
cascade="all,delete",
backref="EmployeeDetailsRepo")
IndividualScores = relationship("IndividualScores",
cascade="all,delete",
backref="EmployeeDetailsRepo")
CompetancyScores = relationship("CompetancyScores",
cascade="all,delete",
backref="EmployeeDetailsRepo")
ClientAdminRepo = relationship("ClientAdminRepo",
cascade="all,delete",
backref="EmployeeDetailsRepo")
def __repr__(self):
return "<EmployeeDetailsRepo(emp_id='{}',client_id='{}',emp_name='{}',emp_email='{}')>".format(
self.emp_id, self.client_id, self.emp_name, self.emp_email)
def get_json(self):
body = {
"emp_id": self.emp_id,
"client_id": self.client_id,
"emp_name": self.emp_name,
"emp_email": self.emp_email,
"department_id": self.department_id,
"role_id": self.role_id
}
return body
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 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 ClientCycleRepo(Base):
__tablename__ = 'client_cycle_details_m'
cycle_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
client_id = db.Column(db.String(64),
db.ForeignKey(ClientRepo.client_id),
nullable=False)
cycle_name = db.Column(db.String(64), nullable=False)
created_date = db.Column(db.Date, nullable=False)
from_date = db.Column(db.Date)
to_date = db.Column(db.Date)
is_active = db.Column(db.Boolean, default=True)
departments = relationship("ClientDepartmentsDetailsRepo",
cascade="all,delete",
backref="clientcyclerepo",
lazy='dynamic')
EmployeRelationMapping = relationship("EmployeRelationMapping",
cascade="all,delete",
backref="clientcyclerepo",
lazy='dynamic')
ParticipantsAssessmentStatus = relationship("ParticipantsAssessmentStatus",
cascade="all,delete",
backref="clientcyclerepo",
lazy='dynamic')
IndividualScores = relationship("IndividualScores",
cascade="all,delete",
backref="clientcyclerepo",
lazy='dynamic')
CompetancyScores = relationship("CompetancyScores",
cascade="all,delete",
backref="clientcyclerepo",
lazy='dynamic')
ClientAdminRepo = relationship("ClientAdminRepo",
cascade="all,delete",
backref="clientcyclerepo",
lazy='dynamic')
def __repr__(self):
return "<ClientCycleRepo(cycle_id='{}',client_id='{}',cycle_name='{}',from_date='{}',to_date='{}'," \
"is_active='{}')>".format(self.cycle_id, self.client_id, self.cycle_name, self.from_date, self.to_date,
self.is_active)
def get_json(self):
body = {
"cycle_id": self.cycle_id,
"client_id": self.client_id,
"cycle_name": self.cycle_name,
"created_date": self.created_date,
"from_date": self.from_date,
"to_date": self.to_date,
"is_active": self.is_active,
}
return body
class FeedBackStatements(Base):
__tablename__ = 'store_feedback_statements_t'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
map_id = db.Column(db.Integer,
db.ForeignKey(EmployeRelationMapping.map_id))
level_id = db.Column(db.Integer, db.ForeignKey(FeedBackLevelRepo.level_id))
feedback_text = db.Column(db.Text, nullable=False)
def __repr__(self):
return "<FeedBackStatements(id='{}')>".format(self.id)
def get_json(self):
body = {
"id": self.id,
"level_id": self.level_id,
"feedback_text": self.feedback_text,
"map_id": self.map_id
}
return body
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 AssessmentTestAnswers(Base):
__tablename__ = 'assessment_question_answered_t'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
que_id = db.Column(db.Integer,
db.ForeignKey(AssessmentQuestionsRepo.que_id))
opt_id = db.Column(db.Integer, db.ForeignKey(AssessmentOptionsRepo.opt_id))
map_id = db.Column(db.Integer,
db.ForeignKey(EmployeRelationMapping.map_id))
answer_score = db.Column(db.Integer, nullable=False)
def __repr__(self):
return "<AssessmentTestAnswers(id='{}')>".format(self.id)
def get_json(self):
body = {
"id": self.id,
"que_id": self.que_id,
"opt_id": self.opt_id,
"map_id": self.map_id,
"answer_score": self.answer_score
}
return body
class FavoriteStation(db.Model):
__tablename__ = 'favorite_stations'
id = db.Column(db.Integer, primary_key=True)
station_id = db.Column(db.Integer, db.ForeignKey('stations.id'))
created_time = db.Column(db.TIMESTAMP(True), nullable=False)
class SaleTransaction(STModel):
id = db.Column(db.Integer, primary_key=True)
items = db.relationship('Item', backref='sale_transaction', lazy='dynamic')
transaction_date = db.Column(db.DateTime, index=True, nullable=False)
delivery_fee = db.Column(db.Integer, index=True)
customer_id = db.Column(db.Integer,
db.ForeignKey('customer.id'),
nullable=False)
courier_id = db.Column(db.Integer, db.ForeignKey('courier.id'))
transaction_medium_id = db.Column(db.Integer,
db.ForeignKey('transaction_medium.id'))
notes = db.Column(db.String(NOTES_LENGTH))
def __init__(self,
transaction_date=None,
customer_id=None,
courier_id=None,
delivery_fee=None,
transaction_medium_id=None,
notes=None):
if transaction_date is None:
self.transaction_date = datetime.now()
else:
self.transaction_date = transaction_date
if customer_id is None:
raise TypeError('customer_id is required')
self.delivery_fee = delivery_fee
self.customer_id = customer_id
self.courier_id = courier_id
self.transaction_medium_id = transaction_medium_id
self.notes = notes
@utils.classproperty
def total(cls):
# there are two ways doing this same as purchase transaction
q = db.session.query(func.count(Item.id)) \
.filter(Item.sale_transaction_id != None) \
.group_by(Item.sale_transaction_id, Item.item_type_id)
return len(q.all())
@classmethod
def get_list(cls,
page_num=DEFAULT_PAGE_NUMBER,
list_per_page=DEFAULT_POSTS_PER_PAGE,
include_header=True,
order_by=None,
ids=None,
year=None,
month=None,
day=None):
qty_label = 'quantity'
individual_price_label = 'sale_price_(each)'
total_sale_price_label = 'total_sale_price'
total_purchase_price_label = 'total_purchase_price'
profit_label = 'profit'
delivery_fee_label = 'delivery_fee'
# initial query
q = db.session.query(
Item.sale_transaction_id.label('sale_id'), ItemType.item_type,
SaleTransaction.transaction_date,
func.count(Item.id).label(qty_label),
Item.sale_price.label(individual_price_label),
func.sum(Item.sale_price).label(total_sale_price_label),
func.sum(Item.purchase_price).label(total_purchase_price_label),
func.sum(Item.profit).label(profit_label),
Customer.name.label('customer'),
TransactionMedium.name.label('medium'),
Courier.name.label('courier'),
SaleTransaction.delivery_fee.label(delivery_fee_label),
SaleTransaction.notes)
# filter based on param
# id is only 1, so if it is not None just skip other filters
if ids:
q = q.filter(Item.sale_transaction_id.in_(ids))
else:
# any is useful for checking ['']
# http://stackoverflow.com/questions/11191264/python-how-to-check-list-doest-contain-any-value
if year and any(year):
q = q.filter(
extract('year',
SaleTransaction.transaction_date).in_(year))
if month and any(month):
q = q.filter(
extract('month',
SaleTransaction.transaction_date).in_(month))
if day and any(day):
q = q.filter(
extract('day', SaleTransaction.transaction_date).in_(day))
# finishing query
# outer join for optional
q = q.join(ItemType, SaleTransaction, PurchaseTransaction,
Customer) \
.outerjoin(Courier, TransactionMedium) \
.group_by(Item.sale_transaction_id, Item.item_type_id)
q = q.order_by(SaleTransaction.transaction_date.desc())
q = paginate_query(q, page_num, list_per_page)
s_list = q.all()
column_names = tuple(x['name'] for x in q.column_descriptions)
# dynamically check where is total_price column is from column_names
# add first column name 'TOTAL'
# and the total_price_id in its respective column
total_sale_price_id = column_names.index(total_sale_price_label)
total_purchase_price_id = column_names.index(
total_purchase_price_label)
profit_id = column_names.index(profit_label)
delivery_fee_id = column_names.index(delivery_fee_label)
total_row = [''] * len(column_names)
total_row[0] = 'TOTAL'
total_row[total_sale_price_id] = sum(x[total_sale_price_id]
for x in s_list)
total_row[total_purchase_price_id] = sum(x[total_purchase_price_id]
for x in s_list)
total_row[profit_id] = sum(x[profit_id] for x in s_list)
total_row[delivery_fee_id] = db.session.query(
func.sum(SaleTransaction.delivery_fee)) \
.scalar()
s_list.append(tuple(total_row))
# append description last because there is sum previously in total_row
s_list.insert(0, column_names)
return s_list
@classmethod
def try_add(cls,
date=None,
customer_id=None,
courier_id=None,
delivery_fee=None,
transaction_medium_id=None,
notes=None,
transaction_items=None):
"""
transaction_items should be a list of dict
containing item_type_id, quantity
"""
new = SaleTransaction(transaction_date=date,
customer_id=customer_id,
courier_id=courier_id,
delivery_fee=delivery_fee,
transaction_medium_id=transaction_medium_id,
notes=notes)
try:
db.session.add(new)
db.session.flush()
if cls.try_add_sale_items(new.id, transaction_items, False):
db.session.commit()
return True
except Exception as e:
cls.add_error(e)
db.session.rollback()
return False
@classmethod
def get_sale_items(cls, id):
return db.session.query(func.count(Item.id).label('quantity'),
Item.item_type_id,
Item.sale_price) \
.filter(Item.sale_transaction_id == id) \
.group_by(Item.item_type_id)
@classmethod
def try_delete_sale_items(cls, trans_id, commit=False):
items = cls.get(trans_id).items
try:
# remove sale transaction id for the item
for item in items:
item.sale_transaction_id = None
db.session.add(item)
db.session.flush()
if commit:
db.session.commit()
flash('commit deleting transaction item')
return True
except Exception as e:
cls.add_error(e)
db.session.rollback()
return False
@classmethod
def try_add_sale_items(cls, trans_id, transaction_items, commit=False):
try:
for trans_item in transaction_items:
if not ('item_type_id' in trans_item or 'quantity'
in trans_item or 'sale_price' in trans_item):
cls.add_error('transaction item missing required keys')
raise Exception('transaction item missing required keys')
# get items FIFO models
items = Item.query.join(PurchaseTransaction) \
.filter(Item.sale_transaction_id == None) \
.filter(Item.item_type_id == trans_item['item_type_id']) \
.order_by(PurchaseTransaction.transaction_date, Item.id) \
.limit(trans_item['quantity'])
# the quantity input is more than available items
# this one is internal error
if trans_item['quantity'] > items.count():
cls.add_error('The total input is exceeding total stock')
raise Exception('The total input is exceeding total stock')
# for each item add sale transaction field
for it in items:
it.sale_price = trans_item['sale_price']
it.sale_transaction_id = trans_id
db.session.add(it)
db.session.flush()
if commit:
db.session.commit()
return True
except Exception as e:
cls.add_error(e)
db.session.rollback()
return False
# TODO: Problem with total stock in the form
@classmethod
def try_edit_sale_items(cls, trans_id, transaction_items, commit=False):
if cls.try_delete_sale_items(trans_id, False) and \
cls.try_add_sale_items(trans_id, transaction_items, False):
if commit:
db.session.commit()
return True
else:
db.session.rollback()
return False
class PurchaseTransaction(STModel):
id = db.Column(db.Integer, primary_key=True)
items = db.relationship('Item',
backref='purchase_transaction',
lazy='dynamic',
cascade='save-update, merge, delete')
transaction_date = db.Column(db.DateTime, index=True, nullable=False)
supplier_id = db.Column(db.Integer,
db.ForeignKey('supplier.id'),
nullable=False)
notes = db.Column(db.String(NOTES_LENGTH))
def __init__(self, transaction_date=None, supplier_id=None, notes=None):
if transaction_date is None:
self.transaction_date = datetime.now()
else:
self.transaction_date = transaction_date
if not supplier_id:
raise TypeError(
'supplier id cannot be empty for new purchase transaction')
else:
self.supplier_id = supplier_id
self.notes = notes
@utils.classproperty
def total(cls):
# there are two ways doing this
q = db.session.query(func.count(Item.id)) \
.filter(Item.purchase_transaction_id != None) \
.group_by(Item.purchase_transaction_id, Item.item_type_id)
# in here can use q.count() but apparently it is slower
return len(q.all())
# OR
# q = db.session.query(func.count(distinct(Item.item_type_id)))\
# .filter(Item.purchase_transaction_id != None)\
# .group_by(Item.purchase_transaction_id)
# return sum(x[0] for x in q.all())
@classmethod
def get_list(cls,
page_num=DEFAULT_PAGE_NUMBER,
list_per_page=DEFAULT_POSTS_PER_PAGE,
include_header=True,
order_by=None,
ids=None,
year=None,
month=None,
day=None):
qty_label = 'quantity'
individual_price_label = 'price_(each)'
total_price_label = 'total_price'
# initial query
q = db.session.query(
Item.purchase_transaction_id.label('id'), ItemType.item_type,
PurchaseTransaction.transaction_date,
func.count(Item.id).label(qty_label),
Item.purchase_price.label(individual_price_label),
func.sum(Item.purchase_price).label(total_price_label),
Supplier.name.label('supplier'), PurchaseTransaction.notes)
# filter based on param
# id is only 1, so if it is not None just skip other filters
if ids:
q = q.filter(Item.purchase_transaction_id.in_(ids))
else:
# any is useful for checking ['']
# http://stackoverflow.com/questions/11191264/python-how-to-check-list-doest-contain-any-value
if year and any(year):
q = q.filter(
extract('year',
PurchaseTransaction.transaction_date).in_(year))
if month and any(month):
q = q.filter(
extract('month',
PurchaseTransaction.transaction_date).in_(month))
if day and any(day):
q = q.filter(
extract('day',
PurchaseTransaction.transaction_date).in_(day))
# finishing query
q = q.join(ItemType, PurchaseTransaction, Supplier) \
.group_by(Item.purchase_transaction_id, Item.item_type_id)
# I can use this to add the total row, but one problem is the date_time field needs to have valid date
# also the order by needs to be placed last, so maybe it is better to just use python?
# q = q.union_all(
# db.session.query(func.count(distinct(Item.purchase_transaction_id)),
# func.count(distinct(Item.item_type_id)),
# "'{}'".format(str(datetime.now())),
# func.count(Item.id),
# "'test2'",
# func.sum(Item.purchase_price),
# "'test3'").join(ItemType, PurchaseTransaction))
q = q.order_by(PurchaseTransaction.transaction_date.desc())
q = paginate_query(q, page_num, list_per_page)
p_list = q.all()
column_names = tuple(x['name'] for x in q.column_descriptions)
# dynamically check where is total_price column is from column_names
# add first column name 'TOTAL'
# and the total_price_id in its respective column
total_price_id = column_names.index(total_price_label)
total_row = [''] * len(column_names)
total_row[0] = 'TOTAL'
total_row[total_price_id] = sum(x[total_price_id] for x in p_list)
p_list.append(tuple(total_row))
# append description last because there is sum previously in total_row
p_list.insert(0, column_names)
return p_list
@classmethod
def try_add(cls,
date=None,
supplier_id=None,
notes=None,
transaction_items=None):
"""
Adding new transaction with date, notes and collections of items
the expected format for the items should be dictionary
with keys: purchase_price, item_type_id, supplier_id, quantity)
"""
new_trans = PurchaseTransaction(transaction_date=date,
supplier_id=supplier_id,
notes=notes)
try:
db.session.add(new_trans)
db.session.flush()
if not cls.try_add_purchase_items(new_trans.id, transaction_items,
False):
return False
db.session.commit()
return True
except Exception as e:
cls.add_error(e)
db.session.rollback()
return False
@classmethod
def try_delete(cls, id, **kwargs):
if super().try_delete(id, **kwargs):
if not validate_transactions(cls, purchase_trans=True):
cls.add_error('Transaction is deleted but fail to'
'validate transactions, there might be'
'transaction without items')
return False
return True
else:
return False
@classmethod
def get_purchase_items(cls, id):
return db.session.query(func.count(Item.id).label('quantity'),
Item.item_type_id,
Item.purchase_price) \
.filter(Item.purchase_transaction_id == id) \
.group_by(Item.item_type_id)
@classmethod
def get_purchase_item_ids(cls, trans_id, item_type_id):
return [i[0] for i in db.session.query(Item.id) \
.filter(Item.purchase_transaction_id == trans_id) \
.filter(Item.item_type_id == item_type_id).all()]
@classmethod
def try_delete_all_purchase_items(cls, trans_id, commit=False):
items = cls.get(trans_id).items
try:
for item in items:
db.session.delete(item)
db.session.flush()
if commit:
db.session.commit()
return True
except Exception as e:
cls.add_error(e)
db.session.rollback()
return False
@classmethod
def try_add_purchase_items(cls, trans_id, transaction_items, commit=False):
try:
for trans_item in transaction_items:
# check if it contains all the data needed
# this is mainly for internal error from views to models
if not ('quantity' in trans_item and 'purchase_price'
in trans_item and 'item_type_id' in trans_item):
cls.add_error('transaction item missing required keys')
raise Exception('transaction item missing required keys')
for i in range(trans_item['quantity']):
item = Item(purchase_price=trans_item['purchase_price'],
item_type_id=trans_item['item_type_id'],
purchase_transaction_id=trans_id)
db.session.add(item)
db.session.flush()
if commit:
db.session.commit()
return True
except Exception as e:
cls.add_error(e)
db.session.rollback()
return False
# problem, if you delete everything and add everything
# it will affect the sale transaction
# that's why now it is going to check whether it should add
# or delete item for the existing transaction
@classmethod
def try_edit_purchase_items(cls,
trans_id,
transaction_items,
commit=False):
for transaction_item in transaction_items:
ids = list(map(int, transaction_item['ids'].split(',')))
# update every ids until reaches total of new qty
update_ids = ids[:transaction_item['quantity']]
for id in update_ids:
item = Item.get(id)
item.item_type_id = transaction_item['item_type_id']
item.purchase_price = transaction_item['purchase_price']
db.session.add(item)
db.session.flush()
# flash('updated ids: {}'.format(id))
# discard extra id after new qty in case new qty < old qty
delete_ids = ids[transaction_item['quantity']:]
for id in delete_ids:
item = Item.get(id)
db.session.delete(item)
db.session.flush()
# flash('deleted ids: {}'.format(id))
# get new qty if total new qty is more than previous item
new_qty = max(0, transaction_item['quantity'] - len(ids))
if new_qty > 0:
# flash('new qty: {}'.format(new_qty))
items = transaction_item.copy()
items['quantity'] = new_qty
if not cls.try_add_purchase_items(trans_id, [items]):
return False
if commit:
db.session.commit()
return True
class Item(STModel):
id = db.Column(db.Integer, primary_key=True)
item_type_id = db.Column(db.Integer,
db.ForeignKey('item_type.id'),
nullable=False)
purchase_price = db.Column(db.Integer, index=True, nullable=False)
sale_price = db.Column(db.Integer, index=True)
purchase_transaction_id = db.Column(
db.Integer, db.ForeignKey('purchase_transaction.id'), nullable=False)
sale_transaction_id = db.Column(db.Integer,
db.ForeignKey('sale_transaction.id'))
@hybrid_property
def profit(self):
if self.sale_price and self.sale_transaction_id is not None:
return self.sale_price - self.purchase_price
else:
return None
@utils.classproperty
def total_item_stock(cls):
q = db.session.query(func.count(Item.id)) \
.group_by(Item.item_type_id)
return len(q.all())
@classmethod
def get_list(cls,
page_num=DEFAULT_PAGE_NUMBER,
list_per_page=DEFAULT_POSTS_PER_PAGE,
include_header=True,
order_by=None,
**kwargs):
purchase_price_label = 'purchase_price'
sale_price_label = 'sale_price'
purchase_transaction_label = 'purchase_transaction_date'
sale_transaction_label = 'sale_transaction_date'
q = db.session.query(Item.id,
ItemType.item_type,
Item.purchase_price.label(purchase_price_label),
Item.sale_price.label(sale_price_label),
PurchaseTransaction.transaction_date.label(purchase_transaction_label),
SaleTransaction.transaction_date.label(sale_transaction_label),
Supplier.name.label('supplier')) \
.outerjoin(ItemType, PurchaseTransaction, SaleTransaction, Supplier) \
.order_by(Item.id.desc())
q = paginate_query(q, page_num, list_per_page)
at_list = q.all()
column_names = tuple(x['name'] for x in q.column_descriptions)
purchase_price_id = column_names.index(purchase_price_label)
sale_price_id = column_names.index(sale_price_label)
total_row = [''] * len(column_names)
total_row[0] = 'TOTAL'
total_row[purchase_price_id] = sum(x[purchase_price_id]
for x in at_list
if x[purchase_price_id] is not None)
total_row[sale_price_id] = sum(x[sale_price_id] for x in at_list
if x[sale_price_id] is not None)
at_list.append(tuple(total_row))
at_list.insert(0, column_names)
return at_list
@classmethod
def get_stock_list(cls,
page_num=DEFAULT_PAGE_NUMBER,
list_per_page=DEFAULT_POSTS_PER_PAGE):
q = db.session.query(Item.item_type_id,
ItemType.item_type,
(func.count(Item.purchase_transaction_id) - func.count(Item.sale_transaction_id)).label(
'stock_qty'),
func.count(Item.sale_transaction_id).label('sold_qty'),
func.count(Item.id).label('total_qty'),
func.sum(Item.profit).label('total_proft_from_sold')) \
.join(ItemType) \
.group_by(Item.item_type_id) \
.order_by(Item.item_type_id)
q = paginate_query(q, page_num, list_per_page)
is_list = q.all()
is_list.insert(0, tuple(x['name'] for x in q.column_descriptions))
return is_list
class Character(db.Model):
id = db.Column(db.Integer, primary_key=True)
sheet = db.Column(db.JSON)
owner_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)