<?php

namespace App\Controllers\backend;

use PHPExcel;
use PHPExcel_IOFactory;
use App\Models\backend\KaryawanModel;
use App\Models\backend\MenuModel;
use App\Models\backend\SubmenuModel;
use App\Models\backend\JabatanModel;
use App\Models\backend\StatusPegawaiModel;
use App\Models\backend\DivisiModel;
use App\Models\backend\UserDivisiModel;
use App\Models\backend\AbsenPegawaiModel;
use App\Models\backend\GolGajiModel;
use App\Models\backend\PangkatModel;
use App\Models\backend\NilaiPegawaiModel;
use App\Models\backend\InputPangkatModel;
use CodeIgniter\Controller;
use CodeIgniter\HTTP\Request;
use DateTime;
use CodeIgniter\HTTP\Files\UploadedFile;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Pegawai extends Controller
{
  protected $karyawanModel;
  protected $menuModel;
  protected $submenuModel;
  protected $jabatanModel;
  protected $statusPegawaiModel;
  protected $divisiModel;
  protected $userDivisiModel;
  protected $absenPegawaiModel;
  protected $golGajiModel;
  protected $pangkatModel;
  protected $nilaiPegawaiModel;
  protected $inputPangkatModel;

  public function __construct()
  {
    helper('fisi');
    $this->karyawanModel = new KaryawanModel();
    $this->menuModel = new MenuModel();
    $this->submenuModel = new SubmenuModel();
    $this->jabatanModel = new JabatanModel();
    $this->statusPegawaiModel = new StatusPegawaiModel();
    $this->divisiModel = new DivisiModel();
    $this->userDivisiModel = new UserDivisiModel();
    $this->absenPegawaiModel = new AbsenPegawaiModel();
    $this->golGajiModel = new GolGajiModel();
    $this->pangkatModel = new PangkatModel();
    $this->nilaiPegawaiModel = new NilaiPegawaiModel();
    $this->inputPangkatModel = new InputPangkatModel();
  }

  // controller Data Pegawai
  public function index()
  {
    $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();

    $data = [
      'title' => 'Data Pegawai',
      'user' => $cekuser,
      'validation' => \Config\Services::validation()
    ];

    return view('backend/pegawai/pegawai', $data);
  }

  public function fetchpegawai()
  {
    if ($this->request->isAJAX()) {
      if ($pegawai = $this->karyawanModel->where('username!=', 'adminmij')->findAll()) {
        // if ($pegawai = $this->karyawanModel->findAll()) {
        $data = [
          'responce' => 'success',
          'pegawai' => $pegawai
        ];
      } else {
        $data = [
          'responce' => 'error',
          'pesan' => 'gagal fetch'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  // controller tambah pegawai
  public function formtambah()
  {
    $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();
    $jabatan = $this->jabatanModel->findAll();
    $statuspegawai = $this->statusPegawaiModel->findAll();
    $golgaji = $this->golGajiModel->findAll();
    $golongan = $this->pangkatModel->orderBy('sort', 'asc')->findAll();
    // dd($statuspegawai);

    $data = [
      'title' => 'Tambah Pegawai',
      'user' => $cekuser,
      'jabatan' => $jabatan,
      'status' => $statuspegawai,
      'golgaji' => $golgaji,
      'golongan' => $golongan,
      'validation' => \Config\Services::validation()
    ];

    return view('backend/pegawai/formtambah', $data);
  }

  public function tambahpegawai()
  {
    if ($this->request->isAJAX()) {

      if (!$this->validate([
        'nama_lengkap' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Nama lengkap tidak boleh kosong'
          ]
        ],
        'nama_panggilan' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Nama panggilan tidak boleh kosong'
          ]
        ],
        'tem_lahir' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Tempat lahir tidak boleh kosong'
          ]
        ],
        'tgl_lahir' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Tanggal lahir tidak boleh kosong'
          ]
        ],
        'j_kel' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Jenis kelamin tidak boleh kosong'
          ]
        ],
        'agama' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Agama tidak boleh kosong'
          ]
        ],
        'username' => [
          'rules' => 'required|is_unique[karyawan.username]',
          'errors' => [
            'required' => 'Username tidak boleh kosong',
            'is_unique' => 'Username sudah ada yang punya'
          ]
        ],
        'password' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Password tidak boleh kosong'
          ]
        ],
        'repassword' => [
          'rules' => 'required|matches[password]',
          'errors' => [
            'required' => 'Re-Type Password tidak boleh kosong',
            'matches' => 'Re-Type password tidak sesuai'
          ]
        ],

        'nip' => [
          'rules' => 'required|is_unique[karyawan.nip]',
          'errors' => [
            'required' => 'NIP tidak boleh kosong',
            'is_unique' => 'NIP harus unik'
          ]
        ],
        'jabatan_kode' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Jabatan tidak boleh kosong'
          ]
        ],
        'status_pegawai_kode' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Status Pegawai tidak boleh kosong'
          ]
        ],
        'gol_gaji' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Golongan Gaji tidak boleh kosong'
          ]
        ],
        // 'tahun_pangkat' => [
        //   'rules' => 'required|numeric',
        //   'errors' => [
        //     'required' => 'Tahun Pangkat tidak boleh kosong',
        //     'numeric' => 'Tahun pangkat harus berupa angka'
        //   ]
        // ],
        // 'golongan' => [
        //   'rules' => 'required',
        //   'errors' => [
        //     'required' => 'Golongan/Pangkat tidak boleh kosong'
        //   ]
        // ],
        'tgl_mulai_bekerja' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Tanggal mulai bekerja tidak boleh kosong'
          ]
        ],
        'foto' => [
          'rules' => 'max_size[foto,1024]|is_image[foto]|mime_in[foto,image/jpg,image/jpeg,image/png]',
          'errors' => [
            'max_size' => 'ukuran gambar terlalu besar. Max 1 mb ',
            'is_image' => 'yang anda pilih bukan gambar',
            'mime_in' => 'Gunakan file ekstensi jpg/jpeg/png'
          ]
        ],
      ])) {
        $validation = \Config\Services::validation();

        $data = [
          'responce' => 'error',
          'pesan' => $validation->listErrors()
        ];
      } else {
        // validasi sukses
        $fileFoto = $this->request->getFile('foto');

        // if ($fileFoto === null) {
        if ($fileFoto->getError() == 4) {
          // throw new \RuntimeException($fileFoto->getErrorString() . '(' . $fileFoto->getError() . ')');
          $namaFoto = "default.png";
        } else {
          //generate nama file random
          $namaFoto = $fileFoto->getRandomName();
        }

        $insert = [
          'nama_lengkap' => $this->request->getVar('nama_lengkap'),
          'nama_panggilan' => $this->request->getVar('nama_panggilan'),
          'gelar' => $this->request->getVar('gelar'),
          'tem_lahir' => $this->request->getVar('tem_lahir'),
          'tgl_lahir' => $this->request->getVar('tgl_lahir'),
          'tgl_mulai_bekerja' => $this->request->getVar('tgl_mulai_bekerja'),
          'j_kel' => $this->request->getVar('j_kel'),
          'agama' => $this->request->getVar('agama'),
          'status' => $this->request->getVar('status'),
          'username' => $this->request->getVar('username'),
          'password' => password_hash($this->request->getVar('password'), PASSWORD_DEFAULT),
          'jalan_no' => $this->request->getVar('jalan_no'),
          'rt' => $this->request->getVar('rt'),
          'rw' => $this->request->getVar('rw'),
          'desa_kel' => $this->request->getVar('desa_kel'),
          'kecamatan' => $this->request->getVar('kecamatan'),
          'kota' => $this->request->getVar('kota'),
          'kd_pos' => $this->request->getVar('kode_pos'),
          'jalan_no_domisili' => $this->request->getVar('jalan_no_domisili'),
          'rt_domisili' => $this->request->getVar('rt_domisili'),
          'rw_domisili' => $this->request->getVar('rw_domisili'),
          'desa_kel_domisili' => $this->request->getVar('desa_kel_domisili'),
          'kecamatan_domisili' => $this->request->getVar('kecamatan_domisili'),
          'kota_domisili' => $this->request->getVar('kota_domisili'),
          'kd_pos_domisili' => $this->request->getVar('kode_pos_domisili'),
          'email' => $this->request->getVar('email'),
          'telepon' => $this->request->getVar('telepon'),
          'no_ktp' => $this->request->getVar('ktp'),
          'no_kk' => $this->request->getVar('no_kk'),
          'nip' => $this->request->getVar('nip'),
          'no_npwp' => $this->request->getVar('no_npwp'),
          'no_bpjs_ketenagakerjaan' => $this->request->getVar('no_bpjs_ketenagakerjaan'),
          'no_bpjs_kesehatan' => $this->request->getVar('no_bpjs_kesehatan'),
          'bank' => $this->request->getVar('bank'),
          'no_rek' => $this->request->getVar('no_rekening'),
          'status_pegawai_kode' => $this->request->getVar('status_pegawai_kode'),
          'jabatan_kode' => $this->request->getVar('jabatan_kode'),
          'gol_gaji' => $this->request->getVar('gol_gaji'),
          'tahun_pangkat' => $this->request->getVar('tahun_pangkat'),
          'golongan' => $this->request->getVar('golongan'),
          'ijazah' => $this->request->getVar('ijazah'),
          'last_user' => $this->request->getVar('last_user'),
          'role_kode' => "UMUM",
          'foto' => $namaFoto

        ];

        if ($this->karyawanModel->insert($insert)) {
          //pindahkan gambar
          // $fileFoto->getError() == 4
          // if ($fileFoto != null) {
          if ($fileFoto->getError() != 4) {
            $fileFoto->move('asset/images/user', $namaFoto);
          }
        };

        $data = [
          'responce' => 'success',
          'pesan' => 'Data Pegawai berhasil ditambah',
        ];
      }
      echo json_encode($data);
    } else {
      echo "No Direct Script access allowed";
    }
  }

  // controller edit pegawai
  public function editformpegawai($id)
  {
    $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();
    $pegawai = $this->karyawanModel->getProfil($id);
    $jabatan = $this->jabatanModel->findAll();
    $statuspegawai = $this->statusPegawaiModel->findAll();
    $golgaji = $this->golGajiModel->findAll();
    $golongan = $this->pangkatModel->orderBy('sort', 'asc')->findAll();
    // dd($user);

    $data = [
      'title' => 'Edit Pegawai',
      'user' => $cekuser,
      'pegawai' => $pegawai,
      'jabatan' => $jabatan,
      'status' => $statuspegawai,
      'golgaji' => $golgaji,
      'golongan' => $golongan,
      'validation' => \Config\Services::validation()
    ];

    return view('backend/pegawai/editformpegawai', $data);
  }

  public function editpegawai()
  {
    if ($this->request->isAJAX()) {

      $username = $this->karyawanModel->where('id', $this->request->getVar('idpegawai'))->get()->getRowArray();
      // dd($username);

      if ($username['username'] == $this->request->getVar('username')) {
        $rule_username = 'required';
      } else {
        $rule_username = 'required|is_unique[karyawan.username]';
      }

      if (!$this->validate([
        'nama_lengkap' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Nama lengkap tidak boleh kosong'
          ]
        ],
        'nama_panggilan' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Nama panggilan tidak boleh kosong'
          ]
        ],
        'tem_lahir' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Tempat lahir tidak boleh kosong'
          ]
        ],
        'tgl_lahir' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Tanggal lahir tidak boleh kosong'
          ]
        ],
        'j_kel' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Jenis kelamin tidak boleh kosong'
          ]
        ],
        'agama' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Agama tidak boleh kosong'
          ]
        ],
        'username' => [
          'rules' => $rule_username,
          'errors' => [
            'required' => 'Username tidak boleh kosong',
            'is_unique' => 'Username sudah ada yang punya'
          ]
        ],
        'foto' => [
          'rules' => 'max_size[foto,1024]|is_image[foto]|mime_in[foto,image/jpg,image/jpeg,image/png]',
          'errors' => [
            'max_size' => 'ukuran gambar terlalu besar. Max 1 mb ',
            'is_image' => 'yang anda pilih bukan gambar',
            'mime_in' => 'Gunakan file ekstensi jpg/jpeg/png'
          ]
        ],
        'nip' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'NIP tidak boleh kosong'
          ]
        ],
        'jabatan_kode' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Kode Jabatan tidak boleh kosong'
          ]
        ],
        'status_pegawai_kode' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Status Pegawai tidak boleh kosong'
          ]
        ],
        'gol_gaji' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Golongan gaji tidak boleh kosong'
          ]
        ],
        // 'tahun_pangkat' => [
        //   'rules' => 'required|numeric',
        //   'errors' => [
        //     'required' => 'Tahun Pangkat tidak boleh kosong',
        //     'numeric' => 'Tahun pangkat hanya berupa angka'
        //   ]
        // ],
        // 'golongan' => [
        //   'rules' => 'required',
        //   'errors' => [
        //     'required' => 'Golongan/Pangkat tidak boleh kosong'
        //   ]
        // ],
        'tgl_mulai_bekerja' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Tanggal Mulai bekerja tidak boleh kosong'
          ]
        ]

      ])) {
        $validation = \Config\Services::validation();

        $data = [
          'responce' => 'error',
          'pesan' => $validation->listErrors()
        ];
      } else {
        // validasi sukses
        // $validation = \Config\Services::validation();
        $fileFoto = $this->request->getFile('foto');

        //cek gambar, apakah tetap gambar lama
        if ($fileFoto->getError() == 4) {
          $namaFoto = $this->request->getVar('fotoLama');
        } else {
          if ($this->request->getVar('fotoLama') == "default.png") {
            //generate nama file random
            $namaFoto = $fileFoto->getRandomName();

            //pindahkan gambar
            $fileFoto->move('asset/images/user', $namaFoto);
          } else {
            //generate nama file random
            $namaFoto = $fileFoto->getRandomName();

            //pindahkan gambar
            $fileFoto->move('asset/images/user', $namaFoto);

            //hapus gambar lama
            unlink('asset/images/user/' . $this->request->getPost('fotoLama'));
          }
        }

        $idpegawai = $this->request->getVar('idpegawai');

        $update = [
          'nama_lengkap' => $this->request->getVar('nama_lengkap'),
          'nama_panggilan' => $this->request->getVar('nama_panggilan'),
          'gelar' => $this->request->getVar('gelar'),
          'tem_lahir' => $this->request->getVar('tem_lahir'),
          'tgl_lahir' => $this->request->getVar('tgl_lahir'),
          'j_kel' => $this->request->getVar('j_kel'),
          'agama' => $this->request->getVar('agama'),
          'status' => $this->request->getVar('status'),
          'username' => $this->request->getVar('username'),
          'jalan_no' => $this->request->getVar('jalan_no'),
          'rt' => $this->request->getVar('rt'),
          'rw' => $this->request->getVar('rw'),
          'desa_kel' => $this->request->getVar('desa_kel'),
          'kecamatan' => $this->request->getVar('kecamatan'),
          'kota' => $this->request->getVar('kota'),
          'kd_pos' => $this->request->getVar('kode_pos'),
          'jalan_no_domisili' => $this->request->getVar('jalan_no_domisili'),
          'rt_domisili' => $this->request->getVar('rt_domisili'),
          'rw_domisili' => $this->request->getVar('rw_domisili'),
          'desa_kel_domisili' => $this->request->getVar('desa_kel_domisili'),
          'kecamatan_domisili' => $this->request->getVar('kecamatan_domisili'),
          'kota_domisili' => $this->request->getVar('kota_domisili'),
          'kd_pos_domisili' => $this->request->getVar('kode_pos_domisili'),
          'email' => $this->request->getVar('email'),
          'telepon' => $this->request->getVar('telepon'),
          'no_ktp' => $this->request->getVar('ktp'),
          'no_kk' => $this->request->getVar('no_kk'),
          'foto' => $namaFoto,
          'nip' => $this->request->getVar('nip'),
          'jabatan_kode' => $this->request->getVar('jabatan_kode'),
          'status_pegawai_kode' => $this->request->getVar('status_pegawai_kode'),
          'gol_gaji' => $this->request->getVar('gol_gaji'),
          'tahun_pangkat' => $this->request->getVar('tahun_pangkat'),
          'golongan' => $this->request->getVar('golongan'),
          'ijazah' => $this->request->getVar('ijazah'),
          'tgl_mulai_bekerja' => $this->request->getVar('tgl_mulai_bekerja'),
          'no_npwp' => $this->request->getVar('no_npwp'),
          'no_bpjs_ketenagakerjaan' => $this->request->getVar('no_bpjs_ketenagakerjaan'),
          'no_bpjs_kesehatan' => $this->request->getVar('no_bpjs_kesehatan'),
          'bank' => $this->request->getVar('bank'),
          'last_user' => $this->request->getVar('last_user'),
          'no_rek' => $this->request->getVar('no_rekening'),


        ];

        $this->karyawanModel->update($idpegawai, $update);

        $data = [
          'responce' => 'success',
          'pesan' => 'Profil Pegawai berhasil diupdate'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No Direct Script access allowed";
    }
  }

  public function editpasswordpegawai()
  {
    if ($this->request->isAJAX()) {
      if (!$this->validate([
        'password' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Password tidak boleh kosong'
          ]
        ],
        'repassword' => [
          'rules' => 'required|matches[password]',
          'errors' => [
            'required' => 'Retype password harus dipilih',
            'matches' => 'Password tidak sesuai dengan retype'
          ]
        ]
      ])) {
        $validation = \Config\Services::validation();

        $data = [
          'responce' => 'error',
          'pesan' => $validation->listErrors()
        ];
      } else {
        // validasi sukses
        $idpegawai = $this->request->getVar('idpegawaipassword');
        $password = password_hash($this->request->getVar('password'), PASSWORD_DEFAULT);

        $update = [
          'password' => $password
        ];

        $this->karyawanModel->update($idpegawai, $update);

        $data = [
          'responce' => 'success',
          'pesan' => 'Password berhasil diupdate'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No Direct Script access allowed";
    }
  }

  public function importpegawai()
  {
    if ($this->request->isAJAX()) {
      $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();
      $file = $this->request->getFile('filepegawai');

      if ($file) {


        $ext = $file->getClientExtension();

        if ($ext == 'xls') {
          $render = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
        } else {
          $render = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        }

        $spreadsheet = $render->load($file);

        // $data = $spreadsheet->getActiveSheet()->toArray();
        $sheet = $spreadsheet->getActiveSheet()->toArray("", true, true, true);
        // $sheet    = $objPHPExcel->getActiveSheet()->toArray('', true, true, true);




        foreach ($sheet as $idx => $data) {
          //skip index 1 karena title excel
          if ($idx == 1) {
            continue;
          }

          $nip = $data['B'];
          $username = $data['C'];
          $password = $data['D'];
          $nama_lengkap = $data['E'];
          $nama_panggilan = $data['F'];
          $gelar = $data['G'];
          $j_kel = strtolower($data['H']);
          $tem_lahir = $data['I'];
          $tgl_lahir = $data['J'];
          $jalan_no = $data['K'];
          $rt = $data['L'];
          $rw = $data['M'];
          $desa_kel = $data['N'];
          $kecamatan = $data['O'];
          $kd_pos = $data['P'];
          $kota = $data['Q'];
          $telepon = $data['R'];
          $email = $data['S'];
          $agama = strtolower($data['T']);
          $status = $data['U'];
          $tgl_mulai_bekerja = $data['V'];
          $status_pegawai_kode = $data['W'];
          $jabatan_kode = $data['X'];
          $gol_gaji = $data['Y'];
          $ijazah = $data['Z'];

          $no_ktp = $data['AA'];
          $no_kk = $data['AB'];
          $no_npwp = $data['AC'];
          $no_bpjs_ketenagakerjaan = $data['AD'];
          $no_bpjs_kesehatan = $data['AE'];
          $bank = $data['AF'];
          $no_rek = $data['AG'];


          // insert data
          $this->karyawanModel->insert([
            'nip'        =>    $nip,
            'username'        =>    $username,
            'password'       =>    password($password),
            'nama_lengkap'       =>    $nama_lengkap,
            'nama_panggilan'       =>    $nama_panggilan,
            'gelar'       =>    $gelar,
            'j_kel'       =>    $j_kel,
            'tem_lahir'       =>    $tem_lahir,
            'tgl_lahir'       =>    tanggal($tgl_lahir),
            'jalan_no'       =>    $jalan_no,
            'rt'       =>    $rt,
            'rw'       =>    $rw,
            'desa_kel'       =>    $desa_kel,
            'kecamatan'       =>    $kecamatan,
            'kd_pos'       =>    $kd_pos,
            'kota'       =>    $kota,
            'telepon'       =>    $telepon,
            'email'       =>    $email,
            'agama'       =>    $agama,
            'status'       =>    $status,
            'tgl_mulai_bekerja'       =>  tanggal($tgl_mulai_bekerja),
            'status_pegawai_kode'       =>    $status_pegawai_kode,
            'jabatan_kode'       =>    $jabatan_kode,
            'gol_gaji'       =>    $gol_gaji,
            // 'tahun_pangkat'       =>    $tahun_pangkat,
            // 'golongan'       =>    $golongan,
            'ijazah'       =>    $ijazah,
            'no_ktp'       =>    $no_ktp,
            'no_kk'       =>    $no_kk,
            'no_npwp'       =>    $no_npwp,
            'no_bpjs_ketenagakerjaan'       =>    $no_bpjs_ketenagakerjaan,
            'no_bpjs_kesehatan'       =>    $no_bpjs_kesehatan,
            'bank'       =>    $bank,
            'no_rek'       =>    $no_rek,
            'role_kode'       =>    'UMUM',
            'last_user' => $cekuser['nama_lengkap']
          ]);
        }

        $data = [
          'responce' => 'success',
          'pesan' => 'Import pegawai berhasil'
        ];
      } else {
        //upload gagal
        $data = [
          'responce' => 'error',
          'pesan' => 'Import pegawai gagal'
        ];
      }

      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function deletepegawai()
  {
    if ($this->request->isAJAX()) {
      if ($id = $this->request->getVar('checkbox_value')) {
        for ($count = 0; $count < count($id); $count++) {
          // $this->karyawanModel->delete_karyawan($id[$count]);
          $karya = $this->karyawanModel->where('id', $id[$count])->get()->getRowArray();

          if ($karya['foto'] != 'default.png' and $karya['foto'] != "") {
            unlink('asset/images/user/' . $karya['foto']);
          }

          $this->karyawanModel->where('id', $id[$count])->delete();
        }

        $data = [
          'responce' => 'success',
          'pesan' => 'Data pegawai berhasil dihapus'
        ];

        echo json_encode($data);
      }
    } else {
      echo "No direct script access allowed";
    }
  }

  // controller divisi pegawai
  public function divisi()
  {
    $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();
    $divisi = $this->divisiModel->findAll();
    $divisi2 = $this->divisiModel->findAll();
    // dd($divisi);

    $data = [
      'title' => 'Divisi Karyawan',
      'user' => $cekuser,
      'divisi' => $divisi,
      'divisi2' => $divisi2,
      'validation' => \Config\Services::validation()
    ];

    return view('backend/pegawai/divisi', $data);
  }

  // fetch divisi belum diatur
  public function fetchdivisibdpegawai()
  {

    if ($this->request->isAJAX()) {
      if ($pegawaibd = $this->karyawanModel->where('divisi', '')->where('username!=', 'adminmij')->findAll()) {
        $data = [
          'responce' => 'success',
          'pegawai' => $pegawaibd
        ];
      } else {
        $data = [
          'responce' => 'error',
          'pesan' => 'gagal fetch'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  // fetch divisi semua
  public function fetchdivisisemuapegawai()
  {

    if ($this->request->isAJAX()) {
      if ($pegawaisemua = $this->karyawanModel->where('username!=', 'adminmij')->findAll()) {

        $data = [
          'responce' => 'success',
          'pegawai' => $pegawaisemua
        ];
      } else {
        $data = [
          'responce' => 'error',
          'pesan' => 'gagal fetch'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  // delete divisi
  public function deletedivisipegawai($id)
  {
    if ($this->request->isAJAX()) {

      $update = [
        'divisi' => ''
      ];

      $this->karyawanModel->update($id, $update);
    } else {
      echo "No direct script access allowed";
    }
  }

  // fetch divisi satuan
  public function fetchdivisipegawai()
  {
    if ($this->request->isAJAX()) {
      $satuan = $this->request->getVar('divisiasal');
      // dd($satuan);
      if ($pegawaisatuan = $this->karyawanModel->getdivisisatuan($satuan)) {
        $data = [
          'responce' => 'success',
          'pegawai' => $pegawaisatuan
        ];
      } else {
        $data = [
          'responce' => 'error',
          'pesan' => 'gagal fetch'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }



  public function btntujuandivisipegawai()
  {
    if ($this->request->isAJAX()) {
      if ($id = $this->request->getVar('checkbox_value')) {
        $iddivisi = $this->request->getVar('iddivisitujuan');

        for ($count = 0; $count < count($id); $count++) {

          $peg = $this->karyawanModel->where('id', $id[$count])->get()->getRowArray();
          // dd($peg);
          $divisiawal = $peg['divisi'];

          if ($divisiawal == null) {
            $inputdivisi = $iddivisi;
          } else {
            $divisibaru = explode(",", $divisiawal);

            $divisibaru[] = $iddivisi;
            $inputdivisi = implode(',', $divisibaru);
          }

          $update = [
            'divisi' => $inputdivisi,
          ];
          // $this->userDivisiModel->where('id', $id[$count])->delete();
          $this->karyawanModel->update($id[$count], $update);
        }

        $data = [
          'responce' => 'success',
          'pesan' => 'Data Divisi pegawai berhasil dihapus'
        ];

        echo json_encode($data);
      }
    } else {
      echo "No direct script access allowed";
    }
  }

  // controller absen pegawai
  public function absen()
  {
    $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();

    $pegawai = $this->karyawanModel->findAll();
    $gol_gaji = $this->golGajiModel->findAll();

    $data = [
      'title' => 'Absen',
      'user' => $cekuser,
      'pegawai' => $pegawai,
      'gol_gaji' => $gol_gaji,
      'validation' => \Config\Services::validation()
    ];

    return view('backend/pegawai/absen', $data);
  }

  public function fetchabsenpegawai()
  {
    if ($this->request->isAJAX()) {
      if ($absen = $this->absenPegawaiModel->findAll()) {
        // dd($absen);
        $data = [
          'responce' => 'success',
          'absen' => $absen
        ];
      } else {
        $data = [
          'responce' => 'error',
          'pesan' => 'gagal fetch'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function saveabsenpegawai()
  {
    if ($this->request->isAJAX()) {
      if (!$this->validate([
        'nip' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'NIP tidak boleh kosong'
          ]
        ],
        'gol_gaji' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Golongan tidak boleh kosong'
          ]
        ],
        'bulan' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Bulan tidak boleh kosong'
          ]
        ],
        'tahun' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Tahun tidak boleh kosong',
            'numeric' => 'Tahun harus berupa angka'
          ]
        ],
        'sakit' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Jumlah sakit tidak boleh kosong',
            'numeric' => 'Jumlah sakit harus berupa angka'
          ]
        ],
        'izin' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Jumlah izin tidak boleh kosong',
            'numeric' => 'Jumlah izin harus berupa angka'
          ]
        ],
        'alpha' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Jumlah alpha tidak boleh kosong',
            'numeric' => 'Jumlah alpha harus berupa angka'
          ]
        ],
        'cuti' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Jumlah cuti tidak boleh kosong',
            'numeric' => 'Jumlah cuti harus berupa angka'
          ]
        ],

        'hadir' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Jumlah hadir tidak boleh kosong',
            'numeric' => 'Jumlah hadir harus berupa angka'
          ]
        ]
      ])) {
        $validation = \Config\Services::validation();
        $data = [
          'responce' => 'error',
          'pesan' => $validation->listErrors()
        ];
      } else {
        // validasi sukses
        $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();

        $nip = $this->request->getVar('nip');
        $idkaryawan = $this->karyawanModel->where('nip', $nip)->get()->getRowArray();


        $insert = [
          'nip' => $nip,
          'karyawan' => $idkaryawan['nama_lengkap'],
          'gol_gaji' => $this->request->getVar('gol_gaji'),
          'bulan' => $this->request->getVar('bulan'),
          'tahun' => $this->request->getVar('tahun'),
          'sakit' => $this->request->getVar('sakit'),
          'izin' => $this->request->getVar('izin'),
          'alpha' => $this->request->getVar('alpha'),
          'hadir' => $this->request->getVar('hadir'),
          'setengah' => $this->request->getVar('setengah'),
          'terlambat' => $this->request->getVar('terlambat'),
          'cuti' => $this->request->getVar('cuti'),
          'tugas' => $this->request->getVar('tugas'),
          'libur' => $this->request->getVar('libur'),
          'bkk' => $this->request->getVar('bkk'),
          'makan_puasa' => $this->request->getVar('makan_puasa'),
          'security' => $this->request->getVar('security'),
          'transport' => $this->request->getVar('transport'),
          'user_update' => $cekuser['nama_lengkap']
        ];

        $this->absenPegawaiModel->insert($insert);

        $data = [
          'responce' => 'success',
          'pesan' => 'Absen berhasil ditambah'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function editmodalabsen()
  {
    if ($this->request->isAJAX()) {
      $idabsen = $this->request->getVar('idabsen');
      if ($absen = $this->absenPegawaiModel->where('id', $idabsen)->get()->getRowArray()) {
        $data = [
          'responce' => 'success',
          'absen' => $absen
        ];
      } else {
        $data = [
          'responce' => 'error',
          'pesan' => 'gagal memunculkan modal edit data'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function editabsenpegawai()
  {
    if ($this->request->isAJAX()) {
      if (!$this->validate([
        'nip' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'NIP tidak boleh kosong'
          ]
        ],
        'bulan' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Bulan tidak boleh kosong'
          ]
        ],
        'tahun' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Tahun tidak boleh kosong',
            'numeric' => 'Tahun harus berupa angka'
          ]
        ],
        'sakit' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Jumlah sakit tidak boleh kosong',
            'numeric' => 'Jumlah sakit harus berupa angka'
          ]
        ],
        'izin' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Jumlah izin tidak boleh kosong',
            'numeric' => 'Jumlah izin harus berupa angka'
          ]
        ],
        'alpha' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Jumlah alpha tidak boleh kosong',
            'numeric' => 'Jumlah alpha harus berupa angka'
          ]
        ],
        'cuti' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Jumlah cuti tidak boleh kosong',
            'numeric' => 'Jumlah cuti harus berupa angka'
          ]
        ],
        'hadir' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Jumlah hadir tidak boleh kosong',
            'numeric' => 'Jumlah hadir harus berupa angka'
          ]
        ]
      ])) {
        $validation = \Config\Services::validation();
        $data = [
          'responce' => 'error',
          'pesan' => $validation->listErrors()
        ];
      } else {
        // validasi sukses
        $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();
        $idabsen = $this->request->getVar('idabsen');
        $update = [
          // 'nip' => $this->request->getVar('nip'),
          // 'bulan' => $this->request->getVar('bulan'),
          // 'tahun' => $this->request->getVar('tahun'),
          'sakit' => $this->request->getVar('sakit'),
          'izin' => $this->request->getVar('izin'),
          'alpha' => $this->request->getVar('alpha'),
          'hadir' => $this->request->getVar('hadir'),
          'setengah' => $this->request->getVar('setengah'),
          'terlambat' => $this->request->getVar('terlambat'),
          'cuti' => $this->request->getVar('cuti'),
          'tugas' => $this->request->getVar('tugas'),
          'libur' => $this->request->getVar('libur'),
          'bkk' => $this->request->getVar('bkk'),
          'makan_puasa' => $this->request->getVar('makan_puasa'),
          'security' => $this->request->getVar('security'),
          'transport' => $this->request->getVar('transport'),
          'user_update' => $cekuser['nama_lengkap']
        ];


        $this->absenPegawaiModel->update($idabsen, $update);

        $data = [
          'responce' => 'success',
          'pesan' => 'Absen berhasil diupdate'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function deleteabsenpegawai()
  {
    if ($this->request->isAJAX()) {
      if ($id = $this->request->getVar('checkbox_value')) {
        for ($count = 0; $count < count($id); $count++) {
          $this->absenPegawaiModel->where('id', $id[$count])->delete();
        }

        $data = [
          'responce' => 'success',
          'pesan' => 'Data absen berhasil dihapus'
        ];

        echo json_encode($data);
      }
    } else {
      echo "No direct script access allowed";
    }
  }

  public function importabsenpegawai()
  {
    if ($this->request->isAJAX()) {

      $file = $this->request->getFile('fileabsenpegawai');

      if ($file) {

        $ext = $file->getClientExtension();

        if ($ext == 'xls') {
          $render = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
        } else {
          $render = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        }

        $spreadsheet = $render->load($file);

        // $data = $spreadsheet->getActiveSheet()->toArray();
        $sheet = $spreadsheet->getActiveSheet()->toArray("", true, true, true);
        // $sheet    = $objPHPExcel->getActiveSheet()->toArray('', true, true, true);



        $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();

        $angkakosong = 0;
        // $ceknip = array();
        $barisgagal = array();
        foreach ($sheet as $cekidx => $cekdata) {
          //skip index 1 karena title excel
          if ($cekidx == 1) {
            continue;
          }

          $cnip = $cekdata['B'];
          // array_push($ceknip, $cnip);

          if ($this->karyawanModel->where('nip', $cnip)->countAllResults() == 0) {
            $angkakosong++;
            array_push($barisgagal, $cekidx);
          }
        }

        if ($angkakosong > 0) {
          //upload gagal
          $data = [
            'responce' => 'errorkosong',
            'pesan' => 'Import absen pegawai gagal',
            'barisgagal' => $barisgagal
          ];
        } else {
          foreach ($sheet as $idx => $data) {
            //skip index 1 karena title excel
            if ($idx == 1) {
              continue;
            }

            $nip = $data['B'];
            $gol_gaji = $data['D'];
            $bulan = $data['E'];
            $tahun = $data['F'];
            $sakit = $data['G'];
            $izin = $data['H'];
            $alpha = $data['I'];
            $hadir = $data['J'];
            $setengah = $data['K'];
            $terlambat = $data['L'];
            $cuti = $data['M'];
            $tugas = $data['N'];
            $libur = $data['O'];
            $bkk = $data['P'];
            $makan_puasa = $data['Q'];
            $makan_security = $data['R'];
            $transport = $data['S'];

            $idkaryawan = $this->karyawanModel->where('nip', $nip)->get()->getRowArray();

            // insert data
            $this->absenPegawaiModel->insert([
              'nip'        =>    $nip,
              'karyawan' => $idkaryawan['nama_lengkap'],
              'gol_gaji'        =>    $gol_gaji,
              'bulan'        =>    $bulan,
              'tahun'       =>    $tahun,
              'sakit'       =>    $sakit,
              'izin'       =>    $izin,
              'alpha'       =>    $alpha,
              'hadir'       =>    $hadir,
              'setengah'       =>    $setengah,
              'terlambat'       =>    $terlambat,
              'cuti'       =>    $cuti,
              'tugas'       =>    $tugas,
              'libur'       =>    $libur,
              'bkk'       =>    $bkk,
              'makan_puasa'       =>    $makan_puasa,
              'security'       =>    $makan_security,
              'transport'       =>    $transport,
              'user_update'       =>    $cekuser['nama_lengkap']
            ]);
          }

          $data = [
            'responce' => 'success',
            'pesan' => 'Import absen pegawai berhasil'
          ];
        }
      } else {
        //upload gagal
        $data = [
          'responce' => 'error',
          'pesan' => 'Import absen pegawai gagal'
        ];
      }

      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  // controller Nilai DP3
  public function nilaidp3()
  {
    $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();

    $pegawai = $this->karyawanModel->findAll();


    $data = [
      'title' => 'Nilai DP3',
      'user' => $cekuser,
      'pegawai' => $pegawai,
      'validation' => \Config\Services::validation()
    ];

    return view('backend/pegawai/nilaidp3', $data);
  }

  public function fetchnilaipegawai()
  {
    if ($this->request->isAJAX()) {
      if ($nilai = $this->nilaiPegawaiModel->findAll()) {
        // dd($absen);
        $data = [
          'responce' => 'success',
          'nilai' => $nilai
        ];
      } else {
        $data = [
          'responce' => 'error',
          'pesan' => 'gagal fetch'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function savenilaipegawai()
  {
    if ($this->request->isAJAX()) {
      if (!$this->validate([
        'nip' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'NIP tidak boleh kosong'
          ]
        ],
        'tahun' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Tahun tidak boleh kosong',
            'numeric' => 'Tahun harus berupa angka'
          ]
        ],
        'kehadiran' => [
          'rules' => 'required|in_list[i,sb,b,c,r,sr]',
          'errors' => [
            'required' => 'Kehadiran tidak boleh kosong',
            'in_list' => 'Kehadiran hanya boleh I, SB, B, C, R, SR'
          ]
        ],
        'disiplin' => [
          'rules' => 'required|in_list[i,sb,b,c,r,sr]',
          'errors' => [
            'required' => 'Disiplin tidak boleh kosong',
            'in_list' => 'Disiplin hanya boleh I, SB, B, C, R, SR'
          ]
        ],
        'kinerja' => [
          'rules' => 'required|in_list[i,sb,b,c,r,sr]',
          'errors' => [
            'required' => 'Kinerja tidak boleh kosong',
            'in_list' => 'Kinerja hanya boleh I, SB, B, C, R, SR'
          ]
        ],
        'etika' => [
          'rules' => 'required|in_list[i,sb,b,c,r,sr]',
          'errors' => [
            'required' => 'Etika tidak boleh kosong',
            'in_list' => 'Etika hanya boleh I, SB, B, C, R, SR'
          ]
        ],
        'loyalitas' => [
          'rules' => 'required|in_list[i,sb,b,c,r,sr]',
          'errors' => [
            'required' => 'Loyalitas tidak boleh kosong',
            'in_list' => 'Loyalitas hanya boleh I, SB, B, C, R, SR'
          ]
        ]

      ])) {
        $validation = \Config\Services::validation();
        $data = [
          'responce' => 'error',
          'pesan' => $validation->listErrors()
        ];
      } else {
        // validasi sukses
        $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();

        $nip = $this->request->getVar('nip');
        $idkaryawan = $this->karyawanModel->where('nip', $nip)->get()->getRowArray();

        $kehadiran = strtolower($this->request->getVar('kehadiran'));
        $disiplin = strtolower($this->request->getVar('disiplin'));
        $kinerja = strtolower($this->request->getVar('kinerja'));
        $etika = strtolower($this->request->getVar('etika'));
        $loyalitas = strtolower($this->request->getVar('loyalitas'));

        if ($kehadiran == 'c' or $kehadiran == 'r' or $kehadiran == 'sr' or $disiplin == 'c' or $disiplin == 'r' or $disiplin == 'sr' or $kinerja == 'c' or $kinerja == 'r' or $kinerja == 'sr' or $etika == 'c' or $etika == 'r' or $etika == 'sr'  or $loyalitas == 'c' or $loyalitas == 'r' or $loyalitas == 'sr') {
          $kesimpulan = 0;
        } else {
          $kesimpulan = 1;
        }

        $insert = [
          'nip' => $nip,
          'karyawan' => $idkaryawan['nama_lengkap'],
          'tahun' => $this->request->getVar('tahun'),
          'kehadiran' => $kehadiran,
          'disiplin' => $disiplin,
          'kinerja' => $kinerja,
          'etika' => $etika,
          'loyalitas' => $loyalitas,
          'kesimpulan' => $kesimpulan,
          'user_update' => $cekuser['nama_lengkap']
        ];

        $this->nilaiPegawaiModel->insert($insert);

        $data = [
          'responce' => 'success',
          'pesan' => 'Nilai berhasil ditambah'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function editmodalnilai()
  {
    if ($this->request->isAJAX()) {
      $idnilai = $this->request->getVar('idnilai');
      if ($nilai = $this->nilaiPegawaiModel->where('id', $idnilai)->get()->getRowArray()) {
        $data = [
          'responce' => 'success',
          'nilai' => $nilai
        ];
      } else {
        $data = [
          'responce' => 'error',
          'pesan' => 'gagal memunculkan modal edit data'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function editnilaipegawai()
  {
    if ($this->request->isAJAX()) {
      if (!$this->validate([
        'nip' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'NIP tidak boleh kosong'
          ]
        ],

        'tahun' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Tahun tidak boleh kosong',
            'numeric' => 'Tahun harus berupa angka'
          ]
        ],
        'kehadiran' => [
          'rules' => 'required|in_list[i,sb,b,c,r,sr]',
          'errors' => [
            'required' => 'Kehadiran tidak boleh kosong',
            'in_list' => 'Kehadiran hanya boleh I, SB, B, C, R, SR'
          ]
        ],
        'disiplin' => [
          'rules' => 'required|in_list[i,sb,b,c,r,sr]',
          'errors' => [
            'required' => 'Disiplin tidak boleh kosong',
            'in_list' => 'Disiplin hanya boleh I, SB, B, C, R, SR'
          ]
        ],
        'kinerja' => [
          'rules' => 'required|in_list[i,sb,b,c,r,sr]',
          'errors' => [
            'required' => 'Kinerja tidak boleh kosong',
            'in_list' => 'Kinerja hanya boleh I, SB, B, C, R, SR'
          ]
        ],
        'etika' => [
          'rules' => 'required|in_list[i,sb,b,c,r,sr]',
          'errors' => [
            'required' => 'Etika tidak boleh kosong',
            'in_list' => 'Etika hanya boleh I, SB, B, C, R, SR'
          ]
        ],
        'loyalitas' => [
          'rules' => 'required|in_list[i,sb,b,c,r,sr]',
          'errors' => [
            'required' => 'Loyalitas tidak boleh kosong',
            'in_list' => 'Loyalitas hanya boleh I, SB, B, C, R, SR'
          ]
        ]
      ])) {
        $validation = \Config\Services::validation();
        $data = [
          'responce' => 'error',
          'pesan' => $validation->listErrors()
        ];
      } else {
        // validasi sukses
        $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();
        $idnilai = $this->request->getVar('idnilai');


        $kehadiran = strtolower($this->request->getVar('kehadiran'));
        $disiplin = strtolower($this->request->getVar('disiplin'));
        $kinerja = strtolower($this->request->getVar('kinerja'));
        $etika = strtolower($this->request->getVar('etika'));
        $loyalitas = strtolower($this->request->getVar('loyalitas'));

        if ($kehadiran == 'c' or $kehadiran == 'r' or $kehadiran == 'sr' or $disiplin == 'c' or $disiplin == 'r' or $disiplin == 'sr' or $kinerja == 'c' or $kinerja == 'r' or $kinerja == 'sr' or $etika == 'c' or $etika == 'r' or $etika == 'sr'  or $loyalitas == 'c' or $loyalitas == 'r' or $loyalitas == 'sr') {
          $kesimpulan = 0;
        } else {
          $kesimpulan = 1;
        }


        $update = [
          'kehadiran' => $kehadiran,
          'disiplin' => $disiplin,
          'kinerja' => $kinerja,
          'etika' => $etika,
          'loyalitas' => $loyalitas,
          'kesimpulan' => $kesimpulan,
          'user_update' => $cekuser['nama_lengkap']
        ];


        $this->nilaiPegawaiModel->update($idnilai, $update);

        $data = [
          'responce' => 'success',
          'pesan' => 'Nilai berhasil diupdate'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function deletenilaipegawai()
  {
    if ($this->request->isAJAX()) {
      if ($id = $this->request->getVar('checkbox_value')) {
        for ($count = 0; $count < count($id); $count++) {
          $this->nilaiPegawaiModel->where('id', $id[$count])->delete();
        }

        $data = [
          'responce' => 'success',
          'pesan' => 'Data nilai berhasil dihapus'
        ];

        echo json_encode($data);
      }
    } else {
      echo "No direct script access allowed";
    }
  }

  public function importnilaipegawai()
  {
    if ($this->request->isAJAX()) {

      $file = $this->request->getFile('filenilaipegawai');

      if ($file) {


        $ext = $file->getClientExtension();

        if ($ext == 'xls') {
          $render = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
        } else {
          $render = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        }

        $spreadsheet = $render->load($file);

        // $data = $spreadsheet->getActiveSheet()->toArray();
        $sheet = $spreadsheet->getActiveSheet()->toArray("", true, true, true);
        // $sheet    = $objPHPExcel->getActiveSheet()->toArray('', true, true, true);



        $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();

        $angkakosong = 0;
        // $ceknip = array();
        $barisgagal = array();
        foreach ($sheet as $cekidx => $cekdata) {
          //skip index 1 karena title excel
          if ($cekidx == 1) {
            continue;
          }

          $cnip = $cekdata['B'];
          // array_push($ceknip, $cnip);

          if ($this->karyawanModel->where('nip', $cnip)->countAllResults() == 0) {
            $angkakosong++;
            array_push($barisgagal, $cekidx);
          }
        }

        if ($angkakosong > 0) {
          //upload gagal
          $data = [
            'responce' => 'errorkosong',
            'pesan' => 'Import absen pegawai gagal',
            'barisgagal' => $barisgagal
          ];
        } else {
          foreach ($sheet as $idx => $data) {
            //skip index 1 karena title excel
            if ($idx == 1) {
              continue;
            }



            $nip = $data['B'];
            $tahun = $data['D'];
            $kehadiran = strtolower($data['E']);
            $disiplin = strtolower($data['F']);
            $kinerja = strtolower($data['G']);
            $etika = strtolower($data['H']);
            $loyalitas = strtolower($data['I']);

            if ($kehadiran == 'c' or $kehadiran == 'r' or $kehadiran == 'sr' or $disiplin == 'c' or $disiplin == 'r' or $disiplin == 'sr' or $kinerja == 'c' or $kinerja == 'r' or $kinerja == 'sr' or $etika == 'c' or $etika == 'r' or $etika == 'sr'  or $loyalitas == 'c' or $loyalitas == 'r' or $loyalitas == 'sr') {
              $kesimpulan = 0;
            } else {
              $kesimpulan = 1;
            }

            $idkaryawan = $this->karyawanModel->where('nip', $nip)->get()->getRowArray();

            // insert data
            $this->nilaiPegawaiModel->insert([
              'nip'        =>    $nip,
              'karyawan' => $idkaryawan['nama_lengkap'],
              'tahun'       =>    $tahun,
              'kehadiran'       =>    $kehadiran,
              'disiplin'       =>    $disiplin,
              'kinerja'       =>    $kinerja,
              'etika'       =>    $etika,
              'loyalitas'       =>    $loyalitas,
              'kesimpulan'       =>    $kesimpulan,
              'user_update'       =>    $cekuser['nama_lengkap']
            ]);
          }

          $data = [
            'responce' => 'success',
            'pesan' => 'Import nilai pegawai berhasil'
          ];
        }
      } else {
        //upload gagal
        $data = [
          'responce' => 'error',
          'pesan' => 'Import nilai pegawai gagal'
        ];
      }

      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }


  // controller input pangkat
  public function inputpangkat()
  {
    $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();

    $pegawai = $this->karyawanModel->findAll();
    $daftarpangkat = $this->inputPangkatModel->findAll();
    $pangkat = $this->pangkatModel->findAll();


    $data = [
      'title' => 'Daftar Input Pangkat',
      'user' => $cekuser,
      'pegawai' => $pegawai,
      'daftarpangkat' => $daftarpangkat,
      'pangkat' => $pangkat,
      'validation' => \Config\Services::validation()
    ];

    return view('backend/pegawai/inputpangkat', $data);
  }

  public function fetchdaftarpangkatpegawai()
  {
    if ($this->request->isAJAX()) {
      if ($pangkat = $this->inputPangkatModel->findAll()) {
        // dd($absen);
        $data = [
          'responce' => 'success',
          'pangkat' => $pangkat
        ];
      } else {
        $data = [
          'responce' => 'error',
          'pesan' => 'gagal fetch'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function savedaftarpangkatpegawai()
  {
    if ($this->request->isAJAX()) {
      if (!$this->validate([
        'nip' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'NIP tidak boleh kosong'
          ]
        ],
        'tmt' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'TMT tidak boleh kosong'
          ]
        ],
        'tahun' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Tahun tidak boleh kosong',
            'numeric' => 'Tahun harus berupa angka'
          ]
        ],
        'pangkat' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Pangkat tidak boleh kosong'
          ]
        ]

      ])) {
        $validation = \Config\Services::validation();
        $data = [
          'responce' => 'error',
          'pesan' => $validation->listErrors()
        ];
      } else {
        // validasi sukses
        $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();

        $nip = $this->request->getVar('nip');
        $idkaryawan = $this->karyawanModel->where('nip', $nip)->get()->getRowArray();


        $insert = [
          'nip' => $nip,
          'nama' => $idkaryawan['nama_lengkap'],
          'tahun' => $this->request->getVar('tahun'),
          'tmt' => $this->request->getVar('tmt'),
          'pangkat' => $this->request->getVar('pangkat'),
        ];

        $this->inputPangkatModel->insert($insert);

        $data = [
          'responce' => 'success',
          'pesan' => 'Pangkat berhasil ditambah'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function editmodaldaftarpangkat()
  {
    if ($this->request->isAJAX()) {
      $iddaftarpangkat = $this->request->getVar('iddaftarpangkat');
      if ($pangkat = $this->inputPangkatModel->where('id', $iddaftarpangkat)->get()->getRowArray()) {
        $nama_pangkat = $this->pangkatModel->orderBy('sort', 'asc')->findColumn('golongan');

        $data = [
          'responce' => 'success',
          'pangkat' => $pangkat,
          'nama_pangkat' => $nama_pangkat
        ];
      } else {
        $data = [
          'responce' => 'error',
          'pesan' => 'gagal memunculkan modal edit data'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function editdaftarpangkatpegawai()
  {
    if ($this->request->isAJAX()) {
      if (!$this->validate([
        'nip' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'NIP tidak boleh kosong'
          ]
        ],

        'tmt' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'TMT tidak boleh kosong'
          ]
        ],
        'tahun' => [
          'rules' => 'required|numeric',
          'errors' => [
            'required' => 'Tahun tidak boleh kosong',
            'numeric' => 'Tahun harus berupa angka'
          ]
        ],
        'pangkat' => [
          'rules' => 'required',
          'errors' => [
            'required' => 'Pangkat tidak boleh kosong'
          ]
        ]

      ])) {
        $validation = \Config\Services::validation();
        $data = [
          'responce' => 'error',
          'pesan' => $validation->listErrors()
        ];
      } else {
        // validasi sukses
        $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();
        $iddaftarpangkat = $this->request->getVar('iddaftarpangkat');

        $update = [
          'tmt' => $this->request->getVar('tmt'),
          'pangkat' => $this->request->getVar('pangkat'),
          'tahun' => $this->request->getVar('tahun')

        ];


        $this->inputPangkatModel->update($iddaftarpangkat, $update);

        $data = [
          'responce' => 'success',
          'pesan' => 'Pangkat berhasil diupdate'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function deletedaftarpangkatpegawai()
  {
    if ($this->request->isAJAX()) {
      if ($id = $this->request->getVar('checkbox_value')) {
        for ($count = 0; $count < count($id); $count++) {
          $this->inputPangkatModel->where('id', $id[$count])->delete();
        }

        $data = [
          'responce' => 'success',
          'pesan' => 'Data pangkat berhasil dihapus'
        ];

        echo json_encode($data);
      }
    } else {
      echo "No direct script access allowed";
    }
  }

  public function importdaftarpangkatpegawai()
  {
    if ($this->request->isAJAX()) {

      $file = $this->request->getFile('filepangkatpegawai');

      if ($file) {

        $ext = $file->getClientExtension();

        if ($ext == 'xls') {
          $render = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
        } else {
          $render = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        }

        $spreadsheet = $render->load($file);

        // $data = $spreadsheet->getActiveSheet()->toArray();
        $sheet = $spreadsheet->getActiveSheet()->toArray("", true, true, true);
        // $sheet    = $objPHPExcel->getActiveSheet()->toArray('', true, true, true);




        $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();

        $angkakosong = 0;
        // $ceknip = array();
        $barisgagal = array();
        foreach ($sheet as $cekidx => $cekdata) {
          //skip index 1 karena title excel
          if ($cekidx == 1) {
            continue;
          }

          $cnip = $cekdata['B'];
          $cpangkat = $cekdata['F'];
          // array_push($ceknip, $cnip);

          if ($this->karyawanModel->where('nip', $cnip)->countAllResults() == 0) {
            $angkakosong++;
            array_push($barisgagal, $cekidx);
          }

          if ($this->pangkatModel->where('golongan', $cpangkat)->countAllResults() == 0) {
            $angkakosong++;
            array_push($barisgagal, $cekidx);
          }
        }

        if ($angkakosong > 0) {
          //upload gagal
          $data = [
            'responce' => 'errorkosong',
            'pesan' => 'Import Pangkat pegawai gagal',
            'barisgagal' => $barisgagal
          ];
        } else {
          foreach ($sheet as $idx => $data) {
            //skip index 1 karena title excel
            if ($idx == 1) {
              continue;
            }



            $nip = $data['B'];
            $tmt = $data['D'];
            $tahun = $data['E'];
            $pangkat = $data['F'];

            $idkaryawan = $this->karyawanModel->where('nip', $nip)->get()->getRowArray();

            // insert data
            $this->inputPangkatModel->insert([
              'nip'        =>    $nip,
              'nama' => $idkaryawan['nama_lengkap'],
              'tahun'       =>    $tahun,
              'tmt'       =>    tanggal($tmt),
              'pangkat'       =>    $pangkat
            ]);
          }

          $data = [
            'responce' => 'success',
            'pesan' => 'Import pangkat pegawai berhasil'
          ];
        }
      } else {
        //upload gagal
        $data = [
          'responce' => 'error',
          'pesan' => 'Import pangkat pegawai gagal'
        ];
      }

      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }








  // controller cek pangkat
  public function cekpangkat()
  {
    $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();

    $pegawai = $this->karyawanModel->findAll();


    $data = [
      'title' => 'Cek Pangkat',
      'user' => $cekuser,
      'pegawai' => $pegawai,
      'validation' => \Config\Services::validation()
    ];

    return view('backend/pegawai/cekpangkat', $data);
  }

  public function cektglpangkat()
  {
    $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();
    $pegawai = $this->karyawanModel->findAll();
    $pangkat = $this->pangkatModel->orderBy('sort', 'asc')->findColumn('golongan');



    $tahunawal = $this->request->getVar('tahunawal');
    $tahunakhir = $this->request->getVar('tahunakhir');

    $jumlahtahun = ($tahunakhir - $tahunawal) + 1;

    $arraytahun = [];

    $i = 0;
    while ($i < $jumlahtahun) {
      $arraytahun[] = $tahunawal + $i;
      $i++;
    }

    $data = [
      'title' => 'Cek Pangkat Periode ' . $tahunawal . ' - ' . $tahunakhir,
      'user' => $cekuser,
      'pegawai' => $pegawai,
      'tahunawal' => $tahunawal,
      'tahunakhir' => $tahunakhir,
      'arraytahun' => $arraytahun,
      'pangkat' => $pangkat,
      'validation' => \Config\Services::validation()
    ];

    return view('backend/pegawai/cektglpangkat', $data);
  }



  public function fetchcekpangkat()
  {
    if ($this->request->isAJAX()) {
      if ($cekpangkat = $this->karyawanModel->findAll()) {
        // dd($absen);
        $data = [
          'responce' => 'success',
          'cekpangkat' => $cekpangkat
        ];
      } else {
        $data = [
          'responce' => 'error',
          'pesan' => 'gagal fetch'
        ];
      }
      echo json_encode($data);
    } else {
      echo "No direct script access allowed";
    }
  }

  public function ceknilaidp3()
  {
    $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();

    $pegawai = $this->karyawanModel->findAll();


    $data = [
      'title' => 'Cek Nilai DP3',
      'user' => $cekuser,
      'pegawai' => $pegawai,
      'validation' => \Config\Services::validation()
    ];

    return view('backend/pegawai/ceknilaidp3', $data);
  }

  public function cektglnilai()
  {
    $cekuser = $this->karyawanModel->where('id', session('id'))->get()->getRowArray();
    $pegawai = $this->karyawanModel->findAll();
    // $pangkat = $this->pangkatModel->orderBy('sort', 'asc')->findColumn('golongan');

    $tahunawal = $this->request->getVar('tahunawal');
    $tahunakhir = $this->request->getVar('tahunakhir');

    $jumlahtahun = ($tahunakhir - $tahunawal) + 1;

    $arraytahun = [];

    $i = 0;
    while ($i < $jumlahtahun) {
      $arraytahun[] = $tahunawal + $i;
      $i++;
    }

    $data = [
      'title' => 'Cek Nilai DP3 Periode ' . $tahunawal . ' - ' . $tahunakhir,
      'user' => $cekuser,
      'pegawai' => $pegawai,
      'tahunawal' => $tahunawal,
      'tahunakhir' => $tahunakhir,
      'arraytahun' => $arraytahun,
      // 'pangkat' => $pangkat,
      'validation' => \Config\Services::validation()
    ];

    return view('backend/pegawai/cektglnilai', $data);
  }
}
