prepare() for security. * * @package TrueConversion */ // Prevent direct file access. if ( ! defined( 'ABSPATH' ) ) { exit; } /** * Class FTCON_Entry_Manager */ class FTCON_Entry_Manager { /** * Get the full table name (with WordPress prefix). * * @return string The table name, e.g., "wp_tc_form_entries". */ private static function table_name() { global $wpdb; return $wpdb->prefix . 'ftcon_form_entries'; } /** * Save a New Entry * * Inserts a form submission entry into the database. * TrueConversion has unlimited entries on all plans. * * @param array $data { * The entry data to save. All values should already be sanitized. * * @type string $form_plugin Form plugin name. * @type string $form_name Form name/title. * @type string $submitter_name Name from the form. * @type string $submitter_email Email from the form. * @type string $message_excerpt First 200 chars of the message. * @type string $traffic_source Human-readable source label. * @type string $utm_source Raw utm_source value. * @type string $utm_medium Raw utm_medium value. * @type string $utm_campaign Raw utm_campaign value. * @type string $utm_term Raw utm_term value. * @type string $utm_content Raw utm_content value. * @type string $click_id Click ID value. * @type string $click_id_type Click ID type (gclid, fbclid, etc.). * @type string $page_url URL where form was submitted. * @type string $landing_page Original landing page URL. * @type string $referrer HTTP referrer. * @type string $ip_address Anonymised IP address. * } * * @return int|false The new entry ID, or false on failure. */ public static function save_entry( $data ) { global $wpdb; $table = self::table_name(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery $result = $wpdb->insert( $table, array( 'form_plugin' => isset( $data['form_plugin'] ) ? $data['form_plugin'] : '', 'form_name' => isset( $data['form_name'] ) ? $data['form_name'] : '', 'submitter_name' => isset( $data['submitter_name'] ) ? $data['submitter_name'] : '', 'submitter_email' => isset( $data['submitter_email'] ) ? $data['submitter_email'] : '', 'message_excerpt' => isset( $data['message_excerpt'] ) ? $data['message_excerpt'] : '', 'form_fields_summary' => isset( $data['form_fields_summary'] ) ? $data['form_fields_summary'] : '', 'traffic_source' => isset( $data['traffic_source'] ) ? $data['traffic_source'] : 'Direct', 'utm_source' => isset( $data['utm_source'] ) ? $data['utm_source'] : '', 'utm_medium' => isset( $data['utm_medium'] ) ? $data['utm_medium'] : '', 'utm_campaign' => isset( $data['utm_campaign'] ) ? $data['utm_campaign'] : '', 'utm_term' => isset( $data['utm_term'] ) ? $data['utm_term'] : '', 'utm_content' => isset( $data['utm_content'] ) ? $data['utm_content'] : '', 'click_id' => isset( $data['click_id'] ) ? $data['click_id'] : '', 'click_id_type' => isset( $data['click_id_type'] ) ? $data['click_id_type'] : '', 'page_url' => isset( $data['page_url'] ) ? $data['page_url'] : '', 'landing_page' => isset( $data['landing_page'] ) ? $data['landing_page'] : '', 'referrer' => isset( $data['referrer'] ) ? $data['referrer'] : '', 'ip_address' => isset( $data['ip_address'] ) ? $data['ip_address'] : '', 'ip_address_raw' => isset( $data['ip_address_raw'] ) ? $data['ip_address_raw'] : '', 'user_agent' => isset( $data['user_agent'] ) ? $data['user_agent'] : '', 'meta_fbp' => isset( $data['meta_fbp'] ) ? $data['meta_fbp'] : '', 'meta_fbc' => isset( $data['meta_fbc'] ) ? $data['meta_fbc'] : '', 'submitted_at' => current_time( 'mysql' ), ), array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', ) ); if ( false === $result ) { return false; } return $wpdb->insert_id; } /** * Get Entries (Paginated) * * Retrieves entries from the database with support for pagination, * searching, sorting, and filtering by traffic source. * * @param array $args { * Query arguments. * * @type int $per_page Number of entries per page. Default 20. * @type int $page Current page number. Default 1. * @type string $search Search term (searches name, email, source, message). * @type string $orderby Column to sort by. Default 'submitted_at'. * @type string $order Sort direction: 'ASC' or 'DESC'. Default 'DESC'. * @type string $source Filter by traffic source platform. * } * * @return array { * @type array $entries Array of entry objects. * @type int $total Total number of matching entries. * } */ public static function get_entries( $args = array() ) { global $wpdb; $table = self::table_name(); // Defaults. $defaults = array( 'per_page' => 20, 'page' => 1, 'search' => '', 'orderby' => 'submitted_at', 'order' => 'DESC', 'source' => '', ); $args = wp_parse_args( $args, $defaults ); // Sanitize orderby to prevent SQL injection. $allowed_columns = array( 'id', 'form_plugin', 'form_name', 'submitter_name', 'submitter_email', 'traffic_source', 'submitted_at', 'is_conversion' ); $orderby = in_array( $args['orderby'], $allowed_columns, true ) ? $args['orderby'] : 'submitted_at'; $order = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC'; // Build WHERE clause. $where = array( '1=1' ); $values = array(); // Search filter. if ( ! empty( $args['search'] ) ) { $like = '%' . $wpdb->esc_like( $args['search'] ) . '%'; $where[] = '(submitter_name LIKE %s OR submitter_email LIKE %s OR traffic_source LIKE %s OR message_excerpt LIKE %s)'; $values[] = $like; $values[] = $like; $values[] = $like; $values[] = $like; } // Source filter. if ( ! empty( $args['source'] ) ) { $where[] = 'traffic_source LIKE %s'; $values[] = '%' . $wpdb->esc_like( $args['source'] ) . '%'; } $where_sql = implode( ' AND ', $where ); // Get total count. $count_sql = "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared if ( ! empty( $values ) ) { $count_sql = $wpdb->prepare( $count_sql, $values ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared } $total = (int) $wpdb->get_var( $count_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching // Get entries. $offset = ( max( 1, (int) $args['page'] ) - 1 ) * (int) $args['per_page']; $query_sql = "SELECT * FROM {$table} WHERE {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d"; $values[] = (int) $args['per_page']; $values[] = $offset; $entries = $wpdb->get_results( $wpdb->prepare( $query_sql, $values ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching return array( 'entries' => $entries ? $entries : array(), 'total' => $total, ); } /** * Get a Single Entry * * @param int $id The entry ID. * * @return object|null The entry object, or null if not found. */ public static function get_entry( $id ) { global $wpdb; $table = self::table_name(); return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching } /** * Update a Single Field * * Updates one column for a specific entry. Used for toggling * conversion status, storing AI classification results, etc. * * @param int $id The entry ID. * @param string $field The column name. * @param mixed $value The new value. * * @return bool True on success, false on failure. */ public static function update_field( $id, $field, $value ) { global $wpdb; $table = self::table_name(); // Whitelist allowed fields to prevent SQL injection. $allowed_fields = array( 'is_conversion', 'conversion_sent', 'conversion_sent_at', 'meta_conversion_sent', 'meta_conversion_sent_at', 'li_conversion_sent', 'li_conversion_sent_at', 'ai_classification', 'ai_confidence', 'ai_reasoning', ); if ( ! in_array( $field, $allowed_fields, true ) ) { return false; } // Determine format based on field type. $format = '%s'; if ( in_array( $field, array( 'is_conversion', 'conversion_sent', 'meta_conversion_sent', 'li_conversion_sent' ), true ) ) { $format = '%d'; } elseif ( 'ai_confidence' === $field ) { $format = '%f'; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching return false !== $wpdb->update( $table, array( $field => $value ), array( 'id' => absint( $id ) ), array( $format ), array( '%d' ) ); } /** * Delete a Single Entry * * @param int $id The entry ID to delete. * * @return bool True on success, false on failure. */ public static function delete_entry( $id ) { global $wpdb; $table = self::table_name(); return false !== $wpdb->delete( $table, array( 'id' => (int) $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching } /** * Delete Multiple Entries * * @param array $ids Array of entry IDs to delete. * * @return int Number of entries deleted. */ public static function delete_entries( $ids ) { global $wpdb; $table = self::table_name(); if ( empty( $ids ) ) { return 0; } // Sanitize IDs. $ids = array_map( 'absint', $ids ); $ids = array_filter( $ids ); if ( empty( $ids ) ) { return 0; } $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) ); $query = "DELETE FROM {$table} WHERE id IN ({$placeholders})"; return (int) $wpdb->query( $wpdb->prepare( $query, $ids ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching } /** * Get Entry Count * * @return int Total number of entries in the database. */ public static function get_count() { global $wpdb; $table = self::table_name(); return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching } /** * Get Source Statistics * * Returns aggregate counts grouped by traffic source platform. * * @return array Array of objects with 'traffic_source' and 'count' properties. */ public static function get_source_stats() { global $wpdb; $table = self::table_name(); return $wpdb->get_results( "SELECT traffic_source, COUNT(*) as count FROM {$table} GROUP BY traffic_source ORDER BY count DESC" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching } /** * Get Conversion Statistics * * Returns aggregate conversion counts for the stats cards. * * @return array { * @type int $total_entries Total entries. * @type int $conversions Entries marked as conversion. * @type float $conversion_rate Conversion rate as percentage. * @type int $sent_to_google Conversions sent to Google Ads. * } */ public static function get_conversion_stats() { global $wpdb; $table = self::table_name(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching $row = $wpdb->get_row( "SELECT COUNT(*) as total_entries, SUM(is_conversion = 1) as conversions, SUM(conversion_sent = 1) as sent_to_google FROM {$table}" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared ); $total = $row ? (int) $row->total_entries : 0; $conversions = $row ? (int) $row->conversions : 0; $sent = $row ? (int) $row->sent_to_google : 0; return array( 'total_entries' => $total, 'conversions' => $conversions, 'conversion_rate' => $total > 0 ? round( ( $conversions / $total ) * 100, 1 ) : 0, 'sent_to_google' => $sent, ); } /** * Get Pending Conversions * * Returns entries that are marked as conversions with a gclid * but haven't been successfully sent to Google Ads yet. * Only includes entries from the last 7 days to prevent * perpetual retries on permanently failed uploads. * * @param int $limit Max number of entries to return. * * @return array Array of entry objects. */ public static function get_pending_conversions( $limit = 50 ) { global $wpdb; $table = self::table_name(); $cutoff = gmdate( 'Y-m-d H:i:s', strtotime( '-7 days' ) ); return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE is_conversion = 1 AND conversion_sent = 0 AND click_id_type = 'gclid' AND click_id != '' AND submitted_at >= %s ORDER BY submitted_at ASC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $cutoff, $limit ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching } /** * Get Entries Pending Meta CAPI Upload * * Returns entries that haven't been successfully sent to Meta CAPI yet. * Requires a submitter_email. Only includes entries from the last 7 days * to prevent perpetual retries on permanently failed uploads. * * @param int $limit Max entries to return. * * @return array Array of entry objects. */ public static function get_pending_meta_conversions( $limit = 50 ) { global $wpdb; $table = self::table_name(); $cutoff = gmdate( 'Y-m-d H:i:s', strtotime( '-7 days' ) ); return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE meta_conversion_sent = 0 AND submitter_email != '' AND submitted_at >= %s ORDER BY submitted_at ASC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $cutoff, $limit ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching } /** * Get Entries Pending LinkedIn CAPI Upload * * Returns entries that haven't been successfully sent to LinkedIn CAPI yet. * Requires a submitter_email. Only includes entries from the last 7 days. * * @param int $limit Max entries to return. * * @return array Array of entry objects. */ public static function get_pending_li_conversions( $limit = 50 ) { global $wpdb; $table = self::table_name(); $cutoff = gmdate( 'Y-m-d H:i:s', strtotime( '-7 days' ) ); return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE li_conversion_sent = 0 AND submitter_email != '' AND submitted_at >= %s ORDER BY submitted_at ASC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $cutoff, $limit ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching } /** * Log a Conversion Upload Attempt * * Inserts a row into ftcon_conversion_uploads for every API call made * for Meta or LinkedIn uploads — successful or failed. Used for * auditing, debugging, and understanding retry patterns. * * @param int $entry_id The entry ID. * @param string $platform 'meta_ads' or 'linkedin_ads'. * @param string $status 'success' or 'failed'. * @param string $request_payload JSON payload sent to the relay (no secrets). * @param string $response_body Raw response body from the relay. * @param int $http_status_code HTTP status code returned. * @param string $error_message Error description on failure, empty on success. * * @return int|false Inserted row ID, or false on failure. */ public static function log_conversion_upload( $entry_id, $platform, $status, $request_payload = '', $response_body = '', $http_status_code = 0, $error_message = '' ) { global $wpdb; $table = $wpdb->prefix . 'ftcon_conversion_uploads'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching $retry_count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE entry_id = %d AND platform = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared absint( $entry_id ), sanitize_text_field( $platform ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery $result = $wpdb->insert( $table, array( 'entry_id' => absint( $entry_id ), 'platform' => sanitize_text_field( $platform ), 'status' => sanitize_text_field( $status ), 'request_payload' => $request_payload, 'response_body' => $response_body, 'http_status_code' => (int) $http_status_code, 'error_message' => sanitize_text_field( $error_message ), 'attempted_at' => current_time( 'mysql' ), 'retry_count' => $retry_count, ), array( '%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%d' ) ); if ( false === $result ) { return false; } return $wpdb->insert_id; } /** * Get Entries for AI Classification * * Returns entries that haven't been classified by AI yet. * Used for batch classification or reprocessing. * * @param int $limit Max number of entries to return. * * @return array Array of entry objects. */ public static function get_entries_for_ai( $limit = 50 ) { global $wpdb; $table = self::table_name(); return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE ai_classification = '' ORDER BY submitted_at DESC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $limit ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching } /** * Sanitize a value for safe CSV output. * * Prevents formula injection in Excel/Sheets by prefixing dangerous * leading characters with a single quote. * * @param mixed $value The cell value to sanitize. * * @return mixed The sanitized value. */ private static function sanitize_csv_value( $value ) { if ( is_string( $value ) && isset( $value[0] ) && in_array( $value[0], array( '=', '+', '-', '@', "\t", "\r" ), true ) ) { return "'" . $value; } return $value; } /** * Export Entries as CSV * * Generates and outputs a CSV file of all entries (or filtered entries). * Includes conversion and AI classification columns. * This method sends headers and outputs directly — call it before any HTML. * * @param array $ids Optional. Specific entry IDs to export. Empty = all entries. */ public static function export_csv( $ids = array() ) { global $wpdb; $table = self::table_name(); // Get entries. if ( ! empty( $ids ) ) { $ids = array_map( 'absint', $ids ); $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) ); $entries = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE id IN ({$placeholders}) ORDER BY submitted_at DESC", // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $ids ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching } else { $entries = $wpdb->get_results( "SELECT * FROM {$table} ORDER BY submitted_at DESC" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching } // Set CSV headers. $filename = 'trueconversion-entries-' . gmdate( 'Y-m-d' ) . '.csv'; header( 'Content-Type: text/csv; charset=utf-8' ); header( 'Content-Disposition: attachment; filename=' . $filename ); // Open output stream. $output = fopen( 'php://output', 'w' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- Writing to php://output stream for CSV download. // Write header row. fputcsv( $output, array( 'Date/Time', 'Form Plugin', 'Form Name', 'Submitter Name', 'Submitter Email', 'Message', 'Form Fields', 'Traffic Source', 'UTM Source', 'UTM Medium', 'UTM Campaign', 'UTM Term', 'UTM Content', 'Click ID', 'Click ID Type', 'Page URL', 'Landing Page', 'Referrer', 'Conversion', 'Sent to Google', 'Sent at', 'AI Classification', 'AI Confidence', 'AI Reasoning', ) ); // Write data rows. foreach ( $entries as $entry ) { fputcsv( $output, array_map( array( __CLASS__, 'sanitize_csv_value' ), array( $entry->submitted_at, $entry->form_plugin, $entry->form_name, $entry->submitter_name, $entry->submitter_email, $entry->message_excerpt, isset( $entry->form_fields_summary ) ? $entry->form_fields_summary : '', $entry->traffic_source, $entry->utm_source, $entry->utm_medium, $entry->utm_campaign, $entry->utm_term, $entry->utm_content, $entry->click_id, $entry->click_id_type, $entry->page_url, $entry->landing_page, $entry->referrer, $entry->is_conversion ? 'Yes' : 'No', $entry->conversion_sent ? 'Yes' : 'No', $entry->conversion_sent_at ? $entry->conversion_sent_at : '', $entry->ai_classification, $entry->ai_confidence, $entry->ai_reasoning, ) ) ); } fclose( $output ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- Writing to php://output stream, WP_Filesystem not applicable. exit; } }