zammad/app/models/concerns/can_csv_import.rb

# Copyright (C) 2012-2023 Zammad Foundation, https://zammad-foundation.org/

module CanCsvImport
  extend ActiveSupport::Concern

  # methods defined here are going to extend the class, not the instance of it
  class_methods do

=begin

  result = Model.csv_import(
    string: csv_string,
    parse_params: {
      col_sep: ',',
    },
    try: true,
    delete: false,
  )

  result = Model.csv_import(
    file: '/file/location/of/file.csv',
    parse_params: {
      col_sep: ',',
    },
    try: true,
    delete: false,
  )

  result = TextModule.csv_import(
    file: '/Users/me/Downloads/Textbausteine_final.csv',
    parse_params: {
      col_sep: ',',
    },
    try: false,
    delete: false,
  )

returns

  {
    records: [record1, ...]
    try: true, # true|false
    success: true, # true|false
  }

=end

    def csv_import(data)
      try    = data[:try].to_s == 'true'
      delete = data[:delete].to_s == 'true'

      begin
        data[:string] = File.read(data[:file]) if data[:file].present?
      rescue Errno::ENOENT
        raise Exceptions::UnprocessableEntity, "No such file '#{data[:file]}'"
      rescue => e
        raise Exceptions::UnprocessableEntity, "Unable to read file '#{data[:file]}': #{e.inspect}"
      end

      require 'csv' # Only load it when it's really needed to save memory.
      header, *rows = ::CSV.parse(data[:string], **data[:parse_params])

      header&.each do |column|
        column.try(:strip!)
        column.try(:downcase!)
      end

      begin
        raise "Delete is not possible for #{self}." if delete && !csv_delete_possible
        raise "Unable to parse empty file/string for #{self}." if data[:string].blank?
        raise "Unable to parse file/string without header for #{self}." if header.blank?
        raise "No records found in file/string for #{self}." if rows.first.blank?
        raise "No lookup column like #{lookup_keys.map(&:to_s).join(',')} for #{self} found." if (header & lookup_keys.map(&:to_s)).none?
      rescue => e
        return {
          try:    try,
          result: 'failed',
          errors: [e.message],
        }
      end

      # get payload based on csv
      payload = []
      rows.each do |row|
        if row.first(2).any?(&:present?)
          payload.push(
            header.zip(row).to_h
                  .compact.transform_values(&:strip)
                  .except(nil).transform_keys(&:to_sym)
                  .except(*csv_attributes_ignored)
                  .merge(data[:fixed_params] || {})
          )
        else
          header.zip(row).to_h
                .compact.transform_values(&:strip)
                .except(nil).except('').transform_keys(&:to_sym)
                .each do |col, val|
                  next if val.blank?

                  payload.last[col] = [*payload.last[col], val]
                end
        end
      end

      stats = {
        created: 0,
        updated: 0,
        deleted: (count if delete),
      }.compact

      # delete
      destroy_all if delete && !try

      # create or update records
      records = []
      errors  = []

      transaction do
        payload.each.with_index do |attributes, i|
          record = (lookup_keys & attributes.keys).lazy.map do |lookup_key|
            params = attributes.slice(lookup_key)
            params.transform_values!(&:downcase) if lookup_key.in?(%i[email login])
            lookup(**params)
          end.detect(&:present?)

          if record&.in?(records)
            errors.push "Line #{i.next}: duplicate record found."
            next
          end

          if !record && attributes[:id].present?
            errors.push "Line #{i.next}: unknown #{self} with id '#{attributes[:id]}'."
            next
          end

          if record&.id&.in?(csv_object_ids_ignored)
            errors.push "Line #{i.next}: unable to update #{self} with id '#{attributes[:id]}'."
            next
          end

          begin
            clean_params = association_name_to_id_convert(attributes)
          rescue => e
            errors.push "Line #{i.next}: #{e.message}"
            next
          end

          # create object
          Transaction.execute(disable_notification: true, reset_user_id: true, bulk: true) do
            UserInfo.current_user_id = clean_params[:updated_by_id] || clean_params[:created_by_id]

            if !record || delete == true
              stats[:created] += 1
              begin
                csv_verify_attributes(clean_params)

                record = new(param_cleanup(clean_params).reverse_merge(created_by_id: 1, updated_by_id: 1))
                record.associations_from_param(attributes)
                record.save!
              rescue => e
                errors.push "Line #{i.next}: Unable to create record - #{e.message}"
                next
              end
            else
              stats[:updated] += 1

              begin
                csv_verify_attributes(clean_params)
                clean_params = param_cleanup(clean_params).reverse_merge(updated_by_id: 1)

                record.with_lock do
                  record.associations_from_param(attributes)
                  record.assign_attributes(clean_params)
                  record.save! if record.changed?
                end
              rescue => e
                errors.push "Line #{i.next}: Unable to update record - #{e.message}"
                next
              end
            end
          end

          records.push record if record
        end
      ensure
        raise ActiveRecord::Rollback if try || errors.any?
      end

      {
        stats:   stats,
        records: records,
        errors:  errors,
        try:     try,
        result:  errors.empty? ? 'success' : 'failed',
      }
    end

=begin

verify if attributes are valid, will raise an ArgumentError with "unknown attribute '#{key}' for #{new.class}."

  Model.csv_verify_attributes({'attribute': 'some value'})

=end

    def csv_verify_attributes(clean_params)
      all_clean_attributes = {}
      new.attributes.each_key do |attribute|
        all_clean_attributes[attribute.to_sym] = true
      end
      reflect_on_all_associations.map do |assoc|
        all_clean_attributes[assoc.name.to_sym] = true
        ref = if assoc.name.to_s.end_with?('_id')
                "#{assoc.name}_id"
              else
                "#{assoc.name.to_s.chop}_ids"
              end
        all_clean_attributes[ref.to_sym] = true
      end
      clean_params.each_key do |key|
        next if all_clean_attributes.key?(key.to_sym)

        raise ArgumentError, "unknown attribute '#{key}' for #{new.class}."
      end
      true
    end

=begin

  csv_string = Model.csv_example(
    col_sep: ',',
  )

returns

  csv_string

=end

    def csv_example(params = {})
      header = []
      records = where.not(id: csv_object_ids_ignored).offset(1).limit(23).to_a
      if records.count < 20
        record_ids = records.pluck(:id).concat(csv_object_ids_ignored)
        local_records = where.not(id: record_ids).limit(20 - records.count)
        records.concat(local_records)
      end
      records_attributes_with_association_names = []
      records.each do |record|
        record_attributes_with_association_names = record.attributes_with_association_names
        records_attributes_with_association_names.push record_attributes_with_association_names
      end
      new.attributes_with_association_names(empty_keys: true).each do |key, value|
        next if value.instance_of?(ActiveSupport::HashWithIndifferentAccess)
        next if value.instance_of?(Hash)
        next if csv_attributes_ignored&.include?(key.to_sym)
        next if key.end_with?('_id')
        next if key.end_with?('_ids')
        next if key == 'created_by'
        next if key == 'updated_by'
        next if key == 'created_at'
        next if key == 'updated_at'
        next if header.include?(key)

        header.push key
      end

      rows = []
      records_attributes_with_association_names.each do |record|
        row = []
        rows_to_add = []
        position = -1
        header.each do |key|
          position += 1
          if record[key].instance_of?(ActiveSupport::TimeWithZone)
            row.push record[key].iso8601
            next
          end
          if record[key].instance_of?(Array)
            entry_count = -2
            record[key].each do |entry|
              entry_count += 1
              next if entry_count == -1

              if !rows_to_add[entry_count]
                rows_to_add[entry_count] = Array.new(header.count + 1) { '' }
              end
              rows_to_add[entry_count][position] = entry
            end
            record[key] = record[key][0]
          end
          row.push record[key]
        end
        rows.push row
        next if rows_to_add.count.zero?

        rows_to_add.each do |item|
          rows.push item
        end
        rows_to_add = []
      end

      require 'csv' # Only load it when it's really needed to save memory.
      ::CSV.generate(**params) do |csv|
        csv << header
        rows.each do |row|
          csv << row
        end
      end
    end

=begin

serve method to ignore model based on id

class Model < ApplicationModel
  include CanCsvImport
  csv_object_ids_ignored(1, 2, 3)
end

=end

    def csv_object_ids_ignored(*object_ids)
      return @csv_object_ids_ignored || [] if object_ids.empty?

      @csv_object_ids_ignored = object_ids
    end

=begin

serve method to ignore model attributes

class Model < ApplicationModel
  include CanCsvImport
  csv_attributes_ignored :password,
    :image_source,
    :login_failed,
    :source,
    :image_source,
    :image,
    :authorizations,
    :organizations

end

=end

    def csv_attributes_ignored(*attributes)
      return @csv_attributes_ignored || [] if attributes.empty?

      @csv_attributes_ignored = attributes
    end

=begin

serve method to define if delete option is possible or not

class Model < ApplicationModel
  include CanCsvImport
  csv_delete_possible true

end

=end

    def csv_delete_possible(*value)
      return @csv_delete_possible if value.empty?

      @csv_delete_possible = value.first
    end
  end
end