Index: test/unit/trip_test.rb =================================================================== --- test/unit/trip_test.rb (revision 11258) +++ test/unit/trip_test.rb (revision 11375) @@ -1,115 +0,0 @@ -# To change this template, choose Tools | Templates -# and open the template in the editor. - -$:.unshift File.join(File.dirname(__FILE__),'..','lib') - -require 'test_helper' - -class TripTest < ActiveSupport::TestCase - - self.use_transactional_fixtures = false - - - def setup - Account.delete_all - Device.delete_all - Reading.delete_all - StopEvent.delete_all - TripEvent.delete_all - file = File.new("db_procs.sql") - file.readline - file.readline #skip 1st two lines of file - sql = file.read - sql.strip! - statements = sql.split(';;') - - statements.each {|stmt| - ActiveRecord::Base.connection.execute(stmt) - } - end - - context "A device" do - setup do - account = Factory.create(:account) - @device1 = Factory.create(:device, :gateway_name => "xirgo", :account => account) - @reading1 = Factory.create(:reading, :device => @device1, :speed => 0, :latitude =>1, :longitude =>2, :created_at => Time.now-250) - end - context "with an ignition off" do - setup {Factory.create(:reading, :device => @device1, :speed => 0, :latitude =>1, :longitude =>2, :created_at => Time.now-250, :ignition => 0)} - should "not create a trip" do - assert_equal 0, @device1.trip_events.size - end - end - context "With a new ignition on" do - setup {@reading1 = Factory.create(:reading, :device => @device1, :speed => 0, :latitude =>1, :longitude =>2, :created_at => Time.now-250, :ignition => 1)} - should "start a new trip" do - assert_equal 1, @device1.trip_events.size - end - end - context "with a duplicate ignition on" do - setup do - timestamp = Time.now-250 - Factory.create(:reading, :device => @device1, :speed => 0, :latitude =>1, :longitude =>2, :created_at => timestamp, :ignition => 1) - Factory.create(:reading, :device => @device1, :speed => 0, :latitude =>1, :longitude =>2, :created_at => timestamp, :ignition => 1) - end - should "only create one trip event" do - @device1.reload - assert_equal 1, @device1.trip_events.size - end - end - context "with a complete set of on...off readings" do - setup do - Factory.create(:reading, :device => @device1, :speed => 0, :ignition => 1, :latitude =>1, :longitude =>2, :created_at => Time.now-180) - Factory.create(:reading, :device => @device1, :speed => 10,:ignition => 1, :latitude =>1, :longitude =>2, :created_at => Time.now-150) - @stop = Factory.create(:reading, :device => @device1, :speed => 0, :ignition => 0,:latitude =>1, :longitude =>2, :created_at => Time.now) - end - should "create and close a trip event" do - @device1.reload - assert_equal 1, @device1.trip_events.size - assert_equal 3, @device1.trip_events[0].duration - assert_equal @stop.id, @device1.trip_events[0].reading_stop_id - end - end - context "reporting out of order readings" do - setup do - Factory.create(:reading, :device => @device1, :speed => 0, :ignition => 0, :latitude =>1, :longitude =>2, :created_at => Time.now-200) - Factory.create(:reading, :device => @device1, :speed => 10, :ignition => 1, :latitude =>1, :longitude =>2, :created_at => Time.now-150) - @start = Factory.create(:reading, :device => @device1, :speed => 10,:ignition => 1, :latitude =>1, :longitude =>2, :created_at => Time.now-180) - @stop = Factory.create(:reading, :device => @device1, :speed => 0, :ignition => 0, :latitude =>1, :longitude =>2, :created_at => Time.now-120) - end - should "create a single trip event" do - @device1.reload - assert_equal 1, @device1.trip_events.size - assert_equal 1, @device1.trip_events[0].duration - assert_equal @start.id, @device1.trip_events[0].reading_start_id - assert_equal @start.created_at.to_s, @device1.trip_events[0].created_at.to_s - assert_equal @stop.id, @device1.trip_events[0].reading_stop_id - end - end - context "with an open trip event" do - setup do - Factory.create(:trip_event, :device => @device1) - Factory.create(:reading, :device => @device1, :speed => 10,:ignition => 1, :latitude =>1, :longitude =>2, :created_at => Time.now-180) - end - should "not create a new trip" do - @device1.reload - assert_equal 1, @device1.trip_events.size - end - end - context "with a closed trip event and out of order readings" do - setup do - Factory.create(:reading, :device => @device1, :speed => 0,:ignition => 0, :latitude =>1, :longitude =>2, :created_at => Time.now-180) - @trip1_start_reading = Factory.create(:reading, :device => @device1, :speed => 0,:ignition => 1, :latitude =>1, :longitude =>2, :created_at => Time.now-170) - @trip2_start_reading = Factory.create(:reading, :device => @device1, :speed => 0,:ignition => 1, :latitude =>1, :longitude =>2, :created_at => Time.now-150) - Factory.create(:reading, :device => @device1, :speed => 0,:ignition => 0, :latitude =>1, :longitude =>2, :created_at => Time.now-160) - Factory.create(:reading, :device => @device1, :speed => 0,:ignition => 1, :latitude =>1, :longitude =>2, :created_at => Time.now-140) - end - should "create two trip events" do - @device1.reload - assert_equal 2, @device1.trip_events.size - assert_equal @trip1_start_reading.id, @device1.trip_events[1].reading_start.id - assert_equal @trip2_start_reading.id, @device1.trip_events[0].reading_start.id - end - end - end -end Index: test/unit/spanning_event_hit_test.rb =================================================================== --- test/unit/spanning_event_hit_test.rb (revision 0) +++ test/unit/spanning_event_hit_test.rb (revision 11375) @@ -0,0 +1,64 @@ +require 'test_helper' + +class SpanningEventHitTest < ActiveSupport::TestCase + fixtures :spanning_event_hits, :readings + +# def setup +# file = File.new("#{RAILS_ROOT}/db_procs.sql") +# file.readline +# file.readline #skip 1st two lines of file +# sql = file.read +# sql.strip! +# statements = sql.split(';;') +# +# statements.each {|stmt| +# ActiveRecord::Base.connection.execute(stmt) +# } +# end + + def test_spanning_event_hits + SpanningEventHit.process_queue(true) + + assert_equal 'delayed', readings(:readings_10000).note + assert_equal 'delayed normal', readings(:readings_10000).event_type + assert_equal 'other', readings(:readings_10001).note + assert_equal 'GPS Lock', readings(:readings_10001).event_type + assert_equal true, readings(:readings_10002).ignition + assert_equal nil, readings(:readings_10003).ignition + end + + def test_spanning_event_hit_creation + + Reading.delete_all + + file = File.new("#{RAILS_ROOT}/db_procs.sql") + file.readline + file.readline #skip 1st two lines of file + sql = file.read + sql.strip! + statements = sql.split(';;') + + statements.each {|stmt| + ActiveRecord::Base.connection.execute(stmt) + } + + reading1 = Reading.create(:event_type => 'normal', :ignition => true, :device_id => 2, :speed => 20.0, :created_at => 10.seconds.ago) + reading2 = Reading.create(:event_type => 'normal', :ignition => true, :device_id => 2, :speed => 20.0, :created_at => reading1.created_at) + reading3 = Reading.create(:event_type => 'engine_on', :ignition => true, :device_id => 2, :speed => 20.0, :created_at => 15.seconds.ago) + reading4 = Reading.create(:event_type => 'engine_on', :ignition => true, :device_id => 2, :speed => 20.0, :created_at => 500.seconds.ago) + reading5 = Reading.create(:event_type => 'normal', :ignition => true, :device_id => 2, :speed => 20.0, :created_at => 9.seconds.ago) + reading6 = Reading.create(:event_type => 'GPS Lock', :ignition => true, :device_id => 2, :speed => 20.0, :created_at => 9.seconds.ago) + reading7 = Reading.create(:event_type => 'normal', :ignition => true, :device_id => 2, :speed => 20.0, :created_at => 5.seconds.ago) + +#This one behaves differently in Dev vs. Test + assert_equal 'alone', SpanningEventHit.find(reading1.id).event_type + assert_equal 'duplicate', SpanningEventHit.find(reading2.id).event_type + assert_equal 'tardy', SpanningEventHit.find(reading3.id).event_type + assert_equal 'delayed', SpanningEventHit.find(reading4.id).event_type + assert_nil SpanningEventHit.find_by_id(reading5.id) + assert_equal 'GPS Lock', SpanningEventHit.find(reading6.id).event_type + assert_equal 'other', SpanningEventHit.find(reading7).event_type + + end + +end Index: test/unit/suspect_event_test.rb =================================================================== --- test/unit/suspect_event_test.rb (revision 0) +++ test/unit/suspect_event_test.rb (revision 11375) @@ -0,0 +1,16 @@ +require 'test_helper' + +class SuspectEventTest < ActiveSupport::TestCase + fixtures :trip_events + + def test_suspect_events + TripEvent.identify_suspect_events(RAILS_DEFAULT_LOGGER) + + assert_equal 3, TripEvent.count(:conditions => {:suspect => true}) + + assert_equal true, trip_events(:suspect_overlapping_a).suspect + assert_equal true, trip_events(:suspect_overlapping_b).suspect + assert_equal true, trip_events(:suspect_overlapped_a).suspect + assert_equal false, trip_events(:suspect_overlapped_b).suspect + end +end Index: test/test_helper.rb =================================================================== --- test/test_helper.rb (revision 11258) +++ test/test_helper.rb (revision 11375) @@ -36,7 +36,10 @@ # fixtures :all # Add more helper methods to be used by all tests here... + def teardown + ActiveRecord::Base.connection.execute("DROP TRIGGER IF EXISTS trig_readings_after_insert") + end + end Webrat.configure do |config| Index: test/fixtures/readings.yml =================================================================== --- test/fixtures/readings.yml (revision 11258) +++ test/fixtures/readings.yml (revision 11375) @@ -1986,4 +1986,89 @@ created_at: <%= Time.now.to_s :db %> ignition: 1 gpio1: 1 + +readings_10000: + updated_at: <%= Time.now.to_s :db %> + latitude: 32.8491 + altitude: 182 + device_id: 9 + id: 10000 + notified: + note: + speed: 0 + longitude: -97.1706 + street: Yates Dr + place_name: Hurst + admin_name1: TX + geofence_event_type: exit + event_type: normal + direction: 0 + created_at: <%= Time.now.to_s :db %> + ignition: 1 + gpio1: 0 + gpio2: 0 + + +readings_10001: + updated_at: <%= Time.now.to_s :db %> + latitude: 32.8491 + altitude: 182 + device_id: 9 + id: 10001 + notified: + note: + speed: 0 + longitude: -97.1706 + street: Yates Dr + place_name: Hurst + admin_name1: TX + geofence_event_type: exit + event_type: GPS Lock + direction: 0 + created_at: <%= Time.now.to_s :db %> + ignition: 1 + gpio1: 0 + gpio2: 0 + +readings_10002: + updated_at: <%= Time.now.to_s :db %> + latitude: 32.8491 + altitude: 182 + device_id: 9 + id: 10002 + notified: + note: + speed: 23 + longitude: -97.1706 + street: Yates Dr + place_name: Hurst + admin_name1: TX + geofence_event_type: exit + event_type: Direction Change + direction: 0 + created_at: <%= Time.now.to_s :db %> + ignition: + gpio1: 0 + gpio2: 0 + +readings_10003: + updated_at: <%= Time.now.to_s :db %> + latitude: 32.8491 + altitude: 182 + device_id: 9 + id: 10003 + notified: + note: + speed: 0 + longitude: -97.1706 + street: Yates Dr + place_name: Hurst + admin_name1: TX + geofence_event_type: exit + event_type: Heartbeat + direction: 0 + created_at: <%= Time.now.to_s :db %> + ignition: + gpio1: 0 + gpio2: 0 Index: test/fixtures/trip_events.yml =================================================================== --- test/fixtures/trip_events.yml (revision 11258) +++ test/fixtures/trip_events.yml (revision 11375) @@ -3,13 +3,138 @@ device_id: 1 reading_start_id: 6 reading_stop_id: 13 - created_at: <%= Time.now.to_s :db %> + created_at: <%= 10.hours.ago.to_s :db %> duration: 9 two: id: 2 device_id: 1 reading_start_id: 1 reading_stop_id: 5 - created_at: <%= Time.now.to_s :db %> - duration: 3 \ No newline at end of file + created_at: <%= 9.5.hours.ago.to_s :db %> + duration: 3 + +# overlapping = B (unterminated) created during A +# A should be flagged +suspect_overlapping_a: + id: 3 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= 8.hours.ago.to_s :db %> + duration: 15 + +# this one is not newest so it should be flagged as unterminated +suspect_overlapping_b: + id: 4 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= (8.hours.ago + 5.minutes).to_s :db %> + duration: null + +# overlapped = B ended during A +# A should be flagged +suspect_overlapped_a: + id: 5 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= 7.hours.ago.to_s :db %> + duration: 10 + +suspect_overlapped_b: + id: 6 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= (7.hours.ago - 5.minutes).to_s :db %> + duration: 10 + +not_suspect_a: + id: 9 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= 5.hours.ago.to_s :db %> + duration: 2 + +not_suspect_b: + id: 10 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= (5.hours.ago + 3.minutes).to_s :db %> + duration: 2 + +not_suspect_c: + id: 11 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= (5.hours.ago + 6.minutes).to_s :db %> + duration: 2 + +not_suspect_d: + id: 12 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= (5.hours.ago + 9.minutes).to_s :db %> + duration: 2 + +not_suspect_e: + id: 13 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= (5.hours.ago + 12.minutes).to_s :db %> + duration: 2 + +not_suspect_f: + id: 14 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= (5.hours.ago + 15.minutes).to_s :db %> + duration: 2 + +not_suspect_g: + id: 15 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= (5.hours.ago + 18.minutes).to_s :db %> + duration: 2 + +not_suspect_h: + id: 16 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= (5.hours.ago + 21.minutes).to_s :db %> + duration: 2 + +not_suspect_i: + id: 17 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= (5.hours.ago + 24.minutes).to_s :db %> + duration: 2 + +not_suspect_j: + id: 18 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= (5.hours.ago + 27.minutes).to_s :db %> + duration: 2 + +not_suspect_k: + id: 19 + device_id: 2 + reading_start_id: 1 + reading_stop_id: 1 + created_at: <%= (5.hours.ago + 30.minutes).to_s :db %> + duration: 2 Index: test/fixtures/spanning_event_hits.yml =================================================================== --- test/fixtures/spanning_event_hits.yml (revision 0) +++ test/fixtures/spanning_event_hits.yml (revision 11375) @@ -0,0 +1,31 @@ +one: + id: 10000 + device_id: 9 + event_type: delayed + ignition: 1 + speed: 0 + created_at: <%= Time.now.to_s :db %> + +two: + id: 10001 + device_id: 9 + event_type: other + ignition: 1 + speed: 0 + created_at: <%= Time.now.to_s :db %> + +three: + id: 10002 + device_id: 9 + event_type: other + ignition: + speed: 0 + created_at: <%= Time.now.to_s :db %> + +four: + id: 10003 + device_id: 9 + event_type: other + ignition: + speed: + created_at: <%= Time.now.to_s :db %> Index: app/models/idle_event.rb =================================================================== --- app/models/idle_event.rb (revision 11258) +++ app/models/idle_event.rb (revision 11375) @@ -1,5 +1,6 @@ class IdleEvent < ActiveRecord::Base + extend SuspectEvent belongs_to :reading - belongs_to :device + belongs_to :device include ApplicationHelper end Index: app/models/spanning_event_state.rb =================================================================== --- app/models/spanning_event_state.rb (revision 0) +++ app/models/spanning_event_state.rb (revision 11375) @@ -0,0 +1,209 @@ +class SpanningEventState + + MIN_STOP_IDLE_MINUTES = 3 + MIN_STOP_IDLE_SECONDS = MIN_STOP_IDLE_MINUTES * 60 + MAX_MISSING_SECONDS = 60 * 60 * 2 # 2 hours + MAX_EXPIRED_SECONDS = 60 * 60 * 3 # 3 hours + + @@state_cache = nil + + def self.close_expired_trips + devices = Device.find_by_sql(" + SELECT d.id, d.recent_reading_id + FROM trip_events t + LEFT JOIN devices d ON t.device_id = d.id + LEFT JOIN readings r ON d.recent_reading_id = r.id + WHERE t.duration IS NULL + AND (t.suspect = 0 OR t.suspect IS NULL) + AND r.created_at < DATE_SUB(NOW(), INTERVAL #{MAX_EXPIRED_SECONDS} SECOND)") + for device in devices + state = get_by_device_id(device.id) + state.set_current_reading(device.latest_reading) + state.end_open_trip + end + end + + def self.get_by_reading(reading) + get_by_device_id(reading.device_id) + end + + def self.get_by_device_id(device_id) + state_cache[device_id] || new(device_id) + end + + def self.reset_cache + state_cache.values.each {| state | state.force_open_stop_and_idle} + @state_cache = nil + end + + def self.state_cache + @@state_cache ||= {} + end + + def initialize(device_id) + SpanningEventState.state_cache[@device_id = device_id] = self + end + + def consider_reading(reading) + return unless reading.created_at + + if (trip = find_last_open_trip) and (@current_reading ||= find_previous_reading(reading)) and reading.created_at - @current_reading.created_at > MAX_MISSING_SECONDS + if !(previous_reading = find_previous_trip_on_reading(trip,reading)) + end_open_trip + elsif previous_reading and (reading.created_at - previous_reading.created_at) > MAX_MISSING_SECONDS + @current_reading = previous_reading + end_open_trip + end + end + + @current_reading = reading + case @current_reading.event_type + when 'GPS Lock' + @current_reading.ignition ? begin_new_trip : end_open_trip(true) + when 'engine on' + begin_new_trip + when 'engine off' + end_open_trip + when 'idle' + ensure_open_idle + else + #NOTE: fix the occasional non-report of "ignition" by things like "Direction Change" + @current_reading.update_attribute(:ignition, true) if @current_reading.ignition.nil? and @current_reading.speed and @current_reading.speed.to_f > 0 + if @current_reading.ignition.nil? + # NOTE: do nothing -- may be a Heartbeat or other event that does NOT report ignition + elsif (trip = find_last_open_trip).nil? + begin_new_trip if @current_reading.ignition + elsif not @current_reading.ignition + end_open_trip + elsif @current_reading.speed + @current_reading.speed > 0 ? end_open_stop_and_idle(trip,@current_reading) : ensure_open_stop_and_idle + end + end + end + +#private + + def force_open_stop_and_idle + @last_open_stop.save! if find_last_open_stop + @last_open_idle.save! if find_last_open_idle + @last_open_stop,@last_open_idle = nil,nil + end + + def ensure_open_stop_and_idle + ensure_open_stop + ensure_open_idle + end + + def ensure_open_idle + @last_open_idle = ensure_open_event(IdleEvent,find_last_open_idle) || :nil + end + + def ensure_open_stop + @last_open_stop = ensure_open_event(StopEvent,find_last_open_stop) || :nil + end + + def ensure_open_event(klass,open_event) + return klass.new(:device_id => @device_id,:reading_id => @current_reading.id,:created_at => @current_reading.created_at,:latitude => @current_reading.latitude,:longitude => @current_reading.longitude) unless open_event + + open_event.save! if @current_reading.created_at - open_event.created_at > MIN_STOP_IDLE_SECONDS + + open_event + end + + def begin_new_trip + end_open_trip(true) if find_last_open_trip + + end_open_idle(nil,nil) # just to be sure... + + @last_open_trip = TripEvent.create!(:device_id => @device_id,:reading_start_id => @current_reading.id,:created_at => @current_reading.created_at) + + ensure_open_idle if @current_reading.speed == 0 + end_open_stop(@last_open_trip,@current_reading) if @current_reading.speed > 0 + end + + def end_open_trip(use_previous = false) + return end_open_idle(nil,nil) unless trip = find_last_open_trip + + closing_reading = use_previous ? find_previous_trip_on_reading(trip,@current_reading) : @current_reading + + duration = ((closing_reading.created_at - trip.created_at) / 60).round if closing_reading + trip.update_attributes!(:reading_stop_id => (closing_reading ? closing_reading.id : 0),:duration => duration,:suspect => (duration.nil? || duration <= 0)) + + end_open_idle(trip,closing_reading) + ensure_open_stop if @current_reading.speed == 0 + + @last_open_trip = :nil + end + + def end_open_stop_and_idle(trip,closing_reading) + end_open_stop(trip,closing_reading) if find_last_open_stop + end_open_idle(trip,closing_reading) if find_last_open_idle + end + + def end_open_idle(enclosing_trip,closing_reading) + return unless idle = find_last_open_idle + + end_open_event(idle,enclosing_trip,closing_reading) + + @last_open_idle = :nil + end + + def end_open_stop(enclosing_trip,closing_reading) + return unless stop = find_last_open_stop + + end_open_event(stop,enclosing_trip,closing_reading) + + @last_open_stop = :nil + end + + def end_open_event(open_event,enclosing_trip,closing_reading) + if enclosing_trip.nil? + open_event.update_attributes!(:suspect => true) unless open_event.new_record? + else + closing_reading ||= enclosing_trip.reading_stop + duration = ((closing_reading.created_at - open_event.created_at) / 60).round if closing_reading + suspect = duration.nil? || duration < MIN_STOP_IDLE_MINUTES + open_event.update_attributes!(:duration => duration,:suspect => suspect) unless open_event.new_record? and suspect + end + end + + def find_last_open_idle + @last_open_idle = find_last_open_event(IdleEvent,@last_open_idle) + @last_open_idle unless @last_open_idle == :nil + end + + def find_last_open_stop + @last_open_stop = find_last_open_event(StopEvent,@last_open_stop) + @last_open_stop unless @last_open_stop == :nil + end + + def find_last_open_trip + @last_open_trip = find_last_open_event(TripEvent,@last_open_trip) + @last_open_trip unless @last_open_trip == :nil + end + + def find_last_open_event(klass,previous) + return previous if previous + + results = klass.all(:conditions => ['device_id = ? and created_at is not null and duration is null and (suspect = 0 or suspect is null)',@device_id],:order => 'created_at desc') + results.pop.update_attributes!(:suspect => true) while results.size > 1 # NOTE: fix suspects NOW! + + results[0] || :nil + end + + def find_previous_reading(reading) + first_minimal_reading(:conditions => ['device_id = ? and id < ? and created_at <= ?',@device_id,reading.id,reading.created_at],:order => 'created_at desc') + end + + def find_previous_trip_on_reading(trip,reading) + first_minimal_reading(:conditions => ['device_id = ? and created_at between ? and ? and ignition = 1',@device_id,trip.created_at,reading.created_at.advance(:seconds => -1)],:order => 'created_at desc') if trip + end + + def first_minimal_reading(options) + Reading.first({:select => SpanningEventHit::MINIMAL_READING_COLUMNS,:readonly => true}.update(options)) + end + + def set_current_reading(reading) + @current_reading = reading + end +end \ No newline at end of file Index: app/models/trip_event.rb =================================================================== --- app/models/trip_event.rb (revision 11258) +++ app/models/trip_event.rb (revision 11375) @@ -1,4 +1,5 @@ class TripEvent < ActiveRecord::Base + extend SuspectEvent belongs_to :device belongs_to :reading_start,:class_name => "Reading" belongs_to :reading_stop,:class_name => "Reading" Index: app/models/spanning_event_hit.rb =================================================================== --- app/models/spanning_event_hit.rb (revision 0) +++ app/models/spanning_event_hit.rb (revision 11375) @@ -0,0 +1,34 @@ +class SpanningEventHit < ActiveRecord::Base + + DELAY_FOR_OUT_OF_ORDER_VOLATILITY = 60 * 5 # 5 minutes + MINIMAL_READING_COLUMNS = "#{column_names.join(',')},latitude,longitude" + + belongs_to :device + belongs_to :reading, :foreign_key => :id, :select => MINIMAL_READING_COLUMNS + + def self.process_queue(system_is_current = false) + true while process_next_queue_chunk + SpanningEventState.close_expired_trips if system_is_current + ensure + SpanningEventState.reset_cache + end + + def consider_and_discard +#puts "CONSIDER #{event_type} @ #{created_at}" + if event_type == 'delayed' + reading.update_attributes!(:note => event_type, :event_type => "delayed #{reading.event_type}") + else + reading.update_attributes!(:note => event_type) + SpanningEventState.get_by_reading(reading).consider_reading(reading) + end + destroy + end + +private + + def self.process_next_queue_chunk +# hits = all(:conditions => "created_at < adddate(now(),interval -#{DELAY_FOR_OUT_OF_ORDER_VOLATILITY} second)",:order => 'created_at,id',:limit => 100).each {| hit | hit.consider_and_discard} + hits = all(:order => 'created_at,id',:limit => 100).each {| hit | hit.consider_and_discard} + hits.any? + end +end Index: app/models/stop_event.rb =================================================================== --- app/models/stop_event.rb (revision 11258) +++ app/models/stop_event.rb (revision 11375) @@ -1,4 +1,5 @@ class StopEvent < ActiveRecord::Base + extend SuspectEvent belongs_to :reading belongs_to :device include ApplicationHelper Index: app/models/suspect_event.rb =================================================================== --- app/models/suspect_event.rb (revision 0) +++ app/models/suspect_event.rb (revision 11375) @@ -0,0 +1,64 @@ +module SuspectEvent + unloadable + SUSPECT_BATCH_LIMIT = 100 + SUSPECT_TOTAL = 2500 + + def identify_suspect_events(log = nil) + candidates = connection.select_rows "SELECT device_id, max(id), count(id) FROM #{table_name} WHERE suspect IS NULL OR duration IS NULL GROUP BY device_id" + log_info(log,"#{self}: #{candidates.length} possible suspects") + for row_results in candidates + device_id,last_event_id,event_count = row_results + if event_count.to_i > SUSPECT_TOTAL + log_info(log,"#{self}: #{event_count} total events -- immediately suspect") + connection.execute "update #{table_name} set suspect = 1 where suspect is null and device_id = #{device_id} and id <= #{last_event_id}" + else + true while identify_unterminated_events(device_id,last_event_id,log) + true while identify_overlapping_events(device_id,last_event_id,log) + true while identify_overlapped_events(device_id,last_event_id,log) + connection.execute "update #{table_name} set suspect = 0 where suspect is null and device_id = #{device_id} and id <= #{last_event_id}" + end + end + end + +private + def identify_overlapping_events(device_id,last_event_id,log) + process_suspects("overlapping events for #{device_id}",log,find_by_sql("select a.* from #{table_name} a,#{table_name} b where + a.device_id = #{device_id} and a.id <= #{last_event_id} and a.suspect is null and + a.duration > 1 and a.device_id = b.device_id and a.id != b.id and + b.duration is null and + b.created_at between a.created_at and adddate(a.created_at,interval (a.duration - 1) minute) + group by a.id + limit #{SUSPECT_BATCH_LIMIT}")) + end + + def identify_overlapped_events(device_id,last_event_id,log) + process_suspects("overlapped events for #{device_id}",log,find_by_sql("select a.* from #{table_name} a,#{table_name} b where + a.device_id = #{device_id} and a.id <= #{last_event_id} and a.suspect is null and + a.duration > 1 and a.device_id = b.device_id and a.id != b.id and + b.duration is not null and adddate(b.created_at,interval b.duration minute) between a.created_at and adddate(a.created_at,interval (a.duration - 1) minute) + group by a.id + limit #{SUSPECT_BATCH_LIMIT}")) + end + + def identify_unterminated_events(device_id,last_event_id,log) + process_suspects("unterminated events for #{device_id}",log,find_by_sql("select a.* from #{table_name} a,#{table_name} b where + a.device_id = #{device_id} and a.id <= #{last_event_id} and (a.suspect is null or a.suspect = 0) and + a.duration is null and a.device_id = b.device_id and a.id < b.id + group by a.id + limit #{SUSPECT_BATCH_LIMIT}")) + end + + def process_suspects(label,log,events) + return false unless events.any? + log_info(log,"#{self}: #{events.length} #{label}" ) + events.each do + |event| + event.update_attributes!(:suspect => true) + end + return events.length >= SUSPECT_BATCH_LIMIT + end + + def log_info(log,info) + log ? log.info(info) : puts(info) + end +end Index: app/controllers/reports_controller.rb =================================================================== --- app/controllers/reports_controller.rb (revision 11258) +++ app/controllers/reports_controller.rb (revision 11375) @@ -24,27 +24,28 @@ def trip get_start_and_end_date - @device = Device.find(params[:id]) - @device_names = Device.get_names(session[:account_id]) - + get_devices() + needy_events = TripEvent.find(:all,:conditions => ["device_id = ? AND created_at BETWEEN ? AND ? AND duration IS NOT NULL AND (distance IS NULL OR idle IS NULL)",params[:id],@start_dt_str, @end_dt_str]) for event in needy_events event.update_stats end - + @trip_events = TripEvent.paginate(:per_page=>ResultCount, :page=>params[:page], - :conditions => ["device_id = ? and created_at between ? and ?",params[:id],@start_dt_str, @end_dt_str], + :conditions => get_device_and_dates_with_duration_conditions(:trip_events), :readonly => true,# NOTE: this causes some problems, but would be nice... :include => [:reading_start,:reading_stop], :order => "created_at desc") - @readings = @trip_events - @record_count = TripEvent.count('id', :conditions => ["device_id = ? and created_at between ? and ?", params[:id], @start_dt_str, @end_dt_str]) + + @readings = @trip_events # TODO -- remove this??? + + @record_count = TripEvent.count('id', :conditions => get_device_and_dates_with_duration_conditions(:trip_events)) @actual_record_count = @record_count # this is because currently we are putting MAX_LIMIT on export data so export and view data going to be diferent in numbers. @record_count = MAX_LIMIT if @record_count > MAX_LIMIT - + @total_travel_time = TripEvent.sum(:duration,:conditions => ["id in (?)", @trip_events.collect(&:id)]) @total_idle_time = TripEvent.sum(:idle,:conditions => ["id in (?)", @trip_events.collect(&:id)]) @total_distance = TripEvent.sum(:distance,:conditions => ["id in (?)", @trip_events.collect(&:id)]) - @max_speed = Reading.maximum(:speed,:conditions => ['device_id = ? and created_at between ? and ?',params[:id],@start_dt_str, @end_dt_str]) + @max_speed = Reading.maximum(:speed,:conditions => get_device_and_dates_conditions) || 0 end def trip_detail @@ -102,7 +103,7 @@ get_start_and_end_date @device = Device.find(params[:id]) @device_names = Device.get_names(session[:account_id]) - conditions = get_device_and_dates_with_duration_conditions + conditions = get_device_and_dates_with_duration_conditions(:stop_events) @stop_events = StopEvent.paginate(:per_page=>ResultCount, :page=>params[:page], :conditions => conditions, :order => "created_at desc") @readings = @stop_events @record_count = StopEvent.count('id', :conditions => conditions) @@ -117,7 +118,7 @@ get_start_and_end_date @device = Device.find(params[:id]) @device_names = Device.get_names(session[:account_id]) - conditions = get_device_and_dates_with_duration_conditions + conditions = get_device_and_dates_with_duration_conditions(:idle_events) @idle_events = IdleEvent.paginate(:per_page=>ResultCount, :page=>params[:page], :conditions => conditions, :order => "created_at desc") @readings = @idle_events @record_count = IdleEvent.count('id', :conditions => conditions) @@ -132,7 +133,7 @@ get_start_and_end_date @device = Device.find(params[:id]) @device_names = Device.get_names(session[:account_id]) - conditions = get_device_and_dates_with_duration_conditions + conditions = get_device_and_dates_with_duration_conditions(:runtime_events,false) @runtime_events = RuntimeEvent.paginate(:per_page=>ResultCount, :page=>params[:page],:conditions => conditions,:order => "created_at desc") @runtime_total = RuntimeEvent.sum(:duration,:conditions => conditions) active_event = RuntimeEvent.find(:first,:conditions => "#{conditions} and duration is null") @@ -192,6 +193,17 @@ redirect_to :back end + def all_events + get_start_and_end_date + get_devices() + @readings = Reading.find_by_sql("SELECT readings.id,readings.created_at,readings.ignition,readings.speed,readings.event_type,t1.id AS trip_start_id,(SELECT t2.id FROM trip_events t2 WHERE t2.reading_stop_id = readings.id) AS trip_stop_id,t1.suspect AS trip_suspect,t1.duration AS trip_duration,i.id AS idle_id,i.duration,i.suspect AS idle_suspect,i.duration AS idle_duration,s.id AS stop_id,s.duration AS stop_duration,s.suspect AS stop_suspect FROM readings LEFT JOIN trip_events t1 ON readings.id = reading_start_id LEFT JOIN idle_events i ON i.reading_id = readings.id LEFT JOIN stop_events s ON s.reading_id = readings.id WHERE #{get_device_and_dates_conditions} ORDER BY readings.created_at,readings.id") + @record_count = @readings.length + @actual_record_count = @record_count + rescue + flash[:error] = $!.to_s + @readings,@record_count,@actual_record_count = [],0,0 + end + # Export report data to CSV def export params[:page] = 1 unless params[:page] @@ -199,11 +211,11 @@ case params[:type] when 'stop' - return export_events(StopEvent.find(:all, {:order => "created_at desc", :conditions => get_device_and_dates_with_duration_conditions})) + return export_events(StopEvent.find(:all, {:order => "created_at desc", :conditions => get_device_and_dates_with_duration_conditions(:stop_events)})) when 'idle' - return export_events(IdleEvent.find(:all, {:order => "created_at desc", :conditions => get_device_and_dates_with_duration_conditions})) + return export_events(IdleEvent.find(:all, {:order => "created_at desc", :conditions => get_device_and_dates_with_duration_conditions(:idle_events)})) when 'runtime' - return export_events(RuntimeEvent.find(:all, {:order => "created_at desc", :conditions => get_device_and_dates_with_duration_conditions})) + return export_events(RuntimeEvent.find(:all, {:order => "created_at desc", :conditions => get_device_and_dates_with_duration_conditions(:runtime_events,false)})) when 'maintenance' return export_maintenance when 'trip' @@ -308,15 +320,26 @@ end - def get_device_and_dates_conditions - "device_id = #{params[:id]} and created_at between '#{@start_dt_str}' and '#{@end_dt_str}'" - end - - def get_device_and_dates_with_duration_conditions - return "device_id = #{params[:id]} and ((created_at between '#{@start_dt_str}' and '#{@end_dt_str}') or (duration is null))" if Time.now < @end_dt_str.to_time - get_device_and_dates_conditions - end + def get_device_and_dates_conditions(table = :readings,optional_clause = nil) + "#{table}.device_id = #{params[:id]} AND #{table}.created_at BETWEEN '#{@start_dt_str}' AND '#{@end_dt_str}'#{optional_clause}" + end + def get_device_and_dates_with_duration_conditions(table = :readings,possible_suspects = true) + return "device_id = #{params[:id]} AND ((created_at BETWEEN '#{@start_dt_str}' AND '#{@end_dt_str}') OR (duration IS NULL))#{suspect_clause(possible_suspects)}" if Time.now < @end_dt_str.to_time + get_device_and_dates_conditions(table,suspect_clause(possible_suspects)) + end + + def suspect_clause(possible_suspects) + # @suspect_clause ||= current_user.is_super_super_admin? ? "" : " and (suspect = 0 or suspect is null)" if possible_suspects + @suspect_clause ||= " and (suspect = 0 or suspect is null)" if possible_suspects + end + + def get_devices + @device = Device.find(params[:id]) + @device_names = Device.get_names(session[:account_id]) + end + + def get_start_and_end_date if params[:start_date].blank? @end_date = Date.today Index: app/views/reports/all_events.rhtml =================================================================== --- app/views/reports/all_events.rhtml (revision 0) +++ app/views/reports/all_events.rhtml (revision 11375) @@ -0,0 +1,61 @@ +<%= render :partial => 'report_header' %> + + + + + + + + + + + + + <% trip_end_at,idle_end_at,stop_end_at,trip_suspect,idle_suspect,stop_suspect = nil,nil,nil,nil,nil,nil,nil %> + <% for reading in @readings %> + + + + + <% if reading.trip_start_id %> + + <% trip_end_at = reading.trip_duration ? reading.created_at.advance(:minutes => reading.trip_duration.to_i) : trip_suspect ? nil : Time.now %> + <% elsif reading.trip_stop_id %> + + <% trip_end_at = nil%> + <% elsif trip_end_at and ((trip_end_at - reading.created_at + 30) / 60).round >= 0 %> + + <% else%> + <% trip_end_at = nil%> + + <% end %> + <% if reading.stop_id %> + + <% stop_end_at = reading.stop_duration ? reading.created_at.advance(:minutes => reading.stop_duration.to_i) : stop_suspect ? nil : Time.now %> + <% elsif stop_end_at and ((stop_end_at - reading.created_at + 30) / 60).round >= 0 %> + + <% else%> + <% stop_end_at = nil%> + + <% end %> + <% if reading.idle_id %> + + <% idle_end_at = reading.idle_duration ? reading.created_at.advance(:minutes => reading.idle_duration.to_i) : idle_suspect ? nil : Time.now %> + <% elsif idle_end_at and ((idle_end_at - reading.created_at + 30) / 60).round >= 0 %> + + <% else%> + <% idle_end_at = nil%> + + <% end %> + + <% end %> +
WhenEventIgnitionSpeedTrip?Stop?Idle?
<%= displayLocalDT(reading.created_at) %><%= reading.event_type.titleize %><%= reading.ignition ? 'Y' : reading.ignition.nil? ? '?' : 'N' %> + <%= reading.display_speed %> + BEGIN + <% if reading.trip_stop_id %>END<% end %> + END  S:<%= reading.stop_duration %>  I:<%= reading.idle_duration %>  
+ <%if @readings.empty? %> +
+ No readings found. +
+ <%end%> Index: db/schema.rb =================================================================== --- db/schema.rb (revision 11258) +++ db/schema.rb (revision 11375) @@ -9,7 +9,7 @@ # # It's strongly recommended to check this file into your version control system. -ActiveRecord::Schema.define(:version => 20090812224428) do +ActiveRecord::Schema.define(:version => 20091203005626) do create_table "accounts", :force => true do |t| t.string "company", :limit => 75 @@ -128,8 +128,11 @@ t.integer "device_id" t.integer "reading_id" t.datetime "created_at" + t.boolean "suspect" end + add_index "idle_events", ["device_id", "created_at", "suspect"], :name => "index_idle_events_on_device_id_and_created_at_and_suspect" + create_table "maintenance_tasks", :force => true do |t| t.integer "device_id" t.string "description", :null => false @@ -215,6 +218,16 @@ add_index "sessions", ["session_id"], :name => "index_sessions_on_session_id" add_index "sessions", ["updated_at"], :name => "index_sessions_on_updated_at" + create_table "spanning_event_hits", :force => true do |t| + t.integer "device_id" + t.string "event_type" + t.boolean "ignition" + t.float "speed" + t.datetime "created_at" + end + + add_index "spanning_event_hits", ["created_at", "id"], :name => "index_spanning_event_hits_on_created_at_and_id" + create_table "stop_events", :force => true do |t| t.float "latitude" t.float "longitude" @@ -222,8 +235,11 @@ t.integer "device_id" t.datetime "created_at" t.integer "reading_id" + t.boolean "suspect" end + add_index "stop_events", ["device_id", "created_at", "suspect"], :name => "index_stop_events_on_device_id_and_created_at_and_suspect" + create_table "trip_events", :force => true do |t| t.integer "device_id" t.integer "reading_start_id" @@ -232,8 +248,11 @@ t.datetime "created_at" t.float "distance" t.integer "idle" + t.boolean "suspect" end + add_index "trip_events", ["device_id", "created_at", "suspect"], :name => "index_trip_events_on_device_id_and_created_at_and_suspect" + create_table "users", :force => true do |t| t.string "first_name", :limit => 30 t.string "last_name", :limit => 30 Index: db/migrate/20091203005626_create_spanning_event_hits.rb =================================================================== --- db/migrate/20091203005626_create_spanning_event_hits.rb (revision 0) +++ db/migrate/20091203005626_create_spanning_event_hits.rb (revision 11375) @@ -0,0 +1,25 @@ +class CreateSpanningEventHits < ActiveRecord::Migration + def self.up + create_table :spanning_event_hits do |t| + t.column :device_id,:integer + t.column :event_type,:string + t.column :ignition,:boolean + t.column :speed,:float + t.column :created_at,:datetime + end + add_index :spanning_event_hits,[:created_at,:id] + add_index :trip_events,[:device_id,:created_at,:suspect] + add_index :idle_events,[:device_id,:created_at,:suspect] + add_index :stop_events,[:device_id,:created_at,:suspect] + end + + def self.down + remove_index :trip_events,[:device_id,:created_at,:suspect] + remove_index :idle_events,[:device_id,:created_at,:suspect] + remove_index :stop_events,[:device_id,:created_at,:suspect] + drop_table :spanning_event_hits + add_index :trip_events,[:device_id,:suspect,:created_at] + add_index :idle_events,[:device_id,:suspect,:created_at] + add_index :stop_events,[:device_id,:suspect,:created_at] + end +end Index: db/migrate/20091203005620_suspected_events.rb =================================================================== --- db/migrate/20091203005620_suspected_events.rb (revision 0) +++ db/migrate/20091203005620_suspected_events.rb (revision 11375) @@ -0,0 +1,13 @@ +class SuspectedEvents < ActiveRecord::Migration + def self.up + add_column :trip_events,:suspect,:boolean + add_column :idle_events,:suspect,:boolean + add_column :stop_events,:suspect,:boolean + end + + def self.down + remove_column :trip_events,:suspect + remove_column :idle_events,:suspect + remove_column :stop_events,:suspect + end +end Index: db_procs.sql =================================================================== --- db_procs.sql (revision 11258) +++ db_procs.sql (revision 11375) @@ -332,12 +332,44 @@ DROP TRIGGER IF EXISTS trig_readings_after_insert;; CREATE TRIGGER trig_readings_after_insert AFTER INSERT ON readings FOR EACH ROW BEGIN - DECLARE last_reading_time DATETIME; - CALL check_for_trip_change(NEW.device_id, NEW.id, NEW.created_at, NEW.ignition); - SELECT r.created_at INTO last_reading_time FROM devices d,readings r WHERE d.id=NEW.device_id AND r.id=d.recent_reading_id; - IF NEW.created_at >= last_reading_time OR last_reading_time IS NULL THEN - UPDATE devices SET recent_reading_id=NEW.id WHERE id=NEW.device_id; - END IF; + DECLARE last_reading_time DATETIME; + IF NEW.created_at IS NOT NULL THEN + IF NEW.event_type IN ('engine on','engine off','GPS Lock') THEN + INSERT INTO spanning_event_hits (id,device_id,event_type,ignition,speed,created_at) VALUES (NEW.id,NEW.device_id,NEW.event_type,NEW.ignition,NEW.speed,NEW.created_at); + ELSE + SET @time_delay = 1; -- assure that no match is neither "delayed", "tardy" or "duplicate" + SELECT TIME_TO_SEC(TIMEDIFF(NEW.created_at,MAX(r.created_at))) INTO @time_delay FROM readings r WHERE r.device_id = NEW.device_id AND r.created_at >= NEW.created_at AND r.id < NEW.id; + IF @time_delay < -5 * 60 THEN + INSERT INTO spanning_event_hits (id,device_id,event_type,ignition,speed,created_at) VALUES (NEW.id,NEW.device_id,'delayed',NEW.ignition,NEW.speed,NEW.created_at); + ELSEIF @time_delay < 0 THEN + INSERT INTO spanning_event_hits (id,device_id,event_type,ignition,speed,created_at) VALUES (NEW.id,NEW.device_id,'tardy',NEW.ignition,NEW.speed,NEW.created_at); + ELSEIF @time_delay = 0 THEN + INSERT INTO spanning_event_hits (id,device_id,event_type,ignition,speed,created_at) VALUES (NEW.id,NEW.device_id,'duplicate',NEW.ignition,NEW.speed,NEW.created_at); + ELSE + SELECT NULL,NULL,NULL,NULL INTO @last_event_type,@last_ignition,@last_speed,@last_created_at; + SELECT r.event_type,r.ignition,r.speed,r.created_at + INTO @last_event_type,@last_ignition,@last_speed,@last_created_at + FROM readings r + WHERE r.device_id = NEW.device_id AND r.id < NEW.id AND r.created_at BETWEEN ADDDATE(NEW.created_at,INTERVAL -5 MINUTE) AND NEW.created_at + ORDER BY created_at DESC,id DESC + LIMIT 1; + IF @last_created_at IS NULL THEN + INSERT INTO spanning_event_hits (id,device_id,event_type,ignition,speed,created_at) VALUES (NEW.id,NEW.device_id,'alone',NEW.ignition,NEW.speed,NEW.created_at); + ELSEIF @last_event_type = 'GPS Lock' OR + @last_ignition IS NULL OR NEW.ignition IS NULL OR + (@last_speed > 0 AND NEW.speed = 0) OR (@last_speed = 0 AND NEW.speed > 0) OR + (NEW.ignition IS NOT NULL AND @last_ignition IS NOT NULL AND NEW.ignition != @last_ignition) THEN + INSERT INTO spanning_event_hits (id,device_id,event_type,ignition,speed,created_at) VALUES (NEW.id,NEW.device_id,'other',NEW.ignition,NEW.speed,NEW.created_at); + END IF; + END IF; + END IF; + + SELECT r.created_at INTO last_reading_time FROM devices d,readings r WHERE d.id=NEW.device_id AND r.id=d.recent_reading_id; + IF NEW.created_at >= last_reading_time OR last_reading_time IS NULL THEN + UPDATE devices SET recent_reading_id=NEW.id WHERE id=NEW.device_id; + END IF; +END IF; + END;; DROP PROCEDURE IF EXISTS insert_trip_event;; Index: lib/daemons/notifier.rb =================================================================== --- lib/daemons/notifier.rb (revision 11258) +++ lib/daemons/notifier.rb (revision 11375) @@ -25,8 +25,13 @@ begin while($running) do - logger.info("This notification daemon is still running at #{Time.now}.\n") + logger.info("The Notification and Spanning-Event-Hit (#{SpanningEventHit.count}) daemon is still running at #{Time.now}.\n") + TripEvent.identify_suspect_events(logger) + IdleEvent.identify_suspect_events(logger) + StopEvent.identify_suspect_events(logger) # not currently needed, but keeping up just in case... + SpanningEventHit.process_queue(true) # NOTE: if performance becomes an issue having these two logical processes together, put this in another daemon + NotificationState.instance.begin_reading_bounds Notifier.send_geofence_notifications(logger)