<?php
// Load library phpspreadsheet
require('./vendor/autoload.php');
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
// End load library phpspreadsheet
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();
// Set document properties
$spreadsheet->getProperties()->setCreator('Aditya Yudhit Irfianto - UNU Purwokerto')
			->setLastModifiedBy('Aditya Yudhit - UNU Purwokerto')
			->setTitle('Office 2007 XLSX Test Document')
			->setSubject('Office 2007 XLSX Test Document')
			->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
			->setKeywords('office 2007 openxml php')
			->setCategory('Test result file');
// set size column
$spreadsheet->getActiveSheet(0)->getColumnDimension('A')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('B')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('C')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('D')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('E')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('F')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('H')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('I')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('J')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('K')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('L')
			->setAutoSize(true);
// Add some data
$spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1','Rekapitulasi Penjualan Rinci')
            ->setCellValue('A2','Periode Tanggal '.@$awal.' Sampai '.@$akhir);
$spreadsheet->setActiveSheetIndex(0)->mergeCells('A1:L1');
$spreadsheet->setActiveSheetIndex(0)->mergeCells('A2:L2');
//pemberian nama kolom
$spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A4','Hari Ini')
            ->setCellValue('E4','Rata-Rata s/d Hari Ini')
            ->setCellValue('I4','Hari Ini')
            ->setCellValue('K4','Rata-Rata s/d Hari Ini');
$spreadsheet->setActiveSheetIndex(0)->mergeCells('A4:D4');
$spreadsheet->setActiveSheetIndex(0)->mergeCells('E4:H4');
$spreadsheet->setActiveSheetIndex(0)->mergeCells('I4:J4');
$spreadsheet->setActiveSheetIndex(0)->mergeCells('K4:L4');
$spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A5','Tanggal')
            ->setCellValue('B5','SPD')
            ->setCellValue('C5','STD')
            ->setCellValue('D5','APC')
			->setCellValue('E5','SPD')
			->setCellValue('F5','STD')
			->setCellValue('G5','APC')
            ->setCellValue('H5','HPP')
            ->setCellValue('I5','Margin (Rp)')
            ->setCellValue('J5','Margin (%)')
            ->setCellValue('K5','Margin (Rp)')
            ->setCellValue('L5','Margin (%)');
$spreadsheet->getActiveSheet(0)->getStyle('A4:L5')->getAlignment()->setHorizontal('center');
//tampilkan data di excel
$no=1;
$i=6;
while (strtotime($awal)<=strtotime($akhir)) {
    $tgl=date('d-m-Y',strtotime($awal));
    if (@array_sum($arrjualrinci['penjualan'][$tgl])) {
    	$spd=array_sum($arrjualrinci['penjualan'][$tgl]);
    	$std=@array_sum($arrjualrinci['faktur'][$tgl]);
    	$apc='=B'.$i.'/C'.$i;
    	$margin='=I'.$i.'/B'.$i.'*100%';
    } else {
    	$spd=0;
    	$std=0;
    	$apc=0;
    	$margin=0;
    }
	$spreadsheet->setActiveSheetIndex(0)
				->setCellValue('A'.$i, $tgl)
				->setCellValue('B'.$i, $spd)
				->setCellValue('C'.$i, $std)
				->setCellValue('D'.$i, $apc)
                ->setCellValue('E'.$i, '=AVERAGE(B6:B'.$i.')')
				->setCellValue('F'.$i, '=AVERAGE(C6:C'.$i.')')
				->setCellValue('G'.$i, '=E'.$i.'/F'.$i)
                ->setCellValue('H'.$i, @array_sum($arrjualrinci['hpp'][$tgl]))
                ->setCellValue('I'.$i, '=B'.$i.'-H'.$i)
                ->setCellValue('J'.$i, $margin)
                ->setCellValue('K'.$i, '=AVERAGE(I6:I'.$i.')')
                ->setCellValue('L'.$i, '=AVERAGE(J6:J'.$i.')');
    $spreadsheet->setActiveSheetIndex(0)
				->getStyle('J'.$i)->getNumberFormat()->setFormatCode('0.00%');
	$spreadsheet->setActiveSheetIndex(0)
				->getStyle('L'.$i)->getNumberFormat()->setFormatCode('0.00%');
    $awal=mktime(0,0,0,date('m',strtotime($awal)),date('d',strtotime($awal))+1,date('Y',strtotime($awal)));
    $awal=date('Y-m-d',$awal);
	$i++;
}
$x=$i-1;
$spreadsheet->setActiveSheetIndex(0)
				->setCellValue('A'.$i, 'Total')
				->setCellValue('B'.$i, '=SUM(B6:B'.$x.')')
				->setCellValue('C'.$i, '=SUM(C6:C'.$x.')')
				->setCellValue('D'.$i, '=SUM(D6:D'.$x.')')
                ->setCellValue('E'.$i, '=SUM(E6:E'.$x.')')
				->setCellValue('F'.$i, '=SUM(F6:F'.$x.')')
				->setCellValue('G'.$i, '=SUM(G6:G'.$x.')')
                ->setCellValue('H'.$i, '=SUM(H6:H'.$x.')')
                ->setCellValue('I'.$i, '=SUM(I6:I'.$x.')')
                ->setCellValue('J'.$i, '=AVERAGE(J6:J'.$x.')')
                ->setCellValue('K'.$i, '=SUM(K6:K'.$x.')')
                ->setCellValue('L'.$i, '=AVERAGE(L6:L'.$x.')');
//untuk border
$border=array();
$border['borders']=array();
$border['borders']['outline']['borderStyle']=\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN;
$border['borders']['allBorders']['borderStyle']=\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN;
$spreadsheet->setActiveSheetIndex(0)->getStyle('A4:L'.$i)->applyFromArray($border);
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('Sheet 1');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Laporan Penjualan Detail - '.@$awal.'_'.$akhir.'.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;
?>