# -*- coding: utf-8; -*-
################################################################################
#
# Sideshow -- Case/Special Order Tracker
# Copyright © 2024-2025 Lance Edgar
#
# This file is part of Sideshow.
#
# Sideshow is free software: you can redistribute it and/or modify it
# under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# Sideshow is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with Sideshow. If not, see <http://www.gnu.org/licenses/>.
#
################################################################################
"""
Data models for Orders
"""
import datetime
import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.ext.orderinglist import ordering_list
from wuttjamaican.db import model
[docs]
class OrderMixin: # pylint: disable=too-few-public-methods
"""
Mixin class providing common columns for orders and new order
batches.
"""
store_id = sa.Column(
sa.String(length=10),
nullable=True,
doc="""
ID of the store to which the order pertains, if applicable.
""",
)
customer_id = sa.Column(
sa.String(length=20),
nullable=True,
doc="""
Proper account ID for the :term:`external customer` to which the
order pertains, if applicable.
See also :attr:`local_customer` and :attr:`pending_customer`.
""",
)
customer_name = sa.Column(
sa.String(length=100),
nullable=True,
doc="""
Name for the customer account.
""",
)
phone_number = sa.Column(
sa.String(length=20),
nullable=True,
doc="""
Phone number for the customer.
""",
)
email_address = sa.Column(
sa.String(length=255),
nullable=True,
doc="""
Email address for the customer.
""",
)
total_price = sa.Column(
sa.Numeric(precision=10, scale=3),
nullable=True,
doc="""
Full price (not including tax etc.) for all items on the order.
""",
)
[docs]
class OrderItemMixin: # pylint: disable=too-few-public-methods
"""
Mixin class providing common columns for order items and new order
batch rows.
"""
product_id = sa.Column(
sa.String(length=20),
nullable=True,
doc="""
Proper ID for the :term:`external product` which the order item
represents, if applicable.
See also :attr:`local_product` and :attr:`pending_product`.
""",
)
product_scancode = sa.Column(
sa.String(length=14),
nullable=True,
doc="""
Scancode for the product, as string.
.. note::
This column allows 14 chars, so can store a full GPC with check
digit. However as of writing the actual format used here does
not matter to Sideshow logic; "anything" should work.
That may change eventually, depending on POS integration
scenarios that come up. Maybe a config option to declare
whether check digit should be included or not, etc.
""",
)
product_brand = sa.Column(
sa.String(length=100),
nullable=True,
doc="""
Brand name for the product - up to 100 chars.
""",
)
product_description = sa.Column(
sa.String(length=255),
nullable=True,
doc="""
Description for the product - up to 255 chars.
""",
)
product_size = sa.Column(
sa.String(length=30),
nullable=True,
doc="""
Size of the product, as string - up to 30 chars.
""",
)
product_weighed = sa.Column(
sa.Boolean(),
nullable=True,
doc="""
Flag indicating the product is sold by weight; default is null.
""",
)
department_id = sa.Column(
sa.String(length=10),
nullable=True,
doc="""
ID of the department to which the product belongs, if known.
""",
)
department_name = sa.Column(
sa.String(length=30),
nullable=True,
doc="""
Name of the department to which the product belongs, if known.
""",
)
special_order = sa.Column(
sa.Boolean(),
nullable=True,
doc="""
Flag indicating the item is a "special order" - e.g. something not
normally carried by the store. Default is null.
""",
)
vendor_name = sa.Column(
sa.String(length=50),
nullable=True,
doc="""
Name of vendor from which product may be purchased, if known. See
also :attr:`vendor_item_code`.
""",
)
vendor_item_code = sa.Column(
sa.String(length=20),
nullable=True,
doc="""
Item code (SKU) to use when ordering this product from the vendor
identified by :attr:`vendor_name`, if known.
""",
)
case_size = sa.Column(
sa.Numeric(precision=10, scale=4),
nullable=True,
doc="""
Case pack count for the product, if known.
If this is not set, then customer cannot order a "case" of the item.
""",
)
order_qty = sa.Column(
sa.Numeric(precision=10, scale=4),
nullable=False,
doc="""
Quantity (as decimal) of product being ordered.
This must be interpreted along with :attr:`order_uom` to determine
the *complete* order quantity, e.g. "2 cases".
""",
)
order_uom = sa.Column(
sa.String(length=10),
nullable=False,
doc="""
Code indicating the unit of measure for product being ordered.
This should be one of the codes from
:data:`~sideshow.enum.ORDER_UOM`.
Sideshow will treat :data:`~sideshow.enum.ORDER_UOM_CASE`
differently but :data:`~sideshow.enum.ORDER_UOM_UNIT` and others
are all treated the same (i.e. "unit" is assumed).
""",
)
unit_cost = sa.Column(
sa.Numeric(precision=9, scale=5),
nullable=True,
doc="""
Cost of goods amount for one "unit" (not "case") of the product,
as decimal to 4 places.
""",
)
unit_price_reg = sa.Column(
sa.Numeric(precision=8, scale=3),
nullable=True,
doc="""
Regular price for the item unit. Unless a sale is in effect,
:attr:`unit_price_quoted` will typically match this value.
""",
)
unit_price_sale = sa.Column(
sa.Numeric(precision=8, scale=3),
nullable=True,
doc="""
Sale price for the item unit, if applicable. If set, then
:attr:`unit_price_quoted` will typically match this value. See
also :attr:`sale_ends`.
""",
)
sale_ends = sa.Column(
sa.DateTime(timezone=True),
nullable=True,
doc="""
End date/time for the sale in effect, if any.
This is only relevant if :attr:`unit_price_sale` is set.
""",
)
unit_price_quoted = sa.Column(
sa.Numeric(precision=8, scale=3),
nullable=True,
doc="""
Quoted price for the item unit. This is the "effective" unit
price, which is used to calculate :attr:`total_price`.
This price does *not* reflect the :attr:`discount_percent`. It
normally should match either :attr:`unit_price_reg` or
:attr:`unit_price_sale`.
See also :attr:`case_price_quoted`, if applicable.
""",
)
case_price_quoted = sa.Column(
sa.Numeric(precision=8, scale=3),
nullable=True,
doc="""
Quoted price for a "case" of the item, if applicable.
This is mostly for display purposes; :attr:`unit_price_quoted` is
used for calculations.
""",
)
discount_percent = sa.Column(
sa.Numeric(precision=5, scale=3),
nullable=True,
doc="""
Discount percent to apply when calculating :attr:`total_price`, if
applicable.
""",
)
total_price = sa.Column(
sa.Numeric(precision=8, scale=3),
nullable=True,
doc="""
Full price (not including tax etc.) which the customer is quoted
for the order item.
This is calculated using values from:
* :attr:`unit_price_quoted`
* :attr:`order_qty`
* :attr:`order_uom`
* :attr:`case_size`
* :attr:`discount_percent`
""",
)
[docs]
class Order( # pylint: disable=too-few-public-methods,duplicate-code
OrderMixin, model.Base
):
"""
Represents an :term:`order` for a customer. Each order has one or
more :attr:`items`.
Usually, orders are created by way of a
:class:`~sideshow.db.model.batch.neworder.NewOrderBatch`.
"""
__tablename__ = "sideshow_order"
# TODO: this feels a bit hacky yet but it does avoid problems
# showing the Orders grid for a PendingCustomer
__colanderalchemy_config__ = {
"excludes": ["items"],
}
uuid = model.uuid_column()
order_id = sa.Column(
sa.Integer(),
nullable=False,
doc="""
Unique ID for the order.
When the order is created from New Order Batch, this order ID will
match the batch ID.
""",
)
store = orm.relationship(
"Store",
primaryjoin="Store.store_id == Order.store_id",
foreign_keys="Order.store_id",
doc="""
Reference to the :class:`~sideshow.db.model.stores.Store`
record, if applicable.
""",
)
local_customer_uuid = model.uuid_fk_column(
"sideshow_customer_local.uuid", nullable=True
)
local_customer = orm.relationship(
"LocalCustomer",
cascade_backrefs=False,
back_populates="orders",
doc="""
Reference to the
:class:`~sideshow.db.model.customers.LocalCustomer` record
for the order, if applicable.
See also :attr:`customer_id` and :attr:`pending_customer`.
""",
)
pending_customer_uuid = model.uuid_fk_column(
"sideshow_customer_pending.uuid", nullable=True
)
pending_customer = orm.relationship(
"PendingCustomer",
cascade_backrefs=False,
back_populates="orders",
doc="""
Reference to the
:class:`~sideshow.db.model.customers.PendingCustomer` record
for the order, if applicable.
See also :attr:`customer_id` and :attr:`local_customer`.
""",
)
created = sa.Column(
sa.DateTime(timezone=True),
nullable=False,
default=datetime.datetime.now,
doc="""
Timestamp when the order was created.
If the order is created via New Order Batch, this will match the
batch execution timestamp.
""",
)
created_by_uuid = model.uuid_fk_column("user.uuid", nullable=False)
created_by = orm.relationship(
model.User,
cascade_backrefs=False,
doc="""
Reference to the
:class:`~wuttjamaican:wuttjamaican.db.model.auth.User` who
created the order.
""",
)
items = orm.relationship(
"OrderItem",
collection_class=ordering_list("sequence", count_from=1),
cascade="all, delete-orphan",
cascade_backrefs=False,
back_populates="order",
doc="""
List of :class:`OrderItem` records belonging to the order.
""",
)
def __str__(self):
return str(self.order_id)
[docs]
class OrderItem(OrderItemMixin, model.Base):
"""
Represents an :term:`order item` within an :class:`Order`.
Usually these are created from
:class:`~sideshow.db.model.batch.neworder.NewOrderBatchRow`
records.
"""
__tablename__ = "sideshow_order_item"
uuid = model.uuid_column()
order_uuid = model.uuid_fk_column("sideshow_order.uuid", nullable=False)
order = orm.relationship(
Order,
cascade_backrefs=False,
back_populates="items",
doc="""
Reference to the :class:`Order` to which the item belongs.
""",
)
sequence = sa.Column(
sa.Integer(),
nullable=False,
doc="""
1-based numeric sequence for the item, i.e. its line number within
the order.
""",
)
local_product_uuid = model.uuid_fk_column(
"sideshow_product_local.uuid", nullable=True
)
local_product = orm.relationship(
"LocalProduct",
cascade_backrefs=False,
back_populates="order_items",
doc="""
Reference to the
:class:`~sideshow.db.model.products.LocalProduct` record for
the order item, if applicable.
See also :attr:`product_id` and :attr:`pending_product`.
""",
)
pending_product_uuid = model.uuid_fk_column(
"sideshow_product_pending.uuid", nullable=True
)
pending_product = orm.relationship(
"PendingProduct",
cascade_backrefs=False,
back_populates="order_items",
doc="""
Reference to the
:class:`~sideshow.db.model.products.PendingProduct` record for
the order item, if applicable.
See also :attr:`product_id` and :attr:`local_product`.
""",
)
status_code = sa.Column(
sa.Integer(),
nullable=False,
doc="""
Code indicating current status for the order item.
""",
)
paid_amount = sa.Column(
sa.Numeric(precision=8, scale=3),
nullable=False,
default=0,
doc="""
Amount which the customer has paid toward the :attr:`total_price`
of the item.
""",
)
payment_transaction_number = sa.Column(
sa.String(length=20),
nullable=True,
doc="""
Transaction number in which payment for the order was taken, if
applicable/known.
""",
)
events = orm.relationship(
"OrderItemEvent",
order_by="OrderItemEvent.occurred, OrderItemEvent.uuid",
cascade="all, delete-orphan",
cascade_backrefs=False,
back_populates="item",
doc="""
List of :class:`OrderItemEvent` records for the item.
""",
)
@property
def full_description(self): # pylint: disable=empty-docstring
""" """
fields = [
self.product_brand or "",
self.product_description or "",
self.product_size or "",
]
fields = [f.strip() for f in fields if f.strip()]
return " ".join(fields)
def __str__(self):
return self.full_description
[docs]
def add_event(self, type_code, user, **kwargs):
"""
Convenience method to add a new :class:`OrderItemEvent` for
the item.
"""
kwargs["type_code"] = type_code
kwargs["actor"] = user
self.events.append(OrderItemEvent(**kwargs))
[docs]
class OrderItemEvent(model.Base): # pylint: disable=too-few-public-methods
"""
An event in the life of an :term:`order item`.
"""
__tablename__ = "sideshow_order_item_event"
uuid = model.uuid_column()
item_uuid = model.uuid_fk_column("sideshow_order_item.uuid", nullable=False)
item = orm.relationship(
OrderItem,
cascade_backrefs=False,
back_populates="events",
doc="""
Reference to the :class:`OrderItem` to which the event
pertains.
""",
)
type_code = sa.Column(
sa.Integer,
nullable=False,
doc="""
Code indicating the type of event; values must be defined in
:data:`~sideshow.enum.ORDER_ITEM_EVENT`.
""",
)
occurred = sa.Column(
sa.DateTime(timezone=True),
nullable=False,
default=datetime.datetime.now,
doc="""
Date and time when the event occurred.
""",
)
actor_uuid = model.uuid_fk_column("user.uuid", nullable=False)
actor = orm.relationship(
model.User,
doc="""
:class:`~wuttjamaican:wuttjamaican.db.model.auth.User` who
performed the action.
""",
)
note = sa.Column(
sa.Text(),
nullable=True,
doc="""
Optional note recorded for the event.
""",
)