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

Constants included from Www::SeoHelper

Www::SeoHelper::AWARDS, Www::SeoHelper::CA_ADDRESS, Www::SeoHelper::CA_BUSINESS_HOURS, Www::SeoHelper::CA_CONTACT_POINT, Www::SeoHelper::CA_CURRENCIES, Www::SeoHelper::CA_DESCRIPTION, Www::SeoHelper::CA_FOUNDING_DATE, Www::SeoHelper::CA_GLOBAL_LOCATION_NUMBER, Www::SeoHelper::CA_LEGAL_NAME, Www::SeoHelper::CA_LOCAL_BUSINESS, Www::SeoHelper::CA_ONLINE_STORE, Www::SeoHelper::CA_RETURN_POLICY, Www::SeoHelper::CA_SALES_DEPARTMENT, Www::SeoHelper::CA_SERVICE_AREA, Www::SeoHelper::CA_URL, Www::SeoHelper::CA_VAT_ID, Www::SeoHelper::CA_WAREHOUSE_DEPARTMENT, Www::SeoHelper::CA_WAREHOUSE_HOURS, Www::SeoHelper::COMPANY_EMAIL, Www::SeoHelper::COMPANY_LOGO, Www::SeoHelper::COMPANY_NAME, Www::SeoHelper::COMPANY_SLOGAN, Www::SeoHelper::EXPERTISE, Www::SeoHelper::FAX_NUMBER, Www::SeoHelper::GS1_COMPANY_PREFIX, Www::SeoHelper::ISO6523_CODE, Www::SeoHelper::PAYMENT_METHODS, Www::SeoHelper::PHONE_NUMBER, Www::SeoHelper::PRIMARY_NAICS, Www::SeoHelper::REFUND_TYPE, Www::SeoHelper::RETURN_FEES, Www::SeoHelper::RETURN_METHOD, Www::SeoHelper::RETURN_POLICY_CATEGORY, Www::SeoHelper::SECONDARY_NAICS, Www::SeoHelper::SOCIAL_PROFILES, Www::SeoHelper::US_ADDRESS, Www::SeoHelper::US_BUSINESS_HOURS, Www::SeoHelper::US_CONTACT_POINT, Www::SeoHelper::US_CURRENCIES, Www::SeoHelper::US_DESCRIPTION, Www::SeoHelper::US_FOUNDING_DATE, Www::SeoHelper::US_GLOBAL_LOCATION_NUMBER, Www::SeoHelper::US_IMAGE, Www::SeoHelper::US_LEGAL_NAME, Www::SeoHelper::US_LOCAL_BUSINESS, Www::SeoHelper::US_ONLINE_STORE, Www::SeoHelper::US_RETURN_POLICY, Www::SeoHelper::US_SALES_DEPARTMENT, Www::SeoHelper::US_SERVICE_AREA, Www::SeoHelper::US_TAX_ID, Www::SeoHelper::US_URL, Www::SeoHelper::US_WAREHOUSE_DEPARTMENT, Www::SeoHelper::US_WAREHOUSE_HOURS

Constants included from IconHelper

IconHelper::CUSTOM_ICON_MAP, IconHelper::CUSTOM_SVG_DIR, IconHelper::DEFAULT_FAMILY

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

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?, #stamp_impersonation_context, #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, #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 ApplicationHelper

#better_number_to_currency, #check_force_logout, #check_or_cross, #check_or_times, #embedded_tab_frame_id, #error_messages, #general_disclaimer_on_product_installation_and_local_codes, #gridjs_from_html_table, #gridjs_table, #is_wy_ip, #line_break, #parent_layout, #pass_or_fail, #render_error_messages_list, #render_video_card, #resolved_auth_form_turbo_frame, #return_path_or, #safe_css_color, #set_return_path_if_present, #set_section_if_present, #tab_frame_id, #to_underscore, #track_page?, #turbo_section_wrapper, #turbo_tabs_request?, #url_on_same_domain_as_request, #widget_index_daily_focus_index_path, #working_hours?, #yes_or_no, #yes_or_no_highlighted, #yes_or_no_with_check_or_cross, #youtube_video

Methods included from UppyUploaderHelper

#file_uploader, #image_uploader, #large_file_uploader_s3, #lead_sketch_uploader, #rma_image_uploader, #rma_image_uploader_s3, #uppy_uploader, #video_uploader

Methods included from Www::ImagesHelper

#image_asset_tag, #image_asset_url

Methods included from Www::SeoHelper

#add_page_schema, #add_webpage_schema, #canada?, #company_social_links, #ensure_context_json, #json_ld_script_tag, #local_business_schema, #online_store_id, #online_store_schema, #page_main_entity, #page_main_entity_json, #render_auto_collection_page_schema, #render_collection_page_schema, #render_local_business_schema, #render_online_store_schema, #render_page_schemas, #render_page_video_schemas, #render_webpage_schema, #render_webpage_schema_with_collections, #usa?

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?

Methods included from IconHelper

#account_nav_icon, #fa_icon, #star_rating_html

Instance Method Details

#charts ⇒ Object 



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

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 ⇒ Object 



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
113
114
115
116
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 85

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 ⇒ Object 



35
36
37
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 35

def edit
  load_sales_commission
end

#index ⇒ Object 



8
9
10
11
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 8

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

#load_commission_percentage(year) ⇒ Object 



193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 193

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 ⇒ Object 



247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 247

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 ⇒ Object 



168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 168

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



142
143
144
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 142

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

#load_sales_commission_by_channel ⇒ Object 



209
210
211
212
213
214
215
216
217
218
219
220
221
222
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 209

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 ⇒ Object 



224
225
226
227
228
229
230
231
232
233
234
235
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 224

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 ⇒ Object 



152
153
154
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 152

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 ⇒ Object 



156
157
158
159
160
161
162
163
164
165
166
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 156

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 ⇒ Object 



146
147
148
149
150
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 146

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 ⇒ Object 



237
238
239
240
241
242
243
244
245
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 237

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 ⇒ Object 



79
80
81
82
83
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 79

def remove_orders
  load_sales_commission
  load_sales_commission_net_base_breakdown
  load_rate
end

#show ⇒ Object 



13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 13

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 ⇒ Object 



39
40
41
42
43
44
45
46
47
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 39

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