clementinecomputing
/
popufare


			
				
					
						
						
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197
							<?php
// Allows a user to upload billing annotations to the billing_log_annotation table.Add
// Written by Chris Martin

session_start();

// Connect to database
$mysqli = new mysqli ( "localhost", "XXX_DBUSER_XXX", "XXX_DBPASSWORD_XXX", "XXX_DB_XXX" );
if ( $mysqli -> connect_errno ) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
}

// Valid HTML
echo "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\"
  \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">
  <html xmlns=\"http://www.w3.org/1999/xhtml\">
  <head>
    <title>Annotation Uploader</title>
  </head>
  <body>
  <h1>Annotation Uploader</h1>";

// If no annotation file is provided, prompt for .CSV upload
if(!isset($_POST['confirm']) && !isset($_FILES['annotation_file'])) {
  echo "Upload a .CSV file of billing annotations.<br />
    <ul>
      <li>The first row in your CSV file must be a header consisting of field/column names that match fields in the billing_log table.</li>
      <li>The seq_num field is required, all others are optional.</li>
      <li>Fields can be in any order.</li>
      <li>Only fields provided will be entered into the database.</li>
      <li>There is also an optional 'note' field, indicating a reason for the annotation.</li>
      <li>If using Excel to prepare your .CSV file, ensure that Excel does not auto-format your date or credential fields in the .CSV file. You can enforce this by enclosing your values in double quotes preceded with an equal sign, like =\"this\" (demonstrated in the example .CSV file).</li>
    </ul>
    <a href=\"example.csv\">Download a sample .CSV file</a> with all fields populated.
    <form enctype=\"multipart/form-data\" action=\"annotation.php\" method=\"POST\">
      <input type=\"hidden\" name=\"MAX_FILE_SIZE\" value=\"4000000\" />
      Upload annotations file (.csv): <input name=\"annotation_file\" type=\"file\" accept=\"text/csv,application/csv\" /> <input type=\"submit\" value=\"Upload File\" />
    </form>
    <a href=\"index.php?goto=mainmenu\">Return to menu</a>";
  exit;
}

// If .CSV file is uploaded, store as two-dimensional array and perform
// input validation. Present table of data to user for confirmation.
if(isset($_FILES['annotation_file'])) {
  // Convert .CSV file to two-dimensional array
  $tmp_name = $_FILES['annotation_file']['tmp_name'];
  $csv_array = array_map('str_getcsv', file($tmp_name));

  // If values are encapulated like ="this", remove encapsulation.
  // This is a workaround for Excel mangling date fields.
  for($row_count = 0; $row_count < count($csv_array); $row_count++) {
    for($col_count = 0; $col_count < count($csv_array[$row_count]); $col_count++) {
      // Match on values encapsulated like ="this"
      $pattern = "/\A=\"(.*)\"\z/";
      if(preg_match($pattern,$csv_array[$row_count][$col_count],$match) == 1) {
        // Remove encapulation if match is found
        $csv_array[$row_count][$col_count] = $match[1];
      }
    }
  }

  // If seq_num is not provided, exit with error message
  if(!in_array("seq_num",$csv_array[0])) {
    echo "seq_num is a required field and is missing from your input .csv file. Please try again.";
    exit;
  }

  // Query database for valid fields in billing_log
  $field_result = $mysqli -> query("SHOW FIELDS FROM billing_log");
  while ($field = $field_result -> fetch_assoc()) {
    $valid_fields[] = $field['Field'];
  }
  $valid_fields[] = "note";

  // Check if each field entered is a valid field in billing_log.
  // If an invalid field is found, exit with error message.
  foreach($csv_array[0] as $input_field) {
    if(!in_array($input_field,$valid_fields)) {
      echo "Your field \"$input_field\" is not valid. Please try again. Valid fields in billing_log:<br />";
      foreach($valid_fields as $valid_field) {
        echo "$valid_field<br />";
      }
      exit;
    }
  }

  // Don't allow any column to appear more than once
  if(count(array_unique($csv_array[0]))<count($csv_array[0])) {
    echo "You have provided more than one column with the same name. Please try again.";
    exit;
  }

  // Regexes of invalid input for each field
  $valid_chars = array(
    'seq_num' => "/\A\d*\z/",
    'equip_num' => "/\A\d*\z/",
    'driver' => "/\A\d*\z/",
    'paddle' => "/\A\d*\z/",
    'route' => "/\A\d*\z/",
    'trip' => "/\A\d*\z/",
    'stop' => "/\A\d*\z/",
    'ride_time' => "/\A\d{4}-\d{1,2}-\d{1,2} \d{1,2}:\d{2}(:\d{2})?\z/",
    'latitude' => "/\A-?\d+(\.\d+)?\z/",
    'longitude' => "/\A-?\d+(\.\d+)?\z/",
    'action' => "/\A[A-Z]+\z/",
    'rule' => "/.*/",
    'ruleparam' => "/.*/",
    'reason' => "/.*/",
    'credential' => "/\A[0-9:]+\z/",
    'logical_card_id' => "/\A\d*\z/",
    'cash_value' => "/\A\d*\z/",
    'stop_name' => "/.*/",
    'note' => "/.*/",
  );

  // Check all input against invalid characters array.
  foreach($csv_array[0] as $input_field) {
    $col_pos = array_search($input_field, $csv_array[0]);
    for ($row = 1; $row <= count($csv_array)-1; $row++) {
      $value = $csv_array[$row][$col_pos];
      if(!empty($value)) {
        $valid_match = preg_match($valid_chars[$input_field],$value);
        if($valid_match==0) {
          $human_row = $row + 1;
          echo "Row $human_row of your CSV file contains invalid characters in field \"$input_field\", value \"$value\". Please <a href=\"annotation.php\">try again</a>.";
          exit;
        }
      }
    }
  }
  
  // Store CSV array as session variable
  $_SESSION['csv_array'] = $csv_array;
  
  // Display CSV table to user as HTML table and prompt for confirmation
  echo "<p>The following changes will be entered into the database.</p>";
  $table = "<table border=\"1\">";
  $firstrow = true;
  foreach($csv_array as $key => $element)
  {
    $table .= "<tr>";
    foreach ($element as $subkey => $subelement) {
      if ($firstrow == true) {
        $table .= "<th>$subelement</th>";      
      } else {
        $table .= "<td>$subelement</td>";
      }
    }
    $firstrow = false;
    $table .= "</tr>";
  }
  $table .= "</table>";
  echo $table;
  echo "Confirm new annotations for insertion to the database.<br />  
    <form method=\"post\" action=\"annotation.php\">
    <input type=\"hidden\" name=\"confirm\" value=\"true\">
    <button type=\"submit\">Confirm</button><br />
    <a href=\"annotation.php\">Cancel</a><br />
    </form>";
}

// If user has confirmed input, insert into database
if(isset($_POST['confirm'])) {
  $csv_array = $_SESSION['csv_array'];
  $columns = implode(', ', array_shift($csv_array));
  $values = array();
  // Process each row of array
  foreach($csv_array as $row) {
    // Process each value in row
    foreach($row as $key => $value) {
      if(!empty($row[$key])) {
        // Escape each input value for SQL query
        $row[$key] = "'" . $mysqli->real_escape_string($row[$key]) . "'";
      } else {
        $row[$key] = 'NULL';
      }
    }
    $values[] = "(" . implode(', ', $row) . ")";
  }
  $insert = "INSERT INTO billing_log_annotation ($columns) VALUES " . implode(', ', $values);
  echo "Executing database insertion.<br />";
  // echo "<code>" . $insert . "</code><br />";
  if(!$mysqli -> query($insert)) {
    echo "Insert operation failed: (" . $mysqli -> errno . ") ". $mysqli -> error;
  } else {
    echo "Done! Annotation table has been updated.<br />";
  }
}

echo '<a href="index.php?goto=mainmenu">Return to Menu</a>';

// Valid HTML
echo "</body>
  </html>";

?>