public static function batchGeocode($max_records_to_geocode)
{
$count = 0;
$cached_count = 0;
$hcps = DB::table(static::$table)->where('geo_cache_id', null)->take(5000)->get();
$hcps_not_geocoded = [];
foreach ($hcps as $hcp) {
Log::debug("HCP ID:" . $hcp->hcp_id);
list($geo, $cached, $status) = GeoCache::findByAddressOrCreateWithNewnessIndicator(Hcp::getGeocodeAddress($hcp));
if ($status == 'OK' || $status == 'LOCALLY_CACHED') {
HcpImport::update($hcp->hcp_id, ['lat' => $geo->lat, 'lng' => $geo->lng, 'geo_cache_id' => $geo->id, 'geo_address' => $geo->address]);
Log::debug("Geocode OK:", ['hcp_id' => $hcp->hcp_id, 'status' => $status]);
} else {
Log::error("Geocode Error:", ['hcp_id' => $hcp->hcp_id, 'address' => Hcp::getGeocodeAddress($hcp), 'status' => $status]);
$hcps_not_geocoded[] = $hcp;
}
if (!$cached) {
Log::debug("{$count} new geocodes");
$count++;
} else {
Log::debug("{$cached_count} cached geocodes");
$cached_count++;
}
if ($count > $max_records_to_geocode) {
break;
}
}
return $hcps_not_geocoded;
}
/**
* @return \Illuminate\View\View
*/
public function import()
{
$import_path = "/var/www/vhosts/dev1/docroot/import/";
// it appears there are some unexpected commas in the data so csv is difficult to fully parse
//$import_file = "bup_providers_data.csv";
// using pipe delimeted works better
$import_file = "bup_providers_data-pipe.txt";
$terminator = "|";
$file = $import_path . $import_file;
$import_statement = "LOAD DATA INFILE '{$file}' INTO TABLE " . HcpImport::getTable() . " FIELDS TERMINATED BY '{$terminator}' OPTIONALLY ENCLOSED BY '\"'\nLINES TERMINATED BY '\n' IGNORE 2 LINES (first_name,m_name,last_name,suffix,address_line1,address_line2,city,state,zip_code,phone)";
// import statement:
// LOAD DATA INFILE '/var/www/vhosts/dev1/docroot/import/bup_providers_data-pipe.txt' INTO TABLE hcp_import FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' LINES TERMINATED BY '\n' IGNORE 2 LINES (first_name,m_name,last_name,suffix,address_line1,address_line2,city,state,zip_code,phone)
// fails from PHP due to some driver/buffering issue, haven't found a solution yet.
// Field order from SAMSHA csv
// First Name,Middle Name,Last Name,Suffix,Address Line 1,Address Line2,City,State,Zip Code,Phone
DB::statement($import_statement);
$view_data = ['file' => $file, 'sql' => $import_statement];
return view('data.import', ['data' => $view_data]);
}
