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/hifi11.in/public_html/medical/admin

Free Space:

10.27 TB

🔧 Quick Actions

✏️ Editing: compliance-reports.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');
$report_type = isset($_GET['report_type']) ? $_GET['report_type'] : 'prescription';

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

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

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

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

// Get compliance data based on report type
switch ($report_type) {
    case 'prescription':
        $title = "Prescription Medicine Sales Report";
        $description = "Report of all prescription medicine sales as per regulatory requirements";
        
        $compliance_query = "
            SELECT 
                s.id as sale_id,
                s.sale_date,
                s.total_amount,
                s.payment_method,
                s.customer_name,
                s.customer_phone,
                GROUP_CONCAT(DISTINCT m.medicine_name SEPARATOR ', ') as prescription_medicines,
                COUNT(DISTINCT si.medicine_id) as prescription_count,
                SUM(si.quantity) as total_quantity
            FROM sales s
            JOIN sale_items si ON s.id = si.sale_id
            JOIN medicines m ON si.medicine_id = m.id
            WHERE m.requires_prescription = 1
            AND DATE(s.sale_date) BETWEEN ? AND ?
            GROUP BY s.id
            ORDER BY s.sale_date DESC
        ";
        
        $stmt = $conn->prepare($compliance_query);
        $stmt->bind_param("ss", $start_date, $end_date);
        break;
        
    case 'expiry':
        $title = "Expired Medicine Report";
        $description = "Report of medicines that are expired or nearing expiry";
        
        $compliance_query = "
            SELECT 
                m.id,
                m.medicine_name,
                m.generic_name,
                m.batch_number,
                m.expiry_date,
                m.stock_quantity,
                m.mrp,
                m.manufacturer,
                mc.category_name,
                DATEDIFF(m.expiry_date, CURDATE()) as days_to_expiry,
                CASE 
                    WHEN m.expiry_date < CURDATE() THEN 'Expired'
                    WHEN DATEDIFF(m.expiry_date, CURDATE()) <= 30 THEN 'Expiring Soon (≤30 days)'
                    WHEN DATEDIFF(m.expiry_date, CURDATE()) <= 90 THEN 'Expiring (≤90 days)'
                    ELSE 'Valid'
                END as expiry_status
            FROM medicines m
            LEFT JOIN medicine_categories mc ON m.category_id = mc.id
            WHERE m.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 90 DAY)
            AND m.stock_quantity > 0
            ORDER BY m.expiry_date ASC, m.medicine_name ASC
        ";
        
        $stmt = $conn->prepare($compliance_query);
        break;
        
    case 'narcotic':
        $title = "Narcotic/Controlled Substances Report";
        $description = "Report of narcotic and controlled substance sales";
        
        // Assuming we have a 'narcotic' category or can identify by medicine names
        $compliance_query = "
            SELECT 
                s.id as sale_id,
                s.sale_date,
                s.total_amount,
                s.payment_method,
                s.customer_name,
                s.customer_phone,
                s.customer_address,
                GROUP_CONCAT(CONCAT(m.medicine_name, ' (', si.quantity, ' units)') SEPARATOR '<br>') as narcotic_items,
                SUM(si.quantity) as total_units,
                COUNT(DISTINCT s.id) as transaction_count
            FROM sales s
            JOIN sale_items si ON s.id = si.sale_id
            JOIN medicines m ON si.medicine_id = m.id
            WHERE (
                m.medicine_name LIKE '%morphine%' 
                OR m.medicine_name LIKE '%oxycodone%' 
                OR m.medicine_name LIKE '%fentanyl%'
                OR m.medicine_name LIKE '%codeine%'
                OR m.medicine_name LIKE '%diazepam%'
                OR m.medicine_name LIKE '%alprazolam%'
                OR m.category_id IN (SELECT id FROM medicine_categories WHERE category_name LIKE '%narcotic%' OR category_name LIKE '%controlled%')
            )
            AND DATE(s.sale_date) BETWEEN ? AND ?
            GROUP BY s.id, s.customer_name, s.customer_phone, s.customer_address
            ORDER BY s.sale_date DESC
        ";
        
        $stmt = $conn->prepare($compliance_query);
        $stmt->bind_param("ss", $start_date, $end_date);
        break;
        
    case 'tax':
        $title = "Tax Compliance Report";
        $description = "GST/Tax compliance report for all transactions";
        
        $compliance_query = "
            SELECT 
                s.id as sale_id,
                s.sale_date,
                s.total_amount,
                s.subtotal_amount,
                s.tax_amount,
                s.payment_method,
                s.customer_name,
                s.customer_phone,
                GROUP_CONCAT(CONCAT(m.medicine_name, ' (', m.hsn_code, ' @', m.gst_rate, '%)') SEPARATOR '<br>') as items_with_tax,
                AVG(m.gst_rate) as avg_gst_rate,
                COUNT(si.id) as item_count
            FROM sales s
            JOIN sale_items si ON s.id = si.sale_id
            JOIN medicines m ON si.medicine_id = m.id
            WHERE DATE(s.sale_date) BETWEEN ? AND ?
            GROUP BY s.id
            HAVING tax_amount > 0
            ORDER BY s.sale_date DESC
        ";
        
        $stmt = $conn->prepare($compliance_query);
        $stmt->bind_param("ss", $start_date, $end_date);
        break;
        
    case 'returns':
        $title = "Returns & Refunds Compliance Report";
        $description = "Report of all returned and refunded medicines";
        
        $compliance_query = "
            SELECT 
                r.id as refund_id,
                r.refund_date,
                r.refund_amount,
                r.refund_method,
                r.refund_reason,
                r.return_type,
                s.id as original_sale_id,
                s.sale_date as original_sale_date,
                s.customer_phone,
                c.customer_name,
                GROUP_CONCAT(CONCAT(m.medicine_name, ' (', ri.quantity, ' units)') SEPARATOR '<br>') as returned_items,
                COUNT(ri.id) as item_count
            FROM refunds r
            JOIN sales s ON r.sale_id = s.id
            JOIN refund_items ri ON r.id = ri.refund_id
            JOIN medicines m ON ri.medicine_id = m.id
            LEFT JOIN customers c ON s.customer_phone = c.customer_phone
            WHERE DATE(r.refund_date) BETWEEN ? AND ?
            GROUP BY r.id
            ORDER BY r.refund_date DESC
        ";
        
        $stmt = $conn->prepare($compliance_query);
        $stmt->bind_param("ss", $start_date, $end_date);
        break;
        
    default:
        $title = "Compliance Reports";
        $description = "Select a report type to view compliance data";
        $stmt = null;
}

if ($stmt) {
    $stmt->execute();
    $compliance_result = $stmt->get_result();
    $total_records = $compliance_result->num_rows;
} else {
    $total_records = 0;
}

// Get summary statistics
$summary_query = "
    SELECT 
        COUNT(*) as total_sales,
        SUM(total_amount) as total_revenue,
        COUNT(DISTINCT customer_phone) as unique_customers,
        AVG(total_amount) as avg_sale_amount,
        SUM(CASE WHEN payment_method = 'cash' THEN total_amount ELSE 0 END) as cash_sales,
        SUM(CASE WHEN payment_method != 'cash' THEN total_amount ELSE 0 END) as digital_sales
    FROM sales
    WHERE DATE(sale_date) BETWEEN ? AND ?
";

$summary_stmt = $conn->prepare($summary_query);
$summary_stmt->bind_param("ss", $start_date, $end_date);
$summary_stmt->execute();
$summary_result = $summary_stmt->get_result();
$summary = $summary_result->fetch_assoc();

// Get prescription statistics
$prescription_stats_query = "
    SELECT 
        COUNT(DISTINCT s.id) as prescription_sales,
        COUNT(DISTINCT si.medicine_id) as prescription_medicines_count,
        SUM(si.quantity) as prescription_units_sold
    FROM sales s
    JOIN sale_items si ON s.id = si.sale_id
    JOIN medicines m ON si.medicine_id = m.id
    WHERE m.requires_prescription = 1
    AND DATE(s.sale_date) BETWEEN ? AND ?
";

$prescription_stats_stmt = $conn->prepare($prescription_stats_query);
$prescription_stats_stmt->bind_param("ss", $start_date, $end_date);
$prescription_stats_stmt->execute();
$prescription_stats_result = $prescription_stats_stmt->get_result();
$prescription_stats = $prescription_stats_result->fetch_assoc();

// Get expiry statistics
$expiry_stats_query = "
    SELECT 
        COUNT(*) as expiring_soon,
        SUM(stock_quantity) as expiring_stock,
        SUM(mrp * stock_quantity) as expiring_value
    FROM medicines
    WHERE expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY)
    AND stock_quantity > 0
";

$expiry_stats_result = $conn->query($expiry_stats_query);
$expiry_stats = $expiry_stats_result->fetch_assoc();

// Get expired medicines count
$expired_query = "
    SELECT 
        COUNT(*) as expired_count,
        SUM(stock_quantity) as expired_stock,
        SUM(mrp * stock_quantity) as expired_value
    FROM medicines
    WHERE expiry_date < CURDATE()
    AND stock_quantity > 0
";

$expired_result = $conn->query($expired_query);
$expired_stats = $expired_result->fetch_assoc();
?>

<?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-3 col-md-6">
                        <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_sales'] ?? 0; ?></h4>
                                        <p class="text-muted mb-0">Total Sales</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-file-document-outline font-size-24 text-primary"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-3 col-md-6">
                        <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 $prescription_stats['prescription_sales'] ?? 0; ?></h4>
                                        <p class="text-muted mb-0">Prescription Sales</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-prescription font-size-24 text-warning"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-3 col-md-6">
                        <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 $expiry_stats['expiring_soon'] ?? 0; ?></h4>
                                        <p class="text-muted mb-0">Expiring Soon (≤90 days)</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-calendar-alert font-size-24 text-danger"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-3 col-md-6">
                        <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 number_format($expired_stats['expired_value'] ?? 0, 2); ?></h4>
                                        <p class="text-muted mb-0">Expired Stock Value</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-alert-circle-outline 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">Generate Compliance Report</h4>
                                
                                <form method="GET" action="" class="row g-3">
                                    <div class="col-md-4">
                                        <label for="report_type" class="form-label">Report Type</label>
                                        <select class="form-select" id="report_type" name="report_type" required>
                                            <option value="prescription" <?php echo $report_type === 'prescription' ? 'selected' : ''; ?>>Prescription Medicine Sales</option>
                                            <option value="expiry" <?php echo $report_type === 'expiry' ? 'selected' : ''; ?>>Expired Medicine Report</option>
                                            <option value="narcotic" <?php echo $report_type === 'narcotic' ? 'selected' : ''; ?>>Narcotic/Controlled Substances</option>
                                            <option value="tax" <?php echo $report_type === 'tax' ? 'selected' : ''; ?>>Tax Compliance Report</option>
                                            <option value="returns" <?php echo $report_type === 'returns' ? 'selected' : ''; ?>>Returns & Refunds Report</option>
                                        </select>
                                    </div>
                                    
                                    <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 mt-4">
                                        <button type="submit" class="btn btn-primary">
                                            <i class="mdi mdi-filter me-1"></i> Generate Report
                                        </button>
                                    </div>
                                    
                                    <div class="col-12 mt-2">
                                        <div class="d-flex gap-2">
                                            <button type="button" class="btn btn-outline-info btn-sm" onclick="setDateFilter(7)">
                                                Last 7 Days
                                            </button>
                                            <button type="button" class="btn btn-outline-info btn-sm" onclick="setDateFilter(30)">
                                                Last 30 Days
                                            </button>
                                            <button type="button" class="btn btn-outline-info btn-sm" onclick="setDateFilter(90)">
                                                Last 90 Days
                                            </button>
                                            <button type="button" class="btn btn-outline-info btn-sm" onclick="setToday()">
                                                Today
                                            </button>
                                        </div>
                                    </div>
                                </form>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row">
                    <div class="col-12">
                        <div class="card">
                            <div class="card-body">
                                <h4 class="card-title"><?php echo $title; ?></h4>
                                <p class="card-title-desc"><?php echo $description; ?></p>
                                
                                <div class="row mb-3">
                                    <div class="col-md-6">
                                        <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>
                                    </div>
                                    <div class="col-md-6 text-md-end">
                                        <p><strong>Total Records:</strong> <?php echo $total_records; ?></p>
                                        <div class="mt-2">
                                            <button type="button" class="btn btn-outline-success btn-sm" onclick="printReport()">
                                                <i class="mdi mdi-printer me-1"></i> Print Report
                                            </button>
                                            <button type="button" class="btn btn-outline-primary btn-sm ms-1" onclick="exportToExcel()">
                                                <i class="mdi mdi-file-excel me-1"></i> Export Excel
                                            </button>
                                            <button type="button" class="btn btn-outline-secondary btn-sm ms-1" onclick="saveAsPDF()">
                                                <i class="mdi mdi-file-pdf me-1"></i> Save as PDF
                                            </button>
                                        </div>
                                    </div>
                                </div>
                                
                                
                                <div id="reportContent">
                                    <?php if ($total_records > 0): ?>
                                        
                                        <?php if ($report_type == 'prescription'): ?>
                                            <div class="table-responsive">
                                                <table class="table table-bordered table-striped mb-0">
                                                    <thead class="table-dark">
                                                        <tr>
                                                            <th>Sale ID</th>
                                                            <th>Date & Time</th>
                                                            <th>Customer</th>
                                                            <th>Prescription Medicines</th>
                                                            <th>Quantity</th>
                                                            <th>Amount</th>
                                                            <th>Payment Method</th>
                                                        </tr>
                                                    </thead>
                                                    <tbody>
                                                        <?php while ($row = $compliance_result->fetch_assoc()): ?>
                                                            <tr>
                                                                <td>#<?php echo $row['sale_id']; ?></td>
                                                                <td><?php echo date('d M Y h:i A', strtotime($row['sale_date'])); ?></td>
                                                                <td>
                                                                    <?php echo htmlspecialchars($row['customer_name'] ?: 'Unknown'); ?><br>
                                                                    <small class="text-muted"><?php echo htmlspecialchars($row['customer_phone']); ?></small>
                                                                </td>
                                                                <td><?php echo htmlspecialchars($row['prescription_medicines']); ?></td>
                                                                <td><?php echo $row['total_quantity']; ?> units</td>
                                                                <td>₹<?php echo number_format($row['total_amount'], 2); ?></td>
                                                                <td>
                                                                    <span class="badge bg-<?php echo $row['payment_method'] == 'cash' ? 'success' : 'primary'; ?>">
                                                                        <?php echo ucfirst($row['payment_method']); ?>
                                                                    </span>
                                                                </td>
                                                            </tr>
                                                        <?php endwhile; ?>
                                                    </tbody>
                                                </table>
                                            </div>
                                            
                                        <?php elseif ($report_type == 'expiry'): ?>
                                            <div class="table-responsive">
                                                <table class="table table-bordered table-striped mb-0">
                                                    <thead class="table-dark">
                                                        <tr>
                                                            <th>Medicine</th>
                                                            <th>Generic Name</th>
                                                            <th>Batch No</th>
                                                            <th>Category</th>
                                                            <th>Expiry Date</th>
                                                            <th>Days to Expiry</th>
                                                            <th>Status</th>
                                                            <th>Stock</th>
                                                            <th>Value (MRP)</th>
                                                            <th>Manufacturer</th>
                                                        </tr>
                                                    </thead>
                                                    <tbody>
                                                        <?php while ($row = $compliance_result->fetch_assoc()): ?>
                                                            <?php 
                                                            $status_class = '';
                                                            switch($row['expiry_status']) {
                                                                case 'Expired':
                                                                    $status_class = 'danger';
                                                                    break;
                                                                case 'Expiring Soon (≤30 days)':
                                                                    $status_class = 'warning';
                                                                    break;
                                                                case 'Expiring (≤90 days)':
                                                                    $status_class = 'info';
                                                                    break;
                                                                default:
                                                                    $status_class = 'success';
                                                            }
                                                            ?>
                                                            <tr>
                                                                <td><?php echo htmlspecialchars($row['medicine_name']); ?></td>
                                                                <td><?php echo htmlspecialchars($row['generic_name']); ?></td>
                                                                <td><?php echo htmlspecialchars($row['batch_number']); ?></td>
                                                                <td><?php echo htmlspecialchars($row['category_name']); ?></td>
                                                                <td><?php echo date('d M Y', strtotime($row['expiry_date'])); ?></td>
                                                                <td><?php echo $row['days_to_expiry']; ?></td>
                                                                <td>
                                                                    <span class="badge bg-<?php echo $status_class; ?>">
                                                                        <?php echo $row['expiry_status']; ?>
                                                                    </span>
                                                                </td>
                                                                <td><?php echo $row['stock_quantity']; ?></td>
                                                                <td>₹<?php echo number_format($row['mrp'] * $row['stock_quantity'], 2); ?></td>
                                                                <td><?php echo htmlspecialchars($row['manufacturer']); ?></td>
                                                            </tr>
                                                        <?php endwhile; ?>
                                                    </tbody>
                                                </table>
                                            </div>
                                            
                                        <?php elseif ($report_type == 'narcotic'): ?>
                                            <div class="table-responsive">
                                                <table class="table table-bordered table-striped mb-0">
                                                    <thead class="table-dark">
                                                        <tr>
                                                            <th>Sale ID</th>
                                                            <th>Date & Time</th>
                                                            <th>Customer Details</th>
                                                            <th>Narcotic Items</th>
                                                            <th>Total Units</th>
                                                            <th>Amount</th>
                                                            <th>Payment Method</th>
                                                        </tr>
                                                    </thead>
                                                    <tbody>
                                                        <?php while ($row = $compliance_result->fetch_assoc()): ?>
                                                            <tr>
                                                                <td>#<?php echo $row['sale_id']; ?></td>
                                                                <td><?php echo date('d M Y h:i A', strtotime($row['sale_date'])); ?></td>
                                                                <td>
                                                                    <strong><?php echo htmlspecialchars($row['customer_name'] ?: 'Unknown'); ?></strong><br>
                                                                    Phone: <?php echo htmlspecialchars($row['customer_phone']); ?><br>
                                                                    <small><?php echo htmlspecialchars($row['customer_address']); ?></small>
                                                                </td>
                                                                <td><?php echo $row['narcotic_items']; ?></td>
                                                                <td><?php echo $row['total_units']; ?> units</td>
                                                                <td>₹<?php echo number_format($row['total_amount'], 2); ?></td>
                                                                <td>
                                                                    <span class="badge bg-<?php echo $row['payment_method'] == 'cash' ? 'success' : 'primary'; ?>">
                                                                        <?php echo ucfirst($row['payment_method']); ?>
                                                                    </span>
                                                                </td>
                                                            </tr>
                                                        <?php endwhile; ?>
                                                    </tbody>
                                                </table>
                                            </div>
                                            
                                        <?php elseif ($report_type == 'tax'): ?>
                                            <div class="table-responsive">
                                                <table class="table table-bordered table-striped mb-0">
                                                    <thead class="table-dark">
                                                        <tr>
                                                            <th>Sale ID</th>
                                                            <th>Date</th>
                                                            <th>Customer</th>
                                                            <th>Items with HSN & GST</th>
                                                            <th>Subtotal</th>
                                                            <th>Tax Amount</th>
                                                            <th>Total Amount</th>
                                                            <th>Avg GST Rate</th>
                                                            <th>Payment Method</th>
                                                        </tr>
                                                    </thead>
                                                    <tbody>
                                                        <?php while ($row = $compliance_result->fetch_assoc()): ?>
                                                            <tr>
                                                                <td>#<?php echo $row['sale_id']; ?></td>
                                                                <td><?php echo date('d M Y', strtotime($row['sale_date'])); ?></td>
                                                                <td>
                                                                    <?php echo htmlspecialchars($row['customer_name'] ?: 'Unknown'); ?><br>
                                                                    <small class="text-muted"><?php echo htmlspecialchars($row['customer_phone']); ?></small>
                                                                </td>
                                                                <td><?php echo $row['items_with_tax']; ?></td>
                                                                <td>₹<?php echo number_format($row['subtotal_amount'], 2); ?></td>
                                                                <td>₹<?php echo number_format($row['tax_amount'], 2); ?></td>
                                                                <td>₹<?php echo number_format($row['total_amount'], 2); ?></td>
                                                                <td><?php echo number_format($row['avg_gst_rate'], 2); ?>%</td>
                                                                <td>
                                                                    <span class="badge bg-<?php echo $row['payment_method'] == 'cash' ? 'success' : 'primary'; ?>">
                                                                        <?php echo ucfirst($row['payment_method']); ?>
                                                                    </span>
                                                                </td>
                                                            </tr>
                                                        <?php endwhile; ?>
                                                    </tbody>
                                                </table>
                                            </div>
                                            
                                        <?php elseif ($report_type == 'returns'): ?>
                                            <div class="table-responsive">
                                                <table class="table table-bordered table-striped mb-0">
                                                    <thead class="table-dark">
                                                        <tr>
                                                            <th>Refund ID</th>
                                                            <th>Refund Date</th>
                                                            <th>Original Sale</th>
                                                            <th>Customer</th>
                                                            <th>Returned Items</th>
                                                            <th>Refund Amount</th>
                                                            <th>Refund Method</th>
                                                            <th>Reason</th>
                                                            <th>Type</th>
                                                        </tr>
                                                    </thead>
                                                    <tbody>
                                                        <?php while ($row = $compliance_result->fetch_assoc()): ?>
                                                            <tr>
                                                                <td>#<?php echo $row['refund_id']; ?></td>
                                                                <td><?php echo date('d M Y h:i A', strtotime($row['refund_date'])); ?></td>
                                                                <td>
                                                                    Sale #<?php echo $row['original_sale_id']; ?><br>
                                                                    <small><?php echo date('d M Y', strtotime($row['original_sale_date'])); ?></small>
                                                                </td>
                                                                <td>
                                                                    <?php echo htmlspecialchars($row['customer_name'] ?: 'Unknown'); ?><br>
                                                                    <small class="text-muted"><?php echo htmlspecialchars($row['customer_phone']); ?></small>
                                                                </td>
                                                                <td><?php echo $row['returned_items']; ?></td>
                                                                <td>₹<?php echo number_format($row['refund_amount'], 2); ?></td>
                                                                <td>
                                                                    <span class="badge bg-<?php echo $row['refund_method'] == 'cash' ? 'success' : 'primary'; ?>">
                                                                        <?php echo ucfirst($row['refund_method']); ?>
                                                                    </span>
                                                                </td>
                                                                <td><?php echo htmlspecialchars($row['refund_reason']); ?></td>
                                                                <td>
                                                                    <span class="badge bg-<?php echo $row['return_type'] == 'full' ? 'danger' : 'warning'; ?>">
                                                                        <?php echo ucfirst($row['return_type']); ?>
                                                                    </span>
                                                                </td>
                                                            </tr>
                                                        <?php endwhile; ?>
                                                    </tbody>
                                                </table>
                                            </div>
                                            
                                        <?php endif; ?>
                                        
                                    <?php else: ?>
                                        <div class="text-center py-5">
                                            <i class="mdi mdi-file-document-outline display-4 text-muted"></i>
                                            <h4 class="mt-3">No Data Available</h4>
                                            <p class="text-muted">No compliance records found for the selected criteria.</p>
                                        </div>
                                    <?php endif; ?>
                                </div>
                                
                                
                                <?php if ($total_records > 0): ?>
                                    <div class="row mt-4">
                                        <div class="col-md-6">
                                            <div class="card border">
                                                <div class="card-body">
                                                    <h5 class="card-title">Report Summary</h5>
                                                    <ul class="list-unstyled mb-0">
                                                        <li><strong>Report Type:</strong> <?php echo ucfirst($report_type); ?> Report</li>
                                                        <li><strong>Period:</strong> <?php echo date('d M Y', strtotime($start_date)); ?> to <?php echo date('d M Y', strtotime($end_date)); ?></li>
                                                        <li><strong>Total Records:</strong> <?php echo $total_records; ?></li>
                                                        <li><strong>Generated On:</strong> <?php echo date('d M Y h:i A'); ?></li>
                                                    </ul>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-6">
                                            <div class="card border">
                                                <div class="card-body">
                                                    <h5 class="card-title">Compliance Notes</h5>
                                                    <ul class="mb-0">
                                                        <?php if ($report_type == 'prescription'): ?>
                                                            <li>All prescription medicine sales must be properly recorded</li>
                                                            <li>Prescriptions should be maintained for 3 years minimum</li>
                                                            <li>Verify customer identity for controlled substances</li>
                                                        <?php elseif ($report_type == 'expiry'): ?>
                                                            <li>Expired medicines must be segregated immediately</li>
                                                            <li>Return expired stock to suppliers if possible</li>
                                                            <li>Destroy expired medicines as per regulatory guidelines</li>
                                                        <?php elseif ($report_type == 'narcotic'): ?>
                                                            <li>Maintain narcotic register as per NDPS Act</li>
                                                            <li>Verify prescriptions for Schedule H1 medicines</li>
                                                            <li>Report suspicious purchases to authorities</li>
                                                        <?php elseif ($report_type == 'tax'): ?>
                                                            <li>Maintain GST records for 6 years minimum</li>
                                                            <li>File GST returns on time</li>
                                                            <li>Keep all tax invoices properly filed</li>
                                                        <?php elseif ($report_type == 'returns'): ?>
                                                            <li>Maintain separate return register</li>
                                                            <li>Issue credit notes for all returns</li>
                                                            <li>Investigate frequent return patterns</li>
                                                        <?php endif; ?>
                                                    </ul>
                                                </div>
                                            </div>
                                        </div>
                                    </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">Regulatory Information</h4>
                                <div class="row">
                                    <div class="col-md-4">
                                        <div class="border rounded p-3 mb-3">
                                            <h5><i class="mdi mdi-file-document-outline text-primary me-2"></i>Records Retention</h5>
                                            <ul class="mb-0">
                                                <li>Sales Records: 5 years</li>
                                                <li>Purchase Records: 5 years</li>
                                                <li>Prescriptions: 3 years</li>
                                                <li>GST Records: 6 years</li>
                                            </ul>
                                        </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>Compliance Requirements</h5>
                                            <ul class="mb-0">
                                                <li>Drug License valid</li>
                                                <li>GST Registration current</li>
                                                <li>Prescription verification</li>
                                                <li>Expiry monitoring</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>Key Deadlines</h5>
                                            <ul class="mb-0">
                                                <li>GST Filing: 20th of next month</li>
                                                <li>License Renewal: Annual</li>
                                                <li>Stock Audit: Quarterly</li>
                                                <li>Expiry Check: Monthly</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')?>

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();
    }

// Print report
    function printReport() {
        const reportContent = document.getElementById('reportContent');
        if (!reportContent) {
            alert('No report content to print!');
            return;
        }
        
        const printHTML = reportContent.outerHTML;
        const originalContent = document.body.innerHTML;
        
        document.body.innerHTML = `
            <html>
                <head>
                    <title>Compliance 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; }
                        h3 { color: #555; margin-top: 20px; }
                        .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; }
                        @media print {
                            .no-print { display: none; }
                            @page { margin: 0.5cm; }
                            table { font-size: 12px; }
                        }
                        .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><?php echo $title; ?></h2>
                        <p><strong>Description:</strong> <?php echo $description; ?></p>
                        <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 On:</strong> <?php echo date('d M Y h:i A'); ?></p>
                        <p><strong>Total Records:</strong> <?php echo $total_records; ?></p>
                        <p><strong>Report Type:</strong> <?php echo ucfirst($report_type); ?></p>
                    </div>
                    
                    <div class="report-content">
                        ${printHTML}
                    </div>
                    
                    <div class="print-footer">
                        <p><strong>Disclaimer:</strong> This is a system generated report for compliance purposes. Please verify all 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 exportToExcel() {
        const table = document.querySelector('#reportContent table');
        if (!table) {
            alert('No data to export!');
            return;
        }
        
        // Create a temporary iframe
        const iframe = document.createElement('iframe');
        iframe.style.display = 'none';
        document.body.appendChild(iframe);
        
        const doc = iframe.contentDocument;
        doc.open();
        doc.write(`
            <html>
                <head>
                    <title>Compliance Report - <?php echo date('d M Y'); ?></title>
                    <style>
                        table { border-collapse: collapse; width: 100%; }
                        th, td { border: 1px solid #000; padding: 8px; }
                        th { background-color: #f2f2f2; }
                    </style>
                </head>
                <body>
                    <h2><?php echo $title; ?></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>
                    ${table.outerHTML}
                </body>
            </html>
        `);
        doc.close();
        
        // Trigger download
        const filename = 'compliance_report_<?php echo $report_type; ?>_<?php echo date('Y-m-d'); ?>.xls';
        iframe.contentWindow.document.execCommand('SaveAs', true, filename);
        
        // Clean up
        setTimeout(() => {
            document.body.removeChild(iframe);
        }, 100);
    }

// Save as PDF (simulated)
    function saveAsPDF() {
        alert('PDF export feature requires additional library setup. Please use Print function and save as PDF from print dialog.');
        printReport();
    }

// 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-submit when report type changes
    document.getElementById('report_type').addEventListener('change', function() {
        if (this.value) {
            document.querySelector('form').submit();
        }
    });
</script>

</body>

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

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: compliance-reports.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation