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

		/////// font
		$index 			= 	0;
		foreach ($laporan as $key) {
			$title = $key['title'];
			
			if ($index==0) {
				$objPHPExcel->setActiveSheetIndex($index);
				$objPHPExcel->getActiveSheet()->setTitle("$title");
			} else {
				$objPHPExcel->createSheet();
				$objPHPExcel->setActiveSheetIndex($index);
				$objPHPExcel->getActiveSheet()->setTitle("$title");
			}
			
			$objPHPExcel->getDefaultStyle()
				->getFont()
				->setName("Calibri")
				->setSize(11);
	
			//////heading
			$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Saleout Klaim [DELL COMMERCIAL]');
			$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Model : ('.$title.')');
			$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Periode : '.$awal.' - '.$akhir);
			
			//merge heading
			$objPHPExcel->getActiveSheet()->mergeCells("A1:K1");
			$objPHPExcel->getActiveSheet()->mergeCells("A2:K2");
			$objPHPExcel->getActiveSheet()->mergeCells("A3:K3");
			
			///font heading
			$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);
			$objPHPExcel->getActiveSheet()->getStyle('A2:A3')->getFont()->setSize(14);
			$objPHPExcel->getActiveSheet()->getStyle('A1:A3')->getFont()->setBold(true);
			
			//set cell alignment
			$objPHPExcel->getActiveSheet()->getStyle('A1:A3')->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(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
			$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
			$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
			$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
			$objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
			$objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
			$objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
			$objPHPExcel->getActiveSheet()->getStyle('J')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
			$objPHPExcel->getActiveSheet()->getStyle('K')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
			
			//lebar kolom
			$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
			$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
			$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(75);
			$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);
		
			//font style 
			$objPHPExcel->getActiveSheet()->getStyle('A5:K5')->getFont()->setSize(12);
			$objPHPExcel->getActiveSheet()->getStyle('A5:K5')->getFont()->setBold(true);
			$objPHPExcel->getActiveSheet()->setCellValue('A5', 'NO');
			$objPHPExcel->getActiveSheet()->setCellValue('B5', 'KODE BARANG');
			$objPHPExcel->getActiveSheet()->setCellValue('C5', 'NAMA BARANG');
			$objPHPExcel->getActiveSheet()->setCellValue('D5', 'SERIAL NUMBER');
			$objPHPExcel->getActiveSheet()->setCellValue('E5', 'NAMA SUPPLIER');
			$objPHPExcel->getActiveSheet()->setCellValue('F5', 'NOTA PEMBELIAN');
			$objPHPExcel->getActiveSheet()->setCellValue('G5', 'TGL BELI');
			$objPHPExcel->getActiveSheet()->setCellValue('H5', 'NAMA PELANGGAN');
			$objPHPExcel->getActiveSheet()->setCellValue('I5', 'ALAMAT PELANGGAN');
			$objPHPExcel->getActiveSheet()->setCellValue('J5', 'NOTA PENJUALAN');
			$objPHPExcel->getActiveSheet()->setCellValue('K5', 'TGL JUAL');
		
			$sel = array(
				'A',
				'B',
				'C',
				'D',
				'E',
				'F',
				'G',
				'H',
				'I',
				'J',
				'K',
			);
			
			$nt = [
				'NO' 				=> 'NO',
				'KODE BARANG'		=> 'KODE_BARANG',
				'NAMA BARANG'		=> 'NAMA_BARANG',
				'SERIAL NUMBER' 	=> 'NAMA_SNSTOCK',
				'NAMA SUPPLIER' 	=> 'NAMA_SUPPLIER',
				'NOTA PEMBELIAN' 	=> 'INV_PEMBELIAN',
				'TGL BELI'		 	=> 'TGL_BELI',
				'NAMA PELANGGAN' 	=> 'NAMA_PELANGGAN',
				'ALAMAT PELANGGAN' 	=> 'ALAMAT_PELANGGAN',
				'NOTA PENJUALAN' 	=> 'INV_HSN',
				'TGL JUAL' 			=> 'TANGGAL',
			];
	
			//Isi Laporan
			$nocell	=	6;
			$no		=	0;
			$nolpr	=	1;
			foreach ($key['hsn'] as $lprn) {
				foreach ($key['nama_tabel'] as $tbl) {
					if ($tbl == "NO") {
						$objPHPExcel->getActiveSheet()->setCellValue($sel[$no] . '' . $nocell . '', $nolpr);
					} else {
						$objPHPExcel->getActiveSheet()->setCellValue($sel[$no] . '' . $nocell . '', $lprn[$nt[$tbl]]);
					}
					$no++;
				}
				$no = 0;
				$nocell++;
				$nolpr++;
			}
			
			$limit = $nocell-1;
			
			$objPHPExcel->getActiveSheet()->getStyle('A5:K5')->getFill()->applyFromArray (
				array(
					'type'       => 'FILL_SOLID',
					'rotation'   => 0,
					'startcolor' => array(
						'rgb' => 'E69138'
					),
					'endcolor'   => array(
						'argb' => 'FFFFFFFF'
					)
				)
			);
			
			$objPHPExcel->getActiveSheet()->getStyle('A5:K'.$limit)->applyFromArray(
				array(
					'borders' => array(
						'allborders' => array(
							'style' => PHPExcel_Style_Border::BORDER_THIN,
							'color' => array('rgb' => 'FF000000')
						)
					)
				)
			);
			
			$objPHPExcel->getActiveSheet()->freezePane('A6');
			$index++;
		}	
			
		$objPHPExcel->setActiveSheetIndex(0);	
			
		$filename = "LAPORAN SALEOUT DELL COMMERCIAL STOKREAL ". $awal." - ". $akhir . '.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();

?>