<?php
require(APPPATH. 'PHPExcel-1.8/Classes/PHPExcel.php');
require(APPPATH. 'PHPExcel-1.8/Classes/PHPExcel/Writer/Excel2007.php');
use PHPExcel\Style\PHPExcel_Style_Alignment;
use PHPExcel\Style\PHPExcel_Style_Fill;

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("PC Master");
$objPHPExcel->getProperties()->setLastModifiedBy("PC Master");
$objPHPExcel->getProperties()->setTitle("Laporan");
$objPHPExcel->getProperties()->setSubject("");
$objPHPExcel->getProperties()->setDescription("");
$objPHPExcel->setActiveSheetIndex(0);

$index 	= 	0;
if ($tampilGambaran==1) {
	$objPHPExcel->getActiveSheet()->setTitle("Rekap KPI (Sales)");
	
	//font
	$objPHPExcel->getDefaultStyle()
		->getFont()
		->setName("Calibri")
		->setSize(12);
	
	//heading
	$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Laporan Rekap KPI (Sales)');
	$objPHPExcel->getActiveSheet()->setCellValue('A2', $filter);
	$objPHPExcel->getActiveSheet()->setCellValue('A3', $nama_pum.' ['.$awal.' s.d '.$akhir.']');
	
	//Setting Judul
	$objPHPExcel->getActiveSheet()->mergeCells("A1:C1");
	$objPHPExcel->getActiveSheet()->mergeCells("A2:C2");
	$objPHPExcel->getActiveSheet()->mergeCells("A3:C3");
	$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(17);
	$objPHPExcel->getActiveSheet()->getStyle('A2:A3')->getFont()->setSize(15);
	$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
	$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
	$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
	$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB('9400D3');
	$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->getColor()->setRGB('008080');
	$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->getColor()->setRGB('B22222');
	
	//set cell alignment
	$objPHPExcel->getActiveSheet()->getStyle('A5')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('B5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('C5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('D5')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('D6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('E6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('F6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('G6')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('H6')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('I6')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('J5')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('J6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('K6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('L6')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('M6')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('N6')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('O5')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('O6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('P6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('Q6')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('R6')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('S6')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('T5')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	
	////lebar kolom
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('7');
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth('13');
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('24');
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth('16');
	
	//font style
	$objPHPExcel->getActiveSheet()->mergeCells("A5:A6");
	$objPHPExcel->getActiveSheet()->mergeCells("B5:B6");
	$objPHPExcel->getActiveSheet()->mergeCells("C5:C6");
	$objPHPExcel->getActiveSheet()->mergeCells("D5:I5");
	$objPHPExcel->getActiveSheet()->mergeCells("J5:N5");
	$objPHPExcel->getActiveSheet()->mergeCells("O5:S5");
	$objPHPExcel->getActiveSheet()->mergeCells("T5:T6");
	$objPHPExcel->getActiveSheet()->getStyle('A5:T6')->getFont()->setSize(14);
	$objPHPExcel->getActiveSheet()->getStyle('A5:T6')->getFont()->setBold(true);
	$objPHPExcel->getActiveSheet()->setCellValue('A5', 'No');
	$objPHPExcel->getActiveSheet()->setCellValue('B5', 'Nama');
	$objPHPExcel->getActiveSheet()->setCellValue('C5', 'Target 2026');
	$objPHPExcel->getActiveSheet()->setCellValue('D5', 'Omzet Sales');
	$objPHPExcel->getActiveSheet()->setCellValue('D6', 'Target');
	$objPHPExcel->getActiveSheet()->setCellValue('E6', 'Pencapaian');
	$objPHPExcel->getActiveSheet()->setCellValue('F6', 'Kekurangan');
	$objPHPExcel->getActiveSheet()->setCellValue('G6', 'Presentase');
	$objPHPExcel->getActiveSheet()->setCellValue('H6', 'Bobot');
	$objPHPExcel->getActiveSheet()->setCellValue('I6', 'Nilai KPI');
	$objPHPExcel->getActiveSheet()->setCellValue('J5', 'Penawaran');
	$objPHPExcel->getActiveSheet()->setCellValue('J6', 'Target');
	$objPHPExcel->getActiveSheet()->setCellValue('K6', 'Pencapaian');
	$objPHPExcel->getActiveSheet()->setCellValue('L6', 'Presentase');
	$objPHPExcel->getActiveSheet()->setCellValue('M6', 'Bobot');
	$objPHPExcel->getActiveSheet()->setCellValue('N6', 'Nilai KPI');
	$objPHPExcel->getActiveSheet()->setCellValue('O5', 'Visit Pelanggan');
	$objPHPExcel->getActiveSheet()->setCellValue('O6', 'Target');
	$objPHPExcel->getActiveSheet()->setCellValue('P6', 'Pencapaian');
	$objPHPExcel->getActiveSheet()->setCellValue('Q6', 'Presentase');
	$objPHPExcel->getActiveSheet()->setCellValue('R6', 'Bobot');
	$objPHPExcel->getActiveSheet()->setCellValue('S6', 'Nilai KPI');
	$objPHPExcel->getActiveSheet()->setCellValue('T5', "Total $nama_pum");
	
	$objPHPExcel->getActiveSheet()->getStyle('A5:C6')->getFill()->applyFromArray (
		array(
			'type'       => 'FILL_SOLID',
			'rotation'   => 0,
			'startcolor' => array(
				'rgb' => 'FFFACD'
			),
			'endcolor'   => array(
				'argb' => 'FFFFFFFF'
			)
		)
	);
	
	$objPHPExcel->getActiveSheet()->getStyle('D5:I6')->getFill()->applyFromArray (
		array(
			'type'       => 'FILL_SOLID',
			'rotation'   => 0,
			'startcolor' => array(
				'rgb' => 'FFA07A'
			),
			'endcolor'   => array(
				'argb' => 'FFFFFFFF'
			)
		)
	);
	
	$objPHPExcel->getActiveSheet()->getStyle('J5:N6')->getFill()->applyFromArray (
		array(
			'type'       => 'FILL_SOLID',
			'rotation'   => 0,
			'startcolor' => array(
				'rgb' => 'D3D3D3'
			),
			'endcolor'   => array(
				'argb' => 'FFFFFFFF'
			)
		)
	);
	
	$objPHPExcel->getActiveSheet()->getStyle('O5:S6')->getFill()->applyFromArray (
		array(
			'type'       => 'FILL_SOLID',
			'rotation'   => 0,
			'startcolor' => array(
				'rgb' => '90EE90'
			),
			'endcolor'   => array(
				'argb' => 'FFFFFFFF'
			)
		)
	);
	
	$objPHPExcel->getActiveSheet()->getStyle('T5:T6')->getFill()->applyFromArray (
		array(
			'type'       => 'FILL_SOLID',
			'rotation'   => 0,
			'startcolor' => array(
				'rgb' => '87CEFA'
			),
			'endcolor'   => array(
				'argb' => 'FFFFFFFF'
			)
		)
	);
	
	$objPHPExcel->getActiveSheet()->freezePane('A7');
	$index++;
}	
	
//Worksheet untuk Detail Barang
$norek		=	7;
$savepot	=	0;
$nama_sales	=	NULL;
foreach ($laporan as $key) {
	if ($nama_sales!=$key['NAMA_PANGGILAN_KARYAWAN']) {
		$nama_sales	=	$key['NAMA_PANGGILAN_KARYAWAN'];
		
		if ($index>0) {
			$objPHPExcel->createSheet();
		}
		$objPHPExcel->setActiveSheetIndex($index);
		$objPHPExcel->getActiveSheet()->setTitle("$nama_sales");
		
		//font
		$objPHPExcel->getDefaultStyle()
			->getFont()
			->setName("Calibri")
			->setSize(12);
		
		//heading
		$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Karyawan : ('.$nama_sales.')');
		$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Jabatan : ('.$key['NAMA_JABATAN'].')');
		$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Periode '.$nama_pum.' ['.$awal.' s.d '.$akhir.']');
		
		$sel = array(
			'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H'
		);
		
		$nt = [
			'No' 			=> 'No',
			'Parameter'		=> 'NAMA_KPI',
			'Satuan'		=> 'NAMA_SATUAN',
			'Target'		=> 'target',
			'Pencapaian'	=> 'PENCAPAIAN',
			'Presentase'	=> 'presentase',
			'Bobot'			=> 'bobot',
			'Nilai KPI'		=> 'nilai',
		];

		//Modified Style
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
		$objPHPExcel->getActiveSheet()->getStyle('A2:A3')->getFont()->setSize(16);
	
		$objPHPExcel->getActiveSheet()->mergeCells("A1:" . $sel[$jumlah_kolom - 1] . "1");
		$objPHPExcel->getActiveSheet()->getStyle("A1")->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB('9400D3');
		
		$objPHPExcel->getActiveSheet()->mergeCells("A2:" . $sel[$jumlah_kolom - 1] . "2");
		$objPHPExcel->getActiveSheet()->getStyle("A2")->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->getColor()->setRGB('008080');
		
		$objPHPExcel->getActiveSheet()->mergeCells("A3:" . $sel[$jumlah_kolom - 1] . "3");
		$objPHPExcel->getActiveSheet()->getStyle("A3")->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->getColor()->setRGB('B22222');
		
		$objPHPExcel->getActiveSheet()->getStyle('A5:' . $sel[$jumlah_kolom - 1] . '5')->getFont()->setSize(13);
		$objPHPExcel->getActiveSheet()->getStyle('A5:' . $sel[$jumlah_kolom - 1] . '5')->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getStyle('A5:' . $sel[$jumlah_kolom - 1] . '5')->getFill()->applyFromArray(
			array(
				'type'       => 'FILL_SOLID',
				'rotation'   => 0,
				'startcolor' => array(
					'rgb' => '9fc5e8'
				),
				'endcolor'   => array(
					'argb' => 'FFFFFFFF'
				)
			)
		);
		
		$objPHPExcel->getActiveSheet()->getStyle('C5')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('D5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('E5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('F5')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('G5')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('H5')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
		
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth('12');
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
		
		//Isi Name Tabel
		$no = 0;
		foreach ($nama_tabel as $tbl) {
			$objPHPExcel->getActiveSheet()->setCellValue($sel[$no] . '5', $tbl);
			$no++;
		}

		//Isi Laporan
		$no			=	0;
		$nolpr 		=	1;
		$nocell 	=	6;
		foreach ($key['ISI'] as $lprn) {
			foreach ($nama_tabel as $tbl) {
				$objPHPExcel->setActiveSheetIndex($index);
				$colrek	=	"";
				if ($tbl == "No") {
					$objPHPExcel->getActiveSheet()->setCellValue($sel[$no] . '' . $nocell . '', $nolpr);
				} else if ($tbl == "Presentase") {
					$presentase	=	$lprn['PRESENTASE'];
					$presentase	=	"$presentase%";
					$objPHPExcel->getActiveSheet()->setCellValue($sel[$no] . '' . $nocell . '', $presentase);
					// Isi Rekap
					if ($tampilGambaran==1) {
						$objPHPExcel->setActiveSheetIndex(0);
						if ($lprn['KPI_ID']==3) {
							$colrek	=	"G";
						} else if ($lprn['KPI_ID']==4) {
							$colrek	=	"L";
						} else if ($lprn['KPI_ID']==5) {
							$colrek	=	"Q";
						}
						if ($colrek!="") {
							$objPHPExcel->getActiveSheet()->setCellValue("$colrek".$norek, $presentase);
							$objPHPExcel->getActiveSheet()->getStyle("$colrek".$norek)->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
						}
					}
				} else if ($tbl == "Bobot") {
					$bobot	=	$lprn['BOBOT'];
					$bobot	=	"$bobot%";
					$objPHPExcel->getActiveSheet()->setCellValue($sel[$no] . '' . $nocell . '', $bobot);
					// Isi Rekap
					if ($tampilGambaran==1) {
						$objPHPExcel->setActiveSheetIndex(0);
						if ($lprn['KPI_ID']==3) {
							$colrek	=	"H";
						} else if ($lprn['KPI_ID']==4) {
							$colrek	=	"M";
						} else if ($lprn['KPI_ID']==5) {
							$colrek	=	"R";
						}
						if ($colrek!="") {
							$objPHPExcel->getActiveSheet()->setCellValue("$colrek".$norek, $bobot);
							$objPHPExcel->getActiveSheet()->getStyle("$colrek".$norek)->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
						}
					}
				} else if ($tbl == "Nilai KPI") {
					$nilai	=	$lprn['NILAI'];
					$nilai	=	"$nilai%";
					$objPHPExcel->getActiveSheet()->setCellValue($sel[$no] . '' . $nocell . '', $nilai);
					// Isi Rekap
					if ($tampilGambaran==1) {
						$objPHPExcel->setActiveSheetIndex(0);
						if ($lprn['KPI_ID']==3) {
							$colrek	=	"I";
						} else if ($lprn['KPI_ID']==4) {
							$colrek	=	"N";
						} else if ($lprn['KPI_ID']==5) {
							$colrek	=	"S";
						}
						if ($colrek!="") {
							$objPHPExcel->getActiveSheet()->setCellValue("$colrek".$norek, $nilai);
							$objPHPExcel->getActiveSheet()->getStyle("$colrek".$norek)->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
						}
					}
				} else if ($tbl == "Target") {
					if ($lprn['KPI_ID']==2) {
						$target	=	round($lprn['TARGET'], 0);
						$target	=	"$target%";
					} else if ($lprn['KPI_ID']==5) {
						$target	=	round($lprn['TARGET'], 0);
					} else {
						$target	=	$lprn['TARGET'];
					}
					$objPHPExcel->getActiveSheet()->setCellValueExplicit($sel[$no] . '' . $nocell . '', $target, PHPExcel_Cell_DataType::TYPE_STRING);
					// Isi Rekap
					if ($tampilGambaran==1) {
						$objPHPExcel->setActiveSheetIndex(0);
						if ($lprn['KPI_ID']==3) {
							$colrek		=	"D";
						} else if ($lprn['KPI_ID']==4) {
							$colrek	=	"J";
						} else if ($lprn['KPI_ID']==5) {
							$colrek	=	"O";
						}
						if ($colrek!="") {
							$objPHPExcel->getActiveSheet()->setCellValueExplicit("$colrek".$norek, $target, PHPExcel_Cell_DataType::TYPE_STRING);
							$objPHPExcel->getActiveSheet()->getStyle("$colrek".$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
						}
					}
				} else if ($tbl == "Pencapaian") {
					$pencapaian	=	$lprn['PENCAPAIAN'];
					if ($lprn['KPI_ID']==2) {
						$pencapaian	=	"$pencapaian%";
						if ($nama_sales=="Anton") {
							$savepot	=	$lprn['PENCAPAIAN'];
						}
					}
					$objPHPExcel->getActiveSheet()->setCellValueExplicit($sel[$no] . '' . $nocell . '', $pencapaian, PHPExcel_Cell_DataType::TYPE_STRING);
					// Isi Rekap
					if ($tampilGambaran==1) {
						$objPHPExcel->setActiveSheetIndex(0);
						if ($lprn['KPI_ID']==3) {
							$colrek	=	"E";
						} else if ($lprn['KPI_ID']==4) {
							$colrek	=	"K";
						} else if ($lprn['KPI_ID']==5) {
							$colrek	=	"P";
						}
						if ($colrek!="") {
							$objPHPExcel->getActiveSheet()->setCellValueExplicit("$colrek".$norek, $pencapaian, PHPExcel_Cell_DataType::TYPE_STRING);
							if ($lprn['KPI_ID']==3) {
								$objPHPExcel->getActiveSheet()->setCellValueExplicit("F".$norek, $lprn['KEKURANGAN'], PHPExcel_Cell_DataType::TYPE_STRING);
								$objPHPExcel->getActiveSheet()->getStyle("F".$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
							}
							$objPHPExcel->getActiveSheet()->getStyle("$colrek".$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
						}
					}
				} else {
					$objPHPExcel->getActiveSheet()->setCellValue($sel[$no] . '' . $nocell . '', $lprn[$nt[$tbl]]);
				}
				$no++;
			}
			
			$no	=	0;
			$nocell++;
			$nolpr++;
		}
		
		$objPHPExcel->setActiveSheetIndex($index);
		$objPHPExcel->getActiveSheet()->freezePane('A6');
		$objPHPExcel->getActiveSheet()->mergeCells('A'.$nocell.':F'.$nocell);
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$nocell, "TOTAL");
		$objPHPExcel->getActiveSheet()->getStyle('A'.$nocell)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->setCellValue('G'.$nocell, $key['TOTBOBOT'].'%');
		$objPHPExcel->getActiveSheet()->setCellValue('H'.$nocell, $key['TOTNILAI'].'%');
		$objPHPExcel->getActiveSheet()->getStyle('A'.$nocell.':H'.$nocell)->getFont()->setSize(14);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$nocell.':H'.$nocell)->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$nocell.':H'.$nocell)->getFill()->applyFromArray(
			array(
				'type'       => 'FILL_SOLID',
				'rotation'   => 0,
				'startcolor' => array(
					'rgb' => 'cfe2f3'
				),
				'endcolor'   => array(
					'argb' => 'FFFFFFFF'
				)
			)
		);

		$objPHPExcel->getActiveSheet()->getStyle('A5:H'.$nocell)->applyFromArray(
			array(
				'borders' => array(
					'allborders' => array(
						'style' => PHPExcel_Style_Border::BORDER_THIN,
						'color' => array('rgb' => 'FF000000')
					)
				)
			)
		);
		
		// Isi Tabel Rekap
		if ($tampilGambaran==1) {
			$objPHPExcel->setActiveSheetIndex(0);
			$objPHPExcel->getActiveSheet()->setCellValue('A'.$norek, $index);
			$objPHPExcel->getActiveSheet()->setCellValue('B'.$norek, $nama_sales);
			$objPHPExcel->getActiveSheet()->setCellValue('C'.$norek, $key['TARTAHUN']);
			$objPHPExcel->getActiveSheet()->getStyle('C'.$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
			if ($nama_sales!="Anton") {
				$objPHPExcel->getActiveSheet()->getStyle('T'.$norek)->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
				$objPHPExcel->getActiveSheet()->setCellValue('T'.$norek, $key['TOTNILAI'].'%');
			}
			$norek++;
		}
		$index++;
	}
}
	
if ($tampilGambaran==1) {
	//Isi Rekap Data		
	$objPHPExcel->setActiveSheetIndex(0);

	//Isi jika ada Sales tanpa Target
	foreach ($tambahan as $key) {
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$norek, $index++);
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$norek, $key['NAMA_PANGGILAN_KARYAWAN']);
		$objPHPExcel->getActiveSheet()->getStyle('E'.$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
		$objPHPExcel->getActiveSheet()->mergeCells('F'.$norek.':T'.$norek);
		$objPHPExcel->getActiveSheet()->setCellValueExplicit("E".$norek++, $key['PENCAPAIAN'], PHPExcel_Cell_DataType::TYPE_STRING);
	}

	$objPHPExcel->getActiveSheet()->setCellValue('C'.$norek, $totTarTahun);
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$norek, $totTarOmz);
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$norek, $totPenOmz);
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$norek, $totKekOmz);
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$norek, $totPreOmz.'%');
	$objPHPExcel->getActiveSheet()->setCellValue('J'.$norek, $totTarPen);
	$objPHPExcel->getActiveSheet()->setCellValue('K'.$norek, $totPenPen);
	$objPHPExcel->getActiveSheet()->setCellValue('L'.$norek, $totPrePen.'%');
	$objPHPExcel->getActiveSheet()->setCellValue('O'.$norek, $totTarVis);
	$objPHPExcel->getActiveSheet()->setCellValue('P'.$norek, $totPenVis);
	$objPHPExcel->getActiveSheet()->setCellValue('Q'.$norek, $totPreVis.'%');
	$objPHPExcel->getActiveSheet()->setCellValue('T'.$norek, $savepot.'%');
	$objPHPExcel->getActiveSheet()->getStyle('A'.$norek.':T'.$norek)->getFont()->setSize(14);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$norek.':T'.$norek)->getFont()->setBold(true);
	$objPHPExcel->getActiveSheet()->getStyle('C'.$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('D'.$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('E'.$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('F'.$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('G'.$norek)->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('J'.$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('K'.$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('L'.$norek)->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('O'.$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('P'.$norek)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('Q'.$norek)->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
	$objPHPExcel->getActiveSheet()->getStyle('T'.$norek)->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');

	$objPHPExcel->getActiveSheet()->getStyle('A5:T'.$norek)->applyFromArray(
		array(
			'borders' => array(
				'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN,
					'color' => array('rgb' => 'FF000000')
				)
			)
		)
	);

	$objPHPExcel->getActiveSheet()->getStyle('A'.$norek.':C'.$norek)->getFill()->applyFromArray (
		array(
			'type'       => 'FILL_SOLID',
			'rotation'   => 0,
			'startcolor' => array(
				'rgb' => 'FFFACD'
			),
			'endcolor'   => array(
				'argb' => 'FFFFFFFF'
			)
		)
	);

	$objPHPExcel->getActiveSheet()->getStyle('D'.$norek.':I'.$norek)->getFill()->applyFromArray (
		array(
			'type'       => 'FILL_SOLID',
			'rotation'   => 0,
			'startcolor' => array(
				'rgb' => 'FFA07A'
			),
			'endcolor'   => array(
				'argb' => 'FFFFFFFF'
			)
		)
	);

	$objPHPExcel->getActiveSheet()->getStyle('J'.$norek.':N'.$norek)->getFill()->applyFromArray (
		array(
			'type'       => 'FILL_SOLID',
			'rotation'   => 0,
			'startcolor' => array(
				'rgb' => 'D3D3D3'
			),
			'endcolor'   => array(
				'argb' => 'FFFFFFFF'
			)
		)
	);

	$objPHPExcel->getActiveSheet()->getStyle('O'.$norek.':S'.$norek)->getFill()->applyFromArray (
		array(
			'type'       => 'FILL_SOLID',
			'rotation'   => 0,
			'startcolor' => array(
				'rgb' => '90EE90'
			),
			'endcolor'   => array(
				'argb' => 'FFFFFFFF'
			)
		)
	);

	$objPHPExcel->getActiveSheet()->getStyle('T'.$norek)->getFill()->applyFromArray (
		array(
			'type'       => 'FILL_SOLID',
			'rotation'   => 0,
			'startcolor' => array(
				'rgb' => '87CEFA'
			),
			'endcolor'   => array(
				'argb' => 'FFFFFFFF'
			)
		)
	);
}

$filename = "Rekap KPI (Sales) ".$filter." ". $ts. '.xlsx';

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');

$writer = PHPExcel_IOFactory::createwriter($objPHPExcel, 'Excel2007');
$writer->save('php://output');
exit();

?>