490 lines
18 KiB
PHP
Executable File
490 lines
18 KiB
PHP
Executable File
<?php
|
|
require_once("config.inc.php");
|
|
require_once("func_rollenrechte.php");
|
|
$jahrgang = $_COOKIE["jahrgang"];
|
|
$user_admin = $_COOKIE["user_admin"];
|
|
$prof1 = $_GET['prof'];
|
|
|
|
|
|
if (!rore($user_admin, 'j_detail', 'RE')) {
|
|
echo "Keine Rechte";
|
|
exit;
|
|
}
|
|
|
|
if ($user_admin == "") {
|
|
require("index.php");
|
|
exit;
|
|
} //Wenn man nicht angemeldet ist, darf man nicht auf die Seite
|
|
|
|
if ($prof1 == '%') {
|
|
#echo "Prozent";
|
|
$query = "SELECT did, doz_name, doz_vorname
|
|
FROM prog_professor
|
|
ORDER BY doz_name ASC, doz_vorname ASC";
|
|
|
|
} else {
|
|
$query = "SELECT did, doz_name, doz_vorname
|
|
FROM prog_professor
|
|
where did = $prof1
|
|
ORDER BY doz_name ASC, doz_vorname ASC";
|
|
}
|
|
|
|
$result = $db->query($query) or die("Cannot execute query");
|
|
$zaehler = 0;
|
|
while ($row = $result->fetch_array()) {
|
|
$prof = $row['did'];
|
|
$nachname = $row['doz_name'];
|
|
$vorname = $row['doz_vorname'];
|
|
|
|
# Wenn ein Prof keine Zulagen hat, dann soll keine leere Seite erzeugt werden
|
|
$result_anz_bz = $db->query("SELECT count(*) Anz
|
|
FROM prog_tmp_lb_bz
|
|
WHERE did= '$prof'
|
|
AND date_format(monat,'%Y') = '$jahrgang'
|
|
AND wert > 0
|
|
");
|
|
$row_anz_bz = $result_anz_bz->fetch_array();
|
|
|
|
$result_anz_lz = $db->query("SELECT count(*) Anz
|
|
FROM prog_tmp_lb_lz
|
|
WHERE did= '$prof'
|
|
AND date_format(monat,'%Y') = '$jahrgang'
|
|
AND wert > 0
|
|
");
|
|
$row_anz_lz = $result_anz_lz->fetch_array();
|
|
|
|
$result_anz_fz = $db->query("SELECT count(*) Anz
|
|
FROM prog_tmp_lb_fz
|
|
WHERE did= '$prof'
|
|
AND date_format(monat,'%Y') = '$jahrgang'
|
|
AND wert > 0
|
|
");
|
|
$row_anz_fz = $result_anz_fz->fetch_array();
|
|
$summe_anz = $row_anz_bz['Anz'] + $row_anz_lz['Anz'] + $row_anz_fz['Anz'];
|
|
if ($summe_anz != 0) {
|
|
// ##################################################################
|
|
// #################Header#########################################
|
|
|
|
|
|
if ($zaehler == 0) {
|
|
$inhalt_fin = '<page backtop="7mm" backbottom="10mm" style="font-size: 11pt">';
|
|
$zaehler = 1;
|
|
} else {
|
|
$inhalt_fin .= "</page>
|
|
<page pageset='old' backtop='7mm' backbottom=10mm';>";
|
|
}
|
|
|
|
$inhalt_fin .= "<style type='text/css'>
|
|
td { border:0.3px solid black; }
|
|
.td_foot { border: none; }
|
|
body { font-size: 12px; }
|
|
table { font-size: 12px; }
|
|
td{padding: 3px;}
|
|
p { font-size: 12px; }
|
|
table.page_footer {width: 100%; border: none; padding: 20mm}
|
|
.fcol{color: #0C25BF;}
|
|
</style>";
|
|
$inhalt_fin .= "<page_footer>
|
|
<table width='110%' cellspacing='0' >
|
|
<tr>
|
|
<td class='td_foot' width='680' style='text-align: center;'>Seite [[page_cu]]/[[page_nb]]</td>
|
|
</tr>
|
|
</table>
|
|
</page_footer>";
|
|
|
|
// BZ
|
|
$inhalt_fin .= "<p align=center><h1>Zulagenübersicht $jahrgang</h1>";
|
|
$inhalt_fin .= "<h3>$nachname, $vorname</h3></p>";
|
|
$inhalt_fin .= "<br><br>";
|
|
$inhalt_fin .= "<b>Berufungs- und Bleibeleistungsbezüge</b>";
|
|
$inhalt_fin .= "<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td width='155'><b>Ab Datum</b></td>
|
|
<td width='155'><b>Ende</b></td>
|
|
<td width='155' align='right'><b>Jahresbetrag</b></td>
|
|
<td width='155'><b>Art</b></td>
|
|
</tr>";
|
|
|
|
$query8 = "SELECT lbbzid, sum(wert) wert, a.art
|
|
FROM prog_tmp_lb_bz a
|
|
WHERE date_format(monat,'%Y') = '$jahrgang'
|
|
AND a.did = $prof
|
|
AND wert > 0
|
|
GROUP BY a.did, lbbzid, art
|
|
ORDER BY lbbzid asc";
|
|
|
|
$result8 = $db->query($query8) or die("Cannot execute query8");
|
|
$summe_bz = 0;
|
|
while ($row8 = $result8->fetch_array()) {
|
|
|
|
$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_bz
|
|
WHERE lbbzid='$row8[lbbzid]'
|
|
AND did= '$prof'
|
|
LIMIT 1");
|
|
$row_laufz = $result_laufz->fetch_array();
|
|
$startdat = "$row_laufz[min_monat_form]";
|
|
$enddat = "$row_laufz[max_monat_form]";
|
|
|
|
$wert = number_format($row8['wert'], 2, ',', '.');
|
|
if ($row8['art'] == 'B') {
|
|
$art = 'Erfasster Wert';
|
|
}
|
|
if ($row8['art'] == 'F') {
|
|
$art = 'Fiktiver Wert';
|
|
}
|
|
if ($row8['art'] == 'O') {
|
|
$art = 'max. LBZ erreicht';
|
|
}
|
|
$inhalt_fin .= "<tr>
|
|
<td width='155'>$startdat</td>
|
|
<td width='155'>$enddat</td>
|
|
<td width='155' align='right'>$wert €</td>
|
|
<td width='155'>$art</td>
|
|
</tr>
|
|
";
|
|
$summe_bz = $summe_bz+$row8['wert'];
|
|
}
|
|
if($summe_bz > 0){
|
|
$summe_bz_form = number_format($summe_bz, 2, ',', '.');
|
|
$inhalt_fin .= "<tr>
|
|
<td width='155'><b>Summe:</b></td>
|
|
<td width='155'></td>
|
|
<td width='155' align='right'><b>$summe_bz_form €</b></td>
|
|
<td width='155'></td>
|
|
</tr>";
|
|
}
|
|
$inhalt_fin .= "</table>";
|
|
|
|
// Deltas: Erfasste Werte
|
|
$inhalt_fin .= "<br><br>";
|
|
$inhalt_fin .= "<b>besondere Leistungsbezüge mtl. (LBZ) </b>";
|
|
$inhalt_fin .= "<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td width='155'><b>Ab Datum</b></td>
|
|
<td width='155'><b>Ende</b></td>
|
|
<td width='155' align='right'><b>Jahresbetrag</b></td>
|
|
<td width='155'><b>Art</b></td>
|
|
</tr>";
|
|
$query4 = "SELECT lblzid, sum(wert) wert, a.art
|
|
FROM prog_tmp_lb_lz a
|
|
WHERE date_format(monat,'%Y') = '$jahrgang'
|
|
AND a.did = $prof
|
|
AND (wert > 0 or a.art ='O')
|
|
AND art='B'
|
|
GROUP BY a.did, lblzid, art
|
|
ORDER BY lblzid asc";
|
|
|
|
$result4 = $db->query($query4) or die("Cannot execute query4");
|
|
$summe_lbz = 0;
|
|
while ($row4 = $result4->fetch_array()) {
|
|
|
|
$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= '$prof'
|
|
LIMIT 1");
|
|
$row_laufz = $result_laufz->fetch_array();
|
|
$startdat = "$row_laufz[min_monat_form]";
|
|
$enddat = "$row_laufz[max_monat_form]";
|
|
|
|
$wert_lz = number_format($row4['wert'], 2, ',', '.');
|
|
if ($row4['art'] == 'B') {
|
|
$art = 'Erfasster Wert';
|
|
}
|
|
if ($row4['art'] == 'F') {
|
|
$art = 'Fiktiver Wert';
|
|
}
|
|
if ($row4['art'] == 'O') {
|
|
$art = 'max. LBZ erreicht';
|
|
}
|
|
$inhalt_fin .= "<tr>
|
|
<td width='155'>$startdat</td>
|
|
<td width='155'>$enddat</td>
|
|
<td width='155' align='right'>$wert_lz € </td>
|
|
<td width='155'>$art</td>
|
|
</tr>
|
|
";
|
|
$summe_lbz = $summe_lbz+$row4['wert'];
|
|
}
|
|
if($summe_lbz > 0){
|
|
$summe_lbz_form = number_format($summe_lbz, 2, ',', '.');
|
|
$inhalt_fin .= "<tr>
|
|
<td width='155'><b>Summe:</b></td>
|
|
<td width='155'></td>
|
|
<td width='155' align='right'><b>$summe_lbz_form €</b></td>
|
|
<td width='155'></td>
|
|
</tr>";
|
|
}
|
|
$inhalt_fin .= "</table>";
|
|
|
|
|
|
|
|
// Deltas: Fiktive Werte mit Obergrenze
|
|
$inhalt_fin .= "<br><br>";
|
|
$inhalt_fin .= "<b>besondere Leistungsbezüge mtl. (LBZ) (fiktive Werte)</b>";
|
|
$inhalt_fin .= "<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td width='155'><b>Ab Datum</b></td>
|
|
<td width='155'><b>Ende</b></td>
|
|
<td width='155' align='right'><b>Jahresbetrag</b></td>
|
|
<td width='155'><b>Art</b></td>
|
|
</tr>";
|
|
$query4 = "SELECT lblzid, sum(wert) wert, a.art
|
|
FROM prog_tmp_lb_lz a
|
|
WHERE date_format(monat,'%Y') = '$jahrgang'
|
|
AND a.did = $prof
|
|
AND (wert > 0 or a.art ='O')
|
|
AND art!='B'
|
|
GROUP BY a.did, lblzid, art
|
|
ORDER BY lblzid asc";
|
|
|
|
$result4 = $db->query($query4) or die("Cannot execute query4");
|
|
$summe_lbzf = 0;
|
|
while ($row4 = $result4->fetch_array()) {
|
|
|
|
$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= '$prof'
|
|
LIMIT 1");
|
|
$row_laufz = $result_laufz->fetch_array();
|
|
$startdat = "$row_laufz[min_monat_form]";
|
|
$enddat = "$row_laufz[max_monat_form]";
|
|
|
|
$wert_lz = number_format($row4['wert'], 2, ',', '.');
|
|
if ($row4['art'] == 'B') {
|
|
$art = 'Erfasster Wert';
|
|
}
|
|
if ($row4['art'] == 'F') {
|
|
$art = 'Fiktiver Wert';
|
|
}
|
|
if ($row4['art'] == 'O') {
|
|
$art = 'max. LBZ erreicht';
|
|
}
|
|
$inhalt_fin .= "<tr>
|
|
<td width='155'>$startdat</td>
|
|
<td width='155'>$enddat</td>
|
|
<td width='155' align='right'>$wert_lz € </td>
|
|
<td width='155'>$art</td>
|
|
</tr>
|
|
";
|
|
$summe_lbzf = $summe_lbzf+$row4['wert'];
|
|
}
|
|
if($summe_lbzf > 0){
|
|
$summe_lbzf_form = number_format($summe_lbzf, 2, ',', '.');
|
|
$inhalt_fin .= "<tr>
|
|
<td width='155'><b>Summe:</b></td>
|
|
<td width='155'></td>
|
|
<td width='155' align='right'><b>$summe_lbzf_form €</b></td>
|
|
<td width='155'></td>
|
|
</tr>";
|
|
}
|
|
$inhalt_fin .= "</table>";
|
|
|
|
|
|
|
|
|
|
|
|
|
|
// Auflistung des maximalen Leistungsbezugswert
|
|
$inhalt_fin .= "<br><br>";
|
|
$inhalt_fin .= "<b>Obergrenze der Leistungsbezüge</b>";
|
|
$inhalt_fin .= "<table border=0.3 cellspacing=0>";
|
|
$summe_maxlb = number_format($summe_lbz + $summe_bz, 2, ',', '.');
|
|
if($jahrgang >= '2018' ){
|
|
# Ab 2018 sollen die maximalen Leistungsbezüge auf ein Fixbetrag geändert werden. Bspw. 1000€/Monat
|
|
$query_maxl = $db->query("SELECT wert1*12 wert1
|
|
FROM `prog_parameter`
|
|
WHERE pid =7
|
|
");
|
|
$row_maxl = $query_maxl->fetch_array();
|
|
$maxzul_ges = number_format($row_maxl['wert1'], 2, ',', '.');
|
|
$summebismax = number_format($row_maxl['wert1']-($summe_lbz + $summe_bz), 2, ',', '.');
|
|
$inhalt_fin .= "<tr>
|
|
<td width='310'>Max. Leistungsbezüge</td>
|
|
<td width='336' align='right'>$maxzul_ges €</td>
|
|
</tr>
|
|
<tr>
|
|
<td width='310'>Summe der Leistungsbezüge:</td>
|
|
<td width='336' align='right'>$summe_maxlb €</td>
|
|
</tr>
|
|
<tr>
|
|
<td width='310'>Mögliche Zulagen $jahrgang:</td>
|
|
<td width='336' align='right'>$summebismax €</td>
|
|
</tr>
|
|
";
|
|
|
|
|
|
}else{
|
|
$result_maxzul = $db->query("SELECT round(sum(w2),2) w2
|
|
,round(sum(c3),2) c3
|
|
,round(sum(max_zulage),2) max_zulage
|
|
FROM prog_tmp_lb_obergrenze
|
|
WHERE date_format(ab_datum,'%Y') = '$jahrgang'
|
|
LIMIT 1");
|
|
$row_maxzul = $result_maxzul->fetch_array();
|
|
$maxzul_w2 = number_format($row_maxzul['w2'], 2, ',', '.');
|
|
$maxzul_c3 = number_format($row_maxzul['c3'], 2, ',', '.');
|
|
$maxzul_ges = number_format($row_maxzul['max_zulage'], 2, ',', '.');
|
|
$summebismax = number_format($row_maxzul['max_zulage']-($summe_lbz + $summe_bz), 2, ',', '.');
|
|
$inhalt_fin .= "<tr>
|
|
<td width='310'>Grundgehalt W2</td>
|
|
<td width='336' align='right'>$maxzul_w2 €</td>
|
|
</tr>
|
|
<tr>
|
|
<td width='310'>Grundgehalt C3 - Stufe 15</td>
|
|
<td width='336' align='right'>$maxzul_c3 €</td>
|
|
</tr>
|
|
<tr>
|
|
<td width='310'>Max. Leistungsbezüge</td>
|
|
<td width='336' align='right'>$maxzul_ges €</td>
|
|
</tr>
|
|
<tr>
|
|
<td width='310'>Summe der Leistungsbezüge:</td>
|
|
<td width='336' align='right'>$summe_maxlb €</td>
|
|
</tr>
|
|
<tr>
|
|
<td width='310'>Mögliche Zulagen $jahrgang:</td>
|
|
<td width='336' align='right'>$summebismax €</td>
|
|
</tr>
|
|
";
|
|
}
|
|
|
|
$inhalt_fin .= "</table>";
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
// Funktionszulagen
|
|
$inhalt_fin .= "<br><br>";
|
|
$inhalt_fin .= "<b>Leistungsbezüge FZ</b>";
|
|
$inhalt_fin .= "<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td width='310'><b>Ab Datum (Funktion)</b></td>
|
|
<td width='77'><b>Ende</b></td>
|
|
<td width='78' align='right'><b>Jahresbetrag</b></td>
|
|
<td width='155'><b>Art</b></td>
|
|
</tr>";
|
|
$query6 = "SELECT lbfzid, b.kfid, a.did, sum(a.wert) wert, art, b.bezeichnung, monat
|
|
FROM prog_tmp_lb_fz a, prog_kat_funktionen b
|
|
WHERE a.kfid=b.kfid
|
|
AND date_format(monat,'%Y') = '$jahrgang'
|
|
AND a.did = $prof
|
|
AND wert > 0
|
|
GROUP BY a.lbfzid, b.kfid, a.did
|
|
ORDER BY bezeichnung asc, monat asc";
|
|
|
|
$result6 = $db->query($query6) or die("Cannot execute query4");
|
|
$summe_fz = 0;
|
|
while ($row6 = $result6->fetch_array()) {
|
|
|
|
$result_name = $db->query("SELECT doz_titel, doz_vorname, doz_name, date_format(startdat, '%d.%m.%Y') startdat, date_format(enddat, '%d.%m.%Y') enddat
|
|
FROM prog_tmp_professor a, prog_lb_fz b
|
|
WHERE a.did=b.did
|
|
AND a.did= '$prof'
|
|
AND b.lbfzid=$row6[lbfzid]
|
|
ORDER BY a.art, startdat DESC
|
|
LIMIT 1");
|
|
$row_name = $result_name->fetch_array();
|
|
|
|
$startdat = "$row_name[startdat]";
|
|
$enddat = "$row_name[enddat]";
|
|
|
|
$wert_fz = number_format($row6['wert'], 2, ',', '.');
|
|
if ($row6['art'] == 'B') {
|
|
$art = 'Erfasster Wert';
|
|
}
|
|
if ($row6['art'] == 'F') {
|
|
$art = 'Fiktiver Wert';
|
|
}
|
|
if ($row6['art'] == 'O') {
|
|
$art = 'max. LBZ erreicht';
|
|
}
|
|
$inhalt_fin .= "<tr>
|
|
<td width='310'>$startdat ($row6[bezeichnung])</td>
|
|
<td width='77'>$enddat</td>
|
|
<td width='78' align='right'>$wert_fz €</td>
|
|
<td width='155'>$art</td>
|
|
</tr>
|
|
";
|
|
$summe_fz = $summe_fz+$row6['wert'];
|
|
}
|
|
if($summe_fz > 0){
|
|
$summe_fz_form = number_format($summe_fz, 2, ',', '.');
|
|
$inhalt_fin .= "<tr>
|
|
<td width='310'><b>Summe:</b></td>
|
|
<td width='77'></td>
|
|
<td width='78' align='right'><b>$summe_fz_form €</b></td>
|
|
<td width='155'></td>
|
|
</tr>";
|
|
}
|
|
$inhalt_fin .= "</table>";
|
|
}
|
|
}
|
|
|
|
|
|
|
|
$inhalt_fin .= "</page>";
|
|
|
|
#echo $inhalt_fin;
|
|
#exit;
|
|
|
|
// ##############################################################################################################
|
|
# echo "<pre>";
|
|
# echo $inhalt_fin;
|
|
# echo "</pre>";
|
|
# exit;
|
|
|
|
require_once('html2pdf_v4.03/html2pdf.class.php');
|
|
|
|
// $inhalt_fin = '<page backtop="0mm" backbottom="10mm" style="font-size: 11pt" style="font-family: freeserif">'.nl2br($inhalt_fin).'</page>';
|
|
|
|
try {
|
|
|
|
// /*
|
|
// seitenränder (in mm)
|
|
|
|
$oben = 15; //mT
|
|
$unten = 15; //mB
|
|
$links = 15; //mL
|
|
$rechts = 15; //mR
|
|
|
|
// $html2pdf = new HTML2PDF('P', 'A4', 'de');
|
|
|
|
$html2pdf = new HTML2PDF('P', 'A4', 'de', true, 'UTF-8', array(
|
|
$links,
|
|
$oben,
|
|
$rechts,
|
|
$unten
|
|
));
|
|
$html2pdf->pdf->SetDisplayMode('fullpage');
|
|
|
|
// $html2pdf->setDefaultFont('arialunicid0'); //add this line
|
|
// $html2pdf->setModeDebug();
|
|
// $html2pdf->setDefaultFont('Arial');
|
|
|
|
|
|
|
|
// Outputparameter:
|
|
// I: Das PDF wird im Browser geöffnet
|
|
// D: Es wird ein Download Dialog angezeigt
|
|
// F: Die Datei wird auf dem Server abgespeichert
|
|
// S: Das PDF Dokument wird als String zurück gegeben
|
|
|
|
$html2pdf->writeHTML($inhalt_fin, isset($_GET['vuehtml']));
|
|
$html2pdf->Output("Jahresuebersicht_$jahrgang.pdf", 'I');
|
|
|
|
// */
|
|
|
|
}
|
|
|
|
catch (HTML2PDF_exception $e) {
|
|
echo $e;
|
|
exit;
|
|
}
|
|
|
|
?>
|