Class: Report::Reconciliation::Reconciliation

Inherits:
Object
  • Object
show all
Defined in:
app/services/report/reconciliation/reconciliation.rb

Overview

Service object: reconciliation.

Defined Under Namespace

Classes: Result

Class Method Summary collapse

Class Method Details

.query_transactions(company_id, account_number, date) ⇒ Object

Query transactions.

Parameters:

  • company_id (Integer)
  • account_number (Integer)
  • date (Date)

Returns:

  • (Object) 


60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'app/services/report/reconciliation/reconciliation.rb', line 60

def self.query_transactions(company_id, , date)
  LedgerEntry
    .joins('LEFT JOIN ledger_transactions on ledger_entries.ledger_transaction_id = ledger_transactions.id')
    .joins('LEFT JOIN ledger_company_accounts on ledger_entries.ledger_company_account_id = ledger_company_accounts.id')
    .joins('LEFT JOIN ledger_accounts on ledger_company_accounts.ledger_detail_account_id = ledger_accounts.id')
    .joins('left join receipts on receipts.id = ledger_transactions.receipt_id')
    .joins('left join outgoing_payments on outgoing_payments.id = ledger_transactions.outgoing_payment_id')
    .joins('LEFT JOIN business_units on ledger_entries.business_unit_id = business_units.id')
    .joins('left join companies on companies.id = ledger_company_accounts.company_id')
    .where(ledger_company_accounts: { company_id: company_id }).where(ledger_accounts: { number:  })
    .where(ledger_transactions: { transaction_date: ..date })
    .where(LedgerEntry[:bank_date].gt(date).or(LedgerEntry[:reconciled].eq(false))).order(1)
    .select('ledger_transactions.id as tr_number', 'ledger_transactions.transaction_type', 'ledger_transactions.transaction_date', 'companies.short_name',
            "(ledger_accounts.number::varchar || ' ' || ledger_company_accounts.name) as name", 'ledger_transactions.description', 'ledger_entries.company_amount',
            'coalesce(receipts.category,outgoing_payments.category) as type', 'coalesce(ledger_transactions.receipt_id,outgoing_payments.id) as document', 'ledger_entries.reconciled',
            'ledger_entries.bank_date', "('/ledger_transactions/' || ledger_transactions.id::varchar) as transaction_link",
            "coalesce(('/receipts/' || ledger_transactions.receipt_id::varchar),('/payments/' || outgoing_payments.id::varchar)) as document_link")
    .map { |r| r.attributes.symbolize_keys }.map(&:compact)
end

.result_report(options = {}) ⇒ Result

Returns bank vs GL reconciliation data.

Parameters:

  • options (Hash) (defaults to: {}) —

    report filter options

Options Hash (options):

  • years (Array<String>) —

    first element is the report year

  • months (Array<String>) —

    first element is the report month

  • company_ids (Array<String>) —

    first element is the company to report on

  • ledger_account_ids (Array<String>) —

    first element is the ledger account number to reconcile

Returns:

  • (Result) —

    bank vs GL reconciliation data



28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'app/services/report/reconciliation/reconciliation.rb', line 28

def self.result_report(options = {})
  open_items_amount = 0
   = LedgerAccount.where(number: options[:ledger_account_ids][0].to_i).select(:name).to_a[0]['name']
  start_date = Date.new(options[:years][0].to_i, options[:months][0].to_i, 1)
  end_date = Date.new(options[:years][0].to_i, options[:months][0].to_i, 1).end_of_month
  max_date = BankBalanceStatement.where(account_number: options[:ledger_account_ids][0].to_i).where(gl_date: start_date..end_date).maximum(:gl_date)
  bank_balance = BankBalanceStatement.where(account_number: options[:ledger_account_ids][0].to_i).where(gl_date: max_date).select('coalesce(sum(amount),0) as amount').to_a[0]['amount']
   = ViewFinancialAccountBalance.where(year: options[:years][0].to_i).where(month: ..options[:months][0].to_i).where(company_id: options[:company_ids][0].to_i)
                                                  .where(account_number: options[:ledger_account_ids][0].to_i)
                                                  .select('coalesce((sum(monthly_amount) + sum(beginning_balance_amount)),0) as balance').to_a[0]['balance']

  data = query_transactions(options[:company_ids][0].to_i, options[:ledger_account_ids][0].to_i, end_date)
  data.each do |r|
    open_items_amount += r[:company_amount]
  end

  Result.new(success: true,
             year: options[:years][0].to_i,
             month: options[:months][0].to_i,
             company_id: options[:company_ids][0].to_i,
             account_name: ,
             bank_balance: bank_balance,
             open_itmes_amount: open_items_amount,
             gl_account_balance: .round(2),
             data: data)
end