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

    // new urutan rangking
    public function dataStatistikPringkatPendaftar(){
        $koreg = '202306270003';
        $dataUser = [];

    //    ambil data user nya 
        $user=$this->db->get_where('register',['koreg'=>$koreg])->row_array();
    // ambil data jalur nya
        $jalur = $this->db->get_where('jalur_ppdb', ['id' => $user['id']])->row_array();

        // cek rules / aturan tiap jalur
        if($jalur['input_nilai'==1]){
            // ambil data nilai nya
        }

        if($jalur['input_afirmasi'==1]){
            // ambil data afirmasinya
        }

        if($jalur['inputPrestasi']==1){
            // ambil data Prestasinya
        }
        
    

    }

    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

   

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

    // cekNISNOREMAIL
    public function cekEmailOrNISN($kolom,$value){
        $query = "SELECT nisn,email statusDaftar FROM register where (statusDaftar=? || statusDaftar=? || statusDaftar=?) AND $kolom=?";
        return $this->db->query($query, [2,3,4,$value])->num_rows();
    }

   

    // 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)
    {
        
        $operator = $this->db->get_where('user', ['email' => $this->session->userdata('email')])->row_array();
        $dataPendaftar=CekData::getData('register',['id'=> $this->session->userdata('id_pendaftar'),'noOps'=>$operator['id']]);


        $waliCek = CekData::cekUpdateData('wali',['idRegister'=>$dataPendaftar['id'],'nisn'=>$dataPendaftar['nisn']] );

        $nilaiCek=CekData::cekUpdateData('nilai',['nisn'=>$dataPendaftar['nisn']] );
       
        if($waliCek==true){
            $this->db->set([
                'nisn'=>$data['nisn']
            ]);
            $this->db->where(['idRegister' => $dataPendaftar['id'], 'noOps' => $operator['id']]);
            $this->db->update('wali');
        }

        // cek data nilai
        if($nilaiCek==true){
            $this->db->set([
                'nisn'=>$data['nisn'],
                'id_pendaftar'=>$dataPendaftar['id']
            ]);
            $this->db->where(['nisn' => $dataPendaftar['nisn'], 'noOps' => $operator['id']]);
            $this->db->update('nilai');
        }
        // update datatanya
        $data['update_time'] = CekData::dateNow();
        $this->db->set($data);
        $this->db->where('id', $this->session->userdata('id_pendaftar'));
        $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;
    }

    
  public function simpanDataPenftaran($dataAfirmasi = null, $dataPrestasi = null, $dataNilai=null, $jalur=null)
  {

      
    // update data pendaftar
    $user = $this->db->get_where('register', ['id' => $this->session->userdata('id_pendaftar')])->row_array();
    $operator = $this->db->get_where('user', ['email' => $this->session->userdata('email')])->row_array();

    // jika user belum di daftrakan
    if ($user['koreg'] == null || $user['koreg']=="") {
      $update = [
        'id_jalur' => $jalur['id'],
        'statusDaftar' => 2,
      ];
      // start transaction
      $today = date("Ymd");

      $this->db->trans_begin();
      $query = "SELECT max(koreg) AS last FROM register WHERE koreg LIKE '$today%'";
      $data = $this->db->query($query)->row_array();

      $lasKoreg = $data['last'];
      $lastNoUrut = substr($lasKoreg, 8, 4);
      $nexKoreg = $lastNoUrut + 1;
      $nexNoKoreg = $today . sprintf('%04s', $nexKoreg);

      // query Number urut
      $query_urut = "SELECT max(noDaftar) AS last FROM register";
      $dataUrut = $this->db->query($query_urut)->row_array();
      $lasUrut = $dataUrut['last'];
      $nexUrut = $lasUrut + 1;
      $nexUrut;

      $update['koreg'] = $nexNoKoreg;
      $update['noDaftar'] = $nexUrut;
     $update['noOps'] = $operator['id'];
      $update['update_time'] = CekData::dateNow();
      $update['tglDaftar'] = date('Y-m-d');

      $this->db->set($update);
      $this->db->where(['id' => $user['id']]);
      $this->db->update('register');

      $this->db->set([
        'koreg'=>$nexNoKoreg,
        'nisn'=>$user['nisn'],
        'update_time'=>CekData::dateNow()
      ]);
      $this->db->where(['idRegister' => $user['id']]);
      $this->db->update('wali');
      if ($this->db->trans_status() === FALSE) {

        $this->db->trans_rollback();
        return false;
      } else {
        $this->db->trans_commit();
        $this->load->helper('hitungnilai');
        $dataUserAfterUpdate = $this->db->get_where('register',['id'=>$user['id'],'noOps'=>$operator['id']])->row_array();
        // try catch
        try {
          // cek jalur yang di pilih\
          if ($jalur['input_nilai'] == 1) {
            $cekDataNilai = $this->db->get_where('nilai', ['nisn' => $dataUserAfterUpdate['nisn'], 'id_pendaftar' => $dataUserAfterUpdate['id']])->num_rows();
            unset($dataNilai['jalurPpdb']);
            $dataNilai['koreg'] = $dataUserAfterUpdate['koreg'];
            $dataNilai['id_pendaftar'] = $dataUserAfterUpdate['id'];
            $dataNilai['nisn'] = $dataUserAfterUpdate['nisn'];
            $dataNilai['noOps']=$operator['id'];
            $totalPAI = MyHitungNilai::sumNilai($dataNilai['paiK4S1'], $dataNilai['paiK4S2'], $dataNilai['paiK5S1'], $dataNilai['paiK5S2'], $dataNilai['paiK6S1']);
            $totalPKN = MyHitungNilai::sumNilai($dataNilai['pknK4S1'], $dataNilai['pknK4S2'], $dataNilai['pknK5S1'], $dataNilai['pknK5S2'], $dataNilai['pknK6S1']);
            $totalIndo = MyHitungNilai::sumNilai($dataNilai['indoK4S1'], $dataNilai['indoK4S2'], $dataNilai['indoK5S1'], $dataNilai['indoK5S2'], $dataNilai['indoK6S1']);
            $totalMtk = MyHitungNilai::sumNilai($dataNilai['mtkK4S1'], $dataNilai['mtkK4S2'], $dataNilai['mtkK5S1'], $dataNilai['mtkK5S2'], $dataNilai['mtkK6S1']);
            $totalIpa = MyHitungNilai::sumNilai($dataNilai['ipaK4S1'], $dataNilai['ipaK4S2'], $dataNilai['ipaK5S1'], $dataNilai['ipaK5S2'], $dataNilai['ipaK6S1']);
            $totalIps = MyHitungNilai::sumNilai($dataNilai['ipsK4S1'], $dataNilai['ipsK4S2'], $dataNilai['ipsK5S1'], $dataNilai['ipsK5S2'], $dataNilai['ipsK6S1']);
            $totalSbdp = MyHitungNilai::sumNilai($dataNilai['sbdpK4S1'], $dataNilai['sbdpK4S2'], $dataNilai['sbdpK5S1'], $dataNilai['sbdpK5S2'], $dataNilai['sbdpK6S1']);
            $totalPjok = MyHitungNilai::sumNilai($dataNilai['pjokK4S1'], $dataNilai['pjokK4S2'], $dataNilai['pjokK5S1'], $dataNilai['pjokK5S2'], $dataNilai['pjokK6S1']);
            $totalSunda = MyHitungNilai::sumNilai($dataNilai['sundaK4S1'], $dataNilai['sundaK4S2'], $dataNilai['sundaK5S1'], $dataNilai['sundaK5S2'], $dataNilai['sundaK6S1']);
            $dataNilai['total'] = MyHitungNilai::sumRataRata($totalPAI, $totalPKN, $totalIndo, $totalMtk, $totalIpa, $totalIps, $totalSbdp, $totalPjok, $totalSunda);
            $dataNilai['totalSkor'] = MyHitungNilai::sumRataRata($totalPAI, $totalPKN, $totalIndo, $totalMtk, $totalIpa, $totalIps, $totalSbdp, $totalPjok, $totalSunda);

            if ($cekDataNilai>0) {
              // upate datanya
              unset($dataNilai['id_pendaftar']);
              $dataNilai['recordNilaiNama'] = $dataUserAfterUpdate['nama'];
                            $dataNilai['koreg'] = $dataUserAfterUpdate['koreg'];
              $this->db->set($dataNilai);
              $this->db->where(['id_pendaftar' => $dataUserAfterUpdate['id']]);
              $this->db->update('nilai');

            } else {
              // insert datanya
              $dataNilai['recordNilaiNama'] = $dataUserAfterUpdate['nama'];
              $this->db->insert('nilai', $dataNilai);
            }

          }

          // cek afirmasi bukan
          if ($jalur['input_afirmasi'] == 1) {

            $UpdateAfirmasiOrInsert = [
              'id_pendaftar' => $dataUserAfterUpdate['id'],
              'koreg' => $dataUserAfterUpdate['koreg'],
              'afirmasi' => isset($dataAfirmasi['namaAfirmasi'])?$dataAfirmasi['namaAfirmasi']:'-',
              'nama_afirmasi' => isset($dataAfirmasi['namaPemilik'])?$dataAfirmasi['namaPemilik']:'-',
              'no_afirmasi' => isset($dataAfirmasi['noKartu'])?$dataAfirmasi['noKartu']:'-',
              'dtks' => isset($dataAfirmasi['dtks'])?$dataAfirmasi['dtks']:'-'

            ];

            // cek apakah sudah ada data afirmasi
            $cekDataAfirmasi = $this->db->get_where('afirmasi', ['id_pendaftar' => $dataUserAfterUpdate['id'], 'koreg' => $dataUserAfterUpdate['koreg']])->num_rows();

            // cek apakan sudah ada
            if ($cekDataAfirmasi > 0) {
              unset($UpdateAfirmasiOrInsert['id_pendaftar']);
              unset($UpdateAfirmasiOrInsert['koreg']);
              $this->db->set($UpdateAfirmasiOrInsert);
              $this->db->where(['id_pendaftar' => $dataUserAfterUpdate['id'], 'koreg' => $dataUserAfterUpdate['koreg']]);
              $this->db->update('afirmasi');
            } else {
              $this->db->insert('afirmasi', $UpdateAfirmasiOrInsert);

            }

          }

          // cek kejuaraan bukan
          if ($jalur['inputPrestasi'] == 1) {
            // ambil skore Preatasi kejuaraan
            $skoreKejuaraan = $this->db->get_where('prestasi', ['id' => $dataPrestasi['tingkat_kejuaraan']])->row_array();
            $datainputPrestasi = [
              'id_pendaftar' => $dataUserAfterUpdate['id'],
              'koreg' => $dataUserAfterUpdate['koreg'],
              'id_kejuaraan' => $dataPrestasi['tingkat_kejuaraan'],
              'namaKejuaraan' => $dataPrestasi['namaKejuaraan'],
              'skor' => $skoreKejuaraan['score'],
              'total_skor_kejuaraan' => $skoreKejuaraan['score']
            ];

            // cek kejuaran siswaw apakah sudah pernah input
            $cekKejuaraanSiswa = $this->db->get_where('kejuaraansiswa', ['id_pendaftar' => $dataUserAfterUpdate['id'], 'koreg' => $dataUserAfterUpdate['koreg']])->num_rows();
            if ($cekKejuaraanSiswa > 0) {
              unset($datainputPrestasi['id_pendaftar']);
              unset($datainputPrestasi['koreg']);
              $this->db->set($datainputPrestasi);
              $this->db->where(['id_pendaftar' => $dataUserAfterUpdate['id'], 'koreg' => $dataUserAfterUpdate['koreg']]);
              $this->db->update('kejuaraansiswa');
            } else {
              $this->db->insert('kejuaraansiswa', $datainputPrestasi);
            }

          }
        }catch(Exception $error){
          echo "terjadi error: $error";
        }
        return true;
      }
    }else{
        // Jika pendaftar melakukan perbaikan setelah di tolak tim varifikasi
        $this->load->helper('hitungnilai');
         $dataUserRepatRegister = $this->db->get_where('register',['id'=> $this->session->userdata('id_pendaftar'),'noOps'=>$operator['id']])->row_array();
         $updateUserRepeat = [
            'id_jalur' => $jalur['id'],
            'statusDaftar' => 2,
            'update_time'=>CekData::dateNow()
          ];
        //   awal database transsaction
          $this->db->trans_begin();
            try {
                $this->db->set($updateUserRepeat);
                $this->db->where(['id' => $dataUserRepatRegister['id'], 'noOps' => $operator['id']]);
                $this->db->update('register');
                   // cek jalur yang di pilih\
          if ($jalur['input_nilai'] == 1) {
            $cekDataNilai = $this->db->get_where('nilai', ['nisn' => $dataUserRepatRegister['nisn'], 'id_pendaftar' => $dataUserRepatRegister['id'], 'koreg' => $dataUserRepatRegister['koreg']])->num_rows();


            unset($dataNilai['jalurPpdb']);
            $totalPAI = MyHitungNilai::sumNilai($dataNilai['paiK4S1'], $dataNilai['paiK4S2'], $dataNilai['paiK5S1'], $dataNilai['paiK5S2'], $dataNilai['paiK6S1']);
            $totalPKN = MyHitungNilai::sumNilai($dataNilai['pknK4S1'], $dataNilai['pknK4S2'], $dataNilai['pknK5S1'], $dataNilai['pknK5S2'], $dataNilai['pknK6S1']);
            $totalIndo = MyHitungNilai::sumNilai($dataNilai['indoK4S1'], $dataNilai['indoK4S2'], $dataNilai['indoK5S1'], $dataNilai['indoK5S2'], $dataNilai['indoK6S1']);
            $totalMtk = MyHitungNilai::sumNilai($dataNilai['mtkK4S1'], $dataNilai['mtkK4S2'], $dataNilai['mtkK5S1'], $dataNilai['mtkK5S2'], $dataNilai['mtkK6S1']);
            $totalIpa = MyHitungNilai::sumNilai($dataNilai['ipaK4S1'], $dataNilai['ipaK4S2'], $dataNilai['ipaK5S1'], $dataNilai['ipaK5S2'], $dataNilai['ipaK6S1']);
            $totalIps = MyHitungNilai::sumNilai($dataNilai['ipsK4S1'], $dataNilai['ipsK4S2'], $dataNilai['ipsK5S1'], $dataNilai['ipsK5S2'], $dataNilai['ipsK6S1']);
            $totalSbdp = MyHitungNilai::sumNilai($dataNilai['sbdpK4S1'], $dataNilai['sbdpK4S2'], $dataNilai['sbdpK5S1'], $dataNilai['sbdpK5S2'], $dataNilai['sbdpK6S1']);
            $totalPjok = MyHitungNilai::sumNilai($dataNilai['pjokK4S1'], $dataNilai['pjokK4S2'], $dataNilai['pjokK5S1'], $dataNilai['pjokK5S2'], $dataNilai['pjokK6S1']);
            $totalSunda = MyHitungNilai::sumNilai($dataNilai['sundaK4S1'], $dataNilai['sundaK4S2'], $dataNilai['sundaK5S1'], $dataNilai['sundaK5S2'], $dataNilai['sundaK6S1']);
            $dataNilai['total'] = MyHitungNilai::sumRataRata($totalPAI, $totalPKN, $totalIndo, $totalMtk, $totalIpa, $totalIps, $totalSbdp, $totalPjok, $totalSunda);
            $dataNilai['totalSkor'] = MyHitungNilai::sumRataRata($totalPAI, $totalPKN, $totalIndo, $totalMtk, $totalIpa, $totalIps, $totalSbdp, $totalPjok, $totalSunda);

            if ($cekDataNilai>0) {
              // upate datanya
              $this->db->set($dataNilai);
              $this->db->where(['id_pendaftar' => $dataUserRepatRegister['id']]);
              $this->db->update('nilai');

            } else {
              // insert datanya
              $this->db->insert('nilai', $dataNilai);
            }

          }


        // cek afirmasi 
        if ($jalur['input_afirmasi'] == 1) {

                $UpdateAfirmasiOrInsert = [
                    'id_pendaftar' => $dataUserRepatRegister['id'],
                    'koreg' => $dataUserRepatRegister['koreg'],
                    'afirmasi' => isset($dataAfirmasi['namaAfirmasi'])?$dataAfirmasi['namaAfirmasi']:'-',
                    'nama_afirmasi' => isset($dataAfirmasi['namaPemilik'])?$dataAfirmasi['namaPemilik']:'-',
                    'no_afirmasi' => isset($dataAfirmasi['noKartu'])?$dataAfirmasi['noKartu']:'-',
                    'dtks' => isset($dataAfirmasi['dtks'])?$dataAfirmasi['dtks']:'-'

                ];

                // cek apakah sudah ada data afirmasi
                $cekDataAfirmasi = $this->db->get_where('afirmasi', ['id_pendaftar' => $dataUserRepatRegister['id'], 'koreg' => $dataUserRepatRegister['koreg']])->num_rows();

                // cek apakan sudah ada
                if ($cekDataAfirmasi > 0) {
                    unset($UpdateAfirmasiOrInsert['id_pendaftar']);
                    unset($UpdateAfirmasiOrInsert['koreg']);
                    $this->db->set($UpdateAfirmasiOrInsert);
                    $this->db->where(['id_pendaftar' => $dataUserRepatRegister['id'], 'koreg' => $dataUserRepatRegister['koreg']]);
                    $this->db->update('afirmasi');
                } else {
                    $this->db->insert('afirmasi', $UpdateAfirmasiOrInsert);

                }

            }

                // cek kejuaraan bukan
          if ($jalur['inputPrestasi'] == 1) {
            // ambil skore Preatasi kejuaraan
            $skoreKejuaraan = $this->db->get_where('prestasi', ['id' => $dataPrestasi['tingkat_kejuaraan']])->row_array();
            $datainputPrestasi = [
              'id_pendaftar' => $dataUserRepatRegister['id'],
              'koreg' => $dataUserRepatRegister['koreg'],
              'id_kejuaraan' => $dataPrestasi['tingkat_kejuaraan'],
              'namaKejuaraan' => $dataPrestasi['namaKejuaraan'],
              'skor' => $skoreKejuaraan['score'],
              'total_skor_kejuaraan' => $skoreKejuaraan['score']
            ];

            // cek kejuaran siswaw apakah sudah pernah input
            $cekKejuaraanSiswa = $this->db->get_where('kejuaraansiswa', ['id_pendaftar' => $dataUserRepatRegister['id'], 'koreg' => $dataUserRepatRegister['koreg']])->num_rows();
            if ($cekKejuaraanSiswa > 0) {
              unset($datainputPrestasi['id_pendaftar']);
              unset($datainputPrestasi['koreg']);
              $this->db->set($datainputPrestasi);
              $this->db->where(['id_pendaftar' => $dataUserRepatRegister['id'], 'koreg' => $dataUserRepatRegister['koreg']]);
              $this->db->update('kejuaraansiswa');
            } else {
              $this->db->insert('kejuaraansiswa', $datainputPrestasi);
            }
          }

          if ($this->db->trans_status() === FALSE) {

                $this->db->trans_rollback();
                return false;
                } else {
                    $this->db->trans_commit();
                    return true;
                }
        }catch(Exception $error){
            return $error;
        }

    }

  }


    public function getDataJalur($id){
        return $this->db->get_where('jalur_ppdb',['id'=>$id])->row_array();
      }

      public function getDataRaportByid($id){
        return CekData::getData('nilai', ['id_pendaftar' => $id]);
      }

    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();
        $user = $this->db->get_where('register', ['id' => $this->session->userdata('id_pendaftar'), 'noOps' => $ops['id']])->row_array();
        try{

            if (CekData::cekUpdateData('wali',['nisn'=>$user['nisn'],'idRegister'=>$user['id']]) ==true) {
                $data['update_time'] = CekData::dateNow();
                unset($data['idRegister']);
                $this->db->set($data);
                $this->db->where(['idRegister' =>$this->session->userdata('id_pendaftar')]);
                $this->db->update('wali');
            } else {
                $this->db->insert('wali', $data);
            }
            return $this->db->affected_rows();
        }catch(Exception $e){
            return $e;
        }
    }

    // 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) {
            $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=3');
                $this->db->where('register.id_jalur=1');
                $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=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($cekJalur['input_nilai']==1){
                    $row['total'] = $rank['totalSkor'];
                }
                $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) {
            $dataJalur = CekData::getInfoJalur(2);
            if($dataJalur['input_nilai']==1){
                $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.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'];
                if($dataJalur['input_nilai']==1){

                    $row['nilai'] = $rank['totalSkor'];
                }
                $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) {
            $cekJalurPerpindahan = CekData::getInfoJalur(3);
            if($cekJalurPerpindahan['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=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($cekJalurPerpindahan['input_nilai']==1)
                {
                    $row['total'] = $rank['totalSkor'];
                }
                $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) {
            $jalurKejuaraan = CekData::getInfoJalur(4);

            if( $jalurKejuaraan['input_nilai']==1){
                $this->db->select('register.*,jalur_ppdb.*,prestasi.*,kat_satuan.satuan,kategori_penyelengara.peyelengara,nilai.total,nilai.totalNilai,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($jalurKejuaraan['input_nilai']==1){
                    $row['nilai'] = $rank['totalSkor'];
                }
                $row['jarak'] = $rank['jarak'];
                $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) {
            $datajalurPerbatasan = CekData::getInfoJalur(5);
            if($datajalurPerbatasan['input_nilai']==1){
                $this->db->select('register.*,nilai.totalSkor,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($datajalurPerbatasan['input_nilai']==1){
                    
                    $row['totalSkor'] = $rank['totalSkor'];
                }
                $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.totalSkor DESC,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'];
                $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;
        }
    }
}
