<?php 
	class DataProposal extends mysql{
		function get_tahun_skripsi($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select substr(year (tanggalProposal), 1, 4) as tahun from tbskripsi  group by year (tanggalProposal)";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_tahun_proposal($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select substr(year (tanggalProposal), 1, 4) as tahun from tbproposal  group by year (tanggalProposal)";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_tahun_skripsi2($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select left(tanggalProposal, 4)  as tahun from tbskripsi  group by left(tanggalProposal, 4)";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_skripsi_total($kolom,$a,$type, $tglAwal, $tglAkhir, $kelas, $jurusan, $kegiatan){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			if ($kegiatan == 'proposal'){
				if ($tglAwal != "" and $tglAkhir != "" and $kelas != "" and $jurusan != "") {
					$sql = "select * from tbproposal where tanggalProposal >= '".$tglAwal."' and tanggalProposal <= '".$tglAkhir."' and substr(NPM, 3, 2) = '".$jurusan."' and substr(NPM, 5, 3) = '".$kelas."'";
				}else if ($tglAwal != "" and $tglAkhir != "" and $kelas != "" and $jurusan == "") {
					$sql = "select * from tbproposal where tanggalProposal >= '".$tglAwal."' and tanggalProposal <= '".$tglAkhir."'  and substr(NPM, 5, 3) = '".$kelas."'";
				}
			}else if ($kegiatan == 'pending'){
				if ($tglAwal != "" and $tglAkhir != "" and $kelas != "" and $jurusan != "") {
					$sql = "select * from tbproposal where idMhs NOT IN (SELECT idMhs FROM tbskripsi) and tanggalProposal >= '".$tglAwal."' and tanggalProposal <= '".$tglAkhir."' and substr(NPM, 3, 2) = '".$jurusan."' and substr(NPM, 5, 3) = '".$kelas."'";
				}else if ($tglAwal != "" and $tglAkhir != "" and $kelas != "" and $jurusan == "") {
					$sql = "select * from tbproposal where idMhs NOT IN (SELECT idMhs FROM tbskripsi) and tanggalProposal >= '".$tglAwal."' and tanggalProposal <= '".$tglAkhir."'  and substr(NPM, 5, 3) = '".$kelas."'";
				}			
			}else {
				if ($tglAwal != "" and $tglAkhir != "" and $kelas != "" and $jurusan != "") {
					$sql = "select * from tbskripsi where tanggalProposal >= '".$tglAwal."' and tanggalProposal <= '".$tglAkhir."' and substr(NPM, 3, 2) = '".$jurusan."' and substr(NPM, 5, 3) = '".$kelas."'";
				}else if ($tglAwal != "" and $tglAkhir != "" and $kelas != "" and $jurusan == "") {
					$sql = "select * from tbskripsi where tanggalProposal >= '".$tglAwal."' and tanggalProposal <= '".$tglAkhir."'  and substr(NPM, 5, 3) = '".$kelas."'";
				}			
			}
			
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

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

		function get_skripsi($kolom,$a,$type, $tahun){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbskripsi where year (tanggalProposal) = '".$tahun."' order by id desc";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_proposal($kolom,$a,$type, $tahun){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbproposal where year (tanggalProposal) = '".$tahun."' order by id desc";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_penugasan($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbpenugasan";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_penugasan_single_by_id($kolom,$id){
			if($this->cek_result != "on"){
			$sql = "select * from tbpenugasan where idMhs='".$id."' order by id desc";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		
		function get_proposal_single_by_id($kolom,$id){
			if($this->cek_result != "on"){
			$sql = "select * from tbproposal where idMhs='".$id."'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}

		function get_skripsi_single_by_id($kolom,$id){
			if($this->cek_result != "on"){
			$sql = "select * from tbskripsi where idMhs='".$id."'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		function delete_skripsi($idMhs){
		    $sql = "delete from tbskripsi where idMhs = '".$idMhs."'";
		    $this->query = $this-> execute($sql,''); 
		}

		function add_penugasan($idMhs, $NPM, $namaMhs, $noUndangan, $judul, $idPendamping1, $namaPendamping1, $idPendamping2, $namaPendamping2){
		    $sql = "INSERT INTO tbpenugasan (idMhs, NPM, namaMhs, noUndanganProposal, judul, idPendamping1, namaPendamping1, idPendamping2, namaPendamping2) values ('".$idMhs."', '".$NPM."', '".$namaMhs."', '".$noUndangan."', '".$judul."', '".$idPendamping1."', '".$namaPendamping1."', '".$idPendamping2."', '".$namaPendamping2."')";
		    $this->query = $this-> execute($sql,''); 
		}
		function edit_proposal($NPM, $noUndangan, $judul, $idPendamping1, $namaPendamping1, $idPendamping2, $namaPendamping2, $idPendamping3, $namaPendamping3, $tanggalProposal, $waktu, $tempat, $nipPendamping1, $nipPendamping2, $nipPendamping3){
		    $sql = "UPDATE tbproposal SET noUndanganProposal = '".$noUndangan."', judul = '".$judul."', idPendamping1 = '".$idPendamping1."', namaPendamping1 = '".$namaPendamping1."', idPendamping2 = '".$idPendamping2."', namaPendamping2 = '".$namaPendamping2."', idPendamping3 = '".$idPendamping3."', namaPendamping3 = '".$namaPendamping3."', tanggalProposal = '".$tanggalProposal."', waktu = '".$waktu."', tempat = '".$tempat."', nipPendamping1 = '".$nipPendamping1."', nipPendamping2 = '".$nipPendamping2."', nipPendamping3 = '".$nipPendamping3."' where NPM = '".$NPM."'";
		    $this->query = $this-> execute($sql,''); 
		}
				
		function add_proposal($idMhs, $NPM, $namaMhs, $noUndangan, $judul, $idPendamping1, $namaPendamping1, $idPendamping2, $namaPendamping2, $idPendamping3, $namaPendamping3, $tanggalProposal, $waktu, $ttd, $nipttd, $tempat, $uang1, $uang2, $uang3, $ttdPenguji, $nipPenguji, $honor1, $honor2, $ttdkejur, $nipkejur, $ttdwadek2, $nipwadek2, $nipPendamping1, $nipPendamping2, $nipPendamping3){
		    $sql = "INSERT INTO tbproposal (idMhs, NPM, namaMhs, noUndanganProposal, judul, idPendamping1, namaPendamping1, idPendamping2, namaPendamping2, idPendamping3, namaPendamping3, tanggalProposal, waktu, isAktif, ttd, nip, tempat, ttdPenguji, nipPenguji, uang1, uang2, uang3, honor1, honor2, ttdkejur, nipkejur, ttdwadek2, nipwadek2, nipPendamping1, nipPendamping2, nipPendamping3) values ('".$idMhs."', '".$NPM."', '".$namaMhs."', '".$noUndangan."', '".$judul."', '".$idPendamping1."', '".$namaPendamping1."', '".$idPendamping2."', '".$namaPendamping2."', '".$idPendamping3."', '".$namaPendamping3."', '".$tanggalProposal."', '".$waktu."', '1', '".$ttd."', '".$nipttd."', '".$tempat."', '".$ttd."', '".$nipPenguji."', '".$uang1."', '".$uang2."', '".$uang3."', '".$honor1."', '".$honor2."', '".$ttdkejur."', '".$nipkejur."', '".$ttdwadek2."', '".$nipwadek2."', '".$nipPendamping1."', '".$nipPendamping2."', '".$nipPendamping3."')";
		    $this->query = $this-> execute($sql,''); 
		}
		function add_skripsi($idMhs, $NPM, $namaMhs, $noUndangan, $judul, $idPendamping1, $namaPendamping1, $idPendamping2, $namaPendamping2, $idPendamping3, $namaPendamping3, $idPendamping4, $namaPendamping4, $idPendamping5, $namaPendamping5, $tanggalProposal, $waktu, $tempat, $wadek2, $nipwadek2, $bendahara, $nipbendahara, $uanglelah1, $uanglelah2, $uanglelah3, $uanglelah4, $uanglelah5, $nipketuaPenguji, $nipsekretaris, $wadek1, $nipwadek1){
		    $sql = "INSERT INTO tbskripsi (idMhs, NPM, namaMhs, noUndanganProposal, judul, idPendamping1, namaPendamping1, idPendamping2, namaPendamping2, idPendamping3, namaPendamping3, idPendamping4, namaPendamping4, idPendamping5, namaPendamping5, tanggalProposal, waktu, isAktif, tempat, nipPendamping1, nipPendamping2, uangLelah1, uangLelah2, uangLelah3, uangLelah4, uangLelah5, wadek2, nipWadek2, bendahara, nipBendahara, wadek1, nipwadek1) values ('".$idMhs."', '".$NPM."', '".$namaMhs."', '".$noUndangan."', '".$judul."', '".$idPendamping1."', '".$namaPendamping1."', '".$idPendamping2."', '".$namaPendamping2."', '".$idPendamping3."', '".$namaPendamping3."', '".$idPendamping4."', '".$namaPendamping4."', '".$idPendamping5."', '".$namaPendamping5."', '".$tanggalProposal."', '".$waktu."', '1', '".$tempat."', '".$nipketuaPenguji."', '".$nipsekretaris."', '".$uanglelah1."', '".$uanglelah2."', '".$uanglelah3."', '".$uanglelah4."', '".$uanglelah5."', '".$wadek2."', '".$nipwadek2."', '".$bendahara."', '".$nipbendahara."', '".$wadek1."', '".$nipwadek1."')";
			$this->query = $this-> execute($sql,''); 
		}
		
				
	}
?>