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 Models::Schedulable

Models::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 Models::Schedulable

config

Methods included from Models::AfterCommittable

#after_commit

Methods included from Models::EventPublishable

#publish_event

Instance Attribute Details

#amount ⇒ Object (readonly)

Validates description, company id, ledger account id, business unit id, year, month, currency, amount.

Validations:



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

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

#business_unit_id ⇒ Object (readonly)

Validates description, company id, ledger account id, business unit id, year, month, currency, amount.

Validations:



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

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

#company_id ⇒ Object (readonly)

Validates description, company id, ledger account id, business unit id, year, month, currency, amount.

Validations:



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

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

#currency ⇒ Object (readonly)

Validates description, company id, ledger account id, business unit id, year, month, currency, amount.

Validations:



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

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

#description ⇒ Object (readonly)

Validates description, company id, ledger account id, business unit id, year, month, currency, amount.

Validations:



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

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

#ledger_account_id ⇒ Object (readonly)

Validates description, company id, ledger account id, business unit id, year, month, currency, amount.

Validations:



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

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

#month ⇒ Object (readonly)

Validates description, company id, ledger account id, business unit id, year, month, currency, amount.

Validations:



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

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) 


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

def supplier_type
  @supplier_type
end

#year ⇒ Object (readonly)

Validates description, company id, ledger account id, business unit id, year, month, currency, amount.

Validations:



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

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) 


263
264
265
 
# File 'app/models/budget.rb', line 263

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

Parameters:

  • existing_attrs (Hash)
  • new_attrs (Hash)

Returns:



219
220
221
222
223
224
225
226
227
 
# File 'app/models/budget.rb', line 219

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

Returns true.

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



147
148
149
150
151
152
153
154
155
156
 
# File 'app/models/budget.rb', line 147

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

Returns true.

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



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

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

    path to the CSV file to import

Returns:

  • (Boolean) —

    true on success

Raises:

  • (RuntimeError) —

    when file_path is missing



92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
 
# File 'app/models/budget.rb', line 92

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:



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

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:



255
256
257
 
# File 'app/models/budget.rb', line 255

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

    path to the summary-layout CSV file

Returns:

  • (Boolean)

Raises:

  • (RuntimeError) —

    when file_path is missing



119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
 
# File 'app/models/budget.rb', line 119

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

Parameters:

Returns:

  • (BigDecimal) 


235
236
237
238
239
240
241
 
# File 'app/models/budget.rb', line 235

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 the business unit this record belongs to.

Returns:

  • (BusinessUnit, nil) —

    the business unit this record belongs to



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

belongs_to :business_unit, optional: true

#company ⇒ Company?

Returns the company this record belongs to.

Returns:

  • (Company, nil) —

    the company this record belongs to



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

belongs_to :company, optional: true

#ledger_account ⇒ LedgerAccount?

Returns the ledger account this record belongs to.

Returns:

  • (LedgerAccount, nil) —

    the ledger account this record belongs to



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

belongs_to :ledger_account, optional: true

#ledger_project ⇒ LedgerProject?

Returns the ledger project this record belongs to.

Returns:

  • (LedgerProject, nil) —

    the ledger project this record belongs to



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

belongs_to :ledger_project, optional: true

#set_company_accounts ⇒ void

This method returns an undefined value.



293
294
295
296
297
 
# File 'app/models/budget.rb', line 293

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.



273
274
275
276
277
278
279
280
281
282
283
284
285
 
# File 'app/models/budget.rb', line 273

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 the supplier this record belongs to.

Returns:

  • (Supplier, nil) —

    the supplier this record belongs to



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

belongs_to :supplier, optional: true

#supplier_name ⇒ String?

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

Returns:

  • (String, nil) 


80
81
82
 
# File 'app/models/budget.rb', line 80

def supplier_name
 	supplier.try(:full_name)
end

#to_s ⇒ String

Returns:

  • (String) 


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

def to_s
	"Budget #{id}"
end