<?php
defined('BASEPATH') or exit('No direct script access allowed');
class Register21 extends CI_Model
{
  public function regiterAkun($data)
  {
    $this->db->insert('register', $data);
    return $this->db->affected_rows();
  }
  public function getInfromasi()
  {
    return $this->db->get('informasi');
  }
  public function getInfromasiByid($id)
  {
    return $this->db->get_where('informasi', ['id' => $id]);
  }

  public function getDataStatistik()
  {
    // ambil semua data pendaftran berdasrkan jalur
    $this->db->select('jalur_ppdb.ppdb as namaJalur,jalur_ppdb.statusAktif,jalur_ppdb.id,jalur_ppdb.quota');
    $this->db->from('jalur_ppdb');
    $this->db->where('jalur_ppdb.statusAktif', 1);
    $data = $this->db->get()->result_array();
    $result = [];
    foreach ($data as $value) {
      $row['nama_jalur'] = $value['namaJalur'];
      $row['quota'] = $value['quota'];
      $row['pendaftar'] = $this->_ambilDataPendaftar($value['id']);
      $row['terverifikasi'] = $this->_ambilDataPendaftar($value['id'], 3);
      $result[$value['namaJalur']] = $row;
    }
    return $result;
  }

  // ambil data pendaftaran perjaur
  private function _ambilDataPendaftar($id_jalur, $status = null)
  {
    if ($status == null) {
      // ambil semua pendaftar
      $this->db->select('register.id_jalur,statusDaftar');
      $this->db->from('register');
      $this->db->where('id_jalur', $id_jalur);
      $data = $this->db->get();
      return $data->num_rows();
    } else {
      // seleksi perjalur
      $this->db->select('register.id_jalur,statusDaftar');
      $this->db->from('register');
      $this->db->where(['id_jalur' => $id_jalur, 'statusDaftar' => $status]);
      $data = $this->db->get();
      return $data->num_rows();
    }
  }



  // ===================================blok data pendaftran 
  // public function getDataPendaftaran(){
  //   // ambil jalur siswa yang login
  //   $this->db->get
  // }
  // akhir blok data pendaftran

  //mengambil data kota berdasarkan id provinsi 
  public function getDataKotaByIdProv($id)
  {
    return $this->db->get_where('regencies', ['province_id' => $id])->result_array();
  }
  // end mengambil data kota berdasarkan Provinsi

  // mengambil data kecamatan berdasddarkan id kota
  public function getDataKecamatanByIdKota($id)
  {
    return $this->db->get_where('districts', ['regency_id' => $id])->result_array();
  }

  // mengambil data desa berdasarkan id kecamtan
  public function getDataDesaByIdKecamatan($id)
  {
    return $this->db->get_where('villages', ['district_id' => $id])->result_array();
  }

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

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

  }

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

  public function prosesUploadBerkas()
  {
    $foto = $_FILES['logo'];
    $foto = $_FILES['logo'];
    $foto = $_FILES['logo'];

    $extend = explode('/', $foto['type']);
    $ext = end($extend);
    $newName = rand(10, 100000);
    $newjadi = $newName . '.' . $ext;

    if ($foto['error'] == 4) {
      $data = [
        'nama_sekolah' => $this->input->post('nama'),
        'alamat' => $this->input->post('alamat'),
        'kecamatan' => $this->input->post('kecamatan'),
        'desa' => $this->input->post('desa'),
        'npsn' => $this->input->post('npsn'),
      ];
      $this->db->set($data);
      $this->db->where('id', $this->input->post('id'));
      $this->db->update('kop_surat');
      $this->session->set_flashdata('message', '
        <div class="alert alert-success" role="alert">
      Data Seting berhasil Ubah!!
        </div>');
      redirect('setting');
    } else {
      $config['upload_path'] = 'assets/img/logo';
      $config['allowed_types'] = 'jpg|png|gif|jpeg';
      $config['file_name'] = $newjadi;
      $this->load->library('upload', $config);


      if (!$this->upload->do_upload('logo')) {
        $this->session->set_flashdata('message', '
        <div class="alert alert-danger" role="alert">
        logo sekolah gagal di upload
        </div>');
        redirect('setting');
      } else {
        $this->upload->data('file_name');
        $data = [
          'nama_sekolah' => $this->input->post('nama'),
          'alamat' => $this->input->post('alamat'),
          'kecamatan' => $this->input->post('kecamatan'),
          'desa' => $this->input->post('desa'),
          'npsn' => $this->input->post('npsn'),
          'logo'   => $newjadi
        ];
        $this->db->set($data);
        $this->db->where('id', $this->input->post('id'));
        $this->db->update('kop_surat');
        $this->session->set_flashdata('message', '
          <div class="alert alert-success" role="alert">
        Data Seting berhasil Ubah!!
          </div>');
        redirect('setting');
      }
    }
  }


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

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

    if ($user['koreg'] == null) {
      $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;

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

      $this->db->set([
        'koreg'=>$nexNoKoreg,
        'nisn'=>$user['nisn']
      ]);
      $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'],'email'=> $this->session->userdata('pin')])->row_array();
        // try catch
        try {
          // cek jalur yang di pilih\
          if ($jalur['input_nilai'] == 1) {
            $cekDataNilai = $this->db->get_where('nilai', ['nisn' => $user['nisn'], 'id_pendaftar' => $user['id']])->num_rows();

            unset($dataNilai['jalurPpdb']);
            $dataNilai['koreg'] = $dataUserAfterUpdate['koreg'];
            $dataNilai['id_pendaftar'] = $dataUserAfterUpdate['id'];
            $dataNilai['nisn'] = $dataUserAfterUpdate['nisn'];
            $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']);
              $this->db->set($dataNilai);
              $this->db->where(['id_pendaftar' => $dataUserAfterUpdate['id']]);
              $this->db->update('nilai');

            } else {
              // insert datanya
              $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 koreg sudah ada dan gagal
      $this->load->helper('hitungnilai');
      $userRepeatUpdate = $this->db->get_where('register',['email'=> $this->session->userdata('pin')])->row_array();
      
      try {
        // cek jalur yang di pilih\
        if ($jalur['input_nilai'] == 1) {
          $cekDataNilai = $this->db->get_where('nilai', ['nisn' => $user['nisn'], 'id_pendaftar' => $user['id'], 'koreg' => $user['koreg']])->num_rows();

          unset($dataNilai['jalurPpdb']);
          $dataNilai['koreg'] = $userRepeatUpdate['koreg'];
          $dataNilai['id_pendaftar'] = $userRepeatUpdate['id'];
          $dataNilai['nisn'] = $userRepeatUpdate['nisn'];
          $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']);
            $this->db->set($dataNilai);
            $this->db->where(['id_pendaftar' => $userRepeatUpdate['id']]);
            $this->db->update('nilai');

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

        }

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

          $UpdateAfirmasiOrInsert = [
            'id_pendaftar' => $userRepeatUpdate['id'],
            'koreg' => $userRepeatUpdate['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' => $userRepeatUpdate['id'], 'koreg' => $userRepeatUpdate['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' => $userRepeatUpdate['id']]);
            $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' => $userRepeatUpdate['id'],
            'koreg' => $userRepeatUpdate['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' => $userRepeatUpdate['id'], 'koreg' => $userRepeatUpdate['koreg']])->num_rows();
          if ($cekKejuaraanSiswa > 0) {
            unset($datainputPrestasi['id_pendaftar']);
            unset($datainputPrestasi['koreg']);
            $this->db->set($datainputPrestasi);
            $this->db->where(['id_pendaftar' => $userRepeatUpdate['id'], 'koreg' => $userRepeatUpdate['koreg']]);
            $this->db->update('kejuaraansiswa');
          } else {
            $this->db->insert('kejuaraansiswa', $datainputPrestasi);
          }

        }

        $updateRegister['update_time'] = CekData::dateNow();
        $updateRegister['statusDaftar'] = 2;
        $updateRegister['id_jalur'] = $jalur['id'];
        $this->db->set($updateRegister);
        $this->db->where(['id' => $userRepeatUpdate['id']]);
        $this->db->update('register');
        return true;

      }catch(Exception $error){
        echo "terjadi error: $error";
      }

    }

  }

  public function koreg()
  {
    $today = date("Ymd");
    $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);
    return $nexNoKoreg;
  }

  public function urut()
  {
    $query = "SELECT max(noDaftar) AS last FROM register";
    $data = $this->db->query($query)->row_array();
    $lasKoreg = $data['last'];
    $nexKoreg = $lasKoreg + 1;
    return $nexKoreg;
  }

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