eminuxCRM/app/services/reports/builder.rb

class Reports::Builder
  def initialize(project: nil)
    @project = project
    @opp_scope = project ? Opportunity.for_project(project) : Opportunity.all
    @org_scope = project ? Organization.for_project(project) : Organization.all
  end

  def funnel
    metrics = Dashboard::Metrics.new(scope: @opp_scope, project: @project)
    {
      stages: Catalog::PIPELINE_ORDER.map { |s| { stage: s, label: Catalog.label_for(Catalog::PIPELINE_STAGES, s), count: metrics.stage_counts[s] } },
      conversions: metrics.conversion_rates,
      funnel: metrics.funnel_steps
    }
  end

  def lead_sources
    Catalog::LEAD_SOURCES.map do |key, label|
      orgs = @org_scope.where(lead_source: key)
      opps = @opp_scope.joins(:organization).where(organizations: { lead_source: key })
      won = opps.won.count
      leads = orgs.count
      {
        key: key,
        label: label,
        leads: leads,
        opportunities: opps.count,
        customers: won,
        conversion_rate: leads.zero? ? 0 : ((won.to_f / leads) * 100).round
      }
    end
  end

  def won_lost_by_month(months: 6)
    start_date = months.months.ago.beginning_of_month
    won = @opp_scope.won.where("won_at >= ?", start_date).group("DATE_TRUNC('month', won_at)").count
    lost = @opp_scope.lost.where("lost_at >= ?", start_date).group("DATE_TRUNC('month', lost_at)").count
    keys = (won.keys + lost.keys).uniq.sort
    keys.map do |month|
      {
        month: month.to_date,
        won: won[month] || 0,
        lost: lost[month] || 0
      }
    end
  end

  def lost_reasons
    @opp_scope.lost.group(:lost_reason).count.map do |reason, count|
      {
        key: reason,
        label: Catalog.label_for(Catalog::LOST_REASONS, reason),
        count: count
      }
    end.sort_by { |r| -r[:count] }
  end

  def sales_owners
    User.active.map do |user|
      open_opps = user.assigned_opportunities.merge(@opp_scope).open_stage
      won_opps = user.assigned_opportunities.merge(@opp_scope).won
      {
        user: user,
        open_count: open_opps.count,
        open_value: open_opps.sum(:estimated_value).to_f,
        won_count: won_opps.count,
        won_value: won_opps.sum(:estimated_value).to_f
      }
    end
  end

  def revenue_by_month(months: 6)
    start_date = months.months.ago.beginning_of_month
    @opp_scope.won
              .where("won_at >= ?", start_date)
              .group("DATE_TRUNC('month', won_at)")
              .sum(:estimated_value)
              .sort_by { |month, _| month }
              .map { |month, value| { month: month.to_date, value: value.to_f } }
  end
end