function CleanUp($field)
{
$delimiter = '/\\v|\\s*[,|\\/\\\\.]\\s*/';
$split_array = preg_split($delimiter, $field);
$split_array = array_map(function ($e) {
return StripSpace($e);
}, $split_array);
return json_encode($split_array);
}
function ProcessFile($file, $region)
{
global $db;
$sql_find = <<<SQL
\t\t\tSELECT
\t\t\t\tid,
\t\t\t\tname
\t\t\tFROM
\t\t\t\tplaces
\t\t\tWHERE
\t\t\t\tABS(latitude - ?) < 0.00001
\t\t\t\tAND ABS(longitude - ?) < 0.00001
SQL;
if ($stmt_search = $db->prepare($sql_find)) {
/* bind parameters for markers */
$stmt_search->bind_param("dd", $latitude_search, $longitude_search);
$stmt_search->bind_result($search_id, $search_name);
} else {
echo htmlspecialchars($db->error);
}
$sql_insert = <<<SQL
\t\t\tINSERT INTO
\t\t\t\tplaces
\t\t\t\t(
\t\t\t\t\tname,
\t\t\t\t\tlatitude,
\t\t\t\t\tlongitude,
\t\t\t\t\tregion,
\t\t\t\t\tcategory,
\t\t\t\t\temail,
\t\t\t\t\ttelephone,
\t\t\t\t\taddress,
\t\t\t\t\tpostcode,
\t\t\t\t\twebsite,
\t\t\t\t\tentry_rates,
\t\t\t\t\topening_times,
\t\t\t\t\trating,
\t\t\t\t\tmore_info,
\t\t\t\t\tfacilities,
\t\t\t\t\tdisabled_facilities,
\t\t\t\t\tgood_stuff,
\t\t\t\t\tbad_stuff)
\t\t\tVALUES
\t\t\t\t(
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?,
\t\t\t\t\t?
\t\t\t\t)
SQL;
if ($stmt_insert = $db->prepare($sql_insert)) {
/* bind parameters for markers */
$stmt_insert->bind_param("sddsssssssssdsssss", $name, $latitude, $longitude, $region, $category, $email, $telephone, $address, $postcode, $website, $entry_rates, $opening_times, $rating, $more_info, $facilities, $disabled_facilities, $good_stuff, $bad_stuff);
// echo 'New record inserted<br><br>';
} else {
echo htmlspecialchars($db->error);
}
$sql_update = <<<SQL
\t\t\tUPDATE
\t\t\t\tplaces
\t\t\tSET
\t\t\t\tname = ?,
\t\t\t\tlatitude = ?,
\t\t\t\tlongitude = ?,
\t\t\t\tregion = ?,
\t\t\t\tcategory = ?,
\t\t\t\temail = ?,
\t\t\t\ttelephone = ?,
\t\t\t\taddress = ?,
\t\t\t\tpostcode = ?,
\t\t\t\twebsite = ?,
\t\t\t\tentry_rates = ?,
\t\t\t\topening_times = ?,
\t\t\t\trating = ?,
\t\t\t\tmore_info = ?,
\t\t\t\tfacilities = ?,
\t\t\t\tdisabled_facilities = ?,
\t\t\t\tgood_stuff = ?,
\t\t\t\tbad_stuff = ?
\t\t\tWHERE
\t\t\t\tid = ?
SQL;
if ($stmt_update = $db->prepare($sql_update)) {
$stmt_update->bind_param("sddsssssssssdsssssi", $name, $latitude, $longitude, $region, $category, $email, $telephone, $address, $postcode, $website, $entry_rates, $opening_times, $rating, $more_info, $facilities, $disabled_facilities, $good_stuff, $bad_stuff, $update_id);
// echo 'New record inserted<br><br>';
} else {
echo htmlspecialchars($db->error);
}
$handle = fopen($file, 'r');
$count = 0;
$inserted = 0;
$updated = 0;
$skipped = 0;
$header = fgetcsv($handle);
foreach ($header as $index => $field_name) {
$slug = str_replace('.', '', str_replace(' ', '_', strtolower($field_name)));
$var_name = "csv_{$slug}";
${$var_name} = $index;
}
// print_r(get_defined_vars());
// exit;
while ($record = fgetcsv($handle)) {
if ($count == 0) {
$count++;
continue;
}
$name = StripSpace($record[$csv_name]);
$latitude = StripSpace($record[$csv_latitude]);
$longitude = StripSpace($record[$csv_longitude]);
if ($name == '' || $latitude == '' || $longitude == '') {
if ($name != '') {
echo "skipped: {$name},{$latitude},{$longitude}<br>";
$skipped++;
}
continue;
}
$email = Nullable(StripSpace($record[$csv_email]), true);
$telephone = CleanUp($record[$csv_telephone]);
$address = CleanUp($record[$csv_address]);
$postcode = StripSpace($record[$csv_postcode]);
$website = StripSpace($record[$csv_website]);
$entry_rates = CleanUp($record[$csv_entry_rates]);
$opening_times = Nullable(CleanUp($record[$csv_opening_times]), true);
$rating = Nullable(StripSpace($record[$csv_rating]));
$more_info = StripSpace($record[$csv_more_info]);
$facilities = StripSpace($record[$csv_facilities]);
$disabled_facilities = StripSpace($record[$csv_disabled_facilities]);
$good_stuff = StripSpace($record[$csv_good_stuff]);
$bad_stuff = StripSpace($record[$csv_bad_stuff]);
$categories = array();
for ($index = $csv_indoor; $index <= $csv_free + 30; $index++) {
if (isset($record[$index]) && $record[$index] != '') {
$categories[] = $record[$index];
}
}
$category = CleanUp(implode(',', $categories));
// echo "latlong:$latitude $longitude<br><br>";
$latitude_search = (double) $latitude;
$longitude_search = (double) $longitude;
$stmt_search->execute();
$match_id = 0;
$found_count = 0;
while ($stmt_search->fetch()) {
if ($search_name == $name) {
if ($match_id == 0) {
$match_id = $search_id;
}
}
$found_count++;
}
if ($found_count == 0) {
$inserted++;
$stmt_insert->execute();
echo "inserted: {$name},{$latitude},{$longitude}<br>";
echo $stmt_insert->error;
} elseif ($found_count == 1) {
$updated++;
if ($match_id != 0) {
$update_id = $match_id;
$stmt_update->execute();
}
echo "updated (location match): {$update_id}, {$name}, {$latitude}, {$longitude}<br>";
} else {
if ($match_id != 0) {
$updated++;
$update_id = $match_id;
$stmt_update->execute();
echo "updated (name match): {$update_id}, {$name}, {$latitude}, {$longitude}<br>";
} else {
echo "skipped: {$name},{$latitude},{$longitude}<br>";
$skipped++;
}
}
$count++;
}
echo '<br><br>';
echo $inserted . ' records were inserted<br>';
echo $updated . ' records were updated<br>';
echo $skipped . ' records were skipped<br>';
}
