<?php
require_once('../config.php');
$user_id = $_SESSION['user_id'];
$emp_id = $_SESSION['emp_id'];
$user_role = $_SESSION['user_role'];


// SQL server connection information
// $sql_details = $search_db;
$sql_details = $search_db;

if(!isset($_GET['isset'])){
    exit();
}


if ($user_role == 1) {
    $reqWhere = " u.leader='$emp_id' and ";
} else {
    $reqWhere = "";
}

$table = <<<EOT
    (
        SELECT 
            ROW_NUMBER() OVER(ORDER BY rl.id DESC) row_num,
            u.name, 
            u.leader, 
            rl.id, 
            rl.employee_id, 
            rl.leave_date, 
            rl.leave_type, 
            rl.leave_reason, 
            rl.leave_status, 
            rl.remark, 
            rl.created_at
        FROM users AS u 
        JOIN request_leave AS rl ON u.employee_id = rl.employee_id 
        WHERE $reqWhere 
        (
            (
                '$user_role' = 0 AND u.leader = '$emp_id'
            )
            OR
            (
                (
                    u.leader <> '$emp_id' OR $user_role = 1
                )
                AND NOT
                (
                    (
                        rl.leave_date LIKE '% to %'
                        AND $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 $user_role = 0 AND TRIM(LOWER(rl.leave_type)) NOT IN ('work from home', 'holiday')
                    )
                    OR
                    (
                        rl.leave_date LIKE '% to %'
                        AND $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 (
            $user_role = 1 
            AND TRIM(LOWER(rl.leave_type)) IN ('work from home', 'holiday')
        )
    ) join_table
    EOT;


$whereResult = null;
if (isset($_GET['requestLeave_where_clause']) && !empty($_GET['requestLeave_where_clause'])) {
    $whereAll = $_GET['requestLeave_where_clause'];
} else {
    $whereAll = null;
}

// Table's primary key
$primaryKey = 'id';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array('db' => 'row_num', 'dt' => 'row_num'),
    array('db' => 'employee_id', 'dt' => 'employee_id'),
    array('db' => 'name', 'dt' => 'name'),
    array('db' => 'leave_date', 'dt' => 'leave_date'),
    array('db' => 'leave_type', 'dt' => 'leave_type'),
    array(
        'db' => 'leave_reason', 
        'dt' => 'leave_reason',
        'formatter' => function($cell, $row){
            $returnValue = '<div style="min-width:300px; white-space: pre-wrap;">'.$row['leave_reason'].'</div>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'created_at', 
        'dt' => 'created_at',
        'formatter' => function($cell, $row){
            $returnValue = date("h:i A - d M Y", strtotime($row['created_at']));
            return $returnValue;
        }
    ),
    array(
        'db' => 'leave_status', 
        'dt' => 'leave_status',
        'formatter' => function($cell, $row){
            $returnValue = '<span id="requestStatus'.$row['id'].'">';
            if ($row['leave_status'] == 0) {
                $returnValue .= '<span class="text-warning">Pending</span>';
            } else if ($row['leave_status'] == 1) {
                $returnValue .= '<span class="text-success">Approved</span>';
            } else if ($row['leave_status'] == 2) {
                $returnValue .= '<span class="text-danger">Rejected</span>';
            }
            $returnValue .= '</span>'; 
            return $returnValue;
        }
    ),
    array(
        'db' => 'remark', 
        'dt' => 'remark',
        'formatter' => function($cell, $row){
            $returnValue = '<div style="min-width:200px;">
            <textarea name="remark" id="remark'.$row['id'].'" class="form-control" rows="1" placeholder="Enter Remark" '.(($row['leave_status'] != 0) ? 'disabled' : '').' required>'.$row['remark'].'</textarea>
            </div>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'leader', 
        'dt' => 'leader',
        'formatter' => function($cell, $row){
            if ($row['leave_status'] == 0) {
                $returnValue = '<div id="requestButton'.$row['id'].'">
                    <button type="button" onclick="changeStatus(\''.$row['id'].'\',1);" class="btn btn-sm btn-soft-success" title="Approve"><i class="uil uil-check-circle mb-0" style="font-size:20px;"></i></button>
                    <button type="button" onclick="changeStatus(\''.$row['id'].'\',2);" class="btn btn-sm btn-soft-danger" title="Reject"><i class="uil uil-times-circle mb-0" style="font-size:20px;"></i></button>
                </div>';
            } else if ($row['leave_status'] == 1) {
                $returnValue = '<button type="button" class="btn btn-sm btn-soft-success" title="Approved"><i class="uil uil-check-circle mb-0" style="font-size:20px;"></i></button>';
            } else if ($row['leave_status'] == 2) {
                $returnValue = '<button type="button" class="btn btn-sm btn-soft-danger" title="Rejected"><i class="uil uil-times-circle mb-0" style="font-size:20px;"></i></button>';
            }
            return $returnValue;
        }
    ),
    array(
        'db' => 'id', 
        'dt' => 'id',
        'formatter' => function($cell, $row){
            $returnValue = '<button type="button" class="btn btn-danger btn-sm" onclick="_delete_(\''.$row['id'].'\');">Delete</button>';
            return $returnValue;
        }
    ),
    
);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */
require('ssp.class.php');

echo json_encode(
    // SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns)
    SSP::complex($_GET, $sql_details, $table, $primaryKey, $columns, $whereResult, $whereAll)
);
