Class: Query::NewOpportunityFactQuery

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

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

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

Returns a new instance of NewOpportunityFactQuery.



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

def self.core_relation
	Analytic::DateTimeDimension.all.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 



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

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

Instance Method Details

#channel_filter(report_grouping_dim_ids) ⇒ Object 



50
51
52
53
54
 
# File 'app/services/query/new_opportunity_fact_query.rb', line 50

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

#date_time_filter(start_date, end_date) ⇒ Object 



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

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



17
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
 
# File 'app/services/query/new_opportunity_fact_query.rb', line 17

def select_statement(grouping_by,store_ids,channel)
	by_channel = " and new.report_grouping_dimension_id in (#{channel.join(',')})" rescue nil if channel
	by_store = " and new.store_id in (#{store_ids.join(',')})" rescue nil if store_ids
	@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 



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

def store_filter(store_ids)
	return self unless store_ids.is_a?Array and store_ids.present?
	@relation = @relation.where("analytic_won_opportunity_facts.store_id in (?)",store_ids)
	self
end