File: /home/goremar3/public_html/wp-content/plugins/popup-builder-block/includes/Helpers/DataBase.php
<?php
namespace PopupBuilderBlock\Helpers;
defined( 'ABSPATH' ) || exit;
/**
* Global helper class.
*
* @since 1.0.0
*/
class DataBase {
private static $LOGS_TABLE = 'pbb_logs';
private static $SUBSCRIBERS_TABLE = 'pbb_subscribers';
private static $COUNTRIES_TABLE = 'pbb_countries';
private static $BROWSERS_TABLE = 'pbb_browsers';
private static $REFERRERS_TABLE = 'pbb_referrers';
private static $LOG_COUNTRIES = 'pbb_log_countries';
private static $LOG_BROWSERS = 'pbb_log_browsers';
private static $LOG_REFERRERS = 'pbb_log_referrers';
private static $AB_TESTS_TABLE = 'pbb_ab_tests';
private static $AB_TESTS_VARIANTS_TABLE = 'pbb_ab_test_variants';
private static $POST_TABLE = 'posts';
public static $DATABASE_KEY = 'pbb_db_version';
public static $DATABASE_VERSION = '1.1.0';
/**
* Create the database table.
*
* @return void
* @since 1.0.0
*/
public static function createDB() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
// Table names with prefix
$logs_table = $wpdb->prefix . self::$LOGS_TABLE;
$subscribers_table = $wpdb->prefix . self::$SUBSCRIBERS_TABLE;
$countries_table = $wpdb->prefix . self::$COUNTRIES_TABLE;
$browsers_table = $wpdb->prefix . self::$BROWSERS_TABLE;
$referrers_table = $wpdb->prefix . self::$REFERRERS_TABLE;
$log_countries = $wpdb->prefix . self::$LOG_COUNTRIES;
$log_browsers = $wpdb->prefix . self::$LOG_BROWSERS;
$log_referrers = $wpdb->prefix . self::$LOG_REFERRERS;
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
// Main logs table
$sql = "CREATE TABLE IF NOT EXISTS $logs_table (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
campaign_id BIGINT UNSIGNED NOT NULL,
views INT DEFAULT 0,
converted INT DEFAULT 0,
date DATE NOT NULL,
device_desktop INT DEFAULT 0,
device_tablet INT DEFAULT 0,
device_mobile INT DEFAULT 0,
KEY campaign_id (campaign_id),
KEY date (date),
KEY campaign_date (campaign_id, date)
) $charset_collate;";
dbDelta($sql);
// Countries table
$sql = "CREATE TABLE IF NOT EXISTS $countries_table (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
country_name VARCHAR(2) NOT NULL
) $charset_collate;";
dbDelta($sql);
// Browsers table
$sql = "CREATE TABLE IF NOT EXISTS $browsers_table (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
browser_name VARCHAR(100) NOT NULL
) $charset_collate;";
dbDelta($sql);
// Referrers table
$sql = "CREATE TABLE IF NOT EXISTS $referrers_table (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
referrer_name TEXT NOT NULL
) $charset_collate;";
dbDelta($sql);
// Pivot: Logs x Countries
$sql = "CREATE TABLE IF NOT EXISTS $log_countries (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
log_id BIGINT UNSIGNED NOT NULL,
country_id INT UNSIGNED NOT NULL,
count INT NOT NULL DEFAULT 0,
FOREIGN KEY (log_id) REFERENCES $logs_table(id) ON DELETE CASCADE,
FOREIGN KEY (country_id) REFERENCES $countries_table(id) ON DELETE CASCADE,
KEY log_country (log_id, country_id)
) $charset_collate;";
dbDelta($sql);
// Pivot: Logs x Browsers
$sql = "CREATE TABLE IF NOT EXISTS $log_browsers (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
log_id BIGINT UNSIGNED NOT NULL,
browser_id INT UNSIGNED NOT NULL,
count INT NOT NULL DEFAULT 0,
FOREIGN KEY (log_id) REFERENCES $logs_table(id) ON DELETE CASCADE,
FOREIGN KEY (browser_id) REFERENCES $browsers_table(id) ON DELETE CASCADE,
KEY log_browser (log_id, browser_id)
) $charset_collate;";
dbDelta($sql);
// Pivot: Logs x Referrers
$sql = "CREATE TABLE IF NOT EXISTS $log_referrers (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
log_id BIGINT UNSIGNED NOT NULL,
referrer_id INT UNSIGNED NOT NULL,
count INT NOT NULL DEFAULT 0,
FOREIGN KEY (log_id) REFERENCES $logs_table(id) ON DELETE CASCADE,
FOREIGN KEY (referrer_id) REFERENCES $referrers_table(id) ON DELETE CASCADE,
KEY log_referrer (log_id, referrer_id)
) $charset_collate;";
dbDelta($sql);
// Create the subscribers table
$sql = "CREATE TABLE IF NOT EXISTS $subscribers_table (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
campaign_id BIGINT UNSIGNED NOT NULL,
campaign_title VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
form_data TEXT NOT NULL,
user_data VARCHAR(100) NOT NULL,
PRIMARY KEY (id),
KEY campaign_id (campaign_id),
KEY date (date),
KEY campaign_date (campaign_id, date)
) $charset_collate;";
dbDelta( $sql );
self::createABTestTables();
// Save the database version
add_option( self::$DATABASE_KEY, self::$DATABASE_VERSION );
}
public static function createABTestTables() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
// Table names with prefix
$ab_tests_table = $wpdb->prefix . self::$AB_TESTS_TABLE;
$campaigns_table = $wpdb->prefix . self::$POST_TABLE;
$ab_test_variants_table = $wpdb->prefix . self::$AB_TESTS_VARIANTS_TABLE;
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
// Create the AB tests table
$sql = "CREATE TABLE IF NOT EXISTS $ab_tests_table (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
type VARCHAR(10) NOT NULL,
status TINYINT UNSIGNED NOT NULL,
metric VARCHAR(10) DEFAULT NULL,
winner BIGINT UNSIGNED DEFAULT NULL,
started_at DATETIME DEFAULT NULL,
duration INT UNSIGNED DEFAULT NULL,
PRIMARY KEY (id)
) $charset_collate;";
dbDelta( $sql );
// Create the AB tests variants table
$sql = "CREATE TABLE IF NOT EXISTS $ab_test_variants_table (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
test_id BIGINT UNSIGNED NOT NULL,
campaign_id BIGINT UNSIGNED NOT NULL,
views INT DEFAULT 0,
converted INT DEFAULT 0,
PRIMARY KEY (id),
KEY test_id (test_id),
KEY campaign_id (campaign_id),
FOREIGN KEY (test_id) REFERENCES $ab_tests_table(id) ON DELETE CASCADE,
FOREIGN KEY (campaign_id) REFERENCES $campaigns_table(ID) ON DELETE CASCADE
) $charset_collate;";
dbDelta( $sql );
}
/**
* Insert or update log entry.
*
* @param int $campaign_id Campaign ID.
* @param string $date Date of the log entry.
* @param string $device_type Device type (desktop, mobile, tablet).
*
* @return bool
* @since 1.0.0
*/
public static function insertOrUpdateLog($campaign_id, $date, $device_type = null) {
global $wpdb;
$table = esc_sql( $wpdb->prefix . 'pbb_logs' );
// Check if log already exists for this campaign + date
$existing = $wpdb->get_row(
$wpdb->prepare(
"SELECT * FROM %i WHERE campaign_id = %d AND date = %s",
$table,
$campaign_id,
$date
)
);
$device = $device_type ? "device_$device_type" : false;
if ($existing) {
$fields = ['views' => $existing->views + 1];
if($device) {
$fields[$device] = $existing->{"$device"} + 1;
}
$wpdb->update($table, $fields, ['id' => $existing->id]);
return $existing->id;
} else {
$fields = [
'campaign_id' => $campaign_id,
'date' => $date,
'views' => 1,
];
if($device) {
$fields[$device] = 1;
}
$wpdb->insert($table, $fields);
return $wpdb->insert_id;
}
}
private static function insertOrUpdateTable($table, $name, $value) {
global $wpdb;
$table_name = $wpdb->prefix . $table;
// Check if the name already exists in the table
$id = $wpdb->get_var(
$wpdb->prepare(
"SELECT id FROM %i WHERE %i = %s",
$table_name,
$name,
$value
)
);
if (!$id) {
$wpdb->insert($table_name, [$name => $value]);
return $wpdb->insert_id;
}
return $id;
}
private static function insertOrUpdatePivotTable($pivot_table, $id_name, $log_id, $id) {
global $wpdb;
$table_name = $wpdb->prefix . $pivot_table;
// Check if the log_id and id already exist in the pivot table
$row = $wpdb->get_row(
$wpdb->prepare(
"SELECT id, count FROM %i WHERE log_id = %d AND %i = %d",
$table_name,
$log_id,
$id_name,
$id
)
);
if ($row) {
$wpdb->update($table_name, ['count' => $row->count + 1], ['id' => $row->id]);
} else {
$wpdb->insert($table_name, [
'log_id' => $log_id,
$id_name => $id,
'count' => 1
]);
}
}
public static function insertOrUpdateBrowser($log_id, $browser_name) {
$id = self::insertOrUpdateTable('pbb_browsers', 'browser_name', $browser_name);
self::insertOrUpdatePivotTable('pbb_log_browsers', 'browser_id', $log_id, $id);
}
public static function insertOrUpdateCountry($log_id, $country_name) {
$id = self::insertOrUpdateTable('pbb_countries', 'country_name', $country_name);
self::insertOrUpdatePivotTable('pbb_log_countries', 'country_id', $log_id, $id);
}
public static function insertOrUpdateReferrer($log_id, $referrer_name) {
$id = self::insertOrUpdateTable('pbb_referrers', 'referrer_name', $referrer_name);
self::insertOrUpdatePivotTable('pbb_log_referrers', 'referrer_id', $log_id, $id);
}
public static function get_devices($campaign_id, $start_date, $end_date) {
global $wpdb;
$table_name = $wpdb->prefix . self::$LOGS_TABLE;
if ( $campaign_id ) {
return $wpdb->get_results(
$wpdb->prepare(
"SELECT SUM(device_desktop) as desktop,
SUM(device_tablet) as tablet,
SUM(device_mobile) as mobile
FROM %i WHERE date BETWEEN %s AND %s AND campaign_id = %d",
$table_name,
$start_date,
$end_date,
$campaign_id
)
);
} else {
return $wpdb->get_results(
$wpdb->prepare(
"SELECT SUM(device_desktop) as desktop,
SUM(device_tablet) as tablet,
SUM(device_mobile) as mobile
FROM %i WHERE date BETWEEN %s AND %s",
$table_name,
$start_date,
$end_date
)
);
}
}
private static function get_data($campaign_id, $start_date, $end_date, $table, $log_table, $column, $id) {
global $wpdb;
$table_name = $wpdb->prefix . self::$LOGS_TABLE;
$table = $wpdb->prefix . $table;
$log_table = $wpdb->prefix . $log_table;
if ( $campaign_id ) {
return $wpdb->get_results(
$wpdb->prepare(
"SELECT t.%i, SUM(lt.count) AS total_count FROM %i logs JOIN %i lt ON lt.log_id = logs.id JOIN %i t ON t.id = lt.%i WHERE logs.date BETWEEN %s AND %s AND campaign_id = %d GROUP BY t.%i ORDER BY total_count DESC;",
$column,
$table_name,
$log_table,
$table,
$id,
$start_date,
$end_date,
$campaign_id,
$column
)
);
} else {
return $wpdb->get_results(
$wpdb->prepare(
"SELECT t.%i, SUM(lt.count) AS total_count FROM %i logs JOIN %i lt ON lt.log_id = logs.id JOIN %i t ON t.id = lt.%i WHERE logs.date BETWEEN %s AND %s GROUP BY t.%i ORDER BY total_count DESC;",
$column,
$table_name,
$log_table,
$table,
$id,
$start_date,
$end_date,
$column
)
);
}
}
public static function get_countries($campaign_id, $start_date, $end_date) {
return self::get_data($campaign_id, $start_date, $end_date, 'pbb_countries', 'pbb_log_countries', 'country_name', 'country_id');
}
public static function get_browsers($campaign_id, $start_date, $end_date) {
return self::get_data($campaign_id, $start_date, $end_date, 'pbb_browsers', 'pbb_log_browsers', 'browser_name', 'browser_id');
}
public static function get_referrers($campaign_id, $start_date, $end_date) {
return self::get_data($campaign_id, $start_date, $end_date, 'pbb_referrers', 'pbb_log_referrers', 'referrer_name', 'referrer_id');
}
public static function get_campaigns($campaign_id, $start_date, $end_date) {
global $wpdb;
$table_name = $wpdb->prefix . self::$LOGS_TABLE;
if ( $campaign_id ) {
return $wpdb->get_results(
$wpdb->prepare(
"SELECT
campaign_id,
SUM(converted) as count
FROM %i WHERE date BETWEEN %s AND %s AND campaign_id = %d GROUP BY campaign_id ORDER BY count DESC;",
$table_name,
$start_date,
$end_date,
$campaign_id
)
);
} else {
return $wpdb->get_results(
$wpdb->prepare(
"SELECT
campaign_id,
SUM(converted) as count
FROM %i WHERE date BETWEEN %s AND %s GROUP BY campaign_id ORDER BY count DESC;",
$table_name,
$start_date,
$end_date
)
);
}
}
public static function get_convertion( $campaign_id, $start_date, $end_date ) {
global $wpdb;
$table_name = $wpdb->prefix . self::$LOGS_TABLE;
if ( $campaign_id ) {
$grouped_data = $wpdb->get_results(
$wpdb->prepare(
"SELECT DATE(date) AS dateLog, SUM(views) AS totalViews, SUM(converted) AS totalConverted FROM %i WHERE DATE(date) BETWEEN %s AND %s AND campaign_id = %d GROUP BY DATE(date);",
$table_name,
$start_date,
$end_date,
$campaign_id
)
);
$total_data = $wpdb->get_results(
$wpdb->prepare(
"SELECT SUM(views) AS totalViews, SUM(converted) AS totalConverted FROM %i WHERE DATE(date) BETWEEN %s AND %s AND campaign_id = %d",
$table_name,
$start_date,
$end_date,
$campaign_id
)
);
} else {
$grouped_data = $wpdb->get_results(
$wpdb->prepare(
"SELECT DATE(date) AS dateLog, SUM(views) AS totalViews, SUM(converted) AS totalConverted FROM %i WHERE DATE(date) BETWEEN %s AND %s GROUP BY DATE(date);",
$table_name,
$start_date,
$end_date
)
);
$total_data = $wpdb->get_results(
$wpdb->prepare(
"SELECT SUM(views) AS totalViews, SUM(converted) AS totalConverted FROM %i WHERE DATE(date) BETWEEN %s AND %s",
$table_name,
$start_date,
$end_date
)
);
}
return array(
'group' => $grouped_data,
'total' => $total_data,
);
}
/**
* Drop the database table.
*
* @return void
* @since 1.0.0
*/
public static function dropDB() {
global $wpdb;
$tables = array(
'log_countries' => self::$LOG_COUNTRIES,
'log_browsers' => self::$LOG_BROWSERS,
'log_referrers' => self::$LOG_REFERRERS,
'logs' => self::$LOGS_TABLE,
'subscribers' => self::$SUBSCRIBERS_TABLE,
'browsers' => self::$BROWSERS_TABLE,
'countries' => self::$COUNTRIES_TABLE,
'referrers' => self::$REFERRERS_TABLE,
);
foreach ( $tables as $table ) {
$table_name = $wpdb->prefix . $table;
$wpdb->query(
$wpdb->prepare( "DROP TABLE IF EXISTS %i;", $table_name )
);
}
// Delete the database version option
delete_option( self::$DATABASE_KEY );
}
/**
* Insert into database table.
*
* @return bool
* @since 1.0.0
*/
public static function insertDB( $table, $data ) {
global $wpdb;
$table_name = $wpdb->prefix . $table;
$wpdb->insert( $table_name, $data );
return $wpdb->insert_id;
}
public static function insert_subscriber( $data ) {
if ( empty( $data['email'] ) || ! is_email( $data['email'] ) ) {
return rest_ensure_response([
'status' => 'error',
'message' => esc_html__('Invalid email address', 'popup-builder-block')
]);
}
$where = array(
"email = %s" => esc_sql( $data['email'] ),
"campaign_id = %d" => esc_sql( $data['campaign_id'] ),
);
$is_old_data = DataBase::getDB( 'id', 'pbb_subscribers', $where, 1 );
// Insert into the database
if ( $is_old_data ) {
return false;
}
return DataBase::insertDB( 'pbb_subscribers', $data );
}
/**
* Get data from database table.
* @param array|string $columns
* @param string $table
* @param array $where
* @param int $limit
* @param bool $count
* @param string|array $order_by
* @param string|array $group_by
*
* @return array
* @since 1.0.0
*/
public static function getDB( $columns, $table, $where = [], $limit = 0, $count = false, $order_by = '', $group_by = '' ) {
global $wpdb;
// Handle table name safely
$table_name = esc_sql( $wpdb->prefix . $table );
// Handle columns (support string or array)
if ( is_array( $columns ) ) {
$columns = array_map( 'esc_sql', $columns );
$columns = implode( ', ', $columns );
} else {
$columns = esc_sql( $columns );
}
// Base SQL
$sql = $count
? "SELECT COUNT($columns) FROM $table_name"
: "SELECT $columns FROM $table_name";
$where_sql = '';
$prepare_values = [];
// Handle array-based WHERE conditions
if ( is_array( $where ) && ! empty( $where ) ) {
$where_parts = [];
foreach ( $where as $condition => $values ) {
$where_parts[] = '(' . $condition . ')';
if ( is_array( $values ) ) {
$prepare_values = array_merge( $prepare_values, $values );
} else {
$prepare_values[] = $values;
}
}
$where_sql = ' WHERE ' . implode( ' AND ', $where_parts );
}
$sql .= $where_sql;
// GROUP BY support
if ( $group_by ) {
if ( is_array( $group_by ) ) {
$group_by = implode( ', ', array_map( 'esc_sql', $group_by ) );
} else {
$group_by = esc_sql( $group_by );
}
$sql .= " GROUP BY $group_by";
}
// ORDER BY (sanitized)
if ( $order_by ) {
if ( is_array( $order_by ) ) {
$order_by = implode( ', ', array_map( 'esc_sql', $order_by ) );
} else {
$order_by = esc_sql( $order_by );
}
$sql .= " ORDER BY $order_by";
}
// LIMIT (integer)
if ( $limit ) {
$limit = absint( $limit );
$sql .= " LIMIT %d";
$prepare_values[] = $limit;
}
// Prepare query if values exist
if ( ! empty( $prepare_values ) ) {
$sql = $wpdb->prepare( $sql, $prepare_values ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
}
return $wpdb->get_results( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
}
/**
* Update data in database table.
*
* @param string $table
* @param array $data
* @param array $where
* @return bool
* @since 1.0.0
*/
public static function updateDB( $table, $data, $where ) {
global $wpdb;
$table_name = $wpdb->prefix . $table;
return $wpdb->update( $table_name, $data, $where );
}
/**
* Delete data from database table.
*
* @param string $table
* @param int|array $id
* @param string $field
* @return bool
* @since 1.0.0
*/
public static function deleteDB( $table, $id, $field = 'id' ) {
global $wpdb;
if ( ! isset( $id ) ) {
return false;
}
// If id is array then delete multiple rows and if id is integer then delete single row
$table_name = $wpdb->prefix . $table;
if ( is_array( $id ) ) {
$placeholders = implode( ',', array_fill( 0, count( $id ), '%d' ) );
$sql = $wpdb->prepare(
"DELETE FROM %i WHERE $field IN ($placeholders)", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
array_merge( array( $table_name ), $id )
);
} else {
$sql = $wpdb->prepare(
"DELETE FROM %i WHERE %i = %d",
$table_name,
$field,
$id
);
}
return $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
}
public static function deleteExpiredData($data) {
global $wpdb;
$data_tables = array(
self::$LOG_COUNTRIES, // Delete from pivot tables first (foreign key constraints)
self::$LOG_BROWSERS,
self::$LOG_REFERRERS,
self::$LOGS_TABLE, // Delete from main logs table
);
$expire_time = $data['expire_time'] ?? '';
$start_date = $data['start_date'] ?? '';
$end_date = $data['end_date'] ?? '';
if( !empty($expire_time) ) {
$date_limit = gmdate('Y-m-d', strtotime("-{$expire_time} years"));
// Step 1: Get old log IDs
$logs_table = $wpdb->prefix . self::$LOGS_TABLE;
$old_log_ids = $wpdb->get_col(
$wpdb->prepare(
"SELECT id FROM %i WHERE date < %s",
$logs_table,
$date_limit
)
);
if (empty($old_log_ids)) {
return []; // No old logs to delete
}
return self::delete_logs($wpdb, $data_tables, $old_log_ids);
}
if( !empty($start_date) && !empty($end_date) ) {
// Step 1: Get log IDs in the given date range
$logs_table = $wpdb->prefix . self::$LOGS_TABLE;
$range_log_ids = $wpdb->get_col(
$wpdb->prepare(
"SELECT id FROM %i WHERE date BETWEEN %s AND %s",
$logs_table,
$start_date,
$end_date
)
);
if (empty($range_log_ids)) {
return []; // No logs in this range
}
return self::delete_logs($wpdb, $data_tables, $range_log_ids);
}
}
private static function delete_logs($wpdb, $data_tables, $log_ids) {
$deleted_logs = array();
$placeholders = implode(',', array_fill(0, count($log_ids), '%d'));
foreach ($data_tables as $table) {
$table_name = $wpdb->prefix . $table;
// Use `id` for the main logs table, `log_id` for others
$column = ($table === self::$LOGS_TABLE) ? 'id' : 'log_id';
$deleted_logs[] = $wpdb->query(
$wpdb->prepare(
"DELETE FROM %i WHERE $column IN ($placeholders)", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
array_merge( array( $table_name ), $log_ids )
)
);
}
return $deleted_logs;
}
}