<?php
require_once GTFWConfiguration::GetValue( 'application', 'docroot') . 'module/Laporan_formasi_calon_mhs_baru/business/AppLaporanFormasiCalonMhsBaru.class.php';
require_once GTFWConfiguration::GetValue( 'application', 'docroot') . 'main/function/date.php';
class ViewLaporanFormasiCalonMhsBaru extends XlsResponse{
   var $mWorksheets = array('Data');
   function GetFileName() {
      // name it whatever you want
      return 'DaftarMahasiswa.xls';
   }
   function ProcessRequest(){
      $lapObj = new AppLaporanFormasiCalonMhsBaru();
      
      $tahunMasuk=$_SESSION['tahunMasuk'];
      $semsMasuk=$_SESSION['semsMasuk'];
      $jenjang=$_SESSION['jenjang'];
      $jalurMasuk=$_SESSION['jalurMasuk'];
      
      if($tahunMasuk=='all'){
         $tahunMasuk='';
      }       
      
      if($semsMasuk=='all'){
         $semsMasuk='';
      }      
      
      if($jenjang=='all'){
         $jenjang='';
      }  
      
      if($jalurMasuk=='all'){
         $jalurMasuk='';
      }   
      
      $dataLaporanJmlMhsBaru = $lapObj->GetRekapMhsBaru($tahunMasuk,$semsMasuk,$jalur,$jenjang);
      
      $dataPeriodeAktif = $lapObj->GetPeriodeAktif();
      if(empty($dataLaporanJmlMhsBaru)){
         $this->mWorksheets['Data']->write(0, 0, 'Tidak Ada Data');
      }else{
      # Start Formating WorkBook
         $h1 = $this->mrWorkbook->add_format();
         $h1->set_bold();
         $h1->set_size(14);
         $h1->set_align('vcenter');
         
         $h2 = $this->mrWorkbook->add_format();
         $h2->set_bold();
         $h2->set_size(12);
         $h2->set_align('left');
         $h2->set_align('vcenter');
         $h2->set_text_wrap();
         
         $h3 = $this->mrWorkbook->add_format();
         $h3->set_bold();
         $h3->set_size(11);
         $h3->set_align('left');
         $h3->set_align('vcenter');
         
         $strong = $this->mrWorkbook->add_format();
         $strong->set_bold();
         
         $sign = $this->mrWorkbook->add_format();
         $sign->set_size(10);
         $sign->set_align('center');
         
         $left = $this->mrWorkbook->add_format();
         $left->set_size(10);
         $left->set_align('left');
         
         #set colom
         $th = $this->mrWorkbook->add_format();
         $th->set_border(1);
         $th->set_bold();
         $th->set_size(10);
         $th->set_align('center');
         $th->set_align('vcenter');
         $th->set_text_wrap();
         
         
         $td = $this->mrWorkbook->add_format();
         $td->set_border(1);
         $td->set_size(10);         
         $td->set_align('left');         
         $td->set_text_wrap();         
         
         $tdBold = $this->mrWorkbook->add_format();
         $tdBold->set_border(1);
         $tdBold->set_size(10);         
         $tdBold->set_align('left');         
         $tdBold->set_text_wrap();
         $tdBold->set_bold();
         
         $tdTop = $this->mrWorkbook->add_format();
         $tdTop->set_left(1);
         $tdTop->set_right(1);
         $tdTop->set_top(1);
         $tdTop->set_size(10);         
         $tdTop->set_align('left');
         $tdTop->set_text_wrap();
         
         $tdMid = $this->mrWorkbook->add_format();
         $tdMid->set_left(1);
         $tdMid->set_right(1);
         $tdMid->set_size(10);         
         $tdMid->set_align('left'); 
         
         $tdCenter = $this->mrWorkbook->add_format();
         $tdCenter->set_border(1);
         $tdCenter->set_size(10);         
         $tdCenter->set_align('center');         
         
         $tdMidCenter = $this->mrWorkbook->add_format();
         $tdMidCenter->set_left(1);
         $tdMidCenter->set_right(1);
         $tdMidCenter->set_size(10);         
         $tdMidCenter->set_align('center');         
         $tdMidCenter->set_text_wrap();  
         
         $tdBtmCenter = $this->mrWorkbook->add_format();
         $tdBtmCenter->set_left(1);
         $tdBtmCenter->set_right(1);
         $tdBtmCenter->set_bottom(1);
         $tdBtmCenter->set_size(10);         
         $tdBtmCenter->set_align('center'); 
         $tdBtmCenter->set_text_wrap();
         
         $tdTopCenter = $this->mrWorkbook->add_format();
         $tdTopCenter->set_left(1);
         $tdTopCenter->set_right(1);
         $tdTopCenter->set_top(1);
         $tdTopCenter->set_size(10);         
         $tdTopCenter->set_align('center');
         $tdTopCenter->set_text_wrap();
                 
         
         $tdRight = $this->mrWorkbook->add_format();
         $tdRight->set_border(0);
         $tdRight->set_size(10);         
         $tdRight->set_align('right');
         $tdRight->set_text_wrap();
         
         $tdValueMid = $this->mrWorkbook->add_format();
         $tdValueMid->set_left(1);
         $tdValueMid->set_right(1);
         $tdValueMid->set_size(7);         
         $tdValueMid->set_align('center');   
         
         $tdValueBtm = $this->mrWorkbook->add_format();
         $tdValueBtm->set_left(1);
         $tdValueBtm->set_right(1);
         $tdValueBtm->set_bottom(1);
         $tdValueBtm->set_size(7);         
         $tdValueBtm->set_align('center');  
         

            
         $this->mWorksheets['Data']->set_column(0, 0, 10);
         $this->mWorksheets['Data']->set_column(1, 1, 50);
         $this->mWorksheets['Data']->set_column(2, 2, 8);
         $this->mWorksheets['Data']->set_column(3, 3, 8);
         $this->mWorksheets['Data']->set_column(4, 4, 8);        
         $this->mWorksheets['Data']->set_column(5, 5, 8);
         $this->mWorksheets['Data']->set_column(6, 6, 8);
         $this->mWorksheets['Data']->set_column(7, 7, 8);         
         $this->mWorksheets['Data']->set_column(8, 8, 8);
         $this->mWorksheets['Data']->set_column(9, 9, 8);         
         $this->mWorksheets['Data']->set_column(10, 10, 8);
         $this->mWorksheets['Data']->set_column(11, 11, 8);
         $this->mWorksheets['Data']->set_column(12, 12, 8);         
         $this->mWorksheets['Data']->set_column(13, 13, 8);
         $this->mWorksheets['Data']->set_column(14, 14, 8);
         $this->mWorksheets['Data']->set_column(15, 16, 8);

      #End Formating WorkBook
      
      #Start Create Header Report
         $row = 0;
         $this->mWorksheets['Data']->write($row, 0, GTFWConfiguration::GetValue('organization', 'company_name'), $h1);
         $this->mWorksheets['Data']->merge_cells($row, 0, 1, 7);
         
         $row+=3;
         $this->mWorksheets['Data']->write($row, 0, 'FORMASI CALON MAHASISWA BARU YANG LULUS PILIHAN 1 DAN 2 ', $h2);
         $this->mWorksheets['Data']->merge_cells($row, 0, $row, 5);
         $row++;
         $periode=$tahunMasuk;
         $periode2=$tahunMasuk+1;
         if($periode!=''){
         $this->mWorksheets['Data']->write($row, 0,'TAHUN AKADEMIK :'. $semsMasuk. ' ' .$periode .'/ '.$periode2 , $h3);
         }else{
         $this->mWorksheets['Data']->write($row, 0,'Semua' , $h3);

         }
         $this->mWorksheets['Data']->merge_cells($row, 0, $row, 5);
         $row++;;
         #$this->mWorksheets['Data']->write($row, 0,'(Menurut Asal Sekolah)', $h3);
$row+=1;

      # End Create Header Report

      #Start Create Header Table
         $row+=2;
         $this->mWorksheets['Data']->write($row, 0, 'Fakultas/Jurusan/Prodi', $th);
         $this->mWorksheets['Data']->write($row, 1, '', $th);
         $this->mWorksheets['Data']->write($row+1, 0, '', $th);
         $this->mWorksheets['Data']->write($row+1, 1, '', $th);
         $this->mWorksheets['Data']->merge_cells($row, 0, $row+1, 1);

         $this->mWorksheets['Data']->write($row, 2, 'Formasi', $th);
         $this->mWorksheets['Data']->write($row, 3, '', $th);
         $this->mWorksheets['Data']->write($row, 4, '', $th);
         $this->mWorksheets['Data']->write($row, 5, '', $th);
         $this->mWorksheets['Data']->merge_cells($row, 2, $row, 5);
         
         $this->mWorksheets['Data']->write($row+1, 2, 'Kls SNMPTN', $th);
         $this->mWorksheets['Data']->write($row+1, 3, 'Kls Reguler', $th);
         $this->mWorksheets['Data']->write($row+1, 4, 'Isi Kls', $th);
         $this->mWorksheets['Data']->write($row+1, 5, 'Formasi', $th);
         
         $this->mWorksheets['Data']->write($row, 6, 'Jalur PMB', $th);
         $this->mWorksheets['Data']->write($row, 7, '', $th);
         $this->mWorksheets['Data']->write($row, 8, '', $th);
         $this->mWorksheets['Data']->write($row, 9, '', $th);
         $this->mWorksheets['Data']->write($row, 10, '', $th);
         $this->mWorksheets['Data']->write($row, 11, '', $th);
         $this->mWorksheets['Data']->write($row, 12, '', $th);
         $this->mWorksheets['Data']->write($row, 13, '', $th);
         $this->mWorksheets['Data']->write($row, 14, '', $th);
         $this->mWorksheets['Data']->write($row, 15, '', $th);
         $this->mWorksheets['Data']->merge_cells($row, 6, $row, 15);
         
         $this->mWorksheets['Data']->write($row+1, 6, 'Pendf. PPA', $th);
         $this->mWorksheets['Data']->write($row+1, 7, 'Lulus. PPA', $th);
         $this->mWorksheets['Data']->write($row+1, 8, 'Reg. PPA', $th);
         $this->mWorksheets['Data']->write($row+1, 9, 'Pendf. SNMPTN I,II,III', $th);
         $this->mWorksheets['Data']->write($row+1, 10, 'Lulus SNMPTN', $th);
         $this->mWorksheets['Data']->write($row+1, 11, 'Reg SNMPTN', $th);
         $this->mWorksheets['Data']->write($row+1, 12, 'Pendf. UTL', $th);
         $this->mWorksheets['Data']->write($row+1, 13, 'Lulus UTL', $th);
         $this->mWorksheets['Data']->write($row+1, 14, 'Reg UTL', $th);
         $this->mWorksheets['Data']->write($row+1, 15, 'Jumlah Mhs baru', $th);
         
         $row+=2;
         
         $this->mWorksheets['Data']->write($row, 0, '1', $th);
         $this->mWorksheets['Data']->write($row, 1, '', $th);
         $this->mWorksheets['Data']->merge_cells($row, 0, $row, 1);
         $this->mWorksheets['Data']->write($row, 2, '', $th);
         $this->mWorksheets['Data']->write($row, 3, '2', $th);
         $this->mWorksheets['Data']->write($row, 4, '3', $th);
         $this->mWorksheets['Data']->write($row, 5, '4', $th);
         $this->mWorksheets['Data']->write($row, 6, '5', $th);
         $this->mWorksheets['Data']->write($row, 7, '6', $th);
         $this->mWorksheets['Data']->write($row, 8, '7', $th);
         $this->mWorksheets['Data']->write($row, 9, '8', $th);
         $this->mWorksheets['Data']->write($row, 10, '', $th);
         $this->mWorksheets['Data']->write($row, 11, '9', $th);
         $this->mWorksheets['Data']->write($row, 12, '10', $th);
         $this->mWorksheets['Data']->write($row, 13, '11', $th);
         $this->mWorksheets['Data']->write($row, 14, '12', $th);
         $this->mWorksheets['Data']->write($row, 15, '13', $th);
         
        
      # End Create Header Table
      # Start Generate Row Data
         $no=0;
         $fakKode='';
for($i=0;$i<sizeOf($dataLaporanJmlMhsBaru);$i++){
            if($fakKode!=$dataLaporanJmlMhsBaru[$i]['fak_kode']){
               $dataLaporan[$no]['fak_nama']=$dataLaporanJmlMhsBaru[$i]['fak_nama'];
               $dataLaporan[$no]['no']=$no+1;
               $no++;
             $j=0;
            }
            $dataProdi[$no-1][$j]['prodi_kode']= $dataLaporanJmlMhsBaru[$i]['prodi_kode'];
            $dataProdi[$no-1][$j]['prodi_nama']= $dataLaporanJmlMhsBaru[$i]['prodi_nama'];
            $dataProdi[$no-1][$j]['mhs_lulus_ppa']= $dataLaporanJmlMhsBaru[$i]['lulus_ppa'];
            $dataProdi[$no-1][$j]['mhs_reg_ppa']= $dataLaporanJmlMhsBaru[$i]['reg_ppa'];
            $dataProdi[$no-1][$j]['mhs_lulus_snmptn']= $dataLaporanJmlMhsBaru[$i]['lulus_snmptn'];
            $dataProdi[$no-1][$j]['mhs_reg_snmptn']= $dataLaporanJmlMhsBaru[$i]['reg_snmptn'];
            $dataProdi[$no-1][$j]['mhs_lulus_utl']= $dataLaporanJmlMhsBaru[$i]['lulus_utl'];
            $dataProdi[$no-1][$j]['mhs_reg_utl']= $dataLaporanJmlMhsBaru[$i]['reg_utl'];
            $dataProdi[$no-1][$j]['jml_mhs_baru']= $dataProdi[$no-1][$j]['mhs_lulus_ppa']+
                                                   $dataProdi[$no-1][$j]['mhs_reg_ppa']+
                                                   $dataProdi[$no-1][$j]['mhs_lulus_snmptn']+
                                                   $dataProdi[$no-1][$j]['mhs_reg_snmptn']+
                                                   $dataProdi[$no-1][$j]['mhs_lulus_utl']+
                                                   $dataProdi[$no-1][$j]['mhs_reg_utl'];
                                                   
            $totalLulusPPA+=$dataLaporanJmlMhsBaru[$i]['lulus_ppa'];
            $totalRegPPA+=$dataLaporanJmlMhsBaru[$i]['reg_ppa'];
            $totalLulusSNMPTN+=$dataLaporanJmlMhsBaru[$i]['lulus_snmptn'];
            $totalRegSNMPTN+=$dataLaporanJmlMhsBaru[$i]['reg_snmptn'];
            $totalLulusUTL+=$dataLaporanJmlMhsBaru[$i]['lulus_utl'];
            $totalRegUtl+=$dataLaporanJmlMhsBaru[$i]['reg_utl'];

            $j++;
            $fakKode=$dataLaporanJmlMhsBaru[$i]['fak_kode'];
         }
         $row++;
         $totalMhsBaru=$totalLulusPPA+$totalRegPPA+$totalLulusSNMPTN+$totalRegSNMPTN+$totalLulusUTL+$totalRegUtl;
         //print_r($dataLaporan);
         for($i=0;$i<sizeof($dataLaporan);$i++){
            $this->mWorksheets['Data']->write($row, 0, $dataLaporan[$i]['fak_nama'], $tdBold);
            $this->mWorksheets['Data']->write($row, 1, '', $td);
            $this->mWorksheets['Data']->write($row, 2, '', $td);
            $this->mWorksheets['Data']->write($row, 3, '', $td);
            $this->mWorksheets['Data']->write($row, 4, '', $td);            
            $this->mWorksheets['Data']->write($row, 5, '', $td);
            $this->mWorksheets['Data']->write($row, 6, '', $td);
            $this->mWorksheets['Data']->write($row, 7, '', $td);
            $this->mWorksheets['Data']->write($row, 8, '', $td);
            $this->mWorksheets['Data']->write($row, 9, '', $td);
            $this->mWorksheets['Data']->write($row, 10, '', $td);            
            $this->mWorksheets['Data']->write($row, 11, '', $td);
            $this->mWorksheets['Data']->write($row, 12, '', $td);
            $this->mWorksheets['Data']->write($row, 13, '', $td);
            $this->mWorksheets['Data']->write($row, 14, '', $td);
            $this->mWorksheets['Data']->write($row, 15, '', $td);            

            $this->mWorksheets['Data']->merge_cells($row, 0, $row, 15);
            $row++;
            for($j=0;$j<sizeof($dataProdi[$i]);$j++){
               $this->mWorksheets['Data']->write($row, 0, $dataProdi[$i][$j]['prodi_kode'], $td);
               $this->mWorksheets['Data']->write($row, 1, $dataProdi[$i][$j]['prodi_nama'], $td);
               $this->mWorksheets['Data']->write($row, 2, '', $td);
               $this->mWorksheets['Data']->write($row, 3, '', $td);
               $this->mWorksheets['Data']->write($row, 4, '', $td);            
               $this->mWorksheets['Data']->write($row, 5, '', $td);
               $this->mWorksheets['Data']->write($row, 6, '', $td);
               $this->mWorksheets['Data']->write($row, 7, $dataProdi[$i][$j]['mhs_lulus_ppa'], $td);
               $this->mWorksheets['Data']->write($row, 8, $dataProdi[$i][$j]['mhs_reg_ppa'], $td);
               $this->mWorksheets['Data']->write($row, 9, '', $td);
               $this->mWorksheets['Data']->write($row, 10, $dataProdi[$i][$j]['mhs_lulus_snmptn'], $td);
               $this->mWorksheets['Data']->write($row, 11, $dataProdi[$i][$j]['mhs_reg_snmptn'], $td);
               $this->mWorksheets['Data']->write($row, 12, '', $td);
               $this->mWorksheets['Data']->write($row, 13, $dataProdi[$i][$j]['mhs_lulus_utl'], $td);
               $this->mWorksheets['Data']->write($row, 14, $dataProdi[$i][$j]['mhs_reg_utl'], $td);
               $this->mWorksheets['Data']->write($row, 15, $dataProdi[$i][$j]['jml_mhs_baru'], $td);
               $row++;
           }
         }
         $this->mWorksheets['Data']->write($row, 0, 'JUMLAH SELURUHNYA', $tdBold);
         $this->mWorksheets['Data']->merge_cells($row, 0, $row, 1);
         $this->mWorksheets['Data']->write($row, 1, '', $tdBold);
         $this->mWorksheets['Data']->write($row, 2, '', $td);
         $this->mWorksheets['Data']->write($row, 3, '', $td);
         $this->mWorksheets['Data']->write($row, 4, '', $td);            
         $this->mWorksheets['Data']->write($row, 5, '', $td);
         $this->mWorksheets['Data']->write($row, 6, '', $td);
         $this->mWorksheets['Data']->write($row, 7, $totalLulusPPA, $tdBold);
         $this->mWorksheets['Data']->write($row, 8, $totalRegPPA, $tdBold);
         $this->mWorksheets['Data']->write($row, 9, '', $td);
         $this->mWorksheets['Data']->write($row, 10, $totalLulusSNMPTN, $tdBold);
         $this->mWorksheets['Data']->write($row, 11, $totalRegSNMPTN, $tdBold);
         $this->mWorksheets['Data']->write($row, 12, '', $td);
         $this->mWorksheets['Data']->write($row, 13, $totalLulusUTL, $tdBold);
         $this->mWorksheets['Data']->write($row, 14, $totalRegUtl, $tdBold);
         $this->mWorksheets['Data']->write($row, 15, $totalMhsBaru, $tdBold);
      # End Generate Row Data
      }
   }
}