Refactor hell

instruction.rb

class Instruction < ActiveRecord::Base
  #...
  def self.new_over_period(from, to, range, user)

    where = ""

    if user.is_solicitor
      where = " AND instructions.solicitor_id IN ('"+user.solicitor.id.to_s+"')"
    end

    if user.is_casehandler
      where = " AND instructions.casehandler_id IN ('"+user.casehandler.id.to_s+"')"
    end

    if user.is_source
      where = " AND instructions.source_id IN ('"+user.source.id.to_s+"')"
    end

    case range
    when "days"
      sql = "SELECT instructions.solicitor_id, solicitors.name, DATE(instructions.created_at) as date, 
        count(instructions.id) FROM instructions INNER JOIN solicitors ON instructions.solicitor_id = solicitors.id  
        WHERE instructions.created_at >= '"+from.beginning_of_day.strftime('%Y-%m-%d %H:%M:%S')+"'
        AND instructions.created_at <= '"+to.end_of_day.strftime('%Y-%m-%d %H:%M:%S')+"' "+where+"
        AND instructions.deleted = FALSE GROUP BY instructions.solicitor_id, solicitors.name, DATE(instructions.created_at)"
    when "months"
      sql = "SELECT instructions.solicitor_id, solicitors.name, date_part('month',instructions.created_at) AS month, 
        date_part('year', instructions.created_at) AS year , count(instructions.id) FROM instructions 
        INNER JOIN solicitors ON instructions.solicitor_id = solicitors.id  
        WHERE instructions.created_at >= '"+from.beginning_of_day.strftime('%Y-%m-%d %H:%M:%S')+"' 
        AND instructions.created_at <= '"+to.end_of_day.strftime('%Y-%m-%d %H:%M:%S')+"'  "+where+" 
        AND instructions.deleted = FALSE GROUP BY instructions.solicitor_id, solicitors.name, 
        date_part('month',instructions.created_at), date_part('year', instructions.created_at)"
    else
     sql = "SELECT instructions.solicitor_id, solicitors.name, date_part('year', instructions.created_at) AS year , 
      count(instructions.id) FROM instructions INNER JOIN solicitors ON instructions.solicitor_id = solicitors.id 
      WHERE instructions.created_at >= '"+from.beginning_of_day.strftime('%Y-%m-%d %H:%M:%S')+"' 
      AND instructions.created_at <= '"+to.end_of_day.strftime('%Y-%m-%d %H:%M:%S')+"' "+where+"
      AND instructions.deleted = FALSE GROUP BY instructions.solicitor_id, solicitors.name, 
      date_part('year', instructions.created_at)"
    end


    ActiveRecord::Base.connection.execute(sql).to_a
  end
end

instruction_dashboard_filter.rb

class Filters::InstructionDashboardFilter
  attr_reader :from, :to, :range, :user

  def initialize(from, to, range, user)
    @from  = from
    @to    = to
    @range = range
    @user  = user
  end

  def new_over_period
    sql = Instruction.joins(:solicitor).
      select(select_fields).
      where('created_at >= ? and created_at <= ?',
            from.beginning_of_day,
            to.end_of_day).
      where(user_conditions)
      group(group_fields)
      
    Instruction.select_all(sql)
  end

protected
  def user_conditions
    conditions = { servicetype_ids: user.servicetype_ids }
    if user.is_solicitor
      conditions[:solicitor_id] = user.solicitor.id
    elsif user.is_casehandler
      conditions[:casehandler_id] = user.casehandler.id
    elsif user.is_source
      conditions[:source_id] = user.source.id
    end
    conditions
  end

  def day_range?
    @range == 'days'
  end

  def month_range?
    @range == 'months'
  end

  def select_fields
    fields = ['instructions.solicitor_id', 'solicitors.name', 'count(instructions.id)']
    if day_range?
      fields << 'DATE(instructions.report_send_date) as date'
    elsif month_range?
      fields << "date_part('month',instructions.created_at) AS month"
      fields << "date_part('year', instructions.created_at) AS year"
    else
      fields << "date_part('year', instructions.created_at) AS year"
    end
    fields
  end

  def group_fields
    fields = ['instructions.solicitor_id', 'solicitors.name', 'count(instructions.id)']
    if day_range?
      fields << 'DATE(instructions.report_send_date) as date'
    elsif month_range?
      fields << "date_part('month',instructions.created_at)"
      fields << "date_part('year', instructions.created_at)"
    else
      fields << "date_part('year', instructions.created_at) AS year"
    end
    fields
  end
end