| Class | Results::ResultsFile |
| In: |
lib/results/results_file.rb
|
| Parent: | Object |
Import Excel results file
Result time limited to hundredths of seconds
Notes example: Senior Men Pro 1/2 | Field size: 79 riders | Laps: 2
Set DEBUG_RESULTS to Toggle expensive debug logging. E.g., DEBUG_RESULTS=yes ./script/server
| COLUMN_MAP | = | { "placing" => "place", ";place" => "place", "#" => "number", "wsba#" => "number", "rider_#" => "number", 'racing_age' => 'age', 'age' => 'age', "gender" => "gender", "barcategory" => "parent", "bar_category" => "parent", "category.name" => "category_name", "categories" => "category_name", "category" => "category_name", "cat." => "category_name", "class" => "category_class", # Example: A for Masters A "first" => "first_name", "firstname" => "first_name", "person.first_name" => "first_name", "person" => "name", "lane" => "category_name", "last" => "last_name", "lastname" => "last_name", "person.last_name" => "last_name", "team" => "team_name", "team.name" => "team_name", "bib_#" => "number", "obra_number" => "number", "oregoncup" => "oregon_cup", "membership_#" => "license", "membership" => "license", "license_#" => "license", "club/team" => "team_name", "hometown" => 'city', "stage_time" => "time", "st" => "time", "stop_time" => "time", "bonus" => "time_bonus_penalty", "penalty" => "time_bonus_penalty", "stage_+_penalty" => "time_total", "time" => "time", "time_total" => "time_total", "total_time" => "time_total", "down" => "time_gap_to_leader", "gap" => "time_gap_to_leader", "delta_time" => "time_gap_to_leader", "pts" => "points", "sex" => "gender" |
| column_indexes | [RW] | |
| columns | [RW] | |
| custom_columns | [RW] | All custom columns in file |
| event | [RW] | |
| race_custom_columns | [RW] | Custom columns just for Race |
| rows | [RW] | |
| source | [RW] |
# File lib/results/results_file.rb, line 75
75: def initialize(source, event)
76: self.column_indexes = nil
77: self.event = event
78: self.custom_columns = Set.new
79: self.race_custom_columns = Set.new
80: self.source = source
81: end
# File lib/results/results_file.rb, line 220
220: def construct_usac_category(row)
221: #category_name and gender should always be populated.
222: #juniors, and conceivably masters, may be split by age group in which case the age column should
223: #contain the age range. otherwise it may be empty or contain an individual racer's age.
224: #The end result should look like "Junior Women 13-14" or "Junior Men"
225: #category_class may or may not be populated
226: #e.g. "Master B Men" or "Cat4 Female"
227: if row[:age].present? && /\d+-\d+/ =~ row[:age].to_s
228: return ((row[:category_name].to_s + " " + row[:category_class].to_s + " " + row[:gender].to_s + " " + row[:age].to_s)).squeeze(" ").strip
229: else
230: return ((row[:category_name].to_s + " " + row[:category_class].to_s + " " + row[:gender].to_s)).squeeze(" ").strip
231: end
232: end
Create Hash of normalized column name indexes Convert column names to lowercase and underscore. Use COLUMN_MAP to normalize.
Example: Place, Num, First Name { :place => 0, :number => 1, :first_name => 2 }
# File lib/results/results_file.rb, line 145
145: def create_columns(spreadsheet_row)
146: self.column_indexes = Hash.new
147: self.columns = []
148: spreadsheet_row.each_with_index do |cell, index|
149: cell_string = cell.to_s
150: if index == 0 && cell_string.blank?
151: column_indexes[:place] = 0
152: elsif cell_string.present?
153: cell_string.strip!
154: cell_string.gsub!(/^"/, '')
155: cell_string.gsub!(/"$/, '')
156: cell_string = cell_string.downcase.underscore
157: cell_string.gsub!(" ", "_")
158: cell_string = COLUMN_MAP[cell_string] if COLUMN_MAP[cell_string]
159: if cell_string.present?
160: if usac_results_format?
161: if Race::RESULT.respond_to?(cell_string.to_sym)
162: column_indexes[cell_string.to_sym] = index
163: self.columns << cell_string
164: end
165: else
166: column_indexes[cell_string.to_sym] = index
167: self.columns << cell_string
168: if !Race::RESULT.respond_to?(cell_string.to_sym)
169: self.custom_columns << cell_string
170: self.race_custom_columns << cell_string
171: end
172: end
173: end
174: end
175: end
176:
177: Rails.logger.debug("Results::ResultsFile #{Time.zone.now} Create column indexes #{self.column_indexes.inspect}") if debug?
178: self.column_indexes
179: end
# File lib/results/results_file.rb, line 244
244: def create_result(row, race)
245: if race
246: result = race.results.build(result_attributes(row, race))
247: result.updated_by = @event.name
248:
249: if row.same_time?
250: result.time = row.previous.result.time
251: end
252:
253: if result.place.to_i > 0
254: result.place = result.place.to_i
255: elsif result.place.present?
256: result.place = result.place.upcase rescue result.place
257: elsif row.previous[:place].present? && row.previous[:place].to_i == 0
258: result.place = row.previous[:place]
259: end
260:
261: # USAC format input may contain an age range in the age column for juniors.
262: if row[:age].present? && /\d+-\d+/ =~ row[:age].to_s
263: result.age = nil
264: result.age_group = row[:age]
265: end
266:
267: result.cleanup
268: result.save!
269: row.result = result
270: Rails.logger.debug("Results::ResultsFile #{Time.zone.now} create result #{race} #{result.place}") if debug?
271: else
272: # TODO Maybe a hard exception or error would be better?
273: Rails.logger.warn("No race. Skip.")
274: end
275: end
# File lib/results/results_file.rb, line 113
113: def create_rows(worksheet)
114: # Need all rows. Decorate them before inspecting them.
115: # Drop empty ones
116: self.rows = []
117: previous_row = nil
118: worksheet.each do |spreadsheet_row|
119: if debug?
120: Rails.logger.debug("Results::ResultsFile #{Time.zone.now} row #{spreadsheet_row.to_a.join(', ')}")
121: spreadsheet_row.each_with_index do |cell, index|
122: Rails.logger.debug("number_format pattern to_s to_f #{spreadsheet_row.format(index).number_format} #{spreadsheet_row.format(index).pattern} #{cell.to_s} #{cell.to_f if cell.respond_to?(:to_f)} #{cell.class}")
123: end
124: end
125: row = Results::Row.new(spreadsheet_row, column_indexes, usac_results_format?)
126: unless row.blank?
127: if column_indexes.nil?
128: create_columns(spreadsheet_row)
129: else
130: row.previous = previous_row
131: previous_row.next = row if previous_row
132: rows << row
133: previous_row = row
134: end
135: end
136: end
137: end
# File lib/results/results_file.rb, line 301
301: def debug?
302: ENV["DEBUG_RESULTS"].present? && Rails.logger.debug?
303: end
# File lib/results/results_file.rb, line 200
200: def find_or_create_race(row)
201: if usac_results_format?
202: category = Category.find_or_create_by_name(construct_usac_category(row))
203: else
204: category = Category.find_or_create_by_name(row.first)
205: end
206: race = event.races.detect { |race| race.category == category }
207: if race
208: race.results.clear
209: else
210: race = event.races.build(:category => category, :notes => row.notes)
211: end
212: race.result_columns = columns
213: race.custom_columns = race_custom_columns.to_a
214: race_custom_columns = Set.new
215: race.save!
216: Rails.logger.info("Results::ResultsFile #{Time.zone.now} create race #{category}")
217: race
218: end
See trac.butlerpress.com/racing_on_rails/wiki/SampleImportFiles for format details and examples.
# File lib/results/results_file.rb, line 84
84: def import
85: Rails.logger.info("Results::ResultsFile #{Time.zone.now} import")
86:
87: Event.transaction do
88: event.disable_notification!
89: book = Spreadsheet.open(source.path)
90: book.worksheets.each do |worksheet|
91: race = nil
92: create_rows(worksheet)
93:
94: rows.each do |row|
95: Rails.logger.debug("Results::ResultsFile #{Time.zone.now} row #{row.spreadsheet_row.to_a.join(', ')}") if debug?
96: if race?(row)
97: race = find_or_create_race(row)
98: # This row is also a result. I.e., no separate race header row.
99: if usac_results_format?
100: create_result(row, race)
101: end
102: elsif result?(row)
103: create_result(row, race)
104: end
105: end
106: end
107: event.enable_notification!
108: CombinedTimeTrialResults.create_or_destroy_for!(event)
109: end
110: Rails.logger.info("Results::ResultsFile #{Time.zone.now} import done")
111: end
# File lib/results/results_file.rb, line 181
181: def race?(row)
182: if usac_results_format?
183: #new race when one of the key fields changes: category, gender, class or age if age is a range
184: #i was looking for place = 1 but it is possible that all in race were dq or dnf or dns
185: return false if column_indexes.nil? || row.blank?
186: return true if row.previous.blank?
187: #break if age is a range and it has changed
188: if row[:age].present? && /\d+-\d+/ =~ row[:age].to_s
189: return true unless row[:age] == row.previous[:age]
190: end
191: return false if row[:category_name] == row.previous[:category_name] && row[:gender] == row.previous[:gender] && row[:category_class] == row.previous[:category_class]
192: return true
193: else
194: return false if column_indexes.nil? || row.last? || row.blank? || (row.next && row.next.blank?)
195: return false if row.place && row.place.to_i != 0
196: row.next && row.next.place && row.next.place.to_i == 1
197: end
198: end
# File lib/results/results_file.rb, line 234
234: def result?(row)
235: return false unless column_indexes
236: return false if row.blank?
237: return true if row.place.present? || row[:number].present? || row[:license].present? || row[:team_name].present?
238: if !(row[:first_name].blank? && row[:last_name].blank? && row[:name].blank?)
239: return true
240: end
241: false
242: end
# File lib/results/results_file.rb, line 277
277: def result_attributes(row, race)
278: attributes = row.to_hash.dup
279: custom_attributes = {}
280: attributes.delete_if do |key, value|
281: if race.custom_columns.include?(key.to_s)
282: custom_attributes[key] = case value
283: when Time
284: value.strftime "%H:%M:%S"
285: else
286: value
287: end
288: true
289: else
290: false
291: end
292: end
293: attributes.merge!(:custom_attributes => custom_attributes)
294: attributes
295: end