<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class C_karturencanastudi extends Controller {
var $stat;
  /*membuka koneksi database untuk d_mahasiswa*/
    public function __construct()
    {
        parent::Controller();
        $this->load->library('session');
        
    }
       
    function nm_field($column,$tbl,$whereb, $wherea){
        $q = "SELECT ".$column." as nm FROM ".$tbl." where ".$whereb." = '".$wherea."' " ;
        $query  = $this->db->query($q);
        $nm= ''; 
                    
        if ($query->num_rows() != 0)
        {
            $row = $query->row();
            $nm=$row->nm;
        }
        return $nm;
    }  
    
     function oto($var){
        $q = "SELECT getOtonoPmb(".$var.") as nm;";
        $query  = $this->db->query($q);
        $nm= ''; 
                    
        if ($query->num_rows() != 0)
        {
            $row = $query->row();
            $nm=$row->nm;
        }
        return $nm;
    }  
       
     // START PENGGUNA
     
    function grid(){ //ISTRA
        //kurikulum.kdprodi,
        //kurikulum.tahunmsk,
        $kdprodi                  = $this->input->post("kdprodi");
		$idkelas                  = $this->input->post("idkelas");
        $tahunmsk                 = $this->input->post("tahunmsk");
        $kdthnakademik            = $this->input->post("kdthnakademik");
        $nmjnssemester            = trim(substr($kdthnakademik, 10, 6));
		$kdstsemester			  = $this->input->post("kdstsemester");
        $nim          			  = $this->input->post("nim");
		
		$KEDUA= $this->load->database('second', TRUE); 
		 
		//============CEK PAKET/PILIHAN

        $querycek  = $KEDUA->query("SELECT idsiskrs,kdsiskrs,nmsiskrs,idsemester FROM v_setkrs 
		WHERE kdprodi='".$kdprodi."' 
		AND idsemester IN (SELECT stmhssemester.idsemester FROM stmhssemester WHERE stmhssemester.nim='".$nim."' AND stmhssemester.kdstsemester='".$kdstsemester."')");
        
		$jdwl= ''; 
        $idsiskrs = '';    
		$idsemester = '';    
		
        if ($querycek->num_rows() > 0) {
			$rowcek = $querycek->row();
			$idsiskrs = $rowcek->idsiskrs; 
			$idsemester = $rowcek->idsemester; 
		}
		
		//=========================		
		$q  = $KEDUA->query("CALL sp_getKrsMatkul (?,?,?,?,?,?,?)",array($nim,$kdprodi,$kdstsemester,$tahunmsk,$idkelas,$idsemester,$idsiskrs));
		
       /* $q = $KEDUA->query("SELECT `kurikulum`.`idkurikulum` AS `idkurikulum`
							 , `kurikulum`.`kdprodi` AS `kdprodi`
                             , `kurikulum`.`thnmasuk` AS `thnmasuk`
                             , `kurikulum`.`kdmk` AS `kdmk`
                             , `matakuliah`.`nmmkind` AS `nmmkind`
                             , `kurikulum`.`idsemester` AS `idsemester`
                             , `jsemester`.`nmjnssemester` AS `nmjnssemester`
                             , `kurikulum`.`jmlsks` AS `jmlsks`
                             , (SELECT `vv_status_semester`.`kdstsemester`
                                FROM
                                  `vv_status_semester`
                                WHERE 
                                  (`vv_status_semester`.`idstatus` = 1)) AS `kdstsemester`
                             , (SELECT `dosen`.`nmdosdgngelar`
                                    FROM
                                      `dosen`
                                    WHERE
                                      (`dosen`.`nidu` = (SELECT `struprogdet`.`nidu` AS `nidu`
                                                         FROM
                                                           `struprogdet`
                                                         WHERE
                                                           ((`struprogdet`.`kdstsemester` = (SELECT `vv_status_semester`.`kdstsemester` AS `kdstsemester`
                                                                                             FROM
                                                                                               `vv_status_semester`
                                                                                             WHERE
                                                                                               (`vv_status_semester`.`idstatus` = 1)))
                                                           AND (`struprogdet`.`kdprodi` = `kurikulum`.`kdprodi`)
                                                           AND (`struprogdet`.`kdmk` = `kurikulum`.`kdmk`)
														   AND (`struprogdet`.idkelas='".$idkelas."')
														   AND (`struprogdet`.idjnsdosen='1'))))) AS namadosen
                                , (SELECT `dosen`.`nidu`
                                    FROM
                                      `dosen`
                                    WHERE
                                      (`dosen`.`nidu` = (SELECT `struprogdet`.`nidu` AS `nidu`
                                                         FROM
                                                           `struprogdet`
                                                         WHERE
                                                           ((`struprogdet`.`kdstsemester` = (SELECT `vv_status_semester`.`kdstsemester` AS `kdstsemester`
                                                                                             FROM
                                                                                               `vv_status_semester`
                                                                                             WHERE
                                                                                               (`vv_status_semester`.`idstatus` = 1)))
                                                           AND (`struprogdet`.`kdprodi` = `kurikulum`.`kdprodi`)
                                                           AND (`struprogdet`.`kdmk` = `kurikulum`.`kdmk`)
														   AND (`struprogdet`.idkelas='".$idkelas."')
														   AND (`struprogdet`.idjnsdosen='1'))))) AS nidudosen
                             , if(((SELECT `nilai`.`kdmk`
									FROM
									`nilai`
									WHERE
									((`nilai`.`kdstsemester` = (SELECT `vv_status_semester`.`kdstsemester`
                                          FROM
                                            `vv_status_semester`
                                          WHERE
                                            (`vv_status_semester`.`idstatus` = 1)))
									AND (`nilai`.`kdprodi` = `kurikulum`.`kdprodi`)
									AND (`nilai`.`kdmk` = `kurikulum`.`kdmk`)
									AND (`nilai`.`nim` = '".$nim."')
									AND idstambilmk <> '6')) = `kurikulum`.`kdmk`), 1, 0) AS `ambilval`

							, (SELECT `nilai`.`idstambilmk`
									FROM
											`nilai`
									WHERE
								((`nilai`.`kdstsemester` = (SELECT `vv_status_semester`.`kdstsemester`
                                      FROM
                                        `vv_status_semester`
                                      WHERE
                                        (`vv_status_semester`.`idstatus` = 1)))
									AND (`nilai`.`kdprodi` = `kurikulum`.`kdprodi`)
								AND (`nilai`.`kdmk` = `kurikulum`.`kdmk`)
								AND (`nilai`.`nim` = '".$nim."'))) AS `idstambilmk1`
                             , (SELECT `nilai`.accnilaihuruf
                                    FROM
                                      `nilai`
                                    WHERE
                                      ((`nilai`.`kdstsemester` = (SELECT `vv_status_semester`.`kdstsemester`
                                                                  FROM
                                                                    `vv_status_semester`
                                                                  WHERE
                                                                    (`vv_status_semester`.`idstatus` = 1)))
                                      AND (`nilai`.`kdprodi` = `kurikulum`.`kdprodi`)
                                      AND (`nilai`.`kdmk` = `kurikulum`.`kdmk`)
                                      AND (`nilai`.`nim` = '".$nim."'))) AS `nilai`
							  ,  (SELECT `nilai`.idstsetuju
                                    FROM
                                      `nilai`
                                    WHERE
                                      ((`nilai`.`kdstsemester` = (SELECT `vv_status_semester`.`kdstsemester`
                                                                  FROM
                                                                    `vv_status_semester`
                                                                  WHERE
                                                                    (`vv_status_semester`.`idstatus` = 1)))
                                      AND (`nilai`.`kdprodi` = `kurikulum`.`kdprodi`)
                                      AND (`nilai`.`kdmk` = `kurikulum`.`kdmk`)
                                      AND (`nilai`.`nim` = '".$nim."'))) AS `idstkrs`
                              
								, (SELECT `stambilmk`.`nmstambilmk` FROM `stambilmk` WHERE `stambilmk`.`idstambilmk` = `idstambilmk1`) as namaambil
								, (SELECT `stsetuju`.`nmstsetuju` FROM `stsetuju` WHERE `stsetuju`.`idstsetuju` = `idstkrs`) AS namasetuju
								, (SELECT count(syaratmk.idsyaratmk) FROM syaratmk WHERE syaratmk.idkurikulum = `kurikulum`.idkurikulum) AS jmlsyaratmk
                        FROM
                          (((`kurikulum`
                        JOIN `matakuliah`
                        ON (((`kurikulum`.`kdmk` = `matakuliah`.`kdmk`) AND (`kurikulum`.`kdprodi` = `matakuliah`.`kdprodi`))))
                        JOIN `semester`
                        ON ((`kurikulum`.`idsemester` = `semester`.`idsemester`)))
                        JOIN `jsemester`
                        ON ((`semester`.`idjnssemester` = `jsemester`.`idjnssemester`))) 
						WHERE `kurikulum`.kdprodi='".$kdprodi."' AND `kurikulum`.thnmasuk='".$tahunmsk."' 
						".$idsemester."
						-- AND `kurikulum`.idsemester IN (SELECT stmhssemester.idsemester FROM stmhssemester WHERE stmhssemester.nim='".$nim."' AND stmhssemester.kdstsemester='".$kdstsemester."')
                        ORDER BY
                          `kurikulum`.`idsemester`
                        , `kurikulum`.`kdmk`"); */
       
        $data = array();
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
        $ttl = count($data);
        
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

        $sks = 0;
		$statkrs ='';
		
      foreach($data as $row) {
		  
		  if($row->is_konversi == null){
			$sks = $sks + $row->jmlsks;
		  //}
		  
          if($row->nilai== null || $row->nilai==''){
              $row->nilai = "-";
          }

            array_push($build_array["data"],array(
				'idkurikulum'=>$row->idkurikulum,
                'kdmk'=>$row->kdmk,
                'nmmk'=>$row->nmmkind,
                'idsemester'=>$row->idsemester,
                'jmlsks'=>$row->jmlsks,
                'tahunmsk'=>$row->thnmasuk,
                'nidupengajar'=>($row->nidudosen) ? $row->nidudosen:'-',
                'namapengajar'=>($row->namadosen) ? $row->namadosen:'-',
				'idkelaspengajar'=>($row->idkelas) ? $row->idkelas:'-',
                'nmkelaspengajar'=>($row->namakelas) ? $row->namakelas:'-',
                'jadwal'=>'--',//($this->gridjadwalmk($kdstsemester,$kdprodi,$row->kdmk,$idkelas)) ? $this->gridjadwalmk($kdstsemester,$kdprodi,$row->kdmk,$idkelas):'-', //ambil jadwal kuliah
                'ambilval'=>$row->ambilval,
				'setujuval'=>(!$row->idstkrs || $row->idstkrs== 2) ? 0:$row->idstkrs,
                'nilai'=>$row->nilai,
				'idstkrs'=>(!$row->idstkrs || $row->idstkrs== 2) ? 0:$row->idstkrs,
				'idstambilmk1'=>$row->idstambilmk1,
				'namaambil'=>($row->namaambil) ? $row->namaambil:'-',
				'namasetuju'=>($row->namasetuju) ? $row->namasetuju:'-',
				'jmlsyaratmk'=>($row->jmlsyaratmk != 0) ? $row->jmlsyaratmk:0,
				'syaratmk'=>'--',//($this->gridsyaratmk($nim,$row->idkurikulum,$row->thnmasuk)) ? $this->gridsyaratmk($nim,$row->idkurikulum,$row->thnmasuk):'-', //ambil syarat makul
				'ketsyaratmk'=>($this->gridketsyaratmk($nim,$row->idkurikulum,$row->thnmasuk)) ? $this->gridketsyaratmk($nim,$row->idkurikulum,$row->thnmasuk):'-', //ambil keterangan syarat makul
            ));
			}else{
				
			}
        }
       
        $carisks = $this->input->post("carisks");
        if ($carisks != ''){
             echo '{"jumlahsks":'.$sks."}";
        }else{
            $build_array["jumlahsks"]=$sks;
            echo json_encode($build_array);
           
        }
    }
	
	function gridjadwalmk($kdstsemester,$kdprodi,$kdmk,$idkelas){ ////untuk mengambil jadwal kuliah
     
        $KEDUA= $this->load->database('second', TRUE);   		
		$q = $KEDUA->query("SELECT * FROM
				  v_jadwalkuliah
				WHERE
				  kdstsemester = '$kdstsemester' AND kdprodi = '$kdprodi' AND kdmk = '$kdmk' AND idkelas = '$idkelas'");

	
	$jadwal='';
    $a=1;
	$data = array();
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
		
		foreach($data as $row) {
                $jadwal .=$a.". ".($row->nmhari).", ".($row->mulai)." - ".($row->selesai)." - Kelas ".($row->nmkelas)." - Ruangan ".($row->nmruangan)." "."<br>";
				$a= $a + 1;
        }
       
			 return $jadwal;
    }
	
	function gridjadwalmkklik(){ ////untuk mengambil jadwal kuliah
		$kdstsemester = $this->input->post("kdstsemester");
		$kdprodi = $this->input->post("kdprodi");
		$kdmk = $this->input->post("kdmk");
		$nidu = $this->input->post("nidu");
		$idkelas = $this->input->post("idkelas");

		//$queryadd = (/* $this->input->post("kdstawalmhs")=="P" &&  */$this->input->post("ganjilgenap")=="2") ? "":" AND nidu = '$nidu' AND idkelas='$idkelas'";
		
        $KEDUA= $this->load->database('second', TRUE);   		
		/*$q = $KEDUA->query("SELECT * FROM
				  v_jadwalkuliah
				WHERE
				  kdstsemester = '$kdstsemester' AND kdprodi = '$kdprodi' AND kdmk = '$kdmk'");*/

		$a = $KEDUA->query("SELECT h.nmhari as nmhari,
									
									jkd.darijam AS mulai,
									jkd.sampaijam AS selesai,
									r.nmruangan AS nmruangan,
		   d.nmdosdgngelar as nmdosdgngelar,
		   k.nmkelas as nmkelas,
		   k.idkelas as idkelas,
		   jk.nidu as niduasli
		   
									FROM
									jadwalkuliah as jk
									JOIN hari as h on h.idhari = jk.idhari
									JOIN kelas as k on k.idkelas = jk.idkelas
									JOIN jadwalkuliahdet as jkd on jkd.idjk = jk.idjk
									JOIN ruangan as r on r.idruangan = jk.idruangan
									JOIN dosen as d on d.nidu = jk.nidu
									WHERE
									jk.kdstsemester = '$kdstsemester' AND jk.kdprodi = '$kdprodi' AND jk.kdmk = '$kdmk'");
				  
	$data = array();
        if ($a->num_rows() > 0) {
            $data = $a->result();
        }
		
				$ttl = count($data);
				
				$build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

			  foreach($data as $row) {
					array_push($build_array["data"],array(
					'nmhari'=>$row->nmhari,
					'jam'=>($row->mulai)." - ".($row->selesai),
					'nmruangan'=>$row->nmruangan,    
					'nmkelas'=>$row->nmkelas,   
					'idkelas'=>$row->idkelas,	
					'nidu'=>$row->niduasli,   
					'nmdosdgngelar'=>$row->nmdosdgngelar,	
					));
				}

		echo json_encode($build_array);
    }
	
	function gridsyaratmk($nim,$idkurikulum,$thnmasuk){ ////untuk mengambil syarat matakuliah
     
        $KEDUA= $this->load->database('second', TRUE);   		
		$q = $KEDUA->query("SELECT v_syaratmk.kdmk
					 , v_syaratmk.nmmkind
					 , v_syaratmk.idsemester
					 , v_syaratmk.jmlsks
					 , v_syaratmk.nmlvlsyaratmk
					 , v_syaratmk.nilaihurufmin
					 , v_syaratmk.bobotnilaimin
					 , (SELECT accnilaihuruf
						FROM
						  nilai
						WHERE
						  kdmk = v_syaratmk.kdmk
						  AND nim = '$nim') AS nilaihurufmhs
					 , (SELECT accbobotnilai
						FROM
						  nilai
						WHERE
						  kdmk = v_syaratmk.kdmk
						  AND nim = '$nim') AS bobotnilaimhs
					 , (if((SELECT accbobotnilai
							FROM
							  nilai
							WHERE
							  kdmk = v_syaratmk.kdmk
							  AND nim = '$nim') > v_syaratmk.bobotnilaimin, 'Lulus Syarat', 'Tidak Lulus Syarat')) AS keterangan
				FROM
				  v_syaratmk
				WHERE
				  idkurikulum = '$idkurikulum' AND thnmasuk= '$thnmasuk'");

	
	$makul='';
    $a=1;
	 $data = array();
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
		
		foreach($data as $row) {
                $makul .=$a.". ".($row->nmmkind)
				." - Nilai (".(($row->nilaihurufmhs) ? $row->nilaihurufmhs:'-')." > ".(($row->nilaihurufmin) ? $row->nilaihurufmin:'-').") ".
				($row->keterangan)."<br>";
				
				$a= $a + 1;
        }
       
			 return $makul;
    }
	
	function gridsyaratmkklik(){ ////untuk mengambil syarat matakuliah
		$nim = $this->input->post("nim");
		$idkurikulum = $this->input->post("idkurikulum");
		$thnmasuk = $this->input->post("tahunmsk");
		
        $KEDUA= $this->load->database('second', TRUE);   		
		$q = $KEDUA->query("SELECT v_syaratmk.kdmk
					 , v_syaratmk.nmmkind
					 , v_syaratmk.idsemester
					 , v_syaratmk.jmlsks
					 , v_syaratmk.nmlvlsyaratmk
					 , v_syaratmk.nilaihurufmin
					 , v_syaratmk.bobotnilaimin
					 , ifnull((SELECT accnilaihuruf
						FROM
						  nilai
						WHERE
						  kdmk = v_syaratmk.kdmk
						  AND nim = '$nim'),'-') AS nilaihurufmhs
					 , ifnull((SELECT accbobotnilai
						FROM
						  nilai
						WHERE
						  kdmk = v_syaratmk.kdmk
						  AND nim = '$nim'),'-') AS bobotnilaimhs
					 , (if((SELECT accbobotnilai
							FROM
							  nilai
							WHERE
							  kdmk = v_syaratmk.kdmk
							  AND nim = '$nim') > v_syaratmk.bobotnilaimin, 'Lulus Syarat', 'Tidak Lulus Syarat')) AS keterangan
				FROM
				  v_syaratmk
				WHERE
				  idkurikulum = '$idkurikulum' AND thnmasuk= '$thnmasuk'");

	 $data = array();
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
		
		$ttl = count($data);
				
		$build_array = array ("success"=>true,"results"=>$ttl,"data"=>$data);

		echo json_encode($build_array);
    }
	
	function gridketsyaratmk($nim,$idkurikulum,$thnmasuk){ //untuk keterangan lulus syarat atau tidak
    
		$KEDUA= $this->load->database('second', TRUE);   
		$q = $KEDUA->query("SELECT (if((SELECT bobotnilai
							FROM
							  nilai
							WHERE
							  kdmk = v_syaratmk.kdmk
							  AND nim = '$nim') > v_syaratmk.bobotnilaimin, 'Lulus Syarat', 'Tidak Lulus Syarat')) AS keterangan
				FROM
				  v_syaratmk
				WHERE
				  idkurikulum = '$idkurikulum' AND thnmasuk = '$thnmasuk'");

	
		$ket='';
		$data = array();
		
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
		
		foreach($data as $row) {
			if ($row->keterangan == 'Tidak Lulus Syarat') {
				$ket = 'Tidak Lulus Syarat';
				return $ket;
			} else {
				$ket = $row->keterangan;
			}
        }
		
		return $ket;
	 
    }
	
    function form_mhs(){ //ISTRA
        $KEDUA= $this->load->database('second', TRUE);  /*membuka koneksi database untuk d_mahasiswa*/
           
       // $nama              = $this->input->post("nama");
        $userid     	= $this->input->post("userid");
		$kdstsemester   = $this->input->post("kdstsemester");
		
        $KEDUA->select("*");
        $KEDUA->from("vv_mahasiswa_profile2"); 
        
        if($userid!=''){
            $where['nim']=$userid;
            $KEDUA->where($where);
        }
   
        $q = $KEDUA->get(); 
       
        $data = array();
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
        $datax = count($data);
        $ttl = $datax;
        
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

      foreach($data as $row) {
            array_push($build_array["data"],array(
                'nim'=>$row->nim,
				'tahunmsk'=>$row->thnmasuk, //in beda
                'nmmhs'=>$row->nmmhs,
                'nmfakultas'=>$row->nmfakultas,
                'nmprodi'=>$row->nmprodi,
                'kdthnakademik'=>$row->kdthnakademik.' - '.$row->nmjnssemester,
				'persen'=>'-',//'100',
                'stbayar'=>'-',
				'idjnssemester'=>$row->idjnssemester,
                'kdstsemester'=>$row->kdstsemester,
				'foto'=>$row->foto,
				'kdprodi'=>$row->kdprodi,
				'idkelas'=>$this->get_kelas_semester($kdstsemester,$userid)->idkelas,
				'nmkelas'=>$this->get_kelas_semester($kdstsemester,$userid)->nmkelas,
				'idklsmhs'=>$row->idklsmhs,
				'kdstawalmhs'=>$row->kdstawalmhs,
				'nmstawalmhs'=>$row->nmstawalmhs,   
            ));
        }
        echo json_encode($build_array);
    }
	
	function get_kelas_semester($kdstsemester,$nim){ ////untuk mengambil jadwal kuliah
     
        $KEDUA= $this->load->database('second', TRUE);   		
		$q = $KEDUA->query("SELECT s.idkelas,k.nmkelas FROM
				  stmhssemester s
				LEFT JOIN kelas k ON s.idkelas=k.idkelas
				WHERE
				  s.kdstsemester = '$kdstsemester' AND s.nim = '$nim'");

	
		$kelas='';
		$data = array();
		
		if ($q->num_rows() > 0) {
				$data = $q->result();
		}
			
		foreach($data as $row) {
			return $row;
		}
	
    }
    
    function kelengkapan(){
        $KEDUA= $this->load->database('second', TRUE);
        $nim                      = $this->input->post("nim");
        $kdprodi                  = $this->input->post("kdprodi");
		$idklsmhs                 = $this->input->post("idklsmhs");
        $tahunmsk                 = $this->input->post("tahunmsk");
		$kdstsemester            = $this->input->post("kdstsemester");

        
        
        $q = "SELECT getkelengkapan('".$nim."','".$kdprodi."','".$idklsmhs."','".$tahunmsk."','".$kdstsemester."') as nm;";
		
        $query  = $KEDUA->query($q);
        $nm= ''; 
                    
        if ($query->num_rows() > 0)
        {
            $row = $query->row();
            $nm=$row->nm;
        }
        echo $nm;
    }
	
	function getjadwalkrs(){
        $KEDUA= $this->load->database('second', TRUE);
        $kdprodi                  = $this->input->post("kdprodi");
		$kdstsemester            = $this->input->post("kdstsemester");

        $q = "SELECT getjadwalkrs('".$kdprodi."','".$kdstsemester."') as jdwl;";
		
        $query  = $KEDUA->query($q);
        $jdwl= ''; 
                    
        if ($query->num_rows() > 0)
        {
            $row = $query->row();
            $jdwl=$row->jdwl;
        }
        echo $jdwl;
    }
	
	function getsistemkrs(){
        $KEDUA= $this->load->database('second', TRUE);
        $kdprodi                  = $this->input->post("kdprodi");
		$nim                  = $this->input->post("nim");
		$kdstsemester            = $this->input->post("kdstsemester");

        $q = "SELECT idsiskrs,kdsiskrs,nmsiskrs FROM v_setkrs 
		WHERE kdprodi='".$kdprodi."' 
		AND idsemester IN (SELECT stmhssemester.idsemester FROM stmhssemester WHERE stmhssemester.nim='".$nim."' AND stmhssemester.kdstsemester='".$kdstsemester."')";
		
        $query  = $KEDUA->query($q);
        $jdwl= ''; 
                    
        if ($query->num_rows() > 0)
        {
            $row = $query->row();
			$return["stat"]=1;
			$return["idsiskrs"]=$row->idsiskrs;
			$return["kdsiskrs"]=$row->kdsiskrs;
            $return["nmsiskrs"]=$row->nmsiskrs;
        } else {
			$return["stat"]=0;
			$return["idsiskrs"]='';
			$return["kdsiskrs"]='';
            $return["nmsiskrs"]='';
		}
        echo json_encode($return);
    }
    
	//========================================S K S======================================
    function simpanambilsks(){ 
        $KEDUA= $this->load->database('second', TRUE);
        $get_par=$this->input->post("par_ambil");
       
        $KEDUA->trans_begin();
            $rows = explode(";",$get_par);
            $row_count = count($rows);
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
				
					if ($ri==0) {
						$this->simpanperwalian($rows2[0],$rows2[1],$rows2[2],$rows2[5],$rows2[8],2);
						if ($this->input->post("validkrs")=='0') {
							if ($this->input->post("validlunasdpp")=='0') {
								$this->simpantagihan($rows2[2],
									1,
									$rows2[0],
									$this->input->post("biayatagihandpp"),
									$this->input->post("mintagihandpp"),
									$this->input->post("dibayardpp"));
							}
							if ($this->input->post("validlunasdpk")=='0') {
								$this->simpantagihan($rows2[2],
									2,
									$rows2[0],
									$this->input->post("biayatagihandpk"),
									$this->input->post("mintagihandpk"),
									$this->input->post("dibayardpk"));
							}
						}
					}
                
                     $KEDUA->query("CALL SP_insertambilkrslangsung (?,?,?,?,?,?,?,?,?,?,?)",
                             array(
                                 $rows2[0], //kdstsemester
                                 $rows2[1], //kdprodi
                                 $rows2[2], //nim
                                 $rows2[3], //kdmk
                                 $rows2[4], //nidu
								 $rows2[5], //niduwali
                                 $rows2[6], //idstambilmk
                                 $rows2[7], //nilai
								 $rows2[8], //ipkterakhir
								 $rows2[9],	//idkelas
								 $this->input->post("validkrs"))); //validkrs
             }
        
         if ($KEDUA->trans_status() === FALSE)
        {
            $KEDUA->trans_rollback();
            $return["success"]=false;
            $return["message"]="Simpan Data gagal";
        }
        else
        {
            $KEDUA->trans_commit();
            $return["success"]=true;
            $return["message"]="Simpan Data Berhasil";
        }
        return $return;
     }
        
    function simpansetujusks(){ 
        $KEDUA= $this->load->database('second', TRUE);
        $get_par=$this->input->post("par_setuju");
       
        $KEDUA->trans_begin();
            $rows = explode(";",$get_par);
            $row_count = count($rows);
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
				
					if ($ri==0) {
						$this->simpanperwalian($rows2[0],$rows2[1],$rows2[3],$rows2[6],$rows2[10],1);
					}
                
                     $KEDUA->query("CALL SP_insertsetujukrs (?,?,?,?,?,?,?,?,?,?,?)",
                             array(
                                 $rows2[0], //kdstsemester
                                 $rows2[1], //kdprodi
								 $rows2[2], //idklsmhs
                                 $rows2[3], //nim
                                 $rows2[4], //kdmk
                                 $rows2[5], //nidu
								 $rows2[6], //niduwali
                                 $rows2[7], //idstambilmk
                                 $rows2[8], //idstsetuju
								 $rows2[9], //nilai
								 $rows2[10])); //ipkterakhir
             }
        
         if ($KEDUA->trans_status() === FALSE)
        {
            $KEDUA->trans_rollback();
            $return["success"]=false;
            $return["message"]="Simpan Data gagal";
        }
        else
        {
            $KEDUA->trans_commit();
            $return["success"]=true;
            $return["message"]="Simpan Data Berhasil";
        }
        return $return;
     }
	 
	//========================================P A K E T======================================
    function simpanambilpaket(){ 
        $KEDUA= $this->load->database('second', TRUE);
        $get_par=$this->input->post("par_ambil");
       
        $KEDUA->trans_begin();
            $rows = explode(";",$get_par);
            $row_count = count($rows);
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
                
					if ($ri==0) {
						$this->simpanperwalian($rows2[0],$rows2[1],$rows2[2],$rows2[5],$rows2[7],2);
						if ($this->input->post("validkrs")=='0') {
							if ($this->input->post("validlunasdpp")=='0') {
								$this->simpantagihan($rows2[2],
									1,
									$rows2[0],
									$this->input->post("biayatagihandpp"),
									$this->input->post("mintagihandpp"),
									$this->input->post("dibayardpp"));
							}
							if ($this->input->post("validlunasdpk")=='0') {
								$this->simpantagihan($rows2[2],
									2,
									$rows2[0],
									$this->input->post("biayatagihandpk"),
									$this->input->post("mintagihandpk"),
									$this->input->post("dibayardpk"));
							}
						}
					}
					
                     $KEDUA->query("CALL SP_insertambilkrspaketlangsung (?,?,?,?,?,?,?,?,?,?)",
                             array(
                                 $rows2[0], //kdstsemester
                                 $rows2[1], //kdprodi
                                 $rows2[2], //nim
                                 $rows2[3], //kdmk
                                 $rows2[4], //nidu
								 $rows2[5], //niduwali
                                 $rows2[6], //nilai
								 $rows2[7], //ipkterakhir
								 $rows2[8], //idkelas
								 $this->input->post("validkrs"))); //validkrs
             }
        
         if ($KEDUA->trans_status() === FALSE)
        {
            $KEDUA->trans_rollback();
            $return["success"]=false;
            $return["message"]="Simpan Data gagal";
        }
        else
        {
            $KEDUA->trans_commit();
            $return["success"]=true;
            $return["message"]="Simpan Data Berhasil";
        }
        return $return;
     }
        
    function simpansetujupaket(){ 
        $KEDUA= $this->load->database('second', TRUE);
        $get_par=$this->input->post("par_setuju");
       
        $KEDUA->trans_begin();
            $rows = explode(";",$get_par);
            $row_count = count($rows);
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
                
					if ($ri==0) {
						$this->simpanperwalian($rows2[0],$rows2[1],$rows2[3],$rows2[6],$rows2[8],1);
					}
				
                     $KEDUA->query("CALL SP_insertsetujukrspaket (?,?,?,?,?,?,?,?,?)",
                             array(
                                 $rows2[0], //kdstsemester
                                 $rows2[1], //kdprodi
								 $rows2[2], //idklsmhs
                                 $rows2[3], //nim
                                 $rows2[4], //kdmk
                                 $rows2[5], //nidu
								 $rows2[6], //niduwali
								 $rows2[7], //nilai
								 $rows2[8])); //ipkterakhir
             }
        
         if ($KEDUA->trans_status() === FALSE)
        {
            $KEDUA->trans_rollback();
            $return["success"]=false;
            $return["message"]="Simpan Data gagal";
        }
        else
        {
            $KEDUA->trans_commit();
            $return["success"]=true;
            $return["message"]="Simpan Data Berhasil";
        }
        return $return;
     }
	 
	function batalsetuju(){ 
        $KEDUA= $this->load->database('second', TRUE);
       
        $KEDUA->trans_begin();

        $KEDUA->query("CALL SP_batalsetujukrs (?,?,?,?,?,?)",
            array(
                $this->input->post("kdstsemester"),
                $this->input->post("kdprodi"),
				$this->input->post("idkelas"),
                $this->input->post("nim"),
				$this->input->post("kdmk"),
                $this->input->post("nidupengajar")
			)
		);
 
         if ($KEDUA->trans_status() === FALSE)
        {
            $KEDUA->trans_rollback();
            $return["success"]=false;
            $return["message"]="Simpan Data gagal";
        }
        else
        {
            $KEDUA->trans_commit();
            $return["success"]=true;
            $return["message"]="Simpan Data Berhasil";
        }
        return $return;
     }
	 
	 function simpanperwalian($kdstsemester,$kdprodi,$nim,$niduwali,$ipkterakhir,$stat){ 
        $KEDUA= $this->load->database('second', TRUE);
       
        $KEDUA->trans_begin();

        $KEDUA->query("CALL SP_perwaliankrs (?,?,?,?,?,?)",
            array(
                $kdstsemester,
                $kdprodi,
				$nim,
                $niduwali,
				$ipkterakhir,
				$stat
			)
		);
 
         if ($KEDUA->trans_status() === FALSE)
        {
            $KEDUA->trans_rollback();
            return false;
        }
        else
        {
            $KEDUA->trans_commit();
            return true;
        }
     }
	 
	 //========================================TEMP======================================    
	/* function gridsyarat(){ //ISTRA

        $nim          			  		  = $this->input->post("nim");
		$idkurikulum          			  = $this->input->post("idkurikulum");
            
        $KEDUA= $this->load->database('second', TRUE);   
				
       $q = $KEDUA->query("SELECT v_syaratmk.kdmk
					 , v_syaratmk.nmmkind
					 , v_syaratmk.idsemester
					 , v_syaratmk.jmlsks
					 , v_syaratmk.nmlvlsyaratmk
					 , v_syaratmk.nilaihurufmin
					 , v_syaratmk.bobotnilaimin
					 , (SELECT nilaihuruf
						FROM
						  nilai
						WHERE
						  kdmk = v_syaratmk.kdmk
						  AND nim = '$nim') AS nilaihurufmhs
					 , (SELECT bobotnilai
						FROM
						  nilai
						WHERE
						  kdmk = v_syaratmk.kdmk
						  AND nim = '$nim') AS bobotnilaimhs
					 , (if((SELECT bobotnilai
							FROM
							  nilai
							WHERE
							  kdmk = v_syaratmk.kdmk
							  AND nim = '$nim') > v_syaratmk.bobotnilaimin, 'Lulus Syarat', 'Tidak Lulus Syarat')) AS keterangan
				FROM
				  v_syaratmk
				WHERE
				  idkurikulum = '$idkurikulum'");
       
        $data = array();
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
        $ttl = count($data);
        
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

      foreach($data as $row) {
            array_push($build_array["data"],array(
                'kdmk'=>$row->kdmk,
                'nmmk'=>$row->nmmkind,
                'idsemester'=>$row->idsemester,
                'jmlsks'=>$row->jmlsks,
                'nmlvlsyaratmk'=>$row->nmlvlsyaratmk,
                'nilaihurufmin'=>($row->nilaihurufmin) ? $row->nilaihurufmin:'-',
                'bobotnilaimin'=>($row->bobotnilaimin) ? $row->bobotnilaimin:0,
                'nilaihurufmhs'=>($row->nilaihurufmhs) ? $row->nilaihurufmhs:'-',
				'bobotnilaimhs'=>($row->bobotnilaimhs) ? $row->bobotnilaimhs:0,
				'keterangan'=>$row->keterangan
            ));
        }
       
			echo json_encode($build_array);
    } */
	
	function get_biaya_dpp(){
		$KEDUA= $this->load->database('second', TRUE);   
		
		$nim = $_POST['nim'];
		$kdprodi = $_POST['kdprodi'];
		$tahunmsk = $_POST['tahunmsk'];
		$idjnskls = $this->nm_field('idjnskls','klsmhs','idklsmhs', $_POST['idklsmhs']);
		$idjnsbiaya = $_POST['idjnsbiaya'];
		$kdstsemester = $_POST['kdstsemester'];	

		$datasent = "";

		$querybiaya = $KEDUA->query("SELECT biaya			
				FROM(
					SELECT sum(biaya) as biaya, sum(jmlbayar) as jmlbayar
					FROM(
						SELECT ifnull(biaya,0) as biaya, 0 as jmlbayar
						FROM setbiaya
						WHERE kdprodi='".$kdprodi."'
							and tahunmsk='".$tahunmsk."'
							and kdstsemester = '".$kdstsemester."'
							and idjnskls='".$idjnskls."'
							and idjnsbiaya='".$idjnsbiaya."'
						LIMIT 0,1)A
				)a LIMIT 0,1");
		if ($querybiaya->num_rows() == 1){
			$databiaya = $querybiaya->row();
			$datasent['biaya']=$databiaya->biaya;
			$datasent['diskon']=$this->get_diskon_dpp();
			$datasent['kuitansi']=$this->get_kuitansi_dpp();
			$datasent['mintagihan']=$this->get_mintagihan_dpp();
			$datasent['bayar']=$this->get_byrtagihan_dpp();
			$datasent['novadpp']=$this->get_virtual_account($nim)->novadpp;
			
		}	
		
		echo json_encode($datasent);
	}
	
	function get_diskon_dpp(){
		$KEDUA= $this->load->database('second', TRUE);   
		
		$nim = $_POST['nim'];
		$kdprodi = $_POST['kdprodi'];
		$tahunmsk = $_POST['tahunmsk'];
		$idjnskls = $this->nm_field('idjnskls','klsmhs','idklsmhs', $_POST['idklsmhs']);
		$idjnsbiaya = $_POST['idjnsbiaya'];
		$kdstsemester = $_POST['kdstsemester'];	

		$querydiskon = $KEDUA->query("SELECT diskon
				FROM
					diskonmhs
				WHERE
					nim = '".$nim."'
					AND idsetbiaya IN (SELECT idsetbiaya
                     FROM
                       setbiaya
                     WHERE
                       kdprodi = '".$kdprodi."'
                       AND idjnsbiaya = '".$idjnsbiaya."'
                       AND idjnskls = '".$idjnskls."'
                       AND tahunmsk = '".$tahunmsk."'
                       AND idstatus = '1')");
		if ($querydiskon->num_rows() == 1){
			$datadiskon = $querydiskon->row();
			return $datadiskon->diskon;
		} else {
			return 0;
		}

	}
	
	function get_kuitansi_dpp(){
		$KEDUA= $this->load->database('second', TRUE);   
		
		$nim = $_POST['nim'];
		$idjnsbiaya = $_POST['idjnsbiaya'];
		$kdstsemester = $_POST['kdstsemester'];

		$querykuitansi = $KEDUA->query("SELECT ifnull(sum(jmlbayar), 0) AS jmlbayar
               FROM
                 kuitansi
               WHERE
                 idjnsbiaya = '".$idjnsbiaya."'
				 AND kdstsemester = '".$kdstsemester."'
                 AND nim = '".$nim."'
                 AND idstkuitansi = 1");
		if ($querykuitansi->num_rows() == 1){
			$datakuitansi = $querykuitansi->row();
			return $datakuitansi->jmlbayar;
		} else {
			return 0;
		}

	}
	
	function get_byrtagihan_dpp(){
		$KEDUA= $this->load->database('second', TRUE);   
		
		$nim = $_POST['nim'];
		$idjnsbiaya = $_POST['idjnsbiaya'];
		$kdstsemester = $_POST['kdstsemester'];	

		$querytagihan = $KEDUA->query("SELECT dibayar
                    FROM
                      tagihan
                    WHERE
                      nim = '$nim'
                      AND idjnsbiaya = '$idjnsbiaya'
                      AND kdstsemester = '$kdstsemester'");
		if ($querytagihan->num_rows() == 1){
			$datatagihan = $querytagihan->row();
			return $datatagihan->dibayar;
		} else {
			return 0;
		}

	}
	
	function get_mintagihan_dpp(){
		$KEDUA= $this->load->database('second', TRUE);   
		
		$kdstsemester = $_POST['kdstsemester'];
		$idjnsbiaya = $_POST['idjnsbiaya'];
		
		$querytahapandpp = $KEDUA->query("SELECT sum(persentase) as persentase
				FROM
					setbiayatahapan
				WHERE
					kdstsemester = '".$kdstsemester."'
					AND idjnsbiaya = '".$idjnsbiaya."'
					AND idsttahapan = '1'");
		if ($querytahapandpp->num_rows() == 1){
			$datatahapandpp = $querytahapandpp->row();
			return $datatahapandpp->persentase;
		} else {
			return 0;
		}

	}
	
	function get_biaya_dpk(){
		$KEDUA= $this->load->database('second', TRUE);   
		
		$nim = $_POST['nim'];
		$kdprodi = $_POST['kdprodi'];
		$tahunmsk = $_POST['tahunmsk'];
		$idjnskls = $this->nm_field('idjnskls','klsmhs','idklsmhs', $_POST['idklsmhs']);
		$idjnsbiaya = $_POST['idjnsbiaya'];

		$datasent = "";

		/* $querybiaya = $KEDUA->query("SELECT biaya			
				FROM(
					SELECT sum(biaya) as biaya, sum(jmlbayar) as jmlbayar
					FROM(
						SELECT ifnull(biaya,0) as biaya, 0 as jmlbayar
						FROM setbiaya
						WHERE kdprodi='".$kdprodi."'
							and tahunmsk='".$tahunmsk."'
							and idjnskls='".$idjnskls."'
							and idjnsbiaya='".$idjnsbiaya."'
						LIMIT 0,1)A
				)a LIMIT 0,1"); */
		$querybiaya = $KEDUA->query("SELECT biaya
FROM
  (
  SELECT sum(biaya) AS biaya
       , sum(jmlbayar) AS jmlbayar
  FROM
    (
    SELECT (
           CASE
           WHEN '".$tahunmsk."' <= 2012 THEN
             biaya = 0
           WHEN '".$tahunmsk."' > 2012 THEN
             ifnull(biaya, 0)
           END) AS biaya
         , 0 AS jmlbayar
    FROM
      setbiaya
   WHERE kdprodi='".$kdprodi."'
							and tahunmsk='".$tahunmsk."'
							and idjnskls='".$idjnskls."'
							and idjnsbiaya='".$idjnsbiaya."'
    LIMIT
      0, 1) A
  ) a
LIMIT
  0, 1");
		if ($querybiaya->num_rows() == 1){
			$databiaya = $querybiaya->row();
			$datasent['biaya']=$databiaya->biaya;
			$datasent['diskon']=$this->get_diskon_dpk();
			$datasent['kuitansi']=$this->get_kuitansi_dpk();
			$datasent['mintagihan']=$this->get_mintagihan_dpk();
			$datasent['bayar']=$this->get_byrtagihan_dpk();
			$datasent['novadpk']=$this->get_virtual_account($nim)->novadpk;
			
		}	
		
		echo json_encode($datasent);
	}
	
	function get_diskon_dpk(){
		$KEDUA= $this->load->database('second', TRUE);   
		
		$nim = $_POST['nim'];
		$kdprodi = $_POST['kdprodi'];
		$tahunmsk = $_POST['tahunmsk'];
		$idjnskls = $this->nm_field('idjnskls','klsmhs','idklsmhs', $_POST['idklsmhs']);
		$idjnsbiaya = $_POST['idjnsbiaya'];

		$querydiskon = $KEDUA->query("SELECT diskon
				FROM
					diskonmhs
				WHERE
					nim = '".$nim."'
					AND idsetbiaya IN (SELECT idsetbiaya
                     FROM
                       setbiaya
                     WHERE
                       kdprodi = '".$kdprodi."'
                       AND idjnsbiaya = '".$idjnsbiaya."'
                       AND idjnskls = '".$idjnskls."'
                       AND tahunmsk = '".$tahunmsk."'
                       AND idstatus = '1')");
		if ($querydiskon->num_rows() == 1){
			$datadiskon = $querydiskon->row();
			return $datadiskon->diskon;
		} else {
			return 0;
		}

	}
	
	function get_kuitansi_dpk(){
		$KEDUA= $this->load->database('second', TRUE);   
		
		$nim = $_POST['nim'];
		$idjnsbiaya = $_POST['idjnsbiaya'];

		$querykuitansi = $KEDUA->query("SELECT ifnull(sum(jmlbayar), 0) AS jmlbayar
               FROM
                 kuitansi
               WHERE
                 idjnsbiaya = '".$idjnsbiaya."'
                 AND nim = '".$nim."'
                 AND idstkuitansi = 1");
		if ($querykuitansi->num_rows() == 1){
			$datakuitansi = $querykuitansi->row();
			return $datakuitansi->jmlbayar;
		} else {
			return 0;
		}

	}
	
	function get_byrtagihan_dpk(){
		$KEDUA= $this->load->database('second', TRUE);   
		
		$nim = $_POST['nim'];
		$idjnsbiaya = $_POST['idjnsbiaya'];
		$kdstsemester = $_POST['kdstsemester'];	

		$querytagihan = $KEDUA->query("SELECT dibayar
                    FROM
                      tagihan
                    WHERE
                      nim = '$nim'
                      AND idjnsbiaya = '$idjnsbiaya'
                      AND kdstsemester = '$kdstsemester'");
		if ($querytagihan->num_rows() == 1){
			$datatagihan = $querytagihan->row();
			return $datatagihan->dibayar;
		} else {
			return 0;
		}

	}
	
	function get_mintagihan_dpk(){
		$KEDUA= $this->load->database('second', TRUE);   
		
		$tahunmsk = $_POST['tahunmsk'];
		$idjnsbiaya = $_POST['idjnsbiaya'];
		$semester = $_POST['semester'];
		
		$querytahapandpk = $KEDUA->query("SELECT nominal
				FROM
					setbiayatahapan
				WHERE
					thnmasuk = '".$tahunmsk."'
					AND idjnsbiaya = '".$idjnsbiaya."'"); 
					// AND idsttahapan = '".$semester."'");
		if ($querytahapandpk->num_rows() >= 1){
			$datatahapandpk = $querytahapandpk->row();
			return $datatahapandpk->nominal;
		} else {
			return 0;
		}

	}
	
	function get_virtual_account($nim){
		$KEDUA= $this->load->database('second', TRUE);   
		
		$tahunmsk = $_POST['tahunmsk'];
		$idjnsbiaya = $_POST['idjnsbiaya'];
		$semester = $_POST['semester'];
		
		$querytahapandpk = $KEDUA->query("SELECT novadpp, novadpk
				FROM
					mahasiswa
				WHERE
					nim = '".$nim."'");
		if ($querytahapandpk->num_rows() == 1){
			$datatahapandpk = $querytahapandpk->row();
			return $datatahapandpk;
		} else {
			return null;
		}

	}
	
	function simpantagihan($nim,$idjnsbiaya,$kdstsemester,$biayatagihan,$mintagihan,$dibayar){ 
        $KEDUA= $this->load->database('second', TRUE);
       
        $KEDUA->trans_begin();

        $KEDUA->query("CALL sp_simpan_tagihan (?,?,?,?,?,?)",
            array(
                $nim,
                $idjnsbiaya,
				$kdstsemester,
                $biayatagihan,
				$mintagihan,
				$dibayar
			)
		);
 
         if ($KEDUA->trans_status() === FALSE)
        {
            $KEDUA->trans_rollback();
            return false;
        }
        else
        {
            $KEDUA->trans_commit();
            return true;
        }
     }
     
}