'overdue' ); const SEGMENT_SLUGS_TRANSIENT = 'aieo_audience_segment_slugs'; public static function segment_slugs() { static $memo = null; if ( null !== $memo ) { return $memo; } $cached = get_transient( self::SEGMENT_SLUGS_TRANSIENT ); if ( is_array( $cached ) && ! empty( $cached ) ) { $memo = $cached; return $memo; } global $wpdb; $ci = $wpdb->prefix . 'aieo_customer_intelligence'; $seen = array(); if ( self::table( $ci ) ) { $rows = $wpdb->get_col( "SELECT segments_json FROM `{$ci}` WHERE segments_json IS NOT NULL AND segments_json <> '' AND segments_json <> '[]'" ); if ( ! empty( $rows ) ) { preg_match_all( '/"([a-z0-9_\-]+)"/i', implode( ',', $rows ), $m ); foreach ( (array) $m[1] as $slug ) { $slug = self::SEGMENT_SLUG_ALIASES[ $slug ] ?? $slug; if ( in_array( $slug, self::SEGMENT_SLUGS_RETIRED, true ) ) { continue; } $seen[ $slug ] = true; } } } if ( empty( $seen ) ) { $memo = self::SEGMENT_SLUGS_FALLBACK; return $memo; } $memo = array_keys( $seen ); sort( $memo ); set_transient( self::SEGMENT_SLUGS_TRANSIENT, $memo, 6 * HOUR_IN_SECONDS ); return $memo; } public static function flush_segment_slugs() { delete_transient( self::SEGMENT_SLUGS_TRANSIENT ); } const DORMANCY_COHORTS = array( 'active', 'drifting', 'lapsed', 'long_dormant', 'lost' ); const SYNTHETIC_POPULATIONS = array( 'pure_intent' => array( 'col' => 'sole_order_share', 'op' => '>=', 'value' => 0.50 ), ); public static function population_names() { $out = array(); foreach ( array_keys( self::SYNTHETIC_POPULATIONS ) as $n ) { $out[ $n ] = 'synthetic'; } foreach ( self::DORMANCY_COHORTS as $n ) { $out[ $n ] = 'dormancy'; } foreach ( self::segment_slugs() as $n ) { if ( ! isset( $out[ $n ] ) ) { $out[ $n ] = 'segment'; } } return $out; } public static function is_population( $name ) { $names = self::population_names(); return isset( $names[ (string) $name ] ); } public static function population_where( $name, $alias = 'ci' ) { $name = (string) $name; $alias = preg_replace( '/[^A-Za-z0-9_]/', '', (string) $alias ); if ( '' === $alias ) { $alias = 'ci'; } $kind = self::population_names()[ $name ] ?? ''; if ( 'synthetic' === $kind ) { $def = self::SYNTHETIC_POPULATIONS[ $name ]; $op = in_array( $def['op'], array( '>=', '>', '<=', '<', '=' ), true ) ? $def['op'] : '>='; return array( 'sql' => "{$alias}.`" . $def['col'] . "` {$op} %f", 'args' => array( (float) $def['value'] ), 'kind' => 'synthetic', ); } if ( 'dormancy' === $kind ) { return array( 'sql' => "{$alias}.`dormancy_cohort` = %s", 'args' => array( 'active' === $name ? '' : $name ), 'kind' => 'dormancy', ); } if ( 'segment' === $kind ) { $names = array( $name ); $key = strtolower( $name ); if ( isset( self::SEGMENT_SLUG_ALIASES[ $key ] ) ) { $names[] = self::SEGMENT_SLUG_ALIASES[ $key ]; } foreach ( self::SEGMENT_SLUG_ALIASES as $legacy => $current ) { if ( $current === $key ) { $names[] = $legacy; } } $names = array_values( array_unique( $names ) ); $parts = array(); $args = array(); foreach ( $names as $n ) { $parts[] = "JSON_SEARCH(IFNULL({$alias}.`segments_json`, '[]'), 'one', %s) IS NOT NULL"; $args[] = $n; } return array( 'sql' => '(' . implode( ' OR ', $parts ) . ')', 'args' => $args, 'kind' => 'segment', ); } throw new InvalidArgumentException( sprintf( 'Unknown population "%s". Expected a synthetic (%s), a dormancy cohort (%s) or a segment (%s).', $name, implode( ', ', array_keys( self::SYNTHETIC_POPULATIONS ) ), implode( ', ', self::DORMANCY_COHORTS ), implode( ', ', self::segment_slugs() ) ) ); } public static function field_catalog() { $num = array( '=', '!=', '>', '>=', '<', '<=' ); $sym = function_exists( 'get_woocommerce_currency_symbol' ) ? html_entity_decode( get_woocommerce_currency_symbol(), ENT_QUOTES, 'UTF-8' ) : '€'; return array( 'ltv_total' => array( 'col' => 'ltv_total', 'type' => 'number', 'label' => 'Lifetime value (' . $sym . ')', 'ops' => $num ), 'aov' => array( 'col' => 'aov', 'type' => 'number', 'label' => 'Average order value (' . $sym . ')', 'ops' => $num ), 'orders_count' => array( 'col' => 'orders_count', 'type' => 'number', 'label' => 'Orders count', 'ops' => $num ), 'recency_days' => array( 'col' => 'recency_days', 'type' => 'number', 'label' => 'Days since last order', 'ops' => $num ), 'reactivation_score' => array( 'col' => 'reactivation_score', 'type' => 'number', 'label' => 'Reactivation score (0-100)', 'ops' => $num ), 'discount_share' => array( 'col' => 'discount_share', 'type' => 'number', 'label' => 'Discount share (0-1)', 'ops' => $num, 'help' => 'the share of the lines the customer bought that were on sale when bought; not coupon use' ), 'avg_item_price' => array( 'col' => 'avg_item_price', 'type' => 'number', 'label' => 'Avg item price (' . $sym . ')', 'ops' => $num ), 'days_since_last_engagement' => array( 'col' => 'days_since_last_engagement', 'type' => 'number', 'label' => 'Days since last email engagement', 'ops' => $num ), 'open_rate_lifetime' => array( 'col' => 'open_rate_lifetime', 'type' => 'number', 'label' => 'Email open rate (0-1)', 'ops' => $num ), 'sale_purchase_cnt' => array( 'col' => 'sale_purchase_cnt', 'type' => 'number', 'label' => 'On-sale purchases', 'ops' => $num ), 'free_shipping_purchase_cnt' => array( 'col' => 'free_shipping_purchase_cnt', 'type' => 'number', 'label' => 'Free-shipping purchases', 'ops' => $num ), 'sole_order_share' => array( 'col' => 'sole_order_share', 'type' => 'number', 'label' => 'Single-item order share (0-1)', 'ops' => $num ), 'segments' => array( 'col' => 'segments_json', 'type' => 'json', 'label' => 'Segment (behavioural — not dormancy)', 'ops' => array( 'contains' ), 'enum' => self::segment_slugs() ), 'dormancy_cohort' => array( 'col' => 'dormancy_cohort', 'type' => 'string', 'label' => 'Dormancy cohort (days since last order)', 'ops' => array( '=','!=','in' ), 'enum' => array( 'active','drifting','lapsed','long_dormant','lost' ) ), 'rfm_cell' => array( 'col' => 'rfm_cell', 'type' => 'string', 'label' => 'RFM cell', 'ops' => array( '=','!=','in' ) ), 'price_band' => array( 'col' => 'price_band', 'type' => 'number', 'label' => 'Price band (1-5 quintile of avg item price)', 'ops' => array_merge( $num, array( 'in' ) ), 'enum' => array( 1, 2, 3, 4, 5 ), 'enum_labels' => array( 1 => 'lowest fifth', 2 => 'second fifth', 3 => 'middle fifth', 4 => 'fourth fifth', 5 => 'highest fifth' ), 'exclude_zero' => true ), 'tnp_status' => array( 'col' => 'tnp_status', 'type' => 'string', 'label' => 'Newsletter status', 'ops' => array( '=','!=' ), 'enum' => array( 'C','U','B','' ) ), 'inferred_top_priority' => array( 'col' => 'inferred_top_priority', 'type' => 'string', 'label' => 'Inferred top priority', 'ops' => array( '=','!=' ) ), 'top_category_id' => array( 'col' => 'top_category_id', 'type' => 'term', 'label' => 'Top category (term id)', 'ops' => array( '=','in' ) ), 'top_brand_id' => array( 'col' => 'top_brand_id', 'type' => 'term', 'label' => 'Top brand (term id)', 'ops' => array( '=','in' ) ), 'instigator' => array( 'col' => '', 'type' => 'behaviour', 'label' => 'In unfulfilled-intent state (carted/wished/heavy-viewed, not bought)', 'ops' => array( 'is' ) ), 'lookalike' => array( 'col' => '', 'type' => 'behaviour', 'label' => 'Behaviourally similar to a seed subject', 'ops' => array( 'is' ) ), 'image_engaged' => array( 'col' => '', 'type' => 'behaviour', 'label' => 'Has opened product/content images (deliberate look, no purchase needed)', 'ops' => array( 'is' ) ), 'ever_purchased_product' => array( 'col' => '', 'type' => 'purchase', 'label' => 'Ever purchased product (product id)', 'ops' => array( 'in', 'not_in' ) ), 'ever_purchased_category' => array( 'col' => '', 'type' => 'purchase', 'label' => 'Ever purchased in category (term id)', 'ops' => array( 'in', 'not_in' ) ), 'ever_purchased_brand' => array( 'col' => '', 'type' => 'purchase', 'label' => 'Ever purchased brand (term id)', 'ops' => array( 'in', 'not_in' ) ), 'ever_purchased_tag' => array( 'col' => '', 'type' => 'purchase', 'label' => 'Ever purchased tagged (tag term id)', 'ops' => array( 'in', 'not_in' ) ), 'ever_purchased_need' => array( 'col' => '', 'type' => 'purchase', 'label' => 'Ever purchased for need (need term id)', 'ops' => array( 'in', 'not_in' ) ), 'nps_class' => array( 'col' => 'nps_class', 'type' => 'voc', 'voc_kind' => 'string', 'label' => 'Survey NPS class', 'ops' => array( '=', '!=', 'in' ), 'enum' => array( 'promoter', 'passive', 'detractor' ) ), 'voc_band' => array( 'col' => 'voc_band', 'type' => 'voc', 'voc_kind' => 'string', 'label' => 'Voice-of-customer band', 'ops' => array( '=', '!=', 'in' ), 'enum' => array( 'high', 'mid', 'low' ) ), 'voc_score' => array( 'col' => 'voc_score', 'type' => 'voc', 'voc_kind' => 'number', 'label' => 'Voice-of-customer score (0-100)', 'ops' => $num ), 'shipping_price_concern' => array( 'col' => 'shipping_price_concern', 'type' => 'voc', 'voc_kind' => 'number', 'label' => 'Flagged shipping-price concern in survey (0/1)', 'ops' => array( '=' ), 'enum' => array( '0', '1' ) ), 'competition_entered' => array( 'col' => '', 'type' => 'entry', 'entry' => 'competition', 'label' => 'Entered a competition (competition id)', 'ops' => array( 'in', 'not_in' ), 'help' => 'a confirmed entry in one of the competitions listed by id; optional "days": entered in the last so many days' ), 'form_submitted' => array( 'col' => '', 'type' => 'entry', 'entry' => 'form', 'label' => 'Filled in a form (form id)', 'ops' => array( 'in', 'not_in' ), 'help' => 'an entry of one of the forms listed by id; optional "days": sent in the last so many days' ), 'newsletter_clicked' => array( 'col' => '', 'type' => 'entry', 'entry' => 'newsletter', 'label' => 'Clicked a newsletter (newsletter id: 1 = the newsletter digest)', 'ops' => array( 'in', 'not_in' ), 'help' => 'clicked a link in one of the newsletters listed by id (1 is the newsletter digest, the only one); optional "days": clicked in the last so many days' ), 'campaign_engaged' => array( 'col' => '', 'type' => 'entry', 'entry' => 'campaign', 'label' => 'Opened or clicked a campaign (workflow id)', 'ops' => array( 'in', 'not_in' ), 'help' => 'opened an e-mail, or clicked an e-mail or a push notification, of one of the workflows listed by id; optional "days": in the last so many days' ), 'push_clicked' => array( 'col' => '', 'type' => 'entry', 'entry' => 'push', 'label' => 'Clicked a push notification of a campaign (workflow id)', 'ops' => array( 'in', 'not_in' ), 'help' => 'clicked a push notification of one of the workflows listed by id; optional "days": clicked in the last so many days' ), ) + self::survey_fields() + self::measure_fields(); } const MEASURE_FIELDS = array( 'new_product_purchase_cnt', 'gift_completed_count', 'monthly_offer_purchase_cnt', 'offer_converted', 'offer_declined', 'kind_converted', 'kind_declined', 'campaign_converted', 'campaign_declined' ); private static function measure_fields() { if ( ! class_exists( 'AIEO_DMM_Offer_Exposures' ) || ! AIEO_DMM_Offer_Exposures::is_on() ) { return array(); } $num = array( '=', '!=', '>', '>=', '<', '<=' ); $in = array( 'in', 'not_in' ); return array( 'new_product_purchase_cnt' => array( 'col' => 'new_product_purchase_cnt', 'type' => 'number', 'label' => 'New-product purchases', 'ops' => $num ), 'gift_completed_count' => array( 'col' => 'gift_completed_count', 'type' => 'number', 'label' => 'Gifts received', 'ops' => $num ), 'monthly_offer_purchase_cnt' => array( 'col' => 'monthly_offer_purchase_cnt', 'type' => 'number', 'label' => 'Monthly-offer purchases', 'ops' => $num ), 'offer_converted' => array( 'col' => '', 'type' => 'response', 'label' => 'Took an offer (offer id)', 'ops' => $in, 'response' => array( 'converted', 'offer_id' ) ), 'offer_declined' => array( 'col' => '', 'type' => 'response', 'label' => 'Let an offer pass (offer id)', 'ops' => $in, 'response' => array( 'declined', 'offer_id' ) ), 'kind_converted' => array( 'col' => '', 'type' => 'response', 'label' => 'Took an offer of a kind', 'ops' => $in, 'response' => array( 'converted', 'offer_kind' ) ), 'kind_declined' => array( 'col' => '', 'type' => 'response', 'label' => 'Let an offer of a kind pass', 'ops' => $in, 'response' => array( 'declined', 'offer_kind' ) ), 'campaign_converted' => array( 'col' => '', 'type' => 'response', 'label' => 'Took an offer of a campaign (campaign id)', 'ops' => $in, 'response' => array( 'converted', 'campaign_id' ) ), 'campaign_declined' => array( 'col' => '', 'type' => 'response', 'label' => 'Let an offer of a campaign pass (campaign id)', 'ops' => $in, 'response' => array( 'declined', 'campaign_id' ) ), ); } private static function survey_fields() { if ( ! class_exists( 'AIEO_Survey_Blueprint' ) ) { return array(); } $num = array( '=', '!=', '>', '>=', '<', '<=' ); $out = array(); $seen = array(); foreach ( (array) AIEO_Survey_Blueprint::all()['blueprints'] as $bid => $bp ) { foreach ( AIEO_Survey_Blueprint::question_codes( $bid ) as $code ) { if ( isset( $seen[ $code ] ) ) { continue; } $seen[ $code ] = true; $q = AIEO_Survey_Blueprint::question( $code ); if ( ! $q || 'text' === $q['answers'] ) { continue; } $scale = ( 'scale' === $q['scored'] ); $out[ 'survey_' . $code ] = array( 'col' => $code, 'type' => 'survey', 'survey_kind' => $scale ? 'number' : 'string', 'label' => sprintf( __( 'User Survey: %s', 'ai-eshop-optimizer' ), $q['label'] ), 'ops' => $scale ? $num : array( '=', '!=', 'in' ), 'enum' => $scale ? array() : array_keys( $q['choices'] ), ); } unset( $bp ); } return $out; } public static function compile( $rule ) { if ( ! is_array( $rule ) ) { return '1=0'; } $op = ( isset( $rule['op'] ) && 'or' === strtolower( (string) $rule['op'] ) ) ? 'OR' : 'AND'; $conds = isset( $rule['conditions'] ) && is_array( $rule['conditions'] ) ? $rule['conditions'] : array(); if ( empty( $conds ) ) { return '1=0'; } $parts = array(); foreach ( $conds as $cnd ) { if ( ! is_array( $cnd ) ) { continue; } if ( isset( $cnd['conditions'] ) ) { $sub = self::compile( $cnd ); if ( '' !== $sub ) { $parts[] = '(' . $sub . ')'; } continue; } $leaf = self::compile_leaf( $cnd ); if ( '' !== $leaf ) { $parts[] = $leaf; } } if ( empty( $parts ) ) { return '1=0'; } return implode( ' ' . $op . ' ', $parts ); } private static function compile_leaf( $cnd ) { global $wpdb; $cat = self::field_catalog(); $field = (string) ( $cnd['field'] ?? '' ); if ( ! isset( $cat[ $field ] ) ) { return in_array( $field, self::MEASURE_FIELDS, true ) ? '1=0' : ''; } $def = $cat[ $field ]; $op = strtolower( (string) ( $cnd['op'] ?? '' ) ); if ( ! in_array( $op, array_map( 'strtolower', $def['ops'] ), true ) ) { return ''; } $a = self::CI_ALIAS; $col = $a . '.' . $def['col']; switch ( $def['type'] ) { case 'number': if ( 'in' === $op ) { $vals = array(); foreach ( self::int_list( $cnd['value'] ?? array() ) as $iv ) { if ( empty( $def['exclude_zero'] ) || 0 !== $iv ) { $vals[] = $iv; } } return $vals ? "{$col} IN (" . implode( ',', $vals ) . ')' : '1=0'; } $v = (float) ( $cnd['value'] ?? 0 ); $sym = self::num_op( $op ); if ( ! $sym ) { return ''; } if ( ! empty( $def['exclude_zero'] ) ) { return "({$col} {$sym} {$v} AND {$col} <> 0)"; } return "{$col} {$sym} {$v}"; case 'term': if ( 'in' === $op ) { $ids = self::int_list( $cnd['value'] ?? array() ); return $ids ? "{$col} IN (" . implode( ',', $ids ) . ')' : '1=0'; } return "{$col} = " . (int) ( $cnd['value'] ?? 0 ); case 'string': $map = ( 'dormancy_cohort' === $field ) ? function ( $x ) { return 'active' === (string) $x ? '' : (string) $x; } : null; if ( 'in' === $op ) { $raw = is_array( $cnd['value'] ?? null ) ? $cnd['value'] : array( $cnd['value'] ?? '' ); if ( $map ) { $raw = array_map( $map, (array) $raw ); } $vals = self::str_list( $raw ); return $vals ? "{$col} IN (" . implode( ',', $vals ) . ')' : '1=0'; } $sym = ( '!=' === $op ) ? '<>' : '='; $val = (string) ( $cnd['value'] ?? '' ); if ( $map ) { $val = $map( $val ); } return "{$col} {$sym} '" . esc_sql( $val ) . "'"; case 'json': $val = preg_replace( '/[^a-z0-9_\-]/i', '', (string) ( $cnd['value'] ?? '' ) ); if ( '' === $val ) { return '1=0'; } $names = array( $val ); $key = strtolower( $val ); if ( isset( self::SEGMENT_SLUG_ALIASES[ $key ] ) ) { $names[] = self::SEGMENT_SLUG_ALIASES[ $key ]; } foreach ( self::SEGMENT_SLUG_ALIASES as $legacy => $current ) { if ( $current === $key ) { $names[] = $legacy; } } $names = array_unique( $names ); $likes = array(); foreach ( $names as $n ) { $likes[] = "{$col} LIKE '%\"" . esc_sql( $n ) . "\"%'"; } return ( 1 === count( $likes ) ) ? $likes[0] : '(' . implode( ' OR ', $likes ) . ')'; case 'behaviour': return self::compile_behaviour( $field, $cnd ); case 'purchase': return self::compile_purchase( $field, $op, $cnd ); case 'response': return self::compile_response( $def, $op, $cnd ); case 'entry': return self::compile_entry( $def, $op, $cnd ); case 'voc': return self::compile_voc( $field, $op, $cnd ); case 'survey': return self::compile_survey( $field, $op, $cnd ); } return ''; } private static function compile_survey( $field, $op, $cnd ) { global $wpdb; $cat = self::field_catalog(); $def = $cat[ $field ] ?? null; if ( ! $def || empty( $def['col'] ) || ! class_exists( 'AIEO_Form_CPT' ) ) { return ''; } $entries = AIEO_Form_CPT::entries_table(); $values = AIEO_Form_CPT::entry_values_table(); if ( ! self::table( $entries ) || ! self::table( $values ) ) { return '1=0'; } $code = esc_sql( (string) $def['col'] ); if ( 'number' === ( $def['survey_kind'] ?? '' ) ) { $sym = self::num_op( $op ); if ( ! $sym ) { return ''; } $inner = "sav.value_num {$sym} " . (float) ( $cnd['value'] ?? 0 ); } elseif ( 'in' === $op ) { $raw = is_array( $cnd['value'] ?? null ) ? $cnd['value'] : array( $cnd['value'] ?? '' ); $vals = self::str_list( $raw ); if ( ! $vals ) { return '1=0'; } $inner = 'sav.choice_key IN (' . implode( ',', $vals ) . ')'; } else { $sym = ( '!=' === $op ) ? '<>' : '='; $inner = "sav.choice_key {$sym} '" . esc_sql( (string) ( $cnd['value'] ?? '' ) ) . "'"; } $a = self::CI_ALIAS; return "EXISTS (SELECT 1 FROM `{$values}` sav JOIN `{$entries}` sae ON sae.id = sav.entry_id WHERE sae.eponymous_id = {$a}.eponymous_id AND sae.eponymous_id > 0 AND sav.field_key = '{$code}' AND sav.disposition = 'answered' AND {$inner})"; } private static function compile_voc( $field, $op, $cnd ) { global $wpdb; $cat = self::field_catalog(); $def = $cat[ $field ] ?? null; if ( ! $def || empty( $def['col'] ) ) { return ''; } $sv = $wpdb->prefix . 'aieo_survey_intelligence'; if ( ! self::table( $sv ) ) { return '1=0'; } $col = 'sv.' . $def['col']; if ( 'number' === ( $def['voc_kind'] ?? '' ) ) { $sym = self::num_op( $op ); if ( ! $sym ) { return ''; } $inner = "{$col} {$sym} " . (float) ( $cnd['value'] ?? 0 ); } else { if ( 'in' === $op ) { $raw = is_array( $cnd['value'] ?? null ) ? $cnd['value'] : array( $cnd['value'] ?? '' ); $vals = self::str_list( $raw ); if ( ! $vals ) { return '1=0'; } $inner = "{$col} IN (" . implode( ',', $vals ) . ')'; } else { $sym = ( '!=' === $op ) ? '<>' : '='; $inner = "{$col} {$sym} '" . esc_sql( (string) ( $cnd['value'] ?? '' ) ) . "'"; } } $a = self::CI_ALIAS; $guard = ''; if ( 'nps_class' === $field ) { $guard = " AND sv.nps_class <> ''"; } elseif ( 'shipping_price_concern' === $field ) { $guard = ' AND sv.f18_entry_id > 0'; } return "EXISTS (SELECT 1 FROM `{$sv}` sv WHERE {$a}.email <> '' AND sv.email = LOWER(TRIM({$a}.email)){$guard} AND {$inner})"; } private static function compile_purchase( $field, $op, $cnd ) { global $wpdb; $kind_map = array( 'ever_purchased_product' => 'product', 'ever_purchased_category' => 'category', 'ever_purchased_brand' => 'brand', 'ever_purchased_tag' => 'tag', 'ever_purchased_need' => 'need', ); $kind = $kind_map[ $field ] ?? ''; if ( '' === $kind ) { return ''; } $t = function_exists( 'aieo_customer_purchases_table' ) ? aieo_customer_purchases_table() : ( $wpdb->prefix . 'aieo_customer_purchases' ); if ( ! self::table( $t ) ) { return '1=0'; } if ( 'not_in' === $op && class_exists( 'AIEO_Customer_Purchases' ) && ! in_array( $kind, AIEO_Customer_Purchases::ready_kinds(), true ) ) { return '1=0'; } $ids = self::int_list( $cnd['value'] ?? array() ); if ( empty( $ids ) ) { return ( 'not_in' === $op ) ? '1=1' : '1=0'; } $in = implode( ',', $ids ); $a = self::CI_ALIAS; $exists = "EXISTS (SELECT 1 FROM `{$t}` cp WHERE cp.email_hash = MD5(LOWER(TRIM({$a}.email))) AND cp.kind = '{$kind}' AND cp.ref_id IN ({$in}))"; return ( 'not_in' === $op ) ? "NOT {$exists}" : $exists; } private static function compile_response( $def, $op, $cnd ) { global $wpdb; $t = $wpdb->prefix . 'aieo_dmm_offer_responses'; if ( ! self::table( $t ) ) { return '1=0'; } list( $state, $column ) = $def['response']; if ( 'offer_kind' === $column ) { $values = array_values( array_unique( array_filter( array_map( 'sanitize_key', (array) ( $cnd['value'] ?? array() ) ) ) ) ); $list = $values ? "'" . implode( "','", $values ) . "'" : ''; } else { $values = self::int_list( $cnd['value'] ?? array() ); $list = implode( ',', $values ); } if ( '' === $list ) { return '1=0'; } $a = self::CI_ALIAS; $days = isset( $cnd['days'] ) ? (int) $cnd['days'] : 0; $when = $days > 0 ? $wpdb->prepare( ' AND r.closed_gmt >= %s', gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS ) ) : ''; $exists = "EXISTS (SELECT 1 FROM `{$t}` r WHERE ((r.user_id > 0 AND r.user_id = {$a}.wp_user_id) OR r.email_hash = SHA2(LOWER(TRIM({$a}.email)), 256)) AND r.state = '{$state}' AND r.{$column} IN ({$list}){$when})"; return 'not_in' === $op ? "NOT {$exists}" : $exists; } const ENTRY_FIELDS = array( 'competition_entered' => 'competition', 'form_submitted' => 'form', ); const CLICK_FIELDS = array( 'newsletter_clicked' => 'newsletter', 'campaign_engaged' => 'campaign', 'push_clicked' => 'push', ); const NEWSLETTERS = array( 1 => 'newsletter_digest' ); private static function compile_entry( $def, $op, $cnd ) { $src = self::entry_source( (string) ( $def['entry'] ?? '' ), self::int_list( $cnd['value'] ?? array() ), isset( $cnd['days'] ) ? (int) $cnd['days'] : 0 ); if ( null === $src ) { return '1=0'; } $a = self::CI_ALIAS; $parts = array( "({$a}.wp_user_id > 0 AND {$a}.wp_user_id IN ({$src['users']}))" ); if ( '' !== $src['emails'] ) { $parts[] = "({$a}.email <> '' AND SHA2(LOWER(TRIM({$a}.email)), 256) IN ({$src['emails']}))"; } $in = '(' . implode( ' OR ', $parts ) . ')'; return 'not_in' === $op ? "NOT {$in}" : $in; } private static function entry_source( $kind, array $ids, $days = 0 ) { global $wpdb; $ids = array_values( array_unique( array_filter( array_map( 'intval', $ids ) ) ) ); if ( ! $ids ) { return null; } $in = implode( ',', $ids ); $since = $days > 0 ? gmdate( 'Y-m-d H:i:s', time() - min( 3650, (int) $days ) * DAY_IN_SECONDS ) : ''; if ( 'competition' === $kind ) { $t = $wpdb->prefix . 'aieo_competition_participants'; if ( ! self::table( $t ) ) { return null; } $when = '' !== $since ? $wpdb->prepare( ' AND cp.created_at >= %s', $since ) : ''; $base = "FROM `{$t}` cp WHERE cp.competition_id IN ({$in}) AND cp.status = 'confirmed'{$when}"; return array( 'users' => "SELECT cp.wp_user_id {$base} AND cp.wp_user_id > 0", 'emails' => "SELECT SHA2(LOWER(TRIM(cp.email)), 256) {$base} AND LOCATE('@', cp.email) > 0", ); } if ( 'form' === $kind ) { $t = class_exists( 'AIEO_Form_CPT' ) ? AIEO_Form_CPT::entries_table() : $wpdb->prefix . 'aieo_form_entries'; if ( ! self::table( $t ) ) { return null; } $when = '' !== $since ? $wpdb->prepare( ' AND fe.created_at >= %s', $since ) : ''; $base = "FROM `{$t}` fe WHERE fe.form_id IN ({$in}){$when}"; return array( 'users' => "SELECT fe.user_id {$base} AND fe.user_id > 0", 'emails' => self::column( $t, 'email' ) ? "SELECT SHA2(fe.email, 256) {$base} AND LOCATE('@', fe.email) > 0" : '', ); } if ( in_array( $kind, self::CLICK_FIELDS, true ) ) { $where = self::dispatch_where( $kind, $ids, $since ); if ( null === $where ) { return null; } $t = $wpdb->prefix . 'aieo_push_dispatch'; $base = "FROM `{$t}` d WHERE {$where}"; $emails = "SELECT SHA2(LOWER(TRIM(d.recipient_email)), 256) AS h {$base} AND LOCATE('@', d.recipient_email) > 0"; $subs = 'newsletter' !== $kind ? self::push_identities() : ''; if ( '' !== $subs ) { $emails = "SELECT z.h FROM ( {$emails} UNION SELECT SHA2(LOWER(TRIM(s.identified_email)), 256) AS h FROM `{$t}` d JOIN `{$subs}` s ON s.subscription_id = d.subscription_id WHERE {$where} AND d.subscription_id > 0 AND d.user_id = 0 AND d.recipient_email = '' AND LOCATE('@', s.identified_email) > 0 ) z"; } return array( 'users' => "SELECT d.user_id {$base} AND d.user_id > 0", 'emails' => $emails, ); } return null; } private static function dispatch_where( $kind, array $ids, $since = '' ) { global $wpdb; $t = $wpdb->prefix . 'aieo_push_dispatch'; if ( ! self::table( $t ) ) { return null; } $triggers = array(); foreach ( array_values( array_unique( array_filter( array_map( 'intval', $ids ) ) ) ) as $id ) { if ( 'newsletter' === $kind ) { if ( isset( self::NEWSLETTERS[ $id ] ) ) { $triggers[] = "'" . self::NEWSLETTERS[ $id ] . "'"; } } elseif ( $id > 0 ) { $triggers[] = "'wf_" . $id . "'"; } } if ( ! $triggers ) { return null; } $where = 'd.trigger_id IN (' . implode( ',', $triggers ) . ')'; if ( 'campaign' === $kind && self::column( $t, 'opened_at' ) ) { return $where . ( '' !== $since ? $wpdb->prepare( ' AND ( d.clicked_at >= %s OR d.opened_at >= %s )', $since, $since ) : ' AND ( d.clicked_at IS NOT NULL OR d.opened_at IS NOT NULL )' ); } if ( 'push' === $kind ) { $where .= " AND d.channel = 'push'"; } return $where . ( '' !== $since ? $wpdb->prepare( ' AND d.clicked_at >= %s', $since ) : ' AND d.clicked_at IS NOT NULL' ); } private static function push_identities() { global $wpdb; $s = $wpdb->prefix . 'aieo_push_subscriptions'; return self::table( $s ) && self::column( $s, 'identified_email' ) ? $s : ''; } public static function names_entries( $rule ) { $named = self::entry_ids( $rule ); return ( isset( $named['competition'] ) || isset( $named['form'] ) ) && ! array_intersect_key( $named, array_flip( self::CLICK_FIELDS ) ); } public static function entry_ids( $rule ) { $out = array(); $walk = static function ( $node ) use ( &$walk, &$out ) { if ( ! is_array( $node ) ) { return; } if ( isset( $node['conditions'] ) && is_array( $node['conditions'] ) ) { foreach ( $node['conditions'] as $child ) { $walk( $child ); } return; } $field = (string) ( $node['field'] ?? '' ); $kind = self::ENTRY_FIELDS[ $field ] ?? self::CLICK_FIELDS[ $field ] ?? ''; if ( '' === $kind ) { return; } foreach ( self::int_list( $node['value'] ?? array() ) as $id ) { if ( $id > 0 ) { $out[ $kind ][ $id ] = $id; } } }; $walk( $rule ); foreach ( $out as $kind => $ids ) { $ids = array_values( $ids ); sort( $ids, SORT_NUMERIC ); $out[ $kind ] = $ids; } ksort( $out ); return $out; } private static function beyond_from( $rule, array $person = array() ) { global $wpdb; $named = self::entry_ids( $rule ); $ci = $wpdb->prefix . 'aieo_customer_intelligence'; if ( ! $named || ! self::table( $ci ) ) { return ''; } $cp = $wpdb->prefix . 'aieo_competition_participants'; $cp = ! empty( $named['competition'] ) && self::table( $cp ) ? $cp : ''; $fe = class_exists( 'AIEO_Form_CPT' ) ? AIEO_Form_CPT::entries_table() : $wpdb->prefix . 'aieo_form_entries'; $fe = ! empty( $named['form'] ) && self::table( $fe ) ? $fe : ''; $clicks = array(); foreach ( self::CLICK_FIELDS as $kind ) { $where = empty( $named[ $kind ] ) ? null : self::dispatch_where( $kind, $named[ $kind ] ); if ( null !== $where ) { $clicks[ $kind ] = $where; } } if ( '' === $cp && '' === $fe && ! $clicks ) { return ''; } $email = isset( $person['email'] ) ? strtolower( trim( (string) $person['email'] ) ) : ''; $uid = isset( $person['user_id'] ) ? max( 0, (int) $person['user_id'] ) : 0; if ( '' !== $email || $uid > 0 ) { return self::beyond_one( $named, $cp, $fe, $ci, $uid, $email, $clicks ); } $users = $wpdb->users; $parts = array(); if ( '' !== $cp ) { $parts[] = "SELECT SHA2(LOWER(TRIM(cp.email)), 256) AS h, CAST(LOWER(TRIM(cp.email)) AS BINARY) AS e, cp.wp_user_id AS uid, cp.participant_id AS i FROM `{$cp}` cp WHERE cp.competition_id IN (" . implode( ',', $named['competition'] ) . ") AND cp.status = 'confirmed' AND LOCATE('@', cp.email) > 0"; } if ( '' !== $fe ) { $forms = implode( ',', $named['form'] ); $no_mail = ''; if ( self::column( $fe, 'email' ) ) { $parts[] = "SELECT SHA2(fe.email, 256) AS h, CAST(fe.email AS BINARY) AS e, fe.user_id AS uid, fe.id + " . self::FORM_KEY_BASE . " AS i FROM `{$fe}` fe WHERE fe.form_id IN ({$forms}) AND LOCATE('@', fe.email) > 0"; $no_mail = " AND fe.email = ''"; } $parts[] = "SELECT SHA2(LOWER(TRIM(u.user_email)), 256) AS h, CAST(LOWER(TRIM(u.user_email)) AS BINARY) AS e, fe.user_id AS uid, fe.id + " . self::FORM_KEY_BASE . " AS i FROM `{$fe}` fe JOIN `{$users}` u ON u.ID = fe.user_id WHERE fe.form_id IN ({$forms}) AND fe.user_id > 0{$no_mail}"; } $pd = $wpdb->prefix . 'aieo_push_dispatch'; $subs = $clicks ? self::push_identities() : ''; $dk = self::DISPATCH_KEY_BASE; foreach ( $clicks as $where ) { $parts[] = "SELECT SHA2(LOWER(TRIM(d.recipient_email)), 256) AS h, CAST(LOWER(TRIM(d.recipient_email)) AS BINARY) AS e, d.user_id AS uid, d.dispatch_id + {$dk} AS i FROM `{$pd}` d WHERE {$where} AND LOCATE('@', d.recipient_email) > 0"; $parts[] = "SELECT SHA2(LOWER(TRIM(u.user_email)), 256) AS h, CAST(LOWER(TRIM(u.user_email)) AS BINARY) AS e, d.user_id AS uid, d.dispatch_id + {$dk} AS i FROM `{$pd}` d JOIN `{$users}` u ON u.ID = d.user_id WHERE {$where} AND d.user_id > 0 AND d.recipient_email = ''"; if ( '' !== $subs ) { $parts[] = "SELECT SHA2(LOWER(TRIM(s.identified_email)), 256) AS h, CAST(LOWER(TRIM(s.identified_email)) AS BINARY) AS e, 0 AS uid, d.dispatch_id + {$dk} AS i FROM `{$pd}` d JOIN `{$subs}` s ON s.subscription_id = d.subscription_id WHERE {$where} AND d.user_id = 0 AND d.subscription_id > 0 AND d.recipient_email = '' AND LOCATE('@', s.identified_email) > 0"; } } $people = "SELECT k.r AS uid, MIN(k.e) AS e, MIN(k.i) AS i FROM ( SELECT q.h, q.e, q.i, CASE WHEN q.uid > 0 THEN q.uid ELSE COALESCE(ua.ID, 0) END AS r FROM ( " . implode( ' UNION ALL ', $parts ) . " ) q LEFT JOIN ( SELECT MIN(ID) AS ID, SHA2(LOWER(TRIM(user_email)), 256) AS h FROM `{$users}` GROUP BY h ) ua ON q.uid = 0 AND ua.h = q.h WHERE q.e <> '' AND q.h NOT IN ( SELECT SHA2(LOWER(TRIM(x.email)), 256) FROM `{$ci}` x WHERE x.email <> '' ) ) k WHERE k.r = 0 OR k.r NOT IN ( SELECT x.wp_user_id FROM `{$ci}` x WHERE x.wp_user_id > 0 ) GROUP BY k.r, CASE WHEN k.r > 0 THEN '' ELSE k.h END"; return self::beyond_table( "( {$people} ) g" ); } private static function beyond_one( array $named, $cp, $fe, $ci, $uid, $email, array $clicks = array() ) { global $wpdb; $users = $wpdb->users; $same = static function ( $col, $norm ) use ( $wpdb, $email ) { return $wpdb->prepare( " AND {$col} = %s AND SHA2({$norm}, 256) = SHA2(%s, 256)", $email, $email ); }; $parts = array(); if ( '' !== $cp ) { $sel = "SELECT CAST(LOWER(TRIM(cp.email)) AS BINARY) AS e, cp.wp_user_id AS uid FROM `{$cp}` cp WHERE cp.competition_id IN (" . implode( ',', $named['competition'] ) . ") AND cp.status = 'confirmed' AND LOCATE('@', cp.email) > 0"; if ( '' !== $email ) { $parts[] = $sel . $same( 'cp.email', 'LOWER(TRIM(cp.email))' ); } if ( $uid > 0 ) { $parts[] = $sel . $wpdb->prepare( ' AND cp.wp_user_id = %d', $uid ); } } if ( '' !== $fe ) { $forms = implode( ',', $named['form'] ); $own = self::column( $fe, 'email' ); if ( $own ) { $sel = "SELECT CAST(fe.email AS BINARY) AS e, fe.user_id AS uid FROM `{$fe}` fe WHERE fe.form_id IN ({$forms}) AND LOCATE('@', fe.email) > 0"; if ( '' !== $email ) { $parts[] = $sel . $same( 'fe.email', 'fe.email' ); } if ( $uid > 0 ) { $parts[] = $sel . $wpdb->prepare( ' AND fe.user_id = %d', $uid ); } } $who = array(); if ( $uid > 0 ) { $who[] = $wpdb->prepare( 'fe.user_id = %d', $uid ); } if ( '' !== $email ) { $who[] = '1' . $same( 'u.user_email', 'LOWER(TRIM(u.user_email))' ); } $parts[] = "SELECT CAST(LOWER(TRIM(u.user_email)) AS BINARY) AS e, fe.user_id AS uid FROM `{$fe}` fe JOIN `{$users}` u ON u.ID = fe.user_id WHERE fe.form_id IN ({$forms}) AND fe.user_id > 0" . ( $own ? " AND fe.email = ''" : '' ) . ' AND ( ' . implode( ' OR ', $who ) . ' )'; } $pd = $wpdb->prefix . 'aieo_push_dispatch'; $subs = $clicks ? self::push_identities() : ''; foreach ( $clicks as $where ) { $sel = "SELECT CAST(LOWER(TRIM(d.recipient_email)) AS BINARY) AS e, d.user_id AS uid FROM `{$pd}` d WHERE {$where} AND LOCATE('@', d.recipient_email) > 0"; if ( '' !== $email ) { $parts[] = $sel . $same( 'd.recipient_email', 'LOWER(TRIM(d.recipient_email))' ); } if ( $uid > 0 ) { $parts[] = $sel . $wpdb->prepare( ' AND d.user_id = %d', $uid ); } $who = array(); if ( $uid > 0 ) { $who[] = $wpdb->prepare( 'd.user_id = %d', $uid ); } if ( '' !== $email ) { $who[] = '1' . $same( 'u.user_email', 'LOWER(TRIM(u.user_email))' ); } $parts[] = "SELECT CAST(LOWER(TRIM(u.user_email)) AS BINARY) AS e, d.user_id AS uid FROM `{$pd}` d JOIN `{$users}` u ON u.ID = d.user_id WHERE {$where} AND d.user_id > 0 AND d.recipient_email = '' AND ( " . implode( ' OR ', $who ) . ' )'; if ( '' !== $subs && '' !== $email ) { $parts[] = "SELECT CAST(LOWER(TRIM(s.identified_email)) AS BINARY) AS e, 0 AS uid FROM `{$pd}` d JOIN `{$subs}` s ON s.subscription_id = d.subscription_id WHERE {$where} AND d.user_id = 0 AND d.subscription_id > 0 AND d.recipient_email = '' AND LOCATE('@', s.identified_email) > 0" . $same( 's.identified_email', 'LOWER(TRIM(s.identified_email))' ); } } $found = array(); foreach ( (array) $wpdb->get_results( implode( ' UNION ALL ', $parts ), ARRAY_A ) as $row ) { $e = (string) $row['e']; if ( '' !== $e ) { $found[] = array( $e, (int) $row['uid'], hash( 'sha256', $e ) ); } } if ( ! $found ) { return ''; } $addrs = array_values( array_unique( array_column( $found, 0 ) ) ); $in = implode( ',', array_fill( 0, count( $addrs ), '%s' ) ); $customer = array_flip( array_map( 'strval', (array) $wpdb->get_col( $wpdb->prepare( "SELECT SHA2(LOWER(TRIM(x.email)), 256) FROM `{$ci}` x WHERE x.email IN ({$in})", $addrs ) ) ) ); $bare = array(); $account = array(); foreach ( $found as $f ) { if ( 0 === $f[1] && ! isset( $customer[ $f[2] ] ) ) { $bare[ $f[0] ] = $f[0]; } } if ( $bare ) { $in = implode( ',', array_fill( 0, count( $bare ), '%s' ) ); foreach ( (array) $wpdb->get_results( $wpdb->prepare( "SELECT u.ID, SHA2(LOWER(TRIM(u.user_email)), 256) AS h FROM `{$users}` u WHERE u.user_email IN ({$in})", array_values( $bare ) ), ARRAY_A ) as $row ) { $h = (string) $row['h']; if ( ! isset( $account[ $h ] ) || (int) $row['ID'] < $account[ $h ] ) { $account[ $h ] = (int) $row['ID']; } } } $people = array(); foreach ( $found as $f ) { if ( isset( $customer[ $f[2] ] ) ) { continue; } $r = $f[1] > 0 ? $f[1] : ( $account[ $f[2] ] ?? 0 ); $key = $r > 0 ? 'u' . $r : 'e' . $f[2]; if ( ! isset( $people[ $key ] ) || strcmp( $f[0], $people[ $key ][1] ) < 0 ) { $people[ $key ] = array( $r, $f[0] ); } } $ids = array_values( array_filter( array_unique( array_column( $people, 0 ) ) ) ); if ( $ids ) { $known = array_flip( array_map( 'intval', (array) $wpdb->get_col( "SELECT DISTINCT x.wp_user_id FROM `{$ci}` x WHERE x.wp_user_id IN (" . implode( ',', array_map( 'intval', $ids ) ) . ')' ) ) ); foreach ( $people as $key => $p ) { if ( $p[0] > 0 && isset( $known[ $p[0] ] ) ) { unset( $people[ $key ] ); } } } if ( ! $people ) { return ''; } $rows = array(); foreach ( $people as $p ) { $rows[] = 'SELECT ' . (int) $p[0] . ' AS uid, CAST(' . $wpdb->prepare( '%s', $p[1] ) . ' AS BINARY) AS e, 0 AS i'; } return self::beyond_table( '( ' . implode( ' UNION ALL ', $rows ) . ' ) g' ); } private static function beyond_table( $g ) { $cols = array(); foreach ( self::ci_columns() as $col ) { if ( 'eponymous_id' === $col ) { $cols[] = '0 AS `eponymous_id`'; } elseif ( 'wp_user_id' === $col ) { $cols[] = 'g.uid AS `wp_user_id`'; } elseif ( 'email' === $col ) { $cols[] = 'g.e AS `email`'; } else { $cols[] = 'NULL AS `' . $col . '`'; } } $cols[] = 'g.i AS `_k`'; return '( SELECT ' . implode( ', ', $cols ) . " FROM {$g} WHERE g.e <> '' ) " . self::CI_ALIAS; } private static $ci_columns = null; private static function ci_columns() { global $wpdb; if ( null === self::$ci_columns ) { $ci = $wpdb->prefix . 'aieo_customer_intelligence'; self::$ci_columns = array_values( array_filter( array_map( 'strval', (array) $wpdb->get_col( "SHOW COLUMNS FROM `{$ci}`" ) ) ) ); } return self::$ci_columns; } public static function forget_request() { self::$ci_columns = null; self::$present = array(); } public static function guest_members( $rule, $after = '', $limit = 200 ) { global $wpdb; $ci = $wpdb->prefix . 'aieo_customer_intelligence'; if ( ! self::table( $ci ) ) { return array(); } $where = str_replace( '%', '%%', self::compile( $rule ) ); $a = self::CI_ALIAS; $limit = max( 1, min( 1000, (int) $limit ) ); $sql = "SELECT LOWER(TRIM({$a}.email)) AS e FROM `{$ci}` {$a} WHERE ({$where}) AND {$a}.wp_user_id = 0 AND {$a}.email <> ''"; $more = str_replace( '%', '%%', self::beyond_from( $rule ) ); if ( '' !== $more ) { $sql .= " UNION SELECT {$a}.email AS e FROM {$more} WHERE ({$where}) AND {$a}.wp_user_id = 0"; } $rows = $wpdb->get_col( $wpdb->prepare( "SELECT x.e FROM ( {$sql} ) x WHERE x.e > %s ORDER BY x.e ASC LIMIT {$limit}", (string) $after ) ); return array_values( array_filter( array_map( 'strval', (array) $rows ) ) ); } const FORM_KEY_BASE = 1099511627776; const DISPATCH_KEY_BASE = 2199023255552; const BEYOND_KEY_BASE = 4611686018427387904; public static function guest_members_keyed( $rule, $after = 0, $limit = 200 ) { global $wpdb; $ci = $wpdb->prefix . 'aieo_customer_intelligence'; if ( ! self::table( $ci ) ) { return array(); } $where = self::compile( $rule ); $a = self::CI_ALIAS; $limit = max( 1, min( 1000, (int) $limit ) ); $after = preg_replace( '/[^0-9]/', '', (string) $after ); $after = '' === $after ? '0' : $after; $sql = "SELECT CAST({$a}.eponymous_id AS UNSIGNED) AS k, LOWER(TRIM({$a}.email)) AS e FROM `{$ci}` {$a} WHERE ({$where}) AND {$a}.wp_user_id = 0 AND {$a}.email <> ''"; $more = self::beyond_from( $rule ); if ( '' !== $more ) { $sql .= " UNION ALL SELECT CAST({$a}._k AS UNSIGNED) + " . self::BEYOND_KEY_BASE . " AS k, {$a}.email AS e FROM {$more} WHERE ({$where}) AND {$a}.wp_user_id = 0"; } $rows = (array) $wpdb->get_results( "SELECT x.k, x.e FROM ( {$sql} ) x WHERE x.k > {$after} ORDER BY x.k ASC LIMIT {$limit}", ARRAY_A ); $out = array(); foreach ( $rows as $r ) { $email = strtolower( trim( (string) $r['e'] ) ); if ( '' !== $email ) { $out[] = array( 'k' => (string) $r['k'], 'email' => $email ); } } return $out; } private static function compile_behaviour( $field, $cnd ) { global $wpdb; $a = self::CI_ALIAS; $subj = $wpdb->prefix . 'aieo_subjects'; $isum = aieo_interaction_summary_table(); if ( 'instigator' === $field ) { if ( ! self::table( $subj ) || ! self::table( $isum ) ) { return '1=0'; } $days = 14; return "{$a}.wp_user_id IN ( SELECT DISTINCT s.wp_user_id FROM `{$subj}` s JOIN `{$isum}` i ON i.subject_id = s.subject_id WHERE s.wp_user_id > 0 AND s.merged_into = 0 AND i.item_type='product' AND i.conversions = 0 AND (i.carts > 0 OR i.wishlists > 0 OR i.weight >= 4) AND i.last_at >= (UTC_TIMESTAMP() - INTERVAL {$days} DAY) )"; } if ( 'image_engaged' === $field ) { if ( ! self::table( $subj ) || ! self::table( $isum ) ) { return '1=0'; } $min = max( 1, min( 50, (int) ( $cnd['min'] ?? 1 ) ) ); $days = max( 1, min( 365, (int) ( $cnd['days'] ?? 90 ) ) ); return "{$a}.wp_user_id IN ( SELECT DISTINCT s.wp_user_id FROM `{$subj}` s JOIN `{$isum}` i ON i.subject_id = s.subject_id WHERE s.wp_user_id > 0 AND s.merged_into = 0 AND i.image_clicks >= {$min} AND i.last_at >= (UTC_TIMESTAMP() - INTERVAL {$days} DAY) )"; } if ( 'lookalike' === $field ) { $seed = (int) ( $cnd['seed_subject_id'] ?? 0 ); if ( $seed <= 0 || ! class_exists( 'AIEO_Behavioural_Reco' ) ) { return '1=0'; } $n = (int) ( $cnd['limit'] ?? 500 ); $looks = AIEO_Behavioural_Reco::lookalikes( $seed, $n ); $sids = array(); foreach ( (array) $looks as $l ) { $sids[] = (int) $l['subject_id']; } if ( empty( $sids ) ) { return '1=0'; } $in = implode( ',', array_map( 'intval', $sids ) ); return "{$a}.wp_user_id IN ( SELECT wp_user_id FROM `{$subj}` WHERE subject_id IN ({$in}) AND wp_user_id > 0 )"; } return '1=0'; } public static function count( $rule ) { global $wpdb; $ci = $wpdb->prefix . 'aieo_customer_intelligence'; if ( ! self::table( $ci ) ) { return 0; } $where = self::compile( $rule ); $n = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$ci}` " . self::CI_ALIAS . " WHERE ({$where})" ); $more = self::beyond_from( $rule ); if ( '' !== $more ) { $n += (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$more} WHERE ({$where})" ); } return $n; } public static function audience_stats( $rule ) { global $wpdb; $ci = $wpdb->prefix . 'aieo_customer_intelligence'; if ( ! self::table( $ci ) ) { return array( 'count' => 0, 'contactable' => 0, 'avg_aov' => 0.0 ); } $where = self::compile( $rule ); $a = self::CI_ALIAS; $reach = self::reach_sql( $a ); $row = $wpdb->get_row( "SELECT COUNT(*) c, SUM(CASE WHEN {$reach} THEN 1 ELSE 0 END) contactable, AVG(NULLIF(CASE WHEN {$reach} THEN {$a}.aov END, 0)) aov FROM `{$ci}` {$a} WHERE ({$where})", ARRAY_A ); $out = array( 'count' => (int) ( $row['c'] ?? 0 ), 'contactable' => (int) ( $row['contactable'] ?? 0 ), 'avg_aov' => (float) ( $row['aov'] ?? 0 ), ); $more = self::beyond_from( $rule ); if ( '' !== $more ) { $extra = $wpdb->get_row( "SELECT COUNT(*) c, SUM(CASE WHEN {$reach} THEN 1 ELSE 0 END) contactable FROM {$more} WHERE ({$where})", ARRAY_A ); $out['count'] += (int) ( $extra['c'] ?? 0 ); $out['contactable'] += (int) ( $extra['contactable'] ?? 0 ); } return $out; } private static function reach_sql( $a ) { global $wpdb; $consent = class_exists( 'AIEO_Contact_Consent' ) ? AIEO_Contact_Consent::consent_sql( "{$a}.email" ) : '0'; $email = "( ( {$a}.tnp_status = 'C' OR {$consent} ) AND {$a}.email <> '' )"; $supp = $wpdb->prefix . 'aieo_email_suppression'; if ( self::table( $supp ) ) { $email = "( {$email} AND NOT EXISTS ( SELECT 1 FROM `{$supp}` sp WHERE sp.email_hash = SHA1( LOWER( TRIM( {$a}.email ) ) ) ) )"; } elseif ( '1' === get_option( 'aieo_aw_suppression_schema', '' ) ) { $email = '0'; } return "( {$email} OR " . self::push_reach_sql( $a ) . ' )'; } private static function push_reach_sql( $a ) { global $wpdb; $push_tbl = $wpdb->prefix . 'aieo_push_subscriptions'; if ( ! self::table( $push_tbl ) ) { return '0'; } $by_email = self::column( $push_tbl, 'identified_email' ) ? " OR ({$a}.email <> '' AND ps.identified_email = {$a}.email)" : ''; return "EXISTS ( SELECT 1 FROM `{$push_tbl}` ps WHERE ps.status = 'active' AND ps.endpoint <> '' AND ( ({$a}.wp_user_id > 0 AND ps.user_id = {$a}.wp_user_id){$by_email} ) )"; } public static function preview( $rule, $limit = 8 ) { global $wpdb; $ci = $wpdb->prefix . 'aieo_customer_intelligence'; if ( ! self::table( $ci ) ) { return array(); } $where = self::compile( $rule ); $limit = max( 1, min( 25, (int) $limit ) ); $a = self::CI_ALIAS; $rows = (array) $wpdb->get_results( "SELECT {$a}.eponymous_id, {$a}.wp_user_id, {$a}.email FROM `{$ci}` {$a} WHERE ({$where}) ORDER BY {$a}.ltv_total DESC LIMIT {$limit}", ARRAY_A ); $more = count( $rows ) < $limit ? self::beyond_from( $rule ) : ''; if ( '' !== $more ) { $left = $limit - count( $rows ); $rows = array_merge( $rows, (array) $wpdb->get_results( "SELECT {$a}.eponymous_id, {$a}.wp_user_id, {$a}.email FROM {$more} WHERE ({$where}) ORDER BY {$a}.email ASC LIMIT {$left}", ARRAY_A ) ); } $out = array(); foreach ( (array) $rows as $r ) { $name = ''; if ( (int) $r['wp_user_id'] > 0 ) { $u = get_userdata( (int) $r['wp_user_id'] ); $name = $u ? (string) $u->display_name : ''; } $out[] = array( 'eponymous_id' => (int) $r['eponymous_id'], 'email' => (string) $r['email'], 'name' => $name, ); } return $out; } public static function members( $rule, $after = 0, $limit = 200 ) { global $wpdb; $ci = $wpdb->prefix . 'aieo_customer_intelligence'; if ( ! self::table( $ci ) ) { return array(); } $where = self::compile( $rule ); $where = str_replace( '%', '%%', $where ); $a = self::CI_ALIAS; $limit = max( 1, min( 1000, (int) $limit ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT {$a}.eponymous_id, {$a}.wp_user_id, {$a}.email FROM `{$ci}` {$a} WHERE ({$where}) AND {$a}.eponymous_id > %d ORDER BY {$a}.eponymous_id ASC LIMIT {$limit}", (int) $after ), ARRAY_A ); $out = array(); foreach ( (array) $rows as $r ) { $out[] = array( 'eponymous_id' => (int) $r['eponymous_id'], 'wp_user_id' => (int) $r['wp_user_id'], 'email' => (string) $r['email'], ); } return $out; } public static function reach( $rule ) { global $wpdb; $ci = $wpdb->prefix . 'aieo_customer_intelligence'; $out = array( 'total' => 0, 'emailable' => 0, 'with_account' => 0, 'consented' => 0, 'push' => 0 ); if ( ! self::table( $ci ) ) { return $out; } $where = self::compile( $rule ); $a = self::CI_ALIAS; $push_sel = "SUM(CASE WHEN " . self::push_reach_sql( $a ) . " THEN 1 ELSE 0 END)"; $row = $wpdb->get_row( "SELECT COUNT(*) total, SUM(CASE WHEN {$a}.email <> '' THEN 1 ELSE 0 END) emailable, SUM(CASE WHEN {$a}.wp_user_id > 0 THEN 1 ELSE 0 END) with_account, SUM(CASE WHEN {$a}.tnp_status = 'C' THEN 1 ELSE 0 END) consented, {$push_sel} push FROM `{$ci}` {$a} WHERE ({$where})", ARRAY_A ); foreach ( array_keys( $out ) as $k ) { $out[ $k ] = (int) ( $row[ $k ] ?? 0 ); } $more = self::beyond_from( $rule ); if ( '' !== $more ) { $extra = $wpdb->get_row( "SELECT COUNT(*) total, SUM(CASE WHEN {$a}.email <> '' THEN 1 ELSE 0 END) emailable, SUM(CASE WHEN {$a}.wp_user_id > 0 THEN 1 ELSE 0 END) with_account, 0 consented, {$push_sel} push FROM {$more} WHERE ({$where})", ARRAY_A ); foreach ( array_keys( $out ) as $k ) { $out[ $k ] += (int) ( $extra[ $k ] ?? 0 ); } } return $out; } public static function member_user_ids( $rule, $after_id = 0, $limit = 200 ) { global $wpdb; $ci = $wpdb->prefix . 'aieo_customer_intelligence'; if ( ! self::table( $ci ) ) { return array(); } $where = self::compile( $rule ); $where = str_replace( '%', '%%', $where ); $a = self::CI_ALIAS; $limit = max( 1, min( 1000, (int) $limit ) ); $more = str_replace( '%', '%%', self::beyond_from( $rule ) ); if ( '' !== $more ) { $ids = $wpdb->get_col( $wpdb->prepare( "SELECT x.u FROM ( SELECT {$a}.wp_user_id AS u FROM `{$ci}` {$a} WHERE ({$where}) AND {$a}.wp_user_id > %d UNION SELECT {$a}.wp_user_id FROM {$more} WHERE ({$where}) AND {$a}.wp_user_id > %d ) x ORDER BY x.u ASC LIMIT {$limit}", (int) $after_id, (int) $after_id ) ); return array_map( 'intval', (array) $ids ); } $ids = $wpdb->get_col( $wpdb->prepare( "SELECT {$a}.wp_user_id FROM `{$ci}` {$a} WHERE ({$where}) AND {$a}.wp_user_id > %d ORDER BY {$a}.wp_user_id ASC LIMIT {$limit}", (int) $after_id ) ); return array_map( 'intval', (array) $ids ); } public static function matches_user( $rule, $wp_user_id ) { global $wpdb; $wp_user_id = (int) $wp_user_id; if ( $wp_user_id <= 0 ) { return false; } $ci = $wpdb->prefix . 'aieo_customer_intelligence'; if ( ! self::table( $ci ) ) { return false; } $where = self::compile( $rule ); $where = str_replace( '%', '%%', $where ); $a = self::CI_ALIAS; if ( (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$ci}` {$a} WHERE ({$where}) AND {$a}.wp_user_id = %d", $wp_user_id ) ) > 0 ) { return true; } $user = get_userdata( $wp_user_id ); $more = str_replace( '%', '%%', self::beyond_from( $rule, array( 'user_id' => $wp_user_id, 'email' => $user ? (string) $user->user_email : '' ) ) ); return '' !== $more && (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$more} WHERE ({$where}) AND {$a}.wp_user_id = %d", $wp_user_id ) ) > 0; } public static function matches_email( $rule, $email ) { global $wpdb; $email = strtolower( trim( (string) $email ) ); if ( '' === $email || ! is_email( $email ) ) { return false; } $ci = $wpdb->prefix . 'aieo_customer_intelligence'; if ( ! self::table( $ci ) ) { return false; } $where = self::compile( $rule ); $where = str_replace( '%', '%%', $where ); $a = self::CI_ALIAS; if ( (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$ci}` {$a} WHERE ({$where}) AND LOWER(TRIM({$a}.email)) = %s", $email ) ) > 0 ) { return true; } $more = str_replace( '%', '%%', self::beyond_from( $rule, array( 'email' => $email ) ) ); return '' !== $more && (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$more} WHERE ({$where}) AND {$a}.email = %s", $email ) ) > 0; } public static function describe_rule( $rule ) { if ( ! is_array( $rule ) || empty( $rule['conditions'] ) ) { return __( '(empty)', 'ai-eshop-optimizer' ); } $cat = self::field_catalog(); $op = ( isset( $rule['op'] ) && 'or' === strtolower( (string) $rule['op'] ) ) ? ' OR ' : ' AND '; $bits = array(); foreach ( $rule['conditions'] as $c ) { if ( ! is_array( $c ) ) { continue; } if ( isset( $c['conditions'] ) ) { $bits[] = '(' . self::describe_rule( $c ) . ')'; continue; } $f = (string) ( $c['field'] ?? '' ); if ( ! isset( $cat[ $f ] ) ) { continue; } $label = $cat[ $f ]['label']; if ( 'entry' === $cat[ $f ]['type'] ) { $bits[] = self::describe_entry( $f, $c ); continue; } if ( 'behaviour' === $cat[ $f ]['type'] ) { $bits[] = $label . ( 'lookalike' === $f && ! empty( $c['seed_subject_id'] ) ? ' #' . (int) $c['seed_subject_id'] : '' ); } else { $val = is_array( $c['value'] ?? null ) ? implode( ',', $c['value'] ) : (string) ( $c['value'] ?? '' ); if ( isset( $cat[ $f ]['enum_labels'][ $val ] ) ) { $val .= ' (' . $cat[ $f ]['enum_labels'][ $val ] . ')'; } $bits[] = $label . ' ' . (string) ( $c['op'] ?? '' ) . ' ' . $val; } } return implode( $op, $bits ); } public static function describe_entry( $field, array $c ) { if ( isset( self::CLICK_FIELDS[ $field ] ) ) { return self::describe_click( $field, $c ); } $names = array(); foreach ( self::int_list( $c['value'] ?? array() ) as $id ) { $title = $id > 0 ? html_entity_decode( wp_strip_all_tags( (string) get_the_title( $id ) ), ENT_QUOTES, 'UTF-8' ) : ''; $names[] = '' !== trim( $title ) ? '“' . $title . '”' : sprintf( __( 'number %d', 'ai-eshop-optimizer' ), $id ); } $list = implode( ', ', $names ); $not = 'not_in' === strtolower( (string) ( $c['op'] ?? 'in' ) ); if ( 'competition_entered' === $field ) { $text = $not ? sprintf( __( 'Did not enter %s', 'ai-eshop-optimizer' ), $list ) : sprintf( __( 'Entered %s', 'ai-eshop-optimizer' ), $list ); } else { $text = $not ? sprintf( __( 'Did not fill in %s', 'ai-eshop-optimizer' ), $list ) : sprintf( __( 'Filled in %s', 'ai-eshop-optimizer' ), $list ); } $days = isset( $c['days'] ) ? (int) $c['days'] : 0; if ( $days > 0 ) { $text = sprintf( _n( '%1$s, in the last %2$d day', '%1$s, in the last %2$d days', $days, 'ai-eshop-optimizer' ), $text, $days ); } return $text; } private static function describe_click( $field, array $c ) { $names = array(); foreach ( self::int_list( $c['value'] ?? array() ) as $id ) { $title = ''; if ( 'newsletter_clicked' === $field ) { $title = isset( self::NEWSLETTERS[ $id ] ) ? __( 'Newsletter digest', 'ai-eshop-optimizer' ) : ''; } elseif ( $id > 0 && class_exists( 'AIEO_Workflows' ) ) { $wf = AIEO_Workflows::get( $id ); $title = is_array( $wf ) ? (string) ( $wf['name'] ?? '' ) : ''; } $names[] = '' !== trim( $title ) ? '“' . $title . '”' : sprintf( __( 'number %d', 'ai-eshop-optimizer' ), $id ); } $list = implode( ', ', $names ); $not = 'not_in' === strtolower( (string) ( $c['op'] ?? 'in' ) ); if ( 'newsletter_clicked' === $field ) { $text = $not ? sprintf( __( 'Did not click a link in the newsletter %s', 'ai-eshop-optimizer' ), $list ) : sprintf( __( 'Clicked a link in the newsletter %s', 'ai-eshop-optimizer' ), $list ); } elseif ( 'campaign_engaged' === $field ) { $text = $not ? sprintf( __( 'Did not open or click the campaign %s', 'ai-eshop-optimizer' ), $list ) : sprintf( __( 'Opened or clicked the campaign %s', 'ai-eshop-optimizer' ), $list ); } else { $text = $not ? sprintf( __( 'Did not click a push notification of the campaign %s', 'ai-eshop-optimizer' ), $list ) : sprintf( __( 'Clicked a push notification of the campaign %s', 'ai-eshop-optimizer' ), $list ); } $days = isset( $c['days'] ) ? (int) $c['days'] : 0; if ( $days > 0 ) { $text = sprintf( _n( '%1$s, in the last %2$d day', '%1$s, in the last %2$d days', $days, 'ai-eshop-optimizer' ), $text, $days ); } return $text; } private static function entry_choices_for_prompt() { $lines = array(); foreach ( array( 'aieo_competition' => 'COMPETITIONS (for competition_entered)', 'aieo_form' => 'FORMS (for form_submitted)' ) as $type => $head ) { if ( ! post_type_exists( $type ) ) { continue; } $posts = get_posts( array( 'post_type' => $type, 'post_status' => array( 'publish', 'future', 'private' ), 'numberposts' => 30, 'orderby' => 'date', 'order' => 'DESC', 'no_found_rows' => true ) ); if ( ! $posts ) { continue; } $items = array(); foreach ( $posts as $p ) { $items[] = (int) $p->ID . ' = ' . wp_strip_all_tags( (string) $p->post_title ); } $lines[] = $head . ': ' . implode( '; ', $items ); } return $lines ? "\n" . implode( "\n", $lines ) . "\nAn entry leaf is {\"field\":\"competition_entered\"|\"form_submitted\",\"op\":\"in\"|\"not_in\",\"value\":[ids],\"days\":}." : ''; } public static function from_nl( $text ) { if ( ! class_exists( 'AI_eShop_Chat_Anthropic_API' ) ) { return array( 'ok' => false, 'rule' => array(), 'error' => 'chat_api_unavailable' ); } $cat = self::field_catalog(); $lines = array(); foreach ( $cat as $f => $d ) { $enum = ''; if ( isset( $d['enum'] ) ) { $vals = array(); foreach ( (array) $d['enum'] as $ev ) { $vals[] = isset( $d['enum_labels'][ $ev ] ) ? $ev . ' = ' . $d['enum_labels'][ $ev ] : $ev; } $enum = ' — values: ' . implode( '|', $vals ); } $help = isset( $d['help'] ) && '' !== $d['help'] ? ' — ' . $d['help'] : ''; $lines[] = "- \"{$f}\" ({$d['type']}, ops: " . implode( '/', $d['ops'] ) . "): {$d['label']}{$help}{$enum}"; } $system = "You convert a plain-English customer-audience description into a JSON rule tree. Output ONLY valid JSON, no prose.\n" . "Schema: {\"op\":\"and\"|\"or\",\"conditions\":[ | ]}. " . "A leaf is {\"field\":\"\",\"op\":\"\",\"value\":}. " . "For behaviour fields use {\"field\":\"instigator\",\"op\":\"is\"} or {\"field\":\"lookalike\",\"op\":\"is\",\"seed_subject_id\":}. " . "For a 'segments' membership use op 'contains' with a single value. Use ONLY the fields below; if the request needs something unavailable, omit it.\n" . "FIELDS:\n" . implode( "\n", $lines ) . self::entry_choices_for_prompt() . "\nDORMANCY: express it ONLY with \"dormancy_cohort\" (calendar staleness since the last order: " . "active = under 60 days, drifting = 60-90, lapsed = 90-180, long_dormant = 180-365, lost = 365+). " . "There is no 'dormant' segment — never put \"dormant\" in a segments leaf. " . "The \"overdue\" SEGMENT is a different idea: overdue against that customer's OWN reorder cadence " . "(a weekly buyer silent 3 weeks), which can fire long before any dormancy cohort does.\n" . "Example — 'lapsed VIPs with LTV over 100 who aren't discount hunters': " . "{\"op\":\"and\",\"conditions\":[{\"field\":\"dormancy_cohort\",\"op\":\"=\",\"value\":\"lapsed\"},{\"field\":\"ltv_total\",\"op\":\">\",\"value\":100},{\"field\":\"discount_share\",\"op\":\"<\",\"value\":0.4}]}"; $res = AI_eShop_Chat_Anthropic_API::get_instance()->chat_completion( array( array( 'role' => 'user', 'content' => (string) $text ) ), array( 'system' => $system, 'model' => (string) get_option( 'aieo_chat_model', 'claude-opus-5-5' ), 'max_tokens' => 800, 'temperature' => 0.1, 'effort' => 'low', 'source' => 'aieo.chat.audience', ) ); if ( is_wp_error( $res ) ) { return array( 'ok' => false, 'rule' => array(), 'error' => $res->get_error_message() ); } $txt = AI_eShop_Chat_Anthropic_API::text_of( $res ); $rule = self::extract_json( $txt ); if ( ! is_array( $rule ) || empty( $rule['conditions'] ) ) { return array( 'ok' => false, 'rule' => array(), 'error' => 'could_not_parse_rule' ); } return array( 'ok' => true, 'rule' => $rule, 'error' => '' ); } private static function extract_json( $txt ) { $txt = trim( $txt ); if ( '' === $txt ) { return null; } $txt = preg_replace( '/^```(?:json)?\s*|\s*```$/m', '', $txt ); $start = strpos( $txt, '{' ); $end = strrpos( $txt, '}' ); if ( false === $start || false === $end || $end <= $start ) { return null; } $d = json_decode( substr( $txt, $start, $end - $start + 1 ), true ); return is_array( $d ) ? $d : null; } public static function suggestions() { $defs = array( array( 'label' => __( 'In-market now (unfulfilled intent)', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'instigator', 'op' => 'is' ) ) ) ), array( 'label' => __( 'High-value dormant (180+ days)', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'dormancy_cohort', 'op' => 'in', 'value' => array( 'long_dormant', 'lost' ) ), array( 'field' => 'ltv_total', 'op' => '>=', 'value' => 100 ) ) ) ), array( 'label' => __( 'Reactivation-ready', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'reactivation_score', 'op' => '>=', 'value' => AIEO_DMM::REACTIVATION_SCORE_MIN ), array( 'field' => 'dormancy_cohort', 'op' => '!=', 'value' => 'active' ) ) ) ), array( 'label' => __( 'Loyalists', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'segments', 'op' => 'contains', 'value' => 'loyalist' ) ) ) ), array( 'label' => __( 'Deal-driven', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'discount_share', 'op' => '>=', 'value' => 0.5 ) ) ) ), array( 'label' => __( 'Lapsing (drifting/lapsed)', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'dormancy_cohort', 'op' => 'in', 'value' => array( 'drifting', 'lapsed' ) ) ) ) ), array( 'label' => __( 'Overdue vs own cadence', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'segments', 'op' => 'contains', 'value' => 'overdue' ) ) ) ), ); $out = array(); foreach ( $defs as $d ) { $out[] = array( 'label' => $d['label'], 'rule' => $d['rule'], 'count' => self::count( $d['rule'] ), 'summary' => self::describe_rule( $d['rule'] ), ); } return $out; } const MADE_BY_KEY = 'made_by'; const MADE_BY_WIZARD = 'offer_wizard'; public static function entry_condition( $rule ) { $conds = is_array( $rule ) && isset( $rule['conditions'] ) && is_array( $rule['conditions'] ) ? array_values( $rule['conditions'] ) : array(); $leaf = 1 === count( $conds ) && is_array( $conds[0] ) && ! isset( $conds[0]['conditions'] ) ? $conds[0] : array(); $field = (string) ( $leaf['field'] ?? '' ); if ( ! isset( self::ENTRY_FIELDS[ $field ] ) || 'in' !== strtolower( (string) ( $leaf['op'] ?? '' ) ) ) { return null; } $ids = array_values( array_unique( array_filter( self::int_list( $leaf['value'] ?? array() ), static function ( $id ) { return $id > 0; } ) ) ); sort( $ids, SORT_NUMERIC ); return $ids ? array( 'field' => $field, 'ids' => $ids, 'days' => max( 0, min( 3650, (int) ( $leaf['days'] ?? 0 ) ) ) ) : null; } public static function wizard_entry( $rule ) { return self::made_by_wizard( $rule ) ? self::entry_condition( $rule ) : null; } public static function made_by_wizard( $rule ) { return is_array( $rule ) && self::MADE_BY_WIZARD === ( $rule[ self::MADE_BY_KEY ] ?? '' ); } public static function entry_name( $field, array $ids, $days = 0 ) { $names = array(); foreach ( $ids as $id ) { $title = html_entity_decode( wp_strip_all_tags( (string) get_the_title( (int) $id ) ), ENT_QUOTES, 'UTF-8' ); $names[] = '' !== trim( $title ) ? '“' . $title . '”' : '#' . (int) $id; } $list = implode( ', ', $names ); $name = 'competition_entered' === $field ? sprintf( __( 'Entrants of %s', 'ai-eshop-optimizer' ), $list ) : sprintf( __( 'Filled in %s', 'ai-eshop-optimizer' ), $list ); if ( (int) $days > 0 ) { $name = sprintf( _n( '%1$s, in the last %2$d day', '%1$s, in the last %2$d days', (int) $days, 'ai-eshop-optimizer' ), $name, (int) $days ); } return function_exists( 'mb_substr' ) ? mb_substr( $name, 0, 190 ) : substr( $name, 0, 190 ); } public static function entry_audience( $field, array $ids, $days = 0, $make = false ) { global $wpdb; $leaf = array( 'field' => (string) $field, 'op' => 'in', 'value' => $ids ); if ( (int) $days > 0 ) { $leaf['days'] = (int) $days; } $want = self::entry_condition( array( 'conditions' => array( $leaf ) ) ); $t = aieo_audiences_table(); if ( null === $want || ! self::table( $t ) ) { return 0; } $rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT id, rule_json FROM `{$t}` WHERE rule_json LIKE %s ORDER BY id ASC", '%' . $wpdb->esc_like( self::MADE_BY_WIZARD ) . '%' ), ARRAY_A ); foreach ( $rows as $row ) { if ( self::wizard_entry( json_decode( (string) $row['rule_json'], true ) ) === $want ) { return (int) $row['id']; } } if ( ! $make ) { return 0; } if ( $want['days'] > 0 ) { $leaf['days'] = $want['days']; } else { unset( $leaf['days'] ); } $leaf['value'] = $want['ids']; return (int) self::create( self::entry_name( $want['field'], $want['ids'], $want['days'] ), array( 'op' => 'and', 'conditions' => array( $leaf ), self::MADE_BY_KEY => self::MADE_BY_WIZARD, ) ); } public static function create( $name, $rule ) { global $wpdb; $t = aieo_audiences_table(); if ( ! self::table( $t ) ) { return 0; } $now = gmdate( 'Y-m-d H:i:s' ); $wpdb->insert( $t, array( 'name' => sanitize_text_field( (string) $name ), 'rule_json' => wp_json_encode( $rule ), 'count_cached' => self::count( $rule ), 'count_at' => $now, 'created' => $now, 'updated' => $now, ) ); $id = (int) $wpdb->insert_id; do_action( 'aieo_audience_saved', $id ); return $id; } public static function save( $name, $rule ) { global $wpdb; $t = aieo_audiences_table(); if ( ! self::table( $t ) ) { return 0; } $now = gmdate( 'Y-m-d H:i:s' ); $cnt = self::count( $rule ); $name = sanitize_text_field( (string) $name ); $data = array( 'name' => $name, 'rule_json' => wp_json_encode( $rule ), 'count_cached' => $cnt, 'count_at' => $now, 'updated' => $now, ); $existing = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM `{$t}` WHERE name = %s LIMIT 1", $name ) ); if ( $existing > 0 ) { $wpdb->update( $t, $data, array( 'id' => $existing ) ); do_action( 'aieo_audience_saved', $existing ); return $existing; } $data['created'] = $now; $wpdb->insert( $t, $data ); $id = (int) $wpdb->insert_id; do_action( 'aieo_audience_saved', $id ); return $id; } public static function list_saved( $limit = 100 ) { global $wpdb; $t = aieo_audiences_table(); if ( ! self::table( $t ) ) { return array(); } $limit = max( 0, (int) $limit ); $rows = $wpdb->get_results( "SELECT id, name, rule_json, count_cached FROM `{$t}` ORDER BY updated DESC" . ( $limit > 0 ? ' LIMIT ' . $limit : '' ), ARRAY_A ); $out = array(); foreach ( (array) $rows as $r ) { $out[] = array( 'id' => (int) $r['id'], 'name' => (string) $r['name'], 'rule' => json_decode( (string) $r['rule_json'], true ), 'count_cached' => (int) $r['count_cached'], ); } return $out; } public static function get_saved( $id ) { global $wpdb; $t = aieo_audiences_table(); if ( (int) $id <= 0 || ! self::table( $t ) ) { return null; } $r = $wpdb->get_row( $wpdb->prepare( "SELECT id, name, rule_json, count_cached FROM `{$t}` WHERE id = %d", (int) $id ), ARRAY_A ); if ( ! is_array( $r ) ) { return null; } return array( 'id' => (int) $r['id'], 'name' => (string) $r['name'], 'rule' => json_decode( (string) $r['rule_json'], true ), 'count_cached' => (int) $r['count_cached'], ); } public static function delete( $id ) { global $wpdb; $t = aieo_audiences_table(); if ( ! self::table( $t ) || (int) $id <= 0 ) { return false; } return (bool) $wpdb->delete( $t, array( 'id' => (int) $id ), array( '%d' ) ); } private static function num_op( $op ) { $m = array( '=' => '=', '!=' => '<>', '>' => '>', '>=' => '>=', '<' => '<', '<=' => '<=' ); return $m[ $op ] ?? ''; } private static function int_list( $v ) { $v = is_array( $v ) ? $v : array( $v ); $out = array(); foreach ( $v as $x ) { if ( is_numeric( $x ) ) { $out[] = (int) $x; } } return $out; } private static function str_list( $v ) { $v = is_array( $v ) ? $v : array( $v ); $out = array(); foreach ( $v as $x ) { $out[] = "'" . esc_sql( (string) $x ) . "'"; } return $out; } private static $present = array(); private static function table( $t ) { global $wpdb; if ( isset( self::$present[ 't:' . $t ] ) ) { return true; } $yes = (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) === $t; if ( $yes ) { $fe = class_exists( 'AIEO_Form_CPT' ) ? AIEO_Form_CPT::entries_table() : $wpdb->prefix . 'aieo_form_entries'; if ( in_array( $t, array( $wpdb->prefix . 'aieo_customer_intelligence', $wpdb->prefix . 'aieo_competition_participants', $fe, $wpdb->users ), true ) ) { self::$present[ 't:' . $t ] = true; } } return $yes; } private static function column( $t, $c ) { global $wpdb; if ( isset( self::$present[ 'c:' . $t . '.' . $c ] ) ) { return true; } $yes = (bool) $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$t}` LIKE %s", $c ) ); if ( $yes ) { self::$present[ 'c:' . $t . '.' . $c ] = true; } return $yes; } }