division.rb

class Division < ActiveRecord::Base

  def products_excel
    query = <<-EOS
      SELECT  firstname, surname, mobile,
          IF(subscribe_email = 1, 'Yes', 'No') as subscribe_email,
          IF(subscribe_sms = 1, 'Yes', 'No') as subscribe_sms,
          doppelbock_products.email,
          DATE_FORMAT(doppelbock_products.created_at, '%Y-%m-%d %H:%i:%s') as created_at,
          doppelbock_locations.name AS location,
          doppelbock_regions.name AS province,
          doppelbock_countries.name AS country,
          doppelbock_admin_users.name AS user,
          jobtitles.name AS jobtitle,
          primaryskill.name AS primaryskill,
          secondaryskill.name AS secondaryskill
      FROM doppelbock_products
      JOIN doppelbock_locations ON doppelbock_locations.id = doppelbock_products.location_id
      JOIN doppelbock_regions ON doppelbock_regions.id = doppelbock_locations.region_id
      JOIN doppelbock_countries ON doppelbock_countries.id = doppelbock_regions.country_id
      JOIN doppelbock_admin_users ON doppelbock_admin_users.id = doppelbock_products.admin_user_id
      JOIN jobtitles ON jobtitles.id = doppelbock_products.jobtitle_id
      JOIN skills AS primaryskill ON primaryskill.id = doppelbock_products.primaryskill_id
      JOIN skills AS secondaryskill ON secondaryskill.id = doppelbock_products.secondaryskill_id
      WHERE doppelbock_products.division_id = #{id}
      AND doppelbock_products.status = 1
      ORDER BY firstname, surname
    EOS
    connection.select_all(query).inject([]) do |product, row|
      product << [
        row['firstname'],
        row['surname'],
        row['email'],
        row['mobile'],
        row['location'],
        row['province'],
        row['country'],
        row['jobtitle'],
        row['primaryskill'],
        row['secondaryskill'],
        row['subscribe_email'],
        row['subscribe_sms'],
        row['user'],
        row['created_at']
      ]
    end
  end

end