i want you to fix this generate_report.php (<?php
session_start();
require_once '../db_connect.php';
require_once '../assets/libraries/tcpdf/tcpdf.php';

if (!isset($_SESSION['accountant_id'])) {
    header("Location: login.php");
    exit();
}

// Initialize report variables
$report_title = "";
$total_revenue = 0;
$total_salaries = 0;
$error_message = "";
$profit_loss = 0;

// Handle form submission
if ($_SERVER["REQUEST_METHOD"] == "POST") {

    // Data sanitization
    $report_type = $_POST['report_type'] ?? '';
    $year = $_POST['year'] ?? '';
    $month = $_POST['month'] ?? '';
    $quarter = $_POST['quarter'] ?? '';
    $start_date = '';
    $end_date = '';

    // Validate year
    if (!preg_match('/^\d{4}$/', $year)) {
        $error_message = "Please enter a valid year.";
    } else {
        if ($report_type == 'monthly' && $month) {
            $month = str_pad($month, 2, '0', STR_PAD_LEFT);
            $start_date = "$year-$month-01";
            $end_date = date("Y-m-t", strtotime($start_date));
            $report_title = "Financial Report for " . date("F Y", strtotime($start_date));
        } elseif ($report_type == 'quarterly' && $quarter) {
            switch ($quarter) {
                case '1':
                    $start_date = "$year-01-01";
                    $end_date = "$year-03-31";
                    $report_title = "Financial Report for Q1 $year";
                    break;
                case '2':
                    $start_date = "$year-04-01";
                    $end_date = "$year-06-30";
                    $report_title = "Financial Report for Q2 $year";
                    break;
                case '3':
                    $start_date = "$year-07-01";
                    $end_date = "$year-09-30";
                    $report_title = "Financial Report for Q3 $year";
                    break;
                case '4':
                    $start_date = "$year-10-01";
                    $end_date = "$year-12-31";
                    $report_title = "Financial Report for Q4 $year";
                    break;
                default:
                    $error_message = "Invalid quarter selected.";
                    break;
            }
        } elseif ($report_type == 'annual') {
            $start_date = "$year-01-01";
            $end_date = "$year-12-31";
            $report_title = "Financial Report for the Year $year";
        } elseif ($report_type == 'custom') {
            $start_date = $_POST['start_date'] ?? '';
            $end_date = $_POST['end_date'] ?? '';
            if ($start_date && $end_date) {
                $report_title = "Financial Report from $start_date to $end_date";
            } else {
                $error_message = "Please select a valid start and end date.";
            }
        } else {
            $error_message = "Please select a valid report type and date.";
        }
    }

    // Fetch invoices and calculate totals
    if (empty($error_message)) {
        // Fetch invoices
        $stmt = $conn->prepare("SELECT * FROM invoices WHERE invoice_date BETWEEN ? AND ?");
        $stmt->bind_param("ss", $start_date, $end_date);
        $stmt->execute();
        $invoices_result = $stmt->get_result();
        $invoices = [];
        while ($invoice = $invoices_result->fetch_assoc()) {
            $total_revenue += $invoice['total_amount'];
            $invoices[] = $invoice;
        }
        $stmt->close();

        // Fetch salaries
        $stmt = $conn->prepare("SELECT * FROM salaries WHERE payment_date BETWEEN ? AND ?");
        $stmt->bind_param("ss", $start_date, $end_date);
        $stmt->execute();
        $salaries_result = $stmt->get_result();
        $salaries = [];
        while ($salary = $salaries_result->fetch_assoc()) {
            $total_salaries += $salary['net_salary']; // Use net_salary for accurate reporting
            $salaries[] = $salary;
        }
        $stmt->close();

        // Fetch other expenses (if any, optional)

        // Calculate profit/loss
        $profit_loss = $total_revenue - $total_salaries;

        // Generate PDF Report
        $pdf = new TCPDF();
        $pdf->SetCreator(PDF_CREATOR);
        $pdf->SetAuthor('Longevacare');
        $pdf->SetTitle($report_title);
        $pdf->SetSubject('Financial Report');
        $pdf->SetKeywords('TCPDF, PDF, financial, report');

        // Set margins
        $pdf->SetMargins(15, 20, 15);
        $pdf->SetHeaderMargin(10);
        $pdf->SetFooterMargin(10);

        // Add a page
        $pdf->AddPage();

        // Add company logo
        // Adjust the path to the logo as needed
        $logo_path = '../assets/images/logo.png';
        if (file_exists($logo_path)) {
            $pdf->Image($logo_path, 15, 10, 50); // x, y, width
            $pdf->Ln(25); // move down after inserting logo
        }

        // Set font
        $pdf->SetFont('Helvetica', 'B', 16);

        // Add report title
        $pdf->Cell(0, 10, $report_title, 0, 1, 'C');
        $pdf->SetFont('Helvetica', '', 12);

        // Company info
        $pdf->Cell(0, 10, "Company: Longevacare", 0, 1, 'C');
        $pdf->Cell(0, 10, "Email: info@longevacare.com | Phone: +971 528 863 600 | WhatsApp: +1 (702) 526-2050", 0, 1, 'C');
        $pdf->Ln(10);

        // Report summary
        $pdf->SetFont('Helvetica', 'B', 12);
        $pdf->Cell(120, 10, 'Total Revenue:', 1);
        $pdf->Cell(60, 10, "$" . number_format($total_revenue, 2), 1, 1, 'R');

        $pdf->Cell(120, 10, 'Total Salaries Paid:', 1);
        $pdf->Cell(60, 10, "$" . number_format($total_salaries, 2), 1, 1, 'R');

        $pdf->Cell(120, 10, 'Profit/Loss:', 1);
        $pdf->Cell(60, 10, "$" . number_format($profit_loss, 2), 1, 1, 'R');

        $pdf->Ln(10);

        // Add invoices table
        $pdf->SetFont('Helvetica', 'B', 14);
        $pdf->Cell(0, 10, 'Invoices', 0, 1, 'L');
        $pdf->SetFont('Helvetica', 'B', 12);

        // Table header
        $pdf->Cell(30, 8, 'Invoice ID', 1);
        $pdf->Cell(50, 8, 'Patient', 1);
        $pdf->Cell(50, 8, 'Date', 1);
        $pdf->Cell(50, 8, 'Amount', 1, 1, 'R');

        $pdf->SetFont('Helvetica', '', 12);

        // Output invoice data
        foreach ($invoices as $inv) {
            // Fetch patient name
            $stmt = $conn->prepare("SELECT name FROM patients WHERE id = ?");
            $stmt->bind_param("i", $inv['patient_id']);
            $stmt->execute();
            $stmt->bind_result($patient_name);
            $stmt->fetch();
            $stmt->close();

            $pdf->Cell(30, 8, $inv['id'], 1);
            $pdf->Cell(50, 8, $patient_name, 1);
            $pdf->Cell(50, 8, $inv['invoice_date'], 1);
            $pdf->Cell(50, 8, "$" . number_format($inv['total_amount'], 2), 1, 1, 'R');
        }

        $pdf->Ln(10);

        // Add salaries table
        $pdf->SetFont('Helvetica', 'B', 14);
        $pdf->Cell(0, 10, 'Salaries', 0, 1, 'L');
        $pdf->SetFont('Helvetica', 'B', 12);

        // Table header
        $pdf->Cell(30, 8, 'Salary ID', 1);
        $pdf->Cell(50, 8, 'Employee', 1);
        $pdf->Cell(50, 8, 'Date', 1);
        $pdf->Cell(50, 8, 'Net Salary', 1, 1, 'R');

        $pdf->SetFont('Helvetica', '', 12);

        // Output salary data
        foreach ($salaries as $salary) {
            // Fetch employee name
            $stmt = $conn->prepare("SELECT name FROM employees WHERE id = ?");
            $stmt->bind_param("i", $salary['employee_id']);
            $stmt->execute();
            $stmt->bind_result($employee_name);
            $stmt->fetch();
            $stmt->close();

            $pdf->Cell(30, 8, $salary['id'], 1);
            $pdf->Cell(50, 8, $employee_name, 1);
            $pdf->Cell(50, 8, $salary['payment_date'], 1);
            $pdf->Cell(50, 8, "$" . number_format($salary['net_salary'], 2), 1, 1, 'R');
        }

        // Output PDF
        $pdf->Output('financial_report.pdf', 'D');
        exit(); // Stop further execution after PDF download
    }
}
?>

<!-- Begin HTML -->

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Generate Financial Report</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f2f2f2;
            margin: 0;
            padding: 0;
        }
        .dashboard-header {
            background-color: #263238;
            color: #ffffff;
            padding: 20px;
            text-align: center;
        }
        .dashboard-header a {
            color: #ffffff;
            margin: 0 10px;
            text-decoration: none;
        }
        .filters {
            background-color: #ffffff;
            padding: 20px;
            margin: 20px auto;
            max-width: 600px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        }
        .filters label {
            display: block;
            margin-bottom: 5px;
            font-weight: bold;
        }
        .filters input[type="number"], .filters input[type="text"], .filters select, .filters input[type="date"] {
            width: 100%;
            padding: 8px;
            margin-bottom: 10px;
            box-sizing: border-box;
        }
        .filters input[type="radio"] {
            margin-right: 5px;
        }
        .btn-custom {
            background-color: #1565C0;
            color: #ffffff;
            padding: 10px 15px;
            border: none;
            cursor: pointer;
        }
        .btn-custom:hover {
            background-color: #0D47A1;
        }
        .error {
            color: red;
        }
        h3 {
            text-align: center;
        }
        .report-summary {
            background-color: #ffffff;
            padding: 20px;
            margin: 20px auto;
            max-width: 600px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        }
        .report-summary p {
            font-size: 16px;
            margin: 5px 0;
        }
    </style>
</head>
<body>
    <div class="dashboard-header">
        <h1>Generate Financial Report</h1>
        <p><a href="dashboard.php">Dashboard</a> | <a href="logout.php">Logout</a></p>
    </div>
    <div class="filters">
        <form action="generate_report.php" method="post">
            <label>Report Type:</label>
            <input type="radio" name="report_type" value="monthly" required onclick="toggleDateInputs()"> Monthly
            <input type="radio" name="report_type" value="quarterly" onclick="toggleDateInputs()"> Quarterly
            <input type="radio" name="report_type" value="annual" onclick="toggleDateInputs()"> Annual
            <input type="radio" name="report_type" value="custom" onclick="toggleDateInputs()"> Custom Date Range<br><br>

            <div id="year-input">
                <label>Year:</label>
                <input type="number" name="year" value="<?php echo date('Y'); ?>" required min="2000" max="2100"><br><br>
            </div>

            <div id="month-input" style="display:none;">
                <label>Month:</label>
                <select name="month">
                    <option value="">Select Month</option>
                    <?php 
                    for($i=1; $i<=12; $i++){
                        echo '<option value="'.$i.'">'.date('F', mktime(0,0,0,$i,1)).'</option>';
                    }
                    ?>
                </select><br><br>
            </div>

            <div id="quarter-input" style="display:none;">
                <label>Quarter:</label>
                <select name="quarter">
                    <option value="">Select Quarter</option>
                    <option value="1">Q1 (Jan - Mar)</option>
                    <option value="2">Q2 (Apr - Jun)</option>
                    <option value="3">Q3 (Jul - Sep)</option>
                    <option value="4">Q4 (Oct - Dec)</option>
                </select><br><br>
            </div>

            <div id="custom-date-inputs" style="display:none;">
                <label>Start Date:</label>
                <input type="date" name="start_date"><br><br>
                <label>End Date:</label>
                <input type="date" name="end_date"><br><br>
            </div>

            <input type="submit" class="btn-custom" value="Generate Report">
        </form>
        <?php if (!empty($error_message)) { ?>
            <p class="error"><?php echo $error_message; ?></p>
        <?php } ?>
    </div>
    
    <?php if (isset($report_title) && empty($error_message)) { ?>
        <div class="report-summary">
            <h3><?php echo $report_title; ?></h3>
            <p><strong>Total Revenue:</strong> $<?php echo number_format($total_revenue, 2); ?></p>
            <p><strong>Total Salaries Paid:</strong> $<?php echo number_format($total_salaries, 2); ?></p>
            <p><strong>Profit/Loss:</strong> $<?php echo number_format($profit_loss, 2); ?></p>
            <p><em>The detailed report has been downloaded as a PDF.</em></p>
        </div>
    <?php } ?>

    <script>
        function toggleDateInputs() {
            var reportType = document.querySelector('input[name="report_type"]:checked').value;
            document.getElementById('month-input').style.display = 'none';
            document.getElementById('quarter-input').style.display = 'none';
            document.getElementById('custom-date-inputs').style.display = 'none';
            document.getElementById('year-input').style.display = 'block';

            if (reportType == 'monthly') {
                document.getElementById('month-input').style.display = 'block';
            } else if (reportType == 'quarterly') {
                document.getElementById('quarter-input').style.display = 'block';
            } else if (reportType == 'custom') {
                document.getElementById('custom-date-inputs').style.display = 'block';
                document.getElementById('year-input').style.display = 'none';
            }
        }
        // Initialize on page load
        window.onload = function() {
            toggleDateInputs();
        };
    </script>
</body>
</html>) and this database (Updated Database Structure
The database for the admin dashboard and patient management system consists of the following tables:
a. Patients
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each patient.
name (VARCHAR): Full name of the patient.
age (INT): Age of the patient.
gender (ENUM('Male', 'Female', 'Other')): Gender of the patient.
phone (VARCHAR): Phone number of the patient.
email (VARCHAR): Email address of the patient.
medical_history (TEXT): Medical history of the patient.
date_of_birth (DATE): Date of birth of the patient.
registration_date (DATE): Date when the patient was registered.
registration_datetime (DATETIME): Date and time when the patient was registered.
status (ENUM): Current status of the patient (e.g., 'Initial Consultation', 'Treatment in Progress').
b. Health Records
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each health record.
patient_id (INT, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with this health record.
blood_pressure (VARCHAR): Blood pressure reading.
weight (DECIMAL): Weight of the patient.
bmi (DECIMAL): Body Mass Index of the patient.
cholesterol (DECIMAL): Cholesterol level of the patient.
prescription (TEXT): Prescriptions given to the patient.
treatment_progress (TEXT): Notes on the treatment progress.
record_date (DATE): Date of the health record.
c. Admins
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each admin.
username (VARCHAR): Admin's username.
password (VARCHAR): Hashed password for the admin.
email (VARCHAR): Email address of the admin.
d. Patient Logins
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each patient login.
patient_id (INT, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with this login.
username (VARCHAR): Username for patient login.
password (VARCHAR): Hashed password for patient login.
e. Invoices
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each invoice.
patient_id (INT NOT NULL, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with this invoice.
service_name (VARCHAR(255) NOT NULL): Name of the service rendered.
service_description (TEXT): Description of the service.
cost (DECIMAL(10, 2) NOT NULL): Cost of the service.
tax_rate (DECIMAL(5, 2) DEFAULT 0): Tax rate applied to the service.
discount (DECIMAL(5, 2) DEFAULT 0): Discount applied to the service.
total_amount (DECIMAL(10, 2) NOT NULL): Total amount due after tax and discount.
invoice_date (DATE DEFAULT CURRENT_DATE): Date of the invoice.
notes (TEXT): Additional notes for the invoice.
f. Services
service_id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each service.
service_name (VARCHAR(255) NOT NULL): Name of the service.
service_price (DECIMAL(10, 2) NOT NULL): Price of the service.
service_description (TEXT): Description of the service.
created_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP): Timestamp when the service was created.
updated_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP): Timestamp when the service was last updated.
g. Invoice Services
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each entry in the invoice services table.
invoice_id (INT NOT NULL, FOREIGN KEY REFERENCES invoices(id)): ID of the associated invoice.
service_id (INT NOT NULL, FOREIGN KEY REFERENCES services(service_id)): ID of the associated service.
service_name (VARCHAR(255) NOT NULL): Name of the service rendered.
cost (DECIMAL(10, 2) NOT NULL): Cost of the service rendered.
h. Comments
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each comment.
patient_id (INT NOT NULL, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with the comment.
sender (ENUM('patient', 'doctor', 'admin') NOT NULL): Sender of the comment, which can be 'patient', 'doctor', or 'admin'.
message (TEXT NOT NULL): The message or comment content.
timestamp (DATETIME DEFAULT CURRENT_TIMESTAMP): Date and time the comment was made.
parent_id (INT NULL DEFAULT NULL): ID of the parent comment (for threaded conversations), if applicable.
i. Doctor-Patient
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each doctor-patient relationship.
doctor_id (INT NOT NULL, FOREIGN KEY REFERENCES doctors(id)): ID of the associated doctor.
patient_id (INT NOT NULL, FOREIGN KEY REFERENCES patients(id)): ID of the associated patient.
UNIQUE (doctor_id, patient_id): Ensures a unique doctor-patient pair.
j. Employees
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each employee.
name (VARCHAR(255) NOT NULL): Full name of the employee.
role (ENUM('admin', 'doctor', 'staff') NOT NULL): Role of the employee, which can be 'admin', 'doctor', or 'staff'.
email (VARCHAR(255) NOT NULL UNIQUE): Email address of the employee, ensuring it is unique.
phone (VARCHAR(20)): Phone number of the employee.
department (VARCHAR(100)): Department where the employee works.
date_of_joining (DATE): The date when the employee joined the organization.
status (ENUM('active', 'inactive') DEFAULT 'active'): Employment status, either 'active' or 'inactive', with 'active' as the default.
username (VARCHAR(100) NOT NULL UNIQUE): Username for employee login, ensuring it is unique.
password (VARCHAR(255) NOT NULL): Hashed password for the employee.
created_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP): Timestamp when the employee record was created.
updated_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP): Timestamp when the employee record was last updated.
profile_picture (VARCHAR(255) DEFAULT 'default_profile.png'): Profile picture of the employee.
specialization (VARCHAR(255) DEFAULT NULL): Specialization of the employee.
k. Doctors
id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each doctor.
employee_id (INT NOT NULL, FOREIGN KEY REFERENCES employees(id)): ID of the associated employee record.
department (VARCHAR(100) DEFAULT NULL): Department where the doctor works.
date_of_joining (DATE DEFAULT NULL): The date when the doctor joined the organization.
status (ENUM('active', 'inactive') DEFAULT 'active'): Employment status of the doctor.
profile_picture (VARCHAR(255) DEFAULT 'default_profile.png'): Profile picture of the doctor.
l. Payments
id (INT AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each payment.
patient_id (INT NOT NULL, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with this payment.
invoice_id (INT NOT NULL, FOREIGN KEY REFERENCES invoices(id)): ID of the invoice associated with this payment.
amount_paid (DECIMAL(10,2) NOT NULL): Amount paid by the patient.
payment_date (DATE NOT NULL): Date of the payment.
payment_method (VARCHAR(50)): Method of payment (e.g., cash, card, insurance).
notes (TEXT): Additional notes regarding the payment.
m. Salaries
id (INT AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each salary record.
employee_id (INT NOT NULL, FOREIGN KEY REFERENCES employees(id)): ID of the employee receiving the salary.
basic_salary (DECIMAL(10,2) NOT NULL): Basic salary of the employee.
bonuses (DECIMAL(10,2) DEFAULT 0): Bonuses added to the salary.
deductions (DECIMAL(10,2) DEFAULT 0): Deductions from the salary.
overtime_hours (INT DEFAULT 0): Number of overtime hours worked by the employee.
overtime_rate (DECIMAL(10,2) DEFAULT 0): Rate per overtime hour.
tax (DECIMAL(10,2) DEFAULT 0): Tax applied to the salary.
payment_date (DATE NOT NULL): Date of the salary payment.
net_salary (DECIMAL(10,2) NOT NULL): Net salary after adjustments.
status (ENUM('Pending', 'Paid') DEFAULT 'Pending'): Status of the salary payment.
notes (TEXT): Additional notes for the salary record.



SQL Updates for Doctors Table
The SQL statement ALTER TABLE doctors ADD COLUMN date_of_joining DATE DEFAULT NULL; adds a date_of_joining column to the doctors table, allowing the storage of the date when a doctor joined the organization.
The SQL statement ALTER TABLE doctors ADD COLUMN department VARCHAR(100) DEFAULT NULL; adds a department column to the doctors table, enabling the specification of the department where the doctor works.
) and make sure everything is working, (accountant/generate_report.php) and type full codes