<?php
defined('BASEPATH') or exit('No direct script access allowed');
class Model_zonasi extends CI_Model
{
  public function getDataDaftarlimit($jalur)
  {
    $lim = $this->db->get_where('jalur_ppdb', ['id' => 2])->row_array();
    $limit = $lim['quota'];
    $query = "SELECT `pendaftar`.`namaSiswa`,totalNilai,totalJarak,asalSekolah,koreg,`pendaftar`.`id`,`jalur_ppdb`.`ppdb`
    FROM `pendaftar` JOIN `jalur_ppdb`
    ON `pendaftar`.`id_jalur`=`jalur_ppdb`.`id`
    WHERE `pendaftar`.`id_jalur`= $jalur AND `pendaftar`.`statusPendaftaran`=2
    ORDER BY `pendaftar`.`totalJarak` ASC ,`pendaftar`.`totalNilai`
     DESC LIMIT $limit ";
    return $this->db->query($query)->result_array();
  }

  public function getDataDaftar()
  {
    $dataJalurZonasi = CekData::getInfoJalur(1);
    if($dataJalurZonasi['input_nilai']==1){
      $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->where('register.statusDaftar=3');
      $this->db->where('register.id_jalur=1');
      $this->db->order_by('register.jarak ASC,register.usia DESC,totalSkor DESC');
    }else{
      $this->db->select('register.id AS `id_register`, register.*');
      $this->db->from('register');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->where('register.statusDaftar=3');
      $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;
  }

  public function getDataDaftarAfirmasi()
  {
    $cekJalurAfirmasi = CekData::getInfoJalur(2);
    if($cekJalurAfirmasi['input_nilai']==1){
    $this->db->select('register.*, register.id AS `id_register`, nilai.totalSkor,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.statusDaftar=3');
    $this->db->where('register.id_jalur=2');
    $this->db->order_by('register.jarak ASC,usia DESC,nilai.totalSkor DESC');
    }else{
      $this->db->select('register.*, register.id AS `id_register`,jalur_ppdb.ppdb,quota,afirmasi.*,jenis_afirmasi.*');
      $this->db->from('register');
      $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.statusDaftar=3');
      $this->db->where('register.id_jalur=2');
      $this->db->order_by('register.jarak ASC,usia');
    }
    return $hasil = $this->db->get()->result_array();
  }

  public function getDataDaftarJalurOrangtua()
  {
    $jalurOrangTua = CekData::getInfoJalur(3);
    if($jalurOrangTua['input_nilai']==1){
      $this->db->select('register.*, register.id AS `id_register`, nilai.totalSkor,jalur_ppdb.ppdb,quota');
      $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.statusDaftar=3');
      $this->db->where('register.id_jalur=3');
      $this->db->order_by('register.jarak ASC,usia DESC,nilai.totalSkor DESC');
    }else{
      $this->db->select('register.*, register.id AS `id_register`,jalur_ppdb.ppdb,quota');
      $this->db->from('register');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->where('register.statusDaftar=3');
      $this->db->where('register.id_jalur=3');
      $this->db->order_by('register.jarak ASC,usia DESC');
    }
   
    return $hasil = $this->db->get()->result_array();
  }

  public function getDataDaftarJalurAkademik()
  {
    $cekJalur = CekData::getInfoJalur(4);
    if($cekJalur['input_nilai']==1){
      $this->db->select('register.*,register.id AS `id_register`, jalur_ppdb.*,prestasi.*,kat_satuan.satuan,kategori_penyelengara.peyelengara,nilai.total,nilai.totalSkor,kejuaraansiswa.namaKejuaraan,skor,kejuaraansiswa.total_skor_kejuaraan');
      $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.statusDaftar=3');
      $this->db->where('register.id_jalur=4');
      $this->db->order_by('kejuaraansiswa.total_skor_kejuaraan DESC,register.usia DESC, nilai.total DESC');

    }else{
      $this->db->select('register.*,register.id AS `id_register`, jalur_ppdb.*,prestasi.*,kat_satuan.satuan,kategori_penyelengara.peyelengara,kejuaraansiswa.namaKejuaraan,skor,kejuaraansiswa.total_skor_kejuaraan');
      $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('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.statusDaftar=3');
      $this->db->where('register.id_jalur=4');
      $this->db->order_by('kejuaraansiswa.total_skor_kejuaraan DESC,register.usia DESC');
    }
    
    $hasil = $this->db->get()->result_array();
    return $hasil;
  }

  public function getDataDaftarJalurPerbatasan()
  {
    $cekJalur = CekData::getInfoJalur(5);
    if($cekJalur['input_nilai']==1){
      $this->db->select('register.*, register.id AS `id_register`, nilai.total,nilai.totalSkor,jalur_ppdb.ppdb,quota');
      $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.statusDaftar=3');
      $this->db->where('register.id_jalur=5');
      $this->db->order_by('register.jarak ASC,usia DESC,nilai.totalSkor DESC');
    }else{

      $this->db->select('register.*, register.id AS `id_register`,jalur_ppdb.ppdb,quota');
      $this->db->from('register');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->where('register.statusDaftar=3');
      $this->db->where('register.id_jalur=5');
      $this->db->order_by('register.jarak ASC,usia DESC');
    }
   
    return $hasil = $this->db->get()->result_array();
  }

  public function getDataDaftarJalurRaport()
  {
    $cekJalurRaport = CekData::getInfoJalur(6);
    if($cekJalurRaport['input_afirmasi']==1){
      $this->db->select('register.*, register.id AS `id_register`, nilai.total,nilai.totalSkor,jalur_ppdb.ppdb,quota,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('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
    $this->db->where('register.statusDaftar=3');
    $this->db->where('register.id_jalur=6');
    $this->db->order_by('nilai.totalSKor DESC,register.jarak ASC,register.usia DESC');
    }else{
      $this->db->select('register.*, register.id AS `id_register`, nilai.total,nilai.totalSkor,jalur_ppdb.ppdb,quota');
      $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.statusDaftar=3');
      $this->db->where('register.id_jalur=6');
      $this->db->order_by('nilai.totalSKor DESC,register.jarak ASC,register.usia DESC');
    }
    
     $hasil = $this->db->get()->result_array();
    return $hasil;
  }

  // area print data all per jalur
  // 2021 

  public function getDataDaftarZonasi($status = '3')
  {
    $cekJalur = CekData::getInfoJalur(1);
    if($cekJalur['input_nilai']==1){
      $this->db->select('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->where('register.statusDaftar=' . $status);
      $this->db->where('register.id_jalur=1');
      $this->db->order_by('register.jarak ASC,usia DESC,nilai.totalSkor DESC');
      $hasil = $this->db->get()->result_array();
      return $hasil;
    }else{
      $this->db->select('register.*,jalur_ppdb.*');
      $this->db->from('register');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->where('register.statusDaftar=' . $status);
      $this->db->where('register.id_jalur=1');
      $this->db->order_by('register.jarak ASC,usia DESC');
      $hasil = $this->db->get()->result_array();
      return $hasil;
    }
      
    }
    
   
  

  // jalur zonasi afirmasi
  public function getDataDaftarAllAfirmasi($status = '3')
  {
    $cekjalrAfr = CekData::getInfoJalur(2);
    if($cekjalrAfr['input_nilai']==1){
      $this->db->select('register.*,nilai.total,nilai.totalSkor,jalur_ppdb.ppdb,quota');
      $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.statusDaftar=' . $status);
      $this->db->where('register.id_jalur=2');
      $this->db->order_by('register.jarak ASC,usia DESC,nilai.totalSkor DESC');
    }else{
      $this->db->select('register.*,jalur_ppdb.ppdb,quota');
      $this->db->from('register');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->where('register.statusDaftar=' . $status);
      $this->db->where('register.id_jalur=2');
      $this->db->order_by('register.jarak ASC,usia DESC');
    }
     
      $hasil = $this->db->get()->result_array();
      return $hasil;
  }

  // jalur orng tua
  public function getDataDaftarAllJalurOrangtua($status = '3')
  {
    $cekJalur = CekData::getInfoJalur(3);
    if($cekJalur['input_nilai']==1){
      $this->db->select('register.*,nilai.total,nilai.totalSkor,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.statusDaftar=' . $status);
      $this->db->where('register.id_jalur=3');
      $this->db->order_by('register.jarak ASC,register.usia DESC,nilai.totalSkor DESC');
    } else {
      $this->db->select('register.*,jalur_ppdb.*');
      $this->db->from('register');
      $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
      $this->db->where('register.statusDaftar=' . $status);
      $this->db->where('register.id_jalur=3');
      $this->db->order_by('register.jarak ASC,register.usia DESC');
    }
    $hasil = $this->db->get()->result_array();
    return $hasil;
  }

  // jalur lomba akademik dan non akademik
  public function getDataDaftarAllJalurAkademik($status = '3')
  {
    $cekJalurAka = CekData::getInfoJalur(4);
    if($cekJalurAka['input_nilai']==1){
      $this->db->select('register.*,jalur_ppdb.*,prestasi.*,kat_satuan.satuan,kategori_penyelengara.peyelengara,nilai.total,nilai.totalSkor,kejuaraansiswa.namaKejuaraan,kejuaraansiswa.total_skor_kejuaraan, 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->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.statusDaftar=' . $status);
      $this->db->where('register.id_jalur=4');
      $this->db->order_by('kejuaraansiswa.total_skor_kejuaraan DESC,register.usia DESC');
    } else {
      $this->db->select('register.*,jalur_ppdb.*,prestasi.*,kat_satuan.satuan,kategori_penyelengara.peyelengara,kejuaraansiswa.namaKejuaraan,kejuaraansiswa.total_skor_kejuaraan, 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('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.statusDaftar=' . $status);
      $this->db->where('register.id_jalur=4');
      $this->db->order_by('kejuaraansiswa.total_skor_kejuaraan DESC,register.usia DESC');
    }
      $hasil = $this->db->get()->result_array();
      return $hasil;
  }

  // jalur perbatasan
  public function getDataDaftarAllJalurPerbatasan($status = '3')
  {
    $cekJalur = CekData::getInfoJalur(5);
    if($cekJalur['input_nilai']==1){
      $this->db->select('register.*,nilai.total,nilai.totalSkor,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.statusDaftar=' . $status);
      $this->db->where('register.id_jalur=5');
      $this->db->order_by('register.jarak ASC,register.usia DESC,totalSkor DESC');


    }
    else{
      $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.statusDaftar=' . $status);
      $this->db->where('register.id_jalur=5');
      $this->db->order_by('register.jarak ASC,register.usia DESC');
    }
   
      return $this->db->get()->result_array();
  }

  public function getDataDaftarAllJalurRaport($status = '3')
  {
    
      $this->db->select('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->where('register.statusDaftar=' . $status);
      $this->db->where('register.id_jalur=6');
      $this->db->order_by('nilai.totalSkor DESC, register.jarak ASC, register.usia DESC');
      return$this->db->get()->result_array();
  }
}
