<?php
include "db.php";

$charSet = "utf8";
$collation = "utf8_general_ci";

$databases = array(
                  "hotlunch",
                  );


$Commands = array();
$fp = fopen("fix.sql","r");
while ($q = trim(fgets($fp,1000))) {
   $Commands[] = $q;
}


for ($i = 0; $i < count($databases); $i++) {
   $database = $databases[$i];
   db_query("USE $database");
   echo("*** $database ***\n");

   $tables = db_query("SHOW TABLES");
   while ($row = db_fetch_array($tables)) {
      $tableName = $row[0];
      echo("Table: $tableName\n");

      $columns = db_query("SHOW COLUMNS FROM $tableName");
      while ($row = db_fetch_array($columns)) {
         $columnName = $row['Field'];
         $columnType = $row['Type'];
         $columnNull = $row['Null'];

         if ($columnType == "date") {
            echo("$columnName: $columnType\n");
            $count = db_getVal("SELECT count(*) FROM $tableName WHERE $columnName < '1900-01-01'");
            if ($count > 0) {
               echo(" $tableName\n");
               echo("  - $columnName  $columnType ($count)\n");
               echo("\n");
               if ($columnNull == 'YES') {
                  echo("    UPDATE $tableName SET $columnName = NULL WHERE $columnName < '1900-01-01'\n");
                  db_query("UPDATE $tableName SET $columnName = NULL WHERE $columnName < '1900-01-01'");
               }
               else {
                  echo("    UPDATE $tableName SET $columnName = '2020-01-01' WHERE $columnName < '1900-01-01'\n");
                  db_query("UPDATE $tableName SET $columnName = '2020-01-01' WHERE $columnName < '1900-01-01'");
               }
            }

            $count = db_getVal("SELECT count(*) FROM $tableName WHERE $columnName LIKE '%-00%'");
            if ($count > 0) {
               echo(" $tableName\n");
               echo("  - $columnName  $columnType ($count)\n");
               echo("\n");
               $results = db_query("SELECT * FROM $tableName WHERE $columnName LIKE '%-00%'");
               while ($row = db_fetch_assoc($results)) {
                  $rnum = $row['record_num'];
                  $date = $row[$columnName];
                  $date = str_replace("-00","-01",$date);
                  $row[$columnName] = $date;
                  echo("    UPDATE $tableName SET $columnName = '$date' WHERE record_num = $rnum\n");
                  db_query("UPDATE $tableName SET $columnName = '$date' WHERE record_num = $rnum");
               }
            }
         }

         if ($columnType == "datetime") {
            $count = db_getVal("SELECT count(*) FROM $tableName WHERE $columnName < '1900-01-01 00:00:00'");
            if ($count > 0) {
               echo(" $tableName\n");
               echo("  - $columnName  $columnType ($count)\n");
               echo("\n");
               if ($columnNull == 'YES') {
                  echo("    UPDATE $tableName SET $columnName = NULL WHERE $columnName < '1900-01-01 00:00:00'\n");
                  db_query("UPDATE $tableName SET $columnName = NULL WHERE $columnName < '1900-01-01 00:00:00'");
               }
               else {
                  echo("    UPDATE $tableName SET $columnName = '2020-01-01 00:00:00' WHERE $columnName < '1900-01-01 00:00:00'\n");
                  db_query("UPDATE $tableName SET $columnName = '2020-01-01 00:00:00' WHERE $columnName < '1900-01-01 00:00:00'");
               }
            }

            $count = db_getVal("SELECT count(*) FROM $tableName WHERE $columnName LIKE '%-00%'");
            if ($count > 0) {
               echo(" $tableName\n");
               echo("  - $columnName  $columnType ($count)\n");
               echo("\n");
               $results = db_query("SELECT * FROM $tableName WHERE $columnName LIKE '%-00%'");
               while ($row = db_fetch_assoc($results)) {
                  $rnum = $row['record_num'];
                  $date = $row[$columnName];
                  $date = str_replace("-00","-01",$date);
                  $row[$columnName] = $date;
                  echo("    UPDATE $tableName SET $columnName = '$date' WHERE record_num = $rnum\n");
                  db_query("UPDATE $tableName SET $columnName = '$date' WHERE record_num = $rnum");
               }
            }
         }
      }
   }
}

?>