arel_report.rb

class ArelReport

  # == MIXINS
  extend ActiveModel::Callbacks
  define_model_callbacks :optionals, :only => [:after, :before]
  define_model_callbacks :query,     :only => :before

  # == CLASS
  class << self
    attr_accessor :projections, :default_table, :remove_zeros
    attr_accessor :joinings, :optionals, :alterations, :order_by

    def project(*items)
      @projections ||= {}

      extended = items.extract_options!

      project(@default_table.name.to_sym => items) if items.length > 0

      extended.each do |tbl, fields|
        @projections[tbl] ||= []
        @projections[tbl] += fields
        @projections[tbl].uniq!
      end
    end

    def join(*tables, &block)
      @joinings ||= []
      @joinings << {:tables => tables, :block => block}
    end

    def base_table(arel_table)
      @default_table = self.retrieve_table(arel_table)
      @projections ||= {}
      @projections[@default_table.name.to_sym] ||= []
    end

    def order_by(*fields)
      @order_by ||= []
      @order_by += fields.collect {|x| @default_table[x].asc}
    end

    def alter_query(&block)
      @alterations ||= []
      @alterations << block
    end

    def remove_zeros
      @remove_zeros = true
    end

    def optional_fields(*fields)
      @optionals ||= {}
      opts = fields.extract_options!

      fields.each do |fld|
        @optionals[fld] = {:check => "include_#{fld}"}.merge(opts)
      end
    end

    def retrieve_table(table)
      return table                       if table.is_a? Arel::Table
      table = table.to_s                 if table.is_a? Symbol
      table = table.classify.constantize if table.is_a? String
      table = table.arel_table           if table.ancestors.include? ActiveRecord::Base
      table
    end

    def coerce_date(data)
      case data
      when Date
        data
      when String
        Date.parse(data)
      when Fixnum, Bignum
        Date.new(data)
      end
    end

    alias_method :select_fields,  :project
    alias_method :select_field,   :project
    alias_method :optional_field, :optional_fields
    alias_method :table_object,   :base_table
  end

  # == CONSTRUCTOR
  def initialize(options = {})
    @options         = options                  || {}
    @default_table   = self.class.default_table
    @remove_zeros    = self.class.remove_zeros
    @projections     = self.class.projections   || {}
    @order_by        = self.class.order_by      || []
    @optionals       = self.class.optionals     || {}
    @alterations     = self.class.alterations   || []
    @joinings        = self.class.joinings      || []
    @where           = []
    @aggregate_names = []
    @query_alters    = []
    @raw_projects    = []

    _run_optionals_callbacks do
      process_optionals
    end
  end

  # == INSTANCE
  def default_table
    @default_table
  end

  def add_raw_project(clause, field_name=nil, aggregate=false)
    @raw_projects << clause
    @aggregate_names << field_name if aggregate
  end

  def case_statement(clause, positive, negative)
    Arel.sql("(CASE WHEN #{sql_string_for(clause)} THEN #{sql_string_for(positive)} ELSE #{sql_string_for(negative)} END)")
  end

  def add_aggregate(return_field_name, action, field, clause_field=nil, start_date=nil, end_date=nil)
    action = action.to_s.upcase

    if clause_field
      true_clause = @default_table[clause_field.to_sym].in(coerce_date(start_date)..coerce_date(end_date))
      agg_clause = case_statement(true_clause, field, 0)
      agg_clause = "CASE WHEN #{true_clause.to_sql} THEN #{Arel.sql(field.to_s)} ELSE 0 END"
    else
      agg_clause = Arel.sql(field.to_s)
    end

    add_raw_project(Arel.sql("#{action}(#{agg_clause}) AS #{Arel.sql(return_field_name.to_s)}"), return_field_name, true)
  end

  def order_by(field, order = :asc)
    @order_by << @default_table[field].send(order)
  end

  def add_field(*fields)
    extended = fields.extract_options!

    add_field(@default_table.name.to_sym => fields) if fields.length > 0

    extended.each do |tbl, ext_fields|
      @projections[tbl] ||= []
      @projections[tbl] += ext_fields
      @projections[tbl].uniq!
    end
  end

  def add_where(field, action, param)
    @where << @default_table[field].send(action, param)
  end

  def alter_query(&block)
    @query_alters << block
  end

  def to_sql
    setup_query.to_sql
  end

  def all
    results = ActiveRecord::Base.connection.select_all(setup_query.to_sql, self.class.to_s).collect {|x| HashWithIndifferentAccess.new(x)}

    if @remove_zeros
      results.delete_if do |ex|
        del = true
        @aggregate_names.each do |field|
          field_val = ex[field]
          del = false if field_val && field_val > 0
        end
        del
      end
    end

    results = after_query(results)

    results
  end

  def to_csv
    results = self.all
    return "No Results For Filter" if results.length == 0
    headers = results.first.keys
    records = [headers]

    results.each do |line|
      records << headers.collect {|k| line[k.to_sym]}
    end

    records.collect {|x| x.to_csv}.join
  end

  def options
    @options
  end

  # == PROTECTED
  protected

  def sql_string_for(obj)
    obj.respond_to?(:to_sql) ? obj.to_sql : obj.to_s
  end

  def process_optionals
    optionals = @optionals || {}

    optionals.each do |field, opts|
      check_field = opts[:check]

      next if opts[:not_blank] && @options[field].blank?
      next if check_field && !@options[check_field]

      add_field(field)
    end
  end

  def setup_query
    @query        = @default_table

    @projections.each do |tbl_name, fields|
      tbl = retrieve_table(tbl_name)

      fields.each do |fld|
        @query = @query.project(tbl[fld]).group(tbl[fld])
      end
    end

    @joinings.uniq.each do |join|
      tables = [@default_table] + join[:tables].collect {|tbl| retrieve_table(tbl)}
      @query = join[:block].bind(@query).call(*tables)
    end

    _run_query_callbacks do
      @query_alters.each do |blk|
        @query = blk.bind(@query).call
      end

      @raw_projects.uniq.each do |agg|
        @query = @query.project(agg)
      end

      @where.uniq.each do |clause|
        @query = @query.where(clause)
      end

      @order_by.uniq.each do |ordr|
        @query = @query.order(ordr.to_sql)
      end
    end

    @query
  end

  def retrieve_table(table)
    self.class.retrieve_table(table)
  end

  def coerce_date(data)
    self.class.coerce_date(data)
  end

  # == PROTECTED
  protected

  def after_query(results)
    results
  end

end

unit_demand_report.rb

class UnitDemandReport < ArelReport

  base_table OrderItem

  project :order_items      => [:product_group, :product_type, :item_number],
          :forecast_updates => [:future_plant]

  order_by :item_number

  #optional_field :plant, :check => :plant, :not_blank => true
  #optional_field :business_line, :check => :include_bu

  join ForecastUpdate do |base, fu|
    join(fu, Arel::Nodes::OuterJoin).
      on(base[:item_number].eq(fu[:item_number]).
      and(base[:plant].eq(fu[:current_plant])).
      and(base[:business_line].eq(fu[:business_line])).and(fu[:customer].eq("").or(fu[:customer].eq(nil))))
  end

  before_query :ignore_items
  before_query :set_filtering

  before_query do
    add_aggregate(:last_year,        :sum, :quantity, :invoice_date, '2010-01-01', Date.today - 1.year)
    add_aggregate(:this_year,        :sum, :quantity, :invoice_date, '2011-01-01', Date.today)
    add_aggregate(:last_year_full,   :sum, :quantity, :invoice_date, '2010-01-01', '2010-12-31')

    # our total inv groupings
    add_raw_project(Arel.sql("SUM(forecast_updates.inv_last_year) AS inv_last_year"))
    add_raw_project(Arel.sql("SUM(forecast_updates.inv_this_year) AS inv_this_year"))

    if options[:monthly]
      add_period_field(:this_jan, '2011-01-01', '2011-01-31')
      add_period_field(:this_feb, '2011-02-01', '2011-02-28')
      add_period_field(:this_mar, '2011-03-01', '2011-03-31')
      add_period_field(:this_apr, '2011-04-01', '2011-04-30')
      add_period_field(:this_may, '2011-05-01', '2011-05-31')
      add_period_field(:this_jun, '2011-06-01', '2011-06-30')
      add_period_field(:this_jul, '2011-07-01', '2011-07-31')
      add_period_field(:this_aug, '2011-08-01', '2011-08-31')
      add_period_field(:this_sep, '2011-09-01', '2011-09-30')
      add_period_field(:this_oct, '2011-10-01', '2011-10-31')
      add_period_field(:this_nov, '2011-11-01', '2011-11-30')
      add_period_field(:this_dec, '2011-12-01', '2011-12-31')
    else
      #add_period_field(:last_rest_this_year, Date.today - 1.year, '2010-12-31')
      #add_period_field(:last_jan_apr,        '2010-01-01', '2010-04-30')
      #add_period_field(:last_may_first_half, '2010-05-01', '2010-05-15')
      #add_period_field(:last_may_last_half,  '2010-05-16', '2010-05-31')
      #add_period_field(:last_jun_jul,        '2010-06-01', '2010-07-31')
      #add_period_field(:last_aug_dec,        '2010-08-01', '2010-12-31')
      add_period_field(:this_rest_this_year, Date.today,   '2011-12-31')
      add_period_field(:this_jan_apr,        '2011-01-01', '2011-04-30')
      add_period_field(:this_may_first_half, '2011-05-01', '2011-05-15')
      add_period_field(:this_may_last_half,  '2011-05-16', '2011-05-31')
      add_period_field(:this_jun_jul,        '2011-06-01', '2011-07-31')
      add_period_field(:this_aug_dec,        '2011-08-01', '2011-12-31')
    end
  end

  # == PROTECTED METHODS
  protected

  def add_period_field(field_name, start, stop)
    forecast   = ForecastUpdate.arel_table
    case_field = case_statement(default_table[:invoice_date].in(start.to_date..stop.to_date), "order_items.quantity", 0)
    multiplier = case_statement(forecast[:in_program].eq(true),
      Arel.sql("(1 + (CAST(ISNULL(forecast_updates.pct_change, 0) as float) / 100))"), 0)
    add_raw_project(Arel.sql("SUM(#{case_field} * #{multiplier}) AS #{Arel.sql(field_name.to_s)}"), field_name.to_s, true)
  end

  def ignore_items
    add_where(:item_number, :does_not_match_all, %w(%90-200% %90-205% %90-202% %90-208%))
    add_where(:item_number, :not_eq_all,         %w(20001 90-215 90-203 miles fuel\ surcharge))
    add_where(:item_number, :not_eq_all,         %w(Iowa Illinois))
  end

  def set_filtering
    add_where(:business_line, :not_eq, 'Internal')
    add_where(:business_line, :in, options[:business_line]) unless options[:business_line].blank?
    add_where(:plant,         :in, options[:plant])         unless options[:plant].blank?
    add_where(:product_group, :eq, options[:product_group]) unless options[:product_group].blank?
    add_where(:product_type,  :eq, options[:product_type])  unless options[:product_type].blank?
    add_where(:state,         :eq, options[:state])         unless options[:state].blank?
  end

  def after_query(results)
    if options[:monthly]
      breakdowns =          %w(jan feb mar apr may jun jul aug sep oct nov dec).collect(&:to_sym)
      breakdowns_for_bags = breakdowns[0..4]
    else
      breakdowns =          %w(rest_this_year jan_apr may_first_half may_last_half jun_jul aug_dec).collect(&:to_sym)
      breakdowns_for_bags = breakdowns[0..3]
    end

    new_results = results.collect do |rec|
      res = {
        :plant                    => rec[:future_plant],
        :product_group            => rec[:product_group],
        :product_type             => rec[:product_type],
        :item_number              => "=\"#{rec[:item_number]}\"",
        :description              => OrderItem.where(:item_number => rec[:item_number]).first.description,
        :business_line            => rec[:business_line],
        :pallet_quantity          => Product.where(:item_number => rec[:item_number]).first.try(:pallet_quantity),
        :current_inventory        => inventory_count(rec),
        :bags_on_hand             => bagging_count(rec),
        :percentage_growth        => 0,
        :last_year_to_date        => rec[:last_year],
        :this_year_to_date        => rec[:this_year],
        :total_estimate_next_year => 0}

      prefix = "this" #rec[:this_year] > 0 ? "this" : "last"

      res.merge!(breakdowns.inject({}) do |hash, breakdown|
        hash[breakdown] = rec["#{prefix}_#{breakdown}"]
        hash
      end)

      total          = breakdowns.inject(0) {|token, breakdown| token + res[breakdown]}
      total_for_bags = breakdowns_for_bags.inject(0) {|token, breakdown| token + res[breakdown]} || 0

      bags_needed = (total_for_bags - res[:bags_on_hand] - res[:current_inventory]).ceil
      res[:bags_needed_through_may] = bags_needed < 0 ? 0 : bags_needed

      breakdowns.each {|bd| res[bd] = res[bd].nan? ? 0 : res[bd].ceil}

      res[:total_estimate_next_year] = breakdowns.inject(0) {|token, bd| token + res[bd]}

      res[:percentage_growth] =
        "%0.2f" % (((res[:total_estimate_next_year] - res[:this_year_to_date]) / res[:this_year_to_date]) * 100)

      inventory_difference = rec[:inv_last_year].to_i - rec[:inv_this_year].to_i
      if inventory_difference != 0
        new_total = 0
        breakdowns.each do |bd|
          percentage = 1.0 / res[:total_estimate_next_year] * res[bd]
          percentage = percentage.nan? ? 0 : percentage
          bd_diff = (percentage * inventory_difference).round
          res[bd] += bd_diff
          new_total += res[bd]
        end
        res[:total_estimate_next_year] = new_total
      end

      res = nil if rec[:this_year].to_i == 0
      res
    end

    new_results.compact!
    new_results
  end

  def inventory_count(data)
    ret = Product.where(:item_number => data[:item_number])
    ret = ret.where(:facility_name => data[:plant]) if data[:plant].present?
    ret.sum(:current_inventory)
  end

  def bagging_count(data)
    ret = Product.where(:item_number => data[:item_number])
    ret = ret.where(:facility_name => data[:plant]) if data[:plant].present?
    ret = ret.all

    ret.map {|p| [p.hand_bags, p.zip_bags, p.premier_bags]}.flatten.compact.sum
  end

end