desktop-mode / includes / my-wordpress / integrations / woocommerce-relations.php

woocommerce-relations.php in OpenStation: Desktop Windows, Dock & Virtual Desktops for WP Admin 1.1.10, at includes/my-wordpress/integrations/woocommerce-relations.php

1,157 lines 38.4 KB

Raw Download Zip

1	<?php
2	/**
3	* OpenStation — My WordPress: WooCommerce × the relations layer.
4	*
5	* An order is the most connected object in WordPress and the least
6	* connected screen. It names a customer, some products and maybe a
7	* coupon, and every one of those is a dead end: WooCommerce prints the
8	* customer's name as text, the line items as text, the coupon as a
9	* token. To go from an order to the product it sold you go back to the
10	* catalogue and search for it.
11	*
12	* The shell already knows how to express that. Two surfaces, both
13	* public, neither WooCommerce-specific:
14	*
15	* 1. Content identity (`openstation_window_content_identity`) —
16	* what a window is showing, plus the objects it refers to. Two
17	* open windows whose identities meet get a drawn tie on the
18	* desktop. Open an order beside the product it sold and the line
19	* between them is the shell telling you they are the same story.
20	*
21	* 2. Related entities (`openstation_window_related_entities`) —
22	* the title bar's "Related" menu. One click from the order to the
23	* customer's profile, to any product on it, to the coupon that
24	* discounted it, each opening as its own window rather than
25	* navigating away from what you were reading.
26	*
27	* Both run inside the chromeless iframe — real admin context — so the
28	* relations resolve against live WooCommerce objects rather than
29	* against a URL we guessed at.
30	*
31	* Screens covered:
32	*
33	* - Order edit, both storages. High-Performance Order Storage moves
34	* the screen to `admin.php?page=wc-orders&action=edit&id=N`, where
35	* the built-in `post.php` detection can never see it; legacy
36	* storage lands on `post.php` and gets an identity already, but
37	* one with no links on it.
38	* - Product edit — categories, tags, reviews, and the media the
39	* built-in extractor already finds.
40	* - Coupon edit — the products and categories it is restricted to,
41	* which WooCommerce shows as bare token fields you have to click
42	* into to read.
43	* - User edit — a customer's orders, when the viewer may see them.
44	*
45	* Everything here is inert without WooCommerce.
46	*
47	* @package OpenStation
48	*/
49
50	defined( 'ABSPATH' ) \|\| exit;
51
52	/**
53	* How many line items / coupons one order announces.
54	*
55	* The relations engine caps a ref's whole `links` array at 64 and its
56	* `related` list at 64; a 200-line wholesale order would spend the
57	* entire budget on products and silently drop the customer. Bounded
58	* here so the trailing groups always survive.
59	*/
60	const OPENSTATION_WOO_RELATION_ITEM_CAP = 20;
61
62	/**
63	* How many orders the product and coupon groups list.
64	*/
65	const OPENSTATION_WOO_RELATION_ORDER_CAP = 10;
66
67	/**
68	* How many order-item rows to read to fill that list.
69	*
70	* The id lists come out of `woocommerce_order_items`, which holds
71	* refund rows alongside order rows — and refunds sort first there,
72	* since the query orders by descending id and a refund is created
73	* after the order it refunds. A `LIMIT 10` on a much-refunded product
74	* could therefore come back as ten refunds and no orders at all, and
75	* the group would render empty on the one product whose history a
76	* merchant most wants to read. Reading a few times the budget and
77	* stopping at the cap costs one bounded query.
78	*/
79	const OPENSTATION_WOO_RELATION_ORDER_CANDIDATES = 40;
80
81	/**
82	* Query flag marking a person-URL as a request for a particular
83	* view of that person rather than for the profile editor.
84	*
85	* Must stay equal to `OS_PERSON_VIEW_PARAM` in
86	* `src/native-url-remap.ts` — the URL is built here and read there,
87	* so the two ends have to agree on the literal.
88	*/
89	const OPENSTATION_PERSON_VIEW_PARAM = 'os_person_view';
90
91	/*
92	-------------------------------------------------------------------
93	* Screen resolution
94	* ----------------------------------------------------------------
95	*/
96
97	/**
98	* The order the current admin screen is editing, whichever storage
99	* the store uses.
100	*
101	* @return WC_Abstract_Order\|null
102	*/
103	function openstation_my_wordpress_woo_current_order() {
104	if ( ! openstation_my_wordpress_woo_active() ) {
105	return null;
106	}
107
108	$pagenow = isset( $GLOBALS['pagenow'] ) ? (string) $GLOBALS['pagenow'] : '';
109
110	// phpcs:disable WordPress.Security.NonceVerification.Recommended -- read-only identity harvest; the host admin page enforces capability + nonce.
111	$id = 0;
112	if ( 'admin.php' === $pagenow ) {
113	// HPOS. `wc-orders` for shop orders, `wc-orders--{type}` for
114	// custom order types (subscriptions and friends) — both are
115	// orders as far as the relations layer cares.
116	$page = isset( $_GET['page'] ) ? sanitize_key( wp_unslash( $_GET['page'] ) ) : '';
117	if ( 0 !== strpos( $page, 'wc-orders' ) ) {
118	return null;
119	}
120	$action = isset( $_GET['action'] ) ? sanitize_key( wp_unslash( $_GET['action'] ) ) : '';
121	if ( 'edit' !== $action ) {
122	return null;
123	}
124	$id = isset( $_GET['id'] ) ? absint( $_GET['id'] ) : 0;
125	} elseif ( 'post.php' === $pagenow ) {
126	// Legacy storage: orders are posts.
127	$id = isset( $_GET['post'] ) ? absint( $_GET['post'] ) : 0;
128	if ( $id > 0 && 'shop_order' !== get_post_type( $id ) ) {
129	return null;
130	}
131	}
132	// phpcs:enable WordPress.Security.NonceVerification.Recommended
133
134	if ( $id <= 0 ) {
135	return null;
136	}
137
138	$order = wc_get_order( $id );
139
140	return $order instanceof WC_Abstract_Order ? $order : null;
141	}
142
143	/**
144	* Whether the viewer may see this order at all.
145	*
146	* @return bool
147	*/
148	function openstation_my_wordpress_woo_can_read_orders() {
149	return true === openstation_my_wordpress_woo_orders_permission();
150	}
151
152	/**
153	* Whether an object read back from an order-item row is a purchase.
154	*
155	* Refunds keep their own line items in the same
156	* `woocommerce_order_items` tables, under the refund's id — so a
157	* lookup that asks those tables "which orders contain product X"
158	* answers with refund ids too, for any product that has ever been
159	* refunded. `WC_Order_Refund` extends `WC_Abstract_Order`, so the
160	* usual guard waves it through, and the next line asks it for
161	* `get_order_number()`: a `WC_Order` method the abstract base does
162	* not declare, and therefore a fatal on the product edit screen.
163	*
164	* Dropping refunds is also the truer answer. "Who bought this" and
165	* "where was this coupon used" are questions about purchases, and a
166	* refund is the undoing of one.
167	*
168	* Deliberately not `instanceof WC_Order`. The abstract base is the
169	* type every order class actually extends, including HPOS's overrides
170	* and whatever custom order type a store registers — testing against
171	* `WC_Order` has already been tried elsewhere in this integration and
172	* silently emptied lists on stores that use them. So this excludes the
173	* one known-hostile subclass and then asks the object directly for the
174	* accessors these lists call, which keeps an exotic order type that
175	* extends the base without them out of a fatal too.
176	*
177	* @param mixed $order Whatever `wc_get_order()` returned.
178	* @return bool
179	*/
180	function openstation_my_wordpress_woo_is_purchase( $order ) {
181	if ( ! $order instanceof WC_Abstract_Order ) {
182	return false;
183	}
184	if ( $order instanceof WC_Order_Refund ) {
185	return false;
186	}
187	return method_exists( $order, 'get_order_number' );
188	}
189
190	/**
191	* The content identity for WooCommerce's product-reviews screen when
192	* it is filtered to a single product.
193	*
194	* `edit.php?post_type=product&page=product-reviews&product_id=N`.
195	* The unfiltered all-reviews list stays identity-less, the same way
196	* core leaves the unfiltered comments list alone: a window showing
197	* everything belongs to nothing in particular.
198	*
199	* @return array\|null
200	*/
201	function openstation_my_wordpress_woo_reviews_identity() {
202	$pagenow = isset( $GLOBALS['pagenow'] ) ? (string) $GLOBALS['pagenow'] : '';
203	if ( 'edit.php' !== $pagenow ) {
204	return null;
205	}
206
207	// phpcs:disable WordPress.Security.NonceVerification.Recommended -- read-only identity harvest; the host admin page enforces capability + nonce.
208	$page = isset( $_GET['page'] ) ? sanitize_key( wp_unslash( $_GET['page'] ) ) : '';
209	if ( 'product-reviews' !== $page ) {
210	return null;
211	}
212	$product_id = isset( $_GET['product_id'] ) ? absint( $_GET['product_id'] ) : 0;
213	// phpcs:enable WordPress.Security.NonceVerification.Recommended
214
215	if ( $product_id <= 0 \|\| 'product' !== get_post_type( $product_id ) ) {
216	return null;
217	}
218	if ( ! current_user_can( 'edit_post', $product_id ) ) {
219	return null;
220	}
221
222	return array(
223	'type' => 'reviews',
224	'id' => $product_id,
225	/* translators: %s: product name. */
226	'label' => sprintf( __( 'Reviews of %s', 'desktop-mode' ), get_the_title( $product_id ) ),
227	'root' => array(
228	'type' => 'product',
229	'id' => $product_id,
230	),
231	);
232	}
233
234	/*
235	-------------------------------------------------------------------
236	* Content identity
237	* ----------------------------------------------------------------
238	*/
239
240	/**
241	* The objects an order refers to — its customer, its products, its
242	* coupons — as relation refs.
243	*
244	* Direction is `references` throughout: the order points at them. A
245	* product does not belong to an order (it outlives it), and a customer
246	* certainly doesn't, so `child` would be a lie the arrowheads would
247	* then tell on screen.
248	*
249	* @param WC_Abstract_Order $order Order.
250	* @return array[] Ref entries for the identity's `links` array.
251	*/
252	function openstation_my_wordpress_woo_order_refs( $order ) {
253	$links = array();
254	$seen = array();
255
256	$push = static function ( $type, $id ) use ( &$links, &$seen ) {
257	$id = (int) $id;
258	$key = $type . ':' . $id;
259	if ( $id <= 0 \|\| isset( $seen[ $key ] ) \|\| count( $links ) >= 64 ) {
260	return;
261	}
262	$seen[ $key ] = true;
263	$links[] = array(
264	'type' => $type,
265	'id' => $id,
266	);
267	};
268
269	// The customer. `user` is the type the shell's own user-edit
270	// screens announce, so the tie forms against a profile window
271	// opened from anywhere — not just from the shop.
272	$customer_id = method_exists( $order, 'get_customer_id' ) ? (int) $order->get_customer_id() : 0;
273	if ( $customer_id > 0 ) {
274	$push( 'user', $customer_id );
275	}
276
277	$items = 0;
278	foreach ( $order->get_items() as $item ) {
279	if ( $items >= OPENSTATION_WOO_RELATION_ITEM_CAP ) {
280	break;
281	}
282	$product_id = method_exists( $item, 'get_product_id' ) ? (int) $item->get_product_id() : 0;
283	if ( $product_id > 0 && 'product' === get_post_type( $product_id ) ) {
284	$push( 'product', $product_id );
285	++$items;
286	}
287	}
288
289	$coupons = 0;
290	foreach ( $order->get_items( 'coupon' ) as $line ) {
291	if ( $coupons >= OPENSTATION_WOO_RELATION_ITEM_CAP ) {
292	break;
293	}
294	$coupon = new WC_Coupon( $line->get_code() );
295	if ( $coupon->get_id() ) {
296	$push( 'shop_coupon', $coupon->get_id() );
297	++$coupons;
298	}
299	}
300
301	return $links;
302	}
303
304	/**
305	* The objects a coupon refers to — the products and categories it is
306	* restricted to.
307	*
308	* @param WC_Coupon $coupon Coupon.
309	* @return array[]
310	*/
311	function openstation_my_wordpress_woo_coupon_refs( $coupon ) {
312	$links = array();
313
314	foreach ( array_slice( (array) $coupon->get_product_ids(), 0, OPENSTATION_WOO_RELATION_ITEM_CAP ) as $product_id ) {
315	$product_id = (int) $product_id;
316	if ( $product_id > 0 && 'product' === get_post_type( $product_id ) ) {
317	$links[] = array(
318	'type' => 'product',
319	'id' => $product_id,
320	);
321	}
322	}
323
324	foreach ( array_slice( (array) $coupon->get_product_categories(), 0, OPENSTATION_WOO_RELATION_ITEM_CAP ) as $term_id ) {
325	$term_id = (int) $term_id;
326	$term = $term_id ? get_term( $term_id, 'product_cat' ) : null;
327	if ( $term instanceof WP_Term ) {
328	$links[] = array(
329	'type' => 'term/product_cat',
330	'id' => $term_id,
331	);
332	}
333	}
334
335	return $links;
336	}
337
338	/**
339	* Announce an identity for WooCommerce's own screens, and hang the
340	* shop's links off the identities the built-in detection already
341	* produces.
342	*
343	* @param array\|null $identity Identity so far.
344	* @param WP_Screen\|null $screen Current screen, when available.
345	* @return array\|null
346	*/
347	function openstation_my_wordpress_woo_content_identity( $identity, $screen ) {
348	unset( $screen );
349	if ( ! openstation_my_wordpress_woo_active() ) {
350	return $identity;
351	}
352
353	// 1. Order edit. Under HPOS there is no identity yet at all —
354	// `post.php` never runs — so this is the only place it can come
355	// from. Under legacy storage there IS one (the generic post
356	// branch), and it arrives with no links: an order's content is
357	// empty, so the hyperlink/media/term extractor finds nothing.
358	$order = openstation_my_wordpress_woo_current_order();
359	if ( $order && openstation_my_wordpress_woo_can_read_orders() ) {
360	$name = method_exists( $order, 'get_formatted_billing_full_name' )
361	? trim( $order->get_formatted_billing_full_name() )
362	: '';
363
364	$identity = array(
365	'type' => 'shop_order',
366	'id' => (int) $order->get_id(),
367	'label' => '' !== $name
368	? sprintf(
369	/* translators: 1: order number, 2: customer name. */
370	__( 'Order #%1$s · %2$s', 'desktop-mode' ),
371	$order->get_order_number(),
372	$name
373	)
374	: sprintf(
375	/* translators: %s: order number. */
376	__( 'Order #%s', 'desktop-mode' ),
377	$order->get_order_number()
378	),
379	);
380
381	$links = openstation_my_wordpress_woo_order_refs( $order );
382	if ( ! empty( $links ) ) {
383	$identity['links'] = $links;
384	}
385
386	return $identity;
387	}
388
389	// 2. The product-reviews screen, filtered to one product —
390	// `edit.php?post_type=product&page=product-reviews&product_id=N`,
391	// the target the Related menu's "Reviews" item opens.
392	//
393	// Without this the item opened a window that drew no tie to the
394	// product it came from, while every other item in the same menu
395	// did. The reason is structural rather than a bug in the menu:
396	// a tie needs BOTH windows to have an identity, and WooCommerce
397	// moved reviews off `edit-comments.php` onto its own admin page,
398	// which the built-in detection has no reason to know about. (The
399	// older `edit-comments.php?p=N` route is already covered by core
400	// detection, which is why a post's comments window ties.)
401	//
402	// Rooted at the product, exactly like the built-in comments
403	// identity is rooted at its post: reviews belong to the thing
404	// they review.
405	$reviews_identity = openstation_my_wordpress_woo_reviews_identity();
406	if ( $reviews_identity ) {
407	return $reviews_identity;
408	}
409
410	// 3. Coupon edit — the built-in post branch gives the identity;
411	// the restrictions are what make it interesting.
412	if ( is_array( $identity ) && 'shop_coupon' === ( $identity['type'] ?? '' ) ) {
413	$coupon = new WC_Coupon( (int) $identity['id'] );
414	if ( $coupon->get_id() ) {
415	$links = openstation_my_wordpress_woo_coupon_refs( $coupon );
416	if ( ! empty( $links ) ) {
417	$identity['links'] = array_merge(
418	(array) ( $identity['links'] ?? array() ),
419	$links
420	);
421	}
422	}
423	}
424
425	return $identity;
426	}
427
428	/*
429	-------------------------------------------------------------------
430	* Related entities — the title bar's "Related" menu
431	* ----------------------------------------------------------------
432	*/
433
434	/**
435	* A related-entity item, with the fields the sanitizer requires.
436	*
437	* @param string $id Unique id in the list.
438	* @param string $group Section key.
439	* @param string $group_label Section header.
440	* @param string $label Item label.
441	* @param string $icon Dashicon class.
442	* @param string $url Admin URL to open.
443	* @param int $count Optional count badge; 0 omits it.
444	* @return array
445	*/
446	function openstation_my_wordpress_woo_related_item( $id, $group, $group_label, $label, $icon, $url, $count = 0 ) {
447	$item = array(
448	'id' => $id,
449	'group' => $group,
450	'groupLabel' => $group_label,
451	'label' => $label,
452	'icon' => $icon,
453	'url' => $url,
454	);
455	if ( $count > 0 ) {
456	$item['count'] = (int) $count;
457	}
458
459	return $item;
460	}
461
462	/**
463	* Related items for an order: the customer, every product on it, and
464	* every coupon it used.
465	*
466	* @param WC_Abstract_Order $order Order.
467	* @return array[]
468	*/
469	function openstation_my_wordpress_woo_order_related( $order ) {
470	$related = array();
471
472	$customer_id = method_exists( $order, 'get_customer_id' ) ? (int) $order->get_customer_id() : 0;
473	if ( $customer_id > 0 ) {
474	$user = get_userdata( $customer_id );
475	if ( $user instanceof WP_User ) {
476	$label = $user->display_name ? $user->display_name : $user->user_login;
477
478	if ( current_user_can( 'edit_user', $customer_id ) ) {
479	// The person, as a customer. From an order, "customer"
480	// means this is who bought it — not *change their
481	// role* — so this opens the Customer window rather
482	// than the profile editor.
483	//
484	// The Related menu can only express a destination as
485	// a URL, and the only URL WordPress has for a person
486	// is their profile editor. The marker is what lets a
487	// specific view claim that URL: the shell's built-in
488	// profile remap stands down on any person-URL carrying
489	// it, so the claim doesn't depend on winning a
490	// registration-order race.
491	$related[] = openstation_my_wordpress_woo_related_item(
492	'wc-customer-' . $customer_id,
493	'wc-customer',
494	__( 'Customer', 'desktop-mode' ),
495	$label,
496	'dashicons-businessperson',
497	add_query_arg(
498	OPENSTATION_PERSON_VIEW_PARAM,
499	'wc-customer',
500	(string) get_edit_user_link( $customer_id )
501	)
502	);
503
504	// The profile editor is still one item away, unmarked
505	// — it is a real destination, just not the one
506	// "customer" means from an order.
507	$related[] = openstation_my_wordpress_woo_related_item(
508	'wc-customer-profile-' . $customer_id,
509	'wc-customer',
510	__( 'Customer', 'desktop-mode' ),
511	__( 'Edit profile', 'desktop-mode' ),
512	'dashicons-admin-users',
513	(string) get_edit_user_link( $customer_id )
514	);
515	}
516
517	// Their other orders. The count comes off the cached
518	// aggregate the Customers section already builds, so this
519	// is free — and an item that opens a list the merchant
520	// then has to filter by hand is not worth the click.
521	$map = function_exists( 'openstation_my_wordpress_woo_customer_spend_map' )
522	? openstation_my_wordpress_woo_customer_spend_map()
523	: array();
524	$orders = (int) ( $map[ $customer_id ]['orders'] ?? 0 );
525	if ( $orders > 1 && function_exists( 'openstation_my_wordpress_woo_customer_orders_url' ) ) {
526	$related[] = openstation_my_wordpress_woo_related_item(
527	'wc-customer-orders-' . $customer_id,
528	'wc-customer',
529	__( 'Customer', 'desktop-mode' ),
530	__( 'All orders by this customer', 'desktop-mode' ),
531	'dashicons-cart',
532	openstation_my_wordpress_woo_customer_orders_url( $customer_id ),
533	$orders
534	);
535	}
536	}
537	}
538
539	$items = 0;
540	foreach ( $order->get_items() as $item ) {
541	if ( $items >= OPENSTATION_WOO_RELATION_ITEM_CAP ) {
542	break;
543	}
544	$product_id = method_exists( $item, 'get_product_id' ) ? (int) $item->get_product_id() : 0;
545	if ( $product_id <= 0 \|\| ! get_post( $product_id ) ) {
546	// A line item whose product has since been deleted has no
547	// screen to open. It still reads correctly as text on the
548	// order itself; it just isn't navigation.
549	continue;
550	}
551	if ( ! current_user_can( 'edit_post', $product_id ) ) {
552	continue;
553	}
554	$related[] = openstation_my_wordpress_woo_related_item(
555	'wc-product-' . $product_id,
556	'wc-products',
557	__( 'Products', 'desktop-mode' ),
558	$item->get_name(),
559	'dashicons-products',
560	(string) get_edit_post_link( $product_id, 'raw' ),
561	(int) $item->get_quantity()
562	);
563	++$items;
564	}
565
566	$coupons = 0;
567	foreach ( $order->get_items( 'coupon' ) as $line ) {
568	if ( $coupons >= OPENSTATION_WOO_RELATION_ITEM_CAP ) {
569	break;
570	}
571	$coupon = new WC_Coupon( $line->get_code() );
572	if ( ! $coupon->get_id() \|\| ! current_user_can( 'edit_post', $coupon->get_id() ) ) {
573	continue;
574	}
575	$related[] = openstation_my_wordpress_woo_related_item(
576	'wc-coupon-' . $coupon->get_id(),
577	'wc-coupons',
578	__( 'Coupons', 'desktop-mode' ),
579	$coupon->get_code(),
580	'dashicons-tickets-alt',
581	(string) get_edit_post_link( $coupon->get_id(), 'raw' )
582	);
583	++$coupons;
584	}
585
586	return $related;
587	}
588
589	/**
590	* Order ids containing a given product, newest first.
591	*
592	* Read from the order-items tables rather than through
593	* `wc_get_orders()`, because there is no "orders containing product X"
594	* query in the WooCommerce API and walking orders to find one would
595	* mean loading every order on the store. Those two tables are the
596	* right index and they are populated under BOTH storages — High
597	* Performance Order Storage moves the order rows, not the line items.
598	*
599	* Matches `_variation_id` as well as `_product_id`: a variation is
600	* sold as its own line, and a merchant asking "who bought this
601	* product" means the variable product too.
602	*
603	* @param int $product_id Product id.
604	* @param int $limit How many orders.
605	* @return int[] Order ids.
606	*/
607	function openstation_my_wordpress_woo_orders_with_product( $product_id, $limit = 10 ) {
608	global $wpdb;
609
610	$product_id = (int) $product_id;
611	$limit = max( 1, (int) $limit );
612	// The order-items tables are WooCommerce's, not core's. Without
613	// the plugin they don't exist, and the query would print a
614	// "table doesn't exist" notice into whatever page called it.
615	if ( $product_id <= 0 \|\| ! openstation_my_wordpress_woo_active() ) {
616	return array();
617	}
618
619	$items = $wpdb->prefix . 'woocommerce_order_items';
620	$itemmeta = $wpdb->prefix . 'woocommerce_order_itemmeta';
621
622	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are structural; every value is prepared.
623	$sql = $wpdb->prepare(
624	"SELECT DISTINCT oi.order_id
625	FROM {$items} oi
626	INNER JOIN {$itemmeta} oim ON oim.order_item_id = oi.order_item_id
627	WHERE oi.order_item_type = 'line_item'
628	AND oim.meta_key IN ( '_product_id', '_variation_id' )
629	AND oim.meta_value = %d
630	ORDER BY oi.order_id DESC
631	LIMIT %d",
632	$product_id,
633	$limit
634	);
635
636	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- no core API answers "orders containing product X"; $sql came out of prepare() above, and the result feeds one menu render.
637	$ids = $wpdb->get_col( $sql );
638
639	return array_map( 'intval', (array) $ids );
640	}
641
642	/**
643	* Coupons restricted to a given product (or to one of its categories).
644	*
645	* WooCommerce stores both restrictions as comma-separated id strings
646	* in postmeta, which no meta query can search reliably — `LIKE
647	* '%12%'` matches 112 and 121. So the rows are read and split in PHP.
648	* Bounded: a store with more coupons than this has a coupon strategy,
649	* not a coupon, and the menu is not the place to enumerate it.
650	*
651	* @param int $product_id Product id.
652	* @param int $limit How many coupons.
653	* @return int[] Coupon post ids.
654	*/
655	function openstation_my_wordpress_woo_coupons_for_product( $product_id, $limit = 8 ) {
656	global $wpdb;
657
658	$product_id = (int) $product_id;
659	if ( $product_id <= 0 ) {
660	return array();
661	}
662
663	$category_ids = wp_get_post_terms( $product_id, 'product_cat', array( 'fields' => 'ids' ) );
664	$category_ids = is_wp_error( $category_ids ) ? array() : array_map( 'intval', $category_ids );
665
666	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are structural.
667	$sql = "SELECT pm.post_id, pm.meta_key, pm.meta_value
668	FROM {$wpdb->postmeta} pm
669	INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
670	WHERE p.post_type = 'shop_coupon'
671	AND p.post_status = 'publish'
672	AND pm.meta_key IN ( 'product_ids', 'product_categories' )
673	LIMIT 400";
674
675	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- comma-joined id strings can't be searched with a meta query; bounded scan feeding one menu render.
676	$rows = $wpdb->get_results( $sql );
677
678	$matched = array();
679	foreach ( (array) $rows as $row ) {
680	if ( count( $matched ) >= $limit ) {
681	break;
682	}
683	$values = array_filter( array_map( 'intval', explode( ',', (string) $row->meta_value ) ) );
684	if ( empty( $values ) ) {
685	continue;
686	}
687	$hit = 'product_ids' === $row->meta_key
688	? in_array( $product_id, $values, true )
689	: ( ! empty( array_intersect( $category_ids, $values ) ) );
690	if ( $hit ) {
691	$matched[ (int) $row->post_id ] = true;
692	}
693	}
694
695	return array_map( 'intval', array_keys( $matched ) );
696	}
697
698	/**
699	* Related items for a product: everything the catalogue screen knows
700	* about it and can't take you to.
701	*
702	* The built-in related pass covers `post` and `page` only, so a
703	* product gets none of this for free — its taxonomies are exactly as
704	* navigable as its order history, which is to say not at all.
705	*
706	* Budgets are per group and add up deliberately. The engine hard-caps
707	* the whole `related` list at 64, and an unbudgeted group would push
708	* the trailing ones silently over — losing the orders because a
709	* product happened to carry thirty tags. Worst case here is
710	* 10 + 10 + 1 + 1 + 10 + 8 + 8 = 48.
711	*
712	* @param int $product_id Product id.
713	* @return array[]
714	*/
715	function openstation_my_wordpress_woo_product_related( $product_id ) {
716	$related = array();
717	$product = wc_get_product( $product_id );
718	if ( ! $product ) {
719	return $related;
720	}
721
722	foreach ( array( 'product_cat', 'product_tag' ) as $taxonomy ) {
723	$tax = get_taxonomy( $taxonomy );
724	if ( ! $tax ) {
725	continue;
726	}
727	$terms = get_the_terms( $product_id, $taxonomy );
728	if ( ! is_array( $terms ) ) {
729	continue;
730	}
731	foreach ( array_slice( $terms, 0, 10 ) as $term ) {
732	$related[] = openstation_my_wordpress_woo_related_item(
733	'wc-term-' . $taxonomy . '-' . (int) $term->term_id,
734	'terms/' . $taxonomy,
735	(string) $tax->labels->name,
736	$term->name,
737	'product_cat' === $taxonomy ? 'dashicons-category' : 'dashicons-tag',
738	admin_url(
739	'term.php?taxonomy=' . rawurlencode( $taxonomy ) . '&tag_ID=' . (int) $term->term_id . '&post_type=product'
740	)
741	);
742	}
743	}
744
745	// Reviews. WooCommerce files them as comments of type `review`,
746	// and its own Reviews screen is the comment list with that filter
747	// pre-applied — which is exactly the URL worth linking.
748	$reviews = (int) $product->get_review_count();
749	if ( $reviews > 0 && current_user_can( 'moderate_comments' ) ) {
750	$related[] = openstation_my_wordpress_woo_related_item(
751	'wc-reviews-' . $product_id,
752	'wc-reviews',
753	__( 'Reviews', 'desktop-mode' ),
754	__( 'Reviews', 'desktop-mode' ),
755	'dashicons-star-filled',
756	admin_url( 'edit.php?post_type=product&page=product-reviews&product_id=' . (int) $product_id ),
757	$reviews
758	);
759	}
760
761	// Variations edit through their parent's screen, but a variable
762	// product's children are the thing a merchant actually adjusts —
763	// surface the parent screen's variations tab as one jump.
764	if ( $product->is_type( 'variable' ) ) {
765	$children = count( $product->get_children() );
766	if ( $children > 0 ) {
767	$related[] = openstation_my_wordpress_woo_related_item(
768	'wc-variations-' . $product_id,
769	'wc-products',
770	__( 'Product', 'desktop-mode' ),
771	__( 'Variations', 'desktop-mode' ),
772	'dashicons-networking',
773	(string) get_edit_post_link( $product_id, 'raw' ) . '#variable_product_options',
774	$children
775	);
776	}
777	}
778
779	// The other half of the story: who bought it. An order names its
780	// products, so the order → product jump has always worked; the
781	// reverse is the one a merchant actually asks for ("is this
782	// selling? who to?") and the catalogue screen has no answer at
783	// all.
784	//
785	// Gated on order access rather than on `edit_post`: this is order
786	// data reached from a product screen, and a shop editor who may
787	// not read orders must not read them sideways.
788	if ( openstation_my_wordpress_woo_can_read_orders() ) {
789	$customers = array();
790	$listed = 0;
791	foreach ( openstation_my_wordpress_woo_orders_with_product( $product_id, OPENSTATION_WOO_RELATION_ORDER_CANDIDATES ) as $order_id ) {
792	if ( $listed >= OPENSTATION_WOO_RELATION_ORDER_CAP ) {
793	break;
794	}
795	$order = wc_get_order( $order_id );
796	if ( ! openstation_my_wordpress_woo_is_purchase( $order ) ) {
797	continue;
798	}
799	++$listed;
800
801	$name = method_exists( $order, 'get_formatted_billing_full_name' )
802	? trim( $order->get_formatted_billing_full_name() )
803	: '';
804	$total = openstation_my_wordpress_woo_price(
805	$order->get_total(),
806	$order->get_currency()
807	);
808
809	$related[] = openstation_my_wordpress_woo_related_item(
810	'wc-order-' . $order_id,
811	'wc-orders',
812	__( 'Orders', 'desktop-mode' ),
813	'' !== $name
814	? sprintf(
815	/* translators: 1: order number, 2: customer name, 3: order total. */
816	__( '#%1$s · %2$s · %3$s', 'desktop-mode' ),
817	$order->get_order_number(),
818	$name,
819	$total
820	)
821	: sprintf(
822	/* translators: 1: order number, 2: order total. */
823	__( '#%1$s · %2$s', 'desktop-mode' ),
824	$order->get_order_number(),
825	$total
826	),
827	'dashicons-cart',
828	method_exists( $order, 'get_edit_order_url' )
829	? (string) $order->get_edit_order_url()
830	: ''
831	);
832
833	// Harvested from the same orders rather than queried
834	// again — the buyers of a product ARE the customers on
835	// its orders, and a second query would only say so more
836	// slowly.
837	$customer_id = method_exists( $order, 'get_customer_id' )
838	? (int) $order->get_customer_id()
839	: 0;
840	if ( $customer_id > 0 && ! isset( $customers[ $customer_id ] ) ) {
841	$customers[ $customer_id ] = true;
842	}
843	}
844
845	$shown = 0;
846	foreach ( array_keys( $customers ) as $customer_id ) {
847	if ( $shown >= 8 ) {
848	break;
849	}
850	$user = get_userdata( (int) $customer_id );
851	if ( ! $user instanceof WP_User \|\| ! current_user_can( 'edit_user', $user->ID ) ) {
852	continue;
853	}
854	$related[] = openstation_my_wordpress_woo_related_item(
855	'wc-buyer-' . (int) $customer_id,
856	'wc-customer',
857	__( 'Customers', 'desktop-mode' ),
858	$user->display_name ? $user->display_name : $user->user_login,
859	'dashicons-businessperson',
860	// The Customer window, not the profile editor — from
861	// a product or a coupon, a person is a buyer.
862	add_query_arg(
863	OPENSTATION_PERSON_VIEW_PARAM,
864	'wc-customer',
865	(string) get_edit_user_link( $user->ID )
866	)
867	);
868	++$shown;
869	}
870	}
871
872	// Coupons that discount it — WooCommerce shows the relationship
873	// only from the coupon's side, as a token field, so from the
874	// product there is currently no way to learn it is on offer.
875	foreach ( openstation_my_wordpress_woo_coupons_for_product( $product_id, 8 ) as $coupon_id ) {
876	if ( ! current_user_can( 'edit_post', $coupon_id ) ) {
877	continue;
878	}
879	$coupon = new WC_Coupon( $coupon_id );
880	if ( ! $coupon->get_id() ) {
881	continue;
882	}
883	$related[] = openstation_my_wordpress_woo_related_item(
884	'wc-product-coupon-' . $coupon_id,
885	'wc-coupons',
886	__( 'Coupons', 'desktop-mode' ),
887	$coupon->get_code(),
888	'dashicons-tickets-alt',
889	(string) get_edit_post_link( $coupon_id, 'raw' )
890	);
891	}
892
893	return $related;
894	}
895
896	/**
897	* Order ids that used a given coupon code, newest first.
898	*
899	* Coupon usage is a line item like any other, so it lives in the same
900	* always-populated order-items tables. `order_item_name` holds the
901	* code, lowercased by WooCommerce on apply.
902	*
903	* @param string $code Coupon code.
904	* @param int $limit How many orders.
905	* @return int[] Order ids.
906	*/
907	function openstation_my_wordpress_woo_orders_with_coupon( $code, $limit = 10 ) {
908	global $wpdb;
909
910	$code = strtolower( trim( (string) $code ) );
911	// Same table-ownership caveat as the product lookup above.
912	if ( '' === $code \|\| ! openstation_my_wordpress_woo_active() ) {
913	return array();
914	}
915
916	$items = $wpdb->prefix . 'woocommerce_order_items';
917
918	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is structural; every value is prepared.
919	$sql = $wpdb->prepare(
920	"SELECT DISTINCT order_id
921	FROM {$items}
922	WHERE order_item_type = 'coupon' AND LOWER( order_item_name ) = %s
923	ORDER BY order_id DESC
924	LIMIT %d",
925	$code,
926	max( 1, (int) $limit )
927	);
928
929	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- no core API answers "orders that used coupon X"; $sql came out of prepare() above, and the result feeds one menu render.
930	$ids = $wpdb->get_col( $sql );
931
932	return array_map( 'intval', (array) $ids );
933	}
934
935	/**
936	* Related items for a coupon: what it is restricted to, and who
937	* actually used it.
938	*
939	* WooCommerce renders the restrictions as select2 token fields —
940	* readable only by clicking into each token, and not links to
941	* anywhere. The usage count it does show is a bare number with
942	* nothing behind it.
943	*
944	* Budgets: 20 + 20 + 10 + 8 = 58, inside the engine's 64-item cap.
945	*
946	* @param int $coupon_id Coupon id.
947	* @return array[]
948	*/
949	function openstation_my_wordpress_woo_coupon_related( $coupon_id ) {
950	$related = array();
951	$coupon = new WC_Coupon( (int) $coupon_id );
952	if ( ! $coupon->get_id() ) {
953	return $related;
954	}
955
956	foreach ( array_slice( (array) $coupon->get_product_ids(), 0, OPENSTATION_WOO_RELATION_ITEM_CAP ) as $product_id ) {
957	$product = wc_get_product( (int) $product_id );
958	if ( ! $product \|\| ! current_user_can( 'edit_post', (int) $product_id ) ) {
959	continue;
960	}
961	$related[] = openstation_my_wordpress_woo_related_item(
962	'wc-coupon-product-' . (int) $product_id,
963	'wc-products',
964	__( 'Applies to', 'desktop-mode' ),
965	$product->get_name(),
966	'dashicons-products',
967	(string) get_edit_post_link( (int) $product_id, 'raw' )
968	);
969	}
970
971	foreach ( array_slice( (array) $coupon->get_product_categories(), 0, OPENSTATION_WOO_RELATION_ITEM_CAP ) as $term_id ) {
972	$term = get_term( (int) $term_id, 'product_cat' );
973	if ( ! $term instanceof WP_Term ) {
974	continue;
975	}
976	$related[] = openstation_my_wordpress_woo_related_item(
977	'wc-coupon-cat-' . (int) $term_id,
978	'terms/product_cat',
979	__( 'Applies to', 'desktop-mode' ),
980	$term->name,
981	'dashicons-category',
982	admin_url( 'term.php?taxonomy=product_cat&tag_ID=' . (int) $term_id . '&post_type=product' )
983	);
984	}
985
986	// Who redeemed it. The coupon screen shows a usage count and
987	// nothing behind it, so "did this campaign work, and for whom" is
988	// a question you currently answer by exporting orders.
989	if ( openstation_my_wordpress_woo_can_read_orders() ) {
990	$customers = array();
991	$listed = 0;
992	foreach ( openstation_my_wordpress_woo_orders_with_coupon( $coupon->get_code(), OPENSTATION_WOO_RELATION_ORDER_CANDIDATES ) as $order_id ) {
993	if ( $listed >= OPENSTATION_WOO_RELATION_ORDER_CAP ) {
994	break;
995	}
996	$order = wc_get_order( $order_id );
997	if ( ! openstation_my_wordpress_woo_is_purchase( $order ) ) {
998	continue;
999	}
1000	++$listed;
1001
1002	$name = method_exists( $order, 'get_formatted_billing_full_name' )
1003	? trim( $order->get_formatted_billing_full_name() )
1004	: '';
1005	$total = openstation_my_wordpress_woo_price(
1006	$order->get_total(),
1007	$order->get_currency()
1008	);
1009
1010	$related[] = openstation_my_wordpress_woo_related_item(
1011	'wc-coupon-order-' . $order_id,
1012	'wc-orders',
1013	__( 'Used on', 'desktop-mode' ),
1014	'' !== $name
1015	? sprintf(
1016	/* translators: 1: order number, 2: customer name, 3: order total. */
1017	__( '#%1$s · %2$s · %3$s', 'desktop-mode' ),
1018	$order->get_order_number(),
1019	$name,
1020	$total
1021	)
1022	: sprintf(
1023	/* translators: 1: order number, 2: order total. */
1024	__( '#%1$s · %2$s', 'desktop-mode' ),
1025	$order->get_order_number(),
1026	$total
1027	),
1028	'dashicons-cart',
1029	method_exists( $order, 'get_edit_order_url' )
1030	? (string) $order->get_edit_order_url()
1031	: ''
1032	);
1033
1034	$customer_id = method_exists( $order, 'get_customer_id' )
1035	? (int) $order->get_customer_id()
1036	: 0;
1037	if ( $customer_id > 0 ) {
1038	$customers[ $customer_id ] = true;
1039	}
1040	}
1041
1042	$shown = 0;
1043	foreach ( array_keys( $customers ) as $customer_id ) {
1044	if ( $shown >= 8 ) {
1045	break;
1046	}
1047	$user = get_userdata( (int) $customer_id );
1048	if ( ! $user instanceof WP_User \|\| ! current_user_can( 'edit_user', $user->ID ) ) {
1049	continue;
1050	}
1051	$related[] = openstation_my_wordpress_woo_related_item(
1052	'wc-coupon-buyer-' . (int) $customer_id,
1053	'wc-customer',
1054	__( 'Customers', 'desktop-mode' ),
1055	$user->display_name ? $user->display_name : $user->user_login,
1056	'dashicons-businessperson',
1057	// The Customer window, not the profile editor — from
1058	// a product or a coupon, a person is a buyer.
1059	add_query_arg(
1060	OPENSTATION_PERSON_VIEW_PARAM,
1061	'wc-customer',
1062	(string) get_edit_user_link( $user->ID )
1063	)
1064	);
1065	++$shown;
1066	}
1067	}
1068
1069	return $related;
1070	}
1071
1072	/**
1073	* Related items for a user identity: their orders, when they have any
1074	* and the viewer may see them.
1075	*
1076	* @param int $user_id User id.
1077	* @return array[]
1078	*/
1079	function openstation_my_wordpress_woo_user_related( $user_id ) {
1080	if (
1081	! function_exists( 'openstation_my_wordpress_woo_customer_spend_map' )
1082	\|\| true !== openstation_my_wordpress_woo_customers_permission()
1083	) {
1084	return array();
1085	}
1086
1087	$map = openstation_my_wordpress_woo_customer_spend_map();
1088	$stats = $map[ (int) $user_id ] ?? null;
1089	$orders = $stats ? (int) $stats['orders'] : 0;
1090	if ( $orders <= 0 ) {
1091	return array();
1092	}
1093
1094	return array(
1095	openstation_my_wordpress_woo_related_item(
1096	'wc-user-orders-' . (int) $user_id,
1097	'wc-orders',
1098	__( 'Store', 'desktop-mode' ),
1099	__( 'Orders', 'desktop-mode' ),
1100	'dashicons-cart',
1101	openstation_my_wordpress_woo_customer_orders_url( (int) $user_id ),
1102	$orders
1103	),
1104	);
1105	}
1106
1107	/**
1108	* Hang WooCommerce's relations off whatever identity the screen
1109	* resolved to.
1110	*
1111	* @param array[] $related Related items so far.
1112	* @param array $identity The resolved content identity.
1113	* @param WP_Screen\|null $screen Current screen, when available.
1114	* @return array[]
1115	*/
1116	function openstation_my_wordpress_woo_related_entities( $related, $identity, $screen ) {
1117	unset( $screen );
1118	if ( ! openstation_my_wordpress_woo_active() \|\| ! is_array( $identity ) ) {
1119	return $related;
1120	}
1121
1122	$type = (string) ( $identity['type'] ?? '' );
1123	$id = (int) ( $identity['id'] ?? 0 );
1124	if ( $id <= 0 ) {
1125	return $related;
1126	}
1127
1128	if ( 'shop_order' === $type && openstation_my_wordpress_woo_can_read_orders() ) {
1129	$order = wc_get_order( $id );
1130	if ( $order instanceof WC_Abstract_Order ) {
1131	$related = array_merge( (array) $related, openstation_my_wordpress_woo_order_related( $order ) );
1132	}
1133	} elseif ( 'product' === $type ) {
1134	$related = array_merge( (array) $related, openstation_my_wordpress_woo_product_related( $id ) );
1135	} elseif ( 'shop_coupon' === $type ) {
1136	$related = array_merge( (array) $related, openstation_my_wordpress_woo_coupon_related( $id ) );
1137	} elseif ( 'user' === $type ) {
1138	$related = array_merge( (array) $related, openstation_my_wordpress_woo_user_related( $id ) );
1139	}
1140
1141	return $related;
1142	}
1143
1144	/**
1145	* Boot the relations wiring.
1146	*
1147	* Priority 20 on the identity filter so a site that overrides the
1148	* order identity for its own reasons still wins.
1149	*
1150	* @return void
1151	*/
1152	function openstation_my_wordpress_woo_relations_boot() {
1153	add_filter( 'openstation_window_content_identity', 'openstation_my_wordpress_woo_content_identity', 20, 2 );
1154	add_filter( 'openstation_window_related_entities', 'openstation_my_wordpress_woo_related_entities', 20, 3 );
1155	}
1156	openstation_my_wordpress_woo_relations_boot();
1157