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 ⇒ Object 



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

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) ⇒ Object 



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

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