<?php
session_start();
include "../../inc/config.php";


require_once '../../lib/Writer.php';
include "../../lib/vendor/autoload.php";
use \JsonMachine\JsonMachine;

$json_response = array();
$final = 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('soap');
$client = new nusoap_client($url, true);
$proxy = $client->getProxy();
 
$url_rest = get_service_url('rest');

$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 = $jumlah['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 = "and id_prodi='".$_POST['jurusan']."'";
        } else {
            if ($_SESSION['level']!=1) {
                $filter_sms = "and id_prodi='".$_SESSION['id_sms']."'";
            }
        }


        if ($_POST['status_mhs']!='') {
              $aktif = 0;
               foreach ($_POST['status_mhs'] as $val) {
                   if ($val!='AKTIF') {
                       $array_status[] = "'$val'";
                   } else {
                      $aktif=1;
                   }
               }
               $implode_status = implode(",", $array_status);
               if (!empty($array_status)) {
                 if ($aktif==1) {
                    $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)";
              }
        }

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


        $data_rec = array();
        $filter_mahasiswa = "id_registrasi_mahasiswa is not null $filter_sms $filter_angkatan $filter_status and id_mahasiswa in(select id_pd from prestasi)";


        if ($_POST['nim']!='all') {
            $nim_label = $_POST['value_nim'];
            $filter_mahasiswa  = "nim like '%".$_POST['value_nim']."%'";
        } else {
            $filter_mahasiswa = $filter_mahasiswa;
        }


        if ($_POST['nim']!='all') {
            $nim_label = $_POST['value_nim'];
            $filter_mahasiswa  = "nim like '%".$_POST['value_nim']."%'";
        } else {
            $filter_mahasiswa = $filter_mahasiswa;
        }

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

$order_by = "nim ASC";

$limit = 200;
$temp_rec = array();
$data_dic_riwayat = [
      'act' => 'GetListRiwayatPendidikanMahasiswa',
        'token' => $token,
        'filter' => $filter_mahasiswa,
        'order' => $order_by,
        'limit' => $limit,
        'offset' => $offset
    ];
$temp_riwayat = json_decode($db->run($data_dic_riwayat,$url));
if(!empty($temp_riwayat->data)) {
  foreach ($temp_riwayat->data as $keys) {
      $key = $db->convert_obj_to_array($keys);
      //dump($key);
      $data_reg_pd[$key['id_mahasiswa']] = array(
      'nipd' => $key['nim'],
      'id_jalur_masuk' => $key['id_jalur_daftar'],
      'nm_pd' => $key['nama_mahasiswa'],
      'id_jns_daftar' => $key['id_jenis_daftar'],
      'tgl_masuk_sp' => $key['tanggal_daftar'],
      'mulai_smt' => $key['id_periode_masuk'],
      'id_pembiayaan' => $key['id_pembiayaan'],
      'id_sms' => $key['id_prodi'],
      'id_mahasiswa' => $key['id_mahasiswa'],
      'keterangan_keluar' => $key['keterangan_keluar']
    );

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


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

$header = array(
  'NIM'=>'string',
  'Nama'=>'string',
  'Jenis Prestasi'=>'string',
  'Tingkat Prestasi' => 'string',
  'Nama Prestasi' => 'string',
  'Tahun' => 'string',
  'Penyelenggara'=>'string',
  'Peringkat'=>'string',
  'Kode Prodi' => 'string',
  'ID Aktivitas NEO' => 'string',
  'ID Prestasi NEO' => 'string',
  'Angkatan Mahasiswa'=>'string'
);

            $data_req_transkrip = [
              'act' => 'GetListPrestasiMahasiswa',
                'token' => $token,
                'filter' => "id_mahasiswa in($id_reg_pd_concat_where)",
                'order' => "id_mahasiswa asc",
                'limit' => "",
                'offset' => ""

            ];
            
            $data_prestasi = json_decode($db->run($data_req_transkrip,$url_rest));

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

            // print_r($check_transkrip_exist);
            if (!empty($data_prestasi->data)) {
                  foreach ($data_prestasi->data as $key) {
                          $data_rec = array(
                               convert_ascii($data_reg_pd[$key->id_mahasiswa]['nipd']),
                               convert_ascii($data_reg_pd[$key->id_mahasiswa]['nm_pd']),
                               convert_ascii($key->id_jenis_prestasi),
                               convert_ascii($key->id_tingkat_prestasi),
                               convert_ascii($key->nama_prestasi),
                               convert_ascii($key->tahun_prestasi),
                               convert_ascii($key->penyelenggara),
                               convert_ascii($key->peringkat),
                               $data_array_jur[$data_reg_pd[$key->id_mahasiswa]['id_sms']],
                               convert_ascii($key->id_aktivitas),
                               convert_ascii($key->id_prestasi),
                               convert_ascii($data_reg_pd[$key->id_mahasiswa]['mulai_smt'])
                               );
                          $merge_key_val[] = array_combine(array_keys($header), $data_rec);
                  }

write_data($json_filename,$merge_key_val);

            }

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

  if (count($final)<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();
  } else {
$writer = new XLSXWriter();
$style =
        array (
                      array(
              'border' => array(
                'style' => 'thin',
                'color' => '000000'
                ),
            'allfilleddata' => true
            ),
            array(
                'fill' => array(
                    'color' => 'ff0000'
                    ),
                'cells' => array(
                    'A1',
                    'C1',
                    'D1',
                    'E1',
                   'F1',
                    'I1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
            ),
                        array(
                'fill' => array(
                    'color' => '00ff00'
                    ),
                'cells' => array(
                  'B1',
                    'G1',
                    'H1',
                    'J1',
                    'K1',
                    'L1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
                )
            );

//column width
$col_width = array(
  1 => 19,
  2 => 27,
  3 => 20,
  4 => 20,
  5 => 40,
  6 => 15,
  7 => 25,
  8 => 20,
  9 => 20,
  10 => 40,
  11 => 40,
  12 => 20
  );
$writer->setColWidth($col_width);
    //unlink($filename);
    $filename = 'Data_Prestasi.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 Prestasi', $header, $style);
$writer->writeToFile(SITE_ROOT."upload/temp/".$_POST['random_number']."_".XLSXWriter::sanitize_filename(str_replace(' ','_',$filename)));

ob_end_clean();



$msg =  "<div class=\"alert alert-warning \" role=\"alert\">
         <font color=\"#3c763d\">".$jml_akhir." 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();

?>