Changeset - 5f1d6d76ed6e
[Not reviewed]
0 1 4
Lance Edgar - 3 years ago 2021-11-04 21:21:37
lance@edbob.org
Add batch to update CORE member records directly via SQL
5 files changed with 439 insertions and 0 deletions:
0 comments (0 inline, 0 general) First comment
rattail_corepos/batch/coremember.py
Show inline comments
 
new file 100644
 
# -*- coding: utf-8; -*-
 
################################################################################
 
#
 
#  Rattail -- Retail Software Framework
 
#  Copyright © 2010-2021 Lance Edgar
 
#
 
#  This file is part of Rattail.
 
#
 
#  Rattail 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.
 
#
 
#  Rattail 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
 
#  Rattail.  If not, see <http://www.gnu.org/licenses/>.
 
#
 
################################################################################
 
"""
 
Handler for CORE member batches
 
"""
 

	
 
import csv
 
import logging
 

	
 
from corepos.db.office_op import Session as CoreSession, model as corepos
 

	
 
from rattail.batch import BatchHandler
 
from rattail.db.util import maxlen
 
from rattail_corepos.db.model import CoreMemberBatch, CoreMemberBatchRow
 

	
 

	
 
log = logging.getLogger(__name__)
 

	
 

	
 
class CoreMemberBatchHandler(BatchHandler):
 
    """
 
    Handler for CORE member batches.
 
    """
 
    batch_model_class = CoreMemberBatch
 

	
 
    importing_fields = [
 
        'first_name',
 
        'last_name',
 
        'street',
 
        'city',
 
        'state',
 
        'zipcode',
 
        'phone',
 
        'email1',
 
    ]
 

	
 
    def should_populate(self, batch):
 
        if batch.input_file:
 
            return True
 
        return False
 

	
 
    def setup(self, batch, progress=None):
 
        self.core_session = CoreSession()
 

	
 
    setup_populate = setup
 
    setup_refresh = setup
 

	
 
    def teardown(self, batch, progress=None):
 
        self.core_session.close()
 
        del self.core_session
 

	
 
    teardown_populate = teardown
 
    teardown_refresh = teardown
 

	
 
    def populate(self, batch, progress=None):
 
        if batch.input_file:
 
            return self.populate_from_file(batch, progress=progress)
 
        raise NotImplementedError("do not know how to populate this batch")
 

	
 
    def populate_from_file(self, batch, progress=None):
 
        """
 
        Populate member batch from input data file.
 
        """
 
        # TODO: should detect what type of input file we have, but for
 
        # now we only support one kind..
 
        return self.populate_from_contact_file(batch, progress=progress)
 

	
 
    def populate_from_contact_file(self, batch, progress=None):
 
        """
 
        Populate member batch from "contact" CSV input data file.
 
        """
 
        input_path = batch.filepath(self.config, batch.input_file)
 
        input_file = open(input_path, 'rt')
 
        reader = csv.DictReader(input_file)
 
        data = list(reader)
 
        input_file.close()
 

	
 
        batch.set_param('fields', self.importing_fields)
 

	
 
        maxlens = {}
 
        for field in self.importing_fields:
 
            maxlens[field] = maxlen(getattr(CoreMemberBatchRow, field))
 

	
 
        def append(csvrow, i):
 
            row = self.make_row()
 
            row.card_number = int(csvrow['external_id'])
 
            row.first_name = csvrow['first_name']
 
            row.last_name = csvrow['last_name']
 
            row.street = csvrow['primary_address1']
 
            row.city = csvrow['primary_city']
 
            row.state = csvrow['primary_state']
 
            row.zipcode = csvrow['primary_zip']
 
            row.phone = csvrow['phone_number']
 
            # TODO: this seems useful, but maybe in another step?
 
            # row.phone = self.app.format_phone_number(csvrow['phone_number'])
 
            row.email1 = csvrow['email']
 

	
 
            for field in self.importing_fields:
 
                if len(getattr(row, field)) > maxlens[field]:
 
                    log.warning("%s field is %s and will be truncated to %s "
 
                                "for row #%s in CSV data: %s",
 
                                field,
 
                                len(getattr(row, field)),
 
                                maxlens[field],
 
                                i + 1,
 
                                csvrow)
 
                    value = getattr(row, field)
 
                    setattr(row, field, value[:maxlens[field]])
 

	
 
            self.add_row(batch, row)
 

	
 
        self.progress_loop(append, data, progress,
 
                           message="Adding initial rows to batch")
 

	
 
    def refresh_row(self, row):
 

	
 
        # clear these first in case they are set
 
        row.first_name_old = None
 
        row.last_name_old = None
 
        row.street_old = None
 
        row.city_old = None
 
        row.state_old = None
 
        row.zipcode_old = None
 
        row.phone_old = None
 
        row.email1_old = None
 
        row.email2_old = None
 
        row.member_type_id_old = None
 
        row.status_text = None
 

	
 
        if not row.card_number:
 
            row.status_code = row.STATUS_MEMBER_NOT_FOUND
 
            row.status_text = "row has no card number"
 
            return
 

	
 
        core_member = self.core_session.query(corepos.MemberInfo).get(row.card_number)
 
        if not core_member:
 
            row.status_code = row.STATUS_MEMBER_NOT_FOUND
 
            row.status_text = "matching record not found in CORE"
 
            return
 

	
 
        core_customer = core_member.customers[0] if core_member.customers else None
 

	
 
        row.street_old = core_member.street
 
        row.city_old = core_member.city
 
        row.state_old = core_member.state
 
        row.zipcode_old = core_member.zip
 
        row.phone_old = core_member.phone
 
        row.email1_old = core_member.email
 
        row.email2_old = core_member.email2
 

	
 
        if core_customer:
 
            row.first_name_old = core_customer.first_name
 
            row.last_name_old = core_customer.last_name
 
            row.member_type_id_old = core_customer.member_type_id
 

	
 
        diffs = []
 
        for field in self.importing_fields:
 
            if getattr(row, field) != getattr(row, '{}_old'.format(field)):
 
                diffs.append(field)
 

	
 
        if diffs:
 
            row.status_code = row.STATUS_FIELDS_CHANGED
 
            row.status_text = ', '.join(diffs)
 
        else:
 
            row.status_code = row.STATUS_NO_CHANGE
 

	
 
    def describe_execution(self, batch, **kwargs):
 
        return ("CORE will be updated, by writing SQL directly to its DB, "
 
                "for each row indicating a change.  Note that this will "
 
                "affect one or both of the following tables:\n\n"
 
                "- `custdata`\n"
 
                "- `meminfo`")
 

	
 
    def execute(self, batch, progress=None, **kwargs):
 
        """
 
        Update the CORE DB with changes from the batch.
 
        """
 
        # we only want to process "update member" (changed) rows
 
        rows = [row for row in batch.active_rows()
 
                if row.status_code in (row.STATUS_FIELDS_CHANGED,)]
 
        if not rows:
 
            return True
 

	
 
        self.update_corepos(batch, rows, progress=progress)
 
        return True
 

	
 
    def update_corepos(self, batch, rows, progress=None):
 
        """
 
        For each of the given batch rows, this will update the CORE DB
 
        directly via SQL, for the fields which are specified in the
 
        batch params.
 
        """
 
        core_session = CoreSession()
 
        fields = batch.get_param('fields')
 

	
 
        def update(row, i):
 
            core_member = core_session.query(corepos.MemberInfo).get(row.card_number)
 
            if not core_member:
 
                log.warning("CORE member not found for row %s with card number: %s",
 
                            row.uuid, row.card_number)
 
                return
 

	
 
            if 'street' in fields:
 
                core_member.street = row.street
 
            if 'city' in fields:
 
                core_member.city = row.city
 
            if 'state' in fields:
 
                core_member.state = row.state
 
            if 'zipcode' in fields:
 
                core_member.zip = row.zipcode
 
            if 'phone' in fields:
 
                core_member.phone = row.phone
 
            if 'email1' in fields:
 
                core_member.email = row.email1
 

	
 
            core_customer = core_member.customers[0] if core_member.customers else None
 
            if core_customer:
 

	
 
                if 'first_name' in fields:
 
                    core_customer.first_name = row.first_name
 
                if 'last_name' in fields:
 
                    core_customer.last_name = row.last_name
 

	
 
        self.progress_loop(update, rows, progress,
 
                           message="Updating members in CORE-POS")
 

	
 
        core_session.commit()
 
        core_session.close()
rattail_corepos/db/alembic/versions/50961b4b854a_add_corepos_member_batch.py
Show inline comments
 
new file 100644
 
# -*- coding: utf-8; -*-
 
"""add corepos_member batch
 

	
 
Revision ID: 50961b4b854a
 
Revises: 7fea5aebddfb
 
Create Date: 2021-11-04 18:36:23.494783
 

	
 
"""
 

	
 
from __future__ import unicode_literals
 

	
 
# revision identifiers, used by Alembic.
 
revision = '50961b4b854a'
 
down_revision = '7fea5aebddfb'
 
branch_labels = None
 
depends_on = None
 

	
 
from alembic import op
 
import sqlalchemy as sa
 
import rattail.db.types
 

	
 

	
 

	
 
def upgrade():
 

	
 
    # batch_corepos_member
 
    op.create_table('batch_corepos_member',
 
                    sa.Column('uuid', sa.String(length=32), nullable=False),
 
                    sa.Column('id', sa.Integer(), nullable=False),
 
                    sa.Column('description', sa.String(length=255), nullable=True),
 
                    sa.Column('created', sa.DateTime(), nullable=False),
 
                    sa.Column('created_by_uuid', sa.String(length=32), nullable=False),
 
                    sa.Column('cognized', sa.DateTime(), nullable=True),
 
                    sa.Column('cognized_by_uuid', sa.String(length=32), nullable=True),
 
                    sa.Column('rowcount', sa.Integer(), nullable=True),
 
                    sa.Column('complete', sa.Boolean(), nullable=False),
 
                    sa.Column('executed', sa.DateTime(), nullable=True),
 
                    sa.Column('executed_by_uuid', sa.String(length=32), nullable=True),
 
                    sa.Column('purge', sa.Date(), nullable=True),
 
                    sa.Column('notes', sa.Text(), nullable=True),
 
                    sa.Column('params', rattail.db.types.JSONTextDict(), nullable=True),
 
                    sa.Column('extra_data', sa.Text(), nullable=True),
 
                    sa.Column('status_code', sa.Integer(), nullable=True),
 
                    sa.Column('status_text', sa.String(length=255), nullable=True),
 
                    sa.Column('input_file', sa.String(length=255), nullable=True),
 
                    sa.ForeignKeyConstraint(['cognized_by_uuid'], ['user.uuid'], name='batch_corepos_member_fk_cognized_by'),
 
                    sa.ForeignKeyConstraint(['created_by_uuid'], ['user.uuid'], name='batch_corepos_member_fk_created_by'),
 
                    sa.ForeignKeyConstraint(['executed_by_uuid'], ['user.uuid'], name='batch_corepos_member_fk_executed_by'),
 
                    sa.PrimaryKeyConstraint('uuid')
 
    )
 

	
 
    # batch_corepos_member_row
 
    op.create_table('batch_corepos_member_row',
 
                    sa.Column('uuid', sa.String(length=32), nullable=False),
 
                    sa.Column('batch_uuid', sa.String(length=32), nullable=False),
 
                    sa.Column('sequence', sa.Integer(), nullable=False),
 
                    sa.Column('status_code', sa.Integer(), nullable=True),
 
                    sa.Column('status_text', sa.String(length=255), nullable=True),
 
                    sa.Column('modified', sa.DateTime(), nullable=True),
 
                    sa.Column('removed', sa.Boolean(), nullable=False),
 
                    sa.Column('card_number', sa.Integer(), nullable=True),
 
                    sa.Column('first_name', sa.String(length=30), nullable=True),
 
                    sa.Column('first_name_old', sa.String(length=30), nullable=True),
 
                    sa.Column('last_name', sa.String(length=30), nullable=True),
 
                    sa.Column('last_name_old', sa.String(length=30), nullable=True),
 
                    sa.Column('street', sa.String(length=255), nullable=True),
 
                    sa.Column('street_old', sa.String(length=255), nullable=True),
 
                    sa.Column('city', sa.String(length=20), nullable=True),
 
                    sa.Column('city_old', sa.String(length=20), nullable=True),
 
                    sa.Column('state', sa.String(length=2), nullable=True),
 
                    sa.Column('state_old', sa.String(length=2), nullable=True),
 
                    sa.Column('zipcode', sa.String(length=10), nullable=True),
 
                    sa.Column('zipcode_old', sa.String(length=10), nullable=True),
 
                    sa.Column('phone', sa.String(length=30), nullable=True),
 
                    sa.Column('phone_old', sa.String(length=30), nullable=True),
 
                    sa.Column('email1', sa.String(length=50), nullable=True),
 
                    sa.Column('email1_old', sa.String(length=50), nullable=True),
 
                    sa.Column('email2', sa.String(length=50), nullable=True),
 
                    sa.Column('email2_old', sa.String(length=50), nullable=True),
 
                    sa.Column('member_type_id', sa.SmallInteger(), nullable=True),
 
                    sa.Column('member_type_id_old', sa.SmallInteger(), nullable=True),
 
                    sa.ForeignKeyConstraint(['batch_uuid'], ['batch_corepos_member.uuid'], name='batch_corepos_member_row_fk_batch_uuid'),
 
                    sa.PrimaryKeyConstraint('uuid')
 
    )
 

	
 

	
 
def downgrade():
 

	
 
    # batch_corepos_member*
 
    op.drop_table('batch_corepos_member_row')
 
    op.drop_table('batch_corepos_member')
rattail_corepos/db/model/__init__.py
Show inline comments
 
@@ -28,3 +28,5 @@ from .stores import CoreStore
 
from .people import CorePerson, CoreCustomer, CoreMember
 
from .products import (CoreDepartment, CoreSubdepartment,
 
                       CoreVendor, CoreProduct, CoreProductCost)
 

	
 
from .batch.coremember import CoreMemberBatch, CoreMemberBatchRow
rattail_corepos/db/model/batch/__init__.py
Show inline comments
 
new file 100644
rattail_corepos/db/model/batch/coremember.py
Show inline comments
 
new file 100644
 
# -*- coding: utf-8; -*-
 
################################################################################
 
#
 
#  Rattail -- Retail Software Framework
 
#  Copyright © 2010-2021 Lance Edgar
 
#
 
#  This file is part of Rattail.
 
#
 
#  Rattail 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.
 
#
 
#  Rattail 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
 
#  Rattail.  If not, see <http://www.gnu.org/licenses/>.
 
#
 
################################################################################
 
"""
 
Schema for CORE member update batch
 
"""
 

	
 
import sqlalchemy as sa
 

	
 
from rattail.db import model
 
from rattail.db.core import filename_column
 

	
 

	
 
class CoreMemberBatch(model.BatchMixin, model.Base):
 
    """
 
    Hopefully generic batch for adding / updating member data in CORE.
 
    """
 
    batch_key = 'corepos_member'
 
    __tablename__ = 'batch_corepos_member'
 
    __batchrow_class__ = 'CoreMemberBatchRow'
 
    model_title = "CORE Member Batch"
 
    model_title_plural = "CORE Member Batches"
 

	
 
    STATUS_OK                           = 1
 
    STATUS_CANNOT_PARSE_FILE            = 2
 

	
 
    STATUS = {
 
        STATUS_OK                       : "ok",
 
        STATUS_CANNOT_PARSE_FILE        : "cannot parse file",
 
    }
 

	
 
    input_file = filename_column(nullable=True, doc="""
 
    Base name of the input data file.
 
    """)
 

	
 

	
 
class CoreMemberBatchRow(model.BatchRowMixin, model.Base):
 
    """
 
    Row of data within a CORE member batch.
 
    """
 
    __tablename__ = 'batch_corepos_member_row'
 
    __batch_class__ = CoreMemberBatch
 

	
 
    STATUS_NO_CHANGE                    = 1
 
    STATUS_MEMBER_NOT_FOUND             = 2
 
    STATUS_FIELDS_CHANGED               = 3
 

	
 
    STATUS = {
 
        STATUS_NO_CHANGE                : "no change",
 
        STATUS_MEMBER_NOT_FOUND         : "member not found",
 
        STATUS_FIELDS_CHANGED           : "update member",
 
    }
 

	
 
    card_number = sa.Column(sa.Integer(), nullable=True)
 

	
 
    first_name = sa.Column(sa.String(length=30), nullable=True)
 
    first_name_old = sa.Column(sa.String(length=30), nullable=True)
 
    last_name = sa.Column(sa.String(length=30), nullable=True)
 
    last_name_old = sa.Column(sa.String(length=30), nullable=True)
 

	
 
    street = sa.Column(sa.String(length=255), nullable=True)
 
    street_old = sa.Column(sa.String(length=255), nullable=True)
 
    city = sa.Column(sa.String(length=20), nullable=True)
 
    city_old = sa.Column(sa.String(length=20), nullable=True)
 
    state = sa.Column(sa.String(length=2), nullable=True)
 
    state_old = sa.Column(sa.String(length=2), nullable=True)
 
    zipcode = sa.Column(sa.String(length=10), nullable=True)
 
    zipcode_old = sa.Column(sa.String(length=10), nullable=True)
 

	
 
    phone = sa.Column(sa.String(length=30), nullable=True)
 
    phone_old = sa.Column(sa.String(length=30), nullable=True)
 

	
 
    email1 = sa.Column(sa.String(length=50), nullable=True)
 
    email1_old = sa.Column(sa.String(length=50), nullable=True)
 
    email2 = sa.Column(sa.String(length=50), nullable=True)
 
    email2_old = sa.Column(sa.String(length=50), nullable=True)
 

	
 
    member_type_id = sa.Column(sa.SmallInteger(), nullable=True)
 
    member_type_id_old = sa.Column(sa.SmallInteger(), nullable=True)
0 comments (0 inline, 0 general) First comment
You need to be logged in to comment. Login now