Class: RawgentoDB::Query

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

Class Method Summary collapse

Class Method Details

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



120
121
122
123
124
125
126
127
128
129
130
131
 
# File 'lib/rawgento_db/query.rb', line 120

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 



110
111
112
113
114
115
116
117
118
 
# File 'lib/rawgento_db/query.rb', line 110

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

.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 



101
102
103
104
105
106
107
108
 
# File 'lib/rawgento_db/query.rb', line 101

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



66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/rawgento_db/query.rb', line 66

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 



92
93
94
95
96
97
98
99
 
# File 'lib/rawgento_db/query.rb', line 92

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 



79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/rawgento_db/query.rb', line 79

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