prefix . 'affistyle_clicks' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) ) !== $table_name ) { if ( file_exists( ABSPATH . 'wp-admin/includes/upgrade.php' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$table_name} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, post_id bigint(20) unsigned NOT NULL DEFAULT 0, box_id bigint(20) unsigned NOT NULL DEFAULT 0, product_index tinyint(2) unsigned NOT NULL DEFAULT 1, country_code varchar(10) NOT NULL DEFAULT 'ZZ', device_type varchar(20) NOT NULL DEFAULT 'desktop', referrer_url varchar(255) NOT NULL DEFAULT '', referrer varchar(255) NOT NULL DEFAULT '', ip_address varchar(45) NOT NULL DEFAULT '', user_agent text, created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (id), KEY post_id (post_id), KEY box_id (box_id), KEY country_code (country_code), KEY created_at (created_at) ) {$charset_collate};"; dbDelta( $sql ); } } /** * Retrieve compiled analytics metrics & counts. * * @param string $date_range Range preset ('all', '7', '30', '60', '90', 'custom'). * @param string $start_date YYYY-MM-DD. * @param string $end_date YYYY-MM-DD. * @return array */ public static function get_analytics_summary( $date_range = '7', $start_date = '', $end_date = '' ) { self::ensure_analytics_table_exists(); global $wpdb; $table_name = esc_sql( $wpdb->prefix . 'affistyle_clicks' ); $date_range = sanitize_text_field( $date_range ); $start_date = sanitize_text_field( $start_date ); $end_date = sanitize_text_field( $end_date ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $total_all_time = (int) $wpdb->get_var( "SELECT COUNT(id) FROM {$table_name}" ); if ( in_array( $date_range, array( '7', '30', '60', '90' ), true ) ) { $days = absint( $date_range ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $total_last_range = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COUNT(id) FROM {$table_name} WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)", $days ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $active_cards_tracked = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COUNT(DISTINCT COALESCE(NULLIF(box_id, 0), post_id)) FROM {$table_name} WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)", $days ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $top_countries = $wpdb->get_results( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT country_code, COUNT(id) as click_count FROM {$table_name} WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY) GROUP BY country_code ORDER BY click_count DESC LIMIT %d", $days, 5 ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $top_posts = $wpdb->get_results( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COALESCE(NULLIF(box_id, 0), post_id) as post_id, COUNT(id) as click_count FROM {$table_name} WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY) GROUP BY COALESCE(NULLIF(box_id, 0), post_id) ORDER BY click_count DESC LIMIT %d", $days, 5 ), ARRAY_A ); } elseif ( 'custom' === $date_range && ! empty( $start_date ) && ! empty( $end_date ) ) { $start_datetime = $start_date . ' 00:00:00'; $end_datetime = $end_date . ' 23:59:59'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $total_last_range = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COUNT(id) FROM {$table_name} WHERE created_at >= %s AND created_at <= %s", $start_datetime, $end_datetime ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $active_cards_tracked = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COUNT(DISTINCT COALESCE(NULLIF(box_id, 0), post_id)) FROM {$table_name} WHERE created_at >= %s AND created_at <= %s", $start_datetime, $end_datetime ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $top_countries = $wpdb->get_results( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT country_code, COUNT(id) as click_count FROM {$table_name} WHERE created_at >= %s AND created_at <= %s GROUP BY country_code ORDER BY click_count DESC LIMIT %d", $start_datetime, $end_datetime, 5 ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $top_posts = $wpdb->get_results( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COALESCE(NULLIF(box_id, 0), post_id) as post_id, COUNT(id) as click_count FROM {$table_name} WHERE created_at >= %s AND created_at <= %s GROUP BY COALESCE(NULLIF(box_id, 0), post_id) ORDER BY click_count DESC LIMIT %d", $start_datetime, $end_datetime, 5 ), ARRAY_A ); } elseif ( 'custom' === $date_range && ! empty( $start_date ) ) { $start_datetime = $start_date . ' 00:00:00'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $total_last_range = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COUNT(id) FROM {$table_name} WHERE created_at >= %s", $start_datetime ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $active_cards_tracked = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COUNT(DISTINCT COALESCE(NULLIF(box_id, 0), post_id)) FROM {$table_name} WHERE created_at >= %s", $start_datetime ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $top_countries = $wpdb->get_results( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT country_code, COUNT(id) as click_count FROM {$table_name} WHERE created_at >= %s GROUP BY country_code ORDER BY click_count DESC LIMIT %d", $start_datetime, 5 ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $top_posts = $wpdb->get_results( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COALESCE(NULLIF(box_id, 0), post_id) as post_id, COUNT(id) as click_count FROM {$table_name} WHERE created_at >= %s GROUP BY COALESCE(NULLIF(box_id, 0), post_id) ORDER BY click_count DESC LIMIT %d", $start_datetime, 5 ), ARRAY_A ); } elseif ( 'custom' === $date_range && ! empty( $end_date ) ) { $end_datetime = $end_date . ' 23:59:59'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $total_last_range = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COUNT(id) FROM {$table_name} WHERE created_at <= %s", $end_datetime ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $active_cards_tracked = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COUNT(DISTINCT COALESCE(NULLIF(box_id, 0), post_id)) FROM {$table_name} WHERE created_at <= %s", $end_datetime ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $top_countries = $wpdb->get_results( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT country_code, COUNT(id) as click_count FROM {$table_name} WHERE created_at <= %s GROUP BY country_code ORDER BY click_count DESC LIMIT %d", $end_datetime, 5 ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $top_posts = $wpdb->get_results( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COALESCE(NULLIF(box_id, 0), post_id) as post_id, COUNT(id) as click_count FROM {$table_name} WHERE created_at <= %s GROUP BY COALESCE(NULLIF(box_id, 0), post_id) ORDER BY click_count DESC LIMIT %d", $end_datetime, 5 ), ARRAY_A ); } else { $total_last_range = $total_all_time; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $active_cards_tracked = (int) $wpdb->get_var( "SELECT COUNT(DISTINCT COALESCE(NULLIF(box_id, 0), post_id)) FROM {$table_name}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $top_countries = $wpdb->get_results( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT country_code, COUNT(id) as click_count FROM {$table_name} GROUP BY country_code ORDER BY click_count DESC LIMIT %d", 5 ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $top_posts = $wpdb->get_results( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COALESCE(NULLIF(box_id, 0), post_id) as post_id, COUNT(id) as click_count FROM {$table_name} GROUP BY COALESCE(NULLIF(box_id, 0), post_id) ORDER BY click_count DESC LIMIT %d", 5 ), ARRAY_A ); } // Count published affistyle_box posts within the active date range so the // "Published Boxes" card reflects the same filter as the other metrics. $posts_table = esc_sql( $wpdb->posts ); $published_boxes_base = "SELECT COUNT(ID) FROM {$posts_table} WHERE post_type = 'affistyle_box' AND post_status = 'publish'"; if ( in_array( $date_range, array( '7', '30', '60', '90' ), true ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $total_published_cards = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( $published_boxes_base . ' AND post_date >= DATE_SUB(NOW(), INTERVAL %d DAY)', $days ) ); } elseif ( 'custom' === $date_range && ! empty( $start_date ) && ! empty( $end_date ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $total_published_cards = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( $published_boxes_base . ' AND post_date >= %s AND post_date <= %s', $start_date . ' 00:00:00', $end_date . ' 23:59:59' ) ); } elseif ( 'custom' === $date_range && ! empty( $start_date ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $total_published_cards = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( $published_boxes_base . ' AND post_date >= %s', $start_date . ' 00:00:00' ) ); } elseif ( 'custom' === $date_range && ! empty( $end_date ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $total_published_cards = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( $published_boxes_base . ' AND post_date <= %s', $end_date . ' 23:59:59' ) ); } else { // All-time: count all published affistyle_box posts using core helper. $published_counts = wp_count_posts( 'affistyle_box' ); $total_published_cards = isset( $published_counts->publish ) ? (int) $published_counts->publish : 0; } return array( 'total_all_time' => $total_all_time, 'total_last_7_days' => $total_last_range, 'total_range_clicks' => $total_last_range, 'total_published_cards' => $total_published_cards, 'active_cards_tracked' => $active_cards_tracked, 'top_countries' => is_array( $top_countries ) ? $top_countries : array(), 'top_posts' => is_array( $top_posts ) ? $top_posts : array(), ); } /** * Helper function for options retrieval to avoid PHP reserved word parameters. * * @param string $key Option key. * @param mixed $default_value Fallback value if option not set. * @return mixed */ public static function get_option( $key, $default_value = false ) { $val = get_option( $key ); return ( false !== $val ) ? $val : $default_value; } /** * Retrieve all saved affiliate card post items for dropdown selectors. * * @return array */ public static function get_all_boxes_for_dropdown() { $posts = get_posts( array( 'post_type' => array( 'affistyle_box', 'aff_box' ), 'posts_per_page' => -1, 'post_status' => 'publish', 'orderby' => 'title', 'order' => 'ASC', ) ); $options = array( array( 'label' => __( 'Select an Affiliate Box...', 'affistyle' ), 'value' => '', ), ); if ( ! empty( $posts ) ) { foreach ( $posts as $post ) { $options[] = array( 'label' => $post->post_title, 'value' => (string) $post->ID, ); } } return $options; } /** * Retrieve and compile all manual metadata for a specific affiliate box ID. * * @param int $post_id Post ID. * @return array|false */ public static function get_box_data( $post_id ) { $post_id = absint( $post_id ); $post_type = get_post_type( $post_id ); if ( 'affistyle_box' !== $post_type && 'aff_box' !== $post_type ) { return false; } // Fetch all metadata in a single cached database call. $all_meta = get_post_meta( $post_id ); $get_meta = function ( $key, $default_value = '' ) use ( $all_meta ) { return isset( $all_meta[ $key ][0] ) ? $all_meta[ $key ][0] : $default_value; }; $count = absint( $get_meta( '_affistyle_rating_count', 0 ) ); $sum = absint( $get_meta( '_affistyle_rating_sum', 0 ) ); $avg = $count > 0 ? round( $sum / $count, 1 ) : 0.0; $score_meta = $get_meta( '_affistyle_rating_score' ); $score = ( '' !== $score_meta ) ? floatval( $score_meta ) : $avg; $rev_count = $get_meta( '_affistyle_review_count' ); if ( empty( $rev_count ) && $count > 0 ) { /* translators: %d: Review count number */ $rev_count = sprintf( _n( '%d review', '%d reviews', $count, 'affistyle' ), $count ); } $show_meta = $get_meta( '_affistyle_show_rating' ); $show_rating = ( '' === $show_meta ) ? true : ( '1' === (string) $show_meta ); $title_1 = $get_meta( '_affistyle_title_1' ); if ( empty( $title_1 ) ) { $title_1 = get_the_title( $post_id ); } $image_1 = $get_meta( '_affistyle_image_1' ); if ( empty( $image_1 ) ) { $image_1 = $get_meta( '_affistyle_image_url' ); } $raw_dest_1 = $get_meta( '_affistyle_destination' ); if ( empty( $raw_dest_1 ) ) { $raw_dest_1 = $get_meta( '_affistyle_url_default' ); } $is_cloaking_enabled = apply_filters( 'affistyle_is_cloaking_enabled', apply_filters( 'affistyle_enable_cloaking', true ), $post_id ); if ( $is_cloaking_enabled && class_exists( 'AffiStyle_Cloaking' ) ) { $dest_1 = AffiStyle_Cloaking::get_cloaked_url( $post_id, 1 ); } else { $dest_1 = $raw_dest_1; } $link_attributes = class_exists( 'AffiStyle_Compliance' ) ? AffiStyle_Compliance::get_link_attributes_string( $post_id ) : 'target="_blank" rel="nofollow sponsored noopener"'; $ftc_disclosure = class_exists( 'AffiStyle_Compliance' ) ? AffiStyle_Compliance::get_ftc_disclosure( $post_id ) : __( 'We may earn an affiliate commission at no extra cost to you when you purchase through links on our site.', 'affistyle' ); $show_ftc = class_exists( 'AffiStyle_Compliance' ) ? AffiStyle_Compliance::is_ftc_enabled( $post_id ) : true; $data = array( 'id' => $post_id, 'post_id' => $post_id, 'template_style' => $get_meta( '_affistyle_template_style', 'style-1' ), 'title' => get_the_title( $post_id ), 'title_1' => $title_1, 'description' => $get_meta( '_affistyle_description' ), 'destination' => $dest_1, 'link_1' => $dest_1, 'raw_destination' => $raw_dest_1, 'raw_link_1' => $raw_dest_1, 'button_text' => $get_meta( '_affistyle_button_text', __( 'Claim Deal', 'affistyle' ) ), 'badge_text' => $get_meta( '_affistyle_badge_text' ), 'image_url' => $image_1, 'image_1' => $image_1, 'bg_color' => $get_meta( '_affistyle_bg_color', '#ffffff' ), 'btn_color' => $get_meta( '_affistyle_btn_color', '#2563eb' ), 'pros' => $get_meta( '_affistyle_pros' ), 'cons' => $get_meta( '_affistyle_cons' ), 'rating_score' => $score, 'review_count' => $rev_count, 'show_rating' => $show_rating, 'rating_count' => $count, 'rating_sum' => $sum, 'rating_average' => $avg, // Automated Compliance & FTC Disclosure fields. 'link_attributes' => $link_attributes, 'ftc_disclosure' => $ftc_disclosure, 'show_ftc' => $show_ftc, ); return apply_filters( 'affistyle_card_data', $data, $post_id ); } } new AffiStyle_Database();