Current path: home/webdevt/www/demo2/wp-content/plugins/divi-contact-form-helper/d5/server/Admin/Managers/
?? Go up: /home/webdevt/www/demo2/wp-content/plugins/divi-contact-form-helper/d5/server/Admin
<?php
namespace KS_PAC_DCFH\Admin\Managers;
if (!defined('ABSPATH')) {
exit;
}
class GeneralDatabaseManager
{
private static $_instance;
public static function instance(): self
{
if (self::$_instance === null) {
self::$_instance = new self();
}
return self::$_instance;
}
public static function get_dashboard_stats(): array
{
$cache_key = 'dashboard_stats';
$cached_data = wp_cache_get($cache_key, 'dcfh_cached');
if (false === $cached_data) {
global $wpdb;
// phpcs:disable
$results = $wpdb->get_results("
SELECT
CASE
WHEN DATE(wpp.post_date) = CURDATE() THEN 'today'
WHEN DATE(wpp.post_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 'yesterday'
WHEN DATE(wpp.post_date) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 WEEK) AND CURDATE() THEN 'last_week'
WHEN DATE(wpp.post_date) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE() THEN 'last_month'
ELSE 'other'
END AS time_period,
COUNT(wpp.ID) AS total_posts,
wppm.meta_value AS contact_form_id
FROM $wpdb->posts AS wpp
LEFT JOIN $wpdb->postmeta wppm
ON wppm.post_id = wpp.ID
WHERE DATE(wpp.post_date) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE()
AND wpp.post_type = 'pwh_dcfh'
AND wppm.meta_key = '_pwh_dcfh_contact_form_id'
GROUP BY time_period, wppm.meta_value
ORDER BY wppm.meta_value");
// phpcs:enable
if (!empty($results)) {
$cached_data = [];
foreach ($results as $result) {
$cached_data[$result->contact_form_id][$result->time_period] = $result->total_posts;
}
wp_cache_set($cache_key, $cached_data, 'dcfh_cached', HOUR_IN_SECONDS); // 1-Hours Cache
}
}
return is_array($cached_data) ? $cached_data : [];
}
public static function get_latest_form_submission_data(string $contact_form_id): array
{
$cache_key = 'latest_form_submission_'.md5($contact_form_id);
$cached_data = wp_cache_get($cache_key, 'dcfh_cached_queries');
if ($cached_data !== false) {
return $cached_data;
}
global $wpdb;
$data = [];
// phpcs:disable
$post_excerpt = $wpdb->get_var(
$wpdb->prepare(
"SELECT p.post_excerpt
FROM $wpdb->posts p
INNER JOIN $wpdb->postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'pwh_dcfh'
AND p.post_status != 'trash'
AND pm.meta_key = '_pwh_dcfh_contact_form_id'
AND pm.meta_value = %s
ORDER BY p.post_date DESC
LIMIT 1",
$contact_form_id
)
);
// phpcs:enable
if (!empty($post_excerpt) && is_serialized($post_excerpt)) {
$post_excerpt = ks_pac_dcfh_app()->misc_utils()::maybe_unserialize($post_excerpt);
if (!empty($post_excerpt)) {
$data = $post_excerpt;
}
}
// Cache the result for 15 minutes
wp_cache_set($cache_key, $data, 'dcfh_cached_queries', 15 * MINUTE_IN_SECONDS);
return $data;
}
public static function get_attachment_id_by_name($name): ?string
{
global $wpdb;
$name = pathinfo($name, PATHINFO_FILENAME);
return $wpdb->get_var($wpdb->prepare("SELECT ID FROM $wpdb->posts WHERE post_name = '%s'", $name)); // phpcs:ignore
}
public static function get_entry_count_by_form_id(string $contact_form_id): int
{
global $wpdb;
// phpcs:disable
return (int)$wpdb->get_var($wpdb->prepare(
"SELECT COUNT(*)
FROM $wpdb->postmeta
WHERE meta_key = %s
AND meta_value = %s",
'_pwh_dcfh_contact_form_id',
$contact_form_id
));
// phpcs:enable
}
public static function get_contact_form_last_post_id(string $contact_form_id): int
{
$cache_key = 'dcfh_last_post_id_'.md5($contact_form_id);
$cached_post_id = wp_cache_get($cache_key, 'dcfh_cached_queries');
if (false !== $cached_post_id) {
return $cached_post_id ? (int)$cached_post_id : 0;
}
global $wpdb;
// phpcs:disable
$post_id = $wpdb->get_var($wpdb->prepare(
"SELECT post_id
FROM $wpdb->postmeta
WHERE meta_key = '_pwh_dcfh_contact_form_id'
AND meta_value = %s
ORDER BY meta_id DESC
LIMIT 1",
$contact_form_id
)); // db call ok; no-cache ok
// phpcs:enable
wp_cache_set($cache_key, $post_id, 'dcfh_cached_queries', 15 * MINUTE_IN_SECONDS);
return $post_id ? (int)$post_id : 0;
}
public static function get_contact_forms(): array
{
$cache_key = 'dcfh_contact_forms_'.md5(__METHOD__);
$cached_data = wp_cache_get($cache_key, 'dcfh_cached_queries');
if (false !== $cached_data) {
return is_array($cached_data) ? $cached_data : [];
}
global $wpdb;
// phpcs:disable
$results = $wpdb->get_results($wpdb->prepare(
"SELECT option_name, option_value
FROM $wpdb->options
WHERE option_name LIKE %s",
$wpdb->esc_like('_pwh_dcfh_contact_form_settings_').'%'
)); // db call ok; no-cache ok
// phpcs:enable
$contact_forms = [];
foreach ($results as $result) {
$form_settings = maybe_unserialize($result->option_value);
if (empty($form_settings['unique_id'])) {
continue;
}
$contact_forms[$form_settings['unique_id']] = [
'unique_id' => $form_settings['unique_id'],
'title' => $form_settings['title'] ?? '',
'page_id' => $form_settings['page_id'] ?? 0,
'max_entries_allowed' => $form_settings['max_entries_allowed'] ?? '-1',
'total_entries' => $form_settings['total_entries'] ?? 0,
'views' => $form_settings['views'] ?? 0,
'unique_views' => $form_settings['unique_views'] ?? 0,
];
}
// Cache even empty results to prevent repeated queries
wp_cache_set($cache_key, $contact_forms, 'dcfh_cached_queries', 15 * MINUTE_IN_SECONDS);
return $contact_forms;
}
public static function get_csv_data()
{
$key = 'dcfh_contact_form_csv_'.md5(__FUNCTION__);
$cached_data = wp_cache_get($key);
if (false === $cached_data) {
$cached_data = [];
$contact_forms = self::get_contact_forms();
if (!empty($contact_forms)) {
foreach ($contact_forms as $contact_form) {
$contact_form_id = $contact_form['unique_id'];
$contact_form_title = $contact_form['title'];
$total_rows = $contact_form['total_entries'];
$form_entries = ks_pac_dcfh_app()->general_db_manager()::get_latest_form_submission_data($contact_form_id);
// Always start output list
$output = "<ul>";
if (!empty($form_entries)) {
foreach ($form_entries as $form_entry) {
$field_id = $form_entry['id'];
$field_label = $form_entry['label'];
$output .= sprintf(
"<li><input type='checkbox' id='%s' name='contact_form_fields[%s]' value='%s' checked>".
"<label for='%s'>%s</label></li>",
esc_attr($field_id),
esc_attr($field_id),
esc_attr($field_label),
esc_attr($field_id),
esc_html($field_label)
);
}
}
// Always include system CSV placeholders
foreach (ks_pac_dcfh_app()->placeholder_helper()::get_csv_placeholders() as $key => $value) {
$output .= sprintf(
"<li><input type='checkbox' id='%s' name='contact_form_fields[%s]' value='%s' checked>".
"<label for='%s'>%s</label></li>",
esc_attr($key),
esc_attr($key),
esc_attr($value),
esc_attr($key),
esc_html($value)
);
}
$output .= "</ul>";
$cached_data[$contact_form_id] = [
'contact_form_id' => $contact_form_id,
'contact_form_title' => $contact_form_title,
'contact_form_fields_html' => $output,
'contact_form_total_entries' => number_format_i18n($total_rows)
];
wp_cache_set($key, $cached_data, 'dcfh_cached_queries', 2 * MINUTE_IN_SECONDS);
}
}
}
return $cached_data ?: [];
}
public static function get_email_template_data(): array
{
$key = 'dcfh_contact_form_template_'.md5(__FUNCTION__);
$cached_data = wp_cache_get($key);
if (false === $cached_data) {
$cached_data = [];
$contact_forms = self::get_contact_forms();
if (!empty($contact_forms)) {
foreach ($contact_forms as $contact_form) {
$contact_form_id = $contact_form['unique_id'];
$contact_form_title = $contact_form['title'];
$total_rows = $contact_form['total_entries'];
$form_entries = ks_pac_dcfh_app()->general_db_manager()::get_latest_form_submission_data($contact_form_id);
// Always start with empty list
$output = "<ul>";
if (!empty($form_entries)) {
foreach ($form_entries as $form_entry) {
$field_id = $form_entry['id'];
$output .= "<li class='keyword'>%%".$field_id."%%</li>";
}
}
// System placeholders should always be added
foreach (ks_pac_dcfh_app()->placeholder_helper()::get_system_placeholders() as $placeholder) {
$output .= "<li class='keyword'>%%".$placeholder."%%</li>";
}
$output .= "</ul>";
$cached_data[$contact_form_id] = [
'contact_form_id' => $contact_form_id,
'contact_form_title' => $contact_form_title,
'contact_form_fields_html' => $output,
'contact_form_total_entries' => number_format_i18n($total_rows)
];
wp_cache_set($key, $cached_data, 'dcfh_cached_queries', 2 * MINUTE_IN_SECONDS);
}
}
}
return $cached_data ?: [];
}
public static function get_contact_form_entry_count_by_status($contact_form_id, $allowed_post_statuses = ['private', 'draft']): int
{
global $wpdb;
$placeholders = implode(', ', array_fill(0, count($allowed_post_statuses), '%s'));
$params = array_merge(['_pwh_dcfh_contact_form_id', $wpdb->esc_like($contact_form_id)], $allowed_post_statuses);
// phpcs:disable
$count = $wpdb->get_var($wpdb->prepare("
SELECT COUNT(*) AS total_entries
FROM $wpdb->postmeta pm
INNER JOIN $wpdb->posts p ON pm.post_id = p.ID
WHERE pm.meta_key = %s
AND pm.meta_value LIKE %s
AND p.post_status IN ($placeholders)
", ...$params)); // db call ok; no-cache ok
// phpcs:enable
return $count !== null ? (int)$count : 0;
}
}