get_charset_collate();
// SQL to create necessary tables
$sql = "
CREATE TABLE {$wpdb->prefix}sige_place (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
place TINYTEXT NOT NULL,
comments TEXT NOT NULL,
PRIMARY KEY (id)
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_speakers (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
research_degree VARCHAR(50) NOT NULL,
full_name TINYTEXT NOT NULL,
email VARCHAR(100) NOT NULL,
institution TINYTEXT NOT NULL,
PRIMARY KEY (id)
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_event_names (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
acronym VARCHAR(50) NOT NULL,
name TINYTEXT NOT NULL,
responsible_for TINYTEXT NOT NULL,
PRIMARY KEY (id)
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_meta_keys (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
meta_key VARCHAR(255) NOT NULL,
status TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY meta_key (meta_key)
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_activities (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
name TINYTEXT NOT NULL,
description TEXT,
start_date DATE,
start_time TIME,
end_date DATE,
end_time TIME,
type VARCHAR(50) NOT NULL,
modality VARCHAR(50) NOT NULL,
status VARCHAR(50) NOT NULL,
curriculum TEXT,
comment TEXT,
place_id MEDIUMINT(9),
speaker_id MEDIUMINT(9),
event_name_id MEDIUMINT(9) NOT NULL,
security_credential VARCHAR(20) NOT NULL,
chair TINYTEXT,
target_audience VARCHAR(50),
attendance_control ENUM('yes', 'no') NOT NULL DEFAULT 'no',
slots INT(5) NULL,
activity_matching_key INT(5) NULL,
PRIMARY KEY (id),
KEY place_id (place_id),
KEY speaker_id (speaker_id),
KEY event_name_id (event_name_id),
CONSTRAINT fk_place FOREIGN KEY (place_id) REFERENCES {$wpdb->prefix}sige_place(id) ON DELETE CASCADE,
CONSTRAINT fk_speaker FOREIGN KEY (speaker_id) REFERENCES {$wpdb->prefix}sige_speakers(id) ON DELETE CASCADE,
CONSTRAINT fk_event_name FOREIGN KEY (event_name_id) REFERENCES {$wpdb->prefix}sige_event_names(id) ON DELETE SET NULL
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_enrollments (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
activity_id MEDIUMINT(9) NOT NULL,
user_id BIGINT(20) UNSIGNED NOT NULL,
enrollment_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
status VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (activity_id) REFERENCES {$wpdb->prefix}sige_activities(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES {$wpdb->prefix}users(ID) ON DELETE CASCADE
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_cancellation_record (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
activity_id MEDIUMINT(9) NOT NULL,
user_id BIGINT(20) UNSIGNED NOT NULL,
date_of_cancelation DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
reasons VARCHAR(50) NULL,
PRIMARY KEY (id),
FOREIGN KEY (activity_id) REFERENCES {$wpdb->prefix}sige_activities(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES {$wpdb->prefix}users(ID) ON DELETE CASCADE
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_activity_notices (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
type VARCHAR(50) NOT NULL,
notice TEXT NOT NULL,
PRIMARY KEY (id)
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_display_options (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
field_name VARCHAR(50) NOT NULL,
activity_type VARCHAR(50) NOT NULL,
display TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (id)
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_enrollment_button_style (
button_action VARCHAR(50) NOT NULL,
button_label VARCHAR(50) NOT NULL,
background_color VARCHAR(7) NOT NULL,
font_size VARCHAR(10) NOT NULL,
font_family VARCHAR(100) NOT NULL,
font_weight VARCHAR(10) NOT NULL,
PRIMARY KEY (button_action)
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_enrollment_status_style (
status_action VARCHAR(50) NOT NULL,
status_label VARCHAR(50) NOT NULL,
font_color VARCHAR(7) NOT NULL,
font_size VARCHAR(10) NOT NULL,
font_family VARCHAR(100) NOT NULL,
font_weight VARCHAR(10) NOT NULL,
PRIMARY KEY (status_action)
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_email_messages (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
email_sender VARCHAR(100) NOT NULL,
email_subject VARCHAR(255) NOT NULL,
email_message TEXT NOT NULL,
status_action VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (status_action) REFERENCES {$wpdb->prefix}sige_enrollment_status_style(status_action) ON DELETE CASCADE
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_enrollment_attendance_control (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
enrollment_id MEDIUMINT(9) NOT NULL,
attended ENUM('yes', 'no') DEFAULT NULL,
PRIMARY KEY (id),
FOREIGN KEY (enrollment_id) REFERENCES {$wpdb->prefix}sige_enrollments(id) ON DELETE CASCADE
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_enrollment_online_attendance_control (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
enrollment_id MEDIUMINT(9) NOT NULL,
attended ENUM('yes', 'no') DEFAULT NULL,
PRIMARY KEY (id),
FOREIGN KEY (enrollment_id) REFERENCES {$wpdb->prefix}sige_enrollments(id) ON DELETE CASCADE
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_confidentiality_message (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
information_type VARCHAR(255) NOT NULL,
message TEXT NULL,
PRIMARY KEY (id)
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_confidentiality_user_option (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
user_id BIGINT(20) UNSIGNED NOT NULL,
year INT(4) NOT NULL,
confidentiality ENUM('yes', 'no') DEFAULT NULL,
sharing_information ENUM('yes', 'no') DEFAULT NULL,
PRIMARY KEY (id),
KEY user_id (user_id),
CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES {$wpdb->prefix}users(ID) ON DELETE CASCADE
) $charset_collate;
CREATE TABLE {$wpdb->prefix}sige_attendance_email_messages (
id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
email_sender VARCHAR(100) NOT NULL,
email_subject VARCHAR(255) NOT NULL,
email_message TEXT NOT NULL,
status_action VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
) $charset_collate;
";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);
// Insert default display options for each activity type
$default_options = [
'Notices',
'Speaker',
'Curriculum Vitae',
'Date',
'Hour',
'Place',
'Modality',
'Enrollment Status',
'Comment',
'Security Credential',
'Target Audience',
'Chair',
'Slots'
];
$activity_types = [
'Main Event',
'Presentation',
'Short-course',
'Lecture',
'Workshop',
'Others'
];
foreach ($default_options as $option) {
foreach ($activity_types as $activity_type) {
if (!$wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->prefix}sige_display_options WHERE field_name = %s AND activity_type = %s", $option, $activity_type))) {
$wpdb->insert("{$wpdb->prefix}sige_display_options", ['field_name' => $option, 'activity_type' => $activity_type, 'display' => 1]);
}
}
}
// Insert default button styles
$default_styles = [
['button_action' => 'request_enrollment', 'button_label' => 'Request Enrollment', 'background_color' => '#0073aa', 'font_size' => '14px', 'font_family' => 'Arial, sans-serif', 'font_weight' => 'normal'],
['button_action' => 'cancel_enrollment', 'button_label' => 'Cancel Enrollment', 'background_color' => '#ac1226', 'font_size' => '14px', 'font_family' => 'Arial, sans-serif', 'font_weight' => 'normal']
];
foreach ($default_styles as $style) {
$wpdb->insert("{$wpdb->prefix}sige_enrollment_button_style", $style);
}
// Insert default status styles
$default_status_styles = [
['status_action' => 'Not Requested', 'status_label' => 'Not Requested', 'font_color' => '#000000', 'font_size' => '14px', 'font_family' => 'Arial, sans-serif', 'font_weight' => 'normal'],
['status_action' => 'Enrollment Canceled by User', 'status_label' => 'Enrollment Canceled by User', 'font_color' => '#ac1226', 'font_size' => '14px', 'font_family' => 'Arial, sans-serif', 'font_weight' => 'normal'],
['status_action' => 'Under Analysis', 'status_label' => 'Under Analysis', 'font_color' => '#0073aa', 'font_size' => '14px', 'font_family' => 'Arial, sans-serif', 'font_weight' => 'normal'],
['status_action' => 'Approved', 'status_label' => 'Approved', 'font_color' => '#008000', 'font_size' => '14px', 'font_family' => 'Arial, sans-serif', 'font_weight' => 'normal'],
['status_action' => 'Waiting List', 'status_label' => 'Waiting List', 'font_color' => '#CC9900', 'font_size' => '14px', 'font_family' => 'Arial, sans-serif', 'font_weight' => 'normal'],
['status_action' => 'Not Approved', 'status_label' => 'Not Approved', 'font_color' => '#46384C', 'font_size' => '14px', 'font_family' => 'Arial, sans-serif', 'font_weight' => 'normal']
];
foreach ($default_status_styles as $style) {
$wpdb->insert("{$wpdb->prefix}sige_enrollment_status_style", $style);
}
// Insert default confidentiality messages
$default_confidentiality_messages = [
['information_type' => 'Credential', 'message' => 'The information provided by the user will be used to verify the user\'s credentials.'],
['information_type' => 'Sharing Information', 'message' => 'The information provided by the user will be shared with ours sponsors.']
];
foreach ($default_confidentiality_messages as $message) {
$wpdb->insert("{$wpdb->prefix}sige_confidentiality_message", $message);
}
// Insert default email messages
$default_email_messages = [
['email_sender' => get_bloginfo('admin_email'), 'email_subject' => 'Not Requested', 'email_message' => 'Your enrollment status is Not Requested.', 'status_action' => 'Not Requested'],
['email_sender' => get_bloginfo('admin_email'), 'email_subject' => 'Enrollment Canceled by User', 'email_message' => 'Your enrollment has been canceled by user.', 'status_action' => 'Enrollment Canceled by User'],
['email_sender' => get_bloginfo('admin_email'), 'email_subject' => 'Under Analysis', 'email_message' => 'Your enrollment is under analysis.', 'status_action' => 'Under Analysis'],
['email_sender' => get_bloginfo('admin_email'), 'email_subject' => 'Approved', 'email_message' => 'Your enrollment has been approved.', 'status_action' => 'Approved'],
['email_sender' => get_bloginfo('admin_email'), 'email_subject' => 'Waiting List', 'email_message' => 'You have been placed on the waiting list.', 'status_action' => 'Waiting List'],
['email_sender' => get_bloginfo('admin_email'), 'email_subject' => 'Not Approved', 'email_message' => 'Your enrollment has not been approved.', 'status_action' => 'Not Approved']
];
foreach ($default_email_messages as $message) {
$wpdb->insert("{$wpdb->prefix}sige_email_messages", $message);
}
// Insert default email messages in sige_attendance_email_messages
$default_attendance_email_messages = [
['email_sender' => get_bloginfo('admin_email'), 'email_subject' => 'Attendance Counted', 'email_message' => 'Your attendance at the event was counted.', 'status_action' => 'yes'],
['email_sender' => get_bloginfo('admin_email'), 'email_subject' => 'Attendance Not Counted', 'email_message' => 'Your attendance at the event was not counted.', 'status_action' => 'no']
];
foreach ($default_attendance_email_messages as $message) {
$wpdb->insert("{$wpdb->prefix}sige_attendance_email_messages", $message);
}
// Ensure wp_usermeta has specified meta_keys
$required_meta_keys = [
'forca',
'full_name',
'posto',
'militar',
'instituição',
'nacionalidade',
'efetivo_dcta',
'cidade',
'estado',
'areaatividadeprincipal',
'setordeatuacao',
'cargofuncao',
'ehativa',
'circulo_hierarquico',
'telefone',
'passaporte',
'pais',
'formacao_academica',
'regiao_do_brasil',
'genero'
];
foreach ($required_meta_keys as $meta_key) {
if (!$wpdb->get_var($wpdb->prepare("SELECT meta_key FROM {$wpdb->prefix}usermeta WHERE meta_key = %s", $meta_key))) {
$wpdb->insert("{$wpdb->prefix}usermeta", ['meta_key' => $meta_key, 'meta_value' => '']);
}
}
// Table to store participants feedback
$table_name = $wpdb->prefix . 'sige_feedback';
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE IF NOT EXISTS $table_name (
id mediumint(9) NOT NULL AUTO_INCREMENT,
participant_name varchar(100),
institution varchar(100),
participant_type varchar(50) NOT NULL,
positive_highlight text,
improvement_aspect text,
suggestions text,
future_interest varchar(3) NOT NULL,
submission_date datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) $charset_collate;";
// Check if the JSON file with saved data exists and restore data if available
$json_file_path = plugin_dir_path(__FILE__) . 'sige_events_data.json';
if (file_exists($json_file_path)) {
$json_data = json_decode(file_get_contents($json_file_path), true);
if ($json_data) {
$tables_to_replace = [
'sige_enrollment_status_style',
'sige_email_messages',
'sige_enrollment_button_style',
'sige_display_options',
'sige_activities',
'sige_enrollments',
'sige_cancellation_record',
'sige_enrollment_attendance_control',
'sige_enrollment_online_attendance_control',
'sige_confidentiality_user_option',
'sige_confidentiality_message',
'sige_attendance_email_messages'
];
// Restore the non-replacement tables first
foreach ($json_data as $table => $records) {
if (!in_array($table, $tables_to_replace)) {
foreach ($records as $record) {
$wpdb->insert("{$wpdb->prefix}$table", $record);
}
}
}
// Restore the replacement tables last
foreach ($tables_to_replace as $table) {
if (isset($json_data[$table])) {
foreach ($json_data[$table] as $record) {
$wpdb->replace("{$wpdb->prefix}$table", $record);
}
}
}
}
}
}
function sige_events_uninstall()
{
global $wpdb;
// Export data to JSON file before dropping tables
$tables = [
'sige_activity_notices',
'sige_display_options',
'sige_enrollment_button_style',
'sige_meta_keys',
'sige_email_messages',
'sige_enrollment_status_style',
'sige_event_names',
'sige_place',
'sige_speakers',
'sige_activities',
'sige_enrollments',
'sige_cancellation_record',
'sige_enrollment_attendance_control',
'sige_enrollment_online_attendance_control',
'sige_confidentiality_message',
'sige_confidentiality_user_option',
'sige_attendance_email_messages'
];
$data = [];
// Save non-replacement tables first
foreach ($tables as $table) {
if (!in_array($table, ['sige_activities', 'sige_enrollments', 'sige_enrollment_attendance_control', 'sige_cancellation_record', 'sige_confidentiality_user_option'])) {
$data[$table] = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}$table", ARRAY_A);
}
}
// Save replacement tables last
foreach (['sige_activities', 'sige_enrollments', 'sige_enrollment_attendance_control', 'sige_cancellation_record', 'sige_confidentiality_user_option'] as $table) {
$data[$table] = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}$table", ARRAY_A);
}
$json_file_path = plugin_dir_path(__FILE__) . 'sige_events_data.json';
file_put_contents($json_file_path, json_encode($data));
// Drop the tables
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_activity_notices");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_display_options");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_enrollment_button_style");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_meta_keys");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_email_messages");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_enrollment_status_style");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_enrollment_attendance_control");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_enrollment_online_attendance_control");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_enrollments");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_cancellation_record");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_activities");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_event_names");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_place");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_speakers");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_confidentiality_message");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_confidentiality_user_option");
$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}sige_attendance_email_messages");
}
function export_sige_events_data()
{
global $wpdb;
$tables = [
'sige_activity_notices',
'sige_apoio',
'sige_display_options',
'sige_enrollment_button_style',
'sige_meta_keys',
'sige_email_messages',
'sige_enrollment_status_style',
'sige_event_names',
'sige_place',
'sige_speakers',
'sige_activities',
'sige_enrollments',
'sige_cancellation_record',
'sige_enrollment_attendance_control',
'sige_enrollment_online_attendance_control',
'sige_confidentiality_message',
'sige_confidentiality_user_option',
'sige_attendance_email_messages'
];
$data = [];
foreach ($tables as $table) {
if (!in_array($table, ['sige_activities', 'sige_enrollments', 'sige_enrollment_attendance_control', 'sige_cancellation_record', 'sige_confidentiality_user_option'])) {
$data[$table] = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}$table", ARRAY_A);
}
}
foreach (['sige_activities', 'sige_enrollments', 'sige_enrollment_attendance_control', 'sige_cancellation_record', 'sige_confidentiality_user_option'] as $table) {
$data[$table] = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}$table", ARRAY_A);
}
$current_datetime = new DateTime();
$formatted_datetime = $current_datetime->format('d_m_Y_H_i_s');
$json_file_name = 'sige_events_data_' . $formatted_datetime . '.json';
$json_file_path = plugin_dir_path(__FILE__) . $json_file_name;
file_put_contents($json_file_path, json_encode($data));
}
//add_action('export_sige_events_data_hook', 'export_sige_events_data');
/*
function schedule_daily_export()
{
if (!wp_next_scheduled('export_sige_events_data_hook')) {
$timestamp = strtotime('04:05:00'); // 1:00 AM today
if ($timestamp < time()) {
$timestamp = strtotime('04:05:00 +1 day'); // 1:00 AM tomorrow
}
wp_schedule_event($timestamp, 'daily', 'export_sige_events_data_hook');
}
}
add_action('wp', 'schedule_daily_export');
*/
#function unschedule_daily_export()
// {
// $timestamp = wp_next_scheduled('export_sige_events_data_hook');
// if ($timestamp) {
// wp_unschedule_event($timestamp, 'export_sige_events_data_hook');
// }
// }
// register_deactivation_hook(__FILE__, 'unschedule_daily_export');
add_action('admin_menu', 'addAdminPageContent');
function addAdminPageContent()
{
add_menu_page('SIGE Events', 'SIGE Events', 'manage_options', 'sige-events', 'SigeEventmenuPage', 'dashicons-welcome-learn-more');
add_submenu_page('sige-events', 'Dashboard', 'Dashboard', 'manage_options', 'dashboard-submenu', 'dashboardSubmenuPage');
add_submenu_page('sige-events', 'Create Activities', 'Create Activities', 'manage_options', 'create-activities-submenu', 'createActivitiesSubmenuPage');
add_submenu_page('sige-events', 'Occasional Emails', 'Occasional Emails', 'manage_options', 'occasional-emails-submenu', 'SendOccasionalEmailsSubmenuPage');
add_submenu_page('sige-events', 'Settings', 'Settings', 'manage_options', 'settings-submenu', 'settingsSubmenuPage');
}
function SigeEventmenuPage()
{
echo '
';
echo '
Welcome to Sige Events
';
// Main Purpose Section
echo '
';
echo '
Main Purpose
';
echo '
Sige Events is a comprehensive plugin designed to facilitate the management of enrollments for short courses and events. It offers a user-friendly interface and a variety of features that help administrators efficiently handle all aspects of event management.
';
echo '
Key Features:
';
echo '
';
echo '
Event Creation and Management: Easily create and manage different types of activities, including presentations, short courses, lectures, workshops, and more.
';
echo '
Enrollment Handling: Manage enrollments, track participant status, and handle approvals and cancellations.
';
echo '
Email Notifications: Set up and customize email notifications to keep participants informed about their enrollment status and event updates.
';
echo '
Data Export: Export activity and enrollment data to CSV for reporting and analysis purposes.
';
echo '
Customizable Display Options: Choose which fields to display on the frontend and customize notices for different types of activities.
';
echo '
';
echo '
This plugin is an ideal solution for institutions, organizations, or individuals who need a reliable and efficient system to manage their educational or informational events.
';
echo '
'; // End Main Purpose Section
// Shortcode Usage Section
echo '
';
echo '
Shortcode Usage
';
echo '
To display the open activities on any page or post, use the following shortcode:
';
echo '[open_activities]';
echo '
To display the enrollment Controls on any page or post, use the following shortcode:
';
echo '[enrollments]';
echo '
To display to Security and Defense Commission the enrollment Controls for SIGE events on any page or post, use the following shortcode:
';
echo '
To manage attendance control on any page or post, use the following shortcode:
';
echo '[frontend_attendance_control]';
echo '
To generate the QR CODE for user purpose, use the following shortcode:
';
echo '[userbadge_qr_code]';
echo '
To generate the dashboard for management purpose, use the following shortcode:
';
echo '[Dashboard]';
echo '
To manage attendance control automatic on any page or post, use the following shortcode
To manage label edition for badges on any page or post, use the following shortcode
';
echo '[generate_attendance_pdf]';
echo '
'; // End Shortcode Usage Section
// Available Functions Section
echo '
';
echo '
Available Functions and Their Purposes
';
echo '
';
echo '
sige_events_install: Function to create the necessary database tables and insert default options when the plugin is activated.
';
echo '
sige_events_uninstall: Function to drop the database tables when the plugin is deactivated.
';
echo '
export_sige_events_data: Function to export the database tables in Json in certain amount of time automatically.
';
echo '
addAdminPageContent: Function to add the admin menu and submenu pages for managing the plugin.
';
echo '
SigeEventmenuPage: Function to display the main content of the SIGE Events submenu page.
';
echo '
dashboardSubmenuPage: Function to display the content of the Dashboard submenu page.
';
echo '
createActivitiesSubmenuPage: Function to display and manage available activities in the Available Activities submenu page.
';
echo '
emailSettingsSubmenuPage: Function to manage email settings in the Email Settings submenu page.
';
echo '
export_activities_to_csv: Function to export activities to a CSV file.
';
echo '
enqueue_datatables_assets_admin: Function to enqueue DataTables assets for enhanced table interactions in the admin area.
';
echo '
frontendDisplayOpenActivities: Shortcode function to display open activities on the frontend. Usage: [open_activities]
';
echo '
frontendEnrollmentControl: Function to display enrollments on the frontend. Usage: [enrollments]
';
echo '
frontendEnrollmentControlSecurityAndDefense: Function To display to Security and Defense Commission the enrollment Controls for ALL events on any page or post. Usage: [enrollmentsSecurityAndDefenseSIGE]
';
echo '
frontendEnrollmentControlSecurityAndDefenseSIGE: Function to display enrollments on the frontend. Usage: [enrollmentsSecurityAndDefenseSIGE]
';
echo '
frontendEnrollmentControlSecurityAndDefenseSERFA: Function to display enrollments on the frontend. Usage: [enrollmentsSecurityAndDefenseSERFA]
';
echo '
frontendAttendanceControl: Function to display attendance control on the frontend. Usage: [frontend_attendance_control]
';
echo '
frontendAttendanceControlAutomatic: Function to display attendance control automatic on the frontend. Usage: [frontend_attendance_control_automatic]
';
echo '
generate_attendance_pdf: Function to generate the user label for badges. Usage: [generate_attendance_pdf]
';
echo '
send_email_notification: Function used to send email to user whenever some enrollment status was changed
';
echo '
displayMainOptions: Function to display the Main Options tab in the Settings submenu page.
';
echo '
displayDashboardOptions: Function to display the Dashboard Options tab in the Settings submenu page.
';
echo '
displayActivityOptions: Function to display and manage activity-related options in the Activity Options tab in the Settings submenu page.
';
echo '
displayEnrollmentOptions: Function to display and manage enrollment-related options in the Enrollment Options tab in the Settings submenu page.
';
echo '
displayEmailOptions: Function to display the Email Options tab in the Settings submenu page.
';
echo '
displayMiscellaneous: Function to display the Miscellaneous tab in the Settings submenu page.
';
echo '
settingsSubmenuPage: Function to display the content of the Settings submenu page and handle tab navigation.
';
echo '
';
echo '
'; // End Available Functions Section
echo '
';
}
// Add this CSS to your plugin to apply the styles
function sige_events_admin_styles()
{
echo '';
}
add_action('admin_head', 'sige_events_admin_styles');
function dashboardSubmenuPage()
{
global $wpdb;
// Fetch data for the first graph (unique users by region)
$query1 = "
SELECT um.meta_value as regiao_do_brasil, COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'regiao_do_brasil'
GROUP BY um.meta_value
";
$results1 = $wpdb->get_results($query1, ARRAY_A);
// Prepare data for the first chart
$regions = ['Norte', 'Nordeste', 'Centro-Oeste', 'Sudeste', 'Sul'];
$data1 = array_fill_keys($regions, 0);
foreach ($results1 as $row) {
if (in_array($row['regiao_do_brasil'], $regions)) {
$data1[$row['regiao_do_brasil']] = (int) $row['count'];
}
}
// Convert data to JSON for Chart.js
$labels1 = json_encode(array_keys($data1));
$counts1 = json_encode(array_values($data1));
// Fetch data for the second graph (formacao_academica by region)
$query2 = "
SELECT
fa.meta_value as formacao_academica,
rb.meta_value as regiao_do_brasil,
COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta fa ON e.user_id = fa.user_id AND fa.meta_key = 'formacao_academica'
LEFT JOIN {$wpdb->prefix}usermeta rb ON e.user_id = rb.user_id AND rb.meta_key = 'regiao_do_brasil'
GROUP BY fa.meta_value, rb.meta_value
";
$results2 = $wpdb->get_results($query2, ARRAY_A);
// Prepare data for the second chart
$formacoes = ['Aluno de Graduação', 'Aluno de Pós-Graduação', 'Professor da Educação Básica', 'Professor/Pesquisador', 'Outros'];
$regions = ['Norte', 'Nordeste', 'Centro-Oeste', 'Sudeste', 'Sul'];
$data2 = [];
foreach ($formacoes as $formacao) {
$data2[$formacao] = array_fill_keys($regions, 0);
}
foreach ($results2 as $row) {
if (in_array($row['formacao_academica'], $formacoes)) {
$region = $row['regiao_do_brasil'];
if (array_key_exists($region, $data2[$row['formacao_academica']])) {
$data2[$row['formacao_academica']][$region] += (int) $row['count'];
}
}
}
// Convert data to JSON for Chart.js
$labels2 = json_encode(array_keys($data2['Aluno de Graduação']));
$datasets2 = [];
foreach ($data2 as $formacao => $counts) {
$datasets2[] = [
'label' => $formacao,
'data' => array_values($counts),
'backgroundColor' => 'rgba(' . rand(0, 255) . ', ' . rand(0, 255) . ', ' . rand(0, 255) . ', 0.2)',
'borderColor' => 'rgba(' . rand(0, 255) . ', ' . rand(0, 255) . ', ' . rand(0, 255) . ', 1)',
'borderWidth' => 1
];
}
$datasets2 = json_encode($datasets2);
// Fetch data for the third graph (In-person vs Online Enrollment)
$query3_in_person = "
SELECT DISTINCT e.user_id
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
WHERE a.modality IN ('In-person', 'Hybrid')
AND e.status NOT IN ('Not Approved', 'Enrollment Canceled by User','Waiting List','Under Analysis')
";
$query3_online = "
SELECT COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
WHERE e.user_id NOT IN (
SELECT DISTINCT e.user_id
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
WHERE a.modality IN ('In-person', 'Hybrid')
AND e.status NOT IN ('Not Approved', 'Enrollment Canceled by User','Waiting List','Under Analysis')
)
AND e.status NOT IN ('Not Approved', 'Enrollment Canceled by User', 'Waiting List','Under Analysis')
";
$in_person_users = $wpdb->get_col($query3_in_person);
$online_count = $wpdb->get_var($query3_online);
// Prepare data for the third chart
$in_person_count = count($in_person_users);
$labels3 = json_encode(['In-person Enrollment', 'Online Enrollment']);
$counts3 = json_encode([$in_person_count, (int) $online_count]);
// Fetch data for the fourth graph (circulo_hierárquico distribution for In-person users)
$query4 = "
SELECT um.meta_value as circulo_hierarquico, COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'circulo_hierárquico'
WHERE e.user_id IN (" . implode(',', array_map('intval', $in_person_users)) . ")
GROUP BY um.meta_value
";
$results4 = $wpdb->get_results($query4, ARRAY_A);
// Prepare data for the fourth chart
$circulos = ['Praça', 'Graduado', 'Praça Especial', 'Oficial Subalterno', 'Oficial Intermediário', 'Oficial Superior', 'Oficial General'];
$data4 = array_fill_keys($circulos, 0);
foreach ($results4 as $row) {
if (in_array($row['circulo_hierarquico'], $circulos)) {
$data4[$row['circulo_hierarquico']] = (int) $row['count'];
}
}
// Convert data to JSON for Chart.js
$labels4 = json_encode(array_keys($data4));
$counts4 = json_encode(array_values($data4));
// Fetch data for the fifth graph (militar In-person vs Online Enrollment)
$query5_in_person = "
SELECT DISTINCT e.user_id
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'militar'
WHERE a.modality IN ('In-person', 'Hybrid')
AND e.status NOT IN ('Not Approved', 'Enrollment Canceled by User','Waiting List','Under Analysis')
AND um.meta_value = 'Sim'
";
$query5_online = "
SELECT COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'militar'
WHERE e.user_id NOT IN (
SELECT DISTINCT e.user_id
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'militar'
WHERE a.modality IN ('In-person', 'Hybrid')
AND e.status NOT IN ('Not Approved', 'Enrollment Canceled by User','Waiting List','Under Analysis')
AND um.meta_value = 'Sim'
)
AND e.status NOT IN ('Not Approved', 'Enrollment Canceled by User','Waiting List','Under Analysis')
AND um.meta_value = 'Sim'
";
$in_person_users_militar = $wpdb->get_col($query5_in_person);
$online_count_militar = $wpdb->get_var($query5_online);
// Prepare data for the fifth chart
$in_person_count_militar = count($in_person_users_militar);
$labels5 = json_encode(['In-person Enrollment Militar', 'Online Enrollment Militar']);
$counts5 = json_encode([$in_person_count_militar, (int) $online_count_militar]);
// Fetch data for the sixth graph (non-militar In-person vs Online Enrollment)
$query6_in_person = "
SELECT DISTINCT e.user_id
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'militar'
WHERE a.modality IN ('In-person', 'Hybrid')
AND e.status NOT IN ('Not Approved', 'Enrollment Canceled by User','Waiting List','Under Analysis')
AND um.meta_value = 'Não'
";
$query6_online = "
SELECT COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'militar'
WHERE e.user_id NOT IN (
SELECT DISTINCT e.user_id
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'militar'
WHERE a.modality IN ('In-person', 'Hybrid')
AND e.status NOT IN ('Not Approved', 'Enrollment Canceled by User','Waiting List','Under Analysis')
AND um.meta_value = 'Não'
)
AND e.status NOT IN ('Not Approved', 'Enrollment Canceled by User','Waiting List','Under Analysis')
AND um.meta_value = 'Não'
";
$in_person_users_non_militar = $wpdb->get_col($query6_in_person);
$online_count_non_militar = $wpdb->get_var($query6_online);
// Prepare data for the sixth chart
$in_person_count_non_militar = count($in_person_users_non_militar);
$labels6 = json_encode(['In-person Enrollment Non-Militar', 'Online Enrollment Non-Militar']);
$counts6 = json_encode([$in_person_count_non_militar, (int) $online_count_non_militar]);
// Fetch data for the seventh graph (Brasileiros vs Estrangeiro by distinct users)
$query7 = "
SELECT um.meta_value as nacionalidade, COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'nacionalidade'
WHERE e.status NOT IN ('Not Approved', 'Enrollment Canceled by User')
GROUP BY um.meta_value
";
$results7 = $wpdb->get_results($query7, ARRAY_A);
// Prepare data for the seventh chart
$nationalities = ['Brasileiro', 'Estrangeiro'];
$data7 = array_fill_keys($nationalities, 0);
foreach ($results7 as $row) {
if (in_array($row['nacionalidade'], $nationalities)) {
$data7[$row['nacionalidade']] = (int) $row['count'];
}
}
// Convert data to JSON for Chart.js
$labels7 = json_encode(array_keys($data7));
$counts7 = json_encode(array_values($data7));
// Fetch data for the eighth graph (Gender distribution by distinct users)
$query8 = "
SELECT um.meta_value as genero, COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'genero'
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
WHERE a.modality IN ('In-person', 'Hybrid','Online')
AND e.status NOT IN ('Not Approved', 'Enrollment Canceled by User')
GROUP BY um.meta_value
";
$results8 = $wpdb->get_results($query8, ARRAY_A);
// Prepare data for the eighth chart
$genders = ['Feminino', 'Masculino'];
$data8 = array_fill_keys($genders, 0);
foreach ($results8 as $row) {
if (in_array($row['genero'], $genders)) {
$data8[$row['genero']] = (int) $row['count'];
}
}
// Convert data to JSON for Chart.js
$labels8 = json_encode(array_keys($data8));
$counts8 = json_encode(array_values($data8));
// Fetch data for the ninth graph (Gender distribution of military users by distinct users)
$query9 = "
SELECT um1.meta_value as genero, COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um1 ON e.user_id = um1.user_id AND um1.meta_key = 'genero'
LEFT JOIN {$wpdb->prefix}usermeta um2 ON e.user_id = um2.user_id AND um2.meta_key = 'militar'
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
WHERE a.modality IN ('In-person', 'Hybrid','Online')
AND e.status NOT IN ('Not Approved', 'Enrollment Canceled by User')
AND um2.meta_value = 'Sim'
GROUP BY um1.meta_value
";
$results9 = $wpdb->get_results($query9, ARRAY_A);
// Prepare data for the ninth chart
$genders_military = ['Feminino', 'Masculino'];
$data9 = array_fill_keys($genders_military, 0);
foreach ($results9 as $row) {
if (in_array($row['genero'], $genders_military)) {
$data9[$row['genero']] = (int) $row['count'];
}
}
// Convert data to JSON for Chart.js
$labels9 = json_encode(array_keys($data9));
$counts9 = json_encode(array_values($data9));
// Tenth chart
$query10 = "
SELECT DISTINCT e.user_id,
SUM(CASE WHEN um1.meta_value = 'Sim' AND um2.meta_value = 'Sim' THEN 1 ELSE 0 END) as military_dcta,
SUM(CASE WHEN um1.meta_value = 'Sim' AND um2.meta_value = 'Não' THEN 1 ELSE 0 END) as military_outside,
SUM(CASE WHEN um1.meta_value = 'Não' THEN 1 ELSE 0 END) as civilians
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}usermeta um1 ON e.user_id = um1.user_id AND um1.meta_key = 'militar'
LEFT JOIN {$wpdb->prefix}usermeta um2 ON e.user_id = um2.user_id AND um2.meta_key = 'efetivo_dcta'
WHERE a.type = 'Main Event'
AND a.modality IN ('Hybrid', 'In-person')
AND e.status IN ('Approved', 'Waiting List', 'Under Analysis')
GROUP BY e.user_id
";
$results10 = $wpdb->get_results($query10, ARRAY_A);
// Prepare data for the tenth chart by processing unique users
$military_dcta_count = 0;
$military_outside_count = 0;
$civilian_count = 0;
$processed_user_ids = [];
foreach ($results10 as $row) {
// Count only unique user_ids in each category
if (!in_array($row['user_id'], $processed_user_ids)) {
if ($row['military_dcta'] > 0) {
$military_dcta_count++;
} elseif ($row['military_outside'] > 0) {
$military_outside_count++;
} elseif ($row['civilians'] > 0) {
$civilian_count++;
}
$processed_user_ids[] = $row['user_id']; // Track processed user_ids
}
}
// Calculate the total in-person count
$total_in_person_count = $military_dcta_count + $military_outside_count + $civilian_count;
// Labels and data for the chart
$labels10 = json_encode(['Military DCTA', 'Military Outside', 'Civilian', 'Total In Person']);
$counts10 = json_encode([$military_dcta_count, $military_outside_count, $civilian_count, $total_in_person_count]);
// Eleventh chart: Total In-person Enrollment attendance of Main Event
$query11 = "
SELECT DISTINCT e.user_id, ac.attended
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_enrollment_attendance_control ac ON e.id = ac.enrollment_id
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
WHERE a.type = 'Main Event'
AND a.modality IN ('Hybrid', 'In-person')
AND e.status = 'Approved'
";
$results11 = $wpdb->get_results($query11, ARRAY_A);
$attended_yes = [];
$attended_no_or_empty = [];
foreach ($results11 as $row) {
$user_id = $row['user_id'];
if ($row['attended'] === 'yes' && !in_array($user_id, $attended_yes)) {
$attended_yes[] = $user_id;
} elseif (($row['attended'] === 'no' || $row['attended'] === '' || is_null($row['attended'])) && !in_array($user_id, $attended_no_or_empty)) {
$attended_no_or_empty[] = $user_id;
}
}
$attended_yes_count = count($attended_yes);
$attended_no_or_empty_count = count($attended_no_or_empty);
$labels11 = json_encode(['Attended (Yes)', 'Did Not Attend (No or Empty)']);
$counts11 = json_encode([$attended_yes_count, $attended_no_or_empty_count]);
// Twelfth chart: Total In-person Enrollment attendance of Short-Courses (unique IDs, filtering for 'Approved')
$query12 = "
SELECT DISTINCT e.user_id, ac.attended, a.name as course_name
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_enrollment_attendance_control ac ON e.id = ac.enrollment_id
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
WHERE a.type = 'Short-course'
AND a.modality IN ('Hybrid', 'In-person')
AND e.status = 'Approved'
";
// Execute the query
$results12 = $wpdb->get_results($query12, ARRAY_A);
// Initialize arrays to store unique users for each short course
$short_courses = [];
$attended_yes = [];
$attended_no_or_empty = [];
// Process the results to group users by course and attendance status
foreach ($results12 as $row) {
$course_name = $row['course_name'];
$user_id = $row['user_id'];
// Initialize the course arrays if not already present
if (!isset($short_courses[$course_name])) {
$short_courses[$course_name] = ['attended_yes' => [], 'attended_no_or_empty' => []];
}
// If the user attended 'yes', add to attended_yes if not already present
if ($row['attended'] === 'yes' && !in_array($user_id, $short_courses[$course_name]['attended_yes'])) {
$short_courses[$course_name]['attended_yes'][] = $user_id;
}
// If the user attended 'no', '', or is not in the attendance table, add to attended_no_or_empty if not already present
elseif (($row['attended'] === 'no' || $row['attended'] === '' || is_null($row['attended'])) && !in_array($user_id, $short_courses[$course_name]['attended_no_or_empty'])) {
$short_courses[$course_name]['attended_no_or_empty'][] = $user_id;
}
}
// Prepare the data for the twelfth chart
$labels12 = [];
$attended_yes_data = [];
$attended_no_or_empty_data = [];
$course_legend = []; // For legend to map short-course names
$counter = 1;
foreach ($short_courses as $course_name => $data) {
// Create shortened course labels (Short-course 1, Short-course 2, ...)
$course_label = "Short-course " . $counter++;
$course_legend[$course_label] = $course_name; // Add to legend
$labels12[] = $course_label; // Single label for each course
$attended_yes_data[] = count($data['attended_yes']); // Count of attended 'Yes'
$attended_no_or_empty_data[] = count($data['attended_no_or_empty']); // Count of attended 'No' or empty
}
// Convert the data to JSON format for the chart
$labels12 = json_encode($labels12);
$attended_yes_data = json_encode($attended_yes_data);
$attended_no_or_empty_data = json_encode($attended_no_or_empty_data);
echo '
Dashboard
';
echo '
';
// First chart
echo '
';
// Second chart
echo '
';
// Third chart
echo '
';
// Fourth chart
echo '
';
// Fifth chart
echo '
';
// Sixth chart
echo '
';
// Seventh chart (Brasileiros vs Estrangeiro)
echo '
';
// Eigth chart (Homens vs Mulheres total)
echo '
';
// Nineth chart (Homens vs Mulheres militares)
echo '
';
// Tenth chart
echo '
';
// Eleventh chart
echo '
';
// Twelfth chart
echo '
';
echo '
';
// Fetch summary counts for the first table
$summary_counts1 = $wpdb->get_results("
SELECT
a.name as activity_name,
a.slots,
COUNT(e.id) as total_requests,
SUM(CASE WHEN e.status = 'Enrollment Canceled by User' THEN 1 ELSE 0 END) as canceled_by_user,
SUM(CASE WHEN e.status = 'Waiting List' THEN 1 ELSE 0 END) as waiting_list,
SUM(CASE WHEN e.status = 'Approved' THEN 1 ELSE 0 END) as approved,
SUM(CASE WHEN e.status = 'Not Approved' THEN 1 ELSE 0 END) as not_approved,
SUM(CASE WHEN e.status = 'Under Analysis' THEN 1 ELSE 0 END) as under_analysis,
a.slots - SUM(CASE WHEN e.status = 'Approved' THEN 1 ELSE 0 END) as available_slots,
SUM(CASE WHEN um1.meta_value = 'Sim' AND um2.meta_value = 'Sim' THEN 1 ELSE 0 END) as military_dcta,
SUM(CASE WHEN um1.meta_value = 'Sim' AND um2.meta_value = 'Não' THEN 1 ELSE 0 END) as military_outside,
SUM(CASE WHEN um1.meta_value = 'Não' THEN 1 ELSE 0 END) as civilians
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}usermeta um1 ON e.user_id = um1.user_id AND um1.meta_key = 'militar'
LEFT JOIN {$wpdb->prefix}usermeta um2 ON e.user_id = um2.user_id AND um2.meta_key = 'efetivo_dcta'
WHERE a.type = 'Main Event' AND a.modality = 'Hybrid'
GROUP BY a.name, a.slots
");
ob_start();
// Display first summary counts table
echo '
Hybrid Enrollment Summary
';
echo '
';
echo '
Activity Name
Enrollment Canceled by User
Waiting List
Approved
Not Approved
Under Analysis
Military (DCTA)
Military (Outside)
Civilians
Total Enrollment Requests
Enrollment Availables
Slots
';
if ($summary_counts1) {
foreach ($summary_counts1 as $summary) {
echo '
' . esc_html($summary->activity_name) . '
' . esc_html($summary->canceled_by_user) . '
' . esc_html($summary->waiting_list) . '
' . esc_html($summary->approved) . '
' . esc_html($summary->not_approved) . '
' . esc_html($summary->under_analysis) . '
' . esc_html($summary->military_dcta) . '
' . esc_html($summary->military_outside) . '
' . esc_html($summary->civilians) . '
' . esc_html($summary->total_requests) . '
' . esc_html($summary->available_slots) . '
' . esc_html($summary->slots) . '
';
}
} else {
echo '
No data found
';
}
echo '
';
echo '
';
// Fetch summary counts for the second table
$summary_counts2 = $wpdb->get_results("
SELECT
a.name as activity_name,
a.slots,
COUNT(e.id) as total_requests,
SUM(CASE WHEN e.status = 'Enrollment Canceled by User' THEN 1 ELSE 0 END) as canceled_by_user,
SUM(CASE WHEN e.status = 'Waiting List' THEN 1 ELSE 0 END) as waiting_list,
SUM(CASE WHEN e.status = 'Approved' THEN 1 ELSE 0 END) as approved,
SUM(CASE WHEN e.status = 'Not Approved' THEN 1 ELSE 0 END) as not_approved,
SUM(CASE WHEN e.status = 'Under Analysis' THEN 1 ELSE 0 END) as under_analysis,
a.slots - SUM(CASE WHEN e.status = 'Approved' THEN 1 ELSE 0 END) as available_slots,
SUM(CASE WHEN um1.meta_value = 'Sim' AND um2.meta_value = 'Sim' THEN 1 ELSE 0 END) as military_dcta,
SUM(CASE WHEN um1.meta_value = 'Sim' AND um2.meta_value = 'Não' THEN 1 ELSE 0 END) as military_outside,
SUM(CASE WHEN um1.meta_value = 'Não' THEN 1 ELSE 0 END) as civilians
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}usermeta um1 ON e.user_id = um1.user_id AND um1.meta_key = 'militar'
LEFT JOIN {$wpdb->prefix}usermeta um2 ON e.user_id = um2.user_id AND um2.meta_key = 'efetivo_dcta'
WHERE a.type = 'Main Event' AND a.modality = 'In-person'
GROUP BY a.name, a.slots
");
// Display second summary counts table
echo '
In-person Enrollment Summary
';
echo '
';
echo '
Activity Name
Enrollment Canceled by User
Waiting List
Approved
Not Approved
Under Analysis
Military (DCTA)
Military (Outside)
Civilians
Total Enrollment Requests
Enrollment Availables
Slots
';
if ($summary_counts2) {
foreach ($summary_counts2 as $summary) {
echo '
';
// Add the JavaScript for showing/hiding slots based on attendance control
echo '
';
$activities = $wpdb->get_results("
SELECT a.*, p.place, s.full_name as speaker, e.name as event_name
FROM {$wpdb->prefix}sige_activities a
LEFT JOIN {$wpdb->prefix}sige_place p ON a.place_id = p.id
LEFT JOIN {$wpdb->prefix}sige_speakers s ON a.speaker_id = s.id
LEFT JOIN {$wpdb->prefix}sige_event_names e ON a.event_name_id = e.id
");
if ($activities) {
$types = ['Main Event', 'Presentation', 'Short-course', 'Lecture', 'Workshop', 'Others'];
$modalities = ['Online', 'In-person', 'Hybrid', 'Others'];
$statuses = ['Open', 'Closed', 'Not applicable'];
if ($activities) {
echo '';
}
echo '
';
echo '';
}
}
function frontendDashboard()
{
if (!is_user_logged_in()) {
return '
You need to be logged in to view the registrations that have been made.
';
}
if (!current_user_can('administrator') && !current_user_can('contributor')) {
return '
You do not have the required permissions to handle the attendance control.
';
}
global $wpdb;
// ---------- 1) SELECTOR DE ATIVIDADE ----------
// Lista de atividades com rótulo: "name (YYYY) - modality"
$activities = $wpdb->get_results("
SELECT id, name, modality, start_date, type
FROM {$wpdb->prefix}sige_activities
ORDER BY COALESCE(start_date, '1970-01-01') DESC, id DESC
", ARRAY_A);
$selected_activity_id = isset($_GET['activity_id']) ? intval($_GET['activity_id']) : 0;
$selected_activity = null;
$selected_activity_modality = null;
$selected_activity_type = null;
if ($selected_activity_id > 0) {
$selected_activity = $wpdb->get_row(
$wpdb->prepare("
SELECT id, name, modality, start_date, type
FROM {$wpdb->prefix}sige_activities
WHERE id = %d
", $selected_activity_id),
ARRAY_A
);
if ($selected_activity) {
$selected_activity_modality = $selected_activity['modality'];
$selected_activity_type = $selected_activity['type'];
} else {
$selected_activity_id = 0; // id inválido -> zera seleção
}
}
ob_start();
echo '
Dashboard
';
// Formulário (GET) com auto-submit no change
echo '';
if ($selected_activity_id === 0) {
echo '
Selecione uma atividade para visualizar os gráficos e tabelas.
';
echo '
'; // .wrap
return ob_get_clean();
}
// Aviso solicitado quando a atividade é presencial
if ($selected_activity_modality === 'In-person') {
echo '
Aviso: Dados indisponíveis para modalidade online. Motivo: atividade presencial.
';
}
// ---------- 2) VARIÁVEIS AUXILIARES ----------
// Cláusulas de filtro por atividade (reutilizáveis)
$WHERE_ACTIVITY = $wpdb->prepare(" e.activity_id = %d ", $selected_activity_id);
// Status usados no seu código original (mantidos)
$STATUS_EXCLUDE_Q3 = "('Not Approved','Enrollment Canceled by User','Waiting List','Under Analysis')";
$STATUS_EXCLUDE_Q7_Q8_Q9 = "('Not Approved','Enrollment Canceled by User')";
// Para dividir "presencial vs online" dentro da MESMA atividade (sem olhar outras)
// Conta usuários distintos APENAS desta atividade (Approved, etc.)
$total_users_this_activity_approved = intval($wpdb->get_var("
SELECT COUNT(DISTINCT e.user_id)
FROM {$wpdb->prefix}sige_enrollments e
WHERE {$WHERE_ACTIVITY}
AND e.status NOT IN {$STATUS_EXCLUDE_Q3}
"));
// ---------- 3) Q1 – Região (AGORA filtrando pela atividade) ----------
$query1 = "
SELECT um.meta_value as regiao_do_brasil, COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um
ON e.user_id = um.user_id AND um.meta_key = 'regiao_do_brasil'
WHERE {$WHERE_ACTIVITY}
GROUP BY um.meta_value
";
$results1 = $wpdb->get_results($query1, ARRAY_A);
$regions = ['Norte', 'Nordeste', 'Centro-Oeste', 'Sudeste', 'Sul'];
$data1 = array_fill_keys($regions, 0);
foreach ($results1 as $row) {
if (in_array($row['regiao_do_brasil'], $regions, true)) {
$data1[$row['regiao_do_brasil']] = (int)$row['count'];
}
}
$labels1 = json_encode(array_keys($data1));
$counts1 = json_encode(array_values($data1));
// ---------- 4) Q2 – Formação x Região (AGORA filtrando pela atividade) ----------
$query2 = "
SELECT
fa.meta_value as formacao_academica,
rb.meta_value as regiao_do_brasil,
COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta fa ON e.user_id = fa.user_id AND fa.meta_key = 'formacao_academica'
LEFT JOIN {$wpdb->prefix}usermeta rb ON e.user_id = rb.user_id AND rb.meta_key = 'regiao_do_brasil'
WHERE {$WHERE_ACTIVITY}
GROUP BY fa.meta_value, rb.meta_value
";
$results2 = $wpdb->get_results($query2, ARRAY_A);
$formacoes = ['Aluno de Graduação', 'Aluno de Pós-Graduação', 'Professor da Educação Básica', 'Professor/Pesquisador', 'Outros'];
$regions2 = ['Norte', 'Nordeste', 'Centro-Oeste', 'Sudeste', 'Sul'];
$data2 = [];
foreach ($formacoes as $f) {
$data2[$f] = array_fill_keys($regions2, 0);
}
foreach ($results2 as $row) {
if (in_array($row['formacao_academica'], $formacoes, true)) {
$region = $row['regiao_do_brasil'];
if (array_key_exists($region, $data2[$row['formacao_academica']])) {
$data2[$row['formacao_academica']][$region] += (int)$row['count'];
}
}
}
$labels2 = json_encode(array_keys($data2['Aluno de Graduação']));
$datasets2_arr = [];
foreach ($data2 as $formacao => $counts) {
$datasets2_arr[] = [
'label' => $formacao,
'data' => array_values($counts),
// cores aleatórias como no seu código
'backgroundColor' => 'rgba(' . rand(0, 255) . ', ' . rand(0, 255) . ', ' . rand(0, 255) . ', 0.2)',
'borderColor' => 'rgba(' . rand(0, 255) . ', ' . rand(0, 255) . ', ' . rand(0, 255) . ', 1)',
'borderWidth' => 1
];
}
$datasets2 = json_encode($datasets2_arr);
// ---------- 5) Q3 – Presencial vs Online (apenas desta atividade) ----------
// Base: aprovados (ou equivalentes) desta atividade
$in_person_modalities = ['In-person', 'Hybrid'];
$in_person_count = in_array($selected_activity_modality, $in_person_modalities, true) ? $total_users_this_activity_approved : 0;
$online_count = ($selected_activity_modality === 'Online') ? $total_users_this_activity_approved : 0;
$labels3 = json_encode(['In-person Enrollment', 'Online Enrollment']);
$counts3 = json_encode([$in_person_count, $online_count]);
// ---------- 6) Q4 – Círculo hierárquico (apenas usuários desta atividade; mantém seus status) ----------
$query4 = "
SELECT um.meta_value as circulo_hierarquico, COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'circulo_hierárquico'
WHERE {$WHERE_ACTIVITY}
AND e.status NOT IN {$STATUS_EXCLUDE_Q3}
GROUP BY um.meta_value
";
$results4 = $wpdb->get_results($query4, ARRAY_A);
$circulos = ['Praça', 'Graduado', 'Praça Especial', 'Oficial Subalterno', 'Oficial Intermediário', 'Oficial Superior', 'Oficial General'];
$data4 = array_fill_keys($circulos, 0);
foreach ($results4 as $row) {
if (in_array($row['circulo_hierarquico'], $circulos, true)) {
$data4[$row['circulo_hierarquico']] = (int)$row['count'];
}
}
$labels4 = json_encode(array_keys($data4));
$counts4 = json_encode(array_values($data4));
// ---------- 7) Q5 – Militar: Presencial vs Online (desta atividade) ----------
$in_person_users_militar = intval($wpdb->get_var("
SELECT COUNT(DISTINCT e.user_id)
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key='militar'
WHERE {$WHERE_ACTIVITY}
AND e.status NOT IN {$STATUS_EXCLUDE_Q3}
AND um.meta_value = 'Sim'
"));
// Distribuição por modalidade da atividade selecionada
$in_person_count_militar = in_array($selected_activity_modality, $in_person_modalities, true) ? $in_person_users_militar : 0;
$online_count_militar = ($selected_activity_modality === 'Online') ? $in_person_users_militar : 0;
$labels5 = json_encode(['In-person Enrollment Militar', 'Online Enrollment Militar']);
$counts5 = json_encode([$in_person_count_militar, $online_count_militar]);
// ---------- 8) Q6 – Não-militar: Presencial vs Online (desta atividade) ----------
$in_person_users_non_militar = intval($wpdb->get_var("
SELECT COUNT(DISTINCT e.user_id)
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key='militar'
WHERE {$WHERE_ACTIVITY}
AND e.status NOT IN {$STATUS_EXCLUDE_Q3}
AND um.meta_value = 'Não'
"));
$in_person_count_non_militar = in_array($selected_activity_modality, $in_person_modalities, true) ? $in_person_users_non_militar : 0;
$online_count_non_militar = ($selected_activity_modality === 'Online') ? $in_person_users_non_militar : 0;
$labels6 = json_encode(['In-person Enrollment Non-Militar', 'Online Enrollment Non-Militar']);
$counts6 = json_encode([$in_person_count_non_militar, $online_count_non_militar]);
// ---------- 9) Q7 – Nacionalidade (desta atividade) ----------
$query7 = "
SELECT um.meta_value as nacionalidade, COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'nacionalidade'
WHERE {$WHERE_ACTIVITY}
AND e.status NOT IN {$STATUS_EXCLUDE_Q7_Q8_Q9}
GROUP BY um.meta_value
";
$results7 = $wpdb->get_results($query7, ARRAY_A);
$nationalities = ['Brasileiro', 'Estrangeiro'];
$data7 = array_fill_keys($nationalities, 0);
foreach ($results7 as $row) {
if (in_array($row['nacionalidade'], $nationalities, true)) {
$data7[$row['nacionalidade']] = (int)$row['count'];
}
}
$labels7 = json_encode(array_keys($data7));
$counts7 = json_encode(array_values($data7));
// ---------- 10) Q8 – Gênero (desta atividade) ----------
$query8 = "
SELECT um.meta_value as genero, COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'genero'
WHERE {$WHERE_ACTIVITY}
AND e.status NOT IN {$STATUS_EXCLUDE_Q7_Q8_Q9}
GROUP BY um.meta_value
";
$results8 = $wpdb->get_results($query8, ARRAY_A);
$genders = ['Feminino', 'Masculino'];
$data8 = array_fill_keys($genders, 0);
foreach ($results8 as $row) {
if (in_array($row['genero'], $genders, true)) {
$data8[$row['genero']] = (int)$row['count'];
}
}
$labels8 = json_encode(array_keys($data8));
$counts8 = json_encode(array_values($data8));
// ---------- 11) Q9 – Militar por gênero (desta atividade) ----------
$query9 = "
SELECT um1.meta_value as genero, COUNT(DISTINCT e.user_id) as count
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um1 ON e.user_id = um1.user_id AND um1.meta_key = 'genero'
LEFT JOIN {$wpdb->prefix}usermeta um2 ON e.user_id = um2.user_id AND um2.meta_key = 'militar'
WHERE {$WHERE_ACTIVITY}
AND e.status NOT IN {$STATUS_EXCLUDE_Q7_Q8_Q9}
AND um2.meta_value = 'Sim'
GROUP BY um1.meta_value
";
$results9 = $wpdb->get_results($query9, ARRAY_A);
$genders_military = ['Feminino', 'Masculino'];
$data9 = array_fill_keys($genders_military, 0);
foreach ($results9 as $row) {
if (in_array($row['genero'], $genders_military, true)) {
$data9[$row['genero']] = (int)$row['count'];
}
}
$labels9 = json_encode(array_keys($data9));
$counts9 = json_encode(array_values($data9));
// ---------- 12) Q10 – DCTA / fora / civil + total (desta atividade; presenciais) ----------
$results10 = $wpdb->get_results("
SELECT DISTINCT e.user_id,
SUM(CASE WHEN um1.meta_value = 'Sim' AND um2.meta_value = 'Sim' THEN 1 ELSE 0 END) as military_dcta,
SUM(CASE WHEN um1.meta_value = 'Sim' AND um2.meta_value = 'Não' THEN 1 ELSE 0 END) as military_outside,
SUM(CASE WHEN um1.meta_value = 'Não' THEN 1 ELSE 0 END) as civilians
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}usermeta um1 ON e.user_id = um1.user_id AND um1.meta_key = 'militar'
LEFT JOIN {$wpdb->prefix}usermeta um2 ON e.user_id = um2.user_id AND um2.meta_key = 'efetivo_dcta'
WHERE {$WHERE_ACTIVITY}
AND e.status IN ('Approved','Waiting List','Under Analysis')
GROUP BY e.user_id
", ARRAY_A);
$military_dcta_count = 0;
$military_outside_count = 0;
$civilian_count = 0;
foreach ($results10 as $row) {
if ($row['military_dcta'] > 0) $military_dcta_count++;
elseif ($row['military_outside'] > 0) $military_outside_count++;
elseif ($row['civilians'] > 0) $civilian_count++;
}
$total_in_person_count = $military_dcta_count + $military_outside_count + $civilian_count;
$labels10 = json_encode(['Military DCTA', 'Military Outside', 'Civilian', 'Total In Person']);
$counts10 = json_encode([$military_dcta_count, $military_outside_count, $civilian_count, $total_in_person_count]);
// ---------- 13) Q11 – Presença Main Event (somente se a atividade selecionada for Main Event) ----------
$labels11 = json_encode(['Attended (Yes)', 'Did Not Attend (No or Empty)']);
$counts11 = json_encode([0, 0]);
$show_q11 = ($selected_activity_type === 'Main Event' && in_array($selected_activity_modality, $in_person_modalities, true));
if ($show_q11) {
$results11 = $wpdb->get_results($wpdb->prepare("
SELECT DISTINCT e.user_id, ac.attended
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_enrollment_attendance_control ac ON e.id = ac.enrollment_id
WHERE e.activity_id = %d
AND e.status = 'Approved'
", $selected_activity_id), ARRAY_A);
$attended_yes = [];
$attended_no_or_empty = [];
foreach ($results11 as $row) {
$uid = $row['user_id'];
if ($row['attended'] === 'yes') {
if (!in_array($uid, $attended_yes, true)) $attended_yes[] = $uid;
} else { // 'no' / '' / NULL
if (!in_array($uid, $attended_no_or_empty, true)) $attended_no_or_empty[] = $uid;
}
}
$labels11 = json_encode(['Attended (Yes)', 'Did Not Attend (No or Empty)']);
$counts11 = json_encode([count($attended_yes), count($attended_no_or_empty)]);
}
// ---------- 14) Q12 – Presença Short-courses (somente se a atividade selecionada for Short-course) ----------
$labels12 = json_encode([]);
$attended_yes_data = json_encode([]);
$attended_no_or_empty_data = json_encode([]);
$course_legend = [];
$show_q12 = ($selected_activity_type === 'Short-course' && in_array($selected_activity_modality, $in_person_modalities, true));
if ($show_q12) {
$results12 = $wpdb->get_results($wpdb->prepare("
SELECT DISTINCT e.user_id, ac.attended, a.name as course_name
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_enrollment_attendance_control ac ON e.id = ac.enrollment_id
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
WHERE e.activity_id = %d
AND e.status = 'Approved'
", $selected_activity_id), ARRAY_A);
$short_courses = []; // para compatibilidade com seu layout original
foreach ($results12 as $row) {
$course = $row['course_name'];
$uid = $row['user_id'];
if (!isset($short_courses[$course])) {
$short_courses[$course] = ['attended_yes' => [], 'attended_no_or_empty' => []];
}
if ($row['attended'] === 'yes') {
if (!in_array($uid, $short_courses[$course]['attended_yes'], true))
$short_courses[$course]['attended_yes'][] = $uid;
} else {
if (!in_array($uid, $short_courses[$course]['attended_no_or_empty'], true))
$short_courses[$course]['attended_no_or_empty'][] = $uid;
}
}
$labels12_arr = [];
$yes_arr = [];
$no_arr = [];
$legend = [];
$i = 1;
foreach ($short_courses as $course_name => $data) {
$label = "Short-course " . $i++;
$labels12_arr[] = $label;
$yes_arr[] = count($data['attended_yes']);
$no_arr[] = count($data['attended_no_or_empty']);
$legend[$label] = $course_name;
}
$labels12 = json_encode($labels12_arr);
$attended_yes_data = json_encode($yes_arr);
$attended_no_or_empty_data = json_encode($no_arr);
$course_legend = $legend;
}
// ---------- 15) GRID + CHARTS (seu HTML original, intacto) ----------
echo '
';
echo '
';
echo '
';
echo '
';
echo '
';
echo '
';
echo '
';
echo '
';
echo '
';
echo '
';
echo '
';
if ($show_q11) {
echo '
';
}
if ($show_q12) {
echo '
';
}
echo '
'; // grid
// ---------- 16) TABELAS (filtradas pela atividade selecionada) ----------
// Híbrido Main Event (mostrará "No data found" se a atividade escolhida não for híbrida/main event)
$summary_counts1 = $wpdb->get_results($wpdb->prepare("
SELECT
a.name as activity_name,
a.slots,
COUNT(e.id) as total_requests,
SUM(CASE WHEN e.status = 'Enrollment Canceled by User' THEN 1 ELSE 0 END) as canceled_by_user,
SUM(CASE WHEN e.status = 'Waiting List' THEN 1 ELSE 0 END) as waiting_list,
SUM(CASE WHEN e.status = 'Approved' THEN 1 ELSE 0 END) as approved,
SUM(CASE WHEN e.status = 'Not Approved' THEN 1 ELSE 0 END) as not_approved,
SUM(CASE WHEN e.status = 'Under Analysis' THEN 1 ELSE 0 END) as under_analysis,
a.slots - SUM(CASE WHEN e.status = 'Approved' THEN 1 ELSE 0 END) as available_slots,
SUM(CASE WHEN um1.meta_value = 'Sim' AND um2.meta_value = 'Sim' THEN 1 ELSE 0 END) as military_dcta,
SUM(CASE WHEN um1.meta_value = 'Sim' AND um2.meta_value = 'Não' THEN 1 ELSE 0 END) as military_outside,
SUM(CASE WHEN um1.meta_value = 'Não' THEN 1 ELSE 0 END) as civilians
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}usermeta um1 ON e.user_id = um1.user_id AND um1.meta_key = 'militar'
LEFT JOIN {$wpdb->prefix}usermeta um2 ON e.user_id = um2.user_id AND um2.meta_key = 'efetivo_dcta'
WHERE a.modality = 'Hybrid' AND a.id = %d
GROUP BY a.name, a.slots
", $selected_activity_id));
echo '
Hybrid Enrollment Summary
';
echo '
';
echo '
Activity Name
Enrollment Canceled by User
Waiting List
Approved
Not Approved
Under Analysis
Military (DCTA)
Military (Outside)
Civilians
Total Enrollment Requests
Enrollment Availables
Slots
';
if ($summary_counts1) {
foreach ($summary_counts1 as $summary) {
echo '
' . esc_html($summary->activity_name) . '
' . esc_html($summary->canceled_by_user) . '
' . esc_html($summary->waiting_list) . '
' . esc_html($summary->approved) . '
' . esc_html($summary->not_approved) . '
' . esc_html($summary->under_analysis) . '
' . esc_html($summary->military_dcta) . '
' . esc_html($summary->military_outside) . '
' . esc_html($summary->civilians) . '
' . esc_html($summary->total_requests) . '
' . esc_html($summary->available_slots) . '
' . esc_html($summary->slots) . '
';
}
} else {
echo '
No data found
';
}
echo '
';
// In-person Main Event (filtrado pela atividade)
$summary_counts2 = $wpdb->get_results($wpdb->prepare("
SELECT
a.name as activity_name,
a.slots,
COUNT(e.id) as total_requests,
SUM(CASE WHEN e.status = 'Enrollment Canceled by User' THEN 1 ELSE 0 END) as canceled_by_user,
SUM(CASE WHEN e.status = 'Waiting List' THEN 1 ELSE 0 END) as waiting_list,
SUM(CASE WHEN e.status = 'Approved' THEN 1 ELSE 0 END) as approved,
SUM(CASE WHEN e.status = 'Not Approved' THEN 1 ELSE 0 END) as not_approved,
SUM(CASE WHEN e.status = 'Under Analysis' THEN 1 ELSE 0 END) as under_analysis,
a.slots - SUM(CASE WHEN e.status = 'Approved' THEN 1 ELSE 0 END) as available_slots,
SUM(CASE WHEN um1.meta_value = 'Sim' AND um2.meta_value = 'Sim' THEN 1 ELSE 0 END) as military_dcta,
SUM(CASE WHEN um1.meta_value = 'Sim' AND um2.meta_value = 'Não' THEN 1 ELSE 0 END) as military_outside,
SUM(CASE WHEN um1.meta_value = 'Não' THEN 1 ELSE 0 END) as civilians
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}usermeta um1 ON e.user_id = um1.user_id AND um1.meta_key = 'militar'
LEFT JOIN {$wpdb->prefix}usermeta um2 ON e.user_id = um2.user_id AND um2.meta_key = 'efetivo_dcta'
WHERE a.modality = 'In-person' AND a.id = %d
GROUP BY a.name, a.slots
", $selected_activity_id));
echo '
In-person Enrollment Summary
';
echo '
';
echo '
Activity Name
Enrollment Canceled by User
Waiting List
Approved
Not Approved
Under Analysis
Military (DCTA)
Military (Outside)
Civilians
Total Enrollment Requests
Enrollment Availables
Slots
';
if ($summary_counts2) {
foreach ($summary_counts2 as $summary) {
echo '
' . esc_html($summary->activity_name) . '
' . esc_html($summary->canceled_by_user) . '
' . esc_html($summary->waiting_list) . '
' . esc_html($summary->approved) . '
' . esc_html($summary->not_approved) . '
' . esc_html($summary->under_analysis) . '
' . esc_html($summary->military_dcta) . '
' . esc_html($summary->military_outside) . '
' . esc_html($summary->civilians) . '
' . esc_html($summary->total_requests) . '
' . esc_html($summary->available_slots) . '
' . esc_html($summary->slots) . '
';
}
} else {
echo '
No data found
';
}
echo '
';
// ---------- 17) JS (seu Chart.js original, só consumindo as variáveis novas) ----------
echo '
';
echo '
';
echo '';
$notices = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}sige_activity_notices");
if ($notices) {
echo '
Existing Notices
';
echo '
Type
Notice
';
foreach ($notices as $notice) {
echo "
{$notice->type}
{$notice->notice}
";
}
echo '
';
}
echo '';
echo '
';
}
add_action('wp_ajax_get_notice', 'get_notice');
add_action('wp_ajax_nopriv_get_notice', 'get_notice');
function get_notice()
{
global $wpdb;
$type = sanitize_text_field($_POST['type']);
$notice = $wpdb->get_var($wpdb->prepare("SELECT notice FROM {$wpdb->prefix}sige_activity_notices WHERE type = %s", $type));
if ($notice !== null) {
wp_send_json_success(['notice' => wp_kses_post($notice)]);
} else {
wp_send_json_error(['notice' => '']);
}
}
function displayMiscellaneous()
{
global $wpdb;
echo '
Miscellaneous
';
// Fetch all distinct meta keys from wp_usermeta
$meta_keys = $wpdb->get_col("SELECT DISTINCT meta_key FROM {$wpdb->prefix}usermeta");
// Fetch the saved meta keys and their statuses
$saved_meta_keys = $wpdb->get_results("SELECT meta_key, status FROM {$wpdb->prefix}sige_meta_keys", OBJECT_K);
// Remove saved meta keys from the list of all meta keys
$meta_keys = array_diff($meta_keys, array_keys($saved_meta_keys));
// Handle form submission for saving meta keys
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['save_meta_keys'])) {
$selected_meta_key = sanitize_text_field($_POST['meta_key']);
$meta_key_status = isset($_POST['meta_key_status']) ? 1 : 0;
// Save or update the selected meta key status
$existing_entry = $wpdb->get_var($wpdb->prepare("
SELECT COUNT(*) FROM {$wpdb->prefix}sige_meta_keys
WHERE meta_key = %s
", $selected_meta_key));
if ($existing_entry) {
$wpdb->update(
"{$wpdb->prefix}sige_meta_keys",
['status' => $meta_key_status],
['meta_key' => $selected_meta_key]
);
} else {
$wpdb->insert(
"{$wpdb->prefix}sige_meta_keys",
['meta_key' => $selected_meta_key, 'status' => $meta_key_status]
);
}
echo '
';
echo '';
// Display the saved meta keys in a table
if (!empty($saved_meta_keys)) {
echo '
Saved Meta Keys
';
echo '
Meta Key
Status
Action
';
foreach ($saved_meta_keys as $meta_key => $data) {
echo '
' . esc_html($meta_key) . '
' . ($data->status ? 'Enabled' : 'Disabled') . '
';
}
echo '
';
}
echo '';
// Separator line
echo '';
// New box for confidentiality and sharing information
echo '
';
echo '
Confidentiality and Sharing Information
';
// Fetch information types from the new table
$information_types = $wpdb->get_col("SELECT information_type FROM {$wpdb->prefix}sige_confidentiality_message");
echo '';
echo '
';
// Fetch and display the message for the selected information type
echo '';
echo '
';
}
// AJAX handler for fetching the message
function get_message()
{
global $wpdb;
$information_type = sanitize_text_field($_GET['information_type']);
$message = $wpdb->get_var($wpdb->prepare("
SELECT message FROM {$wpdb->prefix}sige_confidentiality_message
WHERE information_type = %s
", $information_type));
wp_send_json(['message' => $message]);
}
add_action('wp_ajax_get_message', 'get_message');
// AJAX handler for updating the message
function update_message()
{
global $wpdb;
$information_type = sanitize_text_field($_POST['information_type']);
$message = wp_kses_post($_POST['message']); // Use wp_kses_post to allow safe HTML
$updated = $wpdb->update(
"{$wpdb->prefix}sige_confidentiality_message",
['message' => $message],
['information_type' => $information_type]
);
if ($updated !== false) {
wp_send_json(['success' => true]);
} else {
wp_send_json(['success' => false]);
}
}
add_action('wp_ajax_update_message', 'update_message');
// Update settingsSubmenuPage function to include emailSettingsSubmenuPage
function settingsSubmenuPage()
{
$tab = isset($_GET['tab']) ? $_GET['tab'] : 'main_options';
echo '
';
}
// Handle GSD export
function gsd_export_data()
{
if (isset($_GET['gsd_export']) && $_GET['gsd_export'] == '1') {
global $wpdb;
$current_year = date('Y'); // Get the current year dynamically
$activity_type = 'Main Event';
// Fetch user meta keys selected by the user
$current_user = wp_get_current_user();
$selected_meta_keys = get_user_meta($current_user->ID, 'selected_additional_features_frontend', true);
// Prepare the select and join clauses for the meta fields
$meta_selects = '';
$meta_joins = '';
if (!empty($selected_meta_keys)) {
foreach ($selected_meta_keys as $meta_key) {
$meta_selects .= ", um_$meta_key.meta_value as $meta_key";
$meta_joins .= " LEFT JOIN {$wpdb->prefix}usermeta um_$meta_key ON um_$meta_key.user_id = u.ID AND um_$meta_key.meta_key = '$meta_key'";
}
}
// Fetch user IDs, emails, and selected meta data from sige_enrollments table for activity_id representing Main Event
$query = $wpdb->prepare("
SELECT DISTINCT u.ID, u.user_email $meta_selects
FROM {$wpdb->prefix}sige_enrollments e
JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
JOIN {$wpdb->prefix}users u ON e.user_id = u.ID
$meta_joins
WHERE a.type = %s AND YEAR(a.start_date) = %d AND e.status NOT IN ('Not Approved', 'Enrollment Canceled by User') AND a.modality IN ('In-person', 'Hybrid')
", $activity_type, $current_year);
$results = $wpdb->get_results($query);
if (!empty($results)) {
// Get current date and time
$current_datetime = date('Ymd_His');
$filename = "gsd_enrollments_{$current_datetime}.csv";
header('Content-Type: text/csv');
header("Content-Disposition: attachment; filename=\"$filename\"");
$output = fopen('php://output', 'w');
// Header row
$header = ['User Email'];
if (!empty($selected_meta_keys)) {
foreach ($selected_meta_keys as $meta_key) {
$header[] = ucfirst(str_replace('_', ' ', $meta_key));
}
}
fputcsv($output, $header, ';'); // Header row
// Data rows
foreach ($results as $row) {
$data = [$row->user_email];
if (!empty($selected_meta_keys)) {
foreach ($selected_meta_keys as $meta_key) {
$data[] = $row->$meta_key;
}
}
fputcsv($output, $data, ';'); // Data rows
}
fclose($output);
exit;
} else {
wp_die('No data found for GSD export.');
}
}
}
add_action('init', 'gsd_export_data');
function userbadgeQRCode()
{
// Disable cache for all users
nocache_headers();
// Append nocache query parameter to bust cache
if (!isset($_GET['nocache'])) {
// Append a unique timestamp-based parameter to the URL
$permalink = get_permalink();
if (!$permalink) {
$permalink = home_url();
}
$new_url = add_query_arg('nocache', time(), $permalink);
wp_redirect($new_url);
exit;
exit();
}
// Check if the user is logged in
if (!is_user_logged_in()) {
$login_url = wp_login_url(get_permalink()); // Redirect to the current page after login
echo '
You must be logged in to view the available activities.
';
return;
}
// Prevent back/forward navigation cache
echo '';
// Check if the user is an administrator
//if (!current_user_can('administrator')) {
// error_log('User is not an administrator.');
// return '
//
//
This page is currently under maintenance. Please check back later.
//
';
//}
global $wpdb;
$current_user_id = get_current_user_id();
// Check if the last_time_update meta is greater than 27/08/2024
$last_time_update = get_user_meta($current_user_id, 'last_time_update', true);
$cutoff_date = strtotime('2024-09-04');
// Parse the last update time to a timestamp
$last_update_timestamp = strtotime(str_replace('/', '-', $last_time_update));
// Log the parsed timestamp and other relevant data
//error_log('Last time update (original format): ' . var_export($last_time_update, true));
//error_log('Last time update (parsed timestamp): ' . ($last_update_timestamp ? $last_update_timestamp : 'NULL or invalid date'));
//error_log('Cutoff date (timestamp): ' . $cutoff_date);
//error_log('Cutoff date (formatted): ' . date('Y-m-d H:i:s', $cutoff_date));
if (!$last_time_update || $last_update_timestamp <= $cutoff_date) {
echo '
We need to verify and update your profile information.
';
echo '';
return '';
}
//error_log('Current user ID: ' . $current_user_id);
// Fetch full name from usermeta
$full_name = get_user_meta($current_user_id, 'full_name', true);
//error_log('Full name: ' . $full_name);
// Secret phrase
$secret_phrase = 'KrWy5Zc5q3gqSxPTzrw36fYrnafsRt57si6pRUpeHJNGkvQcGj';
//error_log('Secret phrase: ' . $secret_phrase);
// Generate MD5 hash of the user ID
$hashed_user_id = md5($secret_phrase . $current_user_id);
//error_log('Hashed user ID: ' . $hashed_user_id);
// Generate QR Code with the hashed user ID
ob_start();
QRcode::png($hashed_user_id, null, QR_ECLEVEL_L, 10);
$imageString = ob_get_contents();
ob_end_clean();
$qrCodeDataUri = 'data:image/png;base64,' . base64_encode($imageString);
//error_log('QR Code data URI generated.');
$current_year = (int) date('Y');
$current_year = date('Y');
//error_log('Current year: ' . $current_year);
$activities = $wpdb->get_results($wpdb->prepare("
SELECT a.*, p.place, s.full_name as speaker, e.name as event_name, e.id as event_id
FROM {$wpdb->prefix}sige_activities a
LEFT JOIN {$wpdb->prefix}sige_place p ON a.place_id = p.id
LEFT JOIN {$wpdb->prefix}sige_speakers s ON a.speaker_id = s.id
LEFT JOIN {$wpdb->prefix}sige_event_names e ON a.event_name_id = e.id
LEFT JOIN {$wpdb->prefix}sige_enrollments e2 ON e2.activity_id = a.id
WHERE (a.status = 'Open' OR a.status = 'Closed')
AND YEAR(a.start_date) = %d
AND a.attendance_control = 'yes'
AND e2.status NOT IN ('Not Requested', '')
AND e2.status IS NOT NULL
GROUP BY a.id
", $current_year));
if ($activities === null) {
//error_log('Error fetching activities: ' . $wpdb->last_error);
} elseif (empty($activities)) {
//error_log('No activities found.');
} else {
//error_log('Activities fetched successfully. Number of activities: ' . count($activities));
foreach ($activities as $activity) {
//error_log('Activity: ' . print_r($activity, true));
}
}
// Organize activities by event name
$events = [];
foreach ($activities as $activity) {
$enrollment_status_action = $wpdb->get_var($wpdb->prepare("
SELECT status FROM {$wpdb->prefix}sige_enrollments
WHERE activity_id = %d AND user_id = %d
", $activity->id, $current_user_id));
// Skip the activity if the enrollment status is 'Not Requested'
if (!$enrollment_status_action || $enrollment_status_action == 'Not Requested') {
continue; // Skip this iteration and move to the next one
}
//error_log('Activity ID: ' . $activity->id . ', Enrollment Status: ' . $enrollment_status_action);
$events[$activity->event_name][] = [
'activity_name' => $activity->name,
'modality' => $activity->modality,
'status' => $enrollment_status_action
];
}
// Output the HTML
ob_start();
?>
XXVII SYMPOSIUM ON OPERATIONAL
APPLICATIONS IN DEFENSE AREAS
Activities for
Enrollment status on
$activities) { ?>
Activity: Modality: Status:
No activities available for this year.
Dear ,
SIGE is an open event promoted by the Aeronautics Institute of Technology with the aim of
creating an environment for the exchange of experiences between the academic, industrial and operational
sectors of the Armed Forces, on issues of teaching, research, and development in Defense areas.
The organizing team is made up of master’s and doctoral students from the Graduate Program in
Operational Applications (PPGAO) at ITA.
Please note that the badge only shows your registrations for in-person and hybrid activities.
To track your registrations for online activities, visit www.sige.ita.br/inscricoes/
Contact the event Organizing Committee for questions via email at sige@ita.br
You must be logged in to perform this action.
';
}
global $wpdb;
$current_user_id = get_current_user_id();
// Fetch full name from usermeta
$full_name = get_user_meta($current_user_id, 'full_name', true);
// Secret phrase
$secret_phrase = 'KrWy5Zc5q3gqSxPTzrw36fYrnafsRt57si6pRUpeHJNGkvQcGj';
// Generate MD5 hash of the user ID
$hashed_user_id = md5($secret_phrase . $current_user_id);
// Generate QR Code with the hashed user ID
ob_start();
QRcode::png($hashed_user_id, null, QR_ECLEVEL_L, 10);
$imageString = ob_get_contents();
ob_end_clean();
$imgdata = base64_decode(str_replace('data:image/png;base64,', '', base64_encode($imageString)));
// Get activities for the current year
$current_year = date('Y');
//error_log('Current year: ' . $current_year);
$activities = $wpdb->get_results($wpdb->prepare("
SELECT a.*, p.place, s.full_name as speaker, e.name as event_name, e.id as event_id
FROM {$wpdb->prefix}sige_activities a
LEFT JOIN {$wpdb->prefix}sige_place p ON a.place_id = p.id
LEFT JOIN {$wpdb->prefix}sige_speakers s ON a.speaker_id = s.id
LEFT JOIN {$wpdb->prefix}sige_event_names e ON a.event_name_id = e.id
LEFT JOIN {$wpdb->prefix}sige_enrollments e2 ON e2.activity_id = a.id
WHERE (a.status = 'Open' OR a.status = 'Closed')
AND YEAR(a.start_date) = %d
AND a.attendance_control = 'yes'
AND e2.status NOT IN ('Not Requested', '')
AND e2.status IS NOT NULL
GROUP BY a.id
", $current_year));
if ($activities === null) {
//error_log('Error fetching activities: ' . $wpdb->last_error);
} elseif (empty($activities)) {
//error_log('No activities found.');
} else {
//error_log('Activities fetched successfully. Number of activities: ' . count($activities));
foreach ($activities as $activity) {
//error_log('Activity: ' . print_r($activity, true));
}
}
// Organize activities by event name
$events = [];
foreach ($activities as $activity) {
$enrollment_status_action = $wpdb->get_var($wpdb->prepare("
SELECT status FROM {$wpdb->prefix}sige_enrollments
WHERE activity_id = %d AND user_id = %d
", $activity->id, $current_user_id));
// Skip the activity if the enrollment status is 'Not Requested'
if (!$enrollment_status_action || $enrollment_status_action == 'Not Requested') {
continue; // Skip this iteration and move to the next one
}
//error_log('Activity ID: ' . $activity->id . ', Enrollment Status: ' . $enrollment_status_action);
$events[$activity->event_name][] = [
'activity_name' => $activity->name,
'modality' => $activity->modality,
'status' => $enrollment_status_action
];
}
// Create a new PDF document
$pdf = new TCPDF();
$pdf->AddPage();
// Add custom styling
$css = '
';
// Add logos (adjusted Y coordinate by 10 pixels)
$pdf->Image(plugin_dir_path(__FILE__) . 'image/ita.png', 15, 15, 27.5); // Adjusted Y coordinate to 20
$pdf->Image(plugin_dir_path(__FILE__) . 'image/gladioalado.png', 145, 15, 40); // Adjusted Y coordinate to 20
// Add QR code image
$pdf->Image('@' . $imgdata, 55, 55, 100, 100, 'PNG'); // Adjusted 100px down
// Add text
$pdf->SetXY(15, 55); // Adjusted 100px down
$pdf->writeHTMLCell(0, 0, '', '', '
XXVII SYMPOSIUM ON OPERATIONAL APPLICATIONS IN DEFENSE AREAS
', 0, 1, 0, true, 'C', true);
//$pdf->SetXY(15, 65); // Adjusted 100px down
// Prepare the HTML content
$html_content = '
';
$html_content .= '
Dear ' . esc_html($full_name) . ',
';
$html_content .= '
Activities for ' . $current_year . '
';
$html_content .= '
Enrollment status on : ' . date('d-m-Y - H:i:s') . '
';
if (!empty($events)) {
foreach ($events as $event_name => $activities) {
$html_content .= '
' . esc_html($event_name) . '
';
foreach ($activities as $activity) {
$html_content .= '
Contact the event Organizing Committee for questions via email at sige@ita.br
';
$html_content .= '
';
// Add HTML content
$pdf->SetXY(15, 140); // Adjusted 100px down
$pdf->writeHTMLCell(0, 0, '', '', $css . $html_content, 0, 1, 0, true, '', true);
// Output PDF
$pdf->Output('user_badge.pdf', 'D');
wp_die();
}
function verifyUserBasedOnQRCode($hashed_user_id, &$error_message = '')
{
// Validação básica do formato do hash MD5
if (!preg_match('/^[a-f0-9]{32}$/i', $hashed_user_id)) {
$error_message = 'Formato de hash inválido.';
return false;
}
$secret_phrase = 'KrWy5Zc5q3gqSxPTzrw36fYrnafsRt57si6pRUpeHJNGkvQcGj';
// Tenta obter mapa em cache (transient de curta duração)
$cache_key = 'sige_qr_hash_user_map';
$hash_map = get_transient($cache_key);
if ($hash_map === false || !is_array($hash_map)) {
$hash_map = [];
// Usa apenas IDs para economizar memória
$user_ids = get_users(['fields' => 'ids']);
foreach ($user_ids as $uid) {
$hash_map[md5($secret_phrase . $uid)] = $uid;
}
// Cache por 10 minutos
set_transient($cache_key, $hash_map, 10 * MINUTE_IN_SECONDS);
}
if (isset($hash_map[$hashed_user_id])) {
return (int)$hash_map[$hashed_user_id];
}
// Fallback adicional (caso algum usuário novo não esteja em cache ainda)
// Evita reconstruir tudo de novo: checa último user_id criado
$maybe_new_user_id = username_exists($hashed_user_id); // provavel falso, apenas placeholder
// (Mantido para possível extensão futura)
if (defined('WP_DEBUG') && WP_DEBUG) {
error_log('[verifyUserBasedOnQRCode] Hash não encontrado: ' . $hashed_user_id);
}
return false;
}
function format_full_name($full_name)
{
// Convert the full name to uppercase using multibyte function
$full_name = mb_strtoupper($full_name, 'UTF-8');
// Prepositions to keep intact
$prepositions = ['DE', 'DA', 'DO', 'DAS', 'DOS', 'E'];
// Check if the total length of the name is less than or equal to 25
if (strlen($full_name) <= 15) {
return $full_name;
}
// Process name if length exceeds 15 characters
$name_parts = explode(' ', $full_name); // Split name by spaces
$formatted_name = '';
$char_count = 0;
// Build the formatted name up to the last full name part under 15 characters
foreach ($name_parts as $index => $part) {
// Always include prepositions and conjunctions as full words
if (in_array($part, $prepositions)) {
$formatted_name .= $part . ' ';
$char_count += strlen($part) + 1;
} elseif ($char_count + strlen($part) <= 15) {
// Add full parts of the name if still within limit
$formatted_name .= $part . ' ';
$char_count += strlen($part) + 1;
} else {
break; // Stop if adding the next part would exceed 15 characters
}
}
// For the remaining parts, abbreviate them by using initials
for ($i = $index; $i < count($name_parts); $i++) {
if (!in_array($name_parts[$i], $prepositions)) {
$formatted_name .= $name_parts[$i][0] . '.';
$char_count += 2; // 1 for the initial and 1 for the period
}
}
// Ensure the final name is trimmed of any extra spaces and return it
return trim($formatted_name);
}
function generate_attendance_pdf()
{
if (isset($_POST['download_pdf']) && $_POST['download_pdf'] == 1) {
if (!is_user_logged_in()) {
wp_send_json_error(array('message' => 'You must be logged in to create and download the PDF of name Labels.'));
return;
}
global $wpdb;
$current_user_id = get_current_user_id();
// Capture form inputs
$TopCorrection = floatval($_POST['TopCorrection']);
$LeftCorrection = floatval($_POST['LeftCorrection']);
$TopMargin = floatval($_POST['TopMargin']);
$LabelHeight = floatval($_POST['LabelHeight']);
$LeftMargin = floatval($_POST['LeftMargin']);
$LabelWidth = floatval($_POST['LabelWidth']);
$status = sanitize_text_field($_POST['attendance_status']);
$start_datetime = sanitize_text_field($_POST['start_datetime']);
$end_datetime = sanitize_text_field($_POST['end_datetime']);
$sort_order = sanitize_text_field($_POST['sort_order']); // Get sorting order (Asc/Desc)
// Update user metadata
update_user_meta($current_user_id, 'TopCorrection', $TopCorrection);
update_user_meta($current_user_id, 'LeftCorrection', $LeftCorrection);
update_user_meta($current_user_id, 'TopMargin', $TopMargin);
update_user_meta($current_user_id, 'LabelHeight', $LabelHeight);
update_user_meta($current_user_id, 'LeftMargin', $LeftMargin);
update_user_meta($current_user_id, 'LabelWidth', $LabelWidth);
update_user_meta($current_user_id, 'attendance_status', $status);
update_user_meta($current_user_id, 'start_datetime', $start_datetime);
update_user_meta($current_user_id, 'end_datetime', $end_datetime);
update_user_meta($current_user_id, 'sort_order', $sort_order); // Save sorting order
// Fetch users based on the selected status, date range, and sorting order, filtering by activity type and modality
$users = $wpdb->get_results($wpdb->prepare("
SELECT DISTINCT u.ID, u.user_email
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}users u ON e.user_id = u.ID
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
WHERE e.status = %s
AND e.enrollment_date BETWEEN %s AND %s
AND a.type = 'Main Event'
AND a.modality IN ('In-person', 'Hybrid')
", $status, $start_datetime, $end_datetime));
// Sort users by formatted full name
usort($users, function ($a, $b) use ($sort_order) {
// Initialize the Collator for the current locale (e.g., "en_US" or "pt_BR")
$collator = new Collator('pt_BR'); // Adjust locale if necessary
$name_a = format_full_name(get_user_meta($a->ID, 'full_name', true) ?: $a->user_email);
$name_b = format_full_name(get_user_meta($b->ID, 'full_name', true) ?: $b->user_email);
// Log the names and sort order for debugging
error_log("Comparing: $name_a and $name_b (Sort order: $sort_order)");
if ($sort_order == 'Asc') {
return $collator->compare($name_a, $name_b);
} else {
return $collator->compare($name_b, $name_a);
}
});
// Create a new PDF document
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, 'LETTER', true, 'UTF-8', false);
$pdf->setPrintHeader(false);
$pdf->setPrintFooter(false);
$pdf->AddPage();
// If no users are found, add a message to the PDF with status, start date, and end date
if (empty($users)) {
$message = 'No users found for status: ' . $status . "\n" .
'Start Date and Time: ' . $start_datetime . "\n" .
'End Date and Time: ' . $end_datetime;
// Display the message on the PDF
$pdf->SetXY(15, 50);
$pdf->SetFont('Helvetica', '', 14);
$pdf->MultiCell(0, 10, $message, 0, 'C');
} else {
// Define positions for the labels dynamically with corrections
$label_positions = [];
for ($row = 0; $row < 10; $row++) {
$top_value = $TopMargin + ($row * $LabelHeight) + $TopCorrection;
$left_1 = $LeftMargin + $LeftCorrection;
$left_2 = $left_1 + $LabelWidth;
$left_3 = $left_2 + $LabelWidth;
$label_positions[] = ['top' => $top_value, 'left' => $left_1]; // Column 1
$label_positions[] = ['top' => $top_value, 'left' => $left_2]; // Column 2
$label_positions[] = ['top' => $top_value, 'left' => $left_3]; // Column 3
}
$i = 0; // Counter for label positions
foreach ($users as $user) {
// Get user's full name from metadata or fallback to email
$full_name = get_user_meta($user->ID, 'full_name', true);
if (!$full_name) {
$full_name = $user->user_email; // Fallback to email if no full_name is available
} else {
// Format the full_name using the support function
$full_name = format_full_name($full_name);
}
// Add full name to the current label position
if (isset($label_positions[$i])) {
$position = $label_positions[$i];
$pdf->SetXY($position['left'], $position['top']);
$pdf->Cell(60, 10, $full_name, 0, 1, 'L');
}
$i++;
// Add new page after filling positions on one page
if ($i % count($label_positions) == 0) {
$pdf->AddPage();
$i = 0;
}
}
}
// Generate file name with date, time, and status
$date_time = date('Y-m-d_H-i-s');
$file_name = "attendance_{$status}_{$date_time}.pdf"; // This uses the selected status in the file name
// Output PDF as a downloadable file
$pdf->Output($file_name, 'I');
wp_die();
} else {
// Log if the download PDF button was not pressed
error_log('Download PDF button not pressed.');
}
}
add_action('wp_ajax_generate_attendance_pdf', 'generate_attendance_pdf');
add_action('wp_ajax_nopriv_generate_attendance_pdf', 'generate_attendance_pdf'); // If you want to allow non-logged-in users as well
function generate_pdf_shortcode()
{
if (!is_user_logged_in()) {
return '
You must be logged in to handle the label edition.
';
}
// Get the current user ID
$current_user_id = get_current_user_id();
// Retrieve user metadata or set default values, handle 0 values correctly by using explicit checks for false
$TopCorrection = get_user_meta($current_user_id, 'TopCorrection', true);
$TopCorrection = ($TopCorrection !== false && $TopCorrection !== '') ? floatval($TopCorrection) : 0;
$LeftCorrection = get_user_meta($current_user_id, 'LeftCorrection', true);
$LeftCorrection = ($LeftCorrection !== false && $LeftCorrection !== '') ? floatval($LeftCorrection) : 0;
$TopMargin = get_user_meta($current_user_id, 'TopMargin', true);
$TopMargin = ($TopMargin !== false && $TopMargin !== '') ? floatval($TopMargin) : 17;
$LabelHeight = get_user_meta($current_user_id, 'LabelHeight', true);
$LabelHeight = ($LabelHeight !== false && $LabelHeight !== '') ? floatval($LabelHeight) : 25.8;
$LeftMargin = get_user_meta($current_user_id, 'LeftMargin', true);
$LeftMargin = ($LeftMargin !== false && $LeftMargin !== '') ? floatval($LeftMargin) : 4.8;
$LabelWidth = get_user_meta($current_user_id, 'LabelWidth', true);
$LabelWidth = ($LabelWidth !== false && $LabelWidth !== '') ? floatval($LabelWidth) : 70.8;
// Define the $attendance_status variable, check if it's set in the metadata or POST
$attendance_status = get_user_meta($current_user_id, 'attendance_status', true);
if (!$attendance_status) {
$attendance_status = isset($_POST['attendance_status']) ? sanitize_text_field($_POST['attendance_status']) : 'Approved';
}
// Use current date and time if start_datetime or end_datetime is not set
$start_datetime = get_user_meta($current_user_id, 'start_datetime', true)
? get_user_meta($current_user_id, 'start_datetime', true)
: (isset($_POST['start_datetime']) && !empty($_POST['start_datetime'])
? sanitize_text_field($_POST['start_datetime'])
: current_time('Y-m-d\TH:i'));
$end_datetime = get_user_meta($current_user_id, 'end_datetime', true)
? get_user_meta($current_user_id, 'end_datetime', true)
: (isset($_POST['end_datetime']) && !empty($_POST['end_datetime'])
? sanitize_text_field($_POST['end_datetime'])
: current_time('Y-m-d\TH:i'));
// Retrieve the sort order from user metadata or use a default value
$sort_order = get_user_meta($current_user_id, 'sort_order', true);
if (!$sort_order) {
$sort_order = isset($_POST['sort_order']) ? sanitize_text_field($_POST['sort_order']) : 'Asc';
}
ob_start();
?>
prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}users u ON e.user_id = u.ID
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'full_name'
LEFT JOIN {$wpdb->prefix}sige_enrollment_attendance_control ac ON e.id = ac.enrollment_id
WHERE e.user_id = %d
AND ac.attended = 'yes'
AND YEAR(a.start_date) = %d
";
$hybrid_in_person_results = $wpdb->get_results($wpdb->prepare($hybrid_in_person_query, $filtered_user_id, $selected_year));
// Query for Online attendance
$online_query = "
SELECT e.activity_id, a.name as activity_name, a.type, a.modality, a.start_date, a.start_time, a.end_date, a.end_time,
um.meta_value as user_full_name, u.user_email
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}users u ON e.user_id = u.ID
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'full_name'
LEFT JOIN {$wpdb->prefix}sige_enrollment_online_attendance_control ac ON e.id = ac.enrollment_id
WHERE e.user_id = %d
AND ac.attended = 'yes'
AND YEAR(a.start_date) = %d
";
$online_results = $wpdb->get_results($wpdb->prepare($online_query, $filtered_user_id, $selected_year));
// Get the user's full name and email for displaying
if (!empty($hybrid_in_person_results)) {
$full_name = $hybrid_in_person_results[0]->user_full_name;
$user_email = $hybrid_in_person_results[0]->user_email;
} elseif (!empty($online_results)) {
$full_name = $online_results[0]->user_full_name;
$user_email = $online_results[0]->user_email;
} else {
$full_name = $wpdb->get_var($wpdb->prepare("SELECT meta_value FROM {$wpdb->prefix}usermeta WHERE user_id = %d AND meta_key = 'full_name'", $filtered_user_id));
$user_email = $wpdb->get_var($wpdb->prepare("SELECT user_email FROM {$wpdb->prefix}users WHERE ID = %d", $filtered_user_id));
$show_no_certificates_modal = true; // Trigger the no certificates modal if no results found
}
} elseif ($_SERVER['REQUEST_METHOD'] == 'POST') {
$show_no_user_modal = true; // Trigger the no user modal if QR code doesn't match any user
}
// Output the certificate verification form
ob_start();
echo '
VERIFICAÇÃO DE VALIDADE DO CERTIFICADO
';
// Add a styled container for the form elements
echo '
OR
';
// Show Hybrid and In-person results first
if (!empty($hybrid_in_person_results)) {
echo '
Hybrid and In-Person Attendance
';
// Display full name and email
echo '
Nome: ' . esc_html($full_name) . '
';
echo '
Email: ' . esc_html($user_email) . '
';
foreach ($hybrid_in_person_results as $result) {
echo '
Data de início: ' . esc_html($result->start_date) . ' at ' . esc_html($result->start_time) . '
Data de término: ' . esc_html($result->end_date) . ' at ' . esc_html($result->end_time) . '
';
}
}
// Add the modals for no certificates or no user found
if ($show_no_certificates_modal) {
echo '
No Attendance Certificates Found
Não há certificados de participação para ' . esc_html($full_name) . ' para o ano ' . esc_html($selected_year) . '.
';
}
if ($show_no_user_modal) {
echo '
Invalid QR Code
A partir do QRCode fornecido, não foi possível identificar o usuário em nosso banco de dados para o ano selecionado. Por favor, se o problema persistir, entre em contato conosco em webti.sige@ita.br.
';
}
// Add custom JavaScript for handling QR code scanning and modals
echo '';
echo '
'; // End of wrapper div
return ob_get_clean();
}
// Register the shortcode to display the certificate verification procedure on the frontend
add_shortcode('certificate_verification_pt', 'CertificateVerificationProcedure_pt');
function CertificateVerificationProcedure_en()
{
global $wpdb;
// Initialize variables
$filtered_user_id = '';
$qrScannerUsedInput = isset($_POST['filtered_user_id']) ? sanitize_text_field($_POST['filtered_user_id']) : '';
$selected_year = isset($_POST['certificate_year']) ? intval($_POST['certificate_year']) : date('Y');
$show_no_certificates_modal = false;
$show_no_user_modal = false;
// Get the current year and next 5 years
$current_year = date('Y');
$years = range($current_year, $current_year + 0);
// Handle QR code verification when form is submitted
if ($_SERVER['REQUEST_METHOD'] == 'POST' && !empty($qrScannerUsedInput)) {
// Apply the QR code verification to get the user ID
$filtered_user_id = verifyUserBasedOnQRCodefromCertificate($qrScannerUsedInput, $selected_year);
}
// Fetch enrollments for Hybrid and In-person activities
$hybrid_in_person_results = [];
$online_results = [];
$full_name = '';
$user_email = '';
if (!empty($filtered_user_id)) {
// Query for Hybrid and In-person attendance
$hybrid_in_person_query = "
SELECT e.activity_id, a.name as activity_name, a.type, a.modality, a.start_date, a.start_time, a.end_date, a.end_time,
um.meta_value as user_full_name, u.user_email
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}users u ON e.user_id = u.ID
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'full_name'
LEFT JOIN {$wpdb->prefix}sige_enrollment_attendance_control ac ON e.id = ac.enrollment_id
WHERE e.user_id = %d
AND ac.attended = 'yes'
AND YEAR(a.start_date) = %d
";
$hybrid_in_person_results = $wpdb->get_results($wpdb->prepare($hybrid_in_person_query, $filtered_user_id, $selected_year));
// Query for Online attendance
$online_query = "
SELECT e.activity_id, a.name as activity_name, a.type, a.modality, a.start_date, a.start_time, a.end_date, a.end_time,
um.meta_value as user_full_name, u.user_email
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}users u ON e.user_id = u.ID
LEFT JOIN {$wpdb->prefix}usermeta um ON e.user_id = um.user_id AND um.meta_key = 'full_name'
LEFT JOIN {$wpdb->prefix}sige_enrollment_online_attendance_control ac ON e.id = ac.enrollment_id
WHERE e.user_id = %d
AND ac.attended = 'yes'
AND YEAR(a.start_date) = %d
";
$online_results = $wpdb->get_results($wpdb->prepare($online_query, $filtered_user_id, $selected_year));
// Get the user's full name and email for displaying
if (!empty($hybrid_in_person_results)) {
$full_name = $hybrid_in_person_results[0]->user_full_name;
$user_email = $hybrid_in_person_results[0]->user_email;
} elseif (!empty($online_results)) {
$full_name = $online_results[0]->user_full_name;
$user_email = $online_results[0]->user_email;
} else {
$full_name = $wpdb->get_var($wpdb->prepare("SELECT meta_value FROM {$wpdb->prefix}usermeta WHERE user_id = %d AND meta_key = 'full_name'", $filtered_user_id));
$user_email = $wpdb->get_var($wpdb->prepare("SELECT user_email FROM {$wpdb->prefix}users WHERE ID = %d", $filtered_user_id));
$show_no_certificates_modal = true; // Trigger the no certificates modal if no results found
}
} elseif ($_SERVER['REQUEST_METHOD'] == 'POST') {
$show_no_user_modal = true; // Trigger the no user modal if QR code doesn't match any user
}
// Output the certificate verification form
ob_start();
echo '
CERTIFICATE VALIDITY CHECK
';
// Add a styled container for the form elements
echo '
OR
';
// Show Hybrid and In-person results first
if (!empty($hybrid_in_person_results)) {
echo '
Hybrid and In-Person Attendance
';
// Display full name and email
echo '
Name: ' . esc_html($full_name) . '
';
echo '
Email: ' . esc_html($user_email) . '
';
foreach ($hybrid_in_person_results as $result) {
echo '
End Date: ' . esc_html($result->end_date) . ' at ' . esc_html($result->end_time) . '
';
}
}
// Add the modals for no certificates or no user found
if ($show_no_certificates_modal) {
echo '
No Attendance Certificates Found
There are no attendance certificates for ' . esc_html($full_name) . ' for the year ' . esc_html($selected_year) . '.
';
}
if ($show_no_user_modal) {
echo '
Invalid QR Code
From the QRCode provided, it was not possible to identify the user in our database for the year selected. Please, if the problem persists, contact us at webti.sige@ita.br.
';
}
// Add custom JavaScript for handling QR code scanning and modals
echo '';
echo '
'; // End of wrapper div
return ob_get_clean();
}
// Register the shortcode to display the certificate verification procedure on the frontend
add_shortcode('certificate_verification_en', 'CertificateVerificationProcedure_en');
// Load the required TCPDF, FPDI, and QRCode libraries
require_once(plugin_dir_path(__FILE__) . 'libs/fpdf/fpdf.php');
require_once(plugin_dir_path(__FILE__) . 'fpdi/src/autoload.php');
require_once(plugin_dir_path(__FILE__) . 'phpqrcode/qrlib.php'); // Assuming you have PHP QR Code library
use setasign\Fpdi\TcpdfFpdi;
// Shortcode function to display the form
function display_certificate_form_pt()
{
// Only proceed if not in admin area or REST API
if (is_admin() || (defined('REST_REQUEST') && REST_REQUEST)) {
return ''; // Return empty string to prevent interference
}
// Disable cache for all users
nocache_headers();
// Append nocache query parameter to bust cache
if (!isset($_GET['nocache'])) {
// Append a unique timestamp-based parameter to the URL
$new_url = add_query_arg('nocache', time(), get_permalink());
wp_redirect($new_url);
exit();
}
// Check if the user is logged in
if (!is_user_logged_in()) {
$login_url = wp_login_url(get_permalink()); // Redirect to the current page after login
return '
Você precisa estar logado para obter seus certificados.
';
}
// Prevent back/forward navigation cache
echo '';
// Prepare the output buffer
$output = '';
// Append the form HTML to the output buffer
$output .= '
';
return $output;
}
// Function to handle the file download
function handle_certificate_download_pt()
{
// Check if the download_certificate parameter is set
if (isset($_GET['download_certificate']) && $_GET['download_certificate'] == '1') {
// Ensure no output is sent before headers
if (ob_get_length()) {
ob_end_clean();
}
// Disable error display to prevent corrupting the file download
ini_set('display_errors', 0);
ini_set('display_startup_errors', 0);
error_reporting(0);
// Start output buffering
ob_start();
// Check if the user is logged in
if (!is_user_logged_in()) {
wp_safe_redirect(wp_login_url(get_permalink()));
exit;
}
global $wpdb;
// Get the current user ID
$current_user_id = get_current_user_id();
// Retrieve the user's full name from metadata (or use display_name as a fallback)
$full_name = get_user_meta($current_user_id, 'full_name', true);
if (empty($full_name)) {
// If 'full_name' meta doesn't exist, fall back to display_name
$user_info = get_userdata($current_user_id);
$full_name = $user_info->display_name;
}
// Format the full name using the provided format function
if (function_exists('format_full_name_certificate')) {
$full_name = format_full_name_certificate($full_name);
} else {
// If the function doesn't exist, just use the full name as is
$full_name = $full_name;
}
// Get the selected year from the query parameter
$certificate_year = intval($_GET['certificate_year']);
// Fetch Hybrid and In-person attendance
$query_hybrid = $wpdb->prepare("
SELECT a.name as activity_name, a.start_date, a.end_date, a.start_time, a.end_time, a.type
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}sige_enrollment_attendance_control ac ON e.id = ac.enrollment_id
WHERE e.user_id = %d AND ac.attended = 'yes' AND YEAR(a.start_date) = %d AND a.modality IN ('Hybrid', 'In-person') AND a.attendance_control = 'yes'
", $current_user_id, $certificate_year);
$hybrid_results = $wpdb->get_results($query_hybrid);
// Fetch Online attendance
$query_online = $wpdb->prepare("
SELECT a.name as activity_name, a.start_date, a.end_date, a.start_time, a.end_time, a.type
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}sige_enrollment_online_attendance_control ac ON e.id = ac.enrollment_id
WHERE e.user_id = %d AND ac.attended = 'yes' AND YEAR(a.start_date) = %d AND a.modality = 'Online'
", $current_user_id, $certificate_year);
$online_results = $wpdb->get_results($query_online);
// Combine both results
$all_activities = array_merge($hybrid_results, $online_results);
$total_activities = count($all_activities);
// If no activities are found for the year, redirect back with an error message
if ($total_activities == 0) {
wp_safe_redirect(add_query_arg('no_certificates', '1', remove_query_arg(array('download_certificate', 'certificate_year'))));
exit;
}
// Secret phrase for generating a unique hash
$secret_phrase = 'CGLtShwG9qV5p3zmS3BR7G9J8iKCTe9Pd1BAyCCorBfpCCbB7F';
// Path to your PDF template
$template_path = plugin_dir_path(__FILE__) . 'templates/certificate_template_2024_pt.pdf';
// Ensure that the template file exists
if (!file_exists($template_path)) {
// Log error and redirect back with an error message
error_log('Error: Template not found at ' . $template_path);
wp_safe_redirect(add_query_arg('template_not_found', '1', remove_query_arg(array('download_certificate', 'certificate_year'))));
exit;
}
// Get the WordPress uploads directory
$upload_dir = wp_upload_dir();
$temp_dir = trailingslashit($upload_dir['basedir']) . 'certificate_temp_files/';
// Ensure the temporary directory exists
if (!file_exists($temp_dir)) {
if (!mkdir($temp_dir, 0755, true)) {
// Log error and redirect back with an error message
error_log('Error: Failed to create temporary directory at ' . $temp_dir);
wp_safe_redirect(add_query_arg('temp_dir_error', '1', remove_query_arg(array('download_certificate', 'certificate_year'))));
exit;
}
}
// Initialize variables
$certificate_number = 1;
$pdf_files = array(); // To store paths to temp PDF files
// Fixed y-position and font size
$y = 90; // Updated y-position to 90
$font_size = 20; // Font size remains 20
// Define months in Portuguese
$months = array(
'01' => 'janeiro',
'02' => 'fevereiro',
'03' => 'março',
'04' => 'abril',
'05' => 'maio',
'06' => 'junho',
'07' => 'julho',
'08' => 'agosto',
'09' => 'setembro',
'10' => 'outubro',
'11' => 'novembro',
'12' => 'dezembro',
);
// Loop over each activity to generate individual PDFs
foreach ($all_activities as $activity) {
// Generate $hashed_user_id for this certificate
$hashed_user_id = md5($secret_phrase . $current_user_id . $certificate_year . $certificate_number);
// Generate QR Code with the hashed user ID
ob_start();
QRcode::png($hashed_user_id, null, QR_ECLEVEL_L, 10); // Generates QR Code
$imageString = ob_get_contents(); // Get the QR code image as a string
ob_end_clean();
// Decode the image string into binary format for saving
$imgdata = base64_decode(str_replace('data:image/png;base64,', '', base64_encode($imageString)));
// Save the image temporarily
$temp_image_path = $temp_dir . 'temp_qrcode_' . $certificate_number . '.png';
if (file_put_contents($temp_image_path, $imgdata) === false) {
// Log error and redirect back with an error message
error_log('Error: Could not save QR code image to ' . $temp_image_path);
wp_safe_redirect(add_query_arg('qrcode_error', '1', remove_query_arg(array('download_certificate', 'certificate_year'))));
exit;
}
// Initialize TCPDF with FPDI
$pdf = new TcpdfFpdi();
// Call before the addPage() method
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->AddPage('L'); // Set orientation to Landscape
// Import the certificate template
$pdf->setSourceFile($template_path);
$template_id = $pdf->importPage(1);
$pdf->useTemplate($template_id, 0, 0, 297, 210); // Adjust size to A4 Landscape
// Set margins
$pdf->SetMargins(15, 15, 15);
// Prepare date components
$start_date = strtotime($activity->start_date);
$end_date = strtotime($activity->end_date);
$start_day = date('d', $start_date);
$end_day = date('d', $end_date);
$month_num = date('m', $start_date); // Assuming start and end dates are in the same month
$year = date('Y', $start_date);
$month_name = $months[$month_num];
// Get the activity name
$activity_name = $activity->activity_name;
// Make the full name bold
$bold_full_name = '' . $full_name . '';
// Make the activity name bold
$bold_activity_name = '' . $activity_name . '';
// Get start_time and end_time, formatted as 'H:i'
$start_time = date('H:i', strtotime($activity->start_time));
$end_time = date('H:i', strtotime($activity->end_time));
// Calculate duration in seconds
$start_timestamp = strtotime($activity->start_date . ' ' . $activity->start_time);
$end_timestamp = strtotime($activity->end_date . ' ' . $activity->end_time);
$duration_in_seconds = $end_timestamp - $start_timestamp;
// Handle cases where end time is earlier than start time (e.g., over midnight)
if ($duration_in_seconds < 0) {
$duration_in_seconds += 24 * 3600; // Add 24 hours
}
// Calculate hours and minutes
$hours = floor($duration_in_seconds / 3600);
$minutes = floor(($duration_in_seconds % 3600) / 60);
// Format duration as 'HHhMM'
$duration_formatted = sprintf('%02dh%02d', $hours, $minutes);
// Build the activity information based on type
if ($activity->type == 'Main Event') {
$activity_info = "Certificamos que $bold_full_name participou do $bold_activity_name, realizado no Instituto Tecnológico de Aeronáutica, no período de $start_day a $end_day de $month_name de $year, em São José dos Campos - SP, Brasil.";
} elseif ($activity->type == 'Short-course') {
$activity_info = "Certificamos que $bold_full_name participou do minicurso $bold_activity_name do XXVII Simpósio de Aplicações Operacionais em Áreas de Defesa - XXVII SIGE, realizado no Instituto Tecnológico de Aeronáutica, no dia $start_day de $month_name de $year, com duração de $duration_formatted, em São José dos Campos - SP, Brasil.";
} else {
$activity_info = "Certificamos que $bold_full_name participou da atividade $bold_activity_name do XXVII Simpósio de Aplicações Operacionais em Áreas de Defesa - XXVII SIGE, realizado no Instituto Tecnológico de Aeronáutica, no dia $start_day de $month_name de $year, com duração de $duration_formatted, em São José dos Campos - SP, Brasil.";
}
// Add activity information to the PDF
$pdf->SetFont('Helvetica', '', $font_size); // Set font size
$pdf->SetXY(15, $y);
// Use writeHTMLCell to output the text with bold formatting, justified alignment, and text indent
// Wrap the activity_info in a
tag with text-indent CSS
$activity_info = '
' . $activity_info . '
';
// Use writeHTMLCell to write the HTML content
$pdf->writeHTMLCell(267, 0, '', '', $activity_info, 0, 1, false, true, 'J', true);
// Calculate positions for QR code and hashed ID
$page_width = 297; // A4 Landscape width in mm
$page_height = 210; // A4 Landscape height in mm
$margin_right = 15; // Right margin
$margin_bottom = 15; // Bottom margin
$qr_size = 35; // Adjusted size
$qr_x = $page_width - $margin_right - $qr_size - 15; // Adjusted X position for QR code
$qr_y = $page_height - $margin_bottom - $qr_size - 10; // Y position for QR code, adjusted 10mm above bottom margin
// Add the QR code image to the PDF at the specified position
$pdf->Image($temp_image_path, $qr_x, $qr_y, $qr_size, $qr_size); // Adjusted position and size of the QR code
// Add the hashed user ID (MD5) just below the QR code image
$pdf->SetFont('Helvetica', '', 8); // Set font size for the hash
$hash_x = $qr_x - 15; // Adjusted X position for hashed ID
$hash_y = $qr_y + $qr_size - 6; // Y position just below QR code with adjustment
$pdf->SetXY($hash_x, $hash_y);
$pdf->Cell(0, 10, 'ID único: ' . $hashed_user_id, 0, 1, 'L'); // Insert the hashed ID
// Save the PDF to a temporary file
$pdf_temp_path = $temp_dir . 'temp_certificate_' . $certificate_number . '.pdf';
$pdf->Output($pdf_temp_path, 'F'); // Save to file
// Add the temp PDF path to the array
$pdf_files[] = $pdf_temp_path;
// Delete the temporary QR code image file
if (file_exists($temp_image_path)) {
unlink($temp_image_path); // Delete the temp image file
}
// Increment the certificate number
$certificate_number++;
}
// Create a ZIP file containing all the PDFs
$zip = new ZipArchive();
$zip_filename = $temp_dir . 'certificados_' . $certificate_year . '.zip';
if ($zip->open($zip_filename, ZipArchive::CREATE) !== TRUE) {
// Log error and redirect back with an error message
error_log('Error: Cannot create ZIP file at ' . $zip_filename);
wp_safe_redirect(add_query_arg('zip_error', '1', remove_query_arg(array('download_certificate', 'certificate_year'))));
exit;
}
// Add files to ZIP
foreach ($pdf_files as $pdf_file) {
if (file_exists($pdf_file)) {
$zip->addFile($pdf_file, basename($pdf_file));
} else {
error_log('Error: PDF file not found: ' . $pdf_file);
// Optionally handle this error
}
}
$zip->close();
// Before sending headers and outputting the ZIP file, clean the output buffer
if (ob_get_length()) {
ob_end_clean();
}
// Send headers and force ZIP download
header('Content-Type: application/zip');
header('Content-Disposition: attachment; filename="certificados_' . $certificate_year . '.zip"');
header('Content-Length: ' . filesize($zip_filename));
header('Pragma: public');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
// Output the ZIP file
readfile($zip_filename);
// Delete temporary PDF files
foreach ($pdf_files as $pdf_file) {
if (file_exists($pdf_file)) {
unlink($pdf_file);
}
}
// Delete the ZIP file
if (file_exists($zip_filename)) {
unlink($zip_filename);
}
// Optionally, delete the temporary directory if it's empty
if (is_dir($temp_dir)) {
@rmdir($temp_dir);
}
// Stop further execution after sending the ZIP file
exit;
}
}
// Add action to handle the file download before the template is loaded
add_action('template_redirect', 'handle_certificate_download_pt');
// Create a shortcode to display the form
add_shortcode('download_certificate_pt', 'display_certificate_form_pt');
// Function to display error messages with modal styling
function display_certificate_errors_pt()
{
if (isset($_GET['no_certificates']) && $_GET['no_certificates'] == '1') {
echo '
Certificados de Participação Não Encontrados
Não há certificados de participação disponíveis para o ano selecionado.
Não foi possível criar o arquivo ZIP. Por favor, entre em contato com o suporte.
';
}
}
add_action('wp_head', 'display_certificate_errors_pt');
// Shortcode function to display the form in English
function display_certificate_form_en()
{
// Only proceed if not in admin area or REST API
if (is_admin() || (defined('REST_REQUEST') && REST_REQUEST)) {
return ''; // Return empty string to prevent interference
}
// Disable cache for all users
nocache_headers();
// Append nocache query parameter to bust cache
if (!isset($_GET['nocache'])) {
// Append a unique timestamp-based parameter to the URL
$new_url = add_query_arg('nocache', time(), get_permalink());
wp_redirect($new_url);
exit();
}
// Check if the user is logged in
if (!is_user_logged_in()) {
$login_url = wp_login_url(get_permalink()); // Redirect to the current page after login
return '
';
}
// Prevent back/forward navigation cache
echo '';
// Prepare the output buffer
$output = '';
// Check if the form has been submitted
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['certificate_year'])) {
$certificate_year = intval($_POST['certificate_year']);
// Build the redirect URL with the necessary GET parameters
$redirect_url = add_query_arg(array(
'download_certificate_en' => '1',
'certificate_year' => $certificate_year
), get_permalink());
// Redirect to the download URL
wp_redirect($redirect_url);
exit();
}
// Append the form HTML to the output buffer
$output .= '
';
return $output;
}
// Function to handle the file download for English certificates
function handle_certificate_download_en()
{
// Check if the download_certificate_en parameter is set
if (isset($_GET['download_certificate_en']) && $_GET['download_certificate_en'] == '1') {
// Ensure no output is sent before headers
if (ob_get_length()) {
ob_end_clean();
}
// Disable error display to prevent corrupting the file download
ini_set('display_errors', 0);
ini_set('display_startup_errors', 0);
error_reporting(0);
// Start output buffering
ob_start();
// Check if the user is logged in
if (!is_user_logged_in()) {
wp_safe_redirect(wp_login_url(get_permalink()));
exit;
}
global $wpdb;
// Get the current user ID
$current_user_id = get_current_user_id();
// Retrieve the user's full name from metadata (or use display_name as a fallback)
$full_name = get_user_meta($current_user_id, 'full_name', true);
if (empty($full_name)) {
// If 'full_name' meta doesn't exist, fall back to display_name
$user_info = get_userdata($current_user_id);
$full_name = $user_info->display_name;
}
// Format the full name using the provided format function
if (function_exists('format_full_name_certificate')) {
$full_name = format_full_name_certificate($full_name);
} else {
// If the function doesn't exist, just use the full name as is
$full_name = $full_name;
}
// Get the selected year from the query parameter
$certificate_year = intval($_GET['certificate_year']);
// Fetch Hybrid and In-person attendance
$query_hybrid = $wpdb->prepare("
SELECT a.name as activity_name, a.start_date, a.end_date, a.start_time, a.end_time, a.type
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}sige_enrollment_attendance_control ac ON e.id = ac.enrollment_id
WHERE e.user_id = %d AND ac.attended = 'yes' AND YEAR(a.start_date) = %d AND a.modality IN ('Hybrid', 'In-person') AND a.attendance_control = 'yes'
", $current_user_id, $certificate_year);
$hybrid_results = $wpdb->get_results($query_hybrid);
// Fetch Online attendance
$query_online = $wpdb->prepare("
SELECT a.name as activity_name, a.start_date, a.end_date, a.start_time, a.end_time, a.type
FROM {$wpdb->prefix}sige_enrollments e
LEFT JOIN {$wpdb->prefix}sige_activities a ON e.activity_id = a.id
LEFT JOIN {$wpdb->prefix}sige_enrollment_online_attendance_control ac ON e.id = ac.enrollment_id
WHERE e.user_id = %d AND ac.attended = 'yes' AND YEAR(a.start_date) = %d AND a.modality = 'Online'
", $current_user_id, $certificate_year);
$online_results = $wpdb->get_results($query_online);
// Combine both results
$all_activities = array_merge($hybrid_results, $online_results);
$total_activities = count($all_activities);
// If no activities are found for the year, redirect back with an error message
if ($total_activities == 0) {
wp_safe_redirect(add_query_arg('no_certificates_en', '1', remove_query_arg(array('download_certificate_en', 'certificate_year'))));
exit;
}
// Secret phrase for generating a unique hash
$secret_phrase = 'CGLtShwG9qV5p3zmS3BR7G9J8iKCTe9Pd1BAyCCorBfpCCbB7F';
// Path to your PDF template
$template_path = plugin_dir_path(__FILE__) . 'templates/certificate_template_2024_en.pdf';
// Ensure that the template file exists
if (!file_exists($template_path)) {
// Log error and redirect back with an error message
error_log('Error: Template not found at ' . $template_path);
wp_safe_redirect(add_query_arg('template_not_found_en', '1', remove_query_arg(array('download_certificate_en', 'certificate_year'))));
exit;
}
// Get the WordPress uploads directory
$upload_dir = wp_upload_dir();
$temp_dir = trailingslashit($upload_dir['basedir']) . 'certificate_temp_files_en/';
// Ensure the temporary directory exists
if (!file_exists($temp_dir)) {
if (!mkdir($temp_dir, 0755, true)) {
// Log error and redirect back with an error message
error_log('Error: Failed to create temporary directory at ' . $temp_dir);
wp_safe_redirect(add_query_arg('temp_dir_error_en', '1', remove_query_arg(array('download_certificate_en', 'certificate_year'))));
exit;
}
}
// Initialize variables
$certificate_number = 1;
$pdf_files = array(); // To store paths to temp PDF files
// Fixed y-position and font size
$y = 90; // Updated y-position to 90
$font_size = 20; // Font size remains 20
// Define months in English
$months = array(
'01' => 'January',
'02' => 'February',
'03' => 'March',
'04' => 'April',
'05' => 'May',
'06' => 'June',
'07' => 'July',
'08' => 'August',
'09' => 'September',
'10' => 'October',
'11' => 'November',
'12' => 'December',
);
// Loop over each activity to generate individual PDFs
foreach ($all_activities as $activity) {
// Generate $hashed_user_id for this certificate
$hashed_user_id = md5($secret_phrase . $current_user_id . $certificate_year . $certificate_number);
// Generate QR Code with the hashed user ID
ob_start();
QRcode::png($hashed_user_id, null, QR_ECLEVEL_L, 10); // Generates QR Code
$imageString = ob_get_contents(); // Get the QR code image as a string
ob_end_clean();
// Decode the image string into binary format for saving
$imgdata = base64_decode(str_replace('data:image/png;base64,', '', base64_encode($imageString)));
// Save the image temporarily
$temp_image_path = $temp_dir . 'temp_qrcode_' . $certificate_number . '.png';
if (file_put_contents($temp_image_path, $imgdata) === false) {
// Log error and redirect back with an error message
error_log('Error: Could not save QR code image to ' . $temp_image_path);
wp_safe_redirect(add_query_arg('qrcode_error_en', '1', remove_query_arg(array('download_certificate_en', 'certificate_year'))));
exit;
}
// Initialize TCPDF with FPDI
$pdf = new TcpdfFpdi();
// Call before the addPage() method
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->AddPage('L'); // Set orientation to Landscape
// Import the certificate template
$pdf->setSourceFile($template_path);
$template_id = $pdf->importPage(1);
$pdf->useTemplate($template_id, 0, 0, 297, 210); // Adjust size to A4 Landscape
// Set margins
$pdf->SetMargins(15, 15, 15);
// Prepare date components
$start_date = strtotime($activity->start_date);
$end_date = strtotime($activity->end_date);
$start_day = date('d', $start_date);
$end_day = date('d', $end_date);
$month_num = date('m', $start_date); // Assuming start and end dates are in the same month
$year = date('Y', $start_date);
$month_name = $months[$month_num];
// Get the activity name
$activity_name = $activity->activity_name;
// Make the full name bold
$bold_full_name = '' . $full_name . '';
// Make the activity name bold
$bold_activity_name = '' . $activity_name . '';
// Get start_time and end_time, formatted as 'H:i'
$start_time = date('H:i', strtotime($activity->start_time));
$end_time = date('H:i', strtotime($activity->end_time));
// Calculate duration in seconds
$start_timestamp = strtotime($activity->start_date . ' ' . $activity->start_time);
$end_timestamp = strtotime($activity->end_date . ' ' . $activity->end_time);
$duration_in_seconds = $end_timestamp - $start_timestamp;
// Handle cases where end time is earlier than start time (e.g., over midnight)
if ($duration_in_seconds < 0) {
$duration_in_seconds += 24 * 3600; // Add 24 hours
}
// Calculate hours and minutes
$hours = floor($duration_in_seconds / 3600);
$minutes = floor(($duration_in_seconds % 3600) / 60);
// Format duration as 'HHhMM'
$duration_formatted = sprintf('%02dh%02d', $hours, $minutes);
// Build the activity information based on type
if ($activity->type == 'Main Event') {
$activity_info = "This is to certify that $bold_full_name participated in the $bold_activity_name, held at the Aeronautics Institute of Technology, from $start_day to $end_day of $month_name, $year, in São José dos Campos - SP, Brazil.";
} elseif ($activity->type == 'Short-course') {
$activity_info = "This is to certify that $bold_full_name participated in the short-course $bold_activity_name of the XXVII Symposium of Operational Applications in Defense Areas - XXVII SIGE, held at the Aeronautics Institute of Technology, on $start_day of $month_name, $year, with a duration of $duration_formatted, in São José dos Campos - SP, Brazil.";
} else {
$activity_info = "This is to certify that $bold_full_name participated in the activity $bold_activity_name of the XXVII Symposium of Operational Applications in Defense Areas - XXVII SIGE, held at the Aeronautics Institute of Technology, on $start_day of $month_name, $year, with a duration of $duration_formatted, in São José dos Campos - SP, Brazil.";
}
// Add activity information to the PDF
$pdf->SetFont('Helvetica', '', $font_size); // Set font size
$pdf->SetXY(15, $y);
// Use writeHTMLCell to output the text with bold formatting, justified alignment, and text indent
// Wrap the activity_info in a
tag with text-indent CSS
$activity_info = '
' . $activity_info . '
';
// Use writeHTMLCell to write the HTML content
$pdf->writeHTMLCell(267, 0, '', '', $activity_info, 0, 1, false, true, 'J', true);
// Calculate positions for QR code and hashed ID
$page_width = 297; // A4 Landscape width in mm
$page_height = 210; // A4 Landscape height in mm
$margin_right = 15; // Right margin
$margin_bottom = 15; // Bottom margin
$qr_size = 35; // Adjusted size
$qr_x = $page_width - $margin_right - $qr_size - 15; // Adjusted X position for QR code
$qr_y = $page_height - $margin_bottom - $qr_size - 10; // Y position for QR code, adjusted 10mm above bottom margin
// Add the QR code image to the PDF at the specified position
$pdf->Image($temp_image_path, $qr_x, $qr_y, $qr_size, $qr_size); // Adjusted position and size of the QR code
// Add the hashed user ID (MD5) just below the QR code image
$pdf->SetFont('Helvetica', '', 8); // Set font size for the hash
$hash_x = $qr_x - 15; // Adjusted X position for hashed ID
$hash_y = $qr_y + $qr_size - 6; // Y position just below QR code with adjustment
$pdf->SetXY($hash_x, $hash_y);
$pdf->Cell(0, 10, 'Unique ID: ' . $hashed_user_id, 0, 1, 'L'); // Insert the hashed ID
// Save the PDF to a temporary file
$pdf_temp_path = $temp_dir . 'temp_certificate_' . $certificate_number . '.pdf';
$pdf->Output($pdf_temp_path, 'F'); // Save to file
// Add the temp PDF path to the array
$pdf_files[] = $pdf_temp_path;
// Delete the temporary QR code image file
if (file_exists($temp_image_path)) {
unlink($temp_image_path); // Delete the temp image file
}
// Increment the certificate number
$certificate_number++;
}
// Create a ZIP file containing all the PDFs
$zip = new ZipArchive();
$zip_filename = $temp_dir . 'certificates_' . $certificate_year . '.zip';
if ($zip->open($zip_filename, ZipArchive::CREATE) !== TRUE) {
// Log error and redirect back with an error message
error_log('Error: Cannot create ZIP file at ' . $zip_filename);
wp_safe_redirect(add_query_arg('zip_error_en', '1', remove_query_arg(array('download_certificate_en', 'certificate_year'))));
exit;
}
// Add files to ZIP
foreach ($pdf_files as $pdf_file) {
if (file_exists($pdf_file)) {
$zip->addFile($pdf_file, basename($pdf_file));
} else {
error_log('Error: PDF file not found: ' . $pdf_file);
// Optionally handle this error
}
}
$zip->close();
// Before sending headers and outputting the ZIP file, clean the output buffer
if (ob_get_length()) {
ob_end_clean();
}
// Send headers and force ZIP download
header('Content-Type: application/zip');
header('Content-Disposition: attachment; filename="certificates_' . $certificate_year . '.zip"');
header('Content-Length: ' . filesize($zip_filename));
header('Pragma: public');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
// Output the ZIP file
readfile($zip_filename);
// Delete temporary PDF files
foreach ($pdf_files as $pdf_file) {
if (file_exists($pdf_file)) {
unlink($pdf_file);
}
}
// Delete the ZIP file
if (file_exists($zip_filename)) {
unlink($zip_filename);
}
// Optionally, delete the temporary directory if it's empty
if (is_dir($temp_dir)) {
@rmdir($temp_dir);
}
// Stop further execution after sending the ZIP file
exit;
}
}
// Add action to handle the file download before the template is loaded
add_action('template_redirect', 'handle_certificate_download_en');
// Function to display error messages with modal styling for English version
function display_certificate_errors_en()
{
if (isset($_GET['no_certificates_en']) && $_GET['no_certificates_en'] == '1') {
echo '
No Attendance Certificates Found
There are no attendance certificates available for the selected year.
Failed to create ZIP file. Please contact support.
';
}
}
add_action('wp_head', 'display_certificate_errors_en');
// Register the shortcode
add_shortcode('download_certificate_en', 'display_certificate_form_en');
function format_full_name_certificate($full_name)
{
// Convert the full name to uppercase using multibyte function
$full_name = mb_strtoupper($full_name, 'UTF-8');
// Prepositions to keep intact
$prepositions = ['DE', 'DA', 'DO', 'DAS', 'DOS', 'E'];
// Check if the total length of the name is less than or equal to 35
if (strlen($full_name) <= 35) {
return $full_name;
}
// Process name if length exceeds 35 characters
$name_parts = explode(' ', $full_name); // Split name by spaces
$formatted_name = '';
$char_count = 0;
// Build the formatted name up to the last full name part under 35 characters
foreach ($name_parts as $index => $part) {
// Always include prepositions and conjunctions as full words
if (in_array($part, $prepositions)) {
$formatted_name .= $part . ' ';
$char_count += strlen($part) + 1;
} elseif ($char_count + strlen($part) <= 35) {
// Add full parts of the name if still within limit
$formatted_name .= $part . ' ';
$char_count += strlen($part) + 1;
} else {
break; // Stop if adding the next part would exceed 35 characters
}
}
// For the remaining parts, abbreviate them by using initials
for ($i = $index; $i < count($name_parts); $i++) {
if (!in_array($name_parts[$i], $prepositions)) {
$formatted_name .= $name_parts[$i][0] . '.';
$char_count += 2; // 1 for the initial and 1 for the period
}
}
// Ensure the final name is trimmed of any extra spaces and return it
return trim($formatted_name);
}
function verifyUserBasedOnQRCodefromCertificate($hashed_user_id, $certificate_year)
{
global $wpdb;
// Define the secret phrase used for hashing
$secret_phrase = 'CGLtShwG9qV5p3zmS3BR7G9J8iKCTe9Pd1BAyCCorBfpCCbB7F';
// Loop through users to find the matching hash
$users = get_users();
foreach ($users as $user) {
for ($certificate_number = 1; $certificate_number <= 10; $certificate_number++) {
$computed_hash = md5($secret_phrase . $user->ID . $certificate_year . $certificate_number);
if ($computed_hash === $hashed_user_id) {
return $user->ID;
}
}
}
// Return false if no match found
return false;
}
function sige_enqueue_export_csv_script($unique_id = '')
{
if (!is_admin() && !empty($unique_id)) {
wp_enqueue_script(
'sige-export-csv',
plugin_dir_url(__FILE__) . 'js/sige-export-csv.js',
array('jquery'),
null,
true
);
wp_localize_script('sige-export-csv', 'SIGEExportVars', array(
'unique_id' => $unique_id
));
}
}
add_action('wp_enqueue_scripts', 'sige_enqueue_export_csv_script');
// Secure AJAX handler for saving confidentiality response
add_action('wp_ajax_save_confidentiality_response', function () {
// Only allow logged-in users
if (!is_user_logged_in()) {
wp_send_json_error(['message' => 'Unauthorized'], 403);
exit;
}
$current_user_id = get_current_user_id();
// Validate and sanitize input
$user_id = isset($_POST['user_id']) ? intval($_POST['user_id']) : 0;
$year = isset($_POST['year']) ? intval($_POST['year']) : 0;
$response = isset($_POST['response']) ? sanitize_text_field($_POST['response']) : '';
// Only allow the current user to update their own record
if ($user_id !== $current_user_id || $year < 2000 || $year > (int)date('Y') + 1) {
wp_send_json_error(['message' => 'Invalid request'], 400);
exit;
}
global $wpdb;
$table = $wpdb->prefix . 'sige_confidentiality_user_option';
// Insert or update the user's response
$existing = $wpdb->get_var($wpdb->prepare(
"SELECT COUNT(*) FROM $table WHERE user_id = %d AND year = %d",
$current_user_id,
$year
));
if ($existing) {
$wpdb->update(
$table,
['response' => $response],
['user_id' => $current_user_id, 'year' => $year]
);
} else {
$wpdb->insert(
$table,
['user_id' => $current_user_id, 'year' => $year, 'response' => $response]
);
}
wp_send_json_success(['message' => 'Response saved']);
exit;
});
function attendance_control($attributes)
{
nocache_headers();
if (!isset($_GET['nocache'])) {
$new_url = add_query_arg('nocache', time(), get_permalink());
wp_redirect($new_url);
exit();
}
// Prevent back/forward navigation cache
$output = '';
if (!is_user_logged_in()) {
$login_url = wp_login_url(get_permalink());
return '
Você precisa estar logado para acessar esta página.
';
}
$user = wp_get_current_user();
$user_id = $user->ID;
$args = shortcode_atts(array('activity_id' => 'none'), $attributes);
$activity_id = intval($args['activity_id']);
global $wpdb;
$result = $wpdb->get_row($wpdb->prepare("
SELECT id
FROM {$wpdb->prefix}sige_enrollments
WHERE user_id = '%d'
AND activity_id = '%d'
", $user_id, $activity_id));
if ($result) {
$enrollment_id = $result->id;
$already_confirmed = $wpdb->get_row($wpdb->prepare("
SELECT id
FROM {$wpdb->prefix}sige_enrollment_attendance_control
WHERE enrollment_id = '%d'
", $enrollment_id));
if (!$already_confirmed) {
$results = $wpdb->insert("{$wpdb->prefix}sige_enrollment_attendance_control", ['enrollment_id' => $enrollment_id, 'attended' => 'yes']);
if (!$results) {
return '
Sua presença não pode ser confirmada no momento, por favor tente novamente.
';
}
}
return '
Presença confirmada.
';
} else {
return '
Você não está inscrito neste evento.
';
}
}
add_shortcode('attendanceControl', 'attendance_control');
/**
* Shortcode handler for QR code POST access.
* Usage: [qr_post_access]
* Only allows access if POST request with correct token.
*/
function qr_post_access_handler()
{
// Only allow POST requests
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
return '
Access denied. Please scan the QR code and submit the form to access this page.
';
}
// Validate QR token (replace 'EXPECTED_TOKEN' with your actual token)
if (!isset($_POST['qr_token']) || $_POST['qr_token'] !== 'EXPECTED_TOKEN') {
return '
Invalid QR code data.
';
}
// Authorized content
return '
Welcome! You accessed this page via POST after scanning the QR code.
';
});
// Redirect to prevent form resubmission
wp_redirect(add_query_arg('feedback', 'success', wp_get_referer()));
exit;
}
// Display the feedback form template
include_once plugin_dir_path(__FILE__) . 'templates/feedback-form.php';
}
add_shortcode('feedback_form', 'handle_feedback_submission');
function display_feedback_statistics()
{
global $wpdb;
ob_start();
echo '';
$global_stats = $wpdb->get_results(
"
SELECT
COUNT(CASE WHEN global_score IS NOT NULL THEN 1 END) as total_responses,
COUNT(CASE WHEN global_score = 'Muito Bom' THEN 1 END) as vgood,
COUNT(CASE WHEN global_score = 'Bom' THEN 1 END) as good,
COUNT(CASE WHEN global_score = 'Neutro' THEN 1 END) as regular,
COUNT(CASE WHEN global_score = 'Ruim' THEN 1 END) as poor,
COUNT(CASE WHEN global_score = 'Muito Ruim' THEN 1 END) as vpoor
FROM {$wpdb->prefix}sige_feedback",
ARRAY_A
);
$schedule_stats = $wpdb->get_results(
"
SELECT
COUNT(CASE WHEN schedule IS NOT NULL THEN 1 END) as total_responses,
COUNT(CASE WHEN schedule = 'Muito Bom' THEN 1 END) as vgood,
COUNT(CASE WHEN schedule = 'Bom' THEN 1 END) as good,
COUNT(CASE WHEN schedule = 'Neutro' THEN 1 END) as regular,
COUNT(CASE WHEN schedule = 'Ruim' THEN 1 END) as poor,
COUNT(CASE WHEN schedule = 'Ruim' THEN 1 END) as vpoor
FROM {$wpdb->prefix}sige_feedback",
ARRAY_A
);
$technical_stats = $wpdb->get_results(
"
SELECT
COUNT(CASE WHEN technical_sessions IS NOT NULL THEN 1 END) as total_responses,
COUNT(CASE WHEN technical_sessions = 'Muito Bom' THEN 1 END) as vgood,
COUNT(CASE WHEN technical_sessions = 'Bom' THEN 1 END) as good,
COUNT(CASE WHEN technical_sessions = 'Regular' THEN 1 END) as regular,
COUNT(CASE WHEN technical_sessions = 'Ruim' THEN 1 END) as poor,
COUNT(CASE WHEN technical_sessions = 'Muito Ruim' THEN 1 END) as vpoor
FROM {$wpdb->prefix}sige_feedback",
ARRAY_A
);
$lecture_stats = $wpdb->get_results(
"
SELECT
COUNT(CASE WHEN lectures IS NOT NULL THEN 1 END) as total_responses,
COUNT(CASE WHEN lectures = 'Muito Bom' THEN 1 END) as vgood,
COUNT(CASE WHEN lectures = 'Bom' THEN 1 END) as good,
COUNT(CASE WHEN lectures = 'Regular' THEN 1 END) as regular,
COUNT(CASE WHEN lectures = 'Ruim' THEN 1 END) as poor,
COUNT(CASE WHEN lectures = 'Muito Ruim' THEN 1 END) as vpoor
FROM {$wpdb->prefix}sige_feedback",
ARRAY_A
);
$coffee_stats = $wpdb->get_results(
"
SELECT
COUNT(CASE WHEN coffee_break IS NOT NULL THEN 1 END) as total_responses,
COUNT(CASE WHEN coffee_break = 'Muito Bom' THEN 1 END) as vgood,
COUNT(CASE WHEN coffee_break = 'Bom' THEN 1 END) as good,
COUNT(CASE WHEN coffee_break = 'Regular' THEN 1 END) as regular,
COUNT(CASE WHEN coffee_break = 'Ruim' THEN 1 END) as poor,
COUNT(CASE WHEN coffee_break = 'Muito Ruim' THEN 1 END) as vpoor
FROM {$wpdb->prefix}sige_feedback",
ARRAY_A
);
$opening_stats = $wpdb->get_results(
"
SELECT
COUNT(CASE WHEN opening IS NOT NULL THEN 1 END) as total_responses,
COUNT(CASE WHEN opening = 'Muito Bom' THEN 1 END) as vgood,
COUNT(CASE WHEN opening = 'Bom' THEN 1 END) as good,
COUNT(CASE WHEN opening = 'Regular' THEN 1 END) as regular,
COUNT(CASE WHEN opening = 'Ruim' THEN 1 END) as poor,
COUNT(CASE WHEN opening = 'Muito Ruim' THEN 1 END) as vpoor
FROM {$wpdb->prefix}sige_feedback",
ARRAY_A
);
$shortc_stats = $wpdb->get_results(
"
SELECT
COUNT(CASE WHEN short_courses IS NOT NULL THEN 1 END) as total_responses,
COUNT(CASE WHEN short_courses = 'Muito Bom' THEN 1 END) as vgood,
COUNT(CASE WHEN short_courses = 'Bom' THEN 1 END) as good,
COUNT(CASE WHEN short_courses = 'Regular' THEN 1 END) as regular,
COUNT(CASE WHEN short_courses = 'Ruim' THEN 1 END) as poor,
COUNT(CASE WHEN short_courses = 'Muito Ruim' THEN 1 END) as vpoor
FROM {$wpdb->prefix}sige_feedback",
ARRAY_A
);
// Display results in HTML format
echo '
';
// Global Score Section
echo '
Avaliação Geral do SIGE
';
echo '
Total de Respostas
Muito Bom
Bom
Neutro
Ruim
Muito Ruim
' . $global_stats[0]['total_responses'] . '
' . $global_stats[0]['vgood'] . '
' . $global_stats[0]['good'] . '
' . $global_stats[0]['regular'] . '
' . $global_stats[0]['poor'] . '
' . $global_stats[0]['vpoor'] . '
';
// Schedule Section
echo '
Avaliação da agenda do evento
';
echo '
Total de Respostas
Muito Bom
Bom
Neutro
Ruim
Muito Ruim
' . $schedule_stats[0]['total_responses'] . '
' . $schedule_stats[0]['vgood'] . '
' . $schedule_stats[0]['good'] . '
' . $schedule_stats[0]['regular'] . '
' . $schedule_stats[0]['poor'] . '
' . $schedule_stats[0]['vpoor'] . '
';
// Technical Sessions Section
echo '
Avaliação da duração das seções técnicas
';
echo '
Total de Respostas
Muito Bom
Bom
Neutro
Ruim
Muito Ruim
' . $technical_stats[0]['total_responses'] . '
' . $technical_stats[0]['vgood'] . '
' . $technical_stats[0]['good'] . '
' . $technical_stats[0]['regular'] . '
' . $technical_stats[0]['poor'] . '
' . $technical_stats[0]['vpoor'] . '
';
echo '
Avaliação dos temas das palestras
';
echo '
Total de Respostas
Muito Bom
Bom
Neutro
Ruim
Muito Ruim
' . $lecture_stats[0]['total_responses'] . '
' . $lecture_stats[0]['vgood'] . '
' . $lecture_stats[0]['good'] . '
' . $lecture_stats[0]['regular'] . '
' . $lecture_stats[0]['poor'] . '
' . $lecture_stats[0]['vpoor'] . '
';
echo '
Avaliação dos coffee breaks
';
echo '
Total de Respostas
Muito Bom
Bom
Neutro
Ruim
Muito Ruim
' . $coffee_stats[0]['total_responses'] . '
' . $coffee_stats[0]['vgood'] . '
' . $coffee_stats[0]['good'] . '
' . $coffee_stats[0]['regular'] . '
' . $coffee_stats[0]['poor'] . '
' . $coffee_stats[0]['vpoor'] . '
';
echo '
Avaliação da abertura do evento
';
echo '
Total de Respostas
Muito Bom
Bom
Neutro
Ruim
Muito Ruim
' . $opening_stats[0]['total_responses'] . '
' . $opening_stats[0]['vgood'] . '
' . $opening_stats[0]['good'] . '
' . $opening_stats[0]['regular'] . '
' . $opening_stats[0]['poor'] . '
' . $opening_stats[0]['vpoor'] . '
';
echo '
Avaliação dos minicursos
';
echo '
Total de Respostas
Muito Bom
Bom
Neutro
Ruim
Muito Ruim
' . $shortc_stats[0]['total_responses'] . '
' . $shortc_stats[0]['vgood'] . '
' . $shortc_stats[0]['good'] . '
' . $shortc_stats[0]['regular'] . '
' . $shortc_stats[0]['poor'] . '
' . $shortc_stats[0]['vpoor'] . '
';
echo '
Avaliações escritas dos participantes
';
echo '
' . $shortc_stats[0]['total_responses'] . '
' . $shortc_stats[0]['vgood'] . '
' . $shortc_stats[0]['good'] . '
' . $shortc_stats[0]['regular'] . '
' . $shortc_stats[0]['poor'] . '
' . $shortc_stats[0]['vpoor'] . '
';
$suggestions = $wpdb->get_results(
"SELECT suggestions, date
FROM {$wpdb->prefix}sige_feedback
WHERE suggestions IS NOT NULL
AND suggestions != ''
ORDER BY date DESC",
ARRAY_A
);
// Display suggestions section
echo '
 . 'image/ita.png'; ?>)
 . 'image/gladioalado.png'; ?>)