source: rattail/rattail/db/model/purchase.py @ 88b089c

Last change on this file since 88b089c was 88b089c, checked in by Lance Edgar <lance@…>, 11 months ago

Add basic "out of stock" awareness for vendor invoices, receiving

this probably needs some work yet, we'll see

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