<?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();
}


$att_status = mysqli_fetch_all(mysqli_query($db, "SELECT * FROM attendance_status"), MYSQLI_ASSOC);

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

$extra_where = '';
if(isset($_GET['extra_where'])){
    $extra_where = " AND ". implode(' AND ', $_GET['extra_where']);
    $extra_where = str_replace("punch_in", "COALESCE(a.punch_in, '')<>'' && str_to_date(a.punch_in, '%h:%i %p')", $extra_where);
    $extra_where = str_replace("punch_out", "COALESCE(a.punch_out, '')<>'' && str_to_date(a.punch_out, '%h:%i %p')", $extra_where);
}

$table = <<<EOT
(
    SELECT 
        u.name, 
        u.leader, 
        rl.id, 
        rl.employee_id, 
        rl.issue_date, 
        rl.issue_note, 
        rl.issue_status, 
        rl.remark, 
        rl.created_at, 
        rl.issue_type,
        rl.request_for,
        GROUP_CONCAT(
            CASE WHEN rl.issue_status = 1 THEN ac.attendance_status ELSE a.attendance_status END 
            SEPARATOR ', '
        ) AS attendance_status,
        GROUP_CONCAT(
            CASE WHEN rl.issue_status = 1 THEN ac.punch_in ELSE a.punch_in END 
            SEPARATOR ', '
        ) AS punch_in,
        GROUP_CONCAT(
            CASE WHEN rl.issue_status = 1 THEN ac.punch_out ELSE a.punch_out END 
            SEPARATOR ', '
        ) AS punch_out
    FROM users AS u 
    JOIN issue_with_date AS rl ON u.employee_id = rl.employee_id
    LEFT JOIN attendance AS a ON 
    a.employee_id = rl.employee_id AND 
    STR_TO_DATE(a.which_month, '%M %d %Y') BETWEEN 
        STR_TO_DATE(SUBSTRING_INDEX(rl.issue_date, ' to ', 1), '%d %b %Y') AND 
        STR_TO_DATE(SUBSTRING_INDEX(rl.issue_date, ' to ', -1), '%d %b %Y')
    LEFT JOIN attendance_change AS ac ON 
        ac.employee_id = rl.employee_id AND 
        ac.which_month = a.which_month AND 
        rl.issue_status = 1 AND 
        STR_TO_DATE(ac.which_month, '%M %d %Y') BETWEEN 
            STR_TO_DATE(SUBSTRING_INDEX(rl.issue_date, ' to ', 1), '%d %b %Y') AND 
            STR_TO_DATE(SUBSTRING_INDEX(rl.issue_date, ' to ', -1), '%d %b %Y') 
    WHERE $reqWhere
    (
        (
            '$user_role' = 0 AND u.leader = '$emp_id'
        )
        OR
        (
            (
                u.leader <> '$emp_id' OR '$user_role' = 1
            )
            AND NOT 
            (
                (
                    rl.issue_date LIKE '% to %'
                    AND $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 $user_role = 0 AND TRIM(LOWER(rl.request_for)) NOT IN ('work from home', 'holiday')
                )
                OR
                (
                    rl.issue_date LIKE '% to %'
                    AND $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 (
        $user_role = 1 
        AND TRIM(LOWER(rl.request_for)) IN ('work from home', 'holiday')
    )
    $extra_where
    GROUP BY u.name, u.leader, rl.id, rl.employee_id, rl.issue_date, rl.issue_note, rl.issue_status, rl.remark, rl.created_at, rl.issue_type
) join_table
EOT;

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


// 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' => 'id', 
        'dt' => 'id',
        'formatter' => function($cell, $row){
            $returnValue = '<input name="r_checked[]" class="checkboxes" value="'.$row['employee_id'].'" data-id="'.$row['id'].'" data-issueDate="'.$row['issue_date'].'" type="checkbox" />';
            return $returnValue;
        }
    ),
    array('db' => 'employee_id', 'dt' => 'employee_id'),
    array('db' => 'name', 'dt' => 'name'),
    array(
        'db' => 'attendance_status', 
        'dt' => 'attendance_status',
        'formatter' => function($cell, $row){
            $returnValue = '<div style="min-width:300px;">
                    <span>Status: <strong style="white-space: pre-wrap;">'.$row['attendance_status'].'</strong></span><br>
                    <span>Punch In: <strong style="white-space: pre-wrap;">'.$row['punch_in'].'</strong></span><br>
                    <span>Punch Out: <strong style="white-space: pre-wrap;">'.$row['punch_out'].'</strong></span><br>';

                    // calculate total hours
                    $punchIns = explode(', ', $row['punch_in']);
                    $punchOuts = explode(', ', $row['punch_out']);
                    $totalHours = [];
                    foreach ($punchIns as $index => $punchIn) {
                        if (isset($punchOuts[$index])) {
                            $inTime = strtotime($punchIn);
                            $outTime = strtotime($punchOuts[$index]);
                            if ($inTime && $outTime) {
                                $totalHours[] = date('H:i', $outTime - $inTime);
                            }
                        }
                    }
                    $returnValue .= '<span>Total Hours: <strong style="white-space: pre-wrap;">'.implode(', ', $totalHours).'</strong></span>';

            $returnValue .= '</div>';
            return $returnValue;
        }
    ),
    array('db' => 'request_for', 'dt' => 'request_for'),
    array('db' => 'issue_date', 'dt' => 'issue_date'),
    array(
        'db' => 'issue_note', 
        'dt' => 'issue_note',
        'formatter' => function($cell, $row){
            $returnValue = '<div style="min-width:300px; white-space: pre-wrap;">'.$row['issue_note'].'</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' => 'issue_status', 
        'dt' => 'issue_status',
        'formatter' => function($cell, $row){
            $returnValue = '<span id="requestStatus'.$row['id'].'">';
            if ($row['issue_status'] == 0) {
                $returnValue .= '<span class="text-warning">Pending</span>';
            } else if ($row['issue_status'] == 1) {
                $returnValue .= '<span class="text-success">Approved</span>';
            } else if ($row['issue_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['issue_status'] != 0) ? 'disabled' : '').' required>'.$row['remark'].'</textarea></div>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'issue_type', 
        'dt' => 'issue_type',
        'formatter' => function($cell, $row){
            $returnValue = '<div style="min-width:200px;">
            <select class="form-control" id="AppAttendanceStatus'.$row['id'].'" '. (($row['issue_status'] != 0) ? 'disabled' : '').' >
                    <option selected value="">Select Attendance Status</option>';
                    foreach ($GLOBALS['att_status'] as $key => $att) {
                        $returnValue .= '<option '.(($row['issue_type'] == $att['status']) ? 'selected' : '').' value="'.$att['status'].'">'.$att['status'].'</option>';
                    }
                $returnValue .= '</select>
            </div>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'punch_in', 
        'dt' => 'punch_in',
        'formatter' => function($cell, $row){
            if ($row['issue_status'] == 0) {
                $returnValue = '<div id="requestButton'.$row['id'].'">
                    <button type="button" onclick="changeStatus(\''.$row['id'].'\',1,\''.$row['issue_date'].'\');" 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,\''.$row['issue_date'].'\');" 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['issue_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['issue_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' => 'punch_out', 
        'dt' => 'punch_out',
        '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)
);
