Class: Budget

Inherits:
ApplicationRecord show all
Includes:
Models::Auditable
Defined in:
app/models/budget.rb

Overview

== Schema Information

Table name: budgets
Database name: primary

id :integer not null, primary key
all_ledger_company_account_ids_1 :text default([]), is an Array
all_ledger_company_account_ids_2 :text default([]), is an Array
all_ledger_company_account_ids_4 :text default([]), is an Array
amount :decimal(12, 2)
consolidated_amount :decimal(12, 2)
currency :string
description :string
exchange_rate :decimal(, )
is_revenue :boolean
month :integer
quarter :integer
year :integer
created_at :datetime not null
updated_at :datetime not null
business_unit_id :integer
company_id :integer
creator_id :integer
ledger_account_id :integer
ledger_project_id :integer
supplier_id :integer
updater_id :integer

Indexes

index_budgets_on_business_unit_id (business_unit_id)
index_budgets_on_company_id (company_id)
index_budgets_on_ledger_account_id (ledger_account_id)
index_budgets_on_ledger_project_id (ledger_project_id)
index_budgets_on_supplier_id (supplier_id)
index_budgets_on_year_and_month_and_ledger_account_id (year,month,ledger_account_id)
index_budgets_on_year_and_quarter (year,quarter)

Constant Summary collapse

NET_SALES_ACCOUNTS =

GL ledger account IDs that the budget report treats as "net sales"
rollup buckets — used by the CRM Budget report to split revenue
rows from cost rows when totalling.

[10001, 10002, 10003]

Constants included from Models::Auditable

Models::Auditable::ALWAYS_IGNORED

Constants included from Schedulable

Schedulable::SIMPLE_FORM_OPTIONS

Instance Attribute Summary collapse

Belongs to collapse

Methods included from Models::Auditable

#creator, #updater

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Models::Auditable

#all_skipped_columns, #audit_reference_data, #should_not_save_version, #stamp_record

Methods inherited from ApplicationRecord

ransackable_associations, ransackable_attributes, ransackable_scopes, ransortable_attributes, #to_relation

Methods included from Schedulable

config

Methods included from Models::AfterCommittable

#after_commit

Methods included from Models::EventPublishable

#publish_event

Instance Attribute Details

#amount ⇒ Object (readonly) 



56
 
# File 'app/models/budget.rb', line 56

validates :description, :company_id, :ledger_account_id, :business_unit_id, :year, :month, :currency, :amount, presence: true

#business_unit_id ⇒ Object (readonly) 



56
 
# File 'app/models/budget.rb', line 56

validates :description, :company_id, :ledger_account_id, :business_unit_id, :year, :month, :currency, :amount, presence: true

#company_id ⇒ Object (readonly) 



56
 
# File 'app/models/budget.rb', line 56

validates :description, :company_id, :ledger_account_id, :business_unit_id, :year, :month, :currency, :amount, presence: true

#currency ⇒ Object (readonly) 



56
 
# File 'app/models/budget.rb', line 56

validates :description, :company_id, :ledger_account_id, :business_unit_id, :year, :month, :currency, :amount, presence: true

#description ⇒ Object (readonly) 



56
 
# File 'app/models/budget.rb', line 56

validates :description, :company_id, :ledger_account_id, :business_unit_id, :year, :month, :currency, :amount, presence: true

#ledger_account_id ⇒ Object (readonly) 



56
 
# File 'app/models/budget.rb', line 56

validates :description, :company_id, :ledger_account_id, :business_unit_id, :year, :month, :currency, :amount, presence: true

#month ⇒ Object (readonly) 



56
 
# File 'app/models/budget.rb', line 56

validates :description, :company_id, :ledger_account_id, :business_unit_id, :year, :month, :currency, :amount, presence: true

#supplier_type ⇒ String?

Class name ("Supplier", "Customer", …) of the linked
Party for the budget-row supplier dropdown.

Returns:

  • (String, nil) 


68
69
70
 
# File 'app/models/budget.rb', line 68

def supplier_type
  @supplier_type
end

#year ⇒ Object (readonly) 



56
 
# File 'app/models/budget.rb', line 56

validates :description, :company_id, :ledger_account_id, :business_unit_id, :year, :month, :currency, :amount, presence: true

Class Method Details

.closed_to_date(company) ⇒ Date

Latest JOURNAL_ENTRY closing date for company — the
boundary between locked actuals and forecastable months.

Parameters:

Returns:

  • (Date) 


257
258
259
 
# File 'app/models/budget.rb', line 257

def self.closed_to_date(company)
	LedgerClosingPeriod.company_in_all_or(company.id).transaction_type_in_all_or("JOURNAL_ENTRY").order(:close_to).first.close_to
end

.find_or_create_with(existing_attrs, new_attrs) ⇒ Budget

Upsert helper: find a Budget matching existing_attrs and
update it with new_attrs, or create a new row from the
union of both.

Parameters:

  • existing_attrs (Hash)
  • new_attrs (Hash)

Returns:



213
214
215
216
217
218
219
220
221
 
# File 'app/models/budget.rb', line 213

def self.find_or_create_with(existing_attrs, new_attrs)
    b = self.where(existing_attrs).first
    if b.nil?
      b = self.create!(existing_attrs.merge(new_attrs))
    else
      b.update!(new_attrs)
    end
    b
end

.generate_for_month(company_id, year, month, exchange_rate = nil) ⇒ Boolean

Run every active BudgetRule against (company, year, month) to produce / refresh the Budget rows for that
month. Called by BudgetRefresherWorker when rules or
inputs (sales forecast, prior-year actuals) change.

Parameters:

  • company_id (Integer)
  • year (Integer)
  • month (Integer)
  • exchange_rate (BigDecimal, nil) (defaults to: nil) —

    override; nil triggers FX lookup per row

Returns:

  • (Boolean) —

    true



141
142
143
144
145
146
147
148
149
150
 
# File 'app/models/budget.rb', line 141

def self.generate_for_month(company_id, year, month, exchange_rate = nil)
	company = Company.find(company_id)
	currency = company.currency
	closed_to_date = self.closed_to_date(company)
	business_units = BusinessUnit.order(:number).all
	BudgetRule.all.each do |rule|
		self.generate_for_rule(rule, company, currency, closed_to_date, business_units, year, month, exchange_rate)
	end
	return true
end

.generate_for_rule(rule, company, currency, closed_to_date, business_units, year, month, exchange_rate) ⇒ Boolean

Apply one BudgetRule to every BusinessUnit, computing
the monthly amount via the rule's category — sales forecast,
percent-of-net-sales, prior-year actuals, 3- or 12-month
average, or manual zero — and persists the result.

Parameters:

  • rule (BudgetRule)
  • company (Company)
  • currency (String)
  • closed_to_date (Date) —

    last fully-closed period

  • business_units (Array<BusinessUnit>)
  • year (Integer)
  • month (Integer)
  • exchange_rate (BigDecimal, nil)

Returns:

  • (Boolean) —

    true



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
 
# File 'app/models/budget.rb', line 166

def self.generate_for_rule(rule, company, currency, closed_to_date, business_units, year, month, exchange_rate)
	 = rule.
	company_accounts = .company_accounts_for(company)
	business_units.each do |bu|
		case rule.category
		when "sales_forecast"
			forecast = SalesForecast.where(:company => company, :ledger_account => , :business_unit => bu, :year => year, :month => month).first
			amount = forecast.nil? ? 0 : forecast.amount
		when "calculated_percent_net_sales"
			start_date = closed_to_date - 12.months
			end_date = closed_to_date - 1.month
			query = LedgerEntry.joins(:ledger_transaction).where("ledger_transactions.transaction_date between :start_date and :end_date", {:start_date => start_date.beginning_of_month, :end_date => end_date.end_of_month}).where(:ledger_company_account => company_accounts, :business_unit => bu)
			last_12_month_amount = query.sum(:company_amount)
			amount_percent = last_12_month_amount / Budget.total_net_sales_for(company)
			forecast = SalesForecast.where(:company => company, :ledger_account => self.net_sales_ledger_accounts, :year => year, :month => month).sum(:amount)
			amount = amount_percent * forecast
		when "previous_year"
			date = Date.new(Date.current.year, month).prev_year
			query = LedgerEntry.joins(:ledger_transaction).where("ledger_transactions.transaction_date between :start_date and :end_date", {:start_date => date.beginning_of_month, :end_date => date.end_of_month}).where(:ledger_company_account => company_accounts, :business_unit => bu)
			amount = query.sum(:company_amount)
		when "avg_last_3_months"
			start_date = closed_to_date - 3.months
			end_date = closed_to_date - 1.month
			query = LedgerEntry.joins(:ledger_transaction).where("ledger_transactions.transaction_date between :start_date and :end_date", {:start_date => start_date.beginning_of_month, :end_date => end_date.end_of_month}).where(:ledger_company_account => company_accounts, :business_unit => bu)
			amount = query.sum(:company_amount) / 3
		when "avg_last_12_months"
			start_date = closed_to_date - 12.months
			end_date = closed_to_date - 1.month
			query = LedgerEntry.joins(:ledger_transaction).where("ledger_transactions.transaction_date between :start_date and :end_date", {:start_date => start_date.beginning_of_month, :end_date => end_date.end_of_month}).where(:ledger_company_account => company_accounts, :business_unit => bu)
			amount = query.sum(:company_amount) / 12
		when "manual"
			amount = 0
			consolidated_amount = 0
		end

		self.find_or_create_with({company: company, currency: currency, month: month, year: year, ledger_account: , business_unit: bu}, {is_revenue: rule.is_revenue?, description: .name, exchange_rate: exchange_rate, amount: amount})
	end
	return true
end

.import_from_csv(file_path) ⇒ Boolean

Bulk-import per-month budget lines from the standard CSV
template (one row = one month per account / BU / project /
supplier). Idempotent: existing rows are updated rather than
duplicated.

Parameters:

  • file_path (String)

Returns:

  • (Boolean) —

    true on success

Raises:

  • (RuntimeError) —

    when file_path is missing



86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
 
# File 'app/models/budget.rb', line 86

def self.import_from_csv(file_path)
 	raise "File missing at #{file_path}" unless file_path and File.exist?file_path
summary_accounts = {}
LedgerSummaryAccount.all.each {|lsa| summary_accounts[lsa.number] = BigDecimal("0")}
# accounts_key = {5110 => 10001, 5320 => 10002, 5310 => 10003, 6110 => 10004, 6210 => 10005, 6225 => 10006, 5530 => 10007, 8210 => 10008, 8290 => 10009, 8545 => 10010, 8710 => 10011, 8816 => 10012, 8742 => 10013, 6265 => 10014, 6270 => 10015, 9126 => 10016, 8610 => 10017, 9014 => 10018, 9040 => 10019, 9205 => 10020, 9245 => 10021, 6235 => 10022}
 	CSV.foreach(file_path, headers: true) do |row|
   		if row[0].present?
 			# account_num = accounts_key[row['account'].to_i].nil? ? row['account'].to_i : accounts_key[row['account'].to_i]
 			 = LedgerAccount.find_by(number: row['account'].to_i)
 			bu = BusinessUnit.find_by(number: row['bus_unit'])
 			project = row['project'].blank? ? nil : LedgerProject.find_by(project_number: row['project']).id
 			supplier = row['supplier'].blank? ? nil : Party.find(row['supplier']).id
		b = self.find_or_initialize_by(company_id: row['company'], year: row['year'], month: row['month'], ledger_account_id: .id, business_unit_id: bu.id, ledger_project_id: project, supplier_id: supplier)
		b.update!(amount: row['amount'], exchange_rate: row['rate'], currency: row['currency'], description: .name)
 		end
   	end
		return true
end

.net_sales_company_accounts(company) ⇒ Array<LedgerCompanyAccount>

All LedgerCompanyAccounts for the NET_SALES_ACCOUNTS
summary nodes within company.

Parameters:

Returns:



241
242
243
244
245
 
# File 'app/models/budget.rb', line 241

def self.net_sales_company_accounts(company)
	company_accounts = []
	NET_SALES_ACCOUNTS.each {|a| company_accounts.concat(LedgerAccount.find_by(number: a).company_accounts_for(company))}
	return company_accounts
end

.net_sales_ledger_accounts ⇒ Array<LedgerAccount>

LedgerAccount records for NET_SALES_ACCOUNTS.

Returns:



249
250
251
 
# File 'app/models/budget.rb', line 249

def self.net_sales_ledger_accounts
	NET_SALES_ACCOUNTS.collect {|a| LedgerAccount.find_by(number: a)}
end

.summary_import_from_csv(file_path) ⇒ Boolean

Variant of import_from_csv that takes the "summary" layout
(one row per account with twelve month columns) and explodes
it to twelve Budget rows, then queues a
BudgetRefresherWorker per (company, month) touched.

Parameters:

  • file_path (String)

Returns:

  • (Boolean)

Raises:

  • (RuntimeError) —

    when file_path is missing



113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'app/models/budget.rb', line 113

def self.summary_import_from_csv(file_path)
		raise "File missing at #{file_path}" unless file_path and File.exist?file_path
		done = []
		CSV.foreach(file_path, headers: true) do |row|
			if row[0].present?
			 = LedgerAccount.find_by(number: row['Account'].to_i)
			bu = BusinessUnit.find_by(number: row['BU'])
			(1..12).each do |month|
b = Budget.find_or_initialize_by(company_id: row['Company'], year: row[0], month: month, ledger_account_id: .id, business_unit_id: bu.id)
b.update!(amount: row[Date::ABBR_MONTHNAMES[month]], exchange_rate: row['Rate'], currency: row['Currency'], description: .name)
done << [row[0], month]
			end
			end
		end
		done.uniq.each {|enq| BudgetRefresherWorker.perform_async(enq[0].to_i, enq[1])}
		return true
end

.total_net_sales_for(company) ⇒ BigDecimal

Trailing-twelve-month net-sales total (in company currency)
for company, used as the denominator in the
calculated_percent_net_sales rule.

Parameters:

Returns:

  • (BigDecimal) 


229
230
231
232
233
234
235
 
# File 'app/models/budget.rb', line 229

def self.total_net_sales_for(company)
	company_accounts = self.net_sales_company_accounts(company)
	start_date = self.closed_to_date(company) - 12.months
	end_date = self.closed_to_date(company) - 1.month
	query = LedgerEntry.joins(:ledger_transaction).where("ledger_transactions.transaction_date between :start_date and :end_date", {:start_date => start_date.beginning_of_month, :end_date => end_date.end_of_month}).where(:ledger_company_account => company_accounts)
	return query.sum(:company_amount)
end

Instance Method Details

#business_unit ⇒ BusinessUnit

Returns:

See Also:



49
 
# File 'app/models/budget.rb', line 49

belongs_to :business_unit, optional: true

#company ⇒ Company

Returns:

See Also:



52
 
# File 'app/models/budget.rb', line 52

belongs_to :company, optional: true

#ledger_account ⇒ LedgerAccount

Returns:

See Also:



48
 
# File 'app/models/budget.rb', line 48

belongs_to :ledger_account, optional: true

#ledger_project ⇒ LedgerProject

Returns:

See Also:



51
 
# File 'app/models/budget.rb', line 51

belongs_to :ledger_project, optional: true

#set_company_accounts ⇒ void

This method returns an undefined value.

Cache the ids of every descendant LedgerCompanyAccount for
each company onto the row so the budget-actuals join can
unnest the array directly without recursing the hierarchy
at query time.



287
288
289
290
291
 
# File 'app/models/budget.rb', line 287

def set_company_accounts
	self. = .company_accounts_for(Company.find(Company::USA)).collect(&:id)
	self. = .company_accounts_for(Company.find(Company::CAN)).collect(&:id)
	self. = .company_accounts_for(Company.find(Company::NLD)).collect(&:id)
end

#set_consolidated_amount ⇒ void

This method returns an undefined value.

Populate consolidated_amount (USD) and, for non-USD rows,
also flip amount to local currency by multiplying through
the FX rate. Falls back to today's rate when the budget
month is in the future.



267
268
269
270
271
272
273
274
275
276
277
278
279
 
# File 'app/models/budget.rb', line 267

def set_consolidated_amount
	if currency == CONSOLIDATED_CURRENCY
		self.consolidated_amount = amount
	else
		if exchange_rate.nil?
			budget_date = Date.new(year, month)
			date_to_use = Date.current if budget_date > Date.current
			self.exchange_rate = ExchangeRate.get_exchange_rate(CONSOLIDATED_CURRENCY, currency, date_to_use)
		end
		self.consolidated_amount = amount
		self.amount = (amount * exchange_rate).round(2)
   	end
end

#supplier ⇒ Supplier

Returns:

See Also:



50
 
# File 'app/models/budget.rb', line 50

belongs_to :supplier, optional: true

#supplier_name ⇒ String?

Convenience: name of the linked supplier Party, or nil.

Returns:

  • (String, nil) 


74
75
76
 
# File 'app/models/budget.rb', line 74

def supplier_name
 	supplier.try(:full_name)
end

#to_s ⇒ String

Returns:

  • (String) 


61
62
63
 
# File 'app/models/budget.rb', line 61

def to_s
	"Budget #{id}"
end