Class: LedgerEntry

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

Overview

== Schema Information

Table name: ledger_entries
Database name: primary

id :integer not null, primary key
amount :decimal(12, 2)
bank_date :date
company_amount :decimal(12, 2)
company_exchange_rate :float
consolidated_amount :decimal(12, 2)
consolidated_exchange_rate :float
currency :string(3) not null
description :string(255)
reconciled :boolean default(FALSE), not null
reconciled_at :datetime
created_at :datetime
updated_at :datetime
business_unit_id :integer
creator_id :integer
ledger_company_account_id :integer not null
ledger_detail_project_id :integer
ledger_transaction_id :integer not null
reconciled_by_id :integer
updater_id :integer

Indexes

by_buid_ltid (business_unit_id,ledger_transaction_id)
by_lcaid_buid_ltid (ledger_company_account_id,business_unit_id,ledger_transaction_id)
by_reconciled_and_account (reconciled,ledger_company_account_id)
idx_lt_id_lca_id (ledger_transaction_id,ledger_company_account_id)
index_ledger_entries_on_bank_date (bank_date)
lca_id_lt_id (ledger_company_account_id,ledger_transaction_id)
ldp_id_bu_id_lt_id (ledger_detail_project_id,business_unit_id,ledger_transaction_id)
lt_id_reconciled (ledger_transaction_id,reconciled)

Constant Summary collapse

LOCKED_ATTRIBUTES = 
["ledger_transaction_id", "ledger_company_account_id", "currency", "amount", "company_exchange_rate", "company_amount",
"consolidated_exchange_rate", "consolidated_amount", "business_unit_id", "ledger_detail_project_id"]
BANK_REC_ATTRIBUTES = 
["reconciled", "bank_date"]

Constants included from Models::Auditable

Models::Auditable::ALWAYS_IGNORED

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

Class Method Details

.filter(params) ⇒ Object 



132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
 
# File 'app/models/ledger_entry.rb', line 132

def self.filter(params)
  # ledger_entries = LedgerEntry.select("distinct(ledger_entries.*)").with_transaction_and_resource.order("ledger_transactions.transaction_date desc, ledger_transactions.transaction_number desc, ledger_entries.id desc")
  params[:transaction_type] = params[:transaction_type].map(&:presence).compact if params[:transaction_type].present?
  # params[:reconciled] = params[:reconciled].to_b if params[:reconciled].present?
  params[:payment_type] = params[:payment_type].map(&:presence).compact if params[:payment_type].present?
  params[:company_account_id] = (params[:company_account_id].map(&:presence).compact).map!(&:to_i) if params[:company_account_id].present?
  ledger_entries = LedgerEntry.joins("left join ledger_transactions on ledger_entries.ledger_transaction_id = ledger_transactions.id")
                              .joins("left join receipts on ledger_transactions.receipt_id = receipts.id")
                              .joins("left join outgoing_payments on ledger_transactions.outgoing_payment_id = outgoing_payments.id")
                              .select("ledger_entries.*,ledger_transactions.transaction_number,ledger_transactions.transaction_type,ledger_transactions.company_id,ledger_transactions.transaction_date,ledger_transactions.description,ledger_transactions.currency,outgoing_payments.category,receipts.category")
                              .order("ledger_transactions.transaction_date desc, ledger_transactions.transaction_number desc, ledger_entries.id desc")
  ledger_entries = ledger_entries.where("ledger_transactions.transaction_type in (?)", params[:transaction_type]) if params[:transaction_type].present?
  # ledger_entries = ledger_entries.where("ledger_entries.reconciled = ?", params[:reconciled]) if params[:reconciled] == false || params[:reconciled] == true
  ledger_entries = ledger_entries.where("outgoing_payments.category in (?) or receipts.category in (?)", params[:payment_type].collect {|pt| pt.downcase.underscore}, params[:payment_type]) if params[:payment_type].present?
  if params[:transaction_date_gteq].present? or params[:transaction_date_lteq].present?
    start_date = params[:transaction_date_gteq].present? ? params[:transaction_date_gteq] : '2000-01-01'
    end_date = params[:transaction_date_lteq].present? ? params[:transaction_date_lteq] : Time.current.utc.to_date.to_fs(:db)
    ledger_entries = ledger_entries.where("ledger_transactions.transaction_date between ? and ?", start_date.to_date, end_date.to_date)
  end
  if params[:bank_date_gteq].present? or params[:bank_date_lteq].present?
    bank_start_date = params[:bank_date_gteq].present? ? params[:bank_date_gteq] : '2000-01-01'
    bank_end_date = params[:bank_date_lteq].present? ? params[:bank_date_lteq] : Time.current.utc.to_date.to_fs(:db)
    ledger_entries = ledger_entries.where("ledger_entries.bank_date between ? and ?", bank_start_date.to_date, bank_end_date.to_date)
  end
  if params[:reconciled_date_gteq].present? or params[:reconciled_date_lteq].present?
    reconciled_start_date = params[:reconciled_date_gteq].present? ? params[:reconciled_date_gteq] : '2000-01-01'
    reconciled_end_date = params[:reconciled_date_lteq].present? ? params[:reconciled_date_lteq] : Time.current.utc.to_date.to_fs(:db)
    ledger_entries = ledger_entries.where("ledger_entries.reconciled_at between ? and ?", reconciled_start_date.to_date, reconciled_end_date.to_date)
  end
  ledger_entries = ledger_entries.where("ledger_entries.ledger_company_account_id in (?)", params[:company_account_id]) if params[:company_account_id].present?
  # ledger_entries = ledger_entries.where("ledger_transactions.transaction_number = ?", params[:transaction_number]) unless params[:transaction_number].blank?
  # ledger_entries = ledger_entries.where("ledger_transactions.company_id in (?)", params[:company_id]) unless params[:company_id].blank?
  # ledger_entries = ledger_entries.where("ledger_entries.reconciled = ?", params[:reconciled]) unless params[:reconciled].blank?
  # ledger_entries = ledger_entries.where(:receipts => {:card_type => params[:card_type]}) unless params[:card_type].blank?
  # unless params[:description].blank?
  #   description = "%#{params[:description]}%"
  #   ledger_entries = ledger_entries.where("ledger_transactions.description like ? or ledger_entries.description like ?", description, description)
  # end
  # ledger_entries = ledger_entries.where("ledger_entries.amount = ?", BigDecimal(params[:amount])) unless params[:amount].blank?
  # ledger_entries = ledger_entries.where("ledger_transactions.currency in (?)", params[:currency]) unless params[:currency].blank?
  # ledger_entries = ledger_entries.where("ledger_entries.ledger_detail_project_id in (?)", params[:project_id]) unless params[:project_id].blank?
  # ledger_entries = ledger_entries.where("ledger_entries.business_unit_id in (?)", params[:business_unit_id]) unless params[:business_unit_id].blank?
  return ledger_entries
end

.get_materials_in_transit(company_id, start_date, end_date, po_number_ref, reconciled) ⇒ Object 



216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
 
# File 'app/models/ledger_entry.rb', line 216

def self.get_materials_in_transit(company_id,start_date,end_date,po_number_ref,reconciled)
  accounts = company_id.empty? ? LedgerCompanyAccount.where(name: 'Materials in Transit').map(&:id).join(',') : LedgerCompanyAccount.where(name: 'Materials in Transit').where(company_id: company_id).first.id
   = company_id.empty? ? " where ledger_company_account_id in (#{accounts}) " : " where ledger_company_account_id = #{accounts} "
  if start_date.present? && end_date.present?
    dates_sql = " and ltr.transaction_date between '#{start_date}' and '#{end_date}' "
  elsif start_date.present? && end_date.empty?
    dates_sql = " and ltr.transaction_date >= '#{start_date}' "
  end
  po_numbers = po_number_ref.strip.split(/[\s,]+/).map(&:strip).reject(&:blank?)
  description_sql = po_numbers.present? ? " and le.description similar to '%(#{po_numbers.join('|')})%' " : " "
  reconciled_sql = reconciled.present? ?  (reconciled.to_bool == true ? ' and reconciled = true ' : ' and reconciled = false ') : ' '
  sql = <<-SQL
    select ltr.id as ledger_transaction_id,ltr.transaction_number,lca.company_id,c.short_name,le.id as ledger_entry_id,le.ledger_company_account_id,acc.number as account,
    ltr.transaction_type as type,ltr.transaction_date as gl_date,le.currency,le.amount,le.company_amount,le.company_exchange_rate as exchange_rate,le.description,le.reconciled as vcr_reconciled
    from ledger_accounts acc
    inner join ledger_company_accounts lca on acc.id = lca.ledger_detail_account_id
    inner join ledger_entries le on le.ledger_company_account_id = lca.id
    inner join ledger_transactions ltr on le.ledger_transaction_id = ltr.id
    inner join companies c on lca.company_id = c.id
    #{}
    #{dates_sql}
    #{description_sql}
    #{reconciled_sql}
  SQL

  materials_in_transit = ActiveRecord::Base.connection.execute(sql).to_a.map{ |r| r.symbolize_keys }
end

.get_receipts_not_vouchered(company_id, start_date, end_date, po_number_ref, reconciled) ⇒ Object 



244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
 
# File 'app/models/ledger_entry.rb', line 244

def self.get_receipts_not_vouchered(company_id,start_date,end_date,po_number_ref,reconciled)
  accounts = company_id.empty? ? LedgerCompanyAccount.where(name: 'Receipts Not Vouchered').map(&:id).join(',') : LedgerCompanyAccount.where(name: 'Receipts Not Vouchered').where(company_id: company_id).first.id
   = company_id.empty? ? " where ledger_company_account_id in (#{accounts}) " : " where ledger_company_account_id = #{accounts} "
  if start_date.present? && end_date.present?
    dates_sql = " and ltr.transaction_date between '#{start_date}' and '#{end_date}' "
  elsif start_date.present? && end_date.empty?
    dates_sql = " and ltr.transaction_date >= '#{start_date}' "
  end
  po_numbers = po_number_ref.strip.split(/[\s,]+/).map(&:strip).reject(&:blank?)
  description_sql = po_numbers.present? ? " and (po.po_numbers similar to '%(#{po_numbers.join('|')})%' or le.description similar to '%(#{po_numbers.join('|')})%') " : " "
  reconciled_sql = reconciled.present? ?  (reconciled.to_bool == true ? ' and reconciled = true ' : ' and reconciled = false ') : ' '
  lca_ids = LedgerCompanyAccount.where(name: 'Receipts Not Vouchered').map(&:id)

  sql = <<-SQL
    with receipts_tb as (
        select shipment_receipt_id,purchase_order_id,array_agg(distinct po.reference_number)::varchar as po_numbers,abs(sum(sri.total_cost)) as total_cost
        from shipment_receipt_items sri
        inner join purchase_orders po on sri.purchase_order_id = po.id
        group by shipment_receipt_id,purchase_order_id
    ), r_transactions_tb as (
        select lt.id as ledger_transaction_id,shipment_receipt_id,abs(sum(amount)) as amount
        from ledger_transactions lt
        inner join ledger_entries le on lt.id = le.ledger_transaction_id and ledger_company_account_id in (#{lca_ids.join(',')})
        where shipment_receipt_id is not null
        group by lt.id,shipment_receipt_id
    ), landed_cost_tb as (
        select lc.id as landed_cost_id,purchase_order_id,array_agg(distinct po.reference_number)::varchar as po_numbers,abs(round(sum(lc.total_landed_cost),2)) as total_cost
        from landed_costs lc
        inner join purchase_orders po on lc.purchase_order_id = po.id
        group by lc.id,purchase_order_id
    ), lc_transactions_tb as (
        select lt.id as ledger_transaction_id,landed_cost_id,abs(sum(amount)) as amount
        from ledger_transactions lt
        inner join ledger_entries le on lt.id = le.ledger_transaction_id and ledger_company_account_id in (#{lca_ids.join(',')})
        where landed_cost_id is not null
        group by lt.id,landed_cost_id
    ), pot_same_amount as (
        select ledger_transaction_id,count(*)
        from receipts_tb rt
        inner join r_transactions_tb tt on rt.shipment_receipt_id = tt.shipment_receipt_id and rt.total_cost = tt.amount
        group by ledger_transaction_id
        having count(*) > 1
    ), lt_same_amount as (
        select lt.id as ledger_transaction_id,shipment_receipt_id,abs(sum(amount)) as amount,
                case when lt.id = 44932 then 522
                when lt.id = 44933 then 527
                when lt.id = 513103 then 5686
                when lt.id = 513104 then 5687
                when lt.id = 618809 then 6513
                when lt.id = 618810 then 6779
                when lt.id = 619478 then 6642
                when lt.id = 619479 then 6685 else null end as purchase_order_id
        from ledger_transactions lt
        inner join ledger_entries le on lt.id = le.ledger_transaction_id and ledger_company_account_id in (#{lca_ids.join(',')})
        where lt.id in (select ledger_transaction_id from pot_same_amount)
        group by lt.id,shipment_receipt_id
    ), purchase_orders_transactions as (
        select ledger_transaction_id,po_numbers
        from receipts_tb rt
        inner join r_transactions_tb tt on rt.shipment_receipt_id = tt.shipment_receipt_id and rt.total_cost = tt.amount
        where ledger_transaction_id not in (select ledger_transaction_id from pot_same_amount)
        union all
        select ledger_transaction_id,po_numbers
        from lt_same_amount lsa
        inner join receipts_tb rt on lsa.shipment_receipt_id = rt.shipment_receipt_id and lsa.purchase_order_id = rt.purchase_order_id
        union all
        select ledger_transaction_id,po_numbers
        from landed_cost_tb lct
        left join lc_transactions_tb ltt on lct.landed_cost_id = ltt.landed_cost_id and lct.total_cost = ltt.amount
    )
    select ltr.id as ledger_transaction_id,ltr.transaction_number,lca.company_id,c.short_name,le.id as ledger_entry_id,le.ledger_company_account_id,acc.number as account,
    ltr.transaction_type as type,ltr.transaction_date as gl_date,le.currency,ltr.exchange_rate,le.amount,le.company_amount,po.po_numbers,le.description,le.reconciled as po_reconciled
    from ledger_accounts acc
    inner join ledger_company_accounts lca on acc.id = lca.ledger_detail_account_id
    inner join ledger_entries le on le.ledger_company_account_id = lca.id
    inner join ledger_transactions ltr on le.ledger_transaction_id = ltr.id
    inner join companies c on lca.company_id = c.id
    left join purchase_orders_transactions po on po.ledger_transaction_id = ltr.id
    #{}
    #{dates_sql}
    #{description_sql}
    #{reconciled_sql}
  SQL

  receipts_not_vouchered = ActiveRecord::Base.connection.execute(sql).to_a.map{ |r| r.symbolize_keys }
end

.with_transaction_and_resource ⇒ ActiveRecord::Relation<LedgerEntry>

A relation of LedgerEntries that are with transaction and resource. Active Record Scope

Returns:

See Also:



65
 
# File 'app/models/ledger_entry.rb', line 65

scope :with_transaction_and_resource, -> { includes([{:ledger_transaction => [:item_ledger_entries, :company, {:invoice => :customer}, {:receipt => :customer}, :shipment_receipt, :landed_cost, {:voucher => :supplier}, {:credit_memo => :customer}, {:outgoing_payment => [:supplier, :checks]}], :ledger_company_account => [:ledger_detail_account, :company]}, :ledger_detail_project, :business_unit]) }

Instance Method Details

#bank_rec_date_not_closed ⇒ Object 



206
207
208
209
210
211
212
213
214
 
# File 'app/models/ledger_entry.rb', line 206

def bank_rec_date_not_closed
  lt = self.ledger_transaction
  unless lt.nil? or lt.company_id.nil? or self.bank_date.nil?
    if self.changed? and self.changes.keys.any? {|attr| BANK_REC_ATTRIBUTES.include?(attr)}
      lcls = LedgerClosingPeriod.company_in_all_or(lt.company_id).transaction_type_in("BANK_REC").where("close_to >= ?", self.bank_date.to_fs(:db))
      errors.add :base, "Bank reconciliation is closed up to #{lcls.first.close_to.to_fs(:crm_default)} for #{lt.company.short_name}." if lcls.any?
    end
  end
end

#bank_rec_edit_allowed ⇒ Object 



195
196
197
198
199
200
201
202
203
204
 
# File 'app/models/ledger_entry.rb', line 195

def bank_rec_edit_allowed
  lt = self.ledger_transaction
  unless lt.nil? or lt.company_id.nil? or self.bank_date_was.nil?
    if (self.reconciled_changed? and self.reconciled_was == true) or self.bank_date_changed?
      # it had previously been reconciled, so check the books are not closed for bank recs on the old date
      lcls = LedgerClosingPeriod.company_in_all_or(lt.company_id).transaction_type_in("BANK_REC").where("close_to >= ?", (self.bank_date_was || self.bank_date).to_fs(:db))
      errors.add :base, "Bank reconciliation details cannot be changed as they have already been closed up to #{lcls.first.close_to.to_fs(:crm_default)} for #{lt.company.short_name}." if lcls.any?
    end
  end
end

#business_unit ⇒ BusinessUnit

Returns:

See Also:



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

belongs_to :business_unit, optional: true

#business_unit_belongs_to_same_company ⇒ Object 



126
127
128
129
130
 
# File 'app/models/ledger_entry.rb', line 126

def business_unit_belongs_to_same_company
  if business_unit_id.present? and .present? and business_unit.company_id != .company_id
    errors.add(:business_unit_id, "must belong to same company as the chosen account")
  end
end

#currency_symbol ⇒ Object 



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

def currency_symbol
  Money::Currency.new(currency).symbol
end

#edit_allowed ⇒ Object 



185
186
187
188
189
190
191
192
193
 
# File 'app/models/ledger_entry.rb', line 185

def edit_allowed
  lt = self.ledger_transaction
  unless lt.nil? or lt.company_id.nil? or lt.transaction_type.nil? or lt.transaction_date.nil?
    if self.changed? and self.changes.keys.any? {|attr| LOCKED_ATTRIBUTES.include?(attr)}
      lcls = LedgerClosingPeriod.company_in_all_or(lt.company_id).transaction_type_in_all_or(lt.transaction_type).where("close_to >= ?", lt.transaction_date.to_fs(:db))
      errors.add :base, "Period is closed for #{lt.transaction_type} for #{lt.company.short_name} on #{lcls.first.close_to.to_fs(:crm_default)}." if lcls.any?
    end
  end
end

#ledger_company_account ⇒ LedgerCompanyAccount

Returns:

See Also:



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

belongs_to :ledger_company_account, :inverse_of => :ledger_entries, optional: true

#ledger_detail_project ⇒ LedgerDetailProject

Returns:

See Also:



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

belongs_to :ledger_detail_project, :inverse_of => :ledger_entries, optional: true

#ledger_transaction ⇒ LedgerTransaction

Returns:

See Also:



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

belongs_to :ledger_transaction, :inverse_of => :ledger_entries, optional: true

#period_not_closed ⇒ Object 



177
178
179
180
181
182
183
 
# File 'app/models/ledger_entry.rb', line 177

def period_not_closed
  lt = self.ledger_transaction
  unless lt.nil? or lt.company_id.nil? or lt.transaction_type.nil? or lt.transaction_date.nil?
    lcls = LedgerClosingPeriod.company_in_all_or(lt.company_id).transaction_type_in_all_or(lt.transaction_type).where("close_to >= ?", lt.transaction_date.to_fs(:db))
    errors.add :base, "Period is closed for #{lt.transaction_type} for #{lt.company.short_name} up to #{lcls.first.close_to.to_fs(:crm_default)}." if lcls.any?
  end
end

#reconciled_by ⇒ Employee

Returns:

See Also:



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

belongs_to :reconciled_by, :class_name => "Employee", optional: true

#requires_business_unit? ⇒ Boolean

Returns:

  • (Boolean) 


71
72
73
 
# File 'app/models/ledger_entry.rb', line 71

def requires_business_unit?
  .present? && .&.requires_business_unit?
end

#set_company_amount ⇒ Object 



81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'app/models/ledger_entry.rb', line 81

def set_company_amount
  unless self.currency.nil? or self.amount.nil? or self..nil?
    if self.new_record? or self.company_amount.nil? or self. or self.amount_changed? or (self.ledger_transaction and (self.ledger_transaction.transaction_date_changed? or self.ledger_transaction.exchange_rate_changed?))
      if self.currency != self..company.currency
        # the currency of the entry is different to the owning company's currency
        # need to set the exchange rate and record the company amount
        exchange_rate = self.ledger_transaction.exchange_rate || ExchangeRate.get_exchange_rate(self.currency, self..company.currency, self.ledger_transaction.transaction_date)
        self.company_exchange_rate = exchange_rate
        self.company_amount = (self.amount * self.company_exchange_rate).round(2)
      else
        # it's the same so we just copy the amounts and set the exchange rate as 1
        self.company_exchange_rate = 1.0
        self.company_amount = self.amount
      end
    elsif self.amount.present? and self.company_exchange_rate.present? and self.company_amount != self.amount * self.company_exchange_rate
      self.company_amount = (self.amount * self.company_exchange_rate).round(2)
    end
  end
end

#set_consolidated_amount ⇒ Object 



101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
# File 'app/models/ledger_entry.rb', line 101

def set_consolidated_amount
  unless self.currency.nil? or self.amount.nil?
    if self.new_record? or self.consolidated_amount.nil? or self. or self.amount_changed? or (self.ledger_transaction and self.ledger_transaction.transaction_date_changed?)
      if self.currency != CONSOLIDATED_CURRENCY && self..company.currency == CONSOLIDATED_CURRENCY && self.company_amount != nil
        # the currency of the entry is different to the consolidated currency, but the linked account is the same currency
        # so we can just copy the company amount if it's already
        self.consolidated_exchange_rate = self.company_exchange_rate
        self.consolidated_amount = self.company_amount
      elsif self.currency != CONSOLIDATED_CURRENCY
        # the currency of the entry is different to the owning company's currency
        # need to set the exchange rate and record the company amount
        exchange_rate = ExchangeRate.get_exchange_rate(self.currency, CONSOLIDATED_CURRENCY, self.ledger_transaction.transaction_date)
        self.consolidated_exchange_rate = exchange_rate
        self.consolidated_amount = (self.amount * self.consolidated_exchange_rate).round(2)
      else
        # it's the same so we just copy the amounts and set the exchange rate as 1
        self.consolidated_exchange_rate = 1.0
        self.consolidated_amount = self.amount
      end
    elsif self.amount.present? and self.consolidated_exchange_rate.present? and self.consolidated_amount != self.amount * self.consolidated_exchange_rate
      self.consolidated_amount = (self.amount * self.consolidated_exchange_rate).round(2)
    end
  end
end

#set_currency ⇒ Object 



75
76
77
78
79
 
# File 'app/models/ledger_entry.rb', line 75

def set_currency
  if self.new_record? or (self.ledger_transaction and self.ledger_transaction.currency_changed?)
    self.currency = self.ledger_transaction.currency rescue nil
  end
end