class Item(db.Model, BaseMixin, AuditableMixin):
"""Inventory model
"""
code = db.Column(db.String(200), nullable=False) # item number
name = db.Column(db.String(200), unique=True, nullable=False)
description = db.Column(db.String(2000))
category_id = db.Column(db.String(50), db.ForeignKey("item_category.uuid"))
model_id = db.Column(db.String(50))
price = db.Column(db.String(50))
item_type = db.Column(db.String(50))
make_id = db.Column(db.String(50))
entity_id = db.Column(db.String(50), db.ForeignKey("entity.uuid"))
entity = db.relationship("Entity")
category = db.relationship("ItemCategory")
def __init__(self, **kwargs):
super(Item, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<Item %s>" % self.name
@property
def quantity(self):
"""Get the item balance."""
try:
return query(
" quantity from item_balances where uuid='" + str(self.uuid) + "'"
)[0]["quantity"]
except Exception:
return 0
class Employee(db.Model, BaseMixin, AuditableMixin):
"""Basic Employee model
"""
name = db.Column(db.String(80))
phone = db.Column(db.String(80), nullable=False)
email = db.Column(db.String(50))
address = db.Column(db.String(500))
entity_id = db.Column(db.String(50),
db.ForeignKey("entity.uuid"),
nullable=False)
type_id = db.Column(db.String(50),
db.ForeignKey("employee_type.uuid"),
nullable=False)
entity = db.relationship("Entity")
type = db.relationship("EmployeeType")
jobs = db.relationship("Job", back_populates="employee")
def __init__(self, **kwargs):
super(Employee, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<Employee %s>" % self.uuid
@property
def entity(self):
entity = Entity.get(uuid=self.entity_id)
if entity is None:
return None
return entity.name
class JobItem(db.Model, BaseMixin, AuditableMixin):
job_id = db.Column(db.String(50), db.ForeignKey("job.uuid"))
item_id = db.Column(db.String(50), db.ForeignKey("item.uuid"))
quantity = db.Column(db.String(50))
unit_cost = db.Column(db.String(50))
units = db.Column(db.String(50))
entity_id = db.Column(db.String(50), db.ForeignKey("entity.uuid"))
item = db.relationship("Item")
job = db.relationship("Job")
entity = db.relationship("Entity")
def __init__(self, **kwargs):
super(JobItem, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<JobItem %s>" % self.uuid
@property
def cost(self):
try:
return int(self.quantity) * int(self.unit_cost)
except Exception:
return None
def save(self):
item_log = ItemLog.init_jobitem(self)
db.session.add(self)
item_log.transact()
class WorkItem(db.Model, BaseMixin, AuditableMixin):
request_id = db.Column(db.String(50),
db.ForeignKey("service_request.uuid"))
item = db.Column(db.String(50))
quantity = db.Column(db.String(50))
unit_cost = db.Column(db.String(50))
units = db.Column(db.String(50))
entity_id = db.Column(db.String(50), db.ForeignKey("entity.uuid"))
request = db.relationship("ServiceRequest")
entity = db.relationship("Entity")
def __init__(self, **kwargs):
super(WorkItem, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<WorkItem %s>" % self.uuid
@property
def cost(self):
try:
return int(self.quantity) * int(self.unit_cost)
except Exception:
return None
class Customer(db.Model, BaseMixin, AuditableMixin):
"""Basic Customer model
"""
name = db.Column(db.String(80))
phone = db.Column(db.String(80), nullable=False)
email = db.Column(db.String(50))
address = db.Column(db.String(500))
entity_id = db.Column(db.String(50),
db.ForeignKey("entity.uuid"),
nullable=False)
type_id = db.Column(db.String(50),
db.ForeignKey("customer_type.uuid"),
nullable=False)
vehicles = db.relationship("Vehicle", back_populates="customer")
def __init__(self, **kwargs):
super(Customer, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<Customer %s>" % self.uuid
@property
def entity(self):
entity = Entity.get(uuid=self.entity_id)
if entity is None:
return None
return entity.name
@property
def account(self):
return Account.get(owner_id=self.uuid)
class Vehicle(db.Model, BaseMixin, AuditableMixin):
registration_no = db.Column(db.String(50))
chassis_no = db.Column(db.String(50))
model_id = db.Column(db.String(50), db.ForeignKey("vehicle_model.uuid"))
model_no = db.Column(db.String(50))
engine_no = db.Column(db.String(50))
vehicle_type = db.Column(db.String(50))
customer_id = db.Column(db.String(50), db.ForeignKey("customer.uuid"))
customer = db.relationship("Customer", back_populates="vehicles")
vehicle_model = db.relationship("VehicleModel")
def __init__(self, **kwargs):
super(Vehicle, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<Vehicle %s>" % self.uuid
class VehicleModel(db.Model, BaseMixin, AuditableMixin):
name = db.Column(db.String(50), unique=True, nullable=False)
fuel_type = db.Column(db.String(50))
type_id = db.Column(db.String(50), db.ForeignKey("vehicle_type.uuid"))
transmission = db.Column(db.String(50))
year = db.Column(db.Integer)
make_id = db.Column(db.Integer, db.ForeignKey("make.id"))
make = db.relationship("Make")
type = db.relationship("VehicleType")
def __init__(self, **kwargs):
super(VehicleModel, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<VehicleModel %s>" % self.uuid
class Tarriff(db.Model, BaseMixin, AuditableMixin):
"""Tarriff model : Contains charges
"""
name = db.Column(db.String(50))
entity_id = db.Column(db.String(50))
tran_type = db.Column(db.String(50))
payment_type = db.Column(db.String(50)) # [flat,percentage,tiered]
charges = db.relationship("Charge",
cascade="all, delete-orphan",
lazy="dynamic")
splits = db.relationship("ChargeSplit",
cascade="all, delete-orphan",
lazy="dynamic")
def __init__(self, **kwargs):
super(Tarriff, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<Tarriff %s>" % self.name
class Model(db.Model, BaseMixin, AuditableMixin):
name = db.Column(db.String(50), unique=True, nullable=False)
year = db.Column(db.Integer)
make_id = db.Column(db.Integer, db.ForeignKey("make.id"))
make = db.relationship("Make")
def __init__(self, **kwargs):
super(Make, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<Model %s>" % self.uuid
class ServiceRequest(db.Model, BaseMixin, AuditableMixin):
""""
Service Request
"""
customer_id = db.Column(db.String(50), db.ForeignKey("customer.uuid"))
service_id = db.Column(db.String(50), db.ForeignKey("service.uuid"))
vehicle_id = db.Column(db.String(50), db.ForeignKey("vehicle.uuid"))
entity_id = db.Column(db.String(50), db.ForeignKey("entity.uuid"))
customer = db.relationship("Customer")
service = db.relationship("Service")
vehicle = db.relationship("Vehicle")
entity = db.relationship("Entity")
job = db.relationship("Job")
def __init__(self, **kwargs):
super(ServiceRequest, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<ServiceRequest %s>" % self.uuid
class Entity(db.Model, BaseMixin, AuditableMixin):
"""Entity model
"""
name = db.Column(db.String(80), unique=True, nullable=False)
email = db.Column(db.String(80), unique=True, nullable=False)
address = db.Column(db.String(255), nullable=False)
phone = db.Column(db.String(50))
description = db.Column(db.String(4000))
vendors = db.relationship(
"Vendor",
secondary=entity_vendors,
backref=db.backref("entities", lazy="dynamic"),
lazy="dynamic",
)
customers = db.relationship("Customer", lazy="dynamic")
def __init__(self, **kwargs):
super(Entity, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<Entity %s>" % self.name
@property
def account(self):
return Account.get(owner_id=self.uuid)
def add_vendor(self, vendor):
if not self.is_entity_vendor(vendor):
self.vendors.append(vendor)
def remove_vendor(self, vendor):
if self.is_entity_vendor(vendor):
self.vendors.remove(vendor)
def is_entity_vendor(self, vendor):
return self.vendors.filter(
entity_vendors.c.vendor_id == vendor.id).count() > 0
class LpoItem(db.Model, BaseMixin, AuditableMixin):
order_id = db.Column(db.String(50),
db.ForeignKey('local_purchase_order.uuid'))
item_id = db.Column(db.String(80),
db.ForeignKey("item.uuid"),
nullable=False)
quantity = db.Column(db.String(50))
unit_price = db.Column(db.String(50))
entity_id = db.Column(db.String(50), db.ForeignKey('entity.uuid'))
item = db.relationship('Item')
order = db.relationship('LocalPurchaseOrder')
entity = db.relationship('Entity')
def __init__(self, **kwargs):
super(LpoItem, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<LpoItem %s>" % self.uuid
@property
def amount(self):
return float(self.quantity) * float(self.unit_price)
class Service(db.Model, BaseMixin, AuditableMixin):
"""Service model
"""
name = db.Column(db.String(200), unique=True, nullable=False)
description = db.Column(db.String(4000))
entity_id = db.Column(db.String(50), db.ForeignKey("entity.uuid"))
entity = db.relationship("Entity")
def __init__(self, **kwargs):
super(Service, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<Service %s>" % self.name
class ItemCategory(db.Model, BaseMixin, AuditableMixin):
name = db.Column(db.String(200), unique=True, nullable=False)
description = db.Column(db.String(2000))
parent_id = db.Column(db.String(50))
entity_id = db.Column(db.String(50), db.ForeignKey("entity.uuid"))
entity = db.relationship("Entity")
def __init__(self, **kwargs):
super(ItemCategory, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<ItemCategory %s>" % self.name
@property
def parent(self):
if self.parent_id and self.parent_id != "0":
return ItemCategory.get(uuid=self.parent_id).name
else:
return None
class TokenBlacklist(db.Model):
"""Blacklist representation
"""
id = db.Column(db.Integer, primary_key=True)
jti = db.Column(db.String(36), nullable=False, unique=True)
token_type = db.Column(db.String(10), nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
revoked = db.Column(db.Boolean, nullable=False)
expires = db.Column(db.DateTime, nullable=False)
user = db.relationship("User", lazy="joined")
def to_dict(self):
return {
"token_id": self.id,
"jti": self.jti,
"token_type": self.token_type,
"user_identity": self.user_identity,
"revoked": self.revoked,
"expires": self.expires,
}
class Expenditure(db.Model, BaseMixin, AuditableMixin):
"""Expenditure model: expenses, purchases, payouts, salaries etc
"""
reference = db.Column(db.String(50))
amount = db.Column(db.String(50))
phone = db.Column(db.String(80))
pay_type = db.Column(db.String(50))
on_credit = db.Column(db.Boolean, default=False)
category = db.Column(db.String(50))
credit_status = db.Column(db.String(50), default='NONE')
narration = db.Column(db.String(4000))
entity_id = db.Column(db.String(50),
db.ForeignKey("entity.uuid"),
nullable=False)
vendor_id = db.Column(db.String(50),
db.ForeignKey("vendor.uuid"),
nullable=False)
entity = db.relationship('Entity')
vendor = db.relationship('Vendor')
def __init__(self, **kwargs):
super(Expenditure, self).__init__(**kwargs)
if self.pay_type == 'credit':
self.on_credit = True
self.credit_status = 'PENDING'
self.get_uuid()
def __repr__(self):
return "<Expenditure of {0} on {1}>".format(self.amount, self.item)
@property
def credit(self):
entries = Entry.query.filter_by(cheque_number=self.reference).all()
count = 0
paid = 0
for entry in entries:
if 'credit' not in entry.reference:
paid += entry.amount
count += 1
return {
"paid": float(paid),
"balance": float(self.amount) - float(paid),
"payments": count
}
def clear_credit(self, amount_to_pay):
"""Check if expenses on credit are cleared"""
if not self.on_credit:
raise Exception('This is not a credit expense')
paid = self.credit['paid']
actual_bal = self.credit['balance']
count = self.credit['payments']
balance = float(self.amount) - (float(paid) + float(amount_to_pay))
self.credit_status = 'PAID' if int(balance) == 0 else 'PARTIAL'
app.logger.info(self.credit_status)
if balance < 0.0:
raise Exception(
'Amount to pay should not be greater than the balance {2}'.
format(commas(actual_bal)))
else:
entry = Entry.init_expenditure(self)
entry.amount = amount_to_pay
entry.reference = self.uuid + str(count)
entry.transact()
@staticmethod
def init_lpo(lpo):
exp = Expenditure(reference=lpo.uuid,
amount=lpo.amount,
phone='',
pay_type=lpo.pay_type,
category='purchase',
narration=lpo.narration,
entity_id=lpo.entity_id,
vendor_id=lpo.vendor_id)
exp.create()
def create(self):
entry = Entry.init_expenditure(self)
self.save()
entry.transact()
class Job(db.Model, BaseMixin, AuditableMixin):
""""
job cards
"""
employee_id = db.Column(db.String(50),
db.ForeignKey("employee.uuid"),
nullable=False)
request_id = db.Column(db.String(50),
db.ForeignKey("service_request.uuid"),
nullable=False)
entity_id = db.Column(db.String(50),
db.ForeignKey("entity.uuid"),
nullable=False)
is_complete = db.Column(db.Boolean, default=False)
completed_date = db.Column(db.DateTime(timezone=True),
default=datetime.datetime.utcnow)
completed_by = db.Column(db.Integer, db.ForeignKey("users.id"))
employee = db.relationship("Employee")
request = db.relationship("ServiceRequest")
entity = db.relationship("Entity")
def __init__(self, **kwargs):
super(Job, self).__init__(**kwargs)
self.get_uuid()
def __repr__(self):
return "<Job %s>" % self.employee_id
@property
def time(self):
if self.completed_date:
import datetime
from dateutil.relativedelta import relativedelta
start = self.date_created
ends = self.completed_date
diff = relativedelta(ends, start)
return {
"years":
diff.years,
"months":
diff.months,
"days":
diff.days,
"hours":
diff.hours,
"minutes":
diff.minutes,
"word":
"%d year %d month %d days %d hours %d minutes" %
(diff.years, diff.months, diff.days, diff.hours, diff.minutes)
}
else:
return None
def complete(self):
from autoshop.models.item import Item, ItemLog
from autoshop.commons.util import random_tran_id
job = Job.get(uuid=self.uuid)
hours = job.time['hours']
days_in_hours = job.time['days'] * 24
time = hours + days_in_hours
if time == 0 and job.time['minutes'] > 0:
time = 1
item = Item.get(code='labour')
if item:
log = ItemLog(item_id=item.uuid,
debit=item.uuid,
credit=item.entity_id,
reference=random_tran_id(),
category='sale',
quantity=time,
unit_cost=item.price,
amount=time * int(item.price),
entity_id=item.entity_id)
job_item = JobItem(job_id=self.uuid,
item_id=item.uuid,
quantity=time,
unit_cost=item.price,
entity_id=item.entity_id)
db.session.add(job_item)
db.session.add(log)
db.session.commit()
class ItemLog(db.Model, BaseMixin, AuditableMixin):
"""Inventory log model to track usage
If a purchase is made, debit is vendor id and credit is item id
if a sale is mafe, debit is item id and credit is entity_id
"""
item_id = db.Column(db.String(50), db.ForeignKey("item.uuid"))
reference = db.Column(db.String(50)) # job id or vendor id
category = db.Column(db.String(50)) # sale or purchase
credit = db.Column(db.String(50))
debit = db.Column(db.String(50))
quantity = db.Column(db.Integer)
unit_cost = db.Column(db.String(50))
pay_type = db.Column(db.String(50))
on_credit = db.Column(db.Boolean, default=False)
credit_status = db.Column(db.String(50), default='NONE')
amount = db.Column(db.Numeric(20, 2), CheckConstraint("amount > 0.0"))
entity_id = db.Column(db.String(50), db.ForeignKey("entity.uuid"))
accounting_date = db.Column(db.Date, default=db.func.now())
accounting_period = db.Column(db.String(50), default=db.func.now())
entity = db.relationship("Entity")
item = db.relationship("Item")
def __init__(self, **kwargs):
super(ItemLog, self).__init__(**kwargs)
self.accounting_period = datetime.now().strftime("%Y-%m")
self.get_uuid()
def __repr__(self):
return "<ItemLog %s>" % self.item_id
@property
def debit_account(self):
sql = (
""" name FROM item_accounts where item_accounts.uuid ='"""
+ str(self.debit)
+ """'
"""
)
data = query(sql)
return data if data is None else data[0]["name"]
@property
def credit_account(self):
sql = (
""" name FROM item_accounts where item_accounts.uuid ='"""
+ str(self.credit)
+ """'"""
)
data = query(sql)
return data if data is None else data[0]["name"]
def is_valid(self):
"""validate the object"""
if self.category not in ('sale', 'purchase'):
return False, {"msg": "The category {0} doesn't exist".format(self.tran_type)}, 422
if not Item.get(uuid=self.credit) and not Item.get(uuid=self.debit):
return False, {"msg": "The supplied item id does not exist"}, 422
if ItemLog.get(reference=self.reference):
return False, {"msg": "The supplied reference already exists"}, 409
if ItemLog.get(reference=self.cheque_number):
return False, {"msg": "This transaction is already reversed"}, 409
if self.category == "reversal" and not ItemLog.get(
reference=self.cheque_number
):
return False, {"msg": "You can only reverse an existing transaction"}, 422
# check balance
item = Item.get(uuid=self.debit)
bal_after = int(item.quantity) - int(self.quantity)
app.logger.info(item.quantity, self.quantity)
if Item.get(uuid=self.item_id).name != 'labour' and self.category == 'sale' and float(bal_after) < 0.0:
return False, {
"msg": "Insufficient quantity on the {0} account {1}".format(item.name, commas(item.quantity))}, 409
if self.tran_type == "reversal":
orig = ItemLog.get(reference=self.cheque_number)
self.debit = orig.credit
self.credit = orig.debit
self.amount = orig.amount
self.entity_id = orig.entity_id
return True, self, 200
@staticmethod
def init_jobitem(job_item):
item_log = ItemLog(
item_id=job_item.item_id,
debit=job_item.item_id,
credit=job_item.entity_id,
reference=job_item.job_id,
category='sale',
quantity=job_item.quantity,
amount=job_item.cost,
unit_cost=job_item.unit_cost,
entity_id=job_item.entity_id,
)
valid, reason, status = item_log.is_valid()
if not valid:
raise Exception(reason.get('msg'), status)
return item_log
def transact(self):
"""
:rtype: object
If a customer is invoiced, value is debited off their account onto
the entity account, when they make a payment
1. Value is debited off the `escrow` onto the customer account
2. Value is also moved off the entity account onto the pay type account
In the future, the payment type account ought to be created per entity
"""
valid, reason, status = self.is_valid()
if not valid:
raise Exception(reason.get('msg'), status)
db.session.commit()
class LocalPurchaseOrder(db.Model, BaseMixin, AuditableMixin, CreditMixin):
"""Basic LPO model
"""
entity_id = db.Column(db.String(50),
db.ForeignKey("entity.uuid"),
nullable=False)
vendor_id = db.Column(db.String(50),
db.ForeignKey("vendor.uuid"),
nullable=False)
amount = db.Column(db.String(50), default='0')
narration = db.Column(db.String(50))
status = db.Column(db.String(50), default='PENDING')
entity = db.relationship('Entity')
vendor = db.relationship('Vendor')
def __init__(self, **kwargs):
super(LocalPurchaseOrder, self).__init__(**kwargs)
if self.pay_type == 'credit':
self.on_credit = True
self.credit_status = 'PENDING'
self.get_uuid()
def __repr__(self):
return "<LocalPurchaseOrder %s>" % self.uuid
@property
def items(self):
return LpoItem.query.filter_by(order_id=self.uuid).count()
def log_items(self):
logs = []
# entries = []
items = LpoItem.query.filter_by(order_id=self.uuid).all()
if not items:
raise Exception("No items found in LPO. Please add some items")
total = 0
self.status = 'COMPLETED'
for item in items:
log = ItemLog(item_id=item.item_id,
debit=self.vendor_id,
credit=item.item_id,
reference=self.uuid,
category='purchase',
quantity=item.quantity,
unit_cost=item.unit_price,
amount=int(item.unit_price) * int(item.quantity),
entity_id=item.entity_id,
pay_type=self.pay_type)
logs.append(log)
total += int(log.amount)
self.amount = total
if not Expenditure.get(uuid=self.uuid):
db.session.add_all(logs)
db.session.commit()
Expenditure.init_lpo(self)
def clear_credit(self, amount_to_pay):
"""Check if expenses on credit are cleared"""
if not self.on_credit:
return
paid = self.credit['paid']
actual_bal = self.credit['balance']
count = self.credit['payments']
balance = float(self.amount) - (float(paid) + float(amount_to_pay))
self.credit_status = 'PAID' if int(balance) == 0 else 'PARTIAL'
if balance < 0.0:
raise Exception(
'Amount to pay should not be greater than the balance {2}'.
format(commas(actual_bal)))
else:
exp = Expenditure.get(reference=self.uuid)
exp.amount = amount_to_pay
exp.pay_type = self.pay_type
entry = Entry.init_expenditure(exp)
entry.reference = self.uuid + str(count)
entry.transact()
