Update wrong prices in specified collection with the right ones.

updatePreOrdrePrices.rb

sql = 'CREATE TABLE update_prices
AS (
SELECT temp_art AS article_id, price_value 
  FROM (
    SELECT *, id AS temp_art 
    FROM articles
    WHERE id IN (
      SELECT DISTINCT Z1.article_id 
      FROM ordering_pre_order_items AS Z1
      WHERE Z1.pre_order_id IN (
        SELECT id 
        FROM ordering_pre_orders 
        WHERE customer_id IN (
          SELECT id 
          FROM customers 
          WHERE currency_name = "EUR"
        ) 
        AND customer_type <> "ShopCustomer" 
        AND collection_id = 57
      )
    )
  ) AS A
  JOIN prices AS P 
  ON A.style_id = P.style_id
  WHERE type = "WholesalePrice" 
  AND price_currency = "EUR" 
  AND rank = 1
);'

ActiveRecord::Base.connection.execute(sql)

sql = "SELECT * FROM update_prices;"
@prices = ActiveRecord::Base.connection.execute(sql)


sql = 'CREATE TABLE update_items
  AS (
  SELECT DISTINCT Z2.article_id, Z2.price_value
  FROM ordering_pre_order_items AS Z2
  WHERE Z2.pre_order_id IN (
    SELECT id 
    FROM ordering_pre_orders 
    WHERE customer_id IN (
      SELECT id 
      FROM customers 
      WHERE currency_name = "EUR"
    ) 
    AND customer_type <> "ShopCustomer" 
    AND collection_id = 57
  )
);'

ActiveRecord::Base.connection.execute(sql)

sql = "SELECT * FROM update_items;"
@items = ActiveRecord::Base.connection.execute(sql)


def update
  @items.each do |i|
    items = Ordering::PreOrderItem.where(article_id:i.first, price_currency:"EUR")
    items.each do |item|
      @prices.each do |p|
        if p.first == item.article_id
          item.price_value = p.last
          item.save!
        end
      end
    end
  end
end

update

sql = "DROP TABLE update_items;"
ActiveRecord::Base.connection.execute(sql)
sql = "DROP TABLE update_prices;"
ActiveRecord::Base.connection.execute(sql)