<?php
defined('BASEPATH') or exit('No direct script access allowed');
class Pendaftar_m extends CI_Model
{

  var $column_order = array(null, 'koreg', 'nama', 'ppdb'); //set column field database for datatable orderable
  var $column_search = array('koreg', 'nama', 'ppdb'); //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', 3);
    // $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('pendaftar');
    return $this->db->count_all_results();
  }
  // akhir data tables verifikasi
  // ==============================================================================data kelulusan======================
  public function prosesResetByid($id)
  {
    $data = ['statusDfatar' => 3];
    $this->db->set($data);
    $this->db->where('id', $id);
    $this->db->update('register');
    return $this->db->affected_rows();
  }

  // cetak data lulus berdasarkan jalur 
  public function getDataLulusById($jalur)
  {

    $this->db->select('register.id AS `id_register`, register.*,jalur_ppdb.*,wali.*');
    $this->db->from('register');
    $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
    $this->db->join('wali', 'wali.idRegister = register.id');
    $this->db->where('register.lulus=4');
    $this->db->where('register.id_jalur=' . $jalur);
    return $this->db->get()->result_array();
  }
  // print lulus satuan
  public function printDataLulusSatuan($koreg)
  {
    $this->db->select('register.id AS `id_register`, register.*,jalur_ppdb.*,wali.*');
    $this->db->from('register');
    $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
    $this->db->join('wali', 'wali.idRegister = register.id');
    $this->db->where('register.lulus=4');
    $this->db->where('register.koreg=' . $koreg);
    return $this->db->get()->row_array();
  }

  // print tidaklulus satuan
  public function printDataTidakLulusSatuan($koreg)
  {
    $this->db->select('register.id AS `id_register`, register.*,jalur_ppdb.*,wali.*');
    $this->db->from('register');
    $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
    $this->db->join('wali', 'wali.idRegister = register.id');
    $this->db->where('register.lulus=5');
    $this->db->where('register.koreg=' . $koreg);
    return $this->db->get()->row_array();
  }

  public function getDataTidakLulusByjalur($jalur)
  {

    $this->db->select('register.id AS `id_register`, register.*,jalur_ppdb.*,wali.*');
    $this->db->from('register');
    $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
    $this->db->join('wali', 'wali.idRegister = register.id');
    $this->db->where('register.lulus=5');
    $this->db->where('register.id_jalur=' . $jalur);
    return $this->db->get()->result_array();
  }

  public function getDataLulusAll()
  {
    $this->db->select('register.id AS `id_register`, register.*, 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->join('kejuaraansiswa', 'kejuaraansiswa.id_pendaftar = register.id');
    $this->db->where('register.lulus=4');
    // $this->db->where('register.id_jalur=1');
    $this->db->order_by('register.jarak ASC,register.usia DESC,total DESC');
    $hasil = $this->db->get()->result_array();
    return $hasil;
    // return $this->db->query($query)->result_array();
  }

  public function getDataTidakLulusAll()
  {

    $this->db->select('register.id AS `id_register`, register.*,jalur_ppdb.*,wali.*');
    $this->db->from('register');
    $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
    $this->db->join('wali', 'wali.idRegister = register.id');
    $this->db->where('register.lulus=5');
    return $this->db->get()->result_array();
    // $query = "SELECT `pendaftar`.`id`,`nama`,`koreg`,totalNilai,jumlahRataRata,totalJarak,asalSekolah,id_jalur,namaIbu,tanggalRegister,`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 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.*,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.*,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.*,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;
  }
}
