source: rattail/rattail/db/model/batch/pricing.py @ 2b2a27b

Last change on this file since 2b2a27b was 2b2a27b, checked in by Lance Edgar <lance@…>, 2 years ago

Add PricingBatchRow.old_true_margin column to schema

  • Property mode set to 100644
File size: 8.0 KB
Line 
1# -*- coding: utf-8; -*-
2################################################################################
3#
4#  Rattail -- Retail Software Framework
5#  Copyright © 2010-2020 Lance Edgar
6#
7#  This file is part of Rattail.
8#
9#  Rattail is free software: you can redistribute it and/or modify it under the
10#  terms of the GNU General Public License as published by the Free Software
11#  Foundation, either version 3 of the License, or (at your option) any later
12#  version.
13#
14#  Rattail is distributed in the hope that it will be useful, but WITHOUT ANY
15#  WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
16#  FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
17#  details.
18#
19#  You should have received a copy of the GNU General Public License along with
20#  Rattail.  If not, see <http://www.gnu.org/licenses/>.
21#
22################################################################################
23"""
24Data model for pricing batches
25"""
26
27from __future__ import unicode_literals, absolute_import
28
29import sqlalchemy as sa
30from sqlalchemy import orm
31from sqlalchemy.ext.declarative import declared_attr
32
33from rattail.db.model import Base, BatchMixin, ProductBatchRowMixin, Vendor
34from rattail.db.core import filename_column
35
36
37class PricingBatch(BatchMixin, Base):
38    """
39    Primary data model for pricing batches.
40    """
41    __tablename__ = 'batch_pricing'
42    __batchrow_class__ = 'PricingBatchRow'
43    batch_key = 'pricing'
44    model_title = "Pricing Batch"
45
46    min_diff_threshold = sa.Column(sa.Numeric(precision=6, scale=2), nullable=True, doc="""
47    Threshold amount in currency units (i.e. dollars), to which the new *price
48    change* is compared, to determine whether the price change is "minor".
49    """)
50
51    min_diff_percent = sa.Column(sa.Numeric(precision=5, scale=2), nullable=True, doc="""
52    Percentage to which the new price *margin change* is to be compared, to
53    determine whether the price change is "minor".
54    """)
55
56    calculate_for_manual = sa.Column(sa.Boolean(), nullable=True, default=False, doc="""
57    Flag indicating whether prices should be calculated for products which are
58    manually priced under normal circumstances.
59    """)
60
61    input_filename = filename_column(nullable=True, doc="""
62    Base name of the input data file, if applicable.
63    """)
64
65    shelved = sa.Column(sa.Boolean(), nullable=False, default=False, doc="""
66    Flag to indicate whether the batch is "shelved" for later processing.  This
67    may be used to assist with workflow when entering/executing new batches.
68    """)
69
70
71class PricingBatchRow(ProductBatchRowMixin, Base):
72    """
73    Row of data within a pricing batch.
74    """
75    __tablename__ = 'batch_pricing_row'
76    __batch_class__ = PricingBatch
77
78    @declared_attr
79    def __table_args__(cls):
80        return cls.__default_table_args__() + (
81            sa.ForeignKeyConstraint(['vendor_uuid'], ['vendor.uuid'],
82                                    name='batch_pricing_row_fk_vendor'),
83        )
84
85    STATUS_PRICE_UNCHANGED              = 1
86    STATUS_PRICE_INCREASE               = 2
87    STATUS_PRICE_DECREASE               = 3
88    STATUS_PRODUCT_NOT_FOUND            = 4
89    STATUS_PRODUCT_MANUALLY_PRICED      = 5
90    STATUS_CANNOT_CALCULATE_PRICE       = 6
91    STATUS_PRICE_INCREASE_MINOR         = 7
92    STATUS_PRICE_DECREASE_MINOR         = 8
93    STATUS_PRICE_BREACHES_SRP           = 9
94
95    STATUS = {
96        STATUS_PRICE_UNCHANGED          : "price not changed",
97        STATUS_PRICE_INCREASE           : "price increase",
98        STATUS_PRICE_INCREASE_MINOR     : "price increase (minor)",
99        STATUS_PRICE_DECREASE           : "price drop",
100        STATUS_PRICE_DECREASE_MINOR     : "price drop (minor)",
101        STATUS_PRICE_BREACHES_SRP       : "price goes over SRP",
102        STATUS_PRODUCT_NOT_FOUND        : "product not found",
103        STATUS_PRODUCT_MANUALLY_PRICED  : "product is priced manually",
104        STATUS_CANNOT_CALCULATE_PRICE   : "cannot calculate price",
105    }
106
107    vendor_uuid = sa.Column(sa.String(length=32), nullable=True)
108    vendor = orm.relationship(Vendor)
109
110    vendor_item_code = sa.Column(sa.String(length=20), nullable=True, doc="""
111    Vendor-specific code (SKU) for the item.
112    """)
113
114    family_code = sa.Column(sa.Integer(), nullable=True, doc="""
115    Family code for the item.
116    """)
117
118    report_code = sa.Column(sa.Integer(), nullable=True, doc="""
119    Report code for the item.
120    """)
121
122    alternate_code = sa.Column(sa.String(length=20), nullable=True, doc="""
123    Alternate code (UPC, PLU etc.) for the item.
124    """)
125
126    regular_unit_cost = sa.Column(sa.Numeric(precision=9, scale=5), nullable=True, doc="""
127    The "normal" base unit cost for the item, i.e. with no discounts applied.
128    """)
129
130    true_unit_cost = sa.Column(sa.Numeric(precision=9, scale=5), nullable=True, doc="""
131    The "true" base unit cost for the item.  Note that this may vary over time
132    based on allowances etc.
133    """)
134
135    old_true_margin = sa.Column(sa.Numeric(precision=8, scale=3), nullable=True, doc="""
136    Profit margin for the "old" price vs. the "true" unit cost
137    (:attr:`true_unit_cost`).
138    """)
139
140    true_margin = sa.Column(sa.Numeric(precision=8, scale=3), nullable=True, doc="""
141    Profit margin for the "new" price vs. the "true" unit cost
142    (:attr:`true_unit_cost`).
143    """)
144
145    discounted_unit_cost = sa.Column(sa.Numeric(precision=9, scale=5), nullable=True, doc="""
146    The "discounted" base unit cost for the item.  This is meant to be the
147    "effective" unit cost which is used as the basis for the price calculation.
148    """)
149
150    suggested_price = sa.Column(sa.Numeric(precision=8, scale=3), nullable=True, doc="""
151    The "suggested" retail price for the item.  This is assumed to be defined
152    by some external source, i.e. traditional MSRP as opposed to the price
153    being actually suggested by the batch logic.
154    """)
155
156    current_price = sa.Column(sa.Numeric(precision=8, scale=3), nullable=True, doc="""
157    The "current" price for the item (i.e. which overrides the "regular"
158    price), if there is one at time of batch creation/refresh.
159    """)
160
161    current_price_type = sa.Column(sa.Integer(), nullable=True, doc="""
162    The "type" code for :attr:`current_price`, if applicable.  Distinguishes
163    e.g. Sale price from TPR.
164    """)
165
166    current_price_starts = sa.Column(sa.DateTime(), nullable=True, doc="""
167    Start date/time for the :attr:`current_price`, if applicable.
168    """)
169
170    current_price_ends = sa.Column(sa.DateTime(), nullable=True, doc="""
171    End date/time for the :attr:`current_price`, if applicable.
172    """)
173
174    old_price = sa.Column(sa.Numeric(precision=8, scale=3), nullable=True, doc="""
175    The "old" price for the item.  This is assumed to be the "regular" price as
176    of batch creation.
177    """)
178
179    old_price_margin = sa.Column(sa.Numeric(precision=8, scale=3), nullable=True, doc="""
180    Profit margin for the "old" price vs. the "effective" unit cost
181    (:attr:`discounted_unit_cost`).
182    """)
183
184    price_markup = sa.Column(sa.Numeric(precision=5, scale=3), nullable=True, doc="""
185    Markup used to calculate new price, if applicable.
186    """)
187
188    new_price = sa.Column(sa.Numeric(precision=8, scale=3), nullable=True, doc="""
189    The "new" price for the item, as calculated by the batch handler logic.
190    """)
191
192    price_margin = sa.Column(sa.Numeric(precision=8, scale=3), nullable=True, doc="""
193    Profit margin for the "new" price vs. the "effective" unit cost
194    (:attr:`discounted_unit_cost`).
195    """)
196
197    price_diff = sa.Column(sa.Numeric(precision=8, scale=3), nullable=True, doc="""
198    Difference in dollars, between the new and old price amounts.
199    """)
200
201    price_diff_percent = sa.Column(sa.Numeric(precision=8, scale=3), nullable=True, doc="""
202    Difference in percentage, between the new and old price amounts.
203    """)
204
205    margin_diff = sa.Column(sa.Numeric(precision=8, scale=3), nullable=True, doc="""
206    Difference in margin, between the new and old price margins.
207    """)
208
209    manually_priced = sa.Column(sa.Boolean(), nullable=True, doc="""
210    Optional flag to indicate whether the product is manually priced, under
211    normal circumstances.
212    """)
Note: See TracBrowser for help on using the repository browser.