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()