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 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 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 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 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 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 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 User(db.Model, UserMixin):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(20), unique=True, nullable=False)
email = db.Column(db.String(40), unique=True, nullable=False)
password = db.Column(db.String(60), nullable=False)
activated = db.Column(db.Boolean, default=False)
sheets = db.relationship('Character', backref='owner', lazy=True)
def get_token(self, expires=600):
s = Serializer(app.config['SECRET_KEY'], expires)
return s.dumps({'user_id': self.id}).decode('utf-8')
@staticmethod
def verify_token(token):
s = Serializer(app.config['SECRET_KEY'])
try:
user_id = s.loads(token)['user_id']
except Exception:
return None
return User.query.get(user_id)
class Supplier(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))
purchase_transactions = db.relationship(
'PurchaseTransaction',
backref='supplier',
lazy='dynamic',
cascade='save-update, merge, delete')
@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 = Supplier.query.with_entities(Supplier.id, Supplier.name,
Supplier.contact, Supplier.address)
q = paginate_query(q, page_num, list_per_page)
s_list = q.all()
s_list.insert(0, tuple(x['name'] for x in q.column_descriptions))
return s_list
@classmethod
def try_add(cls, name, contact, address):
new = Supplier(name=name, contact=contact, address=address)
try:
db.session.add(new)
db.session.commit()
return True
except Exception as e:
cls.add_error(e)
db.session.rollback()
return 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 ItemType(STModel):
id = db.Column(db.Integer, primary_key=True)
item_type = db.Column(db.String(NAME_LENGTH),
index=True,
unique=True,
nullable=False)
items = db.relationship('Item',
backref='item_type',
lazy='dynamic',
cascade='save-update, merge, delete')
@classmethod
def format_item_type(cls, text):
return '_'.join(text.strip().upper().split())
@classmethod
def check_exist(cls, item_type):
return db.session.query(
exists().where(ItemType.item_type == ItemType.format_item_type(
item_type))).scalar()
@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 = ItemType.query.with_entities(ItemType.id, ItemType.item_type)
q = paginate_query(q, page_num, list_per_page)
if order_by:
q = q.order_by(order_by)
it_list = q.all()
if include_header:
it_list.insert(0, tuple(x['name'] for x in q.column_descriptions))
return it_list
@classmethod
def try_add(cls, item_type):
# convert name to correct one
# if item exists in db already -> it has been checked by
# the model and form
item_type = ItemType.format_item_type(item_type)
new = ItemType(item_type=item_type)
try:
db.session.add(new)
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, True, True):
cls.add_error('Fail to validate transaction, item type is '
'deleted but some transactions does not have '
'items referenced into it')
return False
return True
else:
return False
class Station(db.Model):
__tablename__ = 'stations'
id = db.Column(db.Integer, primary_key=True)
station_no = db.Column(db.Integer, nullable=True)
favorites = db.relationship(
'FavoriteStation', backref='stations', lazy='dynamic')
