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 



37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'app/services/report/reconciliation/reconciliation.rb', line 37

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 = {}) ⇒ Object 



10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
# File 'app/services/report/reconciliation/reconciliation.rb', line 10

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