<?php 
	class DataTransaksi extends mysql{
		
		function get_jumlah_trx_grup($type, $a, $idgrup){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select NoTRX from tbmastertrx where idgrup = '".$idgrup."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_master_id($kolom,$id){
			if($this->cek_result != "on"){
			$sql = "select alamat from tbmastertrx where NoTRX = '".$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_all_trans_total($kolom,$a,$type, $tglawal, $tglakhir, $program, $tahun, $semester, $idgrup, $idOperator, $jurusan){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			if($jurusan == 'Akuntansi'){
				$jurusan = '33';	
			}elseif($jurusan == 'Manajemen'){
				$jurusan = '32';	
			}elseif($jurusan == 'IESP'){
				$jurusan = '31';
			}else{
				$jurusan = 'Semua';	
			}
			
			if($jurusan == 'Semua'){
				if ($tglawal != "" and $tglakhir != "" and $program != "" and $tahun != "" and $semester != "" and $idgrup != "" and $idOperator != ""){
					$sql = "select tb1.*, tb2.Nama, tb3.Des, tb4.namaOperator from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tboperator tb4 on tb1.idOperator = tb4.KodeOperator where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'  and tb1.program = '".$program."' and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.idgrup = '".$idgrup."' and tb1.idOperator = '".$idOperator."' ";
				}else if ($tglawal != "" and $tglakhir != "" and $program == "" and $tahun != "" and $semester != "" and $idgrup != "" and $idOperator != "") {
					//program 
					$sql = "select tb1.*, tb2.Nama, tb3.Des, tb4.namaOperator from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tboperator tb4 on tb1.idOperator = tb4.KodeOperator where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'   and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.idgrup = '".$idgrup."'  and tb1.idOperator = '".$idOperator."'";			
				}else if ($tglawal != "" and $tglakhir != "" and $program == "" and $tahun != "" and $semester != "" and $idgrup == "" and $idOperator != "") {
					//program , grup
					$sql = "select tb1.*, tb2.Nama, tb3.Des, tb4.namaOperator from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tboperator tb4 on tb1.idOperator = tb4.KodeOperator where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'   and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.idOperator = '".$idOperator."'";
				}else if ($tglawal != "" and $tglakhir != "" and $program != "" and $tahun != "" and $semester != "" and $idgrup == "" and $idOperator != "") {
					//grup
					$sql = "select tb1.*, tb2.Nama, tb3.Des, tb4.namaOperator from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tboperator tb4 on tb1.idOperator = tb4.KodeOperator where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'   and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.program = '".$program."'  and tb1.idOperator = '".$idOperator."'";
				}else if ($tglawal != "" and $tglakhir != "" and $program != "" and $tahun != "" and $semester != "" and $idgrup != "" and $idOperator == "") {
					//operator
					$sql = "select tb1.*, tb2.Nama, tb3.Des, tb4.namaOperator from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tboperator tb4 on tb1.idOperator = tb4.KodeOperator where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'   and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.program = '".$program."'  and  tb1.idgrup = '".$idgrup."'";

				}else {
					$sql = "select tb1.*, tb2.Nama, tb3.Des, tb4.namaOperator from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tboperator tb4 on tb1.idOperator = tb4.KodeOperator where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."' and tb1.program = '".$program."' ";
				}
			}else{
				if ($tglawal != "" and $tglakhir != "" and $program != "" and $tahun != "" and $semester != "" and $idgrup != "" and $idOperator != ""){
					$sql = "select tb1.*, tb2.Nama, tb3.Des, tb4.namaOperator from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tboperator tb4 on tb1.idOperator = tb4.KodeOperator where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'  and tb1.program = '".$program."' and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.idgrup = '".$idgrup."' and tb1.idOperator = '".$idOperator."' and substring(tb1.NPM, 3, 2) = '".$jurusan."' ";
				}else if ($tglawal != "" and $tglakhir != "" and $program == "" and $tahun != "" and $semester != "" and $idgrup != "" and $idOperator != "") {
					//program 
					$sql = "select tb1.*, tb2.Nama, tb3.Des, tb4.namaOperator from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tboperator tb4 on tb1.idOperator = tb4.KodeOperator where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'   and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.idgrup = '".$idgrup."'  and tb1.idOperator = '".$idOperator."' and substring(tb1.NPM, 3, 2) = '".$jurusan."'";			
				}else if ($tglawal != "" and $tglakhir != "" and $program == "" and $tahun != "" and $semester != "" and $idgrup == "" and $idOperator != "") {
					//program , grup
					$sql = "select tb1.*, tb2.Nama, tb3.Des, tb4.namaOperator from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tboperator tb4 on tb1.idOperator = tb4.KodeOperator where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'   and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.idOperator = '".$idOperator."' and substring(tb1.NPM, 3, 2) = '".$jurusan."'";
				}else if ($tglawal != "" and $tglakhir != "" and $program != "" and $tahun != "" and $semester != "" and $idgrup == "" and $idOperator != "") {
					//grup
					$sql = "select tb1.*, tb2.Nama, tb3.Des, tb4.namaOperator from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tboperator tb4 on tb1.idOperator = tb4.KodeOperator where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'   and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.program = '".$program."'  and tb1.idOperator = '".$idOperator."' and substring(tb1.NPM, 3, 2) = '".$jurusan."'";
				}else if ($tglawal != "" and $tglakhir != "" and $program != "" and $tahun != "" and $semester != "" and $idgrup != "" and $idOperator == "") {
					//operator
					$sql = "select tb1.*, tb2.Nama, tb3.Des, tb4.namaOperator from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tboperator tb4 on tb1.idOperator = tb4.KodeOperator where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'   and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.program = '".$program."'  and  tb1.idgrup = '".$idgrup."' and substring(tb1.NPM, 3, 2) = '".$jurusan."'";

				}else {
					$sql = "select tb1.*, tb2.Nama, tb3.Des, tb4.namaOperator from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup INNER JOIN tboperator tb4 on tb1.idOperator = tb4.KodeOperator where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."' and tb1.program = '".$program."' and substring(tb1.NPM, 3, 2) = '".$jurusan."'";
				}
			}
			//echo $sql;
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_all_trans_operator($kolom,$a,$type, $tglawal, $tglakhir, $program, $tahun, $semester, $idgrup, $idoperator){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
				if ($tglawal != "" and $tglakhir != "" and $program != "" and $tahun != "" and $semester != "" and $idgrup != ""){
					$sql = "select tb1.*, tb2.Nama, tb3.Des from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'  and tb1.program = '".$program."' and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.idgrup = '".$idgrup."'  and tb1.idOperator = '".$idoperator."'";
				}else if ($tglawal != "" and $tglakhir != "" and $program == "" and $tahun != "" and $semester != "" and $idgrup != "") {
					//program 
					$sql = "select tb1.*, tb2.Nama, tb3.Des from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'   and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.idgrup = '".$idgrup."'  and tb1.idOperator = '".$idoperator."'";			
				}else if ($tglawal != "" and $tglakhir != "" and $program == "" and $tahun != "" and $semester != "" and $idgrup == "") {
					//program , grup
					$sql = "select tb1.*, tb2.Nama, tb3.Des from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'   and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.idOperator = '".$idoperator."'";
				}else if ($tglawal != "" and $tglakhir != "" and $program != "" and $tahun != "" and $semester != "" and $idgrup == "") {
					//grup
					$sql = "select tb1.*, tb2.Nama, tb3.Des from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."'   and tb1.tahun = '".$tahun."' and tb1.semester = '".$semester."' and tb1.program = '".$program."'  and tb1.idOperator = '".$idoperator."'";
				}else {
					$sql = "select tb1.*, tb2.Nama, tb3.Des from tbmastertrx tb1 INNER JOIN tbmahasiswa tb2 on tb1.NPM = tb2.NPM INNER JOIN tbgrup tb3 on tb1.idgrup = tb3.idGrup where date(tb1.TglInput) >= '".$tglawal."' and date(tb1.TglInput) <= '".$tglakhir."' and tb1.idOperator = '".$idoperator."'";
				}
				
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_all_trans_dc($kolom,$a,$type, $tgl, $program, $tahun, $semester){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select tb1.NoTRX, tb1.TglInput, tb1.NPM, tb1.TotalBayar, tb2.Nama FROM tbmastertrx tb1 inner join tbmahasiswa tb2 on tb1.NPM =  tb2.NPM where tb1.TglInput = '".$tgl."' and tb1.program = '".$program."' and tb1.tahun = '".$tahun."' and '".$semester."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_all_trans_by_tgl($kolom,$a,$type, $tgl){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select tb1.NoTRX, tb1.TglInput, tb1.NPM, tb1.TotalBayar, tb2.Nama FROM tbmastertrx tb1 inner join tbmahasiswa tb2 on tb1.NPM =  tb2.NPM where tb1.TglInput = '".$tgl."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_temp($kolom,$a,$type, $ids){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "SELECT * FROM temp WHERE IdSession ='".$ids."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_all_trans($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select tb1.NoTRX, tb1.TglInput, tb1.NPM, tb1.TotalBayar, tb2.Nama FROM tbmastertrx tb1 inner join tbmahasiswa tb2 on tb1.NPM =  tb2.NPM";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_last_trans($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbmastertrx where month(TglInput) = month(curdate())";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_last_trans_pendaftaran($kolom,$a,$type){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbmastertrxpendaftaran where month(tglInput) = month(curdate())";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_npm_single_by_ids($kolom,$id){
			if($this->cek_result != "on"){
			$sql = "SELECT * FROM tbdetiltrx WHERE NoTrx ='".$id."'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}

		function get_trx_for_cek($type,$npm, $program, $tahun, $semester, $idgrup){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "SELECT * FROM tbmastertrx WHERE NPM ='".$npm."' and program = '".$program."' and tahun = '".$tahun."' and semester = '".$semester."' and idgrup = '".$idgrup."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_last_detil_trans($kolom, $NoTrx, $NoId){
			if($this->cek_result != "on"){
			$sql = "select * from tbdetiltrx where NoTrx = '".$NoTrx."' and NoID = '".$NoId."'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		function get_detil_trans_by_id($kolom, $NoTrx, $NoId){
			if($this->cek_result != "on"){
			$sql = "select * from tbdetiltrx where NoTrx = '".$NoTrx."' and NoID = '".$NoId."'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		function get_master_trans_by_id($kolom, $NoTrx){
			if($this->cek_result != "on"){
			$sql = "select * from tbmastertrx where NoTRX = '".$NoTrx."'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		function get_detil_trans($kolom,$a,$type, $noTrx){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbdetiltrx where NoTrx = '".$noTrx."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_detil_trans_pendaftaran($kolom,$a,$type, $noTrx){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbdetiltrxpendaftaran where NoTRX = '".$noTrx."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}
		function get_master_trans($kolom,$a,$type, $noTrx){
			if($type == '') return trim ($this->sql_order_seek($sql,$kolom,$a));
			$sql = "select * from tbmastertrx where NoTRX = '".$noTrx."'";
			$this->query = $this->execute($sql,'');
			if ($type == 'jumlah') return $this->sql_order_count($sql);
		}

		function get_master_trans_pendaftaran($kolom, $noPendaftaran){
			if($this->cek_result != "on"){
			$sql = "SELECT * FROM tbmastertrxpendaftaran WHERE noPendaftaran ='".$noPendaftaran."' and idGrup = 'G0022'";
			$this->result = $this->execute($sql,'order');
			$this->cek_result = "on";
			return trim($this->result[$kolom]);
			}
				else return trim($this->result[$kolom]);
		}
		
		function add_temp($idSession, $NoId, $namaTrx, $Biaya, $tgl){
			  $sql = "INSERT INTO temp (IdSession, NoID, NamaTrx, Biaya, tgl) values ('".$idSession."','".$NoId."', '".$namaTrx."', '".$Biaya."', '".$tgl."')";
			  $this->query = $this-> execute($sql,''); 
			  
		}
		function delete_temp($id){
			  $sql = "DELETE FROM temp WHERE IdSession = '".$id."'";
			  $this->query = $this-> execute($sql,''); 
			  
		}
		function cancel_trans($id){
			  $sql = "update tbmastertrx set NPM = 'cancel', TotalBayar = 0, id = '', program = '', tahun = '',semester = '', idOperator = '', idgrup = '', alamat = '', IsEksekutif = '' where NoTRX = '".$id."'";
			  $this->query = $this-> execute($sql,''); 
			  
		}
		function delete_detil_trans($id){
			  $sql = "delete from tbdetiltrx where NoTrx = '".$id."'";
			  $this->query = $this-> execute($sql,''); 
			  
		}
		function delete_temp_id($id){
			  $sql = "DELETE FROM temp WHERE id = '".$id."'";
			  $this->query = $this-> execute($sql,''); 
		}
		function add_master_transaksi_pendaftaran($idMaster, $noPendaftaran, $total, $idgrup, $idoperator){
			  $sql = "INSERT INTO tbmastertrxpendaftaran values ('".$idMaster."',curdate(), '".$noPendaftaran."', '".$total."', '".$idgrup."', '".$idoperator."')";
			  $this->query = $this-> execute($sql,''); 
			  
		}

		function add_detil_transaksi_pendaftaran($idMaster, $idtemplate, $jumlah){
			  $sql = "INSERT INTO tbdetiltrxpendaftaran (NoTRX,idTemplate,jumlah) values ('".$idMaster."','".$idtemplate."', '".$jumlah."')";
			  $this->query = $this-> execute($sql,''); 
			  
		}

		function add_master_transaksi($idMaster, $NPM, $TotalBayar, $idMhs, $program, $tahun, $semester, $idOperator, $idgrup, $alamat, $noArsip){
			  $sql = "INSERT INTO tbmastertrx values ('".$idMaster."',curdate(), '".$NPM."', '".$TotalBayar."', '1', '".$idMhs."', '".$program."', '".$tahun."', '".$semester."', '".$idOperator."', '".$idgrup."', '".$alamat."', '".$noArsip."')";
			  $this->query = $this-> execute($sql,''); 
			  
		}
		function add_detil_transaksi($idMaster, $tgl, $NoID, $namaTrx, $Biaya, $NPM, $NamaMahasiswa, $idBendahara, $NamaBendahara){
			  $sql = "INSERT INTO tbdetiltrx (NoTrx, TglInput, Tgl, NoID, NamaTRX, Biaya, NPM, NamaMahasiswa, IsEksekutif, idBendahara, NamaBendahara) values ('".$idMaster."',curdate(), '".$tgl."', '".$NoID."', '".$namaTrx."', '".$Biaya."', '".$NPM."', '".$NamaMahasiswa."', '1', '".$idBendahara."', '".$NamaBendahara."')";
			  $this->query = $this-> execute($sql,''); 
		}
	}
?>