944 lines
37 KiB
PHP
Executable File
944 lines
37 KiB
PHP
Executable File
<?php
|
|
require_once ("config.inc.php");
|
|
require_once ("func_rollenrechte.php");
|
|
|
|
$user_admin = $_COOKIE["user_admin"];
|
|
$jahrgang = $_GET['jahr'];
|
|
|
|
if (!rore($user_admin, 'j_uber', 'RE')) {
|
|
echo "Keine Rechte";
|
|
exit;
|
|
}
|
|
|
|
if ($user_admin == "") {
|
|
require ("index.php");
|
|
exit;
|
|
} //Wenn man nicht angemeldet ist, darf man nicht auf die Seite
|
|
|
|
$result_data1 = $db->query("SELECT date_format(monat,'%Y') jahr
|
|
,round(sum(vergaberahmen_vj),2) vergaberahmen_vj
|
|
,round(sum(jahresrahmen),2) jahresrahmen
|
|
,round(sum(besausgaben),2) besausgaben
|
|
,round(sum(vergaberahmen),2) vergaberahmen
|
|
,round(sum(verg_uebertragvj),2) verg_uebertragvj
|
|
,round(sum(leistungsbez),2) leistungsbez
|
|
,round(sum(vergaberahmen_nj),2) vergaberahmen_nj
|
|
FROM `prog_tmp_synopse`
|
|
WHERE date_format(monat,'%Y') = '$jahrgang'
|
|
GROUP BY date_format(monat,'%Y')
|
|
ORDER BY jahr");
|
|
$row_data1 = $result_data1->fetch_array();
|
|
$ju_vergaberahmen_vj = number_format($row_data1['vergaberahmen_vj'], 2, ',', '.');
|
|
$ju_jahresrahmen = number_format($row_data1['jahresrahmen'], 2, ',', '.');
|
|
$ju_vergaberahmen = number_format($row_data1['vergaberahmen'], 2, ',', '.');
|
|
$ju_verg_uebertragvj = number_format($row_data1['verg_uebertragvj'], 2, ',', '.');
|
|
$ju_vergaberahmen_nj = number_format($row_data1['vergaberahmen_nj'], 2, ',', '.');
|
|
$result_data2 = $db->query("SELECT jahr, wert
|
|
FROM `prog_tmp_besschnitt`
|
|
WHERE jahr = '$jahrgang'");
|
|
$row_data2 = $result_data2->fetch_array();
|
|
$ju_bes_durchschnitt = number_format($row_data2['wert'], 2, ',', '.');
|
|
|
|
// ##################################################################
|
|
// #################Header#########################################
|
|
|
|
$inhalt_fin = '<page backtop="7mm" backbottom="10mm" style="font-size: 11pt">';
|
|
$inhalt_fin.= "<style type='text/css'>
|
|
td { border:0.3px solid black; }
|
|
.tdbes { border:0px solid black; font-size: 8px;}
|
|
.tdbeshead { border:0px solid black; font-size: 9px;}
|
|
.td_foot { border: none; }
|
|
body { font-size: 11px; }
|
|
table { font-size: 11px; }
|
|
p { font-size: 11px; }
|
|
table.page_footer {width: 100%; border: none; padding: 20mm}
|
|
.fcol{color: #0C25BF;}
|
|
.tg{
|
|
border-collapse:collapse;
|
|
border-spacing:0;
|
|
}
|
|
.tg td{
|
|
border-style:solid;
|
|
font-size: 6.5px;
|
|
border-width:1px;
|
|
overflow:hidden;
|
|
word-break:normal;
|
|
}
|
|
</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>";
|
|
$inhalt_fin.= "<h3>Jahresübersicht $jahrgang</h3>";
|
|
$inhalt_fin.= "<table border=0.3 cellspacing=0>";
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'><b>Zeilennr.</b></td>
|
|
<td width='280'><b>Beschreibung</b></td>
|
|
<td align='center' width='80'><b>Berechnung (anhand Zeilennr.)</b></td>
|
|
<td align='right' width='260'><b>Werte</b></td>
|
|
</tr>
|
|
<tr>
|
|
<td align='center' width='60'>1</td>
|
|
<td width='280'>Übertrag Vergaberahmen aus Vorjahr</td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>$ju_vergaberahmen_vj €</td>
|
|
</tr>";
|
|
|
|
// VZÄ
|
|
# AB Stufe 3 kann nicht sein, dass es keine erfassten VZÄ mehr gibt. Das System berechnet diese bis der letzte in Ruhestand geht
|
|
#$result_vzavorh = $db->query("SELECT count(*) Anz
|
|
# FROM prog_vza
|
|
# WHERE ab_datum like '$jahrgang%'
|
|
# ORDER BY ab_datum ASC");
|
|
#$row_vza_vorh = $result_vzavorh->fetch_array();
|
|
|
|
#if ($row_vza_vorh['Anz'] > 0) {
|
|
|
|
$result_data2 = $db->query("SELECT REPLACE(TRUNCATE(sum(prozent)/12,4),'.',',') wert
|
|
FROM prog_tmp_professor_synopse
|
|
WHERE monat like '$jahrgang%'
|
|
AND betrag != 0.00");
|
|
$row_data2 = $result_data2->fetch_array();
|
|
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'>2</td>
|
|
<td width='280'>Vollzeitäquivalente</td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>
|
|
<table width='100%' class='tg' align='right'>
|
|
<tr>
|
|
<td align='center' width='8%'><b>Jan</b></td>
|
|
<td align='center' width='8%'><b>Feb</b></td>
|
|
<td align='center' width='8%'><b>Mär</b></td>
|
|
<td align='center' width='8%'><b>Apr</b></td>
|
|
<td align='center' width='8%'><b>Mai</b></td>
|
|
<td align='center' width='8%'><b>Jun</b></td>
|
|
<td align='center' width='8%'><b>Jul</b></td>
|
|
<td align='center' width='8%'><b>Aug</b></td>
|
|
<td align='center' width='8%'><b>Sep</b></td>
|
|
<td align='center' width='8%'><b>Okt</b></td>
|
|
<td align='center' width='8%'><b>Nov</b></td>
|
|
<td align='center' width='8%'><b>Dez</b></td>
|
|
<td align='center' width='8%'><b>Schnitt</b></td>
|
|
</tr>
|
|
<tr>
|
|
";
|
|
|
|
|
|
$query2 = "SELECT REPLACE(TRUNCATE(sum(prozent),2),'.',',') wert
|
|
FROM prog_tmp_professor_synopse
|
|
WHERE monat like '$jahrgang%'
|
|
AND betrag != 0.00
|
|
GROUP BY date_format(monat,'%m')
|
|
ORDER BY date_format(monat,'%m') ASC";
|
|
$result2 = $db->query($query2) or die("Cannot execute query2");
|
|
|
|
while ($row2 = $result2->fetch_array()) {
|
|
$inhalt_fin.= "<td width='8%' align='center'>{$row2[wert]}</td>";
|
|
}
|
|
$inhalt_fin.= "<td width='8%' align='center'>$row_data2[wert]</td>
|
|
</tr>
|
|
</table>
|
|
</td>
|
|
</tr>";
|
|
|
|
|
|
|
|
#}else {
|
|
#
|
|
# // Dieses Jahr ist kein VZA Eintrag vorhanden
|
|
#
|
|
# $result_vzalast = $db->query("SELECT date_format(ab_datum, '%d.%m.%Y') ab_datum_form
|
|
# , replace(round(wert,2),'.',',') wert
|
|
# FROM prog_vza
|
|
# WHERE date_format(ab_datum,'%Y') <= '$jahrgang'
|
|
# ORDER BY ab_datum DESC
|
|
# LIMIT 1");
|
|
# $row_vza_last = $result_vzalast->fetch_array();
|
|
# $ju_last_vza_dat = $row_vza_last['ab_datum_form'];
|
|
# $ju_last_vza_wert = $row_vza_last['wert'];
|
|
# $inhalt_fin.= "<tr>
|
|
# <td align='center' width='60'>2</td>
|
|
# <td width='280'>Vollzeitäquivalente $ju_last_vza_dat</td>
|
|
# <td align='center' width='80'></td>
|
|
# <td width='260' align='right'>$ju_last_vza_wert %</td>
|
|
# </tr>";
|
|
#}
|
|
|
|
// Besoldungsdurchschnitt
|
|
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'>3</td>
|
|
<td width='280'>Besoldungsdurchschnitt</td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>$ju_bes_durchschnitt €</td>
|
|
</tr>";
|
|
|
|
// Jahresrahmen
|
|
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'>4</td>
|
|
<td width='280'>Jahresrahmen</td>
|
|
<td align='center' width='80'>2 x 3</td>
|
|
<td width='260' align='right'>$ju_jahresrahmen €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "</table>";
|
|
|
|
// Besoldungsausgaben
|
|
|
|
|
|
$result_besa = $db->query("SELECT sum(betrag) wert
|
|
FROM prog_tmp_professor_synopse
|
|
WHERE monat like '$jahrgang%'
|
|
");
|
|
$row_besa = $result_besa->fetch_array();
|
|
|
|
$result_besa_zul = $db->query("SELECT zulage
|
|
FROM prog_besausg_zulage
|
|
WHERE jahr = '$jahrgang'
|
|
");
|
|
$row_besa_zul = $result_besa_zul->fetch_array();
|
|
|
|
|
|
$ju_besa_wert = number_format($row_besa['wert'], 2, ',', '.');
|
|
$ju_besa_zulage = number_format($row_besa_zul['zulage'], 2, ',', '.');
|
|
$summe_besa = number_format($row_besa_zul['zulage'] + $row_besa['wert'], 2, ',', '.');
|
|
$inhalt_fin.= "<br />
|
|
<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td align='center' width='60'>5</td>
|
|
<td width='280'>Besoldungsausgaben</td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>
|
|
<table border='0' cellspacing='0' cellpadding='0' align='right'>
|
|
<tr>
|
|
<td class='tdbeshead' width='50' align='left' style='border-bottom: 1px solid black;'>
|
|
<b>Besoldung</b>
|
|
</td>
|
|
<td class='tdbeshead' width='25' align='left' style='border-bottom: 1px solid black;'>
|
|
<b>Stufe</b>
|
|
</td>
|
|
<td class='tdbeshead' width='25' align='right' style='border-bottom: 1px solid black;'>
|
|
<b>VZÄ</b>
|
|
</td>
|
|
<td class='tdbeshead' width='2' align='left' style='border-bottom: 1px solid black;'>
|
|
|
|
</td>
|
|
<td class='tdbeshead' width='75' align='left' style='border-bottom: 1px solid black;'>
|
|
<b>Zuweisung</b>
|
|
</td>
|
|
<td class='tdbeshead' width='62' align='right' style='border-bottom: 1px solid black;'>
|
|
<b>Betrag</b>
|
|
</td>
|
|
</tr>
|
|
";
|
|
|
|
$query3 = "SELECT distinct besoldung, stufe, bzid
|
|
FROM `prog_tmp_professor_synopse`
|
|
WHERE date_format(monat, '%Y') = '$jahrgang'
|
|
ORDER BY `prog_tmp_professor_synopse`.`besoldung` ASC";
|
|
$result3 = $db->query($query3) or die("Cannot execute query3");
|
|
$zaehler = 0;
|
|
while ($row3 = $result3->fetch_array()) {
|
|
$result_zudesc = $db->query("SELECT beschreibung
|
|
FROM prog_kat_besoldungszuweisung
|
|
WHERE bzid = '$row3[bzid]'
|
|
");
|
|
$row_zudesc = $result_zudesc->fetch_array();
|
|
|
|
if($row3['bzid'] == 1){
|
|
$zuweisung = '';
|
|
}else{
|
|
$zuweisung = $row_zudesc['beschreibung'];
|
|
}
|
|
|
|
$result_besadet = $db->query("SELECT sum(betrag) betragdetail, sum(prozent)/12 vza
|
|
FROM prog_tmp_professor_synopse
|
|
WHERE monat like '$jahrgang%'
|
|
and besoldung = '$row3[besoldung]'
|
|
and stufe = '$row3[stufe]'
|
|
and bzid = $row3[bzid]
|
|
");
|
|
$row_besadet = $result_besadet->fetch_array();
|
|
|
|
// Wenn einer Besoldung kein Wert zugewiesen wurde A13/Stufe 0 gibt es in der LBV-Liste nicht.
|
|
// Die Profs mit Stufe 0 sind mit 0 erfasst, da sie nicht für das System relevant sind und das Ergebnis verfälschen würden
|
|
if($row_besadet['betragdetail'] != 0){
|
|
if($row3['stufe'] == 0){
|
|
$stufedetail = "";
|
|
}else{
|
|
$stufedetail = $row3['stufe'];
|
|
}
|
|
|
|
// Hintergrundsfarbe
|
|
if($zaehler == 0){
|
|
$hgfarbe = "#ffffff";
|
|
$zaehler = 1;
|
|
}else{
|
|
$hgfarbe = "#d5d5d5";
|
|
$zaehler = 0;
|
|
}
|
|
|
|
|
|
$betragdetail = number_format($row_besadet['betragdetail'], 2, ',', '.');
|
|
$vza = number_format($row_besadet['vza'], 4, ',', '.');
|
|
|
|
|
|
$inhalt_fin.= "<tr>
|
|
<td class='tdbes' bgcolor='$hgfarbe' width='50' align='left'>$row3[besoldung]</td>
|
|
<td class='tdbes' bgcolor='$hgfarbe' width='25' align='left'>$stufedetail</td>
|
|
<td class='tdbes' bgcolor='$hgfarbe' width='25' align='right'>$vza</td>
|
|
<td class='tdbes' bgcolor='$hgfarbe' width='2' align='left'> </td>
|
|
<td class='tdbes' bgcolor='$hgfarbe' width='75' align='left'>$zuweisung</td>
|
|
<td class='tdbes' bgcolor='$hgfarbe' width='62' align='right'>$betragdetail €</td>
|
|
</tr>";
|
|
|
|
|
|
}
|
|
}
|
|
|
|
|
|
|
|
$inhalt_fin.= "<tr>
|
|
<td class='tdbes' width='177' colspan='5' align='left' style='border-top: 3px double black;'>Gesamt</td>
|
|
<td class='tdbes' width='62' align='right' style='border-top: 3px double black;'>$ju_besa_wert €</td>
|
|
</tr>
|
|
</table>
|
|
</td>
|
|
</tr>";
|
|
|
|
// Zulagen
|
|
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'>6</td>
|
|
<td width='280'>Zulagen HStZulV</td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>$ju_besa_zulage €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'></td>
|
|
<td width='280'>Summe Besoldungsausgaben</td>
|
|
<td align='center' width='80'>5 + 6</td>
|
|
<td width='260' align='right'>$summe_besa €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "</table>";
|
|
|
|
// Vergaberahmen
|
|
|
|
#$result_besa = $db->query("SELECT round(wert,2) wert
|
|
# ,round(zulage,2) zulage
|
|
# FROM prog_tmp_besausg
|
|
# WHERE jahr= '$jahrgang'
|
|
# LIMIT 1");
|
|
#$row_besa = $result_besa->fetch_array();
|
|
#$ju_besa_wert = number_format($row_besa['wert'], 2, ',', '.');
|
|
#$ju_besa_zulage = number_format($row_besa['zulage'], 2, ',', '.');
|
|
#$summe_besa = number_format($row_besa['zulage'] + $row_besa['wert'], 2, ',', '.');
|
|
$inhalt_fin.= "<br />
|
|
<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td align='center' width='60'>8</td>
|
|
<td width='280'>Vergaberahmen Jahr</td>
|
|
<td align='center' width='80'>4 - 7</td>
|
|
<td width='260' align='right'>$ju_vergaberahmen €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'>9</td>
|
|
<td width='280'>Vergaberahmen mit Übertrag</td>
|
|
<td align='center' width='80'>1 + 8</td>
|
|
<td width='260' align='right'>$ju_verg_uebertragvj €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "</table>";
|
|
|
|
// Leistungsbezüge
|
|
|
|
$result_lb_bz = $db->query("SELECT round(sum(wert),2) wert
|
|
FROM prog_tmp_lb_bz
|
|
WHERE date_format(monat,'%Y')= '$jahrgang'
|
|
LIMIT 1");
|
|
$row_lb_bz = $result_lb_bz->fetch_array();
|
|
$ju_lb_bz = number_format($row_lb_bz['wert'], 2, ',', '.');
|
|
|
|
$result_lb_lz = $db->query("SELECT round(sum(wert),2) wert
|
|
FROM prog_tmp_lb_lz
|
|
WHERE date_format(monat,'%Y') = '$jahrgang'
|
|
LIMIT 1");
|
|
$row_lb_lz = $result_lb_lz->fetch_array();
|
|
$ju_lb_lz = number_format($row_lb_lz['wert'], 2, ',', '.');
|
|
|
|
$result_lb_einmal = $db->query("SELECT round(wert,2) wert
|
|
FROM prog_tmp_lb_einmal
|
|
WHERE jahr= '$jahrgang'
|
|
LIMIT 1");
|
|
$row_lb_einmal = $result_lb_einmal->fetch_array();
|
|
$ju_lb_einmal = number_format($row_lb_einmal['wert'], 2, ',', '.');
|
|
$result_lb_fz = $db->query("SELECT round(sum(wert),2) wert
|
|
FROM prog_tmp_lb_fz
|
|
WHERE date_format(monat,'%Y') = '$jahrgang'
|
|
LIMIT 1");
|
|
$row_lb_fz = $result_lb_fz->fetch_array();
|
|
$ju_lb_fz = number_format($row_lb_fz['wert'], 2, ',', '.');
|
|
$ju_lb_summe = number_format($row_data1['leistungsbez'], 2, ',', '.');
|
|
$inhalt_fin.= "<br />
|
|
<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td align='center' width='60'>10</td>
|
|
<td width='280'>Berufungs- und Bleibeleistungsbezüge</td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>$ju_lb_bz €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'>11</td>
|
|
<td width='280'>besondere Leistungsbezüge mtl. (LBZ) </td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>$ju_lb_lz €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'>12</td>
|
|
<td width='280'>besondere Leistungsbezüge Einmalzahlungen</td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>$ju_lb_einmal €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'>13</td>
|
|
<td width='280'>Leistungsbezüge FZ</td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>$ju_lb_fz €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'>14</td>
|
|
<td width='280'>Summe der Leistungsbezüge</td>
|
|
<td align='center' width='80'>10+11+12+13</td>
|
|
<td width='260' align='right'>$ju_lb_summe €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "</table>";
|
|
|
|
// Auflistung des maximalen Leistungsbezugswert
|
|
|
|
$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();
|
|
$ju_maxzul_w2 = number_format($row_maxzul['w2'], 2, ',', '.');
|
|
$ju_maxzul_c3 = number_format($row_maxzul['c3'], 2, ',', '.');
|
|
$ju_maxzul_ges = number_format($row_maxzul['max_zulage'], 2, ',', '.');
|
|
|
|
# Obergrenze vor 2018
|
|
if($jahrgang < '2018' ){
|
|
$inhalt_fin.= "<br />
|
|
<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td align='center' width='60'>15</td>
|
|
<td width='280'>Grundgehalt W2</td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>$ju_maxzul_w2 €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'>16</td>
|
|
<td width='280'>Grundgehalt C3 - Stufe 15</td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>$ju_maxzul_c3 €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "<tr>
|
|
<td align='center' width='60'>17</td>
|
|
<td width='280'>Max. Leistungsbezüge</td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>$ju_maxzul_ges €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "</table>";
|
|
|
|
// Vergaberahmen nächstes Jahr
|
|
$inhalt_fin.= "<br />
|
|
<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td align='center' width='60'>18</td>
|
|
<td width='280'>Übertrag Vergaberahmen nächstes Jahr</td>
|
|
<td align='center' width='80'>1 + (8-14)</td>
|
|
<td width='260' align='right'>$ju_vergaberahmen_nj €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "</table>";
|
|
$inhalt_fin.= "</page>";
|
|
}
|
|
|
|
|
|
# Obergrenze nach 2018
|
|
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();
|
|
$maxwert = number_format($row_maxl['wert1'], 2, ',', '.');
|
|
|
|
$inhalt_fin.= "<br />
|
|
<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td align='center' width='60'>15</td>
|
|
<td width='280'>Max. Leistungsbezüge</td>
|
|
<td align='center' width='80'></td>
|
|
<td width='260' align='right'>$maxwert €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "</table>";
|
|
// Vergaberahmen nächstes Jahr
|
|
$inhalt_fin.= "<br />
|
|
<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td align='center' width='60'>16</td>
|
|
<td width='280'>Übertrag Vergaberahmen nächstes Jahr</td>
|
|
<td align='center' width='80'>1 + (8-14)</td>
|
|
<td width='260' align='right'>$ju_vergaberahmen_nj €</td>
|
|
</tr>";
|
|
$inhalt_fin.= "</table>";
|
|
$inhalt_fin.= "</page>";
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
// Berechnete BZ
|
|
|
|
$inhalt_fin.= "<page pageset='old' backtop='7mm' backbottom=10mm';>";
|
|
$inhalt_fin.= "<h3>Berufungs- und Bleibeleistungsbezüge $jahrgang</h3>";
|
|
$inhalt_fin.= "<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td width='180'>Name</td>
|
|
<td width='125'>Ab Datum</td>
|
|
<td width='125'>Ende</td>
|
|
<td width='125' align='right'>Jahresbetrag</td>
|
|
<td width='125' align='right'>Art</td>
|
|
</tr>";
|
|
|
|
|
|
$query3 = "SELECT distinct a.did
|
|
FROM prog_tmp_lb_bz 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, lbbzid
|
|
ORDER BY doz_name asc, lbbzid asc";
|
|
$result3 = $db->query($query3) or die("Cannot execute query3b");
|
|
$ln_zaehler5 = 0;
|
|
$ln_zaehler6 = 0;
|
|
|
|
while ($row3 = $result3->fetch_array()) {
|
|
$value_delta = '';
|
|
if ($ln_zaehler5 == 0) {
|
|
$last_did = $row3['did'];
|
|
$ln_zaehler5= 1;
|
|
}
|
|
|
|
if ($row3['did'] == $last_did) {
|
|
$farbe = "#d8d8d8";
|
|
}
|
|
else {
|
|
$farbe = "#f4f4f4";
|
|
$ln_zaehler5= 0;
|
|
}
|
|
|
|
$last_did = $row3['did'];
|
|
$query4 = "SELECT a.did, doz_titel, doz_vorname, doz_name, lbbzid, monat, sum(wert) wert, a.art
|
|
FROM prog_tmp_lb_bz 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, lbbzid
|
|
ORDER BY doz_name asc, lbbzid asc";
|
|
$result4 = $db->query($query4) or die("Cannot execute query4");
|
|
while ($row4 = $result4->fetch_array()) {
|
|
if ($ln_zaehler6 == 60) {
|
|
|
|
// Seitenumbruch nach 60 Zeilen
|
|
|
|
$inhalt_fin.= "</table>
|
|
</page>
|
|
<page pageset='old' backtop='7mm' backbottom=10mm';>
|
|
<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td width='180'>Name</td>
|
|
<td width='125'>Ab Datum</td>
|
|
<td width='125'>Ende</td>
|
|
<td width='125' align='right'>Jahresbetrag</td>
|
|
<td width='125' align='right'>Art</td>
|
|
</tr>";
|
|
$ln_zaehler6 = 0;
|
|
}
|
|
|
|
if ($row4['art'] == 'F') {
|
|
$art = 'Fiktiver Wert';
|
|
$edit = "";
|
|
}
|
|
|
|
if ($row4['art'] == 'B') {
|
|
$art = 'Erfasster Wert';
|
|
$edit = "$row4[did]";
|
|
}
|
|
|
|
if ($row4['art'] == 'O') {
|
|
$art = 'max. LBZ erreicht';
|
|
$edit = "$row4[did]";
|
|
}
|
|
|
|
// if($row4['lbbzid'] < 1000){
|
|
// Mindestdatum und Ende Laufzeit ermitteln
|
|
|
|
$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='$row4[lbbzid]'
|
|
AND did= '$row4[did]'
|
|
LIMIT 1");
|
|
$row_laufz = $result_laufz->fetch_array();
|
|
$startdat = "$row_laufz[min_monat_form]";
|
|
$enddat = "$row_laufz[max_monat_form]";
|
|
$wert_neu = number_format($row4['wert'], 2, ',', '.');
|
|
$inhalt_fin.= "<tr>
|
|
<td width='180' bgcolor='$farbe'>$row4[doz_name], $row4[doz_vorname]</td>
|
|
<td width='125' bgcolor='$farbe'>$startdat</td>
|
|
<td width='125' bgcolor='$farbe'>$enddat</td>
|
|
<td width='125' bgcolor='$farbe' align='right'>$wert_neu €</td>
|
|
<td width='125' bgcolor='$farbe' align='right'>$art</td>
|
|
</tr>";
|
|
|
|
// Zähler für Zeilenumbruch
|
|
|
|
$ln_zaehler6 = $ln_zaehler6 + 1;
|
|
}
|
|
}
|
|
|
|
$inhalt_fin.= "<tr>
|
|
<td width='180' bgcolor='$farbe'><b>Summe:</b></td>
|
|
<td width='125' bgcolor='$farbe'></td>
|
|
<td width='125' bgcolor='$farbe'></td>
|
|
<td width='125' bgcolor='$farbe' align='right'><b>$ju_lb_bz €</b></td>
|
|
<td width='125' bgcolor='$farbe' align='right'></td>
|
|
</tr>";
|
|
|
|
$inhalt_fin.= "</table>";
|
|
$inhalt_fin.= "</page>";
|
|
|
|
|
|
// Berechnete Deltas
|
|
|
|
$inhalt_fin.= "<page pageset='old' backtop='7mm' backbottom=10mm';>";
|
|
$inhalt_fin.= "<h3>besondere Leistungsbezüge mtl. (LBZ) $jahrgang</h3>";
|
|
$inhalt_fin.= "<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td width='180'>Name</td>
|
|
<td width='125'>Ab Datum</td>
|
|
<td width='125'>Ende</td>
|
|
<td width='125' align='right'>Jahresbetrag</td>
|
|
<td width='125' align='right'>Art</td>
|
|
</tr>";
|
|
|
|
// Deltas
|
|
|
|
$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, a.art
|
|
ORDER BY doz_name asc, lblzid asc";
|
|
$result3 = $db->query($query3) or die("Cannot execute query3a");
|
|
$ln_zaehler = 0;
|
|
$ln_zaehler1 = 0;
|
|
|
|
while ($row3 = $result3->fetch_array()) {
|
|
$value_delta = '';
|
|
if ($ln_zaehler == 0) {
|
|
$last_did = $row3['did'];
|
|
$ln_zaehler = 1;
|
|
}
|
|
|
|
if ($row3['did'] == $last_did) {
|
|
$farbe = "#d8d8d8";
|
|
}
|
|
else {
|
|
$farbe = "#f4f4f4";
|
|
$ln_zaehler = 0;
|
|
}
|
|
|
|
$last_did = $row3['did'];
|
|
$query4 = "SELECT a.did, doz_titel, doz_vorname, doz_name, lblzid, monat, sum(wert) wert, a.art
|
|
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, a.art
|
|
ORDER BY doz_name asc, lblzid asc";
|
|
$result4 = $db->query($query4) or die("Cannot execute query4");
|
|
while ($row4 = $result4->fetch_array()) {
|
|
if ($ln_zaehler1 == 60) {
|
|
|
|
// Seitenumbruch nach 60 Zeilen
|
|
|
|
$inhalt_fin.= "</table>
|
|
</page>
|
|
<page pageset='old' backtop='7mm' backbottom=10mm';>
|
|
<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td width='180'>Name</td>
|
|
<td width='125'>Ab Datum</td>
|
|
<td width='125'>Ende</td>
|
|
<td width='125' align='right'>Jahresbetrag</td>
|
|
<td width='125' align='right'>Art</td>
|
|
</tr>";
|
|
$ln_zaehler1 = 0;
|
|
}
|
|
|
|
if ($row4['art'] == 'F') {
|
|
$art = 'Fiktiver Wert';
|
|
$edit = "";
|
|
}
|
|
|
|
if ($row4['art'] == 'B') {
|
|
$art = 'Erfasster Wert';
|
|
$edit = "$row4[did]";
|
|
}
|
|
|
|
if ($row4['art'] == 'O') {
|
|
$art = 'max. LBZ erreicht';
|
|
$edit = "$row4[did]";
|
|
}
|
|
|
|
// if($row4['lblzid'] < 1000){
|
|
// Mindestdatum und Ende Laufzeit ermitteln
|
|
|
|
$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]";
|
|
$wert_neu = number_format($row4['wert'], 2, ',', '.');
|
|
$inhalt_fin.= "<tr>
|
|
<td width='180' bgcolor='$farbe'>$row4[doz_name], $row4[doz_vorname]</td>
|
|
<td width='125' bgcolor='$farbe'>$startdat</td>
|
|
<td width='125' bgcolor='$farbe'>$enddat</td>
|
|
<td width='125' bgcolor='$farbe' align='right'>$wert_neu €</td>
|
|
<td width='125' bgcolor='$farbe' align='right'>$art</td>
|
|
</tr>";
|
|
|
|
// Zähler für Zeilenumbruch
|
|
|
|
$ln_zaehler1 = $ln_zaehler1 + 1;
|
|
}
|
|
}
|
|
$inhalt_fin.= "<tr>
|
|
<td width='180' bgcolor='$farbe'><b>Summe:</b></td>
|
|
<td width='125' bgcolor='$farbe'></td>
|
|
<td width='125' bgcolor='$farbe'></td>
|
|
<td width='125' bgcolor='$farbe' align='right'><b>$ju_lb_lz €</b></td>
|
|
<td width='125' bgcolor='$farbe' align='right'></td>
|
|
</tr>";
|
|
|
|
$inhalt_fin.= "</table>";
|
|
$inhalt_fin.= "</page>";
|
|
|
|
// Funktionszulagen
|
|
// $inhalt_fin .= "<page>";
|
|
|
|
$inhalt_fin.= "<page pageset='old' backtop='7mm' backbottom='10mm' style='font-size: 11pt'>";
|
|
$inhalt_fin.= "<h3>Funktionsleistungsbezüge $jahrgang</h3>";
|
|
$inhalt_fin.= "<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td width='180'>Name</td>
|
|
<td width='125'>Ab Datum</td>
|
|
<td width='125'>Ende</td>
|
|
<td width='125' align='right'>Jahresbetrag</td>
|
|
<td width='125' align='right'>Art</td>
|
|
</tr>";
|
|
$query5 = "SELECT distinct a.kfid
|
|
FROM prog_tmp_lb_fz a, prog_kat_funktionen b
|
|
WHERE a.kfid=b.kfid
|
|
AND date_format(monat,'%Y') = '$jahrgang'
|
|
and wert > 0
|
|
GROUP BY a.lbfzid, b.kfid, a.did
|
|
ORDER BY bezeichnung asc, monat asc
|
|
";
|
|
$result5 = $db->query($query5) or die("Cannot execute query4");
|
|
$ln_zaehler2 = 0;
|
|
$ln_zaehler3 = 0;
|
|
|
|
while ($row5 = $result5->fetch_array()) {
|
|
$value_funktion = '';
|
|
if ($ln_zaehler2 == 0) {
|
|
$last_kfid = $row5['kfid'];
|
|
$ln_zaehler2 = 1;
|
|
}
|
|
|
|
if ($row5['kfid'] == $last_kfid) {
|
|
$farbe = "#d8d8d8";
|
|
}
|
|
else {
|
|
$farbe = "#f4f4f4";
|
|
$ln_zaehler2 = 0;
|
|
}
|
|
|
|
$last_kfid = $row5['kfid'];
|
|
$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.kfid = $row5[kfid]
|
|
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 query6");
|
|
while ($row6 = $result6->fetch_array()) {
|
|
if ($ln_zaehler3 == 60) {
|
|
|
|
// Seitenumbruch nach 60 Zeilen
|
|
|
|
$inhalt_fin.= "</table>
|
|
</page>
|
|
<page pageset='old' backtop='7mm' backbottom='10mm' style='font-size: 11pt'>
|
|
<table border=0.3 cellspacing=0>
|
|
<tr>
|
|
<td width='180'>Name</td>
|
|
<td width='125'>Ab Datum</td>
|
|
<td width='125'>Ende</td>
|
|
<td width='125' align='right'>Jahresbetrag</td>
|
|
<td width='125' align='right'>Art</td>
|
|
</tr>";
|
|
$ln_zaehler1 = 0;
|
|
}
|
|
|
|
if ($row6['art'] == 'F') {
|
|
$art = 'Fiktiver Wert';
|
|
$edit = "";
|
|
}
|
|
else {
|
|
$art = 'Erfasster Wert';
|
|
$edit = "$row6[kfid]";
|
|
}
|
|
|
|
if ($row6['did'] != 0) {
|
|
|
|
// Wenn kein fiktiver Wert zugeordneten Prof suchen
|
|
|
|
$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= '$row6[did]'
|
|
AND b.lbfzid=$row6[lbfzid]
|
|
ORDER BY startdat DESC
|
|
LIMIT 1");
|
|
$row_name = $result_name->fetch_array();
|
|
$name = trim("$row_name[doz_titel] $row_name[doz_name], $row_name[doz_vorname]");
|
|
$startdat = "$row_name[startdat]";
|
|
$enddat = "$row_name[enddat]";
|
|
}
|
|
else {
|
|
|
|
// Startdatum des fiktiven Werts herausfinden
|
|
|
|
$result_startf = $db->query("SELECT date_format(min(monat), '%d.%m.%Y') monat_form
|
|
FROM prog_tmp_lb_fz
|
|
WHERE kfid= '$row6[kfid]'
|
|
AND art='F'
|
|
ORDER BY Monat DESC
|
|
LIMIT 1");
|
|
$row_startf = $result_startf->fetch_array();
|
|
$name = "Fiktiver Wert";
|
|
$startdat = "$row_startf[monat_form]";
|
|
$enddat = "";
|
|
}
|
|
|
|
if ($row6['wert'] > 0) {
|
|
$wert_neu = number_format($row6['wert'], 2, ',', '.');
|
|
$row6['fz_art'] = $art;
|
|
$row6['edit'] = $edit;
|
|
$row6['wert'] = $wert_neu;
|
|
$row6['name'] = $name;
|
|
$row6['startdat'] = $startdat;
|
|
$row6['enddat'] = $enddat;
|
|
$value_funktion[] = $row6;
|
|
$inhalt_fin.= "<tr>
|
|
<td width='180' bgcolor='$farbe'>$row6[bezeichnung] ($name)</td>
|
|
<td width='125' bgcolor='$farbe'>$startdat</td>
|
|
<td width='125' bgcolor='$farbe'>$enddat</td>
|
|
<td width='125' bgcolor='$farbe' align='right'>$wert_neu €</td>
|
|
<td width='125' bgcolor='$farbe' align='right'>$art</td>
|
|
</tr>";
|
|
|
|
// Zähler für Zeilenumbruch
|
|
|
|
$ln_zaehler3 = $ln_zaehler3 + 1;
|
|
}
|
|
}
|
|
}
|
|
$inhalt_fin.= "<tr>
|
|
<td width='180' bgcolor='$farbe'><b>Summe:</b></td>
|
|
<td width='125' bgcolor='$farbe'></td>
|
|
<td width='125' bgcolor='$farbe'></td>
|
|
<td width='125' bgcolor='$farbe' align='right'><b>$ju_lb_fz €</b></td>
|
|
<td width='125' bgcolor='$farbe' align='right'></td>
|
|
</tr>";
|
|
$inhalt_fin.= "</table>";
|
|
$inhalt_fin.= "</page>";
|
|
|
|
|
|
// ##############################################################################################################
|
|
// 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');
|
|
|
|
$html2pdf->writeHTML($inhalt_fin, isset($_GET['vuehtml']));
|
|
$html2pdf->Output("Jahresuebersicht_$jahrgang.pdf", 'D');
|
|
|
|
// */
|
|
|
|
}
|
|
|
|
catch(HTML2PDF_exception $e) {
|
|
echo $e;
|
|
exit;
|
|
}
|
|
|
|
?>
|