Class: CanadianTire::StoreAddressChangesImporter

Inherits:
Object
  • Object
show all
Defined in:
app/services/canadian_tire/store_address_changes_importer.rb

Overview

Applies the +StoreAddressChanges+ sheet of a Canadian Tire "Store listings"
.xlsx workbook as a delta against +canadian_tire_store_addresses+.

Replaces the previous full drop/recreate migration pattern (see
+db/migrate/refresh_canadian_tire_store_addresses_from_csv.rb+) with a
per-row upsert driven by the sheet's +CHANGE TYPE+ column.

== Change-type action mapping

[+ADDITION+] insert a new row; +new_store_indicator: true+,
+active_indicator: true+.
[+DELETION+] hard-delete the row by +store_number+ (history is
preserved on referencing records elsewhere).
[+ADDRESS CHANGES+,
+STREET ADDRESS CHANGE+,
+CITY, PROV CHANGE+] update the seven non-key fields with the row's
values (CTC labeling drift — all reduce to UPDATE).
[anything else] recorded as an error so a new CTC label surfaces
on first occurrence instead of silently no-oping.

== Defensive cleanups

Every CTC sheet ships with whitespace padding on every header and every
cell, and a couple of recent workbooks carry junk trailing header columns
(+#N/A+, +RESS+, +ESS 1+) from clipboard accidents. This importer strips
every value, ignores any column past the canonical 11, and applies the
same +postal_code.gsub(' ', '')+ normalization the legacy CSV importer
does at +db/migrate/20260407180000_refresh_canadian_tire_store_addresses_from_csv_040726.rb:41+.

== Usage

result = CanadianTire::StoreAddressChangesImporter.call(path: '/tmp/ctc.xlsx')
result.success? # => true
result.summary # => "added=11 deleted=27 updated=1 skipped=0 errors=0"

Pass +dry_run: true+ to validate without persisting (transaction is rolled
back at the end of the run).

Defined Under Namespace

Classes: Result

Constant Summary collapse

SHEET_NAME = 
'StoreAddressChanges'
HEADER_TO_ATTR =

Canonical header → DB column mapping. The two trailing entries are
sheet-only and don't get written to the DB (they drive routing and are
logged for auditability).

{
  'STORE #'            => :store_number,
  'N=NEW STORE'        => :new_store_indicator,
  'LOCATION NAME'      => :location_name,
  'LOCATION ADDRESS 1' => :location_address_name,
  'LOCATION ADDRESS 2' => :location_address_street,
  'CITY'               => :city,
  'PROV'               => :state_code,
  'COUNTRY'            => :country_iso,
  'POSTAL CODE'        => :postal_code,
  'CHANGE TYPE'        => :_change_type,
  'DATE OF CHANGE'     => :_date_of_change,
}.freeze
UPDATE_TYPES = 
['ADDRESS CHANGES', 'STREET ADDRESS CHANGE', 'CITY, PROV CHANGE'].freeze

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(path:, dry_run: false, logger: Rails.logger) ⇒ StoreAddressChangesImporter

Returns a new instance of StoreAddressChangesImporter.



90
91
92
93
94
95
96
97
98
99
 
# File 'app/services/canadian_tire/store_address_changes_importer.rb', line 90

def initialize(path:, dry_run: false, logger: Rails.logger)
  @path = path
  @dry_run = dry_run
  @logger = logger
  @added = 0
  @deleted = 0
  @updated = 0
  @skipped = []
  @errors = []
end

Instance Attribute Details

#added [Integer] count of +ADDITION+ rows inserted.([Integer]) ⇒ Object (readonly)

Outcome of a single import run.



74
75
76
77
78
79
80
81
 
# File 'app/services/canadian_tire/store_address_changes_importer.rb', line 74

Result = Data.define(:added, :deleted, :updated, :skipped, :errors) do
  def initialize(added: 0, deleted: 0, updated: 0, skipped: [], errors: []) = super
  def success? = errors.empty?
  def summary
    "added=#{added} deleted=#{deleted} updated=#{updated} " \
      "skipped=#{skipped.length} errors=#{errors.length}"
  end
end

#deleted [Integer] count of +DELETION+ rows destroyed.([Integer]) ⇒ Object (readonly)

Outcome of a single import run.



74
75
76
77
78
79
80
81
 
# File 'app/services/canadian_tire/store_address_changes_importer.rb', line 74

Result = Data.define(:added, :deleted, :updated, :skipped, :errors) do
  def initialize(added: 0, deleted: 0, updated: 0, skipped: [], errors: []) = super
  def success? = errors.empty?
  def summary
    "added=#{added} deleted=#{deleted} updated=#{updated} " \
      "skipped=#{skipped.length} errors=#{errors.length}"
  end
end

#errors [Array<Hash>] rows that failed validation —([Array<Hash>]) ⇒ Object (readonly)

unknown +CHANGE TYPE+ or exception during apply.



74
75
76
77
78
79
80
81
 
# File 'app/services/canadian_tire/store_address_changes_importer.rb', line 74

Result = Data.define(:added, :deleted, :updated, :skipped, :errors) do
  def initialize(added: 0, deleted: 0, updated: 0, skipped: [], errors: []) = super
  def success? = errors.empty?
  def summary
    "added=#{added} deleted=#{deleted} updated=#{updated} " \
      "skipped=#{skipped.length} errors=#{errors.length}"
  end
end

#skipped [Array<Hash>] rows that were intentionally not([Array<Hash>]) ⇒ Object (readonly)

applied (idempotency hits — missing target, duplicate add, blank type),
each as +{ row:, store:, reason: }+.



74
75
76
77
78
79
80
81
 
# File 'app/services/canadian_tire/store_address_changes_importer.rb', line 74

Result = Data.define(:added, :deleted, :updated, :skipped, :errors) do
  def initialize(added: 0, deleted: 0, updated: 0, skipped: [], errors: []) = super
  def success? = errors.empty?
  def summary
    "added=#{added} deleted=#{deleted} updated=#{updated} " \
      "skipped=#{skipped.length} errors=#{errors.length}"
  end
end

#updated [Integer] count of update-variant rows applied.([Integer]) ⇒ Object (readonly)

Outcome of a single import run.



74
75
76
77
78
79
80
81
 
# File 'app/services/canadian_tire/store_address_changes_importer.rb', line 74

Result = Data.define(:added, :deleted, :updated, :skipped, :errors) do
  def initialize(added: 0, deleted: 0, updated: 0, skipped: [], errors: []) = super
  def success? = errors.empty?
  def summary
    "added=#{added} deleted=#{deleted} updated=#{updated} " \
      "skipped=#{skipped.length} errors=#{errors.length}"
  end
end

Class Method Details

.call ⇒ Result

Parameters:

  • path (String, Pathname) —

    path to a CTC .xlsx workbook.

  • dry_run (Boolean) —

    when true, the transaction is rolled back at
    the end so no DB changes persist (counts in +Result+ still reflect
    what would have changed).

Returns:



88
 
# File 'app/services/canadian_tire/store_address_changes_importer.rb', line 88

def self.call(...) = new(...).call

Instance Method Details

#call ⇒ Object 



101
102
103
104
105
106
107
108
109
 
# File 'app/services/canadian_tire/store_address_changes_importer.rb', line 101

def call
  rows = read_rows
  ApplicationRecord.transaction do
    rows.each { |row| apply_row(row) }
    raise ActiveRecord::Rollback if @dry_run
  end
  Result.new(added: @added, deleted: @deleted, updated: @updated,
             skipped: @skipped, errors: @errors)
end