# Note: Schema is legacy (not my design!)
class WidgetSummary < ActiveRecord::Base
  class << self
    def for_factory_by_part_between(factory, start_date, end_date)
      start_hour = Hour.after(start_date)
      end_hour = Hour.before(end_date)
      find_by_sql <<-SQL
        select WidgetSummary.* 
          from WidgetSummary
        inner join Widget 
          on Widget.id = WidgetSummary.widgetID
        inner join Factory 
          on Factory.id = Widget.factoryID
        inner join Hour 
          on Hour.id = WidgetSummary.hourID
        inner join HourOfDay 
          on HourOfDay.id = Hour.hourOfDayID
        inner join Day 
          on Day.id = Hour.dayID
        inner join DayOfWeek 
          on DayOfWeek.id = Day.dowID
        inner join MachinePartVersion
          on MachinePartVersion.id = WidgetSummary.platformPartVersionID
        inner join MachinePart
          on MachinePart.id = MachinePartVersion.partID
        where Factory.id = #{factory.id}
          and Hour.id >= #{start_hour.id}
          and Hour.id <= #{end_hour.id}
        order by Hour.id
      SQL
    end
  end
end