connect_errno ) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
}
// Valid HTML
echo "
Annotation Uploader
Annotation Uploader
";
// 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.
- The first row in your CSV file must be a header consisting of field/column names that match fields in the billing_log table.
- The seq_num field is required, all others are optional.
- Fields can be in any order.
- Only fields provided will be entered into the database.
- There is also an optional 'note' field, indicating a reason for the annotation.
- 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).
Download a sample .CSV file with all fields populated.
Return to menu";
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:
";
foreach($valid_fields as $valid_field) {
echo "$valid_field
";
}
exit;
}
}
// Don't allow any column to appear more than once
if(count(array_unique($csv_array[0])) "/\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 try again.";
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 "The following changes will be entered into the database.
";
$table = "";
$firstrow = true;
foreach($csv_array as $key => $element)
{
$table .= "";
foreach ($element as $subkey => $subelement) {
if ($firstrow == true) {
$table .= "| $subelement | ";
} else {
$table .= "$subelement | ";
}
}
$firstrow = false;
$table .= "
";
}
$table .= "
";
echo $table;
echo "Confirm new annotations for insertion to the database.
";
}
// 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.
";
// echo "" . $insert . "
";
if(!$mysqli -> query($insert)) {
echo "Insert operation failed: (" . $mysqli -> errno . ") ". $mysqli -> error;
} else {
echo "Done! Annotation table has been updated.
";
}
}
echo 'Return to Menu';
// Valid HTML
echo "
";
?>