module DutyFree::Extensions::ClassMethods

Public Instance Methods

df_export(is_with_data = true, import_template = nil, use_inner_joins = false) { |relation, mapping| ... } click to toggle source

Export at least column header, and optionally include all existing data as well

# File lib/duty_free/extensions.rb, line 21
def df_export(is_with_data = true, import_template = nil, use_inner_joins = false)
  use_inner_joins = true unless respond_to?(:left_joins)
  # In case they are only supplying the columns hash
  if is_with_data.is_a?(Hash) && !import_template
    import_template = is_with_data
    is_with_data = true
  end
  import_template ||= if constants.include?(:IMPORT_TEMPLATE)
                        self::IMPORT_TEMPLATE
                      else
                        suggest_template(0, false, false)
                      end

  # Friendly column names that end up in the first row of the CSV
  # Required columns get prefixed with a *
  requireds = (import_template[:required] || [])
  rows = ::DutyFree::Extensions._template_columns(self, import_template).map do |col|
    is_required = requireds.include?(col)
    col = col.to_s.titleize
    # Alias-ify the full column names
    aliases = (import_template[:as] || [])
    aliases.each do |k, v|
      if col.start_with?(v)
        col = k + col[v.length..-1]
        break
      end
    end
    (is_required ? '* ' : '') + col
  end
  rows = [rows]

  if is_with_data
    order_by = []
    order_by << ['_', primary_key] if primary_key
    all = import_template[:all] || import_template[:all!]
    # Automatically create a JOINs strategy and select list to get back all related rows
    template_cols, template_joins = ::DutyFree::Extensions._recurse_def(self, all, import_template, nil, order_by)
    # We do this so early here because it removes type objects from template_joins so then
    # template_joins can immediately be used for inner and outer JOINs.
    our_names = [[self, '_']] + ::DutyFree::Util._recurse_arel(template_joins)
    relation = use_inner_joins ? joins(template_joins) : left_joins(template_joins)

    # So we can properly create the SELECT list, create a mapping between our
    # column alias prefixes and the aliases AREL creates.
    # %%% If with Rails 3.1 and older you get "NoMethodError: undefined method `eq' for nil:NilClass"
    # when trying to call relation.arel, then somewhere along the line while navigating a has_many
    # relationship it can't find the proper foreign key.
    core = relation.arel.ast.cores.first
    # Accommodate AR < 3.2
    arel_alias_names = if core.froms.is_a?(Arel::Table)
                         # All recent versions of AR have #source which brings up an Arel::Nodes::JoinSource
                         ::DutyFree::Util._recurse_arel(core.source)
                       else
                         # With AR < 3.2, "froms" brings up the top node, an Arel::Nodes::InnerJoin
                         ::DutyFree::Util._recurse_arel(core.froms)
                       end
    # Make sure our_names lines up with the arel_alias_name by comparing the ActiveRecord type.
    # AR < 5.0 behaves differently than newer versions, and AR 5.0 and 5.1 have a bug in the
    # way the types get determined, so if we want perfect results then we must compensate for
    # these foibles.  Thank goodness that AR 5.2 and later find the proper type and make it
    # available through the type_caster object, which is what we use when building the list of
    # arel_alias_names.
    mapping = arel_alias_names.each_with_object({}) do |arel_alias_name, s|
      if our_names.first&.first == arel_alias_name.first
        s[our_names.first.last] = arel_alias_name.last
        our_names.shift
      end
      s
    end
    relation = (order_by.empty? ? relation : relation.order(order_by.map { |o| "#{mapping[o.first]}.#{o.last}" }))
    # puts mapping.inspect
    # puts relation.dup.select(template_cols.map { |x| x.to_s(mapping) }).to_sql

    # Allow customisation of query before running it
    relation = yield(relation, mapping) if block_given?

    relation&.select(template_cols.map { |x| x.to_s(mapping) })&.each do |result|
      rows << ::DutyFree::Extensions._template_columns(self, import_template).map do |col|
        value = result.send(col)
        case value
        when true
          'Yes'
        when false
          'No'
        else
          value.to_s
        end
      end
    end
  end
  rows
end
df_import(data, import_template = nil) click to toggle source
# File lib/duty_free/extensions.rb, line 114
def df_import(data, import_template = nil)
  ::DutyFree::Extensions.import(self, data, import_template)
end

Private Instance Methods

_defined_uniques(uniques, cols = [], col_list = nil, starred = [], trim_prefix = '') click to toggle source
# File lib/duty_free/extensions.rb, line 120
def _defined_uniques(uniques, cols = [], col_list = nil, starred = [], trim_prefix = '')
  col_list ||= cols.join('|')
  unless (defined_uniq = (@defined_uniques ||= {})[col_list])
    utilised = {} # Track columns that have been referenced thusfar
    defined_uniq = uniques.each_with_object({}) do |unique, s|
      if unique.is_a?(Array)
        key = []
        value = []
        unique.each do |unique_part|
          val = (unique_part_name = unique_part.to_s.titleize).start_with?(trim_prefix) &&
                cols.index(upn = unique_part_name[trim_prefix.length..-1])
          next unless val

          key << upn
          value << val
        end
        unless key.empty?
          s[key] = value
          utilised[key] = nil
        end
      else
        val = (unique_name = unique.to_s.titleize).start_with?(trim_prefix) &&
              cols.index(un = unique_name[trim_prefix.length..-1])
        if val
          s[[un]] = [val]
          utilised[[un]] = nil
        end
      end
      s
    end
    if defined_uniq.empty?
      (starred - utilised.keys).each { |star| defined_uniq[[star]] = [cols.index(star)] }
      # %%% puts "Tried to establish #{defined_uniq.inspect}"
    end
    @defined_uniques[col_list] = defined_uniq
  end
  defined_uniq
end
_find_existing(uniques, cols, starred, import_template, keepers, train_we_came_in_here_on, row = nil, klass_or_collection = nil, template_all = nil, trim_prefix = '', assoc = nil, base_obj = nil) click to toggle source

For use with importing, based on the provided column list calculate all valid combinations of unique columns. If there is no valid combination, throws an error. Returns an object found by this means, as well as the criteria that was used to find it.

# File lib/duty_free/extensions.rb, line 162
def _find_existing(uniques, cols, starred, import_template, keepers, train_we_came_in_here_on,
                   row = nil, klass_or_collection = nil, template_all = nil, trim_prefix = '',
                   assoc = nil, base_obj = nil)
  unless trim_prefix.blank?
    cols = cols.map { |c| c.start_with?(trim_prefix) ? c[trim_prefix.length..-1] : nil }
    starred = starred.each_with_object([]) do |v, s|
      s << v[trim_prefix.length..-1] if v.start_with?(trim_prefix)
      s
    end
  end
  col_list = cols.join('|')

  # First add in foreign key stuff we can find from belongs_to associations (other than the
  # one we might have arrived here upon).
  criteria = {} # Enough detail to find or build a new object
  bt_criteria = {}
  bt_criteria_all_nil = true
  bt_col_indexes = []
  available_bts = []
  only_valid_uniques = (train_we_came_in_here_on == false)
  uniq_lookups = {} # The data, or how to look up the data

  vus = ((@valid_uniques ||= {})[col_list] ||= {}) # Fancy memoisation

  # First, get an overall list of AVAILABLE COLUMNS before considering tricky foreign key stuff.
  # ============================================================================================
  # Generate a list of column names matched up with their zero-ordinal column number mapping for
  # all columns from the incoming import data.
  if (is_new_vus = vus.empty?)
    template_column_objects = ::DutyFree::Extensions._recurse_def(
      self,
      template_all || import_template[:all],
      import_template
    ).first
    available = if trim_prefix.blank?
                  template_column_objects.select { |col| col.pre_prefix.blank? && col.prefix.blank? }
                else
                  trim_prefix_snake = trim_prefix.downcase.tr(' ', '_')
                  template_column_objects.select do |col|
                    this_prefix = ::DutyFree::Util._prefix_join([col.pre_prefix, col.prefix], '_').tr('.', '_')
                    trim_prefix_snake == "#{this_prefix}_"
                  end
                end.map { |avail| avail.name.to_s.titleize }
  end

  # Process FOREIGN KEY stuff by going through each belongs_to in this model.
  # =========================================================================
  # This list of all valid uniques will help to filter which foreign keys are kept, and also
  # get further filtered later to arrive upon a final set of valid uniques.  (Often but not
  # necessarily a specific valid unique as perhaps with a list of users you want to update some
  # folks based on having their email as a unique identifier, and other folks by having a
  # combination of their name and street address as unique, and use both of those possible
  # unique variations to update phone numbers, and do that all as a part of one import.)
  all_vus = _defined_uniques(uniques, cols, col_list, starred, trim_prefix)

  # %%% Ultimately may consider making this recursive
  reflect_on_all_associations.each do |sn_bt|
    next unless sn_bt.belongs_to? && (!train_we_came_in_here_on || sn_bt != train_we_came_in_here_on)

    # # %%% Make sure there's a starred column we know about from this one
    # uniq_lookups[sn_bt.foreign_key] = nil if only_valid_uniques

    # This search prefix becomes something like "Order Details Product "
    cols.each_with_index do |bt_col, idx|
      next if bt_col_indexes.include?(idx) ||
              !bt_col&.start_with?(bt_prefix = (trim_prefix + "#{sn_bt.name.to_s.underscore.tr('_', ' ').titleize} "))

      available_bts << bt_col
      fk_id = if row
                # Max ID so if there are multiple matches, only the most recent one is picked.
                # %%% Need to stack these up in case there are multiple
                # (like first_name, last_name on a referenced employee)
                sn_bt.klass.where(keepers[idx].name => row[idx]).limit(1).pluck(MAX_ID).first
              else
                # elsif is_new_vus
                #   # Add to our criteria if this belongs_to is required
                #   bt_req_by_default = sn_bt.klass.respond_to?(:belongs_to_required_by_default) &&
                #                       sn_bt.klass.belongs_to_required_by_default
                #   unless !vus.values.first&.include?(idx) &&
                #          (sn_bt.options[:optional] || (sn_bt.options[:required] == false) || !bt_req_by_default)
                #     # # Add this fk to the criteria
                #     # criteria[fk_name] = fk_id

                #     ref = [keepers[idx].name, idx]
                #     # bt_criteria[(fk_name = sn_bt.foreign_key)] ||= [sn_bt.klass, []]
                #     # bt_criteria[fk_name].last << ref
                #     # bt_criteria[bt_col] = [sn_bt.klass, ref]

                #     # Maybe this is the most useful
                #     # First array is friendly column names, second is references
                #     foreign_uniques = (bt_criteria[sn_bt.name] ||= [sn_bt.klass, [], []])
                #     foreign_uniques[1] << ref
                #     foreign_uniques[2] << bt_col
                #     vus[bt_col] = foreign_uniques # And we can look up this growing set from any foreign column
                [sn_bt.klass, keepers[idx].name, idx]
              end
      if fk_id
        bt_col_indexes << idx
        bt_criteria_all_nil = false
      end
      # If we're processing a row then this list of foreign key column name entries, named such as
      # "order_id" or "product_id" instead of column-specific stuff like "Order Date" and "Product Name",
      # is kept until the last and then gets merged on top of the other criteria before being returned.
      bt_criteria[(fk_name = sn_bt.foreign_key)] = fk_id

      # Check to see if belongs_tos are generally required on this specific table
      bt_req_by_default = sn_bt.klass.respond_to?(:belongs_to_required_by_default) &&
                          sn_bt.klass.belongs_to_required_by_default

      # Add to our CRITERIA just the belongs_to things that check out.
      # ==============================================================
      # The first check, "all_vus.keys.first.none? { |k| k.start_with?(bt_prefix) }"
      # is to see if one of the columns we're working with from the unique that we've chosen
      # comes from the table referenced by this belongs_to (sn_bt).
      #
      # The second check on the :optional option and bt_req_by_default comes directly from
      # how Rails 5 and later checks to see if a specific belongs_to is marked optional
      # (or required), and without having that indication will fall back on checking the model
      # itself to see if it requires belongs_tos by default.
      next if all_vus.keys.first.none? { |k| k.start_with?(bt_prefix) } &&
              (sn_bt.options[:optional] || !bt_req_by_default)

      # Add to the criteria
      criteria[fk_name] = fk_id
    end
  end

  # Now circle back find a final list of VALID UNIQUES by re-assessing the list of all valid uniques
  # in relation to the available belongs_tos found in the last foreign key step.
  if is_new_vus
    available += available_bts
    all_vus.each do |k, v|
      combined_k = []
      combined_v = []
      k.each_with_index do |key, idx|
        if available.include?(key)
          combined_k << key
          combined_v << v[idx]
        end
      end
      vus[combined_k] = combined_v unless combined_k.empty?
    end
  end

  # uniq_lookups = vus.inject({}) do |s, v|
  #   return s if available_bts.include?(v.first) # These will be provided in criteria, and not uniq_lookups

  #   # uniq_lookups[k[trim_prefix.length..-1].downcase.tr(' ', '_').to_sym] = val[idx] if k.start_with?(trim_prefix)
  #   s[v.first.downcase.tr(' ', '_').to_sym] = v.last
  #   s
  # end

  new_criteria_all_nil = bt_criteria_all_nil

  # Make sure they have at least one unique combination to take cues from
  unless vus.empty? # raise NoUniqueColumnError.new(I18n.t('import.no_unique_column_error'))
    # Convert the first entry to a simplified hash, such as:
    #   {[:investigator_institutions_name, :investigator_institutions_email] => [8, 9], ...}
    #     to {:name => 8, :email => 9}
    key, val = vus.first # Utilise the first identified set of valid uniques
    key.each_with_index do |k, idx|
      next if available_bts.include?(k) # These will be provided in criteria, and not uniq_lookups

      # uniq_lookups[k[trim_prefix.length..-1].downcase.tr(' ', '_').to_sym] = val[idx] if k.start_with?(trim_prefix)
      k_sym = k.downcase.tr(' ', '_').to_sym
      v = val[idx]
      uniq_lookups[k_sym] = v # The column number in which to find the data

      next if only_valid_uniques || bt_col_indexes.include?(v)

      # Find by all corresponding columns
      if (row_value = row[v])
        new_criteria_all_nil = false
        criteria[k_sym] = row_value # The data, or how to look up the data
      end
    end
  end

  return uniq_lookups.merge(criteria) if only_valid_uniques
  # If there's nothing to match upon then we're out
  return [nil, {}] if new_criteria_all_nil

  # With this criteria, find any matching has_many row we can so we can update it.
  # First try directly looking it up through ActiveRecord.
  klass_or_collection ||= self # Comes in as nil for has_one with no object yet attached
  # HABTM proxy
  # binding.pry if klass_or_collection.is_a?(ActiveRecord::Associations::CollectionProxy)
  # if klass_or_collection.respond_to?(:proxy_association) && klass_or_collection.proxy_association.options.include?(:through)
  # klass_or_collection.proxy_association.association_scope.to_a

  # if assoc.respond_to?(:require_association) && klass_or_collection.is_a?(Array)
  #   # else
  #   #   klass_or_collection = assoc.klass
  #   end
  # end
  found_object = case klass_or_collection
                 when ActiveRecord::Base # object from a has_one?
                   existing_object = klass_or_collection
                   klass_or_collection = klass_or_collection.class
                   other_object = klass_or_collection.find_by(criteria)
                   pk = klass_or_collection.primary_key
                   existing_object.send(pk) == other_object&.send(pk) ? existing_object : other_object
                 when Array # has_* in AR < 4.0
                   # Old AR doesn't have a CollectionProxy that can do any of this on its own.
                   base_id = base_obj.send(base_obj.class.primary_key)
                   if assoc.macro == :has_and_belongs_to_many || (assoc.macro == :has_many && assoc.options[:through])
                     # Find all association foreign keys, then find or create the foreign object
                     # based on criteria, and finally put an entry with both foreign keys into
                     # the associative table unless it already exists.
                     ajt = assoc.through_reflection&.table_name || assoc.join_table
                     fk = assoc.foreign_key
                     afk = assoc.association_foreign_key
                     existing_ids = ActiveRecord::Base.connection.execute(
                       "SELECT #{afk} FROM #{ajt} WHERE #{fk} = #{base_id}"
                     ).map { |r| r[afk] }
                     new_or_existing = assoc.klass.find_or_create_by(criteria)
                     new_or_existing_id = new_or_existing.send(new_or_existing.class.primary_key)
                     unless existing_ids.include?(new_or_existing_id)
                       ActiveRecord::Base.connection.execute(
                         "INSERT INTO #{ajt} (#{fk}, #{afk}) VALUES (#{base_id}, #{new_or_existing_id})"
                       )
                     end
                     new_or_existing
                   else # Must be a has_many
                     assoc.klass.find_or_create_by(criteria.merge({ assoc.foreign_key => base_id }))
                   end
                 else
                   klass_or_collection.find_by(criteria)
                 end
  # If not successful, such as when fields are exposed via helper methods instead of being
  # real columns in the database tables, try this more intensive approach.  This is useful
  # if you had full name kind of data coming in on a spreadsheeet, but in the destination
  # table it's broken out to first_name, middle_name, surname.  By writing both full_name
  # and full_name= methods, the importer can check to see if this entry is already there,
  # and put a new row in if not, having one incoming name break out to populate three
  # destination columns.
  unless found_object || klass_or_collection.is_a?(Array)
    found_object = klass_or_collection.find do |obj|
      is_good = true
      criteria.each do |k, v|
        if obj.send(k).to_s != v.to_s
          is_good = false
          break
        end
      end
      is_good
    end
  end
  # Standard criteria as well as foreign key column name detail with exact foreign keys
  # that match up to a primary key so that if needed a new related object can be built,
  # complete with all its association detail.
  [found_object, criteria.merge(bt_criteria)]
end