erp / modules / accounting / includes / functions / opening-balances.php

opening-balances.php in ERP: Complete HR, Accounting & CRM Suite Built for WooCommerce 1.6.7, at modules/accounting/includes/functions/opening-balances.php

477 lines 15.6 KB

Raw Download Zip

1	<?php
2
3	if ( ! defined( 'ABSPATH' ) ) {
4	exit; // Exit if accessed directly
5	}
6
7	/**
8	* Get all opening_balances
9	*
10	* @return mixed
11	*/
12	function erp_acct_get_all_opening_balances( $args = [] ) {
13	global $wpdb;
14
15	$defaults = [
16	'number' => 20,
17	'offset' => 0,
18	'orderby' => 'id',
19	'order' => 'DESC',
20	'count' => false,
21	's' => '',
22	];
23
24	$args = wp_parse_args( $args, $defaults );
25
26	$where = '';
27	$limit = '';
28
29	if ( ! empty( $args['start_date'] ) ) {
30	$where .= "WHERE opening_balance.trn_date BETWEEN '{$args['start_date']}' AND '{$args['end_date']}'";
31	}
32
33	if ( '-1' === $args['number'] ) {
34	$limit = "LIMIT {$args['number']} OFFSET {$args['offset']}";
35	}
36
37	$sql = 'SELECT';
38
39	if ( $args['count'] ) {
40	$sql .= ' COUNT( DISTINCT opening_balance.id ) as total_number';
41	} else {
42	$sql .= ' *';
43	}
44
45	$sql .= " FROM {$wpdb->prefix}erp_acct_opening_balances AS opening_balance LEFT JOIN {$wpdb->prefix}erp_acct_financial_years AS financial_year";
46	$sql .= " ON opening_balance.financial_year_id = financial_year.id {$where} GROUP BY financial_year.name ORDER BY financial_year.{$args['orderby']} {$args['order']} {$limit}";
47
48	if ( $args['count'] ) {
49	$wpdb->get_results( $sql );
50
51	return $wpdb->num_rows;
52	}
53
54	return $wpdb->get_results( $sql, ARRAY_A );
55	}
56
57	/**
58	* Get opening_balances of a year
59	*
60	* @param $year_id
61	*
62	* @return mixed
63	*/
64	function erp_acct_get_opening_balance( $year_id ) {
65	global $wpdb;
66
67	$rows = $wpdb->get_results(
68	$wpdb->prepare(
69	"SELECT ob.id, ob.financial_year_id, ob.ledger_id, ledger.name, ob.chart_id, ob.debit, ob.credit FROM {$wpdb->prefix}erp_acct_opening_balances as ob LEFT JOIN {$wpdb->prefix}erp_acct_ledgers as ledger ON ledger.id = ob.ledger_id WHERE financial_year_id = %d AND ob.type = 'ledger'",
70	$year_id
71	),
72	ARRAY_A
73	);
74
75	return $rows;
76	}
77
78	/**
79	* Get virtual accounts of a year
80	*
81	* @param $year_id
82	*
83	* @return mixed
84	*/
85	function erp_acct_get_virtual_acct( $year_id ) {
86	global $wpdb;
87
88	$rows = $wpdb->get_results(
89	$wpdb->prepare(
90	"SELECT ob.id, ob.financial_year_id, ob.ledger_id, ob.type, ob.debit, ob.credit
91	FROM {$wpdb->prefix}erp_acct_opening_balances as ob WHERE financial_year_id = %d AND ob.type <> 'ledger'",
92	$year_id
93	),
94	ARRAY_A
95	);
96
97	$rows = erp_acct_get_ob_virtual_acct( $year_id );
98
99	return $rows;
100	}
101
102	/**
103	* Insert opening_balance data
104	*
105	* @param $data
106	*
107	* @return mixed
108	*/
109	function erp_acct_insert_opening_balance( $data ) {
110	global $wpdb;
111
112	$created_by = get_current_user_id();
113	$data['created_at'] = date( 'Y-m-d H:i:s' );
114	$data['created_by'] = $created_by;
115
116	try {
117	$wpdb->query( 'START TRANSACTION' );
118
119	$opening_balance_data = erp_acct_get_formatted_opening_balance_data( $data );
120
121	$items = $opening_balance_data['ledgers'];
122
123	$ledgers = [];
124
125	foreach ( $items as $item ) {
126	$ledgers = array_merge( $ledgers, $item );
127	}
128
129	$year_id = $opening_balance_data['year'];
130
131	$wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}erp_acct_opening_balances WHERE financial_year_id = %d", $year_id ) );
132
133	foreach ( $ledgers as $ledger ) {
134	if ( ( isset( $ledger['debit'] ) && (float) $ledger['debit'] > 0 ) \|\| ( isset( $ledger['credit'] ) && (float) $ledger['credit'] > 0 ) ) {
135	$wpdb->insert(
136	$wpdb->prefix . 'erp_acct_opening_balances',
137	[
138	'financial_year_id' => $year_id,
139	'ledger_id' => $ledger['ledger_id'],
140	'chart_id' => $ledger['chart_id'],
141	'type' => 'ledger',
142	'debit' => isset( $ledger['debit'] ) ? $ledger['debit'] : 0,
143	'credit' => isset( $ledger['credit'] ) ? $ledger['credit'] : 0,
144	'created_at' => $opening_balance_data['created_at'],
145	'created_by' => $opening_balance_data['created_by'],
146	'updated_at' => $opening_balance_data['updated_at'],
147	'updated_by' => $opening_balance_data['updated_by'],
148	]
149	);
150	}
151	}
152
153	erp_acct_insert_ob_vir_accounts( $opening_balance_data, $year_id );
154
155	$wpdb->query( 'COMMIT' );
156	} catch ( Exception $e ) {
157	$wpdb->query( 'ROLLBACK' );
158
159	return new WP_error( 'opening_balance-exception', $e->getMessage() );
160	}
161
162	return erp_acct_get_opening_balance( $year_id );
163	}
164
165	/**
166	* Insert virtual accounts data
167	*
168	* @param $data
169	* @param $year_id
170	*/
171	function erp_acct_insert_ob_vir_accounts( $data, $year_id ) {
172	global $wpdb;
173
174	if ( ! empty( $data['acct_rec'] ) ) {
175	foreach ( $data['acct_rec'] as $acct_rec ) {
176	$wpdb->insert(
177	$wpdb->prefix . 'erp_acct_opening_balances',
178	[
179	'financial_year_id' => $year_id,
180	'ledger_id' => $acct_rec['people']['id'],
181	'type' => 'people',
182	'debit' => $acct_rec['debit'],
183	'credit' => 0,
184	'created_at' => $data['created_at'],
185	'created_by' => $data['created_by'],
186	'updated_at' => $data['updated_at'],
187	'updated_by' => $data['updated_by'],
188	]
189	);
190	}
191	}
192
193	if ( ! empty( $data['acct_pay'] ) ) {
194	foreach ( $data['acct_pay'] as $acct_pay ) {
195	$wpdb->insert(
196	$wpdb->prefix . 'erp_acct_opening_balances',
197	[
198	'financial_year_id' => $year_id,
199	'ledger_id' => $acct_pay['people']['id'],
200	'type' => 'people',
201	'debit' => 0,
202	'credit' => $acct_pay['credit'],
203	'created_at' => $data['created_at'],
204	'created_by' => $data['created_by'],
205	'updated_at' => $data['updated_at'],
206	'updated_by' => $data['updated_by'],
207	]
208	);
209	}
210	}
211
212	if ( ! empty( $data['tax_pay'] ) ) {
213	foreach ( $data['tax_pay'] as $tax_pay ) {
214	$wpdb->insert(
215	$wpdb->prefix . 'erp_acct_opening_balances',
216	[
217	'financial_year_id' => $year_id,
218	'ledger_id' => $tax_pay['agency']['id'],
219	'type' => 'tax_agency',
220	'debit' => 0,
221	'credit' => $tax_pay['credit'],
222	'created_at' => $data['created_at'],
223	'created_by' => $data['created_by'],
224	'updated_at' => $data['updated_at'],
225	'updated_by' => $data['updated_by'],
226	]
227	);
228	}
229	}
230	}
231
232	/**
233	* Get formatted opening_balance data
234	*
235	* @param $data
236	* @param $voucher_no
237	*
238	* @return mixed
239	*/
240	function erp_acct_get_formatted_opening_balance_data( $data ) {
241	$opening_balance_data = [];
242
243	$opening_balance_data['year'] = isset( $data['year'] ) ? $data['year'] : '';
244	$opening_balance_data['ledgers'] = isset( $data['ledgers'] ) ? $data['ledgers'] : [];
245	$opening_balance_data['descriptions'] = isset( $data['descriptions'] ) ? $data['descriptions'] : '';
246	$opening_balance_data['amount'] = isset( $data['amount'] ) ? $data['amount'] : '';
247	$opening_balance_data['acct_pay'] = isset( $data['acct_pay'] ) ? $data['acct_pay'] : [];
248	$opening_balance_data['acct_rec'] = isset( $data['acct_rec'] ) ? $data['acct_rec'] : [];
249	$opening_balance_data['tax_pay'] = isset( $data['tax_pay'] ) ? $data['tax_pay'] : [];
250	$opening_balance_data['created_at'] = isset( $data['created_at'] ) ? $data['created_at'] : '';
251	$opening_balance_data['created_by'] = isset( $data['created_by'] ) ? $data['created_by'] : '';
252	$opening_balance_data['updated_at'] = isset( $data['updated_at'] ) ? $data['updated_at'] : '';
253	$opening_balance_data['updated_by'] = isset( $data['updated_by'] ) ? $data['updated_by'] : '';
254
255	return $opening_balance_data;
256	}
257
258	/**
259	* Get opening balance names
260	*
261	* @return array
262	*/
263	function erp_acct_get_opening_balance_names() {
264	global $wpdb;
265
266	$rows = $wpdb->get_results( "SELECT id, name, start_date, end_date FROM {$wpdb->prefix}erp_acct_financial_years", ARRAY_A );
267
268	return $rows;
269	}
270
271	/**
272	* Get opening balance date ranges
273	*
274	* @param $ob_name
275	*
276	* @return array
277	*/
278	function erp_acct_get_start_end_date( $year_id ) {
279	$dates = [];
280	global $wpdb;
281
282	$rows = $wpdb->get_row( $wpdb->prepare( "SELECT start_date, end_date FROM {$wpdb->prefix}erp_acct_financial_years WHERE id = %d", $year_id ), ARRAY_A );
283
284	$dates['start'] = $rows['start_date'];
285	$dates['end'] = $rows['end_date'];
286
287	return $dates;
288	}
289
290	/**
291	* Get virtual accts summary for opening balance
292	*/
293	function erp_acct_get_ob_virtual_acct( $year_id ) {
294	global $wpdb;
295
296	$vir_ac['acct_receivable'] = $wpdb->get_results( $wpdb->prepare( "SELECT ledger_id as people_id, debit, credit from {$wpdb->prefix}erp_acct_opening_balances where financial_year_id = %d and credit=0 and type='people'", $year_id ), ARRAY_A );
297
298	$vir_ac['acct_payable'] = $wpdb->get_results( $wpdb->prepare( "SELECT ledger_id as people_id, debit, credit from {$wpdb->prefix}erp_acct_opening_balances where financial_year_id = %d and debit=0 and type='people'", $year_id ), ARRAY_A );
299
300	$vir_ac['tax_payable'] = $wpdb->get_results( $wpdb->prepare( "SELECT ledger_id as agency_id, debit, credit from {$wpdb->prefix}erp_acct_opening_balances where financial_year_id = %d and debit=0 and type='tax_agency'", $year_id ), ARRAY_A );
301
302	for ( $i = 0; $i < count( $vir_ac['acct_payable'] ); $i++ ) {
303	if ( empty( $vir_ac['acct_payable'][ $i ]['people_id'] ) ) {
304	return;
305	}
306
307	$vir_ac['acct_payable'][ $i ]['people']['id'] = $vir_ac['acct_payable'][ $i ]['people_id'];
308	$vir_ac['acct_payable'][ $i ]['people']['name'] = erp_acct_get_people_name_by_people_id( $vir_ac['acct_payable'][ $i ]['people_id'] );
309	}
310
311	for ( $i = 0; $i < count( $vir_ac['acct_receivable'] ); $i++ ) {
312	if ( empty( $vir_ac['acct_receivable'][ $i ]['people_id'] ) ) {
313	return;
314	}
315
316	$vir_ac['acct_receivable'][ $i ]['people']['id'] = $vir_ac['acct_receivable'][ $i ]['people_id'];
317	$vir_ac['acct_receivable'][ $i ]['people']['name'] = erp_acct_get_people_name_by_people_id( $vir_ac['acct_receivable'][ $i ]['people_id'] );
318	}
319
320	for ( $i = 0; $i < count( $vir_ac['tax_payable'] ); $i++ ) {
321	if ( empty( $vir_ac['tax_payable'][ $i ]['agency_id'] ) ) {
322	return;
323	}
324
325	$vir_ac['tax_payable'][ $i ]['agency']['id'] = $vir_ac['tax_payable'][ $i ]['agency_id'];
326	$vir_ac['tax_payable'][ $i ]['agency']['name'] = erp_acct_get_tax_agency_name_by_id( $vir_ac['tax_payable'][ $i ]['agency_id'] );
327	}
328
329	return $vir_ac;
330	}
331
332	/**
333	* Get balance with opening balance of a ledger
334	*
335	* @param $ledger_id
336	* @param array $args
337	*
338	* @return mixed
339	*/
340	function get_ledger_balance_with_opening_balance( $ledger_id, $start_date, $end_date ) {
341	global $wpdb;
342
343	// get closest financial year id and start date
344	$closest_fy_date = erp_acct_get_closest_fn_year_date( $start_date );
345
346	// get opening balance data within that(^) financial year
347	$opening_balance = (float) erp_acct_ledger_report_opening_balance_by_fn_year_id( $closest_fy_date['id'], $ledger_id );
348
349	// should we go further calculation, check the diff
350	if ( erp_acct_has_date_diff( $start_date, $closest_fy_date['start_date'] ) ) {
351	$prev_date_of_start = date( 'Y-m-d', strtotime( '-1 day', strtotime( $start_date ) ) );
352
353	$sql1 = $wpdb->prepare(
354	"SELECT SUM(debit - credit) AS balance
355	FROM {$wpdb->prefix}erp_acct_ledger_details
356	WHERE ledger_id = %d AND trn_date BETWEEN '%s' AND '%s'",
357	$ledger_id,
358	$closest_fy_date['start_date'],
359	$prev_date_of_start
360	);
361
362	$prev_ledger_details = $wpdb->get_var( $sql1 );
363	$opening_balance += (float) $prev_ledger_details;
364	}
365
366	// ledger details
367	$sql2 = $wpdb->prepare(
368	"SELECT
369	SUM(debit-credit) as balance
370	FROM {$wpdb->prefix}erp_acct_ledger_details
371	WHERE ledger_id = %d AND trn_date BETWEEN '%s' AND '%s'",
372	$ledger_id,
373	$start_date,
374	$end_date
375	);
376
377	$res = $wpdb->get_row( $sql2, ARRAY_A );
378
379	$total_debit = 0;
380	$total_credit = 0;
381	$final_balance = 0;
382
383	$final_balance = $opening_balance + $res['balance'];
384
385	$l_data = erp_acct_get_ledger_by_id( $ledger_id );
386
387	if ( empty( $l_data ) ) {
388	return [];
389	}
390
391	return [
392	'id' => $ledger_id,
393	'name' => $l_data->name,
394	'code' => $l_data->code,
395	'obalance' => $opening_balance,
396	'balance' => $final_balance,
397	'total_debit' => $total_debit,
398	'total_credit' => $total_credit,
399	];
400	}
401
402	/**
403	* Get opening balance invoice account details
404	*
405	* @param string $fy_start_date
406	*
407	* @return int
408	*/
409	function erp_acct_get_opb_invoice_account_details( $fy_start_date ) {
410	global $wpdb;
411
412	// mainly ( debit - credit )
413	$sql = "SELECT SUM(balance) AS amount
414	FROM ( SELECT SUM( debit - credit ) AS balance
415	FROM {$wpdb->prefix}erp_acct_invoice_account_details WHERE trn_date < '%s'
416	GROUP BY invoice_no HAVING balance > 0 )
417	AS get_amount";
418
419	return (float) $wpdb->get_var( $wpdb->prepare( $sql, $fy_start_date ) );
420	}
421
422	/**
423	* Get opening balance bill & purchase
424	*
425	* @param string $fy_start_date
426	*
427	* @return int
428	*/
429	function erp_acct_get_opb_bill_purchase_account_details( $fy_start_date ) {
430	global $wpdb;
431
432	/**
433	*? Why only bills, not expense?
434	*? Expense is `direct expense`, and we don't include direct expense here
435	*/
436	$bill_sql = "SELECT SUM(balance) AS amount
437	FROM ( SELECT SUM( debit - credit ) AS balance FROM {$wpdb->prefix}erp_acct_bill_account_details WHERE trn_date < '%s'
438	GROUP BY bill_no HAVING balance < 0 )
439	AS get_amount";
440
441	$purchase_sql = "SELECT SUM(balance) AS amount
442	FROM ( SELECT SUM( debit - credit ) AS balance FROM {$wpdb->prefix}erp_acct_purchase_account_details WHERE trn_date < '%s'
443	GROUP BY purchase_no HAVING balance < 0 )
444	AS get_amount";
445
446	$bill_amount = $wpdb->get_var( $wpdb->prepare( $bill_sql, $fy_start_date ) );
447	$purchase_amount = $wpdb->get_var( $wpdb->prepare( $purchase_sql, $fy_start_date ) );
448
449	return abs( (float) $bill_amount + (float) $purchase_amount );
450	}
451
452	/**
453	*Get lower and upper bound of financial years
454	*/
455	function erp_acct_get_date_boundary() {
456	global $wpdb;
457
458	$result = $wpdb->get_row( "SELECT MIN(start_date) as lower, MAX(end_date) as upper FROM {$wpdb->prefix}erp_acct_financial_years", ARRAY_A );
459
460	return $result;
461	}
462
463	/**
464	* Get current financial year
465	*/
466	function erp_acct_get_current_financial_year( $date = '' ) {
467	global $wpdb;
468
469	if ( empty( $date ) ) {
470	$date = date( 'Y-m-d' );
471	}
472
473	$result = $wpdb->get_row( $wpdb->prepare( "SELECT id,name,start_date,end_date FROM {$wpdb->prefix}erp_acct_financial_years WHERE '%s' between start_date AND end_date", $date ) );
474
475	return $result;
476	}
477