<?php /* -------------------------------------------- * Verspätungsalarm einrichten * Autor: Walter Hupfeld * Version 0.1 vom 10.10.2017 * zuletzt bearbeitet: 10.10.2017 * -------------------------------------------- */ ?> <!doctype html> <html> <head> <meta charset="utf-8"> <title>DB-Verspätungsalarm</title> <meta name="description" content="Deutsche Bahn Verspätungsalarm einrichten"> <meta name="author" content="Walter Hupfeld"> <link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-/Y6pD6FV/Vv2HJnA6t+vslU6fwYXjCFtcEpHbNJ0lyAFsXTsjBbfaDjzALeQsN6M" crossorigin="anonymous"><script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script> <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" integrity="sha384-wvfXpqpZZVQGK6TAh5PVlGOfQNHSoD2xbE+QkPxCAFlNEevoEH3Sl0sibVcOQVnN" crossorigin="anonymous"> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta/js/bootstrap.min.js" integrity="sha384-h0AbiXch4ZDo7tp9hKZ4TsHbi047NrKGLO3SEJAg45jXxnGIfYzk4Si90RDIqNm1" crossorigin="anonymous"></script> <style> .choosen { width: 500px; } .red { color:red;} </style> <script type="text/javascript"> function submitform() { document.zugalarm.submit(); } </script> </head> <?php include("lib/settings.php"); include("lib/phpbahn.php"); $db = new SQLite3(DB_FILENAME); /* /Noch an geänderte Datenstruktur anpassen 10.10.18 $db-> exec("CREATE TABLE IF NOT EXISTS tableAlarm( id INTEGER PRIMARY KEY AUTOINCREMENT, email text NOT NULL DEFAULT '', zug text NOT NULL DEFAULT '', station text NOT NULL DEFAULT '')"); */ $strIbnr = (!empty($_POST['station'])) ? $_POST['station'] : "8000149"; //Hamm $numHour = (!empty($_POST['hour'])) ? $_POST['hour'] : 0 ; $strEmail = (!empty($_POST['email'])) ? $_POST['email'] : ""; $strStation = $arrStations[$strIbnr]; // Züge zum Verspätungsalarm ausgewählt -> in die Datenbank einfügen $arrSZuege = (!empty($_POST['zug'])) ? $_POST['zug'] : array(); foreach ($arrSZuege as $strZug) { //Zugnummer und Abfahrtszeit splitten $arrZug = explode("_",$strZug); $strZug = $arrZug[0]; $strDeparture = $arrZug[1]; //Erst mal schauen, ob er Zug schon eingetragen ist. $strSQL = "SELECT id FROM tableAlarm WHERE email='$strEmail' AND zug='$strZug' AND station='$strStation'"; $result=$db->query($strSQL); if ($result->fetchArray()==FALSE) { //Zug eingtragen $strSQL = "INSERT INTO tableAlarm (email,zug,station,departure,ibnr) VALUES ('$strEmail','$strZug','$strStation','$strDeparture','$strIbnr')"; $db-> exec($strSQL); } } $boolOk = false; // Formular ausgefüllt und E-Mail vorhanden - Züge und Abfahrtstermine ermitteln if (!(empty($strIbnr) or empty($numHour) or empty($strEmail))) { $boolOk = true; $bahn = new phpbahn(SETTING_APIKEY); //Bahnhofsnummer muss nicht jedesmal ermittelt werden //Wird in per Hand in settings.php eingetragen //$bhf = $bahn->getStation($strStation) ; //reset($bhf); //$ibnr = key($bhf); //$bhf = array_shift($bhf); $time = strtotime('midnight')+$numHour*3600; $zuege = $bahn->getTimetable($strIbnr, $time ); if(!count($zuege)){ echo "keine Verbindungen";} } // Eintrag für Teilnehmer löschen if ($_POST['delete']==123){ $numId = (!empty($_POST['id'])) ? (int)$_POST['id'] : -1; $strSQL="DELETE FROM tableAlarm WHERE id=$numId"; $db = new SQLite3(DB_FILENAME); $db->exec($strSQL); } ?> <body> <div class="container"> <h2>Verpätungsalarm einrichten</h2> <div class="row"> <div class="col-6"> <div class="card choosen"> <div class="card-header"><h4>Alarm hinzufügen</h4></div> <div class="card-body"> <form id="formv" method="post" action="<?=$strUrl?>" style="width:450px;"> <div class="form-group"> <label for="station">Abfahrtsbahnhof:</label> <select class="form-control" id="station" name="station"> <?php foreach ($arrStations as $strKey => $strVal) { $strSelected = ($strIbnr == $strKey) ? "selected='selected'" : ""; echo "<option $strSelected value='$strKey'>$strVal</option>›"; } ?> </select><br> <label for="hour">Abfahrt</label> <select class="form-control" id="hour" name="hour"> <?php for ($i=5;$i<=20;$i++) { $strSelected = ($i==$numHour) ? "selected='selected'" : "" ; echo "<option $strSelected value='$i'>$i:00</option>"; } ?> </select><br> <label for="email">Mailadresse:</label> <input type="email" class="form-control" id="email" name="email" type="text" value="<?=$strEmail?>"><br> <?php if ($boolOk) { foreach ($zuege as $zug) { if(isset($zug['abfahrt'])){ echo "<div class='row'>"; $strZiel = array_pop($zug['abfahrt']['routeGeplant']); $timeAbfahrt = $bahn->dateToTimestamp($zug['abfahrt']['zeitGeplant']); $strAbfahrt = date("H:i",$timeAbfahrt); $strBahnValue = $zug['zug']['klasse'].$zug['zug']['nummer']."_".$strAbfahrt; $strBahnAnzeige = $zug['zug']['klasse']." ".$zug['zug']['nummer']."</div>". "<div class='col-2'>" . $strAbfahrt . "</div>". "<div class='col-6'>" . $strZiel . "</div>"; echo "<div class='col-4'> <input type='checkbox' name='zug[]' class='form-check-input' value='$strBahnValue'> "; echo $strBahnAnzeige; echo "</div>\n"; } } //DEBUG: echo "<pre>"; print_r($zuege); echo "</pre>"; } ?> <button type="submit" class="btn btn-primary">Abschicken</button> </div> <!-- form-group --> </div> <!-- card-body --> </div> <!-- card --> </form> </div> <!-- col-6 --> <div class="col-6"> <?php //DEBUG: echo "<pre>"; print_r($_POST); echo "</pre>"; if (!empty($strEmail)) { ?> <div class='card choosen'> <div class='card-header'> <h4>Ihre vorhandenen Alarme:</h4> </div> <div class='card-body'> <strong>E-Mail</strong>: <?= $strEmail?><br> <?php $strSQL = "SELECT DISTINCT * from tableAlarm WHERE email='$strEmail' ORDER BY departure"; $result = $db->query($strSQL); echo "<form id='zugalarm' name='zugalarm' action='$strUrl' method='post' onsubmit=''>\n"; echo "<div class='row'>"; while ($row = $result->fetchArray()) { echo "<div class='col-4'>".$row['station']."</div>\n"; echo "<div class='col-4'><i class='fa fa-train' aria-hidden='true'></i> ".$row['zug']."</div>\n"; echo "<div class='col-3'>".$row['departure']." Uhr</div>\n"; echo "<div class='col-1'>\n"; echo "<input type='hidden' name='email' value='$strEmail'>\n"; echo "<input type='hidden' name='hour' value='$numHour'>\n"; echo "<input type='hidden' name='station' value='$strIbnr'>\n"; echo "<input type='hidden' name='id' value='".$row['id']."'>\n"; echo "<input type='hidden' name='delete' value='123'>\n"; echo "<a href='javascript: submitform()'><i class='fa fa-times red' aria-hidden='true'></i></a>\n"; echo "</div>\n"; } ?> </div> <!-- row --> </form> </div> <!-- card-body --> </div> <!-- card --> <br> <div class="card choosen"> <div class="card-header"> <h4>Verspätungsstatistik</h4> </div> <div class="card-body"> <table class="table"> <tr><th>Zug</th><th>Mo</th><th>Di</th><th>Mi</th><th>Do</th><th>Fr</th></tr> <?php $strSQL = "SELECT DISTINCT zug,max(verspaetung) as verspaetung, DATE(zeit,'m.Y') as datum FROM verspaetungen WHERE email='$strEmail' GROUP BY zug,verspaetung"; $result = $db->query($strSQL); while ($row = $result->fetchArray()) { echo "<tr><th>".$row['zug']."</th>"; echo "<td>".$row['datum']."</td><td>".$row['verspaetung']/60 ."</td><td>Mi</td><td>Do</td><td>Fr</td></tr>"; } ?> </table> </div><!-- card-body --> </div> <!-- card --> <?php } ?> </div> <!-- col-6 --> </div> <!-- row --> </div> <!-- container --> </body> </html>