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

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

602 lines 15.0 KB

Raw Download Zip

1	<?php
2
3	if ( ! defined( 'ABSPATH' ) ) {
4	exit; // Exit if accessed directly
5	}
6
7	/**
8	* Upload attachments
9	*
10	* @return array
11	*/
12	function erp_acct_upload_attachments( $files ) {
13	if ( ! function_exists( 'wp_handle_upload' ) ) {
14	require_once ABSPATH . 'wp-admin/includes/file.php';
15	}
16
17	$attachments = [];
18	$movefiles = [];
19
20	// Formatting request for upload
21	for ( $i = 0; $i < count( $files['name'] ); $i++ ) {
22	$attachments[] = [
23	'name' => wp_unslash( $files['name'][ $i ] ),
24	'type' => $files['type'][ $i ],
25	'tmp_name' => wp_unslash( $files['tmp_name'][ $i ] ),
26	'error' => $files['error'][ $i ],
27	'size' => $files['size'][ $i ],
28	];
29	}
30
31	foreach ( $attachments as $attachment ) {
32	$movefiles[] = wp_handle_upload( $attachment, [ 'test_form' => false ] );
33	}
34
35	return $movefiles;
36	}
37
38	/**
39	* Get payable data for given month
40	*
41	* @param $from
42	* @param $to
43	*
44	* @return array\|object\|null
45	*/
46	function erp_acct_get_payables( $from, $to ) {
47	global $wpdb;
48
49	$from_date = date( 'Y-m-d', strtotime( $from ) );
50	$to_date = date( 'Y-m-d', strtotime( $to ) );
51
52	$purchases = $wpdb->prefix . 'erp_acct_purchase';
53	$purchase_acct_details = $wpdb->prefix . 'erp_acct_purchase_account_details';
54
55	$purchase_query = $wpdb->prepare(
56	"Select voucher_no, SUM(ad.debit - ad.credit) as due, due_date
57	FROM $purchases LEFT JOIN $purchase_acct_details as ad
58	ON ad.purchase_no = voucher_no where due_date
59	BETWEEN %s and %s Group BY voucher_no Having due < 0 ",
60	$from_date,
61	$to_date
62	);
63
64	$purchase_results = $wpdb->get_results( $purchase_query, ARRAY_A );
65
66	$bills = $wpdb->prefix . 'erp_acct_bills';
67	$bill_acct_details = $wpdb->prefix . 'erp_acct_bill_account_details';
68	$bills_query = $wpdb->prepare(
69	"Select voucher_no, SUM(ad.debit - ad.credit) as due, due_date
70	FROM $bills LEFT JOIN $bill_acct_details as ad
71	ON ad.bill_no = voucher_no where due_date
72	BETWEEN %s and %s Group BY voucher_no Having due < 0",
73	$from_date,
74	$to_date
75	);
76
77	$bill_results = $wpdb->get_results( $bills_query, ARRAY_A );
78
79	if ( ! empty( $purchase_results ) && ! empty( $bill_results ) ) {
80	return array_merge( $bill_results, $purchase_results );
81	}
82
83	if ( empty( $bill_results ) ) {
84	return $purchase_results;
85	}
86
87	if ( empty( $purchase_results ) ) {
88	return $bill_results;
89	}
90	}
91
92	/**
93	* Get Payable overview data
94	*
95	* @return array
96	*/
97	function erp_acct_get_payables_overview() {
98	// get dates till coming 90 days
99	$from_date = date( 'Y-m-d' );
100	$to_date = date( 'Y-m-d', strtotime( '+90 day', strtotime( $from_date ) ) );
101
102	$data = [];
103	$amount = [
104	'first' => 0,
105	'second' => 0,
106	'third' => 0,
107	];
108
109	$result = erp_acct_get_payables( $from_date, $to_date );
110
111	if ( ! empty( $result ) ) {
112	$from_date = new DateTime( $from_date );
113
114	foreach ( $result as $item_data ) {
115	$item = (object) $item_data;
116	$later = new DateTime( $item->due_date );
117	$diff = $later->diff( $from_date )->format( '%a' );
118
119	//segment by date difference
120	switch ( $diff ) {
121	case 0 === $diff:
122	$data['first'][] = $item_data;
123	$amount['first'] = $amount['first'] + abs( $item->due );
124	break;
125
126	case $diff <= 30:
127	$data['first'][] = $item_data;
128	$amount['first'] = $amount['first'] + abs( $item->due );
129	break;
130
131	case $diff <= 60:
132	$data['second'][] = $item_data;
133	$amount['second'] = $amount['second'] + abs( $item->due );
134	break;
135
136	case $diff <= 90:
137	$data['third'][] = $item_data;
138	$amount['third'] = $amount['third'] + abs( $item->due );
139	break;
140
141	default:
142	}
143	}
144	}
145
146	return [
147	'data' => $data,
148	'amount' => $amount,
149	];
150	}
151
152	/**
153	* Insert check data
154	*
155	* @param array $check_data
156	*
157	* @return void
158	*/
159	function erp_acct_insert_check_data( $check_data ) {
160	global $wpdb;
161
162	$wpdb->insert(
163	$wpdb->prefix . 'erp_acct_expense_checks',
164	[
165	'trn_no' => $check_data['voucher_no'],
166	'check_no' => $check_data['check_no'],
167	'voucher_type' => $check_data['voucher_type'],
168	'amount' => $check_data['amount'],
169	'bank' => $check_data['bank'],
170	'name' => $check_data['name'],
171	'pay_to' => $check_data['pay_to'],
172	'created_at' => $check_data['created_at'],
173	'created_by' => $check_data['created_by'],
174	'updated_at' => $check_data['updated_at'],
175	'updated_by' => $check_data['updated_by'],
176	]
177	);
178	}
179
180	/**
181	* Update check data
182	*
183	* @param array $check_data
184	* @param $check_no
185	*
186	* @return void
187	*/
188	function erp_acct_update_check_data( $check_data, $check_no ) {
189	global $wpdb;
190
191	$wpdb->insert(
192	$wpdb->prefix . 'erp_acct_expense_checks',
193	[
194	'trn_no' => $check_data['voucher_no'],
195	'voucher_type' => $check_data['voucher_type'],
196	'amount' => $check_data['amount'],
197	'bank' => $check_data['bank'],
198	'name' => $check_data['name'],
199	'pay_to' => $check_data['pay_to'],
200	'created_at' => $check_data['created_at'],
201	'created_by' => $check_data['created_by'],
202	'updated_at' => $check_data['updated_at'],
203	'updated_by' => $check_data['updated_by'],
204	],
205	[
206	'check_no' => $check_no,
207	]
208	);
209	}
210
211	/**
212	* Get people name, email by id
213	*
214	* @param $people_id
215	*
216	* @return array
217	*/
218	function erp_acct_get_people_info_by_id( $people_id ) {
219	global $wpdb;
220
221	$row = $wpdb->get_row( $wpdb->prepare( "SELECT first_name, last_name, email FROM {$wpdb->prefix}erp_peoples WHERE id = %d LIMIT 1", $people_id ) );
222
223	return $row;
224	}
225
226	/**
227	* Get ledger name, slug by id
228	*
229	* @param $ledger_id
230	*
231	* @return array
232	*/
233	function erp_acct_get_ledger_by_id( $ledger_id ) {
234	global $wpdb;
235
236	$row = $wpdb->get_row( $wpdb->prepare( "SELECT name, slug, code FROM {$wpdb->prefix}erp_acct_ledgers WHERE id = %d LIMIT 1", $ledger_id ) );
237
238	return $row;
239	}
240
241	/**
242	* Get sing ledger row data by id, slug or code
243	*
244	* @since 1.6.3
245	*
246	* @param $field string id, code or slug field name to search by
247	* @param $value string $field value to search
248	*
249	* @return array
250	*/
251	function erp_acct_get_ledger_by( $field = 'id', $value = '' ) {
252	global $wpdb;
253
254	$field = sanitize_text_field( $field );
255	// validate fields
256	if ( ! in_array( $field, [ 'id', 'code', 'slug' ] ) ) {
257	return null;
258	}
259
260	if ( empty( $value ) ) {
261	return null;
262	}
263
264	return $wpdb->get_row(
265	$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}erp_acct_ledgers WHERE $field = %s LIMIT 1", $value ),
266	ARRAY_A
267	);
268	}
269
270	/**
271	* Get product type by id
272	*
273	* @param $product_type_id
274	*
275	* @return array
276	*/
277	function erp_acct_get_product_type_by_id( $product_type_id ) {
278	global $wpdb;
279
280	$row = $wpdb->get_row( $wpdb->prepare( "SELECT name FROM {$wpdb->prefix}erp_acct_product_types WHERE id = %d LIMIT 1", $product_type_id ) );
281
282	return $row;
283	}
284
285	/**
286	* Get product category by id
287	*
288	* @param $cat_id
289	*
290	* @return array
291	*/
292	function erp_acct_get_product_category_by_id( $cat_id ) {
293	global $wpdb;
294
295	$row = $wpdb->get_row( $wpdb->prepare( "SELECT name FROM {$wpdb->prefix}erp_acct_product_categories WHERE id = %d LIMIT 1", $cat_id ) );
296
297	return $row;
298	}
299
300	/**
301	* Get tax agency name by id
302	*
303	* @param $agency_id
304	*
305	* @return array
306	*/
307	function erp_acct_get_tax_agency_by_id( $agency_id ) {
308	global $wpdb;
309
310	$row = $wpdb->get_row( $wpdb->prepare( "SELECT name FROM {$wpdb->prefix}erp_acct_tax_agencies WHERE id = %d LIMIT 1", $agency_id ) );
311
312	return $row->name;
313	}
314
315	/**
316	* Get tax category by id
317	*
318	* @param $cat_id
319	*
320	* @return array
321	*/
322	function erp_acct_get_tax_category_by_id( $cat_id ) {
323	global $wpdb;
324
325	if ( null !== $cat_id ) {
326	return $wpdb->get_var( $wpdb->prepare( "SELECT name FROM {$wpdb->prefix}erp_acct_tax_categories WHERE id = %d", $cat_id ) );
327	}
328
329	return '';
330	}
331
332	/**
333	* Get transaction status by id
334	*
335	* @param $trn_id
336	*
337	* @return string
338	*/
339	function erp_acct_get_trn_status_by_id( $trn_id ) {
340	global $wpdb;
341
342	if ( ! $trn_id ) {
343	return 'pending';
344	}
345
346	$row = $wpdb->get_row( $wpdb->prepare( "SELECT type_name FROM {$wpdb->prefix}erp_acct_trn_status_types WHERE id = %d", $trn_id ) );
347
348	return ucfirst( str_replace( '_', ' ', $row->type_name ) );
349	}
350
351	/**
352	* Get payment method by id
353	*
354	* @param $trn_id
355	*
356	* @return array
357	*/
358	function erp_acct_get_payment_method_by_id( $method_id ) {
359	global $wpdb;
360
361	$row = $wpdb->get_row( $wpdb->prepare( "SELECT name FROM {$wpdb->prefix}erp_acct_payment_methods WHERE id = %d LIMIT 1", $method_id ) );
362
363	return $row;
364	}
365
366	/**
367	* Get payment method by id
368	*
369	* @param $trn_id
370	*
371	* @return string
372	*/
373	function erp_acct_get_payment_method_name_by_id( $method_id ) {
374	global $wpdb;
375
376	$row = $wpdb->get_row( $wpdb->prepare( "SELECT name FROM {$wpdb->prefix}erp_acct_payment_methods WHERE id = %d LIMIT 1", $method_id ) );
377
378	return $row->name;
379	}
380
381	/**
382	* Get check transaction type by id
383	*
384	* @param $trn_id
385	*
386	* @return array
387	*/
388	function erp_acct_get_check_trn_type_by_id( $trn_type_id ) {
389	global $wpdb;
390
391	$row = $wpdb->get_row( $wpdb->prepare( "SELECT name FROM {$wpdb->prefix}erp_acct_check_trn_tables WHERE id = %d LIMIT 1", $trn_type_id ) );
392
393	return $row;
394	}
395
396	/**
397	* Get Accounting Quick Access Menus
398	*
399	* @return array
400	*/
401	function erp_acct_quick_access_menu() {
402	$menus = [
403	'invoice' => [
404	'title' => 'Invoice',
405	'slug' => 'invoice',
406	'url' => 'invoices/new',
407	],
408	'estimate' => [
409	'title' => 'Estimate',
410	'slug' => 'estimate',
411	'url' => 'estimates/new',
412	],
413	'rec_payment' => [
414	'title' => 'Receive Payment',
415	'slug' => 'payment',
416	'url' => 'payments/new',
417	],
418	'bill' => [
419	'title' => 'Bill',
420	'slug' => 'bill',
421	'url' => 'bills/new',
422	],
423	'pay_bill' => [
424	'title' => 'Pay Bill',
425	'slug' => 'pay_bill',
426	'url' => 'pay-bills/new',
427	],
428	'purchase-order' => [
429	'title' => 'Purchase Order',
430	'slug' => 'purchase-orders',
431	'url' => 'purchase-orders/new',
432	],
433	'purchase' => [
434	'title' => 'Purchase',
435	'slug' => 'purchase',
436	'url' => 'purchases/new',
437	],
438	'pay_purchase' => [
439	'title' => 'Pay Purchase',
440	'slug' => 'pay_purchase',
441	'url' => 'pay-purchases/new',
442	],
443	'expense' => [
444	'title' => 'Expense',
445	'slug' => 'expense',
446	'url' => 'expenses/new',
447	],
448	'check' => [
449	'title' => 'Check',
450	'slug' => 'check',
451	'url' => 'checks/new',
452	],
453	'journal' => [
454	'title' => 'Journal',
455	'slug' => 'journal',
456	'url' => 'transactions/journals/new',
457	],
458	'tax_rate' => [
459	'title' => 'Tax Payment',
460	'slug' => 'pay_tax',
461	'url' => 'pay-tax',
462	],
463	'opening_balance' => [
464	'title' => __( 'Opening Balance', 'erp' ),
465	'slug' => 'opening_balance',
466	'url' => 'opening-balance',
467	],
468	];
469
470	return apply_filters( 'erp_acct_quick_menu', $menus );
471	}
472
473	/**
474	* Change a string to slug
475	*/
476	function slugify( $str ) {
477	// replace non letter or digits by _
478	$str = preg_replace( '~[^\pL\d]+~u', '_', $str );
479
480	return strtolower( $str );
481	}
482
483	/**
484	* Check voucher edit state
485	*
486	* @param int $id
487	*
488	* @return bool
489	*/
490	function erp_acct_check_voucher_edit_state( $id ) {
491	global $wpdb;
492
493	$res = $wpdb->get_var( $wpdb->prepare( "SELECT editable FROM {$wpdb->prefix}erp_acct_voucher_no WHERE id = %d", $id ) );
494
495	return ! empty( $res ) ? true : false;
496	}
497
498	/**
499	* Check if people exists (customer/vendor)
500	*
501	* @param string $email
502	*
503	* @return bool
504	*/
505	function erp_acct_check_people_exists( $email ) {
506	$people = erp_get_people_by( 'email', $email );
507
508	// this $email belongs to nobody
509	if ( ! $people ) {
510	return false;
511	}
512
513	if ( in_array( 'customer', $people->types, true ) \|\| in_array( 'vendor', $people->types, true ) ) {
514	return true;
515	}
516
517	return false;
518	}
519
520	/**
521	* Get transaction id by status slug
522	*
523	* @param string $slug
524	*
525	* @return int
526	*/
527	function erp_acct_trn_status_by_id( $slug ) {
528	global $wpdb;
529
530	return $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}erp_acct_trn_status_types WHERE slug = %s", $slug ) );
531	}
532
533	/**
534	* Get all transaction statuses
535	*
536	* @return array
537	*/
538	function erp_acct_get_all_trn_statuses() {
539	global $wpdb;
540
541	return $wpdb->get_results( "SELECT id,type_name as name, slug FROM {$wpdb->prefix}erp_acct_trn_status_types", ARRAY_A );
542	}
543
544	// accounting customer auto create
545	add_action( 'erp_crm_save_contact_data', 'erp_acct_customer_create_from_crm', 10, 3 );
546	add_action( 'erp_crm_contact_created', 'erp_acct_customer_auto_create_from_crm', 10, 2 );
547
548	/**
549	* Auto create customer when creating CRM contact/company
550	*
551	* @param $customer
552	* @param $customer_id
553	* @param $data
554	*
555	* @since 1.2.7
556	*/
557	function erp_acct_customer_create_from_crm( $customer, $customer_id, $data ) {
558	$customer_auto_import = (int) erp_get_option( 'customer_auto_import', false, 0 );
559	$crm_user_type = erp_get_option( 'crm_user_type', false, [] ); // Contact or Company
560
561	if ( ! $customer_auto_import ) {
562	return;
563	}
564
565	if ( ! count( $crm_user_type ) ) {
566	return;
567	}
568
569	// no need to add wordpress `user id` again
570	// [ `user id` already added when contact is created ]
571	$data['is_wp_user'] = false;
572	$data['wp_user_id'] = '';
573
574	if ( ! isset( $data['company'] ) ) {
575	// the created crm user type is NOT a company
576	if ( ! in_array( 'contact', $crm_user_type ) ) {
577	return;
578	}
579	} else {
580	if ( ! in_array( 'company', $crm_user_type ) ) {
581	return;
582	}
583	}
584
585	$data['people_id'] = $customer_id;
586	$data['type'] = 'customer';
587
588	erp_convert_to_people( $data );
589	}
590
591	/**
592	* Accounting customer auto create from crm
593	*
594	* @since 1.2.8
595	*
596	* @param int $contact_id
597	* @param object $data
598	*/
599	function erp_acct_customer_auto_create_from_crm( $contact_id, $data ) {
600	erp_acct_customer_create_from_crm( [], $contact_id, $data );
601	}
602