import_venues.php 7.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203
  1. <?php /** @noinspection PhpUnhandledExceptionInspection */
  2. require "../initialise.php";
  3. if($argc < 3) {
  4. die(sprintf("Usage: php %s [PATH] [CUSTOMER ID] [REGION CODE]\n", basename(__FILE__)));
  5. }
  6. $script = array_shift($argv);
  7. $path = array_shift($argv);
  8. $customer_id = array_shift($argv);
  9. $region_code = array_shift($argv);
  10. if(!file_exists($path)) die($path . " not found.");
  11. $customer = Customer::get($customer_id);
  12. if(!$customer) die("Customer not found.");
  13. $confirm = trim(readline($customer->name . ": Confirm Y/N: "));
  14. if($confirm != "Y") die("Aborted");
  15. $handle = fopen($path, "r");
  16. date_default_timezone_set("UTC");
  17. for($i = 0; $data = fgetcsv($handle); $i++) {
  18. $row_number = $i + 1;
  19. $data = array_map("trim", $data);
  20. if($i == 0) {
  21. // Header
  22. foreach($data as $index => $field_name) {
  23. switch($field_name) {
  24. case "Name": $name_column = $index; break;
  25. case "Legal Entity Name": $legal_entity_name_column = $index; break;
  26. case "Address": $address_column = $index; break;
  27. case "Address 1": $address_1_column = $index; break;
  28. case "Address 2": $address_2_column = $index; break;
  29. case "City": $city_column = $index; break;
  30. case "County": $county_column = $index; break;
  31. case "Postcode":
  32. case "Post Code":
  33. case "Postal Code": $postal_code_column = $index; break;
  34. case "State": $state_column = $index; break;
  35. case "Country": $country_column = $index; break;
  36. case "Email": $email_column = $index; break;
  37. case "Phone": $phone_column = $index; break;
  38. case "Opening Hours": $opening_hours_column = $index; break;
  39. case "Closing Hours": $closing_hours_column = $index; break;
  40. case "Shop Left": $shop_left_column = $index; break;
  41. case "Shop Right": $shop_right_column = $index; break;
  42. case "Brand": $brand_column = $index; break;
  43. case "Region": $region_column = $index; break;
  44. case "Region Name": $region_name_column = $index; break;
  45. case "Internal ID": $internal_id_column = $index; break;
  46. case "Area Manager": $area_manager_column = $index; break;
  47. case "Area Manager Name": $area_manager_name_column = $index; break;
  48. case "Area Manager Email": $area_manager_email_column = $index; break;
  49. case "Operations Manager Name": $operations_manager_name_column = $index; break;
  50. case "Closed": $closed_column = $index; break;
  51. case "Latitude": $latitude_column = $index; break;
  52. case "Longitude": $longitude_column = $index; break;
  53. case "Ignore":
  54. break;
  55. default:
  56. throw new Exception("Unknown field name: {$field_name}");
  57. }
  58. }
  59. continue;
  60. }
  61. $internal_id = isset($internal_id_column) ? $data[$internal_id_column] : null;
  62. $city = isset($city_column) ? $data[$city_column] : null;
  63. $legal_entity_name = isset($legal_entity_name_column) ? $data[$legal_entity_name_column] : null;
  64. $region = isset($region_column) ? $data[$region_column] : null;
  65. $area_manager = isset($area_manager_column) ? $data[$area_manager_column] : null;
  66. $opening_hours = isset($opening_hours_column) ? $data[$opening_hours_column] : null;
  67. $closing_hours = isset($closing_hours_column) ? $data[$closing_hours_column] : null;
  68. $name = isset($name_column) ? $data[$name_column] : null;
  69. $address = isset($address_column) ? $data[$address_column] : null;
  70. $address_1 = isset($address_1_column) ? $data[$address_1_column] : null;
  71. $postal_code = isset($postal_code_column) ? $data[$postal_code_column] : null;
  72. $email = isset($email_column) ? $data[$email_column] : null;
  73. $latitude = isset($latitude_column) ? $data[$latitude_column] : null;
  74. $longitude = isset($longitude_column) ? $data[$longitude_column] : null;
  75. $address_2 = isset($address_2_column) ? $data[$address_2_column] : null;
  76. $county = isset($county_column) ? $data[$county_column] : null;
  77. $phone = isset($phone_column) ? $data[$phone_column] : null;
  78. $brand = isset($brand_column) ? $data[$brand_column] : null;
  79. $area_manager_name = isset($area_manager_name_column) ? $data[$area_manager_name_column] : null;
  80. $area_manager_email = isset($area_manager_email_column) ? $data[$area_manager_email_column] : null;
  81. $region_name = isset($region_name_column) ? $data[$region_name_column] : null;
  82. $operations_manager_name = isset($operations_manager_name_column) ? $data[$operations_manager_name_column] : null;
  83. if($address) {
  84. // Try to determine address parts from address
  85. $parts = explode(",", $address);
  86. $parts = array_map("trim", $parts);
  87. $postal_code = array_pop($parts);
  88. $address_1 = array_shift($parts);
  89. $count = count($parts);
  90. if($count === 2) list($address_2, $city) = $parts;
  91. else if($count === 1) list($city) = $parts;
  92. }
  93. if(empty($internal_id)) continue;
  94. if(empty($city) && empty($postal_code)) {
  95. echo sprintf("Skipping row %d as there is no city or postal code\n", $row_number);
  96. continue;
  97. }
  98. echo "Importing $internal_id - $city ... ";
  99. $location = Location::queryOne([
  100. "customer_id" => $customer_id,
  101. "internal_id" => $internal_id
  102. ]);
  103. if(!$location) {
  104. $location = new Location;
  105. $location->customer_id = $customer_id;
  106. $location->internal_id = $internal_id;
  107. }
  108. if(!empty($email)) $location->email = $email;
  109. if(!empty($latitude)) $location->latitude = $latitude;
  110. if(!empty($longitude)) $location->longitude = $longitude;
  111. if(!empty($address_2)) $location->address_2 = $address_2;
  112. if(!empty($county)) $location->county = $county;
  113. if(!empty($phone)) $location->phone = $phone;
  114. if(!empty($brand)) $location->brand = $brand;
  115. if(!empty($area_manager_name)) $location->area_manager_name = $area_manager_name;
  116. if(!empty($area_manager_email)) $location->area_manager_email = $area_manager_email;
  117. if(!empty($region_name)) $location->region_name = $region_name;
  118. if(!empty($operations_manager_name)) $location->operations_manager_name = $operations_manager_name;
  119. if(!empty($city)) $location->city = $city;
  120. if(!empty($legal_entity_name)) $location->legal_entity_name = $legal_entity_name;
  121. if(!empty($region)) $location->region = $region;
  122. if(!empty($area_manager)) $location->area_manager = $area_manager;
  123. if(!empty($opening_hours) || !empty($closing_hours)) {
  124. $hours_parts = [];
  125. if($opening_hours) $hours_parts[] = "Opening Hours: $opening_hours";
  126. if($closing_hours) $hours_parts[] = "Closing Hours: $closing_hours";
  127. $location->opening_hours = implode("\n", $hours_parts);
  128. }
  129. if(!empty($name)) {
  130. $location->name = $name;
  131. if($city && stripos($name, $city) === false) {
  132. $location->name .= " ($city)";
  133. }
  134. }
  135. if(!empty($address_1) && !empty($city)) {
  136. $location->address_1 = str_replace(", $city", "", $address_1);
  137. }
  138. if(!empty($postal_code)) $location->postal_code = $postal_code;
  139. if($region_code && !$location->latitude) {
  140. for($attempt = 0; $attempt < 2; $attempt++) {
  141. switch($attempt) {
  142. case 0: $query = $location->getDisplayAddress(); break;
  143. case 1: $query = $postal_code; break;
  144. }
  145. try {
  146. $results = Geolocation::geocode($query, $region_code);
  147. if($results) {
  148. $result = $results[0];
  149. $location->latitude = $result->geometry->location->lat;
  150. $location->longitude = $result->geometry->location->lng;
  151. break;
  152. }
  153. }
  154. catch(Exception $e) {
  155. echo $e->getMessage() . ": $query ... ";
  156. }
  157. }
  158. }
  159. $success = $location->commit();
  160. if(!$success) throw new Exception("Commit failed.");
  161. echo "OK\n";
  162. }