AutoShell

dfhdfhhdfdfhdhfhfdhdfdhfdhfdfhhdfdfhdhfhdf AutoShell

?? Editing: fin_report.php

<?php
session_start();
include('includes/dbconnection.php');

if (strlen($_SESSION['aid']) == 0) {
    header('location:logout.php');
    exit();
}

// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Fetch total pending payments
$total_pending_query = "SELECT SUM(amount_paid) AS total_pending FROM payments WHERE status = 'Pending'";
$result_pending = $con->query($total_pending_query);
$total_pending = $result_pending->fetch_assoc()['total_pending'];

// Fetch total approved payments
$total_approved_query = "SELECT SUM(amount_paid) AS total_approved FROM payments WHERE status = 'Approved'";
$result_approved = $con->query($total_approved_query);
$total_approved = $result_approved->fetch_assoc()['total_approved'];

// Fetch available campuses and sessions
$campuses_query = "SELECT name FROM campuses";
$campuses_result = $con->query($campuses_query);

$sessions_query = "SELECT DISTINCT Session FROM tbladmapplications";
$sessions_result = $con->query($sessions_query);

// Handle filter form submission
$selected_campus = isset($_GET['campus']) ? $_GET['campus'] : '';
$selected_session = isset($_GET['session']) ? $_GET['session'] : '';
$selected_purpose = isset($_GET['purpose']) ? $_GET['purpose'] : '';

// Prepare the financial report query
$financial_report_query = "
    SELECT 
        tbluser.Campus AS campus_name, 
        SUM(tbluser.Total_Payment) AS total_payment, 
        SUM(tbluser.School_Fee) AS total_fee,
        (SUM(tbluser.School_Fee) - SUM(tbluser.Total_Payment)) AS outstanding_payment
    FROM tbluser
    JOIN tbladmapplications ON tbluser.ID = tbladmapplications.UserId
    WHERE 1=1";

// Add filters dynamically based on selected filters
if ($selected_campus != '') {
    $financial_report_query .= " AND tbluser.Campus = ?";
}
if ($selected_session != '') {
    $financial_report_query .= " AND tbladmapplications.Session = ?";
}
if ($selected_purpose != '') {
    $financial_report_query .= " AND tbluser.Purpose = ?";
}

$financial_report_query .= " GROUP BY tbluser.Campus";

// Prepare the statement
$stmt = $con->prepare($financial_report_query);
if (!$stmt) {
    die("Prepare failed: (" . $con->errno . ") " . $con->error);
}

// Bind parameters dynamically
$types = '';
$params = [];
if ($selected_campus != '') {
    $types .= 's'; // string
    $params[] = $selected_campus;
}
if ($selected_session != '') {
    $types .= 's'; // string
    $params[] = $selected_session;
}
if ($selected_purpose != '') {
    $types .= 's'; // string
    $params[] = $selected_purpose;
}

if ($params) {
    $stmt->bind_param($types, ...$params);
}

// Execute the statement
$stmt->execute();
$result = $stmt->get_result();

// Prepare total sums for all campuses
$total_sum = 0;
$total_outstanding = 0;

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Financial Report</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
    <style>
        body {
            background-color: #f8f9fa;
        }
        .card {
            margin-bottom: 20px;
        }
        .filter-form {
            margin-bottom: 30px;
        }
        .download-btns {
            margin-top: 20px;
            text-align: center;
        }
        .download-btns button {
            margin-right: 10px;
        }
        .table-responsive {
            margin-top: 20px;
        }
        @media (max-width: 768px) {
            .download-btns {
                display: flex;
                justify-content: center;
            }
        }
    </style>
</head>
<body>

<div class="container mt-5">
    <h1 class="mb-4 text-center">Financial Report</h1>

<div class="row">
        <div class="col-md-6">
            <div class="card text-white bg-warning">
                <div class="card-body text-center">
                    <h5 class="card-title">Total Pending Payments</h5>
                    <p class="card-text display-4"><?php echo number_format($total_pending, 2); ?> NGN</p>
                </div>
            </div>
        </div>
        <div class="col-md-6">
            <div class="card text-white bg-success">
                <div class="card-body text-center">
                    <h5 class="card-title">Total Approved Payments</h5>
                    <p class="card-text display-4"><?php echo number_format($total_approved, 2); ?> NGN</p>
                </div>
            </div>
        </div>
    </div>

<form class="filter-form" method="GET" action="">
        <div class="row">
            <div class="col-md-4 mb-3">
                <select name="campus" class="form-control">
                    <option value="">Select Campus</option>
                    <?php while ($campus = $campuses_result->fetch_assoc()): ?>
                        <option value="<?php echo $campus['name']; ?>" <?php echo ($selected_campus == $campus['name']) ? 'selected' : ''; ?>>
                            <?php echo $campus['name']; ?>
                        </option>
                    <?php endwhile; ?>
                </select>
            </div>
            <div class="col-md-4 mb-3">
                <select name="session" class="form-control">
                    <option value="">Select Session</option>
                    <?php while ($session = $sessions_result->fetch_assoc()): ?>
                        <option value="<?php echo $session['Session']; ?>" <?php echo ($selected_session == $session['Session']) ? 'selected' : ''; ?>>
                            <?php echo $session['Session']; ?>
                        </option>
                    <?php endwhile; ?>
                </select>
            </div>
            <div class="col-md-4 mb-3">
                <select name="purpose" class="form-control">
                    <option value="">Select Purpose</option>
                    <option value="School Fee" <?php echo ($selected_purpose == 'School Fee') ? 'selected' : ''; ?>>School Fee</option>
                    <option value="Lecture Note" <?php echo ($selected_purpose == 'Lecture Note') ? 'selected' : ''; ?>>Lecture Note</option>
                    <option value="Development Levy" <?php echo ($selected_purpose == 'Development Levy') ? 'selected' : ''; ?>>Development Levy</option>
                    
                </select>
            </div>
            <div class="col-md-4 mb-3">
                <button type="submit" class="btn btn-primary btn-block">Filter</button>
            </div>
        </div>
    </form>

<div class="table-responsive">
        <table class="table table-striped">
            <thead class="thead-dark">
            <tr>
                <th>Campus</th>
                <th>Total Payment (NGN)</th>
                <th>Outstanding Payment (NGN)</th>
            </tr>
            </thead>
            <tbody>
            <?php if ($result && $result->num_rows > 0): ?>
                <?php while ($row = $result->fetch_assoc()): ?>
                    <tr>
                        <td><?php echo $row['campus_name']; ?></td>
                        <td><?php echo number_format($row['total_payment'], 2); ?></td>
                        <td><?php echo number_format($row['outstanding_payment'], 2); ?></td>
                    </tr>
                    <?php
                    $total_sum += $row['total_payment'];
                    $total_outstanding += $row['outstanding_payment'];
                    ?>
                <?php endwhile; ?>
            <?php else: ?>
                <tr>
                    <td colspan="3" class="text-center">No data found</td>
                </tr>
            <?php endif; ?>
            </tbody>
        </table>
    </div>

<div class="mt-3 text-center">
        <h5>Total Payment: <strong><?php echo number_format($total_sum, 2); ?> NGN</strong></h5>
        <h5>Total Outstanding: <strong><?php echo number_format($total_outstanding, 2); ?> NGN</strong></h5>
    </div>

<div class="download-btns">
        <button class="btn btn-secondary" onclick="downloadCSV()">Download CSV</button>
        <button class="btn btn-secondary" onclick="window.print()">Print</button>
    </div>

</div>

<script>
    function downloadCSV() {
        const csvData = [];
        const rows = document.querySelectorAll("table tr");

rows.forEach(row => {
            const rowData = [];
            const cols = row.querySelectorAll("td, th");
            cols.forEach(col => {
                rowData.push(col.innerText);
            });
            csvData.push(rowData.join(","));
        });

const csvFile = new Blob([csvData.join("\n")], { type: "text/csv" });
        const downloadLink = document.createElement("a");
        downloadLink.href = URL.createObjectURL(csvFile);
        downloadLink.download = "financial_report.csv";
        downloadLink.click();
    }
</script>

</body>
</html>

<?php
$stmt->close();
$con->close();
?>

Path:

home / rcmewpjy / public_html / tmportal / admin /

?? Editing: fin_report.php

Path:

?? Upload