<?php defined('BASEPATH') or exit('No direct script access allowed');

class Tables_hr extends AdminController
{

  public function __construct()
  {
    parent::__construct();
  }


  public function table_employees(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
        '' . db_prefix() . 'staff.staffid as staffid',
      '(CASE WHEN ISNULL(' . db_prefix() . 'staff.staffid) THEN COALESCE('.db_prefix().'hr_employees.firstname, "") ELSE COALESCE('.db_prefix().'staff.firstname," ") END) as firstname',
      '(CASE WHEN ISNULL(' . db_prefix() . 'staff.staffid) THEN COALESCE('.db_prefix().'hr_employees.lastname, "") ELSE COALESCE('.db_prefix().'staff.lastname," ") END) as lastname',
      '(CASE WHEN ISNULL(' . db_prefix() . 'staff.staffid) THEN COALESCE('.db_prefix().'hr_employees.phonenumber, "") ELSE COALESCE('.db_prefix().'staff.phonenumber," ") END) as phonenumber',
      'CONCAT(COALESCE(supervisor.firstname,"")," ",COALESCE(supervisor.lastname,"")) as Supervisor_Name',
      '' . db_prefix() . 'staff.staffid as staffid',
    ];

    $join = [
      'LEFT JOIN ' . db_prefix() . 'hr_employees ON ' . db_prefix() . 'hr_employees.id_staff = ' . db_prefix() . 'staff.staffid',
      'LEFT JOIN ' . db_prefix() . 'staff supervisor ON ' . db_prefix() . 'hr_employees.parent_id = supervisor.staffid',

    ];

    $sIndexColumn = 'staffid';
    $sTable       = db_prefix() . 'staff';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];

      $row[] = $aRow['staffid'];
      $row[] = $aRow['lastname'];
      $row[] = $aRow['firstname'];
      $row[] = $aRow['phonenumber'];
      $row[] = $aRow['Supervisor_Name'];

      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('staff/member/' . $aRow['staffid']) . '"><i class="fal fa-edit"></i></a>';
      //$action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/employee/removeEmployee/' . $aRow['staffid']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[] = $action;

      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_transfers(){
    $this->load->model('locations_model');
    $this->load->model('departments_hr_model');

    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      db_prefix() . 'hr_transfers.id as transfer_id',
      db_prefix() . 'hr_transfers.department_id as department_id',
      db_prefix() . 'hr_transfers.location_id as location_id',
      db_prefix() . 'hr_transfers.date as date',
      'CONCAT('. db_prefix() . 'staff.firstname," ", '. db_prefix() . 'staff.lastname) as fullname',
    ];

    $join = [
      'LEFT JOIN ' . db_prefix() . 'staff ON ' . db_prefix() . 'staff.staffid = ' . db_prefix() . 'hr_transfers.user_id',
      'LEFT JOIN ' . db_prefix() . 'hr_employees ON ' . db_prefix() . 'hr_employees.id_staff = ' . db_prefix() . 'staff.staffid',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_transfers';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row      = [];
      $location = $this->locations_model->get((int)$aRow['location_id']);

      $department = $this->departments_hr_model->get((int)$aRow['department_id']);

      $row[] = date('d-m-Y', strtotime($aRow['date']));
      $row[] = $aRow['fullname'];
      $row[] = $location ? $location->name : '';
      $row[] = $department ? $department->name : '';

      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/employee/createTransfer/' . $aRow['transfer_id']) . '"><i class="fal fa-edit"></i></a>';
      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/employee/removeTransfer/' . $aRow['transfer_id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[] = $action;

      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_resignations(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      db_prefix() . 'hr_resignations.id as resignation_id',
      db_prefix() . 'hr_resignations.date as date',
      db_prefix() . 'hr_resignations.date_resignation as date_resignation',
      'CONCAT('. db_prefix() . 'staff.firstname," ", '. db_prefix() . 'staff.lastname) as fullname',
    ];

    $join = [
      'LEFT JOIN ' . db_prefix() . 'staff ON ' . db_prefix() . 'staff.staffid = ' . db_prefix() . 'hr_resignations.user_id',
      'LEFT JOIN ' . db_prefix() . 'hr_employees ON ' . db_prefix() . 'hr_employees.id_staff = ' . db_prefix() . 'staff.staffid',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_resignations';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];

      $row[] = date('d-m-Y', strtotime($aRow['date']));
      $row[] = date('d-m-Y', strtotime($aRow['date_resignation']));
      $row[] = $aRow['fullname'];

      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/employee/createResignation/' . $aRow['resignation_id']) . '"><i class="fal fa-edit"></i></a>';
      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/employee/removeResignation/' . $aRow['resignation_id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[] = $action;

      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_promotions(){
    $this->load->model('designations_hr_model');

    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      db_prefix() . 'hr_promotions.id as promotion_id',
      db_prefix() . 'hr_promotions.date as date',
      db_prefix() . 'hr_promotions.title as title',
      db_prefix() . 'hr_promotions.designation_id as designation_id',
      'CONCAT('. db_prefix() . 'staff.firstname," ", '. db_prefix() . 'staff.lastname) as fullname',
    ];

    $join = [
      'LEFT JOIN ' . db_prefix() . 'staff ON ' . db_prefix() . 'staff.staffid = ' . db_prefix() . 'hr_promotions.user_id',
      'LEFT JOIN ' . db_prefix() . 'hr_employees ON ' . db_prefix() . 'hr_employees.id_staff = ' . db_prefix() . 'staff.staffid',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_promotions';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row         = [];
      $designation = $this->designations_hr_model->get((int)$aRow['designation_id']);
      $row[]       = date('d-m-Y', strtotime($aRow['date']));
      $row[]       = $aRow['fullname'];
      $row[]       = $aRow['title'];
      $row[]       = $designation ? $designation->name : '';

      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/employee/createPromotion/' . $aRow['promotion_id']) . '"><i class="fal fa-edit"></i></a>';
      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/employee/removePromotion/' . $aRow['promotion_id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[] = $action;

      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_complaints(){
    $this->load->model('employees_model');
    $this->load->model('staff_model');

    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [

      ' CONCAT(COALESCE(staff_from.firstname)," ", COALESCE(staff_from.lastname))
         as from_user',
      '(CASE WHEN ISNULL('.db_prefix().'staff.staffid) THEN CONCAT(COALESCE('.db_prefix().'hr_employees.firstname)," ", COALESCE('.db_prefix().'hr_employees.lastname)) ELSE CONCAT(COALESCE('.db_prefix().'staff.firstname)," ", COALESCE('.db_prefix().'staff.lastname)) END
       ) as fullname',

       db_prefix() . 'hr_complaints.title as title',
        db_prefix() . 'hr_complaints.date as date',
        db_prefix() . 'hr_complaints.id as complaint_id',
      //db_prefix() . 'hr_complaints.from_user_id as from_user_id',


    ];

    $join = [
      'LEFT JOIN ' . db_prefix() . 'staff ON ' . db_prefix() . 'staff.staffid = ' . db_prefix() . 'hr_complaints.user_id',
      'LEFT JOIN ' . db_prefix() . 'hr_employees ON ' . db_prefix() . 'hr_employees.id_staff = ' . db_prefix() . 'staff.staffid',
      'LEFT JOIN ' . db_prefix() . 'staff staff_from ON staff_from.staffid = ' . db_prefix() . 'hr_complaints.from_user_id',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_complaints';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row   = [];
      //$from  = $this->staff_model->get((int)$aRow['user_id']);
      $row[] = '<a href="' . admin_url('hr/employee/viewComplaint/' . $aRow['complaint_id']) . '">' .$aRow['from_user'] . '</a>'; //$from ? $from->firstname . ' ' . $from->lastname : '';
      $row[] = '<a href="' . admin_url('hr/employee/viewComplaint/' . $aRow['complaint_id']) . '">' .$aRow['fullname'] . '</a>';
      $row[] = '<a href="' . admin_url('hr/employee/viewComplaint/' . $aRow['complaint_id']) . '">' .$aRow['title'] . '</a>';
      $row[] = date('d-m-Y', strtotime($aRow['date']));

      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/employee/createComplaint/' . $aRow['complaint_id']) . '"><i class="fal fa-edit"></i></a>';
      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/employee/removeComplaint/' . $aRow['complaint_id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[] = $action;

      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }
  public function table_complaints_made(){
    $this->load->model('employees_model');

    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [

            ' CONCAT(COALESCE(staff_from.firstname)," ", COALESCE(staff_from.lastname))
               as from_user',
            '(CASE WHEN ISNULL('.db_prefix().'staff.staffid) THEN CONCAT(COALESCE('.db_prefix().'hr_employees.firstname)," ", COALESCE('.db_prefix().'hr_employees.lastname)) ELSE CONCAT(COALESCE('.db_prefix().'staff.firstname)," ", COALESCE('.db_prefix().'staff.lastname)) END
             ) as fullname',

            db_prefix() . 'hr_complaints.title as title',
              db_prefix() . 'hr_complaints.date as date',
              db_prefix() . 'hr_complaints.id as complaint_id',
            //db_prefix() . 'hr_complaints.from_user_id as from_user_id',

    ];

    $join = [
      'LEFT JOIN ' . db_prefix() . 'hr_employees ON ' . db_prefix() . 'hr_employees.id = ' . db_prefix() . 'hr_complaints.user_id',
      'LEFT JOIN ' . db_prefix() . 'hr_complaints_status ON ' . db_prefix() . 'hr_complaints_status.id = ' . db_prefix() . 'hr_complaints.id_status',
      'LEFT JOIN ' . db_prefix() . 'staff staff_from ON staff_from.staffid = ' . db_prefix() . 'hr_complaints.from_user_id',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_complaints';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row   = [];
      //$from  = $this->employees_model->get((int)$aRow['complaint_id']);
      $row[] = $aRow['from_user']; //$from ? $from->firstname . ' ' . $from->lastname : '';
      $row[] = $aRow['fullname'];
      $row[] = $aRow['title'];
      $row[] = date('d-m-Y', strtotime($aRow['date']));

      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/employee/createComplaint/' . $aRow['complaint_id']) . '"><i class="fal fa-edit"></i></a>';
      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/employee/removeComplaint/' . $aRow['complaint_id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[] = $action;

      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_warnings(){
    $this->load->model('employees_model');

    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      ' CONCAT(COALESCE(staff_from.firstname)," ", COALESCE(staff_from.lastname))
         as from_user',
      '(CASE WHEN ISNULL('.db_prefix().'staff.staffid) THEN CONCAT(COALESCE('.db_prefix().'hr_employees.firstname)," ", COALESCE('.db_prefix().'hr_employees.lastname)) ELSE CONCAT(COALESCE('.db_prefix().'staff.firstname)," ", COALESCE('.db_prefix().'staff.lastname)) END
       ) as fullname',

      db_prefix() . 'hr_warnings.title as title',
      db_prefix() . 'hr_warnings.date as date',
      db_prefix() . 'hr_warnings.id as warning_id',
    ];

    $join = [
      'LEFT JOIN ' . db_prefix() . 'staff ON ' . db_prefix() . 'staff.staffid = ' . db_prefix() . 'hr_warnings.user_id',
      'LEFT JOIN ' . db_prefix() . 'hr_employees ON ' . db_prefix() . 'hr_employees.id_staff = ' . db_prefix() . 'staff.staffid',
      'LEFT JOIN ' . db_prefix() . 'staff staff_from ON staff_from.staffid = ' . db_prefix() . 'hr_warnings.from_user_id',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_warnings';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row   = [];
      //$from  = $this->employees_model->get((int)$aRow['warning_id']);
      $row[] = '<a type="button" class="text-primary" href="' . admin_url('hr/employee/viewWarning/' . $aRow['warning_id']) . '">'.$aRow['from_user'].'</a>'; //$from ? $from->firstname . ' ' . $from->lastname : '';
      $row[] = '<a type="button" class="text-primary" href="' . admin_url('hr/employee/viewWarning/' . $aRow['warning_id']) . '">'.$aRow['fullname'].'</a>';
      $row[] = '<a type="button" class="text-primary" href="' . admin_url('hr/employee/viewWarning/' . $aRow['warning_id']) . '">'.$aRow['title'].'</a>';
      $row[] = date('d-m-Y', strtotime($aRow['date']));

      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/employee/createWarning/' . $aRow['warning_id']) . '"><i class="fal fa-edit"></i></a>';
      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/employee/removeWarning/' . $aRow['warning_id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[] = $action;

      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_shifts(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      'id',
      'name',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_shifts';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row   = [];
      $row[] = $aRow['name'];

      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/employee/createShift/' . $aRow['id']) . '"><i class="fal fa-edit"></i></a>';
      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/employee/removeShift/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[] = $action;

      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }
  public function table_shift_days(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      db_prefix() . 'hr_shift_days.id',
      db_prefix() . 'hr_shift_days.start as Start_time',
      db_prefix() . 'hr_shift_days.end as End_time',
      "case when ".db_prefix()."hr_shift_days.day = 0 then 'Sunday'
        when ".db_prefix()."hr_shift_days.day = 1 then 'Monday'
        when ".db_prefix()."hr_shift_days.day = 2 then 'Tuesday'
        when ".db_prefix()."hr_shift_days.day = 3 then 'Wednesday'
        when ".db_prefix()."hr_shift_days.day = 4 then 'Thursday'
        when ".db_prefix()."hr_shift_days.day = 5 then 'Friday'
        when ".db_prefix()."hr_shift_days.day = 6 then 'Saturday'
        else null end as Day_name",
      db_prefix() . 'hr_shifts.name as Shift_name',

    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_shift_days';

$join = [
      'LEFT JOIN ' . db_prefix() . 'hr_shifts ON ' . db_prefix() . 'hr_shifts.id = ' . db_prefix() . 'hr_shift_days.shift_id',
    ];

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row   = [];
      $row[] = $aRow['Start_time'];
      $row[] = $aRow['End_time'];
      $row[] = $aRow['Day_name'];
      $row[] = $aRow['Shift_name'];

      $action = '';

      $row[] = $action;

      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_terminations(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      db_prefix() . 'hr_end_contracts.id as termination_id',
      db_prefix() . 'hr_end_contracts.date as date',
      db_prefix() . 'hr_end_contracts.date_finish as date_finish',
      'CONCAT(firstname," ", lastname) as fullname',
    ];

    $join = [
      'LEFT JOIN ' . db_prefix() . 'staff ON ' . db_prefix() . 'staff.staffid = ' . db_prefix() . 'hr_end_contracts.user_id',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_end_contracts';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row   = [];
      $row[] = $aRow['fullname'];
      $row[] = date('d-m-Y', strtotime($aRow['date']));
      $row[] = date('d-m-Y', strtotime($aRow['date_finish']));

      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/employee/createTermination/' . $aRow['termination_id']) . '"><i class="fal fa-edit"></i></a>';
      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/employee/removeTermination/' . $aRow['termination_id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[] = $action;

      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_departments(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      db_prefix() . 'departments.departmentid as department_id',
      db_prefix() . 'departments.name as name',
      db_prefix() . 'hr_locations.name as location_name',
      'CONCAT(firstname," ", lastname) as head_name',
    ];

    $join = [
      'LEFT JOIN ' . db_prefix() . 'hr_employees ON ' . db_prefix() . 'hr_employees.id = ' . db_prefix() . 'departments.user_id',
      'LEFT JOIN ' . db_prefix() . 'hr_locations ON ' . db_prefix() . 'departments.location_id = ' . db_prefix() . 'hr_locations.id',
    ];

    $sIndexColumn = 'departmentid';
    $sTable       = db_prefix() . 'departments';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where);
    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];

      $row[]  = $aRow['name'];
      $row[]  = $aRow['head_name'];
      $row[]  = $aRow['location_name'];
      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/company/createDepartment/' . $aRow['department_id']) . '"><i class="fal fa-edit"></i></a>';
      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/company/removeDempartment/' . $aRow['department_id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_locations(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [

      db_prefix() . 'hr_locations.name as name',
      'CONCAT(coalesce(firstname,"")," ", coalesce(lastname,"")) as head_name',
      //db_prefix() . 'cities.name as city_name',
      db_prefix() . 'countries.short_name as country_name',
      db_prefix() . 'hr_locations.county_name as county_name',
      db_prefix() . 'hr_locations.city_name as city_name',
      db_prefix() . 'hr_locations.id as location_id',

    ];

    $join = [
    //  'LEFT JOIN ' . db_prefix() . 'hr_employees ON ' . db_prefix() . 'hr_employees.id = ' . db_prefix() . 'hr_locations.user_id',
      //'LEFT JOIN ' . db_prefix() . 'cities ON ' . db_prefix() . 'hr_locations.city_id = ' . db_prefix() . 'cities.id',
      'LEFT JOIN ' . db_prefix() . 'countries ON ' . db_prefix() . 'hr_locations.country_id = ' . db_prefix() . 'countries.country_id',
      'LEFT JOIN ' . db_prefix() . 'staff ON ' . db_prefix() . 'staff.staffid = ' . db_prefix() . 'hr_locations.user_id',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_locations';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where);
    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];

      $row[]  = $aRow['name'];
      $row[]  = $aRow['head_name'];
      //$row[]  = $aRow['city_name'];
      $row[]  = $aRow['country_name'];
      $row[]  = $aRow['county_name'];
      $row[]  = $aRow['city_name'];

      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/company/createLocation/' . $aRow['location_id']) . '"><i class="fal fa-edit"></i></a>';
      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/company/removeLocation/' . $aRow['location_id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }
  public function table_designations(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      db_prefix() . 'designations.name as name',
      db_prefix() . 'designations.score as score',
      db_prefix() . 'designations.id as id',
    ];

    $join = [];
    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'designations';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where);
    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];
      $row[]  = $aRow['name'];
      $row[]  = $aRow['score'];

      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/company/createDesignation/' . $aRow['id']) . '"><i class="fal fa-edit"></i></a>';
      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/company/removeDesignation/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_documents(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }
    $this->load->model('documents_model');
    $this->load->model('document_categories_model');
    $this->load->model('document_types_model');
    $this->load->model('departments_model');
    $this->load->model('employees_model');

    $aColumns = [
      'id',
      'name',
      'category_id',
      'type_id',
      'department_id',
      'user_id',
      'date_expire',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_documents';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where);
    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];
      $employee = null;
      $department = null;

      $type = $this->document_types_model->get($aRow['type_id']);
      $category = $this->document_categories_model->get($aRow['category_id']);

      if($aRow['user_id']){
        $employee = $this->employees_model->get($aRow['user_id']);
      }

      if($aRow['user_id']){
        $department = $this->departments_model->get($aRow['department_id']);
      }


      $row[]  = $aRow['name'];
      $row[]  = $type ? $type->name : '';
      $row[]  = $category ? $category->name : '';
      $row[]  = $department ? $department->name : '';
      $row[]  = $employee ? $employee->firstname.' '.$employee->lastname : '';
      $row[] = date('d-m-Y', strtotime($aRow['date_expire']));
      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/documents/create/' . $aRow['id']) . '"><i class="fal fa-edit"></i></a>';
      $action
        .= '<a type="button" class="btn btn-sm btn-outline-secondary mg-l-5" href="' . admin_url('hr/documents/download/' . $aRow['id']) . '"><i class="fal fa-download"></i></a>';

      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/documents/remove/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_documents_expired(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }
    $this->load->model('documents_model');
    $this->load->model('document_categories_model');
    $this->load->model('document_types_model');
    $this->load->model('departments_model');
    $this->load->model('employees_model');

    $aColumns = [
      'id',
      'name',
      'category_id',
      'type_id',
      'department_id',
      'user_id',
      'date_expire',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_documents';


    $where  = [' AND date_expire < "'.date('Y-m-d').'"'];

    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where);
    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];
      $employee = null;
      $department = null;

      $type = $this->document_types_model->get($aRow['type_id']);
      $category = $this->document_categories_model->get($aRow['category_id']);

      if($aRow['user_id']){
        $employee = $this->employees_model->get($aRow['user_id']);
      }

      if($aRow['user_id']){
        $department = $this->departments_model->get($aRow['department_id']);
      }


      $row[]  = $aRow['name'];
      $row[]  = $type ? $type->name : '';
      $row[]  = $category ? $category->name : '';
      $row[]  = $department ? $department->name : '';
      $row[]  = $employee ? $employee->firstname.' '.$employee->lastname : '';
      $row[] = date('d-m-Y', strtotime($aRow['date_expire']));
      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/documents/create/' . $aRow['id']) . '"><i class="fal fa-edit"></i></a>';
      $action
              .= '<a type="button" class="btn btn-sm btn-outline-secondary mg-l-5" href="' . admin_url('hr/documents/download/' . $aRow['id']) . '"><i class="fal fa-download"></i></a>';

      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/documents/remove/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_document_categories(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      'id',
      'name',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_document_categories';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where);
    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];

      $row[]  = $aRow['name'];
      $action
              = '<button type="button" class="btn btn-sm btn-outline-secondary btn_edit_category" data-id="'.$aRow['id'].'"><i class="fal fa-edit"></i></button>';

      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/documents/removeCategory/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_document_types(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      'id',
      'name',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_document_types';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where);
    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];

      $row[]  = $aRow['name'];
      //$action  = '<button type="button" class="btn btn-sm btn-outline-secondary btn_edit_type" data-id="'.$aRow['id'].'"><i class="fal fa-edit"></i></button>';
      $action  = '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/documents/createType/' . $aRow['id']) . '"><i class="fal fa-edit"></i></a>';
      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/documents/removeType/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_travels(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }
    $this->load->model('travel_modes_model');
    $this->load->model('travel_types_model');
    $this->load->model('employees_model');

    $aColumns = [
      'id',
      'place',
      'mode_id',
      'type_id',
      'user_id',
      'date_start',
      'date_end',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_travels';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where);
    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];
      $employee = null;

      $type = $this->travel_types_model->get($aRow['type_id']);
      $mode = $this->travel_modes_model->get($aRow['mode_id']);

      if($aRow['user_id']){
        $employee = $this->employees_model->get($aRow['user_id']);
      }


      $row[]  = $employee ? $employee->firstname.' '.$employee->lastname : '';
      $row[]  = $aRow['place'];
      $row[] = date('d-m-Y', strtotime($aRow['date_start']));
      $row[] = date('d-m-Y', strtotime($aRow['date_end']));
      $row[]  = $type ? $type->name : '';
      $row[]  = $mode ? $mode->name : '';
      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/travels/create/' . $aRow['id']) . '"><i class="fal fa-edit"></i></a>';

      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/travels/remove/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_travel_types(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      'id',
      'name',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_travel_types';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where);
    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];

      $row[]  = $aRow['name'];
      $action
              = '<button type="button" class="btn btn-sm btn-outline-secondary btn_edit_type" data-id="'.$aRow['id'].'"><i class="fal fa-edit"></i></button>';

      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/travels/removeType/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_travel_modes(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      'id',
      'name',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_travel_modes';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where);
    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];

      $row[]  = $aRow['name'];
      $action
              = '<button type="button" class="btn btn-sm btn-outline-secondary btn_edit_mode" data-id="'.$aRow['id'].'"><i class="fal fa-edit"></i></button>';

      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/travels/removeMode/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_workshops(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }
    $this->load->model('workshop_users_model');
    $this->load->model('workshop_types_model');
    $this->load->model('employees_model');

    $aColumns = [
      'description',
      'trainer_name',
      'trainer_id',
      'date_start',
      'date_end',
      'type_id',
      'mode',
      'cost',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_workshops';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }
    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where, ['id']);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];
      $employee = null;

      $type = $this->workshop_types_model->get($aRow['type_id']);

      $trainer = null;
      if(isset($aRow['trainer_id'])){
          if($aRow['trainer_id'] != 0){
              $trainer = $this->employees_model->get($aRow['trainer_id']);
          }
      }


      $row[]  = $aRow['description'];
      $row[]  = $trainer ? $trainer->firstname.' '.$trainer->lastname : $aRow['trainer_name'];
      $row[] = date('d-m-Y', strtotime($aRow['date_start']));
      $row[] = date('d-m-Y', strtotime($aRow['date_end']));
      $row[]  = $type ? $type->name : '';
      $row[]  = _l($aRow['mode']);
      $row[]  = $aRow['cost'] > 0 ? round($aRow['cost'],2) : 0;
      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/workshops/create/' . $aRow['id']) . '"><i class="fal fa-edit"></i></a>';

      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/workshops/remove/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_workshop_types(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      'id',
      'name',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_workshop_types';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where);
    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];

      $row[]  = $aRow['name'];
      $action
              = '<button type="button" class="btn btn-sm btn-outline-secondary btn_edit_type" data-id="'.$aRow['id'].'"><i class="fal fa-edit"></i></button>';

      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/workshops/removeType/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }
  public function table_workshop_modes(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $aColumns = [
      'id',
      'name',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_workshop_modes';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }

    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where);
    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];

      $row[]  = $aRow['name'];
      $action
              = '<button type="button" class="btn btn-sm btn-outline-secondary btn_edit_type" data-id="'.$aRow['id'].'"><i class="fal fa-edit"></i></button>';

      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/workshops/removeType/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_holidays(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $this->load->model('employees_model');

    $aColumns = [
      'description',
      'start',
      'end',
      'user_id',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_holidays';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }
    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where, ['id']);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];
      $employee = null;

      $employee = $this->employees_model->get($aRow['user_id']);

      $row[]  = $aRow['description'];
      $row[]  = $employee ? $employee->firstname.' '.$employee->lastname : (isset($aRow['trainer_name']) ? $aRow['trainer_name'] : '');
      $row[] = date('d-m-Y', strtotime($aRow['start']));
      $row[] = date('d-m-Y', strtotime($aRow['end']));
      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/holidays/create/' . $aRow['id']) . '"><i class="fal fa-edit"></i></a>';

      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/holidays/remove/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }

  public function table_checkins(){
    if (!has_permission('hr', '', 'view')) {
      access_denied('hr_employees');
    }

    $this->load->model('employees_model');

    $aColumns = [
      'start',
      'end',
      'user_id',
    ];

    $sIndexColumn = 'id';
    $sTable       = db_prefix() . 'hr_checkins';

    $where  = [];
    $filter = [];

    if (count($filter) > 0) {
      array_push($where, 'AND (' . prepare_dt_filter($filter) . ')');
    }
    $result  = data_tables_init($aColumns, $sIndexColumn, $sTable, [], $where, ['id']);

    $output  = $result['output'];
    $rResult = $result['rResult'];

    foreach ($rResult as $aRow) {
      $row = [];
      $employee = null;

      $employee = $this->employees_model->get($aRow['user_id']);

      $row[]  = (isset($employee->firstname) ? $employee->firstname.' '.$employee->lastname : '');
      $row[] = date('d-m-Y H:i', strtotime($aRow['start']));
      $row[] = date('d-m-Y H:i', strtotime($aRow['end']));
      $action
              = '<a type="button" class="btn btn-sm btn-outline-secondary" href="' . admin_url('hr/checkins/create/' . $aRow['id']) . '"><i class="fal fa-edit"></i></a>';

      $action .= '<a type="button" class="btn btn-sm btn-outline-danger mg-l-5" href="' . admin_url('hr/checkins/remove/' . $aRow['id']) . '"><i class="fal fa-trash text-danger"></i></a>';

      $row[]              = $action;
      $output['aaData'][] = $row;
    }

    echo json_encode($output);
    die();
  }
}
