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

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

755 lines 25.7 KB

Raw Download Zip

1	<?php
2
3	if ( ! defined( 'ABSPATH' ) ) {
4	exit; // Exit if accessed directly
5	}
6
7	/**
8	* Get all purchases
9	*
10	* @param array $args
11	*
12	* @return mixed
13	*/
14	function erp_acct_get_purchases( $args = [] ) {
15	global $wpdb;
16
17	$defaults = [
18	'number' => 20,
19	'offset' => 0,
20	'orderby' => 'id',
21	'order' => 'DESC',
22	'count' => false,
23	's' => '',
24	];
25
26	$args = wp_parse_args( $args, $defaults );
27
28	$limit = '';
29
30	if ( -1 !== $args['number'] ) {
31	$limit = "LIMIT {$args['number']} OFFSET {$args['offset']}";
32	}
33
34	$sql = 'SELECT';
35	$sql .= $args['count'] ? ' COUNT( id ) as total_number ' : ' * ';
36	$sql .= "FROM {$wpdb->prefix}erp_acct_purchase ORDER BY {$args['orderby']} {$args['order']} {$limit}";
37
38	if ( $args['count'] ) {
39	return $wpdb->get_var( $sql );
40	}
41
42	return $wpdb->get_results( $sql, ARRAY_A );
43	}
44
45	/**
46	* Get a purchase
47	*
48	* @param $purchase_no
49	*
50	* @return mixed
51	*/
52	function erp_acct_get_purchase( $purchase_no ) {
53	global $wpdb;
54
55	$sql = $wpdb->prepare(
56	"SELECT
57
58	voucher.editable,
59	purchase.id,
60	purchase.voucher_no,
61	purchase.vendor_id,
62	purchase.trn_date,
63	purchase.due_date,
64	purchase.amount,
65	purchase.vendor_name,
66	purchase.ref,
67	purchase.status,
68	purchase.purchase_order,
69	purchase.attachments,
70	purchase.particulars,
71	purchase.created_at,
72
73	purchase_acc_detail.purchase_no,
74	purchase_acc_detail.debit,
75	purchase_acc_detail.credit
76
77	FROM {$wpdb->prefix}erp_acct_purchase AS purchase
78	LEFT JOIN {$wpdb->prefix}erp_acct_voucher_no as voucher ON purchase.voucher_no = voucher.id
79	LEFT JOIN {$wpdb->prefix}erp_acct_purchase_account_details AS purchase_acc_detail ON purchase.voucher_no = purchase_acc_detail.trn_no
80	WHERE purchase.voucher_no = %d",
81	$purchase_no
82	);
83
84	$row = $wpdb->get_row( $sql, ARRAY_A );
85	$row['line_items'] = erp_acct_format_purchase_line_items( $purchase_no );
86	$row['attachments'] = unserialize( $row['attachments'] );
87	$row['total_due'] = $row['credit'] - $row['debit'];
88
89	return $row;
90	}
91
92	/**
93	* Purchase items detail
94	*
95	* @param $voucher_no
96	*
97	* @return array\|object\|null
98	*/
99	function erp_acct_format_purchase_line_items( $voucher_no ) {
100	global $wpdb;
101
102	$sql = $wpdb->prepare(
103	"SELECT
104	purchase_detail.product_id,
105	purchase_detail.qty,
106	purchase_detail.price,
107	purchase_detail.amount,
108
109	product.name,
110	product.product_type_id,
111	product.category_id,
112	product.vendor,
113	product.cost_price,
114	product.sale_price as unit_price
115
116	FROM {$wpdb->prefix}erp_acct_purchase AS purchase
117	LEFT JOIN {$wpdb->prefix}erp_acct_purchase_details AS purchase_detail ON purchase.voucher_no = purchase_detail.trn_no
118	LEFT JOIN {$wpdb->prefix}erp_acct_products AS product ON purchase_detail.product_id = product.id
119	WHERE purchase.voucher_no = %d",
120	$voucher_no
121	);
122
123	$results = $wpdb->get_results( $sql, ARRAY_A );
124
125	// calculate every line total
126	foreach ( $results as $key => $value ) {
127	$results[ $key ]['line_total'] = $value['amount'];
128	}
129
130	return $results;
131	}
132
133	/**
134	* Insert a purchase
135	*
136	* @param $data
137	*
138	* @return mixed
139	*/
140	function erp_acct_insert_purchase( $data ) {
141	global $wpdb;
142
143	$created_by = get_current_user_id();
144	$voucher_no = null;
145	$data['created_at'] = date( 'Y-m-d' );
146	$data['created_by'] = $created_by;
147	$data['updated_at'] = date( 'Y-m-d' );
148	$data['updated_by'] = $created_by;
149
150	$purchase_type_order = 1;
151	$draft = 1;
152	$currency = erp_get_currency( true );
153
154	try {
155	$wpdb->query( 'START TRANSACTION' );
156
157	$wpdb->insert(
158	$wpdb->prefix . 'erp_acct_voucher_no',
159	[
160	'type' => 'purchase',
161	'currency' => $currency,
162	'editable' => 1,
163	'created_at' => $data['created_at'],
164	'created_by' => $created_by,
165	'updated_at' => isset( $data['updated_at'] ) ? $data['updated_at'] : '',
166	'updated_by' => isset( $data['updated_by'] ) ? $data['updated_by'] : '',
167	]
168	);
169
170	$voucher_no = $wpdb->insert_id;
171	$purchase_no = $voucher_no;
172
173	$purchase_data = erp_acct_get_formatted_purchase_data( $data, $voucher_no );
174
175	$wpdb->insert(
176	$wpdb->prefix . 'erp_acct_purchase',
177	[
178	'voucher_no' => $voucher_no,
179	'vendor_id' => $purchase_data['vendor_id'],
180	'vendor_name' => $purchase_data['vendor_name'],
181	'billing_address' => $purchase_data['billing_address'],
182	'trn_date' => $purchase_data['trn_date'],
183	'due_date' => $purchase_data['due_date'],
184	'amount' => $purchase_data['amount'],
185	'ref' => $purchase_data['ref'],
186	'status' => $purchase_data['status'],
187	'purchase_order' => $purchase_data['purchase_order'],
188	'attachments' => $purchase_data['attachments'],
189	'particulars' => $purchase_data['particulars'],
190	'created_at' => $purchase_data['created_at'],
191	'created_by' => $created_by,
192	'updated_at' => $purchase_data['updated_at'],
193	'updated_by' => $purchase_data['updated_by'],
194	]
195	);
196
197	$items = $data['line_items'];
198
199	foreach ( $items as $item ) {
200	$wpdb->insert( $wpdb->prefix . 'erp_acct_purchase_details', [
201	'trn_no' => $voucher_no,
202	'product_id' => $item['product_id'],
203	'qty' => $item['qty'],
204	'price' => $item['unit_price'],
205	'amount' => $item['item_total'],
206	'created_at' => $purchase_data['created_at'],
207	'created_by' => $created_by,
208	'updated_at' => $purchase_data['updated_at'],
209	'updated_by' => $purchase_data['updated_by'],
210	] );
211	}
212
213	do_action( 'erp_acct_after_purchase_create', $data, $voucher_no );
214
215	$email = erp_get_people_email( $purchase_data['vendor_id'] );
216
217	if ( $purchase_type_order === $purchase_data['purchase_order'] \|\| $draft === $purchase_data['status'] ) {
218	$wpdb->query( 'COMMIT' );
219	$purchase_order = erp_acct_get_purchase( $voucher_no );
220	$purchase_order['email'] = $email;
221	do_action( 'erp_acct_new_transaction_purchase_order', $voucher_no, $purchase_order );
222
223	return $purchase_order;
224	}
225
226	$wpdb->insert(
227	$wpdb->prefix . 'erp_acct_purchase_account_details',
228	[
229	'purchase_no' => $purchase_no,
230	'trn_no' => $voucher_no,
231	'trn_date' => $purchase_data['trn_date'],
232	'particulars' => $purchase_data['particulars'],
233	'debit' => 0,
234	'credit' => $purchase_data['amount'],
235	'created_at' => $purchase_data['created_at'],
236	'created_by' => $created_by,
237	'updated_at' => $purchase_data['updated_at'],
238	'updated_by' => $purchase_data['updated_by'],
239	]
240	);
241
242	erp_acct_insert_purchase_data_into_ledger( $purchase_data );
243
244	$data['dr'] = 0;
245	$data['cr'] = $purchase_data['amount'];
246	erp_acct_insert_data_into_people_trn_details( $data, $voucher_no );
247
248	$wpdb->query( 'COMMIT' );
249	} catch ( Exception $e ) {
250	$wpdb->query( 'ROLLBACK' );
251
252	return new WP_error( 'purchase-exception', $e->getMessage() );
253	}
254
255	$purchase = erp_acct_get_purchase( $purchase_no );
256
257	$purchase['email'] = $email;
258
259	do_action( 'erp_acct_new_transaction_purchase', $voucher_no, $purchase );
260
261	return $purchase;
262	}
263
264	/**
265	* Update a purchase
266	*
267	* @param $purchase_data
268	* @param $purchase_id
269	*
270	* @return mixed
271	*/
272	function erp_acct_update_purchase( $purchase_data, $purchase_id ) {
273	global $wpdb;
274
275	if ( 1 === $purchase_data['purchase_order'] && $purchase_data['convert'] ) {
276	erp_acct_convert_order_to_purchase( $purchase_data, $purchase_id );
277
278	return;
279	}
280
281	$user_id = get_current_user_id();
282	$purchase_type_order = 1;
283	$draft = 1;
284	$voucher_no = null;
285
286	$data['created_at'] = date( 'Y-m-d' );
287	$data['created_by'] = $user_id;
288	$data['updated_at'] = date( 'Y-m-d' );
289	$data['updated_by'] = $user_id;
290	$currency = erp_get_currency( true );
291
292	try {
293	$wpdb->query( 'START TRANSACTION' );
294
295	if ( $purchase_type_order === $purchase_data['purchase_order'] \|\| $draft === $purchase_data['status'] ) {
296	$purchase_data = erp_acct_get_formatted_purchase_data( $purchase_data, $purchase_id );
297
298	$wpdb->update(
299	$wpdb->prefix . 'erp_acct_purchase',
300	[
301	'vendor_id' => $purchase_data['vendor_id'],
302	'vendor_name' => $purchase_data['vendor_name'],
303	'trn_date' => $purchase_data['trn_date'],
304	'due_date' => $purchase_data['due_date'],
305	'amount' => $purchase_data['amount'],
306	'ref' => $purchase_data['ref'],
307	'status' => $purchase_data['status'],
308	'purchase_order' => $purchase_data['purchase_order'],
309	'attachments' => $purchase_data['attachments'],
310	'particulars' => $purchase_data['particulars'],
311	'created_at' => $purchase_data['created_at'],
312	'created_by' => $purchase_data['created_by'],
313	'updated_at' => $purchase_data['updated_at'],
314	'updated_by' => $purchase_data['updated_by'],
315	],
316	[
317	'voucher_no' => $purchase_id,
318	]
319	);
320
321	/**
322	*? We can't update `purchase_details` directly
323	*? suppose there were 5 detail rows previously
324	*? but on update there may be 2 detail rows
325	*? that's why we can't update because the foreach will iterate only 2 times, not 5 times
326	*? so, remove previous rows and insert new rows
327	*/
328	$prev_detail_ids = $wpdb->get_results( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}erp_acct_purchase_details WHERE trn_no = %s", $purchase_id ), ARRAY_A );
329
330	$prev_detail_ids = implode( ',', array_map( 'absint', $prev_detail_ids ) );
331
332	$wpdb->delete( $wpdb->prefix . 'erp_acct_purchase_details', [ 'trn_no' => $purchase_id ] );
333
334	$items = $purchase_data['purchase_details'];
335
336	foreach ( $items as $key => $item ) {
337	$wpdb->update(
338	$wpdb->prefix . 'erp_acct_purchase_details',
339	[
340	'product_id' => $item['product_id'],
341	'qty' => $item['qty'],
342	'price' => $item['unit_price'],
343	'amount' => $item['item_total'],
344	'created_at' => $purchase_data['created_at'],
345	'created_by' => $purchase_data['created_by'],
346	'updated_at' => $purchase_data['updated_at'],
347	'updated_by' => $purchase_data['updated_by'],
348	],
349	[
350	'trn_no' => $purchase_id,
351	]
352	);
353	}
354
355	$wpdb->query( 'COMMIT' );
356
357	return erp_acct_get_purchase( $purchase_id );
358	} else {
359	// disable editing on old bill
360	$wpdb->update( $wpdb->prefix . 'erp_acct_voucher_no', [ 'editable' => 0 ], [ 'id' => $purchase_id ] );
361
362	// insert contra voucher
363	$wpdb->insert(
364	$wpdb->prefix . 'erp_acct_voucher_no',
365	[
366	'type' => 'purchase',
367	'currency' => $currency,
368	'editable' => 0,
369	'created_at' => $data['created_at'],
370	'created_by' => $data['created_by'],
371	'updated_at' => $data['updated_at'],
372	'updated_by' => $data['updated_by'],
373	]
374	);
375
376	$voucher_no = $wpdb->insert_id;
377
378	$old_purchase = erp_acct_get_purchase( $purchase_id );
379
380	// insert contra `erp_acct_purchase` (basically a duplication of row)
381	$wpdb->query( $wpdb->prepare( "CREATE TEMPORARY TABLE acct_tmptable SELECT * FROM {$wpdb->prefix}erp_acct_purchase WHERE voucher_no = %d", $purchase_id ) );
382	$wpdb->query(
383	$wpdb->prepare(
384	"UPDATE acct_tmptable SET id = %d, voucher_no = %d, particulars = 'Contra entry for voucher no \#%d', created_at = '%s'",
385	0,
386	$voucher_no,
387	$purchase_id,
388	$data['created_at']
389	)
390	);
391	$wpdb->query( "INSERT INTO {$wpdb->prefix}erp_acct_purchase SELECT * FROM acct_tmptable" );
392	$wpdb->query( 'DROP TABLE acct_tmptable' );
393
394	// change purchase status and other things
395	$status_closed = 7;
396	$wpdb->query(
397	$wpdb->prepare(
398	"UPDATE {$wpdb->prefix}erp_acct_purchase SET status = %d, updated_at ='%s', updated_by = %d WHERE voucher_no IN (%d, %d)",
399	$status_closed,
400	$data['updated_at'],
401	$user_id,
402	$purchase_id,
403	$voucher_no
404	)
405	);
406
407	$items = $old_purchase['line_items'];
408
409	foreach ( $items as $key => $item ) {
410	$wpdb->insert(
411	$wpdb->prefix . 'erp_acct_purchase_details',
412	[
413	'trn_no' => $voucher_no,
414	'product_id' => $item['product_id'],
415	'qty' => $item['qty'],
416	'price' => $item['unit_price'],
417	'amount' => (float) $item['qty'] * (float) $item['unit_price'],
418	'updated_at' => date( 'Y-m-d H:i:s' ),
419	'updated_by' => $user_id,
420	]
421	);
422	}
423
424	$wpdb->insert(
425	$wpdb->prefix . 'erp_acct_purchase_account_details',
426	[
427	'purchase_no' => $purchase_id,
428	'trn_no' => $voucher_no,
429	'trn_date' => $purchase_data['trn_date'],
430	'particulars' => $purchase_data['particulars'],
431	'debit' => $old_purchase['amount'],
432	'updated_at' => date( 'Y-m-d H:i:s' ),
433	'updated_by' => $user_id,
434	]
435	);
436
437	do_action( 'erp_acct_after_purchase_update', $purchase_data, $purchase_id );
438
439	erp_acct_update_purchase_data_into_ledger( $items, $purchase_id );
440
441	$new_purchase = erp_acct_insert_purchase( $purchase_data );
442
443	$data['dr'] = 0;
444	$data['cr'] = $purchase_data['amount'];
445	erp_acct_update_data_into_people_trn_details( $data, $old_purchase['voucher_no'] );
446	}
447
448	$wpdb->query( 'COMMIT' );
449	} catch ( Exception $e ) {
450	$wpdb->query( 'ROLLBACK' );
451
452	return new WP_error( 'purchase-exception', $e->getMessage() );
453	}
454
455	return erp_acct_get_purchase( $new_purchase['voucher_no'] );
456	}
457
458	/**
459	* Convert order to purchase
460	*
461	* @param array $purchase_data
462	* @param int $purchase_id
463	*
464	* @return array
465	*/
466	function erp_acct_convert_order_to_purchase( $purchase_data, $purchase_id ) {
467	global $wpdb;
468
469	$user_id = get_current_user_id();
470
471	try {
472	$wpdb->query( 'START TRANSACTION' );
473
474	$purchase_data = erp_acct_get_formatted_purchase_data( $purchase_data, $purchase_id );
475
476	// erp_acct_purchase
477	$wpdb->update(
478	$wpdb->prefix . 'erp_acct_purchase',
479	[
480	'vendor_id' => $purchase_data['vendor_id'],
481	'vendor_name' => $purchase_data['vendor_name'],
482	'trn_date' => $purchase_data['trn_date'],
483	'due_date' => $purchase_data['due_date'],
484	'amount' => $purchase_data['amount'],
485	'ref' => $purchase_data['ref'],
486	'status' => 2,
487	'purchase_order' => false,
488	'attachments' => $purchase_data['attachments'],
489	'particulars' => $purchase_data['particulars'],
490	'created_at' => $purchase_data['created_at'],
491	'created_by' => $purchase_data['created_by'],
492	'updated_at' => $purchase_data['updated_at'],
493	'updated_by' => $purchase_data['updated_by'],
494	],
495	[ 'voucher_no' => $purchase_id ]
496	);
497
498	// remove data from erp_acct_purchase_details
499	$wpdb->delete( $wpdb->prefix . 'erp_acct_purchase_details', [ 'trn_no' => $purchase_id ] );
500
501	foreach ( $purchase_data['line_items'] as $item ) {
502	$wpdb->insert( $wpdb->prefix . 'erp_acct_purchase_details', [
503	'trn_no' => $purchase_id,
504	'product_id' => $item['product_id'],
505	'qty' => $item['qty'],
506	'price' => $item['unit_price'],
507	'amount' => (float) $item['qty'] * (float) $item['unit_price'],
508	'updated_at' => date( 'Y-m-d' ),
509	'updated_by' => $user_id,
510	] );
511	}
512
513	$wpdb->insert( $wpdb->prefix . 'erp_acct_purchase_account_details', [
514	'purchase_no' => $purchase_id,
515	'trn_no' => $purchase_id,
516	'trn_date' => $purchase_data['trn_date'],
517	'particulars' => $purchase_data['particulars'],
518	'credit' => $purchase_data['amount'],
519	'updated_at' => date( 'Y-m-d' ),
520	'updated_by' => $user_id,
521	] );
522
523	erp_acct_insert_purchase_data_into_ledger( $purchase_data );
524
525	$data['dr'] = 0;
526	$data['cr'] = $purchase_data['amount'];
527	$data['vendor_id'] = $purchase_data['vendor_id'];
528	erp_acct_insert_data_into_people_trn_details( $data, $purchase_id );
529
530	$wpdb->query( 'COMMIT' );
531	} catch ( Exception $e ) {
532	$wpdb->query( 'ROLLBACK' );
533
534	return new WP_error( 'purchase-exception', $e->getMessage() );
535	}
536
537	$purchase = erp_acct_get_purchase( $purchase_id );
538
539	$purchase['email'] = erp_get_people_email( $purchase['vendor_id'] );
540
541	do_action( 'erp_acct_new_transaction_purchase', $purchase_id, $purchase );
542
543	return $purchase;
544	}
545
546	/**
547	* Void a purchase
548	*
549	* @param $id
550	*
551	* @return void
552	*/
553	function erp_acct_void_purchase( $id ) {
554	global $wpdb;
555
556	if ( ! $id ) {
557	return;
558	}
559
560	$wpdb->update(
561	$wpdb->prefix . 'erp_acct_purchase',
562	[
563	'status' => 8,
564	],
565	[ 'voucher_no' => $id ]
566	);
567
568	$wpdb->delete( $wpdb->prefix . 'erp_acct_ledger_details', [ 'trn_no' => $id ] );
569	$wpdb->delete( $wpdb->prefix . 'erp_acct_purchase_account_details', [ 'purchase_no' => $id ] );
570	}
571
572	/**
573	* Get formatted purchase data
574	*
575	* @param $data
576	* @param $voucher_no
577	*
578	* @return mixed
579	*/
580	function erp_acct_get_formatted_purchase_data( $data, $voucher_no ) {
581	$user_info = erp_get_people( $data['vendor_id'] );
582
583	$purchase_data['voucher_no'] = isset( $data['voucher_no'] ) ? $data['voucher_no'] : $voucher_no;
584	$purchase_data['vendor_id'] = isset( $data['vendor_id'] ) ? $data['vendor_id'] : 0;
585	$purchase_data['vendor_name'] = $user_info->first_name . ' ' . $user_info->last_name;
586	$purchase_data['billing_address'] = isset( $data['billing_address'] ) ? $data['billing_address'] : '';
587	$purchase_data['trn_date'] = isset( $data['trn_date'] ) ? $data['trn_date'] : date( 'Y-m-d' );
588	$purchase_data['due_date'] = isset( $data['due_date'] ) ? $data['due_date'] : date( 'Y-m-d' );
589	$purchase_data['amount'] = isset( $data['amount'] ) ? floatval( $data['amount'] ) : 0;
590	$purchase_data['attachments'] = isset( $data['attachments'] ) ? $data['attachments'] : '';
591	$purchase_data['status'] = isset( $data['status'] ) ? intval( $data['status'] ) : '';
592	$purchase_data['line_items'] = isset( $data['line_items'] ) ? $data['line_items'] : [];
593	$purchase_data['purchase_order'] = isset( $data['purchase_order'] ) ? intval( $data['purchase_order'] ) : '';
594	$purchase_data['ref'] = isset( $data['ref'] ) ? $data['ref'] : '';
595	// translators: %s: voucher_no
596	$purchase_data['particulars'] = ! empty( $data['particulars'] ) ? $data['particulars'] : sprintf( __( 'Purchase created with voucher no %s', 'erp' ), $voucher_no );
597	$purchase_data['created_at'] = date( 'Y-m-d' );
598	$purchase_data['created_by'] = isset( $data['created_by'] ) ? $data['created_by'] : '';
599	$purchase_data['updated_at'] = isset( $data['updated_at'] ) ? $data['updated_at'] : '';
600	$purchase_data['updated_by'] = isset( $data['updated_by'] ) ? $data['updated_by'] : '';
601
602	if ( ! empty( $data['purchase_order'] ) ) {
603	$purchase_data['status'] = 3;
604	}
605
606	return $purchase_data;
607	}
608
609	/**
610	* Insert purchase/s data into ledger
611	*
612	* @param array $purchase_data
613	*
614	* @return mixed
615	*/
616	function erp_acct_insert_purchase_data_into_ledger( $purchase_data ) {
617	global $wpdb;
618
619	$ledger_map = \WeDevs\ERP\Accounting\Includes\Classes\Ledger_Map::get_instance();
620	$ledger_id = $ledger_map->get_ledger_id_by_slug( 'purchase' );
621
622	if ( ! $ledger_id ) {
623	return new WP_Error( 505, 'Ledger ID not found for purchase', $purchase_data );
624	}
625	// Insert amount in ledger_details
626	$wpdb->insert(
627	$wpdb->prefix . 'erp_acct_ledger_details',
628	[
629	'ledger_id' => $ledger_id,
630	'trn_no' => $purchase_data['voucher_no'],
631	'particulars' => $purchase_data['particulars'],
632	'debit' => $purchase_data['amount'],
633	'credit' => 0,
634	'trn_date' => $purchase_data['trn_date'],
635	'created_at' => $purchase_data['created_at'],
636	'created_by' => $purchase_data['created_by'],
637	'updated_at' => $purchase_data['updated_at'],
638	'updated_by' => $purchase_data['updated_by'],
639	]
640	);
641	}
642
643	/**
644	* Update purchase/s data into ledger
645	*
646	* @param array $purchase_data
647	* @param array $purchase_no
648	*
649	* @return mixed
650	*/
651	function erp_acct_update_purchase_data_into_ledger( $purchase_data, $purchase_no ) {
652	global $wpdb;
653
654	$ledger_map = \WeDevs\ERP\Accounting\Includes\Classes\Ledger_Map::get_instance();
655	$ledger_id = $ledger_map->get_ledger_id_by_slug( 'purchase' );
656
657	if ( ! $ledger_id ) {
658	return new WP_Error( 505, 'Ledger ID not found for purchase', $purchase_data );
659	}
660
661	// insert contra `erp_acct_ledger_details`
662	$wpdb->update( $wpdb->prefix . 'erp_acct_ledger_details', [
663	'ledger_id' => $ledger_id,
664	'particulars' => ! empty( $purchase_data['particulars'] ) ? $purchase_data['particulars'] : '',
665	'credit' => $purchase_data['amount'],
666	'trn_date' => $purchase_data['trn_date'],
667	'created_at' => $purchase_data['created_at'],
668	'created_by' => $purchase_data['created_by'],
669	'updated_at' => $purchase_data['updated_at'],
670	'updated_by' => $purchase_data['updated_by'],
671	], [
672	'trn_no' => $purchase_no,
673	] );
674	}
675
676	/**
677	* Get Purchases count
678	*
679	* @return int
680	*/
681	function erp_acct_get_purchase_count() {
682	global $wpdb;
683
684	$row = $wpdb->get_row( 'SELECT COUNT(*) as count FROM ' . $wpdb->prefix . 'erp_acct_purchase' );
685
686	return $row->count;
687	}
688
689	/**
690	* Get due purchases by vendor
691	*
692	* @return mixed
693	*/
694	function erp_acct_get_due_purchases_by_vendor( $args ) {
695	global $wpdb;
696
697	$defaults = [
698	'number' => 20,
699	'offset' => 0,
700	'orderby' => 'id',
701	'order' => 'DESC',
702	'count' => false,
703	's' => '',
704	];
705
706	$args = wp_parse_args( $args, $defaults );
707
708	$limit = '';
709
710	if ( '-1' !== $args['number'] ) {
711	$limit = "LIMIT {$args['number']} OFFSET {$args['offset']}";
712	}
713
714	$purchases = "{$wpdb->prefix}erp_acct_purchase";
715	$purchase_act_details = "{$wpdb->prefix}erp_acct_purchase_account_details";
716	$items = $args['count'] ? ' COUNT( id ) as total_number ' : ' * ';
717
718	$query = $wpdb->prepare(
719	"SELECT $items FROM $purchases as purchase INNER JOIN
720	(
721	SELECT purchase_no, ABS(SUM( pa.debit - pa.credit)) as due
722	FROM $purchase_act_details as pa
723	GROUP BY pa.purchase_no
724	HAVING due > 0
725	) as ps
726	ON purchase.voucher_no = ps.purchase_no
727	WHERE purchase.vendor_id = %d AND purchase.status != 1 AND purchase.purchase_order != 1
728	ORDER BY %s %s $limit",
729	$args['vendor_id'],
730	$args['orderby'],
731	$args['order']
732	);
733
734	if ( $args['count'] ) {
735	return $wpdb->get_var( $query );
736	}
737
738	return $wpdb->get_results( $query, ARRAY_A );
739	}
740
741	/**
742	* Get due of a purchase
743	*
744	* @param $purchase_no
745	*
746	* @return int
747	*/
748	function erp_acct_get_purchase_due( $purchase_no ) {
749	global $wpdb;
750
751	$result = $wpdb->get_row( $wpdb->prepare( "SELECT purchase_no, SUM( debit - credit) as due FROM {$wpdb->prefix}erp_acct_purchase_account_details WHERE purchase_no = %d GROUP BY purchase_no", $purchase_no ), ARRAY_A );
752
753	return $result['due'];
754	}
755