public function finalize()
{
$writer = new \PHPExcel_Writer_Excel2007($this->excel);
$writer->setPreCalculateFormulas(false);
$writer->save($this->filename);
return $this->filename;
}
public function generate($format = "Excel5", $docName = "Tabelle")
{
switch ($format) {
case 'Excel2007':
include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel2007.php';
$writer = new PHPExcel_Writer_Excel2007($this);
$ext = 'xlsx';
$header = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
//supprime le pre-calcul
$writer->setPreCalculateFormulas(false);
break;
case 'Excel2003':
include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel2007.php';
$writer = new PHPExcel_Writer_Excel2007($this);
$writer->setOffice2003Compatibility(true);
$ext = 'xlsx';
//supprime le pre-calcul
$writer->setPreCalculateFormulas(false);
break;
case 'Excel5':
include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel5.php';
$writer = new PHPExcel_Writer_Excel5($this);
$ext = 'xls';
break;
case 'CSV':
include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/CSV.php';
$writer = new PHPExcel_Writer_CSV($this);
$writer->setDelimiter(",");
//l'op�rateur de s�paration est la virgule
$writer->setSheetIndex(0);
//Une seule feuille possible
$ext = 'csv';
break;
case 'PDF':
include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/PDF.php';
$writer = new PHPExcel_Writer_PDF($this);
$writer->setSheetIndex(0);
//Une seule feuille possible
$ext = 'pdf';
break;
case 'HTML':
include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/HTML.php';
$writer = new PHPExcel_Writer_HTML($this);
$writer->setSheetIndex(0);
//Une seule feuille possible
$ext = 'html';
break;
}
header('Content-type:' . $header);
header('Content-Disposition:inline;filename=' . $docName . '.' . $ext);
$writer->save('php://output');
}
public function execute()
{
$options = $this->getOptions();
$optionsExport = $options['settings']['export'];
$optionsRenderer = $this->getOptionsRenderer();
$phpExcel = new PHPExcel();
// Sheet 1
$phpExcel->setActiveSheetIndex(0);
$sheet = $phpExcel->getActiveSheet();
$sheet->setTitle($this->getTranslator()->translate($optionsRenderer['sheetName']));
if (true === $optionsRenderer['displayTitle']) {
$sheet->setCellValue('A' . $optionsRenderer['rowTitle'], $this->getTitle());
$sheet->getStyle('A' . $optionsRenderer['rowTitle'])->getFont()->setSize(15);
}
/*
* Print settings
*/
$this->setPrinting($phpExcel);
/*
* Calculate column width
*/
$this->calculateColumnWidth($sheet, $this->getColumnsToExport());
/*
* Header
*/
$xColumn = 0;
$yRow = $optionsRenderer['startRowData'];
foreach ($this->getColumnsToExport() as $col) {
/* @var $column \ZfcDatagrid\Column\AbstractColumn */
$label = $this->getTranslator()->translate($col->getLabel());
$sheet->setCellValueByColumnAndRow($xColumn, $yRow, $label);
$sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($xColumn))->setWidth($col->getWidth());
$xColumn++;
}
/*
* Data
*/
$yRow = $optionsRenderer['startRowData'] + 1;
foreach ($this->getData() as $row) {
$xColumn = 0;
foreach ($this->getColumnsToExport() as $col) {
$value = $row[$col->getUniqueId()];
if (is_array($value)) {
$value = implode(PHP_EOL, $value);
}
/* @var $column \ZfcDatagrid\Column\AbstractColumn */
$currentColumn = PHPExcel_Cell::stringFromColumnIndex($xColumn);
$sheet->getCell($currentColumn . $yRow)->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
$columnStyle = $sheet->getStyle($currentColumn . $yRow);
$columnStyle->getAlignment()->setWrapText(true);
/*
* Styles
*/
$styles = array_merge($this->getRowStyles(), $col->getStyles());
foreach ($styles as $style) {
/* @var $style \ZfcDatagrid\Column\Style\AbstractStyle */
if ($style->isApply($row) === true) {
switch (get_class($style)) {
case 'ZfcDatagrid\\Column\\Style\\Bold':
$columnStyle->getFont()->setBold(true);
break;
case 'ZfcDatagrid\\Column\\Style\\Italic':
$columnStyle->getFont()->setItalic(true);
break;
case 'ZfcDatagrid\\Column\\Style\\Color':
$columnStyle->getFont()->getColor()->setRGB($style->getRgbHexString());
break;
case 'ZfcDatagrid\\Column\\Style\\BackgroundColor':
$columnStyle->getFill()->applyFromArray(['type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => ['rgb' => $style->getRgbHexString()]]);
break;
case 'ZfcDatagrid\\Column\\Style\\Align':
switch ($style->getAlignment()) {
case \ZfcDatagrid\Column\Style\Align::$RIGHT:
$columnStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
break;
case \ZfcDatagrid\Column\Style\Align::$LEFT:
$columnStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
break;
case \ZfcDatagrid\Column\Style\Align::$CENTER:
$columnStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
break;
case \ZfcDatagrid\Column\Style\Align::$JUSTIFY:
$columnStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
break;
default:
//throw new \Exception('Not defined yet: "'.get_class($style->getAlignment()).'"');
break;
}
break;
case 'ZfcDatagrid\\Column\\Style\\Strikethrough':
$columnStyle->getFont()->setStrikethrough(true);
break;
case 'ZfcDatagrid\\Column\\Style\\Html':
// @todo strip the html?
break;
default:
throw new \Exception('Not defined yet: "' . get_class($style) . '"');
break;
}
}
}
$xColumn++;
}
$yRow++;
}
/*
* Autofilter, freezing, ...
*/
$highest = $sheet->getHighestRowAndColumn();
// Letzte Zeile merken
// Autofilter + Freeze
$sheet->setAutoFilter('A' . $optionsRenderer['startRowData'] . ':' . $highest['column'] . $highest['row']);
$freezeRow = $optionsRenderer['startRowData'] + 1;
$sheet->freezePane('A' . $freezeRow);
// repeat the data header for each page!
$sheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd($optionsRenderer['startRowData'], $optionsRenderer['startRowData']);
// highlight header line
$style = ['font' => ['bold' => true], 'borders' => ['allborders' => ['style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => ['argb' => PHPExcel_Style_Color::COLOR_BLACK]]], 'fill' => ['type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => ['argb' => PHPExcel_Style_Color::COLOR_YELLOW]]];
$range = 'A' . $optionsRenderer['startRowData'] . ':' . $highest['column'] . $optionsRenderer['startRowData'];
$sheet->getStyle($range)->applyFromArray($style);
// print borders
$range = 'A' . $freezeRow . ':' . $highest['column'] . $highest['row'];
$sheet->getStyle($range)->applyFromArray(['borders' => ['allborders' => ['style' => PHPExcel_Style_Border::BORDER_THIN]]]);
/*
* Save the file
*/
$path = $optionsExport['path'];
$saveFilename = date('Y-m-d_H-i-s') . $this->getCacheId() . '.xlsx';
$excelWriter = new \PHPExcel_Writer_Excel2007($phpExcel);
$excelWriter->setPreCalculateFormulas(false);
$excelWriter->save($path . '/' . $saveFilename);
/*
* Send the response stream
*/
$response = new ResponseStream();
$response->setStream(fopen($path . '/' . $saveFilename, 'r'));
$headers = new Headers();
$headers->addHeaders(['Content-Type' => ['application/force-download', 'application/octet-stream', 'application/download'], 'Content-Length' => filesize($path . '/' . $saveFilename), 'Content-Disposition' => 'attachment;filename=' . $this->getFilename() . '.xlsx', 'Cache-Control' => 'must-revalidate', 'Pragma' => 'no-cache', 'Expires' => 'Thu, 1 Jan 1970 00:00:00 GMT']);
$response->setHeaders($headers);
return $response;
}
protected function getWriter()
{
$writer = new \PHPExcel_Writer_Excel2007($this->objPHPExcel);
$writer->setPreCalculateFormulas(false);
return $writer;
}
/**
* Export the actual selected translations as Excel file
* uses stripped version of PHPExcel located in system/library
*
* @return void
*/
public function exportExcel()
{
// Include PHPExcel found in library folder
include_once DIR_SYSTEM . 'library' . DIRECTORY_SEPARATOR . 'Classes' . DIRECTORY_SEPARATOR . 'PHPExcel.php';
// Start computing data
$this->index();
// Create phpExcel object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Andreas Tangemann")->setLastModifiedBy("Andreas Tangemann")->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");
// Create header
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', $this->data['column_module']);
$objPHPExcel->getActiveSheet()->setCellValue('B1', $this->data['column_folder']);
$objPHPExcel->getActiveSheet()->setCellValue('C1', $this->data['column_file']);
$objPHPExcel->getActiveSheet()->setCellValue('D1', $this->data['column_variable']);
$objPHPExcel->getActiveSheet()->setCellValue('E1', $this->data['column_translation_source']);
$objPHPExcel->getActiveSheet()->setCellValue('F1', $this->data['column_translation_destination']);
$objPHPExcel->getActiveSheet()->setCellValue('G1', 'Hyperlink');
// Set column width
$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')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
// Same style for all cells
$objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('A1'), 'B1:G1');
// Set page orientation and size
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Translation');
// Get the directory
$directory = $this->_getDirectory();
// Get all selected translations
$results = $this->model_localisation_translator->getTranslations($this->data);
if ($results) {
foreach ($results as $key => $result) {
// Iterate through all translations
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, (int) $key + 2, $result['module']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, (int) $key + 2, $result['folder']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, (int) $key + 2, $result['file']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, (int) $key + 2, $result['variable']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, (int) $key + 2, $result['translation_source']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, (int) $key + 2, $result['translation_destination']);
// Create link to file and insert in Excel file
$fileref = $this->_createFileref($directory, $result);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, (int) $key + 2, str_replace('/', '\\', $fileref));
$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(6, (int) $key + 2)->getHyperlink()->setUrl($fileref);
}
}
// Insert Autofilter
$objPHPExcel->getActiveSheet()->setAutoFilter('A1:G' . count($results));
// Save Excel 2007 file
try {
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->setPreCalculateFormulas(false);
ob_start();
$objWriter->save("php://output");
$excel = ob_get_contents();
ob_end_clean();
} catch (Exception $e) {
die('Error saving Excel file: ' . $e->getMessage());
}
// Add custom header and print file
header('Content-Description: File Transfer');
header('Content-Type: application/vnd.openxmlformats-officedocument' . '.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename=translations' . date('c') . '.xlsx');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-re_validate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . strlen($excel));
echo $excel;
// so no other output is added to the Excel file
die;
}
public static function export2Excel($objPHPExcel, $versionExcel = 'Excel2007', $fileName = '')
{
switch ($versionExcel) {
case 'Excel2007':
// redirect output to client browser with Excel2007
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->setOffice2003Compatibility(true);
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
break;
case 'Excel5':
// redirect output to client browser with Excel 2005
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');
header('Cache-Control: max-age=0');
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
break;
default:
break;
}
}
private function exportExcelCustomerList($statistics)
{
$statuses = $this->getDoctrine()->getManager()->getRepository('CoreBundle:ProductStatus')->findAll();
$excel = new \PHPExcel();
$worksheet = $excel->getActiveSheet();
$worksheet->setCellValueByColumnAndRow(0, 1, 'Customer');
$column = 1;
foreach ($statuses as $status) {
$worksheet->setCellValueByColumnAndRow($column++, 1, $status->getName());
}
$row = 2;
foreach ($statistics as $item) {
$worksheet->setCellValueByColumnAndRow(0, $row, $item['customer']->getName());
$column = 1;
foreach ($statuses as $status) {
$worksheet->setCellValueByColumnAndRow($column++, $row, empty($item['changes'][$status->getId()]) ? '0' : $item['changes'][$status->getId()]);
}
$row++;
}
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename=report.xlsx');
$objWriter = new \PHPExcel_Writer_Excel2007($excel);
$objWriter->setPreCalculateFormulas(false);
$objWriter->setOffice2003Compatibility(true);
$objWriter->save('php://output');
die;
}
public function action_index()
{
$start = Arr::get($_GET, 'start') ? strtotime($_GET['start']) : strtotime('first day of this month');
$end = Arr::get($_GET, 'end') ? strtotime($_GET['end']) + 86399 : time();
$query = array('financial_time' => isset($_GET['excel']) ? array('$gt' => 0) : array('$exists' => 1), 'update_time' => array('$gt' => intval($start), '$lt' => intval($end)));
if (Arr::get($_GET, 'app-start')) {
$query['process_time']['$gt'] = strtotime($_GET['app-start']);
}
if (Arr::get($_GET, 'app-end')) {
$query['process_time']['$lt'] = strtotime($_GET['app-end']) + 86399;
}
if (Arr::get($_GET, 'fin-start')) {
$query['financial_time']['$gt'] = strtotime($_GET['fin-start']);
}
if (Arr::get($_GET, 'fin-end')) {
$query['financial_time']['$lt'] = strtotime($_GET['fin-end']) + 86399;
}
if (Group::current('allow_assign')) {
$companies = DB::select('id', 'name')->from('companies')->order_by('name', 'asc')->execute()->as_array('id', 'name');
}
if (!Group::current('allow_assign') || Arr::get($_GET, 'company')) {
if (Group::current('allow_assign')) {
$company = $_GET['company'];
if (!is_array($company)) {
$company = explode(',', $company);
}
$company = array_map('intval', $company);
} else {
$company = array(User::current('company_id'));
}
$query['user_id'] = array('$in' => DB::select('id')->from('users')->where('company_id', 'IN', $company)->execute()->as_array(NULL, 'id'));
}
$jobs = array();
if (Arr::get($_GET, 'ticket')) {
$tickets = explode(',', $_GET['ticket']);
$q = array();
foreach ($tickets as $ticket) {
$ticket = preg_replace('/[^a-z0-9]/i', '', strval($ticket));
if (!$ticket) {
continue;
}
if (preg_match('/^T1W[0-9]{12}$/', $ticket)) {
$q[] = $ticket;
} else {
$q[] = new MongoRegex('/.*' . $ticket . '.*/i');
}
}
if (count($q) > 1) {
$jobs['_id'] = array('$in' => $q);
} elseif ($q) {
$jobs['_id'] = $q[0];
}
}
if (Arr::get($_GET, 'fsa')) {
$values = is_array($_GET['fsa']) ? $_GET['fsa'] : explode(',', $_GET['fsa']);
$jobs['data.12'] = count($values) > 1 ? array('$in' => array_values($values)) : current($values);
}
if (Arr::get($_GET, 'fsam')) {
$values = is_array($_GET['fsam']) ? $_GET['fsam'] : explode(',', $_GET['fsam']);
$jobs['data.13'] = count($values) > 1 ? array('$in' => array_values($values)) : current($values);
}
if (Arr::get($_GET, 'fda')) {
$values = is_array($_GET['fda']) ? $_GET['fda'] : explode(',', $_GET['fda']);
$jobs['data.14'] = count($values) > 1 ? array('$in' => array_values($values)) : current($values);
}
if (Arr::get($_GET, 'address')) {
$jobs['data.8'] = new MongoRegex('/.*' . strval($_GET['address']) . '.*/mi');
}
if ($jobs) {
if (count($jobs) == 1 && isset($jobs['_id'])) {
$query['job_key'] = $jobs['_id'];
} else {
$query['job_key'] = array('$in' => Database_Mongo::collection('jobs')->distinct('_id', $jobs));
}
}
$sort = array('job_key' => 1);
if (!Arr::get($_GET, 'sort')) {
$_GET['sort'] = array('-submission');
}
foreach ($_GET['sort'] as $s) {
$dir = substr($s, 0, 1) == '-' ? -1 : 1;
$s = substr($s, 1);
switch ($s) {
case 'submission':
$sort['update_time'] = $dir;
break;
case 'approval':
$sort['process_time'] = $dir;
break;
case 'financial':
$sort['financial_time'] = $dir;
break;
}
}
$result = Database_Mongo::collection('submissions')->find($query)->sort($sort);
$submissions = array();
$users = array();
$jobs = array();
$keys = array('region' => 1, 'data.8' => 1, 'data.9' => 1, 'data.14' => 1);
foreach ($result as $submission) {
$jobs[$submission['job_key']] = 1;
$keys[$submission['key']] = 1;
$submissions[$submission['job_key']][] = $submission;
$users[$submission['user_id']] = 1;
}
$result = Database_Mongo::collection('jobs')->find(array('_id' => array('$in' => array_keys($jobs))), $keys);
$jobs = array();
foreach ($result as $job) {
$jobs[$job['_id']] = $job;
}
if ($users) {
User::get(array_keys($users));
}
$rates = array();
$result = DB::select()->from('rates')->execute();
foreach ($result as $row) {
$rates[$row['company_id']][$row['region_id']][$row['column_id']] = $row['rate'];
}
$columns = Columns::get_financial();
$approved = array();
$duplicates = array();
$discr = array();
$partial = array();
$full = array();
$skip = array();
if (Group::current('allow_assign') && isset($_GET['approve']) && Arr::get($_GET, 'company') && count($_GET['company']) == 1) {
$rates = Arr::get($rates, $_GET['company'][0], array());
foreach ($submissions as $job => $list) {
$region = $jobs[$job]['region'];
$data = array();
$partial_fl = false;
$full_fl = true;
$dup_fl = false;
$discr_fl = false;
$skip_fl = false;
foreach ($list as $submission) {
$data[$submission['key']][] = $submission;
}
foreach ($data as $key => $values) {
$key = substr($key, 5);
$rate = isset($rates[$region][$key]) ? $rates[$region][$key] : (isset($rates[0][$key]) ? $rates[0][$key] : 0);
$value = array_shift($values);
if (count($values)) {
$dup_fl = true;
$full_fl = false;
} elseif ($value['value'] != Arr::path($jobs, $job . '.data.' . $key)) {
$discr_fl = true;
$full_fl = false;
} elseif (!$rate) {
$skip_fl = true;
$full_fl = false;
} elseif (!$value['financial_time']) {
$approved[] = array('id' => $value['_id'], 'rate' => $rate, 'paid' => min(floatval($value['value']), Arr::get($columns, $key)));
$partial_fl = true;
}
}
if ($partial_fl) {
if ($full_fl) {
$full[$job] = 1;
} else {
$partial[$job] = 1;
}
}
if ($skip_fl) {
$skip[$job] = 1;
}
if ($dup_fl) {
$duplicates[$job] = 1;
}
if ($discr_fl) {
$discr[$job] = 1;
}
$submissions[$job] = $data;
}
$time = time();
foreach ($approved as $value) {
Database_Mongo::collection('submissions')->update(array('_id' => $value['id']), array('$set' => array('paid' => $value['paid'], 'rate' => $value['rate'], 'financial_time' => $time)));
}
$count = count($jobs);
$jobs = Database_Mongo::collection('jobs')->find(array('_id' => array('$in' => array_keys($jobs))));
foreach ($jobs as $job) {
Utils::calculate_financial($job);
}
Messages::save(sprintf('%d/%d tickets were successfully approved.', count($full), $count), 'success');
if ($partial) {
Messages::save(sprintf('%d tickets were partially approved.', count($partial)), 'warning');
}
if ($discr) {
Messages::save(sprintf('%d tickets contain discrepancies.', count($discr)), 'danger');
}
if ($duplicates) {
Messages::save(sprintf('%d tickets contain duplicates.', count($duplicates)), 'danger');
}
if ($skip) {
Messages::save(sprintf('%d tickets contain submissions with unknown rates.', count($skip)), 'danger');
}
$this->redirect($this->request->uri() . URL::query(array('approve' => NULL)));
} elseif (isset($_GET['export'])) {
$discr = isset($_GET['discrepancy']);
header('Content-type: text/csv');
header('Content-disposition: filename="Submissions.' . date('Ymd', $start) . '-' . date('Ymd', $end) . '.' . date('YmdHi', time()) . '.csv"');
$file = tmpfile();
$headers = array('Tickets ID', 'Submission Date', 'Approval Date', 'User');
if (Group::current('allow_assign')) {
$headers[] = 'Company';
}
$headers[] = 'Column';
$headers[] = 'Value';
$keys = array();
fputcsv($file, $headers);
foreach ($submissions as $job => $list) {
foreach ($list as $submission) {
if (!$discr || Arr::path($jobs, $job . '.' . $submission['key']) != $submission['value']) {
$keys[$submission['key']] = 1;
$key = substr($submission['key'], 5);
$data = array($job, date('d-m-Y H:i', $submission['update_time']), Arr::get($submission, 'process_time') ? date('d-m-Y H:i', $submission['process_time']) : '', User::get($submission['user_id'], 'login'));
if (Group::current('allow_assign')) {
$data[] = Arr::get($companies, User::get($submission['user_id'], 'company_id'), 'Unknown');
}
$data[] = Columns::get_name($key);
$data[] = Columns::output($submission['value'], Columns::get_type($key), true);
fputcsv($file, $data);
}
}
}
fseek($file, 0);
fpassthru($file);
fclose($file);
die;
} elseif (isset($_GET['excel'])) {
$doc = PHPExcel_IOFactory::load(DOCROOT . 'financial.template.xlsx');
$sheet = $doc->getSheet();
$sheet->setTitle(date('d-m-Y', $start) . ' - ' . date('d-m-Y', $end));
$s = date('M Y', $start);
$e = date('M Y', $end);
if ($s == $e) {
$month = $s;
} else {
$month = $s . ' - ' . $e;
}
$map = array('G' => '190', 'H' => '191', 'I' => '192', 'J' => '193', 'K' => '194', 'L' => '195', 'M' => '196', 'N' => '197', 'O' => '198', 'P' => '199', 'Q' => '200', 'R' => '201', 'S' => '202', 'T' => '203', 'U' => '204', 'V' => '205', 'W' => '206', 'X' => '207', 'Y' => '208', 'Z' => '43', 'AA' => '257');
$i = 10;
$total = array();
$amount = array();
$users = array();
if (count($submissions)) {
foreach (array_merge($map, array('AC' => 0, 'AD' => 0)) as $column => $key) {
$sheet->setCellValue($column . '7', '=SUM(' . $column . '11:' . $column . (count($submissions) + 10) . ')');
}
$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$sheet->getStyle('B11:AD' . (count($submissions) + 10))->applyFromArray($styleArray);
}
foreach ($submissions as $job => $list) {
$job = Database_Mongo::collection('jobs')->findOne(array('_id' => $job));
$data = Arr::get($job, 'data');
$inst_min = false;
$inst_max = false;
$app_min = false;
$app_max = false;
$result = array();
$rate = array();
foreach ($list as $submission) {
$users[$submission['user_id']] = 1;
$inst = $submission['update_time'];
if ($inst_min === false || $inst_min > $inst) {
$inst_min = $inst;
}
if ($inst_max === false || $inst_max < $inst) {
$inst_max = $inst;
}
$app = Arr::get($submission, 'process_time', false);
if ($app_min === false && $app !== false || $app_min > $app) {
$app_min = $app;
}
if ($app_max === false && $app !== false || $app_max < $app) {
$app_max = $app;
}
$key = substr($submission['key'], 5);
$result[$key] = $submission['paid'];
$rate[$key] = $submission['rate'];
}
$sum = 0;
foreach ($result as $key => $value) {
$sum += $value * $rate[$key];
$total[$key] = Arr::get($total, $key, 0) + $value * $rate[$key];
$amount[$key] = Arr::get($amount, $key, 0) + $value;
}
$i++;
$inst_min = date('d-m-Y', $inst_min);
$inst_max = date('d-m-Y', $inst_max);
$app_min = date('d-m-Y', $app_min);
$app_max = date('d-m-Y', $app_max);
$sheet->setCellValue('B' . $i, $inst_min == $inst_max ? $inst_min : $inst_min . ' - ' . $inst_max)->setCellValue('C' . $i, $app_min == $app_max ? $app_min : $app_min . ' - ' . $app_max)->setCellValue('D' . $i, $job['_id'])->setCellValue('E' . $i, Arr::get($data, 8))->setCellValue('F' . $i, Arr::get($data, 14))->setCellValue('AC' . $i, $sum);
foreach ($map as $key => $value) {
$sheet->setCellValue($key . $i, Arr::get($result, $value) ? $result[$value] : '');
}
}
foreach ($map as $key => $value) {
$sheet->setCellValue($key . '10', Arr::get($amount, $value) ? $total[$value] / $amount[$value] : '');
}
if ($users) {
$companies = DB::select('name')->from('companies')->where('id', 'IN', DB::select('company_id')->distinct(true)->from('users')->where('id', 'IN', array_keys($users)))->execute()->as_array(NULL, 'name');
} else {
$companies = array('None');
}
$sheet->setCellValue('D2', implode(', ', $companies))->setCellValue('D3', $month);
header('Content-type: application/xlsx');
header('Content-disposition: filename="Report.xlsx"');
$name = tempnam(sys_get_temp_dir(), 'excel');
$writer = new PHPExcel_Writer_Excel2007($doc);
$writer->setPreCalculateFormulas(true);
$writer->save($name);
readfile($name);
unlink($name);
die;
}
$discrepancies = array();
foreach ($submissions as $job => $list) {
$fl = false;
foreach ($list as $submission) {
if (Arr::path($jobs, $job . '.' . $submission['key']) != $submission['value']) {
$fl = true;
break;
}
}
if (!$fl) {
$discrepancies[$job] = 1;
}
}
$view = View::factory("Reports/Financial")->set('approve_all', $start > 0 && date('m', $start) == date('m', $end) && Arr::get($_GET, 'company') && count($_GET['company']) == 1 && !Arr::get($_GET, 'fin-start'))->bind('companies', $companies)->bind('submissions', $submissions)->bind('discrepancies', $discrepancies)->bind('columns', $columns)->bind('jobs', $jobs)->bind('rates', $rates);
$this->response->body($view);
}
public function execute()
{
if ($this->getFindException()) {
foreach ($this->getReasonsException() as $key => $reason) {
$msg[$key] = $reason;
}
if ($reason !== null) {
return $this->raiseError($reason, $key);
}
}
$options = $this->getOptions();
$optionsExport = $options['settings']['export'];
$optionsRenderer = $this->getOptionsRenderer();
$phpExcel = new PHPExcel();
// Sheet 1
$phpExcel->setActiveSheetIndex(0);
$sheet = $phpExcel->getActiveSheet();
$sheet->setTitle($this->getTranslator()->translate($optionsRenderer['sheetName']));
if ($optionsRenderer['displayTitle'] === true) {
$sheet->setCellValue('A' . $optionsRenderer['rowTitle'], $this->getTitle());
$sheet->getStyle('A' . $optionsRenderer['rowTitle'])->getFont()->setSize(15);
}
$this->calculateColumnWidth($this->getColumnsToExport());
$xColumn = 0;
$yRow = $optionsRenderer['startRowData'];
foreach ($this->getColumnsToExport() as $column) {
/* @var $column \Zf2datatable\Column\AbstractColumn */
//$label = $this->getTranslator()->translate($column->getLabel());
$label = $column->getLabel();
$sheet->setCellValueByColumnAndRow($xColumn, $yRow, $label);
// $sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($xColumn))->setCollapsed(true);
$sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($xColumn))->setWidth($column->getWidth());
$xColumn++;
}
/*
* Data
*/
$yRow = $optionsRenderer['startRowData'] + 1;
foreach ($this->getData() as $row) {
$xColumn = 0;
foreach ($this->getColumnsToExport() as $column) {
/* @var $column \Zf2datatable\Column\AbstractColumn */
$currentColumn = PHPExcel_Cell::stringFromColumnIndex($xColumn);
$sheet->getCell($currentColumn . $yRow)->setValueExplicit($row[$column->getUniqueId()], PHPExcel_Cell_DataType::TYPE_STRING);
$columnStyle = $sheet->getStyle($currentColumn . $yRow);
$columnStyle->getAlignment()->setWrapText(true);
/*
* Styles
*/
$styles = array_merge($this->getRowStyles(), $column->getStyles());
foreach ($styles as $style) {
/* @var $style \Zf2datatable\Column\Style\AbstractStyle */
if ($style->isApply($row) === true) {
switch (get_class($style)) {
case 'Zf2datatable\\Column\\Style\\Bold':
$columnStyle->getFont()->setBold(true);
break;
case 'Zf2datatable\\Column\\Style\\Italic':
$columnStyle->getFont()->setItalic(true);
break;
case 'Zf2datatable\\Column\\Style\\Color':
$columnStyle->getFont()->getColor()->setRGB($style->getRgbHexString());
break;
case 'Zf2datatable\\Column\\Style\\BackgroundColor':
$columnStyle->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => $style->getRgbHexString())));
break;
default:
throw new \Exception('Not defined yet: "' . get_class($style) . '"');
break;
}
}
}
$xColumn++;
}
$yRow++;
}
/*
* Autofilter, freezing, ...
*/
// Letzte Zeile merken
$endRow = $yRow - 1;
$endColumn = count($this->getColumnsToExport()) - 1;
// Autofilter + Freeze
$sheet->setAutoFilter('A' . $optionsRenderer['startRowData'] . ':' . PHPExcel_Cell::stringFromColumnIndex($endColumn) . $endRow);
$freezeRow = $optionsRenderer['startRowData'] + 1;
$sheet->freezePane('A' . $freezeRow);
/*
* Print settings
*/
$this->setPrinting($phpExcel);
/*
* Save the file
*/
$path = $optionsExport['path'];
$saveFilename = $this->getCacheId() . '.xlsx';
$excelWriter = new \PHPExcel_Writer_Excel2007($phpExcel);
$excelWriter->setPreCalculateFormulas(false);
$excelWriter->save($path . '/' . $saveFilename);
/*
* Send the response stream
*/
$response = new ResponseStream();
$response->setStream(fopen($path . '/' . $saveFilename, 'r'));
$headers = new Headers();
$headers->addHeaders(array('Content-Type' => array('application/force-download', 'application/octet-stream', 'application/download'), 'Content-Length' => filesize($path . '/' . $saveFilename), 'Content-Disposition' => 'attachment;filename=' . $this->getFilename() . '.xlsx', 'Cache-Control' => 'must-revalidate', 'Pragma' => 'no-cache', 'Expires' => 'Thu, 1 Jan 1970 00:00:00 GMT'));
$response->setHeaders($headers);
return $response;
}
