Class: Report::ProfileDiscount::ProfileDiscount

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

Defined Under Namespace

Classes: Result

Class Method Summary collapse

Class Method Details

.accounts_overview(employee_ids) ⇒ Object 



27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
# File 'app/services/report/profile_discount/profile_discount.rb', line 27

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

  sql = <<-SQL
    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

  results = ActiveRecord::Base.connection.execute(sql).to_a.map{ |r| r.symbolize_keys }
end

.business_overview_offices(employee_ids) ⇒ Object 



51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'app/services/report/profile_discount/profile_discount.rb', line 51

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

  sql = <<-SQL
    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

  results = ActiveRecord::Base.connection.execute(sql).to_a.map{ |r| r.symbolize_keys }
end

.business_overview_ppy(employee_ids) ⇒ Object 



83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'app/services/report/profile_discount/profile_discount.rb', line 83

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

  sql = <<-SQL
    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

  results = ActiveRecord::Base.connection.execute(sql).to_a.map{ |r| r.symbolize_keys }
end

.result_report(options = {}) ⇒ Object 



8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
# File 'app/services/report/profile_discount/profile_discount.rb', line 8

def self.result_report(options = {})
  employee_ids = options[:employee_ids].map(&:presence).compact
  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%'],
                          [999998,'Catalog Pricing'],
                          [999999,'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