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

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

588 lines 17.6 KB

Raw Download Zip

1	<?php
2
3	if ( ! defined( 'ABSPATH' ) ) {
4	exit; // Exit if accessed directly
5	}
6
7	/**
8	* Insert employee data as people
9	*
10	* @param $data
11	* @param $update
12	*
13	* @return int
14	*/
15	function erp_acct_add_employee_as_people( $data, $update = false ) {
16	global $wpdb;
17	$people_id = null;
18
19	if ( erp_acct_is_employee_people( $data['user_id'] ) ) {
20	return;
21	}
22
23	$company = new \WeDevs\ERP\Company();
24
25	if ( $update ) {
26	$wpdb->update(
27	$wpdb->prefix . 'erp_peoples',
28	[
29	'first_name' => $data['personal']['first_name'],
30	'last_name' => $data['personal']['last_name'],
31	'company' => $company->name,
32	'email' => $data['user_email'],
33	'phone' => $data['personal']['phone'],
34	'mobile' => $data['personal']['mobile'],
35	'other' => '',
36	'website' => '',
37	'fax' => '',
38	'notes' => $data['personal']['description'],
39	'street_1' => $data['personal']['street_1'],
40	'street_2' => $data['personal']['street_2'],
41	'city' => $data['personal']['city'],
42	'state' => $data['personal']['state'],
43	'postal_code' => $data['personal']['postal_code'],
44	'country' => $data['personal']['country'],
45	'currency' => '',
46	'life_stage' => '',
47	'contact_owner' => '',
48	'hash' => '',
49	'created_by' => get_current_user_id(),
50	'created' => '',
51	],
52	[
53	'user_id' => $data['user_id'],
54	]
55	);
56	} else {
57	$wpdb->insert(
58	$wpdb->prefix . 'erp_peoples',
59	[
60	'user_id' => $data['user_id'],
61	'first_name' => $data['personal']['first_name'],
62	'last_name' => $data['personal']['last_name'],
63	'company' => $company->name,
64	'email' => $data['user_email'],
65	'phone' => $data['personal']['phone'],
66	'mobile' => $data['personal']['mobile'],
67	'other' => '',
68	'website' => '',
69	'fax' => '',
70	'notes' => $data['personal']['description'],
71	'street_1' => $data['personal']['street_1'],
72	'street_2' => $data['personal']['street_2'],
73	'city' => $data['personal']['city'],
74	'state' => $data['personal']['state'],
75	'postal_code' => $data['personal']['postal_code'],
76	'country' => $data['personal']['country'],
77	'currency' => '',
78	'life_stage' => '',
79	'contact_owner' => '',
80	'hash' => '',
81	'created_by' => get_current_user_id(),
82	'created' => '',
83	]
84	);
85
86	$people_id = $wpdb->insert_id;
87	}
88
89	return $people_id;
90	}
91
92	/**
93	* Get transaction by date
94	*
95	* @param int $people_id
96	* @param array $args
97	*
98	* @return array
99	*/
100	function erp_people_filter_transaction( $people_id, $args = [] ) {
101	global $wpdb;
102	$start_date = isset( $args['start_date'] ) ? $args['start_date'] : '';
103	$end_date = isset( $args['end_date'] ) ? $args['start_date'] : '';
104
105	$rows = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}erp_acct_people_account_details WHERE trn_date >= '{$start_date}' AND trn_date <= '{$end_date}' AND people_id = {$people_id}", ARRAY_A );
106
107	return $rows;
108	}
109
110	/**
111	* Get address of a people
112	*
113	* @param $people_id
114	*
115	* @return mixed
116	*/
117	function erp_acct_get_people_address( $people_id ) {
118	global $wpdb;
119
120	$row = [];
121
122	$row = $wpdb->get_row(
123	$wpdb->prepare(
124	"SELECT street_1, street_2, city, state, postal_code, country FROM {$wpdb->prefix}erp_peoples WHERE id = %d",
125	$people_id
126	),
127	ARRAY_A
128	);
129
130	return $row;
131	}
132
133	/**
134	* Format people address
135	*/
136	function erp_acct_format_people_address( $address = [] ) {
137	$add = '';
138
139	$keys = array_keys( $address );
140	$values = array_values( $address );
141
142	for ( $idx = 0; $idx < count( $address ); $idx++ ) {
143	$add .= $keys[ $idx ] . ': ' . $values[ $idx ] . '; ';
144	}
145
146	return $add;
147	}
148
149	/**
150	* Get all transactions
151	*
152	* @return mixed
153	*/
154	function erp_acct_get_people_transactions( $args = [] ) {
155	global $wpdb;
156
157	$defaults = [
158	'number' => 20,
159	'offset' => 0,
160	'order' => 'ASC',
161	'count' => false,
162	's' => '',
163	];
164
165	$args = wp_parse_args( $args, $defaults );
166
167	$limit = '';
168
169	$where = '';
170
171	if ( ! empty( $args['people_id'] ) ) {
172	$where .= " AND people.people_id = {$args['people_id']} ";
173	}
174
175	if ( ! empty( $args['start_date'] ) ) {
176	$where .= " AND people.trn_date BETWEEN '{$args['start_date']}' AND '{$args['end_date']}'";
177	}
178
179	if ( empty( $args['end_date'] ) ) {
180	$args['end_date'] = date( 'Y-m-d', strtotime( 'last day of this month' ) );
181	}
182
183	if ( '-1' === $args['number'] ) {
184	$limit = "LIMIT {$args['number']} OFFSET {$args['offset']}";
185	}
186
187	$sql = 'SELECT';
188
189	if ( $args['count'] ) {
190	$sql .= ' COUNT( DISTINCT people.voucher_no ) AS total_number';
191	} else {
192	$sql .= '
193	voucher.id as voucher_no,
194	people.people_id,
195	people.voucher_no,
196	people.trn_date,
197	people.debit,
198	people.credit,
199	people.particulars,
200	people.created_at';
201	}
202
203	$sql .= " FROM {$wpdb->prefix}erp_acct_voucher_no AS voucher
204	INNER JOIN {$wpdb->prefix}erp_acct_people_trn_details AS people ON voucher.id = people.voucher_no
205	{$where} ORDER BY people.trn_date {$args['order']} {$limit}";
206
207	if ( $args['count'] ) {
208	$wpdb->get_results( $sql );
209
210	return $wpdb->num_rows;
211	}
212
213	$results = $wpdb->get_results( $sql, ARRAY_A );
214
215	$total = erp_acct_get_people_opening_balance( $args );
216	$o_balance = erp_acct_get_people_opening_balance( $args );
217	$dr_total = 0;
218	$cr_total = 0;
219
220	if ( $o_balance > 0 ) {
221	$dr_total = (float) $o_balance;
222	$temp = $o_balance . ' Dr';
223	} else {
224	$cr_total = (float) $o_balance;
225	$temp = $o_balance . ' Cr';
226	}
227
228	array_unshift(
229	$results,
230	[
231	'voucher_no' => null,
232	'particulars' => 'Opening Balance',
233	'people_id' => null,
234	'trn_no' => null,
235	'trn_date' => null,
236	'created_at' => null,
237	'debit' => null,
238	'credit' => null,
239	'balance' => $o_balance,
240	]
241	);
242
243	for ( $idx = 0; $idx < count( $results ); $idx++ ) {
244	if ( 0 == $idx ) {
245	$results[ $idx ]['balance_val'] = 0;
246	continue;
247	}
248	$dr_total += (float) $results[ $idx ]['debit'];
249	$cr_total += (float) $results[ $idx ]['credit'];
250	$balance = (float) $results[ $idx - 1 ]['balance_val'] + (float) $results[ $idx ]['debit'] - (float) $results[ $idx ]['credit'];
251
252	if ( $balance >= 0 ) {
253	$results[ $idx ]['balance_val'] = $balance;
254	$results[ $idx ]['balance'] = erp_get_currency_symbol( erp_get_currency() ) . abs( (float) $results[ $idx ]['balance_val'] ) . ' Dr';
255	} else {
256	$results[ $idx ]['balance_val'] = $balance;
257	$results[ $idx ]['balance'] = erp_get_currency_symbol( erp_get_currency() ) . abs( (float) $results[ $idx ]['balance_val'] ) . ' Cr';
258	}
259	$total = $balance;
260	}
261
262	$results[0]['balance'] = $total;
263
264	array_push(
265	$results,
266	[
267	'voucher_no' => null,
268	'particulars' => 'Total',
269	'people_id' => null,
270	'trn_no' => null,
271	'trn_date' => null,
272	'created_at' => null,
273	'debit' => $dr_total,
274	'credit' => $cr_total,
275	'balance' => null,
276	]
277	);
278
279	return $results;
280	}
281
282	/**
283	* Get opening balance
284	*
285	* @param array $args
286	*
287	* @return mixed
288	*/
289	function erp_acct_get_people_opening_balance( $args = [] ) {
290	global $wpdb;
291
292	$defaults = [
293	'number' => 20,
294	'offset' => 0,
295	'order' => 'ASC',
296	'count' => false,
297	's' => '',
298	];
299
300	$args = wp_parse_args( $args, $defaults );
301
302	$where = '';
303
304	if ( ! empty( $args['people_id'] ) ) {
305	$where .= " WHERE people_id = {$args['people_id']} ";
306	}
307
308	if ( ! empty( $args['start_date'] ) ) {
309	$where .= " AND trn_date < '{$args['start_date']}'";
310	} else {
311	$args['start_date'] = date( 'Y-m-d', strtotime( 'first day of january this year' ) );
312	$where .= " AND trn_date < '{$args['start_date']}'";
313	}
314
315	$sql = "SELECT SUM(debit - credit) AS opening_balance FROM {$wpdb->prefix}erp_acct_people_trn_details {$where}";
316
317	$result = $wpdb->get_row( $sql, ARRAY_A );
318
319	return isset( $result['opening_balance'] ) ? $result['opening_balance'] : 0;
320	}
321
322	/**
323	* Get People type by people id
324	*
325	* @param $people_id
326	*
327	* @return mixed
328	*/
329	function erp_acct_get_people_type_by_id( $people_id ) {
330	global $wpdb;
331
332	$row = $wpdb->get_row( $wpdb->prepare( "SELECT people_types_id FROM {$wpdb->prefix}erp_people_type_relations WHERE people_id = %d LIMIT 1", $people_id ) );
333
334	return erp_acct_get_people_type_by_type_id( $row->people_types_id );
335	}
336
337	/**
338	* Get people type by type id
339	*
340	* @param $type_id
341	*
342	* @return mixed
343	*/
344	function erp_acct_get_people_type_by_type_id( $type_id ) {
345	global $wpdb;
346
347	$row = $wpdb->get_row( $wpdb->prepare( "SELECT name FROM {$wpdb->prefix}erp_people_types WHERE id = %d LIMIT 1", $type_id ) );
348
349	return $row->name;
350	}
351
352	/**
353	* Get people id by user id
354	*
355	* @return mixed
356	*/
357	function erp_acct_get_people_id_by_user_id( $user_id ) {
358	global $wpdb;
359
360	$row = $wpdb->get_row( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}erp_peoples WHERE user_id = %d LIMIT 1", $user_id ) );
361
362	return $row->id;
363	}
364
365	/**
366	* Get people id by people_id
367	*
368	* @return mixed
369	*/
370	function erp_acct_get_people_name_by_people_id( $people_id ) {
371	global $wpdb;
372
373	$row = $wpdb->get_row( $wpdb->prepare( "SELECT first_name, last_name FROM {$wpdb->prefix}erp_peoples WHERE id = %d LIMIT 1", $people_id ) );
374
375	return $row->first_name . ' ' . $row->last_name;
376	}
377
378	/**
379	* Checks if an employee is people
380	*
381	* @param $user_id
382	*
383	* @return bool
384	*/
385	function erp_acct_is_employee_people( $user_id ) {
386	global $wpdb;
387
388	if ( ! $user_id ) {
389	return false;
390	}
391
392	$res = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(1) FROM {$wpdb->prefix}erp_peoples WHERE user_id = %d", $user_id ) );
393
394	if ( '1' === $res ) {
395	return true;
396	}
397
398	return false;
399	}
400
401	/**
402	* Get $user_id by $people_id
403	*
404	* @param $people_id
405	*
406	* @return mixed
407	*/
408	function erp_acct_get_user_id_by_people_id( $people_id ) {
409	global $wpdb;
410
411	$row = $wpdb->get_row( $wpdb->prepare( "SELECT user_id FROM {$wpdb->prefix}erp_peoples WHERE id = %d LIMIT 1", $people_id ) );
412
413	return $row->user_id;
414	}
415
416	/**
417	* Get Customer or Vendors
418	*/
419	function erp_acct_get_accounting_people( $args = [] ) {
420	global $wpdb;
421
422	$defaults = [
423	'type' => 'all',
424	'number' => 20,
425	'offset' => 0,
426	'orderby' => 'id',
427	'order' => 'DESC',
428	'trashed' => false,
429	'meta_query' => [],
430	'count' => false,
431	'life_stage' => '',
432	'include' => [],
433	'exclude' => [],
434	's' => '',
435	'no_object' => false,
436	];
437	$args = wp_parse_args( $args, $defaults );
438
439	$people_type = is_array( $args['type'] ) ? implode( '-', $args['type'] ) : $args['type'];
440	$cache_key = 'erp-people-' . $people_type . '-' . md5( serialize( $args ) );
441	$items = wp_cache_get( $cache_key, 'erp' );
442	$pep_tb = $wpdb->prefix . 'erp_peoples';
443	$pepmeta_tb = $wpdb->prefix . 'erp_peoplemeta';
444	$types_tb = $wpdb->prefix . 'erp_people_types';
445	$type_rel_tb = $wpdb->prefix . 'erp_people_type_relations';
446
447	if ( false === $items ) {
448	extract( $args );
449
450	$sql = [];
451	$trashed_sql = $trashed ? '`deleted_at` is not null' : '`deleted_at` is null';
452
453	if ( is_array( $type ) ) {
454	$type_sql = "and `name` IN ( '" . implode( "','", $type ) . "' )";
455	} else {
456	$type_sql = ( 'all' !== $type ) ? "and `name` = '" . $type . "'" : '';
457	}
458
459	$wrapper_select = 'SELECT people.*, ';
460
461	$sql['select'][] = "GROUP_CONCAT( DISTINCT t.name SEPARATOR ',') AS types";
462	$sql['join'][] = "LEFT JOIN $type_rel_tb AS r ON people.id = r.people_id LEFT JOIN $types_tb AS t ON r.people_types_id = t.id";
463	$sql_from_tb = "FROM $pep_tb AS people";
464	$sql_people_type = "where ( select count(*) from $types_tb
465	inner join $type_rel_tb
466	on $types_tb.`id` = $type_rel_tb.`people_types_id`
467	where $type_rel_tb.`people_id` = people.`id` $type_sql and $trashed_sql
468	) >= 1";
469	$sql['where'] = [ '' ];
470
471	$sql_group_by = 'GROUP BY `people`.`id`';
472	$sql_order_by = "ORDER BY $orderby $order";
473
474	// Check if want all data without any pagination
475	$sql_limit = ( '-1' !== $number && ! $count ) ? "LIMIT $number OFFSET $offset" : '';
476
477	if ( $meta_query ) {
478	$sql['join'][] = "LEFT JOIN $pepmeta_tb as people_meta on people.id = people_meta.`erp_people_id`";
479
480	$meta_key = isset( $meta_query['meta_key'] ) ? $meta_query['meta_key'] : '';
481	$meta_value = isset( $meta_query['meta_value'] ) ? $meta_query['meta_value'] : '';
482	$compare = isset( $meta_query['compare'] ) ? $meta_query['compare'] : '=';
483
484	$sql['where'][] = "AND people_meta.meta_key='$meta_key' and people_meta.meta_value='$meta_value'";
485	}
486
487	// Check if the row want to search
488	if ( ! empty( $s ) ) {
489	$search_like = '%' . $wpdb->esc_like( $s ) . '%';
490	$words = explode( ' ', $s );
491
492	if ( $type == 'customer' \|\| $type == 'vendor' ) {
493	if ( defined( 'DOING_AJAX' ) && DOING_AJAX ) {
494	if ( $type === 'customer' ) {
495	$sql['where'][] = $wpdb->prepare(
496	'AND ( people.first_name ) LIKE %s OR ' .
497	'( people.last_name ) LIKE %s',
498	[ $search_like, $search_like ]
499	);
500	} else {
501	$sql['where'][] = $wpdb->prepare( 'AND ( people.company ) LIKE %s', [ $search_like ] );
502	}
503	} else {
504	$sql['where'][] = $wpdb->prepare(
505	'AND ( people.first_name ) LIKE %s OR ' .
506	'( people.last_name ) LIKE %s OR ' .
507	'( people.email ) LIKE %s OR ' .
508	'( people.company ) LIKE %s',
509	[ $search_like, $search_like, $search_like, $search_like ]
510	);
511	}
512	} elseif ( is_array( $type ) ) {
513	$sql['where'][] = $wpdb->prepare(
514	'AND ( people.first_name ) LIKE %s OR ' .
515	'( people.last_name ) LIKE %s',
516	[ $search_like, $search_like ]
517	);
518	}
519	}
520
521	// Check if args count true, then return total count customer according to above filter
522	if ( $count ) {
523	$sql_order_by = '';
524	$sql_group_by = '';
525	$wrapper_select = 'SELECT COUNT( DISTINCT people.id ) as total_number';
526	unset( $sql['select'][0] );
527	}
528
529	$sql = apply_filters( 'erp_get_people_pre_query', $sql, $args );
530
531	$post_where_queries = '';
532
533	if ( ! empty( $sql['post_where_queries'] ) ) {
534	$post_where_queries = 'AND ( 1 = 1 '
535	. implode( ' ', $sql['post_where_queries'] )
536	. ' )';
537	}
538
539	$final_query = $wrapper_select . ' '
540	. implode( ' ', $sql['select'] ) . ' '
541	. $sql_from_tb . ' '
542	. implode( ' ', $sql['join'] ) . ' '
543	. $sql_people_type . ' '
544	. 'AND ( 1=1 '
545	. implode( ' ', $sql['where'] ) . ' '
546	. ' )'
547	. $post_where_queries
548	. $sql_group_by . ' '
549	. $sql_order_by . ' '
550	. $sql_limit;
551
552	if ( $count ) {
553	// Only filtered total count of people
554	$items = $wpdb->get_var( apply_filters( 'erp_get_people_total_count_query', $final_query, $args ) );
555	} else {
556	// Fetch results from people table
557	$results = $wpdb->get_results( apply_filters( 'erp_get_people_total_query', $final_query, $args ), ARRAY_A );
558	array_walk(
559	$results,
560	function ( &$results ) {
561	$results['types'] = explode( ',', $results['types'] );
562	}
563	);
564
565	$items = ( $no_object ) ? $results : erp_array_to_object( $results );
566	}
567	wp_cache_set( $cache_key, $items, 'erp' );
568	}
569
570	return $items;
571	}
572
573	/**
574	* Check if transaction associated with this people
575	*
576	* @param int $id
577	*/
578	function erp_acct_check_associated_tranasaction( $people_id ) {
579	global $wpdb;
580
581	return $wpdb->get_var(
582	$wpdb->prepare(
583	"SELECT id FROM {$wpdb->prefix}erp_acct_people_trn_details WHERE people_id = %d",
584	$people_id
585	)
586	);
587	}
588