OXIESEC PANEL

Name	Size	Modified	Perms
📁 ..	-	03/31/2025 06:36:42 AM	rwxr-xr-x
📁 PHPMailer	-	03/26/2025 04:07:42 AM	rwxr-xr-x
📄 addPro.php	800 bytes	03/26/2025 03:48:03 AM	rw-r--r--
📄 dash.php	0 bytes	03/26/2025 03:48:03 AM	rw-r--r--
📄 dashboard.php	8.86 KB	03/26/2025 03:48:03 AM	rw-r--r--
📄 db_connect.php	292 bytes	03/26/2025 03:48:03 AM	rw-r--r--
📄 mail.php	2.01 KB	03/26/2025 03:48:03 AM	rw-r--r--
📄 proUsers.php	8.8 KB	03/26/2025 03:48:03 AM	rw-r--r--
📄 send_otp.php	1.38 KB	03/26/2025 03:48:03 AM	rw-r--r--
📄 userActivity.php	9.79 KB	03/26/2025 03:48:03 AM	rw-r--r--
📄 verification.php	4.71 KB	03/26/2025 03:48:03 AM	rw-r--r--
📄 verify_otp.php	644 bytes	03/26/2025 03:48:03 AM	rw-r--r--

Editing: userActivity.php

<?php
include 'assets/php/db_config.php'; // Include your DB configuration

// Step 2: Handle form submission and get the selected date
$date = isset($_POST['date']) ? $_POST['date'] : date('Y-m-d'); // Default to today's date if no date is selected

// Define SQL queries first so they're available throughout the script
// SQL query to fetch the social posts data
$sql = "SELECT 
        u.id,
        u.full_name,
        DATE(r.postedOn) AS post_date, 
        COUNT(*) AS number_of_posts  
    FROM 
        reader.reader_stream r 
    JOIN 
        cream.user u ON r.userId = u.id  
    WHERE 
        DATE(r.postedOn) = ? 
        AND
        r.deleteFlag=0
    GROUP BY 
        u.id, 
        u.full_name, 
        DATE(r.postedOn) 
    ORDER BY  
        number_of_posts DESC; 
";

// Query to fetch articles count from user_collection table
$articles_sql = "SELECT 
        u.id,
        COUNT(*) AS number_of_articles
    FROM 
        cream.user_collection uc
    JOIN 
        cream.user u ON uc.user_id = u.id
    WHERE 
        DATE(uc.date_added) = ?
    GROUP BY 
        u.id
";

// Function to get user data - reused in both PDF and HTML sections
function getUsersData($readerdb, $date, $sql, $articles_sql) {
    try {
        // Prepare the query for social posts
        $stmt = $readerdb->prepare($sql);
        $stmt->bind_param('s', $date);
        $stmt->execute();
        $result = $stmt->get_result();
        
        // Store results in an associative array for easy access
        $users_data = [];
        while ($row = $result->fetch_assoc()) {
            $users_data[$row['id']] = [
                'id' => $row['id'],
                'full_name' => $row['full_name'],
                'number_of_posts' => $row['number_of_posts'],
                'number_of_articles' => 0 // Default to 0
            ];
        }
        
        // Prepare the query for articles count
        $articles_stmt = $readerdb->prepare($articles_sql);
        $articles_stmt->bind_param('s', $date);
        $articles_stmt->execute();
        $articles_result = $articles_stmt->get_result();
        
        // Update the users_data array with article counts
        while ($article_row = $articles_result->fetch_assoc()) {
            $user_id = $article_row['id'];
            
            // If user exists in our array, update article count
            if (isset($users_data[$user_id])) {
                $users_data[$user_id]['number_of_articles'] = $article_row['number_of_articles'];
            } else {
                // User has articles but no social posts, add them to the array
                $user_stmt = $readerdb->prepare("SELECT full_name FROM cream.user WHERE id = ?");
                $user_stmt->bind_param('i', $user_id);
                $user_stmt->execute();
                $user_result = $user_stmt->get_result();
                $user_data = $user_result->fetch_assoc();
                
                $users_data[$user_id] = [
                    'id' => $user_id,
                    'full_name' => $user_data['full_name'],
                    'number_of_posts' => 0, // No social posts
                    'number_of_articles' => $article_row['number_of_articles']
                ];
            }
        }
        
        // Sort by number of posts DESC
        usort($users_data, function($a, $b) {
            return $b['number_of_posts'] - $a['number_of_posts'];
        });
        
        return $users_data;
        
    } catch (mysqli_sql_exception $e) {
        echo "Error in SQL query: " . $e->getMessage();
        exit;
    }
}

// Check if PDF download is requested
if(isset($_POST['download_pdf'])) {
    // Alternative approach: Use TCPDF instead of FPDF for better Unicode support
    require_once('tcpdf/tcpdf.php');
    
    // Get the data for PDF
    $users_data = getUsersData($readerdb, $date, $sql, $articles_sql);
    
    // Create new TCPDF object
    $pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
    
    // Set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('User Analytics');
    $pdf->SetTitle('User Activity Report');
    $pdf->SetSubject('User Analytics Report');
    
    // Set default header data
    $pdf->SetHeaderData('', 0, 'User Activity Analytics', 'Date: ' . $date);
    
    // Set header and footer fonts
    $pdf->setHeaderFont(Array('dejavusans', '', 12));
    $pdf->setFooterFont(Array('dejavusans', '', 10));
    
    // Set default monospaced font
    $pdf->SetDefaultMonospacedFont('courier');
    
    // Set margins
    $pdf->SetMargins(15, 27, 15);
    $pdf->SetHeaderMargin(5);
    $pdf->SetFooterMargin(10);
    
    // Set auto page breaks
    $pdf->SetAutoPageBreak(TRUE, 25);
    
    // Set image scale factor
    $pdf->setImageScale(1.25);
    
    // Set font
    $pdf->SetFont('dejavusans', '', 10);
    
    // Add a page
    $pdf->AddPage();
    
    // Create the table content
    $html = '<table border="1" cellpadding="5">
        <thead>
            <tr style="background-color:#f2f2f2;">
                <th><b>User ID</b></th>
                <th><b>Full Name</b></th>
                <th><b>Social Posts</b></th>
                <th><b>Articles</b></th>
            </tr>
        </thead>
        <tbody>';
    
    foreach($users_data as $user) {
        $html .= '<tr>
            <td style="text-align:center;">' . $user['id'] . '</td>
            <td>' . $user['full_name'] . '</td>
            <td style="text-align:center;">' . $user['number_of_posts'] . '</td>
            <td style="text-align:center;">' . $user['number_of_articles'] . '</td>
        </tr>';
    }
    
    $html .= '</tbody></table>';
    
    // Print the HTML table
    $pdf->writeHTML($html, true, false, true, false, '');
    
    // Close and output PDF document
    $pdf_filename = 'user_activity_' . $date . '.pdf';
    $pdf->Output($pdf_filename, 'D');
    exit;
}

// Get data for HTML display
$users_data = getUsersData($readerdb, $date, $sql, $articles_sql);

?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>User Activity Analytics</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
        }
        
        .container {
            max-width: 1200px;
            margin: 0 auto;
        }
        
        .header {
            text-align: center;
            margin-bottom: 20px;
        }
        
        .form-container {
            display: flex;
            justify-content: center;
            margin-bottom: 20px;
            gap: 10px;
        }
        
        .date-form {
            display: inline-block;
            margin-right: 10px;
        }
        
        .pdf-form {
            display: inline-block;
        }
        
        .btn {
            padding: 8px 15px;
            background-color: #4CAF50;
            color: white;
            border: none;
            border-radius: 4px;
            cursor: pointer;
        }
        
        .btn:hover {
            background-color: #45a049;
        }
        
        .btn-pdf {
            background-color: #f44336;
        }
        
        .btn-pdf:hover {
            background-color: #d32f2f;
        }
        
        table {
            width: 100%;
            margin: 20px auto;
            border-collapse: collapse;
        }

th, td {
            padding: 10px;
            text-align: center;
            border: 1px solid #ddd;
        }

th {
            background-color: #f2f2f2;
        }

tr:nth-child(even) {
            background-color: #f9f9f9;
        }
    </style>
</head>

<body>
    <div class="container">
        <div class="header">
            <h1>User Activity Analytics</h1>
        </div>

<div class="form-container">
            
            <form class="date-form" method="POST" action="userActivity.php">
                <label for="date">Select Date: </label>
                <input type="date" id="date" name="date" value="<?php echo htmlspecialchars($date); ?>" required>
                <input type="submit" value="Submit" class="btn">
            </form>

</div>

<table>
            <thead>
                <tr>
                    <th>User ID</th>
                    <th>Full Name</th>
                    <th>Social Posts</th>
                    <th>Collections</th>
                </tr>
            </thead>
            <tbody>
                <?php
                // Loop through the results and display them
                foreach ($users_data as $user) {
                    echo "<tr>";
                    echo "<td>" . htmlspecialchars($user['id']) . "</td>";
                    echo "<td>" . htmlspecialchars($user['full_name']) . "</td>";
                    echo "<td>" . htmlspecialchars($user['number_of_posts']) . "</td>";
                    echo "<td>" . htmlspecialchars($user['number_of_articles']) . "</td>";
                    echo "</tr>";
                }
                ?>
            </tbody>
        </table>
    </div>
</body>
</html>