erp / modules / accounting / includes / functions / bank.php

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

488 lines 12.8 KB

Raw Download Zip

1	<?php
2
3	if ( ! defined( 'ABSPATH' ) ) {
4	exit; // Exit if accessed directly
5	}
6
7	/**
8	* Get all bank accounts
9	*
10	* @param $data
11	*
12	* @return mixed
13	*/
14	function erp_acct_get_banks( $show_balance = false, $with_cash = false, $no_bank = false ) {
15	global $wpdb;
16
17	$args = [];
18	$args['start_date'] = date( 'Y-m-d' );
19
20	$closest_fy_date = erp_acct_get_closest_fn_year_date( $args['start_date'] );
21	$args['start_date'] = $closest_fy_date['start_date'];
22	$args['end_date'] = $closest_fy_date['end_date'];
23
24	$ledgers = $wpdb->prefix . 'erp_acct_ledgers';
25	$show_all = false;
26	$cash_only = false;
27	$bank_only = false;
28
29	$chart_id = 7;
30	$cash_ledger = '';
31	$where = '';
32
33	if ( $with_cash && ! $no_bank ) {
34	$where = " WHERE chart_id = {$chart_id}";
35	$cash_ledger = " OR slug = 'cash' ";
36	$show_all = true;
37	}
38
39	if ( $with_cash && $no_bank ) {
40	$where = ' WHERE';
41	$cash_ledger = " slug = 'cash' ";
42	$cash_only = true;
43	}
44
45	if ( ! $with_cash && ! $no_bank ) {
46	$where = " WHERE chart_id = {$chart_id}";
47	$cash_ledger = '';
48	$bank_only = true;
49	}
50
51	if ( ! $show_balance ) {
52	$query = "SELECT * FROM $ledgers" . $where . $cash_ledger;
53	$results = $wpdb->get_results( $query, ARRAY_A );
54
55	return $results;
56	}
57
58	$sub_query = "SELECT id FROM $ledgers" . $where . $cash_ledger;
59	$ledger_details = $wpdb->prefix . 'erp_acct_ledger_details';
60	$query = "Select l.id, ld.ledger_id, l.code, l.name, SUM(ld.debit - ld.credit) as balance
61	From $ledger_details as ld
62	LEFT JOIN $ledgers as l ON l.id = ld.ledger_id
63	Where ld.ledger_id IN ($sub_query)
64	Group BY ld.ledger_id";
65
66	$temp_accts = $wpdb->get_results( $query, ARRAY_A );
67
68	if ( $with_cash ) {
69	// little hack to solve -> opening_balance cash entry with no ledger_details cash entry
70	$cash_ledger = '7';
71	$no_cash = true;
72
73	foreach ( $temp_accts as $temp_acct ) {
74	if ( $temp_acct['ledger_id'] === $cash_ledger ) {
75	$no_cash = false;
76	break;
77	}
78	}
79
80	if ( $no_cash ) {
81	$temp_accts[] = [ 'id' => 7 ];
82	}
83	}
84
85	$accts = [];
86	$bank_accts = [];
87	$uniq_accts = [];
88
89	$ledger_map = \WeDevs\ERP\Accounting\Includes\Classes\Ledger_Map::get_instance();
90	$ledger_id = $ledger_map->get_ledger_id_by_slug( 'cash' );
91
92	$c_balance = get_ledger_balance_with_opening_balance( $ledger_id, $args['start_date'], $args['end_date'] );
93	$balance = isset( $c_balance->balance ) ? $c_balance->balance : 0;
94
95	foreach ( $temp_accts as $temp_acct ) {
96	$bank_accts[] = get_ledger_balance_with_opening_balance( $temp_acct['id'], $args['start_date'], $args['end_date'] );
97	}
98
99	if ( $cash_only && ! empty( $accts ) ) {
100	return $accts;
101	}
102
103	$banks = erp_acct_get_ledgers_by_chart_id( 7 );
104
105	if ( $bank_only && empty( $banks ) ) {
106	return new WP_Error( 'rest_empty_accounts', __( 'Bank accounts are empty.' ), [ 'status' => 204 ] );
107	}
108
109	foreach ( $banks as $bank ) {
110	$bank_accts[] = get_ledger_balance_with_opening_balance( $bank['id'], $args['start_date'], $args['end_date'] );
111	}
112
113	$results = array_merge( $accts, $bank_accts );
114
115	foreach ( $results as $index => $result ) {
116	if ( ! empty( $uniq_accts ) && in_array( $result['id'], $uniq_accts, true ) ) {
117	unset( $results[ $index ] );
118	continue;
119	}
120	$uniq_accts[] = $result['id'];
121	}
122
123	return $results;
124	}
125
126	/**
127	* Get all accounts to show in dashboard
128	*
129	* @param $data
130	*
131	* @return mixed
132	*/
133	function erp_acct_get_dashboard_banks() {
134	$args = [];
135	$args['start_date'] = date( 'Y-m-d' );
136
137	$closest_fy_date = erp_acct_get_closest_fn_year_date( $args['start_date'] );
138	$args['start_date'] = $closest_fy_date['start_date'];
139	$args['end_date'] = $closest_fy_date['end_date'];
140
141	$results = [];
142
143	$ledger_map = \WeDevs\ERP\Accounting\Includes\Classes\Ledger_Map::get_instance();
144	$ledger_id = $ledger_map->get_ledger_id_by_slug( 'cash' );
145
146	$c_balance = get_ledger_balance_with_opening_balance( $ledger_id, $args['start_date'], $args['end_date'] );
147
148	$results[] = [
149	'name' => __( 'Cash', 'erp' ),
150	'balance' => isset( $c_balance['balance'] ) ? $c_balance['balance'] : 0,
151	];
152
153	$results[] = [
154	'name' => __( 'Cash at Bank', 'erp' ),
155	'balance' => erp_acct_cash_at_bank( $args, 'balance' ),
156	'additional' => erp_acct_bank_balance( $args, 'balance' ),
157	];
158
159	$results[] = [
160	'name' => __( 'Bank Loan', 'erp' ),
161	'balance' => erp_acct_cash_at_bank( $args, 'loan' ),
162	'additional' => erp_acct_bank_balance( $args, 'loan' ),
163	];
164
165	return $results;
166	}
167
168	/**
169	* Get a single bank account
170	*
171	* @param $bank_no
172	*
173	* @return mixed
174	*/
175	function erp_acct_get_bank( $bank_no ) {
176	global $wpdb;
177
178	$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}erp_acct_cash_at_banks WHERE ledger_id = %d", $bank_no ), ARRAY_A );
179
180	return $row;
181	}
182
183	/**
184	* Insert a bank account
185	*
186	* @param $data
187	* @param $bank_id
188	*
189	* @return int
190	*/
191	function erp_acct_insert_bank( $data ) {
192	global $wpdb;
193
194	$bank_data = erp_acct_get_formatted_bank_data( $data );
195
196	try {
197	$wpdb->query( 'START TRANSACTION' );
198
199	$wpdb->insert(
200	$wpdb->prefix . 'erp_acct_cash_at_banks',
201	[
202	'ledger_id' => $bank_data['ledger_id'],
203	]
204	);
205
206	$wpdb->query( 'COMMIT' );
207	} catch ( Exception $e ) {
208	$wpdb->query( 'ROLLBACK' );
209
210	return new WP_error( 'bank-account-exception', $e->getMessage() );
211	}
212
213	return $bank_data['ledger_id'];
214	}
215
216	/**
217	* Delete a bank account
218	*
219	* @param $id
220	*
221	* @return int
222	*/
223	function erp_acct_delete_bank( $id ) {
224	global $wpdb;
225
226	try {
227	$wpdb->query( 'START TRANSACTION' );
228	$wpdb->delete( $wpdb->prefix . 'erp_acct_cash_at_banks', [ 'ledger_id' => $id ] );
229	$wpdb->query( 'COMMIT' );
230	} catch ( Exception $e ) {
231	$wpdb->query( 'ROLLBACK' );
232
233	return new WP_error( 'bank-account-exception', $e->getMessage() );
234	}
235
236	return $id;
237	}
238
239	/**
240	* Get formatted bank data
241	*
242	* @param $data
243	* @param $voucher_no
244	*
245	* @return mixed
246	*/
247	function erp_acct_get_formatted_bank_data( $bank_data ) {
248	$bank_data['ledger_id'] = ! empty( $bank_data['ledger_id'] ) ? $bank_data['ledger_id'] : 0;
249
250	return $bank_data;
251	}
252
253	/**
254	* Get balance of a single account
255	*
256	* @param $ledger_id
257	*/
258	function erp_acct_get_single_account_balance( $ledger_id ) {
259	global $wpdb;
260
261	$result = $wpdb->get_row( $wpdb->prepare( "SELECT ledger_id, SUM(credit) - SUM(debit) AS 'balance' FROM {$wpdb->prefix}erp_acct_ledger_details WHERE ledger_id = %d", $ledger_id ), ARRAY_A );
262
263	return $result;
264	}
265
266	/**
267	* @param $ledger_id
268	*
269	* @return array
270	*/
271	function erp_acct_get_account_debit_credit( $ledger_id ) {
272	global $wpdb;
273	$dr_cr = [];
274
275	$dr_cr['debit'] = $wpdb->get_var( $wpdb->prepare( "SELECT SUM(debit) FROM {$wpdb->prefix}erp_acct_ledger_details WHERE ledger_id = %d", $ledger_id ) );
276	$dr_cr['credit'] = $wpdb->get_var( $wpdb->prepare( "SELECT SUM(credit) FROM {$wpdb->prefix}erp_acct_ledger_details WHERE ledger_id = %d", $ledger_id ) );
277
278	return $dr_cr;
279	}
280
281	/**
282	* Perform transfer amount between two account
283	*
284	* @param $item
285	*/
286	function erp_acct_perform_transfer( $item ) {
287	global $wpdb;
288	$created_by = get_current_user_id();
289	$created_at = date( 'Y-m-d' );
290	$updated_at = date( 'Y-m-d' );
291	$updated_by = $created_by;
292	$currency = erp_get_currency( true );
293
294	try {
295	$wpdb->query( 'START TRANSACTION' );
296
297	$wpdb->insert(
298	$wpdb->prefix . 'erp_acct_voucher_no',
299	[
300	'type' => 'transfer_voucher',
301	'currency' => $currency,
302	'created_at' => $created_at,
303	'created_by' => $created_by,
304	'updated_at' => $updated_at,
305	'updated_by' => $updated_by,
306	]
307	);
308
309	$voucher_no = $wpdb->insert_id;
310
311	// Inset transfer amount in ledger_details
312	$wpdb->insert(
313	$wpdb->prefix . 'erp_acct_ledger_details',
314	[
315	'ledger_id' => $item['from_account_id'],
316	'trn_no' => $voucher_no,
317	'particulars' => $item['particulars'],
318	'debit' => 0,
319	'credit' => $item['amount'],
320	'trn_date' => $item['date'],
321	'created_at' => $created_at,
322	'created_by' => $created_by,
323	'updated_at' => $updated_at,
324	'updated_by' => $updated_by,
325	]
326	);
327
328	$wpdb->insert(
329	$wpdb->prefix . 'erp_acct_ledger_details',
330	[
331	'ledger_id' => $item['to_account_id'],
332	'trn_no' => $voucher_no,
333	'particulars' => $item['particulars'],
334	'debit' => $item['amount'],
335	'credit' => 0,
336	'trn_date' => $item['date'],
337	'created_at' => $created_at,
338	'created_by' => $created_by,
339	'updated_at' => $updated_at,
340	'updated_by' => $updated_by,
341	]
342	);
343
344	$wpdb->insert(
345	$wpdb->prefix . 'erp_acct_transfer_voucher',
346	[
347	'voucher_no' => $voucher_no,
348	'amount' => $item['amount'],
349	'ac_from' => $item['from_account_id'],
350	'ac_to' => $item['to_account_id'],
351	'particulars' => $item['particulars'],
352	'trn_date' => $item['date'],
353	'created_at' => $created_at,
354	'created_by' => $created_by,
355	'updated_at' => $updated_at,
356	'updated_by' => $updated_by,
357	]
358	);
359
360	$wpdb->query( 'COMMIT' );
361	} catch ( Exception $e ) {
362	$wpdb->query( 'ROLLBACK' );
363
364	return new WP_error( 'transfer-exception', $e->getMessage() );
365	}
366	}
367
368	/**
369	* Sync dashboard account on transfer
370	*/
371	function erp_acct_sync_dashboard_accounts() {
372	global $wpdb;
373
374	$accounts = erp_acct_get_banks( true, true, false );
375
376	foreach ( $accounts as $account ) {
377	$wpdb->update(
378	$wpdb->prefix . 'erp_acct_cash_at_banks',
379	[
380	'balance' => $account['balance'],
381	],
382	[
383	'ledger_id' => $account['ledger_id'],
384	]
385	);
386	}
387	}
388
389	/**
390	* Get transferrable accounts
391	*/
392	function erp_acct_get_transfer_accounts( $show_balance = false ) {
393	$results = erp_acct_get_banks( true, true, false );
394
395	return $results;
396	}
397
398	/**
399	* Get created Transfer voucher list
400	*
401	* @param array $args
402	*
403	* @return array
404	*/
405	function erp_acct_get_transfer_vouchers( $args = [] ) {
406	global $wpdb;
407
408	$defaults = [
409	'number' => 20,
410	'offset' => 0,
411	'order_by' => 'id',
412	'order' => 'DESC',
413	'count' => false,
414	's' => '',
415	];
416
417	$args = wp_parse_args( $args, $defaults );
418
419	$limit = '';
420
421	if ( -1 !== $args['number'] ) {
422	$limit = "LIMIT {$args['number']} OFFSET {$args['offset']}";
423	}
424
425	$result = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}erp_acct_transfer_voucher ORDER BY %s %s %s", $args['order_by'], $args['order'], $limit ), ARRAY_A );
426
427	return $result;
428	}
429
430	/**
431	* Get single voucher
432	*
433	* @param int $id Voucher id
434	*
435	* @return object Single voucher
436	*/
437	function erp_acct_get_single_voucher( $id ) {
438	global $wpdb;
439
440	if ( ! $id ) {
441	return;
442	}
443
444	$result = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}erp_acct_transfer_voucher WHERE id = %d", $id ) );
445
446	return $result;
447	}
448
449	/**
450	* Get balance by Ledger ID
451	*
452	* @param $id array
453	*
454	* @return array
455	*/
456	function erp_acct_get_balance_by_ledger( $id ) {
457	if ( is_array( $id ) ) {
458	$id = "'" . implode( "','", $id ) . "'";
459	}
460
461	global $wpdb;
462	$table_name = $wpdb->prefix . 'erp_acct_ledger_details';
463	$query = "Select ld.ledger_id,SUM(ld.debit - ld.credit) as balance From $table_name as ld Where ld.ledger_id IN ($id) Group BY ld.ledger_id ";
464	$result = $wpdb->get_results( $query, ARRAY_A );
465
466	return $result;
467	}
468
469	/**
470	* Get bank accounts dropdown with cash
471	*
472	* @param $id array
473	*
474	* @return array
475	*/
476	function erp_acct_get_bank_dropdown() {
477	$accounts = [];
478	$banks = erp_acct_get_banks( true, true, false );
479
480	if ( $banks ) {
481	foreach ( $banks as $bank ) {
482	$accounts[ $bank['id'] ] = sprintf( '%s', $bank['name'] );
483	}
484	}
485
486	return $accounts;
487	}
488