3 months ago (excludes never-ordered)
* 6m → last order > 6 months ago (excludes never-ordered)
* 1y → last order > 12 months ago (excludes never-ordered)
* never → no qualifying order at all
*
* Uses a single grouped SQL query (HPOS-aware) instead of N+1 calls.
* Results are paginated at 50 per page.
*
* @since 1.1.0
* @updated 1.2.0 Single grouped query, HPOS + legacy compatible, pagination.
*/
class AISP_Customer_Activity_Dormant {
const TRANSIENT_PREFIX = 'aisp_ca_dormant_';
const TRANSIENT_TTL = 600;
const PER_PAGE = 50;
public static function periods(): array {
return [
'3m' => [ 'label' => __( '3 months +', 'aisp-customer-activity' ), 'months' => 3 ],
'6m' => [ 'label' => __( '6 months +', 'aisp-customer-activity' ), 'months' => 6 ],
'1y' => [ 'label' => __( '1 year +', 'aisp-customer-activity' ), 'months' => 12 ],
'never' => [ 'label' => __( 'Never ordered', 'aisp-customer-activity' ), 'months' => null ],
];
}
// -------------------------------------------------------------------------
// Cache
// -------------------------------------------------------------------------
/**
* Return the full sorted customer list for a period (cached).
*/
public static function get_dormant_customers( string $period_key ): array {
if ( ! array_key_exists( $period_key, self::periods() ) ) {
return [];
}
$cache_key = self::TRANSIENT_PREFIX . sanitize_key( $period_key );
$cached = get_transient( $cache_key );
if ( false !== $cached ) {
return $cached;
}
$results = self::query_dormant( $period_key );
set_transient( $cache_key, $results, self::TRANSIENT_TTL );
return $results;
}
public static function invalidate_cache() {
foreach ( array_keys( self::periods() ) as $key ) {
delete_transient( self::TRANSIENT_PREFIX . sanitize_key( $key ) );
}
}
// -------------------------------------------------------------------------
// Query — single grouped SQL, HPOS + legacy aware
// -------------------------------------------------------------------------
private static function query_dormant( string $period_key ): array {
global $wpdb;
$period = self::periods()[ $period_key ];
$is_never = ( $period_key === 'never' );
$months = $period['months'];
$cutoff_dt = ( ! $is_never && $months )
? gmdate( 'Y-m-d H:i:s', strtotime( "-{$months} months" ) )
: null;
$tracked_roles = AISP_Customer_Activity_Settings::get_tracked_roles();
if ( empty( $tracked_roles ) ) {
return [];
}
// ── Step 1: fetch all eligible user IDs ──────────────────────────────
$users = get_users( [
'role__in' => $tracked_roles,
'fields' => 'ID',
'number' => -1, // all — filtering happens in step 2.
] );
if ( empty( $users ) ) {
return [];
}
$user_ids_sql = implode( ',', array_map( 'intval', $users ) );
// ── Step 2: one grouped query for last order per customer ─────────────
// Detect HPOS vs legacy storage.
$use_hpos = class_exists( '\Automattic\WooCommerce\Utilities\OrderUtil' )
&& \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
if ( $use_hpos ) {
// HPOS: wp_wc_orders table.
$orders_table = $wpdb->prefix . 'wc_orders';
$last_order_sql = "
SELECT customer_id, MAX(date_created_gmt) AS last_order_date
FROM {$orders_table}
WHERE type = 'shop_order'
AND status IN ('wc-completed','wc-processing','wc-on-hold')
AND customer_id IN ({$user_ids_sql})
GROUP BY customer_id
";
} else {
// Legacy: wp_posts + wp_postmeta.
$last_order_sql = "
SELECT pm.meta_value AS customer_id, MAX(p.post_date_gmt) AS last_order_date
FROM {$wpdb->posts} p
INNER JOIN {$wpdb->postmeta} pm
ON pm.post_id = p.ID AND pm.meta_key = '_customer_user'
WHERE p.post_type = 'shop_order'
AND p.post_status IN ('wc-completed','wc-processing','wc-on-hold')
AND pm.meta_value IN ({$user_ids_sql})
GROUP BY pm.meta_value
";
}
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$order_rows = $wpdb->get_results( $last_order_sql );
// Build a map: user_id => last_order_date (string | null).
$last_order_map = [];
foreach ( $order_rows as $row ) {
$last_order_map[ (int) $row->customer_id ] = $row->last_order_date;
}
// ── Step 3: fetch user + billing meta in bulk ─────────────────────────
$users_full = get_users( [
'include' => $users,
'fields' => [ 'ID', 'display_name', 'user_email' ],
'number' => -1,
] );
// Bulk fetch billing meta to avoid per-user meta queries.
$meta_keys = [ 'billing_first_name', 'billing_last_name', 'billing_company', 'billing_phone' ];
$meta_map = []; // [ user_id ][ meta_key ] = value
foreach ( $meta_keys as $meta_key ) {
$meta_key_sql = esc_sql( $meta_key );
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$rows = $wpdb->get_results( $wpdb->prepare(
"SELECT user_id, meta_value FROM {$wpdb->usermeta}
WHERE meta_key = %s AND user_id IN ({$user_ids_sql})",
$meta_key
) );
foreach ( $rows as $row ) {
$meta_map[ (int) $row->user_id ][ $meta_key ] = $row->meta_value;
}
}
// ── Step 4: filter and build result objects ───────────────────────────
$results = [];
foreach ( $users_full as $user ) {
$uid = (int) $user->ID;
$last_order_date = $last_order_map[ $uid ] ?? null; // null = never ordered.
if ( $is_never ) {
// Never bucket: keep only users with no order at all.
if ( $last_order_date !== null ) {
continue;
}
} else {
// Time-based bucket: must have ordered, but not within threshold.
if ( $last_order_date === null ) {
continue; // never ordered → belongs to "never" bucket only.
}
if ( $last_order_date > $cutoff_dt ) {
continue; // ordered too recently.
}
}
$row = new \stdClass();
$row->ID = $uid;
$row->display_name = $user->display_name;
$row->user_email = $user->user_email;
$row->first_name = $meta_map[ $uid ]['billing_first_name'] ?? '';
$row->last_name = $meta_map[ $uid ]['billing_last_name'] ?? '';
$row->company = $meta_map[ $uid ]['billing_company'] ?? '';
$row->phone = $meta_map[ $uid ]['billing_phone'] ?? '';
$row->last_order_date = $last_order_date;
$results[] = $row;
}
// Sort: most recent order first, then alphabetical. Never-ordered: alphabetical only.
usort( $results, function ( $a, $b ) {
$ta = $a->last_order_date ? strtotime( $a->last_order_date ) : 0;
$tb = $b->last_order_date ? strtotime( $b->last_order_date ) : 0;
if ( $ta !== $tb ) {
return $tb - $ta; // DESC.
}
return strcmp( (string) $a->display_name, (string) $b->display_name );
} );
return $results;
}
// -------------------------------------------------------------------------
// Render
// -------------------------------------------------------------------------
public static function render_tab() {
// phpcs:disable WordPress.Security.NonceVerification.Recommended
$current = isset( $_GET['dormant_period'] ) ? sanitize_key( $_GET['dormant_period'] ) : '3m';
$paged = isset( $_GET['dormant_paged'] ) ? max( 1, (int) $_GET['dormant_paged'] ) : 1;
// phpcs:enable
$periods = self::periods();
if ( ! array_key_exists( $current, $periods ) ) {
$current = '3m';
}
$all_customers = self::get_dormant_customers( $current );
$total = count( $all_customers );
$total_pages = (int) ceil( $total / self::PER_PAGE );
$paged = min( $paged, max( 1, $total_pages ) );
$offset = ( $paged - 1 ) * self::PER_PAGE;
$customers = array_slice( $all_customers, $offset, self::PER_PAGE );
$period = $periods[ $current ];
$base_url = admin_url( 'admin.php?page=aisp-customer-activity&tab=dormant&dormant_period=' );
// Description.
echo '';
if ( $current === 'never' ) {
esc_html_e( 'Customers who have never placed a qualifying order.', 'aisp-customer-activity' );
} else {
echo esc_html( sprintf(
/* translators: %s: period label e.g. "3 months +" */
__( 'Customers whose last qualifying order was more than %s ago.', 'aisp-customer-activity' ),
$period['label']
) );
}
echo '
';
// Period selector buttons.
echo '';
echo '
' . esc_html__( 'No orders since:', 'aisp-customer-activity' ) . '';
foreach ( $periods as $key => $def ) {
$active = ( $key === $current ) ? ' button-primary' : '';
echo '
'
. esc_html( $def['label'] ) . '';
}
echo '
' . esc_html__( 'No customers found for this period.', 'aisp-customer-activity' ) . '
';
return;
}
// Count + pagination info.
$showing_from = $offset + 1;
$showing_to = min( $offset + self::PER_PAGE, $total );
echo '';
echo esc_html( sprintf(
/* translators: 1: first item, 2: last item, 3: total */
__( 'Showing %1$d–%2$d of %3$d customers.', 'aisp-customer-activity' ),
$showing_from,
$showing_to,
$total
) );
echo '
';
// Table.
// Bulk-fetch notes for the current page only.
$page_user_ids = array_column( $customers, 'ID' );
$notes_map = AISP_Customer_Activity_Notes::get_bulk( $page_user_ids );
echo '';
echo '';
echo '| ' . esc_html__( 'Name', 'aisp-customer-activity' ) . ' | ';
echo '' . esc_html__( 'Company', 'aisp-customer-activity' ) . ' | ';
echo '' . esc_html__( 'Phone', 'aisp-customer-activity' ) . ' | ';
echo '' . esc_html__( 'Email', 'aisp-customer-activity' ) . ' | ';
echo '' . esc_html__( 'Last Order', 'aisp-customer-activity' ) . ' | ';
echo '' . esc_html__( 'Note / Reminder', 'aisp-customer-activity' ) . ' | ';
echo '
';
foreach ( $customers as $c ) {
$uid = (int) $c->ID;
$note_data = $notes_map[ $uid ] ?? [ 'note' => '', 'reminder' => '' ];
self::render_row( $c, $note_data['note'], $note_data['reminder'] );
}
echo '
';
// Pagination nav.
if ( $total_pages > 1 ) {
self::render_pagination( $current, $paged, $total_pages );
}
}
private static function render_row( object $c, string $note = '', string $reminder = '' ) {
$first = trim( (string) $c->first_name );
$last = trim( (string) $c->last_name );
$full_name = trim( "$first $last" ) ?: $c->display_name;
$company = trim( (string) $c->company );
$phone = trim( (string) $c->phone );
$email = trim( (string) $c->user_email );
$edit_url = get_edit_user_link( (int) $c->ID );
$last_order = $c->last_order_date
? date_i18n( get_option( 'date_format' ), strtotime( $c->last_order_date ) )
: '' . esc_html__( 'Never ordered', 'aisp-customer-activity' ) . '';
echo '';
echo '| ' . esc_html( $full_name ) . ' | ';
echo '' . ( $company ? esc_html( $company ) : '—' ) . ' | ';
echo '' . ( $phone ? '📞 ' . esc_html( $phone ) . '' : '—' ) . ' | ';
echo '' . ( $email ? '✉ ' . esc_html( $email ) . '' : '—' ) . ' | ';
echo '' . wp_kses_post( $last_order ) . ' | ';
AISP_Customer_Activity_Notes::render_note_cell( (int) $c->ID, $note, $reminder );
echo '
';
}
private static function render_pagination( string $period_key, int $current_page, int $total_pages ) {
$base = admin_url( 'admin.php?page=aisp-customer-activity&tab=dormant&dormant_period=' . $period_key . '&dormant_paged=' );
echo '';
}
}