<?php
use FFI\CData;
use PhpOffice\PhpSpreadsheet\Calculation\Information\Value;
defined('BASEPATH') or exit('No direct script access allowed');
class Operator_m extends CI_Model
{
    // start datatables
    var $column_order = array(null, 'nisn', 'recordNilaiNama', 'total'); //set column field database for datatable orderable
    var $column_search = array('nisn', 'recordNilaiNama', 'total'); //set column field database for datatable searchable
    var $order = array('total' => 'DESC'); // default 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();
    }

    private function _get_datatables_query()
    {
        $operator = $this->db->get_where('user', ['email' => $this->session->userdata('email')])->row_array();
        $this->db->select('nilai.total,nilai.recordNilaiNama as nama, nilai.nisn,nilai.id,nilai.total');
        $this->db->from('nilai');
        $this->db->where('nilai.noOps', $operator['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)]);
        }
    }

    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

    // proses impport nilai raport
    public function prosesUploadNilai($data)
    {

        $numrow = 0;
        $namaNisnDuplikast = [];
        $jumlahUpload = 0;
        $nisnKosong = [];
        $nisnLebih = [];
        // PAI
        $paiK4S1 = "";
        $paiK4S2 = "";
        $paiK5S1 = "";
        $paiK5S2 = "";
        $paiK6S1 = "";
        // PKN
        $pknK4S1 = "";
        $pknK4S2 = "";
        $pknK5S1 = "";
        $pknK5S2 = "";
        $pknK6S1 = "";
        // Indo
        $indoK4S1 = "";
        $indoK4S2 = "";
        $indoK5S1 = "";
        $indoK5S2 = "";
        $indoK6S1 = "";
        // mtk
        $mtkK4S1 = "";
        $mtkK4S2 = "";
        $mtkK5S1 = "";
        $mtkK5S2 = "";
        $mtkK6S1 = "";
        // ipa
        $ipaK4S1 = "";
        $ipaK4S2 = "";
        $ipaK5S1 = "";
        $ipaK5S2 = "";
        $ipaK6S1 = "";
        // ips
        $ipsK4S1 = "";
        $ipsK4S2 = "";
        $ipsK5S1 = "";
        $ipsK5S2 = "";
        $ipsK6S1 = "";
        // sbdp
        $sbdpK4S1 = "";
        $sbdpK4S2 = "";
        $sbdpK5S1 = "";
        $sbdpK5S2 = "";
        $sbdpK6S1 = "";
        // pjok
        $pjokK4S1 = "";
        $pjokK4S2 = "";
        $pjokK5S1 = "";
        $pjokK5S2 = "";
        $pjokK6S1 = "";
        // sunda
        $sundaK4S1 = "";
        $sundaK4S2 = "";
        $sundaK5S1 = "";
        $sundaK5S2 = "";
        $sundaK6S1 = "";

        $operator = $this->db->get_where('user', ['email' => $this->session->userdata('email')])->row_array();
        foreach ($data as $value) {
            // if agar memulai dari baris ke 4
            if ($numrow > 1) {
                $cekNISN = $this->operator->cekNISN($value['B'], 'nilai');
                if ($cekNISN > 0) {
                    $row = $value['C'];
                    $namaNisnDuplikast[] = $row;
                    continue;
                } else if (strlen($value['B']) > 10) {
                    $row = $value['B'];
                    $nisnLebih[] = $row;
                    continue;
                } else {
                    // jika nisn aman lanjutkan ke pengecekan data yang kosong
                    // cek data Kosong
                    if ($value['B'] == "") {
                        $row = "nisn " . $value['C'] . "Kosong";
                        $nisnKosong[] = $row;
                        continue;
                    } else {
                        // pripare varibale
                        // PAI
                        $paiK4S1 = isset($value['D']) ? $value['D'] : 0;
                        $paiK4S2 = isset($value['M']) ? $value['M'] : 0;
                        $paiK5S1 = isset($value['V']) ? $value['V'] : 0;
                        $paiK5S2 = isset($value['AE']) ? $value['AE'] : 0;
                        $paiK6S1 = isset($value['AN']) ? $value['AN'] : 0;
                        // PKN
                        $pknK4S1 = isset($value['E']) ? $value['E'] : 0;
                        $pknK4S2 = isset($value['N']) ? $value['N'] : 0;
                        $pknK5S1 = isset($value['W']) ? $value['W'] : 0;
                        $pknK5S2 = isset($value['AF']) ? $value['AF'] : 0;
                        $pknK6S1 = isset($value['AO']) ? $value['AO'] : 0;
                        // Indo
                        $indoK4S1 = isset($value['F']) ? $value['F'] : 0;
                        $indoK4S2 = isset($value['O']) ? $value['O'] : 0;
                        $indoK5S1 = isset($value['X']) ? $value['X'] : 0;
                        $indoK5S2 = isset($value['AG']) ? $value['AG'] : 0;
                        $indoK6S1 = isset($value['AP']) ? $value['AP'] : 0;
                        // mtk
                        $mtkK4S1 = isset($value['G']) ? $value['G'] : 0;
                        $mtkK4S2 = isset($value['P']) ? $value['P'] : 0;
                        $mtkK5S1 = isset($value['Y']) ? $value['Y'] : 0;
                        $mtkK5S2 = isset($value['AH']) ? $value['AH'] : 0;
                        $mtkK6S1 = isset($value['AQ']) ? $value['AQ'] : 0;
                        // ipa
                        $ipaK4S1 = isset($value['H']) ? $value['H'] : 0;
                        $ipaK4S2 = isset($value['Q']) ? $value['Q'] : 0;
                        $ipaK5S1 = isset($value['Z']) ? $value['Z'] : 0;
                        $ipaK5S2 = isset($value['AI']) ? $value['AI'] : 0;
                        $ipaK6S1 = isset($value['AR']) ? $value['AR'] : 0;
                        // ips
                        $ipsK4S1 = isset($value['I']) ? $value['I'] : 0;
                        $ipsK4S2 = isset($value['R']) ? $value['R'] : 0;
                        $ipsK5S1 = isset($value['AA']) ? $value['AA'] : 0;
                        $ipsK5S2 = isset($value['AJ']) ? $value['AJ'] : 0;
                        $ipsK6S1 = isset($value['AS']) ? $value['AS'] : 0;
                        // sbdp
                        $sbdpK4S1 = isset($value['J']) ? $value['J'] : 0;
                        $sbdpK4S2 = isset($value['S']) ? $value['S'] : 0;
                        $sbdpK5S1 = isset($value['AB']) ? $value['AB'] : 0;
                        $sbdpK5S2 = isset($value['AK']) ? $value['AK'] : 0;
                        $sbdpK6S1 = isset($value['AT']) ? $value['AT'] : 0;
                        // pjok
                        $pjokK4S1 = isset($value['K']) ? $value['K'] : 0;
                        $pjokK4S2 = isset($value['T']) ? $value['T'] : 0;
                        $pjokK5S1 = isset($value['AC']) ? $value['AC'] : 0;
                        $pjokK5S2 = isset($value['AL']) ? $value['AL'] : 0;
                        $pjokK6S1 = isset($value['AU']) ? $value['AU'] : 0;
                        // sunda
                        $sundaK4S1 = isset($value['L']) ? $value['L'] : 0;
                        $sundaK4S2 = isset($value['U']) ? $value['U'] : 0;
                        $sundaK5S1 = isset($value['AD']) ? $value['AD'] : 0;
                        $sundaK5S2 = isset($value['AM']) ? $value['AM'] : 0;
                        $sundaK6S1 = isset($value['AV']) ? $value['AV'] : 0;

                        $totalPai = round(($paiK4S1 + $paiK4S2 + $paiK5S1 + $paiK5S2 + $paiK6S1) / 5, 2);
                        $totalPkn = round(($pknK4S1 + $pknK4S2 + $pknK5S1 + $pknK5S2 + $pknK6S1) / 5, 2);
                        $totalIndo = round(($indoK4S1 + $indoK4S2 + $indoK5S1 + $indoK5S2  + $indoK6S1) / 5, 2);
                        $totalMtk = round(($mtkK4S1 +  $mtkK4S2 + $mtkK5S1 + $mtkK5S2 + $mtkK6S1) / 5, 2);
                        $totalIpa = round(($ipaK4S1 + $ipaK4S2 + $ipaK5S1 + $ipaK5S2 + $ipaK6S1) / 5, 2);
                        $totalIps = round(($ipsK4S1 + $ipsK4S2 + $ipsK5S1 + $ipsK5S2 + $ipsK6S1) / 5, 2);
                        $totalSbdp = round(($sbdpK4S1 + $sbdpK4S2 + $sbdpK5S1 + $sbdpK5S2  + $sbdpK6S1) / 5, 2);
                        $totalPjok = round(($pjokK4S1 + $pjokK4S2 +  $pjokK5S1 +  $pjokK5S2 +  $pjokK6S1) / 5, 2);
                        $totalSunda = round(($sundaK4S1 + $sundaK4S2 + $sundaK5S1 +  $sundaK5S2 + $sundaK6S1) / 5, 2);
                        $total = $totalPai + $totalPkn + $totalIndo + $totalMtk + $totalIpa + $totalIps + $totalSbdp + $totalPjok + $totalSunda;

                        // prepare data upload

                        // data siap simpan atau update
                        $data = [
                            'nisn' => $value['B'],
                            'noOps' => $operator['id'],
                            'recordNilaiNama' => $value['C'],
                            'paiK4S1' => $paiK4S1,
                            'paiK4S2' => $paiK4S2,
                            'paiK5S1' => $paiK5S1,
                            'paiK5S2' => $paiK5S2,
                            'paiK6S1' => $paiK6S1,

                            'pknK4S1' => $pknK4S1,
                            'pknK4S2' => $pknK4S2,
                            'pknK5S1' => $pknK5S1,
                            'pknK5S2' => $pknK5S2,
                            'pknK6S1' => $pknK6S1,

                            'indoK4S1' => $indoK4S1,
                            'indoK4S2' => $indoK4S2,
                            'indoK5S1' => $indoK5S1,
                            'indoK5S2' => $indoK5S2,
                            'indoK6S1' => $indoK6S1,

                            'mtkK4S1' => $mtkK4S1,
                            'mtkK4S2' => $mtkK4S2,
                            'mtkK5S1' => $mtkK5S1,
                            'mtkK5S2' => $mtkK5S2,
                            'mtkK6S1' => $mtkK6S1,

                            'ipaK4S1' => $ipaK4S1,
                            'ipaK4S2' => $ipaK4S2,
                            'ipaK5S1' => $ipaK5S1,
                            'ipaK5S2' => $ipaK5S2,
                            'ipaK6S1' => $ipaK6S1,

                            'ipsK4S1' => $ipsK4S1,
                            'ipsK4S2' => $ipsK4S2,
                            'ipsK5S1' => $ipsK5S1,
                            'ipsK5S2' => $ipsK5S2,
                            'ipsK6S1' => $ipsK6S1,

                            'sbdpK4S1' => $sbdpK4S1,
                            'sbdpK4S2' => $sbdpK4S2,
                            'sbdpK5S1' => $sbdpK5S1,
                            'sbdpK5S2' => $sbdpK5S2,
                            'sbdpK6S1' => $sbdpK6S1,

                            'pjokK4S1' => $pjokK4S1,
                            'pjokK4S2' => $pjokK4S2,
                            'pjokK5S1' => $pjokK5S1,
                            'pjokK5S2' => $pjokK5S2,
                            'pjokK6S1' => $pjokK6S1,

                            'sundaK4S1' => $sundaK4S1,
                            'sundaK4S2' => $sundaK4S2,
                            'sundaK5S1' => $sundaK5S1,
                            'sundaK5S2' => $sundaK5S2,
                            'sundaK6S1' => $sundaK6S1,
                            'total' => $total
                        ];
                        $this->db->insert('nilai', $data);
                        $jumlahUpload++;
                    }
                }
            }
            // akhir dari loop
            $numrow++;
        }

        $hasilUpload = [
            'jumlahUpload' => $jumlahUpload,
            'namaNisnDuplikast' => implode(" | ", $namaNisnDuplikast),
            'nisnKosong' => implode(" | ", $nisnKosong),
            'namaNisnDuplikast' => implode(" | ", $namaNisnDuplikast),
            'nisnLebih' => implode(" | ", $nisnLebih),
        ];

        return $hasilUpload;
    }

    // cek data NISN
    public function cekNISN($nisn, $table = 'register')
    {
        // cek dari table register

        $cek = $this->db->get_where($table, ['nisn' => $nisn])->num_rows();
        return $cek;
    }

    // cek email ganda
    public function cekEmail($email)
    {

        $cek = $this->db->get_where('register', ['email' => trim($email)])->num_rows();
        return $cek;
    }

    // cekStatusDaftar
    private function cekStatusDaftar($status, $nisn, $ops)
    {
        return $this->db->get_where('register', ['noOps' => $ops, 'nisn' =>  $nisn, 'statusDaftar' => $status])->num_rows();
    }

    // proses upload Biodata
    public function prosesUploadBiodata($data)
    {
        $numrow = 0;
        $nisnKosong = 0;
        $sudahVerifikasi = 0;
        $sudahDaftar = 0;
        $emailDuplikat = 0;
        $emailKosong = 0;
        $namaNisnDuplikast = [];
        $nisnLebih = [];
        $upload = 0;

        $operator = $this->db->get_where('user', ['email' => $this->session->userdata('email')])->row_array();
        // melakukan perulagan
        foreach ($data as $value) {
            // pilih baris yang ada datanya
            if ($numrow > 1) {
                $karakter = '123456789';
                $pin = substr(str_shuffle($karakter), 0, 5);
                $cekVerifikasi = $this->cekStatusDaftar(3, $value['B'], $operator['id']);
                $cekSudahDaftar = $this->cekStatusDaftar(2, $value['B'], $operator['id']);
                $cekEmail = $this->cekEmail($value['R']);
                $cekNISDataWali = $this->cekNISN($value['B'], 'wali');
                // cek nisn
                $cekNISN = $this->cekNISN($value['B'], 'register');
                if ($cekNISN > 0) {
                    $row = $value['C'];
                    $namaNisnDuplikast[] = $row;
                    continue;
                    // jika email Duplikat
                } else if ($cekEmail > 0) {
                    $emailDuplikat++;
                    continue;
                    // jika Email Tidak di isi
                } else if ($value['B'] == null || $value['B'] == '' || $value['B'] == "") {
                    $nisnKosong++;
                    continue;
                    // jika sudah diverifikasi
                } else if ($cekVerifikasi > 0) {
                    $sudahVerifikasi;
                    continue;
                    // jika sudah di di dafatarkan
                } else if ($cekSudahDaftar > 0) {
                    $sudahDaftar++;
                    continue;
                    // jika data Aman
                } else if (strlen($value['B']) > 10) {
                    $row = $value['B'];
                    $nisnLebih[] = $row;
                    continue;
                } else {

                    // prepare data untuk insert table register
                    $dataSiswa = [
                        'nama' =>  isset($value['C']) ? $value['C'] : "0",
                        'tempatLahir' => isset($value['D']) ? $value['D'] : "none",
                        'noOps' => $operator['id'],
                        'jk' => strtoupper(isset($value['E']) ? $value['E'] : "L"),
                        'agama' => isset($value['F']) ? $value['F'] : "Islam",
                        'anak_ke' => isset($value['G']) ? $value['G'] : "1",
                        'alamat' => isset($value['K']) ? $value['K'] : "none",
                        'tinggiBadan' => isset($value['I']) ? $value['I'] : "0",
                        'beratBadan' => isset($value['J']) ? $value['J'] : "0",
                        'noHp' => isset($value['H']) ? $value['H'] : "0",
                        'sekolahAsal' => $operator['asal_sekolah_ops'],
                        'npsn' => $operator['npsnOps'],
                        'rt' =>  isset($value['L']) ? $value['L'] : "0",
                        'rw' =>  isset($value['M']) ? $value['M'] : "0",
                        'longitude' => $value['P'],
                        'nisn' => $value['B'],
                        'kodePos' => isset($value['N']) ? $value['N'] : "000",
                        'nik' => isset($value['O']) ? $value['O'] : "000",
                        'kk' => isset($value['Q']) ? $value['Q'] : "327",
                        'email' =>  isset($value['R']) ? $value['R'] : "default" . $pin . "@ppdb.com",
                        'pin' =>  password_hash(($pin), PASSWORD_DEFAULT),
                        'roles_user' => 4,
                        'isAktif' => 1,
                        'statusDaftar' => 1,
                        'panto' =>  $pin,
                    ];
                    $this->db->insert('register', $dataSiswa);
                    $upload++;

                    // prepare untuk data ke table wali
                    $dataWali = [
                        'nisn' => $value['B'],
                        'noOps' =>  $operator['id'],
                        'ayah' => isset($value['T']) ? $value['T'] : "-",
                        'ibu' =>  isset($value['AA']) ? $value['AA'] : "-",
                        'nikAyah' =>  isset($value['U']) ? $value['U'] : "327",
                        'nikIbu' =>  isset($value['AB']) ? $value['AB'] : "327",
                        'TanggalLahirAyah' => isset($value['V']) ? $value['V'] : "00-00-000",
                        'TanggalLahirIbu' => isset($value['AC']) ? $value['AC'] : "00-00-000",
                        'pendidikanAyah' => isset($value['W']) ? $value['W'] : "None",
                        'pendidikanIbu' =>  isset($value['AD']) ? $value['AD'] : "None",
                        'pekerjaanAyah' => isset($value['X']) ? $value['X'] : "None",
                        'pekerjaanIbu' => isset($value['AE']) ? $value['AE'] : "None",
                        'penghasilanAyah' => isset($value['Y']) ? $value['Y'] : "Kurang Dari 1 jt",
                        'penghasilanIbu' => isset($value['AF']) ? $value['AF'] : "Kurang dari 1 jt",
                        'notelepon' =>  isset($value['AG']) ? $value['AG'] : "08",
                        'status' => 1,
                    ];

                    // cek nisn di table wali 
                    if ($cekNISDataWali > 0) {
                        // jika ada update datanya
                        unset($dataWali['nisn']);
                        $this->db->set($dataWali);
                        $this->db->where(['nisn' => $value['B']]);
                        $this->db->update('wali');

                        // jika tidak ada tambahkan baru
                    } else {
                        $this->db->insert('wali', $dataWali);
                    }
                }
            }
            $numrow++;
        }
        if ($emailKosong > 0) {
            $emailKosong   = "Cek Kembali ada Email Yang kosong!!!";
        }

        $hasil = [
            'upload' => $upload,
            'nisnKosong' =>  $nisnKosong,
            'sudahVerifikasi' =>  $sudahVerifikasi,
            'emailDuplikat' =>  $emailDuplikat,
            'namaNisnDuplikast' => implode(" | ", $namaNisnDuplikast),
            'nisnLebih' => implode(" | ", $nisnLebih)
        ];
        return $hasil;
    }

    // ambil data berdasarkan data yang didaftarkan ops
    public function getDataPendaftarByOps()
    {
        $operator = $this->db->get_where('user', ['email' => $this->session->userdata('email')])->row_array();
        $this->db->select('nama,id,nisn,panto,statusDaftar,noOps,koreg');
        $this->db->from('register');
        $this->db->where(['noOps' => $operator['id']]);
        return $this->db->get()->result_array();
    }

    // Get Data Biodata Pendaftar kota desa kec. prov
    public function biodataAlamatSiswa($id)
    {
        $user = $this->db->get_where('register', ['id' => $id])->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;
    }

    // proses update data dari form biodata yang di operator
    public function prosesBiodata($data)
    {

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

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


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


    // function untuk menghitung jjarak=============================================
    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);
    }

    // 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));
    }
    // akhir function hitung jarak

    // proses simapan data wali
    public function simpanWali($data)
    {
        $ops = $this->db->get_where('user', ['email' => $this->session->userdata('email')])->row_array();
        $wali = $this->db->get_where('wali', ['nisn' => $this->session->userdata('nisn'), 'noOps' => $ops['id']])->num_rows();
        if ($wali > 0) {
            $user = $this->db->get_where('register', ['id' => $this->session->userdata('id_pendaftar')])->row_array();
            $this->db->set($data);
            $this->db->where('nisn', $user['nisn']);
            $this->db->update('wali');
        } else {
            $this->db->insert('wali', $data);
        }
        return $this->db->affected_rows();
    }

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

    // update untuk menambahkan koreg dan nomor urut tabele register
    public function updateRegister($data)
    {
        $update = [
            'id_jalur' => $data['id_jalur'],
            'koreg' => $data['koreg'],
            'noDaftar' => $data['noDaftar'],
            'noOps' => $data['noOps'],
            'statusDaftar' => 2,
            'tglDaftar' => date('y-m-d'),
        ];
        $this->db->set($update);
        $this->db->where(['id' => $data['id'], 'noOps' => $data['noOps']]);
        $this->db->update('register');
        return $this->db->affected_rows();
    }

    public function updateNilai($data)
    {
        $update = [
            'koreg' => $data['koreg'],
            'recordNilaiNama' => $data['nama'],
        ];
        $this->db->set($update);
        $this->db->where(['id_pendaftar' => $data['id'], 'noOps' => $data['noOps']]);
        $this->db->update('nilai');
        return $this->db->affected_rows();
    }

    public function updateWali($data)
    {
        $update = [
            'koreg' => $data['koreg'],
        ];
        $this->db->set($update);
        $this->db->where(['idRegister' => $data['id'], 'noOps' => $data['noOps']]);
        $this->db->update('wali');
        return $this->db->affected_rows();
    }

    // update data pendaftar kejuaraan saat pendaftaran
    public function updatePendaftarKejuaraan($data)
    {
        $update = [
            'id_kejuaraan' => $data['id_kejuaraan'],
            'namaKejuaraan' => $data['namaKejuaraan'],
            'skor' => $data['skor'],
        ];
        $this->db->set($update);
        $this->db->where(['id_pendaftar' => $data['id_pendaftar']]);
        $this->db->update('kejuaraansiswa');
        return $this->db->affected_rows();
    }

    // simpan data pendaftar kejuaraan saat pendaftaran
    public function SimpanPendaftarKejuaraan($data)
    {
        $this->db->insert('kejuaraansiswa', $data);
        return $this->db->affected_rows();
    }

    // update data afirmasi saat daftar
    public function updateDataAfirmasiDaftar($data)
    {
        $update = [
            'afirmasi' => $data['afirmasi'],
            'nama_afirmasi' =>  $data['nama_afirmasi'],
            'no_afirmasi' =>  $data['no_afirmasi'],
            'dtks' =>  $data['dtks'],
        ];
        $this->db->set($update);
        $this->db->where(['id_pendaftar' => $data['id_pendaftar']]);
        $this->db->update('afirmasi');
        return $this->db->affected_rows();
    }

    // simpan data afimasi saat pendaftaran
    public function simpanDataAfirmasiDaftar($data)
    {
        $this->db->insert('afirmasi', $data);
        return $this->db->affected_rows();
    }

    // statistik statistik by operator
    public function getStatistikByOps()
    {
        $operator = $this->db->get_where('user', ['email' => $this->session->userdata('email')])->row_array();
        $data = [
            'terverifikasi' => $this->dataPendaftar(3, $operator['id']),
            'sudahDaftar' => $this->dataPendaftar(2, $operator['id']),
            'totalSiswa' => $this->db->get_where('register', ['noOps' => $operator['id']])->num_rows()
        ];
        return $data;
    }

    private function dataPendaftar($status, $ops)
    {
        return $this->db->get_where('register', ['statusDaftar' => $status, 'noOps' => $ops])->num_rows();
    }


    // ============================kumpulan function status pendaftran rangking per jalur
    public function statistikPendaftar()
    {
        $data = [
            'pendaftar' => $this->_jumlahPendaftar(),
            'terverivikasi' => $this->_terverifikasi(),
            'belumTerverifikasi' => $this->belumTerverifikasi(),
        ];

        return $data;
    }

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

        return $this->db->get_where('register', ['id_jalur' => $user['id_jalur']])->num_rows();
    }
    private function _terverifikasi()
    {
        $user = $this->db->get_where('register', ['id' => $this->session->userdata('idStatus')])->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', ['id' => $this->session->userdata('idStatus')])->row_array();

        return $this->db->get_where('register', ['id_jalur' => $user['id_jalur'], 'statusDaftar' => 2])->num_rows();
    }
    // cek data pendaftaran user
    public function pendaftaran()
    {
        // cek dia jalur apa
        $dataUser = $this->db->get_where('register', ['id' => $this->session->userdata('idStatus')])->row_array();
        $key = $dataUser['koreg'];

        $hasil = [];
        // if jika jalur zonasi
        if ($dataUser['id_jalur'] == 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.total DESC');
            $hasil = $this->db->get()->result_array();
            $no = 1;
            $data = [];
            foreach ($hasil as $rank) {
                $row['rank'] = $no++;
                $row['koreg'] = $rank['koreg'];
                $row['nama'] =  $rank['nama'];
                $row['total'] = $rank['total'];
                $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) {
            $this->db->select('register.*,nilai.total,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.total DESC');
            $hasil = $this->db->get()->result_array();
            $no = 1;
            $data = [];
            foreach ($hasil as $rank) {
                $row['rank'] = $no++;
                $row['koreg'] = $rank['koreg'];
                $row['nama'] =  $rank['nama'];
                $row['nilai'] = $rank['total'];
                $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) {
            $this->db->select('register.*,nilai.total,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.total DESC');
            $hasil = $this->db->get()->result_array();
            $no = 1;
            $data = [];
            foreach ($hasil as $rank) {
                $row['rank'] = $no++;
                $row['koreg'] = $rank['koreg'];
                $row['nama'] =  $rank['nama'];
                $row['total'] = $rank['total'];
                $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) {
            $this->db->select('register.*,jalur_ppdb.*,prestasi.*,kat_satuan.satuan,kategori_penyelengara.peyelengara,nilai.total,kejuaraansiswa.namaKejuaraan,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=3');
            $this->db->where('register.id_jalur=4');
            $this->db->order_by('kejuaraansiswa.skor DESC,register.usia DESC');
            $hasil = $this->db->get()->result_array();
            $no = 1;
            $data = [];
            foreach ($hasil as $rank) {
                $row['rank'] = $no++;
                $row['koreg'] = $rank['koreg'];
                $row['nama'] =  $rank['nama'];
                $row['nilai'] = $rank['total'];
                $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) {
            $this->db->select('register.*,nilai.total,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.total DESC');
            $hasil = $this->db->get()->result_array();
            $no = 1;
            $data = [];
            foreach ($hasil as $rank) {
                $row['rank'] = $no++;
                $row['koreg'] = $rank['koreg'];
                $row['nama'] =  $rank['nama'];
                $row['total'] = $rank['total'];
                $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) {
            $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.total DESC,register.jarak ASC,register.usia DESC');
            $hasil = $this->db->get()->result_array();
            $no = 1;
            $data = [];
            foreach ($hasil as $rank) {
                $row['rank'] = $no++;
                $row['koreg'] = $rank['koreg'];
                $row['nama'] =  $rank['nama'];
                $row['total'] = $rank['total'];
                $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;
        }
    }
}
