Skip to content
Snippets Groups Projects
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);

?>