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

class C_studentbody extends Controller {
var $stat;

    public function __construct()
    {
        parent::Controller();
        $this->load->library('session');
		$this->load->library('rhlib');
       }
       
    function getJsonFromSPPrivate($sql, $parameters){
		$KEDUA= $this->load->database('second', TRUE);
		$query = $KEDUA->query($sql, $parameters);
        $data = array();
		$build_array = array ("success"=>false,"results"=>-1,"data"=>array(),"msg"=>"error");

		if ($query->num_rows() == 0)
		{
			$build_array["success"]=true;
			$build_array["results"]=0;
			$build_array["msg"]="no data";
		}
		if ($query->num_rows() > 0) {
            $data = $query->result();
			$ttl = count($data);
			if($ttl > 0){
				$build_array["success"]=true;
				$build_array["results"]=$ttl;
				$build_array["data"]=$data;
				$build_array["msg"]= "";
			}
        }
        echo json_encode($build_array);
	}
	

    function grid(){
		if (isset($_POST['kdstsemester'])){
			$kdstsemester = $_POST['kdstsemester'];
			$kdfakultas = $_POST['kdfakultas'];

			$sql = "CALL sp_getStudentbodysemesterfakultas(?,?)";
			$parameters = array($kdstsemester,$kdfakultas);
			$this->getJsonFromSPPrivate($sql, $parameters);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_stmhssemester(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdstaktivitasmhs = ($_POST['kdstaktivitasmhs']==99) ? "":"AND a.kdstaktivitasmhs = '".$_POST['kdstaktivitasmhs']."'";
		$q = "SELECT a.*
			 , b.nmstaktivitasmhs
			 , c.nmmhs
			 , c.kdprodi
			 , c.kdjnskelamin
			 , c.thnmasuk
			 , d.nmkelas
			 , e.tingkat
			 , f.nmkodtbkod
			 , concat(e.tingkat, '/ ', e.kdsemester) AS tingsem
		FROM
		  stmhssemester a
		LEFT JOIN (SELECT kdkodtbkod AS kode
						, nmkodtbkod AS nmstaktivitasmhs
				   FROM
					 tbkod
				   WHERE
					 kdapltbkod = '05') b
		ON b.kode = a.kdstaktivitasmhs
		LEFT JOIN mahasiswa c
		ON c.nim = a.nim
		LEFT JOIN kelas d
		ON d.idkelas = a.idkelas
		LEFT JOIN semester e
		ON e.idsemester = a.idsemester
		LEFT JOIN tbkod f
		ON c.kdstawalmhs = f.kdkodtbkod AND f.kdapltbkod = '06'
		WHERE
		  c.kdprodi = '$kdprodi'
		  AND a.kdstsemester = '$kdstsemester'
		  ".$kdstaktivitasmhs."
		ORDER BY
		   f.nmkodtbkod,
		   c.thnmasuk,
		   right(a.nim, 4)";
		//$this->jsonFromQueryWLimit($q);		
		$this->rhlib->jsonFromQuery2($q);		
	}
	
	function delete_temporary(){
		$KEDUA= $this->load->database('second', TRUE);
        $KEDUA->trans_begin();
        $KEDUA->query("DELETE FROM mhsnostatustemp where ipuser='".$_SERVER['REMOTE_ADDR']."-".$this->session->userdata("user_id1unla")."'");
       
         if ($KEDUA->trans_status() === FALSE)
        {
            $KEDUA->trans_rollback();
            $return["success"]=false;
            $return["message"]="Hapus Data gagal";
        }
        else
        {
            $KEDUA->trans_commit();
            $return["success"]=true;
            $return["message"]="Hapus Data Berhasil";
			$this->insert_temporary();
        }
        return $return;
    }
	
	function insert_temporary(){ 
        $KEDUA= $this->load->database('second', TRUE);
        $get_par=$this->input->post("par");
       
        $KEDUA->trans_begin();
            $rows = explode(";",$get_par);
            $row_count = count($rows);
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
                
                     $KEDUA->query("CALL sp_insert_mhsnostatustemp (?,?,?,?,?)",
                             array(
                                 $rows2[0],  
								 $rows2[1],
                                 $rows2[2], 
                                 $rows2[3], 
								 $_SERVER['REMOTE_ADDR']."-".$this->session->userdata("user_id1unla")
								 )
					);
             }
        
         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 get_rekapmhs(){
		$thnmasuk = $_POST['thnmasuk'];
		$kdstsemester1 = $_POST['kdstsemester1'];
		$kdstsemester2 = $_POST['kdstsemester2'];
		
		$kdfakultas = ($_POST['kdfakultas']==99) ? "":"AND v_prodi.kdfakultas = '".$_POST['kdfakultas']."'";
		
		$q = "SELECT `v_prodi`.`nourutprodi` AS `nourutprodi`
			 , v_prodi.kdprodi
			 , `v_prodi`.`nmprodi` AS `nmprodi`
			 , `v_prodi`.`nmjenjangstudi` AS `nmjenjangstudi`
			 , concat(`v_prodi`.`nourutprodi`, '. ',`v_prodi`.`nmprodi`,' (',`v_prodi`.`nmjenjangstudi`,')') AS lnmprodi
			 , sum(if((mahasiswa.kdstsemester = '$kdstsemester1'), 1, 0)) AS ganjil
			 , sum(if((mahasiswa.kdstsemester = '$kdstsemester2'), 1, 0)) AS genap
			 , ifnull(count(mahasiswa.nim),0) as jml
		FROM
		  v_prodi
		LEFT JOIN mahasiswa
		ON mahasiswa.kdprodi = v_prodi.kdprodi
		WHERE 
		  `v_prodi`.`nmjenjangstudi` <> 'S-2'
		  ".$kdfakultas." 
		  AND mahasiswa.thnmasuk = '$thnmasuk'
		GROUP BY
		  `v_prodi`.`nourutprodi`
		, `v_prodi`.`nmprodi`
		, `v_prodi`.`nmjenjangstudi`";		
		$this->rhlib->jsonFromQuery2($q);		
	} 
     
	 function get_daftarrekapmhs(){
		$kdprodi = $_POST['kdprodi'];
		$thnmasuk = $_POST['thnmasuk'];
		$kdstsemester = $_POST['kdstsemester'];
		
		$q = "select mahasiswa.*,stawalmhs.nmkodtbkod AS nmstawal,staktifmhs.nmkodtbkod AS nmstaktivitas from mahasiswa 
		LEFT JOIN `tbkod` `stawalmhs`
		ON `mahasiswa`.`kdstawalmhs` = `stawalmhs`.`kdkodtbkod` AND `stawalmhs`.`kdapltbkod` = 6
		LEFT JOIN `tbkod` `staktifmhs`
		ON `mahasiswa`.`kdstaktivitasmhs` = `staktifmhs`.`kdkodtbkod` AND `staktifmhs`.`kdapltbkod` = 5
		where kdprodi='$kdprodi' 
		and thnmasuk='$thnmasuk' and 
		kdstsemester='$kdstsemester'";	
		
		$this->rhlib->jsonFromQuery2($q);		
	} 
         
}