Class: Report::Reconciliation::Reconciliation

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

Defined Under Namespace

Classes: Result

Class Method Summary collapse

Class Method Details

.query_transactions(company_id, account_number, date) ⇒ Object 



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 35

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("ledger_entries.bank_date > ? or ledger_entries.reconciled = false",date).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{ |a| a.compact }
end

.result_report(options = {}) ⇒ Object 



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

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