_form.html.haml

= breadcrumb resource
= simple_form_for resource, :html => { :class => 'form-horizontal', :remote => true } do |f|
  .toolbar
    = save_button(f)
    = cancel_button
  .row
    .span-6
      = f.association :old_product, :as => :lookup,
                      :indicator => resource.old_product.try(:name_with_part_number),
                      :url => lookups_products_path,
                      :search_placeholder => t('helpers.products_lookup_placeholder')
      = f.input :forward_code, :collection => ForwardSubstitutionCode.to_a
      = f.input :backward_code, :collection => BackwardSubstitutionCode.to_a
      = f.association :new_product, :as => :lookup,
                      :indicator => resource.new_product.try(:name_with_part_number),
                      :url => lookups_products_path,
                      :search_placeholder => t('helpers.products_lookup_placeholder')

_grid.html.haml

= breadcrumb :product_substitutions
.toolbar
  = link_to I18n.t('helpers.actions.new'), new_resource_path, :class => 'btn btn-primary', :remote => true
  = link_to I18n.t("helpers.actions.import_from_file"), import_resources_path, :class => 'btn', :id => 'import'
= table_for(collection) do
  %thead
    %tr
      %th= sortable_header(ProductSubstitution.human_attribute_name(:old_product_part_number), :old_product_part_number, :remote => true)
      %th= sortable_header(ProductSubstitution.human_attribute_name(:old_product_name), :old_product_name, :remote => true)
      %th= sortable_header(ProductSubstitution.human_attribute_name(:code), :code, :remote => true)
      %th= sortable_header(ProductSubstitution.human_attribute_name(:new_product_part_number), :new_product_part_number, :remote => true)
      %th= sortable_header(ProductSubstitution.human_attribute_name(:new_product_name), :new_product_name, :remote => true)
      %th.actions= I18n.t('helpers.actions.actions')
  %tbody
    - collection.each do |item|
      %tr
        %td= item.old_product.try(:part_number)
        %td= item.old_product.try(:name)
        %td= item.code
        %td= item.new_product.try(:part_number)
        %td= item.new_product.try(:name)
        %td
          = grid_row_toolbar(item)
= form_tag import_resources_path, :multipart => true, :class => 'hidden', :id => 'file_upload_form', :remote => true do
  = file_field_tag :file
  = text_field_tag :file_name
  = submit_tag
  = button_tag

CPD_SNAP_SUBS_29012013.csv

ÄÀÒÀ_ÐÅÃÈÑÒÐÀÖÈÈ_ÇÀÌÅÍÛ;ÄÀÒÀ_ÓÄÀËÅÍÈß_ÇÀÌÅÍÛ;ÑÒÀÐÛÉ_ÍÎÌÅÐ;ÍÎÌÅÐ_ÏÎ_ÇÀÌÅÍÅ;ÊÎÄ_ÏÐßÌÎÉ_ÇÀÌÅÍÛ;ÊÎÄ_ÎÁÐÀÒÍÎÉ_ÇÀÌÅÍÛ

28-JAN-13;;'SU00300319;'178010D011;01;NN
28-JAN-13;;'9091902260;'9091902248;01;YY
;28-JAN-13;'9091902248;'9091902260;01;NN
28-JAN-13;;'2880031260;'2880042042;01;NN
28-JAN-13;;'3510A44011;'3510A44012;01;NN
28-JAN-13;;'3510A44010;'3510A44011;01;NN
28-JAN-13;;'4440644140;'4440644141;01;NN
28-JAN-13;;'7608530090A0;'7608530091A0;01;NN
28-JAN-13;;'6881048021;'6881048070;01;NN
28-JAN-13;;'6882048011;'6882048040;01;NN

index.js.erb

new AutoHub.ProductSubstitutions.List("<%= j render('grid') %>");
window.History.navigate("<%= collection_path %>");

product.rb

# == Schema Information
#
# Table name: products
#
#  id                     :integer          not null, primary key
#  part_number            :string(255)
#  mask                   :string(255)
#  name                   :string(255)
#  fdp                    :decimal(16, 2)
#  fdp_currency_id        :integer
#  importer               :string(255)
#  product_code           :string(255)
#  pnc1                   :string(255)
#  pnc2                   :string(255)
#  pnc3                   :string(255)
#  length                 :integer
#  width                  :integer
#  height                 :integer
#  volume                 :integer
#  weight                 :integer
#  per_package            :integer
#  created_in_domain_id   :integer
#  created_in_division_id :integer
#  created_by_user_id     :integer
#  created_by_employee_id :integer
#  created_at             :datetime         not null
#  updated_at             :datetime         not null
#  product_group_id       :integer
#  manufacturer           :string(255)
#  measurement_unit_id    :integer
#  can_order              :boolean
#  fob                    :decimal(16, 4)
#  supplier_id            :integer
#

class Product < ActiveRecord::Base
  shared_in :company

  include NetCostable

  [:retail_price_nc,
   :retail_price_bc,
   :supplier,
   :supplier_id,
   :price_group,
   :price_group_id,
   :actual_incoming_note_item,
   :actual_incoming_note_item_id].each do |column|
      delegate column, "#{column}=", :to => :profile, :allow_nil => true
  end

  delegate :on_hand,
           :reserved,
           :to_sell,
           :to_sell_by_branches,
           :to_distribute,
           :to_distribute_original,
           :to_distribute_substitutes,
           :to_ship,
           :shipped,
           :to => :quantity,
           :prefix => :quantity

  belongs_to :fdp_currency, :class_name => Currency
  belongs_to :product_group
  belongs_to :measurement_unit
  belongs_to :supplier, :class_name => Customer
  has_one :profile, :class_name => ProductProfile, :dependent => :restrict, :autosave => true
  has_many :storage_slots, :dependent => :restrict
  has_many :reserves, :dependent => :restrict

  # FIX: сократить список полей, доступных для mass assignment
  attr_accessible(
    :part_number,
    :mask,
    :name,
    :fdp,
    :fdp_currency_id,
    :fob,
    :importer,
    :manufacturer,
    :product_code,
    :pnc1,
    :pnc2,
    :pnc3,
    :length,
    :width,
    :height,
    :volume,
    :weight,
    :per_package,
    :product_group_id,
    :measurement_unit_id,
    :retail_price_bc,
    :retail_price_nc,
    :supplier_id,
    :price_group_id
  )

  validates :part_number,
    :presence => true,
    :uniqueness => { :scope => :created_in_domain_id, :case_sensitive => false },
    :length => { :in => 3..16 },
    :ascii_only => true

  validates :name, :presence => true, :length => { :maximum => 64 }
  validates :product_code, :length => { :maximum => 4 }
  validates :pnc1, :length => { :maximum => 20 }
  validates :pnc2, :length => { :maximum => 20 }
  validates :pnc3, :length => { :maximum => 64 }
  validates :length, :numericality => { :greater_than => 0 }, :allow_nil => true
  validates :width,  :numericality => { :greater_than => 0 }, :allow_nil => true
  validates :height, :numericality => { :greater_than => 0 }, :allow_nil => true
  validates :volume, :numericality => { :greater_than => 0 }, :allow_nil => true
  validates :weight, :numericality => { :greater_than => 0 }, :allow_nil => true
  validates :per_package, :numericality => { :greater_than => 0 }, :allow_nil => true

  normalize_attributes :part_number, :with => :upcase
  normalize_attributes :product_code, :with => :upcase

  module Scopes
    def by_part_number(value)
      return scoped if value.blank?
      where{part_number =~ "%#{value}%"}
    end

    def by_name(value)
      return scoped if value.blank?
      where{name =~ "%#{value}%"}
    end
  end

  extend Scopes

  def to_breadcrumb
    "#{part_number} - #{name}"
  end

  def masked_part_number
    part_number.mask mask
  end

  def unregistered?
    profile.nil?
  end

  def registered?
    !unregistered?
  end

  def register
    create_profile! if unregistered?
  end

  def main_storage_slot(stock = nil)
    stock ||= Settings.default_stock

    if stock.blank?
      fail LogicError::Base, errors.generate_message(:base, :stock_required_to_find_address)
    end

    StorageSlot.with_product(id).at_stock(stock.id).main.first
  end

  def storage_slots_at_stock(stock)
    sid = stock.is_a?(Stock) ? stock.id : stock
    slots = StorageSlot
      .at_stock(sid)
      .with_product(id)
      .includes([:stock_address, :stock])
      .all
  end

  def assign_price_group
    return if unregistered?
    product_code = ProductCode.find_by_code(product_code)
    if product_code.present?
      profile.price_group_id = product_code.price_group_id
      save!
    end
  end

  def new_substitutes
    ProductSubstitution.new_substitutes_for(self)
  end

  def old_substitutes
    ProductSubstitution.old_substitutes_for(self)
  end

  def substitutes
    ProductSubstitution.substitutes_for(self)
  end

  def substitutions
    ProductSubstitution.for_product(self)
  end

  def name_with_part_number
    @name_with_part_number ||= "#{name} - #{part_number}"
  end

  def received_by!(incoming_note_item)
    incoming_note_item.former_incoming_note_item = actual_incoming_note_item
    incoming_note_item.former_quantity = quantity_to_sell
    profile.actual_incoming_note_item = incoming_note_item
    profile.save!
  end

  private

  def quantity
    @quantity ||= ProductQuantity.new(self)
  end
end

product_substitution.rb

# encoding: utf-8
# == Schema Information
#
# Table name: product_substitutions
#
#  id                     :integer          not null, primary key
#  old_product_id         :integer
#  new_product_id         :integer
#  forward_code           :string(255)
#  backward_code          :string(255)
#  created_in_domain_id   :integer
#  created_in_division_id :integer
#  created_by_user_id     :integer
#  created_by_employee_id :integer
#  created_at             :datetime         not null
#  updated_at             :datetime         not null
#

class ProductSubstitution < ActiveRecord::Base
  belongs_to :old_product, :class_name => Product.name
  belongs_to :new_product, :class_name => Product.name

  has_enumeration_for :forward_code, :with => ForwardSubstitutionCode, :required => true
  has_enumeration_for :backward_code, :with => BackwardSubstitutionCode, :required => true

  validates :old_product, :presence => true
  validates :new_product, :presence => true

  scope :for_product, lambda { |id| where{(old_product_id == my{id}) || (new_product_id == my{id})} }

  class << self
    # @return [Array<Product>] old substitutes for the specified product
    def old_substitutes_for(product)
      substitutes = []
      while substitution = self.find_by_new_product_id(product.id) do
        product = substitution.old_product
        substitutes.push(product)
      end
      substitutes.reverse
    end

    # @return [Array<Product>] new substitutes for the specified product
    def new_substitutes_for(product)
      substitutes = []
      while substitution = self.find_by_old_product_id(product.id) do
        product = substitution.new_product
        substitutes.push(product)
      end
      substitutes
    end

    # @return [Array<Product>] all substitutes for the specified product
    def substitutes_for(product)
      old_substitutes_for(product) + new_substitutes_for(product)
    end
  end

  def code
    if forward_code.present? && backward_code.present?
      "#{forward_code_humanize} - #{backward_code_humanize}"
    end
  end
end

product_substitution_cpd_uploader.rb

class ProductSubstitutionCpdUploader < CarrierWave::Uploader::Base
  attr_reader :warnings

  process :process_substitutions

  # Overrides the directory where uploaded cache files on form resubmit.
  def cache_dir
    'system/tmp'
  end

  # Overrides the directory where uploaded files will be stored.
  def store_dir
    File.join(cache_dir, CarrierWave.generate_cache_id)
  end

  private

  def process_substitutions
    @warnings = []

    ActiveRecord::Base.transaction do
      parse_file do
        warning = []
        warning << I18n.t('messages.substitution_should_be_processed_manually') if @forward_code != '01'
        warning << I18n.t('messages.cant_find_product_by_part_number', :part_number => @old_part_number) if not @old_product
        warning << I18n.t('messages.cant_find_product_by_part_number', :part_number => @new_part_number) if not @new_product
        if warning.empty?
          register! if should_be_registered?
          destroy! if should_be_destroyed?
        else
          @warnings << warning
        end
      end
    end
    @warnings = @warnings.flatten.uniq
  end

  def parse_file
    rows = File.open(path, "r:#{Encoding::CP1251}").read.split($/)[2..-1]
    rows.map do |row|
      cells = row.split(';')

      @registered_at   = cells[0]
      @destroyed_at    = cells[1]
      @old_part_number = normalize_part_number(cells[2])
      @new_part_number = normalize_part_number(cells[3])
      @old_product     = find_product(@old_part_number)
      @new_product     = find_product(@new_part_number)
      @forward_code    = cells[4]
      @backward_code   = cells[5]

      yield
    end
  end

  def should_be_registered?
    @destroyed_at.blank? && @registered_at.present? && @old_product.registered? && @new_product.unregistered?
  end

  def should_be_destroyed?
    @destroyed_at.present? && @registered_at.blank? && @old_product.registered? && @new_product.registered?
  end

  def register!
    @new_product.register
    substitution_scope.first_or_create.update_attributes(:forward_code => @forward_code, :backward_code => @backward_code)
  end

  def destroy!
    substitution_scope.destroy_all
  end

  def substitution_scope
    ProductSubstitution.where(:old_product_id => @old_product.id, :new_product_id => @new_product.id)
  end

  def normalize_part_number(part_number)
    part_number and part_number[1..-1]
  end

  def find_product(part_number)
    Product.where(:part_number => part_number).first
  end
end

product_substitution_cpd_uploader_spec.rb

require 'spec_helper'

describe 'import product substitutions from CPD' do
  subject(:uploader) { ProductSubstitutionCpdUploader.new }

  before :all do
    DatabaseCleaner.clean_with(:truncation, :only => %w[products product_substitutions])
  end

  context 'when old code registred, new code uregistred and filled registration date' do
    before :each do
      create :registered_product, :part_number => 'SU00300319'
      @new_product = create :product, :part_number => '178010D011'
    end

    let(:file_path) { File.open(Rails.root.join('spec','files','product_substitution','CPD_SNAP_CREATE_NEW_SUBS.csv')) }

    it 'should register new product' do
      subject.store!(file_path)
      @new_product.should be_registered
    end

    it 'should create new product substitution' do
      subject.store!(file_path)
      ProductSubstitution.first.should_not be_nil
    end
  end

  context "when old and new code registred and filled deletion date " do
    before :each do
      old_product = create :registered_product, :part_number => 'SU00300319'
      new_product = create :registered_product, :part_number => '178010D011'
      @product_substitution_id = (create :product_substitution,
                                         :old_product => old_product,
                                         :new_product => new_product,
                                         :forward_code => '01',
                                         :backward_code => 'NN').id
    end

    let(:file_path) { File.open(Rails.root.join('spec','files','product_substitution','CPD_SNAP_DELETE_SUBS.csv')) }
    it 'should delete substitution' do
      subject.store!(file_path)
      expect { ProductSubstitution.find(@product_substitution_id) }.to raise_error(ActiveRecord::RecordNotFound)
    end
  end

  context "when forward_code do not equal '01'" do
    before :each do
      create :registered_product, :part_number => 'SU00300319'
      create :product, :part_number => '178010D011'
    end
    let(:file_path) { File.open(Rails.root.join('spec','files','product_substitution','CPD_SNAP_MESSAGES_SUBS.csv')) }

    it "error message should be 'substitution_should_be_processed_manually'" do
      subject.store!(file_path)
      subject.warnings.should == [I18n.t('messages.substitution_should_be_processed_manually')]
    end
  end

  context "when cant find product by part_number" do
    let(:file_path) { File.open(Rails.root.join('spec','files','product_substitution','CPD_SNAP_CREATE_NEW_SUBS.csv')) }

    it "should be messages 'cant_find_product_by_part_number" do
      subject.store!(file_path)
      subject.warnings.should == [I18n.t('messages.cant_find_product_by_part_number', :part_number => 'SU00300319'),
                                  I18n.t('messages.cant_find_product_by_part_number', :part_number => '178010D011')]
    end
  end
end

product_substitutions.js.coffee

@AutoHub.namespace 'AutoHub.ProductSubstitutions', () ->
  class @List extends AutoHub.Widget
    events:
      'click #upload': (e) ->
        e.preventDefault()
    load: () ->
      @importButton = @container.find('#import')
      @fileUpload = $('<input id="first_upload" name="csv_file" type="file" class="hidden-upload" />').insertAfter(@importButton)
      @fileUpload.width(@importButton.width()).height(@importButton.height()).css('margin-left': - @importButton.width(), 'opacity': 0)
      @fileUpload.fileupload
        url: @importButton.attr('href')
        dataType: 'script'
        dropZone: @container
        limitMultiFileUploads: 1
        formData:
          'authenticity_token': $('meta[name="csrf-token"]').attr('content')
        xhrFields:
          withCredentials: true

product_substitutions_controller.rb

class ProductSubstitutionsController < CrudResourcesController
  respond_to :js
  custom_actions :collection => [:nested, :import]

  def nested
    @product_substitutions = Product.find(params[:id]).substitutions
    nested!
  end

  def import
    uploader = ProductSubstitutionCpdUploader.new
    uploader.store!(params[:csv_file])
    if uploader.warnings.present?
      set_flash(:alert, uploader.warnings.join('<br>'))
    end

    render :action => 'index'
  end
end