Class: Crm::SalesCommissionsController

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

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, #initialize_crm_lazy_chunks, #record_not_found, #redirect_to_job_or_fallback, #render_edit_action, #set_context, #set_download_path, #stash_file_for_temp_download

Methods inherited from ApplicationController

#account_impersonated?, #add_to_flash, #append_token, #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::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, #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_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!, #authenticate_account_from_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, #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, #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 



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

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 



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

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 



33
34
35
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 33

def edit
  load_sales_commission
end

#index ⇒ Object 



6
7
8
9
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 6

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

#load_commission_percentage(year) ⇒ Object 



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

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.to_s)

  sql = <<-SQL
    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.connection.execute(sql).to_a.map { |r| r.symbolize_keys }
end

#load_progression_data ⇒ Object 



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

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
    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.connection.execute(sql).to_a.map { |r| r.symbolize_keys }
end

#load_rate ⇒ Object 



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

def load_rate
  date = @sales_commission.commission_date
  sql = <<-SQL
    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.connection.execute(sql).to_a.map { |r| r.symbolize_keys }

  if @rates.empty? || @rates[0][:usd_to_cad].to_f.zero?
    fallback_sql = <<-SQL
      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.connection.execute(fallback_sql).to_a.map { |r| r.symbolize_keys }
  end
end

#load_sales_commission ⇒ Object

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



140
141
142
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 140

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

#load_sales_commission_by_channel ⇒ Object 



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

def load_sales_commission_by_channel
  sc_id = @sales_commission.id
  sql = <<-SQL
    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.connection.execute(sql).to_a.map { |r| r.symbolize_keys }
end

#load_sales_commission_by_channel_breakdown ⇒ Object 



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

def load_sales_commission_by_channel_breakdown
  sc_id = @sales_commission.id
  sql = <<-SQL
    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.connection.execute(sql).to_a.map { |r| r.symbolize_keys }
end

#load_sales_commission_net_base ⇒ Object 



150
151
152
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 150

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 



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

def load_sales_commission_net_base_breakdown
  sc_id = @sales_commission.id
  sql = <<-SQL
    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.connection.execute(sql).to_a.map { |r| r.symbolize_keys }
end

#load_sales_commissions ⇒ Object 



144
145
146
147
148
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 144

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 



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

def load_type_of_user
  sql = <<-SQL
    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.connection.execute(sql).to_a.map { |r| r.symbolize_keys }
end

#remove_orders ⇒ Object 



77
78
79
80
81
 
# File 'app/controllers/crm/sales_commissions_controller.rb', line 77

def remove_orders
  load_sales_commission
  load_sales_commission_net_base_breakdown
  load_rate
end

#show ⇒ Object 



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

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 



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

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_entity
  end
end