<?php

$pageTitle = "Monthly Clearance Report";

require_once '../includes/auth.php';

require_login();

$user = current_user();

/*
|--------------------------------------------------------------------------
| SUMMARY
|--------------------------------------------------------------------------
*/

$totalClearances = $db->query("

SELECT COUNT(*)

FROM monthly_clearance

")->fetchColumn();

$pending = $db->query("

SELECT COUNT(*)

FROM monthly_clearance

WHERE status='Pending'

")->fetchColumn();

$approved = $db->query("

SELECT COUNT(*)

FROM monthly_clearance

WHERE status='Approved'

")->fetchColumn();

$rejected = $db->query("

SELECT COUNT(*)

FROM monthly_clearance

WHERE status='Rejected'

")->fetchColumn();

/*
|--------------------------------------------------------------------------
| FETCH CLEARANCE RECORDS
|--------------------------------------------------------------------------
*/

$stmt = $db->query("

SELECT

mc.*,

cm.firstname,

cm.lastname,

cm.state_code,

cm.call_up_number,

cg.group_name,

CONCAT(

co.firstname,

' ',

co.lastname

) approved_by_name

FROM monthly_clearance mc

INNER JOIN corps_members cm

ON cm.id=mc.corps_member_id

LEFT JOIN cds_group_members cgm

ON cgm.corps_member_id=cm.id

AND cgm.status='Active'

LEFT JOIN cds_groups cg

ON cg.id=cgm.cds_group_id

LEFT JOIN camp_officials co

ON co.id=mc.approved_by

ORDER BY

mc.clearance_year DESC,

mc.clearance_month DESC,

cm.firstname

");

$records=$stmt->fetchAll(PDO::FETCH_ASSOC);

require_once '../includes/header.php';
require_once '../includes/sidebar.php';

?>

<main class="dashboard-main">

    <?php require_once '../includes/topbar.php'; ?>

    <div class="dashboard-main-body">

        <div class="breadcrumb d-flex flex-wrap align-items-center justify-content-between gap-3 mb-24">

            <div>

                <h6 class="fw-semibold mb-0">

                    <?= $pageTitle ?>

                </h6>

                <p class="text-neutral-500">

                    <?= APP_SHORT_NAME ?>

                    >

                    Reports

                    >

                    Monthly Clearance Report

                </p>

            </div>

            <div>

                <a href="export-monthly-clearance.php" class="btn btn-success">

                    <i class="ri-file-excel-line"></i>

                    Export CSV

                </a>

                <a href="print-monthly-clearance.php" target="_blank" class="btn btn-primary">

                    <i class="ri-printer-line"></i>

                    Print Report

                </a>

            </div>

        </div>

        <div class="row gy-4 mb-24">

            <div class="col-lg-3">

                <div class="card">

                    <div class="card-body">

                        <h6>Total Clearances</h6>

                        <h2><?= $totalClearances ?></h2>

                    </div>

                </div>

            </div>

            <div class="col-lg-3">

                <div class="card">

                    <div class="card-body">

                        <h6>Pending</h6>

                        <h2 class="text-warning">

                            <?= $pending ?>

                        </h2>

                    </div>

                </div>

            </div>

            <div class="col-lg-3">

                <div class="card">

                    <div class="card-body">

                        <h6>Approved</h6>

                        <h2 class="text-success">

                            <?= $approved ?>

                        </h2>

                    </div>

                </div>

            </div>

            <div class="col-lg-3">

                <div class="card">

                    <div class="card-body">

                        <h6>Rejected</h6>

                        <h2 class="text-danger">

                            <?= $rejected ?>

                        </h2>

                    </div>

                </div>

            </div>

        </div>

        <div class="card">

            <div class="card-header">

                <h6>

                    Monthly Clearance Report

                </h6>

            </div>

            <div class="card-body">

                <table class="table bordered-table data-table" id="dataTable">

                    <thead>

                        <tr>

                            <th>#</th>

                            <th>State Code</th>

                            <th>Corps Member</th>

                            <th>CDS Group</th>

                            <th>Month</th>

                            <th>Year</th>

                            <th>Status</th>

                            <th>Action</th>

                        </tr>

                    </thead>

                    <tbody>

                        <?php

$count=1;

foreach($records as $record):

?>

                        <tr>

                            <td>

                                <?= $count++ ?>

                            </td>

                            <td>

                                <?= htmlspecialchars($record['state_code']) ?>

                            </td>

                            <td>

                                <?= htmlspecialchars($record['firstname'].' '.$record['lastname']) ?>

                            </td>

                            <td>

                                <?= htmlspecialchars($record['group_name']) ?>

                            </td>

                            <td>

                                <?= date(

'F',

mktime(

0,

0,

0,

$record['clearance_month'],

1

)

) ?>

                            </td>

                            <td>

                                <?= $record['clearance_year'] ?>

                            </td>

                            <td>

                                <?php

switch($record['status']){

case 'Pending':

echo '<span class="badge bg-warning">Pending</span>';

break;

case 'Approved':

echo '<span class="badge bg-success">Approved</span>';

break;

default:

echo '<span class="badge bg-danger">Rejected</span>';

}

?>

                            </td>

                            <td>

                                <div class="dropdown">

                                    <button class="btn btn-light" data-bs-toggle="dropdown">

                                        <i class="ri-more-2-fill"></i>

                                    </button>

                                    <ul class="dropdown-menu">

                                        <li>

                                            <button class="dropdown-item view-clearance-btn"
                                                data-name="<?= htmlspecialchars($record['firstname'].' '.$record['lastname']) ?>"
                                                data-state="<?= htmlspecialchars($record['state_code']) ?>"
                                                data-callup="<?= htmlspecialchars($record['call_up_number']) ?>"
                                                data-group="<?= htmlspecialchars($record['group_name']) ?>"
                                                data-month="<?= date('F',mktime(0,0,0,$record['clearance_month'],1)) ?>"
                                                data-year="<?= $record['clearance_year'] ?>"
                                                data-status="<?= $record['status'] ?>"
                                                data-cds="<?= $record['cds_attendance_verified'] ?>"
                                                data-camp="<?= $record['camp_activity_verified'] ?>"
                                                data-bio="<?= $record['biometric_verified'] ?>"
                                                data-submitted="<?= $record['submitted_date'] ?>"
                                                data-approved="<?= $record['approval_date'] ?>"
                                                data-approvedby="<?= htmlspecialchars($record['approved_by_name']) ?>"
                                                data-remarks="<?= htmlspecialchars($record['remarks']) ?>"
                                                data-bs-toggle="modal" data-bs-target="#viewClearanceModal">

                                                <i class="ri-eye-line"></i>

                                                View

                                            </button>

                                        </li>

                                        <li>

                                            <a href="print-clearance.php?id=<?= $record['id'] ?>" target="_blank"
                                                class="dropdown-item">

                                                <i class="ri-printer-line"></i>

                                                Print

                                            </a>

                                        </li>

                                    </ul>

                                </div>

                            </td>

                        </tr>

                        <?php endforeach; ?>

                    </tbody>

                </table>

            </div>

        </div>

        <!-- ======================================================= -->
        <!-- VIEW MONTHLY CLEARANCE -->
        <!-- ======================================================= -->

        <div class="modal fade" id="viewClearanceModal" tabindex="-1">

            <div class="modal-dialog modal-lg">

                <div class="modal-content">

                    <div class="modal-header">

                        <h5 class="modal-title">

                            Monthly Clearance Details

                        </h5>

                        <button class="btn-close" data-bs-dismiss="modal">

                        </button>

                    </div>

                    <div class="modal-body">

                        <table class="table table-bordered">

                            <tr>

                                <th width="35%">

                                    Corps Member

                                </th>

                                <td id="viewMember"></td>

                            </tr>

                            <tr>

                                <th>

                                    State Code

                                </th>

                                <td id="viewStateCode"></td>

                            </tr>

                            <tr>

                                <th>

                                    Call-Up Number

                                </th>

                                <td id="viewCallUp"></td>

                            </tr>

                            <tr>

                                <th>

                                    CDS Group

                                </th>

                                <td id="viewGroup"></td>

                            </tr>

                            <tr>

                                <th>

                                    Clearance Month

                                </th>

                                <td id="viewMonth"></td>

                            </tr>

                            <tr>

                                <th>

                                    Clearance Year

                                </th>

                                <td id="viewYear"></td>

                            </tr>

                            <tr>

                                <th>

                                    CDS Attendance Verified

                                </th>

                                <td id="viewCDS"></td>

                            </tr>

                            <tr>

                                <th>

                                    Camp Activity Verified

                                </th>

                                <td id="viewCamp"></td>

                            </tr>

                            <tr>

                                <th>

                                    Biometric Verified

                                </th>

                                <td id="viewBiometric"></td>

                            </tr>

                            <tr>

                                <th>

                                    Submitted Date

                                </th>

                                <td id="viewSubmitted"></td>

                            </tr>

                            <tr>

                                <th>

                                    Approval Date

                                </th>

                                <td id="viewApproved"></td>

                            </tr>

                            <tr>

                                <th>

                                    Approved By

                                </th>

                                <td id="viewApprovedBy"></td>

                            </tr>

                            <tr>

                                <th>

                                    Status

                                </th>

                                <td id="viewStatus"></td>

                            </tr>

                            <tr>

                                <th>

                                    Remarks

                                </th>

                                <td id="viewRemarks"></td>

                            </tr>

                        </table>

                    </div>

                </div>

            </div>

        </div>

        <?php require_once "../includes/footer.php"; ?>

        <script>
        $(document).on(

            'click',

            '.view-clearance-btn',

            function() {

                $('#viewMember').text($(this).data('name'));

                $('#viewStateCode').text($(this).data('state'));

                $('#viewCallUp').text($(this).data('callup'));

                $('#viewGroup').text($(this).data('group'));

                $('#viewMonth').text($(this).data('month'));

                $('#viewYear').text($(this).data('year'));

                $('#viewCDS').text($(this).data('cds'));

                $('#viewCamp').text($(this).data('camp'));

                $('#viewBiometric').text($(this).data('bio'));

                $('#viewSubmitted').text($(this).data('submitted'));

                $('#viewApproved').text(

                    $(this).data('approved') || '-'

                );

                $('#viewApprovedBy').text(

                    $(this).data('approvedby') || '-'

                );

                $('#viewRemarks').text(

                    $(this).data('remarks') || '-'

                );

                let status = $(this).data('status');

                let badge = '';

                switch (status) {

                    case 'Pending':

                        badge = '<span class="badge bg-warning">Pending</span>';

                        break;

                    case 'Approved':

                        badge = '<span class="badge bg-success">Approved</span>';

                        break;

                    default:

                        badge = '<span class="badge bg-danger">Rejected</span>';

                }

                $('#viewStatus').html(badge);

            });
        </script>