<?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('register');
    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();
  }

  // untuk pendaftar non submin dan perbaikan
  public function getNonSubmit(){
    $query = "SELECT *
    FROM `register` 
    WHERE `register`.`id_jalur`= 0 OR `register`.`id_jalur`=0";
    return $this->db->query($query)->result_array();
  }

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

    $this->db->select('register.id AS `id_register`,register.nisn as nisnSiswa, 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.nisn as nisnSiswa,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.nisn as nisnSiswa, 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.nisn as nisnSiswa, 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.*,jalur_ppdb.*');
    $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->where('register.lulus=4');
    // $this->db->where('register.id_jalur=1');
    $this->db->order_by('register.jarak ASC,register.usia 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)
  {
    

    $dataNilai=array();
    $dataAfirmasi = [];
    $dataKejuaraan = [];
    $dataSekolah = [];
    $jalurPpdb = [];
    $dataSyarat = [];
    $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.koreg=' . $koreg);
    $user = $this->db->get()->row_array();
    // $user['tglDaftar'] = CekData::tanggalIndo($user['tglDaftar']);
    // $user['tglLahir'] = CekData::tanggalIndo($user['tglLahir']);
    $jalurPpdb = $this->db->get_where('jalur_ppdb',['id'=>$user['id_jalur']])->row_array();

    if($jalurPpdb['input_nilai']==1){
      $dataNilai = CekData::getData('nilai', ['koreg' => $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();
    }

    // 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'=>$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
    ];
    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;
  }

  // reflace email kosong
  public function reflaceEmailKosong()
  {
    $this->db->select('register.email,id,statusDaftar');
    $this->db->from('register');
    $hasil = $this->db->get()->result_array();
    foreach ($hasil as $value) {
      if ($value['email'] == "" || $value['email'] == NULL || $value['email'] == null) {
        $karakter = '123456789';
        $pin = substr(str_shuffle($karakter), 0, 5);
        $data = [
          'email' => 'default' . $pin . '@gmail.com',
        ];
        $this->db->set($data);
        $this->db->where('id', $value['id']);
        $this->db->update('register');
      }
    }
    return $this->db->affected_rows();
  }
}
