<?php 
	class dataUjian extends mysql{
		function get_tahun($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select tahun from tbuts group by tahun";
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_data_uts_laporan($kolom,$a,$type, $semester, $tahun){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbuts where hadir <> 0 and sm = '".$semester."' and tahun = '".$tahun."' group by dosen";
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_data_uts_nama($kolom,$a,$type, $nama, $semester, $tahun){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select tbuts.*, (select sum(hadir) from tbuts where dosen = '".$nama."' and sm = '".$semester."' and tahun = '".$tahun."' group by dosen) as jmlhadir, tbnorek.norek from tbuts left join tbnorek on tbuts.dosen = tbnorek.dosen where tbuts.dosen = '".$nama."' and tbuts.hadir <> 0 group by tbuts.dosen";
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_data_uts_nama_laporan($kolom,$a,$type, $nama, $semester, $tahun){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbuts where dosen = '".$nama."' and sm = '".$semester."' and tahun = '".$tahun."'";
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_koreksi($kolom){
			if($this->cek_result != "on"){
			$sql = "select * from tbsetkoreksi";
			//echo $sql;
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		function get_jadwal_single_by_id($kolom,$id){
			if($this->cek_result != "on"){
			$sql = "select * from tbuts where id='".$id."'";
			//echo $sql;
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		function get_uts_id($kolom, $id){
			if($this->cek_result != "on"){
			$sql = "select * from tbuts where id = '".$id."'";
			//echo $sql;
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		function update_koreksi($koreksiuts, $naskahuts, $koreksiuas, $naskahuas, $koreksiutsnon, $naskahutsnon, $koreksiuasnon, $naskahuasnon){
		    $sql = "update tbsetkoreksi set koreksiuts = '".$koreksiuts."', naskahuts = '".$naskahuts."', koreksiuas = '".$koreksiuas."', naskahuas = '".$naskahuas."', koreksiutsnon = '".$koreksiutsnon."', naskahutsnon = '".$naskahutsnon."', koreksiuasnon = '".$koreksiuasnon."', naskahuasnon = '".$naskahuasnon."'";
		    $this->query = $this-> execute($sql,''); 
		}
		function update_uts($id, $hadir, $seharusnya, $tidakhadir, $tambahan){
		    $sql = "update tbuts set hadir = '".$hadir."', seharusnya = '".$seharusnya."', tidakhadir = '".$tidakhadir."', tambahan = '".$tambahan."' where id = '".$id."'";
		    $this->query = $this-> execute($sql,''); 
		}
		function get_data_uts($kolom,$a,$type, $hari, $semester, $tahun, $program){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			if($program == 'REGULER'){
				if($hari == 'SEMUA HARI'){
					$sql = "select * from tbuts where sm = '".$semester."' and tahun = '".$tahun."' and substring(tbuts.kelas, 1, 1) <> 'E'";
				}else{
					$sql = "select * from tbuts where hari = '".$hari."' and sm = '".$semester."' and tahun = '".$tahun."' and substring(tbuts.kelas, 1, 1) <> 'E'";
				}
			}elseif($program == 'SEMUA'){
				if($hari == 'SEMUA HARI'){
					$sql = "select * from tbuts where sm = '".$semester."' and tahun = '".$tahun."'";
				}else{
					$sql = "select * from tbuts where hari = '".$hari."' and sm = '".$semester."' and tahun = '".$tahun."'";
				}
			}else{
				if($hari == 'SEMUA HARI'){
					$sql = "select * from tbuts where sm = '".$semester."' and tahun = '".$tahun."' and substring(tbuts.kelas, 1, 1) = 'E'";
				}else{
					$sql = "select * from tbuts where hari = '".$hari."' and sm = '".$semester."' and tahun = '".$tahun."' and substring(tbuts.kelas, 1, 1) = 'E'";
				}
			}
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_data_uts_all($kolom,$a,$type, $semester, $tahun){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbuts where sm = '".$semester."' and tahun = '".$tahun."' group by dosen";
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function add_jadwal_uts($idmatakuliah, $matakuliah, $dosen, $sks, $semester, $jurusan, $kelas, $hari, $ruang, $waktu, $sm, $tahun){
		    $sql = "insert into tbuts (idmatakuliah, matakuliah, dosen, sks, semester, jurusan, kelas, hari, ruang, waktu, sm, tahun, hadir, seharusnya, tidakhadir, tambahan) values ('".$idmatakuliah."', '".$matakuliah."', '".$dosen."', '".$sks."', '".$semester."', '".$jurusan."', '".$kelas."', '".$hari."', '".$ruang."', '".$waktu."', '".$sm."', '".$tahun."', 0, 0, 0, 0)";
			//echo $sql;
		    $this->query = $this-> execute($sql,''); 
		}
		function edit_jadwal_uts($id, $idmatakuliah, $matakuliah, $dosen, $sks, $semester, $jurusan, $kelas, $hari, $ruang, $waktu, $sm, $tahun){
		    $sql = "update tbuts set idmatakuliah = '".$idmatakuliah."', matakuliah= '".$matakuliah."', dosen = '".$dosen."', sks='".$sks."', semester='".$semester."', jurusan='".$jurusan."', kelas='".$kelas."', hari='".$hari."', ruang='".$ruang."', waktu='".$waktu."', sm='".$sm."', tahun='".$tahun."' where id ='".$id."'";
		    $this->query = $this-> execute($sql,''); 
		}
		function delete_jadwal($id){
		    $sql = "delete from tbuts where id = '".$id."'";
		    $this->query = $this-> execute($sql,''); 
		}
	}
?>	