source: rattail/rattail/db/model/purchase.py @ 6927d74

Last change on this file since 6927d74 was 6927d74, checked in by Lance Edgar <ledgar@…>, 9 months ago

Fix logic for calculating "credit total"

also copy receiving date from truck dump parent to child, and fix output of
str(PurchaseCredit)

  • Property mode set to 100644
File size: 20.0 KB
Line 
1# -*- coding: utf-8; -*-
2################################################################################
3#
4#  Rattail -- Retail Software Framework
5#  Copyright © 2010-2018 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 purchase orders
25"""
26
27from __future__ import unicode_literals, absolute_import
28
29import datetime
30
31import six
32import sqlalchemy as sa
33from sqlalchemy import orm
34from sqlalchemy.ext.declarative import declared_attr
35
36from rattail.db.model import Base, uuid_column, Store, Department, Vendor, Employee, User, Product
37from rattail.db.types import GPCType
38from rattail.util import pretty_quantity
39
40
41@six.python_2_unicode_compatible
42class PurchaseBase(object):
43    """
44    Base class for purchases; defines common fields.
45    """
46
47    @declared_attr
48    def __table_args__(cls):
49        return cls.__purchase_table_args__()
50
51    @classmethod
52    def __purchase_table_args__(cls):
53        return (
54            sa.ForeignKeyConstraint(['store_uuid'], ['store.uuid'], name='{}_fk_store'.format(cls.__tablename__)),
55            sa.ForeignKeyConstraint(['vendor_uuid'], ['vendor.uuid'], name='{}_fk_vendor'.format(cls.__tablename__)),
56            sa.ForeignKeyConstraint(['department_uuid'], ['department.uuid'], name='{}_fk_department'.format(cls.__tablename__)),
57            sa.ForeignKeyConstraint(['buyer_uuid'], ['employee.uuid'], name='{}_purchase_fk_buyer'.format(cls.__tablename__)),
58        )
59
60    store_uuid = sa.Column(sa.String(length=32), nullable=False)
61
62    @declared_attr
63    def store(cls):
64        return orm.relationship(
65            Store,
66            doc="""
67            Reference to the :class:`Store` for which the purchase was made.
68            """)
69
70    vendor_uuid = sa.Column(sa.String(length=32), nullable=False)
71
72    @declared_attr
73    def vendor(cls):
74        return orm.relationship(
75            Vendor,
76            doc="""
77            Reference to the :class:`Vendor` to which the purchase was made.
78            """)
79
80    department_uuid = sa.Column(sa.String(length=32), nullable=True)
81
82    @declared_attr
83    def department(cls):
84        return orm.relationship(
85            Department,
86            doc="""
87            Reference to the primary :class:`Department` for which the purchase was made.
88            """)
89
90    buyer_uuid = sa.Column(sa.String(length=32), nullable=True)
91
92    @declared_attr
93    def buyer(cls):
94        return orm.relationship(
95            Employee,
96            doc="""
97            Reference to the :class:`Employee` who placed the order with the
98            vendor, if applicable/known.
99            """)
100
101    po_number = sa.Column(sa.String(length=20), nullable=True, doc="""
102    Purchase order number, e.g. for cross-reference with another system.
103    """)
104
105    po_total = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
106    Purchase order total, i.e. expected cost as of order placement.
107    """)
108
109    date_ordered = sa.Column(sa.Date(), nullable=True, doc="""
110    Date on which the purchase order was first submitted to the vendor.
111    """)
112
113    ship_method = sa.Column(sa.String(length=50), nullable=True, doc="""
114    Code representing the shipping method.
115    """)
116
117    notes_to_vendor = sa.Column(sa.Text(), nullable=True, doc="""
118    Any arbitrary notes to the vendor, regarding the purchase.
119    """)
120
121    date_shipped = sa.Column(sa.Date(), nullable=True, doc="""
122    Date on which the order was shipped from the vendor.
123    """)
124
125    date_received = sa.Column(sa.Date(), nullable=True, doc="""
126    Date on which the order was received at the store.
127    """)
128
129    invoice_number = sa.Column(sa.String(length=20), nullable=True, doc="""
130    Invoice number, e.g. for cross-reference with another system.
131    """)
132
133    invoice_date = sa.Column(sa.Date(), nullable=True, doc="""
134    Invoice date, if applicable.
135    """)
136
137    invoice_total = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
138    Invoice total, if applicable.
139    """)
140
141    def __str__(self):
142        if self.vendor and self.date_ordered:
143            return "{} ({})".format(self.vendor, self.date_ordered.strftime('%Y-%m-%d'))
144        if self.vendor:
145            return str(self.vendor)
146        return ''
147
148
149class PurchaseItemBase(object):
150    """
151    Base class for purchase line items.
152    """
153
154    @declared_attr
155    def __table_args__(cls):
156        return cls.__purchaseitem_table_args__()
157
158    @classmethod
159    def __purchaseitem_table_args__(cls):
160        return (
161            sa.ForeignKeyConstraint(['product_uuid'], ['product.uuid'], name='{}_fk_product'.format(cls.__tablename__)),
162        )
163
164    sequence = sa.Column(sa.Integer(), nullable=True, doc="""
165    Effectively the (internal) line number for the purchase line item, using 1-based indexing.
166    """)
167
168    vendor_code = sa.Column(sa.String(length=20), nullable=True, doc="""
169    Vendor item code for the product.
170    """)
171
172    product_uuid = sa.Column(sa.String(length=32), nullable=True)
173
174    @declared_attr
175    def product(cls):
176        return orm.relationship(
177            Product,
178            doc="""
179            Reference to the :class:`Product` which the line item contains / represents.
180            """,
181            backref=orm.backref(
182                '_{}_records'.format(cls.__tablename__),
183                doc="""
184                List of ``{}`` records associated with the product.
185                """.format(cls.__tablename__)))
186
187    upc = sa.Column(GPCType(), nullable=True, doc="""
188    Product UPC for the line item.
189    """)
190
191    item_id = sa.Column(sa.String(length=20), nullable=True, doc="""
192    Generic ID string for the item.
193    """)
194
195    brand_name = sa.Column(sa.String(length=100), nullable=True, doc="""
196    Brand name for the line item.
197    """)
198
199    description = sa.Column(sa.String(length=60), nullable=False, default='', doc="""
200    Product description for the line item.
201    """)
202
203    size = sa.Column(sa.String(length=255), nullable=True, doc="""
204    Product size for the line item.
205    """)
206
207    department_number = sa.Column(sa.Integer(), nullable=True, doc="""
208    Number of the department to which the product belongs.
209    """)
210
211    department_name = sa.Column(sa.String(length=30), nullable=True, doc="""
212    Name of the department to which the product belongs.
213    """)
214
215    case_quantity = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
216    Number of units in a single case of product.
217    """)
218
219    cases_ordered = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
220    Number of cases of product which were initially ordered.
221    """)
222
223    units_ordered = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
224    Number of units of product which were initially ordered.
225    """)
226
227    po_line_number = sa.Column(sa.Integer(), nullable=True, doc="""
228    Line number from the PO if known, for cross-reference.
229    """)
230
231    po_unit_cost = sa.Column(sa.Numeric(precision=7, scale=3), nullable=True, doc="""
232    Expected cost per single unit of product, as of initial order placement.
233    """)
234
235    po_total = sa.Column(sa.Numeric(precision=7, scale=2), nullable=True, doc="""
236    Expected total cost for line item, as of initial order placement.
237    """)
238
239    cases_shipped = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
240    Number of cases of product which were supposedly shipped by/from the vendor.
241    """)
242
243    units_shipped = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
244    Number of units of product which were supposedly shipped by/from the vendor.
245    """)
246
247    cases_received = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
248    Number of cases of product which were ultimately received.
249    """)
250
251    units_received = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
252    Number of units of product which were ultimately received.
253    """)
254
255    out_of_stock = sa.Column(sa.Boolean(), nullable=True, doc="""
256    Flag indicating whether the item was known to be "out of stock" per the
257    vendor invoice.
258    """)
259
260    invoice_line_number = sa.Column(sa.Integer(), nullable=True, doc="""
261    Line number from the invoice if known, for cross-reference.
262    """)
263
264    invoice_case_cost = sa.Column(sa.Numeric(precision=7, scale=3), nullable=True, doc="""
265    Actual cost per case of product, per invoice.
266    """)
267
268    invoice_unit_cost = sa.Column(sa.Numeric(precision=7, scale=3), nullable=True, doc="""
269    Actual cost per single unit of product, per invoice.
270    """)
271
272    invoice_total = sa.Column(sa.Numeric(precision=7, scale=2), nullable=True, doc="""
273    Actual total cost for line item, per invoice.
274    """)
275
276    cases_damaged = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
277    Number of cases of product which were shipped damaged.
278    """)
279
280    units_damaged = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
281    Number of units of product which were shipped damaged.
282    """)
283
284    cases_expired = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
285    Number of cases of product which were shipped expired.
286    """)
287
288    units_expired = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
289    Number of units of product which were shipped expired.
290    """)
291
292    cases_mispick = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
293    Number of cases of product for which mispick was shipped.
294    """)
295
296    units_mispick = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
297    Number of units of product for which mispick was shipped.
298    """)
299
300
301@six.python_2_unicode_compatible
302class PurchaseCreditBase(object):
303    """
304    Base class for purchase credits.
305    """
306
307    @declared_attr
308    def __table_args__(cls):
309        return cls.__purchasecredit_table_args__()
310
311    @classmethod
312    def __purchasecredit_table_args__(cls):
313        return (
314            sa.ForeignKeyConstraint(['store_uuid'], ['store.uuid'], name='{}_fk_store'.format(cls.__tablename__)),
315            sa.ForeignKeyConstraint(['vendor_uuid'], ['vendor.uuid'], name='{}_fk_vendor'.format(cls.__tablename__)),
316            sa.ForeignKeyConstraint(['product_uuid'], ['product.uuid'], name='{}_fk_product'.format(cls.__tablename__)),
317            sa.ForeignKeyConstraint(['mispick_product_uuid'], ['product.uuid'], name='{}_fk_mispick_product'.format(cls.__tablename__)),
318        )
319
320    store_uuid = sa.Column(sa.String(length=32), nullable=False)
321
322    @declared_attr
323    def store(cls):
324        return orm.relationship(
325            Store,
326            doc="""
327            Reference to the :class:`Store` for which the purchase was made.
328            """)
329
330    vendor_uuid = sa.Column(sa.String(length=32), nullable=False)
331
332    @declared_attr
333    def vendor(cls):
334        return orm.relationship(
335            Vendor,
336            doc="""
337            Reference to the :class:`Vendor` to which the purchase was made.
338            """)
339
340    date_ordered = sa.Column(sa.Date(), nullable=True, doc="""
341    Date on which the purchase order was first submitted to the vendor.
342    """)
343
344    date_shipped = sa.Column(sa.Date(), nullable=True, doc="""
345    Date on which the order was shipped from the vendor.
346    """)
347
348    date_received = sa.Column(sa.Date(), nullable=True, doc="""
349    Date on which the order was received at the store.
350    """)
351
352    invoice_number = sa.Column(sa.String(length=20), nullable=True, doc="""
353    Invoice number, e.g. for cross-reference with another system.
354    """)
355
356    invoice_date = sa.Column(sa.Date(), nullable=True, doc="""
357    Invoice date, if applicable.
358    """)
359
360    credit_type = sa.Column(sa.String(length=20), nullable=False, doc="""
361    Type of the credit, i.e. damaged/expired/mispick
362    """)
363
364    product_uuid = sa.Column(sa.String(length=32), nullable=True)
365
366    @declared_attr
367    def product(cls):
368        return orm.relationship(
369            Product,
370            primaryjoin='Product.uuid == {}.product_uuid'.format(cls.__name__),
371            doc="""
372            Reference to the :class:`Product` with which the credit is associated.
373            """)
374
375    upc = sa.Column(GPCType(), nullable=True, doc="""
376    Product UPC for the credit item.
377    """)
378
379    vendor_item_code = sa.Column(sa.String(length=20), nullable=True, doc="""
380    Vendor-specific code for the credit item.
381    """)
382
383    brand_name = sa.Column(sa.String(length=100), nullable=True, doc="""
384    Brand name for the credit item.
385    """)
386
387    description = sa.Column(sa.String(length=60), nullable=False, default='', doc="""
388    Product description for the credit item.
389    """)
390
391    size = sa.Column(sa.String(length=255), nullable=True, doc="""
392    Product size for the credit item.
393    """)
394
395    department_number = sa.Column(sa.Integer(), nullable=True, doc="""
396    Number of the department to which the product belongs.
397    """)
398
399    department_name = sa.Column(sa.String(length=30), nullable=True, doc="""
400    Name of the department to which the product belongs.
401    """)
402
403    case_quantity = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
404    Number of units in a single case of product.
405    """)
406
407    cases_shorted = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
408    Number of cases of product which were ordered but not received.
409    """)
410
411    units_shorted = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
412    Number of cases of product which were ordered but not received.
413    """)
414
415    product_discarded = sa.Column(sa.Boolean(), nullable=True, doc="""
416    Indicates the associated product was discarded, and cannot be returned to vendor.
417    """)
418
419    expiration_date = sa.Column(sa.Date(), nullable=True, doc="""
420    Expiration date marked on expired product, if applicable.
421    """)
422
423    invoice_line_number = sa.Column(sa.Integer(), nullable=True, doc="""
424    Line number from the invoice if known, for cross-reference.
425    """)
426
427    invoice_case_cost = sa.Column(sa.Numeric(precision=7, scale=3), nullable=True, doc="""
428    Actual cost per case of product, per invoice.
429    """)
430
431    invoice_unit_cost = sa.Column(sa.Numeric(precision=7, scale=3), nullable=True, doc="""
432    Actual cost per single unit of product, per invoice.
433    """)
434
435    invoice_total = sa.Column(sa.Numeric(precision=7, scale=2), nullable=True, doc="""
436    Actual total cost for line item, per invoice.
437    """)
438
439    credit_total = sa.Column(sa.Numeric(precision=7, scale=2), nullable=True, doc="""
440    Actual total cost for credit, i.e. value of missing/damaged product.
441    """)
442
443    mispick_product_uuid = sa.Column(sa.String(length=32), nullable=True)
444
445    @declared_attr
446    def mispick_product(cls):
447        return orm.relationship(
448            Product,
449            primaryjoin='Product.uuid == {}.mispick_product_uuid'.format(cls.__name__),
450            doc="""
451            Reference to the :class:`Product` which was shipped in place of the
452            one which was ordered.
453            """)
454
455    mispick_upc = sa.Column(GPCType(), nullable=True, doc="""
456    Product UPC for the mispick item.
457    """)
458
459    mispick_brand_name = sa.Column(sa.String(length=100), nullable=True, doc="""
460    Brand name for the mispick item.
461    """)
462
463    mispick_description = sa.Column(sa.String(length=60), nullable=True, default='', doc="""
464    Product description for the mispick item.
465    """)
466
467    mispick_size = sa.Column(sa.String(length=255), nullable=True, doc="""
468    Product size for the mispick item.
469    """)
470
471    def __str__(self):
472        if self.cases_shorted is not None and self.units_shorted is not None:
473            qty = "{} cases, {} units".format(
474                pretty_quantity(self.cases_shorted),
475                pretty_quantity(self.units_shorted))
476        elif self.cases_shorted is not None:
477            qty = "{} cases".format(pretty_quantity(self.cases_shorted))
478        elif self.units_shorted is not None:
479            qty = "{} units".format(pretty_quantity(self.units_shorted))
480        else:
481            qty = "??"
482        qty += " {}".format(self.credit_type)
483        if self.credit_type == 'expired' and self.expiration_date:
484            qty += " ({})".format(self.expiration_date)
485        if self.credit_type == 'mispick' and self.mispick_product:
486            qty += " ({})".format(self.mispick_product)
487        if self.invoice_total:
488            return "{} = ${:0.2f}".format(qty, self.invoice_total)
489        return qty
490
491
492class Purchase(PurchaseBase, Base):
493    """
494    Represents a purchase made by a store.
495    """
496    __tablename__ = 'purchase'
497
498    @declared_attr
499    def __table_args__(cls):
500        return cls.__purchase_table_args__() + (
501            sa.ForeignKeyConstraint(['created_by_uuid'], ['user.uuid'], name='purchase_fk_created_by'),
502        )
503
504    uuid = uuid_column()
505
506    status = sa.Column(sa.Integer(), nullable=False, doc="""
507    Numeric code used to signify current status for the purchase, e.g. placed
508    or paid etc.
509    """)
510
511    created = sa.Column(sa.DateTime(), nullable=False, default=datetime.datetime.utcnow, doc="""
512    Timestamp when the purchase was first created within this system.
513    """)
514
515    created_by_uuid = sa.Column(sa.String(length=32), nullable=False)
516
517    created_by = orm.relationship(
518        User,
519        primaryjoin=User.uuid == created_by_uuid,
520        foreign_keys=[created_by_uuid],
521        doc="""
522        Reference to the :class:`User` who first created the purchase within
523        this system.
524        """)
525
526
527class PurchaseItem(PurchaseItemBase, Base):
528    """
529    Represents a line item on a purchase.
530    """
531    __tablename__ = 'purchase_item'
532
533    @declared_attr
534    def __table_args__(cls):
535        return cls.__purchaseitem_table_args__() + (
536            sa.ForeignKeyConstraint(['purchase_uuid'], ['purchase.uuid'], name='purchase_item_fk_purchase'),
537        )
538
539    uuid = uuid_column()
540
541    purchase_uuid = sa.Column(sa.String(length=32), nullable=False)
542
543    purchase = orm.relationship(
544        Purchase,
545        doc="""
546        Reference to the :class:`Purchase` to which the item belongs.
547        """,
548        backref=orm.backref(
549            'items',
550            cascade='all',
551            doc="""
552            List of :class:`PurchaseItem` instances for the purchase.
553            """))
554
555    status = sa.Column(sa.Integer(), nullable=True, doc="""
556    Numeric code used to signify current status for the line item, e.g. for
557    highlighting rows when invoice cost differed from expected/PO cost (?)
558    """)
559
560
561class PurchaseCredit(PurchaseCreditBase, Base):
562    """
563    Represents a purchase credit item.
564    """
565    __tablename__ = 'purchase_credit'
566
567    @declared_attr
568    def __table_args__(cls):
569        return cls.__purchasecredit_table_args__() + (
570            sa.ForeignKeyConstraint(['purchase_uuid'], ['purchase.uuid'], name='purchase_credit_fk_purchase'),
571            sa.ForeignKeyConstraint(['item_uuid'], ['purchase_item.uuid'], name='purchase_credit_fk_item'),
572        )
573
574    uuid = uuid_column()
575
576    purchase_uuid = sa.Column(sa.String(length=32), nullable=True)
577
578    purchase = orm.relationship(
579        Purchase,
580        doc="""
581        Reference to the :class:`Purchase` to which the credit applies.
582        """,
583        backref=orm.backref(
584            'credits',
585            doc="""
586            List of :class:`PurchaseCredit` instances for the purchase.
587            """))
588
589    item_uuid = sa.Column(sa.String(length=32), nullable=True)
590
591    item = orm.relationship(
592        PurchaseItem,
593        doc="""
594        Reference to the purchase item with which the credit is associated.
595        """)
596
597    status = sa.Column(sa.Integer(), nullable=True, doc="""
598    Numeric code used to signify current status for the credit.
599    """)
Note: See TracBrowser for help on using the repository browser.