<?php

require '../config.php';

// usersSelect.js
if(isset($_GET['employees'])){

    $search_key = $_GET['employees'];
    $param_data = json_decode($_GET['param_data'], true);
    $default = $param_data['default'];
    $currentPageName = $param_data['current_page'];
    $month = $param_data['month'] ?? '';
    $first_time = $_GET['first_time'] ?? 'false';

    $where = ["1"];

    // resigned not show in => 
    $resigned_not_show = array('notALeave', 'all_password', 'addSalary', 'add_assets', 'attendance');
    $users = '';
    if(in_array($currentPageName,$resigned_not_show)){

        $today_date = (isset($_GET['month']) && !empty($_GET['month']))?date("Y-m",strtotime($_GET['month'])):date("Y-m");
        if ($user_role == 0) {
            // $users = mysqli_query($db, "SELECT name,employee_id from users where (COALESCE(joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(joining_date, '%Y-%m-%d'),'%Y-%m') <= '$today_date') AND (COALESCE(working_status_date,'')='' or DATE_FORMAT(STR_TO_DATE(working_status_date, '%Y-%m-%d'),'%Y-%m')>='$today_date') ");
            $where[] = "(COALESCE(joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(joining_date, '%Y-%m-%d'),'%Y-%m') <= '$today_date')";
            $where[] = "(COALESCE(working_status_date,'')='' or DATE_FORMAT(STR_TO_DATE(working_status_date, '%Y-%m-%d'),'%Y-%m')>='$today_date')";
        } else if ($user_role == 1 && $currentPageName=='notALeave') {
            // $users = mysqli_query($db, "SELECT name,employee_id from users where (COALESCE(joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(joining_date, '%Y-%m-%d'),'%Y-%m') <= '$today_date') AND (COALESCE(working_status_date,'')='' or DATE_FORMAT(STR_TO_DATE(working_status_date, '%Y-%m-%d'),'%Y-%m')>='$today_date') and leader='$emp_id'");
            $where[] = "(COALESCE(joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(joining_date, '%Y-%m-%d'),'%Y-%m') <= '$today_date')";
            $where[] = "(COALESCE(working_status_date,'')='' or DATE_FORMAT(STR_TO_DATE(working_status_date, '%Y-%m-%d'),'%Y-%m')>='$today_date')";
            $where[] = "leader='$emp_id'";
        } else if ($user_role == 1) {
            // $users = mysqli_query($db, "SELECT name,employee_id from users where (COALESCE(joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(joining_date, '%Y-%m-%d'),'%Y-%m') <= '$today_date') AND (COALESCE(working_status_date,'')='' or DATE_FORMAT(STR_TO_DATE(working_status_date, '%Y-%m-%d'),'%Y-%m')>='$today_date') and leader='$emp_id' OR employee_id='$emp_id'");
            $where[] = "(COALESCE(joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(joining_date, '%Y-%m-%d'),'%Y-%m') <= '$today_date')";
            $where[] = "(COALESCE(working_status_date,'')='' or DATE_FORMAT(STR_TO_DATE(working_status_date, '%Y-%m-%d'),'%Y-%m')>='$today_date')";
            $where[] = "(leader='$emp_id' OR employee_id='$emp_id')";
        } else {
        }
    }else if(in_array($currentPageName, ['relieving_letter'])){
        // $users = mysqli_query($db, "SELECT name,employee_id from users where COALESCE(working_status_date,'')<>'' AND working_status<>'Abscond';");
        $where[] = "COALESCE(working_status_date,'')<>'' AND working_status<>'Abscond'";
    }else{
        if ($user_role == 0) {
            // $users = mysqli_query($db, "SELECT name,employee_id from users ");
        } else if ($user_role == 1 && $currentPageName== "notALeave") {
            // $users = mysqli_query($db, "SELECT name,employee_id from users where leader='$emp_id'");
            $where[] = "leader='$emp_id'";
        } else if ($user_role == 1) {
            // $users = mysqli_query($db, "SELECT name,employee_id from users where leader='$emp_id' OR employee_id='$emp_id'");
            $where[] = "(leader='$emp_id' OR employee_id='$emp_id')";
        }
    }
    if(!empty($search_key)){
        $where[] = "(name LIKE '%$search_key%' OR email LIKE '%$search_key%' OR phone LIKE '%$search_key%' OR employee_id LIKE '%$search_key%')";
    }
    $where_sql = implode(" AND ", $where);
    if($first_time=='true'){
        $default_esc = mysqli_real_escape_string($db, $default);
        // $sql = "
        // (
        //     SELECT * 
        //     FROM users 
        //     WHERE $where_sql
        //     ORDER BY name ASC
        //     LIMIT 10
        // )
        // UNION
        // (
        //     SELECT * 
        //     FROM users 
        //     WHERE employee_id = '$default_esc'
        // )
        // ORDER BY name ASC
        // ";
        $sql= "SELECT * FROM users where $where_sql ORDER BY name ASC";
    }else{
        $sql = "SELECT * FROM users where $where_sql ORDER BY name ASC limit 10";
    }
    // echo $sql;
    // exit();
    $users = mysqli_query($db, $sql);

    $option = [];
    $show_all = ['notALeave','leave_policy','work_policy'];
    if (in_array($currentPageName,$show_all) && $user_role == 0) {
        $option[] = ['id' => 'all', 'title' => 'All Employees'];
    }
    while ($user = mysqli_fetch_assoc($users)) {

        $selected = (($default_id == $user['employee_id']) ? 'selected' : '');
        $option[] = ['id' => $user['employee_id'], 'title' => "($user[name])-($user[employee_id])"];
    }
    echo json_encode($option);
    exit();
}

if(isset($_GET['managers'])){
    $search_key = $_GET['managers'];
    $param_data = json_decode($_GET['param_data'], true);
    $default = $param_data['default'];
    $currentPageName = $param_data['current_page'];
    $first_time = $_GET['first_time'] ?? 'false';

    $where = ["1"];
    if ($user_role == 0) {
        $where[] = "role <> '2'";
    } else if ($user_role == 1) {
        $where[] = "id='$user_id'";
    } else {
    }
    if(!empty($search_key)){
        $where[] = "(name LIKE '%$search_key%' OR email LIKE '%$search_key%' OR phone LIKE '%$search_key%' OR employee_id LIKE '%$search_key%')";
    }
    $where_sql = implode(" AND ", $where);
    if($first_time=='true'){
        $default_esc = mysqli_real_escape_string($db, $default);

        // $sql = "
        // (
        //     SELECT * 
        //     FROM users 
        //     WHERE $where_sql
        //     ORDER BY name ASC
        //     LIMIT 10
        // )
        // UNION
        // (
        //     SELECT * 
        //     FROM users 
        //     WHERE employee_id = '$default_esc'
        // )
        // ORDER BY name ASC
        // ";
        $sql= "SELECT * FROM users where $where_sql ORDER BY name ASC";
    }else{
        $sql = "SELECT * FROM users where $where_sql ORDER BY name ASC limit 10";
    }
    $users = mysqli_query($db, $sql);
    $option = [];
    while ($user = mysqli_fetch_assoc($users)) {
        $option[] = ['id' => $user['employee_id'], 'title' => "($user[name])-($user[email])-($user[phone])-($user[employee_id])"];
    }
    echo json_encode($option);
    exit();
}

if(isset($_GET['departments'])){
    $search_key = $_GET['departments'];
    $param_data = json_decode($_GET['param_data'], true);
    $default = $param_data['default'];
    $currentPageName = $param_data['current_page'];
    $first_time = $_GET['first_time'] ?? 'false';

    $where = ["1"];

    if(!empty($search_key)){
        $where[] = "department_name LIKE '%$search_key%'";
    }
    $where_sql = implode(" AND ", $where);
    if($first_time=='true'){
        $default_esc = mysqli_real_escape_string($db, $default);
        // $sql = "
        // (
        //     SELECT * 
        //     FROM departments 
        //     WHERE $where_sql
        //     ORDER BY department_name ASC
        //     LIMIT 10
        // )
        // UNION
        // (
        //     SELECT * 
        //     FROM departments 
        //     WHERE id = '$default_esc'
        // )
        // ORDER BY department_name ASC
        // ";
        $sql= "SELECT * FROM departments WHERE $where_sql ORDER BY department_name ASC";
    }else{
        $sql = "SELECT * FROM departments WHERE $where_sql ORDER BY department_name ASC limit 10";
    }
    $departments = mysqli_query($db, $sql);

    $option = [];

    if($currentPageName == 'leave_policy'){
        $option[] = ["id" => "all", "title" => "All"];
    }

    while ($d = mysqli_fetch_assoc($departments)) {
        $option[] = ["id" => $d['id'], "title" => $d['department_name']];
    }
    echo json_encode($option);
    exit();
}

if(isset($_GET['designations'])){
    
    $search_key = $_GET['designations'];
    $param_data = json_decode($_GET['param_data'], true);
    $default = $param_data['default'];
    $currentPageName = $param_data['current_page'];
    $first_time = $_GET['first_time'] ?? 'false';

    $where = ["1"];
    if(!empty($search_key)){
        // $designations = mysqli_query($db, "SELECT * FROM designations WHERE designation_name LIKE '%$search_key%' ORDER BY designation_name ASC");
        $where[] = "designation_name LIKE '%$search_key%'";
    }
    $where_sql = implode(" AND ", $where);
    if($first_time=='true'){
        $default_esc = mysqli_real_escape_string($db, $default);
        // $sql = "
        // (
        //     SELECT * 
        //     FROM designations 
        //     WHERE $where_sql
        //     ORDER BY designation_name ASC
        //     LIMIT 10
        // )
        // UNION
        // (   
        //     SELECT * 
        //     FROM designations 
        //     WHERE id = '$default_esc'
        // )
        // ORDER BY designation_name ASC
        // ";
        $sql= "SELECT * FROM designations WHERE $where_sql ORDER BY designation_name ASC";
    }else{
        $sql = "SELECT * FROM designations WHERE $where_sql ORDER BY designation_name ASC limit 10";
    }
    $designations = mysqli_query($db, $sql);
    $option = [];
    while ($d = mysqli_fetch_assoc($designations)) {
        $option[] = ["id" => $d['id'], "title" => $d['designation_name']];
    }
    echo json_encode($option);
    exit();
}


if(isset($_GET['loi_ids'])){

    $search_key = $_GET['loi_ids'];
    $param_data = json_decode($_GET['param_data'], true);
    $default = $param_data['default'];
    $currentPageName = $param_data['current_page'];
    $first_time = $_GET['first_time'] ?? 'false';

    $where = ["1"];
    if(!empty($search_key)){
        $where[] = "(loi_reference_id LIKE '%$search_key%' OR applicant_name LIKE '%$search_key%' OR applicant_email LIKE '%$search_key%' OR applicant_mobile LIKE '%$search_key%')";
    }

    $where_sql = implode(" AND ", $where);
    if($first_time=='true'){
        $default_esc = mysqli_real_escape_string($db, $default);
        // $sql = "
        // (
        //     SELECT * 
        //     FROM letters_of_intent 
        //     WHERE $where_sql
        //     ORDER BY id DESC
        //     LIMIT 10
        // )
        // UNION
        // (
        //     SELECT * 
        //     FROM letters_of_intent 
        //     WHERE id = '$default_esc'
        // )
        // ORDER BY id DESC
        // ";
        $sql= "SELECT * FROM letters_of_intent WHERE $where_sql ORDER BY id desc";
    }else{
        $sql = "SELECT * FROM letters_of_intent WHERE $where_sql ORDER BY id desc limit 10";
    }
    $loi_references = mysqli_query($db, $sql);

    $option = [];
    while ($d = mysqli_fetch_assoc($loi_references)) {
        $option[] = ["id" => $d['id'], "title" => "($d[applicant_name])-($d[applicant_email])-($d[applicant_mobile])-($d[loi_reference_id])"];
    }
    echo json_encode($option);
    exit();
}

?>