Class: ItemDemandForecast

Inherits:
ApplicationRecord show all
Defined in:
app/models/item_demand_forecast.rb

Overview

== Schema Information

Table name: item_demand_forecasts
Database name: primary

id :integer not null, primary key
on_order_ca :integer
on_order_combined :integer
on_order_us :integer
po_received_ca :integer
po_received_combined :integer
po_received_us :integer
quantity_16_ca :integer
quantity_16_combined :integer
quantity_16_us :integer
quantity_52_ca :integer
quantity_52_combined :integer
quantity_52_us :integer
quantity_amazon_ca :integer
quantity_amazon_combined :integer
quantity_amazon_us :integer
quantity_average_ca :integer
quantity_average_combined :integer
quantity_average_normalized_ca :integer
quantity_average_normalized_combined :integer
quantity_average_normalized_us :integer
quantity_average_us :integer
quantity_costco_ca :integer
quantity_costco_combined :integer
quantity_costco_us :integer
quantity_future_release_ca :integer
quantity_future_release_combined :integer
quantity_future_release_us :integer
quantity_home_depot_ca :integer
quantity_home_depot_combined :integer
quantity_home_depot_us :integer
quantity_misc_ca :integer
quantity_misc_combined :integer
quantity_misc_us :integer
quantity_quote_ca :integer
quantity_quote_combined :integer
quantity_quote_us :integer
starting_stock_ca :integer
starting_stock_combined :integer
starting_stock_us :integer
week_start_date :date
created_at :datetime
updated_at :datetime
item_id :integer

Indexes

index_item_demand_forecasts_on_item_id_and_week_start_date (item_id,week_start_date)
index_item_demand_forecasts_on_week_start_date (week_start_date)

Belongs to collapse

Class Method Summary collapse

Methods inherited from ApplicationRecord

ransackable_associations, ransackable_attributes, ransackable_scopes, ransortable_attributes, #to_relation

Methods included from Models::EventPublishable

#publish_event

Class Method Details

.last_refreshed_at ⇒ Object 



60
61
62
 
# File 'app/models/item_demand_forecast.rb', line 60

def self.last_refreshed_at
  ItemDemandForecast.last&.created_at
end

.populate ⇒ Object 



64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
 
# File 'app/models/item_demand_forecast.rb', line 64

def self.populate
  # 52 weeks ahead
  ItemDemandForecast.delete_all
  ActiveRecord::Base.connection.execute("select setval('item_demand_forecasts_id_seq',1)")
  last_sunday = Date.current.monday - 1.day
  first_monday = last_sunday.weeks_ago(51).monday
  forecast_monday = last_sunday + 1.day
  forecasts = []
  Item.forecastable.where(do_not_replenish: false).find_each do |item|
    # reset back to last monday after looping to next item
    forecast_monday = last_sunday + 1.day

    # additional forecasts
    additional_forecasts = Analytic::DateTimeDimension.select('analytic_date_time_dimensions.week_start_date, category, coalesce(sum(quantity),0) as qty_sum, store_id').joins("left join item_demand_forecast_additions on item_demand_forecast_additions.item_id = #{item.id} and item_demand_forecast_additions.date = analytic_date_time_dimensions.date").where("analytic_date_time_dimensions.date between :start_date and :end_date and category is not null", start_date: forecast_monday, end_date: forecast_monday + 24.weeks).group("analytic_date_time_dimensions.week_start_date, category, store_id").order("analytic_date_time_dimensions.week_start_date")

    # future releases
    future_releases = Analytic::DateTimeDimension.select('analytic_date_time_dimensions.week_start_date, coalesce(sum(quantity_us),0) as qty_us, coalesce(sum(quantity_ca),0) as qty_ca').joins("left join analytic_item_demand_facts on analytic_item_demand_facts.date = analytic_date_time_dimensions.date and analytic_item_demand_facts.item_id in (#{[item.id,item.replacement_for_id].compact.join(',')})").where("analytic_date_time_dimensions.date >= :start_date", start_date: forecast_monday).group("analytic_date_time_dimensions.week_start_date").order("analytic_date_time_dimensions.week_start_date").to_a

    # POs we'll be receiving in next 24 weeks
    #future_po_receipts = Analytic::DateTimeDimension.select('analytic_date_time_dimensions.week_start_date, coalesce(sum(ea_quantity),0) as qty_sum,purchase_orders.store_id').joins("left join purchase_order_items poi on poi.id = si.purchase_order_item_id and poi.item_id = #{item.id} and si.promised_delivery_date = analytic_date_time_dimensions.date left join purchase_orders on purchase_orders.id = poi.purchase_order_id").where("analytic_date_time_dimensions.date between :start_date and #:end_date and poi.state = 'not_receipted'", start_date: forecast_monday, end_date: forecast_monday + 24.weeks).group("analytic_date_time_dimensions.week_start_date,purchase_orders.store_id").order("analytic_date_time_dimensions.week_start_date")

    future_po_receipts = Analytic::DateTimeDimension.select(%{
        analytic_date_time_dimensions.week_start_date,
        coalesce(sum(ea_quantity),0) as qty_sum,
        purchase_orders.store_id
      }).joins('left join purchase_order_shipments pos on pos.promised_delivery_date = analytic_date_time_dimensions.date').
      joins('left join shipment_items si on si.purchase_order_shipment_id = pos.id').
      joins('left join purchase_order_items poi on poi.id = si.purchase_order_item_id').
      joins('left join purchase_orders on purchase_orders.id = si.purchase_order_id').
      where(date: forecast_monday..(forecast_monday + 24.weeks)).
      where('poi.item_id = ?', item.id).
      where("poi.state = 'not_receipted'").
      group("analytic_date_time_dimensions.week_start_date,purchase_orders.store_id").
      order("analytic_date_time_dimensions.week_start_date")

    demand_52 = Analytic::DateTimeDimension.select('analytic_date_time_dimensions.week_start_date, coalesce(sum(quantity_us),0) as qty_us, coalesce(sum(quantity_ca),0) as qty_ca').joins("left join analytic_item_demand_facts on analytic_item_demand_facts.date = analytic_date_time_dimensions.date and analytic_item_demand_facts.item_id in (#{[item.id,item.replacement_for_id].compact.join(',')})").where("analytic_date_time_dimensions.date between :start and :end", start: first_monday, end: last_sunday).group("analytic_date_time_dimensions.week_start_date").order("analytic_date_time_dimensions.week_start_date").to_a
    demand_16 = demand_52.last(16)
    remainder_16_us = 0
    remainder_16_ca = 0
    remainder_52_us = 0
    remainder_52_ca = 0
    remainder_average_us = 0
    remainder_average_ca = 0
    remainder_average_combined = 0
    us_store_item = StoreItem.where(item_id: item.id, location: 'AVAILABLE', store_id: 1).first
    starting_stock_us = us_store_item.store_item_audits.where('entered_at <= ?', forecast_monday).order('entered_at desc').first.new_qty_on_hand rescue us_store_item.nil? ? 0 : us_store_item.qty_on_hand
    ca_store_item = StoreItem.where(item_id: item.id, location: 'AVAILABLE', store_id: 2).first
    starting_stock_ca = ca_store_item.store_item_audits.where('entered_at <= ?', forecast_monday).order('entered_at desc').first.new_qty_on_hand rescue ca_store_item.nil? ? 0 : ca_store_item.qty_on_hand
    starting_stock_combined = starting_stock_us + starting_stock_ca
    52.times do

      # additional forecasts
      manual_forecast_us = {}
      manual_forecast_ca = {}
      ['amazon', 'costco', 'home_depot', 'quote', 'misc'].each do |f_type|
        manual_forecast_us[f_type] = additional_forecasts.select {|af| af.store_id == 1 and af.week_start_date == forecast_monday and af.category == f_type}.sum(&:qty_sum)
        manual_forecast_ca[f_type] = additional_forecasts.select {|af| af.store_id == 2 and af.week_start_date == forecast_monday and af.category == f_type}.sum(&:qty_sum)
      end

      # future releases
      manual_forecast_us['future_release'] = future_releases.select {|fr| fr.week_start_date == forecast_monday}.sum(&:qty_us)
      manual_forecast_ca['future_release'] = future_releases.select {|fr| fr.week_start_date == forecast_monday}.sum(&:qty_ca)

      # on order
      on_order_us = future_po_receipts.select {|poi| poi.store_id == 1 and poi.week_start_date > forecast_monday}.sum(&:qty_sum)
      on_order_ca = future_po_receipts.select {|poi| poi.store_id == 2 and poi.week_start_date > forecast_monday}.sum(&:qty_sum)
      on_order_combined = on_order_us + on_order_ca

      # po receipts
      po_receipt_count_us = future_po_receipts.select {|poi| poi.store_id == 1 and poi.week_start_date == forecast_monday}.sum(&:qty_sum)
      po_receipt_count_ca = future_po_receipts.select {|poi| poi.store_id == 2 and poi.week_start_date == forecast_monday}.sum(&:qty_sum)
      po_receipt_count_combined = po_receipt_count_us + po_receipt_count_ca

      # 16 week forecast (US)
      forecast_16_us = demand_16.sum {|d| d[:qty_us]}.to_f / 16
      forecast_16_us += remainder_16_us
      rounded_forecast_16_us = forecast_16_us.to_i
      remainder_16_us = forecast_16_us - rounded_forecast_16_us
      week_forecast_16_us = rounded_forecast_16_us + manual_forecast_us.values.sum

      # 16 week forecast (CA)
      forecast_16_ca = demand_16.sum {|d| d[:qty_ca]}.to_f / 16
      forecast_16_ca += remainder_16_ca
      rounded_forecast_16_ca = forecast_16_ca.to_i
      remainder_16_ca = forecast_16_ca - rounded_forecast_16_ca
      week_forecast_16_ca = rounded_forecast_16_ca + manual_forecast_ca.values.sum

      # 16 week forecast (combined)
      week_forecast_16_combined = week_forecast_16_us + week_forecast_16_ca
      week_forecast_16_combined_normalized = rounded_forecast_16_us + rounded_forecast_16_ca

      # 52 week forecast (US)
      forecast_52_us = demand_52.sum {|d| d[:qty_us]}.to_f / 52
      forecast_52_us += remainder_52_us
      rounded_forecast_52_us = forecast_52_us.to_i
      remainder_52_us = forecast_52_us - rounded_forecast_52_us
      week_forecast_52_us = rounded_forecast_52_us + manual_forecast_us.values.sum

      # 52 week forecast (CA)
      forecast_52_ca = demand_52.sum {|d| d[:qty_ca]}.to_f / 52
      forecast_52_ca += remainder_52_ca
      rounded_forecast_52_ca = forecast_52_ca.to_i
      remainder_52_ca = forecast_52_ca - rounded_forecast_52_ca
      week_forecast_52_ca = rounded_forecast_52_ca + manual_forecast_ca.values.sum

      # 52 week forecast (combined)
      week_forecast_52_combined = week_forecast_52_us + week_forecast_52_ca
      week_forecast_52_combined_normalized = rounded_forecast_52_us + rounded_forecast_52_ca

      # average forecast (US)
      average_forecast_us = ((rounded_forecast_16_us.to_f + rounded_forecast_52_us.to_f) / 2)
      average_forecast_us += remainder_average_us
      rounded_forecast_average_us = average_forecast_us.to_i
      remainder_average_us = average_forecast_us - rounded_forecast_average_us
      week_forecast_average_us = rounded_forecast_average_us + manual_forecast_us.values.sum

      # average forecast (CA)
      average_forecast_ca = ((rounded_forecast_16_ca.to_f + rounded_forecast_52_ca.to_f) / 2)
      average_forecast_ca += remainder_average_ca
      rounded_forecast_average_ca = average_forecast_ca.to_i
      remainder_average_ca = average_forecast_ca - rounded_forecast_average_ca
      week_forecast_average_ca = rounded_forecast_average_ca + manual_forecast_ca.values.sum

      # average forecast (combined)
      average_forecast_combined = ((week_forecast_16_combined_normalized.to_f + week_forecast_52_combined_normalized.to_f) / 2)
      average_forecast_combined += remainder_average_combined
      rounded_forecast_average_combined = average_forecast_combined.to_i
      remainder_average_combined = average_forecast_combined - rounded_forecast_average_combined
      week_forecast_average_combined = rounded_forecast_average_combined + manual_forecast_us.values.sum + manual_forecast_ca.values.sum

      # average forecast (normalized)
      average_forecast_normalized_us = week_forecast_average_us - manual_forecast_us.values.sum
      average_forecast_normalized_ca = week_forecast_average_ca - manual_forecast_ca.values.sum
      average_forecast_normalized_combined = week_forecast_average_combined - manual_forecast_us.values.sum - manual_forecast_ca.values.sum

      forecasts << ItemDemandForecast.new(
        item_id: item.id,
        week_start_date: forecast_monday,
        quantity_16_us: week_forecast_16_us,
        quantity_16_ca: week_forecast_16_ca,
        quantity_16_combined: week_forecast_16_combined,
        quantity_52_us: week_forecast_52_us,
        quantity_52_ca: week_forecast_52_ca,
        quantity_52_combined: week_forecast_52_combined,
        quantity_average_us: week_forecast_average_us,
        quantity_average_ca: week_forecast_average_ca,
        quantity_average_combined: week_forecast_average_combined,
        quantity_average_normalized_us: average_forecast_normalized_us,
        quantity_average_normalized_ca: average_forecast_normalized_ca,
        quantity_average_normalized_combined: average_forecast_normalized_combined,
        quantity_amazon_us: manual_forecast_us['amazon'],
        quantity_amazon_ca: manual_forecast_ca['amazon'],
        quantity_amazon_combined: manual_forecast_us['amazon'] + manual_forecast_ca['amazon'],
        quantity_costco_us: manual_forecast_us['costco'],
        quantity_costco_ca: manual_forecast_ca['costco'],
        quantity_costco_combined: manual_forecast_us['costco'] + manual_forecast_ca['costco'],
        quantity_home_depot_us: manual_forecast_us['home_depot'],
        quantity_home_depot_ca: manual_forecast_ca['home_depot'],
        quantity_home_depot_combined: manual_forecast_us['home_depot'] + manual_forecast_ca['home_depot'],
        quantity_quote_us: manual_forecast_us['quote'],
        quantity_quote_ca: manual_forecast_ca['quote'],
        quantity_quote_combined: manual_forecast_us['quote'] + manual_forecast_ca['quote'],
        quantity_misc_us: manual_forecast_us['misc'],
        quantity_misc_ca: manual_forecast_ca['misc'],
        quantity_misc_combined: manual_forecast_us['misc'] + manual_forecast_ca['misc'],
        quantity_future_release_us: manual_forecast_us['future_release'],
        quantity_future_release_ca: manual_forecast_ca['future_release'],
        quantity_future_release_combined: manual_forecast_us['future_release'] + manual_forecast_ca['future_release'],
        starting_stock_us: starting_stock_us,
        starting_stock_ca: starting_stock_ca,
        starting_stock_combined: starting_stock_combined,
        po_received_us: po_receipt_count_us,
        po_received_ca: po_receipt_count_ca,
        po_received_combined: po_receipt_count_combined,
        on_order_us: on_order_us,
        on_order_ca: on_order_ca,
        on_order_combined: on_order_combined
      )
      demand_16.shift # remove the first one
      demand_16 << {qty_us: rounded_forecast_16_us, qty_ca: rounded_forecast_16_ca} # add the new forecast to the end
      demand_52.shift # remove the first one
      demand_52 << {qty_us: rounded_forecast_52_us, qty_ca: rounded_forecast_52_ca} # add the new forecast to the end
      forecast_monday += 1.week
      starting_stock_us = (starting_stock_us - week_forecast_average_us) + po_receipt_count_us
      starting_stock_ca = (starting_stock_ca - week_forecast_average_ca) + po_receipt_count_ca
      starting_stock_combined = starting_stock_us + starting_stock_ca
    end

  end
  require 'activerecord-import/base'
  require 'activerecord-import/active_record/adapters/postgresql_adapter'
  ItemDemandForecast.import forecasts
  return true
end

Instance Method Details

#item ⇒ Item

Returns:

See Also:



58
 
# File 'app/models/item_demand_forecast.rb', line 58

belongs_to :item, optional: true