<?php 
	class DataMahasiswa extends mysql{

	function get_all_trans_total($kolom,$a,$type, $tglawal, $tglakhir, $idgrup, $jurusan, $idOperator){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			if ($idgrup == 'Pendaftaran Mahasiswa') {
				$idgrup = 'G0015';	
			
	
					if ($tglawal != "" and $tglakhir != "" and $idgrup != "" and $jurusan != "" and $idOperator != ""){
						$sql = "select tb1.*, tb2.*, tb3.Des, (select sum(total) from tbmastertrxpendaftaran where (date(tglInput) between '".$tglawal."' and '".$tglakhir."') and (idgrup = '".$idgrup."')) as total2 from tbmastertrxpendaftaran tb1 INNER JOIN tbpendaftaran tb2 on tb1.noPendaftaran = tb2.NoPendaftaran INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup where (date(tb1.tglInput) between '".$tglawal."' and '".$tglakhir."') and tb1.idgrup = '".$idgrup."' and tb2.Jurusan = '".$jurusan."' and tb1.idOperator = '".$idOperator."' group by tb1.noPendaftaran";
					}else if ($tglawal != "" and $tglakhir != "" and $idgrup != "" and $jurusan == "" and $idOperator != "") {
						//jurusan
						$sql = "select tb1.*, tb2.*, tb3.Des, (select sum(total) from tbmastertrxpendaftaran where (date(tglInput) between '".$tglawal."' and '".$tglakhir."') and (idgrup = '".$idgrup."')) as total2 from tbmastertrxpendaftaran tb1 INNER JOIN tbpendaftaran tb2 on tb1.noPendaftaran = tb2.NoPendaftaran INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup where (date(tb1.tglInput) between '".$tglawal."' and '".$tglakhir."') and tb1.idgrup = '".$idgrup."' and tb1.idOperator = '".$idOperator."' group by tb1.noPendaftaran";			
					}else if ($tglawal != "" and $tglakhir != "" and $idgrup != "" and $jurusan != "" and $idOperator == "") {
						//jurusan
						$sql = "select tb1.*, tb2.*, tb3.Des, (select sum(total) from tbmastertrxpendaftaran where (date(tglInput) between '".$tglawal."' and '".$tglakhir."') and (idgrup = '".$idgrup."')) as total2 from tbmastertrxpendaftaran tb1 INNER JOIN tbpendaftaran tb2 on tb1.noPendaftaran = tb2.NoPendaftaran INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup where (date(tb1.tglInput) between '".$tglawal."' and '".$tglakhir."') and tb1.idgrup = '".$idgrup."'  and tb2.Jurusan = '".$jurusan."' group by tb1.noPendaftaran";			

					}else {
						$sql = "select tb1.*, tb2.*, tb3.Des, (select sum(total) from tbmastertrxpendaftaran where (date(tglInput) between '".$tglawal."' and '".$tglakhir."') and (idgrup = '".$idgrup."')) as total2 from tbmastertrxpendaftaran tb1 INNER JOIN tbpendaftaran tb2 on tb1.noPendaftaran = tb2.NoPendaftaran INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup where (date(tb1.tglInput) between '".$tglawal."' and '".$tglakhir."') and tb1.idgrup = '".$idgrup."' group by tb1.noPendaftaran";
					}
					
			}else{
				
				
				if ($tglawal != "" and $tglakhir != "" and $idgrup != "" and $jurusan != "" and $idOperator != ""){
						$sql = "select tb1.*, tb2.Nama, tb2.Jurusan, tb3.Des, (select sum(total) from tbmastertrxpendaftaran where (date(tglInput) between '".$tglawal."' and '".$tglakhir."') and idgrup = 'G0022') as total2, tb4.NPM from tbmastertrxpendaftaran tb1 INNER JOIN tbpendaftaran tb2 on tb1.noPendaftaran = tb2.NoPendaftaran INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tbmahasiswa tb4 on tb1.noPendaftaran = tb4.noPendaftaran where (date(tb1.tglInput) between '".$tglawal."' and '".$tglakhir."') and tb1.idgrup = 'G0022' and tb2.Jurusan = '".$jurusan."' and tb1.idOperator = '".$idOperator."' group by tb1.noPendaftaran";
					}else if ($tglawal != "" and $tglakhir != "" and $idgrup != "" and $jurusan == "" and $idOperator != "") {
						//jurusan
						$sql = "select tb1.*, tb2.Nama, tb2.Jurusan, tb3.Des, (select sum(total) from tbmastertrxpendaftaran where (date(tglInput) between '".$tglawal."' and '".$tglakhir."') and idgrup = 'G0022') as total2, tb4.NPM from tbmastertrxpendaftaran tb1 INNER JOIN tbpendaftaran tb2 on tb1.noPendaftaran = tb2.NoPendaftaran INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tbmahasiswa tb4 on tb1.noPendaftaran = tb4.noPendaftaran where (date(tb1.tglInput) between '".$tglawal."' and '".$tglakhir."') and tb1.idgrup = 'G0022' and tb1.idOperator = '".$idOperator."' group by tb1.noPendaftaran";			
					}else if ($tglawal != "" and $tglakhir != "" and $idgrup != "" and $jurusan != "" and $idOperator == "") {
						//jurusan
						$sql = "select tb1.*, tb2.Nama, tb2.Jurusan, tb3.Des, (select sum(total) from tbmastertrxpendaftaran where (date(tglInput) between '".$tglawal."' and '".$tglakhir."') and idgrup = 'G0022') as total2, tb4.NPM from tbmastertrxpendaftaran tb1 INNER JOIN tbpendaftaran tb2 on tb1.noPendaftaran = tb2.NoPendaftaran INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tbmahasiswa tb4 on tb1.noPendaftaran = tb4.noPendaftaran where (date(tb1.tglInput) between '".$tglawal."' and '".$tglakhir."') and tb1.idgrup = 'G0022' and tb2.Jurusan = '".$jurusan."' group by tb1.noPendaftaran";			

					}else {
						$sql = "select tb1.*, tb2.Nama, tb2.Jurusan, tb3.Des, (select sum(total) from tbmastertrxpendaftaran where (date(tglInput) between '".$tglawal."' and '".$tglakhir."') and idgrup = 'G0022') as total2, tb4.NPM from tbmastertrxpendaftaran tb1 INNER JOIN tbpendaftaran tb2 on tb1.noPendaftaran = tb2.NoPendaftaran INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tbmahasiswa tb4 on tb1.noPendaftaran = tb4.noPendaftaran where (date(tb1.tglInput) between '".$tglawal."' and '".$tglakhir."') and tb1.idgrup = 'G0022' group by tb1.noPendaftaran";
					}
					
				}
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_all_by_jurusan_angkatan($kolom,$a,$type, $jurusan, $angkatan, $klas){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
				if ($jurusan == 'IESP'){
					$kdjurusan = '31';
				}else if ($jurusan == 'MANAJEMEN'){
					$kdjurusan = '32';
				}else {
					$kdjurusan = '33';	
				}
				
				if ($jurusan != '' and $klas != ''){
					$sql = "select * from tbmahasiswa where substring(NPM, 3, 2) = '".$kdjurusan."' and substring(NPM, 1, 2) = '".$angkatan."' and substring(NPM, 5, 3) = '".$klas."'";
				} else if ($jurusan == '' and $klas != ''){
					$sql = "select * from tbmahasiswa where substring(NPM, 1, 2) = '".$angkatan."' and substring(NPM, 5, 3) = '".$klas."'";
				} else if ($jurusan != '' and $klas == ''){
					$sql = "select * from tbmahasiswa where substring(NPM, 3, 2) = '".$kdjurusan."' and substring(NPM, 1, 2) = '".$angkatan."'";	
				} else {
					$sql = "select * from tbmahasiswa where substring(NPM, 1, 2) = '".$angkatan."'";
				} 
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		
		function get_all_by_jurusan($kolom,$a,$type, $jurusan){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbmahasiswa where substring(NPM, 3, 2) = '".$jurusan."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_all_angkatan($kolom,$a,$type, $jurusan){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select substring(NPM, 1, 2) as angkatan  from tbmahasiswa group by substring(NPM, 1, 2) asc";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_all_angkatan_2($kolom,$a,$type, $jurusan){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select substring(NPM, 1, 2) as angkatan  from tbmahasiswa group by substring(NPM, 1, 2) desc";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_tahun_pendaftaran($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select substring(NoPendaftaran, 1, 2) as tahun  from tbpendaftaran group by substring(NoPendaftaran, 1, 2) asc";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_all_by_angkatan($kolom,$a,$type, $angkatan){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbmahasiswa where substring(NPM, 1, 2) = '".$angkatan."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_angkatan($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select substring(NPM, 1, 2) as angkatan from tbmahasiswa group by substring(NPM, 1, 2)  desc";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_mahasiswa_by_angkatan($kolom,$a,$type, $angkatan){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select substring(NPM, 3, 2) as kd from tbmahasiswa where substring(NPM, 1, 2) = '".$angkatan."'  group by substring(NPM, 3, 2)  desc";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		
		function get_mahasiswa_by_tgl_pd($kolom,$a,$type, $tgl){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "SELECT * FROM tbpendaftaran WHERE tgl = '".$tgl."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_mahasiswa_by_tgl_du($kolom,$a,$type, $tgl){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "SELECT * FROM tbmahasiswa WHERE isAktif ='1' and tglDaftarUlang = '".$tgl."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_last_NPM_by_Jurusan($kolom,$Jurusan, $Tahun, $type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbmahasiswa where Jurusan = '".$Jurusan."' and substring(NPM, 1, 2) = '".$Tahun."'  order by NPM desc";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);

		}

		function get_pendaftaran_single_by_id($kolom,$id){
			if($this->cek_result != "on"){
			$sql = "SELECT * FROM tbpendaftaran WHERE NoPendaftaran ='".$id."'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		function get_mhs_by_nopendaftaran($kolom,$id){
			if($this->cek_result != "on"){
			$sql = "select * from tbmahasiswa where noPendaftaran = ".$id."'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}

		function get_all_pendaftaran($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbpendaftaran order by NoPendaftaran desc";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function add_pendaftaran($NoPendaftaran,$Nama,$Jurusan, $TempatLahir, $TglLahir, $JenisKelamin, $Agama, $Alamat, $NoTelp, $Email, $AsalSekolah, $NamaorangTua, $desa, $kecamatan, $kabupaten, $provinsi){
			  $sql = "INSERT INTO tbpendaftaran (NoPendaftaran, Nama, Jurusan, TempatLahir, TglLahir, JK, Agama, Alamat, NoTelp, Email, AsalSekolah, tgl, NamaorangTua, desa, kecamatan, kabupaten, provinsi) values ('".$NoPendaftaran."','".$Nama."','".$Jurusan."', '".$TempatLahir."', '".$TglLahir."', '".$JenisKelamin."', '".$Agama."', '".$Alamat."', '".$NoTelp."', '".$Email."', '".$AsalSekolah."', curdate(), '".$NamaorangTua."', '".$desa."', '".$kecamatan."', '".$kabupaten."', '".$provinsi."')";
			  $this->query = $this-> execute($sql,''); 
		}

		function get_last_pendaftaran($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select NoPendaftaran from tbpendaftaran order by NoPendaftaran desc";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_mahasiswa_all($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "SELECT * FROM tbmahasiswa WHERE isAktif ='1'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_mahasiswa_by_npm($kolom,$a,$type, $npm){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "SELECT * FROM tbmahasiswa WHERE isAktif ='1' and NPM = '".$npm."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_mahasiswa_single_by_id($kolom,$id){
			if($this->cek_result != "on"){
			$sql = "SELECT * FROM tbmahasiswa WHERE id ='".$id."'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		function get_npm_single_by_id($kolom,$id){
			if($this->cek_result != "on"){
			$sql = "SELECT * FROM tbmahasiswa WHERE NPM ='".$id."'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}

		function get_mahasiswa_single_by_npm_trx($kolom,$npm){
			if($this->cek_result != "on"){
			$sql = "SELECT * FROM tbmahasiswa WHERE NPM ='".$npm."'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}

		function get_mahasiswa_single_by_NPM($kolom,$npm){
			if($this->cek_result != "on"){
			$sql = "SELECT * FROM tbmahasiswa WHERE NPM ='".$npm."'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		function edit_mahasiswa($id, $NPM,$Nama,$Jurusan, $TempatLahir, $TglLahir, $JenisKelamin, $Agama, $Alamat, $NoTelp, $Email, $AsalSekolah, $StatusPerkawinan, $StatusPekerjaan, $Perusahaan, $Jabatan, $AlamatPerusahaan, $StatusMahasiswa, $PerguruanTinggiAsal, $ProgramStudi, $Jenjang, $SKSDiakui){
			  $sql = "update tbmahasiswa set NPM = '".$NPM."', Nama = '".$Nama."', Jurusan='".$Jurusan."', TempatLahir = '".$TempatLahir."', TglLahir = '".$TglLahir."', JenisKelamin = '".$JenisKelamin."', Agama = '".$Agama."', Alamat = '".$Alamat."', NoTelp = '".$NoTelp."', Email = '".$Email."', AsalSekolah = '".$AsalSekolah."', StatusPerkawinan = '".$StatusPerkawinan."', StatusPekerjaan = '".$StatusPekerjaan."', Perusahaan = '".$Perusahaan."', Jabatan = '".$Jabatan."', AlamatPerusahaan = '".$AlamatPerusahaan."', StatusMahasiswa = '".$StatusMahasiswa."', PerguruanTinggiAsal = '".$PerguruanTinggiAsal."', ProgramStudi = '".$ProgramStudi."', Jenjang = '".$Jenjang."', SKSDiakui = '".$SKSDiakui."' where id = '".$id."'";
			  $this->query = $this-> execute($sql,''); 
		}
		function add_mahasiswa($NPM,$Nama,$Jurusan, $TempatLahir, $TglLahir, $JenisKelamin, $Agama, $Alamat, $NoTelp, $Email, $AsalSekolah, $StatusPerkawinan, $StatusPekerjaan, $Perusahaan, $Jabatan, $AlamatPerusahaan, $StatusMahasiswa, $PerguruanTinggiAsal, $ProgramStudi, $Jenjang, $SKSDiakui, $noPendaftaran){
			  $sql = "INSERT INTO tbmahasiswa (NPM, Nama, Jurusan, TempatLahir, TglLahir, JenisKelamin, Agama, Alamat, NoTelp, Email, AsalSekolah, StatusPerkawinan, StatusPekerjaan, Perusahaan, Jabatan, AlamatPerusahaan, StatusMahasiswa, PerguruanTinggiAsal, ProgramStudi, Jenjang, SKSDiakui, isEksekutif, isAktif, tglDaftarUlang, noPendaftaran) values ('".$NPM."','".$Nama."','".$Jurusan."', '".$TempatLahir."', '".$TglLahir."', '".$JenisKelamin."', '".$Agama."', '".$Alamat."', '".$NoTelp."', '".$Email."', '".$AsalSekolah."', '".$StatusPerkawinan."', '".$StatusPekerjaan."', '".$Perusahaan."', '".$Jabatan."', '".$AlamatPerusahaan."', '".$StatusMahasiswa."', '".$PerguruanTinggiAsal."', '".$ProgramStudi."', '".$Jenjang."', '".$SKSDiakui."', '1', '1', curdate(), '".$noPendaftaran."')";
			  $this->query = $this-> execute($sql,''); 
		}
		function nonaktif_mahasiswa($id,$status){
			  $sql = "update tbmahasiswa set isAktif ='".$status."' where id='".$id."'";
			  $this->query = $this-> execute($sql,''); 
		}
		function delete_pendaftaran($id){
			  $sql = "update tbmastertrxpendaftaran set total = '', idGrup = '', noPendaftaran = '' where NoTRX = '".$id."'";
			  $this->query = $this-> execute($sql,''); 
		}
		function delete_pendaftaran_detil($id){
			  $sql = "delete from tbdetiltrxpendaftaran where NoTRX = '".$id."'";
			  $this->query = $this-> execute($sql,''); 
		}
		function delete_mahasiswa($id){
			  $sql = "delete from tbmahasiswa where noPendaftaran = '".$id."'";
			  $this->query = $this-> execute($sql,''); 
		}
		
		
	}
?>