Enhanced PHP File Manager

⬆️ Go Up (..)

📊 Server Information

Server:

LiteSpeed

System:

Linux 5.14.0-503.34.1.el9_5.x86_64

PHP Version:

8.2.30

User:

u329947844

Directory:

/home/u329947844/domains/ecommer.in/public_html/billing/medical

Free Space:

10.28 TB

🔧 Quick Actions

✏️ Editing: audit-trail.php

<!doctype html>
<html lang="en">

<?php 
include('includes/head.php');

// Database connection
include('includes/db_connection.php');

// Get filter parameters
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
$action_type = isset($_GET['action_type']) ? $_GET['action_type'] : '';
$user_filter = isset($_GET['user_filter']) ? $_GET['user_filter'] : '';
$table_filter = isset($_GET['table_filter']) ? $_GET['table_filter'] : '';
$record_id = isset($_GET['record_id']) ? intval($_GET['record_id']) : 0;

// Create audit trail table if it doesn't exist
$create_audit_table = "
CREATE TABLE IF NOT EXISTS `audit_trail` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) DEFAULT NULL,
    `user_name` varchar(255) DEFAULT NULL,
    `action_type` enum('CREATE','UPDATE','DELETE','LOGIN','LOGOUT','VIEW','PRINT','EXPORT','OTHER') NOT NULL,
    `table_name` varchar(100) DEFAULT NULL,
    `record_id` int(11) DEFAULT NULL,
    `old_data` text DEFAULT NULL,
    `new_data` text DEFAULT NULL,
    `ip_address` varchar(45) DEFAULT NULL,
    `user_agent` text DEFAULT NULL,
    `timestamp` datetime NOT NULL,
    `description` varchar(500) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_action_type` (`action_type`),
    KEY `idx_table_name` (`table_name`),
    KEY `idx_record_id` (`record_id`),
    KEY `idx_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
";

$conn->query($create_audit_table);

// Function to log audit trail
function logAuditTrail($conn, $action_type, $table_name = null, $record_id = null, $old_data = null, $new_data = null, $description = null) {
    $user_id = isset($_SESSION['user_id']) ? $_SESSION['user_id'] : null;
    $user_name = isset($_SESSION['user_name']) ? $_SESSION['user_name'] : 'System';
    $ip_address = $_SERVER['REMOTE_ADDR'] ?? '127.0.0.1';
    $user_agent = $_SERVER['HTTP_USER_AGENT'] ?? '';
    
    $stmt = $conn->prepare("
        INSERT INTO audit_trail 
        (user_id, user_name, action_type, table_name, record_id, old_data, new_data, ip_address, user_agent, timestamp, description)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?)
    ");
    
    $stmt->bind_param(
        "isssisssss",
        $user_id,
        $user_name,
        $action_type,
        $table_name,
        $record_id,
        $old_data,
        $new_data,
        $ip_address,
        $user_agent,
        $description
    );
    
    return $stmt->execute();
}

// Log viewing of audit trail
logAuditTrail($conn, 'VIEW', 'audit_trail', null, null, null, 'Viewed audit trail report');

// Build WHERE clause for filtering
$where_conditions = [];
$query_params = [];
$param_types = '';

if (!empty($start_date)) {
    $where_conditions[] = "DATE(timestamp) >= ?";
    $query_params[] = $start_date;
    $param_types .= 's';
}

if (!empty($end_date)) {
    $where_conditions[] = "DATE(timestamp) <= ?";
    $query_params[] = $end_date;
    $param_types .= 's';
}

if (!empty($action_type)) {
    $where_conditions[] = "action_type = ?";
    $query_params[] = $action_type;
    $param_types .= 's';
}

if (!empty($user_filter)) {
    $where_conditions[] = "(user_name LIKE ? OR user_id = ?)";
    $query_params[] = "%$user_filter%";
    $query_params[] = is_numeric($user_filter) ? $user_filter : 0;
    $param_types .= 'si';
}

if (!empty($table_filter)) {
    $where_conditions[] = "table_name = ?";
    $query_params[] = $table_filter;
    $param_types .= 's';
}

if ($record_id > 0) {
    $where_conditions[] = "record_id = ?";
    $query_params[] = $record_id;
    $param_types .= 'i';
}

$where_clause = '';
if (!empty($where_conditions)) {
    $where_clause = "WHERE " . implode(" AND ", $where_conditions);
}

// Fetch audit trail data with filters
$audit_query = "
    SELECT 
        a.*,
        CASE 
            WHEN a.action_type = 'CREATE' THEN 'bg-success'
            WHEN a.action_type = 'UPDATE' THEN 'bg-info'
            WHEN a.action_type = 'DELETE' THEN 'bg-danger'
            WHEN a.action_type = 'LOGIN' THEN 'bg-primary'
            WHEN a.action_type = 'LOGOUT' THEN 'bg-secondary'
            WHEN a.action_type = 'VIEW' THEN 'bg-warning'
            ELSE 'bg-dark'
        END as action_class
    FROM audit_trail a
    $where_clause
    ORDER BY a.timestamp DESC
    LIMIT 1000
";

if (!empty($query_params)) {
    $stmt = $conn->prepare($audit_query);
    if ($stmt) {
        $stmt->bind_param($param_types, ...$query_params);
        $stmt->execute();
        $audit_result = $stmt->get_result();
    } else {
        // Fallback to simple query if prepare fails
        $audit_result = $conn->query("SELECT * FROM audit_trail ORDER BY timestamp DESC LIMIT 1000");
    }
} else {
    $audit_result = $conn->query($audit_query);
}

$total_records = $audit_result ? $audit_result->num_rows : 0;

// Get summary statistics
$summary_query = "
    SELECT 
        COUNT(*) as total_actions,
        COUNT(DISTINCT user_name) as unique_users,
        COUNT(DISTINCT table_name) as tables_affected,
        COUNT(DISTINCT DATE(timestamp)) as active_days,
        MIN(timestamp) as first_action,
        MAX(timestamp) as last_action
    FROM audit_trail
    $where_clause
";

if (!empty($query_params)) {
    $summary_stmt = $conn->prepare($summary_query);
    if ($summary_stmt) {
        $summary_stmt->bind_param($param_types, ...$query_params);
        $summary_stmt->execute();
        $summary_result = $summary_stmt->get_result();
        $summary = $summary_result->fetch_assoc();
    } else {
        $summary = [
            'total_actions' => 0,
            'unique_users' => 0,
            'tables_affected' => 0,
            'active_days' => 0,
            'first_action' => null,
            'last_action' => null
        ];
    }
} else {
    $summary_result = $conn->query($summary_query);
    $summary = $summary_result ? $summary_result->fetch_assoc() : [
        'total_actions' => 0,
        'unique_users' => 0,
        'tables_affected' => 0,
        'active_days' => 0,
        'first_action' => null,
        'last_action' => null
    ];
}

// Get action type distribution
$action_stats_query = "
    SELECT 
        action_type,
        COUNT(*) as action_count,
        COUNT(DISTINCT user_name) as users_per_action
    FROM audit_trail
    $where_clause
    GROUP BY action_type
    ORDER BY action_count DESC
";

if (!empty($query_params)) {
    $action_stats_stmt = $conn->prepare($action_stats_query);
    if ($action_stats_stmt) {
        $action_stats_stmt->bind_param($param_types, ...$query_params);
        $action_stats_stmt->execute();
        $action_stats_result = $action_stats_stmt->get_result();
    } else {
        $action_stats_result = $conn->query("
            SELECT 
                action_type,
                COUNT(*) as action_count,
                COUNT(DISTINCT user_name) as users_per_action
            FROM audit_trail
            GROUP BY action_type
            ORDER BY action_count DESC
        ");
    }
} else {
    $action_stats_result = $conn->query($action_stats_query);
}

// Get user activity statistics - FIXED VERSION
$user_stats_query = "
    SELECT 
        user_name,
        COUNT(*) as action_count,
        COUNT(DISTINCT DATE(timestamp)) as active_days,
        MIN(timestamp) as first_action,
        MAX(timestamp) as last_action,
        GROUP_CONCAT(DISTINCT action_type ORDER BY action_type SEPARATOR ', ') as action_types
    FROM audit_trail
";

// Build the complete WHERE clause for user stats
$user_where_conditions = ["user_name IS NOT NULL"];
if (!empty($where_conditions)) {
    $user_where_conditions = array_merge($user_where_conditions, $where_conditions);
}
$user_where_clause = "WHERE " . implode(" AND ", $user_where_conditions);

$user_stats_query .= " $user_where_clause GROUP BY user_name ORDER BY action_count DESC LIMIT 10";

if (!empty($query_params)) {
    // For user stats, we need to handle the additional "user_name IS NOT NULL" condition
    // We'll add an empty string parameter for it (it doesn't need a parameter)
    $user_param_types = $param_types; // user_name IS NOT NULL doesn't need a parameter
    $user_stats_stmt = $conn->prepare($user_stats_query);
    if ($user_stats_stmt) {
        $user_stats_stmt->bind_param($user_param_types, ...$query_params);
        $user_stats_stmt->execute();
        $user_stats_result = $user_stats_stmt->get_result();
    } else {
        // Fallback query without filters if prepare fails
        $user_stats_result = $conn->query("
            SELECT 
                user_name,
                COUNT(*) as action_count,
                COUNT(DISTINCT DATE(timestamp)) as active_days,
                MIN(timestamp) as first_action,
                MAX(timestamp) as last_action,
                GROUP_CONCAT(DISTINCT action_type ORDER BY action_type SEPARATOR ', ') as action_types
            FROM audit_trail
            WHERE user_name IS NOT NULL
            GROUP BY user_name
            ORDER BY action_count DESC
            LIMIT 10
        ");
    }
} else {
    $user_stats_result = $conn->query($user_stats_query);
}

// Get table activity statistics - FIXED VERSION
$table_stats_query = "
    SELECT 
        table_name,
        COUNT(*) as action_count,
        COUNT(DISTINCT user_name) as users_per_table,
        COUNT(DISTINCT action_type) as action_types_count,
        MAX(timestamp) as last_action
    FROM audit_trail
    WHERE table_name IS NOT NULL
";

// Add additional conditions if they exist
if (!empty($where_conditions)) {
    $table_where_conditions = ["table_name IS NOT NULL"];
    $table_where_conditions = array_merge($table_where_conditions, $where_conditions);
    $table_where_clause = "WHERE " . implode(" AND ", $table_where_conditions);
    $table_stats_query = str_replace("WHERE table_name IS NOT NULL", $table_where_clause, $table_stats_query);
}

$table_stats_query .= " GROUP BY table_name ORDER BY action_count DESC";

if (!empty($query_params)) {
    $table_stats_stmt = $conn->prepare($table_stats_query);
    if ($table_stats_stmt) {
        $table_stats_stmt->bind_param($param_types, ...$query_params);
        $table_stats_stmt->execute();
        $table_stats_result = $table_stats_stmt->get_result();
    } else {
        // Fallback query
        $table_stats_result = $conn->query("
            SELECT 
                table_name,
                COUNT(*) as action_count,
                COUNT(DISTINCT user_name) as users_per_table,
                COUNT(DISTINCT action_type) as action_types_count,
                MAX(timestamp) as last_action
            FROM audit_trail
            WHERE table_name IS NOT NULL
            GROUP BY table_name
            ORDER BY action_count DESC
        ");
    }
} else {
    $table_stats_result = $conn->query($table_stats_query);
}

// Get all tables for filter dropdown
$tables_query = "
    SELECT DISTINCT table_name 
    FROM audit_trail 
    WHERE table_name IS NOT NULL 
    ORDER BY table_name
";
$tables_result = $conn->query($tables_query);

// Get all action types for filter dropdown
$action_types_query = "
    SELECT DISTINCT action_type 
    FROM audit_trail 
    ORDER BY action_type
";
$action_types_result = $conn->query($action_types_query);
?>

<?php include('includes/pre-loader.php')?>

<div id="layout-wrapper">

<?php include('includes/topbar.php')?>

<div class="vertical-menu">

<?php include('includes/sidebar.php')?>
            
        </div>
    </div>

<div class="main-content">
        <div class="page-content">
           
            <div class="container-fluid">

<div class="row">
                    <div class="col-xl-2 col-md-4">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo $summary['total_actions'] ?? 0; ?></h4>
                                        <p class="text-muted mb-0">Total Actions</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-history font-size-24 text-primary"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-2 col-md-4">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo $summary['unique_users'] ?? 0; ?></h4>
                                        <p class="text-muted mb-0">Unique Users</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-account-group font-size-24 text-success"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-2 col-md-4">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo $summary['tables_affected'] ?? 0; ?></h4>
                                        <p class="text-muted mb-0">Tables Affected</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-table font-size-24 text-info"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-2 col-md-4">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo $summary['active_days'] ?? 0; ?></h4>
                                        <p class="text-muted mb-0">Active Days</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-calendar-clock font-size-24 text-warning"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-2 col-md-4">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h6 class="mb-0">
                                            <?php 
                                            if (isset($summary['first_action']) && $summary['first_action']) {
                                                echo date('d M', strtotime($summary['first_action']));
                                            } else {
                                                echo 'N/A';
                                            }
                                            ?>
                                        </h6>
                                        <p class="text-muted mb-0">First Action</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-arrow-up-drop-circle font-size-24 text-danger"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-2 col-md-4">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h6 class="mb-0">
                                            <?php 
                                            if (isset($summary['last_action']) && $summary['last_action']) {
                                                echo date('d M H:i', strtotime($summary['last_action']));
                                            } else {
                                                echo 'N/A';
                                            }
                                            ?>
                                        </h6>
                                        <p class="text-muted mb-0">Last Action</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-arrow-down-drop-circle font-size-24 text-secondary"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row">
                    <div class="col-12">
                        <div class="card">
                            <div class="card-body">
                                <h4 class="card-title mb-3">Filter Audit Trail</h4>
                                
                                <form method="GET" action="" class="row g-3">
                                    <div class="col-md-3">
                                        <label for="start_date" class="form-label">From Date</label>
                                        <input type="date" class="form-control" id="start_date" name="start_date" value="<?php echo htmlspecialchars($start_date); ?>">
                                    </div>
                                    
                                    <div class="col-md-3">
                                        <label for="end_date" class="form-label">To Date</label>
                                        <input type="date" class="form-control" id="end_date" name="end_date" value="<?php echo htmlspecialchars($end_date); ?>">
                                    </div>
                                    
                                    <div class="col-md-2">
                                        <label for="action_type" class="form-label">Action Type</label>
                                        <select class="form-select" id="action_type" name="action_type">
                                            <option value="">All Actions</option>
                                            <?php if ($action_types_result && $action_types_result->num_rows > 0): ?>
                                                <?php while ($action_type_row = $action_types_result->fetch_assoc()): ?>
                                                    <option value="<?php echo $action_type_row['action_type']; ?>" <?php echo $action_type === $action_type_row['action_type'] ? 'selected' : ''; ?>>
                                                        <?php echo $action_type_row['action_type']; ?>
                                                    </option>
                                                <?php endwhile; ?>
                                            <?php endif; ?>
                                        </select>
                                    </div>
                                    
                                    <div class="col-md-2">
                                        <label for="user_filter" class="form-label">User</label>
                                        <input type="text" class="form-control" id="user_filter" name="user_filter" 
                                               value="<?php echo htmlspecialchars($user_filter); ?>" 
                                               placeholder="Name or ID">
                                    </div>
                                    
                                    <div class="col-md-2">
                                        <label for="table_filter" class="form-label">Table</label>
                                        <select class="form-select" id="table_filter" name="table_filter">
                                            <option value="">All Tables</option>
                                            <?php if ($tables_result && $tables_result->num_rows > 0): ?>
                                                <?php while ($table_row = $tables_result->fetch_assoc()): ?>
                                                    <option value="<?php echo $table_row['table_name']; ?>" <?php echo $table_filter === $table_row['table_name'] ? 'selected' : ''; ?>>
                                                        <?php echo $table_row['table_name']; ?>
                                                    </option>
                                                <?php endwhile; ?>
                                            <?php endif; ?>
                                        </select>
                                    </div>
                                    
                                    <div class="col-md-2">
                                        <label for="record_id" class="form-label">Record ID</label>
                                        <input type="number" class="form-control" id="record_id" name="record_id" 
                                               value="<?php echo $record_id; ?>" 
                                               placeholder="Record ID" min="0">
                                    </div>
                                    
                                    <div class="col-md-12 mt-3">
                                        <button type="submit" class="btn btn-primary">
                                            <i class="mdi mdi-filter me-1"></i> Apply Filters
                                        </button>
                                        <a href="audit-trail.php" class="btn btn-outline-secondary ms-2">
                                            <i class="mdi mdi-refresh me-1"></i> Reset Filters
                                        </a>
                                        <button type="button" class="btn btn-outline-success ms-2" onclick="exportAuditTrail()">
                                            <i class="mdi mdi-file-excel me-1"></i> Export to Excel
                                        </button>
                                        <button type="button" class="btn btn-outline-info ms-2" onclick="printAuditTrail()">
                                            <i class="mdi mdi-printer me-1"></i> Print Report
                                        </button>
                                        <button type="button" class="btn btn-outline-danger ms-2" onclick="clearOldAuditTrail()">
                                            <i class="mdi mdi-delete me-1"></i> Clear Old Records
                                        </button>
                                    </div>
                                    
                                    <?php if (!empty($start_date) || !empty($end_date) || !empty($action_type) || !empty($user_filter) || !empty($table_filter) || $record_id > 0): ?>
                                        <div class="col-12 mt-2">
                                            <small class="text-muted">
                                                <i class="mdi mdi-filter me-1"></i> 
                                                <?php
                                                $filters = [];
                                                if (!empty($start_date)) $filters[] = "From: $start_date";
                                                if (!empty($end_date)) $filters[] = "To: $end_date";
                                                if (!empty($action_type)) $filters[] = "Action: $action_type";
                                                if (!empty($user_filter)) $filters[] = "User: $user_filter";
                                                if (!empty($table_filter)) $filters[] = "Table: $table_filter";
                                                if ($record_id > 0) $filters[] = "Record ID: $record_id";
                                                
                                                if (!empty($filters)) {
                                                    echo "Active filters: " . implode(' | ', $filters);
                                                }
                                                ?>
                                            </small>
                                        </div>
                                    <?php endif; ?>
                                </form>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row">
                    
                    <div class="col-lg-6">
                        <div class="card">
                            <div class="card-body">
                                <h4 class="card-title mb-4">Action Type Distribution</h4>
                                
                                <?php if ($action_stats_result && $action_stats_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-centered table-nowrap mb-0">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>Action Type</th>
                                                    <th>Count</th>
                                                    <th>Percentage</th>
                                                    <th>Unique Users</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php 
                                                $total_actions = $summary['total_actions'] ?? 1;
                                                while ($action_stat = $action_stats_result->fetch_assoc()): 
                                                    $percentage = ($action_stat['action_count'] / $total_actions) * 100;
                                                ?>
                                                    <tr>
                                                        <td>
                                                            <span class="badge <?php 
                                                                switch($action_stat['action_type']) {
                                                                    case 'CREATE': echo 'bg-success'; break;
                                                                    case 'UPDATE': echo 'bg-info'; break;
                                                                    case 'DELETE': echo 'bg-danger'; break;
                                                                    case 'LOGIN': echo 'bg-primary'; break;
                                                                    case 'LOGOUT': echo 'bg-secondary'; break;
                                                                    case 'VIEW': echo 'bg-warning'; break;
                                                                    default: echo 'bg-dark';
                                                                }
                                                            ?> me-1">
                                                                <?php echo $action_stat['action_type']; ?>
                                                            </span>
                                                        </td>
                                                        <td><?php echo $action_stat['action_count']; ?></td>
                                                        <td>
                                                            <div class="progress mt-1" style="height: 6px;">
                                                                <div class="progress-bar" role="progressbar" 
                                                                     style="width: <?php echo $percentage; ?>%" 
                                                                     aria-valuenow="<?php echo $percentage; ?>" 
                                                                     aria-valuemin="0" 
                                                                     aria-valuemax="100"></div>
                                                            </div>
                                                            <small class="text-muted"><?php echo number_format($percentage, 1); ?>%</small>
                                                        </td>
                                                        <td><?php echo $action_stat['users_per_action']; ?></td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-chart-bar display-4 text-muted"></i>
                                        <p class="text-muted mt-2">No action statistics available</p>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                    
                    
                    <div class="col-lg-6">
                        <div class="card">
                            <div class="card-body">
                                <h4 class="card-title mb-4">Top Active Users</h4>
                                
                                <?php if ($user_stats_result && $user_stats_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-centered table-nowrap mb-0">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>User</th>
                                                    <th>Actions</th>
                                                    <th>Active Days</th>
                                                    <th>Last Action</th>
                                                    <th>Action Types</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($user_stat = $user_stats_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td>
                                                            <strong><?php echo htmlspecialchars($user_stat['user_name'] ?: 'System'); ?></strong>
                                                        </td>
                                                        <td><?php echo $user_stat['action_count']; ?></td>
                                                        <td><?php echo $user_stat['active_days']; ?></td>
                                                        <td><?php echo date('d M H:i', strtotime($user_stat['last_action'])); ?></td>
                                                        <td>
                                                            <small class="text-muted"><?php echo htmlspecialchars($user_stat['action_types']); ?></small>
                                                        </td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-account-supervisor display-4 text-muted"></i>
                                        <p class="text-muted mt-2">No user activity data available</p>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row">
                    <div class="col-12">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex justify-content-between align-items-center mb-4">
                                    <h4 class="card-title">Audit Trail Log</h4>
                                    <div class="text-muted">
                                        Showing <?php echo min($total_records, 1000); ?> of <?php echo $total_records; ?> record(s)
                                    </div>
                                </div>
                                
                                <?php if ($total_records > 0): ?>
                                    <div class="table-responsive" id="auditTrailTable">
                                        <table class="table table-centered table-nowrap mb-0">
                                            <thead class="table-dark">
                                                <tr>
                                                    <th>ID</th>
                                                    <th>Timestamp</th>
                                                    <th>User</th>
                                                    <th>Action</th>
                                                    <th>Table</th>
                                                    <th>Record ID</th>
                                                    <th>Description</th>
                                                    <th>IP Address</th>
                                                    <th>Details</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($audit = $audit_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td>#<?php echo $audit['id']; ?></td>
                                                        <td>
                                                            <?php echo date('d M Y', strtotime($audit['timestamp'])); ?>
                                                            <br>
                                                            <small class="text-muted"><?php echo date('H:i:s', strtotime($audit['timestamp'])); ?></small>
                                                        </td>
                                                        <td>
                                                            <?php if (!empty($audit['user_name'])): ?>
                                                                <strong><?php echo htmlspecialchars($audit['user_name']); ?></strong>
                                                                <?php if (!empty($audit['user_id'])): ?>
                                                                    <br>
                                                                    <small class="text-muted">ID: <?php echo $audit['user_id']; ?></small>
                                                                <?php endif; ?>
                                                            <?php else: ?>
                                                                <span class="text-muted">System</span>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <span class="badge <?php echo $audit['action_class']; ?>">
                                                                <?php echo $audit['action_type']; ?>
                                                            </span>
                                                        </td>
                                                        <td>
                                                            <?php if (!empty($audit['table_name'])): ?>
                                                                <code><?php echo $audit['table_name']; ?></code>
                                                            <?php else: ?>
                                                                <span class="text-muted">N/A</span>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <?php if (!empty($audit['record_id'])): ?>
                                                                <span class="badge bg-light text-dark">#<?php echo $audit['record_id']; ?></span>
                                                            <?php else: ?>
                                                                <span class="text-muted">N/A</span>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <?php if (!empty($audit['description'])): ?>
                                                                <?php echo htmlspecialchars($audit['description']); ?>
                                                            <?php else: ?>
                                                                <span class="text-muted">No description</span>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <small class="text-muted"><?php echo htmlspecialchars($audit['ip_address']); ?></small>
                                                        </td>
                                                        <td>
                                                            <div class="d-flex gap-2">
                                                                <button type="button" class="btn btn-sm btn-outline-info" 
                                                                        onclick="viewAuditDetails(<?php echo $audit['id']; ?>)"
                                                                        title="View Details">
                                                                    <i class="mdi mdi-eye"></i>
                                                                </button>
                                                                <?php if (!empty($audit['old_data']) || !empty($audit['new_data'])): ?>
                                                                    <button type="button" class="btn btn-sm btn-outline-primary" 
                                                                            onclick="viewDataChanges(<?php echo $audit['id']; ?>)"
                                                                            title="View Changes">
                                                                        <i class="mdi mdi-compare"></i>
                                                                    </button>
                                                                <?php endif; ?>
                                                            </div>
                                                        </td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                    
                                    
                                    
                                <?php else: ?>
                                    <div class="text-center py-5">
                                        <i class="mdi mdi-history-off display-4 text-muted"></i>
                                        <h4 class="mt-3">No Audit Records Found</h4>
                                        <p class="text-muted">No audit trail records match the selected filters.</p>
                                        <p class="text-muted">The audit trail table has been created. Start using the system to see audit logs.</p>
                                        <button type="button" class="btn btn-primary mt-2" onclick="testAuditLog()">
                                            <i class="mdi mdi-test-tube me-1"></i> Test Audit Logging
                                        </button>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row mt-4">
                    <div class="col-12">
                        <div class="card">
                            <div class="card-body">
                                <h4 class="card-title mb-4">Table Activity Statistics</h4>
                                
                                <?php if ($table_stats_result && $table_stats_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-centered table-nowrap mb-0">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>Table Name</th>
                                                    <th>Actions</th>
                                                    <th>Users</th>
                                                    <th>Action Types</th>
                                                    <th>Last Action</th>
                                                    <th>Status</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($table_stat = $table_stats_result->fetch_assoc()): ?>
                                                    <?php 
                                                    $days_since_last = floor((time() - strtotime($table_stat['last_action'])) / (60 * 60 * 24));
                                                    $status_class = $days_since_last <= 1 ? 'success' : ($days_since_last <= 7 ? 'warning' : 'danger');
                                                    $status_text = $days_since_last == 0 ? 'Today' : ($days_since_last == 1 ? 'Yesterday' : $days_since_last . ' days ago');
                                                    ?>
                                                    <tr>
                                                        <td>
                                                            <code><?php echo $table_stat['table_name']; ?></code>
                                                        </td>
                                                        <td><?php echo $table_stat['action_count']; ?></td>
                                                        <td><?php echo $table_stat['users_per_table']; ?></td>
                                                        <td><?php echo $table_stat['action_types_count']; ?></td>
                                                        <td><?php echo date('d M H:i', strtotime($table_stat['last_action'])); ?></td>
                                                        <td>
                                                            <span class="badge bg-<?php echo $status_class; ?>">
                                                                <?php echo $status_text; ?>
                                                            </span>
                                                        </td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-database-off display-4 text-muted"></i>
                                        <p class="text-muted mt-2">No table activity data available</p>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row mt-4">
                    <div class="col-12">
                        <div class="card">
                            <div class="card-body">
                                <h4 class="card-title mb-3">Audit Trail Information</h4>
                                <div class="row">
                                    <div class="col-md-4">
                                        <div class="border rounded p-3 mb-3">
                                            <h5><i class="mdi mdi-information-outline text-primary me-2"></i>About Audit Trail</h5>
                                            <p class="mb-0">Audit trail provides a complete record of all system activities, including user actions, data changes, and access logs for compliance and security monitoring.</p>
                                        </div>
                                    </div>
                                    <div class="col-md-4">
                                        <div class="border rounded p-3 mb-3">
                                            <h5><i class="mdi mdi-shield-check text-success me-2"></i>Security Features</h5>
                                            <ul class="mb-0">
                                                <li>Track all user activities</li>
                                                <li>Monitor data changes</li>
                                                <li>IP address tracking</li>
                                                <li>Timestamp of all actions</li>
                                                <li>Before/after data comparison</li>
                                            </ul>
                                        </div>
                                    </div>
                                    <div class="col-md-4">
                                        <div class="border rounded p-3 mb-3">
                                            <h5><i class="mdi mdi-alert-circle text-warning me-2"></i>Compliance</h5>
                                            <ul class="mb-0">
                                                <li>Maintain records for 7 years</li>
                                                <li>HIPAA/GDPR compliant</li>
                                                <li>Non-repudiation proof</li>
                                                <li>Forensic analysis</li>
                                                <li>Regulatory reporting</li>
                                            </ul>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

</div>
            
        </div>

<?php include('includes/footer.php')?>
    </div>

</div>

<?php include('includes/rightbar.php')?>

<?php include('includes/scripts.php')?>

<div class="modal fade" id="auditDetailsModal" tabindex="-1" aria-hidden="true">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Audit Trail Details</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <div class="modal-body" id="auditDetailsContent">
                Loading...
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                <button type="button" class="btn btn-primary" onclick="printAuditDetails()">
                    <i class="mdi mdi-printer me-1"></i> Print Details
                </button>
            </div>
        </div>
    </div>
</div>

<div class="modal fade" id="dataChangesModal" tabindex="-1" aria-hidden="true">
    <div class="modal-dialog modal-xl">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Data Changes</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <div class="modal-body" id="dataChangesContent">
                Loading...
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
            </div>
        </div>
    </div>
</div>

<div class="modal fade" id="clearAuditModal" tabindex="-1" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Clear Old Audit Records</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <div class="modal-body">
                <div class="mb-3">
                    <label for="days_to_keep" class="form-label">Keep records from last (days):</label>
                    <input type="number" class="form-control" id="days_to_keep" value="365" min="30" max="3650">
                    <small class="text-muted">Records older than specified days will be permanently deleted.</small>
                </div>
                <div class="alert alert-warning">
                    <i class="mdi mdi-alert-circle-outline me-2"></i>
                    <strong>Warning:</strong> This action cannot be undone. All audit records older than the specified days will be permanently deleted.
                </div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                <button type="button" class="btn btn-danger" onclick="confirmClearAuditTrail()">Delete Old Records</button>
            </div>
        </div>
    </div>
</div>

// Quick date filters
    function setDateFilter(days) {
        const endDate = new Date();
        const startDate = new Date();
        startDate.setDate(startDate.getDate() - days);
        
        document.getElementById('start_date').value = startDate.toISOString().split('T')[0];
        document.getElementById('end_date').value = endDate.toISOString().split('T')[0];
        document.querySelector('form').submit();
    }

function setToday() {
        const today = new Date();
        document.getElementById('start_date').value = today.toISOString().split('T')[0];
        document.getElementById('end_date').value = today.toISOString().split('T')[0];
        document.querySelector('form').submit();
    }

// View audit details
    function viewAuditDetails(auditId) {
        // For now, show a simple alert since ajax file may not exist
        alert('Viewing details for audit ID: ' + auditId + '\n\nThis feature requires the ajax/get_audit_details.php file to be created.');
        
        // Uncomment when you have the ajax file:
        /*
        fetch(`ajax/get_audit_details.php?id=${auditId}`)
            .then(response => response.text())
            .then(html => {
                document.getElementById('auditDetailsContent').innerHTML = html;
                const modal = new bootstrap.Modal(document.getElementById('auditDetailsModal'));
                modal.show();
            })
            .catch(error => {
                console.error('Error:', error);
                document.getElementById('auditDetailsContent').innerHTML = `
                    <div class="alert alert-danger">
                        <i class="mdi mdi-alert-circle me-2"></i>
                        Error loading audit details. Please try again.
                    </div>
                `;
                const modal = new bootstrap.Modal(document.getElementById('auditDetailsModal'));
                modal.show();
            });
        */
    }

// View data changes
    function viewDataChanges(auditId) {
        // For now, show a simple alert
        alert('Viewing data changes for audit ID: ' + auditId + '\n\nThis feature requires the ajax/get_data_changes.php file to be created.');
        
        // Uncomment when you have the ajax file:
        /*
        fetch(`ajax/get_data_changes.php?id=${auditId}`)
            .then(response => response.text())
            .then(html => {
                document.getElementById('dataChangesContent').innerHTML = html;
                const modal = new bootstrap.Modal(document.getElementById('dataChangesModal'));
                modal.show();
            })
            .catch(error => {
                console.error('Error:', error);
                document.getElementById('dataChangesContent').innerHTML = `
                    <div class="alert alert-danger">
                        <i class="mdi mdi-alert-circle me-2"></i>
                        Error loading data changes. Please try again.
                    </div>
                `;
                const modal = new bootstrap.Modal(document.getElementById('dataChangesModal'));
                modal.show();
            });
        */
    }

// Print audit trail
    function printAuditTrail() {
        const printContent = document.getElementById('auditTrailTable');
        if (!printContent) {
            alert('No audit data to print!');
            return;
        }
        
        const printHTML = printContent.outerHTML;
        const originalContent = document.body.innerHTML;
        
        document.body.innerHTML = `
            <html>
                <head>
                    <title>Audit Trail Report - <?php echo date('d M Y'); ?></title>
                    <style>
                        body { font-family: Arial, sans-serif; margin: 20px; }
                        h2 { color: #333; border-bottom: 2px solid #007bff; padding-bottom: 10px; }
                        .report-header { 
                            background: #f8f9fa; 
                            padding: 20px; 
                            border-radius: 5px; 
                            margin-bottom: 20px;
                            border: 1px solid #dee2e6;
                        }
                        .report-header p { margin: 5px 0; }
                        table { width: 100%; border-collapse: collapse; margin-top: 20px; }
                        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
                        th { background-color: #343a40; color: white; }
                        .badge { padding: 3px 8px; border-radius: 4px; color: white; font-size: 12px; }
                        .bg-success { background-color: #28a745; }
                        .bg-primary { background-color: #007bff; }
                        .bg-info { background-color: #17a2b8; }
                        .bg-warning { background-color: #ffc107; color: #000; }
                        .bg-danger { background-color: #dc3545; }
                        .bg-secondary { background-color: #6c757d; }
                        @media print {
                            .no-print { display: none; }
                            @page { margin: 0.5cm; }
                            table { font-size: 10px; }
                        }
                        .print-footer { 
                            margin-top: 30px; 
                            font-size: 12px; 
                            color: #666;
                            border-top: 1px solid #ddd;
                            padding-top: 10px;
                        }
                    </style>
                </head>
                <body>
                    <div class="report-header">
                        <h2>Audit Trail Report</h2>
                        <p><strong>Report Period:</strong> <?php echo date('d M Y', strtotime($start_date)); ?> to <?php echo date('d M Y', strtotime($end_date)); ?></p>
                        <p><strong>Generated:</strong> <?php echo date('d M Y h:i A'); ?></p>
                        <p><strong>Total Records:</strong> <?php echo $total_records; ?></p>
                        <p><strong>Filters:</strong> <?php 
                            $filters = [];
                            if (!empty($action_type)) $filters[] = "Action: $action_type";
                            if (!empty($user_filter)) $filters[] = "User: $user_filter";
                            if (!empty($table_filter)) $filters[] = "Table: $table_filter";
                            if ($record_id > 0) $filters[] = "Record ID: $record_id";
                            echo !empty($filters) ? implode(' | ', $filters) : 'None';
                        ?></p>
                    </div>
                    
                    <div class="report-content">
                        ${printHTML}
                    </div>
                    
                    <div class="print-footer">
                        <p><strong>Confidential:</strong> This audit trail report contains sensitive system activity information.</p>
                        <p>Generated by Medical Store Management System | Page 1 of 1</p>
                    </div>
                    
                    <div class="no-print" style="margin-top: 20px; text-align: center;">
                        <button onclick="window.print()" class="btn btn-primary">Print Report</button>
                        <button onclick="window.close()" class="btn btn-secondary">Close Window</button>
                    </div>
                </body>
            </html>
        `;
        
        window.print();
        document.body.innerHTML = originalContent;
        location.reload();
    }

// Export to Excel
    function exportAuditTrail() {
        const table = document.querySelector('#auditTrailTable table');
        if (!table) {
            alert('No audit data to export!');
            return;
        }
        
        // Simple export to CSV
        let csv = [];
        const rows = table.querySelectorAll('tr');
        
        for (let i = 0; i < rows.length; i++) {
            const row = [], cols = rows[i].querySelectorAll('td, th');
            
            for (let j = 0; j < cols.length; j++) {
                // Clean up the data
                let data = cols[j].innerText.replace(/(\r\n|\n|\r)/gm, ' ').replace(/(\s\s)/gm, ' ');
                data = data.replace(/"/g, '""');
                row.push('"' + data + '"');
            }
            
            csv.push(row.join(','));
        }
        
        // Download CSV file
        const csvFile = new Blob([csv.join('\n')], { type: 'text/csv' });
        const downloadLink = document.createElement('a');
        downloadLink.download = 'audit_trail_<?php echo date('Y-m-d'); ?>.csv';
        downloadLink.href = window.URL.createObjectURL(csvFile);
        downloadLink.style.display = 'none';
        document.body.appendChild(downloadLink);
        downloadLink.click();
        document.body.removeChild(downloadLink);
    }

// Print audit details
    function printAuditDetails() {
        const detailsContent = document.getElementById('auditDetailsContent');
        const originalContent = document.body.innerHTML;
        
        document.body.innerHTML = `
            <html>
                <head>
                    <title>Audit Details - <?php echo date('d M Y'); ?></title>
                    <style>
                        body { font-family: Arial, sans-serif; margin: 20px; }
                        h2 { color: #333; border-bottom: 2px solid #007bff; padding-bottom: 10px; }
                        .details-box { background: #f8f9fa; padding: 20px; border-radius: 5px; margin: 20px 0; }
                        .section-title { color: #495057; margin-top: 20px; padding-bottom: 5px; border-bottom: 1px solid #dee2e6; }
                        @media print {
                            .no-print { display: none; }
                            @page { margin: 0.5cm; }
                        }
                    </style>
                </head>
                <body>
                    <h2>Audit Trail Details</h2>
                    <div class="details-box">
                        ${detailsContent.innerHTML}
                    </div>
                    <div class="no-print" style="margin-top: 20px; text-align: center;">
                        <button onclick="window.print()" class="btn btn-primary">Print Details</button>
                        <button onclick="window.close()" class="btn btn-secondary">Close</button>
                    </div>
                </body>
            </html>
        `;
        
        window.print();
        document.body.innerHTML = originalContent;
    }

// Test audit logging
    function testAuditLog() {
        if (confirm('This will create a test audit log entry. Continue?')) {
            // Simple test without ajax
            alert('Test audit log feature requires ajax/test_audit_log.php file.\n\nFor now, you can manually add test data to the audit_trail table.');
            
            // Uncomment when you have the ajax file:
            /*
            fetch('ajax/test_audit_log.php')
                .then(response => response.json())
                .then(data => {
                    if (data.success) {
                        alert('Test audit log created successfully!');
                        location.reload();
                    } else {
                        alert('Error creating test audit log: ' + data.message);
                    }
                })
                .catch(error => {
                    console.error('Error:', error);
                    alert('Error creating test audit log');
                });
            */
        }
    }

// Clear old audit trail
    function clearOldAuditTrail() {
        const modal = new bootstrap.Modal(document.getElementById('clearAuditModal'));
        modal.show();
    }

// Date validation
    document.getElementById('start_date').addEventListener('change', function() {
        const endDate = document.getElementById('end_date');
        if (this.value && endDate.value && this.value > endDate.value) {
            alert('Start date cannot be after end date!');
            this.value = '';
        }
    });

document.getElementById('end_date').addEventListener('change', function() {
        const startDate = document.getElementById('start_date');
        if (this.value && startDate.value && this.value < startDate.value) {
            alert('End date cannot be before start date!');
            this.value = '';
        }
    });

// Auto refresh every 5 minutes if on today's view
    if (document.getElementById('start_date').value === '<?php echo date("Y-m-d"); ?>' && 
        document.getElementById('end_date').value === '<?php echo date("Y-m-d"); ?>') {
        setInterval(() => {
            if (!document.hidden) {
                location.reload();
            }
        }, 300000); // 5 minutes
    }
</script>

</body>

</html>
<?php
// Close database connections
if (isset($stmt)) {
    $stmt->close();
}
if (isset($summary_stmt)) {
    $summary_stmt->close();
}
if (isset($action_stats_stmt)) {
    $action_stats_stmt->close();
}
if (isset($user_stats_stmt)) {
    $user_stats_stmt->close();
}
if (isset($table_stats_stmt)) {
    $table_stats_stmt->close();
}
if (isset($conn)) {
    $conn->close();
}
?>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: audit-trail.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation