188 lines
6.8 KiB
PHP
Executable File
188 lines
6.8 KiB
PHP
Executable File
<?php
|
|
require_once ("config.inc.php");
|
|
if (isset($_GET["jahrgang"])) {
|
|
$jahrgang = $_GET["jahrgang"];
|
|
}
|
|
else {
|
|
$jahrgang = $_COOKIE["jahrgang"];
|
|
}
|
|
|
|
/**
|
|
* PHPExcel
|
|
*
|
|
* Copyright (c) 2006 - 2015 PHPExcel
|
|
*
|
|
* This library is free software; you can redistribute it and/or
|
|
* modify it under the terms of the GNU Lesser General Public
|
|
* License as published by the Free Software Foundation; either
|
|
* version 2.1 of the License, or (at your option) any later version.
|
|
*
|
|
* This library is distributed in the hope that it will be useful,
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
|
|
* Lesser General Public License for more details.
|
|
*
|
|
* You should have received a copy of the GNU Lesser General Public
|
|
* License along with this library; if not, write to the Free Software
|
|
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
|
|
*
|
|
* @category PHPExcel
|
|
* @package PHPExcel
|
|
* @copyright Copyright (c) 2006 - 2015 PHPExcel (https://www.codeplex.com/PHPExcel)
|
|
* @license https://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
|
|
* @version ##VERSION##, ##DATE##
|
|
*/
|
|
|
|
/** Error reporting */
|
|
error_reporting(E_ALL);
|
|
ini_set('display_errors', TRUE);
|
|
ini_set('display_startup_errors', TRUE);
|
|
date_default_timezone_set('Europe/London');
|
|
|
|
if (PHP_SAPI == 'cli')
|
|
die('This example should only be run from a Web Browser');
|
|
|
|
/** Include PHPExcel */
|
|
require_once './PHPExcel/Classes/PHPExcel.php';
|
|
|
|
|
|
// Create new PHPExcel object
|
|
$objPHPExcel = new PHPExcel();
|
|
|
|
// Set document properties
|
|
#$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
|
|
# ->setLastModifiedBy("Maarten Balliauw")
|
|
# ->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");
|
|
|
|
$objPHPExcel->getActiveSheet()->setCellValue('A' . 1, "Nachname");
|
|
$objPHPExcel->getActiveSheet()->setCellValue('B' . 1, "Vorname");
|
|
$objPHPExcel->getActiveSheet()->setCellValue('C' . 1, "Ab Datum");
|
|
$objPHPExcel->getActiveSheet()->setCellValue('D' . 1, "Ende");
|
|
$objPHPExcel->getActiveSheet()->setCellValue('E' . 1, "Jahresbetrag");
|
|
$objPHPExcel->getActiveSheet()->setCellValue('F' . 1, "Art");
|
|
$objPHPExcel->getActiveSheet()->freezePane('A2');
|
|
|
|
// Add some data
|
|
|
|
$query3 = "SELECT distinct a.did
|
|
FROM prog_tmp_lb_lz a, prog_tmp_professor b
|
|
WHERE a.did=b.did
|
|
AND date_format(monat,'%Y') = '$jahrgang'
|
|
AND (wert > 0 or a.art ='O')
|
|
GROUP BY a.did, lblzid
|
|
ORDER BY b.art, doz_name asc, lblzid asc";
|
|
|
|
$result3 = $db->query($query3) or die("Cannot execute query3a");
|
|
$ln_zaehler = 1;
|
|
while ($row3 = $result3->fetch_array()) {
|
|
#echo "$row3[did]<br>";
|
|
|
|
$query4 = "SELECT a.did, doz_titel, doz_vorname, doz_name, lblzid, monat, sum(wert) wert, a.art, date_format(monat, '%Y-01-01') start_aussetz_fiktiv, date_format(monat, '%Y-12-31') ende_aussetz_fiktiv, b.art fikt_prof
|
|
FROM prog_tmp_lb_lz a, prog_tmp_professor b
|
|
WHERE a.did=b.did
|
|
AND date_format(monat,'%Y') = '$jahrgang'
|
|
AND a.did = $row3[did]
|
|
AND (wert > 0 or a.art ='O')
|
|
GROUP BY a.did, lblzid, art
|
|
ORDER BY b.art, doz_name asc, lblzid asc";
|
|
|
|
|
|
$result4 = $db->query($query4) or die("Cannot execute query4");
|
|
while ($row4 = $result4->fetch_array()) {
|
|
$ln_zaehler++;
|
|
|
|
if ($row4['art'] == 'F') {
|
|
$art = "Fiktiver Wert";
|
|
}
|
|
|
|
if ($row4['art'] == 'B') {
|
|
$art = 'Erfasster Wert';
|
|
}
|
|
|
|
if ($row4['art'] == 'O') {
|
|
$art = 'max. LBZ erreicht';
|
|
}
|
|
|
|
$result_laufz = $db->query("SELECT date_format(min(monat), '%d.%m.%Y') min_monat_form, date_format(max(monat), '%d.%m.%Y') max_monat_form
|
|
FROM prog_tmp_lb_lz
|
|
WHERE lblzid='$row4[lblzid]'
|
|
AND did= '$row4[did]'
|
|
LIMIT 1");
|
|
$row_laufz = $result_laufz->fetch_array();
|
|
$startdat = "$row_laufz[min_monat_form]";
|
|
$enddat = "$row_laufz[max_monat_form]";
|
|
|
|
$objPHPExcel->getActiveSheet()->setCellValue('A' . $ln_zaehler, "$row4[doz_name]");
|
|
$objPHPExcel->getActiveSheet()->setCellValue('B' . $ln_zaehler, "$row4[doz_vorname]");
|
|
$objPHPExcel->getActiveSheet()->setCellValue('C' . $ln_zaehler, "$startdat");
|
|
$objPHPExcel->getActiveSheet()->setCellValue('D' . $ln_zaehler, "$enddat");
|
|
$objPHPExcel->getActiveSheet()->setCellValue('E' . $ln_zaehler, "$row4[wert]");
|
|
$objPHPExcel->getActiveSheet()->setCellValue('F' . $ln_zaehler, "$art");
|
|
|
|
|
|
}
|
|
}
|
|
|
|
|
|
|
|
const FORMAT_CURRENCY_EUR_SIMPLE = '#,##0.00 €_-';
|
|
$objPHPExcel->getActiveSheet()
|
|
->getStyle('E1:E65000')
|
|
->getNumberFormat()
|
|
->setFormatCode(
|
|
FORMAT_CURRENCY_EUR_SIMPLE
|
|
);
|
|
// Rename worksheet
|
|
$objPHPExcel->getActiveSheet()->setTitle('LBZ');
|
|
|
|
|
|
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
|
|
$objPHPExcel->setActiveSheetIndex(0);
|
|
|
|
// Summe drunterschreiben
|
|
$ln_zaehler2 = $ln_zaehler+1;
|
|
|
|
$objPHPExcel->getActiveSheet()
|
|
->setCellValue(
|
|
'E'.$ln_zaehler2,
|
|
'=SUM(E2:E'.$ln_zaehler.')'
|
|
);
|
|
|
|
|
|
// Auto size columns for each worksheet
|
|
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
|
|
|
|
$objPHPExcel->setActiveSheetIndex($objPHPExcel->getIndex($worksheet));
|
|
|
|
$sheet = $objPHPExcel->getActiveSheet();
|
|
$cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
|
|
$cellIterator->setIterateOnlyExistingCells(true);
|
|
/** @var PHPExcel_Cell $cell */
|
|
foreach ($cellIterator as $cell) {
|
|
$sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
|
|
}
|
|
}
|
|
$objPHPExcel->getActiveSheet()->getStyle("A1:F1")->getFont()->setBold(true);
|
|
|
|
|
|
// Redirect output to a client’s web browser (Excel2007)
|
|
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
|
header('Content-Disposition: attachment;filename="LBZ_'.$jahrgang.'.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
|
|
|
|
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
|
|
$objWriter->save('php://output');
|
|
|
|
?>
|