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

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

#publish_event

Class Method Details

.account_beginning_balance(account_number, year, company_id) ⇒ Object 



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
90
91
92
93
94
95
96
97
98
99
100
 
# File 'app/models/ledger_beginning_balance.rb', line 63

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 { |r| r.symbolize_keys! }
end

.accounts ⇒ Object 



102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'app/models/ledger_beginning_balance.rb', line 102

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 { |r| r.symbolize_keys! }
end

.populate_table ⇒ Object 



26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'app/models/ledger_beginning_balance.rb', line 26

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



41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'app/models/ledger_beginning_balance.rb', line 41

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