<?php
defined('BASEPATH') or exit('No direct script access allowed');
class Admin_model extends CI_Model
{
  // start datatables
  var $column_order = array(null, 'koreg', 'nama'); //set column field database for datatable orderable
  var $column_search = array('koreg', 'nama'); //set column field database for datatable searchable
  var $order = array('koreg' => 'asc'); // default order

  private function _get_datatables_query()
  {
    $this->db->select('register.*, jalur_ppdb.ppdb as namaJalur');
    $this->db->from('register');
    $this->db->join('jalur_ppdb', 'register.id_jalur = jalur_ppdb.id');
    $this->db->where('statusDaftar', 2);
    // $this->db->join('jalur_ppdb', 'pendaftar.id_jalur = jalur_ppdb.id');
    $i = 0;
    foreach ($this->column_search as $item) { // loop column
      if (@$_POST['search']['value']) { // if datatable send POST for search
        if ($i === 0) { // first loop
          $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
          $this->db->like($item, $_POST['search']['value']);
        } else {
          $this->db->or_like($item, $_POST['search']['value']);
        }
        if (count($this->column_search) - 1 == $i) //last loop
          $this->db->group_end(); //close bracket
      }
      $i++;
    }

    if (isset($_POST['order'])) { // here order processing
      $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
    } else if (isset($this->order)) {
      $order = $this->order;
      $this->db->order_by(key($order), $order[key($order)]);
    }
  }
  function get_datatables()
  {
    $this->_get_datatables_query();
    if (@$_POST['length'] != -1)
      $this->db->limit(@$_POST['length'], @$_POST['start']);
    $query = $this->db->get();
    return $query->result();
  }
  function count_filtered()
  {
    $this->_get_datatables_query();
    $query = $this->db->get();
    return $query->num_rows();
  }
  function count_all()
  {
    $this->db->from('register');
    return $this->db->count_all_results();
  }
  // end datatables
  // ambil data verifikasi
  public function getVerifikasiById($id)
  {
    $dataUser = $this->db->get_where('register', ['koreg' => $id])->row_array();
    $hasil = [];
    if ($dataUser['id_jalur'] == 2) {
      $this->db->select('register.*,register.nisn as nisnSiswa,nilai.total,jalur_ppdb.ppdb,quota,afirmasi.*,jenis_afirmasi.*');
      $this->db->from('register');
      $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
      $this->db->join('afirmasi', 'afirmasi.id_pendaftar = register.id');
      $this->db->join('jenis_afirmasi', 'jenis_afirmasi.id = afirmasi.afirmasi');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->where('register.koreg=' . $id);
      $hasil = $this->db->get()->row_array();
    } else if ($dataUser['id_jalur'] == 4) {
      $this->db->select('register.*,register.nisn as nisnSiswa,jalur_ppdb.*,prestasi.*,kat_satuan.satuan,kategori_penyelengara.peyelengara,nilai.total,kejuaraansiswa.namaKejuaraan,skor,kejuaraansiswa.skorKompetensi');
      $this->db->from('register');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->join('kejuaraansiswa', 'kejuaraansiswa.id_pendaftar = register.id');
      $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
      $this->db->join('prestasi', 'prestasi.id = kejuaraansiswa.id_kejuaraan');
      $this->db->join('kat_satuan', 'kat_satuan.id_satuan = prestasi.id_satuan');
      $this->db->join('kategori_penyelengara', 'kategori_penyelengara.id_penyelengara = kat_satuan.id_penyelengara');
      $this->db->where('register.koreg=' . $id);
      $hasil = $this->db->get()->row_array();
    } else {
      $this->db->select('register.*,register.nisn as nisnSiswa,nilai.*,jalur_ppdb.*');
      $this->db->from('register');
      $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->where('register.koreg=' . $id);
      $hasil = $this->db->get()->row_array();
    }

    return $hasil;
  }

  public function getDaftarById($data)
  {
    $id = $data['id'];
    $koreg = $data['koreg'];
    $pendaftar = $this->db->get_where('register', ['id' => $id, 'koreg' => $koreg])->row_array();

    // cek ada gak
    if ($pendaftar) {
      // cek jalaurnya
      if ($pendaftar['id_jalur'] == 4) {
        // ambil pendaftar dengan jalur kejuaraan
        return $this->_getDataPendaftarKejuaraan($pendaftar['id']);
      } else if ($pendaftar['id_jalur'] == 2) {
        return $this->_getDataPendaftarAfirmasi($pendaftar['id']);
      } else {
        return $this->_getDataPendaftarBiasa($pendaftar['id']);
      }
    }

    // $query = "SELECT * FROM `pendaftar` WHERE id=$id AND koreg=$koreg";
    // return $this->db->query($query)->row_array();

    // return $this->db->get_where('pendaftar', ['id' => $data['id']])->row_array();
  }

 
    public function getDataRaportByid($id){

      return CekData::getData('nilai', ['id_pendaftar' => $id]);
    }

    public function getDataJalur($id){
      return $this->db->get_where('jalur_ppdb',['id'=>$id])->row_array();
    }

   
      public function penyelengara()
    {
        echo json_encode($this->akun->dataPeyelengara()->result_array());
    }

    
  

  private function _getDataPendaftarKejuaraan($id)
  {
    $this->db->select('register.*,register.nisn as nisnPendaftar,register.id AS `id_pendaftar`,jalur_ppdb.ppdb,prestasi.*,prestasi.id AS `id_prestasi`, nilai.id AS `id_nilai`, kat_satuan.*,kategori_penyelengara.*,nilai.*,kejuaraansiswa.namaKejuaraan,skor,skorKompetensi,total_skor_prestasi');
    $this->db->from('register');
    $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
    $this->db->join('kejuaraansiswa', 'kejuaraansiswa.id_pendaftar = register.id');
    $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
    $this->db->join('prestasi', 'prestasi.id = kejuaraansiswa.id_kejuaraan');
    $this->db->join('kat_satuan', 'kat_satuan.id_satuan = prestasi.id_satuan');
    $this->db->join('kategori_penyelengara', 'kategori_penyelengara.id_penyelengara = kat_satuan.id_penyelengara');
    $this->db->where('register.id=' . $id);
    $hasil = $this->db->get()->row_array();
    return $hasil;
  }

  public function _getDataPendaftarAfirmasi($id)
  {
    $this->db->select('register.id AS `id_pendaftar`,register.*,register.nisn as nisnPendaftar,nilai.*,nilai.id AS `id_nilai`, jalur_ppdb.ppdb,quota,afirmasi.*,jenis_afirmasi.*');
    $this->db->from('register');
    $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
    $this->db->join('afirmasi', 'afirmasi.id_pendaftar = register.id');
    $this->db->join('jenis_afirmasi', 'jenis_afirmasi.id = afirmasi.afirmasi');
    $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
    $this->db->where('register.id=' . $id);
    $hasil = $this->db->get()->row_array();
    return $hasil;
  }

  public function _getDataPendaftarBiasa($id)
  {
    $this->db->select('register.id AS `id_pendaftar`,register.*,register.nisn as nisnPendaftar,nilai.*, nilai.id AS `id_nilai`, jalur_ppdb.ppdb');
    $this->db->from('register');
    $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
    $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
    $this->db->where('register.id=' . $id);
    $hasil = $this->db->get()->row_array();
    return $hasil;
  }

  public function getDataPrestasiByIdPendaftar($id)
  {
    $query = "SELECT `prestasi`.*, `kat_satuan`.`satuan`, `kategori_penyelengara`.`peyelengara`
    FROM  `prestasi` JOIN `kat_satuan`
    ON    `prestasi`.`id_satuan`=`kat_satuan`.`id_satuan`
    JOIN `kategori_penyelengara`
    ON    `kat_satuan`.`id_penyelengara`=`kategori_penyelengara`.`id_penyelengara`

    WHERE `prestasi`.`id`= $id";

    return $this->db->query($query)->row_array();
  }


  // hitung jarak

  function hitungJarak($lokasi1_lat, $lokasi1_long, $lokasi2_lat, $lokasi2_long, $unit = 'km', $desimal = 2)
  {
    // Menghitung jarak dalam derajat
    $derajat = rad2deg(acos((sin(deg2rad($lokasi1_lat)) * sin(deg2rad($lokasi2_lat))) + (cos(deg2rad($lokasi1_lat)) * cos(deg2rad($lokasi2_lat)) * cos(deg2rad($lokasi1_long - $lokasi2_long)))));

    // Mengkonversi derajat kedalam unit yang dipilih (kilometer, mil atau mil laut)
    switch ($unit) {
      case 'km':
        $jarak = $derajat * 111.13384; // 1 derajat = 111.13384 km, berdasarkan diameter rata-rata bumi (12,735 km)
        break;
      case 'mi':
        $jarak = $derajat * 69.05482; // 1 derajat = 69.05482 miles(mil), berdasarkan diameter rata-rata bumi (7,913.1 miles)
        break;
      case 'nmi':
        $jarak =  $derajat * 59.97662; // 1 derajat = 59.97662 nautic miles(mil laut), berdasarkan diameter rata-rata bumi (6,876.3 nautical miles)
    }
    return round($jarak, $desimal);
  }

  // data pnyelengara
  public function dataPeyelengara()
  {
    return $this->db->get('kategori_penyelengara');
  }

  // ambil data jenis afirmasi
  public function jenisAfirmasi()
  {
    return $this->db->get('jenis_afirmasi');
  }
  // data kategori kejuaraan berdasarkan penyelengara
  public function KategoriKejuaraan($id)
  {
    return $this->db->get_where('kat_satuan', ['id_penyelengara' => $id]);
  }

  // ambil data afirmasi denga id
  public function getAfirmasiById($id)
  {
    $pesan = [''];
    $nama = $this->db->get_where('jenis_afirmasi', ['id' => $id]);
    if ($nama) {
      return $nama;
    } else {
      $pesan = ['nama' => 'kosong'];
    }
  }

  // data tingkat kejuaraan berdasarkan kategori
  public function tingkatKejuaraan($id)
  {
    return $this->db->get_where('prestasi', ['id_satuan' => $id]);
  }

  public function cekEmailOrNISN($kolom,$value){
    $query = "SELECT nisn,email statusDaftar FROM register where ( statusDaftar=? || statusDaftar=?) AND $kolom=?";
    return $this->db->query($query, [3,4,$value])->num_rows();
}



  public function jarak($longSekolah, $longPendaftar)
  {
    $kodinatSekolah = explode(",", $longSekolah);
    $kodinatDaftar = explode(",", $longPendaftar);
    $latSekolah = $kodinatSekolah[0];
    $longSekolah = $kodinatSekolah[1];
    $latDaftar = $kodinatDaftar[0];
    $longDaftar = $kodinatDaftar[1];
    return  $this->_hitungJarak($latSekolah, $longSekolah, $latDaftar, $longDaftar);
  }


  // private function
  function _hitungJarak($latSekolah,  $longSekolah,  $latDaftar,  $longDaftar, $unit = 'kilometers')
  {
    $theta =  $longSekolah -  $longDaftar;
    $distance = (sin(deg2rad($latSekolah)) * sin(deg2rad($latDaftar))) + (cos(deg2rad($latSekolah)) * cos(deg2rad($latDaftar)) * cos(deg2rad($theta)));
    $distance = acos($distance);
    $distance = rad2deg($distance);
    $distance = $distance * 60 * 1.1515;
    switch ($unit) {
      case 'miles':
        break;
      case 'kilometers':
        $distance = $distance * 1.609344;
    }
    return (round($distance, 2));
  }


  // hitung usia
  private function hitungUsia($tanggalLahir)
  {

    $rubahTanggal = date('y-m-d', strtotime($tanggalLahir));
    // tanggal lahir
    $tanggal = new DateTime($rubahTanggal);

    // tanggal hari ini
    $today = new DateTime('today');

    // tahun
    $y = $today->diff($tanggal)->y;

    // bulan
    $m = $today->diff($tanggal)->m;

    // hari
    $d = $today->diff($tanggal)->d;

    if ($m < 10) {
      $m = '0' . $m;
    }
    return ($y . "." . $m);
  }





  public function simpanVerifikasi($post)
  {
    // error_reporting(E_ALL);
    // var_dump($post);
    // die;

    $data['sekolah'] = $this->db->get('kop_surat')->row_array();
    $latlogSekolah =  $data['sekolah']['longitude'];
    $latlogDaftar =  $post['longitude'];

    $admin = $this->db->get_where('user', ['email' => $this->session->userdata('email')])->row_array();
    $jmlRataRatapai = round(($post['paiK4S1'] + $post['paiK4S2'] + $post['paiK5S1'] + $post['paiK5S2'] + $post['paiK6S1']) / 5, 2);
    $jmlRataRatapkn = round(($post['pknK4S1'] + $post['pknK4S2'] + $post['pknK5S1'] + $post['pknK5S2'] + $post['pknK6S1']) / 5, 2);
    $jmlRataRataIndo = round(($post['indoK4S1'] + $post['indoK4S2'] + $post['indoK5S1'] + $post['indoK5S2'] + $post['indoK6S1']) / 5, 2);
    $jmlRataRataMtk = round(($post['mtkK4S1'] + $post['mtkK4S2'] + $post['mtkK5S1'] + $post['mtkK5S2'] + $post['mtkK6S1']) / 5, 2);
    $jmlRataRataIpa = round(($post['ipaK4S1'] + $post['ipaK4S2'] + $post['ipaK5S1'] + $post['ipaK5S2'] + $post['ipaK6S1']) / 5, 2);
    $jmlRataRataips = round(($post['ipsK4S1'] + $post['ipsK4S2'] + $post['ipsK5S1'] + $post['ipsK5S2'] + $post['ipsK6S1']) / 5, 2);
    $jmlRataRatasbdp = round(($post['sbdpK4S1'] + $post['sbdpK4S2'] + $post['sbdpK5S1'] + $post['sbdpK5S2'] + $post['sbdpK6S1']) / 5, 2);
    $jmlRataRatapjok = round(($post['pjokK4S1'] + $post['pjokK4S2'] + $post['pjokK5S1'] + $post['pjokK5S2'] + $post['pjokK6S1']) / 5, 2);
    $jmlRataRatasunda = round(($post['sundaK4S1'] + $post['sundaK4S2'] + $post['sundaK5S1'] + $post['sundaK5S2'] + $post['sundaK6S1']) / 5, 2);
    $totalRataRataNilai = $jmlRataRatapai + $jmlRataRatapkn + $jmlRataRataIndo + $jmlRataRataIpa + $jmlRataRataMtk + $jmlRataRataips + $jmlRataRatasbdp + $jmlRataRatapjok + $jmlRataRatasunda;

    $dataBio = [
      'id' => $post['id'],
      'koreg' => $post['koregSen'],
      'nama' => $post['nama'],
      'sekolahAsal' => $post['namaSekolah'],
      'tempatLahir' => $post['tempatLahir'],
      'tglLahir' => $post['tanggalLahir'],
      'npsn' => $post['npsn'],
      'nisn' => $post['nisn'],
      'longitude' => $post['longitude'],
      'id_jalur' => $post['jalurPPDB'],
      'jarak' =>  $this->jarak($latlogSekolah, $latlogDaftar) * 1000,
      'usia' => $this->hitungUsia($post['tanggalLahir']),
      'statusDaftar' => $post['verifikasi'],
      'prosesBy' => $admin['name'],
    ];

    $dataNilai = [
      'id_pendaftar' => $post['id'],
      'koreg' => $post['koregSen'],
      // pai
      'paiK4S1' => $post['paiK4S1'],
      'paiK4S2' => $post['paiK4S2'],
      'paiK5S1' => $post['paiK5S1'],
      'paiK5S2' => $post['paiK5S2'],
      'paiK6S1' => $post['paiK6S1'],

      // pkn
      'pknK4S1' => $post['pknK4S1'],
      'pknK4S2' => $post['pknK4S2'],
      'pknK5S1' => $post['pknK5S1'],
      'pknK5S2' => $post['pknK5S2'],
      'pknK6S1' => $post['pknK6S1'],

      // indo
      'indoK4S1' => $post['indoK4S1'],
      'indoK4S2' => $post['indoK4S2'],
      'indoK5S1' => $post['indoK5S1'],
      'indoK5S2' => $post['indoK5S2'],
      'indoK6S1' => $post['indoK6S1'],

      // mtk
      'mtkK4S1' => $post['mtkK4S1'],
      'mtkK4S2' => $post['mtkK4S2'],
      'mtkK5S1' => $post['mtkK5S1'],
      'mtkK5S2' => $post['mtkK5S2'],
      'mtkK6S1' => $post['mtkK6S1'],

      // ipa
      'ipaK4S1' => $post['ipaK4S1'],
      'ipaK4S2' => $post['ipaK4S2'],
      'ipaK5S1' => $post['ipaK5S1'],
      'ipaK5S2' => $post['ipaK5S2'],
      'ipaK6S1' => $post['ipaK6S1'],

      // ips
      'ipsK4S1' => $post['ipsK4S1'],
      'ipsK4S2' => $post['ipsK4S2'],
      'ipsK5S1' => $post['ipsK5S1'],
      'ipsK5S2' => $post['ipsK5S2'],
      'ipsK6S1' => $post['ipsK6S1'],
      // sbdp
      'sbdpK4S1' => $post['sbdpK4S1'],
      'sbdpK4S2' => $post['sbdpK4S2'],
      'sbdpK5S1' => $post['sbdpK5S1'],
      'sbdpK5S2' => $post['sbdpK5S2'],
      'sbdpK6S1' => $post['sbdpK6S1'],

      // pjok
      'pjokK4S1' => $post['pjokK4S1'],
      'pjokK4S2' => $post['pjokK4S2'],
      'pjokK5S1' => $post['pjokK5S1'],
      'pjokK5S2' => $post['pjokK5S2'],
      'pjokK6S1' => $post['pjokK6S1'],

      // sunda
      'sundaK4S1' => $post['sundaK4S1'],
      'sundaK4S2' => $post['sundaK4S2'],
      'sundaK5S1' => $post['sundaK5S1'],
      'sundaK5S2' => $post['sundaK5S2'],
      'sundaK6S1' => $post['sundaK6S1'],
      'total' => $totalRataRataNilai,
    ];

    if ($post['jalurPPDB'] == '4') {
      $dataKejuaraan = [
        'id_pendaftar' => $post['id'],
        'koreg' => $post['koregSen'],
        'id_kejuaraan' => $post['tingkatPrestasi'],
        'namaKejuaraan' => $post['namaKejuaraan'],
        'skorKompetensi' => $post['skorKompetensi']
      ];
      $this->_simpanBiodata($dataBio);
      $this->_simpanDataNilai($dataNilai);
      $this->_simpanDataKejuaraan($dataKejuaraan);
      $hasil['affect'] = $this->db->affected_rows();
      $hasil['kode'] = $post['id'];
      return $hasil;
      exit;
    } else if ($post['jalurPPDB'] == '2') {
      $dataAfirmasi = [
        'id_pendaftar' => $post['id'],
        'koreg' => $post['koregSen'],
        'afirmasi' => $post['namaAfrimasi'],
        'nama_afirmasi' => isset($post['namaPemilik']) ? $post['namaPemilik'] : 'None',
        'no_afirmasi' => isset($post['noKartu']) ? $post['noKartu'] : '0',
        'dtks' => isset($post['dtks']) ? $post['dtks'] : '0',
      ];
      $this->_simpanDataAfirmasi($dataAfirmasi);
      $this->_simpanBiodata($dataBio);
      $this->_simpanDataNilai($dataNilai);
      $hasil['affect'] = $this->db->affected_rows();
      $hasil['kode'] = $post['id'];
      return $hasil;
      exit;
    } else {
      $this->_simpanBiodata($dataBio);
      $this->_simpanDataNilai($dataNilai);
      $hasil['affect'] = $this->db->affected_rows();
      $hasil['kode'] = $post['id'];
      return $hasil;
    }
  }


  private function _simpanDataKejuaraan($data)
  {
    $cekKejuaran = $this->db->get_where('kejuaraansiswa', ['id_pendaftar	' => $data['id_pendaftar'], 'koreg' => $data['koreg']])->row_array();
    $skor = $this->db->get_where('prestasi', ['id' => $data['id_kejuaraan']])->row_array();
    $data['skor'] = $skor['score'];
    $data['skor'] =  $data['skor'] + (int)$data['skorKompetensi'];
    if ($cekKejuaran) {
      $this->db->set($data);
      $this->db->where('id_pendaftar', $data['id_pendaftar']);
      $this->db->where('koreg', $data['koreg']);
      $this->db->update('kejuaraansiswa');
    } else {
      $this->db->insert('kejuaraansiswa', $data);
    }
    // return $this->db->affected_rows();
  }
  private function _simpanBiodata($data)
  {
    $this->db->set($data);
    $this->db->where('id', $data['id']);
    // $this->db->where('koreg', $data['koreg']);
    $this->db->update('register');
    // return $this->db->affected_rows();
  }
  private function _simpanDataNilai($data)
  {
      $this->db->set($data);
    $this->db->where('id_pendaftar', $data['id_pendaftar']);
    $this->db->update('nilai');
  }
  private function _simpanDataAfirmasi($data)
  {
    $afirmasi = $this->db->get_where('afirmasi', ['id_pendaftar	' => $data['id_pendaftar'], 'koreg' => $data['koreg']])->row_array();
    if ($afirmasi) {
      $this->db->set($data);
      $this->db->where('id_pendaftar', $data['id_pendaftar']);
      $this->db->where('koreg', $data['koreg']);
      $this->db->update('afirmasi');
    } else {
      $this->db->insert('afirmasi', $data);
    }
    return $this->db->affected_rows();
  }


  // edit munus plus nilai praktek
  public function editNilaiPraktekByTombol($form)
  {
    $id = $form['id'];
    $koreg = $form['koreg'];
    $nilai = $form['nilai'];
    $query = "SELECT nilaiPraktekPrestasi,nilaiPrestasi FROM pendaftar WHERE id=$id AND koreg = $koreg";
    $dataNilai = $this->db->query($query)->row_array();

    $hasilNilai = ($dataNilai['nilaiPrestasi'] - $nilai);
    $hasilNilaiPraktek = ($dataNilai['nilaiPraktekPrestasi'] - $nilai);
    $data = [
      'nilaiPraktekPrestasi' => $hasilNilaiPraktek,
      'nilaiPrestasi' => $hasilNilai,
    ];

    $this->db->set($data);
    $this->db->where('id', $id);
    $this->db->where('koreg', $koreg);
    $this->db->update('pendaftar');
    if ($this->db->affected_rows() > 0) {
      return $this->db->get_where('pendaftar', ['id' => $id, 'koreg' => $koreg])->row_array();
    }
  }

  // tambah
  public function proseseditNilaiPraktekByTombolPlus($form)
  {
    $id = $form['id'];
    $koreg = $form['koreg'];
    $nilai = $form['nilai'];
    $query = "SELECT nilaiPraktekPrestasi,nilaiPrestasi FROM pendaftar WHERE id=$id AND koreg = $koreg";
    $dataNilai = $this->db->query($query)->row_array();

    $hasilNilai = ($dataNilai['nilaiPrestasi'] + $nilai);
    $hasilNilaiPraktek = ($dataNilai['nilaiPraktekPrestasi'] + $nilai);
    $data = [
      'nilaiPraktekPrestasi' => $hasilNilaiPraktek,
      'nilaiPrestasi' => $hasilNilai,
    ];

    $this->db->set($data);
    $this->db->where('id', $id);
    $this->db->where('koreg', $koreg);
    $this->db->update('pendaftar');
    if ($this->db->affected_rows() > 0) {
      return $this->db->get_where('pendaftar', ['id' => $id, 'koreg' => $koreg])->row_array();
    }
  }

  //   GET DATA UNTUK PRINT BUKI PENDAFTARAN
  public function getdataprintByjalur($koreg)
  {
    $user = $this->db->get_where('register', ['koreg' => $koreg])->row_array();
    $jalur = $user['id_jalur'];
    $id = $user['id'];
    if ($jalur == 4) {
      $this->db->select('register.*,register.nisn as nisnSiswa,jalur_ppdb.*,prestasi.*,kat_satuan.satuan,kategori_penyelengara.peyelengara,nilai.*,kejuaraansiswa.*');
      $this->db->from('register');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
      $this->db->join('kejuaraansiswa', 'kejuaraansiswa.id_pendaftar = register.id');
      $this->db->join('prestasi', 'prestasi.id = kejuaraansiswa.id_kejuaraan');
      $this->db->join('kat_satuan', 'kat_satuan.id_satuan = prestasi.id_satuan');
      $this->db->join('kategori_penyelengara', 'kategori_penyelengara.id_penyelengara = kat_satuan.id_penyelengara');
      $this->db->where('register.id=' . $id);
      $hasil = $this->db->get()->row_array();
      return $hasil;
    } else if (
      $jalur == 2
    ) {
      $this->db->select('register.*,register.nisn as nisnSiswa,ppdb,afirmasi.*,nama_jenis_afirmasi,nilai.*');
      $this->db->from('register');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
      $this->db->join('afirmasi', 'afirmasi.id_pendaftar = register.id');
      $this->db->join('jenis_afirmasi', 'jenis_afirmasi.id = afirmasi.afirmasi');
      $this->db->where('register.id=' . $id);
      $hasil = $this->db->get()->row_array();
      return $hasil;
    } else if (
      $jalur == 6
    ) {
      $this->db->select('register.*,register.nisn as nisnSiswa,jalur_ppdb.ppdb,nilai.*');
      $this->db->from('register');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
      // $this->db->join('afirmasi', 'afirmasi.id_pendaftar = register.id');
      // $this->db->join('jenis_afirmasi', 'jenis_afirmasi.id = afirmasi.afirmasi');
      $this->db->where('register.id=' . $id);
      $hasil = $this->db->get()->row_array();
      return $hasil;
    } else {
      $this->db->select('register.*,register.nisn as nisnSiswa,jalur_ppdb.ppdb,nilai.*');
      $this->db->from('register');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
      $this->db->where('register.id=' . $id);
      $hasil = $this->db->get()->row_array();
      return $hasil;
    }
  }


  // get data alamat
  public function getAlamat($koreg)
  {
    $user = $this->db->get_where('register', ['koreg' => $koreg])->row_array();
    $id = $user['id'];

    $this->db->select(
      'register.*,
           districts.name AS `kecamatan`,
           villages.name AS `desa`,
           regencies.name AS `kota`,
           provinces.name AS `provinsi`',
      false
    );
    $this->db->from('register');
    $this->db->join('villages', 'villages.id = register.desa');
    $this->db->join('districts', 'districts.id = register.kecamatan');
    $this->db->join('regencies', 'regencies.id = register.kota');
    $this->db->join('provinces', 'provinces.id = register.provinsi');
    $this->db->where('register.id=' . $id);
    $hasil = $this->db->get()->row_array();
    return $hasil;
    // return $data;
  }
}
