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.28 TB

🔧 Quick Actions

✏️ Editing: overdue-collections.php

<?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 finance_id from session
$finance_id = isset($_SESSION['finance_id']) ? intval($_SESSION['finance_id']) : 0;

// Get current month, year
$current_month = date('m');
$current_year = date('Y');

// Handle filter parameters
$selected_month = isset($_GET['month']) ? $_GET['month'] : $current_month;
$selected_year = isset($_GET['year']) ? intval($_GET['year']) : $current_year;
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : '';
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : '';
$customer_search = isset($_GET['search']) ? $_GET['search'] : '';

// Validate inputs
if ($selected_month !== 'all' && ($selected_month < 1 || $selected_month > 12)) {
    $selected_month = $current_month;
}
if ($selected_year < 2000 || $selected_year > date('Y') + 1) {
    $selected_year = $current_year;
}

if ($start_date && $end_date) {
    $start_date = date('Y-m-d', strtotime($start_date));
    $end_date = date('Y-m-d', strtotime($end_date));
    if ($start_date > $end_date) {
        $start_date = '';
        $end_date = '';
        $_SESSION['error'] = "Invalid date range.";
    }
}

// Fetch overdue collections (EMIs with overdue charges)
$sql_overdue = "SELECT 
                e.id AS emi_id,
                e.emi_amount,
                e.emi_due_date,
                e.paid_date,
                e.emi_bill_number,
                e.principal_paid,
                e.interest_paid,
                e.overdue_charges,
                e.status,
                c.id AS customer_id,
                c.customer_name,
                c.customer_number,
                c.agreement_number,
                c.emi,
                DATEDIFF(CURDATE(), e.emi_due_date) AS overdue_days
             FROM emi_schedule e
             JOIN customers c ON e.customer_id = c.id
             WHERE e.status IN ('overdue', 'partial')
             AND e.overdue_charges > 0
             AND c.finance_id = ?";
             
$params_overdue = [$finance_id];
$types_overdue = "i";

// Apply search filter
if ($customer_search) {
    $sql_overdue .= " AND (c.customer_name LIKE ? OR c.agreement_number LIKE ? OR c.customer_number LIKE ?)";
    $search_term = "%$customer_search%";
    $params_overdue[] = $search_term;
    $params_overdue[] = $search_term;
    $params_overdue[] = $search_term;
    $types_overdue .= "sss";
}

// Apply date filters
if ($start_date && $end_date) {
    $sql_overdue .= " AND e.paid_date BETWEEN ? AND ?";
    $params_overdue[] = $start_date;
    $params_overdue[] = $end_date;
    $types_overdue .= "ss";
} elseif ($selected_month === 'all') {
    $sql_overdue .= " AND YEAR(e.paid_date) = ?";
    $params_overdue[] = $selected_year;
    $types_overdue .= "i";
} else {
    $sql_overdue .= " AND MONTH(e.paid_date) = ? AND YEAR(e.paid_date) = ?";
    $params_overdue[] = $selected_month;
    $params_overdue[] = $selected_year;
    $types_overdue .= "ii";
}

$sql_overdue .= " ORDER BY e.emi_due_date ASC";

$stmt_overdue = $conn->prepare($sql_overdue);
$overdue_emis = [];
if ($stmt_overdue) {
    $stmt_overdue->bind_param($types_overdue, ...$params_overdue);
    $stmt_overdue->execute();
    $result_overdue = $stmt_overdue->get_result();
    
    while ($row = $result_overdue->fetch_assoc()) {
        $overdue_emis[] = $row;
    }
    $stmt_overdue->close();
}

// Handle manual overdue calculation submission
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['calculate_overdue'])) {
    $customer_id = intval($_POST['customer_id']);
    $emi_id = intval($_POST['emi_id']);
    $overdue_days = intval($_POST['overdue_days']);
    $overdue_rate = floatval($_POST['overdue_rate']);
    $principal_amount = floatval($_POST['principal_amount']);
    
    // Manual calculation formula: Overdue = (Principal * Rate * Days) / 100
    $calculated_overdue = ($principal_amount * $overdue_rate * $overdue_days) / 100;
    
    // Update the overdue charges
    $update_sql = "UPDATE emi_schedule SET overdue_charges = ? WHERE id = ? AND customer_id = ?";
    $update_stmt = $conn->prepare($update_sql);
    $update_stmt->bind_param("dii", $calculated_overdue, $emi_id, $customer_id);
    
    if ($update_stmt->execute()) {
        $_SESSION['success'] = "Overdue charges calculated and saved: ₹" . number_format($calculated_overdue, 2);
    } else {
        $_SESSION['error'] = "Failed to save overdue charges.";
    }
    $update_stmt->close();
    
    // Redirect to refresh the page
    header("Location: overdue-collections.php");
    exit();
}

// Calculate totals
$total_overdue_amount = 0;
$total_emi_amount = 0;
$total_overdue_charges = 0;
$total_customers = count($overdue_emis);

foreach ($overdue_emis as $row) {
    $total_emi_amount += $row['emi_amount'];
    $total_overdue_charges += $row['overdue_charges'];
    $total_overdue_amount += $row['emi_amount'] + $row['overdue_charges'];
}

$conn->close();
?>

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

<div class="row align-items-center mb-4">
            <div class="col">
              <h3 class="mb-1">Overdue Collections</h3>
              <div class="text-muted">
                <?php if ($start_date && $end_date): ?>
                  Date Range: <?php echo date('d M Y', strtotime($start_date)); ?> - <?php echo date('d M Y', strtotime($end_date)); ?>
                <?php elseif ($selected_month === 'all'): ?>
                  Year: <?php echo $selected_year; ?>
                <?php else: ?>
                  Month: <?php echo date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)); ?>
                <?php endif; ?>
                <span class="mx-2">•</span>
                Overdue EMIs: <?php echo count($overdue_emis); ?>
                <span class="mx-2">•</span>
                Total Overdue: ₹<?= number_format($total_overdue_charges, 2) ?>
              </div>
            </div>
            <div class="col-auto">
              <a href="collection.php" class="btn btn-outline-primary">
                <i class="fas fa-arrow-left me-1"></i> Back to Collections
              </a>
            </div>
          </div>

<div class="card shadow-sm mb-4">
            <div class="card-header bg-white border-bottom">
              <h5 class="mb-0"><i class="fas fa-filter me-2"></i>Filter Overdue Collections</h5>
            </div>
            <div class="card-body">
              <form method="GET" class="row g-3">
                <div class="col-md-3">
                  <label class="form-label">Search Customer</label>
                  <input type="text" class="form-control" name="search" value="<?= htmlspecialchars($customer_search) ?>" placeholder="Name, Agreement, Phone">
                </div>
                <div class="col-md-2">
                  <label class="form-label">Month</label>
                  <select class="form-select" name="month">
                    <option value="all" <?= $selected_month === 'all' ? 'selected' : '' ?>>All Months</option>
                    <?php for ($m = 1; $m <= 12; $m++): ?>
                      <option value="<?= $m ?>" <?= $m == $selected_month ? 'selected' : '' ?>>
                        <?= date('F', mktime(0, 0, 0, $m, 1)) ?>
                      </option>
                    <?php endfor; ?>
                  </select>
                </div>
                <div class="col-md-2">
                  <label class="form-label">Year</label>
                  <select class="form-select" name="year">
                    <?php for ($y = 2020; $y <= date('Y') + 1; $y++): ?>
                      <option value="<?= $y ?>" <?= $y == $selected_year ? 'selected' : '' ?>><?= $y ?></option>
                    <?php endfor; ?>
                  </select>
                </div>
                <div class="col-md-2">
                  <label class="form-label">Start Date</label>
                  <input type="date" class="form-control" name="start_date" value="<?= htmlspecialchars($start_date) ?>">
                </div>
                <div class="col-md-2">
                  <label class="form-label">End Date</label>
                  <input type="date" class="form-control" name="end_date" value="<?= htmlspecialchars($end_date) ?>">
                </div>
                <div class="col-md-1 d-flex align-items-end">
                  <button type="submit" class="btn btn-primary w-100">
                    <i class="fas fa-search"></i>
                  </button>
                </div>
              </form>
              <?php if ($customer_search || $start_date || $end_date || $selected_month !== 'all'): ?>
                <div class="mt-3">
                  <a href="overdue-collections.php" class="btn btn-sm btn-outline-secondary">
                    <i class="fas fa-times me-1"></i> Clear Filters
                  </a>
                </div>
              <?php endif; ?>
            </div>
          </div>

<div class="row mb-4">
            <div class="col-xl-3 col-md-6">
              <div class="card border-0 shadow-sm bg-gradient-danger text-white">
                <div class="card-body">
                  <div class="d-flex justify-content-between">
                    <div>
                      <h6 class="text-white-50 mb-1">Total Overdue Amount</h6>
                      <h3 class="mb-0">₹<?= number_format($total_overdue_amount, 2) ?></h3>
                    </div>
                    <div class="avatar-sm">
                      <div class="avatar-title bg-white-soft rounded-circle">
                        <i class="fas fa-exclamation-triangle fa-lg"></i>
                      </div>
                    </div>
                  </div>
                  <div class="mt-2">
                    <small class="text-white-75">
                      EMI: ₹<?= number_format($total_emi_amount, 2) ?> + Charges: ₹<?= number_format($total_overdue_charges, 2) ?>
                    </small>
                  </div>
                </div>
              </div>
            </div>
            
            <div class="col-xl-3 col-md-6">
              <div class="card border-0 shadow-sm bg-gradient-warning text-white">
                <div class="card-body">
                  <div class="d-flex justify-content-between">
                    <div>
                      <h6 class="text-white-50 mb-1">Overdue Charges</h6>
                      <h3 class="mb-0">₹<?= number_format($total_overdue_charges, 2) ?></h3>
                    </div>
                    <div class="avatar-sm">
                      <div class="avatar-title bg-white-soft rounded-circle">
                        <i class="fas fa-clock fa-lg"></i>
                      </div>
                    </div>
                  </div>
                  <div class="mt-2">
                    <small class="text-white-75">Late payment fees</small>
                  </div>
                </div>
              </div>
            </div>
            
            <div class="col-xl-3 col-md-6">
              <div class="card border-0 shadow-sm bg-gradient-info text-white">
                <div class="card-body">
                  <div class="d-flex justify-content-between">
                    <div>
                      <h6 class="text-white-50 mb-1">Overdue Customers</h6>
                      <h3 class="mb-0"><?= $total_customers ?></h3>
                    </div>
                    <div class="avatar-sm">
                      <div class="avatar-title bg-white-soft rounded-circle">
                        <i class="fas fa-users fa-lg"></i>
                      </div>
                    </div>
                  </div>
                  <div class="mt-2">
                    <small class="text-white-75">Customers with overdue payments</small>
                  </div>
                </div>
              </div>
            </div>
            
            <div class="col-xl-3 col-md-6">
              <div class="card border-0 shadow-sm bg-gradient-secondary text-white">
                <div class="card-body">
                  <div class="d-flex justify-content-between">
                    <div>
                      <h6 class="text-white-50 mb-1">Average Overdue Days</h6>
                      <?php
                      $avg_days = 0;
                      if ($total_customers > 0) {
                          $total_days = 0;
                          foreach ($overdue_emis as $row) {
                              $total_days += $row['overdue_days'];
                          }
                          $avg_days = round($total_days / $total_customers, 1);
                      }
                      ?>
                      <h3 class="mb-0"><?= $avg_days ?> Days</h3>
                    </div>
                    <div class="avatar-sm">
                      <div class="avatar-title bg-white-soft rounded-circle">
                        <i class="fas fa-calendar-days fa-lg"></i>
                      </div>
                    </div>
                  </div>
                  <div class="mt-2">
                    <small class="text-white-75">Average delay in payments</small>
                  </div>
                </div>
              </div>
            </div>
          </div>

<div class="card">
            <div class="card-header">
              <div class="d-flex justify-content-between align-items-center">
                <h5 class="mb-0">
                  <i class="fas fa-exclamation-triangle text-danger me-2"></i>Overdue EMI Collections
                </h5>
                <div>
                  <input type="text" class="form-control form-control-sm" id="searchOverdue" placeholder="Search customer, agreement..." style="width: 300px;">
                </div>
              </div>
            </div>
            <div class="card-body">
              <?php if (!empty($overdue_emis)): ?>
                <div class="table-responsive">
                  <table class="table table-hover" id="overdueTable">
                    <thead class="table-light">
                      <tr>
                        <th width="50">#</th>
                        <th>Customer Details</th>
                        <th class="text-center">EMI Amount</th>
                        <th class="text-center">Due Date</th>
                        <th class="text-center">Paid Date</th>
                        <th class="text-center">Overdue Days</th>
                        <th class="text-center">Manual Overdue Calculation</th>
                        <th class="text-center">Current Overdue</th>
                        <th class="text-center">Total Amount</th>
                        <th class="text-center">Status</th>
                        <th class="text-center">Actions</th>
                      </tr>
                    </thead>
                    <tbody>
                      <?php foreach ($overdue_emis as $index => $row): ?>
                        <?php
                        $total_amount = $row['emi_amount'] + $row['overdue_charges'];
                        $overdue_bg = $row['overdue_days'] > 30 ? 'bg-danger' : ($row['overdue_days'] > 15 ? 'bg-warning' : 'bg-info');
                        ?>
                        <tr>
                          <td class="text-center"><?= $index + 1 ?></td>
                          <td>
                            <div class="customer-details">
                              <div class="mb-1"><?= htmlspecialchars($row['customer_name']) ?></div>
                              <div class="text-muted small">
                                <span><i class="fas fa-file-contract me-1"></i><?= htmlspecialchars($row['agreement_number']) ?></span>
                                <span class="mx-2">•</span>
                                <span><i class="fas fa-phone me-1"></i><?= htmlspecialchars($row['customer_number']) ?></span>
                              </div>
                            </div>
                          </td>
                          <td class="text-center">
                            <div class="text-dark">
                              ₹<?= number_format($row['emi_amount'], 2) ?>
                            </div>
                            <div class="text-muted small">
                              Principal: ₹<?= number_format($row['principal_paid'], 2) ?><br>
                              Interest: ₹<?= number_format($row['interest_paid'], 2) ?>
                            </div>
                          </td>
                          <td class="text-center">
                            <div class="<?= $row['overdue_days'] > 0 ? 'text-danger fw-bold' : 'text-dark' ?>">
                              <?= date('d M Y', strtotime($row['emi_due_date'])) ?>
                            </div>
                          </td>
                          <td class="text-center">
                            <div>
                              <?= $row['paid_date'] ? date('d M Y', strtotime($row['paid_date'])) : '<span class="text-muted">Not Paid</span>' ?>
                            </div>
                          </td>
                          <td class="text-center">
                            <span class="badge <?= $overdue_bg ?> text-white">
                              <?= $row['overdue_days'] ?> Days
                            </span>
                          </td>
                          <td class="text-center">
                            
                            <button type="button" class="btn btn-sm btn-outline-primary" 
                                    data-bs-toggle="modal" 
                                    data-bs-target="#calculateOverdueModal"
                                    data-customer-id="<?= $row['customer_id'] ?>"
                                    data-emi-id="<?= $row['emi_id'] ?>"
                                    data-customer-name="<?= htmlspecialchars($row['customer_name']) ?>"
                                    data-agreement="<?= htmlspecialchars($row['agreement_number']) ?>"
                                    data-principal="<?= $row['principal_paid'] ?>"
                                    data-overdue-days="<?= $row['overdue_days'] ?>"
                                    data-current-overdue="<?= $row['overdue_charges'] ?>">
                              <i class="fas fa-calculator me-1"></i> Calculate
                            </button>
                          </td>
                          <td class="text-center">
                            <div class="text-danger fw-bold">
                              ₹<?= number_format($row['overdue_charges'], 2) ?>
                            </div>
                          </td>
                          <td class="text-center">
                            <div class="fw-bold text-dark">
                              ₹<?= number_format($total_amount, 2) ?>
                            </div>
                          </td>
                          <td class="text-center">
                            <span class="badge bg-<?= $row['status'] === 'overdue' ? 'danger' : 'warning' ?>">
                              <?= ucfirst($row['status']) ?>
                            </span>
                          </td>
                          <td class="text-center">
                            <div class="btn-group btn-group-sm">
                              <a href="emi-schedule.php?customer_id=<?= $row['customer_id'] ?>" 
                                 class="btn btn-outline-info" title="View Schedule">
                                <i class="fas fa-calendar-alt"></i>
                              </a>
                              <a href="tel:<?= $row['customer_number'] ?>" 
                                 class="btn btn-outline-success" title="Call Customer">
                                <i class="fas fa-phone"></i>
                              </a>
                              <a href="https://wa.me/91<?= preg_replace('/\D/', '', $row['customer_number']) ?>?text=Dear%20<?= urlencode($row['customer_name']) ?>%2C%0A%0AThis%20is%20a%20reminder%20about%20your%20overdue%20EMI%20payment.%0AAgreement%3A%20<?= urlencode($row['agreement_number']) ?>%0AOverdue%20Amount%3A%20₹<?= number_format($total_amount, 2) ?>%0A%0APlease%20make%20the%20payment%20at%20your%20earliest%20convenience.%0A%0AThank%20you." 
                                 class="btn btn-outline-success" 
                                 target="_blank" 
                                 title="Send WhatsApp">
                                <i class="fab fa-whatsapp"></i>
                              </a>
                            </div>
                          </td>
                        </tr>
                      <?php endforeach; ?>
                    </tbody>
                  </table>
                </div>
              <?php else: ?>
                <div class="text-center py-5">
                  <div class="mb-3">
                    <i class="fas fa-exclamation-triangle fa-4x text-light"></i>
                  </div>
                  <h5 class="text-muted">No Overdue Collections Found</h5>
                  <p class="text-muted mb-0">No overdue payments for the selected period</p>
                  <div class="mt-3">
                    <a href="collection.php" class="btn btn-primary">
                      <i class="fas fa-arrow-left me-1"></i> Back to Collections
                    </a>
                  </div>
                </div>
              <?php endif; ?>
            </div>
            <?php if (!empty($overdue_emis)): ?>
            <div class="card-footer bg-white">
              <div class="row align-items-center">
                <div class="col">
                  <div class="text-muted">
                    Showing <?= count($overdue_emis) ?> records
                    <span class="mx-2">•</span>
                    Total Overdue Amount: <strong>₹<?= number_format($total_overdue_amount, 2) ?></strong>
                    <span class="mx-2">•</span>
                    Overdue Charges: <strong>₹<?= number_format($total_overdue_charges, 2) ?></strong>
                    <span class="mx-2">•</span>
                    EMI Amount: <strong>₹<?= number_format($total_emi_amount, 2) ?></strong>
                  </div>
                </div>
                <div class="col-auto">
                  <button class="btn btn-sm btn-outline-primary" onclick="window.print()">
                    <i class="fas fa-print me-1"></i> Print Report
                  </button>
                </div>
              </div>
            </div>
            <?php endif; ?>
          </div>

</div>
      </div>
    </div>
  </div>

<div class="modal fade" id="calculateOverdueModal" tabindex="-1">
    <div class="modal-dialog">
      <div class="modal-content">
        <form method="POST" action="">
          <div class="modal-header bg-warning text-white">
            <h5 class="modal-title">
              <i class="fas fa-calculator me-2"></i>Manual Overdue Calculation
            </h5>
            <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal"></button>
          </div>
          <div class="modal-body">
            <div class="mb-3">
              <label class="form-label fw-bold">Customer</label>
              <input type="text" class="form-control" id="modalCustomerName" readonly>
            </div>
            <div class="row">
              <div class="col-md-6">
                <div class="mb-3">
                  <label class="form-label fw-bold">Agreement No</label>
                  <input type="text" class="form-control" id="modalAgreement" readonly>
                </div>
              </div>
              <div class="col-md-6">
                <div class="mb-3">
                  <label class="form-label fw-bold">Principal Amount</label>
                  <input type="text" class="form-control" id="modalPrincipal" readonly>
                </div>
              </div>
            </div>
            <div class="row">
              <div class="col-md-6">
                <div class="mb-3">
                  <label class="form-label fw-bold">Overdue Days <span class="text-danger">*</span></label>
                  <input type="number" class="form-control" name="overdue_days" id="modalOverdueDays" required min="1" value="1">
                </div>
              </div>
              <div class="col-md-6">
                <div class="mb-3">
                  <label class="form-label fw-bold">Overdue Rate (% per day) <span class="text-danger">*</span></label>
                  <input type="number" step="0.01" class="form-control" name="overdue_rate" id="modalOverdueRate" required min="0.01" value="1.00">
                </div>
              </div>
            </div>
            <div class="mb-3">
              <label class="form-label fw-bold">Calculation Formula</label>
              <div class="alert alert-light">
                <div class="mb-2"><strong>Overdue Charges = (Principal × Rate × Days) ÷ 100</strong></div>
                <div id="calculationFormula" class="text-muted">
                  (0 × 0 × 0) ÷ 100 = ₹0.00
                </div>
              </div>
            </div>
            <div class="mb-3">
              <label class="form-label fw-bold">Current Overdue Amount</label>
              <input type="text" class="form-control" id="modalCurrentOverdue" readonly>
            </div>
            <div class="alert alert-info">
              <div class="fw-bold">New Calculated Amount: <span id="calculatedAmount" class="text-danger">₹0.00</span></div>
            </div>
            <input type="hidden" name="customer_id" id="modalCustomerId">
            <input type="hidden" name="emi_id" id="modalEmiId">
            <input type="hidden" name="principal_amount" id="modalPrincipalAmount">
            <input type="hidden" name="calculate_overdue" value="1">
          </div>
          <div class="modal-footer">
            <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
            <button type="submit" class="btn btn-warning">Save Calculated Overdue</button>
          </div>
        </form>
      </div>
    </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://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.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>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: overdue-collections.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation