Class: Analytic::BudgetFact

Inherits:
ApplicationRecord
  • Object
show all
Includes:
Utility
Defined in:
app/models/analytic/budget_fact.rb

Overview

== Schema Information

Table name: analytic_budget_facts
Database name: primary

id :integer not null, primary key
actual_accumulated :integer
actual_accumulated_previous :integer
actual_accumulated_previous_diff :integer
actual_accumulated_previous_percent :decimal(12, 2)
actual_month :integer
actual_month_previous :integer
actual_month_previous_diff :integer
actual_month_previous_percent :decimal(12, 2)
budget_accumulated :integer
budget_accumulated_diff :integer
budget_accumulated_percent :decimal(12, 2)
budget_month :integer
budget_month_diff :integer
budget_month_percent :decimal(12, 2)
is_revenue :boolean
month :integer
year :integer
created_at :datetime not null
updated_at :datetime not null
budget_dimension_id :integer

Indexes

idx_budget_facts_year_month_dimension_unique (year,month,budget_dimension_id) UNIQUE

Constant Summary collapse

REFRESH_MUTEX =

Mutex to prevent concurrent refresh operations from corrupting shared class instance variables
The refresh method uses class instance variables for caches and aggregates, which are not thread-safe

Mutex.new

Belongs to collapse

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.build_percentage(diff, original) ⇒ Object 



699
700
701
702
703
704
705
 
# File 'app/models/analytic/budget_fact.rb', line 699

def self.build_percentage(diff, original)
  if diff.zero?
    0
  else
    (original.zero? ? 100 : ((diff.to_f / original) * 100))
  end
end

.collect_all_supplier_ids(year, month) ⇒ Object

Collect all supplier IDs that might be needed for the refresh



261
262
263
264
265
266
267
268
269
270
271
272
 
# File 'app/models/analytic/budget_fact.rb', line 261

def self.collect_all_supplier_ids(year, month)
  supplier_ids = Budget.where(year: year, month: 1..month).pluck(:supplier_id)
  supplier_ids += LedgerEntry.joins(:ledger_transaction)
                             .where(ledger_transactions: { transaction_date: Date.new(year - 1, 1).beginning_of_month..Date.new(year, month).end_of_month })
                             .distinct
                             .pluck(:supplier_id)
  supplier_ids += LedgerTransaction
                  .where(transaction_date: Date.new(year - 1, 1).beginning_of_month..Date.new(year, month).end_of_month)
                  .distinct
                  .pluck(:supplier_id)
  supplier_ids.compact.uniq
end

.collect_all_supplier_ids_from_aggregates ⇒ Object

Collect supplier IDs from pre-computed aggregates (no database queries)
This is much faster than collect_all_supplier_ids since data is already in memory



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
 
# File 'app/models/analytic/budget_fact.rb', line 276

def self.collect_all_supplier_ids_from_aggregates
  supplier_ids = Set.new

  # Collect from ledger aggregates
  @ledger_suppliers_by_lca_bu_proj.each_value do |bu_hash|
    bu_hash.each_value do |proj_hash|
      proj_hash.each_value do |supplier_set|
        supplier_ids.merge(supplier_set)
      end
    end
  end

  # Collect from budget aggregates
  @budget_suppliers_by_cid_la_bu_proj.each_value do |la_hash|
    la_hash.each_value do |bu_hash|
      bu_hash.each_value do |proj_hash|
        proj_hash.each_value do |supplier_set|
          supplier_ids.merge(supplier_set)
        end
      end
    end
  end

  supplier_ids.delete(nil) # Remove nil entries
  supplier_ids.to_a
end

.find_or_create_dimension_cached(existing_attrs, new_attrs) ⇒ Object

Cached dimension lookup/creation



527
528
529
530
531
532
533
534
 
# File 'app/models/analytic/budget_fact.rb', line 527

def self.find_or_create_dimension_cached(existing_attrs, new_attrs)
  cache_key = existing_attrs.to_s
  return @dimension_cache[cache_key] if @dimension_cache[cache_key]

  dimension = Analytic::BudgetDimension.find_or_create_with(existing_attrs, new_attrs)
  @dimension_cache[cache_key] = dimension
  dimension
end

.format_project_name(description) ⇒ Object

Reformat project names from "Name_NEW-CNxxxxxx" to "CNxxxxxx - Name"
This provides a cleaner display format matching the customer number pattern



713
714
715
716
717
718
719
 
# File 'app/models/analytic/budget_fact.rb', line 713

def self.format_project_name(description)
  if description =~ /\A(.+)_NEW-(CN\d+)\z/
    "#{::Regexp.last_match(2)} - #{::Regexp.last_match(1)}"
  else
    description
  end
end

.get_company_accounts_for(company_account_array, ledger_account_ids, company_id) ⇒ Object 



99
100
101
102
103
 
# File 'app/models/analytic/budget_fact.rb', line 99

def self.get_company_accounts_for(, , company_id)
   = []
  .each { |id| .concat([id.to_i][company_id]) }
  
end

.get_project_ids(year, month, company_id, ledger_account_ids, ledger_company_account_ids, business_unit_id) ⇒ Object 



44
45
46
47
48
49
50
 
# File 'app/models/analytic/budget_fact.rb', line 44

def self.get_project_ids(year, month, company_id, , , business_unit_id)
  project_ids = [nil]
  project_ids += Budget.where(year: year, month: 1..month, company_id: company_id, ledger_account_id: , business_unit_id: business_unit_id).pluck(:ledger_project_id)
  project_ids += LedgerEntry.joins(:ledger_transaction).where(ledger_transactions: { transaction_date: Date.new(year - 1, 1).beginning_of_month..Date.new(year, month).end_of_month }, ledger_company_account_id: ,
business_unit_id: business_unit_id).pluck(:ledger_detail_project_id)
  project_ids.uniq
end

.get_project_ids_cached(_year, _month, company_id, ledger_account_ids, ledger_company_account_ids, business_unit_id) ⇒ Object

Get project_ids from pre-computed indexes (O(1) lookups, no database queries)
Note: year/month kept for API compatibility but not used since we use pre-computed data



645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
 
# File 'app/models/analytic/budget_fact.rb', line 645

def self.get_project_ids_cached(_year, _month, company_id, , , business_unit_id)
  @project_ids_cache ||= {}
  cache_key = [company_id, , business_unit_id].to_s

  return @project_ids_cache[cache_key] if @project_ids_cache[cache_key]

  project_ids = Set.new([nil]) # Always include nil

  # Get from ledger data
  .each do |lca_id|
    project_ids.merge(@ledger_projects_by_lca_bu[lca_id][business_unit_id]) if @ledger_projects_by_lca_bu.dig(lca_id, business_unit_id)
  end

  # Get from budget data
  company_ids = company_id.is_a?(Array) ? company_id : [company_id]
  .each do |la_id|
    # Convert string to integer for hash lookup (all_ledger_account_ids is text[] but hash keys are integers)
    la_id_int = la_id.to_i
    company_ids.each do |cid|
      project_ids.merge(@budget_projects_by_cid_la_bu[cid][la_id_int][business_unit_id]) if @budget_projects_by_cid_la_bu.dig(cid, la_id_int, business_unit_id)
    end
  end

  @project_ids_cache[cache_key] = project_ids.to_a
end

.get_results(year, month, company_id, ledger_field, budget_field, ledger_account_ids, ledger_company_account_ids, budget_options = {}, ledger_options = {}) ⇒ Object 



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
 
# File 'app/models/analytic/budget_fact.rb', line 64

def self.get_results(year, month, company_id, ledger_field, budget_field, , , budget_options = {}, ledger_options = {})
  budgets_month = Budget.where(year: year, month: month, company_id: company_id, ledger_account_id: ).where(budget_options)
  ledger_entries_month = LedgerEntry.joins(:ledger_transaction).where(ledger_transactions: { transaction_date: Date.new(year, month).all_month },
ledger_company_account_id: ).where(ledger_options)
  budgets_accumulated = Budget.where(year: year, month: 1..month, company_id: company_id, ledger_account_id: ).where(budget_options).where(budget_options)
  ledger_entries_accumulated = LedgerEntry.joins(:ledger_transaction).where(ledger_transactions: { transaction_date: Date.new(year, 1).beginning_of_month..Date.new(year, month).end_of_month },
ledger_company_account_id: ).where(ledger_options)
  ledger_entries_month_previous = LedgerEntry.joins(:ledger_transaction).where(ledger_transactions: { transaction_date: Date.new(year - 1, month).all_month },
ledger_company_account_id: ).where(ledger_options)
  ledger_entries_accumulated_previous = LedgerEntry.joins(:ledger_transaction).where(ledger_transactions: { transaction_date: Date.new(year - 1, 1).beginning_of_month..Date.new(year - 1, month).end_of_month },
ledger_company_account_id: ).where(ledger_options)

  res = {}
  # budget month
  res[:budget_month] = -budgets_month.sum(budget_field).round
  res[:actual_month] = -ledger_entries_month.sum(ledger_field).round
  res[:budget_month_diff] = res[:actual_month] - res[:budget_month]
  res[:budget_month_percent] = build_percentage(res[:budget_month_diff], res[:budget_month])
  # budget accumulated
  res[:budget_accumulated] = -budgets_accumulated.sum(budget_field).round
  res[:actual_accumulated] = -ledger_entries_accumulated.sum(ledger_field).round
  res[:budget_accumulated_diff] = res[:actual_accumulated] - res[:budget_accumulated]
  res[:budget_accumulated_percent] = build_percentage(res[:budget_accumulated_diff], res[:budget_accumulated])
  # previous year month
  res[:actual_month_previous] = -ledger_entries_month_previous.sum(ledger_field).round
  res[:actual_month_previous_diff] = res[:actual_month] - res[:actual_month_previous]
  res[:actual_month_previous_percent] = build_percentage(res[:actual_month_previous_diff], res[:actual_month])
  # previous year accumulated
  res[:actual_accumulated_previous] = -ledger_entries_accumulated_previous.sum(ledger_field).round
  res[:actual_accumulated_previous_diff] = res[:actual_accumulated] - res[:actual_accumulated_previous]
  res[:actual_accumulated_previous_percent] = build_percentage(res[:actual_accumulated_previous_diff], res[:actual_accumulated])

  res
end

.get_results_optimized(_year, _month, company, ledger_account_ids, ledger_company_account_ids, business_unit_id, project_id, supplier_id) ⇒ Object

Optimized get_results using pre-computed aggregates
Note: year/month kept for API compatibility but not used since we use pre-computed data



550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
 
# File 'app/models/analytic/budget_fact.rb', line 550

def self.get_results_optimized(_year, _month, company, , , business_unit_id, project_id, supplier_id)
  ledger_field = company[:ledger_field]
  budget_field = company[:budget_field]
  company_id = company[:id]

  # Sum ledger entries from pre-computed aggregates
  actual_month = sum_ledger_aggregates(ledger_field, :month, , business_unit_id, project_id, supplier_id)
  actual_accumulated = sum_ledger_aggregates(ledger_field, :accumulated, , business_unit_id, project_id, supplier_id)
  actual_month_previous = sum_ledger_aggregates(ledger_field, :month_previous, , business_unit_id, project_id, supplier_id)
  actual_accumulated_previous = sum_ledger_aggregates(ledger_field, :accumulated_previous, , business_unit_id, project_id, supplier_id)

  # Sum budget entries from pre-computed aggregates
  budget_month = sum_budget_aggregates(budget_field, :month, company_id, , business_unit_id, project_id, supplier_id)
  budget_accumulated = sum_budget_aggregates(budget_field, :accumulated, company_id, , business_unit_id, project_id, supplier_id)

  # Apply negation (matching original logic)
  actual_month = -actual_month
  actual_accumulated = -actual_accumulated
  actual_month_previous = -actual_month_previous
  actual_accumulated_previous = -actual_accumulated_previous
  budget_month = -budget_month
  budget_accumulated = -budget_accumulated

  # Calculate derived values
  {
    budget_month: budget_month,
    actual_month: actual_month,
    budget_month_diff: actual_month - budget_month,
    budget_month_percent: build_percentage(actual_month - budget_month, budget_month),
    budget_accumulated: budget_accumulated,
    actual_accumulated: actual_accumulated,
    budget_accumulated_diff: actual_accumulated - budget_accumulated,
    budget_accumulated_percent: build_percentage(actual_accumulated - budget_accumulated, budget_accumulated),
    actual_month_previous: actual_month_previous,
    actual_month_previous_diff: actual_month - actual_month_previous,
    actual_month_previous_percent: build_percentage(actual_month - actual_month_previous, actual_month),
    actual_accumulated_previous: actual_accumulated_previous,
    actual_accumulated_previous_diff: actual_accumulated - actual_accumulated_previous,
    actual_accumulated_previous_percent: build_percentage(actual_accumulated - actual_accumulated_previous, actual_accumulated)
  }
end

.get_supplier_ids(year, month, company_id, ledger_account_ids, ledger_company_account_ids, business_unit_id, project_id) ⇒ Object 



52
53
54
55
56
57
58
59
60
61
62
 
# File 'app/models/analytic/budget_fact.rb', line 52

def self.get_supplier_ids(year, month, company_id, , , business_unit_id, project_id)
  supplier_ids = [nil]
  budget_query = Budget.where(year: year, month: 1..month, company_id: company_id, ledger_account_id: , ledger_project_id: project_id&.zero? ? nil : project_id)
  budget_query = budget_query.where(business_unit_id: business_unit_id) unless business_unit_id.nil?
  supplier_ids += budget_query.pluck(:supplier_id)
  ledger_query = LedgerEntry.joins(:ledger_transaction).where(ledger_transactions: { transaction_date: Date.new(year - 1, 1).beginning_of_month..Date.new(year, month).end_of_month }, ledger_company_account_id: ,
ledger_detail_project_id: project_id)
  ledger_query = ledger_query.where(business_unit_id: business_unit_id) unless business_unit_id.nil?
  supplier_ids += ledger_query.pluck(:supplier_id)
  supplier_ids.uniq
end

.get_supplier_ids_cached(_year, _month, company_id, ledger_account_ids, ledger_company_account_ids, business_unit_id, project_id) ⇒ Object

Get supplier_ids from pre-computed indexes (O(1) lookups, no database queries)
Note: year/month kept for API compatibility but not used since we use pre-computed data



673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
 
# File 'app/models/analytic/budget_fact.rb', line 673

def self.get_supplier_ids_cached(_year, _month, company_id, , , business_unit_id, project_id)
  @supplier_ids_cache ||= {}
  cache_key = [company_id, , business_unit_id, project_id].to_s

  return @supplier_ids_cache[cache_key] if @supplier_ids_cache[cache_key]

  supplier_ids = Set.new([nil]) # Always include nil

  # Get from ledger data
  .each do |lca_id|
    supplier_ids.merge(@ledger_suppliers_by_lca_bu_proj[lca_id][business_unit_id][project_id]) if @ledger_suppliers_by_lca_bu_proj.dig(lca_id, business_unit_id, project_id)
  end

  # Get from budget data
  company_ids = company_id.is_a?(Array) ? company_id : [company_id]
  .each do |la_id|
    # Convert string to integer for hash lookup (all_ledger_account_ids is text[] but hash keys are integers)
    la_id_int = la_id.to_i
    company_ids.each do |cid|
      supplier_ids.merge(@budget_suppliers_by_cid_la_bu_proj[cid][la_id_int][business_unit_id][project_id]) if @budget_suppliers_by_cid_la_bu_proj.dig(cid, la_id_int, business_unit_id, project_id)
    end
  end

  @supplier_ids_cache[cache_key] = supplier_ids.to_a
end

.index_budget_aggregate(field, period, cid, la_id, bu_id, project_id, supplier_id, sum_i) ⇒ Object

Helper to index a budget aggregate at all levels



430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
 
# File 'app/models/analytic/budget_fact.rb', line 430

def self.index_budget_aggregate(field, period, cid, la_id, bu_id, project_id, supplier_id, sum_i)
  # Level 1: By company + ledger_account
  @budget_by_cid_la[field][period][cid] ||= Hash.new(0)
  @budget_by_cid_la[field][period][cid][la_id] += sum_i

  # Level 2: By company + ledger_account + BU
  @budget_by_cid_la_bu[field][period][cid] ||= {}
  @budget_by_cid_la_bu[field][period][cid][la_id] ||= Hash.new(0)
  @budget_by_cid_la_bu[field][period][cid][la_id][bu_id] += sum_i

  # Level 3: By company + ledger_account + BU + Project
  @budget_by_cid_la_bu_proj[field][period][cid] ||= {}
  @budget_by_cid_la_bu_proj[field][period][cid][la_id] ||= {}
  @budget_by_cid_la_bu_proj[field][period][cid][la_id][bu_id] ||= Hash.new(0)
  @budget_by_cid_la_bu_proj[field][period][cid][la_id][bu_id][project_id] += sum_i

  # Level 4: Exact match
  @budget_exact[field][period][[cid, la_id, bu_id, project_id, supplier_id]] = sum_i
end

.no_results?(results) ⇒ Boolean

Returns:

  • (Boolean) 


707
708
709
 
# File 'app/models/analytic/budget_fact.rb', line 707

def self.no_results?(results)
  [results[:budget_month], results[:actual_month], results[:budget_accumulated], results[:actual_accumulated], results[:actual_month_previous], results[:actual_accumulated_previous]].all?(0)
end

.precompute_budget_aggregates(year, month) ⇒ Object

Pre-compute all budget aggregates with multi-level indexing for O(1) lookups
OPTIMIZED: Fetch both fields in a single query per period (2 queries instead of 4)



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
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
 
# File 'app/models/analytic/budget_fact.rb', line 383

def self.precompute_budget_aggregates(year, month)
  # Multi-level indexes for O(1) lookups at different aggregation levels
  @budget_by_cid_la = { amount: { month: {}, accumulated: {} }, consolidated_amount: { month: {}, accumulated: {} } }
  @budget_by_cid_la_bu = { amount: { month: {}, accumulated: {} }, consolidated_amount: { month: {}, accumulated: {} } }
  @budget_by_cid_la_bu_proj = { amount: { month: {}, accumulated: {} }, consolidated_amount: { month: {}, accumulated: {} } }
  @budget_exact = { amount: { month: {}, accumulated: {} }, consolidated_amount: { month: {}, accumulated: {} } }

  # Pre-compute project_ids and supplier_ids from budget data for O(1) lookups
  @budget_projects_by_cid_la_bu = {}
  @budget_suppliers_by_cid_la_bu_proj = {}

  # Month budgets - ONE query fetching BOTH fields
  Budget.where(year: year, month: month)
        .group(:company_id, :ledger_account_id, :business_unit_id, :ledger_project_id, :supplier_id)
        .pluck(:company_id, :ledger_account_id, :business_unit_id, :ledger_project_id, :supplier_id,
           Arel.sql("SUM(amount)"), Arel.sql("SUM(consolidated_amount)"))
        .each do |cid, la_id, bu_id, project_id, supplier_id, amount_sum, consolidated_sum|
          index_budget_aggregate(:amount, :month, cid, la_id, bu_id, project_id, supplier_id, amount_sum.to_i)
          index_budget_aggregate(:consolidated_amount, :month, cid, la_id, bu_id, project_id, supplier_id, consolidated_sum.to_i)
        end

  # Accumulated budgets - ONE query fetching BOTH fields
  Budget.where(year: year, month: 1..month)
        .group(:company_id, :ledger_account_id, :business_unit_id, :ledger_project_id, :supplier_id)
        .pluck(:company_id, :ledger_account_id, :business_unit_id, :ledger_project_id, :supplier_id,
           Arel.sql("SUM(amount)"), Arel.sql("SUM(consolidated_amount)"))
        .each do |cid, la_id, bu_id, project_id, supplier_id, amount_sum, consolidated_sum|
          index_budget_aggregate(:amount, :accumulated, cid, la_id, bu_id, project_id, supplier_id, amount_sum.to_i)
          index_budget_aggregate(:consolidated_amount, :accumulated, cid, la_id, bu_id, project_id, supplier_id, consolidated_sum.to_i)

          # Index projects and suppliers (once per row)
          @budget_projects_by_cid_la_bu[cid] ||= {}
          @budget_projects_by_cid_la_bu[cid][la_id] ||= {}
          @budget_projects_by_cid_la_bu[cid][la_id][bu_id] ||= Set.new
          @budget_projects_by_cid_la_bu[cid][la_id][bu_id] << project_id

          @budget_suppliers_by_cid_la_bu_proj[cid] ||= {}
          @budget_suppliers_by_cid_la_bu_proj[cid][la_id] ||= {}
          @budget_suppliers_by_cid_la_bu_proj[cid][la_id][bu_id] ||= {}
          @budget_suppliers_by_cid_la_bu_proj[cid][la_id][bu_id][project_id] ||= Set.new
          @budget_suppliers_by_cid_la_bu_proj[cid][la_id][bu_id][project_id] << supplier_id
        end

  true
end

.precompute_ledger_aggregates(year, month) ⇒ Object

Pre-compute all ledger entry aggregates with multi-level indexing for O(1) lookups
OPTIMIZED: Fetch both fields in a single query per period (4 queries instead of 8)



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
 
# File 'app/models/analytic/budget_fact.rb', line 305

def self.precompute_ledger_aggregates(year, month)
  date_ranges = {
    month: Date.new(year, month).all_month,
    accumulated: Date.new(year, 1).beginning_of_month..Date.new(year, month).end_of_month,
    month_previous: Date.new(year - 1, month).all_month,
    accumulated_previous: Date.new(year - 1, 1).beginning_of_month..Date.new(year - 1, month).end_of_month
  }

  # Multi-level indexes for O(1) lookups at different aggregation levels
  @ledger_by_lca = { company_amount: {}, consolidated_amount: {} }
  @ledger_by_lca_bu = { company_amount: {}, consolidated_amount: {} }
  @ledger_by_lca_bu_proj = { company_amount: {}, consolidated_amount: {} }
  @ledger_exact = { company_amount: {}, consolidated_amount: {} }

  # Pre-compute project_ids and supplier_ids indexes for O(1) lookups
  @ledger_projects_by_lca_bu = {}
  @ledger_suppliers_by_lca_bu_proj = {}

  # Initialize period hashes for all fields
  date_ranges.each_key do |period|
    %i[company_amount consolidated_amount].each do |field|
      @ledger_by_lca[field][period] = Hash.new(0)
      @ledger_by_lca_bu[field][period] = {}
      @ledger_by_lca_bu_proj[field][period] = {}
      @ledger_exact[field][period] = {}
    end
  end

  # Run ONE query per period, fetching BOTH fields at once (4 queries instead of 8)
  date_ranges.each do |period, date_range|
    results = LedgerEntry.joins(:ledger_transaction)
                         .where(ledger_transactions: { transaction_date: date_range })
                         .group(:ledger_company_account_id, :business_unit_id, :ledger_detail_project_id, "ledger_transactions.supplier_id")
                         .pluck(
        :ledger_company_account_id,
        :business_unit_id,
        :ledger_detail_project_id,
        Arel.sql("ledger_transactions.supplier_id"),
        Arel.sql("SUM(ledger_entries.company_amount)"),
        Arel.sql("SUM(ledger_entries.consolidated_amount)")
      )

    results.each do |lca_id, bu_id, project_id, supplier_id, company_sum, consolidated_sum|
      # Process both fields from the same row
      { company_amount: company_sum.to_i, consolidated_amount: consolidated_sum.to_i }.each do |field, sum_i|
        # Level 1: By LCA only
        @ledger_by_lca[field][period][lca_id] += sum_i

        # Level 2: By LCA + BU
        @ledger_by_lca_bu[field][period][lca_id] ||= Hash.new(0)
        @ledger_by_lca_bu[field][period][lca_id][bu_id] += sum_i

        # Level 3: By LCA + BU + Project
        @ledger_by_lca_bu_proj[field][period][lca_id] ||= {}
        @ledger_by_lca_bu_proj[field][period][lca_id][bu_id] ||= Hash.new(0)
        @ledger_by_lca_bu_proj[field][period][lca_id][bu_id][project_id] += sum_i

        # Level 4: Exact match
        @ledger_exact[field][period][[lca_id, bu_id, project_id, supplier_id]] = sum_i
      end

      # Index project_ids and supplier_ids (only once per result row)
      @ledger_projects_by_lca_bu[lca_id] ||= {}
      @ledger_projects_by_lca_bu[lca_id][bu_id] ||= Set.new
      @ledger_projects_by_lca_bu[lca_id][bu_id] << project_id

      @ledger_suppliers_by_lca_bu_proj[lca_id] ||= {}
      @ledger_suppliers_by_lca_bu_proj[lca_id][bu_id] ||= {}
      @ledger_suppliers_by_lca_bu_proj[lca_id][bu_id][project_id] ||= Set.new
      @ledger_suppliers_by_lca_bu_proj[lca_id][bu_id][project_id] << supplier_id
    end
  end

  true
end

.process_budget_group(year, month, company, bg, business_units, leaf_budget_groups) ⇒ Object

Process a single budget group (extracted for clarity)



451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
 
# File 'app/models/analytic/budget_fact.rb', line 451

def self.process_budget_group(year, month, company, bg, business_units, leaf_budget_groups)
   = get_company_accounts_for(@company_account_array, bg., company[:id])

  bg_dimension = find_or_create_dimension_cached(
    { company_id: company[:dimension], ledger_account_ids: bg., business_unit_id: nil, ledger_project_id: nil, supplier_id: nil, budget_group_id: bg.id, parent_id: nil },
    { description: bg.description, ledger_company_account_ids:  }
  )

  bg_results = get_results_optimized(year, month, company, bg., , nil, nil, nil)
  queue_fact(year, month, bg_dimension.id, bg.is_revenue?, bg_results)

  business_units.each do |bu|
    process_business_unit(year, month, company, bg, bu, bg_dimension, , leaf_budget_groups)
  end
end

.process_business_unit(year, month, company, bg, bu, bg_dimension, ledger_company_account_ids, _leaf_budget_groups) ⇒ Object

Process a single business unit within a budget group



468
469
470
471
472
473
474
475
476
477
478
479
480
 
# File 'app/models/analytic/budget_fact.rb', line 468

def self.process_business_unit(year, month, company, bg, bu, bg_dimension, , _leaf_budget_groups)
  bg_bu_results = get_results_optimized(year, month, company, bg., , bu.id, nil, nil)

  bg_bu_dimension = find_or_create_dimension_cached(
    { company_id: company[:dimension], ledger_account_ids: bg., business_unit_id: bu.id, ledger_project_id: nil, supplier_id: nil, budget_group_id: bg.id, parent_id: bg_dimension.id },
    { description: bu.name, ledger_company_account_ids:  }
  )

  queue_fact(year, month, bg_bu_dimension.id, bg.is_revenue?, bg_bu_results)

  # Only process projects/suppliers for leaf budget groups with non-zero results
  process_projects_for_bu(year, month, company, bg, bu, bg_bu_dimension, ) if bg.leaf? && !no_results?(bg_bu_results)
end

.process_projects_for_bu(year, month, company, bg, bu, bg_bu_dimension, ledger_company_account_ids) ⇒ Object

Process projects for a business unit



483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
 
# File 'app/models/analytic/budget_fact.rb', line 483

def self.process_projects_for_bu(year, month, company, bg, bu, bg_bu_dimension, )
  project_ids = get_project_ids_cached(year, month, company[:id], bg., , bu.id)

  project_ids.each do |project_id|
    project_results = get_results_optimized(year, month, company, bg., , bu.id, project_id, nil)

    next if no_results?(project_results)

    project_name = project_id.nil? ? "No Project Specified" : format_project_name(@projects_by_id[project_id]&.description || "Unknown Project")

    project_dimension = find_or_create_dimension_cached(
      { company_id: company[:dimension], ledger_account_ids: bg., business_unit_id: bu.id, ledger_project_id: project_id || 0, supplier_id: nil, budget_group_id: bg.id, parent_id: bg_bu_dimension.id },
      { description: project_name, ledger_company_account_ids:  }
    )

    queue_fact(year, month, project_dimension.id, bg.is_revenue?, project_results)

    process_suppliers_for_project(year, month, company, bg, bu, project_id, project_dimension, )
  end
end

.process_suppliers_for_project(year, month, company, bg, bu, project_id, project_dimension, ledger_company_account_ids) ⇒ Object

Process suppliers for a project



505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
 
# File 'app/models/analytic/budget_fact.rb', line 505

def self.process_suppliers_for_project(year, month, company, bg, bu, project_id, project_dimension, )
  supplier_ids = get_supplier_ids_cached(year, month, company[:id], bg., , bu.id, project_id)

  supplier_ids.each do |supplier_id|
    supplier_name = supplier_id.nil? ? "No Supplier Specified" : @parties_by_id[supplier_id]&.full_name

    next if supplier_name.nil?

    supplier_results = get_results_optimized(year, month, company, bg., , bu.id, project_id, supplier_id)

    next if no_results?(supplier_results)

    supplier_dimension = find_or_create_dimension_cached(
      { company_id: company[:dimension], ledger_account_ids: bg., business_unit_id: bu.id, ledger_project_id: project_id || 0, supplier_id: supplier_id || 0, budget_group_id: bg.id, parent_id: project_dimension.id },
      { description: supplier_name, ledger_company_account_ids:  }
    )

    queue_fact(year, month, supplier_dimension.id, bg.is_revenue?, supplier_results)
  end
end

.queue_fact(year, month, dimension_id, is_revenue, results) ⇒ Object

Queue a fact for batch insertion



537
538
539
540
541
542
543
544
545
546
 
# File 'app/models/analytic/budget_fact.rb', line 537

def self.queue_fact(year, month, dimension_id, is_revenue, results)
  @facts_to_insert << {
    year: year,
    month: month,
    budget_dimension_id: dimension_id,
    is_revenue: is_revenue,
    created_at: Time.current,
    updated_at: Time.current
  }.merge(results)
end

.refresh_data_for_month(year = Date.current.year, month, incremental: false, &progress_block) ⇒ Object

Refresh budget facts for a given year/month

Parameters:

  • year (Integer) (defaults to: Date.current.year) —

    the year to refresh

  • month (Integer) —

    the month to refresh

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

    if true, uses upsert (update or insert) instead of delete-all-reinsert

  • progress_block (Block) —

    optional block for progress reporting



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
 
# File 'app/models/analytic/budget_fact.rb', line 110

def self.refresh_data_for_month(year = Date.current.year, month, incremental: false, &progress_block)
  # Use mutex to prevent concurrent refresh operations from corrupting shared class instance variables
  # This ensures thread-safety when multiple Sidekiq workers might run simultaneously
  REFRESH_MUTEX.synchronize do
    logger ||= Rails.logger
    start_time = Time.current
    @progress_block = progress_block
    @incremental_mode = incremental

    refresh_mode = incremental ? 'incremental (upsert)' : 'full (delete-reinsert)'
    report_progress(0, "Starting #{refresh_mode} refresh for #{year}-#{month}")
    logger.info "[BudgetFact] Starting #{refresh_mode} refresh for #{year}-#{month}"

    # Only delete when not in incremental mode
    Analytic::BudgetFact.delete_by(year: year, month: month) unless incremental

    business_units = BusinessUnit.order(:number).to_a
    budget_groups = BudgetGroup.all.to_a
    leaf_budget_groups = budget_groups.select(&:leaf?)

    @company_account_array = LedgerAccount.

    # ============================================================
    # OPTIMIZATION 1: Pre-load projects (suppliers loaded after aggregates)
    # ============================================================
    report_progress(10, 'Pre-loading projects...')
    t1 = Time.current
    @projects_by_id = LedgerProject.all.index_by(&:id)
    logger.info "[BudgetFact] Pre-loaded #{@projects_by_id.size} projects in #{(Time.current - t1).round(2)}s"

    # ============================================================
    # OPTIMIZATION 2: Pre-compute all aggregates with multi-level indexing for O(1) lookups
    # ============================================================
    report_progress(20, 'Computing ledger aggregates...')
    t2 = Time.current
    precompute_ledger_aggregates(year, month)
    logger.info "[BudgetFact] Ledger aggregates computed in #{(Time.current - t2).round(2)}s"

    report_progress(35, 'Computing budget aggregates...')
    t3 = Time.current
    precompute_budget_aggregates(year, month)
    logger.info "[BudgetFact] Budget aggregates computed in #{(Time.current - t3).round(2)}s"

    # ============================================================
    # OPTIMIZATION 2.5: Load only suppliers we need (from pre-computed aggregates)
    # This replaces loading ALL parties (600K+) with only needed suppliers (~1-5K)
    # ============================================================
    report_progress(37, 'Loading required suppliers...')
    t_suppliers = Time.current
    supplier_ids = collect_all_supplier_ids_from_aggregates
    @parties_by_id = supplier_ids.any? ? Party.where(id: supplier_ids).index_by(&:id) : {}
    logger.info "[BudgetFact] Pre-loaded #{@parties_by_id.size} suppliers (from #{supplier_ids.size} IDs) in #{(Time.current - t_suppliers).round(2)}s"

    # ============================================================
    # OPTIMIZATION 3: Pre-load ALL existing dimensions into cache
    # This eliminates database lookups during processing
    # ============================================================
    report_progress(38, 'Pre-loading existing dimensions...')
    t4 = Time.current
    @dimension_cache = {}
    Analytic::BudgetDimension.find_each do |dim|
      cache_key = {
        company_id: dim.company_id,
        ledger_account_ids: dim.,
        business_unit_id: dim.business_unit_id,
        ledger_project_id: dim.ledger_project_id,
        supplier_id: dim.supplier_id,
        budget_group_id: dim.budget_group_id,
        parent_id: dim.parent_id
      }.to_s
      @dimension_cache[cache_key] = dim
    end
    logger.info "[BudgetFact] Pre-loaded #{@dimension_cache.size} dimensions in #{(Time.current - t4).round(2)}s"

    @facts_to_insert = []
    @dimensions_to_create = [] # Queue for batch creation

    # Company configurations for budget report generation
    company_configs = [
      { dimension: '1', id: Company::USA, ledger_field: :company_amount, budget_field: :amount },
      { dimension: '2', id: Company::CAN, ledger_field: :company_amount, budget_field: :amount },
      { dimension: '4', id: Company::NLD, ledger_field: :company_amount, budget_field: :amount },
      { dimension: 'all', id: [Company::USA, Company::CAN], ledger_field: :consolidated_amount, budget_field: :consolidated_amount }
    ]

    report_progress(40, 'Processing budget groups...')
    t5 = Time.current
    total_iterations = company_configs.size * budget_groups.size
    current_iteration = 0

    company_configs.each do |company|
      budget_groups.each do |bg|
        current_iteration += 1
        # Progress from 40% to 85% during main processing
        progress_pct = 40 + ((current_iteration.to_f / total_iterations) * 45).round
        report_progress(progress_pct, "Processing #{company[:dimension]}: #{bg.description}")

        process_budget_group(year, month, company, bg, business_units, leaf_budget_groups)
      end
    end
    logger.info "[BudgetFact] Main processing loop completed in #{(Time.current - t5).round(2)}s"

    # ============================================================
    # OPTIMIZATION 4: Batch insert/upsert all facts at once
    # ============================================================
    if @incremental_mode
      report_progress(90, "Upserting #{@facts_to_insert.size} facts...")
      if @facts_to_insert.any?
        logger.info "[BudgetFact] Upserting #{@facts_to_insert.size} facts (incremental mode)"
        # Upsert in batches of 1000 to avoid memory issues
        # Uses unique index on (year, month, budget_dimension_id)
        @facts_to_insert.each_slice(1000) do |batch|
          Analytic::BudgetFact.upsert_all(
            batch,
            unique_by: :idx_budget_facts_year_month_dimension_unique,
            update_only: %i[
              budget_month actual_month budget_month_diff budget_month_percent
              budget_accumulated actual_accumulated budget_accumulated_diff budget_accumulated_percent
              actual_month_previous actual_month_previous_diff actual_month_previous_percent
              actual_accumulated_previous actual_accumulated_previous_diff actual_accumulated_previous_percent
              is_revenue
            ]
          )
        end
      end
    else
      report_progress(90, "Batch inserting #{@facts_to_insert.size} facts...")
      if @facts_to_insert.any?
        logger.info "[BudgetFact] Batch inserting #{@facts_to_insert.size} facts"
        # Insert in batches of 1000 to avoid memory issues
        @facts_to_insert.each_slice(1000) do |batch|
          Analytic::BudgetFact.insert_all!(batch)
        end
      end
    end

    elapsed = Time.current - start_time
    refresh_mode = @incremental_mode ? 'incremental' : 'full'
    report_progress(100, "Completed #{refresh_mode} refresh in #{elapsed.round(1)}s")
    logger.info "[BudgetFact] Completed #{refresh_mode} refresh for #{year}-#{month} in #{elapsed.round(2)}s"

    true
  end
end

.report_progress(percent, message) ⇒ Object

Report progress to callback block if provided



256
257
258
 
# File 'app/models/analytic/budget_fact.rb', line 256

def self.report_progress(percent, message)
  @progress_block&.call(percent, message)
end

.sum_budget_aggregates(field, period, company_id, ledger_account_ids, business_unit_id, project_id, supplier_id) ⇒ Object

Sum budget aggregates using O(1) indexed lookups



616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
 
# File 'app/models/analytic/budget_fact.rb', line 616

def self.sum_budget_aggregates(field, period, company_id, , business_unit_id, project_id, supplier_id)
  total = 0
  company_ids = company_id.is_a?(Array) ? company_id : [company_id]

  .each do |la_id|
    # Convert string to integer for hash lookup (all_ledger_account_ids is text[] but hash keys are integers)
    la_id_int = la_id.to_i
    company_ids.each do |cid|
      total += if business_unit_id.nil? && project_id.nil? && supplier_id.nil?
                 # Level 1: By company + ledger_account - O(1) lookup
                 @budget_by_cid_la.dig(field, period, cid, la_id_int).to_i
               elsif project_id.nil? && supplier_id.nil?
                 # Level 2: By company + ledger_account + BU - O(1) lookup
                 @budget_by_cid_la_bu.dig(field, period, cid, la_id_int, business_unit_id).to_i
               elsif supplier_id.nil?
                 # Level 3: By company + ledger_account + BU + Project - O(1) lookup
                 @budget_by_cid_la_bu_proj.dig(field, period, cid, la_id_int, business_unit_id, project_id).to_i
               else
                 # Level 4: Exact match - O(1) lookup
                 @budget_exact.dig(field, period, [cid, la_id_int, business_unit_id, project_id, supplier_id]).to_i
               end
    end
  end

  total
end

.sum_ledger_aggregates(field, period, ledger_company_account_ids, business_unit_id, project_id, supplier_id) ⇒ Object

Sum ledger aggregates using O(1) indexed lookups



593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
 
# File 'app/models/analytic/budget_fact.rb', line 593

def self.sum_ledger_aggregates(field, period, , business_unit_id, project_id, supplier_id)
  total = 0

  .each do |lca_id|
    total += if business_unit_id.nil? && project_id.nil? && supplier_id.nil?
               # Level 1: Aggregate all for this LCA - O(1) lookup
               @ledger_by_lca.dig(field, period, lca_id).to_i
             elsif project_id.nil? && supplier_id.nil?
               # Level 2: By LCA + BU - O(1) lookup
               @ledger_by_lca_bu.dig(field, period, lca_id, business_unit_id).to_i
             elsif supplier_id.nil?
               # Level 3: By LCA + BU + Project - O(1) lookup
               @ledger_by_lca_bu_proj.dig(field, period, lca_id, business_unit_id, project_id).to_i
             else
               # Level 4: Exact match - O(1) lookup
               @ledger_exact.dig(field, period, [lca_id, business_unit_id, project_id, supplier_id]).to_i
             end
  end

  total
end

Instance Method Details

#budget_dimension ⇒ BudgetDimension

Returns:

See Also:



38
 
# File 'app/models/analytic/budget_fact.rb', line 38

belongs_to :budget_dimension, optional: true

#is_zero? ⇒ Boolean

Returns:

  • (Boolean) 


721
722
723
 
# File 'app/models/analytic/budget_fact.rb', line 721

def is_zero?
  [budget_month, actual_month, budget_accumulated, actual_accumulated, actual_month_previous, actual_accumulated_previous].all?(&:zero?)
end