class CrujCrujCruj::Services::ImportRules

Public Class Methods

add_data_validation(sheet, column_number, validation, allowBlank, parameters_sheet_name) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 92
def self.add_data_validation(sheet, column_number, validation, allowBlank, parameters_sheet_name)
  column_letter = number2column(column_number)
  values_size   = validation.is_a?(Array) ? validation.size : validation[:clazz].all.count
  sheet.add_data_validation("#{column_letter}2:#{column_letter}1048576", type: :list, allowBlank: allowBlank,
                                                                         formula1: "#{parameters_sheet_name}!$#{column_letter}$2:$#{column_letter}$#{values_size + 1}")
end
create_validations(fields, workbook, parameters_sheet_name) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 99
def self.create_validations(fields, workbook, parameters_sheet_name)
  workbook.add_worksheet(name: parameters_sheet_name) do |sheet|
    sheet.add_row [:id] | fields.map { |field| field[:field_name] }

    cols_values = [[]].concat(fields.map { |field| field_validation_values(field) })

    cols_values.map { |values| values.size }.max.times do |i|
      row = cols_values.map { |v| v[i] }
      sheet.add_row row, types: row.map { |_| :string }
    end
  end
end
export_template(fields, data) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 6
def self.export_template(fields, data)
  package          = Axlsx::Package.new
  workbook         = package.workbook

  parameters_sheet_name = 'PARAMETERS'

  workbook.add_worksheet(name: 'TEMPLATE') do |sheet|
    title = sheet.styles.add_style(bg_color: 'FF007E7A', fg_color: 'FFFFFFFF', sz: 12, b: true,
                                   border: { style: :thin, color: 'FF000000' })

    fields.each_with_index do |field, idx|
      validation  = field[:data_validation][:validation]
      allow_blank = field[:data_validation][:allow_blank]
      add_data_validation(sheet, idx + 1, validation, allow_blank, parameters_sheet_name) if validation
    end

    sheet.add_row (([:id] | fields.map do |field|
                              field[:field_name]
                            end).map { |t| I18n.t("#{t}_label") }), style: title

    data.map do |resource|
      [resource.id].concat(fields.map do |field|
                             resource_field_value(resource, field)
                           end)
    end.each do |row|
      sheet.add_row row, types: row.map { |_| :string }
    end
  end

  create_validations(fields, workbook, parameters_sheet_name)

  filename = "tmp/template_#{Time.zone.now.strftime('%Y%m%d%H%M%S')}.xlsx"
  package.serialize(filename)
  filename
end
fetch(row, col, spreadsheet) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 161
def self.fetch(row, col, spreadsheet)
  if spreadsheet.celltype(row,
                          col + 1) == :float
    spreadsheet.send(:cell_to_csv, row, col + 1,
                     spreadsheet.default_sheet)
  else
    row[col]
  end
end
field_validation_values(field) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 121
def self.field_validation_values(field)
  if field[:data_validation][:validation]
    if field[:data_validation][:validation].is_a?(Array)
      field[:data_validation][:validation]
    elsif Rails::VERSION::MAJOR < 4
      field[:data_validation][:validation][:clazz].order((field[:data_validation][:validation][:field])).map(&field[:data_validation][:validation][:field])
    else
      field[:data_validation][:validation][:clazz].all.order(field[:data_validation][:validation][:field]).pluck(field[:data_validation][:validation][:field])
    end
  else
    []
  end
end
import(file, fields, clazz) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 42
def self.import(file, fields, clazz)
  spreadsheet = Roo::Spreadsheet.open(file.path, extension: :xlsx)

  errors = []

  invalid_rows = if Rails::VERSION::MAJOR < 4
                   validate_id_array(spreadsheet, clazz)
                 else
                   validate_id(spreadsheet, clazz)
                 end
  unless invalid_rows.blank?
    errors << I18n.t(:column_invalid_rows, column: :id,
                                           rows: invalid_rows.first(25).join(', '))
  end

  fields.each_with_index do |field, idx|
    col = idx + 1
    unless field[:data_validation][:allow_blank]
      invalid_rows = validate_not_blank(col, spreadsheet)
      unless invalid_rows.blank?
        errors << I18n.t(:column_invalid_rows, column: field[:field_name],
                                               rows: invalid_rows.first(25).join(', '))
      end
    end

    next unless field[:data_validation][:validation]

    invalid_rows = validate_in_array(col, field_validation_values(field), spreadsheet)
    unless invalid_rows.blank?
      errors << I18n.t(:column_invalid_rows, column: field[:field_name],
                                             rows: invalid_rows.first(25).join(', '))
    end
  end

  return errors unless errors.blank?

  (2..spreadsheet.last_row).each do |i|
    row = spreadsheet.row(i)
    id  = row[0]
    resource = id ? clazz.find(id) : clazz.new

    fields.each_with_index do |field, idx|
      resource.send("#{field[:field_name]}=", row_value(row, idx + 1, field, spreadsheet))
      resource.save!
    end
  end

  {}
end
number2column(number) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 135
def self.number2column(number)
  Hash.new { |hash, key| hash[key] = hash[key - 1].next }.merge({ 0 => 'A' })[number]
end
resource_field_value(resource, field) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 112
def self.resource_field_value(resource, field)
  if field[:data_validation][:validation] && !field[:data_validation][:validation].is_a?(Array)
    relation = resource.send(field[:field_name])
    relation ? relation.send(field[:data_validation][:validation][:field]) : nil
  else
    resource.send(field[:field_name])
  end
end
row_value(row, col, field, spreadsheet) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 171
def self.row_value(row, col, field, spreadsheet)
  if field[:data_validation][:validation] && !field[:data_validation][:validation].is_a?(Array)
    field[:data_validation][:validation][:clazz].send("find_by_#{field[:data_validation][:validation][:field]}",
                                                      fetch(row, col, spreadsheet))
  else
    fetch(row, col, spreadsheet)
  end
end
validate_id(spreadsheet, clazz) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 139
def self.validate_id(spreadsheet, clazz)
  validate_in_array(0, clazz.all.pluck(:id).map(&:to_s), spreadsheet)
end
validate_id_array(spreadsheet, clazz) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 143
def self.validate_id_array(spreadsheet, clazz)
  validate_in_array(0, clazz.all.map(&:id).map(&:to_s), spreadsheet)
end
validate_in_array(col, array, spreadsheet) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 154
def self.validate_in_array(col, array, spreadsheet)
  (2..spreadsheet.last_row)
    .map { |i| { i => (spreadsheet.row(i)[col].blank? || array.map(&:to_s).map(&:downcase).include?(spreadsheet.row(i)[col].to_s.downcase)) } }
    .select { |e| !e.values.first }
    .map { |e| e.keys.first }
end
validate_not_blank(col, spreadsheet) click to toggle source
# File lib/cruj_cruj_cruj/services/import_rules.rb, line 147
def self.validate_not_blank(col, spreadsheet)
  (2..spreadsheet.last_row)
    .map { |i| { i => !spreadsheet.row(i)[col].blank? } }
    .select { |e| !e.values.first }
    .map { |e| e.keys.first }
end