<?php 
class Model_dosen extends CI_model{

//xxxxxxxxxxxxxxxxxxxxxxxxx
	function list_dosenwali(){
        return $this->db->query("SELECT A.*,COUNT(B.mahasiswa_id) AS totalmhs FROM tb_dosen A
								LEFT JOIN tb_mahasiswa B ON A.dosen_id=B.dosen_wali_id
								GROUP BY A.dosen_id ORDER BY A.nama_lengkap ASC");
    }
    function list_(){
        return $this->db->query("SELECT A.dosen_id,
									A.nidn,
									A.gelar_pendidikan_id,
									A.nama_lengkap,
									A.nip,
									A.jenis_kelamin,
									A.status_kawin,
									A.alamat,
									A.hp,
									A.email,
									A.photo,
									A.aktif,A.nama_file,
									B.keterangan AS keterangan_pendidikan,B.nama_gelar_pendidikan,
									C.keterangan AS agama,
									D.nama_gelar_pendidikan AS gelar_belakang
								FROM tb_dosen A
								LEFT JOIN tb_gelar_pendidikan B ON A.gelar_pendidikan_id=B.gelar_pendidikan_id
								LEFT JOIN tb_gelar_pendidikan D ON D.gelar_pendidikan_id=A.gelar_belakang_pendidikan_id
								LEFT JOIN tb_agama C ON A.agama_id=C.agama_id
								ORDER BY A.nama_lengkap ASC, A.nidn DESC");
    }
	function list_gelar(){
        return $this->db->query("SELECT * FROM tb_gelar_pendidikan ORDER BY nama_gelar_pendidikan DESC");
    }
	function list_agama(){
        return $this->db->query("SELECT * FROM tb_agama ORDER BY keterangan DESC");
    }
    
    function tambah_baru(){
       $config['upload_path'] = 'asset/pas_photo/';
        $config['allowed_types'] = 'gif|jpg|png|JPG|JPEG';
        $config['max_size'] = '3000'; // kb
        $this->load->library('upload', $config);
        $this->upload->do_upload('m');
        $hasil=$this->upload->data();
            
            if ($hasil['file_name']==''){
                    $datadb = array('nip'=>$this->db->escape_str($this->input->post('a')),
									'nidn'=>$this->input->post('b'),
									'dosen_id'=>$this->input->post('id'),
                                    'gelar_belakang_pendidikan_id'=>$this->db->escape_str($this->input->post('c')),
                                    'nama_lengkap'=>$this->db->escape_str($this->input->post('d')),
									'gelar_pendidikan_id'=>seo_title($this->input->post('e')),
									'agama_id'=>$this->db->escape_str($this->input->post('f')),
									'jenis_kelamin'=>$this->db->escape_str($this->input->post('g')),
                                    'status_kawin'=>$this->db->escape_str($this->input->post('h')),
                                    'alamat'=>$this->db->escape_str($this->input->post('i')),
                                    'email'=>$this->db->escape_str($this->input->post('j')),
                                    'hp'=>$this->db->escape_str($this->input->post('k')),
                                    'aktif'=>$this->db->escape_str($this->input->post('l')),
									'diinput_tanggal'=>date('Y-m-d H:i:s'),
                                    'diinput_oleh'=>$this->session->username);
            }else{
                     $datadb = array('nip'=>$this->db->escape_str($this->input->post('a')),
					 				'nidn'=>$this->input->post('b'),
									'dosen_id'=>$this->input->post('id'),
                                    'gelar_belakang_pendidikan_id'=>$this->db->escape_str($this->input->post('c')),
                                    'nama_lengkap'=>$this->db->escape_str($this->input->post('d')),
									'gelar_pendidikan_id'=>seo_title($this->input->post('e')),
									'agama_id'=>$this->db->escape_str($this->input->post('f')),
									'jenis_kelamin'=>$this->db->escape_str($this->input->post('g')),
                                    'status_kawin'=>$this->db->escape_str($this->input->post('h')),
                                    'alamat'=>$this->db->escape_str($this->input->post('i')),
                                    'email'=>$this->db->escape_str($this->input->post('j')),
                                    'hp'=>$this->db->escape_str($this->input->post('k')),
                                    'aktif'=>$this->db->escape_str($this->input->post('l')),
									'photo'=>$hasil['file_name'],
                                    'diperbaharui_tanggal'=>date('Y-m-d H:i:s'),
                                    'diperbaharui_oleh'=>$this->session->username);
            }
        $this->db->insert('tb_dosen',$datadb);
		$this->insert_pengguna();
		$title = "Tambah Data Dosen <br />dengan ID [id=".$this->input->post('id')."]";
			$descrip = $this->fetch_description($title,$datadb);
			$this->add_audittrail("INSERT",$title,$descrip,'tb_dosen');
    }
	function insert_pengguna(){
      		$datadb = array('username'=>$this->db->escape_str($this->input->post('username')),
							'pengguna_id'=>$this->db->escape_str($this->input->post('id')),
							'password'=>hash("sha512", md5('dosen')),
							'level'=>'6',
							'blokir'=>'N',
							'nama_lengkap'=>$this->input->post('d'),
							'tanggal_perubahan'=>date('Y-m-d H:i:s'),
							'username_perubahan'=>$this->session->username,
							'id_session'=>md5($this->input->post('a')));
			$this->db->insert('tb_pengguna',$datadb);
    }
	function update_pengguna(){
		if ($this->input->post('username') =='' AND $this->input->post('password') ==''){
      		$datadb = array('level'=>'6',
							'blokir'=>'N',
							'tanggal_perubahan'=>date('Y-m-d H:i:s'),
							'username_perubahan'=>$this->session->username);
		}elseif ($this->input->post('username') !='' AND $this->input->post('password') !=''){
			$datadb = array('username'=>$this->db->escape_str($this->input->post('username')),
							'pengguna_id'=>$this->db->escape_str($this->input->post('id')),
							'password'=>hash("sha512", md5($this->input->post('password'))),
							'level'=>'6',
							'blokir'=>'N',
							'tanggal_perubahan'=>date('Y-m-d H:i:s'),
							'username_perubahan'=>$this->session->username,
							'id_session'=>md5($this->input->post('username')));
		}elseif ($this->input->post('username') !='' AND $this->input->post('password') ==''){
			$datadb = array('username'=>$this->db->escape_str($this->input->post('username')),
							'pengguna_id'=>$this->db->escape_str($this->input->post('id')),
							'level'=>'6',
							'blokir'=>'N',
							'tanggal_perubahan'=>date('Y-m-d H:i:s'),
							'username_perubahan'=>$this->session->username,
							'id_session'=>md5($this->input->post('username')));
		}elseif ($this->input->post('username') =='' AND $this->input->post('password') !=''){
			$datadb = array('password'=>hash("sha512", md5($this->input->post('password'))),
							'pengguna_id'=>$this->db->escape_str($this->input->post('id')),
							'level'=>'6',
							'blokir'=>'N',
							'tanggal_perubahan'=>date('Y-m-d H:i:s'),
							'username_perubahan'=>$this->session->username);
		}
			$this->db->where('user_id',$this->input->post('id'));
			$this->db->update('tb_pengguna',$datadb);
			
    }
	function list_edit($id){
        return $this->db->query("SELECT A.*,D.username from tb_dosen A 
								LEFT JOIN tb_pengguna D ON A.dosen_id=D.user_id
								WHERE A.dosen_id='$id'");
    }
	 function hapus_data($id){
        return $this->db->query("DELETE FROM tb_dosen where dosen_id='$id'");
		$title = "Hapus Data Dosen <br />dengan ID [id=".$this->input->post('id')."]";
			$descrip = $this->fetch_description($title,$datadb);
			$this->add_audittrail("DELETE",$title,$descrip,'tb_dosen');
    }
	function list_detail($id){
        return $this->db->query("SELECT A.*,B.keterangan AS agama FROM tb_mahasiswa A LEFT JOIN tb_agama B ON A.agama_id=B.agama_id
								where A.mahasiswa_id='$id'")->result_array();
    }
	  function list_update(){
        $config['upload_path'] = 'asset/pas_photo/';
        $config['allowed_types'] = 'gif|jpg|png|JPG|JPEG';
        $config['max_size'] = '3000'; // kb
        $this->load->library('upload', $config);
        $this->upload->do_upload('m');
        $hasil=$this->upload->data();
            if ($hasil['file_name']==''){
                    $datadb = array('nip'=>$this->db->escape_str($this->input->post('a')),
									'nidn'=>$this->input->post('b'),
                                    'gelar_belakang_pendidikan_id'=>$this->db->escape_str($this->input->post('c')),
                                    'nama_lengkap'=>$this->db->escape_str($this->input->post('d')),
									'gelar_pendidikan_id'=>seo_title($this->input->post('e')),
									'agama_id'=>$this->db->escape_str($this->input->post('f')),
									'jenis_kelamin'=>$this->db->escape_str($this->input->post('g')),
                                    'status_kawin'=>$this->db->escape_str($this->input->post('h')),
                                    'alamat'=>$this->db->escape_str($this->input->post('i')),
                                    'email'=>$this->db->escape_str($this->input->post('j')),
                                    'hp'=>$this->db->escape_str($this->input->post('k')),
                                    'aktif'=>$this->db->escape_str($this->input->post('l')),
									'diinput_tanggal'=>date('Y-m-d H:i:s'),
                                    'diinput_oleh'=>$this->session->username);
            }else{
                     $datadb = array('nip'=>$this->db->escape_str($this->input->post('a')),
									'nidn'=>$this->input->post('b'),
                                    'gelar_belakang_pendidikan_id'=>$this->db->escape_str($this->input->post('c')),
                                    'nama_lengkap'=>$this->db->escape_str($this->input->post('d')),
									'gelar_pendidikan_id'=>seo_title($this->input->post('e')),
									'agama_id'=>$this->db->escape_str($this->input->post('f')),
									'jenis_kelamin'=>$this->db->escape_str($this->input->post('g')),
                                    'status_kawin'=>$this->db->escape_str($this->input->post('h')),
                                    'alamat'=>$this->db->escape_str($this->input->post('i')),
                                    'email'=>$this->db->escape_str($this->input->post('j')),
                                    'hp'=>$this->db->escape_str($this->input->post('k')),
                                    'aktif'=>$this->db->escape_str($this->input->post('l')),
									'photo'=>$hasil['file_name'],
                                    'diperbaharui_tanggal'=>date('Y-m-d H:i:s'),
                                    'diperbaharui_oleh'=>$this->session->username);
            }
        $this->db->where('dosen_id',$this->input->post('id'));
        $this->db->update('tb_dosen',$datadb);
		$this->update_pengguna();
		$title = "Update Data Dosen <br />dengan ID [id=".$this->input->post('b')."]";
			$descrip = $this->fetch_description($title,$datadb);
			$this->add_audittrail("UPDATE",$title,$descrip,'tb_dosen');
    }
	function list_update_cv($id){
                    $datadb = array('nama_file'=>'',
									'diperbaharui_tanggal'=>date('Y-m-d H:i:s'),
                                    'diperbaharui_oleh'=>$this->session->username);
            
        $this->db->where('dosen_id',$id);
        $this->db->update('tb_dosen',$datadb);
		
			$title = "Update Data CV <br />dengan ID [id=".$this->input->post('b')."]";
			$descrip = $this->fetch_description($title,$datadb);
			$this->add_audittrail("UPDATE",$title,$descrip,'tb_dosen');
    }
	function list_dosenwalixx(){
        return $this->db->query("SELECT A.dosen_id,
									A.nidn,
									A.gelar_pendidikan_id,
									A.nama_lengkap,
									A.nip,
									A.jenis_kelamin,
									A.status_kawin,
									A.alamat,
									A.hp,
									A.email,
									A.photo,
									A.aktif,A.nama_file,
									B.keterangan AS keterangan_pendidikan,B.nama_gelar_pendidikan,
									D.nama_gelar_pendidikan AS gelar_belakang
								FROM tb_dosen A
								LEFT JOIN tb_gelar_pendidikan B ON A.gelar_pendidikan_id=B.gelar_pendidikan_id
								LEFT JOIN tb_gelar_pendidikan D ON D.gelar_pendidikan_id=A.gelar_belakang_pendidikan_id
								LEFT JOIN tb_mahasiswa C ON A.dosen_id=C.dosen_wali_id
								ORDER BY A.nama_lengkap ASC, A.nidn DESC");
    }
	function tambah_dosenwali($id){
                    $datadb = array('dosen_wali_id'=>$this->db->escape_str($this->input->post('dosen_id')),
									'tanggal_perubahan'=>date('Y-m-d H:i:s'),
                                    'username_perubahan'=>$this->session->username);
            
        $this->db->where('mahasiswa_id',$id);
        $this->db->update('tb_mahasiswa',$datadb);
		
			$title = "Tambah Data Dosen Wali <br />dengan ID [id=".$this->input->post('b')."]";
			$descrip = $this->fetch_description($title,$datadb);
			$this->add_audittrail("UPDATE",$title,$descrip,'tb_mahasiswa');
    }
	function tamabah_dosenwali(){
      		$datadb = array('dosen_wali_id'=>$this->db->escape_str($this->input->post('dosen_id')));
			$this->db->where('mahasiswa_id',$this->input->post('mahasiswa_id'));
			$this->db->update('tb_mahasiswa',$datadb);
    }
	
	function list_upload_cv(){
        $config['upload_path'] = 'asset/dokumen/';
        $config['allowed_types'] = 'doc|rtf|docx|pdf|rar';
        $config['max_size'] = '2048'; // kb
        $this->load->library('upload', $config);
        $this->upload->do_upload('m');
        $hasil=$this->upload->data();
            $datadb = array('nama_file'=>$hasil['file_name'],
                                    'diperbaharui_tanggal'=>date('Y-m-d H:i:s'),
                                    'diperbaharui_oleh'=>$this->session->username);
            
        $this->db->where('dosen_id',$this->input->post('id'));
        $this->db->update('tb_dosen',$datadb);
		
		$title = "Update CV Data Dosen <br />dengan ID [id=".$this->input->post('id')."]";
			$descrip = $this->fetch_description($title,$datadb);
			$this->add_audittrail("UPDATE",$title,$descrip,'tb_dosen');
    }
	function delete_upload_cv($id){
  
            $datadb = array('nama_file'=>'',
                                    'diperbaharui_tanggal'=>date('Y-m-d H:i:s'),
                                    'diperbaharui_oleh'=>$this->session->username);
            
        $this->db->where('dosen_id',$id);
        $this->db->update('tb_dosen',$datadb);
		$this->update_pengguna();
		$title = "Update CV Data Dosen <br />dengan ID [id=".$this->input->post('id')."]";
			$descrip = $this->fetch_description($title,$datadb);
			$this->add_audittrail("UPDATE",$title,$descrip,'tb_dosen');
    }
	function fetch_description($title,$data){
		$descrip = '<br><table style="vertical-align:top" cellspacing="0" cellpadding="1" border="1">';
		$descrip .= '<tr><th>Nama Kolom</th><th>Nilai</th></tr>';
		foreach ($data as $key => $value) {
			$descrip .= '<tr>';
			$descrip .= '<td>'.$key.'</td>';
			$descrip .= '<td>'.$value.'</td>';
			$descrip .= '</tr>';
		}
		$descrip .= '</table>';
		return $descrip;
	}

	function add_audittrail($action,$title,$descrip,$tablename){
		try {
			$data = array(
				'datetime' => date("Y-m-d H:i:s"),
				'ipaddress' => $this->input->ip_address(),
				'username' => $this->session->userdata('username'),
				'tablename' => $tablename,
				'formname' => 'by komar',
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
		}
	}

}