popufare/server/ui/fleet-management/bulk_update.php

<?php
  include 'connect.php';
  
  if(! (isset($_FILES['routesfile']) && isset($_FILES['paddlesfile'])) )
  {
    $okay=false;
  }
  else
  {
    $okay=true;
  }
  
  if(!$okay)
  {
    echo 'Route spreadsheet should have the following format:';
    echo '<table border="1"><tr><th>A</th><th>B</th><th>C</th><th>...</th></tr>
                 <tr><td>Route#</td><td>Stop 1 Name</td><td>Stop 2 Name</td><td>...</td></tr>
                 <tr><td colspan="4"><center>...</center></td></tr></table><br>';

    echo 'Paddle spreadsheet should have the following format:';
    echo '<table border="1"><tr><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>...</th></tr>
                 <tr><td>Paddle#</td><td>Order#</td><td>Block#</td><td>Route #</td><td>Trip #</td><td>Stop 1 Time</td><td>Stop 2 Time</td><td>...</td></tr>
                 <tr><td colspan="8"><center>...</center></td></tr></table><br>';
    echo 'It should be noted that Order# and Block# are ignored, it is assumed that the spreadsheet is sorted.<br>';
    
    echo '
    <form enctype="multipart/form-data" action="bulk_update.php" method="POST">
    <input type="hidden" name="MAX_FILE_SIZE" value="400000" />
    Upload routes file (.csv): <input name="routesfile" type="file" accept="text/csv,application/csv" /><br />
    Upload paddles file (.csv): <input name="paddlesfile" type="file" accept="text/csv,application/csv" /><br />
    <input type="submit" value="Upload Files" />
    </form>';
    exit;
  }
  
  $routes=array();

  $stops=array();
  $res=mysqli_query($sql, "SELECT name,id FROM stops");
  while($row=mysqli_fetch_assoc($res))
  {
    $stops[$row['name']]=$row['id'];
  }
  

  //print_r($_FILES);

  $f=fopen($_FILES['routesfile']['tmp_name'],"rb");
  while($row=fgetcsv($f,1024))
  {
    $num=intval($row[0]);
    if($num > 0)
    {
      $idx=0;
      $tmp=array();

      while(strlen($row[$idx + 1]) > 0)
      {
        $tmp[$idx]=trim($row[$idx + 1]);
        $idx++;  
      }
      
      $routes[$num]=$tmp;
    }    
  }  
  fclose($f);
  
//  echo "<pre>";
//  print_r($routes);
//  echo "</pre>";

  $paddles=array();

  $f=fopen($_FILES['paddlesfile']['tmp_name'],"rb");
  while($row=fgetcsv($f,1024))
  {
    $pnum=intval($row[0]);
    
    if($pnum > 0)
    {
      $rt=intval($row[3]);
      $tp=intval($row[4]);
      
      if(isset($routes[$rt]))
      {
        $n=count($routes[$rt]);
        for($i=0;$i < $n; $i++)
        {
          $time=$row[$i + 5];
          
          if(isset($stops[$routes[$rt][$i]]))
          {
            $stpid=$stops[$routes[$rt][$i]];
          }
          else
          {
            $stpid=0;
            $err=$routes[$rt][$i];
            $x=$i+1;
            echo "Ignoring Unknown stop \"$err\" (Work Run $pnum Route $rt Trip $tp Stop $x)<br>";
            continue;
          }
          
          if(strlen(trim($time)) > 0)
          {
            $paddles[$pnum][]=array( 'id' => $pnum, 'arrival' => $time, 'route' => $rt, 'trip' => $tp, 'stop' => ($i + 1), 'stage' => ($i + 1), 'stopid' => $stpid);
          }
        }
      }
    }
    
  }  
  fclose($f);
  
  
  
  //echo '<pre>';
  //print_r($paddles);
  //echo '</pre>';

  foreach($paddles as $pnum => $paddle)
  {
    mysqli_query($sql,"DELETE FROM paddles WHERE id='$pnum'");
    
    foreach($paddle as $slot => $record)
    {
      $arr=$record['arrival'];
      $rt=$record['route'];
      $tp=$record['trip'];
      $stp=$record['stop'];
      $stg=$record['stage'];
      $stpid=$record['stopid'];
      
      mysqli_query($sql,"INSERT INTO paddles SET id='$pnum', slot='$slot', arrival='$arr', route='$rt', trip='$tp', stage='$stg', stop='$stp', stopid='$stpid'");
    }
    
  }

  echo "Done!<br>";
  echo '<a href="index.php?goto=mainmenu">Return to menu</a>';
  
?>