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 



703
704
705
 
# File 'app/models/analytic/budget_fact.rb', line 703

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

.collect_all_supplier_ids(year, month) ⇒ Object

Collect all supplier IDs that might be needed for the refresh



255
256
257
258
259
260
261
262
263
264
265
266
 
# File 'app/models/analytic/budget_fact.rb', line 255

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



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

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



523
524
525
526
527
528
529
530
 
# File 'app/models/analytic/budget_fact.rb', line 523

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/
    "#{$2} - #{$1}"
  else
    description
  end
end

.get_company_accounts_for(company_account_array, ledger_account_ids, company_id) ⇒ Object 



91
92
93
94
95
 
# File 'app/models/analytic/budget_fact.rb', line 91

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

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



42
43
44
45
46
47
 
# File 'app/models/analytic/budget_fact.rb', line 42

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)
	return 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



641
642
643
644
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 641

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|
    if @ledger_projects_by_lca_bu.dig(lca_id, business_unit_id)
      project_ids.merge(@ledger_projects_by_lca_bu[lca_id][business_unit_id])
    end
  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|
      if @budget_projects_by_cid_la_bu.dig(cid, la_id_int, business_unit_id)
        project_ids.merge(@budget_projects_by_cid_la_bu[cid][la_id_int][business_unit_id])
      end
    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 



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

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).beginning_of_month..Date.new(year, month).end_of_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).beginning_of_month..Date.new(year - 1, month).end_of_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] = self.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] = self.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] = self.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] = self.build_percentage(res[:actual_accumulated_previous_diff], res[:actual_accumulated])

return 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



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

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 



49
50
51
52
53
54
55
56
57
58
 
# File 'app/models/analytic/budget_fact.rb', line 49

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 = business_unit_id.nil? ? budget_query : budget_query.where(business_unit_id: business_unit_id)
	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 = business_unit_id.nil? ? ledger_query : ledger_query.where(business_unit_id: business_unit_id)
	supplier_ids += ledger_query.pluck(:supplier_id)
	return 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
698
699
700
701
 
# 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|
    if @ledger_suppliers_by_lca_bu_proj.dig(lca_id, business_unit_id, project_id)
      supplier_ids.merge(@ledger_suppliers_by_lca_bu_proj[lca_id][business_unit_id][project_id])
    end
  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|
      if @budget_suppliers_by_cid_la_bu_proj.dig(cid, la_id_int, business_unit_id, project_id)
        supplier_ids.merge(@budget_suppliers_by_cid_la_bu_proj[cid][la_id_int][business_unit_id][project_id])
      end
    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



424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'app/models/analytic/budget_fact.rb', line 424

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? {|x| x == 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)



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

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)



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

def self.precompute_ledger_aggregates(year, month)
  date_ranges = {
    month: Date.new(year, month).beginning_of_month..Date.new(year, month).end_of_month,
    accumulated: Date.new(year, 1).beginning_of_month..Date.new(year, month).end_of_month,
    month_previous: Date.new(year - 1, month).beginning_of_month..Date.new(year - 1, month).end_of_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|
    [: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)



445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
 
# File 'app/models/analytic/budget_fact.rb', line 445

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



462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
 
# File 'app/models/analytic/budget_fact.rb', line 462

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
  if bg.leaf? && !no_results?(bg_bu_results)
    process_projects_for_bu(year, month, company, bg, bu, bg_bu_dimension, )
  end
end

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

Process projects for a business unit



479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
 
# File 'app/models/analytic/budget_fact.rb', line 479

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



501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
 
# File 'app/models/analytic/budget_fact.rb', line 501

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



533
534
535
536
537
538
539
540
541
542
 
# File 'app/models/analytic/budget_fact.rb', line 533

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



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

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
  unless incremental
    Analytic::BudgetFact.where(:year => year, :month => month).delete_all
  end

  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 # REFRESH_MUTEX.synchronize
end

.report_progress(percent, message) ⇒ Object

Report progress to callback block if provided



250
251
252
 
# File 'app/models/analytic/budget_fact.rb', line 250

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



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

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|
      if business_unit_id.nil? && project_id.nil? && supplier_id.nil?
        # Level 1: By company + ledger_account - O(1) lookup
        total += @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
        total += @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
        total += @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
        total += @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



589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
 
# File 'app/models/analytic/budget_fact.rb', line 589

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

  .each do |lca_id|
    if business_unit_id.nil? && project_id.nil? && supplier_id.nil?
      # Level 1: Aggregate all for this LCA - O(1) lookup
      total += @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
      total += @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
      total += @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
      total += @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:



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

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