erp / modules / accounting / includes / classes / FinalAccountsHelper.php

FinalAccountsHelper.php in ERP: Complete HR, Accounting & CRM Suite Built for WooCommerce 1.6.7, at modules/accounting/includes/classes/FinalAccountsHelper.php

140 lines 4.8 KB

Raw Download Zip

1	<?php
2
3
4	namespace WeDevs\ERP\Accounting\Includes\Classes;
5
6
7	class FinalAccountsHelper
8	{
9	private $db;
10	public $totalCashAtBank = 0;
11	public $totalLoanAtBank = 0;
12	public $cashAtBankBreakdowns = [];
13	public $loanAtBankBreakdowns = [];
14	private $args;
15	public $financialYear;
16
17	public function __construct( $args ) {
18	global $wpdb;
19	$this->db = $wpdb;
20	$this->args = $args;
21
22	$this->getClosestFinYear();
23	$this->setBankData();
24
25	}
26
27	private function getPreviousBalance($chart_id){
28
29	$sql = "SELECT SUM( ld.debit - ld.credit ) AS balance,ledger.name,ld.ledger_id,ledger.chart_id from {$this->db->prefix}erp_acct_ledger_details as ld
30	INNER JOIN {$this->db->prefix}erp_acct_ledgers as ledger ON ledger.id = ld.ledger_id
31	WHERE ledger.chart_id = %d AND ld.trn_date >= '%s' AND ld.trn_date < '%s'
32	GROUP BY ld.ledger_id";
33
34	return $this->db->get_results( $this->db->prepare( $sql, $chart_id, $this->financialYear['start_date'], $this->args['start_date'] ), ARRAY_A );
35
36	}
37
38	/**
39	* get opening balance of ledger
40	* @param $chart_id
41	* @return array\|object\|null
42	*/
43	private function getOpeningBalances( $chart_id ) {
44
45	$sql = "SELECT ob.ledger_id, SUM(ob.debit - ob.credit) as balance, ledger.name, ob.chart_id FROM {$this->db->prefix}erp_acct_opening_balances as ob
46	INNER JOIN {$this->db->prefix}erp_acct_ledgers as ledger ON ledger.id = ob.ledger_id
47	WHERE ob.financial_year_id = %d AND ob.chart_id = %d GROUP BY ob.ledger_id";
48
49	return $this->db->get_results( $this->db->prepare( $sql, $this->financialYear['id'], $chart_id ), ARRAY_A );
50	}
51
52	/**
53	* get balance from ledger_details of ledger
54	* @param $chart_id
55	* @return array\|object\|null
56	*/
57	private function getLedgerDetails( $chart_id ) {
58
59	$sql = "SELECT SUM( ld.debit - ld.credit ) AS balance,ledger.name,ld.ledger_id,ledger.chart_id from {$this->db->prefix}erp_acct_ledger_details as ld
60	INNER JOIN {$this->db->prefix}erp_acct_ledgers as ledger ON ledger.id = ld.ledger_id
61	WHERE ledger.chart_id = %d AND ld.trn_date BETWEEN '%s' AND '%s'
62	GROUP BY ld.ledger_id";
63
64	return $this->db->get_results( $this->db->prepare( $sql, $chart_id, $this->args['start_date'], $this->args['end_date'] ), ARRAY_A );
65	}
66
67
68	/**
69	* generate totalCashAtBank, totalLoanAtBank, cashAtBankBreakdowns, loanAtBankBreakdowns
70	*/
71	private function setBankData() {
72
73	$openingBalances = $this->getOpeningBalances( 7 );
74
75	$bankLedgerData = [];
76
77	// format opening balance data by setting ledger_id as index
78	foreach ( $openingBalances as $item ) {
79	$bankLedgerData[ $item['ledger_id'] ] = $item;
80	}
81
82	$ledgerDetails = $this->getLedgerDetails( 7 );
83
84	// format ledger details data by setting ledger_id as index
85	//and merge ledger data according to ledger id with summation
86	foreach ( $ledgerDetails as $ledger ) {
87
88	if ( isset( $bankLedgerData[ $ledger['ledger_id'] ] ) ) {
89	$bankLedgerData[ $ledger['ledger_id'] ]['balance'] += $ledger['balance'];
90	} else {
91	$bankLedgerData[ $ledger['ledger_id'] ] = $ledger;
92	}
93	}
94
95	$previousLedgerBalance = $this->getPreviousBalance( 7 );
96
97	// format ledger details data by setting ledger_id as index
98	//and merge ledger data according to ledger id with summation
99	foreach ( $previousLedgerBalance as $ledger ) {
100
101	if ( isset( $bankLedgerData[ $ledger['ledger_id'] ] ) ) {
102	$bankLedgerData[ $ledger['ledger_id'] ]['balance'] += $ledger['balance'];
103	} else {
104	$bankLedgerData[ $ledger['ledger_id'] ] = $ledger;
105	}
106	}
107
108
109	foreach ( $bankLedgerData as $ledger ) {
110
111
112	if ( (float)$ledger['balance'] > 0 ) {
113
114	$this->totalCashAtBank += (float)$ledger['balance'];
115	$this->cashAtBankBreakdowns[ $ledger['ledger_id'] ] = $ledger;
116
117	}
118
119	if ( (float)$ledger['balance'] < 0 ) {
120
121	$this->totalLoanAtBank += (float)$ledger['balance'];
122	$this->loanAtBankBreakdowns[ $ledger['ledger_id'] ] = $ledger;
123
124	}
125	}
126
127	}
128
129	/**
130	* get closest financial year by start date
131	*/
132	private function getClosestFinYear() {
133
134	$sql = "SELECT id, name, start_date, end_date FROM {$this->db->prefix}erp_acct_financial_years WHERE start_date <= '%s' ORDER BY start_date DESC LIMIT 1";
135
136	$this->financialYear = $this->db->get_row( $this->db->prepare( $sql, $this->args['start_date'] ), ARRAY_A );
137	}
138
139	}
140