Class: Report::ChannelRevenue::Query

Inherits:
Object
  • Object
show all
Defined in:
app/services/report/channel_revenue/query.rb

Constant Summary collapse

DEFAULT_COMPANIES = 
[Company::USA, Company::CAN]

Class Method Summary collapse

Class Method Details

.process_period_results(result, crr, period_channel_revenues, period, consolidated_currency) ⇒ Object

Process the period results for a channel

Parameters:

  • result (Hash) —

    The global report result hash

  • crr (Hash) —

    The channel revenue result hash

  • period_channel_revenues (Array) —

    The revenue results for the period

  • period (Symbol) —

    The period key (:period1 or :period2)



71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
 
# File 'app/services/report/channel_revenue/query.rb', line 71

def self.process_period_results(result, crr, period_channel_revenues, period, consolidated_currency)
  period_channel_revenues.group_by { |r| r['company_id'] }.each do |company_id, company_channel_revenues|
    company = Company.find(company_id)
    company_currency = company.currency
    crr[:company_results] ||= {}
    crr[:company_results][company_id] ||= {}
    crr[:company_results][company_id][period] ||= {}
    crr[:company_results][company_id][period][:gross_revenue_consolidated] = company_channel_revenues.first['gross_revenue_consolidated']
    crr[:company_results][company_id][period][:gross_revenue_native] = company_channel_revenues.first['gross_revenue_native']
    crr[:company_results][company_id][period][:invoices_count] = company_channel_revenues.first['invoices_count']
    crr[:company_results][company_id][period][:native_currency] = company_channel_revenues.first['native_currency']
    result[:totals][company_id] ||= { currency: nil }
    result[:totals][company_id][period] ||= {
      gross_revenue_consolidated: 0.0,
      gross_revenue_native: 0.0,
      gross_revenue: 0.0,
      currency: nil,
      invoices_count: 0.0
    }
    result[:totals][company_id][period][:gross_revenue_consolidated] += company_channel_revenues.first['gross_revenue_consolidated']
    result[:totals][company_id][period][:gross_revenue_native] += company_channel_revenues.first['gross_revenue_native']
    result[:totals][company_id][period][:invoices_count] += company_channel_revenues.first['invoices_count']
    if consolidated_currency
      crr[:company_results][company_id][period][:gross_revenue] = crr[:company_results][company_id][period][:gross_revenue_consolidated]
      result[:totals][company_id][period][:gross_revenue] = result[:totals][company_id][period][:gross_revenue_consolidated]
      crr[:company_results][company_id][:currency] = result[:totals][company_id][:currency] = 'USD'
    else
      crr[:company_results][company_id][period][:gross_revenue] = crr[:company_results][company_id][period][:gross_revenue_native]
      result[:totals][company_id][period][:gross_revenue] = result[:totals][company_id][period][:gross_revenue_native]
      crr[:company_results][company_id][:currency] = result[:totals][company_id][:currency] = company_currency
    end
  end
end

.report(period1_range:, period2_range:, primary_sales_rep_ids: nil, invoice_types: nil, company_ids: nil, consolidated_currency: true) ⇒ Hash

Returns a channel revenue report for the given date ranges, filters, and options

Parameters:

  • period1_range (Range) —

    The start and end date range for the first period

  • period2_range (Range) —

    The start and end date range for the second period

  • primary_sales_rep_ids (Integer) (defaults to: nil) —

    Filter the report by primary sales reps

  • invoice_types (Array<String>) (defaults to: nil) —

    Filter the report by invoice types

  • company_ids (Array<Integer>) (defaults to: nil) —

    Filter the report by company ids

  • consolidated_currency (Boolean) (defaults to: true) —

    Whether to return consolidated or native currency

Returns:

  • (Hash) —

    The channel revenue report



14
15
16
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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
# File 'app/services/report/channel_revenue/query.rb', line 14

def self.report(period1_range:, period2_range:, primary_sales_rep_ids: nil,
                invoice_types: nil, company_ids: nil, consolidated_currency: true)
  result = {}

  company_ids = company_ids&.map(&:presence)&.uniq&.compact&.presence
  company_ids ||= DEFAULT_COMPANIES
  result[:company_ids] = company_ids
  result[:period1_range] = period1_range
  result[:period2_range] = period2_range
  result[:primary_sales_rep_ids] = primary_sales_rep_ids
  result[:invoice_types] = invoice_types
  result[:consolidated_currency] = consolidated_currency

  # Retrieve raw results for each period
  period1_revenue = revenue_per_channel(date_range: period1_range,
                                        primary_sales_rep_ids:,
                                        invoice_types:,
                                        company_ids:)
  period2_revenue = revenue_per_channel(date_range: period2_range,
                                        primary_sales_rep_ids:,
                                        invoice_types:,
                                        company_ids:)

  channel_revenues = []

  # Gather a list of channel names
  channel_names = period1_revenue.map { |r| r['channel'] } + period2_revenue.map { |r| r['channel'] }
  channel_names = channel_names.sort.uniq

  result[:totals] ||= {}
  # Loop through all channels, build a result, gather data
  channel_names.each do |channel_name|
    crr = {}
    crr[:channel_name] = channel_name
    crr[:primary_sales_rep_ids] = primary_sales_rep_ids
    crr[:invoice_types] = invoice_types
    crr[:company_ids] = company_ids
    crr[:period1] = period1_range
    crr[:period2] = period2_range
    period1_channel_revenues = period1_revenue.select { |r| r['channel'] == channel_name }
    period2_channel_revenues = period2_revenue.select { |r| r['channel'] == channel_name }
    process_period_results(result, crr, period1_channel_revenues, :period1, consolidated_currency)
    process_period_results(result, crr, period2_channel_revenues, :period2, consolidated_currency)
    channel_revenues << crr
  end
  result[:channel_revenues] = channel_revenues

  result
end

.revenue_per_channel(date_range:, primary_sales_rep_ids: nil, invoice_types: nil, company_ids: nil) ⇒ Array

Returns revenue grouped by channel for the given filters and date range

Parameters:

  • date_range (Range) —

    The start and end date for the report

  • primary_sales_rep_ids (Integer) (defaults to: nil) —

    Filter by primary sales rep

  • invoice_types (Array<String>) (defaults to: nil) —

    Filter by invoice types

  • company_ids (Array<Integer>) (defaults to: nil) —

    Filter by company ids

  • consolidated_currency (Boolean) —

    Whether to return consolidated or native currency

Returns:

  • (Array) —

    Revenue grouped by channel



113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'app/services/report/channel_revenue/query.rb', line 113

def self.revenue_per_channel(date_range:, primary_sales_rep_ids: nil, invoice_types: nil,
                             company_ids: nil)
  primary_sales_rep_ids_sql = " AND invoices.primary_sales_rep_id IN (#{primary_sales_rep_ids.join(',')})" if primary_sales_rep_ids.present?
  invoice_types_sql = " AND invoices.invoice_type in (#{invoice_types.map { |it| "'#{it}'" }.join(',')})" if invoice_types.present?
  company_ids_sql = " AND invoices.company_id in (#{company_ids.map { |it| it.to_i }.join(',')})" if company_ids.present?
  sql = <<-EOS
    SELECT COALESCE(report_grouping,'Undefined') as channel, invoices.company_id,
    sum(invoices.revenue_consolidated) as gross_revenue_consolidated,
    sum(invoices.line_total) as gross_revenue_native,
    max(invoices.currency) as native_currency,
    count(invoices.id) as invoices_count
    FROM "invoices"
    WHERE 1=1
    #{invoice_types_sql}
    #{primary_sales_rep_ids_sql}
    #{company_ids_sql}
    AND (invoices.gl_date BETWEEN '#{date_range.first}' and '#{date_range.last}')
    GROUP BY COALESCE(report_grouping,'Undefined'),invoices.company_id
    HAVING sum(invoices.revenue_consolidated) > 0 OR count(invoices.id) > 0
    ORDER BY channel
  EOS

  results = ActiveRecord::Base.connection.execute(sql).to_a
end