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

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

339 lines 11.1 KB

Raw Download Zip

1	<?php
2
3	if ( ! defined( 'ABSPATH' ) ) {
4	exit; // Exit if accessed directly
5	}
6
7	/**
8	* Get all journals
9	*
10	* @return mixed
11	*/
12	function erp_acct_get_all_journals( $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 journal.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 journal.id ) as total_number';
41	} else {
42	$sql .= ' journal.*';
43	}
44
45	$sql .= " FROM {$wpdb->prefix}erp_acct_journals AS journal LEFT JOIN {$wpdb->prefix}erp_acct_journal_details AS journal_detail";
46	$sql .= " ON journal.voucher_no = journal_detail.trn_no {$where} GROUP BY journal.voucher_no ORDER BY journal.{$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 an single journal
59	*
60	* @param $journal_no
61	*
62	* @return mixed
63	*/
64	function erp_acct_get_journal( $journal_no ) {
65	global $wpdb;
66
67	$sql = "SELECT
68
69	journal.id,
70	journal.voucher_no,
71	journal.trn_date,
72	journal.ref,
73	journal.voucher_amount,
74	journal.attachments,
75	journal.particulars,
76	journal.created_at,
77	journal.created_by,
78	journal.updated_at,
79	journal.updated_by
80
81	FROM {$wpdb->prefix}erp_acct_journals as journal
82	LEFT JOIN {$wpdb->prefix}erp_acct_journal_details as journal_detail ON journal.voucher_no = journal_detail.trn_no
83	WHERE journal.voucher_no = {$journal_no} LIMIT 1";
84
85	$row = $wpdb->get_row( $sql, ARRAY_A );
86	$rows = $row;
87	$rows['line_items'] = erp_acct_format_journal_data( $row, $journal_no );
88
89	return $rows;
90	}
91
92	/**
93	* Insert journal data
94	*
95	* @param $data
96	*
97	* @return mixed
98	*/
99	function erp_acct_insert_journal( $data ) {
100	global $wpdb;
101
102	$created_by = get_current_user_id();
103	$data['created_at'] = date( 'Y-m-d H:i:s' );
104	$data['created_by'] = $created_by;
105
106	$voucher_no = null;
107	$currency = erp_get_currency( true );
108
109	try {
110	$wpdb->query( 'START TRANSACTION' );
111
112	$wpdb->insert(
113	$wpdb->prefix . 'erp_acct_voucher_no',
114	[
115	'type' => 'journal',
116	'currency' => $currency,
117	'created_at' => $data['created_at'],
118	'created_by' => $data['created_by'],
119	'updated_at' => isset( $data['updated_at'] ) ? $data['updated_at'] : '',
120	'updated_by' => isset( $data['updated_by'] ) ? $data['updated_by'] : '',
121	]
122	);
123
124	$voucher_no = $wpdb->insert_id;
125
126	$journal_data = erp_acct_get_formatted_journal_data( $data, $voucher_no );
127
128	$wpdb->insert(
129	$wpdb->prefix . 'erp_acct_journals',
130	[
131	'voucher_no' => $voucher_no,
132	'trn_date' => $journal_data['trn_date'],
133	'ref' => $journal_data['ref'],
134	'voucher_amount' => $journal_data['voucher_amount'],
135	'particulars' => $journal_data['particulars'],
136	'attachments' => $journal_data['attachments'],
137	'created_at' => $journal_data['created_at'],
138	'created_by' => $journal_data['created_by'],
139	'updated_at' => $journal_data['updated_at'],
140	'updated_by' => $journal_data['updated_by'],
141	]
142	);
143
144	$items = $journal_data['line_items'];
145
146	foreach ( $items as $key => $item ) {
147	$wpdb->insert(
148	$wpdb->prefix . 'erp_acct_journal_details',
149	[
150	'trn_no' => $voucher_no,
151	'ledger_id' => $item['ledger_id'],
152	'particulars' => empty( $item['particulars'] ) ? $journal_data['particulars'] : $item['particulars'],
153	'debit' => $item['debit'],
154	'credit' => $item['credit'],
155	'created_at' => $journal_data['created_at'],
156	'created_by' => $journal_data['created_by'],
157	'updated_at' => $journal_data['updated_at'],
158	'updated_by' => $journal_data['updated_by'],
159	]
160	);
161
162	$wpdb->insert(
163	$wpdb->prefix . 'erp_acct_ledger_details',
164	[
165	'ledger_id' => $item['ledger_id'],
166	'trn_no' => $voucher_no,
167	'particulars' => empty( $item['particulars'] ) ? $journal_data['particulars'] : $item['particulars'],
168	'debit' => $item['debit'],
169	'credit' => $item['credit'],
170	'trn_date' => $journal_data['trn_date'],
171	'created_at' => $journal_data['created_at'],
172	'created_by' => $journal_data['created_by'],
173	'updated_at' => $journal_data['updated_at'],
174	'updated_by' => $journal_data['updated_by'],
175	]
176	);
177	}
178
179	$wpdb->query( 'COMMIT' );
180	} catch ( Exception $e ) {
181	$wpdb->query( 'ROLLBACK' );
182
183	return new WP_error( 'journal-exception', $e->getMessage() );
184	}
185
186	return erp_acct_get_journal( $voucher_no );
187	}
188
189	/**
190	* Update journal data
191	*
192	* @param $data
193	*
194	* @return int
195	*/
196	function erp_acct_update_journal( $data, $journal_no ) {
197	global $wpdb;
198
199	$updated_by = get_current_user_id();
200	$data['updated_at'] = date( 'Y-m-d H:i:s' );
201	$data['updated_by'] = $updated_by;
202
203	try {
204	$wpdb->query( 'START TRANSACTION' );
205
206	$journal_data = erp_acct_get_formatted_journal_data( $data, $journal_no );
207
208	$wpdb->update(
209	$wpdb->prefix . 'erp_acct_journals',
210	[
211	'trn_date' => $journal_data['trn_date'],
212	'ref' => $journal_data['ref'],
213	'voucher_amount' => $journal_data['voucher_amount'],
214	'particulars' => $journal_data['particulars'],
215	'attachments' => $journal_data['attachments'],
216	'created_at' => $journal_data['created_at'],
217	'created_by' => $journal_data['created_by'],
218	'updated_at' => $journal_data['updated_at'],
219	'updated_by' => $journal_data['updated_by'],
220	],
221	[
222	'voucher_no' => $journal_no,
223	]
224	);
225
226	$items = $journal_data['line_items'];
227
228	foreach ( $items as $key => $item ) {
229	$wpdb->update(
230	$wpdb->prefix . 'erp_acct_journal_details',
231	[
232	'ledger_id' => $item['ledger_id'],
233	'particulars' => empty( $item['particulars'] ) ? $journal_data['particulars'] : $item['particulars'],
234	'debit' => $item['debit'],
235	'credit' => $item['credit'],
236	'created_at' => $journal_data['created_at'],
237	'created_by' => $journal_data['created_by'],
238	'updated_at' => $journal_data['updated_at'],
239	'updated_by' => $journal_data['updated_by'],
240	],
241	[
242	'trn_no' => $journal_no,
243	]
244	);
245
246	$wpdb->update(
247	$wpdb->prefix . 'erp_acct_ledger_details',
248	[
249	'ledger_id' => $item['ledger_id'],
250	'particulars' => empty( $item['particulars'] ) ? $journal_data['particulars'] : $item['particulars'],
251	'debit' => $item['debit'],
252	'credit' => $item['credit'],
253	'trn_date' => $journal_data['trn_date'],
254	'created_at' => $journal_data['created_at'],
255	'created_by' => $journal_data['created_by'],
256	'updated_at' => $journal_data['updated_at'],
257	'updated_by' => $journal_data['updated_by'],
258	],
259	[
260	'trn_no' => $journal_no,
261	]
262	);
263	}
264
265	$wpdb->query( 'COMMIT' );
266	} catch ( Exception $e ) {
267	$wpdb->query( 'ROLLBACK' );
268
269	return new WP_error( 'journal-exception', $e->getMessage() );
270	}
271
272	return erp_acct_get_journal( $journal_no );
273	}
274
275	/**
276	* Get formatted journal data
277	*
278	* @param $data
279	* @param $voucher_no
280	*
281	* @return mixed
282	*/
283	function erp_acct_get_formatted_journal_data( $data, $voucher_no ) {
284	$journal_data = [];
285
286	$journal_data['voucher_no'] = ! empty( $voucher_no ) ? $voucher_no : 0;
287	$journal_data['trn_date'] = isset( $data['date'] ) ? $data['date'] : date( 'Y-m-d' );
288	$journal_data['voucher_amount'] = isset( $data['voucher_amount'] ) ? $data['voucher_amount'] : 0;
289	$journal_data['line_items'] = isset( $data['line_items'] ) ? $data['line_items'] : [];
290	// translators: %s: voucher_no
291	$journal_data['particulars'] = ! empty( $data['particulars'] ) ? $data['particulars'] : sprintf( __( 'Journal created with voucher no %s', 'erp' ), $voucher_no );
292	$journal_data['ref'] = isset( $data['ref'] ) ? $data['ref'] : '';
293	$journal_data['attachments'] = isset( $data['attachments'] ) ? $data['attachments'] : '';
294	$journal_data['created_at'] = isset( $data['created_at'] ) ? $data['created_at'] : '';
295	$journal_data['created_by'] = isset( $data['created_by'] ) ? $data['created_by'] : '';
296	$journal_data['updated_at'] = isset( $data['updated_at'] ) ? $data['updated_at'] : '';
297	$journal_data['updated_by'] = isset( $data['updated_by'] ) ? $data['updated_by'] : '';
298
299	return $journal_data;
300	}
301
302	/**
303	* Format journal data
304	*
305	* @param $data
306	* @param $voucher_no
307	*
308	* @return mixed
309	*/
310	function erp_acct_format_journal_data( $item, $journal_no ) {
311	global $wpdb;
312
313	$sql = "SELECT
314	journal.id,
315
316	journal_detail.trn_no,
317	journal_detail.ledger_id,
318	journal_detail.particulars,
319	journal_detail.debit,
320	journal_detail.credit
321
322	FROM {$wpdb->prefix}erp_acct_journals as journal
323	LEFT JOIN {$wpdb->prefix}erp_acct_journal_details as journal_detail ON journal.voucher_no = journal_detail.trn_no
324	WHERE journal.voucher_no = {$journal_no}";
325
326	$rows = $wpdb->get_results( $sql, ARRAY_A );
327	$line_items = [];
328
329	foreach ( $rows as $key => $item ) {
330	$line_items[ $key ]['ledger_id'] = $item['ledger_id'];
331	$line_items[ $key ]['account'] = erp_acct_get_ledger_name_by_id( $item['ledger_id'] );
332	$line_items[ $key ]['particulars'] = $item['particulars'];
333	$line_items[ $key ]['debit'] = $item['debit'];
334	$line_items[ $key ]['credit'] = $item['credit'];
335	}
336
337	return $line_items;
338	}
339