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/selvaganapathi

Free Space:

10.16 TB

🔧 Quick Actions

✏️ Editing: pending-principal.php

<?php
// pending-principal.php
// Enable error logging
ini_set('display_errors', 0);
ini_set('log_errors', 1);
ini_set('error_log', '/home/u329947844/domains/hifi11.in/public_html/finance/error_log');

// Start session
session_start();

// Database connection
include 'includes/db.php';

// Get selected finance_id from session
$finance_id = isset($_SESSION['finance_id']) ? intval($_SESSION['finance_id']) : 0;

// Check if finance is selected
$finance_selected = $finance_id > 0;

// Get finance name
$finance_name = '';
if ($finance_selected) {
    $query = "SELECT finance_name FROM finance WHERE id = ?";
    $stmt = $conn->prepare($query);
    $stmt->bind_param("i", $finance_id);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        $finance_name = $row['finance_name'];
    }
    $stmt->close();
}

// Get filter parameters
$search_customer = isset($_GET['customer_name']) ? trim($_GET['customer_name']) : '';
$search_agreement = isset($_GET['agreement_number']) ? trim($_GET['agreement_number']) : '';
$search_vehicle = isset($_GET['vehicle_number']) ? trim($_GET['vehicle_number']) : '';
$status_filter = isset($_GET['status']) ? trim($_GET['status']) : 'all';
$min_pending = isset($_GET['min_pending']) ? floatval($_GET['min_pending']) : 0;
$max_pending = isset($_GET['max_pending']) ? floatval($_GET['max_pending']) : 0;
$sort_by = isset($_GET['sort_by']) ? trim($_GET['sort_by']) : 'pending_desc';

// Initialize arrays
$customers = [];
$total_stats = [
    'total_customers' => 0,
    'total_loan_amount' => 0,
    'total_principal_paid' => 0,
    'total_pending_principal' => 0,
    'total_interest_paid' => 0,
    'total_pending_interest' => 0,
    'total_overdue' => 0,
    'active_loans' => 0,
    'completed_loans' => 0,
    'overdue_customers' => 0
];

if ($finance_selected) {
    // Build WHERE clause for customers
    $where_conditions = ["c.finance_id = ?"];
    $params = [$finance_id];
    $types = "i";
    
    if (!empty($search_customer)) {
        $where_conditions[] = "c.customer_name LIKE ?";
        $params[] = "%{$search_customer}%";
        $types .= "s";
    }
    
    if (!empty($search_agreement)) {
        $where_conditions[] = "c.agreement_number LIKE ?";
        $params[] = "%{$search_agreement}%";
        $types .= "s";
    }
    
    if (!empty($search_vehicle)) {
        $where_conditions[] = "c.vehicle_number LIKE ?";
        $params[] = "%{$search_vehicle}%";
        $types .= "s";
    }
    
    $where_clause = implode(" AND ", $where_conditions);
    
    // Query to get all customers with their payment summary
    $sql = "SELECT 
                c.id,
                c.customer_name,
                c.customer_number,
                c.customer_number2,
                c.agreement_number,
                c.vehicle_number,
                c.vehicle_type,
                c.vehicle_model,
                c.loan_amount,
                c.principal_amount,
                c.interest_amount,
                c.interest_rate,
                c.loan_tenure,
                c.emi,
                c.emi_date,
                c.finance_id,
                c.customer_address,
                
                -- Calculate total paid principal from EMI schedule
                COALESCE((
                    SELECT SUM(es.principal_paid) 
                    FROM emi_schedule es 
                    WHERE es.customer_id = c.id AND es.status = 'paid'
                ), 0) as total_principal_paid,
                
                -- Calculate total paid interest
                COALESCE((
                    SELECT SUM(es.interest_paid) 
                    FROM emi_schedule es 
                    WHERE es.customer_id = c.id AND es.status = 'paid'
                ), 0) as total_interest_paid,
                
                -- Calculate total overdue charges paid
                COALESCE((
                    SELECT SUM(es.overdue_paid) 
                    FROM emi_schedule es 
                    WHERE es.customer_id = c.id AND es.status = 'paid'
                ), 0) as total_overdue_paid,
                
                -- Count paid EMIs
                COALESCE((
                    SELECT COUNT(*) 
                    FROM emi_schedule es 
                    WHERE es.customer_id = c.id AND es.status = 'paid'
                ), 0) as paid_emis_count,
                
                -- Count total EMIs scheduled
                COALESCE((
                    SELECT COUNT(*) 
                    FROM emi_schedule es 
                    WHERE es.customer_id = c.id
                ), 0) as total_emis_count,
                
                -- Get last payment date
                (
                    SELECT MAX(es.paid_date) 
                    FROM emi_schedule es 
                    WHERE es.customer_id = c.id AND es.status = 'paid'
                ) as last_payment_date,
                
                -- Check if any EMI is overdue
                CASE 
                    WHEN EXISTS (
                        SELECT 1 FROM emi_schedule es 
                        WHERE es.customer_id = c.id 
                        AND es.status IN ('unpaid', 'partial')
                        AND es.emi_due_date < CURDATE()
                    ) THEN 1 ELSE 0 
                END as has_overdue,
                
                -- Calculate total pending principal
                (c.principal_amount - COALESCE((
                    SELECT SUM(es.principal_paid) 
                    FROM emi_schedule es 
                    WHERE es.customer_id = c.id AND es.status = 'paid'
                ), 0)) as pending_principal,
                
                -- Calculate total pending interest
                (c.interest_amount - COALESCE((
                    SELECT SUM(es.interest_paid) 
                    FROM emi_schedule es 
                    WHERE es.customer_id = c.id AND es.status = 'paid'
                ), 0)) as pending_interest,
                
                -- Calculate pending EMIs count
                (c.loan_tenure - COALESCE((
                    SELECT COUNT(*) 
                    FROM emi_schedule es 
                    WHERE es.customer_id = c.id AND es.status = 'paid'
                ), 0)) as pending_emis,
                
                -- Get next EMI due date
                (
                    SELECT MIN(es.emi_due_date) 
                    FROM emi_schedule es 
                    WHERE es.customer_id = c.id AND es.status IN ('unpaid', 'partial')
                ) as next_due_date
                
            FROM customers c
            WHERE {$where_clause}
            ORDER BY c.id DESC";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            // Calculate completion percentage
            $row['completion_percentage'] = $row['total_emis_count'] > 0 
                ? round(($row['paid_emis_count'] / $row['total_emis_count']) * 100, 2) 
                : 0;
            
            // Determine loan status
            if ($row['pending_principal'] <= 0) {
                $row['loan_status'] = 'completed';
                $total_stats['completed_loans']++;
            } else {
                $row['loan_status'] = 'active';
                $total_stats['active_loans']++;
            }
            
            // Check if overdue
            $row['is_overdue'] = $row['has_overdue'] == 1;
            if ($row['is_overdue']) {
                $total_stats['overdue_customers']++;
            }
            
            $customers[] = $row;
            
            // Update totals
            $total_stats['total_loan_amount'] += $row['loan_amount'];
            $total_stats['total_principal_paid'] += $row['total_principal_paid'];
            $total_stats['total_pending_principal'] += $row['pending_principal'];
            $total_stats['total_interest_paid'] += $row['total_interest_paid'];
            $total_stats['total_pending_interest'] += $row['pending_interest'];
            $total_stats['total_overdue'] += $row['total_overdue_paid'];
        }
        $total_stats['total_customers'] = count($customers);
    }
    $stmt->close();
    
    // Apply pending amount filters if specified
    if ($min_pending > 0 || $max_pending > 0 || $status_filter != 'all') {
        $filtered_customers = [];
        foreach ($customers as $customer) {
            $pending = $customer['pending_principal'];
            if ($min_pending > 0 && $pending < $min_pending) continue;
            if ($max_pending > 0 && $pending > $max_pending) continue;
            if ($status_filter != 'all') {
                if ($status_filter == 'active' && $customer['loan_status'] != 'active') continue;
                if ($status_filter == 'completed' && $customer['loan_status'] != 'completed') continue;
                if ($status_filter == 'overdue' && !$customer['is_overdue']) continue;
            }
            $filtered_customers[] = $customer;
        }
        $customers = $filtered_customers;
    }
    
    // Apply sorting
    usort($customers, function($a, $b) use ($sort_by) {
        switch ($sort_by) {
            case 'pending_desc':
                return $b['pending_principal'] <=> $a['pending_principal'];
            case 'pending_asc':
                return $a['pending_principal'] <=> $b['pending_principal'];
            case 'name_asc':
                return strcmp($a['customer_name'], $b['customer_name']);
            case 'name_desc':
                return strcmp($b['customer_name'], $a['customer_name']);
            case 'due_date_asc':
                return ($a['next_due_date'] ?? '9999-12-31') <=> ($b['next_due_date'] ?? '9999-12-31');
            case 'due_date_desc':
                return ($b['next_due_date'] ?? '0000-00-00') <=> ($a['next_due_date'] ?? '0000-00-00');
            case 'percentage_asc':
                return $a['completion_percentage'] <=> $b['completion_percentage'];
            case 'percentage_desc':
                return $b['completion_percentage'] <=> $a['completion_percentage'];
            default:
                return $b['pending_principal'] <=> $a['pending_principal'];
        }
    });
}

mysqli_close($conn);
?>

<!DOCTYPE html>
<html lang="en" dir="ltr" data-startbar="dark" data-bs-theme="light">
<?php include 'includes/head.php'; ?>

<div class="startbar d-print-none">
        <?php include 'includes/leftbar-tab-menu.php'; ?> 
        <?php include 'includes/leftbar.php'; ?>
        <div class="startbar-overlay d-print-none"></div>

<?php if (isset($_SESSION['success'])): ?>
                        <div class="alert alert-success alert-dismissible fade show" role="alert">
                            <strong>Success!</strong> <?php echo $_SESSION['success']; ?>
                            <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                        </div>
                        <?php unset($_SESSION['success']); ?>
                    <?php endif; ?>
                    <?php if (isset($_SESSION['error'])): ?>
                        <div class="alert alert-danger alert-dismissible fade show" role="alert">
                            <strong>Error!</strong> <?php echo $_SESSION['error']; ?>
                            <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                        </div>
                        <?php unset($_SESSION['error']); ?>
                    <?php endif; ?>

<?php if (!$finance_selected): ?>
                        <div class="alert alert-warning" role="alert">
                            <i class="fas fa-exclamation-triangle me-2"></i>
                            <strong>No Finance Selected:</strong> Please select a finance from the topbar dropdown to view pending principal.
                        </div>
                    <?php endif; ?>

<div class="row align-items-center mb-3">
                        <div class="col">
                            <h3 class="mb-0">Pending Principal Amount</h3>
                            <small class="text-muted">
                                <?php if ($finance_selected && $total_stats['total_customers'] > 0): ?>
                                    Showing <?php echo count($customers); ?> customers with total pending principal of ₹<?php echo number_format($total_stats['total_pending_principal'], 2); ?>
                                <?php elseif ($finance_selected): ?>
                                    No customers found for the selected finance
                                <?php else: ?>
                                    Please select a finance to view pending principal
                                <?php endif; ?>
                            </small>
                        </div>
                        <div class="col-auto">
                            <a href="customers.php" class="btn btn-light">
                                <i class="iconoir-arrow-left me-1"></i> Back to Customers
                            </a>
                            <a href="collection.php" class="btn btn-light ms-2">Collection</a>
                        </div>
                    </div>

<?php if ($finance_selected): ?>
                        
                        <?php if (count($customers) > 0): ?>
                        <div class="card mb-4 border-primary">
                            <div class="card-header bg-primary text-white">
                                <h5 class="card-title mb-0"><i class="fas fa-download me-2"></i>Export & Print Options</h5>
                            </div>
                            <div class="card-body">
                                <div class="row">
                                    <div class="col-md-2 col-6 mb-2">
                                        <a href="pending-principal-pdf.php?type=full&finance_id=<?php echo $finance_id; ?>&<?php echo http_build_query($_GET); ?>" 
                                           class="btn btn-outline-danger w-100" target="_blank">
                                            <i class="fas fa-file-pdf me-1"></i> PDF Report
                                        </a>
                                    </div>
                                    <div class="col-md-2 col-6 mb-2">
                                        <button class="btn btn-outline-primary w-100" onclick="printPendingSummary()">
                                            <i class="fas fa-print me-1"></i> Print
                                        </button>
                                    </div>
                                    <div class="col-md-2 col-6 mb-2">
                                        <button class="btn btn-outline-success w-100" onclick="exportToExcel()">
                                            <i class="fas fa-file-excel me-1"></i> Excel
                                        </button>
                                    </div>
                                    <div class="col-md-2 col-6 mb-2">
                                        <a href="pending-principal-pdf.php?type=summary&finance_id=<?php echo $finance_id; ?>&<?php echo http_build_query($_GET); ?>" 
                                           class="btn btn-outline-info w-100" target="_blank">
                                            <i class="fas fa-chart-pie me-1"></i> Summary PDF
                                        </a>
                                    </div>
                                    <div class="col-md-2 col-6 mb-2">
                                        <a href="pending-principal-pdf.php?type=overdue&finance_id=<?php echo $finance_id; ?>&<?php echo http_build_query($_GET); ?>" 
                                           class="btn btn-outline-warning w-100" target="_blank">
                                            <i class="fas fa-exclamation-triangle me-1"></i> Overdue PDF
                                        </a>
                                    </div>
                                    <div class="col-md-2 col-6 mb-2">
                                        <button class="btn btn-outline-secondary w-100" onclick="copySummary()">
                                            <i class="fas fa-copy me-1"></i> Copy
                                        </button>
                                    </div>
                                </div>
                                <div class="row mt-2">
                                    <div class="col-md-2 col-6 mb-2">
                                        <a href="pending-principal-pdf.php?type=completed&finance_id=<?php echo $finance_id; ?>&<?php echo http_build_query($_GET); ?>" 
                                           class="btn btn-outline-success w-100" target="_blank">
                                            <i class="fas fa-check-circle me-1"></i> Completed PDF
                                        </a>
                                    </div>
                                    <div class="col-md-2 col-6 mb-2">
                                        <a href="pending-principal-pdf.php?type=active&finance_id=<?php echo $finance_id; ?>&<?php echo http_build_query($_GET); ?>" 
                                           class="btn btn-outline-primary w-100" target="_blank">
                                            <i class="fas fa-play-circle me-1"></i> Active PDF
                                        </a>
                                    </div>
                                    <div class="col-md-2 col-6 mb-2">
                                        <button class="btn btn-outline-dark w-100" onclick="downloadJSON()">
                                            <i class="fas fa-download me-1"></i> JSON
                                        </button>
                                    </div>
                                    <div class="col-md-2 col-6 mb-2">
                                        <button class="btn btn-outline-light text-dark border w-100" onclick="sendEmailReport()">
                                            <i class="fas fa-envelope me-1"></i> Email
                                        </button>
                                    </div>
                                    <div class="col-md-2 col-6 mb-2">
                                        <a href="pending-principal-pdf.php?type=detailed&finance_id=<?php echo $finance_id; ?>&<?php echo http_build_query($_GET); ?>" 
                                           class="btn btn-outline-info w-100" target="_blank">
                                            <i class="fas fa-file-alt me-1"></i> Detailed PDF
                                        </a>
                                    </div>
                                    <div class="col-md-2 col-6 mb-2">
                                        <button class="btn btn-outline-danger w-100" onclick="exportHighRisk()">
                                            <i class="fas fa-exclamation-circle me-1"></i> High Risk
                                        </button>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <?php endif; ?>

<div class="row mb-4">
                            <div class="col-xl-3 col-md-6 mb-3">
                                <div class="card bg-primary text-white h-100">
                                    <div class="card-body">
                                        <div class="d-flex justify-content-between align-items-start">
                                            <div>
                                                <h6 class="text-white-50 mb-1">Total Customers</h6>
                                                <h3 class="mb-0"><?php echo $total_stats['total_customers']; ?></h3>
                                                <small class="text-white-50">
                                                    Active: <?php echo $total_stats['active_loans']; ?> | 
                                                    Completed: <?php echo $total_stats['completed_loans']; ?>
                                                </small>
                                            </div>
                                            <i class="fas fa-users fa-2x opacity-50"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                            
                            <div class="col-xl-3 col-md-6 mb-3">
                                <div class="card bg-warning text-white h-100">
                                    <div class="card-body">
                                        <div class="d-flex justify-content-between align-items-start">
                                            <div>
                                                <h6 class="text-white-50 mb-1">Total Loan Amount</h6>
                                                <h3 class="mb-0">₹<?php echo number_format($total_stats['total_loan_amount'], 2); ?></h3>
                                                <small class="text-white-50">
                                                    Principal: ₹<?php echo number_format($total_stats['total_principal_paid'] + $total_stats['total_pending_principal'], 2); ?>
                                                </small>
                                            </div>
                                            <i class="fas fa-hand-holding-usd fa-2x opacity-50"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                            
                            <div class="col-xl-3 col-md-6 mb-3">
                                <div class="card bg-success text-white h-100">
                                    <div class="card-body">
                                        <div class="d-flex justify-content-between align-items-start">
                                            <div>
                                                <h6 class="text-white-50 mb-1">Paid Principal</h6>
                                                <h3 class="mb-0">₹<?php echo number_format($total_stats['total_principal_paid'], 2); ?></h3>
                                                <small class="text-white-50">
                                                    <?php 
                                                    $paid_percent = $total_stats['total_loan_amount'] > 0 
                                                        ? round(($total_stats['total_principal_paid'] / $total_stats['total_loan_amount']) * 100, 2) 
                                                        : 0;
                                                    echo $paid_percent; ?>% of total
                                                </small>
                                            </div>
                                            <i class="fas fa-check-circle fa-2x opacity-50"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                            
                            <div class="col-xl-3 col-md-6 mb-3">
                                <div class="card bg-danger text-white h-100">
                                    <div class="card-body">
                                        <div class="d-flex justify-content-between align-items-start">
                                            <div>
                                                <h6 class="text-white-50 mb-1">Pending Principal</h6>
                                                <h3 class="mb-0">₹<?php echo number_format($total_stats['total_pending_principal'], 2); ?></h3>
                                                <small class="text-white-50">
                                                    Overdue Customers: <?php echo $total_stats['overdue_customers']; ?>
                                                </small>
                                            </div>
                                            <i class="fas fa-clock fa-2x opacity-50"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

<div class="row mb-4">
                            <div class="col-md-4 mb-3">
                                <div class="card bg-info text-white">
                                    <div class="card-body p-3">
                                        <div class="d-flex justify-content-between align-items-center">
                                            <div>
                                                <h6 class="mb-0">Paid Interest</h6>
                                                <h4 class="mb-0">₹<?php echo number_format($total_stats['total_interest_paid'], 2); ?></h4>
                                            </div>
                                            <i class="fas fa-percentage fa-2x opacity-50"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-4 mb-3">
                                <div class="card bg-secondary text-white">
                                    <div class="card-body p-3">
                                        <div class="d-flex justify-content-between align-items-center">
                                            <div>
                                                <h6 class="mb-0">Pending Interest</h6>
                                                <h4 class="mb-0">₹<?php echo number_format($total_stats['total_pending_interest'], 2); ?></h4>
                                            </div>
                                            <i class="fas fa-chart-line fa-2x opacity-50"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-4 mb-3">
                                <div class="card bg-dark text-white">
                                    <div class="card-body p-3">
                                        <div class="d-flex justify-content-between align-items-center">
                                            <div>
                                                <h6 class="mb-0">Overdue Charges Paid</h6>
                                                <h4 class="mb-0">₹<?php echo number_format($total_stats['total_overdue'], 2); ?></h4>
                                            </div>
                                            <i class="fas fa-exclamation-triangle fa-2x opacity-50"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

<div class="card mb-4">
                            <div class="card-header bg-light">
                                <h5 class="card-title mb-0">Filter Customers</h5>
                            </div>
                            <div class="card-body">
                                <form method="GET" action="pending-principal.php" id="filterForm">
                                    <div class="row g-3">
                                        <div class="col-md-3">
                                            <label for="customer_name" class="form-label fw-bold">Customer Name</label>
                                            <input type="text" class="form-control" id="customer_name" name="customer_name" 
                                                   value="<?php echo htmlspecialchars($search_customer); ?>" 
                                                   placeholder="Search by name">
                                        </div>
                                        <div class="col-md-3">
                                            <label for="agreement_number" class="form-label fw-bold">Agreement No.</label>
                                            <input type="text" class="form-control" id="agreement_number" name="agreement_number" 
                                                   value="<?php echo htmlspecialchars($search_agreement); ?>" 
                                                   placeholder="Search by agreement">
                                        </div>
                                        <div class="col-md-3">
                                            <label for="vehicle_number" class="form-label fw-bold">Vehicle No.</label>
                                            <input type="text" class="form-control" id="vehicle_number" name="vehicle_number" 
                                                   value="<?php echo htmlspecialchars($search_vehicle); ?>" 
                                                   placeholder="Search by vehicle">
                                        </div>
                                        <div class="col-md-3">
                                            <label for="status" class="form-label fw-bold">Loan Status</label>
                                            <select class="form-select" id="status" name="status">
                                                <option value="all" <?php echo $status_filter == 'all' ? 'selected' : ''; ?>>All Loans</option>
                                                <option value="active" <?php echo $status_filter == 'active' ? 'selected' : ''; ?>>Active Only</option>
                                                <option value="completed" <?php echo $status_filter == 'completed' ? 'selected' : ''; ?>>Completed</option>
                                                <option value="overdue" <?php echo $status_filter == 'overdue' ? 'selected' : ''; ?>>Overdue</option>
                                            </select>
                                        </div>
                                        <div class="col-md-3">
                                            <label for="min_pending" class="form-label fw-bold">Min Pending (₹)</label>
                                            <input type="number" class="form-control" id="min_pending" name="min_pending" 
                                                   value="<?php echo $min_pending > 0 ? $min_pending : ''; ?>" 
                                                   placeholder="Minimum amount" step="1000" min="0">
                                        </div>
                                        <div class="col-md-3">
                                            <label for="max_pending" class="form-label fw-bold">Max Pending (₹)</label>
                                            <input type="number" class="form-control" id="max_pending" name="max_pending" 
                                                   value="<?php echo $max_pending > 0 ? $max_pending : ''; ?>" 
                                                   placeholder="Maximum amount" step="1000" min="0">
                                        </div>
                                        <div class="col-md-3">
                                            <label for="sort_by" class="form-label fw-bold">Sort By</label>
                                            <select class="form-select" id="sort_by" name="sort_by">
                                                <option value="pending_desc" <?php echo $sort_by == 'pending_desc' ? 'selected' : ''; ?>>Pending (High to Low)</option>
                                                <option value="pending_asc" <?php echo $sort_by == 'pending_asc' ? 'selected' : ''; ?>>Pending (Low to High)</option>
                                                <option value="name_asc" <?php echo $sort_by == 'name_asc' ? 'selected' : ''; ?>>Name (A to Z)</option>
                                                <option value="name_desc" <?php echo $sort_by == 'name_desc' ? 'selected' : ''; ?>>Name (Z to A)</option>
                                                <option value="due_date_asc" <?php echo $sort_by == 'due_date_asc' ? 'selected' : ''; ?>>Due Date (Earliest)</option>
                                                <option value="due_date_desc" <?php echo $sort_by == 'due_date_desc' ? 'selected' : ''; ?>>Due Date (Latest)</option>
                                                <option value="percentage_asc" <?php echo $sort_by == 'percentage_asc' ? 'selected' : ''; ?>>Completion % (Low to High)</option>
                                                <option value="percentage_desc" <?php echo $sort_by == 'percentage_desc' ? 'selected' : ''; ?>>Completion % (High to Low)</option>
                                            </select>
                                        </div>
                                        <div class="col-md-3 d-flex align-items-end">
                                            <div class="d-flex gap-2 w-100">
                                                <button type="submit" class="btn btn-primary flex-grow-1">
                                                    <i class="fas fa-search me-1"></i> Apply Filters
                                                </button>
                                                <a href="pending-principal.php" class="btn btn-outline-secondary">
                                                    <i class="fas fa-times"></i> Clear
                                                </a>
                                            </div>
                                        </div>
                                    </div>
                                </form>
                            </div>
                        </div>

<div class="card">
                            <div class="card-header d-flex justify-content-between align-items-center">
                                <h4 class="card-title mb-0">Customer-wise Pending Principal</h4>
                                <?php if (count($customers) > 0): ?>
                                    <div>
                                        <span class="badge bg-primary me-2">Total: ₹<?php echo number_format($total_stats['total_pending_principal'], 2); ?></span>
                                        <span class="badge bg-info">Customers: <?php echo count($customers); ?></span>
                                    </div>
                                <?php endif; ?>
                            </div>
                            <div class="card-body">
                                <?php if (count($customers) > 0): ?>
                                    <div class="table-responsive">
                                        <table id="tblPendingPrincipal" class="table table-striped table-bordered align-middle w-100">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>#</th>
                                                    <th>Customer Details</th>
                                                    <th>Agreement / Vehicle</th>
                                                    <th>Loan Details</th>
                                                    <th>Paid Summary</th>
                                                    <th>Pending Principal</th>
                                                    <th>Pending Interest</th>
                                                    <th>Progress</th>
                                                    <th>Next Due</th>
                                                    <th>Status</th>
                                                    <th class="text-center">Actions</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php $counter = 1; ?>
                                                <?php foreach ($customers as $customer): ?>
                                                    <?php
                                                    $status_class = '';
                                                    $status_text = '';
                                                    
                                                    if ($customer['loan_status'] == 'completed') {
                                                        $status_class = 'success';
                                                        $status_text = 'Completed';
                                                    } elseif ($customer['is_overdue']) {
                                                        $status_class = 'danger';
                                                        $status_text = 'Overdue';
                                                    } else {
                                                        $status_class = 'primary';
                                                        $status_text = 'Active';
                                                    }
                                                    
                                                    $progress_color = $customer['completion_percentage'] >= 75 ? 'success' : 
                                                                     ($customer['completion_percentage'] >= 50 ? 'info' : 
                                                                     ($customer['completion_percentage'] >= 25 ? 'warning' : 'danger'));
                                                    ?>
                                                    <tr>
                                                        <td><?php echo $counter++; ?></td>
                                                        <td>
                                                            <div class="d-flex align-items-center">
                                                                <div class="flex-shrink-0 me-2">
                                                                    <div class="avatar-sm">
                                                                        <div class="avatar-title bg-soft-<?php echo $status_class; ?> text-<?php echo $status_class; ?> rounded-circle">
                                                                            <?php echo strtoupper(substr($customer['customer_name'], 0, 1)); ?>
                                                                        </div>
                                                                    </div>
                                                                </div>
                                                                <div class="flex-grow-1">
                                                                    <strong><?php echo htmlspecialchars($customer['customer_name']); ?></strong>
                                                                    <div class="small text-muted">
                                                                        <i class="fas fa-phone-alt me-1"></i>
                                                                        <a href="tel:<?php echo $customer['customer_number']; ?>" class="text-decoration-none">
                                                                            <?php echo htmlspecialchars($customer['customer_number']); ?>
                                                                        </a>
                                                                        <?php if (!empty($customer['customer_number2'])): ?>
                                                                            , <a href="tel:<?php echo $customer['customer_number2']; ?>" class="text-decoration-none">
                                                                                <?php echo htmlspecialchars($customer['customer_number2']); ?>
                                                                            </a>
                                                                        <?php endif; ?>
                                                                    </div>
                                                                </div>
                                                            </div>
                                                        </td>
                                                        <td>
                                                            <span class="badge bg-secondary"><?php echo htmlspecialchars($customer['agreement_number']); ?></span>
                                                            <?php if (!empty($customer['vehicle_number'])): ?>
                                                                <div class="small mt-1">
                                                                    <i class="fas fa-motorcycle me-1"></i>
                                                                    <?php echo htmlspecialchars($customer['vehicle_number']); ?>
                                                                </div>
                                                                <?php if (!empty($customer['vehicle_model'])): ?>
                                                                    <div class="small text-muted">
                                                                        <?php echo htmlspecialchars($customer['vehicle_model']); ?>
                                                                    </div>
                                                                <?php endif; ?>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <div class="small">
                                                                <strong>Loan:</strong> ₹<?php echo number_format($customer['loan_amount'], 2); ?><br>
                                                                <strong>Principal:</strong> ₹<?php echo number_format($customer['principal_amount'], 2); ?><br>
                                                                <strong>Interest:</strong> ₹<?php echo number_format($customer['interest_amount'], 2); ?><br>
                                                                <strong>Rate:</strong> <?php echo $customer['interest_rate']; ?>%<br>
                                                                <strong>EMI:</strong> ₹<?php echo number_format($customer['emi'], 2); ?>
                                                            </div>
                                                        </td>
                                                        <td>
                                                            <div class="small">
                                                                <span class="text-success">
                                                                    <i class="fas fa-check-circle me-1"></i>
                                                                    Paid Principal: ₹<?php echo number_format($customer['total_principal_paid'], 2); ?>
                                                                </span><br>
                                                                <span class="text-warning">
                                                                    <i class="fas fa-percentage me-1"></i>
                                                                    Paid Interest: ₹<?php echo number_format($customer['total_interest_paid'], 2); ?>
                                                                </span><br>
                                                                <span class="text-info">
                                                                    <i class="fas fa-calendar-check me-1"></i>
                                                                    EMIs: <?php echo $customer['paid_emis_count']; ?>/<?php echo $customer['total_emis_count']; ?>
                                                                </span><br>
                                                                <?php if (!empty($customer['last_payment_date'])): ?>
                                                                    <span class="text-muted">
                                                                        <i class="fas fa-history me-1"></i>
                                                                        Last: <?php echo date('d M Y', strtotime($customer['last_payment_date'])); ?>
                                                                    </span>
                                                                <?php endif; ?>
                                                            </div>
                                                        </td>
                                                        <td class="fw-bold text-danger">
                                                            <h5 class="mb-0 text-danger">₹<?php echo number_format($customer['pending_principal'], 2); ?></h5>
                                                            <small class="text-muted">
                                                                <?php 
                                                                $pending_percent = $customer['principal_amount'] > 0 
                                                                    ? round(($customer['pending_principal'] / $customer['principal_amount']) * 100, 1) 
                                                                    : 0;
                                                                echo $pending_percent; ?>% of principal
                                                            </small>
                                                        </td>
                                                        <td class="fw-bold text-warning">
                                                            ₹<?php echo number_format($customer['pending_interest'], 2); ?>
                                                        </td>
                                                        <td>
                                                            <div class="d-flex align-items-center">
                                                                <div class="flex-grow-1 me-2">
                                                                    <div class="progress" style="height: 6px;">
                                                                        <div class="progress-bar bg-<?php echo $progress_color; ?>" 
                                                                             role="progressbar" 
                                                                             style="width: <?php echo $customer['completion_percentage']; ?>%;" 
                                                                             aria-valuenow="<?php echo $customer['completion_percentage']; ?>" 
                                                                             aria-valuemin="0" 
                                                                             aria-valuemax="100">
                                                                        </div>
                                                                    </div>
                                                                </div>
                                                                <span class="small fw-bold"><?php echo $customer['completion_percentage']; ?>%</span>
                                                            </div>
                                                            <small class="text-muted">
                                                                <?php echo $customer['pending_emis']; ?> EMIs left
                                                            </small>
                                                        </td>
                                                        <td>
                                                            <?php if (!empty($customer['next_due_date'])): ?>
                                                                <?php 
                                                                $due_date = strtotime($customer['next_due_date']);
                                                                $today = strtotime(date('Y-m-d'));
                                                                $days_diff = round(($due_date - $today) / (60 * 60 * 24));
                                                                
                                                                $due_class = $days_diff < 0 ? 'danger' : ($days_diff <= 7 ? 'warning' : 'success');
                                                                $due_text = $days_diff < 0 ? 'Overdue' : ($days_diff == 0 ? 'Today' : "In $days_diff days");
                                                                ?>
                                                                <span class="badge bg-<?php echo $due_class; ?> p-2">
                                                                    <?php echo date('d M Y', strtotime($customer['next_due_date'])); ?>
                                                                    <br>
                                                                    <small><?php echo $due_text; ?></small>
                                                                </span>
                                                            <?php else: ?>
                                                                <span class="badge bg-secondary">No pending EMIs</span>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <span class="badge bg-<?php echo $status_class; ?> p-2">
                                                                <?php echo $status_text; ?>
                                                                <?php if ($customer['is_overdue']): ?>
                                                                    <i class="fas fa-exclamation-circle ms-1"></i>
                                                                <?php endif; ?>
                                                            </span>
                                                        </td>
                                                        <td class="text-center">
                                                            <div class="btn-group btn-group-sm">
                                                                <a href="customer-details.php?id=<?php echo $customer['id']; ?>" 
                                                                   class="btn btn-outline-primary" title="View Details">
                                                                    <i class="fas fa-eye"></i>
                                                                </a>
                                                                <a href="collection.php?customer_id=<?php echo $customer['id']; ?>" 
                                                                   class="btn btn-outline-success" title="Collect Payment">
                                                                    <i class="fas fa-rupee-sign"></i>
                                                                </a>
                                                                <a href="emi-schedule.php?customer_id=<?php echo $customer['id']; ?>" 
                                                                   class="btn btn-outline-info" title="View Schedule">
                                                                    <i class="fas fa-calendar-alt"></i>
                                                                </a>
                                                                <a href="pending-principal-pdf.php?type=customer&customer_id=<?php echo $customer['id']; ?>&finance_id=<?php echo $finance_id; ?>" 
                                                                   class="btn btn-outline-secondary" target="_blank" title="Print PDF">
                                                                    <i class="fas fa-file-pdf"></i>
                                                                </a>
                                                            </div>
                                                        </td>
                                                    </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                            <tfoot>
                                                <tr class="table-primary">
                                                    <th colspan="5" class="text-end">Total:</th>
                                                    <th class="text-danger">₹<?php echo number_format($total_stats['total_pending_principal'], 2); ?></th>
                                                    <th class="text-warning">₹<?php echo number_format($total_stats['total_pending_interest'], 2); ?></th>
                                                    <th colspan="3"></th>
                                                    <th></th>
                                                </tr>
                                                <tr class="table-light">
                                                    <th colspan="5" class="text-end">Averages:</th>
                                                    <th class="text-danger">
                                                        ₹<?php echo count($customers) > 0 ? number_format($total_stats['total_pending_principal'] / count($customers), 2) : 0; ?>
                                                    </th>
                                                    <th class="text-warning">
                                                        ₹<?php echo count($customers) > 0 ? number_format($total_stats['total_pending_interest'] / count($customers), 2) : 0; ?>
                                                    </th>
                                                    <th colspan="3"></th>
                                                    <th></th>
                                                </tr>
                                            </tfoot>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-5">
                                        <i class="fas fa-users fa-3x text-muted mb-3"></i>
                                        <h5 class="text-muted">No customers found</h5>
                                        <p class="text-muted">
                                            <?php if (!empty($search_customer) || !empty($search_agreement) || !empty($search_vehicle) || $status_filter != 'all' || $min_pending > 0 || $max_pending > 0): ?>
                                                No customers match your filter criteria.
                                            <?php else: ?>
                                                No customers found for the selected finance.
                                            <?php endif; ?>
                                        </p>
                                        <?php if (!empty($search_customer) || !empty($search_agreement) || !empty($search_vehicle) || $status_filter != 'all' || $min_pending > 0 || $max_pending > 0): ?>
                                            <a href="pending-principal.php" class="btn btn-primary mt-2">
                                                <i class="fas fa-times me-1"></i> Clear Filters
                                            </a>
                                        <?php endif; ?>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>

<?php else: ?>
                        
                        <div class="card">
                            <div class="card-body text-center py-5">
                                <i class="fas fa-hand-holding-usd fa-3x text-muted mb-3"></i>
                                <h5 class="text-muted">Please select a finance</h5>
                                <p class="text-muted">To view pending principal amounts, please select a finance from the dropdown in the top bar.</p>
                                <div class="mt-3">
                                    <form action="set-finance-session.php" method="post" id="financeSelectForm" class="d-inline">
                                        <select class="form-select d-inline-block w-auto me-2" name="finance_id" onchange="this.form.submit()">
                                            <option value="0">Select Finance</option>
                                            <option value="1">Sri Selvaganapathi</option>
                                            <option value="2">Sri Veerabathra Auto Finance</option>
                                        </select>
                                    </form>
                                </div>
                            </div>
                        </div>
                    <?php endif; ?>
                </div>

<?php include 'includes/rightbar.php'; ?>
                <?php include 'includes/footer.php'; ?>
            </div>
            
        </div>

<?php include 'includes/scripts.php'; ?>

<link rel="stylesheet" href="https://cdn.datatables.net/1.13.8/css/dataTables.bootstrap5.min.css">
        <link rel="stylesheet" href="https://cdn.datatables.net/buttons/2.4.2/css/buttons.bootstrap5.min.css">
        <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
        <script src="https://cdn.datatables.net/1.13.8/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/1.13.8/js/dataTables.bootstrap5.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/2.4.2/js/dataTables.buttons.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.bootstrap5.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.html5.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.print.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js"></script>
        <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.pdfMake.min.js"></script>

// Add row coloring based on status
                $('#tblPendingPrincipal tbody tr').each(function() {
                    var status = $(this).find('td:eq(9) .badge').text().trim();
                    if (status.includes('Overdue')) {
                        $(this).addClass('overdue-row');
                    } else if (status.includes('Completed')) {
                        $(this).addClass('completed-row');
                    }
                    
                    var pending = parseFloat($(this).find('td:eq(5) h5').text().replace(/[^0-9.-]+/g, ''));
                    if (pending > 50000) {
                        $(this).addClass('high-pending');
                    }
                });
            });

// Print pending summary
            function printPendingSummary() {
                var printContent = `
                    <div style="padding: 20px; font-family: Arial, sans-serif;">
                        <h2 style="text-align: center; color: #333; margin-bottom: 20px;">Pending Principal Summary Report</h2>
                        
                        <div style="margin-bottom: 30px; border: 2px solid #007bff; padding: 20px; border-radius: 8px; background: #f8f9fa;">
                            <h3 style="margin-top: 0; color: #007bff;">Summary Statistics</h3>
                            <table style="width: 100%; border-collapse: collapse;">
                                <tr>
                                    <td style="padding: 8px; width: 33%;">
                                        <strong>Finance:</strong> <?php echo $finance_name; ?><br>
                                        <strong>Total Customers:</strong> <?php echo count($customers); ?><br>
                                        <strong>Active Loans:</strong> <?php echo $total_stats['active_loans']; ?><br>
                                        <strong>Completed:</strong> <?php echo $total_stats['completed_loans']; ?>
                                    </td>
                                    <td style="padding: 8px; width: 33%;">
                                        <strong>Total Loan Amount:</strong> ₹<?php echo number_format($total_stats['total_loan_amount'], 2); ?><br>
                                        <strong>Total Principal Paid:</strong> ₹<?php echo number_format($total_stats['total_principal_paid'], 2); ?><br>
                                        <strong>Total Pending Principal:</strong> ₹<?php echo number_format($total_stats['total_pending_principal'], 2); ?>
                                    </td>
                                    <td style="padding: 8px; width: 33%;">
                                        <strong>Paid Interest:</strong> ₹<?php echo number_format($total_stats['total_interest_paid'], 2); ?><br>
                                        <strong>Pending Interest:</strong> ₹<?php echo number_format($total_stats['total_pending_interest'], 2); ?><br>
                                        <strong>Overdue Customers:</strong> <?php echo $total_stats['overdue_customers']; ?>
                                    </td>
                                </tr>
                            </table>
                        </div>
                        
                        <h3>Customer-wise Details</h3>
                        ${document.getElementById('tblPendingPrincipal').outerHTML}
                        
                        <div style="margin-top: 30px; text-align: center; color: #666; font-size: 12px;">
                            Generated on: <?php echo date('d M Y, h:i A'); ?> | Finance Management System
                        </div>
                    </div>
                `;
                
                var printWindow = window.open('', '_blank');
                printWindow.document.write(`
                    <html>
                        <head>
                            <title>Pending Principal Report</title>
                            <style>
                                body { font-family: Arial, sans-serif; margin: 20px; }
                                table { width: 100%; border-collapse: collapse; margin-top: 20px; font-size: 12px; }
                                th, td { border: 1px solid #000; padding: 6px; text-align: left; }
                                th { background-color: #f2f2f2; font-weight: bold; }
                                .overdue-row { background-color: #fff0f0; }
                                .completed-row { background-color: #f0fff0; }
                                .high-pending { background-color: #fff3f3; }
                                .badge { 
                                    display: inline-block; 
                                    padding: 3px 6px; 
                                    border-radius: 3px;
                                    font-size: 11px;
                                    border: 1px solid #000;
                                }
                                .bg-success { background-color: #d4edda; }
                                .bg-danger { background-color: #f8d7da; }
                                .bg-warning { background-color: #fff3cd; }
                                .bg-primary { background-color: #cce5ff; }
                                @media print {
                                    @page { size: landscape; }
                                }
                            </style>
                        </head>
                        <body>
                            ${printContent}
                        </body>
                    </html>
                `);
                printWindow.document.close();
                printWindow.print();
            }

// Export to Excel
            function exportToExcel() {
                var table = $('#tblPendingPrincipal').DataTable();
                table.button('.buttons-excel').trigger();
            }

// Copy summary to clipboard
            function copySummary() {
                var summaryText = `PENDING PRINCIPAL SUMMARY\n`;
                summaryText += `====================================\n`;
                summaryText += `Finance: <?php echo $finance_name; ?>\n`;
                summaryText += `Generated: <?php echo date('d M Y, h:i A'); ?>\n\n`;
                summaryText += `Total Customers: <?php echo count($customers); ?>\n`;
                summaryText += `Active Loans: <?php echo $total_stats['active_loans']; ?>\n`;
                summaryText += `Completed Loans: <?php echo $total_stats['completed_loans']; ?>\n`;
                summaryText += `Overdue Customers: <?php echo $total_stats['overdue_customers']; ?>\n\n`;
                summaryText += `Total Loan Amount: ₹<?php echo number_format($total_stats['total_loan_amount'], 2); ?>\n`;
                summaryText += `Total Principal Paid: ₹<?php echo number_format($total_stats['total_principal_paid'], 2); ?>\n`;
                summaryText += `Total Pending Principal: ₹<?php echo number_format($total_stats['total_pending_principal'], 2); ?>\n`;
                summaryText += `Total Pending Interest: ₹<?php echo number_format($total_stats['total_pending_interest'], 2); ?>\n\n`;
                summaryText += `Average Pending per Customer: ₹<?php echo count($customers) > 0 ? number_format($total_stats['total_pending_principal'] / count($customers), 2) : 0; ?>\n`;
                
                navigator.clipboard.writeText(summaryText).then(function() {
                    alert('Summary copied to clipboard!');
                }, function() {
                    // Fallback
                    var textArea = document.createElement("textarea");
                    textArea.value = summaryText;
                    document.body.appendChild(textArea);
                    textArea.select();
                    document.execCommand("copy");
                    document.body.removeChild(textArea);
                    alert('Summary copied to clipboard!');
                });
            }

// Export only overdue customers
            function exportHighRisk() {
                var table = $('#tblPendingPrincipal').DataTable();
                table.column(9).search('Overdue').draw();
                setTimeout(function() {
                    exportToExcel();
                    table.column(9).search('').draw(); // Reset filter
                }, 500);
            }

// Download as JSON
            function downloadJSON() {
                var data = <?php echo json_encode($customers); ?>;
                var dataStr = "data:text/json;charset=utf-8," + encodeURIComponent(JSON.stringify(data, null, 2));
                var downloadAnchorNode = document.createElement('a');
                downloadAnchorNode.setAttribute("href", dataStr);
                downloadAnchorNode.setAttribute("download", "pending_principal_data.json");
                document.body.appendChild(downloadAnchorNode);
                downloadAnchorNode.click();
                downloadAnchorNode.remove();
            }

// Simulate email report
            function sendEmailReport() {
                var email = prompt("Enter email address to send report:", "finance@example.com");
                if (email) {
                    window.location.href = `mailto:${email}?subject=Pending Principal Report&body=Please find attached the pending principal report. (This is a simulation - actual email functionality would require backend)`;
                }
            }

// Auto-submit on filter change
            $(document).ready(function() {
                $('#status, #sort_by').on('change', function() {
                    $('#filterForm').submit();
                });
            });
        </script>
</body>
</html>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: pending-principal.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation