wpdb = $wpdb; } public static function get_instance() { static $instance = null; if ( ! $instance ) { $instance = new self(); } return $instance; } public static function remove_table() { global $wpdb; $table_name = $wpdb->prefix . self::TABLE; $wpdb->query( "DROP TABLE IF EXISTS $table_name" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared } public static function create_table() { global $wpdb; $table_name = $wpdb->prefix . self::TABLE; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS $table_name ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, transaction_id VARCHAR(100) NOT NULL, form_id VARCHAR(50) NULL, form_name VARCHAR(200) NULL, page_id VARCHAR(50) NULL, user_id VARCHAR(50) NULL, user_ip VARCHAR(45) NULL, user_agent VARCHAR(255) NULL, customer_id VARCHAR(100) NULL, customer_name VARCHAR(60) NULL, customer_email VARCHAR(100) NULL, amount DECIMAL NOT NULL, refund_amount DECIMAL DEFAULT 0, currency CHAR(3) NOT NULL, recurring TINYINT(1) DEFAULT 0, recurring_interval VARCHAR(50) NULL, recurring_count INT NULL, payment_status VARCHAR(50) NOT NULL, payment_method VARCHAR(50) NULL, form_data TEXT NULL, meta_data TEXT NULL, comment TEXT NULL, source VARCHAR(100) NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY unique_transaction (transaction_id), KEY transaction_id_idx (transaction_id), KEY customer_email_idx (customer_email), KEY customer_name_idx (customer_name), KEY amount_idx (amount), KEY user_id_idx (user_id), KEY payment_status_idx (payment_status), KEY created_at_idx (created_at) ) $charset_collate;"; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; dbDelta( $sql ); } public function insert_transaction( $transaction_id, $args ) { $defaults = array( 'form_id' => isset( $_POST['_wpcf7'] ) ? absint( $_POST['_wpcf7'] ) : null, // phpcs:ignore WordPress.Security.NonceVerification.Missing 'form_name' => null, 'page_id' => isset( $_POST['_wpcf7_container_post'] ) ? absint( $_POST['_wpcf7_container_post'] ) : null, // phpcs:ignore WordPress.Security.NonceVerification.Missing 'user_id' => get_current_user_id() ?: null, 'user_ip' => Utils::get_client_ip(), 'user_agent' => isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_textarea_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '', 'customer_id' => null, 'customer_name' => null, 'customer_email' => null, 'amount' => 0, 'refund_amount' => 0, 'currency' => '', 'recurring' => 0, 'recurring_interval' => null, 'recurring_count' => null, 'payment_status' => '', 'payment_method' => null, 'form_data' => null, 'meta_data' => null, 'comment' => '', 'source' => null, 'transaction_id' => $transaction_id, ); $data = wp_parse_args( $args, $defaults ); $data['form_data'] = ( is_array( $data['form_data'] ) || is_object( $data['form_data'] ) ) ? wp_json_encode( $data['form_data'] ) : $data['form_data']; $data['meta_data'] = ( is_array( $data['meta_data'] ) || is_object( $data['meta_data'] ) ) ? wp_json_encode( $data['meta_data'] ) : $data['meta_data']; $result = $this->wpdb->insert( $this->wpdb->prefix . self::TABLE, $data ); if ( $result ) { return $this->wpdb->insert_id; } return 0; } public function update_by_transaction_id( $transaction_id, $data ) { return $this->wpdb->update( $this->wpdb->prefix . self::TABLE, $data, array( 'transaction_id' => $transaction_id ) ); } public function delete_by_id( $id ) { return $this->wpdb->delete( $this->wpdb->prefix . self::TABLE, array( 'id' => $id ) ); } public function get_transactions( $args = array() ) { $defaults = array( 'search' => '', 'status' => '', 'date_start' => '', 'date_end' => '', 'page' => 1, 'per_page' => 20, 'order_by' => 'created_at', 'order_dir' => 'DESC', 'no_limit' => false, ); $args = wp_parse_args( $args, $defaults ); $table = $this->wpdb->prefix . self::TABLE; $where = array(); $params = array(); if ( ! empty( $args['search'] ) ) { $search = '%' . $this->wpdb->esc_like( $args['search'] ) . '%'; $where[] = '(payment_status LIKE %s OR form_data LIKE %s OR customer_email LIKE %s OR customer_name LIKE %s)'; $params[] = $search; $params[] = $search; $params[] = $search; $params[] = $search; } if ( ! empty( $args['status'] ) ) { $where[] = 'payment_status = %s'; $params[] = $args['status']; } if ( ! empty( $args['date_start'] ) ) { $where[] = 'created_at >= %s'; $params[] = $args['date_start']; } if ( ! empty( $args['date_end'] ) ) { $where[] = 'created_at <= %s'; $params[] = $args['date_end']; } $where_sql = $where ? ( 'WHERE ' . implode( ' AND ', $where ) ) : ''; // Whitelist sort columns $allowed_order_by = array( 'payment_status', 'amount', 'customer_name', 'created_at', 'payment_method' ); $order_by = in_array( $args['order_by'], $allowed_order_by, true ) ? $args['order_by'] : 'created_at'; $order_dir = strtoupper( $args['order_dir'] ) === 'ASC' ? 'ASC' : 'DESC'; // Count $count_sql = "SELECT COUNT(*) FROM $table $where_sql"; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $total = $params ? $this->wpdb->get_var( $this->wpdb->prepare( $count_sql, ...$params ) ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared : $this->wpdb->get_var( $count_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared if ( ! empty( $args['no_limit'] ) ) { $data_sql = "SELECT * FROM $table $where_sql ORDER BY $order_by $order_dir"; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $sql = $params ? $this->wpdb->prepare( $data_sql, ...$params ) : $data_sql; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $results = $this->wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared return array( 'data' => $results, 'total' => $total, 'page' => 1, 'per_page' => $total, ); } $offset = ( $args['page'] - 1 ) * $args['per_page']; $data_sql = "SELECT * FROM $table $where_sql ORDER BY $order_by $order_dir LIMIT %d OFFSET %d"; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $data_params = $params; $data_params[] = (int) $args['per_page']; $data_params[] = (int) $offset; $sql = $this->wpdb->prepare( $data_sql, ...$data_params ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $results = $this->wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared return array( 'data' => $results, 'total' => $total, 'page' => $args['page'], 'per_page' => $args['per_page'], ); } public function get_summary( $args = array() ) { $defaults = array( 'search' => '', 'status' => '', 'date_start' => '', 'date_end' => '', ); $args = wp_parse_args( $args, $defaults ); $table = $this->wpdb->prefix . self::TABLE; $where = array(); $params = array(); if ( ! empty( $args['search'] ) ) { $search = '%' . $this->wpdb->esc_like( $args['search'] ) . '%'; $where[] = '(payment_status LIKE %s OR form_data LIKE %s OR customer_email LIKE %s OR customer_name LIKE %s)'; $params[] = $search; $params[] = $search; $params[] = $search; $params[] = $search; } if ( ! empty( $args['status'] ) ) { $where[] = 'payment_status = %s'; $params[] = $args['status']; } if ( ! empty( $args['date_start'] ) ) { $where[] = 'created_at >= %s'; $params[] = $args['date_start']; } if ( ! empty( $args['date_end'] ) ) { $where[] = 'created_at <= %s'; $params[] = $args['date_end']; } $where_sql = $where ? ( 'WHERE ' . implode( ' AND ', $where ) ) : ''; // Total revenue $revenue_sql = "SELECT COALESCE(SUM(amount - refund_amount),0) FROM $table $where_sql"; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $total_revenue = $params ? floatval( $this->wpdb->get_var( $this->wpdb->prepare( $revenue_sql, ...$params ) ) ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared : floatval( $this->wpdb->get_var( $revenue_sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared // Total count $count_sql = "SELECT COUNT(*) FROM $table $where_sql"; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $total_tx = $params ? intval( $this->wpdb->get_var( $this->wpdb->prepare( $count_sql, ...$params ) ) ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared : intval( $this->wpdb->get_var( $count_sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared // Status counts $status_sql = "SELECT payment_status, COUNT(*) as cnt FROM $table $where_sql GROUP BY payment_status"; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $status_counts = array(); $rows = $params ? $this->wpdb->get_results( $this->wpdb->prepare( $status_sql, ...$params ), ARRAY_A ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared : $this->wpdb->get_results( $status_sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared foreach ( ( $rows ?: array() ) as $r ) { $status_counts[ $r['payment_status'] ] = intval( $r['cnt'] ); } // Revenue by status $rev_status_sql = "SELECT payment_status, COALESCE(SUM(amount - refund_amount),0) as revenue FROM $table $where_sql GROUP BY payment_status"; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $revenue_by_status = array(); $rrows = $params ? $this->wpdb->get_results( $this->wpdb->prepare( $rev_status_sql, ...$params ), ARRAY_A ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared : $this->wpdb->get_results( $rev_status_sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared foreach ( ( $rrows ?: array() ) as $r ) { $revenue_by_status[ $r['payment_status'] ] = floatval( $r['revenue'] ); } return array( 'total_revenue' => $total_revenue, 'total_transactions' => $total_tx, 'status_counts' => $status_counts, 'revenue_by_status' => $revenue_by_status, ); } public function get_transaction( $id ) { $table = $this->wpdb->prefix . self::TABLE; $txn = $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM $table WHERE id = %d", $id ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared if ( $txn ) { if ( isset( $txn['form_data'] ) ) { $decoded = json_decode( $txn['form_data'], true ); if ( json_last_error() === JSON_ERROR_NONE ) { $txn['form_data'] = $decoded; } } if ( isset( $txn['meta_data'] ) ) { $decoded = json_decode( $txn['meta_data'], true ); if ( json_last_error() === JSON_ERROR_NONE ) { $txn['meta_data'] = $decoded; } } } return $txn; } }