-
Gerd Schachtschneider authoredGerd Schachtschneider authored
cronDatabase.php 9.50 KiB
<?php
// Cronjob Skript fuer Aktionen ausserhalb der DB
// z.B. Mailbenachrichtigung, Loeschen verwaister Ablagen etc.
// stelle sicher das dieses Skript in einem Subdir liegt, normalerweise im Ordner cronjobs, sonst gibt es kausale Problem :-(
// schachi 2016-06-06
chdir(__DIR__);
chdir('../');
//print_r (realpath(__DIR__).' '.getcwd()."\n");
if (!is_readable('conf.ini') ) trigger_error ('Error loading config file from here '.getcwd()."\n");
$conf = array();
$conf = parse_ini_file('conf.ini', true);
//print_r ($conf);
if ( !isset( $conf['_database'] ) ) trigger_error ('Error loading config file. No database specified.');
$dbinfo =& $conf['_database'];
if ( !is_array( $dbinfo ) || !isset($dbinfo['host']) || !isset( $dbinfo['user'] ) || !isset( $dbinfo['password'] ) || !isset( $dbinfo['name'] ) ) {
trigger_error ('Error loading config file. The database information was not entered correctly.');
}
$db = mysqli_connect($dbinfo['host'], $dbinfo['user'], $dbinfo['password'], $dbinfo['name'] );
if ( !$db ) trigger_error ('Failed to connect to MySQL database: '.mysqli_connect_error($db)."\n");
$debug = 0; // 1 = ausgabe und keine Mail an User
$mailto = 0; // nur bei Debug aktiv, 0 = keine Mail senden im debugmodus
//$mailto = 'schachi@mpi-magdeburg.mpg.de'; // nur bei Debug aktiv, Mail an User $mailto
// url
$url = 'http';
if ( isset($conf['_own']['ssl'])) {
if ($conf['_own']['ssl'] == 1) $url = 'https';
}
$path = (basename(realpath('./')));
$host = shell_exec("hostname -f | tr -d '\n'");
$url = $url.'://'.$host.'/'.$path;
if ($debug) print_r($url."\n");
// loesche alle <table>__history, welche nicht erwuenscht sind
// Xataface hat nur einen globalen Schalter ON/OFF fuer history, aber wer braucht denn alle histories?
$sql = "SELECT reiter FROM view_reiter WHERE reiter IN (SELECT CONCAT(lst.reiter, '__history') AS table_his FROM list_reiter AS lst LEFT JOIN view_reiter AS vReit ON lst.reiter = vReit.reiter WHERE lst.history = '0' AND vReit.table_type = 'BASE TABLE' AND lst.reiter NOT LIKE '%__history') AND table_type = 'BASE TABLE';";
$result = mysqli_query($db, $sql) OR trigger_error ('Query reiter failed: '.mysqli_error($db)."\n");
$count = mysqli_num_rows($result);
if ( $count >= 1 ) {
while($row = mysqli_fetch_assoc($result)) {
$table = $row['reiter'];
$sql = "DROP TABLE IF EXISTS $table;";
if ($debug) echo "$sql\n";
mysqli_query($db, $sql) OR trigger_error ('Query history failed: '.mysqli_error($db)."\n");
}
}
// loesche alle Ablagen, welche keine Verbindung mehr haben
// schachi 2017-02-07
// uber trigger auch moeglich, aber in bestimmten Xataface-Dialogen kommen Probelme weil die alte Verbindund ploetzlich nicht mehr existiert
$sql = "DELETE FROM tab_ablage WHERE ablID NOT IN (SELECT ablID FROM con_ablage)";
mysqli_query($db, $sql) OR trigger_error ('Query del ablage failed: '.mysqli_error($db)."\n");
/*
// ueber trigger geloest
// loesche alle Verlinkungen, welche mal Verbindung hatten und nun keine Verbindung mehr haben
// schachi 2017-01-19
$sql = "DELETE FROM con_mainTab WHERE auswTabS = 'sysID' AND tabIDS NOT IN (SELECT tabID FROM mpi_arbeitsplatz)";
mysqli_query($db, $sql) OR trigger_error ('Query del anlage source failed: '.mysqli_error($db)."\n");
$sql = "DELETE FROM con_mainTab WHERE auswTabD = 'sysID' AND tabIDD NOT IN (SELECT tabID FROM mpi_arbeitsplatz)";
mysqli_query($db, $sql) OR trigger_error ('Query del anlage dest failed: '.mysqli_error($db)."\n");
$sql = "DELETE FROM con_mainTab WHERE auswTabS = 'gerID' AND tabIDS NOT IN (SELECT tabID FROM mpi_geraete)";
mysqli_query($db, $sql) OR trigger_error ('Query del geraete source failed: '.mysqli_error($db)."\n");
$sql = "DELETE FROM con_mainTab WHERE auswTabD = 'gerID' AND tabIDD NOT IN (SELECT tabID FROM mpi_geraete)";
mysqli_query($db, $sql) OR trigger_error ('Query del geraete dest failed: '.mysqli_error($db)."\n");
$sql = "DELETE FROM con_mainTab WHERE auswTabS = 'matID' AND tabIDS NOT IN (SELECT tabID FROM mpi_material)";
mysqli_query($db, $sql) OR trigger_error ('Query del material source failed: '.mysqli_error($db)."\n");
$sql = "DELETE FROM con_mainTab WHERE auswTabD = 'matID' AND tabIDD NOT IN (SELECT tabID FROM mpi_material)";
mysqli_query($db, $sql) OR trigger_error ('Query del material dest failed: '.mysqli_error($db)."\n");
// loesche verwaiste Verbindungen in tab_parameter
// schachi 2017-02-01
$sql = "DELETE FROM tab_parameter WHERE auswTab = 'sysID' AND tabID NOT IN (SELECT tabID FROM mpi_arbeitsplatz)";
mysqli_query($db, $sql) OR trigger_error ('Query del anlage source failed: '.mysqli_error($db)."\n");
$sql = "DELETE FROM tab_parameter WHERE auswTab = 'gerID' AND tabID NOT IN (SELECT tabID FROM mpi_geraete)";
mysqli_query($db, $sql) OR trigger_error ('Query del geraete dest failed: '.mysqli_error($db)."\n");
$sql = "DELETE FROM tab_parameter WHERE auswTab = 'matID' AND tabID NOT IN (SELECT tabID FROM mpi_material)";
mysqli_query($db, $sql) OR trigger_error ('Query del material source failed: '.mysqli_error($db)."\n");
$sql = "DELETE FROM tab_parameter WHERE auswTab = 'medID' AND tabID NOT IN (SELECT medID FROM mpi_medien)";
mysqli_query($db, $sql) OR trigger_error ('Query del medien dest failed: '.mysqli_error($db)."\n");
*/
// Script fuer automatisches senden von emails, wenn Minzahl erreicht oder unterschritten.
// schachi 2016-07-09
$sql = <<<EOT
SELECT
mat.tabID,
mat.name,
SUM(fluss.fluss) AS menge,
mat.`min`,
mat.einheit,
mat.nachricht
FROM
mpi_material AS mat
LEFT JOIN mpi_matMengenfluss AS fluss ON mat.tabID = fluss.matID
WHERE (mat.nachricht LIKE '%@%') AND (mat.`min` > 0) AND ((SELECT SUM(fluss) FROM mpi_matMengenfluss WHERE matID = mat.tabID) <= mat.`min`)
GROUP BY fluss.matID
ORDER BY name
EOT;
$result = mysqli_query($db, $sql) OR trigger_error ('Query failed: '.mysqli_error($db)."\n");
$count = mysqli_num_rows($result);
if ( $count >= 1 ) {
$table = 'mpi_material';
$field = 'tabID';
$base = $dbinfo['name'];
while($row = mysqli_fetch_array($result)) {
$tabID = $row[$field];
$name = $row['name'];
$mail = $row['nachricht'];
$anz = $row['menge'];
$min = $row['min'];
$binde = $row['einheit'];
if ($anz < $min) $ende = 'unterschritten!'; else $ende = 'erreicht.';
$body = "[INFO] Mindestmenge von $anz $binde für $name $ende";
$text = "Artikel: $name\nAnzahl: $anz\nMinimum: $min\nEinheit: $binde\n";
$link = "Link Artikel: $url/index.php?-table=$table&-action=browse&$field=$tabID\n";
$head = "From: Database ".$base." <".$mail.">\n";
$head .= "Content-Type: text/plain; charset=utf-8\n";
$head .= "MIME-Version: 1.0\n";
if ($debug) {
print_r( "$mail\n$body\n${text}${link}\n$head\n" );
if ($mailto != '0') mail( $mailto, $body, $text.$link, $head );
} else {
mail( $mail, $body, $text.$link, $head );
}
}
}
// Script fuer automatisches senden von emails, wenn Wartungstermin erreicht oder unterschritten.
// schachi 2016-07-07
function sendMail($db, $pre, $delay, $base, $table, $field, $url, $debug, $mailto) {
$sql = <<<EOT
SELECT
ger.tabID,
wart.wartID,
wart.status,
wart.intervall,
wart.letzte,
wart.naechste,
wart.nachricht,
wart.bemerkung,
ger.name,
ger.lagerort
FROM
mpi_gerWartung AS wart
LEFT JOIN
mpi_geraete AS ger ON ger.tabID = wart.gerID
WHERE
(wart.naechste = ADDDATE( CURDATE(), $delay)) AND (wart.status = 1) AND (wart.nachricht LIKE '%@%')
EOT;
$result = mysqli_query($db, $sql) OR trigger_error ('Query wartung failed: '.mysqli_error($db)."\n");
$count = mysqli_num_rows($result);
if ($debug) print_r ("\n$count $pre $delay\n");
if ( $count >= 1 ) {
while($row = mysqli_fetch_array($result)) {
$tabID = $row[$field];
$name = $row['name'];
$mail = $row['nachricht'];
$raum = $row['lagerort'];
$next = $row['naechste'];
$last = $row['letzte'];
$note = $row['bemerkung'];
if ( $delay > 0 ) {
$ende = 'ist in '.$delay.' Tagen.';
} elseif ( $delay < 0 ) {
$ende = 'ist überschritten!';
} else {
$ende = 'endet heute!';
}
$body = "$pre Wartungs- bzw. Prüftermin für $name $ende";
$text = "Gerät: $name\nRaum: $raum\nLetzter Termin: $last\nAktueller Termin: $next\nBemerkung: $note\n";
$link = "Link Wartungs- bzw. Prütermin: $url/index.php?-table=$table&-action=browse&$field=$tabID\n";
$head = "From: Database ".$base." <".$mail.">\n";
$head .= "Content-Type: text/plain; charset=utf-8\n";
$head .= "MIME-Version: 1.0\n";
if ($debug) {
print_r( "$mail\n$body\n${text}${link}\n$head\n" );
if ($mailto != '0') mail( $mailto, $body, $text.$link, $head );
} else {
mail( $mail, $body, $text.$link, $head );
}
}
}
}
if (!isset( $conf['_own']['notify'] )) $delay = 30; else $delay = $conf['_own']['notify'];
$base = $dbinfo['name'];
$table = 'mpi_gerWartung';
$field = 'wartID';
//sendMail( $db, '[INFO]', $delay, $base, $table, $field, $url, $debug, $mailto );
//sendMail( $db, '[TERMIN]', '7', $base, $table, $field, $url, $debug, $mailto );
sendMail( $db, '[WICHTIG]', '0', $base, $table, $field, $url, $debug, $mailto );
//sendMail( $db, '[WARNUNG]', '-7', $base, $table, $field, $url, $debug, $mailto );
mysqli_close($db);
?>