导入线下excell业务数据按权重匹配线上数据

业务场景：（系统间还没有接口对调，订单号暂时需要线下处理）
线上订单需要线下第三方票据核对之后才能抽取明细对账开票。
根据第三方提供的票据匹配上双方系统的订单号。

class Report::Importcsv

    # 上传csv文件load数据到数据库临时表
	def self.import_csv(file_root,table_name='mapping_datas')
	   # xlsx 保存为csv
	   # 上传到路径：/var/lib/mysql-files/
	   new_table_name = "cope_#{table_name}_#{Time.now.to_i}"
       create_table_sql = cope_table_sql(new_table_name,table_name)
	   			
	  # 创建表数据	
	  ActiveRecord::Base.connection.execute(create_table_sql)	

	  # load 写入csv数据
      @sql = %Q(LOAD DATA INFILE '#{file_root}' INTO TABLE #{table_name} FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;)				
	  ActiveRecord::Base.connection.execute(@sql)
	end

	def self.cope_table_sql(new_table_name,table_name)
       "create table #{new_table_name}
        select * from #{table_name} where 1=2;"
	end


	################################################
	## 比对mapping
	################################################

	require 'matrix'

    def self.mapping_data_test
      # 等待匹配的数据库订单数据
      sql  = "select * from mapping_datas"
      base_a = ActiveRecord::Base.connection.execute(sql)
      compale_a = base_a.map{|_|  _[1..-1].map{|a| b = (a ? a.to_f : 'null') ; b } } 
      aaa = Matrix.columns(compale_a).to_a

      # 假设excell导入的临时表数据
      excell_sql  = "select * from cope_mapping_datas_11232131"
      base_b = ActiveRecord::Base.connection.execute(excell_sql)
      compale_b = base_b.map{|_|  _[1..-1].map{|a| b = (a ? a.to_f : 'null') ; b } } 
      bbb = Matrix.columns(compale_b).to_a

      # 完全一致的
      #ccc = compale_a & compale_b

      ddd = []
      compale_a.each_with_index do |a,i|
        eee = []
        aaa.each_with_index do |_,j|
          bbb[j].each_with_index do |b,k|	
	          if a[j] == b
	            eee << k 
	          end
          end
        end
        re = eee.flatten

        # 获取最大匹配指标的值
        h = Hash.new(0)
        re.each { | v | h.store(v, h[v]+1) }

        v = h.values.max
        ind = h.values.index(v)
        ddd << [{index:i,  mapping:h.keys[ind.to_i], size:v}]
      end

      return ddd

    end

end

:index => 原数据库id，
:mapping => excell入库id，
:size => 匹配指标（users.mobile, od.art_no, od.cas, od.package_count）命中个数

[
 [{:index=>70, :mapping=>69, :size=>4}],
 [{:index=>71, :mapping=>70, :size=>4}],
 [{:index=>72, :mapping=>71, :size=>4}],
 [{:index=>73, :mapping=>72, :size=>4}],
 [{:index=>74, :mapping=>73, :size=>4}],
 [{:index=>75, :mapping=>74, :size=>4}],
 [{:index=>76, :mapping=>75, :size=>4}],
 [{:index=>77, :mapping=>76, :size=>4}],
 [{:index=>78, :mapping=>0, :size=>2}],
 [{:index=>79, :mapping=>69, :size=>3}],
 [{:index=>80, :mapping=>0, :size=>2}],
 [{:index=>81, :mapping=>24, :size=>2}],
 [{:index=>82, :mapping=>69, :size=>3}],
 [{:index=>83, :mapping=>75, :size=>2}],
 [{:index=>84, :mapping=>75, :size=>2}],
 [{:index=>85, :mapping=>68, :size=>2}],
 [{:index=>86, :mapping=>0, :size=>2}],
 [{:index=>87, :mapping=>42, :size=>3}]
]

全部指标命中的，默认为相等，部分指标命中的为待筛查数据