Class: Query::ItemSoldFactQuery

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

Overview

Service object: item sold fact query.

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

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

Returns a new instance of ItemSoldFactQuery.



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

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

Class Method Details

.core_relation ⇒ Object 



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

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

.example_use ⇒ Object 



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

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

Instance Method Details

#all_filters ⇒ Object 



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

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 



51
52
53
54
55
56
 
# File 'app/services/query/item_sold_fact_query.rb', line 51

def catalog_filter(catalog_ids)
  return self unless catalog_ids.is_a?(Array) && catalog_ids.present?

  @relation = @relation.where(catalog_id: catalog_ids)
  self
end

#channel_filter(report_grouping_dim_ids) ⇒ Object 



44
45
46
47
48
49
 
# File 'app/services/query/item_sold_fact_query.rb', line 44

def channel_filter(report_grouping_dim_ids)
  return self unless report_grouping_dim_ids.is_a?(Array) && 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 



58
59
60
61
62
63
64
65
66
67
 
# File 'app/services/query/item_sold_fact_query.rb', line 58

def coupon_filter(coupon_ids, item_dim_ids = nil)
  return self unless coupon_ids.is_a?(Array) && coupon_ids.present?

  @relation = if item_dim_ids.present?
                @relation.where("coupon_id IN (?) OR item_dimension_id IN (?)", coupon_ids, item_dim_ids)
              else
                @relation.where(coupon_id: coupon_ids)
              end
  self
end

#coupon_ids_to_code(coupon_ids) ⇒ Object 



69
70
71
 
# File 'app/services/query/item_sold_fact_query.rb', line 69

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

#date_time_filter(start_date, end_date) ⇒ Object 



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

def date_time_filter(start_date, end_date)
  return self unless start_date.present? && 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 



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

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 



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

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)" if show_year.blank?
  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