<?php
if ($_SESSION['user_role'] < 2) {

    if ($_SESSION['user_role'] == 1) {
        $lvWhere = " u.leader='$_SESSION[emp_id]' and ";
        $isWhere = " u.leader='$_SESSION[emp_id]' and ";
    } else if ($_SESSION['user_role'] == 0) {
        $lvWhere = "";
        $isWhere = "";
    }

    $lv = <<<EOT
        SELECT * FROM
        (
            SELECT 
                rl.id
            FROM users AS u 
            JOIN request_leave AS rl ON u.employee_id = rl.employee_id 
            WHERE $lvWhere rl.leave_status='0' and
            (
                (
                    '$_SESSION[user_role]' = 0 AND u.leader = '$_SESSION[emp_id]'
                )
                OR
                (
                    ( u.leader <> '$_SESSION[emp_id]' OR '$_SESSION[user_role]' = 1 )
                    AND NOT
                    (
                        (
                            rl.leave_date LIKE '% to %'
                            AND '$_SESSION[user_role]' = 1
                            AND ABS(DATEDIFF(
                                    STR_TO_DATE(SUBSTRING_INDEX(rl.leave_date, ' to ', 1), '%d %b %Y'), 
                                    STR_TO_DATE(SUBSTRING_INDEX(rl.leave_date, ' to ', -1), '%d %b %Y')
                                )) > 2
                        )
                        OR
                        ( rl.leave_date NOT LIKE '% to %' AND '$_SESSION[user_role]' = 0 AND TRIM(LOWER(rl.leave_type)) NOT IN ('work from home', 'holiday') )
                        OR 
                        (
                            rl.leave_date LIKE '% to %'
                            AND '$_SESSION[user_role]' = 0
                            AND ABS(DATEDIFF(
                                    STR_TO_DATE(SUBSTRING_INDEX(rl.leave_date, ' to ', 1), '%d %b %Y'), 
                                    STR_TO_DATE(SUBSTRING_INDEX(rl.leave_date, ' to ', -1), '%d %b %Y')
                                )) <= 2
                        )
                    )
                )
            )
            AND NOT (
                $_SESSION[user_role] = 1 
                AND TRIM(LOWER(rl.leave_type)) IN ('work from home', 'holiday')
            )
        ) join_table
    EOT;

   
    $is = <<<EOT
        SELECT * FROM
        (
            SELECT 
                rl.id
            FROM users AS u 
            JOIN issue_with_date AS rl ON u.employee_id = rl.employee_id
            WHERE $isWhere rl.issue_status='0' and 
            (
                (
                    '$_SESSION[user_role]' = 0 AND u.leader = '$_SESSION[emp_id]'
                )
                OR
                (
                    ( u.leader <> '$_SESSION[emp_id]' OR '$_SESSION[user_role]' = 1 )
                    AND NOT
                    (
                        (
                            rl.issue_date LIKE '% to %'
                            AND '$_SESSION[user_role]' = 1
                            AND TIMESTAMPDIFF(DAY,
                                        STR_TO_DATE(SUBSTRING_INDEX(rl.issue_date, ' to ', 1), '%d %b %Y'), 
                                        STR_TO_DATE(SUBSTRING_INDEX(rl.issue_date, ' to ', -1), '%d %b %Y')
                                    ) > 2
                        )
                        OR
                        ( rl.issue_date NOT LIKE '% to %' AND '$_SESSION[user_role]' = 0 AND TRIM(LOWER(rl.request_for)) NOT IN ('work from home', 'holiday') )
                        OR (
                            rl.issue_date LIKE '% to %'
                            AND '$_SESSION[user_role]' = 0
                            AND TIMESTAMPDIFF(DAY,
                                        STR_TO_DATE(SUBSTRING_INDEX(rl.issue_date, ' to ', 1), '%d %b %Y'), 
                                        STR_TO_DATE(SUBSTRING_INDEX(rl.issue_date, ' to ', -1), '%d %b %Y')
                                    ) <= 2
                        )
                    
                    )
                )
            )
            AND NOT (
                $_SESSION[user_role] = 1 
                AND TRIM(LOWER(rl.issue_type)) IN ('work from home', 'holiday')
            )
        ) join_table
    EOT;

    $lvNotCount = mysqli_num_rows(mysqli_query($db,$lv));
    $isNotCount = mysqli_num_rows(mysqli_query($db, $is));
}

$letters_approval_counts_sql = <<<EOT
    SELECT 
        count(letter_type) as count
    FROM (
        SELECT 'Full Time' AS letter_type, '12' AS letter_id, approved_by
            FROM full_time_letter
        UNION ALL

        SELECT 'Internship' AS letter_type, '10' AS letter_id, approved_by
            FROM internship_letter
        UNION ALL

        SELECT 'Internship Complete' AS letter_type, '11' AS letter_id, approved_by
            FROM internship_complete_letter
        UNION ALL

        SELECT 'Appraisal' AS letter_type, '13' AS letter_id, approved_by
            FROM appraisal_letter
        UNION ALL

        SELECT 'Relieving' AS letter_type, '14' AS letter_id, t1.approved_by
            FROM relieving_letter t1
            INNER JOIN users t2 ON t1.employee_id=t2.employee_id
        WHERE COALESCE(t2.working_status_date,'')<>''
    ) as all_letters
    WHERE JSON_UNQUOTE(
        JSON_EXTRACT(
            JSON_EXTRACT(approved_by, CONCAT('$[', JSON_LENGTH(approved_by) - 1, ']')), '$.$_SESSION[emp_id].status'
        )
    )=0
EOT;
$letters_approval_pending_total = mysqli_fetch_assoc(mysqli_query($db, $letters_approval_counts_sql))['count'];
?>


<!-- ========== Left Sidebar Start ========== -->
<div class="left-side-menu">
    <div class="media user-profile mt-2 mb-2">
        <img id="sidebarImage" onerror="this.onerror=null;this.src='<?php echo $cdn_url; ?>content/employee/images/avatar.png';" src="<?php echo $cdn_url; ?>content/employee/images/<?php echo $_SESSION['image']; ?>" class="avatar-sm rounded-circle mr-2" alt="Shreyu" />
        <!-- <img src="assets/images/users/avatar-7.jpg" class="avatar-xs rounded-circle mr-2" alt="Shreyu" /> -->

        <div class="media-body">
            <h6 class="pro-user-name mt-0 mb-0 d-block"><?php echo $_SESSION['user_name']; ?></h6>
            <span class="pro-user-desc">
                <?php if ($_SESSION['user_role'] == 0) { ?>
                    Administrator
                <?php } else if ($_SESSION['user_role'] == 1) { ?>
                    Manager
                <?php } else { ?>
                    Employee
                <?php } ?>
            </span>
        </div>
        
    </div>
    <div class="sidebar-content">
        <!--- Sidemenu -->
        <div id="sidebar-menu" class="slimscroll-menu">
            <ul class="metismenu" id="menu-bar">
                <li class="menu-title">Navigation</li>

                <?php
                    foreach ($files as $parent => $children) {

                        $parent_file = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM emp_files WHERE position='left' and id='$parent'"));
                        if(empty($parent_file)){
                            continue;
                        }
                        
                        $children_size = sizeof($children);
                        $parent_link = ($children_size>0)?"javascript: void(0);":"$parent_file[page]";
                        $parent_menu_arrow = ($children_size>0)?'<span class="menu-arrow"></span>':"";

                        $paySlipRequest = '';
                        if($_SESSION['user_role']=='0' && $parent_file['id']=='28'){
                            $ps = mysqli_num_rows(mysqli_query($db, "select * from request_slip where status=0 "));
                            $paySlipRequest = '<span class="btn btn-danger badge" id="" data-toggle="tooltip" data-placement="top" title="Total no of Requests">'.$ps.'</span>';
                        }

                        $all_issueDate = '';
                        if ($_SESSION['user_role'] < 2 && $parent_file['id']=='30') {
                            $all_issueDate = '<span class="btn btn-danger badge" id="" data-toggle="tooltip" data-placement="top" title="Total no of Requests">'.$isNotCount.'</span>';
                        }
                        $all_leaveRequest = '';
                        if ($_SESSION['user_role'] < 2 && $parent_file['id']=='33') {
                            $all_leaveRequest = '<span class="btn btn-danger badge" id="" data-toggle="tooltip" data-placement="top" title="Total no of Requests">'.$lvNotCount.'</span>';
                        }

                        $all_letters = '';
                        if($parent_file['id']=='8' && $letters_approval_pending_total){
                            $all_letters = '<span class="btn btn-danger badge" id="" data-toggle="tooltip" data-placement="top" title="Total no of Requests">'.$letters_approval_pending_total.'</span>';
                        }

                        echo <<<EOT
                            <li>
                                <a href="$parent_link">
                                    <i $parent_file[icon]></i>
                                    <span> $parent_file[name] </span>
                                    $parent_menu_arrow
                                    $paySlipRequest
                                    $all_issueDate
                                    $all_leaveRequest
                                    $all_letters
                                </a>
                        EOT;
                        if($children_size>0){
                            echo '<ul class="nav-second-level" aria-expanded="false">';
                            foreach ($children as $child) {
                                $child_file = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM emp_files WHERE position='left' and id='$child'"));

                                $all_issueDate = '';
                                if ($_SESSION['user_role'] < 2 && $child_file['id']=='32') {
                                    $all_issueDate = '<span class="btn btn-danger badge" id="" data-toggle="tooltip" data-placement="top" title="Total no of Requests">'.$isNotCount.'</span>';
                                }
                                $all_leaveRequest = '';
                                if ($_SESSION['user_role'] < 2 && $child_file['id']=='35') {
                                    $all_leaveRequest = '<span class="btn btn-danger badge" id="" data-toggle="tooltip" data-placement="top" title="Total no of Requests">'.$lvNotCount.'</span>';
                                }

                                $letter = '';
                                if($child_file['id']=='9' && $letters_approval_pending_total){
                                    $letter = '<span class="btn btn-danger badge" id="" data-toggle="tooltip" data-placement="top" title="Total no of Requests">'.$letters_approval_pending_total.'</span>';
                                }

                                echo <<<EOT
                                    <li>
                                        <a href="$child_file[page]">
                                            <span>$child_file[name]</span>
                                            $all_issueDate
                                            $all_leaveRequest
                                            $letter
                                        </a>
                                    </li>
                                EOT;
                            }
                            echo '</ul>';
                        }
                        echo <<<EOT
                            </li>
                        EOT;
                    }
                ?>


            </ul>
        </div>
        <!-- End Sidebar -->

        <div class="clearfix"></div>
    </div>
    <!-- Sidebar -left -->

</div>
<!-- Left Sidebar End -->