<?php
include "../../inc/config.php";
require_once '../../lib/Writer.php';
include "../../lib/vendor/autoload.php";
use \JsonMachine\JsonMachine;
$json_response = array();
$data_rec = array();
$merge_key_val = 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('url_rest');
 
    $data_jurusan = $db->fetch_all('jurusan');
    $data_array_jur = array();
    foreach ($data_jurusan as $dt) {
        $data_array_jur[$dt->id_sms] = $dt->kode_jurusan;    
    }

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

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

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

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



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

$angkatan = "";
$id_jenis_daftar = "";



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

   $filter_angkatan = "and id_periode_masuk >= '".$_POST['angkatan_awal']."1' and id_periode_masuk<='".$_POST['angkatan_akhir']."2'";

       


        if ($_POST['status_mhs']!='') {
              $aktif = 0;
               foreach ($_POST['status_mhs'] as $val) {
                   if ($val!='AKTIF') {
                       $array_status[] = "'$val'";
                   } else {
                      $aktif+=1;
                   }
               }
               
               if (!empty($array_status)) {
                 $implode_status = implode(",", $array_status);
                 if ($aktif>0) {
                    $filter_status = "and (id_registrasi_mahasiswa in (select id_reg_pd from reg_pd where tgl_keluar is null) or id_jenis_keluar IN(".$implode_status.") )";
                 } else {
                    $filter_status = " and id_jenis_keluar IN(".$implode_status.")";
                 }
              } else {
                    $filter_status = " and id_registrasi_mahasiswa in (select id_reg_pd from reg_pd where tgl_keluar is null)";
              }
        }

        if ($_POST['id_jns_daftar']!="all") {
            $id_jenis_daftar = "and id_jenis_daftar='".$_POST['id_jns_daftar']."'";
        }


        $data_rec = array();
        $filter_mahasiswa = "$filter_sms $filter_angkatan $filter_status $id_jenis_daftar";
        $jumlah['filter'] = $filter_mahasiswa;
}


$data_rec = array(); 

$kode_pt_asal = "";
$kode_prodi_asal = "";
$kode_prodi = "";
        $order_by = "nipd ASC";

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


$header = array(
 'NIM'=> 'string',
  'Mulai Semester'=> 'string',
  'Jenis Pendaftaran'=> 'string',
  'Jalur Pendaftaran'=> 'string',
  'Tanggal Masuk'=> 'string',
  'SKS Diakui'=> 'string',
  'Asal Perguruan Tinggi'=> 'string',
  'Asal Program Studi'=> 'string',
  'Kode Prodi'=> 'string',
  'Jenis Pembiayaan'=> 'string',
  'Jumlah Biaya Masuk'=> 'string',
  'Id Neo' => 'string',
  'Nama Mahasiswa' => 'string',
  'Nama Prodi' => 'string',
  'Status Mahasiswa' => 'string',
  'Periode Keluar' => 'string',
);
$data_dic_riwayat = [
      'act' => 'GetListRiwayatPendidikanMahasiswa',
        'token' => $token,
        'filter' => $filter_mahasiswa,
        'order' => 'id_registrasi_mahasiswa desc',
        'limit' => $limit,
        'offset' => $offset
    ];
$temp_riwayat = service_request($data_dic_riwayat);
$json_filename = SITE_ROOT . "upload/temp/" . $_POST['random_number'] . ".json";
        if (!empty($temp_riwayat->data)) {

                    foreach ($temp_riwayat->data as $key) {

                  //kampus 
                  $pt = $db->fetch_single_row("satuan_pendidikan","id_sp",$key->id_perguruan_tinggi_asal);
                  if ($pt) {
                    $kode_pt_asal = $pt->npsn;
                  }
                  //prodi asal
                  $prodis = $db->fetch_single_row("sms","id_sms",$key->id_prodi_asal);
                  if ($prodis) {
                    $kode_prodi_asal = $prodis->kode_prodi;
                  }
                  if (in_array($key->id_prodi, array_keys($data_array_jur))) { 
                    $kode_prodi = $data_array_jur[$key->id_prodi];
                  } else {
                    $kode_prodi = '';
                  }
                  if ($key->tanggal_keluar=="") {
                     $status = 'Aktif';
                  } else {
                    $status = $key->keterangan_keluar;
                  }
                  $data_rec = array(
                       convert_ascii($key->nim),
                       convert_ascii($key->id_periode_masuk),
                       convert_ascii($key->id_jenis_daftar),
                       convert_ascii($key->id_jalur_daftar),
                       convert_ascii(tgl_english($key->tanggal_daftar)),
                       convert_ascii($key->sks_diakui),
                       $kode_pt_asal,
                       $kode_prodi_asal,
                       $kode_prodi,
                       convert_ascii($key->id_pembiayaan),
                       convert_ascii($key->biaya_masuk),
                       $key->id_registrasi_mahasiswa,
                       $key->nama_mahasiswa,
                       $key->nama_program_studi,
                       $status,
                       $key->id_periode_keluar,
                       );
                  $merge_key_val[] = array_combine(array_keys($header), $data_rec);
                    }

                    



write_data($json_filename,$merge_key_val);

        }

if ($_POST['last']=='yes') {
$data_file = JsonMachine::fromFile($json_filename);
foreach ($data_file as $key => $value) {
      $final[] = array_values($value);
      
}

$writer = new XLSXWriter();
$style =
        array (
                      array(
              'border' => array(
                'style' => 'thin',
                'color' => '000000'
                ),
            'allfilleddata' => true
            ),
            array(
                'fill' => array(
                    'color' => 'ff0000'
                    ),
                'cells' => array(
                    'A1',
                    'B1',
                     'C1',
                    'E1',
                   'I1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
            ),
                        array(
                'fill' => array(
                    'color' => '00ff00'
                    ),
                'cells' => array(
                  'D1',
                    'F1',
                    'G1',
                    'H1',
                    'J1',
                    'K1',
                    'L1',
                    'M1',
                    'N1',
                    'O1',
                    'P1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
                )
            );

//column width
$col_width = array(
  1 => 19,
  2 => 20,
  3 => 20,
  4 => 20,
  5 => 20,
  6 => 20,
  7 => 20,
  8 => 20,
  9 => 20,
  10 => 23,
  11 => 28,
  12 => 40,
  13 => 40,
  14 => 32,
  15 => 16,
  16 => 16
  );
$writer->setColWidth($col_width);


$filename = 'History_pendidikan.xlsx';
$writer->writeSheet($final,'Data History', $header, $style);
$writer->writeToFile(SITE_ROOT."upload/temp/".$_POST['random_number']."_".XLSXWriter::sanitize_filename(str_replace(' ','_',$filename)));

$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_url()."upload/temp/".$_POST['random_number']."_".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."upload/temp/".$_POST['random_number']."_".XLSXWriter::sanitize_filename(str_replace(' ','_',$filename));

        $jumlah['json_file'] = $json_filename; 

        $jumlah['last_notif'] = $msg;
        //unlink($json_filename);
}


array_push($json_response, $jumlah);

echo json_encode($json_response);
exit();
?>