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

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

323 lines 9.3 KB

Raw Download Zip

1	<?php
2
3	if ( ! defined( 'ABSPATH' ) ) {
4	exit; // Exit if accessed directly
5	}
6
7	/**
8	* Get all products
9	*
10	* @return mixed
11	*/
12	function erp_acct_get_all_products( $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 ( -1 !== $args['number'] ) {
30	$limit = "LIMIT {$args['number']} OFFSET {$args['offset']}";
31	}
32
33	$sql = 'SELECT';
34
35	if ( $args['count'] ) {
36	$sql .= ' COUNT( product.id ) as total_number';
37	} else {
38	$sql .= " product.id,
39	product.name,
40	product.product_type_id,
41	product.cost_price,
42	product.sale_price,
43	product.tax_cat_id,
44	people.id AS vendor,
45	CONCAT(people.first_name, ' ', people.last_name) AS vendor_name,
46	cat.id AS category_id,
47	cat.name AS cat_name,
48	product_type.name AS product_type_name";
49	}
50
51	$sql .= " FROM {$wpdb->prefix}erp_acct_products AS product
52	LEFT JOIN {$wpdb->prefix}erp_peoples AS people ON product.vendor = people.id
53	LEFT JOIN {$wpdb->prefix}erp_acct_product_categories AS cat ON product.category_id = cat.id
54	LEFT JOIN {$wpdb->prefix}erp_acct_product_types AS product_type ON product.product_type_id = product_type.id
55	WHERE product.product_type_id<>3 ORDER BY product.{$args['orderby']} {$args['order']} {$limit}";
56
57	if ( $args['count'] ) {
58	return $wpdb->get_var( $sql );
59	}
60
61	return $wpdb->get_results( $sql, ARRAY_A );
62	}
63
64	/**
65	* Get an single product
66	*
67	* @param $product_no
68	*
69	* @return mixed
70	*/
71	function erp_acct_get_product( $product_id ) {
72	global $wpdb;
73
74	$row = $wpdb->get_row(
75	"SELECT
76	product.id,
77	product.name,
78	product.product_type_id,
79	product.cost_price,
80	product.sale_price,
81	product.tax_cat_id,
82
83	people.id AS vendor,
84	CONCAT(people.first_name, ' ', people.last_name) AS vendor_name,
85
86	cat.id AS category_id,
87	cat.name AS cat_name,
88
89	product_type.name AS product_type_name
90
91	FROM {$wpdb->prefix}erp_acct_products AS product
92	LEFT JOIN {$wpdb->prefix}erp_peoples AS people ON product.vendor = people.id
93	LEFT JOIN {$wpdb->prefix}erp_acct_product_categories AS cat ON product.category_id = cat.id
94	LEFT JOIN {$wpdb->prefix}erp_acct_product_types AS product_type ON product.product_type_id = product_type.id WHERE product.id = {$product_id} LIMIT 1",
95	ARRAY_A
96	);
97
98	return $row;
99	}
100
101	/**
102	* Insert product data
103	*
104	* @param $data
105	*
106	* @return int
107	*/
108	function erp_acct_insert_product( $data ) {
109	global $wpdb;
110
111	$created_by = get_current_user_id();
112	$data['created_at'] = date( 'Y-m-d H:i:s' );
113	$data['created_by'] = $created_by;
114	$product_id = null;
115
116	try {
117	$wpdb->query( 'START TRANSACTION' );
118	$product_data = erp_acct_get_formatted_product_data( $data );
119
120	$wpdb->insert(
121	$wpdb->prefix . 'erp_acct_products',
122	[
123	'name' => $product_data['name'],
124	'product_type_id' => $product_data['product_type_id'],
125	'category_id' => $product_data['category_id'],
126	'tax_cat_id' => $product_data['tax_cat_id'],
127	'vendor' => $product_data['vendor'],
128	'cost_price' => $product_data['cost_price'],
129	'sale_price' => $product_data['sale_price'],
130	'created_at' => $product_data['created_at'],
131	'created_by' => $product_data['created_by'],
132	'updated_at' => $product_data['updated_at'],
133	'updated_by' => $product_data['updated_by'],
134	]
135	);
136
137	$product_id = $wpdb->insert_id;
138
139	$wpdb->query( 'COMMIT' );
140	} catch ( Exception $e ) {
141	$wpdb->query( 'ROLLBACK' );
142
143	return new WP_error( 'product-exception', $e->getMessage() );
144	}
145
146	return erp_acct_get_product( $product_id );
147	}
148
149	/**
150	* Update product data
151	*
152	* @param $data
153	*
154	* @return int
155	*/
156	function erp_acct_update_product( $data, $id ) {
157	global $wpdb;
158
159	$updated_by = get_current_user_id();
160	$data['updated_at'] = date( 'Y-m-d H:i:s' );
161	$data['updated_by'] = $updated_by;
162
163	try {
164	$wpdb->query( 'START TRANSACTION' );
165	$product_data = erp_acct_get_formatted_product_data( $data );
166
167	$wpdb->update(
168	$wpdb->prefix . 'erp_acct_products',
169	[
170	'name' => $product_data['name'],
171	'product_type_id' => $product_data['product_type_id'],
172	'category_id' => $product_data['category_id'],
173	'tax_cat_id' => $product_data['tax_cat_id'],
174	'vendor' => $product_data['vendor'],
175	'cost_price' => $product_data['cost_price'],
176	'sale_price' => $product_data['sale_price'],
177	'created_at' => $product_data['updated_at'],
178	'created_by' => $product_data['updated_by'],
179	'updated_at' => $product_data['updated_at'],
180	'updated_by' => $product_data['updated_by'],
181	],
182	[
183	'id' => $id,
184	]
185	);
186
187	$wpdb->query( 'COMMIT' );
188	} catch ( Exception $e ) {
189	$wpdb->query( 'ROLLBACK' );
190
191	return new WP_error( 'product-exception', $e->getMessage() );
192	}
193
194	return erp_acct_get_product( $id );
195	}
196
197	/**
198	* Get formatted product data
199	*
200	* @param $data
201	* @param $voucher_no
202	*
203	* @return mixed
204	*/
205	function erp_acct_get_formatted_product_data( $data ) {
206	$product_data['name'] = ! empty( $data['name'] ) ? $data['name'] : 1;
207	$product_data['product_type_id'] = ! empty( $data['product_type_id'] ) ? $data['product_type_id'] : 1;
208	$product_data['category_id'] = ! empty( $data['category_id'] ) ? $data['category_id'] : 0;
209	$product_data['tax_cat_id'] = ! empty( $data['tax_cat_id'] ) ? $data['tax_cat_id'] : 0;
210	$product_data['vendor'] = ! empty( $data['vendor'] ) ? $data['vendor'] : '';
211	$product_data['cost_price'] = ! empty( $data['cost_price'] ) ? $data['cost_price'] : '';
212	$product_data['sale_price'] = ! empty( $data['sale_price'] ) ? $data['sale_price'] : '';
213	$product_data['created_at'] = ! empty( $data['created_at'] ) ? $data['created_at'] : '';
214	$product_data['created_by'] = ! empty( $data['created_by'] ) ? $data['created_by'] : '';
215	$product_data['updated_at'] = ! empty( $data['updated_at'] ) ? $data['updated_at'] : '';
216	$product_data['updated_by'] = ! empty( $data['updated_by'] ) ? $data['updated_by'] : '';
217
218	return $product_data;
219	}
220
221	/**
222	* Delete an product
223	*
224	* @param $product_no
225	*
226	* @return int
227	*/
228	function erp_acct_delete_product( $product_id ) {
229	global $wpdb;
230
231	$wpdb->delete( $wpdb->prefix . 'erp_acct_products', [ 'id' => $product_id ] );
232
233	return $product_id;
234	}
235
236	/**
237	* Get product types
238	*
239	* @param $product_id
240	*
241	* @return int
242	*/
243	function erp_acct_get_product_types() {
244	global $wpdb;
245
246	$types = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}erp_acct_product_types" );
247
248	return apply_filters( 'erp_acct_product_types', $types );
249	}
250
251	/**
252	* Get product type id by product id
253	*
254	* @param $product_id
255	*
256	* @return int
257	*/
258	function erp_acct_get_product_type_id_by_product_id( $product_id ) {
259	global $wpdb;
260
261	$type_id = $wpdb->get_var( $wpdb->prepare( "SELECT product_type_id FROM {$wpdb->prefix}erp_acct_products WHERE id = %d", $product_id ) );
262
263	return $type_id;
264	}
265
266	/**
267	* Get all products of a vendor
268	*
269	* @return mixed
270	*/
271	function erp_acct_get_vendor_products( $args = [] ) {
272	global $wpdb;
273
274	$defaults = [
275	'number' => 20,
276	'offset' => 0,
277	'orderby' => 'id',
278	'order' => 'DESC',
279	'count' => false,
280	's' => '',
281	'vendor' => 0,
282	];
283
284	$args = wp_parse_args( $args, $defaults );
285
286	$where = '';
287	$limit = '';
288
289	if ( -1 !== $args['number'] ) {
290	$limit = "LIMIT {$args['number']} OFFSET {$args['offset']}";
291	}
292
293	$sql = 'SELECT';
294
295	if ( $args['count'] ) {
296	$sql .= ' COUNT( product.id ) as total_number';
297	} else {
298	$sql .= " product.id,
299	product.name,
300	product.product_type_id,
301	product.cost_price,
302	product.sale_price,
303	product.tax_cat_id,
304	product.vendor,
305	CONCAT(people.first_name, ' ', people.last_name) AS vendor_name,
306	cat.id AS category_id,
307	cat.name AS cat_name,
308	product_type.name AS product_type_name";
309	}
310
311	$sql .= " FROM {$wpdb->prefix}erp_acct_products AS product
312	LEFT JOIN {$wpdb->prefix}erp_peoples AS people ON product.vendor = people.id
313	LEFT JOIN {$wpdb->prefix}erp_acct_product_categories AS cat ON product.category_id = cat.id
314	LEFT JOIN {$wpdb->prefix}erp_acct_product_types AS product_type ON product.product_type_id = product_type.id
315	WHERE people.id={$args['vendor']} AND product.product_type_id<>3 ORDER BY product.{$args['orderby']} {$args['order']} {$limit}";
316
317	if ( $args['count'] ) {
318	return $wpdb->get_var( $sql );
319	}
320
321	return $wpdb->get_results( $sql, ARRAY_A );
322	}
323