93 lines
3.5 KiB
PHP
Executable File
93 lines
3.5 KiB
PHP
Executable File
<?php
|
|
require_once("config.inc.php");
|
|
|
|
function func_doz_synopse()
|
|
{
|
|
|
|
// Stellt den Arbeitsumfang (VZÄ) und die Besoldung pro Monat zusammen
|
|
|
|
$db = dbconnect();
|
|
$result_truncate = $db->query("TRUNCATE TABLE prog_tmp_professor_synopse");
|
|
|
|
// Start des Prognosetools
|
|
$query_minjahr = $db->query("SELECT min(jahr) jahr
|
|
FROM `prog_vergaberahmen`
|
|
");
|
|
$row_minjahr = $query_minjahr->fetch_array();
|
|
$start = new DateTime($row_minjahr['jahr'] . '-01-01');
|
|
|
|
// Ende, wenn letzter Prof in Ruhestand geht
|
|
$result = $db->query("SELECT date_format(max(weggang),'%Y-%m-28') enddat_form
|
|
FROM prog_tmp_professor
|
|
WHERE zugang != '0000-00-00'");
|
|
$row = $result->fetch_array();
|
|
|
|
$ende = $row['enddat_form'];
|
|
$end = date_create($ende, new DateTimeZone('GMT'));
|
|
|
|
|
|
|
|
// Foreach Schleife dauert lange. Ggf.
|
|
|
|
foreach (new DatePeriod($start, new DateInterval('P1M'), $end) as $cur) {
|
|
$akt_mon = $cur->format('Y-m');
|
|
|
|
// Schleife über alle Professoren, die im aktuellen Monat an der HS waren
|
|
$query2 = "SELECT did
|
|
FROM prog_tmp_professor
|
|
WHERE date_format(zugang, '%Y-%m') <= '$akt_mon'
|
|
AND (date_format(weggang, '%Y-%m')> '$akt_mon' or weggang = '0000-00-00')
|
|
";
|
|
|
|
$result2 = $db->query($query2) or die("Cannot execute query2");
|
|
while ($row2 = $result2->fetch_array()) {
|
|
#echo "<pre>";
|
|
#echo "$akt_mon<br>";
|
|
#echo "</pre>";
|
|
|
|
// Arbeitsumfang des Profs zu diesem Zeitpunkt
|
|
$result_umf = $db->query("SELECT prozent
|
|
FROM prog_tmp_professor_arbumfang
|
|
WHERE date_format(startdat, '%Y-%m') <= '$akt_mon'
|
|
AND did = '$row2[did]'
|
|
ORDER BY startdat desc
|
|
LIMIT 1
|
|
");
|
|
$row_umf = $result_umf->fetch_array();
|
|
|
|
// Besoldung des Profs zu diesem Zeitpunkt
|
|
$result_bes = $db->query("SELECT besoldung, stufe, art, bzid
|
|
FROM prog_tmp_professor_besoldung
|
|
WHERE date_format(startdat, '%Y-%m') <= '$akt_mon'
|
|
AND did = '$row2[did]'
|
|
ORDER BY startdat desc
|
|
LIMIT 1
|
|
");
|
|
$row_bes = $result_bes->fetch_array();
|
|
|
|
$result_ggeh = $db->query("SELECT betrag, art
|
|
FROM prog_tmp_grundgehalt
|
|
WHERE date_format(startdat, '%Y-%m') <= '$akt_mon'
|
|
AND besoldung = '$row_bes[besoldung]'
|
|
AND stufe = '$row_bes[stufe]'
|
|
ORDER BY startdat desc
|
|
LIMIT 1
|
|
");
|
|
$row_ggeh = $result_ggeh->fetch_array();
|
|
$gehalt = $row_ggeh['betrag']*$row_umf['prozent'];
|
|
#if($row_bes['besoldung'] == 'W3'){
|
|
#echo "$akt_mon | $row_ggeh[betrag]*$row_umf[prozent]<br>";
|
|
#}
|
|
$result_insert = $db->query("INSERT INTO prog_tmp_professor_synopse (monat, did, prozent, besoldung, stufe, bzid, betrag, bes_art) VALUES ('$akt_mon-01', $row2[did], '$row_umf[prozent]', '$row_bes[besoldung]', '$row_bes[stufe]', '$row_bes[bzid]', '$gehalt', '$row_ggeh[art]')");
|
|
}
|
|
}
|
|
|
|
// echo "<hr>";
|
|
|
|
|
|
|
|
}
|
|
|
|
#func_doz_synopse()
|
|
?>
|