unfallkarte/lib/functions.php

205 lines
10 KiB
PHP
Raw Permalink Normal View History

2020-07-30 18:36:57 +02:00
<?php
function generate_tooltip_description($row) {
2020-07-30 19:46:02 +02:00
global $arrCategory;
global $arrUnfallart;
global $arrUnfalltyp;
global $arrLicht;
global $arrRad;
global $arrStrassenzustand;
2020-08-02 20:08:48 +02:00
global $arrWochentage;
2020-07-30 18:36:57 +02:00
global $db;
2020-07-30 19:46:02 +02:00
$strDescription = "<h3>Daten zum Unfall</h3>";
$description = "<strong>Unfallkategorie:</strong> ".$arrCategory[$row['UKATEGORIE']]."<br>";
2020-08-02 20:08:48 +02:00
$description .= "<strong>Unfallmonat/Jahr:</strong> ".$row['UMONAT']."/".$row['UJAHR']."<br>";
$description .= "<strong>Tag/Stunde:</strong> ".$arrWochentage[$row['UWOCHENTAG']]." ".$row['USTUNDE']." Uhr<br>";
2020-07-30 19:46:02 +02:00
$description .= "<strong>Unfallart:</strong> ".$arrUnfallart[$row['UART']]."<br>";
$description .= "<strong>Unfalltyp:</strong> ".$arrUnfalltyp[$row['UTYP1']]."<br>";
$description .= "<strong>Lichtverhältnisse:</strong> ".$arrLicht[$row['ULICHTVERH']]."<br>";
2022-07-23 10:19:44 +02:00
$description .= "<strong>Straßenzustand:</strong> ".$arrStrassenzustand[$row['USTRZUSTAND']]."<br>";
2020-07-30 19:46:02 +02:00
$description .= "<strong>Unfallbeteiligte:</strong> ";
if ($row['IstRad']) $description .="mit Fahrradbeteiligung, ";
if ($row['IstPKW']) $description .="mit PKW-Beteiligung, ";
if ($row['IstFuss']) $description .="mit Fußgängerbeteiligung, ";
if ($row['IstKrad']) $description .="mit Kraftradbeteiligung, ";
if ($row['IstGkfz']) $description .="mit Güterkraftzeugbeteiligung, ";
if ($row['IstSonstige']) $description .="mit Beteiligung eines oben nicht genannten Verkehrsmittels";
2020-07-30 18:36:57 +02:00
$strDescription .= nl2br2($description);
return $strDescription;
}
2023-07-28 07:45:47 +02:00
function generate_category($row) {
2023-07-28 22:36:31 +02:00
$strReturn = "UART_".$row['UART']."|UKATEGORIE_".$row['UKATEGORIE']."|UTYP_".$row['UTYP1'];
if ($row['IstRad']) $strReturn .="|UBETEILIGT_RAD";
if ($row['IstPKW']) $strReturn .="|UBETEILIGT_PKW";
if ($row['IstFuss']) $strReturn .="|UBETEILIGT_FUSS";
if ($row['IstKrad']) $strReturn .="|UBETEILIGT_KRAD";
if ($row['IstGkfz']) $strReturn .="|UBETEILIGT_LKW";
if ($row['IstSonstige']) $strReturn .="|UBETEILIGT_SONSTIGES";
2023-07-30 17:44:03 +02:00
if (substr_count($strReturn, '|')==3) $strReturn.="|UBETEILIGT_SELBST";
2023-07-28 22:36:31 +02:00
return $strReturn;
2023-07-28 07:45:47 +02:00
}
2020-07-30 18:36:57 +02:00
function read_gps_location($file){
if (is_file($file)) {
$info = exif_read_data($file);
if (isset($info['GPSLatitude']) && isset($info['GPSLongitude']) &&
isset($info['GPSLatitudeRef']) && isset($info['GPSLongitudeRef']) &&
in_array($info['GPSLatitudeRef'], array('E','W','N','S')) && in_array($info['GPSLongitudeRef'], array('E','W','N','S'))) {
$GPSLatitudeRef = strtolower(trim($info['GPSLatitudeRef']));
$GPSLongitudeRef = strtolower(trim($info['GPSLongitudeRef']));
$lat_degrees_a = explode('/',$info['GPSLatitude'][0]);
$lat_minutes_a = explode('/',$info['GPSLatitude'][1]);
$lat_seconds_a = explode('/',$info['GPSLatitude'][2]);
$lng_degrees_a = explode('/',$info['GPSLongitude'][0]);
$lng_minutes_a = explode('/',$info['GPSLongitude'][1]);
$lng_seconds_a = explode('/',$info['GPSLongitude'][2]);
$lat_degrees = $lat_degrees_a[0] / $lat_degrees_a[1];
$lat_minutes = $lat_minutes_a[0] / $lat_minutes_a[1];
$lat_seconds = $lat_seconds_a[0] / $lat_seconds_a[1];
$lng_degrees = $lng_degrees_a[0] / $lng_degrees_a[1];
$lng_minutes = $lng_minutes_a[0] / $lng_minutes_a[1];
$lng_seconds = $lng_seconds_a[0] / $lng_seconds_a[1];
$lat = (float) $lat_degrees+((($lat_minutes*60)+($lat_seconds))/3600);
$lng = (float) $lng_degrees+((($lng_minutes*60)+($lng_seconds))/3600);
//If the latitude is South, make it negative.
//If the longitude is west, make it negative
$GPSLatitudeRef == 's' ? $lat *= -1 : '';
$GPSLongitudeRef == 'w' ? $lng *= -1 : '';
return array(
'lat' => $lat,
'lng' => $lng
);
}
}
return false;
}
function nl2br2($string) {
$string = str_replace(array("\r\n", "\r", "\n"), "<br />", $string);
return $string;
2020-09-05 11:59:06 +02:00
}
2021-07-31 20:56:13 +02:00
function get_statistics($numVehicle,$strWhere,$strYear) {
2020-09-05 11:59:06 +02:00
global $db;
2020-09-05 17:36:49 +02:00
global $arrUnfallart;
global $arrUnfalltyp;
2021-08-01 21:46:53 +02:00
global $arrCategory;
2021-10-17 10:07:21 +02:00
global $strLocation;
2021-08-01 21:46:53 +02:00
2023-07-30 17:44:03 +02:00
// Unfallkategorie
$strTable="<h5><input type='radio' value='UKAT' name='kategorie' id='kategorie1' class='radio_header' checked='checked'>";
$strTable.="<label for='kategorie1'>Unfallkategorie</label></h5>";
2021-08-01 21:46:53 +02:00
$strTable.="<table class='statistik'>";
2021-10-17 10:07:21 +02:00
$strSQL="SELECT UKATEGORIE, count(UKATEGORIE) as anz FROM data WHERE ".$strWhere.$strYear.$strLocation." GROUP BY UKATEGORIE";
2021-08-01 21:46:53 +02:00
$result = $db->query($strSQL);
2021-10-17 10:07:21 +02:00
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
2023-07-30 16:58:10 +02:00
$strTable.="<tr><td class='td_ukat'><input type='checkbox' name='UKATEGORIE_".$row['UKATEGORIE']."' id='UKATEGORIE_".$row['UKATEGORIE']."' class='check ukat' checked='checked' ></td>"
2023-07-28 07:45:47 +02:00
."<td>".$arrCategory[$row['UKATEGORIE']]."</td><td>".$row['anz']."</td></tr>\n";
2021-08-01 21:46:53 +02:00
}
$strTable.="</table>";
2023-07-30 17:44:03 +02:00
//Unfallbeteilgte
2021-08-01 21:46:53 +02:00
2020-09-05 11:59:06 +02:00
$strSQL="SELECT
2023-07-28 22:36:31 +02:00
sum(IstRad) as rad,
sum(IstPKW) as pkw,
sum(IstFuss) as fuss,
sum(IstKrad) as krad,
sum(IstGkfz) as lkw,
sum(IstSonstige) as sonstiges,
count(*) as summe
FROM data
WHERE ".$strWhere.$strYear.$strLocation;
2020-09-05 11:59:06 +02:00
$result = $db->query($strSQL);
2023-07-30 17:44:03 +02:00
$strTable.="<h5><input type='radio' value='UBET' name='kategorie' id='kategorie2' class='radio_header'>";
$strTable.="<label for='kategorie2'>Unfallbeteiligte</label></h5>";
2021-08-01 21:46:53 +02:00
$strTable.="<table class='statistik'>";
2021-10-17 10:07:21 +02:00
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
2023-07-30 17:44:03 +02:00
$strTable.="<tr><td class='td_ubet'><input type='checkbox' name='UBETEILIGT_RAD' id='BETEILIGT_RAD' class='check veh_1' checked='checked' ></td><td>Fahrräder</td><td>".$row['rad']."</td></tr>";
$strTable.="<tr><td class='td_ubet'><input type='checkbox' name='UBETEILIGT_PKW' id='BETEILIGT_PKW' class='check veh_2' checked='checked' ></td><td>PKWs</td><td>".$row['pkw']."</td></tr>";
$strTable.="<tr><td class='td_ubet'><input type='checkbox' name='UBETEILIGT_FUSS' id='BETEILIGT_FUSS' class='check veh_3' checked='checked' ></td><td>Fussgänger</td><td>".$row['fuss']."</td></tr>";
$strTable.="<tr><td class='td_ubet'><input type='checkbox' name='UBETEILIGT_KRAD' id='BETEILIGT_KRAD' class='check veh_4' checked='checked' ></td><td>Krafträder</td><td>".$row['krad']."</td></tr>";
$strTable.="<tr><td class='td_ubet'><input type='checkbox' name='UBETEILIGT_LKW' id='BETEILIGT_LKW' class='check veh_5' checked='checked' ></td><td>LKWs</td><td>".$row['lkw']."</td></tr>\n";
$strTable.="<tr><td class='td_ubet'><input type='checkbox' name='UBETEILIGT_SONSTIGES' id='BETEILIGT_SONSTIGES' class='check veh_6' checked='checked' ></td><td>Sonstiges</td><td>".$row['sonstiges']."</td></tr>\n";
2020-09-05 11:59:06 +02:00
}
switch ($numVehicle) {
case 1:
case 0:
2021-10-17 10:07:21 +02:00
$strSQL="SELECT count(*) as count FROM data
WHERE IstPKW=0 and IstFuss=0 and IstKrad=0 and IstGkfz=0 and IstSonstige=0".$strLocation;
2020-09-05 11:59:06 +02:00
break;
case 2:
2021-10-17 10:07:21 +02:00
$strSQL="SELECT count(*) as count FROM data
WHERE IstRad=0 and IstFuss=0 and IstKrad=0 and IstGkfz=0 and IstSonstige=0".$strLocation;
2020-09-05 11:59:06 +02:00
break;
case 3:
2021-10-17 10:07:21 +02:00
$strSQL="SELECT count(*) as count FROM data
WHERE IstPKW=0 and IstRad=0 and IstKrad and IstGkfz=0 and IstSonstige=0".$strLocation;
2020-09-05 11:59:06 +02:00
break;
case 4:
2021-10-17 10:07:21 +02:00
$strSQL="SELECT count(*) as count FROM data
WHERE IstPKW=0 and IstFuss=0 and IstRad=0 and IstGkfz=0 and IstSonstige=0".$strLocation;
2020-09-05 11:59:06 +02:00
break;
case 5:
2021-10-17 10:07:21 +02:00
$strSQL="SELECT count(*) as count FROM data
WHERE IstPKW=0 and IstFuss=0 and IstKrad=0 and IstRad=0 and IstSonstige=0".$strLocation;
2020-09-05 11:59:06 +02:00
break;
case 6:
2021-10-17 10:07:21 +02:00
$strSQL="SELECT count(*) as count FROM data
WHERE IstPKW=0 and IstFuss=0 and IstKrad=0 and IstGkfz=0 and IstRad=0".$strLocation;
2020-09-05 11:59:06 +02:00
break;
}
2020-09-05 17:36:49 +02:00
2021-07-31 20:56:13 +02:00
$result = $db->query($strSQL.$strYear);
2021-10-17 10:07:21 +02:00
if ($row = $result->fetch(PDO::FETCH_ASSOC)) {
2023-07-30 17:44:03 +02:00
$strTable.="<tr><td class='td_ubet'><input type='checkbox' name='UBETEILIGT_SELBST' id='BETEILIGT_SELBST' class='check' checked='checked' ></td><td>Alleinunfälle und Unfälle mit geicher Fahrzeugart</td><td>".$row['count']."</td></tr>";
2020-09-05 11:59:06 +02:00
}
$strTable.="</table>";
2020-09-05 17:36:49 +02:00
2023-07-28 20:11:45 +02:00
//Unfalltyp
$strSQL="SELECT UTYP1, count(UTYP1) as anz FROM data WHERE ".$strWhere.$strYear.$strLocation." GROUP BY UTYP1";
2023-07-30 17:44:03 +02:00
$strTable.="<h5><input type='radio' value='UTYP' name='kategorie' id='kategorie3' class='radio_header'>";
$strTable.="<label for='kategorie3'>Unfalltyp</label></h5>";
2020-09-05 17:36:49 +02:00
$strTable.="<table class='statistik'>";
$result = $db->query($strSQL);
2021-10-17 10:07:21 +02:00
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
2023-07-30 16:58:10 +02:00
$strTable.="<tr><td class='td_utyp'><input type='checkbox' name='UTYP_".$row['UTYP1']."' id='UTYP_".$row['UTYP1']."' class='check utyp' checked='checked' ></td>"
2023-07-28 20:11:45 +02:00
."<td>".$arrUnfalltyp[$row['UTYP1']]."</td><td>".$row['anz']."</td></tr>\n";
2020-09-05 17:36:49 +02:00
}
$strTable.="</table>";
//Unfallart
2023-07-28 20:11:45 +02:00
$strSQL="SELECT UART, count(UART) as anz FROM data WHERE ".$strWhere.$strYear.$strLocation." GROUP BY UART";
2023-07-30 17:44:03 +02:00
$strTable.="<h5><input type='radio' value='UART' name='kategorie' id='kategorie4' class='radio_header'>";
$strTable.="<label for='kategorie4'>Unfallart<label></h5>";
2020-09-05 17:36:49 +02:00
$strTable.="<table class='statistik'>";
$result = $db->query($strSQL);
2021-10-17 10:07:21 +02:00
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
2023-07-30 16:58:10 +02:00
$strTable.="<tr><td class='td_uart'><input type='checkbox' name='UART_".$row['UART']."' id='UART_".$row['UART']."' class='check uart' checked='checked' ></td>"
2023-07-28 20:11:45 +02:00
."<td>".$arrUnfallart[$row['UART']]."</td><td>".$row['anz']."</td></tr>\n";
2020-09-05 17:36:49 +02:00
}
$strTable.="</table>";
2023-07-28 20:11:45 +02:00
//Erläuterung
2020-09-05 17:36:49 +02:00
$strTable.="<a href='https://recht.nrw.de/lmi/owa/br_vbl_show_pdf?p_id=12225' target='_blank'>Unfalltpyen</a><br>";
$strTable.="<a href='https://www.destatis.de/DE/Themen/Gesellschaft-Umwelt/Verkehrsunfaelle/Methoden/verkehrsunfaelle-grundbegriffe.pdf?__blob=publicationFile' target='_blank'>Unfallbeschreibung</a><br>";
2020-09-05 11:59:06 +02:00
return $strTable;
}