<?php
defined('BASEPATH') or exit('No direct script access allowed');
class Ppdb_model extends CI_Model
{
  public function getDataSyarat()
  {
    $query = "SELECT `persyaratan`.*,`jalur_ppdb`.`ppdb` 
            FROM `persyaratan` JOIN `jalur_ppdb`
            ON `persyaratan`.`id_jalur`=`jalur_ppdb`.`id`
            ORDER BY `persyaratan`.`id_jalur` ASC
    ";
    return $this->db->query($query)->result_array();
  }

  // simpan data jenis afrimasi
  public function simpanDataAfirmasi($data){
    $nama = $data['namaAfirmasi'];
    $simpan = [
      'nama_jenis_afirmasi' => $nama
    ];
    $this->db->insert('jenis_afirmasi', $simpan);
    return $this->db->affected_rows();
  }

  public function editDataTableAfirmasi($data){
    $edit = [
      'nama_jenis_afirmasi' => $data['namaAfirmasi']
    ];


    $this->db->set($edit);
    $this->db->where(['id' => $data['idAfirmasi']]);
    $this->db->update('jenis_afirmasi');
    return $this->db->affected_rows();
  }

  public function hapusAfirmasi($id){
    $this->db->delete('jenis_afirmasi', ['id' => $id]);
    return $this->db->affected_rows();
  }

  // data untuk table afirmasi
  public function getDataTabelJenisAfirmasi(){
    return $this->db->get('jenis_afirmasi')->result_array();
  }

  public function simpanDataVerifikasi($dataAfirmasi = null, $dataPrestasi = null, $dataNilai=null, $jalur=null,$id_pendaftar=null)
  {

    // update data pendaftar
    $user = $this->db->get_where('register',['id' => $id_pendaftar])->row_array();

    if (!$user['koreg'] == null) {
     
     
        $this->load->helper('hitungnilai');
        // try catch
        try {
          $this->db->trans_begin();
          // cek jalur yang di pilih\
          if ($jalur['input_nilai'] == 1) {
            unset($dataNilai['jalurPpdb']);
            $totalPAI = MyHitungNilai::sumNilai($dataNilai['paiK4S1'], $dataNilai['paiK4S2'], $dataNilai['paiK5S1'], $dataNilai['paiK5S2'], $dataNilai['paiK6S1']);
            $totalPKN = MyHitungNilai::sumNilai($dataNilai['pknK4S1'], $dataNilai['pknK4S2'], $dataNilai['pknK5S1'], $dataNilai['pknK5S2'], $dataNilai['pknK6S1']);
            $totalIndo = MyHitungNilai::sumNilai($dataNilai['indoK4S1'], $dataNilai['indoK4S2'], $dataNilai['indoK5S1'], $dataNilai['indoK5S2'], $dataNilai['indoK6S1']);
            $totalMtk = MyHitungNilai::sumNilai($dataNilai['mtkK4S1'], $dataNilai['mtkK4S2'], $dataNilai['mtkK5S1'], $dataNilai['mtkK5S2'], $dataNilai['mtkK6S1']);
            $totalIpa = MyHitungNilai::sumNilai($dataNilai['ipaK4S1'], $dataNilai['ipaK4S2'], $dataNilai['ipaK5S1'], $dataNilai['ipaK5S2'], $dataNilai['ipaK6S1']);
            $totalIps = MyHitungNilai::sumNilai($dataNilai['ipsK4S1'], $dataNilai['ipsK4S2'], $dataNilai['ipsK5S1'], $dataNilai['ipsK5S2'], $dataNilai['ipsK6S1']);
            $totalSbdp = MyHitungNilai::sumNilai($dataNilai['sbdpK4S1'], $dataNilai['sbdpK4S2'], $dataNilai['sbdpK5S1'], $dataNilai['sbdpK5S2'], $dataNilai['sbdpK6S1']);
            $totalPjok = MyHitungNilai::sumNilai($dataNilai['pjokK4S1'], $dataNilai['pjokK4S2'], $dataNilai['pjokK5S1'], $dataNilai['pjokK5S2'], $dataNilai['pjokK6S1']);
            $totalSunda = MyHitungNilai::sumNilai($dataNilai['sundaK4S1'], $dataNilai['sundaK4S2'], $dataNilai['sundaK5S1'], $dataNilai['sundaK5S2'], $dataNilai['sundaK6S1']);
            $dataNilai['total'] = MyHitungNilai::sumRataRata($totalPAI, $totalPKN, $totalIndo, $totalMtk, $totalIpa, $totalIps, $totalSbdp, $totalPjok, $totalSunda);
            $dataNilai['totalSkor'] =( MyHitungNilai::sumRataRata($totalPAI, $totalPKN, $totalIndo, $totalMtk, $totalIpa, $totalIps, $totalSbdp, $totalPjok, $totalSunda));
      
              // ambil dulu data rangkinng nnya
            unset($dataNilai['totalNilai']);
            $rangkig = $this->db->get_where('rangking_skor', ['id_rangking' => $dataNilai['rangking']])->row_array();
              unset($dataNilai['rangking']);
            if(!$rangkig==null || !$rangkig==""){
            
              $dataNilai['totalSkor'] += $rangkig['skorRanking'];
              $dataNilai['skorRank'] = $rangkig['skorRanking'];
              $dataNilai['rank'] = $rangkig['rangking'];
              $dataNilai['isRank'] = $rangkig['id_rangking'];
            }else{
              $dataNilai['isRank'] = 0;
              $dataNilai['rank'] = "none";
              $dataNilai['skorRank'] = 0;
            $dataNilai['totalSkor'] = $dataNilai['total'];
            }

            // cek ke table nilai udah ada tau belum ? jika ada upade jika tidak ada insert
          $cekTableNilai = $this->db->get_where('nilai', ['id_pendaftar' => $user['id']])->num_rows();
          if($cekTableNilai >0){
            // upate datanya
            unset($dataNilai['rangking']);
            unset($dataNilai['id_pendaftar']);
            $this->db->set($dataNilai);
            $this->db->where(['id_pendaftar' => $user['id']]);
            $this->db->update('nilai');

          }else{
            $dataNilai['id_pendaftar'] = $user['id'];
            $dataNilai['koreg'] = $user['koreg'];
            $dataNilai['nisn'] = $user['nisn'];
            $this->db->insert('nilai',$dataNilai);
          }

            
          }

          // cek afirmasi 
          if($jalur['input_afirmasi'] == 1){
         
            // amibil data afirmasi
            $cekDanAmbil = $this->db->get_where('jenis_afirmasi',['id'=>$dataAfirmasi['afirmasi']])->row_array();

          $dataInputAfirmasi = [
            'afirmasi'=>$cekDanAmbil['id'],
            'nama_afirmasi'=>$dataAfirmasi['nama_afirmasi'],
            'no_afirmasi'=>$dataAfirmasi['no_afirmasi'],
            'dtks'=>$dataAfirmasi['dtks'],
          ];

          $cekTableAfirmasi = $this->db->get_where('afirmasi',['id_pendaftar'=>$user['id']])->num_rows();
          if($cekTableAfirmasi>0){
            $this->db->set($dataInputAfirmasi);
            $this->db->where(['id_pendaftar' => $user['id']]);
            $this->db->update('afirmasi');
          }else{
            $dataInputAfirmasi['id_pendaftar'] = $user['id'];
            $dataInputAfirmasi['koreg'] = $user['koreg'];
            $this->db->insert('afirmasi', $dataInputAfirmasi);
          }

        }

     

          // cek kejuaraan bukan

          if ($jalur['inputPrestasi'] == 1) {
            // ambil skore Preatasi kejuaraan
            $skoreKejuaraan = $this->db->get_where('prestasi', ['id' => $dataPrestasi['prestasi']])->row_array();
            $datainputPrestasi = [
              'id_kejuaraan' => $dataPrestasi['prestasi'],
              'namaKejuaraan' => $dataPrestasi['namaKejuaraan'],
              'skorKompetensi' => $dataPrestasi['skorKompentensi'],
              'skor' => $skoreKejuaraan['score'],
              'total_skor_kejuaraan' => (floatval($skoreKejuaraan['score']) + floatval($dataPrestasi['skorKompentensi']))
            ];

            // cek ketable prestasi 
              $cekTableKejuaran= $this->db->get_where('kejuaraansiswa',['id_pendaftar'=>$user['id']])->num_rows();
              if($cekTableKejuaran>0){
                $this->db->set($datainputPrestasi);
                $this->db->where(['id_pendaftar' => $user['id']]);
                $this->db->update('kejuaraansiswa');
              }else{
                    $datainputPrestasi['koreg'] = $user['koreg'];
                    $datainputPrestasi['id_pendaftar'] = $user['id'];
                   $this->db->insert('kejuaraansiswa', $datainputPrestasi);
              }
          }

          if ($this->db->trans_status() === FALSE)
          {
                  $this->db->trans_rollback();
                  return false;
          }
          else
          {
                  $this->db->trans_commit();
                  $update['statusDaftar'] = 3;
                  $update['id_jalur'] = $jalur['id'];
                  $this->db->set($update);
                  $this->db->where(['id' => $user['id']]);
                  $this->db->update('register');

          $message = [
              'pesan'=>true,
              'koreg'=>$user['koreg']
          ];
             return  $message;

          }



        }catch(Exception $error){
          echo "terjadi error: $error";
        }

        return  $message = [
          'pesan'=>true,
          'koreg'=>$user['koreg']
      ];
      
    }

  }

  public function getSkorrangking(){
     $query = "SELECT * from rangking_skor
            ORDER BY `rangking_skor`.`rangking` ASC
    ";
    return $this->db->query($query)->result_array();
  }

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

    $this->db->where(['id'=>$user['id'],'koreg'=>$user['koreg']]);
    unset($data['nisn']);
    unset($data['id']);
    unset($data['koreg']);
    $data['update_time'] = CekData::dateNow();
    $this->db->update('register', $data);
    return $this->db->affected_rows();
  }

  public function SimpanAlamatVerifikasi($data){
    $this->load->model('akun_m','akun');
    $sekolah =$this->db->get_where('kop_surat', ['id' => 1])->row_array();
    $jarak = $this->akun->jarak($sekolah['longitude'], $data['longitude']) * 1000;
    $jarak = round($jarak, 2);
    $user=$this->db->get_where('register',['id'=>$data['id_pendaftar'],'koreg'=>$data['koreg']])->row_array();
    if(!isset($user) || $user !=""){

      $dataUpdate = [
        'longitude'=>$data['longitude'],
        'kota'=>$data['kota'],
        'provinsi'=>$data['provinsi'],
        'kecamatan'=>$data['kecamatan'],
        'desa'=>$data['desa'],
        'kk'=>$data['kk'],
        'kodePos'=>$data['kodePos'],
        'rt'=>$data['rt'],
        'rw'=>$data['rw'],
        'alamat'=>$data['alamat'],
        'jarak'=>$jarak
      ];
      $dataUpdate['update_time'] = CekData::dateNow();
      $this->db->where(['id'=>$user['id']]);
      $this->db->update('register', $dataUpdate);
      return $this->db->affected_rows();

    }else{
      return false;
    }

   
  
    
  }
  
  public function getDataVerifikasi($data){
    $dataNilai=array();
    $dataAfirmasi = [];
    $dataKejuaraan = [];
    $dataSekolah = [];
    $jalurPpdb = [];
    $dataSyarat = [];
    $dataWali = [];
    $this->db->select(
      'register.*,
           districts.name AS `kecamatan`,
           villages.name AS `desa`,
           regencies.name AS `kota`,
           provinces.name AS `provinsi`,
           provinces.id as `id_provinsi`,
           regencies.id AS `id_kota`,
           villages.id AS `id_desa`,
           districts.id AS `id_kecamatan`,
           register.id as `id_pendaftar`',
      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->or_where(['register.id'=>$data['id'],'register.koreg'=>$data['koreg']]);
    $user = $this->db->get()->row_array();
    $jalurPpdb = $this->db->get_where('jalur_ppdb',['id'=>$user['id_jalur']])->row_array();

    if($jalurPpdb['input_nilai']==1){
      $dataNilai = CekData::getData('nilai', ['koreg' => $user['koreg'], 'id_pendaftar' => $user['id']]);
    }

    // cek lagi kejuaran aktif gak
    if ($jalurPpdb['inputPrestasi'] == 1) {
      $this->db->select('prestasi.*,kat_satuan.*,kategori_penyelengara.*,kejuaraansiswa.*');
      $this->db->from('kejuaraansiswa');
      $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(['kejuaraansiswa.id_pendaftar'=>$user['id']]);
      $dataKejuaraan  = $this->db->get()->row_array();
    }


    $dataWali = CekData::getData('wali', ['idRegister' => $user['id']]);
    

    // cek afirmasinya aktif gak

    if($jalurPpdb['input_afirmasi'] == 1){
      $this->db->from('afirmasi');
      $this->db->join('jenis_afirmasi', 'afirmasi.afirmasi = jenis_afirmasi.id');
      $this->db->or_where(['koreg'=>$user['koreg'],'id_pendaftar'=>$user['id']]);
      $dataAfirmasi = $this->db->get()->row_array();
    }

    $dataSekolah = CekData::getData('kop_surat',['id'=>1]);
    $dataSyarat = $this->db->get_where('persyaratan', ['id_jalur' => $jalurPpdb['id']])->result_array();

    $hasil = [
      'user'=>$user,
      'dataKejuaraan'=>$dataKejuaraan,
      'dataAfirmasi'=>$dataAfirmasi,
      'dataNilai'=>$dataNilai,
      'dataSekolah'=>$dataSekolah,
      'dataJalur'=>$jalurPpdb,
      'dataSyarat'=>$dataSyarat,
      'dataWali'=>$dataWali
    ];
    return $hasil;
    }
  

  public function getDataPendaftar()
  {

    $this->db->select('register.*,jalur_ppdb.ppdb,nilai.total,kejuaraansiswa.skor');
    $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->order_by('register.id_jalur ASC');
    $hasil = $this->db->get()->result_array();
    return $hasil;
  }  

  public function rangkingPendaftar()
  {
    $query = "SELECT namaSiswa,totalNilai,
    ( SELECT find_in_set( totalNilai,
    ( SELECT group_concat(distinct totalNilai  order by totalNilai DESC )from pendaftar))) as rangking
    from pendaftar ";
    return $this->db->query($query)->result_array();
  }

  public function  getJumlahDataJalur()
  {
    $query = "SELECT  COUNT(`pendaftar`.`id_jalur`) AS jlmPendaftar ,`jalur_ppdb`.*,`pendaftar`.* 
    FROM `jalur_ppdb` JOIN `pendaftar`
    ON `jalur_ppdb`.`id`=`pendaftar`.`id_jalur`
   
    GROUP BY `pendaftar`.`id_jalur`
    ORDER BY `pendaftar`.`id_jalur` ASC";
    return $this->db->query($query)->result_array();
  }

  public function getDataPpdb()
  {
    return $this->db->get('jalur_ppdb')->result_array();
  }


  public function simpanSyarat($data)
  {
    $syarat = ['id_jalur' => $data['jalur'], 'persyaratan' => $data['syarat']];
    $this->db->insert('persyaratan', $syarat);
    return $this->db->affected_rows();
  }

  public function getDataSyaratById($id)
  {
    $query = "SELECT `persyaratan`.*,`jalur_ppdb`.`ppdb`
    FROM `persyaratan` JOIN `jalur_ppdb`
    ON `persyaratan`.`id_jalur`=`jalur_ppdb`.`id`
    WHERE `persyaratan`.`id`=$id
    ORDER BY `persyaratan`.`id_jalur` ASC
";
    return $this->db->query($query)->row_array();
  }

  public function prosesEdit($data)
  {

    $edit = [
      'id_jalur' => $data['jalur'],
      'persyaratan' => $data['syarat']
    ];
    $this->db->set($edit);
    $this->db->where('id', $data['idSyarat']);
    $this->db->update('persyaratan');
    return $this->db->affected_rows();
  }

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

  public function prosesEditJalur($form)
  {
    $edit = [
      'ppdb' => $form['jalur'],
      'quota' => $form['quota'],
      'input_nilai' => $form['input_nilai'],
      'input_afirmasi' => $form['input_afirmasi'],
      'inputPrestasi' => $form['inputPrestasi'],
      'statusAktif' => $form['statusAktif'],
    ];
    $this->db->set($edit);
    $this->db->where('id', $form['idJalur']);
    $this->db->update('jalur_ppdb');
    return $this->db->affected_rows();
  }

  public function editQuotaOnly($form){
    $edit = [
      'ppdb' => $form['jalur'],
      'quota' => $form['quota'],
    ];
    $this->db->set($edit);
    $this->db->where('id', $form['idJalur']);
    $this->db->update('jalur_ppdb');
    return $this->db->affected_rows();

  }

  // proses edit skor rangking
  public function prosesSimpanEditRangking($form){
    $edit = [
      'rangking' => $form['namaRangking'],
      'skorRanking' => $form['skorRangking'],
    ];
    $this->db->set($edit);
    $this->db->where('id_rangking', $form['idRangking']);
    $this->db->update('rangking_skor');
    return $this->db->affected_rows();
  }

  public function prosesHapusRangking($id){
    $this->db->delete('rangking_skor', ['id_rangking' => $id]);
    return $this->db->affected_rows();
  }

  public function prosesHapusJalur($id)
  {
    $this->db->delete('jalur_ppdb', ['id' => $id]);
    return $this->db->affected_rows();
  }

  public function simpanJalurPPDB($data)
  {

    $simpan = [
      'ppdb' => $data['jalur'],
      'quota' => $data['quota'],
      'input_nilai' => $data['input_nilai'],
      'inputPrestasi' => $data['inputPrestasi'],
    ];
    $this->db->insert('jalur_ppdb', $simpan);
    return $this->db->affected_rows();
  }

  // response untuk data jalur ppdb saat memilih jalur pppdb
  public function getDataJalur($id){
    return $this->db->get_where('jalur_ppdb',['id'=>$id])->row_array();
  }

  public function prosesSimpanRangking($data){
      $simpan = [
      'rangking' => $data['namaRangking'],
      'skorRanking' => $data['skorRangking'],
    ];
    $this->db->insert('rangking_skor',$simpan);
    return $this->db->affected_rows();
  }


  // untuk nilai prestasi

  public function prosesSimpanPrestasi($form)
  {

    $data = [

      'kejuaraan' => $form['tingkat'],
      'id_satuan' => $form['kategori'],
      'score' => $form['nilaiPrestasi']
    ];

    $this->db->insert('prestasi', $data);
    return $this->db->affected_rows();
  }

  public function prosesEditNilaiPrestasi($form)
  {
    $data = [
      'kejuaraan' => $form['tingkat'],
      'id_satuan' => $form['kategori'],
      'score' => $form['nilaiPrestasi']
    ];

    $this->db->set($data);
    $this->db->where('id', $form['idPrestasi']);
    $this->db->update('prestasi');
    return $this->db->affected_rows();
  }

  public function prosesHapusPrestasi($id)
  {
    $this->db->delete('prestasi', ['id' => $id]);
    return $this->db->affected_rows();
  }

  // untuk informasi
  public function simpanInformasi($form)
  {
    $user['user'] = $this->db->get_where('user', ['email' => $this->session->userdata('email')])->row_array();
    $data = [
      'title' => $form['titleInformasi'],
      'deskripsi' => $form['deskripsi'],
      'date_created' => time(),
      'by_created' => $user['user']['name'],
      'is_active' => '1',
      'is_headline' => '1'
    ];
    $this->db->insert('informasi', $data);
    return $this->db->affected_rows();
  }

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

  public function prosesEditInformasi($form)
  {
    $data = [
      'title' => $form['titleInformasi'],
      'deskripsi' => $form['deskripsi'],
      'is_headline' => $form['jumboTron']
    ];
    $this->db->set($data);
    $this->db->where('id', $form['idInformasi']);
    $this->db->update('informasi');
    return $this->db->affected_rows();
  }

  public function prosesHapusInformasi($id)
  {
    $this->db->delete('informasi', ['id' => $id]);
    return $this->db->affected_rows();
  }

  public function getJumlahDaftar($jalur)
  {
    return $this->db->get_where('register', ['statusDaftar' => $jalur])->num_rows();
  }

  // batas informasi method

  // batas awal data tables
  // start datatables
  var $column_order = array(null, 'koreg', 'namaSiswa', 'totalNilai', 'totalJarak'); //set column field database for datatable orderable
  var $column_search = array('koreg', 'namaSiswa'); //set column field database for datatable searchable
  var $order = array('totalNilai' => 'desc'); // default orders

  private function _get_datatables_query()
  {
    $this->db->select('pendaftar.*, jalur_ppdb.ppdb as namaJalur');
    $this->db->from('pendaftar');
    $this->db->join('jalur_ppdb', 'pendaftar.id_jalur = jalur_ppdb.id');
    // $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)]);
    }
  }


  // get data tables all

  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('pendaftar');
    return $this->db->count_all_results();
  }
  // end datatables all



  // data tables ke 1

  function get_datatables1()
  {
    $this->_get_datatables_query1();
    if (@$_POST['length'] != -1)
      $this->db->limit(@$_POST['length'], @$_POST['start']);
    $query = $this->db->get();
    return $query->result();
  }


  private function _get_datatables_query1()
  {
    $this->db->select('pendaftar.*, jalur_ppdb.ppdb as namaJalur');
    $this->db->from('pendaftar');
    $this->db->where('id_jalur', 1);
    $this->db->join('jalur_ppdb', 'pendaftar.id_jalur = jalur_ppdb.id');
    // $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)]);
    }
  }

  // batas akhir data tables ke 1

  // get data yg Tidaklulus
  public function getDataTidakLulus()
  {
    $query = "SELECT `pendaftar`.*,`jalur_ppdb`.`ppdb` 
    FROM `pendaftar` JOIN `jalur_ppdb`
    ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
   WHERE `pendaftar`.`lulus` =5";
    return $this->db->query($query)->result_array();
  }

  // get data yg lulus
  public function getDataLulus()
  {
    $query = "SELECT `pendaftar`.*,`jalur_ppdb`.`ppdb` 
     FROM `pendaftar` JOIN `jalur_ppdb`
     ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
    WHERE `pendaftar`.`lulus` =4";
    return $this->db->query($query)->result_array();
  }


  public function ambilDataPendaftarByjalur($jalur)
  {

    $query = "SELECT `pendaftar`.`namaSiswa`,totalNilai,totalJarak,asalSekolah,koreg,statusPendaftaran,`pendaftar`.`id`,`jalur_ppdb`.`ppdb`
    FROM `pendaftar` JOIN `jalur_ppdb`
    ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
    WHERE `pendaftar`.`id_jalur`= $jalur
    ORDER BY `pendaftar`.`totalJarak` ASC ,`pendaftar`.`totalNilai` DESC ";
    return $this->db->query($query)->result_array();
  }


  public function getDataKategoriNilaiAkademik()
  {
    $query = "SELECT `kat_satuan`.*,`prestasi`.*,`kategori_penyelengara`.*
    FROM `kat_satuan`
   JOIN `prestasi`
    ON `prestasi`.`id_satuan`=`kat_satuan`.`id_satuan`

    JOIN `kategori_penyelengara`
    ON `kategori_penyelengara`.`id_penyelengara`=`kat_satuan`.`id_penyelengara`
    
    ";
    return $this->db->query($query)->result_array();
  }


  public function getdataPendaftarByUser($key)
  {
    if ($this->db->get_where('pendaftar', ['koreg' => $key])->num_rows() > 0) {
      $data = $this->db->get_where('pendaftar', ['koreg' => $key])->row_array();
      $jalur = $data['id_jalur'];
      // cek data jika sudah di verifikasi
      if ($data['statusPendaftaran'] == 2) {
        // jika jalur prestasi akademik dan non akademik
        if ($data['id_jalur'] == 4) {
          $query = "SELECT  `namaSiswa`,`jumlahRataRata`,`noUrut`,`nilaiPraktekPrestasi`,`lulus`,`id_jalur`,`idPrestasi`,`asalSekolah`,`totalNilai`,`totalJarak`,`statusPendaftaran`,`nilaiPrestasi`,`koreg`,`usiaSiswa`,`rataRataTotalNilai`,`jalur_ppdb`.*
        FROM `pendaftar`
       JOIN `jalur_ppdb`
        ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
        WHERE id_jalur = $jalur and statusPendaftaran=2
        ORDER BY nilaiPrestasi DESC, totalJarak ASC, usiaSiswa DESC, jumlahRataRata DESC";
          // jika jalur rerata raport
        } elseif ($data['id_jalur'] == 6) {
          $query = "SELECT  `namaSiswa`,`noUrut`,`nilaiPraktekPrestasi`,`lulus`,`jumlahRataRata`,id_jalur,`idPrestasi`,`asalSekolah`,`totalNilai`,`totalJarak`,`statusPendaftaran`,`nilaiPrestasi`,`usiaSiswa`,`koreg`,`rataRataTotalNilai`,`jalur_ppdb`.*
        FROM `pendaftar`
        JOIN `jalur_ppdb`
        ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
        WHERE id_jalur = $jalur and statusPendaftaran=2
        ORDER BY jumlahRataRata DESC, totalJarak ASC, usiaSiswa DESC";
        } else {
          // jika mengatur jarak
          $query = "SELECT  `namaSiswa`,`noUrut`,`nilaiPraktekPrestasi`,`lulus`,`jumlahRataRata`,id_jalur,`idPrestasi`,`asalSekolah`,`totalNilai`,`statusPendaftaran`,`totalJarak`,`nilaiPrestasi`,`rataRataTotalNilai`,`koreg`,`usiaSiswa`,`jalur_ppdb`.*
          FROM `pendaftar`
          JOIN `jalur_ppdb`
          ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
          WHERE id_jalur = $jalur and statusPendaftaran=2 
          ORDER BY totalJarak ASC, usiaSiswa DESC, jumlahRataRata DESC";
        }
      }
      // jika belum verifikasi tidak lulus  atau cabut berkas
      else {
        // jika jalur prestasi akademik dan non akademik
        if ($data['id_jalur'] == 4) {
          $query = "SELECT  `namaSiswa`,`jumlahRataRata`,`noUrut`,`nilaiPraktekPrestasi`,`lulus`,`id_jalur`,`idPrestasi`,`asalSekolah`,`totalNilai`,`totalJarak`,`statusPendaftaran`,`nilaiPrestasi`,`koreg`,`usiaSiswa`,`rataRataTotalNilai`,`jalur_ppdb`.*
        FROM `pendaftar`
       JOIN `jalur_ppdb`
        ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
        WHERE id_jalur = $jalur
        ORDER BY nilaiPrestasi DESC, totalJarak ASC, usiaSiswa DESC, jumlahRataRata DESC";
          // jika jalur rerata raport
        } elseif ($data['id_jalur'] == 6) {
          $query = "SELECT  `namaSiswa`,`noUrut`,`nilaiPraktekPrestasi`,`lulus`,`jumlahRataRata`,id_jalur,`idPrestasi`,`asalSekolah`,`totalNilai`,`totalJarak`,`statusPendaftaran`,`nilaiPrestasi`,`usiaSiswa`,`koreg`,`rataRataTotalNilai`,`jalur_ppdb`.*
        FROM `pendaftar`
       JOIN `jalur_ppdb`
        ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
        WHERE id_jalur = $jalur
        ORDER BY jumlahRataRata DESC, totalJarak ASC, usiaSiswa DESC";
        } else {
          // jika mengatur jarak
          $query = "SELECT  `namaSiswa`,`noUrut`,`nilaiPraktekPrestasi`,`lulus`,`jumlahRataRata`,id_jalur,`idPrestasi`,`asalSekolah`,`totalNilai`,`statusPendaftaran`,`totalJarak`,`nilaiPrestasi`,`rataRataTotalNilai`,`koreg`,`usiaSiswa`,`jalur_ppdb`.*
     FROM `pendaftar`
     JOIN `jalur_ppdb`
      ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
      WHERE id_jalur = $jalur 
      ORDER BY totalJarak ASC, usiaSiswa DESC, jumlahRataRata DESC";
        }
      }

      $hasil['pendaftar'] = $this->db->query($query)->result_array();
      $no = 1;
      $data = [];
      foreach ($hasil['pendaftar'] as $rank) {
        $row['rank'] = $no++;
        $row['koreg'] = $rank['koreg'];
        $row['nama'] = $rank['namaSiswa'];
        $row['lulus'] = $rank['lulus'];
        $row['nilai'] = $rank['totalNilai'];
        $row['jarak'] = $rank['totalJarak'];
        $row['id_jalur'] = $rank['id_jalur'];
        $row['idPrestasi'] = $rank['idPrestasi'];
        $row['ppdb'] = $rank['ppdb'];
        $row['usia'] = $rank['usiaSiswa'];
        $row['noUrut'] = $rank['noUrut'];
        $row['sekolah'] = $rank['asalSekolah'];
        $row['rataRata'] = $rank['rataRataTotalNilai'];
        $row['jumlahRataRata'] = $rank['jumlahRataRata'];
        $row['nilaiPrestasi'] = $rank['nilaiPrestasi'];
        $row['nilaiPraktekPrestasi'] = $rank['nilaiPraktekPrestasi'];
        $row['statusPendaftaran'] = $rank['statusPendaftaran'];
        // $row['totalDaftar'] = $rank['totalDaftar'];
        $row['quota'] = $rank['quota'];

        $data[$rank['koreg']] = $row;
      }
      return ($data[$key]); 
    } else {
      return  2;
    }
  }

  public function getJumlahBydJalur($idjalur)
  {
    $data = [];
    $query = "SELECT `pendaftar`.`koreg`, `jalur_ppdb`.`ppdb`,
              COUNT(*) as jumlahDaftar,
              SUM(statusPendaftaran=1)as belumVErifikasi,
              SUM(statusPendaftaran=2) as terVerifikasi,
              SUM(statusPendaftaran=4) as lulus,
              SUM(statusPendaftaran=5) as Tidaklulus,
              SUM(statusPendaftaran=3) as pindah
              FROM pendaftar JOIN jalur_ppdb ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
              WHERE `pendaftar`.`id_jalur`=$idjalur
              ";
    $data = $this->db->query($query)->row_array();
    return $data;
  }


  function tanggal_indonesia($tanggal)
  {
    $bulan = array(
      1 =>   'Januari',
      'Februari',
      'Maret',
      'April',
      'Mei',
      'Juni',
      'Juli',
      'Agustus',
      'September',
      'Oktober',
      'November',
      'Desember'
    );

    $pecahkan = explode('-', $tanggal);

    // variabel pecahkan 0 = tanggal
    // variabel pecahkan 1 = bulan
    // variabel pecahkan 2 = tahun

    return $pecahkan[2] . ' ' . $bulan[(int) $pecahkan[1]] . ' ' . $pecahkan[0];
  }

  public function getDataPendaftarDiterima()
  {
    $this->db->select('register.*,jalur_ppdb.ppdb');
    $this->db->from('register');
    $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
    $this->db->where('register.lulus=4');
    $this->db->order_by('register.id_jalur ASC');
    $hasil = $this->db->get()->result_array();
    return $hasil;
  }

  public function getDataLulusExcel()
  {
    $this->db->select('register.*,jalur_ppdb.ppdb');
    $this->db->from('register');
    $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
    $this->db->where('register.lulus=4');
    $this->db->order_by('register.id_jalur ASC');
    $hasil = $this->db->get()->result_object();
    return $hasil;
   
  }

  public function getDataForExcel($jalur)
  {

    if ($jalur == 4) {
      $cekJalurKejuaraan = CekData::getInfoJalur(1);
      if($cekJalurKejuaraan['input_nilai']==1){
        $this->db->select(
          'register.*,
          register.nisn as nisnSiswa,
          nilai.total,
          jalur_ppdb.ppdb,
          districts.name AS `kecamatan`,
          villages.name AS `desa`,
          regencies.name AS `kota`,
          provinces.name AS `provinsi`,wali.*, prestasi.*,kat_satuan.*,kategori_penyelengara.*,kejuaraansiswa.*',
          false
        );
      }else{
        $this->db->select(
          'register.*,
          register.nisn as nisnSiswa,
          jalur_ppdb.ppdb,
          districts.name AS `kecamatan`,
          villages.name AS `desa`,
          regencies.name AS `kota`,
          provinces.name AS `provinsi`,wali.*, prestasi.*,kat_satuan.*,kategori_penyelengara.*,kejuaraansiswa.*',
          false
        );
      }
      
      $this->db->from('register');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->join('wali', 'wali.idRegister = register.id');
      if($cekJalurKejuaraan['input_nilai']==1){

        $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->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_jalur=' . $jalur);
      $hasil = $this->db->get()->result_object();
      return $hasil;
    } elseif ($jalur == 2) {
      $cekJalurAfir = CekData::getInfoJalur(2);
      if($cekJalurAfir['input_nilai']==1){
        $this->db->select(
          'register.*,
          register.nisn as nisnSiswa,
          nilai.total,
          jalur_ppdb.ppdb,
          districts.name AS `kecamatan`,
          villages.name AS `desa`,
          regencies.name AS `kota`,
          provinces.name AS `provinsi`,wali.*,afirmasi.*,jenis_afirmasi.nama_jenis_afirmasi,',
          false
        );
      }else{
        $this->db->select(
          'register.*,
          register.nisn as nisnSiswa,
          jalur_ppdb.ppdb,
          districts.name AS `kecamatan`,
          villages.name AS `desa`,
          regencies.name AS `kota`,
          provinces.name AS `provinsi`,wali.*,afirmasi.*,jenis_afirmasi.nama_jenis_afirmasi,',
          false
        );
      }
     
      $this->db->from('register');
      if($cekJalurAfir['input_nilai']==1){

        $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
      }
      $this->db->join('afirmasi', 'afirmasi.id_pendaftar = register.id');
      $this->db->join('wali', 'wali.idRegister = 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->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_jalur=' . $jalur);
      $hasil = $this->db->get()->result_object();
      return $hasil;
    } else if($jalur==3){
      $cekJalurPerpidahan = CekData::getInfoJalur(3);
      if($cekJalurPerpidahan['input_nilai']==1){
        $this->db->select(
          'register.*,
          register.nisn as nisnSiswa,
          nilai.total,
          jalur_ppdb.ppdb,
          districts.name AS `kecamatan`,
          villages.name AS `desa`,
          regencies.name AS `kota`,
          provinces.name AS `provinsi`,wali.*',
          false
        );
      }else{
        $this->db->select(
          'register.*,
          register.nisn as nisnSiswa,
          jalur_ppdb.ppdb,
          districts.name AS `kecamatan`,
          villages.name AS `desa`,
          regencies.name AS `kota`,
          provinces.name AS `provinsi`,wali.*',
          false
        );
      }
      $this->db->from('register');
      if($cekJalurPerpidahan['input_nilai']==1){

        $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
      }
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->join('wali', 'wali.idRegister = register.id');
      $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_jalur=' . $jalur);
      $hasil = $this->db->get()->result_object();
      return $hasil;
    }else if($jalur==4){
      $cekJalurPerpidahan = CekData::getInfoJalur(4);
      if($cekJalurPerpidahan['input_nilai']==1){
        $this->db->select(
          'register.*,
          register.nisn as nisnSiswa,
          nilai.total,
          jalur_ppdb.ppdb,
          districts.name AS `kecamatan`,
          villages.name AS `desa`,
          regencies.name AS `kota`,
          provinces.name AS `provinsi`,wali.*',
          false
        );
      }else{
        $this->db->select(
          'register.*,
          register.nisn as nisnSiswa,
          jalur_ppdb.ppdb,
          districts.name AS `kecamatan`,
          villages.name AS `desa`,
          regencies.name AS `kota`,
          provinces.name AS `provinsi`,wali.*',
          false
        );
      }
      $this->db->from('register');
      if($cekJalurPerpidahan['input_nilai']==1){

        $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
      }
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->join('wali', 'wali.idRegister = register.id');
      $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_jalur=' . $jalur);
      $hasil = $this->db->get()->result_object();
      return $hasil;
    }else if($jalur==5){
      $cekJalurPerpidahan = CekData::getInfoJalur(5);
      if($cekJalurPerpidahan['input_nilai']==1){
        $this->db->select(
          'register.*,
          register.nisn as nisnSiswa,
          nilai.total,
          jalur_ppdb.ppdb,
          districts.name AS `kecamatan`,
          villages.name AS `desa`,
          regencies.name AS `kota`,
          provinces.name AS `provinsi`,wali.*',
          false
        );
      }else{
        $this->db->select(
          'register.*,
          register.nisn as nisnSiswa,
          jalur_ppdb.ppdb,
          districts.name AS `kecamatan`,
          villages.name AS `desa`,
          regencies.name AS `kota`,
          provinces.name AS `provinsi`,wali.*',
          false
        );
      }
      $this->db->from('register');
      if($cekJalurPerpidahan['input_nilai']==1){

        $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
      }
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->join('wali', 'wali.idRegister = register.id');
      $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_jalur=' . $jalur);
      $hasil = $this->db->get()->result_object();
      return $hasil;
    }else if($jalur==6){
      $cekJalurPerpidahan = CekData::getInfoJalur(6);
      if($cekJalurPerpidahan['input_nilai']==1){
        $this->db->select(
          'register.*,
          register.nisn as nisnSiswa,
          nilai.total,
          jalur_ppdb.ppdb,
          districts.name AS `kecamatan`,
          villages.name AS `desa`,
          regencies.name AS `kota`,
          provinces.name AS `provinsi`,wali.*',
          false
        );
      }else{
        $this->db->select(
          'register.*,
          register.nisn as nisnSiswa,
          jalur_ppdb.ppdb,
          districts.name AS `kecamatan`,
          villages.name AS `desa`,
          regencies.name AS `kota`,
          provinces.name AS `provinsi`,wali.*',
          false
        );
      }
      $this->db->from('register');
      if($cekJalurPerpidahan['input_nilai']==1){

        $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
      }
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->join('wali', 'wali.idRegister = register.id');
      $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_jalur=' . $jalur);
      $hasil = $this->db->get()->result_object();
      return $hasil;
    }
  }



  public function getMinmax($jalur)
  {
    // jumlah pendaftar dan jalur
    $query = "SELECT `pendaftar`.`statusPendaftaran`,`jalur_ppdb`.`ppdb`, 
     COUNT(*) as jumlahPendaftar,
    SUM(statusPendaftaran = 2) as jumlahVerifiksi,
    SUM(statusPendaftaran = 1) as jumlahTidakVerifiksi,
    SUM(statusPendaftaran = 3) as jumlahPindah,
    SUM(statusPendaftaran = 4) as lulus,
    SUM(statusPendaftaran = 5) as tidakLulus
    FROM `pendaftar` JOIN `jalur_ppdb`
    ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
   WHERE `pendaftar`.`id_jalur`=$jalur 
  ";
    // nilai nya 
    $query2 = "SELECT `pendaftar`.`jumlahRataRata`,`totalJarak`,`nilaiPrestasi`,`statusPendaftaran`,
  MAX(jumlahRataRata) as maxrataRataNilai, 
  MIN(jumlahRataRata) as minrataRataNilai,
  MAX(totalJarak) as maxTotalJarak,
  MIN(totalJarak) as minTotalJarak,
  MAX(nilaiPrestasi) as maxNilaiPrestasi,
  MIN(nilaiPrestasi) as minNilaiPrestasi
  FROM `pendaftar` 
 WHERE `statusPendaftaran` = 2 and `id_jalur`=$jalur";

    $data['pdfr'] = $this->db->query($query)->row_array();
    $data['nilai'] = $this->db->query($query2)->row_array();
    return $data;
  }
  // public function getMinmax()
  // {
  //   $query = "SELECT `pendaftar`.*,`jalur_ppdb`.`ppdb`, 
  //   COUNT(*) as jumlahPendaftar,
  //   SUM(statusPendaftaran = 2) as jumlahVerifiksi,
  //   SUM(statusPendaftaran = 1) as jumlahTidakVerifiksi,
  //   SUM(statusPendaftaran = 3) as jumlahPindah,
  //   SUM(statusPendaftaran = 4) as lulus,
  //   SUM(statusPendaftaran = 5) as tidakLulus,
  //   MAX(jumlahRataRata) as maxrataRataNilai, 
  //   MIN(jumlahRataRata) as minrataRataNilai,
  //   MAX(totalJarak) as maxTotalJarak,
  //   MIN(totalJarak) as minTotalJarak,
  //   MAX(nilaiPrestasi) as maxNilaiPrestasi,
  //   MIN(nilaiPrestasi) as minNilaiPrestasi
  //   FROM `pendaftar` JOIN `jalur_ppdb`
  //   ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
  //   GROUP BY `id_jalur`
  //   ORDER BY `pendaftar`.`id_jalur` ASC 
  // ";
  //   return $this->db->query($query)->result_array();
  // }

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

  // untuk laporan formt A1 statistik pendafataran perjalur====================
  public function statistikLaporanA1()
  {
    // ambil jalur pppdb yang di aktif kan 
    $jalur = $this->db->get_where('jalur_ppdb', ['statusAktif' => 1])->result_array();
    $data = [];
    foreach ($jalur as $value) {
      $row['jalur'] = $value['ppdb'];
      // pendaftar
      $row['Pendaftar'] =
        ($this->_getJumlahPendaftarPerjalur($value['id'], 'L')) + ($this->_getJumlahPendaftarPerjalur($value['id'], 'P')) + ($this->_getJumlahPendaftarPerjalur($value['id'], 'L', 3)) + ($this->_getJumlahPendaftarPerjalur($value['id'], 'P', 3)) + ($this->_getJumlahPendaftarPerjalur($value['id'], 'L', 4)) + ($this->_getJumlahPendaftarPerjalur($value['id'], 'P', 4));

      $row['Pendaftar_L'] = ($this->_getJumlahPendaftarPerjalur($value['id'], 'L')) + ($this->_getJumlahPendaftarPerjalur($value['id'], 'L', 3)) + ($this->_getJumlahPendaftarPerjalur($value['id'], 'L', 4));
      $row['Pendaftar_P'] = ($this->_getJumlahPendaftarPerjalur($value['id'], 'P')) + ($this->_getJumlahPendaftarPerjalur($value['id'], 'P', 3)) + ($this->_getJumlahPendaftarPerjalur($value['id'], 'P', 4));

      // diterima
      $row['Pendaftar_Drima'] = ($this->_getJumlahPendaftarDiterimaTidak($value['id'], 'L')) + ($this->_getJumlahPendaftarDiterimaTidak($value['id'], 'P'));
      $row['Pendaftar_Drima_L'] = $this->_getJumlahPendaftarDiterimaTidak($value['id'], 'L');
      $row['Pendaftar_Drima_P'] = $this->_getJumlahPendaftarDiterimaTidak($value['id'], 'P');

      // tidak diterima
      $row['Pendaftar_TDrima'] = ($this->_getJumlahPendaftarDiterimaTidak($value['id'], 'L', 5)) + ($this->_getJumlahPendaftarDiterimaTidak($value['id'], 'P', 5));
      $row['Pendaftar_TDrima_L'] = $this->_getJumlahPendaftarDiterimaTidak($value['id'], 'L', 5);
      $row['Pendaftar_TDrima_P'] = $this->_getJumlahPendaftarDiterimaTidak($value['id'], 'P', 5);

      // tidak Verifikasi
      $row['Pendaftar_tdk_ver'] = ($this->_getJumlahPendaftarPerjalur($value['id'], 'L', 2)) + ($this->_getJumlahPendaftarPerjalur($value['id'], 'P', 2));
      $row['Pendaftar_tdk_ver_L'] = $this->_getJumlahPendaftarPerjalur($value['id'], 'L', 2);
      $row['Pendaftar_tdk_ver_P'] = $this->_getJumlahPendaftarPerjalur($value['id'], 'P', 2);

      // cabut Berkas
      $row['Pendaftar_tdk_cabut'] = ($this->_getJumlahPendaftarPerjalur($value['id'], 'L', 4)) + ($this->_getJumlahPendaftarPerjalur($value['id'], 'P', 4));
      $row['Pendaftar_tdk_cabut_L'] = $this->_getJumlahPendaftarPerjalur($value['id'], 'L', 4);
      $row['Pendaftar_tdk_cabut_P'] = $this->_getJumlahPendaftarPerjalur($value['id'], 'P', 4);

      $data[] = $row;
    }
    return $data;
  }

  // cari pendaftaran berdasarkan jalur jenis kelamain
  private function _getJumlahPendaftarPerjalur($jalur, $jk, $status = 2)
  {
    return $this->db->get_where('register', ['id_jalur' => $jalur, 'jk' => $jk, 'statusDaftar' => $status])->num_rows();
  }

  private function _getJumlahPendaftarDiterimaTidak($jalur, $jk, $status = 4)
  {
    return $this->db->get_where('register', ['id_jalur' => $jalur, 'jk' => $jk, 'lulus' => $status])->num_rows();
  }

 
 
}
