<?php

require_once('../config.php');

if (!isset($_SESSION['user_id'])) {
    http_response_code(403);
    exit(json_encode(["error" => "Unauthorized access!!"]));
}

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


require_once "../salaryCodeCalculate.php";
require_once "../salaryCode.php";
$salary_code = '';

$extra_where = json_decode($_GET['extra_where'], true);
$firstDayLast = $extra_where['firstDayLast'];
$lastDayLast = $extra_where['lastDayLast'];


$join_month = date("Y-m", strtotime($firstDayLast));
$wsDate = date("Y-m", strtotime($lastDayLast));

$query = "SELECT 
    ROW_NUMBER() OVER (ORDER BY u.id ASC) AS row_num, 
    u.id, 
    u.employee_id, 
    u.name, 
    u.email, 
    u.phone, 
    u.gender, 
    d.department_name, 
    u.joining_date, 
    u.working_status_date,
    '$firstDayLast' as firstDayLast,
    '$lastDayLast' as lastDayLast
FROM users as u 
INNER JOIN departments AS d ON d.id = u.department
JOIN attendance AS a ON u.employee_id = a.employee_id 
WHERE ";

if ($user_role == 0) {

    $query .= " (STR_TO_DATE(a.which_month, '%M %d %Y') BETWEEN '$firstDayLast' AND '$lastDayLast')
        AND (COALESCE(u.joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(u.joining_date, '%Y-%m-%d'),'%Y-%m') <= '$join_month')
        AND (COALESCE(u.working_status_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(u.working_status_date, '%Y-%m-%d'),'%Y-%m') >= '$wsDate') ";
} else if ($user_role == 1) {

    $query .= " (u.employee_id='$emp_id' or u.leader='$emp_id') 
        AND (STR_TO_DATE(a.which_month, '%M %d %Y') BETWEEN '$firstDayLast' AND '$lastDayLast')
        AND (COALESCE(u.joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(u.joining_date, '%Y-%m-%d'),'%Y-%m') <= '$join_month')
        AND (COALESCE(u.working_status_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(u.working_status_date, '%Y-%m-%d'),'%Y-%m') >= '$wsDate') ";
} else if ($user_role == 2) {

    $query .= " u.employee_id='$emp_id' 
        AND (STR_TO_DATE(a.which_month, '%M %d %Y') BETWEEN '$firstDayLast' AND '$lastDayLast')
        AND (COALESCE(u.joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(u.joining_date, '%Y-%m-%d'),'%Y-%m') <= '$join_month')
        AND (COALESCE(u.working_status_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(u.working_status_date, '%Y-%m-%d'),'%Y-%m') >= '$wsDate') ";
} else {
    // added by rahul, previously else condition was empty
    $query .= " u.employee_id='$emp_id' 
        AND (STR_TO_DATE(a.which_month, '%M %d %Y') BETWEEN '$firstDayLast' AND '$lastDayLast')
        AND (COALESCE(u.joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(u.joining_date, '%Y-%m-%d'),'%Y-%m') <= '$join_month')
        AND (COALESCE(u.working_status_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(u.working_status_date, '%Y-%m-%d'),'%Y-%m') >= '$wsDate') ";
}
$query .= " GROUP BY u.id 
ORDER BY u.id ASC";


$table = <<<EOT
(
    $query
) 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'].'" type="checkbox" />';
            return $returnValue;
        }
    ),
    array('db' => 'row_num', 'dt' => 'row_num'),
    array('db' => 'employee_id', 'dt' => 'employee_id'),
    array('db' => 'name', 'dt' => 'name'),
    array('db' => 'department_name', 'dt' => 'department_name'),
    array(
        'db' => 'firstDayLast', 
        'dt' => 'firstDayLast',
        'formatter' => function($cell, $row){
            $GLOBALS['salary_code'] = salaryCode($row, $row['firstDayLast'], $row['firstDayLast']);

            $s_code = $GLOBALS['salary_code'];
            // $returnValue = '<a href="javascript:;" onclick="monthSalaryDetails(\''.$row['name'].'\','.htmlspecialchars(json_encode($s_code['monthSalaryDetails'])).');" title="Show Salary Details">&#8377;&nbsp;'.$s_code['monthlySalary'].'</a>';
            $returnValue = '<a href="javascript:;" onclick="monthSalaryDetails(\''.$row['name'].'\',\''.htmlspecialchars(json_encode($s_code['monthSalaryDetails'])).'\');" title="Show Salary Details">&#8377;&nbsp;XXXX</a>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'lastDayLast', 
        'dt' => 'lastDayLast',
        'formatter' => function($cell, $row){

            $s_code = $GLOBALS['salary_code'];
            $returnValue = '<a href="javascript:;" onclick="totalLeave(\''.$row['name'].'\',\''.htmlspecialchars(json_encode($s_code['leaves'])).'\');" title="Show Leave Details">'.($s_code['leaves']['Total Leaves']??0).' Leave</a>';
            return $returnValue;
        }
    ),

    array(
        'db' => 'email', 
        'dt' => 'email',
        'formatter' => function($cell, $row){

            $s_code = $GLOBALS['salary_code'];
            // $returnValue = '<a href="javascript:;" onclick="workingSalaryDetails(\''.$row['name'].'\','.htmlspecialchars(json_encode($s_code['working_salary'])).');" title="Show Leave Details">&#8377;&nbsp;'.$s_code['working_day_salary'].'</a>';
            $returnValue = '<a href="javascript:;" onclick="workingSalaryDetails(\''.$row['name'].'\',\''.htmlspecialchars(json_encode($s_code['working_salary'])).'\');" title="Show Leave Details">&#8377;&nbsp;XXXX</a>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'phone', 
        'dt' => 'phone',
        'formatter' => function($cell, $row){

            $s_code = $GLOBALS['salary_code'];
            // $returnValue = '<a href="javascript:;" onclick="monthDeduction(\''.$row['name'].'\','.$s_code['working_tds'].','.$s_code['working_employee_epf'].','.$s_code['working_company_epf'].','.$s_code['working_company_esi'].','.$s_code['working_employee_esi'].');" title="Show Deduction Details">&#8377;&nbsp;'.$s_code['deduction'].'</a>';
            $returnValue = '<a href="javascript:;" onclick="monthDeduction(\''.$row['name'].'\',\''.htmlspecialchars(json_encode($s_code['deduction'])).'\');" title="Show Deduction Details">&#8377;&nbsp;XXXX</a>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'gender', 
        'dt' => 'gender',
        'formatter' => function($cell, $row){

            $s_code = $GLOBALS['salary_code'];
            $returnValue = '<a href="javascript:;" data-salary="'.$s_code['final_salary']['Payable Salary'].'"><span>&#8377;&nbsp;XXXX</span> <i class="uil uil-eye-slash text-primary showPayableSalary"></i></a>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'working_status_date', 
        'dt' => 'working_status_date',
        'formatter' => function($cell, $row){
            $returnValue = '';

            // $month = date("M Y", strtotime($row['firstDayLast']));
            // $vDate = date("Y-m-d");
            // $slipRequest = mysqli_fetch_assoc(mysqli_query($GLOBALS['db'], "select * from request_slip where request_id='$GLOBALS[emp_id]' and slip_id='$row[employee_id]' and month='$month' order by id desc limit 1 "));

            // if ((!empty($slipRequest) && $slipRequest['status'] == 1 && $slipRequest['validity'] >= $vDate) || ($GLOBALS['user_role'] == 0)) {
            //     $returnValue = '<a href="/pay_slip/payslip?slip_id='.$row['employee_id'].'&from='.$row['firstDayLast'].'&to='.$row['lastDayLast'].'" target="_blank" class="btn btn-sm btn-primary">
            //             <i class="uil uil-file-alt mr-1"></i>Download<i class="uil uil-down-arrow ml-1"></i>
            //         </a>';
            // } else if (!empty($slipRequest) && $slipRequest['status'] == 0) {
            //     $returnValue = '<button type="button" class="btn btn-sm btn-link">
            //             <i class="uil uil-file-alt mr-1"></i>Request Send<i class="uil uil-arrow-up-right ml-1"></i>
            //         </button>
            //         <i class="uil uil-check-circle text-success"></i>';
            // } else if (!empty($slipRequest) && $slipRequest['status'] == 2 && $slipRequest['validity'] >= $vDate) {
            //     $returnValue = '<button type="button" class="btn btn-sm btn-link">
            //             <i class="uil uil-file-alt mr-1"></i>Request Reject<i class="uil uil-arrow-up-right ml-1"></i>
            //         </button>
            //         <i class="uil uil-times-circle text-danger"></i>';
            // } else {
                $returnValue = '<button type="button" class="btn btn-sm btn-primary" id="requestSalarySlip'.$row['employee_id'].'" onclick="requestSalarySlip(\''.$row['employee_id'].'\',\''.$row['firstDayLast'].'\',\''.$row['lastDayLast'].'\');">
                        <i class="uil uil-file-alt mr-1"></i>Download<i class="uil uil-down-arrow ml-1"></i>
                    </button>';
            // }

            return $returnValue;
        }
    ),
    array('db' => 'joining_date', 'dt' => 'joining_date'),

);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * 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)
);
