<?php 
	class dataabsen extends mysql{
		function get_tahun_absen($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select tanggal from tbabsendosen group by  substr(0, 4, tanggal)";
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_riwayat_absen($kolom,$a,$type, $tahun){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select tanggal, count(id) as jumlah , sum(hadir) as hadir from tbabsendosen where substr(tanggal, 1, 4)= '".$tahun."'  group by tanggal order by id desc";
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_setting($kolom){
			if($this->cek_result != "on"){
			$sql = "select * from tbsettingsempek";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		function get_dosen_sempek($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tempbagi where dosen is not null group by dosen desc";
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_data_jadwal_filter($kolom,$a,$type, $semester, $tahun, $jurusan, $program, $tanggal){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			
			if ($program == 'REGULER'){
				if ($jurusan == 'AKUNTANSI'){
						$sql = "select * from tbabsendosen where sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'D' and tanggal = '".$tanggal."'";

				}elseif($jurusan == 'IESP'){
						$sql = "select * from tbabsendosen where sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'B' and tanggal = '".$tanggal."'";
				}elseif($jurusan == 'MANAJEMEN'){
						$sql = "select * from tbabsendosen where sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) in ('C', 'S', 'K', 'P') and tanggal = '".$tanggal."'";
				}else{
						$sql = "select * from tbabsendosen where sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) <> 'E' and tanggal = '".$tanggal."'";
				}
			}elseif ($program == 'SEMUA'){
				if ($jurusan == 'AKUNTANSI'){
						$sql = "select * from tbabsendosen where sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) in ('D', 'E') and tanggal = '".$tanggal."'";

				}elseif($jurusan == 'IESP'){
						$sql = "select * from tbabsendosen where sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) in ('B', 'E') and tanggal = '".$tanggal."'";
				}elseif($jurusan == 'MANAJEMEN'){
						$sql = "select * from tbabsendosen where sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) in ('C', 'S', 'K', 'P', 'E') and tanggal = '".$tanggal."'";
				}else{
						$sql = "select * from tbabsendosen where sm = '".$semester."' and tahun ='".$tahun."' and tanggal = '".$tanggal."'";
				}
			}else{
				if ($jurusan == 'AKUNTANSI'){
						$sql = "select * from tbabsendosen where sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'E' and tanggal = '".$tanggal."'";
	
				}elseif($jurusan == 'IESP'){
						$sql = "select * from tbabsendosen where sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'E' and tanggal = '".$tanggal."'";
				}elseif($jurusan == 'MANAJEMEN'){
						$sql = "select * from tbabsendosen where sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'E' and tanggal = '".$tanggal."'";
				}else{
						$sql = "select * from tbabsendosen where sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'E' and tanggal = '".$tanggal."'";
				}
				
			}
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_data_jadwal_rekap($kolom,$a,$type, $semester, $tahun, $jurusan, $program, $tanggalawal, $tanggalakhir, $hari){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			if($hari == 'SEMUA'){
				if ($program == 'REGULER'){
					if ($jurusan == 'AKUNTANSI'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'D' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
	
					}elseif($jurusan == 'IESP'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'B' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}elseif($jurusan == 'MANAJEMEN'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) in ('C', 'S', 'K', 'P') and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}else{
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) <> 'E' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}
				}elseif ($program == 'SEMUA'){
					if ($jurusan == 'AKUNTANSI'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) in ('D', 'E') and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
	
					}elseif($jurusan == 'IESP'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) in ('B', 'E') and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}elseif($jurusan == 'MANAJEMEN'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) in ('C', 'S', 'K', 'P,' 'E') and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}else{
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}
				}else{
					if ($jurusan == 'AKUNTANSI'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'E' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
		
					}elseif($jurusan == 'IESP'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'E' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}elseif($jurusan == 'MANAJEMEN'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'E' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}else{
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'E' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}
					
				}
			}else{
				if ($program == 'REGULER'){
					if ($jurusan == 'AKUNTANSI'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'D' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."' and hari = '".$hari."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
	
					}elseif($jurusan == 'IESP'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'B' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."' and hari = '".$hari."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}elseif($jurusan == 'MANAJEMEN'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) in ('C', 'S', 'K', 'P') and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."' and hari = '".$hari."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}else{
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) <> 'E' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."' and hari = '".$hari."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}
				}elseif ($program == 'SEMUA'){
					if ($jurusan == 'AKUNTANSI'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) in ('D', 'E') and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."' and hari = '".$hari."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
	
					}elseif($jurusan == 'IESP'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) in ('B', 'E') and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."' and hari = '".$hari."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}elseif($jurusan == 'MANAJEMEN'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) in ('C', 'S', 'K', 'P,' 'E') and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."' and hari = '".$hari."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}else{
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."' and hari = '".$hari."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}
				}else{
					if ($jurusan == 'AKUNTANSI'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'E' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."' and hari = '".$hari."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
		
					}elseif($jurusan == 'IESP'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'E' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."'  and hari = '".$hari."' group by hari, dosen, idmatakuliah, kelas order by id desc";
					}elseif($jurusan == 'MANAJEMEN'){
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'E' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."' and hari = '".$hari."'  group by hari, dosen, idmatakuliah, kelas order by id desc";
					}else{
							$sql = "select *, sum(hadir) as jumlah from tbabsendosen where  sm = '".$semester."' and tahun ='".$tahun."' and substring(kelas, 1, 1) = 'E' and tanggal >= '".$tanggalawal."' and tanggal <= '".$tanggalakhir."' and hari = '".$hari."' group by hari, dosen, idmatakuliah, kelas order by id desc";
					}
					
				}
			}
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_data_norek($kolom,$dosen){
			if($this->cek_result != "on"){
			$sql = "select * from tbnorek where dosen = '".$dosen."' 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_jumlah($kolom,$a,$type, $tanggal){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbabsendosen where tanggal = '".$tanggal."'";
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function add_jadwal($hari, $tahun, $semester){
		    $sql = "insert into tbabsendosen (idmatakuliah, matakuliah, dosen, sks, semester, jurusan, kelas, hari, ruang, waktu, sm, tahun)  
(select idmatakuliah,matakuliah, dosen, sks, semester, jurusan, kelas, hari, ruang, waktu, sm, tahun from tbperkuliahan where hari = '".$hari."' and tahun = '".$tahun."' and sm = '".$semester."' )";
		//echo $sql;
		    $this->query = $this-> execute($sql,''); 
		}
		function add_jadwal_tambahan($tanggal){
		    $sql = "update tbabsendosen set tanggal = '".$tanggal."', hadir = '1' where tanggal is null";
		    $this->query = $this-> execute($sql,''); 
		}
		function update_hadir($id){
		    $sql = "update tbabsendosen set hadir = '1' where id = '".$id."'";
			$this->query = $this-> execute($sql,''); 
		}
		function update_tidak_hadir($id){
		    $sql = "update tbabsendosen set hadir = '0' where id = '".$id."'";
			$this->query = $this-> execute($sql,''); 
		}
		function update_setting($tanggalawal, $tanggalakhir, $tanggalkumpul){
		    $sql = "update tbsettingsempek set tanggalawal = '".$tanggalawal."', tanggalakhir = '".$tanggalakhir."', tanggalkumpul = '".$tanggalkumpul."'";
			$this->query = $this-> execute($sql,''); 
		}
		function delete_absen($tanggal){
		    $sql = "delete from tbabsendosen where tanggal = '".$tanggal."'";
			//echo $sql;
			$this->query = $this-> execute($sql,''); 
		}
				
	}
?>