<?php

if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Feeder_aktmhs_controller extends Controller {
  function __construct(){
    parent::__construct();
    $this->ceklog();
		//$this->load->library('pdf_spp');
    $this->load->library('rhrpt');
		$this->load->library('rhlib');
            
  }
	
	function retValOrNull($val){
		//especially for combo & looukup with no item selected
		$val = ($val=='')? null : $val;
		return $val;
	}
    
  function ceklog() {
    //$user = $this->session->userdata("user_id");
    $user = $this->session->userdata("user_idunlaprivate");
    if($user==""){
      redirect('auth/login');
    }
  }
	
	function jsonFromQuery($q) {
		$this->rhlib->jsonFromQuery($q);
	}
	function jsonFromQueryWLimit($q){
		$this->rhlib->jsonFromQueryWLimit($q);
	}
	
  function get_stmhssemester(){
    $kdprodi = $this->input->post('kdprodi');
    $kdstsemester = $this->input->post('kdstsemester');

    $query = "SELECT 
       stmhssemester.idstmhssemester
     , stmhssemester.kdstsemester
     , stmhssemester.nim
     , stmhssemester.kdstaktivitasmhs
     , tbkod.nmkodtbkod
     , jmhs.nmkodtbkod as jmasuk
     , stmhssemester.catnonaktif
     , stmhssemester.idsemester
     , mahasiswa.nmmhs
     , mahasiswa.thnmasuk
     , round((SELECT ifnull(sum(mutu) / sum(jmlsks), 0) AS IPS FROM v_nilai_semester WHERE kdstsemester = stmhssemester.kdstsemester AND nim = mahasiswa.nim AND left(nilaihuruf, 1) IN ('A', 'B', 'C', 'D', 'E') AND (idsemester BETWEEN 1 AND 8)), 2) as ips
     , round((SELECT ifnull(sum(mutu) / sum(jmlsks), 0) AS IPK FROM v_nilai_semester WHERE nim = mahasiswa.nim AND left(nilaihuruf, 1) IN ('A', 'B', 'C', 'D', 'E') AND (idsemester BETWEEN 1 AND 8)), 2) as ipk
     -- , (SELECT ifnull(sum(jmlsks), 0) AS SKS_SMT FROM v_nilai_semester WHERE kdstsemester = stmhssemester.kdstsemester AND nim = mahasiswa.nim AND left(nilaihuruf, 1) IN ('A', 'B', 'C', 'D', 'E') AND (idsemester BETWEEN 1 AND 8)) as sks_smt
     , (SELECT ifnull(sum(jmlsks), 0) AS SKS_SMT FROM v_nilai_semester WHERE kdstsemester = stmhssemester.kdstsemester AND nim = mahasiswa.nim AND (idsemester BETWEEN 1 AND 8)) as sks_smt
     , (SELECT ifnull(sum(jmlsks), 0) AS SKS_TOTAL FROM v_nilai_semester WHERE nim = mahasiswa.nim AND left(nilaihuruf, 1) IN ('A', 'B', 'C', 'D', 'E') AND (idsemester BETWEEN 1 AND 8)) as sks_total
     -- , round((SELECT ifnull(sum(mutu) / sum(jmlsks), 0) AS IPK FROM v_nilai_semester WHERE nim = mahasiswa.nim AND left(nilaihuruf, 1) IN ('A', 'B', 'C', 'D', 'E') AND (idsemester BETWEEN 1 AND 8)), 2) as ipk
     -- , '0' as sks_smt
     -- , '0' as sks_total
     -- , (SELECT sum(jmlsks) FROM kurikulum WHERE kurikulum.kdprodi = '{$kdprodi}' AND kurikulum.thnmasuk = mahasiswa.thnmasuk AND kurikulum.idsemester = stmhssemester.idsemester) as jmlsks_semester
      FROM stmhssemester
      JOIN mahasiswa ON stmhssemester.nim = mahasiswa.nim
      LEFT JOIN tbkod ON stmhssemester.kdstaktivitasmhs = tbkod.kdkodtbkod AND tbkod.kdapltbkod = '05'
      LEFT JOIN tbkod `jmhs` ON jmhs.kdapltbkod ='06' AND mahasiswa.kdstawalmhs = jmhs.kdkodtbkod
      WHERE
        stmhssemester.kdstsemester = '{$kdstsemester}' AND
        (stmhssemester.idsemester BETWEEN 1 AND 8) AND
        mahasiswa.kdprodi = '{$kdprodi}'
      ORDER BY 
        stmhssemester.nim ASC, 
        stmhssemester.idsemester ASC, 
        mahasiswa.thnmasuk ASC
      ";
      //echo $query;die;
    $this->jsonFromQuery($query);
  }
	

  function upload_excel()
  {
    $fileName = $_FILES['excelaktmhs']['name'];
    $tmpName  = $_FILES['excelaktmhs']['tmp_name'];
    $fileSize = $_FILES['excelaktmhs']['size'];
    $fileType = $_FILES['excelaktmhs']['type'];
    
    set_time_limit(0);
    require_once(APPPATH.'libraries/nusoap/nusoap.php'); //includes nusoap
    require_once(APPPATH.'libraries/nusoap/class.wsdlcache.php'); //includes nusoap
    $url = 'http://192.168.116.113:8082/ws/live.php?wsdl';
	$client = new nusoap_client($url, true);
    $proxy = $client->getProxy();
    $username = '041015';
    $password = 'Unla2015';
  
    $token = $proxy->GetToken($username, $password); 
    $success = true;
    $messages = "";
    if (strpos($fileName,'.xls') !== false) {
      $fp      = fopen($tmpName, 'r');
      $content = fread($fp, filesize($tmpName));
      fclose($fp);
      
      $return_data = array();
      $rows = explode("\n", $content); //split whole data to row array
      foreach($rows as $idx => $row)
      {
        if($idx == 0) continue; //skip the header
        
        $fields = explode("\t", $row); //split row to field data
        

        /*
          data $fields
          [0] => id semester
          [1] => nim
          [2] => SKS Semester
          [3] => SKS Total
          [4] => IPS
          [5] => IPK
          [6] => KD Status mahasiswa
        */
        if(empty($fields[0])) continue;
        
        //get id_reg_pd
        $nim = str_replace("'", "", $fields[1]);
        $get_reg_pd =  $proxy->GetRecord($token, 'mahasiswa_pt', "trim(nipd) = '".$nim."'");
        if(!isset($get_reg_pd['result']['id_reg_pd']) || empty($get_reg_pd['result']['id_reg_pd'])){
          $success = false;
          $messages .= 'mahasiswa dengan nim '.$nim.' tidak terdaftar di feeder<br>';
          continue;
        }

        $fields[6] = trim(preg_replace('/\s+/', ' ', $fields[6]));
        //get kode status mahasiswa
        switch($fields[6]){
          case 'A':
            $nmkodtbkod = 'Aktif';
            break;
          case 'C':
            $nmkodtbkod = 'Cuti';
            break;
          case 'D':
            $nmkodtbkod = 'Drop Out';
            break;
          case 'L':
            $nmkodtbkod = 'Lulus';
            break;
          case 'K':
            $nmkodtbkod = 'Mengundurkan Diri';
            break;
          default:
            $nmkodtbkod = $fields[6];
          
        }

        $rowdata = array(
          'idstmhssemester' => '',
          'kdstsemester' => $fields[0],
          'nim' => $nim,
          'kdstaktivitasmhs' => $fields[6],
          'nmkodtbkod' => $nmkodtbkod,
          'jmasuk' => $get_reg_pd['result']['fk__jns_daftar'],
          'catnonaktif' => '',
          'idsemester' => '',
          'nmmhs' => $get_reg_pd['result']['nm_pd'],
          'thnmasuk' => substr($get_reg_pd['result']['mulai_smt'], 0, 4),
          'ips' => $fields[4],
          'sks_smt' => $fields[2],
          'ipk' => $fields[5],
          'sks_total' => $fields[3],
        );
        
        $return_data[] = $rowdata;
        
      }

      $return = array(
        'success' => $success,
        'message' => $messages,
        'data' => $return_data,
      );
      echo json_encode($return);
      die;

    }
    else
    {
      $return = array(
        'success' => false,
        'message' => 'sistem hanya menerima file berekstensi .xls',
        'data' => array(),
      );
      echo json_encode($return);
      die;
    }


  }

	function insert(){
     set_time_limit(0);
		 require_once(APPPATH.'libraries/nusoap/nusoap.php'); //includes nusoap
		 require_once(APPPATH.'libraries/nusoap/class.wsdlcache.php'); //includes nusoap

		$url = 'http://192.168.116.113:8082/ws/live.php?wsdl';     
		$client = new nusoap_client($url, true);
		 $proxy = $client->getProxy();
		 
		 $username = '041015';
		 $password = 'Unla2015';
	
		 $token = $proxy->GetToken($username, $password); 
		 
     //$idsemester = $this->input->post('semester');
     $postdata = $this->input->post('postdata');

		 $var = explode('||', $postdata);
		 $row_count = count($var);
     $success_all = true;
     $messages = '';
		 for($ri=0;$ri<$row_count;$ri++){
        $field = explode(";",$var[$ri]);
				
        $record = array();
				
        $filter_mahasiswa = "trim(nipd) = '".$field[0]."'";
				$nim = $proxy->GetRecord($token, 'mahasiswa_pt', $filter_mahasiswa);
        
        if(isset($nim['result']['id_reg_pd']) && !empty($nim['result']['id_reg_pd'])){
          //$record['id_smt'] = $idsemester;
          $record['id_smt'] = $field[6];
          $record['id_reg_pd'] =  $nim['result']['id_reg_pd'];
          $record['ips'] =  $field[3];
          $record['sks_smt'] =  $field[1];
          $record['ipk'] =  $field[4];
          $record['sks_total'] =  $field[2];
          $record['id_stat_mhs'] = $field[5];
          $result = $proxy->InsertRecord($token, 'kuliah_mahasiswa', json_encode($record));
          if(!isset($result['result']['id_smt'])){
            $success_all = false;
            $messages .= 'gagal menginput aktifitas mahasiswa '.$field[7].' :'.$result['result']['error_desc'].'<br>';
          }
        }

		}	
		
    $return = array(
      'success' => $success_all,
      'message' => $messages,
    );
    echo json_encode($return);
		
  }
	

	
    
}    
	

?>