<?php
include "../../inc/config.php";
include "../../lib/nusoap/nusoap.php";
include "../../inc/feeder_function.php";
require_once '../../lib/Writer.php';


$json_response = array();
$data_rec = array();
if ($_POST['total_data']<1) {
    $msg =  "<div class=\"alert alert-warning \" role=\"alert\">
        <font color=\"#3c763d\">Tidak ada data berhasil di generate</font><br />
        </div>";

    $jumlah['last_notif'] = $msg;
    array_push($json_response, $jumlah);

    echo json_encode($json_response);
    exit();
}

$url = get_service_url('soap');
$client = new nusoap_client($url, true);
$proxy = $client->getProxy();
 


$offset = $_POST['offset'];
$token = $_POST['token'];

$jumlah['offset'] = $offset;
$jumlah['token'] = $_POST['token'];
$jumlah['jurusan_attribute'] = $_POST['jurusan_attribute'];
$data_array_jur = json_decode($_POST['jurusan_attribute'],true);  

$filter_mahasiswa = $_POST['filter'];
$jumlah['filter'] = $filter_mahasiswa;

if ($offset==0) {
    $jumlah['token'] = get_token();
    $token = get_token();



$filter_sms = "";
$filter_angkatan = "";
$filter_status = "";

$angkatan = "";

    $data_jurusan = $db->fetch_all('jurusan');
    $data_array_jur = array();
    foreach ($data_jurusan as $dt) {
        $data_array_jur[$dt->id_sms] = $dt->kode_jurusan;    
    }
    $jumlah['jurusan_attribute'] = json_encode($data_array_jur);


if ($_POST['jurusan']!="all") {
    $filter_sms = "p.id_reg_pd is not null and p.id_sms='".$_POST['jurusan']."'";
} else {
    $filter_sms = "p.id_reg_pd is not null";

}
 $filter_angkatan = "and (left(p.mulai_smt,4) BETWEEN '".$_POST['angkatan_awal']."' and '".$_POST['angkatan_akhir']."')";


        if ($_POST['status_mhs']!='') {
            if (in_array('aktif', $_POST['status_mhs']) && count($_POST['status_mhs'])==1) {
               $filter_status  = "and (p.id_jns_keluar is null or p.id_jns_keluar='')";
            } elseif (in_array('aktif', $_POST['status_mhs']) && count($_POST['status_mhs'])>1) {
               $filter_status  .= "and (p.id_jns_keluar is null or p.id_jns_keluar='')";
               foreach ($_POST['status_mhs'] as $val) {
                   if ($val!='aktif') {
                       $array_status[] = "'$val'";
                   }
               }
               $implode_status = implode(",", $array_status);
               $filter_status  .= " and p.id_jns_keluar IN(".$implode_status.")";
            } else {
               foreach ($_POST['status_mhs'] as $val) {
                   if ($val!='aktif') {
                       $array_status[] = "'$val'";
                   }
               }
               $implode_status = implode(",", $array_status);
               $filter_status  = "and p.id_jns_keluar IN(".$implode_status.")";
            }
        }



$filter_mahasiswa = "$filter_sms $filter_angkatan $filter_status";
$jumlah['filter'] = $filter_mahasiswa;
}

$order_by = "nipd ASC";

if ($_POST['total_data']<=200) {
    $limit = $_POST['total_data'];
} else {
    $limit = 200;
}

$temp_rec = array();

//echo $filter_mahasiswa;
$temp_rec = $proxy->GetRecordset($token,"mahasiswa_pt.raw", $filter_mahasiswa,$order_by, $limit,$offset);
//print_r($temp_rec);
foreach ($temp_rec['result'] as $key) {
    $data_reg_pd[$key['id_pd']] = array(
    'nipd' => $key['nipd'],
    'id_jalur_masuk' => $key['id_jalur_masuk'],
    'id_jns_daftar' => $key['id_jns_daftar'],
    'tgl_masuk_sp' => $key['tgl_masuk_sp'],
    'mulai_smt' => $key['mulai_smt'],
    'id_pembiayaan' => $key['id_pembiayaan'],
    'id_sms' => $key['id_sms']
  );

  $id_reg_pd[] = "'".$key['id_pd']."'";
}

$id_reg_pd_concat_where = implode(",", $id_reg_pd);

$filter_mhs = "(p.id_pd) IN ($id_reg_pd_concat_where)";

$students = $proxy->GetRecordset($token,"mahasiswa.raw", $filter_mhs,"","","");


foreach ($students['result'] as $mhs) {

  $reg_pd_data = $data_reg_pd[$mhs['id_pd']];
    if (in_array($reg_pd_data['id_sms'], array_keys($data_array_jur))) { 
      $kode_jur = $data_array_jur[$reg_pd_data['id_sms']];
    } else {
      $kode_jur = '';
    }

    $data_rec[] = array(
          convert_ascii($reg_pd_data['nipd']),
          convert_ascii($mhs['nm_pd']),
          convert_ascii($mhs['tmpt_lahir']),
          convert_ascii($mhs['tgl_lahir']),
          convert_ascii($mhs['jk']),
          convert_ascii($mhs['nik']),
          convert_ascii($mhs['id_agama']),
          convert_ascii($mhs['nisn']),
          convert_ascii($reg_pd_data['id_jalur_masuk']),
          convert_ascii($mhs['npwp']),
          convert_ascii($mhs['kewarganegaraan']),
          convert_ascii($reg_pd_data['id_jns_daftar']),
          convert_ascii($reg_pd_data['tgl_masuk_sp']),
          convert_ascii($reg_pd_data['mulai_smt']),
          convert_ascii($mhs['jln']),
          convert_ascii($mhs['rt']),
          convert_ascii($mhs['rw']),
          convert_ascii($mhs['nm_dsn']),
          convert_ascii($mhs['ds_kel']),
          convert_ascii($mhs['id_wil']),
          convert_ascii($mhs['kode_pos']),
          convert_ascii($mhs['id_jns_tinggal']),
          convert_ascii($mhs['id_alat_transport']),
          convert_ascii($mhs['no_tel_rmh']),
          convert_ascii($mhs['no_hp']),
          convert_ascii($mhs['email']),
          convert_ascii($mhs['a_terima_kps']),
          convert_ascii($mhs['no_kps']),
          convert_ascii($mhs['nik_ayah']),
          convert_ascii($mhs['nm_ayah']),
          convert_ascii($mhs['tgl_lahir_ayah']),
          convert_ascii($mhs['id_jenjang_pendidikan_ayah']),
          convert_ascii($mhs['id_pekerjaan_ayah']),
          convert_ascii($mhs['id_penghasilan_ayah']),
          convert_ascii($mhs['nik_ibu']),
          convert_ascii($mhs['nm_ibu_kandung']),
          convert_ascii($mhs['tgl_lahir_ibu']),
          convert_ascii($mhs['id_jenjang_pendidikan_ibu']),
          convert_ascii($mhs['id_pekerjaan_ibu']),
          convert_ascii($mhs['id_penghasilan_ibu']),
          convert_ascii($mhs['nm_wali']),
          convert_ascii($mhs['tgl_lahir_wali']),
          convert_ascii($mhs['id_jenjang_pendidikan_wali']),
          convert_ascii($mhs['id_pekerjaan_wali']),
          convert_ascii($mhs['id_penghasilan_wali']),
          $kode_jur,
          convert_ascii($reg_pd_data['id_pembiayaan'])
    );
}

/*$file_name = $_POST['random_number'];
//write temp file
$filename = SITE_ROOT."upload/temp/$file_name.json";

write_data($filename,$data_rec);*/


$jumlah['data_rec'] = json_encode($data_rec);
if ($_POST['last']=='yes') {
    if ($_POST['total_data']>200) {
            $final_array = array();
        foreach ($_POST['data_rec'] as $dat) {
             $data = json_decode($dat);
             foreach ($data as $dt) {
                 $final_array[]=$dt;
             }    
        }
        $final = array_merge($final_array,$data_rec);
        $jumlah_total = count($final);

    } else {
        $final = $data_rec;
        $jumlah_total = count($final);
    }
    /*
$filename = SITE_ROOT."upload/temp/1305.json";
    $data_file = file_get_contents($filename);
    $final_decode = json_decode($data_file,true);

    foreach ($final_decode as $key => $value) {
      $final[] = array(
          $value['nipd'],
          $value['nm_pd'],
          $value['tmpt_lahir'],
          $value['tgl_lahir'],
          $value['jk'],
          $value['nik'],
          $value['id_agama'],
          $value['nisn'],
          $value['id_jalur_masuk'],
          $value['npwp'],
          $value['kewarganegaraan'],
          $value['id_jns_daftar'],
          $value['tgl_masuk_sp'],
          $value['mulai_smt'],
          $value['jln'],
          $value['rt'],
          $value['rw'],
          $value['nm_dsn'],
          $value['ds_kel'],
          $value['id_wil'],
          $value['kode_pos'],
          $value['id_jns_tinggal'],
          $value['id_alat_transport'],
          $value['no_tel_rmh'],
          $value['no_hp'],
          $value['email'],
          $value['a_terima_kps'],
          $value['no_kps'],
          $value['nik_ayah'],
          $value['nm_ayah'],
          $value['tgl_lahir_ayah'],
          $value['id_jenjang_pendidikan_ayah'],
          $value['id_pekerjaan_ayah'],
          $value['id_penghasilan_ayah'],
          $value['nik_ibu'],
          $value['nm_ibu_kandung'],
          $value['tgl_lahir_ibu'],
          $value['id_jenjang_pendidikan_ibu'],
          $value['id_pekerjaan_ibu'],
          $value['id_penghasilan_ibu'],
          $value['nm_wali'],
          $value['tgl_lahir_wali'],
          $value['id_jenjang_pendidikan_wali'],
          $value['id_pekerjaan_wali'],
          $value['id_penghasilan_wali'],
          $value['kode_jur'],
          $value['id_pembiayaan']
      );
    }
*/

  

$writer = new XLSXWriter();
$style =
        array (
                      array(
              'border' => array(
                'style' => 'thin',
                'color' => '000000'
                ),
            'allfilleddata' => true
            ),
            array(
                'fill' => array(
                    'color' => 'ff0000'
                    ),
                'cells' => array(
                    'A1',
                    'B1',
                    'C1',
                    'D1',
                    'E1',
                    'F1',
                    'G1',
                    'K1',
                    'L1',
                    'M1',
                    'N1',
                    'S1',
                    'T1',
                    'AA1',
                    'AJ1',
                    'AT1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
            ),
                        array(
                'fill' => array(
                    'color' => '00ff00'
                    ),
                'cells' => array(
                    'H1',
                    'I1',
                    'J1',
                    'L1',
                    'O1',
                    'P1',
                    'Q1',
                    'R1',
                    'U1',
                    'V1',
                    'W1',
                    'X1',
                    'Y1',
                    'Z1',
                    'AB1',
                    'AC1',
                    'AD1',
                    'AE1',
                    'AF1',
                    'AG1',
                    'AH1',
                    'AI1',
                    'AK1',
                    'AL1',
                    'AM1',
                    'AN1',
                    'AO1',
                    'AP1',
                    'AQ1',
                    'AR1',
                    'AS1',
                     'AU1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
                )
            );

//column width
$col_width = array(
  1 => 27,
  2 => 27,
  3 => 27,
  4 => 27,
  5 => 27,
  6 => 27,
  7 => 19,
  8 => 19,
  9 => 19,
  10 => 19,
  11 => 19,
  12 => 19,
  13 => 19,
  14 => 19,
 15 => 19,
  16 => 19,
  17 => 19,
  18 => 19,
  19 => 19,
  20 => 19,
  21 => 19,
  22 => 19,
 23 => 19,
  24 => 19,
  25 => 19,
  26 => 19,
  27 => 19,
  28 => 19,
  29 => 19,
  30 => 19,
 31=> 19,
  32 => 19,
  33 => 19,
  34 => 19,
  35 => 19,
  36 => 19,
  37 => 19,
  38 => 19,
  
  39 => 19,
  40 => 19,
  41 => 19,
  42 => 19,
  43 => 19,
  44 => 19,
  45 => 19,
  46 => 19,
  47 => 19
  );

$writer->setColWidth($col_width);


$header = array(
            'NIM' =>'string',
             'NAMA' =>'string',
             'Tempat Lahir' =>'string',
             'Tanggal Lahir' =>'string',
             'Jenis Kelamin' =>'string',
             'NIK' =>'string',
             'Agama' =>'string',
             'NISN' =>'string',
             'Jalur Pendaftaran' =>'string',
             'NPWP' =>'string',
             'Kewarganegaraan' =>'string',
             'Jenis Pendaftaran' =>'string',
             'Tgl Masuk Kuliah' =>'string',
             'Mulai Semester' =>'string',
             'Jalan' =>'string',
             'RT' =>'string',
             'RW' =>'string',
             'Nama Dusun' =>'string',
             'Kelurahan' =>'string',
             'Kecamatan' =>'string',
             'Kode Pos' =>'string',
             'Jenis Tinggal' =>'string',
             'Alat Transportasi' =>'string',
             'Telp Rumah' =>'string',
             'No HP' =>'string',
             'Email' =>'string',
             'Terima KPS' =>'string',
            'No KPS' =>'string',
             'NIK Ayah' =>'string',
             'Nama Ayah' =>'string',
             'Tgl Lahir Ayah' =>'string',
             'Pendidikan Ayah' =>'string',
             'Pekerjaan Ayah' =>'string',
             'Penghasilan Ayah' =>'string',
             'NIK Ibu' =>'string',
             'Nama Ibu' =>'string',
             'Tanggal Lahir Ibu' =>'string',
             'Pendidikan Ibu' =>'string',
             'Pekerjaan Ibu' =>'string',
             'Penghasilan Ibu' =>'string',
             'Nama Wali' =>'string',
             'Tanggal Lahir wali' =>'string',
             'Pendidikan Wali' =>'string',
             'Pekerjaan Wali' =>'string',
             'Penghasilan Wali' =>'string',
             'Kode Prodi' => 'string',
             'Jenis Pembiayaan' => 'string'
);
//unlink($filename);
$filename = 'Data_mahasiswa.xlsx';
/*header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');

$writer->writeToFile('xlsx-simple.xlsx');*/
$writer->writeSheet($final,'Data Mahasiswa', $header, $style);
$writer->writeToFile(XLSXWriter::sanitize_filename(str_replace(' ','_',$filename)));

ob_end_clean();
//$writer->writeToStdOut();
//exit(0);
$modul_name = explode("/", $_SERVER['REQUEST_URI']);

$total_length = count($modul_name);
$index_modul = $total_length-2;


$msg =  "<div class=\"alert alert-warning \" role=\"alert\">
        <font color=\"#3c763d\">".$_POST['total_data']." data berhasil di generate</font><br />";
        $msg .= "<a class='btn btn-block btn-primary btn-xs' href='".base_admin()."modul/download_mahasiswa/".XLSXWriter::sanitize_filename(str_replace(' ','_',$filename))."' target='_blank'><i class='fa  fa-cloud-download'></i> Download File</a></div>
        </div>";

        $jumlah['data_uri'] = SITE_ROOT."admina/modul/".$modul_name[$index_modul]."/".XLSXWriter::sanitize_filename(str_replace(' ','_',$filename));

        $jumlah['last_notif'] = $msg;
}



array_push($json_response, $jumlah);

echo json_encode($json_response);
exit();

?>