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)

Class Method Summary collapse

Class Method Details

.refresh_data ⇒ void

This method returns an undefined value.

Refreshes coupon-items-sold analytic facts from invoice line items.



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
52
 
# File 'app/models/analytic/item_dimension.rb', line 23

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"
  execute(sql)
end

.select_options(_character_limit = nil) ⇒ Array<Array(String, Integer)>

Parameters:

  • _character_limit (Integer, nil) (defaults to: nil) —

    unused

Returns:

  • (Array<Array(String, Integer)>) 


56
57
58
 
# File 'app/models/analytic/item_dimension.rb', line 56

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