<?php

session_name('super_admin_session');

if (!is_dir(dirname(__DIR__) . '/session_data/super')) { mkdir(dirname(__DIR__) . '/session_data/super', 0700, true); } session_save_path(dirname(__DIR__) . '/session_data/super');

session_set_cookie_params(['path' => '/', 'httponly' => true, 'samesite' => 'Strict']);

session_start();

error_reporting(0);

include('includes/dbconnection.php');

require_once __DIR__ . '/includes/session_term_helper.php';
// Fetch current session and term (with gap/fallback support)
$stResult = resolveCurrentSessionTerm($con);
$currentSessionId   = $stResult['session_id'];
$currentSessionName = $stResult['session_name'];
$currentTermId      = $stResult['term_id'];
$currentTermName    = $stResult['term_name'];


if (!isset($_SESSION['sid']) || strlen($_SESSION['sid']) == 0) {

    header('location:logout.php');

    exit();

}



// Retrieve raw values

$class   = trim($_POST['class'] ?? '');

$pay_for = trim($_POST['pay_for'] ?? '');

$session = trim($_POST['session'] ?? '');

$term    = trim($_POST['Term'] ?? '');

$section = trim($_POST['arm'] ?? '');



$result = false; // Initialize to false



// ✅ Only run the query if a Class and Fee Name are selected

if (!empty($class) && !empty($pay_for)) {



    $query = "

        SELECT 

            s.studentno AS admission_no,

            s.studentRegNo,

            s.studentName, 

            s.class, 

            s.section, 

            s.stream, 

            f.name   AS fee_name,

            f.amount AS required_fee,

            COALESCE(SUM(p.amount), 0) AS total_paid

        FROM students s

        LEFT JOIN fees f 

            ON s.class = f.class

            AND (s.stream = f.stream OR f.stream IS NULL OR f.stream = '' OR s.stream = '')

            AND f.name = ?

        LEFT JOIN payment p 

            ON s.studentno = p.studentno

            AND p.pay_for = f.name

    ";



    $params = [$pay_for];

    $types  = "s";



    if (!empty($session)) { 

        $query .= " AND p.session = ? "; 

        $params[] = $session; 

        $types .= "s"; 

    }

    if (!empty($term)) { 

        $query .= " AND p.Term = ? "; 

        $params[] = $term; 

        $types .= "s"; 

    }



    $query .= " WHERE s.status < 3 ";



    if (!empty($class)) { 

        $query .= " AND s.class = ?"; 

        $params[] = $class; 

        $types .= "s"; 

    }

    if (!empty($section)) { 

        $query .= " AND s.section = ?"; 

        $params[] = $section; 

        $types .= "s"; 

    }



    $query .= " GROUP BY s.studentno, s.studentName, s.class, s.section, s.stream, f.name, f.amount 

                ORDER BY s.class, s.section, s.studentno";



    $stmt = $con->prepare($query);

    if ($stmt) {

        $stmt->bind_param($types, ...$params);

        $stmt->execute();

        $result = $stmt->get_result();

    }

}

?>







<!DOCTYPE html>

<html>

<?php @include("includes/head.php"); ?>

<body class="hold-transition sidebar-mini layout-fixed">

  <div class="wrapper">

    <?php @include("includes/header.php"); ?>

    <?php @include("includes/sidebar.php"); ?>



    <div class="content-wrapper">

      <!-- Page header -->

                   <section class="content-header premium-header">

    <div class="container-fluid">

        <div class="header-glass-card">

            <div class="row align-items-center">

                <div class="col-md-8">

                    <div class="title-section">

                        <div class="icon-box bg-primary-gradient shadow-sm">

                            <i class="fas fa-wallet"></i>

                        </div>

                        <div>

                            <h1 class="premium-title text-primary">

                                Payment Management

                                <span class="premium-subtitle text-secondary">Balance Report</span>

                            </h1>
                    <div class="mt-1">
                      <?php if ($currentSessionName): ?>
                        <span class="badge badge-primary mr-1">
                          <i class="fas fa-calendar-alt"></i>
                          <?= htmlspecialchars($currentSessionName) ?>
                        </span>
                      <?php endif; ?>
                      <?php if ($currentTermName): ?>
                        <span class="badge badge-success">
                          <i class="fas fa-bookmark"></i>
                          <?= htmlspecialchars($currentTermName) ?>
                        </span>
                      <?php else: ?>
                        <span class="badge badge-warning">
                          <i class="fas fa-exclamation-triangle"></i> No active term
                        </span>
                      <?php endif; ?>
                    </div>

                        </div>

                    </div>

                </div>

                <div class="col-md-4">

                    <nav aria-label="breadcrumb">

                        <ol class="breadcrumb premium-breadcrumb">

                            <li class="breadcrumb-item"><a href="dashboard.php" class="text-primary">Home</a></li>

                            <li class="breadcrumb-item active" aria-current="page">Payment History</li>

                        </ol>

                    </nav>

                </div>

            </div>

        </div>

    </div>

</section>





      <!-- Main content -->

      <section class="content">

        <div class="container-fluid">

          <div class="row">

            <div class="col-12">

              <div class="card card-primary">

                <div class="card-header">

                  <h5 class="card-title font-weight-bold">

                    <i class="fas fa-money-bill-wave"></i> Fee Balance Report

                  </h5>

                  <div class="card-tools">

                    <a href="viewpayment.php">

                      <button type="button" class="btn btn-sm btn-primary">

                        <span style="color: #fff;"><i class="fas fa-list"></i> Payment List</span>

                      </button>

                    </a>   

                  </div>

                </div>

                  <?php

                  // Capture submitted values

                  $selectedSession = $_POST['session'] ?? '';

                  $selectedTerm    = $_POST['Term'] ?? '';

                  $selectedClass   = $_POST['class'] ?? '';

                  $selectedArm     = $_POST['arm'] ?? '';

                  $selectedFee     = $_POST['pay_for'] ?? '';

                  ?>

                  <!-- Payment Filter Form -->

                  <div class="card-body">

                    <form method="POST" action="view_payment_percentage.php">

                      <div class="row">

                        <!-- Session -->

                        <div class="col-md-4">

                          <label for="session">Session</label>

                          <select class="form-control" id="session" name="session" required>

                            <option value="">Select Session</option>

                            <?php

                            $query3 = "SELECT session_name FROM school_sessions ORDER BY id DESC";

                            $result3 = $con->query($query3);

                            while ($row3 = $result3->fetch_assoc()) {

                                $sel = ($row3['session_name'] == $selectedSession) ? 'selected' : '';

                                echo "<option value='".htmlspecialchars($row3['session_name'])."' $sel>".htmlspecialchars($row3['session_name'])."</option>";

                            }

                            ?>

                          </select>

                        </div>



                        <!-- Term -->

                        <div class="col-md-4">

                          <label for="Term">Term</label>

                          <select class="form-control" id="Term" name="Term" required>

                            <option value="">Select Term</option>

                            <?php

                            $query4 = "SELECT DISTINCT term_name FROM school_terms ORDER BY term_name ASC";

                            $result4 = $con->query($query4);

                            while ($row4 = $result4->fetch_assoc()) {

                                $sel = ($row4['term_name'] == $selectedTerm) ? 'selected' : '';

                                echo "<option value='".htmlspecialchars($row4['term_name'])."' $sel>".htmlspecialchars($row4['term_name'])."</option>";

                            }

                            ?>

                          </select>

                        </div>



                        <!-- Class -->

                        <div class="col-md-4">

                          <label for="class">Class</label>

                          <select name="class" id="class" class="form-control" onchange="fetchArms()" required>

                            <option value="">Select Class</option>

                            <?php

                            $query2 = "SELECT DISTINCT class FROM class_section ORDER BY class";

                            $result2 = $con->query($query2);

                            while ($row2 = $result2->fetch_assoc()) {

                                $sel = ($row2['class'] == $selectedClass) ? 'selected' : '';

                                echo "<option value='".htmlspecialchars($row2['class'])."' $sel>".htmlspecialchars($row2['class'])."</option>";

                            }

                            ?>

                          </select>

                        </div>



                        <!-- Arm -->

                        <div class="col-md-4">

                          <label for="arm">Select Arm (Optional)</label>

                          <select name="arm" id="arm" class="form-control">

                            <option value="">Select Arm</option>

                            <?php

                            if ($selectedClass) {

                                $stmtArm = $con->prepare("SELECT DISTINCT section FROM class_section WHERE class = ?");

                                $stmtArm->bind_param("s", $selectedClass);

                                $stmtArm->execute();

                                $resultArm = $stmtArm->get_result();

                                while ($rowArm = $resultArm->fetch_assoc()) {

                                    $sel = ($rowArm['section'] == $selectedArm) ? 'selected' : '';

                                    echo "<option value='".htmlspecialchars($rowArm['section'])."' $sel>".htmlspecialchars($rowArm['section'])."</option>";

                                }

                                $stmtArm->close();

                            }

                            ?>

                          </select>

                        </div>



                        <!-- Fee -->

                        <div class="col-md-4">

                          <label for="pay_for">Fee Name</label>

                          <select name="pay_for" id="pay_for" class="form-control" required>

                            <option value="">Select Fee</option>

                            <?php

                            $query1 = "SELECT DISTINCT name FROM fees ORDER BY id";

                            $result1 = $con->query($query1);

                            while ($row1 = $result1->fetch_assoc()) {

                                $sel = ($row1['name'] == $selectedFee) ? 'selected' : '';

                                echo "<option value='".htmlspecialchars($row1['name'])."' $sel>".htmlspecialchars($row1['name'])."</option>";

                            }

                            ?>

                          </select>

                        </div>

                      </div>



                      <div class="col-md-12 text-center mt-3">

                        <button type="submit" class="btn btn-primary">

                          <i class="fas fa-search"></i> Search

                        </button>

                        <button type="button" class="btn btn-success" onclick="downloadFilteredPDF()">

                          <i class="fas fa-file-pdf"></i> Download PDF

                        </button>

                      </div>

                    </form>

                  </div>





                <!-- Payment Table -->

                <div class="card-body">

                  <table id="example1" class="table table-bordered table-hover">

                    <thead>

                      <tr>

                        <th>#</th>

                        <th>Admission No.</th>

                        <th>Student Name</th>

                        <th>Class</th>

                        <th>Arm</th>

                        <th>Payment For</th>

                        <th>Amount Paid</th>

                        <th>Required Fee</th>

                        <th>To Balance</th>

                        <th>% Paid</th>

                      </tr>

                    </thead>

                    <tbody>

                      <?php 

                        if ($result && $result->num_rows > 0) {

                              $cnt = 1;

                              while ($row = $result->fetch_assoc()) {

                                  $requiredFee = $row['required_fee'] ?? 0;

                                  $totalPaid   = $row['total_paid'] ?? 0;

                          // ✅ Apply School Fees logic

                          if ($pay_for == 'School Fees') {

                              $student_id = $row['admission_no'];

                              $class      = $row['class'];

                              $stream     = $row['stream'];

                              $currentSessionName = $selectedSession;

                              $currentTermName    = $selectedTerm;



                              // 1. Determine new/old

                              $payQuery = "SELECT pay_for FROM payment 

                                  WHERE studentno = ? 

                                  AND class = ? 

                                  AND school_type = ? 

                                  AND session = ? 

                                  AND Term = ? 

                                  AND pay_for LIKE 'School Fees%' 

                                  LIMIT 1";

                              $PaidStmt = $con->prepare($payQuery);

                              $PaidStmt->bind_param("sssss", $student_id, $class, $stream, $currentSessionName, $currentTermName);

                              $PaidStmt->execute();

                              $PaidResult = $PaidStmt->get_result();

                              $PaidRow = $PaidResult->fetch_assoc();

                              $studentPayFor = $PaidRow['pay_for'] ?? 'School Fees';

                              $student_type = ($studentPayFor == 'School Fees (New Student)') ? 'New' : 'Old';



                              // 2. Default fee

                              $feeQuery = "SELECT amount FROM fee_records 

                                  WHERE class = ? 

                                  AND stream = ? 

                                  AND name = ? 

                                  AND student_type = ? 

                                  AND session = ? 

                                  AND term = ? 

                                  ORDER BY created_at DESC 

                                  LIMIT 1";

                              $stmt2 = $con->prepare($feeQuery);

                              $stmt2->bind_param("ssssss", $class, $stream, $studentPayFor, $student_type, $currentSessionName, $currentTermName);

                              $stmt2->execute();

                              $feeResult = $stmt2->get_result();

                              $feeRow = $feeResult->fetch_assoc();

                              $requiredFee = $feeRow['amount'] ?? $requiredFee;



                              // 3. Special cases

                              if ($class === 'SS 1' && !empty($row['repeat']) && $row['repeat'] == 1 && $currentTermName === 'First Term') {

                                  $feeQuery = "SELECT amount FROM fee_records 

                                      WHERE class = ? 

                                      AND stream = ? 

                                      AND name = ? 

                                      AND student_type = ? 

                                      AND session = ? 

                                      AND term = ? 

                                      ORDER BY created_at DESC 

                                      LIMIT 1";

                                  $stmt3 = $con->prepare($feeQuery);

                                  $stmt3->bind_param("ssssss", $class, $stream, $studentPayFor, $student_type, $currentSessionName, $currentTermName);

                                  $stmt3->execute();

                                  $feeResult = $stmt3->get_result();

                                  $feeRow = $feeResult->fetch_assoc();

                                  $requiredFee = $feeRow['amount'] ?? $requiredFee;

                              }



                              if ($currentTermName === 'First Term' && in_array($class, ['JSS 3', 'SS 1', 'SS 3'])) {

                                  $special_fee_names = [

                                      'JSS 3' => 'First Term JSS 3 School Fees',

                                      'SS 1'  => 'First Term SS 1 School Fees',

                                      'SS 3'  => 'First Term SS 3 School Fees'

                                  ];

                                  $special_fee_name = $special_fee_names[$class];

                                  $feeQuery = "SELECT amount FROM fees 

                                      WHERE class = ? 

                                      AND stream = ? 

                                      AND student_type = 'Old' 

                                      AND name = ? 

                                      LIMIT 1";

                                  $stmt4 = $con->prepare($feeQuery);

                                  $stmt4->bind_param("sss", $class, $stream, $special_fee_name);

                                  $stmt4->execute();

                                  $feeResult = $stmt4->get_result();

                                  $feeRow = $feeResult->fetch_assoc();

                                  $requiredFee = $feeRow['amount'] ?? $requiredFee;

                              }

                          }



                          $balance = max(0, $requiredFee - $totalPaid);

                      ?>

                      <tr>

                        <td><?= $cnt; ?></td>

                        <td><?= htmlspecialchars($row['admission_no'] ?? $row['studentRegNo']); ?></td>

                        <td><?= htmlspecialchars($row['studentName']); ?></td>

                        <td><?= htmlspecialchars($row['class']); ?></td>

                        <td><?= htmlspecialchars($row['section'] ?? 'N/A'); ?></td>

                        <td><?= htmlspecialchars($row['fee_name'] ?? 'School Fees'); ?></td>

                        <td><?= number_format($totalPaid, 2); ?></td>

                        <td><?= number_format($requiredFee, 2); ?></td>

                        <td><?= number_format($balance, 2); ?></td>

                        <td><?= round(($totalPaid / max(1, $requiredFee)) * 100, 2) . "%"; ?></td>

                      </tr>

                    <?php 

                              $cnt++; 

                          } 

                      } else { ?>

                          <tr>

                            <td colspan="10" class="text-center">

                                <?php echo (!$result) ? "Please select a Class and Fee Name to generate the report." : "No records found for the selected criteria."; ?>

                            </td>

                          </tr>

                      <?php } ?>

                    </tbody>

                  </table>

                </div>



              </div>

            </div>

          </div>

        </div>

      </section>

    </div>



    <?php @include("includes/footer.php"); ?>

  </div>



  <?php @include("includes/foot.php"); ?>

  <script src="dist/js/select2.min.js"></script>

  <script>

    $(document).ready(function() {

      $('#class, #pay_for, #session, #Term, #arm').select2();

    });



    function fetchArms() {

      var classSelected = document.getElementById("class").value;

      var armDropdown = document.getElementById("arm");

      armDropdown.innerHTML = '<option value="">Select Arm</option>';



      if (classSelected) {

        fetch("fetch_arms.php?class=" + encodeURIComponent(classSelected))

          .then(response => response.json())

          .then(data => {

            data.forEach(arm => {

              let option = document.createElement("option");

              option.value = arm;

              option.textContent = arm;

              armDropdown.appendChild(option);

            });

          });

      }

    }



    function downloadFilteredPDF() {

      var section = document.getElementById("arm").value;

      var classs = document.getElementById("class").value;

      var pay_for = document.getElementById("pay_for").value;

      var Term = document.getElementById("Term").value;

      var session = document.getElementById("session").value;



      var url = "generate_pdf2.php?pay_for=" + encodeURIComponent(pay_for);



      if (classs) { url += "&class=" + encodeURIComponent(classs); }

      if (section) { url += "&arm=" + encodeURIComponent(section); }

      if (Term) { url += "&Term=" + encodeURIComponent(Term); }

      if (session) { url += "&session=" + encodeURIComponent(session); }



      window.location.href = url;

    }

  </script>

</body>

</html>

