/**
* Download Report for Organisationunit Completeness
*
* @Route("/download", name="report_organisationunit_completeness_download")
* @Method("GET")
* @Template()
*/
public function downloadAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
$organisationunitId = $request->query->get('organisationunitId');
$organisationunitLevelId = $request->query->get('organisationunitLevel');
$sameLevel = $request->query->get('sameLevel');
// View same level personal data .ie. CHMT & RHMT
if (!empty($sameLevel)) {
$this->sameLevel = True;
}
$formsId = explode(",", $request->query->get('formids'));
$forms = new ArrayCollection();
//Get the objects from the the variables
$this->organisationunit = $em->getRepository('HrisOrganisationunitBundle:Organisationunit')->find($organisationunitId);
$this->organisationunitLevel = $em->getRepository('HrisOrganisationunitBundle:OrganisationunitLevel')->findOneBy(array('id' => $organisationunitLevelId));
foreach ($formsId as $formId) {
$forms->add($em->getRepository('HrisFormBundle:Form')->find($formId));
}
$this->forms = $forms;
$this->processCompletenessFigures();
// ask the service for a Excel5
$excelService = $this->get('phpexcel')->createPHPExcelObject();
$excelService->getProperties()->setCreator("HRHIS3")->setLastModifiedBy("HRHIS3")->setTitle(str_replace(' ', '_', $this->title))->setSubject("Office 2005 XLSX Test Document")->setDescription("Test document for Office 2005 XLSX, generated using PHP classes.")->setKeywords("office 2005 openxml php")->setCategory("Test result file");
//add style to the header
$heading_format = array('font' => array('bold' => true, 'color' => array('rgb' => '000099'), 'size' => 12), 'alignment' => array('wrap' => true, 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
//add style to the Value header
$header_format = array('font' => array('bold' => true, 'color' => array('rgb' => 'FFFFFF')), 'alignment' => array('wrap' => true, 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => '3333FF')));
//add style to the text to display
$text_format1 = array('font' => array('bold' => false, 'color' => array('rgb' => '000000')), 'alignment' => array('wrap' => true, 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
//add style to the Value header
$text_format2 = array('font' => array('bold' => false, 'color' => array('rgb' => '000000')), 'alignment' => array('wrap' => true, 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'E0E0E0')));
//write the header of the report
$column = 'A';
$row = 1;
$date = "Date: " . date("jS F Y");
$excelService->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
$excelService->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
//Merge the Title Rows
$mergeColumnTitle = 'A';
if ($this->visibleFields) {
for ($i = 1; $i < count($this->visibleFields) + 2; $i++) {
$mergeColumnTitle++;
}
} else {
for ($i = 1; $i < count($this->forms) * 3 + 2; $i++) {
$mergeColumnTitle++;
}
}
$excelService->getActiveSheet()->mergeCells($column . $row . ':' . $mergeColumnTitle . $row);
$excelService->setActiveSheetIndex(0)->setCellValue($column . $row++, $this->title);
$excelService->getActiveSheet()->mergeCells($column . $row . ':' . $mergeColumnTitle . $row);
$excelService->setActiveSheetIndex(0)->setCellValue($column . $row, $date);
//Apply the heading Format and Set the Row dimension for the headers
$excelService->getActiveSheet()->getStyle('A1:' . $mergeColumnTitle . '2')->applyFromArray($heading_format);
$excelService->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
$excelService->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
//reset the colomn and row number
$column == 'A';
$row += 2;
if ($this->organisationunitChildren && !$this->sameLevel) {
if ($this->organisationunitChildren && !$this->sameLevel) {
//write the table heading of the values
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $mergeColumnTitle . ($row + 1))->applyFromArray($header_format);
$excelService->getActiveSheet()->mergeCells($column . $row . ':' . $column . ($row + 1));
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, 'SN');
$excelService->getActiveSheet()->mergeCells($column . $row . ':' . $column . ($row + 1));
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, 'Organisationunit');
}
foreach ($this->forms as $forms) {
if ($this->organisationunitChildren || $this->sameLevel) {
if ($this->visibleFields) {
$colspan = count($this->visibleFields);
} else {
$colspan = 3;
}
} else {
$colspan = 3;
}
if ($this->organisationunitChildren) {
$mergeColumn = $column;
for ($i = 1; $i < $colspan; $i++) {
$mergeColumn++;
}
$excelService->getActiveSheet()->mergeCells($column . $row . ':' . $mergeColumn . $row);
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $forms->getName());
for ($i = 1; $i < $colspan; $i++) {
$column++;
}
}
}
} elseif ($this->visibleFields) {
//Headers for records compeleteness
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, 'SN');
foreach ($this->visibleFields as $visibleField) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $visibleField->getCaption());
}
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, 'Form name');
}
if ($this->organisationunitChildren && !$this->sameLevel) {
$row++;
$column = 'C';
foreach ($this->forms as $form) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, 'Entered Records')->setCellValue($column++ . $row, 'Expected Records')->setCellValue($column++ . $row, 'Percentage');
}
}
$counter = 0;
$row++;
$column = 'A';
//Start populating the data
if ($this->organisationunitChildren && !$this->sameLevel) {
foreach ($this->organisationunitChildren as $childOrganisationunit) {
$longNamePrefix = NULL;
$counter = $counter + 1;
//format of the row
if ($counter % 2 == 1) {
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $mergeColumnTitle . $row)->applyFromArray($text_format1);
} else {
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $mergeColumnTitle . $row)->applyFromArray($text_format2);
}
foreach ($this->lowerLevels as $lowerLevelKey => $lowerLevel) {
if ($lowerLevel['level'] - $this->organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() == 0) {
$longNamePrefix = $childOrganisationunit->getLongname() . $longNamePrefix;
} elseif ($lowerLevel['level'] - $this->organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() == 1) {
$longNamePrefix = $childOrganisationunit->getParent()->getLongname() . $longNamePrefix;
} elseif ($lowerLevel['level'] - $this->organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() == 2) {
$longNamePrefix = $childOrganisationunit->getParent()->getParent()->getLongname() . $longNamePrefix;
} elseif ($lowerLevel['level'] - $this->organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() == 3) {
$longNamePrefix = $childOrganisationunit->getParent()->getParent()->getParent()->getLongname() . $longNamePrefix;
} elseif ($lowerLevel['level'] - $this->organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() == 4) {
$longNamePrefix = $childOrganisationunit->getParent()->getParent()->getParent()->getParent()->getLongname() . $longNamePrefix;
} elseif ($lowerLevel['level'] - $this->organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() == 5) {
$longNamePrefix = $childOrganisationunit->getParent()->getParent()->getParent()->getParent()->getParent()->getLongname() . $longNamePrefix;
}
// Separator
$longNamePrefix = $longNamePrefix . ' > ';
}
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $counter)->setCellValue($column++ . $row, $longNamePrefix . $childOrganisationunit->getLongname());
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $mergeColumnTitle . $row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $mergeColumnTitle . $row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $mergeColumnTitle . $row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
foreach ($this->forms as $form) {
// Entered records
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $this->completenessMatrix[$childOrganisationunit->getId()][$form->getId()]);
// Expected records and Percentage
if ($this->expectedCompleteness[$childOrganisationunit->getId()][$form->getId()]) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $this->expectedCompleteness[$childOrganisationunit->getId()][$form->getId()]);
if ($this->completenessMatrix[$childOrganisationunit->getId()][$form->getId()] > $this->expectedCompleteness[$childOrganisationunit->getId()][$form->getId()]) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, 'Above Expected');
} elseif ($this->expectedCompleteness[$childOrganisationunit->getId()][$form->getId()] <= 0) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, '');
} else {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, round($this->completenessMatrix[$childOrganisationunit->getId()][$form->getId()] / $this->expectedCompleteness[$childOrganisationunit->getId()][$form->getId()] * 100), 2);
}
} else {
//# Expected records & percentage#
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, '')->setCellValue($column++ . $row, '');
}
}
$row++;
$column = 'A';
}
// Root node completeness
$counter = $counter + 1;
//format of the row
if ($counter % 2 == 1) {
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $mergeColumnTitle . $row)->applyFromArray($text_format1);
} else {
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $mergeColumnTitle . $row)->applyFromArray($text_format2);
}
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $counter)->setCellValue($column++ . $row, $this->rootNodeOrganisationunit->getLongname());
foreach ($this->forms as $form) {
// Entered records
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $this->completenessMatrix[$this->rootNodeOrganisationunit->getId()][$form->getId()]);
# Expected records and Percentage #
if ($this->expectedCompleteness[$this->rootNodeOrganisationunit->getId()][$form->getId()]) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $this->expectedCompleteness[$this->rootNodeOrganisationunit->getId()][$form->getId()]);
if ($this->completenessMatrix[$this->rootNodeOrganisationunit->getId()][$form->getId()] > $this->expectedCompleteness[$this->rootNodeOrganisationunit->getId()][$form->getId()]) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, 'Above Expected');
} elseif ($this->expectedCompleteness[$this->rootNodeOrganisationunit->getId()][$form->getId()] <= 0) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, '');
} else {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, round($this->completenessMatrix[$this->rootNodeOrganisationunit->getId()][$form->getId()] / $this->expectedCompleteness[$this->rootNodeOrganisationunit->getId()][$form->getId()] * 100), 2);
}
} else {
// Expected records & percentage
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, '')->setCellValue($column++ . $row, '');
}
}
// Total completeness
$row++;
$column = 'A';
$counter = $counter + 1;
//format of the row
if ($counter % 2 == 1) {
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $mergeColumnTitle . $row)->applyFromArray($text_format1);
} else {
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $mergeColumnTitle . $row)->applyFromArray($text_format2);
}
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $counter)->setCellValue($column++ . $row, 'Total:' . $this->rootNodeOrganisationunit->getLongname() . ' and lower levels');
foreach ($this->forms as $form) {
// Entered records
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $this->totalCompletenessMatrix[$form->getId()]);
//# Expected records and Percentage #
if ($this->totalExpectedCompleteness[$form->getId()]) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $this->totalExpectedCompleteness[$form->getId()]);
if ($this->totalCompletenessMatrix[$form->getId()] > $this->totalExpectedCompleteness[$form->getId()]) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, 'Above Expected');
} elseif ($this->totalExpectedCompleteness[$form->getId()] <= 0) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, '');
} else {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, round($this->totalCompletenessMatrix[$form->getId()] / $this->totalExpectedCompleteness[$form->getId()] * 100), 2);
}
} else {
# Expected records & percentage#
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, '')->setCellValue($column++ . $row, '');
}
}
} else {
foreach ($this->recordInstances as $recordInstance) {
$counter = $counter + 1;
//format of the row
if ($counter % 2 == 1) {
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $mergeColumnTitle . $row)->applyFromArray($text_format1);
} else {
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $mergeColumnTitle . $row)->applyFromArray($text_format2);
}
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $counter);
foreach ($this->visibleFields as $visibleField) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $this->recordsToDisplay[$recordInstance][$visibleField->getUid()]);
}
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $this->recordsToDisplay[$recordInstance]['form']);
$row++;
$column = 'A';
}
}
$excelService->getActiveSheet()->setTitle('Completeness Report');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$excelService->setActiveSheetIndex(0);
// create the writer
$writer = $this->get('phpexcel')->createWriter($excelService, 'Excel5');
// create the response
$response = $this->get('phpexcel')->createStreamedResponse($writer);
$response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
$response->headers->set('Content-Disposition', 'attachment;filename=' . str_replace(' ', '_', $this->title) . '.xls');
$response->headers->set('Pragma', 'public');
$response->headers->set('Cache-Control', 'maxage=1');
//$response->sendHeaders();
return $response;
}