📁 File Manager
Editing:
/home/wvl7mof7402e/www/remindyourdata.live/admin/balance_sheet.php
<?php session_start(); include "dbconnect.php"; // Database Connection // Define the number of records per page $records_per_page = 10; // Get the current page number from the URL, default to 1 if not set $page_number = isset($_GET['page']) ? $_GET['page'] : 1; $start_from = ($page_number - 1) * $records_per_page; // Fetch total client payments from the database $sql_client_payments = "SELECT SUM(total_payment) AS total_received FROM clientspayment"; $result_client_payments = $con->query($sql_client_payments); $total_received = $result_client_payments->fetch_assoc()['total_received'] ?? 0; // Fetch total expenses from the database $sql_expenses = "SELECT SUM(amount) AS total_expenses FROM expenses"; $result_expenses = $con->query($sql_expenses); $total_expenses = $result_expenses->fetch_assoc()['total_expenses'] ?? 0; // Calculate net balance (Profit or Loss) $net_balance = $total_received - $total_expenses; // Fetch all client payment records with pagination $sql_all_client_payments = "SELECT * FROM clientspayment ORDER BY payment_date DESC LIMIT $start_from, $records_per_page"; $result_all_client_payments = $con->query($sql_all_client_payments); // Fetch all expense records with pagination $sql_all_expenses = "SELECT * FROM expenses ORDER BY date DESC LIMIT $start_from, $records_per_page"; $result_all_expenses = $con->query($sql_all_expenses); // Get total number of records for client payments and expenses $total_records_client_payments = $con->query("SELECT COUNT(*) FROM clientspayment")->fetch_row()[0]; $total_records_expenses = $con->query("SELECT COUNT(*) FROM expenses")->fetch_row()[0]; // Calculate total pages for pagination $total_pages_client_payments = ceil($total_records_client_payments / $records_per_page); $total_pages_expenses = ceil($total_records_expenses / $records_per_page); ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8" /> <meta name="viewport" content="width=device-width, initial-scale=1" /> <title>Financial Balance Sheet</title> <!-- Bootstrap CSS --> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet" /> <!-- Bootstrap Icons --> <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.5/font/bootstrap-icons.css" rel="stylesheet" /> <!-- FileSaver & XLSX --> <script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.0/xlsx.full.min.js"></script> <style> body { font-family: 'Arial', sans-serif; background: linear-gradient(135deg, #0066cc, #00aaff); color: #222; min-height: 100vh; margin: 0; padding-top: 70px; } .container { background: #fff; border-radius: 12px; padding: 30px 25px; box-shadow: 0 0 25px rgba(0,0,0,0.1); max-width: 1200px; } h3, h4 { color: #007bff; font-weight: 600; text-align: center; margin-bottom: 1.5rem; } .summary-box { background-color: #f0f8ff; border-radius: 10px; padding: 15px 25px; box-shadow: inset 0 0 10px #d0e9ff; margin-bottom: 2rem; text-align: center; } .summary-box h5 { font-weight: 600; color: #004085; } table { width: 100%; border-collapse: separate; border-spacing: 0 10px; margin-bottom: 1.8rem; } thead tr th { background-color: #007bff; color: #fff; padding: 10px 12px; font-weight: 600; vertical-align: middle; border-radius: 6px; text-align: center; } tbody tr { background: #f9f9f9; box-shadow: 0 2px 5px rgba(0,0,0,0.05); border-radius: 6px; transition: background-color 0.3s ease; } tbody tr:hover { background-color: #e6f0ff; } tbody tr td { padding: 10px 12px; vertical-align: middle; text-align: center; border: none; font-size: 0.9rem; color: #333; } /* Pagination Buttons */ .pagination { display: flex; justify-content: center; gap: 0.5rem; margin-bottom: 2rem; flex-wrap: wrap; } .btn-rounded { border-radius: 25px; min-width: 100px; } /* Logout button */ .logout-btn { position: fixed; top: 15px; right: 20px; background-color: #dc3545; color: #fff; padding: 10px 18px; border-radius: 8px; font-weight: 600; text-decoration: none; box-shadow: 0 3px 8px rgba(220,53,69,0.6); transition: background-color 0.3s ease; z-index: 999; display: flex; align-items: center; gap: 8px; } .logout-btn:hover { background-color: #b02a37; text-decoration: none; color: #fff; } /* Excel buttons styling */ .btn-excel { background-color: #28a745; border-color: #28a745; color: white; font-weight: 600; border-radius: 8px; display: flex; align-items: center; gap: 8px; padding: 8px 16px; margin-bottom: 1rem; user-select: none; cursor: pointer; transition: background-color 0.3s ease; } .btn-excel:hover { background-color: #1e7e34; border-color: #1c7430; color: white; text-decoration: none; } /* Responsive tweaks */ @media (max-width: 768px) { tbody tr td, thead tr th { font-size: 0.8rem; padding: 8px 6px; } .btn-excel { width: 100%; justify-content: center; } } @media (max-width: 400px) { .logout-btn { padding: 8px 12px; font-size: 0.85rem; gap: 4px; } } </style> </head> <body> <!-- Logout Button --> <a href="logout.php" class="logout-btn"><i class="bi bi-box-arrow-right"></i> Logout</a> <div class="container"> <h3><i class="bi bi-file-earmark-spreadsheet-fill"></i> Financial Balance Sheet</h3> <!-- Financial Summary --> <div class="row mb-4 summary-box"> <div class="col-12 col-md-6 mb-3 mb-md-0"> <h5><i class="bi bi-cash-stack"></i> Total Client Payments Received</h5> <p class="fs-4 fw-bold text-success">₹<?= number_format($total_received, 2) ?></p> </div> <div class="col-12 col-md-6"> <h5><i class="bi bi-receipt-cutoff"></i> Total Expenses</h5> <p class="fs-4 fw-bold text-danger">₹<?= number_format($total_expenses, 2) ?></p> </div> </div> <!-- Net Balance --> <div class="row mb-4 summary-box"> <div class="col-12 text-center"> <h4><i class="bi bi-calculator-fill"></i> Net Balance</h4> <p class="fs-3 fw-bold <?= $net_balance >= 0 ? 'text-success' : 'text-danger' ?>"> ₹<?= number_format($net_balance, 2) ?> <?= $net_balance >= 0 ? '<i class="bi bi-arrow-up-circle-fill"></i> Profit' : '<i class="bi bi-arrow-down-circle-fill"></i> Loss' ?> </p> </div> </div> <hr /> <!-- Client Payment Records --> <div class="d-flex justify-content-between align-items-center"> <h4><i class="bi bi-people-fill"></i> Client Payment Records</h4> <button class="btn-excel" onClick="downloadExcelClientPayments()"> <i class="bi bi-file-earmark-excel-fill"></i> Download Client Payments as Excel </button> </div> <div class="table-responsive"> <table class="table align-middle" id="clientPaymentsTable" > <thead> <tr> <th><i class="bi bi-person-badge"></i> Applicant ID</th> <th><i class="bi bi-currency-rupee"></i> Total Payment</th> <th><i class="bi bi-cash-stack"></i> Paid Amount</th> <th><i class="bi bi-clock-history"></i> Pending Amount</th> <th><i class="bi bi-calendar-event"></i> Payment Date</th> <th><i class="bi bi-receipt"></i> Installment No.</th> <th><i class="bi bi-calendar-check"></i> Installment Due Date</th> <th><i class="bi bi-calendar3"></i> Due Date</th> <th><i class="bi bi-chat-dots"></i> Payment Message</th> <th><i class="bi bi-clock"></i> Payment Time</th> </tr> </thead> <tbody> <?php while ($row = $result_all_client_payments->fetch_assoc()) { ?> <tr> <td><?= htmlspecialchars($row['applicant_id']) ?></td> <td>₹<?= number_format($row['total_payment'], 2) ?></td> <td>₹<?= number_format($row['paid_amount'], 2) ?></td> <td>₹<?= number_format($row['pending_amount'], 2) ?></td> <td><?= htmlspecialchars($row['payment_date']) ?></td> <td><?= htmlspecialchars($row['installment_number']) ?></td> <td><?= htmlspecialchars($row['installment_due_date']) ?></td> <td><?= htmlspecialchars($row['due_date']) ?></td> <td><?= htmlspecialchars($row['payment_message']) ?></td> <td><?= htmlspecialchars($row['payment_time']) ?></td> </tr> <?php } ?> </tbody> </table> </div> <!-- Pagination for Client Payments --> <div class="pagination mb-5"> <?php if ($page_number > 1) { ?> <a href="?page=<?= $page_number - 1 ?>" class="btn btn-primary btn-rounded"><i class="bi bi-arrow-left"></i> Previous</a> <?php } ?> <?php if ($page_number < $total_pages_client_payments) { ?> <a href="?page=<?= $page_number + 1 ?>" class="btn btn-primary btn-rounded">Next <i class="bi bi-arrow-right"></i></a> <?php } ?> </div> <hr /> <!-- Expense Records --> <div class="d-flex justify-content-between align-items-center"> <h4><i class="bi bi-wallet-fill"></i> Expense Records</h4> <button class="btn-excel" onClick="downloadExcelExpenses()"> <i class="bi bi-file-earmark-excel-fill"></i> Download Expenses as Excel </button> </div> <div class="table-responsive"> <table class="table align-middle" id="expensesTable"> <thead> <tr> <th><i class="bi bi-tags-fill"></i> Category</th> <th><i class="bi bi-currency-rupee"></i> Amount</th> <th><i class="bi bi-calendar-date"></i> Date</th> <th><i class="bi bi-card-text"></i> Description</th> </tr> </thead> <tbody> <?php while ($row = $result_all_expenses->fetch_assoc()) { ?> <tr> <td><?= htmlspecialchars($row['expense_category']) ?></td> <td>₹<?= number_format($row['amount'], 2) ?></td> <td><?= htmlspecialchars($row['date']) ?></td> <td><?= htmlspecialchars($row['description']) ?></td> </tr> <?php } ?> </tbody> </table> </div> <!-- Pagination for Expenses --> <div class="pagination"> <?php if ($page_number > 1) { ?> <a href="?page=<?= $page_number - 1 ?>" class="btn btn-primary btn-rounded"><i class="bi bi-arrow-left"></i> Previous</a> <?php } ?> <?php if ($page_number < $total_pages_expenses) { ?> <a href="?page=<?= $page_number + 1 ?>" class="btn btn-primary btn-rounded">Next <i class="bi bi-arrow-right"></i></a> <?php } ?> </div> </div> <script> // Excel download for Client Payments table function downloadExcelClientPayments() { var table = document.getElementById('clientPaymentsTable'); var wb = XLSX.utils.table_to_book(table, {sheet:"Client Payments"}); var wbout = XLSX.write(wb, {bookType:'xlsx', type: 'binary'}); function s2ab(s) { var buf = new ArrayBuffer(s.length); var view = new Uint8Array(buf); for (var i=0; i<s.length; i++) view[i] = s.charCodeAt(i) & 0xFF; return buf; } saveAs(new Blob([s2ab(wbout)], {type:"application/octet-stream"}), 'ClientPayments.xlsx'); } // Excel download for Expenses table function downloadExcelExpenses() { var table = document.getElementById('expensesTable'); var wb = XLSX.utils.table_to_book(table, {sheet:"Expenses"}); var wbout = XLSX.write(wb, {bookType:'xlsx', type: 'binary'}); function s2ab(s) { var buf = new ArrayBuffer(s.length); var view = new Uint8Array(buf); for (var i=0; i<s.length; i++) view[i] = s.charCodeAt(i) & 0xFF; return buf; } saveAs(new Blob([s2ab(wbout)], {type:"application/octet-stream"}), 'Expenses.xlsx'); } </script> <!-- Bootstrap JS Bundle --> <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script> </body> </html>
Save
Cancel