class RawgentoDB::Query
Public Class Methods
attribute_int(attribute_id, settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 196 def self.attribute_int attribute_id, settings=RawgentoDB.settings result = client(settings).query(" SELECT entity_id, value FROM catalog_product_entity_int WHERE attribute_id=#{attribute_id};") result.map do |r| [r['entity_id'], r['value']] end end
attribute_option(attribute_id, settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 206 def self.attribute_option attribute_id, settings=RawgentoDB.settings # Join result = client(settings).query(" SELECT optchoice.entity_id, optval.value FROM eav_attribute_option_value as optval, catalog_product_entity_int as optchoice WHERE optchoice.attribute_id=#{attribute_id} AND optval.option_id=optchoice.value;") result.map do |r| [r['entity_id'], r['value']] end end
attribute_varchar(attribute_id, settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 177 def self.attribute_varchar attribute_id, settings=RawgentoDB.settings result = client(settings).query(" SELECT entity_id, value FROM catalog_product_entity_varchar WHERE attribute_id=#{attribute_id};") result.map do |r| [r['entity_id'], r['value']] end end
client(settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 8 def self.client settings=RawgentoDB.settings # Pick up a memoized settings? Mysql2::Client.new settings end
first_sales(product_ids, settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 152 def self.first_sales product_ids, settings=RawgentoDB.settings query = "SELECT MIN(period), product_id "\ "FROM sales_bestsellers_aggregated_daily "\ "WHERE product_id in (%s) "\ "GROUP BY product_id" % [[*product_ids].join(",")] result = client(settings).query(query) result.map do |r| [r['product_id'], r['MIN(period)']] end.to_h end
notify_stock_qty_for(settings=RawgentoDB.settings, product_ids)
click to toggle source
# File lib/rawgento_db/query.rb, line 50 def self.notify_stock_qty_for settings=RawgentoDB.settings, product_ids results = client(settings).query( "SELECT product_id, notify_stock_qty "\ "FROM cataloginventory_stock_item "\ "WHERE product_id IN (#{product_ids.join(', ')});") results.map do |row| [row['product_id'], row['notify_stock_qty']] end end
num_sales_since(day, product_ids, settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 163 def self.num_sales_since day, product_ids, settings=RawgentoDB.settings query = "SELECT SUM(qty_ordered), product_id "\ "FROM sales_bestsellers_aggregated_daily "\ "WHERE product_id in (%s) "\ " AND period >= '%s' "\ " AND store_id = 1 "\ "GROUP BY product_id" % [[*product_ids].join(","), day.strftime] result = client(settings).query(query) result.map do |r| [r['product_id'], ProductQty.new(r['product_id'], r['SUM(qty_ordered)'].to_i)] end.to_h end
product_names(product_ids=nil, settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 187 def self.product_names product_ids=nil, settings=RawgentoDB.settings where = product_ids.nil? ? "" : " WHERE entity_id IN (#{product_ids.join(', ')})" query = "SELECT entity_id, name FROM catalog_product_flat_1 #{where};" result = client(settings).query(query) result.map do |r| [r['entity_id'], r['name']] end.to_h end
products(settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 13 def self.products settings=RawgentoDB.settings # Unfortunately, name is an own attribute in different table. result = client(settings).query('SELECT entity_id '\ 'FROM catalog_product_entity') result.map do |r| Product.new r["entity_id"], r[""] end end
sales_daily(product_id, settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 143 def self.sales_daily product_id, settings=RawgentoDB.settings result = client(settings).query('SELECT * '\ 'FROM sales_bestsellers_aggregated_daily '\ ' WHERE product_id = %d ORDER BY period DESC' % product_id) result.map do |r| [r['period'], "%1.0f" % r['qty_ordered']] end end
sales_daily_between(product_id, from_date, to_date, settings=RawgentoDB.settings)
click to toggle source
Newer version might require query via entity_id array('aggregation' => $collection->getResource()->getTable('sales/bestsellers_aggregated_monthly')),
"e.entity_id = aggregation.product_id AND aggregation.store_id={$storeId} AND aggregation.period BETWEEN '{$fromDate}' AND '{$toDate}'", array('SUM(aggregation.qty_ordered) AS sold_quantity')
# File lib/rawgento_db/query.rb, line 106 def self.sales_daily_between product_id, from_date, to_date, settings=RawgentoDB.settings min_date, max_date = [from_date, to_date].minmax query = 'SELECT * '\ 'FROM sales_bestsellers_aggregated_daily '\ 'WHERE product_id = %d AND '\ 'store_id = 1 AND '\ 'period >= \'%s\' AND period <= \'%s\' '\ 'ORDER BY PERIOD DESC' % [product_id, min_date.strftime, max_date.strftime] result = client(settings).query(query) result.map do |r| [r['period'], "%1.0f" % r['qty_ordered']] end end
sales_monthly(product_id, settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 134 def self.sales_monthly product_id, settings=RawgentoDB.settings result = client(settings).query('SELECT * '\ 'FROM sales_bestsellers_aggregated_monthly '\ ' WHERE product_id = %d ORDER BY period DESC' % product_id) result.map do |r| [r['period'], "%1.0f" % r['qty_ordered']] end end
sales_monthly_between(product_id, from_date, to_date, settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 120 def self.sales_monthly_between product_id, from_date, to_date, settings=RawgentoDB.settings min_date, max_date = [from_date, to_date].minmax query = 'SELECT DISTINCT * '\ 'FROM sales_bestsellers_aggregated_monthly '\ 'WHERE product_id = %d AND '\ 'store_id = 1 AND '\ 'period >= \'%s\' AND period <= \'%s\' '\ 'ORDER BY period DESC' % [product_id, min_date.strftime, max_date.strftime] result = client(settings).query(query) result.map do |r| [r['period'], "%1.0f" % r['qty_ordered']] end#.uniq end
set_available_on_stock(product_id, settings=RawgentoDB.settings)
click to toggle source
One-way ticket: Do set available if qty > 0 (but not unavailable if <= 0).
# File lib/rawgento_db/query.rb, line 81 def self.set_available_on_stock product_id, settings=RawgentoDB.settings result = client(settings).query( "SELECT is_in_stock FROM cataloginventory_stock_item "\ "WHERE product_id = %d AND is_in_stock = 0 AND qty > 0" % product_id) if result && result.size > 0 #&& result[0] == 0 result = client(settings).query( "UPDATE cataloginventory_stock_item SET is_in_stock = 1 "\ "WHERE product_id = %d" % product_id) result else "unclear what happened" end end
stock(settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 22 def self.stock settings=RawgentoDB.settings result = client(settings).query('SELECT product_id, qty '\ 'FROM cataloginventory_stock_item ') result.map do |r| ProductQty.new r["product_id"], r["qty"].to_i end end
stock_for(product_ids, settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 30 def self.stock_for product_ids, settings=RawgentoDB.settings result = client(settings).query('SELECT product_id, qty '\ 'FROM cataloginventory_stock_item '\ "WHERE product_id IN (#{[*product_ids].join(', ')})") result.map do |r| ProductQty.new r["product_id"], r["qty"].to_i end end
understocked(settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 39 def self.understocked settings=RawgentoDB.settings results = client(settings).query( "SELECT product_id, qty, notify_stock_qty "\ "FROM cataloginventory_stock_item "\ "WHERE notify_stock_qty > qty OR notify_stock_qty IS NULL;") results.map do |row| [row['product_id'], row['name'], row['notify_stock_qty'], row['qty']] end end
update_stock(product_id, stock_addition, settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 61 def self.update_stock product_id, stock_addition, settings=RawgentoDB.settings if stock_addition.to_i == 0 return end results = client(settings).query( "UPDATE cataloginventory_stock_item SET qty = qty + %f "\ "WHERE product_id = %d" % [stock_addition, product_id]) end
wrongly_not_in_stock(settings=RawgentoDB.settings)
click to toggle source
# File lib/rawgento_db/query.rb, line 95 def self.wrongly_not_in_stock settings=RawgentoDB.settings query = "SELECT product_id FROM rlg15.cataloginventory_stock_item WHERE qty > 0 AND is_in_stock = 0;" results = client(settings).query query results.map{|r| r['product_id']} end