<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class M_akun extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

    function getJabatan($id)
    {
        return $this->db->query("SELECT nama FROM jabatan WHERE id = ? AND is_active = '1'", $id);
    }

    function getUser($is_count = false, $limit = null, $start = null, $id = null) {
        $param = array();

        if ($is_count) {
            $query = "SELECT COUNT(id) AS jumlah";
        } else {
            $query = "SELECT u.id, u.email, u.username, u.real_name, p.nama AS personil, u.role_analis, GROUP_CONCAT(ug.id_group SEPARATOR ', ') AS id_group, GROUP_CONCAT(lg.nama_group SEPARATOR ', ') AS nama_group, GROUP_CONCAT(lg.keterangan SEPARATOR ', ') AS ket_group";
        }

        $query .= " FROM user u";

        if (!$is_count) {
            $query .= " LEFT JOIN user_group ug ON u.id = ug.id_user
						LEFT JOIN login_group lg ON ug.id_group = lg.id_group
						LEFT JOIN personil p ON p.id_user = u.id
					";
        }
        $query .= " WHERE u.is_active = '1'";
        if ($id != null) {
            $query .= " AND u.id = " . $id;
        }
        $query .= " GROUP BY u.id
                    ORDER BY u.id DESC
                ";

        if ($is_count == false) {
            if (isset($limit)) {
                $query .= " LIMIT ?, ?";
                $param[] = intval($start);
                $param[] = intval($limit);
            }
        }

        $result = $this->db->query($query, $param);

        if ($is_count) {
            return $result->row_array()['jumlah'];
        } else {
            if ($id != null) {
                return $result->row_array();
            } else {
                $result = $result->result_array();
                foreach ($result as $k => $r) {
                    $r['role_analis'] = json_decode($r['role_analis']);
                    $result[$k]['r_analis'] = '';
                    if (!empty($r['role_analis'])) {
                        foreach ($r['role_analis'] as $k2 => $ra) {
                            $this->db->where('id', $ra);
                            $res = $this->db->get('parameter_uji')->row_array();
                            $result[$k]['r_analis'] .= $res['nama'] . ', ';
                        }
                    }
                }
                return $result;
            }
        }
    }

    function getPersonil($is_count = false, $limit = null, $start = null, $id = null) {
        $param = array();

        if ($is_count) {
            $query = "SELECT COUNT(p.id_user) AS jumlah";
        } else {
            $query = "SELECT p.*, j.nama as nama_jabatan";
        }

        $query .= " FROM personil p
          LEFT JOIN jabatan j ON j.id = p.id_jabatan
        ";

        if (!$is_count) {
            $query .= " ";
        }

        $query .= " WHERE p.is_active = '1'
                    ORDER BY p.id_user ASC
                ";

        if ($is_count == false) {
            if (isset($limit)) {
                $query .= " LIMIT ?, ?";
                $param[] = intval($start);
                $param[] = intval($limit);
            }
        }

        $result = $this->db->query($query, $param);

        if ($is_count) {
            return $result->row_array()['jumlah'];
        } else {
            if ($id != null) {
                return $result->row_array();
            } else {
                return $result->result_array();
            }
        }
    }

    function getRole() {
        $query = "SELECT *
            FROM login_group
            WHERE is_active = '1'
        ";
        $result = $this->db->query($query);
        return $result->result_array();
    }

    function getAnalisRole() {
        return $this->db->get('parameter_uji')->result_array();
    }

    function setUser($data, $id = null) {
        $this->db->trans_begin();
        $param = array();
        $param[] = $data['username'];
        $param[] = $data['email'];

        if(is_null($id) && $data['password'] != '' && !is_null($data['password'])){
          $param[] = $data['password'];
        }

        $param[] = $data['role_analis'];

        if (is_null($id)) {
            $query = "INSERT INTO `user` (`username`, `email`, `password`, role_analis) VALUES
                (?,?,SHA2(?, 256),?)
            ";
        } else {
            $param[] = $id;
            $query = "UPDATE `user` SET `username` = ?, `email` = ?";

            if($data['password'] != '' && !is_null($data['password'])){
              $query .= ", `password` = SHA2(?, 256)";
              $param[] = $data['password'];
            }

            $query .= ", `role_analis` = ?
                WHERE id_user = ?";
        }

        $result = $this->db->query($query, $param);

        if (is_null($id))
            $idUser = $this->db->insert_id();
        else
            $idUser = $id;

        $query = "DELETE FROM user_group WHERE id_user = ?";
        $this->db->query($query, $idUser);

        foreach ($data['role_user'] AS $d) {
            $this->db->set('id_user', $idUser);
            $this->db->set('id_group', $d);
            $this->db->insert('user_group');
        }

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return false;
        } else {
            $this->db->trans_commit();
            return $idUser;
        }
    }

    function delete_user_personil($data){
      $this->db->trans_begin();
      $this->db->query("UPDATE ".$data['table']." SET is_active = '0' WHERE ".$data['id_key']." = ?", $data['id']);

      if($data['id_key'] == 'id_user'){
        $query = "DELETE FROM user_group WHERE id_user = ?";
        $this->db->query($query, $data['id']);
      }

      if($this->db->trans_status() == false){
        $this->db->trans_rollback();
        return false;
      }else{
        $this->db->trans_commit();
        return true;
      }
    }

    function setPersonil($data) {
        $this->db->trans_begin();
        $param = array();
        $param[] = $data['nama_personil'];
        $param[] = $data['id_jabatan'];
        $param[] = isset($data['jabatan_lainnya']) && $data['jabatan_lainnya'] != '' ? $data['jabatan_lainnya'] : null;
        $param[] = $data['nip'];
        $param[] = $data['pangkat'];
        $param[] = $data['alamat'];
        $param[] = $data['no_telp'];
        $param[] = $data['email'];
        $param[] = $data['file_foto'];

        if (isset($data['id_personil']) && $data['id_personil'] != '') {
            $param[] = $id = $data['id_personil'];
            $query = "UPDATE personil SET nama = ?, id_jabatan = ?, jabatan_lainnya = ?, nip = ?, pangkat = ?, alamat = ?, no_telp = ?, email = ?, file_foto = ?
            WHERE id_personil = ? AND is_active = '1'";
        } else {
            $query = "INSERT INTO personil (nama, id_jabatan, jabatan_lainnya, nip, pangkat, alamat, no_telp, email, file_foto) VALUES
            (?,?,?,?,?,?,?,?,?)";
        }

        $result = $this->db->query($query, $param);
        if (empty($id))
            $id = $this->db->insert_id();

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return false;
        } else {
            $this->db->trans_commit();
            return $id;
        }
    }

    function detailPersonil($id) {
        $query = "SELECT p.*
            FROM personil p
            WHERE p.id_personil = ? AND p.is_active = '1';
        ";

        $result = $this->db->query($query, $id)->row_array();

        return $result;
    }

    function edit_user() {
        $this->db->set('username', $this->input->post('username'));
        $this->db->set('email', $this->input->post('email'));
        $this->db->set('real_name', $this->input->post('realName'));
        $this->db->where('id_user', $this->input->post('id'));
        $this->db->update('user');
    }

    function change_pass() {
        $this->db->set('password', hash('sha256', $this->input->post('confirmPass')));
        $this->db->update('user');
    }

    function edit() {
        $input = $this->input->post();
        $this->db->set('username', $input['username']);
        $this->db->set('email', $input['email']);
        $this->db->set('real_name', $input['real_name']);
        if ($input['newPass']) {
            $this->db->set('password', hash('sha256', $input['newPass']));
        }
        $this->db->where('id', $input['id_user']);
        return $this->db->update('user');
    }

    function my_account($userId) {
        $param = array();
        $param[] = $userId;

        $result = $this->db->query("SELECT u.id, u.username, u.real_name, u.email
        FROM user u
        WHERE u.id = ?", $param);

        $base = $result->first_row();

        $result = $this->db->query("SELECT ug.id_group, lg.nama_group, lg.keterangan
        FROM user_group ug
		LEFT JOIN login_group lg ON ug.id_group = lg.id_group
        WHERE ug.id_user = ?", $param);

        $group = $result->result();

        $result = new stdclass();
        $result->id_user = $base->id_user;
        $result->username = $base->username;
        $result->real_name = $base->real_name;
        $result->email = $base->email;
        $result->group = $group;

        return $result;
    }

    function update_my_account($data, $isPassword = false) {
        if ($isPassword) {
            $query = "UPDATE user SET email = ?, real_name = ?, password = SHA2(?, 256) WHERE id_user = ?";
        } else {
            $query = "UPDATE user SET email = ?, real_name = ? WHERE id = ?";
        }

        $status = $this->db->query($query, $data);
        return $status;
    }

    //-------------------------------------------------------
    //  NEW MODAL
    //-------------------------------------------------------

    function get_data_user_list($is_count='')
    {
        if($is_count != ''){
            $this->db->select("count(u.id) as total");
        }else{
            $this->db->select("u.*, p.nama");
        }
        $this->db->join('personil p ', 'p.id_user = u.id', 'left');
        $this->db->where(['u.is_active'=>'1']);
        $query = $this->db->get('user u');

        if($is_count != ''){
            $result = $query->row_array()['total'];
        }else{
            $result = $query->result_array();
        }
        return $result;
        
    }

    function get_data_personil_list($is_count='')
    {
        if($is_count != ''){
            $this->db->select("count(p.id) as total");
        }else{
            $this->db->select("p.*");
        }
        $this->db->where(['p.is_active'=>'1']);
        $query = $this->db->get('personil p');

        if($is_count != ''){
            $result = $query->row_array()['total'];
        }else{
            $result = $query->result_array();
        }
        return $result;
        
    }

    function get_user_by_id($id)
    {
        $this->db->select("u.*");
        $query = $this->db->get_where("user u", ['u.id'=>$id, 'u.is_active'=>'1']);
        $result = $query->row_array();
        return $result;
    }

    function get_personil_by_id($id)
    {
        $this->db->select("p.*");
        $query = $this->db->get_where("personil p", ['p.id'=>$id, 'p.is_active'=>'1']);
        $result = $query->row_array();
        return $result;
    }

    function save_akun($type='', $data=[])
    {
        $this->db->insert($type, $data);
        return $this->db->insert_id();
    }

    function update_akun($type='', $data='', $id='')
    {
        $param = $data;
        if($type == 'user'){
            $this->db->where(['id'=>$id]);
            $this->db->update('user', $param);
        }
        if($type == 'personil'){
            $this->db->where(['id'=>$id]);
            $this->db->update('personil', $param);
        }

        return $id;
    }

    function get_role_user($id="")
    {
        $param = [];
        $param['is_active'] = '1';
        if($id != ''){
            $param['id_group'] = $id;
        }
        $this->db->select("id_group as id, nama_group as nama");
        $query = $this->db->get_where('login_group', $param);

        if($id != ''){
            $result = $query->row_array();
        }else{
            $result = $query->result_array();
        }
        return $result;
    }

    function get_role_analis($id=''){
        $param = [];
        $param['is_active'] = '1';
        if($id != ''){
            $param['id'] = $id;
        }

        $this->db->select('id, nama');
        $query = $this->db->get_where('parameter_uji', $param);

        if($id != ''){
            $result = $query->row_array();
        }else{
            $result = $query->result_array();
        }

        return $result;
    }

    function hapus_akun($type, $data, $id)
    {
        if($type == 'user'){
            $this->db->where(['id'=>$id]);
            $this->db->update('user', $data);
        }
        if($type == 'personil'){
            $this->db->where(['id'=>$id]);
            $this->db->update('personil', $data);
        }
    }

    function get_data_jabatan($id='', $status='', $edit='')
    {   
        $list_jabat = [];
        $user_jabat = [];
        
        $list_jabat = $this->validasi_jabatan();
        if($edit != ''){
            $user_jabat = $this->validasi_jabatan($edit);
        }

        $param = [];
        $param['is_active'] = '1';
        if($id != ''){
            $param['id'] = $id;
        }
        if($status != ''){
            $param['status'] = '0';
        }
        $this->db->select('id, nama');
        $this->db->where($param);

        if(count($list_jabat) > 0 && count($user_jabat) > 0){
            $this->db->where_not_in('id', $list_jabat);
            $this->db->or_where_in('id', $user_jabat);
        }elseif(count($list_jabat) > 0 && $id == ''){
            $this->db->where_not_in('id', $list_jabat);
        }
        
        $query = $this->db->get('jabatan');

        if($id != ''){
            $result = $query->row_array();
        }else{
            $result = $query->result_array();
        }
        return $result;
    }

    function validasi_jabatan($id_personil = '')
    {
        $this->db->select('id_jabatan');
        $param['is_active'] = '1';
        if($id_personil != ''){
            $param['id'] = $id_personil;
        }
        $this->db->where($param);
        $query = $this->db->get('personil');
        $list_jabatan = $query->result_array();

        $list_jabat = [];
        if(count($list_jabatan) > 0){
            foreach($list_jabatan as $l){
                if(!is_null($l['id_jabatan']) && $l['id_jabatan'] != '' && $l['id_jabatan'] != 'null'){
                    $array_l = json_decode($l['id_jabatan']);
                    if(count($array_l)>0){
                        foreach($array_l as $al){
                            $list_jabat[] = $al;
                        }
                    }
                }
            }
        }

        $list_jabat = array_unique($list_jabat);

        return $list_jabat;
    }

}
