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

    public function jarak($longSekolah, $longPendaftar)
    {
        $kodinatSekolah = explode(",", $longSekolah);
        $kodinatDaftar = explode(",", $longPendaftar);

        $latSekolah = isset($kodinatSekolah[0]) ? $kodinatSekolah[0] : $kodinatSekolah[0];
        $longSekolah = isset($kodinatSekolah[1]) ? $kodinatSekolah[1] : $kodinatSekolah[1];
        $latDaftar = isset($kodinatDaftar[0]) ? $kodinatDaftar[0] : $kodinatDaftar[0];
        $longDaftar = isset($kodinatDaftar[1]) ? $kodinatDaftar[1] : $kodinatDaftar[1];
        return $this->_hitungJarak($latSekolah, $longSekolah, $latDaftar, $longDaftar);
    
    }

    public function cekNisnBiforeReg($nisn){
        $query = "SELECT nisn, statusDaftar FROM register where (statusDaftar=? || statusDaftar=? || statusDaftar=?) AND nisn=?";
        $cek = $this->db->query($query, [2,3,4,$nisn])->num_rows();
        if($cek >0){
          return "false";
        }else {
          return "true";
        }
    
      }

      public function cekEmailBiforeReg($email){
        $query = "SELECT email, statusDaftar FROM register where (statusDaftar=?||statusDaftar=?|| statusDaftar==?)AND email=?";
        $cek = $this->db->query($query, [2,3,4,$email])->num_rows();
        if($cek >0){
          return "false";
        }else {
          return "true";
        }
    
      }
    
      public function cekNikBiforeReg($nik){
      $query = "SELECT nik, statusDaftar FROM register where (statusDaftar=? || statusDaftar=?||statusDaftar=?)AND nik=?";
        $cek = $this->db->query($query, [2,3,4,$nik])->num_rows();
        if($cek >0){
          return "false";
        }else {
          return "true";
        }
      }



    // private function
    public  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));
    }

    // cek data pendaftaran user
    public function pendaftaran()
    {
        // cek dia jalur apa
        $dataUser = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->row_array();
        $key = $dataUser['koreg'];

        $hasil = [];
        // if jika jalur zonasi
        if ($dataUser['id_jalur'] == 1) {
            $dataJalurZonasi = CekData::getInfoJalur(1);
            if($dataJalurZonasi['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=3');
                $this->db->where('register.id_jalur=1');
                $this->db->order_by('register.jarak ASC,register.usia DESC,nilai.totalSkor DESC');
            }else{
                $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();
            $no = 1;
            $data = [];
            foreach ($hasil as $rank) {
                $row['peringkat'] = $no++;
                $row['koreg'] = $rank['koreg'];
                $row['nama'] =  $rank['nama'];
               
                if ($dataJalurZonasi['input_nilai'] == 1) {
                    $row['total'] = $rank['total'];
                    $row['totalSkor'] = $rank['totalSkor'];
                    $row['rank'] = $rank['rank'];
                    $row['skorRank'] = $rank['skorRank'];
                }
                $row['jarak'] = $rank['jarak'];
                $row['usia'] = $rank['usia'];
                $row['ppdb'] = $rank['ppdb'];
                $row['quota'] = $rank['quota'];
                $row['noDaftar'] = $rank['noDaftar'];
                $row['sekolah'] = $rank['sekolahAsal'];
                $row['statusDaftar'] = $rank['statusDaftar'];
                // $row['totalDaftar'] = $rank['totalDaftar'];
                //   $row['quota'] = $rank['quota'];
                $data[$rank['koreg']] = $row;
            }
            return ($data[$key]);
            exit;
        }
        // afirmasi
        else if ($dataUser['id_jalur'] == 2) {
            $cekJalurAfirmasi=CekData::getInfoJalur(2);
            if($cekJalurAfirmasi['input_nilai']==1){
                $this->db->select('register.*,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.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.*,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 DESC');
            }
           
            $hasil = $this->db->get()->result_array();
            $no = 1;
            $data = [];
            foreach ($hasil as $rank) {
                $row['peringkat'] = $no++;
                $row['koreg'] = $rank['koreg'];
                $row['nama'] =  $rank['nama'];
                $row['dtks'] =  $rank['dtks'];
                if($cekJalurAfirmasi['input_nilai']==1){
                    
                    $row['total'] = $rank['total'];
                    $row['totalSkor'] = $rank['totalSkor'];
                    $row['rank'] = $rank['rank'];
                    $row['skorRank'] = $rank['skorRank'];
                }
                $row['jenisAfirmasi'] = $rank['nama_jenis_afirmasi'];
                $row['no_afirmasi'] = $rank['no_afirmasi'];
                $row['jarak'] = $rank['jarak'];
                $row['usia'] = $rank['usia'];
                $row['quota'] = $rank['quota'];
                $row['ppdb'] = $rank['ppdb'];
                $row['quota'] = $rank['quota'];
                $row['noDaftar'] = $rank['noDaftar'];
                $row['sekolah'] = $rank['sekolahAsal'];
                $row['statusDaftar'] = $rank['statusDaftar'];
                // $row['totalDaftar'] = $rank['totalDaftar'];
                //   $row['quota'] = $rank['quota'];
                $data[$rank['koreg']] = $row;
            }
            return ($data[$key]);
            exit;
        }

        
        // perpindahan tugas orang tua
        else if ($dataUser['id_jalur'] == 3) {
            $cekJalurOrtu = CekData::getInfoJalur(3);
            if($cekJalurOrtu['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=3');
                $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=3');
                $this->db->where('register.id_jalur=3');
                $this->db->order_by('register.jarak ASC,register.usia DESC');
            }
           
            $hasil = $this->db->get()->result_array();
            $no = 1;
            $data = [];
            foreach ($hasil as $rank) {
                $row['peringkat'] = $no++;
                $row['koreg'] = $rank['koreg'];
                $row['nama'] =  $rank['nama'];
                if($cekJalurOrtu['input_nilai']==1){
                    $row['total'] = $rank['total'];
                    $row['totalSkor'] = $rank['totalSkor'];
                    $row['rank'] = $rank['rank'];
                    $row['skorRank'] = $rank['skorRank'];
                }
                $row['jarak'] = $rank['jarak'];
                $row['usia'] = $rank['usia'];
                $row['ppdb'] = $rank['ppdb'];
                $row['quota'] = $rank['quota'];
                $row['noDaftar'] = $rank['noDaftar'];
                $row['statusDaftar'] = $rank['statusDaftar'];
                // $row['totalDaftar'] = $rank['totalDaftar'];
                //   $row['quota'] = $rank['quota'];
                $data[$rank['koreg']] = $row;
            }
            return ($data[$key]);
            exit;
        }
        // prestasi lomba akademik dan non akademik
        else if ($dataUser['id_jalur'] == 4) {
            $cekJalurAka=CekData::getInfoJalur(4);

            if($cekJalurAka['input_nilai']==1){
                $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('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');
            }else{

                $this->db->select('register.*,jalur_ppdb.*,prestasi.*,kat_satuan.satuan,kategori_penyelengara.peyelengara,kejuaraansiswa.*');
                $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();
            $no = 1;
            $data = [];
            foreach ($hasil as $rank) {
                $row['peringkat'] = $no++;
                $row['koreg'] = $rank['koreg'];
                $row['nama'] =  $rank['nama'];
                if ($cekJalurAka['input_nilai'] == 1) {
                    $row['total'] = $rank['total'];
                    $row['totalSkor'] = $rank['totalSkor'];
                    $row['rank'] = $rank['rank'];
                    $row['skorRank'] = $rank['skorRank'];
                }
                $row['skorKompetensi'] = $rank['skorKompetensi'];
                $row['total_skor_kejuaraan'] = $rank['total_skor_kejuaraan'];
                $row['jarak'] = $rank['jarak'];
                $row['usia'] = $rank['usia'];
                $row['ppdb'] = $rank['ppdb'];
                $row['quota'] = $rank['quota'];
                $row['noDaftar'] = $rank['noDaftar'];
                $row['kejuaraan'] = $rank['kejuaraan'];
                $row['satuan'] = $rank['satuan'];
                $row['peyelengara'] = $rank['peyelengara'];
                $row['statusDaftar'] = $rank['statusDaftar'];
                $row['namaKejuaraan'] = $rank['namaKejuaraan'];
                $row['skor'] = $rank['skor'];
                // $row['totalDaftar'] = $rank['totalDaftar'];
                //   $row['quota'] = $rank['quota'];
                $data[$rank['koreg']] = $row;
            }
            return ($data[$key]);
            exit;
        }
        // penduduk perbatasana
        else if ($dataUser['id_jalur'] == 5) {
            $cekJalurPerbatasan = CekData::getInfoJalur(5);
            if($cekJalurPerbatasan['input_nilai']==1){
                $this->db->select('register.*,nilai.*,jalur_ppdb.ppdb,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=5');
                $this->db->order_by('register.jarak ASC,register.usia DESC,nilai.totalSkor DESC');
            }else{
                $this->db->select('register.*,jalur_ppdb.ppdb,jalur_ppdb.*');
                $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,register.usia DESC');
            }
          
            $hasil = $this->db->get()->result_array();
            $no = 1;
            $data = [];
            foreach ($hasil as $rank) {
                $row['peringkat'] = $no++;
                $row['koreg'] = $rank['koreg'];
                $row['nama'] =  $rank['nama'];
                if ($cekJalurPerbatasan['input_nilai'] == 1) {
                    $row['total'] = $rank['total'];
                    $row['totalSkor'] = $rank['totalSkor'];
                    $row['rank'] = $rank['rank'];
                    $row['skorRank'] = $rank['skorRank'];
                }
                $row['jarak'] = $rank['jarak'];
                $row['usia'] = $rank['usia'];
                $row['ppdb'] = $rank['ppdb'];
                $row['quota'] = $rank['quota'];
                $row['noDaftar'] = $rank['noDaftar'];
                $row['sekolah'] = $rank['sekolahAsal'];
                $row['statusDaftar'] = $rank['statusDaftar'];
                // $row['totalDaftar'] = $rank['totalDaftar'];
                //   $row['quota'] = $rank['quota'];
                $data[$rank['koreg']] = $row;
            }
            return ($data[$key]);
            exit;
        }
        // prestasi rerata nilai raport
        else if ($dataUser['id_jalur'] == 6) {
            $cekJalurNilai=CekData::getInfoJalur(6);
            if($cekJalurNilai['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=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.*,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=6');
                $this->db->order_by('register.jarak ASC,register.usia DESC');
            }
            $hasil = $this->db->get()->result_array();
            $no = 1;
            $data = [];
            foreach ($hasil as $rank) {
                $row['peringkat'] = $no++;
                $row['koreg'] = $rank['koreg'];
                $row['nama'] =  $rank['nama'];
                if ($cekJalurNilai['input_nilai'] == 1) {
                    $row['total'] = $rank['total'];
                    $row['totalSkor'] = $rank['totalSkor'];
                    $row['rank'] = $rank['rank'];
                    $row['skorRank'] = $rank['skorRank'];
                }
                $row['jarak'] = $rank['jarak'];
                $row['usia'] = $rank['usia'];
                $row['ppdb'] = $rank['ppdb'];
                $row['quota'] = $rank['quota'];
                $row['noDaftar'] = $rank['noDaftar'];
                $row['sekolah'] = $rank['sekolahAsal'];
                $row['statusDaftar'] = $rank['statusDaftar'];
                $data[$rank['koreg']] = $row;
            }
            return ($data[$key]);
            exit;
        }
    }


    // cek pendaftran belum verifikasi
    public function pendaftarBelumVerifikasi()
    {
        $dataUser = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->row_array();
        $jalur = $dataUser['id_jalur'];
        $id = $dataUser['id'];
        // jalur afirmasi
        if ($jalur == 2) {
            $this->db->select('register.*,ppdb,afirmasi.*,nama_jenis_afirmasi');
            $this->db->from('register');
            $this->db->join('jalur_ppdb', 'jalur_ppdb.id = register.id_jalur');
            $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();
        }
        // jalur kejuaraan
        else if ($jalur == 4) {
            $this->db->select('register.*,jalur_ppdb.*,prestasi.*,kat_satuan.satuan,kategori_penyelengara.peyelengara');
            $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.id=' . $id);
            $hasil = $this->db->get()->row_array();
        } 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.id=' . $id);
            $hasil = $this->db->get()->row_array();
        }

        return $hasil;
    }


    // data sttistik perjalur
    // catatan yang sudah verifikasi
    public function statistikJarakPerjalaur($jalur)
    {
        $this->db->select(
            '
        (SELECT max(register.jarak) FROM register WHERE register.statusDaftar=3 AND register.id_jalur=' . $jalur . ') AS jarakTerjauh,
        (SELECT min(register.jarak) FROM register WHERE register.statusDaftar=3 AND register.id_jalur=' . $jalur . ') AS jarakTerdekat
      ',
            FALSE
        );

        $query = $this->db->get('register')->row_array();
        return $query;
    }
    public function statistikUsiaPerjalaur($jalur)
    {
        $this->db->select(
            '
        (SELECT max(register.usia) FROM register WHERE register.statusDaftar=3 AND register.id_jalur=' . $jalur . ') AS usiaTertua,
        (SELECT min(register.usia) FROM register WHERE register.statusDaftar=3 AND register.id_jalur=' . $jalur . ') AS usiaTemuda
      ',
            FALSE
        );

        $query = $this->db->get('register')->row_array();
        return $query;
    }

    public function statistiknilaiPerjalaur($jalur)
    {
        $this->db->select_max('totalSkor', 'nilaiTerbesar');
        $this->db->select_min('totalSkor', 'nilaiTerkecil');
        $this->db->join('nilai', 'nilai.id_pendaftar = register.id');
        $this->db->where('register.id_jalur=' . $jalur);
        $this->db->where('register.statusDaftar=3');
        $query = $this->db->get('register')->row_array();
        return $query;
    }


    public function statistikKejuaraan()
    {
        $this->db->select_max('total_skor_kejuaraan', 'skorTerbesar');
        $this->db->select_min('total_skor_kejuaraan', 'skorTerkecil');
        $this->db->join('kejuaraansiswa', 'kejuaraansiswa.id_pendaftar = register.id');
        $this->db->where('register.id_jalur=4');
        $this->db->where('register.statusDaftar=3');
        $query = $this->db->get('register')->row_array();
        return $query;
    }

    public function statistikPendaftar()
    {
        $data = [
            'pendaftar' => $this->_jumlahPendaftar(),
            'terverivikasi' => $this->_terverifikasi(),
            'belumTerverifikasi' => $this->belumTerverifikasi(),
        ];

        return $data;
    }

    private function _jumlahPendaftar()
    {
        $user = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->row_array();

        return $this->db->get_where('register', ['id_jalur' => $user['id_jalur']])->num_rows();
    }
    private function _terverifikasi()
    {
        $user = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->row_array();

        return $this->db->get_where('register', ['id_jalur' => $user['id_jalur'], 'statusDaftar' => 3])->num_rows();
    }
    private function belumTerverifikasi()
    {
        $user = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->row_array();

        return $this->db->get_where('register', ['id_jalur' => $user['id_jalur'], 'statusDaftar' => 2])->num_rows();
    }

    private function _secorPrestasiTerbesar()
    {
        $this->db->select('*');
        $this->db->from('register');
        $this->db->join('kejuaraansiswa', 'kejuaraansiswa.id_kejuaraan = register.id');
        $this->db->select_max('total_skor_kejuaraan');
        // $query = $this->db->get();
        $query = $this->db->get()->row_array();
        return $query;
    }
    private function _secorPrestasiTerkecil()
    {
        $this->db->select('register.id, kejuaraansiswa.total_skor_kejuaraan');
        $this->db->from('register');
        $this->db->join('(select max(total_skor_kejuaraan) max_id,kejuaraansiswa.id_pendaftar=register.id)');
        $query = $this->db->get()->row_array();
        return $query;
    }
    // end statistik skor perjalur

    private function _pendaftarTerverifikasi($jalur)
    {
        return $this->db->get_where('register', ['statusDaftar' => 3, 'id_jalur' => $jalur])->num_rows();
    }
    private function _pendaftarBelumTerverifikasi($jalur)
    {
        return $this->db->get_where('register', ['statusDaftar' => 2, 'id_jalur' => $jalur])->num_rows();
    }
    private function _totalPendaftar($jalur)
    {
        return $this->db->get_where('register', ['id_jalur' => $jalur])->num_rows();
    }

    // get data alamat
    public function getAlamat()
    {
        $user = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->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;
    }

    public function getDataUserPendaftar()
    {
      
        
    $getDataUserLogin=$this->db->get_where('register',['email'=>$this->session->userdata('pin')])->row_array();
        $getDataUserLogin = isset($getDataUserLogin) ? $getDataUserLogin : false;
      $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=' . $getDataUserLogin['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' => $getDataUserLogin['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'=>$getDataUserLogin['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 = [
        'dataUser'=>$user,
        'dataKejuaraan'=>$dataKejuaraan,
        'dataAfirmasi'=>$dataAfirmasi,
        'dataNilai'=>$dataNilai,
        'dataSekolah'=>$dataSekolah,
        'dataJalur'=>$jalurPpdb,
        'dataSyarat'=>$dataSyarat
      ];
      return $hasil;
    }



    public function prosesBiodata($data)
    {
        
        $user = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->row_array();
        $data['update_time'] = CekData::dateNow();
        $this->db->set($data);
        $this->db->where('id', $user['id']);
        $this->db->update('register');
        return $this->db->affected_rows();
    }

    public function simpanWali($data)
    {
        $wali = $this->db->get_where('wali', ['idRegister' => $data['idRegister']])->row_array();
        $data['update_time'] = CekData::dateNow();
        if ($wali) {
            $user = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->row_array();
            $this->db->set($data);
            $this->db->where('idRegister', $user['id']);
            $this->db->update('wali');
        } else {
            $this->db->insert('wali', $data);
        }
        return $this->db->affected_rows();
    }

    public function simpanNilai($data)
    {
        $wali = $this->db->get_where('nilai', ['id_pendaftar' => $data['id_pendaftar']])->row_array();
        if ($wali) {
            $user = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->row_array();
            $this->db->set($data);
            $this->db->where('id_pendaftar', $user['id']);
            $this->db->update('nilai');
        } else {
            $this->db->insert('nilai', $data);
        }
        return $this->db->affected_rows();
    }

    public function biodata()
    {
        $user = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->row_array();
        $id = $user['id'];
        $this->db->select('tempatLahir,tglLahir,jk,alamat,tinggiBadan,beratBadan,noHp,sekolahAsal,npsn,rt,rw,kodePos,longitude,kk');
        $this->db->from('register');
        $this->db->where('register.id', $id);
        $data['user'] = $this->db->get()->row_array();
        $data['provinsi'] = $this->getProv($user['provinsi']);
        $data['kota'] = $this->getKota($user['kota']);
        $data['kecamatan'] = $this->getKecamatan($user['kecamatan']);
        $data['desa'] = $this->getDesa($user['desa']);
        return $data;
    }

    private function getProv($id)
    {
        return $this->db->get_where('provinces', ['id' => $id])->row_array();
    }
    private function getKota($id)
    {
        return $this->db->get_where('regencies', ['id' => $id])->row_array();
    }
    private function getKecamatan($id)
    {
        return $this->db->get_where('districts', ['id' => $id])->row_array();
    }
    private function getDesa($id)
    {
        return $this->db->get_where('villages', ['id' => $id])->row_array();
    }
    public function getProvinsi()
    {
        return $this->db->get('provinsi');
    }

    // ambil data jalur yang aktif
    public function getJalur()
    {
        return $this->db->get_where('jalur_ppdb', ['statusAktif' => 1]);
    }

    public function getJalurWil()
    {
        return $this->db->get_where('jalur_ppdb', ['untuk' => 2, 'statusAktif' => 1]);
    }

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

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

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

    // ambil data jenis afirmasi
    public function jenisAfirmasi()
    {
        return $this->db->get('jenis_afirmasi');
    }

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

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

    // simpan data kejuaraan
    public function simpanJalurKhusus($data, $tabel)
    {
        $this->db->insert($tabel, $data);
        return $this->db->affected_rows();
    }


    // edit berkas kejuaraan
    public function editJalurKhusus($data, $tabel)
    {
        $user = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->row_array();
        $this->db->set($data);
        $this->db->where('id_pendaftar', $user['id']);
        $this->db->update($tabel);
        return $this->db->affected_rows();
    }

    // simpan berkas umum
    public function simpanBerkasUmum($data)
    {
        $this->db->insert('berkasumum', $data);
        return $this->db->affected_rows();
    }

    // update data user setelah selesai pendaftaran
    public function updateUser($jalur)
    {
        $data = [
            'id_jalur' => $jalur,
            'statusDaftar' => 2
        ];
        $user = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->row_array();
        $this->db->set($data);
        $this->db->where('id', $user['id']);
        $this->db->update('register');
    }

    // hitung usia
    public function hitungUsia($tanggal)
    {
        $rubahTanggal = date('y-m-d', strtotime($tanggal));
        // 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);
    }

    //   GET DATA UNTUK PRINT BUKI PENDAFTARAN
    public function getdataprintByjalur()
    {
        $user = $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->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.*,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;
        }
    }

    public function prosesGetDataLongByUser()
    {
        return $this->db->get_where('register', ['email' => $this->session->userdata('pin')])->row_array;
    }
}
