<?php
defined('BASEPATH') or exit('No direct script access allowed');

/**
 * 
 */
class Tambah_ruta_model extends CI_Model
{

    function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    //tambahblok1
    function tambahblok1()
    {
        $this->db->trans_start();
        $id =  $this->uuid->v4();
        if ($id == 0) {
            $id =  $this->uuid->v4();
            $idZ =  str_replace("-", "", $id);
        } else {
            $idZ =  str_replace("-", "", $id);
        }
        $data = [
            'id' => $idZ,
            'provinsi' => htmlspecialchars($this->input->post('provinsi', TRUE), ENT_QUOTES),
            'kabupaten' => htmlspecialchars($this->input->post('kabupaten', TRUE), ENT_QUOTES),
            'kecamatan' => htmlspecialchars($this->input->post('kecamatan', TRUE), ENT_QUOTES),
            'desa' => htmlspecialchars($this->input->post('desa', TRUE), ENT_QUOTES),
            'alamat' => htmlspecialchars($this->input->post('alamat', TRUE), ENT_QUOTES),
            'nokk' => htmlspecialchars($this->input->post('nokk', TRUE), ENT_QUOTES),
            'nm_kpl_kg' => htmlspecialchars($this->input->post('namaKK', TRUE), ENT_QUOTES),
            'jml_agt' => htmlspecialchars($this->input->post('jmlagt', TRUE), ENT_QUOTES),
            'normh' => htmlspecialchars($this->input->post('norumah', TRUE), ENT_QUOTES),

        ];
        $this->db->insert('dc_ruta', $data);
        $tes = $_POST['jmlagt'];
        $nokk = $_POST['nokk'];
        for ($i = 1; $i <= $tes; $i++) {
            $this->db->query("INSERT INTO dc_agt_keluarga (id_agt, nokk) VALUE ('$idZ', '$nokk')");
        }
        return $this->db->trans_complete();
    }
    public function getrutablok1($nokk)
    {
        $this->db->select('*')
            ->from('dc_ruta u')
            ->where('nokk', $nokk);
        return $this->db->get()->result();
    }
    //getidruta
    public function getrutabyid($id)
    {
        $this->db->select('*')
            ->from('dc_ruta u')

            ->where('u.id', $id);
        return $this->db->get()->result();
    }
    //getidruta
    public function getruta()
    {
        $this->db->select('*')
            ->from('dc_ruta u');
        return $this->db->get()->result();
    }
    public function getagt()
    {
        $this->db->select('*')
            ->from('dc_agt_keluarga u');
        return $this->db->get()->result();
    }
    public function ubahblok1($id, $provinsi, $kabupaten, $kecamatan, $desa, $alamat, $nokk, $namaKK, $jmlagt, $norumah)
    {
        $this->db->trans_start();
        // $this->db->query("UPDATE dc_agt_keluarga SET nokk='$nokk' WHERE nokk='$nokk'");
        // $this->db->update('dc_lahan', array('id_kk_fgn' => $id));

        // $this->db->delete('dc_lahan', array('id_kk_lhn' => $id));

        $this->db->query("UPDATE dc_lahan SET nokk='$nokk' WHERE id_kk_fgn='$id'");

        $this->db->delete('dc_agt_keluarga', array('id_agt' => $id));

        $this->db->query("UPDATE dc_ruta SET provinsi ='$provinsi',kabupaten ='$kabupaten', kecamatan ='$kecamatan', desa ='$desa', desa ='$desa', alamat ='$alamat', nokk ='$nokk', nm_kpl_kg ='$namaKK', jml_agt ='$jmlagt', normh ='$norumah' WHERE id='$id'");


        for ($i = 1; $i <= $jmlagt; $i++) {
            $this->db->query("INSERT INTO dc_agt_keluarga (id_agt, nokk) VALUE ('$id','$nokk')");
        }

        return $this->db->trans_complete();
    }
    public function ubahblok2($id, $tglcacah, $tglperiksa, $namapencacah, $namaperiksa, $hasil)
    {

        $edit = $this->db->query("UPDATE dc_ruta SET tglcacah ='$tglcacah',  tglperiksa = '$tglperiksa', namapencacah = '$namapencacah', namaperiksa ='$namaperiksa', hasil ='$hasil' WHERE id='$id'");
        return $edit;
    }
    public function ubahblok3($id, $status1, $status2, $r301c, $luastnh, $luas, $jenis, $jenisdinding, $jenisatap, $sumberair, $sumberpen, $daya, $jummeteran, $bahan, $pengguna, $jeniskloset, $tempatpembuangan)
    {
        if ($status2 != 1) {
            $r301c = null;
        }
        // if ($jenisdinding == 4) {
        //     $kondisidinding = null;
        // }
        // if ($jenisatap == 8 || $jenisatap == 9 || $jenisatap == 10) {
        //     $kondisiatap = null;
        // }
        if ($sumberpen != 1) {
            $daya = null;
        }
        if ($pengguna == 4) {
            $jeniskloset = null;
        }
        $edit = $this->db->query("UPDATE dc_ruta SET status1 ='$status1', status2 ='$status2', r301c='$r301c,',luas ='$luas',  luastnh ='$luastnh',jenis ='$jenis', jenisdinding='$jenisdinding', jenisatap='$jenisatap',sumberair='$sumberair', sumberpen='$sumberpen', jummeteran='$jummeteran',daya='$daya',  bahan='$bahan',pengguna='$pengguna', jeniskloset='$jeniskloset', tempatpembuangan='$tempatpembuangan' WHERE id='$id'");
        return $edit;
    }
    public function ubahblok4($id, $pekerjaan, $pendapatan)
    {
        $edit = $this->db->query("UPDATE dc_ruta SET pekerjaan='$pekerjaan', pendapatan='$pendapatan' WHERE id='$id'");
        return $edit;
    }
    public function ubahblok5($id, $nokk,  $tabung, $tabung12kg, $tabung3kg, $prgrmkur, $jnsbantuan, $jnslayanan, $komp,  $sepeda, $ac, $spdmtr,  $mbl,  $perahu, $telv, $nohp, $tv, $lhn, $buah, $rmhtmpt, $sapi,  $kerbau, $kuda, $kambing)
    {
        $this->db->trans_start();

        $this->db->delete('dc_lahan', array('id_kk_fgn' => $id));
        if ($lhn == 2) {
            $buah = null;
        }
        $this->db->query("UPDATE dc_ruta SET tabung='$tabung', komp ='$komp', prgrmkur='$prgrmkur', jnsbantuan='$jnsbantuan',jnslayanan='$jnslayanan',tabung12kg ='$tabung12kg',tabung3kg ='$tabung3kg', sepeda ='$sepeda', ac = '$ac', spdmtr ='$spdmtr', mbl='$mbl',  perahu='$perahu', telv='$telv',  nohp='$nohp', tv='$tv', lhn= '$lhn', buah='$buah', rmhtmpt='$rmhtmpt', sapi='$sapi',  kerbau ='$kerbau', kuda='$kuda', kambing='$kambing' WHERE id='$id'");

        if ($lhn != 0) {
            for ($i = 1; $i <= $buah; $i++) {
                $this->db->query("INSERT INTO dc_lahan (id_kk_fgn, nokk) VALUE ('$id','$nokk')");
            }
        }

        return $this->db->trans_complete();
    }
    //get_dc_lahan
    function get_lahanbyid($id)
    {
        $this->db->select('*')
            ->from('dc_lahan')
            ->where('id_kk_fgn', $id);

        return $this->db->get()->result();
    }
    public function countlahan($id)
    {
        $this->db->select('count(nokk) as lahan')
            ->from('dc_lahan')
            ->where('id_kk_fgn', $id);
        return $this->db->get()->result();
    }
    //get_dc_agt_klrga
    function get_agt($id)
    {
        $this->db->select('*')
            ->from('dc_agt_keluarga')
            ->where('id_agt', $id);

        return $this->db->get()->result();
    }
    public function countart($id)
    {
        $this->db->select('count(nokk) as agt')
            ->from('dc_agt_keluarga')
            ->where('id_agt', $id);
        return $this->db->get()->result();
    }
    public function ubahblok6($id, $nokk,  $namalhn, $hub, $sppt, $nopajak, $llahan, $haksertif, $nmhak)
    {
        if ($sppt != 1) {
            $nopajak = null;
            $llahan = null;
            $haksertif = null;
            $nmhak = null;
        }
        $this->db->trans_start();
        $this->db->query("UPDATE dc_lahan SET nokk='$nokk', namalhn='$namalhn', hub='$hub', sppt='$sppt', nopajak='$nopajak', llhan='$llahan', haksertif='$haksertif', nmhak='$nmhak' WHERE id_pm_lahan='$id'");
        return $this->db->trans_complete();
    }
    public function ubahblok7($id, $nokk, $nama, $nik, $hubkel, $jk, $umur, $sts, $pemilik, $kk, $ki, $hamil, $kb, $jc, $kronis, $goldar, $sekolah, $pend, $kelas, $ijazah, $pensi, $kerja, $lapangan, $statkerja, $jumpkerja, $tmptlokasi, $omsetusaha, $agm, $suku, $dmsl, $bansos)
    {
        if ($umur < 5) {
            $hamil = null;
            $kb = null;
            $pemilik = null;
            $sekolah = null;
            $pend = null;
            $kelas = null;
            $ijazah = null;
        } else if ($umur >= 10 && $umur <= 49 && $sts == 1 && $sts == 4) {
            $hamil = null;
            $kb = null;
            $pemilik = null;
        } else if ($umur >= 10 && $umur <= 49 && $sts == 3) {
            $kb = null;
            $hamil = null;
        }
        if ($kerja == 2) {
            $lapangan = null;
            $statkerja = null;
            $jumpkerja = null;
            $tmptlokasi = null;
            $omsetusaha = null;
        }
        if ($statkerja != 1 && $statkerja != 2 && $statkerja != 3) {
            $jumpkerja = null;
            $omsetusaha = null;
        }
        $edit = $this->db->query("UPDATE dc_agt_keluarga SET nokk='$nokk', nama='$nama', nik='$nik',hubkel='$hubkel', jk='$jk', umur='$umur', sts='$sts', pemilik='$pemilik', kk='$kk', ki='$ki', hamil='$hamil', kb='$kb', jc='$jc', kronis='$kronis', goldar='$goldar', sekolah='$sekolah', pend='$pend', kelas='$kelas', ijazah='$ijazah', pensi='$pensi', kerja='$kerja', lapangan='$lapangan', statkerja='$statkerja', jumpkerja='$jumpkerja', tmptlokasi='$tmptlokasi', omsetusaha='$omsetusaha', agm='$agm', suku='$suku', dmsl='$dmsl', bansos='$bansos' WHERE id_pm_agt='$id'");
        return $edit;
    }
    public function ubahblok8($id, $catatan)
    {
        $edit = $this->db->query("UPDATE dc_ruta SET catatan='$catatan'  WHERE id='$id'");
        return $edit;
    }
    //add pengguna
    public function addpengguna()
    {
        $data = [
            'username' => htmlspecialchars($this->input->post('username', TRUE), ENT_QUOTES),
            'email' => htmlspecialchars($this->input->post('email', TRUE), ENT_QUOTES),
            'password' => password_hash($this->input->post('pass1', TRUE), PASSWORD_DEFAULT),
            'role_id' => htmlspecialchars($this->input->post('role', TRUE), ENT_QUOTES),
            'status' => htmlspecialchars($this->input->post('status', TRUE), ENT_QUOTES),
        ];
        $this->db->insert('dc_pengguna', $data);
    }
    //kelola pengguna
    public function getpengguna()
    {
        $this->db->select('*')
            ->from('dc_pengguna u');
        return $this->db->get()->result();
    }
    //get count all jumlah penduduk
    public function countagt()
    {
        $this->db->select('count(id_pm_agt) as penduduk')
            ->from('dc_agt_keluarga');
        return $this->db->get()->result();
    }
    public function countluas10()
    {
        $this->db->select('count(id) as miskin')
            ->from('dc_ruta')
            ->where('luas <=', 10);
        return $this->db->get()->result();
    }
    //hapus ruta
    public function hapusruta($id)
    {
        $this->db->trans_start();
        $this->db->delete('dc_ruta', array('id' => $id));
        $this->db->delete('dc_lahan', array('id_kk_fgn' => $id));
        $this->db->delete('dc_lahan', array('id_kk_fgn' => $id));
        $this->db->delete('dc_agt_keluarga', array('id_agt' => $id));
        return $this->db->trans_complete();
    }
    function calculate_median()
    {
        $median = $this->db->query("SELECT pendapatan FROM dc_ruta ORDER BY ABS(pendapatan) ASC");

        // $row = $median->row_array(5);
        // return $median->result_array();
        $result = array();
        foreach ($median->result_array() as $row) {
            // $row['pendapatan'] = $pendapatan;
            // unset($row['pendapatan']);
            // echo $row['name'];
            // echo $row['body'];
            $result[] = $row['pendapatan'];
        }
        // var_export($result);
        sort($result);
        $count = sizeof($result);   // cache the count
        $index = floor($count / 2);  // cache the index
        if (!$count) {
            $t =  "no values";
        } elseif ($count & 1) {    // count is odd
            $t = (int)$result[$index];
        } else {                   // count is even
            $t = ((int)$result[$index - 1] + (int)$result[$index]) / 2;
        }
        error_reporting(0);
        return $t;
        // $row = $median->num_rows();
        // $count = count($arr); //total numbers in array
        // $middleval = floor(($count - 1) / 2); // find the middle value, or the lowest middle value
        // if ($count % 2) { // odd number, middle is the median
        //     $median = $arr[$middleval];
        // } else { // even number, calculate avg of 2 medians
        //     $low = $arr[$middleval];
        //     $high = $arr[$middleval + 1];
        //     $median = (($low + $high) / 2);
        // }
        // $arr = array_column($meadian, "pendapatan");
        // print_r($arr);
    }
    //getpenggunabyidpgn
    public function getpenggunabyid($idpgn)
    {
        $this->db->select('*')
            ->from('dc_pengguna u ')
            ->where('u.idpgn', $idpgn);
        return $this->db->get()->result();
    }
    public function ubahpengguna($id, $role2, $email2, $username2, $status2)
    {
        $edit = $this->db->query("UPDATE dc_pengguna SET role_id ='$role2', email ='$email2', username ='$username2', status = '$status2' WHERE idpgn ='$id'");
        return $edit;
    }
}
