Class: RawgentoDB::Query

Inherits:
Object
  • Object
show all
Defined in:
lib/rawgento_db/query.rb

Class Method Summary collapse

Class Method Details

.attribute_int(attribute_id, settings = RawgentoDB.settings) ⇒ Object 



159
160
161
162
163
164
165
166
167
 
# File 'lib/rawgento_db/query.rb', line 159

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) ⇒ Object 



169
170
171
172
173
174
175
176
177
178
179
180
 
# File 'lib/rawgento_db/query.rb', line 169

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) ⇒ Object 



140
141
142
143
144
145
146
147
148
 
# File 'lib/rawgento_db/query.rb', line 140

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) ⇒ Object 



8
9
10
11
 
# 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) ⇒ Object 



116
117
118
119
120
121
122
123
124
125
 
# File 'lib/rawgento_db/query.rb', line 116

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

.num_sales_since(day, product_ids, settings = RawgentoDB.settings) ⇒ Object 



127
128
129
130
131
132
133
134
135
136
137
138
 
# File 'lib/rawgento_db/query.rb', line 127

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' "\
          "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) ⇒ Object 



150
151
152
153
154
155
156
157
 
# File 'lib/rawgento_db/query.rb', line 150

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) ⇒ Object 



13
14
15
16
17
18
19
20
 
# 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) ⇒ Object 



107
108
109
110
111
112
113
114
 
# File 'lib/rawgento_db/query.rb', line 107

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) ⇒ Object

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')



72
73
74
75
76
77
78
79
80
81
82
83
 
# File 'lib/rawgento_db/query.rb', line 72

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 '\
          '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) ⇒ Object 



98
99
100
101
102
103
104
105
 
# File 'lib/rawgento_db/query.rb', line 98

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) ⇒ Object 



85
86
87
88
89
90
91
92
93
94
95
96
 
# File 'lib/rawgento_db/query.rb', line 85

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 '\
          '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) ⇒ Object 



47
48
49
50
51
52
53
54
55
56
57
58
59
 
# File 'lib/rawgento_db/query.rb', line 47

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.length && 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) ⇒ Object 



22
23
24
25
26
27
28
 
# 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

.understocked(settings = RawgentoDB.settings) ⇒ Object 



30
31
32
33
34
35
36
37
38
39
 
# File 'lib/rawgento_db/query.rb', line 30

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;")
  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) ⇒ Object 



41
42
43
44
45
 
# File 'lib/rawgento_db/query.rb', line 41

def self.update_stock product_id, stock_addition, settings=RawgentoDB.settings
  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) ⇒ Object 



61
62
63
64
65
 
# File 'lib/rawgento_db/query.rb', line 61

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