class-wcml-reports.php — WPML Multilingual & Multicurrency for WooCommerce 5.5.7

woocommerce-multilingual / inc / class-wcml-reports.php

woocommerce-multilingual / inc Last commit date

class-wcml-reports.php

290 lines

1	<?php
2
3	use WCML\Utilities\DB;
4
5	class WCML_Reports{
6
7	public $tab;
8	public $report;
9
10	public function __construct(){
11
12	add_action('init', [ $this, 'init' ] );
13
14	}
15
16	public function init(){
17
18	if( isset($_GET['page']) && $_GET['page']==='wc-reports' ) {
19
20	$this->tab = $_GET['tab'] ?? 'orders';
21	$this->report = $_GET['report'] ?? '';
22
23	add_filter( 'woocommerce_reports_get_order_report_query', [ $this, 'filter_reports_query' ], 0 );
24
25	if ( $this->tab==='orders' && $this->report==='sales_by_product' ) {
26	add_filter( 'woocommerce_reports_get_order_report_data', [ $this, 'combine_report_by_languages' ] );
27	}
28	if ( $this->tab==='orders' && $this->report==='sales_by_category' ) {
29	add_filter( 'woocommerce_report_sales_by_category_get_products_in_category', [
30	$this,
31	'use_categories_in_all_languages'
32	], 10, 2 );
33	}
34	}
35
36	add_filter( 'woocommerce_report_most_stocked_query_from', [ $this, 'filter_reports_stock_query' ] );
37	add_filter( 'woocommerce_report_out_of_stock_query_from', [ $this, 'filter_reports_stock_query' ] );
38	add_filter( 'woocommerce_report_low_in_stock_query_from', [ $this, 'filter_reports_stock_query' ] );
39
40	}
41
42	public function filter_reports_query($query){
43	global $wpdb, $sitepress;
44
45	$current_language = $sitepress->get_current_language();
46	$active_languages = $sitepress->get_active_languages();
47
48	if($this->tab==='orders' && $this->report==='sales_by_product'){
49
50	$sparkline_query = strpos( $query[ 'select'], 'sparkline_value' ) !== false;
51
52	if(
53
54	$sparkline_query \|\|
55	isset( $query[ 'order_by' ] ) && ( $query[ 'order_by' ]==='ORDER BY order_item_qty DESC' \|\| $query[ 'order_by' ]==='ORDER BY order_item_total DESC' )
56
57	){
58
59
60	$query[ 'select' ] .= " , order_language.meta_value AS order_language , translations.trid";
61
62	$query[ 'join' ] .= " LEFT JOIN {$wpdb->postmeta} order_language ON posts.ID = order_language.post_id";
63	$query[ 'where' ] .= " AND order_language.meta_key = 'wpml_language' ";
64
65	$query[ 'join' ] .= " LEFT JOIN {$wpdb->prefix}icl_translations translations ON translations.element_id = order_item_meta__product_id.meta_value";
66	$query[ 'where' ] .= " AND translations.element_type IN ('post_product','post_product_variation') ";
67
68	if(!$sparkline_query){
69	$limit = (int) trim( str_replace( 'LIMIT ', '', $query['limit'] ) );
70	$query['limit'] = sprintf( ' LIMIT %d ', $limit * count( $active_languages ) );
71	}
72
73
74	if($sparkline_query){
75	preg_match("#order_item_meta__product_id\.meta_value = '([0-9]+)'#", $query[ 'where' ], $matches);
76	$product_id = $matches[1];
77	$post_type = get_post_type($product_id);
78	$trid = $sitepress->get_element_trid($product_id, 'post_'.$post_type);
79	$translations = $sitepress->get_element_translations($trid, 'post_'.$post_type, true);
80	$product_ids = [];
81	foreach($translations as $translation){
82	$product_ids[] = $translation->element_id;
83	}
84
85	$query[ 'where' ] = str_replace( "order_item_meta__product_id.meta_value = '{$product_id}'", "order_item_meta__product_id.meta_value IN (" . DB::prepareIn( array_filter( $product_ids ), '%d' ) . ")", $query[ 'where' ] );
86	}
87
88	$query[ 'select' ] .= ', translations.language_code AS language_code_' . esc_sql( str_replace('-', '_', $current_language) );
89
90	}elseif(
91	$query[ 'select' ]==='SELECT SUM( order_item_meta__line_total.meta_value) as order_item_amount' \|\|
92	$query[ 'select' ]==='SELECT SUM( order_item_meta__qty.meta_value) as order_item_count' \|\|
93	$query[ 'select' ]==='SELECT SUM( order_item_meta__qty.meta_value) as order_item_count, posts.post_date as post_date, order_item_meta__product_id.meta_value as product_id' \|\|
94	$query[ 'select' ]==='SELECT SUM( order_item_meta__line_total.meta_value) as order_item_amount, posts.post_date as post_date, order_item_meta__product_id.meta_value as product_id'
95
96	){
97	preg_match("#order_item_meta__product_id_array\.meta_value IN $([^$]+)\)#", $query[ 'where' ], $matches);
98	$product_ids = [];
99	$exp = array_map('trim', explode(',', $matches[1]));
100	foreach($exp as $e){
101	$product_ids[] = trim($e, "'");
102	}
103	$all_product_ids = [];
104	foreach($product_ids as $product_id){
105	$post_type = get_post_type($product_id);
106	$trid = $sitepress->get_element_trid($product_id, 'post_'.$post_type);
107	$translations = $sitepress->get_element_translations($trid, 'post_'.$post_type, true);
108	foreach($translations as $translation){
109	$all_product_ids[] = $translation->element_id;
110	}
111	}
112
113	$query[ 'where' ] = preg_replace( "#order_item_meta__product_id_array\.meta_value IN $([^$]+)\)#", "order_item_meta__product_id_array.meta_value IN (" . DB::prepareIn( array_filter( $all_product_ids ), '%d' ) . ')', $query[ 'where' ] );
114	}
115
116	}
117
118	return $query;
119	}
120
121
122	public function combine_report_by_languages($results){
123	global $sitepress;
124
125	if(is_array($results) && isset($results['0']->order_item_qty)){
126	$mode = 'top_sellers';
127	}elseif(is_array($results) && isset($results['0']->order_item_total)){
128	$mode = 'top_earners';
129	}elseif(isset($results['0']->sparkline_value)){
130	$mode = 'top_sellers_spark';
131	}else{
132	return $results;
133	}
134
135	if(!isset($results['0']->trid)) return $results;
136
137	$current_language = $sitepress->get_current_language();
138
139	$combined_results = [];
140
141	foreach($results as $row){
142
143	switch($mode){
144	case 'top_sellers':
145	case 'top_earners':
146	$key = $row->trid;
147	break;
148	case 'top_sellers_spark':
149	$key = $row->trid . '#' . substr($row->post_date, 0, 10);
150	break;
151	default:
152	$key = null;
153	}
154
155	if($key && $row->order_language===$current_language){
156	$combined_results[$key] = $row;
157	}
158	}
159
160	foreach($results as $row){
161
162	if($row->order_language != $current_language){
163
164	switch($mode){
165	case 'top_sellers':
166	case 'top_earners':
167	$key = $row->trid;
168	break;
169	case 'top_sellers_spark':
170	$key = $row->trid . '#' . substr($row->post_date, 0, 10);
171	break;
172	default:
173	$key = null;
174	}
175
176	if($key && isset($combined_results[$key])){
177
178	switch($mode){
179	case 'top_sellers':
180	$combined_results[$key]->order_item_qty += $row->order_item_qty;
181	break;
182	case 'top_earners':
183	$combined_results[$key]->order_item_total += $row->order_item_total;
184	break;
185	case 'top_sellers_spark':
186	$combined_results[$key]->sparkline_value += $row->sparkline_value;
187	break;
188
189	}
190
191	}else{
192
193	$default_product_id = apply_filters( 'wpml_object_id',$row->product_id, 'product', false, $current_language);
194
195	if($default_product_id){
196	$combined_results[$key] = new stdClass();
197	$combined_results[$key]->product_id = $default_product_id;
198
199	switch($mode){
200	case 'top_sellers':
201	$combined_results[$key]->order_item_qty = $row->order_item_qty;
202	break;
203	case 'top_earners':
204	$combined_results[$key]->order_item_total = $row->order_item_total;
205	break;
206	case 'top_sellers_spark':
207	$combined_results[$key]->sparkline_value = $row->sparkline_value;
208	$combined_results[$key]->post_date = $row->post_date;
209	break;
210	}
211
212	}
213
214	}
215
216
217	}
218
219	}
220
221	switch($mode){
222	case 'top_sellers':
223	usort($combined_results, [ __CLASS__, 'order_by_quantity' ] );
224	break;
225	case 'top_earners':
226	usort($combined_results, [ __CLASS__, 'order_by_total' ] );
227	break;
228	case 'top_sellers_spark':
229	break;
230
231
232	}
233
234	foreach($combined_results as $k => $row){
235	unset($combined_results[$k]->trid, $combined_results[$k]->order_language);
236	}
237
238
239	return $combined_results;
240	}
241
242	private static function order_by_quantity( $a, $b ) {
243	return $b->order_item_qty - $a->order_item_qty;
244	}
245
246	private static function order_by_total( $a, $b ) {
247	return $b->order_item_total - $a->order_item_total;
248	}
249
250	public function filter_reports_stock_query( $query_from ){
251	global $wpdb, $sitepress;
252
253	$current_language = $sitepress->get_current_language();
254
255	if( $current_language !== 'all' ){
256	$query_from = preg_replace("/WHERE/",
257	"LEFT JOIN {$wpdb->prefix}icl_translations AS t
258	ON posts.ID = t.element_id
259	WHERE", $query_from);
260
261	$query_from .= " AND t.element_type IN ( 'post_product', 'post_product_variation' ) AND t.language_code = '".$current_language."'";
262	}
263
264
265	return $query_from;
266	}
267
268	public function use_categories_in_all_languages( $product_ids, $category_id ) {
269	global $woocommerce_wpml, $sitepress;
270
271	$category_term = $woocommerce_wpml->terms->wcml_get_term_by_id( $category_id, 'product_cat' );
272
273	if ( ! is_wp_error( $category_term ) ) {
274	$trid = $sitepress->get_element_trid( $category_term->term_taxonomy_id, 'tax_product_cat' );
275	$translations = $sitepress->get_element_translations( $trid, 'tax_product_cat', true );
276
277	foreach ( $translations as $translation ) {
278	if ( $translation->term_id != $category_id ) {
279	$term_ids = get_term_children( $translation->term_id, 'product_cat' );
280	$term_ids[] = $translation->term_id;
281	$product_ids = array_merge( array_unique( $product_ids ), get_objects_in_term( $term_ids, 'product_cat' ) );
282	}
283	}
284	}
285
286	return $product_ids;
287	}
288
289	}
290