<?php
	require_once('./libs/Ultraviolet.php');

	require_once('./libs/Database.php');

	require_once('./libs/AdminFunctions.php');

	$database =& DatabaseConnect::getInstance();

	$database->query("SET CHARACTER SET utf8");

	$functions =& AdminFunctions::getInstance();

	require_once ('spreadsheet/vendor/autoload.php');

	for($i = 0; $i < count($_GET); $i++):

		${key($_GET)} = $_GET[key($_GET)];

		next($_GET);

	endfor;

	$database->setQuery("SELECT name FROM client WHERE id = '$client'");

	$clientname = $database->loadResult();

	$convertm = explode('-', $month);

	$monthheader = $convertm[1].'.'.$convertm[0];

	switch($convertm[1]){

		case('12'):$mesec = 'Decembar ';break;
		case('1'):$mesec = 'Januar ';break;
		case('2'):$mesec = 'Februar ';break;
		case('3'):$mesec = 'Mart ';break;
		case('4'):$mesec = 'April ';break;
		case('5'):$mesec = 'Maj ';break;
		case('6'):$mesec = 'Jun ';break;
		case('7'):$mesec = 'Jul ';break;
		case('8'):$mesec = 'Avgust ';break;
		case('9'):$mesec = 'Septembar ';break;
		case('10'):$mesec = 'Oktobar ';break;
		case('11'):$mesec = 'Novembar ';break;

		}

	$fd =  $convertm[0].'-'.$convertm[1];

	$database->setBareQuery("SELECT DISTINCT(user) FROM task WHERE project IN (SELECT id FROM project WHERE parent = '$client') AND  SUBSTRING_INDEX( DATE,  '-', 2 ) =  '$fd' ORDER BY user");

	$users = $database->loadResultArray(0);

	$endcell = count($users);
$endcell = $endcell + 66;
$endcell = chr($endcell).'1';



$reportdate = $mesec.' '.$convertm[0];

	$database->setBareQuery("SELECT DISTINCT(t1.name), t1.id, t1.project_num, t1.project_year FROM project t1, task t2 WHERE SUBSTRING_INDEX(t2.date, '-', 2) = '".$fd."' AND t1.parent = '".$client."' AND t1.id = t2.project ORDER BY t1.project_year, t1.project_num");

	$projects = $database->loadResultArray(0);

	$pid = $database->loadResultArray(1);

	$projectnum = $database->loadResultArray(2);

	$project_year = $database->loadResultArray(3);

	$projectcount = count($projects);



$helper = new \PhpOffice\PhpSpreadsheet\Helper\Sample();
if ($helper->isCli()) {
    echo 'This example should only be run from a Web Browser' . PHP_EOL;

    return;
}
// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// Set document properties
$spreadsheet->getProperties()->setCreator('Popular Communications')
        ->setLastModifiedBy('Popular Communications')
        ->setTitle('Izvestaj za mesec')
        ->setSubject('Izvestaj za mesec')
        ->setDescription('Izvestaj za mesec')
        ->setKeywords('Izvestaj za mesec')
        ->setCategory('Izvestaj za mesec');

// Add some data
$spreadsheet->setActiveSheetIndex(0)
        ->setCellValue('A1', $clientname)
        ->setCellValue($endcell, $mesec.' '.$convertm[0]);

$spreadsheet->setActiveSheetIndex(0)
        ->setCellValue('A2', 'Projekat');
for($i=0; $i<count($users); $i++):
$colnum = $i + 66;
$colnum = chr($colnum).'2';
$spreadsheet->setActiveSheetIndex(0)->setCellValue($colnum, $users[$i]);
endfor;

$spreadsheet->setActiveSheetIndex(0)->setCellValue(str_replace('1', '2', $endcell), 'Grand Total');

for($i=0; $i<count($projects); $i++):
if($projectnum[$i] < 100){

			if($projectnum[$i] < 10){ $visiblenum = '00'.$projectnum[$i];}

			else { $visiblenum = '0'.$projectnum[$i];}

			}

			else { $visiblenum = $projectnum[$i];}
$rownum = $i + 3;
$rownum = 'A'.$rownum;
$cellcont = $project_year[$i];
$spreadsheet->setActiveSheetIndex(0)->setCellValue($rownum, $project_year[$i].$visiblenum.' '.$projects[$i]);
$database->setBareQuery("SELECT DISTINCT(user) FROM task WHERE project IN (SELECT id FROM project WHERE parent = '$client') AND  SUBSTRING_INDEX( DATE,  '-', 2 ) =  '$fd' ORDER BY user");
$rowuser = $database->loadResultArray(0);
for($z = 0; $z<count($rowuser); $z++):
$database->setQuery("SELECT SUM(duration) FROM task WHERE project = '$pid[$i]' AND user = '$rowuser[$z]' AND SUBSTRING_INDEX( DATE,  '-', 2 ) =  '$fd'");
$usersum = $database->loadResult();
$row = $i + 3;
$col = $z + 66;

$spreadsheet->setActiveSheetIndex(0)->setCellValue(chr($col).$row, $usersum);
endfor;
$rowsum = '=SUM(B'.$row.':'.chr($col).$row.')';
$col = $col + 1;
$spreadsheet->setActiveSheetIndex(0)->setCellValue(chr($col).$row, $rowsum);
endfor;
$database->setBareQuery("SELECT DISTINCT(user) FROM task WHERE project IN (SELECT id FROM project WHERE parent = '$client') AND  SUBSTRING_INDEX( DATE,  '-', 2 ) =  '$fd' ORDER BY user");
$rowuser = $database->loadResultArray(0);
for($i = 0; $i<count($rowuser); $i++):
$col = chr($i + 66);
$row = $projectcount + 3;
$calculaterow = $projectcount + 2;
$colsum = '=SUM('.$col.'3:'.$col.$calculaterow.')';
$spreadsheet->setActiveSheetIndex(0)->setCellValue($col.$row, $colsum);
endfor;

$lastcol = count($rowuser) + 2;
$lastcolchr = chr($lastcol + 64);

$spreadsheet->setActiveSheetIndex(0)->setCellValue('A'.$row, 'Grand Total');


for($i = 1; $i <$lastcol; $i++):
$column = chr($i+1+64);
$lastdatarow = $row - 1;
$spreadsheet->setActiveSheetIndex(0)->setCellValue($column.$row, '=SUM('.$column.'3:'.$column.$lastdatarow.')');
endfor;



for($i = 0; $i <$lastcol; $i++):
$column = chr($i+1+64);

$spreadsheet->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);

endfor;

// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle($mesec.' '.$convertm[0]);


// 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=\"Timesheet $convertm[0] $mesec$clientname.xlsx\"");
header('Cache-Control: max-age=0');

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