Class: Employee

Inherits:
ApplicationRecord show all
Includes:
EmployeesProcedures, HasAddress
Defined in:
app/models/employee.rb

Overview

Represents an employee in the system.

Constant Summary collapse

PAY_COLUMNS = 
%w[
  accepts_direct_deposit
  provincial_tax_category
  federal_tax_category
  vacation_percentage
  health_insurance_amount
  dental_insurance_amount
  rrsp_amount
  long_term_disability_insurance_amount
  life_insurance_amount
  additional_federal_taxes_retained
  additional_provincial_taxes_retained
  serious_illness_amount
  cotisation_syndicale_amount
  employer_health_insurance_contribution_amount
  employer_dental_insurance_contribution_amount
  employer_life_insurance_contribution_amount
].freeze
MINIMUM_AGE = 
15
ENCRYPTED_FIELDS = 
%w[
  employee_name
  ssn
  phone_no
  business_email
  google_address
  address_line1
  address_line2
  city
  personal_email
  mobile_phone_no
  fax_no
  postal_code
  date_of_birth
].freeze

Constants included from EmployeesProcedures

EmployeesProcedures::HEADERS

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from EmployeesProcedures

#write_procedure

Methods included from HasAddress

#address, #comma_separated_address, #line_separated_address, #space_separated_address

Methods inherited from ApplicationRecord

define_decrypted_attribute, define_decrypted_attributes, #errors_in_bullet_points, primary_connection, special_connection

Instance Attribute Details

#address_line1 ⇒ String

Returns the first line of the address of the employee.

Returns:

  • (String) —

    the first line of the address of the employee



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
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
102
103
104
105
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
292
293
294
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
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
 
# File 'app/models/employee.rb', line 25

class Employee < ApplicationRecord
  include HasAddress
  include EmployeesProcedures

  PAY_COLUMNS = %w[
    accepts_direct_deposit
    provincial_tax_category
    federal_tax_category
    vacation_percentage
    health_insurance_amount
    dental_insurance_amount
    rrsp_amount
    long_term_disability_insurance_amount
    life_insurance_amount
    additional_federal_taxes_retained
    additional_provincial_taxes_retained
    serious_illness_amount
    cotisation_syndicale_amount
    employer_health_insurance_contribution_amount
    employer_dental_insurance_contribution_amount
    employer_life_insurance_contribution_amount
  ].freeze

  MINIMUM_AGE = 15

  self.table_name = 'employees'
  self.primary_key = 'employee_id'

  ENCRYPTED_FIELDS = %w[
    employee_name
    ssn
    phone_no
    business_email
    google_address
    address_line1
    address_line2
    city
    personal_email
    mobile_phone_no
    fax_no
    postal_code
    date_of_birth
  ].freeze

  define_decrypted_attributes(ENCRYPTED_FIELDS)

  attr_accessor :bank_account, :employee_role, :province_code

  belongs_to :company
  belongs_to :erp_user, optional: true
  belongs_to :province

  has_many :employee_timesheets, dependent: :destroy
  has_many :employee_payroll_transactions, dependent: :destroy
  has_many :employee_roles, dependent: :destroy
  has_many :employee_payroll_calculations, dependent: :destroy
  has_many :integration_references, as: :reference, dependent: :destroy
  has_many :reference_events, as: :reference, dependent: nil

  validates :employee_name, presence: true, length: { maximum: 50 },
                            format: {
                              with: /\A[a-zA-Z\s\-.]+\z/
                              # message: I18n.t('payroll.employees.form.ssn_validation')
                            }
  validates :date_of_birth, presence: true
  validates :start_date, presence: false
  validates :vacation_percentage, numericality: { greater_than_or_equal_to: 0, less_than_or_equal_to: 100 },
                                  presence: false
  validates :health_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :dental_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :cotisation_syndicale_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :child_support_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :rrsp_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :long_term_disability_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :life_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :business_email, uniqueness: { scope: :company_id }, allow_blank: true
  validates :personal_email, uniqueness: { scope: :company_id }, allow_blank: false
  validates :phone_no, uniqueness: { scope: :company_id }, allow_blank: true
  validates :default_language, inclusion: { in: %w[E F], message: "must be 'E' or 'F'" }

  validate :validate_tax_categories
  validate :validate_dates
  validate :termination_reason_is_with_termination_date
  validate :validate_ssn
  validate :validate_erp_user_id, if: :erp_user_id_changed?

  validates :address_line1, presence: true, length: { maximum: 300 }
  validates :address_line2, presence: false, length: { maximum: 300 }
  validates :city, presence: true, length: { maximum: 300 }
  validates :postal_code, presence: true, length: { maximum: 7 },
                          format: { with: /\A[A-Za-z]\d[A-Za-z] \d[A-Za-z]\d\z/, message: 'must be in the format A1A 1A1' }
  validates :federal_tax_category, presence: true
  validates :provincial_tax_category, presence: true

  scope :without_timesheets_for_period, lambda { |payroll_period_id|
    joins('LEFT JOIN employee_time_sheets ON employees.employee_id = employee_time_sheets.employee_id')
      .where(
        'employee_time_sheets.payroll_period_id IS NULL OR employee_time_sheets.payroll_period_id != ?',
        payroll_period_id
      )
      .distinct
  }

  def name
    employee_name
  end

  # Checks if any of the pay columns have changed.
  #
  # @return [Boolean] true if any pay columns have changed, false otherwise
  def pay_columns_changed?
    changes.keys.any? { |column| PAY_COLUMNS.include?(column) }
  end

  # Counts the number of timesheets for a specific payroll period.
  #
  # @param payroll_period_id [Integer] the ID of the payroll period
  # @return [Integer] the number of timesheets for the specified payroll period
  def timesheets_count_for_period(payroll_period_id)
    employee_timesheets.where(payroll_period_id: payroll_period_id).count
  end

  # Returns a description of the employee's roles.
  #
  # @return [String] a comma-separated list of role descriptions
  def role_description
    employee_roles.select(:description).map(&:description).join(', ')
  end

  class << self
    # Retrieves employee options using a stored procedure.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_procedure(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_procedure(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      result_type = if must_have_timesheets
                      '01'
                    else
                      '01' # should be fixed
                    end
      ActiveRecord::Base.execute_procedure(
        'pr_get_employees_for_dropdown',
        nil, # employee_id
        payroll_period_id,
        company_id,
        nil, # payroll_transaction_header_id
        result_type
      )
    end

    # Retrieves employee options using a SQL query.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_sql(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_sql(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      sql = <<-SQL.squish
        OPEN SYMMETRIC KEY PaieHubSymmetricKey DECRYPTION BY CERTIFICATE PaieHubCertificate;
        SELECT
          employees.employee_id,
          CONVERT(NVARCHAR(300), DecryptByKey(employees.[employee_name])) AS employee_name,
          COUNT(employee_time_sheets.employee_id) AS timesheets_count
        FROM
          employees
        LEFT JOIN
          employee_time_sheets
          ON employees.employee_id = employee_time_sheets.employee_id
        WHERE
          employees.company_id = ?
      SQL

      if payroll_period_id.positive?
        sql += <<-SQL.squish
          AND employee_time_sheets.payroll_period_id = ?
        SQL
      end

      sql += <<-SQL.squish
        GROUP BY
          employees.employee_id,
          employee_name
      SQL

      if must_have_timesheets
        sql += <<-SQL.squish
          HAVING COUNT(employee_time_sheets.employee_id) > 0
        SQL
      end

      sql += <<-SQL.squish
        ORDER BY
          timesheets_count DESC
      SQL

      sanitized_query = if payroll_period_id.positive?
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id, payroll_period_id])
                        else
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id])
                        end

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a
    end

    # Validates the format of a social security number (SSN).
    #
    # @param ssn [String] the social security number to validate
    # @return [Boolean] true if the SSN is valid, false otherwise
    # @example
    #   Employee.ssn_valid?('123-45-6789')
    def ssn_valid?(ssn)
      ssn = ssn.to_s.strip
      return false if ssn.blank?
      return false unless ssn.match?(/^\d{9}$|^\d{3}-\d{3}-\d{3}$/)

      digits = ssn.scan(/\d/).join

      sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT dbo.fu_validate_ssn(?) as value', digits])

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a.dig(0, 'value')
    end
  end

  # Returns the bank accounts associated with the employee.
  #
  # @return [ActiveRecord::Relation] the bank accounts associated with the employee
  def bank_accounts
    BankAccount.where(
      source: 'Employee',
      reference_id: employee_id
    )
  end

  # Returns the default bank account of the employee.
  #
  # @return [BankAccount, nil] the default bank account of the employee, or nil if none exists
  def 
    bank_accounts.find_by(is_default: true)
  end

  # Returns the locale of the employee.
  #
  # @return [String] the locale of the employee
  def locale
    Language.new(default_language).locale
  end

  def formatted_start_date
    I18n.l(start_date, format: :long) if start_date
  end

  def url
    "#{AppMode.payroll.web_uri}/app/payroll/employees/#{employee_id}"
  end

  # Validates the social security number (SSN) of the employee.
  #
  # @return [Boolean] true if the SSN is valid, false otherwise
  def validate_ssn
    validity = Employee.ssn_valid?(ssn)
    errors.add(:ssn, I18n.t('general.is_invalid')) unless validity
    validity
  end

  # Returns the date of birth of the employee.
  # This should be replaced once the decyrption there is fixed
  #
  # @return [Date] the date of birth of the employee
  def date_of_birth
    self.class.find_employee_procedure(company_id: company_id, employee_id: employee_id,
                                       active: '03')['date_of_birth'] || super
  rescue StandardError
    super
  end

  private

  # Validates that the termination reason is present if the termination date is present.
  #
  # @return [void]
  def termination_reason_is_with_termination_date
    return unless termination_date.blank? && termination_reason.present?

    errors.add(:termination_reason, "can't be present without termination date")
  end

  # Validates the federal and provincial tax categories.
  #
  # @return [void]
  def validate_tax_categories
    return if federal_tax_category.blank? && provincial_tax_category.blank?

    unless TypesMaster.federal_tax_codes.exists?(type: federal_tax_category)
      errors.add(:federal_tax_category, 'is not a valid federal tax category')
    end

    return if TypesMaster.provincial_tax_codes.exists?(type: provincial_tax_category)

    errors.add(:provincial_tax_category, 'is not a valid provincial tax category')
  end

  # Validates the dates related to the employee.
  #
  # @return [void]
  def validate_dates
    today = Time.zone.today.beginning_of_day

    date_of_birth = parse_date_if_needed(self.date_of_birth)
    if date_of_birth.present? && date_of_birth.beginning_of_day > today - MINIMUM_AGE.years
      errors.add(:date_of_birth, "must be at least #{MINIMUM_AGE} years old")
    end

    start_date = parse_date_if_needed(self.start_date)
    errors.add(:start_date, "can't be in the future") if start_date.present? && start_date.beginning_of_day > today

    termination_date = parse_date_if_needed(self.termination_date)
    return if termination_date.blank?

    termination_date_only = termination_date.beginning_of_day

    if start_date.present? && termination_date_only < start_date.beginning_of_day
      errors.add(:termination_date, "can't be before start date")
    end

    if date_of_birth.present? && termination_date_only < date_of_birth.beginning_of_day
      errors.add(:termination_date, "can't be before date of birth")
    end

    errors.add(:termination_date, "can't be in the future") if termination_date_only > today

    return if termination_reason.present?

    errors.add(:termination_reason, "can't be blank")
  rescue StandardError => e
    errors.add(:base, e.message)
  end

  # Returns the email of the employee for the employee portal login.
  # It is the email of the ERP user associated with the employee.
  #
  # @return [String] the email of the employee for the employee portal login
  def 
    erp_user&.email
  end

  # Returns whether the employee portal login is enabled for the employee.
  # It is enabled if the ERP user associated with the employee is active.
  #
  # @return [Boolean] true if the employee portal login is enabled, false otherwise
  def 
    erp_user&.active
  end

  def validate_erp_user_id
    user_with_email = ErpUser.accessible.find_by(email: )
    return unless user_with_email

    user_has_other_employees_for_company = user_with_email.employees.where(
      company_id: company_id
    ).where.not(
      employee_id: employee_id
    ).exists?
    return unless user_has_other_employees_for_company

    errors.add(:employee_portal_login_email, 'is already in use by another employee in this company')
  end

  # Parses a date if it is a string.
  #
  # @param date [String, Date, DateTime] the date to parse
  # @return [DateTime, nil] the parsed date, or nil if the date is not present
  def parse_date_if_needed(date)
    if date.is_a?(String)
      DateTime.parse(date)
    else
      date
    end
  end
end

#address_line2 ⇒ String

Returns the second line of the address of the employee.

Returns:

  • (String) —

    the second line of the address of the employee



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
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
102
103
104
105
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
292
293
294
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
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
 
# File 'app/models/employee.rb', line 25

class Employee < ApplicationRecord
  include HasAddress
  include EmployeesProcedures

  PAY_COLUMNS = %w[
    accepts_direct_deposit
    provincial_tax_category
    federal_tax_category
    vacation_percentage
    health_insurance_amount
    dental_insurance_amount
    rrsp_amount
    long_term_disability_insurance_amount
    life_insurance_amount
    additional_federal_taxes_retained
    additional_provincial_taxes_retained
    serious_illness_amount
    cotisation_syndicale_amount
    employer_health_insurance_contribution_amount
    employer_dental_insurance_contribution_amount
    employer_life_insurance_contribution_amount
  ].freeze

  MINIMUM_AGE = 15

  self.table_name = 'employees'
  self.primary_key = 'employee_id'

  ENCRYPTED_FIELDS = %w[
    employee_name
    ssn
    phone_no
    business_email
    google_address
    address_line1
    address_line2
    city
    personal_email
    mobile_phone_no
    fax_no
    postal_code
    date_of_birth
  ].freeze

  define_decrypted_attributes(ENCRYPTED_FIELDS)

  attr_accessor :bank_account, :employee_role, :province_code

  belongs_to :company
  belongs_to :erp_user, optional: true
  belongs_to :province

  has_many :employee_timesheets, dependent: :destroy
  has_many :employee_payroll_transactions, dependent: :destroy
  has_many :employee_roles, dependent: :destroy
  has_many :employee_payroll_calculations, dependent: :destroy
  has_many :integration_references, as: :reference, dependent: :destroy
  has_many :reference_events, as: :reference, dependent: nil

  validates :employee_name, presence: true, length: { maximum: 50 },
                            format: {
                              with: /\A[a-zA-Z\s\-.]+\z/
                              # message: I18n.t('payroll.employees.form.ssn_validation')
                            }
  validates :date_of_birth, presence: true
  validates :start_date, presence: false
  validates :vacation_percentage, numericality: { greater_than_or_equal_to: 0, less_than_or_equal_to: 100 },
                                  presence: false
  validates :health_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :dental_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :cotisation_syndicale_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :child_support_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :rrsp_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :long_term_disability_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :life_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :business_email, uniqueness: { scope: :company_id }, allow_blank: true
  validates :personal_email, uniqueness: { scope: :company_id }, allow_blank: false
  validates :phone_no, uniqueness: { scope: :company_id }, allow_blank: true
  validates :default_language, inclusion: { in: %w[E F], message: "must be 'E' or 'F'" }

  validate :validate_tax_categories
  validate :validate_dates
  validate :termination_reason_is_with_termination_date
  validate :validate_ssn
  validate :validate_erp_user_id, if: :erp_user_id_changed?

  validates :address_line1, presence: true, length: { maximum: 300 }
  validates :address_line2, presence: false, length: { maximum: 300 }
  validates :city, presence: true, length: { maximum: 300 }
  validates :postal_code, presence: true, length: { maximum: 7 },
                          format: { with: /\A[A-Za-z]\d[A-Za-z] \d[A-Za-z]\d\z/, message: 'must be in the format A1A 1A1' }
  validates :federal_tax_category, presence: true
  validates :provincial_tax_category, presence: true

  scope :without_timesheets_for_period, lambda { |payroll_period_id|
    joins('LEFT JOIN employee_time_sheets ON employees.employee_id = employee_time_sheets.employee_id')
      .where(
        'employee_time_sheets.payroll_period_id IS NULL OR employee_time_sheets.payroll_period_id != ?',
        payroll_period_id
      )
      .distinct
  }

  def name
    employee_name
  end

  # Checks if any of the pay columns have changed.
  #
  # @return [Boolean] true if any pay columns have changed, false otherwise
  def pay_columns_changed?
    changes.keys.any? { |column| PAY_COLUMNS.include?(column) }
  end

  # Counts the number of timesheets for a specific payroll period.
  #
  # @param payroll_period_id [Integer] the ID of the payroll period
  # @return [Integer] the number of timesheets for the specified payroll period
  def timesheets_count_for_period(payroll_period_id)
    employee_timesheets.where(payroll_period_id: payroll_period_id).count
  end

  # Returns a description of the employee's roles.
  #
  # @return [String] a comma-separated list of role descriptions
  def role_description
    employee_roles.select(:description).map(&:description).join(', ')
  end

  class << self
    # Retrieves employee options using a stored procedure.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_procedure(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_procedure(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      result_type = if must_have_timesheets
                      '01'
                    else
                      '01' # should be fixed
                    end
      ActiveRecord::Base.execute_procedure(
        'pr_get_employees_for_dropdown',
        nil, # employee_id
        payroll_period_id,
        company_id,
        nil, # payroll_transaction_header_id
        result_type
      )
    end

    # Retrieves employee options using a SQL query.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_sql(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_sql(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      sql = <<-SQL.squish
        OPEN SYMMETRIC KEY PaieHubSymmetricKey DECRYPTION BY CERTIFICATE PaieHubCertificate;
        SELECT
          employees.employee_id,
          CONVERT(NVARCHAR(300), DecryptByKey(employees.[employee_name])) AS employee_name,
          COUNT(employee_time_sheets.employee_id) AS timesheets_count
        FROM
          employees
        LEFT JOIN
          employee_time_sheets
          ON employees.employee_id = employee_time_sheets.employee_id
        WHERE
          employees.company_id = ?
      SQL

      if payroll_period_id.positive?
        sql += <<-SQL.squish
          AND employee_time_sheets.payroll_period_id = ?
        SQL
      end

      sql += <<-SQL.squish
        GROUP BY
          employees.employee_id,
          employee_name
      SQL

      if must_have_timesheets
        sql += <<-SQL.squish
          HAVING COUNT(employee_time_sheets.employee_id) > 0
        SQL
      end

      sql += <<-SQL.squish
        ORDER BY
          timesheets_count DESC
      SQL

      sanitized_query = if payroll_period_id.positive?
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id, payroll_period_id])
                        else
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id])
                        end

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a
    end

    # Validates the format of a social security number (SSN).
    #
    # @param ssn [String] the social security number to validate
    # @return [Boolean] true if the SSN is valid, false otherwise
    # @example
    #   Employee.ssn_valid?('123-45-6789')
    def ssn_valid?(ssn)
      ssn = ssn.to_s.strip
      return false if ssn.blank?
      return false unless ssn.match?(/^\d{9}$|^\d{3}-\d{3}-\d{3}$/)

      digits = ssn.scan(/\d/).join

      sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT dbo.fu_validate_ssn(?) as value', digits])

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a.dig(0, 'value')
    end
  end

  # Returns the bank accounts associated with the employee.
  #
  # @return [ActiveRecord::Relation] the bank accounts associated with the employee
  def bank_accounts
    BankAccount.where(
      source: 'Employee',
      reference_id: employee_id
    )
  end

  # Returns the default bank account of the employee.
  #
  # @return [BankAccount, nil] the default bank account of the employee, or nil if none exists
  def 
    bank_accounts.find_by(is_default: true)
  end

  # Returns the locale of the employee.
  #
  # @return [String] the locale of the employee
  def locale
    Language.new(default_language).locale
  end

  def formatted_start_date
    I18n.l(start_date, format: :long) if start_date
  end

  def url
    "#{AppMode.payroll.web_uri}/app/payroll/employees/#{employee_id}"
  end

  # Validates the social security number (SSN) of the employee.
  #
  # @return [Boolean] true if the SSN is valid, false otherwise
  def validate_ssn
    validity = Employee.ssn_valid?(ssn)
    errors.add(:ssn, I18n.t('general.is_invalid')) unless validity
    validity
  end

  # Returns the date of birth of the employee.
  # This should be replaced once the decyrption there is fixed
  #
  # @return [Date] the date of birth of the employee
  def date_of_birth
    self.class.find_employee_procedure(company_id: company_id, employee_id: employee_id,
                                       active: '03')['date_of_birth'] || super
  rescue StandardError
    super
  end

  private

  # Validates that the termination reason is present if the termination date is present.
  #
  # @return [void]
  def termination_reason_is_with_termination_date
    return unless termination_date.blank? && termination_reason.present?

    errors.add(:termination_reason, "can't be present without termination date")
  end

  # Validates the federal and provincial tax categories.
  #
  # @return [void]
  def validate_tax_categories
    return if federal_tax_category.blank? && provincial_tax_category.blank?

    unless TypesMaster.federal_tax_codes.exists?(type: federal_tax_category)
      errors.add(:federal_tax_category, 'is not a valid federal tax category')
    end

    return if TypesMaster.provincial_tax_codes.exists?(type: provincial_tax_category)

    errors.add(:provincial_tax_category, 'is not a valid provincial tax category')
  end

  # Validates the dates related to the employee.
  #
  # @return [void]
  def validate_dates
    today = Time.zone.today.beginning_of_day

    date_of_birth = parse_date_if_needed(self.date_of_birth)
    if date_of_birth.present? && date_of_birth.beginning_of_day > today - MINIMUM_AGE.years
      errors.add(:date_of_birth, "must be at least #{MINIMUM_AGE} years old")
    end

    start_date = parse_date_if_needed(self.start_date)
    errors.add(:start_date, "can't be in the future") if start_date.present? && start_date.beginning_of_day > today

    termination_date = parse_date_if_needed(self.termination_date)
    return if termination_date.blank?

    termination_date_only = termination_date.beginning_of_day

    if start_date.present? && termination_date_only < start_date.beginning_of_day
      errors.add(:termination_date, "can't be before start date")
    end

    if date_of_birth.present? && termination_date_only < date_of_birth.beginning_of_day
      errors.add(:termination_date, "can't be before date of birth")
    end

    errors.add(:termination_date, "can't be in the future") if termination_date_only > today

    return if termination_reason.present?

    errors.add(:termination_reason, "can't be blank")
  rescue StandardError => e
    errors.add(:base, e.message)
  end

  # Returns the email of the employee for the employee portal login.
  # It is the email of the ERP user associated with the employee.
  #
  # @return [String] the email of the employee for the employee portal login
  def 
    erp_user&.email
  end

  # Returns whether the employee portal login is enabled for the employee.
  # It is enabled if the ERP user associated with the employee is active.
  #
  # @return [Boolean] true if the employee portal login is enabled, false otherwise
  def 
    erp_user&.active
  end

  def validate_erp_user_id
    user_with_email = ErpUser.accessible.find_by(email: )
    return unless user_with_email

    user_has_other_employees_for_company = user_with_email.employees.where(
      company_id: company_id
    ).where.not(
      employee_id: employee_id
    ).exists?
    return unless user_has_other_employees_for_company

    errors.add(:employee_portal_login_email, 'is already in use by another employee in this company')
  end

  # Parses a date if it is a string.
  #
  # @param date [String, Date, DateTime] the date to parse
  # @return [DateTime, nil] the parsed date, or nil if the date is not present
  def parse_date_if_needed(date)
    if date.is_a?(String)
      DateTime.parse(date)
    else
      date
    end
  end
end

#bank_account ⇒ Object

Returns the value of attribute bank_account.



71
72
73
 
# File 'app/models/employee.rb', line 71

def 
  @bank_account
end

#business_email ⇒ String

Returns the business email of the employee.

Returns:

  • (String) —

    the business email of the employee



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
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
102
103
104
105
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
292
293
294
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
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
 
# File 'app/models/employee.rb', line 25

class Employee < ApplicationRecord
  include HasAddress
  include EmployeesProcedures

  PAY_COLUMNS = %w[
    accepts_direct_deposit
    provincial_tax_category
    federal_tax_category
    vacation_percentage
    health_insurance_amount
    dental_insurance_amount
    rrsp_amount
    long_term_disability_insurance_amount
    life_insurance_amount
    additional_federal_taxes_retained
    additional_provincial_taxes_retained
    serious_illness_amount
    cotisation_syndicale_amount
    employer_health_insurance_contribution_amount
    employer_dental_insurance_contribution_amount
    employer_life_insurance_contribution_amount
  ].freeze

  MINIMUM_AGE = 15

  self.table_name = 'employees'
  self.primary_key = 'employee_id'

  ENCRYPTED_FIELDS = %w[
    employee_name
    ssn
    phone_no
    business_email
    google_address
    address_line1
    address_line2
    city
    personal_email
    mobile_phone_no
    fax_no
    postal_code
    date_of_birth
  ].freeze

  define_decrypted_attributes(ENCRYPTED_FIELDS)

  attr_accessor :bank_account, :employee_role, :province_code

  belongs_to :company
  belongs_to :erp_user, optional: true
  belongs_to :province

  has_many :employee_timesheets, dependent: :destroy
  has_many :employee_payroll_transactions, dependent: :destroy
  has_many :employee_roles, dependent: :destroy
  has_many :employee_payroll_calculations, dependent: :destroy
  has_many :integration_references, as: :reference, dependent: :destroy
  has_many :reference_events, as: :reference, dependent: nil

  validates :employee_name, presence: true, length: { maximum: 50 },
                            format: {
                              with: /\A[a-zA-Z\s\-.]+\z/
                              # message: I18n.t('payroll.employees.form.ssn_validation')
                            }
  validates :date_of_birth, presence: true
  validates :start_date, presence: false
  validates :vacation_percentage, numericality: { greater_than_or_equal_to: 0, less_than_or_equal_to: 100 },
                                  presence: false
  validates :health_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :dental_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :cotisation_syndicale_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :child_support_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :rrsp_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :long_term_disability_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :life_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :business_email, uniqueness: { scope: :company_id }, allow_blank: true
  validates :personal_email, uniqueness: { scope: :company_id }, allow_blank: false
  validates :phone_no, uniqueness: { scope: :company_id }, allow_blank: true
  validates :default_language, inclusion: { in: %w[E F], message: "must be 'E' or 'F'" }

  validate :validate_tax_categories
  validate :validate_dates
  validate :termination_reason_is_with_termination_date
  validate :validate_ssn
  validate :validate_erp_user_id, if: :erp_user_id_changed?

  validates :address_line1, presence: true, length: { maximum: 300 }
  validates :address_line2, presence: false, length: { maximum: 300 }
  validates :city, presence: true, length: { maximum: 300 }
  validates :postal_code, presence: true, length: { maximum: 7 },
                          format: { with: /\A[A-Za-z]\d[A-Za-z] \d[A-Za-z]\d\z/, message: 'must be in the format A1A 1A1' }
  validates :federal_tax_category, presence: true
  validates :provincial_tax_category, presence: true

  scope :without_timesheets_for_period, lambda { |payroll_period_id|
    joins('LEFT JOIN employee_time_sheets ON employees.employee_id = employee_time_sheets.employee_id')
      .where(
        'employee_time_sheets.payroll_period_id IS NULL OR employee_time_sheets.payroll_period_id != ?',
        payroll_period_id
      )
      .distinct
  }

  def name
    employee_name
  end

  # Checks if any of the pay columns have changed.
  #
  # @return [Boolean] true if any pay columns have changed, false otherwise
  def pay_columns_changed?
    changes.keys.any? { |column| PAY_COLUMNS.include?(column) }
  end

  # Counts the number of timesheets for a specific payroll period.
  #
  # @param payroll_period_id [Integer] the ID of the payroll period
  # @return [Integer] the number of timesheets for the specified payroll period
  def timesheets_count_for_period(payroll_period_id)
    employee_timesheets.where(payroll_period_id: payroll_period_id).count
  end

  # Returns a description of the employee's roles.
  #
  # @return [String] a comma-separated list of role descriptions
  def role_description
    employee_roles.select(:description).map(&:description).join(', ')
  end

  class << self
    # Retrieves employee options using a stored procedure.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_procedure(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_procedure(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      result_type = if must_have_timesheets
                      '01'
                    else
                      '01' # should be fixed
                    end
      ActiveRecord::Base.execute_procedure(
        'pr_get_employees_for_dropdown',
        nil, # employee_id
        payroll_period_id,
        company_id,
        nil, # payroll_transaction_header_id
        result_type
      )
    end

    # Retrieves employee options using a SQL query.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_sql(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_sql(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      sql = <<-SQL.squish
        OPEN SYMMETRIC KEY PaieHubSymmetricKey DECRYPTION BY CERTIFICATE PaieHubCertificate;
        SELECT
          employees.employee_id,
          CONVERT(NVARCHAR(300), DecryptByKey(employees.[employee_name])) AS employee_name,
          COUNT(employee_time_sheets.employee_id) AS timesheets_count
        FROM
          employees
        LEFT JOIN
          employee_time_sheets
          ON employees.employee_id = employee_time_sheets.employee_id
        WHERE
          employees.company_id = ?
      SQL

      if payroll_period_id.positive?
        sql += <<-SQL.squish
          AND employee_time_sheets.payroll_period_id = ?
        SQL
      end

      sql += <<-SQL.squish
        GROUP BY
          employees.employee_id,
          employee_name
      SQL

      if must_have_timesheets
        sql += <<-SQL.squish
          HAVING COUNT(employee_time_sheets.employee_id) > 0
        SQL
      end

      sql += <<-SQL.squish
        ORDER BY
          timesheets_count DESC
      SQL

      sanitized_query = if payroll_period_id.positive?
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id, payroll_period_id])
                        else
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id])
                        end

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a
    end

    # Validates the format of a social security number (SSN).
    #
    # @param ssn [String] the social security number to validate
    # @return [Boolean] true if the SSN is valid, false otherwise
    # @example
    #   Employee.ssn_valid?('123-45-6789')
    def ssn_valid?(ssn)
      ssn = ssn.to_s.strip
      return false if ssn.blank?
      return false unless ssn.match?(/^\d{9}$|^\d{3}-\d{3}-\d{3}$/)

      digits = ssn.scan(/\d/).join

      sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT dbo.fu_validate_ssn(?) as value', digits])

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a.dig(0, 'value')
    end
  end

  # Returns the bank accounts associated with the employee.
  #
  # @return [ActiveRecord::Relation] the bank accounts associated with the employee
  def bank_accounts
    BankAccount.where(
      source: 'Employee',
      reference_id: employee_id
    )
  end

  # Returns the default bank account of the employee.
  #
  # @return [BankAccount, nil] the default bank account of the employee, or nil if none exists
  def 
    bank_accounts.find_by(is_default: true)
  end

  # Returns the locale of the employee.
  #
  # @return [String] the locale of the employee
  def locale
    Language.new(default_language).locale
  end

  def formatted_start_date
    I18n.l(start_date, format: :long) if start_date
  end

  def url
    "#{AppMode.payroll.web_uri}/app/payroll/employees/#{employee_id}"
  end

  # Validates the social security number (SSN) of the employee.
  #
  # @return [Boolean] true if the SSN is valid, false otherwise
  def validate_ssn
    validity = Employee.ssn_valid?(ssn)
    errors.add(:ssn, I18n.t('general.is_invalid')) unless validity
    validity
  end

  # Returns the date of birth of the employee.
  # This should be replaced once the decyrption there is fixed
  #
  # @return [Date] the date of birth of the employee
  def date_of_birth
    self.class.find_employee_procedure(company_id: company_id, employee_id: employee_id,
                                       active: '03')['date_of_birth'] || super
  rescue StandardError
    super
  end

  private

  # Validates that the termination reason is present if the termination date is present.
  #
  # @return [void]
  def termination_reason_is_with_termination_date
    return unless termination_date.blank? && termination_reason.present?

    errors.add(:termination_reason, "can't be present without termination date")
  end

  # Validates the federal and provincial tax categories.
  #
  # @return [void]
  def validate_tax_categories
    return if federal_tax_category.blank? && provincial_tax_category.blank?

    unless TypesMaster.federal_tax_codes.exists?(type: federal_tax_category)
      errors.add(:federal_tax_category, 'is not a valid federal tax category')
    end

    return if TypesMaster.provincial_tax_codes.exists?(type: provincial_tax_category)

    errors.add(:provincial_tax_category, 'is not a valid provincial tax category')
  end

  # Validates the dates related to the employee.
  #
  # @return [void]
  def validate_dates
    today = Time.zone.today.beginning_of_day

    date_of_birth = parse_date_if_needed(self.date_of_birth)
    if date_of_birth.present? && date_of_birth.beginning_of_day > today - MINIMUM_AGE.years
      errors.add(:date_of_birth, "must be at least #{MINIMUM_AGE} years old")
    end

    start_date = parse_date_if_needed(self.start_date)
    errors.add(:start_date, "can't be in the future") if start_date.present? && start_date.beginning_of_day > today

    termination_date = parse_date_if_needed(self.termination_date)
    return if termination_date.blank?

    termination_date_only = termination_date.beginning_of_day

    if start_date.present? && termination_date_only < start_date.beginning_of_day
      errors.add(:termination_date, "can't be before start date")
    end

    if date_of_birth.present? && termination_date_only < date_of_birth.beginning_of_day
      errors.add(:termination_date, "can't be before date of birth")
    end

    errors.add(:termination_date, "can't be in the future") if termination_date_only > today

    return if termination_reason.present?

    errors.add(:termination_reason, "can't be blank")
  rescue StandardError => e
    errors.add(:base, e.message)
  end

  # Returns the email of the employee for the employee portal login.
  # It is the email of the ERP user associated with the employee.
  #
  # @return [String] the email of the employee for the employee portal login
  def 
    erp_user&.email
  end

  # Returns whether the employee portal login is enabled for the employee.
  # It is enabled if the ERP user associated with the employee is active.
  #
  # @return [Boolean] true if the employee portal login is enabled, false otherwise
  def 
    erp_user&.active
  end

  def validate_erp_user_id
    user_with_email = ErpUser.accessible.find_by(email: )
    return unless user_with_email

    user_has_other_employees_for_company = user_with_email.employees.where(
      company_id: company_id
    ).where.not(
      employee_id: employee_id
    ).exists?
    return unless user_has_other_employees_for_company

    errors.add(:employee_portal_login_email, 'is already in use by another employee in this company')
  end

  # Parses a date if it is a string.
  #
  # @param date [String, Date, DateTime] the date to parse
  # @return [DateTime, nil] the parsed date, or nil if the date is not present
  def parse_date_if_needed(date)
    if date.is_a?(String)
      DateTime.parse(date)
    else
      date
    end
  end
end

#city ⇒ String

Returns the city of the employee.

Returns:

  • (String) —

    the city of the employee



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
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
102
103
104
105
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
292
293
294
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
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
 
# File 'app/models/employee.rb', line 25

class Employee < ApplicationRecord
  include HasAddress
  include EmployeesProcedures

  PAY_COLUMNS = %w[
    accepts_direct_deposit
    provincial_tax_category
    federal_tax_category
    vacation_percentage
    health_insurance_amount
    dental_insurance_amount
    rrsp_amount
    long_term_disability_insurance_amount
    life_insurance_amount
    additional_federal_taxes_retained
    additional_provincial_taxes_retained
    serious_illness_amount
    cotisation_syndicale_amount
    employer_health_insurance_contribution_amount
    employer_dental_insurance_contribution_amount
    employer_life_insurance_contribution_amount
  ].freeze

  MINIMUM_AGE = 15

  self.table_name = 'employees'
  self.primary_key = 'employee_id'

  ENCRYPTED_FIELDS = %w[
    employee_name
    ssn
    phone_no
    business_email
    google_address
    address_line1
    address_line2
    city
    personal_email
    mobile_phone_no
    fax_no
    postal_code
    date_of_birth
  ].freeze

  define_decrypted_attributes(ENCRYPTED_FIELDS)

  attr_accessor :bank_account, :employee_role, :province_code

  belongs_to :company
  belongs_to :erp_user, optional: true
  belongs_to :province

  has_many :employee_timesheets, dependent: :destroy
  has_many :employee_payroll_transactions, dependent: :destroy
  has_many :employee_roles, dependent: :destroy
  has_many :employee_payroll_calculations, dependent: :destroy
  has_many :integration_references, as: :reference, dependent: :destroy
  has_many :reference_events, as: :reference, dependent: nil

  validates :employee_name, presence: true, length: { maximum: 50 },
                            format: {
                              with: /\A[a-zA-Z\s\-.]+\z/
                              # message: I18n.t('payroll.employees.form.ssn_validation')
                            }
  validates :date_of_birth, presence: true
  validates :start_date, presence: false
  validates :vacation_percentage, numericality: { greater_than_or_equal_to: 0, less_than_or_equal_to: 100 },
                                  presence: false
  validates :health_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :dental_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :cotisation_syndicale_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :child_support_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :rrsp_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :long_term_disability_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :life_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :business_email, uniqueness: { scope: :company_id }, allow_blank: true
  validates :personal_email, uniqueness: { scope: :company_id }, allow_blank: false
  validates :phone_no, uniqueness: { scope: :company_id }, allow_blank: true
  validates :default_language, inclusion: { in: %w[E F], message: "must be 'E' or 'F'" }

  validate :validate_tax_categories
  validate :validate_dates
  validate :termination_reason_is_with_termination_date
  validate :validate_ssn
  validate :validate_erp_user_id, if: :erp_user_id_changed?

  validates :address_line1, presence: true, length: { maximum: 300 }
  validates :address_line2, presence: false, length: { maximum: 300 }
  validates :city, presence: true, length: { maximum: 300 }
  validates :postal_code, presence: true, length: { maximum: 7 },
                          format: { with: /\A[A-Za-z]\d[A-Za-z] \d[A-Za-z]\d\z/, message: 'must be in the format A1A 1A1' }
  validates :federal_tax_category, presence: true
  validates :provincial_tax_category, presence: true

  scope :without_timesheets_for_period, lambda { |payroll_period_id|
    joins('LEFT JOIN employee_time_sheets ON employees.employee_id = employee_time_sheets.employee_id')
      .where(
        'employee_time_sheets.payroll_period_id IS NULL OR employee_time_sheets.payroll_period_id != ?',
        payroll_period_id
      )
      .distinct
  }

  def name
    employee_name
  end

  # Checks if any of the pay columns have changed.
  #
  # @return [Boolean] true if any pay columns have changed, false otherwise
  def pay_columns_changed?
    changes.keys.any? { |column| PAY_COLUMNS.include?(column) }
  end

  # Counts the number of timesheets for a specific payroll period.
  #
  # @param payroll_period_id [Integer] the ID of the payroll period
  # @return [Integer] the number of timesheets for the specified payroll period
  def timesheets_count_for_period(payroll_period_id)
    employee_timesheets.where(payroll_period_id: payroll_period_id).count
  end

  # Returns a description of the employee's roles.
  #
  # @return [String] a comma-separated list of role descriptions
  def role_description
    employee_roles.select(:description).map(&:description).join(', ')
  end

  class << self
    # Retrieves employee options using a stored procedure.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_procedure(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_procedure(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      result_type = if must_have_timesheets
                      '01'
                    else
                      '01' # should be fixed
                    end
      ActiveRecord::Base.execute_procedure(
        'pr_get_employees_for_dropdown',
        nil, # employee_id
        payroll_period_id,
        company_id,
        nil, # payroll_transaction_header_id
        result_type
      )
    end

    # Retrieves employee options using a SQL query.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_sql(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_sql(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      sql = <<-SQL.squish
        OPEN SYMMETRIC KEY PaieHubSymmetricKey DECRYPTION BY CERTIFICATE PaieHubCertificate;
        SELECT
          employees.employee_id,
          CONVERT(NVARCHAR(300), DecryptByKey(employees.[employee_name])) AS employee_name,
          COUNT(employee_time_sheets.employee_id) AS timesheets_count
        FROM
          employees
        LEFT JOIN
          employee_time_sheets
          ON employees.employee_id = employee_time_sheets.employee_id
        WHERE
          employees.company_id = ?
      SQL

      if payroll_period_id.positive?
        sql += <<-SQL.squish
          AND employee_time_sheets.payroll_period_id = ?
        SQL
      end

      sql += <<-SQL.squish
        GROUP BY
          employees.employee_id,
          employee_name
      SQL

      if must_have_timesheets
        sql += <<-SQL.squish
          HAVING COUNT(employee_time_sheets.employee_id) > 0
        SQL
      end

      sql += <<-SQL.squish
        ORDER BY
          timesheets_count DESC
      SQL

      sanitized_query = if payroll_period_id.positive?
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id, payroll_period_id])
                        else
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id])
                        end

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a
    end

    # Validates the format of a social security number (SSN).
    #
    # @param ssn [String] the social security number to validate
    # @return [Boolean] true if the SSN is valid, false otherwise
    # @example
    #   Employee.ssn_valid?('123-45-6789')
    def ssn_valid?(ssn)
      ssn = ssn.to_s.strip
      return false if ssn.blank?
      return false unless ssn.match?(/^\d{9}$|^\d{3}-\d{3}-\d{3}$/)

      digits = ssn.scan(/\d/).join

      sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT dbo.fu_validate_ssn(?) as value', digits])

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a.dig(0, 'value')
    end
  end

  # Returns the bank accounts associated with the employee.
  #
  # @return [ActiveRecord::Relation] the bank accounts associated with the employee
  def bank_accounts
    BankAccount.where(
      source: 'Employee',
      reference_id: employee_id
    )
  end

  # Returns the default bank account of the employee.
  #
  # @return [BankAccount, nil] the default bank account of the employee, or nil if none exists
  def 
    bank_accounts.find_by(is_default: true)
  end

  # Returns the locale of the employee.
  #
  # @return [String] the locale of the employee
  def locale
    Language.new(default_language).locale
  end

  def formatted_start_date
    I18n.l(start_date, format: :long) if start_date
  end

  def url
    "#{AppMode.payroll.web_uri}/app/payroll/employees/#{employee_id}"
  end

  # Validates the social security number (SSN) of the employee.
  #
  # @return [Boolean] true if the SSN is valid, false otherwise
  def validate_ssn
    validity = Employee.ssn_valid?(ssn)
    errors.add(:ssn, I18n.t('general.is_invalid')) unless validity
    validity
  end

  # Returns the date of birth of the employee.
  # This should be replaced once the decyrption there is fixed
  #
  # @return [Date] the date of birth of the employee
  def date_of_birth
    self.class.find_employee_procedure(company_id: company_id, employee_id: employee_id,
                                       active: '03')['date_of_birth'] || super
  rescue StandardError
    super
  end

  private

  # Validates that the termination reason is present if the termination date is present.
  #
  # @return [void]
  def termination_reason_is_with_termination_date
    return unless termination_date.blank? && termination_reason.present?

    errors.add(:termination_reason, "can't be present without termination date")
  end

  # Validates the federal and provincial tax categories.
  #
  # @return [void]
  def validate_tax_categories
    return if federal_tax_category.blank? && provincial_tax_category.blank?

    unless TypesMaster.federal_tax_codes.exists?(type: federal_tax_category)
      errors.add(:federal_tax_category, 'is not a valid federal tax category')
    end

    return if TypesMaster.provincial_tax_codes.exists?(type: provincial_tax_category)

    errors.add(:provincial_tax_category, 'is not a valid provincial tax category')
  end

  # Validates the dates related to the employee.
  #
  # @return [void]
  def validate_dates
    today = Time.zone.today.beginning_of_day

    date_of_birth = parse_date_if_needed(self.date_of_birth)
    if date_of_birth.present? && date_of_birth.beginning_of_day > today - MINIMUM_AGE.years
      errors.add(:date_of_birth, "must be at least #{MINIMUM_AGE} years old")
    end

    start_date = parse_date_if_needed(self.start_date)
    errors.add(:start_date, "can't be in the future") if start_date.present? && start_date.beginning_of_day > today

    termination_date = parse_date_if_needed(self.termination_date)
    return if termination_date.blank?

    termination_date_only = termination_date.beginning_of_day

    if start_date.present? && termination_date_only < start_date.beginning_of_day
      errors.add(:termination_date, "can't be before start date")
    end

    if date_of_birth.present? && termination_date_only < date_of_birth.beginning_of_day
      errors.add(:termination_date, "can't be before date of birth")
    end

    errors.add(:termination_date, "can't be in the future") if termination_date_only > today

    return if termination_reason.present?

    errors.add(:termination_reason, "can't be blank")
  rescue StandardError => e
    errors.add(:base, e.message)
  end

  # Returns the email of the employee for the employee portal login.
  # It is the email of the ERP user associated with the employee.
  #
  # @return [String] the email of the employee for the employee portal login
  def 
    erp_user&.email
  end

  # Returns whether the employee portal login is enabled for the employee.
  # It is enabled if the ERP user associated with the employee is active.
  #
  # @return [Boolean] true if the employee portal login is enabled, false otherwise
  def 
    erp_user&.active
  end

  def validate_erp_user_id
    user_with_email = ErpUser.accessible.find_by(email: )
    return unless user_with_email

    user_has_other_employees_for_company = user_with_email.employees.where(
      company_id: company_id
    ).where.not(
      employee_id: employee_id
    ).exists?
    return unless user_has_other_employees_for_company

    errors.add(:employee_portal_login_email, 'is already in use by another employee in this company')
  end

  # Parses a date if it is a string.
  #
  # @param date [String, Date, DateTime] the date to parse
  # @return [DateTime, nil] the parsed date, or nil if the date is not present
  def parse_date_if_needed(date)
    if date.is_a?(String)
      DateTime.parse(date)
    else
      date
    end
  end
end

#date_of_birth ⇒ Date

Returns the date of birth of the employee. This should be replaced once the decyrption there is fixed

Returns:

  • (Date) —

    the date of birth of the employee



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
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
102
103
104
105
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
292
293
294
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
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
 
# File 'app/models/employee.rb', line 25

class Employee < ApplicationRecord
  include HasAddress
  include EmployeesProcedures

  PAY_COLUMNS = %w[
    accepts_direct_deposit
    provincial_tax_category
    federal_tax_category
    vacation_percentage
    health_insurance_amount
    dental_insurance_amount
    rrsp_amount
    long_term_disability_insurance_amount
    life_insurance_amount
    additional_federal_taxes_retained
    additional_provincial_taxes_retained
    serious_illness_amount
    cotisation_syndicale_amount
    employer_health_insurance_contribution_amount
    employer_dental_insurance_contribution_amount
    employer_life_insurance_contribution_amount
  ].freeze

  MINIMUM_AGE = 15

  self.table_name = 'employees'
  self.primary_key = 'employee_id'

  ENCRYPTED_FIELDS = %w[
    employee_name
    ssn
    phone_no
    business_email
    google_address
    address_line1
    address_line2
    city
    personal_email
    mobile_phone_no
    fax_no
    postal_code
    date_of_birth
  ].freeze

  define_decrypted_attributes(ENCRYPTED_FIELDS)

  attr_accessor :bank_account, :employee_role, :province_code

  belongs_to :company
  belongs_to :erp_user, optional: true
  belongs_to :province

  has_many :employee_timesheets, dependent: :destroy
  has_many :employee_payroll_transactions, dependent: :destroy
  has_many :employee_roles, dependent: :destroy
  has_many :employee_payroll_calculations, dependent: :destroy
  has_many :integration_references, as: :reference, dependent: :destroy
  has_many :reference_events, as: :reference, dependent: nil

  validates :employee_name, presence: true, length: { maximum: 50 },
                            format: {
                              with: /\A[a-zA-Z\s\-.]+\z/
                              # message: I18n.t('payroll.employees.form.ssn_validation')
                            }
  validates :date_of_birth, presence: true
  validates :start_date, presence: false
  validates :vacation_percentage, numericality: { greater_than_or_equal_to: 0, less_than_or_equal_to: 100 },
                                  presence: false
  validates :health_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :dental_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :cotisation_syndicale_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :child_support_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :rrsp_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :long_term_disability_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :life_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :business_email, uniqueness: { scope: :company_id }, allow_blank: true
  validates :personal_email, uniqueness: { scope: :company_id }, allow_blank: false
  validates :phone_no, uniqueness: { scope: :company_id }, allow_blank: true
  validates :default_language, inclusion: { in: %w[E F], message: "must be 'E' or 'F'" }

  validate :validate_tax_categories
  validate :validate_dates
  validate :termination_reason_is_with_termination_date
  validate :validate_ssn
  validate :validate_erp_user_id, if: :erp_user_id_changed?

  validates :address_line1, presence: true, length: { maximum: 300 }
  validates :address_line2, presence: false, length: { maximum: 300 }
  validates :city, presence: true, length: { maximum: 300 }
  validates :postal_code, presence: true, length: { maximum: 7 },
                          format: { with: /\A[A-Za-z]\d[A-Za-z] \d[A-Za-z]\d\z/, message: 'must be in the format A1A 1A1' }
  validates :federal_tax_category, presence: true
  validates :provincial_tax_category, presence: true

  scope :without_timesheets_for_period, lambda { |payroll_period_id|
    joins('LEFT JOIN employee_time_sheets ON employees.employee_id = employee_time_sheets.employee_id')
      .where(
        'employee_time_sheets.payroll_period_id IS NULL OR employee_time_sheets.payroll_period_id != ?',
        payroll_period_id
      )
      .distinct
  }

  def name
    employee_name
  end

  # Checks if any of the pay columns have changed.
  #
  # @return [Boolean] true if any pay columns have changed, false otherwise
  def pay_columns_changed?
    changes.keys.any? { |column| PAY_COLUMNS.include?(column) }
  end

  # Counts the number of timesheets for a specific payroll period.
  #
  # @param payroll_period_id [Integer] the ID of the payroll period
  # @return [Integer] the number of timesheets for the specified payroll period
  def timesheets_count_for_period(payroll_period_id)
    employee_timesheets.where(payroll_period_id: payroll_period_id).count
  end

  # Returns a description of the employee's roles.
  #
  # @return [String] a comma-separated list of role descriptions
  def role_description
    employee_roles.select(:description).map(&:description).join(', ')
  end

  class << self
    # Retrieves employee options using a stored procedure.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_procedure(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_procedure(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      result_type = if must_have_timesheets
                      '01'
                    else
                      '01' # should be fixed
                    end
      ActiveRecord::Base.execute_procedure(
        'pr_get_employees_for_dropdown',
        nil, # employee_id
        payroll_period_id,
        company_id,
        nil, # payroll_transaction_header_id
        result_type
      )
    end

    # Retrieves employee options using a SQL query.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_sql(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_sql(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      sql = <<-SQL.squish
        OPEN SYMMETRIC KEY PaieHubSymmetricKey DECRYPTION BY CERTIFICATE PaieHubCertificate;
        SELECT
          employees.employee_id,
          CONVERT(NVARCHAR(300), DecryptByKey(employees.[employee_name])) AS employee_name,
          COUNT(employee_time_sheets.employee_id) AS timesheets_count
        FROM
          employees
        LEFT JOIN
          employee_time_sheets
          ON employees.employee_id = employee_time_sheets.employee_id
        WHERE
          employees.company_id = ?
      SQL

      if payroll_period_id.positive?
        sql += <<-SQL.squish
          AND employee_time_sheets.payroll_period_id = ?
        SQL
      end

      sql += <<-SQL.squish
        GROUP BY
          employees.employee_id,
          employee_name
      SQL

      if must_have_timesheets
        sql += <<-SQL.squish
          HAVING COUNT(employee_time_sheets.employee_id) > 0
        SQL
      end

      sql += <<-SQL.squish
        ORDER BY
          timesheets_count DESC
      SQL

      sanitized_query = if payroll_period_id.positive?
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id, payroll_period_id])
                        else
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id])
                        end

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a
    end

    # Validates the format of a social security number (SSN).
    #
    # @param ssn [String] the social security number to validate
    # @return [Boolean] true if the SSN is valid, false otherwise
    # @example
    #   Employee.ssn_valid?('123-45-6789')
    def ssn_valid?(ssn)
      ssn = ssn.to_s.strip
      return false if ssn.blank?
      return false unless ssn.match?(/^\d{9}$|^\d{3}-\d{3}-\d{3}$/)

      digits = ssn.scan(/\d/).join

      sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT dbo.fu_validate_ssn(?) as value', digits])

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a.dig(0, 'value')
    end
  end

  # Returns the bank accounts associated with the employee.
  #
  # @return [ActiveRecord::Relation] the bank accounts associated with the employee
  def bank_accounts
    BankAccount.where(
      source: 'Employee',
      reference_id: employee_id
    )
  end

  # Returns the default bank account of the employee.
  #
  # @return [BankAccount, nil] the default bank account of the employee, or nil if none exists
  def 
    bank_accounts.find_by(is_default: true)
  end

  # Returns the locale of the employee.
  #
  # @return [String] the locale of the employee
  def locale
    Language.new(default_language).locale
  end

  def formatted_start_date
    I18n.l(start_date, format: :long) if start_date
  end

  def url
    "#{AppMode.payroll.web_uri}/app/payroll/employees/#{employee_id}"
  end

  # Validates the social security number (SSN) of the employee.
  #
  # @return [Boolean] true if the SSN is valid, false otherwise
  def validate_ssn
    validity = Employee.ssn_valid?(ssn)
    errors.add(:ssn, I18n.t('general.is_invalid')) unless validity
    validity
  end

  # Returns the date of birth of the employee.
  # This should be replaced once the decyrption there is fixed
  #
  # @return [Date] the date of birth of the employee
  def date_of_birth
    self.class.find_employee_procedure(company_id: company_id, employee_id: employee_id,
                                       active: '03')['date_of_birth'] || super
  rescue StandardError
    super
  end

  private

  # Validates that the termination reason is present if the termination date is present.
  #
  # @return [void]
  def termination_reason_is_with_termination_date
    return unless termination_date.blank? && termination_reason.present?

    errors.add(:termination_reason, "can't be present without termination date")
  end

  # Validates the federal and provincial tax categories.
  #
  # @return [void]
  def validate_tax_categories
    return if federal_tax_category.blank? && provincial_tax_category.blank?

    unless TypesMaster.federal_tax_codes.exists?(type: federal_tax_category)
      errors.add(:federal_tax_category, 'is not a valid federal tax category')
    end

    return if TypesMaster.provincial_tax_codes.exists?(type: provincial_tax_category)

    errors.add(:provincial_tax_category, 'is not a valid provincial tax category')
  end

  # Validates the dates related to the employee.
  #
  # @return [void]
  def validate_dates
    today = Time.zone.today.beginning_of_day

    date_of_birth = parse_date_if_needed(self.date_of_birth)
    if date_of_birth.present? && date_of_birth.beginning_of_day > today - MINIMUM_AGE.years
      errors.add(:date_of_birth, "must be at least #{MINIMUM_AGE} years old")
    end

    start_date = parse_date_if_needed(self.start_date)
    errors.add(:start_date, "can't be in the future") if start_date.present? && start_date.beginning_of_day > today

    termination_date = parse_date_if_needed(self.termination_date)
    return if termination_date.blank?

    termination_date_only = termination_date.beginning_of_day

    if start_date.present? && termination_date_only < start_date.beginning_of_day
      errors.add(:termination_date, "can't be before start date")
    end

    if date_of_birth.present? && termination_date_only < date_of_birth.beginning_of_day
      errors.add(:termination_date, "can't be before date of birth")
    end

    errors.add(:termination_date, "can't be in the future") if termination_date_only > today

    return if termination_reason.present?

    errors.add(:termination_reason, "can't be blank")
  rescue StandardError => e
    errors.add(:base, e.message)
  end

  # Returns the email of the employee for the employee portal login.
  # It is the email of the ERP user associated with the employee.
  #
  # @return [String] the email of the employee for the employee portal login
  def 
    erp_user&.email
  end

  # Returns whether the employee portal login is enabled for the employee.
  # It is enabled if the ERP user associated with the employee is active.
  #
  # @return [Boolean] true if the employee portal login is enabled, false otherwise
  def 
    erp_user&.active
  end

  def validate_erp_user_id
    user_with_email = ErpUser.accessible.find_by(email: )
    return unless user_with_email

    user_has_other_employees_for_company = user_with_email.employees.where(
      company_id: company_id
    ).where.not(
      employee_id: employee_id
    ).exists?
    return unless user_has_other_employees_for_company

    errors.add(:employee_portal_login_email, 'is already in use by another employee in this company')
  end

  # Parses a date if it is a string.
  #
  # @param date [String, Date, DateTime] the date to parse
  # @return [DateTime, nil] the parsed date, or nil if the date is not present
  def parse_date_if_needed(date)
    if date.is_a?(String)
      DateTime.parse(date)
    else
      date
    end
  end
end

#employee_id ⇒ Integer

Returns the primary key of the employee.

Returns:

  • (Integer) —

    the primary key of the employee



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
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
102
103
104
105
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
292
293
294
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
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
 
# File 'app/models/employee.rb', line 25

class Employee < ApplicationRecord
  include HasAddress
  include EmployeesProcedures

  PAY_COLUMNS = %w[
    accepts_direct_deposit
    provincial_tax_category
    federal_tax_category
    vacation_percentage
    health_insurance_amount
    dental_insurance_amount
    rrsp_amount
    long_term_disability_insurance_amount
    life_insurance_amount
    additional_federal_taxes_retained
    additional_provincial_taxes_retained
    serious_illness_amount
    cotisation_syndicale_amount
    employer_health_insurance_contribution_amount
    employer_dental_insurance_contribution_amount
    employer_life_insurance_contribution_amount
  ].freeze

  MINIMUM_AGE = 15

  self.table_name = 'employees'
  self.primary_key = 'employee_id'

  ENCRYPTED_FIELDS = %w[
    employee_name
    ssn
    phone_no
    business_email
    google_address
    address_line1
    address_line2
    city
    personal_email
    mobile_phone_no
    fax_no
    postal_code
    date_of_birth
  ].freeze

  define_decrypted_attributes(ENCRYPTED_FIELDS)

  attr_accessor :bank_account, :employee_role, :province_code

  belongs_to :company
  belongs_to :erp_user, optional: true
  belongs_to :province

  has_many :employee_timesheets, dependent: :destroy
  has_many :employee_payroll_transactions, dependent: :destroy
  has_many :employee_roles, dependent: :destroy
  has_many :employee_payroll_calculations, dependent: :destroy
  has_many :integration_references, as: :reference, dependent: :destroy
  has_many :reference_events, as: :reference, dependent: nil

  validates :employee_name, presence: true, length: { maximum: 50 },
                            format: {
                              with: /\A[a-zA-Z\s\-.]+\z/
                              # message: I18n.t('payroll.employees.form.ssn_validation')
                            }
  validates :date_of_birth, presence: true
  validates :start_date, presence: false
  validates :vacation_percentage, numericality: { greater_than_or_equal_to: 0, less_than_or_equal_to: 100 },
                                  presence: false
  validates :health_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :dental_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :cotisation_syndicale_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :child_support_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :rrsp_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :long_term_disability_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :life_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :business_email, uniqueness: { scope: :company_id }, allow_blank: true
  validates :personal_email, uniqueness: { scope: :company_id }, allow_blank: false
  validates :phone_no, uniqueness: { scope: :company_id }, allow_blank: true
  validates :default_language, inclusion: { in: %w[E F], message: "must be 'E' or 'F'" }

  validate :validate_tax_categories
  validate :validate_dates
  validate :termination_reason_is_with_termination_date
  validate :validate_ssn
  validate :validate_erp_user_id, if: :erp_user_id_changed?

  validates :address_line1, presence: true, length: { maximum: 300 }
  validates :address_line2, presence: false, length: { maximum: 300 }
  validates :city, presence: true, length: { maximum: 300 }
  validates :postal_code, presence: true, length: { maximum: 7 },
                          format: { with: /\A[A-Za-z]\d[A-Za-z] \d[A-Za-z]\d\z/, message: 'must be in the format A1A 1A1' }
  validates :federal_tax_category, presence: true
  validates :provincial_tax_category, presence: true

  scope :without_timesheets_for_period, lambda { |payroll_period_id|
    joins('LEFT JOIN employee_time_sheets ON employees.employee_id = employee_time_sheets.employee_id')
      .where(
        'employee_time_sheets.payroll_period_id IS NULL OR employee_time_sheets.payroll_period_id != ?',
        payroll_period_id
      )
      .distinct
  }

  def name
    employee_name
  end

  # Checks if any of the pay columns have changed.
  #
  # @return [Boolean] true if any pay columns have changed, false otherwise
  def pay_columns_changed?
    changes.keys.any? { |column| PAY_COLUMNS.include?(column) }
  end

  # Counts the number of timesheets for a specific payroll period.
  #
  # @param payroll_period_id [Integer] the ID of the payroll period
  # @return [Integer] the number of timesheets for the specified payroll period
  def timesheets_count_for_period(payroll_period_id)
    employee_timesheets.where(payroll_period_id: payroll_period_id).count
  end

  # Returns a description of the employee's roles.
  #
  # @return [String] a comma-separated list of role descriptions
  def role_description
    employee_roles.select(:description).map(&:description).join(', ')
  end

  class << self
    # Retrieves employee options using a stored procedure.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_procedure(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_procedure(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      result_type = if must_have_timesheets
                      '01'
                    else
                      '01' # should be fixed
                    end
      ActiveRecord::Base.execute_procedure(
        'pr_get_employees_for_dropdown',
        nil, # employee_id
        payroll_period_id,
        company_id,
        nil, # payroll_transaction_header_id
        result_type
      )
    end

    # Retrieves employee options using a SQL query.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_sql(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_sql(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      sql = <<-SQL.squish
        OPEN SYMMETRIC KEY PaieHubSymmetricKey DECRYPTION BY CERTIFICATE PaieHubCertificate;
        SELECT
          employees.employee_id,
          CONVERT(NVARCHAR(300), DecryptByKey(employees.[employee_name])) AS employee_name,
          COUNT(employee_time_sheets.employee_id) AS timesheets_count
        FROM
          employees
        LEFT JOIN
          employee_time_sheets
          ON employees.employee_id = employee_time_sheets.employee_id
        WHERE
          employees.company_id = ?
      SQL

      if payroll_period_id.positive?
        sql += <<-SQL.squish
          AND employee_time_sheets.payroll_period_id = ?
        SQL
      end

      sql += <<-SQL.squish
        GROUP BY
          employees.employee_id,
          employee_name
      SQL

      if must_have_timesheets
        sql += <<-SQL.squish
          HAVING COUNT(employee_time_sheets.employee_id) > 0
        SQL
      end

      sql += <<-SQL.squish
        ORDER BY
          timesheets_count DESC
      SQL

      sanitized_query = if payroll_period_id.positive?
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id, payroll_period_id])
                        else
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id])
                        end

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a
    end

    # Validates the format of a social security number (SSN).
    #
    # @param ssn [String] the social security number to validate
    # @return [Boolean] true if the SSN is valid, false otherwise
    # @example
    #   Employee.ssn_valid?('123-45-6789')
    def ssn_valid?(ssn)
      ssn = ssn.to_s.strip
      return false if ssn.blank?
      return false unless ssn.match?(/^\d{9}$|^\d{3}-\d{3}-\d{3}$/)

      digits = ssn.scan(/\d/).join

      sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT dbo.fu_validate_ssn(?) as value', digits])

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a.dig(0, 'value')
    end
  end

  # Returns the bank accounts associated with the employee.
  #
  # @return [ActiveRecord::Relation] the bank accounts associated with the employee
  def bank_accounts
    BankAccount.where(
      source: 'Employee',
      reference_id: employee_id
    )
  end

  # Returns the default bank account of the employee.
  #
  # @return [BankAccount, nil] the default bank account of the employee, or nil if none exists
  def 
    bank_accounts.find_by(is_default: true)
  end

  # Returns the locale of the employee.
  #
  # @return [String] the locale of the employee
  def locale
    Language.new(default_language).locale
  end

  def formatted_start_date
    I18n.l(start_date, format: :long) if start_date
  end

  def url
    "#{AppMode.payroll.web_uri}/app/payroll/employees/#{employee_id}"
  end

  # Validates the social security number (SSN) of the employee.
  #
  # @return [Boolean] true if the SSN is valid, false otherwise
  def validate_ssn
    validity = Employee.ssn_valid?(ssn)
    errors.add(:ssn, I18n.t('general.is_invalid')) unless validity
    validity
  end

  # Returns the date of birth of the employee.
  # This should be replaced once the decyrption there is fixed
  #
  # @return [Date] the date of birth of the employee
  def date_of_birth
    self.class.find_employee_procedure(company_id: company_id, employee_id: employee_id,
                                       active: '03')['date_of_birth'] || super
  rescue StandardError
    super
  end

  private

  # Validates that the termination reason is present if the termination date is present.
  #
  # @return [void]
  def termination_reason_is_with_termination_date
    return unless termination_date.blank? && termination_reason.present?

    errors.add(:termination_reason, "can't be present without termination date")
  end

  # Validates the federal and provincial tax categories.
  #
  # @return [void]
  def validate_tax_categories
    return if federal_tax_category.blank? && provincial_tax_category.blank?

    unless TypesMaster.federal_tax_codes.exists?(type: federal_tax_category)
      errors.add(:federal_tax_category, 'is not a valid federal tax category')
    end

    return if TypesMaster.provincial_tax_codes.exists?(type: provincial_tax_category)

    errors.add(:provincial_tax_category, 'is not a valid provincial tax category')
  end

  # Validates the dates related to the employee.
  #
  # @return [void]
  def validate_dates
    today = Time.zone.today.beginning_of_day

    date_of_birth = parse_date_if_needed(self.date_of_birth)
    if date_of_birth.present? && date_of_birth.beginning_of_day > today - MINIMUM_AGE.years
      errors.add(:date_of_birth, "must be at least #{MINIMUM_AGE} years old")
    end

    start_date = parse_date_if_needed(self.start_date)
    errors.add(:start_date, "can't be in the future") if start_date.present? && start_date.beginning_of_day > today

    termination_date = parse_date_if_needed(self.termination_date)
    return if termination_date.blank?

    termination_date_only = termination_date.beginning_of_day

    if start_date.present? && termination_date_only < start_date.beginning_of_day
      errors.add(:termination_date, "can't be before start date")
    end

    if date_of_birth.present? && termination_date_only < date_of_birth.beginning_of_day
      errors.add(:termination_date, "can't be before date of birth")
    end

    errors.add(:termination_date, "can't be in the future") if termination_date_only > today

    return if termination_reason.present?

    errors.add(:termination_reason, "can't be blank")
  rescue StandardError => e
    errors.add(:base, e.message)
  end

  # Returns the email of the employee for the employee portal login.
  # It is the email of the ERP user associated with the employee.
  #
  # @return [String] the email of the employee for the employee portal login
  def 
    erp_user&.email
  end

  # Returns whether the employee portal login is enabled for the employee.
  # It is enabled if the ERP user associated with the employee is active.
  #
  # @return [Boolean] true if the employee portal login is enabled, false otherwise
  def 
    erp_user&.active
  end

  def validate_erp_user_id
    user_with_email = ErpUser.accessible.find_by(email: )
    return unless user_with_email

    user_has_other_employees_for_company = user_with_email.employees.where(
      company_id: company_id
    ).where.not(
      employee_id: employee_id
    ).exists?
    return unless user_has_other_employees_for_company

    errors.add(:employee_portal_login_email, 'is already in use by another employee in this company')
  end

  # Parses a date if it is a string.
  #
  # @param date [String, Date, DateTime] the date to parse
  # @return [DateTime, nil] the parsed date, or nil if the date is not present
  def parse_date_if_needed(date)
    if date.is_a?(String)
      DateTime.parse(date)
    else
      date
    end
  end
end

#employee_name ⇒ String

Returns the name of the employee.

Returns:

  • (String) —

    the name of the employee



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
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
102
103
104
105
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
292
293
294
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
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
 
# File 'app/models/employee.rb', line 25

class Employee < ApplicationRecord
  include HasAddress
  include EmployeesProcedures

  PAY_COLUMNS = %w[
    accepts_direct_deposit
    provincial_tax_category
    federal_tax_category
    vacation_percentage
    health_insurance_amount
    dental_insurance_amount
    rrsp_amount
    long_term_disability_insurance_amount
    life_insurance_amount
    additional_federal_taxes_retained
    additional_provincial_taxes_retained
    serious_illness_amount
    cotisation_syndicale_amount
    employer_health_insurance_contribution_amount
    employer_dental_insurance_contribution_amount
    employer_life_insurance_contribution_amount
  ].freeze

  MINIMUM_AGE = 15

  self.table_name = 'employees'
  self.primary_key = 'employee_id'

  ENCRYPTED_FIELDS = %w[
    employee_name
    ssn
    phone_no
    business_email
    google_address
    address_line1
    address_line2
    city
    personal_email
    mobile_phone_no
    fax_no
    postal_code
    date_of_birth
  ].freeze

  define_decrypted_attributes(ENCRYPTED_FIELDS)

  attr_accessor :bank_account, :employee_role, :province_code

  belongs_to :company
  belongs_to :erp_user, optional: true
  belongs_to :province

  has_many :employee_timesheets, dependent: :destroy
  has_many :employee_payroll_transactions, dependent: :destroy
  has_many :employee_roles, dependent: :destroy
  has_many :employee_payroll_calculations, dependent: :destroy
  has_many :integration_references, as: :reference, dependent: :destroy
  has_many :reference_events, as: :reference, dependent: nil

  validates :employee_name, presence: true, length: { maximum: 50 },
                            format: {
                              with: /\A[a-zA-Z\s\-.]+\z/
                              # message: I18n.t('payroll.employees.form.ssn_validation')
                            }
  validates :date_of_birth, presence: true
  validates :start_date, presence: false
  validates :vacation_percentage, numericality: { greater_than_or_equal_to: 0, less_than_or_equal_to: 100 },
                                  presence: false
  validates :health_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :dental_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :cotisation_syndicale_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :child_support_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :rrsp_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :long_term_disability_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :life_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :business_email, uniqueness: { scope: :company_id }, allow_blank: true
  validates :personal_email, uniqueness: { scope: :company_id }, allow_blank: false
  validates :phone_no, uniqueness: { scope: :company_id }, allow_blank: true
  validates :default_language, inclusion: { in: %w[E F], message: "must be 'E' or 'F'" }

  validate :validate_tax_categories
  validate :validate_dates
  validate :termination_reason_is_with_termination_date
  validate :validate_ssn
  validate :validate_erp_user_id, if: :erp_user_id_changed?

  validates :address_line1, presence: true, length: { maximum: 300 }
  validates :address_line2, presence: false, length: { maximum: 300 }
  validates :city, presence: true, length: { maximum: 300 }
  validates :postal_code, presence: true, length: { maximum: 7 },
                          format: { with: /\A[A-Za-z]\d[A-Za-z] \d[A-Za-z]\d\z/, message: 'must be in the format A1A 1A1' }
  validates :federal_tax_category, presence: true
  validates :provincial_tax_category, presence: true

  scope :without_timesheets_for_period, lambda { |payroll_period_id|
    joins('LEFT JOIN employee_time_sheets ON employees.employee_id = employee_time_sheets.employee_id')
      .where(
        'employee_time_sheets.payroll_period_id IS NULL OR employee_time_sheets.payroll_period_id != ?',
        payroll_period_id
      )
      .distinct
  }

  def name
    employee_name
  end

  # Checks if any of the pay columns have changed.
  #
  # @return [Boolean] true if any pay columns have changed, false otherwise
  def pay_columns_changed?
    changes.keys.any? { |column| PAY_COLUMNS.include?(column) }
  end

  # Counts the number of timesheets for a specific payroll period.
  #
  # @param payroll_period_id [Integer] the ID of the payroll period
  # @return [Integer] the number of timesheets for the specified payroll period
  def timesheets_count_for_period(payroll_period_id)
    employee_timesheets.where(payroll_period_id: payroll_period_id).count
  end

  # Returns a description of the employee's roles.
  #
  # @return [String] a comma-separated list of role descriptions
  def role_description
    employee_roles.select(:description).map(&:description).join(', ')
  end

  class << self
    # Retrieves employee options using a stored procedure.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_procedure(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_procedure(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      result_type = if must_have_timesheets
                      '01'
                    else
                      '01' # should be fixed
                    end
      ActiveRecord::Base.execute_procedure(
        'pr_get_employees_for_dropdown',
        nil, # employee_id
        payroll_period_id,
        company_id,
        nil, # payroll_transaction_header_id
        result_type
      )
    end

    # Retrieves employee options using a SQL query.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_sql(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_sql(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      sql = <<-SQL.squish
        OPEN SYMMETRIC KEY PaieHubSymmetricKey DECRYPTION BY CERTIFICATE PaieHubCertificate;
        SELECT
          employees.employee_id,
          CONVERT(NVARCHAR(300), DecryptByKey(employees.[employee_name])) AS employee_name,
          COUNT(employee_time_sheets.employee_id) AS timesheets_count
        FROM
          employees
        LEFT JOIN
          employee_time_sheets
          ON employees.employee_id = employee_time_sheets.employee_id
        WHERE
          employees.company_id = ?
      SQL

      if payroll_period_id.positive?
        sql += <<-SQL.squish
          AND employee_time_sheets.payroll_period_id = ?
        SQL
      end

      sql += <<-SQL.squish
        GROUP BY
          employees.employee_id,
          employee_name
      SQL

      if must_have_timesheets
        sql += <<-SQL.squish
          HAVING COUNT(employee_time_sheets.employee_id) > 0
        SQL
      end

      sql += <<-SQL.squish
        ORDER BY
          timesheets_count DESC
      SQL

      sanitized_query = if payroll_period_id.positive?
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id, payroll_period_id])
                        else
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id])
                        end

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a
    end

    # Validates the format of a social security number (SSN).
    #
    # @param ssn [String] the social security number to validate
    # @return [Boolean] true if the SSN is valid, false otherwise
    # @example
    #   Employee.ssn_valid?('123-45-6789')
    def ssn_valid?(ssn)
      ssn = ssn.to_s.strip
      return false if ssn.blank?
      return false unless ssn.match?(/^\d{9}$|^\d{3}-\d{3}-\d{3}$/)

      digits = ssn.scan(/\d/).join

      sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT dbo.fu_validate_ssn(?) as value', digits])

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a.dig(0, 'value')
    end
  end

  # Returns the bank accounts associated with the employee.
  #
  # @return [ActiveRecord::Relation] the bank accounts associated with the employee
  def bank_accounts
    BankAccount.where(
      source: 'Employee',
      reference_id: employee_id
    )
  end

  # Returns the default bank account of the employee.
  #
  # @return [BankAccount, nil] the default bank account of the employee, or nil if none exists
  def 
    bank_accounts.find_by(is_default: true)
  end

  # Returns the locale of the employee.
  #
  # @return [String] the locale of the employee
  def locale
    Language.new(default_language).locale
  end

  def formatted_start_date
    I18n.l(start_date, format: :long) if start_date
  end

  def url
    "#{AppMode.payroll.web_uri}/app/payroll/employees/#{employee_id}"
  end

  # Validates the social security number (SSN) of the employee.
  #
  # @return [Boolean] true if the SSN is valid, false otherwise
  def validate_ssn
    validity = Employee.ssn_valid?(ssn)
    errors.add(:ssn, I18n.t('general.is_invalid')) unless validity
    validity
  end

  # Returns the date of birth of the employee.
  # This should be replaced once the decyrption there is fixed
  #
  # @return [Date] the date of birth of the employee
  def date_of_birth
    self.class.find_employee_procedure(company_id: company_id, employee_id: employee_id,
                                       active: '03')['date_of_birth'] || super
  rescue StandardError
    super
  end

  private

  # Validates that the termination reason is present if the termination date is present.
  #
  # @return [void]
  def termination_reason_is_with_termination_date
    return unless termination_date.blank? && termination_reason.present?

    errors.add(:termination_reason, "can't be present without termination date")
  end

  # Validates the federal and provincial tax categories.
  #
  # @return [void]
  def validate_tax_categories
    return if federal_tax_category.blank? && provincial_tax_category.blank?

    unless TypesMaster.federal_tax_codes.exists?(type: federal_tax_category)
      errors.add(:federal_tax_category, 'is not a valid federal tax category')
    end

    return if TypesMaster.provincial_tax_codes.exists?(type: provincial_tax_category)

    errors.add(:provincial_tax_category, 'is not a valid provincial tax category')
  end

  # Validates the dates related to the employee.
  #
  # @return [void]
  def validate_dates
    today = Time.zone.today.beginning_of_day

    date_of_birth = parse_date_if_needed(self.date_of_birth)
    if date_of_birth.present? && date_of_birth.beginning_of_day > today - MINIMUM_AGE.years
      errors.add(:date_of_birth, "must be at least #{MINIMUM_AGE} years old")
    end

    start_date = parse_date_if_needed(self.start_date)
    errors.add(:start_date, "can't be in the future") if start_date.present? && start_date.beginning_of_day > today

    termination_date = parse_date_if_needed(self.termination_date)
    return if termination_date.blank?

    termination_date_only = termination_date.beginning_of_day

    if start_date.present? && termination_date_only < start_date.beginning_of_day
      errors.add(:termination_date, "can't be before start date")
    end

    if date_of_birth.present? && termination_date_only < date_of_birth.beginning_of_day
      errors.add(:termination_date, "can't be before date of birth")
    end

    errors.add(:termination_date, "can't be in the future") if termination_date_only > today

    return if termination_reason.present?

    errors.add(:termination_reason, "can't be blank")
  rescue StandardError => e
    errors.add(:base, e.message)
  end

  # Returns the email of the employee for the employee portal login.
  # It is the email of the ERP user associated with the employee.
  #
  # @return [String] the email of the employee for the employee portal login
  def 
    erp_user&.email
  end

  # Returns whether the employee portal login is enabled for the employee.
  # It is enabled if the ERP user associated with the employee is active.
  #
  # @return [Boolean] true if the employee portal login is enabled, false otherwise
  def 
    erp_user&.active
  end

  def validate_erp_user_id
    user_with_email = ErpUser.accessible.find_by(email: )
    return unless user_with_email

    user_has_other_employees_for_company = user_with_email.employees.where(
      company_id: company_id
    ).where.not(
      employee_id: employee_id
    ).exists?
    return unless user_has_other_employees_for_company

    errors.add(:employee_portal_login_email, 'is already in use by another employee in this company')
  end

  # Parses a date if it is a string.
  #
  # @param date [String, Date, DateTime] the date to parse
  # @return [DateTime, nil] the parsed date, or nil if the date is not present
  def parse_date_if_needed(date)
    if date.is_a?(String)
      DateTime.parse(date)
    else
      date
    end
  end
end

#employee_role ⇒ Object

Returns the value of attribute employee_role.



71
72
73
 
# File 'app/models/employee.rb', line 71

def employee_role
  @employee_role
end

#phone_no ⇒ String

Returns the phone number of the employee.

Returns:

  • (String) —

    the phone number of the employee



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
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
102
103
104
105
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
292
293
294
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
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
 
# File 'app/models/employee.rb', line 25

class Employee < ApplicationRecord
  include HasAddress
  include EmployeesProcedures

  PAY_COLUMNS = %w[
    accepts_direct_deposit
    provincial_tax_category
    federal_tax_category
    vacation_percentage
    health_insurance_amount
    dental_insurance_amount
    rrsp_amount
    long_term_disability_insurance_amount
    life_insurance_amount
    additional_federal_taxes_retained
    additional_provincial_taxes_retained
    serious_illness_amount
    cotisation_syndicale_amount
    employer_health_insurance_contribution_amount
    employer_dental_insurance_contribution_amount
    employer_life_insurance_contribution_amount
  ].freeze

  MINIMUM_AGE = 15

  self.table_name = 'employees'
  self.primary_key = 'employee_id'

  ENCRYPTED_FIELDS = %w[
    employee_name
    ssn
    phone_no
    business_email
    google_address
    address_line1
    address_line2
    city
    personal_email
    mobile_phone_no
    fax_no
    postal_code
    date_of_birth
  ].freeze

  define_decrypted_attributes(ENCRYPTED_FIELDS)

  attr_accessor :bank_account, :employee_role, :province_code

  belongs_to :company
  belongs_to :erp_user, optional: true
  belongs_to :province

  has_many :employee_timesheets, dependent: :destroy
  has_many :employee_payroll_transactions, dependent: :destroy
  has_many :employee_roles, dependent: :destroy
  has_many :employee_payroll_calculations, dependent: :destroy
  has_many :integration_references, as: :reference, dependent: :destroy
  has_many :reference_events, as: :reference, dependent: nil

  validates :employee_name, presence: true, length: { maximum: 50 },
                            format: {
                              with: /\A[a-zA-Z\s\-.]+\z/
                              # message: I18n.t('payroll.employees.form.ssn_validation')
                            }
  validates :date_of_birth, presence: true
  validates :start_date, presence: false
  validates :vacation_percentage, numericality: { greater_than_or_equal_to: 0, less_than_or_equal_to: 100 },
                                  presence: false
  validates :health_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :dental_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :cotisation_syndicale_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :child_support_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :rrsp_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :long_term_disability_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :life_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :business_email, uniqueness: { scope: :company_id }, allow_blank: true
  validates :personal_email, uniqueness: { scope: :company_id }, allow_blank: false
  validates :phone_no, uniqueness: { scope: :company_id }, allow_blank: true
  validates :default_language, inclusion: { in: %w[E F], message: "must be 'E' or 'F'" }

  validate :validate_tax_categories
  validate :validate_dates
  validate :termination_reason_is_with_termination_date
  validate :validate_ssn
  validate :validate_erp_user_id, if: :erp_user_id_changed?

  validates :address_line1, presence: true, length: { maximum: 300 }
  validates :address_line2, presence: false, length: { maximum: 300 }
  validates :city, presence: true, length: { maximum: 300 }
  validates :postal_code, presence: true, length: { maximum: 7 },
                          format: { with: /\A[A-Za-z]\d[A-Za-z] \d[A-Za-z]\d\z/, message: 'must be in the format A1A 1A1' }
  validates :federal_tax_category, presence: true
  validates :provincial_tax_category, presence: true

  scope :without_timesheets_for_period, lambda { |payroll_period_id|
    joins('LEFT JOIN employee_time_sheets ON employees.employee_id = employee_time_sheets.employee_id')
      .where(
        'employee_time_sheets.payroll_period_id IS NULL OR employee_time_sheets.payroll_period_id != ?',
        payroll_period_id
      )
      .distinct
  }

  def name
    employee_name
  end

  # Checks if any of the pay columns have changed.
  #
  # @return [Boolean] true if any pay columns have changed, false otherwise
  def pay_columns_changed?
    changes.keys.any? { |column| PAY_COLUMNS.include?(column) }
  end

  # Counts the number of timesheets for a specific payroll period.
  #
  # @param payroll_period_id [Integer] the ID of the payroll period
  # @return [Integer] the number of timesheets for the specified payroll period
  def timesheets_count_for_period(payroll_period_id)
    employee_timesheets.where(payroll_period_id: payroll_period_id).count
  end

  # Returns a description of the employee's roles.
  #
  # @return [String] a comma-separated list of role descriptions
  def role_description
    employee_roles.select(:description).map(&:description).join(', ')
  end

  class << self
    # Retrieves employee options using a stored procedure.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_procedure(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_procedure(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      result_type = if must_have_timesheets
                      '01'
                    else
                      '01' # should be fixed
                    end
      ActiveRecord::Base.execute_procedure(
        'pr_get_employees_for_dropdown',
        nil, # employee_id
        payroll_period_id,
        company_id,
        nil, # payroll_transaction_header_id
        result_type
      )
    end

    # Retrieves employee options using a SQL query.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_sql(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_sql(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      sql = <<-SQL.squish
        OPEN SYMMETRIC KEY PaieHubSymmetricKey DECRYPTION BY CERTIFICATE PaieHubCertificate;
        SELECT
          employees.employee_id,
          CONVERT(NVARCHAR(300), DecryptByKey(employees.[employee_name])) AS employee_name,
          COUNT(employee_time_sheets.employee_id) AS timesheets_count
        FROM
          employees
        LEFT JOIN
          employee_time_sheets
          ON employees.employee_id = employee_time_sheets.employee_id
        WHERE
          employees.company_id = ?
      SQL

      if payroll_period_id.positive?
        sql += <<-SQL.squish
          AND employee_time_sheets.payroll_period_id = ?
        SQL
      end

      sql += <<-SQL.squish
        GROUP BY
          employees.employee_id,
          employee_name
      SQL

      if must_have_timesheets
        sql += <<-SQL.squish
          HAVING COUNT(employee_time_sheets.employee_id) > 0
        SQL
      end

      sql += <<-SQL.squish
        ORDER BY
          timesheets_count DESC
      SQL

      sanitized_query = if payroll_period_id.positive?
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id, payroll_period_id])
                        else
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id])
                        end

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a
    end

    # Validates the format of a social security number (SSN).
    #
    # @param ssn [String] the social security number to validate
    # @return [Boolean] true if the SSN is valid, false otherwise
    # @example
    #   Employee.ssn_valid?('123-45-6789')
    def ssn_valid?(ssn)
      ssn = ssn.to_s.strip
      return false if ssn.blank?
      return false unless ssn.match?(/^\d{9}$|^\d{3}-\d{3}-\d{3}$/)

      digits = ssn.scan(/\d/).join

      sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT dbo.fu_validate_ssn(?) as value', digits])

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a.dig(0, 'value')
    end
  end

  # Returns the bank accounts associated with the employee.
  #
  # @return [ActiveRecord::Relation] the bank accounts associated with the employee
  def bank_accounts
    BankAccount.where(
      source: 'Employee',
      reference_id: employee_id
    )
  end

  # Returns the default bank account of the employee.
  #
  # @return [BankAccount, nil] the default bank account of the employee, or nil if none exists
  def 
    bank_accounts.find_by(is_default: true)
  end

  # Returns the locale of the employee.
  #
  # @return [String] the locale of the employee
  def locale
    Language.new(default_language).locale
  end

  def formatted_start_date
    I18n.l(start_date, format: :long) if start_date
  end

  def url
    "#{AppMode.payroll.web_uri}/app/payroll/employees/#{employee_id}"
  end

  # Validates the social security number (SSN) of the employee.
  #
  # @return [Boolean] true if the SSN is valid, false otherwise
  def validate_ssn
    validity = Employee.ssn_valid?(ssn)
    errors.add(:ssn, I18n.t('general.is_invalid')) unless validity
    validity
  end

  # Returns the date of birth of the employee.
  # This should be replaced once the decyrption there is fixed
  #
  # @return [Date] the date of birth of the employee
  def date_of_birth
    self.class.find_employee_procedure(company_id: company_id, employee_id: employee_id,
                                       active: '03')['date_of_birth'] || super
  rescue StandardError
    super
  end

  private

  # Validates that the termination reason is present if the termination date is present.
  #
  # @return [void]
  def termination_reason_is_with_termination_date
    return unless termination_date.blank? && termination_reason.present?

    errors.add(:termination_reason, "can't be present without termination date")
  end

  # Validates the federal and provincial tax categories.
  #
  # @return [void]
  def validate_tax_categories
    return if federal_tax_category.blank? && provincial_tax_category.blank?

    unless TypesMaster.federal_tax_codes.exists?(type: federal_tax_category)
      errors.add(:federal_tax_category, 'is not a valid federal tax category')
    end

    return if TypesMaster.provincial_tax_codes.exists?(type: provincial_tax_category)

    errors.add(:provincial_tax_category, 'is not a valid provincial tax category')
  end

  # Validates the dates related to the employee.
  #
  # @return [void]
  def validate_dates
    today = Time.zone.today.beginning_of_day

    date_of_birth = parse_date_if_needed(self.date_of_birth)
    if date_of_birth.present? && date_of_birth.beginning_of_day > today - MINIMUM_AGE.years
      errors.add(:date_of_birth, "must be at least #{MINIMUM_AGE} years old")
    end

    start_date = parse_date_if_needed(self.start_date)
    errors.add(:start_date, "can't be in the future") if start_date.present? && start_date.beginning_of_day > today

    termination_date = parse_date_if_needed(self.termination_date)
    return if termination_date.blank?

    termination_date_only = termination_date.beginning_of_day

    if start_date.present? && termination_date_only < start_date.beginning_of_day
      errors.add(:termination_date, "can't be before start date")
    end

    if date_of_birth.present? && termination_date_only < date_of_birth.beginning_of_day
      errors.add(:termination_date, "can't be before date of birth")
    end

    errors.add(:termination_date, "can't be in the future") if termination_date_only > today

    return if termination_reason.present?

    errors.add(:termination_reason, "can't be blank")
  rescue StandardError => e
    errors.add(:base, e.message)
  end

  # Returns the email of the employee for the employee portal login.
  # It is the email of the ERP user associated with the employee.
  #
  # @return [String] the email of the employee for the employee portal login
  def 
    erp_user&.email
  end

  # Returns whether the employee portal login is enabled for the employee.
  # It is enabled if the ERP user associated with the employee is active.
  #
  # @return [Boolean] true if the employee portal login is enabled, false otherwise
  def 
    erp_user&.active
  end

  def validate_erp_user_id
    user_with_email = ErpUser.accessible.find_by(email: )
    return unless user_with_email

    user_has_other_employees_for_company = user_with_email.employees.where(
      company_id: company_id
    ).where.not(
      employee_id: employee_id
    ).exists?
    return unless user_has_other_employees_for_company

    errors.add(:employee_portal_login_email, 'is already in use by another employee in this company')
  end

  # Parses a date if it is a string.
  #
  # @param date [String, Date, DateTime] the date to parse
  # @return [DateTime, nil] the parsed date, or nil if the date is not present
  def parse_date_if_needed(date)
    if date.is_a?(String)
      DateTime.parse(date)
    else
      date
    end
  end
end

#postal_code ⇒ String

Returns the postal code of the employee.

Returns:

  • (String) —

    the postal code of the employee



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
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
102
103
104
105
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
292
293
294
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
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
 
# File 'app/models/employee.rb', line 25

class Employee < ApplicationRecord
  include HasAddress
  include EmployeesProcedures

  PAY_COLUMNS = %w[
    accepts_direct_deposit
    provincial_tax_category
    federal_tax_category
    vacation_percentage
    health_insurance_amount
    dental_insurance_amount
    rrsp_amount
    long_term_disability_insurance_amount
    life_insurance_amount
    additional_federal_taxes_retained
    additional_provincial_taxes_retained
    serious_illness_amount
    cotisation_syndicale_amount
    employer_health_insurance_contribution_amount
    employer_dental_insurance_contribution_amount
    employer_life_insurance_contribution_amount
  ].freeze

  MINIMUM_AGE = 15

  self.table_name = 'employees'
  self.primary_key = 'employee_id'

  ENCRYPTED_FIELDS = %w[
    employee_name
    ssn
    phone_no
    business_email
    google_address
    address_line1
    address_line2
    city
    personal_email
    mobile_phone_no
    fax_no
    postal_code
    date_of_birth
  ].freeze

  define_decrypted_attributes(ENCRYPTED_FIELDS)

  attr_accessor :bank_account, :employee_role, :province_code

  belongs_to :company
  belongs_to :erp_user, optional: true
  belongs_to :province

  has_many :employee_timesheets, dependent: :destroy
  has_many :employee_payroll_transactions, dependent: :destroy
  has_many :employee_roles, dependent: :destroy
  has_many :employee_payroll_calculations, dependent: :destroy
  has_many :integration_references, as: :reference, dependent: :destroy
  has_many :reference_events, as: :reference, dependent: nil

  validates :employee_name, presence: true, length: { maximum: 50 },
                            format: {
                              with: /\A[a-zA-Z\s\-.]+\z/
                              # message: I18n.t('payroll.employees.form.ssn_validation')
                            }
  validates :date_of_birth, presence: true
  validates :start_date, presence: false
  validates :vacation_percentage, numericality: { greater_than_or_equal_to: 0, less_than_or_equal_to: 100 },
                                  presence: false
  validates :health_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :dental_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :cotisation_syndicale_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :child_support_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :rrsp_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :long_term_disability_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :life_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :business_email, uniqueness: { scope: :company_id }, allow_blank: true
  validates :personal_email, uniqueness: { scope: :company_id }, allow_blank: false
  validates :phone_no, uniqueness: { scope: :company_id }, allow_blank: true
  validates :default_language, inclusion: { in: %w[E F], message: "must be 'E' or 'F'" }

  validate :validate_tax_categories
  validate :validate_dates
  validate :termination_reason_is_with_termination_date
  validate :validate_ssn
  validate :validate_erp_user_id, if: :erp_user_id_changed?

  validates :address_line1, presence: true, length: { maximum: 300 }
  validates :address_line2, presence: false, length: { maximum: 300 }
  validates :city, presence: true, length: { maximum: 300 }
  validates :postal_code, presence: true, length: { maximum: 7 },
                          format: { with: /\A[A-Za-z]\d[A-Za-z] \d[A-Za-z]\d\z/, message: 'must be in the format A1A 1A1' }
  validates :federal_tax_category, presence: true
  validates :provincial_tax_category, presence: true

  scope :without_timesheets_for_period, lambda { |payroll_period_id|
    joins('LEFT JOIN employee_time_sheets ON employees.employee_id = employee_time_sheets.employee_id')
      .where(
        'employee_time_sheets.payroll_period_id IS NULL OR employee_time_sheets.payroll_period_id != ?',
        payroll_period_id
      )
      .distinct
  }

  def name
    employee_name
  end

  # Checks if any of the pay columns have changed.
  #
  # @return [Boolean] true if any pay columns have changed, false otherwise
  def pay_columns_changed?
    changes.keys.any? { |column| PAY_COLUMNS.include?(column) }
  end

  # Counts the number of timesheets for a specific payroll period.
  #
  # @param payroll_period_id [Integer] the ID of the payroll period
  # @return [Integer] the number of timesheets for the specified payroll period
  def timesheets_count_for_period(payroll_period_id)
    employee_timesheets.where(payroll_period_id: payroll_period_id).count
  end

  # Returns a description of the employee's roles.
  #
  # @return [String] a comma-separated list of role descriptions
  def role_description
    employee_roles.select(:description).map(&:description).join(', ')
  end

  class << self
    # Retrieves employee options using a stored procedure.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_procedure(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_procedure(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      result_type = if must_have_timesheets
                      '01'
                    else
                      '01' # should be fixed
                    end
      ActiveRecord::Base.execute_procedure(
        'pr_get_employees_for_dropdown',
        nil, # employee_id
        payroll_period_id,
        company_id,
        nil, # payroll_transaction_header_id
        result_type
      )
    end

    # Retrieves employee options using a SQL query.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_sql(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_sql(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      sql = <<-SQL.squish
        OPEN SYMMETRIC KEY PaieHubSymmetricKey DECRYPTION BY CERTIFICATE PaieHubCertificate;
        SELECT
          employees.employee_id,
          CONVERT(NVARCHAR(300), DecryptByKey(employees.[employee_name])) AS employee_name,
          COUNT(employee_time_sheets.employee_id) AS timesheets_count
        FROM
          employees
        LEFT JOIN
          employee_time_sheets
          ON employees.employee_id = employee_time_sheets.employee_id
        WHERE
          employees.company_id = ?
      SQL

      if payroll_period_id.positive?
        sql += <<-SQL.squish
          AND employee_time_sheets.payroll_period_id = ?
        SQL
      end

      sql += <<-SQL.squish
        GROUP BY
          employees.employee_id,
          employee_name
      SQL

      if must_have_timesheets
        sql += <<-SQL.squish
          HAVING COUNT(employee_time_sheets.employee_id) > 0
        SQL
      end

      sql += <<-SQL.squish
        ORDER BY
          timesheets_count DESC
      SQL

      sanitized_query = if payroll_period_id.positive?
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id, payroll_period_id])
                        else
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id])
                        end

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a
    end

    # Validates the format of a social security number (SSN).
    #
    # @param ssn [String] the social security number to validate
    # @return [Boolean] true if the SSN is valid, false otherwise
    # @example
    #   Employee.ssn_valid?('123-45-6789')
    def ssn_valid?(ssn)
      ssn = ssn.to_s.strip
      return false if ssn.blank?
      return false unless ssn.match?(/^\d{9}$|^\d{3}-\d{3}-\d{3}$/)

      digits = ssn.scan(/\d/).join

      sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT dbo.fu_validate_ssn(?) as value', digits])

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a.dig(0, 'value')
    end
  end

  # Returns the bank accounts associated with the employee.
  #
  # @return [ActiveRecord::Relation] the bank accounts associated with the employee
  def bank_accounts
    BankAccount.where(
      source: 'Employee',
      reference_id: employee_id
    )
  end

  # Returns the default bank account of the employee.
  #
  # @return [BankAccount, nil] the default bank account of the employee, or nil if none exists
  def 
    bank_accounts.find_by(is_default: true)
  end

  # Returns the locale of the employee.
  #
  # @return [String] the locale of the employee
  def locale
    Language.new(default_language).locale
  end

  def formatted_start_date
    I18n.l(start_date, format: :long) if start_date
  end

  def url
    "#{AppMode.payroll.web_uri}/app/payroll/employees/#{employee_id}"
  end

  # Validates the social security number (SSN) of the employee.
  #
  # @return [Boolean] true if the SSN is valid, false otherwise
  def validate_ssn
    validity = Employee.ssn_valid?(ssn)
    errors.add(:ssn, I18n.t('general.is_invalid')) unless validity
    validity
  end

  # Returns the date of birth of the employee.
  # This should be replaced once the decyrption there is fixed
  #
  # @return [Date] the date of birth of the employee
  def date_of_birth
    self.class.find_employee_procedure(company_id: company_id, employee_id: employee_id,
                                       active: '03')['date_of_birth'] || super
  rescue StandardError
    super
  end

  private

  # Validates that the termination reason is present if the termination date is present.
  #
  # @return [void]
  def termination_reason_is_with_termination_date
    return unless termination_date.blank? && termination_reason.present?

    errors.add(:termination_reason, "can't be present without termination date")
  end

  # Validates the federal and provincial tax categories.
  #
  # @return [void]
  def validate_tax_categories
    return if federal_tax_category.blank? && provincial_tax_category.blank?

    unless TypesMaster.federal_tax_codes.exists?(type: federal_tax_category)
      errors.add(:federal_tax_category, 'is not a valid federal tax category')
    end

    return if TypesMaster.provincial_tax_codes.exists?(type: provincial_tax_category)

    errors.add(:provincial_tax_category, 'is not a valid provincial tax category')
  end

  # Validates the dates related to the employee.
  #
  # @return [void]
  def validate_dates
    today = Time.zone.today.beginning_of_day

    date_of_birth = parse_date_if_needed(self.date_of_birth)
    if date_of_birth.present? && date_of_birth.beginning_of_day > today - MINIMUM_AGE.years
      errors.add(:date_of_birth, "must be at least #{MINIMUM_AGE} years old")
    end

    start_date = parse_date_if_needed(self.start_date)
    errors.add(:start_date, "can't be in the future") if start_date.present? && start_date.beginning_of_day > today

    termination_date = parse_date_if_needed(self.termination_date)
    return if termination_date.blank?

    termination_date_only = termination_date.beginning_of_day

    if start_date.present? && termination_date_only < start_date.beginning_of_day
      errors.add(:termination_date, "can't be before start date")
    end

    if date_of_birth.present? && termination_date_only < date_of_birth.beginning_of_day
      errors.add(:termination_date, "can't be before date of birth")
    end

    errors.add(:termination_date, "can't be in the future") if termination_date_only > today

    return if termination_reason.present?

    errors.add(:termination_reason, "can't be blank")
  rescue StandardError => e
    errors.add(:base, e.message)
  end

  # Returns the email of the employee for the employee portal login.
  # It is the email of the ERP user associated with the employee.
  #
  # @return [String] the email of the employee for the employee portal login
  def 
    erp_user&.email
  end

  # Returns whether the employee portal login is enabled for the employee.
  # It is enabled if the ERP user associated with the employee is active.
  #
  # @return [Boolean] true if the employee portal login is enabled, false otherwise
  def 
    erp_user&.active
  end

  def validate_erp_user_id
    user_with_email = ErpUser.accessible.find_by(email: )
    return unless user_with_email

    user_has_other_employees_for_company = user_with_email.employees.where(
      company_id: company_id
    ).where.not(
      employee_id: employee_id
    ).exists?
    return unless user_has_other_employees_for_company

    errors.add(:employee_portal_login_email, 'is already in use by another employee in this company')
  end

  # Parses a date if it is a string.
  #
  # @param date [String, Date, DateTime] the date to parse
  # @return [DateTime, nil] the parsed date, or nil if the date is not present
  def parse_date_if_needed(date)
    if date.is_a?(String)
      DateTime.parse(date)
    else
      date
    end
  end
end

#province_code ⇒ Object

Returns the value of attribute province_code.



71
72
73
 
# File 'app/models/employee.rb', line 71

def province_code
  @province_code
end

#ssn ⇒ String

Returns the social security number of the employee.

Returns:

  • (String) —

    the social security number of the employee



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
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
102
103
104
105
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
292
293
294
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
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
 
# File 'app/models/employee.rb', line 25

class Employee < ApplicationRecord
  include HasAddress
  include EmployeesProcedures

  PAY_COLUMNS = %w[
    accepts_direct_deposit
    provincial_tax_category
    federal_tax_category
    vacation_percentage
    health_insurance_amount
    dental_insurance_amount
    rrsp_amount
    long_term_disability_insurance_amount
    life_insurance_amount
    additional_federal_taxes_retained
    additional_provincial_taxes_retained
    serious_illness_amount
    cotisation_syndicale_amount
    employer_health_insurance_contribution_amount
    employer_dental_insurance_contribution_amount
    employer_life_insurance_contribution_amount
  ].freeze

  MINIMUM_AGE = 15

  self.table_name = 'employees'
  self.primary_key = 'employee_id'

  ENCRYPTED_FIELDS = %w[
    employee_name
    ssn
    phone_no
    business_email
    google_address
    address_line1
    address_line2
    city
    personal_email
    mobile_phone_no
    fax_no
    postal_code
    date_of_birth
  ].freeze

  define_decrypted_attributes(ENCRYPTED_FIELDS)

  attr_accessor :bank_account, :employee_role, :province_code

  belongs_to :company
  belongs_to :erp_user, optional: true
  belongs_to :province

  has_many :employee_timesheets, dependent: :destroy
  has_many :employee_payroll_transactions, dependent: :destroy
  has_many :employee_roles, dependent: :destroy
  has_many :employee_payroll_calculations, dependent: :destroy
  has_many :integration_references, as: :reference, dependent: :destroy
  has_many :reference_events, as: :reference, dependent: nil

  validates :employee_name, presence: true, length: { maximum: 50 },
                            format: {
                              with: /\A[a-zA-Z\s\-.]+\z/
                              # message: I18n.t('payroll.employees.form.ssn_validation')
                            }
  validates :date_of_birth, presence: true
  validates :start_date, presence: false
  validates :vacation_percentage, numericality: { greater_than_or_equal_to: 0, less_than_or_equal_to: 100 },
                                  presence: false
  validates :health_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :dental_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :cotisation_syndicale_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :child_support_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :rrsp_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :long_term_disability_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :life_insurance_amount, numericality: { greater_than_or_equal_to: 0 }, presence: false
  validates :business_email, uniqueness: { scope: :company_id }, allow_blank: true
  validates :personal_email, uniqueness: { scope: :company_id }, allow_blank: false
  validates :phone_no, uniqueness: { scope: :company_id }, allow_blank: true
  validates :default_language, inclusion: { in: %w[E F], message: "must be 'E' or 'F'" }

  validate :validate_tax_categories
  validate :validate_dates
  validate :termination_reason_is_with_termination_date
  validate :validate_ssn
  validate :validate_erp_user_id, if: :erp_user_id_changed?

  validates :address_line1, presence: true, length: { maximum: 300 }
  validates :address_line2, presence: false, length: { maximum: 300 }
  validates :city, presence: true, length: { maximum: 300 }
  validates :postal_code, presence: true, length: { maximum: 7 },
                          format: { with: /\A[A-Za-z]\d[A-Za-z] \d[A-Za-z]\d\z/, message: 'must be in the format A1A 1A1' }
  validates :federal_tax_category, presence: true
  validates :provincial_tax_category, presence: true

  scope :without_timesheets_for_period, lambda { |payroll_period_id|
    joins('LEFT JOIN employee_time_sheets ON employees.employee_id = employee_time_sheets.employee_id')
      .where(
        'employee_time_sheets.payroll_period_id IS NULL OR employee_time_sheets.payroll_period_id != ?',
        payroll_period_id
      )
      .distinct
  }

  def name
    employee_name
  end

  # Checks if any of the pay columns have changed.
  #
  # @return [Boolean] true if any pay columns have changed, false otherwise
  def pay_columns_changed?
    changes.keys.any? { |column| PAY_COLUMNS.include?(column) }
  end

  # Counts the number of timesheets for a specific payroll period.
  #
  # @param payroll_period_id [Integer] the ID of the payroll period
  # @return [Integer] the number of timesheets for the specified payroll period
  def timesheets_count_for_period(payroll_period_id)
    employee_timesheets.where(payroll_period_id: payroll_period_id).count
  end

  # Returns a description of the employee's roles.
  #
  # @return [String] a comma-separated list of role descriptions
  def role_description
    employee_roles.select(:description).map(&:description).join(', ')
  end

  class << self
    # Retrieves employee options using a stored procedure.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_procedure(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_procedure(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      result_type = if must_have_timesheets
                      '01'
                    else
                      '01' # should be fixed
                    end
      ActiveRecord::Base.execute_procedure(
        'pr_get_employees_for_dropdown',
        nil, # employee_id
        payroll_period_id,
        company_id,
        nil, # payroll_transaction_header_id
        result_type
      )
    end

    # Retrieves employee options using a SQL query.
    #
    # @param company_id [Integer] the ID of the company
    # @param payroll_period_id [Integer] the ID of the payroll period (default: 0)
    # @param must_have_timesheets [Boolean] whether the employee must have timesheets (default: false)
    # @return [Array<Hash>] an array of employee options
    # @example
    #   Employee.options_sql(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)
    def options_sql(company_id:, payroll_period_id: 0, must_have_timesheets: false)
      sql = <<-SQL.squish
        OPEN SYMMETRIC KEY PaieHubSymmetricKey DECRYPTION BY CERTIFICATE PaieHubCertificate;
        SELECT
          employees.employee_id,
          CONVERT(NVARCHAR(300), DecryptByKey(employees.[employee_name])) AS employee_name,
          COUNT(employee_time_sheets.employee_id) AS timesheets_count
        FROM
          employees
        LEFT JOIN
          employee_time_sheets
          ON employees.employee_id = employee_time_sheets.employee_id
        WHERE
          employees.company_id = ?
      SQL

      if payroll_period_id.positive?
        sql += <<-SQL.squish
          AND employee_time_sheets.payroll_period_id = ?
        SQL
      end

      sql += <<-SQL.squish
        GROUP BY
          employees.employee_id,
          employee_name
      SQL

      if must_have_timesheets
        sql += <<-SQL.squish
          HAVING COUNT(employee_time_sheets.employee_id) > 0
        SQL
      end

      sql += <<-SQL.squish
        ORDER BY
          timesheets_count DESC
      SQL

      sanitized_query = if payroll_period_id.positive?
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id, payroll_period_id])
                        else
                          ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id])
                        end

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a
    end

    # Validates the format of a social security number (SSN).
    #
    # @param ssn [String] the social security number to validate
    # @return [Boolean] true if the SSN is valid, false otherwise
    # @example
    #   Employee.ssn_valid?('123-45-6789')
    def ssn_valid?(ssn)
      ssn = ssn.to_s.strip
      return false if ssn.blank?
      return false unless ssn.match?(/^\d{9}$|^\d{3}-\d{3}-\d{3}$/)

      digits = ssn.scan(/\d/).join

      sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT dbo.fu_validate_ssn(?) as value', digits])

      ActiveRecord::Base.connection.exec_query(sanitized_query).to_a.dig(0, 'value')
    end
  end

  # Returns the bank accounts associated with the employee.
  #
  # @return [ActiveRecord::Relation] the bank accounts associated with the employee
  def bank_accounts
    BankAccount.where(
      source: 'Employee',
      reference_id: employee_id
    )
  end

  # Returns the default bank account of the employee.
  #
  # @return [BankAccount, nil] the default bank account of the employee, or nil if none exists
  def 
    bank_accounts.find_by(is_default: true)
  end

  # Returns the locale of the employee.
  #
  # @return [String] the locale of the employee
  def locale
    Language.new(default_language).locale
  end

  def formatted_start_date
    I18n.l(start_date, format: :long) if start_date
  end

  def url
    "#{AppMode.payroll.web_uri}/app/payroll/employees/#{employee_id}"
  end

  # Validates the social security number (SSN) of the employee.
  #
  # @return [Boolean] true if the SSN is valid, false otherwise
  def validate_ssn
    validity = Employee.ssn_valid?(ssn)
    errors.add(:ssn, I18n.t('general.is_invalid')) unless validity
    validity
  end

  # Returns the date of birth of the employee.
  # This should be replaced once the decyrption there is fixed
  #
  # @return [Date] the date of birth of the employee
  def date_of_birth
    self.class.find_employee_procedure(company_id: company_id, employee_id: employee_id,
                                       active: '03')['date_of_birth'] || super
  rescue StandardError
    super
  end

  private

  # Validates that the termination reason is present if the termination date is present.
  #
  # @return [void]
  def termination_reason_is_with_termination_date
    return unless termination_date.blank? && termination_reason.present?

    errors.add(:termination_reason, "can't be present without termination date")
  end

  # Validates the federal and provincial tax categories.
  #
  # @return [void]
  def validate_tax_categories
    return if federal_tax_category.blank? && provincial_tax_category.blank?

    unless TypesMaster.federal_tax_codes.exists?(type: federal_tax_category)
      errors.add(:federal_tax_category, 'is not a valid federal tax category')
    end

    return if TypesMaster.provincial_tax_codes.exists?(type: provincial_tax_category)

    errors.add(:provincial_tax_category, 'is not a valid provincial tax category')
  end

  # Validates the dates related to the employee.
  #
  # @return [void]
  def validate_dates
    today = Time.zone.today.beginning_of_day

    date_of_birth = parse_date_if_needed(self.date_of_birth)
    if date_of_birth.present? && date_of_birth.beginning_of_day > today - MINIMUM_AGE.years
      errors.add(:date_of_birth, "must be at least #{MINIMUM_AGE} years old")
    end

    start_date = parse_date_if_needed(self.start_date)
    errors.add(:start_date, "can't be in the future") if start_date.present? && start_date.beginning_of_day > today

    termination_date = parse_date_if_needed(self.termination_date)
    return if termination_date.blank?

    termination_date_only = termination_date.beginning_of_day

    if start_date.present? && termination_date_only < start_date.beginning_of_day
      errors.add(:termination_date, "can't be before start date")
    end

    if date_of_birth.present? && termination_date_only < date_of_birth.beginning_of_day
      errors.add(:termination_date, "can't be before date of birth")
    end

    errors.add(:termination_date, "can't be in the future") if termination_date_only > today

    return if termination_reason.present?

    errors.add(:termination_reason, "can't be blank")
  rescue StandardError => e
    errors.add(:base, e.message)
  end

  # Returns the email of the employee for the employee portal login.
  # It is the email of the ERP user associated with the employee.
  #
  # @return [String] the email of the employee for the employee portal login
  def 
    erp_user&.email
  end

  # Returns whether the employee portal login is enabled for the employee.
  # It is enabled if the ERP user associated with the employee is active.
  #
  # @return [Boolean] true if the employee portal login is enabled, false otherwise
  def 
    erp_user&.active
  end

  def validate_erp_user_id
    user_with_email = ErpUser.accessible.find_by(email: )
    return unless user_with_email

    user_has_other_employees_for_company = user_with_email.employees.where(
      company_id: company_id
    ).where.not(
      employee_id: employee_id
    ).exists?
    return unless user_has_other_employees_for_company

    errors.add(:employee_portal_login_email, 'is already in use by another employee in this company')
  end

  # Parses a date if it is a string.
  #
  # @param date [String, Date, DateTime] the date to parse
  # @return [DateTime, nil] the parsed date, or nil if the date is not present
  def parse_date_if_needed(date)
    if date.is_a?(String)
      DateTime.parse(date)
    else
      date
    end
  end
end

Class Method Details

.options_procedure(company_id:, payroll_period_id: 0, must_have_timesheets: false) ⇒ Array<Hash>

Retrieves employee options using a stored procedure.

Examples:

Employee.options_procedure(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)

Parameters:

  • company_id (Integer) —

    the ID of the company

  • payroll_period_id (Integer) (defaults to: 0) —

    the ID of the payroll period (default: 0)

  • must_have_timesheets (Boolean) (defaults to: false) —

    whether the employee must have timesheets (default: false)

Returns:

  • (Array<Hash>) —

    an array of employee options



163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
 
# File 'app/models/employee.rb', line 163

def options_procedure(company_id:, payroll_period_id: 0, must_have_timesheets: false)
  result_type = if must_have_timesheets
                  '01'
                else
                  '01' # should be fixed
                end
  ActiveRecord::Base.execute_procedure(
    'pr_get_employees_for_dropdown',
    nil, # employee_id
    payroll_period_id,
    company_id,
    nil, # payroll_transaction_header_id
    result_type
  )
end

.options_sql(company_id:, payroll_period_id: 0, must_have_timesheets: false) ⇒ Array<Hash>

Retrieves employee options using a SQL query.

Examples:

Employee.options_sql(company_id: 1, payroll_period_id: 1, must_have_timesheets: true)

Parameters:

  • company_id (Integer) —

    the ID of the company

  • payroll_period_id (Integer) (defaults to: 0) —

    the ID of the payroll period (default: 0)

  • must_have_timesheets (Boolean) (defaults to: false) —

    whether the employee must have timesheets (default: false)

Returns:

  • (Array<Hash>) —

    an array of employee options



187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
 
# File 'app/models/employee.rb', line 187

def options_sql(company_id:, payroll_period_id: 0, must_have_timesheets: false)
  sql = <<-SQL.squish
    OPEN SYMMETRIC KEY PaieHubSymmetricKey DECRYPTION BY CERTIFICATE PaieHubCertificate;
    SELECT
      employees.employee_id,
      CONVERT(NVARCHAR(300), DecryptByKey(employees.[employee_name])) AS employee_name,
      COUNT(employee_time_sheets.employee_id) AS timesheets_count
    FROM
      employees
    LEFT JOIN
      employee_time_sheets
      ON employees.employee_id = employee_time_sheets.employee_id
    WHERE
      employees.company_id = ?
  SQL

  if payroll_period_id.positive?
    sql += <<-SQL.squish
      AND employee_time_sheets.payroll_period_id = ?
    SQL
  end

  sql += <<-SQL.squish
    GROUP BY
      employees.employee_id,
      employee_name
  SQL

  if must_have_timesheets
    sql += <<-SQL.squish
      HAVING COUNT(employee_time_sheets.employee_id) > 0
    SQL
  end

  sql += <<-SQL.squish
    ORDER BY
      timesheets_count DESC
  SQL

  sanitized_query = if payroll_period_id.positive?
                      ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id, payroll_period_id])
                    else
                      ActiveRecord::Base.send(:sanitize_sql_array, [sql, company_id])
                    end

  ActiveRecord::Base.connection.exec_query(sanitized_query).to_a
end

.ssn_valid?(ssn) ⇒ Boolean

Validates the format of a social security number (SSN).

Examples:

Employee.ssn_valid?('123-45-6789')

Parameters:

  • ssn (String) —

    the social security number to validate

Returns:

  • (Boolean) —

    true if the SSN is valid, false otherwise



241
242
243
244
245
246
247
248
249
250
251
 
# File 'app/models/employee.rb', line 241

def ssn_valid?(ssn)
  ssn = ssn.to_s.strip
  return false if ssn.blank?
  return false unless ssn.match?(/^\d{9}$|^\d{3}-\d{3}-\d{3}$/)

  digits = ssn.scan(/\d/).join

  sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT dbo.fu_validate_ssn(?) as value', digits])

  ActiveRecord::Base.connection.exec_query(sanitized_query).to_a.dig(0, 'value')
end

Instance Method Details

#bank_accounts ⇒ ActiveRecord::Relation

Returns the bank accounts associated with the employee.

Returns:

  • (ActiveRecord::Relation) —

    the bank accounts associated with the employee



257
258
259
260
261
262
 
# File 'app/models/employee.rb', line 257

def bank_accounts
  BankAccount.where(
    source: 'Employee',
    reference_id: employee_id
  )
end

#default_bank_account ⇒ BankAccount?

Returns the default bank account of the employee.

Returns:

  • (BankAccount, nil) —

    the default bank account of the employee, or nil if none exists



267
268
269
 
# File 'app/models/employee.rb', line 267

def 
  bank_accounts.find_by(is_default: true)
end

#formatted_start_date ⇒ Object 



278
279
280
 
# File 'app/models/employee.rb', line 278

def formatted_start_date
  I18n.l(start_date, format: :long) if start_date
end

#locale ⇒ String

Returns the locale of the employee.

Returns:

  • (String) —

    the locale of the employee



274
275
276
 
# File 'app/models/employee.rb', line 274

def locale
  Language.new(default_language).locale
end

#name ⇒ Object 



128
129
130
 
# File 'app/models/employee.rb', line 128

def name
  employee_name
end

#pay_columns_changed? ⇒ Boolean

Checks if any of the pay columns have changed.

Returns:

  • (Boolean) —

    true if any pay columns have changed, false otherwise



135
136
137
 
# File 'app/models/employee.rb', line 135

def pay_columns_changed?
  changes.keys.any? { |column| PAY_COLUMNS.include?(column) }
end

#role_description ⇒ String

Returns a description of the employee’s roles.

Returns:

  • (String) —

    a comma-separated list of role descriptions



150
151
152
 
# File 'app/models/employee.rb', line 150

def role_description
  employee_roles.select(:description).map(&:description).join(', ')
end

#timesheets_count_for_period(payroll_period_id) ⇒ Integer

Counts the number of timesheets for a specific payroll period.

Parameters:

  • payroll_period_id (Integer) —

    the ID of the payroll period

Returns:

  • (Integer) —

    the number of timesheets for the specified payroll period



143
144
145
 
# File 'app/models/employee.rb', line 143

def timesheets_count_for_period(payroll_period_id)
  employee_timesheets.where(payroll_period_id: payroll_period_id).count
end

#url ⇒ Object 



282
283
284
 
# File 'app/models/employee.rb', line 282

def url
  "#{AppMode.payroll.web_uri}/app/payroll/employees/#{employee_id}"
end

#validate_ssn ⇒ Boolean

Validates the social security number (SSN) of the employee.

Returns:

  • (Boolean) —

    true if the SSN is valid, false otherwise



289
290
291
292
293
 
# File 'app/models/employee.rb', line 289

def validate_ssn
  validity = Employee.ssn_valid?(ssn)
  errors.add(:ssn, I18n.t('general.is_invalid')) unless validity
  validity
end