source: pycorepos/corepos/db/model.py @ 1a0a256

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

Define output for str(MemberType)

  • Property mode set to 100644
File size: 13.3 KB
Line 
1# -*- coding: utf-8; -*-
2################################################################################
3#
4#  pyCOREPOS -- Python Interface to CORE POS
5#  Copyright © 2018 Lance Edgar
6#
7#  This file is part of pyCOREPOS.
8#
9#  pyCOREPOS is free software: you can redistribute it and/or modify it under
10#  the terms of the GNU General Public License as published by the Free
11#  Software Foundation, either version 3 of the License, or (at your option)
12#  any later version.
13#
14#  pyCOREPOS 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#  pyCOREPOS.  If not, see <http://www.gnu.org/licenses/>.
21#
22################################################################################
23"""
24CORE POS Data Model
25"""
26
27from __future__ import unicode_literals, absolute_import
28
29import six
30import sqlalchemy as sa
31from sqlalchemy.ext.declarative import declarative_base
32from sqlalchemy.orm import relationship
33from sqlalchemy.ext.associationproxy import association_proxy
34
35
36Base = declarative_base()
37
38
39@six.python_2_unicode_compatible
40class Department(Base):
41    """
42    Represents a department within the organization.
43    """
44    __tablename__ = 'departments'
45
46    dept_no = sa.Column(sa.SmallInteger(), primary_key=True, autoincrement=False, nullable=False)
47
48    dept_name = sa.Column(sa.String(length=30), nullable=True)
49
50    dept_tax = sa.Column(sa.Boolean(), nullable=True)
51
52    dept_fs = sa.Column(sa.Boolean(), nullable=True)
53
54    dept_limit = sa.Column(sa.Float(), nullable=True)
55
56    dept_minimum = sa.Column(sa.Float(), nullable=True)
57
58    dept_discount = sa.Column(sa.Boolean(), nullable=True)
59
60    dept_see_id = sa.Column(sa.Boolean(), nullable=True)
61
62    modified = sa.Column(sa.DateTime(), nullable=True)
63
64    modifiedby = sa.Column(sa.Integer(), nullable=True)
65
66    margin = sa.Column(sa.Float(), nullable=False, default=0)
67
68    salesCode = sa.Column(sa.Integer(), nullable=False, default=0)
69
70    memberOnly = sa.Column(sa.SmallInteger(), nullable=False, default=0)
71
72    def __str__(self):
73        return self.dept_name or ''
74
75
76@six.python_2_unicode_compatible
77class Subdepartment(Base):
78    """
79    Represents a subdepartment within the organization.
80    """
81    __tablename__ = 'subdepts'
82    __table_args__ = (
83        sa.ForeignKeyConstraint(['dept_ID'], ['departments.dept_no']),
84    )
85
86    subdept_no = sa.Column(sa.SmallInteger(), primary_key=True, autoincrement=False, nullable=False)
87
88    subdept_name = sa.Column(sa.String(length=30), nullable=True)
89
90    dept_ID = sa.Column(sa.SmallInteger(), nullable=True)
91    department = relationship(
92        Department,
93        doc="""
94        Reference to the parent :class:`Department` for this subdepartment.
95        """)
96
97    def __str__(self):
98        return self.subdept_name or ''
99
100
101@six.python_2_unicode_compatible
102class Vendor(Base):
103    """
104    Represents a vendor from which product may be purchased.
105    """
106    __tablename__ = 'vendors'
107   
108    vendorID = sa.Column(sa.Integer(), primary_key=True, autoincrement=False, nullable=False)
109
110    vendorName = sa.Column(sa.String(length=50), nullable=True)
111
112    vendorAbbreviation = sa.Column(sa.String(length=10), nullable=True)
113
114    discountRate = sa.Column(sa.Float(), nullable=True)
115
116    contact = relationship(
117        'VendorContact',
118        uselist=False, doc="""
119        Reference to the :class:`VendorContact` instance for this vendor.
120        """)
121
122    phone = association_proxy(
123        'contact', 'phone',
124        creator=lambda p: VendorContact(phone=p))
125
126    fax = association_proxy(
127        'contact', 'fax',
128        creator=lambda f: VendorContact(fax=f))
129
130    email = association_proxy(
131        'contact', 'email',
132        creator=lambda e: VendorContact(email=e))
133
134    website = association_proxy(
135        'contact', 'website',
136        creator=lambda w: VendorContact(website=w))
137
138    notes = association_proxy(
139        'contact', 'notes',
140        creator=lambda n: VendorContact(notes=n))
141
142    def __str__(self):
143        return self.vendorName or ''
144
145
146class VendorContact(Base):
147    """
148    A general contact record for a vendor.
149    """
150    __tablename__ = 'vendorContact'
151
152    vendorID = sa.Column(sa.Integer(), sa.ForeignKey('vendors.vendorID'), primary_key=True, autoincrement=False, nullable=False)
153
154    phone = sa.Column(sa.String(length=15), nullable=True)
155
156    fax = sa.Column(sa.String(length=15), nullable=True)
157
158    email = sa.Column(sa.String(length=50), nullable=True)
159
160    website = sa.Column(sa.String(length=100), nullable=True)
161
162    notes = sa.Column(sa.Text(), nullable=True)
163
164
165@six.python_2_unicode_compatible
166class Product(Base):
167    """
168    Represents a product, purchased and/or sold by the organization.
169    """
170    __tablename__ = 'products'
171    __table_args__ = (
172        sa.ForeignKeyConstraint(['department'], ['departments.dept_no']),
173    )
174
175    id = sa.Column(sa.Integer(), primary_key=True, autoincrement=True, nullable=False)
176
177    upc = sa.Column(sa.String(length=13), nullable=True)
178
179    description = sa.Column(sa.String(length=30), nullable=True)
180
181    brand = sa.Column(sa.String(length=30), nullable=True)
182
183    formatted_name = sa.Column(sa.String(length=30), nullable=True)
184
185    normal_price = sa.Column(sa.Float(), nullable=True)
186
187    pricemethod = sa.Column(sa.SmallInteger(), nullable=True)
188
189    groupprice = sa.Column(sa.Float(), nullable=True)
190
191    quantity = sa.Column(sa.SmallInteger(), nullable=True)
192
193    special_price = sa.Column(sa.Float(), nullable=True)
194
195    specialpricemethod = sa.Column(sa.SmallInteger(), nullable=True)
196
197    specialgroupprice = sa.Column(sa.Float(), nullable=True)
198
199    specialquantity = sa.Column(sa.SmallInteger(), nullable=True)
200
201    start_date = sa.Column(sa.DateTime(), nullable=True)
202
203    end_date = sa.Column(sa.DateTime(), nullable=True)
204
205    dept_no = sa.Column('department', sa.SmallInteger(), nullable=True)
206
207    size = sa.Column(sa.String(length=9), nullable=True)
208
209    tax = sa.Column(sa.SmallInteger(), nullable=True)
210
211    foodstamp = sa.Column(sa.Boolean(), nullable=True)
212
213    scale = sa.Column(sa.Boolean(), nullable=True)
214
215    scaleprice = sa.Column(sa.Boolean(), nullable=True, default=False)
216
217    mixmatchcode = sa.Column(sa.String(length=13), nullable=True)
218
219    modified = sa.Column(sa.DateTime(), nullable=True)
220
221    # advertised = sa.Column(sa.Boolean(), nullable=True)
222
223    tareweight = sa.Column(sa.Float(), nullable=True)
224
225    discount = sa.Column(sa.SmallInteger(), nullable=True)
226
227    discounttype = sa.Column(sa.SmallInteger(), nullable=True)
228
229    line_item_discountable = sa.Column(sa.Boolean(), nullable=True)
230
231    unitofmeasure = sa.Column(sa.String(length=15), nullable=True)
232
233    wicable = sa.Column(sa.SmallInteger(), nullable=True)
234
235    qttyEnforced = sa.Column(sa.Boolean(), nullable=True)
236
237    idEnforced = sa.Column(sa.Boolean(), nullable=True)
238
239    cost = sa.Column(sa.Float(), nullable=True, default=0)
240
241    inUse = sa.Column(sa.Boolean(), nullable=True)
242
243    numflag = sa.Column(sa.Integer(), nullable=True, default=0)
244
245    subdept = sa.Column(sa.SmallInteger(), nullable=True)
246
247    deposit = sa.Column(sa.Float(), nullable=True)
248
249    local = sa.Column(sa.Integer(), nullable=True, default=0)
250
251    store_id = sa.Column(sa.SmallInteger(), nullable=True, default=0)
252
253    default_vendor_id = sa.Column(sa.Integer(), nullable=True, default=0)
254
255    current_origin_id = sa.Column(sa.Integer(), nullable=True, default=0)
256
257    department = relationship(
258        Department,
259        primaryjoin=Department.dept_no == dept_no,
260        foreign_keys=[dept_no],
261        doc="""
262        Reference to the :class:`Department` to which the product belongs.
263        """)
264
265    vendor = relationship(
266        Vendor,
267        primaryjoin=Vendor.vendorID == default_vendor_id,
268        foreign_keys=[default_vendor_id],
269        doc="""
270        Reference to the default :class:`Vendor` from which the product is obtained.
271        """)
272
273    @property
274    def full_description(self):
275        fields = ['brand', 'description', 'size']
276        fields = [getattr(self, f) or '' for f in fields]
277        fields = filter(bool, fields)
278        return ' '.join(fields)
279
280    def __str__(self):
281        return self.description or ''
282
283
284class Employee(Base):
285    """
286    Represents an employee within the organization.
287    """
288    __tablename__ = 'employees'
289
290    emp_no = sa.Column(sa.SmallInteger(), primary_key=True, autoincrement=False, nullable=False)
291
292    CashierPassword = sa.Column(sa.String(length=50), nullable=True)
293
294    AdminPassword = sa.Column(sa.String(length=50), nullable=True)
295
296    FirstName = sa.Column(sa.String(length=255), nullable=True)
297
298    LastName = sa.Column(sa.String(length=255), nullable=True)
299
300    JobTitle = sa.Column(sa.String(length=255), nullable=True)
301
302    EmpActive = sa.Column(sa.Boolean(), nullable=True)
303
304    frontendsecurity = sa.Column(sa.SmallInteger(), nullable=True)
305
306    backendsecurity = sa.Column(sa.SmallInteger(), nullable=True)
307
308    birthdate = sa.Column(sa.DateTime(), nullable=True)
309
310
311@six.python_2_unicode_compatible
312class MemberType(Base):
313    """
314    Represents a type of membership within the organization.
315    """
316    __tablename__ = 'memtype'
317
318    memtype = sa.Column(sa.SmallInteger(), primary_key=True, nullable=False, default=0)
319
320    memDesc = sa.Column(sa.String(length=20), nullable=True)
321
322    custdataType = sa.Column(sa.String(length=10), nullable=True)
323
324    discount = sa.Column(sa.SmallInteger(), nullable=True)
325
326    staff = sa.Column(sa.Boolean(), nullable=True)
327
328    ssi = sa.Column(sa.Boolean(), nullable=True)
329
330    salesCode = sa.Column(sa.Integer(), nullable=True)
331
332    def __str__(self):
333        return self.memDesc or ""
334
335
336class Customer(Base):
337    """
338    Represents a customer of the organization.
339    """
340    __tablename__ = 'custdata'
341
342    id = sa.Column(sa.Integer(), primary_key=True, autoincrement=True, nullable=False)
343
344    CardNo = sa.Column(sa.Integer(), nullable=True)
345
346    personNum = sa.Column(sa.SmallInteger(), nullable=False, default=1)
347
348    FirstName = sa.Column(sa.String(length=30), nullable=True)
349
350    LastName = sa.Column(sa.String(length=30), nullable=True)
351
352    CashBack = sa.Column(sa.Float(), nullable=False, default=60)
353
354    Balance = sa.Column(sa.Float(), nullable=False, default=0)
355
356    Discount = sa.Column(sa.SmallInteger(), nullable=True)
357
358    MemDiscountLimit = sa.Column(sa.Float(), nullable=False, default=0)
359
360    ChargeLimit = sa.Column(sa.Float(), nullable=False, default=0)
361   
362    ChargeOk = sa.Column(sa.Boolean(), nullable=False, default=False)
363
364    WriteChecks = sa.Column(sa.Boolean(), nullable=False, default=True)
365
366    StoreCoupons = sa.Column(sa.Boolean(), nullable=False, default=True)
367
368    Type = sa.Column(sa.String(length=10), nullable=False, default='pc')
369
370    memType = sa.Column(sa.SmallInteger(), nullable=True)
371
372    staff = sa.Column(sa.Boolean(), nullable=False, default=False)
373
374    SSI = sa.Column(sa.Boolean(), nullable=False, default=False)
375
376    Purchases = sa.Column(sa.Float(), nullable=False, default=0)
377
378    NumberOfChecks = sa.Column(sa.SmallInteger(), nullable=False, default=0)
379
380    memCoupons = sa.Column(sa.Integer(), nullable=False, default=1)
381
382    blueLine = sa.Column(sa.String(length=50), nullable=True)
383
384    Shown = sa.Column(sa.Boolean(), nullable=False, default=True)
385
386    LastChange = sa.Column(sa.DateTime(), nullable=False)
387
388    member_type = relationship(
389        MemberType,
390        primaryjoin=MemberType.memtype == memType,
391        foreign_keys=[memType],
392        doc="""
393        Reference to the :class:`MemberType` to which this member belongs.
394        """)
395
396    member_info = relationship(
397        'MemberInfo',
398        primaryjoin='MemberInfo.card_no == Customer.CardNo',
399        foreign_keys=[CardNo],
400        uselist=False,
401        back_populates='customers',
402        doc="""
403        Reference to the :class:`MemberInfo` instance for this customer.
404        """)
405
406
407class MemberInfo(Base):
408    """
409    Contact info regarding a member of the organization.
410    """
411    __tablename__ = 'meminfo'
412
413    card_no = sa.Column(sa.Integer(), primary_key=True, autoincrement=False, nullable=False)
414
415    last_name = sa.Column(sa.String(length=30), nullable=True)
416
417    first_name = sa.Column(sa.String(length=30), nullable=True)
418
419    othlast_name = sa.Column(sa.String(length=30), nullable=True)
420
421    othfirst_name = sa.Column(sa.String(length=30), nullable=True)
422
423    street = sa.Column(sa.String(length=255), nullable=True)
424
425    city = sa.Column(sa.String(length=20), nullable=True)
426
427    state = sa.Column(sa.String(length=2), nullable=True)
428
429    zip = sa.Column(sa.String(length=10), nullable=True)
430
431    phone = sa.Column(sa.String(length=30), nullable=True)
432
433    email_1 = sa.Column(sa.String(length=50), nullable=True)
434
435    email_2 = sa.Column(sa.String(length=50), nullable=True)
436
437    ads_OK = sa.Column(sa.Boolean(), nullable=True, default=True)
438
439    customers = relationship(
440        Customer,
441        primaryjoin=Customer.CardNo == card_no,
442        foreign_keys=[Customer.CardNo],
443        back_populates='member_info',
444        remote_side=Customer.CardNo,
445        doc="""
446        List of :class:`Customer` instances which are associated with this member info.
447        """)
448
449    @property
450    def full_name(self):
451        return '{0} {1}'.format(self.first_name or '', self.last_name or '').strip()
452
453    def __unicode__(self):
454        return self.full_name
Note: See TracBrowser for help on using the repository browser.