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