<?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();
}
$table = <<<EOT
(
    SELECT 
        t1.id, t1.name, t1.email, t1.phone, t1.joining_date, t1.working_status_date,
        t4.designation_name,
        DATE_FORMAT(t1.joining_date, '%Y-%m') AS joining_month,
        DATE_FORMAT(t1.working_status_date, '%Y-%m') AS working_status_month
    FROM (
        SELECT 
            p1.*,
            (CASE
            WHEN COALESCE(p2.promote_to,'')<>'' THEN p2.promote_to
            ELSE p1.designation
            END) AS new_designation
        FROM users p1
        LEFT JOIN (
            SELECT
                *
            FROM (
                SELECT 
                    id,
                    employee_id, 
                    promote_to, 
                    applicable_from, 
                    (CASE 
                    WHEN COALESCE(applicable_to,'')='' THEN DATE_FORMAT(CURRENT_DATE, '%Y-%m')
                    ELSE applicable_to
                    END) as applicable_to 
                FROM promotions
            ) final_promotions
            WHERE (DATE_FORMAT(CURRENT_DATE, '%Y-%m') BETWEEN applicable_from AND applicable_to)
        ) p2 ON p1.employee_id=p2.employee_id
    ) t1
    LEFT JOIN designations AS t4 ON t4.id=t1.new_designation
) 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'),
    array('db' => 'name', 'dt' => 'name'),
    array('db' => 'email', 'dt' => 'email'),
    array('db' => 'phone', 'dt' => 'phone'),
    array('db' => 'designation_name', 'dt' => 'designation_name'),
    array('db' => 'joining_date', 'dt' => 'joining_date'),
    array('db' => 'joining_month', 'dt' => 'joining_month'),
    array(
        'db' => 'working_status_date', 
        'dt' => 'working_status_date',
        'formatter' => function($col, $row) {
            if(empty($row['working_status_date'])){
                return '<div class="text-muted not-active-employee">N/A</div>';
            } else {
                return '<div class="active-employee">' . $row['working_status_date'] . '</div>';
            }
        }
    ),
    array('db' => 'working_status_month', 'dt' => 'working_status_month'),
);


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