<?php

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

if (!isset($_SESSION['user_id']) || $_SESSION['user_role']!='0') {
    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();
}


$table = <<<EOT
(
    SELECT 
        ROW_NUMBER() OVER(ORDER BY t1.id DESC) row_num,
        t1.*, 
        t2.name,
        COALESCE(
            (CASE
                WHEN (t1.revision_start_date='' OR t1.revision_start_date IS NULL)  AND (t1.revision_end_date='' OR t1.revision_end_date IS NULL) 
                THEN (SELECT SUM(bonus) AS bonus FROM employee_bonusIncentive WHERE employee_id=t1.employee_id)
        
                WHEN (t1.revision_start_date='' OR t1.revision_start_date IS NULL)  AND (t1.revision_end_date<>'' OR t1.revision_end_date IS NOT NULL) 
                THEN (SELECT SUM(bonus) AS bonus FROM employee_bonusIncentive WHERE employee_id=t1.employee_id AND date<DATE_FORMAT(t1.revision_end_date, '%Y-%m'))
        
                WHEN (t1.revision_start_date<>'' OR t1.revision_start_date IS NOT NULL)  AND (t1.revision_end_date='' OR t1.revision_end_date IS NULL) 
                THEN (SELECT SUM(bonus) AS bonus FROM employee_bonusIncentive WHERE employee_id=t1.employee_id AND date<DATE_FORMAT(t1.revision_start_date, '%Y-%m'))
        
                ELSE (SELECT SUM(bonus) AS bonus FROM employee_bonusIncentive WHERE employee_id=t1.employee_id AND date BETWEEN DATE_FORMAT(t1.revision_start_date, '%Y-%m') AND DATE_FORMAT(t1.revision_end_date, '%Y-%m'))
        
        END),0) as bonus,
        COALESCE(
            (CASE
                WHEN (t1.revision_start_date='' OR t1.revision_start_date IS NULL)  AND (t1.revision_end_date='' OR t1.revision_end_date IS NULL) 
                THEN (SELECT SUM(incentive) AS incentive FROM employee_bonusIncentive WHERE employee_id=t1.employee_id)
        
                WHEN (t1.revision_start_date='' OR t1.revision_start_date IS NULL)  AND (t1.revision_end_date<>'' OR t1.revision_end_date IS NOT NULL) 
                THEN (SELECT SUM(incentive) AS incentive FROM employee_bonusIncentive WHERE employee_id=t1.employee_id AND date<DATE_FORMAT(t1.revision_end_date, '%Y-%m'))
        
                WHEN (t1.revision_start_date<>'' OR t1.revision_start_date IS NOT NULL)  AND (t1.revision_end_date='' OR t1.revision_end_date IS NULL) 
                THEN (SELECT SUM(incentive) AS incentive FROM employee_bonusIncentive WHERE employee_id=t1.employee_id AND date<DATE_FORMAT(t1.revision_start_date, '%Y-%m'))
        
                ELSE (SELECT SUM(incentive) AS incentive FROM employee_bonusIncentive WHERE employee_id=t1.employee_id AND date BETWEEN DATE_FORMAT(t1.revision_start_date, '%Y-%m') AND DATE_FORMAT(t1.revision_end_date, '%Y-%m'))
        
        END),0) as incentive 
    FROM employee_salary t1
    INNER JOIN users t2 ON t2.employee_id = t1.employee_id
) 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' => 'fixed', 
        'dt' => 'fixed',
        'formatter' => function($cell, $row){
            $fixed = json_decode($cell, true);
            $returnValue = '<div>';
            foreach($fixed as $value) {
                $returnValue .= '<p class="mb-0 salary_key text-primary" style="cursor:pointer;"><strong>'.$value['title'].':</strong> <i class="uil uil-angle-down"></i></p>
                                <ul class="mb-0" style="display:none;"> 
                                    <li><strong>Yearly: </strong>&#8377;&nbsp;'.$value['yearly_amount'].'</li>
                                    <li><strong>Monthly: </strong>&#8377;&nbsp;'.round($value['yearly_amount']/12).'</li>
                                </ul>';
            }
            $returnValue .= '</div>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'add_on', 
        'dt' => 'add_on',
        'formatter' => function($cell, $row){
            $add_on = json_decode($cell, true);
            $returnValue = '<div>';
            foreach($add_on as $value) {
                $returnValue .= '<p class="mb-0 salary_key text-primary" style="cursor:pointer;"><strong>'.$value['title'].':</strong> <i class="uil uil-angle-down"></i></p>
                                <ul class="mb-0" style="display:none;"> 
                                    <li><strong>Yearly: </strong>&#8377;&nbsp;'.$value['yearly_amount'].'</li>
                                    <li><strong>Monthly: </strong>&#8377;&nbsp;'.round($value['yearly_amount']/12).'</li>
                                </ul>';
            }
            $returnValue .= '</div>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'deduction', 
        'dt' => 'deduction',
        'formatter' => function($cell, $row){
            $deduction = json_decode($cell, true);
            $returnValue = '<div>';
            foreach($deduction as $value) {
                $returnValue .= '<p class="mb-0 salary_key text-primary" style="cursor:pointer;"><strong>'.$value['title'].':</strong> <i class="uil uil-angle-down"></i></p>
                                <ul class="mb-0" style="display:none;"> 
                                    <li><strong>Yearly: </strong>&#8377;&nbsp;'.$value['yearly_amount'].'</li>
                                    <li><strong>Monthly: </strong>&#8377;&nbsp;'.round($value['yearly_amount']/12).'</li>
                                </ul>';
            }
            $returnValue .= '</div>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'ctc', 
        'dt' => 'ctc',
        'formatter' => function($cell, $row){
            $returnValue = '<p class="mb-0 salary_key text-primary" style="cursor:pointer;"><strong>CTC:</strong> <i class="uil uil-angle-down"></i></p>
            <ul class="mb-0" style="display:none;">
                <li><strong>Yearly: </strong>&#8377;&nbsp;'.$cell.'</li>
                <li><strong>Monthly: </strong>&#8377;&nbsp;'.round($cell/12).'</li>
            </ul>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'net_payable', 
        'dt' => 'net_payable',
        'formatter' => function($cell, $row){
            $returnValue = '<p class="mb-0 salary_key text-primary" style="cursor:pointer;"><strong>Payable:</strong> <i class="uil uil-angle-down"></i></p>
            <ul class="mb-0" style="display:none;">
                <li><strong>Yearly: </strong>&#8377;&nbsp;'.$cell.'</li>
                <li><strong>Monthly: </strong>&#8377;&nbsp;'.round($cell/12).'</li>
            </ul>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'bonus', 
        'dt' => 'bonus',
        'formatter' => function($cell, $row){
            $returnValue = '<p class="mb-0 salary_key text-primary" style="cursor:pointer;"><strong>Bonus/Incentive:</strong> <i class="uil uil-angle-down"></i></p>
            <ul class="mb-0" style="display:none;">
                <li><strong>Bonus: </strong>&#8377;&nbsp;'.$row['bonus'].'</li>
                <li><strong>Incentive: </strong>&#8377;&nbsp;'.$row['incentive'].'</li>
            </ul>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'revision_start_date', 
        'dt' => 'revision_start_date',
        'formatter' => function($cell, $row){
            $returnValue = (!empty($row['revision_start_date'])) ? date("d M Y", strtotime($row['revision_start_date'])) : '';
            return $returnValue;
        }
    ),
    array(
        'db' => 'revision_end_date', 
        'dt' => 'revision_end_date',
        'formatter' => function($cell, $row){
            $returnValue = '';
            if (empty($row['revision_end_date'])) {
                $returnValue = '
                <a href="add_salary?edit='.$row['id'].'&ReviseSalary" title="Revision"><i class="uil uil-file-copy-alt"></i></a>
                <a href="add_salary?edit='.$row['id'].'" class="mr-2" title="Edit"><i class="uil uil-edit text-success"></i></a>
                <a href="javascript:;" onclick="_delete_(\''.$row['id'].'\');" title="Delete"><i class="uil uil-trash-alt text-danger"></i></a>
                ';
            }
            return $returnValue;
        }
    ),
    array('db' => 'incentive', 'dt' => 'incentive'),

);


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