gistfile1.rb

  def revenue_history_months_sql(months)
    if Order.connection.adapter_name == 'PostgreSQL'
         "select extract(year from orders.order_time) as year,
                 extract(month from orders.order_time) as month,
                 extract(month from age(date_trunc('month', orders.order_time))) as months_ago,
                 sum(line_items.unit_price * quantity)
                   - sum(coalesce(coupons.amount, 0))
                   - sum(line_items.unit_price * quantity * coalesce(percentage, 0) / 100) as revenue,
                 max(orders.order_time) as last_time
    
            from orders
                 inner join line_items on orders.id = line_items.order_id
                 left outer join coupons on coupons.id = orders.coupon_id
    
           where status = 'C' and lower(payment_type) != 'free'
    
           group by year, month, months_ago
    
           order by last_time desc limit #{months}"
    else
      # Assume mysql if it's not postgresql
      "select year(orders.order_time) as year,
              month(orders.order_time) as month,
              timestampdiff(month, orders.order_time, current_date) as months_ago,
              sum(line_items.unit_price * quantity)
                - sum(coalesce(coupons.amount, 0))
                - sum(line_items.unit_price * quantity * coalesce(percentage, 0) / 100) as revenue,
              date_format(max(orders.order_time), '%Y-%m-01 12:00:00') as last_time

         from orders
              inner join line_items on orders.id = line_items.order_id
              left outer join coupons on coupons.id = orders.coupon_id

        where status = 'C' and lower(payment_type) != 'free' and orders.order_time > date_format(current_date,'%Y-%m-%d 23:59:59') - interval #{months} month
        
        group by year, month
        
        order by year, month desc"
    end
  end