self::revenue_by_competition(), 'aggregate' => self::aggregate($top_limit), 'generated_at' => current_time('mysql', true), ); update_option(self::STATS_CACHE_OPTION, $payload, false); return $payload; } private static function table_exists($name) { global $wpdb; static $cache = array(); if (!array_key_exists($name, $cache)) { $cache[$name] = ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $name)) === $name); } return $cache[$name]; } public static function associated_product_ids($competition_id) { global $wpdb; $cid = (int) $competition_id; if (!class_exists('AIEO_Competition_CPT')) { return array(); } $brands = array_map('intval', (array) get_post_meta($cid, AIEO_Competition_CPT::META_BRANDS, true)); $cats = array_map('intval', (array) get_post_meta($cid, AIEO_Competition_CPT::META_CATEGORIES, true)); $direct = array_map('intval', (array) get_post_meta($cid, AIEO_Competition_CPT::META_PRODUCTS, true)); $term_ids = array_values(array_unique(array_filter(array_merge($brands, $cats)))); $from_terms = array(); if ($term_ids) { $brand_tax = (class_exists('AIEO_DMM_Brand_Discounts') && method_exists('AIEO_DMM_Brand_Discounts', 'get_brand_taxonomy')) ? (AIEO_DMM_Brand_Discounts::get_brand_taxonomy() ?: 'product_brand') : 'product_brand'; $place = implode(',', array_fill(0, count($term_ids), '%d')); $sql = $wpdb->prepare( "SELECT DISTINCT tr.object_id FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id JOIN {$wpdb->posts} p ON p.ID = tr.object_id WHERE tt.term_id IN ($place) AND tt.taxonomy IN (%s, 'product_cat') AND p.post_type = 'product' AND p.post_status IN ('publish','private')", array_merge($term_ids, array($brand_tax)) ); $from_terms = array_map('intval', (array) $wpdb->get_col($sql)); } $all = array_merge($from_terms, $direct); if (class_exists('AIEO_DMM_Product_Picker') && method_exists('AIEO_DMM_Product_Picker', 'roll_up_to_parents')) { $all = AIEO_DMM_Product_Picker::roll_up_to_parents($all); } return array_values(array_unique(array_filter(array_map('intval', $all)))); } public static function insights($competition_id) { global $wpdb; $cid = (int) $competition_id; $out = array( 'metrics' => class_exists('AIEO_Competition_Participants') ? AIEO_Competition_Participants::metrics($cid) : array('entries' => 0, 'confirmed' => 0, 'pending' => 0, 'winners' => 0), 'converting_entrants' => 0, 'orders_placed' => 0, 'entrant_revenue' => 0.0, 'assoc_products' => 0, 'pull_units' => 0, 'pull_revenue' => 0.0, 'has_order_data' => false, ); if (!class_exists('AIEO_Competition_Participants')) { return $out; } $ptbl = AIEO_Competition_Participants::table(); $ostats = $wpdb->prefix . 'aieo_order_stats'; $opa = $wpdb->prefix . 'aieo_order_products_analytics'; if (self::table_exists($ostats)) { $out['has_order_data'] = true; $row = $wpdb->get_row($wpdb->prepare( "SELECT COUNT(DISTINCT e.email) ce, COUNT(DISTINCT os.orderid) op, COALESCE(SUM(os.orderspend),0) rev FROM (SELECT DISTINCT email FROM {$ptbl} WHERE competition_id = %d) e JOIN {$ostats} os ON os.customeremail COLLATE " . self::coll() . " = e.email COLLATE " . self::coll() . "", $cid )); if ($row) { $out['converting_entrants'] = (int) $row->ce; $out['orders_placed'] = (int) $row->op; $out['entrant_revenue'] = (float) $row->rev; } } $prod_ids = self::associated_product_ids($cid); $out['assoc_products'] = count($prod_ids); if ($prod_ids && self::table_exists($opa)) { $in = implode(',', array_map('intval', $prod_ids)); $row = $wpdb->get_row($wpdb->prepare( "SELECT COALESCE(SUM(opa.quantity),0) units, COALESCE(SUM(opa.price),0) revenue FROM {$opa} opa JOIN (SELECT DISTINCT email FROM {$ptbl} WHERE competition_id = %d) e ON opa.customer_email COLLATE " . self::coll() . " = e.email COLLATE " . self::coll() . " WHERE opa.product_id IN ({$in})", $cid )); if ($row) { $out['pull_units'] = (int) $row->units; $out['pull_revenue'] = (float) $row->revenue; } } return $out; } public static function revenue_by_competition() { global $wpdb; $out = array(); if (!class_exists('AIEO_Competition_Participants')) { return $out; } $ptbl = AIEO_Competition_Participants::table(); $ostats = $wpdb->prefix . 'aieo_order_stats'; if (!self::table_exists($ostats)) { return $out; } $rows = $wpdb->get_results( "SELECT e.competition_id cid, COUNT(DISTINCT e.email) ce, COUNT(DISTINCT os.orderid) op, COALESCE(SUM(os.orderspend),0) rev FROM (SELECT DISTINCT competition_id, email FROM {$ptbl}) e JOIN {$ostats} os ON os.customeremail COLLATE " . self::coll() . " = e.email COLLATE " . self::coll() . " GROUP BY e.competition_id" ); foreach ((array) $rows as $r) { $out[(int) $r->cid] = array( 'revenue' => (float) $r->rev, 'converting' => (int) $r->ce, 'orders' => (int) $r->op, ); } return $out; } public static function aggregate($top_limit = 10) { global $wpdb; $out = array( 'total_participants' => 0, 'unique_emails' => 0, 'identified' => 0, 'confirmed' => 0, 'converting_entrants' => 0, 'entrant_revenue' => 0.0, 'has_order_data' => false, 'has_ci' => false, 'top_participants' => array(), ); if (!class_exists('AIEO_Competition_Participants')) { return $out; } $ptbl = AIEO_Competition_Participants::table(); $addressed = AIEO_Competition_Participants::addressed_sql('email'); $r = $wpdb->get_row( "SELECT COUNT(*) tot, COUNT(DISTINCT CASE WHEN {$addressed} THEN email END) uniq, COUNT(DISTINCT CASE WHEN eponymous_id > 0 THEN eponymous_id END) ident, SUM(CASE WHEN status='confirmed' THEN 1 ELSE 0 END) conf FROM {$ptbl}" ); if ($r) { $out['total_participants'] = (int) $r->tot; $out['unique_emails'] = (int) $r->uniq; $out['identified'] = (int) $r->ident; $out['confirmed'] = (int) $r->conf; } $ostats = $wpdb->prefix . 'aieo_order_stats'; if (self::table_exists($ostats)) { $out['has_order_data'] = true; $row = $wpdb->get_row( "SELECT COUNT(DISTINCT p.email) ce, COALESCE(SUM(os.orderspend),0) rev FROM (SELECT DISTINCT email FROM {$ptbl}) p JOIN {$ostats} os ON os.customeremail COLLATE " . self::coll() . " = p.email COLLATE " . self::coll() . "" ); if ($row) { $out['converting_entrants'] = (int) $row->ce; $out['entrant_revenue'] = (float) $row->rev; } } $ci = $wpdb->prefix . 'aieo_customer_intelligence'; if (self::table_exists($ci)) { $out['has_ci'] = true; $lim = max(1, (int) $top_limit); $out['top_participants'] = (array) $wpdb->get_results($wpdb->prepare( "SELECT ci.eponymous_id, MAX(cp.email) email, COUNT(DISTINCT cp.competition_id) entries, COUNT(DISTINCT CASE WHEN cp.is_winner = 1 THEN cp.competition_id END) wins, ci.ltv_total FROM {$ptbl} cp JOIN {$ci} ci ON ci.eponymous_id = cp.eponymous_id WHERE cp.eponymous_id > 0 GROUP BY ci.eponymous_id, ci.ltv_total ORDER BY entries DESC, ci.ltv_total DESC LIMIT %d", $lim )); } return $out; } public static function customer_participation($eponymous_id) { global $wpdb; $eid = (int) $eponymous_id; $out = array('count' => 0, 'last_entry' => null, 'won' => 0, 'competitions' => array()); if ($eid <= 0 || !class_exists('AIEO_Competition_Participants')) { return $out; } $ptbl = AIEO_Competition_Participants::table(); $rows = $wpdb->get_results($wpdb->prepare( "SELECT cp.competition_id, MAX(p.post_title) post_title, MAX(cp.created_at) created_at, MAX(cp.status) status, MAX(cp.is_winner) is_winner, COUNT(*) entries FROM {$ptbl} cp LEFT JOIN {$wpdb->posts} p ON p.ID = cp.competition_id WHERE cp.eponymous_id = %d GROUP BY cp.competition_id ORDER BY created_at DESC", $eid )); foreach ((array) $rows as $row) { $out['count']++; if ((int) $row->is_winner === 1) { $out['won']++; } if ($out['last_entry'] === null && $row->created_at) { $out['last_entry'] = $row->created_at; } $out['competitions'][] = array( 'id' => (int) $row->competition_id, 'name' => (string) $row->post_title, 'date' => $row->created_at, 'status' => (string) $row->status, 'is_winner' => (int) $row->is_winner, 'entries' => (int) $row->entries, ); } return $out; } }