import_venues.php 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406
  1. <?php /** @noinspection PhpUnhandledExceptionInspection */
  2. /**
  3. * Imports venues from a CSV file for a customer
  4. *
  5. * Each location MUST have at least:
  6. *
  7. * 1. Internal ID
  8. * 2. City
  9. * 3. Postal Code
  10. */
  11. use Carbon\CarbonImmutable;
  12. require "../initialise.php";
  13. if($argc < 3) {
  14. echo sprintf("Usage: php %s [PATH] [CUSTOMER ID]\n", basename(__FILE__));
  15. exit(0);
  16. }
  17. $script = array_shift($argv);
  18. $path = array_shift($argv);
  19. $customer_id = array_shift($argv);
  20. $dry_run = false;
  21. $archive_missing = false;
  22. $region_code = "GB";
  23. while($argv) {
  24. $argument = array_shift($argv);
  25. switch($argument) {
  26. case "--archive-missing":
  27. echo "Archive missing ON\n";
  28. $archive_missing = true;
  29. break;
  30. case "--region-code":
  31. $region_code = array_shift($argv);
  32. break;
  33. case "--dry-run":
  34. $dry_run = true;
  35. break;
  36. default:
  37. throw new RuntimeException("Invalid argument: " . $argument);
  38. }
  39. }
  40. if(!file_exists($path)) {
  41. echo $path . " not found.\n";
  42. exit(1);
  43. }
  44. $customer = Customer::get($customer_id);
  45. if(!$customer) {
  46. echo "Customer not found.\n";
  47. exit(1);
  48. }
  49. $missing_locations = [];
  50. foreach($customer->getLocations() as $location) {
  51. $missing_locations[$location->id] = $location;
  52. }
  53. echo $customer->name . "\n";
  54. echo sprintf("Region Code: %s\n", $region_code);
  55. if($dry_run) {
  56. echo "Dry run\n";
  57. }
  58. $confirm = trim(readline("Confirm Y/N: "));
  59. if($confirm !== "Y") {
  60. echo "Aborted.\n";
  61. exit(0);
  62. }
  63. $handle = fopen($path, "rb");
  64. date_default_timezone_set("UTC");
  65. for($i = 0; $data = fgetcsv($handle); $i++) {
  66. $row_number = $i + 1;
  67. $data = array_map("trim", $data);
  68. if($i === 0) {
  69. // Header
  70. foreach($data as $index => $field_name) {
  71. switch($field_name) {
  72. case "Internal ID": $internal_id_column = $index; break;
  73. case "Name": $name_column = $index; break;
  74. case "Legal Entity Name": $legal_entity_name_column = $index; break;
  75. case "Bar Name": $bar_name_column = $index; break;
  76. case "Address": $address_column = $index; break;
  77. case "Address 1": $address_1_column = $index; break;
  78. case "Address 2": $address_2_column = $index; break;
  79. case "City": $city_column = $index; break;
  80. case "County": $county_column = $index; break;
  81. case "Postcode":
  82. case "Post Code":
  83. case "Postal Code": $postal_code_column = $index; break;
  84. case "State": $state_column = $index; break;
  85. case "Country": $country_column = $index; break;
  86. case "Email": $email_column = $index; break;
  87. case "Phone": $phone_column = $index; break;
  88. case "Opening Hours": $opening_hours_column = $index; break;
  89. case "Closing Hours": $closing_hours_column = $index; break;
  90. case "Shop Left": $shop_left_column = $index; break;
  91. case "Shop Right": $shop_right_column = $index; break;
  92. case "Brand": $brand_column = $index; break;
  93. case "Region": $region_column = $index; break;
  94. case "Region Name": $region_name_column = $index; break;
  95. case "Area Manager": $area_manager_column = $index; break;
  96. case "Area Manager Name": $area_manager_name_column = $index; break;
  97. case "Area Manager Email": $area_manager_email_column = $index; break;
  98. case "Operations Manager Name": $operations_manager_name_column = $index; break;
  99. case "Operations Manager Email": $operations_manager_email_column = $index; break;
  100. case "Gaming Manager Name": $gaming_manager_name_column = $index; break;
  101. case "Gaming Manager Email": $gaming_manager_email_column = $index; break;
  102. case "Senior Gaming Manager Name": $senior_gaming_manager_name_column = $index; break;
  103. case "Senior Gaming Manager Email": $senior_gaming_manager_email_column = $index; break;
  104. case "Archived": $archived_column = $index; break;
  105. case "Closed": $closed_column = $index; break;
  106. case "Latitude": $latitude_column = $index; break;
  107. case "Longitude": $longitude_column = $index; break;
  108. case "Machine Count": $machine_count_column = $index; break;
  109. case "Ignore":
  110. break;
  111. default:
  112. throw new RuntimeException("Unknown field name: {$field_name}");
  113. }
  114. }
  115. continue;
  116. }
  117. $internal_id = isset($internal_id_column) ? $data[$internal_id_column] : null;
  118. $city = isset($city_column) ? $data[$city_column] : null;
  119. $legal_entity_name = isset($legal_entity_name_column) ? $data[$legal_entity_name_column] : null;
  120. $bar_name = isset($bar_name_column) ? $data[$bar_name_column] : null;
  121. $region = isset($region_column) ? $data[$region_column] : null;
  122. $area_manager = isset($area_manager_column) ? $data[$area_manager_column] : null;
  123. $opening_hours = isset($opening_hours_column) ? $data[$opening_hours_column] : null;
  124. $closing_hours = isset($closing_hours_column) ? $data[$closing_hours_column] : null;
  125. $name = isset($name_column) ? $data[$name_column] : null;
  126. $address = isset($address_column) ? $data[$address_column] : null;
  127. $address_1 = isset($address_1_column) ? $data[$address_1_column] : null;
  128. $postal_code = isset($postal_code_column) ? $data[$postal_code_column] : null;
  129. $country = isset($country_column) ? $data[$country_column] : null;
  130. $email = isset($email_column) ? $data[$email_column] : null;
  131. $latitude = isset($latitude_column) ? $data[$latitude_column] : null;
  132. $longitude = isset($longitude_column) ? $data[$longitude_column] : null;
  133. $address_2 = isset($address_2_column) ? $data[$address_2_column] : null;
  134. $county = isset($county_column) ? $data[$county_column] : null;
  135. $phone = isset($phone_column) ? $data[$phone_column] : null;
  136. $archived = isset($archived_column) ? $data[$archived_column] : null;
  137. $closed = isset($closed_column) ? $data[$closed_column] : null;
  138. $brand = isset($brand_column) ? $data[$brand_column] : null;
  139. $area_manager_name = isset($area_manager_name_column) ? $data[$area_manager_name_column] : null;
  140. $area_manager_email = isset($area_manager_email_column) ? $data[$area_manager_email_column] : null;
  141. $region_name = isset($region_name_column) ? $data[$region_name_column] : null;
  142. $operations_manager_name = isset($operations_manager_name_column) ? $data[$operations_manager_name_column] : null;
  143. $operations_manager_email = isset($operations_manager_email_column) ? $data[$operations_manager_email_column] : null;
  144. $gaming_manager_name = isset($gaming_manager_name_column) ? $data[$gaming_manager_name_column] : null;
  145. $gaming_manager_email = isset($gaming_manager_email_column) ? $data[$gaming_manager_email_column] : null;
  146. $senior_gaming_manager_name = isset($senior_gaming_manager_name_column) ? $data[$senior_gaming_manager_name_column] : null;
  147. $senior_gaming_manager_email = isset($senior_gaming_manager_email_column) ? $data[$senior_gaming_manager_email_column] : null;
  148. $machine_count = isset($machine_count_column) ? (int) $data[$machine_count_column] : null;
  149. if($address) {
  150. // Try to determine address parts from address
  151. $parts = explode(",", $address);
  152. $parts = array_map("trim", $parts);
  153. $postal_code = array_pop($parts);
  154. $address_1 = array_shift($parts);
  155. $count = count($parts);
  156. if($count === 2) [$address_2, $city] = $parts;
  157. else if($count === 1) [$city] = $parts;
  158. }
  159. if(empty($internal_id)) {
  160. continue;
  161. }
  162. if(isset($address_2) && !isset($city)) {
  163. $city = $address_2;
  164. $address_2 = null;
  165. }
  166. if(empty($city) && empty($postal_code)) {
  167. echo sprintf("Skipping row %d as there is no city or postal code\n", $row_number);
  168. continue;
  169. }
  170. echo "Importing $internal_id - $address_1 ($city) ($bar_name) ... ";
  171. $location = Location::queryOne([
  172. "customer_id" => $customer_id,
  173. "internal_id" => $internal_id
  174. ]);
  175. if(!$location) {
  176. $location = new Location();
  177. $location->customer_id = $customer_id;
  178. $location->internal_id = $internal_id;
  179. $location->dts_created = DB::dts();
  180. }
  181. if(!empty($email)) $location->email = $email;
  182. if(!empty($latitude)) $location->latitude = $latitude;
  183. if(!empty($longitude)) $location->longitude = $longitude;
  184. if(!empty($address_2)) $location->address_2 = $address_2;
  185. if(!empty($county)) $location->county = $county;
  186. if(!empty($phone)) $location->phone = $phone;
  187. if(!empty($country)) $location->country = $country;
  188. if(!empty($brand)) $location->brand = $brand;
  189. if(!empty($area_manager_name)) $location->area_manager_name = $area_manager_name;
  190. if(!empty($area_manager_email)) $location->area_manager_email = $area_manager_email;
  191. if(!empty($region_name)) $location->region_name = $region_name;
  192. if(!empty($operations_manager_name)) $location->operations_manager_name = $operations_manager_name;
  193. if(!empty($operations_manager_email)) $location->operations_manager_email = $operations_manager_email;
  194. if(!empty($gaming_manager_name)) $location->gaming_manager_name = $gaming_manager_name;
  195. if(!empty($gaming_manager_email)) $location->gaming_manager_email = $gaming_manager_email;
  196. if(!empty($senior_gaming_manager_name)) $location->senior_gaming_manager_name = $senior_gaming_manager_name;
  197. if(!empty($senior_gaming_manager_email)) $location->senior_gaming_manager_email = $senior_gaming_manager_email;
  198. if(!empty($city)) $location->city = $city;
  199. if(!empty($legal_entity_name)) $location->legal_entity_name = $legal_entity_name;
  200. if(!empty($region)) $location->region = $region;
  201. if(!empty($area_manager)) $location->area_manager = $area_manager;
  202. if(!empty($opening_hours) || !empty($closing_hours)) {
  203. $hours_parts = [];
  204. if($opening_hours) $hours_parts[] = "Opening Hours: $opening_hours";
  205. if($closing_hours) $hours_parts[] = "Closing Hours: $closing_hours";
  206. $location->opening_hours = implode("\n", $hours_parts);
  207. }
  208. if(!empty($name)) {
  209. $location->name = $name;
  210. if($city && stripos($name, $city) === false) {
  211. $location->name .= " ($city)";
  212. }
  213. if($bar_name) {
  214. $location->name .= " ($bar_name)";
  215. }
  216. }
  217. if(!empty($address_1) && !empty($city)) {
  218. $location->address_1 = str_replace(", $city", "", $address_1);
  219. }
  220. if(!empty($postal_code)) {
  221. $location->postal_code = $postal_code;
  222. }
  223. if($archived !== null) {
  224. $location->archived = $archived ? 1 : 0;
  225. }
  226. if($closed !== null) {
  227. $location->closed = $closed ? 1 : 0;
  228. }
  229. if(!$dry_run && !$location->latitude) {
  230. for($attempt = 0; $attempt < 2; $attempt++) {
  231. switch($attempt) {
  232. default:
  233. case 0: $query = $location->getDisplayAddress(); break;
  234. case 1: $query = $postal_code; break;
  235. }
  236. try {
  237. $results = Geolocation::geocode($query, $region_code);
  238. if($results) {
  239. $result = $results[0];
  240. $location->latitude = $result->geometry->location->lat;
  241. $location->longitude = $result->geometry->location->lng;
  242. break;
  243. }
  244. }
  245. catch(Exception $e) {
  246. echo $e->getMessage() . ": $query ... ";
  247. }
  248. }
  249. }
  250. if($machine_count !== null) {
  251. $location->machine_count = $machine_count;
  252. }
  253. if($dry_run) {
  254. $success = true;
  255. }
  256. else {
  257. $success = $location->commit();
  258. }
  259. if(!$success) {
  260. throw new RuntimeException("Commit failed.");
  261. }
  262. if(isset($missing_locations[$location->id])) {
  263. unset($missing_locations[$location->id]);
  264. }
  265. echo "OK\n";
  266. }
  267. // Archive missing locations
  268. if($archive_missing) {
  269. foreach($missing_locations as $missing_location) {
  270. echo sprintf("Location#%s - %s (Internal ID: %s) archived.\n", $missing_location->id, $missing_location->name, $missing_location->internal_id);
  271. $missing_location->archived = 1;
  272. if(!$dry_run) {
  273. $missing_location->commit();
  274. }
  275. }
  276. }
  277. // Write CSV on missing locations in future projects
  278. $handle = fopen("tests_for_missing_locations.csv", "wb");
  279. fputcsv($handle, ["Project", "Location ID", "Location", "Internal ID"]);
  280. $missing_location_ids = array_column($missing_locations, "id");
  281. $dt_now = CarbonImmutable::now();
  282. $dt_threshold = $dt_now->addDays(90);
  283. $projects = Project::query([
  284. "customer_id" => $customer->id,
  285. "dts_start" => Constraint::between((string) $dt_now, (string) $dt_threshold)
  286. ], [
  287. "sort" => "dts_start"
  288. ]);
  289. foreach($projects as $project) {
  290. $tests = Test::query([
  291. "project_id" => $project->id,
  292. "location_id" => $missing_location_ids
  293. ]);
  294. foreach($tests as $test) {
  295. $location = $test->getLocation();
  296. 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";
  297. fputcsv($handle, [
  298. $project->name,
  299. $location->id,
  300. $location->name,
  301. $location->internal_id
  302. ]);
  303. }
  304. }
  305. echo "\n";
  306. echo "Missing locations that are not marked closed or archived\n";
  307. echo "\n";
  308. foreach($missing_locations as $missing_location) {
  309. if($missing_location->isArchived()) {
  310. continue;
  311. }
  312. if($missing_location->closed) {
  313. continue;
  314. }
  315. echo $missing_location->id . " - " . $missing_location->name . "\n";
  316. }