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 = 
[10001, 10002, 10003]

Constants included from Models::Auditable

Models::Auditable::ALWAYS_IGNORED

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::EventPublishable

#publish_event

Instance Attribute Details

#amount ⇒ Object (readonly) 



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

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

#business_unit_id ⇒ Object (readonly) 



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

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

#company_id ⇒ Object (readonly) 



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

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

#currency ⇒ Object (readonly) 



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

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

#description ⇒ Object (readonly) 



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

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

#ledger_account_id ⇒ Object (readonly) 



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

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

#month ⇒ Object (readonly) 



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

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

#supplier_type ⇒ Object

Returns the value of attribute supplier_type.



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

def supplier_type
  @supplier_type
end

#year ⇒ Object (readonly) 



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

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

Class Method Details

.closed_to_date(company) ⇒ Object 



185
186
187
 
# File 'app/models/budget.rb', line 185

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) ⇒ Object 



157
158
159
160
161
162
163
164
165
 
# File 'app/models/budget.rb', line 157

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) ⇒ Object 



106
107
108
109
110
111
112
113
114
115
 
# File 'app/models/budget.rb', line 106

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) ⇒ Object 



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

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) ⇒ Object 



69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'app/models/budget.rb', line 69

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) ⇒ Object 



175
176
177
178
179
 
# File 'app/models/budget.rb', line 175

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 ⇒ Object 



181
182
183
 
# File 'app/models/budget.rb', line 181

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

.summary_import_from_csv(file_path) ⇒ Object 



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

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) ⇒ Object 



167
168
169
170
171
172
173
 
# File 'app/models/budget.rb', line 167

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:



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

belongs_to :business_unit, optional: true

#company ⇒ Company

Returns:

See Also:



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

belongs_to :company, optional: true

#ledger_account ⇒ LedgerAccount

Returns:

See Also:



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

belongs_to :ledger_account, optional: true

#ledger_project ⇒ LedgerProject

Returns:

See Also:



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

belongs_to :ledger_project, optional: true

#set_company_accounts ⇒ Object 



203
204
205
206
207
 
# File 'app/models/budget.rb', line 203

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 ⇒ Object 



189
190
191
192
193
194
195
196
197
198
199
200
201
 
# File 'app/models/budget.rb', line 189

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:



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

belongs_to :supplier, optional: true

#supplier_name ⇒ Object 



65
66
67
 
# File 'app/models/budget.rb', line 65

def supplier_name
 	supplier.try(:full_name)
end

#to_s ⇒ Object 



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

def to_s
	"Budget #{id}"
end