diff --git a/rattail/db/extension/enum.py b/rattail/db/extension/enum.py index 4be5d72824bbaba49a2fde8f9df1f7bf41eb1c44..02c5d8812981387e3dfeca0f07318a598dbb8d4e 100644 --- a/rattail/db/extension/enum.py +++ b/rattail/db/extension/enum.py @@ -58,6 +58,24 @@ EMPLOYEE_STATUS = { } +PRICE_TYPE_REGULAR = 0 +PRICE_TYPE_TPR = 1 +PRICE_TYPE_SALE = 2 +PRICE_TYPE_MANAGER_SPECIAL = 3 +PRICE_TYPE_ALTERNATE = 4 +PRICE_TYPE_FREQUENT_SHOPPER = 5 +PRICE_TYPE_MFR_SUGGESTED = 901 + +PRICE_TYPE = { + PRICE_TYPE_REGULAR : "Regular Price", + PRICE_TYPE_TPR : "TPR", + PRICE_TYPE_SALE : "Sale", + PRICE_TYPE_MANAGER_SPECIAL : "Manager Special", + PRICE_TYPE_ALTERNATE : "Alternate Price", + PRICE_TYPE_FREQUENT_SHOPPER : "Frequent Shopper", + PRICE_TYPE_MFR_SUGGESTED : "Manufacturer's Suggested", + } + # VENDOR_CATALOG_NOT_PARSED = 1 # VENDOR_CATALOG_PARSED = 2 # VENDOR_CATALOG_COGNIZED = 3 diff --git a/rattail/db/extension/model.py b/rattail/db/extension/model.py index 9e4c2934433a6a2033be114c85a24ffb82fe4442..d659becb1ce8ba7411e3e72dcec267f9b512bac0 100644 --- a/rattail/db/extension/model.py +++ b/rattail/db/extension/model.py @@ -34,6 +34,7 @@ from sqlalchemy import (Column, String, Integer, Date, DateTime, Boolean, Text, ForeignKey, BigInteger, Numeric) from sqlalchemy.orm import relationship, object_session from sqlalchemy.ext.associationproxy import association_proxy +from sqlalchemy.ext.orderinglist import ordering_list import edbob from edbob.db.model import Base, uuid_column @@ -43,7 +44,7 @@ from edbob.db.extensions.auth import Person __all__ = ['SilColumn', 'BatchDictionaryColumn', 'BatchDictionary', 'BatchTerminalColumn', 'BatchTerminal', 'BatchColumn', 'Batch', 'Brand', 'Department', 'Subdepartment', 'Category', - 'Product', 'Employee'] + 'Product', 'Employee', 'Vendor', 'ProductCost', 'ProductPrice'] sil_type_pattern = re.compile(r'^(CHAR|NUMBER)\((\d+(?:\,\d+)?)\)$') @@ -577,6 +578,82 @@ class Category(Base): return unicode(self.name or '') +class Vendor(Base): + """ + Represents a vendor from which products are purchased by the store. + """ + + __tablename__ = 'vendors' + + uuid = uuid_column() + id = Column(String(15)) + name = Column(String(40)) + + def __repr__(self): + return "" % self.name + + def __unicode__(self): + return unicode(self.name or '') + + +class ProductCost(Base): + """ + Represents a source from which a product may be obtained via purchase. + """ + + __tablename__ = 'product_costs' + + uuid = uuid_column() + product_uuid = Column(String(32), ForeignKey('products.uuid'), nullable=False) + vendor_uuid = Column(String(32), ForeignKey('vendors.uuid'), nullable=False) + preference = Column(Integer, nullable=False) + code = Column(String(15)) + + case_size = Column(Integer) + case_cost = Column(Numeric(9,5)) + pack_size = Column(Integer) + pack_cost = Column(Numeric(9,5)) + unit_cost = Column(Numeric(9,5)) + effective = Column(DateTime) + + # case_pack = Column(Integer) + # case_qty = Column(Integer) + # pack_qty = Column(Integer) + # # suggested_retail = Column(Numeric(6,2)) + # case_cost = Column(Numeric(9,5)) + # unit_cost = Column(Numeric(9,5)) + # # cost_effective = Column(DateTime) + # # cost_expires = Column(DateTime) + # # cost_recorded = Column(DateTime, default=datetime.datetime.now) + + vendor = relationship(Vendor) + + def __repr__(self): + return "" % (self.product, self.vendor) + + +class ProductPrice(Base): + """ + Represents a price for a product. + """ + + __tablename__ = 'product_prices' + + uuid = uuid_column() + product_uuid = Column(String(32), ForeignKey('products.uuid'), nullable=False) + type = Column(Integer) + level = Column(Integer) + starts = Column(DateTime) + ends = Column(DateTime) + price = Column(Numeric(8,3)) + multiple = Column(Integer) + pack_price = Column(Numeric(8,3)) + pack_multiple = Column(Integer) + + def __repr__(self): + return "" % (self.product, self.price) + + class Product(Base): """ Represents a product for sale and/or purchase. @@ -593,22 +670,56 @@ class Product(Base): description = Column(String(60)) description2 = Column(String(60)) size = Column(String(30)) - regular_price = Column(Numeric(10,4)) - package_price = Column(Numeric(10,4)) - package_price_quantity = Column(Integer) - sale_price = Column(Numeric(10,4)) - sale_price_quantity = Column(Integer) + + regular_price_uuid = Column( + String(32), + ForeignKey('product_prices.uuid', + use_alter=True, + name='products_regular_price_uuid_fkey')) + + current_price_uuid = Column( + String(32), + ForeignKey('product_prices.uuid', + use_alter=True, + name='products_current_price_uuid_fkey')) + + # regular_price = Column(Numeric(10,4)) + # # package_price = Column(Numeric(10,4)) + # # package_price_quantity = Column(Integer) + # sale_price = Column(Numeric(10,4)) + # sale_price_quantity = Column(Integer) + + # case_quantity = Column(Integer) + # pack_quantity = Column(Integer) + # pack_price = Column(Numeric(8,3)) department = relationship(Department) subdepartment = relationship(Subdepartment) category = relationship(Category) brand = relationship(Brand) + costs = relationship(ProductCost, backref='product', + collection_class=ordering_list('preference', count_from=1), + order_by=ProductCost.preference) def __repr__(self): return "" % self.description - def __str__(self): - return str(self.description or '') + def __unicode__(self): + return unicode(self.description or '') + +Product.prices = relationship( + ProductPrice, backref='product', + primaryjoin=ProductPrice.product_uuid == Product.uuid) + +Product.regular_price = relationship( + ProductPrice, + primaryjoin=Product.regular_price_uuid == ProductPrice.uuid, + post_update=True) + +Product.current_price = relationship( + ProductPrice, + primaryjoin=Product.current_price_uuid == ProductPrice.uuid, + post_update=True) class Employee(Base):