Grails Goodness: Using Hibernate Native SQL Queries

EventService.groovy

package com.ericsson.tv.saas.portal

import org.hibernate.transform.AliasToEntityMapResultTransformer

class EventService {

	def sessionFactory

	def historyReport(currentUser) {
		def report = [:]

		final session = sessionFactory.currentSession
		final String query = buildQuery(currentUser)
		final sqlQuery = session.createSQLQuery(query)

		final results = sqlQuery.with {
			resultTransformer = AliasToEntityMapResultTransformer.INSTANCE
			list()
		}

		def months = [
			'DECEMBER', 'NOVEMBER', 'OCTOBER',
			'SEPTEMBER', 'AUGUST', 'JULY', 'JUNE',
			'MAY', 'APRIL', 'MARCH', 'FEBRUARY', 'JANUARY'
		]

		results.each { row ->
			report[row['YEAR']] = ['eventCount':row['TOTAL'], monthlyCount:[:]]
			months.each { month ->
				if(row[month] > 0) {
					report[row['YEAR']].monthlyCount[month] = row[month]
				}
			}
		}

		return report
	}

	def buildQuery(currentUser) {
		String query = $/
			SELECT
				YEAR(date) AS year,
				SUM(CASE MONTH(date) WHEN 1 THEN 1 ELSE 0 END) AS january,
				SUM(CASE MONTH(date) WHEN 2 THEN 1 ELSE 0 END) AS february,
				SUM(CASE MONTH(date) WHEN 3 THEN 1 ELSE 0 END) AS march,
				SUM(CASE MONTH(date) WHEN 4 THEN 1 ELSE 0 END) AS april,
				SUM(CASE MONTH(date) WHEN 5 THEN 1 ELSE 0 END) AS may,
				SUM(CASE MONTH(date) WHEN 6 THEN 1 ELSE 0 END) AS june,
				SUM(CASE MONTH(date) WHEN 7 THEN 1 ELSE 0 END) AS july,
				SUM(CASE MONTH(date) WHEN 8 THEN 1 ELSE 0 END) AS august,
				SUM(CASE MONTH(date) WHEN 9 THEN 1 ELSE 0 END) AS september,
				SUM(CASE MONTH(date) WHEN 10 THEN 1 ELSE 0 END) AS october,
				SUM(CASE MONTH(date) WHEN 11 THEN 1 ELSE 0 END) AS november,
				SUM(CASE MONTH(date) WHEN 12 THEN 1 ELSE 0 END) AS december,
				SUM(CASE YEAR(date) WHEN YEAR(date) THEN 1 ELSE 0 END) AS total
			FROM
				event
		/$

		if(currentUser.isContentProvider()) {
			query += " WHERE AND provider_id = :providerId "
		}

		query += " GROUP BY YEAR(date) "

		return query
	}
}