<?php

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

class M_akun extends CI_Model {

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

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

        if ($is_count) {
            $query = "SELECT COUNT(id_user) AS jumlah";
        } else {
            $query = "SELECT u.id_user, 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_user = 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_user
					";
        }
        $query .= " WHERE u.is_active = '1'";
        if ($id != null) {
            $query .= " AND u.id_user = " . $id;
        }
        $query .= " GROUP BY u.id_user
                    ORDER BY u.id_user 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_jenis', $ra);
                            $res = $this->db->get('parameter_uji')->row_array();
                            $result[$k]['r_analis'] .= $res['nama_jenis'] . ', ';
                        }
                    }
                }
                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_jenis as nama_jabatan";
        }

        $query .= " FROM personil p
          LEFT JOIN jabatan j ON j.id_jenis = 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_user', $input['id_user']);
        return $this->db->update('user');
    }

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

        $result = $this->db->query("SELECT u.id_user, u.username, u.real_name, u.email
        FROM user u
        WHERE u.id_user = ?", $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_user = ?";
        }

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

    function get_role_analis($id){
      return $this->db->query("SELECT nama_jenis FROM parameter_uji WHERE id_jenis = ? AND is_active = '1'", $id)->row_array()['nama_jenis'];
    }

}
