Class: Analytic::ItemSoldFact

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

Overview

== Schema Information

Table name: analytic_item_sold_facts
Database name: primary

id :integer not null, primary key
cogs :decimal(, )
coupon_total :decimal(, )
extended_price :decimal(, )
quantity :integer
ship_to_state :string(255)
catalog_id :integer
coupon_id :integer
date_time_dimension_id :integer
item_dimension_id :integer
report_grouping_dimension_id :integer

Indexes

analytic_item_sold_facts_catalog_id_idx (catalog_id)
analytic_item_sold_facts_coupon_id_idx (coupon_id)
analytic_item_sold_facts_date_time_dimension_id_idx (date_time_dimension_id)
analytic_item_sold_facts_item_dimension_id_idx (item_dimension_id)
analytic_item_sold_facts_report_grouping_dimension_id_idx (report_grouping_dimension_id)
analytic_item_sold_facts_ship_to_state_idx (ship_to_state)

Belongs to collapse

Class Method Summary collapse

Class Method Details

.refresh_data(logger = nil) ⇒ Object 



35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
# File 'app/models/analytic/item_sold_fact.rb', line 35

def self.refresh_data(logger = nil)
  logger ||= Rails.logger

  max_date_id = Analytic::ItemSoldFact.maximum(:date_time_dimension_id)
  max_date_id = Analytic::DateTimeDimension.where(date: '2012-01-01') if max_date_id.blank?
  @max_date = Analytic::DateTimeDimension.where(id: max_date_id).pick(:date)

  sql = "INSERT INTO analytic_item_sold_facts (item_dimension_id,date_time_dimension_id,report_grouping_dimension_id,catalog_id,quantity,cogs,extended_price,coupon_total,coupon_id,ship_to_state)
	select aid.id as item_dimension_id,dt.id as date_time_dimension_id,
	rg.id as report_grouping_dimension_id,p.catalog_id,sum(li.quantity) as quantity,
	sum((si.unit_cogs*li.quantity)) as cogs,sum((li.quantity*li.discounted_price)) as extended_price,sum(coalesce(li.price - li.discounted_price,0)) as coupon_total,
	ld.coupon_id,(select addresses.state_code from addresses where addresses.id = inv.shipping_address_id) as ship_to_state
	from line_items li
	left join line_discounts ld on ld.line_item_id = li.id
	inner join invoices inv on inv.id = li.resource_id
	inner join parties p on p.id = inv.customer_id
	inner join catalog_items ci on ci.id = li.catalog_item_id
	inner join store_items si on si.id = ci.store_item_id
	inner join analytic_date_time_dimensions dt on dt.date = inv.gl_date
	inner join analytic_item_dimensions aid on aid.item_id = li.item_id
	inner join analytic_report_grouping_dimensions rg on rg.report_grouping = inv.report_grouping
	where gl_date between '#{@max_date}' and current_date
	and li.resource_type = 'Invoice'
	group by aid.id,dt.id,rg.id,p.catalog_id,ld.coupon_id,ship_to_state;"
  result = execute(sql)
end

Instance Method Details

#date_time_dimension ⇒ DateTimeDimension

Returns:

See Also:



31
 
# File 'app/models/analytic/item_sold_fact.rb', line 31

belongs_to :date_time_dimension, optional: true

#item_dimension ⇒ ItemDimension

Returns:

See Also:



33
 
# File 'app/models/analytic/item_sold_fact.rb', line 33

belongs_to :item_dimension, optional: true

#report_grouping_dimension ⇒ ReportGroupingDimension

Returns:

See Also:



32
 
# File 'app/models/analytic/item_sold_fact.rb', line 32

belongs_to :report_grouping_dimension, optional: true