<?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');
 


$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 = "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'";


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

        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)";
              }
        }




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

$order_by = "id_registrasi_mahasiswa ASC";

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



$temp_rec = array();
$id_mahasiswa = 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));

$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',
             'Biaya Masuk Kuliah' => 'string',
             'Asal Perguruan Tinggi' =>'string',
             'Asal Program Studi' =>'string',
             'Id Mahasiswa' => 'string',
             'Status Mahasiswa' => 'string',
             'Period Keluar' => 'string',
);


$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',
                    'H1',
                    'I1',
                    'K1',
                    'L1',
                    'M1',
                    'N1',
                    'S1',
                    'T1',
                    'Z1',
                    'AA1',
                    'AJ1',
                     'AU1',
                    'AT1',
                    'AV1',

                    

                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
            ),
                        array(
                'fill' => array(
                    'color' => '91d24a'
                    ),
                'cells' => array(
                    'J1',
                    'O1',
                    'P1',
                    'Q1',
                    'R1',
                    'U1',
                    'V1',
                    'W1',
                    'X1',
                    'Y1',
                  
                    'AB1',
                    'AC1',
                    'AD1',
                    'AE1',
                    'AF1',
                    'AG1',
                    'AH1',
                    'AI1',
                    'AK1',
                    'AL1',
                    'AM1',
                    'AN1',
                    'AO1',
                    'AP1',
                    'AQ1',
                    'AR1',
                    'AS1',
                    'AY1',
                    'AZ1',
                    'BA1'
                    ),
                #2f74b5
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
                ),

              array(
                'fill' => array(
                    'color' => '2f74b5'
                    ),
                'cells' => array(
                    'AW1',
                    'AX1',
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center'
                )
            );

//column width
$col_width = array(
  1 => 16,
  2 => 26,
  3 => 13,
  4 => 16,
  5 => 15,
  6 => 18,
  7 => 10,
  8 => 11,
  9 => 20,
  10 => 13,
  11 => 17,
  12 => 20,
  13 => 16,
  14 => 15,
 15 => 11,
  16 => 9,
  17 => 9,
  18 => 12,
  19 => 12,
  20 => 12,
  21 => 14,
  22 => 12,
  23 => 17,
  24 => 13,
  25 => 13,
  26 => 23,
  27 => 13,
  28 => 11,
  29 => 11,
  30 => 17,
 31=> 13,
  32 => 16,
  33 => 15,
  34 => 17,
  35 => 17,
  36 => 19,
  37 => 17,
  38 => 16,
  
  39 => 17,
  40 => 18,
  41 => 20,
  42 => 17,
  43 => 17,
  44 => 19,
  45 => 22,
  46 => 13,
  47 => 18,
  48 => 21,
  49 => 24,
  50 => 22,
  51 => 50,
  52 => 30,
  53 => 30
  );
$id_pt_asal = array();
$id_prodi_asal = array();
if(!empty($temp_riwayat->data)) {
  foreach ($temp_riwayat->data as $keys) {
      $key = $db->convert_obj_to_array($keys);
      //dump($key);
      $data_reg_pd[] = array(
      'nipd' => $key['nim'],
      'id_jalur_masuk' => $key['id_jalur_daftar'],
      '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'],
      'biaya_masuk' => $key['biaya_masuk'],
      'id_perguruan_tinggi_asal' => $key['id_perguruan_tinggi_asal'],
      'id_prodi_asal' => $key['id_prodi_asal'],
      'id_mahasiswa' => $key['id_mahasiswa'],
      'keterangan_keluar' => $key['keterangan_keluar'],
      'id_periode_keluar' => $key['id_periode_keluar']
    );

    $id_mahasiswa[] = "'".$key['id_mahasiswa']."'";
    if ($key['id_perguruan_tinggi_asal']!='') {
        $id_pt_asal[$key['id_perguruan_tinggi_asal']] = "'".$key['id_perguruan_tinggi_asal']."'";
    }
    if ($key['id_prodi_asal']!='') {
        $id_prodi_asal[$key['id_prodi_asal']] = "('".$key['id_perguruan_tinggi_asal']."','".$key['id_prodi_asal']."')";
    }
    
  }
}


$array_pt_asal = array();
if (!empty($id_pt_asal)) {
    $where_id_pt = implode(",", $id_pt_asal);
    $kampus = $db->fetch_custom("select id_sp,npsn from satuan_pendidikan where id_sp in($where_id_pt)");
    foreach ($kampus as $pt) {
        $array_pt_asal[$pt->id_sp] = $pt->npsn;
    }
}

$array_prodi_asal = array();
if (!empty($id_prodi_asal)) {
    $where_id_prodi = implode(",", $id_prodi_asal);
    $kampus_prodi = $db->fetch_custom("select id_sp,id_sms,kode_prodi from sms where (id_sp,id_sms) in($where_id_prodi)");
    foreach ($kampus_prodi as $prodi) {
        if ($prodi->kode_prodi!='') {
           $array_prodi_asal[$prodi->id_sp.$prodi->id_sms] = $prodi->kode_prodi;
        }
    }
}
$id_pd_concat_where = implode(",", $id_mahasiswa);

$filter_mhs = "(id_mahasiswa) IN ($id_pd_concat_where)";

$data_dic_biodata = [
      'act' => 'GetBiodataMahasiswa',
        'token' => $token,
        'filter' => $filter_mhs,
        'order' => '',
        'limit' => '',
        'offset' => ''
    ];
$temp_biodata = json_decode($db->run($data_dic_biodata,$url));
if(!empty($temp_biodata->data)) {
  foreach ($temp_biodata->data as $mahasiswa) {
    $mhs[$mahasiswa->id_mahasiswa] = $db->convert_obj_to_array($mahasiswa);
      //dump($key);
/*    $reg_pd_data = $data_reg_pd[$mhs['id_mahasiswa']];
    */
  }
}
foreach ($data_reg_pd as $reg_pd_data) {
    $kode_pt = '';
    $kode_prodi = '';
      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 = '';
      }
      if (convert_ascii($reg_pd_data['keterangan_keluar'])=="") {
        $status_mahasiswa = 'Aktif';
      } else {
        $status_mahasiswa = convert_ascii($reg_pd_data['keterangan_keluar']);
      }
      if (convert_ascii($reg_pd_data['id_periode_keluar'])=="") {
        $periode_keluar = '';
      } else {
        $periode_keluar = convert_ascii($reg_pd_data['id_periode_keluar']);
      }
      if ($reg_pd_data['id_perguruan_tinggi_asal']!='' && in_array($reg_pd_data['id_perguruan_tinggi_asal'], array_keys($array_pt_asal))) {
          $kode_pt = $array_pt_asal[$reg_pd_data['id_perguruan_tinggi_asal']];
      }
      if ($reg_pd_data['id_prodi_asal']!='' && in_array($reg_pd_data['id_prodi_asal'], array_keys($array_prodi_asal))) {
          $kode_prodi = $array_prodi_asal[$reg_pd_data['id_prodi_asal']];
      }

      $biaya = convert_ascii($reg_pd_data['biaya_masuk']);

    if (intval($biaya) == $biaya) {
        // If the number is a whole number, cast it to an integer
       $biaya = intval($biaya);
    } else {
        // If the number has a decimal part, keep it as is
        $biaya = $biaya;
    }

      $data_rec = array(
          convert_ascii($reg_pd_data['nipd']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['nama_mahasiswa']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['tempat_lahir']),
          convert_ascii(tgl_english($mhs[$reg_pd_data['id_mahasiswa']]['tanggal_lahir'])),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['jenis_kelamin']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['nik']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_agama']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['nisn']),
          convert_ascii($reg_pd_data['id_jalur_masuk']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['npwp']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_negara']),
          convert_ascii($reg_pd_data['id_jns_daftar']),
          convert_ascii(tgl_english($reg_pd_data['tgl_masuk_sp'])),
          convert_ascii($reg_pd_data['mulai_smt']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['jalan']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['rt']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['rw']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['dusun']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['kelurahan']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_wilayah']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['kode_pos']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_jenis_tinggal']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_alat_transportasi']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['telepon']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['handphone']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['email']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['penerima_kps']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['nomor_kps']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['nik_ayah']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['nama_ayah']),
          convert_ascii(tgl_english($mhs[$reg_pd_data['id_mahasiswa']]['tanggal_lahir_ayah'])),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_pendidikan_ayah']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_pekerjaan_ayah']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_penghasilan_ayah']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['nik_ibu']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['nama_ibu_kandung']),
          convert_ascii(tgl_english($mhs[$reg_pd_data['id_mahasiswa']]['tanggal_lahir_ibu'])),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_pendidikan_ibu']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_pekerjaan_ibu']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_penghasilan_ibu']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['nama_wali']),
          convert_ascii(tgl_english($mhs[$reg_pd_data['id_mahasiswa']]['tanggal_lahir_wali'])),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_pendidikan_wali']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_pekerjaan_wali']),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_penghasilan_wali']),
          $kode_jur,
          convert_ascii($reg_pd_data['id_pembiayaan']),
          convert_ascii($biaya),
          convert_ascii($kode_pt),
          convert_ascii($kode_prodi),
          convert_ascii($mhs[$reg_pd_data['id_mahasiswa']]['id_mahasiswa']),
          $status_mahasiswa,
          $periode_keluar
    );
    $merge_key_val[] = array_combine(array_keys($header), $data_rec);
}
$file_name = $_POST['random_number'];
//write temp file



// PERBAIKAN 1: Pastikan folder temp ada
if (!file_exists(SITE_ROOT . "upload/temp/")) {
    mkdir(SITE_ROOT . "upload/temp/", 0777, true);
}
$json_filename = SITE_ROOT."upload/temp/$file_name.json";
if (!empty($merge_key_val)) {
write_data($json_filename,$merge_key_val);
}





//$db->insertMulti('tmp_mhs',$array_insert);

    //        }
if ($_POST['last']=='yes') {
    if (!file_exists($json_filename) || filesize($json_filename) == 0) {
        $jumlah['last_notif'] = "<div class='alert alert-danger'>Gagal: File sumber data tidak ditemukan.</div>";
    } else {
$data_file = JsonMachine::fromFile($json_filename);
foreach ($data_file as $key => $value) {
      $final[] = array_values($value);
    }

}

      
$writer->setColWidth($col_width);
$filename = 'Data_mahasiswa.xlsx';
$writer->writeSheet($final,'Data Mahasiswa', $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\">".$_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();

?>