Class: Query::NewOpportunityFactQuery

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

Overview

Service object: new opportunity fact query.

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

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

Returns a new instance of NewOpportunityFactQuery.



13
14
15
16
 
# File 'app/services/query/new_opportunity_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/new_opportunity_fact_query.rb', line 9

def self.core_relation
  Analytic::DateTimeDimension.joins("LEFT JOIN analytic_won_opportunity_facts on analytic_won_opportunity_facts.date_time_dimension_id = analytic_date_time_dimensions.id")
end

.example_use ⇒ Object 



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

def self.example_use
  q = new
  q.date_time_filter(120.days.ago, 16.days.ago).select_statement("month", %w[1 2], nil)
end

Instance Method Details

#channel_filter(report_grouping_dim_ids) ⇒ Object 



64
65
66
67
68
69
 
# File 'app/services/query/new_opportunity_fact_query.rb', line 64

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("analytic_won_opportunity_facts.report_grouping_dimension_id in (#{report_grouping_dim_ids.join(',')})")
  self
end

#date_time_filter(start_date, end_date) ⇒ Object 



57
58
59
60
61
62
 
# File 'app/services/query/new_opportunity_fact_query.rb', line 57

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_statement(grouping_by, store_ids, channel) ⇒ Object 



18
19
20
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
51
52
53
54
55
 
# File 'app/services/query/new_opportunity_fact_query.rb', line 18

def select_statement(grouping_by, store_ids, channel)
  if channel
    by_channel = begin
      " and new.report_grouping_dimension_id in (#{channel.join(',')})"
    rescue StandardError
      nil
    end
  end
  if store_ids
    by_store = begin
      " and new.store_id in (#{store_ids.join(',')})"
    rescue StandardError
      nil
    end
  end
  @relation.select("#{grouping_by} as time_name,year,
				round(cast(sum(analytic_won_opportunity_facts.a0_500) as decimal)/cast((select sum(total_opps) from analytic_new_opportunity_facts new where new.date_time_dimension_id in (
					select id from analytic_date_time_dimensions d where d.#{grouping_by} = analytic_date_time_dimensions.#{grouping_by} and d.year = analytic_date_time_dimensions.year) and new.store_id in (1,2)) as decimal)*100,2) as a0_500,
				round(cast(sum(analytic_won_opportunity_facts.a501_750) as decimal)/cast((select sum(total_opps) from analytic_new_opportunity_facts new where new.date_time_dimension_id in (
					select id from analytic_date_time_dimensions d where d.#{grouping_by} = analytic_date_time_dimensions.#{grouping_by} and d.year = analytic_date_time_dimensions.year) and new.store_id in (1,2)) as decimal)*100,2) as a501_750,
				round(cast(sum(analytic_won_opportunity_facts.a751_1000) as decimal)/cast((select sum(total_opps) from analytic_new_opportunity_facts new where new.date_time_dimension_id in (
					select id from analytic_date_time_dimensions d where d.#{grouping_by} = analytic_date_time_dimensions.#{grouping_by} and d.year = analytic_date_time_dimensions.year) and new.store_id in (1,2)) as decimal)*100,2) as a751_1000,
				round(cast(sum(analytic_won_opportunity_facts.a1001_2500) as decimal)/cast((select sum(total_opps) from analytic_new_opportunity_facts new where new.date_time_dimension_id in (
					select id from analytic_date_time_dimensions d where d.#{grouping_by} = analytic_date_time_dimensions.#{grouping_by} and d.year = analytic_date_time_dimensions.year) and new.store_id in (1,2)) as decimal)*100,2) as a1001_2500,
				round(cast(sum(analytic_won_opportunity_facts.gt_2500) as decimal)/cast((select sum(total_opps) from analytic_new_opportunity_facts new where new.date_time_dimension_id in (
					select id from analytic_date_time_dimensions d where d.#{grouping_by} = analytic_date_time_dimensions.#{grouping_by} and d.year = analytic_date_time_dimensions.year) and new.store_id in (1,2)) as decimal)*100,2) as gt_2500,
				sum(analytic_won_opportunity_facts.a0_500) as sum_0_500,
				sum(analytic_won_opportunity_facts.a501_750) as sum_501_750,
				sum(analytic_won_opportunity_facts.a751_1000) as sum_751_1000,
				sum(analytic_won_opportunity_facts.a1001_2500) as sum_1001_2500,
				sum(analytic_won_opportunity_facts.gt_2500) as sum_gt_2500,
				sum(analytic_won_opportunity_facts.total_opps) as won,
				(select sum(total_opps) from analytic_new_opportunity_facts new where new.date_time_dimension_id in (
					select id from analytic_date_time_dimensions d where d.#{grouping_by} = analytic_date_time_dimensions.#{grouping_by} and d.year = analytic_date_time_dimensions.year) #{by_channel} #{by_store}) as new_opps,
				sum(analytic_won_opportunity_facts.value) as won_rev,
				(select sum(value) from analytic_new_opportunity_facts new where new.date_time_dimension_id in (
					select id from analytic_date_time_dimensions d where d.#{grouping_by} = analytic_date_time_dimensions.#{grouping_by} and d.year = analytic_date_time_dimensions.year) #{by_channel} #{by_store}) as new_rev ").group("#{grouping_by},year").order("year,#{grouping_by}")
end

#store_filter(store_ids) ⇒ Object 



71
72
73
74
75
76
 
# File 'app/services/query/new_opportunity_fact_query.rb', line 71

def store_filter(store_ids)
  return self unless store_ids.is_a?(Array) && store_ids.present?

  @relation = @relation.where(analytic_won_opportunity_facts: { store_id: store_ids })
  self
end