category_sku_mapping

sheet_parse_update.rb

require 'roo'
sheet    = Roo::Spreadsheet.open("../../../../../tmp/missing_mapping.xlsx", clean: true)
headers  = sheet.row(1)
mappings = []
sheet.each(sku: headers[0], category: headers[1], sku_type: headers[2]) do |hash|
 mappings << hash
end
mappings.shift
invalid = []
def update_missing_label_category(mappings, invalid = [])
  mappings.each do |mapping|
    begin
      Variant.connection.select_sp("CALL update_category_sku_type('#{mapping[:sku]}', '#{mapping[:category]}', '#{mapping[:sku_type]}')")
    rescue => e
      invalid << [mapping, e.message]
    end
  end
end

sp_update_category_sku.sql

DROP PROCEDURE IF EXISTS update_category_sku_type;

CREATE PROCEDURE update_category_sku_type(IN skustr MEDIUMTEXT, IN category MEDIUMTEXT, IN skutype MEDIUMTEXT)

BEGIN
  SET @skuparam = QUOTE(CONCAT(skustr,"%"));
  SET @categoryparam = QUOTE(category);
  SET @skutypeparam = QUOTE(skutype);

  SET @sku_update_statement = CONCAT('UPDATE variants v JOIN products p ON v.product_id = p.id SET v.sku_type = ',@skutypeparam,', p.category_id = ( SELECT id FROM propertymasters where value = ',@categoryparam,' and ptype = \'category\' ) WHERE sku LIKE ',@skuparam,';');

  PREPARE sku_update_statement FROM @sku_update_statement;

  EXECUTE sku_update_statement;
  DEALLOCATE PREPARE sku_update_statement;
END;