Scheduler

gistfile1.txt

### DISCLAIMER: UNTESTED ###

def self.on_range(top, bottom, schedule_type=nil)
    days = ((top.to_date)..(bottom.to_date))
    weekdays = days.map(&:wday).uniq
    months = days.map{|me| me.strftime("%m")}.uniq
    years = days.map{|me| me.strftime("%Y")}.uniq
    query = ""
    query += "schedule_type = '#{schedule_type}' and " if schedule_type
 
    #standard -> get schedule that overlaps given TIMESTAMP
    query += "    ((repeat = '0' and 
                    ((start_time, end_time) overlaps ( TIMESTAMP '#{top}', TIMESTAMP '#{bottom}'))
                   )"
 
    #daily -> get schedule that overlaps only with given range's TIME
    query += " or ((repeat = '1') and 
                   ((start_time::time, end_time::time)
                    overlaps
                    (timestamp '#{top}'::time, timestamp '#{bottom}'::time))
                  )"
 
    #weekly -> get schedule that overlaps with given range's week
    #          check for the week days of given range
    #          check for time overlap
    #          check only the start_time wday-> assumption weekly days are a max of 7 days (grouped indiv days)
    weekdays.each do |w|
      query += " or ((repeat = '2') and 
                     (to_char(start_time, 'D') = '#{w+1}') and
                     ((start_time::time, end_time::time)
                      overlaps
                      (timestamp '#{top}'::time, timestamp '#{bottom}'::time))
                    )"
    end
 
    #yearly -> get schedule that overlaps with given range's years
    #          convert schedule to range's year
    #          check for timestamp overlap
    years.each { |y|
    query += " or ((repeat = '4') and
                   ((to_timestamp(to_char(start_time, 'DD MM #{y} HH24 MI SS'), 'DD MM YYYY HH24 MI SS'),
                     to_timestamp(to_char(end_time, 'DD MM #{y} HH24 MI SS'), 'DD MM YYYY HH24 MI SS'))
                    overlaps
                    (timestamp '#{top}', timestamp '#{bottom}'))
                  )"
      #monthly -> get schedule that overlaps with given range's months
      #           convert schedule to range's month and year
      #           check for timestamp overlap
      months.each { |m|
        query += " or ((repeat = '3') and
                       ((to_timestamp(to_char(start_time, 'DD #{m} #{y} HH24 MI SS'), 'DD MM YYYY HH24 MI SS'), 
                         to_timestamp(to_char(end_time, 'DD #{m} #{y} HH24 MI SS'), 'DD MM YYYY HH24 MI SS'))
                        overlaps
                        (timestamp '#{top}', timestamp '#{bottom}'))
                      )"
      }
    }
    query += ")"
    where(query)
  end