Class: Analytic::ItemDemandFact

Inherits:
ApplicationRecord
  • Object
show all
Includes:
Utility
Defined in:
app/models/analytic/item_demand_fact.rb

Overview

== Schema Information

Table name: analytic_item_demand_facts
Database name: primary

id :integer not null, primary key
amazon_bulk_buy_ca :integer
amazon_bulk_buy_combined :integer
amazon_bulk_buy_us :integer
amazon_ca :integer
amazon_combined :integer
amazon_us :integer
costco_ca :integer
costco_combined :integer
costco_promotion_ca :integer
costco_promotion_combined :integer
costco_promotion_us :integer
costco_us :integer
date :date
home_depot_ca :integer
home_depot_combined :integer
home_depot_promotion_ca :integer
home_depot_promotion_combined :integer
home_depot_promotion_us :integer
home_depot_us :integer
quantity_ca :integer
quantity_combined :integer
quantity_normalized_ca :integer
quantity_normalized_combined :integer
quantity_normalized_us :integer
quantity_us :integer
warmlyyours_ca :integer
warmlyyours_combined :integer
warmlyyours_us :integer
item_id :integer

Indexes

index_analytic_item_demand_facts_on_date (date)
index_analytic_item_demand_facts_on_item_id_and_date (item_id,date)

Belongs to collapse

Class Method Summary collapse

Class Method Details

.refresh_data(logger = nil) ⇒ Object

belongs_to :report_grouping_dimension



49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
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
 
# File 'app/models/analytic/item_demand_fact.rb', line 49

def self.refresh_data(logger = nil)
   logger ||= Rails.logger
   Analytic::ItemDemandFact.delete_all
   ActiveRecord::Base.connection.execute("select setval('analytic_item_demand_facts_id_seq',1)")
	@max_date = Date.current - 3.years

   sql = "INSERT INTO analytic_item_demand_facts (item_id,date,
   quantity_combined,quantity_us,quantity_ca,
   quantity_normalized_combined,quantity_normalized_us,quantity_normalized_ca,
   amazon_combined,amazon_us,amazon_ca,
   amazon_bulk_buy_combined,amazon_bulk_buy_us,amazon_bulk_buy_ca,
   costco_combined,costco_us,costco_ca,
   costco_promotion_combined,costco_promotion_us,costco_promotion_ca,
   home_depot_combined,home_depot_us,home_depot_ca,
   home_depot_promotion_combined,home_depot_promotion_us,home_depot_promotion_ca,
   warmlyyours_combined,warmlyyours_us,warmlyyours_ca)
   select item_id,
   coalesce(li.future_release_date,li.created_at::date) as date,
   coalesce(sum(li.quantity_us),0) + coalesce(sum(li.quantity_ca),0) as quantity_combined,
   coalesce(sum(li.quantity_us),0) as quantity_us,
   coalesce(sum(li.quantity_ca),0) as quantity_ca,
   coalesce(sum(li.quantity_us),0) + coalesce(sum(li.quantity_ca),0) - coalesce(sum(li.amazon_bulk_buy_us),0) - coalesce(sum(li.amazon_bulk_buy_ca),0) - coalesce(sum(li.costco_promotion_us),0) - coalesce(sum(li.costco_promotion_ca),0) - coalesce(sum(li.home_depot_promotion_us),0) - coalesce(sum(li.home_depot_promotion_ca),0) as quantity_normalized_combined,
   coalesce(sum(li.quantity_us),0) - coalesce(sum(li.amazon_bulk_buy_us),0) - coalesce(sum(li.costco_promotion_us),0) - coalesce(sum(li.home_depot_promotion_us),0) as quantity_normalized_us,
   coalesce(sum(li.quantity_ca),0) - coalesce(sum(li.amazon_bulk_buy_ca),0) - coalesce(sum(li.costco_promotion_ca),0) - coalesce(sum(li.home_depot_promotion_ca),0) as quantity_normalized_ca,
   coalesce(sum(li.amazon_us),0) + coalesce(sum(li.amazon_ca),0) as amazon_combined,
   coalesce(sum(li.amazon_us),0) as amazon_us,
   coalesce(sum(li.amazon_ca),0) as amazon_ca,
   coalesce(sum(li.amazon_bulk_buy_us),0) + coalesce(sum(li.amazon_bulk_buy_ca),0) as amazon_bulk_buy_combined,
   coalesce(sum(li.amazon_bulk_buy_us),0) as amazon_bulk_buy_us,
   coalesce(sum(li.amazon_bulk_buy_ca),0) as amazon_bulk_buy_ca,
   coalesce(sum(li.costco_us),0) + coalesce(sum(li.costco_ca),0) as costco_combined,
   coalesce(sum(li.costco_us),0) as costco_us,
   coalesce(sum(li.costco_ca),0) as costco_ca,
   coalesce(sum(li.costco_promotion_us),0) + coalesce(sum(li.costco_promotion_ca),0) as costco_promotion_combined,
   coalesce(sum(li.costco_promotion_us),0) as costco_promotion_us,
   coalesce(sum(li.costco_promotion_ca),0) as costco_promotion_ca,
   coalesce(sum(li.home_depot_us),0) + coalesce(sum(li.home_depot_ca),0) as home_depot_combined,
   coalesce(sum(li.home_depot_us),0) as home_depot_us,
   coalesce(sum(li.home_depot_ca),0) as home_depot_ca,
   coalesce(sum(li.home_depot_promotion_us),0) + coalesce(sum(li.home_depot_promotion_ca),0) as home_depot_promotion_combined,
   coalesce(sum(li.home_depot_promotion_us),0) as home_depot_promotion_us,
   coalesce(sum(li.home_depot_promotion_ca),0) as home_depot_promotion_ca,
   coalesce(sum(li.quantity_us),0) + coalesce(sum(li.quantity_ca),0) - coalesce(sum(li.amazon_us),0) - coalesce(sum(li.amazon_ca),0) - coalesce(sum(li.amazon_bulk_buy_us),0) - coalesce(sum(li.amazon_bulk_buy_ca),0) - coalesce(sum(li.costco_us),0) - coalesce(sum(li.costco_ca),0) - coalesce(sum(li.costco_promotion_us),0) - coalesce(sum(li.costco_promotion_ca),0) - coalesce(sum(li.home_depot_us),0) - coalesce(sum(li.home_depot_ca),0) - coalesce(sum(li.home_depot_promotion_us),0) - coalesce(sum(li.home_depot_promotion_ca),0) as warmlyyours_combined,
   coalesce(sum(li.quantity_us),0) - coalesce(sum(li.amazon_us),0) - coalesce(sum(li.amazon_bulk_buy_us),0) - coalesce(sum(li.costco_us),0) - coalesce(sum(li.costco_promotion_us),0) - coalesce(sum(li.home_depot_us),0) - coalesce(sum(li.home_depot_promotion_us),0) as warmlyyours_us,
   coalesce(sum(li.quantity_ca),0) - coalesce(sum(li.amazon_ca),0) - coalesce(sum(li.amazon_bulk_buy_ca),0) - coalesce(sum(li.costco_ca),0) - coalesce(sum(li.costco_promotion_ca),0) - coalesce(sum(li.home_depot_ca),0) - coalesce(sum(li.home_depot_promotion_ca),0) as warmlyyours_ca
   from view_expanded_line_items li
   where li.created_at::date between '#{@max_date}' and current_date
   group by item_id,coalesce(li.future_release_date,li.created_at::date);"
	result = execute(sql)
end

Instance Method Details

#item ⇒ Item

Returns:

See Also:



46
 
# File 'app/models/analytic/item_demand_fact.rb', line 46

belongs_to :item, optional: true