Class: Query::ItemSoldFactQuery

Inherits:
BaseQuery
  • Object
show all
Defined in:
app/services/query/item_sold_fact_query.rb

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(relation = nil, params = nil, logger = nil) ⇒ ItemSoldFactQuery

Returns a new instance of ItemSoldFactQuery.



12
13
14
15
 
# File 'app/services/query/item_sold_fact_query.rb', line 12

def initialize(relation = nil, params = nil, logger = nil)
	@params = params
	super(relation || self.class.core_relation, logger)
end

Class Method Details

.core_relation ⇒ Object 



8
9
10
 
# File 'app/services/query/item_sold_fact_query.rb', line 8

def self.core_relation
	Analytic::ItemSoldFact.all.joins(:item_dimension, :report_grouping_dimension, :date_time_dimension)
end

.example_use ⇒ Object 



3
4
5
6
 
# File 'app/services/query/item_sold_fact_query.rb', line 3

def self.example_use
	q = self.new
	q.date_time_filter(30.days.ago, 10.days.ago).coupon_filter(Coupon.pluck(:id)).select_statement
end

Instance Method Details

#all_filters ⇒ Object 



30
31
32
33
34
 
# File 'app/services/query/item_sold_fact_query.rb', line 30

def all_filters
	date_time_filter @params[:start_date], @params[:end_date] 
	coupon_filter(@params[:coupon_ids],@params[:item_dim_ids])
	self
end

#catalog_filter(catalog_ids) ⇒ Object 



48
49
50
51
52
 
# File 'app/services/query/item_sold_fact_query.rb', line 48

def catalog_filter(catalog_ids)
	return self unless catalog_ids.is_a?Array and catalog_ids.present?
	@relation = @relation.where(catalog_id: catalog_ids)
	self
end

#channel_filter(report_grouping_dim_ids) ⇒ Object 



42
43
44
45
46
 
# File 'app/services/query/item_sold_fact_query.rb', line 42

def channel_filter(report_grouping_dim_ids)
	return self unless report_grouping_dim_ids.is_a?Array and report_grouping_dim_ids.present?
	@relation = @relation.where(report_grouping_dimension_id: report_grouping_dim_ids)
	self
end

#coupon_filter(coupon_ids, item_dim_ids = nil) ⇒ Object 



54
55
56
57
58
59
60
61
62
 
# File 'app/services/query/item_sold_fact_query.rb', line 54

def coupon_filter(coupon_ids, item_dim_ids=nil)
	return self unless coupon_ids.is_a?Array and coupon_ids.present?
	if coupon_ids.present? and item_dim_ids.present?
		@relation = @relation.where("coupon_id in (#{coupon_ids.join(',')}) or item_dimension_id in  (#{item_dim_ids.join(',')})")
	else
		@relation = @relation.where("coupon_id in (#{coupon_ids.join(',')})")
	end
	self
end

#coupon_ids_to_code(coupon_ids) ⇒ Object 



64
65
66
 
# File 'app/services/query/item_sold_fact_query.rb', line 64

def coupon_ids_to_code(coupon_ids)
	Coupon.where(id: coupon_ids).pluck("code").compact.uniq.collect{|a| "'#{a}'"}.join(',')
end

#date_time_filter(start_date, end_date) ⇒ Object 



36
37
38
39
40
 
# File 'app/services/query/item_sold_fact_query.rb', line 36

def date_time_filter(start_date, end_date)
	return self unless start_date.present? and end_date.present?
	@relation = @relation.where("analytic_date_time_dimensions.date between ? and ?", start_date, end_date)
	self
end

#select_coupon_item_detail(start_date, end_date) ⇒ Object 



17
18
19
 
# File 'app/services/query/item_sold_fact_query.rb', line 17

def select_coupon_item_detail(start_date, end_date)
	@relation.select("sku,(select code from coupons where coupons.id = coupon_id limit 1) as coupon_name,report_grouping,sum(extended_price) as item_rev,sum(quantity) as qty,(select sum(os.total_revenue) from analytic_orders_sold_facts os where os.report_grouping_dimension_id = analytic_report_grouping_dimensions.id and os.date_time_dimension_id in (select d.id from analytic_date_time_dimensions d where d.date between '#{start_date}' and '#{end_date}')) as report_grouping_revenue").group("(select code from coupons where coupons.id = coupon_id limit 1),sku,analytic_report_grouping_dimensions.id,report_grouping").order("sum(extended_price) desc")
end

#select_grouped_coupon_item_totals(grouping_by, show_year) ⇒ Object 



21
22
23
24
25
26
27
28
 
# File 'app/services/query/item_sold_fact_query.rb', line 21

def select_grouped_coupon_item_totals(grouping_by,show_year)
	week_start = ",week_start_date" if grouping_by == "week"
	y = " ||' '''||right(cast(year as varchar),2)" unless show_year.present?
	t = ",to_char(week_start_date, 'MM/DD') as time_name"
	t = ",'Q' || #{grouping_by} #{y} as time_name" if grouping_by == "quarter"
	t = ",to_char(to_timestamp (month::text, 'MM'), 'TMmon') #{y} as time_name" if grouping_by == "month"
	@relation.except(:select).select("(select code from coupons where coupons.id = coupon_id) as coupon,sku,sum(extended_price) as item_rev,#{grouping_by} as time_measurement,year #{t},(select sum(os.total_revenue) from analytic_orders_sold_facts os inner join analytic_date_time_dimensions dt on dt.id = os.date_time_dimension_id where os.report_grouping_dimension_id = analytic_report_grouping_dimensions.id and os.catalog_id = analytic_item_sold_facts.catalog_id and dt.year = analytic_date_time_dimensions.year and dt.#{grouping_by} = analytic_date_time_dimensions.#{grouping_by}) as report_grouping_revenue").group("(select code from coupons where coupons.id = coupon_id),analytic_report_grouping_dimensions.id,analytic_item_sold_facts.catalog_id,sku,#{grouping_by},year #{week_start}").order("sum(extended_price) desc")
end