Class: LedgerBeginningBalance

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

Overview

== Schema Information

Table name: ledger_beginning_balances
Database name: primary

id :integer not null, primary key
account_name :string not null
account_number :integer not null
beginning_balance_amount :float
month :integer not null
year :integer not null
account_id :integer not null
company_id :integer not null

Indexes

index_ledger_beginning_balances_on_account_id (account_id)
index_ledger_beginning_balances_on_account_number (account_number)
index_ledger_beginning_balances_on_company_id (company_id)
index_ledger_beginning_balances_on_month (month)
index_ledger_beginning_balances_on_year (year)

Constant Summary

Constants included from Models::Auditable

Models::Auditable::ALWAYS_IGNORED

Constants included from Schedulable

Schedulable::SIMPLE_FORM_OPTIONS

Class Method Summary collapse

Methods included from Models::Auditable

#all_skipped_columns, #audit_reference_data, #creator, #should_not_save_version, #stamp_record, #updater

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

Class Method Details

.account_beginning_balance(account_number, year, company_id) ⇒ Array<Hash>

Compute the year-start beginning balance for one
(account_number, year, company) triple. Account 4999
rolls up Net Income (>= 4999), 4980 rolls up Retained
Earnings (everything ≤ that year), and any other account is
just summed for that year and prior.

Parameters:

  • account_number (Integer)
  • year (Integer)
  • company_id (Integer)

Returns:

  • (Array<Hash>) —

    one-row result set, or empty when no postings



85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
 
# File 'app/models/ledger_beginning_balance.rb', line 85

def self.(,year,company_id)
  if  == 4999
    columns = "999::int as account_id,4999::int as account_number,'Net Income'::varchar as account_name,LCA.company_id,#{(year + 1)}::int as year,1::int as month,sum(LEN.company_amount) as beginning_balance_amount "
    where_one = " where LACC.number >= #{}"
    where_two = " and to_char(LTR.transaction_date,'yyyy')::int = #{year}"
    where_three = " and LCA.company_id = #{company_id}"
    group_by = " group by LCA.company_id;"
  elsif  == 4980
    columns = "106::int as account_id,4980::int as account_number,'Retained Earnings'::varchar as account_name,LCA.company_id,#{(year + 2)}::int as year,1::int as month,sum(LEN.company_amount) as beginning_balance_amount "
    where_one = " where LACC.number >= #{}"
    where_two = " and to_char(LTR.transaction_date,'yyyy')::int <= #{year}"
    where_three = " and LCA.company_id = #{company_id}"
    group_by = " group by LCA.company_id;"
  else
    columns = "LACC.id as account_id,LACC.number as account_number,LCA.name as account_name,LCA.company_id,#{(year + 1)}::int as year,1::int as month,sum(LEN.company_amount) as beginning_balance_amount "
    where_one = " where LACC.number = #{}"
    where_two = " and to_char(LTR.transaction_date,'yyyy')::int <= #{year}"
    where_three = " and LCA.company_id = #{company_id}"
    group_by = " group by LACC.id,LACC.number,LCA.name,LCA.company_id;"
  end

   = <<-SQL
    select
    #{columns}
    from ledger_entries LEN
    left join ledger_transactions LTR on LEN.ledger_transaction_id=LTR.id
    left join ledger_company_accounts LCA on LEN.ledger_company_account_id=LCA.id
    left join ledger_accounts LACC on LCA.ledger_detail_account_id=LACC.id
    left join business_units BU on BU.id=LEN.business_unit_id
    #{where_one}
    #{where_two}
    #{where_three}
    #{group_by}
  SQL

  query_result = ActiveRecord::Base.connection.execute()
  query_result.map(&:symbolize_keys!)
end

.accounts ⇒ Array<Hash>

All distinct posted account numbers ≤ 4999 plus 4999 itself
— the working set the back-fill iterates over.

Returns:

  • (Array<Hash>) 


127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
 
# File 'app/models/ledger_beginning_balance.rb', line 127

def self.accounts
  sql_query = <<-SQL
    select distinct LACC.number
    from ledger_entries LEN
    left join ledger_transactions LTR on LEN.ledger_transaction_id=LTR.id
    left join ledger_company_accounts LCA on LEN.ledger_company_account_id=LCA.id
    left join ledger_accounts LACC on LCA.ledger_detail_account_id=LACC.id
    left join business_units BU on BU.id=LEN.business_unit_id
    where LACC.number <= 4999
    union all
    select 4999
    order by number
  SQL

  result = ActiveRecord::Base.connection.execute(sql_query)
  result.map(&:symbolize_keys!)
end

.populate_table ⇒ Symbol

One-shot back-fill of the beginning-balance cache table. Walks
every distinct P&L account from 2007 through last year, for
both companies (US, CA), and bulk-inserts the rows. Idempotent
via on_duplicate_key_ignore.

Returns:

  • (Symbol) —

    :ok



33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
# File 'app/models/ledger_beginning_balance.rb', line 33

def self.populate_table
  beginning_balance = []
   = accounts

  .each do ||
    (2007..(Date.current.year - 1)).each do |y|
      beginning_balance << ([:number].to_i,y,1)[0] if ([:number].to_i,y,1).empty? == false
      beginning_balance << ([:number].to_i,y,2)[0] if ([:number].to_i,y,2).empty? == false
    end
  end

  result = import beginning_balance, on_duplicate_key_ignore: true
  return :ok
end

.refresh_data ⇒ Symbol

Recompute the rolling-window cache: drop last-year + this-year
rows for P&L / retained-earnings accounts and reinsert them.
Run nightly so closing-period adjustments propagate.

Returns:

  • (Symbol) —

    :ok



53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
# File 'app/models/ledger_beginning_balance.rb', line 53

def self.refresh_data
  sql_delete_previous_year = <<-SQL
  delete
  from ledger_beginning_balances
  where (((account_number <= 4910 or account_number = 4990 or account_number = 4999) and year >= #{Date.current.year - 1}) or (account_number = 4980 and year > #{Date.current.year - 1}));
  SQL
  ActiveRecord::Base.connection.execute(sql_delete_previous_year)

  beginning_balance = []
   = accounts

  .each do ||
    ((Date.current.year - 2)..(Date.current.year - 1)).each do |y|
      beginning_balance << ([:number].to_i,y,1)[0] if ([:number].to_i,y,1).empty? == false
      beginning_balance << ([:number].to_i,y,2)[0] if ([:number].to_i,y,2).empty? == false
    end
  end

  result = import beginning_balance, on_duplicate_key_ignore: true
  return :ok
end