Class: Analytic::ItemDimension

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

Overview

== Schema Information

Table name: analytic_item_dimensions
Database name: primary

id :integer not null, primary key
name :string(255)
sku :string(255)
item_id :integer

Indexes

analytic_item_dimensions_item_id_idx (item_id)

Belongs to collapse

Class Method Summary collapse

Class Method Details

.refresh_data ⇒ Object 



22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
# File 'app/models/analytic/item_dimension.rb', line 22

def self.refresh_data
	sql = "WITH coupon_items as
			(select cp.id,cp.code,pf.item_ids from coupons cp
			inner join product_filters pf on pf.resource_id = cp.id and pf.resource_type = 'Coupon'  )

			,item_coupon as (select unnest(item_ids) as item_id,code  from coupon_items  )

			,customer_totals as
			(select quantity*discounted_price as customer_rev,rg.id as rg_id,dt.id as dt_id,quantity,item_id
			from line_items li
			inner join invoices i on i.id = li.resource_id
			inner join analytic_date_time_dimensions dt on dt.date = i.shipped_date
			inner join analytic_report_grouping_dimensions rg on rg.report_grouping = i.report_grouping
			where li.resource_type = 'Invoice' and li.created_at between '2011-01-01' and current_date
			 )

			,invoice_items as
			(select customer_rev as item_rev,quantity,item_id,rg_id,dt_id
			 from customer_totals
			 where item_id IN (select unnest(item_ids) from coupon_items)  )

			INSERT INTO analytic_coupon_items_sold_facts (coupon,item_dimension_id,item_revenue,quantity,date_time_dimension_id,report_grouping_revenue,report_grouping_dimension_id)
			select code as coupon, i.id as item_dimension_id, sum(item_rev) as item_revenue, sum(quantity) as quantity,dt_id as date_time_dimension_id,
			(select sum(customer_rev) from customer_totals ct where ct.rg_id = ii.rg_id and ct.dt_id = ii.dt_id) as report_grouping_revenue,rg_id as report_grouping_dimension_id
			from invoice_items ii inner join item_coupon ic on ic.item_id = ii.item_id
			inner join analytic_item_dimensions i on i.item_id = ii.item_id
			where code = 'DB8'
			group by coupon,sku,dt_id,rg_id,i.id order by rg_id,coupon,item_revenue desc,dt_id"
	result = execute(sql)
end

.select_options(character_limit = nil) ⇒ Object 



53
54
55
 
# File 'app/models/analytic/item_dimension.rb', line 53

def self.select_options(character_limit=nil)
  all.order("sku").map{|i| ["#{i.sku}#{(' - ' + i.name.first(20))}",i.id]}
end

Instance Method Details

#date_time_dimension ⇒ DateTimeDimension

Returns:

See Also:



19
 
# File 'app/models/analytic/item_dimension.rb', line 19

belongs_to :date_time_dimension, optional: true

#report_grouping_dimension ⇒ ReportGroupingDimension

Returns:

See Also:



20
 
# File 'app/models/analytic/item_dimension.rb', line 20

belongs_to :report_grouping_dimension, optional: true