convert2mysql.php4

<?php
  /*******************************************
  /* Programm convert2mysql.php4
  /* convert binary data into MySQL data
  /* (c) Copyright 2002, Jens Bierkandt
  /* e-mail: jens@bierkandt.org
  /* Entstanden im Rahmen meiner Diplomarbeit
  /*******************************************
  */
  function convertFile($file) {
    // needed: name of input file
    error_reporting(63); // 0 = none, 63 = full
    include("/home/schtorch/public_html/diplom/config.php4")
      ;
    set_magic_quotes_runtime(0);
    //Check if file exists and open it
    $fp = fopen($file, "r");
    if (!$fp) {
      return "Datei ".$file." konnte nicht geladen
        werden\n";
    };
    // Read first line with Timestamp
    $datetime = fgets($fp, 150);
    // Get date
    $date = substr($datetime, 0, 10);
    if (!(preg_match("/^....-..-../", $date))) {
      fclose ($fp);
      return "Konnte Datum in Datei $file nicht finden
        (Fehler in Messdatei)\n";
    };
    // Get time
    $time = substr($datetime, 11, 8);
    if (!(preg_match("/^..:..:../", $time))) {
      fclose ($fp);
      return "Konnte Zeit in Datei $file nicht finden
        (Fehler in Messdatei)\n";
    };
    // Get sampling rate
    if (substr($datetime, 19, 1) == '.') {
      // we deal with microtime
      $a = explode(" ", substr($datetime, 20));
      $rate = strlen($a[0]); // 1/10, 1/100 ...
      $timelen = $a[1]; // how many bytes for timestamp used
      $start_mictime = $a[0];
    }
    if (!isset($rate)) {
      $rate = 0; // rate in seconds
    }
    if (!isset($timelen)) {
      $timelen = substr($datetime, 20);
    }
    if (!isset($start_mictime)) {
      $start_mictime = "";
    }
    // Get name of station in outback
    $station_name = trim(fgets($fp, 400));
    // Get name of computer in station
    $computer_name = trim(fgets($fp, 400));
    // Find the corresponding table to computer
    $db = mysql_connect($hostname, $username, $password);
    mysql_select_db($database, $db);
    $result = mysql_query("SELECT `table_name` FROM `admin`
      WHERE computer=\"$computer_name\"", $db);
    $row = mysql_fetch_row($result);
    echo mysql_error($db);
    $table = $row[0];
    mysql_close($db);
    if (!isset($table)) {
      fclose ($fp);
      return "Computer $computer_name in Datei $file
        unbekannt (Eingabefehler in Profilmaske)\n";
    }
    while (!feof($fp)) {
      $a = fread($fp, 1);
      $a = unpack('C1size', $a);
      if ($a['size'] == 0) // Ignore all until Startbit 0x00
      {
        break;
      }
    }
    // Try to open Temp-File for Output
    $fp_out = fopen("MySQL_tmp.txt", "w+");
    if (!$fp_out) {
      fclose ($fp);
      return "Konnte temporäre Datei nicht anlegen
        (Festplatte voll?)\n";
    };
    // convert data
    $year = substr($date, 0, 4);
    $month = substr($date, 5, 2);
    $day = substr($date, 8, 2);
    $hour = substr($time, 0, 2);
    $minute = substr($time, 3, 2);
    $second = substr($time, 6, 2);
    $start_time = mktime($hour, $minute, $second, $month,
      $day, $year);
    while (!feof($fp)) {
      $time_offset = "";
      $mictime = "";
      for ($j = 0; $j < $timelen; $j++) //read date and
        time offset
      {
        $a = fread($fp, 1);
        if ($a == "") continue 2;
        $a = unpack('C1size', $a);
        $b = dechex($a['size']);
        if (strlen($b) == 1) $b = "0".$b;
        $time_offset .= $b;
      }
      $time_offset = hexdec($time_offset)+$start_mictime;
      //echo "time1: ".$time_offset."\n";
      // do we deal mith mictime?
      if ($rate) {
        if (strlen($time_offset) < $rate) {
          $mictime = $time_offset;
        } else {
          $mictime = substr($time_offset,
            strlen($time_offset)-$rate);
        }
        $mictime = "0.".$mictime;
      }
      //echo "Mic: ".$mictime."\n";
      if (strlen($time_offset) > $rate) {
        $time_offset = substr($time_offset, 0,
          strlen($time_offset)-$rate);
      } else {
        $time_offset = "";
      }
      //echo "time2: ".$time_offset."\n";;
      $timesec = $start_time;
      $timesec += $time_offset;
      $id = $timesec.$mictime*10;
      $timesec = date("Y-m-d H:i:s", $timesec);
      if ($a != "") fwrite($fp_out,
        $id.";".$timesec.";".$mictime.";");
      for ($j = 0; $j < 6; $j++) {
        $a = fread($fp, 1);
        if ($a == "") break;
        $a = unpack('C1size', $a);
        fwrite($fp_out, $a['size'].";");
      }
      $a = fread($fp, 1);
      if ($a == "") break;
      $a = unpack('C1size', $a);
      fwrite($fp_out, "\n");
      if ($a['size']  != 0) {
        return "Konnte Stoppbits in Datei $file nicht
          finden (Fehler in Messdatei)\n";
      }
    }
    fclose($fp);
    fclose($fp_out);
    set_magic_quotes_runtime(get_magic_quotes_gpc());
    // Write data in table
    $db = mysql_connect($hostname, $username, $password);
    mysql_select_db($database, $db);
    mysql_query("LOAD DATA LOCAL INFILE 'MySQL_tmp.txt'
      IGNORE INTO TABLE `$table` FIELDS TERMINATED BY ';'
      LINES TERMINATED BY '\n' ", $db);
    if (mysql_error($db)) {
      mysql_close($db);
      return (mysql_error($db));
    };
    mysql_query("OPTIMIZE TABLE `$table`", $db);
    mysql_close($db);
    unlink("/home/schtorch/diplom/MySQL_tmp.txt");
    return "Datei $file erfolgreich konvertiert\n";
  };