Class: Query::StoreItemQuery

Inherits:
BaseQuery
  • Object
show all
Defined in:
app/services/query/store_item_query.rb

Constant Summary collapse

QTY_FUTURE_DELIVERY_SUBQUERY = 
Arel.sql(%q{
  select sum(li.quantity)
  from line_items li
    inner join orders o on li.resource_id = o.id and li.resource_type = 'Order'
    inner join deliveries d on d.order_id = o.id
  where
    li.item_id = items.id
    and o.state = 'awaiting_deliveries'
    and d.state = 'future_release'
}).freeze
QTY_BACKORDERED_SUBQUERY = 
Arel.sql(%q{
  select sum(li.quantity)
  from line_items li
    inner join orders o on li.resource_id = o.id and li.resource_type = 'Order'
  where
    li.item_id = items.id
    and o.state = 'crm_back_order'
}).freeze
BACKORDERED_CONDITION = 
Arel.sql(%Q{
store_items.qty_available = 0
OR
  (#{QTY_BACKORDERED_SUBQUERY}) > store_items.qty_available
}).freeze
PROMISED_DATE_QUERY = 
Arel.sql(%q{
  select min(pos.promised_delivery_date)
    from purchase_orders po
    INNER JOIN purchase_order_items poi ON poi.purchase_order_id = po.id
    INNER JOIN shipment_items ship_it on ship_it.purchase_order_item_id = poi.id
    INNER JOIN purchase_order_shipments pos ON pos.id = ship_it.purchase_order_shipment_id
    WHERE poi.item_id = items.id AND poi.state IN ('not_receipted','partially_receipted')
      AND po.state IN ('partially_receipted', 'pending_fulfillment', 'shipped')
}).freeze
ORDER_CONDITION_CHECK = 
Arel.sql("(#{QTY_BACKORDERED_SUBQUERY}) > 0 OR (#{QTY_FUTURE_DELIVERY_SUBQUERY}) > 0").freeze

Instance Method Summary collapse

Constructor Details

#initialize(relation = nil) ⇒ StoreItemQuery

Returns a new instance of StoreItemQuery.



41
42
43
 
# File 'app/services/query/store_item_query.rb', line 41

def initialize(relation = nil)
  super(relation || StoreItem.all)
end

Instance Method Details

#out_of_stock_items_on_backorders(store_id) ⇒ Array<Hash>

Returns a list of out of stock items that have backorders.

The list contains the name, SKU, ID, quantity backordered, and earliest promised date
for each out of stock item that has at least 1 backorder.

Parameters:

  • store_id (Integer) —

    The ID of the store to get items for.

Returns:

  • (Array<Hash>) —

    The list of out of stock backordered items. Each hash contains
    :item_name, :item_sku, :item_id, :qty_backordered, and :earliest_promised_date.



73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
 
# File 'app/services/query/store_item_query.rb', line 73

def out_of_stock_items_on_backorders(store_id)

  @relation.joins(:item).merge(Item.goods)
           .where(store_id: store_id, location: 'AVAILABLE')
           .where(ORDER_CONDITION_CHECK)
           .where(BACKORDERED_CONDITION)
           .select("items.name as item_name")
           .select("items.sku as item_sku")
           .select("items.id as item_id")
           .select("store_items.*")
           .select("(#{QTY_BACKORDERED_SUBQUERY}) as qty_backordered")
           .select("(#{QTY_FUTURE_DELIVERY_SUBQUERY}) as qty_in_future_release")
           .select("(#{PROMISED_DATE_QUERY}) as earliest_promised_date")
           .order(Arel.sql("(#{PROMISED_DATE_QUERY})"),Item[:sku])

end

#out_of_stock_items_on_backorders_count(store_id) ⇒ Object

Returns the number of out of stock items that have backorders for the given store.



91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'app/services/query/store_item_query.rb', line 91

def out_of_stock_items_on_backorders_count(store_id)
  sql = %Q{
    WITH combined_report AS (
      SELECT i.sku, sum(li.quantity) - sum(si.qty_available) as shortage
      FROM line_items li
      INNER JOIN orders ord on li.resource_id = ord.id and li.resource_type = 'Order' and ord.state = 'crm_back_order'
      INNER JOIN catalog_items ci on ci.id = li.catalog_item_id
      INNER JOIN store_items si on si.id = ci.store_item_id and si.store_id = #{store_id} and si.location = 'AVAILABLE'
      INNER JOIN items i on i.id = si.item_id
      GROUP BY i.sku
      HAVING sum(li.quantity) > sum(si.qty_available)

      UNION

      SELECT i.sku, sum(li.quantity) - sum(si.qty_available) as shortage
      FROM line_items li
      INNER JOIN orders ord on li.resource_id = ord.id and li.resource_type = 'Order' and ord.state = 'awaiting_deliveries'
      INNER JOIN deliveries del on del.id = li.delivery_id and del.state = 'future_release'
      INNER JOIN catalog_items ci on ci.id = li.catalog_item_id
      INNER JOIN store_items si on si.id = ci.store_item_id and si.store_id = #{store_id} and si.location = 'AVAILABLE'
      INNER JOIN items i on i.id = si.item_id
      GROUP BY i.sku
      HAVING sum(li.quantity) > sum(si.qty_available)
      )
    select sku, sum(shortage) as shortage FROM combined_report GROUP BY sku
  }
  StoreItem.connection.execute(sql.sql_safe).ntuples
end

#restock_items(store_id = nil) ⇒ Array<Hash>

Returns a list of restock items for the given store.

The list contains the name, SKU, quantity on hand, and ID of each item that needs
restocking. An item needs restocking if it has 1 or more units on hand and is in the
RESTOCK location for the given store.

Parameters:

  • store_id (Integer) (defaults to: nil) —

    The ID of the store to get restock items for.

Returns:

  • (Array<Hash>) —

    The list of restock items. Each hash contains :item_name,
    :item_sku, :qty_on_hand, and :id.



61
62
63
 
# File 'app/services/query/store_item_query.rb', line 61

def restock_items(store_id = nil)
  restock_items_query(store_id).pluck_to_hash("items.name as item_name", "items.sku as item_sku", "items.id as item_id", :qty_on_hand, :id, :store_id)
end

#restock_items_query(store_id = nil) ⇒ Object 



45
46
47
48
49
50
 
# File 'app/services/query/store_item_query.rb', line 45

def restock_items_query(store_id = nil)
  rel = @relation.joins(:item).merge(Item.goods).
  where(location: 'RESTOCK', qty_on_hand: 1..)
  rel = rel.where(store_id: store_id) if store_id
  rel
end