Class: Crm::SalesCommissionsController

Inherits:
CrmController show all
Includes:
Controllers::Workflowable
Defined in:
app/controllers/crm/sales_commissions_controller.rb

Overview

Controller: sales commissions.

Constant Summary

Constants included from Controllers::ReferenceFindable

Controllers::ReferenceFindable::ID_EMBEDDED_PATTERNS

Constants included from Controllers::AnalyticsEvents

Controllers::AnalyticsEvents::MAX_QUEUED_EVENTS, Controllers::AnalyticsEvents::SESSION_KEY

Constants included from Controllers::ErrorRendering

Controllers::ErrorRendering::NON_CONTENT_PATH_PREFIXES

Instance Method Summary collapse

Methods included from Controllers::Workflowable

#render_workflow_error_stream, #render_workflow_success_stream, #workflow_action, #workflow_action_complete

Methods inherited from CrmController

#access_denied, #context_id, #context_object, #crm_home_path, #current_ability, #default_url_options, #download_temp, #get_tempfile_path_for_download, #init_status_job_collector, #initialize_crm_lazy_chunks, #persist_enqueued_status_jobs, #record_not_found, #redirect_to_job_or_fallback, #render_edit_action, #set_context, #set_download_path, #stash_file_for_temp_download, #sync_admin_presence_cookie, #touch_employee_last_seen

Methods inherited from ApplicationController

#account_impersonated?, #add_to_flash, #after_sign_in_path_for, #bypass_forgery_protection?, #chat_enabled?, #cloudflare_cleared?, #default_catalog, #default_url_options, #enable_turbo_frames, #find_publication, #fix_invalid_accept_header, #init_js_utils, #is_globals_call?, #layout_by_resource, #locale_store, #redirect_to, #require_employee_for_crm, #set_base_host, #set_real_ip, #set_report_errors_for, #should_render_layout?, #skip_layout_for_turbo_frame?, #stamp_impersonation_context, #tab_frame_breakout_request?, #warmlyyours_canada_ip?, #warmlyyours_ip?, #y

Methods included from Controllers::ReturnPathHandling

#check_for_return_path, #redirect_to_return_path_or_default

Methods included from Controllers::AnalyticsEvents

#consume_queued_analytics_events, #registration_lead_type, #track_event

Methods included from Controllers::DeviceDetection

#device_detector, #is_ie?

Methods included from Controllers::SubdomainDetection

#is_crm_request?, #is_www_request?, #json_request?

Methods included from Controllers::TurboSafeRedirect

#redirect_to

Methods included from Controllers::TrackingDetection

#bot_request?, #gdpr_country?, #gdpr_country_data, #prevent_bots, #set_tracking_cookie, #track_visitor?

Methods included from Controllers::AcceleratedFileSending

#send_file_accelerated, #send_upload_accelerated

Methods included from Controllers::ErrorRendering

#excp_string, #mail_to_for_error_reporting, #render_400, #render_404, #render_406, #render_410, #render_500, #render_invalid_authenticity_token, #render_ip_spoof_error, #render_unpermitted_parameters, #safe_referer_or_fallback

Methods included from Controllers::TurnstileVerification

#load_turnstile_script_tag, #turnstile_lazy_widget, #turnstile_script_tag, #turnstile_widget, #validate_turnstile!

Methods included from Controllers::CloudflareCaching

edge_cached, #edge_cached_action?, #reset_cloudflare_cache, #set_cloudflare_cache, #skip_edge_cache!, #skip_session

Methods included from Controllers::Webpackable

#preload_webpack_fonts, #webpack_css_include, #webpack_css_url, #webpack_js_include, #wpd_is_running?

Methods included from Controllers::Localizable

#cloudflare_country_locale, #determine_request_locale, #geocoder_locale, #guest_user_locale_check, #locale_optional_www_auth_path?, #param_locale, #set_locale, #set_request_locale, #skip_localization?, #warmlyyours_ip_locale

Methods included from Controllers::Authenticable

#access_denied, #authenticate_account, #authenticate_account!, #authenticate_account_from_login_token!, #check_is_a_manager, #check_is_a_sales_manager, #check_is_an_admin, #check_is_an_employee, #check_party, #clear_mismatched_guest_user, #create_guest_user, #credentials?, #current_or_guest_user, #current_or_guest_user_id_read_only, #current_user, #devise_mapping, #fully_logged_in?, #generate_bot_id, #guest_user, #identifiable?, #init_current_user, #initialize_guest, #load_context_user, #logging_in, #resource, #resource_name, #restrict_access_for_non_employees, #scrubbed_request_path, #user_object, #warn_on_session_guest_id_leak

Methods included from UrlsHelper

#catalog_breadcrumb_links, #catalog_link, #catalog_link_for_product_line, #catalog_link_for_sku, #cms_link, #delocalized_path, #path_to_sales_product_sku, #path_to_sales_product_sku_for_product_line, #path_to_sales_product_sku_for_product_line_slug, #product_line_from_catalog_link, #protocol_neutral_url, #sanitize_external_url, #valid_external_url?

Instance Method Details

#charts ⇒ void

This method returns an undefined value.

Renders the commission charts partial for the employee's current and previous year.



64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 64

def charts
  load_sales_commission
  load_rate
  load_type_of_user

  employee_id = @sales_commission.employee_id

  # Current year — only up to the clicked record's month
  year_start = @sales_commission.commission_date.beginning_of_year
  month_end  = @sales_commission.commission_date.end_of_month

  @chart_data = SalesCommission
                .where(employee_id: employee_id)
                .where(commission_date: year_start..month_end)
                .order(:commission_date)
                .pluck(:commission_date, :sales, :total)

  # Previous year — full 12 months
  prev_year_start = year_start - 1.year
  prev_year_end   = year_start - 1.day # Dec 31 of previous year

  @prev_chart_data = SalesCommission
                     .where(employee_id: employee_id)
                     .where(commission_date: prev_year_start..prev_year_end)
                     .order(:commission_date)
                     .pluck(:commission_date, :sales, :total)

  render partial: 'charts', layout: false
end

#do_remove_orders ⇒ void

This method returns an undefined value.

Removes the selected net-base detail orders and recomputes the commission.



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
136
137
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 106

def do_remove_orders
  if params[:sales_commission_net_base_details_ids].present?
    sales_commission_net_base_details_ids = params[:sales_commission_net_base_details_ids]
    sales_commission_net_base_details_ids.each do |sc_nbd_id|
      scnd = SalesCommissionNetBaseDetail.find(sc_nbd_id)
      scnd.update_columns(removed: true, updater_id: current_user.id)
    end

    sc = SalesCommission.find(params[:id])
    new_sales_commission = SalesCommission.update_sales_rep_data(sc.commission_date, sc.employee_id, params[:id])
    sc.update_columns(num_orders: new_sales_commission[0][:num_orders],
                      sales: new_sales_commission[0][:sales],
                      cogs: new_sales_commission[0][:cogs],
                      profit: new_sales_commission[0][:profit],
                      co_cm_bd: new_sales_commission[0][:co_cm_bd],
                      net_base: new_sales_commission[0][:net_base],
                      tier1: new_sales_commission[0][:tier1],
                      tier2: new_sales_commission[0][:tier2],
                      tier3: new_sales_commission[0][:tier3],
                      tier4: new_sales_commission[0][:tier4],
                      tier5: new_sales_commission[0][:tier5],
                      tier6: new_sales_commission[0][:tier6],
                      total: new_sales_commission[0][:total],
                      updated_at: new_sales_commission[0][:updated_at],
                      updater_id: current_user.id)
    SalesCommission.repopulate_net_base_tables(sc.commission_date, sc.employee_id, sc.emp_comp_id, params[:id], current_user.id)
  end

  total_records_removed = SalesCommissionNetBaseDetail.where(sales_commission_id: params[:id]).where(removed: true).size
  flash[:warning] = "The current sales commission has <b>#{total_records_removed}</b> records removed from calculations."
  redirect_to sales_commission_path(params[:id])
end

#edit ⇒ void

This method returns an undefined value.

Renders the edit form for a sales commission.



44
45
46
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 44

def edit
  load_sales_commission
end

#index ⇒ void

This method returns an undefined value.

Lists sales commissions, newest commission date first.



11
12
13
14
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 11

def index
  authorize!(:read, SalesCommission)
  load_sales_commissions
end

#load_commission_percentage(year) ⇒ void

This method returns an undefined value.

Loads the employee's commission tier rates for the given year.

Parameters:

  • year (Integer) —

    the commission year to load rates for



229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 229

def load_commission_percentage(year)
  employee = @sales_commission.employee_id
  year = 2023 if year < 2024
  sql_date = Date.current.year == year ? 'is_active = true' : "to_char(end_date, 'YYYY')::int = #{year}"

  sql = <<-SQL.squish
    select ('Tier ' || commission_tier_id::varchar) as tier,commission_tier_description as description,commission_rate
    from commission_rates
    where employee_id = #{employee}
    and #{sql_date}
    order by 1;
  SQL

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

#load_progression_data ⇒ void

This method returns an undefined value.

Loads goal, sales, and commission progression data for the employee's year to date.



295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 295

def load_progression_data
  employee_id = @sales_commission.employee_id
  cur_end_date1 = @sales_commission.commission_date
  cur_end_date2 = cur_end_date1.end_of_year
  cur_start_date = cur_end_date1.beginning_of_year
  prev_end_date1 = cur_end_date1.years_ago(1)
  prev_end_date2 = cur_end_date2.years_ago(1)
  prev_start_date = cur_start_date.years_ago(1)

  sql = <<-SQL.squish
    with goals as (
        select
        sum(case when period_begins between '#{prev_start_date}' and '#{prev_end_date1}' then sales_goal else 0 end) as prev_sales_goal,
        sum(case when period_begins between '#{cur_start_date}' and '#{cur_end_date1}' then sales_goal else 0 end) as cur_sales_goal,
        sum(case when period_begins between '#{prev_start_date}' and '#{prev_end_date2}' then sales_goal else 0 end) as ttl_prev_sales_goal,
        sum(case when period_begins between '#{cur_start_date}' and '#{cur_end_date2}' then sales_goal else 0 end) as ttl_cur_sales_goal
        from sales_goals
        where (period_begins between '#{cur_start_date}' and '#{cur_end_date2}' or period_begins between '#{prev_start_date}' and '#{prev_end_date2}')
        and to_char(period_begins,'MM')::numeric = to_char(period_ends,'MM')::numeric
        and employee_id = #{employee_id}

    ), sales as (
        select
        sum(case when (gl_date between '#{prev_start_date}' and '#{prev_end_date1}') and primary_sr_id = #{employee_id} then (coalesce(quantity,0) * discounted_price * exchange_rate) else 0 end) as emp_prev_sales,
        sum(case when (gl_date between '#{cur_start_date}' and '#{cur_end_date1}') and primary_sr_id = #{employee_id} then (coalesce(quantity,0) * discounted_price * exchange_rate) else 0 end) as emp_cur_sales,
        sum(case when gl_date between '#{prev_start_date}' and '#{prev_end_date1}' then (coalesce(quantity,0) * discounted_price * exchange_rate) else 0 end) as ttl_prev_sales,
        sum(case when gl_date between '#{cur_start_date}' and '#{cur_end_date1}' then (coalesce(quantity,0) * discounted_price * exchange_rate) else 0 end) as ttl_cur_sales,
        to_char(('#{cur_end_date1}'::date),'MM')::int as cur_month,
        (12 - to_char(('#{cur_end_date1}'::date),'MM')::int) as months_left
        from view_sales
        where (gl_date between '#{cur_start_date}' and '#{cur_end_date1}' or gl_date between '#{prev_start_date}' and '#{prev_end_date1}')
        and invoice_type in ('SO','CI')

    ), commission as (
        select
        sum(case when commission_date between '#{prev_start_date}' and '#{prev_end_date1}' then total else 0 end) as prev_sales_commission,
        sum(case when commission_date between '#{cur_start_date}' and '#{cur_end_date1}' then total else 0 end) as cur_sales_commission
        from sales_commissions
        where (commission_date between '#{cur_start_date}' and '#{cur_end_date1}' or commission_date between '#{prev_start_date}' and '#{prev_end_date1}')
        and employee_id = #{employee_id}
    )
    select *
    from goals,sales,commission;
  SQL

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

#load_rate ⇒ void

This method returns an undefined value.

Loads the USD/CAD exchange rates for the commission month, falling back to the latest non-zero rate.



200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 200

def load_rate
  date = @sales_commission.commission_date
  sql = <<-SQL.squish
    select usd_to_cad,cad_to_usd
    from xrate_averages xr
    inner join analytic_date_time_dimensions dt on xr.effective_date = dt.date
    where year = #{date.year.to_i}
    and month = #{date.month.to_i}
  SQL

  @rates = ActiveRecord::Base.lease_connection.execute(sql).to_a.map(&:symbolize_keys)

  return unless @rates.empty? || @rates[0][:usd_to_cad].to_f.zero?

  fallback_sql = <<-SQL.squish
      select usd_to_cad,cad_to_usd
      from xrate_averages xr
      inner join analytic_date_time_dimensions dt on xr.effective_date = dt.date
      where usd_to_cad > 0
      order by dt.date desc
      limit 1
  SQL
  @rates = ActiveRecord::Base.lease_connection.execute(fallback_sql).to_a.map(&:symbolize_keys)
end

#load_sales_commission ⇒ Object

def pay_out
load_sales_commission
@sales_commission.trigger_paid_out
redirect_to sales_commission_path(@sales_commission)
end



162
163
164
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 162

def load_sales_commission
  @sales_commission = SalesCommission.with_employee_name.with_rate.find(params[:id])
end

#load_sales_commission_by_channel ⇒ void

This method returns an undefined value.

Loads commission totals grouped by sales channel.



248
249
250
251
252
253
254
255
256
257
258
259
260
261
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 248

def load_sales_commission_by_channel
  sc_id = @sales_commission.id
  sql = <<-SQL.squish
    select ((row_number() over (order by sum(profit) desc)) + 8) as sorted,channel,sum(case when reference_number = 'BD' then 0 else 1 end) as num_orders,sum(sales) as sales,
           sum(cogs) as cogs,sum(profit) as profit,sum(tier1) as tier1,sum(tier2) as tier2,sum(tier3) as tier3,sum(tier4) as tier4,
           sum(tier5) as tier5,sum(tier6) as tier6,sum(tier7) as tier7,sum(tier8) as tier8,sum(co_cm_bd) as co_cm_bd,sum(total) as total
    from sales_commission_details
    where sales_commission_id = #{sc_id}
    group by channel
    order by profit desc;
  SQL

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

#load_sales_commission_by_channel_breakdown ⇒ void

This method returns an undefined value.

Loads the per-order commission detail rows grouped by channel.



266
267
268
269
270
271
272
273
274
275
276
277
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 266

def load_sales_commission_by_channel_breakdown
  sc_id = @sales_commission.id
  sql = <<-SQL.squish
    select rank,channel,p.full_name,reference_number,order_id,sales,cogs,profit,tier1,tier2,tier3,tier4,tier5,tier6,tier7,tier8,co_cm_bd,total,techs_present,sales_support_rep
    from sales_commission_details scd
    inner join parties p on scd.customer_id = p.id
    where sales_commission_id = #{sc_id}
    order by channel,rank;
  SQL

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

#load_sales_commission_net_base ⇒ void

This method returns an undefined value.

Loads the net-base summary rows for the commission.



178
179
180
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 178

def load_sales_commission_net_base
  @sales_commission_net_base = SalesCommissionNetBase.with_details_num.where(sales_commission_id: params[:id].to_i).order(:sorted)
end

#load_sales_commission_net_base_breakdown ⇒ void

This method returns an undefined value.

Loads the per-order net-base breakdown rows for the commission.



185
186
187
188
189
190
191
192
193
194
195
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 185

def load_sales_commission_net_base_breakdown
  sc_id = @sales_commission.id
  sql = <<-SQL.squish
    select rank,id,gl_date,customer_name,customer_id,sales_code,reference_number,reference_id,company_id,sales,cogs,profit,rate,total,removed
    from sales_commission_net_base_details
    where sales_commission_id = #{sc_id}
    order by sales_code,rank;
  SQL

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

#load_sales_commissions ⇒ void

This method returns an undefined value.

Loads the paginated sales commission list for the index.



169
170
171
172
173
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 169

def load_sales_commissions
  @q = SalesCommission.with_employee_name.with_rate.ransack(params[:q])
  @q.sorts = 'commission_date desc' if @q.sorts.empty?
  @pagy, @sales_commissions = pagy(@q.result, limit: 50)
end

#load_type_of_user ⇒ void

This method returns an undefined value.

Loads the current user's role for display on the commission page.



282
283
284
285
286
287
288
289
290
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 282

def load_type_of_user
  sql = <<-SQL.squish
    select r.name as wy_user,company_id
    from parties p inner join accounts a on p.id = a.party_id inner join accounts_roles ar on ar.account_id = a.id inner join roles r on ar.role_id = r.id
    where type = 'Employee' and inactive = false and p.id = #{current_user.id} order by p.id,r.id limit 1;
  SQL

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

#remove_orders ⇒ void

This method returns an undefined value.

Renders the order-removal review for a sales commission.



97
98
99
100
101
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 97

def remove_orders
  load_sales_commission
  load_sales_commission_net_base_breakdown
  load_rate
end

#show ⇒ void

This method returns an undefined value.

Shows one sales commission with its breakdowns, rates, and progression data.



19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 19

def show
  load_type_of_user
  load_sales_commission
  load_sales_commission_net_base
  load_sales_commission_net_base_breakdown
  load_rate
  load_commission_percentage(@sales_commission.commission_date.year)
  load_sales_commission_by_channel
  load_sales_commission_by_channel_breakdown
  load_progression_data

  # If loaded inside a Turbo Frame, return the mini view wrapped in a matching turbo-frame
  return render partial: 'mini_view', layout: false if request.headers['Turbo-Frame'].present?

  respond_to do |format|
    format.html do
      render partial: 'mini_view', layout: false if request.xhr?
      # Regular HTML requests will render the default show.html.erb template
    end
  end
end

#update ⇒ void

This method returns an undefined value.

Updates a sales commission.



51
52
53
54
55
56
57
58
59
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 51

def update
  load_sales_commission
  if @sales_commission.update(params[:sales_commission])
    flash[:info] = 'Sales Commission Updated'
    redirect_to sales_commissions_path
  else
    render :edit, status: :unprocessable_content
  end
end