<?php
include "pin/header.php";
include "pin/sidenav.php";

$user_id = $_SESSION['user_id'];
$emp_id = $_SESSION['emp_id'];
$user_role = $_SESSION['user_role'];

if(isset($_GET['month'])){
    $selected_month = $_GET['month'];
} else {
    $selected_month = date('Y-m');
}

?>

<style>
    tr.active-employee {
        background-color: #d4edda !important;
    }
    tr.not-active-employee {
        background-color: #f8d7da !important;
    }
</style>

<!-- ============================================================== -->
<!-- Start Page Content here -->
<!-- ============================================================== -->

<div class="content-page">
    <div class="content">
        <div class="container-fluid">

            <div class="page-title"></div>

            <div class="row mb-3" id="filter_block">
                
                <div class="col-md-3">
                    <div class="form-group">
                        <label class="form-label" for="month">Month</label>
                        <input type="month" id="month" name="month" class="form-control" placeholder="Select Month" value="<?php echo $selected_month; ?>">
                    </div>
                </div>
            </div>

            <div class="row">
                <div class="col-12">
                    <div class="card">
                        <div class="card-body p-4">
                            <div class="d-flex justify-content-between">
                                <h4 class="header-title mt-1 mb-4">
                                    On/Off Boarding
                                </h4>
                            </div>


                            <div class="row">
                                <div class="col-12">
                                    <table id="basic-datatable" class="table dt-responsive nowrap">
                                        <thead>
                                            <tr>
                                                <th>ID</th>
                                                <th>Name</th>
                                                <th>Email</th>
                                                <th>Phone</th>
                                                <th>Designation</th>
                                                <th>Joining Date</th>
                                                <th>Last Working Date</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php
                                                $sql = "SELECT 
                                                            t1.id, t1.name, t1.email, t1.phone, t1.joining_date, t1.working_status_date,
                                                            t4.designation_name,
                                                            DATE_FORMAT(t1.joining_date, '%Y-%m') AS joining_month,
                                                            DATE_FORMAT(t1.working_status_date, '%Y-%m') AS working_status_month
                                                        FROM (
                                                            SELECT 
                                                                p1.*,
                                                                (CASE
                                                                WHEN COALESCE(p2.promote_to,'')<>'' THEN p2.promote_to
                                                                ELSE p1.designation
                                                                END) AS new_designation
                                                            FROM users p1
                                                            LEFT JOIN (
                                                                SELECT
                                                                    *
                                                                FROM (
                                                                    SELECT 
                                                                        id,
                                                                        employee_id, 
                                                                        promote_to, 
                                                                        applicable_from, 
                                                                        (CASE 
                                                                        WHEN COALESCE(applicable_to,'')='' THEN DATE_FORMAT(CURRENT_DATE, '%Y-%m')
                                                                        ELSE applicable_to
                                                                        END) as applicable_to 
                                                                    FROM promotions
                                                                ) final_promotions
                                                                WHERE (DATE_FORMAT(CURRENT_DATE, '%Y-%m') BETWEEN applicable_from AND applicable_to)
                                                            ) p2 ON p1.employee_id=p2.employee_id
                                                        ) t1
                                                        LEFT JOIN designations AS t4 ON t4.id=t1.new_designation
                                                        WHERE 
                                                            (DATE_FORMAT(t1.joining_date, '%Y-%m') = '$selected_month'
                                                            OR DATE_FORMAT(t1.working_status_date, '%Y-%m') = '$selected_month')";
                                                $result = mysqli_query($db, $sql);
                                                while ($row = mysqli_fetch_assoc($result)) {
                                            ?>
                                                <tr class="<?php echo empty($row['working_status_date'])?'not-active-employee':'active-employee'; ?>">
                                                    <td><?php echo $row['id']; ?></td>
                                                    <td><?php echo $row['name']; ?></td>
                                                    <td><?php echo $row['email']; ?></td>
                                                    <td><?php echo $row['phone']; ?></td>
                                                    <td><?php echo $row['designation_name']; ?></td>
                                                    <td><?php echo $row['joining_date']; ?></td>
                                                    <td><?php echo $row['working_status_date']; ?></td>
                                                </tr>
                                            <?php } ?>
                                        </tbody>

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

                        </div> <!-- end card body-->
                    </div> <!-- end card -->
                </div><!-- end col-->
            </div>
            <!-- end row-->

        </div>
    </div> <!-- content -->

</div>


<?php
include "pin/footer.php";
?>
<script>
    $('#month').on('change', function() {
        var selectedMonth = $(this).val();
        window.location.href = 'on-off-boarding.php?month=' + selectedMonth;
    });

    $(document).ready(function () {
        $('#basic-datatable').DataTable({
            paging: false,
            searching: true,
            ordering: true,
            info: true,
            order: [[0, 'desc']]
        });
    });
</script>
