<?php 
class Model_mahasiswa extends CI_model{
 
	function cek_login(){
        return $this->db->query("SELECT A.username,A.level,A.foto,A.blokir,
									B.stambuk,B.nama_lengkap,B.semester,B.photo,B.nomor_hp
								FROM tb_pengguna A
								LEFT JOIN tb_mahasiswa B ON A.pengguna_id=B.mahasiswa_id
									WHERE A.username='".$this->session->username."'");
    }
	 function get_all_produk() {
        $this->datatables->select('nomor_perkara,tanggal_surat,pihak1_text,pihak2_text,petitum,tanggal_pendaftaran');
        $this->datatables->from('perkara');
         $this->datatables->add_column('view', '<a href="javascript:void(0);" class="edit_record btn btn-info btn-xs" data-kode="$1" data-nama="$2" data-harga="$3" data-kategori="$4">Edit</a>  <a href="javascript:void(0);" class="hapus_record btn btn-danger btn-xs" data-kode="$1">Hapus</a>','nomor_perkara,tanggal_pendaftaran,pihak1_text,pihak2_text,petitum');
        return $this->datatables->generate();
  }
	function list_perkara(){
        return $this->db->query("SELECT * FROM perkara	ORDER BY nomor_perkara DESC");
    }
    function list_(){
        return $this->db->query("SELECT A.mahasiswa_id,A.nim,A.stambuk,A.nama_lengkap,A.alamat,A.semester,A.ruangan_id,A.semester_aktif,A.tanggal_lahir,A.status,
									B.keterangan AS angkatan,
									C.keterangan AS agama,
									D.nama_ruangan,
									E.nama_lengkap AS nama_dosen
								FROM tb_mahasiswa A
								LEFT JOIN tb_tahun_akademik B ON A.tahun_akademik_id=B.tahun_akademik_id
								LEFT JOIN tb_agama C ON A.agama_id=C.agama_id
								LEFT JOIN tb_ruangan D ON A.ruangan_id=D.ruangan_id
								LEFT JOIN tb_dosen E ON A.dosen_wali_id=E.dosen_id
								ORDER BY A.stambuk DESC,A.ruangan_id ASC");
    }
	function list_mahasiswa_page($awal, $akhir){
       return $this->db->query("SELECT A.*,
									B.keterangan AS angkatan,
									C.keterangan AS agama,
									D.nama_ruangan,
									E.nama_lengkap AS nama_dosen,
									F.tanggal_pengajuan,
									F.judul_skripsi,
									F.skripsi_id
								FROM tb_mahasiswa A
								LEFT JOIN  tb_skripsi F ON A.mahasiswa_id=F.mahasiswa_id
								LEFT JOIN tb_tahun_akademik B ON A.tahun_akademik_id=B.tahun_akademik_id
								LEFT JOIN tb_agama C ON A.agama_id=C.agama_id
								LEFT JOIN tb_ruangan D ON A.ruangan_id=D.ruangan_id
								LEFT JOIN tb_dosen E ON A.dosen_wali_id=E.dosen_id
								ORDER BY A.stambuk DESC,A.ruangan_id ASC LIMIT $awal, $akhir");
    }
	function list_jumlah_mhs_kelas(){
        return $this->db->query("SELECT A.angkatan_id,B.ruangan_id,B.nama_ruangan,COUNT(A.ruangan_id) AS jumlah_mahasiswa,C.keterangan AS ta_angkatan FROM tb_mahasiswa A
								LEFT JOIN tb_ruangan B ON A.ruangan_id=B.ruangan_id
								LEFT JOIN tb_angkatan C ON A.angkatan_id=C.angkatan_id
								GROUP BY A.ruangan_id");
    }
	function list_detail_mhsperkelas($idangkatan,$idact_kelas){
        return $this->db->query("SELECT A.mahasiswa_id,A.nim,A.stambuk,A.nama_lengkap,A.alamat,A.semester,A.ruangan_id,A.semester_aktif,A.tanggal_lahir,A.status,
									B.keterangan AS angkatan,
									C.keterangan AS agama,
									D.nama_ruangan,
									E.nama_lengkap AS nama_dosen
								FROM tb_mahasiswa A
								LEFT JOIN tb_tahun_akademik B ON A.tahun_akademik_id=B.tahun_akademik_id
								LEFT JOIN tb_agama C ON A.agama_id=C.agama_id
								LEFT JOIN tb_ruangan D ON A.ruangan_id=D.ruangan_id
								LEFT JOIN tb_dosen E ON A.dosen_wali_id=E.dosen_id
								WHERE A.tahun_akademik_id = $idangkatan AND A.ruangan_id = $idact_kelas
								ORDER BY A.stambuk DESC,A.ruangan_id ASC");
    }
	function list_kelas(){
        return $this->db->query("SELECT * FROM tb_ruangan WHERE kondisi!='3' ORDER BY ruangan_id DESC");
    }
	function list_angkatan(){
        return $this->db->query("SELECT * FROM tb_tahun_akademik WHERE status='Y' ORDER BY tahun_akademik_id DESC");
    }
	function list_angkatan_detail($idangkatan){
        return $this->db->query("SELECT * FROM tb_tahun_akademik WHERE tahun_akademik_id = $idangkatan ORDER BY tahun_akademik_id DESC");
    }
	function list_kelas_detail($idact_kelas){
        return $this->db->query("SELECT * FROM tb_ruangan WHERE ruangan_id =$idact_kelas ORDER BY ruangan_id DESC");
    }
	function list_ta_angkatan(){
        return $this->db->query("SELECT * FROM tb_angkatan ORDER BY angkatan_id DESC");
    }
	function list_agama(){
        return $this->db->query("SELECT * FROM tb_agama ORDER BY keterangan DESC");
    }
    function list_dosen(){
        return $this->db->query("SELECT * FROM tb_dosen ORDER BY nama_lengkap ASC");
    }
	function tambah_skripsi(){

        $exx = explode('-',$this->input->post('tanggal_skripsi'));
		$tanggal_pengajuan = $exx[2].'-'.$exx[1].'-'.$exx[0];    
            $datadb = array('mahasiswa_id'=>$this->input->post('id'),
									'judul_skripsi'=>$this->input->post('judul'),
									'tanggal_pengajuan'=>$tanggal_pengajuan,
									'dosen_pembimbing_1'=>$this->input->post('pembimbing1'),
                                    'dosen_pembimbing_2'=>$this->db->escape_str($this->input->post('pembimbing2')),
                                	'tanggal_input'=>date('Y-m-d H:i:s'),
                                    'username_input'=>$this->session->username);
            
        $this->db->insert('tb_skripsi',$datadb);
		$title = "Tambah Data Skripsi Mahasiswa <br />dengan ID [id=".$this->input->post('id')."]";
			$descrip = $this->fetch_description($title,$datadb);
			$this->add_audittrail("INSERT",$title,$descrip,'tb_skripsi');
			
    }
    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('l');
        $hasil=$this->upload->data();
        $exx = explode('-',$this->input->post('tgl_lulus'));
			$tanggal_lulus = $exx[2].'-'.$exx[1].'-'.$exx[0];    
            if ($hasil['file_name']==''){
                    $datadb = array('kategori_mahasiswa'=>$this->db->escape_str($this->input->post('a')),
									'mahasiswa_id'=>$this->input->post('id'),
									'stambuk'=>$this->input->post('b'),
									'tahun'=>$this->input->post('tahun'),
                                    'nim'=>$this->db->escape_str($this->input->post('c')),
                                    'nama_lengkap'=>$this->db->escape_str($this->input->post('d')),
									'jenis_kelamin'=>seo_title($this->input->post('e')),
									'agama_id'=>$this->db->escape_str($this->input->post('f')),
									'alamat'=>$this->db->escape_str($this->input->post('g')),
                                    'nomor_hp'=>$this->db->escape_str($this->input->post('h')),
									'email'=>$this->db->escape_str($this->input->post('email')),
                                    'tahun_akademik_id'=>$this->db->escape_str($this->input->post('i')),
                                    'reguler'=>$this->db->escape_str($this->input->post('j')),
                                    'ruangan_id'=>$this->db->escape_str($this->input->post('k')),
                                    'status'=>$this->db->escape_str($this->input->post('status')),
									'tanggal_input'=>date('Y-m-d H:i:s'),
                                    'username_input'=>$this->session->username);
            }else{
                    $datadb = array('kategori_mahasiswa'=>$this->db->escape_str($this->input->post('a')),
									'mahasiswa_id'=>$this->input->post('id'),
									'stambuk'=>$this->input->post('b'),
									'tahun'=>$this->input->post('tahun'),
                                    'nim'=>$this->db->escape_str($this->input->post('c')),
                                    'nama_lengkap'=>$this->db->escape_str($this->input->post('d')),
									'jenis_kelamin'=>seo_title($this->input->post('e')),
									'agama_id'=>$this->db->escape_str($this->input->post('f')),
									'alamat'=>$this->db->escape_str($this->input->post('g')),
                                    'nomor_hp'=>$this->db->escape_str($this->input->post('h')),
									'email'=>$this->db->escape_str($this->input->post('email')),
                                    'tahun_akademik_id'=>$this->db->escape_str($this->input->post('i')),
                                    'reguler'=>$this->db->escape_str($this->input->post('j')),
                                    'ruangan_id'=>$this->db->escape_str($this->input->post('k')),
									'photo'=>$hasil['file_name'],
                                    'status'=>$this->db->escape_str($this->input->post('status')),
									'tanggal_input'=>date('Y-m-d H:i:s'),
                                    'username_input'=>$this->session->username);
					
            }
        $this->db->insert('tb_mahasiswa',$datadb);
		$title = "Tambah Data Mahasiswa <br />dengan ID [id=".$this->input->post('id')."]";
			$descrip = $this->fetch_description($title,$datadb);
			$this->add_audittrail("INSERT",$title,$descrip,'tb_mahasiswa');
			$this->insert_pengguna();
    }
	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('l');
        $hasil=$this->upload->data();
            if ($hasil['file_name']==''){
                    $datadb = array('kategori_mahasiswa'=>$this->db->escape_str($this->input->post('a')),
									'mahasiswa_id'=>$this->input->post('id'),
									'stambuk'=>$this->input->post('b'),
                                    'nim'=>$this->db->escape_str($this->input->post('c')),
                                    'nama_lengkap'=>$this->db->escape_str($this->input->post('d')),
									'jenis_kelamin'=>seo_title($this->input->post('e')),
									'agama_id'=>$this->db->escape_str($this->input->post('f')),
									'alamat'=>$this->db->escape_str($this->input->post('g')),
                                    'nomor_hp'=>$this->db->escape_str($this->input->post('h')),
									'email'=>$this->db->escape_str($this->input->post('email')),
                                    'tahun_akademik_id'=>$this->db->escape_str($this->input->post('i')),
                                    'reguler'=>$this->db->escape_str($this->input->post('j')),
                                    'ruangan_id'=>$this->input->post('k'),
                                    'status'=>$this->db->escape_str($this->input->post('status')),
									'tanggal_perubahan'=>date('Y-m-d H:i:s'),
                                    'username_perubahan'=>$this->session->username);
            }else{
                    $datadb = array('kategori_mahasiswa'=>$this->db->escape_str($this->input->post('a')),
									'mahasiswa_id'=>$this->input->post('id'),
									'stambuk'=>$this->input->post('b'),
                                    'nim'=>$this->db->escape_str($this->input->post('c')),
                                    'nama_lengkap'=>$this->db->escape_str($this->input->post('d')),
									'jenis_kelamin'=>seo_title($this->input->post('e')),
									'agama_id'=>$this->db->escape_str($this->input->post('f')),
									'alamat'=>$this->db->escape_str($this->input->post('g')),
                                    'nomor_hp'=>$this->db->escape_str($this->input->post('h')),
									'email'=>$this->db->escape_str($this->input->post('email')),
                                    'tahun_akademik_id'=>$this->db->escape_str($this->input->post('i')),
                                    'reguler'=>$this->db->escape_str($this->input->post('j')),
                                    'ruangan_id'=>$this->input->post('k'),
									'photo'=>$hasil['file_name'],
                                    'status'=>$this->db->escape_str($this->input->post('status')),
									'tanggal_perubahan'=>date('Y-m-d H:i:s'),
                                    'username_perubahan'=>$this->session->username);
				}
        $this->db->where('mahasiswa_id',$this->input->post('id'));
        $this->db->update('tb_mahasiswa',$datadb);
		$title = "Update Data Mahasiswa <br />dengan ID [id=".$this->input->post('id')."]";
			$descrip = $this->fetch_description($title,$datadb);
			$this->add_audittrail("UPDATE",$title,$descrip,'tb_mahasiswa');
			$this->update_pengguna();
    }
	function list_update_dosenwali(){
        $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('l');
        $hasil=$this->upload->data();
         
                    $datadb = array('dosen_wali_id'=>$this->input->post('dosen'));
            
        $this->db->where('mahasiswa_id',$this->input->post('id'));
        $this->db->update('tb_mahasiswa',$datadb);

    }
	function insert_pengguna(){
      		$datadb = array('username'=>$this->db->escape_str($this->input->post('b')),
							'user_id'=>$this->db->escape_str($this->input->post('id')),
							'pengguna_id'=>$this->db->escape_str($this->input->post('id')),
							'nama_lengkap'=>$this->db->escape_str($this->input->post('d')),
							'password'=>hash("sha512", md5('123')),
							'level'=>'7',
							'blokir'=>'N',
							'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'=>'7',
							'blokir'=>'N',
							'nama_lengkap'=>$this->db->escape_str($this->input->post('d')),
							'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'))),
							'nama_lengkap'=>$this->db->escape_str($this->input->post('d')),
							'level'=>'7',
							'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'=>'7',
							'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'=>'7',
							'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 hapus_data($id){
        return $this->db->query("DELETE FROM tb_mahasiswa where mahasiswa_id='$id'");
    }
	function hapus_data_skripsi($id){
		$datadb = "Delete saja";
		$title = "HAPUS Data SKRIPSI Mahasiswa <br />dengan ID [id=".$id."]";
			$descrip = $this->fetch_description($title,$datadb);
			$this->add_audittrail("HAPUS",$title,$descrip,'tb_skripsi');
        return $this->db->query("DELETE FROM tb_skripsi where skripsi_id='$id'");
		
			
    }
	function list_detail($id){
        return $this->db->query("SELECT A.*,C.keterangan AS angkatan,C.tahun_akademik,
								B.keterangan AS agama ,D.username,D.password,
								E.nama_ruangan AS kelas
								FROM tb_mahasiswa A 
								LEFT JOIN tb_agama B ON A.agama_id=B.agama_id
								LEFT JOIN tb_pengguna D ON A.mahasiswa_id=D.user_id
								LEFT JOIN tb_tahun_akademik C ON A.tahun_akademik_id=C.tahun_akademik_id
								LEFT JOIN tb_ruangan E ON A.ruangan_id=E.ruangan_id
								where A.mahasiswa_id='$id'");
    }
	
	function list_detail_login($id){
        return $this->db->query("SELECT A.*,C.keterangan AS angkatan,
								B.keterangan AS agama ,D.username,D.password,
								E.nama_ruangan AS kelas
								FROM tb_mahasiswa A 
								LEFT JOIN tb_agama B ON A.agama_id=B.agama_id
								LEFT JOIN tb_pengguna D ON A.mahasiswa_id=D.user_id
								LEFT JOIN tb_angkatan C ON A.angkatan_id=C.angkatan_id
								LEFT JOIN tb_ruangan E ON A.ruangan_id=E.ruangan_id
								where A.stambuk='$id'");
    }
	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) {
		}
	}
	function export_mahasiswa(){
        $query = $this->db->query("SELECT * from tb_mahasiswa");
         
        if($query->num_rows() > 0){
            foreach($query->result() as $data){
                $hasil[] = $data;
            }
            return $hasil;
        }
    }
	//data skrpsi
	function list_skripsi($awal, $akhir){
       return $this->db->query("SELECT A.*,
									B.keterangan AS angkatan,
									C.keterangan AS agama,
									D.nama_ruangan,
									E.nama_lengkap AS nama_dosen,
									F.tanggal_pengajuan,
									F.judul_skripsi,
									F.skripsi_id
								FROM tb_mahasiswa A
								LEFT JOIN  tb_skripsi F ON A.mahasiswa_id=F.mahasiswa_id
								LEFT JOIN tb_tahun_akademik B ON A.tahun_akademik_id=B.tahun_akademik_id
								LEFT JOIN tb_agama C ON A.agama_id=C.agama_id
								LEFT JOIN tb_ruangan D ON A.ruangan_id=D.ruangan_id
								LEFT JOIN tb_dosen E ON A.dosen_wali_id=E.dosen_id
								ORDER BY A.stambuk DESC,A.ruangan_id ASC LIMIT $awal, $akhir");
    }
	function list_update_skripsi(){
		$exx = explode('-',$this->input->post('tanggal_skripsi'));
		$tanggal_pengajuan = $exx[2].'-'.$exx[1].'-'.$exx[0];  
		 $datadb = array('mahasiswa_id'=>$this->input->post('id'),
									'judul_skripsi'=>$this->input->post('judul'),
									'tanggal_pengajuan'=>$tanggal_pengajuan,
									'dosen_pembimbing_1'=>$this->input->post('pembimbing1'),
                                    'dosen_pembimbing_2'=>$this->db->escape_str($this->input->post('pembimbing2')),
                                	'tanggal_perubahan'=>date('Y-m-d H:i:s'),
                                    'username_perubahan'=>$this->session->username);
                   
        $this->db->where('skripsi_id',$this->input->post('idskripsi'));
        $this->db->update('tb_skripsi',$datadb);
		$title = "Update Data Judul Skripsi <br />dengan ID [id=".$this->input->post('idskripsi')."]";
			$descrip = $this->fetch_description($title,$datadb);
			$this->add_audittrail("UPDATE",$title,$descrip,'tb_skripsi');
	
    }
	function list_detail_skripsi($id){
        return $this->db->query("SELECT A.*,
									B.keterangan AS angkatan,
									C.keterangan AS agama,
									D.nama_ruangan,
									E.nama_lengkap AS nama_dosen,
									F.tanggal_pengajuan,
									F.judul_skripsi,
									F.skripsi_id,
									F.dosen_pembimbing_1,
									F.dosen_pembimbing_2
								FROM tb_mahasiswa A
								LEFT JOIN  tb_skripsi F ON A.mahasiswa_id=F.mahasiswa_id
								LEFT JOIN tb_tahun_akademik B ON A.tahun_akademik_id=B.tahun_akademik_id
								LEFT JOIN tb_agama C ON A.agama_id=C.agama_id
								LEFT JOIN tb_ruangan D ON A.ruangan_id=D.ruangan_id
								LEFT JOIN tb_dosen E ON A.dosen_wali_id=E.dosen_id
								where A.mahasiswa_id='$id'");
    }
	function list_data_mahasiswa($awal, $akhir){
        return $this->db->query("SELECT A.*,C.keterangan AS angkatan,C.tahun_akademik,
								B.keterangan AS agama ,D.username,D.password,
								E.nama_ruangan AS kelas
								FROM tb_mahasiswa A 
								LEFT JOIN tb_agama B ON A.agama_id=B.agama_id
								LEFT JOIN tb_pengguna D ON A.mahasiswa_id=D.user_id
								LEFT JOIN tb_tahun_akademik C ON A.tahun_akademik_id=C.tahun_akademik_id
								LEFT JOIN tb_ruangan E ON A.ruangan_id=E.ruangan_id
								ORDER BY A.stambuk DESC,A.ruangan_id ASC
								LIMIT $awal, $akhir");
    }

}