Class: Report::ProfileDiscount::ProfileDiscount

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

Overview

Service object: profile discount.

Defined Under Namespace

Classes: Result

Class Method Summary collapse

Class Method Details

.accounts_overview(employee_ids) ⇒ Object

Accounts overview.

Parameters:

  • employee_ids (Array<Integer>)

Returns:

  • (Object) 


44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
# File 'app/services/report/profile_discount/profile_discount.rb', line 44

def self.accounts_overview(employee_ids)
  employee_ids_sql = employee_ids.present? ? " and primary_sales_rep_id in (#{employee_ids.join(',')})" : " "

  sql = <<-SQL.squish
    select pp_id,title,count(distinct id) as num_accounts
    from(
        select
        case when t.id in (1316,2422,2423,2424,2425,582,583) and c.catalog_id in (1,2) then t.id
             when c.catalog_id not in (1,2) then 999998 else 999999 end as pp_id,
        case when t.id in (1316,2422,2423,2424,2425,582,583) and c.catalog_id in (1,2) then title
             when c.catalog_id not in (1,2) then 'Catalog Pricing' else 'Others' end as title,c.id
        from parties c
        left join coupons t on c.tier2_program_pricing_id = t.id
        where c.type = 'Customer'
        and c.state <> 'guest'
        and c.id not in (188889,10954,362571)
        #{employee_ids_sql}
    )a
    group by pp_id,title
  SQL

  ActiveRecord::Base.lease_connection.execute(sql).to_a.map(&:symbolize_keys)
end

.business_overview_offices(employee_ids) ⇒ Object

Business overview offices.

Parameters:

  • employee_ids (Array<Integer>)

Returns:

  • (Object) 


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
 
# File 'app/services/report/profile_discount/profile_discount.rb', line 71

def self.business_overview_offices(employee_ids)
  employee_ids_sql = employee_ids.present? ? " and primary_sales_rep_id in (#{employee_ids.join(',')})" : " "

  sql = <<-SQL.squish
    select pp_id,title,sum(home_office) as home_office,sum(one_office) as one_office,sum(one_office_ws) as one_office_ws,sum(multiple_offices) as multiple_offices,sum(multiple_offices_ws) as multiple_offices_ws
    from(
        select
        case when t.id in (1316,2422,2423,2424,2425,582,583) and c.catalog_id in (1,2) and c.profile_id not in (35,20) then t.id
             when c.catalog_id not in (1,2) then 999998
             when c.profile_id not in (35,20) then 999999 else null end as pp_id,
        case when t.id in (1316,2422,2423,2424,2425,582,583) and c.catalog_id in (1,2) and c.profile_id not in (35,20) then title
             when c.catalog_id not in (1,2) then 'Catalog Pricing'
             when c.profile_id not in (35,20) then 'Others' else '' end as title,
        case when c.number_of_offices = 'Home office only' then 1 else 0 end as home_office,
        case when c.number_of_offices = 'One office (without showroom)' then 1 else 0 end as one_office,
        case when c.number_of_offices = 'One office (with showroom)' then 1 else 0 end as one_office_ws,
        case when c.number_of_offices = 'Multiple offices (without showroom)' then 1 else 0 end as multiple_offices,
        case when c.number_of_offices = 'Multiple offices (with one or more showrooms)' then 1 else 0 end as multiple_offices_ws
        from parties c
        left join coupons t on c.tier2_program_pricing_id = t.id
        where c.type = 'Customer'
        and c.state <> 'guest'
        and c.id not in (188889,10954,362571)
        #{employee_ids_sql}
    )a
    where pp_id is not null
    group by pp_id,title
  SQL

  ActiveRecord::Base.lease_connection.execute(sql).to_a.map(&:symbolize_keys)
end

.business_overview_ppy(employee_ids) ⇒ Object

Business overview ppy.

Parameters:

  • employee_ids (Array<Integer>)

Returns:

  • (Object) 


106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
 
# File 'app/services/report/profile_discount/profile_discount.rb', line 106

def self.business_overview_ppy(employee_ids)
  employee_ids_sql = employee_ids.present? ? " and primary_sales_rep_id in (#{employee_ids.join(',')})" : " "

  sql = <<-SQL.squish
    select pp_id,title,sum(ppy_0) as ppy_0,sum(ppy_1) as ppy_1,sum(ppy_2) as ppy_2,sum(ppy_3) as ppy_3
    from(
        select
        case when t.id in (1316,2422,2423,2424,2425,582,583) and c.catalog_id in (1,2) and c.profile_id not in (35,20) then t.id
             when c.catalog_id not in (1,2) then 999998
             when c.profile_id not in (35,20) then 999999 else null end as pp_id,
        case when t.id in (1316,2422,2423,2424,2425,582,583) and c.catalog_id in (1,2) and c.profile_id not in (35,20) then title
             when c.catalog_id not in (1,2) then 'Catalog Pricing'
             when c.profile_id not in (35,20) then 'Others' else '' end as title,
        case when c.projects_per_year = '0' then 1 else 0 end as ppy_0,
        case when c.projects_per_year = '1' then 1 else 0 end as ppy_1,
        case when c.projects_per_year = '2' then 1 else 0 end as ppy_2,
        case when c.projects_per_year = '3 or more' then 1 else 0 end as ppy_3
        from parties c
        left join coupons t on c.tier2_program_pricing_id = t.id
        where c.type = 'Customer'
        and c.state <> 'guest'
        and c.id not in (188889,10954,362571)
        #{employee_ids_sql}
    )a
    where pp_id is not null
    group by pp_id,title
  SQL

  ActiveRecord::Base.lease_connection.execute(sql).to_a.map(&:symbolize_keys)
end

.result_report(options = {}) ⇒ Result

Returns profile discount data.

Parameters:

  • options (Hash) (defaults to: {}) —

    report filter options

Options Hash (options):

  • employee_ids (Array<Integer>) —

    employees to include (blank entries ignored)

Returns:

  • (Result) —

    profile discount data



22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'app/services/report/profile_discount/profile_discount.rb', line 22

def self.result_report(options = {})
  employee_ids = options[:employee_ids].filter_map(&:presence)
  accounts_overview = accounts_overview(employee_ids)
  business_overview_offices = business_overview_offices(employee_ids)
  business_overview_ppy = business_overview_ppy(employee_ids)

  pricing_program_list = [[1316, 'Level Zero - MSRP or Catalog'],
                          [2422, 'Silver Level - 30%'],
                          [2423, 'Gold Level - 35%'],
                          [2424, 'Platinum Level - 40%'],
                          [2425, 'Titanium Level - 45%'],
                          [582, 'Buying Group Special - 35%'],
                          [583, 'Buying Group Special - 40%'],
                          [999_998, 'Catalog Pricing'],
                          [999_999, 'Others']]

  Result.new(success: true, accounts_overview: accounts_overview, business_overview_offices: business_overview_offices, business_overview_ppy: business_overview_ppy, pricing_program_list: pricing_program_list, employee_ids: employee_ids)
end