getLocations() as $location) { $missing_locations[$location->id] = $location; } echo $customer->name . "\n"; echo sprintf("Region Code: %s\n", $region_code); if($dry_run) { echo "Dry run\n"; } $confirm = trim(readline("Confirm Y/N: ")); if($confirm !== "Y") { echo "Aborted.\n"; exit(0); } $handle = fopen($path, "rb"); date_default_timezone_set("UTC"); for($i = 0; $data = fgetcsv($handle); $i++) { $row_number = $i + 1; $data = array_map("trim", $data); if($i === 0) { // Header foreach($data as $index => $field_name) { switch($field_name) { case "Internal ID": $internal_id_column = $index; break; case "Name": $name_column = $index; break; case "Legal Entity Name": $legal_entity_name_column = $index; break; case "Bar Name": $bar_name_column = $index; break; case "Address": $address_column = $index; break; case "Address 1": $address_1_column = $index; break; case "Address 2": $address_2_column = $index; break; case "City": $city_column = $index; break; case "County": $county_column = $index; break; case "Postcode": case "Post Code": case "Postal Code": $postal_code_column = $index; break; case "State": $state_column = $index; break; case "Country": $country_column = $index; break; case "Email": $email_column = $index; break; case "Phone": $phone_column = $index; break; case "Opening Hours": $opening_hours_column = $index; break; case "Closing Hours": $closing_hours_column = $index; break; case "Shop Left": $shop_left_column = $index; break; case "Shop Right": $shop_right_column = $index; break; case "Brand": $brand_column = $index; break; case "Region": $region_column = $index; break; case "Region Name": $region_name_column = $index; break; case "Area Manager": $area_manager_column = $index; break; case "Area Manager Name": $area_manager_name_column = $index; break; case "Area Manager Email": $area_manager_email_column = $index; break; case "Operations Manager Name": $operations_manager_name_column = $index; break; case "Operations Manager Email": $operations_manager_email_column = $index; break; case "Gaming Manager Name": $gaming_manager_name_column = $index; break; case "Gaming Manager Email": $gaming_manager_email_column = $index; break; case "Senior Gaming Manager Name": $senior_gaming_manager_name_column = $index; break; case "Senior Gaming Manager Email": $senior_gaming_manager_email_column = $index; break; case "Archived": $archived_column = $index; break; case "Closed": $closed_column = $index; break; case "Latitude": $latitude_column = $index; break; case "Longitude": $longitude_column = $index; break; case "Machine Count": $machine_count_column = $index; break; case "Ignore": break; default: throw new RuntimeException("Unknown field name: {$field_name}"); } } continue; } $internal_id = isset($internal_id_column) ? $data[$internal_id_column] : null; $city = isset($city_column) ? $data[$city_column] : null; $legal_entity_name = isset($legal_entity_name_column) ? $data[$legal_entity_name_column] : null; $bar_name = isset($bar_name_column) ? $data[$bar_name_column] : null; $region = isset($region_column) ? $data[$region_column] : null; $area_manager = isset($area_manager_column) ? $data[$area_manager_column] : null; $opening_hours = isset($opening_hours_column) ? $data[$opening_hours_column] : null; $closing_hours = isset($closing_hours_column) ? $data[$closing_hours_column] : null; $name = isset($name_column) ? $data[$name_column] : null; $address = isset($address_column) ? $data[$address_column] : null; $address_1 = isset($address_1_column) ? $data[$address_1_column] : null; $postal_code = isset($postal_code_column) ? $data[$postal_code_column] : null; $country = isset($country_column) ? $data[$country_column] : null; $email = isset($email_column) ? $data[$email_column] : null; $latitude = isset($latitude_column) ? $data[$latitude_column] : null; $longitude = isset($longitude_column) ? $data[$longitude_column] : null; $address_2 = isset($address_2_column) ? $data[$address_2_column] : null; $county = isset($county_column) ? $data[$county_column] : null; $phone = isset($phone_column) ? $data[$phone_column] : null; $archived = isset($archived_column) ? $data[$archived_column] : null; $closed = isset($closed_column) ? $data[$closed_column] : null; $brand = isset($brand_column) ? $data[$brand_column] : null; $area_manager_name = isset($area_manager_name_column) ? $data[$area_manager_name_column] : null; $area_manager_email = isset($area_manager_email_column) ? $data[$area_manager_email_column] : null; $region_name = isset($region_name_column) ? $data[$region_name_column] : null; $operations_manager_name = isset($operations_manager_name_column) ? $data[$operations_manager_name_column] : null; $operations_manager_email = isset($operations_manager_email_column) ? $data[$operations_manager_email_column] : null; $gaming_manager_name = isset($gaming_manager_name_column) ? $data[$gaming_manager_name_column] : null; $gaming_manager_email = isset($gaming_manager_email_column) ? $data[$gaming_manager_email_column] : null; $senior_gaming_manager_name = isset($senior_gaming_manager_name_column) ? $data[$senior_gaming_manager_name_column] : null; $senior_gaming_manager_email = isset($senior_gaming_manager_email_column) ? $data[$senior_gaming_manager_email_column] : null; $machine_count = isset($machine_count_column) ? (int) $data[$machine_count_column] : null; if($address) { // Try to determine address parts from address $parts = explode(",", $address); $parts = array_map("trim", $parts); $postal_code = array_pop($parts); $address_1 = array_shift($parts); $count = count($parts); if($count === 2) [$address_2, $city] = $parts; else if($count === 1) [$city] = $parts; } if(empty($internal_id)) { continue; } if(isset($address_2) && !isset($city)) { $city = $address_2; $address_2 = null; } if(empty($city) && empty($postal_code)) { echo sprintf("Skipping row %d as there is no city or postal code\n", $row_number); continue; } echo "Importing $internal_id - $address_1 ($city) ($bar_name) ... "; $location = Location::queryOne([ "customer_id" => $customer_id, "internal_id" => $internal_id ]); if(!$location) { $location = new Location(); $location->customer_id = $customer_id; $location->internal_id = $internal_id; $location->dts_created = DB::dts(); } if(!empty($email)) $location->email = $email; if(!empty($latitude)) $location->latitude = $latitude; if(!empty($longitude)) $location->longitude = $longitude; if(!empty($address_2)) $location->address_2 = $address_2; if(!empty($county)) $location->county = $county; if(!empty($phone)) $location->phone = $phone; if(!empty($country)) $location->country = $country; if(!empty($brand)) $location->brand = $brand; if(!empty($area_manager_name)) $location->area_manager_name = $area_manager_name; if(!empty($area_manager_email)) $location->area_manager_email = $area_manager_email; if(!empty($region_name)) $location->region_name = $region_name; if(!empty($operations_manager_name)) $location->operations_manager_name = $operations_manager_name; if(!empty($operations_manager_email)) $location->operations_manager_email = $operations_manager_email; if(!empty($gaming_manager_name)) $location->gaming_manager_name = $gaming_manager_name; if(!empty($gaming_manager_email)) $location->gaming_manager_email = $gaming_manager_email; if(!empty($senior_gaming_manager_name)) $location->senior_gaming_manager_name = $senior_gaming_manager_name; if(!empty($senior_gaming_manager_email)) $location->senior_gaming_manager_email = $senior_gaming_manager_email; if(!empty($city)) $location->city = $city; if(!empty($legal_entity_name)) $location->legal_entity_name = $legal_entity_name; if(!empty($region)) $location->region = $region; if(!empty($area_manager)) $location->area_manager = $area_manager; if(!empty($opening_hours) || !empty($closing_hours)) { $hours_parts = []; if($opening_hours) $hours_parts[] = "Opening Hours: $opening_hours"; if($closing_hours) $hours_parts[] = "Closing Hours: $closing_hours"; $location->opening_hours = implode("\n", $hours_parts); } if(!empty($name)) { $location->name = $name; if($city && stripos($name, $city) === false) { $location->name .= " ($city)"; } if($bar_name) { $location->name .= " ($bar_name)"; } } if(!empty($address_1) && !empty($city)) { $location->address_1 = str_replace(", $city", "", $address_1); } if(!empty($postal_code)) { $location->postal_code = $postal_code; } if($archived !== null) { $location->archived = $archived ? 1 : 0; } if($closed !== null) { $location->closed = $closed ? 1 : 0; } if(!$dry_run && !$location->latitude) { for($attempt = 0; $attempt < 2; $attempt++) { switch($attempt) { default: case 0: $query = $location->getDisplayAddress(); break; case 1: $query = $postal_code; break; } try { $results = Geolocation::geocode($query, $region_code); if($results) { $result = $results[0]; $location->latitude = $result->geometry->location->lat; $location->longitude = $result->geometry->location->lng; break; } } catch(Exception $e) { echo $e->getMessage() . ": $query ... "; } } } if($machine_count !== null) { $location->machine_count = $machine_count; } if($dry_run) { $success = true; } else { $success = $location->commit(); } if(!$success) { throw new RuntimeException("Commit failed."); } if(isset($missing_locations[$location->id])) { unset($missing_locations[$location->id]); } echo "OK\n"; } // Archive missing locations if($archive_missing) { foreach($missing_locations as $missing_location) { echo sprintf("Location#%s - %s (Internal ID: %s) archived.\n", $missing_location->id, $missing_location->name, $missing_location->internal_id); $missing_location->archived = 1; if(!$dry_run) { $missing_location->commit(); } } } // Write CSV on missing locations in future projects $handle = fopen("tests_for_missing_locations.csv", "wb"); fputcsv($handle, ["Project", "Location ID", "Location", "Internal ID"]); $missing_location_ids = array_column($missing_locations, "id"); $dt_now = CarbonImmutable::now(); $dt_threshold = $dt_now->addDays(90); $projects = Project::query([ "customer_id" => $customer->id, "dts_start" => Constraint::between((string) $dt_now, (string) $dt_threshold) ], [ "sort" => "dts_start" ]); foreach($projects as $project) { $tests = Test::query([ "project_id" => $project->id, "location_id" => $missing_location_ids ]); foreach($tests as $test) { $location = $test->getLocation(); echo "Test#{$test->id} for Location#{$location->id} - {$location->name} (Internal ID: {$location->internal_id}) in Project#{$project->id} - {$project->name} should be removed.\n"; fputcsv($handle, [ $project->name, $location->id, $location->name, $location->internal_id ]); } } echo "\n"; echo "Missing locations that are not marked closed or archived\n"; echo "\n"; foreach($missing_locations as $missing_location) { if($missing_location->isArchived()) { continue; } if($missing_location->closed) { continue; } echo $missing_location->id . " - " . $missing_location->name . "\n"; }